[Home] [Help]
PACKAGE BODY: APPS.ECX_TP_API
Source
1 PACKAGE BODY ECX_TP_API AS
2 -- $Header: ECXTPXAB.pls 120.5 2006/10/11 05:23:47 gsingh ship $
3
4
5 Procedure retrieve_trading_partner(
6 x_return_status OUT NOCOPY Pls_integer,
7 x_msg OUT NOCOPY Varchar2,
8 x_tp_header_id OUT NOCOPY Pls_integer,
9 p_party_type IN Varchar2,
10 p_party_id IN Varchar2,
11 p_party_site_id IN Varchar2,
12 x_company_admin_email OUT NOCOPY Varchar2,
13 x_created_by OUT NOCOPY Varchar2,
14 x_creation_date OUT NOCOPY Varchar2,
15 x_last_updated_by OUT NOCOPY Varchar2,
16 x_last_update_date OUT NOCOPY Varchar2
17 ) IS
18 Begin
19 x_return_status := ECX_UTIL_API.G_NO_ERROR;
20 x_msg := null;
21 x_tp_header_id := -1;
22 -- make sure party_id, party_type, party_site_id are not null.
23 If (p_party_type is null) then
24 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
25 x_msg := ecx_debug.getTranslatedMessage('ECX_PARTY_TYPE_NOT_NULL',
26 'p_party_type',p_party_type);
27 return;
28 elsif
29 (p_party_id is null) then
30 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
31 x_msg := ecx_debug.getTranslatedMessage('ECX_PARTY_ID_NOT_NULL',
32 'p_party_id',p_party_id);
33 return;
34 elsif
35 (p_party_site_id is null ) then
36 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
37 x_msg := ecx_debug.getTranslatedMessage('ECX_PARTY_SITE_ID_NOT_NULL',
38 'p_party_site_id',p_party_site_id);
39 return;
40 end if;
41
42 -- make sure p_party_type has a valid value.
43 If not(ECX_UTIL_API.validate_party_type(p_party_type)) Then
44 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
45 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_PARTY_TYPE',
46 'p_party_type',p_party_type);
47 return;
48 end if;
49
50 -- select data from ECX_TP_HEADERS.
51 Select
52 TP_HEADER_ID,
53 COMPANY_ADMIN_EMAIL,
54 LAST_UPDATED_BY,
55 LAST_UPDATE_DATE,
56 CREATED_BY,
57 CREATION_DATE
58 into
59 x_tp_header_id,
60 x_company_admin_email,
61 x_last_updated_by,
62 x_last_update_date,
63 x_created_by,
64 x_creation_date
65 from
66 ECX_TP_HEADERS
67 where party_type = p_party_type
68 and party_id = p_party_id
69 and party_site_id = p_party_site_id;
70
71 Exception
72 when no_data_found then
73 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
74 x_msg := ecx_debug.getTranslatedMessage('ECX_TP_HDR_NOT_FOUND',
75 'p_party_type',p_party_type,
76 'p_party_id', p_party_id ,
77 'p_party_site_id', p_party_site_id);
78
79 when too_many_rows then
80 x_return_status := ECX_UTIL_API.G_TOO_MANY_ROWS;
81 x_msg := ecx_debug.getTranslatedMessage('ECX_TP_HDR_TOO_MANY_ROWS',
82 'p_party_type',p_party_type,
83 'p_party_id', p_party_id ,
84 'p_party_site_id', p_party_site_id);
85 when others then
86 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
87 x_msg := SQLERRM;
88 End;
89
90 Procedure create_trading_partner(
91 x_return_status OUT NOCOPY Pls_integer,
92 x_msg OUT NOCOPY Varchar2,
93 x_tp_header_id OUT NOCOPY Pls_integer,
94 p_party_type IN Varchar2,
95 p_party_id IN Varchar2,
96 p_party_site_id IN Varchar2,
97 p_company_admin_email IN Varchar2
98 ) IS
99
100 l_ret_code pls_integer := ECX_UTIL_API.G_NO_ERROR;
101 l_ret_msg varchar2(2000) := null;
102 l_event_name varchar2(250) := null;
103 l_event_key number := -1;
104
105 cursor c_tp_hdr_id is
106 select ecx_tp_headers_s.nextval
107 from dual;
108
109 Begin
110
111 x_return_status := ECX_UTIL_API.G_NO_ERROR;
112 x_msg := null;
113 x_tp_header_id := -1;
114
115 -- make sure party_id, party_type, party_site_id and p_company_admin_email are not null.
116 If (p_party_type is null) then
117 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
118 x_msg := ecx_debug.getTranslatedMessage('ECX_PARTY_TYPE_NOT_NULL',
119 'p_party_type',p_party_type);
120 return;
121 elsif
122 (p_party_id is null) then
123 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
124 x_msg := ecx_debug.getTranslatedMessage('ECX_PARTY_ID_NOT_NULL',
125 'p_party_id',p_party_id);
126 return;
127 elsif
128 (p_party_site_id is null) then
129 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
130 x_msg := ecx_debug.getTranslatedMessage('ECX_PARTY_SITE_ID_NOT_NULL',
131 'p_party_site_id',p_party_site_id);
132 return;
133 elsif
134 (p_company_admin_email is null) then
135 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
136 x_msg := ecx_debug.getTranslatedMessage('ECX_EMAIL_ADDRESS_NOT_NULL',
137 'p_email_address',p_company_admin_email);
138 return;
139
140 end if;
141
142 -- make sure p_party_type has a valid value.
143
144 If not (ECX_UTIL_API.validate_party_type(p_party_type)) Then
145 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
146 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_PARTY_TYPE',
147 'p_party_type',p_party_type);
148 return;
149 end if;
150
151 If not(ECX_UTIL_API.validate_party_id(p_party_type,p_party_id)) Then
152 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
153 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_PARTY_ID',
154 'p_party_id',p_party_id);
155 return;
156 End If;
157
158 If p_party_type <> 'E' Then
159 If not(ECX_UTIL_API.validate_party_site_id(p_party_type, p_party_id,p_party_site_id)) Then
160 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
161 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_PARTY_SITE_ID',
162 'p_party_site_id',p_party_site_id);
163 return;
164 End If;
165 End If;
166
167
168 if not(ECX_UTIL_API.validate_email_address(p_company_admin_email)) Then
169 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
170 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_EMAIL_ADDRESS',
171 'p_email_address', p_company_admin_email);
172 return;
173 end if;
174
175 open c_tp_hdr_id;
176 fetch c_tp_hdr_id into x_tp_header_id;
177 close c_tp_hdr_id;
178
179 -- Insert data into ECX_TP_HEADERS.
180 insert into ECX_TP_HEADERS(
181 TP_HEADER_ID,
182 PARTY_TYPE,
183 PARTY_ID,
184 PARTY_SITE_ID,
185 COMPANY_ADMIN_EMAIL,
186 LAST_UPDATED_BY,
187 LAST_UPDATE_DATE,
188 CREATED_BY,
189 CREATION_DATE)
190 values (
191 x_tp_header_id,
192 p_party_type,
193 p_party_id,
194 p_party_site_id,
195 p_company_admin_email,
196 0,
197 sysdate,
198 0,
199 sysdate
200 ) ;
201
202 /* WFDS changes */
203
204 raise_tp_event(
205 x_return_status => l_ret_code,
206 x_msg => l_ret_msg,
207 x_event_name => l_event_name,
208 x_event_key => l_event_key,
209 p_mod_type => 'CREATE',
210 p_tp_header_id => x_tp_header_id,
211 p_party_type => p_party_type,
212 p_party_id => p_party_id,
213 p_party_site_id => p_party_site_id,
214 p_company_email_addr => p_company_admin_email);
215
216 if NOT(l_ret_code = ECX_UTIL_API.G_NO_ERROR) then
217 raise ecx_tp_api.tp_event_not_raised;
218 end if;
219
220 Exception
221 when dup_val_on_index then
222 x_tp_header_id := -1;
223 x_return_status := ECX_UTIL_API.G_DUP_ERROR;
224 x_msg := ecx_debug.gettranslatedMessage('ECX_TP_HDR_EXISTS',
225 'p_party_type', p_party_type,
226 'p_party_id', p_party_id,
227 'p_party_site_id', p_party_site_id);
228 when ecx_tp_api.tp_event_not_raised then
229 x_tp_header_id := -1;
230 x_return_status := l_ret_code;
231 x_msg := l_ret_msg;
232 when others then
233 x_tp_header_id := -1;
234 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
235 x_msg := SQLERRM;
236 End;
237
238 Procedure update_trading_partner(
239 x_return_status OUT NOCOPY Pls_integer,
240 x_msg OUT NOCOPY Varchar2,
241 p_tp_header_id IN Pls_integer,
242 p_company_admin_email IN Varchar2
243 ) IS
244
245 l_ret_code pls_integer := ECX_UTIL_API.G_NO_ERROR;
246 l_ret_msg varchar2(2000) := null;
247 l_event_name varchar2(250) := null;
248 l_event_key number := -1;
249
250 l_party_type ecx_tp_headers.party_type%type;
251 l_party_id number;
252 l_party_site_id number;
253
254 Begin
255 x_return_status := ECX_UTIL_API.G_NO_ERROR;
256 x_msg := null;
257
258 -- make sure p_tp_header_id is not null.
259 If (p_tp_header_id is null) then
260 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
261 x_msg := ecx_debug.getTranslatedMessage('ECX_TP_HDR_ID_NOT_NULL');
262 return;
263 end if;
264
265 if not(ECX_UTIL_API.validate_email_address(p_company_admin_email)) Then
266 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
267 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_EMAIL_ADDRESS',
268 'p_email_address',p_company_admin_email);
269 return;
270 end if;
271
272 if NOT (ecx_util_api.validate_trading_partner(p_tp_header_id))
273 then
274 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
275 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_TP_HDR_ID',
276 'p_tp_header_id', p_tp_header_id);
277 return;
278 end if;
279
280 -- update company_admin_email in ECX_TP_HEADERS.
281 Update ECX_TP_HEADERS set
282 COMPANY_ADMIN_EMAIL = p_company_admin_email,
283 LAST_UPDATED_BY = 0,
284 LAST_UPDATE_DATE = sysdate
285 where tp_header_id = p_tp_header_id;
286
287 if (sql%rowcount = 0) then
288 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
289 x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
290 'p_table', 'ecx_tp_headers',
291 'p_param_name', 'Trading partner header ID',
292 'p_param_id', p_tp_header_id);
293 return;
294 elsif (sql%rowcount > 0) then
295 select party_type,
296 party_id,
297 party_site_id
298 into l_party_type,
299 l_party_id,
300 l_party_site_id
301 from ecx_tp_headers
302 where tp_header_id = p_tp_header_id;
303
304 raise_tp_event(
305 x_return_status => l_ret_code,
306 x_msg => l_ret_msg,
307 x_event_name => l_event_name,
308 x_event_key => l_event_key,
309 p_mod_type => 'UPDATE',
310 p_tp_header_id => p_tp_header_id,
311 p_party_type => l_party_type,
312 p_party_id => l_party_id,
313 p_party_site_id => l_party_site_id,
314 p_company_email_addr => p_company_admin_email);
315
316 if NOT(l_ret_code = ECX_UTIL_API.G_NO_ERROR) then
317 raise ecx_tp_api.tp_event_not_raised;
318 end if;
319 end if;
320
321 exception
322 when ecx_tp_api.tp_event_not_raised then
323 x_return_status := l_ret_code;
324 x_msg := l_ret_msg;
325 when others then
326 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
327 x_msg := SQLERRM;
328 End;
329
330 Procedure delete_trading_partner(
331 x_return_status OUT NOCOPY Pls_integer,
332 x_msg OUT NOCOPY Varchar2,
333 p_tp_header_id IN Pls_integer
334 ) IS
335
336 l_xref_dtl_id ecx_xref_dtl.xref_dtl_id%type;
337 l_ret_code pls_integer := ECX_UTIL_API.G_NO_ERROR;
338 l_ret_msg varchar2(2000) := null;
339 l_event_name varchar2(250) := null;
340 l_event_key number := -1;
341 l_party_type ecx_tp_headers.party_type%type;
342 l_party_id number;
343 l_party_site_id number;
344 l_company_admin_email ecx_tp_headers.company_admin_email%type;
345
346 cursor get_xref_dtl_id is
347 select xref_dtl_id from ecx_xref_dtl
348 where tp_header_id = p_tp_header_id;
349
350 Begin
351 x_return_status := ECX_UTIL_API.G_NO_ERROR;
352 x_msg := null;
353
354 If p_tp_header_id is null then
355 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
356 x_msg := ecx_debug.getTranslatedMessage('ECX_TP_HDR_ID_NOT_NULL');
357 Return;
358 end if;
359
360 if NOT (ecx_util_api.validate_trading_partner(p_tp_header_id))
361 then
362 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
363 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_TP_HDR_ID',
364 'p_tp_header_id', p_tp_header_id);
365 return;
366 end if;
367
368 open get_xref_dtl_id;
369 fetch get_xref_dtl_id into l_xref_dtl_id;
370 close get_xref_dtl_id;
371
372 delete from ecx_tp_details
373 where tp_header_id = p_tp_header_id;
374
375 delete from ecx_xref_dtl
376 where tp_header_id = p_tp_header_id;
377
378 delete from ecx_xref_dtl_tl
379 where xref_dtl_id = l_xref_dtl_id;
380
381 /* For raising event */
382 select party_type,
383 party_id,
384 party_site_id,
385 company_admin_email
386 into l_party_type,
387 l_party_id,
388 l_party_site_id,
389 l_company_admin_email
390 from ecx_tp_headers
391 where tp_header_id = p_tp_header_id;
392
393 delete from ecx_tp_headers
394 where tp_header_id = p_tp_header_id;
395
396 if (sql%rowcount = 0) then
397 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
398 x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_DELETED',
399 'p_table', 'ecx_tp_headers',
400 'p_param_name', 'Trading partner header ID',
401 'p_param_id', p_tp_header_id);
402 return;
403 elsif (sql%rowcount > 0) then
404 raise_tp_event(
405 x_return_status => l_ret_code,
406 x_msg => l_ret_msg,
407 x_event_name => l_event_name,
408 x_event_key => l_event_key,
409 p_mod_type => 'DELETE',
410 p_tp_header_id => p_tp_header_id,
411 p_party_type => l_party_type,
412 p_party_id => l_party_id,
413 p_party_site_id => l_party_site_id,
414 p_company_email_addr => l_company_admin_email);
415
416 if NOT(l_ret_code = ECX_UTIL_API.G_NO_ERROR) then
417 raise ecx_tp_api.tp_event_not_raised;
418 end if;
419 end if;
420
421 exception
422 when ecx_tp_api.tp_event_not_raised then
423 x_return_status := l_ret_code;
424 x_msg := l_ret_msg;
425 when others then
426 if (get_xref_dtl_id%ISOPEN)
427 then
428 close get_xref_dtl_id;
429 end if;
430 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
431 x_msg := SQLERRM;
432 End;
433
434 /* Bug #2183619, Added one additional input parameter for
435 source_tp_location_code */
436 Procedure retrieve_tp_detail(
437 x_return_status OUT NOCOPY Pls_integer,
438 x_msg OUT NOCOPY Varchar2,
439 x_tp_detail_id OUT NOCOPY Pls_integer,
440 p_tp_header_id IN Pls_integer,
441 p_ext_process_id IN Pls_integer,
442 x_map_code OUT NOCOPY Varchar2,
443 x_connection_type OUT NOCOPY Varchar2,
444 x_hub_user_id OUT NOCOPY Pls_integer,
445 x_protocol_type OUT NOCOPY Varchar2,
446 x_protocol_address OUT NOCOPY Varchar2,
447 x_username OUT NOCOPY Varchar2,
448 x_password OUT NOCOPY Varchar2,
449 x_routing_id OUT NOCOPY Pls_integer,
450 x_source_tp_location_code OUT NOCOPY Varchar2,
451 x_external_tp_location_code OUT NOCOPY Varchar2,
452 x_confirmation OUT NOCOPY Varchar2,
453 x_created_by OUT NOCOPY Varchar2,
454 x_creation_date OUT NOCOPY Varchar2,
455 x_last_updated_by OUT NOCOPY Varchar2,
456 x_last_update_date OUT NOCOPY Varchar2,
457 p_source_tp_location_code IN Varchar2
458 ) IS
459
460 i_hub_id NUMBER;
461
462 Begin
463 x_return_status := ECX_UTIL_API.G_NO_ERROR;
464 x_msg := null;
465
466 If p_tp_header_id is null Then
467 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
468 x_msg := ecx_debug.getTranslatedMessage('ECX_TP_HDR_ID_NOT_NULL');
469 return;
470 ElsIf
471 p_ext_process_id is null then
472 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
473 x_msg := ecx_debug.getTranslatedMessage('ECX_EXT_PROCESS_ID_NOT_NULL');
474 return;
475 end if;
476 /* bug #2183619 , Added check for source_tp_lcoation_code */
477 select
478 TP_DETAIL_ID,
479 MAP_CODE,
480 CONNECTION_TYPE,
481 HUB_USER_ID,
482 HUB_ID,
483 PROTOCOL_TYPE,
484 PROTOCOL_ADDRESS,
485 USERNAME,
486 PASSWORD ,
487 ROUTING_ID,
488 SOURCE_TP_LOCATION_CODE,
489 EXTERNAL_TP_LOCATION_CODE,
490 CONFIRMATION,
491 etd.CREATED_BY,
492 etd.LAST_UPDATED_BY,
493 etd.CREATION_DATE,
494 etd.LAST_UPDATE_DATE
495 Into
496 x_tp_detail_id,
497 x_map_code,
498 x_connection_type,
499 x_hub_user_id,
500 i_hub_id,
501 x_protocol_type,
502 x_protocol_address,
503 x_username,
504 x_password,
505 x_routing_id,
506 x_source_tp_location_code,
507 x_external_tp_location_code,
508 x_confirmation,
509 x_created_by ,
510 x_last_updated_by ,
511 x_creation_date,
512 x_last_update_date
513 from ecx_tp_details etd,
514 ecx_mappings em
515 where etd.tp_header_id = p_tp_header_id
516 and etd.ext_process_id = p_ext_process_id
517 and em.map_id = etd.map_id
518 and (p_source_tp_location_code is null
519 or etd.source_tp_location_code=p_source_tp_location_code);
520
521 if (x_connection_type <> 'DIRECT') then
522 Begin
523 select protocol_type,protocol_address
524 into x_protocol_type,x_protocol_address
525 from ecx_hubs
526 where hub_id=i_hub_id;
527 Exception
528 When no_data_found then
529 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
530 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_HUB_ID',
531 'p_hub_id',i_hub_id);
532 return;
533 End;
534 Begin
535 if(x_hub_user_id is not null) then
536 select username,password
537 into x_username,x_password
538 from ecx_hub_users
539 where hub_user_id=x_hub_user_id ;
540 end if;
541 Exception
542 When no_data_found then
543 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
544 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_HUB_USER_ID',
545 'p_hub_user_id',x_hub_user_id);
546 return;
547 End;
548 End If;
549
550 Exception
551 when no_data_found then
552 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
553 x_msg := ecx_debug.getTranslatedMessage('ECX_TP_DTL_NOT_FOUND',
554 'p_tp_header_id',p_tp_header_id,
555 'p_ext_process_id', p_ext_process_id );
556
557 when too_many_rows then
558 x_return_status := ECX_UTIL_API.G_TOO_MANY_ROWS;
559 x_msg := ecx_debug.getTranslatedMessage('ECX_TP_DTL_TOO_MANY_ROWS',
560 'p_tp_header_id',p_tp_header_id,
561 'p_ext_process_id', p_ext_process_id );
562 when others then
563 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
564 x_msg := SQLERRM;
565 End;
566
567 --Overloaded procedure
568 /* Bug #2183619, Added three additional input parameters for
569 External Type and Subtype and source_tp_location_code*/
570 Procedure retrieve_tp_detail(
571 x_return_status OUT NOCOPY Pls_integer,
572 x_msg OUT NOCOPY Varchar2,
573 x_tp_detail_id OUT NOCOPY Pls_integer,
574 x_tp_header_id OUT NOCOPY Pls_integer,
575 p_party_type IN Varchar2,
576 p_party_id IN Varchar2,
577 p_party_site_id IN Varchar2,
578 p_transaction_type IN Varchar2,
579 p_transaction_subtype IN Varchar2,
580 p_standard_code IN Varchar2,
581 p_direction IN Varchar2,
582 x_ext_type OUT NOCOPY Varchar2,
583 x_ext_subtype OUT NOCOPY Varchar2,
584 x_map_code OUT NOCOPY Varchar2,
585 x_connection_type OUT NOCOPY Varchar2,
586 x_hub_user_id OUT NOCOPY Pls_integer,
587 x_protocol_type OUT NOCOPY Varchar2,
588 x_protocol_address OUT NOCOPY Varchar2,
589 x_username OUT NOCOPY Varchar2,
590 x_password OUT NOCOPY Varchar2,
591 x_routing_id OUT NOCOPY Pls_integer,
592 x_source_tp_location_code OUT NOCOPY Varchar2,
593 x_external_tp_location_code OUT NOCOPY Varchar2,
594 x_confirmation OUT NOCOPY Varchar2,
595 x_created_by OUT NOCOPY Varchar2,
596 x_creation_date OUT NOCOPY Varchar2,
597 x_last_updated_by OUT NOCOPY Varchar2,
598 x_last_update_date OUT NOCOPY Varchar2,
599 p_ext_type IN Varchar2 ,
600 p_ext_subtype IN Varchar2 ,
601 p_source_tp_location_code IN Varchar2
602
603 ) IS
604
605 x_ext_process_id number := 0;
606 x_transaction_id number := 0;
607 x_standard_id number := 0;
608 x_queue_name ecx_ext_processes.queue_name%type;
609 x_transaction_description ecx_transactions_tl.transaction_description%type;
610
611 Begin
612 x_return_status := ECX_UTIL_API.G_NO_ERROR;
613 x_msg := null;
614 x_tp_detail_id := -1;
615
616 If p_party_type is null Then
617 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
618 x_msg := ecx_debug.getTranslatedMessage('ECX_PARTY_TYPE_NOT_NULL');
619 return;
620 ElsIf
621 p_party_id is null then
622 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
623 x_msg := ecx_debug.getTranslatedMessage('ECX_PARTY_ID_NOT_NULL');
624 return;
625 ElsIf
626 p_party_site_id is null then
627 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
628 x_msg := ecx_debug.getTranslatedMessage('ECX_PARTY_SITE_ID_NOT_NULL');
629 return;
630 ElsIf
631 p_transaction_type is null then
632 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
633 x_msg := ecx_debug.getTranslatedMessage('ECX_TRANSACTION_TYPE_NOT_NULL');
634 return;
635 ElsIf
636 p_transaction_subtype is null then
637 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
638 x_msg := ecx_debug.getTranslatedMessage('ECX_TRANSACTION_SUBTYPE_NOT_NULL');
639 return;
640 ElsIf
641 p_standard_code is null then
642 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
643 x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_CODE_NOT_FOUND',
644 'p_standard_code',p_standard_code);
645 return;
646 ElsIf
647 p_direction is null then
648 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
649 x_msg := ecx_debug.getTranslatedMessage('ECX_DIRECTION_NOT_NULL');
650 return;
651 end if;
652 /* Bug #2183619, Added two additional input parameters for
653 External Type and Subtype */
654 ecx_transactions_api.retrieve_external_transaction(
655 p_transaction_type => p_transaction_type,
656 p_transaction_subtype => p_transaction_subtype,
657 p_party_type => p_party_type,
658 p_standard => p_standard_code,
659 p_direction => p_direction,
660 p_ext_type => p_ext_type,
661 p_ext_subtype => p_ext_subtype,
662 x_ext_process_id => x_ext_process_id,
663 x_transaction_id => x_transaction_id,
664 x_transaction_description => x_transaction_description,
665 x_ext_type => x_ext_type,
666 x_ext_subtype => x_ext_subtype,
667 x_standard_id => x_standard_id,
668 x_queue_name => x_queue_name,
669 x_created_by => x_created_by,
670 x_creation_date => x_creation_date,
671 x_last_updated_by => x_last_updated_by,
672 x_last_update_date => x_last_update_date,
673 x_return_status => x_return_status,
674 x_msg => x_msg);
675 if (x_ext_process_id = -1) then return; end if;
676 /* Bug #2183619,Added check for source_tp_Location_code */
677 select
678 etd.TP_HEADER_ID,
679 etd.TP_DETAIL_ID,
680 em.MAP_CODE,
681 etd.CONNECTION_TYPE,
682 etd.HUB_USER_ID,
683 etd.PROTOCOL_TYPE,
684 etd.PROTOCOL_ADDRESS,
685 etd.USERNAME,
686 etd.PASSWORD,
687 etd.ROUTING_ID,
688 etd.SOURCE_TP_LOCATION_CODE,
689 etd.EXTERNAL_TP_LOCATION_CODE,
690 etd.CONFIRMATION,
691 etd.CREATED_BY,
692 etd.LAST_UPDATED_BY,
693 etd.CREATION_DATE,
694 etd.LAST_UPDATE_DATE
695 Into
696 x_tp_header_id,
697 x_tp_detail_id,
698 x_map_code,
699 x_connection_type,
700 x_hub_user_id,
701 x_protocol_type,
702 x_protocol_address,
703 x_username,
704 x_password,
705 x_routing_id,
706 x_source_tp_location_code,
707 x_external_tp_location_code,
708 x_confirmation,
709 x_created_by,
710 x_last_updated_by,
711 x_creation_date,
712 x_last_update_date
713 from ecx_tp_details etd,
714 ecx_tp_headers eth,
715 ecx_mappings em
716 where eth.party_id = p_party_id
717 and eth.party_site_id = p_party_site_id
718 and eth.party_type = p_party_type
719 and etd.ext_process_id = x_ext_process_id
720 and em.map_id = etd.map_id
721 and (p_source_tp_location_code is null
722 or etd.source_tp_location_code=p_source_tp_location_code);
723
724 Exception
725 when no_data_found then
726 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
727 x_msg := ecx_debug.getTranslatedMessage('ECX_TP_DTL1_NOT_FOUND',
728 'p_party_type',p_party_type,
729 'p_party_id', p_party_id ,
730 'p_party_site_id', p_party_site_id,
731 'p_transaction_type', p_transaction_type,
732 'p_transaction_subtype', p_transaction_subtype,
733 'p_standard_code', p_standard_code,
734 'p_direction', p_direction);
735 when too_many_rows then
736 x_return_status := ECX_UTIL_API.G_TOO_MANY_ROWS;
737 x_msg := ecx_debug.getTranslatedMessage('ECX_TP_DTL1_TOO_MANY_ROWS',
738 'p_party_type',p_party_type,
739 'p_party_id', p_party_id ,
740 'p_party_site_id', p_party_site_id,
741 'p_transaction_type', p_transaction_type,
742 'p_transaction_subtype', p_transaction_subtype,
743 'p_standard_code', p_standard_code,
744 'p_direction', p_direction);
745 when others then
746 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
747 x_msg := SQLERRM;
748 End;
749
750
751 Procedure create_tp_detail(
752 x_return_status OUT NOCOPY pls_integer,
753 x_msg OUT NOCOPY Varchar2,
754 x_tp_detail_id OUT NOCOPY Pls_integer,
755 p_tp_header_id IN pls_integer,
756 p_ext_process_id IN pls_integer,
757 p_map_code IN Varchar2,
758 p_connection_type IN Varchar2,
759 p_hub_user_id IN pls_integer,
760 p_protocol_type IN Varchar2,
761 p_protocol_address IN Varchar2,
762 p_username IN Varchar2,
763 p_password IN Varchar2,
764 p_routing_id IN pls_integer,
765 p_source_tp_location_code IN Varchar2 default null,
766 p_external_tp_location_code IN Varchar2,
767 p_confirmation IN pls_integer
768 )IS
769
770 l_confirmation ecx_tp_details.confirmation%type;
771 l_connection_type ecx_tp_details.connection_type%type;
772 l_hub_user_id ecx_hub_users.hub_user_id%type;
773 l_source_tp_location_code ecx_tp_details.source_tp_location_code%type;
774
775 cursor get_src_loc_code (i_hub_id IN pls_integer) is
776 select name
777 from ecx_hubs
778 where hub_id = i_hub_id;
779
780 cursor get_hub_entity_code is
781 select hub_entity_code
782 from ecx_hub_users
783 where hub_user_id = p_hub_user_id;
784
785 Cursor c1 Is
786 select 1 from ecx_tp_headers
787 where tp_header_id = p_tp_header_id;
788
789 Cursor c2 Is
790 select 1,direction from ecx_ext_processes
791 where ext_process_id = p_ext_process_id;
792
793 Cursor c3 Is
794 select map_id from ecx_mappings
795 where map_code = p_map_code;
796
797 Cursor c4 Is
798 select 1 from ecx_hub_users
799 where hub_user_id = p_hub_user_id;
800
801 Cursor c5 Is
802 select 1 from ecx_tp_details
803 where tp_detail_id = p_routing_id;
804
805 /* Start changes for bug #2183619 */
806 Cursor c6(p_ext_type_in VARCHAR2,p_ext_subtype_in VARCHAR2,
807 p_standard_id_in NUMBER,p_direction_in VARCHAR2,
808 p_source_tp_location_code_in VARCHAR2) Is
809 Select 1 from ecx_tp_details tp,ecx_ext_processes ep
810 where tp.ext_process_id=ep.ext_process_id
811 And ep.ext_type = p_ext_type_in
812 And ep.ext_subtype = p_ext_subtype_in
813 And ep.standard_id = p_standard_id_in
814 And tp.source_tp_Location_code= p_source_tp_location_code_in
815 And ep.direction = p_direction_in ;
816
817 Cursor c7(p_tp_header_id_in NUMBER,p_transaction_type_in VARCHAR2,
818 p_transaction_subtype_in VARCHAR2) is
819 select 1 from ecx_tp_details
820 where tp_header_id = p_tp_header_id_in
821 and ext_process_id in ( select ext.ext_process_id
822 from ecx_ext_processes ext,
823 ecx_transactions tran
824 where ext.direction = 'OUT'
825 and ext.transaction_id
826 = tran.transaction_id
827 and tran.transaction_type
828 = p_transaction_type_in
829 and tran.transaction_subtype
830 = p_transaction_subtype_in );
831 Cursor c8(p_ext_process_id_in NUMBER) is
832 select ext_type,ext_subtype,standard_id,direction
833 from ecx_ext_processes
834 where ext_process_id=p_ext_process_id_in;
835
836 Cursor c9 (p_ext_process_id_in NUMBER) is
837 select transaction_type,transaction_subtype
838 from ecx_transactions et,ecx_ext_processes eep
839 where eep.ext_process_id = p_ext_process_id_in
840 and et.transaction_id = eep.transaction_id;
841
842 /* End of changes for bug #2183619*/
843
844 /*Bug #2449729 , cursor to retrieve hub_id */
845 Cursor c10 is
846 select 1,hub_id from ecx_hubs
847 where name=p_connection_type and
848 protocol_type= p_protocol_type;
849
850 Cursor c_tp_dtl_id is
851 select ecx_tp_details_s.nextval
852 from dual;
853
854 num number := 0;
855 i_map_id number:=0;
856 encrypt_password ecx_tp_details.password%type;
857 i_direction ecx_ext_processes.direction%type;
858
859 /* Added declartions for Bug #2183619 */
860 p_ext_type varchar2(80);
861 p_ext_subtype varchar2(80);
862 p_standard_id NUMBER(15);
863 p_direction varchar2(20);
864 p_transaction_type varchar2(100);
865 p_transaction_subtype varchar2(100);
866 x_password varchar2(500);
867 i_hub_id NUMBER ;
868
869 begin
870
871 x_return_status := ECX_UTIL_API.G_NO_ERROR;
872 x_msg := null;
873 x_tp_detail_id := -1;
874 x_password := p_password;
875 If p_tp_header_id is null Then
876 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
877 x_msg := ecx_debug.getTranslatedMessage('ECX_TP_HDR_ID_NOT_NULL');
878 return;
879 ElsIf
880 p_ext_process_id is null then
881 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
882 x_msg := ecx_debug.getTranslatedMessage('ECX_EXT_PROCESS_ID_NOT_NULL');
883 return;
884 end if;
885
886 -- check if the tp header exists. If not, return an error.
887 num := 0;
888 open c1;
889 fetch c1 into num;
890 close c1;
891
892 if (num = 0) then
893 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
894 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_TP_HDR_ID',
895 'p_tp_header_id',p_tp_header_id);
896 return;
897 end if;
898
899 -- check if the ext_process id exists. If not, return an error.
900 num := 0;
901 open c2;
902 fetch c2 into num,i_direction;
903 close c2;
904
905 if(num = 0) then
906 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
907 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_EXT_PROCESS_ID',
908 'p_ext_process_id',p_ext_process_id);
909 return;
910 end if;
911
912 -- check if the map exists or not.
913 open c3;
914 fetch c3 into i_map_id;
915 close c3;
916
917 if(i_map_id = 0) then
918 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
919 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_MAP_CODE',
920 'p_map_code',p_map_code);
921 return;
922 end if;
923
924 -- confirmation_code should be checked for validity only if it specified.
925 -- if not specified set to 0 like forms
926 if (p_confirmation is not null)
927 then
928 if not(ECX_UTIL_API.validate_confirmation_code(p_confirmation)) then
929 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
930 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_CONF_CODE',
931 'p_confirmation', p_confirmation);
932 return;
933 end if;
934 l_confirmation := p_confirmation;
935 else
936 l_confirmation := 0;
937 end if;
938
939 if (i_direction = 'OUT') Then
940 -- validate the connection_type ,protocol_type
941 if (p_connection_type is null)
942 then
943 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
944 x_msg := ecx_debug.getTranslatedMessage('ECX_CONNECTION_TYPE_NOT_NULL');
945 return;
946 end if;
947
948 If p_protocol_type is null Then
949 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
950 x_msg := ecx_debug.getTranslatedMessage('ECX_PROTOCOL_TYPE_NOT_NULL');
951 return;
952 elsif
953 not(ECX_UTIL_API.validate_protocol_type(p_protocol_type)) then
954 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
955 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_PROTOCOL_TYPE',
956 'p_protocol_type',p_protocol_type);
957 return;
958 end if;
959
960 /* Start changes for bug #2183619 */
961 /* Check for uniqueness trading partner details row for OUTBOUND transactions */
962
963 open c9(p_ext_process_id);
964 fetch c9 into p_transaction_type, p_transaction_subtype;
965 close c9;
966 num := 0;
967 open c7(p_tp_header_id, p_transaction_type, p_transaction_subtype );
968 fetch c7 into num;
969 close c7;
970 if (num <> 0) then
971 x_return_status := ECX_UTIL_API.G_DUP_ERROR;
972 x_msg := ecx_debug.getTranslatedMessage('ECX_TP_DTL2_EXISTS',
973 'p_tp_header_id', p_tp_header_id,
974 'p_transaction_type', p_transaction_type,
975 'p_transaction_subtype', p_transaction_subtype
976 );
977 return;
978 End If;
979 /* End of changes for bug #2183619 */
980
981 if (upper(p_connection_type) = 'DIRECT') then
982 l_connection_type := 'DIRECT';
983 if p_protocol_type NOT IN ('NONE','IAS','ITG03') Then
984
985 if p_protocol_address is null Then
986 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
987 x_msg := ecx_debug.getTranslatedMessage('ECX_PROTOCOL_ADDR_NOT_NULL');
988 return;
989 end if;
990
991 if (p_username is not null) then
992 --- Check password length
993 if not(ECX_UTIL_API.validate_password_length(p_password)) then
994 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
995 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_PWD_LEN');
996 return;
997 end if;
998 /* Added check for bug #2410173 */
999 if not(ECX_UTIL_API.validate_password(x_password)) then
1000 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1001 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_PWD');
1002 return;
1003 end if;
1004
1005 --- Encrypt the password
1006 ecx_obfuscate.ecx_data_encrypt(
1007 l_input_string => x_password,
1008 l_output_string => encrypt_password,
1009 errmsg => x_msg,
1010 retcode => x_return_status);
1011
1012 end if;
1013 end if;
1014
1015 -- Check source_tp_location_code
1016 If p_source_tp_location_code is null Then
1017 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1018 x_msg := ecx_debug.getTranslatedMessage('ECX_LOCATION_NOT_NULL');
1019 return;
1020 End If;
1021
1022 open c_tp_dtl_id;
1023 fetch c_tp_dtl_id into x_tp_detail_id;
1024 close c_tp_dtl_id;
1025
1026 -- insert data into ECX_TP_DETAILS.
1027 Insert into ECX_TP_DETAILS(
1028 TP_HEADER_ID,
1029 TP_DETAIL_ID,
1030 MAP_ID,
1031 EXT_PROCESS_ID,
1032 CONNECTION_TYPE,
1033 HUB_USER_ID,
1034 HUB_ID,
1035 PROTOCOL_TYPE,
1036 PROTOCOL_ADDRESS,
1037 USERNAME,
1038 PASSWORD,
1039 ROUTING_ID,
1040 SOURCE_TP_LOCATION_CODE,
1041 EXTERNAL_TP_LOCATION_CODE,
1042 CONFIRMATION,
1043 CREATED_BY,
1044 LAST_UPDATED_BY,
1045 CREATION_DATE,
1046 LAST_UPDATE_DATE
1047 )
1048 values (
1049 p_tp_header_id,
1050 x_tp_detail_id,
1051 i_map_id,
1052 p_ext_process_id,
1053 l_connection_type,
1054 null,
1055 null,
1056 p_protocol_type,
1057 p_protocol_address,
1058 p_username,
1059 encrypt_password, -- CHECK THIS!
1060 null,
1061 p_source_tp_location_code,
1062 p_external_tp_location_code,
1063 l_confirmation,
1064 0,
1065 0,
1066 sysdate,
1067 sysdate
1068 );
1069 else -- Hub connection type
1070 -- bug #2449729
1071 -- Retrieve the hub_id from ecx_hubs
1072 num := 0;
1073 open c10;
1074 fetch c10 into num, i_hub_id;
1075 close c10;
1076 if(num = 0) then
1077 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
1078 x_msg := ecx_debug.gettranslatedMessage('ECX_HUB_NOT_EXISTS',
1079 'p_connection_type',p_connection_type,
1080 'p_protocol_type',p_protocol_type);
1081 return;
1082 End if;
1083
1084 -- hub_user information is required only if protocol_type <> SMTP
1085 if (p_protocol_type <> 'SMTP') then
1086 if p_hub_user_id is null Then
1087 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1088 x_msg := ecx_debug.getTranslatedMessage('ECX_HUB_USER_ID_NOT_NULL');
1089 return;
1090 end if;
1091 end if;
1092
1093
1094 -- for any protocol_type if hub user_id is provided, check if it is valid
1095 if (p_hub_user_id is not null)
1096 then
1097 -- case where protocol_type is non-SMTP or where protocol_type is SMTP and
1098 -- hub_user_id is provided
1099 num := 0;
1100 open c4;
1101 fetch c4 into num;
1102 close c4;
1103
1104 if (num = 0) then
1105 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1106 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_HUB_USER_ID',
1107 'p_hub_user_id',p_hub_user_id);
1108 return;
1109 end if;
1110
1111 open get_hub_entity_code;
1112 fetch get_hub_entity_code
1113 into l_source_tp_location_code;
1114 close get_hub_entity_code;
1115
1116 else -- case for SMTP with no hub_user_id info
1117 -- set source_tp_location_code to the hub_name
1118 open get_src_loc_code (i_hub_id);
1119 fetch get_src_loc_code into l_source_tp_location_code;
1120 close get_src_loc_code;
1121 end if;
1122 l_hub_user_id := p_hub_user_id;
1123
1124 if (not p_source_tp_location_code is null)
1125 then
1126 if (l_source_tp_location_code <> p_source_tp_location_code)
1127 then
1128 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1129 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_LOCATION',
1130 'p_location_code', p_source_tp_location_code);
1131 return;
1132 end if;
1133 end if;
1134
1135 open c_tp_dtl_id;
1136 fetch c_tp_dtl_id into x_tp_detail_id;
1137 close c_tp_dtl_id;
1138
1139 -- insert data into ECX_TP_DETAILS.
1140 Insert into ECX_TP_DETAILS(
1141 TP_HEADER_ID,
1142 TP_DETAIL_ID,
1143 MAP_ID,
1144 EXT_PROCESS_ID,
1145 CONNECTION_TYPE,
1146 HUB_USER_ID,
1147 HUB_ID,
1148 PROTOCOL_TYPE,
1149 PROTOCOL_ADDRESS,
1150 USERNAME,
1151 PASSWORD,
1152 ROUTING_ID,
1153 EXTERNAL_TP_LOCATION_CODE,
1154 CONFIRMATION,
1155 CREATED_BY,
1156 LAST_UPDATED_BY,
1157 CREATION_DATE,
1158 LAST_UPDATE_DATE
1159 )
1160 values (
1161 p_tp_header_id,
1162 x_tp_detail_id,
1163 i_map_id,
1164 p_ext_process_id,
1165 p_connection_type,
1166 l_hub_user_id,
1167 i_hub_id,
1168 null,
1169 null,
1170 null,
1171 null,
1172 null,
1173 p_external_tp_location_code,
1174 l_confirmation,
1175 0,
1176 0,
1177 sysdate,
1178 sysdate
1179 );
1180 end if;
1181 else -- i_direciton is 'IN'
1182 -- Validate routing id
1183 If p_routing_id is not null and
1184 i_direction = 'IN' then
1185 num := 0;
1186 open c5;
1187 fetch c5 into num;
1188 close c5;
1189
1190 if (num = 0) then
1191 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1192 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_ROUTING_ID',
1193 'p_routing_id',p_routing_id);
1194 return;
1195 end if;
1196 end if;
1197
1198 --- Check source_tp_location_code
1199 If p_source_tp_location_code is null Then
1200 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1201 x_msg := ecx_debug.getTranslatedMessage('ECX_LOCATION_NOT_NULL');
1202 return;
1203 End If;
1204 /* Start changes for bug #2183619 */
1205 /* Check for uniqueness of ext_process_id and source_tp_location_code
1206 for inbound transactions */
1207
1208 open c8(p_ext_process_id);
1209 fetch c8 into p_ext_type,p_ext_subtype,p_standard_id,p_direction;
1210 num := 0;
1211 open c6(p_ext_type,p_ext_subtype,p_standard_id,p_direction,
1212 p_source_tp_location_code);
1213 fetch c6 into num;
1214 close c6;
1215 if (num <> 0) then
1216 x_return_status := ECX_UTIL_API.G_DUP_ERROR;
1217 x_msg := ecx_debug.getTranslatedMessage('ECX_TP_DTL1_EXISTS',
1218 'p_ext_type', p_ext_type,
1219 'p_ext_subtype', p_ext_subtype,
1220 'p_standard_id', p_standard_id,
1221 'p_source_tp_location_code', p_source_tp_location_code
1222 );
1223 return;
1224 end if;
1225 /* End of changes for bug #2183619*/
1226
1227 open c_tp_dtl_id;
1228 fetch c_tp_dtl_id into x_tp_detail_id;
1229 close c_tp_dtl_id;
1230
1231 -- insert data into ECX_TP_DETAILS.
1232 Insert into ECX_TP_DETAILS(
1233 TP_HEADER_ID,
1234 TP_DETAIL_ID,
1235 MAP_ID,
1236 EXT_PROCESS_ID,
1237 CONNECTION_TYPE,
1238 HUB_USER_ID,
1239 HUB_ID,
1240 PROTOCOL_TYPE,
1241 PROTOCOL_ADDRESS,
1242 USERNAME,
1243 PASSWORD,
1244 ROUTING_ID,
1245 SOURCE_TP_LOCATION_CODE,
1246 EXTERNAL_TP_LOCATION_CODE,
1247 CONFIRMATION,
1248 CREATED_BY,
1249 LAST_UPDATED_BY,
1250 CREATION_DATE,
1251 LAST_UPDATE_DATE
1252 )
1253 values (
1254 p_tp_header_id,
1255 x_tp_detail_id,
1256 i_map_id,
1257 p_ext_process_id,
1258 null,
1259 null,
1260 null,
1261 null,
1262 null,
1263 null,
1264 null,
1265 p_routing_id,
1266 p_source_tp_location_code,
1267 p_external_tp_location_code,
1268 l_confirmation,
1269 0,
1270 0,
1271 sysdate,
1272 sysdate
1273 );
1274 end if;
1275 Exception
1276 when dup_val_on_index then
1277 x_return_status := ECX_UTIL_API.G_DUP_ERROR;
1278 x_msg := ecx_debug.gettranslatedMessage('ECX_TP_DTL_EXISTS',
1279 'p_tp_header_id', p_tp_header_id,
1280 'p_ext_process_id',p_ext_process_id);
1281 when others then
1282 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
1283 x_msg := SQLERRM;
1284 end;
1285
1286
1287
1288
1289
1290 ---Overloaded
1291 /* Bug 2122579 */
1292 /* Bug #2183619, Added two input additional parameters for
1293 External Type and Subtype */
1294 Procedure create_tp_detail(
1295 x_return_status OUT NOCOPY pls_integer,
1296 x_msg OUT NOCOPY Varchar2,
1297 x_tp_detail_id OUT NOCOPY Pls_integer,
1298 x_tp_header_id OUT NOCOPY Pls_integer,
1299 p_party_type IN Varchar2,
1300 p_party_id IN number,
1301 p_party_site_id IN number,
1302 p_transaction_type IN Varchar2,
1303 p_transaction_subtype IN Varchar2,
1304 p_standard_code IN Varchar2,
1305 p_direction IN Varchar2,
1306 p_map_code IN Varchar2,
1307 p_connection_type IN Varchar2,
1308 p_hub_user_id IN pls_integer,
1309 p_protocol_type IN Varchar2,
1310 p_protocol_address IN Varchar2,
1311 p_username IN Varchar2,
1312 p_password IN Varchar2,
1313 p_routing_id IN pls_integer,
1314 p_source_tp_location_code IN Varchar2 default null,
1315 p_external_tp_location_code IN Varchar2,
1316 p_confirmation IN pls_integer,
1317 p_ext_type IN Varchar2 ,
1318 p_ext_subtype IN Varchar2
1319 ) IS
1320 i_created_by varchar2(10);
1321 i_last_updated_by varchar2(10);
1322 i_creation_date varchar2(25);
1323 i_last_update_date varchar2(25);
1324 i_company_admin_email varchar2(250);
1325 i_ext_process_id number;
1326 i_transaction_id number;
1327 i_standard_id number;
1328 i_transaction_description ecx_transactions_tl.transaction_description%type;
1329 i_ext_type ecx_ext_processes.ext_type%type;
1330 i_ext_subtype ecx_ext_processes.ext_subtype%type;
1331 i_queue_name ecx_ext_processes.queue_name%type;
1332
1333 begin
1334 x_return_status := ECX_UTIL_API.G_NO_ERROR;
1335 x_msg := null;
1336 x_tp_detail_id := -1;
1337
1338 -- make sure party_id, party_type, party_site_id and p_company_admin_email are not null.
1339 If (p_party_type is null) then
1340 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1341 x_msg := ecx_debug.getTranslatedMessage('ECX_PARTY_TYPE_NOT_NULL',
1342 'p_party_type',p_party_type);
1343 return;
1344 elsif
1345 (p_party_id is null) then
1346 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1347 x_msg := ecx_debug.getTranslatedMessage('ECX_PARTY_ID_NOT_NULL',
1348 'p_party_id',p_party_id);
1349 return;
1350 elsif
1351 (p_party_site_id is null) then
1352 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1353 x_msg := ecx_debug.getTranslatedMessage('ECX_PARTY_SITE_ID_NOT_NULL',
1354 'p_party_site_id',p_party_site_id);
1355 return;
1356 end if;
1357
1358 retrieve_trading_partner(
1359 p_party_type => p_party_type,
1360 p_party_id => p_party_id,
1361 p_party_site_id => p_party_site_id,
1362 x_tp_header_id => x_tp_header_id,
1363 x_company_admin_email => i_company_admin_email,
1364 x_created_by => i_created_by,
1365 x_creation_date => i_creation_date,
1366 x_last_updated_by => i_last_updated_by,
1367 x_last_update_date => i_last_update_date,
1368 x_return_status =>x_return_status,
1369 x_msg =>x_msg
1370 );
1371
1372 if (x_tp_header_id = -1) then
1373 return;
1374 end if;
1375
1376
1377 /* Bug #2183619, Added two additional input parameters for
1378 External Type and Subtype */
1379 ecx_transactions_api.retrieve_external_transaction(
1380 p_transaction_type => p_transaction_type,
1381 p_transaction_subtype => p_transaction_subtype,
1382 p_party_type => p_party_type,
1383 p_standard => p_standard_code,
1384 p_direction => p_direction,
1385 p_ext_type => p_ext_type,
1386 p_ext_subtype => p_ext_subtype,
1387 x_ext_process_id => i_ext_process_id,
1388 x_transaction_id => i_transaction_id,
1389 x_transaction_description => i_transaction_description,
1390 x_ext_type => i_ext_type,
1391 x_ext_subtype => i_ext_subtype,
1392 x_standard_id => i_standard_id,
1393 x_queue_name => i_queue_name,
1394 x_created_by => i_created_by,
1395 x_creation_date => i_creation_date,
1396 x_last_updated_by => i_last_updated_by,
1397 x_last_update_date => i_last_update_date,
1398 x_return_status => x_return_status,
1399 x_msg => x_msg);
1400
1401 if (i_ext_process_id = -1) then return; end if;
1402
1403 create_tp_detail(
1404 p_tp_header_id => x_tp_header_id,
1405 p_ext_process_id => i_ext_process_id,
1406 p_map_code => p_map_code,
1407 p_connection_type => p_connection_type,
1408 p_hub_user_id => p_hub_user_id,
1409 p_protocol_type => p_protocol_type,
1410 p_protocol_address => p_protocol_address,
1411 p_username => p_username,
1412 p_password => p_password,
1413 p_routing_id => p_routing_id,
1414 p_source_tp_location_code => p_source_tp_location_code,
1415 p_external_tp_location_code => p_external_tp_location_code,
1416 p_confirmation => p_confirmation,
1417 x_tp_detail_id => x_tp_detail_id,
1418 x_return_status => x_return_status,
1419 x_msg => x_msg);
1420 end;
1421
1422
1423 Procedure update_tp_detail(
1424 x_return_status Out NOCOPY pls_integer,
1425 x_msg Out NOCOPY Varchar2,
1426 p_tp_detail_id In pls_integer,
1427 p_map_code In Varchar2,
1428 p_ext_process_id In pls_integer,
1429 p_connection_type In Varchar2,
1430 p_hub_user_id In pls_integer,
1431 p_protocol_type In Varchar2,
1432 p_protocol_address In Varchar2,
1433 p_username In Varchar2,
1434 p_password In Varchar2,
1435 p_routing_id In pls_integer,
1436 p_source_tp_location_code In Varchar2,
1437 p_external_tp_location_code In Varchar2,
1438 p_confirmation In pls_integer ,
1439 p_passupd_flag IN varchar2
1440 ) Is
1441
1442 l_confirmation ecx_tp_details.confirmation%type;
1443 l_connection_type ecx_tp_details.connection_type%type;
1444 l_source_tp_location_code ecx_tp_details.source_tp_location_code%type;
1445 i_passupd_flag varchar2(1);
1446
1447 cursor get_src_loc_code (i_hub_id IN pls_integer) is
1448 select name
1449 from ecx_hubs
1450 where hub_id = i_hub_id;
1451
1452 cursor get_hub_entity_code is
1453 select hub_entity_code
1454 from ecx_hub_users
1455 where hub_user_id = p_hub_user_id;
1456
1457 cursor c1 is
1458 select map_id from ecx_mappings
1459 where map_code = p_map_code;
1460
1461 cursor c2 is
1462 select direction from ecx_ext_processes
1463 where ext_process_id = p_ext_process_id;
1464
1465 cursor c3 is
1466 select 1 from ecx_tp_details
1467 where tp_detail_id = p_tp_detail_id;
1468
1469 Cursor c4 Is
1470 select 1 from ecx_hub_users
1471 where hub_user_id = p_hub_user_id;
1472
1473 Cursor c5 Is
1474 select 1 from ecx_tp_details
1475 where tp_detail_id = p_routing_id;
1476
1477 /* Start changes for bug #2183619 */
1478 Cursor c6(p_ext_type_in VARCHAR2,p_ext_subtype_in VARCHAR2,
1479 p_standard_id_in NUMBER,p_direction_in VARCHAR2,
1480 p_source_tp_location_code_in VARCHAR2,
1481 p_tp_detail_id_in NUMBER) Is
1482 Select tp_detail_id from ecx_tp_details tp,ecx_ext_processes ep
1483 where tp.ext_process_id=ep.ext_process_id
1484 And ep.ext_type = p_ext_type_in
1485 And ep.ext_subtype = p_ext_subtype_in
1486 And ep.standard_id = p_standard_id_in
1487 And tp.source_tp_Location_code= p_source_tp_location_code_in
1488 And tp.tp_detail_id <> p_tp_detail_id_in
1489 And ep.direction = p_direction_in ;
1490
1491 Cursor c7(p_tp_header_id_in NUMBER,p_tp_detail_id_in NUMBER,
1492 p_transaction_type_in VARCHAR2,
1493 p_transaction_subtype_in VARCHAR2) is
1494 select 1 from ecx_tp_details
1495 where tp_header_id = p_tp_header_id_in
1496 and tp_detail_id <> p_tp_detail_id_in
1497 and ext_process_id in ( select ext.ext_process_id
1498 from ecx_ext_processes ext,
1499 ecx_transactions tran
1500 where ext.direction = 'OUT'
1501 and ext.transaction_id
1502 = tran.transaction_id
1503 and tran.transaction_type
1504 = p_transaction_type_in
1505 and tran.transaction_subtype
1506 = p_transaction_subtype_in );
1507 Cursor c8(p_ext_process_id_in NUMBER) is
1508 select ext_type,ext_subtype,standard_id,direction
1509 from ecx_ext_processes
1510 where ext_process_id=p_ext_process_id_in;
1511
1512 Cursor c9 (p_ext_process_id_in NUMBER) is
1513 select transaction_type,transaction_subtype
1514 from ecx_transactions et,ecx_ext_processes eep
1515 where eep.ext_process_id = p_ext_process_id_in
1516 and et.transaction_id = eep.transaction_id;
1517
1518 Cursor c10 (p_tp_detail_id_in NUMBER) is
1519 select tp_header_id
1520 from ecx_tp_details
1521 where tp_detail_id=p_tp_detail_id_in;
1522
1523 /* Bug #2449729 , cursor to retrieve hub_id */
1524 Cursor c11 is
1525 select 1,hub_id from ecx_hubs
1526 where name=p_connection_type and
1527 protocol_type= p_protocol_type;
1528
1529 /* End of changes for bug #2183619*/
1530
1531 encrypt_password ecx_tp_details.password%type;
1532 num number := 0;
1533 i_map_id number :=0;
1534 i_direction varchar2(5):= null;
1535 --Bug #2183619
1536 p_ext_type varchar2(80);
1537 p_ext_subtype varchar2(80);
1538 p_standard_id NUMBER(15);
1539 p_direction varchar2(20);
1540 p_transaction_type varchar2(100);
1541 p_transaction_subtype varchar2(100);
1542 p_tp_header_id NUMBER;
1543 x_password varchar2(500);
1544 i_hub_id NUMBER;
1545
1546 begin
1547 x_return_status := ECX_UTIL_API.G_NO_ERROR;
1548 x_msg := null;
1549 x_password := p_password;
1550
1551 -- make sure tp_detail_id, map_code and ext_process_id are not null.
1552 If p_tp_detail_id is null Then
1553 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1554 x_msg := ecx_debug.getTranslatedMessage('ECX_TP_DTL_ID_NOT_NULL');
1555 return;
1556 ElsIf
1557 p_ext_process_id is null then
1558 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1559 x_msg := ecx_debug.getTranslatedMessage('ECX_EXT_PROCESS_ID_NOT_NULL');
1560 return;
1561 Elsif
1562 p_map_code is null then
1563 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1564 x_msg := ecx_debug.getTranslatedMessage('ECX_MAP_CODE_NOT_NULL');
1565 return;
1566 Else
1567 if (p_passupd_flag is null) then
1568 i_passupd_flag := 'Y';
1569 elsif (upper(p_passupd_flag) <>'Y' and
1570 upper(p_passupd_flag) <> 'N')
1571 then
1572 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1573 x_msg := ecx_debug.getTranslatedMessage('ECX_PASSUPD_INVALID');
1574 return;
1575 else
1576 i_passupd_flag := upper(p_passupd_flag);
1577 end if;
1578
1579 end if;
1580
1581 --- Get transaction direction
1582 i_direction := null;
1583 open c2;
1584 fetch c2 into i_direction;
1585 close c2;
1586 if(i_direction is NULL) then
1587 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1588 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_EXT_PROCESS_ID',
1589 'p_ext_process_id',p_ext_process_id);
1590 return;
1591 end if;
1592
1593 --- Validate routing id
1594 If p_routing_id is not null and i_direction = 'IN' then
1595 num := 0;
1596 open c3;
1597 fetch c3 into num;
1598 close c3;
1599
1600 if (num = 0) then
1601 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1602 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_ROUTING_ID',
1603 'p_routing_id',p_routing_id);
1604 return;
1605 end if;
1606 end if;
1607
1608 -- get map_id, if map_id doesn't exists, return an error.
1609 i_map_id := 0;
1610 open c1;
1611 fetch c1 into i_map_id;
1612 close c1;
1613
1614 if (i_map_id = 0 ) then
1615 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1616 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_MAP_CODE',
1617 'p_map_code',p_map_code);
1618 return;
1619 end if;
1620
1621 -- validate confirmation
1622 if (p_confirmation is not null)
1623 then
1624 if not(ECX_UTIL_API.validate_confirmation_code(p_confirmation)) then
1625 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1626 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_CONF_CODE',
1627 'p_confirmation', p_confirmation);
1628 return;
1629 end if;
1630 l_confirmation := p_confirmation;
1631 else
1632 l_confirmation := 0;
1633 end if;
1634
1635 -- validate the connection_type ,protocol_type
1636 if (i_direction = 'OUT') then
1637 if (p_connection_type is null)
1638 then
1639 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1640 x_msg := ecx_debug.getTranslatedMessage('ECX_CONNECTION_TYPE_NOT_NULL');
1641 return;
1642 end if;
1643
1644 if(p_protocol_type is null) then
1645 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1646 x_msg := ecx_debug.getTranslatedMessage('ECX_PROTOCOL_TYPE_NOT_NULL');
1647 return;
1648
1649 elsif (not(ECX_UTIL_API.validate_protocol_type(p_protocol_type))) then
1650 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1651 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_PROTOCOL_TYPE',
1652 'p_protocol_type',p_protocol_type);
1653 return;
1654 end if;
1655
1656 /* Start changes for bug #2183619 */
1657 /* Check for uniqueness trading partner details row for OUTBOUND transactions */
1658
1659 /* Get the internal transaction type and sub type for corresponding to the
1660 ext_process_id */
1661 open c9(p_ext_process_id);
1662 fetch c9 into p_transaction_type,p_transaction_subtype;
1663 close c9;
1664
1665 /* Get the trading partner Header Information */
1666 open c10(p_tp_detail_id);
1667 fetch c10 into p_tp_header_id;
1668 close c10;
1669 if (p_tp_header_id is NULL) then
1670 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1671 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_TP_DETAIL_ID',
1672 'p_tp_detail_id',p_tp_detail_id);
1673 return;
1674 end if;
1675 num := 0;
1676 open c7(p_tp_header_id,p_tp_detail_id,
1677 p_transaction_type,p_transaction_subtype );
1678 fetch c7 into num;
1679 close c7;
1680 if (num <> 0) then
1681 x_return_status := ECX_UTIL_API.G_DUP_ERROR;
1682 x_msg := ecx_debug.getTranslatedMessage(
1683 'ECX_TP_DTL2_EXISTS',
1684 'p_tp_header_id', p_tp_header_id,
1685 'p_transaction_type', p_transaction_type,
1686 'p_transaction_subtype', p_transaction_subtype
1687 );
1688 return;
1689 End If;
1690
1691 /* End of changes for bug #2183619*/
1692
1693 if (upper(p_connection_type) = 'DIRECT') Then
1694 l_connection_type := 'DIRECT';
1695 if p_protocol_type NOT IN ('NONE','IAS','ITG03') Then
1696
1697 if p_protocol_address is null Then
1698 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1699 x_msg := ecx_debug.getTranslatedMessage('ECX_PROTOCOL_ADDR_NOT_NULL');
1700 return;
1701 end if;
1702
1703
1704 /***
1705 If p_username is null Then
1706 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1707 x_msg := ecx_debug.getTranslatedMessage('ECX_USRNAME_NOT_NULL');
1708 return;
1709 end if;
1710 If p_password is null Then
1711 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1712 x_msg := ecx_debug.getTranslatedMessage('ECX_PWD_NOT_NULL');
1713 return;
1714 End If;
1715 ***/
1716
1717 if ( i_passupd_flag = 'Y')
1718 then
1719 if (p_username is not null)
1720 then
1721 --- Check password length
1722 if not(ECX_UTIL_API.validate_password_length(p_password)) then
1723 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1724 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_PWD_LEN');
1725 return;
1726 end if;
1727
1728 /* Added check for bug #2410173 */
1729 if not(ECX_UTIL_API.validate_password(x_password)) then
1730 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1731 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_PWD');
1732 return;
1733 end if;
1734
1735 --- Encrypt the password
1736 ecx_obfuscate.ecx_data_encrypt(
1737 l_input_string => x_password,
1738 l_output_string => encrypt_password,
1739 errmsg => x_msg,
1740 retcode => x_return_status);
1741 end if;
1742 end if;
1743 end if;
1744
1745 --- Check source_tp_location_code
1746 If p_source_tp_location_code is null Then
1747 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1748 x_msg := ecx_debug.getTranslatedMessage('ECX_LOCATION_NOT_NULL');
1749 return;
1750 End If;
1751 -- update ECX_TP_DETAILS.
1752 if (i_passupd_flag = 'Y')
1753 then
1754 Update ECX_TP_DETAILS set
1755 MAP_ID = i_map_id,
1756 EXT_PROCESS_ID = p_ext_process_id,
1757 CONNECTION_TYPE = l_connection_type,
1758 HUB_USER_ID = null,
1759 HUB_ID = null,
1760 PROTOCOL_TYPE = p_protocol_type,
1761 PROTOCOL_ADDRESS = p_protocol_address,
1762 USERNAME = p_username,
1763 PASSWORD = encrypt_password,
1764 ROUTING_ID = null,
1765 SOURCE_TP_LOCATION_CODE = p_source_tp_location_code,
1766 EXTERNAL_TP_LOCATION_CODE = p_external_tp_location_code,
1767 CONFIRMATION = l_confirmation,
1768 LAST_UPDATED_BY = 0,
1769 LAST_UPDATE_DATE = sysdate
1770 where tp_detail_id = p_tp_detail_id;
1771 elsif (i_passupd_flag = 'N')
1772 then
1773 Update ECX_TP_DETAILS set
1774 MAP_ID = i_map_id,
1775 EXT_PROCESS_ID = p_ext_process_id,
1776 CONNECTION_TYPE = l_connection_type,
1777 HUB_USER_ID = null,
1778 HUB_ID = null,
1779 PROTOCOL_TYPE = p_protocol_type,
1780 PROTOCOL_ADDRESS = p_protocol_address,
1781 ROUTING_ID = null,
1782 SOURCE_TP_LOCATION_CODE = p_source_tp_location_code,
1783 EXTERNAL_TP_LOCATION_CODE = p_external_tp_location_code,
1784 CONFIRMATION = l_confirmation,
1785 LAST_UPDATED_BY = 0,
1786 LAST_UPDATE_DATE = sysdate
1787 where tp_detail_id = p_tp_detail_id;
1788 end if;
1789
1790 if (sql%rowcount = 0) then
1791 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
1792 x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
1793 'p_table', 'ecx_tp_details',
1794 'p_param_name', 'Trading partner detail ID',
1795 'p_param_id', p_tp_detail_id);
1796 return;
1797 end if;
1798
1799 else -- Hub connection type
1800 --bug #2449729
1801 --Retrieve the hub_id from ecx_hubs
1802 num := 0;
1803 open c11;
1804 fetch c11 into num,i_hub_id;
1805 close c11;
1806
1807 if(num = 0) then
1808 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
1809 x_msg := ecx_debug.gettranslatedMessage('ECX_HUB_NOT_EXISTS',
1810 'p_connection_type',p_connection_type,
1811 'p_protocol_type',p_protocol_type);
1812 return;
1813 End If;
1814
1815 -- hub_user information is required only if protocol_type <> SMTP
1816 if (p_protocol_type <> 'SMTP') then
1817 if p_hub_user_id is null Then
1818 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1819 x_msg := ecx_debug.getTranslatedMessage('ECX_HUB_USER_ID_NOT_NULL');
1820 return;
1821 end if;
1822 end if;
1823
1824 -- for any protocol_type if hub user_id is provided, check if it is valid
1825 if (p_hub_user_id is not null)
1826 then
1827 num := 0;
1828 open c4;
1829 fetch c4 into num;
1830 close c4;
1831
1832 if (num = 0) then
1833 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1834 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_HUB_USER_ID',
1835 'p_hub_user_id',p_hub_user_id);
1836 return;
1837 end if;
1838 -- get the source_tp_location_code
1839 open get_hub_entity_code;
1840 fetch get_hub_entity_code
1841 into l_source_tp_location_code;
1842 close get_hub_entity_code;
1843
1844 else -- case for SMTP with no hub_user_id info
1845 -- set source_tp_location_code to the hub_name
1846 open get_src_loc_code (i_hub_id);
1847 fetch get_src_loc_code into l_source_tp_location_code;
1848 close get_src_loc_code;
1849 end if;
1850
1851 if (not p_source_tp_location_code is null)
1852 then
1853 if (l_source_tp_location_code <> p_source_tp_location_code)
1854 then
1855 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1856 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_LOCATION',
1857 'p_location_code', p_source_tp_location_code);
1858 return;
1859 end if;
1860 end if;
1861
1862 -- update ECX_TP_DETAILS.
1863 if (i_passupd_flag = 'Y')
1864 then
1865 Update ECX_TP_DETAILS set
1866 MAP_ID = i_map_id,
1867 EXT_PROCESS_ID = p_ext_process_id,
1868 CONNECTION_TYPE = p_connection_type,
1869 HUB_USER_ID = p_hub_user_id,
1870 HUB_ID = i_hub_id,
1871 PROTOCOL_TYPE = null,
1872 PROTOCOL_ADDRESS = null,
1873 USERNAME = null,
1874 PASSWORD = null,
1875 ROUTING_ID = null,
1876 EXTERNAL_TP_LOCATION_CODE = p_external_tp_location_code,
1877 CONFIRMATION = l_confirmation,
1878 LAST_UPDATED_BY = 0,
1879 LAST_UPDATE_DATE = sysdate
1880 where tp_detail_id = p_tp_detail_id;
1881 elsif (i_passupd_flag = 'N')
1882 then
1883 Update ECX_TP_DETAILS set
1884 MAP_ID = i_map_id,
1885 EXT_PROCESS_ID = p_ext_process_id,
1886 CONNECTION_TYPE = p_connection_type,
1887 HUB_USER_ID = p_hub_user_id,
1888 HUB_ID = i_hub_id,
1889 PROTOCOL_TYPE = null,
1890 PROTOCOL_ADDRESS = null,
1891 ROUTING_ID = null,
1892 EXTERNAL_TP_LOCATION_CODE = p_external_tp_location_code,
1893 CONFIRMATION = l_confirmation,
1894 LAST_UPDATED_BY = 0,
1895 LAST_UPDATE_DATE = sysdate
1896 where tp_detail_id = p_tp_detail_id;
1897 end if;
1898
1899 if (sql%rowcount = 0) then
1900 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
1901 x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
1902 'p_table', 'ecx_tp_details',
1903 'p_param_name', 'Trading partner detail ID',
1904 'p_param_id', p_tp_detail_id);
1905 return;
1906 end if;
1907 End IF;
1908
1909 else -- i_direction is 'IN'
1910 --- Validate routing id
1911 If p_routing_id is not null and
1912 i_direction = 'IN' then
1913 num := 0;
1914 open c5;
1915 fetch c5 into num;
1916 close c5;
1917
1918 if (num = 0) then
1919 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1920 x_msg := ecx_debug.gettranslatedMessage('ECX_INVALID_ROUTING_ID',
1921 'p_routing_id',p_routing_id);
1922 return;
1923 end if;
1924 end if;
1925
1926 --- Check source_tp_location_code
1927 If p_source_tp_location_code is null Then
1928 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1929 x_msg := ecx_debug.getTranslatedMessage('ECX_LOCATION_NOT_NULL');
1930 return;
1931 End If;
1932 /* Start changes for bug #2183619 */
1933 /* Check for uniqueness of ext_process_id and source_tp_location_code
1934 for inbound transactions */
1935
1936 open c8(p_ext_process_id);
1937 fetch c8 into p_ext_type,p_ext_subtype,p_standard_id,p_direction;
1938 num := 0;
1939 open c6(p_ext_type,p_ext_subtype,p_standard_id,p_direction,
1940 p_source_tp_location_code,p_tp_detail_id);
1941 fetch c6 into num;
1942 close c6;
1943 if (num <> 0) then
1944 x_return_status := ECX_UTIL_API.G_DUP_ERROR;
1945 x_msg := ecx_debug.getTranslatedMessage('ECX_TP_DTL1_EXISTS',
1946 'p_ext_type', p_ext_type,
1947 'p_ext_subtype', p_ext_subtype,
1948 'p_standard_id', p_standard_id,
1949 'p_source_tp_location_code', p_source_tp_location_code
1950 );
1951
1952 return;
1953 end if;
1954 /* End of changes for bug #2183619*/
1955
1956 -- update ECX_TP_DETAILS.
1957 if (i_passupd_flag = 'Y')
1958 then
1959 Update ECX_TP_DETAILS set
1960 MAP_ID = i_map_id,
1961 EXT_PROCESS_ID = p_ext_process_id,
1962 CONNECTION_TYPE = null,
1963 HUB_USER_ID = null,
1964 HUB_ID = null,
1965 PROTOCOL_TYPE = null,
1966 PROTOCOL_ADDRESS = null,
1967 USERNAME = null,
1968 PASSWORD = null,
1969 ROUTING_ID = p_routing_id,
1970 SOURCE_TP_LOCATION_CODE = p_source_tp_location_code,
1971 EXTERNAL_TP_LOCATION_CODE = p_external_tp_location_code,
1972 CONFIRMATION = l_confirmation,
1973 LAST_UPDATED_BY = 0,
1974 LAST_UPDATE_DATE = sysdate
1975 where tp_detail_id = p_tp_detail_id;
1976
1977 elsif (i_passupd_flag = 'N')
1978 then
1979 Update ECX_TP_DETAILS set
1980 MAP_ID = i_map_id,
1981 EXT_PROCESS_ID = p_ext_process_id,
1982 CONNECTION_TYPE = null,
1983 HUB_USER_ID = null,
1984 HUB_ID = null,
1985 PROTOCOL_TYPE = null,
1986 PROTOCOL_ADDRESS = null,
1987 ROUTING_ID = p_routing_id,
1988 SOURCE_TP_LOCATION_CODE = p_source_tp_location_code,
1989 EXTERNAL_TP_LOCATION_CODE = p_external_tp_location_code,
1990 CONFIRMATION = l_confirmation,
1991 LAST_UPDATED_BY = 0,
1992 LAST_UPDATE_DATE = sysdate
1993 where tp_detail_id = p_tp_detail_id;
1994 end if;
1995
1996 if (sql%rowcount = 0) then
1997 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
1998 x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
1999 'p_table', 'ecx_tp_details',
2000 'p_param_name', 'Trading partner detail ID',
2001 'p_param_id', p_tp_detail_id);
2002 return;
2003 end if;
2004 end if;
2005 exception
2006 when others then
2007 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
2008 x_msg := SQLERRM;
2009 End;
2010
2011 Procedure delete_tp_detail( x_return_status Out NOCOPY pls_integer,
2012 x_msg Out NOCOPY Varchar2,
2013 p_tp_detail_id In pls_integer ) Is
2014
2015 begin
2016
2017 x_return_status := ECX_UTIL_API.G_NO_ERROR;
2018 x_msg := null;
2019
2020 If p_tp_detail_id is null Then
2021 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
2022 x_msg := ecx_debug.getTranslatedMessage('ECX_TP_DTL_ID_NOT_NULL');
2023 return;
2024 End If;
2025
2026 delete from ecx_tp_details
2027 where tp_detail_id = p_tp_detail_id;
2028
2029 if (sql%rowcount = 0) then
2030 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
2031 x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_DELETED',
2032 'p_table', 'ecx_tp_details',
2033 'p_param_name', 'Trading partner detail ID',
2034 'p_param_id', p_tp_detail_id);
2035 return;
2036 end if;
2037
2038 exception
2039 when others then
2040 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
2041 x_msg := SQLERRM;
2042 End;
2043 /* This API will be called to create the parametres and then synch up the
2044 ** trading partners with the workflow directories.
2045 **
2046 */
2047 procedure ecx_tp_synch_wf(
2048 org_name in Varchar2,
2049 party_name_or_site in Varchar2,
2050 party_or_site_id in Varchar2,
2051 email_addr in Varchar2,
2052 p_mod_type in Varchar2
2053 )
2054 is
2055 p_params wf_parameter_list_t;
2056
2057 begin
2058
2059 p_params := wf_parameter_list_t();
2060
2061 wf_event.addParameterToList(
2062 p_name => 'USER_NAME',
2063 p_value => org_name ||':'||party_or_site_id,
2064 p_parameterlist => p_params);
2065
2066 wf_event.addParameterToList(
2067 p_name => 'DisplayName',
2068 p_value => party_name_or_site,
2069 p_parameterlist => p_params);
2070
2071 wf_event.addParameterToList(
2072 p_name => 'mail',
2073 p_value => email_addr,
2074 p_parameterlist => p_params);
2075 if(p_mod_type='DELETE') then
2076 wf_local_synch.propagate_role(
2077 p_orig_system => org_name,
2078 p_orig_system_id =>party_or_site_id ,
2079 p_attributes => p_params,
2080 p_expiration_date => sysdate
2081 );
2082 else
2083 wf_local_synch.propagate_role(
2084 p_orig_system => org_name,
2085 p_orig_system_id =>party_or_site_id ,
2086 p_attributes => p_params,
2087 p_start_date => sysdate
2088 );
2089 end if;
2090 END ecx_tp_synch_wf;
2091
2092 procedure raise_tp_event(
2093 x_return_status out NOCOPY pls_integer,
2094 x_msg out NOCOPY varchar2,
2095 x_event_name out NOCOPY varchar2,
2096 x_event_key out NOCOPY number,
2097 p_mod_type in varchar2,
2098 p_tp_header_id in number,
2099 p_party_type in varchar2,
2100 p_party_id in varchar2,
2101 p_party_site_id in varchar2,
2102 p_company_email_addr in varchar2
2103 )
2104 is
2105 l_event_name varchar2(250);
2106 l_event_key number;
2107 l_params wf_parameter_list_t;
2108
2109 org_table_name varchar2(350);
2110 party_name varchar2(350);
2111 org_site_table_name varchar2(350);
2112 org_role_name varchar2(350);
2113 org_site_role_name varchar2(350);
2114 party_site_loc varchar2(350);
2115 /*
2116 ** This API is called when ecx is synching up the data b/w ecx_tp_headers and the wf directories. **
2117 ** Please refer to the bug 4734256 for details.
2118 */
2119 cursor internal_party_name(v_party_id varchar) is
2120 select LOCATION_CODE from hr_locations where LOCATION_ID=v_party_id;
2121
2122 cursor internal_site_name(v_party_id varchar) is
2123 select ADDRESS_LINE_1||ADDRESS_LINE_2||ADDRESS_LINE_3 ||town_or_city||country||postal_code from hr_locations
2124 where location_id =v_party_id ;
2125
2126 cursor bank_party_name(v_party_id varchar) is
2127 select BANK_NAME from CE_BANK_BRANCHES_V where BRANCH_PARTY_ID=v_party_id;
2128
2129 cursor bank_site_name(v_party_id varchar) is
2130 select address_line1||' '||address_line2||' '||address_line3||' '||CITY||' '||ZIP from CE_BANK_BRANCHES_V where BRANCH_PARTY_ID=v_party_id;
2131
2132 cursor supplier_party_name(v_party_id varchar) is
2133 select p.vendor_name from PO_VENDORS p where p.vendor_ID =v_party_id ;
2134
2135 cursor supplier_site_name(v_party_id varchar,v_party_site_id varchar) is
2136 select p1.ADDRESS_LINE1||' '||p1.ADDRESS_LINE2||' '||p1.ADDRESS_LINE3||' '||p1.CITY||p1.ZIP from PO_VENDOR_SITES_ALL p1
2137 where p1.VENDOR_SITE_ID =v_party_site_id and p1.VENDOR_ID=v_party_id;
2138
2139 cursor customer_party_name(v_party_id varchar) is
2140 select PARTY_NAME from hz_parties where party_id=v_party_id;
2141
2142 cursor customer_site_name(v_party_id varchar,v_party_site_id varchar) is
2143 select ADDRESS1 ||ADDRESS2 || ADDRESS3 || ADDRESS4 ||CITY ||POSTAL_CODE ||STATE ||PROVINCE || COUNTY||COUNTRY from hz_locations where location_id =(select location_id from hz_party_sites where party_id=v_party_id and party_site_id=v_party_site_id);
2144 cursor org_table(v_party_id varchar) is
2145 select decode(party_type,'C','HZ_PARTIES','EXCHANGE','HZ_PARTIES','CARRIER','HZ_PARTIES','S','PO_VENDORS','I','HR_LOCATIONS','B','CE_BANK_BRANCHES_V') from ecx_tp_headers where party_id =v_party_id ;
2146
2147 cursor org_site_table(v_party_id varchar) is
2148 select decode(party_type,'C','HZ_PARTY_SITES','EXCHANGE','HZ_PARTY_SITES','CARRIER','HZ_PARTY_SITES','S','PO_VENDOR_SITES_ALL','I','HR_LOCATIONS_SITES','B','CE_BANK_BRANCHES_SITE') from ecx_tp_headers where party_id =v_party_id ;
2149
2150
2151 /* This cursor "orig_site_role" is defined to get the name of the orig_system from wf_local_roles
2152 ** This would be used when we are deleting a Trading Partner**
2153 ** As the above cursors can not be used, because they are based on the ecx_tp_headers table**
2154 */
2155 cursor orig_site_role is
2156 select decode(p_party_type,'C','HZ_PARTY_SITES','EXCHANGE','HZ_PARTY_SITES','CARRIER','HZ_PARTY_SITES','S','PO_VENDOR_SITES','I','HR_LOCATIONS_SITES','B','CE_BANK_BRANCHES_SITE') from dual;
2157
2158 cursor orig_role is
2159 select decode(p_party_type,'C','HZ_PARTIES','EXCHANGE','HZ_PARTIES','CARRIER','HZ_PARTIES','S','PO_VENDORS','I','HR_LOCATIONS','B','CE_BANK_BRANCHES_V') from dual;
2160
2161 begin
2162 x_return_status := ECX_UTIL_API.G_NO_ERROR;
2163 x_msg := null;
2164 x_event_key := -1;
2165 x_event_name := null;
2166
2167 l_event_name := 'oracle.apps.ecx.tp.modified';
2168 l_event_key := p_tp_header_id || wf_core.random;
2169
2170 l_params := wf_parameter_list_t();
2171
2172 wf_event.addParameterToList(p_name => 'ECX_TP_MOD_TYPE',
2173 p_value => p_mod_type,
2174 p_parameterlist => l_params);
2175 wf_event.addParameterToList(p_name => 'ECX_TP_HEADER_ID',
2176 p_value => p_tp_header_id,
2177 p_parameterlist => l_params);
2178 wf_event.addParameterToList(p_name => 'ECX_PARTY_TYPE',
2179 p_value => p_party_type,
2180 p_parameterlist => l_params);
2181 wf_event.addParameterToList(p_name => 'ECX_PARTY_ID',
2182 p_value => p_party_id,
2183 p_parameterlist => l_params);
2184 wf_event.addParameterToList(p_name => 'ECX_PARTY_SITE_ID',
2185 p_value => p_party_site_id,
2186 p_parameterlist => l_params);
2187 wf_event.addParameterToList(p_name => 'ECX_COMPANY_ADMIN_EMAIL',
2188 p_value => p_company_email_addr,
2189 p_parameterlist => l_params);
2190
2191 wf_event.raise(l_event_name, l_event_key, null, l_params);
2192
2193
2194
2195
2196 if (p_party_type='I') then
2197 open internal_party_name(p_party_id);
2198 fetch internal_party_name into party_name;
2199 close internal_party_name;
2200
2201 open internal_site_name(p_party_id);
2202 fetch internal_site_name into party_site_loc;
2203 close internal_site_name;
2204 end if;
2205
2206 if(p_party_type='S') then
2207 open supplier_party_name(p_party_id);
2208 fetch supplier_party_name into party_name;
2209 close supplier_party_name;
2210
2211 open supplier_site_name(p_party_id,p_party_site_id);
2212 fetch supplier_site_name into party_site_loc;
2213 close supplier_site_name;
2214
2215 end if;
2216
2217 if(p_party_type='B') then
2218 open bank_party_name(p_party_id);
2219 fetch bank_party_name into party_name;
2220 close bank_party_name;
2221 open bank_site_name(p_party_id);
2222 fetch bank_site_name into party_site_loc;
2223 close bank_site_name;
2224
2225 end if;
2226
2227 if(p_party_type='C' OR p_party_type='CARRIER' OR p_party_type='EXCHANGE' ) then
2228
2229 open customer_party_name(p_party_id);
2230 fetch customer_party_name into party_name;
2231 close customer_party_name;
2232
2233 open customer_site_name(p_party_id,p_party_site_id);
2234 fetch customer_site_name into party_site_loc;
2235 close customer_site_name;
2236
2237 end if;
2238
2239
2240 if(p_mod_type='DELETE') then
2241 open orig_role;
2242 fetch orig_role into org_table_name;
2243 close orig_role;
2244
2245 open orig_site_role;
2246 fetch orig_site_role into org_site_table_name;
2247 close orig_site_role;
2248
2249 else
2250 open org_table(p_party_id);
2251 fetch org_table into org_table_name;
2252 close org_table;
2253
2254 open org_site_table(p_party_id);
2255 fetch org_site_table into org_site_table_name;
2256 close org_site_table;
2257 end if;
2258
2259
2260 ECX_TP_API.ecx_tp_synch_wf(org_table_name,party_name,p_party_id,p_company_email_addr,p_mod_type);
2261 ECX_TP_API.ecx_tp_synch_wf(org_site_table_name,party_site_loc,p_party_site_id,p_company_email_addr,p_mod_type);
2262
2263
2264
2265 x_return_status := ECX_UTIL_API.G_NO_ERROR;
2266 x_msg := null;
2267 x_event_name := l_event_name;
2268 x_event_key := l_event_key;
2269 exception
2270 when others then
2271 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
2272 x_msg := SQLERRM;
2273 End;
2274 End;