[Home] [Help]
PACKAGE BODY: APPS.OPIMPXWP
Source
1 PACKAGE BODY OPIMPXWP AS
2 /*$Header: OPIMXWPB.pls 120.1 2005/06/08 18:31:48 appldev $ */
3
4 procedure calc_wip_balance(
5 I_ORG_ID IN NUMBER,
6 I_PUSH_START_INV_TXN_DATE IN DATE,
7 I_PUSH_START_WIP_TXN_DATE IN DATE,
8 I_PUSH_LAST_INV_TXN_ID IN NUMBER,
9 I_PUSH_LAST_WIP_TXN_ID IN NUMBER,
10 I_PUSH_END_TXN_DATE IN DATE,
11 I_FIRST_PUSH IN NUMBER,
12 O_ERR_NUM OUT NOCOPY NUMBER,
13 O_ERR_CODE OUT NOCOPY VARCHAR2,
14 O_ERR_MSG OUT NOCOPY VARCHAR2
15 ) IS
16
17 /*---------------------
18 c_txn_daily_sum
19 ----------------------*/
20 /* Cursor to summarize the total WIP charges from the start date thru the end
21 -- date by item, bom revision and transaction date for the specified organization.
22 -- Notes: The item in this context is the assembly of the job/schedules. Hence,
23 -- components issues for the assembly's job should be included in the WIP charges.
24 -- They should be grouped by the assembly item number derived from
25 -- mmt.transaction_source_id (= we.primary_item_id).
26 */
27
28 CURSOR c_txn_daily_sum is
29 select we.primary_item_id item_id, -- matl charges for discrete jobs
30 wdj.bom_revision b_revision,
31 trunc(mmt.transaction_date) txn_date,
32 sum(nvl(mta.base_transaction_value,0)) wip_txn_val
33 from mtl_transaction_accounts mta,
34 mtl_material_transactions mmt,
35 wip_entities we,
36 wip_discrete_jobs wdj
37 where mmt.transaction_source_type_id = 5
38 and mmt.organization_id = i_org_id
39 and mmt.transaction_source_id = we.wip_entity_id
40 and mmt.transaction_id = mta.transaction_id
41 and mta.accounting_line_type = 7
42 and mmt.transaction_date between i_push_start_inv_txn_date
43 and i_push_end_txn_date
44 and mmt.transaction_id <= i_push_last_inv_txn_id
45 and mmt.transaction_source_id = wdj.wip_entity_id
46 group by trunc(mmt.transaction_date),
47 we.primary_item_id,
48 wdj.bom_revision
49 UNION ALL
50 select we.primary_item_id item_id, -- matl charges for rep. schedules
51 wrs.bom_revision b_revision,
52 trunc(mmt.transaction_date) txn_date,
53 sum(nvl(mta.base_transaction_value,0)) wip_txn_val
54 from mtl_transaction_accounts mta,
55 mtl_material_transactions mmt,
56 wip_entities we,
57 wip_repetitive_schedules wrs
58 where mmt.transaction_source_type_id = 5
59 and mmt.organization_id = i_org_id
60 and mmt.transaction_source_id = we.wip_entity_id
61 and mmt.transaction_id = mta.transaction_id
62 and mta.accounting_line_type = 7
63 and mmt.transaction_date between i_push_start_inv_txn_date
64 and i_push_end_txn_date
65 and mmt.transaction_id <= i_push_last_inv_txn_id
66 and mmt.transaction_source_id = wrs.wip_entity_id
67 group by trunc(mmt.transaction_date),
68 we.primary_item_id,
69 wrs.bom_revision
70 UNION ALL
71 select we.primary_item_id item_id, -- matl charges for flow schedules
72 wfs.bom_revision b_revision,
73 trunc(mmt.transaction_date) txn_date,
74 sum(nvl(mta.base_transaction_value,0)) wip_txn_val
75 from mtl_transaction_accounts mta,
76 mtl_material_transactions mmt,
77 wip_entities we,
78 wip_flow_schedules wfs
79 where mmt.transaction_source_type_id = 5
80 and mmt.organization_id = i_org_id
81 and mmt.transaction_source_id = we.wip_entity_id
82 and mmt.transaction_id = mta.transaction_id
83 and mta.accounting_line_type = 7
84 and mmt.transaction_date between i_push_start_inv_txn_date
85 and i_push_end_txn_date
86 and mmt.transaction_id <= i_push_last_inv_txn_id
87 and mmt.transaction_source_id = wfs.wip_entity_id
88 group by trunc(mmt.transaction_date),
89 we.primary_item_id,
90 wfs.bom_revision
91 UNION ALL
92 select wdj.primary_item_id item_id, -- resource charges for discrete jobs
93 wdj.bom_revision b_revision,
94 trunc(wt.transaction_date) txn_date,
95 sum(nvl(wta.base_transaction_value,0)) wip_txn_val
96 from wip_transactions wt,
97 wip_transaction_accounts wta,
98 wip_discrete_jobs wdj
99 where wt.organization_id = i_org_id
100 and wt.transaction_id = wta.transaction_id
101 and wta.accounting_line_type = 7
102 and wt.transaction_date between i_push_start_wip_txn_date
103 and i_push_end_txn_date
104 and wt.transaction_id <= i_push_last_wip_txn_id
105 and wt.wip_entity_id = wdj.wip_entity_id
106 group by trunc(wt.transaction_date),
107 wdj.primary_item_id,
108 wdj.bom_revision
109 UNION ALL
110 select we.primary_item_id item_id, -- resource charges for rep. schedules
111 wrs.bom_revision b_revision,
112 trunc(wt.transaction_date) txn_date,
113 sum(nvl(wta.base_transaction_value,0)) wip_txn_val
114 from wip_transactions wt,
115 wip_transaction_accounts wta,
116 wip_repetitive_schedules wrs,
117 wip_entities we
118 where wt.organization_id = i_org_id
119 and wt.transaction_id = wta.transaction_id
120 and wta.accounting_line_type = 7
121 and wt.transaction_date between i_push_start_wip_txn_date
122 and i_push_end_txn_date
123 and wt.transaction_id <= i_push_last_wip_txn_id
124 and wt.wip_entity_id = wrs.wip_entity_id
125 and wt.wip_entity_id = we.wip_entity_id
126 group by trunc(wt.transaction_date),
127 we.primary_item_id,
128 wrs.bom_revision
129 UNION ALL
130 select wfs.primary_item_id item_id, -- resource charges for flow schedules
131 wfs.bom_revision b_revision,
132 trunc(wt.transaction_date) txn_date,
133 sum(nvl(wta.base_transaction_value,0)) wip_txn_val
134 from wip_transactions wt,
135 wip_transaction_accounts wta,
136 wip_flow_schedules wfs
137 where wt.organization_id = i_org_id
138 and wt.transaction_id = wta.transaction_id
139 and wta.accounting_line_type = 7
140 and wt.transaction_date between i_push_start_wip_txn_date
141 and i_push_end_txn_date
142 and wt.transaction_id <= i_push_last_wip_txn_id
143 and wt.wip_entity_id = wfs.wip_entity_id
144 group by trunc(wt.transaction_date),
145 wfs.primary_item_id,
146 wfs.bom_revision
147 order by 3,1,2;
148
149 l_count number;
150 l_end_bal number;
151 l_prev_end_bal number;
152 l_last_item_id number;
153 l_last_revision varchar2(3);
154 l_last_txn_date date;
155 l_start_date date;
156 l_last_txn_amt number;
157 l_push_log_key varchar2(240);
158 l_stmt_num number;
159 l_err_num number;
160 l_err_code varchar2(240);
161 l_err_msg varchar2(240);
162 process_error exception;
163 no_process exception;
164
165 BEGIN
166
167 EDW_LOG.PUT_LINE('OPIMPXWP.calc_wip_balances. '
168 || 'Processing org id: '
169 || to_char(i_org_id));
170
171 /*
172 DBMS_OUTPUT.PUT_LINE('OPIMPXWP.calc_wip_balances. '
173 || 'Processing org id: '
174 || to_char(i_org_id));
175 */
176
177 EDW_LOG.PUT_LINE('Start inv txn date: '
178 || to_char(i_push_start_inv_txn_date,'DD-MON-YYYY
179 hh24:mi:ss'));
180 EDW_LOG.PUT_LINE('Start wip txn date: '
181 || to_char(i_push_start_wip_txn_date,'DD-MON-YYYY
182 hh24:mi:ss'));
183 EDW_LOG.PUT_LINE('Txn end date: '
184 || to_char(i_push_end_txn_date,'DD-MON-YYYY
185 hh24:mi:ss'));
186 EDW_LOG.PUT_LINE('End inv txn id: '
187 || to_char(i_push_last_inv_txn_id));
188 EDW_LOG.PUT_LINE('End wip txn id: '
189 || to_char(i_push_last_wip_txn_id));
190
191 -- initialize local variables
192 l_stmt_num := 0;
193 l_err_num := 0;
194 l_err_code := '';
195 l_err_msg := '';
196
197 -- Proceed only if we have at least one of the start date.
198 if i_push_start_inv_txn_date is null
199 and i_push_start_wip_txn_date is null then
200 raise no_process;
201 end if;
202
203 /*--------------------------------------------------------------
204 Check if this is the first push for the organization.
205 If so, we need to do the following:
206 - delete existing WIP opi_ids_push_log rows within the process
207 date ranges to avoid duplication in case of repush
208 - calculate the beginning balance at the start date
209 - update beginning balances with daily WIP transactions within
210 the process date ranges.
211 If it is not a first push and WIP rows exist within the date
212 range, it is a repush. We need to set the push_flag to null
213 to indicate that these rows are repushed.
214 ------------------------------------------------------------------*/
215
216 if i_push_start_inv_txn_date < i_push_start_wip_txn_date then
217 l_start_date := i_push_start_inv_txn_date;
218 else
219 l_start_date := i_push_start_wip_txn_date;
220 end if;
221
222 l_stmt_num := 10;
223 if i_first_push > 0 then -- first push process
224
225 l_stmt_num := 15;
226 delete opi_ids_push_log
227 where organization_id = i_org_id
228 and trx_date between l_start_date and i_push_end_txn_date
229 and cost_group_id is null
230 and subinventory_code is null
231 and locator_id is null
232 and lot_number is null
233 and end_wip_val_b is not null
234 and end_wip_qty is not null;
235
236 /*
237 DBMS_OUTPUT.PUT_LINE('call calc_beginning_wip');
238 */
239
240 calc_beginning_wip(
241 i_org_id,
242 i_push_start_wip_txn_date,
243 l_err_num,
244 l_err_code,
245 l_err_msg);
246 if l_err_num <> 0 then
247 raise process_error;
248 end if;
249 else
250 update opi_ids_push_log
251 set push_flag = null,
252 last_update_date = sysdate
253 where organization_id = i_org_id
254 and trx_date between l_start_date and i_push_end_txn_date
255 and cost_group_id is null
256 and subinventory_code is null
257 and locator_id is null
258 and lot_number is null;
259 end if; -- end first push
260
261 l_stmt_num := 20;
262 l_last_item_id := 0;
263 l_last_revision := null;
264 l_last_txn_date := null;
265 l_last_txn_amt := 0;
266
267 /*
268 DBMS_OUTPUT.PUT_LINE('start c_txn_daily_sum loop');
269 */
270
271 for c_txn_daily_sum_rec in c_txn_daily_sum loop
272 if c_txn_daily_sum_rec.wip_txn_val <> 0 then
273 l_push_log_key := c_txn_daily_sum_rec.txn_date
274 || '-'
275 || c_txn_daily_sum_rec.item_id
276 || '-'
277 || i_org_id
278 || '-'
279 || '-' -- no cost group
280 || c_txn_daily_sum_rec.b_revision
281 || '---'; -- no lot,sub or locator
282
283 /*
284 DBMS_OUTPUT.PUT_LINE('push key log: ' || l_push_log_key);
285 DBMS_OUTPUT.PUT_LINE('call update daily_wip');
286 DBMS_OUTPUT.PUT_LINE('item: '|| to_char(c_txn_daily_sum_rec.item_id));
287 DBMS_OUTPUT.PUT_LINE('rev: ' || c_txn_daily_sum_rec.b_revision);
288 DBMS_OUTPUT.PUT_LINE('txn date: ' || to_char(trunc(c_txn_daily_sum_rec.txn_date)));
289 DBMS_OUTPUT.PUT_LINE('value: ' || to_char(c_txn_daily_sum_rec.wip_txn_val));
290 */
291
292 l_stmt_num := 30;
293 update_daily_wip(l_push_log_key,
294 i_org_id,
295 c_txn_daily_sum_rec.item_id,
296 c_txn_daily_sum_rec.b_revision,
297 c_txn_daily_sum_rec.txn_date,
298 c_txn_daily_sum_rec.wip_txn_val,
299 l_err_num,
300 l_err_code,
301 l_err_msg);
302
303 if l_err_num <> 0 then
304 raise process_error;
305 end if;
306
307 end if; -- end checking wip_txn_val
308 end loop; -- c_txn_daily_sum
309
310 -- At the beginning of the process, opi_ids_push_log.push_flag is
311 -- set to null to ensure there is no balance duplication. Since
312 -- potentially some keys may not have transactions at the beginning
313 -- of the date range, their push flag remain at null. They should be
314 -- reset to 1 to make them available to be pushed.
315
316 l_stmt_num := 40;
317 update opi_ids_push_log
318 set push_flag = 1,
319 last_update_date = sysdate
320 where organization_id = i_org_id
321 and trx_date between l_start_date and i_push_end_txn_date
322 and cost_group_id is null
323 and subinventory_code is null
324 and locator_id is null
325 and lot_number is null
326 and push_flag is null;
327
328
329 EXCEPTION
330 when no_process then
331 o_err_num := 0;
332 o_err_code := '';
333 o_err_msg := '';
334 EDW_LOG.PUT_LINE('Org id: ' || to_char(i_org_id));
335 EDW_LOG.PUT_LINE('OPIMPXWP.calc_wip_balance - No WIP data to extract');
336 when process_error then
337 o_err_num := l_err_num;
338 o_err_code := l_err_code;
339 o_err_msg := 'OPIMPXWP.calc_wip_balance ('
340 || to_char(l_stmt_num)
341 || ')';
342
343 /*
344 DBMS_OUTPUT.PUT_LINE('OPIMPXWP.calc_wip_balance ('
345 || to_char(l_stmt_num)
346 || ')');
347 */
348
349 when others then
350 o_err_num := SQLCODE;
351 o_err_msg := 'OPIMPXWP.calc_wip_balance ('
352 || to_char(l_stmt_num)
353 || '): '
354 || substr(SQLERRM, 1,200);
355
356 /*
357 DBMS_OUTPUT.PUT_LINE( 'OPIMPXWP.calc_wip_balance ('
358 || to_char(l_stmt_num)
359 || '): '
360 || substr(SQLERRM, 1,200));
361 */
362
363 END calc_wip_balance;
364
365 /*******************************************************************
366 ** PROCEDURE
367 ** calc_beginning_wip
368 **
369 ** This procedure calculates the beginning WIP value and quantity
370 ** balances for a specific organization. It will do the following:
371 ** - calculate the current balances
372 ** - backtrack material and resource transactions up to the start
373 ** transaction id for mmt and transaction date for wta.
374 ********************************************************************/
375
376 procedure calc_beginning_wip(
377 i_org_id IN NUMBER,
378 i_push_start_wip_txn_date IN DATE,
379 o_err_num OUT NOCOPY NUMBER,
380 o_err_code OUT NOCOPY VARCHAR2,
381 o_err_msg OUT NOCOPY VARCHAR2
382 ) IS
383 l_stmt_num number;
384 l_err_num number;
385 l_err_code varchar2(240);
386 l_err_msg varchar2(240);
387 l_push_log_key varchar2(240);
388 l_push_log_count number;
389 l_update_flag number;
390 l_curr_date date;
391 process_error exception;
392
393 /*-----------------------
394 cursor c_curr_bal
395 -----------------------*/
396 -- cursor to collect current WIP balances from wip_period_balances (WPB)
397 -- by item and bom revision for a specified organization.
398 -- We assume that the current WIP balance for flow schedules will allways
399 -- be zero because flow schedules are maintained with work-order-less
400 -- completion where all transactions are backflushed.
401
402 cursor c_curr_bal is
403 select wdj.primary_item_id item_id, -- curr bal. for discrete jobs
404 wdj.bom_revision b_revision,
405 sum(nvl(tl_resource_in,0) - nvl(tl_resource_out,0)
406 + nvl(tl_overhead_in,0) - nvl(tl_overhead_out,0)
407 + nvl(tl_outside_processing_in,0) - nvl(tl_outside_processing_out,0)
408 + nvl(pl_material_in,0) - nvl(pl_material_out,0)
409 + nvl(pl_material_overhead_in,0) - nvl(pl_material_overhead_out,0)
410 + nvl(pl_resource_in,0) - nvl(pl_resource_out,0)
411 + nvl(pl_overhead_in,0) - nvl(pl_overhead_out,0)
412 + nvl(pl_outside_processing_in,0) - nvl(pl_outside_processing_out,0)
413 - nvl(tl_material_var,0)
414 - nvl(tl_material_overhead_var,0)
415 - nvl(tl_resource_var,0)
416 - nvl(tl_outside_processing_var,0)
417 - nvl(tl_overhead_var,0)
418 - nvl(pl_material_var,0)
419 - nvl(pl_material_overhead_var,0)
420 - nvl(pl_resource_var,0)
421 - nvl(pl_outside_processing_var,0)
422 - nvl(pl_overhead_var,0)) curr_wip_bal
423 from wip_period_balances wpb,
424 wip_discrete_jobs wdj
425 where wpb.wip_entity_id = wdj.wip_entity_id
426 and wdj.status_type in (3,4,5,6,14,15)
427 -- released, complete, complete no charge, on hold,
428 -- pending close, failed close respectively.
429 and wpb.organization_id = wdj.organization_id
430 and wdj.organization_id = i_org_id
431 group by wdj.primary_item_id,
432 wdj.bom_revision
433 UNION ALL
434 select we.primary_item_id item_id, -- current bal. for repetitive schedules
435 wrs.bom_revision b_revision,
436 sum(nvl(tl_resource_in,0) - nvl(tl_resource_out,0)
437 + nvl(tl_overhead_in,0) - nvl(tl_overhead_out,0)
438 + nvl(tl_outside_processing_in,0) - nvl(tl_outside_processing_out,0)
439 + nvl(pl_material_in,0) - nvl(pl_material_out,0)
440 + nvl(pl_material_overhead_in,0) - nvl(pl_material_overhead_out,0)
441 + nvl(pl_resource_in,0) - nvl(pl_resource_out,0)
442 + nvl(pl_overhead_in,0) - nvl(pl_overhead_out,0)
443 + nvl(pl_outside_processing_in,0) - nvl(pl_outside_processing_out,0)
444 - nvl(tl_material_var,0)
445 - nvl(tl_material_overhead_var,0)
446 - nvl(tl_resource_var,0)
447 - nvl(tl_outside_processing_var,0)
448 - nvl(tl_overhead_var,0)
449 - nvl(pl_material_var,0)
450 - nvl(pl_material_overhead_var,0)
451 - nvl(pl_resource_var,0)
452 - nvl(pl_outside_processing_var,0)
453 - nvl(pl_overhead_var,0)) curr_wip_bal
454 from wip_period_balances wpb,
455 wip_repetitive_schedules wrs,
456 wip_entities we
457 where wpb.wip_entity_id = wrs.wip_entity_id
458 and wpb.wip_entity_id = we.wip_entity_id
459 and wrs.status_type in (3,4,5,6,14,15)
460 -- released, complete, complete no charge, on hold,
461 -- pending close, failed close respectively.
462 and wrs.organization_id = i_org_id
463 and wpb.organization_id = wrs.organization_id
464 and we.organization_id = wrs.organization_id
465 group by we.primary_item_id,
466 wrs.bom_revision
467 order by 1, 2;
468
469 /*------------------------
470 cursor c_txn_sum
471 -------------------------*/
472 -- Cursor to summarize the total WIP charges from the start date thru the current
473 -- date by item, by bom revision for the specified organization.
474 -- Notes: The item in this context is the assembly of the job/schedules. Hence,
475 -- components issues for the assembly's job should be included in the WIP charges.
476 -- They should be grouped by the assembly item number derived from
477 -- mmt.transaction_source_id (= we.primary_item_id).
478 cursor c_txn_sum(c_end_date DATE) is
479 select we.primary_item_id item_id, -- matl charges for discrete jobs
480 wdj.bom_revision b_revision,
481 sum(nvl(mta.base_transaction_value,0)) wip_txn_val
482 from mtl_transaction_accounts mta,
483 mtl_material_transactions mmt,
484 wip_entities we,
485 wip_discrete_jobs wdj
486 where mmt.transaction_source_type_id = 5
487 and mmt.organization_id = i_org_id
488 and mmt.transaction_source_id = we.wip_entity_id
489 and mmt.transaction_id = mta.transaction_id
490 and mta.accounting_line_type = 7
491 and mmt.transaction_date between i_push_start_wip_txn_date
492 and c_end_date
493 and mmt.transaction_source_id = wdj.wip_entity_id
494 group by we.primary_item_id,
495 wdj.bom_revision
496 UNION ALL
497 select we.primary_item_id item_id, -- matl charges for rep. schedules
498 wrs.bom_revision b_revision,
499 sum(nvl(mta.base_transaction_value,0)) wip_txn_val
500 from mtl_transaction_accounts mta,
501 mtl_material_transactions mmt,
502 wip_entities we,
503 wip_repetitive_schedules wrs
504 where mmt.transaction_source_type_id = 5
505 and mmt.organization_id = i_org_id
506 and mmt.transaction_source_id = we.wip_entity_id
507 and mmt.transaction_id = mta.transaction_id
508 and mta.accounting_line_type = 7
509 and mmt.transaction_date between i_push_start_wip_txn_date
510 and c_end_date
511 and mmt.transaction_source_id = wrs.wip_entity_id
512 group by we.primary_item_id,
513 wrs.bom_revision
514 UNION ALL
515 select we.primary_item_id item_id, -- matl charges for flow schedules
516 wfs.bom_revision b_revision,
517 sum(nvl(mta.base_transaction_value,0)) wip_txn_val
518 from mtl_transaction_accounts mta,
519 mtl_material_transactions mmt,
520 wip_entities we,
521 wip_flow_schedules wfs
522 where mmt.transaction_source_type_id = 5
523 and mmt.organization_id = i_org_id
524 and mmt.transaction_source_id = we.wip_entity_id
525 and mmt.transaction_id = mta.transaction_id
526 and mta.accounting_line_type = 7
527 and mmt.transaction_date between i_push_start_wip_txn_date
528 and c_end_date
529 and mmt.transaction_source_id = wfs.wip_entity_id
530 group by we.primary_item_id,
531 wfs.bom_revision
532 UNION ALL
533 select wdj.primary_item_id item_id, -- resource charges for discrete jobs
534 wdj.bom_revision b_revision,
535 sum(nvl(wta.base_transaction_value,0)) wip_txn_val
536 from wip_transactions wt,
537 wip_transaction_accounts wta,
538 wip_discrete_jobs wdj
539 where wt.organization_id = i_org_id
540 and wt.transaction_id = wta.transaction_id
541 and wta.accounting_line_type = 7
542 and wt.transaction_date between i_push_start_wip_txn_date
543 and c_end_date
544 and wt.wip_entity_id = wdj.wip_entity_id
545 group by wdj.primary_item_id,
546 wdj.bom_revision
547 UNION ALL
548 select we.primary_item_id item_id, -- resource charges for rep. schedules
549 wrs.bom_revision b_revision,
550 sum(nvl(wta.base_transaction_value,0)) wip_txn_val
551 from wip_transactions wt,
552 wip_transaction_accounts wta,
553 wip_repetitive_schedules wrs,
554 wip_entities we
555 where wt.organization_id = i_org_id
556 and wt.transaction_id = wta.transaction_id
557 and wta.accounting_line_type = 7
558 and wt.transaction_date between i_push_start_wip_txn_date
559 and c_end_date
560 and wt.wip_entity_id = wrs.wip_entity_id
561 and wt.wip_entity_id = we.wip_entity_id
562 group by we.primary_item_id,
563 wrs.bom_revision
564 UNION ALL
565 select wfs.primary_item_id item_id, -- resource charges for flow schedules
566 wfs.bom_revision b_revision,
567 sum(nvl(wta.base_transaction_value,0)) wip_txn_val
568 from wip_transactions wt,
569 wip_transaction_accounts wta,
570 wip_flow_schedules wfs
571 where wt.organization_id = i_org_id
572 and wt.transaction_id = wta.transaction_id
573 and wta.accounting_line_type = 7
574 and wt.transaction_date between i_push_start_wip_txn_date
575 and c_end_date
576 and wt.wip_entity_id = wfs.wip_entity_id
577 group by wfs.primary_item_id,
578 wfs.bom_revision
579 order by 1,2;
580
581 BEGIN
582 -- Initialize local variables
583 l_stmt_num := 0;
584 l_err_num := 0;
585 l_err_code := '';
586 l_err_msg := '';
587 l_push_log_key := null;
588
589 l_stmt_num := 10;
590
591 EDW_LOG.PUT_LINE('Processing (OPIMPXWP.calc_beginning_wip)...');
592
593 /*===============================================================================
594 -- FIRST, get current wip balance and load it to push log with start date in key.
595 ================================================================================*/
596 select sysdate into l_curr_date
597 from dual;
598
599 /*
600 DBMS_OUTPUT.PUT_LINE('calc_beg_bal: start c_curr_bal loop');
601 */
602
603 for c_curr_bal_rec in c_curr_bal loop
604
605 if c_curr_bal_rec.curr_wip_bal <> 0 then
606 l_push_log_key := null;
607 l_push_log_key := trunc(i_push_start_wip_txn_date)
608 || '-'
609 || c_curr_bal_rec.item_id
610 || '-'
611 || i_org_id
612 || '-'
613 || '-' -- no cost group
614 || c_curr_bal_rec.b_revision
615 || '---'; -- no lot,sub or locator
616
617
618 l_stmt_num := 10;
619
620 /*
621 DBMS_OUTPUT.PUT_LINE('calc_beg_bal - call upd_first_push_wip');
622 DBMS_OUTPUT.PUT_LINE('key: ' || l_push_log_key);
623 DBMS_OUTPUT.PUT_LINE('c_curr_bal_rec.curr_wip_bal: '
624 || to_char(c_curr_bal_rec.curr_wip_bal));
625 */
626
627 upd_first_push_wip(l_push_log_key,
628 i_org_id,
629 c_curr_bal_rec.item_id,
630 c_curr_bal_rec.b_revision,
631 i_push_start_wip_txn_date,
632 c_curr_bal_rec.curr_wip_bal,
633 1, -- add to wip balance
634 l_err_num,
635 l_err_code,
636 l_err_msg);
637
638 if l_err_num <> 0 then
639 raise process_error;
640 end if;
641
642 end if; -- end checking curr_wip_bal
643 end loop; -- end c_curr_bal cursor loop
644
645 /*=============================================================
646 -- Then, net transactions from start date thru sysdate against
647 -- current wip balance to come up with beginning balance.
648 ===============================================================*/
649
650 /*
651 DBMS_OUTPUT.PUT_LINE('Net transactions');
652 */
653
654 for c_txn_sum_rec in c_txn_sum(l_curr_date) loop
655
656 if c_txn_sum_rec.wip_txn_val <> 0 then
657 l_push_log_key := null;
658 l_push_log_key := trunc(i_push_start_wip_txn_date)
659 || '-'
660 || c_txn_sum_rec.item_id
661 || '-'
662 || i_org_id
663 || '-'
664 || '-' -- no cost group
665 || c_txn_sum_rec.b_revision
666 || '---'; -- no lot,sub or locator
667
668 /*
669 DBMS_OUTPUT.PUT_LINE('key: ' || l_push_log_key);
670 DBMS_OUTPUT.PUT_LINE('c_txn_sum_rec.wip_txn_val:'
671 || to_char(c_txn_sum_rec.wip_txn_val));
672 DBMS_OUTPUT.PUT_LINE('net trxn - call upd_first_push_wip...');
673 */
674
675 l_stmt_num := 20;
676 upd_first_push_wip(l_push_log_key,
677 i_org_id,
678 c_txn_sum_rec.item_id,
679 c_txn_sum_rec.b_revision,
680 i_push_start_wip_txn_date,
681 c_txn_sum_rec.wip_txn_val,
682 2, -- substract to update beginning balance
683 l_err_num,
684 l_err_code,
685 l_err_msg);
686 if l_err_num <> 0 then
687 raise process_error;
688 end if;
689
690 end if;
691 end loop; -- end c_txn_sum cursor loop
692
693 -- Delete rows with no WIP balances. WIP rows will not have subinv code
694 -- and the only INV rows that have no subinv code should be those created
695 -- for in-transit or cost update. Therefore, it should be safe to delete
696 -- rows that meet the following where clause conditions without running
697 -- the risk of deleting rows inserted by INV
698
699 delete from opi_ids_push_log
700 where beg_wip_val_b = 0
701 and end_wip_val_b = 0
702 and subinventory_code is null
703 and push_flag = 1 -- available to be pushed
704 and period_flag is null -- not period end
705 and nvl(beg_int_val_b,0) = 0
706 and nvl(end_int_val_b,0) = 0
707 and nvl(beg_onh_val_b,0) = 0
708 and nvl(end_onh_val_b,0) = 0
709 and nvl(total_rec_val_b,0) = 0
710 and nvl(tot_issues_val_b,0) = 0;
711
712 EXCEPTION
713 when process_error then
714 o_err_num := l_err_num;
715 o_err_code := l_err_code;
716 o_err_msg := l_err_msg;
717
718 /*
719 DBMS_OUTPUT.PUT_LINE('errnum: ' || to_char(o_err_num)
720 || ', errcode: ' || o_err_code);
721 DBMS_OUTPUT.PUT_LINE('errmsg: ' || o_err_msg);
722 */
723
724 when others then
725 o_err_num := SQLCODE;
726 o_err_msg := 'OPIMPXWP.calc_beginning_wip ('
727 || to_char(l_stmt_num)
728 || '): '
729 || substr(SQLERRM, 1,200);
730
731 /*
732 DBMS_OUTPUT.PUT_LINE('errnum: ' || to_char(o_err_num)
733 || ', errcode: ' || o_err_code);
734 DBMS_OUTPUT.PUT_LINE('errmsg: ' || o_err_msg);
735 */
736
737 END calc_beginning_wip;
738
739 procedure upd_first_push_wip(
740 i_ids_key IN VARCHAR2,
741 i_org_id IN NUMBER,
742 i_item_id IN NUMBER,
743 i_revision IN VARCHAR2,
744 i_txn_date IN DATE,
745 i_wip_amount IN NUMBER,
746 i_update_flag IN NUMBER, -- (1=update bal , 2=substract from bal)
747 o_err_num OUT NOCOPY NUMBER,
748 o_err_code OUT NOCOPY VARCHAR2,
749 o_err_msg OUT NOCOPY VARCHAR2
750 ) IS
751
752 l_push_log_count number;
753 l_item_status varchar2(10);
754 l_item_type varchar2(30);
755 l_base_uom varchar2(3);
756 l_wip_amount number;
757 l_stmt_num number;
758 l_err_num number;
759 l_err_code varchar2(240);
760 l_err_msg varchar2(240);
761 process_error exception;
762
763 BEGIN
764
765 l_push_log_count := 0;
766 l_err_num := 0;
767 l_err_code := '';
768 l_err_msg := '';
769
770 -- check i_update_flag to passed the correct signed amount
771 if i_update_flag = 1 then
772 l_wip_amount := i_wip_amount;
773 else
774 l_wip_amount := -1 * i_wip_amount;
775 end if;
776
777 -- check if there is already a row for key (maybe a row has already been inserted by
778 -- calc_inv_balances procedure). If yes, update/revise beg_wip_val_b column; otherwise,
779 -- insert a row, populating beg_wip_val_b.
780
781 l_stmt_num := 10;
782
783 select count(*)
784 into l_push_log_count
785 from opi_ids_push_log ipl
786 where ipl.ids_key = i_ids_key;
787
788 if l_push_log_count <> 0 then -- check existing row
789 l_stmt_num := 20;
790
791 update opi_ids_push_log ipl
792 set beg_wip_val_b =
793 nvl(ipl.beg_wip_val_b,0) + nvl(l_wip_amount,0),
794 end_wip_val_b =
795 nvl(ipl.end_wip_val_b,0) + nvl(l_wip_amount,0),
796 avg_wip_val_b =
797 (nvl(ipl.beg_wip_val_b,0) + nvl(l_wip_amount,0)
798 + nvl(ipl.end_wip_val_b,0) + nvl(l_wip_amount,0))
799 / 2,
800 ipl.push_flag = 1,
801 ipl.last_update_date = sysdate
802 where ipl.ids_key = i_ids_key;
803 else -- no existing row
804 l_stmt_num := 30;
805 if nvl(i_item_id,0) <> 0 then
806 select msi.inventory_item_status_code,
807 msi.item_type,
808 msi.primary_uom_code
809 into l_item_status,
810 l_item_type,
811 l_base_uom
812 from mtl_system_items msi
813 where msi.organization_id = i_org_id
814 and msi.inventory_item_id = i_item_id;
815 end if;
816
817 /*
818 DBMS_OUTPUT.PUT_LINE('daily update - call insert_upd, key =' || i_ids_key);
819 */
820
821 OPIMPXIN.Insert_update_push_log(
822 i_txn_date,
823 i_org_id,
824 i_item_id,
825 null, -- cost group id
826 i_revision,
827 null, -- lot number
828 null, -- subinventory code
829 null, -- locator
830 l_item_status,
831 l_item_type,
832 l_base_uom,
833 'beg_wip_qty', -- p_col_nam1
834 0, -- p_total1
835 'beg_wip_val_b', -- p_col_nam2
836 l_wip_amount, -- p_total2
837 'end_wip_qty', -- p_col_nam3
838 0, -- p_total3
839 'end_wip_val_b', -- p_col_nam4
840 l_wip_amount, -- p_total4
841 'avg_wip_qty', -- p_col_nam5
842 0, -- p_total5
843 'avg_wip_val_b', -- p_col_nam6
844 l_wip_amount, -- p_total6
845 2, -- selector
846 l_err_num); -- l_status
847 if l_err_num <> 0 then
848 EDW_LOG.PUT_LINE('Error calling OPIMPXIN.Insert_update_push_log');
849 raise process_error;
850 end if;
851 end if; -- end no existing row
852
853 EXCEPTION
854 when process_error then
855 EDW_LOG.PUT_LINE('OPIMPXWP.upd_first_push_wip - ');
856 EDW_LOG.PUT_LINE('Error processing (OPIMPXWP.upd_first_push_wip)...');
857 EDW_LOG.PUT_LINE('Error Num= ' || to_char(l_err_num));
858 EDW_LOG.PUT_LINE('Statement Num= ' || to_char(l_stmt_num));
859
860 when no_data_found then
861 EDW_LOG.PUT_LINE('OPIMPXWP.upd_first_push_wip - stmt: '
862 || to_char(l_stmt_num));
863 EDW_LOG.PUT_LINE('No item in MSI - Item id: ' || to_char(i_item_id)
864 || 'org id: ' || to_char(i_org_id));
865
866 when others then
867 o_err_num := SQLCODE;
868 o_err_msg := 'OPIMPXWP.upd_first_push_wip ('
869 || to_char(l_stmt_num)
870 || '): '
871 || substr(SQLERRM, 1,200);
872
873 END upd_first_push_wip;
874
875 FUNCTION get_prev_end_bal(
876 i_ids_key IN VARCHAR2,
877 i_org_id IN NUMBER,
878 i_item_id IN NUMBER,
879 i_revision IN VARCHAR2,
880 i_txn_date IN DATE,
881 o_err_num OUT NOCOPY NUMBER,
882 o_err_code OUT NOCOPY VARCHAR2,
883 o_err_msg OUT NOCOPY VARCHAR2
884 ) return number IS
885
886 l_trx_date date;
887 l_ids_key varchar2(240);
888 l_return_val number;
889 l_stmt_num number;
890 l_err_num number;
891 l_err_code varchar2(240);
892 l_err_msg varchar2(240);
893
894 BEGIN
895
896 l_trx_date := null;
897 l_err_num := 0;
898 l_err_code := '';
899 l_err_msg := '';
900 l_return_val := 0;
901
902 -- Get the previous day that has balances.
903 select max(trx_date)
904 into l_trx_date
905 from opi_ids_push_log ipl
906 where ipl.organization_id = i_org_id
907 and ipl.inventory_item_id = i_item_id
908 and ipl.revision = i_revision
909 and ipl.trx_date < i_txn_date
910 and ipl.cost_group_id is null
911 and ipl.lot_number is null
912 and ipl.subinventory_code is null
913 and ipl.locator_id is null;
914
915 l_ids_key := l_trx_date
916 || '-'
917 || i_item_id
918 || '-'
919 || i_org_id
920 || '-'
921 || '-' -- no cost group
922 || i_revision
923 || '---'; -- no lot,sub or locator
924
925 /*
926 DBMS_OUTPUT.PUT_LINE('prev ids key: ' || l_ids_key);
927 */
928
929 -- ending wip balance of previous day.
930 select Nvl(end_wip_val_b,0)
931 into l_return_val
932 from opi_ids_push_log ipl
933 where ipl.ids_key = l_ids_key;
934
935 return l_return_val;
936
937 EXCEPTION
938 when no_data_found then
939 l_return_val := 0;
940 return l_return_val;
941 when others then
942 o_err_num := SQLCODE;
943 o_err_msg := 'OPIMPXWP.get_prev_end_bal ('
944 || to_char(l_stmt_num)
945 || '): '
946 || substr(SQLERRM, 1,200);
947
948 END get_prev_end_bal;
949
950 PROCEDURE update_daily_wip(
951 i_ids_key IN VARCHAR2,
952 i_org_id IN NUMBER,
953 i_item_id IN NUMBER,
954 i_revision IN VARCHAR2,
955 i_txn_date IN DATE,
956 i_wip_amount IN NUMBER,
957 o_err_num OUT NOCOPY NUMBER,
958 o_err_code OUT NOCOPY VARCHAR2,
959 o_err_msg OUT NOCOPY VARCHAR2
960 ) IS
961
962 l_push_log_key varchar2(240);
963 l_prev_end_bal number;
964 l_start_value number;
965 l_end_value number;
966 l_avg_value number;
967 l_item_status varchar2(10);
968 l_item_type varchar2(30);
969 l_base_uom varchar2(3);
970 l_ipl_count number;
971 l_stmt_num number;
972 l_err_num number;
973 l_err_code varchar2(240);
974 l_err_msg varchar2(240);
975 l_push_flag number;
976 process_error exception;
977
978 BEGIN
979
980 l_prev_end_bal := 0;
981 l_start_value := 0;
982 l_end_value := 0;
983 l_ipl_count := 0;
984 l_err_num := 0;
985 l_err_code := '';
986 l_err_msg := '';
987
988 -- Get item status and item type.
989 l_stmt_num := 10;
990 if nvl(i_item_id,0) <> 0 then
991 select msi.inventory_item_status_code,
992 msi.item_type,
993 msi.primary_uom_code
994 into l_item_status,
995 l_item_type,
996 l_base_uom
997 from mtl_system_items msi
998 where msi.organization_id = i_org_id
999 and msi.inventory_item_id = i_item_id;
1000 end if;
1001
1002 -- Check if row exists
1003
1004 l_ipl_count := 0;
1005 select count(*)
1006 into l_ipl_count
1007 from opi_ids_push_log ipl
1008 where ipl.ids_key = i_ids_key;
1009
1010 -- If row exists and it's a repushed row, make sure we get the previous ending
1011 -- balance for beg bal. If there is no prev. ending bal, it is probably the
1012 -- very first WIP row for key. In this case, leave the current beg bal alone.
1013
1014 if l_ipl_count <> 0 then -- have existing push log row
1015 select push_flag, Nvl(beg_wip_val_b,0), Nvl(end_wip_val_b,0)
1016 into l_push_flag, l_start_value,l_end_value
1017 from opi_ids_push_log ipl
1018 where ipl.ids_key = i_ids_key;
1019 l_end_value := nvl(l_end_value,0) + nvl(i_wip_amount,0);
1020
1021 if l_push_flag is null then -- repushed row
1022 l_stmt_num := 11;
1023 l_prev_end_bal := get_prev_end_bal(
1024 i_ids_key,
1025 i_org_id,
1026 i_item_id,
1027 i_revision,
1028 i_txn_date,
1029 l_err_num,
1030 l_err_code,
1031 l_err_msg
1032 );
1033 if l_err_num <> 0 then
1034 raise process_error;
1035 end if;
1036
1037 if l_prev_end_bal <> 0 then
1038 l_start_value := l_prev_end_bal;
1039 end if;
1040
1041 l_end_value := l_start_value + nvl(i_wip_amount,0);
1042 end if; -- end checking l_push_flag
1043
1044 /*
1045 DBMS_OUTPUT.PUT_LINE('daily upd - upd key: ' || i_ids_key);
1046 */
1047 l_stmt_num := 12;
1048 update opi_ids_push_log ipl
1049 set ipl.beg_wip_val_b = l_start_value,
1050 ipl.end_wip_val_b = l_end_value,
1051 ipl.avg_wip_val_b = (l_start_value + l_end_value) / 2,
1052 ipl.push_flag = 1,
1053 ipl.last_update_date = sysdate
1054 where ipl.ids_key = i_ids_key;
1055
1056 else -- no push log row
1057
1058 -- Get previous wip day balance
1059 l_stmt_num := 20;
1060 l_prev_end_bal := get_prev_end_bal(
1061 i_ids_key,
1062 i_org_id,
1063 i_item_id,
1064 i_revision,
1065 i_txn_date,
1066 l_err_num,
1067 l_err_code,
1068 l_err_msg
1069 );
1070 if l_err_num <> 0 then
1071 raise process_error;
1072 end if;
1073
1074 -- calculate wip values and quantities for the key. Quantities should be zero since we do not
1075 -- collect wip quantities.
1076 l_start_value := nvl(l_prev_end_bal,0);
1077 l_end_value := l_start_value + nvl(i_wip_amount,0);
1078 l_avg_value := (l_start_value + l_end_value) / 2;
1079
1080 /*
1081 DBMS_OUTPUT.PUT_LINE('daily update - insert key: ' || i_ids_key);
1082 DBMS_OUTPUT.PUT_LINE('start val: ' || to_char(l_start_value));
1083 DBMS_OUTPUT.PUT_LINE('end val: ' || to_char(l_end_value));
1084 DBMS_OUTPUT.PUT_LINE('avg_val: ' || to_char(l_avg_value));
1085 */
1086
1087 l_stmt_num := 20;
1088 OPIMPXIN.Insert_update_push_log(
1089 i_txn_date,
1090 i_org_id,
1091 i_item_id,
1092 null, -- cost group id
1093 i_revision,
1094 null, -- lot number
1095 null, -- subinventory code
1096 null, -- locator
1097 l_item_status,
1098 l_item_type,
1099 l_base_uom,
1100 'beg_wip_qty', -- p_col_nam1
1101 0, -- p_total1
1102 'beg_wip_val_b', -- p_col_nam2
1103 l_start_value, -- p_total2
1104 'end_wip_qty', -- p_col_nam3
1105 0, -- p_total3
1106 'end_wip_val_b', -- p_col_nam4
1107 l_end_value, -- p_total4
1108 'avg_wip_qty', -- p_col_nam5
1109 0, -- p_total5
1110 'avg_wip_val_b', -- p_col_nam6
1111 l_avg_value, -- p_total6
1112 2, -- selector
1113 l_err_num); -- l_status
1114 if l_err_num <> 0 then
1115 EDW_LOG.PUT_LINE('Error calling OPIMPXIN.Insert_update_push_log');
1116 raise process_error;
1117 end if;
1118 end if; -- end checking for existence of push log row
1119
1120 EXCEPTION
1121 when process_error then
1122 EDW_LOG.PUT_LINE('OPIMPXWP.update_daily_wip:');
1123 EDW_LOG.PUT_LINE('Error processing (OPIMPXWP.update_daily_wip)...');
1124 EDW_LOG.PUT_LINE('Error Num= ' || to_char(l_err_num));
1125 EDW_LOG.PUT_LINE('Statement Num= ' || to_char(l_stmt_num));
1126
1127 when others then
1128 o_err_num := SQLCODE;
1129 o_err_msg := 'OPIMPXWP.update_daily_wip ('
1130 || to_char(l_stmt_num)
1131 || '): '
1132 || substr(SQLERRM, 1,200);
1133 EDW_LOG.PUT_LINE('Error Code: ' || to_char(o_err_num));
1134 EDW_LOG.PUT_LINE(o_err_msg);
1135
1136 END update_daily_wip;
1137
1138 END OPIMPXWP;