IBY_EXT_FD_PAYEE_1_0

Details

Schema: FUSION
Object owner: IBY
Object type: VIEW

Columns

Name
PAYMENT_ID PARTY_ID VENDOR_ID VENDOR_SITE_ID SUPPLIER_NUMBER PARTY_NUMBER PARTY_NAME ALTERNATE_NAME SITE_NAME ALTERNATE_SITE_NAME PARTY_TYPE PARTY_TYPE_MEANING TAX_REGISTRATION_NUMBER LE_REGISTRATION_NUMBER LE_REGISTRATION_NUM_TYPE PARTY_ATTR_CAT PAYMENT_LOCATION_ID CUSTOMER_NUM PARTY_SITE_ID PAYEE_SUPPLIER_ATTR_CATEGORY PAYEE_SPPLR_SITE_ATTR_CATEGORY REMIT_ADVICE_DELIVERY_METHOD REMIT_ADVICE_EMAIL REMIT_ADVICE_FAX EMPLOYEE_PAYMENT_FLAG EMPLOYEE_ADDRESS_ID PAYEE_ADDRESS1 PAYEE_ADDRESS2 PAYEE_ADDRESS3 PAYEE_ADDRESS4 PAYEE_CITY PAYEE_COUNTY PAYEE_STATE PAYEE_PROVINCE PAYEE_COUNTRY PAYEE_POSTAL_CODE PAYEE_ADDRESS_CONCAT DATE_OF_BIRTH COUNTRY_OF_BIRTH TOWN_OF_BIRTH ORGANIZATION_TYPE_LOOKUP_CODE ORG_TYPE_MEANING

Name

PAYMENT_ID

PARTY_ID

VENDOR_ID

VENDOR_SITE_ID

SUPPLIER_NUMBER

PARTY_NUMBER

PARTY_NAME

ALTERNATE_NAME

SITE_NAME

ALTERNATE_SITE_NAME

PARTY_TYPE

PARTY_TYPE_MEANING

TAX_REGISTRATION_NUMBER

LE_REGISTRATION_NUMBER

LE_REGISTRATION_NUM_TYPE

PARTY_ATTR_CAT

PAYMENT_LOCATION_ID

CUSTOMER_NUM

PARTY_SITE_ID

PAYEE_SUPPLIER_ATTR_CATEGORY

PAYEE_SPPLR_SITE_ATTR_CATEGORY

REMIT_ADVICE_DELIVERY_METHOD

REMIT_ADVICE_EMAIL

REMIT_ADVICE_FAX

EMPLOYEE_PAYMENT_FLAG

EMPLOYEE_ADDRESS_ID

PAYEE_ADDRESS1

PAYEE_ADDRESS2

PAYEE_ADDRESS3

PAYEE_ADDRESS4

PAYEE_CITY

PAYEE_COUNTY

PAYEE_STATE

PAYEE_PROVINCE

PAYEE_COUNTRY

PAYEE_POSTAL_CODE

PAYEE_ADDRESS_CONCAT

DATE_OF_BIRTH

COUNTRY_OF_BIRTH

TOWN_OF_BIRTH

ORGANIZATION_TYPE_LOOKUP_CODE

ORG_TYPE_MEANING

Query

