PO Requisitions on Cancelled Order

SELECT  
decode(psp.manual_req_num_type, 'NUMERIC',
                null, prh.segment1)       dummy1
,        decode(psp.manual_req_num_type,'NUMERIC',
                to_number(prh.segment1), null)      dummy2
,       prh.segment1                            ReqNumber
,       prl.line_num                            Line
,       null                             C_FLEX_CAT
,       null                            C_FLEX_ITEM
,       prl.item_revision                       Rev
,       prl.item_description                              Item_Description
,       papf.full_name                                     Requestor
,       round(prl.quantity,:P_qty_precision)              Ordered
,       round(prl.quantity_delivered,:P_qty_precision)    Delivered
,       oeh.order_number                        Order_Number
,       oel.line_number                         OLine
,       round(oel.shipped_quantity,:P_qty_precision)      Shipped
,       fnd.user_name                           Cancelled_By
,       ohis.creation_date                         C_Date
,       olu.meaning                       Cancel_Reason, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_FLEX_CAT_DISP, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ITEM_DISP
FROM    po_requisition_lines                    prl
,       po_requisition_headers                  prh
,       per_all_people_f                          papf
,       mtl_system_items                        msi
,       mtl_categories                          mca
,       oe_order_lines_all                                oel
,       oe_order_headers_all                              oeh
,       oe_lookups                              olu
,       fnd_user                                fnd
,       po_system_parameters                    psp
,       oe_order_lines_history             ohis
,       oe_reasons oer
WHERE   papf.full_name                   = nvl(:P_requestor,papf.full_name)
AND     papf.person_id                 = prl.to_person_id
AND     prl.requisition_header_id       = prh.requisition_header_id
AND     prl.source_type_code            = 'INVENTORY'
AND     nvl(prh.transferred_to_oe_flag, 'N') = 'Y'
AND     nvl(prl.cancel_flag,'N')            = 'N'
AND     nvl(prl.closed_code,'OPEN')  <> 'FINALLY CLOSED'
AND     mca.category_id                 = prl.category_id
AND     msi.inventory_item_id(+)        = prl.item_id
AND     nvl(msi.organization_id,prl.source_organization_id) = prl.source_organization_id
AND     exists (select 1 from oe_order_lines_history ohis
                         where oel.line_id = ohis.line_id)
AND     oel.ordered_quantity = 0
AND     oel.line_id                          =   ohis.line_id
AND     ohis.hist_type_code   =  'CANCELLATION'
AND     ohis.rowid = (select max(rowid) from oe_order_lines_history oh1
                                 where oel.line_id = oh1.line_id
                                 and oh1.hist_type_code = 'CANCELLATION'                                  and creation_date = ( select max(creation_date) from oe_order_lines_history oh2
                                                                     where oel.line_id = oh2.line_id
                                                                AND oh2.hist_type_code = 'CANCELLATION' ) ) AND     oel.header_id                   = oeh.header_id
AND  oeh.source_document_id = prh.requisition_header_id
AND  oel.source_document_line_id  = prl.requisition_line_id
AND     oeh.order_source_id   = psp.order_source_id
AND    ohis.reason_id = oer.reason_id
AND     oer.reason_code   || ''              = olu.lookup_code
AND     olu.lookup_type                 = 'CANCEL_CODE'
AND     fnd.user_id    = ohis.created_by
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 &P_WHERE_CAT
AND &P_WHERE_ITEM
order by 1,2,4,12,13