SELECT 1 query
,(select ORGANIZATION_NAME from INV_ORGANIZATION_DEFINITIONS_V
where organization_id=mtrh.organization_id
) ORGANIZATION_NAME1
, MMT.PICK_SLIP_NUMBER
,MTRL.TXN_SOURCE_ID
, esi_assemb.item_number ASSEMBLY
, TO_CHAR(wwob.PLANNED_COMPLETION_DATE,'DD-MON-YY') SCHEDULED_COMPLETION_DATE
, TO_CHAR(wwob.PLANNED_start_DATE,'DD-MON-YY') SCHEDULED_START_DATE
, wwob.PLANNED_START_QUANTITY START_QUANTITY
, wwob.WORK_ORDER_NUMBER WIP_ENTITY_NAME
-- , CASE WHEN INV_MO_PICK_SLIP.CP_WO_ORDER_NUMBER_P IS NULL THEN
-- NULL ELSE MTRL.reference_name END JOB_NAME
, mtrh.organization_id
, MTRH.REQUEST_NUMBER REQUEST_NUMBER
, MTRH.MOVE_ORDER_TYPE
, MTRL.LINE_NUMBER
, MTRL.LINE_ID
, MTRL.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_ID
, TO_NUMBER(NULL) PARENT_LINE_ID
, MMT.INVENTORY_ITEM_ID
, MSI.ITEM_NUMBER CF_ITEM --null C_ITEM_FLEXDAT
, MSI.DESCRIPTION ITEM_DESCRIPTION
, MMT.REVISION
, MMT.SUBINVENTORY_CODE FR_SUBINV
, DECODE(MMT.LOCATOR_ID, NULL, '', INV_PROJECT.GET_LOCSEGS(MMT.LOCATOR_ID, MMT.ORGANIZATION_ID )) FR_LOCATOR
, MMT.TRANSFER_SUBINVENTORY TO_SUBINV
, DECODE(MMT.TRANSFER_LOCATOR_ID, NULL, '', INV_PROJECT.GET_LOCSEGS(MMT.TRANSFER_LOCATOR_ID, MMT.ORGANIZATION_ID )) TO_LOCATOR
, MTRL.PJC_PROJECT_ID PROJECT_ID
, MTRL.PJC_TASK_ID TASK_ID
, DECODE(MMT.DISTRIBUTION_ACCOUNT_ID, '', '', null) C_TO_ACCOUNT_FLEXDAT
, CASE WHEN MTRL.REQUESTER_ID IS NULL
THEN PER.USERNAME
ELSE
(SELECT GLOBAL_NAME FROM INV_EMPLOYEES_CURRENT_V WHERE PERSON_ID = MTRL.REQUESTER_ID AND ROWNUM<2)
END AS REQUESTER
, MMT.TRANSACTION_REFERENCE REFERENCE
, MMT.TRANSACTION_UOM TRANSACTION_UOM
, INV_MO_PICK_SLIP.get_uom_name(MMT.TRANSACTION_UOM) UOM_NAME
, ABS(MMT.TRANSACTION_QUANTITY) TRANSACTION_QTY
,NVL(MTRL.QUANTITY_DELIVERED,0) REQ_QTY
,ABS(MMT.TRANSACTION_QUANTITY)-NVL(MTRL.QUANTITY_DELIVERED,0) SHORT_QTY
, (SELECT DROPPING_ORDER FROM INV_SECONDARY_INVENTORIES WHERE SECONDARY_INVENTORY_NAME = MMT.TRANSFER_SUBINVENTORY AND ORGANIZATION_ID = MMT.ORGANIZATION_ID) SUB_DROPPING_ORDER
, (SELECT DROPPING_ORDER FROM INV_ITEM_LOCATIONS WHERE INVENTORY_LOCATION_ID = MMT.TRANSFER_LOCATOR_ID AND ORGANIZATION_ID = MMT.ORGANIZATION_ID) LOC_DROPPING_ORDER
, MMT.SECONDARY_UOM_CODE SEC_UOM
, INV_MO_PICK_SLIP.get_uom_name(MMT.SECONDARY_UOM_CODE) SEC_UOM_NAME
, ABS(MMT.SECONDARY_TRANSACTION_QUANTITY) SEC_TRANSACTION_QTY
, INV_MO_PICK_SLIP.cf_sec_uomformula(MMT.INVENTORY_ITEM_ID, MMT.SECONDARY_UOM_CODE) CF_SEC_UOM
, INV_MO_PICK_SLIP.cf_sec_qtyformula(MMT.INVENTORY_ITEM_ID, ABS(MMT.SECONDARY_TRANSACTION_QUANTITY)) CF_SEC_QTY
, INV_MO_PICK_SLIP.cf_project_numberformula(MTRL.PJC_PROJECT_ID) CF_PROJECT_NUMBER
, INV_MO_PICK_SLIP.cf_task_numberformula(MTRL.PJC_TASK_ID,MTRL.PJC_PROJECT_ID) CF_TASK_NUMBER
, INV_MO_PICK_SLIP.cf_so_infoformula(LINE_ID, MOVE_ORDER_TYPE) CF_SO_INFO
, INV_MO_PICK_SLIP.CP_SO_ORDER_NUMBER_p CP_SO_ORDER_NUMBER
, INV_MO_PICK_SLIP.CP_SO_LINE_NUMBER_p CP_SO_LINE_NUMBER
, INV_MO_PICK_SLIP.CP_SO_DELIVERY_NAME_p CP_SO_DELIVERY_NAME
, INV_MO_PICK_SLIP.CP_WO_ORDER_NUMBER_P CP_WO_ORDER_NUMBER
, INV_MO_PICK_SLIP.CP_WO_OPERATION_SEQ_NUMBER_P CP_WO_OPERATION_SEQ_NUMBER
, INV_MO_PICK_SLIP.CP_WO_WORK_CENTER_CODE_P CP_WO_WORK_CENTER_CODE
, INV_MO_PICK_SLIP.CP_WO_WORK_AREA_CODE_P CP_WO_WORK_AREA_CODE
, 3 TASK_STATUS_ID --INV_PICK_SLIP_TASK_STATUS (1- UnAllocated, 2-Allocated, 3-Completed)
, INV_MO_PICK_SLIP.cf_task_statusformula(3) CF_TASK_STATUS
, MMT.PROJECT_ID INV_PROJECT_ID
, MMT.TASK_ID INV_TASK_ID
, INV_MO_PICK_SLIP.cf_project_numberformula(MMT.PROJECT_ID) INV_PROJECT_NUMBER
, INV_MO_PICK_SLIP.cf_task_numberformula(MMT.TASK_ID, MMT.PROJECT_ID) INV_TASK_NUMBER
,lot.L_LOT_NUMBER
,lot.L_LOT_qty LOT_TRANSACTION_QTY
FROM INV_MATERIAL_TXNS MMT --MTL_MATERIAL_TRANSACTIONS
, INV_TXN_REQUEST_HEADERS MTRH
, INV_TXN_REQUEST_LINES MTRL
, EGP_SYSTEM_ITEMS MSI
, PER_USERS PER --FND_USER
, wie_work_orders_b wwob
, egp_system_items_b esi_assemb
,(SELECT TRANSACTION_ID L_TRANSACTION_ID
, LOT_NUMBER L_LOT_NUMBER
, ABS(TRANSACTION_QUANTITY) L_LOT_QTY
, SERIAL_TRANSACTION_ID LS_SER_TXN_ID
FROM INV_TRANSACTION_LOT_NUMBERS
UNION ALL
SELECT TRANSACTION_TEMP_ID
, LOT_NUMBER
, ABS(TRANSACTION_QUANTITY)
, SERIAL_TRANSACTION_TEMP_ID
FROM INV_TRANSACTION_LOTS_TEMP) lot
WHERE 1=1 --MMT.ORGANIZATION_ID = :P_ORG_ID
AND MMT.MOVE_ORDER_LINE_ID = MTRL.LINE_ID
AND MTRL.HEADER_ID = MTRH.HEADER_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND PER.USERNAME = MTRL.CREATED_BY
AND NVL(MMT.TRANSACTION_QUANTITY, 0) < 0
AND MTRH.request_number in (:P_REQUEST_NUMBER)
AND wwob.work_order_number(+) = MTRL.reference_name
AND wwob.organization_id(+) = MTRH.organization_id
AND esi_assemb.organization_id (+)= wwob.organization_id
AND esi_assemb.inventory_item_id (+)= wwob.inventory_item_id
and MMT.TRANSACTION_ID=lot.L_TRANSACTION_ID(+)
--and MMT.SUBINVENTORY_CODE =CASE WHEN :P_FR_SUBINV ='NULL' THEN MMT.SUBINVENTORY_CODE ELSE :P_FR_SUBINV END
-- and MMT.TRANSFER_SUBINVENTORY =CASE WHEN :P_TO_SUBINV ='NULL' THEN MMT.TRANSFER_SUBINVENTORY ELSE :P_TO_SUBINV END
-- and trunc(MTRL.DATE_REQUIRED) between :p_date_from and :p_date_to
AND trunc(MTRL.DATE_REQUIRED) BETWEEN NVL (:p_date_from, trunc(MTRL.DATE_REQUIRED))
AND NVL (:p_date_to, trunc(MTRL.DATE_REQUIRED))
AND (:P_FR_SUBINV IS NULL or MTRL.FROM_SUBINVENTORY_CODE=:P_fr_SUBINV)
AND (:P_TO_SUBINV IS NULL or MTRL.TO_SUBINVENTORY_CODE=:P_TO_SUBINV)
UNION ALL
SELECT 2 query
,(select ORGANIZATION_NAME from INV_ORGANIZATION_DEFINITIONS_V
where organization_id=mtrh.organization_id
) ORGANIZATION_NAME1
, (SELECT PICK_SLIP_NUMBER FROM INV_MATERIAL_TXNS_TEMP WHERE TRANSACTION_TEMP_ID = NVL(MMTT.PARENT_LINE_ID, MMTT.TRANSACTION_TEMP_ID) )PICK_SLIP_NUMBER
,MTRL.TXN_SOURCE_ID
, esi_assemb.item_number ASSEMBLY
, to_char(wwob.PLANNED_COMPLETION_DATE,'DD-MON-YY') SCHEDULED_COMPLETION_DATE
, TO_CHAR(wwob.PLANNED_start_DATE,'DD-MON-YY') SCHEDULED_START_DATE
, wwob.PLANNED_START_QUANTITY START_QUANTITY
, wwob.WORK_ORDER_NUMBER WIP_ENTITY_NAME
, mtrh.organization_id
, MTRH.REQUEST_NUMBER REQUEST_NUMBER
, MTRH.MOVE_ORDER_TYPE
, MTRL.LINE_NUMBER
, MTRL.LINE_ID
, MTRL.TRANSACTION_SOURCE_TYPE_ID
, MMTT.TRANSACTION_TEMP_ID TRANSACTION_ID
, MMTT.PARENT_LINE_ID
, MMTT.INVENTORY_ITEM_ID
, MSI.ITEM_NUMBER CF_ITEM --null C_ITEM_FLEXDAT
, MSI.DESCRIPTION ITEM_DESCRIPTION
, MMTT.REVISION
, MMTT.SUBINVENTORY_CODE FR_SUBINV
, DECODE(MMTT.LOCATOR_ID, NULL, '', INV_PROJECT.GET_LOCSEGS(MMTT.LOCATOR_ID, MMTT.ORGANIZATION_ID )) FR_LOCATOR
, MMTT.TRANSFER_SUBINVENTORY TO_SUBINV
+, DECODE(MMTT.TRANSFER_TO_LOCATION, NULL, '', INV_PROJECT.GET_LOCSEGS(MMTT.TRANSFER_TO_LOCATION, MMTT.ORGANIZATION_ID )) TO_LOCATOR
, MTRL.PJC_PROJECT_ID PROJECT_ID
, MTRL.PJC_TASK_ID TAKS_ID
, DECODE(MMTT.DISTRIBUTION_ACCOUNT_ID, '', '', null) C_TO_ACCOUNT_FLEXDAT
, CASE WHEN MTRL.REQUESTER_ID IS NULL
THEN PER.USERNAME
ELSE
(SELECT GLOBAL_NAME FROM INV_EMPLOYEES_CURRENT_V WHERE PERSON_ID = MTRL.REQUESTER_ID AND ROWNUM<2)
END AS REQUESTER
, MMTT.TRANSACTION_REFERENCE REFERENCE
, MMTT.TRANSACTION_UOM TRANSACTION_UOM
, INV_MO_PICK_SLIP.get_uom_name(MMTT.TRANSACTION_UOM) UOM_NAME
, ABS(MMTT.TRANSACTION_QUANTITY) TRANSACTION_QTY
,nVl(MMTT.PRIMARY_QUANTITY,0) REQ_QTY
,ABS(MMTT.TRANSACTION_QUANTITY)-nVl(MMTT.PRIMARY_QUANTITY,0) SHORT_QTY
, (SELECT DROPPING_ORDER FROM INV_SECONDARY_INVENTORIES WHERE SECONDARY_INVENTORY_NAME = MMTT.TRANSFER_SUBINVENTORY AND ORGANIZATION_ID = MMTT.ORGANIZATION_ID) SUB_DROPPING_ORDER
, (SELECT DROPPING_ORDER FROM INV_ITEM_LOCATIONS WHERE INVENTORY_LOCATION_ID = MMTT.TRANSFER_TO_LOCATION AND ORGANIZATION_ID = MMTT.ORGANIZATION_ID) LOC_DROPPING_ORDER
, MMTT.SECONDARY_UOM_CODE SEC_UOM
, INV_MO_PICK_SLIP.get_uom_name(MMTT.SECONDARY_UOM_CODE) SEC_UOM_NAME
, ABS(MMTT.SECONDARY_TRANSACTION_QUANTITY) SEC_TRANSACTION_QTY
, INV_MO_PICK_SLIP.cf_sec_uomformula(MMTT.INVENTORY_ITEM_ID, MMTT.SECONDARY_UOM_CODE) CF_SEC_UOM
, INV_MO_PICK_SLIP.cf_sec_qtyformula(MMTT.INVENTORY_ITEM_ID, ABS(MMTT.SECONDARY_TRANSACTION_QUANTITY)) CF_SEC_QTY
, INV_MO_PICK_SLIP.cf_project_numberformula(MTRL.PJC_PROJECT_ID) CF_PROJECT_NUMBER
, INV_MO_PICK_SLIP.cf_task_numberformula(MTRL.PJC_TASK_ID, MTRL.PJC_PROJECT_ID) CF_TASK_NUMBER
, INV_MO_PICK_SLIP.cf_so_infoformula(MTRL.LINE_ID, MTRH.MOVE_ORDER_TYPE) CF_SO_INFO
, INV_MO_PICK_SLIP.CP_SO_ORDER_NUMBER_p CP_SO_ORDER_NUMBER
, INV_MO_PICK_SLIP.CP_SO_LINE_NUMBER_p CP_SO_LINE_NUMBER
, INV_MO_PICK_SLIP.CP_SO_DELIVERY_NAME_p CP_SO_DELIVERY_NAME
, INV_MO_PICK_SLIP.CP_WO_ORDER_NUMBER_P CP_WO_ORDER_NUMBER
, INV_MO_PICK_SLIP.CP_WO_OPERATION_SEQ_NUMBER_P CP_WO_OPERATION_SEQ_NUMBER
, INV_MO_PICK_SLIP.CP_WO_WORK_CENTER_CODE_P CP_WO_WORK_CENTER_CODE
, INV_MO_PICK_SLIP.CP_WO_WORK_AREA_CODE_P CP_WO_WORK_AREA_CODE
, 2 TASK_STATUS_ID --INV_PICK_SLIP_TASK_STATUS (1- UnAllocated, 2-Allocated, 3-Completed)
, INV_MO_PICK_SLIP.cf_task_statusformula(2) CF_TASK_STATUS
, MMTT.PROJECT_ID INV_PROJECT_ID
, MMTT.TASK_ID INV_TASK_ID
, INV_MO_PICK_SLIP.cf_project_numberformula(MMTT.PROJECT_ID) INV_PROJECT_NUMBER
, INV_MO_PICK_SLIP.cf_task_numberformula(MMTT.TASK_ID, MMTT.PROJECT_ID) INV_TASK_NUMBER
,lot.L_LOT_NUMBER
,lot.L_LOT_qty LOT_TRANSACTION_QTY
FROM INV_MATERIAL_TXNS_TEMP MMTT
, INV_TXN_REQUEST_HEADERS MTRH
, INV_TXN_REQUEST_LINES MTRL
, EGP_SYSTEM_ITEMS MSI
, PER_USERS PER --FND_USER
, wie_work_orders_b wwob
, egp_system_items_b esi_assemb
,(SELECT TRANSACTION_ID L_TRANSACTION_ID
, LOT_NUMBER L_LOT_NUMBER
, ABS(TRANSACTION_QUANTITY) L_LOT_QTY
, SERIAL_TRANSACTION_ID LS_SER_TXN_ID
FROM INV_TRANSACTION_LOT_NUMBERS
UNION ALL
SELECT TRANSACTION_TEMP_ID
, LOT_NUMBER
, ABS(TRANSACTION_QUANTITY)
, SERIAL_TRANSACTION_TEMP_ID
FROM INV_TRANSACTION_LOTS_TEMP) lot
WHERE 1=1 -- MMTT.ORGANIZATION_ID = :P_ORG_ID
AND MTRL.LINE_ID = MMTT.MOVE_ORDER_LINE_ID
AND MTRL.HEADER_ID = MTRH.HEADER_ID
AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND PER.USERNAME = MTRL.CREATED_BY --FND.USER_ID
AND NVL(MMTT.TRANSACTION_QUANTITY, 0) > 0
--and trunc(MTRL.DATE_REQUIRED) between :p_date_from and :p_date_to
AND trunc(MTRL.DATE_REQUIRED) BETWEEN NVL (:p_date_from, trunc(MTRL.DATE_REQUIRED))
AND NVL (:p_date_to, trunc(MTRL.DATE_REQUIRED))
--and MTRL.TO_SUBINVENTORY_CODE =mmtt.SUBINVENTORY_CODE
and MMTT.TRANSACTION_TEMP_ID=lot.L_TRANSACTION_ID(+)
AND wwob.work_order_number (+)= MTRL.reference_name
AND wwob.organization_id(+) = MTRH.organization_id
AND esi_assemb.organization_id(+) = wwob.organization_id
AND esi_assemb.inventory_item_id(+) = wwob.inventory_item_id
AND NOT EXISTS (SELECT 1 FROM INV_MATERIAL_TXNS_TEMP WHERE PARENT_LINE_ID = MMTT.TRANSACTION_TEMP_ID)
AND NVL(MMTT.PARENT_LINE_ID, MMTT.TRANSACTION_TEMP_ID) IN (SELECT NVL(MMTT.PARENT_LINE_ID, MMTT.TRANSACTION_TEMP_ID)
FROM INV_MATERIAL_TXNS_TEMP MMTT
, INV_TXN_REQUEST_LINES MTRL
, INV_TXN_REQUEST_HEADERS MTRH
WHERE MMTT.ORGANIZATION_ID =MMTT.ORGANIZATION_ID
AND MTRL.LINE_ID = MMTT.MOVE_ORDER_LINE_ID
AND MTRH.HEADER_ID = MTRL.HEADER_ID
)
AND MTRH.request_number in (:P_REQUEST_NUMBER)
-- AND MMTT.SUBINVENTORY_CODE in (:P_FR_SUBINV)
--AND MMTT.TRANSFER_SUBINVENTORY in (:P_TO_SUBINV)
AND (:P_FR_SUBINV IS NULL or MTRL.FROM_SUBINVENTORY_CODE=:P_fr_SUBINV)
AND (:P_TO_SUBINV IS NULL or MTRL.TO_SUBINVENTORY_CODE=:P_TO_SUBINV)
UNION ALL
SELECT 3 query
,(select ORGANIZATION_NAME from INV_ORGANIZATION_DEFINITIONS_V
where organization_id=mtrh.organization_id
) ORGANIZATION_NAME1
, MTRL.PICK_SLIP_NUMBER
,MTRL.TXN_SOURCE_ID
, null ASSEMBLY
, null SCHEDULED_COMPLETION_DATE
, null SCHEDULED_START_DATE
, null START_QUANTITY
, MTRL.reference_name WIP_ENTITY_NAME
, mtrh.organization_id
, MTRH.REQUEST_NUMBER REQUEST_NUMBER
, MTRH.MOVE_ORDER_TYPE
, MTRL.LINE_NUMBER
, MTRL.LINE_ID
, MTRL.TRANSACTION_SOURCE_TYPE_ID
, -99999 AS TRANSACTION_ID
, TO_NUMBER(NULL) PARENT_LINE_ID
, MTRL.INVENTORY_ITEM_ID
, MSI.ITEM_NUMBER CF_ITEM --null C_ITEM_FLEXDAT
, MSI.DESCRIPTION
, MTRL.REVISION
, MTRL.FROM_SUBINVENTORY_CODE FR_SUBINV
, DECODE(MTRL.FROM_LOCATOR_ID, NULL, '', INV_PROJECT.GET_LOCSEGS(MTRL.FROM_LOCATOR_ID, mtrl.ORGANIZATION_ID )) FR_LOCATOR
, MTRL.TO_SUBINVENTORY_CODE TO_SUBINV
, DECODE(MTRL.TO_LOCATOR_ID, NULL, '', INV_PROJECT.GET_LOCSEGS(MTRL.TO_LOCATOR_ID, mtrl.ORGANIZATION_ID )) TO_LOCATOR
, MTRL.PJC_PROJECT_ID PROJECT_ID
, MTRL.PJC_TASK_ID TAKS_ID
, DECODE(MTRL.TO_ACCOUNT_ID, '', '', null) C_TO_ACCOUNT_FLEXDAT
, CASE WHEN MTRL.REQUESTER_ID IS NULL
THEN PER.USERNAME
ELSE
(SELECT GLOBAL_NAME FROM INV_EMPLOYEES_CURRENT_V WHERE PERSON_ID = MTRL.REQUESTER_ID AND ROWNUM<2)
END AS REQUESTER
, MTRL.REFERENCE_NAME REFERENCE
, MTRL.UOM_CODE TRANSACTION_UOM
, INV_MO_PICK_SLIP.get_uom_name(MTRL.UOM_CODE) UOM_NAME
, ABS(NVL(MTRL.QUANTITY, 0) - NVL(MTRL.QUANTITY_DETAILED, 0)) TRANSACTION_QTY
,nVl(ABS(NVL(MTRL.QUANTITY, 0) - NVL(MTRL.QUANTITY_DETAILED, 0)),0) REQ_QTY
,ABS(MTRL.QUANTITY)-NVL(ABS(NVL(MTRL.QUANTITY, 0) - NVL(MTRL.QUANTITY_DETAILED, 0)),0) SHORT_QTY
, (SELECT DROPPING_ORDER FROM INV_SECONDARY_INVENTORIES WHERE SECONDARY_INVENTORY_NAME = MTRL.TO_SUBINVENTORY_CODE AND ORGANIZATION_ID = MTRL.ORGANIZATION_ID) SUB_DROPPING_ORDER
, (SELECT DROPPING_ORDER FROM INV_ITEM_LOCATIONS WHERE INVENTORY_LOCATION_ID = MTRL.TO_LOCATOR_ID AND ORGANIZATION_ID = MTRL.ORGANIZATION_ID) LOC_DROPPING_ORDER
, MTRL.SECONDARY_UOM_CODE SEC_UOM
, INV_MO_PICK_SLIP.get_uom_name(MTRL.SECONDARY_UOM_CODE) SEC_UOM_NAME
, ABS(NVL(MTRL.SECONDARY_QUANTITY, 0) - NVL(MTRL.SECONDARY_QUANTITY_DETAILED, 0)) SEC_TRANSACTION_QTY
, INV_MO_PICK_SLIP.cf_sec_uomformula(MTRL.INVENTORY_ITEM_ID, MTRL.SECONDARY_UOM_CODE) CF_SEC_UOM
, INV_MO_PICK_SLIP.cf_sec_qtyformula(MTRL.INVENTORY_ITEM_ID, ABS(NVL(MTRL.SECONDARY_QUANTITY, 0) - NVL(MTRL.SECONDARY_QUANTITY_DETAILED, 0))) CF_SEC_QTY
, INV_MO_PICK_SLIP.cf_project_numberformula(MTRL.PJC_PROJECT_ID) CF_PROJECT_NUMBER
, INV_MO_PICK_SLIP.cf_task_numberformula(MTRL.PJC_TASK_ID, MTRL.PJC_PROJECT_ID) CF_TASK_NUMBER
, INV_MO_PICK_SLIP.cf_so_infoformula(LINE_ID, MOVE_ORDER_TYPE) CF_SO_INFO
, INV_MO_PICK_SLIP.CP_SO_ORDER_NUMBER_p CP_SO_ORDER_NUMBER
, INV_MO_PICK_SLIP.CP_SO_LINE_NUMBER_p CP_SO_LINE_NUMBER
, INV_MO_PICK_SLIP.CP_SO_DELIVERY_NAME_p CP_SO_DELIVERY_NAME
, INV_MO_PICK_SLIP.CP_WO_ORDER_NUMBER_P CP_WO_ORDER_NUMBER
, INV_MO_PICK_SLIP.CP_WO_OPERATION_SEQ_NUMBER_P CP_WO_OPERATION_SEQ_NUMBER
, INV_MO_PICK_SLIP.CP_WO_WORK_CENTER_CODE_P CP_WO_WORK_CENTER_CODE
, INV_MO_PICK_SLIP.CP_WO_WORK_AREA_CODE_P CP_WO_WORK_AREA_CODE
, 1 TASK_STATUS_ID --INV_PICK_SLIP_TASK_STATUS (1- UnAllocated, 2-Allocated, 3-Completed)
, INV_MO_PICK_SLIP.cf_task_statusformula(1) CF_TASK_STATUS
, MTRL.PROJECT_ID INV_PROJECT_ID
, MTRL.TASK_ID INV_TASK_ID
, INV_MO_PICK_SLIP.cf_project_numberformula(MTRL.PROJECT_ID) INV_PROJECT_NUMBER
, INV_MO_PICK_SLIP.cf_task_numberformula(MTRL.TASK_ID, MTRL.PROJECT_ID) INV_TASK_NUMBER
,null L_LOT_NUMBER
,null LOT_TRANSACTION_QTY
FROM INV_TXN_REQUEST_HEADERS MTRH
, INV_TXN_REQUEST_LINES MTRL
, EGP_SYSTEM_ITEMS MSI
, PER_USERS PER --FND_USER
--, wie_work_orders_b wwob
--, egp_system_items_b esi_assemb
/* ,(SELECT TRANSACTION_ID L_TRANSACTION_ID
, LOT_NUMBER L_LOT_NUMBER
, ABS(TRANSACTION_QUANTITY) L_LOT_QTY
, SERIAL_TRANSACTION_ID LS_SER_TXN_ID
FROM INV_TRANSACTION_LOT_NUMBERS
UNION ALL
SELECT TRANSACTION_TEMP_ID
, LOT_NUMBER
, ABS(TRANSACTION_QUANTITY)
, SERIAL_TRANSACTION_TEMP_ID
FROM INV_TRANSACTION_LOTS_TEMP) lot*/
WHERE 1=1 --MTRL.ORGANIZATION_ID = :P_ORG_ID
AND MTRL.HEADER_ID = MTRH.HEADER_ID
AND MSI.INVENTORY_ITEM_ID = MTRL.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MTRL.ORGANIZATION_ID
AND PER.USERNAME = MTRL.CREATED_BY --FND.USER_ID
AND NVL(MTRL.QUANTITY, 0) > NVL(MTRL.QUANTITY_DETAILED, 0)
-- and -99999 =lot.L_TRANSACTION_ID(+)
-- AND MTRL.LINE_STATUS IN (3, 7)
AND MTRH.request_number in (:P_REQUEST_NUMBER)
-- AND wwob.work_order_number(+) = MTRL.reference_name
-- AND wwob.organization_id(+) = MTRH.organization_id
-- AND esi_assemb.organization_id = wwob.organization_id
-- AND esi_assemb.inventory_item_id = wwob.inventory_item_id
AND trunc(MTRL.DATE_REQUIRED) BETWEEN NVL (:p_date_from, trunc(MTRL.DATE_REQUIRED))
AND NVL (:p_date_to, trunc(MTRL.DATE_REQUIRED))
AND (:P_FR_SUBINV IS NULL or MTRL.FROM_SUBINVENTORY_CODE=:P_fr_SUBINV)
AND (:P_TO_SUBINV IS NULL or MTRL.TO_SUBINVENTORY_CODE=:P_TO_SUBINV)
ORDER BY 1, 8, 29, 30, 27, 28, 2, 3, 4, 5, 6, 7, 9, 31, 11, 12, 13, 14, 15, 16, 17, 18, 32, 20, 21, 22, 23
No comments:
Post a Comment