To migrate DFF from Instance1 to Instance2

/*Download description flexfield*/
Run the below download command in home directory of Instance1, the ldt file descript_flex.ldt is created in the same directory.

FNDLOAD apps/ 0 Y DOWNLOAD @FND:patch/115/import/afffload.lct descript_flex.ldt desc_flex_application DESCRIPTIVE_FLEXFIELD_NAME=description_flexfield_name

desc_flex_application - is the shortname of the Application of the DFF.
description_flexfield_name - This is not title of the DFF.

To get description_flexfield_name:
Open the DFF in Application DeveloperFlexfieldDescriptiveSegments

click help->diagnostics->examine->
block=table, field=DESCRIPTIVE_FLEXFIELD_NAME.
The value in the field “Value” is the description_flexfield_name.

For Example, The description_flexfield_name of the DFF “Additional Information” in the above screenshot is: CS_INCIDENTS_ALL_B_EXT.
Application is: Service.
Short name of Service is: CS
And the download command is:

FNDLOAD apps/ 0 Y DOWNLOAD @FND:patch/115/import/afffload.lct descript_flex.ldt CS DESCRIPTIVE_FLEXFIELD_NAME=CS_INCIDENTS_ALL_B_EXT



/*Upload description flexfield*/
Transfer the ldt file descript_flex.ldt from Instance1 to the home directory of the Instance2 and run the below upload command in the same directory.

FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afffload.lct descript_flex.ldt

/*Compiling description flexfield*/
After migration, run the below compilation command to compile the flexfield in the Instance2.

fdfcmp apps/ 0 Y D desc_flex_application description_flexfield_name

Delete Duplicate Rows From A Table

Deleting duplicate emp_id s from the EMP Table.

1) DELETE FROM EMP E1
WHERE ROWID >(SELECT min(ROWID)
FROM EMP E2
WHERE E1.EMP_ID = E2.EMP_ID);

2) DELETE FROM EMP E1
WHERE E1.EMP_ID IN (SELECT E2.EMP_ID
FROM EMP E2
WHERE E1.EMP_ID = E2.EMP_ID
AND E1.ROWID > E2.ROWID);

How To Create New Users From Backend

The following Script can be used to create a User called ‘TESTUSER’ with Password as ‘WELCOME1’:

The Initial responsibility assigned in this case is System Administrator.

DECLARE
ln_user_id NUMBER;
ln_resp_id NUMBER;
ln_app_id NUMBER;

BEGIN
fnd_user_pkg.createUser( 'TESTUSER','','WELCOME1');

SELECT user_id
INTO ln_user_id
FROM fnd_user WHERE user_name = 'TESTUSER';

SELECT responsibility_id,
application_id
INTO ln_resp_id,
ln_app_id
FROM fnd_responsibility
WHERE responsibility_key = 'SYSTEM_ADMINISTRATOR';

FND_USER_RESP_GROUPS_API.insert_assignment( ln_user_id
,ln_resp_id
,ln_app_id
,NULL
,SYSDATE
,NULL
,NULL );

COMMIT;
END;

Service Contracts Query

Headers
=======
create or replace view service_contract_headers_v as
SELECT oah.contract_number contract_number
,oah.id contract_id
, (SELECT TO_CHAR(MAJOR_VERSION)
|| '.'
|| TO_CHAR(MINOR_VERSION)
FROM OKC_K_VERS_NUMBERS_V
WHERE CHR_ID=oah.id
) VERSION ,
oah.class_meaning Category ,
oah.sts_meaning Status ,
oah.short_description SHORT_DESCRIPTION,
opr.role ROLE ,
(SELECT PARTY_NAME
FROM HZ_PARTIES HP,
okc_k_party_roles_b OKP
WHERE HP.PARTY_ID=OKP.object1_id1
AND RLE_CODE =opr.rle_code
AND dnz_chr_id =oah.id
UNION
SELECT hou.name
FROM hr_operating_units hou,
okc_k_party_roles_b OKP
WHERE hou.organization_id =oKP.object1_id1
AND RLE_CODE =opr.rle_code
AND dnz_chr_id =oah.id
) party,
(SELECT cro_code
FROM OKC_CONTACTS OKC
WHERE dnz_chr_id=oah.id
AND cpl_id =opr.id
) contact_role,
(SELECT DECODE(P2.PARTY_NAME,NULL,'NO SALESREP',P2.PARTY_NAME)
FROM hz_relationships r,
hz_parties p3 ,
hz_parties p2 ,
OKC_CONTACTS OKC
WHERE p2.party_id = r.subject_id
AND r.relationship_code IN('CONTACT_OF', 'EMPLOYEE_OF', 'CONTACT')
AND r.content_source_type = 'USER_ENTERED'
AND p3.party_id = r.party_id
AND r.object_id =opr.object1_id1
AND P3.PARTY_ID =OKC.object1_id1
AND okc.dnz_chr_id =oah.id
UNION
SELECT NAME
FROM OKC_CONTACTS OKC,
jtf_rs_salesreps JTF
WHERE OKC.object1_id1 = jtf.salesrep_id
AND jtot_object1_code ='OKX_SALEPERS'
AND OKC.cpl_id =opr.id
AND dnz_chr_id =oah.id
) contact,
(SELECT P3.email_address
FROM hz_relationships r,
hz_parties p3 ,
OKC_CONTACTS OKC
WHERE 1 =1
AND r.relationship_code IN('CONTACT_OF', 'EMPLOYEE_OF', 'CONTACT')
AND r.content_source_type = 'USER_ENTERED'
AND p3.party_id = r.party_id
AND EXISTS
(SELECT '1'
FROM hz_org_contacts oc
WHERE oc.party_relationship_id = r.relationship_id
)
AND r.object_id =opr.object1_id1
AND P3.PARTY_ID =OKC.object1_id1
AND okc.dnz_chr_id=oah.id
AND cpl_id =opr.id
AND rownum =1
UNION
SELECT email_address
FROM OKC_CONTACTS OKC,
jtf_rs_salesreps JTF
WHERE OKC.object1_id1 = jtf.salesrep_id
AND jtot_object1_code ='OKX_SALEPERS'
AND OKC.cpl_id =opr.id
AND dnz_chr_id =oah.id
) email_address ,
(SELECT DECODE (hca.account_name,NULL,hp.party_name,hca.account_name)
||'/'
||hp.party_name --8368
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
hz_cust_accounts hca , --CUST_ACCOUNT_ID
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND hca.party_id =hp.party_id
AND hca.cust_account_id = hzca.cust_account_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) bill_to_account --"BILL TO Account/Party"
,( SELECT DECODE(hca.sales_channel_code,'GOVERNMENT','Yes','No')
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
hz_cust_accounts_all hca ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hca.cust_account_id = hzca.cust_account_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) GOVERNMENT
, (SELECT HCSU.location
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) bill_to_location ,
(SELECT hda.dqaddress1
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) bill_to_address1 ,
(SELECT hda.dqaddress2
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) bill_to_address2 ,
(SELECT hda.dqcity
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) bill_to_city ,
(SELECT hda.dqstate
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) bill_to_state ,
(SELECT DECODE(SUBSTR(hda.dqpostal_code,1,1),'0',hda.dqpostal_code,'0'||hda.dqpostal_code)
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) bill_to_zipcode ,
(SELECT hda.dqcountry --P.O. BOX 6641 ENGLEWOOD ARAPAHOE CO 80155-6641 United States
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) bill_to_country ,
(SELECT DECODE (hca.account_name,NULL,hp.party_name,hca.account_name)
||'/'
||hp.party_name --8368
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
hz_cust_accounts hca , --CUST_ACCOUNT_ID
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND hca.party_id =hp.party_id
AND hca.cust_account_id = hzca.cust_account_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) ship_to_account -- "SHIP TO Account/Party"
,
(SELECT HCSU.location
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) ship_to_location ,
(SELECT hda.dqaddress1
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) ship_to_address1 ,
(SELECT hda.dqaddress2
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) ship_to_address2 ,
(SELECT hda.dqcity
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) ship_to_city ,
(SELECT hda.dqstate
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) ship_to_state ,
(SELECT DECODE(SUBSTR(hda.dqpostal_code,1,1),'0',hda.dqpostal_code,'0'||hda.dqpostal_code)
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) ship_to_zipcode ,
(SELECT hda.dqcountry
FROM okc_rules_b OKCR ,
hz_cust_site_uses_all hcsu ,
hz_cust_acct_sites_all hzca,
HZ_DQM_AC_AS_V hda ,
hz_parties hp ,
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1 =hcsu.site_use_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
AND hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND hp.party_id =hda.dqparty_id
AND hda.dqcust_acct_site_id= hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =oah.id
) ship_to_country ,
(SELECT B.name
FROM OKC_RULES_B A,
qp_list_headers_all B
WHERE A.object1_id1 =B.list_header_id
AND A.jtot_object1_code='OKX_PRICE'
AND dnz_chr_id =oah.id
) price_list ,
oah.currency_code ,
oah.CUST_PO_NUMBER ,
(SELECT rule_information11
FROM OKC_RULES_B okr,okc_rule_groups_b okcg
WHERE okr.rule_information_category='SBG'
AND okcg.ID = okr.rgp_id
and okcg.chr_ID = oah.id
)ar_interface ,
(SELECT rule_information12
FROM OKC_RULES_B okr,okc_rule_groups_b okcg
WHERE rule_information_category='SBG'
AND okcg.ID = okr.rgp_id
and okcg.chr_ID = oah.id
)hold_billing ,
(SELECT rule_information1
FROM OKC_RULES_B okr,okc_rule_groups_b okcg
WHERE rule_information_category='CCR'
AND okcg.ID = okr.rgp_id
and okcg.chr_ID = oah.id
)credit_card_number ,
(SELECT rule_information2
FROM OKC_RULES_B okr,okc_rule_groups_b okcg
WHERE rule_information_category='CCR'
AND okcg.ID = okr.rgp_id
and okcg.chr_ID = oah.id
)expiry ,
oah.date_approved ,
oah.date_signed ,
oah.datetime_cancelled ,
oah.date_terminated ,
oah.date_renewed ,
rct.trx_number invoice_number,
rct.trx_date billed_date,
oah.creation_date ,
(select user_name from fnd_user fu
where fu.user_id =oah.created_by
) created_by,
oah.last_update_date ,
(select user_name from fnd_user fu
where fu.user_id =oah.last_updated_by
) last_updated_by
FROM OKS_AUTH_HEADERS_V oah,
OKC_K_PARTY_ROLES_V opr,
ra_customer_trx_all rct
WHERE 1 =1
AND oah.id = opr.chr_id
and rct.ct_reference=oah.contract_number
--and okl.chr_id=169117819153706617322098785615065897622
;


Lines
=====
create or replace view service_contract_lines_v as
select
okh.contract_number contract_number
,okh.id contract_id
,OKL.line_number line_number
,ols.name LINE_TYPE
,(select msi.segment1 from OKC_K_ITEMS oki
,mtl_system_items_b msi
where oki.object1_id1=msi.inventory_item_id
and to_char(msi.organization_id)=oki.object1_id2
and oki.dnz_chr_id=okl.chr_id
)service_item
,okl.sts_code line_status
,( SELECT hca.account_number --decode (hca.account_name,NULL,hp.party_name,hca.account_name),hca.account_number --8368
FROM okc_rules_b OKCR,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hzca,
hz_parties hp,
hz_cust_accounts hca, --CUST_ACCOUNT_ID
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1=hcsu.site_use_id
and hca.party_id=hp.party_id
and hca.cust_account_id= hzca.cust_account_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
and hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =okl.chr_id
) BILL_TO_ACCOUNT_NUMBER
/*
,( SELECT HCSU.location --decode (hca.account_name,NULL,hp.party_name,hca.account_name),hca.account_number --8368
FROM okc_rules_b OKCR,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hzca,
hz_parties hp,
hz_cust_accounts hca, --CUST_ACCOUNT_ID
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1=hcsu.site_use_id
and hca.party_id=hp.party_id
and hca.cust_account_id= hzca.cust_account_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_BILLTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
and hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =okl.chr_id
) BILL_TO_LOCATION
*/
,( SELECT hca.account_number --decode (hca.account_name,NULL,hp.party_name,hca.account_name),hca.account_number --8368
FROM okc_rules_b OKCR,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hzca,
hz_parties hp,
hz_cust_accounts hca, --CUST_ACCOUNT_ID
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1=hcsu.site_use_id
and hca.party_id=hp.party_id
and hca.cust_account_id= hzca.cust_account_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
and hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =okl.chr_id
) SHIP_TO_ACCOUNT_NUMBER
/*
,( SELECT HCSU.location --decode (hca.account_name,NULL,hp.party_name,hca.account_name),hca.account_number --8368
FROM okc_rules_b OKCR,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hzca,
hz_parties hp,
hz_cust_accounts hca, --CUST_ACCOUNT_ID
okc_rule_groups_b okcg
WHERE OKCR.OBJECT1_ID1=hcsu.site_use_id
and hca.party_id=hp.party_id
and hca.cust_account_id= hzca.cust_account_id
AND okcr.JTOT_OBJECT1_CODE ='OKX_SHIPTO' -- IN('OKX_SHIPTO','OKX_BILLTO')
and hcsu.cust_acct_site_id =hzca.cust_acct_site_id
AND okcg.ID = okcr.rgp_id
AND okcg.chr_id = okcr.dnz_chr_id
AND okcr.dnz_chr_id =okl.chr_id
) SHIP_TO_LOCATION
*/
,(select OKL.line_number||'.'||oklb.line_number from okc_k_lines_b oklb
WHERE 1=1 --okl.id= oki.cle_id
and oklb.price_level_ind='Y'
and oki.dnz_chr_id=okl.dnz_chr_id
and oklb.id=oki.cle_id
)sub_line_number
,(SELECT olsv.name FROM OKC_LINE_STYLES_v olsv
where olsv.id=(select oklb.lse_id from okc_k_lines_b oklb
WHERE 1=1 --okl.id= oki.cle_id
and oklb.price_level_ind='Y'
and oki.dnz_chr_id=okl.dnz_chr_id
and oklb.id=oki.cle_id)
)Level_name
,(select cii.serial_number from csi_item_instances cii
where cii.instance_id=oki.object1_id1
) serial_number
,(select oklb.start_date from okc_k_lines_b oklb
WHERE 1=1 --okl.id= oki.cle_id
and oklb.price_level_ind='Y'
and oki.dnz_chr_id=okl.dnz_chr_id
and oklb.id=oki.cle_id
)start_date
,(select oklb.end_date from okc_k_lines_b oklb
WHERE 1=1 --okl.id= oki.cle_id
and oklb.price_level_ind='Y'
and oki.dnz_chr_id=okl.dnz_chr_id
and oklb.id=oki.cle_id
)end_date
,TO_NUMBER(OKS_ENT_UTIL_PVT.get_duration_period(okh.START_DATE,
okh.END_DATE,'D')) DURATION
,SUBSTR(OKS_ENT_UTIL_PVT.get_duration_period(okh.START_DATE,
okh.END_DATE, 'P'), 1,25) PERIOD_CODE
,(select oklb.date_terminated from okc_k_lines_b oklb
WHERE 1=1 --okl.id= oki.cle_id
and oklb.price_level_ind='Y'
and oki.dnz_chr_id=okl.dnz_chr_id
and oklb.id=oki.cle_id
)date_terminated
,cii.external_reference
,msi.description external_ref_desc
,oks.name coverage_name
,oks.description coverage_desc
,oki.number_of_items qty
,oki.uom_code uom
,(select oklb.price_unit from okc_k_lines_b oklb
WHERE 1=1 --okl.id= oki.cle_id
and oklb.price_level_ind='Y'
and oki.dnz_chr_id=okl.dnz_chr_id
and oklb.id=oki.cle_id
)unit_price
,(select TO_char(oklb.price_negotiated,'999999999D99')
from okc_k_lines_b oklb
WHERE 1=1
and oklb.id=oki.cle_id
)extended_price
,(select TO_char(oklb.price_negotiated,'999999999D99')
from okc_k_lines_b oklb
WHERE 1=1
and oklb.id=oki.cle_id
)subtotal
,(SELECT ole.date_to_interface
FROM okc_rules_b okcr
,oks_level_elements ole
WHERE 1=1
and ole.rul_id = okcr.id
AND okcr.rule_information_category = 'SLL'
AND okcr.dnz_chr_id = okl.chr_id
and rownum=1
)interface_date
from
OKC_K_HEADERS_B okh
,okc_k_lines_b okl
,oks_coverages_v oks
,OKC_LINE_STYLES_v ols
,OKC_K_ITEMS oki
,mtl_system_items_b msi
,csi_item_instances cii
where 1=1
and okh.id=okl.chr_id
and ols.id=okl.lse_id
AND oks.dnz_chr_id = okl.dnz_chr_id
--AND oks.service_cle_id = okl.cle_id
and msi.inventory_item_id=cii.inventory_item_id
and cii.instance_id = oki.object1_id1
and oki.jtot_object1_code='OKX_CUSTPROD'
and to_char(msi.organization_id)=okh.inv_organization_id --fnd_profile.value('ORG_ID') --oki.object1_id2
and okl.sts_code='ACTIVE'
and oki.dnz_chr_id=okl.dnz_chr_id
--and okl.chr_id=169117819153706617322098785615065897622
;

Headers & Lines
===============
create or replace view service_contract_dtls_v as
select
sch.contract_number
,sch.contract_id
,sch.VERSION
,sch.Category
,sch.Status
,sch.SHORT_DESCRIPTION
,sch.ROLE
,sch.party
,sch.contact_role
,sch.contact
,sch.government
,sch.email_address
,sch.bill_to_account
,sch.bill_to_location
,sch.bill_to_address1
,sch.bill_to_address2
,sch.bill_to_city
,sch.bill_to_state
,sch.bill_to_zipcode
,sch.bill_to_country
,sch.ship_to_account
,sch.ship_to_location
,sch.ship_to_address1
,sch.ship_to_address2
,sch.ship_to_city
,sch.ship_to_state
,sch.ship_to_zipcode
,sch.ship_to_country
,sch.price_list
,sch.currency_code
,sch.cust_po_number
,sch.ar_interface
,sch.hold_billing
,sch.credit_card_number
,sch.date_approved
,sch.date_signed
,sch.datetime_cancelled
,sch.date_terminated
,sch.date_renewed
,sch.invoice_number
,sch.billed_date
,sch.creation_date
,sch.created_by
,sch.last_update_date
,sch.last_updated_by
,sch.expiry
,scl.line_number
,scl.LINE_TYPE
,scl.service_item
,scl.line_status
,scl.BILL_TO_ACCOUNT_NUMBER
--,scl.BILL_TO_LOCATION
,scl.SHIP_TO_ACCOUNT_NUMBER
--,scl.SHIP_TO_LOCATION
,scl.sub_line_number
,scl.Level_name
,scl.serial_number
,scl.start_date
,scl.end_date
,scl.DURATION
,scl.PERIOD_CODE
--,scl.date_terminated
,scl.external_reference
,scl.external_ref_desc
,scl.coverage_name
,scl.coverage_desc
--,scl.invoice_text1||invoice_text2 invoice_text
,scl.qty
,scl.uom
,scl.unit_price
,scl.extended_price
,scl.subtotal
,scl.interface_date
from service_contract_headers_v sch
,service_contract_lines_v scl
where 1=1
and scl.contract_id=sch.contract_id;

