Oracle apps technical tutorial

SQL Query to find Vendor Master details in Oracle Apps

SQL Query to find Vendor Master details in Oracle Apps

Below query will help you to get details of Vendor and vendor related information in Oracle Apps.

In this query i am extracting following columns:

Vendor or Supplier Name, Number, PAN Number, Location, Currency, Vendor Type, Payment Method, Address, Contact details and email address, Is one time vendor, Payment Terms, Supplier Status, Pay-Group, and Bank and Account related information.

SELECT AV.segment1 Supplier_Number
, AV.vendor_name Supplier_Name
, jatvh.pan_no PAN_Number
, (SELECT location_code FROM hr_locations 
where location_id=NVL(assa.bill_to_location_id,
assa.ship_to_location_id)) LOCATION
, assa.payment_currency_code Currency

, av.vendor_type_lookup_code vendor_type

, ipm.payment_method_name pay_method
, ASSA.address_line1 Address_1
, ASSA.address_line2 Address_2
, ASSA.address_line3 Address_3

, ASSA.city
, ASSA.state
, ASSA.area_code
, ASSA.phone Contact_Number

,( CASE WHEN AV.one_time_flag='Y' THEN 'YES' ELSE 'NO' END) One_Time_Vendor
,( SELECT NAME
FROM apps.ap_terms APT
WHERE APT.term_id=ASSA.terms_id
AND NVL (enabled_flag, 'Y') = 'Y' 
AND NVL (end_date_active,sysdate + 1) >= TRUNC (sysdate)) Payment_Terms
, hou.name operating_unit
, assa.vendor_site_code Vendor_Site
, assa.pay_group_lookup_code Pay_Group
, ipm.payment_method_name pay_method
, DECODE(assa.inactive_date, NULL, 'Active', 'Inactive') Supplier_Status
, assa.PAY_SITE_FLAG
, assa.PURCHASING_SITE_FLAG
,NVL(
(SELECT DISTINCT 'Y'
FROM FND_DOCUMENT_ENTITIES fde,
FND_ATTACHED_DOCUMENTS fad,
FND_DOCUMENTS fd
WHERE fde.data_object_code = 'PO_VENDOR_SITES'
AND fde.table_name = 'PO_VENDOR_SITES'
AND fad.entity_name = 'PO_VENDOR_SITES'
AND fad.pk1_value = assa.vendor_site_id
AND fd.document_id = fad.document_id
), 'N') Attachment_flag
,(SELECT jatvh.section_code
FROM JAI_AP_TDS_VENDOR_HDRS jatvh
WHERE jatvh.vendor_id = av.vendor_id
AND jatvh.vendor_site_id = assa.vendor_site_id
AND jatvh.section_code IS NOT NULL
AND jatvh.vendor_site_id <> 0
) Default_tds_percent
, (NVL((SELECT DISTINCT hcp2.email_address
FROM hz_party_sites hps,
hz_contact_points hcp2
WHERE hcp2.owner_table_id(+) = hps.party_id
AND hps.party_id = AV.party_id
AND hcp2.CONTACT_POINT_TYPE(+) = 'EMAIL'
AND hcp2.status(+) = 'A'
AND hcp2.owner_table_name(+) = 'HZ_PARTIES')
,(SELECT DISTINCT hcp2.email_address
FROM hz_party_sites hps,
hz_contact_points hcp2
WHERE hcp2.owner_table_id(+) = hps.party_site_id
AND hps.party_site_id = assa.party_site_id
AND hcp2.CONTACT_POINT_TYPE(+) = 'EMAIL'
AND hcp2.status(+) = 'A'
AND hcp2.owner_table_name(+) = 'HZ_PARTY_SITES')
)) supplier_email
,(SELECT DISTINCT tds_vendor_type_lookup_code
FROM JAI_AP_TDS_VENDOR_HDRS
WHERE vendor_site_id = assa.vendor_site_id
AND vendor_id = av.vendor_id
) tds_vendor_type
,av.PURCHASING_HOLD_REASON po_hold_reason
,DECODE(av.HOLD_ALL_PAYMENTS_FLAG, 'Y', av.HOLD_REASON, DECODE(av.HOLD_UNMATCHED_INVOICES_FLAG, 'Y', 
av.HOLD_REASON, DECODE(av.HOLD_FUTURE_PAYMENTS_FLAG, 'Y', av.HOLD_REASON, NULL))) payment_hold_reason
, av.vendor_name_alt Alternate_Supplier_Name
, (SELECT jprl.registration_number
FROM apps.jai_party_regs jpr,
apps.jai_party_reg_lines jprl
WHERE jpr.party_type_code = 'THIRD_PARTY_SITE'
AND jpr.party_id = AV.vendor_id
AND jpr.party_site_id = ASSA.vendor_Site_id
AND jpr.party_reg_id = jprl.party_reg_id
AND jprl.registration_type_code = 'GSTIN'
AND TRUNC(sysdate) BETWEEN jprl.effective_from AND NVL(jprl.effective_to,SYSDATE+1)
AND ROWNUM<2
) AS VENDOR_GSTIN
,ASSA.state AS VENDOR_STATE
,jatvh.tan_no
,av.creation_date vendor_creation_date
, av.attribute8 reference1,
av.attribute9 reference2 ,
assa.creation_date site_creation_date,
TO_CHAR(Site_IEBO.bank_account_num_electronic) bank_account_num ,
site_IEBO.BANK_ACCOUNT_NAME bank_account_name ,
site_Cbb.bank_branch_name BANK_BRANCH_NAME ,
site_CBb.bank_name BANK_NAME ,
site_Cbb.branch_number BRANCH_NUMBER ,
Site_IEBO.check_digits CF_IFSC_CODE ,
TO_CHAR(SUPP_IEBO.bank_account_num_electronic) sup_bank_account_num ,
supp_IEBO.BANK_ACCOUNT_NAME sup_bank_account_name ,
supp_Cbb.bank_branch_name SUP_BANK_BRANCH_NAME ,
supp_CBb.bank_name SUP_BANK_NAME ,
supp_Cbb.branch_number SUP_BRANCH_NUMBER ,
SUPP_IEBO.check_digits SUP_IFSC_CODE,
gcc1.concatenated_segments laib_account,
gcc2.concatenated_segments prepay_account,
assa.creation_date,
(select papf.FULL_NAME from 
per_all_people_f papf,
fnd_user FU
where FU.user_id=assa.created_by
and papf.person_id=fu.employee_id
and trunc(sysdate) between trunc(papf.effective_start_date) and NVL(trunc(papf.effective_end_date),SYSDATE+1) ) Created_By,
assa.last_update_date,
(select papf.FULL_NAME from
per_all_people_f papf,
fnd_user FU
where fu.user_id=assa.last_updated_by
and papf.person_id=fu.employee_id
and trunc(sysdate) between trunc(papf.effective_start_date) and NVL(trunc(papf.effective_end_date),SYSDATE+1) ) updated_By
,av.END_DATE_ACTIVE
FROM ap.ap_suppliers av,
ap.ap_supplier_sites_all assa,
hr_operating_units hou,
gl_code_combinations_kfv gcc1,
gl_code_combinations_kfv gcc2,
iby_external_payees_all iepa,
iby_ext_party_pmt_mthds ieppm,
iby_payment_methods_tl ipm,
jai_ap_tds_vendor_hdrs JATVH ,
apps.IBY_EXTERNAL_payees_all SUPP_IEPA ,
apps.iby_pmt_instr_uses_all SUPP_IPIUA ,
apps.IBY_EXT_bank_accounts SUPP_IEBO ,
apps.ce_bank_branches_v SUPP_CBB ,
apps.IBY_EXTERNAL_payees_all Site_IEPA ,
apps.iby_pmt_instr_uses_all Site_IPIUA ,
apps.IBY_EXT_bank_accounts Site_IEBO ,
apps.ce_bank_branches_v Site_CBB
WHERE av.vendor_id = assa.vendor_id
AND assa.org_id = hou.organization_id
--AND av.vendor_id = 503673
AND assa.ACCTS_PAY_CODE_COMBINATION_ID = gcc1.code_combination_id
AND assa.PREPAY_CODE_COMBINATION_ID = gcc2.code_combination_id
AND assa.vendor_site_id = iepa.supplier_site_id
AND assa.party_site_id = iepa.party_site_id
AND iepa.ext_payee_id = ieppm.ext_pmt_party_id(+)
AND ((ieppm.inactive_date IS NULL)
OR (ieppm.inactive_date > SYSDATE))
AND ipm.PAYMENT_METHOD_CODE(+) = ieppm.payment_method_code
AND jatvh.vendor_id(+) = assa.vendor_id
AND jatvh.vendor_site_id(+) = assa.vendor_site_id
AND assa.pay_group_lookup_code='VENDOR'
AND NVL(ieppm.primary_flag, 'Y') = 'Y'
AND SUPP_IEPA.payee_party_id(+) = aV.PARTY_ID 
AND SUPP_IEPA.party_site_id is null
and supp_iepa.supplier_site_id is null
AND SUPP_IPIUA.ext_pmt_party_id(+) = SUPP_iepa.EXT_PAYEE_ID
and sysdate between SUPP_IPIUA.start_date(+) and nvl(SUPP_IPIUA.end_date(+),sysdate+1)
AND SUPP_IEBO.ext_bank_account_id(+) = SUPP_IPIUA.instrument_id
AND SUPP_CBB.branch_party_id(+) = SUPP_IEBO.branch_id
AND SUPP_CBB.bank_party_id(+) = SUPP_IEBO.bank_id 
AND Site_IEPA.payee_party_id(+) = aV.PARTY_ID 
AND Site_IEPA.party_site_id = assa.party_site_id
AND Site_IEPA.supplier_site_id = assa.vendor_site_id
AND Site_IPIUA.ext_pmt_party_id(+) = Site_iepa.EXT_PAYEE_ID
and sysdate between Site_IPIUA.start_date(+) and nvl(Site_IPIUA.end_date(+),sysdate+1)
AND Site_IEBO.ext_bank_account_id(+) = Site_IPIUA.instrument_id
AND Site_CBB.branch_party_id(+) = Site_IEBO.branch_id
AND Site_CBB.bank_party_id(+) = Site_IEBO.bank_id
-----------------------------------------------------------------------------------------

Leave a Reply