AP Remit Email Address for EFT Vendor Site Report
SELECT AL1.OU_Number,
AL1.Supplier_Name,
AL1.Supplier_Number,
AL1.Site_Code,
AL1.Address_City,
AL1.Pay_Group,
NVL(AL1.Pay_Method,' ') DEFAULT_PAY_METHOD,
NVL(AL1.Site_Pay_Method,' ') Site_Pay_Method,
NVL(AL1.Remit_Email,' ') Remit_Email
FROM
(SELECT hou.short_code OU_Number,
vh.segment1 Supplier_Number,
vh.vendor_name Supplier_Name,
vh.vendor_name_alt Alternate_Supplier_Name,
vh.vendor_type_lookup_code Supplier_Type,
vs.attribute3 Mfg_Area,
vs.attribute14 Legacy_Supplier_Number,
vs.vendor_site_code Site_Code,
vs.vendor_site_code_alt Alternate_Supplier_Site,
-- nvl(vs.vat_registration_num
-- ,vh.vat_registration_num) VAT_Registration_Number,
-- apt.name Site_Payment_Term,
vs.purchasing_site_flag Purchasing_Allowed,
vs.pay_site_flag Payment_Allowed,
vh.small_business_flag Small_Business,
vs.pay_on_code Pay_On,
decode(vs.match_option,'R','Receipt'
,'Purchase Order') Match_Option,
alt.vendor_site_code Alternate_pay_site,
vs.address_lines_alt Alternate_Address,
vs.address_line1 Address_Line1,
vs.address_line2 Address_Line2,
vs.address_line3 Address_Line3,
vs.address_line4 Address_Line4,
vs.city Address_City,
vs.state Address_State,
vs.province Address_Province,
vs.zip Zip_Postal_Code,
vs.country Address_Country,
vs.phone Phone_Number,
vs.fax Fax_Number,
vs.email_address PO_Email_Address,
vs.terms_date_basis Terms_Date_Base,
-- vs.vat_code VAT_Code,
-- gl1.concatenated_segments AP_Liability_Account,
-- gl2.concatenated_segments Prepayment_Account,
-- gl3.concatenated_segments Future_Payment_Account,
vs.invoice_currency_code Invoice_Currency,
vs.payment_currency_code Payment_Currency,
-- vs.allow_awt_flag Allow_Witholding_Tax,
vs.pay_group_lookup_code Pay_Group,
iep.iep_default_payment_method Pay_Method,
iep.ipm_payment_method_code Site_Pay_Method,
iep.iep_payment_reason_code Pay_Reason,
decode(vs.bank_charge_bearer,'I','ON pay'
, 'Supplier Pay') Bank_Charge,
-- vs.exclusive_payment_flag Pay_Alone,
bac.acc_bank_account_name Bank_Account_Name,
bac.acc_bank_account_name_alt Alt_Bank_Account_Name,
bac.acc_bank_number Bank_Number,
bac.acc_bank_name Bank_Name,
bac.acc_bank_name_alt Alt_Bank_Name,
bac.acc_branch_number Branch_Number,
bac.acc_branch_name Branch_Name,
bac.acc_bank_branch_name_alt Alt_Branch_Name,
bac.acc_branch_province Branch_Province,
bac.acc_branch_city Branch_City,
bac.acc_country_code Bank_Country_code,
bac.acc_currency_code Bank_Account_Currency,
-- SUBSTR (bac.acc_bank_account_num,
-- (LENGTH (bac.acc_bank_account_num) - 3),
-- 4)
-- Bank_Acct_Num_Last_4,
bac.iao_primary_flag Primary_flag,
iep.ipi_order_of_preference Preference,
bac.acc_bank_account_type Bank_Type,
TRUNC (bac.acc_start_date) Bank_Account_Start_Date,
TRUNC (bac.acc_end_date) Bank_Account_End_Date,
iep.iep_remit_delivery_method Remit_Deliver_Method,
iep.iep_remit_advice_email Remit_Email,
vh.end_date_active Supplier_End_Date,
case when nvl(vh.end_date_active,sysdate+1) > sysdate
then 'ACTIVE'
else 'INACTIVE' end Supplier_Status,
vs.inactive_date Supplier_Site_End_Date,
case when nvl(vs.inactive_date,sysdate+1) > sysdate
then 'ACTIVE'
else 'INACTIVE' end Supplier_Site_Status,
iep.ipi_end_date Pay_Instrument_End_Date,
vh.creation_date Supplier_Creation_Date,
-- fhc.description Supplier_Created_By,
vh.last_update_date Supplier_Last_Update_Date,
-- fhu.description Supplier_Last_UPdated_By,
vs.creation_date Site_Creation_Date,
-- fsc.description Site_Created_By,
vs.last_update_date Site_Last_Update_Date,
-- fsu.description Site_Last_UPdated_By,
-- bac.acc_creation_date Bank_Account_Creation_Date,
-- bac.acc_created_by Bank_Account_Created_By,
-- bac.acc_last_update_date Bank_Account_Update_Date,
-- bac.acc_last_updated_by Bank_Account_Updated_By,
vs.prc_bu_id Org_Id,
vh.vendor_id Vendor_Id,
vh.party_id Party_Id,
vs.vendor_site_id Vendor_Site_Id,
vs.party_site_id Party_Site_Id,
iep.iep_ext_payee_id Ext_Payee_Id,
iep.ipi_instrument_payment_use_id Instr_Pmt_Use_Id,
bac.iao_account_owner_id Acct_Owner_Id,
bac.ext_bank_account_id Ext_Bank_Acct_Id,
bac.acc_bank_id Acc_Bank_Id,
bac.acc_branch_id Acc_Branch_Id,
-- awt.name Withholding_Tax_Name,
-- awt.description Withholding_Tax_Description,
vh.organization_type_lookup_code Organization_Type,
-- case when bac.org_id = 3236
-- then bac.temp_column
-- else null
-- end Temp_column,
--
-- vh.purchasing_hold_reason Purchasing_Hold_Reason,
-- vh.create_debit_memo_flag Supp_Create_Debit_Memo,
-- vh.qty_rcv_tolerance Qty_Rcv_Tolerance,
-- vh.terms_date_basis Supp_Terms_Date_Basis,
-- vh.num_1099 Fed_Tax_Id,
vh.women_owned_flag Women_Owned,
-- vh.hold_all_payments_flag Hdr_Hold_All_Payments,
-- vh.hold_date Hdr_Hold_Date,
-- vh.hold_flag Hdr_On_Hold_Flag,
-- vh.hold_future_payments_flag Hdr_Hold_Future_Pmts,
-- vh.hold_reason Hdr_Hold_Reason,
-- hdt.name Hdr_Payment_Term,
hzp.mission_statement Mission_Statement,
vs.create_debit_memo_flag Site_Create_Debit_Memo,
vs.pay_on_receipt_summary_code Invoice_Summary_Level,
vs.supplier_notif_method Supplier_Notif_Method,
vs.purchasing_site_flag Purchasing_Site_Flag,
vs.pay_site_flag Pay_Site_Flag,
vs.hold_all_payments_flag Hold_All_Payments,
-- vs.hold_future_payments_flag Site_Hold_Future_Pmts,
vs.hold_unmatched_invoices_flag Hold_Unmatched_Invoices,
vs.fob_lookup_code FOB_Code,
vs.freight_terms_lookup_code Freight_Terms_Code,
vs.ship_via_lookup_code Ship_Via_Code,
--
-- DECODE(vh.receipt_required_flag,
-- 'N', DECODE (vh.inspection_required_flag, 'N', '2-Way', '2-Way' ),
-- 'Y', DECODE (vh.inspection_required_flag, 'N', '3-Way', 'Y', '4-Way',
-- '3-Way' )) Match_Approval_Level,
--
lcs.location_code Dflt_Ship_to_Loc_Cd,
lcb.location_code Dflt_Bill_to_Loc_Cd,
vh.name_control Name_Control,
vs.payment_priority Payment_Priority,
vs.attribute8 Supplier_Additional_Email
FROM POZ_SUPPLIERS_V vh -- vendor_header
,POZ_SUPPLIER_SITES_V vs -- vendor_site
,POZ_SUPPLIER_SITES_V alt -- alternate_pay_site
,POZ_SITE_ASSIGNMENTS_ALL_M sup -- site_assignment
--,apps.ap_awt_groups awt -- withholding tax groups
,hr_operating_units hou,
-- ap_terms apt,
-- ap_terms hdt,
gl_code_combinations_v gl1, -- AP Liability
gl_code_combinations_v gl2, -- Prepayment
gl_code_combinations_v gl3, -- Future Payment
hz_parties hzp,
hr_locations_all lcs,
hr_locations_all lcb,
(SELECT iep.org_id org_id,
iep.payee_party_id party_id,
iep.supplier_site_id vendor_site_id,
iep.party_site_id party_site_id,
iep.ext_payee_id iep_ext_payee_id,
iep.default_payment_method_code iep_default_payment_method,
ipm.payment_method_code ipm_payment_method_code,
iep.remit_advice_delivery_method iep_remit_delivery_method,
iep.remit_advice_email iep_remit_advice_email,
iep.creation_date iep_creation_date,
iep.created_by iep_created_by,
iep.last_update_date iep_last_update_date,
iep.last_updated_by iep_last_updated_by,
pis.ext_pmt_party_id ipi_ext_pmt_party_id,
pis.instrument_payment_use_id ipi_instrument_payment_use_id,
pis.instrument_id ipi_instrument_id,
pis.order_of_preference ipi_order_of_preference,
pis.end_date ipi_end_date,
pis.creation_date ipi_creation_date,
pis.created_by ipi_created_by,
pis.last_update_date ipi_last_update_date,
pis.last_updated_by ipi_last_updated_by,
ieb.bank_account_num ieb_bank_account_num,
ieb.iban ieb_iban,
iep.payment_reason_code iep_payment_reason_code
FROM iby_external_payees_all iep,
iby_pmt_instr_uses_all pis,
iby_ext_bank_accounts ieb,
iby_ext_party_pmt_mthds ipm
WHERE /* iep.org_id IS NOT NULL Org_id Null in the table fusion
AND */ipm.ext_pmt_party_id(+) = iep.ext_payee_id
and ipm.primary_flag(+) = 'Y'
AND NVL (pis.end_date(+), SYSDATE + 9999) > SYSDATE
AND iep.ext_payee_id = pis.ext_pmt_party_id(+)
AND ieb.ext_bank_account_id(+) = pis.instrument_id) IEP, -- External Payee
(SELECT vs.PRC_BU_ID org_id,
vh.vendor_id vendor_id,
vh.party_id party_id,
vs.vendor_site_id vendor_site_id,
vs.party_site_id party_site_id,
iao.ext_bank_account_id ext_bank_account_id,
iao.account_owner_party_id account_owner_party_id,
iao.account_owner_id iao_account_owner_id,
iao.end_date iao_end_date,
iao.primary_flag iao_primary_flag,
iao.creation_date iao_creation_date,
iao.created_by iao_created_by_id,
-- foc.description iao_created_by,
iao.last_update_date iao_last_update_date,
iao.last_updated_by iao_last_updated_by_id,
-- fou.description iao_last_updated_by,
acc.country_code acc_country_code,
acc.branch_id acc_branch_id,
acc.bank_id acc_bank_id,
acc.bank_account_name acc_bank_account_name,
acc.bank_account_name_alt acc_bank_account_name_alt,
acc.bank_account_num acc_bank_account_num,
acc.currency_code acc_currency_code,
acc.bank_account_type acc_bank_account_type,
acc.start_date acc_start_date,
acc.end_date acc_end_date,
acc.bank_name acc_bank_name,
acc.bank_number acc_bank_number,
acc.branch_name acc_branch_name,
acc.branch_number acc_branch_number,
hp.province acc_branch_province,
hp.city acc_branch_city,
acc.bank_branch_name_alt acc_bank_branch_name_alt,
acc.bank_name_alt acc_bank_name_alt,
acc.creation_date acc_creation_date,
acc.created_by acc_created_by_id,
-- fac.description acc_created_by,
acc.last_update_date acc_last_update_date,
acc.last_updated_by acc_last_updated_by_id,
-- fau.description acc_last_updated_by,
acc.bank_account_num temp_column
FROM iby_account_owners iao,
iby_payee_all_bankacct_v acc,
poz_suppliers_v vh,
POZ_SUPPLIER_SITES_ALL_M vs
/* apps.fnd_user foc,
apps.fnd_user fou,
apps.fnd_user fac,
apps.fnd_user fau,*/
,hz_parties hp
WHERE iao.ext_bank_account_id = acc.ext_bank_account_id
AND iao.account_owner_party_id = acc.party_id
-- AND iao.created_by = foc.user_id
-- AND iao.last_updated_by = fou.user_id
AND iao.account_owner_party_id = vh.party_id
AND vh.vendor_id = vs.vendor_id
AND NVL (iao.end_date, SYSDATE + 9999)> SYSDATE
-- AND acc.created_by = fac.user_id
-- AND acc.last_updated_by = fau.user_id
AND NVL (acc.end_date, SYSDATE + 9999) > SYSDATE
AND acc.branch_id = hp.party_id (+)
AND 'ORGANIZATION' = hp.party_type (+)
AND 1 = 1) BAC
--
WHERE
vh.vendor_id = vs.vendor_id
AND vs.VENDOR_SITE_ID = sup.VENDOR_SITE_ID
-- AND awt.GROUP_ID(+) = vs.awt_group_id
-- AND NVL (vh.vendor_type_lookup_code, 'X') != 'EMPLOYEE'
AND vs.PRC_BU_ID = hou.organization_id --Join changed
-- AND vs.terms_id = apt.term_id(+)
-- and vh.terms_id = hdt.term_id(+)
AND sup.accts_pay_code_combination_id = gl1.code_combination_id(+)
AND sup.prepay_code_combination_id = gl2.code_combination_id(+)
AND sup.future_dated_payment_ccid = gl3.code_combination_id(+)
AND vs.default_pay_site_id = alt.vendor_site_id (+)
AND vs.PRC_BU_ID = iep.org_id(+)--Join changed
AND vs.vendor_site_id = iep.vendor_site_id(+)
AND iep.org_id = bac.org_id(+)
AND iep.vendor_site_id = bac.vendor_site_id(+)
AND iep.ipi_instrument_id = bac.ext_bank_account_id(+)
and hzp.party_id(+) = vh.party_id
and lcs.location_id(+) = sup.ship_to_location_id
and lcb.location_id(+) = sup.bill_to_location_id) AL1
WHERE 1=1
AND AL1.Payment_Allowed =nvl( :P_PAYMENT_ALLOWED,AL1.Payment_Allowed)
AND AL1.OU_Number = nvl(:P_OU_SHORT_CODE,AL1.OU_Number )
AND AL1.Pay_Method = nvl(:P_PAY_METHOD,AL1.Pay_Method )
--AND AL1.Pay_Group <> 'EMPLOYEES'
group by AL1.OU_Number,
AL1.Supplier_Name,
AL1.Supplier_Number,
AL1.Site_Code,
AL1.Address_City,
AL1.Pay_Group,
AL1.Pay_Method,
AL1.Site_Pay_Method,
AL1.Remit_Email
No comments:
Post a Comment