1 PACKAGE BODY OPI_DBI_INV_CPCS_PKG as
2 /* $Header: OPIDIVCPCSB.pls 120.2 2005/09/13 06:24:43 manokuma noship $ */
3
4 g_sysdate DATE;
5 g_created_by NUMBER;
6 g_last_update_login NUMBER;
7 g_last_updated_by NUMBER;
8 g_global_start_date DATE;
9 g_opi_cpcs_source CONSTANT NUMBER:= 4; -- R12 onwanrd for CPCS it will be 4. (also documented in etrm)
10 g_ok CONSTANT NUMBER(1) := 0;
11 g_warning CONSTANT NUMBER(1) := 1;
12 g_error CONSTANT NUMBER(1) := -1;
13
14
15 FUNCTION Clean_Staging_Table (
16 errbuf IN OUT NOCOPY VARCHAR2,
17 retcode IN OUT NOCOPY VARCHAR2
18 )
19 return NUMBER
20 IS
21 l_opi_schema VARCHAR2(30);
22 l_status VARCHAR2(30);
23 l_industry VARCHAR2(30);
24 BEGIN
25
26 BIS_COLLECTION_UTILITIES.put_line('Start of cleaning staging table.');
27
28 IF (fnd_installation.get_app_info( 'OPI', l_status,
29 l_industry, l_opi_schema)) THEN
30 execute immediate 'truncate table ' || l_opi_schema ||
31 '.OPI_DBI_ONHAND_STG';
32 BIS_COLLECTION_UTILITIES.put_line(
33 'OPI_DBI_ONHAND_STG table truncated.');
34
35 execute immediate 'truncate table ' || l_opi_schema ||
36 '.OPI_DBI_INTRANSIT_STG';
37 BIS_COLLECTION_UTILITIES.put_line (
38 'OPI_DBI_INTRANSIT_STG table truncated.');
39
40 execute immediate 'truncate table ' || l_opi_schema ||
41 '.OPI_DBI_CONVERSION_RATES';
42 BIS_COLLECTION_UTILITIES.put_line(
43 'OPI_DBI_CONVERSION_RATES table truncated.');
44 END IF;
45
46 BIS_COLLECTION_UTILITIES.put_line('End of cleaning staging table');
47 return g_ok;
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 rollback;
52 BIS_COLLECTION_UTILITIES.put_line(SQLERRM);
53 retcode := SQLCODE;
54 errbuf := SQLERRM;
55 return g_error;
56 END Clean_Staging_Table;
57
58
59 FUNCTION Merge_Into_Summary (
60 errbuf IN OUT NOCOPY VARCHAR2,
61 retcode IN OUT NOCOPY VARCHAR2
62 )
63 return NUMBER
64 IS
65 l_rows NUMBER := 0;
66 BEGIN
67
68 INSERT /*+ append parallel(opi_dbi_inv_value_f) */ INTO opi_dbi_inv_value_f
69 (operating_unit_id,
70 organization_id,
71 subinventory_code,
72 inventory_item_id,
73 transaction_date,
74 primary_uom,
75 onhand_value_b,
76 intransit_value_b,
77 wip_value_b,
78 conversion_rate,
79 sec_conversion_rate,
80 source,
81 created_by,
82 last_update_login,
83 creation_date,
84 last_updated_by,
85 last_update_date
86 )
87 SELECT /*+ use_hash(rate, s) parallel(s) parallel(rate) */
88 NULL operating_unit_id,
89 s.organization_id,
90 s.subinventory_code,
91 s.inventory_item_id,
92 s.transaction_date,
93 msi.primary_uom_code,
94 s.onhand_value_b,
95 s.intransit_value_b,
96 s.wip_value_b,
97 rate.conversion_rate,
98 rate.sec_conversion_rate,
99 g_opi_cpcs_source,
100 g_created_by,
101 g_last_update_login,
102 g_sysdate,
103 g_last_updated_by,
104 g_sysdate
105 FROM
106 (SELECT /*+ parallel(adjustments) */
107 organization_id,
108 subinventory_code,
109 inventory_item_id,
110 transaction_date,
111 sum(onhand_value_b) onhand_value_b,
112 sum(intransit_value_b) intransit_value_b,
113 sum(wip_value_b) wip_value_b
114 FROM
115 (SELECT /*+ parallel(onhand_stg) */
116 organization_id,
117 subinventory_code,
118 inventory_item_id,
119 transaction_date,
120 onhand_value_b,
121 0 intransit_value_b,
122 0 wip_value_b
123 FROM opi_dbi_onhand_stg
124 WHERE source = g_opi_cpcs_source
125 UNION ALL
126 SELECT /*+ parallel(intransit_stg) */
127 organization_id,
128 NULL,
129 inventory_item_id,
130 transaction_date,
131 0 onhand_value_b,
132 intransit_value_b,
133 0 wip_value_b
134 FROM opi_dbi_intransit_stg
135 WHERE source = g_opi_cpcs_source
136 ) adjustments
137 GROUP BY
138 organization_id,
139 subinventory_code,
140 inventory_item_id,
141 transaction_date
142 ) s,
143 (SELECT /*+ no_merge parallel(rates) */
144 organization_id,
145 transaction_date,
146 conversion_rate,
147 sec_conversion_rate
148 FROM opi_dbi_conversion_rates
149 ) rate,
150 mtl_system_items_b msi
151 WHERE s.organization_id = rate.organization_id
152 AND s.transaction_date = rate.transaction_date
153 AND s.organization_id = msi.organization_id
154 AND s.inventory_item_id = msi.inventory_item_id;
155
156 l_rows := SQL%ROWCOUNT;
157
158
159 -- Set the PCS rows as "Regular Adjustments Processed". Also,
160 -- set the from_transaction_Date for next time as current
161 -- transaction_date and the transaction_date as NULL.
162 -- Since periods for different organizations can be closed
163 -- at different times, we can only update rows for orgs that
164 -- have been processed in this run i.e. ones with
165 -- uncosted_trx_id = -99.
166 UPDATE opi_dbi_inv_value_log
167 SET uncosted_trx_id = NULL,
168 from_transaction_date = transaction_date,
169 transaction_date = NULL
170 WHERE uncosted_trx_id = -99
171 AND type = 'PCS'
172 AND source = g_opi_cpcs_source;
173
174 commit;
175
176 return l_rows;
177
178 EXCEPTION
179 WHEN OTHERS THEN
180 rollback;
181 BIS_COLLECTION_UTILITIES.put_line(SQLERRM);
182 retcode := SQLCODE;
183 errbuf := SQLERRM;
184 return g_error;
185 END Merge_Into_Summary;
186
187
188
189 FUNCTION INSERT_ADJUSTMENTS (
190 errbuf IN OUT NOCOPY VARCHAR2,
191 retcode IN OUT NOCOPY VARCHAR2
192 ) RETURN NUMBER IS
193 l_dbilog_rows NUMBER;
194 l_status VARCHAR2(30);
195 l_stmt_num NUMBER;
196 l_err_num NUMBER;
197 l_err_msg VARCHAR2(255);
198 BEGIN
199
200 -- IF there are orgs that have inception balance calculated
201 -- (MIF row in DBI Log table) but do not have a PCS row in
202 -- DBI log table:
203 -- We should execute the fisrt lump-sum process for those orgs.
204 -- (New Orgs that will be created afterwards do not
205 -- need the first lump-sum. Since they do not have inception
206 -- to date rows in the fact table, but only MTA activity rows.
207 -- So regular adjustments are sufficient for them.)
208 BIS_COLLECTION_UTILITIES.put_line(
209 'Start of Period Close Adjustments load.');
210
211 -- For all organizations collected in Initial load of Inventory
212 -- check if lump sum processing is done by CPCS or not.
213 -- R12 Changes: Replaced opi_dbi_inv_value_log by conc_prog_run_log in outer select.
214 BEGIN
215 l_stmt_num := 5;
216 SELECT 1
217 INTO l_dbilog_rows
218 FROM opi_dbi_conc_prog_run_log log
219 ,mtl_parameters mp
220 WHERE log.ETL_TYPE = 'INVENTORY'
221 AND log.load_type = 'INIT'
222 AND log.driving_table_code = 'MMT'
223 AND log.bound_level_entity_code = 'ORGANIZATION'
224 AND log.bound_level_entity_id = mp.organization_id
225 -- cpcs is only valid for discrete orgs. though log table
226 -- does not contain records for discrete orgs still putting
227 -- this additional filter.
228 AND nvl(mp.process_enabled_flag,'-1') <> 'Y'
229 AND NOT EXISTS
230 (SELECT 'x'
231 FROM opi_dbi_inv_value_log inlog
232 WHERE inlog.TYPE = 'PCS'
233 AND inlog.source = g_opi_cpcs_source
234 AND inlog.organization_id = log.bound_level_entity_id)
235 AND rownum = 1;
236
237 EXCEPTION
238 WHEN NO_DATA_FOUND THEN
239 l_dbilog_rows := 0;
240 END;
241
242 -- If there are rows with inventory inception balances but no
243 -- PCS rows, do first CPCS adjustment.
244 IF l_dbilog_rows > 0 THEN
245
246 l_stmt_num := 10;
247 l_status := 'First Period Close adjustment';
248 -- Insert into DBI Inventory Log the organizations
249 -- that need first lump-sum adjustment ...
250 INSERT INTO opi_dbi_inv_value_log
251 (organization_id,
252 transaction_id,
253 transaction_date,
254 uncosted_trx_id,
255 type,
256 source,
257 creation_date,
258 last_update_date,
259 created_by,
260 last_updated_by,
261 last_update_login)
262 SELECT /*+ use_hash(cpcs, oap) parallel(cpcs) parallel(oap) */
263 cpcs.organization_id,
264 -99.99 transaction_id,
265 trunc (min (oap.schedule_close_date)) transaction_date,
266 -99, -- Indicates that the organization needs lump-sum adjustment processing
267 'PCS' type,
268 g_opi_cpcs_source source,
269 g_sysdate creation_date,
270 g_sysdate last_update_date,
271 g_created_by created_by,
272 g_last_updated_by last_updated_by,
273 g_last_update_login last_update_login
274 FROM cst_period_close_summary cpcs,
275 org_acct_periods oap
276 WHERE cpcs.acct_period_id = oap.acct_period_id
277 AND cpcs.organization_id = oap.organization_id
278 AND oap.schedule_close_date >= g_global_start_date
279 AND oap.summarized_flag ='Y'
280 AND NOT EXISTS
281 (SELECT 'x'
282 FROM opi_dbi_inv_value_log inlog
283 WHERE inlog.organization_id = cpcs.organization_id
284 AND inlog.type = 'PCS'
285 AND inlog.source = g_opi_cpcs_source)
286 GROUP BY cpcs.organization_id;
287
288
289 IF sql%rowcount = 0 THEN
290
291 BIS_COLLECTION_UTILITIES.put_line ('No rows to process for the First Period Close Adjustment Load.');
292
293 ELSE
294
295 -- ... but remove Organizations that have:
296 -- Backdated transactions after to_txn_date in DBI
297 -- Inventory Log table and the backdated transaction
298 -- lies within the first period close in CPCS
299 l_stmt_num := 20;
300 DELETE FROM opi_dbi_inv_value_log
301 WHERE organization_id IN
302 (SELECT mmt.organization_id
303 FROM
304 mtl_material_transactions mmt,
305 opi_dbi_inv_value_log log2
306 WHERE log2.uncosted_trx_id = -99 -- Indicates that the organization needs lump-sum adjustment processing
307 AND log2.type = 'PCS'
308 AND log2.source = g_opi_cpcs_source
309 AND mmt.organization_id = log2.organization_id
313 WHERE log1.load_type IN ('INIT','INCR')
310 -- >= equal to is required because to_bound_id is first uncosted txn and not the last costed txn id
311 AND mmt.transaction_id >= (select max(log1.to_bound_id)
312 from opi_dbi_conc_prog_run_log log1
314 AND log1.etl_type = 'INVENTORY'
315 AND log1.driving_table_code = 'MMT'
316 AND log1.bound_level_entity_code = 'ORGANIZATION'
317 AND log1.bound_level_entity_id = log2.organization_id)
318 -- the other condition of mmt.transaction_date <= log1.transaction_date is not required as
319 -- mmt.transaction_date <= log2.transaction_date is sufficient.
320 -- no need to add condition for process orgs inv_value_log cannot have it.
321 -- ... and the backdated transaction lies within the first period close in CPCS
322 AND mmt.transaction_date <= log2.transaction_date
323 )
324 and TYPE = 'PCS' and source = g_opi_cpcs_source;
325
326 IF sql%rowcount > 0 THEN
327 BIS_COLLECTION_UTILITIES.put_line('There are transactions related to a closed period, which ');
328 BIS_COLLECTION_UTILITIES.put_line('have not been collected due to an uncosted transaction. ');
329 BIS_COLLECTION_UTILITIES.put_line('Please ensure all transactions are costed and the data is collected again.');
330 END IF;
331
332 -- Commit data in the log table because we need to access it
333 -- in parallel mode. This is due to bug 4285814.
334 -- This should not affect anything because this branch of the
335 -- code is only run in the initial load. Any errors will
336 -- require the ETL to be run again. Hence there is no risk in
337 -- committing early.
338 -- There is known issue here. Suppose DBI initial load is run
339 -- before at least one period has been closed for every org.
340 -- Then the DBI initial load will not pick up initial
341 -- adjustments for the orgs that have been defined prior to
342 -- the DBI initial load run but have no 11.5.10 closed periods.
343 -- The initial adjustment for those orgs will be picked up
344 -- only during an incremental run once the first period is
345 -- closed for that organization. During that incremental run
346 -- if the program fails after this commit point, e.g. due to
347 -- missing conversion rates, the initial adjustment will never
348 -- get picked up for this org since the PCS row for this
349 -- org has now been committed to the log table. For incrementals
350 -- this issue has been taken care of with the new approach
351 -- of using the transaction_date and from_transaction_date
352 -- columns in the log. However no easy fix is available for the
353 -- the initial adjustment. Of course, this is a corner case
354 -- since it is unlikely that customers will be running this
355 -- version of DBI with no closed 11.5.10 periods. The work-
359
356 -- around is to run the initial load after the first period
357 -- has been closed for all organizations.
358 commit;
360 -- Insert first lump-sum adjustments into
361 -- onhand and intransit staging tables
362 BIS_COLLECTION_UTILITIES.put_line(
363 'Started First Period Close Adjustments load.');
364 BIS_COLLECTION_UTILITIES.put_line('(First period closed with the FP "J"/115.10, period close process.)');
365
366 l_stmt_num := 30;
367 INSERT /*+ append parallel(opi_dbi_onhand_stg)
368 parallel(opi_dbi_intransit_stg) */
369 ALL
370 WHEN onhand_value_lump_Sum <> 0
371 THEN INTO opi_dbi_onhand_stg
372 (organization_id, inventory_item_id, transaction_date,
373 onhand_value_b, source, subinventory_code,
374 creation_date, last_update_date, created_by,
375 last_updated_by, last_update_login)
376 VALUES
377 (organization_id, inventory_item_id, transaction_date,
378 onhand_value_lump_sum, source, subinventory_code,
379 creation_date, last_update_date, created_by,
380 last_updated_by, last_update_login)
381 WHEN intransit_value_lump_sum <> 0
382 THEN INTO OPI_DBI_INTRANSIT_STG
383 (organization_id, inventory_item_id, transaction_date,
384 intransit_value_b, source,
385 creation_date, last_update_date, created_by,
386 last_updated_by, last_update_login)
387 VALUES
388 (organization_id, inventory_item_id, transaction_date,
389 intransit_value_lump_sum, source,
390 creation_date, last_update_date, created_by,
391 last_updated_by, last_update_login)
392 SELECT /*+ use_hash(cpcs_rbk, dbi_itd)
393 parallel(pcs_rbk) parallel(dbi_itd) */
394 dbi_itd.organization_id,
395 dbi_itd.subinventory_code,
396 dbi_itd.inventory_item_id,
397 dbi_itd.transaction_date,
398 nvl (cpcs_onhand_value_b, 0) - dbi_onhand_value_b
399 onhand_value_lump_sum, -- Onhand First Lump-Sum adjustment
400 nvl(cpcs_intransit_value_b, 0) - dbi_intransit_value_b
401 intransit_value_lump_sum, -- Intransit First Lump-Sum adjustment
402 g_opi_cpcs_source source,
403 g_sysdate creation_date,
404 g_sysdate last_update_date,
405 g_created_by created_by,
406 g_last_updated_by last_updated_by,
407 g_last_update_login last_update_login
408 FROM
409 (
410 SELECT /*+ use_hash(cpcs, oap, invlog)
411 parallel(cpcs) parallel(oap) parallel(invlog) */
412 cpcs.organization_id,
413 nvl(cpcs.subinventory_code, -1) subinventory_code,
414 cpcs.inventory_item_id,
415 trunc(oap.schedule_close_date) transaction_date,
416 sum(rollback_onhand_value) cpcs_onhand_value_b,
417 sum(rollback_intransit_value) cpcs_intransit_value_b
418 FROM
419 cst_period_close_summary cpcs,
420 org_acct_periods oap,
421 opi_dbi_inv_value_log invlog
422 WHERE cpcs.acct_period_id = oap.acct_period_id
423 AND cpcs.organization_id = oap.organization_id
424 AND oap.summarized_flag ='Y'
425 AND cpcs.organization_id = invlog.organization_id
426 AND oap.schedule_close_date = invlog.transaction_date
427 AND invlog.uncosted_trx_id = -99 -- Indicates that the organization needs lump-sum adjustment processing
428 AND invlog.type = 'PCS'
429 AND invlog.source = g_opi_cpcs_source
430 GROUP BY
431 cpcs.organization_id,
432 nvl(cpcs.subinventory_code, -1),
433 cpcs.inventory_item_id,
434 TRUNC(oap.SCHEDULE_CLOSE_DATE)
435 ) cpcs_rbk,
436 (
437 SELECT /*+ use_hash(f, invlog) parallel(f) parallel(invlog) */
438 f.organization_id,
439 nvl(f.subinventory_code, -1) subinventory_code,
440 f.inventory_item_id,
441 invlog.transaction_date transaction_date,
442 sum(onhand_value_b) dbi_onhand_value_b,
443 sum(intransit_value_b) dbi_intransit_value_b
444 FROM
445 opi_dbi_inv_value_f f,
446 opi_dbi_inv_value_log invlog
447 WHERE f.organization_id = invlog.organization_id
448 AND f.transaction_date < invlog.transaction_date + 1 -- include all txns on CPCSD First Period close date too
449 AND invlog.uncosted_trx_id = -99 -- Indicates that the organization needs lump-sum adjustment processing
450 AND invlog.TYPE = 'PCS'
451 AND invlog.source = g_opi_cpcs_source
452 GROUP BY
453 f.organization_id,
457 ) dbi_itd
454 nvl(f.subinventory_code, -1),
455 f.inventory_item_id,
456 invlog.transaction_date
458 WHERE cpcs_rbk.organization_id (+) = dbi_itd.organization_id /* Outer join for items that have a balance in DBI but not in CPCS */
459 AND cpcs_rbk.subinventory_code (+) = dbi_itd.subinventory_code
460 AND cpcs_rbk.inventory_item_id (+) = dbi_itd.inventory_item_id
461 AND cpcs_rbk.transaction_date (+) = dbi_itd.transaction_date
462 AND (nvl(cpcs_onhand_value_b, 0) - dbi_onhand_value_b
463 <> 0
464 OR
465 nvl(cpcs_intransit_value_b, 0) - dbi_intransit_value_b
466 <> 0);
467
468 BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' First Period Close Adjustment rows have been inserted into staging tables.');
469
470 -- Set organizations to "lump-sum adjustment has been processed"
471 -- status.
472 -- Also, set the from_transaction_date to the be the
473 -- transaction_Date, and make the transaction_Date null.
474 -- We will use the from_transaction_date as the starting
475 -- bound for all regular adjustments.
476 -- See bug 4285814.
477 UPDATE opi_dbi_inv_value_log
478 SET uncosted_trx_id = null,
479 from_transaction_date = transaction_date,
480 transaction_date = NULL
481 WHERE uncosted_trx_id = -99
482 AND type = 'PCS'
483 AND source = g_opi_cpcs_source;
484
485 commit;
486
487 BIS_COLLECTION_UTILITIES.put_line('Finished First Period Close Adjustments load.');
488 -- First lump-sum adjustment has finished
489
490 END IF;
491
492 END IF;
493
494
495 -- Regular adjustments process
496 -- Update DBI INV log PCS rows for existing organizations
497 -- Insert new DBI Inv log PCS rows for new organizations
498 l_status := 'Regular adjustment';
499 l_stmt_num := 40;
500 MERGE INTO OPI_DBI_INV_VALUE_LOG log
501 USING
502 (
503 SELECT
504 cpcs.Organization_id,
505 -99.99 transaction_id,
506 trunc(max(oap.schedule_close_date)) transaction_date, -- To period end date
507 trunc(min(oap.schedule_close_date)) from_transaction_date, -- From period end date
508 'PCS' type,
509 g_opi_cpcs_source source,
510 g_sysdate creation_date,
511 g_sysdate last_update_date,
512 g_created_by created_by,
513 g_last_updated_by last_updated_by,
514 g_last_update_login last_update_login
515 FROM
516 cst_period_close_summary cpcs,
517 org_acct_periods oap,
518 OPI_DBI_INV_VALUE_LOG invlog
519 WHERE
520 cpcs.acct_period_id = oap.acct_period_id
521 AND cpcs.organization_id = oap.organization_id
522 AND oap.summarized_flag ='Y'
523 AND cpcs.organization_id = invlog.organization_id (+)
524 AND oap.schedule_close_date > nvl(invlog.from_transaction_date, oap.schedule_close_date - 1) -- periods after the last period processed
525 AND invlog.type = 'PCS'
526 AND invlog.source = g_opi_cpcs_source
527 GROUP BY cpcs.Organization_id
528 ) stg
529 ON
530 (log.organization_id = stg.organization_id
531 and log.type = stg.type
532 and log.source = stg.source)
533 WHEN MATCHED THEN
534 UPDATE SET
535 log.transaction_date = stg.transaction_date,
536 log.from_transaction_date = stg.from_transaction_date,
537 log.last_update_date = stg.last_update_date,
538 log.last_updated_by = stg.last_updated_by,
539 log.last_update_login = stg.last_update_login,
540 log.uncosted_trx_id = -99 -- Indicates Indicates that the organization needs regular adjustment processing
541 WHEN NOT MATCHED THEN
542 INSERT
543 (organization_id,
544 transaction_id,
545 transaction_date,
546 from_transaction_date,
547 uncosted_trx_id,
548 type,
549 source,
550 creation_date,
551 last_update_date,
552 created_by,
553 last_updated_by,
554 last_update_login)
555 VALUES
556 (
557 stg.organization_id,
558 stg.transaction_id,
559 stg.transaction_date,
560 stg.from_transaction_date,
561 -99, -- indicates that the organization needs regular adjustment processing
562 stg.type,
563 stg.source,
564 stg.creation_date,
565 stg.last_update_date,
566 stg.created_by,
567 stg.last_updated_by,
568 stg.last_update_login
569 );
570
571 -- Commit these bounds. The commit is required because of the parallel
572 -- access of the inventory value log. Parallel slaves are separate
573 -- sessions and can only see the table data once committed.
574 -- See bug 4285814.
575 -- However, we will update the from_transaction_date only after
576 -- successfully inserting into the fact table so no date ranges
577 -- will be ignored in case the program fails.
578 commit;
579
580 -- Figure out if there are any rows, from the SQL above, or from
581 -- an errored out run from last time that require adjustments.
582 l_dbilog_rows := 0;
583 SELECT count (*)
584 INTO l_dbilog_rows
585 FROM opi_dbi_inv_value_log
586 WHERE type = 'PCS'
587 AND source = g_opi_cpcs_source
588 AND uncosted_trx_id = -99;
589
590 IF l_dbilog_rows > 0 THEN
591
592 -- Insert new regular adjustments into
593 -- inventory onhand and intransit staging tables
594 l_stmt_num := 50;
595 BIS_COLLECTION_UTILITIES.put_line('Started Period Close Regular Adjustments load.');
596
597 INSERT ALL
598 WHEN onhand_value_b <> 0
599 THEN INTO opi_dbi_onhand_stg
600 (organization_id, inventory_item_id, transaction_date,
601 onhand_value_b, source, subinventory_code,
602 creation_date, last_update_date, created_by,
603 last_updated_by, last_update_login)
604 VALUES
608 LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
605 (organization_id, inventory_item_id, transaction_date,
606 onhand_value_b, source, subinventory_code,
607 creation_date, last_update_date, created_by,
609 WHEN intransit_value_b <> 0
610 THEN INTO opi_dbi_intransit_stg
611 (organization_id, inventory_item_id, transaction_date,
612 intransit_value_b, source,
613 creation_date, last_update_date, created_by,
614 last_updated_by, last_update_login)
615 VALUES
616 (organization_id, inventory_item_id, transaction_date,
617 intransit_value_b, source,
618 creation_date, last_update_date, created_by,
619 last_updated_by, last_update_login)
620 SELECT /*+ use_hash(cpcs, oap, invlog)
621 parallel(cpcs) parallel(oap) parallel(invlog) */
622 cpcs.organization_id,
623 nvl(cpcs.subinventory_code, -1) subinventory_code,
624 cpcs.inventory_item_id,
625 trunc(oap.SCHEDULE_CLOSE_DATE) transaction_date,
626 sum(rollback_onhand_value - accounted_onhand_value)
627 onhand_value_b, -- Onhand adjustment
628 sum(rollback_intransit_value - accounted_intransit_value)
629 INTRANSIT_VALUE_B, -- Intransit adjustment
630 g_opi_cpcs_source source,
631 g_sysdate creation_date,
632 g_sysdate last_update_date,
633 g_created_by created_by,
634 g_last_updated_by last_updated_by,
635 g_last_update_login last_update_login
636 FROM
637 cst_period_close_summary cpcs,
638 org_acct_periods oap,
639 opi_dbi_inv_value_log invlog
640 WHERE cpcs.acct_period_id = oap.acct_period_id
641 AND cpcs.organization_id = oap.organization_id
642 AND oap.summarized_flag ='Y'
643 AND cpcs.organization_id = invlog.organization_id
644 AND oap.schedule_close_date >= invlog.from_transaction_date
645 AND oap.schedule_close_date <= invlog.transaction_date
646 AND invlog.type = 'PCS'
647 AND invlog.uncosted_trx_id = -99 -- Indicates that the organization needs regular adjustment processing
648 AND invlog.source = g_opi_cpcs_source
649 AND ( rollback_onhand_value - accounted_onhand_value <> 0
650 OR rollback_intransit_value - accounted_intransit_value <> 0)
651 GROUP BY
652 cpcs.organization_id,
653 nvl(cpcs.SUBINVENTORY_CODE, -1),
654 cpcs.inventory_item_id,
655 trunc(oap.SCHEDULE_CLOSE_DATE)
656 HAVING
657 sum(rollback_onhand_value - accounted_onhand_value) <> 0
658 or
659 sum(rollback_intransit_value - accounted_intransit_value) <>0;
660
661 BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' Period Close Regular Adjustment rows have been inserted into staging tables.');
662 BIS_COLLECTION_UTILITIES.put_line('Finished Period Close Regular Adjustments load.');
663
664 -- Do not update bounds until data has been inserted into the fact.
665 -- Basically bounds update and data merging to the fact must
666 -- happen in the same database transaction.
667 -- See procedure merge_into_summary in this file.
668
669 commit;
670
671 ELSE
672 BIS_COLLECTION_UTILITIES.put_line(
673 'There were no Regular Adjustments to load.');
674 END IF;
675
676
677 -- Finished Period Close Adjustment process
678 BIS_COLLECTION_UTILITIES.put_line('End of Period Close Adjustments load.');
679 return g_ok;
680
681 EXCEPTION
682 WHEN OTHERS THEN
683 rollback;
684 BIS_COLLECTION_UTILITIES.put_line('Failed during collecting data for ' || l_status || '.');
685 l_err_num := SQLCODE;
686 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
687 l_err_msg := 'OPI_DBI_INV_CPCS_PKG.INSERT_ADJUSTMENTS - Error at statement ('
688 || to_char(l_stmt_num)
689 || '): '
690 || substr(SQLERRM, 1,200);
691 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
692
693 retcode := SQLCODE;
694 errbuf := SQLERRM;
695 return g_error;
696
697 END INSERT_ADJUSTMENTS;
698
699
700
701 PROCEDURE Run_Period_Close_Adjustment (
702 errbuf IN OUT NOCOPY VARCHAR2,
703 retcode IN OUT NOCOPY VARCHAR2
704 )
705 IS
706 l_rows1 NUMBER;
707 BEGIN
708
709 l_rows1 := 0;
710 retcode := 0;
711 errbuf := NULL;
712
713 -- Global variable initialization
714 g_sysdate := sysdate;
715 g_created_by := fnd_global.user_id;
716 g_last_update_login := fnd_global.login_id;
717 g_last_updated_by := fnd_global.user_id;
718 g_global_start_date := SYSDATE;
719
720
721 BIS_COLLECTION_UTILITIES.PUT_LINE('==================================================================================');
722 BIS_COLLECTION_UTILITIES.put_line('Period Close Adjustments Collection started at ' || TO_CHAR(g_sysdate, 'DD-MON-YYYY HH24:MI:SS'));
723
724 BEGIN
725 SELECT BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE
726 INTO g_global_start_date
727 FROM DUAL;
728 EXCEPTION
729 WHEN NO_DATA_FOUND THEN
730 BIS_COLLECTION_UTILITIES.put_line('Global start date is not available. Cannot proceed.');
731 BIS_COLLECTION_UTILITIES.put_line(SQLERRM);
732 retcode := SQLCODE;
733 errbuf := SQLERRM;
734 return;
735 END;
736
737 -- Period Close Adjustment process
738 IF (Insert_Adjustments(errbuf, retcode) = g_error) THEN
739 BIS_COLLECTION_UTILITIES.put_line('Failed to collect adjustments into staging tables.');
740 INSERT INTO opi_dbi_inv_value_log
741 (organization_id, transaction_id, transaction_date, type,
742 source, creation_date, last_update_date, created_by,
743 last_updated_by, last_update_login
744 )
745 VALUES
746 (-1, -1, g_sysdate, 'ERR',
747 g_opi_cpcs_source, g_sysdate, g_sysdate, g_created_by,
748 g_last_updated_by, g_last_update_login);
749
750 commit;
751 return;
752 ELSE
753 DELETE
754 FROM opi_dbi_inv_value_log
755 WHERE type = 'ERR'
756 AND source = g_opi_cpcs_source;
757 commit;
758 END IF;
759
760 /* CPCS is called after conversion is done in inventory and also
761 * after staging tables are cleaned up. So CPCS is on its own and
762 * does not depend on inventory for conversion and Merge into
763 * fact.
764 */
765
766 IF (OPI_DBI_INV_VALUE_UTL_PKG.Get_Conversion_Rate (errbuf, retcode) =
767 g_error) THEN
768 BIS_COLLECTION_UTILITIES.put_line('Missing currency rate.');
769 BIS_COLLECTION_UTILITIES.put_line('Please run the concurrent program: Initial Load - Update Inventory Value and Turns Base Summary, after fixing missing currency rates.');
770
771 -- If Incremental is run, the program will first try to fix the currency rates, then merge the stg tables into the summary table and then start the new incremental load
772 retcode := g_error;
773 return;
774 ELSE
775 BIS_COLLECTION_UTILITIES.put_line('All currency conversion rates were found.');
776 commit;
777 END IF;
778
779 l_rows1 := Merge_Into_Summary (errbuf, retcode);
780 IF (l_rows1 = g_error) THEN
781 BIS_COLLECTION_UTILITIES.put_line(
782 'Failed to merge data from staging table to base table.');
783 BIS_COLLECTION_UTILITIES.put_line('Please run the concurrent program: Update Inventory Value and Turns Base Summary, to try finishing this process.');
784
785 -- If Incremental is run, the program will and add rows to the stg tables, and at the end will merge old and new rows into the summary table
786 BIS_COLLECTION_UTILITIES.put_line('Warning: If you decide to run the Initial Load - Update Inventory Value and Turns Base Summary again, the entire process will start over again.');
787 return;
788 END IF;
789
790 IF (Clean_Staging_Table (errbuf, retcode) = g_error) THEN
791 BIS_COLLECTION_UTILITIES.put_line('Failed to clean staging tables.');
792 INSERT INTO opi_dbi_inv_value_log
793 (organization_id, transaction_id, transaction_date, type,
794 source, creation_date, last_update_date, created_by, last_updated_by,
795 last_update_login
796 )
797 VALUES
798 (-1, -1, g_sysdate, 'CLR', 1, g_sysdate, g_sysdate, g_created_by,
799 g_last_updated_by, g_last_update_login);
800 commit;
801 return;
802 END IF;
803
804 commit;
805
806 BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(l_rows1) || ' rows have been inserted into fact table from discrete/manufacturing organizations.');
807 BIS_COLLECTION_UTILITIES.put_line('Period Close Adjustments Collection finished at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
808 BIS_COLLECTION_UTILITIES.PUT_LINE('==================================================================================');
809 return;
810
811 EXCEPTION
812 WHEN OTHERS THEN
813 BIS_COLLECTION_UTILITIES.put_line('Period close adjustments process failed.');
814 BIS_COLLECTION_UTILITIES.put_line(SQLERRM);
815 retcode := SQLCODE;
816 errbuf := SQLERRM;
817 RAISE_APPLICATION_ERROR(-20000,errbuf);
818
819 END Run_Period_Close_Adjustment;
820
821
822
823 END OPI_DBI_INV_CPCS_PKG;
[Home] [Help]