[Home] [Help]
PACKAGE BODY: APPS.JAI_WSH_EXP_HANDLING_PKG
Source
1 package body jai_wsh_exp_handling_pkg as
2 /* $Header: jai_wsh_exp_handling.plb 120.0.12020000.2 2013/03/01 06:43:55 vkaranam noship $ */
3 --+=======================================================================+
4 --| Copyright (c) 2013 Oracle Corporation
5 --| Redwood Shores, CA, USA
6 --| All rights reserved.
7 --+=======================================================================+
8 --| FILENAME |
9 --| jai_credit_check_pkg.pck |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to handling exceptions on shipping |
13 --| |
14 --| PROCEDURE LIST |
15 --| PROCEDURE process_exp_check |
16 --| |
17 --| HISTORY |
18 --| 05-JAN-2013 zhiwei.xin Modified for code review |
19 --+======================================================================*/
20
21 /*-------------------------------------------------------------------------------------------------------------------------------+
22 | Created By : zhiwei.xin |
23 | Creation Date : 5-JAN-2013 |
24 | Bug Number/ER Name : WSH Exception Handling |
25 | SubProgram Name : process_exp_check |
26 | Type : PROCEDURE |
27 | Purpose : fetch wsh exception details for alert |
28 | TDD Reference : WSH Error Handling TSA.doc |
29 | Assumptions : |
30 | Called From : concurrent program 'India - Check Delivery OM/INV Interface' |
31 |--------------------------------------------------------------------------------------------------------------------------------|
32 | parameters IN/OUT Type Required Description and Purpose |
33 | ------------ -------- ------ ---------- ------------------------- |
34 | errbuf OUT NOCOPY VARCHAR2 no used by CP |
35 | retcode OUT NOCOPY VARCHAR2 no used by CP |
36 | p_delivery_id IN NUMBER yes delivery id on shipping |
37 ---------------------------------------------------------------------------------------------------------------------------------*/
38 PROCEDURE process_exp_check(
39 errbuf OUT NOCOPY VARCHAR2
40 , retcode OUT NOCOPY VARCHAR2
41 , p_delivery_id NUMBER DEFAULT NULL
42 )
43 IS
44
45 cursor c_get_exp_details is
46 select *
47 from JAI_WSH_EXCEPTIONS_T
48 where delivery_id = p_delivery_id
49 order by exception_entity;
50
51 ln_count NUMBER := 0;
52 lb_completion_status BOOLEAN;
53
54 BEGIN
55
56 fnd_file.put_line(FND_FILE.LOG,'Shipping status check started.');
57 fnd_file.put_line(FND_FILE.LOG,'Fetching exception details for delivery : ' || p_delivery_id);
58
59 for rec_exp in c_get_exp_details
60 loop
61 fnd_file.put_line(FND_FILE.OUTPUT,'Delivery Id :' || rec_exp.DELIVERY_ID ||
62 ', Exception Entity :' || rec_exp.EXCEPTION_ENTITY ||
63 ', Error Message : ' || rec_exp.ERROR_MESSAGE);
64 ln_count := ln_count + 1;
65
66 end loop;
67
68 fnd_file.put_line(FND_FILE.LOG,'Shipping status check ended.');
69
70 if (ln_count > 0) then
71 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', ln_count || ' errors are outputed.' || 'Please refer to output file for details.');
72 else
73 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', 'No error is outputed.');
74 end if;
75
76 END process_exp_check;
77
78 /*-------------------------------------------------------------------------------------------------------------------------------+
79 | Created By : zhiwei.xin |
80 | Creation Date : 5-JAN-2013 |
81 | Bug Number/ER Name : WSH Exception Handling |
82 | SubProgram Name : process_interface |
83 | Type : PROCEDURE |
84 | Purpose : process failed subscription manually |
85 | TDD Reference : WSH Error Handling TSA.doc |
86 | Assumptions : |
87 | Called From : concurrent program 'India - Process Delivery OM/INV Interface' |
88 |--------------------------------------------------------------------------------------------------------------------------------|
89 | parameters IN/OUT Type Required Description and Purpose |
90 | ------------ -------- ------ ---------- ------------------------- |
91 | errbuf OUT NOCOPY VARCHAR2 no used by CP |
92 | retcode OUT NOCOPY VARCHAR2 no used by CP |
93 | p_delivery_id IN NUMBER yes delivery id on shipping |
94 ---------------------------------------------------------------------------------------------------------------------------------*/
95 PROCEDURE process_interface(
96 errbuf OUT NOCOPY VARCHAR2
97 , retcode OUT NOCOPY VARCHAR2
98 , p_delivery_id NUMBER DEFAULT NULL
99 )
100 IS
101
102 le_sub EXCEPTION;
103 le_val EXCEPTION;
104 lt_exp_msg jai_om_wdd_processing_pkg.exp_tbl_type := jai_om_wdd_processing_pkg.exp_tbl_type();
105 rec_exp_msg JAI_WSH_EXCEPTIONS_T%ROWTYPE;
106 ln_req_id NUMBER;
107 lv_error_exist VARCHAR2(1);
108 lb_completion_status BOOLEAN;
109 lv_return_code VARCHAR2(240);
110 lv_return_msg VARCHAR2(240);
111 lv_delivery_id NUMBER;
112 lv_exp_entity VARCHAR2(30);
113
114 cursor c_get_delivery_details(p_delivery_id wsh_delivery_assignments.delivery_id%type)
115 is
116 SELECT *
117 FROM wsh_delivery_details
118 WHERE delivery_detail_id IN
119 ( SELECT
120 delivery_detail_id
121 FROM
122 wsh_delivery_assignments
123 WHERE
124 delivery_id = p_delivery_id
125 );
126
127 cursor c_om_interface_exist is
128 select 'Y'
129 from JAI_WSH_EXCEPTIONS_T
130 where delivery_id = p_delivery_id and
131 exception_entity = 'OM_INTERFACE';
132
133 cursor c_inv_interface_exist is
134 select 'Y'
135 from JAI_WSH_EXCEPTIONS_T
136 where delivery_id = p_delivery_id and
137 exception_entity = 'INVENTORY_INTERFACE';
138
139 BEGIN
140 lv_delivery_id := p_delivery_id;
141 fnd_file.put_line(FND_FILE.LOG,'Delivery Interface Processing started. Delivery Id : ' || lv_delivery_id);
142
143 open c_om_interface_exist;
144 fetch c_om_interface_exist into lv_error_exist;
145 close c_om_interface_exist;
146
147 if nvl(lv_error_exist, 'N') = 'Y' then
148 fnd_file.put_line(FND_FILE.LOG,'Process OM Interface. Delivery Id : ' || lv_delivery_id);
149
150 lv_exp_entity := 'OM_INTERFACE';
151
152 -- clear GT table
153 JAI_OM_WDD_PROCESSING_PKG.CLEAR_EXP_MSG(p_delivery_id => lv_delivery_id,
154 p_exp_entity => lv_exp_entity);
155
156 FOR rec_wdd IN c_get_delivery_details (lv_delivery_id)
157 LOOP
158 -- process validation
159 JAI_OM_WDD_PROCESSING_PKG.PROCESS_VALIDATION (p_delivery_id => lv_delivery_id,
160 p_rec_new => rec_wdd,
161 px_return_code => lv_return_code,
162 px_exp_msg => lt_exp_msg);
163 fnd_file.put_line(FND_FILE.LOG,
164 'Validation processing returned with ' || lv_return_code);
165
166 IF lv_return_code <> jai_constants.successful then
167 raise le_val;
168 END IF;
169
170 -- process Delivery OM Interface
171 JAI_OM_WDD_PROCESSING_PKG.PROCESS_INTERFACED (pr_old => null,
172 pr_new => rec_wdd,
173 pv_action => jai_constants.updating,
174 pv_return_code => lv_return_code,
175 pv_return_message => lv_return_msg);
176
177 fnd_file.put_line(FND_FILE.LOG,
178 'Interfaced processing returned with ' || lv_return_code || lv_return_msg);
179
180 IF lv_return_code <> jai_constants.successful then
181 raise le_sub;
182 END IF;
183
184 /* accounting entries for RG23D reversal */
185 JAI_OM_WDD_PROCESSING_PKG.RG23D_REV_ACCOUNTING(pr_trig_row => rec_wdd ,
186 pv_return_code => lv_return_code ,
187 pv_return_message => lv_return_msg);
188
189
190 fnd_file.put_line(FND_FILE.LOG,
191 'accounting entries for RG23D reversal returned with ' || lv_return_code || lv_return_msg);
192
193
194 IF lv_return_code <> jai_constants.successful then
195 raise le_sub;
196 END IF;
197
198 END LOOP;
199
200 commit;
201
202 end if;
203
204 open c_inv_interface_exist;
205 fetch c_inv_interface_exist into lv_error_exist;
206 close c_inv_interface_exist;
207
208 if nvl(lv_error_exist, 'N') = 'Y' then
209 fnd_file.put_line(FND_FILE.LOG,'Process Inventory Interface.');
210 lv_exp_entity := 'INVENTORY_INTERFACE';
211
212 -- clear GT table
213 JAI_OM_WDD_PROCESSING_PKG.CLEAR_EXP_MSG(p_delivery_id => lv_delivery_id,
214 p_exp_entity => lv_exp_entity);
215
216 fnd_file.put_line(FND_FILE.LOG,
217 'Process Inventory Interface. Delivery Id : ' || lv_delivery_id);
218
219 -- process Delivery Inventory Interface
220 JAI_OM_WDD_PROCESSING_PKG.process_inventory(pv_return_code => lv_return_code,
221 pv_return_msg => lv_return_msg,
222 p_delivery_id => lv_delivery_id);
223
224 if lv_return_code <> '0' then
225 raise le_sub;
226 end if;
227
228 fnd_file.put_line(FND_FILE.LOG,
229 'Commit the changes.');
230 commit;
231
232 end if;
233
234 ln_req_id := FND_REQUEST.SUBMIT_REQUEST('JA',
235 'JAICDOII',
236 '',
237 '',
238 FALSE,
239 lv_delivery_id);
240
241 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', null);
242
243 EXCEPTION
244 WHEN LE_VAL THEN
245 fnd_file.put_line(FND_FILE.LOG,
246 'Validation failed.');
247 JAI_OM_WDD_PROCESSING_PKG.POPULATE_EXP_MSG(p_delivery_id => lv_delivery_id,
248 p_exp_msg => lt_exp_msg);
249 ln_req_id := FND_REQUEST.SUBMIT_REQUEST('JA',
250 'JAICDOII',
251 '',
252 '',
253 FALSE,
254 lv_delivery_id);
255
256 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', 'Validation failed. Please check error details.');
257 WHEN LE_SUB THEN
258 fnd_file.put_line(FND_FILE.LOG,
259 'Rollback the changes.');
260 rollback;
261
262 rec_exp_msg.EXCEPTION_TYPE := 'U';
263 rec_exp_msg.EXCEPTION_ENTITY := lv_exp_entity;
264 rec_exp_msg.ERROR_MESSAGE := lv_return_msg;
265
266 lt_exp_msg.EXTEND;
267 lt_exp_msg(lt_exp_msg.LAST) := rec_exp_msg;
268
269 JAI_OM_WDD_PROCESSING_PKG.POPULATE_EXP_MSG(p_delivery_id => lv_delivery_id,
270 p_exp_msg => lt_exp_msg);
271
272 ln_req_id := FND_REQUEST.SUBMIT_REQUEST('JA',
273 'JAICDOII',
274 '',
275 '',
276 FALSE,
277 lv_delivery_id);
278
279 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', 'Processing failed. Please check error details.');
280
281 WHEN OTHERS THEN
282 fnd_file.put_line(FND_FILE.LOG,
283 'Rollback the changes.');
284 rollback;
285
286 rec_exp_msg.EXCEPTION_TYPE := 'E';
287 rec_exp_msg.EXCEPTION_ENTITY := lv_exp_entity;
288 rec_exp_msg.ERROR_MESSAGE := lv_return_msg;
289
290 lt_exp_msg.EXTEND;
291 lt_exp_msg(lt_exp_msg.LAST) := rec_exp_msg;
292
293 JAI_OM_WDD_PROCESSING_PKG.POPULATE_EXP_MSG(p_delivery_id => lv_delivery_id,
294 p_exp_msg => lt_exp_msg);
295
296 ln_req_id := FND_REQUEST.SUBMIT_REQUEST('JA',
297 'JAICDOII',
298 '',
299 '',
300 FALSE,
301 lv_delivery_id);
302
303 lb_completion_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', 'Processing failed. Please check error details.');
304
305 END process_interface;
306
307 end jai_wsh_exp_handling_pkg;