[Home] [Help]
PACKAGE BODY: APPS.PMI_BUILD_ONHANDSALE_SUM
Source
1 PACKAGE BODY PMI_BUILD_ONHANDSALE_SUM AS
2 -- $Header: PMIOHDSB.pls 115.35 2002/12/05 17:06:21 skarimis ship $
3
4 PROCEDURE populate_summary(p_last_run_date date,p_log_end_date date) IS
5 -- Cursor Declarations
6 CURSOR check_cldr_for_perd_bal IS
7 select gpm.co_code co_code,
8 c.period_start_date period_start_date,
9 c.period_end_date period_end_date,
10 c.period_num period_num,
11 c.quarter_num quarter_num,
12 c.period_year period_year,
13 c.period_name period_name,
14 c.quarter_name quarter_name,
15 c.year_name year_name ,
16 c.period_set_name period_set_name,
17 d.last_start_date last_start_date,
18 d.last_end_date last_end_date
19 FROM
20 ( select a.set_of_books_name set_of_books_name ,
21 a.period_num period_num,
22 a.period_start_date cur_start_date,
23 a.period_end_Date cur_end_Date,
24 b.period_start_date last_start_date,
25 b.period_end_Date last_end_date
26 from PMI_GL_TIME_V a,PMI_GL_TIME_V b
27 where a.set_of_books_name = b.set_of_books_name and
28 sysdate between a.period_start_date and a.period_end_Date AND
29 p_last_run_date between b.period_start_date and b.period_end_Date
30 ) d,
31 PMI_GL_TIME_V c,
32 GL_PLCY_MST gpm
33 where c.set_of_books_name = d.set_of_books_name
34 and gpm.set_of_books_name = c.set_of_books_name
35 and c.period_start_date >= d.last_start_date
36 and c.period_start_date <= sysdate
37 order by gpm.co_code,c.period_start_date desc;
38 -- Row Type Variables
39 cldr_rec check_cldr_for_perd_bal%ROWTYPE;
40 -- Local Variables
41 l_prev_co_code sy_orgn_mst.co_code%TYPE;
42 BEGIN
43 OPEN check_cldr_for_perd_bal;
44 LOOP
45 FETCH check_cldr_for_perd_bal INTO cldr_rec;
46 EXIT WHEN check_cldr_for_perd_bal%NOTFOUND;
47 /* Insert new period marker rows using ic_loct_inv for the current period and
48 create previous period rows using current period rows
49 E.g.
50 we need to populate data for period 1 to period 3.
51 current period : 3
52 populate period 3 rows using IC_LOCT_INV and Transaction tables (IC_TRAN_PND and IC_TRAN_CMP) this is
53 required because user might have run the purge 0 -zero balance rows
54 For period 2 use period 3 rows and Trnasaction tables
55 For period 1 use period 2 rows and Trnasaction tables
56 */
57 DELETE PMI_ONHAND_SALE_SUM_TEMP;
58
59 IF l_prev_co_code IS NULL OR l_prev_co_code <> cldr_rec.co_code THEN
60 INSERT INTO PMI_ONHAND_SALE_SUM_TEMP(FISCAL_YEAR
61 ,CO_CODE
62 ,ORGN_CODE
63 ,WHSE_CODE
64 ,PERIOD_ID
65 ,QUARTER
66 ,PERIOD
67 ,CONVERTIBLE_UOM
68 ,ITEM_ID
69 ,ITEM_NO
70 ,ITEM_UM
71 ,WHSE_ONHAND_QTY
72 ,WHSE_ONHAND_CONV
73 ,WHSE_ONHAND_VALUE
74 ,WHSE_SALE_QTY
75 ,WHSE_SALE_CONV
76 ,WHSE_SALE_VALUE
77 ,WHSE_RTRN_QTY
78 ,WHSE_RTRN_CONV
79 ,WHSE_RTRN_VALUE
80 ,LOG_END_DATE
81 ,PERIOD_IND
82 ,WHSE_NAME
83 ,ORGN_NAME
84 ,PERIOD_NAME
85 ,QUARTER_NAME
86 ,PERIOD_SET_NAME
87 ,GL_PERIOD_YEAR
88 ,GL_FISCAL_YEAR_NAME
89 ,GL_PERIOD_END_DATE
90 ,GL_PERIOD_START_DATE
91 ,MISS_CONV_FACT_CNT)
92 (SELECT cldr_rec.period_year FISCAL_YEAR
93 ,cldr_rec.co_code CO_CODE
94 ,org.ORGN_CODE
95 ,whs.WHSE_CODE
96 ,cldr_rec.period_num PERIOD_ID
97 ,cldr_rec.quarter_num QUARTER
98 ,cldr_rec.period_num PERIOD
99 ,PV_conv_uom CONVERTIBLE_UOM
100 ,iim.ITEM_ID
101 ,iim.ITEM_NO
102 ,iim.ITEM_UM
103 ,loct.LOCT_ONHAND WHSE_ONHAND_QTY
104 ,0 WHSE_ONHAND_CONV
105 ,0 WHSE_ONHAND_VALUE
106 ,0 WHSE_SALE_QTY
107 ,0 WHSE_SALE_CONV
108 ,0 WHSE_SALE_VALUE
109 ,0 WHSE_RTRN_QTY
110 ,0 WHSE_RTRN_CONV
111 ,0 WHSE_RTRN_VALUE
112 ,p_log_end_date LOG_END_DATE
113 ,0 PERIOD_IND
114 ,whs.WHSE_NAME
115 ,org.ORGN_NAME
116 ,cldr_rec.period_name PERIOD_NAME
117 ,cldr_rec.quarter_name QUARTER_NAME
118 ,cldr_rec.period_set_name PERIOD_SET_NAME
119 ,cldr_rec.period_year GL_PERIOD_YEAR
120 ,cldr_rec.year_name GL_FISCAL_YEAR_NAME
121 ,cldr_rec.period_end_date GL_PERIOD_END_DATE
122 ,cldr_rec.period_start_date GL_PERIOD_START_DATE
123 ,0 MISS_CONV_FACT_CNT
124 FROM (SELECT whse_code,item_id,sum(LOCT_ONHAND) loct_onhand
125 from IC_LOCT_INV
126 group by whse_code,item_id) loct,
127 IC_WHSE_MST whs,
128 SY_ORGN_MST org,
129 IC_ITEM_MST iim
130 WHERE org.co_code = cldr_rec.co_code
131 AND loct.item_id = iim.item_id
132 AND loct.whse_code = whs.whse_code
133 AND whs.orgn_code = org.orgn_code );
134 l_prev_co_code := cldr_rec.co_code;
135 ELSE
136 INSERT INTO PMI_ONHAND_SALE_SUM_TEMP(FISCAL_YEAR
137 ,CO_CODE
138 ,ORGN_CODE
139 ,WHSE_CODE
140 ,PERIOD_ID
141 ,QUARTER
142 ,PERIOD
143 ,CONVERTIBLE_UOM
144 ,ITEM_ID
145 ,ITEM_NO
146 ,ITEM_UM
147 ,WHSE_ONHAND_QTY
148 ,WHSE_ONHAND_CONV
149 ,WHSE_ONHAND_VALUE
150 ,WHSE_SALE_QTY
151 ,WHSE_SALE_CONV
152 ,WHSE_SALE_VALUE
153 ,WHSE_RTRN_QTY
154 ,WHSE_RTRN_CONV
155 ,WHSE_RTRN_VALUE
156 ,LOG_END_DATE
157 ,PERIOD_IND
158 ,WHSE_NAME
159 ,ORGN_NAME
160 ,PERIOD_NAME
161 ,QUARTER_NAME
162 ,PERIOD_SET_NAME
163 ,GL_PERIOD_YEAR
164 ,GL_FISCAL_YEAR_NAME
165 ,GL_PERIOD_END_DATE
166 ,GL_PERIOD_START_DATE
167 ,MISS_CONV_FACT_CNT)
168 SELECT cldr_rec.period_year FISCAL_YEAR
169 ,cldr_rec.co_code CO_CODE
170 ,next_prd.ORGN_CODE
171 ,next_prd.WHSE_CODE
172 ,cldr_rec.period_num PERIOD_ID
173 ,cldr_rec.quarter_num QUARTER
174 ,cldr_rec.period_num PERIOD
175 ,next_prd.CONVERTIBLE_UOM
176 ,next_prd.ITEM_ID
177 ,next_prd.ITEM_NO
178 ,next_prd.ITEM_UM
179 ,nvl(next_prd.WHSE_ONHAND_QTY,0) - nvl(prd_tr_sum.perd_trans_qty,0)
180 ,0 WHSE_ONHAND_CONV
181 ,0 WHSE_ONHAND_VALUE
182 ,0 WHSE_SALE_QTY
183 ,0 WHSE_SALE_CONV
184 ,0 WHSE_SALE_VALUE
185 ,0 WHSE_RTRN_QTY
186 ,0 WHSE_RTRN_CONV
187 ,0 WHSE_RTRN_VALUE
188 ,p_log_end_date LOG_END_DATE
189 ,0 PERIOD_IND
190 ,next_prd.WHSE_NAME
191 ,next_prd.ORGN_NAME
192 ,cldr_rec.period_name PERIOD_NAME
193 ,cldr_rec.quarter_name QUARTER_NAME
194 ,cldr_rec.period_set_name PERIOD_SET_NAME
195 ,cldr_rec.period_year GL_PERIOD_YEAR
196 ,cldr_rec.year_name GL_FISCAL_YEAR_NAME
197 ,cldr_rec.period_end_date GL_PERIOD_END_DATE
198 ,cldr_rec.period_start_date GL_PERIOD_START_DATE
199 ,0 MISS_CONV_FACT_CNT
200 FROM PMI_ONHAND_SALE_SUM_CUR_TEMP next_prd,
201 PMI_PERD_TRANS_SUM_TEMP prd_tr_sum
202 WHERE next_prd.whse_code = prd_tr_sum.whse_code (+)
203 AND next_prd.item_id = prd_tr_sum.item_id (+);
204 END IF;
205
206 /* Summarize Transactions at period level this table is used for
207 populating sales data for current period and to calculate period end balance
208 for the previous period. */
209
210 delete PMI_PERD_TRANS_SUM_TEMP ;
211
212 INSERT INTO PMI_PERD_TRANS_SUM_TEMP (WHSE_CODE,
213 ITEM_ID,
214 PERD_TRANS_QTY,
215 PERD_SALES_QTY)
216 (
217 SELECT whse_code,item_id,sum(trans_qty),sum(sales_qty)
218 FROM (SELECT WHSE_CODE,ITEM_ID,TRANS_QTY,
219 DECODE(DOC_TYPE,'OPSO',trans_qty,'OMSO',trans_qty,0) sales_qty
220 FROM IC_TRAN_PND
221 WHERE trunc(trans_date) between cldr_rec.period_start_date AND
222 cldr_rec.period_end_date
223 AND doc_type NOT IN ('STSI', 'GRDI','STSR', 'GRDR')
224 AND completed_ind = 1 and delete_mark = 0
225 UNION ALL
226 SELECT WHSE_CODE,ITEM_ID,TRANS_QTY,
227 DECODE(DOC_TYPE,'OPSO',trans_qty,'OMSO',trans_qty,0) sales_qty
228 FROM IC_TRAN_CMP
229 WHERE trunc(trans_date) between cldr_rec.period_start_date AND
230 cldr_rec.period_end_date
231 AND doc_type NOT IN ('STSI', 'GRDI','STSR', 'GRDR'))
232 group by whse_code,item_id);
233
234 /*
235 Following statement inserts 0-Zero balance rows if purge 0-zero
236 balance rows routine purges 0 balance rows from ic_LOCT_INV */
237
238
239 INSERT INTO PMI_ONHAND_SALE_SUM_TEMP(FISCAL_YEAR
240 ,CO_CODE
241 ,ORGN_CODE
242 ,WHSE_CODE
243 ,PERIOD_ID
244 ,QUARTER
245 ,PERIOD
246 ,CONVERTIBLE_UOM
247 ,ITEM_ID
248 ,ITEM_NO
249 ,ITEM_UM
250 ,WHSE_ONHAND_QTY
251 ,WHSE_ONHAND_CONV
252 ,WHSE_ONHAND_VALUE
253 ,WHSE_SALE_QTY
254 ,WHSE_SALE_CONV
255 ,WHSE_SALE_VALUE
256 ,WHSE_RTRN_QTY
257 ,WHSE_RTRN_CONV
258 ,WHSE_RTRN_VALUE
259 ,LOG_END_DATE
260 ,PERIOD_IND
261 ,WHSE_NAME
262 ,ORGN_NAME
263 ,PERIOD_NAME
264 ,QUARTER_NAME
265 ,PERIOD_SET_NAME
266 ,GL_PERIOD_YEAR
267 ,GL_FISCAL_YEAR_NAME
268 ,GL_PERIOD_END_DATE
269 ,GL_PERIOD_START_DATE
273 ,org.ORGN_CODE
270 ,MISS_CONV_FACT_CNT)
271 (SELECT cldr_rec.period_year FISCAL_YEAR
272 ,cldr_rec.co_code CO_CODE
274 ,whs.WHSE_CODE
275 ,cldr_rec.period_num PERIOD_ID
276 ,cldr_rec.quarter_num QUARTER
277 ,cldr_rec.period_num PERIOD
278 ,PV_conv_uom CONVERTIBLE_UOM
279 ,iim.ITEM_ID
280 ,iim.ITEM_NO
281 ,iim.ITEM_UM
282 ,0 WHSE_ONHAND_QTY
283 ,0 WHSE_ONHAND_CONV
284 ,0 WHSE_ONHAND_VALUE
285 ,0 WHSE_SALE_QTY
286 ,0 WHSE_SALE_CONV
287 ,0 WHSE_SALE_VALUE
288 ,0 WHSE_RTRN_QTY
289 ,0 WHSE_RTRN_CONV
290 ,0 WHSE_RTRN_VALUE
291 ,null LOG_END_DATE
292 ,0 PERIOD_IND
293 ,whs.WHSE_NAME
294 ,org.ORGN_NAME
295 ,cldr_rec.period_name PERIOD_NAME
296 ,cldr_rec.quarter_name QUARTER_NAME
297 ,cldr_rec.period_set_name PERIOD_SET_NAME
298 ,cldr_rec.period_year GL_PERIOD_YEAR
299 ,cldr_rec.year_name GL_FISCAL_YEAR_NAME
300 ,cldr_rec.period_end_date GL_PERIOD_END_DATE
301 ,cldr_rec.period_start_date GL_PERIOD_START_DATE
302 ,0 MISS_CONV_FACT_CNT
303 FROM (SELECT whse_code,item_id
304 FROM PMI_PERD_TRANS_SUM_TEMP
305 MINUS
306 SELECT WHSE_CODE,ITEM_ID
307 FROM PMI_ONHAND_SALE_SUM_TEMP) prd_sum,
308 IC_WHSE_MST whs,
309 SY_ORGN_MST org,
310 IC_ITEM_MST iim
311 WHERE co_code = cldr_rec.co_code
312 AND prd_sum.item_id = iim.item_id
313 AND prd_sum.whse_code = whs.whse_code
314 AND whs.orgn_code = org.orgn_code );
315
316 delete PMI_ONHAND_SALE_SUM_CUR_TEMP ;
317
318 INSERT INTO PMI_ONHAND_SALE_SUM_CUR_TEMP(FISCAL_YEAR
319 ,CO_CODE
320 ,ORGN_CODE
321 ,WHSE_CODE
322 ,PERIOD_ID
323 ,QUARTER
324 ,PERIOD
325 ,CONVERTIBLE_UOM
326 ,ITEM_ID
327 ,ITEM_NO
328 ,ITEM_UM
329 ,WHSE_ONHAND_QTY
330 ,WHSE_ONHAND_CONV
331 ,WHSE_ONHAND_VALUE
332 ,WHSE_SALE_QTY
333 ,WHSE_SALE_CONV
334 ,WHSE_SALE_VALUE
335 ,WHSE_RTRN_QTY
336 ,WHSE_RTRN_CONV
337 ,WHSE_RTRN_VALUE
338 ,LOG_END_DATE
339 ,PERIOD_IND
340 ,WHSE_NAME
341 ,ORGN_NAME
342 ,PERIOD_NAME
343 ,QUARTER_NAME
344 ,PERIOD_SET_NAME
345 ,GL_PERIOD_YEAR
346 ,GL_FISCAL_YEAR_NAME
347 ,GL_PERIOD_END_DATE
348 ,GL_PERIOD_START_DATE
349 ,MISS_CONV_FACT_CNT)
350 SELECT psum_tmp.FISCAL_YEAR
351 ,psum_tmp.CO_CODE
352 ,psum_tmp.ORGN_CODE
353 ,psum_tmp.WHSE_CODE
354 ,psum_tmp.PERIOD_ID
355 ,psum_tmp.QUARTER
356 ,psum_tmp.PERIOD
357 ,psum_tmp.CONVERTIBLE_UOM
358 ,psum_tmp.ITEM_ID
359 ,psum_tmp.ITEM_NO
360 ,psum_tmp.ITEM_UM
361 ,psum_tmp.WHSE_ONHAND_QTY
362 ,psum_tmp.WHSE_ONHAND_CONV
363 ,psum_tmp.WHSE_ONHAND_VALUE
367 ,psum_tmp.WHSE_RTRN_QTY
364 ,prd_tr_sum.PERD_SALES_QTY WHSE_SALE_QTY
365 ,psum_tmp.WHSE_SALE_CONV
366 ,psum_tmp.WHSE_SALE_VALUE
368 ,psum_tmp.WHSE_RTRN_CONV
369 ,psum_tmp.WHSE_RTRN_VALUE
370 ,psum_tmp.LOG_END_DATE
371 ,psum_tmp.PERIOD_IND
372 ,psum_tmp.WHSE_NAME
373 ,psum_tmp.ORGN_NAME
374 ,psum_tmp.PERIOD_NAME
375 ,psum_tmp.QUARTER_NAME
376 ,psum_tmp.PERIOD_SET_NAME
377 ,psum_tmp.GL_PERIOD_YEAR
378 ,psum_tmp.GL_FISCAL_YEAR_NAME
379 ,psum_tmp.GL_PERIOD_END_DATE
380 ,psum_tmp.GL_PERIOD_START_DATE
381 ,psum_tmp.MISS_CONV_FACT_CNT
382 FROM PMI_ONHAND_SALE_SUM_TEMP psum_tmp,
383 PMI_PERD_TRANS_SUM_TEMP prd_tr_sum
384 WHERE psum_tmp.whse_code = prd_tr_sum.whse_code (+)
385 AND psum_tmp.item_id = prd_tr_sum.item_id (+);
386
387
388 /* Delete data from Summary table for currently processed data
389 we will replace this rows using next insert statement */
390
391 DELETE PMI_ONHAND_SALE_SUM
392 WHERE co_Code = cldr_rec.co_code
393 AND period_set_name = cldr_rec.period_Set_name
394 AND period_name = cldr_rec.period_name;
395
396
397 INSERT INTO PMI_ONHAND_SALE_SUM(FISCAL_YEAR
398 ,CO_CODE
399 ,ORGN_CODE
400 ,WHSE_CODE
401 ,PERIOD_ID
402 ,QUARTER
403 ,PERIOD
404 ,CONVERTIBLE_UOM
405 ,ITEM_ID
406 ,ITEM_NO
407 ,ITEM_UM
408 ,WHSE_ONHAND_QTY
409 ,WHSE_ONHAND_CONV
410 ,WHSE_ONHAND_VALUE
411 ,WHSE_SALE_QTY
412 ,WHSE_SALE_CONV
413 ,WHSE_SALE_VALUE
414 ,WHSE_RTRN_QTY
415 ,WHSE_RTRN_CONV
416 ,WHSE_RTRN_VALUE
417 ,LOG_END_DATE
418 ,PERIOD_IND
419 ,WHSE_NAME
420 ,ORGN_NAME
421 ,PERIOD_NAME
422 ,QUARTER_NAME
423 ,PERIOD_SET_NAME
424 ,GL_PERIOD_YEAR
425 ,GL_FISCAL_YEAR_NAME
426 ,GL_PERIOD_END_DATE
427 ,GL_PERIOD_START_DATE
428 ,MISS_CONV_FACT_CNT)
429 SELECT FISCAL_YEAR
430 ,CO_CODE
431 ,ORGN_CODE
432 ,WHSE_CODE
433 ,PERIOD_ID
434 ,QUARTER
435 ,PERIOD
436 ,CONVERTIBLE_UOM
437 ,ITEM_ID
438 ,ITEM_NO
439 ,ITEM_UM
440 ,WHSE_ONHAND_QTY
441 ,decode(PV_conv_uom,item_um,WHSE_ONHAND_QTY,
442 gmicuom.i2uom_cv(item_id,0,item_um,WHSE_ONHAND_QTY,PV_conv_uom)) WHSE_ONHAND_CONV
443 ,WHSE_ONHAND_QTY *
444 pmi_common_pkg.PMICO_GET_COST(item_id,whse_code,null,GL_PERIOD_START_DATE) WHSE_ONHAND_VALUE
445 ,WHSE_SALE_QTY
446 ,decode(PV_conv_uom,item_um,WHSE_SALE_QTY,
447 gmicuom.i2uom_cv(item_id,0,item_um,WHSE_SALE_QTY,PV_conv_uom)) WHSE_SALE_CONV
448 ,WHSE_SALE_QTY *
449 pmi_common_pkg.PMICO_GET_COST(item_id,whse_code,null,GL_PERIOD_START_DATE) WHSE_SALE_VALUE
450 ,WHSE_RTRN_QTY
451 ,WHSE_RTRN_CONV
452 ,WHSE_RTRN_VALUE
453 ,LOG_END_DATE
454 ,PERIOD_IND
455 ,WHSE_NAME
456 ,ORGN_NAME
457 ,PERIOD_NAME
458 ,QUARTER_NAME
459 ,PERIOD_SET_NAME
460 ,GL_PERIOD_YEAR
461 ,GL_FISCAL_YEAR_NAME
462 ,GL_PERIOD_END_DATE
463 ,GL_PERIOD_START_DATE
467 END LOOP;
464 ,MISS_CONV_FACT_CNT
465 FROM PMI_ONHAND_SALE_SUM_CUR_TEMP;
466 commit;
468 CLOSE check_cldr_for_perd_bal;
469 END populate_summary;
470
471 PROCEDURE BUILD_SUMMARY(errbuf OUT NOCOPY varchar2,retcode OUT NOCOPY VARCHAR2) IS
472 -- Local Variables
473 e_buff VARCHAR2(2000);
474 l_last_run_date DATE;
475 l_log_end_date DATE := trunc(SYSDATE);
476 l_last_trns_eff_date DATE;
477 l_start_population_date DATE;
478 buff32k VARCHAR2(32767);
479 l_mesg VARCHAR2(2000);
480 l_conv_uom pmi_onhand_sale_sum.convertible_uom%TYPE;
481 l_conv_uom1 pmi_onhand_sale_sum.convertible_uom%TYPE;
482 l_table_owner VARCHAR2(40);
483 -- Cursors and cursor row type variables
484 CURSOR cur_bisopm_onhand IS
485 SELECT LAST_RUN_DATE,ATTR1
486 FROM PMI_SUMMARY_LOG_TABLE
487 WHERE summary_table = 'PMI_ONHAND_SALE_SUM';
488 CURSOR cur_last_trans_eff_date IS
489 SELECT min(trans_date)
490 from (Select (min(trans_date)) trans_date
491 from IC_TRAN_PND
492 WHERE (l_last_run_date IS NULL OR
493 (l_last_run_date IS NOT NULL AND last_update_date >= l_last_run_date))
494 UNION ALL
495 (Select (min(trans_date)) trans_date
496 from IC_TRAN_CMP
497 WHERE (l_last_run_date IS NULL OR
498 (l_last_run_date IS NOT NULL AND last_update_date >= l_last_run_date))));
499 rows_exists number := null;
500 BEGIN
501 /* Checking Existancy of data in BIS Summary Table and get the populated UOM and last rundate */
502 OPEN cur_bisopm_onhand;
503 FETCH cur_bisopm_onhand INTO l_last_run_date,l_conv_uom;
504 IF cur_bisopm_onhand%NOTFOUND THEN
505 CLOSE cur_bisopm_onhand;
506 BEGIN
507 SELECT 1 into rows_exists
508 FROM PMI_ONHAND_SALE_SUM
509 WHERE ROWNUM = 1;
510 errbuf := FND_MESSAGE.get_string('PMI','PMI_SUMM_POPULATION_ERR');
511 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf );
512 retcode:= '2';
513 APP_EXCEPTION.Raise_exception;
514 EXCEPTION WHEN NO_DATA_FOUND THEN
515 NULL;
516 END;
517 ELSE
518 CLOSE cur_bisopm_onhand;
519 END IF;
520
521 /* Check for Conversion UOM is defined or not */
522 IF fnd_profile.defined('PMI$CONV_UOM') THEN
523 l_conv_uom1 := fnd_profile.value('PMI$CONV_UOM');
524 IF l_conv_uom1 IS NULL THEN
525 errbuf := FND_MESSAGE.get_string('PMI','PMI_SET_CONV_UOM');
526 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf );
527 retcode:= '2';
528 APP_EXCEPTION.Raise_exception;
529 END IF;
530 PV_conv_uom := l_conv_uom1;
531 IF ((l_last_run_date IS NOT NULL) AND
532 (l_conv_uom1 <> l_conv_uom)) THEN
533 /* Delete data from summary table. now we need to populate using new conversion UOM */
534 buff32k := FND_MESSAGE.get_number('PMI','PMI_CONV_UOM_VAL_CHG') ||'-'||
535 FND_MESSAGE.get_string('PMI','PMI_CONV_UOM_VAL_CHG');
536 FND_FILE.PUT_LINE(FND_FILE.LOG, buff32k );
537 DELETE pmi_onhand_sale_sum;
538 COMMIT;
539 l_last_run_date := NULL;
540 END IF;
541 OPEN cur_last_trans_eff_date;
542 FETCH cur_last_trans_eff_date INTO l_last_trns_eff_date;
543 CLOSE cur_last_trans_eff_date;
544 l_start_population_date := least(nvl(trunc(l_last_run_date),trunc(SYSDATE)),trunc(l_last_trns_eff_date));
545 populate_summary(l_start_population_date,l_log_end_date);
546 BEGIN
547 UPDATE PMI_SUMMARY_LOG_TABLE
548 SET LAST_RUN_DATE = l_log_end_date,
549 ATTR1 = PV_conv_uom
550 WHERE summary_table = 'PMI_ONHAND_SALE_SUM';
551 IF SQL%ROWCOUNT = 0 THEN
552 INSERT INTO PMI_SUMMARY_LOG_TABLE (SUMMARY_TABLE,LAST_RUN_DATE,ATTR1,ATTR2,ATTR3,ATTR4,ATTR5,ATTR6)
553 VALUES ('PMI_ONHAND_SALE_SUM',l_log_end_date,PV_conv_uom,null,null,null,null,null);
554 END IF;
555 END;
556 SELECT TABLE_OWNER INTO l_table_owner
557 FROM USER_SYNONYMS
558 WHERE SYNONYM_NAME = 'PMI_ONHAND_SALE_SUM';
559 FND_STATS.GATHER_TABLE_STATS(l_table_owner, 'PMI_ONHAND_SALE_SUM');
560 ELSE
561 buff32k := FND_MESSAGE.get_number('PMI','PMI_CONV_UOM_PROF_MISS') ||'-'||
562 FND_MESSAGE.get_string('PMI','PMI_CONV_UOM_PROF_MISS');
563 FND_FILE.PUT_LINE(FND_FILE.LOG, buff32k );
564 retcode:= '2';
565 APP_EXCEPTION.Raise_exception;
566 END IF;
567 EXCEPTION
568 WHEN FND_FILE.UTL_FILE_ERROR then
569 errbuf := substr(fnd_message.get, 1, 254);
570 retcode := 2;
571 END BUILD_SUMMARY;
572 END PMI_BUILD_ONHANDSALE_SUM;