FNDLOAD Commands

1 - Printer Styles
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"

2 - Lookups
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="prod"
LOOKUP_TYPE="lookup name"

3 - Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=?COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME="prod" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"

4 - Key Flexfield Structures
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=?COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL? APPLICATION_SHORT_NAME="prod" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"

5 - Concurrent Programs
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="prod" CONCURRENT_PROGRAM_NAME="concurrent name"

6 - Value Sets
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"

7 - Value Sets with values
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"

8 - Profile Options
FNDLOAD apps/apps\@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="prod"

8 - Request Groups
FNDLOAD apps/apps\@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="prod"

10 - Request Sets
FNDLOAD apps/apps\@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET
APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set"

11 - Responsibilities
FNDLOAD apps/apps\@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"

12 - Menus
FNDLOAD apps/apps\@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name"

Data flow for Order-to-Cash cycle

1. Order Entry
This is first stage, When the order is entered in the system, it creates a record in order headers and Order Lines table.
• Enter header details: Once you enter details on the order header and save it or move it to lines, record goes to one table OE_ORDER_HEADERS_ALL FLOW_STATUS_CODE = ENTERED, BOOKED_FLAG = N), Primary key=HEADER_ID
o No record exist in any other table for this order till now.
• Enter Line details for this order: Enter different item numbers, quantity and other details in line tab. When the record gets saved, it goes to one table. Order header details will be linked with line details by order HEADER_ID. OE_ORDER_LINES_ALL (FLOW_STATUS_CODE = ENTERED, BOOKED_FLAG = N, OPEN_FLAG = Y) Primary key= LINE_ID

2.Order Booking
This is next stage, when Order is booked then the Flow status changed from Entered to Booked. At this stage, these below table get affected.
• OE_ORDER_HEADERS_ALL (FLOW_STATUS_CODE as BOOKED, BOOKED_FLAG updated to Y)
• OE_ORDER_LINES_ALL (FLOW_STATUS_CODE as AWAITING_SHIPPING, BOOKED_FLAG updated Y)
• WSH_DELIVERY_DETAILS (DELIVERY_DETAIL_ID is assigned here, RELEASED_STATUS ‘R’ ready to release, LINE_ID comes as SOURCE_LINE_ID)
• WSH_DELIVERY_ASSIGNMENTS (DELIVERY_ASSIGNMENT_ID is assigned for DELIVERY_DETAIL_ID present in WSH_DELIVERY_DETAILS, DELIVERY_ID remains blank till this stage)
*In shipping transaction form order status remains "Ready to Release".
At the same time, Demand interface program runs in background And insert into inventory tables MTL_DEMAND, here LINE_ID come as a reference in DEMAND_SOURCE_LINE

3. Reservation
This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved. Once this program get successfully get completed, the MTL_DEMAND and MTL_RESERVATIONS table get updated. LINE_ID gets updated in DEMAND_SOURCE_LINE_ID in both the tables.

4. Pick Release
Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order.
Pick release can be done from 'Release Sales Order' form or 'Pick release SRS' program can be scheduled in background. In both of these cases all lines of the order gets pick released depending on the Picking rule used. If specific line/s needs to be pick release it can be done from 'Shipping Transaction form. For this case Pick Release is done from 'Release Sales Order' form with Pick Confirm=NO.
Once pick release is done these are the tables get affected:
• If step 3 is not done then MTL_RESERVATIONS gets updated now.
• WSH_NEW_DELIVERIES (one record gets inserted with SOURCE_HEADER_ID= order header ID, STATUS_CODE=OP =>open)
• WSH_DELIVERY_ASSIGNMENTS (DELIVERY_ID gets assigned which comes from WSH_NEW_DELIVERIES)
• WSH_DELIVERY_DETAILS (RELEASED_STATUS ‘S’ ‘submitted for release’)
• MTL_TXN_REQUEST_HEADERS
• MTL_TXN_REQUEST_LINES (LINE_ID goes as TXN_SOURCE_LINE_ID)
• (move order tables. Here request is generated to move item from Source (RM or FG) sub-inventory to staging sub-inventory)
• MTL_MATERIAL_TRANSACTIONS_TEMP (link to above tables through MOVE_ORDER_HEADER_ID/LINE_ID, this table holds the record temporally)
• MTL_SERIAL_NUMBERS_TEMP (if item is serial controlled at receipt then record goes in this table)
• MTL_SERIAL_NUMBERS (enter value in GROUP_MARK_ID )
*In shipping transaction form order status remains "Released to Warehouse" and all the material still remains in source sub-inventory. We need to do Move Order Transaction for this order. Till this no material transaction has been posted to MTL_MATERIAL_TRANSACTIONS

5.Pick Confirm/ Move Order Transaction
Items are transferred from source sub-inventory to staging Sub-inventory. Here material transaction occurs.
Order line status becomes 'Picked' on Sales Order and 'Staged/Pick Confirmed' on Shipping Transaction Form.
• MTL_MATERIAL_TRANSACTIONS_TEMP (Record gets deleted from here and gets posted to MTL_MATERIAL_TRANSACTIONS)
• OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘PICKED’ )
• MTL_MATERIAL_TRANSACTIONS (LINE_ID goes as TXN_SOURCE_LINE_ID)
• MTL_TRANSACTION_ACCOUNTS
• WSH_DELIVERY_DETAILS (RELEASED_STATUS becomes ‘Y’ => ‘Released’ )
• WSH_DELIVERY_ASSIGNMENTS
• MTL_ONHAND_QUANTITIES
• MTL_SERIAL_NUMBERS_TEMP (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')
• MTL_SERIAL_NUMBERS (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')
* This step can be eliminated if we set Pick Confirm=YES at the time of Pick Release

6.Ship Confirm
Here ship confirm interface program runs in background. Data removed from WSH_NEW_DELIVERIES.

The items on the delivery gets shipped to customer at this stage.
• OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘shipped’)
• WSH_DELIVERY_DETAILS (RELEASED_STATUS ‘C’ ‘Shipped’, SERIAL_NUMBER if quantity is ONE)
• WSH_SERIAL_NUMBERS (records gets inserted with the DELIVERY_DETAIL_ID reference, only in case of shipped quantity is two or more)
• MTL_TRANSACTION_INTERFACE
• MTL_MATERIAL_TRANSACTIONS (linked through Transaction source header id)
• MTL_TRANSACTION_ACCOUNTS
• Data deleted from MTL_DEMAND, MTL_RESERVATIONS
• Item deducted from MTL_ONHAND_QUANTITIES
• MTL_SERIAL_NUMBERS_TEMP (records gets deleted from this table)
• MTL_SERIAL_NUMBERS (Serial number stauts gets updated CURRENT_STATUS=4 , 'Issued out of store')

7.Enter Invoice
After shipping the order the order lines gets eligible to get transfered to RA_INTERFACE_LINES_ALL. Workflow background engine picks those records and post it to RA_INTERFACE_LINES_ALL. This is also called Receivables interface, that mean information moved to accounting area for invoicing details. Invoicing workflow activity transfers shipped item information to Oracle Receivables. At the same time records also goes in the table RA_INTERFACE_SALESCREDITS_ALL which hold details of sales credit for the particular order.
RA_INTERFACE_LINES_ALL (interface table into which the data is transferred from order management) Then Autoinvoice program imports data from this table which get affected into this stage are receivables base table. At the same time records goes in
RA_CUSTOMER_TRX_ALL (CUST_TRX_ID is primary key to link it to TRX_LINES table and TRX_NUMBER is the invoice number)
RA_CUSTOMER_TRX_LINES_ALL (LINE_ATTRIBUTE_1 and LINE_ATTRIBUTE_6 are linked to order number and LINE_ID of the orders)

8.Complete Line
In this stage order line level table get updated with Flow status and open flag.
OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘shipped’, OPEN_FLAG “N”)

9.Close Order
This is last step of Order Processing. In this stage only OE_ORDER_LINES_ALL table get updated. These are the table get affected in this step.

OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘closed’, OPEN_FLAG “N”)
OE_ORDER_HEADERS_ALL

AR - Auto Invoice Interface Overview

Auto Invoice is a program that can be used to import and validate transaction data from other financial systems from which one can create invoices, debit memos, credit memos, and on-account credits. It rejects transactions with invalid information to insure the integrity of the data. This fits well with in Oracle ERP or to integrate with any third party application.

Top 10 reasons for using Auto Invoice
1. Powerful Interface Tool
2. Supports Oracle & Non-Oracle Systems
3. Import Large Amount of Data
4. Calculate or Import Tax
5. Group Lines & Invoices
6. Online Error Correction
7. Lines Validation
8. Derive GL Date
9. Import Flex fields
10. Import or Derive Accounting Info

What is inside Auto Invoice?
Auto Invoice is a program set consists of 3 main programs. Each program will have unique nature of work to do and they are called internally except Purge program whose execution is derived on the setup otherwise ready to execute stand alone.
Master (RAXMTR)
Import (RAXTRX)
Purge (RAXDEL)

1. Auto Invoice Master Program RAXMTR
Selects and marks records in the interface tables to be processed based on the parameters the user entered and then calls the Auto Invoice Import program. Auto Invoice Master Program has no report output.
•Gathers statistics, it means it gathers the stats on interface tables and set the stats on certain indices on interface tables
•Marks interface records for processing by marking Request_Id
•Submits multiple workers for Parallel Processing by creating instances for request.

2. Auto Invoice Import Program validates the selected record and creates transaction if it passes validation. Any record that fails validation is left in the interface table with an error code. Depending on the setup, related records may be rejected as well. This program has an output file called Auto Invoice Execution report, which you can view by clicking the View Report button in the Requests window. Working of Auto Invoice, Validates data, Inserts records, Deletes interface data only when system option purge set to ‘Y’

