[Home] [Help]
PACKAGE BODY: APPS.CSTPSMCW
Source
1 PACKAGE BODY CSTPSMCW AS
2 /* $Header: CSTSMCWB.pls 120.2 2006/06/14 10:26:28 sikhanna noship $ */
3
4 G_PKG_NAME VARCHAR2(240) := 'CSTPSMCW';
5 l_debug_flag VARCHAR2(1) := FND_PROFILE.VALUE('MRP_DEBUG');
6
7 ----------------------------------------------------------------------------
8 -- PROCEDURE
9 -- LOT_TXN_COST_PROCESSOR
10 -- --
11 -- DESCRIPTION --
12 -- This procedure was called by the Lot Based Transaction Cost Worker
13 -- to cost WSM Txns prior to BOM patchset I(11.5.9). This is obseleted.
14 -- The procedure is retained for future release to allow the program to
15 -- exit cleanly in the event that it is launched by a user.
16 ----------------------------------------------------------------------------
17 ----------------------------------------------------------------------------
18 -- Changes for Pre-Req patch for Pre 11i.9 customers
19 -- In 11.5.9, two important changes have been done:
20 -- 1. OSFM updates Quantity Issued Columns
21 -- 2. OSFM inserts transactions in MMT that are picked by the
22 -- cost processor
23 -- For customers, that are Pre 11i9 and need to use the new lot
24 -- transaction costing code, the above has to be done.
25 ----------------------------------------------------------------------------
26 ----------------------------------------------------------------------------
27 --
28 -- PURPOSE: --
29 -- OSFM Lot Transactions Costing for Oracle Applications Rel 11i.9
30 -- --
31 -- HISTORY: --
32 -- August-2002 Vinit Obsoleted --
33 ----------------------------------------------------------------------------
34 PROCEDURE LOT_TXN_COST_PROCESSOR (RETCODE OUT NOCOPY number,
35 ERRBUF OUT NOCOPY varchar2,
36 p_org_id in number,
37 p_group_id in number)IS
38
39 -- Flag indicating if the transaction is already inserted into MMT
40 l_txn_mmt_flag NUMBER := 0;
41 l_req_id NUMBER := -1;
42 l_user_id NUMBER ;
43 l_login_id NUMBER;
44 l_request_id NUMBER ;
45 l_prog_appl_id NUMBER ;
46 l_program_id NUMBER ;
47 l_cmcicu_prog_id NUMBER;
48 l_interval NUMBER := 5;
49 l_maxwait NUMBER := 120;
50
51 l_phase varchar(300);
52 l_status varchar(300);
53 l_dev_phase varchar(300);
54 l_dev_status varchar(300);
55 l_message varchar(300);
56 l_cmcicu_status boolean;
57
58 conc_status BOOLEAN;
59 l_err_code VARCHAR2(8000);
60 l_err_msg VARCHAR2(8000);
61
62 l_error_code NUMBER := 0;
63 l_error_buf VARCHAR2(240) := '';
64 l_txn_type_id NUMBER;
65 l_stmt_num NUMBER := 0;
66
67 CST_CSTPSMCW_RUNNING EXCEPTION;
68 CST_CMCCCU_ORG_RUNNING EXCEPTION;
69 INSERT_MMT_FAILURE EXCEPTION;
70 UPDATE_QUANTITY_ISSUE_FAILURE EXCEPTION;
71
72
73 CURSOR c_uncost_sm_txn IS
74 SELECT transaction_id,
75 transaction_type_id,
76 organization_id,
77 transaction_date
78 FROM wsm_split_merge_transactions
79 WHERE costed = WIP_CONSTANTS.PENDING
80 AND status = WIP_CONSTANTS.COMPLETED
81 AND organization_id = p_org_id
82 AND group_id = p_group_id
83 ORDER BY transaction_date,transaction_id;
84
85
86 BEGIN
87
88 /* For 11i9 and above, return */
89 IF WSMPVERS.GET_OSFM_RELEASE_VERSION >= '110509' THEN
90 FND_FILE.put_line(fnd_file.log, 'This program is no longer supported');
91 RETURN;
92 ELSE
93 /* Pre 11i.9 Processor */
94 IF l_debug_flag = 'Y' THEN
95 FND_FILE.put_line(fnd_file.log, 'Pre 11i.9 OSFM');
96 END IF;
97
98 l_user_id := FND_GLOBAL.USER_ID;
99 l_login_id := FND_GLOBAL.LOGIN_ID;
100 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
101 l_prog_appl_id := FND_GLOBAL.PROG_APPL_ID;
102 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
103
104 l_err_msg := '';
105 l_err_code := '';
106
107 /* Check if another Lot based Cost Manager is Running */
108 l_stmt_num := 10;
109
110 SELECT nvl(max(fcr.request_id), -1)
111 INTO l_req_id
112 FROM fnd_concurrent_requests fcr
113 WHERE program_application_id = 702
114 AND concurrent_program_id = l_program_id
115 AND argument1 = TO_CHAR(p_org_id)
116 AND phase_code <> 'C'
117 AND fcr.request_id <> l_request_id;
118
119 IF (l_req_id <> -1) THEN
120 RAISE CST_CSTPSMCW_RUNNING;
121 END IF;
122
123 /* Check if a Standard Cost Update is Running */
124 l_stmt_num := 20;
125 SELECT concurrent_program_id
126 INTO l_cmcicu_prog_id
127 FROM fnd_concurrent_programs fcp
128 WHERE fcp.application_id = 702
129 AND fcp.concurrent_program_name = 'CMCICU';
130
131 l_req_id := -1;
132
133 l_stmt_num := 30;
134 SELECT nvl(max(fcr.request_id), -1)
135 INTO l_req_id
136 FROM fnd_concurrent_requests fcr
137 WHERE program_application_id = 702
138 AND concurrent_program_id = l_cmcicu_prog_id
139 AND argument1 = TO_CHAR(p_org_id)
140 AND phase_code = 'R';
141
142 l_stmt_num := 40;
143
144 IF (l_req_id <> -1) THEN
145 l_cmcicu_status := FND_CONCURRENT.WAIT_FOR_REQUEST(l_req_id,
146 l_interval,
147 l_maxwait,
148 l_phase,
149 l_status,
150 l_dev_phase,
151 l_dev_status,
152 l_message);
153 IF (NOT (l_dev_phase = 'COMPLETE') and (l_dev_status = 'NORMAL')) THEN
154 RAISE CST_CMCCCU_ORG_RUNNING;
155 END IF;
156 END IF;
157
158 /* All transactions of type update_assembly,update_routing must
159 be set to costed for the given organization and group
160 These have no costing impact */
161
162 l_stmt_num := 50;
163
164 UPDATE wsm_split_merge_transactions
165 SET costed = WIP_CONSTANTS.COMPLETED
166 WHERE transaction_type_id in (3,5,7)
167 AND costed = WIP_CONSTANTS.PENDING
168 AND status = WIP_CONSTANTS.COMPLETED
169 AND organization_id = p_org_id
170 AND group_id = p_group_id;
171
172 l_stmt_num := 60;
173
174 FOR c_uncost_rec in c_uncost_sm_txn LOOP
175 SAVEPOINT START_OF_LOOP;
176 /* Check if the transaction is not already in MMT */
177 /* There is no index on source_line_id in MMT
178 Hence this query is used to utilize existing index range
179 scans */
180 SELECT count(*)
181 INTO l_txn_mmt_flag
182 FROM mtl_material_transactions mmt,
183 wsm_split_merge_transactions wsmt
184 WHERE
185 (transaction_source_id in
186 (select wip_entity_id
187 from wsm_sm_resulting_jobs wsrj
188 where wsrj.transaction_id = wsmt.transaction_id)
189 or
190 transaction_source_id in
191 (select wip_entity_id
192 from wsm_sm_starting_jobs wssj
193 where wssj.transaction_id = wsmt.transaction_id))
194 AND mmt.organization_id = wsmt.organization_id
195 AND mmt.source_line_id = wsmt.transaction_id
196 AND wsmt.transaction_id = c_uncost_rec.transaction_id;
197
198 /* If it doesn't, call API's to insert transaction into MMT
199 and update quantity */
200 IF l_txn_mmt_flag = 0 THEN
201 IF ( l_debug_flag = 'Y' ) THEN
202 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting Transaction into MMT');
203 END IF;
204
205 -- Insert
206 WSM_JobCosting_GRP.Insert_MaterialTxn (
207 c_uncost_rec.transaction_id,
208 l_error_code,
209 l_error_buf );
210 IF l_error_code <> 0 THEN
211 RAISE INSERT_MMT_FAILURE;
212 END IF;
213
214 -- FIND TRANSACTION_TYPE
215 SELECT TRANSACTION_TYPE_ID
216 INTO l_txn_type_id
217 FROM WSM_SPLIT_MERGE_TRANSACTIONS
218 WHERE transaction_id = c_uncost_rec.transaction_id;
219
220 -- Update
221 IF ( l_debug_flag = 'Y' ) THEN
222 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating Quantities');
223 END IF;
224 WSM_JobCosting_GRP.Update_QtyIssued (
225 c_uncost_rec.transaction_id,
226 l_txn_type_id,
227 l_error_code,
228 l_error_buf );
229 IF l_error_code <> 0 THEN
230 RAISE UPDATE_QUANTITY_ISSUE_FAILURE;
231 END IF;
232 IF ( l_debug_flag = 'Y' ) THEN
233 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Check Material Transactions form to find if the transaction is costed');
234 END IF;
235 END IF;
236
237 END LOOP;
238
239 END IF;
240
241 EXCEPTION
242 WHEN CST_CMCCCU_ORG_RUNNING THEN
243 ROLLBACK;
244 l_err_code := SUBSTR('CSTPSMCW.lot_txn_cost_processor('
245 || to_char(l_stmt_num)
246 || '): - 24003 '
247 || 'Req_id: '
248 || TO_CHAR(l_req_id)
249 || ' . ',1,240);
250
251 fnd_message.set_name('BOM', 'CST_CMCCCU_ORG_RUNNING');
252 l_err_msg := fnd_message.get;
253 l_err_msg := SUBSTR(l_err_msg,1,240);
254 FND_FILE.PUT_LINE(fnd_file.log,SUBSTR(l_err_code
255 ||' '
256 ||l_err_msg,1,240));
257
258 WHEN CST_CSTPSMCW_RUNNING THEN
259 ROLLBACK;
260 l_err_code := SUBSTR('CSTPSMCW.lot_txn_cost_processor('
261 || to_char(l_stmt_num)
262 || '): - 24143 '
263 || 'Req_id: '
264 || TO_CHAR(l_req_id)
265 || ' . ',1,240);
266
267 fnd_message.set_name('BOM', 'CST_CSTPSMCW_RUNNING');
268 l_err_msg := fnd_message.get;
269 l_err_msg := SUBSTR(l_err_msg,1,240);
270 FND_FILE.PUT_LINE(fnd_file.log,SUBSTR(l_err_code
271 ||' '
272 ||l_err_msg,1,240));
273
274 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
275
276 WHEN UPDATE_QUANTITY_ISSUE_FAILURE THEN
277 ROLLBACK;
278 l_err_msg := SUBSTR('CSTPSMCW.LOT_COST_TXN_PROCESSOR('
279 || to_char(l_stmt_num)
280 || '): '
281 ||SQLERRM,1,240
282 ||': '||l_error_buf);
283 FND_FILE.PUT_LINE(fnd_file.log,l_err_msg);
284
285 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
286
287 WHEN INSERT_MMT_FAILURE THEN
288 ROLLBACK;
289 l_err_msg := SUBSTR('CSTPSMCW.LOT_COST_TXN_PROCESSOR('
290 || to_char(l_stmt_num)
291 || '): '
292 ||SQLERRM,1,240
293 ||': '||l_error_buf);
294 FND_FILE.PUT_LINE(fnd_file.log,l_err_msg);
295
296 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
297
298 WHEN OTHERS THEN
299 ROLLBACK;
300 l_err_code := NULL;
301 l_err_msg := SUBSTR('CSTPSMCW.lot_txn_cost_processor('
302 || to_char(l_stmt_num)
303 || '): '
304 ||SQLERRM,1,240);
305
306 FND_FILE.PUT_LINE(fnd_file.log,l_err_msg);
307
308 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
309
310 END lot_txn_cost_processor;
311
312 ----------------------------------------------------------------------------
313 -- FUNCTION --
314 -- UPDATE_WSMT_TXN_STATUS --
315 -- --
316 -- DESCRIPTION --
317 -- This function updates WSM_SPLIT_MERGE_TRANSACTIONS with the status --
318 -- of the costed column (COSTED/ERROR) --
319 -- PURPOSE: --
320 -- OSFM Lot Transactions Costing for Oracle Applications Rel 11i.8
321 -- --
322 -- PARAMETERS: --
323 -- p_txn_id - Transaction to be updated --
324 -- p_costed - costed flag (COSTED/ERROR)
325 -- p_request_id
326 -- p_prog_appl_id
327 -- p_prog_id - Concurrent WHO parameters.
328 -- HISTORY: --
329 -- August-2002 Vinit Creation --
330 ----------------------------------------------------------------------------
331
332 FUNCTION UPDATE_WSMT_TXN_STATUS
333 ( p_txn_id IN NUMBER,
334 p_costed IN NUMBER,
335 p_error_message IN VARCHAR2,
336 p_request_id IN NUMBER,
337 p_prog_appl_id IN NUMBER,
338 p_prog_id IN NUMBER )
339 RETURN BOOLEAN IS
340 BEGIN
341
342 UPDATE wsm_split_merge_transactions
343 SET costed = p_costed,
344 error_message = p_error_message,
345 request_id = p_request_id,
346 program_application_id = p_prog_appl_id,
347 program_id = p_prog_id,
348 program_update_date = sysdate
349 WHERE transaction_id = p_txn_id;
350 RETURN TRUE;
351 END UPDATE_WSMT_TXN_STATUS;
352
353 ----------------------------------------------------------------------------
354 -- PROCEDURE --
355 -- COST_LOT_TXN --
356 -- --
357 -- DESCRIPTION --
358 -- This procedure is called by the Standard Cost Worker to Cost
359 -- Lot Transactions (Split, Merge, Bonus and Update Quantity) --
360 -- --
361 -- PURPOSE: --
362 -- OSFM Lot Transactions Costing for Oracle Applications Rel 11i.8
363 -- --
364 -- PARAMETERS: --
365 -- p_api_version API version
366 -- p_transaction_id Transaction ID from MMT
367 -- p_request_id Request ID of calling worker
368 -- o_err_num Error Number
369 -- o_err_code Error Code --
370 -- o_err_msg Error Message --
371
372 -- HISTORY: --
373 -- August-2002 Vinit Creation --
374 ----------------------------------------------------------------------------
375
376 PROCEDURE COST_LOT_TXN ( p_api_version IN NUMBER,
377 p_transaction_id IN NUMBER,
378 p_request_id IN NUMBER,
379 x_err_num IN OUT NOCOPY NUMBER,
380 x_err_code IN OUT NOCOPY VARCHAR2,
381 x_err_msg IN OUT NOCOPY VARCHAR2) IS
382
383 l_api_name CONSTANT VARCHAR2(80) := 'COST_LOT_TXN';
384 l_api_version CONSTANT NUMBER := 1.0;
385
386 l_stmt_num NUMBER := 0;
387
388 /* Transaction Specific Information */
389 l_wsmt_transaction_id NUMBER; -- Source Line ID in MMT
390 l_mmt_txn_type_id NUMBER; -- From MMT
391 l_wsmt_txn_type_id NUMBER;
392 l_transaction_type_id NUMBER; -- Txn Type in MMT
393 l_txn_source_type_id NUMBER;
394 l_txn_action_id NUMBER;
395 l_wip_entity_id NUMBER;
396 l_transaction_date DATE;
397 l_organization_id NUMBER;
398
399 /* Program Information */
400 l_prog_application_id NUMBER;
401 l_program_id NUMBER;
402 l_login_id NUMBER;
403 l_user_id NUMBER;
404
405 /* Return Codes */
406 l_op_yield_ret_code NUMBER;
407 l_ret_update BOOLEAN;
408
409 /* Bonus Txn specific Information */
410 l_resulting_wip_id NUMBER;
411 l_starting_op_seq NUMBER;
412
413 /* Error Msg */
414 l_err_msg VARCHAR2(2000) := NULL;
415
416 /* Above is due to fact that MMT has a width of
417 240 for its error_explanation column. We
418 need some details here. */
419
420 /* Exceptions */
421 NO_WSMT_TRANSACTION EXCEPTION;
422 UPDATE_JOB_QUANTITY_FAILURE EXCEPTION;
423 COST_TXN_FAILURE EXCEPTION;
424 PROCESS_SM_OP_YIELD_FAILURE EXCEPTION;
425 PROCESS_OP_YIELD_FAILURE EXCEPTION;
426 UNKNOWN_TXN_ERROR EXCEPTION;
427
428 BEGIN
429
430 l_stmt_num := 10;
431
432 /* Initialize Error Codes */
433 x_err_num := 0;
434 x_err_code := NULL;
435 x_err_msg := NULL;
436
437 IF NOT FND_API.COMPATIBLE_API_CALL (
438 l_api_version,
439 p_api_version,
440 l_api_name,
441 G_PKG_NAME ) THEN
442 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
443 END IF;
444
445
446 /* Get Transaction Information from MMT */
447
448 l_stmt_num := 20;
449
450 SELECT transaction_source_id,
451 transaction_date,
452 organization_id,
453 transaction_type_id,
454 transaction_action_id,
455 transaction_source_type_id,
456 nvl(source_line_id, -1)
457 INTO l_wip_entity_id,
458 l_transaction_date,
459 l_organization_id,
460 l_transaction_type_id,
461 l_txn_action_id,
462 l_txn_source_type_id,
463 l_wsmt_transaction_id
464 FROM MTL_MATERIAL_TRANSACTIONS
465 WHERE transaction_id = p_transaction_id;
466
467 IF (l_wsmt_transaction_id = -1) THEN
468 RAISE NO_WSMT_TRANSACTION;
469 END IF;
470
471
472 /* Get Concurrent Program Information */
473
474 l_stmt_num := 30;
475
476 SELECT program_application_id,
477 concurrent_program_id,
478 conc_login_id,
479 requested_by
480 INTO l_prog_application_id,
481 l_program_id,
482 l_login_id,
483 l_user_id
484 FROM FND_CONCURRENT_REQUESTS
485 WHERE request_id = p_request_id;
486
487
488 /* If Transaction is BONUS at First Operation and Queue Introperation
489 Step, Set it as COSTED */
490 /* Note the additional join to BOS and WO in the inner SQL remains
491 since we could have pre 11i.8 jobs that do not have
492 JOB_OPERATION_SEQ_NUM populated */
493
494 l_stmt_num := 40;
495
496 UPDATE wsm_split_merge_transactions txn
497 SET costed = WIP_CONSTANTS.COMPLETED
498 WHERE transaction_type_id = 4
499 AND transaction_id = l_wsmt_transaction_id /* Added for bug 5008413 */
500 AND costed = WIP_CONSTANTS.PENDING
501 AND status = WIP_CONSTANTS.COMPLETED
502 AND EXISTS ( SELECT 'Queue Intraop'
503 FROM wsm_sm_resulting_jobs rj,
504 bom_operation_sequences bos,
505 wip_operations wo
506 WHERE rj.transaction_id = txn.transaction_id
507 AND rj.starting_intraoperation_step = 1
508 AND (nvl(rj.job_operation_seq_num,
509 wo.operation_seq_num), wo.organization_id) =
510 (SELECT min(operation_seq_num), wo2.organization_id
511 FROM wip_operations wo2
512 WHERE wo2.wip_entity_id = rj.wip_entity_id
513 AND wo2.organization_id = rj.organization_id
514 GROUP BY wo2.organization_id)
515 AND rj.transaction_id = txn.transaction_id
516 AND rj.starting_intraoperation_step = 1
517 AND rj.common_routing_sequence_id = bos.routing_sequence_id
518 AND rj.starting_operation_seq_num = bos.operation_seq_num
519 AND bos.operation_sequence_id = wo.operation_sequence_id
520 AND bos.EFFECTIVITY_DATE <= txn.transaction_date
521 AND NVL( bos.DISABLE_DATE, txn.transaction_date + 1
522 ) > txn.transaction_date
523 AND wo.wip_entity_id = rj.wip_entity_id
524 );
525 /* Call specific procedures to cost different lot transactions */
526
527 l_stmt_num := 50;
528
529 IF (l_txn_source_type_id = 5 AND l_txn_action_id = 40) THEN
530 l_wsmt_txn_type_id := 1;
531 CSTPSMUT.COST_SPLIT_TXN
532 ( p_api_version => 1.0,
533 p_transaction_id => l_wsmt_transaction_id,
534 p_mmt_transaction_id => p_transaction_id,
535 p_transaction_date => l_transaction_date,
536 p_prog_application_id => l_prog_application_id,
537 p_program_id => l_program_id,
538 p_request_id => p_request_id,
539 p_login_id => l_login_id,
540 p_user_id => l_user_id,
541 x_err_num => x_err_num,
542 x_err_code => x_err_code,
543 x_err_msg => l_err_msg );
544 ELSIF (l_txn_source_type_id = 5 AND l_txn_action_id = 41) THEN
545 l_wsmt_txn_type_id := 2;
546 CSTPSMUT.COST_MERGE_TXN
547 ( p_api_version => 1.0,
548 p_transaction_id => l_wsmt_transaction_id,
549 p_mmt_transaction_id => p_transaction_id,
550 p_transaction_date => l_transaction_date,
551 p_prog_application_id => l_prog_application_id,
552 p_program_id => l_program_id,
553 p_request_id => p_request_id,
554 p_login_id => l_login_id,
555 p_user_id => l_user_id,
556 x_err_num => x_err_num,
557 x_err_code => x_err_code,
558 x_err_msg => l_err_msg );
559 ELSIF (l_txn_source_type_id = 5 AND l_txn_action_id = 42) THEN
560 l_wsmt_txn_type_id := 4;
561 CSTPSMUT.COST_BONUS_TXN
562 ( p_api_version => 1.0,
563 p_transaction_id => l_wsmt_transaction_id,
564 p_mmt_transaction_id => p_transaction_id,
565 p_transaction_date => l_transaction_date,
566 p_prog_application_id => l_prog_application_id,
567 p_program_id => l_program_id,
568 p_request_id => p_request_id,
569 p_login_id => l_login_id,
570 p_user_id => l_user_id,
571 x_err_num => x_err_num,
572 x_err_code => x_err_code,
573 x_err_msg => l_err_msg );
574 ELSIF (l_txn_source_type_id = 5 AND l_txn_action_id = 43) THEN
575 l_wsmt_txn_type_id := 6;
576 CSTPSMUT.COST_UPDATE_QTY_TXN
577 ( p_api_version => 1.0,
578 p_transaction_id => l_wsmt_transaction_id,
579 p_mmt_transaction_id => p_transaction_id,
580 p_transaction_date => l_transaction_date,
581 p_prog_application_id => l_prog_application_id,
582 p_program_id => l_program_id,
583 p_request_id => p_request_id,
584 p_login_id => l_login_id,
585 p_user_id => l_user_id,
586 x_err_num => x_err_num,
587 x_err_code => x_err_code,
588 x_err_msg => l_err_msg );
589 ELSE
590 RAISE UNKNOWN_TXN_ERROR;
591 END IF;
592
593 /* If Error, Exception and Return */
594 IF ( x_err_num <> 0 ) THEN
595 RAISE COST_TXN_FAILURE;
596 END IF;
597
598
599 /* Transaction Costing is Successful.
600 Update WRO, WOR for the jobs involved in the transaction.
601 */
602 l_stmt_num := 60;
603
604 CSTPSMUT.UPDATE_JOB_QUANTITY
605 ( p_api_version => 1.0,
606 p_txn_id => l_wsmt_transaction_id,
607 x_err_num => x_err_num,
608 x_err_code => x_err_code,
609 x_err_msg => l_err_msg );
610 IF ( x_err_num <> 0 ) THEN
611 RAISE UPDATE_JOB_QUANTITY_FAILURE;
612 END IF;
613
614 /* Update Successful, Do Operation Yield Costing.
615 Call CSTPOYLD.process_sm_op_yld to populate WOY for the jobs.
616 The Operation Yield Processor then picks it up separately for
617 calculating the yielded costs.
618 For Bonus and Update Qty Txns call CSTPOYLD.process_op_yield
619 online to calculate the yielded costs since the accounting for
620 these transactions is different.
621 (They use the Bonus account specified on the transaction)
622 */
623
624
625 l_stmt_num := 70;
626
627 l_op_yield_ret_code := CSTPOYLD.process_sm_op_yld
628 ( l_wsmt_transaction_id,
629 l_user_id,
630 l_login_id,
631 l_prog_application_id,
632 l_program_id,
633 p_request_id,
634 x_err_num,
635 x_err_code,
636 l_err_msg );
637 IF ( l_op_yield_ret_code = 0 ) THEN
638 RAISE PROCESS_SM_OP_YIELD_FAILURE;
639 END IF;
640
641
642 IF (( l_wsmt_txn_type_id = 4) OR ( l_wsmt_txn_type_id = 6 )) THEN
643 l_stmt_num := 80;
644 IF ( l_wsmt_txn_type_id = 6) THEN
645 SELECT wip_entity_id,
646 operation_seq_num
647 INTO l_resulting_wip_id,
648 l_starting_op_seq
649 FROM wsm_sm_starting_jobs
650 WHERE transaction_id = l_wsmt_transaction_id;
651
652 ELSE
653
654 SELECT wip_entity_id,
655 job_operation_seq_num
656 INTO l_resulting_wip_id,
657 l_starting_op_seq
658 FROM WSM_SM_RESULTING_JOBS WSRJ
659 WHERE transaction_id = l_wsmt_transaction_id;
660
661 /* For Pre 11i.8 jobs, JOB_OPERATION_SEQ_NUM is NULL,
662 Use Bom_operation_sequences and wsrj.starting_op_seq_num to
663 obtain this information */
664 IF l_starting_op_seq IS NULL THEN
665 SELECT wo.operation_seq_num
666 INTO l_starting_op_seq
667 FROM WIP_OPERATIONS WO,
668 WSM_SM_RESULTING_JOBS WSRJ,
669 BOM_OPERATION_SEQUENCES BOS
670 WHERE WSRJ.transaction_id = l_wsmt_transaction_id
671 AND nvl(wsrj.starting_intraoperation_step, 1) = 1
672 AND wsrj.common_routing_sequence_id = bos.routing_sequence_id
673 AND wsrj.starting_operation_seq_num = bos.operation_seq_num
674 AND bos.operation_sequence_id = wo.operation_sequence_id
675 AND bos.EFFECTIVITY_DATE <= l_transaction_date
676 AND NVL( bos.DISABLE_DATE, l_transaction_date + 1) > l_transaction_date
677 AND wo.wip_entity_id = wsrj.wip_entity_id
678 AND wo.organization_id = l_organization_id;
679 END IF;
680
681 END IF;
682
683 l_stmt_num := 90;
684
685 l_err_msg := NULL;
686 CSTPOYLD.process_op_yield
687 ( l_err_msg,
688 x_err_code,
689 2, -- Range: WIP
690 l_resulting_wip_id,
691 3, -- Run_Option (Look in CSTOYLDB.pls)
692 l_starting_op_seq,
693 null,
694 l_wsmt_transaction_id );
695
696 IF (l_err_msg IS NOT NULL) THEN
697 RAISE PROCESS_OP_YIELD_FAILURE;
698 END IF;
699
700 END IF;
701
702 /* l_err_msg is populated if the Op Yield Process fails */
703 l_stmt_num := 100;
704 l_ret_update := UPDATE_WSMT_TXN_STATUS ( l_wsmt_transaction_id,
705 WIP_CONSTANTS.COMPLETED,
706 NULL, -- Error Message
707 p_request_id,
708 l_prog_application_id,
709 l_program_id );
710
711 EXCEPTION
712 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
713 x_err_num := -1;
714 x_err_code := 'Inconsistent API Version';--FND_API.G_RET_SYS_ERROR;
715 l_err_msg := 'CSTPSMCW.COST_LOT_TXN('||to_char(l_stmt_num)||'):' || l_err_msg || substr(SQLERRM, 1, 200);
716 FND_FILE.put_line(fnd_file.log, 'API Version Obsoleted'|| l_err_msg );
717 x_err_msg := 'CSTPSMCW.COST_LOT_TXN('||to_char(l_stmt_num);
718 l_ret_update := UPDATE_WSMT_TXN_STATUS ( l_wsmt_transaction_id,
719 WIP_CONSTANTS.ERROR,
720 l_err_msg,
721 p_request_id,
722 l_prog_application_id,
723 l_program_id );
724 WHEN UNKNOWN_TXN_ERROR THEN
725 x_err_num := -1;
726 x_err_code := 'Unknown Transaction Type passed... Catastrophic Failure';
727 l_err_msg := 'CSTPSMCW.COST_LOT_TXN('||to_char(l_stmt_num)||'):' || l_err_msg;
728 FND_FILE.put_line(fnd_file.log, 'Unknown Transaction Type passed... Catastrophic Failure'|| l_err_msg );
729 x_err_msg := 'CSTPSMCW.COST_LOT_TXN('||to_char(l_stmt_num);
730
731 l_ret_update := UPDATE_WSMT_TXN_STATUS ( l_wsmt_transaction_id,
732 WIP_CONSTANTS.ERROR,
733 l_err_msg,
734 p_request_id,
735 l_prog_application_id,
736 l_program_id );
737
738 WHEN NO_WSMT_TRANSACTION THEN
739 x_err_num := -1;
740 x_err_code := 'MMT not populated with Transaction ID from WSMT';
741 l_err_msg := 'CSTPSMCW.COST_LOT_TXN('||to_char(l_stmt_num)||'): ' || l_err_msg;
742 x_err_msg := 'CSTPSMCW.COST_LOT_TXN('||to_char(l_stmt_num)||'):';
743 FND_FILE.put_line(fnd_file.log, 'MMT not populated with Transaction ID from WSMT'|| l_err_msg );
744 l_ret_update := UPDATE_WSMT_TXN_STATUS ( l_wsmt_transaction_id,
745 WIP_CONSTANTS.ERROR,
746 l_err_msg,
747 p_request_id,
748 l_prog_application_id,
749 l_program_id );
750 WHEN COST_TXN_FAILURE THEN
751 x_err_num := -1;
752 x_err_code := 'Transaction Costing Failed';
753 l_err_msg := 'CSTPSMCW.COST_LOT_TXN('||to_char(l_stmt_num)||'): ' || l_err_msg;
754 FND_FILE.put_line(fnd_file.log, 'Transaction Costing Failed'|| l_err_msg );
755 l_ret_update := UPDATE_WSMT_TXN_STATUS ( l_wsmt_transaction_id,
756 WIP_CONSTANTS.ERROR,
757 l_err_msg,
758 p_request_id,
759 l_prog_application_id,
760 l_program_id );
761 WHEN UPDATE_JOB_QUANTITY_FAILURE THEN
762 x_err_num := -1;
763 x_err_code := 'Failed to Update Job Info: Transaction Costing Failed';
764 l_err_msg := 'CSTPSMCW.COST_LOT_TXN('||to_char(l_stmt_num)||'): ' || l_err_msg;
765 x_err_msg := 'CSTPSMCW.COST_LOT_TXN('||to_char(l_stmt_num)||'):';
766 FND_FILE.put_line(fnd_file.log, 'Failed to Update Job Info: Transaction Costing Failed'|| l_err_msg );
767 l_ret_update := UPDATE_WSMT_TXN_STATUS ( l_wsmt_transaction_id,
768 WIP_CONSTANTS.ERROR,
769 l_err_msg,
770 p_request_id,
771 l_prog_application_id,
772 l_program_id );
773 WHEN PROCESS_SM_OP_YIELD_FAILURE THEN
774 x_err_num := -1;
775 x_err_code := 'Update of Wip Operation Yields Failed';
776 l_err_msg := 'CSTPSMCW.COST_LOT_TXN('||to_char(l_stmt_num)||'): ' || l_err_msg;
777 x_err_msg := 'CSTPSMCW.COST_LOT_TXN('||to_char(l_stmt_num)||'):';
778 FND_FILE.put_line(fnd_file.log, 'Update of Wip Operation Yields Failed'|| l_err_msg );
779 l_ret_update := UPDATE_WSMT_TXN_STATUS ( l_wsmt_transaction_id,
780 WIP_CONSTANTS.ERROR,
781 l_err_msg,
782 p_request_id,
783 l_prog_application_id,
784 l_program_id );
785 WHEN PROCESS_OP_YIELD_FAILURE THEN
786 x_err_num := -1;
787 x_err_code := 'Operation Yield Costing Failed';
788 l_err_msg := 'CSTPSMCW.COST_LOT_TXN('||to_char(l_stmt_num)||'): ' || l_err_msg;
789 x_err_msg := 'CSTPSMCW.COST_LOT_TXN('||to_char(l_stmt_num)||'):';
790 FND_FILE.put_line(fnd_file.log, 'Operation Yield Costing Failed'|| l_err_msg );
791 l_ret_update := UPDATE_WSMT_TXN_STATUS ( l_wsmt_transaction_id,
792 WIP_CONSTANTS.ERROR,
793 l_err_msg,
794 p_request_id,
795 l_prog_application_id,
796 l_program_id );
797 END COST_LOT_TXN;
798
799
800 END CSTPSMCW;