Introduction
This Post described Suppliers Report (XML) is used to review the supplier, supplier site, and supplier contact details.
1.Query Fetching Vendor Details:
SELECT p.vendor_id c_vendor_id, p.segment1 c_vendor_number,
p.vendor_name c_vendor_name,
DECODE (UPPER (:p_order_by_par),
‘VENDOR NAME’, DECODE (:sort_by_alternate,
‘Y’, UPPER (p.vendor_name_alt),
UPPER (p.vendor_name)
),
‘NO SORT’
) c_sort_vendor_name,
DECODE (UPPER (:p_order_by_par),
‘CREATED BY’, UPPER (fu1.user_name),
‘NO SORT’
) c_sort_created_by,
DECODE (UPPER (:p_order_by_par),
‘LAST UPDATED BY’, UPPER (fu2.user_name),
‘NO SORT’
) c_sort_updated_by,
plc.displayed_field c_vendor_type, p.customer_num c_customer_number,
p.num_1099 c_taxpayer_id,
p.vat_registration_num c_tax_registration_num,
p.type_1099 c_income_tax_type,
DECODE (p.hold_flag, ‘Y’, :c_nls_yes, :c_nls_no) c_on_po_hold,
DECODE (SIGN (SYSDATE – NVL (p.start_date_active, SYSDATE – 1)),
-1, :c_nls_inactive,
DECODE (SIGN (NVL (p.end_date_active, SYSDATE + 1) – SYSDATE),
-1, :c_nls_inactive,
DECODE (p.enabled_flag,
‘N’, :c_nls_inactive,
:c_nls_active
)
)
) c_vendor_status,
DECODE (p.one_time_flag, ‘Y’, :c_nls_yes, :c_nls_no) c_one_time_vendor,
DECODE (p.federal_reportable_flag,
‘Y’, :c_nls_yes,
:c_nls_no
) c_fed_vendor,
DECODE (p.state_reportable_flag,
‘Y’, :c_nls_yes,
:c_nls_no
) c_state_vendor,
p.creation_date c_creation_date_vendor,
p.created_by c_created_by_v_num, fu1.user_name c_created_by_vendor,
p.last_update_date c_update_date_vendor,
p.last_updated_by c_updated_by_v_num,
fu2.user_name c_updated_by_vendor
FROM po_vendors p, po_lookup_codes plc, fnd_user fu1, fnd_user fu2
WHERE p.vendor_type_lookup_code = plc.lookup_code(+)
AND plc.lookup_type(+) = ‘VENDOR TYPE’
AND p.created_by = fu1.user_id(+)
AND p.last_updated_by = fu2.user_id(+)
/* Dynamic SQL used because of index on vendor_id */
AND ( :p_vendor_type_par IS NULL
OR p.vendor_type_lookup_code = :p_vendor_type_par
)
AND ( :p_income_tax_rep_par IS NULL
OR p.federal_reportable_flag = :p_income_tax_rep_par
)
AND NVL (p.start_date_active, SYSDATE – 1) <=
DECODE (:p_vendor_status_par,
‘Active’, SYSDATE,
NVL (p.start_date_active, SYSDATE)
)
AND NVL (p.end_date_active, SYSDATE + 1) >=
DECODE (:p_vendor_status_par,
‘Active’, SYSDATE,
NVL (p.end_date_active, SYSDATE)
)
2. Query Fetching Vendor Site Details
SELECT ps.vendor_site_id c_vendor_site_id, ps.vendor_id c_vendor_id_s,
ps.vendor_site_code c_site_code,
DECODE (UPPER (:p_order_by_par),
‘CREATED BY’, UPPER (fu3.user_name),
‘NO SORT’
) c_sort_created_by_site,
DECODE (UPPER (:p_order_by_par),
‘LAST UPDATED BY’, UPPER (fu4.user_name),
‘NO SORT’
) c_sort_updated_by_site,
DECODE (:sort_by_alternate,
‘Y’, UPPER (ps.vendor_site_code_alt),
UPPER (ps.vendor_site_code)
) c_sort_site_code,
ps.address_line1 c_address1, ps.address_line2 c_address2,
ps.address_line3 address3, ps.creation_date c_creation_date_site,
fu3.user_name c_created_by_site,
ps.last_update_date c_update_date_site,
fu4.user_name c_updated_by_site,
DECODE (ps.city, NULL, NULL, ps.city) c_city,
SUBSTR ( DECODE (ps.state,
NULL, ps.province || ‘ ‘,
ps.state || ‘ ‘
)
|| DECODE (ps.zip, NULL, NULL, ps.zip || ‘ ‘)
|| ps.country,
1,
35
) c_state_zip_country,
NVL (ps.state, ps.province) c_state, ps.zip c_zip,
ps.country c_country_code, ft.territory_short_name c_country_name,
SUBSTR ( DECODE (ps.area_code, NULL, NULL, ps.area_code || ‘ ‘)
|| ps.phone,
1,
20
) c_site_telephone,
SUBSTR ( DECODE (ps.fax_area_code,
NULL, NULL,
ps.fax_area_code || ‘ ‘
)
|| ps.fax,
1,
19
) c_site_fax,
t.NAME c_payment_terms, alc.displayed_field c_payment_method,
ps.pay_group_lookup_code c_pay_grop_code,
ps.payment_priority c_payment_priority,
DECODE (ps.pay_site_flag,
‘Y’, :c_pay_site || CHR (10),
NULL
)
|| DECODE (ps.rfq_only_site_flag, ‘Y’, :c_rfq_only || CHR (10), NULL)
|| DECODE (ps.purchasing_site_flag,
‘Y’, :c_purchasing_site || CHR (10),
NULL
)
|| DECODE (ps.pcard_site_flag,
‘Y’, :c_procurement_card || CHR (10),
NULL
) c_site_uses,
DECODE (ps.exclusive_payment_flag,
‘Y’, :c_nls_yes,
:c_nls_no
) c_pay_alone,
DECODE (ps.hold_unmatched_invoices_flag,
‘Y’, :c_nls_yes,
:c_nls_no
) c_matching_required,
DECODE (ps.hold_future_payments_flag,
‘Y’, :c_nls_yes,
:c_nls_no
) c_hold_future_pay,
DECODE (ps.hold_all_payments_flag,
‘Y’, :c_nls_yes,
:c_nls_no
) c_hold_all_pay,
DECODE (SIGN (NVL (ps.inactive_date, SYSDATE + 1) – SYSDATE),
-1, :c_nls_inactive,
:c_nls_active
) c_site_status,
UPPER (pc.last_name)
|| UPPER (pc.first_name)
|| UPPER (pc.middle_name) c_sort_contact_name,
SUBSTR ( DECODE (pc.first_name, NULL, NULL, pc.first_name || ‘ ‘)
|| DECODE (pc.middle_name, NULL, NULL, pc.middle_name || ‘ ‘)
|| pc.last_name,
1,
20
) c_contact_name,
pc.title c_position,
SUBSTR ( DECODE (pc.area_code,
NULL, NULL,
‘(‘ || pc.area_code || ‘) ‘
)
|| pc.phone,
1,
20
) c_contact_telephone,
DECODE (SIGN (NVL (pc.inactive_date, SYSDATE + 1) – SYSDATE),
-1, :c_nls_inactive,
:c_nls_active
) c_contact_status,
att.tolerance_name c_tolerance_name, ps.email_address c_email_address,
ps.attribute1 ap_owner
FROM po_vendor_sites ps,
ap_terms t,
po_vendor_contacts pc,
ap_lookup_codes alc,
fnd_user fu3,
fnd_user fu4,
fnd_territories_vl ft,
ap_tolerance_templates att
WHERE ps.vendor_site_id = pc.vendor_site_id(+)
AND ps.terms_id = t.term_id(+)
AND ps.payment_method_lookup_code = alc.lookup_code(+)
AND alc.lookup_type(+) = ‘PAYMENT METHOD’
AND ps.created_by = fu3.user_id(+)
AND ps.last_updated_by = fu4.user_id(+)
AND (:p_pay_group_par IS NULL
OR :p_pay_group_par = ps.pay_group_lookup_code
)
AND NVL (ps.inactive_date, SYSDATE + 1) >=
DECODE (:p_site_status_par,
‘Active’, SYSDATE,
NVL (ps.inactive_date, SYSDATE)
)
AND NVL (pc.inactive_date, SYSDATE + 1) >=
DECODE (:p_contact_status_par,
‘Active’, SYSDATE,
NVL (pc.inactive_date, SYSDATE)
)
AND ps.country = ft.territory_code(+)
AND ps.tolerance_id = att.tolerance_id(+)
AND ( NVL (ps.attribute1, ‘X’) = NVL (:p_owner, ‘X’)
OR ps.attribute1 = NVL (:p_owner, ps.attribute1)
)
3. Query Fetching Vendor Bank Details:
SELECT aba.bank_account_name c_bank_account_name,
aba.bank_account_num c_bank_account_number,
DECODE (abau.primary_flag, ‘Y’, :c_nls_yes, :c_nls_no) c_primary_flag,
aba.currency_code c_currency_bank_acc, abau.start_date c_start_date,
abau.end_date c_end_date, abau.vendor_site_id c_ba_vendor_site
FROM ap_bank_account_uses abau, po_vendor_sites pvs, ap_bank_accounts aba
WHERE ( NVL (abau.start_date, SYSDATE – 1) <=
DECODE (:p_bank_account_status_par,
‘Active’, SYSDATE,
NVL (abau.start_date, SYSDATE)
)
AND NVL (abau.end_date, SYSDATE + 1) >=
DECODE (:p_bank_account_status_par,
‘Active’, SYSDATE,
NVL (abau.end_date, SYSDATE)
)
)
AND abau.external_bank_account_id = aba.bank_account_id
AND abau.vendor_site_id = pvs.vendor_site_id
AND abau.vendor_id = pvs.vendor_id;
Summary
This script helps us to comprehend how Suppliers Report (XML) is used to review the supplier, supplier site, and supplier contact details Couple of tables which is being
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