3. Auto Invoice Purge Program Deletes records from the interface tables. If you set the Purge Interface Table system option to No in Define System Option window, Auto Invoice does not delete processed records from the interface tables after each run, and we must submit Auto Invoice Purge Program periodically to clean up the interface tables. This program only deletes transaction lines that have been successfully imported.
Deletes all rows where interface_status =‘P’
• RA_INTERFACE_LINES_ALL
• RA_INTERFACE_DISTRIBUTIONS_ALL
• RA_INTERFACE_SALESCREDITS_ALL

Oracle Receivables Auto Invoice program will be used to import and validate Invoices. A custom feeder program is required to transfer data from the Advantage extract files and populate the Auto Invoice interface tables (RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL). If there is a need to run populate sales credit into RA_INTERFACE_SALESCREDITS_ALL table. When run, Auto Invoice produces the Auto Invoice Execution Report and the Auto Invoice Validation Report. Any entries which failed validation can be reviewed in Oracle Receivables’ Auto Invoice Interface Exceptions window. Depending on the error, changes may need to be made in Receivables, the feeder program or the imported records in the interface tables.

How Auto invoice Execution works?
Normally, Auto Invoice can be divided into three major phases,
Pre-grouping: here validates all of the line level data takes place,
Grouping: groups lines based on the grouping rules and validates header level data,
Transfer: validates information that exists in Receivables tables

What happen when Auto Invoice run?
Once the Auto invoice Program gets called, the following activity takes place is part of execution process. This can be analyzed by debug options.
Line, accounting, and sales credit information for each line populates 3 interface tables
Lines are ordered and grouped
Tax is calculated
GL date is determined
GL accounts are assigned using Auto Accounting
Tax, freight, commitments, and credit memos are linked to transaction lines
All transactions are batched
Validated lines are used to create the transaction

How Data is flowing?
Select, insert and update and delete take place on certain tables once it is logged out.

Selects
– RA_INTERFACE_LINES_ALL
– RA_INTERFACE_DISTRIBUTIONS_ALL
– RA_INTERFACE_SALESCREDITS_ALL
Updates/Insert
– RA_INTERFACE_ERRORS_ALL
– RA_CUSTOMER_TRX_ALL
– RA_CUSTOMER_TRX_LINES_ALL
– AR_PAYMENT_SCHEDULES_ALL
– AR_RECEIVABLE_APPLICATIONS_ALL

Inserts
– RA_INTERFACE_ERRORS_ALL

Auto Invoice Exception Handling
Records that fail validation are called ‘Exceptions’. Exceptions stay in Interface Tables which is RA_INTERFACE_ERRORS_ALL. Errors can be corrected in the Exception Handling window. Once corrections are made, Auto invoice must be resubmitted. Records that pass validation get transferred to Receivables tables

Auto Invoice Exception Handling Windows
-Interface Exception window displays exception messages associated with all invalid records
-Interface Lines window displays records that fail validation, provides an error message and can be used to correct the errors
-The Line Errors windows displays errors associated with a specific line, and can only be opened from Interface Lines window
-Interface Exceptions window displays Interface Id, Exception Type, Error Message and Invalid Value associated to the error
-Data cannot be edited in this window, but error can be viewed and corrected by clicking the Details button
-Error Message and Column name with invalid data are displayed in the Message column, and the invalid value that needs to be corrected is displayed in the Invalid Value column

Interface Table:
RA_INTERFACE_LINES_ALL
RA_INTERFACE_SALESCREDITS_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL

Validations:
1- AR Transaction Type Validation: Check if the Transaction type provided in data file is defined in AR transaction types (RA_CUST_TRX_TYPES_ALL)
2- Transaction Batch Source Validation: Check if the source provided in data file is defined in AR transaction Batch source (RA_BATCH_SOURCES_ALL).
3- Invoice Currency Validation: Check if the currency provided in data file is defined in AR Currency (FND_CURRENCIES).
4- Customer Validation: Check if the Bill to Customer Number, Ship to Customer Number, Bill to Custom Location, Ship to Customer Location provided in the data file is defined in AR Customer (RA_CUSTOMERS).
5- Primary Sales Representative Validation: Sales representative number to be hardcode to “-3” for “No Sales Credit.”
6- Term Name: Check if the Term name provided in the data file is defined in Payment terms (RA_TERMS)
7- Validate Sales Credit Type: Sales Credit to hardcode to “Quota Sales Credit”
8- Inventory Item Validation: Check if the Item provided in data file is defined in Inventory Items (MTL_SYSTEM_ITEMS).
9- Unit of Measurement validation: Check if the UOM provided is defined in MTL_UNITS_OF_MEASURE Table
10- Invoice Tax Code Validation: Check if the Tax Code provided in data file is defined in AR_VAT_TAX_ALL_B Table.
11- Invoice GL Date Validation: Check if the GL Data of provided invoices is in open period. Used ARP_UTIL.IS_GL_DATE_VALID API to validate.
Following are the base tables
hz_parties
hz_cust_accounts
hz_cust_acct_sites_all
hz_cust_sit_use_all
hz_party_sites
hz_locations
hz_party_site_uses
hz_customer_profiles
hz_organization_profiles
hz_person_profiles

Points To Remember:
Use the Autoinvoice Execution report to review the Transactions that were successfully imported.

Use the Autoinvoice Validation report to review the Lines that have failed different phases of validation and the error messages associated with these Lines.

Understand “Drop Shipment” in Order Management

Order Management allows you to enter drop-ship sales orders as well as standard sales orders.
It means you can receive orders for items that you do not stock or for which you lack sufficient inventory, and have a supplier provide the items directly to your customer.


The best can be described as:

These are the following activity takes place when you have drop shipment
• Supplier
o Warehouse Item
o Ship order
o Shipment notification
• Order Entry
o Enter customer
o Enter order
o Demand order (optional)
o Cancel order (optional)
o Close order
• Purchasing
o Create and send Purchase Order
o Enter shipment notification in system
• Receivables
o Create invoice
o Collection of payment
o Receipt
hence, drop ship order items ship directly from a supplier to the customer of the order processing company. A purchase requisition, then a purchase order, is generated to notify the supplier of the requirement. After the supplier ships the order, it notifies Purchasing to enter this information in the Purchasing module.
What are the advantages of Drop Shipment Orders?
These are the benefits:
• No inventory is required
• Reduced order fulfillment processing costs
• Reduced flow times
• Elimination of losses on non-sellable goods
• Elimination of packing and shipping costs
• Reduced inventory space requirements
• Reduced shipping time to your customer
• Allows you to offer a variety of products to your customers
How to understand the dataflow for Drop shipment Orders?
To understand, here are the processes divided in sub process and the underline activity is highlighted here:









Here are the Details as per Mark
1.Order Entry
Here the activity is entering process where oe_order_headers_all (flow_status_code as entered) oe_order_lines_all . The order is booked as DROP SHIP
2. Order Booking
3. The Purchase Release program passes information about eligible drop-ship order lines to Oracle Purchasing.The interface table which gets populated is
po_requisitions_interface_all
4. After Purchase Release has completed successfully, run Requisition Import in Oracle Purchasing to generate purchase requisitions for the processed order lines. The Requisition Import program reads the table po_requisitions_interface_all validates your data, derives or defaults additional information and writes an error message for every validation that fails into the po_interface_errors table.The validated data is then inserted into the requisition base tables po_requisition_headers_all,po_requisition_lines_all,po_requisition_distributions_all.Then use autocreate PO fuctionality to create purchase orders and then perform receipts against these purchase orders
7. After the goods are successfully received invoices for vendors are created in accounts payables as in normal purchase orders.
8. Invoices are generated for customers In account receivables.
9. oe_order_lines_all (flow_status_code ’shipped’, open_flag “N”)
10. oe_order_lines_all (flow_status_code ‘closed’, open_flag “N”)

Query to find an PO details

set lines 150
set pages 150

execute fnd_client_info.set_org_context('204');

col PO_Number_Release form a16
col Vendor form a28
col Description form a24
col Unit_Price form a18
col unit form a8
SELECT
decode(por.release_num,NULL, poh.segment1, poh.segment1 ||'-'|| por.release_num) PO_Number_Release
, pol.line_num Line
, pov.vendor_name Vendor
, pol.item_revision Rev
, pol.item_description Description
, pll.shipment_num
, pod.distribution_num Distribution
, decode(plt.order_type_lookup_code, 'AMOUNT',NULL,pll.price_override) Unit_Price
, pll.promised_date Promised_Date
, pol.unit_meas_lookup_code Unit
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_ordered,
'FIXED PRICE', POD.amount_ordered,
POD.quantity_ordered) Quantity_Amount_Ordered
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_billed,
'FIXED PRICE', POD.amount_billed,
POD.quantity_billed) Quantity_Amount_Billed
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_delivered,
'FIXED PRICE', POD.amount_delivered,
POD.quantity_delivered) Qty_Amount_Delivered
, DECODE (POL.order_type_lookup_code,
'RATE', (NVL(POD.amount_ordered, 0) - NVL(POD.amount_billed, 0)) /
DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
'FIXED PRICE', (NVL(POD.amount_ordered, 0) - NVL(POD.amount_billed, 0)) /
DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
(NVL(POD.quantity_ordered, 0) - NVL(POD.quantity_billed, 0)) /
DECODE (NVL(POD.quantity_ordered, 0), 0, 1, POD.quantity_ordered)) * 100 Percent_Unbilled
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_ordered - NVL(POD.amount_cancelled, 0)- NVL(POD.amount_billed, 0),
'FIXED PRICE', POD.amount_ordered - NVL(POD.amount_cancelled, 0)- NVL(POD.amount_billed, 0),
(POD.quantity_ordered - NVL(POD.quantity_cancelled, 0)- NVL(POD.quantity_billed, 0)) * PLL.price_override) C_AMOUNT_OPEN_INV
, poh.po_header_id
, pol.po_line_id
, por.release_num
, poh.currency_code C_CURRENCY
, nvl(por.po_release_id,-1) release_id
FROM po_distributions pod
, mtl_system_items msi
, po_line_locations pll
, po_lines pol
, po_releases por
, po_headers poh
, po_vendors pov
, financials_system_parameters fsp
, po_line_types plt
WHERE poh.segment1='804'
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pll.po_line_id
AND pll.line_location_id = pod.line_location_id
AND pol.item_id = msi.inventory_item_id (+)
AND msi.organization_id = fsp.inventory_organization_id
AND poh.vendor_id = pov.vendor_id (+)
AND pll.po_release_id = por.po_release_id (+)
AND pol.line_type_id = plt.line_type_id
AND pll.shipment_type in ('STANDARD','BLANKET','SCHEDULED')
AND nvl(pol.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(pll.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(poh.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(por.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(poh.cancel_flag,'N') = 'N'
AND nvl(por.cancel_flag,'N') = 'N'
AND nvl(pol.cancel_flag,'N') = 'N'
AND nvl(pll.cancel_flag,'N') = 'N'
ORDER BY pll.line_location_id;

Query to find Requisition details info

set lines 150
set pages 150

execute fnd_client_info.set_org_context('204');
col Justification form a24
col Item_Description form a36
col Source form a56
col Source_Type form a12
col Requestor form a20
col Line_Type form a12
col Item form a16

SELECT prl.line_num Line
, plt.line_type Line_Type
, prl.item_id prl_item_id
, msi.segment1 Item
, prl.item_revision Rev
, prl.need_by_date Need_By_Date
, prl.unit_meas_lookup_code Unit
, round(prl.quantity,2) Quantity_Amount
, prl.unit_price Unit_Price
, DECODE (PRL.order_type_lookup_code, /* */
'FIXED PRICE', PRL.amount,
'RATE', PRL.amount,
NVL(PRL.quantity, 1) * PRL.unit_price) C_AMOUNT
, ppf.full_name Requestor
, plc.displayed_field Source_Type
, decode(prl.source_type_code,'INVENTORY',ood.organization_name||' - '||prl.source_subinventory,'VENDOR',prh.segment1||' - '||prl.suggested_vendor_name||' - '||prl.suggested_vendor_location||' - '||prl.suggested_vendor_contact||' - '||prl.suggested_buyer_id,null) Source
, prl.item_description Item_Description
, prd.req_line_quantity Distributions
, prl.justification Justification
, prl.requisition_header_id
, prl.requisition_line_id
FROM po_requisition_headers prh
, po_requisition_lines prl
, po_req_distributions prd
, po_line_types plt
, per_people_f ppf
, org_organization_definitions ood
, po_lookup_codes plc
, mtl_system_items msi
, mtl_categories mca
, gl_code_combinations gcc, financials_system_parameters fsp
, po_system_parameters psp
WHERE prh.segment1 = '1713'
AND prl.requisition_line_id = prd.requisition_line_id
AND prl.requisition_header_id = prh.requisition_header_id
AND prl.line_type_id = plt.line_type_id
AND prl.to_person_id = ppf.person_id (+)
AND prl.source_organization_id = ood.organization_id(+)
AND plc.lookup_type = 'REQUISITION SOURCE TYPE'
AND plc.lookup_code = prl.source_type_code
AND nvl(ppf.business_group_id, 0) = (select nvl(max(fsp.business_group_id),0)
from financials_system_parameters fsp)
AND trunc(sysdate)
BETWEEN nvl(ppf.effective_start_date, trunc(sysdate))
AND nvl(ppf.effective_end_date, trunc(sysdate))
AND prl.item_id = msi.inventory_item_id(+)
AND msi.organization_id = 204
AND prl.category_id = mca.category_id
AND prd.code_combination_id = gcc.code_combination_id
AND nvl(prl.modified_by_agent_flag,'N') = 'N'
AND nvl(prl.cancel_flag,'N') != 'Y'
AND nvl(prl.closed_code,'OPEN') != 'FINALLY CLOSED'
ORDER BY prl.line_num;

Query to find Requisition header info

set lines 150
set pages 150

execute fnd_client_info.set_org_context('204');

col Description form a40
col Req_type form a26
col type_lookup_code form a16
col PREPARER form a30
col APPROVER form a30
col NOTE_TO_APPROVER form a40

SELECT prh.segment1 Requisition
, psp.manual_req_num_type req_num_type
, ppf.full_name Preparer
, prh.creation_date Creation_Date
, prh.type_lookup_code
, ppf1.full_name Approver
, t.type_name Req_type
, prh.description Description
, pah.note Note_To_Approver
, prh.requisition_header_id Req_header
FROM po_requisition_headers prh
, per_people_f ppf1
, per_people_f ppf
, po_action_history pah
, po_system_parameters psp
, PO_DOCUMENT_TYPES_ALL_TL T
, PO_DOCUMENT_TYPES_ALL_B B
WHERE prh.REQUISITION_HEADER_ID=11675
and NVL(PRH.contractor_requisition_flag, 'N') <> 'Y'
AND prh.preparer_id = ppf.person_id
AND nvl(ppf.business_group_id, 0) = (select nvl(max(fsp.business_group_id), 0)
from financials_system_parameters fsp)
AND nvl(pah.action_code,'SUBMIT') in ('SUBMIT', 'FORWARD', 'REJECT', 'APPROVE',
'APPROVE AND RESERVE', 'RESERVE', 'ACCEPT','RETURN')
--AND prh.segment1 = P_req_num_from
AND EXISTS (SELECT null
FROM po_requisition_lines prl
WHERE prl.requisition_header_id = prh.requisition_header_id
AND nvl(prl.modified_by_agent_flag,'N') = 'N'
AND nvl(prl.closed_code,'OPEN') != 'FINALLY CLOSED')
AND pah.object_id = prh.requisition_header_id
AND pah.employee_id = ppf1.person_id
AND pah.object_type_code = 'REQUISITION'
AND pah.object_sub_type_code = prh.type_lookup_code
AND pah.sequence_num =
(SELECT max(sequence_num)
FROM po_action_history pah
WHERE pah.object_id = prh.requisition_header_id
AND pah.object_type_code = 'REQUISITION'
AND pah.object_sub_type_code = prh.type_lookup_code)
and B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE
AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE
AND b.document_type_code = 'REQUISITION'
AND b.document_subtype = prh.type_lookup_code
AND NVL(B.ORG_ID, -99) = NVL(T.ORG_ID, -99)
AND NVL(B.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1 ,1),' ',
NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
= NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
AND T.LANGUAGE = USERENV('LANG');

Query to find delivery leg and pick up stop info

SELECT wt.trip_id,
wt.name,
wt.STATUS_CODE,
wt.VEHICLE_ITEM_ID,
wt.VEHICLE_NUMBER,
wt.CARRIER_ID,
wt.SHIP_METHOD_CODE,
wts.STOP_ID,
wts.STOP_LOCATION_ID,
wts.STATUS_CODE,
wts.STOP_SEQUENCE_NUMBER,
wts.PLANNED_ARRIVAL_DATE,
wts.PLANNED_DEPARTURE_DATE,
wts.ACTUAL_ARRIVAL_DATE,
wts.ACTUAL_DEPARTURE_DATE,
wts.DEPARTURE_NET_WEIGHT,
wts.WEIGHT_UOM_CODE,
wdl.DELIVERY_LEG_ID,
wdl.DELIVERY_ID,
wdl.PICK_UP_STOP_ID,
wdl.DROP_OFF_STOP_ID,
wdl.SEQUENCE_NUMBER,
wdl.LOADING_ORDER_FLAG,
wdl.SHIPPER_TITLE,
wdl.SHIPPER_PHONE
FROM wsh_trips wt
,wsh_trip_stops wts
,wsh_delivery_legs wdl
WHERE wdl.delivery_id =12814
AND wts.stop_id = wdl.pick_up_stop_id
AND wts.trip_id = wt.trip_id;

Query to find Bill of Lading info of the Delivery

select
wnd.delivery_id delivery_id,
wdi.sequence_number bol_number,
wdi.bol_notify_party,
wdi.port_of_loading,
wdi.port_of_discharge,
wnd.WAYBILL waybill,
wnd.GROSS_WEIGHT gross_weight,
wnd.WEIGHT_UOM_CODE uom,
wnd.status_code
from wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_document_instances wdi
where wnd.delivery_id =12784
and wnd.delivery_id = wdl.delivery_id (+)
and wdi.entity_id (+) = wdl.delivery_leg_id
AND wdi.entity_name (+) = 'WSH_DELIVERY_LEGS'
AND wdi.document_type (+) = 'BOL'
AND wdi.status (+) <> 'CANCELLED';

Query to find out Move order line details

SELECT
wnd.delivery_id,
wnd.name delivery_name,
wnd.initial_pickup_location_id,
mtrh.request_number mo_number,
mtrl.line_number mo_line_number,
mtrl.line_id mo_line_id,
mtrl.from_subinventory_code,
mtrl.to_subinventory_code,
mtrl.lot_number,
mtrl.serial_number_start,
mtrl.serial_number_end,
mtrl.uom_code,
mtrl.quantity,
mtrl.quantity_delivered,
mtrl.quantity_detailed,
wdd.source_header_number so_order_number,
oola.line_number so_line_number,
wdd.source_header_id so_header_id,
wdd.source_line_id so_line_id,
wdd.shipping_instructions,
wdd.inventory_item_id,
wdd.requested_quantity_uom,
msi.description item_description,
msi.revision_qty_control_code ,
wdd.ship_method_code carrier,
wdd.shipment_priority_code priority,
wdd.organization_id,
wdd.released_status,
wdd.source_code
FROM mtl_system_items_vl msi,
oe_order_lines_all oola,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh,
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
WHERE wnd.delivery_id =18910
AND wda.delivery_id = wnd.delivery_id(+)
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.move_order_line_id = mtrl.line_id
AND mtrl.header_id = mtrh.header_id
AND wdd.inventory_item_id = msi.inventory_item_id(+)
AND wdd.organization_id = msi.organization_id(+)
AND wdd.source_line_id = oola.line_id
AND wdd.source_header_id = oola.header_id;

Query to find freight related info of order viz: freight carrier, ship method and service level

col shipping_method_code form a32
col carrier_name form a24

select
h.order_number,
h.shipping_method_code,
wc.carrier_name,
wcsm.SERVICE_LEVEL ,
wcsm.freight_code
from
wsh_carrier_ship_methods_v wcsm,
wsh_carriers_v wc,
oe_order_headers_all h
where h.order_number= 14463
and h.org_id = 204
and h.shipping_method_code = wcsm.ship_method_code(+)
and nvl(wcsm.organization_id(+),0) = 204 --Master Organization
and wcsm.freight_code = wc.freight_code(+)
order by h.order_number;

Query to find freight charges on order lines

col charge_name form a24
col source_system_code form a12

select
HEADER_ID ,
LINE_ID ,
CHARGE_ID ,
CHARGE_NAME ,
CHARGE_AMOUNT ,
CURRENCY_CODE ,
INVOICED_FLAG ,
INTERCO_INVOICED_FLAG ,
ORG_ID ,
SOURCE_SYSTEM_CODE ,
ESTIMATED_FLAG ,
INVOICED_AMOUNT
from OE_CHARGE_LINES_V
where header_id=
(select header_id
from oe_order_headers_all
where order_number=14463)
order by line_id
/
a: The OE_CHARGE_LINES_V view is based on oe_price_adjustments, oe_order_headers_all and oe_order_lines_all for FREIGHT CHARGES.

Query to find price discounts and surcharges on order lines

col list_line_type_code form a12
col arithmetic_operator form a12

Select h.order_number,
l.line_number,
pa.list_line_type_code,
pa.arithmetic_operator,
pa.operand,
DECODE(PA.MODIFIER_LEVEL_CODE,'ORDER',
L.UNIT_LIST_PRICE*L.ORDERED_QUANTITY *PA.OPERAND * SIGN(PA.ADJUSTED_AMOUNT)/100,
(PA.ADJUSTED_AMOUNT* NVL(L.ORDERED_QUANTITY,0) )) DISCOUNT_AMT
From
qp_list_headers_vl lh,
oe_price_adjustments pa,
oe_order_lines_all l,
oe_order_headers_all h
Where h.order_number = 14463
and h.header_id = l.header_id
and h.org_id = l.org_id
and h.header_id = pa.header_id
and l.line_id = pa.line_id(+)
and pa.list_header_id = lh.list_header_id
AND ( PA.LIST_LINE_TYPE_CODE = 'DIS'
OR PA.LIST_LINE_TYPE_CODE = 'SUR'
OR PA.LIST_LINE_TYPE_CODE = 'PBH' )
AND PA.APPLIED_FLAG='Y'
AND NOT EXISTS
(SELECT 'X'
FROM OE_PRICE_ADJ_ASSOCS PAS,
OE_PRICE_ADJUSTMENTS PA1
WHERE PAS.RLTD_PRICE_ADJ_ID =
PA.PRICE_ADJUSTMENT_ID
AND PA1.PRICE_ADJUSTMENT_ID=
PAS.PRICE_ADJUSTMENT_ID
AND PA1.LIST_LINE_TYPE_CODE ='PBH')
Order by l.line_id
/
a: Qp_list_headers_vl is view based on qp_list_headers_b and qp_list_headers_tl tables.

Sample Code for oe_order_pub.Process_Order

Got sample code to create Sales Order in Oracle Order Management .Please note that this is not my code , one of my friend has forwaded this .But I have tested it throughly and succesfully created Sales Orders.

create or replace procedure createsalesorder
(p_org_id NUMBER,
p_user_id NUMBER,
p_resp_id NUMBER,
p_appl_id NUMBER,
p_order_type_id NUMBER,
p_sold_to_org_id NUMBER,
p_ship_to_org_id NUMBER,
p_price_list_id NUMBER,
p_curr_code VARCHAR2,
p_flow_status_code VARCHAR2,
p_po_num VARCHAR2,
p_order_source_id NUMBER,
p_inventory_item_id NUMBER,
p_ordered_quantity NUMBER,
p_tax_code VARCHAR2)
IS
l_api_version_number NUMBER := 1;

l_return_status VARCHAR2(2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
/*****************PARAMETERS****************************************************/
l_debug_level number := 1; -- OM DEBUG LEVEL (MAX 5)
l_org number := p_org_id;--204; -- OPERATING UNIT
l_user number := p_user_id;--1318; -- USER
l_resp number := p_resp_id;--21623; -- RESPONSIBLILTY
l_appl number := p_appl_id;--660; -- ORDER MANAGEMENT
/***INPUT VARIABLES FOR PROCESS_ORDER API*************************/
l_header_rec oe_order_pub.header_rec_type;
l_line_tbl oe_order_pub.line_tbl_type;
l_action_request_tbl oe_order_pub.Request_Tbl_Type;
/***OUT VARIABLES FOR PROCESS_ORDER API***************************/
l_header_rec_out oe_order_pub.header_rec_type;
l_header_val_rec_out oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
l_line_tbl_out oe_order_pub.line_tbl_type;
l_line_val_tbl_out oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out oe_order_pub.request_tbl_type;
l_msg_index NUMBER;
l_data VARCHAR2(2000);
l_loop_count NUMBER;
l_debug_file VARCHAR2(200);
-- book API vars

b_return_status VARCHAR2(200);
b_msg_count NUMBER;
b_msg_data VARCHAR2(2000);
BEGIN
dbms_application_info.set_client_info(l_org);
/*****************INITIALIZE DEBUG INFO*************************************/
if (l_debug_level > 0) then
l_debug_file := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel(l_debug_level);
Oe_Msg_Pub.initialize;
end if;
/*****************INITIALIZE ENVIRONMENT*************************************/
fnd_global.apps_initialize(l_user, l_resp, l_appl); -- pass in user_id, responsibility_id, and application_id
/*****************INITIALIZE HEADER RECORD******************************/
l_header_rec := oe_order_pub.G_MISS_HEADER_REC;
/***********POPULATE REQUIRED ATTRIBUTES **********************************/
l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
l_header_rec.order_type_id := p_order_type_id;--1430;
l_header_rec.sold_to_org_id := p_sold_to_org_id;--1006;
l_header_rec.ship_to_org_id := p_ship_to_org_id;--1026;
l_header_rec.price_list_id := p_price_list_id;--1000;
l_header_rec.pricing_date := SYSDATE;
l_header_rec.transactional_curr_code := p_curr_code;--'USD';
l_header_rec.flow_status_code := p_flow_status_code;--'ENTERED';
l_header_rec.cust_po_number := p_po_num;--'06112009-08';
l_header_rec.order_source_id := p_order_source_id;--0 ;
--l_header_rec.attribute1 := 'ABC';
/*******INITIALIZE ACTION REQUEST RECORD*************************************/
l_action_request_tbl(1) := oe_order_pub.G_MISS_REQUEST_REC;
l_action_request_tbl(1).request_type := oe_globals.g_book_order;
l_action_request_tbl(1).entity_code := oe_globals.g_entity_header;
/*****************INITIALIZE LINE RECORD********************************/
l_line_tbl(1) := oe_order_pub.G_MISS_LINE_REC;
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;
l_line_tbl(1).inventory_item_id := p_inventory_item_id;--149 ;
l_line_tbl(1).ordered_quantity := p_ordered_quantity;--1;
l_line_tbl(1).ship_to_org_id := p_ship_to_org_id;--1026 ;
l_line_tbl(1).tax_code := p_tax_code;--'Location' ;
/*****************CALLTO PROCESS ORDER API*********************************/
dbms_output.put_line('Calling API');
oe_order_pub.Process_Order( p_api_version_number => l_api_version_number,
p_header_rec => l_header_rec,
p_line_tbl => l_line_tbl,
p_action_request_tbl => l_action_request_tbl,
--OUT variables
x_header_rec => l_header_rec_out,
x_header_val_rec => l_header_val_rec_out,
x_header_adj_tbl => l_header_adj_tbl_out,
x_header_adj_val_tbl => l_header_adj_val_tbl_out,
x_header_price_att_tbl => l_header_price_att_tbl_out,
x_header_adj_att_tbl => l_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,
x_header_scredit_tbl => l_header_scredit_tbl_out,
x_header_scredit_val_tbl => l_header_scredit_val_tbl_out,
x_line_tbl => l_line_tbl_out,
x_line_val_tbl => l_line_val_tbl_out,
x_line_adj_tbl => l_line_adj_tbl_out,
x_line_adj_val_tbl => l_line_adj_val_tbl_out,
x_line_price_att_tbl => l_line_price_att_tbl_out,
x_line_adj_att_tbl => l_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,
x_line_scredit_tbl => l_line_scredit_tbl_out,
x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,
x_lot_serial_tbl => l_lot_serial_tbl_out,
x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,
x_action_request_tbl => l_action_request_tbl_out,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);

/*****************CHECK RETURN STATUS***********************************/
if l_return_status = FND_API.G_RET_STS_SUCCESS then
dbms_output.put_line('Return status is success ');
dbms_output.put_line('debug level '
l_debug_level);
if (l_debug_level > 0) then
dbms_output.put_line('success');
end if;
commit;
else
dbms_output.put_line('Return status failure ');
if (l_debug_level > 0) then
dbms_output.put_line('failure');
end if;
rollback;
end if;

/*****************DISPLAY RETURN STATUS FLAGS******************************/
if (l_debug_level > 0) then
DBMS_OUTPUT.PUT_LINE('process ORDER ret status IS: '
l_return_status);
DBMS_OUTPUT.PUT_LINE('process ORDER msg data IS: '
l_msg_data);
DBMS_OUTPUT.PUT_LINE('process ORDER msg COUNT IS: '
l_msg_count);
DBMS_OUTPUT.PUT_LINE('header.order_number IS: ' to_char(l_header_rec_out.order_number));
DBMS_OUTPUT.PUT_LINE('header.return_status IS: ' l_header_rec_out.return_status);
DBMS_OUTPUT.PUT_LINE('header.booked_flag IS: ' l_header_rec_out.booked_flag);
DBMS_OUTPUT.PUT_LINE('header.header_id IS: ' l_header_rec_out.header_id);
DBMS_OUTPUT.PUT_LINE('header.order_source_id IS: ' l_header_rec_out.order_source_id);
DBMS_OUTPUT.PUT_LINE('header.flow_status_code IS: ' l_header_rec_out.flow_status_code);
end if;
/*****************DISPLAY ERROR MSGS*************************************/
if (l_debug_level > 0) then
FOR i IN 1 .. l_msg_count LOOP
Oe_Msg_Pub.get(
p_msg_index => i
,p_encoded => Fnd_Api.G_FALSE
,p_data => l_data
,p_msg_index_out => l_msg_index);
DBMS_OUTPUT.PUT_LINE('message is: ' l_data);
DBMS_OUTPUT.PUT_LINE('message index is: ' l_msg_index);
END LOOP;
end if;
if (l_debug_level > 0) then
DBMS_OUTPUT.PUT_LINE('Debug = ' OE_DEBUG_PUB.G_DEBUG);
DBMS_OUTPUT.PUT_LINE('Debug Level = ' to_char(OE_DEBUG_PUB.G_DEBUG_LEVEL));
DBMS_OUTPUT.PUT_LINE('Debug File = ' OE_DEBUG_PUB.G_DIR'/'OE_DEBUG_PUB.G_FILE);
DBMS_OUTPUT.PUT_LINE('****************************************************');

OE_DEBUG_PUB.DEBUG_OFF;
end if;
--END;
end createsalesorder;

Query to find out order and line hold information

select distinct ohd.name
,ooh.hold_until_date
,ooh.hold_comment
,h.order_number
,l.item_identifier_type
,l.inventory_item_id
,l.ordered_item
,oh.header_id
,oh.line_id
,oh.order_hold_id
from oe_hold_definitions ohd,
oe_hold_sources_all ooh,
oe_order_headers_all h,
oe_order_lines_all l,
oe_order_holds_all oh
where ohd.hold_id = ooh.hold_id
and oh.hold_source_id = ooh.hold_source_id
and oh.header_id = h.header_id
and h.header_id=l.header_id
and l.open_flag='Y'
and h.open_flag='Y'
order by ohd.name,h.order_number;

Query to find out shipper detail info

SELECT
wnd.delivery_id,
wnd.name delivery_name,
wdd.source_header_number
so_order_number,
oola.line_number so_line_number,
wdd.source_header_id so_header_id,
wdd.source_line_id so_line_id,
wdd.shipping_instructions,
wdd.inventory_item_id,
wdd.requested_quantity_uom,
msi.description item_description,
msi.revision_qty_control_code ,
wdd.ship_method_code carrier,
wdd.shipment_priority_code priority,
wdd.organization_id,
wnd.initial_pickup_location_id,
wdd.released_status,
wdd.source_code
FROM mtl_system_items_vl msi,
oe_order_lines_all oola,
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
WHERE wnd.delivery_id =18910
AND wda.delivery_id = wnd.delivery_id(+)
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.inventory_item_id = msi.inventory_item_id(+)
AND wdd.organization_id = msi.organization_id(+)
AND wdd.source_line_id = oola.line_id
AND wdd.source_header_id = oola.header_id;

Query to find out the shipper info

select
wnd.delivery_id delivery_id,
substrb(party.party_name,1,50) customer,
wpb.name batch_name,
wsh_util_core.get_location_description(
wnd.INITIAL_PICKUP_LOCATION_ID,
'NEW UI CODE') ship_from,
wsh_util_core.get_location_description(
wnd.ULTIMATE_DROPOFF_LOCATION_ID,
'NEW UI CODE') ship_to,
wnd.INITIAL_PICKUP_DATE pickup_date,
wnd.ULTIMATE_DROPOFF_DATE dropoff_date,
lv.meaning ship_method,
wnd.WAYBILL waybill,
wnd.GROSS_WEIGHT gross_weight,
wnd.WEIGHT_UOM_CODE uom,
wnd.status_code,
we.message
from wsh_new_deliveries wnd,
wsh_picking_batches wpb,
wsh_exceptions we,
fnd_lookup_values_vl lv,
hz_cust_accounts cust_acct,
hz_parties party
where wnd.delivery_id = 12814
and wpb.batch_id = wnd.batch_id
and we.delivery_id(+) = wnd.delivery_id
and we.exception_name(+) = 'WSH_BATCH_MESSAGE'
and lv.lookup_code(+) = wpb.ship_method_code
and lv.lookup_type(+) = 'SHIP_METHOD'
and lv.view_application_id(+) = 3
and cust_acct.cust_account_id (+)=wnd.customer_id
and party.party_id(+) = cust_acct.party_id;

Concurrent Program Name with Parameter, Value set

SELECT fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, par.column_seq_num
, par.end_user_column_name
, par.form_left_prompt prompt
, par.enabled_flag
, par.required_flag
, par.display_flag
, par.flex_value_set_id
, ffvs.flex_value_set_name
, flv.meaning default_type
, par.DEFAULT_VALUE
FROM fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl par
, fnd_flex_value_sets ffvs
, fnd_lookup_values flv
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = :conc_prg_name
AND fcpl.LANGUAGE = 'US'
AND par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
AND ffvs.flex_value_set_id = par.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = par.default_type
AND flv.LANGUAGE(+) = USERENV ('LANG')
ORDER BY par.column_seq_num;

Sales Order Line Status Flow and Meaning

Below are some of the different statuses of Sales Order Line with brief explanation

OM = Order Management Sales order form
SE = Shipping Transactions or execution form

1) Entered (OM): Order is saved but not booked

2) Booked (OM): Order is Booked.

3) Awaiting Shipping (OM): Order is booked but lines are not yet picked.
Navigating to Shipping Execution, the delivery line status flow is:

4) Not Ready to Release (SE): A delivery line may be in this status when it is interfaced manually into Shipping, is not scheduled and has no reservations. When lines are imported automatically from Order Management this status is not used

5) Released to Warehouse (SE): Pick Release has started but not yet completed. One of the reason could be allocation have not been pick confirmed. The Pick Release process creates a Move Order Header & Mover Order Line in Inventory. This is a common status for users that perform a two-step pick release process. This status indicates that inventory allocation has occurred however pick conformation has not yet taken place.

