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 152: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS for html5 user',

148: END IF;
149:
150:
151: IF CSM_UTIL_PKG.is_html5_user(p_user_id) THEN --non ib serial numbers not reqd
152: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS for html5 user',
153: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
154: RETURN;
155: END IF;
156:

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

149:
150:
151: IF CSM_UTIL_PKG.is_html5_user(p_user_id) THEN --non ib serial numbers not reqd
152: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS for html5 user',
153: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
154: RETURN;
155: END IF;
156:
157: ---Insert for non ib items

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

169:
170: -- bulk insert into acc tables
171: IF l_access_id_tbl.count > 0 THEN
172: FORALL i IN 1..l_access_id_tbl.count
173: INSERT INTO csm_mtl_serial_numbers_acc(access_id, user_id, serial_number, inventory_item_id, current_organization_id,
174: counter, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
175: VALUES (l_access_id_tbl(i), p_user_id, l_serial_numbers_tbl(i), l_inventory_items_tbl(i), l_organizations_tbl(i),
176: 1, fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
177:

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

174: counter, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
175: VALUES (l_access_id_tbl(i), p_user_id, l_serial_numbers_tbl(i), l_inventory_items_tbl(i), l_organizations_tbl(i),
176: 1, fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
177:
178: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_access_id_tbl.count || ' records into csm_mtl_serial_numbers_acc for resource ' || p_resource_id ,
179: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
180:
181: -- make dirty calls
182: FOR i IN 1..l_access_id_tbl.count LOOP

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

175: VALUES (l_access_id_tbl(i), p_user_id, l_serial_numbers_tbl(i), l_inventory_items_tbl(i), l_organizations_tbl(i),
176: 1, fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
177:
178: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_access_id_tbl.count || ' records into csm_mtl_serial_numbers_acc for resource ' || p_resource_id ,
179: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
180:
181: -- make dirty calls
182: FOR i IN 1..l_access_id_tbl.count LOOP
183: l_markdirty := csm_util_pkg.MakeDirtyForResource(g_pub_item,

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

187: l_run_date);
188: END LOOP;
189: END IF;
190:
191: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS ',
192: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
193:
194: EXCEPTION
195: WHEN others THEN

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

188: END LOOP;
189: END IF;
190:
191: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS ',
192: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
193:
194: EXCEPTION
195: WHEN others THEN
196: l_sqlerrno := to_char(SQLCODE);

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

194: EXCEPTION
195: WHEN others THEN
196: l_sqlerrno := to_char(SQLCODE);
197: l_sqlerrmsg := substr(SQLERRM, 1,2000);
198: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
199: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
200:
201: END INSERT_MTL_SERIAL_NUMBERS;
202:

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

195: WHEN others THEN
196: l_sqlerrno := to_char(SQLCODE);
197: l_sqlerrmsg := substr(SQLERRM, 1,2000);
198: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
199: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
200:
201: END INSERT_MTL_SERIAL_NUMBERS;
202:
203: 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 201: END INSERT_MTL_SERIAL_NUMBERS;

197: l_sqlerrmsg := substr(SQLERRM, 1,2000);
198: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
199: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
200:
201: END INSERT_MTL_SERIAL_NUMBERS;
202:
203: 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)
204: IS
205: TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY binary_integer;

Line 203: 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)

199: 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
200:
201: END INSERT_MTL_SERIAL_NUMBERS;
202:
203: 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)
204: IS
205: TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY binary_integer;
206: l_access_id_tbl access_id_tbl_typ;
207: l_run_date DATE;

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

201: END INSERT_MTL_SERIAL_NUMBERS;
202:
203: 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)
204: IS
205: TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY binary_integer;
206: l_access_id_tbl access_id_tbl_typ;
207: l_run_date DATE;
208: l_markdirty boolean;
209: l_sqlerrno varchar2(20);

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

209: l_sqlerrno varchar2(20);
210: l_sqlerrmsg varchar2(2000);
211:
212: -- upd serial numbers whose locations have changed within the org to locations user has access to
213: CURSOR l_upd_mtl_serial_numbers_csr(p_lastrundate IN date, p_resourceid IN number, p_userid IN number)
214: IS
215: SELECT access_id
216: FROM csm_mtl_serial_numbers_acc acc
217: , mtl_serial_numbers msn

Line 216: FROM csm_mtl_serial_numbers_acc acc

