AP Suppliers - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Suppliers Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXVDVSR_XML
DB package: AP_APXVDVSR_XMLP_PKG
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,
       ps.pay_group_lookup_code               c_pay_grop_code,
       ps.payment_priority                    c_payment_priority,
       DECODE(ps.pay_site_flag, 'Y', 'Pay'||CHR(10), null)||
         DECODE(ps.rfq_only_site_flag, 'Y', 'RFQ Only'||CHR(10), null)||
         DECODE(ps.purchasing_site_flag, 'Y', 'Purchasing'||CHR(10), null)||
         DECODE(ps.pcard_site_flag, 'Y', 'Procurement Card'||CHR(10), null)   C_SITE_USES,
       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,
              ps.payment_currency_code c_payment_currency_code,
              ps.org_id c_org_id,
              ps.party_site_id c_party_site_id,
              pv.party_id c_party_id, 
	AP_APXVDVSR_XMLP_PKG.c_address_concatenatedformula(ps.address_line1, ps.address_line2, ps.address_line3, DECODE ( ps.city , null , null , ps.city ), NVL ( ps.state , ps.province ), ps.zip, ft.territory_short_name, ps.country) C_address_concatenated, 
	AP_APXVDVSR_XMLP_PKG.c_iby_infoformula(ps.vendor_site_id, ps.org_id, pv.party_id, ps.party_site_id, ps.payment_currency_code) C_iby_info,
	AP_APXVDVSR_XMLP_PKG.C_PAY_ALONE_p C_PAY_ALONE,
	AP_APXVDVSR_XMLP_PKG.C_PAYMENT_METHOD_p C_PAYMENT_METHOD
FROM   po_vendor_sites ps,
       po_vendors pv,
       ap_terms t,
       po_vendor_contacts pc,
       fnd_user fu3,
       fnd_user fu4,
       fnd_territories_vl ft
WHERE ps.vendor_site_id = pc.vendor_site_id (+) 
  AND pv.vendor_id = ps.vendor_id
  AND ps.terms_id = t.term_id (+)  
  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 (+)
&LP_S_CREATION_DATE_FROM
&LP_S_CREATION_DATE_TO
&LP_S_UPDATE_DATE_FROM
&LP_S_UPDATE_DATE_TO
&LP_S_CREATED_BY
&LP_S_UPDATED_BY 
 and ps.vendor_id=:C_VENDOR_ID
Parameter Name SQL text Validation
AP_SRS_240_CHARS_APXVDVSR_ACCOUNT
 
AP_SRS_240_CHARS_APXVDVSR_CONT
 
AP_SRS_240_CHARS
 
Ledger
 
Include All/Active Accounts
 
LOV Oracle
Include Bank Account Information
 
LOV Oracle
Include All/Active Contacts
 
LOV Oracle
Include Contact Information
 
LOV Oracle
Site Pay Group
 
LOV Oracle
Include Home Country
 
LOV Oracle
Include All/Active Sites
 
LOV Oracle
Include Site Information
 
LOV Oracle
Suppliers This Org Only
 
LOV Oracle
Include All/Active Suppliers
 
LOV Oracle
Updated By
 
LOV Oracle
Created By
 
LOV Oracle
To Update Date
 
Date
From Update Date
 
Date
To Creation Date
 
Date
From Creation Date
 
Date
Income Tax Reportable Only
 
LOV Oracle
Order By
 
LOV Oracle
Supplier Type
 
LOV Oracle
Supplier Name
 
LOV Oracle
Ask a question