APPS.CSL_MTL_SERIAL_NUMBERS_ACC_PKG dependencies on MTL_SERIAL_NUMBERS

Line 1: PACKAGE BODY CSL_MTL_SERIAL_NUMBERS_ACC_PKG AS

1: PACKAGE BODY CSL_MTL_SERIAL_NUMBERS_ACC_PKG AS
2: /* $Header: cslsnacb.pls 115.6 2004/09/30 22:10:00 appldev ship $ */
3:
4: /*** Globals ***/
5: g_debug_level NUMBER; -- debug level

Line 8: JTM_HOOK_UTIL_PKG.t_publication_item_list('MTL_SERIAL_NUMBERS');

4: /*** Globals ***/
5: g_debug_level NUMBER; -- debug level
6:
7: g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
8: JTM_HOOK_UTIL_PKG.t_publication_item_list('MTL_SERIAL_NUMBERS');
9: g_table_name CONSTANT VARCHAR2(30) := 'MTL_SERIAL_NUMBERS';
10:
11:
12:

Line 9: g_table_name CONSTANT VARCHAR2(30) := 'MTL_SERIAL_NUMBERS';

5: g_debug_level NUMBER; -- debug level
6:
7: g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
8: JTM_HOOK_UTIL_PKG.t_publication_item_list('MTL_SERIAL_NUMBERS');
9: g_table_name CONSTANT VARCHAR2(30) := 'MTL_SERIAL_NUMBERS';
10:
11:
12:
13: /**/

Line 16: TYPE item_Tab IS TABLE OF mtl_serial_numbers.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;

12:
13: /**/
14: PROCEDURE INSERT_SERIAL_NUMBERS( p_resource_id IN NUMBER )
15: IS
16: TYPE item_Tab IS TABLE OF mtl_serial_numbers.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
17: TYPE serial_Tab IS TABLE OF mtl_serial_numbers.serial_number%TYPE INDEX BY BINARY_INTEGER;
18:
19: sequences ASG_DOWNLOAD.ACCESS_LIST;
20: resources ASG_DOWNLOAD.USER_LIST;

Line 17: TYPE serial_Tab IS TABLE OF mtl_serial_numbers.serial_number%TYPE INDEX BY BINARY_INTEGER;

13: /**/
14: PROCEDURE INSERT_SERIAL_NUMBERS( p_resource_id IN NUMBER )
15: IS
16: TYPE item_Tab IS TABLE OF mtl_serial_numbers.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
17: TYPE serial_Tab IS TABLE OF mtl_serial_numbers.serial_number%TYPE INDEX BY BINARY_INTEGER;
18:
19: sequences ASG_DOWNLOAD.ACCESS_LIST;
20: resources ASG_DOWNLOAD.USER_LIST;
21: items item_Tab;

Line 49: FROM MTL_SERIAL_NUMBERS

45:
46: /*Block insert every item from given subinventory/org not yet in acc table*/
47: SELECT JTM_ACC_TABLE_S.NEXTVAL, INVENTORY_ITEM_ID, SERIAL_NUMBER, p_resource_id
48: BULK COLLECT INTO sequences, items, serials, resources
49: FROM MTL_SERIAL_NUMBERS
50: WHERE CURRENT_STATUS IN (1,3)
51: AND ( CURRENT_SUBINVENTORY_CODE, CURRENT_ORGANIZATION_ID ) IN (
52: SELECT SUBINVENTORY_CODE
53: , ORGANIZATION_ID

Line 61: FROM JTM_MTL_SERIAL_NUMBERS_ACC

57: AND SYSDATE BETWEEN NVL( EFFECTIVE_DATE_START, SYSDATE )
58: AND NVL( EFFECTIVE_DATE_END , SYSDATE ))
59: AND ( INVENTORY_ITEM_ID, SERIAL_NUMBER ) NOT IN (
60: SELECT INVENTORY_ITEM_ID, SERIAL_NUMBER
61: FROM JTM_MTL_SERIAL_NUMBERS_ACC
62: WHERE RESOURCE_ID = p_resource_id );
63:
64: IF sequences.COUNT > 0 THEN
65: FORALL i IN sequences.FIRST..sequences.LAST

