XML File data to Database Table

XML File data to Database Table

1> Get the xml file,
    >> If it is from any concurrent request, get it from server and change the extension from .out to .xml

select outfile_name
  from fnd_concurrent_requests
 where request_id = <request id> ;

2> create table xx_xml_tst_tbl (
                                xml_data   xmltype
                               ,request_id number
                               );

3> Create a directory, link to any appropriate directory of Database tier.
    >> Here I am using    '/usr/tmp' directory.

Run this sql script.
CREATE directory XXHW_DOC_DWNLD AS '/usr/tmp';

4> Place the converted .xml file to /usr/tmp  -- DB Tier

5> Get the characterset used and insert the xml data into the table we created above

select value
  from nls_database_parameters
 where parameter='NLS_CHARACTERSET';

INSERT INTO xx_xml_tst_tbl
VALUES (XMLTYPE(bfilename('XXHW_XML_DWNLD', 'o2438606V2.xml'), nls_charset_id('AL32UTF8')),2438606)

6> Now get the xml data into columns and rows.

Example 1: Test data(Basic)

  <?xml version="1.0" ?>
- <PONOREC>
- <NORECEIPT_DETAILS>
  <Created>24-JUN-12</Created>
  <Requisition_x0020_Num>111001000058</Requisition_x0020_Num>
  <PO_x0020_Number>111001000052</PO_x0020_Number>
  <RECEIVING_STATUS>Not Received</RECEIVING_STATUS>
  <Business_x0020_Unit>XXX - School of Life</Business_x0020_Unit>
  <Type>Standard Purchase Order</Type>
  <Supplier_Name>Hospitality Services - Internal</Supplier_Name>
  <Supplier_Site>CATERING</Supplier_Site>
  </NORECEIPT_DETAILS>
- <NORECEIPT_DETAILS>
  <Created>20-DEC-12</Created>
  <Requisition_x0020_Num>666601002002</Requisition_x0020_Num>
  <PO_x0020_Number>666601001860</PO_x0020_Number>
  <RECEIVING_STATUS>Partially Received</RECEIVING_STATUS>
  <Business_x0020_Unit>XXX - Professional Services</Business_x0020_Unit>
  <Type>Standard Purchase Order</Type>
  <Supplier_Name>Testing LTD</Supplier_Name>
  <Supplier_Site>HERTS</Supplier_Site>
  </NORECEIPT_DETAILS>
- <NORECEIPT_DETAILS>
  <Created>11-JAN-13</Created>
  <Requisition_x0020_Num>666601002091</Requisition_x0020_Num>
  <PO_x0020_Number>666601001936</PO_x0020_Number>
  <RECEIVING_STATUS>Partially Received</RECEIVING_STATUS>
  <Business_x0020_Unit>XXX - Professional Services</Business_x0020_Unit>
  <Type>Standard Purchase Order</Type>
  <Supplier_Name>Testing2 LTD</Supplier_Name>
  <Supplier_Site>EDINBURGH</Supplier_Site>
  </NORECEIPT_DETAILS>
- <NORECEIPT_DETAILS>
  <Created>11-JAN-13</Created>
  <Requisition_x0020_Num>666601002094</Requisition_x0020_Num>
  <PO_x0020_Number>666601001937</PO_x0020_Number>
  <RECEIVING_STATUS>Partially Received</RECEIVING_STATUS>
  <Business_x0020_Unit>XXX - Professional Services</Business_x0020_Unit>
  <Type>Standard Purchase Order</Type>
  <Supplier_Name>CHARITABLE SERVICES</Supplier_Name>
  <Supplier_Site>MACCLESFIELD</Supplier_Site>
  </NORECEIPT_DETAILS>
  </PONOREC>

> Get the data

SELECT evt.*
from   xx_xml_tst_tbl xxt
      ,xmltable('/PONOREC/NORECEIPT_DETAILS'
                   passing (xxt.xml_data)
                   columns  Created           varchar2(40)   path 'Created' -- <XML Tag name>
                           ,Requi_num         varchar2(40)   path 'Requisition_x0020_Num'
                           ,PO_Num            varchar2(40)   path 'PO_x0020_Number'
                           ,Status            varchar2(100)  path 'RECEIVING_STATUS'
                           ,BU                varchar2(240)  path 'Business_x0020_Unit'
                           ,Type              varchar2(40)   path 'Type'
                           ,Supplier_Name     varchar2(256)  path 'Supplier_Name'
                           ,Supplier_Site     varchar2(256)  path 'Supplier_Site'
               ) evt                                   
