Toad Archives - Oracle ERP Apps Guide

API, Apps Technical, Oracle Apps, R12, Toad

R12 Supplier Contact creation API

Below are the steps to create Supplier contacts in Oracle EBS R12.

AP_SUP_SITE_CONTACT_INT interface used in 11i for loading supplier contact. Currently in R12 contacts can not be loaded using the interface table.

ap_vendor_pub_pkg.create_vendor_contact API is used in R12 and the program has to be registered as a concurrent program.

### Sample R12 Code

BEGIN
v_party_usg_assignment_id := NULL;
v_relationship_id := NULL;
v_directional_flag := NULL;
v_rel_rowid := NULL;
fnd_file.put_line (fnd_file.LOG,
‘Vendor code :’
|| rec.vendor_name
|| ‘ Vendor site code :’
|| rec.vendor_site_code
|| ‘ Person Last Name : ‘
|| rec.person_last_name
);
l_vendor_contact.vendor_id := rec.vendor_id;
l_vendor_contact.vendor_site_id := rec.vendor_site_id;
l_vendor_contact.org_id := rec.org_id;
l_vendor_contact.person_first_name := rec.person_first_name;
l_vendor_contact.person_middle_name := rec.person_middle_name;
l_vendor_contact.person_last_name := rec.person_last_name;
l_vendor_contact.phone := rec.phone;
l_vendor_contact.email_address := rec.email_address;
p_init_msg_list := fnd_api.g_true;
p_commit := fnd_api.g_false;
x_return_status := NULL;
x_msg_count := NULL;
x_msg_data := NULL;

IF rec.process_flag = ‘I’
THEN
fnd_file.put_line (fnd_file.LOG, ‘Creating contacts….’);
ap_vendor_pub_pkg.create_vendor_contact
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_contact_rec => l_vendor_contact,
x_vendor_contact_id => l_vendor_contact.vendor_contact_id,
x_per_party_id => l_vendor_contact.per_party_id,
x_rel_party_id => l_vendor_contact.relationship_id,
x_rel_id => l_vendor_contact.rel_party_id,
x_org_contact_id => l_vendor_contact.org_contact_id,
x_party_site_id => l_vendor_contact.party_site_id
);
error_handling (rec.r_id, x_return_status, x_msg_count, x_msg_data);
fnd_file.put_line (fnd_file.LOG, ‘*************’);
END IF;
END;

May 17, 2015/by Oracle ERP Apps Guide

PL SQL, Toad, XML

How to Reading XML data using PLSQL

This Document explains the steps of how we read an XML data file using PLSQL and write them to oracle tables.

Create a UTL Directory:

This involves the following steps. Creating a Logical directory and giving the Permissions to access this directory using an URL. Using the XML Dom Parser Procedures to read the Xml file , parse it
and then load it into the respective columns.

1) Create a logical directory
Create or replace directory TEST_LOB_DIR as ‘/data/tst1/attachment’
It creates a directory with Owner SYS.Grant privileges on this directory to Apps.

2) Create a physical directory at the given location ‘/data/tst1/attachment’
Permissions on this directory too.This directory has to be a UTL directory.

3) Edit the conf file at this path
Cd $IAS_ORACLE_HOME/Apache/Apache/conf
==>/apps/applmgr/tst1ora/IAS/Apache/Apache/conf
Edit the file apps.conf
Alias /attachment/ “/data/tst1/attachment/”
<Location /attachment/>
Order allow, deny
Allow from all
</Location>

4) Bounce the Apache.

Create a procedure to parse and retrieve the XML data and then insert it into appropriate columns in the database.

Used two PLSQL packages for these –
1. XMLPARSER
2. XMLDOM

1. XMLParser – Procedures

i.)parse(p Parser, url VARCHAR2)
Description – The parse procedure takes two parameters which are the parse object and the url of the
xml file that has to parsed.
ii.) setValidationMode(p Parser, yes BOOLEAN)
Checks whether XML is Valid or not.
iii.) setBaseDir(p Parser, dir VARCHAR2)
Sets the base url or directory path.
iv.) getDocument(p Parser)
Get the Document which has to be parsed.

2. XMLDOM
DOMDocument
DOMELEMENT
DOMNODELIST
DOMNODE
DOMNamedNodeMap

