ORACLE MASTERMINDS

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,

               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;

   By

   Deepak J