[Home] [Help]
25: is
26:
27: CURSOR c_item_number_err IS
28: SELECT organization_id,rowid
29: FROM mtl_item_revisions_interface
30: WHERE inventory_item_id IS NULL
31: AND set_process_id = xset_id
32: AND process_flag = 1
33: AND (organization_id = org_id OR
36: ** for assign item id from item number
37: */
38: CURSOR cc is select distinct item_number,
39: organization_id
40: from mtl_item_revisions_interface
41: where inventory_item_id is NULL
42: and item_number is not NULL
43: and organization_id is not NULL
44: and set_process_id = xset_id
48: ** for assign transacton id
49: */
50: CURSOR ff is select distinct inventory_item_id,
51: organization_id
52: from mtl_item_revisions_interface
53: where process_flag = 1
54: and set_process_id = xset_id
55: and transaction_id IS NULL --Bug: 3019435 Added condition
56: and (organization_id = org_id or
60: ** R12 C for assign revision id during default
61: */
62: CURSOR c_null_rev_id IS
63: SELECT rowid
64: FROM mtl_item_revisions_interface
65: WHERE process_flag = 1
66: AND set_process_id = xset_id
67: AND transaction_type = 'CREATE'
68: AND revision_id IS NULL
86: *Bug:9154307
87: *for assign item id based on transaction_id
88: */
89: CURSOR ct is select distinct transaction_id
90: from mtl_item_revisions_interface
91: where inventory_item_id is NULL
92: and transaction_id is not NULL
93: and organization_id is not NULL
94: and set_process_id = xset_id
104: /*
105: ** assign all the missing organization_id from organization_code
106: */
107:
108: update MTL_ITEM_REVISIONS_INTERFACE i
109: set i.organization_id = (select o.organization_id
110: from MTL_PARAMETERS o
111: where o.organization_code = i.organization_code)
112: where i.organization_id is NULL
125: FROM mtl_system_items_interface
126: WHERE transaction_id = ctr.transaction_id
127: AND ROWNUM = 1;
128:
129: UPDATE mtl_item_revisions_interface
130: SET inventory_item_id = flex_id
131: WHERE transaction_id = ctr.transaction_id
132: AND set_process_id = xset_id;
133:
156: err_text);
157:
158: if status <> 0 then /* Oracle error */
159: ----Bug: 3019435 Changed the code with in IF st.
160: update mtl_item_revisions_interface
161: set process_flag = l_process_flag_3,
162: transaction_id = NVL(transaction_id,MTL_SYSTEM_ITEMS_INTERFACE_S.nextval)
163:
164: where item_number = cr.item_number
182: request_id,
183: tran_id,
184: err_text,
185: 'item_number',
186: 'MTL_ITEM_REVISIONS_INTERFACE',
187: 'BOM_OP_VALIDATION_ERR',
188: err_text);
189: If dumm_status < 0 then
190: raise LOGGING_ERROR ;
189: If dumm_status < 0 then
190: raise LOGGING_ERROR ;
191: End if ;
192: /*
193: update mtl_item_revisions_interface
194: set process_flag = l_process_flag_3,
195: transaction_id = tran_id
196: where item_number = cr.item_number
197: and inventory_item_id is NULL
203: raise ASSIGN_ERROR;
204: end if;
205:
206: else if status = 0 then
207: update mtl_item_revisions_interface
208: set inventory_item_id = flex_id
209: where item_number = cr.item_number
210: and set_process_id = xset_id
211: and organization_id = cr.organization_id;
221: select MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
222: into tran_id
223: from dual;
224:
225: update mtl_item_revisions_interface
226: set transaction_id = tran_id
227: where inventory_item_id = cr.inventory_item_id
228: and organization_id = cr.organization_id
229: -- and set_process_id + 0 = xset_id -- fix for bug#8757041,removed + 0
237: select MTL_ITEM_REVISIONS_B_S.nextval
238: into rev_id
239: from dual;
240:
241: update mtl_item_revisions_interface
242: set revision_id = rev_id
243: where rowid = cr.rowid;
244: END LOOP;
245:
247: /*
248: ** update process flag , at last
249: ** For bug 3226359 added code to update date fields with sysdate + 1/86400 (1 sec) if they are NULL
250: */
251: update mtl_item_revisions_interface
252: set process_flag = DECODE(default_flag, 1, l_process_flag_2 , l_process_flag_1),
253: LAST_UPDATE_DATE = nvl(LAST_UPDATE_DATE,(sysdate + 1/86400)),
254: /* LAST_UPDATED_BY = -1,
255: ** NP 13OCT94 If you encounter ORA-6502 then see TAR 106456.555
275: ** set process flag for the records with errors
276: */
277:
278: --Bug :3625086
279: /* update mtl_item_revisions_interface i
280: set i.process_flag = l_process_flag_3,
281: i.LAST_UPDATE_DATE = sysdate,
282: i.LAST_UPDATED_BY = decode(i.LAST_UPDATED_BY, NULL, user_id,i.LAST_UPDATED_BY),
283: i.CREATION_DATE = l_sysdate,
292: ** failed within the same set
293: */
294:
295: FOR rec IN c_item_number_err LOOP
296: UPDATE mtl_item_revisions_interface i
297: SET i.process_flag = l_process_flag_3,
298: i.LAST_UPDATE_DATE = sysdate,
299: i.LAST_UPDATED_BY = decode(i.LAST_UPDATED_BY, NULL, user_id,i.LAST_UPDATED_BY),
300: i.CREATION_DATE = l_sysdate,
312: request_id,
313: tran_id,
314: null,
315: 'item_number',
316: 'MTL_ITEM_REVISIONS_INTERFACE',
317: 'INV_IOI_ITEM_NUMBER_NO_EXIST',
318: err_text);
319:
320: IF dumm_status < 0 THEN
321: raise LOGGING_ERROR ;
322: END IF ;
323: END LOOP;
324: --End 3625086
325: update mtl_item_revisions_interface i
326: set i.process_flag = l_process_flag_3
327: where i.transaction_id in (select m.transaction_id
328: from mtl_item_revisions_interface m
329: where m.process_flag = l_process_flag_3
324: --End 3625086
325: update mtl_item_revisions_interface i
326: set i.process_flag = l_process_flag_3
327: where i.transaction_id in (select m.transaction_id
328: from mtl_item_revisions_interface m
329: where m.process_flag = l_process_flag_3
330: and (m.organization_id = org_id or
331: all_org = l_all_org )
332: and set_process_id = xset_id )
358: request_id,
359: tran_id,
360: error_msg,
361: null,
362: 'MTL_ITEM_REVISIONS_INTERFACE',
363: 'BOM_PARSE_ITEM_ERROR',
364: err_text);
365: return(SQLCODE);
366: -- return(status);