PO Item Detail Listing

SELECT  null                                                     C_FLEX_ITEM ,       null                                                      C_FLEX_CAT
,       mtl.description                                                  Description  
,       null                                                       C_FLEX_ACC      
,       msi.qty_rcv_tolerance                                             Receipt_Tolerance      
,       decode(msi.allow_item_desc_update_flag,'Y',:yes,'N',:no,null)    Allow_Item_Description_Update      
,       mum.unit_of_measure                                              Unit
,       msi.price_tolerance_percent                                      Price_Tolerance
,       decode(msi.outside_operation_flag,'Y',:yes,'N',:no,null)         Outside_Operation
,       decode(msi.stock_enabled_flag,'Y',:yes,'N',:no,null)             Stock_Item
,       msi.invoice_close_tolerance                                      Invoicing_Close_Tolerance
,       decode(msi.rfq_required_flag,'Y',:yes,'N',:no,null)              RFQ_Required      
,       decode(msi.taxable_flag,'Y',:yes,'N',:no,null)                   Taxable
,       msi.receive_close_tolerance                                      Receiving_Close_Tolerance
,       decode(msi.inspection_required_flag,'Y',:yes,'N',:no,null)       Inspection_Required
,       decode(msi.internal_order_enabled_flag,'Y',:yes,'N',:no,null)            Internal_Order
,        plc2.displayed_field                                                      Tolerance_Level
,       decode(msi.allow_substitute_receipts_flag,'Y',:yes,'N',:no,null) Allow_Substitute_Receipts
,       decode(msi.inventory_asset_flag,'Y',:yes,'N',:no,null)
Inventory_Asset
,       papf.full_name                                                    Buyer
,       decode(msi.allow_express_delivery_flag,'Y',:yes,'N',:no,null)    AllowExpress_Delivery
,       plc1.displayed_field                                                      Days_Receipt_Exception
,       msi.days_early_receipt_allowed                                   Days_Early_Receipt_Allowed
,       plc.displayed_field                                                 Enforce_Ship_To_Location 
,       msi.list_price_per_unit                                          List_Price    
,       msi.days_late_receipt_allowed                                    Days_Late_Receipt_Allowed
,       pun.un_number                                                    UN_Number
,       msi.market_price                                                 Market_Price
,       round(msi.fixed_order_quantity,:P_qty_precision)                 Reorder_Quantity
,       phc.hazard_class                                                 Hazard_Class
&C_CST_SELECT				
,       msi.rounding_factor                                              Rounding_Factor
,       null                                                      C_FLEX_ASS                    
,       msi.receiving_routing_id                                         Routing_Steps
,       rrh.routing_name                                                 Default_Routing
,       decode(msi.must_use_approved_vendor_flag,'Y',:yes,'N',:no,null)  Use_Approved_Vendor
,       msi.outside_operation_uom_type                                   Outside_Operation_Unit_Type
,       msi.unit_of_issue                                                Unit_of_Issue
,       msi.inventory_item_id                                              Parent_id 
,       msi.expense_account                                              Expense_id
,       msi.full_lead_time                                               Processing_Time, 
	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_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_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_ass_disp', 'OFA', 'CAT#', 101, NULL, FAC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ASS_DISP
FROM                                                                     
         mtl_system_items                  msi  
,        mtl_system_items_tl              mtl    
,        gl_code_combinations              gcc                                   
,        mtl_units_of_measure              mum
,        po_un_numbers                     pun
,        po_hazard_classes                 phc
,        per_all_people_f                    papf
,        po_agents                         poa
,        rcv_routing_headers               rrh
&C_CST_FROM
,        &C_OFA_dynamic                     fac
,        mtl_categories                    mca
,        mtl_item_categories               mic
,        po_lookup_codes             plc
,        po_lookup_codes             plc1
,        po_lookup_codes             plc2
where 
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 msi.expense_account               = gcc.code_combination_id (+)
&C_OFA_where
AND mtl.inventory_item_id = msi.inventory_item_id 
AND mtl.organization_id = msi.organization_id 
AND mtl.language = userenv('LANG') 
AND      msi.organization_id               = :c_organization_id
AND      msi.primary_uom_code              = mum.uom_code
AND      msi.buyer_id                      = poa.agent_id (+)
AND      poa.agent_id                      = papf.person_id(+) 
AND      nvl(papf.full_name,'-1')            = nvl(:P_buyer_name, nvl(papf.full_name,'-1'))
AND      msi.un_number_id                  = pun.un_number_id (+)
AND      msi.hazard_class_id               = phc.hazard_class_id (+)
AND      msi.receiving_routing_id          = rrh.routing_header_id (+)
&C_CST_WHERE
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      (msi.purchasing_item_flag         = 'Y'
         OR
          msi.internal_order_flag          = 'Y')
AND      ((:P_active_inactive              = 'BOTH')
OR       (:P_active_inactive               = 'ACTIVE'
         AND msi.purchasing_enabled_flag  = 'Y')
OR       (:P_active_inactive               = 'INACTIVE'
         AND msi.purchasing_enabled_flag   = 'N'))
AND      &P_WHERE_CAT
AND      &P_WHERE_ITEM
AND     plc.lookup_type (+) = 'RECEIVING CONTROL LEVEL'
AND     plc.lookup_code (+) = msi.enforce_ship_to_location_code
AND     plc1.lookup_type (+) = 'RECEIVING CONTROL LEVEL'
AND     plc1.lookup_code (+) = msi.receipt_days_exception_code
AND     plc2.lookup_type (+) = 'RECEIVING CONTROL LEVEL'
AND     plc2.lookup_code (+) = msi.qty_rcv_exception_code
ORDER BY &orderby_clause