[Home] [Help]
PACKAGE BODY: APPS.PMI_BUILD_PROD_SUM
Source
1 PACKAGE BODY PMI_BUILD_PROD_SUM AS
2 /* $Header: PMIPRODB.pls 120.0 2005/05/24 16:57:25 appldev noship $ */
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 BEGIN
41 OPEN check_cldr_for_perd_bal;
42 LOOP
43 FETCH check_cldr_for_perd_bal INTO cldr_rec;
44 EXIT WHEN check_cldr_for_perd_bal%NOTFOUND;
45 /* Delete data for the current processing period from summary table and insert summary data
46 into summary table using transaction tables.
47 */
48 DELETE PMI_PROD_SUM
49 WHERE co_Code = cldr_rec.co_code
50 AND period_set_name = cldr_rec.period_Set_name
51 AND period_name = cldr_rec.period_name;
52
53
54 INSERT INTO PMI_PROD_SUM(FISCAL_YEAR
55 ,CO_CODE
56 ,ORGN_CODE
57 ,WHSE_CODE
58 ,PERIOD_ID
59 ,QUARTER
60 ,PERIOD
61 ,CONVERTIBLE_UOM
62 ,ITEM_ID
63 ,ITEM_NO
64 ,ITEM_UM
65 ,WHSE_USAGE_QTY
66 ,WHSE_USAGE_CONV
67 ,WHSE_USAGE_VALUE
68 ,WHSE_YIELD_QTY
69 ,WHSE_YIELD_CONV
70 ,WHSE_YIELD_VALUE
71 ,LOG_END_DATE
72 ,PERIOD_IND
73 ,WHSE_NAME
74 ,ORGN_NAME
75 ,PERIOD_NAME
76 ,QUARTER_NAME
77 ,PERIOD_SET_NAME
78 ,GL_PERIOD_YEAR
79 ,GL_FISCAL_YEAR_NAME
80 ,GL_PERIOD_START_DATE
81 ,GL_PERIOD_END_DATE
82 ,MISS_CONV_FACT_CNT)
83 (SELECT cldr_rec.period_year FISCAL_YEAR
84 ,cldr_rec.co_code CO_CODE
85 ,org.ORGN_CODE
86 ,whs.WHSE_CODE
87 ,cldr_rec.period_num PERIOD_ID
88 ,cldr_rec.quarter_num QUARTER
89 ,cldr_rec.period_num PERIOD
90 ,PV_conv_uom CONVERTIBLE_UOM
91 ,iim.ITEM_ID
92 ,iim.ITEM_NO
93 ,iim.ITEM_UM
94 ,prod_tr.usage_qty WHSE_USAGE_QTY
95 ,decode(PV_conv_uom,iim.item_um,prod_tr.usage_qty ,
96 gmicuom.i2uom_cv(iim.item_id,0,iim.item_um,prod_tr.usage_qty ,PV_conv_uom)) WHSE_USAGE_CONV
97 ,prod_tr.usage_qty *
98 pmi_common_pkg.PMICO_GET_COST(iim.item_id,whs.whse_code,null,cldr_rec.period_start_date) WHSE_USAGE_VALUE
99 ,prod_tr.yield_qty WHSE_YIELD_QTY
100 ,decode(PV_conv_uom,iim.item_um,prod_tr.yield_qty ,
101 gmicuom.i2uom_cv(iim.item_id,0,iim.item_um,prod_tr.yield_qty ,PV_conv_uom)) WHSE_YIELD_CONV
102 ,prod_tr.yield_qty *
103 pmi_common_pkg.PMICO_GET_COST(iim.item_id,whs.whse_code,null,cldr_rec.period_start_date) WHSE_YIELD_VALUE
104 ,p_log_end_date LOG_END_DATE
105 ,0 PERIOD_IND
106 ,whs.WHSE_NAME
107 ,org.ORGN_NAME
108 ,cldr_rec.period_name PERIOD_NAME
109 ,cldr_rec.quarter_name QUARTER_NAME
110 ,cldr_rec.period_set_name PERIOD_SET_NAME
111 ,cldr_rec.period_year GL_PERIOD_YEAR
112 ,cldr_rec.year_name GL_FISCAL_YEAR_NAME
113 ,cldr_rec.period_start_date GL_PERIOD_START_DATE
114 ,cldr_rec.period_end_date GL_PERIOD_END_DATE
115 ,0 MISS_CONV_FACT_CNT
116 FROM (SELECT orgn_code,whse_code,item_id,sum(yield_qty) yield_qty,sum(usage_qty) usage_qty
117 FROM (SELECT ORGN_CODE,WHSE_CODE,ITEM_ID,decode(line_type,1,trans_qty,0) yield_qty,
118 DECODE(line_type,-1,trans_qty,0) usage_qty
119 FROM IC_TRAN_PND
120 WHERE trunc(trans_date) between cldr_rec.period_start_date AND
121 cldr_rec.period_end_date
122 AND doc_type ='PROD'
123 AND completed_ind = 1 and delete_mark = 0
124 UNION ALL
125 SELECT ORGN_CODE,WHSE_CODE,ITEM_ID,decode(line_type,1,trans_qty,0) yield_qty,
126 DECODE(line_type,-1,trans_qty,0) usage_qty FROM IC_TRAN_CMP
127 WHERE trunc(trans_date) between cldr_rec.period_start_date AND
128 cldr_rec.period_end_date
129 AND doc_type = 'PROD')
130 group by orgn_code,whse_code,item_id) prod_tr,
131 IC_WHSE_MST whs,
132 SY_ORGN_MST org,
133 IC_ITEM_MST iim
134 WHERE org.co_code = cldr_rec.co_code
135 AND prod_tr.item_id = iim.item_id
136 AND prod_tr.whse_code = whs.whse_code
137 AND prod_tr.orgn_code = org.orgn_code );
138
139 commit;
140 END LOOP;
141 CLOSE check_cldr_for_perd_bal;
142 END populate_summary;
143
144 PROCEDURE BUILD_SUMMARY(errbuf OUT NOCOPY varchar2,retcode OUT NOCOPY VARCHAR2) IS
145 -- Local Variables
146 e_buff VARCHAR2(2000);
147 l_last_run_date DATE;
148 l_log_end_date DATE := trunc(SYSDATE);
149 l_last_trns_eff_date DATE;
150 l_start_population_date DATE;
151 buff32k VARCHAR2(32767);
152 l_mesg VARCHAR2(2000);
153 l_conv_uom pmi_onhand_sale_sum.convertible_uom%TYPE;
154 l_conv_uom1 pmi_onhand_sale_sum.convertible_uom%TYPE;
155 l_table_owner VARCHAR2(40);
156
157 -- Cursors and cursor row type variables
158 CURSOR cur_bisopm_prod IS
159 SELECT LAST_RUN_DATE,ATTR1
160 FROM PMI_SUMMARY_LOG_TABLE
161 WHERE summary_table = 'PMI_PROD_SUM';
162 CURSOR cur_last_trans_eff_date IS
163 SELECT min(trans_date)
164 from (Select (min(trans_date)) trans_date
165 from IC_TRAN_PND
166 WHERE doc_type = 'PROD' AND (l_last_run_date IS NULL OR
167 (l_last_run_date IS NOT NULL AND last_update_date >= l_last_run_date))
168 UNION ALL
169 (Select (min(trans_date)) trans_date
170 from IC_TRAN_CMP
171 WHERE DOC_TYPE = 'PROD' AND (l_last_run_date IS NULL OR
172 (l_last_run_date IS NOT NULL AND last_update_date >= l_last_run_date))));
173 rows_exists number := null;
174 BEGIN
175 /* Checking Existancy of data in BIS Summary Table and get the populated UOM and last rundate */
176 OPEN cur_bisopm_prod;
177 FETCH cur_bisopm_prod INTO l_last_run_date,l_conv_uom;
178 IF cur_bisopm_prod%NOTFOUND THEN
179 CLOSE cur_bisopm_prod;
180 BEGIN
181 SELECT 1 into rows_exists
182 FROM PMI_PROD_SUM
183 WHERE ROWNUM = 1;
184 errbuf := FND_MESSAGE.get_string('PMI','PMI_SUMM_POPULATION_ERR');
185 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf );
186 retcode:= '2';
187 APP_EXCEPTION.Raise_exception;
188 EXCEPTION WHEN NO_DATA_FOUND THEN
189 NULL;
190 END;
191 ELSE
192 CLOSE cur_bisopm_prod;
193 END IF;
194 /* Check for Conversion UOM is defined or not */
195 IF fnd_profile.defined('PMI$CONV_UOM') THEN
196 l_conv_uom1 := fnd_profile.value('PMI$CONV_UOM');
197 IF l_conv_uom1 IS NULL THEN
198 errbuf := FND_MESSAGE.get_string('PMI','PMI_SET_CONV_UOM');
199 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf );
200 retcode:= '2';
201 APP_EXCEPTION.Raise_exception;
202 END IF;
203 PV_conv_uom := l_conv_uom1;
204 IF ((l_last_run_date IS NOT NULL) AND
205 (l_conv_uom1 <> l_conv_uom)) THEN
206 /* Delete data from summary table. now we need to populate using new conversion UOM */
207 buff32k := FND_MESSAGE.get_number('PMI','PMI_CONV_UOM_VAL_CHG') ||'-'||
208 FND_MESSAGE.get_string('PMI','PMI_CONV_UOM_VAL_CHG');
209 FND_FILE.PUT_LINE(FND_FILE.LOG, buff32k );
210 DELETE pmi_prod_sum;
211 COMMIT;
212 l_last_run_date := NULL;
213 END IF;
214 OPEN cur_last_trans_eff_date;
215 FETCH cur_last_trans_eff_date INTO l_last_trns_eff_date;
216 CLOSE cur_last_trans_eff_date;
217 l_start_population_date := least(nvl(trunc(l_last_run_date),trunc(SYSDATE)),trunc(l_last_trns_eff_date));
218 populate_summary(l_start_population_date,l_log_end_date);
219 BEGIN
220 UPDATE PMI_SUMMARY_LOG_TABLE
221 SET LAST_RUN_DATE = l_log_end_date,
222 ATTR1 = PV_conv_uom
223 WHERE summary_table = 'PMI_PROD_SUM';
224 IF SQL%ROWCOUNT = 0 THEN
225 INSERT INTO PMI_SUMMARY_LOG_TABLE (SUMMARY_TABLE,LAST_RUN_DATE,ATTR1,ATTR2,ATTR3,ATTR4,ATTR5,ATTR6)
226 VALUES ('PMI_PROD_SUM',l_log_end_date,PV_conv_uom,null,null,null,null,null);
227 END IF;
228 END;
229
230 SELECT TABLE_OWNER INTO l_table_owner
231 FROM USER_SYNONYMS
232 WHERE SYNONYM_NAME = 'PMI_PROD_SUM';
233 FND_STATS.GATHER_TABLE_STATS(l_table_owner, 'PMI_PROD_SUM');
234
235
236 ELSE
237 buff32k := FND_MESSAGE.get_number('PMI','PMI_CONV_UOM_PROF_MISS') ||'-'||
238 FND_MESSAGE.get_string('PMI','PMI_CONV_UOM_PROF_MISS');
239 FND_FILE.PUT_LINE(FND_FILE.LOG, buff32k );
240 retcode:= '2';
241 APP_EXCEPTION.Raise_exception;
242 END IF;
243 EXCEPTION
244 WHEN FND_FILE.UTL_FILE_ERROR then
245 errbuf := substr(fnd_message.get, 1, 254);
246 retcode := 2;
247 END BUILD_SUMMARY;
248 END PMI_BUILD_PROD_SUM;