VIEW: APPS.IBY_XML_FD_PAYEE_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 AND payee.employee_payment_flag ='Y') OR (payee.payment_location_id IS NOT NULL ) THEN XMLElement("Address", XMLConcat( XMLElement("AddressInternalID", DECODE(payee.employee_payment_flag, 'Y',payee.employee_address_id, NVL(payee.vendor_site_id, payee.party_site_id))), XMLElement("AddressLine1", payee.payee_address1), XMLElement("AddressLine2", payee.payee_address2), XMLElement("AddressLine3", payee.payee_address3), XMLElement("AddressLine4", payee.payee_address4), XMLElement("City", payee.payee_city), XMLElement("County", payee.payee_county), XMLElement("State", NVL(payee.payee_state, payee.payee_province)), XMLElement("Country", payee.payee_country), XMLElement("ISO3DigitCountry", te.iso_territory_code), XMLElement("CountryName", te.territory_short_name), XMLElement("PostalCode", payee.payee_postal_code), XMLElement("AddressName", payee.address_name), XMLElement("Addressee", payee.payee_addressee), XMLElement("AlternateAddressName", payee.alternate_site_name), XMLElement("PreFormattedConcatenatedAddress", payee.payee_address_concat), XMLElement("PreFormattedMailingAddress", payee.payee_address_concat) ) ) ELSE NULL END, XMLElement("ContactInfo", IBY_FD_EXTRACT_GEN_PVT.Get_PayeeContact(payee.payment_id,payee.payment_location_id,payee.PARTY_SITE_ID,payee.party_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, payee.party_id PARTY_ID, payee.payment_id PAYMENT_ID FROM iby_ext_fd_payee_1_0_vd payee, fnd_territories_vl te WHERE payee.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
AND PAYEE.EMPLOYEE_PAYMENT_FLAG ='Y') OR (PAYEE.PAYMENT_LOCATION_ID IS NOT NULL ) THEN XMLELEMENT("ADDRESS"
, XMLCONCAT( XMLELEMENT("ADDRESSINTERNALID"
, DECODE(PAYEE.EMPLOYEE_PAYMENT_FLAG
, 'Y'
, PAYEE.EMPLOYEE_ADDRESS_ID
, NVL(PAYEE.VENDOR_SITE_ID
, PAYEE.PARTY_SITE_ID)))
, XMLELEMENT("ADDRESSLINE1"
, PAYEE.PAYEE_ADDRESS1)
, XMLELEMENT("ADDRESSLINE2"
, PAYEE.PAYEE_ADDRESS2)
, XMLELEMENT("ADDRESSLINE3"
, PAYEE.PAYEE_ADDRESS3)
, XMLELEMENT("ADDRESSLINE4"
, PAYEE.PAYEE_ADDRESS4)
, XMLELEMENT("CITY"
, PAYEE.PAYEE_CITY)
, XMLELEMENT("COUNTY"
, PAYEE.PAYEE_COUNTY)
, XMLELEMENT("STATE"
, NVL(PAYEE.PAYEE_STATE
, PAYEE.PAYEE_PROVINCE))
, XMLELEMENT("COUNTRY"
, PAYEE.PAYEE_COUNTRY)
, XMLELEMENT("ISO3DIGITCOUNTRY"
, TE.ISO_TERRITORY_CODE)
, XMLELEMENT("COUNTRYNAME"
, TE.TERRITORY_SHORT_NAME)
, XMLELEMENT("POSTALCODE"
, PAYEE.PAYEE_POSTAL_CODE)
, XMLELEMENT("ADDRESSNAME"
, PAYEE.ADDRESS_NAME)
, XMLELEMENT("ADDRESSEE"
, PAYEE.PAYEE_ADDRESSEE)
, XMLELEMENT("ALTERNATEADDRESSNAME"
, PAYEE.ALTERNATE_SITE_NAME)
, XMLELEMENT("PREFORMATTEDCONCATENATEDADDRESS"
, PAYEE.PAYEE_ADDRESS_CONCAT)
, XMLELEMENT("PREFORMATTEDMAILINGADDRESS"
, PAYEE.PAYEE_ADDRESS_CONCAT) ) ) ELSE NULL END
, XMLELEMENT("CONTACTINFO"
, IBY_FD_EXTRACT_GEN_PVT.GET_PAYEECONTACT(PAYEE.PAYMENT_ID
, PAYEE.PAYMENT_LOCATION_ID
, PAYEE.PARTY_SITE_ID
, PAYEE.PARTY_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
, PAYEE.PARTY_ID PARTY_ID
, PAYEE.PAYMENT_ID PAYMENT_ID
FROM IBY_EXT_FD_PAYEE_1_0_VD PAYEE
, FND_TERRITORIES_VL TE
WHERE PAYEE.PAYEE_COUNTRY = TE.TERRITORY_CODE(+)