FUNCTIONS –
getNodeName(n DOMNode) RETURN VARCHAR2 – Retrieves the Name of the Node
getNodeValue(n DOMNode) RETURN VARCHAR2 – Retrieves the Value of the Node
getElementsByTagName(doc DOMDocument, tagname IN VARCHAR2)- Retrieves the elements in
the by tag name
getDocumentElement(doc DOMDocument) RETURN DOMElement – Retrieves the root element of
the document
getFirstChild(n DOMNode) RETURN DOMNode – Retrieves the first child of the node
getLength(nl DOMNodeList) RETURN NUMBER- Retrieves the number of items in the list.

Sample XML File

-<vapi_HRMS>

– <record>

<vcEmp_Full_Name>Vinod Mudakkayil</vcEmp_Full_Name>

<vcDepartment>HR-Recruitment</vcDepartment>

<vcProgram_Name>HR – Recruitment</vcProgram_Name>

<vcSub_Program_Name>HR-Recruitment</vcSub_Program_Name>

<vcEmail_ID>[email protected]</vcEmail_ID>

<vcCentername>Bangalore</vcCentername>

<Hop_Id>101006923</Hop_Id>

<VP_Id>101006923</VP_Id>

<VCEMP_STATUS>CORPORATE</VCEMP_STATUS>

<vcEmp_First_Name>Vinod</vcEmp_First_Name>

<vcEmp_Last_Name>Mudakkayil</vcEmp_Last_Name>

<vcEmp_Gender>Male</vcEmp_Gender>

<vcEmp_DOJ>2005/04/07</vcEmp_DOJ>

</record>

– <record>

<vcEmp_Full_Name>Vinod Mudakkayil</vcEmp_Full_Name>

<vcDepartment>HR-Recruitment</vcDepartment>

<vcProgram_Name>HR – Recruitment</vcProgram_Name>

<vcSub_Program_Name>HR-Recruitment</vcSub_Program_Name>

<vcEmail_ID>[email protected]</vcEmail_ID>

<vcCentername>Bangalore</vcCentername>

<Hop_Id>01006923,101006923,O01006923</Hop_Id>

<VP_Id>O01006923</VP_Id>

<VCEMP_STATUS>CORPORATE</VCEMP_STATUS>

<vcEmp_First_Name>Vinod</vcEmp_First_Name>

<vcEmp_Last_Name>Mudakkayil</vcEmp_Last_Name>

<vcEmp_Gender>Male</vcEmp_Gender>

<vcEmp_DOJ>2005/04/07</vcEmp_DOJ>

</record>

</vapi_HRMS>

Sample Code:

— Call the procedure —

PROCEDURE xml_perse (

errbuf OUT VARCHAR2,

retcode OUT NUMBER,

dir VARCHAR2,

inpfile VARCHAR2

)

IS

p xmlparser.parser;

doc xmldom.domdocument;

docelem DBMS_XMLDOM.domelement;

— prints elements in a document

BEGIN

— new parser

p := xmlparser.newparser;

— set some characteristics

xmlparser.setvalidationmode (p, FALSE);

fnd_file.put_line (fnd_file.LOG, ‘ xml_perse Validated’);

–xmlparser.setErrorLog(p, dir || ‘/’ || errfile);

xmlparser.setbasedir (p, dir);

fnd_file.put_line (fnd_file.LOG, ‘ xml_perse set path’);

— parse input file

xmlparser.parse (p, dir || ‘/’ || inpfile);

fnd_file.put_line (fnd_file.LOG, ‘ xml_perse parse’);

— get document

doc := xmlparser.getdocument (p);

fnd_file.put_line (fnd_file.LOG, ‘ xml_perse get document’);

— Print document elements

DBMS_OUTPUT.put (‘The elements are: ‘);

printelements (doc);

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

–SQLERRM

DBMS_OUTPUT.put (SQLERRM);

END xml_perse;

PROCEDURE printelements (doc xmldom.domdocument)

IS

nl1 xmldom.domnodelist;

nl2 xmldom.domnodelist;

nl3 xmldom.domnodelist;

nl4 xmldom.domnodelist;

nl5 xmldom.domnodelist;

