1 PACKAGE opi_dbi_wms_utility_pkg AS
2 /*$Header: OPIDEWUTILS.pls 120.0 2005/05/24 17:56:29 appldev noship $ */
3
4 /**************************************************
5 * Global variables
6 **************************************************/
7
8 g_missing_uom boolean := false;
9
10 /****************************************
11 * Package Level Constants
12 ****************************************/
13
14 --Name of the package
15 C_PKG_NAME CONSTANT VARCHAR2 (40) := 'OPI_DBI_WMS_UTILITY_PKG';
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 -- Item level aggregation level flag value
23 C_ITEM_AGGR_LEVEL CONSTANT NUMBER := 0;
24
25 -- Error messages will be 300 characters (arbitrary choice)
26 C_ERRBUF_SIZE CONSTANT NUMBER := 300;
27
28 -- Column Spacing (5 spaces)
29 C_COL_SPACING CONSTANT NUMBER := 5;
30
31 -- Blank line for printing
32 C_BLANK_LINE CONSTANT VARCHAR2(1) := ' ';
33
34 -- Number of rows to report before deciding to print SQL.
35 C_NUM_ROWS_TO_REPORT NUMBER := 50;
36
37 -- Missing setup codes for the weight/volume measures
38 C_NOTHING_MISSING CONSTANT NUMBER := 0;
39 C_WT_MISSING CONSTANT NUMBER := 1;
40 C_VOL_MISSING CONSTANT NUMBER := 2;
41 C_WT_VOL_MISSING CONSTANT NUMBER := 3;
42
43 -- Various column widths
44 C_ORG_COL_WIDTH CONSTANT NUMBER := 45;
45 C_SUB_COL_WIDTH CONSTANT NUMBER := 15;
46 C_LOCATOR_COL_WIDTH CONSTANT NUMBER := 20;
47 C_ITEM_COL_WIDTH CONSTANT NUMBER := 35;
48 C_VOL_MISSING_COL_WIDTH CONSTANT NUMBER := 15;
49 C_WT_MISSING_COL_WIDTH CONSTANT NUMBER := 15;
50 C_VOL_UOM_COL_WIDTH CONSTANT NUMBER := 30;
51 C_WT_UOM_COL_WIDTH CONSTANT NUMBER := 30;
52
53 -- Asset subinventories are marked as 1.
54 C_ASSET_SUBINVENTORY CONSTANT NUMBER := 1;
55
56 -- Identifier for WDTH based CU1 date.
57 C_WDTH_CU1_DATE_TYPE CONSTANT VARCHAR2 (20) := 'WDTH_CU1_DATE';
58
59 -- Identifier for WMS Pick to ship start_date.
60 C_WMS_PTS_DATE_TYPE CONSTANT VARCHAR2 (20) := 'WMS_PTS_GSD';
61
62
63 /**************************************************
64 * Error messages
65 **************************************************/
66 C_ITEM_LOC_CONV_RATE_ERR CONSTANT VARCHAR2 (200) := 'The detail listing of item/locator conversion rates errors has failed';
67
68 /**************************************************
69 * Package Level User Defined Exceptions for functions
70 **************************************************/
71
72 -- Exception to raise item level conversion rates details program fails.
73 ITEM_CONV_RATES_DET_ERR EXCEPTION;
74 PRAGMA EXCEPTION_INIT (ITEM_CONV_RATES_DET_ERR, -20001);
75 ITEM_CONV_RATES_DET_ERR_MESG CONSTANT VARCHAR2(200) := 'An error occurred in listing out the item level conversion rates errors.';
76
77 -- Exception to raise item level conversion rates details program fails.
78 LOC_CONV_RATES_DET_ERR EXCEPTION;
79 PRAGMA EXCEPTION_INIT (LOC_CONV_RATES_DET_ERR, -20002);
80 LOC_CONV_RATES_DET_ERR_MESG CONSTANT VARCHAR2(200) := 'An error occurred in listing out the locator level conversion rates errors.';
81
82
83 /**************************************************
84 * Public procedures
85 **************************************************/
86
87 /* report_item_setup_missing
88
89 Reports the items in WMS organizations which are missing
90 weight and/or volume setup.
91
92 Parameters:
93 errbuf - error message on unsuccessful termination
94 retcode - 0 on success, 1 on warning, -1 on error
95
96 History:
97 Date Author Action
98 12/20/04 Dinkar Gupta Wrote Function.
99
100 */
101 PROCEDURE report_item_setup_missing (errbuf OUT NOCOPY VARCHAR2,
102 retcode OUT NOCOPY NUMBER);
103
104
105 /* report_locator_setup_missing
106
107 Reports the locators in WMS organizations which are missing
108 weight and/or volume setup.
109
110 Parameters:
111 errbuf - error message on unsuccessful termination
112 retcode - 0 on success, 1 on warning, -1 on error
113
114 History:
115 Date Author Action
116 12/20/04 Dinkar Gupta Wrote Function.
117
118 */
119 PROCEDURE report_locator_setup_missing (errbuf OUT NOCOPY VARCHAR2,
120 retcode OUT NOCOPY NUMBER);
121
122
123
124 /* report_item_loc_conv_rate_err
125
126 Report the item level and locator level information for missing
127 conversion rates found by the ETLs for the Warehouse Storage Utilized
128 and Current Capacity Utilization reports.
129
130 This function is meant to be publicly accessed by a standalone
131 concurrent program that the user can optionally run to debug
132 their item/locator setups.
133
134 History:
135 Date Author Action
136 01/10/05 Dinkar Gupta Wrote Function.
137
138 */
139 PROCEDURE report_item_loc_conv_rate_err (errbuf OUT NOCOPY VARCHAR2,
140 retcode OUT NOCOPY NUMBER);
141
142 /* set_wdth_cu1_date
143
144 Get the CU1 date based on the data in the WMS_DISPATCHED_TASKS_HISTORY
145 table. The CU1 date is the first one with transaction_temp_id not set
146 as null.
147
148 If no such date is found, then set the sysdate to be the CU1 date.
149
150 In general since this API can be called simultaneously by multiple
151 ETLs, it will merge the CU1 date into the OPI_DBI_CONC_PROG_RUN_LOG
152 with type = 'WDTH_CU1_DATE'.
153
154 Parameters:
155 p_overwrite - if true, then function always picks the date from WDTH.
156 if false, then does nothing if a record already exists
157 in the OPI_DBI_CONC_PROG_RUN_LOG.
158
159 History:
160 Date Author Action
161 02/17/05 Dinkar Gupta Wrote Function.
162
163 */
164 PROCEDURE set_wdth_cu1_date (p_overwrite BOOLEAN);
165
166
167 /* set_wms_pts_gsd
168
169 Set the WMS pick to ship rack start date as the max of the
170 WDTH CU1 date and GSD with a type of 'WMS_PTS_GSD'.
171
172 As a side effect, populates/updates the WDTH CU1 date as needed.
173
174 Parameters:
175 p_overwrite - if true, then function force updates the WDTH CU1 date.
176 if false, then WDTH CU1 date is not modified (if
177 it already exists).
178
179 History:
180 Date Author Action
181 02/18/05 Dinkar Gupta Wrote Function.
182
183 */
184 PROCEDURE set_wms_pts_gsd (p_overwrite BOOLEAN);
185
186
187 function get_uom_rate (p_inventory_item_id varchar2,
188 p_primary_uom_code varchar2,
189 p_txn_uom_code varchar2) return number parallel_enable;
190
191
192 END opi_dbi_wms_utility_pkg;
[Home] [Help]