Friday 30 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,
               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