INV Item reservations

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Item reservations report (XML)
Short Name: INVDRRSV_XML
DB package: INV_INVDRRSV_XMLP_PKG
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
Parameter Name SQL text Validation
Dynamic Precision Option
 
Number
Source Account Structure
 
Number
Category Structure
 
Number
Organization
 
Number
To
 
Categories From
 
Category Set Name
 
LOV Oracle
To
 
Sources From
 
Transaction Source Type
 
LOV Oracle
To
 
Items From
 
To
 
Date
Required Dates From
 
Date
Sort By
 
LOV Oracle
Display and Break on Category
 
LOV Oracle