6) Ready to Release (SE): Order Line is booked and passed to shipping execution. The line is now eligible to pick Release.

7) Backordered(SE): The status of Backorderd is assigned to a line under the following circumstances.
• The Pick Release process attempted to allocate inventory to the line and all or a partial quantity of the item was not available. In this case the system automatically backorders the discrepant quantity.
• At Ship confirm the user enters a shipped quantity for an item that is less than the original requested quantity.
• The user manually Backorders the entire delivery.
8) Shipped (SE): The delivery line is shipped confirmed.

9) Confirmed (SE): The delivery line is shipped or backordered and the trip stops are open.

10) Picked (OM): Pick release is complete, both allocations and pick confirm

11) Picked Partial (OM): This status occurs when a delivery line is not allocated the full quantity during Pick Release and Ship Confirm has not occurred

12) Interfaced (SE): The delivery line is shipped and Inventory interface concurrent process is complete.

13) Awaiting Fulfillment (OM): When fulfillment set is used, Not all shippable lines in a fulfillment set or a
configuration are fulfilled

14) Fulfilled (OM): All lines in a fulfillment set are fulfilled.

15) Interfaced to Receivables (OM): The order is linked with Receivables and the invoice is created.

16) Partially Interfaced to Receivables (OM): This status is used in a PTO flow and indicates that the particular PTO item is required for revenue.

