1. Tables and Views for Financials
  2. IBY_XML_FD_DOC_SUP_1_0_V

IBY_XML_FD_DOC_SUP_1_0_V

Details

  • Schema: FUSION

  • Object owner: IBY

  • Object type: VIEW

Columns

Name

DOCSUP

PARTY_ID

PAYMENT_ID

Query

SQL_Statement

SELECT XMLConcat( XMLElement("PartyInternalID", docsup.party_id), XMLElement("PartyNumber", docsup.party_number), XMLElement("Name", docsup.party_name),XMLElement("TaxRegistrationType",pmt.inv_payee_tax_reg_type), XMLElement("TaxRegistrationNumber", docsup.tax_registration_number), XMLElement("LegalEntityRegistrationNumber", docsup.le_registration_number),

CASE

WHEN payee.attribute_category IS NULL

THEN NULL

ELSE XMLElement("PartyDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'HZ_PARTIES', docsup.party_id, NULL))

END, XMLElement("AlternateName", docsup.alternate_name), XMLElement("TaxOrganizationType",XMLElement("Code",vendor.organization_type_lookup_code),XMLElement("Meaning",orgtype.meaning)), XMLElement("PersonInfo",

XMLElement("TownOfBirth", docsup.town_of_birth),

XMLElement("BirthDate", to_char(docsup.date_of_birth, 'YYYY-MM-DD"T"HH24:MI:SS')),

XMLElement("CountryOfBirth", docsup.country_of_birth)), XMLElement("SupplierNumber", docsup.supplier_number),

XMLElement("SupplierDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'POZ_SUPPLIERS', docsup.vendor_id, NULL)),

DECODE(pmt.PAYMENT_FUNCTION, 'PAYABLES_DISB', XMLElement("SupplierSiteDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'POZ_SUPPLIER_SITES_ALL_M', docsup.vendor_site_id, NULL)), 'EMPLOYEE_EXP', XMLElement("SupplierSiteDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'HZ_PARTY_SITES', docsup.party_site_id, NULL)), 'AR_CUSTOMER_REFUNDS', XMLElement("SupplierSiteDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'HZ_PARTY_SITES', docsup.party_site_id, NULL))),

CASE

WHEN (docsup.payment_location_id IS NULL

AND docsup.employee_payment_flag ='Y')

OR (docsup.payment_location_id IS NOT NULL )

THEN XMLElement("Address", XMLConcat( XMLElement("AddressInternalID", NVL(docsup.vendor_site_id, docsup.party_site_id)), XMLElement("AddressLine1", docsup.payee_address1), XMLElement("AddressLine2", docsup.payee_address2), XMLElement("AddressLine3", docsup.payee_address3), XMLElement("AddressLine4", docsup.payee_address4), XMLElement("City", docsup.payee_city), XMLElement("County", docsup.payee_county), XMLElement("State", NVL(docsup.payee_state, docsup.payee_province)), XMLElement("Country", docsup.payee_country), XMLElement("ISO3DigitCountry", te.iso_territory_code), XMLElement("CountryName", te.territory_short_name), XMLElement("PostalCode", docsup.payee_postal_code), XMLElement("AddressName", docsup.site_name), XMLElement("AlternateAddressName", docsup.alternate_site_name), XMLElement("PreFormattedConcatenatedAddress", docsup.payee_address_concat), XMLElement("PreFormattedMailingAddress", docsup.payee_address_concat) ) )

ELSE NULL

END, XMLElement("ContactInfo", IBY_FD_EXTRACT_GEN_PVT.Get_PayeeContact(docsup.payment_id,docsup.payment_location_id,docsup.PARTY_SITE_ID,docsup.party_id)),

CASE

WHEN docsup.customer_num IS NULL

THEN NULL

ELSE XMLElement("FirstPartyReference", docsup.customer_num)

END,

CASE

WHEN docsup.remit_advice_delivery_method IS NULL

THEN NULL

ELSE xmlelement("RemitAdviceDeliveryMethod", docsup.remit_advice_delivery_method)

END,

CASE

WHEN docsup.remit_advice_delivery_method = 'EMAIL'

THEN xmlelement("RemitAdviceEmail", docsup.remit_advice_email)

END,

CASE

WHEN docsup.remit_advice_delivery_method = 'FAX'

THEN xmlelement("RemitAdviceFax", docsup.remit_advice_fax)

END ) docsup,

docsup.party_id,

docsup.payment_id

FROM iby_payments_all pmt,

iby_ext_fd_doc_sup_1_0_v docsup,

fnd_territories_vl te,

hz_parties payee,

poz_suppliers_v vendor,

poz_supplier_sites_all_m vendor_site,

fnd_lookups orgtype

WHERE docsup.payee_country = te.territory_code(+)

AND pmt.payment_id = docsup.payment_id

AND docsup.party_id = payee.party_id

AND docsup.party_id = vendor.party_id(+)

AND docsup.vendor_site_id = vendor_site.vendor_site_id(+)

AND vendor.organization_type_lookup_code = orgtype.lookup_code(+)

AND orgtype.lookup_type(+) = 'POZ_ORGANIZATION_TYPE'