1 Package Body OPI_DBI_INV_VALUE_INIT_PKG AS
2 /*$Header: OPIDIVIB.pls 120.31 2008/03/07 09:18:26 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_inception_date DATE;
8 g_global_start_date DATE;
9 g_global_curr_code VARCHAR2(10);
10 g_global_sec_curr_code VARCHAR2(10);
11 g_global_rate_type VARCHAR2(32);
12 g_global_sec_rate_type VARCHAR2(32);
13 g_R12_date DATE;
14 g_pkg_name CONSTANT VARCHAR2(200) := 'OPI_DBI_INV_VALUE_INIT_PKG';
15 g_opi_schema VARCHAR2(10);
16 --OPI_DBI_RPT_UTIL_PKG.g_pk_uom_conversion number;
17 -- User Defined Exceptions
18
19 INITIALIZATION_ERROR EXCEPTION;
20 PRAGMA EXCEPTION_INIT (INITIALIZATION_ERROR, -20900);
21 UOM_CONV_ERROR EXCEPTION;
22 PRAGMA EXCEPTION_INIT (UOM_CONV_ERROR, -20901);
23
24 --RUN_FIRST_ETL
25 -- -->OPI_DBI_INV_VALUE_INIT_PKG.SEED_INV_TYPE_CODE
26 --
27 -- -->RUN_DISCRETE_FIRST_ETL
28 -- --->clean_staging_tables
29 -- --->OPI_DBI_BOUNDS_PKG.MAINTAIN_OPI_DBI_LOGS
30 -- --->EXTRACT_INVENTORY_TXN_QTY
31 -- --->EXTRACT_INVENTORY_TXN_VALUE
32 -- --->GET_INTRANSIT_INITIAL_LOAD
33 -- ---->> INTRANSIT_SETUP
34 --
35 -- -->OPI_DBI_INV_VALUE_OPM_INIT_PKG.RUN_OPM_FIRST_ETL
36 -- --->OPI_DBI_INV_VALUE_OPM_INCR_PKG.EXTRACT_OPM_DAILY_ACTIVITY
37 -- ---->>OPI_DBI_INV_VALUE_OPM_INCR_PKG.OPM_REFRESH
38 -- ----->>OPI_DBI_INV_VALUE_OPM_INCR_PKG.Get_OPM_Net_Activity
39 -- ------>>>REFRESH_ONH_LED_CURRENT
40 -- ------>>>REFRESH_RVAL_LED_CURRENT
41 -- ------>>>REFRESH_ITR_LED_CURRENT
42 -- ------>>>REFRESH_IOR_LED_CURRENT
43 -- ------>>>REFRESH_WIP_LED_CURRENT
44 -- ------>>>PUT_NET_ACTIVITY_TO_STG
45 -- -->GET_INCEPTION_INV_BALANCE
46 -- --->GET_ONHAND_BALANCE
47 -- --->GET_INTRANSIT_BALANCE
48 -- --->GET_WIP_BALANCE
49 -- --->COST_DISCRETE_INCEPTION_QTY
50 -- --->COST_OPM_INCEPTION_QUANTITY
51 -- -->GET_CONVERSION_RATE
52 -- -->MERGE_INITIAL_LOAD
53 -- -->clean_staging_tablesS
54 -- -->OPI_DBI_INV_CPCS_PKG.Run_Period_Close_Adjustment
55 -- -->OPI_DBI_BOUNDS_PKG.bounds_uncosted
56 -- -->OPI_DBI_BOUNDS_PKG.print_opi_org_bounds
57 -- -->OPI_DBI_BOUNDS_PKG.SET_LOAD_SUCCESSFUL
58
59
60 -- Design Highlights
61 -- 1. Data Flow is different in Initial and Incremental Loads. One of the
62 --main reasons for this is that we want to extract all our data once
63 --for activity collection and calculating beginning balance in initial load.
64 -- 2. In the initial load we extract quantity and value separately.
65 --Quantity for process and discrete organizations are extracted at
66 --once from MMt. Value from various tables like MTA, GTV and WTA
67 --are extracted together for process and discrete organizations.
68 --Other data is also extracted from WPB and MOQ is also
69 --extracted at the same time to ensure read consistency.
70 -- 3. For Value MTA is hit once to get Onhand as well as WIP Value
71 --because MTA is accessed for same transaction_id range for these
72 --two measures. This is the reason as why OPI_DBI_ONHAND_STG
73 --now has Onhand AND WIP value columns both.
74 -- 4. In the incremental load the quantity and value are extracted together
75 --once for discrete organization and then for process organizations.
76 -- 5. In initial load PUSH_TO_FACT_FLAG is used in order to distinguish
77 --between the records which are within the bounds and those which are
78 --outside the bound. Data is collected outside the bound in order to
79 --compute beginning balance. Consideration is taken to have extracts
80 --in same union all in order to maintain the read consistency.
81 -- 6. In the staging table and fact there are two columns for quantity and
82 --value measures. E.g. ONHAND_QTY and ONHAND_QTY_DRAFT. There is a
83 --difference the way data is hold in staging tables and in the fact.
84 --In the Staging table _DRAFT column stores data coming from MMT which
85 --are draft posted. The column without _DRAFT holds data coming from
86 --records posted to final ledger. But in the Fact _DRAFT contains same
87 --data but the column without _DRAFT holds sum of total
88 --final posted data and draft posted data.
89 -- 7. DRAFT columns are used only for process organizations.
90 --For discrete orgs these will hold zero/null values.
91 --Purpose of having DRAFT columns is to know the draft posted value in
92 --last run as incremental loads re-collect all the draft posted records.
93 -- 8. LOG table maintenance is centralized.
94 --There is one record for INVENTORY etl for each driving table
95 --i.e. MMT, GTV and WTA.
96 -- 9. For MMT bounds are based on transaction_id.
97 --Process stops at first uncosted txn id found.
98 --At the same time date is stamped for GTV upto which data from GTV
99 --is collected. From GTV data is selected based on
100 --final_posted_date < bound_date or accounted_flag = 'D'.
101 --So this is possible that there are some new transactions in between
102 --the time when quantity from MMT is extracted and value from GTV is
103 --extracted and are posted to Draft ledger. So the quantity and value
104 --could be little out of synch. But this would be corrected whenever next
105 -- incremental load is run.
106 -- 10. Similar to the point above, at the same time when first uncosted txn id
107 -- is found the max transaction_id from WTA is taken and this is set as
108 --bound for extraction from WTA. So it is possible that material txns
109 --collected and resource txns collected are not in synch as there could be
110 --few uncosted material txns.
111 -- 11. Data is collect from OPM old data model in case GSD < R12 installation
112 --date . This data is also used to rollback to the beginning balance.
113 -- 12. Inception To Date is GSD for all organizations.
114 --Beginning balance record will have transaction date as GSD.
115
116
117 -------------------------------------------------------------------------------
118 --This procedure will populate value for global variables
119 -- like GSD, currency codes and rates
120
121
122
123 PROCEDURE CHECK_INITIAL_LOAD_SETUP
124 IS
125 l_proc_name VARCHAR2 (40);
126 l_stmt_num NUMBER;
127 l_setup_good BOOLEAN;
128 l_status VARCHAR2(30) := NULL;
129 l_industry VARCHAR2(30) := NULL;
130 l_debug_msg VARCHAR2(200);
131 BEGIN
132 -- Initialization
133 l_proc_name := 'CHECK_INITIAL_LOAD_SETUP';
134 l_stmt_num := 0;
135
136 -- Check for the global start date setup.
137 -- These parameter must be set up prior to any DBI load.
138 g_global_start_date := trunc(bis_common_parameters.get_global_start_date);
139 g_global_curr_code := bis_common_parameters.get_currency_code;
140 g_global_sec_curr_code := bis_common_parameters.get_secondary_currency_code;
141 g_global_rate_type := bis_common_parameters.get_rate_type;
142 g_global_sec_rate_type := bis_common_parameters.get_secondary_rate_type;
143
144 IF (g_global_start_date IS NULL) THEN
145 l_debug_msg := 'Global start date is not defined';
146 RAISE INITIALIZATION_ERROR;
147 END IF;
148
149 IF (g_global_curr_code IS NULL) THEN
150 l_debug_msg := 'Global currency code is not defined';
151 RAISE INITIALIZATION_ERROR;
152 END IF;
153
154 IF (g_global_rate_type IS NULL) THEN
155 l_debug_msg := 'Global rate type is not defined';
156 RAISE INITIALIZATION_ERROR;
157 END IF;
158
159 IF (g_global_sec_curr_code IS NOT NULL AND g_global_sec_rate_type IS NULL) THEN
160 l_debug_msg := 'Global secondary rate type is not defined';
161 RAISE INITIALIZATION_ERROR;
162 END IF;
163
164 IF (g_global_sec_rate_type IS NOT NULL AND g_global_sec_curr_code IS NULL) THEN
165 l_debug_msg := 'Global secondary curr code is not defined';
166 RAISE INITIALIZATION_ERROR;
167 END IF;
168
169 l_setup_good := fnd_installation.get_app_info('OPI', l_status, l_industry, g_opi_schema);
170 IF (l_setup_good = FALSE OR g_opi_schema IS NULL) THEN
171 l_debug_msg := 'could not find OPI schema';
172 RAISE INITIALIZATION_ERROR;
173 END IF;
174 EXCEPTION
175 WHEN INITIALIZATION_ERROR THEN
176 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
177 RAISE;
178
179 WHEN OTHERS THEN
180 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
181 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
182 RAISE;
183
184 END CHECK_INITIAL_LOAD_SETUP;
185
186
187 -------------------------------------------------------------------------------
188 -- Truncates all the staging Tables, LOG Table and the FACT Tables as well.
189 -- As these are truncate statements this procedure does a commit.
190 -- Common/Misc Tables:
191 -- OPI_DBI_OPM_INV_LED_CURRENT
192 -- OPI_DBI_OPM_INV_STG
193 -- OPI_DBI_CONVERSION_RATES
194 -- OPI_DBI_INV_ITEM_COSTS_TMP
195 -- OPI_PMI_COST_PARAM_GTMP
196 -- OPI_DBI_INV_VALUE_LOG -- added as part of CPCS Change.
197 -- Note: OPI_DBI_INV_TYPE_CODES is not cleaned anywhere.
198 -- Intransit Tables:
199 -- OPI_DBI_INTR_SUP_TMP
200 -- OPI_DBI_INTR_MMT_TMP
201 -- OPI_DBI_INTR_MIP_TMP
202 -- OPI_DBI_INTRANSIT_STG
203 -- WIP Tables
204 -- OPI_DBI_WIP_STG
205 -- Onhand WIP Tables
206 -- OPI_DBI_ONH_QTY_STG
207 -- OPI_DBI_INV_BEG_STG
208 -- OPI_DBI_ONHAND_STG
209 -- IF p_stage = 'PRE_INIT' THEN
210 -- Cleanup the OPI_DBI_INV_VALUE_F
211 PROCEDURE clean_staging_tables(p_stage varchar2)
212 IS
213 l_stmt_num NUMBER;
214 l_debug_msg VARCHAR2(1000);
215 l_proc_name VARCHAR2 (60);
216 l_debug_mode VARCHAR2(1);
217 l_module_name VARCHAR2(30);
218 BEGIN
219 l_proc_name := 'clean_staging_tables';
220 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
221 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
222
223 l_stmt_num := 0;
224 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
225 l_debug_msg := 'Start of cleaning staging table';
226 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
227 END IF;
228
229 l_stmt_num := 10;
230 execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_OPM_INV_LED_CURRENT';
231
232 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
233 l_debug_msg := 'OPI_DBI_OPM_INV_LED_CURRENT table truncated';
234 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
235 END IF;
236
237 l_stmt_num := 20;
238 execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_OPM_INV_STG';
239
240 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
241 l_debug_msg := 'OPI_DBI_OPM_INV_STG table truncated';
242 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
243 END IF;
244
245 l_stmt_num := 30;
246 execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_CONVERSION_RATES';
247
248 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
249 l_debug_msg := 'OPI_DBI_CONVERSION_RATES table truncated';
250 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
251 END IF;
252
253 -- Added opi_dbi_inv_value_log truncate for CPCS. This table should not be truncated in incremental load.
254 -- This log table is only used in CPCS code line.
255 l_stmt_num := 40;
256 execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_INV_VALUE_LOG';
257
258 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
259 l_debug_msg := 'OPI_DBI_INV_VALUE_LOG table truncated';
260 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
261 END IF;
262
263 -- l_stmt_num := 40;
264 -- as we are not using OPM costing API we need not truncate this table
265 -- execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_INV_ITEM_COSTS_TMP';
266
267 -- l_stmt_num := 50;
268 -- not required to be truncated. used only in old code
269 -- execute immediate 'truncate table ' || g_opi_schema || '.OPI_PMI_COST_PARAM_GTMP';
270
271 l_stmt_num := 60;
272 execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_INTR_SUP_TMP';
273
274 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
275 l_debug_msg := 'OPI_DBI_INTR_SUP_TMP table truncated.';
276 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
277 END IF;
278
279 l_stmt_num := 70;
280 execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_INTR_MMT_TMP';
281
282 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
283 l_debug_msg := 'OPI_DBI_INTR_MMT_TMP table truncated.';
284 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg);
285 END IF;
286
287 l_stmt_num := 80;
288 execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_INTR_MIP_TMP';
289
290 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
291 l_debug_msg := 'OPI_DBI_INTR_MIP_TMP table truncated.';
292 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
293 END IF;
294
295 l_stmt_num := 90;
296 execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_INTRANSIT_STG';
297
298 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
299 l_debug_msg := 'OPI_DBI_INTRANSIT_STG table truncated.';
300 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
301 END IF;
302
303 l_stmt_num := 100;
304 execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_WIP_STG';
305
306 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
307 l_debug_msg := 'OPI_DBI_WIP_STG table truncated.';
308 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
309 END IF;
310
311 l_stmt_num := 110;
312 execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_ONH_QTY_STG';
313
314 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
315 l_debug_msg := 'OPI_DBI_ONH_QTY_STG table truncated.';
316 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
317 END IF;
318
319 l_stmt_num := 120;
320 execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_INV_BEG_STG';
321
322 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
323 l_debug_msg := 'OPI_DBI_INV_BEG_STG table truncated.';
324 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
325 END IF;
326
327 l_stmt_num := 130;
328 execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_ONHAND_STG';
329
330 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
331 l_debug_msg := 'OPI_DBI_ONHAND_STG table truncated.';
332 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
333 END IF;
334
335 IF p_stage = 'PRE_INIT' THEN
336
337 l_stmt_num := 150;
338 execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_INV_VALUE_F';
339
340 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
341 l_debug_msg := 'OPI_DBI_INV_VALUE_F table truncated.';
342 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
343 END IF;
344
345 END IF;
346 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
347 l_debug_msg := 'End of cleaning staging table';
348 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
349 END IF;
350 EXCEPTION
351 WHEN OTHERS THEN
352 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
353 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
354 RAISE;
355 END clean_staging_tables;
356
357
358 -------------------------------------------------------------------------------
359
360 PROCEDURE COST_DISCRETE_INCEPTION_QTY
361 IS
362 l_stmt_num NUMBER;
363 l_debug_msg VARCHAR2(1000);
364 l_proc_name VARCHAR2 (60);
365 l_debug_mode VARCHAR2(1);
366 l_module_name VARCHAR2(30);
367
368 BEGIN
369 l_proc_name := 'COST_DISCRETE_INCEPTION_QTY';
370 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
371 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
372
373 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
374 l_debug_msg := 'Entered Into COST_DISCRETE_INCEPTION_QTY ';
375 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
376 END IF;
377
378
379 -- Get costs as of the inception date.
380 --
381 -- For standard costing orgs, get the cost as of the
382 -- last cost update prior to the global start date.
383 --
384 -- For layer costing orgs, get the first transaction from MMT after the
385 -- global start date for each org, item, cost group and pick the
386 -- prior cost of the transaction.
387 --
388 -- The cost method for all orgs is a non-null column of the
389 -- mtl_parameters table.
390 --
391 -- Since we want cost group information, use the non-null default
392 -- cost group id from mtl_parameters for standard costing orgs. For
393 -- layer costing orgs, get the cost group from MMT and if that is
394 -- null, then replace it with default cost group.
395
396 l_stmt_num :=10;
397 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
398 l_debug_msg := 'Inserting data into OPI_DBI_INV_BEG_STG from inception date less than gsd';
399 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
400 l_debug_msg := 'for standard costing organization';
401 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
402 END IF;
403
404
405 UPDATE OPI_DBI_INV_BEG_STG fact
406 SET (onhand_value_b ,intransit_value_b) =
407 (SELECT /*+ ordered use_hash(csc2, csc) parallel(csc2) parallel(csc)*/
408 -- ideally max is not required as standard cost
409 -- revision date is timestamp.
410 max(csc.standard_cost) *onhand_qty onhand_value_b,
411 max(csc.standard_cost) *intransit_qty intransit_value_b
412 FROM (
413 SELECT /*+ use_hash(p csc) parallel(csc)
414 parallel(mtl_parameters) */
415 csc.organization_id,
416 csc.inventory_item_id,
417 -- this is a a timestamp hence max would
418 -- give unique record.
419 max(standard_cost_revision_date) standard_cost_revision_date,
420 p.primary_cost_method cost_method,
421 NULL cost_group_id -- RS: Bug fix 5219487 p.default_cost_group_id cost_group_id
422 FROM mtl_parameters p,
423 cst_standard_costs csc
424 -- not using <= below because txns are
425 -- collected from GSD onward. hence if there is
426 -- any cost update as of GSD additional 24 txns
427 -- will come in separately.
428 WHERE standard_cost_revision_date < g_global_start_date
429 AND p.primary_cost_method = 1
430 AND p.organization_id = csc.organization_id
431 GROUP BY csc.organization_id,
432 csc.inventory_item_id,
433 p.primary_cost_method,
434 p.default_cost_group_id
435 ) csc2,
436 cst_standard_costs csc
437 WHERE csc.organization_id = csc2.organization_id
438 AND csc.inventory_item_id = csc2.inventory_item_id
439 AND csc.standard_cost_revision_date = csc2.standard_cost_revision_date
440 and fact.organization_id = csc2.organization_id
441 and fact.inventory_item_id =csc2.inventory_item_id
442 -- and fact.cost_group_id =csc2.cost_group_id -- RS: Bug fix 5219487
443 -- and fact.cost_method =csc.cost_method
444 GROUP BY csc.organization_id,
445 csc.inventory_item_id,
446 csc2.cost_method,
447 csc2.cost_group_id)
448 where ( nvl(fact.onhand_qty,0) <> 0 or nvl(fact.intransit_qty,0) <> 0 );
449
450 l_debug_msg := 'Updated into staging table OPI_DBI_INV_BEG_STG for ODM - ' || SQL%ROWCOUNT || ' rows. ';
451 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
452
453 l_stmt_num := 20;
454 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
455 l_debug_msg := 'Updated staging table OPI_DBI_INV_BEG_STG from inception date greater than gsd';
456 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
457 l_debug_msg := 'for standard costing organization';
458 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
459 END IF;
460
461
462 -- if cost is not found after earlier step (which means there
463 -- are no cost updates prior to GSD, we try to see if there are
464 -- any cost updates post GSD. In case there are cost updates
465 -- after GSD then take the cost from first MMT inventory txn to
466 -- know the item cost as of GSD.
467
468 -- We have to look into MMT here because CSC does not store
469 -- the prior cost in case a cost revision is done and we would
470 -- have lost the cost as of GSD from CIC as well.
471
472 -- as the cost is not available on the txns following txns
473 -- are excluded from here. scrap, lot merge, lot split, logical
474 -- and lot qty update. Avg cost and layer cost updates,
475 -- container pack, unpack, split.
476
477 UPDATE OPI_DBI_INV_BEG_STG fact
478 SET (onhand_value_b ,intransit_value_b) =
479 (SELECT /*+ NO_MERGE, leading(mmt1) */
480 mmt2.prior_cost * onhand_qty onhand_value_b,
481 mmt2.prior_cost *intransit_qty intransit_value_b
482 FROM (
483 SELECT /*+ leading (stg1) */
484 mmt.organization_id,
485 mmt.inventory_item_id,
486 NULL cost_group_id, --RS: Bug fix 5219487 nvl (mmt.cost_group_id, p.default_cost_group_id) cost_group_id,
487 min(mmt.transaction_id) trx_id
488 FROM mtl_material_transactions mmt,
489 OPI_DBI_INV_BEG_STG stg,
490 mtl_parameters p
491 WHERE primary_cost_method = 1
492 AND stg.organization_id = p.organization_id
493 AND stg.inventory_item_id = mmt.inventory_item_id
494 AND stg.organization_id = mmt.organization_id
495 AND mmt.transaction_date >=g_global_start_date
496 And mmt.transaction_type_id not in (73,25,26,90,91,92,55,56,57,58,87,88,89)
497 AND nvl(mmt.logical_transaction,0) <> 1
498 AND nvl(mmt.owning_tp_type, 2) = 2
499 AND mmt.organization_id = NVL (mmt.owning_organization_id,
500 mmt.organization_id)
501 and mmt.costed_flag is null
502 AND new_cost is not null
503 AND ((stg.onhand_value_b is null and nvl(stg.onhand_qty,0) <> 0 )
504 OR (stg.intransit_value_b is null and nvl(stg.intransit_qty,0) <> 0 ))
505 and exists
506 (select 1 from cst_standard_costs csc
507 where stg.inventory_item_id = csc.inventory_item_id
508 AND stg.organization_id = csc.organization_id
509 and standard_cost_revision_date >= g_global_start_date)
510 GROUP BY mmt.organization_id,
511 mmt.inventory_item_id -- ,
512 -- nvl (mmt.cost_group_id, p.default_cost_group_id), --RS: Bug fix 5219487
513 -- p.primary_cost_method
514 ) mmt1,
515 mtl_material_transactions mmt2
516 WHERE mmt2.transaction_id = mmt1.trx_id
517 and fact.organization_id = mmt1.organization_id
518 and fact.inventory_item_id =mmt1.inventory_item_id
519 -- and fact.cost_group_id =mmt1.cost_group_id -- RS: Bug fix 5219487
520 and ((fact.onhand_value_b is null and nvl(fact.onhand_qty,0) <> 0)
521 or (fact.intransit_value_b is null and nvl(fact.intransit_qty,0) <> 0 ))
522 )
523 where ((fact.onhand_value_b is null and nvl(fact.onhand_qty,0) <> 0)
524 or (fact.intransit_value_b is null and nvl(fact.intransit_qty,0) <> 0 ));
525
526 l_debug_msg := 'Updating staging table table OPI_DBI_INV_BEG_STG for ODM - ' || SQL%ROWCOUNT || ' rows. ';
527 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
528
529 l_stmt_num := 30;
530 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
531 l_debug_msg := 'updated data into OPI_DBI_INV_BEG_STG for standard costing organization';
532 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
533 l_debug_msg := 'form cst_item_costs for which cost is not found till now';
534 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
535 END IF;
536
537
538 -- step 3 for standard costing org items.
539 -- as mentioned in step2 for the items where there are no
540 -- cost updates prior and post GSD the item cost is available
541 -- in CIC. Hence for such items update the cost.
542
543 UPDATE OPI_DBI_INV_BEG_STG fact
544 SET (onhand_value_b ,intransit_value_b) =
545 (SELECT /*+ ordered use_hash(csc2, csc) parallel(csc2) parallel(csc)*/
546 nvl(csc.item_cost,0) *onhand_qty onhand_value_b,
547 nvl(csc.item_cost,0) *intransit_qty intransit_value_b
548 FROM cst_item_costs csc
549 WHERE csc.organization_id = fact.organization_id
550 AND csc.inventory_item_id = fact.inventory_item_id
551 And csc.cost_type_id =1 )
552 WHERE ((fact.onhand_value_b is null and nvl(fact.onhand_qty,0) <> 0)
553 or (fact.intransit_value_b is null and nvl(fact.intransit_qty,0) <> 0 ));
554
555 l_debug_msg := 'updated into staging table OPI_DBI_INV_BEG_STG for ODM - ' || SQL%ROWCOUNT || ' rows. ';
556 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
557
558 l_stmt_num := 40;
559 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
560 l_debug_msg := 'updating data into OPI_DBI_INV_BEG_STG for Non standard costing organization';
561 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
562 END IF;
563
564
565 -- Cost update for average costing orgs.
566 -- get a txn after GSD and prior cost on this txn is the
567 -- item cost as of the GSD for average costing org.
568 UPDATE OPI_DBI_INV_BEG_STG fact
569 SET (onhand_value_b ,intransit_value_b) =
570 (SELECT /*+ NO_MERGE, leading(mmt1) */
571 mmt2.prior_cost * onhand_qty onhand_value_b,
572 mmt2.prior_cost *intransit_qty intransit_value_b
573 FROM (
574 SELECT /*+ leading (stg1) */
575 mmt.organization_id,
576 mmt.inventory_item_id,
577 nvl (mmt.cost_group_id, p.default_cost_group_id) cost_group_id,
578 p.primary_cost_method cost_method,
579 min(mmt.transaction_id) trx_id
580 FROM mtl_material_transactions mmt,
581 OPI_DBI_INV_BEG_STG stg,
582 mtl_parameters p
583 WHERE primary_cost_method <> 1
584 AND stg.organization_id = p.organization_id
585 AND stg.inventory_item_id = mmt.inventory_item_id
586 AND stg.organization_id = mmt.organization_id
587 AND mmt.transaction_date >= g_global_start_date
588 AND mmt.organization_id = NVL (mmt.owning_organization_id,mmt.organization_id)
589 AND nvl(mmt.owning_tp_type, 2) = 2
590 AND new_cost is not null
591 AND ((stg.onhand_value_b is null and nvl(stg.onhand_qty,0) <> 0 )
592 OR (stg.intransit_value_b is null and nvl(stg.intransit_qty,0) <> 0 ))
593 GROUP BY mmt.organization_id,
594 mmt.inventory_item_id,
595 nvl (mmt.cost_group_id, p.default_cost_group_id),
596 p.primary_cost_method
597 ) mmt1,
598 mtl_material_transactions mmt2
599 WHERE mmt2.transaction_id = mmt1.trx_id
600 and fact.organization_id = mmt1.organization_id
601 and fact.inventory_item_id =mmt1.inventory_item_id
602 and fact.cost_group_id =mmt1.cost_group_id
603 --and fact.cost_method =mmt2.cost_method
604 and ((fact.onhand_value_b is null and nvl(fact.onhand_qty,0) <> 0)
605 or (fact.intransit_value_b is null and nvl(fact.intransit_qty,0) <> 0 ))
606 )
607 WHERE ((fact.onhand_value_b is null and nvl(fact.onhand_qty,0) <> 0)
608 or (fact.intransit_value_b is null and nvl(fact.intransit_qty,0) <> 0 ));
609
610 l_debug_msg := 'updated into staging table OPI_DBI_INV_BEG_STG for ODM - ' || SQL%ROWCOUNT || ' rows. ';
611 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
612
613 l_stmt_num := 50;
614 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
615 l_debug_msg := 'updating data into OPI_DBI_INV_BEG_STG for Non standard costing organization';
616 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
617 END IF;
618
619
620
621 -- In case there is no txn found in MMT after GSD then get the
622 -- cost from CQL Table as this mean there are no cost updates
623 -- after GSD.
624 -- item, org and cost_group is unique in this table.
625 UPDATE OPI_DBI_INV_BEG_STG fact
626 SET (onhand_value_b ,intransit_value_b) =
627 (SELECT
628 nvl(cst.item_cost,0) * onhand_qty onhand_value_b,
629 nvl(cst.item_cost,0) * intransit_qty intransit_value_b
630 FROM cst_quantity_layers cst,
631 mtl_parameters mp
632 WHERE mp.organization_id = fact.organization_id
633 AND primary_cost_method <> 1
634 AND cst.organization_id = fact.organization_id
635 AND cst.cost_group_id = fact.cost_group_id
636 AND cst.inventory_item_id = fact.inventory_item_id
637 AND ((fact.onhand_value_b is null and nvl(fact.onhand_qty,0) <> 0 )
638 OR (fact.intransit_value_b is null and nvl(fact.intransit_qty,0) <> 0 ))
639 )
640 WHERE ((fact.onhand_value_b is null and nvl(fact.onhand_qty,0) <> 0 )
641 OR (fact.intransit_value_b is null and nvl(fact.intransit_qty,0) <> 0 ));
642
643
644 l_debug_msg := 'updated into staging table OPI_DBI_INV_BEG_STG for ODM - ' || SQL%ROWCOUNT || ' rows. ';
645 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
646
647 commit;
648
649
650 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
651 l_debug_msg := 'Exit from COST_DISCRETE_INCEPTION_QTY ';
652 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
653 END IF;
654
655 -- 11.5.10 change to match costing team's method for obtaining
656 -- standard costs for an item. Added items with no standard cost
657 -- before global start date. For these items, we are adding the
658 -- earliest cost after or on the global start date
659
660 EXCEPTION
661 WHEN OTHERS THEN
662 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
663 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
664 RAISE;
665
666 END COST_DISCRETE_INCEPTION_QTY;
667 -------------------------------------------------------------------------------
668 FUNCTION GET_OPM_ITEM_COST( l_organization_id NUMBER,
669 l_inventory_item_id NUMBER,
670 l_txn_date DATE)
671 RETURN NUMBER
672 IS
673 x_total_cost NUMBER;
674 x_no_cost NUMBER;
675 x_return_status VARCHAR2(1);
676 x_msg_count NUMBER;
677 x_msg_data VARCHAR2(2000);
678 x_cost_method cm_mthd_mst.cost_mthd_code%TYPE;
679 x_cost_component_class_id cm_cmpt_mst.cost_cmpntcls_id%TYPE;
680 x_cost_analysis_code cm_alys_mst.cost_analysis_code%TYPE;
681 x_no_of_rows NUMBER;
682 l_ret_value NUMBER;
683 l_stmt_num NUMBER;
684 l_debug_msg VARCHAR2(1000);
685 l_proc_name VARCHAR2 (60);
686 l_debug_mode VARCHAR2(1);
687 l_module_name VARCHAR2(30);
688
689 BEGIN
690 l_proc_name := 'GET_OPM_ITEM_COST';
691 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
692 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
693 x_no_cost := NULL;
694
695 l_ret_value := GMF_CMCOMMON.Get_Process_Item_Cost
696 (
697 P_API_VERSION => 1.0
698 ,P_INIT_MSG_LIST => FND_API.G_FALSE
699 ,X_RETURN_STATUS => x_return_status
700 ,X_MSG_COUNT => x_msg_count
701 ,X_MSG_DATA => x_msg_data
702 ,P_INVENTORY_ITEM_ID => l_inventory_item_id
703 ,P_ORGANIZATION_ID => l_organization_id
704 ,P_TRANSACTION_DATE => l_txn_date
705 ,P_DETAIL_FLAG => 1
706 ,P_COST_METHOD => x_cost_method
707 ,P_COST_COMPONENT_CLASS_ID => x_cost_component_class_id
708 ,P_COST_ANALYSIS_CODE => x_cost_analysis_code
709 ,X_TOTAL_COST => x_total_cost
710 ,X_NO_OF_ROWS => x_no_of_rows
711 );
712
713 IF l_ret_value <> 1
714 THEN
715 return x_no_cost;
716 ELSE
717 return x_total_cost;
718 END IF;
719 EXCEPTION
720 WHEN OTHERS THEN
721 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
722 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
723 RAISE;
724
725 END GET_OPM_ITEM_COST;
726
727 -------------------------------------------------------------------------------
728 --This proceudure will cost OPM inception data
729 PROCEDURE COST_OPM_INCEPTION_QTY
730 IS
731 l_stmt_num NUMBER;
732 l_debug_msg VARCHAR2(1000);
733 l_proc_name VARCHAR2 (60);
734 l_debug_mode VARCHAR2(1);
735 l_module_name VARCHAR2(30);
736 l_opm_cost NUMBER;
737 l_row_count NUMBER;
738 CURSOR opm_org_cost_csr
739 IS
740 SELECT distinct
741 fact.organization_id,
742 fact.inventory_item_id,
743 fact.transaction_date
744 FROM OPI_DBI_INV_BEG_STG fact,
745 mtl_parameters p
746 WHERE fact.organization_id = p.organization_id
747 AND p.process_enabled_flag ='Y'
748 ORDER BY fact.organization_id ,
749 fact.inventory_item_id,
750 fact.transaction_date;
751
752 BEGIN
753
754 l_proc_name := 'COST_OPM_INCEPTION_QTY';
755 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
756 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
757 l_row_count := 0;
758
759 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
760 l_debug_msg := 'Entered Into COST_OPM_INCEPTION_QTY ';
761 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
762 END IF;
763
764 l_stmt_num :=10;
765 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
766 l_debug_msg := 'Inserting data into OPI_DBI_INV_BEG_STG for Process costing organization';
767 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
768 END IF;
769
770 /*UPDATE OPI_DBI_INV_BEG_STG fact
771 SET (onhand_value_b ,intransit_value_b, COST_FOUND_FLAG) =
772 (SELECT onhand_qty * GET_OPM_ITEM_COST(fact.organization_id, fact.inventory_item_id,fact.transaction_date) onhand_value_b,
773 intransit_qty * GET_OPM_ITEM_COST(fact.organization_id, fact.inventory_item_id,fact.transaction_date) intransit_value_b,
774 NULL
775 FROM dual, mtl_parameters mp
776 WHERE mp.organization_id =fact .organization_id
777 AND mp.process_enabled_flag ='Y'
778 );*/
779 FOR opm_org_cost_info IN opm_org_cost_csr
780 LOOP
781 l_opm_cost := GET_OPM_ITEM_COST(l_organization_id => opm_org_cost_info.organization_id,
782 l_inventory_item_id => opm_org_cost_info.inventory_item_id,
783 l_txn_date => opm_org_cost_info.transaction_date);
784
785 UPDATE OPI_DBI_INV_BEG_STG fact
786 SET onhand_value_b =onhand_qty * l_opm_cost
787 ,intransit_value_b = intransit_qty * l_opm_cost
788 where fact.organization_id =opm_org_cost_info.organization_id
789 AND fact.inventory_item_id =opm_org_cost_info.inventory_item_id;
790
791 l_row_count := l_row_count + SQL%ROWCOUNT ;
792
793 END LOOP;
794
795 l_debug_msg := 'Updated staging table OPI_DBI_INV_BEG_STG for OPM - ' || l_row_count || ' rows. ';
796 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
797
798 commit;
799
800
801 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
802 l_debug_msg := 'Exit from COST_OPM_INCEPTION_QTY ';
803 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
804 END IF;
805
806 EXCEPTION
807 WHEN OTHERS THEN
808 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
809 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
810 RAISE;
811
812 END COST_OPM_INCEPTION_QTY;
813
814
815 -------------------------------------------------------------------------------
816 --This procedure will provide on hand values
817 -- in table OPI_DBI_INV_BEG_STG
818
819 PROCEDURE GET_ONHAND_BALANCE
820 IS
821 l_stmt_num NUMBER;
822 l_debug_msg VARCHAR2(1000);
823 l_proc_name VARCHAR2 (60);
824 l_debug_mode VARCHAR2(1);
825 l_module_name VARCHAR2(30);
826
827 BEGIN
828
829 l_proc_name := 'GET_ONHAND_BALANCE';
830 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
831 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
832
833 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
834 l_debug_msg := 'Entered Into GET_ONHAND_BALANCE ';
835 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
836 END IF;
837
838 l_stmt_num :=10;
839 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
840 l_debug_msg := 'Inserting data into OPI_DBI_INV_BEG_STG ';
841 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
842 END IF;
843
844 INSERT INTO OPI_DBI_INV_BEG_STG
845 (organization_id
846 ,subinventory_code
847 ,cost_group_id
848 ,inventory_item_id
849 ,transaction_date
850 ,onhand_qty
851 )
852 SELECT balance.organization_id
853 ,balance.subinventory_code
854 ,balance.cost_group_id
855 ,balance.inventory_item_id
856 ,g_global_start_date
857 ,sum(balance.onhand_qty)
858 FROM (SELECT stg1.organization_id
859 ,stg1.subinventory_code
860 ,stg1.cost_group_id
861 ,stg1.inventory_item_id
862 -- beginning balance = current balance from moq - all activities from MMT
863 -- it is assumed that there are no draft quantities as of GSD
864 ,decode(stg1.transaction_source,'MOQ',stg1.onhand_qty
865 ,'MMT',-1*(stg1.onhand_qty+stg1.onhand_qty_draft)
866 ,0) onhand_qty
867 FROM opi_dbi_onh_qty_stg stg1
868 WHERE transaction_source IN ('MMT','MOQ')
869 UNION ALL
870 SELECT stg2.organization_id
871 ,stg2.subinventory_code
872 ,-1 cost_group_id -- there is no cost_group_id required for opm items for finding cost
873 ,stg2.inventory_item_id
874 ,-1*stg2.onhand_qty
875 FROM opi_dbi_opm_inv_stg stg2) balance
876 GROUP BY balance.organization_id
877 ,balance.subinventory_code
878 ,balance.cost_group_id
879 ,balance.inventory_item_id;
880
881 l_debug_msg := 'Inserted into staging table OPI_DBI_INV_BEG_STG - ' || SQL%ROWCOUNT || ' rows. ';
882 OPI_DBI_BOUNDS_PKG.write(g_pkg_name, l_proc_name,l_stmt_num, l_debug_msg);
883
884 commit;
885
886 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
887 l_debug_msg := 'Exit from GET_ONHAND_BALANCE ';
888 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
889 END IF;
890
891 --execute immediate 'alter session disable parallel query';
892
893 EXCEPTION
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_ONHAND_BALANCE;
899
900 -------------------------------------------------------------------------------
901 --This Procedure will collect data into temp table required for Intransit
902 --Initial Load
903 --In opi_dbi_intr_mip_tmp , which contains shipping network information for
904 --discrete organization till now(Pre R12), shipping network between all
905 --type of organization will be collected along with
906 --process enabled flag info
907 --Data from MMT which has been moved to opi_dbi_intr_mmt_tmp for discrete
908 --organization ,pre r12.
909 --After R12 this table will also contains the information for process
910 --organization.
911 --Get MMT Data in opi_dbi_intr_mmt_tmp. We will collect following
912 --transaction actions
913 -- 12 -Intransit Receipt
914 -- 21 - Intransit Shipment
915 -- 24 - Cost Update ODM
916 -- 15 - Logical Intransit Receipt
917 -- 22 - Logical Intransit Shipment
918 --While collecting data we will map it with MTL_Parameters. To get cost
919 --group Id for discrete Organization and Process Enabled flag for
920
921 PROCEDURE INTRANSIT_SETUP(p_mode varchar2)
922 IS
923 l_stmt_num NUMBER;
924 l_debug_msg VARCHAR2(1000);
925 l_proc_name VARCHAR2 (60);
926 l_debug_mode VARCHAR2(1);
927 l_module_name Varchar2 (30);
928 BEGIN
929 l_proc_name := 'GET_INTRANSIT_INITIAL_LOAD';
930 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
931 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
932
933 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
934 l_debug_msg := 'Entered into INTRANSIT_SETUP ';
935 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
936 END IF;
937 -- Setup the intransit shipping network parameters.
938 -- FOB = 1 = Shipment i.e. the to_org is the owning_org.
939 -- FOB = 2 = Receipt i.e. the from_org is the owning_org.
940 -- For shipments, the from_org is the from_org in MIP and the
941 -- to_org is the to_org in MIP.
942 -- For receipts, the roles of the orgs are reversed.
943 IF p_mode = 'INIT' or p_mode = 'INCR' THEN
944 l_stmt_num :=10;
945 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
946 l_debug_msg := 'Insert Data into opi_dbi_intr_mip_tmp ';
947 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
948 END IF;
949
950 INSERT /*+append parallel(opi_dbi_intr_mip_tmp) */
951 INTO opi_dbi_intr_mip_tmp (
952 from_organization_id,
953 to_organization_id,
954 owning_organization_id,
955 owning_org_process_flag,
956 transaction_action_id,
957 fob_point)
958 select from_organization_id,
959 to_organization_id,
960 owning_organization_id,
961 -- Because of wrong setup value 1 is there for some of the discrete org,
962 -- Customers might have this too
963 DECODE(mp.process_enabled_flag,'1','N',mp.process_enabled_flag),
964 transaction_action_id, -- intransit shipment
965 fob_point
966 from MTL_PARAMETERS mp,
967 (SELECT /*+ parallel(mip) */
968 from_organization_id,
969 to_organization_id,
970 DECODE(fob_point,1,to_organization_id,
971 2,from_organization_id) owning_organization_id,
972 21 transaction_action_id, -- intransit shipment
973 fob_point
974 FROM MTL_INTERORG_PARAMETERS mip
975 WHERE NVL(fob_point,-1) in (1,2)
976 UNION ALL
977 SELECT /*+ parallel(mip) */
978 to_organization_id,
979 from_organization_id,
980 DECODE(fob_point,1,to_organization_id,
981 2,from_organization_id) owning_organization_id,
982 12 transaction_action_id, -- intransit receipt
983 fob_point
984 FROM MTL_INTERORG_PARAMETERS mip
985 WHERE NVL(fob_point,-1) in (1,2)) mip_outer
986 WHERE mip_outer.owning_organization_id =mp.organization_id
987 ;
988
989 l_debug_msg := 'Inserted into staging table opi_dbi_intr_mip_tmp - ' || SQL%ROWCOUNT || ' rows. ';
990 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
991
992 commit;
993 END IF;
994
995 IF p_mode = 'INIT' THEN
996 l_stmt_num :=20;
997 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
998 l_debug_msg := 'Insert Data into opi_dbi_intr_mmt_tmp. ';
999 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1000 END IF;
1001 -- Select all intransit data from MMT into a temp table.
1002 --
1003 --
1004 -- Additionally, pick up a cost group associated with each
1005 -- transaction. For standard costing orgs, use the default
1006 -- cost group associated with the organization in MTL_PARAMETERS.
1007 -- As of 11i, the default_cost_group_id is guaranteed to be
1008 -- non-null, so no nvl is needed on the selection of that column.
1009
1010 INSERT /*append parallel(opi_dbi_intr_mmt_tmp) */
1011 INTO OPI_DBI_INTR_MMT_TMP (
1012 transaction_id,
1013 organization_id,
1014 organization_process_flag,
1015 transfer_organization_id,
1016 transfer_org_process_flag,
1017 inventory_item_id,
1018 transaction_action_id,
1019 cost_group_id,
1020 transfer_cost_group_id,
1021 primary_quantity,
1022 transaction_date)
1023 SELECT /*+ use_hash(mmt) use_hash(p) use_hash(p1) parallel(mmt) parallel(p) parallel(p1)*/
1024 mmt.transaction_id,
1025 mmt.organization_id,
1026 -- Setup issue some discrete organization can have value 1.
1027 DECODE(p.process_enabled_flag
1028 ,'1','N',
1029 p.process_enabled_flag),
1030 transfer_organization_id,
1031 DECODE(p1.process_enabled_flag,
1032 '1','N',
1033 p1.process_enabled_flag),
1034 inventory_item_id,
1035 transaction_action_id,
1036 NVL (mmt.cost_group_id,
1037 p.default_cost_group_id) cost_group_id,
1038 NVL (mmt.transfer_cost_group_id,
1039 p1.default_cost_group_id) transfer_cost_group_id,
1040 DECODE (mmt.transaction_action_id,
1041 24, 0,
1042 mmt.primary_quantity),
1043 transaction_date
1044 FROM MTL_MATERIAL_TRANSACTIONS mmt,
1045 MTL_PARAMETERS p,
1046 MTL_PARAMETERS p1
1047 WHERE mmt.transaction_action_id in (12,21,24,15,22)
1048 AND mmt.transaction_date >= g_global_start_date
1049 AND p.organization_id = mmt.organization_id
1050 AND p1.organization_id = mmt.transfer_organization_id
1051 --AND mmt.transaction_type_id IN (12,21,61,62,24,80,26,28) -- Removed in r12
1052 ;
1053
1054 l_debug_msg := 'Inserted into staging table opi_dbi_intr_mmt_tmp - ' || SQL%ROWCOUNT || ' rows. ';
1055 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1056 commit;
1057
1058 l_stmt_num :=30;
1059 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1060 l_debug_msg := 'Insert into opi_dbi_intr_sup_tmp. ';
1061 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1062 END IF;
1063 -- Extract all data from mtl_supply into a temp table.
1064 -- Make sure that the primary quantity used is that
1065 -- of the intransit_owning_org.
1066
1067 -- Bug 4760492
1068 -- MTL supply contains Transaction qty for from/shipping organization.
1069 -- It might not be same as primary quantity.
1070 -- code has been changed for this
1071 INSERT /*append parallel(opi_dbi_intr_sup_tmp) */
1072 INTO OPI_DBI_INTR_SUP_TMP (
1073 intransit_owning_org_id,
1074 from_organization_id,
1075 to_organization_id,
1076 qty,
1077 item_id,
1078 cost_group_id)
1079 SELECT /*+ ordered use_hash(sup) parallel(sup)*/
1080 intransit_owning_org_id,
1081 from_organization_id,
1082 to_organization_id,
1083 sum (DECODE (intransit_owning_org_id,
1084 sup.from_organization_id, NVL(inv_convert.inv_um_convert(sup.item_id,
1085 5,sup.quantity,
1086 um.uom_code,msi_fm.primary_uom_code,
1087 NULL,NULL), 0),
1088 NVL(to_org_primary_quantity, 0))) qty,
1089 item_id,
1090 cost_group_id
1091 FROM MTL_SUPPLY sup,MTL_SYSTEM_ITEMS msi_fm,mtl_units_of_measure um
1092 WHERE supply_type_code in ('SHIPMENT' , 'RECEIVING')
1093 AND intransit_owning_org_id IS NOT NULL
1094 AND msi_fm.organization_id = sup.from_organization_id
1095 AND msi_fm.inventory_item_id = sup.item_id
1096 AND um.unit_of_measure = sup.unit_of_measure
1097 GROUP BY
1098 intransit_owning_org_id,
1099 from_organization_id,
1100 to_organization_id,
1101 item_id,
1102 cost_group_id;
1103
1104 --Old code
1105 /*SELECT
1106 intransit_owning_org_id,
1107 from_organization_id,
1108 to_organization_id,
1109 sum (DECODE (intransit_owning_org_id,
1110 sup.from_organization_id, NVL(quantity, 0),
1111 NVL(to_org_primary_quantity, 0))) qty,
1112 item_id,
1113 cost_group_id
1114 FROM MTL_SUPPLY sup
1115 WHERE supply_type_code in ('SHIPMENT' , 'RECEIVING')
1116 AND intransit_owning_org_id IS NOT NULL
1117 GROUP BY
1118 intransit_owning_org_id,
1119 from_organization_id,
1120 to_organization_id,
1121 item_id,
1122 cost_group_id;
1123 */
1124 l_debug_msg :='Inserted into staging table opi_dbi_intr_sup_tmp - ' || SQL%ROWCOUNT || ' rows. ';
1125 opi_dbi_bounds_pkg.write(g_pkg_name, l_proc_name,l_stmt_num,l_debug_msg);
1126
1127 commit;
1128 END IF;
1129
1130 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1131 l_debug_msg := 'Exit from INTRANSIT_SETUP ';
1132 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1133 END IF;
1134
1135 EXCEPTION
1136 WHEN OTHERS THEN
1137 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
1138 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1139 RAISE;
1140 END INTRANSIT_SETUP;
1141
1142 -------------------------------------------------------------------------------
1143 --In This procedure, Inception Quantities for Intransit Inventory is collected
1144 --In Procedure Intransit setup, data have been collceted into
1145 --following tables
1146 -- opi_dbi_intr_mip_tmp,
1147 -- opi_dbi_intr_mmt_tmp
1148 -- and opi_dbi_intr_sup_tmp,
1149 --which are in sync(approx) with each other.
1150 --We will use these three tables to collect data for inception quantities.
1151 --For Pre R12 OPM data , we will use opi_dbi_intr_sup_tmp table
1152 --Data collection is similar to initial Load; here only quantities are
1153 --collected on inception date.
1154 --No bound are used for inception load. Also all uncosted transaction are
1155 --considered.
1156 --As only quantities need to be collected, only physical transaction will
1157 --be collected. Action type 12, 21
1158 --UOM conversion is required whenever UOM of transfer organization and
1159 --receiving organization is different and UOM is not in term of owning
1160 --organization. Ex. For FOB shipping, Intransit is own by receiving
1161 --organization. Now if UOM of from (transfer) organization and to(receiving
1162 --) organization is not same. Then for shipping transaction (when item is
1163 --shipped) , we need to do UOM conversion.
1164
1165 PROCEDURE GET_INTRANSIT_BALANCE
1166 IS
1167
1168 l_stmt_num NUMBER;
1169 l_debug_msg VARCHAR2(1000);
1170 l_proc_name VARCHAR2 (60);
1171 l_debug_mode VARCHAR2(1);
1172 l_module_name VARCHAR2 (30);
1173 BEGIN
1174
1175 l_proc_name := 'GET_INTRANSIT_BALANCE';
1176 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1177 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
1178
1179 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1180 l_debug_msg := 'Entered Into GET_INTRANSIT_BALANCE';
1181 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1182 END IF;
1183
1184 l_stmt_num := 10;
1185 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1186 l_debug_msg := 'Inserting data into OPI_DBI_INV_BEG_STG for Intransit Inception load ';
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_RPT_UTIL_PKG.g_pk_uom_conversion := 1;
1191
1192 INSERT /*+ append parallel(opi_dbi_intransit_stg) */
1193 INTO OPI_DBI_INV_BEG_STG
1194 ( organization_id ,
1195 inventory_item_id,
1196 subinventory_code,
1197 Cost_group_id,
1198 intransit_qty ,
1199 transaction_date
1200 ) --Gets inventory_item, org_id, cost group combos with qty sums.
1201 SELECT
1202 xy.organization_id,
1203 xy.inventory_item_id,
1204 NULL subinventory_code,
1205 xy.cost_group_id,
1206 sum(xy.tot_prim_qty) tot_prim_qty,
1207 g_global_start_date
1208 FROM (
1209 SELECT
1210 mip.owning_organization_id organization_id,
1211 mmt1.inventory_item_id inventory_item_id,
1212 -- intransit balance = current intransit - activities.
1213 -- here quantities are not negated because intransit
1214 -- sign is already reverse on MMT w.r.t intransit
1215 -- quantity.
1216 sum (decode (msi_fm.primary_uom_code,
1217 msi_to.primary_uom_code,
1218 decode(mmt1.transaction_action_id,
1219 22, mmt1.primary_quantity, --??Not needed
1220 15, -1 * mmt1.primary_quantity,
1221 mmt1.primary_quantity), --Bug 4878458
1222 decode (mmt1.transaction_action_id,
1223 21,decode (mip.fob_point,
1224 2, mmt1.primary_quantity,
1225 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)),
1226 12,decode (mip.fob_point,
1227 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),
1228 mmt1.primary_quantity)))) tot_prim_qty,
1229 decode(Mip.owning_org_process_flag -- this need only for cost group id, As For OPM it is -1
1230 ,'N',decode (mmt1.transaction_action_id,
1231 21,decode (mip.fob_point,
1232 2, mmt1.cost_group_id,
1233 mmt1.transfer_cost_group_id),
1234 12,decode (mip.fob_point,
1235 2,mmt1.transfer_cost_group_id,
1236 mmt1.cost_group_id)
1237 ),-1) cost_group_id
1238 FROM OPI_DBI_INTR_MMT_TMP mmt1,
1239 OPI_DBI_INTR_MIP_TMP mip,
1240 MTL_PARAMETERS mp,
1241 MTL_SYSTEM_ITEMS msi_fm,
1242 MTL_SYSTEM_ITEMS msi_to
1243 WHERE mmt1.organization_id = mip.from_organization_id
1244 AND mmt1.transfer_organization_id = mip.to_organization_id
1245 AND mmt1.transaction_action_id = mip.transaction_action_id
1246 -- not collecting action id 24
1247 -- as we are only collecting quantities
1248 -- not looking at logical txns as well as looking at only
1249 -- quantity and it comes correct from all physical txns
1250 -- alone.
1251 AND mmt1.transaction_action_id in (21,12)
1252 AND mip.owning_organization_id = mp.organization_id
1253 AND msi_fm.organization_id = mip.from_organization_id
1254 AND msi_fm.inventory_item_id = mmt1.inventory_item_id
1255 AND msi_to.organization_id = mip.to_organization_id
1256 AND msi_to.inventory_item_id = mmt1.inventory_item_id
1257 GROUP BY mip.owning_organization_id,
1258 mmt1.inventory_item_id,
1259 decode(Mip.owning_org_process_flag
1260 ,'N', mp.primary_cost_method,-1),
1261 decode (mip.fob_point,
1262 2,decode (mip.transaction_action_id,
1263 21, msi_fm.primary_uom_code,
1264 msi_to.primary_uom_code),
1265 decode (mip.transaction_action_id,
1266 12,msi_to.primary_uom_code,
1267 msi_fm.primary_uom_code)),
1268 decode(Mip.owning_org_process_flag -- this need only for cost group id, As For OPM it is -1
1269 ,'N',decode (mmt1.transaction_action_id,
1270 21,decode (mip.fob_point,
1271 2, mmt1.cost_group_id,
1272 mmt1.transfer_cost_group_id),
1273 12,decode (mip.fob_point,
1274 2,mmt1.transfer_cost_group_id,
1275 mmt1.cost_group_id)
1276 ),-1)
1277 UNION ALL
1278 SELECT sup.intransit_owning_org_id
1279 organization_id,
1280 sup.item_id inventory_item_id,
1281 sum(sup.qty) tot_prim_qty,
1282 nvl (sup.cost_group_id, p.default_cost_group_id)
1283 cost_group_id
1284 FROM OPI_DBI_INTR_SUP_TMP sup,
1285 MTL_SYSTEM_ITEMS msi,
1286 MTL_PARAMETERS p
1287 WHERE sup.intransit_owning_org_id = msi.organization_id
1288 AND p.organization_id = msi.organization_id
1289 AND sup.item_id = msi.inventory_item_id
1290 GROUP BY sup.intransit_owning_org_id,
1291 sup.item_id,
1292 p.primary_cost_method,
1293 nvl (sup.cost_group_id, p.default_cost_group_id),'N'
1294 UNION ALL
1295 SELECT organization_id,
1296 inventory_item_id,
1297 -1 * intransit_qty tot_prim_qty,
1298 -1 cost_group_id
1299 FROM OPI_DBI_OPM_INV_STG) xy
1300 GROUP BY inventory_item_id,
1301 organization_id,
1302 cost_group_id,
1303 g_global_start_date
1304 HAVING SUM(xy.tot_prim_qty) <>0 ;
1305
1306 if OPI_DBI_RPT_UTIL_PKG.g_pk_uom_conversion <>1 then
1307 Raise UOM_CONV_ERROR;
1308 end if;
1309 l_debug_msg := 'Inserted into staging table OPI_DBI_INV_BEG_STG - ' || SQL%ROWCOUNT || ' rows. ';
1310 OPI_DBI_BOUNDS_PKG.write(g_pkg_name, l_proc_name,l_stmt_num, l_debug_msg);
1311
1312 commit;
1313
1314 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1315 l_debug_msg := 'Exit from GET_INTRANSIT_BALANCE';
1316 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1317 END IF;
1318
1319
1320 EXCEPTION
1321 WHEN UOM_CONV_ERROR then
1322 l_debug_msg := 'UOM conversion not found ' || SQLcode || ' - ' ||SQLERRM;
1323 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1324 RAISE;
1325
1326 WHEN OTHERS THEN
1327 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
1328 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1329 RAISE;
1330
1331 END GET_INTRANSIT_BALANCE;
1332
1333 -------------------------------------------------------------------------------
1334
1335 PROCEDURE GET_WIP_BALANCE
1336 IS
1337 l_stmt_num NUMBER;
1338 l_debug_msg VARCHAR2(1000);
1339 l_proc_name VARCHAR2 (60);
1340 l_debug_mode VARCHAR2(1);
1341 l_module_name VARCHAR2(30);
1342 BEGIN
1343 l_proc_name := 'GET_WIP_BALANCE';
1344 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1345 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
1346
1347 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1348 l_debug_msg := 'Entered Into GET_WIP_BALANCE ';
1349 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1350 END IF;
1351
1352 l_stmt_num :=10;
1353 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1354 l_debug_msg := 'Inserting data into OPI_DBI_INV_BEG_STG ';
1355 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1356 END IF;
1357
1358 INSERT INTO OPI_DBI_INV_BEG_STG
1359 (organization_id
1360 ,subinventory_code
1361 ,cost_group_id
1362 ,inventory_item_id
1363 ,transaction_date
1364 ,wip_value_b
1365 )
1366 SELECT balance.organization_id
1367 ,balance.subinventory_code
1368 ,balance.cost_group_id
1369 ,balance.inventory_item_id
1370 ,g_global_start_date
1371 ,sum(balance.wip_value_b)
1372 FROM (SELECT stg1.organization_id
1373 ,stg1.subinventory_code
1374 ,-1 cost_group_id -- as wip is value and not qty no costing done. hence no cost group id required.
1375 ,stg1.inventory_item_id
1376 ,sum((decode(stg1.transaction_source,'WTA',-1*stg1.wip_value_b
1377 ,'MTA',-1*stg1.wip_value_b
1378 ,'GTV',-1*(nvl(wip_value_b,0)+nvl(wip_value_b_draft,0))
1379 ,'WPB',wip_value_b
1380 ,'OPJ',nvl(wip_value_b,0)+nvl(wip_value_b_draft,0)
1381 ,0))) wip_value_b
1382 FROM opi_dbi_onhand_stg stg1
1383 WHERE transaction_source IN ('WTA','MTA','GTV','WPB','OPJ')
1384 group by stg1.organization_id
1385 ,stg1.subinventory_code
1386 ,-1
1387 ,stg1.inventory_item_id
1388 -- for preR12 data. it is already grouped by org, sub,
1389 -- item hence no additional group by here.
1390 UNION ALL
1391 SELECT stg2.organization_id
1392 ,stg2.subinventory_code
1393 ,-1 cost_group_id
1394 ,stg2.inventory_item_id
1395 ,-1*stg2.wip_value_b
1396 FROM opi_dbi_opm_inv_stg stg2) balance
1397 GROUP BY balance.organization_id
1398 ,balance.subinventory_code
1399 ,balance.cost_group_id
1400 ,balance.inventory_item_id;
1401
1402 l_debug_msg := 'Inserted into staging table OPI_DBI_INV_BEG_STG - ' || SQL%ROWCOUNT || ' rows. ';
1403 OPI_DBI_BOUNDS_PKG.write(g_pkg_name, l_proc_name,l_stmt_num, l_debug_msg);
1404
1405 commit;
1406
1407 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1408 l_debug_msg := 'Exit from GET_WIP_BALANCE ';
1409 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1410 END IF;
1411
1412
1413 EXCEPTION
1414 WHEN OTHERS THEN
1415 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
1416 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1417 RAISE;
1418
1419 END GET_WIP_BALANCE;
1420
1421 -------------------------------------------------------------------------------
1422 -- This procedure will call onhand, intransit and wip (value)procedures to collect
1423 -- inception qunatities .
1424 -- For onhand and intransit it will also call discrete and OPM costing procedures
1425 -- to get intransit and onhand values on inception date
1426 PROCEDURE GET_INCEPTION_INV_BALANCE
1427 IS
1428 l_stmt_num NUMBER;
1429 l_debug_msg VARCHAR2(1000);
1430 l_proc_name VARCHAR2 (60);
1431 l_debug_mode VARCHAR2(1);
1432 l_module_name VARCHAR2 (30);
1433 BEGIN
1434
1435 l_proc_name := 'GET_INCEPTION_INV_BALANCE';
1436 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1437 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
1438
1439 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1440 l_debug_msg := 'Entered Into GET_INCEPTION_INV_BALANCE';
1441 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1442 END IF;
1443
1444 g_inception_date := g_global_start_date ;
1445
1446 l_stmt_num := 10;
1447 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1448 l_debug_msg := 'Extracting On Hand Inception Balances into its staging table ...';
1449 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1450 END IF;
1451 -- collect on hand inception balance
1452 OPI_DBI_INV_VALUE_INIT_PKG.get_onhand_balance;
1453
1454 l_stmt_num := 20;
1455 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1456 l_debug_msg := 'Extracting InTransit Inception Balances into its staging table ...';
1457 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1458 END IF;
1459 -- collect intransit inception balance
1460 OPI_DBI_INV_VALUE_INIT_PKG.get_intransit_balance;
1461
1462 l_stmt_num := 30;
1463 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1464 l_debug_msg := 'Extracting Work In Process Inception Balances into its staging table ...';
1465 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1466 END IF;
1467 -- collect WIP inception balance
1468 OPI_DBI_INV_VALUE_INIT_PKG.get_wip_balance;
1469
1470 l_stmt_num := 40;
1471 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1472 l_debug_msg := 'Costing Inception balances ODM ...';
1473 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1474 END IF;
1475 -- cost discrete inception onhand and intransit quantities
1476 OPI_DBI_INV_VALUE_INIT_PKG.cost_discrete_inception_qty;
1477
1478 l_stmt_num := 50;
1479 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1480 l_debug_msg := 'Costing Inception balances OPM ...';
1481 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1482 END IF;
1483 -- cost OPM inception onhand and intransit quantities
1484 OPI_DBI_INV_VALUE_INIT_PKG.cost_opm_inception_qty;
1485
1486 commit;
1487
1488 --execute immediate 'alter session disable parallel query';
1489 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1490 l_debug_msg := 'Exit from GET_INCEPTION_INV_BALANCE';
1491 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1492 END IF;
1493
1494 EXCEPTION
1495 WHEN OTHERS THEN
1496 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
1497 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1498 RAISE;
1499
1500 END GET_INCEPTION_INV_BALANCE;
1501
1502 -------------------------------------------------------------------------------
1503 -- This procedure calls INTRANSIT_SETUP and then collects intransti
1504 -- data for Discrete and Process orgs in two separate extracts.
1505
1506
1507 PROCEDURE GET_INTRANSIT_INITIAL_LOAD
1508 IS
1509 l_stmt_num NUMBER;
1510 l_debug_msg VARCHAR2(1000);
1511 l_proc_name VARCHAR2 (60);
1512 l_debug_mode VARCHAR2(1);
1513 l_module_name VARCHAR2 (30);
1514 BEGIN
1515 l_proc_name := 'GET_INTRANSIT_INITIAL_LOAD';
1516 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1517 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
1518
1519 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1520 l_debug_msg := 'Entered Into GET_INTRANSIT_INITIAL_LOAD ';
1521 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1522 END IF;
1523
1524 l_stmt_num :=10;
1525 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1526 l_debug_msg := 'Calling Intransit setup from get_intransit_initial_load. ';
1527 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1528 END IF;
1529
1530 -- INTRANSIT_SETUP collect data into following temp tables.
1531 -- OPI_DBI_INTR_MIP_TMP
1532 -- OPI_DBI_INTR_SUP_TMP
1533 -- OPI_DBI_INTR_MMT_TMP
1534 Intransit_setup('INIT'); --R12 moved here
1535
1536 l_stmt_num :=20;
1537 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1538 l_debug_msg := 'Inserting data Into opi_dbi_intransit_stg for ODM ';
1539 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1540 END IF;
1541
1542 -- The extract below gets data from mmt staging table populated in INTRANSIT_SETUP.
1543 -- joining with MIP and MTA tables to get the intransit data for
1544 -- transaction_id range as in the bounds table.
1545 -- There is a separate UNION ALL to get cost update on intransit
1546 -- value.
1547 -- This extract gets only discrete intransit data.
1548
1549 -- Explaination of data stored in MIP:
1550 -- mip has two records for each setup in mtl_interorg_parameters.
1551 -- e.g. for M1 -> M2 FOB = 2 there are two records in mip
1552
1553 -- from_org to_org owning_org txn_action_id
1554 -- M1 M2 M1 21(shipment)
1555 -- M2 M1 M1 12(receiving)
1556 -- for another setup line lets say M2 -> M1 fob = 1 there are another two set or records
1557 -- M2 M1 M1 21
1558 -- M2 M1 M1 12
1559
1560 -- When this table is joined with MMT it is joined with
1561 -- transaction_action_id. So for any network a different row is
1562 -- joined with MIP depending upon if its shipping transaction or
1563 -- receiving transaction.
1564 -- mmt.organization_id is joined with mip.from_organization_id
1565 -- which is not necessarily the shipping org id.
1566 -- for txn_action_id 21 from_org is shipping org and transfer_org is receiving_org
1567 -- for txn_action_id 12 from_org is receiving org and transfer_org is shipping_org
1568
1569 -- so when txn_action_id = 21 and fob = 1 (shipping) owning org is receiving_org which is tranfer_org
1570 -- similarly when txn_action_id = 12 and fob = 2 (receiving) owning org is receiving org which is from_org
1571
1572 -- for new senarios in R12 txn 15 and 22 have come in.
1573 -- 15 is logical receipt and 22 is logical shipment.
1574 -- both these logical txns always has txn_organization same as owning organization.
1575 -- refer to detail use cases in DLD for this.
1576 -- The quantity on these txns is also the +ve intransit quantity.
1577 OPI_DBI_RPT_UTIL_PKG.g_pk_uom_conversion := 1;
1578 INSERT /*+ append */ INTO OPI_DBI_INTRANSIT_STG(
1579 organization_id,
1580 inventory_item_id,
1581 transaction_date,
1582 intransit_qty,
1583 primary_uom,
1584 intransit_value_b,
1585 source,
1586 creation_date,
1587 last_update_date,
1588 created_by,
1589 last_updated_by,
1590 last_update_login)
1591 SELECT /*+ use_hash(mta1) use_hash(mmt1) */
1592 organization_id,
1593 inventory_item_id,
1594 decode (sign (trx_date - g_global_start_date),-1, g_global_start_date,trx_date) transaction_date,
1595 SUM (qty) intransit_qty,
1596 primary_uom_code primary_uom,
1597 SUM(value)intransit_value_b,
1598 decode(process_flag,'N',1,2), --Discrete/Process Bug fix: 5362465
1599 g_sysdate,
1600 g_sysdate,
1601 g_user_id,
1602 g_user_id,
1603 g_login_id
1604 FROM
1605 (SELECT
1606 mip.owning_organization_id organization_id,
1607 mmt1.inventory_item_id,
1608 -- logical txn no need of conversion as they are always against the owning org.
1609 -- logical always contain qty in right sign there is -1 outside as well
1610 -- -1 is done outside as txn quantity is always reverse sign of the txn qty.
1611 -- for intransit across process and discrete orgs only logical txns are considered
1612 -1 * SUM (
1613 DECODE(
1614 msi_fm.primary_uom_code,
1615 msi_to.primary_uom_code,
1616 decode(mmt1.transaction_action_id,
1617 22, mmt1.primary_quantity, -- Absolute value fix
1618 15, -1 * mmt1.primary_quantity,
1619 mmt1.primary_quantity), --Bug 4878458
1620 DECODE(
1621 mmt1.transaction_action_id,
1622 21, DECODE( -- intransit shipment
1623 mip.fob_point, -- FOB 2 is receipt
1624 2, mmt1.primary_quantity,
1625 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)
1626 ),
1627 12, DECODE ( -- inransit receipt
1628 mip.fob_point,
1629 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),
1630 mmt1.primary_quantity),
1631 22, mmt1.primary_quantity, -- Absolute value fix
1632 15, -1 * mmt1.primary_quantity))) qty,
1633 SUM (base_transaction_value) value,
1634 DECODE (mip.owning_organization_id, msi_fm.organization_id, msi_fm.primary_uom_code
1635 ,msi_to.organization_id, msi_to.primary_uom_code) primary_uom_code,
1636 TRUNC (mmt1.transaction_date) trx_date,
1637 mip.owning_org_process_flag process_flag -- Bug fix: 5362465
1638 FROM opi_dbi_intr_mip_tmp mip,
1639 opi_dbi_intr_mmt_tmp mmt1,
1640 (select
1641 transaction_id,
1642 sum (base_transaction_value) base_transaction_value
1643 from mtl_transaction_accounts
1644 where accounting_line_type = 14 -- Accounting line for Inransit in MTA
1645 group by transaction_id) mta,
1646 mtl_system_items msi_fm,
1647 mtl_system_items msi_to,
1648 opi_dbi_conc_prog_run_log col
1649 WHERE mmt1.organization_id = mip.from_organization_id
1650 AND mmt1.transfer_organization_id = mip.to_organization_id
1651 AND mmt1.transaction_action_id in (15,12,22,21)
1652 AND decode(mmt1.transaction_action_id,15,12,22,21,mmt1.transaction_action_id) = mip.transaction_action_id
1653 -- condition below avoids getting 1 of the physical txns across process and discrete orgs
1654 -- as the process flag is different for owning org and txn organization
1655 -- for more detail refer to DLD test cases
1656 and mmt1.organization_process_flag = mip.owning_org_process_flag
1657 AND mip.from_organization_id = col.bound_level_entity_id
1658 AND mta.transaction_id (+)= mmt1.transaction_id -- outer join is required to collect expense item
1659 -- As some of them might not have row in MMT.
1660 AND msi_fm.inventory_item_id = mmt1.inventory_item_id
1661 AND msi_fm.organization_id = mip.from_organization_id
1662 and msi_to.inventory_item_id = mmt1.inventory_item_id
1663 AND msi_to.organization_id = mip.to_organization_id
1664 AND mmt1.transaction_id >= col.from_bound_id
1665 AND mmt1.transaction_id < col.to_bound_id
1666 AND col. driving_table_code= 'MMT'
1667 AND col.etl_type = 'INVENTORY'
1668 AND col.load_type= 'INIT'
1669 GROUP BY
1670 mip.owning_organization_id,
1671 mip.owning_org_process_flag, -- Bug fix: 5362465
1672 mmt1.inventory_item_id,
1673 decode (mip.fob_point,2, decode (mip.transaction_action_id,21, msi_fm.primary_uom_code,
1674 msi_to.primary_uom_code),decode (mip.transaction_action_id,
1675 12, msi_to.primary_uom_code,msi_fm.primary_uom_code)) ,
1676 trunc(mmt1.transaction_date),
1677 DECODE (mip.owning_organization_id, msi_fm.organization_id, msi_fm.primary_uom_code
1678 ,msi_to.organization_id, msi_to.primary_uom_code)
1679 --UNION ALL to collect cost update data related to Intransit.
1680 UNION ALL
1681 SELECT
1682 mmt1.organization_id organization_id,
1683 mmt1.inventory_item_id,
1684 0 qty,
1685 sum (base_transaction_value) value,
1686 msi_fm.primary_uom_code,
1687 trunc(mmt1.transaction_date) trx_date,
1688 'N' process_flag -- Bug fix: 5362465, source is only discrete here
1689 FROM opi_dbi_intr_mmt_tmp mmt1,
1690 mtl_transaction_accounts mta,
1691 mtl_system_items msi_fm,
1692 OPI_DBI_CONC_PROG_RUN_LOG col
1693 WHERE mmt1.transaction_action_id = 24
1694 AND mta.transaction_id = mmt1.transaction_id
1695 AND mmt1.organization_id = mta.organization_id
1696 AND mta.accounting_line_type = 14
1697 AND msi_fm.inventory_item_id = mmt1.inventory_item_id
1698 AND msi_fm.organization_id = mmt1.organization_id
1699 AND mmt1.organization_id = col.BOUND_LEVEL_ENTITY_ID
1700 AND mmt1.transaction_id >= col.FROM_BOUND_ID
1701 AND mmt1.transaction_id < col.TO_BOUND_ID
1702 And col. DRIVING_TABLE_CODE= 'MMT'
1703 And col.ETL_TYPE = 'INVENTORY'
1704 And col.LOAD_TYPE= 'INIT'
1705 GROUP BY
1706 mmt1.organization_id,
1707 mmt1.inventory_item_id,
1708 msi_fm.primary_uom_code,
1709 trunc(mmt1.transaction_date))
1710 GROUP BY
1711 organization_id,
1712 inventory_item_id,
1713 primary_uom_code,
1714 trx_date,
1715 decode(process_flag,'N',1,2) -- Bug fix: 5362465
1716 HAVING sum(value) <> 0 or sum(qty) <> 0;
1717
1718 if OPI_DBI_RPT_UTIL_PKG.g_pk_uom_conversion <> 1 then
1719 Raise UOM_CONV_ERROR;
1720 end if ;
1721
1722 l_debug_msg := 'Inserted into staging table opi_dbi_intransit_stg - ' || SQL%ROWCOUNT || ' rows. ';
1723 OPI_DBI_BOUNDS_PKG.write(g_pkg_name, l_proc_name,l_stmt_num, l_debug_msg);
1724
1725 commit;
1726
1727 l_stmt_num :=30;
1728 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1729 l_debug_msg := 'Inserting data Into opi_dbi_intransit_stg for OPM Post R12 ';
1730 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1731 END IF;
1732
1733 -- extract to collect process orgs intransit data
1734 -- process does not support cost updates for intransit data hence
1735 -- no separate extract to get cost update data.
1736 OPI_DBI_RPT_UTIL_PKG.g_pk_uom_conversion := 1;
1737
1738 INSERT /*+ APPEND */ INTO OPI_DBI_INTRANSIT_STG(
1739 organization_id,
1740 inventory_item_id,
1741 transaction_date,
1742 intransit_qty,
1743 intransit_qty_draft,
1744 primary_uom,
1745 intransit_value_b,
1746 intransit_value_draft_b,
1747 source,
1748 creation_date,
1749 last_update_date,
1750 created_by,
1751 last_updated_by,
1752 last_update_login)
1753 SELECT /*+ use_hash(mta1) use_hash(mmt1) */
1754 organization_id,
1755 inventory_item_id,
1756 DECODE (SIGN (trx_date - g_global_start_date),
1757 -1, g_global_start_date,
1758 trx_date) transaction_date,
1759 SUM (qty) intransit_qty,
1760 SUM(draft_qty) intransit_qty_draft,
1761 primary_uom_code primary_uom,
1762 SUM (value) intransit_value_b,
1763 SUM(draft_value) intransit_value_draft_b,
1764 decode(process_flag,'N',1,2), -- 1 - Discrete/ 2 - Process -- Bug fix: 5362465
1765 g_sysdate,
1766 g_sysdate,
1767 g_user_id,
1768 g_user_id,
1769 g_login_id
1770 FROM
1771 (SELECT
1772 mip.owning_organization_id organization_id,
1773 mip.owning_org_process_flag process_flag, -- Bug fix: 5362465
1774 mmt1.inventory_item_id,
1775 -1 * sum (DECODE(gtv.accounted_flag, --
1776 -- -1 * sum (DECODE('D',
1777 'D',0,DECODE (msi_fm.primary_uom_code,
1778 msi_to.primary_uom_code,
1779 decode(mmt1.transaction_action_id,
1780 22, mmt1.primary_quantity, -- Absolute value fix
1781 15, -1 * mmt1.primary_quantity, --bug 4878458
1782 mmt1.primary_quantity),
1783 DECODE (mmt1.transaction_action_id,
1784 21, DECODE (mip.fob_point,
1785 2, mmt1.primary_quantity,
1786 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)
1787 ),
1788 12, DECODE (mip.fob_point,
1789 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),
1790 mmt1.primary_quantity),
1791 22, mmt1.primary_quantity ,-- Absolute value fix ,no need of conversion ??
1792 15, -1 * mmt1.primary_quantity
1793 )))) qty,-- Bug 4901338, removed ,0
1794 -1 * sum (DECODE(gtv.accounted_flag,
1795 -- -1 * sum (DECODE('D',
1796 'D',DECODE (msi_fm.primary_uom_code,
1797 msi_to.primary_uom_code,
1798 decode(mmt1.transaction_action_id,
1799 22, mmt1.primary_quantity, --Absolute value fix
1800 15, -1 * mmt1.primary_quantity, --bug 4878458
1801 mmt1.primary_quantity),
1802 DECODE (mmt1.transaction_action_id,
1803 21, DECODE (mip.fob_point,
1804 2, mmt1.primary_quantity,
1805 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)),
1806 12, DECODE (mip.fob_point,
1807 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),
1808 mmt1.primary_quantity),
1809 22, mmt1.primary_quantity ,-- Absolute value fix no need of conversion ??
1810 15, -1 * mmt1.primary_quantity
1811 )),0)) Draft_qty,
1812 SUM(DECODE(gtv.accounted_flag,'D',base_transaction_value)) Draft_Value,
1813 SUM(DECODE(gtv.accounted_flag,'D',0,base_transaction_value)) Value, --
1814 --SUM(DECODE('D','D',base_transaction_value)) Draft_Value,
1815 --SUM(DECODE('D','D',0,base_transaction_value)) Value,
1816 DECODE (mip.owning_organization_id, msi_fm.organization_id, msi_fm.primary_uom_code
1817 ,msi_to.organization_id, msi_to.primary_uom_code) primary_uom_code,
1818
1819 TRUNC (mmt1.transaction_date) trx_date
1820 FROM opi_dbi_intr_mip_tmp mip,
1821 opi_dbi_intr_mmt_tmp mmt1,
1822 (SELECT transaction_id,
1823 sum(txn_base_value) base_transaction_value
1824 ,accounted_flag
1825 FROM gmf_transaction_valuation gtv,
1826 opi_dbi_org_le_temp tmp, --BUG 4768058
1827 opi_dbi_conc_prog_run_log col
1828 WHERE --gtv.transaction_source = 'INVENTORY' AND --bug 4870029
1829 gtv.journal_line_type = 'ITR'
1830 and col.driving_table_code='GTV'
1831 and col.etl_type= 'INVENTORY'
1832 and col.load_type= 'INIT'
1833 and gtv.ledger_id = tmp.ledger_id --BUG 4768058
1834 and gtv.legal_entity_id = tmp.legal_entity_id
1835 and gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
1836 and gtv.organization_id = tmp. organization_id
1837 AND gtv.transaction_date >= g_global_start_date
1838 AND (( gtv.accounted_flag IS NULL and gtv.final_posting_date < col.TO_BOUND_DATE )
1839 OR (gtv.accounted_flag ='D')) --
1840 GROUP BY transaction_id, accounted_flag
1841 ) gtv,
1842 mtl_system_items msi_fm,
1843 mtl_system_items msi_to
1844 WHERE mmt1.organization_id = mip.from_organization_id
1845 AND mmt1.transfer_organization_id = mip.to_organization_id
1846 AND mmt1.transaction_action_id in (15,12,22,21)
1847 AND decode(mmt1.transaction_action_id,15,12,22,21,
1848 mmt1.transaction_action_id) = mip.transaction_action_id
1849 and mmt1.ORGANIZATION_PROCESS_FLAG = mip.owning_org_process_flag--make sure only logical collected incase of D-> P and P->D
1850 AND gtv.transaction_id = mmt1.transaction_id -- No outer join is required in case of OPM
1851 AND msi_fm.inventory_item_id = mmt1.inventory_item_id
1852 AND msi_fm.organization_id = mip.from_organization_id
1853 AND msi_to.inventory_item_id = mmt1.inventory_item_id
1854 AND msi_to.organization_id = mip.to_organization_id
1855 GROUP BY
1856 mip.owning_organization_id,
1857 mip.owning_org_process_flag, -- Bug fix: 5362465
1858 mmt1.inventory_item_id,
1859 DECODE (mip.owning_organization_id, msi_fm.organization_id, msi_fm.primary_uom_code
1860 ,msi_to.organization_id, msi_to.primary_uom_code),
1861 decode (mip.fob_point,
1862 2, decode (mip.transaction_action_id,21, msi_fm.primary_uom_code,msi_to.primary_uom_code),
1863 decode (mip.transaction_action_id,12, msi_to.primary_uom_code,msi_fm.primary_uom_code)) ,
1864 trunc(mmt1.transaction_date))
1865 GROUP BY
1866 organization_id,
1867 inventory_item_id,
1868 primary_uom_code,
1869 trx_date,
1870 decode(process_flag,'N',1,2) -- Bug fix: 5362465
1871 HAVING sum(value) <> 0 or sum(qty) <> 0 OR sum(draft_value) <> 0 OR sum(draft_qty) <> 0 ;
1872
1873 if OPI_DBI_RPT_UTIL_PKG.g_pk_uom_conversion <> 1 then
1874 Raise UOM_CONV_ERROR;
1875 end if;
1876 commit;
1877
1878 l_debug_msg := 'Inserted into staging table opi_dbi_intransit_stg for OPM Post R12 - ' || SQL%ROWCOUNT || ' rows. ';
1879 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1880
1881 l_debug_msg := 'End of collecting intransit ODM initial load. ';
1882 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1883
1884 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1885 l_debug_msg := 'Exit from GET_INTRANSIT_INITIAL_LOAD ';
1886 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1887 END IF;
1888
1889 EXCEPTION
1890 WHEN UOM_CONV_ERROR then
1891 l_debug_msg := 'UOM conversion not found ' || SQLcode || ' - ' ||SQLERRM;
1892 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1893 RAISE;
1894
1895 WHEN OTHERS THEN
1896 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
1897 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1898 RAISE;
1899
1900 END GET_INTRANSIT_INITIAL_LOAD ;
1901
1902
1903 -------------------------------------------------------------------------------
1904
1905 PROCEDURE EXTRACT_INVENTORY_TXN_QTY
1906 IS
1907 l_stmt_num NUMBER;
1908 l_debug_msg VARCHAR2(1000);
1909 l_proc_name VARCHAR2 (60);
1910 l_debug_mode VARCHAR2(1);
1911 l_module_name VARCHAR2 (30);
1912 BEGIN
1913
1914 l_proc_name := 'EXTRACT_INVENTORY_TXN_QTY';
1915 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1916 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
1917
1918 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1919 l_debug_msg := 'Entered Into EXTRACT_INVENTORY_TXN_QTY ';
1920 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1921 END IF;
1922
1923 /* Extract all records from MMT in quantity staging table (OPI_DBI_INV_ONH_QTY_stg) ,
1924 such that transaction_date >= GSD and TRANSACTION_ID >= FROM_BOUND_ID in LOG table for this ETL.
1925 All Quantities are extracted from MMT but these are marked using push_to_fact_flag 'Y' or 'N'
1926 depending upon the to_bound_id for discrete orgs.
1927
1928 For process orgs all the quantity that is costed is set for pushed to the fact and uncosted
1929 quantity is set for push to fact flag as 'N'.
1930
1931 NOTE: It is assumed that there is not much time difference between the LOG Table
1932 update and Extraction of quantity in this step.
1933 It is assumed that database takes snapshot of underlying tables at the start of this step.
1934 MMT and MTA data is in synch as data is extracted for same transaction id ranges.
1935
1936 MMT and GTV data is in synch as there is no bound on MMT and from GTV data is taken for all
1937 draft records and final_posted_date < timestamp as of stamping the log tables.
1938
1939 Now it is possible there are some new transactions coming in between log table
1940 1.2.2 and 1.2.5 and also getting draft posted.
1941 These transactions will be picked up in step 1.2.5. However the next incremental load will
1942 take care of it by collecting quantity for those records as draft records are always reprocessed.
1943 */
1944
1945 l_stmt_num :=10;
1946 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1947 l_debug_msg := 'Inserting data into OPI_DBI_ONH_QTY_STG ';
1948 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1949 END IF;
1950 --execute immediate 'alter session force parallel query parallel ' || g_degree;
1951
1952 INSERT /*+ append parallel(OPI_DBI_ONH_QTY_STG) */
1953 INTO OPI_DBI_ONH_QTY_STG
1954 (organization_id,
1955 subinventory_code,
1956 cost_group_id,
1957 inventory_item_id,
1958 transaction_date,
1959 onhand_qty,
1960 onhand_qty_draft,
1961 push_to_fact_flag,
1962 source,
1963 transaction_source)
1964 -- staging tables do not have who columns
1965 (SELECT /*+ use_hash(mta1) use_hash(mmt1) */
1966 mmt.organization_id,
1967 nvl(mmt.subinventory_code,-1),
1968 NVL(mmt.cost_group_id, mp.default_cost_group_id),
1969 mmt.inventory_item_id,
1970 TRUNC(mmt.transaction_date) transaction_date,
1971 -- if its process org then consider final accounted quantity for discrete consider all
1972 SUM(DECODE(mp.process_enabled_flag,'Y'
1973 ,DECODE(mmt.opm_costed_flag --
1974 --,DECODE('D'
1975 ,null,mmt.primary_quantity,0)
1976 ,mmt.primary_quantity)) onhand_qty,
1977 -- if its process org then consider draft accounted quantity. for discrete consider its always zero
1978 SUM(DECODE(mp.process_enabled_flag,'Y'
1979 ,DECODE(mmt.opm_costed_flag,'D',
1980 primary_quantity,'N', primary_quantity, 0),0)) onhand_qty_draft, --Qty extracted for opm_costed_flag 'N' and 'D'
1981 CASE WHEN mp.process_enabled_flag = 'Y' THEN -- if its a process org
1982 DECODE(mmt.opm_costed_flaG,'N','N','Y') -- if its costed then push to fact else Not --
1983 --DECODE('D','N','N','Y') -- if its costed then push to fact else Not
1984 WHEN mmt.transaction_id < prl.to_bound_id THEN 'Y'
1985 ELSE 'N'
1986 END push_to_fact_flag,
1987 DECODE(mp.process_enabled_flag,'N',1,'Y',2,1) source,
1988 'MMT' transaction_source
1989 FROM MTL_MATERIAL_TRANSACTIONS MMT
1990 -- ,MTL_SYSTEM_ITEMS MSI
1991 ,OPI_DBI_CONC_PROG_RUN_LOG PRL
1992 ,MTL_PARAMETERS MP
1993 WHERE prl.driving_table_code (+)= 'MMT' --bug 4704813
1994 AND prl.etl_type (+)= 'INVENTORY'
1995 AND prl.load_type (+)= 'INIT'
1996 AND mmt.transaction_id >= nvl(prl.from_bound_id,0) --bug 4704813
1997 -- there is no condition on to_bound as we get all quantity from mmt but push to fact flag is set to No based
1998 -- on to_bound_id in select clause
1999 AND mmt.organization_id = prl.bound_level_entity_id (+) -- outer join as process orgs are not available in log table
2000 AND mmt.transaction_date >= g_global_start_date -- to avoid any backdated transactions
2001 AND mmt.organization_id = mp.organization_id
2002 -- AND mmt.organization_id = msi.organization_id
2003 -- AND mmt.inventory_item_id = msi.inventory_item_id
2004 AND mmt.organization_id = NVL(mmt.owning_organization_id, mmt.organization_id)
2005 AND NVL(mmt.owning_tp_type,2) = 2
2006 AND NVL(mmt.logical_transaction,0) <> 1
2007 AND mmt.transaction_type_id not in (73,25,26,90,91,92,55,56,57,58,87,88,89,24,28,80) --
2008 AND MMT.TRANSACTION_ACTION_ID NOT IN (24)
2009 -- or should we use AND MMT.TRANSACTION_ACTION_ID NOT IN (5,30,24,40,41,42,50,51,52)
2010 -- 82,83,84 inventory lot split/merge/translate are getting excluded by second condition
2011 GROUP BY
2012 mmt.organization_id,
2013 mmt.subinventory_code,
2014 NVL(mmt.cost_group_id, mp.default_cost_group_id),
2015 mmt.inventory_item_id,
2016 TRUNC(mmt.transaction_date) ,
2017 mp.process_enabled_flag
2018 , mmt.opm_costed_flag
2019 ,mmt.transaction_id
2020 ,prl.to_bound_id
2021 ,'MMT'
2022 UNION ALL
2023 SELECT /*+ use_hash(mta1) use_hash(mmt1) */
2024 moq.organization_id,
2025 nvl(moq.subinventory_code,-1) subinventory_code,
2026 nvl(moq.cost_group_id, mp.default_cost_group_id),
2027 moq.inventory_item_id,
2028 null transaction_date,
2029 sum(moq.transaction_quantity) onhand_qty,
2030 null onhand_qty_draft,
2031 'N' push_to_fact_flag,
2032 NULL source,
2033 'MOQ' transaction_source
2034 FROM MTL_ONHAND_QUANTITIES MOQ
2035 ,MTL_PARAMETERS MP
2036 ,MTL_SYSTEM_ITEMS MSI
2037 WHERE moq.organization_id = mp.organization_id
2038 AND moq.organization_id = msi.organization_id
2039 AND moq.inventory_item_id = msi.inventory_item_id
2040 GROUP BY moq.organization_id,
2041 NVL(moq.subinventory_code,-1),
2042 NVL(moq.cost_group_id, mp.default_cost_group_id),
2043 moq.inventory_item_id);
2044
2045 l_debug_msg := 'Inserted into staging table OPI_DBI_ONH_QTY_STG - ' || SQL%ROWCOUNT || ' rows. ';
2046 OPI_DBI_BOUNDS_PKG.write(g_pkg_name, l_proc_name,l_stmt_num, l_debug_msg);
2047
2048 commit;
2049
2050 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2051 l_debug_msg := 'Exit from GET_INTRANSIT_INITIAL_LOAD ';
2052 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2053 END IF;
2054
2055 EXCEPTION
2056 WHEN OTHERS THEN
2057 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
2058 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
2059 RAISE;
2060 END EXTRACT_INVENTORY_TXN_QTY;
2061
2062
2063 -------------------------------------------------------------------------------
2064 --Extract Values in OPI_DBI_ONHAND_STG this has further separate
2065 --unionalls explained below:
2066 --MTA - all transactions for line type 1 and 7 will be taken. Based on
2067 --transaction_id range from log tables it will be marked as push to fact or not.
2068 --GTV - all transactions will be taken prior to the to_bound_date in bounds
2069 --table. Everything will be set for push_to_fact_flag as Yes. Data will be put
2070 --into draft or perm columns depending upon the accounted_flag.
2071 --WPB - all current data from wpb is taken.
2072 --WTA - All current data is taken. But it is categorized to be put into fact or
2073 --not based on the bound.
2074 --Extract to get current OPM Balance from GTV
2075
2076 /* EXTRACT_INVENTORY_TXN_VALUE
2077
2078 Gets the Onhand, WIP Inventory transactions later than global start date.
2079
2080 Author Date Action
2081 Suhasini 09/11/2006 Bug Fix: 5490217: Corrected subinventory_code for
2082 Direct Org transfer between different subinventories
2083 when transferred from std to avg costed organizations
2084 Forward ported from 11.5.10 Bug 5403832
2085 */
2086
2087 PROCEDURE EXTRACT_INVENTORY_TXN_VALUE
2088 IS
2089 l_stmt_num NUMBER;
2090 l_debug_msg VARCHAR2(1000);
2091 l_proc_name VARCHAR2 (60);
2092 l_debug_mode VARCHAR2(1);
2093 l_module_name VARCHAR2 (30);
2094 BEGIN
2095
2096 l_proc_name := 'EXTRACT_INVENTORY_TXN_VALUE';
2097 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
2098 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
2099
2100 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2101 l_debug_msg := 'Enter Into EXTRACT_INVENTORY_TXN_VALUE ';
2102 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2103 END IF;
2104 l_stmt_num :=10;
2105 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2106 l_debug_msg := 'Inserting data into OPI_DBI_ONHAND_STG ';
2107 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2108 END IF;
2109
2110 INSERT /*+ append parallel(OPI_DBI_ONH_QTY_STG) */
2111 INTO OPI_DBI_ONHAND_STG
2112 (organization_id,
2113 subinventory_code,
2114 inventory_item_id,
2115 transaction_date,
2116 onhand_value_b_draft,
2117 onhand_value_b,
2118 wip_value_b_draft,
2119 wip_value_b,
2120 source,
2121 push_to_fact_flag,
2122 transaction_source)
2123 -- note: staging tables do not have who columns
2124 (SELECT
2125 mta.organization_id,
2126 decode(mmt.transaction_action_id,
2127 2,decode(sign(mta.primary_quantity),-1,mmt.subinventory_code,mmt.transfer_subinventory),
2128 3, decode(sign(mta.primary_quantity), sign(mmt.primary_quantity), mmt.subinventory_code,
2129 decode(sign(mta.primary_quantity),-1,mmt.subinventory_code, mmt.transfer_subinventory)), -- Bug 5490217
2130 28,decode(sign(mta.primary_quantity), -1, mmt.subinventory_code, mmt.transfer_subinventory),
2131 24, nvl(mmt.subinventory_code,'-1'),mmt.subinventory_code) subinventory_code,
2132 -- in case its wip related record then
2133 decode(mta.accounting_line_type,7,nvl(we.primary_item_id,-1),mta.inventory_item_id) inventory_item_id,
2134 trunc(mta.transaction_date) transaction_date,
2135 0 onhand_value_b_draft,
2136 sum(decode(mta.accounting_line_type,1,mta.base_transaction_value,0)) onhand_value_b,
2137 0 wip_value_b_draft,
2138 sum(decode(mta.accounting_line_type,'7',
2139 decode(we.entity_type,1,mta.base_transaction_value,
2140 2,mta.base_transaction_value,
2141 3,mta.base_transaction_value,
2142 4,mta.base_transaction_value,
2143 5,mta.base_transaction_value,
2144 8,mta.base_transaction_value,0),0)) wip_value_b,
2145 1 source,
2146 case when mta.transaction_id < prl.to_bound_id then 'Y'
2147 else 'N'
2148 end push_to_fact_flag,
2149 'MTA' transaction_source
2150 FROM mtl_transaction_accounts mta
2151 ,mtl_material_transactions mmt
2152 ,wip_entities we
2153 ,opi_dbi_conc_prog_run_log prl
2154 WHERE prl.driving_table_code = 'MMT'
2155 AND prl.etl_type = 'INVENTORY'
2156 AND prl.load_type = 'INIT'
2157 AND mta.transaction_id >= prl.from_bound_id -- changing bound to MTA bug 4576545
2158 -- there is no condition on to_bound as we get all value from mmt-mta but push to fact flag is set to No based
2159 -- on to_bound_id in select clause
2160 AND mta.organization_id = prl.bound_level_entity_id (+) -- outer join as process orgs are not available in log table
2161 -- changing bound to MTA bug 4576545
2162 AND mmt.transaction_date >= g_global_start_date -- to avoid any backdated transactions
2163 AND mmt.transaction_id = mta.transaction_id
2164 AND mta.accounting_line_type in (1,7)
2165 -- in case of transaction source type id is 5 then join with wip entities table to get the wip assembly id
2166 AND decode(mta.accounting_line_type,7,mta.transaction_source_id,null) = we.wip_entity_id(+)
2167 GROUP BY
2168 mta.organization_id,
2169 decode(mmt.transaction_action_id,
2170 2,decode(sign(mta.primary_quantity),-1,mmt.subinventory_code,mmt.transfer_subinventory),
2171 3, decode(sign(mta.primary_quantity), sign(mmt.primary_quantity), mmt.subinventory_code,
2172 decode(sign(mta.primary_quantity),-1,mmt.subinventory_code, mmt.transfer_subinventory)), -- Bug 5490217
2173 28,decode(sign(mta.primary_quantity), -1, mmt.subinventory_code, mmt.transfer_subinventory),
2174 24, nvl(mmt.subinventory_code,'-1'),mmt.subinventory_code),
2175 -- in case its wip related record then
2176 decode(mta.accounting_line_type,7,nvl(we.primary_item_id,-1),mta.inventory_item_id),
2177 trunc(mta.transaction_date),
2178 case when mta.transaction_id < prl.to_bound_id then 'Y'
2179 else 'N'
2180 end
2181 UNION ALL
2182 /* if we implement the commented code to get OPM open job balance here itself there are some changes required
2183 to get_wip_balance api */
2184 -- WIP value decodes
2185 --1. WIP value is shown against the product and not the ingredient
2186 --2. In OPM one job can yield multiple products.
2187 --So cost allocation has to be done. GTV is joined with gmdtl only
2188 --for WIP records. For INV records the query should nto join
2189 --with gmdtl but still return 1 records and hence the outer join.
2190 --gmdlt can have multiple records for a doc id based on how many
2191 --main product the job can yield. So for jobs where multiple products
2192 --are yielded there is a cartesian product.
2193 --gtv.line_type is 1 for product yields, -1 for ingredient
2194 --issues and 2 for co-products.
2195 --For REsources? so when there are multiple products
2196 --and gtv.line_type is other than 1 its multiplied by
2197 --cost allocation factor.
2198 --for product lines its multiplied by 1.
2199 --when gtv.line_type =1 and gtv.inventory_item_id is not same
2200 --as gmdtl then its multiplied by zero to net affect the cartesian
2201 SELECT
2202 gtv.organization_id,
2203 nvl(gtv.subinventory_code,-1) subinventory_code,
2204 decode(gtv.journal_line_type,'WIP',gmdtl.inventory_item_id,gtv.inventory_item_id) inventory_item_id,
2205 trunc(gtv.transaction_date) transaction_date,
2206 sum(onhand_val_b_draft),
2207 sum(onhand_val_b),
2208 sum(wip_val_b_draft*(decode(gtv.line_type,1,
2209 decode(gtv.inventory_item_id,gmdtl.inventory_item_id,1,0), --Changed to inv item id from item id.Old cols not used is R12.
2210 -1,gmdtl.cost_alloc,
2211 2,gmdtl.cost_alloc,
2212 gmdtl.cost_alloc)))
2213 wip_value_b_draft,
2214 sum(wip_val_b*(decode(gtv.line_type,1,
2215 decode(gtv.inventory_item_id,gmdtl.inventory_item_id,1,0), --Changed to inv item id from item id.Old cols not used is R12.
2216 -1,gmdtl.cost_alloc,
2217 2,gmdtl.cost_alloc,
2218 gmdtl.cost_alloc)))
2219 wip_value_b,
2220 2 source,
2221 CASE WHEN gtv.final_posting_date < prl.to_bound_date
2222 OR gtv.final_posting_date IS NULL /* for draft posted -- recs */
2223 THEN 'Y' ELSE 'N' END push_to_fact_flag, --
2224 --'Y' push_to_fact_flag, --
2225 'GTV' transaction_source
2226 -- decode(gbh,gl_posted_ind,0,'OPJ','GTV') transaction_source
2227 FROM (select gtv.organization_id,
2228 decode(gtv.journal_line_type,'INV',gtv.subinventory_code,NULL) subinventory_code,
2229 gtv.line_type, -- amit has added this
2230 gtv.inventory_item_id,
2231 trunc(gtv.transaction_date) transaction_date,
2232 decode(gtv.journal_line_type,'WIP',gtv.doc_id,NULL)
2233 doc_id,--Gtv.doc_id is populated in inner select only when journal_line_type is WIP.
2234 gtv.journal_line_type,
2235 gtv.event_class_code,
2236 gtv.final_posting_date,
2237 sum(decode(journal_line_type,'INV',
2238 decode(gtv.accounted_flag,'D',txn_base_value,0),0)) --
2239 --decode('D','D',txn_base_value,0),0))
2240 onhand_val_b_draft,
2241 sum(decode(journal_line_type,'INV',
2242 decode(gtv.accounted_flag,NULL,txn_base_value,0),0)) --
2243 --decode('D',NULL,txn_base_value,0),0))
2244 onhand_val_b,
2245 sum(decode(journal_line_type,'WIP',
2246 decode(gtv.accounted_flag,'D',txn_base_value,0),0)) --
2247 --decode('D','D',txn_base_value,0),0))
2248 wip_val_b_draft,
2249 sum(decode(journal_line_type,'WIP',
2250 decode(gtv.accounted_flag,NULL,txn_base_value,0),0)) --
2251 --decode('D',NULL,txn_base_value,0),0))
2252 wip_val_b
2253 from gmf_transaction_valuation gtv,
2254 opi_dbi_org_le_temp tmp --bug 4768058
2255 where --gtv.final_posting_date >= g_global_start_date -- Not required
2256 --and --
2257 gtv.journal_line_type IN ('WIP','INV')
2258 and gtv.transaction_date >= g_global_start_date
2259 and gtv.ledger_id = tmp.ledger_id --bug 4768058
2260 and gtv.legal_entity_id = tmp.legal_entity_id
2261 and gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
2262 and gtv.organization_id = tmp. organization_id
2263 group by gtv.organization_id,
2264 decode(gtv.journal_line_type,'INV',gtv.subinventory_code,NULL),
2265 gtv.line_type,
2266 gtv.inventory_item_id,
2267 trunc(gtv.transaction_date),
2268 decode(gtv.journal_line_type,'WIP',gtv.doc_id,NULL),
2269 gtv.journal_line_type,
2270 gtv.event_class_code,
2271 gtv.final_posting_date) gtv,
2272 gme_material_details gmdtl,
2273 opi_dbi_conc_prog_run_log prl
2274 -- gme_batch_headers gbh
2275 WHERE gtv.doc_id = gmdtl.batch_id(+)
2276 AND nvl(gmdtl.line_type,1) = 1 -- (MK) identified issue during UT. Need to be reviewed with Vikas/David
2277 AND prl.driving_table_code = 'GTV'
2278 AND prl.etl_type = 'INVENTORY'
2279 AND prl.load_type = 'INIT'
2280 -- AND gtv.document_id = gbh.batch_id(+)
2281 GROUP BY gtv.organization_id,
2282 decode(gtv.journal_line_type,'WIP',gmdtl.inventory_item_id,gtv.inventory_item_id),
2283 gtv.transaction_date,
2284 CASE WHEN gtv.final_posting_date < prl.to_bound_date --
2285 OR gtv.final_posting_date IS NULL /* for draft posted
2286 recs */ THEN 'Y' ELSE 'N' END,
2287 --'Y', --
2288 nvl(gtv.subinventory_code,-1)
2289 UNION ALL
2290 SELECT wta.organization_id,
2291 '-1' subinventory_code,
2292 nvl(we.primary_item_id, -1) inventory_item_id,
2293 trunc(wta.transaction_date) transaction_date,
2294 null onhand_value_b_draft,
2295 null onhand_value_b,
2296 null wip_value_b_draft,
2297 sum(nvl(wta.base_transaction_value,0)) wip_value_b,
2298 1 source,
2299 'Y' push_to_fact_flag,
2300 'WTA' transaction_source
2301 FROM wip_transaction_accounts wta,
2302 wip_entities we,
2303 opi_dbi_conc_prog_run_log prl
2304 WHERE prl.etl_type = 'INVENTORY'
2305 AND prl.driving_table_code = 'WTA'
2306 AND prl.load_type = 'INIT'
2307 AND wta.accounting_line_type = 7
2308 AND wta.transaction_id >= prl.from_bound_id
2309 AND wta.transaction_id < prl.to_bound_id
2310 AND wta.transaction_date >= g_global_start_date -- to avoid any backdated txns before GSD
2311 AND we.wip_entity_id = wta.wip_entity_id
2312 AND we.entity_type in (1, 2, 3, 4, 5, 8)
2313 GROUP BY
2314 wta.organization_id,
2315 we.primary_item_id,
2316 wta.transaction_date
2317 HAVING sum(wta.base_transaction_value) <> 0
2318 UNION ALL
2319 SELECT
2320 wpb.organization_id organization_id,
2321 '-1' subinventory_code,
2322 we.primary_item_id inventory_item_id, -- rows with item_id null are not selected.
2323 g_global_start_date transaction_date,
2324 null onhand_value_b_draft,
2325 null onhand_value_b,
2326 null wip_value_b_draft,
2327 sum(nvl(tl_resource_in,0)
2328 + nvl(tl_overhead_in,0)
2329 + nvl(tl_outside_processing_in,0)
2330 + nvl(pl_material_in,0)
2331 + nvl(pl_material_overhead_in,0)
2332 + nvl(pl_resource_in,0)
2333 + nvl(pl_overhead_in,0)
2334 + nvl(pl_outside_processing_in,0)
2335 - nvl(tl_material_out,0)
2336 - nvl(tl_material_overhead_out,0)
2337 - nvl(tl_resource_out,0)
2338 - nvl(tl_overhead_out,0)
2339 - nvl(tl_outside_processing_out,0)
2340 - nvl(pl_material_out,0)
2341 - nvl(pl_material_overhead_out,0)
2342 - nvl(pl_resource_out,0)
2343 - nvl(pl_overhead_out,0)
2344 - nvl(pl_outside_processing_out,0)
2345 - nvl(tl_material_var,0)
2346 - nvl(tl_material_overhead_var,0)
2347 - nvl(tl_resource_var,0)
2348 - nvl(tl_outside_processing_var,0)
2349 - nvl(tl_overhead_var,0)
2350 - nvl(pl_material_var,0)
2351 - nvl(pl_material_overhead_var,0)
2352 - nvl(pl_resource_var,0)
2353 - nvl(pl_overhead_var,0)
2354 - nvl(pl_outside_processing_var,0)) wip_value_b,
2355 1,
2356 'N',
2357 'WPB' transaction_source
2358 FROM wip_period_balances wpb,
2359 wip_entities we
2360 WHERE wpb.wip_entity_id = we.wip_entity_id
2361 AND we.entity_type in (1, 2, 3, 4, 5, 8)
2362 AND we.primary_item_id IS NOT NULL
2363 GROUP BY
2364 wpb.organization_id ,
2365 we.primary_item_id
2366 UNION ALL
2367 --the query should be driven by gbh with index on * gl_posted_ind.
2368 --Otherwise it may end up doing FTS of gtv * which may be expensive
2369 SELECT gtv.organization_id,
2370 '-1' subinventory_code,
2371 --gmdtl.inventory_item_id,
2372 decode(gtv.journal_line_type,'WIP',gmdtl.inventory_item_id,gtv.inventory_item_id) inventory_item_id,
2373 trunc(gtv.transaction_date) transaction_date,
2374 0 onhand_value_b_draft ,
2375 0 onhand_value_b,
2376 sum (decode(accounted_flag,'D',gtv.txn_base_value *
2377 decode(gtv.line_type,1,decode(gtv.inventory_item_id,gmdtl.inventory_item_id,1,0), --Changed to inv item id from item id.Old cols not used is R12.
2378 -1, gmdtl.cost_alloc,
2379 2, gmdtl.cost_alloc,
2380 gmdtl.cost_alloc),0)) wip_value_b_draft,
2381 sum (decode(accounted_flag, 'D', 0, gtv.txn_base_value *
2382 decode(gtv.line_type,1,decode(gtv.inventory_item_id,gmdtl.inventory_item_id,1,0), --Changed to inv item id from item id.Old cols not used is R12.
2383 -1, gmdtl.cost_alloc,
2384 2, gmdtl.cost_alloc,
2385 gmdtl.cost_alloc))) wip_value_b,
2386 2 source,
2387 'N' push_to_fact_flag,
2388 'OPJ' transaction_source
2389 FROM gme_batch_header gbh,
2390 gmf_transaction_valuation gtv,
2391 opi_dbi_org_le_temp tmp, --Bug 4768058
2392 gme_material_details gmdtl
2393 WHERE gtv.journal_line_type = 'WIP'
2394 AND nvl(gtv.accounted_flag,'F') <> 'N'
2395 AND gtv.transaction_date >= g_global_start_date
2396 AND gtv.doc_id = gmdtl.batch_id
2397 AND gmdtl.line_type = 1
2398 AND gbh.batch_id = gtv.doc_id
2399 AND gbh.gl_posted_ind = 0
2400 and gtv.ledger_id = tmp.ledger_id --bug 4768058
2401 and gtv.legal_entity_id = tmp.legal_entity_id
2402 and gtv.valuation_cost_type_id = tmp.valuation_cost_type_id
2403 and gtv.organization_id = tmp. organization_id
2404 GROUP BY
2405 gtv.organization_id,
2406 decode(gtv.journal_line_type,'WIP',gmdtl.inventory_item_id,gtv.inventory_item_id),
2407 trunc(gtv.transaction_date));
2408
2409 l_debug_msg := 'Inserted into staging table OPI_DBI_ONHAND_STG - ' || SQL%ROWCOUNT || ' rows. ';
2410 OPI_DBI_BOUNDS_PKG.write(g_pkg_name, l_proc_name,l_stmt_num, l_debug_msg);
2411
2412 commit;
2413
2414 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2415 l_debug_msg := 'Exit from EXTRACT_INVENTORY_TXN_VALUE ';
2416 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2417 END IF;
2418
2419 EXCEPTION
2420 WHEN OTHERS THEN
2421 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
2422 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
2423 RAISE;
2424 END EXTRACT_INVENTORY_TXN_VALUE;
2425
2426 -------------------------------------------------------------------------------
2427 -- This procedure merges data from various staging tables populated in
2428 -- initial load.
2429 -- 1. OPI_DBI_ONH_QTY_STG - onhand quantity
2430 -- 2. provide each table and what all it contains
2431 -- 3. provide conversion rate and costing info as well for inception to balance.
2432
2433 --In this procedure all all data from staging table is merged
2434 -- into OPI_DBI_INV_VALUE_F fact table
2435 PROCEDURE MERGE_INITIAL_LOAD
2436 IS
2437 l_rows NUMBER := 0;
2438 l_stmt_num NUMBER;
2439 l_debug_msg VARCHAR2(1000);
2440 l_proc_name VARCHAR2 (60);
2441 l_debug_mode VARCHAR2(1);
2442 l_module_name VARCHAR2 (30);
2443 BEGIN
2444
2445 l_proc_name := 'MERGE_INITIAL_LOAD';
2446 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
2447 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
2448
2449 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2450 l_debug_msg := 'Entered Into MERGE_INITIAL_LOAD';
2451 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2452 END IF;
2453
2454 l_stmt_num := 10;
2455 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2456 l_debug_msg := 'Calling Clean staging table PRE INIT';
2457 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2458 END IF;
2459
2460 insert /*+ append parallel(opi_dbi_inv_value_f) */ into OPI_DBI_INV_VALUE_F
2461 ( operating_unit_id,
2462 organization_id,
2463 subinventory_code,
2464 inventory_item_id,
2465 transaction_date,
2466 onhand_qty,
2467 intransit_qty,
2468 primary_uom,
2469 onhand_value_b,
2470 intransit_value_b,
2471 wip_value_b,
2472 conversion_rate,
2473 sec_conversion_rate,
2474 ONHAND_QTY_DRAFT,
2475 INTRANSIT_QTY_DRAFT,
2476 ONHAND_VALUE_B_DRAFT,
2477 INTRANSIT_VALUE_B_DRAFT,
2478 WIP_VALUE_B_DRAFT,
2479 source,
2480 created_by,
2481 last_update_login,
2482 creation_date,
2483 last_updated_by,
2484 last_update_date
2485 )
2486 select /*+ use_hash(rate, s) parallel(s) parallel(rate) */
2487 NULL operating_unit_id,
2488 s.organization_id,
2489 nvl(s.subinventory_code,-1),
2490 s.inventory_item_id,
2491 s.transaction_date,
2492 s.onhand_qty + s.ONHAND_QTY_DRAFT onhand_qty ,
2493 s.intransit_qty + s.INTRANSIT_QTY_DRAFT intransit_qty,
2494 s.primary_uom,
2495 s.onhand_value_b +s.ONHAND_VALUE_B_DRAFT onhand_value_b,
2496 s.intransit_value_b + s.INTRANSIT_VALUE_B_DRAFT intransit_value_b ,
2497 s.wip_value_b + s.WIP_VALUE_B_DRAFT wip_value_b,
2498 rate.conversion_rate,
2499 rate.sec_conversion_rate,
2500 s.ONHAND_QTY_DRAFT,
2501 s.INTRANSIT_QTY_DRAFT,
2502 s.ONHAND_VALUE_B_DRAFT,
2503 s.INTRANSIT_VALUE_B_DRAFT,
2504 s.WIP_VALUE_B_DRAFT,
2505 source,
2506 g_user_id,
2507 g_login_id,
2508 sysdate,
2509 g_user_id,
2510 sysdate
2511 FROM
2512 (SELECT /*+ parallel(activity) */
2513 activity.organization_id,
2514 activity.subinventory_code,
2515 activity.inventory_item_id,
2516 activity.transaction_date,
2517 nvl(SUM(onhand_qty),0) onhand_qty,
2518 nvl(SUM(intransit_qty),0) intransit_qty,
2519 MIN(msi.primary_uom_code) primary_uom,
2520 nvl(SUM(onhand_value_b),0) onhand_value_b,
2521 nvl(SUM(intransit_value_b),0) intransit_value_b,
2522 nvl(SUM(wip_value_b),0) wip_value_b,
2523 nvl(SUM(onhand_qty_draft),0) onhand_qty_draft,
2524 nvl(SUM(INTRANSIT_QTY_DRAFT),0) INTRANSIT_QTY_DRAFT,
2525 nvl(SUM(ONHAND_VALUE_B_DRAFT),0) ONHAND_VALUE_B_DRAFT,
2526 nvl(SUM(INTRANSIT_VALUE_B_DRAFT),0) INTRANSIT_VALUE_B_DRAFT,
2527 nvl(SUM(WIP_VALUE_B_DRAFT),0) WIP_VALUE_B_DRAFT,
2528 activity.source
2529 FROM
2530 (SELECT /*+ parallel(opi_dbi_onhand_stg) */
2531 organization_id,
2532 subinventory_code,
2533 inventory_item_id,
2534 transaction_date,
2535 0 onhand_qty,
2536 0 intransit_qty,
2537 primary_uom,
2538 onhand_value_b,
2539 0 intransit_value_b,
2540 wip_value_b,
2541 0 onhand_qty_draft,
2542 0 intransit_qty_draft,
2543 onhand_value_b_draft onhand_value_b_draft,
2544 0 intransit_value_b_draft,
2545 wip_value_b_draft wip_value_b_draft,
2546 source
2547 FROM OPI_DBI_ONHAND_STG
2548 where push_to_fact_flag = 'Y'
2549 UNION ALL
2550 select /*+ parallel(OPI_DBI_ONH_QTY_STG) */
2551 fact.organization_id,
2552 fact.subinventory_code,
2553 fact.inventory_item_id,
2554 fact.transaction_date,
2555 onhand_qty,
2556 0 intransit_qty,
2557 NULL primary_uom,
2558 0 onhand_value_b,
2559 0 intransit_value_b,
2560 0 wip_value_b,
2561 onhand_qty_draft onhand_qty_draft,
2562 0 intransit_qty_draft,
2563 0 onhand_value_b_draft,
2564 0 intransit_value_b_draft,
2565 0 wip_value_b_draft,
2566 source
2567 FROM OPI_DBI_ONH_QTY_STG fact
2568 WHERE push_to_fact_flag = 'Y'
2569 UNION All
2570 select /*+ parallel(opi_dbi_intransit_stg) */
2571 organization_id,
2572 NULL,
2573 inventory_item_id,
2574 transaction_date,
2575 0 onhand_qty,
2576 intransit_qty,
2577 primary_uom,
2578 0 onhand_value_b,
2579 intransit_value_b,
2580 0 wip_value_b,
2581 0 onhand_qty_draft,
2582 intransit_qty_draft intransit_qty_draft,
2583 0 onhand_value_b_draft,
2584 intransit_value_draft_b intransit_value_b_draft,
2585 0 wip_value_b_draft,
2586 source
2587 from OPI_DBI_INTRANSIT_STG
2588 union all
2589 select /*+ parallel(OPI_DBI_INV_BEG_STG) */
2590 fact.organization_id,
2591 fact.subinventory_code,
2592 fact.inventory_item_id,
2593 fact.transaction_date,
2594 onhand_qty,
2595 intransit_qty,
2596 NULL primary_uom,
2597 onhand_value_b,
2598 intransit_value_b,
2599 wip_value_b,
2600 0 onhand_qty_draft,
2601 0 intransit_qty_draft,
2602 0 onhand_value_b_draft,
2603 0 intransit_value_b_draft,
2604 0 wip_value_b_draft,
2605 decode(mp.process_enabled_flag,'Y',2,1) source
2606 FROM OPI_DBI_INV_BEG_STG fact,
2607 mtl_parameters mp
2608 WHERE fact.organization_id =mp.organization_id
2609 union all
2610 select /*+ parallel(OPI_DBI_OPM_INV_STG) */
2611 organization_id,
2612 subinventory_code,
2613 inventory_item_id,
2614 transaction_date,
2615 onhand_qty,
2616 intransit_qty,
2617 primary_uom,
2618 onhand_value_b,
2619 intransit_value_b,
2620 wip_value_b,
2621 0 onhand_qty_draft,
2622 0 intransit_qty_draft,
2623 0 onhand_value_b_draft,
2624 0 intransit_value_b_draft,
2625 0 wip_value_b_draft,
2626 3 source
2627 FROM opi_dbi_opm_inv_stg
2628 ) activity,
2629 mtl_system_items msi
2630 WHERE activity.organization_id = msi.organization_id
2631 AND activity.inventory_item_id =msi.inventory_item_id
2632 group by
2633 activity.organization_id,
2634 activity.subinventory_code,
2635 activity.inventory_item_id,
2636 activity.transaction_date,
2637 activity.source
2638 having
2639 nvl(SUM(onhand_qty),0) <> 0
2640 OR nvl(SUM(intransit_qty),0) <> 0
2641 OR nvl(SUM(onhand_value_b),0) <>0
2642 OR nvl(SUM(intransit_value_b),0) <> 0
2643 OR nvl(SUM(wip_value_b),0) <> 0
2644 OR nvl(SUM(onhand_qty_draft),0) <> 0
2645 OR nvl(SUM(INTRANSIT_QTY_DRAFT),0) <> 0
2646 OR nvl(SUM(ONHAND_VALUE_B_DRAFT),0) <> 0
2647 OR nvl(SUM(INTRANSIT_VALUE_B_DRAFT),0) <> 0
2648 OR nvl(SUM(WIP_VALUE_B_DRAFT),0)<> 0
2649 ) s,
2650 (select /*+ no_merge parallel(rates) */
2651 organization_id,
2652 transaction_date,
2653 conversion_rate,
2654 sec_conversion_rate
2655 from OPI_DBI_CONVERSION_RATES
2656 ) rate
2657 where s.organization_id = rate.organization_id
2658 and s.transaction_date = rate.transaction_date;
2659
2660 l_debug_msg := 'Inserted into staging table OPI_DBI_INV_VALUE_F - ' || SQL%ROWCOUNT || ' rows. ';
2661 OPI_DBI_BOUNDS_PKG.write(g_pkg_name, l_proc_name,l_stmt_num, l_debug_msg);
2662
2663 commit;
2664
2665 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2666 l_debug_msg := 'Exit from MERGE INITIAL LOAD';
2667 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2668 END IF;
2669
2670 --execute immediate 'alter session disable parallel query';
2671
2672 EXCEPTION
2673 WHEN OTHERS THEN
2674 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
2675 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
2676 RAISE;
2677
2678 END MERGE_INITIAL_LOAD;
2679
2680 -------------------------------------------------------------------------------
2681
2682 PROCEDURE RUN_DISCRETE_FIRST_ETL
2683 IS
2684 l_stmt_num NUMBER;
2685 l_debug_msg VARCHAR2(1000);
2686 l_proc_name VARCHAR2 (60);
2687 l_debug_mode VARCHAR2(1);
2688 l_module_name VARCHAR2 (30);
2689
2690 BEGIN
2691
2692 l_proc_name := 'RUN_DISCRETE_FIRST_ETL';
2693 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
2694 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
2695
2696 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2697 l_debug_msg := 'Entered Into RUN_DISCRETE_FIRST_ETL';
2698 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2699 END IF;
2700
2701 l_stmt_num := 10;
2702 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2703 l_debug_msg := 'Calling Clean staging table PRE INIT';
2704 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2705 END IF;
2706
2707 OPI_DBI_INV_VALUE_INIT_PKG.clean_staging_tables('PRE_INIT');
2708
2709 l_stmt_num := 20;
2710 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2711 l_debug_msg := 'Setting up bound for Inventory Initial Load ';
2712 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2713 END IF;
2714
2715 -- cleans staging table. sets bounds for MMT, GTV and WTA Tables accordingly.
2716 OPI_DBI_BOUNDS_PKG.maintain_opi_dbi_logs('INVENTORY','INIT');
2717
2718 l_stmt_num := 25;
2719 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2720 l_debug_msg := 'Calling OPI_DBI_BOUNDS_PKG.load_opm_org_ledger_data ';
2721 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2722 END IF;
2723
2724 OPI_DBI_BOUNDS_PKG.load_opm_org_ledger_data;
2725
2726 l_stmt_num := 30;
2727 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2728 l_debug_msg := 'Collecting OPI , OPM Initial Load Inventory Quantities ';
2729 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2730 END IF;
2731
2732 OPI_DBI_INV_VALUE_INIT_PKG.extract_inventory_txn_qty;
2733 -- even this procedure has a commit as it has two extracts and each one run in parallel append mode.
2734 commit;
2735
2736 l_stmt_num := 40;
2737 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2738 l_debug_msg := 'Collecting OPI, OPM Initial Load Inventory Values ';
2739 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2740 END IF;
2741
2742 OPI_DBI_INV_VALUE_INIT_PKG.extract_inventory_txn_value;
2743 commit;
2744
2745 l_stmt_num := 50;
2746 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2747 l_debug_msg := 'Collecting OPI , OPM Intransit Intial Load ';
2748 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2749 END IF;
2750
2751 OPI_DBI_INV_VALUE_INIT_PKG.get_intransit_initial_load;
2752 commit;
2753
2754 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2755 l_debug_msg := 'Exit from RUN_DISCRETE_FIRST_ETL';
2756 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2757 END IF;
2758 -- ACTION: call API that Julia is writing for printing the stop reason code.
2759
2760 EXCEPTION
2761 WHEN OTHERS THEN
2762 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
2763 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
2764 RAISE;
2765
2766 END RUN_DISCRETE_FIRST_ETL;
2767
2768 -------------------------------------------------------------------------------
2769 -- This procedure is used to insert data into OPI_DBI_INV_TYPE_CODES which
2770 -- is apparently used in opi_inv_type_org_mv.
2771
2772 PROCEDURE SEED_INV_TYPE_CODES
2773 IS
2774
2775 l_stmt_num NUMBER;
2776 l_debug_msg VARCHAR2(1000);
2777 l_proc_name VARCHAR2 (60);
2778 l_typecode NUMBER;
2779 l_debug_mode VARCHAR2(1);
2780 l_module_name VARCHAR2(30);
2781
2782
2783 -- Cursor to check if the table is empty or not
2784 CURSOR type_code_exists_check_csr IS
2785 SELECT 1
2786 FROM OPI_DBI_INV_TYPE_CODES
2787 WHERE rownum < 2;
2788 --declared for bug 3429014
2789
2790 BEGIN
2791 l_proc_name := 'SEED_INV_TYPE_CODES';
2792 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
2793 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
2794
2795 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2796 l_debug_msg := 'Entered Into SEED_INV_TYPE_CODES';
2797 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2798 END IF;
2799
2800 l_stmt_num := 10;
2801 OPEN type_code_exists_check_csr;
2802
2803 FETCH type_code_exists_check_csr into l_typecode; ---added for bug 3429014
2804
2805 l_stmt_num :=20;
2806 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2807 l_debug_msg := 'Inserting type codes. ';
2808 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2809 END IF;
2810
2811 IF (type_code_exists_check_csr%NOTFOUND) THEN
2812
2813 INSERT INTO opi_dbi_inv_type_codes (inventory_type) VALUES ('ONH');
2814 INSERT INTO opi_dbi_inv_type_codes (inventory_type) VALUES ('INT');
2815 INSERT INTO opi_dbi_inv_type_codes (inventory_type) VALUES ('WIP');
2816
2817 END IF;
2818
2819 l_stmt_num := 30;
2820 CLOSE type_code_exists_check_csr;
2821
2822 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2823 l_debug_msg := 'exit from SEED_INV_TYPE_CODES';
2824 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2825 END IF;
2826
2827 EXCEPTION
2828 WHEN OTHERS THEN
2829 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
2830 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
2831 RAISE;
2832
2833 END SEED_INV_TYPE_CODES;
2834
2835 -------------------------------------------------------------------------------
2836
2837 PROCEDURE RUN_FIRST_ETL(errbuf in out NOCOPY VARCHAR2, retcode in out NOCOPY VARCHAR2, p_degree IN NUMBER)
2838 IS
2839 OPI_DBI_EXCEPTION EXCEPTION;
2840
2841 PRAGMA EXCEPTION_INIT (OPI_DBI_EXCEPTION , -20002);
2842
2843 l_stmt_num NUMBER;
2844 l_debug_msg VARCHAR2(1000);
2845 l_proc_name VARCHAR2 (60);
2846 l_discrete_retcode NUMBER :=0;
2847 l_opm_retcode NUMBER := 0;
2848 l_debug_mode VARCHAR2(1);
2849 l_module_name VARCHAR2(30);
2850 l_inv_migration_date DATE;
2851 l_uncost_trx BOOLEAN;
2852
2853 BEGIN
2854
2855 l_proc_name := 'RUN_FIRST_ETL';
2856 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
2857 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
2858
2859 l_stmt_num := 10;
2860 l_debug_msg := 'Starting Initial Load for Inventory Management page';
2861 bis_collection_utilities.put_line(l_debug_msg );
2862
2863 -- action: why do we need this. confirm with performance team as why this is required
2864 execute immediate 'alter session set hash_area_size=104857600' ;
2865 execute immediate 'alter session set sort_area_size=104857600' ;
2866
2867 l_stmt_num :=20;
2868 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2869 l_debug_msg := 'Checking For Global Parameters';
2870 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2871 END IF;
2872
2873 -- checks for GSD, primary and sec currency rate types, schema name for OPI
2874 -- raises exception in case there is an error
2875 check_initial_load_setup;
2876
2877 l_stmt_num :=30;
2878 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2879 l_debug_msg := 'Calling Procedure seed inv codeType ';
2880 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2881 END IF;
2882 -- This will populate data for INV type report
2883 OPI_DBI_INV_VALUE_INIT_PKG.seed_inv_type_codes; -- No changes for R12
2884 commit;
2885
2886 l_stmt_num := 35;
2887 if (bis_collection_utilities.setup ( p_object_name => 'OPI_DBI_INV_VALUE_F') = false) then
2888 raise_application_error(-20000,errbuf);
2889 end if;
2890
2891 l_stmt_num :=40;
2892 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2893 l_debug_msg := 'Starting ODM and OPM Post R12 Initial Collection. ';
2894 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2895 END IF;
2896
2897 -- collects data from post r12 model for process as well as discrete organizations
2898 -- collects onhand quantity, onhand value, intransit qty and value, wip value
2899 OPI_DBI_INV_VALUE_INIT_PKG.Run_Discrete_First_ETL ;
2900
2901 l_stmt_num :=50;
2902 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2903 l_debug_msg := 'Getting Convergence date. ';
2904 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2905 END IF;
2906 -- Procedure will provide R12 migration date
2907 OPI_DBI_RPT_UTIL_PKG.get_inv_convergence_date(l_inv_migration_date);
2908 g_R12_date:=l_inv_migration_date;
2909 -- ACTION: confirm that it returns a truncated date
2910
2911 IF (g_R12_date IS NULL ) THEN
2912 l_debug_msg := 'CONVERGENGE Date is not available. Can not proceed';
2913 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2914 RAISE OPI_DBI_EXCEPTION;
2915 END IF;
2916
2917 l_stmt_num :=60;
2918 IF g_global_start_date < g_R12_date then
2919 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2920 l_debug_msg := 'Strating Pre R12 OPM Collection . ';
2921 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2922 END IF;
2923 -- This procedure will collect Pre R12 data for OPM
2924 OPI_DBI_INV_VALUE_OPM_INIT_PKG.Run_OPM_First_ETL(errbuf, retcode); -- For Pre R12 data
2925 IF (retcode <> 0) THEN
2926 IF (retcode = 1) THEN
2927 OPI_DBI_BOUNDS_PKG.write(g_pkg_name, l_proc_name,l_stmt_num,'Process Org Initial Collection completed with warnings.');
2928 ELSE
2929 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num, 'Process Org Initial Collection Failed.');
2930 RAISE OPI_DBI_EXCEPTION;
2931 END IF;
2932 END IF;
2933 ELSE
2934 l_debug_msg := 'GSD is greater then R12. No preR12 data is collected.';
2935 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2936 END IF;
2937
2938 l_stmt_num :=70;
2939 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2940 l_debug_msg := 'Start Collection of Inception quantities. ';
2941 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2942 END IF;
2943
2944 -- collects inception balance from staging tables above and also costs the onhand and
2945 -- intransit quantities
2946 OPI_DBI_INV_VALUE_INIT_PKG.get_inception_inv_balance;
2947
2948 l_stmt_num :=80;
2949 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2950 l_debug_msg := 'Checking for Conversion Rate. ';
2951 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2952 END IF;
2953
2954 -- this api prints the report in case there are conversion rates missing.
2955 -- it returns -1 in case there are missing conversion rates as well.
2956 IF (OPI_DBI_INV_VALUE_UTL_PKG.Get_Conversion_Rate(errbuf, retcode) = -1) THEN
2957 OPI_DBI_BOUNDS_PKG.write (g_pkg_name, l_proc_name,l_stmt_num,'Missing currency rate.');
2958 OPI_DBI_BOUNDS_PKG.write (g_pkg_name, l_proc_name,l_stmt_num,'Please run the concurrent program: Initial Load - Update Inventory Value and Turns Base Summary, after fixing missing currency rates.');
2959 retcode := -1; /* 11.5.10. Changed from warning to error */
2960 RAISE OPI_DBI_EXCEPTION;
2961 END IF;
2962 COMMIT;
2963
2964 l_stmt_num :=90;
2965 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2966 l_debug_msg := 'Starting Merge Initial Load . ';
2967 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2968 END IF;
2969 -- This procedure merges data from various
2970 -- staging tables populated in initial load.
2971 -- 1. OPI_DBI_ONH_QTY_STG -- onhand quantity
2972 -- 2. opi_dbi_opm_inv_stg -- Pre r12 OPM DATA
2973 -- 3. OPI_DBI_INTRANSIT_STG -- Intransit Qty and Value
2974 -- 4. OPI_DBI_INV_BEG_STG -- Inception qty and value
2975 -- 5. OPI_DBI_ONHAND_STG -- On hand value
2976 -- 6. OPI_DBI_CONVERSION_RATES -- currency conversion rates
2977 OPI_DBI_INV_VALUE_INIT_PKG.merge_initial_load;
2978
2979 l_stmt_num :=100;
2980 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2981 l_debug_msg := 'Starting Clean staging table post initial Load. ';
2982 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2983 END IF;
2984 -- truncate the data in staging table
2985 OPI_DBI_INV_VALUE_INIT_PKG.clean_staging_tables ('POST_INIT');
2986
2987 l_stmt_num :=110;
2988 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2989 l_debug_msg := 'Starting Period close adjustment. ';
2990 OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2991 END IF;
2992 -- It will populate period close adjustment data
2993 OPI_DBI_INV_CPCS_PKG.Run_Period_Close_Adjustment(errbuf, retcode);
2994
2995 l_stmt_num :=120;
2996 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
2997 l_debug_msg := 'Checking for uncosted transactions. ';
2998 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
2999 END IF;
3000
3001 l_uncost_trx := OPI_DBI_BOUNDS_PKG.bounds_uncosted('INVENTORY', 'INIT');
3002
3003 IF l_uncost_trx =TRUE then
3004 retcode := 1;
3005 l_debug_msg := 'Warning: There are some uncosted transactions. ';
3006 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
3007 END IF;
3008
3009 l_stmt_num :=130;
3010 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
3011 l_debug_msg := 'Priting the log bounds. ';
3012 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
3013 END IF;
3014
3015 OPI_DBI_BOUNDS_PKG.print_opi_org_bounds('INVENTORY', 'INIT');
3016
3017 l_stmt_num :=140;
3018 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
3019 l_debug_msg := 'Updating Log with success. ';
3020 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
3021 END IF;
3022
3023 -- This will update log table for successful completion of
3024 -- Inventory Initial Load
3025 OPI_DBI_BOUNDS_PKG.set_load_successful('INVENTORY', 'INIT');
3026
3027 BIS_COLLECTION_UTILITIES.wrapup( -- updates the log
3028 p_status => TRUE,
3029 p_count => 0, -- for 5.0 only. will put meaningful number in 6.0
3030 p_message => 'Successfully loaded inventory value base table at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
3031 );
3032
3033 EXCEPTION
3034 WHEN OPI_DBI_EXCEPTION THEN
3035 retcode := -1;
3036 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
3037 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
3038 RAISE;
3039
3040 WHEN OTHERS THEN
3041 retcode := -1;
3042 l_debug_msg := 'Failed with errror ' || SQLcode || ' - ' ||SQLERRM;
3043 OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
3044
3045 BIS_COLLECTION_UTILITIES.WRAPUP(
3046 p_status => FALSE,
3047 p_message => 'Failed in Initial Load of inventory value base table.'
3048 );
3049 RAISE;
3050
3051 END RUN_FIRST_ETL;
3052
3053 End OPI_DBI_INV_VALUE_INIT_PKG;
[Home] [Help]