Oracle EBS: How to Delete Data Definition and Data Template From DB Backend Script.

Subject: How to Delete Data Definition and Data Template From DB Backend Script. 

PLSQL Script:

BEGIN

DELETE FROM XDO_TEMPLATES_B

WHERE template_code = 'XXTLM_MW_ITEM_SELF_LIFE_RPT';

DELETE FROM XDO_TEMPLATES_TL

WHERE template_code = 'XXTLM_MW_ITEM_SELF_LIFE_RPT';

DELETE FROM XDO_LOBS

WHERE lob_code = 'XXTLM_MW_ITEM_SELF_LIFE_RPT';

DELETE FROM XDO_DS_DEFINITIONS_B

WHERE data_source_code = 'XXTLM_MW_ITEM_SELF_LIFE_RPT';

DELETE FROM XDO_DS_DEFINITIONS_TL

WHERE data_source_code = 'XXTLM_MW_ITEM_SELF_LIFE_RPT';

COMMIT;

END;

Oracle EBS: How to update the DBA Directory Path

 select * from dba_directories where directory_name='TLM_IFACE_INBOUND';  

--Old Path:--

/s01/oracle/interfaces/TLM/Iface/Inbound

--New Path:--

/s01/interfaces/mounts/ItemAutomation

--SQL Script:--

CREATE OR REPLACE directory TLM_IFACE_INBOUND AS '/s01/interfaces/mounts/ItemAutomation';

Oracle Fusion: How to Get ORIG_SYSTEM_REFERENCE for the Party and Party Sites while performing customer migration

Subject: When we perform bulk customer migration using FBID we need to get ORIG_SYSTEM_REFERENCE after the migration is complete.

Note: HZ_ORIG_SYS_REFERENCES  is the base table to store the ORIG_SYSTEM_REFERENCE details

--PARTY ORIG SYS REFERENCE--

SELECT ORIG_SYSTEM_REFERENCE 

FROM HZ_ORIG_SYS_REFERENCES 

WHERE OWNER_TABLE_ID = HZ_PARTIES.PARTY_ID  --NEED TO PASS PARTY_ID

AND ORIG_SYSTEM='CSV'

AND OWNER_TABLE_NAME='HZ_PARTIES' -- FOR PARTY REFERENCE PARTY TABLE NEED TO PASS

--PARTY SITE ORIG SYS REFERENCE--

SELECT ORIG_SYSTEM_REFERENCE 

FROM HZ_ORIG_SYS_REFERENCES 

WHERE OWNER_TABLE_ID =HZ_PARTY_SITES.PARTY_SITE_ID  --NEED TO PASS PARTY_SITE_ID

AND ORIG_SYSTEM='CSV'

AND OWNER_TABLE_NAME='HZ_PARTY_SITES' -- FOR PARTY REFERENCE PARTY SITE TABLE NEED TO PASS