eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OPI_DBI_INV_CPCS_PKG dependencies on BIS_COLLECTION_UTILITIES

Line 26: BIS_COLLECTION_UTILITIES.put_line('Start of cleaning staging table.');

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 ||

Line 32: BIS_COLLECTION_UTILITIES.put_line(

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';

Line 37: 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';

Line 42: 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');

Line 46: BIS_COLLECTION_UTILITIES.put_line('End of cleaning staging table');

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

Line 52: BIS_COLLECTION_UTILITIES.put_line(SQLERRM);

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;

Line 181: BIS_COLLECTION_UTILITIES.put_line(SQLERRM);

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;

Line 208: BIS_COLLECTION_UTILITIES.put_line(

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.

Line 291: BIS_COLLECTION_UTILITIES.put_line ('No rows to process for the First Period Close Adjustment Load.');

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:

Line 327: BIS_COLLECTION_UTILITIES.put_line('There are transactions related to a closed period, which ');

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:

Line 328: BIS_COLLECTION_UTILITIES.put_line('have not been collected due to an uncosted transaction. ');

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

Line 329: BIS_COLLECTION_UTILITIES.put_line('Please ensure all transactions are costed and the data is collected again.');

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.

Line 362: BIS_COLLECTION_UTILITIES.put_line(

358: commit;
359:
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;

Line 364: BIS_COLLECTION_UTILITIES.put_line('(First period closed with the FP "J"/115.10, period close process.)');

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) */

Line 468: BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' First Period Close Adjustment rows have been inserted into staging tables.');

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

Line 487: BIS_COLLECTION_UTILITIES.put_line('Finished First Period Close Adjustments load.');

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:

Line 595: BIS_COLLECTION_UTILITIES.put_line('Started Period Close Regular Adjustments load.');

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

Line 661: BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' Period Close Regular Adjustment rows have been inserted into staging tables.');

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

Line 662: BIS_COLLECTION_UTILITIES.put_line('Finished Period Close Regular Adjustments load.');

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.

Line 672: BIS_COLLECTION_UTILITIES.put_line(

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:

Line 678: BIS_COLLECTION_UTILITIES.put_line('End of Period Close Adjustments 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

Line 684: BIS_COLLECTION_UTILITIES.put_line('Failed during collecting data for ' || l_status || '.');

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)

Line 686: BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));

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);

Line 691: BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);

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;

Line 721: BIS_COLLECTION_UTILITIES.PUT_LINE('==================================================================================');

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

Line 722: BIS_COLLECTION_UTILITIES.put_line('Period Close Adjustments Collection started at ' || TO_CHAR(g_sysdate, 'DD-MON-YYYY HH24:MI:SS'));

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

Line 730: BIS_COLLECTION_UTILITIES.put_line('Global start date is not available. Cannot proceed.');

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;

Line 731: BIS_COLLECTION_UTILITIES.put_line(SQLERRM);

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;

Line 739: BIS_COLLECTION_UTILITIES.put_line('Failed to collect adjustments into staging tables.');

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

Line 768: BIS_COLLECTION_UTILITIES.put_line('Missing currency rate.');

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;

Line 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.');

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;

Line 775: BIS_COLLECTION_UTILITIES.put_line('All currency conversion rates were found.');

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);

Line 781: BIS_COLLECTION_UTILITIES.put_line(

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

Line 783: BIS_COLLECTION_UTILITIES.put_line('Please run the concurrent program: Update Inventory Value and Turns Base Summary, to try finishing this process.');

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;

Line 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.');

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

Line 791: BIS_COLLECTION_UTILITIES.put_line('Failed to clean staging tables.');

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

Line 806: BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(l_rows1) || ' rows have been inserted into fact table from discrete/manufacturing organizations.');

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:

Line 807: BIS_COLLECTION_UTILITIES.put_line('Period Close Adjustments Collection finished at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));

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

Line 808: BIS_COLLECTION_UTILITIES.PUT_LINE('==================================================================================');

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

Line 813: BIS_COLLECTION_UTILITIES.put_line('Period close adjustments process failed.');

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);

Line 814: BIS_COLLECTION_UTILITIES.put_line(SQLERRM);

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: