PO Cancelled Purchase Orders

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Cancelled Purchase Orders Report (XML)
Short Name: POXPOCAN_XML
DB package: PO_POXPOCAN_XMLP_PKG
SELECT poh.segment1                     PO_Number_Release
,      pov.vendor_name                     Vendor
,      pvs.vendor_site_code               Vendor_Site
,      poh.creation_date                     Creation_Date
,      poh.currency_code                    C_CURRENCY
,      sum(nvl(pll.quantity,0) *
           nvl(pll.price_override,0))       C_AMOUNT
,      papf2.full_name                         Buyer
,      pah.action_date                         Cancelled_Date
,      papf.full_name                           Cancelled_By
,      pah.note                                     Reason
,       decode(psp.manual_po_num_type,'NUMERIC',   
                     null,poh.segment1)
,        decode(psp.manual_po_num_type,'NUMERIC',                      
              to_number(poh.segment1), null)
FROM  po_line_locations      pll
,      ap_supplier_sites           pvs
,      po_vendors                  pov
,      po_action_history         pah
,      po_headers                   poh
,      PER_ALL_PEOPLE_F     PAPF
,      PER_ALL_PEOPLE_F     PAPF2
,      po_system_parameters  psp
WHERE  poh.cancel_flag            = 'Y' 
AND    poh.po_header_id            = pll.po_header_id (+)
AND    pov.vendor_id                 = poh.vendor_id
AND    pvs.vendor_site_id          = poh.vendor_site_id
AND    (pah.object_revision_num,pah.sequence_num) = 
                  (Select max(object_revision_num),max(sequence_num)
                    From po_action_history
                    Where object_type_code in ('PO','PA')
                    And   action_code in ('CANCEL','MASSCANCEL','RELEASE_MASSCANCEL')
                    And   poh.po_header_id = po_action_history.object_id
                    AND    trunc(action_date) 
	               BETWEEN nvl(:P_cancelled_date_from,trunc(action_date))
	               AND     nvl(:P_cancelled_date_to,trunc(action_date))
                   )
AND    pah.object_id                    = poh.po_header_id
AND    pah.object_type_code         in ('PO','PA')
AND    pah.action_code              in ( 'CANCEL','MASSCANCEL','RELEASE_MASSCANCEL')
AND    trunc(pah.action_date) 
               BETWEEN nvl(:P_cancelled_date_from,trunc(pah.action_date))
               AND     nvl(:P_cancelled_date_to,trunc(pah.action_date))
AND    papf.person_id(+)             = pah.employee_id
AND    pov.vendor_name       >= decode(:P_Vendor_Name_From,'',
                                       pov.vendor_name,:P_Vendor_Name_From)
AND    pov.vendor_name       <= decode(:P_Vendor_Name_To,'',
                                       pov.vendor_name,:P_Vendor_Name_To)
AND    papf2.person_id(+)            = poh.agent_id
/* AND    nvl(papf2.full_name,'A')  = decode(:P_Buyer,'',nvl(papf2.full_name,'A'),
                                                  :P_Buyer) */
/*Bug#2453022 Commented out the above condition and replaced
 it as follows as the user parameter P_Buyer will now return the id and not the name */
AND    nvl(papf2.person_id,-9999)  = decode(:P_Buyer,'',nvl(papf2.person_id,-9999),
                                                  :P_Buyer)
/* Bug5122407 - Modified shipment_type condition */
AND pll.po_release_id(+) is null
AND   ( pll.shipment_type in ('STANDARD','PLANNED')  OR poh.type_lookup_code='BLANKET')
and    PAPF.EMPLOYEE_NUMBER (+) IS NOT NULL
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 (+))) = '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.EMPLOYEE_NUMBER(+) IS NOT NULL
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 (+))) = 'TRUE'
AND DECODE(HR_GENERAL.GET_XBG_PROFILE,'Y', PAPF2.BUSINESS_GROUP_ID(+) ,
HR_GENERAL.GET_BUSINESS_GROUP_ID) = PAPF2.BUSINESS_GROUP_ID(+)
GROUP BY poh.segment1 
,        pov.vendor_name                      
,        pvs.vendor_site_code                 
,        poh.currency_code
,        poh.creation_date                    
,        pah.action_date                      
,        papf.full_name                        
,        papf2.full_name
,        pah.note  
,         decode(psp.manual_po_num_type,'NUMERIC',   
                     null,poh.segment1)
,        decode(psp.manual_po_num_type,'NUMERIC',                      
              to_number(poh.segment1), null)   
/*ORDER BY decode(psp.manual_po_num_type,'NUMERIC',   
                     null,poh.segment1)
,        decode(psp.manual_po_num_type,'NUMERIC',                      
              to_number(poh.segment1), null) */
UNION 
SELECT poh.segment1 || '-' || por.release_num  PO_Number_Release
,      pov.vendor_name                      Vendor
,      pvs.vendor_site_code               Vendor_Site
,      poh.creation_date                      Creation_Date
,      poh.currency_code                    C_CURRENCY
,      SUM (DECODE (POL.order_type_lookup_code,
                                'RATE', PLL.amount,
                                'FIXED PRICE', PLL.amount,
                                NVL(PLL.quantity, 0) * NVL(PLL.price_override, 0))) C_AMOUNT /* <SERVICES FPJ> */