SQL_Statement
SELECT pmt.payment_id payment_id, pmt.payee_party_id party_id, pmt.payee_supplier_id vendor_id, pmt.supplier_site_id vendor_site_id, pmt.payee_supplier_number supplier_number, pmt.payee_party_number party_number, pmt.payee_name party_name, pmt.payee_alternate_name alternate_name, pmt.payee_supplier_site_name site_name, NVL(pmt.payee_supplier_site_alt_name, pmt.payee_site_alternate_name) alternate_site_name, NULL PARTY_TYPE, NULL PARTY_TYPE_MEANING, pmt.payee_tax_registration_num tax_registration_number, decode(pmt.payment_function, 'EMPLOYEE_EXP', IBY_FD_EXTRACT_GEN_PVT.Get_Payee_Emp_LE_Reg(pmt.payment_id), pmt.payee_le_registration_num) le_registration_number, decode(pmt.payment_function, 'EMPLOYEE_EXP', IBY_FD_EXTRACT_GEN_PVT.Get_Payee_Emp_LE_RegType(pmt.payment_id), null) le_registration_num_type, pmt.payee_party_attr_category party_attr_cat, pmt.remit_to_location_id payment_location_id, pmt.payee_first_party_reference customer_num, pmt.party_site_id, pmt.payee_supplier_attr_category, pmt.payee_spplr_site_attr_category, ep.REMIT_ADVICE_DELIVERY_METHOD, ep.REMIT_ADVICE_EMAIL, ep.REMIT_ADVICE_FAX, pmt.employee_payment_flag employee_payment_flag, pmt.employee_address_id employee_address_id, decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.addr_line1, 'HOME', emp_home_addr.addr_line1, 'ORA_MAIL', emp_mail_addr.addr_line1, pmt.payee_address1) payee_address1, decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.addr_line2, 'HOME', emp_home_addr.addr_line2, 'ORA_MAIL', emp_mail_addr.addr_line2, pmt.payee_address2) payee_address2, decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.addr_line3, 'HOME', emp_home_addr.addr_line3, 'ORA_MAIL', emp_mail_addr.addr_line3, pmt.payee_address3) payee_address3, decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.addr_line4, 'HOME', emp_home_addr.addr_line4, 'ORA_MAIL', emp_mail_addr.addr_line4, pmt.payee_address4) payee_address4, decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.city, 'HOME', emp_home_addr.city, 'ORA_MAIL', emp_mail_addr.city, pmt.payee_city) payee_city, decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.county, 'HOME', emp_home_addr.county, 'ORA_MAIL', emp_mail_addr.county, pmt.payee_county) payee_county, decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.state, 'HOME', emp_home_addr.state, 'ORA_MAIL', emp_mail_addr.state, pmt.payee_state) payee_state, decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.province, 'HOME', emp_home_addr.province, 'ORA_MAIL', emp_mail_addr.province, pmt.payee_province) payee_province, decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.country, 'HOME', emp_home_addr.country, 'ORA_MAIL', emp_mail_addr.country, pmt.payee_country) payee_country, decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.postal_code, 'HOME', emp_home_addr.postal_code, 'ORA_MAIL', emp_mail_addr.postal_code, pmt.payee_postal_code) payee_postal_code, decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.addr_concat, 'HOME', emp_home_addr.addr_concat, 'ORA_MAIL', emp_mail_addr.addr_concat, pmt.payee_address_concat) payee_address_concat, person.date_of_birth, person.country_of_birth, person.town_of_birth town_of_birth, vendor.organization_type_lookup_code, orgtype.meaning org_type_meaning FROM iby_payments_all pmt, IBY_EXTERNAL_PAYEES_ALL ep, iby_ext_fd_emp_office_addr emp_office_addr, iby_ext_fd_emp_home_addr emp_home_addr, iby_ext_fd_emp_mail_addr_v emp_mail_addr, per_persons person, poz_suppliers_v vendor, fnd_lookups orgtype WHERE pmt.EXT_PAYEE_ID = ep.EXT_PAYEE_ID AND pmt.payment_id = emp_office_addr.payment_id(+) AND pmt.payment_id = emp_home_addr.payment_id(+) AND pmt.payment_id = emp_mail_addr.payment_id(+) AND pmt.employee_person_id = person.person_id(+) AND pmt.payee_party_id = vendor.party_id(+) AND vendor.organization_type_lookup_code = orgtype.lookup_code(+) AND orgtype.lookup_type(+) = 'POZ_ORGANIZATION_TYPE'

SQL_Statement

SELECT pmt.payment_id payment_id,

pmt.payee_party_id party_id,

pmt.payee_supplier_id vendor_id,

pmt.supplier_site_id vendor_site_id,

pmt.payee_supplier_number supplier_number,

pmt.payee_party_number party_number,

pmt.payee_name party_name,

pmt.payee_alternate_name alternate_name,

pmt.payee_supplier_site_name site_name,

NVL(pmt.payee_supplier_site_alt_name, pmt.payee_site_alternate_name) alternate_site_name,

NULL PARTY_TYPE,

