[Home] [Help]
PACKAGE BODY: APPS.WIP_TRANSACTIONS_PKG
Source
1 PACKAGE BODY WIP_TRANSACTIONS_PKG as
2 /* $Header: wiptxnsb.pls 115.12 2004/07/29 10:36:19 panagara ship $ */
3
4 SUCCESS constant number := 0;
5 FAILURE constant number := -1;
6
7 procedure mov_cleanup(
8 mov_group_id in number,
9 res_group_id in number,
10 mtl_header_id in number,
11 bf_page in number,
12 save_point in varchar2,
13 err_code out NOCOPY number,
14 err_app out NOCOPY varchar2,
15 err_msg out NOCOPY varchar2) is
16
17 move_recs wip_move_txn_interface_cleanup.WIP_MOVE_TXN_INTERFACE_REC;
18 alloc_recs wip_move_alloc_cleanup.WIP_MOVE_TXN_ALLOCATIONS_REC;
19 mtl_temp_recs wip_mtl_txns_temp_cleanup.MTL_TRANSACTIONS_TEMP_REC;
20 sn_temp_recs wip_serial_temp_cleanup.MTL_SERIAL_NUMBERS_TEMP_REC;
21 lt_temp_recs wip_lot_temp_cleanup.MTL_TRANSACTION_LOTS_TEMP_REC;
22 sn_recs wip_serial_number_cleanup.MTL_SERIAL_NUMBERS_REC;
23 sn_marks wip_serial_number_cleanup.MTL_SERIAL_NUMBERS_MARK_REC;
24
25 cursor overcpl_move_id is
26 select wmti1.transaction_id
27 from wip_move_txn_interface wmti,
28 wip_move_txn_interface wmti1
29 where wmti.group_id = mov_group_id
30 and wmti.overcompletion_transaction_id is not NULL
31 AND wmti.overcompletion_transaction_qty > 0
32 and wmti1.overcompletion_transaction_id = wmti.overcompletion_transaction_id;
33
34 oc_mov_group_id NUMBER;
35 oc_move_recs wip_move_txn_interface_cleanup.WIP_MOVE_TXN_INTERFACE_REC;
36 oc_alloc_recs wip_move_alloc_cleanup.WIP_MOVE_TXN_ALLOCATIONS_REC;
37
38 x_retcode number;
39 x_app varchar2(3);
40 x_msg varchar2(30);
41 begin
42 -- initialize
43 err_code := SUCCESS;
44 err_app := NULL;
45 err_msg := NULL;
46
47 move_recs.numrecs := 0;
48 alloc_recs.numrecs := 0;
49 mtl_temp_recs.numrecs := 0;
50 sn_temp_recs.numrecs := 0;
51 lt_temp_recs.numrecs := 0;
52
53 oc_move_recs.numrecs := 0;
54 oc_alloc_recs.numrecs := 0;
55
56 oc_mov_group_id := NULL;
57 open overcpl_move_id;
58
59 loop
60 fetch overcpl_move_id into oc_mov_group_id;
61 exit when overcpl_move_id%NOTFOUND;
62 if (oc_mov_group_id IS NOT NULL) THEN
63 -- Overcompletion
64 -- No manual WCTI transactions will be there for child transaction
65 -- and Move processing hasn't happened for child yet.
66
67 wip_move_alloc_cleanup.fetch_and_delete
68 (p_mov_grp_id => oc_mov_group_id,
69 p_mov_allocs => oc_alloc_recs);
70
71 wip_move_txn_interface_cleanup.fetch_and_delete
72 (p_grp_id => oc_mov_group_id,
73 p_moves => oc_move_recs);
74
75 end if;
76 end loop;
77
78 if (res_group_id > 0) then
79 delete wip_cost_txn_interface
80 where group_id = res_group_id;
81 end if;
82
83 if (mtl_header_id > 0) then
84 wip_move_alloc_cleanup.fetch_and_delete(
85 p_mov_grp_id => mov_group_id,
86 p_mov_allocs => alloc_recs);
87
88 wip_mtl_txns_temp_cleanup.fetch_and_delete(
89 p_hdr_id => mtl_header_id,
90 p_act_id => NULL,
91 p_materials => mtl_temp_recs,
92 p_lots => lt_temp_recs,
93 p_serials => sn_temp_recs,
94 p_dyn_serials => sn_recs,
95 p_ser_marks => sn_marks);
96 end if;
97
98 wip_move_txn_interface_cleanup.fetch_and_delete(
99 p_grp_id => mov_group_id,
100 p_moves => move_recs);
101
102 commit;
103
104 if (bf_page = 2) then
105 wip_utilities.do_sql('SAVEPOINT ' || save_point);
106 wip_move_txn_interface_cleanup.insert_rows(move_recs);
107
108 if (mtl_header_id > 0) then
109 wip_mtl_txns_temp_cleanup.insert_rows(
110 p_materials => mtl_temp_recs,
111 p_lots => lt_temp_recs,
112 p_serials => sn_temp_recs,
113 p_dyn_serials => sn_recs,
114 p_ser_marks => sn_marks,
115 p_retcode => x_retcode,
116 p_app => x_app,
117 p_msg => x_msg);
118
119 if (x_retcode <> wip_serial_number_cleanup.SUCCESS) then
120 rollback;
121 err_code := FAILURE;
122 err_app := x_app;
123 err_msg := x_msg;
124 return;
125 end if;
126
127 wip_move_alloc_cleanup.insert_rows(alloc_recs);
128 end if;
129 end if;
130
131 err_code := SUCCESS;
132
133 exception
134 when others then
135 err_code := FAILURE;
136 err_app := 'INV';
137 err_msg := 'INV_RPC_CLEANUP_ERROR';
138 end mov_cleanup;
139
140 procedure mtl_cleanup(
141 mtl_header_id in number,
142 entry_sp in varchar2,
143 err_code out NOCOPY number,
144 err_app out NOCOPY varchar2,
145 err_msg out NOCOPY varchar2) is
146 mtl_tmp_recs wip_mtl_txns_temp_cleanup.MTL_TRANSACTIONS_TEMP_REC;
147 mtl_sn_tmp_recs wip_serial_temp_cleanup.MTL_SERIAL_NUMBERS_TEMP_REC;
148 mtl_lt_tmp_recs wip_lot_temp_cleanup.MTL_TRANSACTION_LOTS_TEMP_REC;
149 mtl_dyn_sn_recs wip_serial_number_cleanup.MTL_SERIAL_NUMBERS_REC;
150 mtl_sn_mrks wip_serial_number_cleanup.MTL_SERIAL_NUMBERS_MARK_REC;
151
152 x_retcode number;
153 x_app varchar2(3);
154 x_msg varchar2(30);
155 begin
156 -- get material records
157 wip_mtl_txns_temp_cleanup.fetch_and_delete(
158 p_hdr_id => mtl_header_id,
159 p_act_id => NULL,
160 p_materials => mtl_tmp_recs,
161 p_lots => mtl_lt_tmp_recs,
162 p_serials => mtl_sn_tmp_recs,
163 p_dyn_serials => mtl_dyn_sn_recs,
164 p_ser_marks => mtl_sn_mrks);
165
166 commit;
167
168 wip_utilities.do_sql('SAVEPOINT ' || entry_sp);
169
170 -- insert material records
171 wip_mtl_txns_temp_cleanup.insert_rows(
172 p_materials => mtl_tmp_recs,
173 p_lots => mtl_lt_tmp_recs,
174 p_serials => mtl_sn_tmp_recs,
175 p_dyn_serials => mtl_dyn_sn_recs,
176 p_ser_marks => mtl_sn_mrks,
177 p_retcode => x_retcode,
178 p_app => x_app,
179 p_msg => x_msg);
180
181 if (x_retcode <> wip_serial_number_cleanup.SUCCESS) then
182 rollback;
183 err_code := FAILURE;
184 err_app := x_app;
185 err_msg := x_msg;
186 return;
187 end if;
188
189 err_code := SUCCESS;
190
191 exception
192 when others then
193 err_code := FAILURE;
194 err_app := 'INV';
195 err_msg := 'INV_RPC_CLEANUP_ERROR';
196 end mtl_cleanup;
197
198 PROCEDURE cmp_cleanup(
199 mtl_header_id IN NUMBER,
200 action_id IN NUMBER,
201 criteria_sp IN VARCHAR2,
202 entry_sp IN VARCHAR2,
203 insert_sp IN VARCHAR2,
204 bf_page IN NUMBER,
205 err_code OUT NOCOPY NUMBER,
206 err_app OUT NOCOPY VARCHAR2,
207 err_msg OUT NOCOPY VARCHAR2) IS
208
209 cmp_mtl_tmp_recs wip_mtl_txns_temp_cleanup.MTL_TRANSACTIONS_TEMP_REC;
210 cmp_sn_tmp_recs wip_serial_temp_cleanup.MTL_SERIAL_NUMBERS_TEMP_REC;
211 cmp_lt_tmp_recs wip_lot_temp_cleanup.MTL_TRANSACTION_LOTS_TEMP_REC;
212 cmp_dyn_sn_recs wip_serial_number_cleanup.MTL_SERIAL_NUMBERS_REC;
213 cmp_sn_mrks wip_serial_number_cleanup.MTL_SERIAL_NUMBERS_MARK_REC;
214
215 bf_mtl_tmp_recs wip_mtl_txns_temp_cleanup.MTL_TRANSACTIONS_TEMP_REC;
216 bf_sn_tmp_recs wip_serial_temp_cleanup.MTL_SERIAL_NUMBERS_TEMP_REC;
217 bf_lt_tmp_recs wip_lot_temp_cleanup.MTL_TRANSACTION_LOTS_TEMP_REC;
218 bf_dyn_sn_recs wip_serial_number_cleanup.MTL_SERIAL_NUMBERS_REC;
219 bf_sn_mrks wip_serial_number_cleanup.MTL_SERIAL_NUMBERS_MARK_REC;
220
221 -- Overcompletion
222 oc_move_recs wip_move_txn_interface_cleanup.WIP_MOVE_TXN_INTERFACE_REC;
223 oc_alloc_recs wip_move_alloc_cleanup.WIP_MOVE_TXN_ALLOCATIONS_REC;
224
225 cursor overcpl_move_id is
226 select wmti.transaction_id
227 from mtl_material_transactions_temp mmtt,
228 wip_move_txn_interface wmti
229 where mmtt.TRANSACTION_HEADER_ID = mtl_header_id
230 and mmtt.overcompletion_transaction_id is not null
231 and wmti.overcompletion_transaction_id = mmtt.overcompletion_transaction_id;
232
233 oc_mov_group_id NUMBER;
234 x_retcode number;
235 x_app varchar2(3);
236 x_msg varchar2(30);
237 BEGIN
238
239 -- get Overcompletion Move records
240 oc_mov_group_id := NULL;
241 OPEN overcpl_move_id;
242 FETCH overcpl_move_id into oc_mov_group_id;
243 CLOSE overcpl_move_id;
244
245 if (oc_mov_group_id IS NOT NULL) THEN
246 -- Overcompletion
247 -- No WCTI transactions will be there since, no Manual charges are allowed
248 -- and Move processing hasn't happened.
249
250 wip_move_alloc_cleanup.fetch_and_delete
251 (p_mov_grp_id => oc_mov_group_id,
252 p_mov_allocs => oc_alloc_recs);
253
254 wip_move_txn_interface_cleanup.fetch_and_delete
255 (p_grp_id => oc_mov_group_id,
256 p_moves => oc_move_recs);
257 end if;
258
259 -- get completion records
260 wip_mtl_txns_temp_cleanup.fetch_and_delete(
261 p_hdr_id => mtl_header_id,
262 p_act_id => action_id,
263 p_materials => cmp_mtl_tmp_recs,
264 p_lots => cmp_lt_tmp_recs,
265 p_serials => cmp_sn_tmp_recs,
266 p_dyn_serials => cmp_dyn_sn_recs,
267 p_ser_marks => cmp_sn_mrks);
268
269 -- get rest of material records
270 wip_mtl_txns_temp_cleanup.fetch_and_delete(
271 p_hdr_id => mtl_header_id,
272 p_act_id => NULL,
273 p_materials => bf_mtl_tmp_recs,
274 p_lots => bf_lt_tmp_recs,
275 p_serials => bf_sn_tmp_recs,
276 p_dyn_serials => bf_dyn_sn_recs,
277 p_ser_marks => bf_sn_mrks);
278
279 commit;
280
281 wip_utilities.do_sql('SAVEPOINT ' || criteria_sp);
282
283 -- post lot and serial records
284 wip_lot_temp_cleanup.insert_rows(p_lots => cmp_lt_tmp_recs);
285 wip_serial_temp_cleanup.insert_rows(p_serials => cmp_sn_tmp_recs);
286 wip_serial_number_cleanup.insert_rows(p_serials => cmp_dyn_sn_recs);
287 wip_serial_number_cleanup.mark(
288 p_serials => cmp_sn_mrks,
289 p_retcode => x_retcode);
290 if (x_retcode <> wip_serial_number_cleanup.SUCCESS) then
291 rollback;
292 err_code := x_retcode;
293 err_app := 'INV';
294 err_msg := 'INV_RPC_CLEANUP_ERROR';
295 return;
296 end if;
297
298 wip_utilities.do_sql('SAVEPOINT ' || entry_sp);
299
300 -- post completion records
301 wip_mtl_txns_temp_cleanup.insert_rows(p_mtls => cmp_mtl_tmp_recs);
302
303 IF ( oc_move_recs.numrecs > 0 ) THEN
304 -- Overcompletion
305 wip_move_txn_interface_cleanup.insert_rows(oc_move_recs);
306 END IF;
307
308 wip_utilities.do_sql('SAVEPOINT ' || insert_sp);
309
310 if (bf_page = 2) then
311 -- insert backflush records
312 wip_mtl_txns_temp_cleanup.insert_rows(
313 p_materials => bf_mtl_tmp_recs,
314 p_lots => bf_lt_tmp_recs,
315 p_serials => bf_sn_tmp_recs,
316 p_dyn_serials => bf_dyn_sn_recs,
317 p_ser_marks => bf_sn_mrks,
318 p_retcode => x_retcode,
319 p_app => x_app,
320 p_msg => x_msg);
321 if (x_retcode <> wip_serial_number_cleanup.SUCCESS) then
322 rollback;
323 err_code := FAILURE;
324 err_app := x_app;
325 err_msg := x_msg;
326 return;
327 end if;
328
329 IF (oc_alloc_recs.numrecs > 0 ) THEN
330 -- Overcompletion
331 wip_move_alloc_cleanup.insert_rows(oc_alloc_recs);
332 END IF;
333
334 end if;
335
336 err_code := SUCCESS;
337
338 exception
339 when others then
340 err_code := FAILURE;
341 err_app := 'INV';
342 err_msg := 'INV_RPC_CLEANUP_ERROR';
343 end cmp_cleanup;
344
345 procedure cleanup(
346 mov_group_id in number,
347 res_group_id in number,
348 mtl_header_id in number) is
349 begin
350
351 if (mov_group_id > 0) then
352
353 delete wip_move_txn_allocations
354 where transaction_id in
355 (select transaction_id
356 from wip_move_txn_interface
357 where group_id = mov_group_id);
358
359 delete wip_move_txn_interface
360 where group_id = mov_group_id;
361
362 end if;
363
364 if (res_group_id > 0) then
365 delete wip_cost_txn_interface
366 where group_id = res_group_id;
367 end if;
368
369 if (mtl_header_id > 0) then
370
371 -- Delete predefined serial numbers
372 delete mtl_serial_numbers
373 where group_mark_id = mtl_header_id
374 and current_status = 6;
375
376 -- Unmark serial numbers
377 update mtl_serial_numbers
378 set group_mark_id = null,
379 line_mark_id = null,
380 lot_line_mark_id = null
381 where group_mark_id = mtl_header_id;
382
383 -- Delete lot and serial records from temp tables
384 delete mtl_serial_numbers_temp
385 where group_header_id = mtl_header_id;
386
387 delete mtl_transaction_lots_temp
388 where group_header_id = mtl_header_id;
389
390 delete mtl_material_transactions_temp
391 where transaction_header_id = mtl_header_id;
392
393 end if;
394
395 commit;
396
397 end cleanup;
398
399 FUNCTION rec_count_MMTT (mtl_hdr_id IN NUMBER) return NUMBER IS
400 ccount NUMBER;
401 BEGIN
402 select count(*)
403 into ccount
404 from mtl_material_transactions_temp
405 where transaction_header_id = mtl_hdr_id;
406
407 RETURN ccount;
408
409 END rec_count_MMTT;
410
411 Procedure cln_up_MMTT(txn_hdr_id in number) is
412 begin
413 delete mtl_transaction_lots_temp
414 where transaction_temp_id in
415 (select transaction_temp_id
416 from mtl_material_transactions_temp
417 where transaction_header_id=txn_hdr_id
418 and transaction_mode=1);
419 delete mtl_serial_numbers_temp where
420 transaction_temp_id in
421 (select transaction_temp_id
422 from mtl_material_transactions_temp
423 where transaction_header_id=txn_hdr_id
424 and transaction_mode=1);
425 delete mtl_material_transactions_temp
426 where transaction_header_id=txn_hdr_id
427 and transaction_mode=1;
428 commit;
429 end cln_up_MMTT ;
430
431 procedure cln_up_MTI (txn_hdr_id in number) is
432
433 completion_count1 number := 0 ;
434 completion_count2 number := 0 ;
435 l_bind1 number;
436 l_bind2 number;
437 l_bind3 number;
438 l_bind4 number;
439
440 begin
441 /* Performance Bug 3788705. Use bind variables so that single parse would be
442 enough for the following 2 queries. */
443 l_bind1 := 17;
444 l_bind2 := 44;
445 l_bind3 := 90;
446 l_bind4 := NULL;
447 select count(*)
448 into completion_count1
449 from mtl_transactions_interface
450 where transaction_header_id = txn_hdr_id
451 and transaction_type_id in (l_bind1, l_bind2, l_bind3, l_bind4) ;
452 l_bind1 := 35;
453 l_bind2 := 43;
454 l_bind3 := 38;
455 l_bind4 := 48;
456 select count(*)
457 into completion_count2
458 from mtl_transactions_interface
459 where transaction_header_id = txn_hdr_id
460 and transaction_type_id in (l_bind1, l_bind2, l_bind3, l_bind4) ;
461
462 if ((completion_count1 = 0) and (completion_count2 > 0)) then
463
464 delete from mtl_transaction_lots_interface
465 where transaction_interface_id in
466 ( select transaction_interface_id from
467 mtl_transactions_interface
468 where transaction_header_id = txn_hdr_id
469 and transaction_type_id in (35, 43, 38, 48)
470 ) ;
471
472 delete from mtl_serial_numbers_interface
473 where transaction_interface_id in
474 ( select transaction_interface_id from
475 mtl_transactions_interface
476 where transaction_header_id = txn_hdr_id
477 and transaction_type_id in (35, 43, 38, 48)
478 ) ;
479
480 delete from mtl_transactions_interface
481 where transaction_header_id = txn_hdr_id
482 and transaction_type_id in (35, 43, 38, 48) ;
483
484 commit ;
485 end if ;
486 END ;
487 end WIP_TRANSACTIONS_PKG;