[Home] [Help]
PACKAGE BODY: APPS.WMS_WP_CUSTOM_APIS_PUB
Source
1 PACKAGE BODY wms_wp_custom_apis_pub AS
2 /* $Header: WMSWPCAB.pls 120.3 2011/04/19 23:07:22 adyeh noship $ */
3
4
5 PROCEDURE print_debug(p_err_msg VARCHAR2) IS
6 BEGIN
7 inv_mobile_helper_functions.tracelog(p_err_msg => p_err_msg,
8 p_module => 'WMS_REPL_CUSTOM_APIS_PUB',
9 p_level => 4);
10 END print_debug;
11
12
13 PROCEDURE create_wave_lines_cust(p_wave_header_id NUMBER,
14 x_api_is_implemented OUT NOCOPY BOOLEAN,
15 x_custom_line_tbl OUT NOCOPY wms_wave_planning_pvt.line_tbl_typ,
16 x_custom_line_action_tbl OUT NOCOPY wms_wave_planning_pvt.action_tbl_typ,
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_msg_count OUT NOCOPY NUMBER,
19 x_msg_data OUT NOCOPY VARCHAR2) IS
20
21 l_api_name CONSTANT VARCHAR2(30) := 'Get_create_wave_lines_cust';
22 l_progress VARCHAR2(10);
23 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
24
25 BEGIN
26 IF (l_debug = 1) THEN
27 print_debug('***Entering create_wave_lines_cust***');
28 END IF;
29
30 -- Set the savepoint
31 SAVEPOINT create_wave_lines_cust_sp;
32 l_progress := '10';
33
34 -- Initialize message list to clear any existing messages
35 fnd_msg_pub.initialize;
36 l_progress := '20';
37
38 -- Initialize API return status to success
39 x_return_status := fnd_api.g_ret_sts_success;
40 l_progress := '30';
41
42 -- If the custom API is not implemented, return a value of FALSE for the output
43 -- variable 'x_api_is_implemented'. When custom logic is implemented, the line below
44 -- should be modified to return a TRUE value instead.
45 x_api_is_implemented := FALSE;
46
47 -- <Insert custom logic here>
48 /* To insert lines into lines table, identify all wave lines need to be inserted and populate it into PLSQL table x_custom_line_tbl.
49 In corresponding record of PLSQL table x_custom_line_action_tbl populate value 'ADD'. For example:
50 x_custom_line_tbl(i).WAVE_HEADER_ID := p_wave_header_id;
51 x_custom_line_tbl(i).WAVE_LINE_ID := WMS_WP_WAVE_LINES_S.NEXTVAL;
52 x_custom_line_tbl(i).WAVE_LINE_SOURCE := 'OE';
53 x_custom_line_tbl(i).WAVE_LINE_STATUS := 'Created';
54 x_custom_line_tbl(i).CREATED_BY := fnd_global.user_id;
55 x_custom_line_tbl(i).CREATION_DATE := sysdate;
56 x_custom_line_tbl(i).LAST_UPDATED_BY := fnd_global.user_id;
57 x_custom_line_tbl(i).LAST_UPDATE_DATE := sysdate;
58 x_custom_line_tbl(i).LAST_UPDATE_LOGIN := fnd_global.login_id;
59 x_custom_line_tbl(i).SOURCE_CODE := l_SOURCE_CODE;
60 x_custom_line_tbl(i).SOURCE_HEADER_ID := l_SOURCE_HEADER_ID;
61 x_custom_line_tbl(i).SOURCE_LINE_ID := l_SOURCE_LINE_ID;
62 x_custom_line_tbl(i).SOURCE_HEADER_NUMBER := l_SOURCE_HEADER_NUMBER;
63 x_custom_line_tbl(i).SOURCE_LINE_NUMBER := l_SOURCE_LINE_NUMBER;
64 x_custom_line_tbl(i).SOURCE_HEADER_TYPE_ID := l_SOURCE_HEADER_TYPE_ID;
65 x_custom_line_tbl(i).SOURCE_DOCUMENT_TYPE_ID := l_SOURCE_DOCUMENT_TYPE_ID;
66 x_custom_line_tbl(i).DELIVERY_DETAIL_ID := l_DELIVERY_DETAIL_ID;
67 x_custom_line_tbl(i).delivery_id := l_delivery_id;
68 x_custom_line_tbl(i).ORGANIZATION_ID := l_ORGANIZATION_ID;
69 x_custom_line_tbl(i).INVENTORY_ITEM_ID := l_INVENTORY_ITEM_ID;
70 x_custom_line_tbl(i).REQUESTED_QUANTITY := l_REQUESTED_QUANTITY;
71 x_custom_line_tbl(i).REQUESTED_QUANTITY_UOM := l_REQUESTED_QUANTITY_UOM;
72 x_custom_line_tbl(i).REQUESTED_QUANTITY2 := l_REQUESTED_QUANTITY2;
73 x_custom_line_tbl(i).REQUESTED_QUANTITY_UOM2 := l_REQUESTED_QUANTITY_UOM2;
74 x_custom_line_tbl(i).DEMAND_SOURCE_HEADER_ID := l_DEMAND_SOURCE_HEADER_ID;
75 x_custom_line_tbl(i).NET_WEIGHT := l_NET_WEIGHT;
76 x_custom_line_tbl(i).VOLUME := l_VOLUME;
77 x_custom_line_tbl(i).NET_VALUE := l_NET_VALUE;
78 x_custom_line_tbl(i).REMOVE_FROM_WAVE_FLAG := null;
79 x_custom_line_tbl(i).ATTRIBUTE_CATEGORY := null;
80 x_custom_line_tbl(i).ATTRIBUTE1 := null;
81 x_custom_line_tbl(i).ATTRIBUTE2 := null;
82 x_custom_line_tbl(i).ATTRIBUTE3 := null;
83 x_custom_line_tbl(i).ATTRIBUTE4 := null;
84 x_custom_line_tbl(i).ATTRIBUTE5 := null;
85 x_custom_line_tbl(i).ATTRIBUTE6 := null;
86 x_custom_line_tbl(i).ATTRIBUTE7 := null;
87 x_custom_line_tbl(i).ATTRIBUTE8 := null;
88 x_custom_line_tbl(i).ATTRIBUTE9 := null;
89 x_custom_line_tbl(i).ATTRIBUTE10 := null;
90 x_custom_line_tbl(i).ATTRIBUTE11 := null;
91 x_custom_line_tbl(i).ATTRIBUTE12 := null;
92 x_custom_line_tbl(i).ATTRIBUTE13 := null;
93 x_custom_line_tbl(i).ATTRIBUTE14 := null;
94 x_custom_line_tbl(i).ATTRIBUTE15 := null;
95
96 x_custom_line_action_tbl(i) := 'ADD';
97
98 To delete lines already added to the lines tables, pupolate PLSQL table x_custom_line_tbl with line's delivery detail id
99 and in corresponding record of PLSQL table x_custom_line_action_tbl populate value 'REMOVE'. For example:
100 x_custom_line_tbl(j).DELIVERY_DETAIL_ID := l_DELIVERY_DETAIL_ID;
101 x_custom_line_action_tbl(j) := 'REMOVE';
102
103 */
104 -- <Custom logic ends here>
105
106 IF (l_debug = 1) THEN
107 print_debug('***Exiting create_wave_lines_cust***');
108 END IF;
109
110 EXCEPTION
111 WHEN FND_API.G_EXC_ERROR THEN
112 ROLLBACK TO create_wave_lines_cust_sp;
113 x_return_status := fnd_api.g_ret_sts_error;
114 fnd_msg_pub.count_and_get(p_count => x_msg_count,
115 p_data => x_msg_data);
116 IF (l_debug = 1) THEN
117 print_debug('Exiting create_wave_lines_cust - Execution error: ' ||
118 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
119 END IF;
120
121 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
122 ROLLBACK TO Get_create_wave_lines_cust_sp;
123 x_return_status := fnd_api.g_ret_sts_unexp_error;
124 fnd_msg_pub.count_and_get(p_count => x_msg_count,
125 p_data => x_msg_data);
126 IF (l_debug = 1) THEN
127 print_debug('Exiting create_wave_lines_cust - Unexpected error: ' ||
128 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
129 END IF;
130
131 WHEN OTHERS THEN
132 ROLLBACK TO create_wave_lines_cust_sp;
133 x_return_status := fnd_api.g_ret_sts_unexp_error;
134 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
135 --fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
136 fnd_msg_pub.add_exc_msg('wms_wp_custom_apis_pub','create_wave_lines_cust');
137 END IF;
138 fnd_msg_pub.count_and_get(p_count => x_msg_count,
139 p_data => x_msg_data);
140 IF (l_debug = 1) THEN
141 print_debug('Exiting create_wave_lines_cust - Others exception: ' ||
142 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
143 END IF;
144 END create_wave_lines_cust;
145
146
147
148 PROCEDURE Get_wave_exceptions_cust(x_api_is_implemented OUT NOCOPY BOOLEAN,
149 p_exception_name IN VARCHAR2,
150 p_organization_id IN NUMBER,
151 p_wave IN NUMBER,
152 p_exception_entity IN VARCHAR2,
153 p_progress_stage IN VARCHAR2,
154 p_completion_threshold IN NUMBER,
155 p_high_sev_exception_threshold IN NUMBER,
156 p_low_sev_exception_threshold IN NUMBER,
157 p_take_corrective_measures IN VARCHAR2,
158 p_release_back_ordered_lines IN VARCHAR2,
159 p_action_name IN VARCHAR2,
160 x_return_status OUT NOCOPY VARCHAR2,
161 x_msg_count OUT NOCOPY NUMBER,
162 x_msg_data OUT NOCOPY VARCHAR2) IS
163
164 l_exception_id NUMBER;
165 l_progress VARCHAR2(10);
166 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
167
168 BEGIN
169 IF (l_debug = 1) THEN
170 print_debug('***Entering Get_wave_exceptions_cust***');
171 END IF;
172
173 -- Set the savepoint
174 SAVEPOINT Get_wave_exceptions_cust_sp;
175 l_progress := '10';
176
177 -- Initialize message list to clear any existing messages
178 fnd_msg_pub.initialize;
179 l_progress := '20';
180
181 -- Initialize API return status to success
182 x_return_status := fnd_api.g_ret_sts_success;
183 l_progress := '30';
184
185 -- If the custom API is not implemented, return a value of FALSE for the output
186 -- variable 'x_api_is_implemented'. When custom logic is implemented, the line below
187 -- should be modified to return a TRUE value instead.
188 x_api_is_implemented := FALSE;
189
190 -- <Insert custom logic here>
191 /* Determine if particular entity should raise an exception.
192 Either upadte table wms_wp_wave_exceptions if exception for a particular entity is already there or insert new exception record. For example:
193 INSERT INTO wms_wp_wave_exceptions
194 (exception_id,
195 exception_name,
196 exception_entity,
197 exception_stage,
198 exception_level,
199 exception_msg,
200 wave_header_id,
201 trip_id,
202 delivery_id,
203 order_number,
204 order_line_id,
205 status,
206 concurrent_request_id,
207 program_id,
208 created_by,
209 creation_date,
210 last_update_date,
211 last_updated_by,
212 last_update_login)
213 VALUES
214 (wms_WP_WAVE_exceptions_s.nextval,
215 p_exception_name,
216 p_exception_entity,
217 p_progress_stage,
218 p_exception_level,
219 l_msg,
220 p_wave_id,
221 p_trip_id,
222 p_delivery_id,
223 p_order_number,
224 p_order_line_id,
225 'Active',
226 fnd_global.conc_request_id,
227 fnd_global.conc_program_id,
228 fnd_global.user_id,
229 sysdate,
230 sysdate,
231 fnd_global.user_id,
232 fnd_global.conc_login_id);
233 */
234
235 -- For each entity in a wave for which an exception is updated or new exception is logged
236 -- call procedure wms_wave_planning_pvt.take_corrective_measures to take any correction action
237 /*
238 IF (l_debug = 1) THEN
239 print_debug('take_corrective_measure is ' || p_take_corrective_measures, l_debug);
240 end if;
241
242 IF (p_take_corrective_measures = 'Yes') THEN
243 -- call api to take corrective measures
244 wms_wave_planning_pvt.take_corrective_measures(l_exception_id,
245 p_wave,
246 p_entity,
247 p_entity_value,
248 p_release_back_ordered_lines,
249 p_action_name,
250 p_organization_id);
251
252 */
253 -- <Custom logic ends here>
254
255 IF (l_debug = 1) THEN
256 print_debug('***Exiting Get_wave_exceptions_cust***');
257 END IF;
258
259 EXCEPTION
260 WHEN FND_API.G_EXC_ERROR THEN
261 ROLLBACK TO Get_wave_exceptions_cust_sp;
262 x_return_status := fnd_api.g_ret_sts_error;
263 fnd_msg_pub.count_and_get(p_count => x_msg_count,
264 p_data => x_msg_data);
265 IF (l_debug = 1) THEN
266 print_debug('Exiting Get_wave_exceptions_cust - Execution error: ' ||
267 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
268 END IF;
269
270 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
271 ROLLBACK TO Get_wave_exceptions_cust_sp;
272 x_return_status := fnd_api.g_ret_sts_unexp_error;
273 fnd_msg_pub.count_and_get(p_count => x_msg_count,
274 p_data => x_msg_data);
275 IF (l_debug = 1) THEN
276 print_debug('Exiting Get_wave_exceptions_cust - Unexpected error: ' ||
277 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
278 END IF;
279
280 WHEN OTHERS THEN
281 ROLLBACK TO Get_wave_exceptions_cust_sp;
282 x_return_status := fnd_api.g_ret_sts_unexp_error;
283 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
284 --fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
285 fnd_msg_pub.add_exc_msg('wms_wp_custom_apis_pub','Get_wave_exceptions_cust');
286 END IF;
287 fnd_msg_pub.count_and_get(p_count => x_msg_count,
288 p_data => x_msg_data);
289 IF (l_debug = 1) THEN
290 print_debug('Exiting Get_wave_exceptions_cust - Others exception: ' ||
291 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
292 END IF;
293
294 END Get_wave_exceptions_cust;
295
296
297 PROCEDURE task_release_cust(p_organization_id IN NUMBER,
298 p_custom_plan_tolerance IN NUMBER,
299 p_final_mmtt_table IN OUT nocopy wms_wave_planning_pvt.num_t, --11870443
300 x_return_status OUT NOCOPY VARCHAR2,
301 x_msg_count OUT NOCOPY NUMBER,
302 x_msg_data OUT NOCOPY VARCHAR2
303 ) IS
304
305 l_progress VARCHAR2(10);
306 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
307
308 BEGIN
309 IF (l_debug = 1) THEN
310 print_debug('***Entering task_release_cust***');
311 END IF;
312
313 -- Set the savepoint
314 SAVEPOINT task_release_cust_sp;
315 l_progress := '10';
316
317 -- Initialize message list to clear any existing messages
318 fnd_msg_pub.initialize;
319 l_progress := '20';
320
321 -- Initialize API return status to success
322 x_return_status := fnd_api.g_ret_sts_success;
323 l_progress := '30';
324
325 -- If the custom API is not implemented, return a value of FALSE for the output
326 -- variable 'x_api_is_implemented'. When custom logic is implemented, the line below
327 -- should be modified to return a TRUE value instead.
328 -- x_api_is_implemented := FALSE;
329
330 -- p_custom_plan_tolerance is tolerance value given in task release set up form
331 -- p_final_mmtt_table stores transaction_temp_id of all tasks, in any status, fetched from the saved query criteria given in the concurrent program
332
333 -- <Insert custom logic here>
334 /* Using custom logic identify taks which needs to be released and update the pl/sql table with the mmtt's that needs to be released
335 and pass it back to the task release concurrent program
336 */
337
338 -- <Custom logic ends here>
339
340 IF (l_debug = 1) THEN
341 print_debug('***Exiting task_release_cust***');
342 END IF;
343
344 EXCEPTION
345 WHEN FND_API.G_EXC_ERROR THEN
346 ROLLBACK TO task_release_cust_sp;
347 x_return_status := fnd_api.g_ret_sts_error;
348 fnd_msg_pub.count_and_get(p_count => x_msg_count,
349 p_data => x_msg_data);
350 IF (l_debug = 1) THEN
351 print_debug('Exiting task_release_cust - Execution error: ' ||
352 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
353 END IF;
354
355 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
356 ROLLBACK TO task_release_cust_ap;
357 x_return_status := fnd_api.g_ret_sts_unexp_error;
358 fnd_msg_pub.count_and_get(p_count => x_msg_count,
359 p_data => x_msg_data);
360 IF (l_debug = 1) THEN
361 print_debug('Exiting task_release_cust - Unexpected error: ' ||
362 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
363 END IF;
364
365 WHEN OTHERS THEN
366 ROLLBACK TO task_release_cust_sp;
367 x_return_status := fnd_api.g_ret_sts_unexp_error;
368 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
369 --fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
370 fnd_msg_pub.add_exc_msg('wms_wp_custom_apis_pub','task_release_cust');
371 END IF;
372 fnd_msg_pub.count_and_get(p_count => x_msg_count,
373 p_data => x_msg_data);
374 IF (l_debug = 1) THEN
375 print_debug('Exiting task_release_cust - Others exception: ' ||
376 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
377 END IF;
378
379 END task_release_cust;
380
381 END wms_wp_custom_apis_pub;
382