IBY_XML_FD_PAYEEM_1_0

Details

Schema: FUSION
Object owner: IBY
Object type: VIEW

Columns

Name
PAYEE PARTY_ID PAYMENT_ID

Name

PAYEE

PARTY_ID

PAYMENT_ID

Query

SQL_Statement
SELECT XMLConcat( XMLElement("PartyInternalID", payee.party_id), XMLElement("PartyNumber", payee.party_number), XMLElement("Name", payee.party_name),XMLElement("TaxRegistrationType",pmt.payee_tax_registration_type), XMLElement("TaxRegistrationNumber", payee.tax_registration_number), XMLElement("LegalEntityRegistrationNumber", payee.le_registration_number), XMLElement("LegalEntityRegistrationType", payee.le_registration_num_type), 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("TaxOrganizationType",XMLElement("Code",payee.organization_type_lookup_code),XMLElement("Meaning",payee.org_type_meaning)), XMLElement("PersonInfo", XMLElement("TownOfBirth", payee.town_of_birth),XMLElement("BirthDate", to_char(payee.date_of_birth, 'YYYY-MM-DD"T"HH24:MI:SS')),XMLElement("CountryOfBirth", payee.country_of_birth)), XMLElement("SupplierNumber", payee.supplier_number),XMLElement("SupplierDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'POZ_SUPPLIERS', payee.vendor_id, NULL)),XMLElement("SupplierSiteDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'POZ_SUPPLIER_SITES_ALL_M', payee.vendor_site_id, NULL)), CASE WHEN payee.payment_location_id IS NULL THEN NULL ELSE 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", '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.site_name), XMLElement("AlternateAddressName", payee.alternate_site_name), XMLElement("PreFormattedConcatenatedAddress", 'XXXX'), XMLElement("PreFormattedMailingAddress", 'XXXX') ) ) 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 ) payee, 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.payee_country = te.territory_code(+) AND pmt.payment_id = payee.payment_id

SQL_Statement

SELECT XMLConcat( XMLElement("PartyInternalID", payee.party_id), XMLElement("PartyNumber", payee.party_number), XMLElement("Name", payee.party_name),XMLElement("TaxRegistrationType",pmt.payee_tax_registration_type), XMLElement("TaxRegistrationNumber", payee.tax_registration_number), XMLElement("LegalEntityRegistrationNumber", payee.le_registration_number), XMLElement("LegalEntityRegistrationType", payee.le_registration_num_type), 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("TaxOrganizationType",XMLElement("Code",payee.organization_type_lookup_code),XMLElement("Meaning",payee.org_type_meaning)), XMLElement("PersonInfo", XMLElement("TownOfBirth", payee.town_of_birth),XMLElement("BirthDate", to_char(payee.date_of_birth, 'YYYY-MM-DD"T"HH24:MI:SS')),XMLElement("CountryOfBirth", payee.country_of_birth)), XMLElement("SupplierNumber", payee.supplier_number),XMLElement("SupplierDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'POZ_SUPPLIERS', payee.vendor_id, NULL)),XMLElement("SupplierSiteDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'POZ_SUPPLIER_SITES_ALL_M', payee.vendor_site_id, NULL)), CASE WHEN payee.payment_location_id IS NULL THEN NULL ELSE 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", '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.site_name), XMLElement("AlternateAddressName", payee.alternate_site_name), XMLElement("PreFormattedConcatenatedAddress", 'XXXX'), XMLElement("PreFormattedMailingAddress", 'XXXX') ) ) 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 ) payee, 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.payee_country = te.territory_code(+) AND pmt.payment_id = payee.payment_id