where  request_id = 2438606
order by evt.Requi_num ;

> Sample Output

Example 2 : Test data (with two layers)

INSERT INTO xx_xml_tst_tbl
VALUES (XMLTYPE(bfilename('XXHW_XML_DWNLD', 'o2421932V2.xml'), nls_charset_id('AL32UTF8')),2421932)

<?xml version="1.0" encoding="UTF-8"?>
<!-- Generated by Oracle Reports version 10.1.2.3.0 -->
<XXHWAPSRA>
  <LIST_G_SEL_CHECKS>
    <G_SEL_CHECKS>
      <VENDOR_TYPE_LOOKUP_CODE>VENDOR</VENDOR_TYPE_LOOKUP_CODE>
      <C_VENDOR_NUMBER>10000155</C_VENDOR_NUMBER>
      <VENDOR_NAME>VENDOR11</VENDOR_NAME>
      <PAYMENT_NUMBER_>98010</PAYMENT_NUMBER_>
      <LIST_G_SEL_INV_DATA>
        <G_SEL_INV_DATA>
          <C_IP_CHECK_ID>98010</C_IP_CHECK_ID>
          <C_INVOICE_DATE>28-APR-13</C_INVOICE_DATE>
          <C_INVOICE_NUM>123456</C_INVOICE_NUM>
        </G_SEL_INV_DATA>
      </LIST_G_SEL_INV_DATA>
    </G_SEL_CHECKS>
    <G_SEL_CHECKS>
      <VENDOR_TYPE_LOOKUP_CODE>VENDOR</VENDOR_TYPE_LOOKUP_CODE>     
      <C_VENDOR_NUMBER>10002911</C_VENDOR_NUMBER>
      <VENDOR_NAME>VENDOR22</VENDOR_NAME>
      <PAYMENT_NUMBER_>98011</PAYMENT_NUMBER_>
      <LIST_G_SEL_INV_DATA>
        <G_SEL_INV_DATA>
          <C_IP_CHECK_ID>98011</C_IP_CHECK_ID>
          <C_INVOICE_DATE>28-APR-13</C_INVOICE_DATE>
          <C_INVOICE_NUM>1234567</C_INVOICE_NUM>
        </G_SEL_INV_DATA>
      </LIST_G_SEL_INV_DATA>
    </G_SEL_CHECKS>
    <G_SEL_CHECKS>
      <VENDOR_TYPE_LOOKUP_CODE>VENDOR</VENDOR_TYPE_LOOKUP_CODE>
      <C_VENDOR_NUMBER>863</C_VENDOR_NUMBER>
      <VENDOR_NAME>VENDOR33</VENDOR_NAME>
      <PAYMENT_NUMBER_>98012</PAYMENT_NUMBER_>
      <LIST_G_SEL_INV_DATA>
        <G_SEL_INV_DATA>
          <C_IP_CHECK_ID>98012</C_IP_CHECK_ID>
          <C_INVOICE_DATE>29-APR-13</C_INVOICE_DATE>
          <C_INVOICE_NUM>12345678</C_INVOICE_NUM>
        </G_SEL_INV_DATA>
      </LIST_G_SEL_INV_DATA>
      <LIST_G_SEL_INV_DATA>
        <G_SEL_INV_DATA>
          <C_IP_CHECK_ID>98012_1</C_IP_CHECK_ID>
          <C_INVOICE_DATE>29-APR-13</C_INVOICE_DATE>
          <C_INVOICE_NUM>12345678_1</C_INVOICE_NUM>
        </G_SEL_INV_DATA>
      </LIST_G_SEL_INV_DATA>
    </G_SEL_CHECKS>
  </LIST_G_SEL_CHECKS>
</XXHWAPSRA>

> Get the data

SELECT hed.Vendor_Type
      ,hed.vendor_num
      ,hed.payment_num
      ,hed.vendor_name
      ,evt.*
