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.