17) Closed (OM): Closed indicates that the line is closed.

18) Canceled (OM): Indicates that the line has been completely canceled. No further processing will occur for this line.

Convert Date into Week Rage

Below is an example of how to display dates into week Range in Oracle
For E.g. 22-Nov-08 is in the date range 17-Nov-08 to 23-Nov-08

Firstly lets see how to get week of the year. Following query can be used to get this

SELECT to_char(sysdate,'WW') FROM Dual;

Now lets get the week range
SELECT TO_CHAR (TRUNC (SYSDATE, 'IYYY') + ((TO_CHAR (SYSDATE, 'WW') - 1) * 7), 'DD-MON-RR')
|| ' to '
|| TO_CHAR (TRUNC (SYSDATE, 'IYYY') + ((TO_CHAR (SYSDATE, 'WW')) * 7)-1, 'DD-MON-RR') week_range
FROM Dual;
Output
======
The output of above query for date 29-Nov-2008 is 24-NOV-08 to 30-NOV-08

Query to List all the responsibilities attached to a User

select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('LANG');

Want to forcefully kill any session

alter system kill session 'sid,serial#'

e.g.
altery system kill session '123,5325';

Query to find locked objects in Oracle

SELECT c.owner
, c.object_name
, c.object_type
, b.SID
, b.serial#
, b.status
, b.osuser
, b.machine
, b.program
, b.module
, b.action
FROM v$locked_object a
, v$session b
, dba_objects c
WHERE b.SID = a.session_id
AND a.object_id = c.object_id
ORDER BY module;

