1 PACKAGE opi_dbi_wms_storage_utz_pkg AS
2 /*$Header: OPIDEWSTORS.pls 120.0 2005/05/24 19:14:10 appldev noship $ */
3
4 --Name of the package
5 C_PKG_NAME CONSTANT VARCHAR2 (40) := 'OPI_DBI_WMS_STORAGE_UTZ_PKG';
6
7 -- Very old start date - for starting the initial load
8 C_START_RUN_DATE CONSTANT DATE := to_date ('01-01-1950', 'DD-MM-YYYY');
9
10 -- ETL identifier for the log table rows. No intended use yet.
11 C_ETL_TYPE CONSTANT VARCHAR2 (10) := 'STR_UTZ';
12
13 -- Return codes for termination
14 C_ERROR CONSTANT NUMBER := -1; -- concurrent manager error code
15 C_WARNING CONSTANT NUMBER := 1; -- concurrent manager warning code
16 C_SUCCESS CONSTANT NUMBER := 0; -- concurrent manager success code
17
18 -- Identification of process vs. discrete organizations in the
19 -- inventory fact table
20 C_DISCRETE_ORGS CONSTANT NUMBER := 1;
21 C_PROCESS_ORGS CONSTANT NUMBER := 2;
22
23 -- Error messages will be 300 characters (arbitrary choice)
24 C_ERRBUF_SIZE CONSTANT NUMBER := 300;
25
26 -- Weight measure code in the measure master table, OPI_DBI_REP_UOMS
27 C_WT_MEASURE_CODE CONSTANT VARCHAR2(40) := 'WT';
28
29 -- Vol measure code in the measure master table, OPI_DBI_REP_UOMS
30 C_VOL_MEASURE_CODE CONSTANT VARCHAR2(40) := 'VOL';
31
32 -- Dummy UOM code to use when the UOM code set up is NULL. Needed during
33 -- join conditions of outer joins.
34 C_DUMMY_UOM_CODE CONSTANT VARCHAR2 (3) := '#?$';
35
36 -- Error codes for conversion rates:
37 -- Conversion is impossible because of missing setup
38 C_CONV_NOT_SETUP CONSTANT NUMBER := -1;
39
40 /****************************************
41 * Success Messages
42 ****************************************/
43
44 C_SUCCESS_MESG CONSTANT VARCHAR2 (300) := 'Successful Termination.';
45
46
47 /****************************************
48 * Error Messages
49 ****************************************/
50 C_STOR_INIT_LOAD_ERROR_MESG CONSTANT VARCHAR2 (300) :=
51 'The Warehouse Storage Utilization report program initial load has terminated with errors. Please refer to the concurrent log file and/or concurrent request output file for details.';
52
53 C_STOR_INCR_LOAD_ERROR_MESG CONSTANT VARCHAR2 (300) :=
54 'The Warehouse Storage Utilization report program incremental load has terminated with errors. Please refer to the concurrent log file and/or concurrent request output file for details.';
55
56 /**************************************************
57 * Package Level User Defined Exceptions for functions
58 **************************************************/
59
60 -- Exception to raise if the OPI schema information is not found
61 SCHEMA_INFO_NOT_FOUND EXCEPTION;
62 PRAGMA EXCEPTION_INIT (SCHEMA_INFO_NOT_FOUND, -20001);
63 SCHEMA_INFO_NOT_FOUND_MESG CONSTANT VARCHAR2(200) := 'OPI schema information not found.';
64
65 -- Exception to raise if global parameters such as global
66 -- start date and global currency code are not available
67 GLOBAL_SETUP_MISSING EXCEPTION;
68 PRAGMA EXCEPTION_INIT (GLOBAL_SETUP_MISSING, -20002);
69 GLOBAL_SETUP_MISSING_MESG CONSTANT VARCHAR2(200) := 'Unable to obtain setup information of global start date, OPI schema etc..';
70
71 -- Exception to raise if the setup of tables at the start fails
72 TABLE_SETUP_FAILED EXCEPTION;
73 PRAGMA EXCEPTION_INIT (TABLE_SETUP_FAILED, -20003);
74 TABLE_SETUP_FAILED_MESG CONSTANT VARCHAR2(200) := 'The setup/cleanup of tables required at the start of the program was not successful.';
75
76 -- Exception to raise if the incremental run does not find the
77 -- last run record in the log table.
78 LAST_RUN_RECORD_MISSING EXCEPTION;
79 PRAGMA EXCEPTION_INIT (LAST_RUN_RECORD_MISSING, -20004);
80 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.';
81
82 -- Exception to raise if unable to update the log table.
83 LOG_UPDATE_FAILED EXCEPTION;
84 PRAGMA EXCEPTION_INIT (LOG_UPDATE_FAILED, -20005);
85 LOG_UPDATE_FAILED_MESG CONSTANT VARCHAR2(200) := 'Unable to the log table with the current run information.';
86
87 -- Exception to raise if unable to extract locator capacity information
88 NO_REP_UOMS_DEFINED EXCEPTION;
89 PRAGMA EXCEPTION_INIT (NO_REP_UOMS_DEFINED, -20006);
90 NO_REP_UOMS_DEFINED_MESG CONSTANT VARCHAR2(200) := 'No reporting UOMs have been defined for the weight and volume measures. The Current Capacity Report can only collect data for a measure once a reporting UOM has been defined.';
91
92 -- Exception to raise if unable to extract item storage data
93 CONV_RATE_CALC_FAILED EXCEPTION;
94 PRAGMA EXCEPTION_INIT (CONV_RATE_CALC_FAILED, -20007);
95 CONV_RATE_CALC_FAILED_MESG CONSTANT VARCHAR2(200) := 'Unable to extract conversion rates for the needed item/org combinations.';
96
97
98 -- Exception to raise if unable to extract item storage data
99 MISSING_RATES_FOUND EXCEPTION;
100 PRAGMA EXCEPTION_INIT (MISSING_RATES_FOUND, -20008);
101 MISSING_RATES_FOUND_MESG CONSTANT VARCHAR2(200) := 'There are missing conversion rates to the reporting UOMs. Please check the concurrent output file for details of missing rates that are causing the program to error out.';
102
103 -- Exception to raise if unable to extract item storage data
104 MERGE_RATES_FAILED EXCEPTION;
105 PRAGMA EXCEPTION_INIT (MERGE_RATES_FAILED, -20009);
106 MERGE_RATES_FAILED_MESG CONSTANT VARCHAR2(200) := 'Failed to merge new conversion rates into the conversion rates fact table.';
107
108 -- Exception to raise if find missing rates
109 CONV_RATES_CHECK_FAILED EXCEPTION;
110 PRAGMA EXCEPTION_INIT (CONV_RATES_CHECK_FAILED, -20010);
111 CONV_RATES_CHECK_FAILED_MESG CONSTANT VARCHAR2(200) := 'Unable to perform check for missing conversion rates.';
112
113 -- Exception to raise if find missing rates
114 TRX_STG_TO_FACT_FAILED EXCEPTION;
115 PRAGMA EXCEPTION_INIT (TRX_STG_TO_FACT_FAILED, -20011);
116 TRX_STG_TO_FACT_FAILED_MESG CONSTANT VARCHAR2(200) := 'Unable to transfer from fact table to staging given conversion rate errors.';
117
118 /**************************************************
119 * Public procedures
120 **************************************************/
121
122 /* wt_vol_init_load
123
124 Set up conversion rates in the reporting UOM conversion rates facts
125 for the weight and volume measures.
126
127 The conversion rates are set up for all items in the inventory
128 value fact, OPI_DBI_INV_VALUE_F, for WMS enabled, discrete
129 manufacturing organizations.
130
131 Currently only interested in conversion rates for weights/volumes.
132
133 History:
134 Date Author Action
135 12/13/04 Dinkar Gupta Wrote Function.
136
137 */
138 PROCEDURE wt_vol_init_load (errbuf OUT NOCOPY VARCHAR2,
139 retcode OUT NOCOPY NUMBER);
140
141 /* wt_vol_incr_load
142
143 Set up conversion rates in the reporting UOM conversion rates facts
144 for the weight and volume measures.
145
146 The conversion rates are set up for all items in the inventory
147 value fact, OPI_DBI_INV_VALUE_F, for WMS enabled, discrete
148 manufacturing organizations.
149
150 Currently only interested in conversion rates for weights/volumes.
151
152 During the incremental load, consider only those items/orgs that
153 have been added during incremental runs of the inventory program
154 after the previous run of the WMS conversion rates program.
155
156 History:
157 Date Author Action
158 12/13/04 Dinkar Gupta Wrote Function.
159
160 */
161 PROCEDURE wt_vol_incr_load (errbuf OUT NOCOPY VARCHAR2,
162 retcode OUT NOCOPY NUMBER);
163
164
165 END opi_dbi_wms_storage_utz_pkg;
[Home] [Help]