PO Purchase Requisition Status

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Purchase Requisition Status Report (XML)
Short Name: POXRQRSR_XML
DB package: PO_POXRQRSR_XMLP_PKG
SELECT prl.line_num                 Line
,      null                 C_FLEX_ITEM
,      null                  C_FLEX_CAT
,      prl.item_revision            Rev
,      prl.item_description         Description
,      prl.unit_meas_lookup_code    Unit
,      prl.quantity                 Quantity
,      prl.unit_price               Unit_Price
,      prl.deliver_to_location_id      deliver_to_location_id
,      papf.full_name                Requestor
,      prl.quantity_delivered       Quantity_Delivered
,      lot.location_code            Deliver_To_Location
,      prl.need_by_date             Need_By_Date
,      plkc.displayed_field         On_RFQ
,      prl2.line_num                From_Req_Line
,      poh.segment1                 PO_Number
,      decode(poh.segment1, '', '', plc.displayed_field)          PO_Status
,      pol.line_num                 PO_Line
,      prl.requisition_header_id    Req_Header_IdB, 
	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, 
	nvl2(prl.item_id,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'),null) C_FLEX_ITEM_DISP, 
	PO_POXRQRSR_XMLP_PKG.cf_locationsformula(lot.location_code, prl.deliver_to_location_id) CF_locations
FROM   po_requisition_lines         prl
,      po_requisition_lines         prl2
,      po_lookup_codes              plc
,     per_all_people_f               papf
,      HR_LOCATIONS_ALL_TL lot 
,      po_line_locations_all         pll
,      po_lines_all                    pol
,      po_headers_all                  poh
,      po_lookup_codes              plkc
,      mtl_system_items             msi
,      mtl_categories               mca
WHERE  prl.to_person_id           = papf.person_id 
AND    nvl(prl.cancel_flag,'N')   = 'N'
AND    prl.deliver_to_location_id = lot.location_id  (+)
AND    prl.deliver_to_location_id is not null 
AND    pll.line_location_id (+)   = prl.line_location_id
AND    pll.po_line_id                 = pol.po_line_id (+)
AND    pol.po_header_id          = poh.po_header_id (+)
AND    plkc.lookup_type           = 'YES/NO'
AND    plkc.lookup_code           = nvl(prl.on_rfq_flag,'N')
AND    prl.item_id               = msi.inventory_item_id(+)
AND    :c_organization_id         = nvl(msi.organization_id, :c_organization_id)
AND    prl.category_id            = mca.category_id
AND    prl.parent_req_line_id     = prl2.requisition_line_id (+)
AND    nvl(poh.authorization_status,'INCOMPLETE')         = plc.lookup_code
AND    plc.lookup_type            = 'DOCUMENT STATE'
AND   nvl( lot.location_code,-1)         = nvl(:P_deliver_to,nvl(lot.location_code,-1)) 
AND    nvl(papf.full_name,'A')     = nvl(:P_requestor,nvl(papf.full_name,'A'))
AND    nvl(prl.closed_code,'OPEN') != 'FINALLY CLOSED'
AND   prl.requisition_header_id = :Req_Header_Id 
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 lot.LANGUAGE(+) = USERENV('LANG') 
 and prl.requisition_header_id=:Req_Header_Id
ORDER BY prl.line_num
Parameter Name SQL text Validation
Category Structure
 
Number
Dynamic Precision Option
 
LOV Oracle
Location
 
LOV Oracle
Requester
 
LOV Oracle
To
 
Date
Creation Dates From
 
Date
To
 
LOV Oracle
Requisition Numbers From
 
LOV Oracle
Title