WIP Resource Transaction

SELECT &P_FLEXDATA C_FLEXDATA
,      DECODE(:P_SORT,1,&P_FLEXDATA,'X') C_FLEXDATA_MASTER
,      WT.TRANSACTION_DATE
,      DECODE(:P_SORT,2,WT.TRANSACTION_DATE,SYSDATE) DATE_MASTER
,      BR.RESOURCE_CODE
,      WE.WIP_ENTITY_NAME
,      WL.LINE_CODE
,      WT.OPERATION_SEQ_NUM
,      WT.RESOURCE_SEQ_NUM
,      BD.DEPARTMENT_CODE
,      WT.TRANSACTION_QUANTITY
,      WT.TRANSACTION_UOM
,      WT.PRIMARY_QUANTITY
,      WT.PRIMARY_UOM
,      CA.ACTIVITY
,      NVL(HRE.FULL_NAME,'  ')
,      DECODE(:P_SORT,3,NVL(HRE.FULL_NAME,'  '),'X') EMP_MASTER
,      NVL(WE.PRIMARY_ITEM_ID,-1) ITEM_ID
,      DECODE(:P_SORT,1,NVL(WE.PRIMARY_ITEM_ID,-1),1) ITEM_ID_MASTER
,      MTR.REASON_NAME
,      WT.REFERENCE
,      MSI.DESCRIPTION
,      DECODE(:P_SORT,1,MSI.DESCRIPTION,'X') DESC_MASTER, 
	NVL2(MSI.inventory_item_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_master_sort', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE'),NULL) C_FLEX_MASTER_SORT, 
	NVL2(MSI.inventory_item_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_assembly_master', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),NULL) C_ASSEMBLY_MASTER, 
	NVL2(MSI.inventory_item_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_assembly2', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),NULL) C_ASSEMBLY2
FROM   MTL_SYSTEM_ITEMS_VL MSI
,      BOM_RESOURCES BR
,      BOM_DEPARTMENTS BD
,      WIP_ENTITIES WE
,      WIP_LINES WL
,      CST_ACTIVITIES CA
,      PER_PEOPLE_F HRE
,      MTL_TRANSACTION_REASONS MTR
,      WIP_TRANSACTIONS WT
WHERE  WT.ORGANIZATION_ID = :P_ORGANIZATION_ID  
&C_Limit_Dates
AND    BR.RESOURCE_ID = WT.RESOURCE_ID
&C_Limit_Resource
AND    BD.DEPARTMENT_ID = WT.DEPARTMENT_ID
AND    WE.WIP_ENTITY_ID = WT.WIP_ENTITY_ID
AND    WE.ORGANIZATION_ID = :P_ORGANIZATION_ID 
&C_Limit_Jobs
AND    WL.LINE_ID(+) = WT.LINE_ID
AND    WL.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID 
&C_Limit_Lines
AND    CA.ACTIVITY_ID(+) = WT.ACTIVITY_ID 
&C_Limit_Activities
AND    HRE.PERSON_ID(+) = WT.EMPLOYEE_ID 
&C_Limit_Emp
AND    MTR.REASON_ID(+) = WT.REASON_ID 
&C_Limit_Reasons
AND    MSI.INVENTORY_ITEM_ID(+) = WE.PRIMARY_ITEM_ID
AND    MSI.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID 
AND   TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE(+) AND HRE.EFFECTIVE_END_DATE(+)
--&C_Limit_Assemblies 
and &P_FLEXWHERE 
ORDER BY C_FLEX_MASTER_SORT,17 ASC,5 ASC , 
/*to_date ( 
  to_char ( WT.TRANSACTION_DATE ) , 'DD-MON-RR HH24:MI' ) , */
  to_char ( WT.TRANSACTION_DATE ,'DD-MON-RR HH24:MI'),   --removed to_date to fix the bug 10273317
  WE.WIP_ENTITY_NAME , WL.LINE_CODE , WT.OPERATION_SEQ_NUM , 
  WT.RESOURCE_SEQ_NUM , WT.CREATION_DATE , WT.TRANSACTION_ID
/*ORDER BY  to_date(to_char (WT.TRANSACTION_DATE), 'DD-MON-RR HH24:MI')
,           WE.WIP_ENTITY_NAME
,           WL.LINE_CODE
,           WT.OPERATION_SEQ_NUM
,           WT.RESOURCE_SEQ_NUM
,           WT.CREATION_DATE
,           WT.TRANSACTION_ID*/