len1 NUMBER;

len2 NUMBER;

len3 NUMBER;

len4 NUMBER;

len5 NUMBER;

n1 xmldom.domnode;

n2 xmldom.domnode;

n3 xmldom.domnode;

n4 xmldom.domnode;

nnm xmldom.domnamednodemap;

attrname VARCHAR (1000);

attrval VARCHAR (1000);

v_empid VARCHAR2 (1000);

v_emp_full_name VARCHAR2 (1000);

v_designation VARCHAR2 (1000);

BEGIN

— get all elements

fnd_file.put_line (fnd_file.LOG, ‘get all elements’);

nl1 := xmldom.getelementsbytagname (doc, ‘record’);

nl2 := xmldom.getelementsbytagname (doc, ‘EmpID’);

nl3 := xmldom.getelementsbytagname (doc, ‘vcEmp_Full_Name’);

nl4 := xmldom.getelementsbytagname (doc, ‘vcDesignation’);

fnd_file.put_line (fnd_file.LOG, ‘Length of the Elements’);

— Length of the Elements

len1 := xmldom.getlength (nl1);

len2 := xmldom.getlength (nl2);

len3 := xmldom.getlength (nl3);

len4 := xmldom.getlength (nl4);

— loop through elements

FOR i IN 0 .. len1 – 1

LOOP

v_empid := NULL;

v_emp_full_name := NULL;

v_designation := NULL;

n1 := xmldom.item (nl1, i);

n2 := xmldom.item (nl2, i);

n3 := xmldom.item (nl3, i);

n4 := xmldom.item (nl4, i);

v_empid := xmldom.getnodevalue (n2);

v_emp_full_name := xmldom.getnodevalue (n3);

v_designation := xmldom.getnodevalue (n4);

fnd_file.put_line (fnd_file.LOG, ‘***************************’);

fnd_file.put_line (fnd_file.LOG, v_empid);

fnd_file.put_line (fnd_file.LOG, v_emp_full_name);

fnd_file.put_line (fnd_file.LOG, v_designation);

fnd_file.put_line (fnd_file.LOG, ‘***************************’);

DELETE FROM xx_employee_temp;

–WHERE status = ‘S’;

INSERT INTO xx_employee_temp

(empid, emp_full_name, designation,status, error_description

)

VALUES (v_empid, v_emp_full_name, v_designation,NULL, NULL

);

DBMS_OUTPUT.put_line (‘ ‘);

END LOOP;

fnd_file.put_line (fnd_file.LOG, ‘Inserted’);

END printelements;

May 17, 2015/by Oracle ERP Apps Guide

Apps Technical, ASCP, Oracle Apps, Query, R12, Toad

Query to find Pegging Details in ASCP