212: -- upd serial numbers whose locations have changed within the org to locations user has access to
213: CURSOR l_upd_mtl_serial_numbers_csr(p_lastrundate IN date, p_resourceid IN number, p_userid IN number)
214: IS
215: SELECT access_id
216: FROM csm_mtl_serial_numbers_acc acc
217: , mtl_serial_numbers msn
218: WHERE msn.inventory_item_id = acc.inventory_item_id
219: AND msn.serial_number = acc.serial_number
220: AND msn.current_organization_id = acc.current_organization_id

Line 217: , mtl_serial_numbers msn

213: CURSOR l_upd_mtl_serial_numbers_csr(p_lastrundate IN date, p_resourceid IN number, p_userid IN number)
214: IS
215: SELECT access_id
216: FROM csm_mtl_serial_numbers_acc acc
217: , mtl_serial_numbers msn
218: WHERE msn.inventory_item_id = acc.inventory_item_id
219: AND msn.serial_number = acc.serial_number
220: AND msn.current_organization_id = acc.current_organization_id
221: AND msn.last_update_date >= p_lastrundate

Line 232: CURSOR l_del_mtl_serial_numbers_csr(p_resourceid IN number, p_userid IN number)

228: AND SYSDATE BETWEEN nvl( effective_date_start, SYSDATE )
229: AND nvl( effective_date_end , SYSDATE ));*/
230:
231: -- decrement counter of serial numbers whose locations have changed within the org or that reside in a diff org
232: CURSOR l_del_mtl_serial_numbers_csr(p_resourceid IN number, p_userid IN number)
233: IS
234: SELECT access_id
235: FROM csm_mtl_serial_numbers_acc acc
236: , mtl_serial_numbers msn

Line 235: FROM csm_mtl_serial_numbers_acc acc

231: -- decrement counter of serial numbers whose locations have changed within the org or that reside in a diff org
232: CURSOR l_del_mtl_serial_numbers_csr(p_resourceid IN number, p_userid IN number)
233: IS
234: SELECT access_id
235: FROM csm_mtl_serial_numbers_acc acc
236: , mtl_serial_numbers msn
237: WHERE msn.inventory_item_id = acc.inventory_item_id
238: AND msn.serial_number = acc.serial_number
239: AND msn.current_organization_id = acc.current_organization_id

Line 236: , mtl_serial_numbers msn

232: CURSOR l_del_mtl_serial_numbers_csr(p_resourceid IN number, p_userid IN number)
233: IS
234: SELECT access_id
235: FROM csm_mtl_serial_numbers_acc acc
236: , mtl_serial_numbers msn
237: WHERE msn.inventory_item_id = acc.inventory_item_id
238: AND msn.serial_number = acc.serial_number
239: AND msn.current_organization_id = acc.current_organization_id
240: AND acc.user_id = p_userid

Line 251: FROM csm_mtl_serial_numbers_acc acc

