Home

Thursday, August 7

XML Parsing -reading xml file in oracle pl sql example

Please find the below stand alone script followed by sample XML file that I am using for this script.

Steps to be performed.

create a XML file with name OMX_Cxml.xml (by copying the XML file data provided at the end of this post).
Place the xml file in any server path ( in this example I have placed the file in USER_TEMP directory) directory namd and path can be seen in all_directories table
Execute the below script and understand how am i reading each node and attribute value also How am i handling the loops for repeating nodes .
Modify the script as per your xml file.

declare
-- below variables are used in XML parsing or for api dbms_xmlparser
L_BFILE bfile;                              -- this holds the binary file (from respective path)
L_CLOB CLOB;                                -- this is to read the data from bfile into clob type
l_parser dbms_xmlparser.parser;             -- this variable for parsing clob value to XMLparser
MY_DOC DBMS_XMLDOM.DOMDOCUMENT;             -- this vairable shall hold XML parsed data
CURRENT_ITEM_LIST DBMS_XMLDOM.DOMNODELIST; -- this represents the XML group of nodes (1 or repeating) and all it sub tags
CURRENT_ITEM DBMS_XMLDOM.DOMNODE;           -- this represents the XML group of nodes (only one instance of repeating group) and all it sub tags
CURRENT_ITEM_LIST1 DBMS_XMLDOM.DOMNODELIST; -- this represents the XML group of nodes (1 or repeating) and all it sub tags
CURRENT_ITEM1 DBMS_XMLDOM.DOMNODE;          -- this represents the XML group of nodes (only one instance of repeating group) and all it sub tags
XMLELEM XMLDOM.DOMELEMENT;                  --This will read the root element information. In our file rooot element is :<cXML ..>
NSPACE VARCHAR2(50);                        --This will read name sapce of root element information. In our file rooot element is :<cXML ..> and no namespaces are defined in file
--- below variables are to be passed as parameters for DBMS_LOB.LOADCLOBFROMFILE
L_DEST_OFFSET NUMBER :=1;
L_SRC_OFFSET   NUMBER :=1;
L_BFILE_CSID   NUMBER := 0;
L_LANG_CONTEXT NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
L_WARNING      NUMBER;
-- Declare local variables the correspond to the XML data
-- in more serious implementation , consider using RECORD type
---Header information
from_credential     VARCHAR2(1000);
from_identity       VARCHAR2(1000);
from_sharedsecret   VARCHAR2(1000);
to_credential       VARCHAR2(1000);
to_identity         VARCHAR2(1000);
to_sharedsecret     VARCHAR2(1000);
sender_credential   VARCHAR2(1000);
sender_identity     VARCHAR2(1000);
sender_sharedsecret VARCHAR2(1000);
SENDER_USERAGENT    VARCHAR2(1000);
------------------
---Request information
L_DEPLOYMENTMODE     VARCHAR2(1000);
L_INVOICEID          VARCHAR2(1000);
L_PURPOSE            VARCHAR2(1000);
L_OPERATION          VARCHAR2(1000);
L_INVOICEDATE        VARCHAR2(1000);
L_ISACCOUNTINGINLINE VARCHAR2(1000);
L_PAYINNUMBEROFDAYS NUMBER;
L_PERCENTAGERATE     NUMBER;
L_EXTRINSIC_NAME     VARCHAR2(1000);
L_EXTRINSIC_value    VARCHAR2(1000);
--invoice partner information
L_CONTACT_ROLE                VARCHAR2(1000);
L_CONTACT_NAME                VARCHAR2(1000);
L_CONTACT_STREET1             VARCHAR2(1000);
L_CONTACT_STREET2             VARCHAR2(1000);
L_CONTACT_CITY                VARCHAR2(1000);
L_CONTACT_STATE               VARCHAR2(1000);
L_CONTACT_POSTALCODE          VARCHAR2(1000);
L_CONTACT_COUNTRY             VARCHAR2(1000);
L_CONTACT_FROM_NAME           VARCHAR2(1000);
L_CONTACT_FROM_STREET1        VARCHAR2(1000);
L_CONTACT_FROM_STREET2        VARCHAR2(1000);
L_CONTACT_FROM_CITY           VARCHAR2(1000);
L_CONTACT_FROM_STATE          VARCHAR2(1000);
L_CONTACT_FROM_POSTALCODE     VARCHAR2(1000);
L_CONTACT_FROM_COUNTRY        VARCHAR2(1000);
L_CONTACT_SOLDTO_NAME         VARCHAR2(1000);
L_CONTACT_SOLDTO_STREET1      VARCHAR2(1000);
L_CONTACT_SOLDTO_STREET2      VARCHAR2(1000);
L_CONTACT_SOLDTO_CITY         VARCHAR2(1000);
L_CONTACT_SOLDTO_STATE        VARCHAR2(1000);
L_CONTACT_SOLDTO_POSTALCODE   VARCHAR2(1000);
L_CONTACT_SOLDTO_COUNTRY      VARCHAR2(1000);
L_CONTACT_BILLTO_NAME         VARCHAR2(1000);
L_CONTACT_BILLTO_STREET1      VARCHAR2(1000);
L_CONTACT_BILLTO_STREET2      VARCHAR2(1000);
L_CONTACT_BILLTO_CITY         VARCHAR2(1000);
L_CONTACT_BILLTO_STATE        VARCHAR2(1000);
L_CONTACT_BILLTO_POSTALCODE   VARCHAR2(1000);
L_CONTACT_BILLTO_COUNTRY      VARCHAR2(1000);
L_CONTACT_RMTTO_NAME          VARCHAR2(1000);
L_CONTACT_RMTTO_STREET1       VARCHAR2(1000);
L_CONTACT_RMTTO_STREET2       VARCHAR2(1000);
L_CONTACT_RMTTO_CITY          VARCHAR2(1000);
L_CONTACT_RMTTO_STATE         VARCHAR2(1000);
L_CONTACT_RMTTO_POSTALCODE    VARCHAR2(1000);
L_CONTACT_RMTTO_COUNTRY       VARCHAR2(1000);
L_CONTACT_SHIPROLE            VARCHAR2(1000);
L_CONTACT_SHIPFROM_NAME       VARCHAR2(1000);
L_CONTACT_SHIPFROM_STREET1    VARCHAR2(1000);
L_CONTACT_SHIPFROM_STREET2    VARCHAR2(1000);
L_CONTACT_SHIPFROM_CITY       VARCHAR2(1000);
L_CONTACT_SHIPFROM_STATE      VARCHAR2(1000);
L_CONTACT_SHIPFROM_POSTALCODE VARCHAR2(1000);
L_CONTACT_SHIPFROM_COUNTRY    VARCHAR2(1000);
L_CONTACT_SHIPTO_NAME         VARCHAR2(1000);
L_CONTACT_SHIPTO_STREET1      VARCHAR2(1000);
L_CONTACT_SHIPTO_STREET2      VARCHAR2(1000);
L_CONTACT_SHIPTO_CITY         VARCHAR2(1000);
L_CONTACT_SHIPTO_STATE        VARCHAR2(1000);
L_CONTACT_SHIPTO_POSTALCODE   VARCHAR2(1000);
L_CONTACT_SHIPTO_COUNTRY      VARCHAR2(1000);
L_ORDER_ID                    NUMBER;
L_PAYLOADID                   VARCHAR2(1000);
L_INVOICELINENUMBER           NUMBER;
L_QUANTITY                    NUMBER;
L_UNITOFMEASURE               VARCHAR2(100);
L_CURRENCY                    VARCHAR2(100);
L_UNITPRICE                   VARCHAR2(100);
L_INV_DET_ITEM_REF_LINENUM    NUMBER;
L_SUPPLIERPARTID              VARCHAR2(100);
L_DESCRIPTION                 VARCHAR2(1000);
L_MANUFACTURERPARTID          VARCHAR2(1000);
L_MANUFACTURERNAME            VARCHAR2(1000);
L_SUBTOTALAMOUNT_CUR          VARCHAR2(1000);
L_SUBTOTALAMOUNT              NUMBER;
L_GROSSAMOUNT_CUR             VARCHAR2(1000);
L_GROSSAMOUNT                 NUMBER;
L_NETAMOUNT_CUR               VARCHAR2(1000);
L_NETAMOUNT                   NUMBER;
L_SRY_SUBTOTAL_CUR            VARCHAR2(1000);
L_SRY_SUBTOTAL_AMOUNT         NUMBER;
L_SRY_TAX_CUR                 VARCHAR2(1000);
L_SRY_TAX_AMOUNT              NUMBER;
L_SRY_TAX_PURPOSE             VARCHAR2(1000);
L_SRY_TAX_CATEGORY            VARCHAR2(1000);
L_SRY_TAX_RATE                VARCHAR2(1000);
L_SRY_TAXABLE_CUR             VARCHAR2(1000);
L_SRY_TAXABLE_AMOUNT          NUMBER;
L_SRY_TAX_CUR1                VARCHAR2(1000);
L_SRY_TAX_AMOUNT1             NUMBER;
L_SRY_GROSS_CUR               VARCHAR2(1000);
L_SRY_GROSS_AMOUNT            NUMBER;
L_SRY_NET_CUR                 VARCHAR2(1000);
L_SRY_NET_AMOUNT              NUMBER;
L_SRY_DUE_CUR                 VARCHAR2(1000);
L_SRY_DUE_AMOUNT              NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('begin :');
-- Point to the XML file called 'OMX_Cxml.xml'which is located in the directory_path pointed at USER_TEMP in table all_directories
L_BFILE := BFILENAME('USER_TEMP', 'OMX_Cxml.xml');
-- creating a temporary space for clob variable with out cache so that it will not have any performance impact
DBMS_LOB.CREATETEMPORARY(L_CLOB, cache=>false);
-- Open the XML file in read mode
dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
-- tranfer the file contents to CLOB variable
--DBMS_LOB.LOADFROMFILE(DEST_LOB => L_CLOB, SRC_LOB => L_BFILE, AMOUNT => DBMS_LOB.GETLENGTH(L_BFILE));
dbms_lob.loadclobfromfile(dest_lob => l_clob, src_bfile => L_BFILE, AMOUNT => DBMS_LOB.GETLENGTH(L_BFILE), DEST_OFFSET => L_DEST_OFFSET, SRC_OFFSET => L_SRC_OFFSET, BFILE_CSID => DBMS_LOB.DEFAULT_CSID, LANG_CONTEXT => L_LANG_CONTEXT, warning => l_warning );
--close the bfile instance
dbms_lob.close(l_bfile);
-- This ends file manipulation and the file contents are now in a CLOB vairable
-- This entry is important to deal with DATE format in the XML data
dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YYYY''');
-- Starting XML processing business
-- Create a parser.
L_PARSER := DBMS_XMLPARSER.NEWPARSER;
-- Parse the document and create a new DOM document.
-- temporarily I am prting the XML data for testing
--DBMS_OUTPUT.PUT_LINE('parser : l_clob---'||L_CLOB);
-- parse the xml data from clob variabl to XML_parser
--DBMS_XMLPARSER.PARSECLOB(L_PARSER,L_CLOB);
DBMS_XMLPARSER.PARSECLOB(L_PARSER, REGEXP_REPLACE(L_CLOB,'<!DOCTYPE cXML SYSTEM "http://xml.cxml.org/schemas/cXML/1.2.009/InvoiceDetail.dtd">',''));
-- The xml parsed CLOB is transfered to a variable called my_doc
my_doc := dbms_xmlparser.getdocument(l_parser);
xmlelem := xmldom.getdocumentelement(my_doc);
nspace := xmldom.getnamespace(xmlelem);
DBMS_OUTPUT.PUT_LINE('XML Root element information');
DBMS_OUTPUT.PUT_LINE('Qualified Name: ' || DBMS_XMLDOM.GETQUALIFIEDNAME(XMLELEM));
DBMS_OUTPUT.PUT_LINE('Local Name: ' || DBMS_XMLDOM.GETLOCALNAME(XMLELEM));
DBMS_OUTPUT.PUT_LINE('Namespace: ' || NSPACE);
DBMS_OUTPUT.PUT_LINE('Expanded Name: ' || DBMS_XMLDOM.GETEXPANDEDNAME(XMLELEM));
-- Free resources associated with the CLOB and Parser now they are no longer needed.
dbms_lob.freetemporary(l_clob);
DBMS_XMLPARSER.FREEPARSER(L_PARSER);
dbms_output.put_line('parser free:');
-- The following statement shall search for a node starting with 'cXML/Header'
-- Then it shall get the no of entries in this node. In our example the 'cXML/Header' contains only one
-- entry, but a big one, because it contains from to and sender XML (there is ony one <Header> </Header>
current_item_list := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(my_doc),'cXML/Header');
-- note no slash at end
DBMS_OUTPUT.PUT_LINE('#############     header loop begin #################');
dbms_output.put_line('Count of cXML/Header : '||dbms_xmldom.getLength(current_item_list)); -- find the no of entries in this node
FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1
LOOP -- it should loop only once because the Header is only one instance

    current_item := dbms_xmldom.item(current_item_list, cur_ent); --READ the current item from current entry of <Header>
    DBMS_OUTPUT.PUT_LINE('current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM));
    --dbms_output.put_line('current_item:'|| dbms_xmldom.getnodevalue(current_item)); --this will not work in our case
    --#############-------------Header/From----------------##############
    dbms_output.put_line('---------------From----------------');
    dbms_xslprocessor.valueof(current_item,'From/Credential/@domain',from_credential);
    dbms_output.put_line('From/Credential/@domain: '||from_credential);
    dbms_xslprocessor.valueof(current_item,'From/Credential/Identity/text()',from_identity);
    dbms_output.put_line('From/Credential/Identity:'||from_identity);
    dbms_xslprocessor.valueof(current_item,'From/Credential/SharedSecret/text()',from_sharedsecret);
    DBMS_OUTPUT.PUT_LINE('From/Credential/SharedSecret:'||FROM_SHAREDSECRET);
    --#############-------------Header/To----------------##############
    dbms_output.put_line('---------------To----------------');
    dbms_xslprocessor.valueof(current_item,'To/Credential/@domain',to_credential);
    dbms_output.put_line('From/Credential/@domain: '||to_credential);
    dbms_xslprocessor.valueof(current_item,'To/Credential/Identity/text()',to_identity);
    dbms_output.put_line('To/Credential/Identity:'||to_identity);
    dbms_xslprocessor.valueof(current_item,'To/Credential/SharedSecret/text()',to_sharedsecret);
    DBMS_OUTPUT.PUT_LINE('To/Credential/SharedSecret:'||TO_SHAREDSECRET);
    --#############-------------Header/Sender----------------##############
    dbms_output.put_line('---------------Sender----------------');
    dbms_xslprocessor.valueof(current_item,'Sender/Credential/@domain',Sender_credential);
    dbms_output.put_line('Sender/Credential/@domain: '||Sender_credential);
    dbms_xslprocessor.valueof(current_item,'Sender/Credential/Identity/text()',sender_identity);
    dbms_output.put_line('Sender/Credential/Identity:'||sender_identity);
    dbms_xslprocessor.valueof(current_item,'Sender/Credential/SharedSecret/text()',sender_sharedsecret);
    dbms_output.put_line('Sender/Credential/SharedSecret:'||sender_sharedsecret);
    dbms_xslprocessor.valueof(current_item,'Sender/UserAgent/text()',sender_UserAgent);
    DBMS_OUTPUT.PUT_LINE('Sender/UserAgent:'||SENDER_USERAGENT);
END LOOP;
--Now the following statement shall search for a node starting with 'cXML/Request'
-- Then it shall get the no of entries in this node. In our example the 'cXML/Request' contains only one
-- entry, but a big one, because it contains from to and sender XML (there is ony one <Request> </Request>
current_item_list := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(my_doc),'cXML/Request');
-- note no slash at end
DBMS_OUTPUT.PUT_LINE('#############     Request loop begin      #################');
dbms_output.put_line('Count of cXML/Request : '||dbms_xmldom.getLength(current_item_list)); -- find the no of entries in this node
FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1
LOOP -- it should loop only once because the Request is only one instance

    current_item := dbms_xmldom.item(current_item_list, cur_ent); --READ the current item from current entry of <Header>
    DBMS_OUTPUT.PUT_LINE('current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM));
    --dbms_output.put_line('current_item:'|| dbms_xmldom.getnodevalue(current_item)); --this will not work in our case
    DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'@deploymentMode',L_DEPLOYMENTMODE);
    DBMS_OUTPUT.PUT_LINE('Request/@domain: '||l_deploymentMode);
    CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest');
    -- note no slash at end
    DBMS_OUTPUT.PUT_LINE('#############     InvoiceDetailRequest loop begin      #################');
    dbms_output.put_line('Count of cXML/Request/InvoiceDetailRequest: '||dbms_xmldom.getLength(current_item_list)); -- find the no of entries in this node
    FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1
    LOOP

      CURRENT_ITEM := DBMS_XMLDOM.ITEM(CURRENT_ITEM_LIST, CUR_ENT);
      DBMS_OUTPUT.PUT_LINE('current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM));
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/@invoiceID',L_INVOICEID);
      dbms_output.put_line('InvoiceDetailRequestHeader/@invoiceID:'||L_INVOICEID );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/@purpose',L_PURPOSE);
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailRequestHeader/@purpose:'||L_PURPOSE );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/@operation',L_OPERATION );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailRequestHeader/@operation:'||L_OPERATION );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/@invoiceDate',L_INVOICEDATE );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailRequestHeader/@invoiceDate:'||L_INVOICEDATE );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/InvoiceDetailLineIndicator/@isAccountingInLine',L_ISACCOUNTINGINLINE );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailLineIndicator/@isAccountingInLine:'||L_ISACCOUNTINGINLINE );
      --- retriving the InvoiceDetailPaymentTerm and Extrinsic details before re-initialinf current item list for retriving InvoicePartner & InvoiceDetailShipping
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/InvoiceDetailPaymentTerm/@payInNumberOfDays',l_payInNumberOfDays );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailPaymentTerm/@payInNumberOfDays:'||l_payInNumberOfDays );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/InvoiceDetailPaymentTerm/@percentageRate',l_percentageRate );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailPaymentTerm/@percentageRate:'||l_percentageRate );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/Extrinsic/@name',l_Extrinsic_name );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailRequestHeader/Extrinsic/@name'||L_EXTRINSIC_NAME );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/Extrinsic/text()',l_Extrinsic_value );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailPaymentTerm/@percentageRate:'||l_Extrinsic_value );
      CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest/InvoiceDetailRequestHeader/InvoicePartner');
      -- note no slash at end

      DBMS_OUTPUT.PUT_LINE('#############     InvoicePartner loop begin      #################');
      DBMS_OUTPUT.PUT_LINE('Count of cXML/Request/InvoiceDetailRequest/InvoiceDetailRequestHeader/InvoicePartner'||DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)); -- find the no of entries in this node
      FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1
      LOOP

        CURRENT_ITEM := DBMS_XMLDOM.ITEM(CURRENT_ITEM_LIST, CUR_ENT);
        DBMS_OUTPUT.PUT_LINE('##############current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM)||' - ' ||CUR_ENT);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/@role',L_CONTACT_ROLE );
        DBMS_OUTPUT.PUT_LINE('Contact/@role : '||L_CONTACT_ROLE );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/Name/text()', L_CONTACT_NAME);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/Street[position()=1]/text()', L_CONTACT_STREET1);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/Street[position()=2]/text()', L_CONTACT_STREET2);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/City/text()', L_CONTACT_CITY);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/State/text()', L_CONTACT_STATE);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/PostalCode/text()', L_CONTACT_POSTALCODE);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/Country/text()', L_CONTACT_COUNTRY);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Name: '|| L_CONTACT_NAME);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' City: '|| L_CONTACT_CITY);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' State: '|| L_CONTACT_STATE);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Postalcode: '|| L_CONTACT_POSTALCODE);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Country: '|| L_CONTACT_COUNTRY);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Street1: '|| L_CONTACT_STREET1);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Street2: '|| L_CONTACT_STREET2);
        IF L_CONTACT_ROLE              = 'from' THEN
          L_CONTACT_FROM_NAME         := L_CONTACT_NAME;
          L_CONTACT_FROM_CITY         := L_CONTACT_CITY;
          L_CONTACT_FROM_STATE        := L_CONTACT_STATE;
          L_CONTACT_FROM_POSTALCODE   := L_CONTACT_POSTALCODE;
          L_CONTACT_FROM_COUNTRY      := L_CONTACT_COUNTRY ;
          L_CONTACT_FROM_Street1      := L_CONTACT_STREET1 ;
        elsif L_CONTACT_ROLE           = 'soldTo' THEN
          L_CONTACT_SOLDTO_NAME       := L_CONTACT_NAME;
          L_CONTACT_SOLDTO_CITY       := L_CONTACT_CITY;
          L_CONTACT_SOLDTO_STATE      := L_CONTACT_STATE;
          L_CONTACT_SOLDTO_POSTALCODE := L_CONTACT_POSTALCODE;
          L_CONTACT_SOLDTO_COUNTRY    := L_CONTACT_COUNTRY ;
          L_CONTACT_SOLDTO_Street1    := L_CONTACT_STREET1 ;
        elsif L_CONTACT_ROLE           = 'remitTo' THEN
          L_CONTACT_RMTTO_NAME        := L_CONTACT_NAME;
          L_CONTACT_RMTTO_CITY        := L_CONTACT_CITY;
          L_CONTACT_RMTTO_STATE       := L_CONTACT_STATE;
          L_CONTACT_RMTTO_POSTALCODE := L_CONTACT_POSTALCODE;
          L_CONTACT_RMTTO_COUNTRY     := L_CONTACT_COUNTRY ;
          L_CONTACT_RMTTO_Street1     := L_CONTACT_STREET1 ;
        elsif L_CONTACT_ROLE           = 'billTo' THEN
          L_CONTACT_billTO_NAME       := L_CONTACT_NAME;
          L_CONTACT_BILLTO_CITY       := L_CONTACT_CITY;
          L_CONTACT_BILLTO_STATE      := L_CONTACT_STATE;
          L_CONTACT_BILLTO_POSTALCODE := L_CONTACT_POSTALCODE;
          L_CONTACT_BILLTO_COUNTRY    := L_CONTACT_COUNTRY ;
          L_CONTACT_BILLTO_Street1    := L_CONTACT_STREET1 ;
        END IF;
      END LOOP;
      CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest/InvoiceDetailRequestHeader/InvoiceDetailShipping/Contact');
      -- note no slash at end
     DBMS_OUTPUT.PUT_LINE('#############     InvoiceDetailShipping loop begin      #################');
      DBMS_OUTPUT.PUT_LINE('count of cXML/Request/InvoiceDetailRequest/InvoiceDetailRequestHeader/InvoicePartner'||DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)); -- find the no of entries in this node

      FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1
      LOOP

        CURRENT_ITEM := DBMS_XMLDOM.ITEM(CURRENT_ITEM_LIST, CUR_ENT);
        DBMS_OUTPUT.PUT_LINE('##########current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM)||' - ' ||CUR_ENT);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'@role',L_CONTACT_SHIPROLE );
        DBMS_OUTPUT.PUT_LINE('Contact/@role : '||L_CONTACT_SHIPROLE );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Name/text()', L_CONTACT_NAME);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/Street[position()=1]/text()', L_CONTACT_STREET1);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/Street[position()=2]/text()', L_CONTACT_Street2);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/City/text()', L_CONTACT_CITY);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/State/text()', L_CONTACT_STATE);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/PostalCode/text()', L_CONTACT_POSTALCODE);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/Country/text()', L_CONTACT_COUNTRY);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Name: '|| L_CONTACT_NAME);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' City: '|| L_CONTACT_CITY);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' State: '|| L_CONTACT_STATE);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Postalcode: '|| L_CONTACT_POSTALCODE);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Country: '|| L_CONTACT_COUNTRY);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Street1: '|| L_CONTACT_STREET1);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Street2: '|| L_CONTACT_STREET2);
        IF L_CONTACT_SHIPROLE            = 'shipFrom' THEN
          L_CONTACT_SHIPFROM_NAME       := L_CONTACT_NAME;
          L_CONTACT_SHIPFROM_CITY       := L_CONTACT_CITY;
          L_CONTACT_SHIPFROM_STATE      := L_CONTACT_STATE;
          L_CONTACT_SHIPFROM_POSTALCODE := L_CONTACT_POSTALCODE;
          L_CONTACT_SHIPFROM_COUNTRY    := L_CONTACT_COUNTRY ;
          L_CONTACT_SHIPFROM_STREET1    := L_CONTACT_STREET1 ;
        elsif L_CONTACT_SHIPROLE         = 'shipTo' THEN
          L_CONTACT_SHIPTO_NAME         := L_CONTACT_NAME;
          L_CONTACT_SHIPTO_CITY         := L_CONTACT_CITY;
          L_CONTACT_SHIPTO_STATE        := L_CONTACT_STATE;
          L_CONTACT_SHIPTO_POSTALCODE   := L_CONTACT_POSTALCODE;
          L_CONTACT_SHIPTO_COUNTRY      := L_CONTACT_COUNTRY ;
          L_CONTACT_SHIPTO_STREET1      := L_CONTACT_STREET1 ;
        END IF;
      END LOOP;
      CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest/InvoiceDetailOrder');
      -- note no slash at end
      DBMS_OUTPUT.PUT_LINE('#############     InvoiceDetailOrder loop begin      #################');
      DBMS_OUTPUT.PUT_LINE('count of cXML/Request/InvoiceDetailRequest/InvoiceDetailOrder'||DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)); -- find the no of entries in this node

      FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1
      LOOP
        CURRENT_ITEM := DBMS_XMLDOM.ITEM(CURRENT_ITEM_LIST, CUR_ENT);
        DBMS_OUTPUT.PUT_LINE('#########current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM)||' - ' ||CUR_ENT);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailOrderInfo/OrderReference/@orderID',L_ORDER_ID );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailOrderInfo/OrderReference/@orderID: '||L_ORDER_ID );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailOrderInfo/OrderReference/DocumentReference/@payloadID', L_PAYLOADID );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailOrderInfo/OrderReference/DocumentReference/@payloadID: '||L_PAYLOADID );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/@invoiceLineNumber', L_INVOICELINENUMBER );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/@invoiceLineNumber: '||L_INVOICELINENUMBER );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/@quantity', L_QUANTITY );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/@quantity: '||L_QUANTITY );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/UnitOfMeasure/text()', L_UNITOFMEASURE );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/UnitOfMeasure/text(): '||L_UNITOFMEASURE );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/UnitPrice/Money/@currency', L_CURRENCY );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/UnitPrice/Money/@currency: '||L_CURRENCY );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/UnitPrice/Money/text()', L_UNITPRICE );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/UnitPrice/Money/text(): '||L_UNITPRICE );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/@lineNumber', L_INV_DET_ITEM_REF_LINENUM );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/@lineNumber: '||L_INV_DET_ITEM_REF_LINENUM );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/ItemID/SupplierPartID/text()', L_SUPPLIERPARTID );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/ItemID/SupplierPartID/text(): '||L_SUPPLIERPARTID );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/Description/text()', L_DESCRIPTION );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/Description/text(): '||L_DESCRIPTION );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/ManufacturerPartID/text()', L_MANUFACTURERPARTID );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/ManufacturerPartID/text(): '||L_MANUFACTURERPARTID );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/ManufacturerName/text()', L_MANUFACTURERNAME );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/ManufacturerName/text(): '||L_MANUFACTURERNAME );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/SubtotalAmount/Money/@currency', L_SUBTOTALAMOUNT_CUR );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/SubtotalAmount/Money/@currency: '||L_SUBTOTALAMOUNT_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/SubtotalAmount/Money/text()', L_SUBTOTALAMOUNT );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/SubtotalAmount/Money/text(): '||L_SUBTOTALAMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/GrossAmount/Money/@currency', L_GROSSAMOUNT_CUR );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/GrossAmount/Money/@currency: '||L_GROSSAMOUNT_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/GrossAmount/Money/text()', L_GROSSAMOUNT );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/GrossAmount/Money/text(): '||L_GROSSAMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/NetAmount/Money/@currency', L_NETAMOUNT_CUR );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/NetAmount/Money/@currency: '||L_NETAMOUNT_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/NetAmount/Money/text()', L_NETAMOUNT );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/NetAmount/Money/text(): '||L_NETAMOUNT );
      end LOOP;

      CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest/InvoiceDetailSummary');
      -- note no slash at end
      DBMS_OUTPUT.PUT_LINE('#############     InvoiceDetailSummary loop begin      #################');
      dbms_output.put_line('Count of cXML/Request/InvoiceDetailRequest/InvoiceDetailSummary'||dbms_xmldom.getLength(current_item_list)); -- find the no of entries in this node
      FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1
      LOOP
        CURRENT_ITEM := DBMS_XMLDOM.ITEM(CURRENT_ITEM_LIST, CUR_ENT);
        DBMS_OUTPUT.PUT_LINE('#######current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM)||' - ' ||CUR_ENT);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'SubtotalAmount/Money/@currency', L_SRY_SUBTOTAL_CUR );
        DBMS_OUTPUT.PUT_LINE('SubtotalAmount/Money/@currency: '||L_SRY_SUBTOTAL_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'SubtotalAmount/Money/text()', L_SRY_SUBTOTAL_AMOUNT );
        DBMS_OUTPUT.PUT_LINE('SubtotalAmount/Money/text(): '||L_SRY_SUBTOTAL_AMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/Money/@currency', L_SRY_TAX_CUR );
        DBMS_OUTPUT.PUT_LINE('Tax/Money/@currency: '||L_SRY_TAX_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/Money/text()', L_SRY_TAX_AMOUNT );
        DBMS_OUTPUT.PUT_LINE('Tax/Money/text(): '||L_SRY_TAX_AMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/@purpose', L_SRY_TAX_PURPOSE );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/@purpose: '||L_SRY_TAX_PURPOSE );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/@category', L_SRY_TAX_CATEGORY );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/@category: '||L_SRY_TAX_CATEGORY );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/@percentageRate', L_SRY_TAX_RATE );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/@percentageRate: '||L_SRY_TAX_RATE );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/TaxableAmount/Money/@currency', L_SRY_TAXABLE_CUR );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/TaxableAmount/Money/@currency: '||L_SRY_TAXABLE_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/TaxableAmount/Money/text()', L_SRY_TAXABLE_AMOUNT );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/TaxableAmount/Money/text(): '||L_SRY_TAXABLE_AMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/TaxAmount/Money/@currency', L_SRY_TAX_CUR1 );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/TaxAmount/Money/@currency: '||L_SRY_TAX_CUR1 );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/TaxAmount/Money/text()', L_SRY_TAX_AMOUNT1 );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/TaxAmount/Money/text(): '||L_SRY_TAX_AMOUNT1 );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'GrossAmount/Money/@currency', L_SRY_GROSS_CUR );
        DBMS_OUTPUT.PUT_LINE('GrossAmount/Money/@currency: '||L_SRY_GROSS_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'GrossAmount/Money/text()', L_SRY_GROSS_AMOUNT );
        DBMS_OUTPUT.PUT_LINE('GrossAmount/Money/text(): '||L_SRY_GROSS_AMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'NetAmount/Money/@currency', L_SRY_NET_CUR );
        DBMS_OUTPUT.PUT_LINE('NetAmount/Money/@currency: '||L_SRY_NET_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'NetAmount/Money/text()', L_SRY_NET_AMOUNT );
        DBMS_OUTPUT.PUT_LINE('NetAmount/Money/text(): '||L_SRY_NET_AMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'DueAmount/Money/@currency', L_SRY_DUE_CUR );
        DBMS_OUTPUT.PUT_LINE('DueAmount/Money/@currency: '||L_SRY_DUE_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'DueAmount/Money/text()', L_SRY_DUE_AMOUNT );
        DBMS_OUTPUT.PUT_LINE('DueAmount/Money/text(): '||L_SRY_DUE_AMOUNT );
      END LOOP;
    END LOOP;
END LOOP;
dbms_xmldom.freedocument(my_doc);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('sqlerrm :'||SQLERRM);
dbms_lob.close(l_bfile);
DBMS_LOB.FREETEMPORARY(L_CLOB);
DBMS_XMLPARSER.FREEPARSER(L_PARSER);
DBMS_XMLDOM.FREEDOCUMENT(MY_DOC);
end;
/

-------------------XML File---------------
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE cXML SYSTEM "http://xml.cxml.org/schemas/cXML/1.2.009/InvoiceDetail.dtd"><cXML version="1.2.009" payloadID="1405088578064.1105606342@qa-is82-real01.officemax.omx.com" timestamp="2014-07-11T12:00:00-00:00" xml:lang="en-US">
<Header>
    <From>
      <Credential domain="DUNS">
        <Identity>178923231</Identity>
        <SharedSecret>boise1</SharedSecret>
      </Credential>
    </From>
    <To>
      <Credential domain="DUNS">
        <Identity>119851215</Identity>
      </Credential>
    </To>
    <Sender>
      <Credential domain="OracleSN">
        <Identity>oraclesn</Identity>
        <SharedSecret>boise1</SharedSecret>
      </Credential>
      <UserAgent>Oracle Supplier Network</UserAgent>
    </Sender>
</Header>
<Request deploymentMode="test">
    <InvoiceDetailRequest>
      <InvoiceDetailRequestHeader invoiceID="755055Jun2414" purpose="standard" operation="new" invoiceDate="2014-06-24T12:00:00-00:00">
        <InvoiceDetailHeaderIndicator/>
        <InvoiceDetailLineIndicator isAccountingInLine="yes"/>
        <InvoicePartner>
          <Contact role="from">
            <Name xml:lang="en-US">Laura Sebesta</Name>
            <PostalAddress>
              <Street>800 W. Bryn Mawr Ave.</Street>
              <Street>AMNE / AMSE cXML Profile</Street>
              <City>Itasca</City>
              <State>IL</State>
              <PostalCode>60143</PostalCode>
              <Country isoCountryCode="US">United States</Country>
            </PostalAddress>
          </Contact>
        </InvoicePartner>
        <InvoicePartner>
          <Contact role="soldTo">
            <Name xml:lang="en-US">INTERACTIVE DATA          </Name>
            <PostalAddress>
              <Street>ACCOUNTS PAYABLE          </Street>
              <Street>32 CROSBY DR              </Street>
              <City>BEDFORD            </City>
              <State>MA</State>
              <PostalCode>01730    </PostalCode>
              <Country isoCountryCode="US">United States</Country>
            </PostalAddress>
          </Contact>
        </InvoicePartner>
        <InvoicePartner>
          <Contact role="remitTo">
            <Name xml:lang="en-US">Wachovia Bank</Name>
            <PostalAddress>
              <Street>P.O. Box 101705</Street>
              <City>Atlanta</City>
              <State>GA</State>
              <PostalCode>303921705</PostalCode>
              <Country isoCountryCode="US">United States</Country>
            </PostalAddress>
          </Contact>
          <IdReference identifier="069984151" domain="accountID"/>
        </InvoicePartner>
        <InvoicePartner>
          <Contact role="billTo" addressID="161   ">
            <Name xml:lang="en-US">INTERACTIVE DATA CORP     </Name>
            <PostalAddress>
              <Street>US BOXBOROUGH             </Street>
              <Street>60 CODMAN HILL RD         </Street>
              <City>BOXBOROUGH         </City>
              <State>MA</State>
              <PostalCode>01719    </PostalCode>
              <Country isoCountryCode="US">United States</Country>
            </PostalAddress>
          </Contact>
        </InvoicePartner>
        <InvoiceDetailShipping>
          <Contact role="shipFrom">
            <Name xml:lang="en-US">OfficeMax Contract</Name>
            <PostalAddress>
              <Street>125 Industrial Way</Street>
              <City>Portland</City>
              <State>ME</State>
              <PostalCode>04104</PostalCode>
              <Country isoCountryCode="US">United States</Country>
            </PostalAddress>
          </Contact>
          <Contact role="shipTo">
            <Name xml:lang="en-US">INTERACTIVE DATA CORP     </Name>
            <PostalAddress>
              <Street>US BOXBOROUGH             </Street>
              <Street>60 CODMAN HILL RD         </Street>
              <City>BOXBOROUGH         </City>
              <State>MA</State>
              <PostalCode>01719    </PostalCode>
              <Country isoCountryCode="US">United States</Country>
            </PostalAddress>
          </Contact>
        </InvoiceDetailShipping>
        <InvoiceDetailPaymentTerm payInNumberOfDays="35" percentageRate="0"/>
        <Extrinsic name="SHIPTOPARTNRIDX">161   </Extrinsic>
      </InvoiceDetailRequestHeader>
      <InvoiceDetailOrder>
        <InvoiceDetailOrderInfo>
          <OrderReference orderID="1035023685">
            <DocumentReference payloadID="Office Max.2503690.35088442.TEST.1035023685:0:103"/>
          </OrderReference>
        </InvoiceDetailOrderInfo>
        <InvoiceDetailItem invoiceLineNumber="401" quantity="3">
          <UnitOfMeasure>EA</UnitOfMeasure>
          <UnitPrice>
            <Money currency="USD">148.00</Money>
          </UnitPrice>
          <InvoiceDetailItemReference lineNumber="4">
            <ItemID>
              <SupplierPartID>Q7VG730M</SupplierPartID>
            </ItemID>
            <Description xml:lang="en-US">LCD MONITOR 17"</Description>
            <ManufacturerPartID>VG730M</ManufacturerPartID>
            <ManufacturerName xml:lang="en-US">VIEWSO</ManufacturerName>
          </InvoiceDetailItemReference>
          <SubtotalAmount>
            <Money currency="USD">00444.00</Money>
          </SubtotalAmount>
          <GrossAmount>
            <Money currency="USD">444</Money>
          </GrossAmount>
          <NetAmount>
            <Money currency="USD">00444.00</Money>
          </NetAmount>
        </InvoiceDetailItem>
      </InvoiceDetailOrder>
      <InvoiceDetailSummary>
        <SubtotalAmount>
          <Money currency="USD">444.00</Money>
        </SubtotalAmount>
        <Tax>
          <Money currency="USD">27.75</Money>
          <Description xml:lang="en-US"/>
          <TaxDetail purpose="tax" category="sales" percentageRate="6.25">
            <TaxableAmount>
              <Money currency="USD">444.00</Money>
            </TaxableAmount>
            <TaxAmount>
              <Money currency="USD">27.75</Money>
            </TaxAmount>
            <TaxLocation xml:lang="en-US">usa</TaxLocation>
          </TaxDetail>
        </Tax>
        <GrossAmount>
          <Money currency="USD">471.75</Money>
        </GrossAmount>
        <NetAmount>
          <Money currency="USD">471.75</Money>
        </NetAmount>
        <DueAmount>
          <Money currency="USD">471.75</Money>
        </DueAmount>
      </InvoiceDetailSummary>
    </InvoiceDetailRequest>
</Request>
</cXML>
------------------------------------------------------------
Issues occured:

1. when using this DBMS_LOB.LOADFROMFILE(DEST_LOB => L_CLOB, SRC_LOB => L_BFILE, AMOUNT => DBMS_LOB.GETLENGTH(L_BFILE));
   the data in xml file is reading in unknown characterset

   Resolved: by using DBMS_LOB.LOADCLOBFROMFILE

2. when using this dbms_xmlparser.parseclob(l_parser,l_clob) getting the below error
    sqlerrm :ORA-31020: The operation is not allowed, Reason: For security reasons, ftp and http access over XDB repository is not allowed on server side
    resolved : by using
        dbms_xmlparser.parseclob(l_parser, REGEXP_REPLACE(l_clob,'<!DOCTYPE cXML SYSTEM "http://xml.cxml.org/schemas/cXML/1.2.009/InvoiceDetail.dtd">',''));

3. could not able to read the file when file is in "/idc/dev/app/apps/apps_st/appl/zzcus/12.0.0/XMLDIR" and getting the below error.
        ORA-22288: file or LOB operation FILEOPEN failed
            No such file or directory
    temporarily Resolved : by placing the file in /idc/dev/csf/tmp.

4. Able to read the node values but not attribute values
    resolved : by using
   for Ex: <Identity>178923231</Identity> by using: dbms_xslprocessor.valueof(current_item,'From/Credential/Identity/text()',from_identity)
   for Ex: <Credential domain="OracleSN"> by using: dbms_xslprocessor.valueof(current_item,'From/Credential/@domain',from_credential);

Thursday, July 24

Displaying concurrent programm name dynamically on a report

for displaying concurrent programm name dynamically in a report
declare user parameter as p_conc_request_id
write the below commnds in triggers as
srw.user_exit('fnd srwinit') in before report trigger and
srw.user_exit('fnd srwexit') in after report trigger.

and take one place holder column in report level... so, now we r going to use this place holder column in one formaula column.
for that take one formula column and in that we want to write a piece of code which is shown below....

select fcp.user_concurrent_program_name
into :cp_conc_name(place holder column name)
from fnd_concurrent_requests fcr,
fnd_concurrent_programs_vl fcp
where fcr.concurrent_program_id=fcp.concurrent_program_id
and fcr.request_id             =nvl(:p_conc_request_id,fcr.request_id);
and for request id we can

declare
    in same in formula column
as
    return(fnd_profile.value(':p_conc_request_id'));

P2P Overview

Procure to Pay:

First let’s see what the heading itself means? Procure to Pay means Procuring Raw Materials required to manufacture the final or finished Goods to Paying the Supplier from whom the material was purchased. But this is not just two steps. It involves many steps. Let’s see the steps and Oracle Application involved in performing those steps.

1. Oracle Purchasing: You enter Suppliers of different materials and products you want to purchase to manufacture a finished good that your organization plans to sell.

2. Oracle Purchasing: You prepare a Request for Quotation (RFQ) and send it to different suppliers to get the best and/or economical price for the product.

3. Oracle Purchasing: Suppliers sends their quotations and you upload those quotations in Oracle Purchasing to get the best three quotes and further to get the one best quote.

4. Oracle Purchasing: You prepare a Purchase Order(PO) against the best RFQ to buy the goods from the supplier who quoted the suitable price and sends the PO to that supplier

5. Oracle Purchasing: The supplier receives the confirmation of purchase from PO and ships the ordered goods. You receive the goods enter a Goods Received Note (GRN) in Oracle Purchasing.

6. Oracle Inventory / Oracle Assets:It’s up to you whether you want to receive the goods at your head office or you Inventory directly. In either case you move the received goods to your different Raw Material Inventory from Oracle Purchasing to Oracle Inventory and the Item Count increases. If the item is Asset Type then it will move to Oracle Assets at the time of Invoice creation in Oracle Payables.

7. Oracle General Ledger:Once you move the goods to Oracle Inventory, it sends the Material Accounting to Oracle General Ledger.

8. Oracle Payables: After this the supplier sends you the invoice for the purchased goods and you Enter or Match the invoice against the PO from Oracle Purchasing in Oracle Payables. As said before, if the item is Asset in nature then it will move to Oracle Asset.

9. Oracle General Ledger:When you enter the invoice it means that you have created a Liability against that supplier and also you have recorded the expense incurred or asset purchased. Oracle Payables sends the invoice accounting to Oracle General Ledger.

10. Oracle Payables:You pay the invoice and settle the Liability.

11. Oracle General Ledger: The liability is settled and your cash movement account is updated.

12. Oracle Cash Management: As you pay the invoice Oracle Payables sends the payment information to Oracle Cash Management for Bank Reconciliation. Once reconciled, Oracle Cash Management sends the updated Bank/Cash accounting entry to Oracle General Ledger.

13. Oracle General Ledger: Your cash at bank is updated with actual balance.

14. Oracle Process Manufacturing(OPM) / Oracle Discrete Manufacturing(ODM):You start the manufacturing of your final product. Both OPM or ODM requests the different raw materials from you inventory organizations and manufactures a finished good.

15. Oracle Inventory: As the raw materials are issued to OPM and ODM the inventory sends the issuing material accounting to General Ledger and decreases the Item Count from the Raw Material Store. As the finished good is prepared, Oracle Inventory receives the finished good in Finished Good Store and increase the Item Count.

Now the final product is ready to be sold in the market and from here the O2C cycle starts.