eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CSM_SERIAL_NUMBERS_EVENT_PKG dependencies on MTL_SERIAL_NUMBERS

Line 14: g_pub_item varchar2(30) := 'CSM_MTL_SERIAL_NUMBERS';

10: -- Person Date Comments
11: -- --------- ------ ------------------------------------------
12: -- Enter procedure, function bodies as shown below
13:
14: g_pub_item varchar2(30) := 'CSM_MTL_SERIAL_NUMBERS';
15:
16: PROCEDURE insert_mtl_serial_numbers(p_organization_id IN number, p_last_run_date IN date, p_resource_id IN number, p_user_id IN number)
17: IS
18: TYPE inventory_items_tbl_typ IS TABLE OF mtl_serial_numbers.inventory_item_id%TYPE INDEX BY binary_integer;

Line 16: PROCEDURE insert_mtl_serial_numbers(p_organization_id IN number, p_last_run_date IN date, p_resource_id IN number, p_user_id IN number)

12: -- Enter procedure, function bodies as shown below
13:
14: g_pub_item varchar2(30) := 'CSM_MTL_SERIAL_NUMBERS';
15:
16: PROCEDURE insert_mtl_serial_numbers(p_organization_id IN number, p_last_run_date IN date, p_resource_id IN number, p_user_id IN number)
17: IS
18: TYPE inventory_items_tbl_typ IS TABLE OF mtl_serial_numbers.inventory_item_id%TYPE INDEX BY binary_integer;
19: TYPE serial_numbers_tbl_typ IS TABLE OF mtl_serial_numbers.serial_number%TYPE INDEX BY binary_integer;
20: TYPE organizations_tbl_typ IS TABLE OF mtl_serial_numbers.current_organization_id%TYPE INDEX BY binary_integer;

Line 18: TYPE inventory_items_tbl_typ IS TABLE OF mtl_serial_numbers.inventory_item_id%TYPE INDEX BY binary_integer;

14: g_pub_item varchar2(30) := 'CSM_MTL_SERIAL_NUMBERS';
15:
16: PROCEDURE insert_mtl_serial_numbers(p_organization_id IN number, p_last_run_date IN date, p_resource_id IN number, p_user_id IN number)
17: IS
18: TYPE inventory_items_tbl_typ IS TABLE OF mtl_serial_numbers.inventory_item_id%TYPE INDEX BY binary_integer;
19: TYPE serial_numbers_tbl_typ IS TABLE OF mtl_serial_numbers.serial_number%TYPE INDEX BY binary_integer;
20: TYPE organizations_tbl_typ IS TABLE OF mtl_serial_numbers.current_organization_id%TYPE INDEX BY binary_integer;
21: TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY binary_integer;
22:

Line 19: TYPE serial_numbers_tbl_typ IS TABLE OF mtl_serial_numbers.serial_number%TYPE INDEX BY binary_integer;

15:
16: PROCEDURE insert_mtl_serial_numbers(p_organization_id IN number, p_last_run_date IN date, p_resource_id IN number, p_user_id IN number)
17: IS
18: TYPE inventory_items_tbl_typ IS TABLE OF mtl_serial_numbers.inventory_item_id%TYPE INDEX BY binary_integer;
19: TYPE serial_numbers_tbl_typ IS TABLE OF mtl_serial_numbers.serial_number%TYPE INDEX BY binary_integer;
20: TYPE organizations_tbl_typ IS TABLE OF mtl_serial_numbers.current_organization_id%TYPE INDEX BY binary_integer;
21: TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY binary_integer;
22:
23: l_inventory_items_tbl inventory_items_tbl_typ;

Line 20: TYPE organizations_tbl_typ IS TABLE OF mtl_serial_numbers.current_organization_id%TYPE INDEX BY binary_integer;

16: PROCEDURE insert_mtl_serial_numbers(p_organization_id IN number, p_last_run_date IN date, p_resource_id IN number, p_user_id IN number)
17: IS
18: TYPE inventory_items_tbl_typ IS TABLE OF mtl_serial_numbers.inventory_item_id%TYPE INDEX BY binary_integer;
19: TYPE serial_numbers_tbl_typ IS TABLE OF mtl_serial_numbers.serial_number%TYPE INDEX BY binary_integer;
20: TYPE organizations_tbl_typ IS TABLE OF mtl_serial_numbers.current_organization_id%TYPE INDEX BY binary_integer;
21: TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY binary_integer;
22:
23: l_inventory_items_tbl inventory_items_tbl_typ;
24: l_serial_numbers_tbl serial_numbers_tbl_typ;

Line 21: TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY binary_integer;

17: IS
18: TYPE inventory_items_tbl_typ IS TABLE OF mtl_serial_numbers.inventory_item_id%TYPE INDEX BY binary_integer;
19: TYPE serial_numbers_tbl_typ IS TABLE OF mtl_serial_numbers.serial_number%TYPE INDEX BY binary_integer;
20: TYPE organizations_tbl_typ IS TABLE OF mtl_serial_numbers.current_organization_id%TYPE INDEX BY binary_integer;
21: TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY binary_integer;
22:
23: l_inventory_items_tbl inventory_items_tbl_typ;
24: l_serial_numbers_tbl serial_numbers_tbl_typ;
25: l_organizations_tbl organizations_tbl_typ;

Line 33: CURSOR l_upd_mtl_serial_numbers_csr(p_organization_id in number, p_resourceid IN number, p_userid in number, p_lastrundate IN date)

29: l_sqlerrno varchar2(20);
30: l_sqlerrmsg varchar2(2000);
31:
32: -- update counter of records that may already exist in acc table
33: CURSOR l_upd_mtl_serial_numbers_csr(p_organization_id in number, p_resourceid IN number, p_userid in number, p_lastrundate IN date)
34: IS
35: SELECT acc.access_id
36: FROM csm_mtl_serial_numbers_acc acc
37: WHERE acc.user_id = p_userid

