[Home] [Help]
11: -- --------- ------ ------------------------------------------
12: -- Enter procedure, function bodies as shown below
13:
14: g_table_name1 CONSTANT VARCHAR2(30) := 'MTL_SYSTEM_ITEMS_B';
15: g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_SYSTEM_ITEMS_ACC';
16: g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_SYSTEM_ITEMS_ACC_S';
17: g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
18: CSM_ACC_PKG.t_publication_item_list('CSF_M_SYSTEM_ITEMS');
19: g_pk1_name1 CONSTANT VARCHAR2(30) := 'INVENTORY_ITEM_ID';
12: -- Enter procedure, function bodies as shown below
13:
14: g_table_name1 CONSTANT VARCHAR2(30) := 'MTL_SYSTEM_ITEMS_B';
15: g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_SYSTEM_ITEMS_ACC';
16: g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_SYSTEM_ITEMS_ACC_S';
17: g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
18: CSM_ACC_PKG.t_publication_item_list('CSF_M_SYSTEM_ITEMS');
19: g_pk1_name1 CONSTANT VARCHAR2(30) := 'INVENTORY_ITEM_ID';
20: g_pk2_name1 CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
49: l_run_date := SYSDATE;
50:
51: l_tab_access_id.DELETE;
52:
53: l_stmt := 'UPDATE csm_system_items_acc acc';
54: l_stmt := l_stmt || ' SET COUNTER = COUNTER - 1';
55: l_stmt := l_stmt || ' , LAST_UPDATE_DATE = SYSDATE';
56: l_stmt := l_stmt || ' , last_updated_by = nvl(fnd_global.user_id, 1)';
57: l_stmt := l_stmt || ' WHERE USER_ID = :1';
64: l_tab_user_id.DELETE;
65:
66: SELECT access_id, user_id
67: BULK COLLECT INTO l_tab_access_id, l_tab_user_id
68: FROM csm_system_items_acc acc
69: WHERE acc.counter = 0;
70:
71: IF l_tab_access_id.COUNT > 0 THEN
72: -- do bulk makedirty
78: , P_TIMESTAMP => l_run_date
79: );
80:
81: FORALL i IN 1..l_tab_access_id.COUNT
82: DELETE FROM csm_system_items_acc WHERE access_id = l_tab_access_id(i);
83: END IF;
84:
85: CSM_UTIL_PKG.LOG('Leaving DELETE_SYSTEM_ITEMS ',
86: 'CSM_SYSTEM_ITEM_EVENT_PKG.DELETE_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
106: l_sqlerrmsg varchar2(2000);
107:
108: CURSOR l_old_system_items_csr(p_old_organization_id IN number, p_user_id IN number)
109: IS
110: --SELECT /*+ index (acc CSM_SYSTEM_ITEMS_ACC_U2) acc.user_id,
111: acc.access_id
112: FROM csm_system_items_acc acc
113: WHERE acc.user_id = p_user_id
114: AND acc.organization_id = p_old_organization_id;
108: CURSOR l_old_system_items_csr(p_old_organization_id IN number, p_user_id IN number)
109: IS
110: --SELECT /*+ index (acc CSM_SYSTEM_ITEMS_ACC_U2) acc.user_id,
111: acc.access_id
112: FROM csm_system_items_acc acc
113: WHERE acc.user_id = p_user_id
114: AND acc.organization_id = p_old_organization_id;
115:
116: l_dummy BOOLEAN;
164: );
165:
166: -- do a bulk delete
167: FORALL i IN l_acc_id_lst.FIRST..l_acc_id_lst.LAST
168: DELETE CSM_SYSTEM_ITEMS_ACC WHERE ACCESS_ID = l_acc_id_lst(i);
169: END IF;
170:
171: x_result := 'System Item purge complete';
172: END IF;
199: l_sqlerrmsg varchar2(2000);
200:
201: CURSOR l_systemitems_ins_csr(p_new_organization_id IN number, p_user_id IN number)
202: IS
203: --SELECT /*+ INDEX (msi MTL_SYSTEM_ITEMS_B_U1) CSM_SYSTEM_ITEMS_ACC_S.NEXTVAL,
204: au.user_id,
205: msi.inventory_item_id,
206: msi.organization_id
207: FROM asg_user au,
218: AND cbtc.billing_category IN ('L', 'E')
219: AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
220: AND NOT EXISTS
221: (SELECT 1
222: FROM csm_system_items_acc acc
223: WHERE acc.user_id = au.user_id
224: AND acc.inventory_item_id = msi.inventory_item_id
225: AND acc.organization_id = msi.organization_id
226: );
224: AND acc.inventory_item_id = msi.inventory_item_id
225: AND acc.organization_id = msi.organization_id
226: );
227:
228: TYPE inv_idTab IS TABLE OF csm_system_items_acc.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
229: TYPE org_idTab IS TABLE OF csm_system_items_acc.organization_id%TYPE INDEX BY BINARY_INTEGER;
230:
231: l_inv_id inv_idTab;
232: l_org_id org_idTab;
225: AND acc.organization_id = msi.organization_id
226: );
227:
228: TYPE inv_idTab IS TABLE OF csm_system_items_acc.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
229: TYPE org_idTab IS TABLE OF csm_system_items_acc.organization_id%TYPE INDEX BY BINARY_INTEGER;
230:
231: l_inv_id inv_idTab;
232: l_org_id org_idTab;
233: l_user_id_lst asg_download.user_list;
284: CLOSE l_systemitems_ins_csr;
285:
286: IF l_acc_id_lst.COUNT > 0 THEN
287: FORALL i IN l_acc_id_lst.FIRST..l_acc_id_lst.LAST
288: INSERT INTO CSM_SYSTEM_ITEMS_ACC (ACCESS_ID, USER_ID, INVENTORY_ITEM_ID,ORGANIZATION_ID,
289: COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
290: 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);
291:
292: -- do bulk makedirty
327: l_max_last_update_date_b DATE;
328: l_max_last_update_date_tl DATE;
329: g_pub_item_name1 VARCHAR2(30) := 'CSF_M_SYSTEM_ITEMS';
330:
331: TYPE inv_idTab IS TABLE OF csm_system_items_acc.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
332: TYPE org_idTab IS TABLE OF csm_system_items_acc.organization_id%TYPE INDEX BY BINARY_INTEGER;
333:
334: l_inv_id inv_idTab;
335: l_org_id org_idTab;
328: l_max_last_update_date_tl DATE;
329: g_pub_item_name1 VARCHAR2(30) := 'CSF_M_SYSTEM_ITEMS';
330:
331: TYPE inv_idTab IS TABLE OF csm_system_items_acc.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
332: TYPE org_idTab IS TABLE OF csm_system_items_acc.organization_id%TYPE INDEX BY BINARY_INTEGER;
333:
334: l_inv_id inv_idTab;
335: l_org_id org_idTab;
336: l_user_id asg_download.user_list;
360:
361: -- insert
362: CURSOR l_systemitems_ins_csr
363: IS
364: SELECT CSM_SYSTEM_ITEMS_ACC_S.NEXTVAL,
365: au.user_id,
366: msi.inventory_item_id,
367: msi.organization_id
368: FROM asg_user au,
376: AND SYSDATE BETWEEN nvl(msi.start_date_active, SYSDATE) AND nvl(msi.end_date_active, SYSDATE)
377: AND msi.material_billable_flag = cbtc.billing_type
378: AND cbtc.billing_category IN ('L', 'E')
379: AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
380: AND NOT EXISTS (SELECT 1 FROM csm_system_items_acc acc
381: WHERE acc.user_id = au.user_id
382: AND acc.inventory_item_id = msi.inventory_item_id
383: AND acc.organization_id = msi.organization_id);
384:
395: AND SYSDATE BETWEEN nvl(msi.start_date_active, SYSDATE) AND nvl(msi.end_date_active, SYSDATE)
396: AND msi.material_billable_flag = cbtc.billing_type
397: AND cbtc.billing_category IN ('L', 'E')
398: AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
399: AND NOT EXISTS (SELECT 1 FROM csm_system_items_acc acc
400: WHERE acc.user_id = p_user_id
401: AND acc.inventory_item_id = msi.inventory_item_id
402: AND acc.organization_id = p_user_organization_id);
403:
403:
404: -- update
405: CURSOR l_systemitems_upd_b_csr(p_last_run_date DATE)
406: IS
407: SELECT /*+ INDEX (acc CSM_SYSTEM_ITEMS_ACC_U1) */ acc.access_id,
408: acc.user_id,
409: msi.inventory_item_id,
410: msi.organization_id
411: FROM csm_system_items_acc acc,
407: SELECT /*+ INDEX (acc CSM_SYSTEM_ITEMS_ACC_U1) */ acc.access_id,
408: acc.user_id,
409: msi.inventory_item_id,
410: msi.organization_id
411: FROM csm_system_items_acc acc,
412: mtl_system_items_b msi
413: WHERE acc.inventory_item_id = msi.inventory_item_id
414: AND acc.organization_id = msi.organization_id
415: AND msi.last_update_date >= p_last_run_date
416: ;
417:
418: CURSOR l_systemitems_upd_tl_csr(p_last_run_date DATE)
419: IS
420: SELECT /*+ INDEX (acc CSM_SYSTEM_ITEMS_ACC_U1) */ acc.access_id,
421: acc.user_id,
422: msi_tl.inventory_item_id,
423: msi_tl.organization_id
424: FROM csm_system_items_acc acc,
420: SELECT /*+ INDEX (acc CSM_SYSTEM_ITEMS_ACC_U1) */ acc.access_id,
421: acc.user_id,
422: msi_tl.inventory_item_id,
423: msi_tl.organization_id
424: FROM csm_system_items_acc acc,
425: asg_user au,
426: mtl_system_items_tl msi_tl
427: WHERE acc.user_id = au.user_id
428: AND acc.inventory_item_id = msi_tl.inventory_item_id
432:
433: -- delete
434: CURSOR l_systemitems_del_csr
435: IS
436: SELECT /*+ index (acc csm_system_items_acc_u1)*/ acc.access_id,
437: acc.user_id,
438: acc.inventory_item_id,
439: acc.organization_id
440: FROM csm_system_items_acc acc
436: SELECT /*+ index (acc csm_system_items_acc_u1)*/ acc.access_id,
437: acc.user_id,
438: acc.inventory_item_id,
439: acc.organization_id
440: FROM csm_system_items_acc acc
441: WHERE NOT EXISTS
442: (SELECT 1
443: FROM mtl_system_items_b msi,
444: CS_BILLING_TYPE_CATEGORIES cbtc
491: );
492:
493: -- do a bulk delete
494: FORALL i IN l_acc_id.FIRST..l_acc_id.LAST
495: DELETE CSM_SYSTEM_ITEMS_ACC WHERE ACCESS_ID = l_acc_id(i);
496: END IF;
497: END LOOP;
498: CLOSE l_systemitems_del_csr;
499:
617:
618: -- One change compared to other insert is that we are not
619: -- checking if the record exists before inserting into the _acc table
620: FORALL j in l_inv_id.FIRST..l_inv_id.LAST
621: INSERT INTO CSM_SYSTEM_ITEMS_ACC (ACCESS_ID, USER_ID, INVENTORY_ITEM_ID,ORGANIZATION_ID,
622: COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
623: VALUES (CSM_SYSTEM_ITEMS_ACC_S.NEXTVAL, l_user_id(i),
624: l_inv_id(j), l_org_id(j), 1, 1, l_current_run_date, 1,
625: l_current_run_date, 1)
619: -- checking if the record exists before inserting into the _acc table
620: FORALL j in l_inv_id.FIRST..l_inv_id.LAST
621: INSERT INTO CSM_SYSTEM_ITEMS_ACC (ACCESS_ID, USER_ID, INVENTORY_ITEM_ID,ORGANIZATION_ID,
622: COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
623: VALUES (CSM_SYSTEM_ITEMS_ACC_S.NEXTVAL, l_user_id(i),
624: l_inv_id(j), l_org_id(j), 1, 1, l_current_run_date, 1,
625: l_current_run_date, 1)
626: RETURNING access_id, user_id BULK COLLECT INTO l_acc_id, l_curr_user_id;
627:
662: EXIT WHEN l_acc_id.COUNT = 0;
663:
664: IF l_acc_id.COUNT > 0 THEN
665: FORALL i IN l_acc_id.FIRST..l_acc_id.LAST
666: INSERT INTO CSM_SYSTEM_ITEMS_ACC (ACCESS_ID, USER_ID, INVENTORY_ITEM_ID,ORGANIZATION_ID,
667: COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
668: 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);
669:
670: -- do bulk makedirty
1127:
1128: -- Insert cursor
1129: CURSOR l_systemitems_ins_csr(p_organization_id IN NUMBER, p_user_id IN NUMBER)
1130: IS
1131: SELECT /*+ INDEX (msi MTL_SYSTEM_ITEMS_B_U1) */ CSM_SYSTEM_ITEMS_ACC_S.NEXTVAL,
1132: au.user_id,
1133: msi.inventory_item_id,
1134: msi.organization_id
1135: FROM asg_user au,
1146: AND cbtc.billing_category IN ('L', 'E')
1147: AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
1148: AND NOT EXISTS
1149: (SELECT 1
1150: FROM csm_system_items_acc acc
1151: WHERE acc.user_id = au.user_id
1152: AND acc.inventory_item_id = msi.inventory_item_id
1153: AND acc.organization_id = msi.organization_id
1154: );
1156: -- Insert cursor based on already created user of same organization
1157: CURSOR l_systemitems_ins_acc_csr (p_old_user_id NUMBER, p_organization_id NUMBER, p_new_user_id NUMBER)
1158: IS
1159: SELECT inventory_item_id
1160: FROM CSM_SYSTEM_ITEMS_ACC
1161: WHERE user_id = p_old_user_id
1162: AND organization_id = p_organization_id
1163: AND NOT EXISTS
1164: (SELECT 1
1161: WHERE user_id = p_old_user_id
1162: AND organization_id = p_organization_id
1163: AND NOT EXISTS
1164: (SELECT 1
1165: FROM csm_system_items_acc acc
1166: WHERE acc.user_id = p_new_user_id
1167: AND acc.organization_id = p_organization_id
1168: );
1169:
1175: WHERE organization_id = p_organization_id
1176: AND user_id <> p_user_id
1177: AND rownum = 1;
1178:
1179: TYPE inv_idTab IS TABLE OF csm_system_items_acc.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
1180: TYPE org_idTab IS TABLE OF csm_system_items_acc.organization_id%TYPE INDEX BY BINARY_INTEGER;
1181:
1182: l_inv_id inv_idTab;
1183: l_org_id org_idTab;
1176: AND user_id <> p_user_id
1177: AND rownum = 1;
1178:
1179: TYPE inv_idTab IS TABLE OF csm_system_items_acc.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
1180: TYPE org_idTab IS TABLE OF csm_system_items_acc.organization_id%TYPE INDEX BY BINARY_INTEGER;
1181:
1182: l_inv_id inv_idTab;
1183: l_org_id org_idTab;
1184: l_user_id_lst asg_download.user_list;
1228:
1229: IF l_inv_id.COUNT > 0 THEN
1230: FORALL i IN l_inv_id.FIRST..l_inv_id.LAST
1231:
1232: INSERT INTO CSM_SYSTEM_ITEMS_ACC (ACCESS_ID, USER_ID, INVENTORY_ITEM_ID,ORGANIZATION_ID,
1233: COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
1234: VALUES (CSM_SYSTEM_ITEMS_ACC_S.NEXTVAL, p_user_id , l_inv_id(i), l_organization_id, 1, 1, l_current_run_date,1,l_current_run_date,1);
1235: -- RETURNING access_id, user_id BULK COLLECT INTO l_acc_id_lst, l_user_id_lst;
1236:
1230: FORALL i IN l_inv_id.FIRST..l_inv_id.LAST
1231:
1232: INSERT INTO CSM_SYSTEM_ITEMS_ACC (ACCESS_ID, USER_ID, INVENTORY_ITEM_ID,ORGANIZATION_ID,
1233: COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
1234: VALUES (CSM_SYSTEM_ITEMS_ACC_S.NEXTVAL, p_user_id , l_inv_id(i), l_organization_id, 1, 1, l_current_run_date,1,l_current_run_date,1);
1235: -- RETURNING access_id, user_id BULK COLLECT INTO l_acc_id_lst, l_user_id_lst;
1236:
1237: -- -- do bulk makedirty
1238: -- l_dummy := asg_download.mark_dirty(
1270: EXIT WHEN l_acc_id_lst.COUNT = 0;
1271:
1272: IF l_acc_id_lst.COUNT > 0 THEN
1273: FORALL i IN l_acc_id_lst.FIRST..l_acc_id_lst.LAST
1274: INSERT INTO CSM_SYSTEM_ITEMS_ACC (ACCESS_ID, USER_ID, INVENTORY_ITEM_ID,ORGANIZATION_ID,
1275: COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
1276: 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);
1277:
1278: -- -- do bulk makedirty
1312: CSM_UTIL_PKG.LOG('Entering SYSTEM_ITEM_MDIRTY_I for inventory_item_id: ' || p_inventory_item_id,
1313: 'CSM_SYSTEM_ITEM_EVENT_PKG.SYSTEM_ITEM_MDIRTY_I',FND_LOG.LEVEL_PROCEDURE);
1314:
1315: IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
1316: -- insert into csm_system_items_acc
1317: CSM_ACC_PKG.Insert_Acc
1318: ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
1319: ,P_ACC_TABLE_NAME => g_acc_table_name1
1320: ,P_SEQ_NAME => g_acc_sequence_name1
1351: CSM_UTIL_PKG.LOG('Entering SYSTEM_ITEM_MDIRTY_D for inventory_item_id: ' || p_inventory_item_id,
1352: 'CSM_SYSTEM_ITEM_EVENT_PKG.SYSTEM_ITEM_MDIRTY_D',FND_LOG.LEVEL_PROCEDURE);
1353:
1354: IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
1355: -- delete from csm_system_items_acc
1356: CSM_ACC_PKG.Delete_Acc
1357: ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
1358: ,P_ACC_TABLE_NAME => g_acc_table_name1
1359: ,P_PK1_NAME => g_pk1_name1