PO ReqExpress Templates Listing

SELECT distinct prxl.sequence_num                         Sequence
,      null                                       C_FLEX_ITEM
,      null                                        C_FLEX_CAT
,      prxl.item_revision                                 Rev
,      prxl.unit_meas_lookup_code                         Unit
,      prxl.unit_price                                    Unit_Price
,      decode(prxl.source_type_code,
              'INVENTORY',(decode(nvl(ood.organization_name,'A')
                                ,'A',ood.organization_name
                                ,ood.organization_name||', ')||
                          decode(nvl(prxl.source_subinventory,'')
                                ,'',prxl.source_subinventory
                                ,prxl.source_subinventory))
             ,'VENDOR'   ,(decode(nvl(pov.vendor_name,'')
                                ,'',pov.vendor_name
                                ,pov.vendor_name||', '||
                          decode(nvl(pvs.vendor_site_code,'')
                                ,'',pvs.vendor_site_code
                                ,pvs.vendor_site_code||', '||
                          decode(nvl(pvc.first_name,'')
                                ,'',pvc.first_name
                                ,pvc.first_name||', '||
                          decode(nvl(papf.first_name,'')
                                ,'',papf.first_name
                               ,papf.first_name))))))    Source
,      prxl.express_name                                  Express_Name
,      t.type_name                                     Requisition_Type
,      polc.displayed_field                               Source_Type
,      prxl.item_description                               Item_Description, 
	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, 
	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
FROM   mtl_system_items                  msi
,      mtl_categories                    mca
,      po_vendors                        pov
,      po_vendor_sites                   pvs
,      po_vendor_contacts                pvc
,      PER_ALL_PEOPLE_F      PAPF
,      po_reqexpress_lines               prxl
,      po_reqexpress_headers             prxh
,      PO_DOCUMENT_TYPES_ALL_TL T
,      PO_DOCUMENT_TYPES_ALL_B B
,      po_lookup_codes                   polc
,      org_organization_definitions      ood
WHERE  prxl.item_id                      = msi.inventory_item_id (+)
AND    prxl.suggested_vendor_id          = pov.vendor_id         (+)
AND    prxl.suggested_vendor_site_id     = pvs.vendor_site_id    (+)
AND    prxl.suggested_vendor_contact_id  = pvc.vendor_contact_id (+)
AND    prxl.suggested_buyer_id           = papf.person_id       (+)
AND    prxl.category_id                  = mca.category_id
AND    prxl.express_name                 = prxh.express_name 
AND    B.document_type_code           = 'REQUISITION'
AND    B.document_subtype             = prxh.type_lookup_code
AND    polc.lookup_type                  = 'REQUISITION SOURCE TYPE'
AND    polc.lookup_code                  = prxl.source_type_code   
AND    prxl.express_name                 = decode(:P_EXPRESS_NAME,'',
                                                   prxl.express_name,
                                                   :P_EXPRESS_NAME)
AND    prxl.source_organization_id       = ood.organization_id (+)
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   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 = prxh.ORG_ID  
AND T.LANGUAGE = USERENV('LANG') 
 and prxl.express_name=:Express_Name_header
ORDER BY prxl.express_name
,        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')    
,        prxl.sequence_num