Line 36: FROM csm_mtl_serial_numbers_acc acc

32: -- update counter of records that may already exist in acc table
33: CURSOR l_upd_mtl_serial_numbers_csr(p_organization_id in number, p_resourceid IN number, p_userid in number, p_lastrundate IN date)
34: IS
35: SELECT acc.access_id
36: FROM csm_mtl_serial_numbers_acc acc
37: WHERE acc.user_id = p_userid
38: --AND acc.current_organization_id = p_organization_id
39: AND (acc.inventory_item_id, acc.serial_number, acc.current_organization_id) IN (
40: SELECT inventory_item_id, serial_number, current_organization_id

Line 41: FROM mtl_serial_numbers

37: WHERE acc.user_id = p_userid
38: --AND acc.current_organization_id = p_organization_id
39: AND (acc.inventory_item_id, acc.serial_number, acc.current_organization_id) IN (
40: SELECT inventory_item_id, serial_number, current_organization_id
41: FROM mtl_serial_numbers
42: WHERE creation_date >= NVL(p_lastrundate, creation_date)
43: AND current_status =3
44: AND (current_subinventory_code, current_organization_id) IN (
45: SELECT subinventory_code

Line 54: CURSOR l_ins_mtl_serial_numbers_csr(p_organization_id in number, p_resourceid IN number, p_userid in number, p_lastrundate IN date)

50: AND nvl( effective_date_end , SYSDATE ))
51: );
52:
53: -- get all new serial numbers that do not exist in acc table
54: CURSOR l_ins_mtl_serial_numbers_csr(p_organization_id in number, p_resourceid IN number, p_userid in number, p_lastrundate IN date)
55: IS--select serial numbers in status 3 for both ib non ib items
56: SELECT csm_mtl_serial_numbers_acc_s.nextval, inventory_item_id, serial_number, current_organization_id
57: FROM mtl_serial_numbers
58: WHERE current_status =3 -- resides in stores + issued out of subinv

Line 56: SELECT csm_mtl_serial_numbers_acc_s.nextval, inventory_item_id, serial_number, current_organization_id

52:
53: -- get all new serial numbers that do not exist in acc table
54: CURSOR l_ins_mtl_serial_numbers_csr(p_organization_id in number, p_resourceid IN number, p_userid in number, p_lastrundate IN date)
55: IS--select serial numbers in status 3 for both ib non ib items
56: SELECT csm_mtl_serial_numbers_acc_s.nextval, inventory_item_id, serial_number, current_organization_id
57: FROM mtl_serial_numbers
58: WHERE current_status =3 -- resides in stores + issued out of subinv
59: AND ( current_subinventory_code, current_organization_id ) IN (
60: SELECT subinventory_code

Line 57: FROM mtl_serial_numbers

53: -- get all new serial numbers that do not exist in acc table
54: CURSOR l_ins_mtl_serial_numbers_csr(p_organization_id in number, p_resourceid IN number, p_userid in number, p_lastrundate IN date)
55: IS--select serial numbers in status 3 for both ib non ib items
56: SELECT csm_mtl_serial_numbers_acc_s.nextval, inventory_item_id, serial_number, current_organization_id
57: FROM mtl_serial_numbers
58: WHERE current_status =3 -- resides in stores + issued out of subinv
59: AND ( current_subinventory_code, current_organization_id ) IN (
60: SELECT subinventory_code
61: ,organization_id

Line 68: FROM csm_mtl_serial_numbers_acc

64: AND SYSDATE BETWEEN nvl( effective_date_start, SYSDATE )
65: AND nvl( effective_date_end , SYSDATE ))
66: AND ( inventory_item_id, serial_number, current_organization_id ) NOT IN (
67: SELECT inventory_item_id, serial_number, current_organization_id
68: FROM csm_mtl_serial_numbers_acc
69: WHERE user_id = p_userid );
70:
71: CURSOR l_ins_mtl_ser_num_fornonib_csr(p_organization_id in number, p_resourceid IN number, p_userid in number, p_lastrundate IN date)
72: IS--select serial numbers in status 1,4 for non ib items only

Line 73: SELECT csm_mtl_serial_numbers_acc_s.nextval,

69: WHERE user_id = p_userid );
70:
71: CURSOR l_ins_mtl_ser_num_fornonib_csr(p_organization_id in number, p_resourceid IN number, p_userid in number, p_lastrundate IN date)
72: IS--select serial numbers in status 1,4 for non ib items only
73: SELECT csm_mtl_serial_numbers_acc_s.nextval,
74: ser.inventory_item_id,
75: ser.serial_number,
76: ser.current_organization_id
77: FROM mtl_serial_numbers ser

Line 77: FROM mtl_serial_numbers ser

73: SELECT csm_mtl_serial_numbers_acc_s.nextval,
74: ser.inventory_item_id,
75: ser.serial_number,
76: ser.current_organization_id
77: FROM mtl_serial_numbers ser
78: WHERE ser.current_status IN (1,4) --Not assigned and out of subinv
79: AND EXISTS (
80: SELECT 'x'
81: FROM csm_mtl_onhand_qty_acc ohqacc,

Line 92: FROM csm_mtl_serial_numbers_acc sacc

88: AND NVL(sys.COMMS_NL_TRACKABLE_FLAG,'N') ='N'
89: )
90: AND NOT EXISTS (
91: SELECT 'x'
92: FROM csm_mtl_serial_numbers_acc sacc
93: WHERE user_id = p_userid
94: AND sacc.inventory_item_id = ser.inventory_item_id
95: AND sacc.serial_number = ser.serial_number
96: AND sacc.current_organization_id = ser.current_organization_id);

Line 99: CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS ',

95: AND sacc.serial_number = ser.serial_number
96: AND sacc.current_organization_id = ser.current_organization_id);
97:
98: BEGIN
99: CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS ',
100: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
101:
102: IF l_access_id_tbl.count > 0 THEN
103: l_access_id_tbl.DELETE;

