PO Cancelled Requisition

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Cancelled Requisition Report (XML)
Short Name: POXRQCRQ_XML
DB package: PO_POXRQCRQ_XMLP_PKG
SELECT  distinct prh.segment1                   ReqNumber
,       T.type_name                          Requisition_Type
,       papf2.full_name                          CancelledBy
,       to_char(pah.action_date,'DD-MON-YY')                         CancelDate
,       pah.note                                CancelReason
,       papf.full_name                           Preparer
,       trunc(prh.creation_date)     CreationDate
,       prh.requisition_header_id
,       decode(psp1.manual_req_num_type, 'NUMERIC'
                    ,null, prh.segment1)
,       decode(psp1.manual_req_num_type, 'NUMERIC'
               ,to_number(prh.segment1), null)
FROM    po_requisition_headers                  prh
,       PER_ALL_PEOPLE_F  PAPF
,       PER_ALL_PEOPLE_F  PAPF2
,       po_action_history                       pah
,       PO_DOCUMENT_TYPES_ALL_TL T
,       PO_DOCUMENT_TYPES_ALL_B B
,       po_system_parameters                    psp1
WHERE   papf.full_name BETWEEN nvl(:P_preparer_from,papf.full_name)
                      AND nvl(:P_preparer_to,papf.full_name)
AND     trunc(prh.creation_date) BETWEEN nvl(:P_creation_date_from, trunc(prh.creation_date)-1)
                      AND nvl(:P_creation_date_to
				      ,trunc(prh.creation_date)+1)
AND     papf.person_id                 = prh.preparer_id
AND     pah.object_id                   = prh.requisition_header_id
AND     pah.object_type_code            = 'REQUISITION'
AND     pah.action_code                 = 'CANCEL'
AND     prh.type_lookup_code            = B.document_subtype
AND     B.document_type_code         = 'REQUISITION'
AND     NVL(PRH.contractor_requisition_flag, 'N') <> 'Y'           
AND     papf2.person_id                = pah.employee_id
AND     EXISTS (SELECT 'Requestor exists'
                FROM po_requisition_lines porl,
                   PER_ALL_PEOPLE_F  PAPF3
                WHERE porl.requisition_header_id =
                       prh.requisition_header_id
                AND   porl.to_person_id = papf3.person_id
                AND   papf3.full_name BETWEEN  nvl(:P_REQUESTOR_FROM, papf3.full_name) 
                                         and nvl(:P_REQUESTOR_TO,papf3.full_name)
                and PAPF.BUSINESS_GROUP_ID = (SELECT NVL(MAX(FSP.BUSINESS_GROUP_ID),0)
                                                                              FROM FINANCIALS_SYSTEM_PARAMETERS FSP)
                AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND
                                                                           PAPF.EFFECTIVE_END_DATE
               AND DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE',
                        HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F',PAPF.PERSON_ID,
                                                                                  PAPF.PERSON_TYPE_ID,
                        PAPF.EMPLOYEE_NUMBER,PAPF.APPLICANT_NUMBER 
                        ,NPW_NUMBER )) = 'TRUE'      
               AND DECODE(HR_GENERAL.GET_XBG_PROFILE,'Y', PAPF.BUSINESS_GROUP_ID ,
                                       HR_GENERAL.GET_BUSINESS_GROUP_ID) = PAPF.BUSINESS_GROUP_ID)
and PAPF.BUSINESS_GROUP_ID = (SELECT NVL(MAX(FSP.BUSINESS_GROUP_ID),0)
FROM FINANCIALS_SYSTEM_PARAMETERS FSP)
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND
PAPF.EFFECTIVE_END_DATE
AND DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE',
HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F',PAPF.PERSON_ID,
PAPF.PERSON_TYPE_ID,
PAPF.EMPLOYEE_NUMBER,PAPF.APPLICANT_NUMBER 
,PAPF.NPW_NUMBER )) = 'TRUE'       
AND DECODE(HR_GENERAL.GET_XBG_PROFILE,'Y', PAPF.BUSINESS_GROUP_ID ,
HR_GENERAL.GET_BUSINESS_GROUP_ID) = PAPF.BUSINESS_GROUP_ID
and PAPF2.BUSINESS_GROUP_ID = (SELECT NVL(MAX(FSP.BUSINESS_GROUP_ID),0)
FROM FINANCIALS_SYSTEM_PARAMETERS FSP)
AND TRUNC(SYSDATE) BETWEEN PAPF2.EFFECTIVE_START_DATE AND
PAPF2.EFFECTIVE_END_DATE
AND DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE',
HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F',PAPF2.PERSON_ID,
PAPF2.PERSON_TYPE_ID,
PAPF2.EMPLOYEE_NUMBER,PAPF2.APPLICANT_NUMBER 
,PAPF2.NPW_NUMBER )) = 'TRUE'                    
AND DECODE(HR_GENERAL.GET_XBG_PROFILE,'Y', PAPF2.BUSINESS_GROUP_ID ,
HR_GENERAL.GET_BUSINESS_GROUP_ID) = PAPF2.BUSINESS_GROUP_ID
and B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE
AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE
AND B.ORG_ID = T.ORG_ID 
AND B.ORG_ID = prh.ORG_ID 
AND T.LANGUAGE = USERENV('LANG')
ORDER BY &orderby_clause
Parameter Name SQL text Validation
Dynamic Precision Option
 
LOV Oracle
Sort By
 
LOV Oracle
To
 
LOV Oracle
Requesters From
 
LOV Oracle
To
 
Date
Requisition Creation Dates From
 
Date
To
 
LOV Oracle
Preparers From
 
LOV Oracle
Title