Suppliers Report (XML)DescriptionSuppliersReport

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

  • December 30, 2016 | 17 views