Line 100: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);

96: AND sacc.current_organization_id = ser.current_organization_id);
97:
98: BEGIN
99: CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS ',
100: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
101:
102: IF l_access_id_tbl.count > 0 THEN
103: l_access_id_tbl.DELETE;
104: END IF;

Line 109: OPEN l_upd_mtl_serial_numbers_csr(p_organization_id, p_resource_id, p_user_id, p_last_run_date);

105:
106: l_run_date := SYSDATE;
107:
108: -- update counter of serial numbers that already exist for user in acc table
109: OPEN l_upd_mtl_serial_numbers_csr(p_organization_id, p_resource_id, p_user_id, p_last_run_date);
110: FETCH l_upd_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
111: CLOSE l_upd_mtl_serial_numbers_csr;
112:
113: IF l_access_id_tbl.count > 0 THEN

Line 110: FETCH l_upd_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;

106: l_run_date := SYSDATE;
107:
108: -- update counter of serial numbers that already exist for user in acc table
109: OPEN l_upd_mtl_serial_numbers_csr(p_organization_id, p_resource_id, p_user_id, p_last_run_date);
110: FETCH l_upd_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
111: CLOSE l_upd_mtl_serial_numbers_csr;
112:
113: IF l_access_id_tbl.count > 0 THEN
114: FORALL i IN 1..l_access_id_tbl.count

Line 111: CLOSE l_upd_mtl_serial_numbers_csr;

107:
108: -- update counter of serial numbers that already exist for user in acc table
109: OPEN l_upd_mtl_serial_numbers_csr(p_organization_id, p_resource_id, p_user_id, p_last_run_date);
110: FETCH l_upd_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
111: CLOSE l_upd_mtl_serial_numbers_csr;
112:
113: IF l_access_id_tbl.count > 0 THEN
114: FORALL i IN 1..l_access_id_tbl.count
115: UPDATE csm_mtl_serial_numbers_acc

Line 115: UPDATE csm_mtl_serial_numbers_acc

111: CLOSE l_upd_mtl_serial_numbers_csr;
112:
113: IF l_access_id_tbl.count > 0 THEN
114: FORALL i IN 1..l_access_id_tbl.count
115: UPDATE csm_mtl_serial_numbers_acc
116: SET counter = counter + 1
117: ,last_update_date = SYSDATE
118: ,last_updated_by = fnd_global.user_id
119: WHERE access_id = l_access_id_tbl(i);

Line 125: OPEN l_ins_mtl_serial_numbers_csr(p_organization_id, p_resource_id, p_user_id, p_last_run_date);

121: l_access_id_tbl.delete;
122: END IF;
123:
124: -- BULK collect all new inserted serial numbers with STatus 3
125: OPEN l_ins_mtl_serial_numbers_csr(p_organization_id, p_resource_id, p_user_id, p_last_run_date);
126: FETCH l_ins_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl, l_inventory_items_tbl, l_serial_numbers_tbl, l_organizations_tbl;
127: CLOSE l_ins_mtl_serial_numbers_csr;
128:
129: -- bulk insert into acc tables

Line 126: FETCH l_ins_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl, l_inventory_items_tbl, l_serial_numbers_tbl, l_organizations_tbl;

122: END IF;
123:
124: -- BULK collect all new inserted serial numbers with STatus 3
125: OPEN l_ins_mtl_serial_numbers_csr(p_organization_id, p_resource_id, p_user_id, p_last_run_date);
126: FETCH l_ins_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl, l_inventory_items_tbl, l_serial_numbers_tbl, l_organizations_tbl;
127: CLOSE l_ins_mtl_serial_numbers_csr;
128:
129: -- bulk insert into acc tables
130: IF l_access_id_tbl.count > 0 THEN

Line 127: CLOSE l_ins_mtl_serial_numbers_csr;

123:
124: -- BULK collect all new inserted serial numbers with STatus 3
125: OPEN l_ins_mtl_serial_numbers_csr(p_organization_id, p_resource_id, p_user_id, p_last_run_date);
126: FETCH l_ins_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl, l_inventory_items_tbl, l_serial_numbers_tbl, l_organizations_tbl;
127: CLOSE l_ins_mtl_serial_numbers_csr;
128:
129: -- bulk insert into acc tables
130: IF l_access_id_tbl.count > 0 THEN
131: FORALL i IN 1..l_access_id_tbl.count

