[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(1)
into l_row_count
from mtl_material_transactions_temp
where transaction_header_id = header_id
and process_flag = 'Y'
and transaction_status = TS_PROCESS
and rownum < 2;
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET PRIMARY_QUANTITY = ROUND(PRIMARY_QUANTITY,5),
TRANSACTION_QUANTITY = ROUND(TRANSACTION_QUANTITY,5)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3;
SELECT MMTT.*,ROWID
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
WHERE TRANSACTION_HEADER_ID = headerID
AND PROCESS_FLAG='Y'
AND TRANSACTION_STATUS = TS_PROCESS
ORDER BY ORGANIZATION_ID,INVENTORY_ITEM_ID,REVISION,
SUBINVENTORY_CODE,LOCATOR_ID;
errupdate(l_txnrecord.rowid);
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
ACCT_PERIOD_ID = l_openAcctPeriod
WHERE ROWID = l_txnrecord.rowid;
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
TRANSACTION_STATUS = 1,
LOCK_FLAG = 'N',
ERROR_CODE = substr(err_code,1,240),
ERROR_EXPLANATION = substr(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND (NOT (TRANSACTION_SOURCE_TYPE_ID IN (13, 2, 4)
AND TRANSACTION_ACTION_ID IN (2, 28, 1)
AND TRANSACTION_TYPE_ID not in (33)));
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
TRANSACTION_STATUS = 1,
LOCK_FLAG = 'N',
ERROR_CODE = substr(err_code,1,240),
ERROR_EXPLANATION = substr(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND NOT EXISTS (
SELECT NULL
FROM ORG_ORGANIZATION_DEFINITIONS OOD
WHERE OOD.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND NVL(OOD.DISABLE_DATE, SYSDATE + 1) > SYSDATE);
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
TRANSACTION_STATUS = 1,
LOCK_FLAG = 'N',
ERROR_CODE = substr(err_code,1,240),
ERROR_EXPLANATION = substr(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND TRANSACTION_ACTION_ID in (3,21)
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND (NOT EXISTS (
SELECT NULL
FROM ORG_ORGANIZATION_DEFINITIONS OOD
WHERE OOD.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION
AND NVL(OOD.DISABLE_DATE, SYSDATE + 1) > SYSDATE)
OR NOT EXISTS (
SELECT NULL
FROM MTL_INTERORG_PARAMETERS MIP
WHERE MIP.TO_ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION
AND MIP.FROM_ORGANIZATION_ID = MMTT.ORGANIZATION_ID));
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
TRANSACTION_STATUS = 1,
LOCK_FLAG = 'N',
ERROR_CODE = substr(err_code,1,240),
ERROR_EXPLANATION = substr(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND ((INVENTORY_ITEM_ID IS NOT NULL
AND (TRANSACTION_ACTION_ID NOT IN (1, 27, 33, 34)
OR TRANSACTION_SOURCE_TYPE_ID <> 5)) OR
(TRANSACTION_ACTION_ID <> 24
AND NVL(SHIPPABLE_FLAG,'Y') = 'Y'))
AND NOT EXISTS (
SELECT NULL
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_FLAG = 'Y');
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND TRANSACTION_ACTION_ID = 24
AND NOT EXISTS (
SELECT NULL
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_FLAG = 'Y'
AND MSI.INVENTORY_ASSET_FLAG = 'Y'
AND MSI.COSTING_ENABLED_FLAG = 'Y');
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_ACTION_ID NOT IN (24,33,34)
AND NOT EXISTS (
SELECT NULL
FROM MTL_ITEM_REVISIONS MIR,
MTL_SYSTEM_ITEMS MSI
WHERE MSI.REVISION_QTY_CONTROL_CODE = 2
AND MIR.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MIR.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MIR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIR.REVISION = MMTT.REVISION
UNION
SELECT NULL
FROM MTL_SYSTEM_ITEMS ITM
WHERE ITM.REVISION_QTY_CONTROL_CODE = 1
AND MMTT.REVISION IS NULL
AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND ITM.ORGANIZATION_ID = MMTT.ORGANIZATION_ID);
/* UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND TRANSACTION_ACTION_ID = 3
AND PROCESS_FLAG = 1
AND NVL(SHIPPABLE_FLAG,'Y') = 'Y'
AND NOT EXISTS (
SELECT NULL
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MTI.TRANSFER_ORGANIZATION
AND MSI.MTL_TRANSACTIONS_ENABLED_FLAG = 'Y'); */
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND TRANSACTION_ACTION_ID = 3
AND PROCESS_FLAG = 'Y'
AND NVL(SHIPPABLE_FLAG,'Y') = 'Y'
AND NOT EXISTS (
SELECT NULL
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION
AND MSI.MTL_TRANSACTIONS_ENABLED_FLAG = 'Y');
/* UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_ACTION_ID = 3
AND NOT EXISTS (
SELECT NULL
FROM MTL_ITEM_REVISIONS MIR,
MTL_SYSTEM_ITEMS MSI
WHERE MSI.REVISION_QTY_CONTROL_CODE = 2
AND MIR.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MIR.ORGANIZATION_ID = MTI.TRANSFER_ORGANIZATION
AND MIR.REVISION = MTI.REVISION
AND MSI.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MTI.TRANSFER_ORGANIZATION
AND MIR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
UNION
SELECT NULL
FROM MTL_SYSTEM_ITEMS ITM
WHERE ITM.REVISION_QTY_CONTROL_CODE = 1
AND MTI.REVISION IS NULL
AND ITM.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID
AND ITM.ORGANIZATION_ID = MTI.TRANSFER_ORGANIZATION); */
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_ACTION_ID = 3
AND NOT EXISTS (
SELECT NULL
FROM MTL_ITEM_REVISIONS MIR,
MTL_SYSTEM_ITEMS MSI
WHERE MSI.REVISION_QTY_CONTROL_CODE = 2
AND MIR.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MIR.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION
AND MIR.REVISION = MMTT.REVISION
AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION
AND MIR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
UNION
SELECT NULL
FROM MTL_SYSTEM_ITEMS ITM
WHERE ITM.REVISION_QTY_CONTROL_CODE = 1
-- AND MTI.REVISION IS NULL not required as per bug 3285134
AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND ITM.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION);
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
TRANSACTION_STATUS = 1,
LOCK_FLAG = 'N',
ERROR_CODE = substr(err_code,1,240),
ERROR_EXPLANATION = substr(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND TRANSACTION_ACTION_ID NOT IN (24, 30) /* CFM Scrap Transactions */
AND (NVL(SHIPPABLE_FLAG,'Y') = 'Y'
AND NOT EXISTS (
SELECT NULL
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MSI.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
AND NVL(DISABLE_DATE,SYSDATE+1) >= SYSDATE)
OR (SHIPPABLE_FLAG = 'N'
AND SUBINVENTORY_CODE IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MSI.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE)));
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND SUBINVENTORY_CODE IS NOT NULL
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND NOT EXISTS (
SELECT NULL
FROM MTL_ITEM_SUB_INVENTORIES MIS,
MTL_SYSTEM_ITEMS MSI
WHERE MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
AND MIS.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MIS.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MIS.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIS.SECONDARY_INVENTORY = MMTT.SUBINVENTORY_CODE
UNION
SELECT NULL
FROM MTL_SYSTEM_ITEMS ITM
WHERE ITM.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND ITM.RESTRICT_SUBINVENTORIES_CODE = 2);
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND LOCATOR_ID IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM MTL_ITEM_LOCATIONS MIL
WHERE MIL.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MIL.SUBINVENTORY_CODE = MMTT.SUBINVENTORY_CODE
AND MIL.INVENTORY_LOCATION_ID = MMTT.LOCATOR_ID
AND NVL(DISABLE_DATE,SYSDATE+1) >= SYSDATE);
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND LOCATOR_ID IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM MTL_SECONDARY_LOCATORS MSL,
MTL_SYSTEM_ITEMS MSI
WHERE MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MSI.RESTRICT_LOCATORS_CODE = 1
AND MSL.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MSL.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MSL.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSL.SUBINVENTORY_CODE = MMTT.SUBINVENTORY_CODE
AND MSL.SECONDARY_LOCATOR = MMTT.LOCATOR_ID
UNION
SELECT NULL
FROM MTL_SYSTEM_ITEMS ITM
WHERE ITM.RESTRICT_LOCATORS_CODE = 2
AND ITM.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID);
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
TRANSACTION_STATUS = 1,
LOCK_FLAG = 'N',
ERROR_CODE = substr(err_code,1,240),
ERROR_EXPLANATION = substr(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND (TRANSACTION_ACTION_ID IN (2,3,21)
AND TRANSFER_SUBINVENTORY IS NOT NULL)
AND ((NVL(SHIPPABLE_FLAG,'Y') = 'Y'
AND NOT EXISTS (
SELECT NULL
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.ORGANIZATION_ID =
DECODE(MMTT.TRANSACTION_ACTION_ID,2,
MMTT.ORGANIZATION_ID,MMTT.TRANSFER_ORGANIZATION)
AND MSI.SECONDARY_INVENTORY_NAME = MMTT.TRANSFER_SUBINVENTORY
AND NVL(MSI.DISABLE_DATE,SYSDATE+1) > SYSDATE)))
OR (SHIPPABLE_FLAG = 'N'
AND TRANSFER_SUBINVENTORY IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.ORGANIZATION_ID =
DECODE(MMTT.TRANSACTION_ACTION_ID,3,
MMTT.TRANSFER_ORGANIZATION,21,
MMTT.TRANSFER_ORGANIZATION,MMTT.ORGANIZATION_ID)
AND MSI.SECONDARY_INVENTORY_NAME =
MMTT.TRANSFER_SUBINVENTORY));
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND TRANSFER_SUBINVENTORY IS NOT NULL
AND TRANSACTION_ACTION_ID in (2,21,3)
AND NOT EXISTS (
SELECT NULL
FROM MTL_ITEM_SUB_INVENTORIES MIS,
MTL_SYSTEM_ITEMS MSI
WHERE MSI.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID, 2,
MMTT.ORGANIZATION_ID, MMTT.TRANSFER_ORGANIZATION)
AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
AND MIS.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID, 2,
MMTT.ORGANIZATION_ID, MMTT.TRANSFER_ORGANIZATION)
AND MIS.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MIS.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIS.SECONDARY_INVENTORY = MMTT.TRANSFER_SUBINVENTORY
UNION
SELECT NULL
FROM MTL_SYSTEM_ITEMS ITM
WHERE ITM.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID, 2,
MMTT.ORGANIZATION_ID, MMTT.TRANSFER_ORGANIZATION)
AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND ITM.RESTRICT_SUBINVENTORIES_CODE = 2);
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND TRANSACTION_ACTION_ID IN (2,3)
AND TRANSFER_TO_LOCATION IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM MTL_ITEM_LOCATIONS MIL
WHERE MIL.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID,3,
MMTT.TRANSFER_ORGANIZATION,MMTT.ORGANIZATION_ID)
AND MIL.SUBINVENTORY_CODE = MMTT.TRANSFER_SUBINVENTORY
AND MIL.INVENTORY_LOCATION_ID = MMTT.TRANSFER_TO_LOCATION);
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND TRANSACTION_ACTION_ID in (2,21,3)
AND TRANSFER_TO_LOCATION IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM MTL_SECONDARY_LOCATORS MSL,
MTL_SYSTEM_ITEMS MSI
WHERE MSI.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID, 2,
MMTT.ORGANIZATION_ID, MMTT.TRANSFER_ORGANIZATION)
AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MSI.RESTRICT_LOCATORS_CODE = 1
AND MSL.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID, 2,
MMTT.ORGANIZATION_ID, MMTT.TRANSFER_ORGANIZATION)
AND MSL.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MSL.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSL.SUBINVENTORY_CODE = MMTT.TRANSFER_SUBINVENTORY
AND MSL.SECONDARY_LOCATOR = MMTT.TRANSFER_TO_LOCATION
UNION
SELECT NULL
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID, 2,
MMTT.ORGANIZATION_ID, MMTT.TRANSFER_ORGANIZATION)
AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MSI.RESTRICT_LOCATORS_CODE = 2);
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
TRANSACTION_STATUS = 1,
LOCK_FLAG = 'N',
ERROR_CODE = substr(err_code,1,240),
ERROR_EXPLANATION = substr(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND ((TRANSACTION_SOURCE_TYPE_ID IN (3, 6, 13 )) OR
(TRANSACTION_SOURCE_TYPE_ID > 100 ) )
AND TRANSACTION_ACTION_ID IN (1, 27 )
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND EXISTS (
SELECT NULL
FROM MTL_TRANSACTION_TYPES MTTY
WHERE MTTY.TRANSACTION_TYPE_ID = MMTT.TRANSACTION_TYPE_ID
AND MTTY.TYPE_CLASS = 1 )
AND NOT EXISTS (
SELECT NULL
FROM pa_projects_expend_v prj1
WHERE prj1.project_id = mmtt.source_project_id ) ;
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
TRANSACTION_STATUS = 1,
LOCK_FLAG = 'N',
ERROR_CODE = substr(err_code,1,240),
ERROR_EXPLANATION = substr(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND ((TRANSACTION_SOURCE_TYPE_ID IN (3, 6, 13 )) OR
(TRANSACTION_SOURCE_TYPE_ID > 100 ) )
AND TRANSACTION_ACTION_ID IN (1, 27 )
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND EXISTS (
SELECT NULL
FROM MTL_TRANSACTION_TYPES MTTY
WHERE MTTY.TRANSACTION_TYPE_ID = MMTT.TRANSACTION_TYPE_ID
AND MTTY.TYPE_CLASS = 1 )
AND NOT EXISTS (
SELECT NULL
FROM PA_TASKS_LOWEST_V TSK
WHERE TSK.PROJECT_ID = MMTT.SOURCE_PROJECT_ID AND
TSK.TASK_ID = MMTT.SOURCE_TASK_ID );
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
TRANSACTION_STATUS = 1,
LOCK_FLAG = 'N',
ERROR_CODE = substr(err_code,1,240),
ERROR_EXPLANATION = substr(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND TRANSACTION_ACTION_ID = 24
AND TRANSACTION_SOURCE_TYPE_ID = 13
AND COST_GROUP_ID IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM CST_COST_GROUPS CCG
WHERE CCG.COST_GROUP_ID = MMTT.COST_GROUP_ID
AND NVL(CCG.ORGANIZATION_ID, MMTT.ORGANIZATION_ID) = MMTT.ORGANIZATION_ID
AND TRUNC(NVL(CCG.DISABLE_DATE,SYSDATE+1)) >= TRUNC(SYSDATE) ) ;
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
TRANSACTION_STATUS = 1,
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND ((TRANSACTION_SOURCE_TYPE_ID IN (3, 6, 13 )) OR
(TRANSACTION_SOURCE_TYPE_ID > 100 ) )
AND TRANSACTION_ACTION_ID IN (1, 27 )
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND EXISTS (
SELECT NULL
FROM MTL_TRANSACTION_TYPES MTTY
WHERE MTTY.TRANSACTION_TYPE_ID = MMTT.TRANSACTION_TYPE_ID
AND MTTY.TYPE_CLASS = 1 )
AND NOT EXISTS (
SELECT NULL
FROM CST_PROJ_EXP_TYPES_VAL_V CET
WHERE CET.EXPENDITURE_TYPE = MMTT.EXPENDITURE_TYPE
AND CET.COST_ELEMENT_ID = 1
AND TRUNC(MMTT.TRANSACTION_DATE) >= CET.SYS_LINK_START_DATE
AND TRUNC(MMTT.TRANSACTION_DATE) <= NVL(SYS_LINK_END_DATE,
MMTT.TRANSACTION_DATE + 1)
AND TRUNC(MMTT.TRANSACTION_DATE) >= CET.EXP_TYPE_START_DATE
AND TRUNC(MMTT.TRANSACTION_DATE) >= NVL(EXP_TYPE_END_DATE,
MMTT.TRANSACTION_DATE+1)) ;
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
TRANSACTION_STATUS = 1,
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND ((TRANSACTION_SOURCE_TYPE_ID IN (3, 6, 13 )) OR
(TRANSACTION_SOURCE_TYPE_ID > 100 ) )
AND TRANSACTION_ACTION_ID IN (1, 27 )
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND EXISTS (
SELECT NULL
FROM MTL_TRANSACTION_TYPES MTTY
WHERE MTTY.TRANSACTION_TYPE_ID = MMTT.TRANSACTION_TYPE_ID
AND MTTY.TYPE_CLASS = 1 )
AND MMTT.EXPENDITURE_TYPE IS NOT NULL ;
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
TRANSACTION_STATUS = 1,
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND ((TRANSACTION_SOURCE_TYPE_ID IN (3, 6, 13 )) OR
(TRANSACTION_SOURCE_TYPE_ID > 100 ) )
AND TRANSACTION_ACTION_ID IN (1, 27 )
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND EXISTS (
SELECT NULL
FROM MTL_TRANSACTION_TYPES MTTY
WHERE MTTY.TRANSACTION_TYPE_ID = MMTT.TRANSACTION_TYPE_ID
AND MTTY.TYPE_CLASS = 1 )
AND NOT EXISTS (
SELECT NULL
FROM PA_ORGANIZATIONS_EXPEND_V POE
WHERE POE.ORGANIZATION_ID = MMTT.PA_EXPENDITURE_ORG_ID
AND TRUNC(SYSDATE) BETWEEN POE.DATE_FROM
AND NVL(POE.DATE_TO, TRUNC(SYSDATE)));
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
TRANSACTION_STATUS = 1,
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND INVENTORY_ITEM_ID IS NOT NULL
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND NOT EXISTS (
SELECT NULL
FROM MTL_ITEM_UOMS_VIEW MIUV
WHERE MIUV.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MIUV.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MIUV.UOM_CODE = MMTT.TRANSACTION_UOM);
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND TRANSACTION_ACTION_ID = 3
AND EXISTS (
SELECT NULL
FROM MTL_SYSTEM_ITEMS MS1,
MTL_SYSTEM_ITEMS MS2
WHERE MS1.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MS1.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MS2.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MS2.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION
AND ((MS1.LOT_CONTROL_CODE = 1 AND
MS2.LOT_CONTROL_CODE = 2)
OR (MS1.SERIAL_NUMBER_CONTROL_CODE IN (1,6)
AND MS2.SERIAL_NUMBER_CONTROL_CODE IN (2,3,5))
OR (MS1.REVISION_QTY_CONTROL_CODE = 1 AND
MS2.REVISION_QTY_CONTROL_CODE = 2)));
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND TRANSACTION_SOURCE_TYPE_ID in (2,8)
AND NOT EXISTS (
SELECT NULL
FROM MTL_SALES_ORDERS MSO
WHERE MSO.SALES_ORDER_ID = MMTT.TRANSACTION_SOURCE_ID
AND NVL(START_DATE_ACTIVE, SYSDATE - 1) <= SYSDATE
AND NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE
AND ENABLED_FLAG = 'Y');
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND TRANSACTION_SOURCE_TYPE_ID = 3
AND NOT EXISTS (
SELECT NULL
FROM GL_CODE_COMBINATIONS GCC,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE GCC.CODE_COMBINATION_ID = MMTT.TRANSACTION_SOURCE_ID
AND GCC.CHART_OF_ACCOUNTS_ID = OOD.CHART_OF_ACCOUNTS_ID
AND OOD.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND GCC.ENABLED_FLAG = 'Y'
AND trunc(NVL(GCC.START_DATE_ACTIVE, SYSDATE - 1)) <= trunc(SYSDATE)
AND trunc(NVL(GCC.END_DATE_ACTIVE, SYSDATE + 1)) > trunc(SYSDATE));
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND TRANSACTION_SOURCE_TYPE_ID = 6
AND NOT EXISTS (
SELECT NULL
FROM MTL_GENERIC_DISPOSITIONS MGD
WHERE MGD.DISPOSITION_ID = MMTT.TRANSACTION_SOURCE_ID
AND MGD.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MGD.ENABLED_FLAG = 'Y'
AND NVL(MGD.START_DATE_ACTIVE, SYSDATE - 1) <= SYSDATE
AND NVL(MGD.END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE );
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND REASON_ID IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM MTL_TRANSACTION_REASONS MTR
WHERE MTR.REASON_ID = MMTT.REASON_ID
AND NVL(MTR.DISABLE_DATE, SYSDATE + 1) > SYSDATE);
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND TRANSACTION_ACTION_ID in (3,21)
AND FREIGHT_CODE IS NOT NULL
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND NOT EXISTS (
SELECT NULL
FROM ORG_FREIGHT FR
WHERE FR.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND FR.FREIGHT_CODE = MMTT.FREIGHT_CODE
AND NVL(FR.DISABLE_DATE, SYSDATE + 1) > SYSDATE);
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE TRANSACTION_HEADER_ID = header_id
AND TRANSACTION_ACTION_ID in (3,21)
AND TRANSPORTATION_ACCOUNT IS NOT NULL
AND PROCESS_FLAG = 'Y'
AND TRANSACTION_STATUS = 3
AND NOT EXISTS (
SELECT NULL
FROM ORG_FREIGHT FR
WHERE FR.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND FR.FREIGHT_CODE = MMTT.FREIGHT_CODE
AND FR.DISTRIBUTION_ACCOUNT = MMTT.TRANSPORTATION_ACCOUNT
AND NVL(FR.DISABLE_DATE, SYSDATE + 1) > SYSDATE);
SELECT LOT_NUMBER,
TRANSACTION_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID,
fnd_date.date_to_canonical(LOT_EXPIRATION_DATE) LOT_EXPIRATION_DATE,
ROWID
FROM MTL_TRANSACTION_LOTS_TEMP
WHERE TRANSACTION_TEMP_ID = txnTempID;
DELETE FROM MTL_TRANSACTION_LOTS_TEMP
WHERE TRANSACTION_temp_id = txnrec.transaction_temp_id;
errupdate(txnrec.rowid);
SELECT 1
INTO l_dummy
FROM MTL_SERIAL_NUMBERS_TEMP
WHERE TRANSACTION_TEMP_ID = txnrec.transaction_temp_id
AND rownum < 2;
errupdate(txnrec.rowid);
DELETE FROM MTL_SERIAL_NUMBERS_TEMP
WHERE TRANSACTION_TEMP_ID = txnrec.transaction_temp_id;
errupdate(txnrec.rowid);
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
TRANSACTION_STATUS = 1,
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE ROWID = txnrec.rowid
AND ABS(TRANSACTION_QUANTITY) <>
(SELECT ABS(SUM(TRANSACTION_QUANTITY))
FROM MTL_TRANSACTION_LOTS_TEMP MTLT
WHERE MTLT.TRANSACTION_TEMP_ID = txnrec.transaction_temp_id);
UPDATE MTL_TRANSACTION_LOTS_TEMP MTLT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_APPLICATION_ID = applid,
PROGRAM_ID = progid,
PROGRAM_UPDATE_DATE = SYSDATE,
REQUEST_ID = reqstid,
ERROR_CODE = substrb(err_code,1,240)
WHERE TRANSACTION_TEMP_ID = txnrec.transaction_temp_id
AND EXISTS (
SELECT NULL
FROM MTL_LOT_NUMBERS MLN
WHERE MLN.LOT_NUMBER = MTLT.LOT_NUMBER
AND MLN.INVENTORY_ITEM_ID <> item.inventory_item_id);
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP MTT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
TRANSACTION_STATUS = 1,
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE ROWID = txnrec.rowid;
SELECT fnd_date.date_to_canonical(EXPIRATION_DATE)
INTO lotrec.lot_expiration_date
FROM MTL_LOT_NUMBERS
WHERE INVENTORY_ITEM_ID = item.inventory_item_id
AND ORGANIZATION_ID = org.organization_id
AND LOT_NUMBER = lotrec.lot_number;
UPDATE MTL_TRANSACTION_LOTS_TEMP MTLT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_APPLICATION_ID = applid,
PROGRAM_ID = progid,
PROGRAM_UPDATE_DATE = SYSDATE,
REQUEST_ID = reqstid,
ERROR_CODE = substrb(err_code,1,240)
WHERE ROWID = lotrec.rowid;
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
SET ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
TRANSACTION_STATUS = 1,
LOCK_FLAG = 'N'
WHERE ROWID = txnrec.rowid;
SELECT 1
INTO l_dummy
FROM MTL_SERIAL_NUMBERS_TEMP
WHERE TRANSACTION_TEMP_ID = txnrec.transaction_temp_id
AND rownum < 2;
UPDATE MTL_TRANSACTION_LOTS_TEMP MTLT
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_APPLICATION_ID = applid,
PROGRAM_ID = progid,
PROGRAM_UPDATE_DATE = SYSDATE,
REQUEST_ID = reqstid,
ERROR_CODE = substrb(err_code,1,240)
WHERE ROWID = lotrec.rowid;
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
TRANSACTION_STATUS = 1,
LOCK_FLAG = 'N',
ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240)
WHERE ROWID = txnrec.rowid;
DELETE FROM MTL_SERIAL_NUMBERS_TEMP
WHERE TRANSACTION_TEMP_ID = lotrec.SERIAL_TRANSACTION_TEMP_ID;
UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_APPLICATION_ID = applid,
PROGRAM_ID = progid,
PROGRAM_UPDATE_DATE = SYSDATE,
REQUEST_ID = reqstid,
-- PRIMARY_QUANTITY = l_priqty,
LOT_EXPIRATION_DATE =
fnd_date.canonical_to_date(lotrec.lot_expiration_date),
SERIAL_TRANSACTION_TEMP_ID = lotrec.SERIAL_TRANSACTION_TEMP_ID
WHERE ROWID = lotrec.rowid;
SELECT 1
INTO l_dummy
FROM PJM_UNIT_NUMBERS_LOV_V
WHERE UNIT_NUMBER = txnrec.end_item_unit_number;
PROCEDURE errupdate(err_row_id IN ROWID)
IS
BEGIN
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
SET ERROR_CODE = substrb(err_code,1,240),
ERROR_EXPLANATION = substrb(error_exp,1,240),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = userid,
LAST_UPDATE_LOGIN = loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 'E',
TRANSACTION_STATUS = 1,
LOCK_FLAG = 'N'
WHERE ROWID = err_row_id;
END errupdate;