PO Purchase Requisition Status

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

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