Line 132: INSERT INTO csm_mtl_serial_numbers_acc(access_id, user_id, serial_number, inventory_item_id, current_organization_id,

128:
129: -- bulk insert into acc tables
130: IF l_access_id_tbl.count > 0 THEN
131: FORALL i IN 1..l_access_id_tbl.count
132: INSERT INTO csm_mtl_serial_numbers_acc(access_id, user_id, serial_number, inventory_item_id, current_organization_id,
133: counter, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
134: VALUES (l_access_id_tbl(i), p_user_id, l_serial_numbers_tbl(i), l_inventory_items_tbl(i), l_organizations_tbl(i),
135: 1, fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
136:

Line 137: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_access_id_tbl.count || ' records into csm_mtl_serial_numbers_acc for resource ' || p_resource_id ,

133: counter, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
134: VALUES (l_access_id_tbl(i), p_user_id, l_serial_numbers_tbl(i), l_inventory_items_tbl(i), l_organizations_tbl(i),
135: 1, fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
136:
137: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_access_id_tbl.count || ' records into csm_mtl_serial_numbers_acc for resource ' || p_resource_id ,
138: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
139:
140: -- make dirty calls
141: FOR i IN 1..l_access_id_tbl.count LOOP

Line 138: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);

134: VALUES (l_access_id_tbl(i), p_user_id, l_serial_numbers_tbl(i), l_inventory_items_tbl(i), l_organizations_tbl(i),
135: 1, fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
136:
137: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_access_id_tbl.count || ' records into csm_mtl_serial_numbers_acc for resource ' || p_resource_id ,
138: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
139:
140: -- make dirty calls
141: FOR i IN 1..l_access_id_tbl.count LOOP
142: l_markdirty := csm_util_pkg.MakeDirtyForResource(g_pub_item,

Line 166: INSERT INTO csm_mtl_serial_numbers_acc(access_id, user_id, serial_number, inventory_item_id, current_organization_id,

162:
163: -- bulk insert into acc tables
164: IF l_access_id_tbl.count > 0 THEN
165: FORALL i IN 1..l_access_id_tbl.count
166: INSERT INTO csm_mtl_serial_numbers_acc(access_id, user_id, serial_number, inventory_item_id, current_organization_id,
167: counter, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
168: VALUES (l_access_id_tbl(i), p_user_id, l_serial_numbers_tbl(i), l_inventory_items_tbl(i), l_organizations_tbl(i),
169: 1, fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
170:

Line 171: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_access_id_tbl.count || ' records into csm_mtl_serial_numbers_acc for resource ' || p_resource_id ,

167: counter, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
168: VALUES (l_access_id_tbl(i), p_user_id, l_serial_numbers_tbl(i), l_inventory_items_tbl(i), l_organizations_tbl(i),
169: 1, fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
170:
171: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_access_id_tbl.count || ' records into csm_mtl_serial_numbers_acc for resource ' || p_resource_id ,
172: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
173:
174: -- make dirty calls
175: FOR i IN 1..l_access_id_tbl.count LOOP

Line 172: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);

168: VALUES (l_access_id_tbl(i), p_user_id, l_serial_numbers_tbl(i), l_inventory_items_tbl(i), l_organizations_tbl(i),
169: 1, fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
170:
171: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_access_id_tbl.count || ' records into csm_mtl_serial_numbers_acc for resource ' || p_resource_id ,
172: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
173:
174: -- make dirty calls
175: FOR i IN 1..l_access_id_tbl.count LOOP
176: l_markdirty := csm_util_pkg.MakeDirtyForResource(g_pub_item,

Line 184: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS ',

180: l_run_date);
181: END LOOP;
182: END IF;
183:
184: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS ',
185: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
186:
187: EXCEPTION
188: WHEN others THEN

Line 185: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);

181: END LOOP;
182: END IF;
183:
184: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS ',
185: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
186:
187: EXCEPTION
188: WHEN others THEN
189: l_sqlerrno := to_char(SQLCODE);

Line 191: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,

187: EXCEPTION
188: WHEN others THEN
189: l_sqlerrno := to_char(SQLCODE);
190: l_sqlerrmsg := substr(SQLERRM, 1,2000);
191: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
192: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
193:
194: END INSERT_MTL_SERIAL_NUMBERS;
195:

Line 192: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);

188: WHEN others THEN
189: l_sqlerrno := to_char(SQLCODE);
190: l_sqlerrmsg := substr(SQLERRM, 1,2000);
191: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
192: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
193:
194: END INSERT_MTL_SERIAL_NUMBERS;
195:
196: PROCEDURE update_mtl_serial_numbers(p_organization_id IN number, p_last_run_date IN date, p_resource_id IN number, p_user_id IN number)

Line 194: END INSERT_MTL_SERIAL_NUMBERS;

190: l_sqlerrmsg := substr(SQLERRM, 1,2000);
191: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
192: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
193:
194: END INSERT_MTL_SERIAL_NUMBERS;
195:
196: PROCEDURE update_mtl_serial_numbers(p_organization_id IN number, p_last_run_date IN date, p_resource_id IN number, p_user_id IN number)
197: IS
198: TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY binary_integer;

Line 196: PROCEDURE update_mtl_serial_numbers(p_organization_id IN number, p_last_run_date IN date, p_resource_id IN number, p_user_id IN number)

192: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
193:
194: END INSERT_MTL_SERIAL_NUMBERS;
195:
196: PROCEDURE update_mtl_serial_numbers(p_organization_id IN number, p_last_run_date IN date, p_resource_id IN number, p_user_id IN number)
197: IS
198: TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY binary_integer;
199: l_access_id_tbl access_id_tbl_typ;
200: l_run_date DATE;

Line 198: TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY binary_integer;

194: END INSERT_MTL_SERIAL_NUMBERS;
195:
196: PROCEDURE update_mtl_serial_numbers(p_organization_id IN number, p_last_run_date IN date, p_resource_id IN number, p_user_id IN number)
197: IS
198: TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY binary_integer;
199: l_access_id_tbl access_id_tbl_typ;
200: l_run_date DATE;
201: l_markdirty boolean;
202: l_sqlerrno varchar2(20);

Line 206: CURSOR l_upd_mtl_serial_numbers_csr(p_lastrundate IN date, p_resourceid IN number, p_userid IN number)

202: l_sqlerrno varchar2(20);
203: l_sqlerrmsg varchar2(2000);
204:
205: -- upd serial numbers whose locations have changed within the org to locations user has access to
206: CURSOR l_upd_mtl_serial_numbers_csr(p_lastrundate IN date, p_resourceid IN number, p_userid IN number)
207: IS
208: SELECT access_id
209: FROM csm_mtl_serial_numbers_acc acc
210: , mtl_serial_numbers msn

Line 209: FROM csm_mtl_serial_numbers_acc acc

205: -- upd serial numbers whose locations have changed within the org to locations user has access to
206: CURSOR l_upd_mtl_serial_numbers_csr(p_lastrundate IN date, p_resourceid IN number, p_userid IN number)
207: IS
208: SELECT access_id
209: FROM csm_mtl_serial_numbers_acc acc
210: , mtl_serial_numbers msn
211: WHERE msn.inventory_item_id = acc.inventory_item_id
212: AND msn.serial_number = acc.serial_number
213: AND msn.current_organization_id = acc.current_organization_id

Line 210: , mtl_serial_numbers msn

206: CURSOR l_upd_mtl_serial_numbers_csr(p_lastrundate IN date, p_resourceid IN number, p_userid IN number)
207: IS
208: SELECT access_id
209: FROM csm_mtl_serial_numbers_acc acc
210: , mtl_serial_numbers msn
211: WHERE msn.inventory_item_id = acc.inventory_item_id
212: AND msn.serial_number = acc.serial_number
213: AND msn.current_organization_id = acc.current_organization_id
214: AND msn.last_update_date >= p_lastrundate

Line 225: CURSOR l_del_mtl_serial_numbers_csr(p_lastrundate IN date, p_resourceid IN number, p_userid IN number)

221: AND SYSDATE BETWEEN nvl( effective_date_start, SYSDATE )
222: AND nvl( effective_date_end , SYSDATE ));*/
223:
224: -- decrement counter of serial numbers whose locations have changed within the org or that reside in a diff org
225: CURSOR l_del_mtl_serial_numbers_csr(p_lastrundate IN date, p_resourceid IN number, p_userid IN number)
226: IS
227: SELECT access_id
228: FROM csm_mtl_serial_numbers_acc acc
229: , mtl_serial_numbers msn

Line 228: FROM csm_mtl_serial_numbers_acc acc

224: -- decrement counter of serial numbers whose locations have changed within the org or that reside in a diff org
225: CURSOR l_del_mtl_serial_numbers_csr(p_lastrundate IN date, p_resourceid IN number, p_userid IN number)
226: IS
227: SELECT access_id
228: FROM csm_mtl_serial_numbers_acc acc
229: , mtl_serial_numbers msn
230: WHERE msn.inventory_item_id = acc.inventory_item_id
231: AND msn.serial_number = acc.serial_number
232: AND msn.current_organization_id = acc.current_organization_id

Line 229: , mtl_serial_numbers msn

225: CURSOR l_del_mtl_serial_numbers_csr(p_lastrundate IN date, p_resourceid IN number, p_userid IN number)
226: IS
227: SELECT access_id
228: FROM csm_mtl_serial_numbers_acc acc
229: , mtl_serial_numbers msn
230: WHERE msn.inventory_item_id = acc.inventory_item_id
231: AND msn.serial_number = acc.serial_number
232: AND msn.current_organization_id = acc.current_organization_id
233: AND acc.user_id = p_userid

Line 244: FROM csm_mtl_serial_numbers_acc acc

240: AND SYSDATE BETWEEN nvl( effective_date_start, SYSDATE )
241: AND nvl( effective_date_end , SYSDATE ))
242: UNION
243: SELECT access_id
244: FROM csm_mtl_serial_numbers_acc acc
245: WHERE acc.user_id = p_user_id
246: AND NOT EXISTS
247: (SELECT 1
248: FROM mtl_serial_numbers msn

Line 248: FROM mtl_serial_numbers msn

244: FROM csm_mtl_serial_numbers_acc acc
245: WHERE acc.user_id = p_user_id
246: AND NOT EXISTS
247: (SELECT 1
248: FROM mtl_serial_numbers msn
249: WHERE msn.serial_number = acc.serial_number
250: AND msn.inventory_item_id = acc.inventory_item_id
251: AND msn.current_organization_id = acc.current_organization_id
252: AND msn.CURRENT_STATUS =3

Line 256: FROM mtl_serial_numbers ser,

252: AND msn.CURRENT_STATUS =3
253: )
254: AND NOT EXISTS
255: ( SELECT 1
256: FROM mtl_serial_numbers ser,
257: csm_mtl_onhand_qty_acc ohqacc,
258: mtl_system_items sys
259: WHERE ser.current_status in (1,4)
260: AND ohqacc.user_id = acc.user_id

Line 275: FROM csm_mtl_serial_numbers_acc

271:
272: CURSOR l_delete_serial_number_acc(p_userid IN number)
273: IS
274: SELECT access_id
275: FROM csm_mtl_serial_numbers_acc
276: WHERE user_id = p_userid
277: AND counter = 0;
278:
279: BEGIN

Line 280: CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS ',

276: WHERE user_id = p_userid
277: AND counter = 0;
278:
279: BEGIN
280: CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS ',
281: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
282:
283: l_run_date := SYSDATE;
284:

Line 281: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);

277: AND counter = 0;
278:
279: BEGIN
280: CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS ',
281: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
282:
283: l_run_date := SYSDATE;
284:
285: IF l_access_id_tbl.count > 0 THEN

Line 290: OPEN l_upd_mtl_serial_numbers_csr(p_last_run_date, p_resource_id, p_user_id);

286: l_access_id_tbl.DELETE;
287: END IF;
288:
289: -- bulk collect all updated serial_numbers
290: OPEN l_upd_mtl_serial_numbers_csr(p_last_run_date, p_resource_id, p_user_id);
291: FETCH l_upd_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
292: CLOSE l_upd_mtl_serial_numbers_csr;
293:
294: IF l_access_id_tbl.count > 0 THEN

Line 291: FETCH l_upd_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;

287: END IF;
288:
289: -- bulk collect all updated serial_numbers
290: OPEN l_upd_mtl_serial_numbers_csr(p_last_run_date, p_resource_id, p_user_id);
291: FETCH l_upd_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
292: CLOSE l_upd_mtl_serial_numbers_csr;
293:
294: IF l_access_id_tbl.count > 0 THEN
295: CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records sent to olite for updating csm_mtl_serial_numbers for resource ' || p_resource_id ,

Line 292: CLOSE l_upd_mtl_serial_numbers_csr;

288:
289: -- bulk collect all updated serial_numbers
290: OPEN l_upd_mtl_serial_numbers_csr(p_last_run_date, p_resource_id, p_user_id);
291: FETCH l_upd_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
292: CLOSE l_upd_mtl_serial_numbers_csr;
293:
294: IF l_access_id_tbl.count > 0 THEN
295: CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records sent to olite for updating csm_mtl_serial_numbers for resource ' || p_resource_id ,
296: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);

Line 295: CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records sent to olite for updating csm_mtl_serial_numbers for resource ' || p_resource_id ,

291: FETCH l_upd_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
292: CLOSE l_upd_mtl_serial_numbers_csr;
293:
294: IF l_access_id_tbl.count > 0 THEN
295: CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records sent to olite for updating csm_mtl_serial_numbers for resource ' || p_resource_id ,
296: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
297:
298: -- make dirty calls
299: FOR i IN 1..l_access_id_tbl.count LOOP

Line 296: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);

292: CLOSE l_upd_mtl_serial_numbers_csr;
293:
294: IF l_access_id_tbl.count > 0 THEN
295: CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records sent to olite for updating csm_mtl_serial_numbers for resource ' || p_resource_id ,
296: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
297:
298: -- make dirty calls
299: FOR i IN 1..l_access_id_tbl.count LOOP
300: l_markdirty := csm_util_pkg.MakeDirtyForResource(g_pub_item,

Line 311: OPEN l_del_mtl_serial_numbers_csr(p_last_run_date, p_resource_id, p_user_id);

307: l_access_id_tbl.DELETE;
308: END IF;
309:
310: -- bulk collect all serial numbers to be deleted
311: OPEN l_del_mtl_serial_numbers_csr(p_last_run_date, p_resource_id, p_user_id);
312: FETCH l_del_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
313: CLOSE l_del_mtl_serial_numbers_csr;
314:
315: -- update counter for records to be deleted

Line 312: FETCH l_del_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;

308: END IF;
309:
310: -- bulk collect all serial numbers to be deleted
311: OPEN l_del_mtl_serial_numbers_csr(p_last_run_date, p_resource_id, p_user_id);
312: FETCH l_del_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
313: CLOSE l_del_mtl_serial_numbers_csr;
314:
315: -- update counter for records to be deleted
316: IF l_access_id_tbl.count > 0 THEN

Line 313: CLOSE l_del_mtl_serial_numbers_csr;

309:
310: -- bulk collect all serial numbers to be deleted
311: OPEN l_del_mtl_serial_numbers_csr(p_last_run_date, p_resource_id, p_user_id);
312: FETCH l_del_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
313: CLOSE l_del_mtl_serial_numbers_csr;
314:
315: -- update counter for records to be deleted
316: IF l_access_id_tbl.count > 0 THEN
317: FORALL i IN 1..l_access_id_tbl.count

Line 318: UPDATE csm_mtl_serial_numbers_acc

314:
315: -- update counter for records to be deleted
316: IF l_access_id_tbl.count > 0 THEN
317: FORALL i IN 1..l_access_id_tbl.count
318: UPDATE csm_mtl_serial_numbers_acc
319: SET counter = counter - 1
320: ,last_update_date = SYSDATE
321: ,last_updated_by = fnd_global.user_id
322: WHERE access_id = l_access_id_tbl(i);

Line 333: CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records sent to olite for deleting csm_mtl_serial_numbers for resource ' || p_resource_id ,

329: FETCH l_delete_serial_number_acc BULK COLLECT INTO l_access_id_tbl;
330: CLOSE l_delete_serial_number_acc;
331:
332: IF l_access_id_tbl.count > 0 THEN
333: CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records sent to olite for deleting csm_mtl_serial_numbers for resource ' || p_resource_id ,
334: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
335:
336: -- make dirty calls
337: FOR i IN 1..l_access_id_tbl.count LOOP

Line 334: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);

330: CLOSE l_delete_serial_number_acc;
331:
332: IF l_access_id_tbl.count > 0 THEN
333: CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records sent to olite for deleting csm_mtl_serial_numbers for resource ' || p_resource_id ,
334: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
335:
336: -- make dirty calls
337: FOR i IN 1..l_access_id_tbl.count LOOP
338: l_markdirty := csm_util_pkg.MakeDirtyForResource(g_pub_item,

Line 346: DELETE csm_mtl_serial_numbers_acc WHERE ACCESS_ID = l_access_id_tbl(i);

342: l_run_date);
343: END LOOP;
344:
345: FORALL i IN 1..l_access_id_tbl.count
346: DELETE csm_mtl_serial_numbers_acc WHERE ACCESS_ID = l_access_id_tbl(i);
347:
348: l_access_id_tbl.DELETE;
349: END IF;
350:

Line 351: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS ',

347:
348: l_access_id_tbl.DELETE;
349: END IF;
350:
351: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS ',
352: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
353:
354: EXCEPTION
355: WHEN others THEN

Line 352: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);

348: l_access_id_tbl.DELETE;
349: END IF;
350:
351: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS ',
352: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
353:
354: EXCEPTION
355: WHEN others THEN
356: l_sqlerrno := to_char(SQLCODE);

Line 358: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,

354: EXCEPTION
355: WHEN others THEN
356: l_sqlerrno := to_char(SQLCODE);
357: l_sqlerrmsg := substr(SQLERRM, 1,2000);
358: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
359: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
360:
361: END UPDATE_MTL_SERIAL_NUMBERS;
362:

Line 359: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);

355: WHEN others THEN
356: l_sqlerrno := to_char(SQLCODE);
357: l_sqlerrmsg := substr(SQLERRM, 1,2000);
358: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
359: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
360:
361: END UPDATE_MTL_SERIAL_NUMBERS;
362:
363: PROCEDURE refresh_mtl_serial_numbers_acc

Line 361: END UPDATE_MTL_SERIAL_NUMBERS;

357: l_sqlerrmsg := substr(SQLERRM, 1,2000);
358: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
359: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
360:
361: END UPDATE_MTL_SERIAL_NUMBERS;
362:
363: PROCEDURE refresh_mtl_serial_numbers_acc
364: IS
365: PRAGMA AUTONOMOUS_TRANSACTION;

Line 363: PROCEDURE refresh_mtl_serial_numbers_acc

359: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
360:
361: END UPDATE_MTL_SERIAL_NUMBERS;
362:
363: PROCEDURE refresh_mtl_serial_numbers_acc
364: IS
365: PRAGMA AUTONOMOUS_TRANSACTION;
366: l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
367: l_all_omfs_palm_resource_list asg_download.user_list;

Line 384: AND procedure_name = 'REFRESH_MTL_SERIAL_NUMBERS_ACC';

380: IS
381: SELECT nvl(last_run_date, (sysdate - 365*50))
382: FROM jtm_con_request_data
383: WHERE package_name = 'CSM_SERIAL_NUMBERS_EVENT_PKG'
384: AND procedure_name = 'REFRESH_MTL_SERIAL_NUMBERS_ACC';
385:
386: CURSOR l_user_id_csr (p_resourceid IN number)
387: IS
388: SELECT user_id

Line 407: CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc ',

403:
404: BEGIN
405: l_run_date := SYSDATE;
406:
407: CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc ',
408: 'CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc',FND_LOG.LEVEL_PROCEDURE);
409:
410: -- R12 Serial Number implementation
411: IF l_usr_list_for_serial.COUNT > 0 THEN

Line 408: 'CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc',FND_LOG.LEVEL_PROCEDURE);