247: AND SYSDATE BETWEEN nvl( effective_date_start, SYSDATE )
248: AND nvl( effective_date_end , SYSDATE ))
249: UNION
250: SELECT access_id
251: FROM csm_mtl_serial_numbers_acc acc
252: WHERE acc.user_id = p_user_id
253: AND NOT EXISTS
254: (SELECT 1
255: FROM mtl_serial_numbers msn

Line 255: FROM mtl_serial_numbers msn

251: FROM csm_mtl_serial_numbers_acc acc
252: WHERE acc.user_id = p_user_id
253: AND NOT EXISTS
254: (SELECT 1
255: FROM mtl_serial_numbers msn
256: WHERE msn.serial_number = acc.serial_number
257: AND msn.inventory_item_id = acc.inventory_item_id
258: AND msn.current_organization_id = acc.current_organization_id
259: AND msn.CURRENT_STATUS =3

Line 263: FROM mtl_serial_numbers ser,

259: AND msn.CURRENT_STATUS =3
260: )
261: AND NOT EXISTS
262: ( SELECT 1
263: FROM mtl_serial_numbers ser,
264: csm_mtl_onhand_qty_acc ohqacc,
265: mtl_system_items sys
266: WHERE ser.current_status in (1,4)
267: AND ohqacc.user_id = acc.user_id

Line 279: CURSOR l_del_mtl_serial_numbers_csr5(p_resourceid IN number, p_userid IN number) --htm5 users only status=3 i.e no non-ib items

275:
276: );
277:
278:
279: CURSOR l_del_mtl_serial_numbers_csr5(p_resourceid IN number, p_userid IN number) --htm5 users only status=3 i.e no non-ib items
280: IS
281: SELECT access_id
282: FROM csm_mtl_serial_numbers_acc acc , mtl_serial_numbers msn
283: WHERE msn.inventory_item_id = acc.inventory_item_id

Line 282: FROM csm_mtl_serial_numbers_acc acc , mtl_serial_numbers msn

278:
279: CURSOR l_del_mtl_serial_numbers_csr5(p_resourceid IN number, p_userid IN number) --htm5 users only status=3 i.e no non-ib items
280: IS
281: SELECT access_id
282: FROM csm_mtl_serial_numbers_acc acc , mtl_serial_numbers msn
283: WHERE msn.inventory_item_id = acc.inventory_item_id
284: AND msn.serial_number = acc.serial_number
285: AND msn.current_organization_id = acc.current_organization_id
286: AND acc.user_id = p_userid

Line 294: FROM csm_mtl_serial_numbers_acc acc

290: FROM csp_inv_loc_assignments WHERE resource_id = p_resourceid
291: AND SYSDATE BETWEEN nvl( effective_date_start, SYSDATE )AND nvl( effective_date_end , SYSDATE ))
292: UNION
293: SELECT access_id
294: FROM csm_mtl_serial_numbers_acc acc
295: WHERE acc.user_id = p_user_id
296: AND NOT EXISTS
297: (SELECT 1 FROM mtl_serial_numbers msn
298: WHERE msn.serial_number = acc.serial_number

Line 297: (SELECT 1 FROM mtl_serial_numbers msn

293: SELECT access_id
294: FROM csm_mtl_serial_numbers_acc acc
295: WHERE acc.user_id = p_user_id
296: AND NOT EXISTS
297: (SELECT 1 FROM mtl_serial_numbers msn
298: WHERE msn.serial_number = acc.serial_number
299: AND msn.inventory_item_id = acc.inventory_item_id
300: AND msn.current_organization_id = acc.current_organization_id
301: AND msn.CURRENT_STATUS =3 );

Line 308: FROM csm_mtl_serial_numbers_acc

304:
305: CURSOR l_delete_serial_number_acc(p_userid IN number)
306: IS
307: SELECT access_id
308: FROM csm_mtl_serial_numbers_acc
309: WHERE user_id = p_userid
310: AND counter = 0;
311:
312: BEGIN

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

309: WHERE user_id = p_userid
310: AND counter = 0;
311:
312: BEGIN
313: CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS ',
314: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
315:
316: l_run_date := SYSDATE;
317:

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

310: AND counter = 0;
311:
312: BEGIN
313: CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS ',
314: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
315:
316: l_run_date := SYSDATE;
317:
318: IF l_access_id_tbl.count > 0 THEN

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

319: l_access_id_tbl.DELETE;
320: END IF;
321:
322: -- bulk collect all updated serial_numbers
323: OPEN l_upd_mtl_serial_numbers_csr(p_last_run_date, p_resource_id, p_user_id);
324: FETCH l_upd_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
325: CLOSE l_upd_mtl_serial_numbers_csr;
326:
327: IF l_access_id_tbl.count > 0 THEN

Line 324: FETCH l_upd_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;

320: END IF;
321:
322: -- bulk collect all updated serial_numbers
323: OPEN l_upd_mtl_serial_numbers_csr(p_last_run_date, p_resource_id, p_user_id);
324: FETCH l_upd_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
325: CLOSE l_upd_mtl_serial_numbers_csr;
326:
327: IF l_access_id_tbl.count > 0 THEN
328: CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records marked update on csm_mtl_serial_numbers for resource ' || p_resource_id ,

Line 325: CLOSE l_upd_mtl_serial_numbers_csr;

321:
322: -- bulk collect all updated serial_numbers
323: OPEN l_upd_mtl_serial_numbers_csr(p_last_run_date, p_resource_id, p_user_id);
324: FETCH l_upd_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
325: CLOSE l_upd_mtl_serial_numbers_csr;
326:
327: IF l_access_id_tbl.count > 0 THEN
328: CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records marked update on csm_mtl_serial_numbers for resource ' || p_resource_id ,
329: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);

Line 328: CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records marked update on csm_mtl_serial_numbers for resource ' || p_resource_id ,

324: FETCH l_upd_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
325: CLOSE l_upd_mtl_serial_numbers_csr;
326:
327: IF l_access_id_tbl.count > 0 THEN
328: CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records marked update on csm_mtl_serial_numbers for resource ' || p_resource_id ,
329: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
330:
331: -- make dirty calls
332: FOR i IN 1..l_access_id_tbl.count LOOP

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

325: CLOSE l_upd_mtl_serial_numbers_csr;
326:
327: IF l_access_id_tbl.count > 0 THEN
328: CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records marked update on csm_mtl_serial_numbers for resource ' || p_resource_id ,
329: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
330:
331: -- make dirty calls
332: FOR i IN 1..l_access_id_tbl.count LOOP
333: l_markdirty := csm_util_pkg.MakeDirtyForResource(g_pub_item,

Line 346: OPEN l_del_mtl_serial_numbers_csr5(p_resource_id, p_user_id);

342:
343:
344: IF CSM_UTIL_PKG.IS_HTML5_USER(p_user_id) THEN
345: -- bulk collect all serial numbers to be deleted
346: OPEN l_del_mtl_serial_numbers_csr5(p_resource_id, p_user_id);
347: FETCH l_del_mtl_serial_numbers_csr5 BULK COLLECT INTO l_access_id_tbl;
348: CLOSE l_del_mtl_serial_numbers_csr5;
349: ELSE
350: -- bulk collect all serial numbers to be deleted

Line 347: FETCH l_del_mtl_serial_numbers_csr5 BULK COLLECT INTO l_access_id_tbl;

343:
344: IF CSM_UTIL_PKG.IS_HTML5_USER(p_user_id) THEN
345: -- bulk collect all serial numbers to be deleted
346: OPEN l_del_mtl_serial_numbers_csr5(p_resource_id, p_user_id);
347: FETCH l_del_mtl_serial_numbers_csr5 BULK COLLECT INTO l_access_id_tbl;
348: CLOSE l_del_mtl_serial_numbers_csr5;
349: ELSE
350: -- bulk collect all serial numbers to be deleted
351: OPEN l_del_mtl_serial_numbers_csr(p_resource_id, p_user_id);

Line 348: CLOSE l_del_mtl_serial_numbers_csr5;

344: IF CSM_UTIL_PKG.IS_HTML5_USER(p_user_id) THEN
345: -- bulk collect all serial numbers to be deleted
346: OPEN l_del_mtl_serial_numbers_csr5(p_resource_id, p_user_id);
347: FETCH l_del_mtl_serial_numbers_csr5 BULK COLLECT INTO l_access_id_tbl;
348: CLOSE l_del_mtl_serial_numbers_csr5;
349: ELSE
350: -- bulk collect all serial numbers to be deleted
351: OPEN l_del_mtl_serial_numbers_csr(p_resource_id, p_user_id);
352: FETCH l_del_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;

Line 351: OPEN l_del_mtl_serial_numbers_csr(p_resource_id, p_user_id);

347: FETCH l_del_mtl_serial_numbers_csr5 BULK COLLECT INTO l_access_id_tbl;
348: CLOSE l_del_mtl_serial_numbers_csr5;
349: ELSE
350: -- bulk collect all serial numbers to be deleted
351: OPEN l_del_mtl_serial_numbers_csr(p_resource_id, p_user_id);
352: FETCH l_del_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
353: CLOSE l_del_mtl_serial_numbers_csr;
354: END IF;
355:

Line 352: FETCH l_del_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;

348: CLOSE l_del_mtl_serial_numbers_csr5;
349: ELSE
350: -- bulk collect all serial numbers to be deleted
351: OPEN l_del_mtl_serial_numbers_csr(p_resource_id, p_user_id);
352: FETCH l_del_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
353: CLOSE l_del_mtl_serial_numbers_csr;
354: END IF;
355:
356: -- update counter for records to be deleted

Line 353: CLOSE l_del_mtl_serial_numbers_csr;

349: ELSE
350: -- bulk collect all serial numbers to be deleted
351: OPEN l_del_mtl_serial_numbers_csr(p_resource_id, p_user_id);
352: FETCH l_del_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
353: CLOSE l_del_mtl_serial_numbers_csr;
354: END IF;
355:
356: -- update counter for records to be deleted
357: IF l_access_id_tbl.count > 0 THEN

Line 359: UPDATE csm_mtl_serial_numbers_acc

355:
356: -- update counter for records to be deleted
357: IF l_access_id_tbl.count > 0 THEN
358: FORALL i IN 1..l_access_id_tbl.count
359: UPDATE csm_mtl_serial_numbers_acc
360: SET counter = counter - 1
361: ,last_update_date = SYSDATE
362: ,last_updated_by = fnd_global.user_id
363: WHERE access_id = l_access_id_tbl(i);

Line 374: CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records marked delete on csm_mtl_serial_numbers for resource ' || p_resource_id ,

370: FETCH l_delete_serial_number_acc BULK COLLECT INTO l_access_id_tbl;
371: CLOSE l_delete_serial_number_acc;
372:
373: IF l_access_id_tbl.count > 0 THEN
374: CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records marked delete on csm_mtl_serial_numbers for resource ' || p_resource_id ,
375: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
376:
377: -- make dirty calls
378: FOR i IN 1..l_access_id_tbl.count LOOP

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

371: CLOSE l_delete_serial_number_acc;
372:
373: IF l_access_id_tbl.count > 0 THEN
374: CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records marked delete on csm_mtl_serial_numbers for resource ' || p_resource_id ,
375: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
376:
377: -- make dirty calls
378: FOR i IN 1..l_access_id_tbl.count LOOP
379: l_markdirty := csm_util_pkg.MakeDirtyForResource(g_pub_item,

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

383: l_run_date);
384: END LOOP;
385:
386: FORALL i IN 1..l_access_id_tbl.count
387: DELETE csm_mtl_serial_numbers_acc WHERE ACCESS_ID = l_access_id_tbl(i);
388:
389: l_access_id_tbl.DELETE;
390: END IF;
391:

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

388:
389: l_access_id_tbl.DELETE;
390: END IF;
391:
392: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS ',
393: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
394:
395: EXCEPTION
396: WHEN others THEN

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

389: l_access_id_tbl.DELETE;
390: END IF;
391:
392: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS ',
393: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
394:
395: EXCEPTION
396: WHEN others THEN
397: l_sqlerrno := to_char(SQLCODE);

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

395: EXCEPTION
396: WHEN others THEN
397: l_sqlerrno := to_char(SQLCODE);
398: l_sqlerrmsg := substr(SQLERRM, 1,2000);
399: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
400: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
401:
402: END UPDATE_MTL_SERIAL_NUMBERS;
403:

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

396: WHEN others THEN
397: l_sqlerrno := to_char(SQLCODE);
398: l_sqlerrmsg := substr(SQLERRM, 1,2000);
399: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
400: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
401:
402: END UPDATE_MTL_SERIAL_NUMBERS;
403:
404: PROCEDURE refresh_mtl_serial_numbers_acc(p_status OUT NOCOPY VARCHAR2,

Line 402: END UPDATE_MTL_SERIAL_NUMBERS;

398: l_sqlerrmsg := substr(SQLERRM, 1,2000);
399: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
400: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
401:
402: END UPDATE_MTL_SERIAL_NUMBERS;
403:
404: PROCEDURE refresh_mtl_serial_numbers_acc(p_status OUT NOCOPY VARCHAR2,
405: p_message OUT NOCOPY VARCHAR2)
406: IS

Line 404: PROCEDURE refresh_mtl_serial_numbers_acc(p_status OUT NOCOPY VARCHAR2,

400: 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
401:
402: END UPDATE_MTL_SERIAL_NUMBERS;
403:
404: PROCEDURE refresh_mtl_serial_numbers_acc(p_status OUT NOCOPY VARCHAR2,
405: p_message OUT NOCOPY VARCHAR2)
406: IS
407: PRAGMA AUTONOMOUS_TRANSACTION;
408: l_prog_update_date jtm_con_request_data.last_run_date%TYPE;

Line 426: AND procedure_name = 'REFRESH_MTL_SERIAL_NUMBERS_ACC';

422: IS
423: SELECT nvl(last_run_date, (sysdate - 365*50))
424: FROM jtm_con_request_data
425: WHERE package_name = 'CSM_SERIAL_NUMBERS_EVENT_PKG'
426: AND procedure_name = 'REFRESH_MTL_SERIAL_NUMBERS_ACC';
427:
428: CURSOR l_user_id_csr (p_resourceid IN number)
429: IS
430: SELECT user_id

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

445:
446: BEGIN
447: l_run_date := SYSDATE;
448:
449: CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc ',
450: 'CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc',FND_LOG.LEVEL_PROCEDURE);
451:
452: -- R12 Serial Number implementation
453: IF l_usr_list_for_serial.COUNT > 0 THEN

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

446: BEGIN
447: l_run_date := SYSDATE;
448:
449: CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc ',
450: 'CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc',FND_LOG.LEVEL_PROCEDURE);
451:
452: -- R12 Serial Number implementation
453: IF l_usr_list_for_serial.COUNT > 0 THEN
454: l_usr_list_for_serial.DELETE;

Line 472: 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));

