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
|
|
|
|
| Re: XML in View output [message #676509 is a reply to message #676500] |
Thu, 13 June 2019 07:07  |
 |
EdStevens
Messages: 1376
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.
|
|
|
|
Goto Forum:
Current Time: Wed Mar 12 03:00:25 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
