PO Undefinitized Contract Actions

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Undefinitized Contract Actions Report
Short Name: POUCA
DB package: PO_UCA
SELECT PO_HEADER_ID phi,
                      CLM_DOCUMENT_NUMBER,
                      RTrim(replace(replace(replace(replace((address_line_1||','|| ADDRESS_LINE_2 ||',' ||TOWN_OR_CITY ||','||REGION_2||','||POSTAL_CODE||','||COUNTRY),',,,,,',','),',,,,',','),',,,',','),',,',','),',') ISSUING_OFFICE,
                      HRE.FULL_NAME BUYER_NAME,
                      PV.VENDOR_NAME,
                      CURRENCY_CODE CURRENCY,
                      APPROVED_DATE,
                      PO_UCA.get_header_amount_ordered_uca(PHA.PO_HEADER_ID,-1,PHA.AUTHORIZATION_STATUS) AWARD_TOTAL          
                 FROM PO_HEADERS_ALL PHA, 
                      HR_LOCATIONS_ALL HRL,
                      HR_EMPLOYEES HRE,
                      PO_VENDORS PV
                WHERE PHA.ORG_ID = :operatingUnit
                  AND PHA.AUTHORIZATION_STATUS = 'APPROVED'
                  AND (:docNum IS NULL OR :docNum = PHA.CLM_DOCUMENT_NUMBER)
                  AND (:issuingOffice IS NULL OR :issuingOffice = PHA.CLM_ISSUING_OFFICE)
                  AND (:buyer IS NULL OR :buyer = PHA.AGENT_ID)
                  AND (:supplier IS NULL OR :supplier = PHA.VENDOR_ID)
                  AND (:dateFrom IS NULL OR :dateFrom <= PHA.APPROVED_DATE)
                  AND (:dateTo IS NULL OR :dateTo >= PHA.APPROVED_DATE)
                  AND EXISTS (SELECT 1
                                FROM po_line_ucas PLC
                               WHERE PLC.PO_HEADER_ID = PHA.PO_HEADER_ID
                                 AND ((NVL(:defType,'UNDEFINITIZED') = 'UNDEFINITIZED' AND undef_approved_date IS NOT NULL AND def_approved_date IS NULL)
                                      OR
                                      (NVL(:defType,'DEFINITIZED') = 'DEFINITIZED' AND def_approved_date IS NOT NULL)
                                     )
                             )
                  AND HRL.LOCATION_ID (+) = PHA.CLM_ISSUING_OFFICE
                  AND HRE.EMPLOYEE_ID (+) = PHA.AGENT_ID
                  AND PV.VENDOR_ID (+) = PHA.VENDOR_ID
Parameter Name SQL text Validation
Approval Date To
 
Date
Approval Date From
 
Date
Supplier
 
LOV Oracle
Defnitization Indicator
 
LOV Oracle
Buyer
 
LOV Oracle
Document Number
 
LOV Oracle
Issuing Office
 
LOV Oracle
operatingUnit
 
LOV Oracle