SELECT null C_FLEX_CAT
, null C_FLEX_ITEM
, mtl.description Item_Description
, null C_FLEX_ACC
, papf.full_name Buyer
, mum.unit_of_measure Unit
, msi.list_price_per_unit List_Price
, plc1.displayed_field Stock_Item
, msi.expense_account Expense_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,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_acc_disp', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ACC_DISP,
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
FROM gl_code_combinations gcc
, mtl_units_of_measure mum
, po_agents poa
, per_all_people_f papf
, mtl_system_items msi
, mtl_system_items_tl mtl
, mtl_categories mca
, mtl_item_categories mic
, po_lookup_codes plc1
WHERE msi.expense_account = gcc.code_combination_id (+)
AND msi.organization_id = :c_organization_id
AND msi.primary_uom_code = mum.uom_code
AND msi.buyer_id = poa.agent_id (+)
AND mtl.inventory_item_id = msi.inventory_item_id
AND mtl.organization_id = msi.organization_id
AND mtl.language = userenv('LANG')
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 poa.agent_id = papf.person_id (+)
AND msi.inventory_item_id = mic.inventory_item_id
AND mic.category_set_id = :c_category_set_id
AND mic.organization_id = :c_organization_id
AND mic.category_id = mca.category_id
AND plc1.lookup_type = 'YES/NO'
AND plc1.lookup_code = msi.stock_enabled_flag
AND ((:P_active_inactive = 'BOTH')
OR (:P_active_inactive = 'ACTIVE'
AND nvl(msi.purchasing_enabled_flag,'N') = 'Y')
OR (:P_active_inactive = 'INACTIVE'
AND nvl(msi.purchasing_enabled_flag,'N') = 'N'))
--order by &P_FLEX_ITEM
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'),&P_FLEX_ITEM,plc1.displayed_field,mtl.description,papf.full_name,msi.list_price_per_unit,mum.unit_of_measure,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_acc_disp', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_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') |