Line 66: INSERT INTO JTM_MTL_SERIAL_NUMBERS_ACC(

62: WHERE RESOURCE_ID = p_resource_id );
63:
64: IF sequences.COUNT > 0 THEN
65: FORALL i IN sequences.FIRST..sequences.LAST
66: INSERT INTO JTM_MTL_SERIAL_NUMBERS_ACC(
67: ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY
68: , COUNTER, RESOURCE_ID, INVENTORY_ITEM_ID, SERIAL_NUMBER ) VALUES (
69: sequences(i), sysdate, 1, sysdate, 1, 1, p_resource_id, items(i), serials(i));
70:

Line 121: FROM JTM_MTL_SERIAL_NUMBERS_ACC acc

117: CURSOR c_remove IS
118: /* Performance bug (3920090)fixing */
119: /*
120: SELECT acc.ACCESS_ID, acc.RESOURCE_ID
121: FROM JTM_MTL_SERIAL_NUMBERS_ACC acc
122: , MTL_SERIAL_NUMBERS msn
123: WHERE msn.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
124: AND msn.SERIAL_NUMBER = acc.SERIAL_NUMBER
125: AND ( msn.CURRENT_SUBINVENTORY_CODE, msn.CURRENT_ORGANIZATION_ID ) NOT IN (

Line 122: , MTL_SERIAL_NUMBERS msn

118: /* Performance bug (3920090)fixing */
119: /*
120: SELECT acc.ACCESS_ID, acc.RESOURCE_ID
121: FROM JTM_MTL_SERIAL_NUMBERS_ACC acc
122: , MTL_SERIAL_NUMBERS msn
123: WHERE msn.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
124: AND msn.SERIAL_NUMBER = acc.SERIAL_NUMBER
125: AND ( msn.CURRENT_SUBINVENTORY_CODE, msn.CURRENT_ORGANIZATION_ID ) NOT IN (
126: SELECT SUBINVENTORY_CODE

Line 134: FROM JTM_MTL_SERIAL_NUMBERS_ACC acc

130: AND SYSDATE BETWEEN NVL( EFFECTIVE_DATE_START, SYSDATE )
131: AND NVL( EFFECTIVE_DATE_END , SYSDATE ))
132: UNION
133: SELECT acc.ACCESS_ID, acc.RESOURCE_ID
134: FROM JTM_MTL_SERIAL_NUMBERS_ACC acc
135: , MTL_SERIAL_NUMBERS msn
136: WHERE msn.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
137: AND msn.SERIAL_NUMBER = acc.SERIAL_NUMBER
138: AND msn.CURRENT_STATUS NOT IN (1,3);

Line 135: , MTL_SERIAL_NUMBERS msn

131: AND NVL( EFFECTIVE_DATE_END , SYSDATE ))
132: UNION
133: SELECT acc.ACCESS_ID, acc.RESOURCE_ID
134: FROM JTM_MTL_SERIAL_NUMBERS_ACC acc
135: , MTL_SERIAL_NUMBERS msn
136: WHERE msn.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
137: AND msn.SERIAL_NUMBER = acc.SERIAL_NUMBER
138: AND msn.CURRENT_STATUS NOT IN (1,3);
139: */

Line 140: SELECT /*+ INDEX (msn MTL_SERIAL_NUMBERS_U1) */

136: WHERE msn.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
137: AND msn.SERIAL_NUMBER = acc.SERIAL_NUMBER
138: AND msn.CURRENT_STATUS NOT IN (1,3);
139: */
140: SELECT /*+ INDEX (msn MTL_SERIAL_NUMBERS_U1) */
141: acc.ACCESS_ID, acc.RESOURCE_ID
142: FROM JTM_MTL_SERIAL_NUMBERS_ACC acc
143: , MTL_SERIAL_NUMBERS msn
144: WHERE msn.SERIAL_NUMBER = acc.SERIAL_NUMBER

Line 142: FROM JTM_MTL_SERIAL_NUMBERS_ACC acc