NULL PARTY_TYPE_MEANING,

pmt.payee_tax_registration_num tax_registration_number,

decode(pmt.payment_function, 'EMPLOYEE_EXP', IBY_FD_EXTRACT_GEN_PVT.Get_Payee_Emp_LE_Reg(pmt.payment_id), pmt.payee_le_registration_num) le_registration_number,

decode(pmt.payment_function, 'EMPLOYEE_EXP', IBY_FD_EXTRACT_GEN_PVT.Get_Payee_Emp_LE_RegType(pmt.payment_id), null) le_registration_num_type,

pmt.payee_party_attr_category party_attr_cat,

pmt.remit_to_location_id payment_location_id,

pmt.payee_first_party_reference customer_num,

pmt.party_site_id,

pmt.payee_supplier_attr_category,

pmt.payee_spplr_site_attr_category,

ep.REMIT_ADVICE_DELIVERY_METHOD,

ep.REMIT_ADVICE_EMAIL,

ep.REMIT_ADVICE_FAX,

pmt.employee_payment_flag employee_payment_flag,

pmt.employee_address_id employee_address_id,

decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.addr_line1, 'HOME', emp_home_addr.addr_line1, 'ORA_MAIL', emp_mail_addr.addr_line1, pmt.payee_address1) payee_address1,

decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.addr_line2, 'HOME', emp_home_addr.addr_line2, 'ORA_MAIL', emp_mail_addr.addr_line2, pmt.payee_address2) payee_address2,

decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.addr_line3, 'HOME', emp_home_addr.addr_line3, 'ORA_MAIL', emp_mail_addr.addr_line3, pmt.payee_address3) payee_address3,

decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.addr_line4, 'HOME', emp_home_addr.addr_line4, 'ORA_MAIL', emp_mail_addr.addr_line4, pmt.payee_address4) payee_address4,

decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.city, 'HOME', emp_home_addr.city, 'ORA_MAIL', emp_mail_addr.city, pmt.payee_city) payee_city,

decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.county, 'HOME', emp_home_addr.county, 'ORA_MAIL', emp_mail_addr.county, pmt.payee_county) payee_county,

decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.state, 'HOME', emp_home_addr.state, 'ORA_MAIL', emp_mail_addr.state, pmt.payee_state) payee_state,

decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.province, 'HOME', emp_home_addr.province, 'ORA_MAIL', emp_mail_addr.province, pmt.payee_province) payee_province,

decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.country, 'HOME', emp_home_addr.country, 'ORA_MAIL', emp_mail_addr.country, pmt.payee_country) payee_country,

decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.postal_code, 'HOME', emp_home_addr.postal_code, 'ORA_MAIL', emp_mail_addr.postal_code, pmt.payee_postal_code) payee_postal_code,

decode(pmt.employee_address_code, 'OFFICE', emp_office_addr.addr_concat, 'HOME', emp_home_addr.addr_concat, 'ORA_MAIL', emp_mail_addr.addr_concat, pmt.payee_address_concat) payee_address_concat,

person.date_of_birth,

person.country_of_birth,

person.town_of_birth town_of_birth,

vendor.organization_type_lookup_code,

orgtype.meaning org_type_meaning

FROM iby_payments_all pmt,

IBY_EXTERNAL_PAYEES_ALL ep,

iby_ext_fd_emp_office_addr emp_office_addr,

iby_ext_fd_emp_home_addr emp_home_addr,

iby_ext_fd_emp_mail_addr_v emp_mail_addr,

per_persons person,

poz_suppliers_v vendor,

fnd_lookups orgtype

WHERE pmt.EXT_PAYEE_ID = ep.EXT_PAYEE_ID

AND pmt.payment_id = emp_office_addr.payment_id(+)

AND pmt.payment_id = emp_home_addr.payment_id(+)

AND pmt.payment_id = emp_mail_addr.payment_id(+)

AND pmt.employee_person_id = person.person_id(+)

AND pmt.payee_party_id = vendor.party_id(+)

AND vendor.organization_type_lookup_code = orgtype.lookup_code(+)

AND orgtype.lookup_type(+) = 'POZ_ORGANIZATION_TYPE'