1 PACKAGE BODY opi_edw_ids_calc as
2 /*$Header: OPIMPPBB.pls 120.1 2005/06/07 03:30:10 appldev $*/
3
4 TYPE bal_rec IS RECORD
5 ( beg_int_qty opi_ids_push_log.beg_int_qty%TYPE,
6 beg_int_val_b opi_ids_push_log.beg_int_val_b%TYPE,
7 beg_onh_qty opi_ids_push_log.beg_onh_qty%TYPE,
8 beg_onh_val_b opi_ids_push_log.beg_onh_val_b%TYPE,
9 beg_wip_qty opi_ids_push_log.beg_wip_qty%TYPE,
10 beg_wip_val_b opi_ids_push_log.beg_wip_val_b%TYPE,
11 end_int_qty opi_ids_push_log.end_int_qty%TYPE,
12 end_int_val_b opi_ids_push_log.end_int_val_b%TYPE,
13 end_onh_qty opi_ids_push_log.end_onh_qty%TYPE,
14 end_onh_val_b opi_ids_push_log.end_onh_val_b%TYPE,
15 end_wip_qty opi_ids_push_log.end_wip_qty%TYPE,
16 end_wip_val_b opi_ids_push_log.end_wip_val_b%TYPE,
17 trx_date opi_ids_push_log.trx_date%TYPE,
18 base_uom opi_ids_push_log.base_uom%TYPE,
19 item_status opi_ids_push_log.item_status%TYPE,
20 item_type opi_ids_push_log.item_type%TYPE,
21 nettable_flag opi_ids_push_log.nettable_flag%TYPE
22 );
23
24 TYPE key_rec IS RECORD
25 ( inventory_item_id opi_ids_push_log.inventory_item_id%TYPE,
26 organization_id opi_ids_push_log.organization_id%TYPE,
27 cost_group_id opi_ids_push_log.cost_group_id%TYPE,
28 revision opi_ids_push_log.revision%TYPE,
29 lot_number opi_ids_push_log.lot_number%TYPE,
30 subinventory_code opi_ids_push_log.subinventory_code%TYPE,
31 locator_id opi_ids_push_log.locator_id%TYPE,
32 project_locator_id opi_ids_push_log.project_locator_id%TYPE);
33
34 PROCEDURE update_ids_push_log (p_ids_key VARCHAR2,
35 p_bal_rec bal_rec ) IS
36 BEGIN
37 UPDATE opi_ids_push_log
38 SET
39 beg_int_qty = p_bal_rec.end_int_qty,
40 beg_int_val_b = p_bal_rec.end_int_val_b,
41 beg_onh_qty = p_bal_rec.end_onh_qty,
42 beg_onh_val_b = p_bal_rec.end_onh_val_b,
43 beg_wip_qty = p_bal_rec.end_wip_qty,
44 beg_wip_val_b = p_bal_rec.end_wip_val_b,
45 end_int_qty = p_bal_rec.end_int_qty,
46 end_int_val_b = p_bal_rec.end_int_val_b,
47 end_onh_qty = p_bal_rec.end_onh_qty,
48 end_onh_val_b = p_bal_rec.end_onh_val_b,
49 end_wip_qty = p_bal_rec.end_wip_qty,
50 end_wip_val_b = p_bal_rec.end_wip_val_b,
51 base_uom = p_bal_rec.base_uom,
52 item_status = p_bal_rec.item_status,
53 item_type = p_bal_rec.item_type,
54 nettable_flag = p_bal_rec.nettable_flag,
55 push_flag =1
56 WHERE ids_key = p_ids_key;
57
58 END update_ids_push_log;
59
60 PROCEDURE insert_ids_push_log ( p_ids_key VARCHAR2,
61 p_trx_date DATE,
62 p_period_flag NUMBER,
63 p_key key_rec,
64 p_bal_rec bal_rec) IS
65 BEGIN
66
67 --dbms_output.put_line('count = 1 ' || p_key.organization_id);
68
69 INSERT INTO opi_ids_push_log
70 (ids_key,
71 cost_group_id,
72 organization_id,
73 inventory_item_id,
74 revision,
75 subinventory_code,
76 locator_id,
77 project_locator_id,
78 lot_number,
79 trx_date,
80 period_flag,
81 push_flag,
82 beg_int_qty, beg_int_val_b,
83 beg_onh_qty, beg_onh_val_b,
84 beg_wip_qty, beg_wip_val_b,
85 end_int_qty, end_int_val_b,
86 end_onh_qty, end_onh_val_b,
87 end_wip_qty, end_wip_val_b,
88 base_uom,
89 item_status,
90 item_type,
91 nettable_flag)
92 VALUES
93 ( p_ids_key,
94 p_key.cost_group_id,
95 p_key.organization_id,
96 p_key.inventory_item_id,
97 p_key.revision,
98 p_key.subinventory_code,
99 p_key.locator_id,
100 p_key.project_locator_id,
101 p_key.lot_number,
102 p_trx_date,
103 p_period_flag,
104 1,
105 p_bal_rec.end_int_qty, p_bal_rec.end_int_val_b,
106 p_bal_rec.end_onh_qty, p_bal_rec.end_onh_val_b,
107 p_bal_rec.end_wip_qty, p_bal_rec.end_wip_val_b,
108 p_bal_rec.end_int_qty, p_bal_rec.end_int_val_b,
109 p_bal_rec.end_onh_qty, p_bal_rec.end_onh_val_b,
110 p_bal_rec.end_wip_qty, p_bal_rec.end_wip_val_b,
111 p_bal_rec.base_uom,
112 p_bal_rec.item_status,
113 p_bal_rec.item_type,
114 p_bal_rec.nettable_flag);
115 END insert_ids_push_log;
116
117
118 PROCEDURE calc_prd_start_end ( p_from_date DATE,
119 p_to_date DATE,
120 p_organization_id NUMBER,
121 x_status OUT NOCOPY NUMBER ) IS
122
123 cursor get_max_push_from_date_csr is
124 select max( last_push_inv_txn_date), max(last_push_wip_txn_date)
125 from opi_ids_push_date_log
126 where organization_id = p_organization_id;
127
128 l_inv_txn_date date;
129 l_wip_txn_date date;
130 l_from_date date;
131
132 CURSOR l_key_combs_csr (p_from_date DATE, p_to_date DATE) IS
133 SELECT DISTINCT inventory_item_id,
134 organization_id,
135 cost_group_id,
136 revision,
137 lot_number,
138 subinventory_code,
139 locator_id,
140 project_locator_id
141 FROM opi_ids_push_log
142 WHERE trx_date BETWEEN (p_from_date -1) AND p_to_date
143 AND organization_id = p_organization_id;
144
145 CURSOR l_extraction_periods_csr ( l_organization_id NUMBER) IS
146 SELECT Trunc(period_start_date) start_date,
147 Trunc(schedule_close_date) end_date
148 FROM org_acct_periods
149 WHERE organization_id = l_organization_id
150 AND (( period_start_date between p_from_date
151 and p_to_date )
152 OR( schedule_close_date between p_from_date
153 and p_to_date )
154 OR
155 ( (p_from_date between period_start_date and schedule_close_date)
156 AND (p_to_date between period_start_date and
157 schedule_close_date) )
158 )
159 ORDER BY start_date;
160
161 CURSOR l_period_end_entry_csr ( l_ids_key VARCHAR2) IS
162 SELECT beg_int_qty, beg_int_val_b,
163 beg_onh_qty, beg_onh_val_b,
164 beg_wip_qty, beg_wip_val_b,
165 end_int_qty, end_int_val_b,
166 end_onh_qty, end_onh_val_b,
167 end_wip_qty, end_wip_val_b,
168 trx_date, base_uom, item_status, item_type, nettable_flag
169 FROM opi_ids_push_log
170 WHERE ids_key = l_ids_key
171 AND period_flag = 1;
172
173 CURSOR l_period_start_entry_csr ( l_ids_key VARCHAR2,
174 l_period_flag NUMBER := 0 ) IS
175 SELECT beg_int_qty, beg_int_val_b,
176 beg_onh_qty, beg_onh_val_b,
177 beg_wip_qty, beg_wip_val_b,
178 end_int_qty, end_int_val_b,
179 end_onh_qty, end_onh_val_b,
180 end_wip_qty, end_wip_val_b,
181 trx_date, base_uom, item_status, item_type, nettable_flag
182 FROM opi_ids_push_log
183 WHERE ids_key = l_ids_key;
184
185 CURSOR l_latest_activity_csr (l_inventory_item_id NUMBER,
186 l_organization_id NUMBER,
187 l_cost_group_id NUMBER,
188 l_revision VARCHAR2,
189 l_lot_number VARCHAR2,
190 l_subinventory_code VARCHAR2,
191 l_locator_id NUMBER,
192 l_trx_start_date DATE,
193 l_trx_end_date DATE ) IS
194 SELECT beg_int_qty, beg_int_val_b,
195 beg_onh_qty, beg_onh_val_b,
196 beg_wip_qty, beg_wip_val_b,
197 end_int_qty, end_int_val_b,
198 end_onh_qty, end_onh_val_b,
199 end_wip_qty, end_wip_val_b,
200 trx_date, base_uom, item_status, item_type, nettable_flag
201 FROM opi_ids_push_log
202 WHERE inventory_item_id = l_inventory_item_id
203 AND organization_id = l_organization_id
204 AND Nvl(cost_group_id, -999) = Nvl(l_cost_group_id, -999)
205 AND Nvl(revision, '-999') = Nvl(l_revision, '-999')
206 AND Nvl(lot_number, '-999') = Nvl(l_lot_number, '-999')
207 AND Nvl(subinventory_code, '-999') = Nvl(l_subinventory_code, '-999')
208 AND Nvl(project_locator_id, -999) = Nvl(l_locator_id, -999)
209 AND trx_date IN ( SELECT MAX(trx_date)
210 FROM opi_ids_push_log
211 WHERE inventory_item_id = l_inventory_item_id
212 AND organization_id = l_organization_id
213 AND Nvl(cost_group_id, -999) = Nvl(l_cost_group_id, -999)
214 AND Nvl(revision, '-999') = Nvl(l_revision, '-999')
215 AND Nvl(lot_number, '-999') = Nvl(l_lot_number, '-999')
216 AND Nvl(subinventory_code, '-999') =
217 Nvl(l_subinventory_code, '-999')
218 AND Nvl(project_locator_id, -999) = Nvl(l_locator_id, -999)
219 AND trx_date BETWEEN l_trx_start_date AND l_trx_end_date
220 -- activity check
221 AND ( (Nvl(beg_int_qty,0) - Nvl(end_int_qty,0)) <> 0
222 OR ( Nvl(beg_int_val_b,0) - Nvl(end_int_val_b,0)) <> 0
223 OR ( Nvl(beg_onh_qty,0) - Nvl(end_onh_qty,0) ) <> 0
224 OR ( Nvl(beg_onh_val_b,0) - Nvl(end_onh_val_b,0)) <> 0
225 OR ( Nvl(beg_wip_qty,0) - Nvl(end_wip_qty,0) ) <> 0
226 OR ( Nvl(beg_wip_val_b,0) - Nvl(end_wip_val_b,0)) <> 0
227 OR nvl(total_rec_qty,0) <> 0
228 OR nvl(total_rec_val_b, 0) <> 0
229 OR nvl(tot_issues_qty,0) <> 0
230 OR nvl(tot_issues_val_b,0) <> 0
231 OR Nvl(from_org_qty,0) <> 0
232 OR Nvl(from_org_val_b,0) <> 0
233 OR Nvl(inv_adj_qty,0) <> 0
234 OR Nvl(inv_adj_val_b,0) <> 0
235 OR Nvl(po_del_qty, 0) <> 0
236 OR Nvl(po_del_val_b, 0) <> 0
237 OR Nvl(to_org_qty,0) <> 0
238 OR Nvl(to_org_val_b,0) <> 0
239 OR Nvl(tot_cust_ship_qty,0) <> 0
240 OR Nvl(tot_cust_ship_val_b, 0) <> 0
241 OR Nvl(wip_assy_qty, 0) <> 0
242 OR Nvl(wip_assy_val_b,0) <> 0
243 OR Nvl(wip_comp_qty,0) <> 0
244 OR Nvl(wip_comp_val_b,0) <> 0
245 OR Nvl(wip_issue_qty,0) <> 0
246 OR Nvl(wip_issue_val_b,0) <> 0
247 )
248 );
249
250 CURSOR l_initial_period_prev_csr (l_start_date DATE,
251 l_end_date DATE,
252 l_organization_id NUMBER ) IS
253 SELECT Trunc(MAX(period_start_date)) start_date,
254 Trunc(MAX(schedule_close_date)) end_date
255 FROM org_acct_periods
256 WHERE organization_id = l_organization_id
257 AND schedule_close_date <l_start_date
258 GROUP BY organization_id;
259
260 CURSOR l_period_flag_activity_csr (l_ids_key VARCHAR2) IS
261 SELECT Decode( nvl(period_flag, 999), 999,999,
262 -- no period start/end entry
263 1) period_start_flag,
264 Decode( (Nvl(beg_int_qty,0) - Nvl(end_int_qty,0)), 0,
265 Decode( ( Nvl(beg_int_val_b,0) - Nvl(end_int_val_b,0)), 0,
266 Decode( ( Nvl(beg_onh_qty,0) - Nvl(end_onh_qty,0) ), 0,
267 Decode( ( Nvl(beg_onh_val_b,0) - Nvl(end_onh_val_b,0)), 0,
268 Decode( ( Nvl(beg_wip_qty,0) - Nvl(end_wip_qty,0) ), 0,
269 Decode( ( Nvl(beg_wip_val_b,0) - Nvl(end_wip_val_b,0)), 0,
270 decode( nvl(total_rec_qty,0), 0,
271 decode( nvl(total_rec_val_b, 0), 0,
272 decode( nvl(tot_issues_qty,0), 0,
273 decode( nvl(tot_issues_val_b,0), 0,
274 Decode(Nvl(from_org_qty,0), 0,
275 Decode(Nvl(from_org_val_b,0),0,
276 Decode(Nvl(inv_adj_qty,0),0,
277 Decode(Nvl(inv_adj_val_b,0),0,
278 Decode(Nvl(po_del_qty, 0),0,
279 Decode(Nvl(po_del_val_b, 0),0,
280 Decode( Nvl(to_org_qty,0),0,
281 Decode( Nvl(to_org_val_b,0),0,
282 Decode( Nvl(tot_cust_ship_qty,0),0,
283 Decode( Nvl(tot_cust_ship_val_b, 0),0,
284 Decode( Nvl(wip_assy_qty, 0),0,
285 Decode( Nvl(wip_assy_val_b,0),0,
286 Decode( Nvl(wip_comp_qty,0),0,
287 Decode( Nvl(wip_comp_val_b,0),0,
288 Decode( Nvl(wip_issue_qty,0),0,
289 Decode( Nvl(wip_issue_val_b,0),0,
290 0, -- no activity at all
291 1), 1), 1), 1), 1), 1), 1), 1), 1), 1), 1), 1), 1), 1),1), 1),
292 1), 1), 1), 1), 1), 1), 1), 1), 1), 1) activity_flag
293 FROM opi_ids_push_log
294 WHERE ids_key = l_ids_key;
295 /*
296 Decode(Nvl(beg_int_qty,0), 0,
297 Decode(Nvl(beg_int_val_b,0), 0,
298 Decode(Nvl(beg_onh_qty,0),0,
299 Decode(Nvl(beg_onh_val_b, 0),0,
300 Decode(Nvl(beg_wip_qty, 0),0,
301 Decode(Nvl(beg_wip_val_b,0),0,
302 Decode(Nvl(end_int_qty,0),0,
303 Decode(Nvl(end_int_val_b,0),0,
304 Decode(Nvl(end_onh_qty,0),0,
305 Decode(Nvl(end_onh_val_b, 0),0,
306 Decode(Nvl(end_wip_qty, 0),0,
307 Decode(Nvl(end_wip_val_b,0),0,0, --no balance at all
308 1 ), 1), 1), 1), 1), 1), 1), 1), 1), 1), 1), 1) bal_flag
309 */
310
311 l_last_period l_extraction_periods_csr%ROWTYPE;
312
313 l_latest_activity_entry bal_rec; -- l_latest_activity_csr%ROWTYPE;
314 l_activity_flag BOOLEAN := FALSE;
315
316 l_last_period_end_entry bal_rec; --l_period_end_entry_csr%ROWTYPE;
317 l_period__entry bal_rec; --l_period_end_entry_csr%ROWTYPE;
318 l_last_period_end_flag NUMBER := 999;
319
320 l_beg_inv_bal_prd_start_entry bal_rec; --l_period_end_entry_csr%ROWTYPE;
321
322
323 l_period_end_flag NUMBER := 999;
324 l_end_activity_flag NUMBER := 999;
325 --l_period_end_bal_flag NUMBER := 999;
326
327 l_period_start_flag number := 999;
328 l_start_activity_flag number := 999;
329 --l_period_start_bal_flag NUMBER := 999;
330
331 l_beg_inv_bal_flag NUMBER := 999;
332
333 l_prd_start_ids_key VARCHAR2(240);
334 l_prd_end_ids_key VARCHAR2(240);
335
336 l_ids_key VARCHAR2(240);
337
338 l_first_push_date DATE;
339
340 l_combs_start_date DATE := NULL;
341 BEGIN
342 x_status := 0;
343
344 -- get the very first period start date for which the beg_inv_bal is created
345 select Trunc( min(trx_date) )
346 into l_first_push_date
347 from opi_ids_push_log
348 where organization_id = p_organization_id;
349
350 open get_max_push_from_date_csr;
351 fetch get_max_push_from_date_csr into l_inv_txn_date, l_wip_txn_date;
352 close get_max_push_from_date_csr;
353
354 select least(p_from_date,
355 nvl(l_inv_txn_date, to_date('01-12-3000','DD-MM-YYYY') ),
356 nvl(l_wip_txn_date, to_date('01-12-3000','DD-MM-YYYY') ) )
357 into l_from_date
358 from dual;
359
360 edw_log.put_line('now in calc_prd_start_end for org ' || p_organization_id
361 || ' start date ' || To_char(p_from_date,'DD-MON-YYYY hh24:mi:ss')
362 || ' to end date ' ||To_char(p_to_date ,'DD-MON-YYYY hh24:mi:ss')
363 || ' l_from_date ' ||To_char(l_from_date ,
364 'DD-MON-YYYY hh24:mi:ss'));
365
366 -- get the date to start collecting combinations from.
370 -- records. We might lose all combinations that have a prior balance
367 -- Since we delete all lines past the from date, we need to know
368 -- all the collections as the beginning of the period. However, if we
369 -- are collecting from the start date, we have deleted all start date
371 -- but no activity in this period. So start at the period end of the
372 -- latest period before the collection start date.
373 -- The only exception is if this is the very first defined period.
374 -- In that case, just pick the start of this period.
375 BEGIN
376 SELECT max (trunc (schedule_close_date))
377 INTO l_combs_start_date
378 FROM org_acct_periods
379 WHERE organization_id = p_organization_id
380 AND schedule_close_date < trunc (l_from_date);
381 -- use < instead of <= since if the from date is a period end date,
382 -- then everything has been truncated for this period.
383
384 IF (l_combs_start_date IS NULL) THEN
385
386 SELECT max (trunc (period_start_date))
387 INTO l_combs_start_date
388 FROM org_acct_periods
389 WHERE organization_id = p_organization_id
390 AND period_start_date <= trunc (l_from_date);
391 -- only happens if there is no prior period defined. Just take
392 -- the first period start date then.
393
394 END IF;
395
396 END;
397
398 -- debug
399 -- DBMS_OUTPUT.ENABLE (1000000);
400
401 -- DBMS_OUTPUT.PUT_LINE ('Start: ' || p_from_date || '---'
402 -- || 'End: ' || p_to_date);
403
404
405 FOR l_key IN l_key_combs_csr (l_combs_start_date, p_to_date) LOOP
406
407 l_last_period_end_flag := 0;
408 l_last_period_end_entry := NULL;
409 FOR l_period IN l_extraction_periods_csr(l_key.organization_id) LOOP
410
411 -- for period_start entry ids_key
412 l_prd_start_ids_key := l_period.start_date || '-'
413 || l_key.inventory_item_id
414 || '-' || l_key.organization_id || '-' || l_key.cost_group_id
415 || '-' || l_key.revision || '-' || l_key.lot_number
416 || '-' || l_key.subinventory_code ||'-'||l_key.project_locator_id ;
417
418 -- for period_end entry ids_key
419 l_prd_end_ids_key := l_period.end_date || '-'
420 || l_key.inventory_item_id
421 || '-' || l_key.organization_id || '-' || l_key.cost_group_id
422 || '-' || l_key.revision || '-' || l_key.lot_number
423 || '-' || l_key.subinventory_code ||'-'
424 || l_key.project_locator_id ;
425
426 /*
427 edw_log.put_line(' l_prd_start_ids_key is '
428 || l_prd_start_ids_key );
429 edw_log.put_line(' l_prd_end_ids_key is ' || l_prd_end_ids_key );
430
431 edw_log.put_line('---period ' || l_extraction_periods_csr%rowcount
432 || ' start date is '|| l_period.start_date || ' '
433 || l_period.end_date || 'l_last_period_end_flag is'
434 || l_last_period_end_flag || ' --- '
435 || l_last_period_end_entry.end_int_val_b|| ' --- '
436 || l_last_period_end_entry.end_onh_qty|| ' --- '
437 || l_last_period_end_entry.end_onh_val_b|| ' --- '
438 || l_last_period_end_entry.end_wip_qty|| ' --- '
439 || l_last_period_end_entry.end_wip_val_b|| ' --- '
440 || l_last_period_end_entry.end_int_qty|| ' --- '
441 || l_last_period_end_entry.end_int_val_b|| ' --- '
442 || l_last_period_end_entry.end_onh_qty|| ' --- '
443 || l_last_period_end_entry.end_onh_val_b|| ' --- '
444 || l_last_period_end_entry.end_wip_qty|| ' --- '
445 || l_last_period_end_entry.end_wip_val_b );
446 */
447
448 -- 1). get the one period before the initial_period
449 IF l_extraction_periods_csr%rowcount = 1 THEN
450 -- 1a). get last period
451 OPEN l_initial_period_prev_csr(l_period.start_date,
452 l_period.end_date,
453 l_key.organization_id);
454 FETCH l_initial_period_prev_csr INTO l_last_period;
455 CLOSE l_initial_period_prev_csr;
456
457 -- 1b). check existence of last period_end entry
458 l_ids_key := l_last_period.end_date || '-'
459 || l_key.inventory_item_id
460 || '-' || l_key.organization_id || '-'
461 || l_key.cost_group_id
462 || '-' || l_key.revision || '-'
463 || l_key.lot_number
464 || '-' || l_key.subinventory_code ||'-'
465 || l_key.project_locator_id ;
466
467 OPEN l_period_end_entry_csr (l_ids_key);
468 FETCH l_period_end_entry_csr INTO l_last_period_end_entry;
469
470 IF l_period_end_entry_csr%notfound THEN
471 l_last_period_end_flag := 0;
472 ELSE
473 l_last_period_end_flag := 1;
474 END IF;
475 CLOSE l_period_end_entry_csr;
476
480
477 --edw_log.put_line(' l_Ids_key ' || l_ids_key );
478 --edw_log.put_line(' l_last_period_end_flag is ' || l_last_period_end_flag );
479 END IF;
481 -- check existing of period_start entry
482 OPEN l_period_flag_activity_csr (l_prd_start_ids_key );
483 FETCH l_period_flag_activity_csr
484 INTO l_period_start_flag,
485 l_start_activity_flag; --, l_period_start_bal_flag;
486
487 IF l_period_flag_activity_csr%notfound THEN
488 l_period_start_flag := 0;
489 l_start_activity_flag := 0;
490 --l_period_start_bal_flag := 0;
491 END IF;
492 CLOSE l_period_flag_activity_csr;
493
494 -- check existing of period_end entry
495 OPEN l_period_flag_activity_csr (l_prd_end_ids_key );
496 FETCH l_period_flag_activity_csr
497 INTO l_period_end_flag,
498 l_end_activity_flag ; --, l_period_end_bal_flag;
499
500 IF l_period_flag_activity_csr%notfound THEN
501 l_period_end_flag := 0;
502 l_end_activity_flag := 0;
503 -- l_period_end_bal_flag := 0;
504 END IF;
505 CLOSE l_period_flag_activity_csr;
506
507 --edw_log.put_line(' l_period_start_flag is '
508 -- || l_period_start_flag || ' activity is '
509 -- || l_start_activity_flag );
510 --edw_log.put_line(' l_period_end_flag is '
511 -- || l_period_end_flag || 'activity is '
512 -- || l_end_activity_flag);
513
514 -- 2). check if there is activity within the period
515 OPEN l_latest_activity_csr(l_key.inventory_item_id,
516 l_key.organization_id,
517 l_key.cost_group_id,
518 l_key.revision,
519 l_key.lot_number,
520 l_key.subinventory_code,
521 l_key.project_locator_id,
522 l_period.start_date,
523 l_period.end_date);
524 FETCH l_latest_activity_csr INTO l_latest_activity_entry;
525 IF l_latest_activity_csr%notfound THEN
526 l_activity_flag := FALSE;
527 --edw_log.put_line('l_activity_flag is false ' );
528
529 ELSE
530 l_activity_flag := TRUE;
531 --edw_log.put_line(' l_activity_flag is true '
532 -- || 'trx_date is '
533 -- || l_latest_activity_entry.trx_date );
534
535 END IF;
536 CLOSE l_latest_activity_csr;
537
538
539 -- There is activity within the period
540 IF l_activity_flag THEN
541 IF l_last_period_end_flag <> 1 THEN
542 IF l_period_start_flag <> 1 THEN
543 IF l_start_activity_flag <>1
544 AND l_period_start_flag <> 999 THEN
545
546 INSERT INTO opi_ids_push_log
547 (ids_key, cost_group_id,
548 organization_id,inventory_item_id,
549 revision, subinventory_code,
550 locator_id, project_locator_id,
551 lot_number, trx_date,
552 period_flag, push_flag,
553 beg_int_qty, beg_int_val_b,
554 beg_onh_qty, beg_onh_val_b,
555 beg_wip_qty, beg_wip_val_b,
556 end_int_qty, end_int_val_b,
557 end_onh_qty, end_onh_val_b,
558 end_wip_qty, end_wip_val_b,
559 base_uom, item_status,
560 item_type,nettable_flag)
561 VALUES
562 ( l_prd_start_ids_key, l_key.cost_group_id,
563 l_key.organization_id,
564 l_key.inventory_item_id,
565 l_key.revision, l_key.subinventory_code,
566 l_key.locator_id, l_key.project_locator_id,
567 l_key.lot_number, l_period.start_date,
568 0, 1, 0,0,0,0,0,0, 0,0,0,0,0,0,
569 l_latest_activity_entry.base_uom,
570 l_latest_activity_entry.item_status,
571 l_latest_activity_entry.item_type,
572 l_latest_activity_entry.nettable_flag);
573
574 --edw_log.put_line('1 start no/no insert');
575
576 ELSE
577 -- if l_start_activity_flag = 1,
578 -- we have activity on start date-> update
579 -- if l_period_start_flag = 999,
580 -- we have beg_inv_bal entry on start_date --> update
581 UPDATE opi_ids_push_log
582 SET period_flag = 0,
586 --edw_log.put_line('1 start update');
583 push_flag =1
584 WHERE ids_key = l_prd_start_ids_key;
585
587 END IF;
588
589 l_period_start_flag := 1;
590 ELSE -- l_period_start_flag = 1
591 IF l_start_activity_flag <>1 THEN
592 -- update existing period_start_entry with 0s
593 -- if the entry is not for the beg_inv_val
594 -- entry
595 IF l_period.start_date <> l_first_push_date
596 THEN
597 UPDATE opi_ids_push_log
598 SET
599 beg_int_qty = 0,
600 beg_int_val_b = 0,
601 beg_onh_qty = 0,
602 beg_onh_val_b = 0,
603 beg_wip_qty = 0,
604 beg_wip_val_b = 0,
605 end_int_qty = 0,
606 end_int_val_b = 0,
607 end_onh_qty = 0,
608 end_onh_val_b = 0,
609 end_wip_qty = 0,
610 end_wip_val_b = 0,
611 base_uom =
612 l_latest_activity_entry.base_uom,
613 item_status =
614 l_latest_activity_entry.item_status,
615 item_type =
616 l_latest_activity_entry.item_type,
617 nettable_flag =
618 l_latest_activity_entry.nettable_flag,
619 push_flag = 1
620 WHERE ids_key = l_prd_start_ids_key;
621 END IF;
622 --edw_log.put_line('1 start update 2');
623 -- ELSE do nothing;
624 END IF;
625 END IF;
626 ELSE -- the key combo does exist in previous period
627 IF l_period_start_flag <> 1 THEN
628 IF l_start_activity_flag <> 1 THEN
629 -- no activity on period_start
630 -- create one by copying the one last
631 -- period_end entry
632 IF l_period_start_flag = 0 THEN
633 insert_ids_push_log(l_prd_start_ids_key,
634 l_period.start_date,
635 0, -- p_period_flag
636 l_key,
637 l_last_period_end_entry );
638
639 --edw_log.put_line('1 start insert 2');
640
641 ELSIF l_period_start_flag = 999 THEN
642 UPDATE opi_ids_push_log
643 SET period_flag = 0,
644 push_flag =1
645 WHERE ids_key = l_prd_start_ids_key;
646 END IF;
647 ELSE
648 -- activity on period_start, but
649 -- no period_start_entry
650 -- turn on the flag
651 UPDATE opi_ids_push_log
652 SET period_flag = 0,
653 push_flag =1
654 WHERE ids_key = l_prd_start_ids_key;
655
656 --edw_log.put_line('1 start update 3');
657 END IF;
658 l_period_start_flag := 1;
659 ELSE -- period_start entry already existed
660 IF l_start_activity_flag <> 1 THEN
661 -- no activity on period_start
662 -- update existing one with numbers
663 -- from last period_end entry
664 update_ids_push_log(l_prd_start_ids_key,
665 l_last_period_end_entry);
666
667 --edw_log.put_line('1 start update 4');
668 --ELSE do nothing
669 END IF;
670 END IF;
674 -- always calculate the period end entry
671 END IF; /* end of period_start entry */
672
673
675 IF l_period_end_flag = 1 THEN
676 -- period end entry exists
677 IF l_end_activity_flag <> 1 THEN
678 -- no activity on the period end date
679 -- update the existing period_end entry with
680 -- numbers from lastest activity entry
681
682 update_ids_push_log(l_prd_end_ids_key,
683 l_latest_activity_entry);
684
685 --edw_log.put_line('1 end update');
686
687 --ELSE -- do nothing
688 END IF;
689 ELSE -- period_entry doesn't exist yet
690 IF l_end_activity_flag <> 1 AND
691 l_period_end_flag = 0 THEN
692 -- create a period_end_entry with numbers
693 -- from latest activity entry
694 insert_ids_push_log(l_prd_end_ids_key,
695 l_period.end_date,
696 1, -- p_period_flag
697 l_key,
698 l_latest_activity_entry);
699 --edw_log.put_line('1 end insert');
700 ELSE
701 -- there is acitivity on period_end date
702 -- turn on the flag
703 -- l_end_activity_flag = 0 or 1,
704 -- l_period_end_flag = 999
705 UPDATE opi_ids_push_log
706 SET period_flag = 1, push_flag = 1
707 WHERE ids_key = l_prd_end_ids_key;
708
709 --edw_log.put_line('1 end update 2');
710 END IF;
711
712 -- now we have a period_end entry for this period
713 l_period_end_flag := 1;
714 END IF;
715
716 l_last_period_end_entry := l_latest_activity_entry;
717 l_last_period_end_flag := l_period_end_flag;
718 /* end of period_end entry */
719 END IF; /* end of there is acitivity within the period */
720
721 IF l_activity_flag = FALSE THEN
722 -- no activity within the period
723 -- either cleanup or carry over the balance
724 -- for period_start/ period_end entries
725
726 IF l_last_period_end_flag <> 1 THEN
727 -- delete the period_start entry if it exists
728 IF l_period_start_flag = 1 THEN
729 IF l_period.start_date <> l_first_push_date THEN
730 DELETE opi_ids_push_log
731 WHERE ids_key = l_prd_start_ids_key;
732
733 --edw_log.put_line('2 start del 0');
734
735 l_period_start_flag := 0;
736 END IF;-- l_period.start_date <> l_first_push_date
737 ELSIF l_period_start_flag = 999 THEN
738 -- the beg_inv_bal entry exists
739 -- a). if with bal, we need to carry over
740 -- b). if no bal, we need to delete it
741
742 OPEN l_period_start_entry_csr(l_prd_start_ids_key);
743 FETCH l_period_start_entry_csr
744 INTO l_beg_inv_bal_prd_start_entry;
745 CLOSE l_period_start_entry_csr;
746
747 -- b).
748 IF Nvl(l_beg_inv_bal_prd_start_entry.beg_int_qty,0)= 0
749 AND Nvl(l_beg_inv_bal_prd_start_entry.beg_int_val_b,0) = 0
750 AND Nvl(l_beg_inv_bal_prd_start_entry.beg_onh_qty,0) = 0
751 AND Nvl(l_beg_inv_bal_prd_start_entry.beg_onh_val_b, 0) = 0
752 AND Nvl(l_beg_inv_bal_prd_start_entry.beg_wip_qty, 0) = 0
753 AND Nvl(l_beg_inv_bal_prd_start_entry.beg_wip_val_b,0) = 0
754 AND Nvl(l_beg_inv_bal_prd_start_entry.end_int_qty,0) = 0
755 AND Nvl(l_beg_inv_bal_prd_start_entry.end_int_val_b,0) = 0
756 AND Nvl(l_beg_inv_bal_prd_start_entry.end_onh_qty,0) = 0
757 AND Nvl(l_beg_inv_bal_prd_start_entry.end_onh_val_b, 0) = 0
758 AND Nvl(l_beg_inv_bal_prd_start_entry.end_wip_qty, 0) = 0
759 AND Nvl(l_beg_inv_bal_prd_start_entry.end_wip_val_b,0) = 0
760 THEN
761 -- delete the period_start entry if it exists
762 DELETE opi_ids_push_log
763 WHERE ids_key = l_prd_start_ids_key;
764
765 l_period_start_flag := 0;
766
767 --edw_log.put_line('2 start del 1');
768 ELSE -- a).
769
770 UPDATE opi_ids_push_log
771 SET period_flag = 0, push_flag = 1
772 WHERE ids_key = l_prd_start_ids_key;
773
774 l_beg_inv_bal_flag := 1;
775 l_period_start_flag := 1;
779 ELSE -- last _period_end_entry exists
776 --edw_log.put_line('2 start update');
777 END IF;
778 END IF;
780 -- check begin/end on last_period_end_entry
781 IF Nvl(l_last_period_end_entry.beg_int_qty,0) = 0
782 AND Nvl(l_last_period_end_entry.beg_int_val_b,0)=0
783 AND Nvl(l_last_period_end_entry.beg_onh_qty,0) = 0
784 AND Nvl(l_last_period_end_entry.beg_onh_val_b, 0)=0
785 AND Nvl(l_last_period_end_entry.beg_wip_qty, 0) = 0
786 AND Nvl(l_last_period_end_entry.beg_wip_val_b,0)= 0
787 AND Nvl(l_last_period_end_entry.end_int_qty,0) = 0
788 AND Nvl(l_last_period_end_entry.end_int_val_b,0)= 0
789 AND Nvl(l_last_period_end_entry.end_onh_qty,0) = 0
790 AND Nvl(l_last_period_end_entry.end_onh_val_b, 0)=0
791 AND Nvl(l_last_period_end_entry.end_wip_qty, 0) = 0
792 AND Nvl(l_last_period_end_entry.end_wip_val_b,0)= 0
793 THEN
794 -- delete the period_start entry if it exists
795 IF l_period_start_flag = 1 THEN
796 DELETE opi_ids_push_log
797 WHERE ids_key = l_prd_start_ids_key;
798 l_period_start_flag := 0;
799
800 --edw_log.put_line('2 start del 2');
801 END IF;
802 ELSE
803 IF l_period_start_flag = 1 THEN
804 -- update the existing period_start entry with the
805 -- numbers from last_period_end_entry.end****
806
807 update_ids_push_log(l_prd_start_ids_key,
808 l_last_period_end_entry);
809
810 --edw_log.put_line('2 start update 2');
811 ELSIF l_period_start_flag = 0 THEN
812 insert_ids_push_log(l_prd_start_ids_key,
813 l_period.start_date,
814 0,
815 l_key,
816 l_last_period_end_entry);
817
818 l_period_start_flag := 1;
819
820 --edw_log.put_line('2 start insert 2');
821 ELSIF l_period_start_flag = 999 THEN
822 UPDATE opi_ids_push_log
823 SET period_flag = 0, push_flag = 1
824 WHERE ids_key = l_prd_start_ids_key;
825
826 --edw_log.put_line('2 start update 2.5');
827 l_period_start_flag := 1;
828 END IF;
829 END IF;
830 END IF; /* end of period_start entry */
831
832 -- always calculate period end entry
833 IF l_period_start_flag = 1 THEN
834 IF l_period_end_flag = 1 THEN
835 IF l_beg_inv_bal_flag <> 1 THEN
836 -- should update the existing period_end entry with the
837 -- period_start entry
838 -- BUT here period_start entry is the same as
839 -- last_period_end_entry.end****
840
841 OPEN l_period_start_entry_csr(l_prd_start_ids_key);
842 FETCH l_period_start_entry_csr
843 INTO l_beg_inv_bal_prd_start_entry;
844 CLOSE l_period_start_entry_csr;
845
846 update_ids_push_log(l_prd_end_ids_key,
847 l_beg_inv_bal_prd_start_entry);
848
849 -- Dinkar 11/20/02 -- added this line
850 l_last_period_end_entry :=
851 l_beg_inv_bal_prd_start_entry;
852 --edw_log.put_line('2 end update ');
853
854 ELSIF l_beg_inv_bal_flag = 1 THEN
855 update_ids_push_log(l_prd_end_ids_key,
856 l_beg_inv_bal_prd_start_entry);
857 l_beg_inv_bal_flag := 999;
858
859 l_period_end_flag := 1;
860 l_last_period_end_entry :=
861 l_beg_inv_bal_prd_start_entry;
862
863 --edw_log.put_line('2 end update 2 ');
864 END IF;
865
866 ELSE
867 IF l_period_end_flag = 0 THEN
868 IF l_beg_inv_bal_flag <> 1 THEN
869
870 OPEN l_period_start_entry_csr
871 (l_prd_start_ids_key);
872
873 FETCH l_period_start_entry_csr
874 INTO l_beg_inv_bal_prd_start_entry;
875
876 CLOSE l_period_start_entry_csr;
877
878 insert_ids_push_log(l_prd_end_ids_key,
882 l_beg_inv_bal_prd_start_entry);
879 l_period.end_date,
880 1,
881 l_key,
883
884 -- Dinkar 11/20/02 -- added this line
885 l_last_period_end_entry :=
886 l_beg_inv_bal_prd_start_entry;
887
888 --edw_log.put_line('2 end insert ');
889 ELSIF l_beg_inv_bal_flag = 1 THEN
890 insert_ids_push_log(l_prd_end_ids_key,
891 l_period.end_date,
892 1,
893 l_key,
894 l_beg_inv_bal_prd_start_entry);
895 l_beg_inv_bal_flag := 999;
896
897 l_period_end_flag := 1;
898 l_last_period_end_entry :=
899 l_beg_inv_bal_prd_start_entry;
900
901 --edw_log.put_line('2 end insert 2');
902 END IF;
903 ELSIF l_period_end_flag = 999 THEN
904 UPDATE opi_ids_push_log
905 SET period_flag = 1, push_flag = 1
906 WHERE ids_key = l_prd_end_ids_key;
907 --edw_log.put_line('2 end update 2.5');
908 END IF;
909
910 l_period_end_flag := 1;
911 END IF;
912 ELSE
913 DELETE opi_ids_push_log
914 WHERE ids_key = l_prd_end_ids_key;
915
916 l_period_end_flag := 0;
917
918 --edw_log.put_line('2 end del 2 ');
919 END IF;
920 /* end of period_end_entry */
921
922 l_last_period_end_flag := l_period_end_flag;
923 END IF; /* end of no activity within the period */
924
925 END LOOP; /* loop for periods */
926 END LOOP; /* loop for key combs */
927
928 -- Fix for bug . Added procedures to fix the inv value for period start/end rows after standard cost update
929
930 cost_update_inventory (p_from_date, p_to_date, p_organization_id,x_status);
931
932
933 EXCEPTION
934 WHEN OTHERS THEN
935 --dbms_output.put_line('sqlerrm ' ||Sqlerrm);
936 edw_log.put_line('Error Code: ' || sqlcode);
937 edw_log.put_line('Error Message: ' || sqlerrm);
938 x_status := 1;
939 --commit;
940 END calc_prd_start_end;
941
942
943
944 -- cost_on_date function
945 -- returns that cost of an item-org on a specified date.
946 -- Cost is of type NUMBER
947 FUNCTION cost_on_date (org_id IN NUMBER, item_id IN NUMBER,
948 cost_date IN DATE)
949 RETURN NUMBER
950 IS
951 -- Cursor for cost query. This returns the newest cost from
952 -- the CST_STANDARD_COSTS table. This stores the latest
953 -- cost, except for new items.
954 CURSOR cost_on_date_csr (org_id NUMBER, item_id NUMBER,
955 cost_date DATE)
956 IS
957 SELECT csc.standard_cost unit_cost -- Standard cost method logic.
958 FROM CST_STANDARD_COSTS csc
959 WHERE csc.ORGANIZATION_ID = org_id
960 AND csc.INVENTORY_ITEM_ID = item_id
961 AND csc.STANDARD_COST_REVISION_DATE =
962 (SELECT max(csc2.STANDARD_COST_REVISION_DATE)
963 FROM CST_STANDARD_COSTS csc2
964 WHERE csc2.ORGANIZATION_ID = org_id
965 AND csc2.INVENTORY_ITEM_ID = item_id
966 AND csc2.STANDARD_COST_REVISION_DATE <
967 trunc(cost_date) + 1 );
968
969 -- cursor for getting the cost of a new item, when there is
970 -- no cost in the CST_STANDARD_COST table.
971 CURSOR new_item_cost_csr (org_id NUMBER, item_id NUMBER,p_cost_date DATE)
972 IS
973 select actual_cost
974 from mtl_material_transactions
975 where transaction_id = (
976 select max(transaction_id)
977 from mtl_material_transactions
978 where inventory_item_id=item_id
979 and organization_id=org_id
980 and actual_cost is not null
981 and transaction_date =
982 (select max(transaction_date)
983 from mtl_material_transactions
984 where inventory_item_id=item_id
985 and organization_id=org_id
986 and trunc(transaction_date) <= p_cost_date
987 and actual_cost is not null));
988
989 -- cost to return -- default is 0
990 on_date_cost NUMBER := 0;
991
992 BEGIN
993
994 --get the latest cost
995 OPEN cost_on_date_csr (org_id, item_id, cost_date);
996
997 FETCH cost_on_date_csr INTO on_date_cost;
998 -- if there is no cost, then the item is probably new, so get cost from the
999 -- new cost table
1000
1001 IF cost_on_date_csr%NOTFOUND
1005 CLOSE new_item_cost_csr;
1002 THEN
1003 OPEN new_item_cost_csr (org_id, item_id,cost_date);
1004 FETCH new_item_cost_csr INTO on_date_cost;
1006 END IF;
1007
1008 CLOSE cost_on_date_csr;
1009
1010 RETURN on_date_cost; -- cost on the given date
1011
1012 END cost_on_date;
1013
1014
1015 -- activity_on_day function.
1016 -- Returns true if there is activity on a given day (date is argument)
1017 -- and false if there is no activity on the day for a given item
1018 -- and org in the opi_ids_push_log.
1019 FUNCTION activity_on_day (day_to_check IN DATE, p_organization_id IN NUMBER,
1020 inv_item_id IN NUMBER)
1021 RETURN BOOLEAN
1022
1023 IS
1024
1025 -- Cursor to see if there is any acitvity on the given day.
1026 -- If so, this cursor will return some data in it,
1027 -- else, it will not for the specified date, item and org in
1028 -- the opi_ids_push_log.
1029 CURSOR activity_log_csr (day_to_check DATE, p_organization_id NUMBER,
1030 inv_item_id NUMBER)
1031 IS
1032 SELECT ids_key
1033 FROM opi_ids_push_log
1034 WHERE organization_id = p_organization_id
1035 AND inventory_item_id = inv_item_id
1036 AND trx_date = day_to_check
1037 AND ( NVL(beg_int_val_b,0) - NVL(end_int_val_b,0) <> 0
1038 OR NVL(beg_onh_val_b,0) - NVL(end_onh_val_b,0) <> 0
1039 OR NVL(beg_wip_val_b,0) - NVL(end_wip_val_b,0) <> 0);
1040
1041 activity_instance activity_log_csr%ROWTYPE;
1042
1043 activity_found BOOLEAN;
1044
1045 BEGIN
1046
1047 -- we only need to fetch once from the cursor to see if
1048 -- any activity was found
1049 OPEN activity_log_csr (day_to_check, p_organization_id,
1050 inv_item_id);
1051 FETCH activity_log_csr INTO activity_instance;
1052 activity_found := activity_log_csr%FOUND;
1053 CLOSE activity_log_csr;
1054 RETURN activity_found;
1055 END activity_on_day;
1056
1057
1058 -- function to return the next period start date, given any date.
1059 -- Returns NULL if no such date is found. Note that we do not
1060 -- care about the push flag anymore, nor the organization or
1061 -- item ids.
1062 -- Argument:
1063 -- curr_date - date in this period
1064
1065 FUNCTION get_next_period_start (curr_date IN DATE,
1066 p_organization_id IN NUMBER,
1067 p_inventory_item_id IN NUMBER)
1068 RETURN DATE
1069 IS
1070
1071 -- cursor to select the next period start date.
1072 CURSOR next_period_start_csr (v_curr_date DATE,
1073 p_organization_id NUMBER,
1074 p_inventory_item_id NUMBER)
1075 IS
1076 SELECT min(push_log.trx_date)
1077 FROM opi_ids_push_log push_log
1078 WHERE push_log.period_flag = 0
1079 AND push_log.organization_id = p_organization_id
1080 AND push_log.inventory_item_id = p_inventory_item_id
1081 AND push_log.trx_date > v_curr_date;
1082
1083 -- variable to get data out of the date cursor
1084 next_period_start DATE; --next_period_start_csr%ROWTYPE;
1085
1086 BEGIN
1087 OPEN next_period_start_csr(curr_date, p_organization_id,
1088 p_inventory_item_id);
1089 FETCH next_period_start_csr INTO next_period_start;
1090 CLOSE next_period_start_csr;
1091 RETURN next_period_start;
1092 END get_next_period_start;
1093
1094
1095 -- cost_update_inventory procedure
1096 -- Description:
1097 -- Finds all the items for a given org that have a cost update transaction
1098 -- registered for them in the given from - to period. If there are items
1099 -- cost updates, then it updates the end of periods (there might be many
1100 -- period ends in the specified from-to date) intransit, on hand and WIP
1101 -- inventory balances with the appropriate costs at the period_end_entries.
1102 --
1103 -- Arguments:
1104 -- p_from_start - date of the start of period of transactions to update
1105 -- p_end_end - date of the end of the period of transactions to update
1106 -- p_organization_id - Org for which to find cost updates
1107
1108 PROCEDURE cost_update_inventory (p_from_date DATE, p_to_date DATE,
1109 p_organization_id NUMBER, p_status OUT NOCOPY NUMBER)
1110 IS
1111
1112 -- cost update transactions are stored in the mtl_material_transaction
1113 -- table
1114 -- standard cost update transaction type IDs in mtl_material_transaction
1115 -- table is 24
1116 COST_UPDATE_TRX_ID CONSTANT NUMBER := 24;
1117
1118 -- period_flag = 1 for end of period entries in the opi_ids_push_log
1119 PERIOD_END_ENTRY_FLAG_VAL CONSTANT NUMBER := 1;
1120
1121 -- period_flag = 0 for start of period entries in the opi_ids_push_log
1122 PERIOD_START_ENTRY_FLAG_VAL CONSTANT NUMBER := 0;
1123
1124 -- push_flag = 1 for transactions just pushed into the opi_ids_push_log
1125 JUST_PUSHED_FLAG_VAL CONSTANT NUMBER:= 1;
1126
1127 -- primary cost method for standard cost update is 1
1128 PRIMARY_COST_METHOD_UPDATE CONSTANT NUMBER := 1;
1129
1130
1131 -- cursor for all the distinct item-org combinations that have a
1132 -- registered cost update transaction in the mtl_material_transactions
1136 --
1133 -- within the specified period dates (inclusive).
1134 -- We need to know the item id, the organization id, the transaction
1135 -- date.
1137 -- The data is sorted by organization ID, item ID, and transaction
1138 -- date, so that we will only have to iterate through it once when
1139 -- going down the list and applying cost updates.
1140 CURSOR cost_update_trx_csr (p_from_date DATE, p_to_date DATE,
1141 p_organization_id NUMBER)
1142 IS
1143 SELECT DISTINCT mmt.inventory_item_id inventory_item_id,
1144 mmt.transaction_date transaction_date
1145 FROM mtl_material_transactions mmt, mtl_parameters mp,
1146 mtl_system_items_b msi
1147 WHERE mmt.transaction_type_id = 24
1148 AND mmt.transaction_date BETWEEN p_from_date AND p_to_date
1149 AND mmt.organization_id = p_organization_id
1150 AND mmt.organization_id = mp.organization_id -- standard costing org
1151 AND msi.organization_id = mmt.organization_id
1152 AND msi.inventory_item_id = mmt.inventory_item_id
1153 AND msi.inventory_asset_flag = 'Y' -- don't pick expense items
1154 AND mp.primary_cost_method = 1
1155 ORDER BY mmt.inventory_item_id, mmt.transaction_date;
1156
1157
1158 -- Cursor of all the distinct period end entry dates within the period
1159 -- start and period end dates (inclusive) with the specified item and
1160 -- org in the opi_ids_push_log.
1161 -- This means that the period_flag is set, the push_flag is set
1162 CURSOR period_end_dates_csr (p_organization_id NUMBER,
1163 p_inventory_item_id NUMBER,
1164 p_from_date DATE)
1165 IS
1166 SELECT push_log.trx_date trx_date
1167 FROM opi_ids_push_log push_log
1168 WHERE push_log.organization_id = p_organization_id
1169 AND push_log.inventory_item_id = p_inventory_item_id
1170 AND push_log.period_flag = 1
1171 AND push_log.push_flag = 1
1172 AND push_log.trx_date >= p_from_date
1173 GROUP BY push_log.trx_date
1174 ORDER BY trx_date;
1175
1176 -- variable for updated cost - we need to get this separately due to another
1177 -- bug in costing. This is cost at the end of the period
1178 period_end_unit_cost NUMBER;
1179
1180 -- variable for the cost at the beginning of the day at the end of the
1181 -- period
1182 period_end_beg_unit_cost NUMBER;
1183
1184 -- variable for the unit cost as of the end of the start of the next period
1185 next_period_start_unit_cost NUMBER;
1186
1187 -- variable for the next period start entry after a specified period end
1188 next_period_start DATE;
1189
1190 -- start date of period which contains the p_from_date
1191 l_from_date_per_start DATE := NULL;
1192
1193 BEGIN
1194
1195 -- setting OUT variable: 0 for success, 1 for failure
1196 p_status := 0;
1197
1198 --DBMS_OUTPUT.PUT ('Cost Update for Org # ');
1199 --DBMS_OUTPUT.PUT_LINE (p_organization_id);
1200
1201 -- select the start date of the period containing the p_from_date.
1202 -- We are assuming that the program is not run across periods.
1203 -- However, if there are two incremental runs in the period, we
1204 -- want the subsequent runs for the period to take the lastest
1205 -- cost even if the cost update was not part of this run dates.
1206 SELECT period_start_date
1207 INTO l_from_date_per_start
1208 FROM org_acct_periods
1209 WHERE period_start_date <= trunc (p_from_date)
1210 AND schedule_close_date >= trunc (p_from_date)
1211 AND organization_id = p_organization_id;
1212
1213
1214 -- for every item org combination, do a bulk update of
1215 -- of the inventory value at the period end
1216 FOR cost_update_item_org IN
1217 cost_update_trx_csr (l_from_date_per_start, p_to_date,
1218 p_organization_id)
1219 LOOP
1220
1221 --DBMS_OUTPUT.PUT_LINE ('Looking for period end entries.');
1222
1223 -- Get all the period_ends in the specified date range for this
1224 -- item-org combination.
1225 -- Then update all inventory balances as of all these period
1226 -- end dates.
1227 FOR sub_period_end IN
1228 period_end_dates_csr (p_organization_id,
1229 cost_update_item_org.inventory_item_id,
1230 l_from_date_per_start)
1231 LOOP
1232
1233 --DBMS_OUTPUT.PUT_LINE ('Looking for new cost.');
1234
1235 -- Update the period end entry beginning and end
1236 -- balances.
1237
1238 -- get unit cost of the item as of the end of period
1239 -- for the item/org combination that was updated
1240 period_end_unit_cost :=
1241 cost_on_date (p_organization_id,
1242 cost_update_item_org.inventory_item_id,
1243 sub_period_end.trx_date);
1244
1245 period_end_beg_unit_cost := period_end_unit_cost;
1246
1247 -- Also, we need to update the beginning balance values for
1251 -- activity, then we need to find the cost as of the previous
1248 -- for the period end day we are updating the ending values
1249 -- The beginning value is the same as the ending value if
1250 -- there is no activity on the day. However, if there is
1252 -- day and then use that to update the beginning balance.
1253 IF (activity_on_day (sub_period_end.trx_date, p_organization_id,
1254 cost_update_item_org.inventory_item_id))
1255 THEN
1256 -- Find the cost at the start of the period end date
1257 -- i.e. the cost up to the day before
1258 period_end_beg_unit_cost :=
1259 cost_on_date (p_organization_id,
1260 cost_update_item_org.inventory_item_id,
1261 sub_period_end.trx_date - 1);
1262
1263 END IF; -- end IF (activity_on_day (sub_period_end.trx_date))
1264
1265
1266 -- Now update the end of day balances after checking if there
1267 -- was activity on the day
1268 UPDATE opi_ids_push_log
1269 SET
1270 beg_int_val_b = beg_int_qty * period_end_beg_unit_cost,
1271 beg_onh_val_b = beg_onh_qty * period_end_beg_unit_cost,
1272 beg_wip_val_b = beg_wip_qty * period_end_beg_unit_cost,
1273 end_int_val_b = end_int_qty * period_end_unit_cost,
1274 end_onh_val_b = end_onh_qty * period_end_unit_cost,
1275 end_wip_val_b = end_wip_qty * period_end_unit_cost
1276 WHERE organization_id = p_organization_id
1277 AND inventory_item_id = cost_update_item_org.inventory_item_id
1278 AND trx_date = sub_period_end.trx_date
1279 AND subinventory_code NOT IN -- don't update expense sub
1280 (SELECT secondary_inventory_name
1281 FROM mtl_secondary_inventories
1282 WHERE organization_id = p_organization_id
1283 AND asset_inventory <> 1) -- expense sub
1284 AND period_flag = 1 -- just to be safe
1285 AND push_flag = 1; -- just to be safe
1286
1287
1288 -- Now update the period start entries for the next period
1289 -- because those entries are out of date too.
1290
1291 -- Get the first period start entry past this period end entry.
1292
1293 next_period_start := get_next_period_start
1294 (sub_period_end.trx_date, p_organization_id,
1295 cost_update_item_org.inventory_item_id);
1296
1297 IF(next_period_start is not null)
1298 THEN
1299 next_period_start_unit_cost := period_end_unit_cost;
1300
1301 -- The ending balance is trickier. If there is activity on the
1302 -- next period start day, then the ending balance has to be
1303 -- computed based on the costs of today (to account for
1304 -- cost updates.
1305 IF (activity_on_day (sub_period_end.trx_date, p_organization_id,
1306 cost_update_item_org.inventory_item_id))
1307 THEN
1308 -- get the cost as of this day
1309 next_period_start_unit_cost :=
1310 cost_on_date (p_organization_id,
1311 cost_update_item_org.inventory_item_id,
1312 next_period_start);
1313 END IF;
1314
1315
1316 -- The beginning balance is always the ending balance of
1317 -- the previous period. We can set this after checking
1318 -- if there was any activity on this day. The cost
1319 -- is the same as the end of the previous period.
1320 UPDATE opi_ids_push_log
1321 SET
1322 beg_int_val_b = beg_int_qty * period_end_unit_cost,
1323 beg_onh_val_b = beg_onh_qty * period_end_unit_cost,
1324 beg_wip_val_b = beg_wip_qty * period_end_unit_cost,
1325 end_int_val_b = end_int_qty * next_period_start_unit_cost,
1326 end_onh_val_b = end_onh_qty * next_period_start_unit_cost,
1327 end_wip_val_b = end_wip_qty * next_period_start_unit_cost
1328 WHERE organization_id = p_organization_id
1329 AND inventory_item_id = cost_update_item_org.inventory_item_id
1330 AND subinventory_code NOT IN -- don't update expense sub
1331 (SELECT secondary_inventory_name
1332 FROM mtl_secondary_inventories
1333 WHERE organization_id = p_organization_id
1334 AND asset_inventory <> 1) -- expense sub
1335 AND trx_date = next_period_start
1336 AND period_flag = 0 -- just to be safe
1337 AND push_flag = 1; -- just to be safe
1338
1342
1339 END IF; -- period_start_date not null
1340
1341 END LOOP; -- end FOR sub_period_end
1343 END LOOP; -- end FOR cost_update_item_org
1344
1345
1346 EXCEPTION
1347
1348 WHEN OTHERS
1349 THEN
1350 --DBMS_OUTPUT.PUT_LINE ('Exception Message: ' || SQLERRM);
1351 --DBMS_OUTPUT.PUT_LINE ('Exception Code: ' || SQLCODE);
1352 EDW_LOG.PUT_LINE ('Exception raised in cost_update_inventory');
1353 EDW_LOG.PUT_LINE ('Exception Message: ' || SQLERRM);
1354 EDW_LOG.PUT_LINE ('Exception Code: ' || SQLCODE);
1355 p_status := 1;
1356
1357 END cost_update_inventory;
1358
1359
1360 End opi_edw_ids_calc;
[Home] [Help]