IBY_XML_FD_PAYEE_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)) , decode(pmt.PAYMENT_FUNCTION, 'PAYABLES_DISB', XMLElement("SupplierSiteDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'POZ_SUPPLIER_SITES_ALL_M', payee.vendor_site_id, NULL)), 'EMPLOYEE_EXP', XMLElement("SupplierSiteDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'HZ_PARTY_SITES', payee.party_site_id, NULL)), 'AR_CUSTOMER_REFUNDS', XMLElement("SupplierSiteDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'HZ_PARTY_SITES', payee.party_site_id, NULL))), 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.site_name), 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 iby_utility_pvt.get_view_param('FMT_TYPE') = 'IBY_FD_PPR_STATUS' THEN (CASE WHEN payee.remit_advice_delivery_method IS NOT NULL THEN XMLELEMENT("RemitAdviceDeliveryMethod", payee.remit_advice_delivery_method) END) ELSE (CASE WHEN iby_fd_extract_gen_pvt.get_sra_attribute(payee.payment_id,1) IS NOT NULL THEN xmlelement("RemitAdviceDeliveryMethod", iby_fd_extract_gen_pvt.get_sra_attribute(payee.payment_id,1)) END ) END, CASE WHEN iby_utility_pvt.get_view_param('FMT_TYPE') = 'IBY_FD_PPR_STATUS' THEN (CASE WHEN payee.remit_advice_delivery_method = 'EMAIL' or payee.remit_advice_delivery_method = 'EMAILPDF' or payee.remit_advice_delivery_method = 'EMAILCSV' THEN XMLELEMENT("RemitAdviceEmail", payee.remit_advice_email ) END) ELSE (CASE WHEN iby_fd_extract_gen_pvt.get_sra_attribute(payee.payment_id,1) = 'EMAIL' or iby_fd_extract_gen_pvt.get_sra_attribute(payee.payment_id,1) = 'EMAILPDF' or iby_fd_extract_gen_pvt.get_sra_attribute(payee.payment_id,1) = 'EMAILCSV' THEN xmlelement("RemitAdviceEmail", iby_fd_extract_gen_pvt.get_sra_attribute(payee.payment_id,2)) END ) 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_payments_all pmt, iby_ext_fd_payee_1_0_vd payee, 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)) ,

decode(pmt.PAYMENT_FUNCTION, 'PAYABLES_DISB', XMLElement("SupplierSiteDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'POZ_SUPPLIER_SITES_ALL_M', payee.vendor_site_id, NULL)),

'EMPLOYEE_EXP', XMLElement("SupplierSiteDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'HZ_PARTY_SITES', payee.party_site_id, NULL)),

'AR_CUSTOMER_REFUNDS', XMLElement("SupplierSiteDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'HZ_PARTY_SITES', payee.party_site_id, NULL))),

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.site_name), 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 iby_utility_pvt.get_view_param('FMT_TYPE') = 'IBY_FD_PPR_STATUS' THEN

(CASE WHEN payee.remit_advice_delivery_method IS NOT NULL THEN XMLELEMENT("RemitAdviceDeliveryMethod", payee.remit_advice_delivery_method) END)

ELSE

(CASE WHEN iby_fd_extract_gen_pvt.get_sra_attribute(payee.payment_id,1) IS NOT NULL THEN xmlelement("RemitAdviceDeliveryMethod", iby_fd_extract_gen_pvt.get_sra_attribute(payee.payment_id,1)) END )

END,

CASE WHEN iby_utility_pvt.get_view_param('FMT_TYPE') = 'IBY_FD_PPR_STATUS' THEN

(CASE WHEN payee.remit_advice_delivery_method = 'EMAIL' or payee.remit_advice_delivery_method = 'EMAILPDF' or payee.remit_advice_delivery_method = 'EMAILCSV' THEN XMLELEMENT("RemitAdviceEmail", payee.remit_advice_email ) END)

ELSE

(CASE WHEN iby_fd_extract_gen_pvt.get_sra_attribute(payee.payment_id,1) = 'EMAIL' or iby_fd_extract_gen_pvt.get_sra_attribute(payee.payment_id,1) = 'EMAILPDF' or iby_fd_extract_gen_pvt.get_sra_attribute(payee.payment_id,1) = 'EMAILCSV' THEN xmlelement("RemitAdviceEmail", iby_fd_extract_gen_pvt.get_sra_attribute(payee.payment_id,2)) END )

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_payments_all pmt, iby_ext_fd_payee_1_0_vd payee, fnd_territories_vl te WHERE payee.payee_country = te.territory_code(+) AND pmt.payment_id = payee.payment_id