468: IF l_usr_list_for_serial.COUNT > 0 THEN
469: FOR l_count in 1..l_usr_list_for_serial.COUNT LOOP
470: l_user_palm_organization_id:=csm_profile_pkg.get_organization_id(l_usr_list_for_serial(l_count));
471: --updating/deleting serial numbers already present in the ACC
472: 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));
473: COMMIT;
474: --inserting new serial numbers
475: 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));
476: COMMIT;

Line 475: 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));

471: --updating/deleting serial numbers already present in the ACC
472: 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));
473: COMMIT;
474: --inserting new serial numbers
475: 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));
476: COMMIT;
477: END LOOP;
478: END IF;
479:

Line 484: AND procedure_name = 'REFRESH_MTL_SERIAL_NUMBERS_ACC';

480: -- update last_run_date
481: UPDATE jtm_con_request_data
482: SET last_run_date = l_run_date
483: WHERE package_name = 'CSM_SERIAL_NUMBERS_EVENT_PKG'
484: AND procedure_name = 'REFRESH_MTL_SERIAL_NUMBERS_ACC';
485:
486: COMMIT;
487:
488: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc ',

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

484: AND procedure_name = 'REFRESH_MTL_SERIAL_NUMBERS_ACC';
485:
486: COMMIT;
487:
488: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc ',
489: 'CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc',FND_LOG.LEVEL_PROCEDURE);
490:
491: p_status := 'FINE';
492: p_message := 'CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC executed successfully';

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

