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;
How to Reading XML data using PLSQL
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
Share this:
How to Create RMA in R12 Order Management
I am taking the case of creation of Return Sales Order with Receipt and Credit Memo.
1.Create New Sales Order with Order Type = “Return Only . ( we can take Mixed or any other that allow return line)
2.Since Header Order Type is “Return Only”, based on the transaction Type Setup , System will default Line Type .In transaction type set the Default RMA = “Return (Receipt)” , with this Setup system will populate the Line Type = “Return (Receipt)” , enter other details like
Item #
Qty etc.
3.Go to Return tab ( Lines) and Enter Return Reason (There is LOV for Return Reason select any relevent value from LOV ).
4.Book the RMA.
5.Requery the RMA , check the Line Status , It should be “Awaiting Return”.
6.Select Receipt (Purchasing Responsibility)
7.Goto Customer Tab and Enter the RMA Num .
8.Press Find Button.
9.Ignore the receipt Header , and go to Receipts form
10.Select the line
11.Press Save,System will ask for the Sub Inventory.Best bet is enter “Stores” ( Here I am taking the case Routing = Direct Delivery)
12.Press Save Button. Save action will automatically submit the “Receiving Transaction Processor (RTP) concurrent Program.
13.Check Order line status , it should be “Returned”.
14.Go to View > request . Submit Concurrent Program “Workflow background Process”
with Item Type = OM Order Line
Process Deferred = Yes
Process Timeout = No.
Query Order line again , Status Should be “Closed”
15.Step # 14 also submit “Autoinvoice” concurrent Program.
16.Goto Transaction (Receivable Responsibility)
Go to Find Window
Enter Sales Order # in Sales Order Number Field
Press Find
System should display your Invoice (Credit memo)
Examine the Invoice and you are good to go.
Share this:
Oracle R12 – RFQ to PO Receipt Cycle
High -Lights of this Cycle is to Create:
Once RFQ is created , Submit concurrent job “Request to Print the RFQ” for a supplier. On completion this job will increment print count for that supplier as shown below.
Once we print the RFQ , status of RFQ become Printed , and print count will increment for all suppliers .Since we got response from the Office Supplier , Inc Site – OFFICESUPPLIER , Responded field populated for this supplier only .
Share this:
How to Get GL Code Combination Description
SELECT gcc.segment1
||’.’
||gcc.segment2
||’.’
||gcc.segment3 ACCOUNT,
A1.DESCRIPTION
||’-‘
||A2.DESCRIPTION
||’-‘
|| A3.DESCRIPTION DECS
FROM fnd_flex_values_vl A1,
fnd_flex_values_vl A2,
fnd_flex_values_vl A3,
gl_code_combinations gcc
WHERE a1.flex_value =gcc.segment1
AND a1.FLEX_VALUE_SET_ID IN
(SELECT FLEX_VALUE_SET_ID
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = ‘GL#’
AND enabled_flag = ‘Y’
AND application_column_name=’SEGMENT1′
)
AND a2.flex_value =gcc.segment2
AND a2.FLEX_VALUE_SET_ID IN
(SELECT FLEX_VALUE_SET_ID
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = ‘GL#’
AND enabled_flag = ‘Y’
AND application_column_name=’SEGMENT2′
)
AND a3.flex_value =gcc.segment3
AND a3.FLEX_VALUE_SET_ID IN
(SELECT FLEX_VALUE_SET_ID
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = ‘GL#’
AND enabled_flag = ‘Y’
AND application_column_name=’SEGMENT3′
);
Script 2: ( Applicable in R12)
SELECT gcc.CONCATENATED_SEGMENTS,
gl_flexfields_pkg.get_concat_description( gcc.chart_of_accounts_id, gcc.code_combination_id) acc_description
FROM gl_code_combinations_kfv gcc;
Share this:
Important OPM Process Execution & Inventory Tables in R12
OPM PROCESS EXECUTION TABLES R12
Share this: