[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_system_items(p_user_id IN NUMBER,
p_organization_id IN NUMBER)
IS
l_sqlerrno VARCHAR2(20);
CSM_UTIL_PKG.LOG('Entering DELETE_SYSTEM_ITEMS ',
'CSM_SYSTEM_ITEM_EVENT_PKG.DELETE_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
l_tab_access_id.DELETE;
l_stmt := 'UPDATE csm_system_items_acc acc';
l_stmt := l_stmt || ' , LAST_UPDATE_DATE = SYSDATE';
l_stmt := l_stmt || ' , last_updated_by = nvl(fnd_global.user_id, 1)';
l_tab_access_id.DELETE;
l_tab_user_id.DELETE;
SELECT access_id, user_id
BULK COLLECT INTO l_tab_access_id, l_tab_user_id
FROM csm_system_items_acc acc
WHERE acc.counter = 0;
DELETE FROM csm_system_items_acc WHERE access_id = l_tab_access_id(i);
CSM_UTIL_PKG.LOG('Leaving DELETE_SYSTEM_ITEMS ',
'CSM_SYSTEM_ITEM_EVENT_PKG.DELETE_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
CSM_UTIL_PKG.LOG('Exception in delete_system_items: ' || l_sqlerrno || ':' || l_sqlerrmsg,
'csm_system_item_event_pkg.delete_system_items',FND_LOG.LEVEL_EXCEPTION);
END delete_system_items;
DELETE CSM_SYSTEM_ITEMS_ACC WHERE ACCESS_ID = l_acc_id_lst(i);
(SELECT 1
FROM csm_system_items_acc acc
WHERE acc.user_id = au.user_id
AND acc.inventory_item_id = msi.inventory_item_id
AND acc.organization_id = msi.organization_id
);
l_acc_id_lst.DELETE;
l_user_id_lst.DELETE;
l_inv_id.DELETE;
l_org_id.DELETE;
INSERT INTO CSM_SYSTEM_ITEMS_ACC (ACCESS_ID, USER_ID, INVENTORY_ITEM_ID,ORGANIZATION_ID,
COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
VALUES (l_acc_id_lst(i), l_user_id_lst(i), l_inv_id(i), l_org_id(i), 1, 1, l_current_run_date,1,l_current_run_date,1);
l_max_last_update_date_b DATE;
l_max_last_update_date_tl DATE;
SELECT nvl(last_run_date, (sysdate - 365*50))
FROM jtm_con_request_data
WHERE package_name = 'CSM_SYSTEM_ITEM_EVENT_PKG'
AND procedure_name = 'REFRESH_ACC';
SELECT MAX(last_update_date) FROM mtl_system_items_b;
SELECT MAX(last_update_date) FROM mtl_system_items_tl;
SELECT /*+ INDEX (msi MTL_SYSTEM_ITEMS_B_U1) */ CSM_SYSTEM_ITEMS_ACC_S.NEXTVAL,
au.user_id,
msi.inventory_item_id,
msi.organization_id
FROM asg_user au,
asg_user_pub_resps aupr,
csm_user_inventory_org user_org,
mtl_system_items_b msi,
CS_BILLING_TYPE_CATEGORIES cbtc
WHERE au.user_name = aupr.user_name
AND aupr.pub_name = 'SERVICEP'
AND user_org.user_id = au.user_id
AND au.user_id = au.owner_id
AND msi.organization_id = user_org.organization_id
AND msi.enabled_flag = 'Y'
AND SYSDATE BETWEEN nvl(msi.start_date_active, SYSDATE) AND nvl(msi.end_date_active, SYSDATE)
AND msi.material_billable_flag = cbtc.billing_type
AND cbtc.billing_category IN ('L', 'E')
AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
AND NOT EXISTS
(SELECT 1
FROM csm_system_items_acc acc
WHERE acc.user_id = au.user_id
AND acc.inventory_item_id = msi.inventory_item_id
AND acc.organization_id = msi.organization_id
);
SELECT /*+ INDEX (acc CSM_SYSTEM_ITEMS_ACC_U1) (msi MTL_SYSTEM_ITEMS_B_U1) */ acc.access_id,
acc.user_id,
msi.inventory_item_id,
msi.organization_id
FROM csm_system_items_acc acc,
mtl_system_items_b msi
WHERE acc.inventory_item_id = msi.inventory_item_id
AND acc.organization_id = msi.organization_id
AND msi.last_update_date >= p_last_run_date
;
SELECT /*+ INDEX (acc CSM_SYSTEM_ITEMS_ACC_U1) (msi_tl MTL_SYSTEM_ITEMS_TL_U1) */ acc.access_id,
acc.user_id,
msi_tl.inventory_item_id,
msi_tl.organization_id
FROM csm_system_items_acc acc,
asg_user au,
mtl_system_items_tl msi_tl
WHERE acc.user_id = au.user_id
AND acc.inventory_item_id = msi_tl.inventory_item_id
AND acc.organization_id = msi_tl.organization_id
AND au.LANGUAGE = msi_tl.LANGUAGE
AND msi_tl.last_update_date >= p_last_run_date;
SELECT /*+ index (acc csm_system_items_acc_u1)*/ acc.access_id,
acc.user_id,
acc.inventory_item_id,
acc.organization_id
FROM csm_system_items_acc acc
WHERE NOT EXISTS
(SELECT 1
FROM mtl_system_items_b msi,
CS_BILLING_TYPE_CATEGORIES cbtc
WHERE msi.inventory_item_id = acc.inventory_item_id
AND msi.organization_id = acc.organization_id
AND msi.material_billable_flag = cbtc.billing_type
AND cbtc.billing_category IN ('L', 'E')
AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
AND msi.enabled_flag = 'Y'
AND SYSDATE BETWEEN nvl(msi.start_date_active, SYSDATE) AND nvl(msi.end_date_active, SYSDATE)
);
l_acc_id.DELETE;
l_user_id.DELETE;
l_inv_id.DELETE;
l_org_id.DELETE;
DELETE CSM_SYSTEM_ITEMS_ACC WHERE ACCESS_ID = l_acc_id(i);
l_acc_id.DELETE;
l_user_id.DELETE;
l_inv_id.DELETE;
l_org_id.DELETE;
/* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */
/* , MTL_SYSTEM_ITEMS_TL which were custom created */
-- get max last_upd_date from msi
OPEN c_max_last_upd_date_b;
FETCH c_max_last_upd_date_b INTO l_max_last_update_date_b;
IF( l_max_last_update_date_b < l_last_run_date) THEN
-- get max last_upd_date from msi_tl
OPEN c_max_last_upd_date_tl;
FETCH c_max_last_upd_date_tl INTO l_max_last_update_date_tl;
IF l_max_last_update_date_tl < l_last_run_date THEN
-- no updates
p_status := 'FINE';
p_message := 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_SYSTEM_ITEMS Executed successfully - No updates';
csm_util_pkg.log('No updates for csm_system_items_event_new_pkg', 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_SYSTEM_ITEMS ');
UPDATE jtm_con_request_data
SET last_run_date = l_current_run_date
WHERE package_name = 'CSM_SYSTEM_ITEM_EVENT_PKG'
AND procedure_name = 'REFRESH_ACC';
l_max_last_update_date_tl := l_max_last_update_date_b;
l_acc_id.DELETE;
l_user_id.DELETE;
l_inv_id.DELETE;
l_org_id.DELETE;
INSERT INTO CSM_SYSTEM_ITEMS_ACC (ACCESS_ID, USER_ID, INVENTORY_ITEM_ID,ORGANIZATION_ID,
COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
VALUES (l_acc_id(i), l_user_id(i), l_inv_id(i), l_org_id(i), 1, 1, l_current_run_date,1,l_current_run_date,1);
UPDATE jtm_con_request_data
SET last_run_date = l_current_run_date
WHERE package_name = 'CSM_SYSTEM_ITEM_EVENT_PKG'
AND procedure_name = 'REFRESH_ACC';
SELECT csm_mtl_onhand_qty_acc_s.NEXTVAL
FROM dual;
CURSOR l_onhand_delete_csr IS
SELECT /*+ index(ohqacc CSM_MTL_ONHAND_QTY_ACC_U2) */ ohqacc.user_id
, ohqacc.inventory_item_id
, ohqacc.organization_id
, ohqacc.ACCESS_ID
FROM csm_mtl_onhand_qty_acc ohqacc
WHERE NOT EXISTS
(
SELECT 1
FROM mtl_onhand_quantities_detail ohqmv
WHERE ohqacc.inventory_item_id = ohqmv.inventory_item_id
AND ohqacc.organization_id = ohqmv.organization_id
AND ohqacc.subinventory_code = ohqmv.subinventory_code
AND ((ohqacc.locator_id IS NULL AND ohqmv.locator_id IS NULL) OR (ohqacc.locator_id = ohqmv.locator_id))
AND ((ohqacc.lot_number IS NULL AND ohqmv.lot_number IS NULL) OR (ohqacc.lot_number = ohqmv.lot_number))
AND ((ohqacc.revision IS NULL AND ohqmv.revision IS NULL) OR (ohqacc.revision = ohqmv.revision))
)
OR NOT EXISTS
(
SELECT 1
FROM csm_inv_loc_ass_acc acc,
csp_inv_loc_assignments cila
WHERE acc.user_id = ohqacc.user_id
AND acc.csp_inv_loc_assignment_id = cila.csp_inv_loc_assignment_id
AND cila.organization_id = ohqacc.organization_id
AND cila.subinventory_code = ohqacc.subinventory_code
);
CURSOR l_onhand_update_csr IS
SELECT /*+ index(ohqacc CSM_MTL_ONHAND_QTY_ACC_U2) index(ohqmv MTL_ONHAND_QUANTITIES_N4)*/ DISTINCT ohqacc.user_id
, ohqmv.INVENTORY_ITEM_ID
, ohqmv.ORGANIZATION_ID
, ohqmv.SUBINVENTORY_CODE
, ohqmv.LOCATOR_ID
, ohqmv.REVISION
, ohqmv.LOT_NUMBER
, ohqacc.quantity
, SUM(ohqmv.transaction_quantity) tot_qty
FROM csm_mtl_onhand_qty_acc ohqacc,
mtl_onhand_quantities_detail ohqmv
WHERE ohqacc.inventory_item_id = ohqmv.inventory_item_id
AND ohqacc.organization_id = ohqmv.organization_id
AND ohqacc.subinventory_code = ohqmv.subinventory_code
AND ((ohqacc.locator_id IS NULL AND ohqmv.locator_id IS NULL) OR (ohqacc.locator_id = ohqmv.locator_id))
AND ((ohqacc.lot_number IS NULL AND ohqmv.lot_number IS NULL) OR (ohqacc.lot_number = ohqmv.lot_number))
AND ((ohqacc.revision IS NULL AND ohqmv.revision IS NULL) OR (ohqacc.revision = ohqmv.revision))
HAVING SUM(ohqmv.transaction_quantity) <> NVL(ohqacc.quantity,0)
GROUP BY ohqacc.user_id, ohqmv.inventory_item_id, ohqmv.organization_id, ohqmv.subinventory_code,
ohqmv.locator_id, ohqmv.revision, ohqmv.lot_number, ohqacc.quantity
;
CURSOR l_onhand_insert_csr IS
SELECT distinct /*+ index (msi MTL_SYSTEM_ITEMS_B_U1) index (ohqmv MTL_ONHAND_QUANTITIES_N5) */ au.user_id
, ohqmv.INVENTORY_ITEM_ID
, ohqmv.ORGANIZATION_ID
, ohqmv.SUBINVENTORY_CODE
, ohqmv.LOCATOR_ID
, ohqmv.REVISION
, ohqmv.LOT_NUMBER
, (SELECT SUM (ohqmv2.transaction_quantity)
FROM mtl_onhand_quantities_detail ohqmv2 WHERE
ohqmv.ORGANIZATION_ID=ohqmv2.ORGANIZATION_ID AND
ohqmv.SUBINVENTORY_CODE=ohqmv2.SUBINVENTORY_CODE AND
ohqmv.INVENTORY_ITEM_ID=ohqmv2.INVENTORY_ITEM_ID AND
nvl(ohqmv.LOCATOR_ID,-9999)=nvl(ohqmv2.LOCATOR_ID,-9999) AND
nvl(ohqmv.REVISION,-9999)=nvl(ohqmv2.REVISION,-9999) AND
nvl(ohqmv.LOT_NUMBER,-9999)=nvl(ohqmv2.LOT_NUMBER,-9999))
FROM asg_user au,
asg_user_pub_resps aupr,
mtl_onhand_quantities_detail ohqmv,
mtl_system_items_b msi,
CS_BILLING_TYPE_CATEGORIES cbtc
WHERE au.user_name = aupr.user_name
AND aupr.pub_name = 'SERVICEP'
AND au.user_id = au.owner_id
AND msi.INVENTORY_ITEM_ID = ohqmv.INVENTORY_ITEM_ID
AND msi.ORGANIZATION_ID = ohqmv.ORGANIZATION_ID
AND msi.mtl_transactions_enabled_flag = 'Y'
AND msi.material_billable_flag = cbtc.billing_type
AND cbtc.billing_category = 'M'
AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
AND EXISTS
( SELECT 1
FROM csm_inv_loc_ass_acc ilaacc1,
csp_inv_loc_assignments ila1
-- csp_sec_inventories csi --R12Not requirec as we are now downloading both the items in good and bad subinv
WHERE ilaacc1.user_id = au.user_id
AND ilaacc1.csp_inv_loc_assignment_id = ila1.csp_inv_loc_assignment_id
AND ila1.subinventory_code = ohqmv.subinventory_code
AND ila1.organization_id = ohqmv.organization_id
AND SYSDATE BETWEEN NVL(ila1.effective_date_start, SYSDATE) AND NVL(ila1.effective_date_end, SYSDATE)
-- AND csi.secondary_inventory_name = ila1.subinventory_code
-- AND csi.organization_id = ila1.organization_id
-- AND csi.condition_type IN('G','B') --R12-4681995
)
AND NOT EXISTS
(SELECT /*index (ohqacc CSM_MTL_ONHAND_QTY_ACC_U2)*/ 1
FROM csm_mtl_onhand_qty_acc ohqacc
WHERE ohqacc.user_id = au.user_id
AND (ohqacc.inventory_item_id = ohqmv.inventory_item_id )
AND (ohqacc.organization_id = ohqmv.organization_id )
AND (ohqacc.subinventory_code = ohqmv.subinventory_code )
AND ((ohqacc.locator_id IS NULL AND ohqmv.locator_id IS NULL) OR (ohqacc.locator_id = ohqmv.locator_id))
AND ((ohqacc.lot_number IS NULL AND ohqmv.lot_number IS NULL) OR (ohqacc.lot_number = ohqmv.lot_number))
AND ((ohqacc.revision IS NULL AND ohqmv.revision IS NULL) OR (ohqacc.revision = ohqmv.revision))
);
OPEN l_onhand_delete_csr;
acc_id_lst.DELETE;
user_id_lst.DELETE;
inv_id.DELETE;
org_id.DELETE;
sub_code.DELETE;
rvsion.DELETE;
loc_id.DELETE;
lot_num.DELETE;
qty.DELETE;
dummy_qty.DELETE;
FETCH l_onhand_delete_csr BULK COLLECT INTO user_id_lst, inv_id, org_id, acc_id_lst LIMIT 100;
CSM_UTIL_PKG.LOG('Pushing ' || acc_id_lst.COUNT || 'deleted records',
'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY',FND_LOG.LEVEL_STATEMENT);
DELETE CSM_MTL_ONHAND_QTY_ACC WHERE ACCESS_ID = acc_id_lst(i);
CSM_ITEM_INSTANCE_EVENT_PKG.DELETE_IB_NOTIN_INV(inv_id(i),org_id(i),user_id_lst(i));
END IF; -- end of deletes
CLOSE l_onhand_delete_csr;
OPEN l_onhand_update_csr;
acc_id_lst.DELETE;
user_id_lst.DELETE;
inv_id.DELETE;
org_id.DELETE;
sub_code.DELETE;
rvsion.DELETE;
loc_id.DELETE;
lot_num.DELETE;
qty.DELETE;
dummy_qty.DELETE;
FETCH l_onhand_update_csr BULK COLLECT INTO user_id_lst, inv_id, org_id, sub_code, loc_id, rvsion, lot_num, dummy_qty, qty LIMIT 100;
CSM_UTIL_PKG.LOG('Pushing ' || user_id_lst.COUNT || 'updated records',
'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY',FND_LOG.LEVEL_STATEMENT);
UPDATE CSM_MTL_ONHAND_QTY_ACC
SET LAST_UPDATE_DATE = l_current_run_date,
QUANTITY = qty(i)
WHERE user_id = user_id_lst(i)
AND inventory_item_id = inv_id(i)
AND organization_id = org_id(i)
AND subinventory_code = sub_code(i)
AND (REVISION IS NULL OR revision = rvsion(i))
AND (LOCATOR_ID IS NULL OR LOCATOR_ID = loc_id(i))
AND (LOT_NUMBER IS NULL OR LOT_NUMBER = lot_num(i))
RETURNING access_id BULK COLLECT INTO acc_id_lst;
END IF; -- end of updates
CLOSE l_onhand_update_csr;
OPEN l_onhand_insert_csr;
acc_id_lst.DELETE;
user_id_lst.DELETE;
inv_id.DELETE;
org_id.DELETE;
sub_code.DELETE;
rvsion.DELETE;
loc_id.DELETE;
lot_num.DELETE;
qty.DELETE;
dummy_qty.DELETE;
FETCH l_onhand_insert_csr BULK COLLECT INTO user_id_lst, inv_id, org_id, sub_code, loc_id, rvsion, lot_num, qty LIMIT 500;
CSM_UTIL_PKG.LOG('Pushing ' || inv_id.COUNT || 'inserted records',
'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY',FND_LOG.LEVEL_STATEMENT);
SELECT csm_mtl_onhand_qty_acc_s.NEXTVAL INTO acc_id_lst(i) FROM dual;
INSERT INTO CSM_MTL_ONHAND_QTY_ACC (ACCESS_ID, user_id, INVENTORY_ITEM_ID,ORGANIZATION_ID,
SUBINVENTORY_CODE,LOCATOR_ID,REVISION,LOT_NUMBER, LAST_UPDATE_DATE,LAST_UPDATED_BY,
CREATION_DATE,CREATED_BY, LAST_UPDATE_LOGIN, QUANTITY, GEN_PK) VALUES (acc_id_lst(i), user_id_lst(i), inv_id(i), org_id(i), sub_code(i),
loc_id(i), rvsion(i), lot_num(i), l_current_run_date,1,l_current_run_date,1, 1, qty(i), acc_id_lst(i));
CLOSE l_onhand_insert_csr;
UPDATE jtm_con_request_data
SET last_run_date = l_current_run_date
WHERE product_code = 'CSM'
AND package_name = 'CSM_SYSTEM_ITEM_EVENT_PKG'
AND procedure_name = 'REFRESH_MTL_ONHAND_QUANTITY';
SELECT /*+ INDEX (msi MTL_SYSTEM_ITEMS_B_U1) */ CSM_SYSTEM_ITEMS_ACC_S.NEXTVAL,
au.user_id,
msi.inventory_item_id,
msi.organization_id
FROM asg_user au,
asg_user_pub_resps aupr,
mtl_system_items_b msi,
CS_BILLING_TYPE_CATEGORIES cbtc
WHERE au.user_id = p_user_id
AND au.user_name = aupr.user_name
AND aupr.pub_name = 'SERVICEP'
AND msi.organization_id = p_organization_id
AND msi.enabled_flag = 'Y'
AND SYSDATE BETWEEN nvl(msi.start_date_active, SYSDATE) AND nvl(msi.end_date_active, SYSDATE)
AND msi.material_billable_flag = cbtc.billing_type
AND cbtc.billing_category IN ('L', 'E')
AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
AND NOT EXISTS
(SELECT 1
FROM csm_system_items_acc acc
WHERE acc.user_id = au.user_id
AND acc.inventory_item_id = msi.inventory_item_id
AND acc.organization_id = msi.organization_id
);
l_acc_id_lst.DELETE;
l_user_id_lst.DELETE;
l_inv_id.DELETE;
l_org_id.DELETE;
INSERT INTO CSM_SYSTEM_ITEMS_ACC (ACCESS_ID, USER_ID, INVENTORY_ITEM_ID,ORGANIZATION_ID,
COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
VALUES (l_acc_id_lst(i), l_user_id_lst(i), l_inv_id(i), l_org_id(i), 1, 1, l_current_run_date,1,l_current_run_date,1);
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
,P_ACC_TABLE_NAME => g_acc_table_name1
,P_SEQ_NAME => g_acc_sequence_name1
,P_PK1_NAME => g_pk1_name1
,P_PK1_NUM_VALUE => p_inventory_item_id
,P_PK2_NAME => g_pk2_name1
,P_PK2_NUM_VALUE => p_organization_id
,P_USER_ID => p_user_id
);
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
,P_ACC_TABLE_NAME => g_acc_table_name1
,P_PK1_NAME => g_pk1_name1
,P_PK1_NUM_VALUE => p_inventory_item_id
,P_PK2_NAME => g_pk2_name1
,P_PK2_NUM_VALUE => p_organization_id
,P_USER_ID => p_user_id
);