ORACLE APPLICATIONS TECHNICAL: Basic Shell Script Commands

Basic Shell Script Commands

# Create Directory
mkdir <dirname>
# Remove Directory
rmdir <dirname>
#remove folder with files
rm -r -f <dirname>
# Change Directory
cd <newpath>
# Create new file
vi <newfile.ext>
#insert data into file
vi <openfilename.ext>
esc i <make changes>
#Save file
esc :wq enter
# exit without saving changes
esc :q! enter
# open existing file
vi <existingfilename.ext>
#remove file
rm <filename.ext>
# copy file with same name
cp <sourcedir>/<sourcefilename.ext> <destinationdir>
# copy file with new name
cp <sourcedir>/<sourcefilename.ext> <destinationdir>/<newfilename.ext>
# Move file with same name
mv <sourcedir>/<sourcefilename.ext> <destinationdir>
# move file with data appended to filename in the front
mv <sourcedir>/<sourcefilename.ext> <destinationdir>/`date+%H%M%d%m%y`<filename.ext>
#print line
echo “your text here to print”
#print date
echo `date`

R12 E-Business Suite Suppliers Query - SQL to join Suppliers, Contacts, Banks

In the Oracle E-Business Suite (EBS) Release 12 the data model of Suppliers has become much more complex. The base tables have changed (Suppliers, Sites, Bank Accounts, Contacts) and some of the fields have become obsolete.
Here is a query to bring many of the Supplier attributes together, with focus on banks / bank accounts, payment methods, contacts, remittance delivery (email, notification method). Please post comments if you find any issues!
Adjust the WHERE clause on the first WITH query to return the suppliers that you need to report on. Hope this query helps someone out.
Add additional fields to the final query (or WITH queries as required.)
with vendors as
(
select vendor_id
from ap_suppliers
where 1=1
/* COMMENT / UNCOMMENT and UPDATE THE NEXT 5 LINES AS YOU REQUIRE */
--and vendor_type_lookup_code = 'VENDOR'
--and upper( vendor_name ) like 'VIRTUATE%'
and creation_date between '01-JAN-2011' and '01-JAN-2012'
--and enabled_flag = 'Y'
)
, vend as
(
select pv.vendor_id vendor_id
, pv.vendor_name_alt vendor_name_alt
, pv.vendor_name vendor_name
, pv.segment1 vendor_number
, pv.vendor_type_lookup_code vendor_type_lookup_code
from ap_suppliers pv
where pv.vendor_id in (select v.vendor_id from vendors v)
)
, site as
(
select ss.vendor_id vendor_id
, ss.vendor_site_id vendor_site_id
, ss.vendor_site_code vendor_site_code
, ss.vendor_site_code_alt vendor_site_code_alt
, ss.vat_code tax_code
, ss.vat_registration_num vat_registration_num
, t.name terms_name
, ss.address_line1 ss_address_line1
, ss.address_line2 ss_address_line2
, ss.address_line3 ss_address_line3
, ss.zip ss_zip
, ss.city ss_city
, ss.state ss_state
, ss.country ss_country
, ss.area_code ss_area_code
, ss.phone ss_phone
, ss.fax_area_code ss_fax_area_code
, ss.fax ss_fax
, ss.telex ss_telex
, ss.pay_site_flag ss_pay_site_flag
, ss.primary_pay_site_flag ss_primary_pay_site_flag
, pm.remit_advice_delivery_method ss_remit_advice_deliv_meth
, pm.remit_advice_email ss_remit_advice_email
, pm.remit_advice_fax ss_remit_advice_fax
, pm.payment_method_code ss_payment_method_code
, ss.remittance_email ss_remittance_email
, ss.supplier_notif_method ss_supplier_notif_method
, ps.addressee ss_addressee
, ( select hcp.phone_area_code
from hz_contact_points hcp
where hcp.owner_table_id = ss.party_site_id
and hcp.owner_table_name = 'HZ_PARTY_SITES'
and hcp.phone_line_type = 'GEN'
and hcp.contact_point_type = 'PHONE'
--and hcp.created_by_module = 'AP_SUPPLIERS_API'
and rownum < 2 -- copied from OAF View Object
) ss_hcp_phone_area_code
, ( select hcp.phone_number
from hz_contact_points hcp
where hcp.owner_table_id = ss.party_site_id
and hcp.owner_table_name = 'HZ_PARTY_SITES'
and hcp.phone_line_type = 'GEN'
and hcp.contact_point_type = 'PHONE'
--and hcp.created_by_module = 'AP_SUPPLIERS_API'
and rownum < 2 -- copied from OAF View Object
) ss_hcp_phone_number
, ( select hcp.phone_area_code
from hz_contact_points hcp
where hcp.owner_table_id = ss.party_site_id
and hcp.owner_table_name = 'HZ_PARTY_SITES'
and hcp.phone_line_type = 'FAX'
and hcp.contact_point_type = 'PHONE'
--and hcp.created_by_module = 'AP_SUPPLIERS_API'
and rownum < 2 -- copied from OAF View Object
) ss_hcp_fax_area_code
, ( select hcp.phone_number
from hz_contact_points hcp
where hcp.owner_table_id = ss.party_site_id
and hcp.owner_table_name = 'HZ_PARTY_SITES'
and hcp.phone_line_type = 'FAX'
and hcp.contact_point_type = 'PHONE'
--and hcp.created_by_module = 'AP_SUPPLIERS_API'
and rownum < 2 -- copied from OAF View Object
) ss_hcp_fax_number
from ap_supplier_sites_all ss
, ap_suppliers sup
, ap_terms t
, (
select ss.vendor_site_id
, payee.remit_advice_delivery_method
, payee.remit_advice_email
, payee.remit_advice_fax
, pm.payment_method_code
from iby_external_payees_all payee
, iby_ext_party_pmt_mthds pm
, hz_party_sites ps
, ap_supplier_sites_all ss
where payee.payee_party_id = ps.party_id
and payee.payment_function = 'PAYABLES_DISB'
and payee.party_site_id = ss.party_site_id
and payee.supplier_site_id = ss.vendor_site_id
and payee.org_id = ss.org_id
and payee.org_type = 'OPERATING_UNIT'
and ss.party_site_id = ps.party_site_id
and pm.ext_pmt_party_id = payee.ext_payee_id
and pm.primary_flag = 'N'
and not exists
( select 1
from iby_ext_party_pmt_mthds pm2
where pm.ext_pmt_party_id = pm2.ext_pmt_party_id
and pm2.primary_flag = 'Y'
)
union all
select ss.vendor_site_id
, payee.remit_advice_delivery_method
, payee.remit_advice_email
, payee.remit_advice_fax
, pm.payment_method_code
from iby_external_payees_all payee
, iby_ext_party_pmt_mthds pm
, hz_party_sites ps
, ap_supplier_sites_all ss
where payee.payee_party_id = ps.party_id
and payee.payment_function = 'PAYABLES_DISB'
and payee.party_site_id = ss.party_site_id
and payee.supplier_site_id = ss.vendor_site_id
and payee.org_id = ss.org_id
and payee.org_type = 'OPERATING_UNIT'
and ss.party_site_id = ps.party_site_id
and pm.ext_pmt_party_id = payee.ext_payee_id
and pm.primary_flag = 'Y'
) pm
, hz_party_sites ps
where sup.vendor_id in (select vendor_id from vendors)
and sup.vendor_id = ss.vendor_id
and pm.vendor_site_id = ss.vendor_site_id
and ss.party_site_id = ps.party_site_id (+)
and ss.terms_id = t.term_id (+)
)
, cont as
(
select pv.vendor_id vendor_id
, pvs.vendor_site_id vendor_site_id
, hp.party_id c_party_id
, hp.person_first_name c_first_name
, hp.person_last_name c_last_name
, hp.person_title c_person_title
, hcpe.email_address c_email_address
, hcpp.phone_area_code c_phone_area_code
, hcpp.phone_number c_phone_number
, hcpf.phone_area_code c_fax_area_code
, hcpf.phone_number c_fax_number
from hz_parties hp
, hz_relationships hzr
, hz_contact_points hcpp
, hz_contact_points hcpf
, hz_contact_points hcpe
, ap_suppliers pv
, ap_supplier_sites_all pvs
, hz_party_sites hps
where hp.party_id = hzr.subject_id
and hzr.relationship_type = 'CONTACT'
and hzr.relationship_code = 'CONTACT_OF'
and hzr.subject_type = 'PERSON'
and hzr.subject_table_name = 'HZ_PARTIES'
and hzr.object_type = 'ORGANIZATION'
and hzr.object_table_name = 'HZ_PARTIES'
and hzr.status = 'A'
and hcpp.owner_table_name(+) = 'HZ_PARTIES'
and hcpp.owner_table_id(+) = hzr.party_id
and hcpp.phone_line_type(+) = 'GEN'
and hcpp.contact_point_type(+) = 'PHONE'
and hcpf.owner_table_name(+) = 'HZ_PARTIES'
and hcpf.owner_table_id(+) = hzr.party_id
and hcpf.phone_line_type(+) = 'FAX'
and hcpf.contact_point_type(+) = 'PHONE'
and hcpe.owner_table_name(+) = 'HZ_PARTIES'
and hcpe.owner_table_id(+) = hzr.party_id
and hcpe.contact_point_type(+) = 'EMAIL'
and hcpp.status (+)='A'
and hcpf.status (+)='A'
and hcpe.status (+)='A'
and hps.party_id = hzr.object_id
and pvs.party_site_id = hps.party_site_id
and pv.vendor_id = pvs.vendor_id
and exists
( select 1
from ap_supplier_contacts ascs
where (ascs.inactive_date is null
or ascs.inactive_date > sysdate)
and hzr.relationship_id = ascs.relationship_id
and hzr.party_id = ascs.rel_party_id
and hps.party_site_id = ascs.org_party_site_id
and hzr.subject_id = ascs.per_party_id
)
and pv.vendor_id in (select vendor_id from vendors)
)
, bank as
(
select pv.vendor_id vendor_id
, ss.vendor_site_id vendor_site_id
, hopbank.bank_or_branch_number bank_number
, hopbranch.bank_or_branch_number branch_number
, eba.bank_account_num bank_account_num
, eba.bank_account_name bank_account_name
, piu.start_date bank_use_start_date
, piu.end_date bank_use_end_date
, piu.order_of_preference bank_priority
from iby_ext_bank_accounts eba
, iby_external_payees_all payee
, iby_pmt_instr_uses_all piu
, ap_supplier_sites_all ss
, ap_suppliers pv
, hz_organization_profiles hopbank
, hz_organization_profiles hopbranch
where 1=1
and eba.bank_id = hopbank.party_id
and eba.branch_id = hopbranch.party_id
and payee.payment_function = 'PAYABLES_DISB'
and payee.party_site_id = ss.party_site_id
and payee.supplier_site_id = ss.vendor_site_id
and payee.org_id = ss.org_id
and payee.org_type = 'OPERATING_UNIT'
and payee.ext_payee_id = piu.ext_pmt_party_id
and piu.payment_flow = 'DISBURSEMENTS'
and piu.instrument_type = 'BANKACCOUNT'
and piu.instrument_id = eba.ext_bank_account_id
and piu.start_date < sysdate
and ( piu.end_date is null or
piu.end_date > sysdate
)
and ss.vendor_id = pv.vendor_id
and pv.vendor_id in (select vendor_id from vendors)
)
-- select distinct v.*, s.*, c.*, b.*
select distinct v.vendor_id supplier_id
, v.vendor_number supplier_num
, v.vendor_name supplier_name
, v.vendor_type_lookup_code supplier_type
, s.terms_name terms_name
, s.tax_code invoice_tax_code
, s.vat_registration_num vat_registration_num
, s.vendor_site_code site_code
, s.ss_address_line1 address1
, s.ss_address_line2 address2
, s.ss_address_line3 address3
, s.ss_city suburb
, s.ss_state state
, s.ss_zip post_code
, s.ss_country country
, s.ss_payment_method_code payment_method
, b.bank_account_name bank_account_name
, b.bank_number bank_number
, b.branch_number branch_number
, b.bank_account_num bank_account_num
, s.ss_remit_advice_email remittance_email
, s.ss_remit_advice_deliv_meth notification_method
, c.c_first_name contact_first_name
, c.c_last_name contact_last_name
, c.c_person_title contact_title
, c.c_email_address contact_email
, c.c_phone_area_code contact_ph_area_code
, c.c_phone_number contact_ph_number
, c.c_fax_area_code contact_fax_area_code
, c.c_fax_number contact_fax_number
from vend v
, site s
, cont c
, bank b
where v.vendor_id = s.vendor_id (+)
and s.vendor_id = b.vendor_id (+)
and s.vendor_site_id = b.vendor_site_id (+)
and s.vendor_id = c.vendor_id (+)
and s.vendor_site_id = c.vendor_site_id (+)
and nvl(b.bank_priority,-1) = (select nvl(min(bank_priority),-1)
from bank b2
where b2.vendor_id = b.vendor_id
and b2.vendor_site_id = b.vendor_site_id)
order by 3,1,2,4,5,6,7,8,9,10,11,12,13;

