Home » SQL & PL/SQL » Client Tools » XML in View output (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
XML in View output [message #676500] Wed, 12 June 2019 10:41 Go to next message
vharish006
Messages: 67
Registered: August 2015
Location: Chicago
Member
Hi All,

Currently we are creating a xml file which will be sent to 3rd party .I would like to display it in the "View Output" tab of the concurrent program window.

I tried using below in the code but it does not work. Please suggest

--dbms_xmldom.writeToClob(l_domdoc,clobdoc) ;
-- apps.fnd_file.put_line(apps.fnd_file.output,clobdoc);

CREATE OR REPLACE PACKAGE BODY PQINF.XXPQ_EDI_ONE_INV_EDICOMM_XML
AS
 PROCEDURE XXPQ_EDI_IP_INV_PICK (P_INV_NUM       IN  VARCHAR2,
                                   p_err_flag       OUT VARCHAR2,
                                   p_err_msg        OUT VARCHAR2)                                   
   AS
      CURSOR c_edi
      IS
         SELECT RACT.CUSTOMER_TRX_ID, RACT.TRX_NUMBER, RACT.TRX_DATE, ARPS.amount_due_remaining
           FROM APPS.RA_CUSTOMER_TRX_ALL RACT,
                APPS.HZ_CUST_ACCOUNTS HZCA,
                APPS.AR_PAYMENT_SCHEDULES_ALL ARPS
          WHERE     RACT.BILL_TO_CUSTOMER_ID = HZCA.CUST_ACCOUNT_ID
                AND RACT.CUSTOMER_TRX_ID = ARPS.CUSTOMER_TRX_ID
                AND RACT.CUST_TRX_TYPE_ID IN (1,1327,1003,1001,1271)
                AND ARPS.amount_due_remaining >= 0               
                AND HZCA.ATTRIBUTE9 IN ('Email&' || 'EDI', 'EDI') --Invoice delivery method               
                AND RACT.INTERFACE_HEADER_ATTRIBUTE12 IS NOT NULL                
                AND RACT.TRX_NUMBER = P_INV_NUM;
   
   BEGIN
      p_err_flag := 'N';
      p_err_msg := NULL;      

      FOR rec_edi IN c_edi
      LOOP      

            UPDATE  PQINF.XXPQ_EDI_IP_INV_PICK
            SET PROCESS_FLAG='N',
                LAST_UPDATE_DATE =SYSDATE
            WHERE INVOICE_NUMBER=rec_edi.TRX_NUMBER;
   
 
      END LOOP;
      

      COMMIT;
      
      
   EXCEPTION
      WHEN OTHERS
      THEN
         p_err_flag := 'E';
         p_err_msg := 'Error in procedure :';
         apps.fnd_file.put_line (
            apps.fnd_file.LOG,
               'Error in procedure XXPQ_EDI_IP_INV_PICK:'
            || DBMS_UTILITY.format_error_backtrace
            || SQLERRM);
   END XXPQ_EDI_IP_INV_PICK;


   PROCEDURE CREATE_EDI_XML_FILE (p_file          IN  VARCHAR2,
                                  p_inv_num       IN   VARCHAR2,
                                  p_err_flag      OUT VARCHAR2,
                                  p_err_msg       OUT VARCHAR2)
   IS
      CURSOR c_edi(C_INV_NUMBER IN VARCHAR2)
      IS
          SELECT * FROM PQINF.XXPQ_EDI_IP_INV_PICK edi
           WHERE PROCESS_FLAG IN ('Y', 'N')
           AND FUTURE_TRX_FLAG = 'N'
           AND amount_due_remaining >= 0
           and EXISTS (SELECT '1' exist_flag
                              FROM apps.oe_order_headers_all oeh,
                                   apps.ra_customer_trx_all rct
                             WHERE rct.interface_header_context = 'ORDER ENTRY' 
                               AND to_char(order_number) = rct.interface_header_attribute1
                               AND rct.customer_trx_id = edi.customer_trx_id                               
                               AND oeh.attribute3 NOT IN ('AR', 'CS', 'GOV', 'V')
                               AND oeh.attribute5 = 'Yes'
                             UNION 
                             SELECT '1' exist_flag
                               FROM apps.okc_k_headers_all_b okh,
                                    apps.ra_customer_trx_all rct
                              WHERE  rct.interface_header_context = 'OKS CONTRACTS'
                                AND contract_number = rct.interface_header_attribute1
                                AND NVL(contract_number_modifier,'-') = NVL(rct.interface_header_attribute2,'-')
                                AND rct.customer_trx_id = edi.customer_trx_id
                                AND okh.attribute1 NOT IN ('AR', 'CS', 'GOV', 'V')
                                AND okh.attribute3 = 'Yes'                                      
                               )
          AND invoice_number = C_INV_NUMBER;
        
        


      CURSOR c_hdr (
         C_INV_NUMBER IN VARCHAR2)
      IS
         SELECT DISTINCT
                HDR.CLIENT_INVOICE_ID CLIENT_INVOICE_ID,
                HDR.INVOICE_NUMBER INVOICE_NUMBER,
                NVL (HDR.PO_NUMBER, HDR.INVOICE_NUMBER) PO_NUMBER,
                HDR.INVOICE_DATE INVOICE_DATE,
                (SELECT TRUNC (TO_DATE (RACT.CREATION_DATE))
                   FROM APPS.RA_CUSTOMER_TRX_ALL RACT
                  WHERE RACT.TRX_NUMBER = HDR.INVOICE_NUMBER)
                   TAXDATE,
                '380' DOCUMENT_SUBTYPE,
                PQINF.XXPQ_PAYBOX_FORMULA_PKG.get_cs_mesg(hdr.pq_customer_trx_id) COMMENTS,
                HDR.INVOICE_CURRENCY INVOICE_CURRENCY,
                HDR.TOTAL_INVOICE_AMOUNT TOTALINVOICEAMOUNT,
                NVL (HDR.PQ_ORDER_NUMBER, HDR.INVOICE_NUMBER) PQ_ORDER_NUMBER,
                (SELECT DISTINCT PTI.TAX_IDENTIFIER
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA,
                        PQINF.XXPQ_PQ_TAX_IDENTIFIER PTI,
                        APPS.RA_CUSTOMER_TRX_ALL RACTL,
                        APPS.FND_TERRITORIES FT
                  WHERE     HDR.CLIENT_INVOICE_ID = IPA.CLIENT_INVOICE_ID
                        AND NVL (UPPER (IPA.COUNTRY), 'US') =
                               DECODE (FT.NLS_TERRITORY,
                                       'AMERICA', 'US',
                                       FT.NLS_TERRITORY)
                        AND FT.TERRITORY_CODE = PTI.BILL_TO_COUNTRY(+)
                        AND RACTL.TRX_NUMBER = HDR.INVOICE_NUMBER
                        AND RACTL.ORG_ID = PTI.ORG_ID
                        AND IPA.address_type = 'B'
                        AND IPA.CLIENT_INVOICE_ID = HDR.CLIENT_INVOICE_ID)
                   PQ_TAX_IDENTIFIER,
                (SELECT IPADD.PARTY_ORGANIZATION_NAME
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD
                  WHERE     HDR.CLIENT_INVOICE_ID = IPADD.CLIENT_INVOICE_ID
                        AND IPADD.ADDRESS_TYPE = 'L')
                   SUPPLIERNAME,
                (SELECT IPADD.ADDRESS_LINE_1
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD
                  WHERE     HDR.CLIENT_INVOICE_ID = IPADD.CLIENT_INVOICE_ID
                        AND IPADD.ADDRESS_TYPE = 'L')
                   SUPPLIERSTREETNAME,
                (SELECT NVL (IPADD.ADDRESS_LINE_2, IPADD.ADDRESS_LINE_1)
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD
                  WHERE     HDR.CLIENT_INVOICE_ID = IPADD.CLIENT_INVOICE_ID
                        AND IPADD.ADDRESS_TYPE = 'L')
                   SUPPLIERADDSTREETNAME,
                (SELECT IPADD.CITY
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD
                  WHERE     HDR.CLIENT_INVOICE_ID = IPADD.CLIENT_INVOICE_ID
                        AND IPADD.ADDRESS_TYPE = 'L')
                   SUPPLIERCITYNAME,
                (SELECT IPADD.STATE_PROVINCE
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD
                  WHERE     HDR.CLIENT_INVOICE_ID = IPADD.CLIENT_INVOICE_ID
                        AND IPADD.ADDRESS_TYPE = 'L')
                   TOWNPV,
                (SELECT IPADD.POSTAL_CODE
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD
                  WHERE     HDR.CLIENT_INVOICE_ID = IPADD.CLIENT_INVOICE_ID
                        AND IPADD.ADDRESS_TYPE = 'L')
                   SUPPLIERPOSTALZONE,
                PQINF.SUPPLIERCOUNTRY (client_invoice_id) SUPPLIERCOUNTRY,
                (SELECT NVL (IPADD.PQ_ADDR_ATTN,
                             IPADD.PARTY_ORGANIZATION_NAME)
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD
                  WHERE     HDR.CLIENT_INVOICE_ID = IPADD.CLIENT_INVOICE_ID
                        AND IPADD.ADDRESS_TYPE = 'L')
                   SUPPLIERCONTACTID,
                (SELECT IPADD.PQ_ADDR_TELEPHONE
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD
                  WHERE     HDR.CLIENT_INVOICE_ID = IPADD.CLIENT_INVOICE_ID
                        AND IPADD.ADDRESS_TYPE = 'L')
                   SUPPLIERCONTACTPHONE,
                'customerservice@proquest.com' SUPPLIERCONTACTEMAIL,
                (SELECT IPADD.PARTY_ID
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD
                  WHERE     HDR.CLIENT_INVOICE_ID = IPADD.CLIENT_INVOICE_ID
                        AND IPADD.ADDRESS_TYPE = 'B')
                   CUSTOMERPARTYID,
                (SELECT IPADD.PARTY_ORGANIZATION_NAME
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD
                  WHERE     HDR.CLIENT_INVOICE_ID = IPADD.CLIENT_INVOICE_ID
                        AND IPADD.ADDRESS_TYPE = 'B')
                   CUSTOMERNAME,
                (SELECT NVL (IPADD.ADDRESS_LINE_1, IPADD.ADDRESS_LINE_2)
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD
                  WHERE     HDR.CLIENT_INVOICE_ID = IPADD.CLIENT_INVOICE_ID
                        AND IPADD.ADDRESS_TYPE = 'B')
                   CUSTOMERSTREETNAME,
                (SELECT IPADD.ADDRESS_LINE_2
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD
                  WHERE     HDR.CLIENT_INVOICE_ID = IPADD.CLIENT_INVOICE_ID
                        AND ADDRESS_TYPE = 'B')
                   CUSTOMERADDSTREETNAME,
                (SELECT IPADD.CITY
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD
                  WHERE     HDR.CLIENT_INVOICE_ID = IPADD.CLIENT_INVOICE_ID
                        AND IPADD.ADDRESS_TYPE = 'B')
                   CUSTOMERCITYNAME,
                (SELECT NVL (IPADD.STATE_PROVINCE, IPADD.CITY)
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD
                  WHERE     HDR.CLIENT_INVOICE_ID = IPADD.CLIENT_INVOICE_ID
                        AND IPADD.ADDRESS_TYPE = 'B')
                   TOWNBY,
                (SELECT IPADD.POSTAL_CODE
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD
                  WHERE     HDR.CLIENT_INVOICE_ID = IPADD.CLIENT_INVOICE_ID
                        AND IPADD.ADDRESS_TYPE = 'B')
                   CUSTOMERPOSTALZONE,
                   (SELECT DISTINCT FTV.TERRITORY_CODE
                    FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD,
                         APPS.FND_TERRITORIES_VL FTV
                   WHERE IPADD.CLIENT_INVOICE_ID=HDR.CLIENT_INVOICE_ID
                     AND DECODE(IPADD.COUNTRY,'US','United States',IPADD.COUNTRY)=FTV.TERRITORY_SHORT_NAME
                    AND IPADD.ADDRESS_TYPE = 'B')
                   CUSTOMERCOUNTRYCODE,
                (SELECT EDI.CUSTOMER_TAX_IDENTIFIER
                   FROM PQINF.XXPQ_EDI_CUSTOMER_IP_DETAILS EDI
                  WHERE EDI.ACCOUNT_NUMBER = HDR.PAYER_NUMBER)
                   CUSTOMERPARTYTAXID,
                (SELECT IPADD.PARTY_ORGANIZATION_NAME
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD
                  WHERE     HDR.CLIENT_INVOICE_ID = IPADD.CLIENT_INVOICE_ID
                        AND IPADD.ADDRESS_TYPE = 'B')
                   CUSTOMERPARTYLEGALNAME,
                (SELECT IPADD.PARTY_ID
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD
                  WHERE     HDR.CLIENT_INVOICE_ID = IPADD.CLIENT_INVOICE_ID
                        AND IPADD.ADDRESS_TYPE = 'B')
                   CUSTOMERPARTYLEGALID,
                (SELECT IPADD.PQ_ADDR_ATTN
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD
                  WHERE     HDR.CLIENT_INVOICE_ID = IPADD.CLIENT_INVOICE_ID
                        AND IPADD.ADDRESS_TYPE = 'B')
                   CUSTOMERCONTACTID,
            (SELECT party.party_name contact_name                           
                  FROM apps.hz_contact_points cont_point
                      ,apps.hz_cust_account_roles hcar
                      ,apps.hz_parties party
                      ,apps.hz_parties rel_party
                      ,apps.hz_relationships rel
                      ,apps.hz_org_contacts org_cont
                      ,apps.hz_cust_accounts hca
                WHERE  hcar.party_id = rel.party_id
                   AND hcar.role_type = 'CONTACT'
                   AND org_cont.party_relationship_id = rel.relationship_id
                   AND rel.subject_id = party.party_id
                   AND rel_party.party_id = rel.party_id
                   AND cont_point.owner_table_id(+) = rel_party.party_id
                   AND cont_point.primary_flag(+) = 'Y' 
                   AND cont_point.status = 'A'
                   AND hcar.cust_account_id = hca.cust_account_id
                   AND hca.party_id = rel.object_id 
                   AND hca.account_number = hdr.payer_number
                   AND rownum = 1)
                   CUSTOMERCONTACTNAME,
                   (SELECT REGEXP_REPLACE ( HCP.PHONE_AREA_CODE || ' ' || HCP.PHONE_NUMBER,
                           '\s') phone    
                      FROM apps.hz_contact_points hcp 
                          ,apps.hz_cust_account_roles hcar
                          ,apps.hz_parties party
                          ,apps.hz_parties rel_party
                          ,apps.hz_relationships rel
                          ,apps.hz_org_contacts org_cont
                          ,apps.hz_cust_accounts hca   
                    WHERE hcar.party_id = rel.party_id
                       AND hcar.role_type = 'CONTACT'
                       AND org_cont.party_relationship_id = rel.relationship_id
                       AND rel.subject_id = party.party_id
                       AND rel_party.party_id = rel.party_id
                       AND hcp.owner_table_id(+) = rel_party.party_id
                       AND hcp.contact_point_type(+) = 'PHONE'
                       AND hcp.primary_flag(+) = 'Y' 
                       AND hcp.status = 'A'
                       AND hcar.cust_account_id = hca.cust_account_id
                       AND hca.party_id = rel.object_id 
                       AND hcp.PHONE_LINE_TYPE = 'GEN'
                       AND hca.account_number = hdr.payer_number
                       AND rownum = 1)
                   CUSTOMERCONTACTPHONE,
                (SELECT rel_party.email_address                           
                  FROM apps.hz_contact_points cont_point
                      ,apps.hz_cust_account_roles hcar
                      ,apps.hz_parties party
                      ,apps.hz_parties rel_party
                      ,apps.hz_relationships rel
                      ,apps.hz_org_contacts org_cont
                      ,apps.hz_cust_accounts hca
                WHERE  hcar.party_id = rel.party_id
                   AND hcar.role_type = 'CONTACT'
                   AND org_cont.party_relationship_id = rel.relationship_id
                   AND rel.subject_id = party.party_id
                   AND rel_party.party_id = rel.party_id
                   AND cont_point.owner_table_id(+) = rel_party.party_id
                   AND cont_point.contact_point_type(+) = 'EMAIL'
                   AND cont_point.primary_flag(+) = 'Y' 
                   AND cont_point.status = 'A'
                   AND hcar.cust_account_id = hca.cust_account_id
                   AND hca.party_id = rel.object_id 
                   AND rel_party.email_address is not null   
                   AND hca.account_number = hdr.payer_number
                   AND rownum = 1)
                   CUSTOMERCONTACTEMAIL,
                --ROUND((PQ_TOTAL_TAX / INVOICE_AMOUNT * 100),2) VATRATE,--MODIFIED  02-MAY-2019
                --ROUND((PQ_TOTAL_TAX /DECODE(INVOICE_AMOUNT,0,1) * 100),2) VATRATE, --05-JUN-2019
                  ROUND((PQ_TOTAL_TAX /DECODE(INVOICE_AMOUNT,0,1,INVOICE_AMOUNT)* 100),2)VATRATE, --MODIFIED  05-JUN-2019
                INVOICE_AMOUNT TAXBASE,
                PQ_TOTAL_TAX TAX,
                (SELECT NVL (SUM (LINE_TOTAL), 0)
                   FROM PQINF.XXPQ_IP_INVOICE_LINES IPL
                  WHERE     PQ_TAX_FLAG = 'Y'
                        AND IPL.CLIENT_INVOICE_ID = HDR.CLIENT_INVOICE_ID)
                   SUBTOTALTAXABLEAMOUNT,
                INVOICE_AMOUNT,
                DECODE (PQ_TOTAL_TAX, 0, 'TAX EXEMPT', PQ_TOTAL_TAX)
                   TAXEXEMPTION,
                --'VAT' TAXSCHEMEID,
                   (SELECT DISTINCT PTI.TAXSCHEMEID
                   FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA,
                        PQINF.XXPQ_PQ_TAX_IDENTIFIER PTI,
                        APPS.RA_CUSTOMER_TRX_ALL RACTL,
                        APPS.FND_TERRITORIES FT                      
                  WHERE     HDR.CLIENT_INVOICE_ID = IPA.CLIENT_INVOICE_ID
                        AND NVL (UPPER (IPA.COUNTRY), 'US') =
                               DECODE (FT.NLS_TERRITORY,
                                       'AMERICA', 'US',
                                       FT.NLS_TERRITORY)
                        AND FT.TERRITORY_CODE = PTI.BILL_TO_COUNTRY(+)
                        AND RACTL.TRX_NUMBER = HDR.INVOICE_NUMBER
                        AND RACTL.ORG_ID = PTI.ORG_ID
                        AND IPA.address_type = 'B'
                        AND IPA.CLIENT_INVOICE_ID = HDR.CLIENT_INVOICE_ID) TAXSCHEMEID, --- 09-May-2019
                (SELECT NVL (RACT.EXCHANGE_RATE, 1)
                   FROM APPS.RA_CUSTOMER_TRX_ALL RACT
                  WHERE RACT.TRX_NUMBER = HDR.INVOICE_NUMBER)
                   CALCULATIONRATE,
                'IBAN' PAYMENTCHANNELCODE,
                'MULTIPLY' MATHEMATICOPERATORCODE,
                PAYMENT_TERMS,
                PAYMENT_DUE_DATE,
                (TOTAL_INVOICE_AMOUNT - AMOUNT_PAID) PAYAMO,
                'EN' IDIOMA,
                'TE' FIRMFACT,
                /*(SELECT ROUND(SUM(nvl(UNIT_PRICE,LINE_TOTAL/QUANTITY) * QUANTITY),2)
                   FROM PQINF.XXPQ_IP_INVOICE_LINES IPL
                  WHERE IPL.CLIENT_INVOICE_ID = HDR.CLIENT_INVOICE_ID)
                   TOTALBRUTO,---Modified by harish on 23-Apr-2019*/
                   INVOICE_AMOUNT TOTALBRUTO,
                (SELECT NVL (SUM (HDR.INVOICE_AMOUNT + IPA.TOTAL_AMOUNT),
                             HDR.INVOICE_AMOUNT)
                   FROM PQINF.XXPQ_IP_INVOICE_CHARGES IPA
                  WHERE IPA.CLIENT_INVOICE_ID = HDR.CLIENT_INVOICE_ID)
                   BASEIMPONIBLE,
                (SELECT NVL (SUM (HDR.PQ_TOTAL_TAX + IPA.TOTAL_AMOUNT),
                             HDR.PQ_TOTAL_TAX)
                   FROM PQINF.XXPQ_IP_INVOICE_CHARGES IPA
                  WHERE IPA.CLIENT_INVOICE_ID = HDR.CLIENT_INVOICE_ID)
                   IMPREPER,
                AMOUNT_PAID,
                'LEGAL ENTITY' TIPPERF,
                'E' TIPRESEF,
                'AE' TAXCATID,
                PQINF.TYPEDISCCHARGES (client_invoice_id) TYPEDISC,
                PQINF.AMOUNTDISC (client_invoice_id) AMODISC,
                SYSDATE MESSAGETIMESTAMP,
                (SELECT HDR.INVOICE_NUMBER || '-' || IPADD.PARTY_ID
                 FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPADD
                 WHERE HDR.CLIENT_INVOICE_ID = IPADD.CLIENT_INVOICE_ID
                        AND IPADD.ADDRESS_TYPE = 'B')
                   PAYMENTID,
                PQINF.PAYMENTACCOUNTID (client_invoice_id) PAYMENTACCOUNTID,
                PQINF.BANKBRANCHID (client_invoice_id) PMTINSTBRNID,
                PQINF.PAYMENTMEANSCODE (client_invoice_id) PMTMEANSCODE,
                PQINF.PAYMENTINSTITUTIONID (client_invoice_id) PMTINSTID,
                 (SELECT EDIP.STANDARD_IDENTIFICATION_NUMBER 
                 FROM PQINF.XXPQ_EDI_CUSTOMER_IP_DETAILS EDIP
                  WHERE EDIP.ACCOUNT_NUMBER=HDR.PAYER_NUMBER)CUSTOMERENDPOINTID,
                'RF01' TAXSYSTEM,
                '1234' CODDEST,
              (SELECT EDIP.EINVOICE_PROVIDER FROM PQINF.XXPQ_EDI_CUSTOMER_IP_DETAILS EDIP
              WHERE  HDR.PAYER_NUMBER=EDIP.ACCOUNT_NUMBER)TOINTERMEDIATOR,
             (SELECT EDIP.CUSTOMER_TYPE FROM PQINF.XXPQ_EDI_CUSTOMER_IP_DETAILS EDIP
              WHERE  HDR.PAYER_NUMBER=EDIP.ACCOUNT_NUMBER)FORMATO
           FROM PQINF.XXPQ_IP_INVOICE_HEADER HDR
          WHERE HDR.INVOICE_NUMBER = C_INV_NUMBER;


      CURSOR c_lin (
         c_client_invoice_id VARCHAR2)
      IS
           SELECT PQ_CUSTOMER_TRX_LINE_ID,
                  PQ_ITEM_DESCRIPTION,
                  QUANTITY,
                  LINE_ITEM_NUMBER,
                  NVL(ITEM_NUMBER,'BUNDLE') ITEM_NUMBER,
                  DECODE(PQ_HIDE_PRICE_FLAG,'Y',0,LINE_TOTAL) LINE_TOTAL,
                  --((NVL(ipl.unit_price,0) * ipl.quantity) - ipl.line_total) PQ_DISCOUNT,
                  --decode(ipl.unit_price, null, 0, ((ipl.unit_price * ipl.quantity) - ipl.line_total)) PQ_DISCOUNT,
                  ROUND(NVL(ipl.unit_price,ipl.LINE_TOTAL/ipl.QUANTITY)* ipl.quantity - ipl.line_total,2) PQ_DISCOUNT,---- Added by Harish(22-apr-2019)
                  --DECODE(PQ_HIDE_PRICE_FLAG,'Y',0,NVL(UNIT_PRICE,0)) UNIT_PRICE,
                   ROUND(DECODE(PQ_HIDE_PRICE_FLAG,'Y',0,NVL(UNIT_PRICE,LINE_TOTAL/QUANTITY)),2) UNIT_PRICE,-- Added by Harish(22-apr-2019)
                  (SELECT NVL(IPL.PQ_CUSTOMER_REFERENCE,HDR.PQ_ORDER_NUMBER)
                   FROM  PQINF.XXPQ_IP_INVOICE_HEADER HDR
                   WHERE  HDR.CLIENT_INVOICE_ID=C_CLIENT_INVOICE_ID)PQ_CUSTOMER_REFERENCE,
                  'EA' UNIT_OF_MEASURE,
                  DECODE(PQ_HIDE_PRICE_FLAG ,'Y', '0',SUM(LINE_TOTAL))TOTALPRICE,
                  (SELECT TRUNC (TO_DATE (RACTL.CREATION_DATE))
                     FROM PQINF.XXPQ_IP_INVOICE_HEADER HDR,
                          APPS.RA_CUSTOMER_TRX_ALL RACTL
                    WHERE     HDR.INVOICE_NUMBER = RACTL.TRX_NUMBER
                          AND HDR.CLIENT_INVOICE_ID = C_CLIENT_INVOICE_ID)
                     INVOICECREATIONDATE,
                  'AE' ITEMTAXID,
                  /*NVL (
                     (  LINE_TAX_1_AMOUNT
                      / DECODE (LINE_TOTAL, '0', '', LINE_TOTAL)
                      * 100),
                     0)
                     VATARATE,*/
                      ROUND(NVL((  LINE_TAX_1_AMOUNT
                      / DECODE (LINE_TOTAL, '0', '', LINE_TOTAL)
                      * 100),
                     0),2) VATARATE,---By Harish 02-MAY-2019
                  --NVL(LINE_TAX_1_AMOUNT,0) TAXLINEAMT
                  NVL(DECODE(PQ_HIDE_PRICE_FLAG ,'Y','0',LINE_TAX_1_AMOUNT),0)TAXLINEAMT,
                  DECODE(PQ_HIDE_PRICE_FLAG ,'Y','0',NVL(LINE_TAX_1_AMOUNT,0)+LINE_TOTAL)TAXINCLUSIVEAMOUNT,
                  --NVL(DECODE(PQ_HIDE_PRICE_FLAG ,'Y','0',(LINE_TAX_1_AMOUNT + LINE_TOTAL)),0)TAXINCLUSIVEAMOUNT,
                  'ProQuest' MANUFACTURERNAME
             FROM PQINF.XXPQ_IP_INVOICE_LINES IPL
            WHERE CLIENT_INVOICE_ID = C_CLIENT_INVOICE_ID
         GROUP BY PQ_ITEM_DESCRIPTION,
                  QUANTITY,
                  LINE_ITEM_NUMBER,
                  ITEM_NUMBER,
                  LINE_TOTAL,
                  PQ_DISCOUNT,
                  UNIT_PRICE,
                  PQ_CUSTOMER_REFERENCE,
                  UNIT_OF_MEASURE,
                  PQ_CUSTOMER_TRX_LINE_ID,
                  LINE_TAX_1_AMOUNT,
                  LINE_TOTAL,
                  PQ_CUSTOMER_TRX_LINE_ID,
                  PQ_HIDE_PRICE_FLAG
         ORDER BY LINE_ITEM_NUMBER ;



      l_domdoc                         DBMS_XMLDOM.DOMDocument;
      l_root_node                      DBMS_XMLDOM.DOMNode;
      l_ediinvoices_node               DBMS_XMLDOM.DOMNode;
      l_edi_invoices                   DBMS_XMLDOM.DOMNode;
      l_invoice_node                   DBMS_XMLDOM.DOMNode;
      l_invoice                        DBMS_XMLDOM.DOMNode;
      l_numdoc_node                    DBMS_XMLDOM.DOMNode; -- HEADER FIELDS START HERE
      l_numdoc_textnode                DBMS_XMLDOM.DOMNode;
      l_orderid_node                   DBMS_XMLDOM.DOMNode;
      l_orderid_textnode               DBMS_XMLDOM.DOMNode;
      l_issuedate_node                 DBMS_XMLDOM.DOMNode;
      l_issuedate_textnode             DBMS_XMLDOM.DOMNode;
      l_invtypcode_node                DBMS_XMLDOM.DOMNode;
      l_invtypcode_textnode            DBMS_XMLDOM.DOMNode;
      l_note_node                      DBMS_XMLDOM.DOMNode;
      l_note_textnode                  DBMS_XMLDOM.DOMNode;
      l_taxdate_node                   DBMS_XMLDOM.DOMNode;
      l_taxdate_textnode               DBMS_XMLDOM.DOMNode;
      l_doccurrency_node               DBMS_XMLDOM.DOMNode;
      l_doccurrency_textnode           DBMS_XMLDOM.DOMNode;
      l_currencycode_node              DBMS_XMLDOM.DOMNode;
      l_currencycode_textnode          DBMS_XMLDOM.DOMNode;
      l_startdate_node                 DBMS_XMLDOM.DOMNode;
      l_startdate_textnode             DBMS_XMLDOM.DOMNode;
      l_enddate_node                   DBMS_XMLDOM.DOMNode;
      l_enddate_textnode               DBMS_XMLDOM.DOMNode;
      l_contractid_node                DBMS_XMLDOM.DOMNode;
      l_contractid_textnode            DBMS_XMLDOM.DOMNode;
      l_supplierpartyid_node           DBMS_XMLDOM.DOMNode;
      l_supplierpartyid_textnode       DBMS_XMLDOM.DOMNode;
      l_suppliername_node              DBMS_XMLDOM.DOMNode;
      l_suppliername_textnode          DBMS_XMLDOM.DOMNode;
      l_supplierstreetname_node        DBMS_XMLDOM.DOMNode;
      l_supplierstreetname_textnode    DBMS_XMLDOM.DOMNode;
      l_supplieraddstrname_node        DBMS_XMLDOM.DOMNode;
      l_supplieraddstrname_textnode    DBMS_XMLDOM.DOMNode;
      l_suppliercityname_node          DBMS_XMLDOM.DOMNode;
      l_suppliercityname_textnode      DBMS_XMLDOM.DOMNode;
      l_supplierpostalzone_node        DBMS_XMLDOM.DOMNode;
      l_supplierpostalzone_textnode    DBMS_XMLDOM.DOMNode;
      l_suppliercountry_node           DBMS_XMLDOM.DOMNode;
      l_suppliercountry_textnode       DBMS_XMLDOM.DOMNode;
      l_supplierpartytaxid_node        DBMS_XMLDOM.DOMNode;
      l_supplierpartytaxid_textnode    DBMS_XMLDOM.DOMNode;
      l_supplierpartyname_node         DBMS_XMLDOM.DOMNode;
      l_supplierpartyname_textnode     DBMS_XMLDOM.DOMNode;
      l_supplierpartylegid_node        DBMS_XMLDOM.DOMNode;
      l_supplierpartylegid_textnode    DBMS_XMLDOM.DOMNode;
      l_supplierlegcity_node           DBMS_XMLDOM.DOMNode;
      l_supplierlegcity_textnode       DBMS_XMLDOM.DOMNode;
      l_supplierlegcountry_node        DBMS_XMLDOM.DOMNode;
      l_supplierlegcountry_textnode    DBMS_XMLDOM.DOMNode;
      l_suppliercontactid_node         DBMS_XMLDOM.DOMNode;
      l_suppliercontactid_textnode     DBMS_XMLDOM.DOMNode;
      l_suppliercontactname_node       DBMS_XMLDOM.DOMNode;
      l_suppliercontactname_textnode   DBMS_XMLDOM.DOMNode;
      l_supplierphone_node             DBMS_XMLDOM.DOMNode;
      l_supplierphone_textnode         DBMS_XMLDOM.DOMNode;
      l_supplieremail_node             DBMS_XMLDOM.DOMNode;
      l_supplieremail_textnode         DBMS_XMLDOM.DOMNode;
      l_customerpartyid_node           DBMS_XMLDOM.DOMNode;
      l_customerpartyid_textnode       DBMS_XMLDOM.DOMNode;
      l_customername_node              DBMS_XMLDOM.DOMNode;
      l_customername_textnode          DBMS_XMLDOM.DOMNode;
      l_customerstreetname_node        DBMS_XMLDOM.DOMNode;
      l_customerstreetname_textnode    DBMS_XMLDOM.DOMNode;
      l_customeraddname_node           DBMS_XMLDOM.DOMNode;
      l_customeraddname_textnode       DBMS_XMLDOM.DOMNode;
      l_customercityname_node          DBMS_XMLDOM.DOMNode;
      l_customercityname_textnode      DBMS_XMLDOM.DOMNode;
      l_customerpostalzone_node        DBMS_XMLDOM.DOMNode;
      l_customerpostalzone_textnode    DBMS_XMLDOM.DOMNode;
      l_customercountrycode_node       DBMS_XMLDOM.DOMNode;
      l_customercountrycode_textnode   DBMS_XMLDOM.DOMNode;
      l_customerpartytaxid_node        DBMS_XMLDOM.DOMNode;
      l_customerpartytaxid_textnode    DBMS_XMLDOM.DOMNode;
      l_custpartylegname_node          DBMS_XMLDOM.DOMNode;
      l_custpartylegname_textnode      DBMS_XMLDOM.DOMNode;
      l_custpartylegalid_node          DBMS_XMLDOM.DOMNode;
      l_custpartylegalid_textnode      DBMS_XMLDOM.DOMNode;
      l_custpartylegcity_node          DBMS_XMLDOM.DOMNode;
      l_custpartylegcity_textnode      DBMS_XMLDOM.DOMNode;
      l_custpartylegcountry_node       DBMS_XMLDOM.DOMNode;
      l_custpartylegcountry_textnode   DBMS_XMLDOM.DOMNode;
      l_custcontactid_node             DBMS_XMLDOM.DOMNode;
      l_custcontactid_textnode         DBMS_XMLDOM.DOMNode;
      l_custcontactname_node           DBMS_XMLDOM.DOMNode;
      l_custcontactname_textnode       DBMS_XMLDOM.DOMNode;
      l_custcontactphone_node          DBMS_XMLDOM.DOMNode;
      l_custcontactphone_textnode      DBMS_XMLDOM.DOMNode;
      l_custcontactemail_node          DBMS_XMLDOM.DOMNode;
      l_custcontactemail_textnode      DBMS_XMLDOM.DOMNode;
      l_FromIdentifier_node            DBMS_XMLDOM.DOMNode;
      l_FromIdentifier_textnode        DBMS_XMLDOM.DOMNode;
      l_ToIdentifier_node              DBMS_XMLDOM.DOMNode;
      l_ToIdentifier_textnode          DBMS_XMLDOM.DOMNode;
      l_msgidentifier_node             DBMS_XMLDOM.DOMNode;
      l_msgidentifier_textnode         DBMS_XMLDOM.DOMNode;
      l_msgtimestamp_node              DBMS_XMLDOM.DOMNode;
      l_msgtimestamp_textnode          DBMS_XMLDOM.DOMNode;
      l_invsenorgname_node             DBMS_XMLDOM.DOMNode;
      l_invsenorgname_textnode         DBMS_XMLDOM.DOMNode;
      l_invrecorgname_node             DBMS_XMLDOM.DOMNode;
      l_invrecorgname_textnode         DBMS_XMLDOM.DOMNode;
      l_vatrate_node                   DBMS_XMLDOM.DOMNode;
      l_vatrate_textnode               DBMS_XMLDOM.DOMNode;
      l_taxbase_node                   DBMS_XMLDOM.DOMNode;
      l_taxbase_textnode               DBMS_XMLDOM.DOMNode;
      l_tax_node                       DBMS_XMLDOM.DOMNode;
      l_tax_textnode                   DBMS_XMLDOM.DOMNode;
      l_subtottaxamt_node              DBMS_XMLDOM.DOMNode;
      l_subtottaxamt_textnode          DBMS_XMLDOM.DOMNode;
      l_subtotaltaxamt_node            DBMS_XMLDOM.DOMNode;
      l_subtotaltaxamt_textnode        DBMS_XMLDOM.DOMNode;
      l_taxamount_node                 DBMS_XMLDOM.DOMNode;
      l_taxamount_textnode             DBMS_XMLDOM.DOMNode;
      l_taxcatpercent_node             DBMS_XMLDOM.DOMNode;
      l_taxcatpercent_textnode         DBMS_XMLDOM.DOMNode;
      l_taxexemption_node              DBMS_XMLDOM.DOMNode;
      l_taxexemption_textnode          DBMS_XMLDOM.DOMNode;
      l_taxschemeid_node               DBMS_XMLDOM.DOMNode;
      l_taxschemeid_textnode           DBMS_XMLDOM.DOMNode;
      l_calculationrate_node           DBMS_XMLDOM.DOMNode;
      l_sourcecurcode_node             DBMS_XMLDOM.DOMNode;
      l_sourcecurcode_textnode         DBMS_XMLDOM.DOMNode;
      l_targetcurcode_node             DBMS_XMLDOM.DOMNode;
      l_targetcurcode_textnode         DBMS_XMLDOM.DOMNode;
      l_calculationrate_textnode       DBMS_XMLDOM.DOMNode;
      l_channelcode_node               DBMS_XMLDOM.DOMNode;
      l_channelcode_textnode           DBMS_XMLDOM.DOMNode;
      l_mathcode_node                  DBMS_XMLDOM.DOMNode;
      l_mathcode_textnode              DBMS_XMLDOM.DOMNode;
      l_duedate_node                   DBMS_XMLDOM.DOMNode;
      l_duedate_textnode               DBMS_XMLDOM.DOMNode;
      l_paymentterms_node              DBMS_XMLDOM.DOMNode;
      l_paymentterms_textnode          DBMS_XMLDOM.DOMNode;
      l_payamo_node                    DBMS_XMLDOM.DOMNode;
      l_payamo_textnode                DBMS_XMLDOM.DOMNode;
      l_totalamo_node                  DBMS_XMLDOM.DOMNode;
      l_totalamo_textnode              DBMS_XMLDOM.DOMNode;
      l_taxidpv_node                   DBMS_XMLDOM.DOMNode;
      l_taxidpv_textnode               DBMS_XMLDOM.DOMNode;
      l_firstnamepv_node               DBMS_XMLDOM.DOMNode;
      l_firstnamepv_textnode           DBMS_XMLDOM.DOMNode;
      l_surnamepv_node                 DBMS_XMLDOM.DOMNode;
      l_surnamepv_textnode             DBMS_XMLDOM.DOMNode;
      l_firstnameby_node               DBMS_XMLDOM.DOMNode;
      l_firstnameby_textnode           DBMS_XMLDOM.DOMNode;
      l_surnameby_node                 DBMS_XMLDOM.DOMNode;
      l_surnameby_textnode             DBMS_XMLDOM.DOMNode;
      l_idioma_node                    DBMS_XMLDOM.DOMNode;
      l_idioma_textnode                DBMS_XMLDOM.DOMNode;
      l_addpv_node                     DBMS_XMLDOM.DOMNode;
      l_addpv_textnode                 DBMS_XMLDOM.DOMNode;
      l_pcpv_node                      DBMS_XMLDOM.DOMNode;
      l_pcpv_textnode                  DBMS_XMLDOM.DOMNode;
      l_citypv_node                    DBMS_XMLDOM.DOMNode;
      l_citypv_textnode                DBMS_XMLDOM.DOMNode;
      l_townpv_node                    DBMS_XMLDOM.DOMNode;
      l_townpv_textnode                DBMS_XMLDOM.DOMNode;
      l_countrypv_node                 DBMS_XMLDOM.DOMNode;
      l_countrypv_textnode             DBMS_XMLDOM.DOMNode;
      l_addby_node                     DBMS_XMLDOM.DOMNode;
      l_addby_textnode                 DBMS_XMLDOM.DOMNode;
      l_pcby_node                      DBMS_XMLDOM.DOMNode;
      l_pcby_textnode                  DBMS_XMLDOM.DOMNode;
      l_cityby_node                    DBMS_XMLDOM.DOMNode;
      l_cityby_textnode                DBMS_XMLDOM.DOMNode;
      l_townby_node                    DBMS_XMLDOM.DOMNode;
      l_townby_textnode                DBMS_XMLDOM.DOMNode;
      l_countryby_node                 DBMS_XMLDOM.DOMNode;
      l_countryby_textnode             DBMS_XMLDOM.DOMNode;
      l_ccodepv_node                   DBMS_XMLDOM.DOMNode;
      l_ccodepv_textnode               DBMS_XMLDOM.DOMNode;
      l_firmfact_node                  DBMS_XMLDOM.DOMNode;
      l_firmfact_textnode              DBMS_XMLDOM.DOMNode;
      l_totalbruto_node                DBMS_XMLDOM.DOMNode;
      l_totalbruto_textnode            DBMS_XMLDOM.DOMNode;
      l_baseimponible_node             DBMS_XMLDOM.DOMNode;
      l_baseimponible_textnode         DBMS_XMLDOM.DOMNode;
      l_impreper_node                  DBMS_XMLDOM.DOMNode;
      l_impreper_textnode              DBMS_XMLDOM.DOMNode;
      l_imprete_node                   DBMS_XMLDOM.DOMNode;
      l_imprete_textnode               DBMS_XMLDOM.DOMNode;
      l_totalpagar_node                DBMS_XMLDOM.DOMNode;
      l_totalpagar_textnode            DBMS_XMLDOM.DOMNode;
      l_totalanticip_node              DBMS_XMLDOM.DOMNode;
      l_totalanticip_textnode          DBMS_XMLDOM.DOMNode;
      l_tipperef_node                  DBMS_XMLDOM.DOMNode;
      l_tipperef_textnode              DBMS_XMLDOM.DOMNode;
      l_tipresef_node                  DBMS_XMLDOM.DOMNode;
      l_tipresef_textnode              DBMS_XMLDOM.DOMNode;
      l_typedisc_node                  DBMS_XMLDOM.DOMNode;
      l_typedisc_textnode              DBMS_XMLDOM.DOMNode;
      l_amodisc_node                   DBMS_XMLDOM.DOMNode;
      l_amodisc_textnode               DBMS_XMLDOM.DOMNode;
      l_riflin_node                    DBMS_XMLDOM.DOMNode;
      l_riflin_textnode                DBMS_XMLDOM.DOMNode;
      l_proginvio_node                 DBMS_XMLDOM.DOMNode;
      l_proginvio_textnode             DBMS_XMLDOM.DOMNode;
      l_taxidby_node                   DBMS_XMLDOM.DOMNode;
      l_taxidby_textnode               DBMS_XMLDOM.DOMNode;
      l_taxcatid_node                  DBMS_XMLDOM.DOMNode;
      l_taxcatid_textnode              DBMS_XMLDOM.DOMNode;
      l_nameby_node                    DBMS_XMLDOM.DOMNode;
      l_nameby_textnode                DBMS_XMLDOM.DOMNode;
      l_paymentid_node                 DBMS_XMLDOM.DOMNode;
      l_paymentid_textnode             DBMS_XMLDOM.DOMNode;
      l_paymentaccountid_node          DBMS_XMLDOM.DOMNode;
      l_paymentaccountid_textnode      DBMS_XMLDOM.DOMNode;
      l_pmtinstbrnid_node              DBMS_XMLDOM.DOMNode;
      l_pmtinstbrnid_textnode          DBMS_XMLDOM.DOMNode;
      l_pmtmeanscode_node              DBMS_XMLDOM.DOMNode;
      l_pmtmeanscode_textnode          DBMS_XMLDOM.DOMNode;
      l_pmtinstid_node                 DBMS_XMLDOM.DOMNode;
      l_pmtinstid_textnode             DBMS_XMLDOM.DOMNode;
      l_custendpointid_node            DBMS_XMLDOM.DOMNode;
      l_custendpointid_textnode        DBMS_XMLDOM.DOMNode;
      l_taxsystem_node                 DBMS_XMLDOM.DOMNode;
      l_taxsystem_textnode             DBMS_XMLDOM.DOMNode;
      l_coddest_node                   DBMS_XMLDOM.DOMNode;
      l_coddest_textnode               DBMS_XMLDOM.DOMNode;
      l_formato_node                   DBMS_XMLDOM.DOMNode;
      l_formato_textnode               DBMS_XMLDOM.DOMNode;
      l_tointermediator_node           DBMS_XMLDOM.DOMNode;
      l_tointermediator_textnode       DBMS_XMLDOM.DOMNode;
      l_invoiceline_node               DBMS_XMLDOM.DOMNode; ---LINE FIELDS START HERE
      l_invoiceline                    DBMS_XMLDOM.DOMNode;
      l_itemname_node                  DBMS_XMLDOM.DOMNode;
      l_itemname_textnode              DBMS_XMLDOM.DOMNode;
      l_linenum_node                   DBMS_XMLDOM.DOMNode;
      l_linenum_textnode               DBMS_XMLDOM.DOMNode;
      l_unitprice_node                 DBMS_XMLDOM.DOMNode;
      l_unitprice_textnode             DBMS_XMLDOM.DOMNode;
      l_totalprice_node                DBMS_XMLDOM.DOMNode;
      l_totalprice_textnode            DBMS_XMLDOM.DOMNode;
      l_quantity_node                  DBMS_XMLDOM.DOMNode;
      l_quantity_textnode              DBMS_XMLDOM.DOMNode;
      l_measunit_node                  DBMS_XMLDOM.DOMNode;
      l_measunit_textnode              DBMS_XMLDOM.DOMNode;
      l_lineamount_node                DBMS_XMLDOM.DOMNode;
      l_lineamount_textnode            DBMS_XMLDOM.DOMNode;
      l_sellerisitemid_node            DBMS_XMLDOM.DOMNode;
      l_sellerisitemid_textnode        DBMS_XMLDOM.DOMNode;
      l_vatarate_node                  DBMS_XMLDOM.DOMNode;
      l_vatarate_textnode              DBMS_XMLDOM.DOMNode;
      l_priceallowamount_node          DBMS_XMLDOM.DOMNode;
      l_priceallowamount_textnode      DBMS_XMLDOM.DOMNode;
      l_orderlineref_node              DBMS_XMLDOM.DOMNode;
      l_orderlineref_textnode          DBMS_XMLDOM.DOMNode;
      l_manafacturername_node          DBMS_XMLDOM.DOMNode;
      l_manafacturername_textnode      DBMS_XMLDOM.DOMNode;
      l_priceamount_node               DBMS_XMLDOM.DOMNode;
      l_priceamount_textnode           DBMS_XMLDOM.DOMNode;
      l_InvoiceCreationDate_node       DBMS_XMLDOM.DOMNode;
      l_InvoiceCreationDate_textnode   DBMS_XMLDOM.DOMNode;
      l_itemtaxid_node                 DBMS_XMLDOM.DOMNode;
      l_itemtaxid_textnode             DBMS_XMLDOM.DOMNode;
      l_taxlineamt_node                DBMS_XMLDOM.DOMNode;
      l_taxlineamt_textnode            DBMS_XMLDOM.DOMNode;
      l_taxinclusiveamt_node           DBMS_XMLDOM.DOMNode;
      l_taxinclusiveamt_textnode       DBMS_XMLDOM.DOMNode;
      l_linereferenceid_node           DBMS_XMLDOM.DOMNode;
      l_linereferenceid_textnode       DBMS_XMLDOM.DOMNode;
      l_manufacturername_node          DBMS_XMLDOM.DOMNode;
      l_manufacturername_textnode      DBMS_XMLDOM.DOMNode;
      l_invtotvatincamt_node           DBMS_XMLDOM.DOMNode;
      l_invtotvatincamt_textnode       DBMS_XMLDOM.DOMNode;
      l_descrp_node                    DBMS_XMLDOM.DOMNode;
      l_descrp_textnode                DBMS_XMLDOM.DOMNode;
      l_file                           VARCHAR2 (100)
                                          := p_file || to_char(CAST(SYSDATE AS TIMESTAMP),'DDMMYYYYHHMI') || '.XML';

      l_invoice_number                 VARCHAR2 (240);
    -- clobdoc VARCHAR2(32767);  --added by harish--11-Jun-2019
      
   BEGIN
     
    apps.fnd_file.put_line (apps.fnd_file.LOG, 'file:' || l_file);

      p_err_flag := 'N';
      p_err_msg := NULL;
      
      -- Create an empty XML document
      l_domdoc := DBMS_XMLDOM.newDomDocument;

      -- Create a root node
      l_root_node := DBMS_XMLDOM.makeNode (l_domdoc);
      DBMS_XMLDOM.setVersion (l_domdoc, '1.0" encoding="UTF-8');
      DBMS_XMLDOM.setCharset (l_domdoc, 'UTF-8');

      l_ediinvoices_node :=
         DBMS_XMLDOM.appendChild (
            l_root_node,
            DBMS_XMLDOM.makeNode (
               DBMS_XMLDOM.createElement (l_domdoc, 'EDI_INVOICES')));         


      FOR rec IN c_edi(p_inv_num)
      LOOP
         l_invoice_number := NULL;

         l_invoice_number := rec.INVOICE_NUMBER;

         apps.fnd_file.put_line (apps.fnd_file.LOG,
                                 'INVOICE NUMBER' || rec.INVOICE_NUMBER);
                                 
                              



         FOR i_hdr IN c_hdr (rec.INVOICE_NUMBER)
         LOOP
            l_invoice_node :=
               DBMS_XMLDOM.appendChild (
                  l_ediinvoices_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'INVOICE')));


            l_edi_invoices :=
               DBMS_XMLDOM.appendChild (
                  l_invoice_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'INVOICE_HEADER')));



            l_numdoc_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'numdoc')));
            l_numdoc_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_numdoc_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.INVOICE_NUMBER)));


            l_orderid_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'orderid')));
            l_orderid_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_orderid_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc, i_hdr.PO_NUMBER)));

            l_issuedate_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'issuedate')));
            l_issuedate_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_issuedate_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.INVOICE_DATE)));

            l_invtypcode_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'invtypcode')));
            l_invtypcode_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_invtypcode_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.DOCUMENT_SUBTYPE)));

            l_note_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'note')));
            l_note_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_note_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc, i_hdr.COMMENTS)));


            l_taxdate_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'taxdate')));
            l_taxdate_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_taxdate_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc, i_hdr.TAXDATE)));

            l_doccurrency_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'doccurrency')));
            l_doccurrency_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_doccurrency_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.INVOICE_CURRENCY)));



            l_currencycode_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'currencycode')));
            l_currencycode_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_currencycode_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.INVOICE_CURRENCY)));



            l_startdate_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'startdate')));
            l_startdate_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_startdate_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.INVOICE_DATE)));

            l_enddate_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'enddate')));
            l_enddate_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_enddate_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.INVOICE_DATE)));



            l_contractid_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'contractid')));
            l_contractid_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_contractid_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.PQ_ORDER_NUMBER)));


            l_supplierpartyid_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'supplierpartyid')));
            l_supplierpartyid_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_supplierpartyid_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.PQ_TAX_IDENTIFIER)));


            l_suppliername_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'suppliername')));
            l_suppliername_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_suppliername_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.SUPPLIERNAME)));


            l_supplierstreetname_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'supplierstreetname')));
            l_supplierstreetname_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_supplierstreetname_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.SUPPLIERSTREETNAME)));


            l_supplieraddstrname_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'supplieraddstreetname')));
            l_supplieraddstrname_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_supplieraddstrname_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.SUPPLIERADDSTREETNAME)));



            l_suppliercityname_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'suppliercityname')));
            l_suppliercityname_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_suppliercityname_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.SUPPLIERCITYNAME)));


            l_supplierpostalzone_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'supplierpostalzone')));
            l_supplierpostalzone_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_supplierpostalzone_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.SUPPLIERPOSTALZONE)));

            l_suppliercountry_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'suppliercountry')));
            l_suppliercountry_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_suppliercountry_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.SUPPLIERCOUNTRY)));


            l_supplierpartytaxid_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'supplierpartytaxid')));
            l_supplierpartytaxid_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_supplierpartytaxid_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.PQ_TAX_IDENTIFIER)));



            l_supplierpartyname_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'supplierpartylegalregname')));
            l_supplierpartyname_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_supplierpartyname_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.SUPPLIERNAME)));



            l_supplierpartylegid_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'supplierpartylegalid')));
            l_supplierpartylegid_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_supplierpartylegid_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.PQ_TAX_IDENTIFIER)));


            l_supplierlegcity_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'supplierpartylegalcityname')));
            l_supplierlegcity_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_supplierlegcity_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.SUPPLIERCITYNAME)));



            l_supplierlegcountry_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (
                        l_domdoc,
                        'supplierpartylegalcitynamecountry')));
            l_supplierlegcountry_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_supplierlegcountry_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.SUPPLIERCOUNTRY)));


            l_suppliercontactid_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'suppliercontactid')));
            l_suppliercontactid_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_suppliercontactid_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.SUPPLIERCONTACTID)));



            l_suppliercontactname_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'suppliercontactname')));
            l_suppliercontactname_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_suppliercontactname_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.SUPPLIERCONTACTID)));

            l_supplierphone_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'suppliercontactphone')));
            l_supplierphone_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_supplierphone_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.SUPPLIERCONTACTPHONE)));


            l_supplieremail_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'suppliercontactemail')));
            l_supplieremail_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_supplieremail_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.SUPPLIERCONTACTEMAIL)));

            l_customerpartyid_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'customerpartyid')));
            l_customerpartyid_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_customerpartyid_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.CUSTOMERPARTYID)));

            l_customername_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'customername')));
            l_customername_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_customername_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.CUSTOMERNAME)));


            l_customerstreetname_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'customerstreetname')));
            l_customerstreetname_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_customerstreetname_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.CUSTOMERSTREETNAME)));


            l_customeraddname_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'customeraddstreetname')));
            l_customeraddname_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_customeraddname_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.CUSTOMERADDSTREETNAME)));


            l_customercityname_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc, 'customercityname')));
            l_customercityname_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_customercityname_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.CUSTOMERCITYNAME)));


            l_customerpostalzone_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'customerpostalzone')));
            l_customerpostalzone_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_customerpostalzone_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.CUSTOMERPOSTALZONE)));


            l_customercountrycode_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'customercountrycode')));
            l_customercountrycode_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_customercountrycode_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.CUSTOMERCOUNTRYCODE)));

            l_customerpartytaxid_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'customerpartytaxid')));
            l_customerpartytaxid_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_customerpartytaxid_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.CUSTOMERPARTYTAXID)));

            l_custpartylegname_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'customerpartylegalname')));
            l_custpartylegname_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_custpartylegname_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.CUSTOMERNAME)));


            l_custpartylegalid_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'customerpartylegalid')));
            l_custpartylegalid_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_custpartylegalid_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.CUSTOMERPARTYLEGALID)));



            l_custpartylegcity_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'customerpartylegalcityname')));
            l_custpartylegcity_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_custpartylegcity_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.CUSTOMERCITYNAME)));


            l_custpartylegcountry_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (
                        l_domdoc,
                        'customerpartylegalcitynamecountry')));
            l_custpartylegcountry_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_custpartylegcountry_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.CUSTOMERCOUNTRYCODE)));


            l_custcontactid_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'customercontactid')));
            l_custcontactid_textnode :=
               DBMS_XMLDOM.appendChild (
                  l_custcontactid_node,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createTextNode (l_domdoc,
                                                 i_hdr.CUSTOMERNAME)));



            l_custcontactname_node :=
               DBMS_XMLDOM.appendChild (
                  l_edi_invoices,
                  DBMS_XMLDOM.makeNode (
                     DBMS_XMLDOM.createElement (l_domdoc,
                                                'customercontactname')));
            l_custcontactname_textnode :=
               DB
		
		
		
Re: XML in View output [message #676509 is a reply to message #676500] Thu, 13 June 2019 07:07 Go to previous message
EdStevens
Messages: 1148
Registered: September 2013
Senior Member
Quote:


Currently we are creating a xml file which will be sent to 3rd party .I would like to display it in the "View Output" tab of the concurrent program window.

I tried using below in the code but it does not work. Please suggest
And just what is "the concurrent program window"? Can you write code to interact with an unknown program? Neither can anyone else.

"it does not work" is not found in any error manual I own.

Don't expect anyone to read through 100's of lines of code when they don't even know what they are looking for.
Previous Topic: generate xml
Next Topic: Cannot get results from table in a different PC
Goto Forum:
  


Current Time: Sun Dec 08 07:44:40 CST 2019