[Home] [Help]
PACKAGE BODY: APPS.ITG_SETUP
Source
4
1 PACKAGE BODY itg_setup AS
2 /* $Header: itghlocb.pls 120.3 2006/07/28 12:34:37 bsaratna noship $ */
3 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'itg_setup';
6 G_EXEC_PROC_NAME VARCHAR2(30) := 'setup';
7
8
9 -- Name
10 -- setup
11 -- Purpose
12 -- This procedure is called from a concurrent program(can be called from anywhere actually).
13 -- This procedure does the setup required for ITG
14 -- i) Setup default TP location in HR_LOCATIONS
15 -- ii) Setup XMLGateway trading partner definition
16 -- iii) Enable all the ITG triggers
17 -- HR_LOCATIONS_ALL table. This is required for the ITG XMLGateway trading partner setup.
18 -- Arguments
19 -- x_err_buf => API out result param for concurrent program calls
20 -- x_retcode => API out result param for concurrent program calls
21 -- p_location_code => Should have value 'OIPC Default TP'
22 -- p_description => Some description
23 -- p_addr_line_1 => Some address line 1
24 -- p_region_1 => Some region 1 (province)
25 -- p_region_2 => Some region 2 (State)
26 -- p_town_or_city => Some city
27 -- p_postal_code => Some postal code
28 -- Notes
29 -- All the input arguments are used in the call to setup_hr_locations
30 -- The concurrent program will be failed in case of any error
31 -- All arguments are moved into the code
32 PROCEDURE setup(
33 x_errbuf OUT NOCOPY VARCHAR2,
34 x_retcode OUT NOCOPY NUMBER
35
36 )
37 IS
38 l_retcode VARCHAR2(20);
39 l_retmesg VARCHAR2(400);
40 l_tp_hdr_id NUMBER;
41 l_location_code VARCHAR2(30);
42 l_description VARCHAR2(30);
43 l_addr_line_1 VARCHAR2(30);
44 l_country VARCHAR2(30);
45 l_style VARCHAR2(30);
46 BEGIN
47 G_EXEC_PROC_NAME := 'setup';
48
49 IF (l_Debug_Level <= 2) THEN
50 itg_debug_pub.Add('ENTERING setup' ,1);
51 END IF;
52
56 l_country := 'US';
53 l_location_code := 'OIPC Default TP';
54 l_description := 'Default XMLG TP for ITG';
55 l_addr_line_1 := 'Valhalla';
57 l_style := 'US_GLB';
58
59
60 IF (l_Debug_Level <= 1) THEN
61 itg_debug_pub.Add('------------------------------' ,1);
62 itg_debug_pub.Add('Procedure executed with params' ,1);
63 itg_debug_pub.Add('l_location_code'|| l_location_code,1);
64 itg_debug_pub.Add('l_description ' || l_description ,1);
65 itg_debug_pub.Add('l_addr_line_1 ' || l_addr_line_1 ,1);
66 itg_debug_pub.Add('l_country ' || l_country ,1);
67 itg_debug_pub.Add('l_style ' || l_style ,1);
68 itg_debug_pub.Add('------------------------------' ,1);
69 END IF;
70
71 setup_hr_loc(l_retmesg,l_retcode,l_location_code,l_description,
72 l_addr_line_1,l_country,l_style);
73
74 IF (l_Debug_Level <= 5) THEN
75 itg_debug_pub.Add('setup_hr_loc_data - ' || l_retcode || ' - ' || substr(l_retmesg,1,200), 5);
76 END IF;
77
78 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
79 x_retcode := 2;
80 x_errbuf := 'ITG Setup failed (step 1) : ' || l_retmesg;
81 RETURN;
82 END IF;
83
84 setup_ecx_tp_header(l_retmesg,l_retcode,l_tp_hdr_id,l_location_code,'[email protected]');
85
86 IF (l_Debug_Level <= 5) THEN
87 itg_debug_pub.Add('setup_ecx_tp_header - ' || l_retcode || ' - ' || substr(l_retmesg,1,200), 5);
88 itg_debug_pub.Add('setup_ecx_tp_header returns - tp_header_id - ' || l_tp_hdr_id, 5);
89 END IF;
90
91 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
92 x_retcode := 2;
93 x_errbuf := 'ITG Setup failed (step 2): ' || l_retmesg;
94 RETURN;
95 END IF;
96
97 setup_tp_details(l_retmesg,l_retcode,l_tp_hdr_id);
98
99 IF (l_Debug_Level <= 5) THEN
100 itg_debug_pub.Add('setup_tp_details - ' || l_retcode || ' - ' || substr(l_retmesg,1,200), 5);
101 END IF;
102
103 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
104 x_retcode := 2;
105 x_errbuf := 'ITG Setup failed (step 3): ' || l_retmesg;
106 RETURN;
107 END IF;
108
109 trigger_control(l_retmesg,l_retcode,true);
110
111 IF (l_Debug_Level <= 5) THEN
112 itg_debug_pub.Add('trigger_control(true) - ' || l_retcode || ' - ' || substr(l_retmesg,1,200), 5);
113 END IF;
114
115 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
116 x_retcode := 2;
117 x_errbuf := 'ITG Setup failed (step 4): ' || l_retmesg;
118 RETURN;
119 END IF;
120
121 x_retcode := 0;
122 x_errbuf := 'Successful';
123
124 IF (l_Debug_Level <= 2) THEN
125 itg_debug_pub.Add('EXITING itg_setup.setup successful', 2);
126 END IF;
127
128 -- Exception Handling
129 EXCEPTION
130 WHEN OTHERS THEN
131 x_retcode := 2;
132 set_errmesg(x_errbuf,SQLCODE,SQLERRM);
133 x_errbuf := 'ITG Setup failed : ' || x_errbuf;
134
135 IF (l_Debug_Level <= 6) THEN
136 itg_debug_pub.Add(x_errbuf, 6);
137 itg_debug_pub.Add('EXITING itg_setup.setup fails,returns on exception', 6);
138 END IF;
139
140 END;
141
142 -- Name
143 -- setup_hr_loc
144 -- Purpose
145 -- This procedure sets up the ITG default trading partner information (OIPC Default TP) in the
146 -- HR_LOCATIONS_ALL table. This is required for the ITG XMLGateway trading partner setup.
147 -- Arguments
148 -- x_err_buf => API param for concurrent program calls
149 -- x_retcode => API param for concurrent program calls
150 -- p_location_code => Should have value 'OIPC Default TP'
151 -- p_description => Some description
152 -- p_addr_line_1 => Some address line 1
153 -- p_region_1 => Some region 1 (province)
154 -- p_region_2 => Some region 2 (State)
155 -- p_town_or_city => Some city
156 -- p_postal_code => Some postal code
157 -- Notes
158 -- We really do not care what value go in here so long as a record
159 -- with location code 'OIPC Default TP' is created in Hr_Locations table
160 -- All the params input here are mandatory, to the HR APIs which create a location
161 -- Defaulting is done in the Concurrent Program definition which wraps this call
162 PROCEDURE setup_hr_loc(
163 x_errbuf OUT NOCOPY VARCHAR2,
167 p_addr_line_1 IN VARCHAR2,
164 x_retcode OUT NOCOPY VARCHAR2,
165 p_location_code IN VARCHAR2,
166 p_description IN VARCHAR2,
168 p_country IN VARCHAR2,
169 p_style IN VARCHAR2
170 )
171 IS
172 CURSOR check_hrloc_data(p_location_code VARCHAR2) IS
173 SELECT location_id, object_version_number
174 FROM hr_locations
175 WHERE location_code = p_location_code;
176 l_location_id NUMBER;
177 l_obj_ver_num NUMBER;
178 l_record_found BOOLEAN;
179 BEGIN
180 G_EXEC_PROC_NAME := 'setup_hr_loc';
181
182 IF (l_Debug_Level <= 2 ) THEN
183 itg_debug_pub.Add('ENTERING setup_hr_loc' ,1);
184 END IF;
185
186 IF (l_Debug_Level <= 1 ) THEN
187 itg_debug_pub.Add('------------------------------' ,1);
188 itg_debug_pub.Add('Procedure setup_hr_loc executed with params' ,1 );
189 itg_debug_pub.Add('p_location_code'|| p_location_code,1);
190 itg_debug_pub.Add('p_description ' || p_description ,1);
191 itg_debug_pub.Add('p_addr_line_1 ' || p_addr_line_1 ,1);
192 itg_debug_pub.Add('p_country ' || p_country ,1);
193 itg_debug_pub.Add('p_style ' || p_style ,1);
194 itg_debug_pub.Add('------------------------------' ,1);
195 END IF;
196
197 -- validate params check for non-null
198 -- if validation fails return error message and bailout.
199 IF ( p_location_code IS NULL
200 OR p_description IS NULL
201 OR p_addr_line_1 IS NULL
202 OR p_country IS NULL
203 OR p_style IS NULL) THEN
204
205 x_retcode := FND_API.G_RET_STS_ERROR;
206 -- Not using a translated message her, since this condition will never occur
207 -- the Concurrent Request submit form itself will validate that input parameters
208 -- are entered by the user.
209 x_errbuf := 'Missing mandatory parameter for itg_setup.setup_hr_loc';
210 IF (l_Debug_Level <= 6 ) THEN
211 itg_debug_pub.Add('Missing mandatory parameter for itg_setup.setup_hr_loc',6);
212 END IF;
213
214 RETURN;
215 END IF;
216
217 -- query Hr_locations to get location id for given location code
218 -- if record does not exists create a new location
219 -- else update using the location-id as key
220 -- The object_verion_number field is used for optimistic locking in the HR apis
221
222 OPEN check_hrloc_data(p_location_code);
223 FETCH check_hrloc_data INTO l_location_id, l_obj_ver_num;
224 l_record_found := check_hrloc_data%FOUND;
225
226 IF (l_Debug_Level <= 1 ) THEN
227 itg_debug_pub.Add('---------------------------------',1);
228 itg_debug_pub.Add('Cursor check_hrloc_data ',1);
229 END IF;
230
231 IF l_record_found THEN
232 IF (l_Debug_Level <= 1 ) THEN
233 itg_debug_pub.Add('l_record_found - true ' ,1);
234 END IF;
235 ELSE
236 IF (l_Debug_Level <= 1 ) THEN
237 itg_debug_pub.Add('l_record_found - false' ,1);
238 END IF;
239 END IF;
240
241 IF (l_Debug_Level <= 1 ) THEN
242 itg_debug_pub.Add('l_location_id ' || l_location_id ,1);
243 itg_debug_pub.Add('l_obj_ver_num ' || l_obj_ver_num ,1);
244 itg_debug_pub.Add('---------------------------------',1);
245 END IF;
246
247 CLOSE check_hrloc_data;
248
249 IF NOT l_record_found THEN
250 HR_LOCATION_API.create_location(
251 p_effective_date => sysdate,
252 p_language_code => userenv('LANG'),
253 p_location_code => p_location_code,
254 p_description => p_description,
255 p_address_line_1 => p_addr_line_1,
256 p_country => p_country,
257 p_style => p_style,
258 p_location_id => l_location_id,
259 p_object_version_number => l_obj_ver_num );
260
261 IF (l_Debug_Level <= 1 ) THEN
262 itg_debug_pub.Add('---------------------------------' ,1);
263 itg_debug_pub.Add('HR_LOCATION_API.create_location returns normal',1);
264 itg_debug_pub.Add('l_location_id ' || l_location_id ,1);
268 ELSE
265 itg_debug_pub.Add('l_obj_ver_num ' || l_obj_ver_num ,1);
266 itg_debug_pub.Add('---------------------------------',1);
267 END IF;
269 HR_LOCATION_API.update_location(
270 p_effective_date => sysdate,
271 p_language_code => userenv('LANG'),
272 p_style => p_style,
273 p_description => p_description,
274 p_address_line_1 => p_addr_line_1,
275 p_region_1 => NULL,
276 p_region_2 => NULL,
277 p_town_or_city => NULL,
278 p_postal_code => NULL,
279 p_country => p_country,
280 p_location_id => l_location_id,
281 p_object_version_number => l_obj_ver_num );
282
283 IF (l_Debug_Level <= 1 ) THEN
284 itg_debug_pub.Add('HR_LOCATION_API.update_location returns normal' ,1);
285 END IF;
286 END IF;
287
288 x_retcode := FND_API.G_RET_STS_SUCCESS;
289 x_errbuf := 'Successful';
290
291 IF (l_Debug_Level <= 2 ) THEN
292 itg_debug_pub.Add('EXITING itg_setup.setup_hr_loc returns normal' ,2);
293 END IF;
294
295 -- Exception Handling
296 EXCEPTION
297 WHEN OTHERS THEN
298 x_retcode := FND_API.G_RET_STS_ERROR;
299 set_errmesg(x_errbuf,SQLCODE,SQLERRM);
300
301 IF (l_Debug_Level <= 6 ) THEN
302 itg_debug_pub.Add(x_errbuf,6);
303 itg_debug_pub.Add('EXITING itg_setup.setup_hr_loc fails,returns on exception',6);
304 END IF;
305 END;
306
307 -- Name
308 -- trigger_control
309 -- Purpose
310 -- Enable or disable all the V3 Connector triggers based on the
311 -- boolean value of the p_enable argument.
312 -- Arguments
313 -- x_errbuf => API error mesg param
314 -- x_retcode => API result param
315 -- p_enable => true - enable / false - disable trigger
316 -- Notes
317 -- The trigger list here MUST track the list of triggers
318 -- created in itgoutev.sql
319 PROCEDURE trigger_control(
320 x_errbuf OUT NOCOPY VARCHAR2,
321 x_retcode OUT NOCOPY VARCHAR2,
322 p_enable BOOLEAN)
323 IS
324 TYPE trigger_list_t IS VARRAY(20) OF VARCHAR(40);
325
326 /* NOTE: The trigger list here MUST track the list of triggers
327 * created in itgoutev.sql
328 */
329 trigger_list trigger_list_t :=
330 trigger_list_t('itg_ip_requisition_headers_ARU',
331 'itg_ip_requisition_lines_ARU',
332 --'itg_ip_invoices_all_ARI',
333 'itg_ip_headers_all_ARU',
334 'itg_ip_headers_all2_ARU',
335 'itg_ip_lines_all_ARU',
336 'itg_ip_releases_all_ARU',
337 'itg_ip_releases_all2_ARU',
338 'itg_ip_line_locations_all_ARU',
339 'itg_ip_rcv_trans_interface_ASD',
340 'itg_ip_rcv_transactions_ARI');
341 i BINARY_INTEGER;
342 l_action VARCHAR2(10);
343 BEGIN
344 G_EXEC_PROC_NAME := 'trigger_control';
345
346 IF (l_Debug_Level <= 2 ) THEN
347 itg_debug_pub.Add('ENTERING trigger_control ' ,2);
348 END IF;
349
350 IF p_enable THEN
351 l_action := 'ENABLE';
352 ELSE
353 l_action := 'DISABLE';
354 END IF;
355 FOR i IN trigger_list.first .. trigger_list.last LOOP
356 BEGIN
357 EXECUTE IMMEDIATE 'ALTER TRIGGER '||trigger_list(i)||' '||l_action;
358 itg_debug_pub.Add(l_action || ' - ' || trigger_list(i));
359 EXCEPTION
360 WHEN OTHERS THEN
361 IF (l_Debug_Level <= 5 ) THEN
362 itg_debug_pub.Add('itg_setup.trigger_control(true) - ' || SQLCODE || ' - ' || SQLERRM, 5);
363 END IF;
364 END;
365 END LOOP;
366
367 x_retcode := FND_API.G_RET_STS_SUCCESS;
368 x_errbuf := 'Successful';
369
370 IF (l_Debug_Level <= 2 ) THEN
371 itg_debug_pub.Add('EXITING itg_setup.trigger_control(true) returns normal' ,2);
372 END IF;
373 EXCEPTION
374 WHEN OTHERS THEN
375 x_retcode := FND_API.G_RET_STS_ERROR;
376 set_errmesg(x_errbuf,SQLCODE,SQLERRM);
380 itg_debug_pub.Add('EXITING itg_setup.trigger_control(true) fails,returns on exception', 6);
377
378 IF (l_Debug_Level <= 6 ) THEN
379 itg_debug_pub.Add(x_errbuf ,6);
381 END IF;
382 END;
383
384 -- Name
385 -- setup_ecx_tp_header
386 -- Purpose
387 -- This procedure sets up the XMLGateway Trading Partner Setup Header block
388 -- for a given location code
389 -- Arguments
390 -- x_err_buf => API param for concurrent program calls
391 -- x_retcode => API param for concurrent program calls
392 -- p_location_code => location code for which TP setup is defined
393 -- Notes
394 -- The given location code should already be present in HR_Locations_all
395 PROCEDURE setup_ecx_tp_header(
396 x_errbuf OUT NOCOPY VARCHAR2,
397 x_retcode OUT NOCOPY VARCHAR2,
398 x_tp_hdr_id OUT NOCOPY NUMBER,
399 p_location_code IN VARCHAR2,
400 p_email_id IN VARCHAR2
401 )
402 IS
403 l_retcode VARCHAR2(30);
404 l_retmesg VARCHAR2(400);
405 l_loc_id NUMBER;
406 l_tp_hdr_id NUMBER;
407 l_found BOOLEAN;
408
409 CURSOR check_ecx_tp_hdr(p_party_type VARCHAR2,
410 p_party_id NUMBER ,
411 p_party_site_id NUMBER) IS
412 SELECT tp_header_id
413 FROM ecx_tp_headers
414 WHERE party_type = p_party_type
415 AND party_id = p_party_id
416 AND party_site_id = p_party_site_id;
417
418 BEGIN
419 G_EXEC_PROC_NAME := 'setup_ecx_tp_header';
420
421 IF (l_Debug_Level <= 2 ) THEN
422 itg_debug_pub.Add('ENTERING setup_ecx_tp_header' ,2);
423 END IF;
424
425 IF (l_Debug_Level <= 1 ) THEN
426 itg_debug_pub.Add('------------------------------' ,1);
427 itg_debug_pub.Add('Procedure setup_ecx_tp_header executed with params' ,1 );
428 itg_debug_pub.Add('p_location_code'|| p_location_code ,1);
429 itg_debug_pub.Add('------------------------------' ,1);
430 END IF;
431
432 SELECT location_id
433 INTO l_loc_id
434 FROM hr_locations_all
435 WHERE location_code = p_location_code;
436
437 IF (l_Debug_Level <= 1 ) THEN
438 itg_debug_pub.Add('Obtained location id - ' || l_loc_id,1);
439 END IF;
440 -- query ecx_tp_headers to get tp_header_id for a given trading partner setup
441 -- If no record is found create a tp header block else update it
442
443 OPEN check_ecx_tp_hdr('I',l_loc_id,l_loc_id);
444 FETCH check_ecx_tp_hdr INTO l_tp_hdr_id;
445 l_found := check_ecx_tp_hdr%FOUND;
446
447 IF (l_Debug_Level <= 1 ) THEN
448 itg_debug_pub.Add('---------------------------------',1);
449 itg_debug_pub.Add('Cursor check_ecx_tp_hdr ',1);
450 END IF;
451
452 IF l_found THEN
453 IF (l_Debug_Level <= 1 ) THEN
454 itg_debug_pub.Add('check_ecx_tp_hdr%FOUND - true ' ,1);
455 END IF;
456 ELSE
457 IF (l_Debug_Level <= 1 ) THEN
458 itg_debug_pub.Add('check_ecx_tp_hdr%FOUND - false',1);
459 END IF;
460 END IF;
461
462 IF (l_Debug_Level <= 1 ) THEN
463 itg_debug_pub.Add('l_tp_hdr_id ' || l_tp_hdr_id ,1);
464 itg_debug_pub.Add('---------------------------------',1);
465 END IF;
466
467 CLOSE check_ecx_tp_hdr;
468
469 IF NOT l_found THEN
470 ecx_tp_api.create_trading_partner(
471 x_return_status => l_retcode,
472 x_msg => l_retmesg,
473 x_tp_header_id => l_tp_hdr_id,
474 p_party_type => 'I',
475 p_party_id => l_loc_id,
476 p_party_site_id => l_loc_id ,
477 p_company_admin_email => p_email_id
478 );
479 IF (l_Debug_Level <= 1 ) THEN
480 itg_debug_pub.Add('---------------------------------',1);
481 itg_debug_pub.Add('ecx_tp_api.create_trading_partner returns normal',1);
482 itg_debug_pub.Add('x_return_status ' || l_retcode ,1);
483 itg_debug_pub.Add('x_msg ' || l_retmesg ,1);
484 itg_debug_pub.Add('x_tp_header_id ' || l_tp_hdr_id ,1);
488 -- this is an overkill for ITG
485 itg_debug_pub.Add('---------------------------------',1);
486 END IF;
487 ELSE
489 -- the email id will not be used for anything
490 ecx_tp_api.update_trading_partner(
491 x_return_status => l_retcode,
492 x_msg => l_retmesg,
493 p_tp_header_id => l_tp_hdr_id,
494 p_company_admin_email => p_email_id
495 );
496
497 IF (l_Debug_Level <= 1 ) THEN
498 itg_debug_pub.Add('---------------------------------',1);
499 itg_debug_pub.Add('ecx_tp_api.update_trading_partner returns normal',1);
500 itg_debug_pub.Add('x_return_status ' || l_retcode ,1);
501 itg_debug_pub.Add('x_msg ' || l_retmesg ,1);
502 itg_debug_pub.Add('---------------------------------' ,1);
503 END IF;
504 END IF;
505
506 IF l_retcode <> 0 THEN
507 x_tp_hdr_id := NULL;
508 x_retcode := FND_API.G_RET_STS_ERROR;
509 x_errbuf := l_retmesg;
510 ELSE
511 x_retcode := FND_API.G_RET_STS_SUCCESS;
512 x_errbuf := 'Successful';
513 x_tp_hdr_id := l_tp_hdr_id;
514 END IF;
515
516 IF (l_Debug_Level <= 1 ) THEN
517 itg_debug_pub.Add('EXITING itg_setup.setup_ecx_tp_header returns normal' ,1);
518 itg_debug_pub.Add('x_errbuf - ' || x_errbuf ,1);
519 itg_debug_pub.Add('x_retcode - ' || x_retcode ,1);
520 itg_debug_pub.Add('x_tp_hdr_id - ' || x_tp_hdr_id ,1);
521 itg_debug_pub.Add('---------------------------------' ,1);
522 END IF;
523 -- Exception Handling
524 EXCEPTION
525 WHEN OTHERS THEN
526 x_retcode := FND_API.G_RET_STS_ERROR;
527 x_tp_hdr_id := NULL;
528 set_errmesg(x_errbuf,SQLCODE,SQLERRM);
529
530 IF (l_Debug_Level <= 6 )THEN
531 itg_debug_pub.Add(x_errbuf ,6);
532 itg_debug_pub.Add('EXITING itg_setup.setup_ecx_tp_header fails,returns on exception',6);
533 END IF;
534 END;
535
536
537
538 PROCEDURE setup_tp_details(
539 x_errbuf OUT NOCOPY VARCHAR2,
540 x_retcode OUT NOCOPY VARCHAR2,
541 p_tp_hdr_id NUMBER)
542 IS
543 l_retcode VARCHAR2(20);
544 l_retmesg VARCHAR2(800);
545 l_tp_dtl_id NUMBER;
546
547 BEGIN
548 G_EXEC_PROC_NAME := 'setup_tp_details';
549
550 IF (l_Debug_Level <= 2 )THEN
551 itg_debug_pub.Add('ENTERING setup_tp_details API' ,2);
552 END IF;
553
554
555 add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
556 'ITG','SYNC_PO_RELEASE','OAG','ITG','SYNC_PO_RELEASE','OUT',
557 'itg_sync_po_release_007_out','DIRECT',null,'ITG03','129.0.0.1',
558 'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
559 IF (l_Debug_Level <= 1 )THEN
560 itg_debug_pub.Add('SYNC_PO_RELEASE - ' || l_retcode || l_retmesg, 1);
561 END IF;
562
563 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
564 x_retcode := FND_API.G_RET_STS_ERROR;
565 x_errbuf := l_retmesg;
566 RETURN;
567 END IF;
568
569 add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
570 'ITG','CANCEL_PO_RELEASE','OAG','ITG','CANCEL_PO_RELEASE','OUT',
571 'itg_cancel_po_release_006_out','DIRECT',null,'ITG03','129.0.0.1',
572 'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
573 IF (l_Debug_Level <= 1 )THEN
574 itg_debug_pub.Add('CANCEL_PO_RELEASE - ' || l_retcode || l_retmesg, 1);
575 END IF;
576
577 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
578 x_retcode := FND_API.G_RET_STS_ERROR;
579 x_errbuf := l_retmesg;
580 RETURN;
581 END IF;
582
583 add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
584 'ITG','CANCEL_PO','OAG','ITG','CANCEL_PO','OUT',
585 'itg_cancel_po_006_out','DIRECT',null,'ITG03','129.0.0.1',
586 'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
587 IF (l_Debug_Level <= 1 )THEN
588 itg_debug_pub.Add('CANCEL_PO - ' || l_retcode || l_retmesg ,1);
589 END IF;
590
591 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
592 x_retcode := FND_API.G_RET_STS_ERROR;
593 x_errbuf := l_retmesg;
597 add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
594 RETURN;
595 END IF;
596
598 'ITG','ADD_REQUISITN','OAG','ITG','ADD_REQUISITN','OUT',
599 'itg_add_requisitn_005_out','DIRECT',null,'ITG03','129.0.0.1',
600 'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
601 IF (l_Debug_Level <= 1 )THEN
602 itg_debug_pub.Add('ADD_REQUISITN - ' || l_retcode || l_retmesg, 1);
603 END IF;
604
605 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
606 x_retcode := FND_API.G_RET_STS_ERROR;
607 x_errbuf := l_retmesg;
608 RETURN;
609 END IF;
610
611 add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
612 'ITG','CHANGE_REQUISITN','OAG','ITG','CHANGE_REQUISITN','OUT',
613 'itg_change_requisitn_005_out','DIRECT',null,'ITG03','129.0.0.1',
614 'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
615 IF (l_Debug_Level <= 1 )THEN
616 itg_debug_pub.Add('CHANGE_REQUISITN - ' || l_retcode || l_retmesg, 1);
617 END IF;
618
619 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
620 x_retcode := FND_API.G_RET_STS_ERROR;
621 x_errbuf := l_retmesg;
622 RETURN;
623 END IF;
624
625 add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
626 'ITG','CANCEL_REQUISITN','OAG','ITG','CANCEL_REQUISITN','OUT',
627 'itg_cancel_requisitn_005','DIRECT',null,'ITG03','129.0.0.1',
628 'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
629 IF (l_Debug_Level <= 1 )THEN
630 itg_debug_pub.Add('CANCEL_REQUISITN - ' || l_retcode || l_retmesg, 1);
631 END IF;
632
633 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
634 x_retcode := FND_API.G_RET_STS_ERROR;
635 x_errbuf := l_retmesg;
636 RETURN;
637 END IF;
638
639 add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
640 'ITG','SYNC_PO','OAG','ITG','SYNC_PO','OUT',
641 'itg_sync_po_007_out','DIRECT',null,'ITG03','129.0.0.1',
642 'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
643 IF (l_Debug_Level <= 1 )THEN
644 itg_debug_pub.Add('SYNC_PO - ' || l_retcode || l_retmesg , 1);
645 END IF;
646
647 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
648 x_retcode := FND_API.G_RET_STS_ERROR;
649 x_errbuf := l_retmesg;
650 RETURN;
651 END IF;
652
653 add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
654 'ITG','UPDATE_DELIVERY','OAG','ITG','UPDATE_DELIVERY','OUT',
655 'itg_update_delivery_005','DIRECT',null,'ITG03','129.0.0.1',
656 'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
657 IF (l_Debug_Level <= 1 )THEN
658 itg_debug_pub.Add('UPDATE_DELIVERY - ' || l_retcode || l_retmesg, 1);
659 END IF;
660
661 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
662 x_retcode := FND_API.G_RET_STS_ERROR;
663 x_errbuf := l_retmesg;
664 RETURN;
665 END IF;
666
667 add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
668 'ITG','LOAD_PLINVOICE','OAG','ITG','LOAD_PLINVOICE','OUT',
669 'itg_load_plinvoice_004','DIRECT',null,'ITG03','129.0.0.1',
670 'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
671
672 IF (l_Debug_Level <= 1 )THEN
673 itg_debug_pub.Add('UPDATE_DELIVERY - ' || l_retcode || l_retmesg, 1);
674 END IF;
675
676 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
677 x_retcode := FND_API.G_RET_STS_ERROR;
678 x_errbuf := l_retmesg;
679 RETURN;
680 END IF;
681
682 add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
683 'ECX','CBODO','OAG','BOD','CONFIRM','OUT',
684 'ECX_CBODO_OAG72_OUT_CONFIRM','DIRECT',null,'ITG03','129.0.0.1',
685 'itg','welcome',null,'ITG03',null,2,p_tp_hdr_id,'I');
686 IF (l_Debug_Level <= 1 )THEN
687 itg_debug_pub.Add('CBODO - ' || l_retcode || l_retmesg , 1);
688 END IF;
689
690 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
691 x_retcode := FND_API.G_RET_STS_ERROR;
692 x_errbuf := l_retmesg;
693 RETURN;
694 END IF;
695
696 add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
697 'ITG','SYNC_FIELD_004','OAG','ITG','SYNC_FIELD_004','IN',
698 'itg_sync_field_004_in',null,null,null,null,
702 END IF;
699 null,null,null,'ITG03',null,0,p_tp_hdr_id,'I');
700 IF (l_Debug_Level <= 1 )THEN
701 itg_debug_pub.Add('SYNC_FIELD_004 IN- ' || l_retcode || l_retmesg, 1);
703
704 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
705 x_retcode := FND_API.G_RET_STS_ERROR;
706 x_errbuf := l_retmesg;
707 RETURN;
708 END IF;
709
710 add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
711 'ITG','SYNC_UOMGROUP_003','OAG','ITG','SYNC_UOMGROUP_003','IN',
712 'itg_sync_uomgroup_003_in',null,null,null,null,
713 null,null,null,'ITG03',null,2,p_tp_hdr_id,'I');
714
715 IF (l_Debug_Level <= 1 )THEN
716 itg_debug_pub.Add('SYNC_UOMGROUP_003 IN- ' || l_retcode || l_retmesg, 1);
717 END IF;
718
719 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
720 x_retcode := FND_API.G_RET_STS_ERROR;
721 x_errbuf := l_retmesg;
722 RETURN;
723 END IF;
724
725 add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
726 'ITG','SYNC_SUPPLIER_005','OAG','ITG','SYNC_SUPPLIER_005','IN',
727 'itg_sync_supplier_005_in',null,null,null,null,
728 null,null,null,'ITG03',null,2,p_tp_hdr_id,'I');
729 IF (l_Debug_Level <= 1 )THEN
730 itg_debug_pub.Add('SYNC_SUPPLIER_005 IN- ' || l_retcode || l_retmesg ,1);
731 END IF;
732
733 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
734 x_retcode := FND_API.G_RET_STS_ERROR;
735 x_errbuf := l_retmesg;
736 RETURN;
737 END IF;
738
739 add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
740 'ITG','SYNC_EXCHNGRATE_003','OAG','ITG','SYNC_EXCHNGRATE_003','IN',
741 'itg_sync_exchngrate_003_in',null,null,null,null,
742 null,null,null,'ITG03',null,2,p_tp_hdr_id,'I');
743 IF (l_Debug_Level <= 1 )THEN
744 itg_debug_pub.Add('SSYNC_EXCHNGRATE_003 IN- ' || l_retcode || l_retmesg, 1);
745 END IF;
746
747 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
748 x_retcode := FND_API.G_RET_STS_ERROR;
749 x_errbuf := l_retmesg;
750 RETURN;
751 END IF;
752
753 add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
754 'ITG','SYNC_ITEM_006','OAG','ITG','SYNC_ITEM_006','IN',
755 'itg_Sync_Item_006_in',null,null,null,null,
756 null,null,null,'ITG03',null,2,p_tp_hdr_id,'I');
757 IF (l_Debug_Level <= 1 )THEN
758 itg_debug_pub.Add('SYNC_ITEM_006 IN- ' || l_retcode || l_retmesg, 1);
759 END IF;
760
761 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
762 x_retcode := FND_API.G_RET_STS_ERROR;
763 x_errbuf := l_retmesg;
764 RETURN;
765 END IF;
766
767 add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
768 'ITG','SYNC_COA_003','OAG','ITG','SYNC_COA_003','IN',
769 'itg_sync_coa_003_in',null,null,null,null,
770 null,null,null,'ITG03',null,2,p_tp_hdr_id,'I');
771 IF (l_Debug_Level <= 1 )THEN
772 itg_debug_pub.Add('SYNC_COA_003 IN- ' || l_retcode || l_retmesg ,1);
773 END IF;
774
775 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
776 x_retcode := FND_API.G_RET_STS_ERROR;
777 x_errbuf := l_retmesg;
778 RETURN;
779 END IF;
780
781 add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
782 'ITG','SYNC_PO','OAG','ITG','SYNC_PO_007','IN',
783 'ITG_sync_po_007_in',null,null,null,null,
784 null,null,null,'ITG03',null,2,p_tp_hdr_id,'I');
785
786 IF (l_Debug_Level <= 1 )THEN
787 itg_debug_pub.Add('SYNC_PO IN- ' || l_retcode || l_retmesg, 1);
788 END IF;
789
790 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
791 x_retcode := FND_API.G_RET_STS_ERROR;
792 x_errbuf := l_retmesg;
793 RETURN;
794 END IF;
795
796 add_or_update_tp_detail(l_retmesg,l_retcode,l_tp_dtl_id,
797 'CLN','NBOD','OAG','CLN','NBOD','IN',
798 'CLN_NBODI_OAG72_IN_CONFIRM',null,null,null,null,
799 null,null,null,'ITG03',null,0,p_tp_hdr_id,'I');
800 IF (l_Debug_Level <= 1 )THEN
801 itg_debug_pub.Add('NBOD IN- ' || l_retcode || l_retmesg, 1);
802 END IF;
803
804 IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
805 x_retcode := FND_API.G_RET_STS_ERROR;
806 x_errbuf := l_retmesg;
807 RETURN;
811 x_errbuf := 'Successful';
808 END IF;
809
810 x_retcode := FND_API.G_RET_STS_SUCCESS;
812
813
814 IF (l_Debug_Level <= 2 )THEN
815 itg_debug_pub.Add(' EXITING itg_setup.setup_tp_details normal', 2);
816 END IF;
817 EXCEPTION
818 WHEN OTHERS THEN
819 x_retcode := FND_API.G_RET_STS_ERROR;
820 set_errmesg(x_errbuf,SQLCODE,SQLERRM);
821
822 IF (l_Debug_Level <= 6 )THEN
823 itg_debug_pub.Add(x_errbuf, 6);
824 itg_debug_pub.Add('EXITING itg_setup.setup_tp_details fails,returns on exception',6);
825 END IF;
826 END;
827
828 -- Name
829 -- add_or_update_tp_detail
830 -- Purpose
831 -- This procedure sets up the XMLGateway Trading Partner Setup detail
832 -- for a single transaction based on the params
833 -- If detail record is present it updates else inserts
834 -- Arguments
835 -- x_err_buf => API param for concurrent program calls
836 -- x_retcode => API param for concurrent program calls
837 -- <paramlist> => corresponds to the ecx_tp_api, nothing to talk abt
838 -- Notes
839 -- none
840 PROCEDURE add_or_update_tp_detail(
841 x_errbuf OUT NOCOPY VARCHAR2,
842 x_retcode OUT NOCOPY VARCHAR2,
843 x_tp_dtl_id NUMBER,
844 p_txn_type VARCHAR2,
845 p_txn_subtype VARCHAR2,
846 p_std_code VARCHAR2,
847 p_ext_type VARCHAR2,
848 p_ext_subtype VARCHAR2,
849 p_direction VARCHAR2,
850 p_map VARCHAR2,
851 p_conn_type VARCHAR2,
852 p_hub_user_id NUMBER,
853 p_protocol VARCHAR2,
854 p_protocol_addr VARCHAR2,
855 p_user VARCHAR2,
856 p_passwd VARCHAR2,
857 p_routing_id NUMBER,
858 p_src_loc VARCHAR2,
859 p_ext_loc VARCHAR2,
860 p_doc_conf NUMBER,
861 p_tp_hdr_id NUMBER,
862 p_party_type VARCHAR2
863
864 )
865 IS
866 CURSOR get_tp_detail_id(
867 p_tp_hdr_id VARCHAR2,
868 p_standard_code VARCHAR2,
869 p_ext_type VARCHAR2,
870 p_ext_subtype VARCHAR2,
871 p_direction VARCHAR2,
872 p_txn_type VARCHAR2,
873 p_txn_subtype VARCHAR2
874 )IS
875 SELECT tp_detail_id
876 FROM
877 ecx_tp_details tpd,
878 ecx_tp_headers tph,
879 ecx_ext_processes extp,
880 ecx_transactions txn,
881 ecx_standards svl
882 WHERE 1=1
883 AND tph.tp_header_id = tpd.tp_header_id
884 AND tpd.ext_process_id = extp.ext_process_id
885 AND extp.transaction_id = txn.transaction_id
886 AND extp.standard_id = svl.standard_id
887 AND svl.standard_code = p_std_code
888 AND extp.ext_type = p_ext_type
889 AND extp.ext_subtype = p_ext_subtype
890 AND extp.direction = p_direction
891 AND txn.transaction_type = p_txn_type
892 AND txn.transaction_subtype = p_txn_subtype
893 AND tph.tp_header_id = p_tp_hdr_id;
894
895 l_tp_dtl_id NUMBER;
896 l_ext_process_id NUMBER;
897 l_record_found BOOLEAN;
898 l_retcode NUMBER;
899 l_count NUMBER := 0;
900 l_retmesg VARCHAR2(400);
901 BEGIN
902
903 G_EXEC_PROC_NAME := 'add_or_update_tp_detail';
904
905 IF (l_Debug_Level <= 2 )THEN
906 itg_debug_pub.Add('ENTERING add_or_update_tp_detail API' ,2);
907 END IF;
908
909 IF (l_Debug_Level <= 1) THEN
910 itg_debug_pub.Add('------------------------------' ,1);
911 itg_debug_pub.Add('Procedure add_or_update_tp_detail with params' ,1 );
912 itg_debug_pub.Add('p_txn_type ' || p_txn_type ,1);
916 itg_debug_pub.Add('p_ext_subtype ' || p_ext_subtype ,1);
913 itg_debug_pub.Add('p_txn_subtype ' || p_txn_subtype ,1);
914 itg_debug_pub.Add('p_std_code ' || p_std_code ,1);
915 itg_debug_pub.Add('p_ext_type ' || p_ext_type ,1);
917 itg_debug_pub.Add('p_direction ' || p_direction ,1);
918 itg_debug_pub.Add('p_map ' || p_map ,1);
919 itg_debug_pub.Add('p_conn_type ' || p_conn_type ,1);
920 itg_debug_pub.Add('p_hub_user_id ' || p_hub_user_id ,1);
921 itg_debug_pub.Add('p_protocol ' || p_protocol ,1);
922 itg_debug_pub.Add('p_protocol_addr' || p_protocol_addr,1);
923 itg_debug_pub.Add('p_user ' || p_user ,1);
924 itg_debug_pub.Add('p_passwd ' || p_passwd ,1);
925 itg_debug_pub.Add('p_routing_id ' || p_routing_id ,1);
926 itg_debug_pub.Add('p_src_loc ' || p_src_loc ,1);
927 itg_debug_pub.Add('p_ext_loc ' || p_ext_loc ,1);
928 itg_debug_pub.Add('p_doc_conf ' || p_doc_conf ,1);
929 itg_debug_pub.Add('p_tp_hdr_id ' || p_tp_hdr_id ,1);
930 itg_debug_pub.Add('p_tparty_type ' || p_party_type ,1);
931 itg_debug_pub.Add('------------------------------' ,1);
932 END IF;
933
934 -- query ecx_tp_detail_id to get ecx_tp_detail_id for given location code
935 -- if record does not exists create a new detail record
936 -- else update the detail record
937 OPEN get_tp_detail_id(p_tp_hdr_id, p_std_code,p_ext_type,p_ext_subtype,
938 p_direction,p_txn_type,p_txn_subtype);
939
940 FETCH get_tp_detail_id INTO l_tp_dtl_id;
941 l_record_found := get_tp_detail_id%FOUND;
942
943 IF (l_Debug_Level <= 1) THEN
944 itg_debug_pub.Add('---------------------------------', 1);
945 itg_debug_pub.Add('Cursor get_tp_detail_id ', 1);
946 END IF;
947
948 IF l_record_found THEN
949 IF (l_Debug_Level <= 1) THEN
950 itg_debug_pub.Add('l_record_found - true ', 1);
951 END IF;
952 ELSE
953 IF (l_Debug_Level <= 1) THEN
954 itg_debug_pub.Add('l_record_found - false', 1);
955 END IF;
956 END IF;
957
958 IF (l_Debug_Level <= 1) THEN
959 itg_debug_pub.Add('l_tp_dtl_id ' || l_tp_dtl_id ,1);
960 itg_debug_pub.Add('---------------------------------',1);
961 END IF;
962
963 CLOSE get_tp_detail_id;
964
965 BEGIN
966 SELECT extp.ext_process_id
967 INTO l_ext_process_id
968 FROM ecx_ext_processes extp,
969 ecx_transactions txn,
970 ecx_standards svl
971 WHERE 1=1
972 AND extp.transaction_id = txn.transaction_id
973 AND extp.standard_id = svl.standard_id
974 AND svl.standard_code = p_std_code
975 AND extp.ext_type = p_ext_type
976 AND extp.ext_subtype = p_ext_subtype
977 AND extp.direction = p_direction
978 AND txn.party_type = p_party_type
979 AND txn.transaction_type = p_txn_type
980 AND txn.transaction_subtype = p_txn_subtype;
981
982 IF (l_Debug_Level <= 1) THEN
983 itg_debug_pub.Add('ext_process_id - ' || l_ext_process_id ,1);
984 END IF;
985 EXCEPTION
986 WHEN OTHERS THEN
987 IF (l_Debug_Level <= 6) THEN
988 itg_debug_pub.Add('Cannot retrieve ext_process_id', 6);
989 itg_debug_pub.Add('Exception - ' || SQLCODE || ' - ' || SQLERRM, 6);
990 END IF;
991
992 x_retcode := FND_API.G_RET_STS_ERROR;
993 set_errmesg(x_errbuf,SQLCODE,SQLERRM);
994 RETURN;
995 END;
996
997 BEGIN
998 SELECT count(*)
999 INTO l_count
1000 FROM ecx_ext_processes extp,
1001 ecx_tp_details tpd,
1002 ecx_standards svl
1003 WHERE 1=1
1004 AND extp.standard_id = svl.standard_id
1005 AND svl.standard_code = p_std_code
1006 AND extp.ext_process_id = tpd.ext_process_id
1007 AND extp.ext_type = p_ext_type
1008 AND extp.ext_subtype = p_ext_subtype
1009 AND extp.direction = p_direction
1010 AND tpd.source_tp_location_code = p_src_loc
1014 x_retcode := FND_API.G_RET_STS_ERROR;
1011 AND tpd.tp_header_id <> p_tp_hdr_id;
1012
1013 IF l_count > 0 THEN
1015 x_errbuf := 'Found ECX TP Detail record for (' || p_std_code
1016 || ',' || p_ext_type || ',' || p_ext_subtype
1017 || ',' || p_direction || ',' || ' source location '|| p_src_loc
1018 || '), please delete this record and resubmit the Setup program.';
1019 RETURN;
1020 END IF;
1021
1022 IF (l_Debug_Level <= 1) THEN
1023 itg_debug_pub.Add('ext_process_id - ' || l_ext_process_id);
1024 END IF;
1025 EXCEPTION
1026 WHEN OTHERS THEN
1027 IF (l_Debug_Level <= 6) THEN
1028 itg_debug_pub.Add('Unexpected error', 6);
1029 itg_debug_pub.Add('Exception - ' || SQLCODE || ' - ' || SQLERRM, 6);
1030 END IF;
1031
1032 x_retcode := FND_API.G_RET_STS_ERROR;
1033 set_errmesg(x_errbuf,SQLCODE,SQLERRM);
1034 RETURN;
1035 END;
1036
1037 IF NOT l_record_found THEN
1038 ecx_tp_api.create_tp_detail(
1039 x_return_status => l_retcode,
1040 x_msg => l_retmesg,
1041 x_tp_detail_id => l_tp_dtl_id,
1042 p_tp_header_id => p_tp_hdr_id,
1043 p_ext_process_id => l_ext_process_id,
1044 p_map_code => p_map,
1045 p_connection_type => p_conn_type,
1046 p_hub_user_id => p_hub_user_id,
1047 p_protocol_type => p_protocol,
1048 p_protocol_address => p_protocol_addr,
1049 p_username => p_user,
1050 p_password => p_passwd,
1051 p_routing_id => p_routing_id,
1052 p_source_tp_location_code => p_src_loc,
1053 p_external_tp_location_code => p_ext_loc,
1054 p_confirmation => p_doc_conf
1055 );
1056
1057 IF (l_Debug_Level <= 1) THEN
1058 itg_debug_pub.Add('----------------------------' ,1);
1059 itg_debug_pub.Add('ecx_tp_api.create_tp_detail returns normal',1);
1060 itg_debug_pub.Add('l_retcode ' || l_retcode ,1);
1061 itg_debug_pub.Add('l_retmesg ' || l_retmesg ,1);
1062 itg_debug_pub.Add('l_tp_dtl_id ' || l_tp_dtl_id ,1);
1063 itg_debug_pub.Add('----------------------------',1);
1064 END IF;
1065 ELSE
1066 ecx_tp_api.update_tp_detail(
1067 x_return_status => l_retcode,
1068 x_msg => l_retmesg,
1069 p_tp_detail_id => l_tp_dtl_id,
1070 p_map_code => p_map,
1071 p_ext_process_id => l_ext_process_id,
1072 p_connection_type => p_conn_type,
1073 p_hub_user_id => p_hub_user_id,
1074 p_protocol_type => p_protocol,
1075 p_protocol_address => p_protocol_addr,
1076 p_username => p_user,
1077 p_password => p_passwd,
1078 p_routing_id => p_routing_id,
1079 p_source_tp_location_code => p_src_loc,
1080 p_external_tp_location_code => p_ext_loc,
1081 p_confirmation => p_doc_conf,
1082 p_passupd_flag => 'Y'
1083 );
1084
1085
1086 IF (l_Debug_Level <= 1) THEN
1087 itg_debug_pub.Add('----------------------------' ,1);
1088 itg_debug_pub.Add('ecx_tp_api.update_tp_detail returns normal',1);
1089 itg_debug_pub.Add('l_retcode ' || l_retcode ,1);
1090 itg_debug_pub.Add('l_retmesg ' || l_retmesg ,1);
1091 itg_debug_pub.Add('----------------------------',1);
1092 END IF;
1093 END IF;
1094
1095 IF l_retcode = 0 THEN
1096 x_retcode := FND_API.G_RET_STS_SUCCESS;
1097 x_errbuf := 'Successful';
1098 ELSE
1099 x_retcode := FND_API.G_RET_STS_ERROR;
1100 x_errbuf := l_retmesg;
1101 END IF;
1102
1103 IF (l_Debug_Level <= 2) THEN
1104 itg_debug_pub.Add('EXITING itg_setup.add_or_update_tp_detail returns normal', 2);
1105 END IF;
1106 -- Exception Handling
1107 EXCEPTION
1108 WHEN OTHERS THEN
1109 x_retcode := FND_API.G_RET_STS_ERROR;
1110 set_errmesg(x_errbuf,SQLCODE,SQLERRM);
1111
1112 IF (l_Debug_Level <= 6) THEN
1113 itg_debug_pub.Add(x_errbuf, 6);
1114 itg_debug_pub.Add('EXITING itg_setup.add_or_update_tp_detail fails,returns on exception',6);
1115 END IF;
1116 END;
1117
1118
1119
1120 -- Name
1121 -- set_errmesg
1122 -- Purpose
1123 -- Helper routine, wraps FND_MESSAGE API call
1124 -- Arguments
1125 -- x_err_buf => FND message containing error with context info
1126 -- p_errcode => Error code
1127 -- p_errmesg => Error message
1128 -- Notes
1129 -- None
1130 PROCEDURE set_errmesg( x_errbuf OUT NOCOPY VARCHAR2,
1131 p_errcode IN VARCHAR2,
1132 p_errmesg IN VARCHAR2)
1133 IS
1134 l_errmesg VARCHAR2(250);
1135 BEGIN
1136 IF (l_Debug_Level <= 2) THEN
1137 itg_debug_pub.Add('ENTERING set_errmesg', 2);
1138 END IF;
1139
1140 l_errmesg := SUBSTR(p_errmesg,1,200);
1141
1142 IF p_errcode IS NOT NULL THEN
1143 l_errmesg := p_errcode || ' - ' || l_errmesg;
1144 END IF;
1145
1146 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
1147 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_errmesg);
1148 FND_MESSAGE.SET_TOKEN('PKG_NAME',G_PKG_NAME);
1149 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME',G_EXEC_PROC_NAME);
1150 x_errbuf := FND_MESSAGE.GET;
1151
1152 IF (l_Debug_Level <= 2) THEN
1153 itg_debug_pub.Add('EXITING set_errmesg - ' || x_errbuf, 2);
1154 END IF;
1155
1156 EXCEPTION
1157 WHEN OTHERS THEN
1158 IF (l_Debug_Level <= 6) THEN
1159 itg_debug_pub.Add('EXITING set_errmesg - ' || SQLCODE || ' - ' || SQLERRM,6);
1160 END IF;
1161 x_errbuf := p_errcode || ' - ' || p_errmesg;
1162 END;
1163
1164 END itg_setup;