485:
486: COMMIT;
487:
488: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc ',
489: 'CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc',FND_LOG.LEVEL_PROCEDURE);
490:
491: p_status := 'FINE';
492: p_message := 'CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC executed successfully';
493:

Line 492: p_message := 'CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC executed successfully';

488: CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc ',
489: 'CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc',FND_LOG.LEVEL_PROCEDURE);
490:
491: p_status := 'FINE';
492: p_message := 'CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC executed successfully';
493:
494: EXCEPTION
495: WHEN others THEN
496: l_sqlerrno := to_char(SQLCODE);

Line 500: p_message := 'Error in CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;

496: l_sqlerrno := to_char(SQLCODE);
497: l_sqlerrmsg := substr(SQLERRM, 1,2000);
498: ROLLBACK;
499: p_status := 'ERROR';
500: p_message := 'Error in CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;
501: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg,
502: 'CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC',FND_LOG.LEVEL_EXCEPTION);
503:
504: END REFRESH_MTL_SERIAL_NUMBERS_ACC;

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

497: l_sqlerrmsg := substr(SQLERRM, 1,2000);
498: ROLLBACK;
499: p_status := 'ERROR';
500: p_message := 'Error in CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;
501: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg,
502: 'CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC',FND_LOG.LEVEL_EXCEPTION);
503:
504: END REFRESH_MTL_SERIAL_NUMBERS_ACC;
505:

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

