VIEW: APPS.IBY_XML_FD_PAYEEM_1_0_V

Source

View Text - Preformatted

SELECT XMLConcat( XMLElement("PartyInternalID", payee.party_id), XMLElement("PartyNumber", payee.party_number), XMLElement("Name", payee.party_name), XMLElement("TaxRegistrationNumber", payee.tax_registration_number), XMLElement("LegalEntityRegistrationNumber", payee.le_registration_number), CASE WHEN payee.party_attr_cat IS NULL THEN NULL ELSE XMLElement("PartyDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'HZ_PARTIES', payee.party_id, NULL)) END, XMLElement("AlternateName", payee.alternate_name), XMLElement("SupplierNumber", payee.supplier_number), CASE WHEN payee.payee_supplier_attr_category IS NULL THEN NULL ELSE XMLElement("SupplierDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'PO_VENDORS', payee.vendor_id, NULL)) END, CASE WHEN payee.payee_spplr_site_attr_category IS NULL THEN NULL ELSE XMLElement("SupplierSiteDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'PO_VENDOR_SITES_ALL', payee.vendor_site_id, NULL)) END, CASE WHEN payee.payment_location_id IS NULL THEN NULL ELSE XMLElement("Address", XMLConcat( XMLElement("AddressInternalID", decode(pmt.employee_payment_flag, 'Y', pmt.employee_address_id, nvl(payee.vendor_site_id, payee.party_site_id))), XMLElement("AddressLine1", 'XXXX'), XMLElement("AddressLine2", 'XXXX'), XMLElement("AddressLine3", 'XXXX'), XMLElement("AddressLine4", 'XXXX'), XMLElement("City", 'XXXX'), XMLElement("County", 'XXXX'), XMLElement("State", 'XXXX'), XMLElement("Country", 'XXXX'), XMLElement("ISO3DigitCountry", 'XXXX'), XMLElement("CountryName", 'XXXX'), XMLElement("PostalCode", 'XXXX'), XMLElement("AddressName", payee.address_name), XMLElement("Addressee", payee.payee_addressee), XMLElement("AlternateAddressName", payee.alternate_site_name), XMLElement("PreFormattedConcatenatedAddress", 'XXXX'), XMLElement("PreFormattedMailingAddress", 'XXXX') ) ) END, XMLElement("ContactInfo", IBY_FD_EXTRACT_GEN_PVT.Get_PayeeContact(pmt.payment_id)), CASE WHEN payee.customer_num IS NULL THEN NULL ELSE XMLElement("FirstPartyReference", payee.customer_num) END, CASE WHEN payee.remit_advice_delivery_method IS NULL THEN NULL ELSE xmlelement("RemitAdviceDeliveryMethod", payee.remit_advice_delivery_method) END, CASE WHEN payee.remit_advice_delivery_method = 'EMAIL' THEN xmlelement("RemitAdviceEmail", payee.remit_advice_email) END, CASE WHEN payee.remit_advice_delivery_method = 'FAX' THEN xmlelement("RemitAdviceFax", payee.remit_advice_fax) END , xmlelement("SupplierSiteCode", payee.site_name)), payee.party_id, payee.payment_id FROM iby_ext_fd_payee_1_0_vd payee, iby_payments_all pmt, fnd_territories_vl te WHERE payee.payment_id = pmt.payment_id AND pmt.payee_country = te.territory_code(+)

View Text - HTML Formatted

SELECT XMLCONCAT( XMLELEMENT("PARTYINTERNALID"
, PAYEE.PARTY_ID)
, XMLELEMENT("PARTYNUMBER"
, PAYEE.PARTY_NUMBER)
, XMLELEMENT("NAME"
, PAYEE.PARTY_NAME)
, XMLELEMENT("TAXREGISTRATIONNUMBER"
, PAYEE.TAX_REGISTRATION_NUMBER)
, XMLELEMENT("LEGALENTITYREGISTRATIONNUMBER"
, PAYEE.LE_REGISTRATION_NUMBER)
, CASE WHEN PAYEE.PARTY_ATTR_CAT IS NULL THEN NULL ELSE XMLELEMENT("PARTYDESCRIPTIVEFLEXFIELD"
, IBY_EXTRACTGEN_PVT.GET_DFFS( 'HZ_PARTIES'
, PAYEE.PARTY_ID
, NULL)) END
, XMLELEMENT("ALTERNATENAME"
, PAYEE.ALTERNATE_NAME)
, XMLELEMENT("SUPPLIERNUMBER"
, PAYEE.SUPPLIER_NUMBER)
, CASE WHEN PAYEE.PAYEE_SUPPLIER_ATTR_CATEGORY IS NULL THEN NULL ELSE XMLELEMENT("SUPPLIERDESCRIPTIVEFLEXFIELD"
, IBY_EXTRACTGEN_PVT.GET_DFFS( 'PO_VENDORS'
, PAYEE.VENDOR_ID
, NULL)) END
, CASE WHEN PAYEE.PAYEE_SPPLR_SITE_ATTR_CATEGORY IS NULL THEN NULL ELSE XMLELEMENT("SUPPLIERSITEDESCRIPTIVEFLEXFIELD"
, IBY_EXTRACTGEN_PVT.GET_DFFS( 'PO_VENDOR_SITES_ALL'
, PAYEE.VENDOR_SITE_ID
, NULL)) END
, CASE WHEN PAYEE.PAYMENT_LOCATION_ID IS NULL THEN NULL ELSE XMLELEMENT("ADDRESS"
, XMLCONCAT( XMLELEMENT("ADDRESSINTERNALID"
, DECODE(PMT.EMPLOYEE_PAYMENT_FLAG
, 'Y'
, PMT.EMPLOYEE_ADDRESS_ID
, NVL(PAYEE.VENDOR_SITE_ID
, PAYEE.PARTY_SITE_ID)))
, XMLELEMENT("ADDRESSLINE1"
, 'XXXX')
, XMLELEMENT("ADDRESSLINE2"
, 'XXXX')
, XMLELEMENT("ADDRESSLINE3"
, 'XXXX')
, XMLELEMENT("ADDRESSLINE4"
, 'XXXX')
, XMLELEMENT("CITY"
, 'XXXX')
, XMLELEMENT("COUNTY"
, 'XXXX')
, XMLELEMENT("STATE"
, 'XXXX')
, XMLELEMENT("COUNTRY"
, 'XXXX')
, XMLELEMENT("ISO3DIGITCOUNTRY"
, 'XXXX')
, XMLELEMENT("COUNTRYNAME"
, 'XXXX')
, XMLELEMENT("POSTALCODE"
, 'XXXX')
, XMLELEMENT("ADDRESSNAME"
, PAYEE.ADDRESS_NAME)
, XMLELEMENT("ADDRESSEE"
, PAYEE.PAYEE_ADDRESSEE)
, XMLELEMENT("ALTERNATEADDRESSNAME"
, PAYEE.ALTERNATE_SITE_NAME)
, XMLELEMENT("PREFORMATTEDCONCATENATEDADDRESS"
, 'XXXX')
, XMLELEMENT("PREFORMATTEDMAILINGADDRESS"
, 'XXXX') ) ) END
, XMLELEMENT("CONTACTINFO"
, IBY_FD_EXTRACT_GEN_PVT.GET_PAYEECONTACT(PMT.PAYMENT_ID))
, CASE WHEN PAYEE.CUSTOMER_NUM IS NULL THEN NULL ELSE XMLELEMENT("FIRSTPARTYREFERENCE"
, PAYEE.CUSTOMER_NUM) END
, CASE WHEN PAYEE.REMIT_ADVICE_DELIVERY_METHOD IS NULL THEN NULL ELSE XMLELEMENT("REMITADVICEDELIVERYMETHOD"
, PAYEE.REMIT_ADVICE_DELIVERY_METHOD) END
, CASE WHEN PAYEE.REMIT_ADVICE_DELIVERY_METHOD = 'EMAIL' THEN XMLELEMENT("REMITADVICEEMAIL"
, PAYEE.REMIT_ADVICE_EMAIL) END
, CASE WHEN PAYEE.REMIT_ADVICE_DELIVERY_METHOD = 'FAX' THEN XMLELEMENT("REMITADVICEFAX"
, PAYEE.REMIT_ADVICE_FAX) END
, XMLELEMENT("SUPPLIERSITECODE"
, PAYEE.SITE_NAME))
, PAYEE.PARTY_ID
, PAYEE.PAYMENT_ID
FROM IBY_EXT_FD_PAYEE_1_0_VD PAYEE
, IBY_PAYMENTS_ALL PMT
, FND_TERRITORIES_VL TE
WHERE PAYEE.PAYMENT_ID = PMT.PAYMENT_ID
AND PMT.PAYEE_COUNTRY = TE.TERRITORY_CODE(+)