--//--++++++++++++++Supplier Site Email
Address++++++++++++++
SELECT HCP.EMAIL_ADDRESS
FROM HZ_PARTY_SITES HPS,
HZ_CONTACT_POINTS HCP,
AP_SUPPLIERS ASS
WHERE 1=1
AND hps.party_site_id = p_party_site_id
AND HCP.OWNER_TABLE_NAME='HZ_PARTY_SITES'
AND HPS.PARTY_ID=ASS.PARTY_ID
AND HCP.OWNER_TABLE_ID=HPS.PARTY_SITE_ID
AND HCP.CONTACT_POINT_TYPE='EMAIL';
---//+++++++++++Supplier Details+++++++++++++++++++
select asa.vendor_name,
asa.vendor_id,
asa.segment1 vendor_number,
asaa.vendor_site_code,
hps.party_site_name,
asaa.vendor_site_id,
hou.name ou_name,
asaa.org_id,
asa.party_id,
asaa.party_site_id
from ap_supplier_sites_all asaa
,ap_suppliers asa
,hr_operating_units hou
,hz_party_sites hps
where asa.vendor_id = asaa.vendor_id
and asa.segment1 = NVL(:p_segment1,asa.segment1)
and asa.vendor_name = NVL(:p_vendor_name,asa.vendor_name)
and asaa.vendor_site_code = NVL(:p_ven_site_code,asaa.vendor_site_code)
and asaa.org_id = hou.organization_id
and hps.party_site_id = asaa.party_site_id;
--//--++++++++++++++SUpplier
Contacts+++++++++++++++++++++++++
SELECT DISTINCT asu.party_id
, asu.segment1 Supp_Num
,asu.vendor_name
,hpc.party_name Contact_Name
,hpr.primary_phone_country_code
cnt_cntry
,hpr.primary_phone_area_code
cnt_area
,hpr.primary_phone_number
cnt_phone
,assa.vendor_site_code
,assa.vendor_site_id
,asco.vendor_contact_id
FROM
hz_relationships hr
,ap_suppliers asu
,ap_supplier_sites_all assa
,ap_supplier_contacts asco
,hz_org_contacts hoc
,hz_parties hpc
,hz_parties hpr
,hz_contact_points hpcp
WHERE hoc.party_relationship_id = hr.relationship_id
AND hr.subject_id = asu.party_id
AND hr.relationship_code = 'CONTACT'
AND hr.object_table_name = 'HZ_PARTIES'
AND asu.vendor_id = assa.vendor_id
AND hr.object_id = hpc.party_id
AND hr.party_id = hpr.party_id
AND asco.relationship_id = hoc.party_relationship_id
AND assa.party_site_id = asco.org_party_site_id
AND hpr.party_type='PARTY_RELATIONSHIP'
AND hpr.party_id = hpcp.owner_table_id
AND hpcp.owner_table_name = 'HZ_PARTIES'
--//--------Supplier Payment Method at Supplier
Level----------
SELECT
supp.segment1 supplier_num,
paym.payment_method_code
FROM
ap_suppliers supp,
iby_external_payees_all payee,
iby_ext_party_pmt_mthds paym
WHERE
supp.party_id = payee.payee_party_id
AND payee.ext_payee_id = paym.ext_pmt_party_id
AND supp.segment1 =:supplier_num
AND supplier_site_id IS NULL
AND paym.primary_flag = 'Y';
--//--------Supplier Payment Method at Site Level--------
SELECT
supp.segment1 supplier_num,
sites.vendor_site_code,
paym.payment_method_code
FROM
ap_suppliers supp,
ap_supplier_sites_all sites,
iby_external_payees_all payee,
iby_ext_party_pmt_mthds paym
WHERE
supp.party_id = payee.payee_party_id
AND payee.ext_payee_id = paym.ext_pmt_party_id
AND supp.segment1 =:supplier_num
AND supp.vendor_id = sites.vendor_id
AND sites.vendor_site_id = payee.supplier_site_id
AND paym.primary_flag = 'Y';
--//-----------Supplier Remit Site-------------
SELECT asa.vendor_site_code, asa1.vendor_site_code
FROM iby_ext_payee_relationships
iep,
ap_supplier_sites_all asa,
ap_supplier_sites_all asa1
WHERE iep.supplier_site_id = asa.vendor_site_id
AND asa1.vendor_site_id = iep.REMIT_SUPPLIER_SITE_ID
AND asa.vendor_site_code IN ('10230908E');
--//--+++++++++++++++++++Invoice Query +++++++++++++++
SELECT hou.name Operating_Unit_Name,
aps.vendor_name
Supplier_Name,
assa.vendor_site_code
Supplier_Site,
--aia.invoice_id,
--aia.vendor_id,
aia.vendor_site_id ,
aia.invoice_date,
aia.invoice_num
Invoice_Number,
aia.invoice_amount,
fct.name invoice_currency,
aia.gl_date,
at.name terms,
aia.payment_status_flag
payment,
aia.invoice_currency_code,
aia.payment_method_code,
DECODE(APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS(aia.invoice_id, aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code),
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'CANCELLED', 'Cancelled',
'Validated') INVOICE_STATUS,
decode(AP_INVOICES_PKG.GET_POSTING_STATUS(invoice_id),'S','Selected','P','Partial','N','Not Posted','Y','Posted') Accounted,
AP_INVOICES_PKG.GET_APPROVAL_STATUS(
aia.INVOICE_ID
,aia.INVOICE_AMOUNT
,aia.PAYMENT_STATUS_FLAG
,aia.INVOICE_TYPE_LOOKUP_CODE
) Approval_Status
FROM ap_invoices_all aia,
hr_operating_units hou,
ap_suppliers aps,
ap_supplier_sites_all assa,
fnd_currencies_tl fct,
ap_terms at
WHERE hou.organization_id = aia.org_id
AND aps.vendor_id = assa.vendor_id
AND aps.vendor_id = aia.vendor_id
AND at.term_id = aia.terms_id
AND assa.vendor_site_id = aia.vendor_site_id
AND fct.currency_code = aia.invoice_currency_code
AND fct.language = USERENV ('LANG')
AND invoice_num = NVL (:p_invoice_num, invoice_num)
AND payment_method_code = NVL (:p_payment_method, payment_method_code)
AND hou.name = NVL (:p_ou_name, hou.name)
AND aia.invoice_date BETWEEN NVL (:p_inv_from_date, invoice_date)
AND NVL (:p_inv_to_date, invoice_date)
AND aia.gl_date BETWEEN NVL (:p_gl_from_date, gl_date)
AND NVL (:p_gl_to_date, gl_date)
AND AP_INVOICES_PKG.GET_APPROVAL_STATUS (aia.INVOICE_ID,
aia.INVOICE_AMOUNT,
aia.PAYMENT_STATUS_FLAG,
aia.INVOICE_TYPE_LOOKUP_CODE) =
NVL (:p_approval_status,
AP_INVOICES_PKG.GET_APPROVAL_STATUS (
aia.INVOICE_ID,
aia.INVOICE_AMOUNT,
aia.PAYMENT_STATUS_FLAG,
aia.INVOICE_TYPE_LOOKUP_CODE))
AND AP_INVOICES_PKG.GET_APPROVAL_STATUS (aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code) =
NVL (
DECODE (:p_invoice_status,
'Never Validated', 'NEVER APPROVED',
'Needs Revalidation', 'NEEDS REAPPROVAL',
'Cancelled', 'CANCELLED',
'Validated', 'APPROVED'),
AP_INVOICES_PKG.GET_APPROVAL_STATUS (
aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code))
AND AP_INVOICES_PKG.GET_POSTING_STATUS (invoice_id) =
NVL (
DECODE (:p_accounted,
'Selected', 'S',
'Partial', 'P',
'Not Posted', 'N',
'Posted', 'P',
'Yes', 'P',
'No', 'N'),
AP_INVOICES_PKG.GET_POSTING_STATUS (invoice_id))
--//+++++++++++++++++Supplier Banks and
Branches+++++++++++++++++++++++
SELECT *
FROM (SELECT
PaymentInstrumentUsesEO.INSTRUMENT_PAYMENT_USE_ID,
PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID,
PaymentInstrumentUsesEO.INSTRUMENT_TYPE,
PaymentInstrumentUsesEO.INSTRUMENT_ID,
PaymentInstrumentUsesEO.PAYMENT_FUNCTION,
PaymentInstrumentUsesEO.ORDER_OF_PREFERENCE,
PaymentInstrumentUsesEO.DEBIT_AUTH_FLAG,
PaymentInstrumentUsesEO.DEBIT_AUTH_METHOD,
PaymentInstrumentUsesEO.DEBIT_AUTH_REFERENCE,
PaymentInstrumentUsesEO.DEBIT_AUTH_BEGIN,
PaymentInstrumentUsesEO.DEBIT_AUTH_END,
PaymentInstrumentUsesEO.START_DATE,
PaymentInstrumentUsesEO.END_DATE,
ibyextbankaccts.bank_account_id,
ibyextbankaccts.bank_account_name,
ibyextbankaccts.bank_account_number,
ibyextbankaccts.currency_code,
ibyextbankaccts.bank_name,
ibyextbankaccts.bank_number,
ibyextbankaccts.bank_branch_name,
ibyextbankaccts.bank_account_type,
ibyextbankaccts.branch_number,
ibyextbankaccts.iban_number,
ibyextbankaccts.eft_swift_code,
'N' detailView,
PaymentInstrumentUsesEO.PAYMENT_FLOW,
supplier.vendor_name
supplier_name,
supplier.vendor_name_alt alt_supplier_name,
supplier.segment1
supplier_number,
NULL vendor_site_id, -- CUSTOM
NULL site_name,
NULL alt_site_name,
NULL business_classification, -- CUSTOM
address.party_site_name,
NULL organization_name,
DECODE(Payee.supplier_site_id, NULL, DECODE(Payee.org_id, NULL, DECODE(Payee.party_site_id, NULL, 'Supplier', 'Address'), 'Address-Operating unit'), 'Site') assignment_level,
ibyextbankaccts.ext_bank_account_id ext_bank_account_id,
ibyextbankaccts.bank_party_id bank_party_id,
ibyextbankaccts.branch_party_id
branch_party_id,
ibyextbankaccts.FOREIGN_PAYMENT_USE_FLAG FOREIGN_PAYMENT_USE_FLAG,
DECODE(ibyextbankaccts.PRIMARY_ACCT_OWNER_PARTY_ID, Payee.payee_party_id, 'Yes', 'No') primary_flag,
supplier.vendor_id vendor_id,
Payee.payee_party_id
FROM IBY_PMT_INSTR_USES_ALL
PaymentInstrumentUsesEO,
IBY_EXTERNAL_BANK_ACCOUNTS_V ibyextbankaccts,
IBY_EXTERNAL_PAYEES_ALL Payee,
ap_suppliers supplier,
hz_party_sites
address
WHERE
PaymentInstrumentUsesEO.instrument_id = ibyextbankaccts.bank_account_id
AND
PaymentInstrumentUsesEO.instrument_type = 'BANKACCOUNT'
AND
PaymentInstrumentUsesEO.payment_function = 'PAYABLES_DISB'
AND
PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID = Payee.ext_payee_id
AND Payee.payee_party_id = supplier.party_id
AND Payee.party_site_id = address.party_site_id(+)
AND Payee.org_id IS NULL
AND Payee.org_type IS NULL
UNION
SELECT
PaymentInstrumentUsesEO.INSTRUMENT_PAYMENT_USE_ID,
PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID,
PaymentInstrumentUsesEO.INSTRUMENT_TYPE,
PaymentInstrumentUsesEO.INSTRUMENT_ID,
PaymentInstrumentUsesEO.PAYMENT_FUNCTION,
PaymentInstrumentUsesEO.ORDER_OF_PREFERENCE,
PaymentInstrumentUsesEO.DEBIT_AUTH_FLAG,
PaymentInstrumentUsesEO.DEBIT_AUTH_METHOD,
PaymentInstrumentUsesEO.DEBIT_AUTH_REFERENCE,
PaymentInstrumentUsesEO.DEBIT_AUTH_BEGIN,
PaymentInstrumentUsesEO.DEBIT_AUTH_END,
PaymentInstrumentUsesEO.START_DATE,
PaymentInstrumentUsesEO.END_DATE,
ibyextbankaccts.bank_account_id,
ibyextbankaccts.bank_account_name,
ibyextbankaccts.bank_account_number,
ibyextbankaccts.currency_code,
ibyextbankaccts.bank_name,
ibyextbankaccts.bank_number,
ibyextbankaccts.bank_branch_name,
ibyextbankaccts.bank_account_type,
ibyextbankaccts.branch_number,
ibyextbankaccts.iban_number,
ibyextbankaccts.eft_swift_code,
'N' detailView,
PaymentInstrumentUsesEO.PAYMENT_FLOW,
supplier.vendor_name supplier_name,
supplier.vendor_name_alt alt_supplier_name,
supplier.segment1
supplier_number,
site.vendor_site_id, -- CUSTOM
site.vendor_site_code site_name,
site.vendor_site_code_alt alt_site_name,
site.attribute_category business_classification, -- CUSTOM
address.party_site_name,
ou.NAME organization_name,
DECODE(Payee.supplier_site_id, NULL, DECODE(Payee.org_id, NULL, DECODE(Payee.party_site_id, NULL, 'Supplier', 'Address'), 'Address-Operating unit'), 'Site') assignment_level,
ibyextbankaccts.ext_bank_account_id
ext_bank_account_id,
ibyextbankaccts.bank_party_id bank_party_id,
ibyextbankaccts.branch_party_id branch_party_id,
ibyextbankaccts.FOREIGN_PAYMENT_USE_FLAG FOREIGN_PAYMENT_USE_FLAG,
DECODE(ibyextbankaccts.PRIMARY_ACCT_OWNER_PARTY_ID, Payee.payee_party_id, 'Yes', 'No') primary_flag,
supplier.vendor_id vendor_id,
Payee.payee_party_id
FROM iby_pmt_instr_uses_all
PaymentInstrumentUsesEO,
iby_external_bank_accounts_v ibyextbankaccts,
iby_external_payees_all Payee,
ap_suppliers
supplier,
ap_supplier_sites_all site,
hz_party_sites address,
hr_operating_units ou
WHERE
PaymentInstrumentUsesEO.instrument_id = ibyextbankaccts.bank_account_id
AND
PaymentInstrumentUsesEO.instrument_type = 'BANKACCOUNT'
AND PaymentInstrumentUsesEO.payment_function
= 'PAYABLES_DISB'
AND
PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID = Payee.ext_payee_id
AND Payee.payee_party_id = supplier.party_id
AND Payee.supplier_site_id = site.vendor_site_id(+)
AND Payee.party_site_id = address.party_site_id(+)
AND Payee.org_id = ou.organization_id(+)
AND Payee.org_id IS NOT NULL
AND Payee.org_type IS NOT NULL
/*AND (Payee.org_id,
Payee.org_type) IN (SELECT uo.organization_id,
uo.organization_type
FROM
ce_security_profiles_v uo)*/
) QRSLT
WHERE (--VENDOR_SITE_ID = 4944131--1
--EXT_BANK_ACCOUNT_ID = 183110
SUPPLIER_NUMBER LIKE '661060'--2
--AND SUPPLIER_NAME LIKE 'Nadia Khaleghi (REFUND ONLY)%'
--VENDOR_SITE_ID = 1288119
--AND (END_DATE IS NULL OR END_DATE > SYSDATE)
)
--ORDER BY BANK_ACCOUNT_NUMBER
-//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
No comments:
Post a Comment