[Home] [Help]
PACKAGE BODY: APPS.WSM_RESERVATIONS_GRP
Source
1 package body WSM_RESERVATIONS_GRP as
2 /* $Header: WSMGRSVB.pls 120.2 2005/09/27 10:38:16 mprathap noship $ */
3
4 /* Package name */
5 g_pkg_name VARCHAR2(20) := 'WSM_RESERVATIONS_GRP';
6
7 /*logging variables*/
8
9 g_log_level_unexpected NUMBER := FND_LOG.LEVEL_UNEXPECTED ;
10 g_log_level_error number := FND_LOG.LEVEL_ERROR ;
11 g_log_level_exception number := FND_LOG.LEVEL_EXCEPTION ;
12 g_log_level_event number := FND_LOG.LEVEL_EVENT ;
13 g_log_level_procedure number := FND_LOG.LEVEL_PROCEDURE ;
14 g_log_level_statement number := FND_LOG.LEVEL_STATEMENT ;
15
16 g_msg_lvl_unexp_error NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ;
17 g_msg_lvl_error NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR ;
18 g_msg_lvl_success NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS ;
19 g_msg_lvl_debug_high NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH ;
20 g_msg_lvl_debug_medium NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM ;
21 g_msg_lvl_debug_low NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW ;
22
23 g_ret_success varchar2(1) := FND_API.G_RET_STS_SUCCESS;
24 g_ret_error varchar2(1) := FND_API.G_RET_STS_ERROR;
25 g_ret_unexpected varchar2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
26
27 PROCEDURE get_available_supply_demand (
28 x_return_status OUT NOCOPY VARCHAR2 ,
29 x_msg_count OUT NOCOPY NUMBER ,
30 x_msg_data OUT NOCOPY VARCHAR2 ,
31 x_available_quantity OUT NOCOPY NUMBER ,
32 x_source_uom_code OUT NOCOPY VARCHAR2 ,
33 x_source_primary_uom_code OUT NOCOPY VARCHAR2 ,
34 p_organization_id IN NUMBER default null ,
35 p_item_id IN NUMBER default null ,
36 p_revision IN VARCHAR2 default null ,
37 p_lot_number IN VARCHAR2 default null ,
38 p_subinventory_code IN VARCHAR2 default null ,
39 p_locator_id IN NUMBER default null ,
40 p_supply_demand_code IN NUMBER ,
41 p_supply_demand_type_id IN NUMBER ,
42 p_supply_demand_header_id IN NUMBER ,
43 p_supply_demand_line_id IN NUMBER ,
44 p_supply_demand_line_detail IN NUMBER ,
45 p_lpn_id IN NUMBER ,
46 p_project_id IN NUMBER default null ,
47 p_task_id IN NUMBER default null ,
48 p_api_version_number IN NUMBER default 1.0 ,
49 p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
50 )
51
52 IS
53 /* API version stored locally */
54 l_api_version NUMBER := 1.0;
55 l_api_name VARCHAR2(30) := 'get_available_supply_demand';
56
57 /* Module name for logging */
58 l_module VARCHAR2(100) := 'wsm.plsql.WSM_RESERVATIONS_GRP.get_available_supply_demand';
59
60 /* local variable for debug purpose */
61 l_stmt_num NUMBER := 0;
62 l_msg_tokens WSM_Log_PVT.token_rec_tbl;
63 l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
64 l_item_id wip_discrete_jobs.primary_item_id%TYPE; --bug 4633035
65
66 BEGIN
67
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69
70 l_stmt_num := 10;
71 /* Initialize message list if p_init_msg_list is set to TRUE. */
72 IF FND_API.to_Boolean( p_init_msg_lst ) THEN
73 FND_MSG_PUB.initialize;
74 /* Message list enabled....-- EVENT */
75 --logging
76 end if;
77
78 l_stmt_num := 20;
79 /* Check for the API compatibilty */
80 IF NOT FND_API.Compatible_API_Call( l_api_version,
81 p_api_version_number,
82 g_pkg_name,
83 l_api_name
84 )
85 THEN
86 --logging here...
87 /* Incompatible versions...*/
88 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
89 END IF;
90
91 l_stmt_num := 30;
92 SELECT WDJ.NET_QUANTITY,WDJ.PRIMARY_ITEM_ID
93 INTO x_available_quantity,l_item_id
94 FROM WIP_DISCRETE_JOBS WDJ
95 WHERE WDJ.WIP_ENTITY_ID = p_supply_demand_header_id;
96
97 l_stmt_num := 40;
98 select primary_uom_code
99 into x_source_primary_uom_code
100 from mtl_system_items
101 where inventory_item_id=l_item_id --p_item_id
102 and organization_id=p_organization_id;
103
104 x_source_uom_code := x_source_primary_uom_code;
105
106 x_return_status := FND_API.G_RET_STS_SUCCESS;
107
108 EXCEPTION
109 when no_data_found then
110 x_return_status := fnd_api.g_ret_sts_error;
111 x_msg_data := 'No job with the given wip_entity_id found';
112 WHEN FND_API.G_EXC_ERROR THEN
113 x_return_status := G_RET_ERROR ;
114
115 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
116 x_return_status := G_RET_UNEXPECTED ;
117 x_msg_data := SUBSTR('WSM_RESERVATIONS_GRP.validate_supply_demand: Unexpected error: '||SQLERRM, 1, 500);
118
119 WHEN OTHERS THEN
120 /* handle it... */
121 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
122 x_msg_data := SUBSTR('WSM_RESERVATIONS_GRP.get_available_supply_demand: Unexpexted error: '||SQLERRM, 1, 500);
123
124 END;
125
126 PROCEDURE validate_supply_demand (
127 x_return_status OUT NOCOPY VARCHAR2
128 , x_msg_count OUT NOCOPY NUMBER
129 , x_msg_data OUT NOCOPY VARCHAR2
130 , x_valid_status OUT NOCOPY VARCHAR2
131 , p_organization_id IN NUMBER
132 , p_item_id IN NUMBER
133 , p_supply_demand_code IN NUMBER
134 , p_supply_demand_type_id IN NUMBER
135 , p_supply_demand_header_id IN NUMBER
136 , p_supply_demand_line_id IN NUMBER
137 , p_supply_demand_line_detail IN NUMBER
138 , p_demand_ship_date IN DATE
139 , p_expected_receipt_date IN DATE
140 , p_api_version_number IN NUMBER default 1.0
141 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
142 )
143 IS
144
145 l_scheduled_completion_date DATE;
146 l_net_qty NUMBER;
147 l_status_type NUMBER;
148
149 /* API version stored locally */
150 l_api_version NUMBER := 1.0;
151 l_api_name VARCHAR2(30) := 'validate_supply_demand';
152
153 /* Module name for logging */
154 l_module VARCHAR2(100) := 'wsm.plsql.WSM_RESERVATIONS_GRP.validate_supply_demand';
155
156 /* local variable for debug purpose */
157 l_stmt_num NUMBER := 0;
158 l_msg_tokens WSM_Log_PVT.token_rec_tbl;
159 l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
160
161 BEGIN
162
163 x_return_status := FND_API.G_RET_STS_SUCCESS;
164
165 l_stmt_num := 10;
166 /* Initialize message list if p_init_msg_list is set to TRUE. */
167 IF FND_API.to_Boolean( p_init_msg_lst ) THEN
168 FND_MSG_PUB.initialize;
169 /* Message list enabled....-- EVENT */
170 --logging
171 end if;
172
173 l_stmt_num := 20;
174 /* Check for the API compatibilty */
175 IF NOT FND_API.Compatible_API_Call( l_api_version,
176 p_api_version_number,
177 g_pkg_name,
178 l_api_name
179 )
180 THEN
181 --logging here...
182 /* Incompatible versions...*/
183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
184 END IF;
185
186 l_stmt_num := 30;
187 select
188 wdj.scheduled_completion_date,
189 wdj.net_quantity,
190 wdj.status_type
191 into
192 l_scheduled_completion_date,
193 l_net_qty,
194 l_status_type
195 from wip_entities we,
196 wip_discrete_jobs wdj
197 where wdj.wip_entity_id = p_supply_demand_header_id
198 and we.wip_entity_id = wdj.wip_entity_id
199 and wdj.job_type in (1,3)
200 and we.entity_type = 5;
201
202 If l_status_type not in (1,3,6) then
203 x_valid_status := 'N';
204 --Return error; job not in released/unreleased/on-hold status
205 /*event log*/
206 IF g_log_level_event >= l_log_level OR FND_MSG_PUB.check_msg_level(G_MSG_LVL_SUCCESS) then
207 l_msg_tokens.delete;
208 WSM_log_PVT.logMessage(p_module_name => l_module ,
209 p_msg_name => 'WSM_INVALID_JOB',
210 p_msg_appl_name => 'WSM' ,
211 p_msg_tokens => l_msg_tokens ,
212 p_stmt_num => l_stmt_num ,
213 p_fnd_msg_level => G_MSG_LVL_ERROR ,
214 p_fnd_log_level => G_LOG_LEVEL_ERROR ,
215 p_run_log_level => l_log_level
216 );
217 END IF;
218 elsif p_demand_ship_date < l_scheduled_completion_date then
219 x_valid_status := 'N';
220 --Return error; the qty wont be available before the need-by date
221 IF g_log_level_event >= l_log_level OR FND_MSG_PUB.check_msg_level(G_MSG_LVL_SUCCESS) then
222 l_msg_tokens.delete;
223 WSM_log_PVT.logMessage(p_module_name => l_module ,
224 p_msg_name => 'WSM_COMPL_LATE',
225 p_msg_appl_name => 'WSM' ,
226 p_msg_tokens => l_msg_tokens ,
227 p_stmt_num => l_stmt_num ,
228 p_fnd_msg_level => G_MSG_LVL_ERROR ,
229 p_fnd_log_level => G_LOG_LEVEL_ERROR ,
230 p_run_log_level => l_log_level
231 );
232 END IF;
233 elsif l_net_qty <= 0 then
234 x_valid_status := 'N';
235 --Return error; the qty available is less than zero.
236 IF g_log_level_event >= l_log_level OR FND_MSG_PUB.check_msg_level(G_MSG_LVL_SUCCESS) then
237 l_msg_tokens.delete;
238 WSM_log_PVT.logMessage(p_module_name => l_module ,
239 p_msg_name => 'WSM_NET_QTY_ZERO',
240 p_msg_appl_name => 'WSM' ,
241 p_msg_tokens => l_msg_tokens ,
242 p_stmt_num => l_stmt_num ,
243 p_fnd_msg_level => G_MSG_LVL_ERROR ,
244 p_fnd_log_level => G_LOG_LEVEL_ERROR ,
245 p_run_log_level => l_log_level
246 );
247 END IF;
248 end if;
249
250 x_return_status := FND_API.G_RET_STS_SUCCESS;
251
252 EXCEPTION
253 WHEN NO_DATA_FOUND THEN
254 x_return_status := G_RET_ERROR;
255
256 WHEN FND_API.G_EXC_ERROR THEN
257 x_return_status := G_RET_ERROR ;
258
259 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
260 x_return_status := G_RET_UNEXPECTED ;
261 x_msg_data := SUBSTR('WSM_RESERVATIONS_GRP.validate_supply_demand: Unexpected error: '||SQLERRM, 1, 500);
262 WHEN OTHERS THEN
263 /* handle it... */
264 x_return_status := G_RET_UNEXPECTED ;
265 x_msg_data := SUBSTR('WSM_RESERVATIONS_GRP.validate_supply_demand: Unexpected error: '||SQLERRM, 1, 500);
266 END;
267
268 end WSM_RESERVATIONS_GRP;