Home » Other » 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 |
|
vharish006
Messages: 70 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
|
|
|
Goto Forum:
Current Time: Tue Apr 23 05:47:23 CDT 2024
|