404: BEGIN
405: l_run_date := SYSDATE;
406:
407: CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc ',
408: 'CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc',FND_LOG.LEVEL_PROCEDURE);
409:
410: -- R12 Serial Number implementation
411: IF l_usr_list_for_serial.COUNT > 0 THEN
412: l_usr_list_for_serial.DELETE;

Line 430: update_mtl_serial_numbers(l_user_palm_organization_id,l_last_run_date,l_rsrc_list_for_serial(l_count),l_usr_list_for_serial(l_count));

426: IF l_usr_list_for_serial.COUNT > 0 THEN
427: FOR l_count in 1..l_usr_list_for_serial.COUNT LOOP
428: l_user_palm_organization_id:=csm_profile_pkg.get_organization_id(l_usr_list_for_serial(l_count));
429: --updating/deleting serial numbers already present in the ACC
430: update_mtl_serial_numbers(l_user_palm_organization_id,l_last_run_date,l_rsrc_list_for_serial(l_count),l_usr_list_for_serial(l_count));
431: --inserting new serial numbers
432: insert_mtl_serial_numbers(l_user_palm_organization_id,l_last_run_date,l_rsrc_list_for_serial(l_count),l_usr_list_for_serial(l_count));
433:
434: END LOOP;

Line 432: insert_mtl_serial_numbers(l_user_palm_organization_id,l_last_run_date,l_rsrc_list_for_serial(l_count),l_usr_list_for_serial(l_count));

