[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mp.organization_id, mp.organization_code
FROM mtl_parameters mp
WHERE mp.organization_id = P_ORG_ID
AND NOT (mp.wms_enabled_flag = 'Y' OR mp.process_enabled_flag = 'Y');
SELECT ORGANIZATION_ID
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE ORGANIZATION_ID = P_ORG_ID
FOR UPDATE NOWAIT;
SELECT count(1) INTO l_prof_cnt
FROM fnd_profile_options o, fnd_profile_option_values v
WHERE o.profile_option_name = 'INV_FIFO_ORIG_REC_DATE'
AND o.start_date_active <= sysdate
AND (nvl(o.end_date_active,sysdate) >= sysdate)
AND o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND nvl(v.profile_option_value,'N') = 'Y' ;
DELETE FROM mtl_moqd_backup
WHERE summarized_flag = 'N';
INSERT INTO MTL_MOQD_BACKUP(
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,DATE_RECEIVED
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,PRIMARY_TRANSACTION_QUANTITY
,SUBINVENTORY_CODE
,REVISION
,LOCATOR_ID
,CREATE_TRANSACTION_ID
,UPDATE_TRANSACTION_ID
,LOT_NUMBER
,ORIG_DATE_RECEIVED
,COST_GROUP_ID
,CONTAINERIZED_FLAG
,PROJECT_ID
,TASK_ID
,ONHAND_QUANTITIES_ID
,ORGANIZATION_TYPE
,OWNING_ORGANIZATION_ID
,OWNING_TP_TYPE
,PLANNING_ORGANIZATION_ID
,PLANNING_TP_TYPE
,TRANSACTION_UOM_CODE
,TRANSACTION_QUANTITY
,SECONDARY_UOM_CODE
,SECONDARY_TRANSACTION_QUANTITY
,IS_CONSIGNED
,ROW_ID
,SUMMARIZED_FLAG
)
SELECT
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,DATE_RECEIVED
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,PRIMARY_TRANSACTION_QUANTITY
,SUBINVENTORY_CODE
,REVISION
,LOCATOR_ID
,CREATE_TRANSACTION_ID
,UPDATE_TRANSACTION_ID
,LOT_NUMBER
,ORIG_DATE_RECEIVED
,COST_GROUP_ID
,CONTAINERIZED_FLAG
,PROJECT_ID
,TASK_ID
,ONHAND_QUANTITIES_ID
,ORGANIZATION_TYPE
,OWNING_ORGANIZATION_ID
,OWNING_TP_TYPE
,PLANNING_ORGANIZATION_ID
,PLANNING_TP_TYPE
,TRANSACTION_UOM_CODE
,TRANSACTION_QUANTITY
,SECONDARY_UOM_CODE
,SECONDARY_TRANSACTION_QUANTITY
,IS_CONSIGNED
,ROWID
,'N'
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
AND PLANNING_TP_TYPE = 2
AND OWNING_TP_TYPE = 2
AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0;
debug('Finished insert into MTL_MOQD_BACKUP');
SELECT count(*)
INTO l_moq_count
FROM mtl_onhand_quantities_detail moqd
WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
AND PLANNING_TP_TYPE = 2
AND OWNING_TP_TYPE = 2
AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0
AND ROWID IN (SELECT ROW_ID FROM MTL_MOQD_BACKUP mmb
WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
AND SUMMARIZED_FLAG = 'N');
SELECT count(*)
INTO l_moq_backup_count
FROM mtl_moqd_backup
WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
AND PLANNING_TP_TYPE = 2
AND OWNING_TP_TYPE = 2
AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0
AND SUMMARIZED_FLAG = 'N';
delete from mtl_onhand_quantities_detail moqd
WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
AND PLANNING_TP_TYPE = 2
AND OWNING_TP_TYPE = 2
AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0
AND ROWID IN (SELECT ROW_ID FROM MTL_MOQD_BACKUP mmb
WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
AND SUMMARIZED_FLAG = 'N');
debug('Deleted '||SQL%ROWCOUNT||' rows from MOQD');
insert into mtl_onhand_quantities_detail(
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,DATE_RECEIVED
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,PRIMARY_TRANSACTION_QUANTITY
,SUBINVENTORY_CODE
,REVISION
,LOCATOR_ID
,CREATE_TRANSACTION_ID
,UPDATE_TRANSACTION_ID
,LOT_NUMBER
,ORIG_DATE_RECEIVED
,COST_GROUP_ID
,CONTAINERIZED_FLAG
,PROJECT_ID
,TASK_ID
,ONHAND_QUANTITIES_ID
,ORGANIZATION_TYPE
,OWNING_ORGANIZATION_ID
,OWNING_TP_TYPE
,PLANNING_ORGANIZATION_ID
,PLANNING_TP_TYPE
,TRANSACTION_UOM_CODE
,TRANSACTION_QUANTITY
,SECONDARY_UOM_CODE
,SECONDARY_TRANSACTION_QUANTITY
,IS_CONSIGNED
)
SELECT
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,MIN(DATE_RECEIVED)
,MIN(LAST_UPDATE_DATE)
,MIN(LAST_UPDATED_BY)
,MIN(CREATION_DATE)
,MIN(CREATED_BY)
,MIN(LAST_UPDATE_LOGIN)
,ROUND(SUM(PRIMARY_TRANSACTION_QUANTITY),5)
,SUBINVENTORY_CODE
,REVISION
,LOCATOR_ID
,MIN(CREATE_TRANSACTION_ID)
,MAX(UPDATE_TRANSACTION_ID) --Capturing Max(update_transaction_id)
,LOT_NUMBER
,MIN(ORIG_DATE_RECEIVED)
,COST_GROUP_ID
,NVL(CONTAINERIZED_FLAG,2) CONTAINERIZED_FLAG
,PROJECT_ID
,TASK_ID
,MIN(ONHAND_QUANTITIES_ID)
,ORGANIZATION_TYPE
,OWNING_ORGANIZATION_ID
,OWNING_TP_TYPE
,PLANNING_ORGANIZATION_ID
,PLANNING_TP_TYPE
,TRANSACTION_UOM_CODE
,ROUND(SUM(PRIMARY_TRANSACTION_QUANTITY),5)
,SECONDARY_UOM_CODE
,ROUND(SUM(SECONDARY_TRANSACTION_QUANTITY),5)
,IS_CONSIGNED
FROM MTL_MOQD_BACKUP
WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
AND PLANNING_TP_TYPE = 2
AND OWNING_TP_TYPE = 2
AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0
AND SUMMARIZED_FLAG = 'N'
GROUP BY
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
REVISION,
LOCATOR_ID,
LOT_NUMBER,
COST_GROUP_ID,
PROJECT_ID,
TASK_ID,
NVL(CONTAINERIZED_FLAG,2),
ORGANIZATION_TYPE,
OWNING_ORGANIZATION_ID,
OWNING_TP_TYPE,
PLANNING_ORGANIZATION_ID,
PLANNING_TP_TYPE,
TRANSACTION_UOM_CODE,
SECONDARY_UOM_CODE,
IS_CONSIGNED
HAVING ROUND(SUM(PRIMARY_TRANSACTION_QUANTITY),5) <> 0 ;
debug('Inserted '||SQL%ROWCOUNT||' rows into MOQD');
SELECT nvl(sum(primary_transaction_quantity) ,0)
INTO l_moq_sum
FROM mtl_onhand_quantities_detail
WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
AND PLANNING_TP_TYPE = 2
AND OWNING_TP_TYPE = 2
AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0;
SELECT nvl(sum(primary_transaction_quantity) ,0)
INTO l_moq_backup_sum
FROM mtl_moqd_backup
WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
AND PLANNING_TP_TYPE = 2
AND OWNING_TP_TYPE = 2
AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0
AND SUMMARIZED_FLAG = 'N';
INSERT INTO MTL_MOQD_BACKUP(
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,DATE_RECEIVED
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,PRIMARY_TRANSACTION_QUANTITY
,SUBINVENTORY_CODE
,REVISION
,LOCATOR_ID
,CREATE_TRANSACTION_ID
,UPDATE_TRANSACTION_ID
,LOT_NUMBER
,ORIG_DATE_RECEIVED
,COST_GROUP_ID
,CONTAINERIZED_FLAG
,PROJECT_ID
,TASK_ID
,ONHAND_QUANTITIES_ID
,ORGANIZATION_TYPE
,OWNING_ORGANIZATION_ID
,OWNING_TP_TYPE
,PLANNING_ORGANIZATION_ID
,PLANNING_TP_TYPE
,TRANSACTION_UOM_CODE
,TRANSACTION_QUANTITY
,SECONDARY_UOM_CODE
,SECONDARY_TRANSACTION_QUANTITY
,IS_CONSIGNED
,SUMMARIZED_FLAG
,REQUEST_ID
,USER_ID
,CONSOLIDATION_DATE
,ROW_ID
)
SELECT
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,DATE_RECEIVED
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,PRIMARY_TRANSACTION_QUANTITY
,SUBINVENTORY_CODE
,REVISION
,LOCATOR_ID
,CREATE_TRANSACTION_ID
,UPDATE_TRANSACTION_ID
,LOT_NUMBER
,ORIG_DATE_RECEIVED
,COST_GROUP_ID
,CONTAINERIZED_FLAG
,PROJECT_ID
,TASK_ID
,ONHAND_QUANTITIES_ID
,ORGANIZATION_TYPE
,OWNING_ORGANIZATION_ID
,OWNING_TP_TYPE
,PLANNING_ORGANIZATION_ID
,PLANNING_TP_TYPE
,TRANSACTION_UOM_CODE
,TRANSACTION_QUANTITY
,SECONDARY_UOM_CODE
,SECONDARY_TRANSACTION_QUANTITY
,IS_CONSIGNED
,'Y'
,l_request_id
,l_user_id
,SYSDATE
,ROWID
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
AND PLANNING_TP_TYPE = 2
AND OWNING_TP_TYPE = 2
AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0;
debug('Inserted '||SQL%ROWCOUNT||' rows into MOQD_BACKUP as Summary Rows');
DELETE FROM MTL_MOQD_BACKUP
WHERE SUMMARIZED_FLAG = 'N';
debug('Deleted '||SQL%ROWCOUNT||' Non-Summary rows from MOQD_BACKUP');