WIP Move Transaction

SELECT &P_FLEXDATA C_FLEXDATA_DETAIL
,      DECODE(:P_SORT,1,&P_FLEXDATA,'X') C_FLEXDATA
,      WT.TRANSACTION_DATE DATE_DETAIL
,      DECODE(:P_SORT,2,WT.TRANSACTION_DATE,SYSDATE)
,      WE.WIP_ENTITY_NAME
,      WL.LINE_CODE
,      WT.FM_OPERATION_SEQ_NUM
,      BSO1.OPERATION_CODE             FM_OPERATION_CODE
,      BD1.DEPARTMENT_CODE DEPARTMENT_CODE
,      ML1.MEANING MEANING
,      WT.TO_OPERATION_SEQ_NUM
,      BSO2.OPERATION_CODE             TO_OPERATION_CODE
,      BD2.DEPARTMENT_CODE DEPARTMENT_CODE1
,      ML2.MEANING MEANING1
,      WT.TRANSACTION_QUANTITY
,      WT.TRANSACTION_UOM
,      WT.PRIMARY_QUANTITY
,      WT.PRIMARY_UOM
,      DECODE(:P_SORT,1,NVL(WE.PRIMARY_ITEM_ID,-1),0) ITEM_ID
,      NVL(WE.PRIMARY_ITEM_ID,-1) ITEM_ID_DETAIL
,      MTR.REASON_NAME
,      WT.REFERENCE
,      DECODE(:P_SORT,1,MSI.DESCRIPTION,'X')
,      MSI.DESCRIPTION DESC_DETAIL,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_sort', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') C_FLEX_SORT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_assembly1', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_ASSEMBLY1, 
	DECODE(:P_SORT,2,'C_ASSEMBLY_DET','X')  C_ASSEMBLY_DET
FROM   MTL_SYSTEM_ITEMS MSI
,      BOM_DEPARTMENTS BD1
,      BOM_DEPARTMENTS BD2
,      BOM_STANDARD_OPERATIONS  BSO1
,      BOM_STANDARD_OPERATIONS  BSO2
,      MFG_LOOKUPS ML1
,      MFG_LOOKUPS ML2
,      WIP_ENTITIES WE
,      WIP_LINES WL
,      WIP_OPERATIONS  WO1
,      WIP_OPERATIONS  WO2
,      MTL_TRANSACTION_REASONS MTR
,      WIP_MOVE_TRANSACTIONS WT
WHERE  WT.ORGANIZATION_ID = :P_ORGANIZATION_ID 
&C_Limit_Dates
AND    NVL(BSO1.OPERATION_TYPE, 1) = 1
AND    NVL(BSO2.OPERATION_TYPE, 1) = 1
AND    BSO1.LINE_ID IS NULL
AND    BSO2.LINE_ID IS NULL
AND    BD1.DEPARTMENT_ID = WT.FM_DEPARTMENT_ID
AND    BD2.DEPARTMENT_ID = WT.TO_DEPARTMENT_ID
AND    WT.WIP_ENTITY_ID = WO1.WIP_ENTITY_ID
AND    WT.WIP_ENTITY_ID = WO2.WIP_ENTITY_ID
AND    WO1.ORGANIZATION_ID = :P_ORGANIZATION_ID
AND    WO2.ORGANIZATION_ID = :P_ORGANIZATION_ID
AND    BSO1.ORGANIZATION_ID (+) = :P_ORGANIZATION_ID
AND    BSO2.ORGANIZATION_ID (+) = :P_ORGANIZATION_ID
AND    WT.FM_OPERATION_SEQ_NUM = WO1.OPERATION_SEQ_NUM
AND    WT.TO_OPERATION_SEQ_NUM = WO2.OPERATION_SEQ_NUM
AND    WO1.STANDARD_OPERATION_ID = BSO1.STANDARD_OPERATION_ID (+)
AND    WO2.STANDARD_OPERATION_ID = BSO2.STANDARD_OPERATION_ID (+)
AND    NVL(WO1.REPETITIVE_SCHEDULE_ID, -999) = 
            (SELECT NVL(MIN(WMTA.REPETITIVE_SCHEDULE_ID), -999) 
             FROM WIP_MOVE_TXN_ALLOCATIONS WMTA
             WHERE WMTA.TRANSACTION_ID = WT.TRANSACTION_ID)
AND    NVL(WO2.REPETITIVE_SCHEDULE_ID, -999) =
            (SELECT NVL(MIN(WMTA.REPETITIVE_SCHEDULE_ID), -999)
             FROM WIP_MOVE_TXN_ALLOCATIONS WMTA
             WHERE WMTA.TRANSACTION_ID = WT.TRANSACTION_ID)
AND    ML1.LOOKUP_TYPE = 'WIP_INTRAOPERATION_STEP'
AND    ML1.LOOKUP_CODE = FM_INTRAOPERATION_STEP_TYPE	
AND    ML2.LOOKUP_TYPE = 'WIP_INTRAOPERATION_STEP'
AND    ML2.LOOKUP_CODE = TO_INTRAOPERATION_STEP_TYPE	
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    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 
--&C_Limit_Assemblies 
AND &P_FLEXWHERE 
/*ORDER BY  WT.TRANSACTION_DATE
,         WE.WIP_ENTITY_NAME
,         WL.LINE_CODE
,         WT.FM_OPERATION_SEQ_NUM
,         WT.TO_OPERATION_SEQ_NUM
,         WT.CREATION_DATE
,         WT.TRANSACTION_ID
*/
ORDER BY 4 ASC , WT.TRANSACTION_DATE , WE.WIP_ENTITY_NAME , WL.LINE_CODE , 
  WT.FM_OPERATION_SEQ_NUM , WT.TO_OPERATION_SEQ_NUM , WT.CREATION_DATE , 
  WT.TRANSACTION_ID