[Home] [Help]
PACKAGE BODY: APPS.M4U_DMD_REQUESTS
Source
1 PACKAGE BODY m4u_dmd_requests AS
2 /* $Header: M4UDREQB.pls 120.3 2007/09/12 06:57:29 bsaratna noship $ */
3
4 -- Table handler for m4u_dmd_messages
5 -- create request record based on input paramters
6 -- returns created message_id, api return status
7 PROCEDURE create_request
8 (
9 p_type IN VARCHAR2,
10 p_direction IN VARCHAR2,
11 p_status IN VARCHAR2 := NULL,
12 p_ref_msg_id IN VARCHAR2 := NULL,
13 p_orig_msg_id IN VARCHAR2 := NULL,
14 p_msg_timstamp IN DATE := NULL,
15 p_payload_id IN VARCHAR2 := NULL,
16 p_sender_gln IN VARCHAR2 := NULL,
17 p_receiver_gln IN VARCHAR2 := NULL,
18 p_rep_party_gln IN VARCHAR2 := NULL,
19 p_user_gln IN VARCHAR2 := NULL,
20 p_user_id IN VARCHAR2 := NULL,
21 x_msg_id OUT NOCOPY VARCHAR2,
22 x_ret_sts OUT NOCOPY VARCHAR2,
23 x_ret_msg OUT NOCOPY VARCHAR2
24 )
25 IS
26 l_msg_id VARCHAR2(30);
27 l_err_api VARCHAR2(50) := 'm4u_dmd_requests.create_request';
28 l_err_msg VARCHAR2(2000);
29 l_param VARCHAR2(100);
30 l_value VARCHAR2(4000);
31 l_orig_msg_id VARCHAR2(80);
32 BEGIN
33
34 m4u_dmd_utils.log('Entering m4u_dmd_request.create_request' ,2);
35 m4u_dmd_utils.log('p_type -|'|| p_type ||'|',1);
36 m4u_dmd_utils.log('p_direction -|'|| p_direction ||'|',1);
37 m4u_dmd_utils.log('p_status -|'|| p_status ||'|',1);
38 m4u_dmd_utils.log('p_ref_msg_id -|'|| p_ref_msg_id ||'|',1);
39 m4u_dmd_utils.log('p_orig_msg_id -|'|| p_orig_msg_id ||'|',1);
40 m4u_dmd_utils.log('p_msg_timstamp -|'|| p_msg_timstamp ||'|',1);
41 m4u_dmd_utils.log('p_payload_id -|'|| p_payload_id ||'|',1);
42
43 m4u_dmd_utils.log('p_sender_gln -|'|| p_sender_gln ||'|',1);
44 m4u_dmd_utils.log('p_receiver_gln -|'|| p_receiver_gln ||'|',1);
45 m4u_dmd_utils.log('p_rep_party_gln -|'|| p_rep_party_gln ||'|',1);
46 m4u_dmd_utils.log('p_user_gln -|'|| p_user_gln ||'|',1);
47 m4u_dmd_utils.log('p_user_id -|'|| p_user_id ||'|',1);
48
49
50
51
52 --validation block begins
53 BEGIN
54 m4u_dmd_utils.log('Entering validation block',2);
55 l_param := '';
56 l_value := '';
57
58 IF NOT m4u_dmd_utils.valid_type('MSG_TYPE',p_type,false) THEN
59 l_param := 'MSG_TYPE';
60 l_value := p_type;
61 ELSIF NOT m4u_dmd_utils.valid_type('DIRECTION',p_direction,false) THEN
62 l_param := 'DIRECTION';
63 l_value := p_direction;
64 ELSIF NOT m4u_dmd_utils.valid_type('MSG_STATUS',p_status,false) THEN
65 l_param := 'MSG_STATUS';
66 l_value := p_status;
67 ELSIF NOT m4u_dmd_utils.valid_msg_id(p_ref_msg_id,true) THEN
68 l_param := 'REF_MSG_ID';
69 l_value := p_ref_msg_id;
70 ELSIF NOT m4u_dmd_utils.valid_len(p_orig_msg_id,1,80,true) THEN
71 l_param := 'ORIG_MSG_ID';
72 l_value := p_orig_msg_id;
73 ELSIF NOT m4u_dmd_utils.valid_payload_id(p_payload_id,true) THEN
74 l_param := 'PAYLOAD_ID';
75 l_value := p_payload_id;
76
77 ELSIF NOT m4u_dmd_utils.valid_gln(p_sender_gln,false) THEN
78 l_param := 'SENDER_GLN';
79 l_value := p_sender_gln;
80 ELSIF NOT m4u_dmd_utils.valid_gln(p_receiver_gln,false) THEN
81 l_param := 'RECEIVER_GLN';
82 l_value := p_receiver_gln;
83 ELSIF NOT m4u_dmd_utils.valid_gln(p_rep_party_gln,true) THEN
84 l_param := 'REP_PARTY_GLN';
85 l_value := p_rep_party_gln;
86 ELSIF p_direction = m4u_dmd_utils.c_dir_out
87 AND NOT m4u_dmd_utils.valid_gln(p_user_gln,false) THEN
88 l_param := 'USER_GLN';
89 l_value := p_user_gln;
90 ELSIF p_direction = m4u_dmd_utils.c_dir_out
91 AND NOT m4u_dmd_utils.valid_len(p_user_id,1,30,false) THEN
92 l_param := 'USER_ID';
93 l_value := p_user_id;
94 END IF;
95
96 IF l_param IS NOT NULL THEN
97 l_err_msg := m4u_dmd_utils.get_inv_param_msg
98 (l_err_api,l_param,l_value);
99 RAISE fnd_api.g_exc_error;
100 END IF;
101 m4u_dmd_utils.log('Exiting validation block - success',2);
102 END;
103 --validation block ends
104
105
106 SELECT m4u_dmd_msgid_s.nextval
107 INTO l_msg_id
108 FROM DUAL;
109
110
111 m4u_dmd_utils.log('l_msg_id -|'|| l_msg_id ||'|',1);
112 m4u_dmd_utils.log('insert into m4u_dmd_messages' ,1);
113
114
115
116 IF p_orig_msg_id = 'GENERATE' THEN
117 --<type>.<date>.<retrycount>.<msgid>
118 l_orig_msg_id := p_type || '.' || to_char(sysdate,'DD-MM-RR') || '.' || '0' || '.' || l_msg_id;
119 ELSE
120 l_orig_msg_id := p_orig_msg_id;
121 END IF;
122
123 INSERT INTO m4u_dmd_messages
124 (
125 msg_id, ref_msg_id, type, direction, status, retry_count,
126 orig_msg_id, sender_gln, receiver_gln, rep_party_gln,
127 user_gln,user_id, msg_timestamp, payload_id,
128 last_update_date, last_updated_by,creation_date, created_by,last_update_login
129 )
130 VALUES
131 (
132 l_msg_id, p_ref_msg_id,p_type, p_direction, p_status, 0,
133 l_orig_msg_id, p_sender_gln, p_receiver_gln, p_rep_party_gln,
134 p_user_gln,p_user_id, p_msg_timstamp, p_payload_id,
135 sysdate, FND_GLOBAL.user_id,sysdate, FND_GLOBAL.user_id, FND_GLOBAL.login_id
136 );
137
138
139 m4u_dmd_utils.log('Exiting m4u_dmd_request.create_request - Success',2);
140
141 x_ret_sts := fnd_api.g_ret_sts_success;
142 x_ret_msg := '';
143 x_msg_id := l_msg_id;
144
145 RETURN;
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 x_msg_id := -1;
150 m4u_dmd_utils.handle_error(l_err_api,l_err_msg,
151 SQLCODE,SQLERRM,x_ret_sts,x_ret_msg);
152 END create_request;
153
154
155 -- Table handler for m4u_dmd_payloads
156 -- store payload record passed as input clob
157 -- returns payload id identify record
158 PROCEDURE create_payload
159 (
160 p_xml IN CLOB,
161 p_type IN VARCHAR2,
162 p_dir IN VARCHAR2,
163 x_payload_id OUT NOCOPY VARCHAR2,
164 x_ret_sts OUT NOCOPY VARCHAR2,
165 x_ret_msg OUT NOCOPY VARCHAR2
166 )
167 IS
168 l_payload_id NUMBER;
169 l_payload_evt_key VARCHAR2(100);
170 l_params wf_parameter_list_t;
171 l_err_api VARCHAR2(50) := 'm4u_dmd_requests.create_payload';
172 l_err_msg VARCHAR2(2000);
173 l_param VARCHAR2(100);
174 l_value VARCHAR2(4000);
175
176 BEGIN
177 m4u_dmd_utils.log('Entering m4u_dmd_request.create_payload',2);
178 m4u_dmd_utils.log('p_type -|'|| p_type ||'|',1);
179 m4u_dmd_utils.log('p_dir -|'|| p_dir ||'|',1);
180 m4u_dmd_utils.log('length(p_xml) -|'|| length(p_xml)||'|',1);
181
182
183 --validation block begins
184 BEGIN
185 m4u_dmd_utils.log('Entering validation block',2);
186 l_param := '';
187 l_value := '';
188
189 IF NOT m4u_dmd_utils.valid_type('PAYLOAD_TYPE',p_type,false) THEN
190 l_param := 'PAYLOAD_TYPE';
191 l_value := p_type;
192 ELSIF NOT m4u_dmd_utils.valid_type('DIRECTION',p_dir,false) THEN
193 l_param := 'DIRECTION';
194 l_value := p_dir;
195 END IF;
196
197 IF l_param IS NOT NULL THEN
198 l_err_msg := m4u_dmd_utils.get_inv_param_msg
199 (l_err_api,l_param,l_value);
200 RAISE fnd_api.g_exc_error;
201 END IF;
202 m4u_dmd_utils.log('Exiting validation block - success',2);
203 END;
204 --validation block ends
205
206 SELECT m4u_dmd_payloadid_s.NEXTVAL
207 INTO l_payload_id
208 FROM dual;
209
210 m4u_dmd_utils.log('l_payload_id -|'|| l_payload_id ||'|',1);
211 m4u_dmd_utils.log('insert into m4u_dmd_payloads' ,1);
212
213
214
215 INSERT INTO m4u_dmd_payloads
216 (
217 payload_id, payload, type, direction,
218 last_update_date, last_updated_by,creation_date,created_by, last_update_login
219 )
220 VALUES
221 (
222 l_payload_id, p_xml, p_type,p_dir, sysdate, FND_GLOBAL.user_id,
223 sysdate,FND_GLOBAL.user_id, FND_GLOBAL.login_id
224 );
225
226
227 --Raise CLN event
228 BEGIN
229 --set key
230 l_payload_evt_key := sysdate || '.' || l_payload_id;
231
232 --set params
233 l_params := wf_parameter_list_t();
234 wf_event.addParameterToList('PAYLOAD_TYPE', p_type, l_params);
235 wf_event.addParameterToList('PAYLOAD_DIR', p_dir, l_params);
236 wf_event.addParameterToList('PAYLOAD_ID', l_payload_id, l_params);
237
238 --raise event
239 wf_event.raise
240 (
241 p_event_name => m4u_dmd_utils.c_payload_event,
242 p_event_key => l_payload_evt_key,
243 p_parameters => l_params
244 );
245
246 m4u_dmd_utils.log('l_payload_evt_key - |' || l_payload_evt_key || '|',2);
247 EXCEPTION
248 WHEN OTHERS THEN
249 m4u_dmd_utils.log('m4u_dmd_messages.create_payload', 6);
250 m4u_dmd_utils.log('Unexpected error while raising payload event',6);
251 m4u_dmd_utils.log(SQLCODE || SQLERRM, 6);
252 END;
253
254 m4u_dmd_utils.log('Exiting m4u_dmd_request.create_payload - Success',2);
255 x_ret_sts := fnd_api.g_ret_sts_success;
256 x_ret_msg := '';
257 x_payload_id:= l_payload_id;
258
259 EXCEPTION
260 WHEN OTHERS THEN
261 x_payload_id := -1;
262 m4u_dmd_utils.handle_error(l_err_api,l_err_msg,
263 SQLCODE,SQLERRM,x_ret_sts,x_ret_msg);
264 END create_payload;
265
266
267 -- Table handler for m4u_dmd_payloads
268 -- create document record based on input paramters
272 (
269 -- valid msg_id is a mandatory parameter
270 -- 1-n relationship between requests and documents
271 PROCEDURE create_document
273 p_msg_id IN VARCHAR2,
274 p_type IN VARCHAR2,
275 p_action IN VARCHAR2,
276 p_doc_status IN VARCHAR2,
277 p_func_status IN VARCHAR2,
278 p_timestamp IN DATE := NULL,
279 p_processing_msg IN VARCHAR2 := NULL,
280 p_ref_doc_id IN VARCHAR2 := NULL,
281 p_orig_doc_id IN VARCHAR2 := NULL,
282 p_top_gtin IN VARCHAR2 := NULL,
283 p_info_provider_gln IN VARCHAR2 := NULL,
284 p_data_rcpt_gln IN VARCHAR2 := NULL,
285 p_tgt_mkt_ctry IN VARCHAR2 := NULL,
286 p_tgt_mkt_div IN VARCHAR2 := NULL,
287 p_param1 IN VARCHAR2 := NULL,
288 p_param2 IN VARCHAR2 := NULL,
289 p_param3 IN VARCHAR2 := NULL,
290 p_param4 IN VARCHAR2 := NULL,
291 p_param5 IN VARCHAR2 := NULL,
292 p_lparam1 IN VARCHAR2 := NULL,
293 p_lparam2 IN VARCHAR2 := NULL,
294 p_lparam3 IN VARCHAR2 := NULL,
295 p_lparam4 IN VARCHAR2 := NULL,
296 p_lparam5 IN VARCHAR2 := NULL,
297 p_payload_id IN VARCHAR2 := NULL,
298 p_payload_dir IN VARCHAR2 := NULL,
299 p_payload_type IN VARCHAR2 := NULL,
300 x_doc_id OUT NOCOPY VARCHAR2,
301 x_ret_sts OUT NOCOPY VARCHAR2,
302 x_ret_msg OUT NOCOPY VARCHAR2
303 )
304 IS
305 l_doc_id VARCHAR2(30);
306 l_cln_evt_key VARCHAR2(30);
307 l_cln_evt_params wf_parameter_list_t;
308 l_err_api VARCHAR2(50) := 'm4u_dmd_requests.create_document';
309 l_err_msg VARCHAR2(2000);
310 l_param VARCHAR2(100);
311 l_value VARCHAR2(4000);
312 l_orig_doc_id VARCHAR2(80);
313
314 BEGIN
315 --TBD: code to validate inputs
316
317
318 m4u_dmd_utils.log('Entering m4u_dmd_request.create_document' ,2);
319 m4u_dmd_utils.log('p_msg_id -|'|| p_msg_id ||'|',1);
320 m4u_dmd_utils.log('p_type -|'|| p_type ||'|',1);
321 m4u_dmd_utils.log('p_action -|'|| p_action ||'|',1);
322 m4u_dmd_utils.log('p_doc_status -|'|| p_doc_status ||'|',1);
323 m4u_dmd_utils.log('p_func_status -|'|| p_func_status ||'|',1);
324 m4u_dmd_utils.log('p_processing_msg -|'|| p_processing_msg ||'|',1);
325 m4u_dmd_utils.log('p_ref_doc_id -|'|| p_ref_doc_id ||'|',1);
326 m4u_dmd_utils.log('p_orig_doc_id -|'|| p_orig_doc_id ||'|',1);
327 m4u_dmd_utils.log('p_timestamp -|'|| p_timestamp ||'|',1);
328
329 m4u_dmd_utils.log('p_top_gtin -|'|| p_top_gtin ||'|',1);
330 m4u_dmd_utils.log('p_info_provider_gln-|'|| p_info_provider_gln ||'|',1);
331 m4u_dmd_utils.log('p_data_rcpt_gln -|'|| p_data_rcpt_gln ||'|',1);
332 m4u_dmd_utils.log('p_tgt_mkt_ctry -|'|| p_tgt_mkt_ctry ||'|',1);
333 m4u_dmd_utils.log('p_tgt_mkt_div -|'|| p_tgt_mkt_div ||'|',1);
334
335 m4u_dmd_utils.log('p_param1 -|'|| p_param1 ||'|',1);
336 m4u_dmd_utils.log('p_param2 -|'|| p_param2 ||'|',1);
337 m4u_dmd_utils.log('p_param3 -|'|| p_param3 ||'|',1);
338 m4u_dmd_utils.log('p_param4 -|'|| p_param4 ||'|',1);
339 m4u_dmd_utils.log('p_param5 -|'|| p_param5 ||'|',1);
340 m4u_dmd_utils.log('p_lparam1 -|'|| p_lparam1 ||'|',1);
341 m4u_dmd_utils.log('p_lparam2 -|'|| p_lparam2 ||'|',1);
342 m4u_dmd_utils.log('p_lparam3 -|'|| p_lparam3 ||'|',1);
343 m4u_dmd_utils.log('p_lparam4 -|'|| p_lparam4 ||'|',1);
344 m4u_dmd_utils.log('p_lparam5 -|'|| p_lparam5 ||'|',1);
345
346 m4u_dmd_utils.log('p_payload_id -|'|| p_payload_id ||'|',1);
347 m4u_dmd_utils.log('p_payload_dir -|'|| p_payload_dir ||'|',1);
348 m4u_dmd_utils.log('p_payload_type -|'|| p_payload_type ||'|',1);
349
350 --validation block begins
351 BEGIN
352 l_param := '';
353 l_value := '';
354
355 m4u_dmd_utils.log('Entering validation block',2);
356
357 -- validate id, type, status params
358 IF NOT m4u_dmd_utils.valid_type('DOC_TYPE',p_type,false) THEN
359 l_param := 'DOC_TYPE';
360 l_value := p_type;
361 ELSIF NOT m4u_dmd_utils.valid_type('ACTION',p_action,false) THEN
362 l_param := 'ACTION';
363 l_value := p_action;
364 ELSIF NOT m4u_dmd_utils.valid_type('DOC_STATUS',p_doc_status,false) THEN
365 l_param := 'DOC_STATUS';
366 l_value := p_doc_status;
367 ELSIF NOT m4u_dmd_utils.valid_msg_id(p_msg_id,false) THEN
368 l_param := 'MSG_ID';
369 l_value := p_msg_id;
370 ELSIF NOT m4u_dmd_utils.valid_doc_id(p_ref_doc_id,true) THEN
371 l_param := 'REF_DOC_ID';
372 l_value := p_ref_doc_id;
373 ELSIF NOT m4u_dmd_utils.valid_len(p_orig_doc_id ,0,80,true) THEN
374 l_param := 'ORIG_DOC_ID';
378 l_value := p_processing_msg ;
375 l_value := p_orig_doc_id ;
376 ELSIF NOT m4u_dmd_utils.valid_len(p_processing_msg,0,400,true) THEN
377 l_param := 'PROCESSING_MSG';
379
380 -- validate extensible params
381 ELSIF NOT m4u_dmd_utils.valid_len(p_param1,0,50,true) THEN
382 l_param := 'PARAM1';
383 l_value := p_param1;
384 ELSIF NOT m4u_dmd_utils.valid_len(p_param2,0,50,true) THEN
385 l_param := 'PARAM2';
386 l_value := p_param2;
387 ELSIF NOT m4u_dmd_utils.valid_len(p_param3,0,50,true) THEN
388 l_param := 'PARAM3';
389 l_value := p_param3;
390 ELSIF NOT m4u_dmd_utils.valid_len(p_param4,0,50,true) THEN
391 l_param := 'PARAM4';
392 l_value := p_param4;
393 ELSIF NOT m4u_dmd_utils.valid_len(p_param5,0,50,true) THEN
394 l_param := 'PARAM5';
395 l_value := p_param5;
396 ELSIF NOT m4u_dmd_utils.valid_len(p_lparam1,0,400,true) THEN
397 l_param := 'LPARAM1';
398 l_value := p_lparam1;
399 ELSIF NOT m4u_dmd_utils.valid_len(p_lparam2,0,400,true) THEN
400 l_param := 'LPARAM2';
401 l_value := p_lparam2;
402 ELSIF NOT m4u_dmd_utils.valid_len(p_lparam3,0,400,true) THEN
403 l_param := 'LPARAM3';
404 l_value := p_lparam3;
405 ELSIF NOT m4u_dmd_utils.valid_len(p_lparam4,0,400,true) THEN
406 l_param := 'LPARAM4';
407 l_value := p_lparam4;
408 ELSIF NOT m4u_dmd_utils.valid_len(p_lparam5,0,400,true) THEN
409 l_param := 'LPARAM5';
410 l_value := p_lparam5;
411
412 -- validate doc params
413 ELSIF NOT m4u_dmd_utils.valid_len(p_tgt_mkt_ctry ,0,30,true) THEN
414 l_param := 'TARGET_MARKET_COUNTRY';
415 l_value := p_tgt_mkt_ctry ;
416 ELSIF NOT m4u_dmd_utils.valid_len(p_tgt_mkt_div ,0,30,true) THEN
420 l_param := 'TOP_GTIN';
417 l_param := 'TARGET_MARKET_SUBDIV';
418 l_value := p_tgt_mkt_div ;
419 ELSIF NOT m4u_dmd_utils.valid_gtin(p_top_gtin ,true) THEN
421 l_value := p_top_gtin ;
422 ELSIF NOT m4u_dmd_utils.valid_gln(p_info_provider_gln ,true) THEN
426 l_param := 'DATA_RCPT_GLN';
423 l_param := 'INFO_PROVIDER_GLN';
424 l_value := p_info_provider_gln;
425 ELSIF NOT m4u_dmd_utils.valid_gln(p_data_rcpt_gln ,true) THEN
427 l_value := p_data_rcpt_gln ;
428 -- validate payload params
429 ELSIF NOT m4u_dmd_utils.valid_type('PAYLOAD_TYPE',p_payload_type,true) THEN
430 l_param := 'PAYLOAD_TYPE';
431 l_value := p_payload_type;
432 ELSIF NOT m4u_dmd_utils.valid_type('DIRECTION',p_payload_dir,true) THEN
433 l_param := 'PAYLOAD_DIRECTION';
434 l_value := p_payload_dir;
435 ELSIF NOT m4u_dmd_utils.valid_payload_id(p_payload_id,true) THEN
436 l_param := 'PAYLOAD_ID';
437 l_value := p_payload_id;
438
439 END IF;
440
441 IF l_param IS NOT NULL THEN
442 l_err_msg := m4u_dmd_utils.get_inv_param_msg
443 (l_err_api,l_param,l_value);
444 RAISE fnd_api.g_exc_error;
445 END IF;
446 m4u_dmd_utils.log('Exiting validation block - success',2);
447 END;
448 --validation block ends
449
450
451 SELECT m4u_dmd_docid_s.NEXTVAL
452 INTO l_doc_id
453 FROM dual;
454
455 IF p_orig_doc_id = 'GENERATE' THEN
456 l_orig_doc_id := p_type || '.' || to_char(sysdate,'DD-MM-RR') || '.' || '0' || '.' || l_doc_id;
457 ELSE
458 l_orig_doc_id := p_orig_doc_id;
459 END IF;
460
461 m4u_dmd_utils.log('l_doc_id -|'|| l_doc_id ||'|',1);
462 m4u_dmd_utils.log('insert into m4u_dmd_documents' ,1);
463
464 INSERT INTO m4u_dmd_documents
465 (
466 msg_id,doc_id,type ,action, retry_count,
467 doc_status, functional_status,processing_message,
468 ref_doc_id,orig_doc_id,doc_timestamp,
469 top_gtin,info_provider_gln,data_recepient_gln,
470 target_market_country,target_market_sub_div,
471 parameter1,parameter2,parameter3,parameter4,parameter5,
472 lparameter1,lparameter2,lparameter3,lparameter4,lparameter5,
473 last_update_date,last_updated_by,creation_date, created_by,
474 last_update_login)
475 VALUES
476 ( p_msg_id,l_doc_id,p_type,p_action,0,
477 p_doc_status, p_func_status,p_processing_msg,
478 p_ref_doc_id,l_orig_doc_id,p_timestamp,
479 p_top_gtin,p_info_provider_gln,p_data_rcpt_gln,
480 p_tgt_mkt_ctry,p_tgt_mkt_div,
481 p_param1,p_param2,p_param3,p_param4,p_param5,
482 p_lparam1,p_lparam2,p_lparam3,p_lparam4,p_lparam5,
483 sysdate,FND_GLOBAL.user_id,sysdate, FND_GLOBAL.user_id,
484 FND_GLOBAL.login_id
485 );
486
487
488 --Raise CLN event
489 BEGIN
490 --set key
491 l_cln_evt_key := sysdate || '.' || l_doc_id;
492
493 --set params
494 l_cln_evt_params := wf_parameter_list_t();
495
496 wf_event.addParameterToList('RETRY_COUNT', 0, l_cln_evt_params);
497 wf_event.addParameterToList('DOC_ID', l_doc_id, l_cln_evt_params);
498 wf_event.addParameterToList('REF_DOC_ID', p_ref_doc_id , l_cln_evt_params);
499
500 wf_event.addParameterToList('DOC_TYPE', p_type, l_cln_evt_params);
501 wf_event.addParameterToList('ACTION', p_action, l_cln_evt_params);
502 wf_event.addParameterToList('DOC_STATUS', p_doc_status, l_cln_evt_params);
503 wf_event.addParameterToList('PROCESSING_MSG', p_processing_msg, l_cln_evt_params);
504
505 wf_event.addParameterToList('PAYLOAD_ID', p_payload_id, l_cln_evt_params);
506 wf_event.addParameterToList('PAYLOAD_TYPE', p_payload_type, l_cln_evt_params);
507 wf_event.addParameterToList('PAYLOAD_DIR', p_payload_dir, l_cln_evt_params);
508
509 --raise event
510 wf_event.raise
511 (
512 p_event_name => m4u_dmd_utils.c_cln_event,
513 p_event_key => l_cln_evt_key,
514 p_parameters => l_cln_evt_params
515 );
516 m4u_dmd_utils.log('l_cln_evt_key - |' || l_cln_evt_key || '|',2);
517 EXCEPTION
518 WHEN OTHERS THEN
519 m4u_dmd_utils.log('m4u_dmd_messages.create_document', 6);
520 m4u_dmd_utils.log('Unexpected error while raising payload event',6);
521 m4u_dmd_utils.log(SQLCODE || SQLERRM, 6);
522
523 END;
524
525 m4u_dmd_utils.log('Exiting m4u_dmd_request.create_document - Success',2);
526
527 x_doc_id := l_doc_id;
528 x_ret_sts := fnd_api.g_ret_sts_success;
529 x_ret_msg := '';
530 RETURN;
531 EXCEPTION
532 WHEN OTHERS THEN
533 x_doc_id := -1;
534 m4u_dmd_utils.handle_error(l_err_api,l_err_msg,
535 SQLCODE,SQLERRM,x_ret_sts,x_ret_msg);
536 END create_document;
537
538 PROCEDURE retry_request
539 (
540 x_errbuf OUT NOCOPY VARCHAR2,
541 x_retcode OUT NOCOPY NUMBER,
542 p_msg_id IN VARCHAR2,
546 IS
543 p_mode IN VARCHAR2,
544 p_time IN DATE
545 )
547 l_ret_sts VARCHAR2(30);
548 l_ret_msg VARCHAR2(50);
549 l_err_api VARCHAR2(50) := 'm4u_dmd_requests.rerty_request';
550 l_err_msg VARCHAR2(2000);
551 l_retry_count NUMBER;
552 l_param VARCHAR2(100);
553 l_value VARCHAR2(4000);
554
555 CURSOR docs_for_msg(p_msg_id IN VARCHAR2) IS
556 SELECT *
557 FROM m4u_dmd_documents
558 WHERE msg_id = p_msg_id;
559 BEGIN
560 m4u_dmd_utils.log('Entering m4u_dmd_request.rerty_request',2);
561 m4u_dmd_utils.log('p_msg_id -|'|| p_msg_id ||'|',1);
562 m4u_dmd_utils.log('p_mode -|'|| p_mode ||'|',1);
563 m4u_dmd_utils.log('p_time -|'|| p_time ||'|',1);
564
565
566 --validation block begins
567 BEGIN
568 m4u_dmd_utils.log('Entering validation block',2);
569 l_param := '';
570 l_value := '';
571
572 IF NOT m4u_dmd_utils.valid_msg_id(p_msg_id,false) THEN
573 l_param := 'MSG_ID';
574 l_value := p_msg_id;
575 ELSIF NOT m4u_dmd_utils.valid_type('RETRY_MODE',p_mode,false) THEN
576 l_param := 'RETRY_MODE';
577 l_value := p_mode;
578 ELSIF p_mode = m4u_dmd_utils.c_retry_timeout
579 AND p_time IS NULL THEN
580 l_param := 'FROM_TIME';
581 l_value := p_time;
582 END IF;
583
584 IF l_param IS NOT NULL THEN
585 l_err_msg := m4u_dmd_utils.get_inv_param_msg
586 (l_err_api,l_param,l_value);
587 RAISE fnd_api.g_exc_error;
588 END IF;
589 m4u_dmd_utils.log('Exiting validation block - success',2);
590 END;
591 --validation block ends
592 SELECT retry_count
593 INTO l_retry_count
594 FROM m4u_dmd_messages
595 WHERE msg_id = p_msg_id;
596
597 m4u_dmd_utils.log('Old l_retry_count -|'|| l_retry_count ||'|',1);
598
599 l_retry_count := l_retry_count+1;
600
601 UPDATE m4u_dmd_messages
602 SET status = m4u_dmd_utils.c_sts_ready,
603 retry_count = l_retry_count,
604 orig_msg_id = type || '.' || sysdate || '.' ||
605 l_retry_count || '.' || p_msg_id
606 WHERE msg_id = p_msg_id;
607
608 FOR l_doc_rec IN docs_for_msg(p_msg_id)
609 LOOP
610
611 m4u_dmd_utils.log('Call update_document - ' || l_doc_rec.doc_id,1);
612 l_ret_msg := '';
613 l_ret_sts := '';
614
615 IF (p_mode = m4u_dmd_utils.c_retry_all
616
617 OR (p_mode = m4u_dmd_utils.c_retry_timeout AND
618 (l_doc_rec.doc_status = 'IN_PROCESS'
619 OR l_doc_rec.doc_status = 'READY') AND
620 (l_doc_rec.last_update_date - nvl(p_time,sysdate) < 0))
621
622 OR (p_mode = m4u_dmd_utils.c_retry_err AND
623 l_doc_rec.doc_status = m4u_dmd_utils.c_sts_error)) THEN
624
625
626 m4u_dmd_requests.update_document
627 (
628 p_doc_id => l_doc_rec.doc_id,
629 p_retry_count => l_retry_count,
630 p_orig_doc_id => 'GENERATE',
631 p_doc_status => m4u_dmd_utils.c_sts_ready,
632 p_func_status => null,
633 x_ret_sts => l_ret_sts,
634 x_ret_msg => l_ret_msg
635 );
636
637 m4u_dmd_utils.log('l_ret_sts - |' || l_ret_sts || '|',2);
638 m4u_dmd_utils.log('l_ret_msg - |' || l_ret_msg || '|',1);
639
640 IF l_ret_sts <> fnd_api.g_ret_sts_success THEN
641 l_err_msg := nvl(l_ret_msg,'Unexpected error while updating document');
642 RAISE fnd_api.g_exc_error;
643 END IF;
644 END IF;
645 END LOOP;
646
647 m4u_dmd_utils.log('Exiting m4u_dmd_request.rerty_request - Success',2);
648
649 x_errbuf := '' ;
650 x_retcode := 0 ;
651 RETURN;
652 EXCEPTION
653 WHEN OTHERS THEN
654 m4u_dmd_utils.handle_error(l_err_api,l_err_msg,
655 SQLCODE,SQLERRM,l_ret_sts,l_ret_msg);
656 x_errbuf := l_ret_msg ;
657 x_retcode := 2 ;
658 RETURN;
659 END retry_request;
660
661
662 PROCEDURE update_request
663 (
664 p_msg_id IN VARCHAR2,
665 p_status IN VARCHAR2,
666 p_update_doc_flag IN VARCHAR2,
667 p_retry_count IN NUMBER,
668 p_ref_msg_id IN VARCHAR2 := NULL,
669 p_orig_msg_id IN VARCHAR2 := NULL,
670 p_msg_timstamp IN DATE := NULL,
671
672 p_sender_gln IN VARCHAR2 := NULL,
673 p_receiver_gln IN VARCHAR2 := NULL,
674 p_rep_party_gln IN VARCHAR2 := NULL,
678 p_bpel_instance_id IN VARCHAR2 := NULL,
675 p_user_id IN VARCHAR2 := NULL,
676 p_user_gln IN VARCHAR2 := NULL,
677
679 p_bpel_process_id IN VARCHAR2 := NULL,
680
681 p_doc_type IN VARCHAR2,
682 p_doc_status IN VARCHAR2 := NULL,
683 p_func_status IN VARCHAR2 := NULL,
684 p_processing_msg IN VARCHAR2 := NULL,
685
686 p_payload IN CLOB := NULL,
687 p_payload_dir IN VARCHAR2 := NULL,
688 p_payload_type IN VARCHAR2 := NULL,
689
690 x_ret_sts OUT NOCOPY VARCHAR2,
691 x_ret_msg OUT NOCOPY VARCHAR2
692 )
693 IS
694 CURSOR docs_for_msg(p_msg_id IN VARCHAR2, p_retry_count IN NUMBER) IS
695 SELECT *
696 FROM m4u_dmd_documents
697 WHERE msg_id = p_msg_id
698 AND retry_count = p_retry_count;
699
700 l_payload_id VARCHAR2(30);
701 l_msg_id VARCHAR2(30);
702 l_ret_sts VARCHAR2(5);
703 l_ret_msg VARCHAR2(400);
704 l_err_api VARCHAR2(50) := 'm4u_dmd_requests.update_request';
705 l_err_msg VARCHAR2(2000);
706 l_param VARCHAR2(100);
707 l_value VARCHAR2(4000);
708 l_retry_count NUMBER;
709 BEGIN
710 m4u_dmd_utils.log('Entering m4u_dmd_request.update_request',2);
711 m4u_dmd_utils.log('p_msg_id -|'|| p_msg_id ||'|',1);
712 m4u_dmd_utils.log('p_status -|'|| p_status ||'|',1);
713 m4u_dmd_utils.log('p_retry_count -|'|| p_retry_count ||'|',1);
714 m4u_dmd_utils.log('p_ref_msg_id -|'|| p_ref_msg_id ||'|',1);
715 m4u_dmd_utils.log('p_orig_msg_id -|'|| p_orig_msg_id ||'|',1);
716 m4u_dmd_utils.log('p_msg_timstamp -|'|| p_msg_timstamp ||'|',1);
717
718 m4u_dmd_utils.log('p_sender_gln -|'|| p_sender_gln ||'|',1);
719 m4u_dmd_utils.log('p_receiver_gln -|'|| p_receiver_gln ||'|',1);
720 m4u_dmd_utils.log('p_rep_party_gln -|'|| p_rep_party_gln ||'|',1);
721 m4u_dmd_utils.log('p_user_gln -|'|| p_user_gln ||'|',1);
722 m4u_dmd_utils.log('p_user_id -|'|| p_user_id ||'|',1);
723
724 m4u_dmd_utils.log('p_bpel_inst_id -|'|| p_bpel_instance_id||'|',1);
725 m4u_dmd_utils.log('p_bpel_proc_id -|'|| p_bpel_process_id ||'|',1);
726
727 m4u_dmd_utils.log('p_doc_type -|'|| p_doc_type ||'|',1);
728 m4u_dmd_utils.log('p_doc_status -|'|| p_doc_status ||'|',1);
729 m4u_dmd_utils.log('p_func_status -|'|| p_func_status ||'|',1);
730 m4u_dmd_utils.log('p_processing_msg-|'|| p_processing_msg ||'|',1);
731
732 m4u_dmd_utils.log('p_payload_dir -|'|| p_payload_dir ||'|',1);
733 m4u_dmd_utils.log('p_payload_type -|'|| p_payload_type ||'|',1);
734 m4u_dmd_utils.log('len(p_payload) -|'|| length(p_payload) ||'|',1);
735
736 -- validation block begin
737 BEGIN
738 m4u_dmd_utils.log('Entering validation block',2);
739 l_param := '';
740 l_value := '';
741 IF NOT m4u_dmd_utils.valid_msg_id(p_msg_id,false)
742 AND NOT m4u_dmd_utils.valid_orig_msg_id(p_orig_msg_id,false) THEN
743 l_param := 'MSG_ID/ORIG_MSG_ID';
744 l_value := p_msg_id || '/' || p_orig_msg_id;
745 ELSIF NOT m4u_dmd_utils.valid_type('MSG_STATUS',p_status,false) THEN
746 l_param := 'MSG_STATUS';
747 l_value := p_status;
748 ELSIF NOT m4u_dmd_utils.valid_msg_id(p_ref_msg_id,true) THEN
749 l_param := 'REF_MSG_ID';
750 l_value := p_ref_msg_id;
751 ELSIF NOT m4u_dmd_utils.valid_len(p_orig_msg_id,0,80,true) THEN
752 l_param := 'ORIG_MSG_ID';
753 l_value := p_orig_msg_id;
754
755 ELSIF NOT m4u_dmd_utils.valid_gln(p_sender_gln,true) THEN
756 l_param := 'SENDER_GLN';
757 l_value := p_sender_gln;
758 ELSIF NOT m4u_dmd_utils.valid_gln(p_receiver_gln,true) THEN
759 l_param := 'RECEIVER_GLN';
760 l_value := p_receiver_gln;
761 ELSIF NOT m4u_dmd_utils.valid_gln(p_rep_party_gln,true) THEN
762 l_param := 'REP_PARTY_GLN';
763 l_value := p_rep_party_gln;
764 ELSIF NOT m4u_dmd_utils.valid_gln(p_user_gln,true) THEN
765 l_param := 'USER_GLN';
766 l_value := p_user_gln;
767 ELSIF NOT m4u_dmd_utils.valid_len(p_user_id,0,30,true) THEN
768 l_param := 'USER_ID';
769 l_value := p_user_id;
770
771 ELSIF NOT m4u_dmd_utils.valid_len(p_bpel_instance_id,0,100,true) THEN
772 l_param := 'BPEL_INSTANCE_ID';
773 l_value := p_bpel_instance_id;
774 ELSIF NOT m4u_dmd_utils.valid_len(p_bpel_process_id,0,100,true) THEN
775 l_param := 'BPEL_PROCESS_ID';
776 l_value := p_bpel_process_id;
777
778 ELSIF NOT m4u_dmd_utils.valid_type('DOC_TYPE',p_doc_type,true) THEN
779 l_param := 'DOC_TYPE';
780 l_value := p_doc_type;
781 ELSIF NOT m4u_dmd_utils.valid_type('DOC_STATUS',p_doc_status,true) THEN
782 l_param := 'DOC_STATUS';
783 l_value := p_doc_status;
787
784 ELSIF NOT m4u_dmd_utils.valid_len(p_processing_msg,0,400,true) THEN
785 l_param := 'PROCESSING_MSG';
786 l_value := p_processing_msg ;
788 ELSIF NOT m4u_dmd_utils.valid_type('PAYLOAD_TYPE',p_payload_type,true) THEN
789 l_param := 'PAYLOAD_TYPE';
790 l_value := p_payload_type;
791 ELSIF NOT m4u_dmd_utils.valid_type('DIRECTION',p_payload_dir,true) THEN
792 l_param := 'PAYLOAD_DIRECTION';
793 l_value := p_payload_dir;
794 END IF;
795
796
797 SELECT msg_id, retry_count
798 INTO l_msg_id, l_retry_count
799 FROM m4u_dmd_messages
800 WHERE orig_msg_id = p_orig_msg_id
801 OR msg_id = p_msg_id;
802
803 IF p_retry_count IS NOT NULL AND
804 l_retry_count <> p_retry_count THEN
805 l_param := 'RETRY_COUNT';
806 l_value := p_retry_count;
807 END IF;
808
809 IF l_param IS NOT NULL THEN
810 l_err_msg := m4u_dmd_utils.get_inv_param_msg
811 (l_err_api,l_param,l_value);
812 RAISE fnd_api.g_exc_error;
813 END IF;
814 m4u_dmd_utils.log('Exiting validation block - success',2);
815 END;
816 -- validation block ends
817
818 m4u_dmd_utils.log('UPDATE m4u_dmd_messages' ,1);
819 UPDATE m4u_dmd_messages
820 SET
821 ref_msg_id = NVL(p_ref_msg_id, ref_msg_id ),
822 orig_msg_id = NVL(p_orig_msg_id, orig_msg_id ),
823 status = NVL(p_status, status ),
824 msg_timestamp = NVL(p_msg_timstamp, msg_timestamp ),
825
826 bpel_process_id = NVL(p_bpel_process_id, bpel_process_id),
827 bpel_instance_id = NVL(p_bpel_instance_id, bpel_instance_id),
828
829 sender_gln = NVL(p_sender_gln, sender_gln ),
830 receiver_gln = NVL(p_receiver_gln, receiver_gln ),
831 rep_party_gln = NVL(p_rep_party_gln, rep_party_gln ),
832 user_id = NVL(p_user_id, user_id ),
833 user_gln = NVL(p_user_gln, user_gln )
834 WHERE msg_id = l_msg_id;
835
836 m4u_dmd_utils.log('Update SQL%ROWCOUNT - '||SQL%rowcount ,1);
837
838
839
840
841 l_payload_id := null;
842
843 IF p_payload IS NOT NULL THEN
844 m4u_dmd_utils.log('Call m4u_dmd_requests.create_payload' ,2);
845 l_ret_msg := '';
846 l_ret_sts := '';
847 m4u_dmd_requests.create_payload
848 (
849 p_xml => p_payload,
850 p_type => p_payload_type,
851 p_dir => p_payload_dir,
852 x_payload_id => l_payload_id,
853 x_ret_sts => l_ret_sts,
854 x_ret_msg => l_ret_msg
855 );
856 m4u_dmd_utils.log('l_ret_sts - |' || l_ret_sts || '|',1);
857 m4u_dmd_utils.log('l_ret_msg - |' || l_ret_msg || '|',1);
858 m4u_dmd_utils.log('l_payload_id - |' || l_payload_id|| '|',1);
859
860 IF l_ret_sts <> fnd_api.g_ret_sts_success THEN
861 l_err_msg := l_ret_msg;
862 RAISE fnd_api.g_exc_error;
863 END IF;
864 END IF;
865
866
867 IF p_update_doc_flag = 'Y' THEN
868 -- loop and update all req documents
869 m4u_dmd_utils.log('Update documents',1);
870 --For cin ack update CIN only on sent
871 --not on init or retry (limitation)
872 IF p_payload_type = m4u_dmd_utils.c_type_cin_ack
873 AND p_doc_status IN (m4u_dmd_utils.c_sts_sent,m4u_dmd_utils.c_sts_error,
874 m4u_dmd_utils.c_sts_fail) THEN
875 l_msg_id := p_ref_msg_id;
876 END IF;
877
878 FOR l_doc_rec IN docs_for_msg(l_msg_id,l_retry_count)
879 LOOP
880
881 m4u_dmd_utils.log('Call update_document - ' || l_doc_rec.doc_id,1);
882 l_ret_msg := '';
883 l_ret_sts := '';
884 m4u_dmd_requests.update_document
885 (
886 p_doc_id => l_doc_rec.doc_id,
887 p_ref_doc_id => l_doc_rec.ref_doc_id,
888 p_doc_status => p_doc_status,
889 p_func_status => null,
890 p_retry_count => l_retry_count,
891 p_processing_msg => p_processing_msg,
892 p_payload_id => l_payload_id,
893 p_payload_dir => p_payload_dir,
894 p_payload_type => p_payload_type,
895 x_ret_sts => l_ret_sts,
896 x_ret_msg => l_ret_msg
897 );
898
899 m4u_dmd_utils.log('l_ret_sts - |' || l_ret_sts || '|',1);
903 l_err_msg := nvl(l_ret_msg,'Unexpected error while updating document');
900 m4u_dmd_utils.log('l_ret_msg - |' || l_ret_msg || '|',1);
901
902 IF l_ret_sts <> fnd_api.g_ret_sts_success THEN
904 RAISE fnd_api.g_exc_error;
905 END IF;
906 END LOOP;
907
908 END IF;
909
910 m4u_dmd_utils.log('Exiting m4u_dmd_request.update_request - Success',2);
911
912 x_ret_sts := fnd_api.g_ret_sts_success;
913 x_ret_msg := '';
914 RETURN;
915 EXCEPTION
916 WHEN OTHERS THEN
917 m4u_dmd_utils.handle_error(l_err_api,l_err_msg,
918 SQLCODE,SQLERRM,x_ret_sts,x_ret_msg);
919 END update_request;
920
921 -- updates document record in m4u_dmd_documents
922 -- only updatable fields are allowed inputs
923 PROCEDURE update_document
924 (
925 p_doc_id IN VARCHAR2,
926 p_doc_status IN VARCHAR2,
927 p_func_status IN VARCHAR2,
928 p_retry_count IN NUMBER,
929 p_processing_msg IN VARCHAR2 := NULL,
930 p_ref_doc_id IN VARCHAR2 := NULL,
931 p_orig_doc_id IN VARCHAR2 := NULL,
932 p_timestamp IN VARCHAR2 := NULL,
933
934 p_top_gtin IN VARCHAR2 := NULL,
935 p_info_provider_gln IN VARCHAR2 := NULL,
936 p_data_recepient_gln IN VARCHAR2 := NULL,
937 p_tgt_mkt_cntry IN VARCHAR2 := NULL,
938 p_tgt_mkt_subdiv IN VARCHAR2 := NULL,
939
940 p_param1 IN VARCHAR2 := NULL,
941 p_param2 IN VARCHAR2 := NULL,
942 p_param3 IN VARCHAR2 := NULL,
943 p_param4 IN VARCHAR2 := NULL,
944 p_param5 IN VARCHAR2 := NULL,
945 p_lparam1 IN VARCHAR2 := NULL,
946 p_lparam2 IN VARCHAR2 := NULL,
947 p_lparam3 IN VARCHAR2 := NULL,
948 p_lparam4 IN VARCHAR2 := NULL,
949 p_lparam5 IN VARCHAR2 := NULL,
950
951 p_payload_id IN VARCHAR2 := NULL,
952 p_payload_dir IN VARCHAR2 := NULL,
953 p_payload_type IN VARCHAR2 := NULL,
954 x_ret_sts OUT NOCOPY VARCHAR2,
955 x_ret_msg OUT NOCOPY VARCHAR2
956 )
957 IS
958 l_cln_evt_params wf_parameter_list_t;
959 l_cln_evt_key VARCHAR2(30);
960 l_err_api VARCHAR2(50) := 'm4u_dmd_requests.update_document';
961 l_err_msg VARCHAR2(2000);
962 l_param VARCHAR2(100);
963 l_value VARCHAR2(2000);
964 l_orig_doc_id VARCHAR2(80);
965 l_doc_rec m4u_dmd_documents%rowtype;
966 l_doc_id VARCHAR2(30);
967 l_retry_count NUMBER;
968 l_cis_status VARCHAR2(30);
969 BEGIN
970
971 -- TBD :validate parameters
972 m4u_dmd_utils.log('Entering m4u_dmd_request.update_document',2);
973 m4u_dmd_utils.log('p_doc_id -|'|| p_doc_id ||'|',1);
974 m4u_dmd_utils.log('p_retry_count -|'|| p_retry_count ||'|',1);
975 m4u_dmd_utils.log('p_doc_status -|'|| p_doc_status ||'|',1);
976 m4u_dmd_utils.log('p_func_status -|'|| p_func_status ||'|',1);
977 m4u_dmd_utils.log('p_processing_msg -|'|| p_processing_msg ||'|',1);
978 m4u_dmd_utils.log('p_ref_doc_id -|'|| p_ref_doc_id ||'|',1);
979 m4u_dmd_utils.log('p_orig_doc_id -|'|| p_orig_doc_id ||'|',1);
980 m4u_dmd_utils.log('p_timestamp -|'|| p_timestamp ||'|',1);
981
982 m4u_dmd_utils.log('p_top_gtin -|'|| p_top_gtin ||'|',1);
983 m4u_dmd_utils.log('p_info_provider_gln-|'|| p_info_provider_gln ||'|',1);
984 m4u_dmd_utils.log('p_data_rcpt_gln -|'|| p_data_recepient_gln||'|',1);
985 m4u_dmd_utils.log('p_tgt_mkt_ctry -|'|| p_tgt_mkt_cntry ||'|',1);
986 m4u_dmd_utils.log('p_tgt_mkt_div -|'|| p_tgt_mkt_subdiv ||'|',1);
987
988 m4u_dmd_utils.log('p_param1 -|'|| p_param1 ||'|',1);
989 m4u_dmd_utils.log('p_param2 -|'|| p_param2 ||'|',1);
990 m4u_dmd_utils.log('p_param3 -|'|| p_param3 ||'|',1);
991 m4u_dmd_utils.log('p_param4 -|'|| p_param4 ||'|',1);
992 m4u_dmd_utils.log('p_param5 -|'|| p_param5 ||'|',1);
993 m4u_dmd_utils.log('p_lparam1 -|'|| p_lparam1 ||'|',1);
994 m4u_dmd_utils.log('p_lparam2 -|'|| p_lparam2 ||'|',1);
995 m4u_dmd_utils.log('p_lparam3 -|'|| p_lparam3 ||'|',1);
996 m4u_dmd_utils.log('p_lparam4 -|'|| p_lparam4 ||'|',1);
997 m4u_dmd_utils.log('p_lparam5 -|'|| p_lparam5 ||'|',1);
998
999 m4u_dmd_utils.log('p_payload_id -|'|| p_payload_id ||'|',1);
1000 m4u_dmd_utils.log('p_payload_dir -|'|| p_payload_dir ||'|',1);
1001 m4u_dmd_utils.log('p_payload_type -|'|| p_payload_type ||'|',1);
1002
1003 --validation block begins
1004 BEGIN
1005 m4u_dmd_utils.log('Entering validation block',2);
1006 l_param := '';
1007 l_value := '';
1008
1009 -- validate id, type, status params
1010 IF NOT m4u_dmd_utils.valid_type('DOC_STATUS',p_doc_status,false) THEN
1011 l_param := 'DOC_STATUS';
1012 l_value := p_doc_status;
1016 l_value := p_doc_id|| '/' || p_orig_doc_id;
1013 ELSIF NOT m4u_dmd_utils.valid_doc_id(p_doc_id,false)
1014 AND NOT m4u_dmd_utils.valid_orig_doc_id(p_orig_doc_id,false) THEN
1015 l_param := 'DOC_ID/ORIG_DOC_ID';
1017 ELSIF NOT m4u_dmd_utils.valid_doc_id(p_ref_doc_id,true) THEN
1018 l_param := 'REF_DOC_ID';
1019 l_value := p_ref_doc_id;
1020 ELSIF NOT m4u_dmd_utils.valid_len(p_orig_doc_id ,0,80,true) THEN
1021 l_param := 'ORIG_DOC_ID';
1022 l_value := p_orig_doc_id ;
1023 ELSIF NOT m4u_dmd_utils.valid_len(p_processing_msg,0,400,true) THEN
1024 l_param := 'PROCESSING_MSG';
1025 l_value := p_processing_msg ;
1026
1027
1028 -- validate extensible params
1029 ELSIF NOT m4u_dmd_utils.valid_len(p_param1,0,50,true) THEN
1030 l_param := 'PARAM1';
1031 l_value := p_param1;
1032 ELSIF NOT m4u_dmd_utils.valid_len(p_param2,0,50,true) THEN
1033 l_param := 'PARAM2';
1034 l_value := p_param2;
1035 ELSIF NOT m4u_dmd_utils.valid_len(p_param3,0,50,true) THEN
1036 l_param := 'PARAM3';
1037 l_value := p_param3;
1038 ELSIF NOT m4u_dmd_utils.valid_len(p_param4,0,50,true) THEN
1039 l_param := 'PARAM4';
1040 l_value := p_param4;
1041 ELSIF NOT m4u_dmd_utils.valid_len(p_param5,0,50,true) THEN
1042 l_param := 'PARAM5';
1043 l_value := p_param5;
1044 ELSIF NOT m4u_dmd_utils.valid_len(p_lparam1,0,400,true) THEN
1045 l_param := 'LPARAM1';
1046 l_value := p_lparam1;
1047 ELSIF NOT m4u_dmd_utils.valid_len(p_lparam2,0,400,true) THEN
1048 l_param := 'LPARAM2';
1049 l_value := p_lparam2;
1050 ELSIF NOT m4u_dmd_utils.valid_len(p_lparam3,0,400,true) THEN
1051 l_param := 'LPARAM3';
1052 l_value := p_lparam3;
1053 ELSIF NOT m4u_dmd_utils.valid_len(p_lparam4,0,400,true) THEN
1054 l_param := 'LPARAM4';
1055 l_value := p_lparam4;
1056 ELSIF NOT m4u_dmd_utils.valid_len(p_lparam5,0,400,true) THEN
1057 l_param := 'LPARAM5';
1058 l_value := p_lparam5;
1059
1060 -- validate doc params
1061 ELSIF NOT m4u_dmd_utils.valid_len(p_tgt_mkt_cntry ,0,30,true) THEN
1062 l_param := 'TARGET_MARKET_COUNTRY';
1063 l_value := p_tgt_mkt_cntry ;
1064 ELSIF NOT m4u_dmd_utils.valid_len(p_tgt_mkt_subdiv ,0,30,true) THEN
1065 l_param := 'TARGET_MARKET_SUBDIV';
1066 l_value := p_tgt_mkt_subdiv ;
1067 ELSIF NOT m4u_dmd_utils.valid_gtin(p_top_gtin ,true) THEN
1068 l_param := 'TOP_GTIN';
1069 l_value := p_top_gtin ;
1070 ELSIF NOT m4u_dmd_utils.valid_gln(p_info_provider_gln ,true) THEN
1071 l_param := 'INFO_PROVIDER_GLN';
1072 l_value := p_info_provider_gln;
1073 ELSIF NOT m4u_dmd_utils.valid_gln(p_data_recepient_gln ,true) THEN
1074 l_param := 'DATA_RCPT_GLN';
1075 l_value := p_data_recepient_gln ;
1076
1077 -- validate payload params
1078 ELSIF NOT m4u_dmd_utils.valid_type('PAYLOAD_TYPE',p_payload_type,true) THEN
1079 l_param := 'PAYLOAD_TYPE';
1080 l_value := p_payload_type;
1081 ELSIF NOT m4u_dmd_utils.valid_type('DIRECTION',p_payload_dir,true) THEN
1082 l_param := 'PAYLOAD_DIRECTION';
1083 l_value := p_payload_dir;
1084 ELSIF NOT m4u_dmd_utils.valid_payload_id(p_payload_id,true) THEN
1085 l_param := 'PAYLOAD_ID';
1086 l_value := p_payload_id;
1087
1088 END IF;
1089
1090 IF l_param IS NOT NULL THEN
1091 l_err_msg := m4u_dmd_utils.get_inv_param_msg
1092 (l_err_api,l_param,l_value);
1093 RAISE fnd_api.g_exc_error;
1094 END IF;
1095 m4u_dmd_utils.log('Exiting validation block - success',2);
1096 END;
1097 --validation block ends
1098
1099 IF p_doc_id IS NULL THEN
1100 SELECT doc_id , retry_count
1101 INTO l_doc_id, l_retry_count
1102 FROM m4u_dmd_documents
1103 WHERE orig_doc_id = p_orig_doc_id;
1104 ELSE
1105 l_doc_id := p_doc_id;
1106 l_retry_count := p_retry_count;
1107 END IF;
1108
1109 SELECT *
1110 INTO l_doc_rec
1111 FROM m4u_dmd_documents
1112 WHERE doc_id = l_doc_id;
1113
1114 IF p_orig_doc_id = 'GENERATE' THEN
1115 l_orig_doc_id := l_doc_rec.type || '.' || sysdate ||
1116 '.' || p_retry_count || '.' || l_doc_id;
1117 ELSE
1118 l_orig_doc_id := l_doc_rec.orig_doc_id;
1119 END IF;
1120
1121 m4u_dmd_utils.log('l_doc_id -|'|| l_doc_id ||'|',1);
1122
1123 m4u_dmd_utils.log('UPDATE m4u_dmd_documents' ,1);
1124 UPDATE m4u_dmd_documents
1125 SET ref_doc_id = NVL(p_ref_doc_id, ref_doc_id ),
1129 processing_message = NVL(p_processing_msg,processing_message ),
1126 orig_doc_id = NVL(l_orig_doc_id, orig_doc_id ),
1127 retry_count = NVL(l_retry_count,retry_count ),
1128 doc_status = NVL(p_doc_status,doc_status ),
1130 functional_status = NVL(p_func_status,functional_status ),
1131 doc_timestamp = NVL(p_timestamp, doc_timestamp ),
1132
1133 top_gtin = NVL(p_top_gtin, top_gtin ),
1134 info_provider_gln = NVL(p_info_provider_gln , info_provider_gln ),
1135 data_recepient_gln = NVL(p_data_recepient_gln, data_recepient_gln ),
1136 target_market_country = NVL(target_market_country, p_tgt_mkt_cntry ),
1137 target_market_sub_div = NVL(target_market_sub_div, p_tgt_mkt_subdiv ),
1138
1139 parameter1 = NVL(p_param1 , parameter1 ),
1140 parameter2 = NVL(p_param2 , parameter2 ),
1141 parameter3 = NVL(p_param3 , parameter3 ),
1142 parameter4 = NVL(p_param4 , parameter4 ),
1143 parameter5 = NVL(p_param5 , parameter5 ),
1144 lparameter1 = NVL(p_lparam1 , lparameter1),
1145 lparameter2 = NVL(p_lparam2 , lparameter2),
1146 lparameter3 = NVL(p_lparam3 , lparameter3),
1147 lparameter4 = NVL(p_lparam4 , lparameter4),
1148 lparameter5 = NVL(p_lparam5 , lparameter5),
1149
1150
1151 last_update_date = sysdate,
1152 last_updated_by = FND_GLOBAL.user_id,
1153 last_update_login = FND_GLOBAL.login_id
1154 WHERE doc_id = l_doc_id;
1155
1156
1157 m4u_dmd_utils.log('Update SQL%ROWCOUNT - ' ||SQL%rowcount,1);
1158
1159 --cis specific processing
1160 BEGIN
1161 IF l_doc_rec.type = m4u_dmd_utils.c_type_cis THEN
1162
1163 m4u_dmd_utils.log('Update CIS record ',1);
1164 BEGIN
1165 SELECT status
1166 INTO l_cis_status
1167 FROM m4u_dmd_subscriptions
1168 WHERE subscription_name = l_doc_rec.lparameter1;
1169 EXCEPTION
1170 WHEN OTHERS THEN
1171 null;
1172 END;
1173
1174 IF l_cis_status = 'ADD_IN_PROGRESS' THEN
1175 IF p_doc_status = m4u_dmd_utils.c_sts_success THEN
1176 l_cis_status := 'SUBSCRIBED';
1177 ELSIF p_doc_status = m4u_dmd_utils.c_sts_sent THEN
1178 l_cis_status := l_cis_status;
1179 ELSIF p_doc_status in
1180 (m4u_dmd_utils.c_sts_error, m4u_dmd_utils.c_sts_fail) THEN
1181 l_cis_status := 'ADD_FAILED';
1182 END IF;
1183 ELSIF l_cis_status = 'DELETE_IN_PROGRESS' THEN
1184 IF p_doc_status = m4u_dmd_utils.c_sts_success THEN
1185 l_cis_status := 'UNSUBSCRIBED';
1186 ELSIF p_doc_status = m4u_dmd_utils.c_sts_sent THEN
1187 l_cis_status := l_cis_status;
1188 ELSIF p_doc_status in
1189 (m4u_dmd_utils.c_sts_error, m4u_dmd_utils.c_sts_fail) THEN
1190 l_cis_status := 'DELETE_FAILED';
1191 END IF;
1192 END IF;
1193
1194 UPDATE m4u_dmd_subscriptions
1195 SET status = l_cis_status
1196 WHERE subscription_name = l_doc_rec.lparameter1;
1197
1198 m4u_dmd_utils.log('Update SQL%ROWCOUNT - ' ||SQL%rowcount,1);
1199 END IF;
1200 END;
1201
1202 --Raise CLN event
1203 BEGIN
1204 --set key
1205 l_cln_evt_key := sysdate || '.' || p_doc_id;
1206
1207 --set params
1208 l_cln_evt_params := wf_parameter_list_t();
1209 wf_event.addParameterToList('DOC_ID', l_doc_id, l_cln_evt_params);
1210 wf_event.addParameterToList('REF_DOC_ID', p_ref_doc_id, l_cln_evt_params);
1211 wf_event.addParameterToList('RETRY_COUNT', l_retry_count, l_cln_evt_params);
1212
1213 wf_event.addParameterToList('DOC_TYPE', l_doc_rec.type, l_cln_evt_params);
1214 wf_event.addParameterToList('ACTION', l_doc_rec.action, l_cln_evt_params);
1215 wf_event.addParameterToList('DOC_STATUS', p_doc_status, l_cln_evt_params);
1216 wf_event.addParameterToList('PROCESSING_MSG', p_processing_msg, l_cln_evt_params);
1217
1218 wf_event.addParameterToList('PAYLOAD_ID', p_payload_id, l_cln_evt_params);
1219 wf_event.addParameterToList('PAYLOAD_TYPE', p_payload_type, l_cln_evt_params);
1220 wf_event.addParameterToList('PAYLOAD_DIR', p_payload_dir, l_cln_evt_params);
1221
1222 --raise event
1223 wf_event.raise
1224 (
1225 p_event_name => m4u_dmd_utils.c_cln_event,
1226 p_event_key => l_cln_evt_key,
1227 p_parameters => l_cln_evt_params
1228 );
1232 m4u_dmd_utils.log('Unexpected error while raising payload event',6);
1229 EXCEPTION
1230 WHEN OTHERS THEN
1231 m4u_dmd_utils.log('m4u_dmd_messages.create_document', 6);
1233 m4u_dmd_utils.log(SQLCODE || SQLERRM, 6);
1234
1235 END;
1236
1237
1238 m4u_dmd_utils.log('Exiting m4u_dmd_request.update_document - Success',2);
1239 x_ret_sts := fnd_api.g_ret_sts_success;
1240 x_ret_msg := '';
1241 RETURN; --sucess
1242
1243 EXCEPTION
1244 WHEN OTHERS THEN
1245 m4u_dmd_utils.handle_error(l_err_api,l_err_msg,
1246 SQLCODE,SQLERRM,x_ret_sts,x_ret_msg);
1247 END update_document;
1248
1249 END m4u_dmd_requests;