Link Purchase Order and Requisition

SELECT prh.segment1 req_number
,prh.authorization_status
,prl.line_num req_line_num
,prl.item_description req_item_description
,prl.unit_price req_unit_price
,prl.quantity req_quantity
,pd.req_header_reference_num
,pd.req_line_reference_num
,pl.line_num
,pl.item_description
,pl.quantity
,pl.amount
,ph.segment1 po_number
,prd.distribution_id
,pd.req_distribution_id
FROM po_requisition_headers_all prh
,po_requisition_lines_all prl
,po_req_distributions_all prd
,po_distributions_all pd
,po_line_locations_all pll
,po_lines_all pl
,po_headers_all ph
WHERE prh.requisition_header_id = prl.requisition_header_id
and prh.org_id = prl.org_id
and prl.requisition_line_id = prd.requisition_line_id
and prl.org_id = prd.org_id
and prd.distribution_id = pd.req_distribution_id(+)
and prd.org_id = pd.org_id(+)
and pd.line_location_id = pll.line_location_id(+)
and pd.org_id = pll.org_id(+)
and pll.po_line_id = pl.po_line_id(+)
and pll.org_id = pl.org_id(+)
and pl.po_header_id = ph.po_header_id(+)
and pl.org_id = ph.org_id(+);

Below is the query that can help in getting onhand quantity at given date

SELECT SUM (target_qty)
, item_id
FROM (SELECT moqv.subinventory_code subinv
, moqv.inventory_item_id item_id
, SUM (transaction_quantity) target_qty
FROM mtl_onhand_qty_cost_v moqv
WHERE moqv.organization_id = :org_id
AND moqv.inventory_item_id = :item_id
GROUP BY moqv.subinventory_code
, moqv.inventory_item_id
, moqv.item_cost
UNION
SELECT mmt.subinventory_code subinv
, mmt.inventory_item_id item_id
, -SUM (primary_quantity) target_qty
FROM mtl_material_transactions mmt
, mtl_txn_source_types mtst
WHERE mmt.organization_id = :org_id
AND transaction_date >= TO_DATE (:hist_date) + 1
AND mmt.transaction_source_type_id =
mtst.transaction_source_type_id
AND mmt.inventory_item_id = :item_id
GROUP BY mmt.subinventory_code
, mmt.inventory_item_id) oq
GROUP BY oq.item_id;

Item Import (Item Conversion)

The Item import Interface(IOI) reads data from following tables for importing items and item details. The MTL_SYSTEMS_ITEM_INTERFACE table is used for new item numbers
and all item attributes. This is the main item interface table, and can be
the only table used to import items. MTL_ITEM_REVISIONS_INTERFACE is used if Item revisions history is also loaded with items. Item categories can be imported using MTL_ITEM_CATEGORIES_INTERFACE.
The import error can be tracked using MTL_INTERFACE_ERRORS table. The transaction_id and request_id populated by the import program can be used to link interface table and error table.

Required columns in MTL_SYSTEM_ITEMS_INTERFACE
PROCESS_FLAG = 1 (The column is used to identify status of record)
TRANSACTION_TYPE = 'CREATE' or 'UPDATE'
SET_PROCESS_ID = any numeric value (This is not a required column but for performance it is advised to use this column and then run import program for the value entered here)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org.
DESCRIPTION = 'Description of the item'
ITEM_NUMBER and/or SEGMENT(n) = If using item_number then each segment value should be entered concatenated by segment seperator. If Item revisions history is also being loaded then Item_number should be populated.
LIST_PRICE_PER_UNIT = If material cost is to be populated for an item along with item import.

Required columns in MTL_ITEM_REVISIONS_INTERFACE table. The table is only used if Item revision is to be loaded in the same run with IOI. If this table is not used then items are created with the default revision setup for an organization.
PROCESS_FLAG = 1
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = any numeric value (Should be same for the item in MTL_SYSTEM_ITEMS_INTERFACE table)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org.
REVISION
EFFECTIVITY_DATE
IMPLEMENTATION_DATE
ITEM_NUMBER = Same as item_number in mtl_system_items_interface table.
Each row in the mtl_item_revisions_interface table must have the REVISION and
EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.

Required columns for MTL_ITEM_CATEGORIES_INTERFACE table.
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = any numeric value (Should be same for the item in MTL_SYSTEM_ITEMS_INTERFACE table)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org
ITEM_NUMBER/INVENTORY_ITEM_ID or both
CATEGORY_SET_NAME or CATEGORY_SET_NAME or both
CATEGORY_ID or CATEGORY_NAME or both


For performance purpose, it is advised to batch set of records using set_process_id column and then run import program for that set_process_id. The item import (IOI) program can be run in parallel if separate set_process_ids are passed while submitting. The IOI automatically separates Master records from Child, and
processes Master records first. However, as one IOI process is not aware of
other IOI processes running in parallel, do not split a given item's separate
Organization records into two different SET_PROCESS_IDs that are being run in
parallel.

Item import program can be run in 2 modes INSERT & UPDATE.
The method to update Item attribute columns to NULL is to use the following values:
• for Numeric fields: insert -999999
• for Character fields: insert '!'

Query to get Customer Related information for a Sales Order

SELECT ooh.order_number
, hp_bill.party_name
, hl_ship.address1 ||Decode(hl_ship.address2,NULL,'',chr(10))
||hl_ship.address2||Decode(hl_ship.address3,NULL,'',chr(10))
||hl_ship.address3||Decode(hl_ship.address4,NULL,'',chr(10))
||hl_ship.address4||Decode(hl_ship.city,NULL,'',chr(10))
||hl_ship.city ||Decode(hl_ship.state,NULL,'',',')
||hl_ship.state ||Decode(hl_ship.postal_code,'',',')
||hl_ship.postal_code ship_to_address
, hl_bill.address1 ||Decode(hl_bill.address2,NULL,'',chr(10))
||hl_bill.address2||Decode(hl_bill.address3,NULL,'',chr(10))
||hl_bill.address3||Decode(hl_bill.address4,NULL,'',chr(10))
||hl_bill.address4||Decode(hl_bill.city,NULL,'',chr(10))
||hl_bill.city ||Decode(hl_bill.state,NULL,'',',')
||hl_bill.state ||Decode(hl_bill.postal_code,'',',')
||hl_bill.postal_code bill_to_address
, ooh.transactional_curr_code currency_code
, mp.organization_code
, ooh.fob_point_code
, ooh.freight_terms_code
, ooh.cust_po_number
FROM oe_order_headers_all ooh
, hz_cust_site_uses_all hcs_ship
, hz_cust_acct_sites_all hca_ship
, hz_party_sites hps_ship
, hz_parties hp_ship
, hz_locations hl_ship
, hz_cust_site_uses_all hcs_bill
, hz_cust_acct_sites_all hca_bill
, hz_party_sites hps_bill
, hz_parties hp_bill
, hz_locations hl_bill
, mtl_parameters mp
WHERE 1 = 1
AND header_id = :p_header_id
AND ooh.ship_to_org_id = hcs_ship.site_use_id
AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
AND hca_ship.party_site_id = hps_ship.party_site_id
AND hps_ship.party_id = hp_ship.party_id
AND hps_ship.location_id = hl_ship.location_id
AND ooh.invoice_to_org_id = hcs_bill.site_use_id
AND hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id
AND hca_bill.party_site_id = hps_bill.party_site_id
AND hps_bill.party_id = hp_bill.party_id
AND hps_bill.location_id = hl_bill.location_id
AND mp.organization_id(+) = ooh.ship_from_org_id;

Request here is the query to list concurrent program name with its parameter, values set and default value/type

SELECT fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, par.column_seq_num
, par.end_user_column_name
, par.form_left_prompt prompt
, par.enabled_flag
, par.required_flag
, par.display_flag
, par.flex_value_set_id
, ffvs.flex_value_set_name
, flv.meaning default_type
, par.DEFAULT_VALUE
FROM fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl par
, fnd_flex_value_sets ffvs
, fnd_lookup_values flv
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = :conc_prg_name
AND fcpl.LANGUAGE = 'US'
AND par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
AND ffvs.flex_value_set_id = par.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = par.default_type
AND flv.LANGUAGE(+) = USERENV ('LANG')
ORDER BY par.column_seq_num;

The query will list Party Name, Number, Customer Number and there Bill To and Ship Addresses

SELECT hp.party_name
, hp.party_number
, hca.account_number
, hca.cust_account_id
, hp.party_id
, hps.party_site_id
, hps.location_id
, hl.address1
, hl.address2
, hl.address3
, hl.city
, hl.state
, hl.country
, hl.postal_code
, hcsu.site_use_code
, hcsu.site_use_id
, hcsa.bill_to_flag
FROM hz_parties hp
, hz_party_sites hps
, hz_locations hl
, hz_cust_accounts_all hca
, hz_cust_acct_sites_all hcsa
, hz_cust_site_uses_all hcsu
WHERE hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hp.party_id = hca.party_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
AND hca.account_number = :customer_number;

Query - Relation between AR Invoice and Sales Order tables

SELECT ooha.order_number
, oola.line_number so_line_number
, oola.ordered_item
, oola.ordered_quantity * oola.unit_selling_price so_extended_price
, rcta.trx_number invoice_number
, rcta.trx_date
, rctla.line_number inv_line_number
, rctla.unit_selling_price inv_unit_selling_price
FROM oe_order_headers_all ooha
, oe_order_lines_all oola
, ra_customer_trx_all rcta
, ra_customer_trx_lines_all rctla
WHERE ooha.header_id = oola.header_id
AND rcta.customer_trx_id = rctla.customer_trx_id
AND rctla.interface_line_attribute6 = TO_CHAR (oola.line_id)
AND rctla.interface_line_attribute1 = TO_CHAR (ooha.order_number)
AND order_number = :p_order_number;