1 PACKAGE opi_dbi_inv_cca_pkg AS
2 /*$Header: OPIDEICCAS.pls 120.1 2005/08/02 04:55:53 visgupta noship $ */
3
4 /**************************************************
5 * Package Level Constants
6 **************************************************/
7
8 -- All OPI data is marked as source 1, OPM data is marked as source 2
9 C_OPI_SOURCE CONSTANT NUMBER := 1;
10 C_OPM_SOURCE CONSTANT NUMBER := 2;
11 C_PRER12_SOURCE CONSTANT NUMBER := 3;
12
13 -- All completed cycle count entries have a status of 5
14 C_COMPLETED_CCA_ENTRY CONSTANT NUMBER := 5;
15
16 -- The MTA inventory accounting line type is 1
17 C_INVENTORY_ACCOUNT CONSTANT NUMBER := 1;
18
19 -- The MMT transaction type id for cycle count adjustments is 4
20 C_MMT_CYCLE_COUNT_ADJ CONSTANT NUMBER := 4;
21
22 -- EURO currency became official on 1st Jan 1999
23 C_EURO_START_DATE CONSTANT DATE := to_date ('01/01/1999', 'mm/dd/yyyy');
24
25 -- GL API returns -3 if EURO rate missing on 01-JAN-1999
26 C_EURO_MISSING_AT_START CONSTANT NUMBER := -3;
27
28 -- This is the OPI_DBI_INV_CCA_PKG
29 C_PKG_NAME CONSTANT VARCHAR2 (50) := 'opi_dbi_inv_cca_pkg';
30
31 -- Rows for this ETL will be created with TYPE='CCA' in the log table
32 C_CCA_MARKER CONSTANT VARCHAR2(3) := 'CCA';
33
34 -- Rows in log table with 'MMT' type
35 C_MMT_MARKER CONSTANT VARCHAR2(3) := 'MMT';
36
37 -- Rows in log table with 'MIF' type
38 C_MIF_MARKER CONSTANT VARCHAR2(3) := 'MIF';
39
40 -- MMT has 5 decimal points of precision
41 C_MMT_PRECISION CONSTANT NUMBER := 5;
42
43 -- Return codes for termination
44 C_ERROR CONSTANT NUMBER := -1; -- concurrent manager error code
45 C_WARNING CONSTANT NUMBER := 1; -- concurrent manager warning code
46 C_SUCCESS CONSTANT NUMBER := 0; -- concurrent manager success code
47
48 -- Error messages will be 300 characters (arbitrary choice)
49 C_ERRBUF_SIZE CONSTANT NUMBER := 300;
50
51 -- Hit are 1, misses are 0
52 C_MISS CONSTANT NUMBER := 0;
53 C_HIT CONSTANT NUMBER := 1;
54
55 -- Exact matches are 1, non matches are 0
56 C_NO_MATCH CONSTANT NUMBER := 0;
57 C_EXACT_MATCH CONSTANT NUMBER := 1;
58
59 -- Expense subinventories are marked as 2 in the asset_inventory field
60 C_EXPENSE_SUBINVENTORY CONSTANT NUMBER := 2;
61
62 -- -1 is an invalid transaction_id to mark in the log
63 C_INVALID_TRANSACTION_ID CONSTANT NUMBER := -1;
64
65 -- marker for secondary conv. rate if the primary and secondary curr codes
66 -- and rate types are identical. Can't be -1, -2, -3 since the FII APIs
67 -- return those values.
68 C_PRI_SEC_CURR_SAME_MARKER CONSTANT NUMBER := -9999;
69
70
71 -- For Bounds
72 C_ETL_TYPE VARCHAR2(12) := 'CYCLE_COUNT';
73 C_LOAD_INIT VARCHAR2(4) := 'INIT';
74 C_LOAD_INCR VARCHAR2(4) := 'INCR';
75 C_LOG_MMT_DRV_TBL VARCHAR2(3) :='MMT';
76 C_LOG_GTV_DRV_TBL VARCHAR2(3) :='GTV';
77
78
79 /**************************************************
80 * Package Level User Defined Exceptions for functions
81 **************************************************/
82
83 -- Exception to raise if global parameters such as global
84 -- start date and global currency code are not available
85 GLOBAL_SETUP_MISSING EXCEPTION;
86 PRAGMA EXCEPTION_INIT (GLOBAL_SETUP_MISSING, -20001);
87 GLOBAL_SETUP_MISSING_MESG CONSTANT VARCHAR2(200) := 'Unable to verify setup of global start date and global currency code.';
88
89
90 -- Exception to raise if the OPI schema information is not found
91 SCHEMA_INFO_NOT_FOUND EXCEPTION;
92 PRAGMA EXCEPTION_INIT (SCHEMA_INFO_NOT_FOUND, -20002);
93 SCHEMA_INFO_NOT_FOUND_MESG CONSTANT VARCHAR2(200) := 'OPI schema information not found.';
94
95
96 -- Exception to raise if the bounds in the log table are incorrect.
97 INIT_BOUNDS_MISSING EXCEPTION;
98 PRAGMA EXCEPTION_INIT (INIT_BOUNDS_MISSING, -20003);
99 INIT_BOUNDS_MISSING_MESG CONSTANT VARCHAR2(200) := 'Bounds information missing for initial load in the log table. Please run the initial load collection program using the request set.';
100
101 -- Exception to raise if initial/incremental load wrappers are unable to
102 -- find the global start date.
103 GLOBAL_START_DATE_NULL EXCEPTION;
104 PRAGMA EXCEPTION_INIT (GLOBAL_START_DATE_NULL, -20004);
105 GLOBAL_START_DATE_NULL_MESG CONSTANT VARCHAR2(200) := 'The global start date seems null. Please set up the global start date correctly.';
106
107 -- Exception to raise if initial/incremental load wrappers are unable to
108 -- find the DBI global currency code.
109 NO_GLOBAL_CURR_CODE EXCEPTION;
110 PRAGMA EXCEPTION_INIT (NO_GLOBAL_CURR_CODE, -20005);
111 NO_GLOBAL_CURR_CODE_MESG CONSTANT VARCHAR2(200) := 'The DBI global currency code is NULL. Please set up the global currency code correctly.';
112
113 -- Exception to raise if initial/incremental load wrappers are unable to
114 -- find the DBI global currency code.
115 NO_GLOBAL_RATE_TYPE EXCEPTION;
116 PRAGMA EXCEPTION_INIT (NO_GLOBAL_RATE_TYPE, -20006);
117 NO_GLOBAL_RATE_TYPE_MESG CONSTANT VARCHAR2(200) := 'The DBI global rate type is NULL. Please set up the global rate type correctly.';
118
119
120 -- Exception to raise if initial/incremental load wrappers are unable to
121 -- find the DBI global currency code.
122 MISSING_CONV_RATES EXCEPTION;
123 PRAGMA EXCEPTION_INIT (MISSING_CONV_RATES, -20007);
124 MISSING_CONV_RATES_MESG CONSTANT VARCHAR2(200) := 'There are missing currency conversion rates. Check the program output and log files for missing rate details. Please fix the rates and run the program again.';
125
126
127 -- Exception to raise if the bounds in the log table are incorrect for the
128 -- incremental load.
129 INCR_BOUNDS_MISSING EXCEPTION;
130 PRAGMA EXCEPTION_INIT (INCR_BOUNDS_MISSING, -20008);
131 INCR_BOUNDS_MISSING_MESG CONSTANT VARCHAR2(200) := 'Bounds information missing for incremental load in the log table. Please run the incremental load collection program using the request set.';
132
133 -- Exception to raise if one of the secondary currency code and rate type
134 -- is null, but the other is not.
135 SEC_CURR_SETUP_INVALID EXCEPTION;
136 PRAGMA EXCEPTION_INIT (SEC_CURR_SETUP_INVALID, -20009);
137 SEC_CURR_SETUP_INVALID_MESG CONSTANT VARCHAR2(200) := 'The secondary currency code cannot be null when the rate type is defined or vice versa.';
138
139
140 /**************************************************
141 * Package Level User Defined Exceptions for ETL stages
142 **************************************************/
143
144 -- Exception to raise if initial/incremental load wrappers are unable to
145 -- to complete their initialization steps.
146 INITIALIZATION_ERROR EXCEPTION;
147 PRAGMA EXCEPTION_INIT (INITIALIZATION_ERROR, -21000);
148 INITIALIZATION_ERROR_MESG CONSTANT VARCHAR2(200) := 'An error occurred during the initialization steps (global parameter checking, table truncation, bounds checking). See concurrent log for details.';
149
150 -- Exception to raise if initial/incremental load wrappers are unable to
151 -- to complete their initialization steps.
152 CONV_RATES_ERROR EXCEPTION;
153 PRAGMA EXCEPTION_INIT (CONV_RATES_ERROR, -21010);
154 CONV_RATES_ERROR_MESG CONSTANT VARCHAR2(200) := 'An error occurred during computation of conversion rates. Please fix these errors before re-running the program. See concurrent log for details.';
155
156 -- Exception to raise if initial/incremental load wrappers are unable to
157 -- set up runtime bounds
158 BOUNDS_SETUP_ERROR EXCEPTION;
159 PRAGMA EXCEPTION_INIT (BOUNDS_SETUP_ERROR, -21020);
160 BOUNDS_SETUP_ERROR_MESG CONSTANT VARCHAR2(200) := 'An error occurred during computation of runtime bounds. See concurrent log for details.';
161
162
163 -- Exception to raise if initial/incremental load wrappers are unable to
164 -- extract adjustment entry data successfully.
165 ADJUSTMENT_EXTR_ERROR EXCEPTION;
166 PRAGMA EXCEPTION_INIT (ADJUSTMENT_EXTR_ERROR, -21030);
167 ADJUSTMENT_EXTR_ERROR_MESG CONSTANT VARCHAR2(200) := 'An error occurred during extraction of adjusted cycle count entries. See concurrent log for details.';
168
169 -- Exception to raise if initial/incremental load wrappers are unable to
170 -- extract exact match entry data successfully.
171 EXACT_MATCH_EXTR_ERROR EXCEPTION;
172 PRAGMA EXCEPTION_INIT (EXACT_MATCH_EXTR_ERROR, -21040);
173 EXACT_MATCH_EXTR_ERROR_MESG CONSTANT VARCHAR2(200) := 'An error occurred during extraction of exact match cycle count entries. See concurrent log for details.';
174
175 -- Exception to raise if initial/incremental load wrappers are unable to
176 -- update runtime bounds after successful extraction.
177 BOUNDS_UPDATE_ERROR EXCEPTION;
178 PRAGMA EXCEPTION_INIT (BOUNDS_UPDATE_ERROR, -21050);
179 BOUNDS_UPDATE_ERROR_MESG CONSTANT VARCHAR2(200) := 'An error occurred while updating the runtime bounds after data extraction. See concurrent log for details.';
180 -- Exception to raise if initial/incremental load wrappers are unable to
181 -- merge the newly extracted data to the fact table
182 FACT_MERGE_ERROR EXCEPTION;
183 PRAGMA EXCEPTION_INIT (FACT_MERGE_ERROR, -21060);
184 FACT_MERGE_ERROR_MESG CONSTANT VARCHAR2(200) := 'An error occurred while inserting the newly extracted data to the base fact table. See concurrent log for details.';
185
186 -- Exception to raise if the OPM function throws an exception
187 OPM_EXTRACTION_ERROR EXCEPTION;
188 PRAGMA EXCEPTION_INIT (OPM_EXTRACTION_ERROR, -21070);
189 OPM_EXTRACTION_ERROR_MESG CONSTANT VARCHAR2(200) := 'An error occurred while extracting data for process manufacturing organizations. See concurrent log for details.';
190
191 -- Exception to raise if the extract_CCA_MMT function throws an exception
192 CCA_MMT_STG_ERROR EXCEPTION;
193 PRAGMA EXCEPTION_INIT (CCA_MMT_STG_ERROR, -21070);
194 CCA_MMT_STG_ERROR_MESG CONSTANT VARCHAR2(200) := 'An error occurred while extracting data from MMT. See concurrent log for details.';
195
196 /* run_initial_load
197
198 Wrapper routine for the initial load of the cycle count accuracy ETL.
199
200 Parameters:
201 retcode - 0 on successful completion, -1 on error and 1 for warning.
202 errbuf - empty on successful completion, message on error or warning
203
204 History:
205 Date Author Action
206 01/12/04 Dinkar Gupta Defined specification.
207
208 */
209 PROCEDURE run_initial_load (errbuf OUT NOCOPY VARCHAR2,
210 retcode OUT NOCOPY NUMBER);
211
212
213 /* run_incr_load
214
215 Wrapper routine for the incremental load of the cycle count accuracy ETL.
216
217 Parameters:
218 retcode - 0 on successful completion, -1 on error and 1 for warning.
219 errbuf - empty on successful completion, message on error or warning
220
221 History:
222 Date Author Action
223 01/12/04 Dinkar Gupta Defined specification.
224
225 */
226 PROCEDURE run_incr_load (errbuf OUT NOCOPY VARCHAR2,
227 retcode OUT NOCOPY NUMBER);
228
229
230
231 END opi_dbi_inv_cca_pkg;
[Home] [Help]