[Home] [Help]
PACKAGE BODY: APPS.CSTPACMS
Source
1 PACKAGE BODY CSTPACMS AS
2 /* $Header: CSTACMSB.pls 115.9 2002/11/08 00:57:38 awwang ship $ */
3
4 FUNCTION move_snapshot(
5 l_txn_temp_id IN NUMBER,
6 l_txn_id IN NUMBER,
7 err_num OUT NOCOPY NUMBER,
8 err_code OUT NOCOPY VARCHAR2,
9 err_msg OUT NOCOPY VARCHAR2)
10 RETURN INTEGER
11 IS
12 stmt_num NUMBER;
13
14 BEGIN
15 stmt_num := 10;
16
17 /*
18 Transfer info from cst_comp_snap_temp to cst_comp_snapshot
19 with transaction_id from MTL_MATERIAL_TRANSACTIONS
20 */
21 INSERT INTO cst_comp_snapshot
22 (transaction_id,
23 wip_entity_id,
24 operation_seq_num,
25 last_update_date,
26 last_updated_by,
27 creation_date,
28 created_by,
29 last_update_login,
30 new_operation_flag,
31 primary_quantity,
32 quantity_completed,
33 prior_completion_quantity,
34 prior_scrap_quantity,
35 request_id,
36 program_application_id,
37 program_id,
38 program_update_date)
39 SELECT
40 l_txn_id,
41 codt.wip_entity_id,
42 codt.operation_seq_num,
43 sysdate,
44 -1,
45 sysdate,
46 -1,
47 -1,
48 codt.new_operation_flag,
49 codt.primary_quantity,
50 codt.quantity_completed,
51 codt.prior_completion_quantity,
52 codt.prior_scrap_quantity,
53 -1,
54 -1,
55 -1,
56 sysdate
57 FROM
58 cst_comp_snap_temp codt
59 WHERE
60 transaction_temp_id = l_txn_temp_id;
61
62 stmt_num := 20;
63
64
65 /*
66 Delete temporary info from cst_comp_snap_temp
67 */
68 DELETE FROM cst_comp_snap_temp
69 WHERE
70 transaction_temp_id = l_txn_temp_id;
71
72 stmt_num := 30;
73
74 /*
75 Update prior_completion_quantity and prior_scrap_quantity
76 as the sum of previous primary_quantity of
77 all the previous transactions of the same job and
78 same operation
79 */
80 UPDATE cst_comp_snapshot cocd1
81 SET
82 (prior_completion_quantity, prior_scrap_quantity) =
83 (SELECT
84 NVL
85 (SUM(
86 DECODE(mmt.transaction_action_id,
87 30,0,
88 cocd2.primary_quantity)
89 ),
90 0),
91 NVL(SUM(
92 DECODE(mmt.transaction_action_id,
93 31,0,
94 32,0,
95 cocd2.primary_quantity)
96 ),
97 0)
98 FROM
99 cst_comp_snapshot cocd2,
100 mtl_material_transactions mmt
101 WHERE
102 cocd2.transaction_id < cocd1.transaction_id
103 AND cocd2.transaction_id = mmt.transaction_id
104 AND cocd2.wip_entity_id = cocd1.wip_entity_id
105 AND cocd2.operation_seq_num = cocd1.operation_seq_num
106 GROUP BY
107 cocd2.operation_seq_num
108 )
109 WHERE
110 cocd1.transaction_id = l_txn_id
111 AND
112 EXISTS
113 (
114 SELECT 'x'
115 FROM cst_comp_snapshot cocd3
116 WHERE
117 cocd3.transaction_id < l_txn_id
118 AND cocd3.wip_entity_id = cocd1.wip_entity_id
119 AND cocd3.operation_seq_num = cocd1.operation_seq_num
120 );
121
122 stmt_num := 40;
123
124 /*
125 * Update cst_comp_snapshot
126 * by setting new_operation_flag to 1
127 * if it is a new operation
128 * ie. operation_seq_num is smaller than the latter ones
129 * but the quantity_completed is also smaller.
130 */
131 UPDATE cst_comp_snapshot cocd1
132 SET
133 new_operation_flag = 1
134 WHERE
135 cocd1.transaction_id = l_txn_id
136 AND cocd1.operation_seq_num IN
137 (SELECT cocd2.operation_seq_num
138 FROM
139 cst_comp_snapshot cocd2,
140 cst_comp_snapshot cocd3
141 WHERE
142 cocd2.transaction_id = l_txn_id
143 AND cocd2.transaction_id = cocd3.transaction_id
144 AND cocd2.operation_seq_num < cocd3.operation_seq_num
145 AND cocd2.quantity_completed < cocd3.quantity_completed
146 );
147
148 return(1);
149
150 EXCEPTION
151
152 WHEN OTHERS THEN
153 err_num := SQLCODE;
154 err_msg := 'CSTPACMS:move_snapshot' || to_char(stmt_num) || substr(SQLERRM,1,150);
155 return(-999);
156
157 END move_snapshot;
158
159
160 FUNCTION validate_move_snap_to_temp(
161 l_txn_interface_id IN NUMBER,
162 l_txn_temp_id IN NUMBER,
163 l_interface_table IN NUMBER,
164 l_primary_quantity IN NUMBER,
165 err_num OUT NOCOPY NUMBER,
166 err_code OUT NOCOPY VARCHAR2,
167 err_msg OUT NOCOPY VARCHAR2)
168 RETURN INTEGER
169 IS
170 stmt_num NUMBER;
171
172 BEGIN
173 stmt_num := 10;
174
175 IF (CSTPACMS.validate_snap_interface(l_txn_interface_id,
176 l_interface_table,
177 l_primary_quantity,
178 err_num,
179 err_code,
180 err_msg) = 1) THEN
181 RETURN(CSTPACMS.move_snapshot_to_temp(l_txn_interface_id,
182 l_txn_temp_id,
183 l_interface_table,
184 err_num,
185 err_code,
186 err_msg));
187 ELSE
188 RETURN(-999);
189 END IF;
190
191 EXCEPTION
192 WHEN OTHERS THEN
193 err_num := SQLCODE;
194 err_msg := 'CSTPACMS:validate_move_snap_to_temp' || to_char(stmt_num) || substr(SQLERRM,1,150);
195 return(-999);
196 END validate_move_snap_to_temp;
197
198
199 FUNCTION validate_snap_interface(
200 l_txn_interface_id IN NUMBER,
201 l_interface_table IN NUMBER,
202 l_primary_quantity IN NUMBER,
203 err_num OUT NOCOPY NUMBER,
204 err_code OUT NOCOPY VARCHAR2,
205 err_msg OUT NOCOPY VARCHAR2)
206 RETURN INTEGER
207 IS
208 stmt_num NUMBER;
209 dummy VARCHAR2(1);
210 v_count1 NUMBER;
211 v_count2 NUMBER;
212 v_operation_seq_num NUMBER;
213 i_wip_entity_id NUMBER;
214 i_primary_quantity NUMBER;
215 i_operation_seq_num NUMBER;
216 i_transaction_action_id NUMBER;
217 l_wop_count NUMBER;
218 e_primary_quantity EXCEPTION;
219
220 CURSOR c_find_missing_op IS
221 SELECT
222 operation_seq_num
223 FROM wip_operations
224 WHERE wip_entity_id = i_wip_entity_id;
225
226 CURSOR c_mti IS
227 SELECT
228 transaction_source_id,
229 l_primary_quantity,
230 transaction_action_id
231 FROM mtl_transactions_interface
232 WHERE transaction_interface_id = l_txn_interface_id;
233
234 CURSOR c_wt IS
235 SELECT
236 wip_entity_id,
237 primary_quantity,
238 to_operation_seq_num,
239 decode(transaction_type,1,30,2,31,3,32)
240 FROM wip_move_txn_interface
241 WHERE transaction_id = l_txn_interface_id;
242
243 BEGIN
244
245 stmt_num := 5;
246
247 /* if l_interface_table = 1, information is passed from MTI
248 if l_interface_table = 2, information is passed from WIP_MOVE_TXN_INTERFACE */
249 IF l_interface_table = 1 THEN
250 OPEN c_mti;
251 FETCH c_mti INTO i_wip_entity_id,
252 i_primary_quantity,
253 i_transaction_action_id;
254 CLOSE c_mti;
255 ELSE
256 OPEN c_wt;
257 FETCH c_wt INTO i_wip_entity_id,
258 i_primary_quantity,
259 i_operation_seq_num,
260 i_transaction_action_id;
261 CLOSE c_wt;
262 END IF;
263
264 l_wop_count := 0;
265 /* If nothing in WIP operations, ignore row validation in cst_comp_snap_interface */
266 SELECT count(*)
267 INTO l_wop_count
268 FROM wip_operations
269 WHERE wip_entity_id = i_wip_entity_id;
270
271 IF (l_wop_count = 0) THEN
272 return(1);
273 END IF;
274
275 /* Check if the transaction_interface_id is the same
276 as MTI or WIP_MOVE_TXN_INTERFACE */
277 stmt_num := 10;
278 SELECT 'x'
279 INTO dummy
280 FROM cst_comp_snap_interface codt
281 WHERE transaction_interface_id = l_txn_interface_id
282 AND rownum = 1;
283
284 /* Check if the wip_entity_id is the same as
285 MTI or WIP_MOVE_TXN_INTERFACE */
286 stmt_num := 20;
287 SELECT 'x'
288 INTO dummy
289 FROM cst_comp_snap_interface codt
290 WHERE transaction_interface_id = l_txn_interface_id
291 AND wip_entity_id = i_wip_entity_id
292 AND rownum = 1;
293
294 /* Make sure OPERATION_SEQ_NUM in WIP_OPERATION
295 exists in CST_COMP_SNAP_INTERFACE */
296 stmt_num := 30;
297 OPEN c_find_missing_op;
298 LOOP
299 FETCH c_find_missing_op INTO v_operation_seq_num;
300 /* exit loop when there are no more rows to fetch */
301 EXIT WHEN c_find_missing_op%NOTFOUND;
302
303 SELECT 'x'
304 INTO dummy
305 FROM cst_comp_snap_interface codt
306 WHERE
307 codt.transaction_interface_id = l_txn_interface_id
308 AND codt.wip_entity_id = i_wip_entity_id
309 AND codt.operation_seq_num = v_operation_seq_num;
310
311 END LOOP;
312 /* free resources used by the cursor */
313 CLOSE c_find_missing_op;
314
315 stmt_num := 40;
316
317 /* Primary_quantity should be the all the same
318 and equal to primary_quantity in mmtt
319 except in for scrap transaction.
320 For scrap transaction, primary_quantity should be
321 the same until last operation seq num */
322
323 IF i_transaction_action_id = 30 THEN
324 SELECT count(*)
325 INTO v_count1
326 FROM cst_comp_snap_interface
327 WHERE transaction_interface_id = l_txn_interface_id
328 AND wip_entity_id = i_wip_entity_id
329 AND operation_seq_num <= i_operation_seq_num;
330
331 SELECT count(*)
332 INTO v_count2
333 FROM cst_comp_snap_interface
334 WHERE transaction_interface_id = l_txn_interface_id
335 AND wip_entity_id = i_wip_entity_id
336 AND operation_seq_num <= i_operation_seq_num
337 AND primary_quantity = i_primary_quantity;
338 ELSE
339 SELECT count(*)
340 INTO v_count1
341 FROM cst_comp_snap_interface
342 WHERE transaction_interface_id = l_txn_interface_id
343 AND wip_entity_id = i_wip_entity_id;
344
345 SELECT count(*)
346 INTO v_count2
347 FROM cst_comp_snap_interface
348 WHERE transaction_interface_id = l_txn_interface_id
349 AND wip_entity_id = i_wip_entity_id
350 AND primary_quantity = i_primary_quantity;
351 END IF;
352
353 IF v_count1 <> v_count2 THEN
354 RAISE e_primary_quantity;
355 END IF;
356
357
358 /* Following should be done by move_snapshot_to_temp */
359
360 /*
361 stmt_num = 60;
362 UPDATE cst_comp_snap_interface
363 SET primary_quantity =
364 decode(i_transaction_action_id,
365 30,
366 decode(sign(operation_seq_num - i_operation_seq_num),
367 1,0,
368 i_primary_quantity),
369 i_primary_quantity)
370 WHERE transaction_interface_id = l_txn_interface_id
371 AND wip_entity_id = i_wip_entity_id;
372 */
373
374 RETURN(1);
375
376
377 EXCEPTION
378 WHEN NO_DATA_FOUND THEN
379 err_num := SQLCODE;
380 IF (stmt_num = 10) THEN
381 fnd_message.set_name('BOM','CST_COMP_SNAP_INTERFACE_FAIL');
382 fnd_message.set_token(
383 token => 'COLUMN',
384 value => 'TRANSACTION_INTERFACE_ID',
385 translate => FALSE);
386 err_msg := 'CSTPACMS:validate_snap_interface ' ||
387 to_char(stmt_num) || ' : ' ||
388 fnd_message.get;
389
390 ELSIF (stmt_num = 20) THEN
391 fnd_message.set_name('BOM','CST_COMP_SNAP_INTERFACE_FAIL');
392 fnd_message.set_token(
393 token => 'COLUMN',
394 value => 'WIP_ENTITY_ID',
395 translate => FALSE);
396 err_msg := 'CSTPACMS:validate_snap_interface ' ||
397 to_char(stmt_num) || ' : ' ||
398 fnd_message.get;
399
400 ELSIF (stmt_num = 30) THEN
401 fnd_message.set_name('BOM','CST_COMP_SNAP_INTERFACE_FAIL');
402 fnd_message.set_token(
403 token => 'COLUMN',
404 value => 'OPERATION_SEQ_NUM',
405 translate => FALSE);
406 err_msg := 'CSTPACMS:validate_snap_interface ' ||
407 to_char(stmt_num) || ' : ' ||
408 fnd_message.get;
409
410 END IF;
411 RETURN(-999);
412
413 WHEN e_primary_quantity THEN
414 err_num := SQLCODE;
415 fnd_message.set_name('BOM','CST_COMP_SNAP_INTERFACE_FAIL');
416 fnd_message.set_token(
417 token => 'COLUMN',
418 value => 'PRIMARY_QUANTITY',
419 translate => FALSE);
420 err_msg := 'CSTPACMS:validate_snap_interface ' ||
421 to_char(stmt_num) || ' : ' ||
422 fnd_message.get;
423 RETURN(-999);
424
425 WHEN OTHERS THEN
426 err_num := SQLCODE;
427 err_msg := 'CSTPACMS:validate_snap_interface ' ||
428 to_char(stmt_num) ||' '|| substr(SQLERRM,1,150);
429 RETURN(-999);
430
431 END validate_snap_interface;
432
433
434
435 FUNCTION move_snapshot_to_temp(
436 l_txn_interface_id IN NUMBER,
437 l_txn_temp_id IN NUMBER,
438 l_interface_table IN NUMBER,
439 err_num OUT NOCOPY NUMBER,
440 err_code OUT NOCOPY VARCHAR2,
441 err_msg OUT NOCOPY VARCHAR2)
442 RETURN INTEGER
443 IS
444 stmt_num NUMBER;
445 i_operation_seq_num NUMBER;
446 i_transaction_action_id NUMBER;
447
448 CURSOR c_mti IS
449 SELECT
450 transaction_action_id
451 FROM mtl_transactions_interface
452 WHERE transaction_interface_id = l_txn_interface_id;
453
454 CURSOR c_wt IS
455 SELECT
456 to_operation_seq_num,
457 decode(transaction_type,1,30,2,31,3,32)
458 FROM wip_move_txn_interface
459 WHERE transaction_id = l_txn_interface_id;
460
461
462 BEGIN
463 stmt_num := 10;
464 IF l_interface_table = 1 THEN
465 OPEN c_mti;
466 FETCH c_mti INTO i_transaction_action_id;
467 CLOSE c_mti;
468 ELSE
469 OPEN c_wt;
470 FETCH c_wt INTO i_operation_seq_num,
471 i_transaction_action_id;
472 CLOSE c_wt;
473 END IF;
474
475 stmt_num := 15;
476 /*
477 Transfer info from cst_comp_snap_interface to cst_comp_snap_temp
478 with transaction_id from MTL_MATERIAL_TRANSACTIONS
479 */
480 INSERT INTO cst_comp_snap_temp
481 (transaction_temp_id,
482 wip_entity_id,
483 operation_seq_num,
484 last_update_date,
485 last_updated_by,
486 creation_date,
487 created_by,
488 last_update_login,
489 new_operation_flag,
490 primary_quantity,
491 quantity_completed,
492 prior_completion_quantity,
493 prior_scrap_quantity,
494 request_id,
495 program_application_id,
496 program_id,
497 program_update_date)
498 SELECT
499 l_txn_temp_id,
500 codt.wip_entity_id,
501 codt.operation_seq_num,
502 sysdate,
503 -1,
504 sysdate,
505 -1,
506 -1,
507 --codt.new_operation_flag,
508 -- we assume this is not new operation flag
509 -- the following package will change it if necessary.
510 2,
511 --
512 -- Bug 608310
513 -- If the transaction is a scrap transaction
514 -- Operation after the scrap operation should be
515 -- primary_quantity of zero.
516 --
517 decode(i_transaction_action_id,
518 30,
519 decode(sign(operation_seq_num - i_operation_seq_num),
520 1,0,
521 codt.primary_quantity),
522 codt.primary_quantity),
523 codt.quantity_completed,
524 codt.prior_completion_quantity,
525 codt.prior_scrap_quantity,
526 -1,
527 -1,
528 -1,
529 sysdate
530 FROM
531 cst_comp_snap_interface codt
532 WHERE
533 transaction_interface_id = l_txn_interface_id;
534
535 stmt_num := 20;
536 /*
537 Delete temporary info from cst_comp_snap_interface
538 */
539 DELETE FROM cst_comp_snap_interface
540 WHERE
541 transaction_interface_id = l_txn_interface_id;
542
543 RETURN(1);
544
545 EXCEPTION
546 WHEN OTHERS THEN
547 err_num := SQLCODE;
548 err_msg := 'CSTPACMS:move_snapshot_to_temp' || to_char(stmt_num) || substr(SQLERRM,1,150);
549 RETURN(-999);
550 END move_snapshot_to_temp;
551
552 END CSTPACMS;