SELECT mso.sales_order_number, md1.*,
MSI1.ITEM_NAME||’ (‘||MSI1.INVENTORY_ITEM_ID||’)’ “COMPONENT (ID)”
, substr( md1.order_number,1,22) order_num
, MS1.NEW_ORDER_QUANTITY COMP_QTY — this is the pegged or previously pegged column
, decode (ms1.order_type,’1′,’Purchase order’
,’11’,’Intransit shipment’
,’12’,’Intransit receipt’
,’13’,’Suggested repetitive schedule’
,’14’,’Discrete job co-product/by-product’
,’15’,’Nonstandard job by-product’
,’16’,’Repetitive schedule by-product’
,’17’,’Planned order co-product/by-product’
,’18’,’On Hand’
,’2′,’Purchase requisition’
,’27’,’Flow schedule’
,’28’,’Flow schedule by-product’
,’29’,’Payback Supply’
,’3′,’Discrete job’
,’30’,’Current repetitive schedule’
,’32’,’Returns’
,’4′,’Suggested aggregate repetitive schedule’
,’41’,’User Supply’
,’45’,’Demand Class Consumption’
,’46’,’Supply Due To Stealing’
,’47’,’Demand Due To Stealing’
,’48’,’Supply Adjustment’
,’49’,’PO Acknowledgment’
,’5′,’Planned order’
,’50’,’ATP Aggregate Supply’
,’51’,’Planned inbound shipment’
,’52’,’Requested inbound shipment’
,’53’,’Internal requisition’
,’60’,’Order Rescheduling Adjustment’
,’7′,’Non-standard job’
,’70’,’Maintenance Work Order’
,’8′,’PO in receiving’) Order_type
— , decode (MFP1.supply_type, 1, ‘Purchase Order’, 2, ‘Purchase Requisition’, 3, ‘Work Order’, 11, ‘Intransit Shipment’, 12, ‘Intransit receipt’) Supply_type
— ,decode (MFP2.supply_type, 1, ‘Purchase Order’, 2, ‘Purchase Requisition’, 3, ‘Work Order’, 11, ‘Intransit Shipment’, 12, ‘Intransit receipt’) Supply_type2
, MFP1.demand_quantity
–, MFP1.supply_quantity
, MFP1.demand_date
, MFP1.supply_date
, ROUND(MFP1.ALLOCATED_QUANTITY,1) COMP_ALLOC
, decode(MD1.origination_type, ’29’,’Peg_Fcast’,’30’,’Peg_Sales’) Pegged_To
, decode (MFP1.DEMAND_ID, -1,’peg_Excess’,-2,’Peg_SS’) Pegged_To_1
, MFP2.ORGANIZATION_ID
,MFP1.ORGANIZATION_ID
, decode(MD2.origination_type, ’29’,’Peg_Fcast’,’30’,’Peg_Sales’) Pegged_To_2
, TRUNC(MS1.NEW_SCHEDULE_DATE) COMP_SUP_DUE
, ‘X’
, DECODE(MD2.ORDER_NUMBER,NULL
,TO_CHAR(MD2.DEMAND_ID)
,(SUBSTR(MD2.ORDER_NUMBER,1,14)||SUBSTR(MD2.ORDER_NUMBER,42,5))) SO_NUM
, MSI2.ITEM_NAME||’ (‘||MSI2.INVENTORY_ITEM_ID||’)’ “ASSEMBLY (ID)”
— , MSI2.DESCRIPTION
, TO_CHAR(MD2.REQUEST_SHIP_DATE,’DD/MM/YYYY’) RDATE
, TRUNC(MD2.USING_ASSEMBLY_DEMAND_DATE) SSDATE
, MD1.Creation_date
, ROUND((MD2.DMD_SATISFIED_DATE-MD2.USING_ASSEMBLY_DEMAND_DATE),1) LATE
, MD2.DEMAND_PRIORITY PRIORITY
, ROUND(MFP2.DEMAND_QUANTITY,1) so_QTY
, ROUND(MFP2.ALLOCATED_QUANTITY,1) WIP_QTY
, TRUNC(MD1.OLD_DEMAND_DATE) ULSD
, TRUNC(MD1.USING_ASSEMBLY_DEMAND_DATE) COMP_DEM_DUE
FROM APPS.MSC_SUPPLIES MS1 — COMP
, APPS.MSC_SYSTEM_ITEMS MSI1 — COMP
, APPS.MSC_FULL_PEGGING MFP1 — COMP
, apps.msc_sales_orders mso
, (SELECT *
FROM APPS.MSC_DEMANDS
WHERE SR_INSTANCE_ID =&1
AND ORGANIZATION_ID =&2
AND PLAN_ID =&3) MD1 — COMP
, (SELECT *
FROM APPS.MSC_FULL_PEGGING
WHERE SR_INSTANCE_ID =&1
AND ORGANIZATION_ID =&2
AND PLAN_ID =&3) MFP2 — FG
, (SELECT *
FROM APPS.MSC_DEMANDS
WHERE SR_INSTANCE_ID =&1
AND ORGANIZATION_ID =&2
AND PLAN_ID =&3) MD2 — FG
, (SELECT *
FROM APPS.MSC_SYSTEM_ITEMS
WHERE SR_INSTANCE_ID = &1
AND ORGANIZATION_ID =&2
AND PLAN_ID =&3) MSI2 — FG LEVEL
WHERE 1=1
AND MS1.SR_INSTANCE_ID =&1
AND MS1.ORGANIZATION_ID =&2
AND MS1.PLAN_ID =&3
and mso.demand_id=md1.demand_id
AND MSI1.SR_INSTANCE_ID = MS1.SR_INSTANCE_ID
AND MSI1.ORGANIZATION_ID = MS1.ORGANIZATION_ID
AND MSI1.PLAN_ID = MS1.PLAN_ID
AND MSI1.INVENTORY_ITEM_ID = MS1.INVENTORY_ITEM_ID
AND MFP1.SR_INSTANCE_ID = MS1.SR_INSTANCE_ID
AND MFP1.ORGANIZATION_ID = MS1.ORGANIZATION_ID
AND MFP1.PLAN_ID = MS1.PLAN_ID
AND MFP1.TRANSACTION_ID = MS1.TRANSACTION_ID
AND MD1.DEMAND_ID (+) = MFP1.DEMAND_ID
AND MFP2.PEGGING_ID (+) = MFP1.PREV_PEGGING_ID
AND MD2.DEMAND_ID (+) = MFP2.DEMAND_ID
AND MSI2.INVENTORY_ITEM_ID (+) = MFP2.INVENTORY_ITEM_ID
AND MSI1.ITEM_NAME = ‘item’
and md1.order_number=’ord’
ORDER BY MS1.OLD_SCHEDULE_DATE, MD2.REQUEST_SHIP_DATE

