ENG Engineering Change Order Detail

SELECT ecv.revised_item_sequence_id  revised_item_sequence_id2,
       lu1.meaning                   Action,
       ecv.component_item_id         component_item_id,
       null                C_RC_FLEXDATA,
       msi.description               Description,
       msi.primary_uom_code          UOM,
       ecv.component_yield_factor    Yield,
       old.operation_seq_num         Old_Op,
       ecv.operation_seq_num         New_Op,
       ecv.item_num                  Item_Seq,
       ecv.disable_date              Disable_Date,
       ROUND(ecv.component_quantity, :P_qty_precision)        Quantity,
       fl.meaning                    Item_Type,
       ecv.optional                  Optional,
       ecv.planning_factor           Planning_Factor,
       ecv.include_in_cost_rollup    Costed,
       lu5.meaning                   Supply_Type,
       ecv.supply_subinventory       Supply_Subinventory,
       ecv.supply_locator_id         supply_locator_id,
       null                C_SL_FLEXDATA,
       ecv.component_remarks         Comments,
       ecv.cancellation_date         Cancellation_Date,
       ecv.cancel_comments           Comments,
       ecv.check_atp                 Check_ATP,
       lu9.meaning                   Basis,
       ecv.mutually_exclusive_options   Mutually_Exclusive_Options,
       ecv.required_to_ship          Required_to_Ship,
       ecv.required_for_revenue      Required_for_Revenue,
       ecv.include_on_ship_docs      Include_on_Shipping_Documents,
       round(ecv.low_quantity, :p_qty_precision)    Min_Qty,
       round(ecv.high_quantity, :p_qty_precision)   Max_Qty,
       ecv.bill_sequence_id          bill_sequence_id,
       ecv.effectivity_date          effectivity_date,
       ecv.component_sequence_id     component_sequence_id,
       ecv.change_notice             change_notice4,
       ecv.quantity_related,
       msi.organization_id organization_id4, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_rc_flexfield', 'INV', 'MSTK', 101, msi.ORGANIZATION_ID, msi.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_RC_FLEXFIELD, 
	DECODE(mil.INVENTORY_LOCATION_ID, NULL, NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_sl_flexfield', 'INV', 'MTLL', 101, mil.ORGANIZATION_ID, mil.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE'))  C_SL_FLEXFIELD, 
	ENG_ENGRCNDR_XMLP_PKG.optional_dispformula(ecv.optional) Optional_disp, 
	ENG_ENGRCNDR_XMLP_PKG.costed_dispformula(ecv.include_in_cost_rollup) Costed_disp, 
	ENG_ENGRCNDR_XMLP_PKG.check_atp_dispformula(ecv.check_atp) Check_ATP_disp, 
	ENG_ENGRCNDR_XMLP_PKG.mutually_exclusive_dispformula(ecv.mutually_exclusive_options) Mutually_Exclusive_disp, 
	ENG_ENGRCNDR_XMLP_PKG.required_to_ship_dispformula(ecv.required_to_ship) Required_to_Ship_disp, 
	ENG_ENGRCNDR_XMLP_PKG.required_for_revenue_dispformu(ecv.required_for_revenue) Required_for_Revenue_disp, 
	ENG_ENGRCNDR_XMLP_PKG.include_on_ship_dispformula(ecv.include_on_ship_docs) Include_on_Ship_disp, 
	ENG_ENGRCNDR_XMLP_PKG.quantity_related_dispformula(ecv.quantity_related) quantity_related_disp
FROM   mfg_lookups               lu1,
       mfg_lookups               lu5,
       mfg_lookups               lu9,
       fnd_common_lookups         fl,
       eng_component_view        ecv,
       mtl_system_items          msi,
       mtl_item_locations        mil,
       bom_inventory_components  old
WHERE ecv.component_item_id = msi.inventory_item_id
  and  ecv.supply_locator_id = mil.inventory_location_id(+)
  and  decode(ecv.implementation_date,null,ecv.old_component_sequence_id,ecv.component_sequence_id)
                                                                        = old.component_sequence_id(+)  and  ecv.acd_type        = lu1.lookup_code(+) and
       lu1.lookup_type(+)     = 'ECG_ACTION'
  and  msi.item_type       = fl.lookup_code(+) and
       fl.lookup_type(+)   = 'ITEM_TYPE'
  and  ecv.wip_supply_type = lu5.lookup_code(+) and
       lu5.lookup_type(+)     = 'WIP_SUPPLY'
  and  ecv.so_basis        = lu9.lookup_code(+) and
       lu9.lookup_type(+)     ='BOM_SO_BASIS' 
 and ecv.revised_item_sequence_id=:revised_item_sequence_id 
 and msi.organization_id=:organization_id1
ORDER BY ecv.operation_seq_num, ecv.old_component_sequence_id