SQL*Loader Concepts

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Capabilities of SQL*Loader Can load data from multiple data files during the same load session Can load data into multiple tables during the same load session Can selectively load data (Records can be loaded based on the records’… Read More »

FNDLOAD and XDOLoader to Migrate XMLP reports

To migrate an XMLP report we need below 4 objects LDT file for Concurrent Program LDT file for XMLP Template and Data Definition XML Data Definition RTF or XLS layout LDT file for Concurrent Program To Download LDT: FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_EMP_DETAILS_RPT.ldt PROGRAM APPLICATION_SHORT_NAME=XXCUST CONCURRENT_PROGRAM_NAME=XX_EMP_DETAILS_RPT To Upload LDT: FNDLOAD apps/apps 0 Y… Read More »

PL/SQL Script to Delete Lookup or Lookup Code

/******************************************** *PURPOSE: To Delete Lookup or Lookup Code * *AUTHOR: Shailender Thallam * *********************************************/ SET serveroutput ON; —   DECLARE CURSOR cur_lkp_values IS SELECT lookup_type, lookup_code, security_group_id, view_application_id FROM fnd_lookup_values WHERE lookup_type = ‘XX_LKP_NAME’ AND lookup_code LIKE ‘XX_LKP_CODE’; BEGIN FOR i IN cur_lkp_values LOOP BEGIN fnd_lookup_values_pkg.delete_row (x_lookup_type => i.lookup_type, x_security_group_id => i.security_group_id, x_view_application_id => i.view_application_id,… Read More »

PL/SQL Script to read CLOB(XML Data) which is more than 32kb size

Here in this article I will be writing how to read a CLOB file using DBMS_LOB which is more than 32kb size. The below example is related to dunning letters where a seeded concurrent program IEX: Bulk XML Delivery Manager generates dunning letters data in XML form and stored it in table IEX_XML_REQUEST_HISTORIES in CLOB… Read More »

Frequently used Oracle SQL Developer Short Cut Keys

Short Cut Description Ctrl+Space Invokes code insight on demand Ctrl+Up Replaces worksheet with Previous SQL from SQL History Ctrl+Down Replaces worksheet with Next SQL from SQL History Ctrl+Shift+Up Appends current worksheet with Previous SQL from SQL History Ctrl+Shift+Down Appends current worksheet with Next SQL from SQL History Dhift+F4 opens a Describe window for current object… Read More »

Oracle HRMS SQL Query to find out Employee and their Supervisor Hierarchy

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 SELECT LEVEL, e.* FROM (SELECT DISTINCT papf.person_id, papf.employee_number, papf.full_name "EMPLOYEE_FULL_NAME", paaf.supervisor_id, papf1.employee_number "SUPERVISOR_EMP_NUMBER", papf1.full_name "SUPERVISOR_FULL_NAME" FROM apps.per_all_people_f papf, apps.per_all_assignments_f paaf, apps.per_all_people_f papf1, apps.per_person_types ppt WHERE papf.person_id = paaf.person_id AND papf1.person_id =… Read More »