[Home] [Help]
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
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
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
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;
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;
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;
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);
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);
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(
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:
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.
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
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
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
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;
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;
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;
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;
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;
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;
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;
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;
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
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);
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(
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:
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
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:
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;
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;
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);
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;
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);
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:
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(
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;
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;
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:
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);
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;
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);
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:
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
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;
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
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 */
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;
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:
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
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
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
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
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
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
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
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
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
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
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,
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,
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:
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
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
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:
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;
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;
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;
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;
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;
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:
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
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;
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;
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
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:
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;
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 */
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;
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:
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:
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;
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;
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;
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;
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 */
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,
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
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:
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.
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
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
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;
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.
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
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
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);
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;
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;
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;
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;
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:
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
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;
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;
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;
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,
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:
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
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: ***/
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;
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:
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;
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:
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.
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);
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;
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;
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;
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;
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);
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 */
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,
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);
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,
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;
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);
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.
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:
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
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;