[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_INV_VALUE_INCR_PKG
Source
1 PACKAGE BODY OPI_DBI_INV_VALUE_INCR_PKG as
2 /* $Header: OPIDIVRB.pls 120.27 2008/03/07 09:20:09 sdiwakar noship $ */
3
4 g_sysdate CONSTANT DATE := SYSDATE;
5 g_user_id CONSTANT NUMBER := nvl(fnd_global.user_id, -1);
6 g_login_id CONSTANT NUMBER := nvl(fnd_global.login_id, -1);
7 g_global_start_date DATE;
8 g_global_curr_code VARCHAR2(10);
9 g_global_sec_curr_code VARCHAR2(10);
10 g_global_rate_type VARCHAR2(32);
11 g_global_sec_rate_type VARCHAR2(32);
12 g_pkg_name VARCHAR2(200) := 'OPI_DBI_INV_VALUE_INCR_PKG';
13 g_opi_schema VARCHAR2(10);
14 --g_uom_conversion number;
15
16 -- User Defined Exceptions
17
18 INITIALIZATION_ERROR EXCEPTION;
19 PRAGMA EXCEPTION_INIT (INITIALIZATION_ERROR, -20900);
20
21 UOM_CONV_ERROR EXCEPTION;
22 PRAGMA EXCEPTION_INIT (UOM_CONV_ERROR, -20901);
23
24 -- This package is used for incremental load collection of Invnetory Management
25 -- Page. This will collect data for both ODM and OPM type of organization.
26 -- This will called using REFRESH Procedure
27 --REFRESH
28
29 -- --> CHECK_INCR_LOAD_SETUP
30 --
31 -- --> DISCRETE_REFRESH
32 -- ---> CLEAN_STAGING_TABLE
33 -- ---> OPI_DBI_BOUNDS_PKG.MAINTAIN_OPI_DBI_LOGS
34 -- ---> GET_NET_ACTIVITY
35 --
36 -- ---->> GET_ONHAND_ACTIVITY
37 -- ---->> GET_INTRANSIT_ACTIVITY
38 -- ---->> GET_WIP_ACTIVITY
39 --
40 -- ---> OPI_DBI_INV_VALUE_UTL_PKG.Get_Conversion_Rate
41 -- ---> MERGE_INTO_SUMMARY
42 -- ---> CLEAN_STAGING_TABLE
43 --
44 -- --> OPI_DBI_INV_CPCS_PKG.RUN_PERIOD_CLOSE_ADJUSTMENT
45 -- -->OPI_DBI_BOUNDS_PKG.print_opi_org_bounds
46 -- -->OPI_DBI_BOUNDS_PKG.SET_LOAD_SUCCESSFUL
47 -- --> OPI_DBI_BOUNDS_PKG.SET_LOAD_SUCCESSFUL
48 --
49 -- Incremental is re- runable. Even if it fail.
50 -- All staging tables are truncated at start of incremental load.
51 -- Merging of data is last step
52 -- Even if the staging tables are not cleared at the end of load
53 -- they will be cleared at the start of next run
54
55 PROCEDURE CHECK_INCR_LOAD_SETUP
56 IS
57 l_proc_name VARCHAR2 (40);
58 l_stmt_num NUMBER;
59 l_setup_good BOOLEAN;
60 l_status VARCHAR2(30) := NULL;
61 l_industry VARCHAR2(30) := NULL;
62 l_debug_msg VARCHAR2(200);
63 BEGIN
64 -- Initialization
65 l_proc_name := 'CHECK_INCR_LOAD_SETUP';
66 l_stmt_num := 0;
67
68 -- Check for the global start date setup.
69 -- These parameter must be set up prior to any DBI load.
70 g_global_start_date := trunc(bis_common_parameters.get_global_start_date);
71 g_global_curr_code := bis_common_parameters.get_currency_code;
72 g_global_sec_curr_code := bis_common_parameters.get_secondary_currency_code;
73 g_global_rate_type := bis_common_parameters.get_rate_type;
74 g_global_sec_rate_type := bis_common_parameters.get_secondary_rate_type;
75
76 IF (g_global_start_date IS NULL) THEN
77 l_debug_msg := 'Global start date is not defined';
78 RAISE INITIALIZATION_ERROR;
79 END IF;
80
81 IF (g_global_curr_code IS NULL) THEN
82 l_debug_msg := 'Global currency code is not defined';
83 RAISE INITIALIZATION_ERROR;
84 END IF;
85
86 IF (g_global_rate_type IS NULL) THEN
87 l_debug_msg := 'Global rate type is not defined';
88 RAISE INITIALIZATION_ERROR;
89 END IF;
90
91 IF (g_global_sec_curr_code IS NOT NULL AND g_global_sec_rate_type IS NULL) THEN
92 l_debug_msg := 'Global secondary rate type is not defined';
93 RAISE INITIALIZATION_ERROR;
94 END IF;
95
96 IF (g_global_sec_rate_type IS NOT NULL AND g_global_sec_curr_code IS NULL) THEN
97 l_debug_msg := 'Global secondary curr code is not defined';
98 RAISE INITIALIZATION_ERROR;
99 END IF;
100
101 l_setup_good := fnd_installation.get_app_info('OPI', l_status, l_industry, g_opi_schema);
102 IF (l_setup_good = FALSE OR g_opi_schema IS NULL) THEN
103 l_debug_msg := 'could not find OPI schema';
104 RAISE INITIALIZATION_ERROR;
105 END IF;
106 EXCEPTION
107 WHEN INITIALIZATION_ERROR THEN
108 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
109 RAISE;
110
111 WHEN OTHERS THEN
112 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
113 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
114 RAISE;
115
116 END CHECK_INCR_LOAD_SETUP;
117
118
119 -------------------------------------------------------------------------------
120
121 PROCEDURE CLEAN_STAGING_TABLE
122 IS
123 l_stmt_num NUMBER;
124 l_debug_msg VARCHAR2(1000);
125 l_proc_name VARCHAR2 (60);
126 l_debug_mode VARCHAR2(1);
127 l_module_name VARCHAR2(30);
128 BEGIN
129 l_proc_name := 'CLEAN_STAGING_TABLE';
130 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
131 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
132
133 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
134 l_debug_msg := 'Start of cleaning staging table';
135 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
136 END IF;
137
138 l_stmt_num := 10;
139 execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_INTR_MIP_TMP';
140
141 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
142 l_debug_msg := 'OPI_DBI_INTR_MIP_TMP table truncated.';
143 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
144 END IF;
145
146 l_stmt_num := 20;
147 execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_INTRANSIT_STG';
148
149 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
150 l_debug_msg := 'OPI_DBI_INTRANSIT_STG table truncated.';
151 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
152 END IF;
153
154 l_stmt_num := 30;
155 execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_WIP_STG';
156
157 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
158 l_debug_msg := 'OPI_DBI_WIP_STG table truncated.';
159 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
160 END IF;
161
162 l_stmt_num := 40;
163 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
164 l_debug_msg := 'OPI_DBI_ONHAND_STG table truncated.';
165 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
166 END IF;
167
168 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
169 l_debug_msg := 'End of cleaning staging table';
170 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
171 END IF;
172 EXCEPTION
173 WHEN OTHERS THEN
174 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
175 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
176 RAISE;
177 END CLEAN_STAGING_TABLE;
178
179 -------------------------------------------------------------------------------
180 /* GET_ONHAND_ACTIVITY
181
182 Gets the Onhand, WIP Inventory transactions for Incremental Load.
183
184 Author Date Action
185 Suhasini 09/11/2006 Bug Fix: 5490217: Corrected subinventory_code for
186 Direct Org transfer between different subinventories
187 when transferred from std to avg costed organizations
188 Forward ported from 11.5.10 Bug 5403832
189 */
190 PROCEDURE GET_ONHAND_ACTIVITY
191 IS
192 l_stmt_num NUMBER;
193 l_debug_msg VARCHAR2(1000);
194 l_proc_name VARCHAR2 (60);
195 l_debug_mode VARCHAR2(1);
196 l_module_name VARCHAR2(30);
197 BEGIN
198 l_proc_name := 'GET_ONHAND_ACTIVITY';
199 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
200 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
201
202 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
203 l_debug_msg := 'Start of GET_ONHAND_ACTIVITY';
204 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
205 END IF;
206
207 -- newly created backdated transactions are not filtered out.
208 -- these will be selected and those which have transaction date prior to GSD
209 -- are bucketed on GSD
210 /* specifications of the API needs to be changed to be called only once for all orgs in incremental load */
211 l_stmt_num :=10;
212 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
213 l_debug_msg := 'Inserting data Into OPI_DBI_ONHAND_STG ';
214 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
215 END IF;
216
217 INSERT /*+ append */ INTO OPI_DBI_ONHAND_STG
218 (organization_id,
219 subinventory_code,
220 inventory_item_id,
221 transaction_date,
222 onhand_qty_draft,
223 onhand_qty,
224 onhand_value_b_draft,
225 onhand_value_b,
226 wip_value_b_draft,
227 wip_value_b,
228 source,
229 push_to_fact_flag,
230 transaction_source)
231 SELECT mmt1.ORGANIZATION_ID,
232 mmt1.SUBINVENTORY_CODE,
233 mmt1.INVENTORY_ITEM_ID,
234 /* backdated transactions prior to GSD are bucketed on GSD in incremental load */
235 decode(sign(mmt1.transaction_date - g_global_start_date), -1,
236 g_global_start_date, mmt1.transaction_date) transaction_date,
237 0 onhand_qty_draft, -- draft qty is applicable to process orgs only.
238 nvl(mmt1.onhand_qty,0) onhand_qty,
239 0 onhand_value_b_draft, -- draft value is applicable to process orgs only.
240 nvl(mta1.base_transaction_value,0) onhand_value_b,
241 null wip_value_b_draft, -- wip value in this table is populated only in initial load
242 null wip_value_b, -- in incr load wip incr extracts are separate
243 1 source,
244 null push_to_fact_flag, -- used only in initial load
245 'MTA' transaction_source -- used only in initial load
246 FROM
247 (SELECT mta.organization_id,
248 decode(mmt3.transaction_action_id,
249 2,decode(sign(mta.primary_quantity),-1,mmt3.subinventory_code,mmt3.transfer_subinventory),
250 3, decode(sign(mta.primary_quantity), sign(mmt3.primary_quantity), mmt3.subinventory_code,
251 decode(sign(mta.primary_quantity),-1,mmt3.subinventory_code, mmt3.transfer_subinventory)), -- Bug 5490217
252 28,decode(sign(mta.primary_quantity), -1, mmt3.subinventory_code, mmt3.transfer_subinventory),
253 24,nvl(mmt3.subinventory_code,'-1'),mmt3.subinventory_code) subinventory_code,
254 mta.inventory_item_id,
255 trunc(mta.transaction_date) transaction_date,
256 sum(mta.base_transaction_value) base_transaction_value
257 FROM mtl_transaction_accounts mta,
258 mtl_material_transactions mmt3,
259 opi_dbi_conc_prog_run_log prl,
260 mtl_parameters mp
261 WHERE mta.accounting_line_type = 1
262 AND mta.transaction_id >= prl.from_bound_id
263 AND mta.transaction_id < to_bound_id
264 AND prl.etl_type = 'INVENTORY'
265 AND prl.load_type = 'INCR'
266 AND prl.driving_table_code = 'MMT'
267 AND prl.bound_level_entity_id = mta.organization_id
268 AND mmt3.transaction_id = mta.transaction_id
269 AND prl.bound_level_entity_id = mp.organization_id
270 AND nvl(mp.process_enabled_flag,'N') <> 'Y'
271 GROUP BY mta.inventory_item_id,
272 decode(mmt3.transaction_action_id,
273 2,decode(sign(mta.primary_quantity),-1,mmt3.subinventory_code,mmt3.transfer_subinventory),
274 3, decode(sign(mta.primary_quantity), sign(mmt3.primary_quantity), mmt3.subinventory_code,
275 decode(sign(mta.primary_quantity),-1,mmt3.subinventory_code, mmt3.transfer_subinventory)), -- Bug 5490217
276 28, decode(sign(mta.primary_quantity), -1, mmt3.subinventory_code, mmt3.transfer_subinventory),
277 24, nvl(mmt3.subinventory_code,'-1'),mmt3.subinventory_code),
278 mta.organization_id,
279 trunc(mta.transaction_date)
280 ) mta1,
281 (
282 -- csheu 3/31/2003. Filter out consigned Inventory transactions
283 -- Added the hint to fix bug #3223207
284 SELECT /*+ index(mmt, MTL_MATERIAL_TRANSACTIONS_U1) */
285 mmt.organization_id,
286 decode(mmt.transaction_action_id,24,nvl(mmt.subinventory_code,-1),mmt.subinventory_code) subinventory_code,
287 mmt.inventory_item_id,
288 trunc(mmt.transaction_date) transaction_date,
289 nvl(sum(decode(mmt.transaction_action_id,24,0,mmt.primary_quantity)),0) onhand_qty
290 FROM mtl_material_transactions mmt,
291 mtl_parameters mp,
292 opi_dbi_conc_prog_run_log prl
293 WHERE prl.etl_type = 'INVENTORY'
294 AND prl.load_type = 'INCR'
295 AND prl.driving_table_code = 'MMT'
296 AND mmt.organization_id = prl.bound_level_entity_id
297 AND mmt.transaction_type_id not in (73, 25, 26, 90, 91, 92,55, 56, 57, 58, 87, 88, 89)
298 AND mmt.organization_id = nvl(mmt.owning_organization_id,mmt.organization_id)
299 AND nvl(mmt.owning_tp_type,2) = 2 -- exclude consigned inventory
300 AND nvl(mmt.logical_transaction,-99) <> 1 -- 11.5.10 changes exclude logical txns
301 AND mmt.transaction_id >= prl.from_bound_id
302 AND mmt.transaction_id < prl.to_bound_id
303 AND prl.bound_level_entity_id = mp.organization_id
304 AND nvl(mp.process_enabled_flag,'N') <> 'Y' -- only discrete orgs
305 GROUP BY mmt.organization_id,
306 decode(mmt.transaction_action_id,24,nvl(mmt.subinventory_code,-1),mmt.subinventory_code) ,
307 mmt.inventory_item_id,
308 trunc(mmt.transaction_date)
309 --, msi.primary_uom_code
310 ) mmt1
311 WHERE mta1.organization_id(+) = mmt1.organization_id -- expense item txns dont have recs in mta
312 AND mta1.inventory_item_id(+) = mmt1.inventory_item_id
313 AND mta1.transaction_date(+) = mmt1.transaction_date
314 AND mta1.subinventory_code(+) = mmt1.subinventory_code
315 AND (mmt1.onhand_qty <> 0 or mta1.base_transaction_value <> 0);
316
317 l_debug_msg := 'Inserted into staging table OPI_DBI_ONHAND_STG - ' || SQL%ROWCOUNT || ' rows. ';
318 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
319
320 commit;
321
322 l_stmt_num :=20;
323 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
324 l_debug_msg := 'Inserting data Into OPI_DBI_ONHAND_STG ';
325 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
326 END IF;
327
328 -- get the process organizations data by joining to GTV
329 -- Query should be (to be verified in performance testing) driven by GTV based on the dates in Log Table.
330 -- grouping is done at transaction_id level in inside query because mmt can be joined at that level
331 -- it is assumed that inner query on gtv is resolved first and then mmt is joined
332 -- using unique index on U1 on MMT. this needs to be tested in performance testing.
333 -- commit should be performed by wrapper as if the program errors out in between incremental load its not
334 -- re-runnable
335 INSERT /*+ append */ INTO OPI_DBI_ONHAND_STG
336 (organization_id,
337 subinventory_code,
338 inventory_item_id,
339 transaction_date,
340 onhand_qty_draft,
341 onhand_qty,
342 onhand_value_b_draft,
343 onhand_value_b,
344 wip_value_b_draft,
345 wip_value_b,
346 source,
347 push_to_fact_flag,
348 transaction_source
349 )
350 SELECT mmt.organization_id,
351 gtv.subinventory_code,
352 mmt.inventory_item_id,
353 -- backdated transactions prior to GSD are bucketed on GSD in incremental load
354 decode(sign(trunc(mmt.transaction_date) - g_global_start_date), -1,g_global_start_date, trunc(mmt.transaction_date)) transaction_date,
355 sum(case when gtv.accounted_flag = 'D' then -- changed mmt.opm_costed_flag to gtv.accounted_flag
356 decode(mmt.transaction_action_id,2
357 ,decode(gtv.subinventory_code,mmt.transfer_subinventory,-1* mmt.primary_quantity,mmt.primary_quantity)
358 ,28
359 ,decode(gtv.subinventory_code,mmt.transfer_subinventory,-1* mmt.primary_quantity,mmt.primary_quantity)
360 ,mmt.primary_quantity)
361 else 0 end) onhand_qty_draft, --Bug 4704689
362 -- sum(case when mmt.opm_costed_flag IS NULL then mmt.primary_quantity else 0 end) onhand_qty,
363 sum(case when gtv.accounted_flag IS NULL then -- changed mmt.opm_costed_flag to gtv.accounted_flag
364 decode(mmt.transaction_action_id,2
365 ,decode(gtv.subinventory_code,mmt.transfer_subinventory,-1* mmt.primary_quantity,mmt.primary_quantity)
366 ,28
367 ,decode(gtv.subinventory_code,mmt.transfer_subinventory,-1* mmt.primary_quantity,mmt.primary_quantity)
368 ,mmt.primary_quantity)
369 else 0 end) onhand_qty, --BUG 4704689
370 sum(gtv.onhand_value_b_draft) onhand_value_b_draft,
371 sum(gtv.onhand_value_b) onhand_value_b,
372 null wip_value_b_draft, -- wip value in this table is populated only in initial load
373 null wip_value_b, -- in incr load wip incr extracts are separate
374 2 source, -- 1 for discrete 2 for process orgs 3 for old opm data
375 null push_to_fact_flag, -- used only in initial load
376 'GTV' transaction_source -- used only in initial load
377 FROM
378 (SELECT gtv.transaction_id,
379 nvl(gtv.subinventory_code,-1) subinventory_code,
380 sum(txn_base_value) onhand_value_b,
381 0 onhand_value_b_draft,
382 gtv.accounted_flag
383 FROM gmf_transaction_valuation gtv,
384 opi_dbi_org_le_temp tmp, --Bug 4768058
385 opi_dbi_conc_prog_run_log prl
386 WHERE prl.driving_table_code = 'GTV'
387 AND prl.load_type = 'INCR'
388 AND prl.etl_type = 'INVENTORY'
389 AND gtv.journal_line_type = 'INV'
390 --AND gtv.transaction_source = 'INVENTORY' --bug 4870029
391 and gtv.ledger_id = tmp.ledger_id --Bug 4768058
392 and gtv.legal_entity_id = tmp.legal_entity_id
393 and gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
394 and gtv.organization_id = tmp. organization_id
395 AND gtv.final_posting_date >= prl.from_bound_date
396 AND gtv.final_posting_date < prl.to_bound_date
397 AND gtv.accounted_flag IS NULL
398 GROUP BY gtv.transaction_id,
399 nvl(gtv.subinventory_code,-1),
400 gtv.accounted_flag
401 UNION ALL
402 -- union all is being done here assuming that both the union alls are driven by
403 -- separate indexes on GTV. one by final_posted_date and other by accounted_flag
404 SELECT gtv.transaction_id,
405 nvl(gtv.subinventory_code,-1) subinventory_code,
406 0 onhand_value_b,
407 sum(txn_base_value) onhand_value_b_draft,
408 gtv.accounted_flag
409 FROM gmf_transaction_valuation gtv,
410 opi_dbi_org_le_temp tmp --Bug 4768058
411 WHERE gtv.journal_line_type IN ('INV')
412 --AND gtv.transaction_source = 'INVENTORY' --bug 4870029
413 and gtv.ledger_id = tmp.ledger_id--Bug 4768058
414 and gtv.legal_entity_id = tmp.legal_entity_id
415 and gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
416 and gtv.organization_id = tmp. organization_id
417 AND gtv.accounted_flag = 'D' --
418 GROUP BY gtv.transaction_id,
419 nvl(gtv.subinventory_code,-1),
420 gtv.accounted_flag
421 ) gtv,
422 mtl_material_transactions mmt,
423 mtl_system_items msi
424 WHERE mmt.transaction_id = gtv.transaction_id
425 AND mmt.inventory_item_id = msi.inventory_item_id
426 AND mmt.organization_id = msi.organization_id
427 GROUP BY mmt.organization_id,
428 gtv.subinventory_code,
429 mmt.inventory_item_id,
430 -- backdated transactions prior to GSD are bucketed on GSD in incremental load
431 decode(sign(trunc(mmt.transaction_date) - g_global_start_date), -1,g_global_start_date, trunc(mmt.transaction_date));
432
433 l_debug_msg := 'Inserted into staging table OPI_DBI_ONHAND_STG - ' || SQL%ROWCOUNT || ' rows. ';
434 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
435
436 commit;
437
438 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
439 l_debug_msg := 'End of GET_ONHAND_ACTIVITY';
440 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
441 END IF;
442 EXCEPTION
443 WHEN OTHERS THEN
444 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
445 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
446 RAISE;
447 END GET_ONHAND_ACTIVITY;
448
449 -------------------------------------------------------------------------------
450
451 PROCEDURE GET_INTRANSIT_ACTIVITY
452 IS
453 l_stmt_num NUMBER;
454 l_debug_msg VARCHAR2(1000);
455 l_proc_name VARCHAR2 (60);
456 l_debug_mode VARCHAR2(1);
457 l_module_name VARCHAR2(30);
458 from_mta_id NUMBER;
459 to_mta_id NUMBER;
460 BEGIN
461 l_proc_name := 'GET_INTRANSIT_ACTIVITY';
462 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
463 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
464
465 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
466 l_debug_msg := 'Start of GET_INTRANSIT_ACTIVITY';
467 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
468 END IF;
469
470
471 l_stmt_num :=10;
472 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
473 l_debug_msg := 'Selecting from_mta bound ';
474 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
475 END IF;
476
477 BEGIN
478
479 select min(FROM_BOUND_ID) into from_mta_id --USED IN DISCRETE QUERY
480 from OPI_DBI_CONC_PROG_RUN_LOG log
481 where log.driving_table_code= 'MMT'
482 And log.etl_type = 'INVENTORY'
483 and FROM_BOUND_ID IS NOT NULL
484 And log.load_type= 'INCR';
485
486 EXCEPTION
487 WHEN OTHERS THEN
488 null;
489 END;
490
491 l_stmt_num :=20;
492 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
493 l_debug_msg := 'Selecting from_mta bound ';
494 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
495 END IF;
496
497 BEGIN
498
499 select Max(TO_BOUND_ID) into to_mta_id -- USED IN DISCRETE QUERY
500 from OPI_DBI_CONC_PROG_RUN_LOG log
501 where log.driving_table_code= 'MMT'
502 And log.etl_type = 'INVENTORY'
503 And log.load_type= 'INCR';
504 EXCEPTION
505 WHEN OTHERS THEN
506 null;
507 END;
508 -- Setup the intransit shipping network parameters.
509 -- FOB = 1 = Shipment i.e. the to_org is the owning_org.
510 -- FOB = 2 = Receipt i.e. the from_org is the owning_org.
511 -- For shipments, the from_org is the from_org in MIP and the
512 -- to_org is the to_org in MIP.
513 -- For receipts, the roles of the orgs are reversed.
514
515 l_stmt_num :=30;
516 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
517 l_debug_msg := 'Insert Data into opi_dbi_intr_mip_tmp ';
518 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
519 END IF;
520
521 OPI_DBI_INV_VALUE_INIT_PKG.intransit_setup('INCR');
522 commit;
523
524 l_stmt_num :=40;
525 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
526 l_debug_msg := 'Inserting data Into opi_dbi_intransit_stg for ODM ';
527 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
528 END IF;
529
530 -- R12 changes.
531 -- 1. Handle intransit across Process and discrete orgs.
532 -- 2. log table is modified
533 --
534 -- mip has two records for each setup in mtl_parameters.
535 -- e.g. for M1 -> M2 FOB = 2 there are two records in mip
536 -- from_org to_org owning_org txn_action_id
537 -- M1 M2 M1 21
538 -- M2 M1 M1 12
539 -- for another setup line lets say M2 -> M1 fob = 1 there are another two set or records
540 -- M2 M1 M1 21
541 -- M2 M1 M1 12
542 -- so what can be inferred from this data is that
543 -- whenever txn_action_id = 21 and FOB = 2 from_org = owning_org
544 -- whenever txn_action_id = 12 and FOB = 1 from_org = owning_org
545 -- as from_org is joined with MMT org this is the txn_org and the other is transfer org
546 -- this is slightly confusing as naming convention for columns in mip is not correct
547 -- instead of from_org and to_org it should have been txn_org and transfer_org
548 -- for new senarios in R12 txn 15 and 22 have come in.
549 -- 15 is logical receipt and 22 is logical shipment.
550 -- both these logical txns always has txn_organization same as owning organization.
551 -- refer to detail use cases in DLD for this.
552 -- The quantity on these txns is also the +ve intransit quantity.
553
554 -- UOM conversion Logic
555 --Condition UOM of Transfer Org and Receiving Org
556 -- Same Different
557 --Shipment FOB
558 -- Shipping Conversion Not Required Conversion Required
559 -- Receiving Conversion Not Required Conversion Not Required
560 --Receipt
561 -- Shipping Conversion Not Required Conversion Not Required
562 -- Receiving Conversion Not Required Conversion Required
563
564 -- Index on opi_dbi_intr_mip_tmp Can avoid full table scan
565 OPI_DBI_RPT_UTIL_PKG.g_pk_uom_conversion :=1 ;
566 INSERT /*+ append */ INTO OPI_DBI_INTRANSIT_STG(
567 organization_id,
568 inventory_item_id,
569 transaction_date,
570 intransit_qty,
571 primary_uom,
572 intransit_value_b,
573 source,
574 creation_date,
575 last_update_date,
576 created_by,
577 last_updated_by,
578 last_update_login)
579 SELECT /*+ use_hash(mta1) use_hash(mmt1) */
580 organization_id,
581 inventory_item_id,
582 decode (sign (trx_date - g_global_start_date),-1, g_global_start_date,trx_date) transaction_date,
583 SUM (qty) intransit_qty,
584 primary_uom_code primary_uom,
585 SUM(value)intransit_value_b,
586 decode(process_flag,'N',1,2), --Discrete/Process Bug fix: 5362465
587 sysdate,
588 sysdate,
589 g_user_id,
590 g_user_id,
591 g_login_id
592 FROM
593 (SELECT
594 mip.owning_organization_id organization_id,
595 mip.owning_org_process_flag process_flag,
596 mmt1.inventory_item_id,
597 -- logical txn no need of conversion as they are always against the owning org.
598 -- logical always contain qty in right sign there is -1 outside as well
599 -- -1 is done outside as txn quantity is always reverse sign of the txn qty.
600 -- for intransit across process and discrete orgs only logical txns are considered
601 -1 * SUM (
602 DECODE(
603 msi_fm.primary_uom_code,
604 msi_to.primary_uom_code,
605 decode(mmt1.transaction_action_id,
606 22, mmt1.primary_quantity, --Absolute value fix
607 15, -1 * mmt1.primary_quantity,
608 mmt1.primary_quantity), --Bug 4878458
609 DECODE(
610 mmt1.transaction_action_id,
611 21, DECODE(
612 mip.fob_point,
613 2, mmt1.primary_quantity,
614 OPI_DBI_RPT_UTIL_PKG.OPI_UM_CONVERT(mmt1.inventory_item_id,mmt1.primary_quantity , msi_fm.primary_uom_code,msi_to.primary_uom_code)),
615 12, DECODE (
616 mip.fob_point,
617 2, OPI_DBI_RPT_UTIL_PKG.OPI_UM_CONVERT(mmt1.inventory_item_id,mmt1.primary_quantity , msi_fm.primary_uom_code,msi_to.primary_uom_code),
618 mmt1.primary_quantity),
619 22, mmt1.primary_quantity, --Absolute value fix
620 15, -1 * mmt1.primary_quantity))) qty,
621 SUM (base_transaction_value) value,
622 DECODE (mip.owning_organization_id, msi_fm.organization_id, msi_fm.primary_uom_code
623 ,msi_to.organization_id, msi_to.primary_uom_code) primary_uom_code,
624 TRUNC (mmt1.transaction_date) trx_date
625 FROM opi_dbi_intr_mip_tmp mip,
626 mtl_material_transactions mmt1,
627 (select
628 transaction_id,
629 sum (base_transaction_value) base_transaction_value
630 from mtl_transaction_accounts
631 where accounting_line_type = 14
632 AND transaction_id >= from_mta_id --BOUNDS COLLECTED ABOVE
633 ANd transaction_id < to_mta_id
634 group by transaction_id) mta,
635 mtl_system_items msi_fm,
636 mtl_system_items msi_to,
637 OPI_DBI_CONC_PROG_RUN_LOG col,
638 MTL_PARAMETERS mp
639 WHERE mmt1.organization_id = mip.from_organization_id
640 AND mmt1.transfer_organization_id = mip.to_organization_id
641 AND mmt1.transaction_action_id in (15,12,22,21)
642 AND decode(mmt1.transaction_action_id,15,12,22,21,mmt1.transaction_action_id) = mip.transaction_action_id
643 -- condition below avoids getting 1 of the physical txns across process and discrete orgs
644 -- as the process flag is different for owning org and txn organization
645 -- for more detail refer to DLD test cases
646 And mp.organization_id =mmt1.organization_id
647 and mp.process_enabled_flag = mip.owning_org_process_flag --make sure only logical collected incase of D-> P and P->D
648 AND mip.from_organization_id = col.bound_level_entity_id
649 AND mta.transaction_id (+)= mmt1.transaction_id -- to collect expense item
650 AND msi_fm.inventory_item_id = mmt1.inventory_item_id
651 AND msi_fm.organization_id = mip.from_organization_id
652 and msi_to.inventory_item_id = mmt1.inventory_item_id
653 AND msi_to.organization_id = mip.to_organization_id
654 AND mmt1.transaction_id >= col.from_bound_id
655 AND mmt1.transaction_id < col.to_bound_id
656 AND col. driving_table_code= 'MMT'
657 AND col.etl_type = 'INVENTORY'
658 AND col.load_type= 'INCR'
659 GROUP BY
660 mip.owning_organization_id,
661 mip.owning_org_process_flag, -- Bug fix: 5362465
662 mmt1.inventory_item_id,
663 decode (mip.fob_point,2, decode (mip.transaction_action_id,21, msi_fm.primary_uom_code,
664 msi_to.primary_uom_code),decode (mip.transaction_action_id,
665 12, msi_to.primary_uom_code,msi_fm.primary_uom_code)) ,
666 trunc(mmt1.transaction_date),
667 DECODE (mip.owning_organization_id, msi_fm.organization_id, msi_fm.primary_uom_code
668 ,msi_to.organization_id, msi_to.primary_uom_code)
669 UNION ALL
670 SELECT
671 mmt1.organization_id organization_id,
672 'N' process_flag, -- Bug fix: 5362465, source is only discrete here
673 mmt1.inventory_item_id,
674 0 qty,
675 sum (base_transaction_value) value,
676 msi_fm.primary_uom_code,
677 trunc(mmt1.transaction_date) trx_date
678 FROM mtl_material_transactions mmt1,
679 mtl_transaction_accounts mta,
680 mtl_system_items msi_fm,
681 OPI_DBI_CONC_PROG_RUN_LOG col
682 WHERE mmt1.transaction_action_id = 24
683 AND mta.transaction_id = mmt1.transaction_id
684 AND mmt1.organization_id = mta.organization_id
685 AND mta.accounting_line_type = 14
686 AND msi_fm.inventory_item_id = mmt1.inventory_item_id
687 AND msi_fm.organization_id = mmt1.organization_id
688 AND mmt1.organization_id = col.BOUND_LEVEL_ENTITY_ID --col.organization_id
689 AND mmt1.transaction_id >= col.FROM_BOUND_ID
690 AND mmt1.transaction_id < col.TO_BOUND_ID
691 And col. DRIVING_TABLE_CODE= 'MMT'
692 And col.ETL_TYPE = 'INVENTORY'
693 And col.LOAD_TYPE= 'INCR'
694 GROUP BY
695 mmt1.organization_id,
696 mmt1.inventory_item_id,
697 msi_fm.primary_uom_code,
698 trunc(mmt1.transaction_date))
699 GROUP BY
700 organization_id,
701 inventory_item_id,
702 primary_uom_code,
703 trx_date,
704 decode(process_flag,'N',1,2) -- Bug fix: 5362465
705 HAVING sum(value) <> 0 or sum(qty) <> 0;
706
707 if OPI_DBI_RPT_UTIL_PKG.g_pk_uom_conversion <>1 then
708 Raise UOM_CONV_ERROR;
709 end if;
710
711 l_debug_msg := 'Inserted into staging table opi_dbi_intransit_stg - ' || SQL%ROWCOUNT || ' rows. ';
712 OPI_DBI_BOUNDS_PKG.write(g_pkg_name, l_proc_name,l_stmt_num, l_debug_msg);
713
714 commit;
715 l_stmt_num :=50;
716 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
717 l_debug_msg := 'Inserting data Into opi_dbi_intransit_stg for OPM Post R12 ';
718 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
719 END IF;
720 OPI_DBI_RPT_UTIL_PKG.g_pk_uom_conversion :=1 ;
721 -- Query to collect POST R12 OPM data
722 INSERT /*+ APPEND */ INTO OPI_DBI_INTRANSIT_STG(
723 organization_id,
724 inventory_item_id,
725 transaction_date,
726 intransit_qty,
727 intransit_qty_draft,
728 primary_uom,
729 intransit_value_b,
730 intransit_value_draft_b,
731 source,
732 creation_date,
733 last_update_date,
734 created_by,
735 last_updated_by,
736 last_update_login)
737 SELECT /*+ use_hash(mta1) use_hash(mmt1) */
738 organization_id,
739 inventory_item_id,
740 DECODE (SIGN (trx_date - g_global_start_date),
741 -1, g_global_start_date,
742 trx_date) transaction_date,
743 SUM (qty) intransit_qty,
744 SUM(draft_qty) intransit_qty_draft,
745 primary_uom_code primary_uom,
746 SUM (value) intransit_value_b,
747 SUM(draft_value) intransit_value_draft_b,
748 decode(process_flag,'N',1,2), -- 1 - Discrete/ 2 - Process -- Bug fix: 5362465
749 sysdate,
750 sysdate,
751 g_user_id,
752 g_user_id,
753 g_login_id
754 FROM
755 (SELECT
756 mip.owning_organization_id organization_id,
757 mip.owning_org_process_flag process_flag, -- Bug fix: 5362465
758 mmt1.inventory_item_id,
759 -1 * sum (DECODE(gtv.accounted_flag, --
760 -- -1 * sum (DECODE('D',
761 'D',0, DECODE (msi_fm.primary_uom_code,
762 msi_to.primary_uom_code,
763 decode(mmt1.transaction_action_id,
764 22, mmt1.primary_quantity, --Absolute value fix
765 15, -1 * mmt1.primary_quantity,
766 mmt1.primary_quantity), --Bug 4878458
767 DECODE (mmt1.transaction_action_id,
768 21, DECODE (mip.fob_point,
769 2, mmt1.primary_quantity,
770 OPI_DBI_RPT_UTIL_PKG.OPI_UM_CONVERT(mmt1.inventory_item_id,mmt1.primary_quantity , msi_fm.primary_uom_code,msi_to.primary_uom_code)),
771 12, DECODE (mip.fob_point,
772 2, OPI_DBI_RPT_UTIL_PKG.OPI_UM_CONVERT(mmt1.inventory_item_id,mmt1.primary_quantity , msi_fm.primary_uom_code,msi_to.primary_uom_code),
773 mmt1.primary_quantity),
774 22, mmt1.primary_quantity ,-- Absolute value fix, no need of conversion ??
775 15, -1 * mmt1.primary_quantity
776 )))) qty, -- Bug 4901338, removed ,0
777 -1 * sum (DECODE(gtv.accounted_flag, --
778 -- -1 * sum (DECODE('D',
779 'D',DECODE (msi_fm.primary_uom_code,
780 msi_to.primary_uom_code,
781 decode(mmt1.transaction_action_id,
782 22, mmt1.primary_quantity, --Absolute value fix
783 15, -1 * mmt1.primary_quantity,
784 mmt1.primary_quantity), --Bug 4878458
785 DECODE (mmt1.transaction_action_id,
786 21, DECODE (mip.fob_point,
787 2, mmt1.primary_quantity,
788 OPI_DBI_RPT_UTIL_PKG.OPI_UM_CONVERT(mmt1.inventory_item_id, mmt1.primary_quantity , msi_fm.primary_uom_code,msi_to.primary_uom_code)),
789 12, DECODE (mip.fob_point,
790 2, OPI_DBI_RPT_UTIL_PKG.OPI_UM_CONVERT(mmt1.inventory_item_id, mmt1.primary_quantity , msi_fm.primary_uom_code,msi_to.primary_uom_code),
791 mmt1.primary_quantity),
792 22, mmt1.primary_quantity ,-- Absolute value fix, no need of conversion ??
793 15, -1 * mmt1.primary_quantity
794 )),0)) Draft_qty,
795 SUM(DECODE(gtv.accounted_flag --
796 --SUM(DECODE('D'
797 ,'D',base_transaction_value)) Draft_Value,
798 SUM(DECODE(gtv.accounted_flag --
799 --SUM(DECODE('D'
800 ,'D',0,base_transaction_value)) Value,
801 DECODE (mip.owning_organization_id, msi_fm.organization_id, msi_fm.primary_uom_code
802 ,msi_to.organization_id, msi_to.primary_uom_code) primary_uom_code,
803 TRUNC (mmt1.transaction_date) trx_date
804 FROM opi_dbi_intr_mip_tmp mip,
805 mtl_material_transactions mmt1,
806 (SELECT transaction_id,
807 sum(txn_base_value) base_transaction_value
808 ,accounted_flag
809 FROM gmf_transaction_valuation gtv,
810 opi_dbi_org_le_temp tmp,--Bug 4760483
811 opi_dbi_conc_prog_run_log col
812 WHERE --gtv.transaction_source = 'INVENTORY' AND -- bug 4870029
813 gtv.journal_line_type = 'ITR'
814 and col.driving_table_code='GTV'
815 and col.etl_type= 'INVENTORY'
816 and col.load_type= 'INCR'
817 and gtv.ledger_id = tmp.ledger_id --Bug 4760483
818 and gtv.legal_entity_id = tmp.legal_entity_id
819 and gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
820 and gtv.organization_id = tmp. organization_id
821 And gtv.final_posting_date >= col.FROM_BOUND_DATE --Bug 4968995
822 And gtv.final_posting_date < col.TO_BOUND_DATE -- Bug 4968995
823 --And col.FROM_BOUND_DATE >=gtv.final_posting_date
824 --And col.TO_BOUND_DATE < gtv.final_posting_date
825 Group by transaction_id, accounted_flag
826 UNION ALL
827 select transaction_id,
828 sum(txn_base_value) base_transaction_value ,
829 accounted_flag
830 from gmf_transaction_valuation gtv,
831 opi_dbi_org_le_temp tmp --Bug 4760483
832 where --gtv.transaction_source = 'INVENTORY' and --bug 4870029
833 gtv.journal_line_type = 'ITR'
834 and gtv.ledger_id = tmp.ledger_id --Bug 4760483
835 and gtv.legal_entity_id = tmp.legal_entity_id
836 and gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
837 and gtv.organization_id = tmp. organization_id
838 AND gtv.accounted_flag ='D' --
839 Group by transaction_id, accounted_flag
840 ) gtv,
841 mtl_system_items msi_fm,
842 mtl_system_items msi_to,
843 mtl_parameters mp
844 WHERE mmt1.organization_id = mip.from_organization_id
845 AND mmt1.transfer_organization_id = mip.to_organization_id
846 AND mmt1.transaction_action_id in (15,12,22,21)
847 AND decode(mmt1.transaction_action_id,15,12,22,21,
848 mmt1.transaction_action_id) = mip.transaction_action_id
849 And mmt1.organization_id=mp.organization_id
850 and mp.process_enabled_flag = mip.owning_org_process_flag--make sure only logical collected incase of D-> P and P->D
851 AND gtv.transaction_id = mmt1.transaction_id
852 AND msi_fm.inventory_item_id = mmt1.inventory_item_id
853 AND msi_fm.organization_id = mip.from_organization_id
854 AND msi_to.inventory_item_id = mmt1.inventory_item_id
855 AND msi_to.organization_id = mip.to_organization_id
856 GROUP BY
857 mip.owning_organization_id,
858 mip.owning_org_process_flag, -- Bug fix: 5362465
859 mmt1.inventory_item_id,
860 DECODE (mip.owning_organization_id, msi_fm.organization_id, msi_fm.primary_uom_code
861 ,msi_to.organization_id, msi_to.primary_uom_code) ,
862 decode (mip.fob_point,
863 2, decode (mip.transaction_action_id,21, msi_fm.primary_uom_code,msi_to.primary_uom_code),
864 decode (mip.transaction_action_id,12, msi_to.primary_uom_code,msi_fm.primary_uom_code)) ,
865 trunc(mmt1.transaction_date))
866 GROUP BY
867 organization_id,
868 inventory_item_id,
869 primary_uom_code,
870 trx_date,
871 decode(process_flag,'N',1,2) -- Bug fix: 5362465
872 HAVING sum(value) <> 0 or sum(qty) <> 0 OR sum(draft_value) <> 0 OR sum(draft_qty) <> 0
873 OR (sum(draft_value) = 0 AND sum(draft_qty) = 0); -- Bug 4968293
874
875 if OPI_DBI_RPT_UTIL_PKG.g_pk_uom_conversion <>1 then
876 Raise UOM_CONV_ERROR;
877 end if;
878 commit;
879
880 l_debug_msg := 'Inserted into staging table opi_dbi_intransit_stg for OPM Post R12 - ' || SQL%ROWCOUNT || ' rows. ';
881 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
882
883 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
884 l_debug_msg := 'End of GET_INTRANSIT_ACTIVITY';
885 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
886 END IF;
887
888 EXCEPTION
889 WHEN UOM_CONV_ERROR then
890 l_debug_msg := 'UOM conversion not found ' || SQLcode || ' - ' ||SQLERRM;
891 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
892 RAISE;
893
894 WHEN OTHERS THEN
895 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
896 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
897 RAISE;
898 END GET_INTRANSIT_ACTIVITY;
899
900 -------------------------------------------------------------------------------
901
902 PROCEDURE GET_WIP_ACTIVITY
903 IS
904 l_stmt_num NUMBER;
905 l_debug_msg VARCHAR2(1000);
906 l_proc_name VARCHAR2 (60);
907 l_debug_mode VARCHAR2(1);
908 l_module_name VARCHAR2(30);
909 BEGIN
910 l_proc_name := 'GET_WIP_ACTIVITY';
911 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
912 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
913
914 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
915 l_debug_msg := 'Start of GET_WIP_ACTIVITY';
916 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
917 END IF;
918
919 l_stmt_num :=10;
920 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
921 l_debug_msg := 'Inserting data Into OPI_DBI_WIP_STG for ODM';
922 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
923 END IF;
924
925 INSERT /*+ append */
926 INTO OPI_DBI_WIP_STG (
927 organization_id,
928 inventory_item_id,
929 transaction_date,
930 primary_uom,
931 wip_value_b,
932 wip_value_b_draft,
933 source,
934 creation_date,
935 last_update_date,
936 created_by,
937 last_updated_by,
938 last_update_login)
939 SELECT
940 wip_activity.organization_id,
941 wip_activity.inventory_item_id,
942 decode (sign (transaction_date - g_global_start_date),
943 -1, g_global_start_date,
944 transaction_date) transaction_date,
945 msi.primary_uom_code,
946 sum (wip_value) wip_value_b,
947 0 wip_value_b_draft,
948 1,
949 sysdate,
950 sysdate,
951 g_user_id,
952 g_user_id,
953 g_login_id
954 FROM
955 (-- Added the hint to fix bug #3223207
956 SELECT /*+ index(mta, mtl_transaction_accounts_n1) */
957 mta.organization_id organization_id,
958 we.primary_item_id inventory_item_id,
959 trunc (mta.transaction_date) transaction_date,
960 sum (nvl (mta.base_transaction_value,0)) wip_value
961 FROM
962 mtl_transaction_accounts mta,
963 Opi_dbi_conc_prog_run_log prl,
964 wip_entities we
965 WHERE
966 prl.driving_table_code = 'MMT'
967 AND prl.load_type = 'INCR'
968 AND prl.etl_type = 'INVENTORY'
969 AND prl.bound_level_entity_code = 'ORGANIZATION'
970 AND prl.bound_level_entity_id = mta.organization_id
971 AND mta.transaction_source_type_id = 5
972 AND mta.accounting_line_type = 7
973 AND mta.transaction_id >= prl.from_bound_id
974 AND mta.transaction_id < to_bound_id
975 AND we.wip_entity_id = mta.transaction_source_id
976 AND we.entity_type in (1, 2, 3, 4, 5, 8)
977 AND we.primary_item_id is not null
978 GROUP BY
979 mta.organization_id,
980 we.primary_item_id,
981 mta.transaction_date
982 UNION ALL
983 SELECT
984 wta.organization_id organization_id,
985 we.primary_item_id inventory_item_id,
986 trunc (wta.transaction_date) transaction_date,
987 sum (nvl (wta.base_transaction_value,0)) wip_value
988 FROM
989 wip_transaction_accounts wta,
990 Opi_dbi_conc_prog_run_log prl,
991 wip_entities we
992 WHERE
993 prl.driving_table_code = 'WTA'
994 AND prl.load_type = 'INCR'
995 AND prl.etl_type = 'INVENTORY'
996 AND wta.accounting_line_type = 7
997 --AND prl.bound_level_entity_code = 'ORGANIZATION'
998 --AND prl.bound_level_entity_id = wta.organization_id
999 AND wta.transaction_id >= prl.from_bound_id
1000 AND wta.transaction_id < prl.to_bound_id
1001 AND we.wip_entity_id = wta.wip_entity_id
1002 AND we.entity_type in (1, 2, 3, 4, 5, 8)
1003 AND we.primary_item_id is not null
1004 GROUP BY
1005 wta.organization_id,
1006 we.primary_item_id,
1007 wta.transaction_date
1008 ) wip_activity,
1009 mtl_system_items_b msi
1010 WHERE msi.organization_id = wip_activity.organization_id
1011 AND wip_activity.inventory_item_id = msi.inventory_item_id
1012 GROUP BY
1013 wip_activity.organization_id,
1014 wip_activity.inventory_item_id,
1015 transaction_date,
1016 msi.primary_uom_code
1017 HAVING sum (wip_value) <> 0;
1018
1019 l_debug_msg := 'Inserted into staging table OPI_DBI_WIP_STG for ODM - ' || SQL%ROWCOUNT || ' rows. ';
1020 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1021
1022 commit;
1023 l_stmt_num :=20;
1024 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1025 l_debug_msg := 'Inserting data Into OPI_DBI_WIP_STG for OPM';
1026 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1027 END IF;
1028
1029
1030 INSERT /*+ append */
1031 INTO OPI_DBI_WIP_STG (
1032 organization_id,
1033 inventory_item_id,
1034 transaction_date,
1035 primary_uom,
1036 wip_value_b,
1037 wip_value_b_draft,
1038 source,
1039 creation_date,
1040 last_update_date,
1041 created_by,
1042 last_updated_by,
1043 last_update_login)
1044 SELECT
1045 wip_activity.organization_id,
1046 wip_activity.inventory_item_id,
1047 decode (sign (transaction_date - g_global_start_date),
1048 -1, g_global_start_date,
1049 transaction_date) transaction_date,
1050 msi.primary_uom_code,
1051 sum (wip_value_b) wip_value_b,
1052 sum (wip_value_b_draft) wip_value_b_draft,
1053 2,
1054 sysdate,
1055 sysdate,
1056 g_user_id,
1057 g_user_id,
1058 g_login_id
1059 FROM
1060 (
1061 SELECT
1062 gmdtl.organization_id,
1063 gmdtl.inventory_item_id,
1064 trunc (gtv.transaction_date) transaction_date,
1065 sum (gtv.txn_base_value * decode (gtv.line_type,
1066 1, decode(gtv.inventory_item_id,gmdtl.inventory_item_id,1,0),
1067 -1, gmdtl.cost_alloc,
1068 2, gmdtl.cost_alloc,
1069 gmdtl.cost_alloc)) wip_value_b,
1070 0 wip_value_b_draft
1071 FROM
1072 gmf_transaction_valuation gtv,
1073 opi_dbi_org_le_temp tmp,--Bug 4768058
1074 gme_material_details gmdtl,
1075 opi_dbi_conc_prog_run_log prl
1076 WHERE prl.driving_table_code = 'GTV'
1077 AND prl.load_type = 'INCR'
1078 AND prl.etl_type = 'INVENTORY'
1079 AND gtv.journal_line_type = 'WIP'
1080 and gtv.ledger_id = tmp.ledger_id --Bug 4768058
1081 and gtv.legal_entity_id = tmp.legal_entity_id
1082 and gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
1083 and gtv.organization_id = tmp. organization_id
1084 AND gtv.final_posting_date >= prl.from_bound_date
1085 AND gtv.final_posting_date < prl.to_bound_date
1086 AND gtv.accounted_flag IS NULL
1087 AND gtv.doc_id = gmdtl.batch_id
1088 AND gmdtl.line_type = 1
1089 GROUP BY
1090 gmdtl.organization_id,
1091 gmdtl.inventory_item_id,
1092 trunc (gtv.transaction_date)
1093 UNION ALL
1094 SELECT
1095 gmdtl.organization_id,
1096 gmdtl.inventory_item_id,
1097 trunc (gtv.transaction_date) transaction_date,
1098 0 wip_value_b,
1099 sum (gtv.txn_base_value * decode (gtv.line_type,
1100 1, decode(gtv.inventory_item_id,gmdtl.inventory_item_id,1,0),
1101 -1, gmdtl.cost_alloc,
1102 2, gmdtl.cost_alloc,
1103 gmdtl.cost_alloc)) wip_value_b_draft
1104 FROM
1105 gmf_transaction_valuation gtv,
1106 opi_dbi_org_le_temp tmp,--Bug 4760483
1107 gme_material_details gmdtl
1108 WHERE gtv.journal_line_type = 'WIP'
1109 AND gtv.accounted_flag = 'D' --
1110 AND gtv.doc_id = gmdtl.batch_id
1111 AND gmdtl.line_type = 1
1112 and gtv.ledger_id = tmp.ledger_id --Bug 4760483
1113 and gtv.legal_entity_id = tmp.legal_entity_id
1114 and gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
1115 and gtv.organization_id = tmp. organization_id
1116 GROUP BY
1117 gmdtl.organization_id,
1118 gmdtl.inventory_item_id,
1119 trunc (gtv.transaction_date)
1120 ) wip_activity,
1121 mtl_system_items_b msi
1122 WHERE msi.organization_id = wip_activity.organization_id
1123 AND wip_activity.inventory_item_id = msi.inventory_item_id
1124 GROUP BY
1125 wip_activity.organization_id,
1126 wip_activity.inventory_item_id,
1127 transaction_date,
1128 msi.primary_uom_code
1129 HAVING sum (wip_value_b) <> 0
1130 or sum (wip_value_b_draft) <> 0;
1131
1132 l_debug_msg := 'Inserted into staging table OPI_DBI_WIP_STG for OPM - ' || SQL%ROWCOUNT || ' rows. ';
1133 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1134
1135 COMMIT;
1136
1137 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1138 l_debug_msg := 'End of GET_WIP_ACTIVITY';
1139 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1140 END IF;
1141 EXCEPTION
1142 WHEN OTHERS THEN
1143 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
1144 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1145 RAISE;
1146 END GET_WIP_ACTIVITY;
1147
1148
1149 -------------------------------------------------------------------------------
1150
1151 PROCEDURE GET_NET_ACTIVITY
1152 IS
1153 l_stmt_num NUMBER;
1154 l_debug_msg VARCHAR2(1000);
1155 l_proc_name VARCHAR2 (60);
1156 l_debug_mode VARCHAR2(1);
1157 l_module_name VARCHAR2(30);
1158 BEGIN
1159 l_proc_name := 'GET_NET_ACTIVITY';
1160 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1161 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
1162
1163 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1164 l_debug_msg := 'Start of GET_NET_ACTIVITY';
1165 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1166 END IF;
1167
1168 l_stmt_num := 10;
1169 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1170 l_debug_msg := 'Calling On Hand Collection ';
1171 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1172 END IF;
1173
1174 OPI_DBI_INV_VALUE_INCR_PKG.get_onhand_activity;
1175
1176 l_stmt_num := 20;
1177 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1178 l_debug_msg := 'Calling Intransit Collection ';
1179 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1180 END IF;
1181
1182 OPI_DBI_INV_VALUE_INCR_PKG.get_intransit_activity;
1183
1184 l_stmt_num := 30;
1185 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1186 l_debug_msg := 'Calling WIP Inventory Collection';
1187 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1188 END IF;
1189
1190 OPI_DBI_INV_VALUE_INCR_PKG.get_wip_activity;
1191
1192
1193 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1194 l_debug_msg := 'End of GET_NET_ACTIVITY';
1195 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1196 END IF;
1197 EXCEPTION
1198 WHEN OTHERS THEN
1199 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
1200 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1201 RAISE;
1202 END GET_NET_ACTIVITY;
1203
1204 -------------------------------------------------------------------------------
1205
1206 PROCEDURE MERGE_INTO_SUMMARY
1207 IS
1208 l_stmt_num NUMBER;
1209 l_debug_msg VARCHAR2(1000);
1210 l_proc_name VARCHAR2 (60);
1211 l_debug_mode VARCHAR2(1);
1212 l_module_name VARCHAR2(30);
1213 BEGIN
1214 l_proc_name := 'MERGE_INTO_SUMMARY';
1215 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1216 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
1217
1218 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1219 l_debug_msg := 'Start of MERGE_INTO_SUMMARY';
1220 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1221 END IF;
1222
1223 l_stmt_num := 10;
1224 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1225 l_debug_msg := 'Inserting data into OPI_DBI_INV_VALUE_F';
1226 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1227 END IF;
1228
1229 MERGE INTO OPI_DBI_INV_VALUE_F base
1230 USING
1231 (SELECT /*+ index (rate OPI_DBI_CONVERSION_RATES_N2) */
1232 NULL operating_unit,
1233 s.organization_id,
1234 s.subinventory_code,
1235 s.inventory_item_id,
1236 s.transaction_date,
1237 s.onhand_qty,
1238 s.intransit_qty,
1239 s.primary_uom,
1240 s.onhand_value_b,
1241 s.intransit_value_b,
1242 s.wip_value_b,
1243 rate.conversion_rate,
1244 rate.sec_conversion_rate,
1245 s.ONHAND_QTY_DRAFT,
1246 s.INTRANSIT_QTY_DRAFT,
1247 s.ONHAND_VALUE_B_DRAFT,
1248 s.INTRANSIT_VALUE_B_DRAFT,
1249 s.WIP_VALUE_B_DRAFT,
1250 source
1251 FROM
1252 (SELECT /*+ index (msi MTL_SYSTEM_ITEMS_B_U1) */
1253 activity.organization_id,
1254 activity.subinventory_code,
1255 activity.inventory_item_id,
1256 activity.transaction_date,
1257 nvl(SUM(onhand_qty),0) onhand_qty,
1258 nvl(SUM(intransit_qty),0) intransit_qty,
1259 MIN(msi.primary_uom_code) primary_uom,
1260 nvl(SUM(onhand_value_b),0) onhand_value_b,
1261 nvl(SUM(intransit_value_b),0) intransit_value_b,
1262 nvl(SUM(wip_value_b),0) wip_value_b,
1263 nvl(SUM(onhand_qty_draft),0) onhand_qty_draft,
1264 nvl(SUM(INTRANSIT_QTY_DRAFT),0) INTRANSIT_QTY_DRAFT,
1265 nvl(SUM(ONHAND_VALUE_B_DRAFT),0) ONHAND_VALUE_B_DRAFT,
1266 nvl(SUM(INTRANSIT_VALUE_B_DRAFT),0) INTRANSIT_VALUE_B_DRAFT,
1267 nvl(SUM(WIP_VALUE_B_DRAFT),0) WIP_VALUE_B_DRAFT,
1268 activity.source
1269 from
1270 (SELECT
1271 organization_id,
1272 subinventory_code,
1273 inventory_item_id,
1274 transaction_date,
1275 onhand_qty,
1276 0 intransit_qty,
1277 primary_uom,
1278 onhand_value_b,
1279 0 intransit_value_b,
1280 0 wip_value_b,
1281 onhand_qty_draft,
1282 0 intransit_qty_draft,
1283 onhand_value_b_draft onhand_value_b_draft,
1284 0 intransit_value_b_draft,
1285 0 wip_value_b_draft,
1286 source
1287 FROM OPI_DBI_ONHAND_STG
1288 UNION ALL
1289 select
1290 organization_id,
1291 NULL,
1292 inventory_item_id,
1293 transaction_date,
1294 0 onhand_qty,
1295 intransit_qty,
1296 primary_uom,
1297 0 onhand_value_b,
1298 intransit_value_b,
1299 0 wip_value_b,
1300 0 onhand_qty_draft,
1301 intransit_qty_draft intransit_qty_draft,
1302 0 onhand_value_b_draft,
1303 intransit_value_draft_b intransit_value_b_draft,
1304 0 wip_value_b_draft,
1305 source
1306 from OPI_DBI_INTRANSIT_STG
1307 UNION ALL
1308 select
1309 organization_id,
1310 NULL,
1311 inventory_item_id,
1312 transaction_date,
1313 0 onhand_qty,
1314 0 intransit_qty,
1315 primary_uom,
1316 0 onhand_value_b,
1317 0 intransit_value_b,
1318 wip_value_b,
1319 0 onhand_qty_draft,
1320 0 intransit_qty_draft,
1321 0 onhand_value_b_draft,
1322 0 intransit_value_b_draft,
1323 wip_value_b_draft,
1324 source
1325 from opi_dbi_wip_stg
1326 ) activity,
1327 mtl_system_items msi
1328 WHERE activity.organization_id = msi.organization_id
1329 AND activity.inventory_item_id =msi.inventory_item_id
1330 group by
1331 activity.organization_id,
1332 activity.subinventory_code,
1333 activity.inventory_item_id,
1334 activity.transaction_date,
1335 activity.source
1336 ) s,
1337 (select
1338 organization_id,
1339 transaction_date,
1340 conversion_rate,
1341 sec_conversion_rate
1342 from opi_dbi_conversion_rates
1343 ) rate
1344 where s.organization_id = rate.organization_id
1345 and s.transaction_date = rate.transaction_date
1346 ) stg
1347 ON
1348 (base.organization_id = stg.organization_id and
1349 base.inventory_item_id = stg.inventory_item_id and
1350 base.transaction_date = stg.transaction_date and
1351 nvl(base.subinventory_code,-1) = nvl(stg.subinventory_code,-1) and
1352 base.source = stg.source
1353 )
1354 WHEN matched THEN
1355 update set
1356 base.onhand_qty = base.onhand_qty + stg.onhand_qty - base.onhand_qty_draft + stg.onhand_qty_draft,
1357 base.intransit_qty = base.intransit_qty + stg.intransit_qty - base.intransit_qty_draft + stg.intransit_qty_draft,
1358 base.onhand_value_b = base.onhand_value_b + stg.onhand_value_b - base.onhand_value_b_draft + stg.onhand_value_b_draft,
1359 base.intransit_value_b = base.intransit_value_b + stg.intransit_value_b - base.intransit_value_b_draft + stg.intransit_value_b_draft,
1360 base.wip_value_b = base.wip_value_b + stg.wip_value_b - base.wip_value_b_draft + stg.wip_value_b_draft,
1361 base.onhand_qty_draft = stg.onhand_qty_draft ,
1362 base.intransit_qty_draft = stg.intransit_qty_draft,
1363 base.onhand_value_b_draft= stg.onhand_value_b_draft,
1364 base.intransit_value_b_draft= stg.intransit_value_b_draft ,
1365 base.wip_value_b_draft = stg.wip_value_b_draft,
1366 base.last_update_date = sysdate,
1367 base.last_update_login = g_login_id,
1368 base.last_updated_by = g_user_id
1369 WHEN not matched THEN
1370 insert (operating_unit_id,
1371 organization_id,
1372 subinventory_code,
1373 inventory_item_id,
1374 transaction_date,
1375 onhand_qty,
1376 intransit_qty,
1377 primary_uom,
1378 onhand_value_b,
1379 intransit_value_b,
1380 wip_value_b,
1381 conversion_rate,
1382 sec_conversion_rate,
1383 ONHAND_QTY_DRAFT,
1384 INTRANSIT_QTY_DRAFT,
1385 ONHAND_VALUE_B_DRAFT,
1386 INTRANSIT_VALUE_B_DRAFT,
1387 WIP_VALUE_B_DRAFT,
1388 source,
1389 created_by,
1390 last_update_login,
1391 creation_date,
1392 last_updated_by,
1393 last_update_date
1394 )
1395 values (stg.operating_unit,
1396 stg.organization_id,
1397 nvl(stg.subinventory_code,-1),
1398 stg.inventory_item_id,
1399 stg.transaction_date,
1400 stg.onhand_qty+stg.onhand_qty_draft,
1401 stg.intransit_qty+stg.intransit_qty_draft,
1402 stg.primary_uom,
1403 stg.onhand_value_b+stg.onhand_value_b_draft,
1404 stg.intransit_value_b+stg.intransit_value_b_draft,
1405 stg.wip_value_b+stg.wip_value_b_draft,
1406 stg.conversion_rate,
1407 stg.sec_conversion_rate,
1408 stg.onhand_qty_draft,
1409 stg.intransit_qty_draft,
1410 stg.onhand_value_b_draft,
1411 stg.intransit_value_b_draft,
1412 stg.wip_value_b_draft,
1413 stg.Source,
1414 g_user_id,
1415 g_login_id,
1416 sysdate,
1417 g_user_id,
1418 sysdate
1419 );
1420
1421 commit;
1422
1423 l_debug_msg := 'Inserted into staging table OPI_DBI_INV_VALUE_F - ' || SQL%ROWCOUNT || ' rows. ';
1424 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1425
1426
1427 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1428 l_debug_msg := 'End of MERGE_INTO_SUMMARY';
1429 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1430 END IF;
1431
1432 EXCEPTION
1433 WHEN OTHERS THEN
1434 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
1435 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1436 RAISE;
1437 END MERGE_INTO_SUMMARY;
1438
1439 -------------------------------------------------------------------------------
1440
1441 PROCEDURE DISCRETE_REFRESH
1442 IS
1443 OPI_DBI_EXCEPTION EXCEPTION;
1444
1445 PRAGMA EXCEPTION_INIT (OPI_DBI_EXCEPTION , -20002);
1446
1447 l_stmt_num NUMBER;
1448 l_debug_msg VARCHAR2(1000);
1449 l_proc_name VARCHAR2 (60);
1450 l_debug_mode VARCHAR2(1);
1451 l_module_name VARCHAR2(30);
1452 errbuf VARCHAR2(30);
1453 retcode VARCHAR2(30);
1454 BEGIN
1455 l_proc_name := 'DISCRETE_REFRESH';
1456 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1457 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
1458
1459 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1460 l_debug_msg := 'Start of Discrete_Refresh';
1461 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1462 END IF;
1463
1464 l_stmt_num := 10;
1465 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1466 l_debug_msg := 'Calling Clean staging table PRE INIT';
1467 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1468 END IF;
1469
1470 OPI_DBI_INV_VALUE_INCR_PKG.clean_staging_table;
1471 -- cleans staging table.
1472
1473 -- sets bounds for MMT, GTV and WTA Tables accordingly.
1474 l_stmt_num := 20;
1475 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1476 l_debug_msg := 'Setting up bound for Inventory Incremental Load ';
1477 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1478 END IF;
1479
1480
1481 OPI_DBI_BOUNDS_PKG.maintain_opi_dbi_logs('INVENTORY','INCR');
1482
1483 l_stmt_num := 25;
1484 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1485 l_debug_msg := 'Calling OPI_DBI_BOUNDS_PKG.load_opm_org_ledger_data ';
1486 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1487 END IF;
1488
1489 OPI_DBI_BOUNDS_PKG.load_opm_org_ledger_data;
1490
1491 l_stmt_num := 30;
1492 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1493 l_debug_msg := 'Collecting OPI , OPM Incremental Load Inventory Quantities and Values';
1494 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1495 END IF;
1496
1497 OPI_DBI_INV_VALUE_INCR_PKG.Get_Net_Activity;
1498 -- even this procedure has a commit as it has two extracts and each one run in parallel append mode.
1499 commit;
1500
1501 l_stmt_num :=40;
1502 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1503 l_debug_msg := 'Checking for Conversion Rate. ';
1504 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1505 END IF;
1506
1507 -- this api prints the report in case there are conversion rates missing.
1508 -- it returns -1 in case there are missing conversion rates as well.
1509 IF (OPI_DBI_INV_VALUE_UTL_PKG.Get_Conversion_Rate(errbuf, retcode) = -1) THEN
1510 OPI_DBI_BOUNDS_PKG.write (g_pkg_name, l_proc_name,l_stmt_num,'Missing currency rate.');
1511 RAISE OPI_DBI_EXCEPTION;
1512 END IF;
1513 COMMIT;
1514
1515
1516 OPI_DBI_INV_VALUE_INCR_PKG.Merge_Into_Summary;
1517 commit;
1518
1519 l_stmt_num := 50;
1520 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1521 l_debug_msg := 'Finished Collecting OPI, OPM Incremental Load Inventory Quantities and Values';
1522 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1523 END IF;
1524
1525 l_stmt_num := 60;
1526 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1527 l_debug_msg := 'Calling Clean staging table PRE INIT';
1528 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1529 END IF;
1530
1531 OPI_DBI_INV_VALUE_INCR_PKG.clean_staging_table;
1532
1533
1534 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1535 l_debug_msg := 'End of Discrete_Refresh';
1536 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1537 END IF;
1538
1539 EXCEPTION
1540 WHEN OPI_DBI_EXCEPTION THEN
1541 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
1542 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1543 RAISE;
1544 WHEN OTHERS THEN
1545 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
1546 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1547 RAISE;
1548 END DISCRETE_REFRESH;
1549 -------------------------------------------------------------------------------
1550
1551 PROCEDURE REFRESH(
1552 errbuf IN OUT NOCOPY VARCHAR2,
1553 retcode IN OUT NOCOPY VARCHAR2
1554 )
1555 IS
1556 l_stmt_num NUMBER;
1557 l_debug_msg VARCHAR2(1000);
1558 l_proc_name VARCHAR2 (60);
1559 l_debug_mode VARCHAR2(1);
1560 l_module_name VARCHAR2(30);
1561 l_uncost_trx BOOLEAN;
1562
1563 BEGIN
1564 l_proc_name := 'REFRESH';
1565 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1566 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
1567
1568
1569 l_debug_msg := 'Start of Incremental Load for Inventory ';
1570 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1571
1572 l_stmt_num := 5;
1573 if (bis_collection_utilities.setup(p_object_name => 'OPI_DBI_INV_VALUE_F') = false) then
1574 raise_application_error(-20000,errbuf);
1575 end if;
1576
1577 l_stmt_num :=10;
1578 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1579 l_debug_msg := 'Checking For Global Parameters';
1580 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1581 END IF;
1582
1583 -- checks for GSD, primary and sec currency rate types, schema name for OPI
1584 -- raises exception in case there is an error
1585 OPI_DBI_INV_VALUE_INCR_PKG.check_incr_load_setup;
1586
1587 l_stmt_num :=20;
1588 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1589 l_debug_msg := 'Calling Procedure Discrete Refersh ';
1590 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1591 END IF;
1592
1593 OPI_DBI_INV_VALUE_INCR_PKG.Discrete_Refresh; -- No changes for R12
1594 commit;
1595
1596 l_stmt_num :=30;
1597 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1598 l_debug_msg := 'Calling Procedure Period close Adjustment ';
1599 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1600 END IF;
1601
1602 OPI_DBI_INV_CPCS_PKG.Run_Period_Close_Adjustment(errbuf, retcode);
1603
1604 l_stmt_num :=40;
1605 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1606 l_debug_msg := 'Checking for uncosted transactions. ';
1607 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1608 END IF;
1609
1610 l_uncost_trx := OPI_DBI_BOUNDS_PKG.bounds_uncosted('INVENTORY', 'INCR');
1611
1612 IF l_uncost_trx =TRUE then
1613 retcode := 1;
1614 l_debug_msg := 'Warning: There are some uncosted transactions. ';
1615 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1616 END IF;
1617
1618 l_stmt_num :=50;
1619 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1620 l_debug_msg := ' Printing Log Bound. ';
1621 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1622 END IF;
1623
1624 OPI_DBI_BOUNDS_PKG.print_opi_org_bounds('INVENTORY', 'INCR');
1625
1626 l_stmt_num :=60;
1627 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1628 l_debug_msg := 'Updating Log with success ';
1629 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1630 END IF;
1631
1632 OPI_DBI_BOUNDS_PKG.set_load_successful('INVENTORY', 'INCR') ;
1633
1634
1635
1636 l_debug_msg := 'End of Incremental Load for Inventory ';
1637 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1638
1639 BIS_COLLECTION_UTILITIES.WRAPUP(
1640 p_status => TRUE,
1641 p_count => 0,
1642 p_message => 'Successfully refreshed inventory value base table at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
1643 );
1644
1645 RETURN;
1646 EXCEPTION
1647 WHEN OTHERS THEN
1648 retcode := -1;
1649 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
1650 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1651
1652 BIS_COLLECTION_UTILITIES.WRAPUP(
1653 p_status => FALSE,
1654 p_message => 'Failed in Incremental Load of inventory value base table.'
1655 );
1656 RAISE;
1657
1658 END REFRESH;
1659
1660 END OPI_DBI_INV_VALUE_INCR_PKG;