PO Cancelled Purchase Orders (1)

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Cancelled Purchase Orders Report
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 NameSQL textValidation
Operating Unit
 
LOV
Title
 
Char
Vendors From
 
LOV Oracle
To
 
LOV Oracle
Buyer Name
 
LOV Oracle
Cancelled Dates From
 
Date
To 2
 
Date
Download
Related Blitz Reports
PO Cancelled Purchase Orders PO Headers and Lines 11i GL Account Distribution Analysis SAM: GL Account Analysis (Drilldown) (with inventory and WIP) ECC Project Manufacturing, Procurement Activities, SQL3 ECC Project Manufacturing, Procurement Activities, SQL1 GL Account Analysis AP Supplier Upload PO Headers and Lines
Blitz Report™