498: ROLLBACK;
499: p_status := 'ERROR';
500: p_message := 'Error in CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;
501: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg,
502: 'CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC',FND_LOG.LEVEL_EXCEPTION);
503:
504: END REFRESH_MTL_SERIAL_NUMBERS_ACC;
505:
506: PROCEDURE DELETE_OLD_ORG_SERIAL_NUMBERS(p_organization_id IN number

Line 504: END REFRESH_MTL_SERIAL_NUMBERS_ACC;

500: p_message := 'Error in CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;
501: CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg,
502: 'CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC',FND_LOG.LEVEL_EXCEPTION);
503:
504: END REFRESH_MTL_SERIAL_NUMBERS_ACC;
505:
506: PROCEDURE DELETE_OLD_ORG_SERIAL_NUMBERS(p_organization_id IN number
507: , p_user_id IN number
508: , p_resource_id IN number)

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

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

Line 519: CURSOR l_del_mtl_serial_numbers_csr(p_organizationid IN number,

515: l_sqlerrmsg varchar2(2000);
516:
517: -- make delete dirty calls for serial numbers with counter=1; do not delete from acc
518: -- since delete will be done based on csp_inv_loc_assignment data
519: CURSOR l_del_mtl_serial_numbers_csr(p_organizationid IN number,
520: p_userid IN number)
521: IS
522: SELECT access_id
523: FROM csm_mtl_serial_numbers_acc

Line 523: FROM csm_mtl_serial_numbers_acc

519: CURSOR l_del_mtl_serial_numbers_csr(p_organizationid IN number,
520: p_userid IN number)
521: IS
522: SELECT access_id
523: FROM csm_mtl_serial_numbers_acc
524: WHERE user_id = p_userid
525: AND current_organization_id = p_organizationid
526: AND counter = 0;
527:

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

525: AND current_organization_id = p_organizationid
526: AND counter = 0;
527:
528: BEGIN
529: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS ',
530: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
531:
532: l_run_date := SYSDATE;
533:

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

526: AND counter = 0;
527:
528: BEGIN
529: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS ',
530: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
531:
532: l_run_date := SYSDATE;
533:
534:

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

531:
532: l_run_date := SYSDATE;
533:
534:
535: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS ',
536: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
537:
538: EXCEPTION
539: WHEN others THEN

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

532: l_run_date := SYSDATE;
533:
534:
535: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS ',
536: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
537:
538: EXCEPTION
539: WHEN others THEN
540: l_sqlerrno := to_char(SQLCODE);

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

539: WHEN others THEN
540: l_sqlerrno := to_char(SQLCODE);
541: l_sqlerrmsg := substr(SQLERRM, 1,2000);
542: --logm('Exception ' || l_sqlerrmsg);
543: CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
544: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
545:
546: END DELETE_OLD_ORG_SERIAL_NUMBERS;
547:

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

540: l_sqlerrno := to_char(SQLCODE);
541: l_sqlerrmsg := substr(SQLERRM, 1,2000);
542: --logm('Exception ' || l_sqlerrmsg);
543: CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
544: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
545:
546: END DELETE_OLD_ORG_SERIAL_NUMBERS;
547:
548:

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

549: PROCEDURE GET_NEW_ORG_SERIAL_NUMBERS(p_organization_id IN number
550: , p_user_id IN number
551: , p_resource_id IN number)
552: IS
553: TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY BINARY_INTEGER;
554: l_access_id_tbl access_id_tbl_typ;
555: l_mark_dirty boolean;
556: l_run_date date;
557: l_sqlerrno varchar2(20);

Line 562: CURSOR l_del_mtl_serial_numbers_csr(p_organizationid IN number,

558: l_sqlerrmsg varchar2(2000);
559:
560: -- make insert dirty calls for serial numbers with counter=1;
561: -- since data with counter>1 would already be existing on the palm
562: CURSOR l_del_mtl_serial_numbers_csr(p_organizationid IN number,
563: p_userid IN number)
564: IS
565: SELECT access_id
566: FROM csm_mtl_serial_numbers_acc

Line 566: FROM csm_mtl_serial_numbers_acc

562: CURSOR l_del_mtl_serial_numbers_csr(p_organizationid IN number,
563: p_userid IN number)
564: IS
565: SELECT access_id
566: FROM csm_mtl_serial_numbers_acc
567: WHERE user_id = p_userid
568: AND current_organization_id = p_organizationid
569: AND counter = 1;
570:

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

568: AND current_organization_id = p_organizationid
569: AND counter = 1;
570:
571: BEGIN
572: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS ',
573: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
574:
575: l_run_date := SYSDATE;
576:

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

569: AND counter = 1;
570:
571: BEGIN
572: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS ',
573: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
574:
575: l_run_date := SYSDATE;
576:
577:

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

574:
575: l_run_date := SYSDATE;
576:
577:
578: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS ',
579: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
580:
581: EXCEPTION
582: WHEN others THEN

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

575: l_run_date := SYSDATE;
576:
577:
578: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS ',
579: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
580:
581: EXCEPTION
582: WHEN others THEN
583: l_sqlerrno := to_char(SQLCODE);

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

581: EXCEPTION
582: WHEN others THEN
583: l_sqlerrno := to_char(SQLCODE);
584: l_sqlerrmsg := substr(SQLERRM, 1,2000);
585: CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
586: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
587:
588:
589: END GET_NEW_ORG_SERIAL_NUMBERS;

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

582: WHEN others THEN
583: l_sqlerrno := to_char(SQLCODE);
584: l_sqlerrmsg := substr(SQLERRM, 1,2000);
585: CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
586: 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
587:
588:
589: END GET_NEW_ORG_SERIAL_NUMBERS;
590:

Line 645: SELECT csm_mtl_serial_numbers_acc_s.nextval, inventory_item_id, serial_number, current_organization_id,au.user_id

641: PROCEDURE RECEIVED_MTL_SERIAL_NUM(p_org_id IN NUMBER,p_subinv_code IN VARCHAR2, p_item_id IN NUMBER,p_serial_number IN VARCHAR2,p_user_id IN NUMBER)
642: IS
643: CURSOR received_srl_item
644: IS
645: SELECT csm_mtl_serial_numbers_acc_s.nextval, inventory_item_id, serial_number, current_organization_id,au.user_id
646: FROM mtl_serial_numbers,asg_user au
647: WHERE current_status =3 -- html5 supports only 3
648: AND current_organization_id=p_org_id
649: AND current_subinventory_code = p_subinv_code

Line 646: FROM mtl_serial_numbers,asg_user au

642: IS
643: CURSOR received_srl_item
644: IS
645: SELECT csm_mtl_serial_numbers_acc_s.nextval, inventory_item_id, serial_number, current_organization_id,au.user_id
646: FROM mtl_serial_numbers,asg_user au
647: WHERE current_status =3 -- html5 supports only 3
648: AND current_organization_id=p_org_id
649: AND current_subinventory_code = p_subinv_code
650: AND inventory_item_id = p_item_id

Line 661: FROM csm_mtl_serial_numbers_acc

657: AND resource_type='RS_EMPLOYEE'
658: AND SYSDATE BETWEEN nvl( effective_date_start, SYSDATE ) AND nvl( effective_date_end , SYSDATE ))
659: AND ( inventory_item_id, serial_number, current_organization_id ) NOT IN (
660: SELECT inventory_item_id, serial_number, current_organization_id
661: FROM csm_mtl_serial_numbers_acc
662: WHERE user_id = au.user_id );
663:
664: l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
665: l_tab_user_id ASG_DOWNLOAD.USER_LIST;

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

666: l_tab_item_id ASG_DOWNLOAD.ACCESS_LIST;
667: l_tab_org_id ASG_DOWNLOAD.ACCESS_LIST;
668: l_markdirty BOOLEAN;
669:
670: TYPE serial_numbers_tbl_typ IS TABLE OF mtl_serial_numbers.serial_number%TYPE INDEX BY binary_integer;
671: l_tab_srl serial_numbers_tbl_typ;
672:
673: BEGIN
674:

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

679: CLOSE received_srl_item;
680:
681: IF l_tab_access_id.COUNT > 0 THEN
682: FORALL i IN 1..l_tab_access_id.count
683: INSERT INTO csm_mtl_serial_numbers_acc(access_id, user_id, serial_number, inventory_item_id, current_organization_id,
684: counter, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
685: VALUES (l_tab_access_id(i), l_tab_user_id(i), l_tab_srl(i), l_tab_item_id(i), l_tab_org_id(i),
686: 1, fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id);
687:

Line 688: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count || ' records into csm_mtl_serial_numbers_acc ',

684: counter, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
685: VALUES (l_tab_access_id(i), l_tab_user_id(i), l_tab_srl(i), l_tab_item_id(i), l_tab_org_id(i),
686: 1, fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id);
687:
688: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count || ' records into csm_mtl_serial_numbers_acc ',
689: 'CSM_SERIAL_NUMBERS_EVENT_PKG.RECEIVED_MTL_SERIAL_NUM',FND_LOG.LEVEL_STATEMENT);
690:
691: l_markdirty := asg_download.mark_dirty(
692: P_PUB_ITEM => g_pub_item