[Home] [Help]
PACKAGE BODY: APPS.CSTPSMUT
Source
1 PACKAGE BODY CSTPSMUT AS
2 /* $Header: CSTSMUTB.pls 120.9.12010000.2 2008/12/16 21:39:30 hyu ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(240) := 'CSTPSMUT';
5 l_debug_flag CONSTANT VARCHAR2(1) := FND_PROFILE.VALUE('MRP_DEBUG');
6
7 ----------------------------------------------------------------------------
8 -- FUNCTION --
9 -- INSERT_WOO
10 -- --
11 -- DESCRIPTION --
12 -- This function inserts records in WIP_OPERATION_OVERHEADS for
13 -- newly created jobs during Split/Merge/Bonus
14 --
15 -- --
16 -- PURPOSE: --
17 -- OSFM Lot Transactions Costing for Oracle Applications Rel 11i.8
18 -- --
19 -- PARAMETERS: --
20 -- p_wip_entity_id Job for which records are to be inserted
21 -- p_organization_id Organization
22 -- p_login_id
23 -- p_user_id Concurrent WHO Parameters
24 -- p_request_id Request ID of calling worker
25 -- p_prog_appl_id
26 -- p_program_id
27 -- HISTORY: --
28 -- September-2002 Vinit Creation --
29 ----------------------------------------------------------------------------
30
31 FUNCTION INSERT_WOO (p_wip_entity_id IN NUMBER,
32 p_organization_id IN NUMBER,
33 p_operation_seq_num IN NUMBER,
34 p_user_id IN NUMBER,
35 p_login_id IN NUMBER,
36 p_request_id IN NUMBER,
37 p_prog_appl_id IN NUMBER,
38 p_program_id IN NUMBER )
39 RETURN BOOLEAN IS
40 l_num_rows NUMBER;
41 l_return BOOLEAN := TRUE;
42 BEGIN
43 IF (l_debug_flag = 'Y') THEN
44 FND_FILE.PUT_LINE (FND_FILE.LOG, 'INSERT_WOO <<<');
45 END IF;
46
47 INSERT INTO WIP_OPERATION_OVERHEADS
48 (WIP_ENTITY_ID,
49 OPERATION_SEQ_NUM,
50 RESOURCE_SEQ_NUM,
51 ORGANIZATION_ID,
52 OVERHEAD_ID,
53 BASIS_TYPE,
54 APPLIED_OVHD_UNITS,
55 APPLIED_OVHD_VALUE,
56 RELIEVED_OVHD_COMPLETION_UNITS,
57 RELIEVED_OVHD_SCRAP_UNITS,
58 RELIEVED_OVHD_COMPLETION_VALUE,
59 RELIEVED_OVHD_SCRAP_VALUE,
60 TEMP_RELIEVED_VALUE,
61 LAST_UPDATED_BY,
62 CREATION_DATE,
63 CREATED_BY,
64 LAST_UPDATE_LOGIN,
65 REQUEST_ID,
66 PROGRAM_APPLICATION_ID,
67 PROGRAM_ID,
68 PROGRAM_UPDATE_DATE,
69 LAST_UPDATE_DATE)
70 (SELECT /* Resource Unit And Value Based Overheads */
71 WO.wip_entity_id,
72 WO.operation_seq_num,
73 WOR.resource_seq_num,
74 WO.organization_id,
75 CDO.overhead_id,
76 CDO.basis_type,
77 0,
78 0,
79 0,
80 0,
81 0,
82 0,
83 0,
84 p_user_id,
85 sysdate,
86 p_user_id,
87 p_login_id,
88 p_request_id,
89 p_prog_appl_id,
90 p_program_id,
91 sysdate,
92 sysdate
93 FROM
94 cst_department_overheads CDO,
95 cst_resource_overheads CRO,
96 wip_operation_resources WOR,
97 wip_operations WO
98 WHERE
99 WO.wip_entity_id = p_wip_entity_id
100 AND WOR.wip_entity_id = WO.wip_entity_id
101 AND WOR.organization_id = WO.organization_id
102 AND CDO.organization_id = WO.organization_id
103 AND WO.organization_id = p_organization_id
104 AND CDO.department_id = WO.department_id
105 AND CDO.overhead_id = CRO.overhead_id
106 AND CRO.resource_id = WOR.resource_id
107 AND CRO.cost_type_id = 1
108 AND CDO.cost_type_id = 1
109 AND CDO.basis_type in (3,4)
110 AND WO.operation_seq_num = WOR.operation_seq_num
111 AND WO.operation_seq_num <= p_operation_seq_num
112 /* Don't insert if a row already exists 5364135 */
113 AND NOT EXISTS (SELECT 'Not exists'
114 FROM wip_operation_overheads woo
115 WHERE woo.wip_entity_id = WO.wip_entity_id
116 AND woo.operation_seq_num = WO.operation_seq_num
117 AND woo.resource_seq_num = WOR.resource_seq_num
118 AND woo.organization_id = WO.organization_id
119 AND woo.overhead_id = CDO.overhead_id
120 AND woo.basis_type = CDO.basis_type)
121 UNION ALL
122 SELECT /* Department Based Overheads */
123 WO.wip_entity_id,
124 WO.operation_seq_num,
125 -1,
126 WO.organization_id,
127 CDO.overhead_id,
128 CDO.basis_type,
129 0,
130 0,
131 0,
132 0,
133 0,
134 0,
135 0,
136 p_user_id,
137 sysdate,
138 p_user_id,
139 p_login_id,
140 p_request_id,
141 p_prog_appl_id,
142 p_program_id,
143 sysdate,
144 sysdate
145 FROM
146 cst_department_overheads CDO,
147 wip_operations WO
148 WHERE
149 WO.wip_entity_id = p_wip_entity_id
150 AND CDO.department_id = WO.department_id
151 AND CDO.organization_id = WO.organization_id
152 AND CDO.cost_type_id = 1
153 AND CDO.basis_type in (1,2)
154 AND WO.organization_id = p_organization_id
155 AND WO.operation_seq_num <= p_operation_seq_num
156 /* Don't insert if a row already exists 5364135 */
157 AND NOT EXISTS (SELECT 'Not exists'
158 FROM wip_operation_overheads woo
159 WHERE woo.wip_entity_id = WO.wip_entity_id
160 AND woo.operation_seq_num = WO.operation_seq_num
161 AND woo.resource_seq_num = -1
162 AND woo.organization_id = WO.organization_id
163 AND woo.overhead_id = CDO.overhead_id
164 AND woo.basis_type = CDO.basis_type));
165
166 l_num_rows := SQL%ROWCOUNT;
167
168 IF(l_debug_flag = 'Y') THEN
169 FND_FILE.put_line(fnd_file.log, to_char(l_num_rows)||' rows inserted into WOO for Job: '||to_char(p_wip_entity_id));
170 FND_FILE.PUT_LINE (FND_FILE.LOG, 'INSERT_WOO >>>');
171 END IF;
172 RETURN l_return;
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 IF(l_debug_flag = 'Y') THEN
177 FND_FILE.put_line(fnd_file.log, 'Failed to Insert into WOO: '||SQLERRM);
178 END IF;
179 RETURN FALSE;
180
181 END INSERT_WOO;
182
183 ----------------------------------------------------------------------------
184 -- PROCEDURE --
185 -- COST_SPLIT_TXN --
186 -- --
187 -- DESCRIPTION --
188 -- This procedure costs a lot split transaction. It inserts entries in
189 -- MTA, WT and WTA. It also updates the WPB for all the involved jobs.
190 -- --
191 -- PURPOSE: --
192 -- OSFM Lot Transactions Costing for Oracle Applications Rel 11i.8
193 -- --
194 -- PARAMETERS: --
195 -- p_app_version API version
196 -- p_transaction_id Transaction ID from WSMT
197 -- p_mmt_transaction_id Transaction ID form MMT
198 -- p_request_id Request ID of calling worker
199 -- p_transaction_date Transaction Date
200 -- p_prog_application_id
201 -- p_program_id
202 -- p_login_id
203 -- p_user_id Concurrent WHO Parameters
204 -- o_err_num Error Number
205 -- o_err_code Error Code --
206 -- o_err_msg Error Message --
207 -- HISTORY: --
208 -- August-2002 Vinit Creation --
209 ----------------------------------------------------------------------------
210 PROCEDURE COST_SPLIT_TXN (p_api_version IN NUMBER,
211 p_transaction_id IN NUMBER,
212 p_mmt_transaction_id IN NUMBER,
213 p_transaction_date IN DATE,
214 p_prog_application_id IN NUMBER,
215 p_program_id IN NUMBER,
216 p_request_id IN NUMBER,
217 p_login_id IN NUMBER,
218 p_user_id IN NUMBER,
219 x_err_num IN OUT NOCOPY NUMBER,
220 x_err_code IN OUT NOCOPY VARCHAR2,
221 x_err_msg IN OUT NOCOPY VARCHAR2) IS
222
223 /* Parameters */
224 l_transaction_type NUMBER;
225 l_organization_id NUMBER;
226 l_transaction_date DATE;
227 l_min_acct_unit NUMBER := 0;
228 l_ext_prec NUMBER(2) := 0;
229 l_wip_transaction_id NUMBER;
230 l_acct_period_id NUMBER;
231
232 /* Local Variables */
233 l_stmt_num NUMBER := 0;
234 l_ins_woo BOOLEAN := TRUE;
235 l_le_transaction_date DATE;
236
237 /* API */
238 l_api_name CONSTANT VARCHAR2(240) := 'COST_SPLIT_TXN';
239 l_api_version CONSTANT NUMBER := 1.0;
240
241 /* Representative Lot Information */
242 l_rep_wip_entity_id NUMBER;
243 l_available_quantity NUMBER;
244 l_job_start_quantity NUMBER;
245 l_operation_seq_num NUMBER;
246 l_intraoperation_step NUMBER;
247
248
249 /* Resulting Jobs */
250 l_total_resulting_qty NUMBER;
251
252 /* Job Charges */
253 l_pl_mtl_cost_in NUMBER := 0;
254 l_pl_mto_cost_in NUMBER := 0;
255 l_pl_res_cost_in NUMBER := 0;
256 l_pl_ovh_cost_in NUMBER := 0;
257 l_pl_osp_cost_in NUMBER := 0;
258 l_tl_res_cost_in NUMBER := 0;
259 l_tl_ovh_cost_in NUMBER := 0;
260 l_tl_osp_cost_in NUMBER := 0;
261
262 /* Relieved Costs */
263 l_pl_mtl_cost_out NUMBER := 0;
264 l_pl_mto_cost_out NUMBER := 0;
265 l_pl_res_cost_out NUMBER := 0;
266 l_pl_ovh_cost_out NUMBER := 0;
267 l_pl_osp_cost_out NUMBER := 0;
268 l_tl_res_cost_out NUMBER := 0;
269 l_tl_ovh_cost_out NUMBER := 0;
270 l_tl_osp_cost_out NUMBER := 0;
271
272
273 /* Net Cost and Total Costs */
274 l_pl_mtl_net NUMBER := 0;
275 l_pl_mto_net NUMBER := 0;
276 l_pl_res_net NUMBER := 0;
277 l_pl_ovh_net NUMBER := 0;
278 l_pl_osp_net NUMBER := 0;
279 l_tl_res_net NUMBER := 0;
280 l_tl_ovh_net NUMBER := 0;
281 l_tl_osp_net NUMBER := 0;
282
283 l_total_tl_res NUMBER := 0;
284 l_total_tl_ovh NUMBER := 0;
285 l_total_tl_osp NUMBER := 0;
286 l_total_pl_mtl NUMBER := 0;
287 l_total_pl_mto NUMBER := 0;
288 l_total_pl_res NUMBER := 0;
289 l_total_pl_ovh NUMBER := 0;
290 l_total_pl_osp NUMBER := 0;
291
292 l_total_qty NUMBER := 0;
293
294 /* Exceptions */
295
296 GET_JOB_VALUE_FAILURE EXCEPTION;
297 FAILED_INSERTING_START_LOT EXCEPTION;
298 FAILED_BALANCING_ACCT EXCEPTION;
299 FAILED_INSERTING_WT EXCEPTION;
300 FAILED_INSERTING_WTA EXCEPTION;
301 FAILED_INSERTING_MTA EXCEPTION;
302 FAILED_INSERTING_RESULT_LOT EXCEPTION;
303 INSERT_WOO_ERROR EXCEPTION;
304
305 /* Accounting Line Types */
306 SPLIT_RESULT_ACT_LTYPE NUMBER := 22;
307 SPLIT_START_ACT_LTYPE NUMBER := 21;
308
309 /* SLA Event Seeding */
310 l_return_status VARCHAR2(1);
311 l_wta_exists NUMBER;
312 l_msg_count NUMBER;
313 l_msg_data VARCHAR2(2000);
314 l_trx_info CST_XLA_PVT.t_xla_wip_trx_info;
315
316
317
318 CURSOR c_start_lot IS
319 SELECT available_quantity, wip_entity_id
320 FROM wsm_sm_starting_jobs
321 WHERE transaction_id = p_transaction_id;
322
323 CURSOR c_result_lot IS
324 SELECT start_quantity, wip_entity_id,
325 nvl(starting_operation_seq_num,10) starting_operation_seq_num,
326 nvl(starting_intraoperation_step, WIP_CONSTANTS.QUEUE) starting_intraoperation_step,
327 common_routing_sequence_id
328 FROM wsm_sm_resulting_jobs
329 WHERE transaction_id = p_transaction_id;
330
331 CURSOR c_new_jobs IS
332 SELECT wip_entity_id
333 FROM wsm_sm_resulting_jobs
334 WHERE transaction_id = p_transaction_id
335 AND wip_entity_id not in
336 ( SELECT wip_entity_id
337 FROM wsm_sm_starting_jobs
338 WHERE transaction_id = p_transaction_id );
339
340
341 BEGIN
342 /* Check API Compatibility */
343 l_stmt_num := 10;
344
345 IF(l_debug_flag = 'Y') THEN
346 FND_FILE.put_line(fnd_file.log, 'CSTPSMUT.COST_SPLIT_TXN <<<');
347 fnd_file.put_line(fnd_file.log, 'Costing Transaction: '||to_char(p_transaction_id));
348 END IF;
349
350 IF NOT FND_API.COMPATIBLE_API_CALL (
351 l_api_version,
352 p_api_version,
353 l_api_name,
354 G_PKG_NAME ) THEN
355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
356 END IF;
357
358
359
360 /* Get Transaction Information from WSMT */
361
362 l_stmt_num := 20;
363 SELECT organization_id,
364 transaction_type_id,
365 transaction_date
366 INTO l_organization_id,
367 l_transaction_type,
368 l_transaction_date
369 FROM WSM_SPLIT_MERGE_TRANSACTIONS
370 WHERE transaction_id = p_transaction_id;
371
372 /* Get Currency Information and Precision */
373
374 l_stmt_num := 30;
375
376 SELECT NVL(FC.minimum_accountable_unit, POWER(10,NVL(-precision,0))),
377 NVL(FC.extended_precision,NVL(FC.precision,0))
378 INTO l_min_acct_unit,
379 l_ext_prec
380 FROM fnd_currencies FC,
381 CST_ORGANIZATION_DEFINITIONS O
382 WHERE O.organization_id = l_organization_id
383 AND O.currency_code = FC.currency_code;
384
385 /* Accounting Period */
386 l_stmt_num := 35;
387
388 l_le_transaction_date := INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(
389 l_transaction_date,
390 l_organization_id);
391
392 l_stmt_num := 40;
393
394 SELECT acct_period_id
395 INTO l_acct_period_id
396 FROM org_acct_periods
397 WHERE organization_id = l_organization_id
398 AND l_le_transaction_date
399 between period_start_date and schedule_close_date;
400
401
402 /* Get Information from WSSJ */
403
404 l_stmt_num := 50;
405 SELECT wip_entity_id,
406 operation_seq_num,
407 intraoperation_step,
408 job_start_quantity,
409 available_quantity
410 INTO l_rep_wip_entity_id,
411 l_operation_seq_num,
412 l_intraoperation_step,
413 l_job_start_quantity,
414 l_available_quantity
415 FROM WSM_SM_STARTING_JOBS
416 WHERE transaction_id = p_transaction_id
417 AND representative_flag = 'Y';
418
419
420 /* Obtain Total Resulting Quantity */
421 l_stmt_num := 60;
422 SELECT SUM(start_quantity)
423 INTO l_total_resulting_qty
424 FROM WSM_SM_RESULTING_JOBS
425 WHERE transaction_id = p_transaction_id;
426
427
428 /* Get the Job Charges */
429
430 l_stmt_num := 70;
431
432 CSTPSMUT.GET_JOB_VALUE ( p_api_version => 1.0,
433 p_lot_size => l_job_start_quantity,
434 p_run_mode => 1, -- CHARGE
435 p_entity_id => l_rep_wip_entity_id,
436 p_intraop_step => l_intraoperation_step,
437 p_operation_seq_num => l_operation_seq_num,
438 p_transaction_id => p_transaction_id,
439 p_txn_type => WSMPCNST.SPLIT,
440 p_org_id => l_organization_id,
441 x_err_num => x_err_num,
442 x_err_code => x_err_code,
443 x_err_msg => x_err_msg,
444 x_pl_mtl_cost => l_pl_mtl_cost_in,
445 x_pl_mto_cost => l_pl_mto_cost_in,
446 x_pl_res_cost => l_pl_res_cost_in,
447 x_pl_ovh_cost => l_pl_ovh_cost_in,
448 x_pl_osp_cost => l_pl_osp_cost_in,
449 x_tl_res_cost => l_tl_res_cost_in,
450 x_tl_ovh_cost => l_tl_ovh_cost_in,
451 x_tl_osp_cost => l_tl_osp_cost_in );
452
453
454
455 /* Get the Costs Relieved from the job */
456 l_stmt_num := 80;
457
458 CSTPSMUT.GET_JOB_VALUE ( p_api_version => 1.0,
459 p_lot_size => l_job_start_quantity,
460 p_run_mode => 2, -- SCRAP
461 p_entity_id => l_rep_wip_entity_id,
462 p_intraop_step => l_intraoperation_step,
463 p_operation_seq_num => l_operation_seq_num,
464 p_transaction_id => p_transaction_id,
465 p_txn_type => WSMPCNST.SPLIT,
466 p_org_id => l_organization_id,
467 x_err_num => x_err_num,
468 x_err_code => x_err_code,
469 x_err_msg => x_err_msg,
470 x_pl_mtl_cost => l_pl_mtl_cost_out,
471 x_pl_mto_cost => l_pl_mto_cost_out,
472 x_pl_res_cost => l_pl_res_cost_out,
473 x_pl_ovh_cost => l_pl_ovh_cost_out,
474 x_pl_osp_cost => l_pl_osp_cost_out,
475 x_tl_res_cost => l_tl_res_cost_out,
476 x_tl_ovh_cost => l_tl_ovh_cost_out,
477 x_tl_osp_cost => l_tl_osp_cost_out );
478
479
480 l_stmt_num := 90;
481
482 l_pl_mtl_net := l_pl_mtl_cost_in - l_pl_mtl_cost_out;
483 l_pl_mto_net := l_pl_mto_cost_in - l_pl_mto_cost_out;
484 l_pl_res_net := l_pl_res_cost_in - l_pl_res_cost_out;
485 l_pl_ovh_net := l_pl_ovh_cost_in - l_pl_ovh_cost_out;
486 l_pl_osp_net := l_pl_osp_cost_in - l_pl_osp_cost_out;
487
488 l_tl_res_net := l_tl_res_cost_in - l_tl_res_cost_out;
489 l_tl_ovh_net := l_tl_ovh_cost_in - l_tl_ovh_cost_out;
490 l_tl_osp_net := l_tl_osp_cost_in - l_tl_osp_cost_out;
491
492 l_stmt_num := 100;
493
494 get_wip_txn_id(l_wip_transaction_id,
495 x_err_num,
496 x_err_code,
497 x_err_msg);
498
499
500 l_stmt_num := 110;
501
502 FOR c_result in c_result_lot LOOP
503 IF (l_rep_wip_entity_id <> c_result.wip_entity_id) THEN
504
505 l_stmt_num := 90;
506
507 CSTPSMUT.INSERT_MAT_TXN_ACCT(
508 l_transaction_date,
509 l_min_acct_unit,
510 l_ext_prec,
511 l_transaction_type,
512 p_mmt_transaction_id,
513 l_organization_id,
514 c_result.wip_entity_id,
515 SPLIT_RESULT_ACT_LTYPE,
516 c_result.start_quantity,
517 (c_result.start_quantity / l_total_resulting_qty * l_pl_mtl_net),
518 (c_result.start_quantity / l_total_resulting_qty * l_pl_mto_net),
519 (c_result.start_quantity / l_total_resulting_qty * l_pl_res_net),
520 (c_result.start_quantity / l_total_resulting_qty * l_pl_ovh_net),
521 (c_result.start_quantity / l_total_resulting_qty * l_pl_osp_net),
522 p_user_id,
523 p_login_id,
524 p_request_id,
525 p_prog_application_id,
526 p_program_id,
527 l_debug_flag,
528 x_err_num,
529 x_err_code,
530 x_err_msg);
531
532 IF x_err_num <> 0 then
533 RAISE FAILED_INSERTING_MTA;
534 END IF;
535
536 /* Insert into WTA */
537
538 l_stmt_num := 110;
539
540 CSTPSMUT.INSERT_WIP_TXN_ACCT(
541 l_transaction_date,
542 l_min_acct_unit,
543 l_ext_prec,
544 p_transaction_id,
545 l_transaction_type,
546 l_wip_transaction_id,
547 l_organization_id,
548 c_result.wip_entity_id,
549 SPLIT_RESULT_ACT_LTYPE,
550 c_result.start_quantity,
551 0, -- This Level Material Cost
552 0, -- This Level Material Ovhd Cost
553 (c_result.start_quantity / l_total_resulting_qty * l_tl_res_net),
554 (c_result.start_quantity / l_total_resulting_qty * l_tl_ovh_net),
555 (c_result.start_quantity / l_total_resulting_qty * l_tl_osp_net),
556 p_user_id,
557 p_login_id,
558 p_request_id,
559 p_prog_application_id,
560 p_program_id,
561 l_debug_flag,
562 x_err_num,
563 x_err_code,
564 x_err_msg);
565
566 IF x_err_num <> 0 then
567 RAISE FAILED_INSERTING_WTA;
568 END IF;
569
570 /* Update WPB of resulting Lot */
571
572 l_stmt_num := 120;
573
574 CSTPSMUT.RESULT_LOT(
575 p_mmt_transaction_id,
576 l_wip_transaction_id,
577 c_result.wip_entity_id,
578 l_acct_period_id,
579 p_user_id,
580 p_login_id,
581 p_request_id,
582 p_prog_application_id,
583 p_program_id,
584 l_debug_flag,
585 x_err_num,
586 x_err_code,
587 x_err_msg);
588 IF x_err_num <> 0 then
589 RAISE FAILED_INSERTING_RESULT_LOT;
590 END IF;
591
592 l_stmt_num := 130;
593
594 /* Update the Amount to be relieved from the parent */
595
596 l_total_tl_res := l_total_tl_res + (ROUND(c_result.start_quantity / l_total_resulting_qty * l_tl_res_net / l_min_acct_unit) * l_min_acct_unit);
597 l_total_tl_ovh := l_total_tl_ovh + (ROUND(c_result.start_quantity / l_total_resulting_qty * l_tl_ovh_net / l_min_acct_unit) * l_min_acct_unit);
598 l_total_tl_osp := l_total_tl_osp + (ROUND(c_result.start_quantity / l_total_resulting_qty * l_tl_osp_net / l_min_acct_unit) * l_min_acct_unit);
599 l_total_pl_mtl := l_total_pl_mtl + (ROUND(c_result.start_quantity / l_total_resulting_qty * l_pl_mtl_net / l_min_acct_unit) * l_min_acct_unit);
600 l_total_pl_mto := l_total_pl_mto + (ROUND(c_result.start_quantity / l_total_resulting_qty * l_pl_mto_net / l_min_acct_unit) * l_min_acct_unit);
601 l_total_pl_res := l_total_pl_res + (ROUND(c_result.start_quantity / l_total_resulting_qty * l_pl_res_net / l_min_acct_unit) * l_min_acct_unit);
602 l_total_pl_ovh := l_total_pl_ovh + (ROUND(c_result.start_quantity / l_total_resulting_qty * l_pl_ovh_net / l_min_acct_unit) * l_min_acct_unit);
603 l_total_pl_osp := l_total_pl_osp + (ROUND(c_result.start_quantity / l_total_resulting_qty * l_pl_osp_net / l_min_acct_unit) * l_min_acct_unit);
604 l_total_qty := l_total_qty + c_result.start_quantity;
605
606 END IF; -- Non Representative Lot
607
608 END LOOP; -- End Resulting Lots
609
610 IF(l_debug_flag = 'Y') THEN
611 fnd_file.put_line(fnd_file.log, 'Net Cost to be Relieved from Rep. Lot: ');
612 fnd_file.put_line(fnd_file.log, 'PL_MTL: '||to_char(l_total_pl_mtl));
613 fnd_file.put_line(fnd_file.log, 'PL_MOH: '||to_char(l_total_pl_mto));
614 fnd_file.put_line(fnd_file.log, 'PL_RES: '||to_char(l_total_pl_res));
615 fnd_file.put_line(fnd_file.log, 'PL_OVH: '||to_char(l_total_pl_ovh));
616 fnd_file.put_line(fnd_file.log, 'TL_RES: '||to_char(l_total_tl_res));
617 fnd_file.put_line(fnd_file.log, 'TL_OVH: '||to_char(l_total_tl_ovh));
618 END IF;
619
620 /* Only One Lot in Starting Jobs but cursor can be used since it is already there */
621 FOR C_start in c_start_lot LOOP
622 l_stmt_num := 140;
623
624 /* Insert into MTA for Representative Lot */
625
626 CSTPSMUT.INSERT_MAT_TXN_ACCT(
627 l_transaction_date,
628 l_min_acct_unit,
629 l_ext_prec,
630 l_transaction_type,
631 p_mmt_transaction_id,
632 l_organization_id,
633 c_start.wip_entity_id,
634 SPLIT_START_ACT_LTYPE,
635 -l_total_qty,
636 -l_total_pl_mtl,
637 -l_total_pl_mto,
638 -l_total_pl_res,
639 -l_total_pl_ovh,
640 -l_total_pl_osp,
641 p_user_id,
642 p_login_id,
643 p_request_id,
644 p_prog_application_id,
645 p_program_id,
646 l_debug_flag,
647 x_err_num,
648 x_err_code,
649 x_err_msg);
650
651 IF x_err_num <> 0 then
652 RAISE FAILED_INSERTING_MTA;
653 END IF;
654
655 l_stmt_num := 150;
656
657 /* Insert TL Accounting into WTA */
658
659 CSTPSMUT.INSERT_WIP_TXN_ACCT(
660 l_transaction_date,
661 l_min_acct_unit,
662 l_ext_prec,
663 p_transaction_id,
664 l_transaction_type,
665 l_wip_transaction_id,
666 l_organization_id,
667 c_start.wip_entity_id,
668 SPLIT_START_ACT_LTYPE,
669 -l_total_qty,
670 0, -- This Level Material Cost
671 0, -- This Level Material Ovhd Cost
672 -l_total_tl_res,
673 -l_total_tl_ovh,
674 -l_total_tl_osp,
675 p_user_id,
676 p_login_id,
677 p_request_id,
678 p_prog_application_id,
679 p_program_id,
680 l_debug_flag,
681 x_err_num,
682 x_err_code,
683 x_err_msg);
684
685 IF x_err_num <> 0 then
686 RAISE FAILED_INSERTING_WTA;
687 END IF;
688
689 /* Insert Transaction into WT */
690
691 l_stmt_num := 160;
692 CSTPSMUT.INSERT_WIP_TXN(
693 l_transaction_date,
694 p_transaction_id,
695 l_wip_transaction_id,
696 l_acct_period_id,
697 c_start.wip_entity_id,
698 l_operation_seq_num,
699 11, -- WIP Transaction type
700 p_user_id,
701 p_login_id,
702 p_request_id,
703 p_prog_application_id,
704 p_program_id,
705 l_debug_flag,
706 x_err_num,
707 x_err_code,
708 x_err_msg,
709 p_mmt_transaction_id); -- Added for Bug#4307365
710
711 IF x_err_num <> 0 then
712 RAISE FAILED_INSERTING_WT;
713 END IF;
714
715 /* Make sure the Debit/Credit for Representative Lot and Resulting Lots
716 are balanced */
717
718 l_stmt_num := 170;
719 CSTPSMUT.BALANCE_ACCOUNTING(p_mmt_transaction_id,
720 l_wip_transaction_id,
721 l_transaction_type,
722 x_err_msg,
723 x_err_code,
724 x_err_num);
725 IF x_err_num <> 0 then
726 RAISE FAILED_BALANCING_ACCT;
727 END IF;
728
729 /* Update WPB of Representative Lot */
730 l_stmt_num := 180;
731 CSTPSMUT.START_LOT(
732 p_mmt_transaction_id,
733 l_wip_transaction_id,
734 c_start.wip_entity_id,
735 l_acct_period_id,
736 p_user_id,
737 p_login_id,
738 p_request_id,
739 p_prog_application_id,
740 p_program_id,
741 x_err_num,
742 x_err_code,
743 x_err_msg);
744 IF x_err_num <> 0 then
745 RAISE FAILED_INSERTING_START_LOT;
746 END IF;
747
748 END LOOP;
749
750 l_stmt_num := 190;
751
752 FOR new_job in c_new_jobs LOOP
753 l_ins_woo := INSERT_WOO (
754 new_job.wip_entity_id,
755 l_organization_id,
756 l_operation_seq_num,
757 p_user_id,
758 p_login_id,
759 p_request_id,
760 p_prog_application_id,
761 p_program_id );
762 IF l_ins_woo = FALSE THEN
763 RAISE INSERT_WOO_ERROR;
764 END IF;
765 END LOOP;
766
767 l_stmt_num := 200;
768
769 SELECT count(*)
770 INTO l_wta_exists
771 FROM WIP_TRANSACTION_ACCOUNTS
772 WHERE transaction_id = l_wip_transaction_id
773 and rownum=1;
774
775 IF l_wta_exists > 0 THEN
776 /* SLA Event Seeding */
777 l_trx_info.TRANSACTION_ID := l_wip_transaction_id;
778 l_trx_info.INV_ORGANIZATION_ID := l_organization_id;
779 l_trx_info.WIP_RESOURCE_ID := -1;
780 l_trx_info.WIP_BASIS_TYPE_ID := -1;
781 l_trx_info.TXN_TYPE_ID := 11;
782 l_trx_info.TRANSACTION_DATE := l_transaction_date;
783
784
785 l_stmt_num := 210;
786
787 CST_XLA_PVT.Create_WIPXLAEvent(
788 p_api_version => 1.0,
789 p_init_msg_list => FND_API.G_FALSE,
790 p_commit => FND_API.G_FALSE,
791 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
792 x_return_status => l_return_status,
793 x_msg_count => l_msg_count,
794 x_msg_data => l_msg_data,
795 p_trx_info => l_trx_info);
796
797 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
798 RAISE FND_API.g_exc_unexpected_error;
799 END IF;
800
801 END IF;
802 IF(l_debug_flag = 'Y') THEN
803 fnd_file.put_line(fnd_file.log, 'CSTPSMUT.COST_SPLIT_TXN >>>');
804 END IF;
805
806
807 EXCEPTION
808 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
809 x_err_num := -1;
810 x_err_code := 'Inconsistent API Version';--FND_API.G_RET_SYS_ERROR;
811 x_err_msg := 'Inconsistent API Version: CSTPSMUT.COST_SPLIT_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
812 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
813 WHEN GET_JOB_VALUE_FAILURE THEN
814 x_err_num := -1;
815 x_err_code := 'Error getting Job Charges/Scrap';
816 x_err_msg := 'Error getting Job Charges/Scrap: CSTPSMUT.COST_SPLIT_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
817 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
818 WHEN FAILED_INSERTING_START_LOT THEN
819 x_err_num := -1;
820 x_err_code := 'Error Inserting WPB Information for Starting Lot';
821 x_err_msg := 'Error Inserting WPB Information for Starting Lot: CSTPSMUT.COST_SPLIT_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
822 WHEN FAILED_BALANCING_ACCT THEN
823 x_err_num := -1;
824 x_err_code := 'Error Balancing Accounts';
825 x_err_msg := 'Error Balancing Accounts: CSTPSMUT.COST_SPLIT_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
826 WHEN FAILED_INSERTING_WT THEN
827 x_err_num := -1;
828 x_err_code := 9999;
829 x_err_msg := 'Error inserting into Wip Transactions: CSTPSMUT.COST_SPLIT_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
830 WHEN FAILED_INSERTING_WTA THEN
831 x_err_num := -1;
832 x_err_code := 'Error inserting into Wip Transaction Accounts';
833 x_err_msg := 'Error inserting into Wip Transaction Accounts: CSTPSMUT.COST_SPLIT_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
834 WHEN FAILED_INSERTING_MTA THEN
835 x_err_num := -1;
836 x_err_code := 'Error inserting into MTL Transaction Accounts';
837 x_err_msg := 'Error inserting into MTL Transaction Accounts: CSTPSMUT.COST_SPLIT_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
838 WHEN FAILED_INSERTING_RESULT_LOT THEN
839 x_err_num := -1;
840 x_err_code := 'Error inserting into WPB for Resulting Lot';
841 x_err_msg := 'Error inserting into WPB for Resulting Lot: CSTPSMUT.COST_SPLIT_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
842
843 WHEN INSERT_WOO_ERROR THEN
844 x_err_num := -1;
845 x_err_code := 'Error inserting into WOO';
846 x_err_msg := 'Error inserting into WOO: CSTPSMUT.COST_SPLIT_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
847 WHEN OTHERS THEN
848 x_err_num := -1;
849 x_err_code := 'Error in CSTPSMUT.COST_SPLIT_TXN';
850 x_err_msg := 'Error in CSTPSMUT.COST_SPLIT_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
851 END COST_SPLIT_TXN;
852
853
854
855 ----------------------------------------------------------------------------
856 -- PROCEDURE --
857 -- COST_MERGE_TXN --
858 -- --
859 -- DESCRIPTION --
860 -- This procedure costs a lot merge transaction. It inserts entries in
861 -- MTA, WT and WTA. It also updates the WPB for all the involved jobs.
862 -- --
863 -- PURPOSE: --
864 -- OSFM Lot Transactions Costing for Oracle Applications Rel 11i.8
865 -- --
866 -- PARAMETERS: --
867 -- p_app_version API version
868 -- p_transaction_id Transaction ID from WSMT
869 -- p_mmt_transaction_id Transaction ID form MMT
870 -- p_request_id Request ID of calling worker
871 -- p_transaction_date Transaction Date
872 -- p_prog_application_id
873 -- p_program_id
874 -- p_login_id
875 -- p_user_id Concurrent WHO Parameters
876 -- x_err_num Error Number
877 -- x_err_code Error Code --
878 -- x_err_msg Error Message --
879 -- HISTORY: --
880 -- August-2002 Vinit Creation --
881 ----------------------------------------------------------------------------
882 PROCEDURE COST_MERGE_TXN (p_api_version IN NUMBER,
883 p_transaction_id IN NUMBER,
884 p_mmt_transaction_id IN NUMBER,
885 p_transaction_date IN DATE,
886 p_prog_application_id IN NUMBER,
887 p_program_id IN NUMBER,
888 p_request_id IN NUMBER,
889 p_login_id IN NUMBER,
890 p_user_id IN NUMBER,
891 x_err_num IN OUT NOCOPY NUMBER,
892 x_err_code IN OUT NOCOPY VARCHAR2,
893 x_err_msg IN OUT NOCOPY VARCHAR2) IS
894
895 /* Parameters */
896 l_transaction_type NUMBER;
897 l_organization_id NUMBER;
898 l_transaction_date DATE;
899 l_min_acct_unit NUMBER := 0;
900 l_ext_prec NUMBER(2) := 0;
901 l_wip_transaction_id NUMBER;
902 l_acct_period_id NUMBER;
903
904 /* Local Variables */
905 l_stmt_num NUMBER := 0;
906 l_ins_woo BOOLEAN := TRUE;
907 l_le_transaction_date DATE;
908
909 /* API */
910 l_api_name CONSTANT VARCHAR2(240) := 'COST_MERGE_TXN';
911 l_api_version CONSTANT NUMBER := 1.0;
912
913 /* Representative Lot Information */
914 l_rep_wip_entity_id NUMBER;
915 l_available_quantity NUMBER;
916 l_job_start_quantity NUMBER;
917 l_operation_seq_num NUMBER;
918 l_intraoperation_step NUMBER;
919
920
921 /* Resulting Jobs */
922 l_total_resulting_qty NUMBER;
923 l_result_wip_entity_id NUMBER;
924
925 /* Job Charges */
926 l_pl_mtl_cost_in NUMBER := 0;
927 l_pl_mto_cost_in NUMBER := 0;
928 l_pl_res_cost_in NUMBER := 0;
929 l_pl_ovh_cost_in NUMBER := 0;
930 l_pl_osp_cost_in NUMBER := 0;
931 l_tl_res_cost_in NUMBER := 0;
932 l_tl_ovh_cost_in NUMBER := 0;
933 l_tl_osp_cost_in NUMBER := 0;
934
935 /* Relieved Costs */
936 l_pl_mtl_cost_out NUMBER := 0;
937 l_pl_mto_cost_out NUMBER := 0;
938 l_pl_res_cost_out NUMBER := 0;
939 l_pl_ovh_cost_out NUMBER := 0;
940 l_pl_osp_cost_out NUMBER := 0;
941 l_tl_res_cost_out NUMBER := 0;
942 l_tl_ovh_cost_out NUMBER := 0;
943 l_tl_osp_cost_out NUMBER := 0;
944
945
946
947 /* Representative Job Charges */
948 l_pl_rep_mtl_cost_in NUMBER := 0;
949 l_pl_rep_mto_cost_in NUMBER := 0;
950 l_pl_rep_res_cost_in NUMBER := 0;
951 l_pl_rep_ovh_cost_in NUMBER := 0;
952 l_pl_rep_osp_cost_in NUMBER := 0;
953 l_tl_rep_res_cost_in NUMBER := 0;
954 l_tl_rep_ovh_cost_in NUMBER := 0;
955 l_tl_rep_osp_cost_in NUMBER := 0;
956
957 /* Representative Job Relieved Costs */
958 l_pl_rep_mtl_cost_out NUMBER := 0;
959 l_pl_rep_mto_cost_out NUMBER := 0;
960 l_pl_rep_res_cost_out NUMBER := 0;
961 l_pl_rep_ovh_cost_out NUMBER := 0;
962 l_pl_rep_osp_cost_out NUMBER := 0;
963 l_tl_rep_res_cost_out NUMBER := 0;
964 l_tl_rep_ovh_cost_out NUMBER := 0;
965 l_tl_rep_osp_cost_out NUMBER := 0;
966
967 /* Net Cost and Total Costs */
968 l_pl_mtl_net NUMBER := 0;
969 l_pl_mto_net NUMBER := 0;
970 l_pl_res_net NUMBER := 0;
971 l_pl_ovh_net NUMBER := 0;
972 l_pl_osp_net NUMBER := 0;
973 l_tl_res_net NUMBER := 0;
974 l_tl_ovh_net NUMBER := 0;
975 l_tl_osp_net NUMBER := 0;
976
977 l_total_tl_res NUMBER := 0;
978 l_total_tl_ovh NUMBER := 0;
979 l_total_tl_osp NUMBER := 0;
980 l_total_pl_mtl NUMBER := 0;
981 l_total_pl_mto NUMBER := 0;
982 l_total_pl_res NUMBER := 0;
983 l_total_pl_ovh NUMBER := 0;
984 l_total_pl_osp NUMBER := 0;
985
986 l_total_qty NUMBER := 0;
987
988 /* Exceptions */
989
990 GET_JOB_VALUE_FAILURE EXCEPTION;
991 FAILED_INSERTING_START_LOT EXCEPTION;
992 FAILED_BALANCING_ACCT EXCEPTION;
993 FAILED_INSERTING_WT EXCEPTION;
994 FAILED_INSERTING_WTA EXCEPTION;
995 FAILED_INSERTING_MTA EXCEPTION;
996 FAILED_INSERTING_RESULT_LOT EXCEPTION;
997 INSERT_WOO_ERROR EXCEPTION;
998
999 /* Accounting Line Types */
1000 MERGE_RESULT_ACT_LTYPE NUMBER := 24;
1001 MERGE_START_ACT_LTYPE NUMBER := 23;
1002
1003 /* SLA Event Seeding */
1004 l_wta_exists NUMBER;
1005 l_return_status VARCHAR2(1);
1006 l_msg_count NUMBER;
1007 l_msg_data VARCHAR2(2000);
1008 l_trx_info CST_XLA_PVT.t_xla_wip_trx_info;
1009
1010
1011 CURSOR c_start_lot IS
1012 SELECT available_quantity, wip_entity_id
1013 FROM wsm_sm_starting_jobs
1014 WHERE transaction_id = p_transaction_id;
1015
1016 CURSOR c_result_lot IS
1017 SELECT start_quantity, wip_entity_id,
1018 nvl(starting_operation_seq_num,10) starting_operation_seq_num,
1019 nvl(starting_intraoperation_step, WIP_CONSTANTS.QUEUE) starting_intraoperation_step,
1020 common_routing_sequence_id
1021 FROM wsm_sm_resulting_jobs
1022 WHERE transaction_id = p_transaction_id;
1023
1024 CURSOR c_new_jobs IS
1025 SELECT wip_entity_id
1026 FROM wsm_sm_resulting_jobs
1027 WHERE transaction_id = p_transaction_id
1028 AND wip_entity_id not in
1029 ( SELECT wip_entity_id
1030 FROM wsm_sm_starting_jobs
1031 WHERE transaction_id = p_transaction_id );
1032
1033 BEGIN
1034
1035 /* Check API Compatibility */
1036 l_stmt_num := 10;
1037
1038 IF(l_debug_flag = 'Y') THEN
1039 fnd_file.put_line(fnd_file.log, 'CSTPSMUT.COST_MERGE_TXN ... <<< ');
1040 fnd_file.put_line(fnd_file.log, 'Costing Transaction: '||to_char(p_transaction_id));
1041 END IF;
1042
1043 IF NOT FND_API.COMPATIBLE_API_CALL (
1044 l_api_version,
1045 p_api_version,
1046 l_api_name,
1047 G_PKG_NAME ) THEN
1048 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1049 END IF;
1050
1051
1052 /* Get Transaction Information from WSMT */
1053
1054 l_stmt_num := 20;
1055 SELECT organization_id,
1056 transaction_type_id,
1057 transaction_date
1058 INTO l_organization_id,
1059 l_transaction_type,
1060 l_transaction_date
1061 FROM WSM_SPLIT_MERGE_TRANSACTIONS
1062 WHERE transaction_id = p_transaction_id;
1063
1064 /* Get Currency Information and Precision */
1065 l_stmt_num := 30;
1066 SELECT NVL(FC.minimum_accountable_unit, POWER(10,NVL(-precision,0))),
1067 NVL(FC.extended_precision,NVL(FC.precision,0))
1068 INTO l_min_acct_unit,
1069 l_ext_prec
1070 FROM fnd_currencies FC,
1071 CST_ORGANIZATION_DEFINITIONS O
1072 WHERE O.organization_id = l_organization_id
1073 AND O.currency_code = FC.currency_code;
1074
1075 /* Accounting Period */
1076 l_stmt_num := 35;
1077
1078 l_le_transaction_date := INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(
1079 l_transaction_date,
1080 l_organization_id);
1081
1082 l_stmt_num := 40;
1083
1084 SELECT acct_period_id
1085 INTO l_acct_period_id
1086 FROM org_acct_periods
1087 WHERE organization_id = l_organization_id
1088 AND l_le_transaction_date
1089 between period_start_date and schedule_close_date;
1090
1091
1092 /* Get Information from WSSJ */
1093
1094 l_stmt_num := 50;
1095 SELECT wip_entity_id,
1096 operation_seq_num,
1097 intraoperation_step,
1098 job_start_quantity,
1099 available_quantity
1100 INTO l_rep_wip_entity_id,
1101 l_operation_seq_num,
1102 l_intraoperation_step,
1103 l_job_start_quantity,
1104 l_available_quantity
1105 FROM WSM_SM_STARTING_JOBS
1106 WHERE transaction_id = p_transaction_id
1107 AND representative_flag = 'Y';
1108
1109 SELECT wip_entity_id
1110 INTO l_result_wip_entity_id
1111 FROM wsm_sm_resulting_jobs
1112 WHERE transaction_id = p_transaction_id;
1113
1114
1115 /* Get the Job Charges */
1116
1117 l_stmt_num := 60;
1118
1119 CSTPSMUT.GET_JOB_VALUE ( p_api_version => 1.0,
1120 p_lot_size => l_job_start_quantity,
1121 p_run_mode => 1, -- CHARGE
1122 p_entity_id => l_rep_wip_entity_id,
1123 p_intraop_step => l_intraoperation_step,
1124 p_operation_seq_num => l_operation_seq_num,
1125 p_transaction_id => p_transaction_id,
1126 p_txn_type => WSMPCNST.MERGE,
1127 p_org_id => l_organization_id,
1128 x_err_num => x_err_num,
1129 x_err_code => x_err_code,
1130 x_err_msg => x_err_msg,
1131 x_pl_mtl_cost => l_pl_mtl_cost_in,
1132 x_pl_mto_cost => l_pl_mto_cost_in,
1133 x_pl_res_cost => l_pl_res_cost_in,
1134 x_pl_ovh_cost => l_pl_ovh_cost_in,
1135 x_pl_osp_cost => l_pl_osp_cost_in,
1136 x_tl_res_cost => l_tl_res_cost_in,
1137 x_tl_ovh_cost => l_tl_ovh_cost_in,
1138 x_tl_osp_cost => l_tl_osp_cost_in );
1139
1140 IF x_err_num <> 0 THEN
1141 RAISE GET_JOB_VALUE_FAILURE;
1142 END IF;
1143
1144 /* Get the Costs Relieved from the job */
1145 l_stmt_num := 70;
1146
1147 CSTPSMUT.GET_JOB_VALUE ( p_api_version => 1.0,
1148 p_lot_size => l_job_start_quantity,
1149 p_run_mode => 2, -- SCRAP
1150 p_entity_id => l_rep_wip_entity_id,
1151 p_intraop_step => l_intraoperation_step,
1152 p_operation_seq_num => l_operation_seq_num,
1153 p_transaction_id => p_transaction_id,
1154 p_txn_type => WSMPCNST.MERGE,
1155 p_org_id => l_organization_id,
1156 x_err_num => x_err_num,
1157 x_err_code => x_err_code,
1158 x_err_msg => x_err_msg,
1159 x_pl_mtl_cost => l_pl_mtl_cost_out,
1160 x_pl_mto_cost => l_pl_mto_cost_out,
1161 x_pl_res_cost => l_pl_res_cost_out,
1162 x_pl_ovh_cost => l_pl_ovh_cost_out,
1163 x_pl_osp_cost => l_pl_osp_cost_out,
1164 x_tl_res_cost => l_tl_res_cost_out,
1165 x_tl_ovh_cost => l_tl_ovh_cost_out,
1166 x_tl_osp_cost => l_tl_osp_cost_out );
1167
1168 l_stmt_num := 80;
1169
1170 l_pl_mtl_net := l_pl_mtl_cost_in - l_pl_mtl_cost_out;
1171 l_pl_mto_net := l_pl_mto_cost_in - l_pl_mto_cost_out;
1172 l_pl_res_net := l_pl_res_cost_in - l_pl_res_cost_out;
1173 l_pl_ovh_net := l_pl_ovh_cost_in - l_pl_ovh_cost_out;
1174 l_pl_osp_net := l_pl_osp_cost_in - l_pl_osp_cost_out;
1175
1176
1177 l_tl_res_net := l_tl_res_cost_in - l_tl_res_cost_out;
1178 l_tl_ovh_net := l_tl_ovh_cost_in - l_tl_ovh_cost_out;
1179 l_tl_osp_net := l_tl_osp_cost_in - l_tl_osp_cost_out;
1180
1181 l_stmt_num := 90;
1182
1183 get_wip_txn_id(l_wip_transaction_id,
1184 x_err_num,
1185 x_err_code,
1186 x_err_msg);
1187
1188
1189 l_stmt_num := 100;
1190
1191 FOR c_start in c_start_lot LOOP
1192 IF (l_rep_wip_entity_id <> c_start.wip_entity_id) THEN
1193 l_stmt_num := 110;
1194 IF(l_debug_flag = 'Y') THEN
1195 fnd_file.put_line(fnd_file.log, 'Processing Job: '||to_char(c_start.wip_entity_id));
1196 END IF;
1197
1198 CSTPSMUT.INSERT_MAT_TXN_ACCT(
1199 l_transaction_date,
1200 l_min_acct_unit,
1201 l_ext_prec,
1202 l_transaction_type,
1203 p_mmt_transaction_id,
1204 l_organization_id,
1205 c_start.wip_entity_id,
1206 MERGE_START_ACT_LTYPE,
1207 -c_start.available_quantity,
1208 -c_start.available_quantity / l_available_quantity * l_pl_mtl_net,
1209 -c_start.available_quantity / l_available_quantity * l_pl_mto_net,
1210 -c_start.available_quantity / l_available_quantity * l_pl_res_net,
1211 -c_start.available_quantity / l_available_quantity * l_pl_ovh_net,
1212 -c_start.available_quantity / l_available_quantity * l_pl_osp_net,
1213 p_user_id,
1214 p_login_id,
1215 p_request_id,
1216 p_prog_application_id,
1217 p_program_id,
1218 l_debug_flag,
1219 x_err_num,
1220 x_err_code,
1221 x_err_msg);
1222
1223 IF x_err_num <> 0 then
1224 RAISE FAILED_INSERTING_MTA;
1225 END IF;
1226
1227 /* Insert into WTA */
1228
1229 l_stmt_num := 110;
1230
1231 CSTPSMUT.INSERT_WIP_TXN_ACCT(
1232 l_transaction_date,
1233 l_min_acct_unit,
1234 l_ext_prec,
1235 p_transaction_id,
1236 l_transaction_type,
1237 l_wip_transaction_id,
1238 l_organization_id,
1239 c_start.wip_entity_id,
1240 MERGE_START_ACT_LTYPE,
1241 -c_start.available_quantity,
1242 0, -- This Level Material Cost
1243 0, -- This Level Material Ovhd Cost
1244 -c_start.available_quantity / l_available_quantity * l_tl_res_net,
1245 -c_start.available_quantity / l_available_quantity * l_tl_ovh_net,
1246 -c_start.available_quantity / l_available_quantity * l_tl_osp_net,
1247 p_user_id,
1248 p_login_id,
1249 p_request_id,
1250 p_prog_application_id,
1251 p_program_id,
1252 l_debug_flag,
1253 x_err_num,
1254 x_err_code,
1255 x_err_msg);
1256
1257 IF x_err_num <> 0 then
1258 RAISE FAILED_INSERTING_WTA;
1259 END IF;
1260
1261 /* Update WPB */
1262
1263 l_stmt_num := 120;
1264
1265 CSTPSMUT.START_LOT(
1266 p_mmt_transaction_id,
1267 l_wip_transaction_id,
1268 c_start.wip_entity_id,
1269 l_acct_period_id,
1270 p_user_id,
1271 p_login_id,
1272 p_request_id,
1273 p_prog_application_id,
1274 p_program_id,
1275 x_err_num,
1276 x_err_code,
1277 x_err_msg);
1278 IF x_err_num <> 0 then
1279 RAISE FAILED_INSERTING_RESULT_LOT;
1280 END IF;
1281
1282 l_stmt_num := 130;
1283
1284 /* Update the Amount to be relieved */
1285
1286 l_total_tl_res := l_total_tl_res + (ROUND(c_start.available_quantity / l_available_quantity * l_tl_res_net / l_min_acct_unit) * l_min_acct_unit);
1287 l_total_tl_ovh := l_total_tl_ovh + (ROUND(c_start.available_quantity / l_available_quantity * l_tl_ovh_net / l_min_acct_unit) * l_min_acct_unit);
1288 l_total_tl_osp := l_total_tl_osp + (ROUND(c_start.available_quantity / l_available_quantity * l_tl_osp_net / l_min_acct_unit) * l_min_acct_unit);
1289 l_total_pl_mtl := l_total_pl_mtl + (ROUND(c_start.available_quantity / l_available_quantity * l_pl_mtl_net / l_min_acct_unit) * l_min_acct_unit);
1290 l_total_pl_mto := l_total_pl_mto + (ROUND(c_start.available_quantity / l_available_quantity * l_pl_mto_net / l_min_acct_unit) * l_min_acct_unit);
1291 l_total_pl_res := l_total_pl_res + (ROUND(c_start.available_quantity / l_available_quantity * l_pl_res_net / l_min_acct_unit) * l_min_acct_unit);
1292 l_total_pl_ovh := l_total_pl_ovh + (ROUND(c_start.available_quantity / l_available_quantity * l_pl_ovh_net / l_min_acct_unit) * l_min_acct_unit);
1293 l_total_pl_osp := l_total_pl_osp + (ROUND(c_start.available_quantity / l_available_quantity * l_pl_osp_net / l_min_acct_unit) * l_min_acct_unit);
1294 l_total_qty := l_total_qty + c_start.available_quantity;
1295
1296 END IF; -- Non Representative Lot
1297
1298 END LOOP; -- End Starting Lots
1299
1300 /* If the resulting job is not the representative lot,
1301 - Get the net job costs from the representative lot
1302 and add it to the total. This is the amount relieved
1303 from the representative lot itself.
1304 To get net job value (including lot based resources) call
1305 GET_JOB_VALUE with transaction_type split.
1306 */
1307
1308
1309 IF( l_rep_wip_entity_id <> l_result_wip_entity_id ) THEN
1310 /* Get the Job Charges */
1311
1312 l_stmt_num := 140;
1313
1314 CSTPSMUT.GET_JOB_VALUE ( p_api_version => 1.0,
1315 p_lot_size => l_job_start_quantity,
1316 p_run_mode => 1, -- CHARGE
1317 p_entity_id => l_rep_wip_entity_id,
1318 p_intraop_step => l_intraoperation_step,
1319 p_operation_seq_num => l_operation_seq_num,
1320 p_transaction_id => p_transaction_id,
1321 p_txn_type => WSMPCNST.SPLIT,
1322 p_org_id => l_organization_id,
1323 x_err_num => x_err_num,
1324 x_err_code => x_err_code,
1325 x_err_msg => x_err_msg,
1326 x_pl_mtl_cost => l_pl_rep_mtl_cost_in,
1327 x_pl_mto_cost => l_pl_rep_mto_cost_in,
1328 x_pl_res_cost => l_pl_rep_res_cost_in,
1329 x_pl_ovh_cost => l_pl_rep_ovh_cost_in,
1330 x_pl_osp_cost => l_pl_rep_osp_cost_in,
1331 x_tl_res_cost => l_tl_rep_res_cost_in,
1332 x_tl_ovh_cost => l_tl_rep_ovh_cost_in,
1333 x_tl_osp_cost => l_tl_rep_osp_cost_in );
1334
1335 IF x_err_num <> 0 THEN
1336 RAISE GET_JOB_VALUE_FAILURE;
1337 END IF;
1338
1339 /* Get the Costs Relieved from the job */
1340 l_stmt_num := 150;
1341
1342 CSTPSMUT.GET_JOB_VALUE ( p_api_version => 1.0,
1343 p_lot_size => l_job_start_quantity,
1344 p_run_mode => 2, -- SCRAP
1345 p_entity_id => l_rep_wip_entity_id,
1346 p_intraop_step => l_intraoperation_step,
1347 p_operation_seq_num => l_operation_seq_num,
1348 p_transaction_id => p_transaction_id,
1349 p_txn_type => WSMPCNST.SPLIT,
1350 p_org_id => l_organization_id,
1351 x_err_num => x_err_num,
1352 x_err_code => x_err_code,
1353 x_err_msg => x_err_msg,
1354 x_pl_mtl_cost => l_pl_rep_mtl_cost_out,
1355 x_pl_mto_cost => l_pl_rep_mto_cost_out,
1356 x_pl_res_cost => l_pl_rep_res_cost_out,
1357 x_pl_ovh_cost => l_pl_rep_ovh_cost_out,
1358 x_pl_osp_cost => l_pl_rep_osp_cost_out,
1359 x_tl_res_cost => l_tl_rep_res_cost_out,
1360 x_tl_ovh_cost => l_tl_rep_ovh_cost_out,
1361 x_tl_osp_cost => l_tl_rep_osp_cost_out );
1362
1363 /* Update MTA and WTA for representative start lot */
1364 /* Relieve everything that has been charged */
1365
1366 CSTPSMUT.INSERT_MAT_TXN_ACCT(
1367 l_transaction_date,
1368 l_min_acct_unit,
1369 l_ext_prec,
1370 l_transaction_type,
1371 p_mmt_transaction_id,
1372 l_organization_id,
1373 l_rep_wip_entity_id,
1374 MERGE_START_ACT_LTYPE,
1375 -l_available_quantity,
1376 -(l_pl_rep_mtl_cost_in - l_pl_rep_mtl_cost_out),
1377 -(l_pl_rep_mto_cost_in - l_pl_rep_mto_cost_out),
1378 -(l_pl_rep_res_cost_in - l_pl_rep_res_cost_out),
1379 -(l_pl_rep_ovh_cost_in - l_pl_rep_ovh_cost_out),
1380 -(l_pl_rep_osp_cost_in - l_pl_rep_osp_cost_out),
1381 p_user_id,
1382 p_login_id,
1383 p_request_id,
1384 p_prog_application_id,
1385 p_program_id,
1386 l_debug_flag,
1387 x_err_num,
1388 x_err_code,
1389 x_err_msg);
1390
1391 IF x_err_num <> 0 then
1392 RAISE FAILED_INSERTING_MTA;
1393 END IF;
1394
1395 /* Insert into WTA */
1396
1397 l_stmt_num := 160;
1398
1399 CSTPSMUT.INSERT_WIP_TXN_ACCT(
1400 l_transaction_date,
1401 l_min_acct_unit,
1402 l_ext_prec,
1403 p_transaction_id,
1404 l_transaction_type,
1405 l_wip_transaction_id,
1406 l_organization_id,
1407 l_rep_wip_entity_id,
1408 MERGE_START_ACT_LTYPE,
1409 -l_available_quantity,
1410 0, -- This Level Material Cost
1411 0, -- This Level Material Ovhd Cost
1412 -(l_tl_rep_res_cost_in - l_tl_rep_res_cost_out),
1413 -(l_tl_rep_ovh_cost_in - l_tl_rep_ovh_cost_out),
1414 -(l_tl_rep_osp_cost_in - l_tl_rep_osp_cost_out),
1415 p_user_id,
1416 p_login_id,
1417 p_request_id,
1418 p_prog_application_id,
1419 p_program_id,
1420 l_debug_flag,
1421 x_err_num,
1422 x_err_code,
1423 x_err_msg);
1424
1425 IF x_err_num <> 0 then
1426 RAISE FAILED_INSERTING_WTA;
1427 END IF;
1428
1429 /* Update WPB */
1430
1431 l_stmt_num := 170;
1432
1433 CSTPSMUT.START_LOT(
1434 p_mmt_transaction_id,
1435 l_wip_transaction_id,
1436 l_rep_wip_entity_id,
1437 l_acct_period_id,
1438 p_user_id,
1439 p_login_id,
1440 p_request_id,
1441 p_prog_application_id,
1442 p_program_id,
1443 x_err_num,
1444 x_err_code,
1445 x_err_msg);
1446 IF x_err_num <> 0 then
1447 RAISE FAILED_INSERTING_RESULT_LOT;
1448 END IF;
1449
1450
1451 l_stmt_num := 180;
1452
1453 l_total_tl_res := l_total_tl_res + ROUND((l_tl_rep_res_cost_in - l_tl_rep_res_cost_out)/l_min_acct_unit) * l_min_acct_unit;
1454 l_total_tl_ovh := l_total_tl_ovh + ROUND((l_tl_rep_ovh_cost_in - l_tl_rep_ovh_cost_out)/l_min_acct_unit) * l_min_acct_unit;
1455 l_total_tl_osp := l_total_tl_osp + ROUND((l_tl_rep_osp_cost_in - l_tl_rep_osp_cost_out)/l_min_acct_unit) * l_min_acct_unit;
1456 l_total_pl_mtl := l_total_pl_mtl + ROUND((l_pl_rep_mtl_cost_in - l_pl_rep_mtl_cost_out)/l_min_acct_unit) * l_min_acct_unit;
1457 l_total_pl_mto := l_total_pl_mto + ROUND((l_pl_rep_mto_cost_in - l_pl_rep_mto_cost_out)/l_min_acct_unit) * l_min_acct_unit;
1458 l_total_pl_res := l_total_pl_res + ROUND((l_pl_rep_res_cost_in - l_pl_rep_res_cost_out)/l_min_acct_unit) * l_min_acct_unit;
1459 l_total_pl_ovh := l_total_pl_ovh + ROUND((l_pl_rep_ovh_cost_in - l_pl_rep_ovh_cost_out)/l_min_acct_unit) * l_min_acct_unit;
1460 l_total_pl_osp := l_total_pl_osp + ROUND((l_pl_rep_osp_cost_in - l_pl_rep_osp_cost_out)/l_min_acct_unit) * l_min_acct_unit;
1461 l_total_qty := l_total_qty + ROUND(l_available_quantity/l_min_acct_unit) * l_min_acct_unit;
1462
1463 END IF;
1464
1465 /* Only One Lot in Starting Jobs but cursor can be used since it is already there */
1466 IF(l_debug_flag = 'Y') THEN
1467 fnd_file.put_line(fnd_file.log, 'Net Charges in Resulting Job: ');
1468 fnd_file.put_line(fnd_file.log, 'PL_MTL: '||to_char(l_total_pl_mtl));
1469 fnd_file.put_line(fnd_file.log, 'PL_MOH: '||to_char(l_total_pl_mto));
1470 fnd_file.put_line(fnd_file.log, 'PL_RES: '||to_char(l_total_pl_res));
1471 fnd_file.put_line(fnd_file.log, 'PL_OVH: '||to_char(l_total_pl_ovh));
1472 fnd_file.put_line(fnd_file.log, 'TL_RES: '||to_char(l_total_tl_res));
1473 fnd_file.put_line(fnd_file.log, 'TL_OVH: '||to_char(l_total_tl_ovh));
1474 END IF;
1475
1476
1477 l_stmt_num := 200;
1478 IF(l_debug_flag = 'Y') THEN
1479 fnd_file.put_line(fnd_file.log, 'Processing Result Lot: '||to_char(l_result_wip_entity_id));
1480 END IF;
1481
1482 /* Insert into MTA for Result Lot */
1483 CSTPSMUT.INSERT_MAT_TXN_ACCT(
1484 l_transaction_date,
1485 l_min_acct_unit,
1486 l_ext_prec,
1487 l_transaction_type,
1488 p_mmt_transaction_id,
1489 l_organization_id,
1490 l_result_wip_entity_id,
1491 MERGE_RESULT_ACT_LTYPE,
1492 l_total_qty,
1493 l_total_pl_mtl,
1494 l_total_pl_mto,
1495 l_total_pl_res,
1496 l_total_pl_ovh,
1497 l_total_pl_osp,
1498 p_user_id,
1499 p_login_id,
1500 p_request_id,
1501 p_prog_application_id,
1502 p_program_id,
1503 l_debug_flag,
1504 x_err_num,
1505 x_err_code,
1506 x_err_msg);
1507
1508 IF x_err_num <> 0 then
1509 RAISE FAILED_INSERTING_MTA;
1510 END IF;
1511
1512 l_stmt_num := 210;
1513
1514 /* Insert TL Accounting into WTA */
1515
1516 CSTPSMUT.INSERT_WIP_TXN_ACCT(
1517 l_transaction_date,
1518 l_min_acct_unit,
1519 l_ext_prec,
1520 p_transaction_id,
1521 l_transaction_type,
1522 l_wip_transaction_id,
1523 l_organization_id,
1524 l_result_wip_entity_id,
1525 MERGE_RESULT_ACT_LTYPE,
1526 l_total_qty,
1527 0, -- This Level Material Cost
1528 0, -- This Level Material Ovhd Cost
1529 l_total_tl_res,
1530 l_total_tl_ovh,
1531 l_total_tl_osp,
1532 p_user_id,
1533 p_login_id,
1534 p_request_id,
1535 p_prog_application_id,
1536 p_program_id,
1537 l_debug_flag,
1538 x_err_num,
1539 x_err_code,
1540 x_err_msg);
1541
1542 IF x_err_num <> 0 then
1543 RAISE FAILED_INSERTING_WTA;
1544 END IF;
1545
1546 /* Insert Transaction into WT */
1547
1548 l_stmt_num := 220;
1549 CSTPSMUT.INSERT_WIP_TXN(
1550 l_transaction_date,
1551 p_transaction_id,
1552 l_wip_transaction_id,
1553 l_acct_period_id,
1554 l_result_wip_entity_id,
1555 l_operation_seq_num,
1556 12, -- WIP Transaction type
1557 p_user_id,
1558 p_login_id,
1559 p_request_id,
1560 p_prog_application_id,
1561 p_program_id,
1562 l_debug_flag,
1563 x_err_num,
1564 x_err_code,
1565 x_err_msg,
1566 p_mmt_transaction_id); -- Added for Bug#4307365
1567
1568 IF x_err_num <> 0 then
1569 RAISE FAILED_INSERTING_WT;
1570 END IF;
1571
1572 /* Make sure the Debit/Credit for Representative Lot and Resulting Lots
1573 are balanced */
1574
1575 l_stmt_num := 190;
1576 CSTPSMUT.BALANCE_ACCOUNTING(p_mmt_transaction_id,
1577 l_wip_transaction_id,
1578 l_transaction_type,
1579 x_err_msg,
1580 x_err_code,
1581 x_err_num);
1582 IF x_err_num <> 0 then
1583 RAISE FAILED_BALANCING_ACCT;
1584 END IF;
1585
1586 /* Update WPB of Result Lot */
1587
1588 l_stmt_num := 200;
1589 CSTPSMUT.RESULT_LOT(
1590 p_mmt_transaction_id,
1591 l_wip_transaction_id,
1592 l_result_wip_entity_id,
1593 l_acct_period_id,
1594 p_user_id,
1595 p_login_id,
1596 p_request_id,
1597 p_prog_application_id,
1598 p_program_id,
1599 l_debug_flag,
1600 x_err_num,
1601 x_err_code,
1602 x_err_msg);
1603 IF x_err_num <> 0 then
1604 RAISE FAILED_INSERTING_START_LOT;
1605 END IF;
1606
1607 /* Insert into WOO for each of the new jobs created due to the split */
1608
1609 FOR new_job in c_new_jobs LOOP
1610 l_ins_woo := INSERT_WOO (
1611 new_job.wip_entity_id,
1612 l_organization_id,
1613 l_operation_seq_num,
1614 p_user_id,
1615 p_login_id,
1616 p_request_id,
1617 p_prog_application_id,
1618 p_program_id );
1619 IF l_ins_woo = FALSE THEN
1620 RAISE INSERT_WOO_ERROR;
1621 END IF;
1622 END LOOP;
1623
1624 l_stmt_num := 200;
1625
1626 SELECT count(*)
1627 INTO l_wta_exists
1628 FROM WIP_TRANSACTION_ACCOUNTS
1629 WHERE transaction_id = l_wip_transaction_id
1630 and rownum=1;
1631
1632 IF l_wta_exists > 0 THEN
1633 /* SLA Event Seeding */
1634 l_trx_info.TRANSACTION_ID := l_wip_transaction_id;
1635 l_trx_info.INV_ORGANIZATION_ID := l_organization_id;
1636 l_trx_info.WIP_RESOURCE_ID := -1;
1637 l_trx_info.WIP_BASIS_TYPE_ID := -1;
1638 l_trx_info.TXN_TYPE_ID := 12;
1639 l_trx_info.TRANSACTION_DATE := l_transaction_date;
1640
1641 l_stmt_num := 210;
1642
1643 CST_XLA_PVT.Create_WIPXLAEvent(
1644 p_api_version => 1.0,
1645 p_init_msg_list => FND_API.G_FALSE,
1646 p_commit => FND_API.G_FALSE,
1647 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1648 x_return_status => l_return_status,
1649 x_msg_count => l_msg_count,
1650 x_msg_data => l_msg_data,
1651 p_trx_info => l_trx_info);
1652
1653 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1654 RAISE FND_API.g_exc_unexpected_error;
1655 END IF;
1656
1657 END IF;
1658
1659
1660 IF(l_debug_flag = 'Y') THEN
1661 fnd_file.put_line(fnd_file.log, 'CSTPSMUT.COST_MERGE_TXN ... >>> ');
1662 END IF;
1663
1664 EXCEPTION
1665
1666 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1667 x_err_num := -1;
1668 x_err_code := 'Inconsistent API Version';--FND_API.G_RET_SYS_ERROR;
1669 x_err_msg := 'Inconsistent API Version: CSTPSMUT.COST_MERGE_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
1670 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1671 WHEN GET_JOB_VALUE_FAILURE THEN
1672 x_err_num := -1;
1673 x_err_code := 'Error getting Job Charges/Scrap';
1674 x_err_msg := 'Error getting Job Charges/Scrap: CSTPSMUT.COST_MERGE_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
1675 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1676 WHEN FAILED_INSERTING_START_LOT THEN
1677 x_err_num := -1;
1678 x_err_code := 'Error Inserting WPB Information for Starting Lot';
1679 x_err_msg := 'Error Inserting WPB Information for Starting Lot: CSTPSMUT.COST_MERGE_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
1680 WHEN FAILED_BALANCING_ACCT THEN
1681 x_err_num := -1;
1682 x_err_code := 'Error Balancing Accounts';
1683 x_err_msg := 'Error Balancing Accounts: CSTPSMUT.COST_MERGE_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
1684 WHEN FAILED_INSERTING_WT THEN
1685 x_err_num := -1;
1686 x_err_code := 'Error inserting into Wip Transactions';
1687 x_err_msg := 'Error inserting into Wip Transactions: CSTPSMUT.COST_MERGE_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
1688 WHEN FAILED_INSERTING_WTA THEN
1689 x_err_num := -1;
1690 x_err_code := 'Error inserting into Wip Transaction Accounts';
1691 x_err_msg := 'Error inserting into Wip Transaction Accounts: CSTPSMUT.COST_MERGE_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
1692 WHEN FAILED_INSERTING_MTA THEN
1693 x_err_num := -1;
1694 x_err_code := 'Error inserting into MTL Transaction Accounts';
1695 x_err_msg := 'Error inserting into MTL Transaction Accounts: CSTPSMUT.COST_MERGE_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
1696 WHEN FAILED_INSERTING_RESULT_LOT THEN
1697 x_err_num := -1;
1698 x_err_code := 'Error inserting into WPB for Resulting Lot';
1699 x_err_msg := 'Error inserting into WPB for Resulting Lot: CSTPSMUT.COST_MERGE_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
1700
1701 WHEN INSERT_WOO_ERROR THEN
1702 x_err_num := -1;
1703 x_err_code := 'Error inserting into WOO';
1704 x_err_msg := 'Error inserting into WOO: CSTPSMUT.COST_MERGE_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
1705
1706 END COST_MERGE_TXN;
1707
1708
1709 ----------------------------------------------------------------------------
1710 -- PROCEDURE --
1711 -- COST_UPDATE_QTY_TXN --
1712 -- --
1713 -- DESCRIPTION --
1714 -- This procedure costs a update lot quantity transaction.
1715 -- It inserts entries in MTA, WT and WTA. It also updates the WPB for
1716 -- all the involved jobs.
1717 -- --
1718 -- PURPOSE: --
1719 -- OSFM Lot Transactions Costing for Oracle Applications Rel 11i.8
1720 -- --
1721 -- PARAMETERS: --
1722 -- p_api_version API version
1723 -- p_transaction_id Transaction ID from WSMT
1724 -- p_mmt_transaction_id Transaction ID form MMT
1725 -- p_request_id Request ID of calling worker
1726 -- p_transaction_date Transaction Date
1727 -- p_prog_application_id
1728 -- p_program_id
1729 -- p_login_id
1730 -- p_user_id Concurrent WHO Parameters
1731 -- x_err_num Error Number
1732 -- x_err_code Error Code --
1733 -- x_err_msg Error Message --
1734 -- HISTORY: --
1735 -- August-2002 Vinit Creation --
1736 ----------------------------------------------------------------------------
1737 PROCEDURE COST_UPDATE_QTY_TXN
1738 (p_api_version IN NUMBER,
1739 p_transaction_id IN NUMBER,
1740 p_mmt_transaction_id IN NUMBER,
1741 p_transaction_date IN DATE,
1742 p_prog_application_id IN NUMBER,
1743 p_program_id IN NUMBER,
1744 p_request_id IN NUMBER,
1745 p_login_id IN NUMBER,
1746 p_user_id IN NUMBER,
1747 x_err_num IN OUT NOCOPY NUMBER,
1748 x_err_code IN OUT NOCOPY VARCHAR2,
1749 x_err_msg IN OUT NOCOPY VARCHAR2) IS
1750
1751 /* Parameters */
1752 l_transaction_type NUMBER;
1753 l_organization_id NUMBER;
1754 l_transaction_date DATE;
1755 l_min_acct_unit NUMBER := 0;
1756 l_ext_prec NUMBER(2) := 0;
1757 l_wip_transaction_id NUMBER;
1758 l_acct_period_id NUMBER;
1759
1760
1761 /* Local Variables */
1762 l_stmt_num NUMBER := 0;
1763 l_factor NUMBER := 0;
1764 l_le_transaction_date DATE;
1765
1766 /* API */
1767 l_api_name CONSTANT VARCHAR2(240) := 'COST_UPDATE_QTY_TXN';
1768 l_api_version CONSTANT NUMBER := 1.0;
1769
1770 /* Representative Lot Information */
1771 l_wip_entity_id NUMBER;
1772 l_start_quantity NUMBER;
1773 l_operation_seq_num NUMBER;
1774 l_intraoperation_step NUMBER := 1;
1775 l_job_start_quantity NUMBER;
1776 l_available_quantity NUMBER;
1777
1778 /* Intraoperation Step Not Needed */
1779
1780 /* Job Charges */
1781 l_pl_mtl_cost_in NUMBER := 0;
1782 l_pl_mto_cost_in NUMBER := 0;
1783 l_pl_res_cost_in NUMBER := 0;
1784 l_pl_ovh_cost_in NUMBER := 0;
1785 l_pl_osp_cost_in NUMBER := 0;
1786 l_tl_res_cost_in NUMBER := 0;
1787 l_tl_ovh_cost_in NUMBER := 0;
1788 l_tl_osp_cost_in NUMBER := 0;
1789
1790
1791 /* Relieved Costs */
1792 l_pl_mtl_cost_out NUMBER := 0;
1793 l_pl_mto_cost_out NUMBER := 0;
1794 l_pl_res_cost_out NUMBER := 0;
1795 l_pl_ovh_cost_out NUMBER := 0;
1796 l_pl_osp_cost_out NUMBER := 0;
1797 l_tl_res_cost_out NUMBER := 0;
1798 l_tl_ovh_cost_out NUMBER := 0;
1799 l_tl_osp_cost_out NUMBER := 0;
1800
1801
1802 /* Net Cost and Total Costs */
1803 l_pl_mtl_net NUMBER := 0;
1804 l_pl_mto_net NUMBER := 0;
1805 l_pl_res_net NUMBER := 0;
1806 l_pl_ovh_net NUMBER := 0;
1807 l_pl_osp_net NUMBER := 0;
1808 l_tl_res_net NUMBER := 0;
1809 l_tl_ovh_net NUMBER := 0;
1810 l_tl_osp_net NUMBER := 0;
1811
1812
1813 /* Exceptions */
1814
1815 GET_JOB_VALUE_FAILURE EXCEPTION;
1816 FAILED_INSERTING_START_LOT EXCEPTION;
1817 FAILED_BALANCING_ACCT EXCEPTION;
1818 FAILED_INSERTING_WT EXCEPTION;
1819 FAILED_INSERTING_WTA EXCEPTION;
1820 FAILED_INSERTING_MTA EXCEPTION;
1821 FAILED_INSERTING_RESULT_LOT EXCEPTION;
1822 FAILED_INSERTING_BONUS_WTA EXCEPTION;
1823 FAILED_INSERTING_BONUS_MTA EXCEPTION;
1824
1825 /* Accounting Line Types */
1826 UPD_QTY_RESULT_ACT_LTYPE NUMBER := 28;
1827 UPD_QTY_START_ACT_LTYPE NUMBER := 27;
1828
1829 /* SLA Event Seeding */
1830 l_wta_exists NUMBER;
1831 l_return_status VARCHAR2(1);
1832 l_msg_count NUMBER;
1833 l_msg_data VARCHAR2(2000);
1834 l_trx_info CST_XLA_PVT.t_xla_wip_trx_info;
1835
1836 BEGIN
1837 /* Check API Compatibility */
1838 l_stmt_num := 10;
1839
1840 IF NOT FND_API.COMPATIBLE_API_CALL (
1841 l_api_version,
1842 p_api_version,
1843 l_api_name,
1844 G_PKG_NAME ) THEN
1845 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1846 END IF;
1847
1848
1849 /* Get Transaction Information from WSMT */
1850
1851 l_stmt_num := 20;
1852 SELECT organization_id,
1853 transaction_type_id,
1854 transaction_date
1855 INTO l_organization_id,
1856 l_transaction_type,
1857 l_transaction_date
1858 FROM WSM_SPLIT_MERGE_TRANSACTIONS
1859 WHERE transaction_id = p_transaction_id;
1860
1861 /* Get Currency Information and Precision */
1862 l_stmt_num := 30;
1863 SELECT NVL(FC.minimum_accountable_unit, POWER(10,NVL(-precision,0))),
1864 NVL(FC.extended_precision,NVL(FC.precision,0))
1865 INTO l_min_acct_unit,
1866 l_ext_prec
1867 FROM fnd_currencies FC,
1868 CST_ORGANIZATION_DEFINITIONS O
1869 WHERE O.organization_id = l_organization_id
1870 AND O.currency_code = FC.currency_code;
1871
1872 /* Accounting Period */
1873 l_stmt_num := 35;
1874
1875 l_le_transaction_date := INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(
1876 l_transaction_date,
1877 l_organization_id);
1878
1879 l_stmt_num := 40;
1880
1881 SELECT acct_period_id
1882 INTO l_acct_period_id
1883 FROM org_acct_periods
1884 WHERE organization_id = l_organization_id
1885 AND l_le_transaction_date
1886 between period_start_date and schedule_close_date;
1887
1888
1889 /* Get Information from WSSJ */
1890
1891 l_stmt_num := 50;
1892
1893 SELECT wip_entity_id,
1894 operation_seq_num,
1895 intraoperation_step,
1896 available_quantity,
1897 job_start_quantity
1898 INTO l_wip_entity_id,
1899 l_operation_seq_num,
1900 l_intraoperation_step,
1901 l_available_quantity,
1902 l_job_start_quantity
1903 FROM WSM_SM_STARTING_JOBS
1904 WHERE transaction_id = p_transaction_id;
1905
1906 /* Get Information from WSRJ */
1907
1908 l_stmt_num := 60;
1909
1910 SELECT start_quantity
1911 INTO l_start_quantity
1912 FROM WSM_SM_RESULTING_JOBS
1913 WHERE transaction_id = p_transaction_id;
1914
1915
1916 /* Get the Job Charges */
1917
1918 l_stmt_num := 70;
1919
1920 CSTPSMUT.GET_JOB_VALUE ( p_api_version => 1.0,
1921 p_lot_size => l_start_quantity,
1922 p_run_mode => 1, -- CHARGE
1923 p_entity_id => l_wip_entity_id,
1924 p_intraop_step => l_intraoperation_step,
1925 p_operation_seq_num => l_operation_seq_num,
1926 p_transaction_id => p_transaction_id,
1927 p_txn_type => WSMPCNST.UPDATE_QUANTITY,
1928 p_org_id => l_organization_id,
1929 x_err_num => x_err_num,
1930 x_err_code => x_err_code,
1931 x_err_msg => x_err_msg,
1932 x_pl_mtl_cost => l_pl_mtl_cost_in,
1933 x_pl_mto_cost => l_pl_mto_cost_in,
1934 x_pl_res_cost => l_pl_res_cost_in,
1935 x_pl_ovh_cost => l_pl_ovh_cost_in,
1936 x_pl_osp_cost => l_pl_osp_cost_in,
1937 x_tl_res_cost => l_tl_res_cost_in,
1938 x_tl_ovh_cost => l_tl_ovh_cost_in,
1939 x_tl_osp_cost => l_tl_osp_cost_in );
1940
1941
1942 IF x_err_num <> 0 THEN
1943 RAISE GET_JOB_VALUE_FAILURE;
1944 END IF;
1945
1946 /* Job Relief */
1947
1948 l_stmt_num := 75;
1949
1950 CSTPSMUT.GET_JOB_VALUE ( p_api_version => 1.0,
1951 p_lot_size => l_start_quantity,
1952 p_run_mode => 2, -- SCRAP
1953 p_entity_id => l_wip_entity_id,
1954 p_intraop_step => l_intraoperation_step,
1955 p_operation_seq_num => l_operation_seq_num,
1956 p_transaction_id => p_transaction_id,
1957 p_txn_type => WSMPCNST.UPDATE_QUANTITY,
1958 p_org_id => l_organization_id,
1959 x_err_num => x_err_num,
1960 x_err_code => x_err_code,
1961 x_err_msg => x_err_msg,
1962 x_pl_mtl_cost => l_pl_mtl_cost_out,
1963 x_pl_mto_cost => l_pl_mto_cost_out,
1964 x_pl_res_cost => l_pl_res_cost_out,
1965 x_pl_ovh_cost => l_pl_ovh_cost_out,
1966 x_pl_osp_cost => l_pl_osp_cost_out,
1967 x_tl_res_cost => l_tl_res_cost_out,
1968 x_tl_ovh_cost => l_tl_ovh_cost_out,
1969 x_tl_osp_cost => l_tl_osp_cost_out );
1970
1971 IF x_err_num <> 0 THEN
1972 RAISE GET_JOB_VALUE_FAILURE;
1973 END IF;
1974
1975 l_pl_mtl_net := l_pl_mtl_cost_in - l_pl_mtl_cost_out;
1976 l_pl_mto_net := l_pl_mto_cost_in - l_pl_mto_cost_out;
1977 l_pl_res_net := l_pl_res_cost_in - l_pl_res_cost_out;
1978 l_pl_ovh_net := l_pl_ovh_cost_in - l_pl_ovh_cost_out;
1979 l_pl_osp_net := l_pl_osp_cost_in - l_pl_osp_cost_out;
1980
1981
1982 l_tl_res_net := l_tl_res_cost_in - l_tl_res_cost_out;
1983 l_tl_ovh_net := l_tl_ovh_cost_in - l_tl_ovh_cost_out;
1984 l_tl_osp_net := l_tl_osp_cost_in - l_tl_osp_cost_out;
1985
1986
1987 l_stmt_num := 80;
1988
1989 l_factor := (l_start_quantity - l_available_quantity)/l_available_quantity;
1990
1991 CSTPSMUT.INSERT_MAT_TXN_ACCT(
1992 l_transaction_date,
1993 l_min_acct_unit,
1994 l_ext_prec,
1995 l_transaction_type,
1996 p_mmt_transaction_id,
1997 l_organization_id,
1998 l_wip_entity_id,
1999 UPD_QTY_RESULT_ACT_LTYPE, -- Accounting Line Type
2000 (l_start_quantity - l_available_quantity),
2001 l_pl_mtl_net * l_factor,
2002 l_pl_mto_net * l_factor,
2003 l_pl_res_net * l_factor,
2004 l_pl_ovh_net * l_factor,
2005 l_pl_osp_net * l_factor,
2006 p_user_id,
2007 p_login_id,
2008 p_request_id,
2009 p_prog_application_id,
2010 p_program_id,
2011 l_debug_flag,
2012 x_err_num,
2013 x_err_code,
2014 x_err_msg);
2015
2016 IF x_err_num <> 0 then
2017 RAISE FAILED_INSERTING_MTA;
2018 END IF;
2019
2020 l_stmt_num := 90;
2021
2022 get_wip_txn_id(l_wip_transaction_id,
2023 x_err_num,
2024 x_err_code,
2025 x_err_msg);
2026
2027 /* Insert into MTA */
2028
2029 l_stmt_num := 100;
2030
2031 CSTPSMUT.INSERT_WIP_TXN_ACCT(
2032 l_transaction_date,
2033 l_min_acct_unit,
2034 l_ext_prec,
2035 p_transaction_id,
2036 l_transaction_type,
2037 l_wip_transaction_id,
2038 l_organization_id,
2039 l_wip_entity_id,
2040 UPD_QTY_RESULT_ACT_LTYPE, -- Accounting Line Type
2041 (l_start_quantity - l_available_quantity),
2042 0, -- This Level Material Cost
2043 0, -- This Level Material Ovhd Cost
2044 l_factor * l_tl_res_net,
2045 l_factor * l_tl_ovh_net,
2046 l_factor * l_tl_osp_net,
2047 p_user_id,
2048 p_login_id,
2049 p_request_id,
2050 p_prog_application_id,
2051 p_program_id,
2052 l_debug_flag,
2053 x_err_num,
2054 x_err_code,
2055 x_err_msg);
2056
2057 IF x_err_num <> 0 then
2058 RAISE FAILED_INSERTING_WTA;
2059 END IF;
2060
2061
2062 /* Insert Transaction into WT */
2063
2064 l_stmt_num := 110;
2065
2066 CSTPSMUT.INSERT_WIP_TXN(
2067 l_transaction_date,
2068 p_transaction_id,
2069 l_wip_transaction_id,
2070 l_acct_period_id,
2071 l_wip_entity_id,
2072 l_operation_seq_num,
2073 14, -- WIP Transaction type
2074 p_user_id,
2075 p_login_id,
2076 p_request_id,
2077 p_prog_application_id,
2078 p_program_id,
2079 l_debug_flag,
2080 x_err_num,
2081 x_err_code,
2082 x_err_msg,
2083 p_mmt_transaction_id); -- Added for Bug#4307365
2084
2085 IF x_err_num <> 0 then
2086 RAISE FAILED_INSERTING_WT;
2087 END IF;
2088
2089 /* Insert credit into Bonus Account */
2090
2091 l_stmt_num := 120;
2092
2093 CSTPSMUT.BONUS_MAT_TXN_ACCT(
2094 l_transaction_date,
2095 l_ext_prec,
2096 l_min_acct_unit,
2097 l_transaction_type,
2098 p_transaction_id,
2099 p_mmt_transaction_id,
2100 l_organization_id,
2101 l_wip_entity_id,
2102 UPD_QTY_START_ACT_LTYPE,
2103 -(ROUND( l_factor * l_pl_mtl_net /
2104 l_min_acct_unit) * l_min_acct_unit +
2105 ROUND( l_factor * l_pl_mto_net /
2106 l_min_acct_unit) * l_min_acct_unit +
2107 ROUND( l_factor * l_pl_res_net /
2108 l_min_acct_unit) * l_min_acct_unit +
2109 ROUND( l_factor * l_pl_osp_net /
2110 l_min_acct_unit) * l_min_acct_unit +
2111 ROUND( l_factor * l_pl_ovh_net /
2112 l_min_acct_unit) * l_min_acct_unit),
2113 p_user_id,
2114 p_login_id,
2115 p_request_id,
2116 p_prog_application_id,
2117 p_program_id,
2118 l_debug_flag,
2119 x_err_num,
2120 x_err_code,
2121 x_err_msg);
2122
2123 /* Insert credit into Bonus Account */
2124
2125 l_stmt_num := 130;
2126
2127 CSTPSMUT.BONUS_WIP_TXN_ACCT(
2128 l_transaction_date,
2129 l_ext_prec,
2130 l_min_acct_unit,
2131 p_transaction_id,
2132 l_transaction_type,
2133 l_wip_transaction_id,
2134 l_organization_id,
2135 l_wip_entity_id,
2136 UPD_QTY_START_ACT_LTYPE,
2137 -(ROUND( l_factor * l_tl_res_net /
2138 l_min_acct_unit) * l_min_acct_unit +
2139 ROUND( l_factor * l_tl_osp_net /
2140 l_min_acct_unit) * l_min_acct_unit +
2141 ROUND( l_factor * l_tl_ovh_net /
2142 l_min_acct_unit) * l_min_acct_unit),
2143 p_user_id,
2144 p_login_id,
2145 p_request_id,
2146 p_prog_application_id,
2147 p_program_id,
2148 l_debug_flag,
2149 x_err_num,
2150 x_err_code,
2151 x_err_msg);
2152
2153 /* Make sure the Debit/Credit for Representative Lot and Resulting Lots
2154 are balanced */
2155
2156 l_stmt_num := 140;
2157 CSTPSMUT.BALANCE_ACCOUNTING(p_mmt_transaction_id,
2158 l_wip_transaction_id,
2159 l_transaction_type,
2160 x_err_msg,
2161 x_err_code,
2162 x_err_num);
2163 IF x_err_num <> 0 then
2164 RAISE FAILED_BALANCING_ACCT;
2165 END IF;
2166
2167 /* Update WPB of Representative Lot */
2168
2169 l_stmt_num := 150;
2170 CSTPSMUT.RESULT_LOT(
2171 p_mmt_transaction_id,
2172 l_wip_transaction_id,
2173 l_wip_entity_id,
2174 l_acct_period_id,
2175 p_user_id,
2176 p_login_id,
2177 p_request_id,
2178 p_prog_application_id,
2179 p_program_id,
2180 l_debug_flag,
2181 x_err_num,
2182 x_err_code,
2183 x_err_msg);
2184 IF x_err_num <> 0 then
2185 RAISE FAILED_INSERTING_START_LOT;
2186 END IF;
2187
2188 l_stmt_num := 155;
2189
2190 SELECT count(*)
2191 INTO l_wta_exists
2192 FROM WIP_TRANSACTION_ACCOUNTS
2193 WHERE transaction_id = l_wip_transaction_id
2194 and rownum=1;
2195
2196
2197
2198 IF l_wta_exists > 0 THEN
2199 /* SLA Event Seeding */
2200 l_trx_info.TRANSACTION_ID := l_wip_transaction_id;
2201 l_trx_info.INV_ORGANIZATION_ID := l_organization_id;
2202 l_trx_info.WIP_RESOURCE_ID := -1;
2203 l_trx_info.WIP_BASIS_TYPE_ID := -1;
2204 l_trx_info.TXN_TYPE_ID := 14;
2205 l_trx_info.TRANSACTION_DATE := l_transaction_date;
2206
2207 l_stmt_num := 160;
2208
2209 CST_XLA_PVT.Create_WIPXLAEvent(
2210 p_api_version => 1.0,
2211 p_init_msg_list => FND_API.G_FALSE,
2212 p_commit => FND_API.G_FALSE,
2213 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2214 x_return_status => l_return_status,
2215 x_msg_count => l_msg_count,
2216 x_msg_data => l_msg_data,
2217 p_trx_info => l_trx_info);
2218
2219 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2220 RAISE FND_API.g_exc_unexpected_error;
2221 END IF;
2222
2223 END IF;
2224 EXCEPTION
2225
2226 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2227 x_err_num := -1;
2228 x_err_code := 'Inconsistent API Version';--FND_API.G_RET_SYS_ERROR;
2229 x_err_msg := 'Inconsistent API Version: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2230 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
2231 WHEN GET_JOB_VALUE_FAILURE THEN
2232 x_err_num := -1;
2233 x_err_code := 'Error getting Job Charges/Scrap';
2234 x_err_msg := 'Error getting Job Charges/Scrap: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2235 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
2236 WHEN FAILED_INSERTING_START_LOT THEN
2237 x_err_num := -1;
2238 x_err_code := 'Error Inserting WPB Information for Starting Lot';
2239 x_err_msg := 'Error Inserting WPB Information for Starting Lot: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2240 WHEN FAILED_BALANCING_ACCT THEN
2241 x_err_num := -1;
2242 x_err_code := 'Error Balancing Accounts';
2243 x_err_msg := 'Error Balancing Accounts: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2244 WHEN FAILED_INSERTING_WT THEN
2245 x_err_num := -1;
2246 x_err_code := 'Error inserting into Wip Transactions';
2247 x_err_msg := 'Error inserting into Wip Transactions: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2248 WHEN FAILED_INSERTING_WTA THEN
2249 x_err_num := -1;
2250 x_err_code := 'Error inserting into Wip Transaction Accounts';
2251 x_err_msg := 'Error inserting into Wip Transaction Accounts: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2252 WHEN FAILED_INSERTING_MTA THEN
2253 x_err_num := -1;
2254 x_err_code := 'Error inserting into MTL Transaction Accounts';
2255 x_err_msg := 'Error inserting into MTL Transaction Accounts: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2256 WHEN FAILED_INSERTING_BONUS_MTA THEN
2257 x_err_num := -1;
2258 x_err_code := 'Error inserting into MTL Bonus Accounts';
2259 x_err_msg := 'Error inserting into MTL Bonus Accounts: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2260 WHEN FAILED_INSERTING_BONUS_WTA THEN
2261 x_err_num := -1;
2262 x_err_code := 'Error inserting into WIP Bonus Accounts';
2263 x_err_msg := 'Error inserting into WIP Bonus Accounts: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2264
2265 WHEN FAILED_INSERTING_RESULT_LOT THEN
2266 x_err_num := -1;
2267 x_err_code := 'Error inserting into WPB for Resulting Lot';
2268 x_err_msg := 'Error inserting into WPB for Resulting Lot: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2269
2270 END COST_UPDATE_QTY_TXN;
2271
2272
2273
2274 ----------------------------------------------------------------------------
2275 -- PROCEDURE --
2276 -- COST_BONUS_TXN --
2277 -- --
2278 -- DESCRIPTION --
2279 -- This procedure costs a lot bonus transaction. It inserts entries in
2280 -- MTA, WT and WTA. It also updates the WPB for all the involved jobs.
2281 -- --
2282 -- PURPOSE: --
2283 -- OSFM Lot Transactions Costing for Oracle Applications Rel 11i.8
2284 -- --
2285 -- PARAMETERS: --
2286 -- p_api_version API version
2287 -- p_transaction_id Transaction ID from WSMT
2288 -- p_mmt_transaction_id Transaction ID form MMT
2289 -- p_request_id Request ID of calling worker
2290 -- p_transaction_date Transaction Date
2291 -- p_prog_application_id
2292 -- p_program_id
2293 -- p_login_id
2294 -- p_user_id Concurrent WHO Parameters
2295 -- x_err_num Error Number
2296 -- x_err_code Error Code --
2297 -- x_err_msg Error Message --
2298 -- HISTORY: --
2299 -- August-2002 Vinit Creation --
2300 ----------------------------------------------------------------------------
2301 PROCEDURE COST_BONUS_TXN (p_api_version IN NUMBER,
2302 p_transaction_id IN NUMBER,
2303 p_mmt_transaction_id IN NUMBER,
2304 p_transaction_date IN DATE,
2305 p_prog_application_id IN NUMBER,
2306 p_program_id IN NUMBER,
2307 p_request_id IN NUMBER,
2308 p_login_id IN NUMBER,
2309 p_user_id IN NUMBER,
2310 x_err_num IN OUT NOCOPY NUMBER,
2311 x_err_code IN OUT NOCOPY VARCHAR2,
2312 x_err_msg IN OUT NOCOPY VARCHAR2) IS
2313
2314 /* Parameters */
2315 l_transaction_type NUMBER;
2316 l_organization_id NUMBER;
2317 l_transaction_date DATE;
2318 l_min_acct_unit NUMBER := 0;
2319 l_ext_prec NUMBER(2) := 0;
2320 l_wip_transaction_id NUMBER;
2321 l_acct_period_id NUMBER;
2322
2323 /* Local Variables */
2324 l_stmt_num NUMBER := 0;
2325 ins_woo BOOLEAN := TRUE;
2326 l_le_transaction_date DATE;
2327
2328 /* API */
2329 l_api_name CONSTANT VARCHAR2(240) := 'COST_BONUS_TXN';
2330 l_api_version CONSTANT NUMBER := 1.0;
2331
2332 /* Representative Lot Information */
2333 l_wip_entity_id NUMBER;
2334 l_start_quantity NUMBER;
2335 l_operation_seq_num NUMBER;
2336 l_intraoperation_step NUMBER := 1;
2337 l_min_op_seq_num NUMBER;
2338
2339
2340 /* Intraoperation Step Not Needed */
2341
2342 /* Job Charges */
2343 l_pl_mtl_cost_in NUMBER := 0;
2344 l_pl_mto_cost_in NUMBER := 0;
2345 l_pl_res_cost_in NUMBER := 0;
2346 l_pl_ovh_cost_in NUMBER := 0;
2347 l_pl_osp_cost_in NUMBER := 0;
2348 l_tl_res_cost_in NUMBER := 0;
2349 l_tl_ovh_cost_in NUMBER := 0;
2350 l_tl_osp_cost_in NUMBER := 0;
2351
2352
2353 /* Exceptions */
2354
2355 GET_JOB_VALUE_FAILURE EXCEPTION;
2356 FAILED_INSERTING_START_LOT EXCEPTION;
2357 FAILED_BALANCING_ACCT EXCEPTION;
2358 FAILED_INSERTING_WT EXCEPTION;
2359 FAILED_INSERTING_WTA EXCEPTION;
2360 FAILED_INSERTING_MTA EXCEPTION;
2361 FAILED_INSERTING_RESULT_LOT EXCEPTION;
2362 FAILED_INSERTING_BONUS_WTA EXCEPTION;
2363 FAILED_INSERTING_BONUS_MTA EXCEPTION;
2364 INSERT_WOO_ERROR EXCEPTION;
2365
2366 /* Accounting Line Types */
2367 BONUS_RESULT_ACT_LTYPE NUMBER := 26;
2368 BONUS_START_ACT_LTYPE NUMBER := 25;
2369
2370 /* SLA Event Seeding */
2371 l_wta_exists NUMBER;
2372 l_return_status VARCHAR2(1);
2373 l_msg_count NUMBER;
2374 l_msg_data VARCHAR2(2000);
2375 l_trx_info CST_XLA_PVT.t_xla_wip_trx_info;
2376
2377 BEGIN
2378 /* Check API Compatibility */
2379 l_stmt_num := 10;
2380 IF (l_debug_flag = 'Y') THEN
2381 fnd_file.put_line(fnd_file.log, 'CSTPSMUT.COST_BONUS_TXN <<<');
2382 END IF;
2383
2384 IF NOT FND_API.COMPATIBLE_API_CALL (
2385 l_api_version,
2386 p_api_version,
2387 l_api_name,
2388 G_PKG_NAME ) THEN
2389 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2390 END IF;
2391
2392
2393 /* Get Transaction Information from WSMT */
2394
2395 l_stmt_num := 20;
2396 SELECT organization_id,
2397 transaction_type_id,
2398 transaction_date
2399 INTO l_organization_id,
2400 l_transaction_type,
2401 l_transaction_date
2402 FROM WSM_SPLIT_MERGE_TRANSACTIONS
2403 WHERE transaction_id = p_transaction_id;
2404
2405
2406
2407 /* Get Currency Information and Precision */
2408 l_stmt_num := 30;
2409 SELECT NVL(FC.minimum_accountable_unit, POWER(10,NVL(-precision,0))),
2410 NVL(FC.extended_precision,NVL(FC.precision,0))
2411 INTO l_min_acct_unit,
2412 l_ext_prec
2413 FROM fnd_currencies FC,
2414 CST_ORGANIZATION_DEFINITIONS O
2415 WHERE O.organization_id = l_organization_id
2416 AND O.currency_code = FC.currency_code;
2417
2418 /* Accounting Period */
2419 l_stmt_num := 35;
2420
2421 l_le_transaction_date := INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(
2422 l_transaction_date,
2423 l_organization_id);
2424
2425 l_stmt_num := 40;
2426
2427 SELECT acct_period_id
2428 INTO l_acct_period_id
2429 FROM org_acct_periods
2430 WHERE organization_id = l_organization_id
2431 AND l_le_transaction_date
2432 between period_start_date and schedule_close_date;
2433
2434
2435 /* Get Information from WSRJ */
2436 /* Note that the starting_operation_seq_num in WSRJ
2437 is NOT the operation_seq_num where the Bonus
2438 transaction took place */
2439
2440
2441 l_stmt_num := 50;
2442
2443 SELECT wip_entity_id,
2444 job_operation_seq_num,
2445 nvl(starting_intraoperation_step, WIP_CONSTANTS.QUEUE),
2446 start_quantity
2447 INTO l_wip_entity_id,
2448 l_operation_seq_num,
2449 l_intraoperation_step,
2450 l_start_quantity
2451 FROM WSM_SM_RESULTING_JOBS
2452 WHERE transaction_id = p_transaction_id;
2453
2454 /* Scenario applicable for jobs that are upgraded
2455 from 11i.8 and below. JOB_OPERATION_SEQ_NUM is not
2456 stamped. Hence, we use BOM_OPERATION_SEQUENCES and
2457 starting_operation_seq_num on the transaction in WSRJ to get
2458 this information */
2459
2460 IF l_operation_seq_num IS NULL THEN
2461 l_stmt_num := 52;
2462 SELECT wo.operation_seq_num
2463 INTO l_operation_seq_num
2464 FROM WIP_OPERATIONS WO,
2465 WSM_SM_RESULTING_JOBS WSRJ,
2466 BOM_OPERATION_SEQUENCES BOS
2467 WHERE WSRJ.transaction_id = p_transaction_id
2468 AND nvl(wsrj.starting_intraoperation_step, 1) = 1
2469 AND wsrj.common_routing_sequence_id = bos.routing_sequence_id
2470 AND wsrj.starting_operation_seq_num = bos.operation_seq_num
2471 AND bos.operation_sequence_id = wo.operation_sequence_id
2472 AND bos.EFFECTIVITY_DATE <= p_transaction_date
2473 AND NVL( bos.DISABLE_DATE, p_transaction_date + 1) > p_transaction_date
2474 AND wo.wip_entity_id = wsrj.wip_entity_id
2475 AND wo.organization_id = l_organization_id;
2476 END IF;
2477
2478 IF ( l_debug_flag = 'Y' ) THEN
2479 fnd_file.put_line(fnd_file.log, 'WIP ENTITY : '||to_char(l_wip_entity_id));
2480 fnd_file.put_line(fnd_file.log, 'OPERATION_SEQ_NUM: '||to_char(l_operation_seq_num));
2481 END IF;
2482
2483 /* Return success if Bonus occurs at the Queue Intraoperation step
2484 of the first operation of the job.
2485 Calling routine sets the status to costed. */
2486
2487 l_stmt_num := 55;
2488
2489 SELECT min(operation_seq_num)
2490 INTO l_min_op_seq_num
2491 FROM wip_operations
2492 WHERE wip_entity_id = l_wip_entity_id
2493 AND organization_id = l_organization_id;
2494
2495
2496 IF (l_operation_seq_num = l_min_op_seq_num
2497 AND l_intraoperation_step = 1) THEN
2498 RETURN;
2499 END IF;
2500
2501 /* Get the Job Charges */
2502
2503 l_stmt_num := 60;
2504
2505 CSTPSMUT.GET_JOB_VALUE ( p_api_version => 1.0,
2506 p_lot_size => l_start_quantity,
2507 p_run_mode => 1, -- CHARGE
2508 p_entity_id => l_wip_entity_id,
2509 p_intraop_step => l_intraoperation_step,
2510 p_operation_seq_num => l_operation_seq_num,
2511 p_transaction_id => p_transaction_id,
2512 p_txn_type => WSMPCNST.BONUS,
2513 p_org_id => l_organization_id,
2514 x_err_num => x_err_num,
2515 x_err_code => x_err_code,
2516 x_err_msg => x_err_msg,
2517 x_pl_mtl_cost => l_pl_mtl_cost_in,
2518 x_pl_mto_cost => l_pl_mto_cost_in,
2519 x_pl_res_cost => l_pl_res_cost_in,
2520 x_pl_ovh_cost => l_pl_ovh_cost_in,
2521 x_pl_osp_cost => l_pl_osp_cost_in,
2522 x_tl_res_cost => l_tl_res_cost_in,
2523 x_tl_ovh_cost => l_tl_ovh_cost_in,
2524 x_tl_osp_cost => l_tl_osp_cost_in );
2525
2526
2527 IF x_err_num <> 0 THEN
2528 RAISE GET_JOB_VALUE_FAILURE;
2529 END IF;
2530
2531 l_stmt_num := 70;
2532
2533 CSTPSMUT.INSERT_MAT_TXN_ACCT(
2534 l_transaction_date,
2535 l_min_acct_unit,
2536 l_ext_prec,
2537 l_transaction_type,
2538 p_mmt_transaction_id,
2539 l_organization_id,
2540 l_wip_entity_id,
2541 BONUS_RESULT_ACT_LTYPE, -- Accounting Line Type for Bonus
2542 l_start_quantity,
2543 l_pl_mtl_cost_in * l_start_quantity,
2544 l_pl_mto_cost_in * l_start_quantity,
2545 l_pl_res_cost_in * l_start_quantity,
2546 l_pl_ovh_cost_in * l_start_quantity,
2547 l_pl_osp_cost_in * l_start_quantity,
2548 p_user_id,
2549 p_login_id,
2550 p_request_id,
2551 p_prog_application_id,
2552 p_program_id,
2553 l_debug_flag,
2554 x_err_num,
2555 x_err_code,
2556 x_err_msg);
2557
2558 IF x_err_num <> 0 then
2559 RAISE FAILED_INSERTING_MTA;
2560 END IF;
2561
2562 l_stmt_num := 80;
2563
2564 get_wip_txn_id(l_wip_transaction_id,
2565 x_err_num,
2566 x_err_code,
2567 x_err_msg);
2568
2569 -- dbms_output.put_line('Wip Transaction ID: '||to_char(l_wip_transaction_id));
2570
2571 /* Insert into MTA */
2572
2573 l_stmt_num := 90;
2574
2575 CSTPSMUT.INSERT_WIP_TXN_ACCT(
2576 l_transaction_date,
2577 l_min_acct_unit,
2578 l_ext_prec,
2579 p_transaction_id,
2580 l_transaction_type,
2581 l_wip_transaction_id,
2582 l_organization_id,
2583 l_wip_entity_id,
2584 BONUS_RESULT_ACT_LTYPE, -- Accounting Line Type for Bonus
2585 l_start_quantity,
2586 0, -- This Level Material Cost
2587 0, -- This Level Material Ovhd Cost
2588 (l_start_quantity * l_tl_res_cost_in),
2589 (l_start_quantity * l_tl_ovh_cost_in),
2590 (l_start_quantity * l_tl_osp_cost_in),
2591 p_user_id,
2592 p_login_id,
2593 p_request_id,
2594 p_prog_application_id,
2595 p_program_id,
2596 l_debug_flag,
2597 x_err_num,
2598 x_err_code,
2599 x_err_msg);
2600
2601 IF x_err_num <> 0 then
2602 RAISE FAILED_INSERTING_WTA;
2603 END IF;
2604
2605
2606 /* Insert Transaction into WT */
2607
2608 l_stmt_num := 100;
2609 CSTPSMUT.INSERT_WIP_TXN(
2610 l_transaction_date,
2611 p_transaction_id,
2612 l_wip_transaction_id,
2613 l_acct_period_id,
2614 l_wip_entity_id,
2615 l_operation_seq_num,
2616 13, -- WIP Transaction type
2617 p_user_id,
2618 p_login_id,
2619 p_request_id,
2620 p_prog_application_id,
2621 p_program_id,
2622 l_debug_flag,
2623 x_err_num,
2624 x_err_code,
2625 x_err_msg,
2626 p_mmt_transaction_id); -- Added for Bug#4307365
2627
2628 IF x_err_num <> 0 then
2629 RAISE FAILED_INSERTING_WT;
2630 END IF;
2631
2632 /* Insert Credit Information */
2633
2634 l_stmt_num := 110;
2635
2636 CSTPSMUT.BONUS_MAT_TXN_ACCT(
2637 l_transaction_date,
2638 l_ext_prec,
2639 l_min_acct_unit,
2640 l_transaction_type,
2641 p_transaction_id,
2642 p_mmt_transaction_id,
2643 l_organization_id,
2644 l_wip_entity_id,
2645 BONUS_START_ACT_LTYPE,
2646 -((ROUND(l_pl_mtl_cost_in * l_start_quantity /
2647 l_min_acct_unit) * l_min_acct_unit) +
2648 (ROUND(l_pl_mto_cost_in * l_start_quantity /
2649 l_min_acct_unit) * l_min_acct_unit) +
2650 (ROUND(l_pl_res_cost_in * l_start_quantity /
2651 l_min_acct_unit) * l_min_acct_unit) +
2652 (ROUND(l_pl_osp_cost_in * l_start_quantity /
2653 l_min_acct_unit) * l_min_acct_unit) +
2654 (ROUND(l_pl_ovh_cost_in * l_start_quantity /
2655 l_min_acct_unit) * l_min_acct_unit)),
2656 p_user_id,
2657 p_login_id,
2658 p_request_id,
2659 p_prog_application_id,
2660 p_program_id,
2661 l_debug_flag,
2662 x_err_num,
2663 x_err_code,
2664 x_err_msg);
2665 IF x_err_num <> 0 then
2666 RAISE FAILED_INSERTING_BONUS_MTA;
2667 END IF;
2668
2669
2670 l_stmt_num := 120;
2671
2672 CSTPSMUT.BONUS_WIP_TXN_ACCT(
2673 l_transaction_date,
2674 l_ext_prec,
2675 l_min_acct_unit,
2676 p_transaction_id,
2677 l_transaction_type,
2678 l_wip_transaction_id,
2679 l_organization_id,
2680 l_wip_entity_id,
2681 BONUS_START_ACT_LTYPE,
2682 -((ROUND(l_tl_res_cost_in * l_start_quantity /
2683 l_min_acct_unit) * l_min_acct_unit) +
2684 (ROUND(l_tl_osp_cost_in * l_start_quantity /
2685 l_min_acct_unit) * l_min_acct_unit) +
2686 (ROUND(l_tl_ovh_cost_in * l_start_quantity /
2687 l_min_acct_unit) * l_min_acct_unit)),
2688 p_user_id,
2689 p_login_id,
2690 p_request_id,
2691 p_prog_application_id,
2692 p_program_id,
2693 l_debug_flag,
2694 x_err_num,
2695 x_err_code,
2696 x_err_msg);
2697 IF x_err_num <> 0 then
2698 RAISE FAILED_INSERTING_BONUS_WTA;
2699 END IF;
2700
2701 /* Make sure the Debit/Credit for Representative Lot and Resulting Lots
2702 are balanced */
2703
2704 l_stmt_num := 130;
2705 CSTPSMUT.BALANCE_ACCOUNTING(p_mmt_transaction_id,
2706 l_wip_transaction_id,
2707 l_transaction_type,
2708 x_err_msg,
2709 x_err_code,
2710 x_err_num);
2711 IF x_err_num <> 0 then
2712 RAISE FAILED_BALANCING_ACCT;
2713 END IF;
2714
2715 /* Update WPB of Representative Lot */
2716
2717 l_stmt_num := 140;
2718 CSTPSMUT.RESULT_LOT(
2719 p_mmt_transaction_id,
2720 l_wip_transaction_id,
2721 l_wip_entity_id,
2722 l_acct_period_id,
2723 p_user_id,
2724 p_login_id,
2725 p_request_id,
2726 p_prog_application_id,
2727 p_program_id,
2728 l_debug_flag,
2729 x_err_num,
2730 x_err_code,
2731 x_err_msg);
2732 IF x_err_num <> 0 then
2733 RAISE FAILED_INSERTING_START_LOT;
2734 END IF;
2735
2736 l_stmt_num := 150;
2737
2738 ins_woo := INSERT_WOO (
2739 l_wip_entity_id,
2740 l_organization_id,
2741 l_operation_seq_num,
2742 p_user_id,
2743 p_login_id,
2744 p_request_id,
2745 p_prog_application_id,
2746 p_program_id );
2747 IF ins_woo = FALSE THEN
2748 RAISE INSERT_WOO_ERROR;
2749 END IF;
2750
2751 l_stmt_num := 155;
2752
2753 SELECT count(*)
2754 INTO l_wta_exists
2755 FROM WIP_TRANSACTION_ACCOUNTS
2756 WHERE transaction_id = l_wip_transaction_id
2757 and rownum=1;
2758
2759 IF l_wta_exists > 0 THEN
2760
2761 /* SLA Event Seeding */
2762 l_trx_info.TRANSACTION_ID := l_wip_transaction_id;
2763 l_trx_info.INV_ORGANIZATION_ID := l_organization_id;
2764 l_trx_info.WIP_RESOURCE_ID := -1;
2765 l_trx_info.WIP_BASIS_TYPE_ID := -1;
2766 l_trx_info.TXN_TYPE_ID := 13;
2767 l_trx_info.TRANSACTION_DATE := l_transaction_date;
2768
2769 l_stmt_num := 160;
2770
2771 CST_XLA_PVT.Create_WIPXLAEvent(
2772 p_api_version => 1.0,
2773 p_init_msg_list => FND_API.G_FALSE,
2774 p_commit => FND_API.G_FALSE,
2775 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2776 x_return_status => l_return_status,
2777 x_msg_count => l_msg_count,
2778 x_msg_data => l_msg_data,
2779 p_trx_info => l_trx_info);
2780
2781 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2782 RAISE FND_API.g_exc_unexpected_error;
2783 END IF;
2784 END IF;
2785
2786 IF (l_debug_flag = 'Y') THEN
2787 fnd_file.put_line(fnd_file.log, 'CSTPSMUT.COST_BONUS_TXN >>>');
2788 END IF;
2789
2790 EXCEPTION
2791
2792 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2793 x_err_num := -1;
2794 x_err_code := 'Inconsistent API Version';--FND_API.G_RET_SYS_ERROR;
2795 x_err_msg := 'Inconsistent API Version: CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2796 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
2797 WHEN GET_JOB_VALUE_FAILURE THEN
2798 x_err_num := -1;
2799 x_err_code := 'Error getting Job Charges/Scrap';
2800 x_err_msg := 'Error getting Job Charges/Scrap: CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2801 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
2802 WHEN FAILED_INSERTING_START_LOT THEN
2803 x_err_num := -1;
2804 x_err_code := 'Error Inserting WPB Information for Starting Lot';
2805 x_err_msg := 'Error Inserting WPB Information for Starting Lot: CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2806 WHEN FAILED_BALANCING_ACCT THEN
2807 x_err_num := -1;
2808 x_err_code := 'Error Balancing Accounts';
2809 x_err_msg := 'Error Balancing Accounts: CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2810 WHEN FAILED_INSERTING_WT THEN
2811 x_err_num := -1;
2812 x_err_code := 'Error inserting into Wip Transactions';
2813 x_err_msg := 'Error inserting into Wip Transactions: CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2814 WHEN FAILED_INSERTING_WTA THEN
2815 x_err_num := -1;
2816 x_err_code := 'Error inserting into Wip Transaction Accounts';
2817 x_err_msg := 'Error inserting into Wip Transaction Accounts: CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2818 WHEN FAILED_INSERTING_MTA THEN
2819 x_err_num := -1;
2820 x_err_code := 'Error inserting into MTL Transaction Accounts';
2821 x_err_msg := 'Error inserting into MTL Transaction Accounts: CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2822 WHEN FAILED_INSERTING_BONUS_MTA THEN
2823 x_err_num := -1;
2824 x_err_code := 'Error inserting into MTL Bonus Accounts';
2825 x_err_msg := 'Error inserting into MTL Bonus Accounts: CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2826 WHEN FAILED_INSERTING_BONUS_WTA THEN
2827 x_err_num := -1;
2828 x_err_code := 'Error inserting into WIP Bonus Accounts';
2829 x_err_msg := 'Error inserting into WIP Bonus Accounts: CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2830 WHEN FAILED_INSERTING_RESULT_LOT THEN
2831 x_err_num := -1;
2832 x_err_code := 'Error inserting into WPB for Resulting Lot';
2833 x_err_msg := 'Error inserting into WPB for Resulting Lot: CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2834
2835 WHEN INSERT_WOO_ERROR THEN
2836 x_err_num := -1;
2837 x_err_code := 'Error inserting into WOO';
2838 x_err_msg := 'Error inserting into WOO: CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2839
2840 WHEN OTHERS THEN
2841 x_err_code := 'Unexpected Error';
2842 x_err_msg := 'CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
2843
2844 END COST_BONUS_TXN;
2845
2846
2847 PROCEDURE GET_WIP_TXN_ID( x_wip_txn_id OUT NOCOPY NUMBER,
2848 x_err_num IN OUT NOCOPY NUMBER,
2849 x_err_code IN OUT NOCOPY VARCHAR2,
2850 x_err_msg IN OUT NOCOPY VARCHAR2 ) IS
2851 l_stmt_num number;
2852
2853 BEGIN
2854 l_stmt_num := 10;
2855 x_err_num := 0;
2856
2857 SELECT wip_transactions_s.nextval
2858 INTO x_wip_txn_id
2859 FROM dual;
2860
2861 EXCEPTION
2862 WHEN others THEN
2863 ROLLBACK;
2864 x_err_num := sqlcode;
2865 x_err_code := 'Failed Getting Wip Transaction ID';
2866 x_err_msg := 'Failed Getting Wip Transaction ID: CSTPSMUT.GET_WIP_TXN_ID: ' || to_char(l_stmt_num) || '): ' || x_err_msg ;
2867
2868 END GET_WIP_TXN_ID;
2869
2870
2871 ----------------------------------------------------------------------------
2872 -- PROCEDURE --
2873 -- GET_JOB_VALUE
2874 -- --
2875 -- DESCRIPTION --
2876 -- This procedure returns the total charges or relief from a job
2877 -- depending on the run_mode it is called with.
2878 -- It should be noted that when this procedure is called while
2879 -- costing a merge/update quantity transaction, lot based
2880 -- resources are excluded from job values both in computing
2881 -- amount charged and relieved from the resource. This is necessary
2882 -- as lot based resources/overheads are not scaled. The amount
2883 -- applied in resulting lots is independent of lot_size.
2884
2885 -- This procedure called with transaction_type of 1 (SPLIT)
2886 -- and operation_seq_num as the final op_seq_num of job would
2887 -- give the total charges/relief (WPB Values) for that job.
2888
2889 -- Procedure replaces GET_CHARGE_VAL and GET_SCRAP_VAL from prior
2890 -- versions
2891 -- --
2892 -- PURPOSE: --
2893 -- OSFM Lot Transactions Costing for Oracle Applications Rel 11i.8
2894
2895 -- PARAMETERS: --
2896 -- p_api_version API version
2897 -- p_lot_size (Only Used for Bonus Txns)
2898 -- Resulting Bonus Lot Size
2899 -- p_run_mode Charge(1)/Scrap(2)
2900 -- p_entity_id Wip Entity ID of Job
2901 -- p_intraop_step Intraoperation Step
2902 -- p_operation_seq_num Operation Sequence Number
2903 -- p_txn_type Transaction Type being processed
2904 -- p_transaction_id Transaction ID (WSMT)
2905 -- p_org_id Organization ID
2906 -- p_err_num Error Number
2907 -- p_err_code Error Code
2908 -- p_err_msg Error Message
2909 -- x_pl_mtl_cost
2910 -- x_pl_mto_cost
2911 -- x_pl_res_cost
2912 -- x_pl_ovh_cost Various Elemental Costs (Levelwise)
2913 -- x_pl_osp_cost
2914 -- x_tl_res_cost
2915 -- x_tl_ovh_cost
2916 -- x_tl_osp_cost
2917
2918 -- HISTORY: --
2919 -- August-2002 Vinit Creation --
2920 ----------------------------------------------------------------------------
2921
2922 PROCEDURE GET_JOB_VALUE
2923 (p_api_version in number,
2924 p_lot_size in number,
2925 p_run_mode in number,
2926 p_entity_id in number,
2927 p_intraop_step in number,
2928 p_operation_seq_num in number,
2929 p_transaction_id in number,
2930 p_txn_type in number,
2931 p_org_id in number,
2932 x_err_num IN OUT NOCOPY number,
2933 x_err_code IN OUT NOCOPY varchar2,
2934 x_err_msg IN OUT NOCOPY varchar2,
2935 x_pl_mtl_cost IN OUT NOCOPY number,
2936 x_pl_mto_cost IN OUT NOCOPY number,
2937 x_pl_res_cost IN OUT NOCOPY number,
2938 x_pl_ovh_cost IN OUT NOCOPY number,
2939 x_pl_osp_cost IN OUT NOCOPY number,
2940 x_tl_res_cost IN OUT NOCOPY number,
2941 x_tl_ovh_cost IN OUT NOCOPY number,
2942 x_tl_osp_cost IN OUT NOCOPY number)
2943 IS
2944 l_stmt_num number := 0;
2945 l_tl_ovh_dept_cost number := 0;
2946 l_operation_seq_num number := 0;
2947 l_first_op_seq_num number := 0;
2948 l_prev_op_seq_num number := 0;
2949
2950 /* Transaction Information */
2951 l_transaction_date DATE;
2952 l_include_comp_yield NUMBER;
2953
2954 BEGIN
2955
2956 x_err_num := 0;
2957
2958 x_tl_res_cost:= 0;
2959 x_tl_ovh_cost:= 0;
2960 x_tl_osp_cost:= 0;
2961 x_pl_mtl_cost:= 0;
2962 x_pl_mto_cost:= 0;
2963 x_pl_res_cost:= 0;
2964 x_pl_ovh_cost:= 0;
2965 x_pl_osp_cost:= 0;
2966
2967 l_stmt_num := 10;
2968
2969 SELECT operation_seq_num
2970 INTO l_first_op_seq_num
2971 FROM wip_operations
2972 WHERE wip_entity_id = p_entity_id
2973 AND previous_operation_seq_num is null;
2974
2975 l_operation_seq_num := p_operation_seq_num;
2976
2977 /* Get the value of Include Component yield flag,
2978 which will determine whether to include or not
2979 component yield factor in quantity per assembly */
2980 l_stmt_num := 12;
2981 SELECT NVL(include_component_yield, 1)
2982 INTO l_include_comp_yield
2983 FROM wip_parameters
2984 WHERE organization_id = p_org_id;
2985
2986 /* Get the transaction_date. This will be use to
2987 restrict the operations processed in WO. Only operations
2988 less than the disable date would be processed
2989 */
2990 l_stmt_num := 15;
2991
2992 SELECT transaction_date
2993 INTO l_transaction_date
2994 FROM WSM_SPLIT_MERGE_TRANSACTIONS
2995 WHERE transaction_id = p_transaction_id;
2996
2997 l_stmt_num := 18;
2998
2999 /* For Bonus transactions, no manual charges at Queue are possible. Hence
3000 calculate charges upto previous op seq num */
3001 if(p_txn_type = 4) then
3002 SELECT MAX( OPERATION_SEQ_NUM )
3003 INTO l_operation_seq_num
3004 FROM wip_operations
3005 WHERE wip_entity_id = p_entity_id
3006 AND operation_seq_num < p_operation_seq_num
3007 AND organization_id = p_org_id;
3008 end if;
3009
3010 if (l_operation_seq_num is null) then
3011 l_operation_seq_num := l_first_op_seq_num;
3012 end if;
3013
3014 /* If RUN_MODE = CHARGE (1)
3015 IF TXN_TYPE = BONUS
3016 Use WOR.quantity_per_assembly*CIC.Item_Cost (by CE)
3017 ELSE
3018 Use WOR.quantity_issued*CIC.Item_Cost (by CE)
3019 ELSE IF RUN_MODE = SCRAP
3020 IF TXN_TYPE <> BONUS (Bonus has no scrap)
3021 Use WOR.quantity_relieved etc.
3022 */
3023
3024 /* OSFM Makes sure that WRO.costed_quantity_issued and WRO.costed_quantity_releived
3025 are initialized to zero. Remove NVL's after UT */
3026
3027 l_stmt_num := 20;
3028
3029 SELECT
3030 nvl(SUM(NVL(DECODE(p_run_mode, 1,
3031 DECODE(p_txn_type, 4, /* LBM project Changes */
3032 (DECODE(NVL(WRO.basis_type,1),2, WRO.quantity_per_assembly/p_lot_size,
3033 WRO.quantity_per_assembly)/
3034 DECODE(l_include_comp_yield,
3035 1, nvl(WRO.component_yield_factor,1),
3036 1)),
3037 nvl(WRO.COSTED_QUANTITY_ISSUED, 0)) ,
3038 DECODE(sign(NVL(WRO.COSTED_QUANTITY_RELIEVED, 0)), 1,
3039 NVL(WRO.COSTED_QUANTITY_RELIEVED, 0), 0))
3040 * CIC.MATERIAL_COST,0)),0),
3041
3042 nvl(SUM(NVL(DECODE(p_run_mode, 1,
3043 DECODE(p_txn_type, 4, /* LBM project Changes */
3044 (DECODE(NVL(WRO.basis_type,1),2, WRO.quantity_per_assembly/p_lot_size,
3045 WRO.quantity_per_assembly)/
3046 DECODE(l_include_comp_yield,
3047 1, nvl(WRO.component_yield_factor,1),
3048 1)),
3049 nvl(WRO.COSTED_QUANTITY_ISSUED, 0)),
3050 DECODE(sign(NVL(WRO.COSTED_QUANTITY_RELIEVED, 0)), 1,
3051 NVL(WRO.COSTED_QUANTITY_RELIEVED, 0), 0))
3052 * CIC.MATERIAL_OVERHEAD_COST,0)),0),
3053
3054 nvl(SUM(NVL(DECODE(p_run_mode, 1,
3055 DECODE(p_txn_type, 4, /* LBM project Changes */
3056 (DECODE(NVL(WRO.basis_type,1),2, WRO.quantity_per_assembly/p_lot_size,
3057 WRO.quantity_per_assembly)/
3058 DECODE(l_include_comp_yield,
3059 1, nvl(WRO.component_yield_factor,1),
3060 1)),
3061 nvl(WRO.COSTED_QUANTITY_ISSUED, 0)),
3062 DECODE(sign(NVL(WRO.COSTED_QUANTITY_RELIEVED, 0)), 1,
3063 NVL(WRO.COSTED_QUANTITY_RELIEVED, 0), 0))
3064 * CIC.RESOURCE_COST,0)),0),
3065
3066 nvl(SUM(NVL(DECODE(p_run_mode, 1,
3067 DECODE(p_txn_type, 4, /* LBM project Changes */
3068 (DECODE(NVL(WRO.basis_type,1),2, WRO.quantity_per_assembly/p_lot_size,
3069 WRO.quantity_per_assembly)/
3070 DECODE(l_include_comp_yield,
3071 1, nvl(WRO.component_yield_factor,1),
3072 1)),
3073 nvl(WRO.COSTED_QUANTITY_ISSUED, 0)),
3074 DECODE(sign(NVL(WRO.COSTED_QUANTITY_RELIEVED, 0)), 1,
3075 NVL(WRO.COSTED_QUANTITY_RELIEVED, 0), 0))
3076 * CIC.OUTSIDE_PROCESSING_COST,0)),0),
3077
3078 nvl(SUM(NVL(DECODE(p_run_mode, 1,
3079 DECODE(p_txn_type, 4, /* LBM project Changes */
3080 (DECODE(NVL(WRO.basis_type,1),2, WRO.quantity_per_assembly/p_lot_size,
3081 WRO.quantity_per_assembly)/
3082 DECODE(l_include_comp_yield,
3083 1, nvl(WRO.component_yield_factor,1),
3084 1)),
3085 nvl(WRO.COSTED_QUANTITY_ISSUED, 0)),
3086 DECODE(sign(NVL(WRO.COSTED_QUANTITY_RELIEVED, 0)), 1,
3087 NVL(WRO.COSTED_QUANTITY_RELIEVED, 0), 0))
3088 * CIC.OVERHEAD_COST,0)),0)
3089 INTO
3090 x_pl_mtl_cost,
3091 x_pl_mto_cost,
3092 x_pl_res_cost,
3093 x_pl_osp_cost,
3094 x_pl_ovh_cost
3095 FROM
3096 wip_requirement_operations WRO,
3097 cst_item_costs CIC
3098 WHERE
3099 CIC.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
3100 AND WRO.ORGANIZATION_ID = p_org_id
3101 AND CIC.ORGANIZATION_ID = WRO.ORGANIZATION_ID
3102 AND CIC.COST_TYPE_ID = 1
3103 AND WRO.WIP_ENTITY_ID = p_entity_id
3104 AND WRO.OPERATION_SEQ_NUM <= l_operation_seq_num
3105 /* Changes for Lot Based Materials project */
3106 AND (NVL(WRO.BASIS_TYPE,1) <> 2 OR (p_txn_type IN (1,4)))
3107 AND ((p_txn_type = 4) OR
3108 (abs(nvl(WRO.COSTED_QUANTITY_ISSUED, 0)) >= abs(nvl(WRO.COSTED_QUANTITY_RELIEVED, 0)))) /* Added abs() for bug 6774122 */
3109 /* LBM Changes end*/
3110 AND ( WRO.WIP_SUPPLY_TYPE not in (2, 4, 5, 6) or p_txn_type <> 4 )
3111 AND not exists (select 'obsolete operation'
3112 from wip_operations WO
3113 where WO.wip_entity_id = WRO.wip_entity_id
3114 and WO.organization_id = WRO.organization_id
3115 and WO.operation_seq_num = WRO.operation_seq_num
3116 and WO.disable_date <= l_transaction_date );
3117
3118
3119 /*
3120 Exclude Assembly Pull(2), Bulk Items(4), Phantom(6) and () for Bonus Txn
3121 */
3122 l_stmt_num := 20;
3123 /*
3124 The following select statement is to calculate this level resource charge
3125 and this level outside processing charge.
3126 */
3127 SELECT
3128 NVL(SUM(DECODE(BR.COST_ELEMENT_ID,
3129 3, DECODE(p_run_mode,
3130 1, DECODE(p_txn_type, 4,
3131 NVL((DECODE(WOR.basis_type,
3132 1,WOR.usage_rate_or_amount,
3133 2,WOR.usage_rate_or_amount/p_lot_size,
3134 WOR.usage_rate_or_amount) *
3135 DECODE(BR.functional_currency_flag,
3136 1,1, nvl(CRC.resource_rate,0))),0),
3137 NVL(WOR.APPLIED_RESOURCE_VALUE,0)),
3138 DECODE(sign(nvl(WOR.relieved_res_value, 0)), 1, nvl(WOR.relieved_res_value, 0), 0)),
3139 0)),0),
3140 NVL(SUM(DECODE(BR.COST_ELEMENT_ID,
3141 4, DECODE(p_run_mode,
3142 1, DECODE(p_txn_type, 4,
3143 NVL((DECODE(WOR.basis_type,
3144 1,WOR.usage_rate_or_amount,
3145 2,WOR.usage_rate_or_amount/p_lot_size,
3146 WOR.usage_rate_or_amount) *
3147 DECODE(BR.functional_currency_flag,
3148 1,1,nvl(CRC.resource_rate,0))),0),
3149 NVL(WOR.APPLIED_RESOURCE_VALUE,0)),
3150 DECODE(sign(nvl(WOR.relieved_res_value, 0)), 1, nvl(WOR.relieved_res_value, 0), 0)),
3151 0)),0)
3152 INTO
3153 x_tl_res_cost,
3154 x_tl_osp_cost
3155 FROM cst_resource_costs CRC,
3156 wip_operation_resources WOR,
3157 bom_resources BR
3158 WHERE
3159 CRC.COST_TYPE_ID(+) = 1
3160 AND CRC.RESOURCE_ID(+) = WOR.RESOURCE_ID
3161 AND WOR.OPERATION_SEQ_NUM <= l_operation_seq_num
3162 AND BR.RESOURCE_ID = WOR.RESOURCE_ID
3163 AND WOR.WIP_ENTITY_ID = p_entity_id
3164 AND WOR.ORGANIZATION_ID = p_org_id
3165 AND (WOR.basis_type <> 2 or p_txn_type in (1, 4))
3166 AND ((p_txn_type = 4) OR
3167 nvl(WOR.applied_resource_value, 0) >= nvl(WOR.relieved_res_value, 0))
3168 AND not exists (select 'obsolete operation'
3169 from wip_operations WO
3170 where WO.wip_entity_id = WOR.wip_entity_id
3171 and WO.organization_id = WOR.organization_id
3172 and WO.operation_seq_num = WOR.operation_seq_num
3173 and WO.disable_date <= l_transaction_date );
3174
3175 l_stmt_num := 30;
3176 /*
3177 IF run_mode = CHARGE
3178 IF txn_type = BONUS
3179 Overhead = CDO.rate_or_amount * WOR.usage_rate_or_amount * CRC.resource_rate
3180 ELSE
3181 Overhead = CDO.rate_or_amount * WOR.costed_applied_resource_units * CRC.resource_rate
3182 ELSE -- run_mode = SCRAP (never called for BONUS)
3183 Overhead = CDO.rate_or_amount * WOR.RELIEVED_RES_VALUE
3184 */
3185
3186 IF (p_txn_type = 4) THEN
3187 SELECT NVL(SUM(NVL(CDO.rate_or_amount *
3188 DECODE(WOR.basis_type,
3189 1,WOR.usage_rate_or_amount,
3190 2,WOR.usage_rate_or_amount/p_lot_size,
3191 WOR.usage_rate_or_amount) *
3192 DECODE(CDO.basis_type, 3, 1,
3193 DECODE(BR.functional_currency_flag,
3194 1,1,nvl(CRC.resource_rate,0))), 0)), 0)
3195
3196 INTO
3197 x_tl_ovh_cost
3198 FROM
3199 wip_operations WO,
3200 wip_operation_resources WOR,
3201 cst_resource_overheads CRO,
3202 cst_department_overheads CDO,
3203 bom_resources BR,
3204 cst_resource_costs CRC
3205 WHERE
3206 WO.wip_entity_id = p_entity_id
3207 AND WOR.resource_id = BR.resource_id
3208 AND CRC.resource_id(+) = BR.resource_id
3209 AND WO.operation_seq_num = WOR.operation_seq_num
3210 AND NVL(WO.DISABLE_DATE, l_transaction_date) <= l_transaction_date
3211 AND WOR.organization_id = p_org_id
3212 AND WOR.wip_entity_id = p_entity_id
3213 AND WOR.operation_seq_num <= l_operation_seq_num
3214 AND WOR.organization_id = p_org_id
3215 AND CDO.department_id = WO.department_id
3216 AND CDO.basis_type in (3, 4)
3217 AND CDO.overhead_id = CRO.overhead_id
3218 AND CRO.resource_id = WOR.resource_id
3219 AND CRC.cost_type_id(+) = 1
3220 AND CRO.cost_type_id = 1
3221 AND CDO.cost_type_id = 1;
3222
3223 /* Department based overheads for Bonus */
3224
3225 SELECT NVL(SUM(NVL(DECODE(CDO.basis_type,
3226 1,CDO.rate_or_amount,
3227 2,CDO.rate_or_amount/p_lot_size),0)), 0)
3228 INTO l_tl_ovh_dept_cost
3229 FROM wip_operations WO,
3230 cst_department_overheads CDO
3231 WHERE
3232 WO.wip_entity_id = p_entity_id
3233 AND WO.operation_seq_num <= l_operation_seq_num
3234 AND WO.organization_id = p_org_id
3235 AND nvl(WO.DISABLE_DATE, l_transaction_date) <= l_transaction_date
3236 AND CDO.department_id = WO.department_id
3237 AND CDO.organization_id = WO.organization_id
3238 AND CDO.basis_type in (1,2)
3239 AND CDO.cost_type_id = 1;
3240
3241 x_tl_ovh_cost := x_tl_ovh_cost + l_tl_ovh_dept_cost;
3242
3243 /* For non bonus transactions use WIP_OPERATION_OVERHEADS to calculate overheads */
3244
3245 ELSE
3246 SELECT nvl(DECODE(p_run_mode,
3247 1, SUM(NVL(WOO.applied_ovhd_value,0)),
3248 2, SUM(NVL(WOO.relieved_ovhd_value,0))),0)
3249 INTO x_tl_ovh_cost
3250 FROM wip_operation_overheads WOO,
3251 cst_resource_overheads CRO,
3252 wip_operation_resources WOR
3253 WHERE
3254 WOO.operation_seq_num <= l_operation_seq_num
3255 AND WOO.wip_entity_id = p_entity_id
3256 AND WOO.organization_id = p_org_id
3257 AND CRO.overhead_id = WOO.overhead_id
3258 AND CRO.resource_id = WOR.resource_id
3259 AND WOR.operation_seq_num = WOO.operation_seq_num
3260 AND WOR.resource_seq_num = WOO.resource_seq_num
3261 AND WOR.wip_entity_id = WOO.wip_entity_id
3262 AND WOR.organization_id = p_org_id
3263 AND CRO.cost_type_id = 1
3264 AND WOO.basis_type in (3, 4)
3265 AND nvl(WOR.applied_resource_value, 0) >= nvl(WOR.relieved_res_value, 0)
3266 AND not exists
3267 ( SELECT 1
3268 FROM wip_operations WO
3269 WHERE WO.DISABLE_DATE <= l_transaction_date
3270 AND WO.operation_seq_num = WOO.operation_seq_num
3271 AND WO.wip_entity_id = p_entity_id )
3272 AND ( WOR.basis_type <> 2 or p_txn_type = 1 );
3273
3274 SELECT nvl(DECODE(p_run_mode,
3275 1, SUM(NVL(WOO.applied_ovhd_value,0)),
3276 2, SUM(NVL(WOO.relieved_ovhd_value,0))),0)
3277 INTO l_tl_ovh_dept_cost
3278 FROM wip_operation_overheads WOO
3279 WHERE
3280 WOO.operation_seq_num <= l_operation_seq_num
3281 AND WOO.wip_entity_id = p_entity_id
3282 AND WOO.organization_id = p_org_id
3283 AND WOO.basis_type in (1, 2)
3284 AND not exists
3285 ( SELECT 1
3286 FROM wip_operations WO
3287 WHERE WO.DISABLE_DATE <= l_transaction_date
3288 AND WO.operation_seq_num = WOO.operation_seq_num
3289 AND WO.wip_entity_id = p_entity_id )
3290 AND ( WOO.basis_type <> 2 or p_txn_type = 1 );
3291
3292 x_tl_ovh_cost := x_tl_ovh_cost + l_tl_ovh_dept_cost;
3293
3294 END IF;
3295
3296 IF(l_debug_flag = 'Y') THEN
3297 FND_FILE.put_line(fnd_file.log,'CSTPSMUT.GET_JOB_VALUE <<<');
3298 FND_FILE.put_line(fnd_file.log,'Job: '||to_char(p_entity_id));
3299 FND_FILE.put_line(fnd_file.log,'Run Mode(1-Charge, 2-Scrap): ' ||to_char(p_run_mode));
3300 FND_FILE.put_line(fnd_file.log,'Transaction Type: ' ||to_char(p_txn_type));
3301 FND_FILE.put_line(fnd_file.log,'PL MTL COST: '||to_char(x_pl_mtl_cost));
3302 FND_FILE.put_line(fnd_file.log,'PL MOH COST: '||to_char(x_pl_mto_cost));
3303 FND_FILE.put_line(fnd_file.log,'PL RES COST: '||to_char(x_pl_res_cost));
3304 FND_FILE.put_line(fnd_file.log,'PL OSP COST: '||to_char(x_pl_osp_cost));
3305 FND_FILE.put_line(fnd_file.log,'PL OVH COST: '||to_char(x_pl_ovh_cost));
3306 FND_FILE.put_line(FND_FILE.log,'TL RES COST: '||to_char(x_tl_res_cost));
3307 FND_FILE.put_line(FND_FILE.log,'TL OSP COST: '||to_char(x_tl_osp_cost));
3308 FND_FILE.put_line(FND_FILE.log,'TL OVH COST: '||to_char(x_tl_ovh_cost));
3309 FND_FILE.put_line(fnd_file.log,'CSTPSMUT.GET_JOB_VALUE >>>');
3310 END IF;
3311
3312 EXCEPTION
3313 when others then
3314 x_err_code:= null;
3315 x_err_num := SQLCODE;
3316 x_err_msg := 'CSTPSMUT: GET_JOB_VALUE- '||l_stmt_num||'.'||SQLERRM;
3317
3318 END GET_JOB_VALUE;
3319
3320
3321 ----------------------------------------------------------------------------
3322 -- PROCEDURE --
3323 -- UPDATE_JOB_QUANTITY --
3324 -- --
3325 -- DESCRIPTION --
3326 -- The procedure is called by the routines costing a lot transaction.
3327 -- It updates COSTED_QUANTITY in WRO, APPLIED_RESOURCE_UNITS and
3328 -- APPLIED_RESOURCE_VALUE in WOR and APPLIED_OVHD_UNITS and
3329 -- APPLIED_OVHD_VALUE in WOO
3330 --
3331 -- --
3332 -- PURPOSE: --
3333 -- OSFM Lot Transactions Costing for Oracle Applications Rel 11i.8
3334 -- --
3335 -- PARAMETERS: --
3336 -- p_api_version API version
3337 -- p_txn_id Transaction ID from MMT
3338 -- x_err_num Error Number
3339 -- x_err_code Error Code --
3340 -- x_err_msg Error Message --
3341
3342 -- HISTORY: --
3343 -- August-2002 Vinit Redesign --
3344 ----------------------------------------------------------------------------
3345
3346 PROCEDURE UPDATE_JOB_QUANTITY ( p_api_version IN NUMBER,
3347 p_txn_id IN NUMBER,
3348 x_err_num IN OUT NOCOPY NUMBER,
3349 x_err_code IN OUT NOCOPY VARCHAR2,
3350 x_err_msg IN OUT NOCOPY VARCHAR2 ) IS
3351
3352 l_api_name CONSTANT VARCHAR2(240) := 'UPDATE_JOB_QUANTITY';
3353 l_api_version CONSTANT NUMBER := 1.0;
3354
3355 l_min_acct_unit NUMBER := NULL;
3356 l_org_id NUMBER := NULL;
3357
3358 l_stmt_num NUMBER := 0;
3359
3360 /* Transaction Information */
3361 l_txn_type NUMBER;
3362 l_transaction_date DATE;
3363 l_operation_seq_num NUMBER;
3364 l_intraoperation_step NUMBER;
3365 l_available_quantity NUMBER;
3366 l_job_start_quantity NUMBER;
3367 l_rep_wip_entity_id NUMBER;
3368
3369 /* Other */
3370 l_scale_factor NUMBER;
3371 l_resulting_job NUMBER := 0;
3372 l_resulting_scale_factor NUMBER := 0;
3373 l_include_comp_yield NUMBER;
3374
3375 CURSOR C_RJ IS SELECT *
3376 FROM wsm_sm_resulting_jobs
3377 WHERE transaction_id = p_txn_id;
3378
3379 CURSOR C_SJ IS SELECT *
3380 FROM wsm_sm_starting_jobs
3381 WHERE transaction_id = p_txn_id;
3382
3383
3384 BEGIN
3385 l_stmt_num := 10;
3386 IF(l_debug_flag = 'Y') THEN
3387 FND_FILE.put_line(fnd_file.log,'CSTPSMUT.UPDATE_JOB_QUANTITY <<<');
3388 END IF;
3389
3390 IF NOT FND_API.COMPATIBLE_API_CALL (
3391 l_api_version,
3392 p_api_version,
3393 l_api_name,
3394 G_PKG_NAME ) THEN
3395 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3396 END IF;
3397
3398
3399 /* Obtain Transaction Information */
3400 l_stmt_num := 20;
3401
3402 SELECT organization_id,
3403 transaction_type_id,
3404 transaction_date
3405 INTO l_org_id,
3406 l_txn_type,
3407 l_transaction_date
3408 FROM WSM_SPLIT_MERGE_TRANSACTIONS
3409 WHERE transaction_id = p_txn_id;
3410
3411 /* Get the value of Include Component yield flag,
3412 which will determine whether to include or not
3413 component yield factor in quantity per assembly */
3414 l_stmt_num := 25;
3415 SELECT NVL(include_component_yield, 1)
3416 INTO l_include_comp_yield
3417 FROM wip_parameters
3418 WHERE organization_id = l_org_id;
3419
3420 l_stmt_num := 30;
3421
3422 /* Get Minimum Accounting Unit */
3423
3424 SELECT NVL(FC.minimum_accountable_unit, POWER(10,NVL(-precision,0)))
3425 INTO l_min_acct_unit
3426 FROM fnd_currencies fc,
3427 cst_organization_definitions o
3428 WHERE o.organization_id = l_org_id
3429 AND o.currency_code = fc.currency_code;
3430
3431 IF (l_txn_type = WSMPCNST.BONUS) THEN
3432 FOR C_result in C_RJ LOOP
3433
3434 l_operation_seq_num := C_result.JOB_OPERATION_SEQ_NUM;
3435
3436 /* Only for jobs created before 11i.8, we need to use
3437 STARTING_OPERATION_SEQ_NUM to get the operation_seq_num for
3438 Bonus. For such jobs, JOB_OPERATION_SEQ_NUM is NULL */
3439
3440 l_stmt_num := 35;
3441
3442 IF l_operation_seq_num IS NULL THEN
3443 SELECT wo.operation_seq_num
3444 INTO l_operation_seq_num
3445 FROM WIP_OPERATIONS WO,
3446 WSM_SM_RESULTING_JOBS WSRJ,
3447 BOM_OPERATION_SEQUENCES BOS
3448 WHERE WSRJ.transaction_id = p_txn_id
3449 AND nvl(wsrj.starting_intraoperation_step, 1) = 1
3450 AND wsrj.common_routing_sequence_id = bos.routing_sequence_id
3451 AND wsrj.starting_operation_seq_num = bos.operation_seq_num
3452 AND bos.operation_sequence_id = wo.operation_sequence_id
3453 AND bos.EFFECTIVITY_DATE <= l_transaction_date
3454 AND NVL( bos.DISABLE_DATE, l_transaction_date + 1) > l_transaction_date
3455 AND wo.wip_entity_id = wsrj.wip_entity_id
3456 AND wo.organization_id = l_org_id;
3457 END IF;
3458
3459 /* Update applied resource units and applied resource value. */
3460 l_stmt_num := 40;
3461
3462
3463 UPDATE wip_operation_resources wor
3464 SET wor.applied_resource_units = ROUND((C_result.start_quantity *
3465 decode(wor.basis_type,
3466 1,wor.usage_rate_or_amount,
3467 2,wor.usage_rate_or_amount/C_result.start_quantity,
3468 wor.usage_rate_or_amount)), 6),
3469 wor.applied_resource_value = (SELECT
3470 ROUND((nvl(max(C_result.start_quantity *
3471 decode(wor.basis_type,
3472 1,wor.usage_rate_or_amount,
3473 2,wor.usage_rate_or_amount/C_result.start_quantity,
3474 wor.usage_rate_or_amount)*
3475 decode (br.functional_currency_flag,
3476 1, 1,
3477 nvl(crc.resource_rate,0)))
3478 ,0))/l_min_acct_unit) * l_min_acct_unit
3479 FROM bom_resources br,
3480 cst_resource_costs crc
3481 WHERE
3482 br.resource_id = wor.resource_id
3483 AND br.organization_id = l_org_id
3484 AND crc.cost_type_id = 1
3485 AND crc.organization_id = l_org_id
3486 AND crc.resource_id = wor.resource_id )
3487 WHERE wor.wip_entity_id = C_result.wip_entity_id
3488 AND wor.operation_seq_num < l_operation_seq_num;
3489
3490 /* Update quantity issued. */
3491
3492 l_stmt_num := 50;
3493
3494 UPDATE wip_requirement_operations wro
3495 SET costed_quantity_issued = ROUND((C_result.start_quantity *
3496 DECODE(nvl(wro.basis_type,1),
3497 2, wro.quantity_per_assembly/C_result.start_quantity,
3498 wro.quantity_per_assembly) /
3499 DECODE(l_include_comp_yield,
3500 1, nvl(wro.component_yield_factor,1),
3501 1)), 6)
3502 WHERE wip_entity_id = C_result.wip_entity_id
3503 AND operation_seq_num < l_operation_seq_num
3504 AND wip_supply_type not in (2, 4, 5, 6);
3505
3506 /* Update WOO */
3507
3508 l_stmt_num := 55;
3509
3510 /* Resource Unit and Value Based Overheads */
3511 UPDATE wip_operation_overheads woo
3512 SET ( applied_ovhd_units,
3513 applied_ovhd_value ) =
3514 ( SELECT decode(woo.basis_type,
3515 3, NVL(WOR.applied_resource_units,0),
3516 4, NVL(WOR.APPLIED_RESOURCE_VALUE,0)),
3517 decode(woo.basis_type,
3518 3, NVL(CDO.rate_or_amount* WOR.applied_resource_units,0),
3519 4, NVL(CDO.rate_or_amount*
3520 NVL(WOR.APPLIED_RESOURCE_VALUE,0), 0))
3521 FROM wip_operation_resources WOR,
3522 cst_resource_overheads CRO,
3523 cst_department_overheads CDO,
3524 wip_operations WO
3525 WHERE
3526 WOR.wip_entity_id = C_result.wip_entity_id
3527 AND WOR.organization_id = l_org_id
3528 AND WOR.operation_seq_num = WOO.operation_seq_num
3529 AND WOR.resource_seq_num = WOO.resource_seq_num
3530 AND WOR.resource_id = CRO.resource_id
3531 AND CRO.overhead_id = CDO.overhead_id
3532 AND CDO.overhead_id = WOO.overhead_id
3533 AND CDO.department_id = WO.department_id
3534 AND WO.wip_entity_id = C_result.wip_entity_id
3535 AND WO.organization_id = l_org_id
3536 AND WO.operation_seq_num = WOO.operation_seq_num
3537 AND CRO.cost_type_id = 1
3538 AND CDO.cost_type_id = 1
3539 AND CDO.basis_type = WOO.basis_type
3540 )
3541 WHERE
3542 wip_entity_id = C_result.wip_entity_id
3543 AND organization_id = l_org_id
3544 AND operation_seq_num < l_operation_seq_num
3545 AND basis_type in (3,4);
3546
3547 l_stmt_num := 58;
3548
3549 /* Department Based Overheads */
3550
3551 UPDATE wip_operation_overheads woo
3552 SET ( applied_ovhd_units,
3553 applied_ovhd_value ) =
3554 ( SELECT decode(woo.basis_type, 1, C_result.start_quantity,
3555 2, 1),
3556 decode(woo.basis_type, 1, CDO.rate_or_amount * C_result.start_quantity,
3557 2, CDO.rate_or_amount)
3558 FROM wip_operations WO,
3559 cst_department_overheads CDO
3560 WHERE
3561 woo.operation_seq_num = wo.operation_seq_num
3562 AND WO.wip_entity_id = C_result.wip_entity_id
3563 AND WO.organization_id = l_org_id
3564 AND CDO.department_id = WO.department_id
3565 AND CDO.overhead_id = WOO.overhead_id
3566 AND CDO.cost_type_id = 1
3567 AND CDO.basis_type = WOO.basis_type )
3568 WHERE wip_entity_id = C_result.wip_entity_id
3569 AND organization_id = l_org_id
3570 AND operation_seq_num < l_operation_seq_num
3571 AND basis_type in (1,2);
3572
3573 END LOOP;
3574 x_err_num := 0;
3575 x_err_code := NULL;
3576
3577 ELSE
3578 /* Update Quantity/Split/Merge */
3579 /* Obtain Information from WSM_SM_STARTING_JOBS
3580 for representative Lot. */
3581 l_stmt_num := 60;
3582
3583 SELECT operation_seq_num,
3584 intraoperation_step,
3585 wip_entity_id,
3586 available_quantity,
3587 job_start_quantity
3588 INTO l_operation_seq_num,
3589 l_intraoperation_step,
3590 l_rep_wip_entity_id,
3591 l_available_quantity,
3592 l_job_start_quantity
3593 FROM WSM_SM_STARTING_JOBS
3594 WHERE transaction_id = p_txn_id
3595 AND representative_flag = 'Y';
3596
3597 FOR C_rec IN C_RJ LOOP
3598 l_stmt_num := 70;
3599
3600 l_scale_factor := C_rec.start_quantity/l_available_quantity;
3601
3602 UPDATE wip_operation_resources wor
3603 SET ( wor.applied_resource_units,
3604 wor.applied_resource_value ) =
3605 ( SELECT (nvl(wor1.applied_resource_units, 0) - DECODE(sign(nvl(wor1.relieved_res_units, 0)), 1, nvl(wor1.relieved_res_units, 0), 0)) *
3606 DECODE(sign(nvl(wor1.applied_resource_units, 0) - DECODE(sign(nvl(wor1.relieved_res_units, 0)), 1, nvl(wor1.relieved_res_units, 0), 0)),
3607 1, 1, 0),
3608 (nvl(wor1.applied_resource_value,0) - DECODE(sign(nvl(wor1.relieved_res_value, 0)), 1, nvl(wor1.relieved_res_value, 0), 0)) *
3609 DECODE(sign(nvl(wor1.applied_resource_value,0) - DECODE(sign(nvl(wor1.relieved_res_value, 0)), 1, nvl(wor1.relieved_res_value, 0), 0)),
3610 1, 1, 0)
3611 FROM wip_operation_resources wor1
3612 WHERE wor1.operation_seq_num = wor.operation_seq_num
3613 AND wor1.wip_entity_id = l_rep_wip_entity_id
3614 AND wor1.organization_id = wor.organization_id
3615 AND wor1.resource_seq_num = wor.resource_seq_num )
3616
3617 WHERE wor.wip_entity_id = C_rec.wip_entity_id
3618 AND wor.organization_id = l_org_id
3619 AND wor.wip_entity_id <> l_rep_wip_entity_id
3620 AND not exists (select 'obsolete operation'
3621 from wip_operations wo
3622 where wo.wip_entity_id = wor.wip_entity_id
3623 and wo.organization_id = wor.organization_id
3624 and wo.operation_seq_num = wor.operation_seq_num
3625 and wo.disable_date <= l_transaction_date )
3626 /* Make sure the operation exists in the Parent */
3627 AND exists (select 'operation exists'
3628 from wip_operation_resources wor2
3629 WHERE wor2.operation_seq_num = wor.operation_seq_num
3630 AND wor2.wip_entity_id = l_rep_wip_entity_id
3631 AND wor2.organization_id = wor.organization_id
3632 AND wor2.resource_seq_num = wor.resource_seq_num);
3633
3634 l_stmt_num := 75;
3635
3636 UPDATE wip_requirement_operations wro
3637 SET wro.costed_quantity_issued =
3638 ( SELECT (NVL(wro1.costed_quantity_issued,0) - DECODE(sign(NVL(WRO1.COSTED_QUANTITY_RELIEVED, 0)), 1, NVL(WRO1.COSTED_QUANTITY_RELIEVED, 0), 0) )*
3639 /* LBM changes (This is cond like basis_type<>2 or l_txn_type=1) Bugs 5202282*/
3640 decode(l_txn_type, 1, l_scale_factor, decode(nvl(wro.basis_type,1), 2, 1, l_scale_factor)) *
3641 DECODE(sign(nvl(wro1.costed_quantity_issued,0) - DECODE(sign(NVL(WRO1.COSTED_QUANTITY_RELIEVED, 0)), 1, NVL(WRO1.COSTED_QUANTITY_RELIEVED, 0), 0)),
3642 1, 1, 0)
3643 FROM wip_requirement_operations wro1
3644 WHERE wro1.wip_entity_id = l_rep_wip_entity_id
3645 AND wro1.inventory_item_id = wro.inventory_item_id
3646 AND wro1.organization_id = wro.organization_id
3647 AND wro1.operation_seq_num = wro.operation_seq_num )
3648 WHERE wro.wip_entity_id = C_rec.wip_entity_id
3649 AND wro.organization_id = l_org_id
3650 AND wro.wip_entity_id <> l_rep_wip_entity_id
3651 AND not exists (select 'obsolete operation'
3652 from wip_operations wo
3653 where wo.wip_entity_id = wro.wip_entity_id
3654 and wo.organization_id = wro.organization_id
3655 and wo.operation_seq_num = wro.operation_seq_num
3656 and wo.disable_date <= l_transaction_date )
3657 /* Make sure the operation exists in the Parent */
3658 AND exists (select 'operation exists'
3659 from wip_requirement_operations wro2
3660 WHERE wro2.wip_entity_id = l_rep_wip_entity_id
3661 AND wro2.inventory_item_id = wro.inventory_item_id
3662 AND wro2.organization_id = wro.organization_id
3663 AND wro2.operation_seq_num = wro.operation_seq_num );
3664
3665 l_stmt_num := 80;
3666
3667 /* For WOO, the strategy is different */
3668 /* For Lot and Item based overheads,
3669 Update WOO using the values for corresponding fields in the
3670 representative lot
3671 For resource unit and resource value based overheads,
3672 set applied_ovhd_units = applied_res_units (Res Unit based ovhd)
3673 = applied_res_value (Res Value based ovhd)
3674 from WOR.
3675 applied_ovhd_value = CDO.rate_or_amount * applied_ovhd_units
3676 */
3677
3678 /* For Item and Lot based Ovhd's, Initialize the Ovhd's */
3679 UPDATE wip_operation_overheads woo
3680 SET ( woo.applied_ovhd_units,
3681 woo.applied_ovhd_value ) =
3682 ( SELECT (NVL(woo1.applied_ovhd_units,0) - DECODE(sign(nvl(woo1.relieved_ovhd_units, 0)), 1, nvl(woo1.relieved_ovhd_units, 0), 0) ) *
3683 DECODE(sign(nvl(woo1.applied_ovhd_units,0) - DECODE(sign(nvl(woo1.relieved_ovhd_units, 0)), 1, nvl(woo1.relieved_ovhd_units, 0), 0)),
3684 1, 1, 0),
3685 (NVL(woo1.applied_ovhd_value,0) - DECODE(sign(nvl(woo1.relieved_ovhd_value, 0)), 1, nvl(woo1.relieved_ovhd_value, 0), 0) ) *
3686 DECODE(sign(nvl(woo1.applied_ovhd_value,0) - DECODE(sign(nvl(woo1.relieved_ovhd_value, 0)), 1, nvl(woo1.relieved_ovhd_value, 0), 0)),
3687 1, 1, 0)
3688 FROM wip_operation_overheads woo1
3689 WHERE woo1.wip_entity_id = l_rep_wip_entity_id
3690 AND woo1.overhead_id = woo.overhead_id
3691 AND woo1.organization_id = woo.organization_id
3692 AND woo1.operation_seq_num = woo.operation_seq_num
3693 AND woo1.resource_seq_num = woo.resource_seq_num)
3694 WHERE woo.wip_entity_id = C_rec.wip_entity_id
3695 AND woo.organization_id = l_org_id
3696 AND woo.wip_entity_id <> l_rep_wip_entity_id
3697 AND not exists (select 'obsolete operation'
3698 from wip_operations wo
3699 where wo.wip_entity_id = woo.wip_entity_id
3700 and wo.organization_id = woo.organization_id
3701 and wo.operation_seq_num = woo.operation_seq_num
3702 and wo.disable_date <= l_transaction_date )
3703 AND woo.basis_type in (1,2)
3704 /* Make sure the operation and overhead exist in the Parent */
3705 AND exists (select 'operation exists'
3706 from wip_operation_overheads woo2
3707 WHERE woo2.wip_entity_id = l_rep_wip_entity_id
3708 AND woo2.overhead_id = woo.overhead_id
3709 AND woo2.organization_id = woo.organization_id
3710 AND woo2.operation_seq_num = woo.operation_seq_num
3711 AND woo2.resource_seq_num = woo.resource_seq_num);
3712 END LOOP;
3713
3714 FOR C_rec1 IN C_RJ LOOP
3715 l_scale_factor := C_rec1.start_quantity/l_available_quantity;
3716
3717 IF(l_debug_flag = 'Y') THEN
3718 FND_FILE.put_line(fnd_file.log, 'Job: '||to_char(C_rec1.wip_entity_id));
3719 FND_FILE.put_line(fnd_file.log,'Updating the Non Representative Lots by the scale factor: '||to_char(l_scale_factor));
3720 END IF;
3721
3722 IF C_rec1.wip_entity_id = l_rep_wip_entity_id THEN
3723 l_resulting_job := 1;
3724 l_resulting_scale_factor := l_scale_factor;
3725 END IF;
3726
3727 l_stmt_num := 92;
3728
3729 UPDATE wip_operation_resources wor
3730 SET wor.applied_resource_units = wor.applied_resource_units * l_scale_factor,
3731 wor.applied_resource_value = wor.applied_resource_value * l_scale_factor
3732 WHERE wor.wip_entity_id = C_rec1.wip_entity_id
3733 AND wor.organization_id = l_org_id
3734 AND wor.wip_entity_id <> l_rep_wip_entity_id
3735 AND not exists (select 'obsolete operation'
3736 from wip_operations wo
3737 where wo.wip_entity_id = wor.wip_entity_id
3738 and wo.organization_id = wor.organization_id
3739 and wo.operation_seq_num = wor.operation_seq_num
3740 and wo.disable_date <= l_transaction_date )
3741 AND ( basis_type <> 2 or l_txn_type = 1 );
3742
3743 l_stmt_num := 95;
3744
3745 /* For Item and Lot based Ovhds, scale them depending on Txn Type
3746 Lot based ovhds not scaled for merge/update_qty
3747 */
3748
3749 UPDATE wip_operation_overheads woo
3750 SET woo.applied_ovhd_units = woo.applied_ovhd_units * l_scale_factor,
3751 woo.applied_ovhd_value = woo.applied_ovhd_value * l_scale_factor
3752 WHERE woo.wip_entity_id = C_rec1.wip_entity_id
3753 AND woo.organization_id = l_org_id
3754 AND woo.wip_entity_id <> l_rep_wip_entity_id
3755 AND not exists (select 'obsolete operation'
3756 from wip_operations wo
3757 where wo.wip_entity_id = woo.wip_entity_id
3758 and wo.organization_id = woo.organization_id
3759 and wo.operation_seq_num = woo.operation_seq_num
3760 and wo.disable_date <= l_transaction_date )
3761 AND ( basis_type <> 2 or l_txn_type = 1 )
3762 AND basis_type in (1, 2);
3763
3764 /* Update WOO for Resource Unit and Value based Ovhds */
3765
3766 l_stmt_num := 98;
3767
3768 UPDATE wip_operation_overheads woo
3769 SET ( applied_ovhd_units,
3770 applied_ovhd_value ) =
3771 ( SELECT decode(woo.basis_type,
3772 3, NVL(WOR.applied_resource_units,0),
3773 4, NVL(WOR.APPLIED_RESOURCE_VALUE,0)),
3774 decode(woo.basis_type,
3775 3, NVL(CDO.rate_or_amount* NVL(WOR.applied_resource_units, 0) , 0),
3776 4, NVL(CDO.rate_or_amount*
3777 NVL(WOR.APPLIED_RESOURCE_VALUE,0), 0))
3778 FROM wip_operation_resources WOR,
3779 cst_resource_overheads CRO,
3780 cst_department_overheads CDO,
3781 wip_operations WO
3782 WHERE
3783 WOR.wip_entity_id = C_rec1.wip_entity_id
3784 AND WOR.organization_id = l_org_id
3785 AND WOR.operation_seq_num = WOO.operation_seq_num
3786 AND WOR.resource_seq_num = WOO.resource_seq_num
3787 AND WOR.resource_id = CRO.resource_id
3788 AND CRO.overhead_id = CDO.overhead_id
3789 AND CDO.overhead_id = WOO.overhead_id
3790 AND CDO.department_id = WO.department_id
3791 AND WO.wip_entity_id = C_rec1.wip_entity_id
3792 AND WO.organization_id = l_org_id
3793 AND WO.operation_seq_num = WOO.operation_seq_num
3794 AND CRO.cost_type_id = 1
3795 AND CDO.cost_type_id = 1
3796 AND CDO.basis_type = WOO.basis_type
3797 )
3798 WHERE
3799 wip_entity_id = C_rec1.wip_entity_id
3800 AND wip_entity_id <> l_rep_wip_entity_id
3801 AND organization_id = l_org_id
3802 AND basis_type in (3, 4);
3803
3804 END LOOP; -- end looping resulting lots
3805
3806
3807 /* If resulting job is the same as the starting job,
3808 add the relieved quantity to the quantity issued. */
3809
3810 IF (l_resulting_job = 1) THEN
3811
3812 IF(l_debug_flag = 'Y') THEN
3813 FND_FILE.put_line(fnd_file.log,'Updating Resulting Job that is part of Start Job:(App-Rel)*F + Rel: '||to_char(l_resulting_scale_factor));
3814 END IF;
3815 l_stmt_num := 100;
3816
3817 UPDATE wip_operation_resources wor
3818 SET wor.applied_resource_units = (NVL(wor.applied_resource_units,0) -
3819 DECODE(sign(nvl(wor.relieved_res_units, 0)), 1, nvl(wor.relieved_res_units, 0), 0))
3820 * l_resulting_scale_factor +
3821 DECODE(sign(nvl(wor.relieved_res_units, 0)), 1, nvl(wor.relieved_res_units, 0), 0),
3822 wor.applied_resource_value = (NVL(wor.applied_resource_value,0) -
3823 DECODE(sign(nvl(wor.relieved_res_value, 0)), 1, nvl(wor.relieved_res_value, 0), 0))
3824 * l_resulting_scale_factor +
3825 DECODE(sign(nvl(wor.relieved_res_value, 0)), 1, nvl(wor.relieved_res_value, 0), 0)
3826 WHERE wor.wip_entity_id = l_rep_wip_entity_id
3827 AND not exists (select 'obsolete operation'
3828 from wip_operations wo
3829 where wo.wip_entity_id = wor.wip_entity_id
3830 and wo.organization_id = wor.organization_id
3831 and wo.operation_seq_num = wor.operation_seq_num
3832 and wo.disable_date <= l_transaction_date )
3833 AND nvl(wor.applied_resource_units, 0) >= nvl(wor.relieved_res_units, 0)
3834 AND nvl(wor.applied_resource_value, 0) >= nvl(wor.relieved_res_value, 0)
3835 AND (wor.basis_type <> 2 or l_txn_type = 1 );
3836
3837 UPDATE wip_requirement_operations wro
3838 SET wro.costed_quantity_issued = (NVL(wro.costed_quantity_issued, 0) -
3839 DECODE(sign(NVL(WRO.COSTED_QUANTITY_RELIEVED, 0)), 1, NVL(WRO.COSTED_QUANTITY_RELIEVED, 0), 0))
3840 * l_resulting_scale_factor +
3841 DECODE(sign(NVL(WRO.COSTED_QUANTITY_RELIEVED, 0)), 1, NVL(WRO.COSTED_QUANTITY_RELIEVED, 0), 0)
3842 WHERE wro.wip_entity_id = l_rep_wip_entity_id
3843 AND not exists (select 'obsolete operation'
3844 from wip_operations wo
3845 where wo.wip_entity_id = wro.wip_entity_id
3846 and wo.organization_id = wro.organization_id
3847 and wo.operation_seq_num = wro.operation_seq_num
3848 and wo.disable_date <= l_transaction_date )
3849 AND nvl(wro.costed_quantity_issued, 0) >= nvl(wro.costed_quantity_relieved, 0)
3850 AND (nvl(wro.basis_type,1) <> 2 or l_txn_type = 1 ); /* LBM Changes for Merge Bug 5202282 */
3851
3852 /* Update WOO in a similar manner */
3853 UPDATE wip_operation_overheads woo
3854 SET applied_ovhd_units =
3855 (NVL(woo.applied_ovhd_units, 0) - DECODE(sign(nvl(relieved_ovhd_units, 0)), 1, nvl(relieved_ovhd_units, 0), 0)) * l_resulting_scale_factor
3856 + DECODE(sign(nvl(relieved_ovhd_units, 0)), 1, nvl(relieved_ovhd_units, 0), 0),
3857 applied_ovhd_value =
3858 (NVL(woo.applied_ovhd_value, 0) - DECODE(sign(nvl(relieved_ovhd_value, 0)), 1, nvl(relieved_ovhd_value, 0), 0)) * l_resulting_scale_factor
3859 + DECODE(sign(nvl(relieved_ovhd_value, 0)), 1, nvl(relieved_ovhd_value, 0), 0)
3860 WHERE woo.wip_entity_id = l_rep_wip_entity_id
3861 AND woo.organization_id = l_org_id
3862 AND not exists (select 'obsolete operation'
3863 from wip_operations wo
3864 where wo.wip_entity_id = woo.wip_entity_id
3865 and wo.organization_id = woo.organization_id
3866 and wo.operation_seq_num = woo.operation_seq_num
3867 and wo.disable_date <= l_transaction_date )
3868 AND (NVL(woo.applied_ovhd_units, 0) - NVL(relieved_ovhd_units, 0)) >= 0
3869 AND (NVL(woo.applied_ovhd_value, 0) - NVL(relieved_ovhd_value, 0)) >= 0
3870 AND (woo.basis_type <> 2 or l_txn_type = 1 )
3871 AND woo.basis_type in (1, 2);
3872
3873 UPDATE wip_operation_overheads woo
3874 SET ( applied_ovhd_units,
3875 applied_ovhd_value ) =
3876 (SELECT decode(woo.basis_type,
3877 3, NVL(WOR.applied_resource_units,0),
3878 4, NVL(WOR.APPLIED_RESOURCE_VALUE,0)),
3879 decode(woo.basis_type,
3880 3, NVL(CDO.rate_or_amount* WOR.applied_resource_units,0),
3881 4, NVL(CDO.rate_or_amount*
3882 NVL(WOR.APPLIED_RESOURCE_VALUE,0), 0))
3883 FROM wip_operation_resources WOR,
3884 cst_resource_overheads CRO,
3885 cst_department_overheads CDO,
3886 wip_operations WO
3887 WHERE
3888 WOR.wip_entity_id = l_rep_wip_entity_id
3889 AND WOR.organization_id = l_org_id
3890 AND WOR.operation_seq_num = WOO.operation_seq_num
3891 AND WOR.resource_seq_num = WOO.resource_seq_num
3892 AND WOR.resource_id = CRO.resource_id
3893 AND CRO.overhead_id = CDO.overhead_id
3894 AND CDO.overhead_id = WOO.overhead_id
3895 AND CDO.department_id = WO.department_id
3896 AND WO.wip_entity_id = l_rep_wip_entity_id
3897 AND WO.organization_id = l_org_id
3898 AND WO.operation_seq_num = WOO.operation_seq_num
3899 AND CRO.cost_type_id = 1
3900 AND CDO.cost_type_id = 1
3901 AND CDO.basis_type = WOO.basis_type
3902 )
3903 WHERE
3904 wip_entity_id = l_rep_wip_entity_id
3905 AND organization_id = l_org_id
3906 AND basis_type in (3,4);
3907
3908 END IF;
3909
3910
3911 /* In Starting Jobs, for Jobs that are not resulting jobs,
3912 set WRO.costed_quantity_issued = WRO.quantity_relieved
3913 WOR.applied_resource_units = WOR.relieved_resource_units
3914 WOR.applied_resource_value = WOR.relieved_resource_value
3915 */
3916
3917
3918 l_resulting_job := 0;
3919
3920 FOR S_rec IN C_SJ LOOP
3921 BEGIN
3922 l_stmt_num := 110;
3923 SELECT 1
3924 INTO l_resulting_job
3925 FROM sys.dual
3926 WHERE EXISTS (SELECT 1
3927 FROM wsm_sm_resulting_jobs
3928 WHERE transaction_id = p_txn_id
3929 AND wip_entity_id = S_rec.wip_entity_id);
3930 EXCEPTION
3931 WHEN NO_DATA_FOUND THEN
3932 l_resulting_job := 0;
3933
3934 END;
3935 IF ( l_resulting_job = 0 ) THEN
3936
3937 IF(l_debug_flag = 'Y') THEN
3938 FND_FILE.put_line(fnd_file.log,'Updating Resulting Job that is not part of Start Job:App = Rel: '||to_char(S_rec.wip_entity_id));
3939 END IF;
3940
3941 l_stmt_num := 120;
3942
3943 UPDATE wip_operation_resources wor
3944 SET applied_resource_units = round(DECODE(sign(nvl(relieved_res_units, 0)), 1, nvl(relieved_res_units, 0), 0),6),
3945 applied_resource_value = round(DECODE(sign(nvl(relieved_res_value, 0)), 1, nvl(relieved_res_value, 0), 0),6)
3946 WHERE wip_entity_id = S_rec.wip_entity_id
3947 AND nvl(applied_resource_units, 0) >= nvl(relieved_res_units, 0)
3948 AND nvl(applied_resource_value, 0) >= nvl(relieved_res_value, 0)
3949 AND not exists (select 'obsolete operation'
3950 from wip_operations wo
3951 where wo.wip_entity_id = wor.wip_entity_id
3952 and wo.organization_id = wor.organization_id
3953 and wo.operation_seq_num = wor.operation_seq_num
3954 and wo.disable_date <= l_transaction_date );
3955
3956 l_stmt_num := 130;
3957
3958 UPDATE wip_requirement_operations wro
3959 SET costed_quantity_issued = round(DECODE(sign(NVL(WRO.COSTED_QUANTITY_RELIEVED, 0)), 1, NVL(WRO.COSTED_QUANTITY_RELIEVED, 0), 0),6)
3960 WHERE wip_entity_id = S_rec.wip_entity_id
3961 AND nvl(costed_quantity_issued, 0) >= nvl(costed_quantity_relieved, 0)
3962 AND not exists (select 'obsolete operation'
3963 from wip_operations wo
3964 where wo.wip_entity_id = wro.wip_entity_id
3965 and wo.organization_id = wro.organization_id
3966 and wo.operation_seq_num = wro.operation_seq_num
3967 and wo.disable_date <= l_transaction_date );
3968
3969
3970
3971 l_stmt_num := 135;
3972 UPDATE wip_operation_overheads woo
3973 SET applied_ovhd_units = DECODE(sign(nvl(relieved_ovhd_units, 0)), 1, nvl(relieved_ovhd_units, 0), 0),
3974 applied_ovhd_value = DECODE(sign(nvl(relieved_ovhd_value, 0)), 1, nvl(relieved_ovhd_value, 0), 0)
3975 WHERE woo.wip_entity_id = S_rec.wip_entity_id
3976 AND woo.organization_id= l_org_id
3977 AND not exists (select 'obsolete operation'
3978 from wip_operations wo
3979 where wo.wip_entity_id = woo.wip_entity_id
3980 and wo.organization_id = woo.organization_id
3981 and wo.operation_seq_num = woo.operation_seq_num
3982 and wo.disable_date <= l_transaction_date )
3983 AND (NVL(woo.applied_ovhd_units, 0) - NVL(relieved_ovhd_units, 0)) >= 0
3984 AND (NVL(woo.applied_ovhd_value, 0) - NVL(relieved_ovhd_value, 0)) >= 0;
3985 END IF;
3986
3987 END LOOP;
3988
3989 END IF; -- Non Bonus Transaction
3990
3991 EXCEPTION
3992 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3993 x_err_num := -1;
3994 x_err_code := 'Inconsistent API Version';--FND_API.G_RET_SYS_ERROR;
3995 x_err_msg := 'CSTPSMUT.UPDATE_JOB_QUANTITY('||to_char(l_stmt_num)||'):'|| x_err_msg || substr(SQLERRM, 1, 200);
3996 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
3997 WHEN OTHERS THEN
3998 x_err_num := -1;
3999 x_err_code := 'Error Updating Quantity';
4000 x_err_msg := 'CSTPSMUT.UPDATE_JOB_QUANTITY('||to_char(l_stmt_num)||'): ' || substr(SQLERRM, 1, 200);
4001 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
4002 END update_job_quantity;
4003
4004 -------------------------------------------------------------------------
4005 ------------------- END CHANGES FOR OSFM_I ------------------------------
4006 -------------------------------------------------------------------------
4007
4008
4009 -- the following parameters are
4010 -- from fespsmas
4011 --x_lot_size number := 0;
4012 --p_min_acct_unit number := 0;
4013 --x_ext_prec number(2):= 0;
4014 -- end fespsmas
4015
4016
4017 PROCEDURE BALANCE_ACCOUNTING (p_mtl_txn_id IN number,
4018 p_wip_txn_id IN number,
4019 p_txn_type IN number,
4020 p_err_msg IN OUT NOCOPY VARCHAR2,
4021 p_err_code IN OUT NOCOPY VARCHAR2,
4022 p_err_num IN OUT NOCOPY NUMBER) IS
4023 l_mta_total_sum NUMBER:=0;
4024 l_wta_total_sum NUMBER:=0;
4025 l_mta_temp_value NUMBER;
4026 l_wta_temp_value NUMBER;
4027 l_stmt_num NUMBER;
4028
4029 BEGIN
4030 p_err_num := 0;
4031 l_stmt_num := 10;
4032
4033 -- find out if the credit and debit are balanced in MTA
4034 SELECT sum(base_transaction_value)
4035 INTO l_mta_total_sum
4036 FROM mtl_transaction_accounts
4037 WHERE transaction_id in (p_mtl_txn_id);
4038
4039 l_stmt_num := 20;
4040 IF l_mta_total_sum <> 0 THEN
4041 IF (p_txn_type = 1) THEN -- split
4042 -- if not balanced, get the sum of all credit and debit
4043 -- except the representative lot's highest cost element elemental cost.
4044
4045 l_stmt_num := 23;
4046 -- Get the sum of all child lots
4047
4048 -- SELECT sum(base_transaction_value)
4049 -- INTO temp_value
4050 -- FROM mtl_transaction_accounts
4051 -- WHERE transaction_id in (p_mtl_txn_id)
4052 -- AND base_transaction_value > 0;
4053
4054 -- get the sum of debit/credit except the rep. lot highest cost element.
4055
4056 l_stmt_num := 30;
4057 SELECT sum(base_transaction_value)
4058 INTO l_mta_temp_value
4059 FROM mtl_transaction_accounts
4060 WHERE transaction_id in (p_mtl_txn_id)
4061 AND NOT (cost_element_id = (SELECT MAX(mta1.cost_element_id)
4062 FROM mtl_transaction_accounts mta1
4063 WHERE mta1.transaction_id = p_mtl_txn_id
4064 AND mta1.base_transaction_value < 0 )
4065 AND base_transaction_value < 0);
4066
4067 -- update the base txn value for the variance
4068 l_stmt_num := 40;
4069 UPDATE mtl_transaction_accounts mta
4070 SET base_transaction_value = -1 * (l_mta_temp_value)
4071 WHERE mta.transaction_id = p_mtl_txn_id
4072 AND mta.cost_element_id = (SELECT MAX(mta1.cost_element_id)
4073 FROM mtl_transaction_accounts mta1
4074 WHERE mta1.transaction_id = p_mtl_txn_id
4075 AND mta.base_transaction_value < 0)
4076 AND mta.base_transaction_value < 0;
4077
4078 ELSIF p_txn_type = 2 THEN -- merge txn
4079 -- if not balanced, get the sum of all credit and debit
4080 -- except the resulting lot's last cost element
4081
4082 -- Get the sum of all child lots
4083
4084 l_stmt_num := 60;
4085 SELECT sum(base_transaction_value)
4086 INTO l_mta_temp_value
4087 FROM mtl_transaction_accounts
4088 WHERE transaction_id in (p_mtl_txn_id)
4089 AND NOT (cost_element_id = (SELECT MAX(mta1.cost_element_id)
4090 FROM mtl_transaction_accounts mta1
4091 WHERE mta1.transaction_id = p_mtl_txn_id
4092 AND mta1.base_transaction_value > 0 )
4093 AND base_transaction_value > 0);
4094
4095 l_stmt_num := 70;
4096 -- update the base txn value for the variance
4097 UPDATE mtl_transaction_accounts mta
4098 SET base_transaction_value = -1 * (l_mta_temp_value)
4099 WHERE mta.transaction_id = p_mtl_txn_id
4100 AND mta.cost_element_id = (SELECT MAX(mta1.cost_element_id)
4101 FROM mtl_transaction_accounts mta1
4102 WHERE mta1.transaction_id = p_mtl_txn_id
4103 AND mta1.base_transaction_value > 0)
4104 AND mta.base_transaction_value > 0;
4105
4106 ELSIF p_txn_type in( 4, 6) THEN -- update qty, bonus transaction
4107 -- if not balanced, get the sum of all debit
4108
4109 l_stmt_num := 80;
4110 SELECT sum(base_transaction_value)
4111 INTO l_mta_temp_value
4112 FROM mtl_transaction_accounts
4113 WHERE transaction_id in (p_mtl_txn_id)
4114 AND base_transaction_value > 0;
4115
4116 l_stmt_num := 90;
4117 UPDATE mtl_transaction_accounts mta
4118 SET base_transaction_value = -1 * (l_mta_temp_value)
4119 WHERE mta.transaction_id = p_mtl_txn_id
4120 AND mta.cost_element_id is null;
4121
4122 END IF;
4123 END IF; -- MTA TOTAL SUM <> 0
4124
4125 l_stmt_num := 100;
4126 -- balance credit / debit in wta
4127 SELECT sum(base_transaction_value)
4128 INTO l_wta_total_sum
4129 FROM wip_transaction_accounts wta
4130 WHERE transaction_id in (p_wip_txn_id);
4131
4132
4133 l_stmt_num := 110;
4134 IF l_wta_total_sum <> 0 THEN
4135 IF (p_txn_type = 1) THEN
4136
4137 l_stmt_num := 120;
4138 -- SELECT sum(base_transaction_value)
4139 -- INTO temp_value
4140 -- FROM wip_transaction_accounts
4141 -- WHERE transaction_id in (p_wip_txn_id)
4142 -- AND base_transaction_value > 0;
4143
4144 l_stmt_num := 130;
4145 SELECT sum(base_transaction_value)
4146 INTO l_wta_temp_value
4147 FROM wip_transaction_accounts wta
4148 WHERE transaction_id in (p_wip_txn_id)
4149 AND NOT ( cost_element_id = (SELECT MAX(cost_element_id)
4150 FROM wip_transaction_accounts wta1
4151 WHERE wta1.transaction_id = p_wip_txn_id
4152 AND wta1.base_transaction_value < 0 )
4153 AND wta.base_transaction_value < 0);
4154
4155 l_stmt_num := 140;
4156 UPDATE wip_transaction_accounts wta
4157 SET wta.base_transaction_value = -1*(l_wta_temp_value)
4158 WHERE transaction_id in (p_wip_txn_id)
4159 AND (base_transaction_value < 0
4160 and cost_element_id=(SELECT MAX(cost_element_id)
4161 FROM wip_transaction_accounts wta1
4162 WHERE wta1.transaction_id = p_wip_txn_id
4163 AND wta1.base_transaction_value < 0 ));
4164 ELSIF (p_txn_type = 2) THEN
4165
4166 l_stmt_num := 160;
4167 SELECT sum(base_transaction_value)
4168 INTO l_wta_temp_value
4169 FROM wip_transaction_accounts wta
4170 WHERE transaction_id in (p_wip_txn_id)
4171 AND NOT ( cost_element_id = (SELECT MAX(cost_element_id)
4172 FROM wip_transaction_accounts wta1
4173 WHERE wta1.transaction_id = p_wip_txn_id
4174 AND wta1.base_transaction_value > 0)
4175 AND base_transaction_value > 0);
4176
4177 l_stmt_num := 170;
4178 UPDATE wip_transaction_accounts wta
4179 SET wta.base_transaction_value = -1*(l_wta_temp_value)
4180 WHERE transaction_id in (p_wip_txn_id)
4181 AND (base_transaction_value > 0
4182 and cost_element_id=(SELECT MAX(cost_element_id)
4183 FROM wip_transaction_accounts wta1
4184 WHERE wta1.transaction_id = p_wip_txn_id));
4185 ELSIF p_txn_type in (4,6) THEN
4186 l_stmt_num := 180;
4187 SELECT sum(base_transaction_value)
4188 INTO l_wta_temp_value
4189 FROM wip_transaction_accounts
4190 WHERE transaction_id in (p_wip_txn_id)
4191 AND base_transaction_value > 0;
4192
4193 l_stmt_num := 190;
4194 UPDATE wip_transaction_accounts wta
4195 SET base_transaction_value = -1 * (l_wta_temp_value)
4196 WHERE wta.transaction_id = p_wip_txn_id
4197 AND wta.cost_element_id is null;
4198 END IF; -- end handling different cases for different transaction types
4199
4200 END IF; -- wta total sum
4201
4202 EXCEPTION
4203 when others then
4204 -- rollback;
4205 p_err_num := SQLCODE;
4206 p_err_msg := 'CSTPSMUT.BALANCE_ACCOUNTING: ' || to_char (l_stmt_num) || ');';
4207 p_err_code := null;
4208 END BALANCE_ACCOUNTING; -- BALANCE_MTA
4209
4210 PROCEDURE INSERT_MAT_TXN( p_date IN DATE,
4211 p_sm_txn_id IN NUMBER,
4212 p_mtl_txn_id IN NUMBER,
4213 p_acct_period_id IN NUMBER,
4214 p_txn_qty IN NUMBER,
4215 p_action_id IN NUMBER,
4216 p_source_type_id IN NUMBER,
4217 p_txn_type_name IN VARCHAR2,
4218 p_wip_entity_id IN NUMBER,
4219 p_operation_seq_num IN NUMBER,
4220 p_user_id IN NUMBER,
4221 p_login_id IN NUMBER,
4222 p_request_id IN NUMBER,
4223 p_prog_appl_id IN NUMBER,
4224 p_program_id IN NUMBER,
4225 p_debug IN VARCHAR2,
4226 p_err_num IN OUT NOCOPY NUMBER,
4227 p_err_code IN OUT NOCOPY VARCHAR2,
4228 p_err_msg IN OUT NOCOPY VARCHAR2) IS
4229
4230 l_rows_inserted number;
4231 l_stmt_num number;
4232
4233
4234 BEGIN
4235
4236 l_stmt_num := 5;
4237 p_err_num := 0;
4238
4239 l_stmt_num := 10;
4240 INSERT INTO mtl_material_transactions
4241 (TRANSACTION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
4242 CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
4243 PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
4244 INVENTORY_ITEM_ID, ORGANIZATION_ID, TRANSACTION_TYPE_ID,
4245 TRANSACTION_ACTION_ID, TRANSACTION_SOURCE_TYPE_ID,
4246 TRANSACTION_SOURCE_ID, TRANSACTION_SOURCE_NAME,
4247 TRANSACTION_QUANTITY, TRANSACTION_UOM, PRIMARY_QUANTITY,
4248 TRANSACTION_DATE, ACCT_PERIOD_ID, COSTED_FLAG, OPERATION_SEQ_NUM,
4249 SOURCE_LINE_ID)
4250 SELECT
4251 p_mtl_txn_id, sysdate, p_user_id, sysdate,
4252 p_user_id, p_login_id, p_request_id,
4253 p_prog_appl_id, p_program_id, sysdate,
4254 we.primary_item_id, we.organization_id, mtt.transaction_type_id,
4255 mtt.transaction_action_id, mtt.transaction_source_type_id,
4256 p_wip_entity_id, we.wip_entity_name,
4257 p_txn_qty, msi.primary_uom_code, p_txn_qty,
4258 p_date, p_acct_period_id, null, p_operation_seq_num,
4259 p_sm_txn_id
4260 FROM
4261 mtl_transaction_types mtt,
4262 mtl_system_items msi,
4263 wip_entities we
4264 WHERE we.wip_entity_id = p_wip_entity_id
4265 AND we.primary_item_id = msi.inventory_item_id
4266 AND we.organization_id = msi.organization_id
4267 AND mtt.transaction_action_id = p_action_id
4268 AND mtt.transaction_source_type_id = p_source_type_id
4269 AND exists
4270 ( SELECT null
4271 FROM mtl_transaction_accounts
4272 WHERE transaction_id = p_mtl_txn_id);
4273
4274 l_rows_inserted := SQL%ROWCOUNT;
4275
4276 IF (l_rows_inserted > 0 ) and (p_debug = 'Y') THEN
4277 FND_FILE.put_line(FND_FILE.LOG,to_char(l_rows_inserted)
4278 || ' row(s) inserted '
4279 || 'into mtl_material_transactions.'
4280 || ', mtl_txn_id: '
4281 || p_mtl_txn_id
4282 || ', action_id: '
4283 || p_action_id
4284 || ', wip_entity_id: '
4285 || p_wip_entity_id);
4286 END IF;
4287 l_rows_inserted := 0;
4288
4289 EXCEPTION
4290 when others then
4291 p_err_num := SQLCODE;
4292 p_err_msg := 'CSTPSMUT.INSERT_MAT_TXN: '
4293 || to_char (l_stmt_num) || ');';
4294 p_err_code := null;
4295 END INSERT_MAT_TXN;
4296
4297 PROCEDURE INSERT_WIP_TXN( p_date IN DATE,
4298 p_sm_txn_id IN NUMBER,
4299 p_wip_txn_id IN NUMBER,
4300 p_acct_period_id IN NUMBER,
4301 p_wip_entity_id IN NUMBER,
4302 p_operation_seq_num IN NUMBER,
4303 p_lookup_code IN NUMBER,
4304 p_user_id IN NUMBER,
4305 p_login_id IN NUMBER,
4306 p_request_id IN NUMBER,
4307 p_prog_appl_id IN NUMBER,
4308 p_program_id IN NUMBER,
4309 p_debug IN VARCHAR2,
4310 p_err_num IN OUT NOCOPY NUMBER,
4311 p_err_code IN OUT NOCOPY VARCHAR2,
4312 p_err_msg IN OUT NOCOPY VARCHAR2,
4313 p_txn_id IN NUMBER) IS -- Added for bug#4307365
4314 l_rows_inserted number;
4315 l_stmt_num number;
4316 l_txn_uom VARCHAR2(3);
4317 l_pr_uom VARCHAR2(3);
4318
4319 BEGIN
4320 p_err_num:= 0;
4321
4322 l_stmt_num:= 10;
4323
4324 SAVEPOINT insert_wip_txn;
4325
4326 SELECT MMT.TRANSACTION_UOM, MSI.PRIMARY_UOM_CODE
4327 INTO l_txn_uom, l_pr_uom
4328 FROM MTL_SYSTEM_ITEMS MSI,
4329 MTL_MATERIAL_TRANSACTIONS MMT
4330 WHERE MMT.TRANSACTION_ID = p_txn_id
4331 AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
4332 AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID;
4333
4334 INSERT INTO wip_transactions
4335 (TRANSACTION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
4336 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
4337 REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
4338 PROGRAM_UPDATE_DATE,
4339 ORGANIZATION_ID, WIP_ENTITY_ID, ACCT_PERIOD_ID,
4340 TRANSACTION_TYPE, TRANSACTION_DATE, OPERATION_SEQ_NUM,
4341 SOURCE_LINE_ID, TRANSACTION_UOM, PRIMARY_UOM,
4342 --{BUG#7314513
4343 primary_item_id
4344 --}
4345 )
4346 SELECT
4347 p_wip_txn_id, sysdate, p_user_id,
4348 sysdate, p_user_id, p_login_id,
4349 p_request_id, p_prog_appl_id, p_program_id,
4350 sysdate,
4351 we.organization_id, we.wip_entity_id, p_acct_period_id,
4352 p_lookup_code, p_date, p_operation_seq_num,
4353 p_sm_txn_id, l_txn_uom, l_pr_uom,
4354 we.primary_item_id
4355 FROM wip_entities we
4356 WHERE we.wip_entity_id = p_wip_entity_id
4357 AND exists
4358 ( SELECT null
4359 FROM wip_transaction_accounts
4360 WHERE transaction_id = p_wip_txn_id);
4361
4362 l_rows_inserted := SQL%ROWCOUNT;
4363 IF (l_rows_inserted > 0 and p_debug = 'Y') THEN
4364 FND_FILE.put_line(FND_FILE.LOG,to_char(l_rows_inserted)
4365 || ' row(s) inserted ' ||
4366 'into wip_transactions.');
4367 END IF;
4368 l_rows_inserted := 0;
4369
4370 EXCEPTION
4371 WHEN OTHERS THEN
4372 /* Changes for Bug #1877576. Using savepoint to prevent "Fetch out of
4373 sequence error */
4374 ROLLBACK TO insert_wip_txn;
4375 p_err_num := SQLCODE;
4376 p_err_msg := 'CSTPSMUT.INSERT_WIP_TXN: (' || to_char (l_stmt_num) || ');';
4377 p_err_code := null;
4378 END INSERT_WIP_TXN;
4379
4380 PROCEDURE INSERT_MTA(
4381 p_date IN DATE,
4382 p_min_acct_unit IN NUMBER,
4383 p_ext_prec IN NUMBER,
4384 p_sm_txn_type IN NUMBER,
4385 p_mtl_txn_id IN NUMBER,
4386 p_org_id IN NUMBER,
4387 p_wip_id IN NUMBER,
4388 p_acct_ltype IN NUMBER,
4389 p_txn_qty IN NUMBER,
4390 p_tl_mtl_cost IN NUMBER,
4391 p_tl_mto_cost IN NUMBER,
4392 p_tl_res_cost IN NUMBER,
4393 p_tl_ovh_cost IN NUMBER,
4394 p_tl_osp_cost IN NUMBER,
4395 p_cost_element_id IN NUMBER,
4396 p_user_id IN NUMBER,
4397 p_login_id IN NUMBER,
4398 p_request_id IN NUMBER,
4399 p_prog_appl_id IN NUMBER,
4400 p_program_id IN NUMBER,
4401 p_debug IN VARCHAR2,
4402 p_err_num IN OUT NOCOPY NUMBER,
4403 p_err_code IN OUT NOCOPY VARCHAR2,
4404 p_err_msg IN OUT NOCOPY VARCHAR2) is
4405
4406 l_rows_inserted number := 0;
4407 l_stmt_num number;
4408
4409 BEGIN
4410
4411 l_stmt_num := 10;
4412
4413
4414 INSERT into mtl_transaction_accounts (
4415 TRANSACTION_ID,
4416 REFERENCE_ACCOUNT,
4417 LAST_UPDATE_DATE,
4418 LAST_UPDATED_BY,
4419 CREATION_DATE,
4420 CREATED_BY,
4421 LAST_UPDATE_LOGIN,
4422 REQUEST_ID,
4423 PROGRAM_APPLICATION_ID,
4424 PROGRAM_ID,
4425 PROGRAM_UPDATE_DATE,
4426 INVENTORY_ITEM_ID,
4427 ORGANIZATION_ID,
4428 TRANSACTION_DATE,
4429 TRANSACTION_SOURCE_ID,
4430 TRANSACTION_SOURCE_TYPE_ID,
4431 COST_ELEMENT_ID,
4432 ACCOUNTING_LINE_TYPE,
4433 CONTRA_SET_ID,
4434 BASE_TRANSACTION_VALUE,
4435 PRIMARY_QUANTITY)
4436 SELECT
4437 p_mtl_txn_id,
4438 DECODE(p_cost_element_id,
4439 1,dj.material_account,
4440 2,dj.material_overhead_account,
4441 3,dj.resource_account,
4442 4,dj.outside_processing_account,
4443 5,dj.overhead_account),
4444 sysdate,
4445 p_user_id, sysdate, p_user_id,
4446 p_login_id, p_request_id, p_prog_appl_id,
4447 p_program_id, sysdate, mmt.inventory_item_id,
4448 p_org_id, p_date, p_wip_id,
4449 5, p_cost_element_id, p_acct_ltype, 1,
4450 ROUND(DECODE(p_cost_element_id,
4451 1, p_tl_mtl_cost,
4452 2, p_tl_mto_cost,
4453 3, p_tl_res_cost,
4454 4, p_tl_osp_cost,
4455 5, p_tl_ovh_cost)/p_min_acct_unit) * p_min_acct_unit,
4456 p_txn_qty
4457 FROM wip_discrete_jobs dj,
4458 mtl_material_transactions mmt
4459 WHERE dj.wip_entity_id = p_wip_id
4460 AND mmt.transaction_id = p_mtl_txn_id
4461 HAVING ROUND(DECODE(p_cost_element_id,
4462 1, p_tl_mtl_cost,
4463 2, p_tl_mto_cost,
4464 3, p_tl_res_cost,
4465 4, p_tl_osp_cost,
4466 5, p_tl_ovh_cost)/p_min_acct_unit) * p_min_acct_unit <> 0;
4467
4468 l_rows_inserted := l_rows_inserted + SQL%ROWCOUNT;
4469
4470 /* R12 - Sub Ledger Unique Identifier */
4471 UPDATE MTL_TRANSACTION_ACCOUNTS
4472 SET INV_SUB_LEDGER_ID = CST_INV_SUB_LEDGER_ID_S.NEXTVAL
4473 WHERE TRANSACTION_ID = p_mtl_txn_id;
4474
4475 IF (l_rows_inserted > 0) and (p_debug = 'Y')THEN
4476 FND_FILE.PUT_LINE(FND_FILE.LOG,to_char(l_rows_inserted)
4477 || ' row(s) inserted '
4478 || 'into mtl_transaction_accounts.'
4479 || ',mtl_txn_id: '
4480 || p_mtl_txn_id
4481 || ', cost element id: '
4482 || p_cost_element_id
4483 );
4484 END IF;
4485
4486
4487 EXCEPTION
4488 WHEN OTHERS THEN
4489 p_err_num := SQLCODE;
4490 p_err_msg := 'CSTPSMUT.INSERT_MAT: ' || to_char (l_stmt_num) || ');';
4491 p_err_code := null;
4492
4493 END INSERT_MTA;
4494
4495 PROCEDURE INSERT_MAT_TXN_ACCT(p_date IN DATE,
4496 p_min_acct_unit IN NUMBER,
4497 p_ext_prec IN NUMBER,
4498 p_sm_txn_type IN NUMBER,
4499 p_mtl_txn_id IN NUMBER,
4500 p_org_id IN NUMBER,
4501 p_wip_id IN NUMBER,
4502 p_acct_ltype IN NUMBER,
4503 p_txn_qty IN NUMBER,
4504 p_tl_mtl_cost IN NUMBER,
4505 p_tl_mto_cost IN NUMBER,
4506 p_tl_res_cost IN NUMBER,
4507 p_tl_ovh_cost IN NUMBER,
4508 p_tl_osp_cost IN NUMBER,
4509 p_user_id IN NUMBER,
4510 p_login_id IN NUMBER,
4511 p_request_id IN NUMBER,
4512 p_prog_appl_id IN NUMBER,
4513 p_program_id IN NUMBER,
4514 p_debug IN VARCHAR2,
4515 p_err_num IN OUT NOCOPY NUMBER,
4516 p_err_code IN OUT NOCOPY VARCHAR2,
4517 p_err_msg IN OUT NOCOPY VARCHAR2) IS
4518 l_stmt_num number;
4519 l_acct_summary number;
4520 l_mta_row number;
4521
4522 CURSOR c_elements IS
4523 SELECT cost_element_id
4524 FROM cst_cost_elements;
4525 BEGIN
4526
4527
4528 l_stmt_num := 5;
4529 p_err_num := 0;
4530
4531
4532 l_stmt_num := 10;
4533 FOR c1 IN c_elements LOOP
4534
4535 INSERT_MTA(p_date,
4536 p_min_acct_unit,
4537 p_ext_prec,
4538 p_sm_txn_type,
4539 p_mtl_txn_id,
4540 p_org_id,
4541 p_wip_id,
4542 p_acct_ltype,
4543 p_txn_qty,
4544 p_tl_mtl_cost,
4545 p_tl_mto_cost,
4546 p_tl_res_cost,
4547 p_tl_ovh_cost,
4548 p_tl_osp_cost,
4549 c1.cost_element_id,
4550 p_user_id,
4551 p_login_id,
4552 p_request_id,
4553 p_prog_appl_id,
4554 p_program_id,
4555 p_debug,
4556 p_err_num,
4557 p_err_code,
4558 p_err_msg);
4559
4560 END LOOP;
4561
4562 EXCEPTION
4563 WHEN OTHERS THEN
4564 --rollback;
4565 p_err_num := SQLCODE;
4566 p_err_msg := 'CSTPSMUT.INSERT_MAT_TXN_ACCT: ' || to_char (l_stmt_num) || ');';
4567 p_err_code := null;
4568
4569 END INSERT_MAT_TXN_ACCT;
4570
4571 PROCEDURE INSERT_WIP_TXN_ACCT (p_date IN DATE,
4572 p_min_acct_unit IN NUMBER,
4573 p_ext_prec IN NUMBER,
4574 p_sm_txn_id IN NUMBER,
4575 p_sm_txn_type IN NUMBER,
4576 p_wip_txn_id IN NUMBER,
4577 p_org_id IN NUMBER,
4578 p_wip_id IN NUMBER,
4579 p_acct_ltype IN NUMBER,
4580 p_txn_qty IN NUMBER,
4581 p_pl_mtl_cost IN NUMBER,
4582 p_pl_mto_cost IN NUMBER,
4583 p_pl_res_cost IN NUMBER,
4584 p_pl_ovh_cost IN NUMBER,
4585 p_pl_osp_cost IN NUMBER,
4586 p_user_id IN NUMBER,
4587 p_login_id IN NUMBER,
4588 p_request_id IN NUMBER,
4589 p_prog_appl_id IN NUMBER,
4590 p_program_id IN NUMBER,
4591 p_debug IN VARCHAR2,
4592 p_err_num IN OUT NOCOPY NUMBER,
4593 p_err_code IN OUT NOCOPY VARCHAR2,
4594 p_err_msg IN OUT NOCOPY VARCHAR2) IS
4595 l_rows_inserted number := 0;
4596 l_stmt_num number;
4597 CURSOR c_elements IS
4598 SELECT cost_element_id
4599 FROM cst_cost_elements;
4600 BEGIN
4601 p_err_num := 0;
4602 l_stmt_num := 10;
4603
4604 IF (p_debug = 'Y') THEN
4605 FND_FILE.put_line(FND_FILE.LOG,'CSTPSMUT.INSERT_WIP_TXN_ACCT: wip_txn_id: '|| p_wip_txn_id);
4606 END IF;
4607
4608 FOR c1 IN c_elements LOOP
4609 INSERT INTO WIP_TRANSACTION_ACCOUNTS
4610 (
4611 TRANSACTION_ID, REFERENCE_ACCOUNT, LAST_UPDATE_DATE,
4612 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
4613 LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
4614 PROGRAM_ID, PROGRAM_UPDATE_DATE, ORGANIZATION_ID,
4615 TRANSACTION_DATE, WIP_ENTITY_ID, ACCOUNTING_LINE_TYPE,
4616 BASE_TRANSACTION_VALUE, COST_ELEMENT_ID,
4617 PRIMARY_QUANTITY
4618 )
4619 SELECT
4620 p_wip_txn_id,
4621 DECODE(c1.cost_element_id,
4622 1,dj.material_account,
4623 2,dj.material_overhead_account,
4624 3,dj.resource_account,
4625 4,dj.outside_processing_account,
4626 5,dj.overhead_account),
4627 sysdate,
4628 p_user_id, sysdate, p_user_id,
4629 p_login_id, p_request_id, p_prog_appl_id, p_program_id,
4630 sysdate, p_org_id, p_date,
4631 p_wip_id, p_acct_ltype,
4632 ROUND(DECODE(c1.cost_element_id,
4633 1,p_pl_mtl_cost,
4634 2,p_pl_mto_cost,
4635 3,p_pl_res_cost,
4636 4,p_pl_osp_cost,
4637 5,p_pl_ovh_cost)/p_min_acct_unit)*p_min_acct_unit,
4638 c1.cost_element_id,
4639 p_txn_qty
4640 FROM wip_discrete_jobs dj
4641 WHERE dj.wip_entity_id = p_wip_id
4642 HAVING ROUND(DECODE(c1.cost_element_id,
4643 1,p_pl_mtl_cost,
4644 2,p_pl_mto_cost,
4645 3,p_pl_res_cost,
4646 4,p_pl_osp_cost,
4647 5,p_pl_ovh_cost)/p_min_acct_unit)*p_min_acct_unit <> 0;
4648
4649 UPDATE WIP_TRANSACTION_ACCOUNTS
4650 SET WIP_SUB_LEDGER_ID = CST_WIP_SUB_LEDGER_ID_S.NEXTVAL
4651 WHERE TRANSACTION_ID = p_wip_txn_id;
4652 l_rows_inserted := l_rows_inserted + SQL%ROWCOUNT;
4653 END LOOP;
4654
4655 IF (l_rows_inserted > 0) and (p_debug = 'Y') THEN
4656 FND_FILE.put_line(FND_FILE.LOG,to_char(l_rows_inserted)
4657 || 'row(s) inserted '
4658 || 'into wip_transaction_accounts.'
4659 || ', wip_entity_id: '
4660 || p_wip_id);
4661 END IF;
4662
4663
4664 EXCEPTION
4665 when others then
4666 --rollback;
4667 p_err_num := SQLCODE;
4668 p_err_msg := 'CSTPSMUT.INSERT_WIP_TXN_ACCT: '
4669 || to_char (l_stmt_num)
4670 || '); ,'
4671 || to_char(p_err_num);
4672 p_err_code := null;
4673 IF ( p_debug = 'Y' ) THEN
4674 fnd_file.put_line(fnd_file.log, 'Insert into MTA Failed: '||p_err_msg || substr(SQLERRM, 1, 250));
4675 END IF;
4676 END INSERT_WIP_TXN_ACCT;
4677
4678 PROCEDURE BONUS_MAT_TXN_ACCT(p_date IN DATE,
4679 p_ext_prec IN NUMBER,
4680 p_min_acct_unit IN NUMBER,
4681 p_sm_txn_type IN NUMBER,
4682 p_sm_txn_id IN NUMBER,
4683 p_mtl_txn_id IN NUMBER,
4684 p_org_id IN NUMBER,
4685 p_wip_id IN NUMBER,
4686 p_acct_ltype IN NUMBER,
4687 p_total_cost IN NUMBER,
4688 p_user_id IN NUMBER,
4689 p_login_id IN NUMBER,
4690 p_request_id IN NUMBER,
4691 p_prog_appl_id IN NUMBER,
4692 p_program_id IN NUMBER,
4693 p_debug IN VARCHAR2,
4694 p_err_num IN OUT NOCOPY NUMBER,
4695 p_err_code IN OUT NOCOPY VARCHAR2,
4696 p_err_msg IN OUT NOCOPY VARCHAR2) IS
4697 l_rows_inserted NUMBER;
4698 l_stmt_num NUMBER;
4699
4700 BEGIN
4701 l_stmt_num := 10;
4702 p_err_num := 0;
4703
4704
4705 INSERT into mtl_transaction_accounts
4706 (
4707 TRANSACTION_ID, REFERENCE_ACCOUNT, LAST_UPDATE_DATE,
4708 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
4709 LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
4710 PROGRAM_ID, PROGRAM_UPDATE_DATE, INVENTORY_ITEM_ID,
4711 ORGANIZATION_ID, TRANSACTION_DATE, TRANSACTION_SOURCE_ID,
4712 TRANSACTION_SOURCE_TYPE_ID, COST_ELEMENT_ID,
4713 ACCOUNTING_LINE_TYPE, CONTRA_SET_ID, BASE_TRANSACTION_VALUE)
4714 SELECT
4715 p_mtl_txn_id,
4716 bonus_acct_id,
4717 sysdate,
4718 p_user_id, sysdate, p_user_id,
4719 p_login_id, p_request_id, p_prog_appl_id,
4720 p_program_id, sysdate, primary_item_id,
4721 p_org_id, p_date, p_wip_id,
4722 5, NULL, p_acct_ltype, 1,
4723 ROUND(p_total_cost/p_min_acct_unit)*p_min_acct_unit
4724 FROM wsm_sm_resulting_jobs
4725 WHERE transaction_id = p_sm_txn_id
4726 HAVING ROUND(p_total_cost/p_min_acct_unit)*p_min_acct_unit <> 0;
4727 l_rows_inserted := SQL%ROWCOUNT;
4728
4729 UPDATE MTL_TRANSACTION_ACCOUNTS
4730 SET INV_SUB_LEDGER_ID = CST_INV_SUB_LEDGER_ID_S.NEXTVAL
4731 WHERE TRANSACTION_ID = p_mtl_txn_id;
4732
4733 IF (l_rows_inserted > 0 and p_debug = 'Y') THEN
4734 FND_FILE.put_line(FND_FILE.LOG,to_char(l_rows_inserted)
4735 || ' row(s) inserted '
4736 || 'into mtl_transaction_accounts '
4737 || ', mtl_txn_id: '
4738 || p_mtl_txn_id);
4739 END IF;
4740
4741 EXCEPTION
4742 when others then
4743 --rollback;
4744 p_err_num := SQLCODE;
4745 p_err_msg := 'CSTPSMUT.BONUS_MAT_TXN_ACCT: ' || to_char (l_stmt_num) || ');';
4746 p_err_code := null;
4747
4748 END BONUS_MAT_TXN_ACCT;
4749
4750 PROCEDURE BONUS_WIP_TXN_ACCT(p_date IN DATE,
4751 p_ext_prec IN NUMBER,
4752 p_min_acct_unit IN NUMBER,
4753 p_sm_txn_id IN NUMBER,
4754 p_sm_txn_type IN NUMBER,
4755 p_wip_txn_id IN NUMBER,
4756 p_org_id IN NUMBER,
4757 p_wip_id IN NUMBER,
4758 p_acct_ltype IN NUMBER,
4759 p_total_cost IN NUMBER,
4760 p_user_id IN NUMBER,
4761 p_login_id IN NUMBER,
4762 p_request_id IN NUMBER,
4763 p_prog_appl_id IN NUMBER,
4764 p_program_id IN NUMBER,
4765 p_debug IN VARCHAR2,
4766 p_err_num IN OUT NOCOPY NUMBER,
4767 p_err_code IN OUT NOCOPY VARCHAR2,
4768 p_err_msg IN OUT NOCOPY VARCHAR2) IS
4769
4770 l_rows_inserted NUMBER;
4771 l_stmt_num NUMBER;
4772
4773 BEGIN
4774 l_stmt_num := 10;
4775 p_err_num := 0;
4776
4777
4778 INSERT INTO WIP_TRANSACTION_ACCOUNTS
4779 (
4780 TRANSACTION_ID, REFERENCE_ACCOUNT, LAST_UPDATE_DATE,
4781 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
4782 LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
4783 PROGRAM_ID, PROGRAM_UPDATE_DATE, ORGANIZATION_ID,
4784 TRANSACTION_DATE, WIP_ENTITY_ID, ACCOUNTING_LINE_TYPE,
4785 BASE_TRANSACTION_VALUE
4786 )
4787 SELECT p_wip_txn_id, res.bonus_acct_id, sysdate,
4788 p_user_id, sysdate, p_user_id,
4789 p_login_id, p_request_id, p_prog_appl_id, p_program_id,
4790 sysdate, p_org_id, p_date,
4791 p_wip_id, p_acct_ltype,
4792 NVL(ROUND(p_total_cost/p_min_acct_unit)*p_min_acct_unit,0)
4793 FROM wsm_sm_resulting_jobs res
4794 WHERE res.transaction_id = p_sm_txn_id
4795 HAVING NVL(ROUND(p_total_cost/p_min_acct_unit)*p_min_acct_unit,0) <> 0;
4796
4797 UPDATE WIP_TRANSACTION_ACCOUNTS
4798 SET WIP_SUB_LEDGER_ID = CST_WIP_SUB_LEDGER_ID_S.NEXTVAL
4799 WHERE TRANSACTION_ID = p_wip_txn_id;
4800 l_rows_inserted := SQL%ROWCOUNT;
4801 IF (l_rows_inserted > 0 and p_debug = 'Y') THEN
4802 FND_FILE.put_line(FND_FILE.LOG,to_char(l_rows_inserted) || 'row(s) inserted ' ||
4803 'into wip_transaction_accounts.');
4804 END IF;
4805 END BONUS_WIP_TXN_ACCT;
4806
4807
4808
4809 PROCEDURE START_LOT (
4810 p_sl_mtl_txn_id IN NUMBER,
4811 p_sl_wip_txn_id IN NUMBER,
4812 p_sl_wip_id IN NUMBER,
4813 p_acct_period_id IN NUMBER,
4814 p_user_id IN NUMBER,
4815 p_login_id IN NUMBER,
4816 p_request_id IN NUMBER,
4817 p_prog_appl_id IN NUMBER,
4818 p_program_id IN NUMBER,
4819 p_err_num in OUT NOCOPY number,
4820 p_err_code in OUT NOCOPY varchar2,
4821 p_err_msg in OUT NOCOPY varchar2) IS
4822 l_rows_inserted number := 0;
4823 l_stmt_num number;
4824 BEGIN
4825 p_err_num := 0;
4826 l_stmt_num := 10;
4827 UPDATE wip_period_balances wpb
4828 SET ( request_id, program_application_id, program_id,
4829 program_update_date, last_update_date, last_updated_by,
4830 last_update_login, pl_material_out, pl_material_overhead_out,
4831 pl_resource_out, pl_outside_processing_out,
4832 pl_overhead_out) = (
4833 SELECT p_request_id, p_prog_appl_id, p_program_id,
4834 sysdate, sysdate, p_user_id,
4835 p_login_id,
4836 nvl(wpb.pl_material_out, 0) +
4837 nvl(SUM(DECODE(mta.cost_element_id,1,
4838 -mta.base_transaction_value, 0)), 0),
4839 nvl(wpb.pl_material_overhead_out, 0) +
4840 nvl(SUM(DECODE(mta.cost_element_id,2,
4841 -mta.base_transaction_value, 0)), 0),
4842 nvl(wpb.pl_resource_out, 0) +
4843 nvl(SUM(DECODE(mta.cost_element_id,3,
4844 -mta.base_transaction_value, 0)), 0),
4845 nvl(wpb.pl_outside_processing_out, 0) +
4846 nvl(SUM(DECODE(mta.cost_element_id,4,
4847 -mta.base_transaction_value, 0)), 0),
4848 nvl(wpb.pl_overhead_out, 0) +
4849 nvl(SUM(DECODE(mta.cost_element_id,5,
4850 -mta.base_transaction_value, 0)), 0)
4851 FROM mtl_transaction_accounts mta
4852 WHERE mta.transaction_id = p_sl_mtl_txn_id
4853 AND mta.transaction_source_id = p_sl_wip_id)
4854 -- AND mta.accounting_line_type <> 31)
4855 WHERE wpb.wip_entity_id = p_sl_wip_id
4856 AND wpb.acct_period_id = p_acct_period_id
4857 AND exists
4858 ( SELECT null
4859 FROM mtl_transaction_accounts
4860 WHERE transaction_id = p_sl_mtl_txn_id);
4861
4862 l_rows_inserted := SQL%ROWCOUNT;
4863 -- dbms_output.put_line(to_char(l_rows_inserted) || 'row(s) updated ' ||
4864 -- 'in wip_period_balances for starting lot from mtl txn acct');
4865 l_rows_inserted := 0;
4866
4867 l_stmt_num := 20;
4868 UPDATE wip_period_balances wpb
4869 SET ( request_id, program_application_id, program_id,
4870 program_update_date, last_update_date, last_updated_by,
4871 last_update_login, tl_material_out, tl_material_overhead_out,
4872 tl_resource_out, tl_outside_processing_out,
4873 tl_overhead_out ) =
4874 ( SELECT p_request_id, p_prog_appl_id, p_program_id,
4875 sysdate, sysdate, p_user_id,
4876 p_login_id,
4877 nvl(wpb.tl_material_out, 0) +
4878 nvl(SUM(DECODE(wta.cost_element_id,1,
4879 -wta.base_transaction_value, 0)), 0),
4880 nvl(wpb.tl_material_overhead_out, 0) +
4881 nvl(SUM(DECODE(wta.cost_element_id,2,
4882 -wta.base_transaction_value, 0)), 0),
4883 nvl(wpb.tl_resource_out, 0) +
4884 nvl(SUM(DECODE(wta.cost_element_id,3,
4885 -wta.base_transaction_value, 0)), 0),
4886 nvl(wpb.tl_outside_processing_out, 0) +
4887 nvl(SUM(DECODE(wta.cost_element_id,4,
4888 -wta.base_transaction_value, 0)), 0),
4889 nvl(wpb.tl_overhead_out, 0) +
4890 nvl(SUM(DECODE(wta.cost_element_id,5,
4891 -wta.base_transaction_value, 0)), 0)
4892 FROM wip_transaction_accounts wta
4893 WHERE wta.transaction_id = p_sl_wip_txn_id
4894 AND wta.wip_entity_id = p_sl_wip_id)
4895 -- AND wta.accounting_line_type <> 31)
4896 WHERE wpb.wip_entity_id = p_sl_wip_id
4897 AND wpb.acct_period_id = p_acct_period_id
4898 AND exists
4899 ( SELECT null
4900 FROM wip_transaction_accounts
4901 WHERE transaction_id = p_sl_wip_txn_id);
4902
4903 l_rows_inserted := SQL%ROWCOUNT;
4904 -- dbms_output.put_line(to_char(l_rows_inserted) || 'row(s) updated ' ||
4905 -- 'in wip_period_balances for starting lot from wip txn acct.');
4906 l_rows_inserted := 0;
4907
4908 EXCEPTION
4909 when others then
4910 --rollback;
4911 p_err_code:= null;
4912 p_err_num := SQLCODE;
4913 p_err_msg := 'CSTPSMUT: START_LOT- '||l_stmt_num||'.'||SQLERRM;
4914
4915
4916 END START_LOT;
4917 /*------------------------------------------------------------------
4918 Procedure: Result_lot
4919
4920 This procedure updates WPB of the resulting lots. It handles
4921 the *_in of the WPB, not *_outs.
4922
4923 -------------------------------------------------------------------*/
4924
4925 PROCEDURE RESULT_LOT(
4926 p_rl_mtl_txn_id IN NUMBER,
4927 p_rl_wip_txn_id IN NUMBER,
4928 p_rl_wip_id IN NUMBER,
4929 p_acct_period_id IN NUMBER,
4930 p_user_id IN NUMBER,
4931 p_login_id IN NUMBER,
4932 p_request_id IN NUMBER,
4933 p_prog_appl_id IN NUMBER,
4934 p_program_id IN NUMBER,
4935 p_debug IN VARCHAR2,
4936 p_err_num in OUT NOCOPY number,
4937 p_err_code in OUT NOCOPY varchar2,
4938 p_err_msg in OUT NOCOPY varchar2) IS
4939 l_rows_inserted number;
4940 l_stmt_num number;
4941
4942 BEGIN
4943
4944 --
4945 -- Update resulting lot period balances
4946 --
4947 p_err_num := 0;
4948 l_stmt_num := 5;
4949
4950 l_stmt_num := 10;
4951
4952 UPDATE wip_period_balances wpb
4953 SET ( request_id, program_application_id, program_id,
4954 program_update_date, last_update_date, last_updated_by,
4955 last_update_login, pl_material_in, pl_material_overhead_in,
4956 pl_resource_in, pl_outside_processing_in,
4957 pl_overhead_in) = (
4958 SELECT p_request_id, p_prog_appl_id, p_program_id,
4959 sysdate, sysdate, p_user_id,
4960 p_login_id,
4961 nvl(wpb.pl_material_in, 0) +
4962 nvl(SUM(DECODE(mta.cost_element_id,1,
4963 mta.base_transaction_value, 0)), 0),
4964 nvl(wpb.pl_material_overhead_in, 0) +
4965 nvl(SUM(DECODE(mta.cost_element_id,2,
4966 mta.base_transaction_value, 0)), 0),
4967 nvl(wpb.pl_resource_in, 0) +
4968 nvl(SUM(DECODE(mta.cost_element_id,3,
4969 mta.base_transaction_value, 0)), 0),
4970 nvl(wpb.pl_outside_processing_in, 0) +
4971 nvl(SUM(DECODE(mta.cost_element_id,4,
4972 mta.base_transaction_value, 0)), 0),
4973 nvl(wpb.pl_overhead_in, 0) +
4974 nvl(SUM(DECODE(mta.cost_element_id,5,
4975 mta.base_transaction_value, 0)), 0)
4976 FROM mtl_transaction_accounts mta
4977 WHERE mta.transaction_id = p_rl_mtl_txn_id
4978 AND mta.transaction_source_id = p_rl_wip_id)
4979 WHERE wpb.wip_entity_id = p_rl_wip_id
4980 AND wpb.acct_period_id = p_acct_period_id
4981 AND exists
4982 ( SELECT null
4983 FROM mtl_transaction_accounts
4984 WHERE transaction_id = p_rl_mtl_txn_id);
4985
4986 l_rows_inserted := SQL%ROWCOUNT;
4987 IF (p_debug = 'Y') and (l_rows_inserted > 0) THEN
4988 FND_FILE.put_line(FND_FILE.LOG,to_char(l_rows_inserted)
4989 || ' row(s) updated '
4990 || 'in wip_period_balances for mtl txn acct.');
4991 END IF;
4992 l_rows_inserted := 0;
4993
4994 l_stmt_num := 20;
4995 UPDATE wip_period_balances wpb
4996 SET ( request_id, program_application_id, program_id,
4997 program_update_date, last_update_date, last_updated_by,
4998 last_update_login,
4999 tl_resource_in, tl_outside_processing_in,
5000 tl_overhead_in ) = (
5001 SELECT p_request_id, p_prog_appl_id, p_program_id,
5002 sysdate, sysdate, p_user_id,
5003 p_login_id,
5004 nvl(wpb.tl_resource_in, 0) +
5005 nvl(SUM(DECODE(wta.cost_element_id,3,
5006 wta.base_transaction_value, 0)), 0),
5007 nvl(wpb.tl_outside_processing_in, 0) +
5008 nvl(SUM(DECODE(wta.cost_element_id,4,
5009 wta.base_transaction_value, 0)), 0),
5010 nvl(wpb.tl_overhead_in, 0) +
5011 nvl(SUM(DECODE(wta.cost_element_id,5,
5012 wta.base_transaction_value, 0)), 0)
5013 FROM wip_transaction_accounts wta
5014 WHERE wta.transaction_id = p_rl_wip_txn_id
5015 AND wta.wip_entity_id = p_rl_wip_id)
5016 -- AND wta.accounting_line_type <> 31)
5017 WHERE wpb.wip_entity_id = p_rl_wip_id
5018 AND wpb.acct_period_id = p_acct_period_id
5019 AND exists
5020 ( SELECT null
5021 FROM wip_transaction_accounts
5022 WHERE transaction_id = p_rl_wip_txn_id);
5023
5024 l_rows_inserted := SQL%ROWCOUNT;
5025
5026 IF (p_debug = 'Y') and (l_rows_inserted > 0 ) THEN
5027 FND_FILE.put_line(FND_FILE.log,to_char(l_rows_inserted)
5028 || ' row(s) updated '
5029 || 'in wip_period_balances for wip txn acct.');
5030 END IF;
5031 l_rows_inserted := 0;
5032 EXCEPTION
5033 when others then
5034 --rollback;
5035 p_err_code:= null;
5036 p_err_num := SQLCODE;
5037 p_err_msg := 'CSTPSMUT: RESULT_LOT- '||l_stmt_num||'.'||SQLERRM;
5038
5039
5040 END RESULT_LOT;
5041
5042 END CSTPSMUT;