428: l_user_palm_organization_id:=csm_profile_pkg.get_organization_id(l_usr_list_for_serial(l_count));
429: --updating/deleting serial numbers already present in the ACC
430: update_mtl_serial_numbers(l_user_palm_organization_id,l_last_run_date,l_rsrc_list_for_serial(l_count),l_usr_list_for_serial(l_count));
431: --inserting new serial numbers
432: insert_mtl_serial_numbers(l_user_palm_organization_id,l_last_run_date,l_rsrc_list_for_serial(l_count),l_usr_list_for_serial(l_count));
433:
434: END LOOP;
435: END IF;
436:

Line 442: AND procedure_name = 'REFRESH_MTL_SERIAL_NUMBERS_ACC';

438: -- update last_run_date
439: UPDATE jtm_con_request_data
440: SET last_run_date = l_run_date
441: WHERE package_name = 'CSM_SERIAL_NUMBERS_EVENT_PKG'
442: AND procedure_name = 'REFRESH_MTL_SERIAL_NUMBERS_ACC';
443:
444: COMMIT;
445:
446: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc ',

Line 446: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc ',

442: AND procedure_name = 'REFRESH_MTL_SERIAL_NUMBERS_ACC';
443:
444: COMMIT;
445:
446: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc ',
447: 'CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc',FND_LOG.LEVEL_PROCEDURE);
448:
449:
450: EXCEPTION

