[Home] [Help]
PACKAGE BODY: APPS.OPI_PMI_IDS_SUMMARY
Source
1 PACKAGE BODY OPI_PMI_IDS_SUMMARY AS
2 /*$Header: OPIMINDB.pls 115.19 2004/01/02 19:05:32 bthammin ship $ */
3 PROCEDURE post_perd_bal_recs(p_last_run_date date) IS
4 /*===================================================================================+
5 | post_perd_bal_recs; |
6 | This procedure is to create period marker rows at location, lot |
7 | level for all periods defined in GL Periods from last run date to current|
8 | date. There will be one row at the beginning of the period(period flag=0)|
9 | and one at end of the period(period flag = 1 ). |
10 | These rows are created for collection hook program to get period |
11 | beginning and ending inventory balances. |
12 +===================================================================================*/
13 /*===================================================================================+
14 | Following Cursor identifies whethear new period marker rows need to be created |
15 | or not. |
16 +===================================================================================*/
17 CURSOR check_cldr_for_perd_bal IS
18 select c.co_code co_code,
19 c.start_date start_date,
20 c.end_date end_date,
21 d.last_start_date last_start_date,
22 d.last_end_date last_end_date
23 FROM
24 ( select a.co_code co_code,
25 a.period_num period_num,
26 a.start_date cur_start_date,
27 a.end_Date cur_end_Date,
28 b.start_date last_start_date,
29 b.end_Date last_end_date
30 from OPI_OPM_GL_CALENDAR_V a,OPI_OPM_GL_CALENDAR_V b
31 where a.co_code = b.co_code and
32 sysdate between a.start_date and a.end_Date AND
33 p_last_run_date between b.start_date and b.end_Date and
34 a.start_date <> b.start_date ) d,
35 OPI_OPM_GL_CALENDAR_V c
36 where c.co_code = d.co_code
37 and c.start_date > d.last_end_date
38 and c.start_date <= sysdate
39 order by c.co_code,c.start_date;
40 cldr_rec check_cldr_for_perd_bal%ROWTYPE;
41 BEGIN
42 OPEN check_cldr_for_perd_bal;
43 LOOP
44 FETCH check_cldr_for_perd_bal INTO cldr_rec;
45 EXIT WHEN check_cldr_for_perd_bal%NOTFOUND;
46
47 /* Insert new period marker rows using last period ending period marker rows.
48 If there are no rows in summary table for last period then creates period
49 marker rows using IC_LOCT_INV
50 */
51 edw_log.put_line ('Before creating New Period begin marker row for Company :'||cldr_rec.co_code);
52
53 INSERT INTO opi_pmi_inv_daily_stat_sum (CO_CODE
54 ,ORGN_CODE
55 ,WHSE_CODE
56 ,LOCATION
57 ,AVG_ONH_QTY
58 ,BEG_ONH_QTY
59 ,CREATION_DATE
60 ,END_ONH_QTY
61 ,FROM_ORG_QTY
62 ,INV_ADJ_QTY
63 ,ITEM_ID
64 ,LAST_UPDATE_DATE
65 ,LOT_ID
66 ,PO_DEL_QTY
67 ,TOTAL_REC_QTY
68 ,TOT_CUST_SHIP_QTY
69 ,TOT_ISSUES_QTY
70 ,TO_ORG_QTY
71 ,TRX_DATE
72 ,WIP_COMP_QTY
73 ,WIP_ISSUE_QTY
74 ,period_flag)
75 (SELECT CO_CODE
76 ,ORGN_CODE
77 ,WHSE_CODE
78 ,LOCATION
79 ,AVG_ONH_QTY
80 ,BEG_ONH_QTY
81 ,CREATION_DATE
82 ,END_ONH_QTY
83 ,0 FROM_ORG_QTY
84 ,0 INV_ADJ_QTY
85 ,ITEM_ID
86 ,LAST_UPDATE_DATE
87 ,LOT_ID
88 ,0 PO_DEL_QTY
89 ,0 TOTAL_REC_QTY
90 ,0 TOT_CUST_SHIP_QTY
91 ,0 TOT_ISSUES_QTY
92 ,0 TO_ORG_QTY
93 ,cldr_rec.start_date TRX_DATE
94 ,0 WIP_COMP_QTY
95 ,0 WIP_ISSUE_QTY
96 ,0
97 FROM opi_pmi_inv_daily_stat_sum
98 WHERE co_code = cldr_rec.co_code
99 AND trx_date = cldr_rec.last_end_Date);
100
101 IF sql%rowcount = 0 THEN
102 INSERT INTO opi_pmi_inv_daily_stat_sum (CO_CODE
103 ,ORGN_CODE
104 ,WHSE_CODE
105 ,LOCATION
106 ,AVG_ONH_QTY
107 ,BEG_ONH_QTY
108 ,CREATION_DATE
109 ,END_ONH_QTY
110 ,FROM_ORG_QTY
111 ,INV_ADJ_QTY
112 ,ITEM_ID
113 ,LAST_UPDATE_DATE
114 ,LOT_ID
115 ,PO_DEL_QTY
116 ,TOTAL_REC_QTY
117 ,TOT_CUST_SHIP_QTY
118 ,TOT_ISSUES_QTY
119 ,TO_ORG_QTY
120 ,TRX_DATE
121 ,WIP_COMP_QTY
122 ,WIP_ISSUE_QTY
123 ,period_flag)
124 (SELECT org.CO_CODE
125 ,org.ORGN_CODE
126 ,loct.WHSE_CODE
127 ,loct.LOCATION
128 ,0 AVG_ONH_QTY
129 ,0 BEG_ONH_QTY
130 ,sysdate CREATION_DATE
131 ,0 END_ONH_QTY
132 ,0 FROM_ORG_QTY
133 ,0 INV_ADJ_QTY
134 ,ITEM_ID
135 ,sysdate LAST_UPDATE_DATE
136 ,LOT_ID
137 ,0 PO_DEL_QTY
138 ,0 TOTAL_REC_QTY
139 ,0 TOT_CUST_SHIP_QTY
140 ,0 TOT_ISSUES_QTY
141 ,0 TO_ORG_QTY
142 ,cldr_rec.start_date TRX_DATE
143 ,0 WIP_COMP_QTY
144 ,0 WIP_ISSUE_QTY
145 ,0
146 FROM IC_LOCT_INV loct,
147 IC_WHSE_MST whs,
148 SY_ORGN_MST org
149 WHERE co_code = cldr_rec.co_code
150 AND loct.whse_code = whs.whse_code
151 AND whs.orgn_code = org.orgn_code);
152
153 edw_log.put_line ('After creating New Period begin marker row for Company :'||cldr_rec.co_code );
154 edw_log.put_line ('Before creating New Period end marker row for Company :'||cldr_rec.co_code );
155
156
157
158 INSERT INTO opi_pmi_inv_daily_stat_sum (CO_CODE
159 ,ORGN_CODE
160 ,WHSE_CODE
161 ,LOCATION
162 ,AVG_ONH_QTY
163 ,BEG_ONH_QTY
164 ,CREATION_DATE
165 ,END_ONH_QTY
166 ,FROM_ORG_QTY
167 ,INV_ADJ_QTY
168 ,ITEM_ID
169 ,LAST_UPDATE_DATE
170 ,LOT_ID
171 ,PO_DEL_QTY
172 ,TOTAL_REC_QTY
173 ,TOT_CUST_SHIP_QTY
174 ,TOT_ISSUES_QTY
175 ,TO_ORG_QTY
176 ,TRX_DATE
177 ,WIP_COMP_QTY
178 ,WIP_ISSUE_QTY
179 ,period_flag)
180 (SELECT org.CO_CODE
181 ,org.ORGN_CODE
182 ,loct.WHSE_CODE
183 ,loct.LOCATION
184 ,0 AVG_ONH_QTY
185 ,0 BEG_ONH_QTY
186 ,sysdate CREATION_DATE
187 ,0 END_ONH_QTY
188 ,0 FROM_ORG_QTY
189 ,0 INV_ADJ_QTY
190 ,ITEM_ID
191 ,sysdate LAST_UPDATE_DATE
192 ,LOT_ID
193 ,0 PO_DEL_QTY
194 ,0 TOTAL_REC_QTY
195 ,0 TOT_CUST_SHIP_QTY
196 ,0 TOT_ISSUES_QTY
197 ,0 TO_ORG_QTY
198 ,cldr_rec.end_date TRX_DATE
199 ,0 WIP_COMP_QTY
200 ,0 WIP_ISSUE_QTY
201 ,1
202 FROM IC_LOCT_INV loct,
203 IC_WHSE_MST whs,
204 SY_ORGN_MST org
205 WHERE co_code = cldr_rec.co_code
206 AND loct.whse_code = whs.whse_code
207 AND whs.orgn_code = org.orgn_code);
208 ELSE
209 INSERT INTO opi_pmi_inv_daily_stat_sum (CO_CODE
210 ,ORGN_CODE
211 ,WHSE_CODE
212 ,LOCATION
213 ,AVG_ONH_QTY
214 ,BEG_ONH_QTY
215 ,CREATION_DATE
216 ,END_ONH_QTY
217 ,FROM_ORG_QTY
218 ,INV_ADJ_QTY
219 ,ITEM_ID
220 ,LAST_UPDATE_DATE
221 ,LOT_ID
222 ,PO_DEL_QTY
223 ,TOTAL_REC_QTY
224 ,TOT_CUST_SHIP_QTY
225 ,TOT_ISSUES_QTY
226 ,TO_ORG_QTY
227 ,TRX_DATE
228 ,WIP_COMP_QTY
229 ,WIP_ISSUE_QTY
230 ,period_flag)
231 (SELECT CO_CODE
232 ,ORGN_CODE
233 ,WHSE_CODE
234 ,LOCATION
235 ,AVG_ONH_QTY
236 ,BEG_ONH_QTY
237 ,CREATION_DATE
238 ,END_ONH_QTY
239 ,0 FROM_ORG_QTY
240 ,0 INV_ADJ_QTY
241 ,ITEM_ID
242 ,LAST_UPDATE_DATE
243 ,LOT_ID
244 ,0 PO_DEL_QTY
245 ,0 TOTAL_REC_QTY
246 ,0 TOT_CUST_SHIP_QTY
247 ,0 TOT_ISSUES_QTY
248 ,0 TO_ORG_QTY
249 ,cldr_rec.end_date TRX_DATE
250 ,0 WIP_COMP_QTY
251 ,0 WIP_ISSUE_QTY
252 ,1
253 FROM opi_pmi_inv_daily_stat_sum
254 WHERE co_code = cldr_rec.co_code
255 AND trx_date = cldr_rec.last_end_Date);
256 END IF;
257
258 edw_log.put_line ('Before creating New Period end marker row for Company :'||cldr_rec.co_code );
259 END LOOP;
260 CLOSE check_cldr_for_perd_bal;
261 commit;
262 END post_perd_bal_recs;
263 PROCEDURE populate_net_change (p_last_run_date date) IS
264 /*=================================================================================+
265 | populate_net_change; |
266 | It moves transaction data created after last summarization date upto |
267 | current date into work table from ic_tran_pnd and ic_tran_cmp. |
268 +=================================================================================*/
269 l_stmt varchar2(2000);
270 l_last_run_date date:= p_last_run_date;
271 BEGIN
272 -- get all new trasactions from ic_tran_cmp and ic_tran_pnd and insert into net change table
273
274 edw_log.put_line ('Before Indentifying Delta change :');
275
276 INSERT INTO opi_pmi_trans_inc (co_code,orgn_code,whse_code,item_id,
277 line_id,lot_id,location,doc_type,DOC_ID,DOC_LINE,TRANS_DATE,
278 TRANS_QTY,TRANS_QTY2,QC_GRADE,LOT_STATUS,CURR_BAL
279 ,TRANS_UM,TRANS_UM2,REASON_CODE,LINE_TYPE,CREATION_DATE,LAST_UPDATE_DATE) (
280 SELECT
281 org.CO_CODE,org.ORGN_CODE,cmp.WHSE_CODE,cmp.ITEM_ID,cmp.LINE_ID,cmp.LOT_ID,
282 nvl(cmp.LOCATION,'NONE'),cmp.DOC_TYPE,cmp.DOC_ID,cmp.DOC_LINE,cmp.TRANS_DATE,
283 cmp.TRANS_QTY,cmp.TRANS_QTY2,cmp.QC_GRADE,cmp.LOT_STATUS,loct.LOCT_ONHAND
284 ,cmp.TRANS_UM,cmp.TRANS_UM2,cmp.REASON_CODE,cmp.LINE_TYPE,cmp.CREATION_DATE,cmp.LAST_UPDATE_DATE
285 from ic_tran_cmp cmp,ic_loct_inv loct, sy_orgn_mst org,ic_whse_mst whse
286 where trunc(cmp.last_update_date) >= trunc(l_last_run_date)
287 AND loct.item_id = cmp.item_id AND loct.lot_id = cmp.lot_id
288 AND loct.whse_code = cmp.whse_code AND loct.location = cmp.location
289 AND cmp.whse_code is not null
290 AND whse.whse_code = loct.whse_code
291 AND whse.orgn_code = org.orgn_code
292 and cmp.whse_code = whse.whse_code
293 AND cmp.location is not null)
294 UNION ALL ( SELECT
295 org.CO_CODE,org.ORGN_CODE,pnd.WHSE_CODE,pnd.ITEM_ID,pnd.LINE_ID,pnd.LOT_ID,
296 nvl(pnd.LOCATION,'NONE'),pnd.DOC_TYPE,pnd.DOC_ID,pnd.DOC_LINE,pnd.TRANS_DATE,pnd.TRANS_QTY,
297 pnd.TRANS_QTY2,pnd.QC_GRADE,pnd.LOT_STATUS,loct.LOCT_ONHAND
298 ,pnd.TRANS_UM,pnd.TRANS_UM2,pnd.REASON_CODE,pnd.LINE_TYPE,pnd.CREATION_DATE,pnd.LAST_UPDATE_DATE
299 from ic_tran_pnd pnd ,ic_loct_inv loct , sy_orgn_mst org,ic_whse_mst whse
300 where trunc(pnd.last_update_date) >= trunc(l_last_run_date) AND pnd.COMPLETED_IND = 1
301 AND loct.item_id = pnd.item_id AND loct.lot_id = pnd.lot_id
302 AND loct.whse_code = pnd.whse_code AND loct.location = pnd.location
303 AND whse.whse_code = loct.whse_code
304 AND whse.orgn_code = org.orgn_code
305 and pnd.whse_code = whse.whse_code
306 AND pnd.delete_mark = 0);
307
308 edw_log.put_line ('After Indentifying Delta change :');
309
310 commit;
311 END;
312
313 PROCEDURE populate_day_sum_temp IS
314 /*===================================================================================+
315 | populate_day_sum_temp; |
316 | Summarize net change records at day level and transform rows |
317 | from horizantal (row) structutre to vertical (column) structure |
318 | In transaction table transaction are stored in multiple rows |
319 | we need to summarize data into multiple columns based on doc_type. |
320 | All transactions are summarized at day level grouped by Transaction Type |
321 +===================================================================================*/
322
323 l_stmt varchar2(2000);
324 BEGIN
325
326 /* summarize new transactions at day level and convert from
327 horizontal (row structure) to vertical (column) structure
328 We use trunc(trans_date) to summarize all transaction at day level
329 All Summarizations are Lot, Location,day Level
330 All OPM(Oracle Process Manufacturing) Transactions are grouped as follows
331
332 TOTAL_ISSUES_QTY This is sum of all transaction which reduces inventory
333 OPSO Sales Orders
334 OPBO Blanket Sales Order
335 OMSO Sales Orders Created through Order Management
336 PROD and LINE_TYPE = -1 Ingredients consumed in Production
337 TRNI and LINE_TYPE = -1 (Transfer Immediate) Transferred out Quantity
338 TRNR and LINE_TYPE = -1 (Transfer Journal) Transferred out Quantity
339 XFER If trans_qty < 0 Transfer Transaction
340 WIP_COMP_QTY This is sum of product/by product Quantity Produced on a given day
341 PROD Production Transaction
342 LINE_TYPE = 1 Product
343 LINE_TYPE = 2 By Product
344 WIP_ISSUE_QTY This is sum of Ingredient Quantity on a given day
345 PROD Production Transaction
346 LINE_TYPE = -1 Ingredient
347 TOTAL_REC_QTY Total quantity received
348 RECV Purchase Order Receiving
349 POSR Purchase Order Quick Receipt
350 CREI Create Immediate
351 CRER Create Journal
352 PROD Production Transactions (LINE_TYPE 1-Product 2-Byproduct)
353 TRNI and LINE_TYPE = 1 (Transfer Immediate) Transferred in Quantity
354 TRNR and LINE_TYPE = 1 (Transfer Journal) Transferred in Quantity
355 XFER If trans_qty > 0 Transfer Transaction
356 TOT_CUST_SHIP_QTY Total Quantity Shipped to Customer
357 OPSO Sales Orders
358 OPBO Blanket Sales Order
359 OMSO Sales Orders Created through Order Management
360 INV_ADJ_QTY Total Inventory Adjustments
361 ADJI Adjust Immediate
362 ADJR Adjust Journal
363 PICY Physical Inventory - Cycle No
364 PIPH Physical Inventory - Physical
365 REPI Replace Quantity/Status - Immediate
366 REPR Replace Quantity/Status - Journaled
367 PO_DEL_QTY Purchase Order Delivered Quantity
368 RECV Purchase Order Receiving
369 POSR Purchase Order Quick Receipt
370 RTRN Purchase Order Returns
371 TO_ORG_QTY Quantity Transferred in
372 TRNI and LINE_TYPE = 1 (Transfer Immediate) Transferred in Quantity
373 TRNR and LINE_TYPE = 1 (Transfer Journal) Transferred in Quantity
374 XFER If trans_qty > 0 Transfer Transaction
375 FROM_ORG_QTY Quantiry Transferred Out
376 TRNI and LINE_TYPE = -1 (Transfer Immediate) Transferred out Quantity
377 TRNR and LINE_TYPE = -1 (Transfer Journal) Transferred out Quantity
378 XFER If trans_qty < 0 Transfer Transaction */
379
380
381 edw_log.put_line ('After Indentifying Delta change :');
382
383 INSERT INTO opi_pmi_day_sum_temp
384 (co_code,orgn_code,whse_code,item_id,lot_id,location,trans_date,TOT_ISSUES_QTY,
385 WIP_COMP_QTY,WIP_ISSUE_QTY,TOTAL_REC_QTY,TOT_CUST_SHIP_QTY, INV_ADJ_QTY,
386 PO_DEL_QTY,TO_ORG_QTY,FROM_ORG_QTY,
387 Period_start_date,Period_end_date,cost_mthd,curr_bal)
388 (select inc.co_code,Orgn_code,whse_code,item_id,lot_id,LOCATION,trunc(trans_date) Trans_date,
389 sum(decode(doc_type,'OPSO',trans_qty,'OPBO',trans_qty,'OMSO',trans_qty,
390 'PROD',decode(line_type,-1,trans_qty),
391 'TRNI',decode(line_type,-1,trans_qty),
392 'TRNR',decode(line_type,-1,trans_qty),
393 'XFER',decode(SIGN(trans_qty),-1,trans_qty),0)) TOTAL_ISSUES_QTY,
394 sum(decode(doc_type,'PROD',decode(line_type,1,trans_qty,2,trans_qty),0)) WIP_COMP_QTY,
398 'TRNI',decode(line_type,1,trans_qty),'TRNR',decode(line_type,1,trans_qty),
395 sum(decode(doc_type,'PROD',decode(line_type,-1,trans_qty),0)) WIP_ISSUE_QTY,
396 sum(decode(doc_type,'RECV',trans_qty,'POSR',trans_qty,'RTRN',trans_qty,
397 'CREI',trans_qty,'CRER',trans_qty,'PROD',decode(line_type,1,trans_qty,2,trans_qty),
399 'XFER',decode(SIGN(trans_qty),1,trans_qty),0)) TOTAL_REC_QTY,
400 sum(decode(doc_type,'OPSO',trans_qty,'OPBO',trans_qty,'OMSO',trans_qty,0)) TOT_CUST_SHIP_QTY,
401 sum(decode(doc_type,'ADJI',trans_qty,'ADJR',trans_qty,'PICY',trans_qty,'PIPH',trans_qty,
402 'REPI',trans_qty,'REPR',trans_qty,0 )) INV_ADJ_QTY,
403 sum(decode(doc_type,'RECV',trans_qty,'POSR',trans_qty,
404 'PORD',trans_qty,'RTRN',trans_qty,0)) PO_DEL_QTY,
405 sum(decode(doc_type,'TRNI',decode(line_type,1,trans_qty),
406 'TRNR',decode(line_type,1,trans_qty),
407 'XFER',decode(SIGN(trans_qty),1,trans_qty,0),0)) TO_ORG_qty,
408 sum(decode(doc_type,'TRNI',decode(line_type,-1,trans_qty),
409 'TRNR',decode(line_type,-1,trans_qty),
410 'XFER',decode(SIGN(trans_qty),-1,trans_qty,0),0)) FROM_ORG_QTY,
411 glcldr.Start_date Period_start_date,
412 glcldr.end_Date Period_end_date,
413 glcldr.gl_cost_mthd cost_mthd,
414 inc.curr_bal curr_bal
415 from opi_pmi_trans_inc inc,OPI_OPM_GL_CALENDAR_V glcldr
416 where trunc(trans_date) between glcldr.start_date and glcldr.end_date
417 AND inc.co_code = glcldr.co_code
418 group by inc.co_code,Orgn_code,whse_code,item_id,lot_id,Location,trunc(trans_date),
419 glcldr.Start_date, glcldr.end_Date,inc.curr_bal,glcldr.gl_cost_mthd);
420 commit;
421
422 edw_log.put_line ('After Summarizing data at day level :');
423
424 END;
425
426
427 PROCEDURE identify_summary_recs_to_chng IS
428 /*===================================================================================+
429 | identify_summary_recs_to_chng; |
430 | Identifies Rows to be re-summarized becasue of new transaction. This |
431 | procedure moves rows identified from Summary table to work table |
432 +===================================================================================*/
433 l_stmt varchar2(4000);
434 BEGIN
435 /* Following insert statement identifies from which date we need to re-summarize summary table
436 for each lot,warehouse,location */
437
438
439 edw_log.put_line ('Before idenfying rows to be re-summaeized :');
440
441 INSERT INTO opi_pmi_ids_idnt (co_code,orgn_code,whse_code,location,item_id,lot_id, trans_date ,
442 start_date ,end_date) (
443 SELECT co_code,orgn_code,whse_code,location,item_id,lot_id,min(trans_date) trans_date ,
444 min(period_start_date) start_date ,min(period_end_date) end_date
445 FROM opi_pmi_day_sum_temp inc
446 group by co_code,orgn_code,whse_code,location,item_id,lot_id );
447
448
449 edw_log.put_line ('After idenfying rows to be re-summarized :');
450
451 -- dbms_output.put_line (' Before Insert ');
452
453 /* following insert moves data to be re-summarized from summary table to work table identified
454 in above step and marks these row with operation_code = 'UPDATE' */
455
456
457 edw_log.put_line ('Before moving rows to be re-summaeized to temp table :');
458
459 INSERT INTO opi_pmi_ids_temp (CO_CODE
460 ,ORGN_CODE
461 ,WHSE_CODE
462 ,LOCATION
463 ,AVG_ONH_QTY
464 ,BEG_ONH_QTY
465 ,CREATION_DATE
466 ,END_ONH_QTY
467 ,FROM_ORG_QTY
468 ,INV_ADJ_QTY
469 ,ITEM_ID
470 ,LAST_UPDATE_DATE
471 ,LOT_ID
472 ,PO_DEL_QTY
473 ,TOTAL_REC_QTY
474 ,TOT_CUST_SHIP_QTY
475 ,TOT_ISSUES_QTY
476 ,TO_ORG_QTY
477 ,TRX_DATE
478 ,WIP_COMP_QTY
479 ,WIP_ISSUE_QTY
480 ,period_flag
481 ,OPERATION_CODE ) (
482 SELECT sm.CO_CODE
483 ,sm.ORGN_CODE
484 ,sm.WHSE_CODE
485 ,sm.LOCATION
486 ,sm.AVG_ONH_QTY
487 ,sm.BEG_ONH_QTY
488 ,sm.CREATION_DATE
489 ,sm.END_ONH_QTY
490 ,sm.FROM_ORG_QTY
491 ,sm.INV_ADJ_QTY
492 ,sm.ITEM_ID
493 ,sm.LAST_UPDATE_DATE
494 ,sm.LOT_ID
495 ,sm.PO_DEL_QTY
496 ,sm.TOTAL_REC_QTY
497 ,sm.TOT_CUST_SHIP_QTY
498 ,sm.TOT_ISSUES_QTY
499 ,sm.TO_ORG_QTY
500 ,sm.TRX_DATE
501 ,sm.WIP_COMP_QTY
502 ,sm.WIP_ISSUE_QTY
503 ,sm.period_flag
504 ,'UPDATE' OPERATION_CODE
505 from opi_pmi_inv_daily_stat_sum sm, opi_pmi_ids_idnt idnt
506 where idnt.co_code = sm.co_code AND
507 idnt.orgn_code = sm.orgn_code AND
508 idnt.whse_code = sm.whse_code AND
509 idnt.location = sm.location AND
510 idnt.item_id = sm.item_id AND
511 idnt.lot_id = sm.lot_id AND
512 sm.trx_date >= idnt.start_date ) ;
513 commit;
514
515 edw_log.put_line ('rows to be re-summaeized are moved to temp table :');
516
517 END identify_summary_recs_to_chng;
518
519
520
521 PROCEDURE summarize_temp_summary IS
525 +===================================================================================*/
522 /*===================================================================================+
523 | This Procedure Summarizes and calculates day beginning and Ending Balances |
524 | and Moves data from day summary table to temporary summary table |
526
527 CURSOR summerize_temp_sum IS
528 select * from opi_pmi_day_sum_temp
529 order by trans_date ASC;
530 rec_summary summerize_temp_sum%ROWTYPE;
531 CURSOR get_records_to_change IS
532 select *
533 from opi_pmi_ids_temp
534 where co_code = rec_summary.co_code
535 AND whse_code = rec_summary.whse_code
536 AND orgn_code = rec_summary.orgn_code
537 AND Location = rec_summary.location
538 AND item_id = rec_summary.item_id
539 AND lot_id = rec_summary.lot_id
540 AND trx_Date >= rec_summary.trans_date
541 order by trx_date ASC;
542 CURSOR get_balance_row IS
543 select trx_Date,end_onh_qty,beg_onh_qty
544 from opi_pmi_inv_daily_stat_sum
545 where co_code = rec_summary.co_code
546 AND whse_code = rec_summary.whse_code
547 AND orgn_code = rec_summary.orgn_code
548 AND Location = rec_summary.location
549 AND item_id = rec_summary.item_id
550 AND lot_id = rec_summary.lot_id
551 AND trx_Date < rec_summary.trans_date
552 order by trx_date desc;
553 ids_bal_rec get_balance_row%ROWTYPE;
554 ids_temp_rec get_records_to_change%ROWTYPE;
555 l_cost_mthd cm_cmpt_dtl.cost_mthd_code%TYPE;
556 l_cmpntcls_id cm_cmpt_dtl.cost_cmpntcls_id%TYPE := null;
557 l_analysis_code cm_cmpt_dtl.cost_analysis_code%TYPE := null;
558 L_perd_st_date DATE;
559 l_perd_end_date DATE;
560 l_retreive_ind number := 1;
561 l_total_cost number;
562 l_no_of_rows number;
563 rc number;
564 l_end_qty NUMBER := 0;
565 l_beg_qty number := 0;
566 l_day_tr number := 0;
567 l_n_tr_qty number := 0;
568 l_n_beg_qty number := 0;
569 l_n_end_qty number := 0;
570 l_prev_end_qty number := null;
571 BEGIN
572
573 edw_log.put_line ('at start of Summarize and calculate day beginning and Ending Balances :');
574
575 OPEN summerize_temp_sum;
576 LOOP
577 FETCH summerize_temp_sum INTO rec_summary;
578 EXIT when summerize_temp_sum%NOTFOUND;
579 BEGIN
580 /* update row if already a row exists for company,organization,warehouse,location
581 item,lot and transaction date */
582 UPDATE opi_pmi_ids_temp
583 SET wip_comp_qty = rec_summary.wip_comp_qty,
584 wip_issue_qty = rec_summary.wip_issue_qty,
585 po_del_qty = rec_summary.po_del_qty,
586 total_rec_qty = rec_summary.total_rec_qty,
587 from_org_qty = rec_summary.from_org_qty,
588 to_org_qty = rec_summary.to_org_qty,
589 tot_cust_ship_qty = rec_summary.tot_cust_ship_qty,
590 inv_adj_qty = rec_summary.inv_adj_qty,
591 tot_issues_qty = rec_summary.tot_issues_qty,
592 creation_date = sysdate,
593 last_update_date = sysdate
594 WHERE co_code = rec_summary.co_code
595 AND whse_code = rec_summary.whse_code
596 AND orgn_code = rec_summary.orgn_code
597 AND Location = rec_summary.location
598 AND item_id = rec_summary.item_id
599 AND lot_id = rec_summary.lot_id
600 AND trunc(trx_Date) = trunc(rec_summary.trans_date);
601 /* if above update fails to find a matching row for company,organization,warehouse,location
602 item,lot and transaction date combination then insert the row with operation code = 'INSERT'
603 */
604
605 if sql%rowcount = 0 THEN
606 INSERT into opi_pmi_ids_temp ( co_code,
607 orgn_code,
608 whse_code,
609 location,
610 item_id,
611 lot_id,
612 trx_date,
613 wip_comp_qty,
614 wip_issue_qty,
615 po_del_qty,
616 total_rec_qty,
617 from_org_qty,
618 to_org_qty,
619 tot_cust_ship_qty,
620 inv_adj_qty,
621 tot_issues_qty,
622 creation_date,
623 last_update_date,
624 operation_code)
625 VALUES
626 (rec_summary.co_code,
627 rec_summary.orgn_code,
628 rec_summary.whse_code,
629 rec_summary.location,
630 rec_summary.item_id,
631 rec_summary.lot_id,
635 rec_summary.po_del_qty,
632 rec_summary.trans_date,
633 rec_summary.wip_comp_qty,
634 rec_summary.wip_issue_qty,
636 rec_summary.total_rec_qty,
637 rec_summary.from_org_qty,
638 rec_summary.to_org_qty,
639 rec_summary.tot_cust_ship_qty,
640 rec_summary.inv_adj_qty,
641 rec_summary.tot_issues_qty,
642 sysdate,
643 sysdate,
644 'INSERT');
645 END IF;
646 END;
647 END LOOP;
648 CLOSE summerize_temp_sum;
649 commit;
650
651
652 edw_log.put_line ('at end of Summarize and calculate day beginning and Ending Balances :');
653 edw_log.put_line ('at start of Calculate day beginning and Ending Balances :');
654
655
656 -- Calculate On Hand Balances
657 OPEN summerize_temp_sum;
658 LOOP
659 FETCH summerize_temp_sum INTO rec_summary;
660 EXIT when summerize_temp_sum%NOTFOUND;
661 OPEN get_balance_row;
662 FETCH get_balance_row into ids_bal_rec;
663
664 /*
665 if we can't find the balance row for company,organization,warehouse,location,item,lot and transaction date
666 combination then we need to create the period marker rows for this combination since this combination is
667 first occrence */
668 IF get_balance_row%NOTFOUND THEN
669 ids_bal_rec.end_onh_qty := NULL;
670 ids_bal_rec.beg_onh_qty := NULL;
671 ids_bal_rec.trx_date := NULL;
672 BEGIN
673 UPDATE opi_pmi_ids_temp
674 SET period_flag = 0
675 where co_code = rec_summary.co_code
676 AND whse_code = rec_summary.whse_code
677 AND orgn_code = rec_summary.orgn_code
678 AND Location = rec_summary.location
679 AND item_id = rec_summary.item_id
680 AND lot_id = rec_summary.lot_id
681 AND trx_Date = rec_summary.period_start_date;
682 IF sql%rowCOUNT = 0 THEN
683 INSERT into opi_pmi_ids_temp ( co_code,
684 orgn_code,
685 whse_code,
686 location,
687 item_id,
688 lot_id,
689 trx_date,
690 wip_comp_qty,
691 wip_issue_qty,
692 po_del_qty,
693 total_rec_qty,
694 from_org_qty,
695 to_org_qty,
696 tot_cust_ship_qty,
697 inv_adj_qty,
698 tot_issues_qty,
699 beg_onh_qty,
700 end_onh_qty,
701 avg_onh_qty,
702 period_flag,
703 creation_date,
704 last_update_date,
705 operation_code)
706 VALUES
707 (rec_summary.co_code,
708 rec_summary.orgn_code,
709 rec_summary.whse_code,
710 rec_summary.location,
711 rec_summary.item_id,
712 rec_summary.lot_id,
713 rec_summary.period_start_date,
714 0,
715 0,
716 0,
717 0,
718 0,
719 0,
720 0,
721 0,
722 0,
723 0,
724 0,
725 0,
726 0,
727 sysdate,
728 sysdate,
729 'INSERT');
730 END IF;
731 UPDATE opi_pmi_ids_temp
732 SET period_flag = 1
733 where co_code = rec_summary.co_code
734 AND whse_code = rec_summary.whse_code
735 AND orgn_code = rec_summary.orgn_code
736 AND Location = rec_summary.location
737 AND item_id = rec_summary.item_id
738 AND lot_id = rec_summary.lot_id
739 AND trx_Date = rec_summary.period_end_date;
740 IF SQL%ROWCOUNT = 0 THEN
741 INSERT into opi_pmi_ids_temp ( co_code,
742 orgn_code,
743 whse_code,
744 location,
745 item_id,
746 lot_id,
747 trx_date,
748 wip_comp_qty,
749 wip_issue_qty,
750 po_del_qty,
751 total_rec_qty,
752 from_org_qty,
753 to_org_qty,
754 tot_cust_ship_qty,
758 end_onh_qty,
755 inv_adj_qty,
756 tot_issues_qty,
757 beg_onh_qty,
759 avg_onh_qty,
760 period_flag,
761 creation_date,
762 last_update_date,
763 operation_code)
764 VALUES
765 (rec_summary.co_code,
766 rec_summary.orgn_code,
767 rec_summary.whse_code,
768 rec_summary.location,
769 rec_summary.item_id,
770 rec_summary.lot_id,
771 rec_summary.period_end_date,
772 0,
773 0,
774 0,
775 0,
776 0,
777 0,
778 0,
779 0,
780 0,
781 0,
782 0,
783 0,
784 1,
785 sysdate,
786 sysdate,
787 'INSERT');
788 END IF;
789 END;
790 UPDATE opi_pmi_ids_temp
791 SET period_flag = 0
792 where co_code = rec_summary.co_code
793 AND whse_code = rec_summary.whse_code
794 AND orgn_code = rec_summary.orgn_code
795 AND Location = rec_summary.location
796 AND item_id = rec_summary.item_id
797 AND lot_id = rec_summary.lot_id
798 AND trx_Date = rec_summary.period_start_date;
799 UPDATE opi_pmi_ids_temp
800 SET period_flag = 1
801 where co_code = rec_summary.co_code
802 AND whse_code = rec_summary.whse_code
803 AND orgn_code = rec_summary.orgn_code
804 AND Location = rec_summary.location
805 AND item_id = rec_summary.item_id
806 AND lot_id = rec_summary.lot_id
807 AND trx_Date = rec_summary.period_end_date;
808
809
810 /* following code is added to fix bug #1700563
811
812 /********************************************************************************************************************
813 *** Above Insert statement creates period marker rows for current rows period. we need to insert marker ***
814 *** rows for all the periods from current process period to either sysdate or till the period row which ***
815 *** already summarized. i.e. take period1 .. period4. we have data for period4 and now we are getting ***
816 *** a backposted transaction in period1 then we need to create period marker rows for period1 though period 3. ***
817 *** above insert statements ensures we get period marker rows for period1. following is to populate period ***
818 *** marker rows for period2 and period3. at the same time if this is the first transaction for company, ***
819 *** organization,warehouse,location,item,lot and transaction date combination then we need to create period ***
820 *** marker rows till current period. ***
821 *********************************************************************************************************************/
822
823 INSERT into opi_pmi_ids_temp ( co_code,
824 orgn_code,
825 whse_code,
826 location,
827 item_id,
828 lot_id,
829 trx_date,
830 wip_comp_qty,
831 wip_issue_qty,
832 po_del_qty,
833 total_rec_qty,
834 from_org_qty,
835 to_org_qty,
836 tot_cust_ship_qty,
837 inv_adj_qty,
838 tot_issues_qty,
839 beg_onh_qty,
840 end_onh_qty,
841 avg_onh_qty,
842 period_flag,
843 creation_date,
844 last_update_date,
845 operation_code)
846 (SELECT p_marker_rows.co_code,
847 p_marker_rows.orgn_code,
848 p_marker_rows.whse_code,
849 p_marker_rows.location,
850 p_marker_rows.item_id,
851 p_marker_rows.lot_id,
852 p_marker_rows.start_date,
853 0,
854 0,
855 0,
856 0,
857 0,
858 0,
859 0,
860 0,
861 0,
862 0,
863 0,
864 0,
868 'INSERT'
865 0,
866 sysdate,
867 sysdate,
869 FROM
870 ((SELECT summ.co_code co_code,
871 summ.orgn_code orgn_code,
872 summ.whse_code whse_code,
873 summ.location location,
874 summ.item_id item_id,
875 summ.lot_id lot_id,
876 glcldr.start_date start_date
877 FROM opi_pmi_ids_temp summ,
878 opi_opm_gl_calendar_v glcldr
879 WHERE summ.period_flag = 1
880 and trunc(summ.trx_date) = trunc(rec_summary.period_end_date)
881 and summ.co_code = glcldr.co_code
882 and glcldr.start_date > summ.trx_date
883 and glcldr.start_date <= SYSDATE
884 and summ.co_code = rec_summary.co_code
885 and summ.orgn_code = rec_summary.orgn_code
886 and summ.whse_code = rec_summary.whse_code
887 and summ.location = rec_summary.location
888 and summ.item_id = rec_summary.item_id
889 and summ.lot_id = rec_summary.lot_id)
890 MINUS
891 (SELECT summ.co_code co_code,
892 summ.orgn_code orgn_code,
893 summ.whse_code whse_code,
894 summ.location location,
895 summ.item_id item_id,
896 summ.lot_id lot_id,
897 summ.trx_date start_date
898 FROM opi_pmi_ids_temp summ,
899 opi_opm_gl_calendar_v glcldr
900 WHERE trunc(summ.trx_date) >= trunc(rec_summary.period_end_date)
901 and summ.co_code = glcldr.co_code
902 and glcldr.start_date = summ.trx_date
903 and glcldr.start_date <= SYSDATE
904 and summ.co_code = rec_summary.co_code
905 and summ.orgn_code = rec_summary.orgn_code
906 and summ.whse_code = rec_summary.whse_code
907 and summ.location = rec_summary.location
908 and summ.item_id = rec_summary.item_id
909 and summ.lot_id = rec_summary.lot_id)) p_marker_rows);
910
911 INSERT into opi_pmi_ids_temp ( co_code,
912 orgn_code,
913 whse_code,
914 location,
915 item_id,
916 lot_id,
917 trx_date,
918 wip_comp_qty,
919 wip_issue_qty,
920 po_del_qty,
921 total_rec_qty,
922 from_org_qty,
923 to_org_qty,
924 tot_cust_ship_qty,
925 inv_adj_qty,
926 tot_issues_qty,
927 beg_onh_qty,
928 end_onh_qty,
929 avg_onh_qty,
930 period_flag,
931 creation_date,
932 last_update_date,
933 operation_code)
934 (SELECT p_marker_rows.co_code,
935 p_marker_rows.orgn_code,
936 p_marker_rows.whse_code,
937 p_marker_rows.location,
938 p_marker_rows.item_id,
939 p_marker_rows.lot_id,
940 p_marker_rows.end_date,
941 0,
942 0,
943 0,
944 0,
945 0,
946 0,
947 0,
948 0,
949 0,
950 0,
951 0,
952 0,
953 1,
954 sysdate,
955 sysdate,
956 'INSERT'
957 FROM
958 ((SELECT summ.co_code co_code,
959 summ.orgn_code orgn_code,
960 summ.whse_code whse_code,
961 summ.location location,
962 summ.item_id item_id,
963 summ.lot_id lot_id,
964 glcldr.end_date end_date
965 FROM opi_pmi_ids_temp summ,
966 opi_opm_gl_calendar_v glcldr
967 WHERE summ.period_flag = 1
968 and trunc(summ.trx_date) = trunc(rec_summary.period_end_date)
969 and summ.co_code = glcldr.co_code
970 and glcldr.start_date > summ.trx_date
974 and summ.whse_code = rec_summary.whse_code
971 and glcldr.start_date <= SYSDATE
972 and summ.co_code = rec_summary.co_code
973 and summ.orgn_code = rec_summary.orgn_code
975 and summ.location = rec_summary.location
976 and summ.item_id = rec_summary.item_id
977 and summ.lot_id = rec_summary.lot_id)
978 MINUS
979 (SELECT summ.co_code co_code,
980 summ.orgn_code orgn_code,
981 summ.whse_code whse_code,
982 summ.location location,
983 summ.item_id item_id,
984 summ.lot_id lot_id,
985 summ.trx_date end_date
986 FROM opi_pmi_ids_temp summ,
987 opi_opm_gl_calendar_v glcldr
988 WHERE trunc(summ.trx_date) >= trunc(rec_summary.period_end_date)
989 and summ.co_code = glcldr.co_code
990 and glcldr.end_date = summ.trx_date
991 and glcldr.start_date <= SYSDATE
992 and summ.co_code = rec_summary.co_code
993 and summ.orgn_code = rec_summary.orgn_code
994 and summ.whse_code = rec_summary.whse_code
995 and summ.location = rec_summary.location
996 and summ.item_id = rec_summary.item_id
997 and summ.lot_id = rec_summary.lot_id)) p_marker_rows);
998 END IF;
999 CLOSE get_balance_row;
1000 l_prev_end_qty := nvl(ids_bal_rec.end_onh_qty,0);
1001 l_n_tr_qty := nvl(rec_summary.tot_issues_qty,0)+nvl(rec_summary.total_rec_qty,0)+
1002 nvl(rec_summary.inv_adj_qty,0);
1003 OPEN get_records_to_change;
1004 LOOP
1005 FETCH get_records_to_change into ids_temp_rec;
1006 EXIT WHEN get_records_to_change%NOTFOUND;
1007 l_beg_qty := ids_temp_rec.BEG_ONH_QTY;
1008 l_end_qty := ids_temp_rec.END_ONH_QTY;
1009 l_day_tr := nvl(ids_temp_rec.END_ONH_QTY,0) - nvl(ids_temp_rec.BEG_ONH_QTY,0);
1010 l_n_tr_qty:= nvl(ids_temp_rec.tot_issues_qty,0)+nvl(ids_temp_rec.total_rec_qty,0)+
1011 nvl(ids_temp_rec.inv_adj_qty,0);
1012
1013 IF ids_bal_rec.trx_date = ids_temp_rec.trx_date THEN
1014 l_n_beg_qty := nvl(ids_bal_rec.beg_onh_qty,0);
1015 l_n_end_qty := nvl(ids_bal_rec.end_onh_qty,0) + l_n_tr_qty;
1016 ELSE
1017 l_n_beg_qty := l_prev_end_qty;
1018 l_n_end_qty := l_n_beg_qty + l_n_tr_qty;
1019 END IF;
1020 l_prev_end_qty := l_n_end_qty;
1021 update opi_pmi_ids_temp
1022 set
1023 AVG_ONH_QTY = (l_n_beg_qty+l_n_end_qty)/2,
1024 BEG_ONH_QTY = l_n_beg_qty,
1025 END_ONH_QTY = l_n_end_qty,
1026 creation_date = sysdate,
1027 last_update_date = sysdate
1028 WHERE co_code = ids_temp_rec.co_code
1029 AND whse_code = ids_temp_rec.whse_code
1030 AND orgn_code = ids_temp_rec.orgn_code
1031 AND Location = ids_temp_rec.location
1032 AND item_id = ids_temp_rec.item_id
1033 AND lot_id = ids_temp_rec.lot_id
1034 AND trx_Date = ids_temp_rec.trx_date;
1035 END LOOP;
1036 CLOSE get_records_to_change;
1037 END LOOP;
1038 CLOSE summerize_temp_sum;
1039 edw_log.put_line ('at End of Calculate day beginning and Ending Balances :');
1040
1041 commit;
1042 END summarize_temp_summary;
1043
1044
1045 PROCEDURE apply_to_summary_tab IS
1046 /*===================================================================================+
1047 | apply_to_summary_tab; |
1048 | This procedure moves data from work table to Actual summary table. |
1049 +===================================================================================*/
1050 l_update_row_cnt number;
1051 l_insert_row_cnt number;
1052 BEGIN
1053 /* Insert all new rows from work table into summary table using operation code = 'INSERT'*/
1054
1055 insert into opi_pmi_inv_daily_stat_sum (CO_CODE
1056 ,ORGN_CODE
1057 ,WHSE_CODE
1058 ,LOCATION
1059 ,AVG_ONH_QTY
1060 ,BEG_ONH_QTY
1061 ,CREATION_DATE
1062 ,END_ONH_QTY
1063 ,FROM_ORG_QTY
1064 ,INV_ADJ_QTY
1065 ,ITEM_ID
1066 ,LAST_UPDATE_DATE
1067 ,LOT_ID
1068 ,PO_DEL_QTY
1069 ,TOTAL_REC_QTY
1070 ,TOT_CUST_SHIP_QTY
1071 ,TOT_ISSUES_QTY
1072 ,TO_ORG_QTY
1073 ,TRX_DATE
1074 ,WIP_COMP_QTY
1075 ,WIP_ISSUE_QTY
1076 ,period_flag)
1077 (SELECT CO_CODE
1078 ,ORGN_CODE
1079 ,WHSE_CODE
1080 ,LOCATION
1081 ,AVG_ONH_QTY
1082 ,BEG_ONH_QTY
1083 ,CREATION_DATE
1084 ,END_ONH_QTY
1085 ,FROM_ORG_QTY
1086 ,INV_ADJ_QTY
1087 ,ITEM_ID
1088 ,LAST_UPDATE_DATE
1089 ,LOT_ID
1090 ,PO_DEL_QTY
1091 ,TOTAL_REC_QTY
1092 ,TOT_CUST_SHIP_QTY
1093 ,TOT_ISSUES_QTY
1094 ,TO_ORG_QTY
1098 ,period_flag
1095 ,TRX_DATE
1096 ,WIP_COMP_QTY
1097 ,WIP_ISSUE_QTY
1099 FROM opi_pmi_ids_temp
1100 WHERE OPERATION_CODE = 'INSERT');
1101
1102 l_insert_row_cnt := sql%rowcount;
1103
1104 -- dbms_output.put_line('Rows inserted '||l_insert_row_cnt);
1105 commit;
1106 /* update summary table using rows marked with operation code = 'UPDATE' in work table */
1107
1108 UPDATE opi_pmi_inv_daily_stat_sum sm
1109 SET (
1110 AVG_ONH_QTY
1111 ,BEG_ONH_QTY
1112 ,CREATION_DATE
1113 ,END_ONH_QTY
1114 ,FROM_ORG_QTY
1115 ,INV_ADJ_QTY
1116 ,LAST_UPDATE_DATE
1117 ,PO_DEL_QTY
1118 ,TOTAL_REC_QTY
1119 ,TOT_CUST_SHIP_QTY
1120 ,TOT_ISSUES_QTY
1121 ,TO_ORG_QTY
1122 ,WIP_COMP_QTY
1123 ,WIP_ISSUE_QTY
1124 ,period_flag) =
1125 (SELECT
1126 tmp.AVG_ONH_QTY
1127 ,tmp.BEG_ONH_QTY
1128 ,tmp.CREATION_DATE
1129 ,tmp.END_ONH_QTY
1130 ,tmp.FROM_ORG_QTY
1131 ,tmp.INV_ADJ_QTY
1132 ,tmp.LAST_UPDATE_DATE
1133 ,tmp.PO_DEL_QTY
1134 ,tmp.TOTAL_REC_QTY
1135 ,tmp.TOT_CUST_SHIP_QTY
1136 ,tmp.TOT_ISSUES_QTY
1137 ,tmp.TO_ORG_QTY
1138 ,tmp.WIP_COMP_QTY
1139 ,tmp.WIP_ISSUE_QTY
1140 ,tmp.period_flag
1141 FROM opi_pmi_ids_temp tmp
1142 WHERE sm.CO_CODE = tmp.CO_CODE
1143 AND sm.ORGN_CODE = tmp.ORGN_CODE
1144 AND sm.WHSE_CODE = tmp.WHSE_CODE
1145 AND sm.LOCATION = tmp.LOCATION
1146 AND sm.LOT_ID = tmp.LOT_ID
1147 AND sm.ITEM_ID = tmp.ITEM_ID
1148 AND sm.TRX_DATE = tmp.TRX_DATE
1149 AND tmp.OPERATION_CODE = 'UPDATE')
1150 where exists (select 1
1151 FROM opi_pmi_ids_temp tmp
1152 WHERE sm.CO_CODE = tmp.CO_CODE
1153 AND sm.ORGN_CODE = tmp.ORGN_CODE
1154 AND sm.WHSE_CODE = tmp.WHSE_CODE
1155 AND sm.LOCATION = tmp.LOCATION
1156 AND sm.LOT_ID = tmp.LOT_ID
1157 AND sm.ITEM_ID = tmp.ITEM_ID
1158 AND sm.TRX_DATE = tmp.TRX_DATE
1159 AND tmp.OPERATION_CODE = 'UPDATE');
1160
1161 l_update_row_cnt := sql%rowcount;
1162 -- dbms_output.put_line('Rows updated '||l_insert_row_cnt);
1163 commit;
1164 END apply_to_summary_tab;
1165
1166 PROCEDURE cost_summary_tab IS
1167 /*===================================================================================+
1168 | Cost_summary_tab; |
1169 | This procedure to cost the summary table rows. |
1170 +===================================================================================*/
1171 BEGIN
1172 /* following insert statement moves all open periods data into work table for costing */
1173 INSERT INTO OPI_PMI_INV_DAILY_STAT_TEMP (CO_CODE
1174 ,ORGN_CODE
1175 ,WHSE_CODE
1176 ,LOCATION
1177 ,ITEM_ID
1178 ,LOT_ID
1179 ,TRX_DATE
1180 ,AVG_ONH_QTY
1181 ,BEG_ONH_QTY
1182 ,CREATION_DATE
1183 ,END_ONH_QTY
1184 ,FROM_ORG_QTY
1185 ,INV_ADJ_QTY
1186 ,LAST_UPDATE_DATE
1187 ,PO_DEL_QTY
1188 ,TOTAL_REC_QTY
1189 ,TOT_CUST_SHIP_QTY
1190 ,TOT_ISSUES_QTY
1191 ,TO_ORG_QTY
1192 ,WIP_COMP_QTY
1193 ,WIP_ISSUE_QTY
1194 ,PERIOD_FLAG
1195 ,AVG_ONH_VAL_B
1196 ,BEG_ONH_VAL_B
1197 ,END_ONH_VAL_B
1198 ,FROM_ORG_VAL_B
1199 ,INV_ADJ_VAL_B
1200 ,PO_DEL_VAL_B
1201 ,TOTAL_REC_VAL_B
1202 ,TOT_CUST_SHIP_VAL_B
1203 ,TOT_ISSUES_VAL_B
1204 ,TO_ORG_VAL_B
1205 ,WIP_COMP_VAL_B
1206 ,WIP_ISSUE_VAL_B
1207 ,PERIOD_STATUS
1208 ,DATA_PUSHED_IND)
1209
1210 (SELECT sm.CO_CODE
1211 ,sm.ORGN_CODE
1212 ,sm.WHSE_CODE
1213 ,sm.LOCATION
1214 ,sm.ITEM_ID
1215 ,sm.LOT_ID
1216 ,sm.TRX_DATE
1217 ,sm.AVG_ONH_QTY
1218 ,sm.BEG_ONH_QTY
1219 ,sm.CREATION_DATE
1223 ,sysdate
1220 ,sm.END_ONH_QTY
1221 ,sm.FROM_ORG_QTY
1222 ,sm.INV_ADJ_QTY
1224 ,sm.PO_DEL_QTY
1225 ,sm.TOTAL_REC_QTY
1226 ,sm.TOT_CUST_SHIP_QTY
1227 ,sm.TOT_ISSUES_QTY
1228 ,sm.TO_ORG_QTY
1229 ,sm.WIP_COMP_QTY
1230 ,sm.WIP_ISSUE_QTY
1231 ,sm.PERIOD_FLAG
1232 ,sm.AVG_ONH_QTY
1233 ,sm.BEG_ONH_QTY
1234 ,sm.END_ONH_QTY
1235 ,sm.FROM_ORG_QTY
1236 ,sm.INV_ADJ_QTY
1237 ,sm.PO_DEL_QTY
1238 ,sm.TOTAL_REC_QTY
1239 ,sm.TOT_CUST_SHIP_QTY
1240 ,sm.TOT_ISSUES_QTY
1241 ,sm.TO_ORG_QTY
1242 ,sm.WIP_COMP_QTY
1243 ,sm.WIP_ISSUE_QTY
1244 ,sm.PERIOD_STATUS
1245 ,sm.DATA_PUSHED_IND
1246 FROM OPI_PMI_INV_DAILY_STAT_SUM sm
1247 WHERE NVL(PERIOD_STATUS,0) <> 2 );
1248 /* Delete rows from summary table which are moved to work table for costing.
1249 later we can insert these rows from work table back to summary table since
1250 delete and insert of mass transactions are faster than update */
1251 delete OPI_PMI_INV_DAILY_STAT_SUM
1252 WHERE NVL(PERIOD_STATUS,0) <> 2 ;
1253
1254 /* Insert parameter to costing procedure in opi_pmi_cost_param_gtmp.
1255 Parameter are Organization code, warehouse code, item id, transaction date
1256 after insertion call costing procedure */
1257
1258 insert into opi_pmi_cost_param_gtmp (orgn_code,
1259 whse_code,
1260 item_id,
1261 trans_date)
1262 (SELECT distinct orgn_code,
1263 whse_code,
1264 item_id,
1265 trx_date
1266 FROM OPI_PMI_INV_DAILY_STAT_TEMP);
1267 opi_pmi_cost.get_cost;
1268
1269 /* Move data from temporary summary table to actual summary table and calculate the values
1270 using cost data inserted into opi_pmi_cost_result_gtmp table by costing procedure */
1271
1272 INSERT INTO OPI_PMI_INV_DAILY_STAT_SUM (CO_CODE
1273 ,ORGN_CODE
1274 ,WHSE_CODE
1275 ,LOCATION
1276 ,ITEM_ID
1277 ,LOT_ID
1278 ,TRX_DATE
1279 ,AVG_ONH_QTY
1280 ,BEG_ONH_QTY
1281 ,CREATION_DATE
1282 ,END_ONH_QTY
1283 ,FROM_ORG_QTY
1284 ,INV_ADJ_QTY
1285 ,LAST_UPDATE_DATE
1286 ,PO_DEL_QTY
1287 ,TOTAL_REC_QTY
1288 ,TOT_CUST_SHIP_QTY
1289 ,TOT_ISSUES_QTY
1290 ,TO_ORG_QTY
1291 ,WIP_COMP_QTY
1292 ,WIP_ISSUE_QTY
1293 ,PERIOD_FLAG
1294 ,AVG_ONH_VAL_B
1295 ,BEG_ONH_VAL_B
1296 ,END_ONH_VAL_B
1297 ,FROM_ORG_VAL_B
1298 ,INV_ADJ_VAL_B
1299 ,PO_DEL_VAL_B
1300 ,TOTAL_REC_VAL_B
1301 ,TOT_CUST_SHIP_VAL_B
1302 ,TOT_ISSUES_VAL_B
1303 ,TO_ORG_VAL_B
1304 ,WIP_COMP_VAL_B
1305 ,WIP_ISSUE_VAL_B
1306 ,PERIOD_STATUS
1307 ,DATA_PUSHED_IND)
1308
1309 (SELECT sm.CO_CODE
1310 ,sm.ORGN_CODE
1311 ,sm.WHSE_CODE
1315 ,sm.TRX_DATE
1312 ,sm.LOCATION
1313 ,sm.ITEM_ID
1314 ,sm.LOT_ID
1316 ,sm.AVG_ONH_QTY
1317 ,sm.BEG_ONH_QTY
1318 ,sm.CREATION_DATE
1319 ,sm.END_ONH_QTY
1320 ,sm.FROM_ORG_QTY
1321 ,sm.INV_ADJ_QTY
1322 ,sysdate
1323 ,sm.PO_DEL_QTY
1324 ,sm.TOTAL_REC_QTY
1325 ,sm.TOT_CUST_SHIP_QTY
1326 ,sm.TOT_ISSUES_QTY
1327 ,sm.TO_ORG_QTY
1328 ,sm.WIP_COMP_QTY
1329 ,sm.WIP_ISSUE_QTY
1330 ,sm.PERIOD_FLAG
1331 ,sm.AVG_ONH_QTY * rslt.total_cost
1332 ,sm.BEG_ONH_QTY * rslt.total_cost
1333 ,sm.END_ONH_QTY * rslt.total_cost
1334 ,sm.FROM_ORG_QTY * rslt.total_cost
1335 ,sm.INV_ADJ_QTY * rslt.total_cost
1336 ,sm.PO_DEL_QTY * rslt.total_cost
1337 ,sm.TOTAL_REC_QTY * rslt.total_cost
1338 ,sm.TOT_CUST_SHIP_QTY * rslt.total_cost
1339 ,sm.TOT_ISSUES_QTY * rslt.total_cost
1340 ,sm.TO_ORG_QTY * rslt.total_cost
1341 ,sm.WIP_COMP_QTY * rslt.total_cost
1342 ,sm.WIP_ISSUE_QTY * rslt.total_cost
1343 ,NVL(rslt.PERIOD_STATUS,0)
1344 ,NVL(sm.DATA_PUSHED_IND,0)
1345 FROM OPI_PMI_INV_DAILY_STAT_TEMP sm,
1346 opi_pmi_cost_result_gtmp rslt
1347 WHERE rslt.orgn_code = sm.orgn_code AND
1348 rslt.whse_code = sm.whse_code AND
1349 rslt.item_id = sm.item_id AND
1350 rslt.trans_date = sm.trx_date);
1351
1352 END cost_summary_tab;
1353
1354
1355
1356 PROCEDURE CLEANUP is
1357 /*===================================================================================+
1358 | This Procedure is to truncate all work tables used by the program. |
1359 | This call is issued at the beginning of the process once (since previous |
1360 | run may be abnormally terminated) and again at the end. |
1361 | In this procedure we fisrt get the table owner and then using table owner|
1362 | truncate the work table. This process is repeted for all work tables |
1363 +===================================================================================*/
1364
1365 l_stmt varchar2(2000);
1366 l_owner VARCHAR2(240);
1367 BEGIN
1368 select TABLE_OWNER INTO l_owner
1369 from user_synonyms
1370 where table_name= 'OPI_PMI_DAY_SUM_TEMP';
1371 IF l_owner IS NOT NULL THEN
1372 l_stmt := 'truncate table '||l_owner||'.opi_pmi_day_sum_temp ';
1373 BEGIN
1374 execute immediate l_stmt;
1375 EXCEPTION WHEN OTHERS THEN
1376 NULL;
1377 END;
1378 END IF;
1379 select TABLE_OWNER INTO l_owner
1380 from user_synonyms
1381 where table_name= 'OPI_PMI_TRANS_INC';
1382 IF l_owner IS NOT NULL THEN
1383 l_stmt := 'truncate table '||l_owner||'.opi_pmi_trans_inc ';
1384 BEGIN
1385 execute immediate l_stmt;
1386 EXCEPTION WHEN OTHERS THEN
1387 NULL;
1388 END;
1389 END IF;
1390 select TABLE_OWNER INTO l_owner
1391 from user_synonyms
1392 where table_name= 'OPI_PMI_IDS_IDNT';
1393 IF l_owner IS NOT NULL THEN
1394 l_stmt := 'truncate table '||l_owner||'.opi_pmi_ids_idnt ';
1395 BEGIN
1396 execute immediate l_stmt;
1397 EXCEPTION WHEN OTHERS THEN
1398 NULL;
1399 END;
1400 END IF;
1401 select TABLE_OWNER INTO l_owner
1402 from user_synonyms
1403 where table_name= 'OPI_PMI_IDS_TEMP';
1404 IF l_owner IS NOT NULL THEN
1405 l_stmt := 'truncate table '||l_owner||'.opi_pmi_ids_temp ';
1406 BEGIN
1407 execute immediate l_stmt;
1408 EXCEPTION WHEN OTHERS THEN
1409 NULL;
1410 END;
1411 END IF;
1412 select TABLE_OWNER INTO l_owner
1413 from user_synonyms
1414 where table_name= 'OPI_PMI_INV_DAILY_STAT_TEMP';
1415 IF l_owner IS NOT NULL THEN
1416 l_stmt := 'truncate table '||l_owner||'.OPI_PMI_INV_DAILY_STAT_TEMP';
1417 BEGIN
1418 execute immediate l_stmt;
1419 EXCEPTION WHEN OTHERS THEN
1420 NULL;
1421 END;
1422 END IF;
1423
1424 END cleanup;
1425
1426 PROCEDURE start_summary(errbuf OUT NOCOPY varchar2,retcode OUT NOCOPY VARCHAR2) IS
1427 /*===================================================================================*
1428 | start_summary(errbuf OUT varchar2,retcode OUT VARCHAR2) |
1429 | This procedure is a driver procedure for the Net Change summary |
1430 | process. It checks summary log table empty or not. If Summary log Table |
1431 | is empty then it errors out with summary table is baseline is not set. |
1432 *===================================================================================*/
1433 l_summary_start_date DATE;
1434 l_last_run_Date date;
1435 CURSOR cur_last_run IS
1436 SELECT LAST_RUN_DATE
1437 FROM OPI_PMI_IDS_SUM_LOG
1438 ORDER BY LAST_RUN_DATE DESC;
1439
1440 BEGIN
1441 OPEN cur_last_run;
1442 FETCH cur_last_run INTO l_last_run_date;
1443 CLOSE cur_last_run;
1444 IF l_last_run_date is not null THEN
1445
1446 -- Cleanup temporary tables
1447
1448 cleanup;
1449
1450 -- Check period Balance Records
1451
1452 post_perd_bal_recs(l_last_run_date);
1453
1454 -- Clock Summary Start Time
1455 l_summary_start_date := sysdate;
1456
1457 -- Get Net change from transaction tables.
1458
1459 populate_net_change(l_last_run_date);
1460
1461 -- Summarize net change records at day level and transform rows from horizantal (row) structutre
1462 -- to vertical (column) structure
1463
1464 populate_day_sum_temp;
1465
1466 -- Identify summary records need to be changed
1467
1468 identify_summary_recs_to_chng;
1469
1470 -- summarize data in temp summary table
1471
1472 summarize_temp_summary;
1473
1474 -- apply changes to actual summay table
1475
1476 apply_to_summary_tab;
1477
1478
1479 -- Cost Changed Rows
1480
1481 cost_summary_tab;
1482
1483 -- log summary completed
1484 UPDATE OPI_PMI_IDS_SUM_LOG
1485 SET LAST_RUN_DATE = l_summary_start_date;
1486 commit;
1487 -- cleanup temporary tables
1488
1489 cleanup;
1490 ELSE
1491 edw_log.put_line (FND_MESSAGE.get_string('OPI','OPI_PMI_SUMMARY_ERROR'));
1492 retcode:= '2';
1493 END IF;
1494 END start_summary;
1495
1496 END OPI_PMI_IDS_SUMMARY;