GL interface using bulk collect
Hi Friends,

These are the mandatory columns in gl interface....

1)Once we get the data into the stage table we have to transfer from stage table into
Interface table by using PL/SQL Program
Inside this program we write
1)Cursor (to select data from stage table)
2)Validate Data
3)Insert Statement(To Insert Into Interface table.

Note:1) If Record is valid record then we will insert into interface table other wise
we will insert into Error tables.
2)Before Inserting the Data into Interface tables first we have to understand the
Interface table structure and we should know waht data is valid and what data
is not valid for the all Mandatroy columns.

2)Once the data is Inserted into the Interface table . Then we will submit concurrent
program to transfer the data from interface table to Base Tables.

In this Interface Interface table is GL_INTERFACE
Mandatroy Columns:
==================
1)STATUS
2)ACCOUNTING_DATE
3)CURRENCY_CODE
4)CREATED_BY
5)CREATION_DATE
6)USER_JE_SOURCE_NAME
7)USER_JE_CATEGORY_NAME
8)ACTUAL_FLAG
9)ENTERED_DR
10)ENTERED_CR
11)GROUP_ID
12)PERIOD_NAME
13)SET_OF_BOOKS_ID

1)Status Column will accept any Data. but we will insert always standard string called
"NEW". It means that we are bringing new data into Oracle Applications.

2)ACCOUNTING_DATE : Column will accept valid acc_date as per the accounting Periods
3)CURRENCY_CODE : from FND_CURRENCIES table we can find out wether CUrrency Code is
valid or Not if Currecny code is available in the Table and enabled it is
valid.otherwise Invalid
4)CREATED_BY : IS nothing but UserID we have to find wether USerID is valid or not
By using FND_USER table we can find out wether it is valid or not.
5)CREATION_DATE : Should be valid date
date should be <= SYSDATE
6)USER_JE_SOURCE_NAME: Valid Source name
By using GL_JE_SOURCES table we can find wether valid source or not.

7)USER_JE_CATEGORY_NAME : Will accept valid cvategory name
By using GL_JE_CATEGORIES table we can find out wether valid category or Not.
8)ACTUL_FLAG : This Column will accept single Character
Either 'A' 'B' 'E'
A=Actuval Amounts
B=Budeget Amounts
E=Encumbrance Amounts
9)ENTERED_DR
10)ENTERED_CR : Both Columns we accept Numbers Only but same number we have to insert
into both the columns.
Always ENTERED_CR = ENTERED_DR . Otherwise Suspense Account will be
Created.
11)GROUP_ID : Will accept any Number
12)period_name : Will Accept valid period and that period should be in the Open status
By using GL_PERIODS we can find wether Period is there or not
GL_PERIOD_STATUSES table we can find wether it is in "Open"
status or Not.
13)SET_OF_BOOKS_ID : This column will accept valid set of Books ID. By using
GL_SETS_OF_BOOKS table we can fnd out wether valuid set_of_books_id or
not.
Program :

