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


2 comments:

  1. You really did a great job. I found your blog very interesting and very informative. I think your blog is great information source & I like your way of writing and explaining the topics. Keep it up.For more details please visit our website...

    Oracle Fusion Training Institute

    ReplyDelete
  2. eToro is the ultimate forex trading platform for new and established traders.

    ReplyDelete