138: AND msn.CURRENT_STATUS NOT IN (1,3);
139: */
140: SELECT /*+ INDEX (msn MTL_SERIAL_NUMBERS_U1) */
141: acc.ACCESS_ID, acc.RESOURCE_ID
142: FROM JTM_MTL_SERIAL_NUMBERS_ACC acc
143: , MTL_SERIAL_NUMBERS msn
144: WHERE msn.SERIAL_NUMBER = acc.SERIAL_NUMBER
145: AND msn.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
146: AND (NOT EXISTS

Line 143: , MTL_SERIAL_NUMBERS msn

139: */
140: SELECT /*+ INDEX (msn MTL_SERIAL_NUMBERS_U1) */
141: acc.ACCESS_ID, acc.RESOURCE_ID
142: FROM JTM_MTL_SERIAL_NUMBERS_ACC acc
143: , MTL_SERIAL_NUMBERS msn
144: WHERE msn.SERIAL_NUMBER = acc.SERIAL_NUMBER
145: AND msn.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
146: AND (NOT EXISTS
147: (SELECT 1

Line 195: DELETE JTM_MTL_SERIAL_NUMBERS_ACC

191: );
192:
193: /*To avoid a mismatch only delete records which are marked dirty*/
194: FORALL i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
195: DELETE JTM_MTL_SERIAL_NUMBERS_ACC
196: WHERE ACCESS_ID = l_tab_access_id(i);
197: END IF;
198: CLOSE c_remove;
199:

Line 225: FROM JTM_MTL_SERIAL_NUMBERS_ACC acc

221: IS
222: /*Get all existing and valid records which are changed*/
223: CURSOR c_changed( b_date DATE ) IS
224: SELECT acc.ACCESS_ID, acc.RESOURCE_ID
225: FROM JTM_MTL_SERIAL_NUMBERS_ACC acc
226: , MTL_SERIAL_NUMBERS msn
227: WHERE msn.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
228: AND msn.SERIAL_NUMBER = acc.SERIAL_NUMBER
229: AND msn.LAST_UPDATE_DATE >= p_date

Line 226: , MTL_SERIAL_NUMBERS msn

222: /*Get all existing and valid records which are changed*/
223: CURSOR c_changed( b_date DATE ) IS
224: SELECT acc.ACCESS_ID, acc.RESOURCE_ID
225: FROM JTM_MTL_SERIAL_NUMBERS_ACC acc
226: , MTL_SERIAL_NUMBERS msn
227: WHERE msn.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
228: AND msn.SERIAL_NUMBER = acc.SERIAL_NUMBER
229: AND msn.LAST_UPDATE_DATE >= p_date
230: AND ( msn.CURRENT_SUBINVENTORY_CODE, msn.CURRENT_ORGANIZATION_ID ) IN (

Line 336: where package_name = 'CSL_MTL_SERIAL_NUMBERS_ACC_PKG'

332: CURSOR c_LastRundate
333: IS
334: select LAST_RUN_DATE
335: from JTM_CON_REQUEST_DATA
336: where package_name = 'CSL_MTL_SERIAL_NUMBERS_ACC_PKG'
337: AND procedure_name = 'CON_REQUEST_SERIAL_NUMBERS';
338:
339: BEGIN
340: /*** get debug level ***/

Line 368: WHERE package_name = 'CSL_MTL_SERIAL_NUMBERS_ACC_PKG'

364:
365: /*Update the last run date*/
366: UPDATE JTM_CON_REQUEST_DATA
367: SET LAST_RUN_DATE = SYSDATE
368: WHERE package_name = 'CSL_MTL_SERIAL_NUMBERS_ACC_PKG'
369: AND procedure_name = 'CON_REQUEST_SERIAL_NUMBERS';
370:
371: COMMIT;
372: /*First remove all records no longer required*/

Line 407: END CSL_MTL_SERIAL_NUMBERS_ACC_PKG;

403: ROLLBACK;
404: RETURN;
405: END CON_REQUEST_SERIAL_NUMBERS;
406:
407: END CSL_MTL_SERIAL_NUMBERS_ACC_PKG;