April 22, 2015/by Oracle ERP Apps Guide

Apps Technical, Basic Concepts, DBA, ERP, Oracle Apps, PL SQL, SQL, Toad

Re-Compile Invalid Schema Objects in Oracle Database

Operations such as upgrades, patches and DDL changes can invalidate schema objects. Provided these changes don’t cause compilation failures the objects will be revalidated by on-demand automatic recompilation, but this can take an unacceptable time to complete, especially where complex dependencies are present. For this reason it makes sense to recompile invalid objects in advance of user calls. It also allows you to identify if any changes have broken your code base. This article presents several methods for recompiling invalid schema objects.

Identifying Invalid Objects
The Manual Approach
Custom Script
DBMS_UTILITY.compile_schema
UTL_RECOMP
utlrp.sql and utlprp.sql
Identifying Invalid Objects

The DBA_OBJECTS view can be used to identify invalid objects using the following query.

COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = ‘INVALID’
ORDER BY owner, object_type, object_name;
With this information you can decide which of the following recompilation methods is suitable for you.

The Manual Approach

For small numbers of objects you may decide that a manual recompilation is sufficient. The following example shows the compile syntax for several object types.

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;
Notice that the package body is compiled in the same way as the package specification, with the addition of the word “BODY” at the end of the command.

An alternative approach is to use the DBMS_DDL package to perform the recompilations.

EXEC DBMS_DDL.alter_compile(‘PACKAGE’, ‘MY_SCHEMA’, ‘MY_PACKAGE’);
EXEC DBMS_DDL.alter_compile(‘PACKAGE BODY’, ‘MY_SCHEMA’, ‘MY_PACKAGE’);
EXEC DBMS_DDL.alter_compile(‘PROCEDURE’, ‘MY_SCHEMA’, ‘MY_PROCEDURE’);
EXEC DBMS_DDL.alter_compile(‘FUNCTION’, ‘MY_SCHEMA’, ‘MY_FUNCTION’);
EXEC DBMS_DDL.alter_compile(‘TRIGGER’, ‘MY_SCHEMA’, ‘MY_TRIGGER’);

This method is limited to PL/SQL objects, so it is not applicable for views.

Custom Script

In some situations you may have to compile many invalid objects in one go. One approach is to write a custom script to identify and compile the invalid objects. The following example identifies and recompiles invalid packages and package bodies.

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT owner,
object_name,
object_type,
DECODE(object_type, ‘PACKAGE’, 1,
‘PACKAGE BODY’, 2, 2) AS recompile_order
FROM dba_objects
WHERE object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)
AND status != ‘VALID’
ORDER BY 4)
LOOP
BEGIN
IF cur_rec.object_type = ‘PACKAGE’ THEN
EXECUTE IMMEDIATE ‘ALTER ‘ || cur_rec.object_type ||
‘ “‘ || cur_rec.owner || ‘”.”‘ || cur_rec.object_name || ‘” COMPILE’;
ElSE
EXECUTE IMMEDIATE ‘ALTER PACKAGE “‘ || cur_rec.owner ||
‘”.”‘ || cur_rec.object_name || ‘” COMPILE BODY’;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ‘ : ‘ || cur_rec.owner ||
‘ : ‘ || cur_rec.object_name);
END;
END LOOP;
END;
/
This approach is fine if you have a specific task in mind, but be aware that you may end up compiling some objects multiple times depending on the order they are compiled in. It is probably a better idea to use one of the methods provided by Oracle since they take the code dependencies into account.

DBMS_UTILITY.compile_schema

The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus.

EXEC DBMS_UTILITY.compile_schema(schema => ‘SCOTT’);
UTL_RECOMP

The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads. Their definitions are listed below.

PROCEDURE RECOMP_SERIAL(
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);

PROCEDURE RECOMP_PARALLEL(
threads IN PLS_INTEGER DEFAULT NULL,
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);

The usage notes for the parameters are listed below.
schema – The schema whose invalid objects are to be recompiled. If NULL all invalid objects in the database are recompiled.
threads – The number of threads used in a parallel operation. If NULL the value of the “job_queue_processes” parameter is used. Matching the number of available CPUs is generally a good starting point for this value.
flags – Used for internal diagnostics and testing only.

The following examples show how these procedures are used.
— Schema level.
EXEC UTL_RECOMP.recomp_serial(‘SCOTT’);
EXEC UTL_RECOMP.recomp_parallel(4, ‘SCOTT’);

— Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

— Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, ‘SCOTT’);

There are a number of restrictions associated with the use of this package including:

Parallel execution is performed using the job queue. All existing jobs are marked as disabled until the operation is complete.
The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM to be present and valid.
Running DDL operations at the same time as this package may result in deadlocks.
utlrp.sql and utlprp.sql

The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of “0”. The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows.

0 – The level of parallelism is derived based on the CPU_COUNT parameter.
1 – The recompilation is run serially, one object at a time.
N – The recompilation is run in parallel with “N” number of threads.
Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

For more information see:
DBMS_UTILITY.compile_schema
UTL_RECOMP

April 21, 2015/by Oracle ERP Apps Guide

Apps Technical, OPM, Oracle Apps, Query, Scripts, SLA, Toad

Query for Link Between SLA and Opm Accounting preprocessor Tables in R12

SELECT GEH.REFERENCE_NO,
GEH.TRANSACTION_ID,
GEH.TRANSACTION_DATE,
gel.line_number,
gel.journal_line_type,
gel.entered_amount,
gel.accounted_amount,
FND_FLEX_EXT.GET_SEGS(‘SQLGL’, ‘GL#’,gcc.chart_of_accounts_id,gcc.code_combination_id) account,
GL_FLEXFIELDS_PKG.get_concat_description( gcc.chart_of_accounts_id, gcc.code_combination_id) acc_description
FROM gl_code_combinations gcc,
xla_ae_lines al,
xla_distribution_links dl,
gmf_xla_extract_headers geh,
gmf_xla_extract_lines gel
WHERE gcc.code_combination_id = al.code_combination_id
AND al.ae_header_id = dl.ae_header_id
AND al.ae_line_num = dl.ae_line_num
AND dl.event_id = geh.event_id
AND dl.application_id = 555
AND dl.source_distribution_type = geh.entity_code
AND dl.source_distribution_id_num_1 = gel.line_id
AND geh.header_id = gel.header_id
and geh.event_id = gel.event_id
–AND gel.journal_line_type IN (‘COGS’,’DCOG’)
AND geh.transaction_date >= TO_DATE(‘&&fromDate’
||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’) –period start date 01/01/2011
AND geh.transaction_date <= TO_DATE(‘&&toDate’
||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’) –Period end date 01/31/2011

April 21, 2015/by Oracle ERP Apps Guide

R12 Supplier Contact creation API

How to Reading XML data using PLSQL

Query to find Pegging Details in ASCP

Re-Compile Invalid Schema Objects in Oracle Database

Query for Link Between SLA and Opm Accounting preprocessor Tables in R12

Recent Comments

Latest Posts

Ads Blocker Detected!!!

Share this:

Share this:

Share this:

Share this:

Share this:

Recent Comments

Latest Posts

Ads Blocker Detected!!!