INV Item reservations

select	decode(:P_break_id,1,&P_cat_flex,'X')	C_cat_flex,
 	decode(:P_sort_id,2,&P_item_flex,
		            1,to_char(requirement_date,'J'),
		            3,mtst.transaction_source_type_name,
		            'NULL')		C_sort_flex,
	decode(:P_sort_id,1,&P_item_flex,'X')	C_item_flex,
	decode(:P_sort_id,3,&P_item_flex,'X')	C_item_det_flex,
	md.revision		rev,
	md.lot_number	lot_number,
	md.subinventory	subinv,
	&P_loc_flex	C_loc_flex,
	md.locator_id             loc_id,
	md.demand_source_type type_id,
	mtst.transaction_source_type_name  source_type,
	DECODE(md.DEMAND_SOURCE_TYPE,
               2,&P_so_flex,
               8,&P_so_flex,
               3,&P_gl_flex,
               5,to_char(md.DEMAND_SOURCE_HEADER_ID),
               6,&P_alias_flex,
                  md.DEMAND_SOURCE_NAME)    source,
	md.requirement_date		required,
	--ADDED
	to_char(md.requirement_date,'DD-MON-YY')		required1,
	md.primary_uom_quantity 	reserved_qty,
	msi.primary_uom_code 	Uom,
	nvl(md.primary_uom_quantity,0) -
	md.completed_quantity		remaining_qty,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_sort_field', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_sort_field,
	INV_INVDRRSV_XMLP_PKG.c_sort_date_fieldformula(decode ( :P_sort_id , 2 , ( MSI.SEGMENT1 || '\n' || MSI.SEGMENT2 || '\n' || MSI.SEGMENT3 || '\n' || MSI.SEGMENT4 || '\n' || MSI.SEGMENT5 || '\n' || MSI.SEGMENT6 || '\n' || MSI.SEGMENT7 || '\n' || MSI.SEGMENT8 || '\n' || MSI.SEGMENT9 || '\n' || MSI.SEGMENT10 || '\n' || MSI.SEGMENT11 || '\n' || MSI.SEGMENT12 || '\n' || MSI.SEGMENT13 || '\n' || MSI.SEGMENT14 || '\n' || MSI.SEGMENT15 || '\n' || MSI.SEGMENT16 || '\n' || MSI.SEGMENT17 || '\n' || MSI.SEGMENT18 || '\n' || MSI.SEGMENT19 || '\n' || MSI.SEGMENT20 ) , 1 , to_char ( requirement_date , 'J' ) , 3 , mtst.transaction_source_type_name , 'NULL' )) C_sort_date_field,
	--fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_field', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_cat_field,
	--fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_pad', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') C_cat_pad,
	null C_cat_field,
	null C_cat_pad,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_sort_pad', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') C_sort_pad,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_pad', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') C_item_pad,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_field', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_item_field,
  --&C_source C_source,   -- bug 14601364
  --begin of bug 14601364
	decode(md.DEMAND_SOURCE_TYPE, 
               2,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source1', 'INV', 'MKTS', 101, NULL, MKTS.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),
               3,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source3', 'SQLGL', 'GL#', GL1.CHART_OF_ACCOUNTS_ID, NULL, GL1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'),
               5,wip1.wip_entity_name, 
               6,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source2', 'INV', 'MDSP', 101, MDSP.ORGANIZATION_ID, MDSP.DISPOSITION_ID, 'ALL', 'Y', 'VALUE'), 
               8,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source1', 'INV', 'MKTS', 101, NULL, MKTS.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),
                 md.DEMAND_SOURCE_NAME) C_source,				 
  --end of bug 14601364
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_det_field', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_item_det_field,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_loc_field', 'INV', 'MTLL', 101, LOC.ORGANIZATION_ID, LOC.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') C_loc_field
from	mtl_demand		md,
	mtl_system_items		msi,
	mtl_txn_source_types	mtst,
  --begin of bug 14601364
  WIP_ENTITIES wip1 , 
  MTL_SALES_ORDERS MKTS ,
  MTL_GENERIC_DISPOSITIONS mdsp ,
  GL_CODE_COMBINATIONS gl1 ,
  --end of bug 14601364
	mtl_item_locations		loc
	&C_source_from
        	&C_from_cat
where	md.inventory_item_id = msi.inventory_item_id
and	md.organization_id = :P_org_id
and	md.reservation_type = 2
and	md.row_status_flag = 1
and       	md.completed_quantity < md.primary_uom_quantity
and	msi.organization_id = :P_org_id
and	md.locator_id = loc.inventory_location_id(+)
and	loc.organization_id (+) = :P_org_id
and	md.demand_source_type =
decode(:P_source_type_id,'',md.demand_source_type,:P_source_type_id)
and     md.demand_source_type = mtst.transaction_source_type_id
and 	exists(select category_id from
		     mtl_item_categories mic
		     where mic.category_set_id = :P_cat_set_id
		     and mic.inventory_item_id = md.inventory_item_id
	 	     and mic.organization_id = md.organization_id)
--begin of bug 14601364
and md.DEMAND_SOURCE_HEADER_ID = wip1.wip_entity_id (+)
and md.organization_id = wip1.organization_id (+)
and md.demand_source_header_id = mkts.sales_order_id(+)
and md.demand_source_header_id = gl1.code_combination_id(+)
and md.demand_source_header_id = mdsp.disposition_id(+)
-- and           &P_source_where &C_source_where              
and (
  (:P_source_type_id=2 and &P_source_where1)
  or (:P_source_type_id=3 and &P_source_where3)
  or (:P_source_type_id=6 and &P_source_where2)
  or (:P_source_type_id=8 and &P_source_where1)
  or (:P_source_type_id not in (2,3,6,8) )
  or (:P_source_type_id is null)
   )
 &C_source_where
--end of bug 14601364    
and	&P_item_where
and	&P_cat_where &C_cat_where
&C_date_where
--&C_order_by
--order by 17,18,20,2,22,13,4,5,6,7,8,9,10,11,12,14,15,16
ORDER BY 2 ASC,13 ASC , 12 , MSI.SEGMENT1 , revision