Line 447: 'CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc',FND_LOG.LEVEL_PROCEDURE);

443:
444: COMMIT;
445:
446: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc ',
447: 'CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc',FND_LOG.LEVEL_PROCEDURE);
448:
449:
450: EXCEPTION
451: WHEN others THEN

Line 455: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg,

451: WHEN others THEN
452: l_sqlerrno := to_char(SQLCODE);
453: l_sqlerrmsg := substr(SQLERRM, 1,2000);
454: ROLLBACK;
455: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg,
456: 'CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC',FND_LOG.LEVEL_EXCEPTION);
457:
458: END REFRESH_MTL_SERIAL_NUMBERS_ACC;
459:

Line 456: 'CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC',FND_LOG.LEVEL_EXCEPTION);

452: l_sqlerrno := to_char(SQLCODE);
453: l_sqlerrmsg := substr(SQLERRM, 1,2000);
454: ROLLBACK;
455: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg,
456: 'CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC',FND_LOG.LEVEL_EXCEPTION);
457:
458: END REFRESH_MTL_SERIAL_NUMBERS_ACC;
459:
460: PROCEDURE DELETE_OLD_ORG_SERIAL_NUMBERS(p_organization_id IN number

Line 458: END REFRESH_MTL_SERIAL_NUMBERS_ACC;

454: ROLLBACK;
455: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg,
456: 'CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC',FND_LOG.LEVEL_EXCEPTION);
457:
458: END REFRESH_MTL_SERIAL_NUMBERS_ACC;
459:
460: PROCEDURE DELETE_OLD_ORG_SERIAL_NUMBERS(p_organization_id IN number
461: , p_user_id IN number
462: , p_resource_id IN number)

