[Home] [Help]
PACKAGE BODY: APPS.M4U_GET_CIN_EXTN
Source
1 PACKAGE BODY m4u_get_cin_extn AS
2 /* $Header: M4UCINXB.pls 120.0 2006/05/25 12:45:09 bsaratna noship $ */
3
4 g_debug_level NUMBER;
5 g_success_code VARCHAR2(30);
6 g_error_code VARCHAR2(30);
7 g_unexp_err_code VARCHAR2(30);
8 g_err VARCHAR2(4000);
9
10 -- Mapping to decode ego_uccnet_event.industry column
11 -- F - FMCG
12 -- H - HARDLINES
13 -- S - SBDH
14 -- This needs to be extended to support other industries.
15 FUNCTION get_indstry_extn_nam
16 (
17 a_indstry_code IN VARCHAR2
18 ) RETURN VARCHAR2
19 AS
20 x_indstry_name VARCHAR2(30);
21 l_indstry_code VARCHAR2(30);
22 BEGIN
23 l_indstry_code := UPPER(a_indstry_code);
24
25 IF l_indstry_code = 'F' THEN
26 x_indstry_name := 'FMCG';
27 ELSIF l_indstry_code = 'H' THEN
28 x_indstry_name := 'HARDLINES';
29 ELSIF l_indstry_code = 'S' THEN
30 x_indstry_name := 'SBDH';
31 ELSE
32 x_indstry_name := l_indstry_code;
33 END IF;
34 RETURN x_indstry_name;
35 END get_indstry_extn_nam;
36
37 -- Update CLN history with XML generation framework status
38 -- error message, status, cln_id are inputs
39 -- autonomous transaction since calling ECX activity will rollback
40 -- if the xml generation has errors,
41 -- commit the cln raise
42 -- NOTE: update subscription should be deferrred for this to work
43 PROCEDURE update_cln_history
44 (
45 a_cln_id IN NUMBER,
46 a_sts IN VARCHAR2,
47 a_msg IN VARCHAR2
48 ) AS
49 PRAGMA AUTONOMOUS_TRANSACTION;
50 l_cln_dtl_id NUMBER;
51 l_ret_sts VARCHAR2(30);
52 l_msg_data VARCHAR2(4000);
53 l_dsptn VARCHAR2(30);
54 l_cln_sts VARCHAR2(30);
55 l_cln_pt VARCHAR2(30);
56 l_doc_sts VARCHAR2(30);
57 l_msg_txt VARCHAR2(4000);
58 l_col_type VARCHAR2(100);
59 l_cln_params wf_parameter_list_t;
60 l_key VARCHAR2(100);
61 BEGIN
62 IF g_debug_level <= 2 THEN
63 cln_debug_pub.add('Entering m4u_get_cin_extn.update_cln_history',2);
64 cln_debug_pub.add('a_cln_id - ' || a_cln_id,1);
65 cln_debug_pub.add('a_sts - ' || a_sts,1);
66 cln_debug_pub.add('a_msg - ' || a_msg,1);
67 END IF;
68
69 -- Actually this api is not called for success
70 IF a_sts = g_success_code THEN
71 l_dsptn := 'PENDING';
72 l_cln_sts := 'COMPLETED';
73 l_doc_sts := 'SUCCESS';
74 FND_MESSAGE.SET_NAME('CLN','M4U_CIN_EXTN_SUCCESS');
75 l_msg_txt := FND_MESSAGE.GET;
76 ELSE
77 l_dsptn := 'REJECTED';
78 l_cln_sts := 'ERROR';
79 l_doc_sts := 'ERROR';
80 FND_MESSAGE.SET_NAME('CLN','M4U_CIN_EXTN_FAILURE');
81 FND_MESSAGE.SET_TOKEN('FAILURE_TEXT',a_msg);
82 l_msg_txt := FND_MESSAGE.GET;
83 END IF;
84
85 -- Call CLN history update API
86 l_key := 'M4U_EXTN_' || a_cln_id;
87 IF g_debug_level <= 1 THEN
88 cln_debug_pub.add('Key - ' || l_key,2);
89 END IF;
90
91 l_cln_params := wf_parameter_list_t();
92
93 wf_event.addparametertolist('DOCUMENT_DIRECTION' , 'OUT',l_cln_params);
94 wf_event.addparametertolist('TRADING_PARTNER_TYPE' , m4u_ucc_utils.c_party_type,l_cln_params);
95 wf_event.addparametertolist('TRADING_PARTNER_ID' , m4u_ucc_utils.g_party_id,l_cln_params);
96 wf_event.addparametertolist('TRADING_PARTNER_SITE' , m4u_ucc_utils.g_party_site_id,l_cln_params);
97 wf_event.addparametertolist('COLLABORATION_ID' , a_cln_id,l_cln_params);
98 wf_event.addparametertolist('DISPOSITION' , l_dsptn,l_cln_params);
99 wf_event.addparametertolist('ROSETTANET_CHECK_REQUIRED' , 'FALSE',l_cln_params);
100 wf_event.addparametertolist('DOCUMENT_STATUS' , l_doc_sts,l_cln_params);
101 wf_event.addparametertolist('MESSAGE_TEXT' , l_msg_txt,l_cln_params);
102 wf_event.addparametertolist('APPLICATION_ID' , m4u_ucc_utils.c_resp_appl_id,l_cln_params);
103 wf_event.addparametertolist('COLLABORATION_STATUS' , l_cln_sts,l_cln_params);
104
105 wf_event.raise( p_event_name =>'oracle.apps.cln.ch.collaboration.update',
106 p_event_key =>l_key,
107 p_parameters =>l_cln_params);
108
109 commit;
110
111 IF g_debug_level <= 2 THEN
112 cln_debug_pub.add('Exiting m4u_get_cin_extn.update_cln_history - Normal',2);
113 END IF;
114
115 RETURN;
116 EXCEPTION
117 WHEN OTHERS THEN
118 -- ignore exceptions - log and continue
119 IF g_debug_level <= 6 THEN
120 cln_debug_pub.add('Unexpected error in m4u_get_cin_extn.update_cln_history',6);
121 cln_debug_pub.add(SQLCODE || ':' || substr(SQLERRM,1,255),6);
122 cln_debug_pub.add('Exiting m4u_get_cin_extn.update_cln_history - Exception',6);
123 END IF;
124 END;
125
126 -- Add any more validation here
127 -- Check if all the required parameters are not null
128 -- return error is any mandatory parameter is missing
129 -- return values of cln_id, tp_id, industry_list on success
130 PROCEDURE read_and_validate_inputs
131 ( a_evnt IN WF_EVENT_T,
132 x_cln_id OUT NOCOPY VARCHAR2,
133 x_tp_id OUT NOCOPY VARCHAR2,
134 x_indstry_list OUT NOCOPY VARCHAR2,
135 x_ret_sts OUT NOCOPY VARCHAR2,
136 x_ret_msg OUT NOCOPY VARCHAR2
137 ) AS
138 l_param VARCHAR2(30);
139 l_value VARCHAR2(4000);
140 l_param_list wf_parameter_list_t;
141 BEGIN
142 l_param := NULL;
143
144 IF g_debug_level <= 2 THEN
145 cln_debug_pub.add('Entering m4u_get_cin_extn.read_and_validate_inputs',2);
146 END IF;
147
148
149 l_param_list := a_evnt.getParameterList();
150
151 -- make sure all these mandatory parameters are not null
152 IF l_param_list IS NULL OR l_param_list.count() = 0 THEN
153 l_param := 'Event parameter-list';
154 ELSIF a_evnt.getValueForParameter('INVENTORY_ITEM_ID')IS NULL THEN
155 l_param := 'INVENTORY_ITEM_ID';
156 ELSIF a_evnt.getValueForParameter('ORGANIZATION_ID')IS NULL THEN
157 l_param := 'ORGANIZATION_ID';
158 ELSIF a_evnt.getValueForParameter('PARTY_SITE_ID') IS NULL THEN
159 l_param := 'PARTY_SITE_ID';
160 ELSIF a_evnt.getValueForParameter('CLN_ID') IS NULL THEN
161 l_param := 'CLN_ID';
162 ELSIF a_evnt.getValueForParameter('TP_GLN') IS NULL THEN
163 l_param := 'TP_GLN';
164 END IF;
165
166 -- if any of the mandatory paramters are not null return error
167 IF l_param IS NOT NULL THEN
168 FND_MESSAGE.SET_NAME('CLN','M4U_XFWK_INV_PARAM');
169 FND_MESSAGE.SET_TOKEN('PARAM',l_param);
170 FND_MESSAGE.SET_TOKEN('VALUE',NULL);
171 x_ret_msg := FND_MESSAGE.GET;
172 x_ret_sts := g_error_code;
173 END IF;
174
175 -- returning these value sinces they are used as paramters
176 -- for calling the map generation routine
177 x_indstry_list := a_evnt.getValueForParameter('INDUSTRY');
178 x_cln_id := a_evnt.getValueForParameter('CLN_ID');
179 x_tp_id := a_evnt.getValueForParameter('PARTY_SITE_ID');
180 x_ret_sts := g_success_code;
181 x_ret_msg := NULL;
182
183 IF g_debug_level <= 2 THEN
184 cln_debug_pub.add('Exiting m4u_get_cin_extn.read_and_validate_inputs - Normal',2);
185 END IF;
186 END;
187
188
189 --Support concatednate XML fragments size < 32767
190 --API is called from the XGM and matches ECX signature
191 --input event parameter contain XGM global variables
192 --write XML as output
193 --Raise exection on any errors and stop XML generation
194 --This api is called from m4u_230_cin_out.xgm extensions/extensionsHookTag
195 PROCEDURE get_xml_fragment
196 (
197 a_evnt IN WF_EVENT_T,
198 x_xml OUT NOCOPY VARCHAR2
199 ) AS
200 l_param_list wf_parameter_list_t;
201 l_cln_id NUMBER;
202 l_tp_site_id NUMBER;
203 l_indstry_list VARCHAR2(400);
204 l_indstry VARCHAR2(30);
205 l_idx NUMBER;
206 l_ret_sts VARCHAR2(30);
207 l_ret_msg VARCHAR2(4000);
208 l_xml_frgmt VARCHAR2(32767);
209 l_upd_col BOOLEAN;
210 handled_exception exception;
211 BEGIN
212 IF g_debug_level <= 2 THEN
213 cln_debug_pub.add('Entering m4u_get_cin_extn.get_xml_fragment',2);
214 END IF;
215
216 l_upd_col := false;
217 l_ret_sts := g_success_code;
218 l_ret_msg := NULL;
219 l_xml_frgmt := NULL;
220 l_param_list := a_evnt.getParameterList();
221
222 -- validate inputs, make sure all mandatory parameters are supplied
223 read_and_validate_inputs(a_evnt,l_cln_id,l_tp_site_id,l_indstry_list,l_ret_sts,l_ret_msg);
224
225 IF g_debug_level <= 1 THEN
226 cln_debug_pub.add('l_cln_id - ' || l_cln_id);
227 cln_debug_pub.add('l_tp_site_id - ' || l_tp_site_id);
228 cln_debug_pub.add('l_indstry_list - ' || l_indstry_list);
229 cln_debug_pub.add('l_ret_sts - ' || l_ret_sts);
233 IF l_ret_sts <> g_success_code THEN
230 cln_debug_pub.add('l_ret_msg - ' || l_ret_msg);
231 END IF;
232 -- validation failed, update CLN and raise exception
234 IF g_debug_level <= 1 THEN
235 cln_debug_pub.add('Update_cln_history with failure',1);
236 END IF;
237 update_cln_history(l_cln_id,l_ret_sts,l_ret_msg);
238 RAISE handled_exception;
239 END IF;
240
241 IF trim(l_indstry_list) is null THEN
242 x_xml := NULL;
243 return;
244 END IF;
245
246 l_indstry_list := l_indstry_list || ':';
247
248 l_idx := INSTR(l_indstry_list,':');
249
250 IF g_debug_level <= 1 THEN
251 cln_debug_pub.add('l_idx - ' || l_idx);
252 END IF;
253
254 -- loop through industry list
255 -- industry1:industry2:industry3:
256 WHILE l_idx > 0
257 LOOP
258 l_ret_msg := NULL;
259 l_indstry := SUBSTR(l_indstry_list,1,l_idx-1);
260 l_indstry_list := SUBSTR(l_indstry_list,l_idx+1);
261
262 IF g_debug_level <= 1 THEN
263 cln_debug_pub.add('l_indstry - ' || l_indstry,1);
264 cln_debug_pub.add('l_indstry_list - ' || l_indstry_list,1);
265 END IF;
266
267 l_indstry := get_indstry_extn_nam(l_indstry);
268
269 IF g_debug_level <= 1 THEN
270 cln_debug_pub.add('decoded l_indstry - ' || l_indstry,1);
271 END IF;
272
273 l_ret_sts := g_success_code;
274
275 IF UPPER(l_indstry) NOT IN ('FMCG','HARDLINES') THEN
276 -- make call to extension API
277
278 IF g_debug_level <= 1 THEN
279 cln_debug_pub.add('Calling where collaboration_id := a_cln_id',1);
280 END IF;
281
282 l_upd_col := true;
283 -- for industry code "XYZ" map-name = "M4U_EXTN_XYZ"
284 l_indstry := 'M4U_EXTN_' || l_indstry;
285 l_xml_frgmt := NULL;
286
287 -- generate XML fragement
288 m4u_xml_extn.generate_xml_fragment
289 ( a_extn_name => l_indstry,
290 a_tp_id => l_tp_site_id,
291 a_tp_dflt => true,
292 a_param_lst => l_param_list,
293 a_log_lvl => g_debug_level,
294 a_remove_empty_elmt => true,
295 a_remove_empty_attr => true,
296 x_ret_sts => l_ret_sts,
297 x_ret_msg => l_ret_msg,
298 x_xml => l_xml_frgmt
299 );
300
301
302
303 IF g_debug_level <= 1 THEN
304 cln_debug_pub.add('m4u_xml_extn.generate_xml_fragment Success',1);
305 cln_debug_pub.add('l_ret_sts - ' || l_ret_sts,1);
306 cln_debug_pub.add('l_ret_msf - ' || l_ret_msg,1);
307 cln_debug_pub.add('XML Size - ' || length(l_xml_frgmt),1);
308 cln_debug_pub.add('Out-xml size- ' || length(x_xml),1);
309 END IF;
310
311 -- if sucess append XML and process next industry
312 IF l_ret_sts = g_success_code THEN
313 x_xml := x_xml || l_xml_frgmt;
314 IF g_debug_level <= 1 THEN
315 cln_debug_pub.add('Concat successful ',1);
316 END IF;
317 ELSE
318 -- bail-out
319 IF g_debug_level <= 1 THEN
320 cln_debug_pub.add('Exiting loop',1);
321 END IF;
322 EXIT;
323 END IF;
324 END IF;
325
326 l_idx := INSTR(l_indstry_list,':');
327
328 END LOOP;
329
330
331 -- This is required since above loop can produce multiple segments
332 -- Well-formed XML requires a single root node
333 -- "OracleM4UExtensionFragment" is used in XSLT m4uoutcin.xsl
334 IF l_ret_sts = g_success_code THEN
335 IF g_debug_level <= 1 THEN
336 cln_debug_pub.add('Adding envelope',1);
337 END IF;
338 x_xml := '<OracleM4UExtensionFragment>' || x_xml;
339 x_xml := x_xml || '</OracleM4UExtensionFragment>';
340 ELSE
341 -- update CLN with failure details
342 IF g_debug_level <= 1 THEN
343 cln_debug_pub.add('Update_cln_history with failure',1);
344 END IF;
345 update_cln_history(l_cln_id,l_ret_sts,l_ret_msg);
346 RAISE handled_exception;
347 END IF;
348
349 IF g_debug_level <= 2 THEN
350 cln_debug_pub.add('Exiting m4u_get_cin_extn.get_xml_fragment - Normal',2);
351 END IF;
352 RETURN;
353 EXCEPTION
354 -- all handled errors reach here
355 -- raise exception to stop M4USTD workflow
356 WHEN handled_exception THEN
357 IF g_debug_level <= 6 THEN
358 cln_debug_pub.add('Unexpected error in m4u_get_cin_extn.get_xml_fragment',6);
359 cln_debug_pub.add(g_err,6);
360 cln_debug_pub.add('Exiting m4u_get_cin_extn.get_xml_fragment - Exception',6);
361 END IF;
362 RAISE;
363 -- all unhandled errors reach here
364 -- 1. update cln
365 -- 2. raise exception to stop M4USTD workflow
366 WHEN OTHERS THEN
367 IF g_debug_level <= 6 THEN
368 cln_debug_pub.add('Unexpected error in m4u_get_cin_extn.get_xml_fragment',6);
369 cln_debug_pub.add(SQLCODE || ':' || SQLERRM,6);
370 cln_debug_pub.add('Exiting m4u_get_cin_extn.get_xml_fragment - Exception',6);
371 END IF;
372 update_cln_history(l_cln_id,g_error_code,SQLCODE || SQLERRM);
373 RAISE;
374 END get_xml_fragment;
375
376 BEGIN
377 -- frequently used package variables
378 g_debug_level := NVL(FND_PROFILE.VALUE('CLN_DEBUG_LEVEL'), 5);
379 g_success_code := FND_API.G_RET_STS_SUCCESS;
380 g_error_code := FND_API.G_RET_STS_ERROR;
381 g_unexp_err_code:= FND_API.G_RET_STS_UNEXP_ERROR;
382 END m4u_get_cin_extn;