DECLARE
F UTL_FILE.FILE_TYPE;
MYCLOB CLOB;

BEGIN
SELECT
    DBMS_XMLGEN.GETXML('
SELECT DISTINCT
NULL SUPPLIER_END_POINT_ID,
''N0974767880'' CUSTOMER_END_POINT_ID,
HDR.INVOICE_NUMBER NUM_DOC,
HDR.PO_NUMBER  ORDER_ID,
HDR.INVOICE_DATE ISSUE_DATE,
HDR.DOCUMENT_SUBTYPE INVOICE_TYPE_CODE,
HDR.COMMENTS  NOTE,
HDR.INVOICE_DATE TAX_DATE,
HDR.INVOICE_CURRENCY  DOC_CURRENCY_CODE,
HDR.PQ_ORDER_NUMBER  CONTRACT_ID,
NULL DOCUMENT_TYPE_CODE,
HDR.INVOICE_CURRENCY CURRENCY_CODE,
HDR.PQ_TAX_IDENTIFIER SUPPLIER_PARTY_ID,
(SELECT IPA.PARTY_ORGANIZATION_NAME FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA 
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND IPA.ADDRESS_TYPE=''L'')SUPPLIER_NAME,
(SELECT IPA.ADDRESS_LINE_1 FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA 
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND IPA.ADDRESS_TYPE=''L'') SUPPLIER_STREET_NAME,
(SELECT IPA.ADDRESS_LINE_2 FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA 
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND IPA.ADDRESS_TYPE=''L'') SUPPLIERADD_STREET_NAME,
(SELECT IPA.CITY FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND IPA.ADDRESS_TYPE=''L'') SUPPLIER_CITY_NAME,
(SELECT IPA.POSTAL_CODE FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND IPA.ADDRESS_TYPE=''L'') SUPPLIER_POSTAL_ZONE,
(SELECT IPA.COUNTRY FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND IPA.ADDRESS_TYPE=''L'') SUPPLIER_COUNTRY,
HDR.PQ_TAX_IDENTIFIER   SUPPLIER_PARTY_TAXID,
NULL SUPPLIERENDPOINTID,
(SELECT IPA.PARTY_ORGANIZATION_NAME FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA 
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND IPA.ADDRESS_TYPE=''L'') SUPPLIERPARTY_LEGALREG_NAME,
HDR.PQ_TAX_IDENTIFIER  SUPPLIERPARTY_LEGAL_ID,
(SELECT IPA.COUNTRY FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND IPA.ADDRESS_TYPE=''L'') SUPPLIERPARTY_LEGAL_COUNTRY,
(SELECT IPA.PQ_ADDR_ATTN  FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID and address_type=''L'') SUPPLIER_CONTACT_ID,
(SELECT IPA.PQ_ADDR_ATTN  FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID and address_type=''L'') SUPPLIER_CONTACT_NAME,
(SELECT IPA.PQ_ADDR_TELEPHONE FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID and address_type=''L'') SUPPLIER_CONTACT_PHONE,
''CUSTOMERSERVICE@PROQUEST.COM'' SUPPLIER_CONTACT_EMAIL,
NULL CUSTOMEREND_POINTID,
NULL CUSTOMERENDPOINTID_SCHEME,
(SELECT IPA.PARTY_ID FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
where HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID and address_type=''B'') CUSTOMER_PARTY_ID,
(SELECT IPA.PARTY_ORGANIZATION_NAME FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_NAME,
(SELECT IPA.ADDRESS_LINE_1 FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_STREETNAME,
(SELECT IPA.ADDRESS_LINE_2 FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_ADD_STREETNAME,
(SELECT IPA.CITY FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_CITYNAME,
(SELECT IPA.POSTAL_CODE FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_POSTALZONE,
(SELECT IPA.COUNTRY FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_COUNTRYCODE,
HDR.PQ_CUSTOMER_TAX_IDENTIFIER  CUSTOMER_PARTY_TAX_ID,
''VAT'' CUSTOMER_TAX_SCHEME_IDENTIFIER,
(SELECT IPA.PARTY_ORGANIZATION_NAME FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_PARTY_LEGALNAME,
(SELECT IPA.PARTY_ID FROM PQINF.XXPQ_IP_INVOICE_ADDRESSES IPA
WHERE HDR.CLIENT_INVOICE_ID=IPA.CLIENT_INVOICE_ID AND ADDRESS_TYPE=''B'') CUSTOMER_PARTY_LEGALID,
FROM PQINF.XXPQ_IP_INVOICE_HEADER HDR
WHERE HDR.INVOICE_NUMBER=''60000593''')
INTO MYCLOB
FROM DUAL;

COMMIT;

F := UTL_FILE.FOPEN('DATA_PUMP_DIR','EDI_INV.xml','w',32767);
UTL_FILE.PUT(F,MYCLOB);
UTL_FILE.FCLOSE(F);
END;
/