PO Buyer's Requisition Action Required

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Buyer's Requisition Action Required Report (XML)
Short Name: POXRQUNI_XML
DB package: PO_POXRQUNI_XMLP_PKG
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
Parameter Name SQL text Validation
P_BASE_CURRENCY
 
LOV Oracle
P_STRUCT_NUM
 
Number
Dynamic Precision Option
 
LOV Oracle
Print Price History
 
LOV Oracle
To
 
LOV Oracle
Requisition Numbers From
 
LOV Oracle
Requester
 
LOV Oracle
Location
 
LOV Oracle
To
 
Date
Need By Dates From
 
Date
To
 
Categories From
 
To
 
LOV Oracle
Suggested Vendors From
 
LOV Oracle
Title