Line 464: TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY BINARY_INTEGER;

460: PROCEDURE DELETE_OLD_ORG_SERIAL_NUMBERS(p_organization_id IN number
461: , p_user_id IN number
462: , p_resource_id IN number)
463: IS
464: TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY BINARY_INTEGER;
465: l_access_id_tbl access_id_tbl_typ;
466: l_mark_dirty boolean;
467: l_run_date date;
468: l_sqlerrno varchar2(20);

Line 473: CURSOR l_del_mtl_serial_numbers_csr(p_organizationid IN number,

469: l_sqlerrmsg varchar2(2000);
470:
471: -- make delete dirty calls for serial numbers with counter=1; do not delete from acc
472: -- since delete will be done based on csp_inv_loc_assignment data
473: CURSOR l_del_mtl_serial_numbers_csr(p_organizationid IN number,
474: p_userid IN number)
475: IS
476: SELECT access_id
477: FROM csm_mtl_serial_numbers_acc

Line 477: FROM csm_mtl_serial_numbers_acc

473: CURSOR l_del_mtl_serial_numbers_csr(p_organizationid IN number,
474: p_userid IN number)
475: IS
476: SELECT access_id
477: FROM csm_mtl_serial_numbers_acc
478: WHERE user_id = p_userid
479: AND current_organization_id = p_organizationid
480: AND counter = 0;
481:

Line 483: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS ',

479: AND current_organization_id = p_organizationid
480: AND counter = 0;
481:
482: BEGIN
483: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS ',
484: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
485:
486: l_run_date := SYSDATE;
487:

Line 484: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);

480: AND counter = 0;
481:
482: BEGIN
483: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS ',
484: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
485:
486: l_run_date := SYSDATE;
487:
488:

Line 489: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS ',

485:
486: l_run_date := SYSDATE;
487:
488:
489: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS ',
490: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
491:
492: EXCEPTION
493: WHEN others THEN

Line 490: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);

486: l_run_date := SYSDATE;
487:
488:
489: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS ',
490: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
491:
492: EXCEPTION
493: WHEN others THEN
494: l_sqlerrno := to_char(SQLCODE);

Line 497: CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,

493: WHEN others THEN
494: l_sqlerrno := to_char(SQLCODE);
495: l_sqlerrmsg := substr(SQLERRM, 1,2000);
496: --logm('Exception ' || l_sqlerrmsg);
497: CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
498: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
499:
500: END DELETE_OLD_ORG_SERIAL_NUMBERS;
501:

Line 498: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);

494: l_sqlerrno := to_char(SQLCODE);
495: l_sqlerrmsg := substr(SQLERRM, 1,2000);
496: --logm('Exception ' || l_sqlerrmsg);
497: CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
498: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
499:
500: END DELETE_OLD_ORG_SERIAL_NUMBERS;
501:
502:

Line 507: TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY BINARY_INTEGER;

503: PROCEDURE GET_NEW_ORG_SERIAL_NUMBERS(p_organization_id IN number
504: , p_user_id IN number
505: , p_resource_id IN number)
506: IS
507: TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY BINARY_INTEGER;
508: l_access_id_tbl access_id_tbl_typ;
509: l_mark_dirty boolean;
510: l_run_date date;
511: l_sqlerrno varchar2(20);

Line 516: CURSOR l_del_mtl_serial_numbers_csr(p_organizationid IN number,

512: l_sqlerrmsg varchar2(2000);
513:
514: -- make insert dirty calls for serial numbers with counter=1;
515: -- since data with counter>1 would already be existing on the palm
516: CURSOR l_del_mtl_serial_numbers_csr(p_organizationid IN number,
517: p_userid IN number)
518: IS
519: SELECT access_id
520: FROM csm_mtl_serial_numbers_acc

Line 520: FROM csm_mtl_serial_numbers_acc

516: CURSOR l_del_mtl_serial_numbers_csr(p_organizationid IN number,
517: p_userid IN number)
518: IS
519: SELECT access_id
520: FROM csm_mtl_serial_numbers_acc
521: WHERE user_id = p_userid
522: AND current_organization_id = p_organizationid
523: AND counter = 1;
524:

Line 526: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS ',

522: AND current_organization_id = p_organizationid
523: AND counter = 1;
524:
525: BEGIN
526: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS ',
527: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
528:
529: l_run_date := SYSDATE;
530:

Line 527: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);

523: AND counter = 1;
524:
525: BEGIN
526: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS ',
527: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
528:
529: l_run_date := SYSDATE;
530:
531:

Line 532: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS ',

528:
529: l_run_date := SYSDATE;
530:
531:
532: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS ',
533: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
534:
535: EXCEPTION
536: WHEN others THEN

Line 533: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);

529: l_run_date := SYSDATE;
530:
531:
532: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS ',
533: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
534:
535: EXCEPTION
536: WHEN others THEN
537: l_sqlerrno := to_char(SQLCODE);

Line 539: CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,

535: EXCEPTION
536: WHEN others THEN
537: l_sqlerrno := to_char(SQLCODE);
538: l_sqlerrmsg := substr(SQLERRM, 1,2000);
539: CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
540: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
541:
542:
543: END GET_NEW_ORG_SERIAL_NUMBERS;

Line 540: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);

536: WHEN others THEN
537: l_sqlerrno := to_char(SQLCODE);
538: l_sqlerrmsg := substr(SQLERRM, 1,2000);
539: CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
540: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
541:
542:
543: END GET_NEW_ORG_SERIAL_NUMBERS;
544: