1 PACKAGE opi_dbi_rep_uom_std_conv_pkg AS
2 /*$Header: OPIDEREPUMS.pls 120.0 2005/05/24 18:22:35 appldev noship $ */
3
4 /****************************************
5 * Package Level Constants
6 ****************************************/
7
8 --Name of the package
9 C_PKG_NAME CONSTANT VARCHAR2 (40) := 'OPI_DBI_REP_UOM_STD_CONV_PKG';
10
11 -- Very old start date - for starting the initial load
12 C_START_RUN_DATE CONSTANT DATE := to_date ('01-01-1950', 'DD-MM-YYYY');
13
14 -- ETL identifier for the log table rows
15 C_ETL_TYPE CONSTANT VARCHAR2 (10) := 'REP_UOM';
16
17 -- Return codes for termination
18 C_ERROR CONSTANT NUMBER := -1; -- concurrent manager error code
19 C_WARNING CONSTANT NUMBER := 1; -- concurrent manager warning code
20 C_SUCCESS CONSTANT NUMBER := 0; -- concurrent manager success code
21
22 -- Error messages will be 300 characters (arbitrary choice)
23 C_ERRBUF_SIZE CONSTANT NUMBER := 300;
24
25 -- Conversion rates precision - use 5 since that is what the core OLTP
26 -- Inventory Application supports.
27 C_CONV_PRECISION CONSTANT NUMBER := 5;
28
29 -- Measure lookup type for FND_LOOKUPS
30 C_MEASURE_LOOKUP_TYPE CONSTANT VARCHAR2(40) := 'OPI_DBI_UOM_MEASURE_TYPE';
31
32 -- Use inventory item Id of 0 for standard rates in the staging table.
33 C_STD_RATE_ITEM_ID CONSTANT NUMBER := 0;
34
35 -- Conversion rate types. 1 for Intra-class, 2 for Inter-class. Currently
36 -- only using intra-class.
37 C_INTRA_CONV_TYPE CONSTANT NUMBER := 1;
38 C_INTER_CONV_TYPE CONSTANT NUMBER := 2;
39
40 -- Base UOM flag values. 'Y' for base UOM, 'N' for non base UOM.
41 C_IS_BASE_UOM CONSTANT VARCHAR2(1) := 'Y';
42 C_NOT_BASE_UOM CONSTANT VARCHAR2(1) := 'N';
43
44 /****************************************
45 * Success Messages
46 ****************************************/
47
48 C_SUCCESS_MESG CONSTANT VARCHAR2 (300) := 'Successful Termination.';
49
50 /****************************************
51 * Error Messages
52 ****************************************/
53 C_INIT_LOAD_ERROR_MESG CONSTANT VARCHAR2 (300) :=
54 'The Reporting UOMs standard conversion rate fact table update program''s initial load has terminated with errors. Please refer to the concurrent log file and/or concurrent request output file for details.';
55
56 C_INCR_LOAD_ERROR_MESG CONSTANT VARCHAR2 (300) :=
57 'The Reporting UOMs standard conversion rate fact table update program''s incremental load has terminated with errors. Please refer to the concurrent log file and/or concurrent request output file for details.';
58
59 /**************************************************
60 * Package Level User Defined Exceptions for functions
61 **************************************************/
62
63 -- Exception to raise if initial/incremental load wrappers are unable to
64 -- find the global start date.
65 GLOBAL_START_DATE_NULL EXCEPTION;
66 PRAGMA EXCEPTION_INIT (GLOBAL_START_DATE_NULL, -20001);
67 GLOBAL_START_DATE_NULL_MESG CONSTANT VARCHAR2(200) := 'The global start date seems null. Please set up the global start date correctly.';
68
69 -- Exception to raise if the OPI schema information is not found
70 SCHEMA_INFO_NOT_FOUND EXCEPTION;
71 PRAGMA EXCEPTION_INIT (SCHEMA_INFO_NOT_FOUND, -20002);
72 SCHEMA_INFO_NOT_FOUND_MESG CONSTANT VARCHAR2(200) := 'OPI schema information not found.';
73
74 -- Exception to raise if global parameters such as global
75 -- start date and global currency code are not available
76 GLOBAL_SETUP_MISSING EXCEPTION;
77 PRAGMA EXCEPTION_INIT (GLOBAL_SETUP_MISSING, -20003);
78 GLOBAL_SETUP_MISSING_MESG CONSTANT VARCHAR2(200) := 'Unable to obtain setup information of global start date, OPI schema etc..';
79
80 -- Exception to raise if the setup of tables at the start fails
81 TABLE_INIT_SETUP_FAILED EXCEPTION;
82 PRAGMA EXCEPTION_INIT (TABLE_INIT_SETUP_FAILED, -20004);
83 TABLE_INIT_SETUP_FAILED_MESG CONSTANT VARCHAR2(200) := 'The setup/cleanup of tables required at the start of the program was not successful.';
84
85 -- Exception to raise if the incremental run does not find the
86 -- last run record in the log table.
87 LAST_RUN_RECORD_MISSING EXCEPTION;
88 PRAGMA EXCEPTION_INIT (LAST_RUN_RECORD_MISSING, -20005);
89 LAST_RUN_RECORD_MISSING_MESG CONSTANT VARCHAR2(200) := 'The record of when this program was run last cannot be found. The incremental load cannot be run. Please run the initial load.';
90
91 -- Exception to raise if setting up the list of measures of interest failed.
92 MEASURE_LIST_SETUP_FAILED EXCEPTION;
93 PRAGMA EXCEPTION_INIT (MEASURE_LIST_SETUP_FAILED, -20006);
94 MEASURE_LIST_SETUP_FAILED_MESG CONSTANT VARCHAR2(200) := 'Unable to set up the list of measures for which conversions are required.';
95
96 -- Exception to raise if computing all intra class conversion rates
97 -- fails.
98 INTRA_STD_CONV_FAILED EXCEPTION;
99 PRAGMA EXCEPTION_INIT (INTRA_STD_CONV_FAILED, -20007);
100 INTRA_STD_CONV_FAILED_MESG CONSTANT VARCHAR2(200) := 'Unable to compute all intra-class standard conversion rates to the reporting units of measure.';
101
102 -- Exception to raise if computing all inter class conversion rates
103 -- fails.
104 INTER_STD_CONV_FAILED EXCEPTION;
105 PRAGMA EXCEPTION_INIT (INTER_STD_CONV_FAILED, -20008);
106 INTER_STD_CONV_FAILED_MESG CONSTANT VARCHAR2(200) := 'Unable to compute all inter-class standard conversion rates to the reporting units of measure.';
107
108 -- Exception to raise if unable to insert conversion rates into the
109 -- fact table during the initial load.
110 INSERT_NEW_RATES_FAILED EXCEPTION;
111 PRAGMA EXCEPTION_INIT (INSERT_NEW_RATES_FAILED, -20009);
112 INSERT_NEW_RATES_FAILED_MESG CONSTANT VARCHAR2(200) := 'Unable to insert new rates into the rates base fact.';
113
114 -- Exception to raise if unable to merge conversion rates into fact table
115 -- during the incremental load.
116 MERGE_STD_RATES_FAILED EXCEPTION;
117 PRAGMA EXCEPTION_INIT (MERGE_STD_RATES_FAILED, -20010);
118 MERGE_STD_RATES_FAILED_MESG CONSTANT VARCHAR2(200) := 'Unable to update UOM conversion rates.';
119
120 -- Exception to raise if unable to update the log table.
121 LOG_UPDATE_FAILED EXCEPTION;
122 PRAGMA EXCEPTION_INIT (LOG_UPDATE_FAILED, -20011);
123 LOG_UPDATE_FAILED_MESG CONSTANT VARCHAR2(200) := 'Unable to the log table with the current run information.';
124
125
126 /**************************************************
127 * Public procedures
128 **************************************************/
129
130
131 /* populate_rep_uom_std_conv_init
132
133 Wrapper routine for the initial load of the standard conversion
134 rates program.
135
136 Parameters:
137 errbuf - error message on unsuccessful termination
138 retcode - 0 on success, 1 on warning, -1 on error
139
140 History:
141 Date Author Action
142 12/01/04 Dinkar Gupta Wrote Function.
143 */
144 PROCEDURE populate_rep_uom_std_conv_init (errbuf OUT NOCOPY VARCHAR2,
145 retcode OUT NOCOPY NUMBER);
146
147 /* populate_rep_uom_std_conv_incr
148
149 Wrapper routine for the incremental load of the standard conversion
150 rates program.
151
152 Parameters:
153 errbuf - error message on unsuccessful termination
154 retcode - 0 on success, 1 on warning, -1 on error
155
156 History:
157 Date Author Action
158 12/01/04 Dinkar Gupta Wrote Function.
159 */
160 PROCEDURE populate_rep_uom_std_conv_incr (errbuf OUT NOCOPY VARCHAR2,
161 retcode OUT NOCOPY NUMBER);
162
163 END opi_dbi_rep_uom_std_conv_pkg;
[Home] [Help]