,      papf2.full_name                             Buyer
,      pah.action_date                          Cancelled_Date
,      papf.full_name                               Cancelled_By
,      pah.note                                       Reason
,       decode(psp.manual_po_num_type,'NUMERIC',   
                     null,poh.segment1)
,        decode(psp.manual_po_num_type,'NUMERIC',                      
              to_number(poh.segment1), null)
FROM  po_line_locations      pll
,      po_releases                      por
,      ap_supplier_sites              pvs
,      po_vendors                      pov
,      po_action_history           pah
,      po_headers                      poh
,      po_lines                         POL    /* <SERVICES FPJ> */
,      PER_ALL_PEOPLE_F  PAPF
,      PER_ALL_PEOPLE_F  PAPF2
,      po_system_parameters  psp
WHERE  por.cancel_flag             = 'Y' 
AND    pll.po_release_id              = por.po_release_id 
AND    POH.po_header_id = POL.po_header_id    /* <SERVICES FPJ> */
AND    POL.po_line_id = PLL.po_line_id (+)              /* <SERVICES FPJ> */
AND    pov.vendor_id                   = poh.vendor_id
AND    pvs.vendor_site_id           = poh.vendor_site_id
AND    (pah.object_revision_num,pah.sequence_num) = 
                  (Select max(object_revision_num),max(sequence_num)
                    From po_action_history
                    Where object_type_code in ('RELEASE')
                    And   action_code in ('CANCEL','MASSCANCEL','RELEASE_MASSCANCEL')
                    And   por.po_release_id = po_action_history.object_id
                    AND    trunc(action_date) 
	               BETWEEN nvl(:P_cancelled_date_from,trunc(action_date))
	               AND     nvl(:P_cancelled_date_to,trunc(action_date))
                   )
AND    pah.object_id                    = por.po_release_id
AND    pah.object_type_code         in ('RELEASE')
AND    pah.action_code              in ( 'CANCEL','MASSCANCEL','RELEASE_MASSCANCEL')
AND    trunc(pah.action_date) 
               BETWEEN nvl(:P_cancelled_date_from,trunc(pah.action_date))
               AND     nvl(:P_cancelled_date_to,trunc(pah.action_date))
AND    papf.person_id(+)             = pah.employee_id
AND    pov.vendor_name       >= decode(:P_Vendor_Name_From,'',
                                       pov.vendor_name,:P_Vendor_Name_From)
AND    pov.vendor_name       <= decode(:P_Vendor_Name_To,'',
                                       pov.vendor_name,:P_Vendor_Name_To)
AND    papf2.person_id(+)            = por.agent_id
/* AND    nvl(papf2.full_name,'A')  = decode(:P_Buyer,'',nvl(papf2.full_name,'A'),
                                                  :P_Buyer) */
/*Bug#2453022 Commented out the above condition and replaced
 it as follows as the user parameter P_Buyer will now return the id and not the name */
AND    nvl(papf2.person_id,-9999)  = decode(:P_Buyer,'',nvl(papf2.person_id,-9999),
                                                  :P_Buyer)
AND    pll.shipment_type in ('BLANKET','SCHEDULED')
/* and    PAPF.EMPLOYEE_NUMBER (+) IS NOT NULL*/    /*<R12 CWK>*/
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'                      /* <R12 CWK>*/
AND DECODE(HR_GENERAL.GET_XBG_PROFILE,'Y', PAPF.BUSINESS_GROUP_ID(+) ,
HR_GENERAL.GET_BUSINESS_GROUP_ID) = PAPF.BUSINESS_GROUP_ID(+)
/* and PAPF2.EMPLOYEE_NUMBER(+) IS NOT NULL*/       /*<R12 CWK>*/
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'       /*<R12 CWK>*/ 
AND DECODE(HR_GENERAL.GET_XBG_PROFILE,'Y', PAPF2.BUSINESS_GROUP_ID(+) ,
HR_GENERAL.GET_BUSINESS_GROUP_ID) = PAPF2.BUSINESS_GROUP_ID(+)
GROUP BY poh.segment1 || '-' || por.release_num     
,        pov.vendor_name                      
,        pvs.vendor_site_code                 
,        poh.currency_code
,        poh.creation_date                    
,        pah.action_date                      
,        papf.full_name                        
,        papf2.full_name
,        pah.note  
,         decode(psp.manual_po_num_type,'NUMERIC',   
                     null,poh.segment1)
,        decode(psp.manual_po_num_type,'NUMERIC',                      
              to_number(poh.segment1), null)   
 ,        por.release_num 
/* ORDER BY decode(psp.manual_po_num_type,'NUMERIC',   
                     null,poh.segment1) 
,        decode(psp.manual_po_num_type,'NUMERIC',                      
              to_number(poh.segment1), null) */
ORDER BY 11,12
Parameter Name SQL text Validation
To
 
Date
Cancelled Dates From
 
Date
Buyer Name
 
LOV Oracle
To
 
LOV Oracle
Vendors From
 
LOV Oracle
Title