PO Requisitions on Cancelled Order

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Requisitions on Cancelled Order Report
Application: Purchasing
Source: Requisitions on Cancelled Order Report (XML)
Short Name: POXRQRCO_XML
DB package: PO_POXRQRCO_XMLP_PKG
Run PO Requisitions on Cancelled Order and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Operating Unit 
 
 LOV
Title 
 
 Char
Items From 
 
 Char
To 
 
 Char
Requester 
 
 LOV Oracle
Categories From 
 
 Char
To 2 
 
 Char
Dynamic Precision Option 
 
 LOV Oracle
Download
Related Blitz Reports
GL Account Analysis FND Attached Documents GL Account Analysis (Distributions) ECC Procurement, Requisitions PO Backordered Internal Requisitions PO Receiving Transactions Register PO Receipt Adjustments PO Receiving Transactions Pipeline PO Requisition Upload
Blitz Report™

Blitz Report™ provides multiple benefits: