1 PACKAGE opi_dbi_wms_capacity_utz_pkg AS
2 /*$Header: OPIDEWCUTZS.pls 120.0 2005/05/24 17:17:30 appldev noship $ */
3
4 /****************************************
5 * Package Level Constants
6 ****************************************/
7
8 --Name of the package
9 C_PKG_NAME CONSTANT VARCHAR2 (40) := 'OPI_DBI_WMS_CAPACITY_UTZ_PKG';
10
11 -- ETL identifier for the log table rows. No intended use yet.
12 C_ETL_TYPE CONSTANT VARCHAR2 (10) := 'CAP_UTZ';
13
14 -- Return codes for termination
15 C_ERROR CONSTANT NUMBER := -1; -- concurrent manager error code
16 C_WARNING CONSTANT NUMBER := 1; -- concurrent manager warning code
17 C_SUCCESS CONSTANT NUMBER := 0; -- concurrent manager success code
18
19 -- Error messages will be 300 characters (arbitrary choice)
20 C_ERRBUF_SIZE CONSTANT NUMBER := 300;
21
22 -- Error code to store missing conversion to the reporting UOM.
23 C_MISSING_REP_CONV CONSTANT NUMBER := -1;
24
25 -- Measure lookup type for FND_LOOKUPS
26 C_MEASURE_LOOKUP_TYPE CONSTANT VARCHAR2(40) := 'OPI_DBI_UOM_MEASURE_TYPE';
27
28 -- Weight measure code in the measure master table, OPI_DBI_REP_UOMS
29 C_WT_MEASURE_CODE CONSTANT VARCHAR2(40) := 'WT';
30
31 -- Vol measure code in the measure master table, OPI_DBI_REP_UOMS
32 C_VOL_MEASURE_CODE CONSTANT VARCHAR2(40) := 'VOL';
33
34 -- Dummy UOM code to use when the UOM code set up is NULL. Needed during
35 -- join conditions of outer joins.
36 C_DUMMY_UOM_CODE CONSTANT VARCHAR2 (3) := '#?$';
37
38 -- Error codes for conversion rates:
39 -- Conversion is impossible because of missing setup
40 C_CONV_NOT_SETUP CONSTANT NUMBER := -1;
41
42 -- From UOM code is not defined
43 C_NO_FROM_UOM_CODE CONSTANT NUMBER := -2;
44
45
46 /****************************************
47 * Success Messages
48 ****************************************/
49
50 C_SUCCESS_MESG CONSTANT VARCHAR2 (300) := 'Successful Termination.';
51
52 /****************************************
53 * Warning Messages
54 ****************************************/
55 C_CURR_UTZ_LOAD_WARN_MESG CONSTANT VARCHAR2 (300) :=
56 'The Current Capacity Utilization report program has terminated with warnings. Please refer to the concurrent log file and/or concurrent request output file for details.';
57
58
59 /****************************************
60 * Error Messages
61 ****************************************/
62 C_CURR_UTZ_LOAD_ERROR_MESG CONSTANT VARCHAR2 (300) :=
63 'The Current Capacity Utilization report program has terminated with errors. Please refer to the concurrent log file and/or concurrent request output file for details.';
64
65 /**************************************************
66 * Package Level User Defined Exceptions for functions
67 **************************************************/
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, -20001);
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, -20002);
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_SETUP_FAILED EXCEPTION;
82 PRAGMA EXCEPTION_INIT (TABLE_SETUP_FAILED, -20003);
83 TABLE_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 unable to extract locator capacity information
86 LOCATOR_CAP_CALC_FAILED EXCEPTION;
87 PRAGMA EXCEPTION_INIT (LOCATOR_CAP_CALC_FAILED, -20004);
88 LOCATOR_CAP_CALC_MESG CONSTANT VARCHAR2(200) := 'Unable to extract weight/volume capacities of the warehouse (extraction at the locator level failed).';
89
90 -- Exception to raise if unable to extract locator capacity information
91 NO_REP_UOMS_DEFINED EXCEPTION;
92 PRAGMA EXCEPTION_INIT (NO_REP_UOMS_DEFINED, -20005);
93 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.';
94
95 -- Exception to raise if there are errors and we need to abort prematurely.
96 PREMATURE_ABORT EXCEPTION;
97 PRAGMA EXCEPTION_INIT (PREMATURE_ABORT, -20006);
98 PREMATURE_ABORT_MESG CONSTANT VARCHAR2(200) := 'The Current Capacity Utilization extraction program has encountered errors and is terminating.';
99
100 -- Exception to raise if summarizing capacities failed
101 SUMMARIZE_CAP_FAILED EXCEPTION;
102 PRAGMA EXCEPTION_INIT (SUMMARIZE_CAP_FAILED, -20006);
103 SUMMARIZE_CAP_FAILED_MESG CONSTANT VARCHAR2(200) := 'An error occurred when summarizing locator capacities to the subinventory and organization level.';
104
105 -- Exception to raise if unable to extract item storage data
106 ITEM_STOR_CALC_FAILED EXCEPTION;
107 PRAGMA EXCEPTION_INIT (ITEM_STOR_CALC_FAILED, -20007);
108 ITEM_STOR_CALC_FAILED_MESG CONSTANT VARCHAR2(200) := 'Unable to extract item storage in the various locators.';
109
110 -- Exception to raise if unable to check for locator capacity conversion
111 -- rates errors.
112 LOCATOR_ERR_CHECK_FAILED EXCEPTION;
113 PRAGMA EXCEPTION_INIT (LOCATOR_ERR_CHECK_FAILED, -20008);
114 LOCATOR_ERR_CHECK_FAILED_MESG CONSTANT VARCHAR2(200) := 'Unable to identify locator capacity conversion rate errors.';
115
116 -- Exception to raise if unable to check for item storage conversion
117 -- rates errors.
118 ITEM_ERR_CHECK_FAILED EXCEPTION;
119 PRAGMA EXCEPTION_INIT (ITEM_ERR_CHECK_FAILED, -20009);
120 ITEM_ERR_CHECK_FAILED_MESG CONSTANT VARCHAR2(200) := 'Unable to identify item weight/volume storage conversion rate errors.';
121
122 /**************************************************
123 * Public procedures
124 **************************************************/
125
126 /* refresh_current_utilization
127
128 Wrapper routine to refresh
129 1. The weight/volume capacity of warehouses and their subinventories.
130 2. The weight and volume of items stored currently in the
131 warehouse's locators.
132
133 Locator capacities:
134 A locator may have a defined max weight and/or max volume capacity.
135 Locators with neither values defined are ignored. Locators with
136 only one of the quantities defined contribute to the corresponding
137 measure for the subinventory to which they belong.
138
139 Item Weights/volume:
140 The item weight will be taken into account only if the locator
141 it is present in has a defined weight capacity. Similar condition
142 for the item volume. Item quantities present in locators with
143 neither weight nor volume capacity defined are ignored.
144
145
146
147 Errors are reported for all:
148 1. Defined locator weight/volume capacities that cannot be
149 converted to the weight/volume reporting UOM values.
150 2. Defined item weights/volumes that cannot be converted
151 into the weight/volume reporting UOM values.
152
153 No data is collected for the report in case of errors, and the fact
154 tables are left truncated.
155
156 Warnings are generated for:
157 1. All locators whose unit weight/volume capacities are undefined.
158 2. All items whose unit weight/volume are undefined.
159
160 Parameters:
161 errbuf - error message on unsuccessful termination
162 retcode - 0 on success, 1 on warning, -1 on error
163
164 History:
165 Date Author Action
166 12/08/04 Dinkar Gupta Wrote Function.
167 */
168 PROCEDURE refresh_current_utilization (errbuf OUT NOCOPY VARCHAR2,
169 retcode OUT NOCOPY NUMBER);
170
171
172
173 END opi_dbi_wms_capacity_utz_pkg;
[Home] [Help]