from   xx_xml_tst_tbl xxt
      ,xmltable('/XXHWAPSRA/LIST_G_SEL_CHECKS/G_SEL_CHECKS'
                   passing (xxt.xml_data)
                   columns  Vendor_Type           varchar2(40)   path 'VENDOR_TYPE_LOOKUP_CODE'
                           ,Vendor_Num            varchar2(40)   path 'C_VENDOR_NUMBER'
                           ,Vendor_Name           varchar2(246)  path 'VENDOR_NAME'
                           ,Payment_Num           varchar2(40)   path 'PAYMENT_NUMBER_'
                           ,evt   xmltype     path 'LIST_G_SEL_INV_DATA' -- Inner Layer
               ) Hed 
      ,xmltable('/LIST_G_SEL_INV_DATA/G_SEL_INV_DATA'
                   passing (Hed.evt)
                   columns  Check_id              varchar2(40)   path 'C_IP_CHECK_ID'
                           ,Inv_Date              varchar2(40)   path 'C_INVOICE_DATE'
                           ,Inv_Num               varchar2(40)   path 'C_INVOICE_NUM'
               ) evt                                   
where  request_id = 2421932
order by hed.vendor_num ;

> Sample Output

>> If you want to store these data into table.

> Create a table

create table xx_requi_tbl (created varchar2(20), requi_num varchar2(40), po_num varchar2(40), status varchar2(100), bu varchar2(240), type varchar2(40), supplier_name varchar2(256), supplier_site varchar2(256)) ;

>> Insert into table

declare
   v_xml_data  xmltype := xmltype(bfilename('XXHW_XML_DWNLD', 'o2438606V2.xml'), nls_charset_id('AL32UTF8')) ;
begin

   insert into xx_requi_tbl (created, requi_num, po_num, status, bu, type, supplier_name, supplier_site)
   select evt.*
     from xmltable('/PONOREC/NORECEIPT_DETAILS'
                   passing (v_xml_data)
                   columns  created           varchar2(20)   path 'Created'
                              ,requi_num         varchar2(40)   path 'Requisition_x0020_Num'
                              ,po_num            varchar2(40)   path 'PO_x0020_Number'
                                 ,status            varchar2(100)  path 'RECEIVING_STATUS'
                                  ,bu                varchar2(240)  path 'Business_x0020_Unit'
                                 ,type              varchar2(40)   path 'Type'
                                ,supplier_name     varchar2(256)  path 'Supplier_Name'
                                ,supplier_site     varchar2(256)  path 'Supplier_Site'
                 ) evt ;   
  commit;                             
 exception
    when others then
       dbms_output.put_line('Error: '||sqlerrm); 
 end;

> Select the data

select *
  from xx_requi_tbl ;

> Sample Output

AR Transaction: FRM-40735: POST-QUERY trigger raised unhandled exception ORA-06502

FRM-40735: POST-QUERY trigger raised unhandled exception ORA-06502

ORA-01403: no data found

AR Transaction form. -- When trying to query transaction.

Reason:  ra_customer_trx_all.ct_reference filed, in table the length of the column is 170 byte but in the form it is only 30 byte long.

Solution: Make the ct_reference field length to 30 characters long

Check for the related invoice/Credit Notes, if it is, you need to update the reference field for both.

select cn.trx_number cn
        ,cn.ct_reference cn_reference
        ,cn.interface_header_attribute1 cn_attri
        ,inv.trx_number invocie
        ,inv.ct_reference inv_reference
        ,inv.interface_header_attribute1 inv_attri
  from ra_customer_trx_all cn
         ,ra_customer_trx_all inv
 where cn.previous_customer_trx_id = inv.customer_trx_id
    and cn.trx_number = <Credit Note#>
    and cn.org_id = <Org Id> ;
   
update ra_customer_trx_all set ct_reference = '<30 Char long reference>'
 where trx_number in ('<Inv#>','<CN#>')
    and org_id = <Org Id> ;   

>> If this does not solve the problem, then there must be default setup for Reference

Goto, Receivables > Setup > Transactions > Sources, and check the setup for 'Reference Field Default Value'.

For Ex, if it is 'interface_header_attribute1', update this as well with the same value.

update ra_customer_trx_all set interface_header_attribute1 = '<30 Char long reference>'
 where trx_number in ('<Inv#>','<CN#>')
    and org_id = <Org Id> ;

Commit the changes and try again.

APP-FND-00756: Cannot find combination CCID=4321 CODE=MSTK SET=111

APP-FND-00756: Cannot find combination CCID=4321 CODE=MSTK SET=111

It is a problem with the stock locator.

Recompile the stock locator flexfield , ensure it is freezed.

Setup > Financials > Key >  Segments

 > Application = 'Inventory'   Flexfield Title = 'Stock Locators'

   > Check 'Freeze Flexfield Definition'  save/compile

*Note :- Even though you are not using stock locator in your organization, this error can populate.