[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_INV_TURNS_PKG
Source
1 PACKAGE BODY OPI_DBI_INV_TURNS_PKG AS
2 /* $Header: OPIDEIVTNB.pls 120.0 2005/05/24 18:36:40 appldev noship $ */
3
4 /* File scope globals */
5 g_user_id NUMBER;
6 g_login_id NUMBER;
7 g_degree NUMBER;
8
9
10 /*++++++++++++++++++++++++++++++++++++++++*/
11 /* PACKAGE LEVEL CONSTANTS */
12 /*++++++++++++++++++++++++++++++++++++++++*/
13
14 s_pkg_name CONSTANT VARCHAR2 (50) := 'opi_dbi_inv_turns_pkg';
15 s_ERROR CONSTANT NUMBER := -1; -- concurrent manager error code
16 s_WARNING CONSTANT NUMBER := 1; -- concurrent manager warning code
17 s_SUCCESS CONSTANT NUMBER := 0; -- concurrent manager success code
18
19
20 /*++++++++++++++++++++++++++++++++++++++++*/
21 /* Package level exceptions defined for
22 clearer error handling. */
23 /*++++++++++++++++++++++++++++++++++++++++*/
24
25 -- exception to raise if the temp table already exists
26 OBJECT_ALREADY_EXISTS EXCEPTION;
27 PRAGMA EXCEPTION_INIT (OBJECT_ALREADY_EXISTS, -955);
28
29
30 -- exception to raise if set up is not correct
31 SETUP_INVALID EXCEPTION;
32 PRAGMA EXCEPTION_INIT (SETUP_INVALID, -20000);
33
34 -- exception to raise if unable to get schema information
35 SCHEMA_INFO_NOT_FOUND EXCEPTION;
36 PRAGMA EXCEPTION_INIT (SCHEMA_INFO_NOT_FOUND, -20001);
37
38
39 /* initialize globals
40
41 Procedure to initialize global/file scope variables
42
43 Date Author Action
44 04/29/04 Dinkar Gupta Writing this
45 function because new GSCC standard
46 does not like globals initialized
47 on declaration
48 */
49 PROCEDURE initialize_globals
50 IS
51
52 l_stmt_num NUMBER;
53 l_proc_name VARCHAR2 (40);
54
55 BEGIN
56
57 l_proc_name := 'initialize_globals';
58
59 g_user_id := fnd_global.user_id;
60 g_login_id := fnd_global.login_id;
61 g_degree := 0;
62
63
64 END initialize_globals;
65
66
67 /*
68 Refresh_inventory_turns
69
70 Main procedure to refresh inventory turns.
71
72 Date Author Action
73 <> Luis Tong Wrote function.
74 05/03/04 Dinkar Gupta Bug fix 3593181. See file
75 header for details.
76 */
77
78 PROCEDURE Refresh_Inventory_Turns (errbuf OUT NOCOPY VARCHAR2,
79 retcode OUT NOCOPY NUMBER)
80 IS
81
82 l_stmt_num NUMBER;
83 l_proc_name VARCHAR2 (40);
84
85 l_row_count NUMBER;
86 l_min_trx_date DATE;
87 l_max_trx_date DATE;
88 i_err_num NUMBER;
89 i_err_msg VARCHAR2(255);
90 TYPE c_range_dates_type IS REF CURSOR;
91 c_range_dates c_range_dates_type;
92 l_opi_schema VARCHAR2(30);
93 l_status VARCHAR2(30);
94 l_industry VARCHAR2(30);
95
96 BEGIN
97
98 -- Initialization block, as per new GSCC standard
99 l_proc_name := 'Refresh_Inventory_Turns';
100
101 -- File scope globals
102 initialize_globals ();
103
104
105 if BIS_COLLECTION_UTILITIES.SETUP( 'OPI_DBI_INV_TURNS_F' ) = false then
106 RAISE SETUP_INVALID;
107 END IF;
108
109 /* **********************
110 Filling missing start date Time bucket rows
111 ********************** */
112
113 -- We restrict the completion within the min and max trx_date
114 -- of OPI_INV_ITD_ORG_MV
115
116 l_stmt_num := 10;
117 IF fnd_installation.get_app_info( 'OPI', l_status,
118 l_industry, l_opi_schema) THEN
119 execute immediate 'truncate table ' || l_opi_schema ||
120 '.OPI_DBI_INV_TURNS_STG ';
121 ELSE
122 RAISE SCHEMA_INFO_NOT_FOUND;
123 END IF;
124
125 -- Pick the smallest date that has inventory or cogs activity
126 -- to start at.
127 l_stmt_num := 20;
128 OPEN c_range_dates FOR
129 'select min (transaction_date)
130 from (
131 select min(TRANSACTION_DATE) transaction_date
132 from OPI_INV_ITD_ORG_MV
133 union all
134 select min (trunc (cogs_date)) transaction_date
135 from opi_dbi_cogs_f
136 where cogs_date is not null
137 and turns_cogs_flag = 1)';
138 FETCH c_range_dates INTO l_min_trx_date;
139 CLOSE c_range_dates;
140
141 -- Forcing row completion up to today.
142 -- This will make the report query return rows up to today
143 -- even though there is no data. It will assume balances are
144 -- remaining the same.
145 -- Also, Compute ITD inventory balance for all those org/date
146 -- pairs that are not present in the inventory fact, but
147 -- are present in the cogs fact.
148 l_max_trx_date := sysdate;
149
150 l_stmt_num := 30;
151 INSERT INTO OPI_DBI_INV_TURNS_STG (
152 ORGANIZATION_ID,
153 TRANSACTION_DATE,
154 INV_BALANCE_G,
155 INV_BALANCE_B,
156 INV_BALANCE_SG
157 )
158 SELECT
159 ORGANIZATION_ID,
160 TRANSACTION_DATE,
161 INV_BALANCE_G,
162 INV_BALANCE_B,
163 INV_BALANCE_SG
164 FROM
165 (
166 (
167 select keys.ORGANIZATION_ID ORGANIZATION_ID,
168 keys.start_date TRANSACTION_DATE,
169 (select INV_BALANCE_G
170 from OPI_INV_ITD_ORG_MV
171 where
172 ORGANIZATION_ID = keys.ORGANIZATION_ID
173 and TRANSACTION_DATE =
174 (select max(TRANSACTION_DATE) max_date
175 from OPI_INV_ITD_ORG_MV
176 where
177 TRANSACTION_DATE <= keys.start_date
178 and ORGANIZATION_ID = keys.ORGANIZATION_ID)
179 and rownum < 2
180 ) INV_BALANCE_G,
181 (select INV_BALANCE_B
182 from OPI_INV_ITD_ORG_MV
183 where
184 ORGANIZATION_ID = keys.ORGANIZATION_ID
185 and TRANSACTION_DATE =
186 (select max(TRANSACTION_DATE) max_date
187 from OPI_INV_ITD_ORG_MV
188 where
189 TRANSACTION_DATE <= keys.start_date
190 and ORGANIZATION_ID = keys.ORGANIZATION_ID)
191 and rownum < 2
192 ) INV_BALANCE_B,
193 (select INV_BALANCE_SG
194 from OPI_INV_ITD_ORG_MV
195 where
196 ORGANIZATION_ID = keys.ORGANIZATION_ID
197 and TRANSACTION_DATE =
198 (select max(TRANSACTION_DATE) max_date
199 from OPI_INV_ITD_ORG_MV
200 where
201 TRANSACTION_DATE <= keys.start_date
202 and ORGANIZATION_ID = keys.ORGANIZATION_ID)
203 and rownum < 2
204 ) INV_BALANCE_SG
205 from
206 (SELECT
207 ot.organization_id organization_id,
208 sd.start_date start_date
209 FROM
210 (
211 select ORGANIZATION_ID
212 from OPI_INV_ITD_ORG_MV
213 group by ORGANIZATION_ID
214 ) ot,
215 (
216 select start_date
217 from
218 (
219 select distinct WEEK_START_DATE start_date
220 from FII_TIME_DAY_ALL_V
221 where REPORT_DATE between l_min_trx_date and
222 l_max_trx_date
223 union
224 select distinct ENT_PERIOD_START_DATE start_date
225 from FII_TIME_DAY_ALL_V
226 where REPORT_DATE between l_min_trx_date and
227 l_max_trx_date
228 union
229 select distinct ENT_QTR_START_DATE start_date
230 from FII_TIME_DAY_ALL_V
231 where REPORT_DATE between l_min_trx_date and
232 l_max_trx_date
233 union
234 select distinct ENT_YEAR_START_DATE start_date
235 from FII_TIME_DAY_ALL_V
236 where REPORT_DATE between l_min_trx_date and
237 l_max_trx_date
238 )
239 where start_date between l_min_trx_date and l_max_trx_date
240 ) sd
241 UNION
242 SELECT organization_id,
243 start_date
244 FROM
245 (
246 SELECT DISTINCT
247 organization_id organization_id,
248 trunc (cogs_date) start_date
249 FROM opi_dbi_cogs_f
250 WHERE cogs_date is not null
251 AND turns_cogs_flag = 1
252 MINUS
253 SELECT DISTINCT
254 organization_id organization_id,
255 transaction_date start_date
256 FROM opi_inv_itd_org_mv) cogs_keys
257 ) keys
258 )
259 union
260 select
261 ORGANIZATION_ID,
262 TRANSACTION_DATE,
263 INV_BALANCE_G,
264 INV_BALANCE_B,
265 INV_BALANCE_SG
266 from OPI_INV_ITD_ORG_MV
267 );
268
269 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished Filling missing start date Time bucket rows process.');
270
271 /* *******************************
272 Calculating Weights, adding start dates
273 ******************************* */
274
275
276 l_stmt_num := 40;
277 IF fnd_installation.get_app_info( 'OPI', l_status,
278 l_industry, l_opi_schema) THEN
279 execute immediate 'truncate table ' || l_opi_schema ||
280 '.OPI_DBI_INV_TURNS_F ';
281 ELSE
282 RAISE SCHEMA_INFO_NOT_FOUND;
283 END IF;
284
285 l_stmt_num := 50;
286 -- This query was modified to do a full outer join between the
287 -- COGS fact and TURNS stg table. However, that is not needed
288 -- anymore since we ensure that all COGS keys are put into
289 -- the TURNS stg. That is the only place we compute ITD inventory
290 -- value for those COGS keys that are not part of the inventory fact.
291 -- Since turns = annualized inv/annualized cogs, make sure the cogs
292 -- value is never NULL. A corresponding fix was made the
293 -- opi_inv_itd_org_mv. The MV earlier had a unique key of org, date
294 -- and source, which caused two rows to be created for the same org/date
295 -- once CPCS was introduced with source = 3. The MV has been modified
296 -- to have a unique key of org, date.
297 INSERT /*+ append */
298 INTO opi_dbi_inv_turns_f
299 (
300 organization_id,
301 transaction_date,
302 start_date_wtd,
303 start_date_mtd,
304 start_date_qtd,
305 start_date_ytd,
306 weight,
307 inv_balance_g,
308 inv_balance_b,
309 inv_balance_sg,
310 cogs_val_g,
314 created_by,
311 cogs_val_b,
312 cogs_val_sg,
313 source,
315 last_update_login,
316 creation_date,
317 last_updated_by,
318 last_update_date
319 )
320 SELECT
321 turns.organization_id,
322 turns.transaction_date,
323 cal.week_start_date start_date_wtd,
324 cal.ent_period_start_date start_date_mtd,
325 cal.ent_qtr_start_date start_date_qtd,
326 cal.ent_year_start_date start_date_ytd,
327 nvl (lead (turns.transaction_date, 1) over
328 (partition by turns.organization_id
329 order by turns.transaction_date) - turns.transaction_date,
330 1) weight,
331 turns.inv_balance_g inv_balance_g,
332 turns.inv_balance_b inv_balance_b,
333 turns.inv_balance_sg inv_balance_sg,
334 nvl (cogs.cogs_val_g, 0) cogs_val_g,
335 nvl (cogs.cogs_val_b, 0) cogs_val_b,
336 nvl (cogs.cogs_val_sg, 0) cogs_val_sg,
337 1, -- actually this does not matter here.
338 g_user_id,
339 g_login_id,
340 sysdate,
341 g_user_id,
342 sysdate
343 FROM
344 opi_dbi_inv_turns_stg turns,
345 fii_time_day_all_v cal,
346 (SELECT /*+ no_merge */
347 organization_id,
348 trunc (cogs_date) cogs_date,
349 nvl (sum (cogs_val_b), 0) cogs_val_b,
350 nvl (sum (cogs_val_g), 0) cogs_val_g,
351 nvl (sum (cogs_val_sg), 0) cogs_val_sg
352 FROM opi_dbi_cogs_f
353 WHERE cogs_date is not null
354 AND turns_cogs_flag = 1
355 GROUP BY
356 organization_id,
357 trunc (cogs_date)) cogs
358 WHERE turns.transaction_date = cal.report_date
359 AND turns.transaction_date = cogs.cogs_date(+)
360 AND turns.organization_id = cogs.organization_id(+);
361
362 l_row_count := sql%rowcount;
363
364 BIS_COLLECTION_UTILITIES.PUT_LINE ('Inserted ' || l_row_count ||
365 ' rows into the turns table successfully.');
366 BIS_COLLECTION_UTILITIES.WRAPUP (TRUE, l_row_count,
367 'Pushed successfully into turns table.');
368 COMMIT;
369
370 errbuf := '';
371 retcode := s_SUCCESS;
372
373 EXCEPTION
374
375 WHEN SETUP_INVALID THEN
376
377 rollback;
378
379 retcode := s_ERROR;
380 errbuf := s_pkg_name || '.' || l_proc_name || ': #' ||
381 to_char (l_stmt_num) || ': ' ||
382 'Setup of OPI_DBI_INV_TURNS_F table is incorrect.';
383
384 BIS_COLLECTION_UTILITIES.WRAPUP(FALSE,
385 l_row_count,
386 'EXCEPTION '|| SQLCODE ||' : ' ||
387 errbuf);
388
389
390 return;
391
392
393 WHEN SCHEMA_INFO_NOT_FOUND THEN
394
395 rollback;
396
397 retcode := s_ERROR;
398 errbuf := s_pkg_name || '.' || l_proc_name || ': #' ||
399 to_char (l_stmt_num) || ': ' ||
400 'Unable to get OPI schema information.';
401
402 BIS_COLLECTION_UTILITIES.WRAPUP(FALSE,
403 l_row_count,
404 'EXCEPTION '|| SQLCODE ||' : ' ||
405 errbuf);
406
407
408 return;
409
410 WHEN OTHERS THEN
411
412 rollback;
413
414 retcode := s_ERROR;
415 errbuf := s_pkg_name || '.' || l_proc_name || ': ' ||
416 to_char (l_stmt_num) || ': ' ||
417 substr(SQLERRM, 1,200);
418
419
420 BIS_COLLECTION_UTILITIES.WRAPUP(FALSE,
424 return;
421 l_row_count,
422 'EXCEPTION '|| SQLCODE ||' : ' ||
423 errbuf);
425
426 END Refresh_Inventory_Turns;
427
428 END OPI_DBI_INV_TURNS_PKG;