[Home] [Help]
PACKAGE BODY: APPS.CSTPOYLD
Source
1 PACKAGE BODY CSTPOYLD AS
2 /* $Header: CSTOYLDB.pls 120.10 2006/08/28 05:49:29 rajagraw noship $ */
3
4 ----------------------------------------------------------------------------
5 -- PROCEDURE --
6 -- process_op_yield --
7 -- --
8 -- DESCRIPTION --
9 -- Use this procedure to calculate operation yield for lot based jobs. --
10 -- --
11 -- PURPOSE: --
12 -- Oracle Applications Rel 11i.1 --
13 -- --
14 -- PARAMETERS: --
15 -- i_entity_id : Wip entity id of lot based job --
16 -- i_run_option : 1 if it is called from standard cost --
17 -- processor and split merge cost processor --
18 -- for txn type Split or Merge --
19 -- 2 if is called from standard cost update --
20 -- 3 if it is called from split merge cost --
21 -- processor with txn type bonus and update --
22 -- quanitty. --
23 -- i_txn_op_seq_num :Operation sequence number for bonus and --
24 -- update quantity txn number. --
25 -- i_range_option : 1 if it is to run for an organization --
26 -- 2 if it is to run for a WIP entity --
27 -- --
28 -- HISTORY: --
29 -- 03/02/00 Sujit Dalai Created --
30 ----------------------------------------------------------------------------
31
32 PROCEDURE process_op_yield(ERRBUF OUT NOCOPY VARCHAR2,
33 RETCODE OUT NOCOPY VARCHAR2,
34 i_range_option NUMBER,
35 i_entity_id NUMBER,
36 i_run_option NUMBER,
37 i_txn_op_seq_num NUMBER,
38 i_organization_id NUMBER,
39 i_sm_txn_id NUMBER) IS
40
41
42 x_err_num NUMBER;
43 x_err_msg VARCHAR2(200);
44 x_statement NUMBER := 0;
45 x_status boolean;
46 x_organization_id NUMBER := 0;
47 x_acct_period_id NUMBER := 0;
48 x_starting_opseq NUMBER := 0;
49 x_first_operation BOOLEAN;
50 x_op_unit_cost NUMBER := 0;
51 x_unit_cost NUMBER := 0;
52 x_est_scrap_per_unit NUMBER := 0;
53 x_net_absorption NUMBER := 0;
54 x_cum_pr_est_scp_per_unit NUMBER := 0;
55 x_abs_account NUMBER;
56 x_net_reversal NUMBER := 0;
57 x_transaction_id NUMBER := 0;
58 x_tl_scrap_in NUMBER := 0;
59 x_tl_scrap_out NUMBER := 0;
60 x_currency_code VARCHAR2(15);
61 x_precision NUMBER := 0;
62 x_ext_precision NUMBER := 0;
63 x_min_acct_unit NUMBER := 0;
64 x_last_updated_by NUMBER(15);
65 x_last_update_login NUMBER(15);
66 x_request_id NUMBER(15);
67 x_program_application_id NUMBER(15);
68 x_program_id NUMBER(15);
69 x_sysdate DATE;
70 x_save_point VARCHAR2(30);
71 x_count NUMBER;
72 x_last_opseq_num NUMBER;
73 x_wsm_enabled_flag VARCHAR2(1);
74 ACCOUNTS_NOT_DEFINED EXCEPTION;
75 l_debug VARCHAR2(80);
76 l_scrap_acct NUMBER;
77 l_scrap_rev_acct NUMBER;
78 l_est_scrap_abs_acct NUMBER;
79 temp_wip_entity_id NUMBER := 0;
80 l_uom VARCHAR2(3);
81 l_legal_entity_date DATE;
82 l_operating_unit NUMBER;
83 l_transaction_date DATE; /* Bug 4757384 */
84
85 /* SLA Event Seeding */
86 l_return_status VARCHAR2(1);
87 l_msg_count NUMBER;
88 l_msg_data VARCHAR2(2000);
89 l_trx_info CST_XLA_PVT.t_xla_wip_trx_info;
90 l_inv_trx_info CST_XLA_PVT.t_xla_inv_trx_info;
91
92 /* Changes for Optional Scrap */
93 x_est_scrap_acct_flag NUMBER := 0;
94 WSM_ESA_PKG_ERROR EXCEPTION;
95 EST_ACCT_NOT_FOUND EXCEPTION;
96
97 CURSOR c_wip_entity IS
98 SELECT woy.wip_entity_id wip_entity_id,
99 woy.organization_id organization_id,
100 MIN(woy.operation_seq_num) starting_op_seq,
101 WDJ.EST_SCRAP_ACCOUNT est_scrap_account,
102 WDJ.EST_SCRAP_VAR_ACCOUNT est_scrap_var_account,
103 WDJ.PRIMARY_ITEM_ID primary_item_id
104 FROM wip_operation_yields woy, wip_discrete_jobs wdj
105 WHERE woy.status IN (1, 3)
106 AND woy.wip_entity_id = DECODE(NVL(i_entity_id, 0), 0, woy.wip_entity_id, i_entity_id)
107 AND woy.organization_id = DECODE(NVL(i_organization_id, 0), 0, woy.organization_id, i_organization_id)
108 AND WDJ.WIP_ENTITY_ID = WOY.WIP_ENTITY_ID
109 AND WDJ.ORGANIZATION_ID = WOY.ORGANIZATION_ID
110 AND WDJ.STATUS_TYPE IN ( 3,4,5,6,7,15 )
111 GROUP BY woy.wip_entity_id, woy.organization_id,
112 wdj.est_scrap_account, wdj.est_scrap_var_account,
113 wdj.primary_item_id
114 ORDER BY woy.wip_entity_id;
115
116
117 /* Bug #1554288, the check for wo.quantity_completed > 0 has been commented,
118 so that Operation Yield processor is able to reverse scrap absorption for
119 undo transactions. This fix however, causes a cumulative value to be
120 inserted against the last operation_seq_num in wip_operation_yields table.
121 Please note that it does not cause any valuation mismatch, and is not
122 a cause for concern, until now */
123
124 CURSOR c_opseq(p_entity_id NUMBER, p_starting_opseq NUMBER, p_organization_id NUMBER) IS
125 SELECT WOY.OPERATION_SEQ_NUM,
126 NVL(WOY.OPERATION_COST, 0) OPERATION_COST,
127 NVL(WOY.OPERATION_UNIT_COST, 0) OPERATION_UNIT_COST,
128 NVL(WOY.CUM_OPERATION_UNIT_COST, 0) CUM_OPERATION_UNIT_COST ,
129 NVL(WOY.EST_SCRAP_UNIT_COST, 0) EST_SCRAP_UNIT_COST,
130 NVL(WOY.CUM_EST_PRIOR_UNIT_COST, 0) CUM_EST_PRIOR_UNIT_COST,
131 NVL(WOY.EST_SCRAP_QTY_COMPLETED, 0) EST_SCRAP_QTY_COMPLETED,
132 NVL(WOY.EST_SCRAP_QTY_SCRAPED, 0) EST_SCRAP_QTY_SCRAPED,
133 WOY.SCRAP_ACCOUNT,
134 WOY.EST_SCRAP_ABSORB_ACCOUNT,
135 WOY.STATUS,
136 NVL(WO.WSM_COSTED_QUANTITY_COMPLETED, NVL(WO.QUANTITY_COMPLETED, 0)) QUANTITY_COMPLETED,
137 NVL(WO.QUANTITY_SCRAPPED, 0) QUANTITY_SCRAPPED,
138 DECODE (WO.OPERATION_YIELD_ENABLED, 1, NVL(WO.OPERATION_YIELD, 1),
139 1) OPERATION_YIELD,
140 NVL(WO.DEPARTMENT_ID, 0) DEPARTMENT_ID,
141 WO.DISABLE_DATE DISABLE_DATE
142 FROM WIP_OPERATION_YIELDS WOY,
143 WIP_OPERATIONS WO
144 WHERE WOY.WIP_ENTITY_ID = p_entity_id
145 AND WOY.OPERATION_SEQ_NUM >= p_starting_opseq
146 AND WO.WIP_ENTITY_ID = WOY.WIP_ENTITY_ID
147 AND WO.OPERATION_SEQ_NUM = WOY.OPERATION_SEQ_NUM
148 AND WO.ORGANIZATION_ID = WOY.ORGANIZATION_ID
149 AND WOY.ORGANIZATION_ID = p_organization_id
150 ORDER BY WOY.OPERATION_SEQ_NUM
151
152 FOR UPDATE OF woy.status;
153
154
155 BEGIN
156 ---------------------------------------------------------
157 -- Get profile value for debug --
158 ---------------------------------------------------------
159 x_statement := 05;
160 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
161
162 ---------------------------------------------------------
163 -- Get Values of WHO Columns --
164 ---------------------------------------------------------
165 x_statement := 10;
166
167 x_last_updated_by := fnd_global.user_id;
168 x_last_update_login := fnd_global.login_id;
169 x_request_id := fnd_global.conc_request_id;
170 x_program_application_id := fnd_global.prog_appl_id;
171 x_program_id := fnd_global.conc_program_id;
172 x_sysdate := SYSDATE;
173
174 IF(l_debug = 'Y') THEN
175 FND_FILE.put_line(fnd_file.log, 'PROCESS_OP_YIELD <<< ');
176 FND_FILE.put_line(FND_FILE.LOG,'P_ENTITY_ID : '||to_char(i_entity_id));
177 FND_FILE.put_line(FND_FILE.LOG,'P_ORGANIZATION_ID: '|| to_char(i_organization_id));
178 END IF;
179
180 ---------------------------------------------------------
181 -- Open wip_entity_id Cursor --
182 ---------------------------------------------------------
183 <<wip_entity>>
184
185 FOR rec_wip_entity IN c_wip_entity LOOP
186 IF(l_debug = 'Y') THEN
187 FND_FILE.put_line(FND_FILE.LOG, 'REC_WIP_ENTITY.WIP_ENTITY_ID: '|| to_char(rec_wip_entity.wip_entity_id));
188 END IF;
189
190 /* Initialize Absorptions */
191 x_tl_scrap_in := 0;
192 x_tl_scrap_out := 0;
193
194 /* Changes for Optional Scrap */
195 x_statement := 15;
196 temp_wip_entity_id := rec_wip_entity.wip_entity_id;
197 x_est_scrap_acct_flag := WSMPUTIL.WSM_ESA_ENABLED(rec_wip_entity.wip_entity_id, x_err_num, x_err_msg);
198 IF (x_est_scrap_acct_flag = 0) THEN
199 RAISE WSM_ESA_PKG_ERROR;
200 END IF;
201
202 /* Do Operation Yield Accounting only if ESA is enabled */
203 IF x_est_scrap_acct_flag = 1 THEN
204 if (l_debug = 'Y') then
205 fnd_file.put_line(fnd_file.log, 'WIP ENTITY ID : '||to_char(rec_wip_entity.wip_entity_id));
206 fnd_file.put_line(fnd_file.log, 'ORG ID : '||to_char(rec_wip_entity.organization_id));
207 end if;
208
209 ---------------------------------------------------------
210 -- Get Currency Information --
211 ---------------------------------------------------------
212 IF (x_organization_id <> rec_wip_entity.organization_id) THEN
213 x_organization_id := rec_wip_entity.organization_id;
214
215
216 ---------------------------------------------------------
217 -- Get Currency Code for Organization --
218 ---------------------------------------------------------
219 x_statement := 20;
220
221 SELECT COD.CURRENCY_CODE, COD.OPERATING_UNIT
222 INTO x_currency_code, l_operating_unit
223 FROM CST_ORGANIZATION_DEFINITIONS COD
224 WHERE COD.ORGANIZATION_ID = rec_wip_entity.organization_id;
225
226
227 x_statement := 30;
228
229 fnd_currency.get_info( x_currency_code,
230 x_precision,
231 x_ext_precision,
232 x_min_acct_unit);
233
234
235 ---------------------------------------------------------
236 -- Get Accounting Period Information --
237 ---------------------------------------------------------
238 x_statement := 38;
239 l_legal_entity_date := INV_LE_TIMEZONE_PUB.GET_LE_SYSDATE_FOR_OU(l_operating_unit);
240
241 x_statement := 40;
242 BEGIN
243 SELECT acct_period_id
244 INTO x_acct_period_id
245 FROM org_acct_periods
246 WHERE organization_id = x_organization_id
247 AND l_legal_entity_date BETWEEN period_start_date AND schedule_close_date;
248 EXCEPTION
249 when NO_DATA_FOUND then
250 fnd_file.put_line(fnd_file.log,'Accounting period is not open for the organization: ' || to_char(x_organization_id));
251 END;
252
253 if (l_debug = 'Y') then
254 fnd_file.put_line(fnd_file.log, 'ACCT PERIOD ID : '||to_char( x_acct_period_id ));
255 end if;
256
257 END IF; /* (x_organization_id <> rec_wip_entity.organization_id) */
258
259 ---------------------------------------------------------
260 -- Get Starting Operation Sequence Number --
261 ---------------------------------------------------------
262
263 x_statement := 50;
264 x_starting_opseq := rec_wip_entity.starting_op_seq;
265
266 x_first_operation := TRUE;
267
268
269 ---------------------------------------------------------
270 -- Open Operation Sequence Cursor --
271 ---------------------------------------------------------
272
273 x_save_point := 'sv'||to_char(rec_wip_entity.wip_entity_id);
274 SAVEPOINT x_save_point;
275 <<opseq>>
276
277 FOR rec_opseq IN c_opseq (rec_wip_entity.wip_entity_id, x_starting_opseq, x_organization_id) LOOP
278
279 BEGIN
280
281
282 /* Changes for Optional Scrap. To take care of case when the
283 ESA flag is toggled between when the job is created and
284 Released or between when the job is closed and unclosed. */
285
286 IF(rec_opseq.SCRAP_ACCOUNT IS NULL OR
287 rec_opseq.EST_SCRAP_ABSORB_ACCOUNT IS NULL) THEN
288
289 x_statement := 55;
290 SELECT bd.scrap_account,bd.est_absorption_account
291 INTO l_scrap_acct, l_est_scrap_abs_acct
292 FROM bom_departments bd, wip_operations wo
293 WHERE wo.operation_seq_num = rec_opseq.operation_seq_num
294 AND wo.wip_entity_id = rec_wip_entity.wip_entity_id
295 AND wo.organization_id = rec_wip_entity.organization_id
296 AND bd.department_id = wo.department_id
297 AND bd.organization_id = wo.organization_id;
298
299
300 IF l_scrap_acct IS NULL OR l_est_scrap_abs_acct IS NULL THEN
301 RAISE EST_ACCT_NOT_FOUND;
302 END IF;
303
304 x_statement := 60;
305 UPDATE wip_operation_yields woy
306 SET SCRAP_ACCOUNT = l_scrap_acct,
307 EST_SCRAP_ABSORB_ACCOUNT = l_est_scrap_abs_acct
308 WHERE woy.operation_seq_num = rec_opseq.operation_seq_num
309 AND woy.wip_entity_id = rec_wip_entity.wip_entity_id
310 AND woy.organization_id = rec_wip_entity.organization_id;
311 ELSE
312 l_scrap_acct := rec_opseq.SCRAP_ACCOUNT;
313 l_est_scrap_abs_acct := rec_opseq.EST_SCRAP_ABSORB_ACCOUNT;
314
315 END IF;
316
317 /* Bug #4045115. Call client extension to override the scrap account. */
318 l_scrap_rev_acct := CSTPSCHK.std_get_est_scrap_rev_acct_id(
319 i_org_id => rec_wip_entity.organization_id,
320 i_wip_entity_id => rec_wip_entity.wip_entity_id,
321 i_operation_seq_num => rec_opseq.operation_seq_num);
322
323 if(l_scrap_rev_acct = -1) then
324 l_scrap_rev_acct := l_scrap_acct;
325 end if;
326
327 if (l_debug = 'Y') then
328 fnd_file.put_line(fnd_file.log, 'EST_SCRAP_ACCOUNT : '||to_char(REC_WIP_ENTITY.EST_SCRAP_ACCOUNT ));
329 fnd_file.put_line(fnd_file.log, 'EST_SCRAP_VAR_ACCOUNT : '||to_char(REC_WIP_ENTITY.EST_SCRAP_VAR_ACCOUNT ));
330 fnd_file.put_line(fnd_file.log, 'OPERATION_SEQ_NUM : '||to_char(REC_OPSEQ.OPERATION_SEQ_NUM ));
331 fnd_file.put_line(fnd_file.log, 'OPERATION_COST : '||to_char(REC_OPSEQ.OPERATION_COST ));
332 fnd_file.put_line(fnd_file.log, 'OPERATION_UNIT_COST : '||to_char(REC_OPSEQ.OPERATION_UNIT_COST ));
333 fnd_file.put_line(fnd_file.log, 'CUM_OPERATION_UNIT_COST : '||to_char(REC_OPSEQ.CUM_OPERATION_UNIT_COST ));
334 fnd_file.put_line(fnd_file.log, 'EST_SCRAP_UNIT_COST : '||to_char(REC_OPSEQ.EST_SCRAP_UNIT_COST ));
335 fnd_file.put_line(fnd_file.log, 'CUM_EST_PRIOR_UNIT_COST : '||to_char(REC_OPSEQ.CUM_EST_PRIOR_UNIT_COST ));
336 fnd_file.put_line(fnd_file.log, 'EST_SCRAP_QTY_COMPLETED : '||to_char(REC_OPSEQ.EST_SCRAP_QTY_COMPLETED ));
337 fnd_file.put_line(fnd_file.log, 'EST_SCRAP_QTY_SCRAPED : '||to_char(REC_OPSEQ.EST_SCRAP_QTY_SCRAPED ));
338 fnd_file.put_line(fnd_file.log, 'SCRAP_ACCOUNT : '||to_char(l_scrap_acct ));
339 fnd_file.put_line(fnd_file.log, 'NET_REV_ACCT : '||to_char(l_scrap_rev_acct ));
340
341 fnd_file.put_line(fnd_file.log, 'EST_SCRAP_ABSORB_ACCOUNT : '||to_char(l_est_scrap_abs_acct ));
342 fnd_file.put_line(fnd_file.log, 'STATUS : '||to_char(REC_OPSEQ.STATUS ));
343 fnd_file.put_line(fnd_file.log, 'QUANTITY_COMPLETED : '||to_char(REC_OPSEQ.QUANTITY_COMPLETED ));
344 fnd_file.put_line(fnd_file.log, 'QUANTITY_SCRAPPED : '||to_char(REC_OPSEQ.QUANTITY_SCRAPPED ));
345 fnd_file.put_line(fnd_file.log, 'OPERATION_YIELD : '||to_char(REC_OPSEQ.OPERATION_YIELD ));
346 fnd_file.put_line(fnd_file.log, 'DEPARTMENT_ID : '||to_char(REC_OPSEQ.DEPARTMENT_ID ));
347 end if;
348 ---------------------------------------------------------
349 -- Initialize Parameters if first operation of job --
350 ---------------------------------------------------------
351 If x_first_operation THEN
352
353 /* Bug 4599116 - Added the join with WIP_OPERATIONS table to avoid the
354 operations that are obsoleted due to undo move or
355 update assembly transactions. The obsoleted operation is determined
356 by a valid disable date */
357
358 x_statement := 200;
359 SELECT count(*)
360 INTO x_count
361 FROM WIP_OPERATION_YIELDS woy,
362 WIP_OPERATIONS wo
363 WHERE woy.wip_entity_id = rec_wip_entity.wip_entity_id
364 and wo.wip_entity_id = woy.wip_entity_id
365 and woy.organization_id = x_organization_id
366 and wo.organization_id = x_organization_id
367 and woy.operation_seq_num = wo.operation_seq_num
368 and woy.operation_seq_num < rec_opseq.operation_seq_num
369 and wo.disable_date is null;
370
371 x_statement := 210;
372
373 IF (x_count = 0) THEN
374 x_unit_cost := 0;
375 x_cum_pr_est_scp_per_unit := 0;
376 ELSE
377 x_statement := 220;
378
379 SELECT NVL(CUM_OPERATION_UNIT_COST, 0)
380 INTO x_unit_cost
381 FROM WIP_OPERATION_YIELDS
382 WHERE wip_entity_id = rec_wip_entity.wip_entity_id
383 and organization_id = x_organization_id
384 and operation_seq_num = (select max(woy.operation_seq_num)
385 from wip_operation_yields woy,
386 wip_operations wo
387 where woy.wip_entity_id = rec_wip_entity.wip_entity_id
388 and woy.organization_id = x_organization_id
389 and woy.operation_seq_num < rec_opseq.operation_seq_num
390 and woy.operation_seq_num = wo.operation_seq_num
391 and woy.wip_entity_id = wo.wip_entity_id
392 and woy.organization_id = wo.organization_id
393 and wo.disable_date is null);
394
395 x_statement := 230;
396 SELECT SUM( NVL(EST_SCRAP_UNIT_COST, 0))
397 INTO x_cum_pr_est_scp_per_unit
398 FROM WIP_OPERATION_YIELDS WOY,
399 WIP_OPERATIONS WO
400 WHERE woy.wip_entity_id = rec_wip_entity.wip_entity_id
401 and woy.organization_id = x_organization_id
402 and woy.operation_seq_num < rec_opseq.operation_seq_num
403 and woy.operation_seq_num = wo.operation_seq_num
404 and woy.wip_entity_id = wo.wip_entity_id
405 and woy.organization_id = wo.organization_id
406 and wo.disable_date is null;
407
408 END IF; /* IF x_count = 0 */
409
410 x_tl_scrap_in := 0;
411 x_tl_scrap_out := 0;
412 x_first_operation := FALSE;
413
414 ELSE
415 ---------------------------------------------------------------
416 -- Compute the Cumulative Prior Estimated Scrap for other cases
417 ---------------------------------------------------------------
418 SELECT SUM( NVL(EST_SCRAP_UNIT_COST, 0))
419 INTO x_cum_pr_est_scp_per_unit
420 FROM WIP_OPERATION_YIELDS WOY,
421 WIP_OPERATIONS WO
422 WHERE woy.wip_entity_id = rec_wip_entity.wip_entity_id
423 and woy.organization_id = x_organization_id
424 and woy.operation_seq_num < rec_opseq.operation_seq_num
425 and woy.operation_seq_num = wo.operation_seq_num
426 and woy.wip_entity_id = wo.wip_entity_id
427 and woy.organization_id = wo.organization_id
428 and wo.disable_date is null;
429 END IF; /* IF x_first_operation */
430
431
432 ---------------------------------------------------------
433 -- Calculate Net Absorption --
434 ---------------------------------------------------------
435 if (l_debug = 'Y') then
436 fnd_file.put_line(fnd_file.log,'Disable_date : '||rec_opseq.disable_date);
437 fnd_file.put_line(fnd_file.log,'Cumulative Est Prior Scrap: '||x_cum_pr_est_scp_per_unit);
438 end if;
439
440 IF (rec_opseq.quantity_completed <> 0 and rec_opseq.disable_date IS NULL) THEN
441 x_op_unit_cost := rec_opseq.operation_cost / rec_opseq.quantity_completed ;
442 ELSE
443 x_op_unit_cost := 0;
444 END IF;
445 x_unit_cost := x_unit_cost + x_op_unit_cost;
446
447 /* Backward Moves : when an operation is obsoleted in a
448 backward move, then it should not be considered
449 while calculating operation yield cost. This check is very
450 specific to WSM organizations */
451 select wsm_enabled_flag
452 into x_wsm_enabled_flag
453 from mtl_parameters
454 where organization_id = rec_wip_entity.organization_id;
455
456 if (x_wsm_enabled_flag = 'Y') then
457 select NVL(last_operation_seq_num,9999)
458 into x_last_opseq_num
459 from wsm_parameters
460 where organization_id = rec_wip_entity.organization_id;
461
462 if (rec_opseq.quantity_completed = 0 OR rec_opseq.disable_date is not null) then
463 x_est_scrap_per_unit := 0;
464 else
465 x_est_scrap_per_unit := x_unit_cost * ((1 - rec_opseq.operation_yield) / rec_opseq.operation_yield);
466 end if;
467 end if; /* x_wsm_enabled_flag = 'Y' */
468
469 if (l_debug = 'Y') then
470 fnd_file.put_line(fnd_file.log,'Est scrap per unit cost : ' || to_char(x_est_scrap_per_unit));
471 end if;
472
473 x_unit_cost := x_unit_cost + x_est_scrap_per_unit;
474
475 IF (i_run_option = 2) THEN
476
477 x_net_absorption := (x_est_scrap_per_unit - rec_opseq.est_scrap_unit_cost) * (rec_opseq.est_scrap_qty_completed - rec_opseq.est_scrap_qty_scraped);
478 ELSE
479
480 x_net_absorption := (x_est_scrap_per_unit *
481 (rec_opseq.quantity_completed - rec_opseq.quantity_scrapped) - rec_opseq.est_scrap_unit_cost *
482 (rec_opseq.est_scrap_qty_completed - rec_opseq.est_scrap_qty_scraped));
483
484 END If; /* i_run_option = 2 */
485
486 if (l_debug = 'Y') then
487 fnd_file.put_line(fnd_file.log,'x_net_absorption : ' || to_char(x_net_absorption));
488 end if;
489
490 ---------------------------------------------------------
491 -- Perform Accounting for Net Absorption: --
492 -- DR CR --
493 -- EST_SCRAP_ACCOUNT X --
494 -- EST_SCRAP_ABSORPTION_ACCOUNT X --
495 -- TRANSACTION_TYPE = 15 (Estimated Scrap_transaction) --
496 -- Acounting Line Type : --
497 -- 29 (Estimated Scrap Absorption) --
498 -- 7 ( WIP valauation) --
499 -- from mfg_lookups
500 ---------------------------------------------------------
501
502
503 IF (x_net_absorption <> 0 ) THEN
504
505 IF (i_run_option = 3 and rec_opseq.operation_seq_num <= i_txn_op_seq_num ) THEN
506 SELECT BONUS_ACCT_ID
507 INTO x_abs_account
508 FROM WSM_SM_RESULTING_JOBS
509 WHERE TRANSACTION_ID = i_sm_txn_id
510 AND WIP_ENTITY_ID = i_entity_id;
511 ELSE
512 x_abs_account := l_est_scrap_abs_acct;
513 END IF;
514
515 x_statement := 60;
516
517 SELECT wip_transactions_s.nextval
518 INTO x_transaction_id
519 FROM dual;
520
521 if (l_debug = 'Y') then
522 fnd_file.put_line(fnd_file.log,'Inserting into WT transaction : '||to_char(x_transaction_id));
523 end if;
524
525 l_transaction_date := sysdate; /* Bug 4757384 */
526
527 INSERT INTO WIP_TRANSACTIONS(transaction_id,
528 organization_id,
529 wip_entity_id,
530 acct_period_id,
531 department_id,
532 transaction_type,
533 transaction_date,
534 operation_seq_num,
535 primary_item_id,
536 last_update_date,
537 last_updated_by,
538 creation_date,
539 created_by,
540 last_update_login,
541 request_id,
542 program_application_id,
543 program_id,
544 program_update_date )
545
546 VALUES(x_transaction_id,
547 x_organization_id,
548 rec_wip_entity.wip_entity_id,
549 x_acct_period_id,
550 rec_opseq.department_id,
551 15,
552 l_transaction_date,
553 rec_opseq.operation_seq_num,
554 rec_wip_entity.primary_item_id,
555 x_sysdate,
556 x_last_updated_by,
557 x_sysdate,
558 x_last_updated_by,
559 x_last_update_login,
560 x_request_id,
561 x_program_application_id,
562 x_program_id,
563 x_sysdate);
564
565 ---------------------------------------------------------
566 -- Debit EST_SCRAP_ACCOUNT --
567 ---------------------------------------------------------
568
569 if (l_debug = 'Y') then
570 fnd_file.put_line(fnd_file.log,'Inserting into WTA transaction : '||to_char(x_transaction_id));
571 end if;
572
573 x_statement := 70;
574
575 INSERT INTO
576 WIP_TRANSACTION_ACCOUNTS
577 (
578 wip_sub_ledger_id, /* R12 - SLA Distribution Link */
579 transaction_id,
580 reference_account,
581 organization_id,
582 transaction_date,
583 wip_entity_id,
584 accounting_line_type,
585 base_transaction_value,
586 last_update_date,
587 last_updated_by,
588 creation_date,
589 created_by,
590 last_update_login,
591 request_id,
592 program_application_id,
593 program_id,
594 program_update_date )
595 VALUES
596 (
597 CST_WIP_SUB_LEDGER_ID_S.NEXTVAL,
598 x_transaction_id,
599 rec_wip_entity.est_scrap_account,
600 x_organization_id,
601 l_transaction_date,
602 rec_wip_entity.wip_entity_id,
603 7,
604 decode(NVL(x_min_acct_unit, 0), 0, ROUND(x_net_absorption, x_precision),
605 ROUND (x_net_absorption / x_min_acct_unit) * x_min_acct_unit),
606 x_sysdate,
607 x_last_updated_by,
608 x_sysdate,
609 x_last_updated_by,
610 x_last_update_login,
611 x_request_id,
612 x_program_application_id,
613 x_program_id,
614 x_sysdate);
615
616 ---------------------------------------------------------
617 -- Credit EST_SCRAP_ABOORPTION_ACCOUNT --
618 ---------------------------------------------------------
619 x_statement := 80;
620 INSERT INTO
621 WIP_TRANSACTION_ACCOUNTS
622 (
623 wip_sub_ledger_id, /* R12 - SLA Distribution Link */
624 transaction_id,
625 reference_account,
626 organization_id,
627 transaction_date,
628 wip_entity_id,
629 accounting_line_type,
630 base_transaction_value,
631 last_update_date,
632 last_updated_by,
633 creation_date,
634 created_by,
635 last_update_login,
636 request_id,
637 program_application_id,
638 program_id,
639 program_update_date )
640 VALUES
641 (
642 CST_WIP_SUB_LEDGER_ID_S.NEXTVAL,
643 x_transaction_id,
644 x_abs_account,
645 x_organization_id,
646 l_transaction_date,
647 rec_wip_entity.wip_entity_id,
648 29,
649 decode(NVL(x_min_acct_unit, 0), 0, ROUND(-1 *(x_net_absorption), x_precision),
650 ROUND (-1 *(x_net_absorption) / x_min_acct_unit) * x_min_acct_unit),
651 x_sysdate,
652 x_last_updated_by,
653 x_sysdate,
654 x_last_updated_by,
655 x_last_update_login,
656 x_request_id,
657 x_program_application_id,
658 x_program_id,
659 x_sysdate);
660
661 /* SLA Event Seeding */
662 l_trx_info.TRANSACTION_ID := x_transaction_id;
663 l_trx_info.WIP_RESOURCE_ID := -1;
664 l_trx_info.WIP_BASIS_TYPE_ID := -1;
665 l_trx_info.TXN_TYPE_ID := 15;
666 l_trx_info.INV_ORGANIZATION_ID := x_organization_id;
667 l_trx_info.TRANSACTION_DATE := x_sysdate;
668 x_statement := 85;
669
670 CST_XLA_PVT.Create_WIPXLAEvent(
671 p_api_version => 1.0,
672 p_init_msg_list => FND_API.G_FALSE,
673 p_commit => FND_API.G_FALSE,
674 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
675 x_return_status => l_return_status,
676 x_msg_count => l_msg_count,
677 x_msg_data => l_msg_data,
678 p_trx_info => l_trx_info);
679
680 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
681 FND_FILE.put_line(FND_FILE.log, 'Event Creation Failed: '||l_msg_data );
682 RAISE FND_API.g_exc_unexpected_error;
683 END IF;
684
685 END IF; /* x_net_absorption <> 0 */
686
687
688 ---------------------------------------------------------
689 -- Calculate Net Reversal --
690 ---------------------------------------------------------
691
692 IF (i_run_option = 2) THEN
693
694 x_net_reversal := (x_cum_pr_est_scp_per_unit - rec_opseq.cum_est_prior_unit_cost) *
695 rec_opseq.est_scrap_qty_scraped;
696 ELSE
697
698 x_net_reversal := x_cum_pr_est_scp_per_unit * rec_opseq.quantity_scrapped -
699 rec_opseq.cum_est_prior_unit_cost * rec_opseq.est_scrap_qty_scraped;
700 END If;
701
702 if (l_debug = 'Y') then
703 fnd_file.put_line(fnd_file.log,'x_net_reversal : '||to_char(x_net_reversal));
704 end if;
705
706 ---------------------------------------------------------
707 -- Perform Accounting for Net Reversal: --
708 -- DR CR --
709 -- SCRAP_ACCOUNT X --
710 -- EST_SCRAP__ACCOUNT X --
711 -- TRANSACTION_TYPE = 90 (Scrap_transaction) --
712 -- Acounting Line Type : --
713 -- 2 (Account) --
714 -- 7 ( WIP valauation) --
715 -- from mfg_lookups
716 ---------------------------------------------------------
717
718
719 IF (x_net_reversal <> 0 ) THEN
720
721 x_statement := 90;
722
723 SELECT mtl_material_transactions_s.nextval
724 INTO x_transaction_id
725 FROM dual;
726
727 /* Bug #2840690. Get primary UOM of assembly */
728 SELECT muom.uom_code
729 INTO l_uom
730 FROM
731 mtl_system_items msi, mtl_units_of_measure muom
732 WHERE msi.inventory_item_id = rec_wip_entity.primary_item_id
733 AND msi.organization_id = x_organization_id
734 AND msi.primary_unit_of_measure = muom.unit_of_measure;
735
736
737 if (l_debug = 'Y') then
738 fnd_file.put_line(fnd_file.log,'Inserting into MMT transaction : '||to_char(x_transaction_id));
739 end if;
740
741 l_transaction_date := sysdate; /* Bug 4757384 */
742
743 INSERT into
744 MTL_MATERIAL_TRANSACTIONS(
745 transaction_id,
746 inventory_item_id,
747 organization_id,
748 transaction_type_id,
749 transaction_action_id,
750 transaction_source_type_id,
751 transaction_quantity,
752 transaction_uom,
753 primary_quantity,
754 transaction_date,
755 acct_period_id,
756 department_id,
757 operation_seq_num,
758 transaction_source_id,
759 last_update_date,
760 last_updated_by,
761 creation_date,
762 created_by,
763 last_update_login,
764 request_id,
765 program_application_id,
766 program_id,
767 program_update_date)
768 VALUES
769 (x_transaction_id,
770 rec_wip_entity.primary_item_id,
771 x_organization_id,
772 92, /* Est Scrap Txn in MMT (new type) */
773 30,
774 5,
775 (rec_opseq.quantity_scrapped
776 - rec_opseq.est_scrap_qty_scraped),
777 l_uom,
778 (rec_opseq.quantity_scrapped
779 - rec_opseq.est_scrap_qty_scraped),
780 l_transaction_date,
781 x_acct_period_id,
782 rec_opseq.department_id,
783 rec_opseq.operation_seq_num,
784 rec_wip_entity.wip_entity_id,
785 x_sysdate,
786 x_last_updated_by,
787 x_sysdate,
788 x_last_updated_by,
789 x_last_update_login,
790 x_request_id,
791 x_program_application_id,
792 x_program_id,
793 x_sysdate);
794
795 ---------------------------------------------------------
796 -- Debit SCRAP_ACCOUNT --
797 ---------------------------------------------------------
798
799 x_statement := 100;
800 INSERT into MTL_TRANSACTION_ACCOUNTS
801 (
802 inv_sub_ledger_id, /* R12 - SLA Distribution Link */
803 transaction_id,
804 reference_account,
805 inventory_item_id,
806 organization_id,
807 transaction_date,
808 transaction_source_id,
809 transaction_source_type_id,
810 primary_quantity,
811 accounting_line_type,
812 base_transaction_value,
813 contra_set_id,
814 rate_or_amount,
815 last_update_date,
816 last_updated_by,
817 creation_date,
818 created_by,
819 last_update_login,
820 request_id,
821 program_application_id,
822 program_id,
823 program_update_date)
824 VALUES(
825 CST_INV_SUB_LEDGER_ID_S.NEXTVAL,
826 x_transaction_id,
827 l_scrap_rev_acct,
828 rec_wip_entity.primary_item_id,
829 x_organization_id,
830 l_transaction_date,
831 rec_wip_entity.wip_entity_id,
832 5,
833 (rec_opseq.quantity_scrapped - rec_opseq.est_scrap_qty_scraped),
834 2,
835 decode(NVL(x_min_acct_unit, 0), 0, ROUND(x_net_reversal, x_precision),
836 ROUND (x_net_reversal / x_min_acct_unit) * x_min_acct_unit),
837 1,
838 x_cum_pr_est_scp_per_unit,
839 x_sysdate,
840 x_last_updated_by,
841 x_sysdate,
842 x_last_updated_by,
843 x_last_update_login,
844 x_request_id,
845 x_program_application_id,
846 x_program_id,
847 x_sysdate);
848
849 ---------------------------------------------------------
850 -- Credit EST_SCRAP_ACCOUNT --
851 ---------------------------------------------------------
852 x_statement := 110;
853 INSERT into MTL_TRANSACTION_ACCOUNTS
854 (
855 inv_sub_ledger_id, /* R12 - SLA Distribution Link */
856 transaction_id,
857 reference_account,
858 inventory_item_id,
859 organization_id,
860 transaction_date,
861 transaction_source_id,
862 transaction_source_type_id,
863 primary_quantity,
864 accounting_line_type,
865 base_transaction_value,
866 contra_set_id,
867 rate_or_amount,
868 last_update_date,
869 last_updated_by,
870 creation_date,
871 created_by,
872 last_update_login,
873 request_id,
874 program_application_id,
875 program_id,
876 program_update_date)
877 VALUES(
878 CST_INV_SUB_LEDGER_ID_S.NEXTVAL,
879 x_transaction_id,
880 rec_wip_entity.est_scrap_account,
881 rec_wip_entity.primary_item_id,
882 x_organization_id,
883 l_transaction_date,
884 rec_wip_entity.wip_entity_id,
885 5,
886 (rec_opseq.quantity_scrapped - rec_opseq.est_scrap_qty_scraped),
887 7,
888 decode(NVL(x_min_acct_unit, 0), 0, ROUND(-1 *(x_net_reversal), x_precision),
889 ROUND (-1 *(x_net_reversal) / x_min_acct_unit) * x_min_acct_unit),
890 1,
891 x_cum_pr_est_scp_per_unit,
892 x_sysdate,
893 x_last_updated_by,
894 x_sysdate,
895 x_last_updated_by,
896 x_last_update_login,
897 x_request_id,
898 x_program_application_id,
899 x_program_id,
900 x_sysdate);
901
902 l_inv_trx_info.TRANSACTION_ID := x_transaction_id;
903 l_inv_trx_info.TXN_ACTION_ID := 30;
904 l_inv_trx_info.TXN_ORGANIZATION_ID := x_organization_id;
905 l_inv_trx_info.TXN_SRC_TYPE_ID := 5;
906 l_inv_trx_info.TXN_TYPE_ID := 92;
907 l_inv_trx_info.TRANSACTION_DATE := l_transaction_date;
908
909 x_statement := 115;
910 /* Create the SLA event for the Estimated Scrap Reversal */
911 CST_XLA_PVT.Create_INVXLAEvent (
912 p_api_version => 1.0,
913 p_init_msg_list => FND_API.G_FALSE,
914 p_commit => FND_API.G_FALSE,
915 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
916 x_return_status => l_return_status,
917 x_msg_count => l_msg_count,
918 x_msg_data => l_msg_data,
919 p_trx_info => l_inv_trx_info
920 );
921 IF l_return_status <> 'S' THEN
922 FND_FILE.put_line(FND_FILE.log, 'Event creation failed: CSTPOYLD.process_op_yield('||x_statement||')');
923 RAISE FND_API.g_exc_unexpected_error;
924 END IF;
925
926 END IF; /* x_net_reversal <> 0 */
927
928 if (l_debug = 'Y') then
929 fnd_file.put_line(fnd_file.log,'Updating WOY ');
930 end if;
931
932
933 ---------------------------------------------------------
934 -- Update WIP_OPERATION_YIELDS --
935 ---------------------------------------------------------
936
937 x_statement := 120;
938 UPDATE WIP_OPERATION_YIELDS
939 SET operation_unit_cost = x_op_unit_cost,
940 cum_operation_unit_cost = x_unit_cost,
941 est_scrap_unit_cost = x_est_scrap_per_unit,
942 cum_est_prior_unit_cost = x_cum_pr_est_scp_per_unit,
943 est_scrap_qty_completed = rec_opseq.quantity_completed,
944 est_scrap_qty_scraped = rec_opseq.quantity_scrapped,
945 status = 2,
946 last_update_date = x_sysdate,
947 last_updated_by = x_last_updated_by,
948 request_id = x_request_id,
949 program_application_id = x_program_application_id,
950 program_id = x_program_id,
951 program_update_date = x_sysdate
952 WHERE organization_id = x_organization_id
953 AND wip_entity_id = rec_wip_entity.wip_entity_id
954 AND operation_seq_num = rec_opseq.operation_seq_num;
955
956 ---------------------------------------------------------
957 -- Update variables --
958 ---------------------------------------------------------
959
960 x_statement := 125;
961 SELECT x_tl_scrap_in + decode(NVL(x_min_acct_unit, 0), 0, ROUND(x_net_absorption, x_precision),
962 ROUND (x_net_absorption / x_min_acct_unit) * x_min_acct_unit),
963 x_tl_scrap_out + decode(NVL(x_min_acct_unit, 0), 0, ROUND(x_net_reversal, x_precision),
964 ROUND (x_net_reversal / x_min_acct_unit) * x_min_acct_unit)
965 INTO x_tl_scrap_in,
966 x_tl_scrap_out
967 FROM dual;
968
969 EXCEPTION
970 WHEN EST_ACCT_NOT_FOUND THEN
971 x_tl_scrap_in := 0;
972 x_tl_scrap_out := 0;
973 ROLLBACK TO x_save_point;
974
975 UPDATE WIP_OPERATION_YIELDS
976 SET status = 3,
977 last_update_date = x_sysdate,
978 last_updated_by = x_last_updated_by,
979 request_id = x_request_id,
980 program_application_id = x_program_application_id,
981 program_id = x_program_id,
982 program_update_date = x_sysdate
983 WHERE organization_id = x_organization_id
984 AND wip_entity_id = rec_wip_entity.wip_entity_id
985 AND operation_seq_num = rec_opseq.operation_seq_num;
986
987 fnd_file.put_line(FND_FILE.LOG,'BOM department does not have scrap account '||
988 'or estimated scrap absortion account defined');
989 fnd_message.set_name('BOM', 'CST_OP_YLD_NOT_PROCESSED');
990 fnd_message.set_token('ENTITY_ID', to_char(rec_wip_entity.wip_entity_id));
991 fnd_file.put_line(fnd_file.log, fnd_message.get);
992
993 fnd_message.set_name('BOM', 'CST_OP_YLD_GENERAL_ERROR');
994 fnd_message.set_token('NUMBER', to_char(x_statement));
995 fnd_file.put_line(fnd_file.log, fnd_message.get);
996
997
998 x_err_num := SQLCODE;
999 x_err_msg := substr(SQLERRM, 1, 200);
1000 ERRBUF := x_err_msg;
1001 fnd_file.put_line(fnd_file.log, 'x_err_num' || ' : ' || x_err_msg);
1002 x_status := fnd_concurrent.set_completion_status( status => 'WARNING',
1003 message => '');
1004
1005 EXIT opseq;
1006
1007 WHEN OTHERS THEN
1008 x_tl_scrap_in := 0;
1009 x_tl_scrap_out := 0;
1010 ROLLBACK TO x_save_point;
1011
1012 UPDATE WIP_OPERATION_YIELDS
1013 SET status = 3,
1014 last_update_date = x_sysdate,
1015 last_updated_by = x_last_updated_by,
1016 request_id = x_request_id,
1017 program_application_id = x_program_application_id,
1018 program_id = x_program_id,
1019 program_update_date = x_sysdate
1020 WHERE organization_id = x_organization_id
1021 AND wip_entity_id = rec_wip_entity.wip_entity_id
1022 AND operation_seq_num = rec_opseq.operation_seq_num;
1023
1024
1025 fnd_message.set_name('BOM', 'CST_OP_YLD_NOT_PROCESSED');
1026 fnd_message.set_token('ENTITY_ID', to_char(rec_wip_entity.wip_entity_id));
1027 fnd_file.put_line(fnd_file.log, fnd_message.get);
1028
1029 fnd_message.set_name('BOM', 'CST_OP_YLD_GENERAL_ERROR');
1030 fnd_message.set_token('NUMBER', to_char(x_statement));
1031 fnd_file.put_line(fnd_file.log, fnd_message.get);
1032
1033
1034 x_err_num := SQLCODE;
1035 x_err_msg := substr(SQLERRM, 1, 200);
1036 ERRBUF := x_err_msg;
1037 fnd_file.put_line(fnd_file.log, 'x_err_num' || ' : ' || x_err_msg);
1038 x_status := fnd_concurrent.set_completion_status( status => 'WARNING',
1039 message => '');
1040
1041 EXIT opseq;
1042
1043 END;
1044
1045 END LOOP opseq;
1046 ---------------------------------------------------------
1047 -- Update WIP_PERIOD_BALANCEs --
1048 ---------------------------------------------------------
1049
1050
1051 /* Update WOY if x_tl_scrap_in or x_tl_scrap_out <> 0 */
1052 IF (x_tl_scrap_in <> 0 OR x_tl_scrap_out <> 0) THEN
1053 x_statement := 130;
1054 UPDATE WIP_PERIOD_BALANCES
1055 SET tl_scrap_in = NVL(tl_scrap_in, 0) + x_tl_scrap_in,
1056 tl_scrap_out = NVL(tl_scrap_out, 0) + x_tl_scrap_out,
1057 last_update_date = x_sysdate,
1058 last_updated_by = x_last_updated_by,
1059 request_id = x_request_id,
1060 program_application_id = x_program_application_id,
1061 program_id = x_program_id,
1062 program_update_date = x_sysdate
1063 WHERE organization_id = x_organization_id
1064 AND wip_entity_id = rec_wip_entity.wip_entity_id
1065 AND acct_period_id = x_acct_period_id;
1066 END IF;
1067
1068 IF (i_run_option <> 3) THEN
1069 COMMIT;
1070 END IF;
1071
1072 END IF; /* If ESA is enabled */
1073
1074 END LOOP wip_entity;
1075 IF(l_debug = 'Y') THEN
1076 fnd_file.put_line(fnd_file.log, 'PROCESS_OP_YIELD >>>');
1077 END IF;
1078
1079 EXCEPTION
1080 WHEN WSM_ESA_PKG_ERROR THEN
1081 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in WSM_ESA_ENABLED : '||
1082 ' wip_entity_id '|| to_char(temp_wip_entity_id) ||
1083 x_err_num ||' : '|| x_err_msg);
1084 ERRBUF := 'CSTPOYLD.process_op_yield:' || x_statement || ':' ||
1085 substr(x_err_msg,1,200);
1086 RAISE_APPLICATION_ERROR( -20001, x_err_msg);
1087 WHEN OTHERS THEN
1088 fnd_message.set_name('BOM', 'CST_OP_YLD_GENERAL_ERROR');
1089 fnd_message.set_token('NUMBER', 'x_statement');
1090 fnd_file.put_line(fnd_file.log, fnd_message.get);
1091 x_err_num := SQLCODE;
1092 x_err_msg := 'CSTPOYLD.process_op_yield:' || x_statement || ':' ||
1093 substr(SQLERRM, 1, 200);
1094 fnd_file.put_line(fnd_file.log,x_err_msg);
1095 ERRBUF := x_err_msg;
1096 RAISE_APPLICATION_ERROR( -20001, x_err_msg);
1097
1098 END process_op_yield;
1099
1100
1101 ---------------------------------------------------------------------------
1102 -- FUNCTION --
1103 -- transact_op_yield_var --
1104 -- --
1105 -- DESCRIPTION --
1106 -- Use this function to calculate op yield reallocation and op yield --
1107 -- variance. This function is to be called from discrete job close --
1108 -- variance program cmlwjv() --
1109 -- --
1110 -- PURPOSE: --
1111 -- Oracle Applications Rel 11i.1 --
1112 -- --
1113 -- PARAMETERS: --
1114 -- i_group_id : Wip entity id of lot based job --
1115 -- RETURNS --
1116 -- 1 : Success --
1117 -- 0 : Failure --
1118 -- --
1119 -- HISTORY: --
1120 -- 03/02/00 Sujit Dalai Created --
1121 ----------------------------------------------------------------------------
1122 FUNCTION transact_op_yield_var( i_group_id IN NUMBER,
1123 i_user_id IN NUMBER,
1124 i_login_id IN NUMBER,
1125 i_prg_appl_id IN NUMBER,
1126 i_prg_id IN NUMBER,
1127 i_req_id IN NUMBER,
1128 o_err_num OUT NOCOPY NUMBER,
1129 o_err_code OUT NOCOPY VARCHAR2,
1130 o_err_msg OUT NOCOPY VARCHAR2)
1131 return NUMBER IS
1132 x_scrap_variance NUMBER := 0;
1133 x_tl_scrap_in NUMBER := 0;
1134 x_tl_scrap_out NUMBER := 0;
1135 x_tl_scrap_var NUMBER := 0;
1136 x_organization_id NUMBER := 0;
1137 x_currency_code VARCHAR2(15);
1138 x_precision NUMBER := 0;
1139 x_ext_precision NUMBER := 0;
1140 x_min_acct_unit NUMBER := 0;
1141 l_debug VARCHAR2(80);
1142
1143 /* Changes for Optional Scrap */
1144 x_est_scrap_acct_flag NUMBER :=0;
1145 x_err_num NUMBER := 0;
1146 x_err_msg VARCHAR2(240) ;
1147 WSM_ESA_PKG_ERROR EXCEPTION;
1148
1149 /* Bug 2469879*/
1150 x_history_count NUMBER;
1151
1152 CURSOR c_wip_entity IS
1153 SELECT distinct wcti.transaction_id,
1154 wcti.wip_entity_id,
1155 wcti.acct_period_id,
1156 wcti.organization_id,
1157 wcti.transaction_date, /* Bug 4757384 */
1158 wdj.est_scrap_account,
1159 wdj.est_scrap_var_account,
1160 wdj.primary_item_id
1161 FROM WIP_COST_TXN_INTERFACE wcti,
1162 WIP_DISCRETE_JOBS wdj,
1163 WIP_ENTITIES we
1164 WHERE wcti.group_id = i_group_id
1165 AND we.entity_type = 5
1166 and we.wip_entity_id = wcti.wip_entity_id
1167 and we.organization_id = wcti.organization_id
1168 AND wcti.wip_entity_id = wdj.wip_entity_id;
1169
1170 BEGIN
1171
1172 x_err_msg := '';
1173 o_err_code := '';
1174 o_err_num := 0;
1175 o_err_msg := '';
1176
1177 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
1178
1179 ---------------------------------------------------------
1180 -- Open wip_entity Cursor --
1181 ---------------------------------------------------------
1182 <<wip_entity>>
1183
1184 FOR rec_wip_entity IN c_wip_entity LOOP
1185
1186 /* Changes for Optional Scrap */
1187 x_est_scrap_acct_flag := WSMPUTIL.WSM_ESA_ENABLED(rec_wip_entity.wip_entity_id,
1188 x_err_num,x_err_msg);
1189
1190 IF (x_est_scrap_acct_flag = 0) THEN
1191 RAISE WSM_ESA_PKG_ERROR;
1192 END IF;
1193 IF (l_debug = 'Y') THEN
1194 FND_FILE.put_line(FND_FILE.log,'transact_op:x_est_scrap_acct_flag : '|| to_char(x_est_scrap_acct_flag));
1195 END IF;
1196
1197 /* Do Operation Yield Accounting only if ESA is enabled */
1198 IF x_est_scrap_acct_flag = 1 THEN
1199
1200 IF (x_organization_id <> rec_wip_entity.organization_id) THEN
1201 x_organization_id := rec_wip_entity.organization_id;
1202
1203 ---------------------------------------------------------
1204 -- Get Currency Code for Organization --
1205 ---------------------------------------------------------
1206
1207 /* The following lines in the select clause has been replaced with
1208 the reference to"CST_ORGANIZATION_DEFINITIONS" as an impact of the
1209 HR-PROFILE option" */
1210
1211
1212 SELECT COD.CURRENCY_CODE
1213 INTO x_currency_code
1214 FROM CST_ORGANIZATION_DEFINITIONS COD
1215 WHERE COD.ORGANIZATION_ID = rec_wip_entity.organization_id;
1216
1217
1218
1219 fnd_currency.get_info( x_currency_code,
1220 x_precision,
1221 x_ext_precision,
1222 x_min_acct_unit);
1223
1224 END IF;
1225 ---------------------------------------------------------
1226 -- Calculate Op YLD Variance --
1227 ---------------------------------------------------------
1228
1229 SELECT
1230 NVL(SUM(NVL(tl_scrap_in,0)), 0),
1231 NVL(SUM(NVL(TL_SCRAP_OUT,0)), 0),
1232 NVL(SUM(NVL(TL_SCRAP_VAR,0)), 0)
1233 INTO
1234 x_tl_scrap_in,
1235 x_tl_scrap_out,
1236 x_tl_scrap_var
1237 FROM
1238 WIP_PERIOD_BALANCES
1239 WHERE
1240 wip_entity_id=rec_wip_entity.wip_entity_id
1241 AND organization_id=rec_wip_entity.organization_id
1242 AND acct_period_id <= rec_wip_entity.acct_period_id;
1243
1244 x_scrap_variance := NVL(x_tl_scrap_in, 0) - (NVL(x_tl_scrap_out, 0)
1245 + NVL(x_tl_scrap_var, 0));
1246
1247 ---------------------------------------------------------
1248 -- Perform Accounting for Scrap Variance: --
1249 -- DR CR --
1250 -- EST_SCRAP_VAR_ACCOUNT X --
1251 -- EST_SCRAP_ACCOUNT X --
1252 -- TRANSACTION_TYPE = 6 (Job Close variance) --
1253 -- Acounting Line Type : --
1254 -- 8 (WIP variance) --
1255 -- 7 ( WIP valauation) --
1256 -- from mfg_lookups
1257 ---------------------------------------------------------
1258
1259
1260 IF (x_scrap_variance <> 0 ) THEN
1261
1262 /* Bug #2325980. No need to enter another transaction of type 6 in WT.
1263 Just use the original transaction_id from WCTI. Otherwise there will
1264 be 2 Job Close Variance transactions in WT and hence two lines in the
1265 output of the Job Close Variance report. */
1266
1267
1268 ---------------------------------------------------------
1269 -- Debit EST_SCRAP_VAR_ACCOUNT --
1270 ---------------------------------------------------------
1271
1272
1273 INSERT INTO
1274 WIP_TRANSACTION_ACCOUNTS
1275 (
1276 wip_sub_ledger_id, /* R12 - SLA Distribution Link */
1277 transaction_id,
1278 reference_account,
1279 organization_id,
1280 transaction_date,
1281 wip_entity_id,
1282 accounting_line_type,
1283 base_transaction_value,
1284 last_update_date,
1285 last_updated_by,
1286 creation_date,
1287 created_by,
1288 last_update_login,
1289 request_id,
1290 program_application_id,
1291 program_id,
1292 program_update_date )
1293 VALUES
1294 (
1295 CST_WIP_SUB_LEDGER_ID_S.NEXTVAL,
1296 rec_wip_entity.transaction_id,
1297 rec_wip_entity.est_scrap_var_account,
1298 x_organization_id,
1299 rec_wip_entity.transaction_date,
1300 rec_wip_entity.wip_entity_id,
1301 8,
1302 decode(NVL(x_min_acct_unit, 0), 0, ROUND(x_scrap_variance, x_precision),
1303 ROUND (x_scrap_variance / x_min_acct_unit) * x_min_acct_unit),
1304 sysdate,
1305 i_user_id,
1306 sysdate,
1307 i_user_id,
1308 i_login_id ,
1309 i_req_id ,
1310 i_prg_appl_id,
1311 i_prg_id,
1312 sysdate);
1313 ---------------------------------------------------------
1314 -- Credit EST_SCRAP_ACCOUNT --
1315 ---------------------------------------------------------
1316
1317 INSERT INTO
1318 WIP_TRANSACTION_ACCOUNTS
1319 (
1320 wip_sub_ledger_id, /* R12 - SLA Distribution Link */
1321 transaction_id,
1322 reference_account,
1323 organization_id,
1324 transaction_date,
1325 wip_entity_id,
1326 accounting_line_type,
1327 base_transaction_value,
1328 last_update_date,
1329 last_updated_by,
1330 creation_date,
1331 created_by,
1332 last_update_login,
1333 request_id,
1334 program_application_id,
1335 program_id,
1336 program_update_date )
1337 VALUES
1338 (
1339 CST_WIP_SUB_LEDGER_ID_S.NEXTVAL,
1340 rec_wip_entity.transaction_id,
1341 rec_wip_entity.est_scrap_account,
1342 x_organization_id,
1343 rec_wip_entity.transaction_date,
1344 rec_wip_entity.wip_entity_id,
1345 7,
1346 decode(NVL(x_min_acct_unit, 0), 0, ROUND(-1 *(x_scrap_variance), x_precision),
1347 ROUND (-1 * (x_scrap_variance) / x_min_acct_unit) * x_min_acct_unit),
1348 sysdate,
1349 i_user_id,
1350 sysdate,
1351 i_user_id,
1352 i_login_id ,
1353 i_req_id ,
1354 i_prg_appl_id,
1355 i_prg_id,
1356 sysdate);
1357
1358 END IF;
1359
1360 ---------------------------------------------------------
1361 -- Update WIP_PERIOD_BALANCES --
1362 ---------------------------------------------------------
1363
1364
1365 UPDATE WIP_PERIOD_BALANCES wpb
1366 SET
1367 TL_SCRAP_VAR =
1368 (SELECT SUM( NVL(TL_SCRAP_IN,0)
1369 - NVL(TL_SCRAP_OUT,0)
1370 - decode(wpb2.acct_period_id,wpb.acct_period_id,0,
1371 NVL(TL_SCRAP_VAR,0)))
1372 FROM WIP_PERIOD_BALANCES wpb2
1373 WHERE wpb2.wip_entity_id = wpb.wip_entity_id
1374 AND wpb2.acct_period_id <= wpb.acct_period_id),
1375 last_update_date = sysdate,
1376 last_updated_by = i_user_id,
1377 last_update_login = i_login_id,
1378 request_id = i_req_id ,
1379 program_application_id = i_prg_appl_id,
1380 program_id = i_prg_id,
1381 program_update_date = sysdate
1382
1383
1384 WHERE
1385 organization_id = x_organization_id
1386 AND acct_period_id = rec_wip_entity.acct_period_id
1387 AND wip_entity_id= rec_wip_entity.wip_entity_id;
1388
1389
1390 /* Bug# 2469879. Check if row already exists. There may be a row
1391 in the history table if the job had been closed earlier and then
1392 unclosed. */
1393
1394 SELECT count(*)
1395 INTO x_history_count
1396 FROM WIP_OP_YIELD_HISTORY
1397 WHERE wip_entity_id = rec_wip_entity.wip_entity_id
1398 AND organization_id = x_organization_id
1399 AND acct_period_id = rec_wip_entity.acct_period_id;
1400
1401 IF (x_history_count = 0) THEN
1402
1403 ---------------------------------------------------------
1404 -- INSERT INTO WIP_OP_YIELD_HISTORY --
1405 ---------------------------------------------------------
1406
1407 INSERT INTO WIP_OP_YIELD_HISTORY
1408 (wip_entity_id,
1409 organization_id,
1410 acct_period_id,
1411 est_scrap_absorb_amt,
1412 est_scrap_reverse_amt,
1413 est_scrap_var_amt,
1414 last_update_date,
1415 last_updated_by,
1416 creation_date,
1417 created_by,
1418 last_update_login,
1419 request_id,
1420 program_application_id,
1421 program_id,
1422 program_update_date)
1423 VALUES
1424 (rec_wip_entity.wip_entity_id,
1425 x_organization_id,
1426 rec_wip_entity.acct_period_id,
1427 x_tl_scrap_in,
1428 x_tl_scrap_out,
1429 (x_scrap_variance + x_tl_scrap_var),
1430 sysdate,
1431 i_user_id,
1432 sysdate,
1433 i_user_id,
1434 i_login_id ,
1435 i_req_id ,
1436 i_prg_appl_id,
1437 i_prg_id,
1438 sysdate);
1439 ELSE /* Row exists */
1440
1441 ---------------------------------------------------------
1442 -- UPDATE WIP_OP_YIELD_HISTORY --
1443 ---------------------------------------------------------
1444
1445 UPDATE WIP_OP_YIELD_HISTORY
1446 SET
1447 est_scrap_absorb_amt = x_tl_scrap_in,
1448 est_scrap_reverse_amt = x_tl_scrap_out,
1449 est_scrap_var_amt = (x_scrap_variance + x_tl_scrap_var),
1450 last_update_date = sysdate,
1451 last_updated_by = i_user_id,
1452 last_update_login = i_login_id,
1453 request_id = i_req_id,
1454 program_application_id = i_prg_appl_id,
1455 program_id = i_prg_id,
1456 program_update_date = sysdate
1457 WHERE wip_entity_id = rec_wip_entity.wip_entity_id
1458 AND organization_id = x_organization_id
1459 AND acct_period_id = rec_wip_entity.acct_period_id;
1460
1461 END IF; /* IF x_history_count = 0 */
1462
1463 END IF; /* If ESA is enabled */
1464
1465 END LOOP wip_entity;
1466
1467 return 1;
1468
1469 EXCEPTION
1470 WHEN WSM_ESA_PKG_ERROR THEN
1471 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in WSM_ESA_ENABLED : '||
1472 x_err_num ||' : '|| x_err_msg);
1473 o_err_num := x_err_num;
1474 o_err_msg := 'CSTPOYLD.transact_op_yield_var:' || substr(x_err_msg,1,150);
1475 return 0;
1476 when others then
1477 o_err_num := SQLCODE;
1478 o_err_msg := 'CSTPOYLD.transact_op_yield_var:' || substrb(SQLERRM,1,150);
1479 return 0;
1480 END transact_op_yield_var;
1481 ---------------------------------------------------------------------------
1482 -- FUNCTION --
1483 -- process_sm_op_yld --
1484 -- --
1485 -- DESCRIPTION --
1486 -- Use this function to calculate op yield for jobs involved in split --
1487 -- merge transaction. --
1488 -- --
1489 -- PURPOSE: --
1490 -- Oracle Applications Rel 11i.1 --
1491 -- --
1492 -- PARAMETERS: --
1493 -- i_txn_id :Split Merge Txn Id --
1494 -- RETURNS --
1495 -- 1 : Success --
1496 -- 0 : Failure --
1497 -- --
1498 -- HISTORY: --
1499 -- 02/12/00 Sujit Dalai Created --
1500 ----------------------------------------------------------------------------
1501 FUNCTION process_sm_op_yld ( i_txn_id IN NUMBER,
1502 i_user_id IN NUMBER,
1503 i_login_id IN NUMBER,
1504 i_prg_appl_id IN NUMBER,
1505 i_prg_id IN NUMBER,
1506 i_req_id IN NUMBER,
1507 o_err_num OUT NOCOPY NUMBER,
1508 o_err_code OUT NOCOPY VARCHAR2,
1509 o_err_msg OUT NOCOPY VARCHAR2)
1510
1511 return NUMBER IS
1512
1513
1514 x_op_seq_num Number;
1515 x_pl_cost Number;
1516 x_tl_res_cost Number;
1517 x_tl_osp_cost Number;
1518 x_tl_res_val_ovhd_cost Number;
1519 x_tl_res_unit_ovhd_cost Number;
1520 x_item_lot_ovhd_cost Number;
1521 x_ovhd_cost Number;
1522 x_operation_cost Number;
1523 l_debug VARCHAR2(80);
1524 l_stmt_num NUMBER := 0;
1525
1526 /* Changes for Optional Scrap */
1527 x_est_scrap_acct_flag NUMBER := 0;
1528 x_err_num NUMBER := 0;
1529 x_err_msg VARCHAR2(240) ;
1530 WSM_ESA_PKG_ERROR EXCEPTION;
1531
1532 CURSOR c_wip_entity IS
1533 SELECT sj.wip_entity_id wip_entity_id,
1534 sj.operation_seq_num op_seq_num,
1535 sj.intraoperation_step intra_op_step,
1536 smt.organization_id organization_id,
1537 sj.routing_seq_id routing_seq_id,
1538 smt.transaction_type_id txn_type_id
1539 FROM wsm_sm_starting_jobs sj,
1540 wsm_split_merge_transactions smt
1541 WHERE smt.transaction_id = i_txn_id
1542 AND smt.transaction_type_id In (1, 2, 6)
1543 AND smt.transaction_id = sj.transaction_id
1544 UNION
1545 select rj.wip_entity_id wip_entity_id,
1546 nvl(rj.starting_operation_seq_num,sj.operation_seq_num) op_seq_num,
1547 rj.starting_intraoperation_step intra_op_step,
1548 smt.organization_id organization_id,
1549 rj.common_routing_sequence_id routing_seq_id,
1550 smt.transaction_type_id txn_type_id
1551 from wsm_sm_resulting_jobs rj,
1552 wsm_split_merge_transactions smt,
1553 wsm_sm_starting_jobs sj
1554 where smt.transaction_id = i_txn_id
1555 and smt.transaction_type_id in (1,2,6)
1556 and smt.transaction_id = rj.transaction_id
1557 and smt.transaction_id = sj.transaction_id
1558 and sj.representative_flag = 'Y'
1559 UNION
1560 Select rj.wip_entity_id wip_entity_id,
1561 rj.job_operation_seq_num op_seq_num,
1562 nvl(rj.starting_intraoperation_step, WIP_CONSTANTS.QUEUE) intra_op_step,
1563 smt.organization_id organization_id,
1564 rj.common_routing_sequence_id routing_seq_id,
1565 smt.transaction_type_id txn_type_id
1566 from wsm_sm_resulting_jobs rj,
1567 wsm_split_merge_transactions smt
1568 where smt.transaction_id = i_txn_id
1569 and smt.transaction_type_id = 4
1570 and smt.transaction_id = rj.transaction_id
1571 and rj.job_operation_seq_num is not NULL
1572 /* Jobs prior to 11i.8 would not have JOB_OPERATION_SEQ_NUM
1573 populated. Not modifying the above since this will not
1574 happen in most cases and also is much cleaner performance
1575 wise */
1576 UNION
1577 Select rj.wip_entity_id wip_entity_id,
1578 wo.operation_seq_num op_seq_num,
1579 nvl(rj.starting_intraoperation_step, WIP_CONSTANTS.QUEUE) intra_op_step,
1580 smt.organization_id organization_id,
1581 rj.common_routing_sequence_id routing_seq_id,
1582 smt.transaction_type_id txn_type_id
1583 from wsm_sm_resulting_jobs rj,
1584 wsm_split_merge_transactions smt,
1585 wip_operations wo,
1586 bom_operation_sequences bos
1587 where smt.transaction_id = i_txn_id
1588 and smt.transaction_type_id = 4
1589 and smt.transaction_id = rj.transaction_id
1590 and rj.starting_operation_seq_num = bos.operation_seq_num
1591 and rj.common_routing_sequence_id = bos.routing_sequence_id
1592 and bos.operation_sequence_id = wo.operation_sequence_id
1593 AND bos.EFFECTIVITY_DATE <= smt.transaction_date
1594 AND NVL( bos.DISABLE_DATE, smt.transaction_date + 1) > smt.transaction_date
1595 and wo.wip_entity_id = rj.wip_entity_id
1596 and wo.organization_id = smt.organization_id
1597 and rj.job_operation_seq_num is NULL
1598 order by wip_entity_id;
1599
1600 CURSOR c_operation (p_wip_entity_id Number,
1601 p_op_seq_num Number,
1602 p_organization_id Number) IS
1603 SELECT wo.operation_seq_num,
1604 wdj.start_quantity
1605 FROM wip_operations wo,
1606 wip_discrete_jobs wdj
1607 WHERE wo.wip_entity_id = p_wip_entity_id
1608 AND wo.operation_seq_num <= p_op_seq_num
1609 AND wo.organization_id = p_organization_id
1610 AND wo.wip_entity_id = wdj.wip_entity_id
1611 AND wo.organization_id = wdj.organization_id
1612 ORDER BY wo.operation_seq_num;
1613
1614 BEGIN
1615
1616 x_err_msg := '';
1617 o_err_code := '';
1618 o_err_num := 0;
1619 o_err_msg := '';
1620
1621 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
1622
1623 IF(l_debug = 'Y') THEN
1624 FND_FILE.PUT_LINE(FND_FILE.LOG, 'PROCESS_SM_OP_YIELD <<< ');
1625 END IF;
1626
1627
1628 ---------------------------------------------------------
1629 -- Open wip_entity Cursor --
1630 ---------------------------------------------------------
1631 <<wip_entity>>
1632
1633 FOR rec_wip_entity IN c_wip_entity LOOP
1634 /* Changes for Optional Scrap */
1635 l_stmt_num := 10;
1636 x_est_scrap_acct_flag := WSMPUTIL.WSM_ESA_ENABLED(rec_wip_entity.wip_entity_id,
1637 x_err_num, x_err_msg);
1638
1639 IF (x_est_scrap_acct_flag = 0) THEN
1640 RAISE WSM_ESA_PKG_ERROR;
1641 END IF;
1642
1643 IF x_est_scrap_acct_flag = 1 THEN
1644 x_op_seq_num := rec_wip_entity.op_seq_num;
1645
1646 <<opseq>>
1647 if (l_debug = 'Y') then
1648 fnd_file.put_line(fnd_file.log,'WIP_ENTITY_ID : ' || to_char(rec_wip_entity.wip_entity_id));
1649 fnd_file.put_line(fnd_file.log,'X_OP_SEQ_NUM: ' || to_char(x_op_seq_num));
1650 end if;
1651
1652 FOR rec_opseq IN c_operation (rec_wip_entity.wip_entity_id,
1653 x_op_seq_num,
1654 rec_wip_entity.organization_id) LOOP
1655
1656 if (l_debug = 'Y') then
1657 fnd_file.put_line(fnd_file.log,'OP_SEQ: ' || to_char(rec_opseq.operation_seq_num));
1658 end if;
1659 l_stmt_num := 20;
1660
1661 ---------------------------------------------------------
1662 -- Get privious level cost --
1663 ---------------------------------------------------------
1664 SELECT
1665 NVL(SUM ((NVL(CIC.MATERIAL_COST,0) +
1666 NVL(CIC.MATERIAL_OVERHEAD_COST,0) +
1667 NVL(CIC.RESOURCE_COST,0) +
1668 NVL(CIC.OUTSIDE_PROCESSING_COST,0) +
1669 NVL(CIC.OVERHEAD_COST,0)) * NVL(WRO.COSTED_QUANTITY_ISSUED, 0)), 0)
1670
1671 INTO
1672 x_pl_cost
1673
1674 FROM
1675 wip_requirement_operations WRO,
1676 cst_item_costs CIC
1677 WHERE
1678 CIC.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
1679 AND CIC.ORGANIZATION_ID = WRO.ORGANIZATION_ID
1680 AND CIC.COST_TYPE_ID = 1
1681 AND WRO.WIP_ENTITY_ID = rec_wip_entity.wip_entity_id
1682 AND WRO.OPERATION_SEQ_NUM = rec_opseq.operation_seq_num
1683 AND WRO.ORGANIZATION_ID = rec_wip_entity.organization_id;
1684
1685 ---------------------------------------------------------
1686 -- Get this level Resource and OSP cost --
1687 ---------------------------------------------------------
1688 l_stmt_num := 30;
1689 SELECT
1690 NVL(SUM(DECODE(BR.COST_ELEMENT_ID,
1691 3, DECODE(BR.STANDARD_RATE_FLAG,
1692 1, decode(BR.functional_currency_flag,
1693 1,nvl(WOR.APPLIED_RESOURCE_UNITS,0),
1694 nvl(CRC.RESOURCE_RATE*WOR.APPLIED_RESOURCE_UNITS,0)),
1695 2, nvl(WOR.APPLIED_RESOURCE_VALUE,0)),
1696 0)),0),
1697 NVL(SUM(DECODE(BR.COST_ELEMENT_ID,
1698 4, DECODE(BR.STANDARD_RATE_FLAG,
1699 1, decode(BR.functional_currency_flag,
1700 1,nvl(WOR.APPLIED_RESOURCE_UNITS,0),
1701 nvl(CRC.RESOURCE_RATE*WOR.APPLIED_RESOURCE_UNITS,0)),
1702 2, nvl(WOR.APPLIED_RESOURCE_VALUE,0)),
1703 0)),0)
1704 INTO
1705 x_tl_res_cost,
1706 x_tl_osp_cost
1707 FROM cst_resource_costs CRC,
1708 wip_operation_resources WOR,
1709 bom_resources BR
1710 WHERE
1711 CRC.COST_TYPE_ID(+) = 1
1712 AND CRC.RESOURCE_ID(+) = WOR.RESOURCE_ID
1713 AND WOR.OPERATION_SEQ_NUM = rec_opseq.operation_seq_num
1714 AND BR.RESOURCE_ID = WOR.RESOURCE_ID
1715 AND WOR.WIP_ENTITY_ID = rec_wip_entity.wip_entity_id
1716 AND WOR.ORGANIZATION_ID = rec_wip_entity.organization_id;
1717
1718 ---------------------------------------------------------
1719 -- Calculate overhead cost --
1720 ---------------------------------------------------------
1721 l_stmt_num := 40;
1722
1723 SELECT nvl(sum(WOO.applied_ovhd_value),0)
1724 INTO x_ovhd_cost
1725 FROM wip_operation_overheads WOO
1726 WHERE
1727 WOO.wip_entity_id = rec_wip_entity.wip_entity_id
1728 and WOO.operation_seq_num = rec_opseq.operation_seq_num
1729 and WOO.organization_id = rec_wip_entity.organization_id;
1730
1731 ---------------------------------------------------------
1732 -- Calculate operation_cost and --
1733 -- Update Wip_operation_yields --
1734 ---------------------------------------------------------
1735
1736 x_operation_cost := x_pl_cost +
1737 x_tl_res_cost +
1738 x_tl_osp_cost +
1739 x_ovhd_cost;
1740 if (l_debug = 'Y') then
1741 fnd_file.put_line(fnd_file.log,'PL cost : ' || to_char(x_pl_cost));
1742 fnd_file.put_line(fnd_file.log,'TL res cost: ' || to_char(x_tl_res_cost));
1743 fnd_file.put_line(fnd_file.log,'TL osp cost: ' || to_char(x_tl_osp_cost));
1744 fnd_file.put_line(fnd_file.log,'TL ovh cost: ' || to_char(x_ovhd_cost));
1745 end if;
1746
1747 l_stmt_num := 50;
1748
1749 UPDATE wip_operation_yields
1750 SET operation_cost = x_operation_cost,
1751 status = 1,
1752 last_update_date = sysdate,
1753 last_updated_by = i_user_id,
1754 last_update_login = i_login_id,
1755 request_id = i_req_id ,
1756 program_application_id = i_prg_appl_id,
1757 program_id = i_prg_id,
1758 program_update_date = sysdate
1759 WHERE wip_entity_id = rec_wip_entity.wip_entity_id
1760 AND operation_seq_num = rec_opseq.operation_seq_num
1761 AND organization_id = rec_wip_entity.organization_id;
1762
1763 if (l_debug = 'Y') then
1764 fnd_file.put_line(fnd_file.log,'TOTAL OPN COST: ' || to_char(x_operation_cost));
1765 end if;
1766
1767 END LOOP opseq;
1768
1769 END IF; /* If ESA is enabled */
1770 END LOOP wip_entity;
1771 IF(l_debug = 'Y') THEN
1772 FND_FILE.PUT_LINE(FND_FILE.LOG, 'PROCESS_SM_OP_YIELD >>> ');
1773 END IF;
1774
1775
1776 RETURN 1;
1777 EXCEPTION
1778 WHEN WSM_ESA_PKG_ERROR THEN
1779 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in WSM_ESA_ENABLED : '||
1780 x_err_num ||' : '|| x_err_msg);
1781 o_err_num := x_err_num;
1782 o_err_msg := 'CSTPOYLD.transact_op_yield_var:' ||substr(x_err_msg,1,150);
1783 return 0;
1784
1785 when others then
1786 o_err_num := SQLCODE;
1787 o_err_msg := 'CSTPOYLD.process_sm_op_yld:(' || to_char(l_stmt_num) || ')'||substrb(SQLERRM,1,150);
1788 return 0;
1789 END process_sm_op_yld;
1790
1791 ---------------------------------------------------------------------------
1792 Function cost_update_adjustment (i_org_id IN NUMBER,
1793 i_update_id IN NUMBER,
1794 i_user_id IN NUMBER,
1795 i_login_id IN NUMBER,
1796 i_prg_appl_id IN NUMBER,
1797 i_prg_id IN NUMBER,
1798 i_req_id IN NUMBER,
1799 o_err_num OUT NOCOPY NUMBER,
1800 o_err_code OUT NOCOPY VARCHAR2,
1801 o_err_msg OUT NOCOPY VARCHAR2)
1802 return NUMBER IS
1803 l_adj_value NUMBER;
1804 l_stmt_num NUMBER;
1805 l_err_num NUMBER;
1806 l_err_code VARCHAR2(240);
1807 l_err_msg VARCHAR2(240);
1808 process_error EXCEPTION;
1809
1810 /* Changes for Optional Scrap */
1811 x_est_scrap_acct_flag NUMBER := 0;
1812
1813
1814 /* Add for bug 4171498 */
1815 CURSOR opseq_cur IS
1816 SELECT cscav.WIP_ENTITY,
1817 cscav.OP_SEQ_NUM,
1818 cscav.ADJ_VALUE
1819 FROM ( SELECT wip_entity_id WIP_ENTITY,
1820 operation_seq_num OP_SEQ_NUM,
1821 SUM((NVL(new_unit_cost,0) - NVL(old_unit_cost,0)) * adjustment_quantity) adj_value
1822 FROM cst_std_cost_adj_values
1823 WHERE organization_id = i_org_id
1824 AND cost_update_id = i_update_id
1825 AND transaction_type NOT IN (1, 2, 4, 5)
1826 GROUP BY wip_entity_id, operation_seq_num
1827 HAVING SUM((NVL(new_unit_cost,0) - NVL(old_unit_cost,0)) * adjustment_quantity) <> 0
1828 ) cscav,
1829 wip_entities we
1830 WHERE cscav.wip_entity = we.wip_entity_id
1831 AND we.organization_id = i_org_id
1832 AND we.entity_type = 5
1833 ORDER BY wip_entity, op_seq_num;
1834
1835 BEGIN
1836 l_stmt_num := 0;
1837 l_err_code := '';
1838 l_err_num := 0;
1839 l_err_msg := '';
1840 l_adj_value := 0;
1841
1842 l_stmt_num := 10;
1843
1844 For opseq_rec IN opseq_cur
1845 LOOP
1846
1847 l_stmt_num := 15;
1848 x_est_scrap_acct_flag := WSMPUTIL.WSM_ESA_ENABLED(opseq_rec.WIP_ENTITY,
1849 l_err_num, l_err_msg);
1850
1851 /* Update operation cost in WOY only if ESA is enabled */
1852 IF x_est_scrap_acct_flag = 1 THEN
1853
1854 l_stmt_num := 20;
1855
1856 l_stmt_num := 30;
1857
1858 update wip_operation_yields
1859 set last_update_date = sysdate,
1860 last_updated_by = i_user_id,
1861 last_update_login = i_login_id,
1862 request_id = i_req_id,
1863 program_application_id = i_prg_appl_id,
1864 program_id = i_prg_id,
1865 program_update_date = sysdate,
1866 operation_cost = nvl(operation_cost,0) + nvl(opseq_rec.ADJ_VALUE,0),
1867 status = 1
1868 where organization_id = i_org_id
1869 and wip_entity_id = opseq_rec.WIP_ENTITY
1870 and operation_seq_num = opseq_rec.OP_SEQ_NUM;
1871 end if;
1872
1873 END LOOP;
1874
1875 return 1;
1876
1877 EXCEPTION
1878 when process_error then
1879 o_err_num := l_err_num;
1880 o_err_code := l_err_code;
1881 o_err_msg := l_err_msg;
1882 return 0;
1883
1884 when others then
1885 rollback;
1886 o_err_num := SQLCODE;
1887 o_err_msg := 'CSTPOYLD.cost_update_adjustment: (' || to_char(l_stmt_num) || '):' || substrb(SQLERRM,1,200);
1888 return 0;
1889 END cost_update_adjustment;
1890 -------------------------------------------------------------------------------
1891 end CSTPOYLD;