PO Internal Requisitions/Deliveries Discrepancy

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Internal Requisitions/Deliveries Discrepancy Report (XML)
Short Name: POXRQSDD_XML
DB package: PO_POXRQSDD_XMLP_PKG
SELECT 
 prh.segment1                                    Req_Number
,        prl.creation_date                               Req_Date
,        papf.full_name                                   Requestor
,        prl.line_num                                    Line
,        psp.order_source_id                      Order_source_Id
,        null                                    C_FLEX_ITEM
,        prl.item_revision                               Rev
,        msi.description                                 Description
,        ood.organization_name                           Organization
,        prl.source_subinventory                         Subinventory
,        prl.unit_meas_lookup_code                       Unit
,        prl.unit_price                                              Unit_price
,        round(prl.quantity_delivered,:P_qty_precision)                   Quantity_Delivered, 
	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_POXRQSDD_XMLP_PKG.c_get_shipped_quantity(round ( prl.quantity_delivered , :P_qty_precision ), prl.unit_price, prl.line_num, prh.segment1, psp.order_source_id, prh.org_id) C_GET_SHIPPED_QUANTITY,
	PO_POXRQSDD_XMLP_PKG.C_quantity_shipped_p C_quantity_shipped,
	PO_POXRQSDD_XMLP_PKG.C_Quantity_Variance_p C_Quantity_Variance,
	PO_POXRQSDD_XMLP_PKG.C_Cost_variance_p C_Cost_variance
FROM     po_requisition_headers                 prh
,        po_requisition_lines                   prl
,        mtl_system_items                       msi
,       per_all_people_f                            papf
,        org_organization_definitions           ood
,        po_system_parameters_all                  psp
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   psp.ORG_ID = prh.org_id  
AND      papf.full_name                      = nvl(:P_requestor,papf.full_name)
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      prl.source_organization_id         = ood.organization_id 
AND      papf.person_id                    = prl.to_person_id
AND      msi.inventory_item_id              = prl.item_id
AND      nvl(msi.organization_id,prl.source_organization_id)
                                            = prl.source_organization_id
AND  papf.business_group_id = (select nvl(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     prl.quantity_delivered  > 0
ORDER BY &orderby_clause
Parameter Name SQL text Validation
Dynamic Precision Option
 
LOV Oracle
Sort By
 
LOV Oracle
To
 
Date
Requisition Creation Dates From
 
Date
Requester
 
LOV Oracle
To
 
LOV Oracle
Requisition Numbers From
 
LOV Oracle
Title