[Home] [Help]
PACKAGE BODY: APPS.ECX_TRADING_PARTNER_PVT
Source
1 PACKAGE BODY ECX_Trading_Partner_PVT AS
2 -- $Header: ECXTPXFB.pls 120.6.12010000.2 2008/08/22 19:21:32 cpeixoto ship $
3
4 /** Returns the trading partners Details as defined in the partner Setup **/
5 procedure get_tp_info
6 (
7 p_tp_header_id IN pls_integer,
8 p_party_id OUT NOCOPY NUMBER,
9 p_party_site_id OUT NOCOPY NUMBER,
10 p_org_id OUT NOCOPY pls_integer,
11 p_admin_email OUT NOCOPY varchar2,
12 retcode OUT NOCOPY pls_integer,
13 retmsg OUT NOCOPY varchar2
14 )
15 is
16
17 p_party_type varchar2(200);
18
19 cursor cinfo is
20 select party_id,
21 party_site_id,
22 party_type,
23 company_admin_email
24 from ecx_tp_headers
25 where tp_header_id = p_tp_header_id;
26
27 l_CursorID NUMBER;
28 l_result NUMBER;
29 l_Select VARCHAR2(2400);
30 begin
31 for c_info in cinfo
32 loop
33 p_party_id := c_info.party_id;
34 p_party_site_id := c_info.party_site_id;
35 p_party_type := c_info.party_type;
36 p_admin_email := c_info.company_admin_email;
37 end loop;
38
39 if p_party_site_id is null
40 then
41 retcode :=0;
42 retmsg := ecx_debug.getTranslatedMessage('ECX_INVALID_TP_HDR_ID',
43 'p_tp_header_id', p_tp_header_id);
44 ecx_debug.setErrorInfo(1,30,
45 'ECX_INVALID_TP_HDR_ID', 'p_tp_header_id', p_tp_header_id);
46 return;
47 end if;
48
49 /** Try to derive the org_id for the transaction **/
50 if p_party_type is not null
51 then
52 /** Customer **/
53 if p_party_type = 'C'
54 then
55 l_Select := ' select haa.org_id' ||
56 ' from hz_cust_acct_sites_all haa ,' ||
57 ' hz_cust_accounts ha ' ||
58 ' where ha.cust_account_id = :party_id' ||
59 ' and haa.cust_account_id = ha.cust_account_id' ||
60 ' and haa.cust_acct_site_id= :party_site_id ';
61
62 /** Vendor **/
63 elsif p_party_type = 'S'
64 then
65 l_Select := ' select org_id' ||
66 ' from po_vendor_sites_all' ||
67 ' where vendor_id = :party_id' ||
68 ' and vendor_site_id = :party_site_id ';
69
70 else
71 ecx_debug.setErrorInfo(1,30, 'ECX_INVALID_PARTY_TYPE', 'p_party_type',p_party_type);
72 retcode := 1;
73 retmsg := ecx_debug.getTranslatedMessage('ECX_INVALID_PARTY_TYPE',
74 'p_party_type',p_party_type);
75 return;
76 end if;
77
78 l_CursorID := DBMS_SQL.OPEN_CURSOR;
79 DBMS_SQL.PARSE(l_CursorID, l_Select, DBMS_SQL.V7);
80 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':party_id', p_party_id);
81 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':party_site_id',
82 p_party_site_id);
83 DBMS_SQL.DEFINE_COLUMN(l_CursorID, 1, p_org_id);
84 l_result := DBMS_SQL.EXECUTE(l_CursorID);
85 IF DBMS_SQL.FETCH_ROWS(l_CursorID) <> 0 THEN
86 DBMS_SQL.COLUMN_VALUE(l_CursorID, 1, p_org_id);
87 END IF;
88 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
89 end if;
90
91 exception
92 when others then
93 ecx_debug.setErrorInfo(2,30, SQLERRM || '- ECX_TRADING_PARTNER_PVT.GET_TP_INFO');
94 retcode := 2;
95 retmsg := SQLERRM;
96 end get_tp_info;
97
98 PROCEDURE Get_Address_id
99 (
100 p_location_code_ext IN VARCHAR2,
101 p_info_type IN VARCHAR2,
102 p_entity_address_id OUT NOCOPY pls_integer,
103 p_org_id OUT NOCOPY pls_integer,
104 retcode OUT NOCOPY pls_integer,
105 retmsg OUT NOCOPY varchar2
106 )
107 IS
108
109 l_CursorID NUMBER;
110 l_result NUMBER;
111 l_Select VARCHAR2(2000);
112 BEGIN
113
114 if ( p_info_type = ECX_Trading_Partner_PVT.G_CUSTOMER)
115 then
116 l_Select := ' select haa.cust_acct_site_id,' ||
117 ' haa.org_id' ||
118 ' from hz_cust_acct_sites_all haa' ||
119 ' where haa.ece_tp_location_code = :location_code_ext ';
120
121 elsif (p_info_type = ECX_Trading_Partner_PVT.G_SUPPLIER)
122 then
123 l_Select := ' select pvs.vendor_site_id,' ||
124 ' pvs.org_id' ||
125 ' from po_vendor_sites_all pvs' ||
126 ' where pvs.ece_tp_location_code = :location_code_ext ';
127
128 elsif (p_info_type = ECX_Trading_Partner_PVT.G_BANK)
129 then
130 l_Select := ' select cbb.branch_party_id, null' ||
131 ' from ce_bank_branches_v cbb, hz_contact_points hcp' ||
132 ' where cbb.branch_party_id=hcp.owner_table_id and
133 hcp.owner_table_name = ''HZ_PARTIES'' and
134 hcp.contact_point_type = ''EDI'' and
135 hcp.edi_ece_tp_location_code = :location_code_ext ';
136
137 elsif (p_info_type = ECX_Trading_Partner_PVT.G_LOCATION)
138 then
139 l_Select := ' select location_id, null' ||
140 ' from hr_locations_all' ||
141 ' where ece_tp_location_code = :location_code_ext ';
142
143 else
144 ecx_debug.setErrorInfo(1,30, 'ECX_INVALID_ADDRESS_TYPE', 'p_address_type',p_info_type);
145 retcode := 1;
146 retmsg := ecx_debug.getTranslatedMessage( 'ECX_INVALID_ADDRESS_TYPE',
147 'p_address_type',p_info_type);
148 return;
149 end if;
150
151 l_CursorID := DBMS_SQL.OPEN_CURSOR;
152 DBMS_SQL.PARSE(l_CursorID, l_Select, DBMS_SQL.V7);
153 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':location_code_ext',
154 p_location_code_ext);
155 DBMS_SQL.DEFINE_COLUMN(l_CursorID, 1, p_entity_address_id);
156 DBMS_SQL.DEFINE_COLUMN(l_CursorID, 2, p_org_id);
157 l_result := DBMS_SQL.EXECUTE(l_CursorID);
158 IF DBMS_SQL.FETCH_ROWS(l_CursorID) <> 0 THEN
159 DBMS_SQL.COLUMN_VALUE(l_CursorID, 1, p_entity_address_id);
160 DBMS_SQL.COLUMN_VALUE(l_CursorID, 2, p_org_id);
161 END IF;
162 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
163
164
165 if p_entity_address_id is NULL
166 then
167 ecx_debug.setErrorInfo(1,30,'ECX_ADDR_DERIVATION_ERR', 'p_address_type',p_info_type,
168 'p_location_code', p_location_code_ext);
169 retcode := 1;
170 retmsg := ecx_debug.getTranslatedMessage('ECX_ADDR_DERIVATION_ERR',
171 'p_address_type',p_info_type,
172 'p_location_code', p_location_code_ext);
173 end if;
174
175
176 EXCEPTION
177 WHEN OTHERS THEN
178 ecx_debug.setErrorInfo(2,30,
179 SQLERRM ||' - ECX_TRADING_PARTNER_PVT.Get_TP_Address');
180 retcode := 2;
181 retmsg := SQLERRM;
182 end Get_Address_id;
183
184 /** Receivers TP Info **/
185 procedure get_receivers_tp_info
186 (
187 p_party_id OUT NOCOPY NUMBER,
188 p_party_site_id OUT NOCOPY NUMBER,
189 p_org_id OUT NOCOPY pls_integer,
190 p_admin_email OUT NOCOPY varchar2,
191 retcode OUT NOCOPY pls_integer,
192 retmsg OUT NOCOPY varchar2
193 )
194 is
195 begin
196 if ecx_utils.g_rec_tp_id is not null
197 then
198 get_tp_info
199 (
200 p_tp_header_id => ECX_UTILS.G_rec_tp_id,
201 p_party_id => p_party_id,
202 p_party_site_id => p_party_site_id,
203 p_org_id => p_org_id,
204 p_admin_email => p_admin_email,
205 retcode => retcode,
206 retmsg => retmsg
207 );
208 else
209 ecx_debug.setErrorInfo(1,30,'ECX_RCVR_NOT_SETUP', 'p_tp_header_id', ECX_UTILS.G_rec_tp_id);
210 retcode := 1;
211 retmsg := ecx_debug.getTranslatedMessage('ECX_RCVR_NOT_SETUP');
212 end if;
213 EXCEPTION
214 WHEN OTHERS THEN
215 ecx_debug.setErrorInfo(2,30,
216 SQLERRM ||' - ECX_TRADING_PARTNER_PVT. GET_RECEIVERS_TP_INFO');
217 retcode := 2;
218 retmsg := SQLERRM ||' - ECX_TRADING_PARTNER_PVT. GET_RECEIVERS_TP_INFO';
219 end get_receivers_tp_info;
220
221 /** Senders TP Info **/
222 procedure get_senders_tp_info
223 (
224 p_party_id OUT NOCOPY NUMBER,
225 p_party_site_id OUT NOCOPY NUMBER,
226 p_org_id OUT NOCOPY pls_integer,
227 p_admin_email OUT NOCOPY varchar2,
228 retcode OUT NOCOPY pls_integer,
229 retmsg OUT NOCOPY varchar2
230 )
231 is
232 begin
233 if ecx_utils.g_snd_tp_id is not null
234 then
235 get_tp_info
236 (
237 p_tp_header_id => ECX_UTILS.G_snd_tp_id,
238 p_party_id => p_party_id,
239 p_party_site_id => p_party_site_id,
240 p_org_id => p_org_id,
241 p_admin_email => p_admin_email,
242 retcode => retcode,
243 retmsg => retmsg
244 );
245 else
246 ecx_debug.setErrorInfo(1,30, 'ECX_SNDR_NOT_ENABLED','p_tp_header_id',ECX_UTILS.G_snd_tp_id);
247 retcode := 1;
248 retmsg := ecx_debug.getTranslatedMessage('ECX_SNDR_NOT_SETUP');
249 end if;
250
251 EXCEPTION
252 WHEN OTHERS THEN
253 ecx_debug.setErrorInfo(2,30,
254 SQLERRM ||' - ECX_TRADING_PARTNER_PVT.GET_SENDERS_TP_INFO');
255 retcode := 2;
256 retmsg := SQLERRM;
257 end get_senders_tp_info;
258
259 /** Get TP Company email ****/
260 procedure get_tp_company_email(l_transaction_type IN varchar2,
261 l_transaction_subtype IN varchar2,
262 l_party_site_id IN number,
263 l_party_type IN varchar2 , --bug #2183619
264 l_email_addr OUT NOCOPY varchar2,
265 retcode OUT NOCOPY pls_integer,
266 errmsg OUT NOCOPY varchar2) IS
267 BEGIN
268 -- Added check for party type for bug #2183619
269 Select eth.company_admin_email
270 Into l_email_addr
271 From ecx_tp_headers eth,
272 ecx_transactions et
273 where eth.party_type = et.party_type
274 and et.transaction_type = l_transaction_type
275 and et.transaction_subtype = l_transaction_subtype
276 and eth.party_site_id = l_party_site_id
277 and (l_party_type is null or et.party_type = l_party_type);
278
279
280 ecx_utils.error_type := 10;
281 retcode := 0;
282
283 Exception
284 When no_data_found Then
285 ecx_debug.setErrorInfo(1,30,'ECX_NO_EMAIL_ADDR',
286 'p_transaction_type', l_transaction_type,
287 'p_transaction_subtype', l_transaction_subtype,
288 'p_party_type', l_party_type,
289 'p_party_site_id', l_party_site_id);
290
291 /* Start of bug #2183619*/
292 when too_many_rows then
293 ecx_debug.setErrorInfo(2,30,'ECX_PARTY_TYPE_NOT_SET');
294 raise ecx_utils.program_exit;
295 /* End of bug #2183619 */
296
297 When Others Then
298 retcode := 2;
299 errmsg := SQLERRM || ' At ECX_TRADING_PARTNER_PVT.GET_TP_COMPANY_EMAIL';
300 ecx_debug.setErrorInfo(2,30,SQLERRM);
301 End Get_TP_Company_Email;
302
303 /** Get System Adminstrator Email ***/
304 Procedure get_sysadmin_email(email_address OUT NOCOPY varchar2,
305 retcode OUT NOCOPY pls_integer,
306 errmsg OUT NOCOPY varchar2)
307 Is
308
309 l_String VARCHAR2(2000);
310 l_instlmode VARCHAR2(100);
311 l_CursorID NUMBER;
312 l_result NUMBER;
313 l_profile VARCHAR2(100) := 'ECX_SYS_ADMIN_EMAIL';
314 Begin
315
316 -- Obtain the installation type - STANALONE/EMBEDDED.
317 l_instlmode := wf_core.translate('WF_INSTALL');
318
319 IF l_instlmode = 'EMBEDDED' THEN
320 null;
321
322 l_String := 'BEGIN
323 --fnd_profile.get(:profile_name,:email_address);
324 :email_address:=
325 fnd_profile.value_specific(
326 name=>:l_profile,user_id=>0,responsibility_id=>20420,
327 application_id=>174,org_id=>null,server_id=>null);
328 END;';
329 l_CursorID := DBMS_SQL.OPEN_CURSOR;
330 DBMS_SQL.PARSE(l_CursorID, l_String, DBMS_SQL.V7);
331 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':l_profile', l_profile);
332 /* Bug# 2243620 - email_address default length is 0 and the email
333 address returned is null unless bind_out_value is specified to be 2000*/
334 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':email_address', email_address, 2000);
335 l_result := DBMS_SQL.EXECUTE(l_CursorID);
336 DBMS_SQL.VARIABLE_VALUE(l_CursorID, ':email_address', email_address);
337 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
338 ELSE
339 email_address := wf_core.translate('ECX_SYS_ADMIN_EMAIL');
340 END IF;
341
342 ecx_utils.error_type := 10;
343 retcode := 0;
344 Exception
345 When Others Then
346 ecx_debug.setErrorInfo(2,30,
347 SQLERRM || ' - ECX_TRADING_PARTNER_PVT.get_sysadmin_email');
348 retcode := 2;
349 errmsg := SQLERRM;
350 End;
351
352 /** Get TP Details given party_type, party_id, party_site_id, trxn type trxn subtype **/
353
354 Procedure get_tp_details (p_party_type IN varchar2,
355 p_party_id IN number,
356 p_party_site_id IN number,
357 p_transaction_type IN varchar2,
358 p_transaction_subtype IN varchar2,
359 p_protocol_type OUT NOCOPY varchar2,
360 p_protocol_address OUT NOCOPY varchar2,
361 p_username OUT NOCOPY varchar2,
362 p_password OUT NOCOPY varchar2,
363 p_retcode OUT NOCOPY pls_integer,
364 p_errmsg OUT NOCOPY varchar2) IS
365
366 Begin
367 If p_party_type is NOT NULL Then
368 If p_party_id is NOT NULL Then
369 If p_party_site_id is NOT NULL Then
370
371 Select etpd.protocol_type, etpd.protocol_address,
372 etpd.username, etpd.password
373 Into p_protocol_type, p_protocol_address,
374 p_username, p_password
375 From ECX_TP_HEADERS etph, ECX_TP_DETAILS_V etpd
376 Where etph.party_type = p_party_type
377 And etph.party_id = p_party_id
378 And etph.party_site_id = p_party_site_id
379 And etpd.tp_header_id = etph.tp_header_id
380 And etpd.transaction_type = p_transaction_type
381 And etpd.transaction_subtype = p_transaction_subtype;
382
383 ecx_utils.error_type := 10;
384 p_retcode := 0;
385 Else
386 ecx_debug.setErrorInfo(1,30, 'ECX_PARTY_SITE_ID_NOT_NULL');
387 End If;
388 Else
389 ecx_debug.setErrorInfo(1,30, 'ECX_PARTY_ID_NOT_NULL');
390 End If;
391 Else
392 ecx_debug.setErrorInfo(1,30, 'ECX_PARTY_TYPE_NOT_NULL');
393 End IF;
394 p_retcode := ecx_utils.i_ret_code;
395 p_errmsg := ecx_utils.i_errbuf;
396 EXCEPTION
397 When no_data_found Then
398 p_retcode := 1;
399 p_errmsg := ecx_debug.getTranslatedMessage('ECX_NO_UNIQUE_TP_SETUP');
400 ecx_debug.setErrorInfo(1,30,
401 'ECX_NO_UNIQUE_TP_SETUP');
402 When Others Then
403 ecx_debug.setErrorInfo(2,30,
404 SQLERRM || ' At ECX_TRADING_PARTNER_PVT.GET_TP_DETAILS');
405 p_retcode := 2;
406 p_errmsg := SQLERRM || ' At ECX_TRADING_PARTNER_PVT.GET_TP_DETAILS';
407 END get_tp_details;
408
409 /** Get error type***/
410 Procedure get_error_type ( i_error_type OUT NOCOPY pls_integer,
411 retcode OUT NOCOPY pls_integer,
412 errmsg OUT NOCOPY varchar2) Is
413 Begin
414 i_error_type := ecx_utils.error_type;
415 retcode := 0;
416 Exception
417 When Others Then
418 ecx_debug.setErrorInfo(2,30, SQLERRM);
419 retcode := 0;
420 errmsg := SQLERRM;
421 End get_error_type;
422
423 procedure getEnvelopeInformation
424 (
425 i_internal_control_number in pls_integer,
426 i_message_type OUT NOCOPY varchar2,
427 i_message_standard OUT NOCOPY varchar2,
428 i_transaction_type OUT NOCOPY varchar2,
429 i_transaction_subtype OUT NOCOPY varchar2,
430 i_document_number OUT NOCOPY varchar2,
431 i_party_id OUT NOCOPY varchar2,
432 i_party_site_id OUT NOCOPY varchar2,
433 i_protocol_type OUT NOCOPY varchar2,
434 i_protocol_address OUT NOCOPY varchar2,
435 i_username OUT NOCOPY varchar2,
436 i_password OUT NOCOPY varchar2,
437 i_attribute1 OUT NOCOPY varchar2,
438 i_attribute2 OUT NOCOPY varchar2,
439 i_attribute3 OUT NOCOPY varchar2,
440 i_attribute4 OUT NOCOPY varchar2,
441 i_attribute5 OUT NOCOPY varchar2,
442 retcode OUT NOCOPY pls_integer,
443 retmsg OUT NOCOPY varchar2
444 )
445 is
446
447 cursor get_msg_attributes(p_icn in pls_integer)
448 is
449 select message_type,
450 message_standard,
451 transaction_type,
452 transaction_subtype,
453 document_number,
454 partyid,
455 party_site_id,
456 protocol_type,
457 protocol_address,
458 username,
459 password,
460 attribute1,
461 attribute2,
462 attribute3,
463 attribute4,
464 attribute5
465 from ecx_doclogs
466 where internal_control_number = p_icn;
467
468 i_get_msg_attributes get_msg_attributes%ROWTYPE;
469 begin
470 open get_msg_attributes(p_icn => i_internal_control_number);
471 fetch get_msg_attributes into i_get_msg_attributes;
472
473 i_message_type := i_get_msg_attributes.message_type;
474 i_message_standard := i_get_msg_attributes.message_standard;
475 i_transaction_type := i_get_msg_attributes.transaction_type;
476 i_transaction_subtype := i_get_msg_attributes.transaction_subtype;
477 i_document_number := i_get_msg_attributes.document_number;
478 i_party_id := i_get_msg_attributes.partyid;
479 i_party_site_id := i_get_msg_attributes.party_site_id;
480 i_protocol_type := i_get_msg_attributes.protocol_type;
481 i_protocol_address := i_get_msg_attributes.protocol_address;
482 i_username := i_get_msg_attributes.username;
483 i_password := i_get_msg_attributes.password;
484 i_attribute1 := i_get_msg_attributes.attribute1;
485 i_attribute2 := i_get_msg_attributes.attribute2;
486 i_attribute3 := i_get_msg_attributes.attribute3;
487 i_attribute4 := i_get_msg_attributes.attribute4;
488 i_attribute5 := i_get_msg_attributes.attribute5;
489
490 if get_msg_attributes%NOTFOUND
491 then
492 ecx_debug.setErrorInfo(1,30, 'ECX_NO_ENVELOPE',
493 'p_icn', i_internal_control_number);
494 retcode := 1;
495 retmsg := ecx_debug.getTranslatedMessage('ECX_NO_ENVELOPE',
496 'p_icn', i_internal_control_number);
497 if get_msg_attributes%ISOPEN
498 then
499 close get_msg_attributes;
500 end if;
501
502 return;
503 end if;
504
505 retcode :=0;
506
507 if get_msg_attributes%ISOPEN
508 then
509 close get_msg_attributes;
510 end if;
511 exception
512 when others then
513 if get_msg_attributes%ISOPEN
514 then
515 close get_msg_attributes;
516 end if;
517
518 ecx_debug.setErrorInfo(2,30, SQLERRM || ' -ECX_TRADING_PARTNER_PVT.getEnvelopeInformation');
519 retcode :=2;
520 retmsg := SQLERRM || ' -ECX_TRADING_PARTNER_PVT.getEnvelopeInformation';
521 end getEnvelopeInformation;
522
523 procedure setOriginalReferenceId
524 (
525 i_internal_control_number in varchar2,
526 i_original_reference_id in varchar2,
527 retcode OUT NOCOPY pls_integer,
528 retmsg OUT NOCOPY varchar2
529 )
530 is
531 begin
532 retcode := 0;
533 retmsg := null;
534
535 update ecx_doclogs
536 set orig_reference_id = i_original_reference_id
537 where internal_control_number = i_internal_control_number;
538
539 exception
540 when others then
541 retcode := 2;
542 retmsg := substr(SQLERRM,1,200);
543 ecx_debug.setErrorInfo(2,30,
544 substr(SQLERRM,1,200) || ' -ECX_TRADING_PARTNER_PVT.setOriginalReferenceId');
545 end setOriginalReferenceId;
546
547
548 function getOAGLOGICALID
549 return varchar2
550 is
551 i_string varchar2(2000);
552 begin
553 --- Check for the Installation Type ( Standalone or Embedded );
554 ecx_utils.g_install_mode := wf_core.translate('WF_INSTALL');
555 if ecx_utils.g_install_mode = 'EMBEDDED'
556 then
557 i_string := 'begin
558 fnd_profile.get('||'''ECX_OAG_LOGICALID'''||',ecx_trading_partner_pvt.g_oag_logicalid);
559 end;';
560 execute immediate i_string ;
561 else
562 ecx_trading_partner_pvt.g_oag_logicalid := wf_core.translate('ECX_OAG_LOGICALID');
563 end if;
564
565 return ecx_trading_partner_pvt.g_oag_logicalid;
566 exception
567 when others then
568 return null;
569 end getOAGLOGICALID;
570
571 Function IsUserAuthorized (p_user_name IN VARCHAR2,
572 p_tp_header_id IN PLS_INTEGER,
573 p_profile_value IN VARCHAR2)
574 Return Boolean is
575 preference_value varchar2(100);
576 profile_value varchar2(1);
577 Begin
578 profile_value := p_profile_value;
579 if (profile_value is null)
580 then
581 fnd_profile.get('ECX_USER_CHECK',profile_value);
582 end if;
583 if (nvl(profile_value,'N') = 'N') then
584 return true;
585 end if;
586 preference_value := fnd_preference.get(upper(p_user_name),'ECX','TP_ENABLED');
587 if (preference_value = p_tp_header_id) then
588 return true;
589 else
590 return false;
591 end if;
592 Exception
593 When Others then
594 return false;
595 End IsUserAuthorized;
596
597 function validateTPUser (
598 p_transaction_type IN VARCHAR2,
599 p_transaction_subtype IN VARCHAR2,
600 p_standard_code IN VARCHAR2,
601 p_standard_type IN VARCHAR2,
602 p_party_site_id IN VARCHAR2,
603 p_user_name IN VARCHAR2,
604 x_tp_header_id OUT NOCOPY NUMBER,
605 retcode OUT NOCOPY VARCHAR2,
606 errmsg OUT NOCOPY VARCHAR2)
607 return varchar2 is
608 x_queue_name varchar2(100);
609 p_tp_flag boolean;
610 p_user_flag boolean;
611 profile_value varchar2(1);
612 begin
613 fnd_profile.get('ECX_USER_CHECK',profile_value);
614 if (nvl(profile_value,'N') = 'N') then
615 profile_value := 'N';
616 return 'Y';
617 end if;
618
619 p_tp_flag := ecx_rule.isTPEnabled ( p_transaction_type,
620 p_transaction_subtype,
621 p_standard_code,
622 p_standard_type,
623 p_party_site_id,
624 x_queue_name,
625 x_tp_header_id);
626
627 if (p_tp_flag) then
628 retcode := 0;
629 else
630 retcode := 1;
631 errmsg := ecx_debug.getTranslatedMessage('ECX_RULE_INVALID_TP_SETUP',
632 'p_standard_code', p_standard_code,'p_transaction_type',p_transaction_type,
633 'p_transaction_subtype',p_transaction_subtype,'p_party_site_id',p_party_site_id);
634
635 ecx_debug.setErrorInfo(2,30,
636 'ECX_RULE_INVALID_TP_SETUP', 'p_standard_code', p_standard_code,'p_transaction_type',p_transaction_type,
637 'p_transaction_subtype',p_transaction_subtype,'p_party_site_id',p_party_site_id);
638
639 return 'N';
640 end if;
641
642
643 p_user_flag := IsUserAuthorized( p_user_name,
644 x_tp_header_id,
645 profile_value);
646
647
648 if (p_user_flag) then
649 retcode := 0;
650 return 'Y';
651 else
652 retcode := 2;
653 errmsg := ecx_debug.getTranslatedMessage('ECX_USER_TP_NOT_VALID',
654 'p_user_name',p_user_name);
655 ecx_debug.setErrorInfo(2,30,
656 'ECX_USER_TP_NOT_VALID','p_user_name',p_user_name);
657 return 'N';
658 end if;
659 Exception
660 when others then
661 retcode := 1;
662 return 'N';
663 End validateTPUser;
664
665 END ECX_TRADING_PARTNER_PVT;