ORACLE MASTERMINDS: December 2016

Suppliers Report (XML)

Description

Suppliers Report (XML) is used to review the supplier, supplier site and supplier contacts 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,

) 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,

) c_site_telephone,

SUBSTR ( DECODE (ps.fax_area_code,

NULL, NULL,

ps.fax_area_code || ' '

)

|| ps.fax,

) 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,

) c_contact_name,

pc.title c_position,

SUBSTR ( DECODE (pc.area_code,

NULL, NULL,

'(' || pc.area_code || ') '

)

|| pc.phone,

) 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;

Deepak J

Friday, 30 December 2016