PO Internal Requisition Status

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Internal Requisition Status Report (XML)
Short Name: POXRQSIN_XML
DB package: PO_POXRQSIN_XMLP_PKG
SELECT 
 prh.segment1                            Req_Number
,       trunc(prl.creation_date)                CDate
,       papf.full_name                           Requestor
,       prl.source_subinventory                 Subinventory
,       null                            C_FLEX_ITEM 
,       prl.item_revision                       Rev
,       prl.item_description                    Description
,       prl.unit_meas_lookup_code               Unit
,       round(prl.quantity,:P_qty_precision)                           Required
,       round(nvl(prl.quantity_delivered,null),:P_qty_precision)                  Delivered
,       round(sum(&C_backordered),
                                       :P_qty_precision)                Backordered  
,       decode(nvl(prl.cancel_flag,'N'),
               'Y',
               round((prl.quantity - nvl(prl.quantity_delivered,0)),:P_qty_precision),
               to_number(NULL))                                         Cancelled
,     nvl(prl.unit_price, 0)	unit_price
,    round(&C_ship_qty, :P_qty_precision) Shipped, 
	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, 
	PO_POXRQSIN_XMLP_PKG.c_ship_amountformula(round ( prl.quantity , :P_qty_precision ), nvl ( prl.unit_price , 0 )) C_ship_amount
FROM    po_requisition_lines                    prl
,       po_requisition_headers                  prh
,       mtl_system_items                        msi
,       per_all_people_f                          papf
&C_from
,       po_system_parameters_all                    psp1
WHERE  
&P_WHERE_QUERY
AND     trunc(prl.creation_date) 
        BETWEEN nvl(:P_creation_date_from,trunc(prl.creation_date)-1)
        AND     nvl(:P_creation_date_to,trunc(prl.creation_date)+1)
AND   psp1.ORG_ID = prh.ORG_ID  
AND     papf.full_name = nvl(:P_requestor,papf.full_name)
AND     nvl(prl.source_subinventory,'-1') 
        BETWEEN nvl(:P_subinventory_from,nvl(prl.source_subinventory,'-1'))
        AND     nvl(:P_subinventory_to,  nvl(prl.source_subinventory,'-1'))
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     msi.inventory_item_id                       = prl.item_id
AND     nvl(msi.organization_id,prl.source_organization_id)   = prl.source_organization_id
&C_where
AND     papf.person_id                             = prl.to_person_id
AND    prh.authorization_status = nvl(:P_status,prh.authorization_status)
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_ITEM
GROUP BY prh.segment1, prl.creation_date, papf.full_name,
         prl.source_subinventory, 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'), prl.item_revision,
         prl.item_description, prl.unit_meas_lookup_code, prl.quantity,
         prl.quantity_delivered, &C_backordered, prl.cancel_flag, prl.unit_price, &C_ship_qty
UNION
SELECT 
prh.segment1                            Req_Number
,       trunc(prl.creation_date)                CDate
,       papf.full_name                           Requestor
,       prl.source_subinventory            Subinventory
,       null                            C_FLEX_ITEM
,       prl.item_revision                       Rev
,       prl.item_description                    Description
,       prl.unit_meas_lookup_code               Unit
,       round(prl.quantity,:P_qty_precision)                            Required
,       round(nvl(prl.quantity_delivered,0),:P_qty_precision)                  Delivered
,       to_number(null)                                                 Backordered  
,       decode(nvl(prl.cancel_flag,'N'),
               'Y',
               round((prl.quantity - nvl(prl.quantity_delivered,0)),:P_qty_precision),
               to_number(NULL))                                         Cancelled
,     nvl(prl.unit_price, 0)			unit_price
,    to_number(NULL)  Shipped, 
	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, 
	PO_POXRQSIN_XMLP_PKG.c_ship_amountformula(round ( prl.quantity , :P_qty_precision ), nvl ( prl.unit_price , 0 )) C_ship_amount
FROM    po_requisition_lines                    prl
,       po_requisition_headers                  prh
,       mtl_system_items                        msi
,       per_all_people_f                         papf
,       po_system_parameters_all                    psp1
WHERE  
&P_WHERE_QUERY
AND   psp1.ORG_ID = prh.ORG_ID  
AND     trunc(prl.creation_date) 
        BETWEEN nvl(:P_creation_date_from,trunc(prl.creation_date)-1)
        AND     nvl(:P_creation_date_to,trunc(prl.creation_date)+1)
AND     papf.full_name = nvl(:P_requestor,papf.full_name)
AND     nvl(prl.source_subinventory,'-1') 
        BETWEEN nvl(:P_subinventory_from,nvl(prl.source_subinventory,'-1'))
        AND     nvl(:P_subinventory_to,  nvl(prl.source_subinventory,'-1'))
AND     prl.requisition_header_id                   = prh.requisition_header_id
AND     prl.source_type_code                        = 'INVENTORY'
AND     (nvl(prh.transferred_to_oe_flag,'N')         = 'N'
        OR (nvl(prh.transferred_to_oe_flag,'N') = 'Y' &C_interface_where)) 
AND     msi.inventory_item_id(+)                    = prl.item_id
AND     nvl(msi.organization_id,prl.source_organization_id)   = prl.source_organization_id
AND     papf.person_id                             = prl.to_person_id
AND     prh.authorization_status = nvl(:P_status,prh.authorization_status)
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_ITEM
ORDER BY &ORDERBY_CLAUSE
Parameter Name SQL text Validation
Dynamic Precision Option
 
LOV Oracle
Sort By
 
LOV Oracle
To
 
LOV Oracle
Requisition Numbers From
 
LOV Oracle
Status
 
LOV Oracle
To
 
LOV Oracle
Subinventories From
 
LOV Oracle
Requester
 
LOV Oracle
to
 
Items From
 
To
 
Date
Creation Dates From
 
Date
Title
 
Ask a question