PO New Vendor Letter

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: New Vendor Letter Report (XML)
Short Name: POXVDRVL_XML
DB package: PO_POXVDRVL_XMLP_PKG
SELECT   pov.vendor_name        Vendor
,        pvs.vendor_site_code   Vendor_site
,        pvs.address_line1      Address
,        pvs.address_line2
,        pvs.address_line3
,        pvs.city
,        pvs.state
,        pvs.country
,        pvs.zip
FROM     po_vendor_sites        pvs
,        po_vendors             pov
WHERE    pov.vendor_id          = pvs.vendor_id
AND      pov.vendor_name        >= nvl(:P_vendor_from,pov.vendor_name)
AND      pov.vendor_name        <= nvl(:P_vendor_to,pov.vendor_name)
AND      pov.creation_date      BETWEEN nvl(:P_create_date_from, 
                                        pov.creation_date - 1) 
                                AND     nvl(:P_create_date_to,
                                        pov.creation_date + 1)
AND      nvl(pvs.purchasing_site_flag,'N')      = 
             decode(:P_site, 'PURCHASING', 'Y', 
                                'BOTH', 'Y',
                                 nvl(pvs.purchasing_site_flag, 'N'))
AND      nvl(pvs.pay_site_flag, 'N')            = 
             decode(:P_site, 'PAY','Y',
                                 'BOTH', 'Y',
                                 nvl(pvs.pay_site_flag, 'N'))
AND      nvl(pov.vendor_type_lookup_code, '-1') = 
         nvl(:P_vendor_type,nvl(pov.vendor_type_lookup_code,'-1'))
AND      (  (:P_active_inactive = 'ACTIVE' 
              AND      sysdate BETWEEN nvl(pov.start_date_active, sysdate-1) 
                               AND      nvl(pov.end_date_active, sysdate+1))
          OR       
            (:P_active_inactive = 'INACTIVE'
              AND     ( sysdate  <= nvl(pov.start_date_active, sysdate-1)
                     OR sysdate  >= nvl(pov.end_date_active, sysdate+1)))
          OR       
         nvl(:P_active_inactive,'BOTH') = 'BOTH')
ORDER BY &orderby_clause
Parameter Name SQL text Validation
Sort By
 
LOV Oracle
Active/Inactive
 
LOV Oracle
To
 
Date
Creation Dates From
 
Date
Vendor Site
 
LOV Oracle
To
 
LOV Oracle
Vendors From
 
LOV Oracle
Vendor Type
 
LOV Oracle
Title
 
Ask a question