CREATE OR REPLACE PACKAGE GL_INTERFACE_PKG
AS
PROCEDURE GL_INTERFACE_PROC(ERRBUF OUT VARCHAR2, RETCODE OUT VARCHAR2)
END GL_INTERFACE_PKG;

/

CREATE OR REPLACE PACKAGE BODY APPS.gl_interface_pkg
AS
PROCEDURE gl_interface_proc (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
-- cursor declaration
CURSOR gl_cur
IS
SELECT *
FROM xtg_gl_interface_stg;

TYPE gl_data_tbl IS TABLE OF xtg_gl_interface_stg%ROWTYPE
INDEX BY BINARY_INTEGER;

rec_cur gl_data_tbl;
l_currencycode VARCHAR2 (25);
l_set_of_books_id NUMBER (5);
l_flag VARCHAR2 (2);
l_error_msg VARCHAR2 (100);
l_err_flag VARCHAR2 (10);
l_category VARCHAR2 (100);
l_userid NUMBER (10);
l_count NUMBER (9) DEFAULT 0;
BEGIN
DELETE FROM gl_interface;

COMMIT;

OPEN gl_cur;

FETCH gl_cur
BULK COLLECT INTO rec_cur;

CLOSE gl_cur;

FOR rec_cur IN gl_cur
LOOP
l_count := l_count + 1;
l_flag := 'A';

--Category Column Validation
BEGIN
SELECT user_je_category_name
INTO l_category
FROM gl_je_categories
WHERE user_je_category_name = rec_cur.user_je_category_name;
EXCEPTION
WHEN OTHERS
THEN
l_flag := 'E';
l_error_msg := 'Category does not exist ';
fnd_file.put_line
(fnd_file.LOG,
'Inserting data into the Interface TABLE'
|| '-'
|| l_count
|| ' '
|| l_error_msg
);
END;

--End Category Column Validation
--User ID column validation
BEGIN
SELECT user_id
INTO l_userid
FROM fnd_user
WHERE user_id = rec_cur.created_by;
EXCEPTION
WHEN OTHERS
THEN
l_flag := 'E';
l_error_msg := 'User ID does not exist ';
fnd_file.put_line
(fnd_file.LOG,
'Inserting data into the Interface TABLE'
|| '-'
|| l_count
|| ' '
|| l_error_msg
);
END;

--End of Created_by OR UserID column Validation
--Set of books Validation
BEGIN
SELECT set_of_books_id
INTO l_set_of_books_id
FROM gl_sets_of_books
WHERE set_of_books_id = rec_cur.set_of_books_id;
EXCEPTION
WHEN OTHERS
THEN
l_flag := 'E';
l_error_msg := 'set of Books ID does not exist ';
fnd_file.put_line
(fnd_file.LOG,
'Inserting data into the Interface TABLE'
|| '-'
|| l_count
|| ' '
|| l_error_msg
);
END;

--Cuurency Code Validation
BEGIN
SELECT currency_code
INTO l_currencycode
FROM fnd_currencies
WHERE currency_code = rec_cur.currency_code
AND currency_code = 'USD';
EXCEPTION
WHEN OTHERS
THEN
l_flag := 'E';
l_error_msg := 'currency code does not exists';
fnd_file.put_line
(fnd_file.LOG,
'Inserting data into the Interface TABLE'
|| '-'
|| l_count
|| ' '
|| l_error_msg
);
END;

IF l_flag != 'E'
THEN
fnd_file.put_line (fnd_file.LOG,
'Inserting data into the Interface TABLE'
);

INSERT INTO gl_interface
(status, set_of_books_id,
accounting_date, currency_code,
date_created, created_by,
actual_flag, user_je_category_name,
user_je_source_name,
user_currency_conversion_type,
segment1, segment2,
segment3, segment4,
segment5, entered_dr,
entered_cr, accounted_dr,
accounted_cr, GROUP_ID,
reference1, reference2,
reference4, reference5
)
VALUES (rec_cur.status, rec_cur.set_of_books_id,
rec_cur.accounting_date, rec_cur.currency_code,
SYSDATE,fnd_global.user_id,
rec_cur.actual_flag, rec_cur.user_je_category_name,
rec_cur.user_je_source_name,
rec_cur.user_currency_conversion_type,
rec_cur.segment1, rec_cur.segment2,
rec_cur.segment3, rec_cur.segment4,
rec_cur.segment5, rec_cur.entered_dr,
rec_cur.entered_cr, rec_cur.accounted_dr,
rec_cur.accounted_cr, rec_cur.GROUP_ID,
rec_cur.reference1, rec_cur.reference2,
rec_cur.reference4, rec_cur.reference5
);
END IF;

l_flag := NULL;
l_error_msg := NULL;
END LOOP;

COMMIT;
END;
END;
/
http://sandeeporaclenotes.blogspot.in/2011/07/ap-invoice-interface-procedure.html

ORACLE APPLICATIONS TECHNICAL

Monday, January 30, 2012

Basic Shell Script Commands

1 comment: