[Home] [Help]
PACKAGE BODY: APPS.M4U_SETUP_PACKAGE
Source
1 PACKAGE BODY M4U_SETUP_PACKAGE AS
2 /* $Header: M4USETPB.pls 120.0 2005/08/01 03:50:08 rkrishan noship $ */
3
4 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
5
6 -- Name
7 -- add_or_update_tp_detail
8 -- Purpose
9 -- This procedure sets up the XMLGateway Trading Partner Setup detail
10 -- for a single transaction based on the params
11 -- If detail record is present it updates else inserts
12 -- Arguments
13 -- x_err_buf => API param for concurrent program calls
14 -- x_retcode => API param for concurrent program calls
15 -- <paramlist> => corresponds to the ecx_tp_api, nothing to talk abt
16 -- Notes
17 -- none
18 PROCEDURE add_or_update_tp_detail(
19 x_errbuf OUT NOCOPY VARCHAR2,
20 x_retcode OUT NOCOPY VARCHAR2,
21 p_txn_subtype VARCHAR2,
22 p_map VARCHAR2,
23 p_direction VARCHAR2,
24 p_tp_hdr_id NUMBER
25 )
26 IS
27 l_tp_dtl_id NUMBER;
28 l_ext_process_id NUMBER;
29 l_routing_id NUMBER;
30 l_doc_conf NUMBER;
31 l_hub_user_id NUMBER;
32 l_tp_hdr_id NUMBER;
33
34
35 l_standard_code VARCHAR2(10);
36 l_party_type VARCHAR2(10);
37 l_direction VARCHAR2(10);
38 l_retcode VARCHAR2(20);
39
40
41 l_ext_type VARCHAR2(30);
42 l_ext_subtype VARCHAR2(30);
43 l_transaction_type VARCHAR2(30);
44 l_transaction_subtype VARCHAR2(30);
45 l_conn_type VARCHAR2(30);
46 l_protocol VARCHAR2(30);
47 l_protocol_addr VARCHAR2(30);
48 l_user VARCHAR2(30);
49 l_passwd VARCHAR2(30);
50 l_src_loc VARCHAR2(30);
51 l_ext_loc VARCHAR2(30);
52 l_map VARCHAR2(60);
53 l_msg_data VARCHAR2(255);
54 l_retmesg VARCHAR2(500);
55
56
57 l_record_found BOOLEAN;
58
59
60
61 BEGIN
62
63 IF (l_Debug_Level <= 2) THEN
64 cln_debug_pub.Add('----- ENTERING add_or_update_tp_detail API ------', 2);
65 END IF;
66
67 IF (l_Debug_Level <= 1) THEN
68 cln_debug_pub.Add('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' );
69 cln_debug_pub.Add('' );
70 cln_debug_pub.Add('Parameters :' );
71 cln_debug_pub.Add('p_txn_subtype ' || p_txn_subtype );
72 cln_debug_pub.Add('p_direction ' || p_direction );
73 cln_debug_pub.Add('p_map ' || p_map );
74 cln_debug_pub.Add('p_tp_hdr_id ' || p_tp_hdr_id );
75 cln_debug_pub.Add('------------------------------' );
76 END IF;
77
78 IF (l_Debug_Level <= 1) THEN
79 cln_debug_pub.Add('- Defaulting the local variables - ', 1);
80 END IF;
81
82 l_ext_type := 'M4U';
83 l_standard_code := 'UCCNET';
84 l_transaction_type := 'M4U';
85 l_party_type := 'I';
86 l_doc_conf := '2';
87 l_src_loc := 'UCCNET_HUB';
88 l_ext_subtype := p_txn_subtype;
89 l_transaction_subtype := p_txn_subtype;
90 l_tp_hdr_id := p_tp_hdr_id;
91 l_map := p_map;
92 l_direction := p_direction;
93
94
95 IF (l_direction = 'OUT') THEN
96 l_conn_type := 'DIRECT';
97 l_protocol := 'HTTP';
98 l_protocol_addr := 'http://none';
99 l_user := 'operations';
100 l_passwd := 'welcome';
101 END IF;
102
103 IF (l_Debug_Level <= 1) THEN
104 cln_debug_pub.Add('-----------------------------------' );
105 cln_debug_pub.Add('l_ext_type :=M4U',1);
106 cln_debug_pub.Add('l_standard_code :=UCCNET',1);
107 cln_debug_pub.Add('l_transaction_type :=M4U',1);
108 cln_debug_pub.Add('l_party_type :=I',1);
109 cln_debug_pub.Add('l_doc_conf :=2',1);
110 cln_debug_pub.Add('l_src_loc :=UCCNET_HUB',1);
111 cln_debug_pub.Add('l_hub_user_id :=null',1);
112 cln_debug_pub.Add('l_routing_id :=null',1);
113 cln_debug_pub.Add('l_ext_loc :=null',1);
114 cln_debug_pub.Add('l_ext_subtype :='||l_ext_subtype,1);
115 cln_debug_pub.Add('l_transaction_subtype :='||l_ext_subtype,1);
116 cln_debug_pub.Add('l_tp_header_id :='||l_tp_hdr_id,1);
117 cln_debug_pub.Add('l_map :='||l_map,1);
118 cln_debug_pub.Add('l_direction :='||l_direction,1);
119 cln_debug_pub.Add('------------------------------' );
120 END IF;
121
122
123 -- query ecx_tp_detail_id to get ecx_tp_detail_id for given location code
124 -- if record does not exists create a new detail record
125 -- else update the detail record
126 BEGIN
127 IF (l_Debug_Level <= 1) THEN
128 cln_debug_pub.Add('- Get TP Detail ID - ', 1);
129 END IF;
130
131 SELECT tp_detail_id
132 INTO l_tp_dtl_id
133 FROM
134 ecx_tp_details tpd,
135 ecx_tp_headers tph,
136 ecx_ext_processes extp,
137 ecx_transactions txn,
138 ecx_standards svl
139 WHERE 1=1
140 AND tph.tp_header_id = tpd.tp_header_id
141 AND tpd.ext_process_id = extp.ext_process_id
142 AND extp.transaction_id = txn.transaction_id
143 AND extp.standard_id = svl.standard_id
144 AND svl.standard_code = l_standard_code
145 AND extp.ext_type = l_ext_type
146 AND extp.ext_subtype = l_ext_subtype
147 AND extp.direction = l_direction
148 AND txn.transaction_type = l_transaction_type
149 AND txn.transaction_subtype = l_transaction_subtype
150 AND tph.tp_header_id = l_tp_hdr_id;
151
152 l_record_found := TRUE;
153
154 IF (l_Debug_Level <= 1) THEN
155 cln_debug_pub.Add('---- Got ECX TP Detail ID Value ----', 1);
156 cln_debug_pub.Add('TP Detail Id -'||l_tp_dtl_id, 1);
157 END IF;
158 EXCEPTION
159 WHEN NO_DATA_FOUND THEN
160 --FND_MESSAGE.SET_NAME('CLN','M4U_TP_DTL_SETUP_NEW');
161 --l_msg_data := FND_MESSAGE.GET;
162 l_record_found := FALSE;
163
164 IF (l_Debug_Level <= 1) THEN
165 cln_debug_pub.Add('-- TP Detail Setup for M4U does not exist --',1);
166 END IF;
167
168 WHEN OTHERS THEN
169 FND_MESSAGE.SET_NAME('CLN','M4U_TP_DTL_SETUP_ISSUE');
170 FND_MESSAGE.SET_TOKEN('PARAM1',l_transaction_type);
171 FND_MESSAGE.SET_TOKEN('PARAM2',l_transaction_subtype);
172 FND_MESSAGE.SET_TOKEN('PARAM3',l_direction);
173 FND_MESSAGE.SET_TOKEN('PARAM4',l_standard_code);
174 l_msg_data := FND_MESSAGE.GET;
175 RAISE FND_API.G_EXC_ERROR;
176 END;
177
178 BEGIN
179 IF (l_Debug_Level <= 1) THEN
180 cln_debug_pub.Add('- Get Ext Process ID - ', 1);
181 END IF;
182
183 SELECT extp.ext_process_id
184 INTO l_ext_process_id
185 FROM ecx_ext_processes extp,
186 ecx_transactions txn,
187 ecx_standards svl
188 WHERE 1=1
189 AND extp.transaction_id = txn.transaction_id
190 AND extp.standard_id = svl.standard_id
191 AND svl.standard_code = l_standard_code
192 AND extp.ext_type = l_ext_type
193 AND extp.ext_subtype = l_ext_subtype
194 AND extp.direction = l_direction
195 AND txn.party_type = l_party_type
196 AND txn.transaction_type = l_transaction_type
197 AND txn.transaction_subtype = l_transaction_subtype;
198
199 IF (l_Debug_Level <= 1) THEN
200 cln_debug_pub.Add('---- Got Ext Process ID Value ----', 1);
201 cln_debug_pub.Add('Ext Process Id -'||l_ext_process_id, 1);
202 END IF;
203
204 EXCEPTION
205 WHEN NO_DATA_FOUND THEN
206 IF (l_Debug_Level <= 1) THEN
207 cln_debug_pub.Add('-- Ext Process ID does not exist --',1);
208 END IF;
209
210 WHEN TOO_MANY_ROWS THEN
211 FND_MESSAGE.SET_NAME('CLN','M4U_EXT_PROCESS_SETUP_EXISTS');
212 FND_MESSAGE.SET_TOKEN('PARAM1',l_transaction_type);
213 FND_MESSAGE.SET_TOKEN('PARAM2',l_transaction_subtype);
214 FND_MESSAGE.SET_TOKEN('PARAM3',l_direction);
215 FND_MESSAGE.SET_TOKEN('PARAM4',l_standard_code);
216 l_msg_data := FND_MESSAGE.GET;
217
218 IF (l_Debug_Level <= 1) THEN
219 cln_debug_pub.Add('-- Two Many Ext Process ID for M4U exists --',1);
220 END IF;
221 RAISE FND_API.G_EXC_ERROR;
222
223 WHEN OTHERS THEN
224 l_msg_data := SQLCODE ||'-'||SQLERRM;
225 IF (l_Debug_Level <= 1) THEN
226 cln_debug_pub.Add('ERROR -'||l_msg_data,1);
227 cln_debug_pub.Add('-- Error in Finding the External Process ID --',1);
228 END IF;
229 RAISE FND_API.G_EXC_ERROR;
230 END;
231
232
233 IF NOT l_record_found THEN
234 IF (l_Debug_Level <= 1) THEN
235 cln_debug_pub.Add('---- Setup ECX TP Detail Values in DB ----', 1);
236 cln_debug_pub.Add('---- Call ecx_tp_api.create_tp_detail ----', 1);
237 END IF;
238
239 ecx_tp_api.create_tp_detail(
240 x_return_status => l_retcode,
241 x_msg => l_retmesg,
242 x_tp_detail_id => l_tp_dtl_id,
243 p_tp_header_id => l_tp_hdr_id,
244 p_ext_process_id => l_ext_process_id,
245 p_map_code => l_map,
246 p_connection_type => l_conn_type,
247 p_hub_user_id => l_hub_user_id,
248 p_protocol_type => l_protocol,
249 p_protocol_address => l_protocol_addr,
250 p_username => l_user,
251 p_password => l_passwd,
252 p_routing_id => l_routing_id,
253 p_source_tp_location_code => l_src_loc,
254 p_external_tp_location_code => l_ext_loc,
255 p_confirmation => l_doc_conf
256 );
257
258 IF l_retcode <> 0 THEN
259 FND_MESSAGE.SET_NAME('CLN','M4U_ECXTPDTL_API_FAILED');
260 FND_MESSAGE.SET_TOKEN('PARAM1','create');
261 l_msg_data := FND_MESSAGE.GET;
262 RAISE FND_API.G_EXC_ERROR;
263 END IF;
264
265 IF (l_Debug_Level <= 1) THEN
266 cln_debug_pub.Add('---- ecx_tp_api.update_trading_partner returns Normal----', 1);
267 END IF;
268 ELSE
269
270 IF (l_Debug_Level <= 1) THEN
271 cln_debug_pub.Add('---- Setup ECX TP Detail Values in DB ----', 1);
272 cln_debug_pub.Add('---- Call ecx_tp_api.update_tp_detail ----', 1);
273 END IF;
274 ecx_tp_api.update_tp_detail(
275 x_return_status => l_retcode,
276 x_msg => l_retmesg,
277 p_tp_detail_id => l_tp_dtl_id,
278 p_map_code => l_map,
279 p_ext_process_id => l_ext_process_id,
280 p_connection_type => l_conn_type,
281 p_hub_user_id => l_hub_user_id,
282 p_protocol_type => l_protocol,
283 p_protocol_address => l_protocol_addr,
284 p_username => l_user,
285 p_password => l_passwd,
286 p_routing_id => l_routing_id,
287 p_source_tp_location_code => l_src_loc,
288 p_external_tp_location_code => l_ext_loc,
289 p_confirmation => l_doc_conf,
290 p_passupd_flag => 'Y'
291 );
292
293 IF l_retcode <> 0 THEN
294 FND_MESSAGE.SET_NAME('CLN','M4U_ECXTPDTL_API_FAILED');
295 FND_MESSAGE.SET_TOKEN('PARAM1','update');
296 l_msg_data := FND_MESSAGE.GET;
297 RAISE FND_API.G_EXC_ERROR;
298 END IF;
299
300 IF (l_Debug_Level <= 1) THEN
301 cln_debug_pub.Add('---- ecx_tp_api.update_trading_partner returns Normal----', 1);
302 END IF;
303 END IF;
304
305 IF l_retcode = 0 THEN
306 x_retcode := FND_API.G_RET_STS_SUCCESS;
307 x_errbuf := 'Successful';
308 END IF;
309
310 cln_debug_pub.Add('Exiting add_or_update_tp_detail normally');
311
312 -- Exception Handling
313 EXCEPTION
314 WHEN FND_API.G_EXC_ERROR THEN
315 x_retcode := 2;
316 x_errbuf := l_msg_data;
317
318 IF (l_Debug_Level <= 2) THEN
319 cln_debug_pub.Add('ERROR : '||x_errbuf, 2);
320 cln_debug_pub.Add('==========ERROR :EXTING add_or_update_tp_detail API ===========', 2);
321 END IF;
322
323 WHEN OTHERS THEN
324 x_retcode := 2;
325 -- Setup Failed
326 x_errbuf := SQLCODE||' - '||SQLERRM;
327
328 IF (l_Debug_Level <= 2) THEN
329 cln_debug_pub.Add('ERROR : '||x_errbuf, 2);
330 cln_debug_pub.Add('==========ERROR :EXTING add_or_update_tp_detail API ===========', 2);
331 END IF;
332 END;
333
334
335 -- Name
336 -- SETUP
337 -- Purpose
338 -- This procedure is called from a concurrent program(can be called from anywhere actually).
339 -- This procedure does the setup required for m4u
340 -- i) Setup default TP location in HR_LOCATIONS
341 -- ii) Setup XMLGateway trading partner definition
342 -- Arguments
343 -- x_err_buf => API out result param for concurrent program calls
344 -- x_retcode => API out result param for concurrent program calls
345 -- p_location_code => Should have value 'OIPC Default TP'
346 -- p_description => Some description
347 -- p_addr_line_1 => Some address line 1
348 -- p_region_1 => Some region 1 (province)
349 -- p_region_2 => Some region 2 (State)
350 -- p_town_or_city => Some city
351 -- p_postal_code => Some postal code
352 -- Notes
353 -- All the input arguments are used in the call to setup_hr_locations
354 -- The concurrent program will be failed in case of any error
355 -- All arguments are moved into the code
356 PROCEDURE SETUP(
357 x_errbuf OUT NOCOPY VARCHAR2,
358 x_retcode OUT NOCOPY NUMBER
359
360 )
361 IS
362 l_style VARCHAR2(7);
363 l_retcode VARCHAR2(20);
364 l_location_code VARCHAR2(60);
365 l_country VARCHAR2(60);
366 l_description VARCHAR2(240);
367 l_msg_data VARCHAR2(240);
368 l_addr_line_1 VARCHAR2(240);
369 l_retmesg VARCHAR2(500);
370
371 l_location_id NUMBER;
372 l_obj_ver_num NUMBER;
373 l_tp_hdr_id NUMBER;
374
375 l_record_found BOOLEAN;
376
377 BEGIN
378
379 IF (l_Debug_Level <= 2) THEN
380 cln_debug_pub.Add('==========ENTERING SETUP API ===========', 2);
381 END IF;
382
383 IF (l_Debug_Level <= 1) THEN
384 cln_debug_pub.Add('---- Defaulting the HR Location Values ----', 1);
385 END IF;
386
387 l_location_code := 'UCCnet';
388 l_description := 'UCCnet Data hub';
389 l_addr_line_1 := 'Princeton';
390 l_style := 'US_GLB';
391 l_country := 'US';
392
393 IF (l_Debug_Level <= 1) THEN
394 cln_debug_pub.Add('Location code -'||l_location_code, 1);
395 cln_debug_pub.Add('Description -'||l_description, 1);
396 cln_debug_pub.Add('Address Line 1 -'||l_addr_line_1, 1);
397 cln_debug_pub.Add('Address Style -'||l_style, 1);
398 cln_debug_pub.Add('Country -'||l_country, 1);
399 END IF;
400
401 IF (l_Debug_Level <= 1) THEN
402 cln_debug_pub.Add('+++++++++ Check HR Location Values in DB +++++++++', 1);
403 END IF;
404
405 -- Setup HR Locations
406 -- Check if record exists. Create Locations if it does not else Update Location value
407 BEGIN
408 SELECT location_id, object_version_number
409 INTO l_location_id, l_obj_ver_num
410 FROM hr_locations_all
411 WHERE location_code = l_location_code;
412
413 l_record_found := TRUE;
414
415 IF (l_Debug_Level <= 1) THEN
416 cln_debug_pub.Add('---- Got HR Location Values ----', 1);
417 cln_debug_pub.Add('Location Id -'||l_location_id, 1);
418 cln_debug_pub.Add('Object Version No -'||l_obj_ver_num, 1);
419 END IF;
420 EXCEPTION
421 WHEN NO_DATA_FOUND THEN
422 l_record_found := FALSE;
423
424 IF (l_Debug_Level <= 1) THEN
425 cln_debug_pub.Add('-- HR Setup for M4U does not exist --',1);
426 END IF;
427
428 WHEN TOO_MANY_ROWS THEN
429 IF (l_Debug_Level <= 1) THEN
430 cln_debug_pub.Add('-- HR Setup for M4U already exists with two many rows--',1);
431 cln_debug_pub.Add('-- Updating one of the records arbitrarily --',1);
432 END IF;
433
434 SELECT location_id, object_version_number
435 INTO l_location_id, l_obj_ver_num
436 FROM hr_locations
437 WHERE location_code = l_location_code
438 AND ROWNUM < 2;
439
440 l_record_found := TRUE;
441
442 IF (l_Debug_Level <= 1) THEN
443 cln_debug_pub.Add('---- Got HR Location Values limiting rownum ----', 1);
444 cln_debug_pub.Add('Location Id -'||l_location_id, 1);
445 cln_debug_pub.Add('Object Version No -'||l_obj_ver_num, 1);
446 END IF;
447
448 WHEN OTHERS THEN
449 FND_MESSAGE.SET_NAME('CLN','M4U_HR_API_ISSUE');
450 l_msg_data := FND_MESSAGE.GET;
451 RAISE FND_API.G_EXC_ERROR;
452 END;
453
454 IF NOT l_record_found THEN
455 IF (l_Debug_Level <= 1) THEN
456 cln_debug_pub.Add('---- Setup HR Location Values in DB ----', 1);
457 cln_debug_pub.Add('---- Call HR_LOCATION_API.create_location ----', 1);
458 END IF;
459
460 FND_MESSAGE.SET_NAME('CLN','M4U_HR_API_FAILED');
461 FND_MESSAGE.SET_TOKEN('PARAM','Create');
462 l_msg_data := FND_MESSAGE.GET;
463
464 HR_LOCATION_API.create_location(
465 p_effective_date => sysdate,
466 p_language_code => userenv('LANG'),
467 p_location_code => l_location_code,
468 p_description => l_description,
469 p_address_line_1 => l_addr_line_1,
470 p_country => l_country,
471 p_style => l_style,
472 p_location_id => l_location_id,
473 p_object_version_number => l_obj_ver_num );
474
475 l_msg_data := 'HR Location created with success';
476
477 IF (l_Debug_Level <= 1) THEN
478 cln_debug_pub.Add('---- HR_LOCATION_API.create_location returns Normal----', 1);
479 cln_debug_pub.Add('Location Id -'||l_location_id, 1);
480 cln_debug_pub.Add('Object Version No -'||l_obj_ver_num, 1);
481 END IF;
482 ELSE
483 IF (l_Debug_Level <= 1) THEN
484 cln_debug_pub.Add('---- Update HR Location Values in DB ----', 1);
485 cln_debug_pub.Add('---- Call HR_LOCATION_API.update_location ----', 1);
486 END IF;
487
488 FND_MESSAGE.SET_NAME('CLN','M4U_HR_API_FAILED');
489 FND_MESSAGE.SET_TOKEN('PARAM','Update');
490 l_msg_data := FND_MESSAGE.GET;
491
492 HR_LOCATION_API.update_location(
493 p_effective_date => sysdate,
494 p_language_code => userenv('LANG'),
495 p_location_code => l_location_code,
496 p_description => l_description,
497 p_address_line_1 => l_addr_line_1,
498 p_country => l_country,
499 p_style => l_style,
500 p_location_id => l_location_id,
501 p_object_version_number => l_obj_ver_num );
502
503 l_msg_data := 'HR Location updated with success';
504
505 IF (l_Debug_Level <= 1) THEN
506 cln_debug_pub.Add('---- HR_LOCATION_API.update_location returns Normal----', 1);
507 cln_debug_pub.Add('Location Id -'||l_location_id, 1);
508 cln_debug_pub.Add('Object Version No -'||l_obj_ver_num, 1);
509 END IF;
510 END IF;
511
512
513 IF (l_Debug_Level <= 1) THEN
514 cln_debug_pub.Add('+++++++++ Setup ECX TP Header Values +++++++++', 1);
515 END IF;
516
517 -- reset the value for next phase
518 l_record_found := FALSE;
519
520 -- Setup ECX TP header
521 -- Check if record exists. Create TP Header if it does not else Update value
522 BEGIN
523
524 SELECT tp_header_id
525 INTO l_tp_hdr_id
526 FROM ecx_tp_headers
527 WHERE party_type = 'I'
528 AND party_id = l_location_id
529 AND party_site_id = l_location_id;
530
531 l_record_found := TRUE;
532
533 IF (l_Debug_Level <= 1) THEN
534 cln_debug_pub.Add('---- Got ECX TP Header Values ----', 1);
535 cln_debug_pub.Add('TP Header Id -'||l_tp_hdr_id, 1);
536 END IF;
537
538 EXCEPTION
539 WHEN NO_DATA_FOUND THEN
540 l_record_found := FALSE;
541
542 IF (l_Debug_Level <= 1) THEN
543 cln_debug_pub.Add('-- TP Header Setup for M4U does not exist --',1);
544 END IF;
545
546 WHEN OTHERS THEN
547 FND_MESSAGE.SET_NAME('CLN','M4U_TP_HDR_SETUP_ISSUE');
548 l_msg_data := FND_MESSAGE.GET;
549 RAISE FND_API.G_EXC_ERROR;
550 END;
551
552 IF NOT l_record_found THEN
553 IF (l_Debug_Level <= 1) THEN
554 cln_debug_pub.Add('---- Setup ECX TP Header Values in DB ----', 1);
555 cln_debug_pub.Add('---- Call ecx_tp_api.create_trading_partner ----', 1);
556 END IF;
557
558 ecx_tp_api.create_trading_partner(
559 x_return_status => l_retcode,
560 x_msg => l_retmesg,
561 x_tp_header_id => l_tp_hdr_id,
562 p_party_type => 'I',
563 p_party_id => l_location_id,
564 p_party_site_id => l_location_id,
565 p_company_admin_email => '[email protected]'
566 );
567
568 IF (l_Debug_Level <= 1) THEN
569 cln_debug_pub.Add('---- Return Values From API ----', 1);
570 cln_debug_pub.Add('Return Message - '||l_retmesg, 1);
571 cln_debug_pub.Add('Return Code - '||l_retcode, 1);
572 END IF;
573
574 IF l_retcode <> 0 THEN
575 FND_MESSAGE.SET_NAME('CLN','M4U_ECXTPHDR_API_FAILED');
576 FND_MESSAGE.SET_TOKEN('PARAM','Create');
577 l_msg_data := FND_MESSAGE.GET||' : '||l_retmesg;
578 RAISE FND_API.G_EXC_ERROR;
579 END IF;
580
581 IF (l_Debug_Level <= 1) THEN
582 cln_debug_pub.Add('---- ecx_tp_api.create_trading_partner returns Normal----', 1);
583 END IF;
584 ELSE
585 IF (l_Debug_Level <= 1) THEN
586 cln_debug_pub.Add('---- Update ECX TP Header Values in DB ----', 1);
587 cln_debug_pub.Add('---- Call ecx_tp_api.update_trading_partner ----', 1);
588 END IF;
589
590 ecx_tp_api.update_trading_partner(
591 x_return_status => l_retcode,
592 x_msg => l_retmesg,
593 p_tp_header_id => l_tp_hdr_id,
594 p_company_admin_email => '[email protected]'
595 );
596
597 IF (l_Debug_Level <= 1) THEN
598 cln_debug_pub.Add('---- Return Values From API ----', 1);
599 cln_debug_pub.Add('Return Message - '||l_retmesg, 1);
600 cln_debug_pub.Add('Return Code - '||l_retcode, 1);
601 END IF;
602
603 IF l_retcode <> 0 THEN
604 FND_MESSAGE.SET_NAME('CLN','M4U_ECXTPHDR_API_FAILED');
605 FND_MESSAGE.SET_TOKEN('PARAM','update');
606 l_msg_data := FND_MESSAGE.GET||' : '||l_retmesg;
607 RAISE FND_API.G_EXC_ERROR;
608 END IF;
609
610 IF (l_Debug_Level <= 1) THEN
611 cln_debug_pub.Add('---- ecx_tp_api.update_trading_partner returns Normal----', 1);
612 END IF;
613 END IF;
614
615 IF (l_Debug_Level <= 1) THEN
616 cln_debug_pub.Add('+++++++++ Setup ECX TP Details Values ++++++++', 1);
617 END IF;
618
619 -- setup ECX TP details
620 --1
621 M4U_SETUP_PACKAGE.add_or_update_tp_detail(l_retmesg,l_retcode,'CIN','m4u_230_cin_out','OUT',l_tp_hdr_id);
622 IF (l_Debug_Level <= 1) THEN
623 cln_debug_pub.Add('---- Return Values From API ----', 1);
624 cln_debug_pub.Add('Return Message - '||l_retmesg, 1);
625 cln_debug_pub.Add('Return Code - '||l_retcode, 1);
626 END IF;
627
628 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
629 FND_MESSAGE.SET_NAME('CLN','M4U_ECXTPDTL_FAILURE');
630 l_msg_data := FND_MESSAGE.GET||' : '||l_retmesg;
631 RAISE FND_API.G_EXC_ERROR;
632 END IF;
633
634 --2
635 M4U_SETUP_PACKAGE.add_or_update_tp_detail(l_retmesg,l_retcode,'GBREGQRY','m4u_230_gbreg_qry_out','OUT',l_tp_hdr_id);
636 IF (l_Debug_Level <= 1) THEN
637 cln_debug_pub.Add('---- Return Values From API ----', 1);
638 cln_debug_pub.Add('Return Message - '||l_retmesg, 1);
639 cln_debug_pub.Add('Return Code - '||l_retcode, 1);
640 END IF;
641
642 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
643 FND_MESSAGE.SET_NAME('CLN','M4U_ECXTPDTL_FAILURE');
644 l_msg_data := FND_MESSAGE.GET||' : '||l_retmesg;
645 RAISE FND_API.G_EXC_ERROR;
646 END IF;
647
648
649 --3
650 M4U_SETUP_PACKAGE.add_or_update_tp_detail(l_retmesg,l_retcode,'PARTYQRY','m4u_230_party_qry_out','OUT',l_tp_hdr_id);
651 IF (l_Debug_Level <= 1) THEN
652 cln_debug_pub.Add('---- Return Values From API ----', 1);
653 cln_debug_pub.Add('Return Message - '||l_retmesg, 1);
654 cln_debug_pub.Add('Return Code - '||l_retcode, 1);
655 END IF;
656
657 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
658 FND_MESSAGE.SET_NAME('CLN','M4U_ECXTPDTL_FAILURE');
659 l_msg_data := FND_MESSAGE.GET||' : '||l_retmesg;
660 RAISE FND_API.G_EXC_ERROR;
661
662 END IF;
663
664 --4
665 M4U_SETUP_PACKAGE.add_or_update_tp_detail(l_retmesg,l_retcode,'RCIR','m4u_230_rcir_out','OUT',l_tp_hdr_id);
666 IF (l_Debug_Level <= 1) THEN
667 cln_debug_pub.Add('---- Return Values From API ----', 1);
668 cln_debug_pub.Add('Return Message - '||l_retmesg, 1);
669 cln_debug_pub.Add('Return Code - '||l_retcode, 1);
670 END IF;
671
672 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
673 FND_MESSAGE.SET_NAME('CLN','M4U_ECXTPDTL_FAILURE');
674 l_msg_data := FND_MESSAGE.GET||' : '||l_retmesg;
675 RAISE FND_API.G_EXC_ERROR;
676 END IF;
677
678 --5
679 M4U_SETUP_PACKAGE.add_or_update_tp_detail(l_retmesg,l_retcode,'WLQ','m4u_230_wlq_out','OUT',l_tp_hdr_id);
680 IF (l_Debug_Level <= 1) THEN
681 cln_debug_pub.Add('---- Return Values From API ----', 1);
682 cln_debug_pub.Add('Return Message - '||l_retmesg, 1);
683 cln_debug_pub.Add('Return Code - '||l_retcode, 1);
684 END IF;
685
686 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
687 FND_MESSAGE.SET_NAME('CLN','M4U_ECXTPDTL_FAILURE');
688 l_msg_data := FND_MESSAGE.GET||' : '||l_retmesg;
689 RAISE FND_API.G_EXC_ERROR;
690 END IF;
691
692 --6
693 M4U_SETUP_PACKAGE.add_or_update_tp_detail(l_retmesg,l_retcode,'RESP_ACK','m4u_230_resp_ack_in','IN',l_tp_hdr_id);
694 IF (l_Debug_Level <= 1) THEN
695 cln_debug_pub.Add('---- Return Values From API ----', 1);
696 cln_debug_pub.Add('Return Message - '||l_retmesg, 1);
697 cln_debug_pub.Add('Return Code - '||l_retcode, 1);
698 END IF;
699
700 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
701 FND_MESSAGE.SET_NAME('CLN','M4U_ECXTPDTL_FAILURE');
702 l_msg_data := FND_MESSAGE.GET||' : '||l_retmesg;
703 RAISE FND_API.G_EXC_ERROR;
704 END IF;
705
706 --7
707 M4U_SETUP_PACKAGE.add_or_update_tp_detail(l_retmesg,l_retcode,'RESP_BATCH','m4u_230_resp_batch_in','IN',l_tp_hdr_id);
708 IF (l_Debug_Level <= 1) THEN
709 cln_debug_pub.Add('---- Return Values From API ----', 1);
710 cln_debug_pub.Add('Return Message - '||l_retmesg, 1);
711 cln_debug_pub.Add('Return Code - '||l_retcode, 1);
712 END IF;
713
714 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
715 FND_MESSAGE.SET_NAME('CLN','M4U_ECXTPDTL_FAILURE');
716 l_msg_data := FND_MESSAGE.GET||' : '||l_retmesg;
717 RAISE FND_API.G_EXC_ERROR;
718 END IF;
719
720 --8
721 M4U_SETUP_PACKAGE.add_or_update_tp_detail(l_retmesg,l_retcode,'RESP_CIC','m4u_230_resp_cic_in','IN',l_tp_hdr_id);
722 IF (l_Debug_Level <= 1) THEN
723 cln_debug_pub.Add('---- Return Values From API ----', 1);
724 cln_debug_pub.Add('Return Message - '||l_retmesg, 1);
725 cln_debug_pub.Add('Return Code - '||l_retcode, 1);
726 END IF;
727
728 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
729 FND_MESSAGE.SET_NAME('CLN','M4U_ECXTPDTL_FAILURE');
730 l_msg_data := FND_MESSAGE.GET||' : '||l_retmesg;
731 RAISE FND_API.G_EXC_ERROR;
732 END IF;
733
734 --9
735 M4U_SETUP_PACKAGE.add_or_update_tp_detail(l_retmesg,l_retcode,'RESP_GTIN','m4u_230_resp_gbregqry_in','IN',l_tp_hdr_id);
736 IF (l_Debug_Level <= 1) THEN
737 cln_debug_pub.Add('---- Return Values From API ----', 1);
738 cln_debug_pub.Add('Return Message - '||l_retmesg, 1);
739 cln_debug_pub.Add('Return Code - '||l_retcode, 1);
740 END IF;
741
742 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
743 FND_MESSAGE.SET_NAME('CLN','M4U_ECXTPDTL_FAILURE');
744 l_msg_data := FND_MESSAGE.GET||' : '||l_retmesg;
745 RAISE FND_API.G_EXC_ERROR;
746 END IF;
747
748 --10
749 add_or_update_tp_detail(l_retmesg,l_retcode,'RESP_PARTY','m4u_230_resp_partyqry_in','IN',l_tp_hdr_id);
750 IF (l_Debug_Level <= 1) THEN
751 cln_debug_pub.Add('---- Return Values From API ----', 1);
752 cln_debug_pub.Add('Return Message - '||l_retmesg, 1);
753 cln_debug_pub.Add('Return Code - '||l_retcode, 1);
754 END IF;
755
756 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
757 FND_MESSAGE.SET_NAME('CLN','M4U_ECXTPDTL_FAILURE');
758 l_msg_data := FND_MESSAGE.GET||' : '||l_retmesg;
759 RAISE FND_API.G_EXC_ERROR;
760 END IF;
761
762 --11
763 add_or_update_tp_detail(l_retmesg,l_retcode,'RESP_RFCIN','m4u_230_resp_rfcin_in','IN',l_tp_hdr_id);
764 IF (l_Debug_Level <= 1) THEN
765 cln_debug_pub.Add('---- Return Values From API ----', 1);
766 cln_debug_pub.Add('Return Message - '||l_retmesg, 1);
767 cln_debug_pub.Add('Return Code - '||l_retcode, 1);
768 END IF;
769
770 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
771 FND_MESSAGE.SET_NAME('CLN','M4U_ECXTPDTL_FAILURE');
772 l_msg_data := FND_MESSAGE.GET||' : '||l_retmesg;
773 RAISE FND_API.G_EXC_ERROR;
774 END IF;
775
776 x_retcode := 0;
777 x_errbuf := 'SUCCESS';
778
779
780 IF (l_Debug_Level <= 2) THEN
781 cln_debug_pub.Add('==========EXTING SETUP API :NORMALLY===========', 2);
782 END IF;
783
784 EXCEPTION
785 WHEN FND_API.G_EXC_ERROR THEN
786 x_retcode := 2;
787 x_errbuf := l_msg_data;
788
789 IF (l_Debug_Level <= 2) THEN
790 cln_debug_pub.Add('ERROR : '||x_errbuf, 2);
791 cln_debug_pub.Add('==========ERROR :EXTING SETUP API ===========', 2);
792 END IF;
793
794 WHEN OTHERS THEN
795 x_retcode := 2;
796 FND_MESSAGE.SET_NAME('CLN','M4U_SETUP_FAILURE');
797 -- Setup Failed
798 x_errbuf := FND_MESSAGE.GET || ' - ' ||SQLCODE||' - '||SQLERRM;
799
800 IF (l_Debug_Level <= 2) THEN
801 cln_debug_pub.Add('ERROR : '||x_errbuf, 2);
802 cln_debug_pub.Add('==========ERROR :EXTING SETUP API ===========', 2);
803 END IF;
804 END SETUP;
805
806 END M4U_SETUP_PACKAGE;