PO Buyer's Requisition Action Required

SELECT   prl.item_revision                   Rev
,        null                        C_FLEX_ITEM
,        null                         C_FLEX_CAT
,        prh.segment1                        Req_Number 
,        prl.line_num                        Line
,        prl.deliver_to_location_id   Location_id
,        prl.need_by_date                    Need_by_date
,        prl.creation_date                   Order_by_date
,        papf1.full_name                      Requestor
,        prl.unit_meas_lookup_code           Unit
,        decode(plt.order_type_lookup_code,
                 'AMOUNT',null,prl.quantity) Quantity
,        decode(plt.order_type_lookup_code,
               'AMOUNT',null,prl.unit_price) Price
,       DECODE (PLT.order_type_lookup_code,                     
                        'FIXED PRICE',  PRL.amount,
                        'RATE', PRL.amount,
                        PRL.quantity * PRL.unit_price) Amount
,        prl.note_to_agent                   Note_to_buyer
,        prl.suggested_vendor_name           Suggested_vendor
,        decode(prl.blanket_po_header_id,
                    NULL,NULL,
               poh.segment1||'-'||prl.blanket_po_line_num)
                                             Suggested_Blanket_PO
,        papf2.full_name                      Suggested_Buyer
,        T.type_name          Suggested_Document_Type
,        prl.item_description                Description
,        msi.inventory_item_id               item_id, 
	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_POXRQUNI_XMLP_PKG.locationformula(prl.deliver_to_location_id) Location
FROM      per_all_people_f                       papf1
,       per_all_people_f                     papf2
,        po_headers                          poh
,        po_line_types                       plt
,        mtl_system_items                    msi
,        mtl_categories                      mca
,        po_requisition_lines                prl
,        po_requisition_headers              prh
,        po_system_parameters                psp
,      PO_DOCUMENT_TYPES_ALL_TL T, PO_DOCUMENT_TYPES_ALL_B B 
WHERE    prl.line_location_id                is NULL
AND      nvl(prl.reqs_in_pool_flag,'Y') = 'Y'
AND      nvl(prl.closed_code,'OPEN')         != 'FINALLY CLOSED' 
AND      nvl(prl.modified_by_agent_flag,'N') = 'N'
AND      nvl(prl.cancel_flag,'N')            = 'N'
AND      prl.source_type_code                = 'VENDOR'
AND      prh.authorization_status            = 'APPROVED'
AND      prh.requisition_header_id           = prl.requisition_header_id
AND      NVL(PRH.contractor_requisition_flag, 'N') <> 'Y'
AND      msi.inventory_item_id (+)           = prl.item_id
AND      nvl(msi.organization_id      ,nvl(:c_organization_id,-1))          = nvl(:c_organization_id,-1)
AND      mca.category_id (+)                 = prl.category_id
AND      prl.deliver_to_location_id  =   nvl(:p_location_id,prl.deliver_to_location_id)
AND      papf1.person_id (+)                = prl.to_person_id
AND      B.document_subtype(+) = prl.document_type_code
AND      nvl(B.document_type_code, 'PA') in ('QUOTATION','PA')
AND  B.DOCUMENT_TYPE_CODE  = T.DOCUMENT_TYPE_CODE (+)
AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE (+)
AND B.ORG_ID = T.ORG_ID  (+)
AND B.ORG_ID (+)= prl.ORG_ID
AND T.LANGUAGE(+) = USERENV('LANG')
AND      papf1.full_name                      = nvl(:P_requestor,              papf1.full_name)
AND      papf2.person_id (+)                = prl.suggested_buyer_id
AND trunc(sysdate) between papf1.effective_start_date(+) AND papf1.effective_end_date (+) AND decode(hr_security.view_all,'Y','TRUE',
hr_security.show_record('PER_ALL_PEOPLE_F', papf1.person_id(+),papf1.person_type_id(+),papf1.employee_number(+),papf1.applicant_number(+)
,papf1.npw_number(+) ))='TRUE'  AND              decode(hr_general.get_xbg_profile,'Y',papf1.business_group_id(+),hr_general.get_business_group_id)=papf1.business_group_id(+)
AND trunc(sysdate) between papf2.effective_start_date (+) AND papf2.effective_end_date (+)AND decode(hr_security.view_all,'Y','TRUE',
hr_security.show_record('PER_ALL_PEOPLE_F', papf2.person_id(+),papf2.person_type_id(+),papf2.employee_number(+),papf2.applicant_number(+)
,papf2.npw_number(+)  ))='TRUE'  AND   
decode(hr_general.get_xbg_profile,'Y',papf2.business_group_id(+),hr_general.get_business_group_id)=papf2.business_group_id(+) 
AND      prl.line_type_id                    = plt.line_type_id
AND      prl.blanket_po_header_id            = poh.po_header_id (+)
AND      ( nvl(prl.suggested_vendor_name,' ')
           BETWEEN nvl(:P_suggested_vendor_from,
                       nvl(prl.suggested_vendor_name,' '))
           AND     nvl(:P_suggested_vendor_to,
                       nvl(prl.suggested_vendor_name,' ')))
AND      (nvl(prl.need_by_date,
          DECODE(:P_needby_date_from,'1900/01/01',
                           sysdate,:P_needby_date_from-1)) >=
        nvl(:P_needby_date_from,nvl(prl.need_by_date,sysdate-1)))
AND      (nvl(prl.need_by_date,
                     DECODE(:P_need_by_date_to,'9999/01/01',
                            sysdate,:P_need_by_date_to+1)) <=
                     nvl(:P_need_by_date_to,
                             nvl(prl.need_by_date,sysdate+1)))
AND &P_WHERE_CAT
AND    ((decode(psp.manual_req_num_type,'NUMERIC',decode(rtrim(prh.segment1,'0123456789'),NULL,
            to_number(prh.segment1),-1),null)
                BETWEEN decode(psp.manual_req_num_type,'NUMERIC',
                        to_number(nvl(:p_req_num_from,decode(rtrim(prh.segment1,'0123456789'),
                        NULL,to_number(prh.segment1),-1))),null)
                AND     decode(psp.manual_req_num_type,'NUMERIC',
                        to_number(nvl(:p_req_num_to,decode(rtrim(prh.segment1,'0123456789'),
                        NULL,to_number(prh.segment1),-1))),null))
OR
        (prh.segment1 BETWEEN decode(psp.manual_req_num_type,'ALPHANUMERIC',
                              nvl(:P_req_num_from,prh.segment1),null)
                     AND     decode(psp.manual_req_num_type,'ALPHANUMERIC',
                              nvl(:P_req_num_to,prh.segment1),null)))
ORDER BY 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') , 
	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.creation_date,
     prl.requisition_line_id