[Home] [Help]
PACKAGE BODY: APPS.ECX_TRANSACTIONS_API
Source
1 PACKAGE BODY ECX_TRANSACTIONS_API AS
2 -- $Header: ECXTXNAB.pls 120.1.12000000.3 2007/07/20 07:39:46 susaha ship $
3
4 /**
5 This retrieves Transaction from the transaction definition in the ECX_TRANSACTIONS table.
6 **/
7 procedure retrieve_transaction
8 (
9 x_return_status Out NOCOPY pls_integer,
10 x_msg Out NOCOPY varchar2,
11 X_transaction_id Out NOCOPY Pls_integer,
12 p_transaction_type In Varchar2,
13 p_transaction_subtype In Varchar2,
14 p_party_type In Varchar2,
15 x_transaction_description OUT NOCOPY Varchar2,
16 x_created_by Out NOCOPY pls_integer,
17 x_creation_date Out NOCOPY date,
18 x_last_updated_by Out NOCOPY pls_integer,
19 x_last_update_date Out NOCOPY date
20 )
21 is
22 -- get data from ECX_TRANSACTIONS.
23 -- Bug #2183619 : Modify the cursor to add party_type
24 cursor c_transaction
25 (
26 p_transaction_type in varchar2,
27 p_transaction_subtype in varchar2,
28 p_party_type in varchar2
29 )
30 is
31
32 Select TRANSACTION_ID,
33 TRANSACTION_DESCRIPTION,
34 CREATED_BY,
35 CREATION_DATE,
36 LAST_UPDATED_BY,
37 LAST_UPDATE_DATE
38 from ECX_TRANSACTIONS_VL
39 where transaction_type = p_transaction_type
40 and transaction_subtype = p_transaction_subtype
41 and party_type = p_party_type; --Bug #2183619
42
43 begin
44 x_transaction_id :=-1;
45 x_return_status := ECX_UTIL_API.G_NO_ERROR;
46 x_msg := null;
47
48 -- make sure the transaction_type, transaction_subtype and party_type are not null.
49 if ( p_transaction_type is null )
50 then
51 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
52 x_msg := ecx_debug.getTranslatedMessage('ECX_TRANSACTION_TYPE_NOT_NULL');
53 return;
54 end if;
55
56 if ( p_transaction_subtype is null )
57 then
58 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
59 x_msg := ecx_debug.getTranslatedMessage('ECX_TRAN_SUBTYPE_NOT_NULL');
60 return;
61 end if;
62
63 if ( p_party_type is null )
64 then
65 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
66 x_msg := ecx_debug.getTranslatedMessage('ECX_PARTY_TYPE_NOT_NULL');
67 return;
68 end if;
69
70 if NOT (ECX_UTIL_API.validate_party_type(p_party_type))
71 then
72 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
73 x_transaction_id := -1;
74 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_PARTY_TYPE','p_party_type',p_party_type);
75 return;
76 end if;
77
78 open c_transaction(p_transaction_type,p_transaction_subtype,
79 p_party_type);
80 fetch c_transaction
81 into x_transaction_id,
82 x_transaction_description,
83 x_created_by,
84 x_creation_date,
85 x_last_updated_by,
86 x_last_update_date;
87
88 if c_transaction%NOTFOUND
89 then
90 raise no_data_found;
91 end if;
92
93 close c_transaction;
94
95 Exception
96 when too_many_rows then
97 x_return_status := ECX_UTIL_API.G_TOO_MANY_ROWS;
98 x_msg := ecx_debug.getTranslatedMessage('ECX_TRANS_TOO_MANY_ROWS',
99 'p_transaction_type', p_transaction_type,
100 'p_transaction_subtype', p_transaction_subtype,
101 'p_party_type',p_party_type
102 );
103
104 if c_transaction%ISOPEN
105 then
106 close c_transaction;
107 end if;
108
112 'p_transaction_subtype',p_transaction_subtype,
109 when no_data_found then
110 x_msg := ecx_debug.getTranslatedMessage('ECX_TRANSACTION_NOT_FOUND',
111 'p_transaction_type',p_transaction_type,
113 'p_party_type',p_party_type
114 );
115 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
116 if c_transaction%ISOPEN
117 then
118 close c_transaction;
119 end if;
120
121 when others then
122 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
123 x_msg := substr(SQLERRM,1,200);
124
125 if c_transaction%ISOPEN
126 then
127 close c_transaction;
128 end if;
129 end retrieve_transaction;
130
131 /**
132 This Create_Transaction API is used to create a new transaction definition in the ECX_TRANSACTIONS table.
133 **/
134 procedure create_transaction
135 (
136 x_return_status Out NOCOPY pls_integer,
137 x_msg Out NOCOPY varchar2,
138 X_transaction_id Out NOCOPY Pls_integer,
139 p_transaction_type In Varchar2,
140 p_transaction_subtype In Varchar2,
141 p_transaction_description In Varchar2,
142 p_admin_user in varchar2 default null,
143 p_party_type In Varchar2,
144 p_owner in varchar2
145 )
146 is
147 i_transaction_description varchar2(256);
148 i_created_by pls_integer;
149 i_creation_date date;
150 i_last_updated_by pls_integer;
151 i_last_update_date date;
152 x1_return_status pls_integer;
153 x1_msg varchar2(200);
154 x1_transaction_id pls_integer;
155 i_rowid varchar2(2000);
156 begin
157 x_return_status := ECX_UTIL_API.G_NO_ERROR;
158 x_transaction_id :=-1;
159 x_msg := null;
160
161 -- make sure the transaction_type, transaction_subtype and party_type are not null.
162 if ( p_transaction_type is null )
163 then
164 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
165 x_msg := ecx_debug.getTranslatedMessage('ECX_TRANSACTION_TYPE_NOT_NULL');
166 return;
167 end if;
168
169 if ( p_transaction_subtype is null )
170 then
171 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
172 x_msg := ecx_debug.getTranslatedMessage('ECX_TRAN_SUBTYPE_NOT_NULL');
173 return;
174 end if;
175
176 if ( p_party_type is null )
177 then
178 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
179 x_msg := ecx_debug.getTranslatedMessage('ECX_PARTY_TYPE_NOT_NULL');
180 return;
181 end if;
182
183 if NOT (ECX_UTIL_API.validate_party_type(p_party_type))
184 then
185 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
186 x_transaction_id := -1;
187 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_PARTY_TYPE','p_party_type',p_party_type);
188 return;
189 end if;
190
191
192 --- Make sure that the Transaction entry is existing or not. The Index is not sufficient.
193 --- We will add the check on party_type later on.
194
195 ecx_transactions_api.retrieve_transaction
196 (
197 x_return_status => x1_return_status,
198 x_msg => x1_msg,
199 x_transaction_id => x1_transaction_id,
200 p_transaction_type => p_transaction_type,
201 p_transaction_subtype => p_transaction_subtype,
202 p_party_type => p_party_type,
203 x_transaction_description => i_transaction_description,
204 x_created_by => i_created_by,
205 x_creation_date => i_creation_date,
206 x_last_updated_by => i_last_updated_by,
207 x_last_update_date => i_last_update_date
208 );
209
210 if ( x1_return_status = ECX_UTIL_API.G_NO_DATA_ERROR )
211 then
212 select ecx_transactions_s.nextval
213 into x_transaction_id
214 from dual;
215
216 if p_owner = 'SEED'
217 then
218 i_last_updated_by :=1;
219 else
220 i_last_updated_by :=0;
221 end if;
222
223 /* Call the table handler API for insertion of data
224 into ecx_transactions_b and ecx_transactions_tl tables*/
225
226 ECX_TRANSACTIONS_PKG.INSERT_ROW
227 (
228 x_rowid => i_rowid,
229 x_transaction_id => x_transaction_id ,
230 x_transaction_type => upper(p_transaction_type),
231 x_transaction_subtype => upper(p_transaction_subtype),
232 x_party_type => p_party_type,
233 x_transaction_description => p_transaction_description,
234 x_admin_user => p_admin_user,
235 x_creation_date => sysdate,
236 x_created_by => i_last_updated_by,
237 x_last_update_date => sysdate,
238 x_last_updated_by => i_last_updated_by,
239 x_last_update_login => 0);
240
241 elsif ( x1_return_status = ECX_UTIL_API.G_NO_ERROR ) then
242 raise dup_val_on_index;
243 else
244 x_return_status := x1_return_status;
245 x_msg := x1_msg;
246 return;
247 end if;
248
249 Exception
250 when dup_val_on_index then
251 x_return_status := ECX_UTIL_API.G_DUP_ERROR;
252 x_msg := ecx_debug.getTranslatedMessage('ECX_DUPLICATE_TRANSACTIONS',
253 'p_transaction_type',p_transaction_type,
254 'p_transaction_subtype',p_transaction_subtype,
255 'p_party_type',p_party_type);
256
257 when no_data_found then
258 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
259 x_msg := ecx_debug.getTranslatedMessage
260 ('ECX_TRANSACTION_NOT_FOUND',
261 'p_transaction_type' ,p_transaction_type,
265 when others then
262 'p_transaction_subtype',p_transaction_subtype,
263 'p_party_type' , p_party_type);
264
266 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
267 x_msg := substr(SQLERRM,1,200);
268 end create_transaction;
269
270 /**
271 This Update_Transaction API is used to update an existing transaction description in the ECX_TRANSACTIONS table.
272 **/
273 procedure update_transaction
274 (
275 x_return_status Out NOCOPY pls_integer,
276 x_msg Out NOCOPY varchar2,
277 p_transaction_id In Pls_integer,
278 p_transaction_type In Varchar2,
279 p_transaction_subtype In Varchar2,
280 p_party_type In Varchar2,
281 p_transaction_description In Varchar2,
282 p_owner in varchar2
283 )
284 is
285 i_last_updated_by pls_integer;
286 begin
287 x_return_status := ECX_UTIL_API.G_NO_ERROR;
288 x_msg := null;
289 -- make sure the p_transaction_id is not null.
290 If p_transaction_id is null
291 then
292 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
293 x_msg := ecx_debug.getTranslatedMessage('ECX_TRANSACTION_ID_NOT_NULL');
294 return;
295 end if;
296
297 -- update description in ECX_TRANSACTIONS.
298 -- We cannot update the Primary Key of the Entity.Only description should be updated.
299
300 if p_owner = 'SEED'
301 then
302 i_last_updated_by :=1;
303 else
304 i_last_updated_by :=0;
305 end if;
306
307 Update ecx_transactions_b
308 set last_updated_by = i_last_updated_by,
309 last_update_date = sysdate
310 Where transaction_id = p_transaction_id;
311
312 if (sql%rowcount = 0)
313 then
314 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
315 x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
316 'p_table',
317 'ECX_TRANSACTIONS_B',
318 'p_param_name',
319 'Transaction ID',
320 'p_param_id',
321 p_transaction_id);
322 return;
323 end if;
324
325
326 Update ecx_transactions_tl
327 set transaction_description = p_transaction_description,
328 last_updated_by = i_last_updated_by,
329 last_update_date = sysdate,
330 source_lang = userenv('LANG')
331 Where transaction_id = p_transaction_id and
332 userenv('LANG') in (language, source_lang);
333
334 if (sql%rowcount = 0)
335 then
336 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
337 x_msg := ecx_debug.getTranslatedMessage('ECX_NO_TRANS_ROWS_UPDATED',
338 'p_table',
339 'ECX_TRANSACTIONS_TL',
340 'p_param_name',
341 'Transaction ID',
342 'p_param_id',
343 'p_transaction_id');
344 return;
345 end if;
346
347 Exception
348 when others then
349 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
350 x_msg := substr(1,200,SQLERRM);
351 end update_transaction;
352
353 /**
354 This Delete_Transaction API is used to delete an existing transaction
355 definition in the ECX_TRANSACTIONS
356 table and also the external processes that are associate to it.
357 This API allows users to delete a
358 transaction definition by specifying the transaction id.
359 **/
360 procedure delete_transaction
361 (
362 x_return_status Out NOCOPY pls_integer,
363 x_msg Out NOCOPY varchar2,
364 p_transaction_id In Pls_integer
365 )
366 is
367
368 num pls_integer;
369
370 Begin
371 x_return_status := ECX_UTIL_API.G_NO_ERROR;
372 x_msg := null;
373
374 If p_transaction_id is null
375 then
376 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
377 x_msg := ecx_debug.getTranslatedMessage('ECX_TRANSACTION_ID_NOT_NULL');
378 return;
379 end if;
380
381 -- make sure that is no TP using this transaction first.
382 -- get all the external processes defined for the given transaction.
383 -- Check if there any TP reference to any ext_process_id
384 -- with the given transaction_id
385 -- If any TP is using it, then return with
386 -- a error return code.
387 -- Otherwise, do the delete.
388
389 -- make sure that is no TP using this process first.
390 select count(*)
391 into num
392 from ecx_tp_details etd,
393 ecx_ext_processes eep
394 where eep.ext_process_id = etd.ext_process_id
395 and eep.transaction_id = p_transaction_id;
396
397 if (num > 0)
398 then
399 x_msg := ecx_debug.getTranslatedMessage('ECX_TP_DETAIL_EXISTS');
400 x_return_status := ECX_UTIL_API.G_REFER_ERROR;
401 return;
402 end if;
403
404 delete from ecx_ext_processes
405 where transaction_id = p_transaction_id;
406
407 /* Call table handler API for deletion */
408 ECX_TRANSACTIONS_PKG.DELETE_ROW(x_transaction_id => p_transaction_id);
409 exception
410 when no_data_found then
411 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
412 x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_DELETED',
413 'p_table', 'ECX_TRANSACTIONS',
414 'p_param_name','Transaction ID',
415 'p_param_id',p_transaction_id);
416 when others then
417 x_msg := substr(SQLERRM,1,200);
418 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
419 end delete_transaction;
420
421 /**
422 Overloaded Procedure.This Retrieve_Ext_Process API is used to retrieve an existing external
423 process definition from the ECX_EXT_PROCESSES table.
424 **/
425 /* Bug #2183619, Added two additional input parameters for
426 External Type and Subtype */
427 procedure retrieve_external_transaction
428 (
429 x_return_status Out NOCOPY pls_integer,
430 x_msg Out NOCOPY varchar2,
431 X_ext_process_id Out NOCOPY pls_integer,
432 x_transaction_id OUT NOCOPY pls_integer,
433 p_transaction_type IN varchar2,
434 p_transaction_subtype IN varchar2,
435 p_party_type IN varchar2,
436 p_standard In Varchar2,
437 p_direction In Varchar2,
438 x_transaction_description Out NOCOPY Varchar2,
439 x_ext_type Out NOCOPY Varchar2,
440 x_ext_subtype Out NOCOPY Varchar2,
441 x_standard_id Out NOCOPY pls_integer,
442 x_queue_name Out NOCOPY Varchar2,
443 x_created_by Out NOCOPY pls_integer,
444 x_creation_date Out NOCOPY date,
445 x_last_updated_by Out NOCOPY pls_integer,
446 x_last_update_date Out NOCOPY date,
447 p_ext_type In Varchar2 ,
448 p_ext_subtype In Varchar2,
449 p_standard_type IN varchar2
450 )
451 is
452 num varchar2(2000);
453
454 begin
455 x_return_status := ECX_UTIL_API.G_NO_ERROR;
456 x_msg := null;
457 x_ext_process_id :=-1;
458 x_transaction_id :=-1;
459
460 ecx_transactions_api.retrieve_transaction
461 (
462 x_return_status => x_return_status,
463 x_msg => x_msg,
464 x_transaction_id => x_transaction_id,
465 p_transaction_type => p_transaction_type,
466 p_transaction_subtype => p_transaction_subtype,
467 p_party_type => p_party_type,
468 x_transaction_description => x_transaction_description,
469 x_created_by => x_created_by,
470 x_creation_date => x_creation_date,
471 x_last_updated_by => x_last_updated_by,
472 x_last_update_date => x_last_update_date
473 );
474 if (x_transaction_id = -1)
475 then
476 return;
477 end if;
478
479 if ( p_direction is null )
480 then
481 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
482 x_msg := ecx_debug.getTranslatedMessage('ECX_DIRECTION_NOT_NULL');
483 return;
484 end if;
485
486 if ( p_standard is null )
487 then
488 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
489 x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_NULL');
490 return;
491 end if;
492
493 if NOT (ECX_UTIL_API.validate_direction(p_direction))
494 then
495 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_DIRECTION','p_direction',p_direction);
496 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
497 return;
498 end if;
499
500 -- make sure it is a valid standard.
501 begin
502 select standard_id
503 into x_standard_id
504 from ecx_standards
505 where standard_code = p_standard
506 and standard_type = p_standard_type;
507 exception
508 when no_data_found then
509 x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_CODE_NOT_FOUND','p_standard',p_standard);
510 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
511 return;
512 end;
513
514 -- select data from ECX_EXT_PROCESSES
515 /* Bug #2183619,Modified to add check for External Type and Sub type */
516 select EXT_PROCESS_ID,
517 EXT_TYPE,
518 EXT_SUBTYPE,
519 QUEUE_NAME,
520 CREATED_BY,
521 CREATION_DATE,
522 LAST_UPDATED_BY,
523 LAST_UPDATE_DATE
524 into x_ext_process_id,
525 x_ext_type,
526 x_ext_subtype,
527 x_queue_name,
528 x_created_by,
529 x_creation_date,
530 x_last_updated_by,
531 x_last_update_date
532 from ECX_EXT_PROCESSES
533 where transaction_id = x_transaction_id
534 and standard_id = x_standard_id
535 and direction = p_direction
536 and (p_ext_type is null or ext_type=p_ext_type)
537 and (p_ext_subtype is null or ext_subtype=p_ext_subtype);
538
539 exception
540 when too_many_rows then
541 x_return_status := ECX_UTIL_API.G_TOO_MANY_ROWS;
542 x_msg := ecx_debug.getTranslatedMessage('ECX_EXT_PROCESS_TOO_MANY_ROWS',
543 'p_transaction_type', p_transaction_type,
544 'p_transaction_subtype', p_transaction_subtype,
545 'p_standard', p_standard,
546 'p_direction', p_direction
547 );
548 when no_data_found then
549
550 x_msg := ecx_debug.getTranslatedMessage('ECX_EXT_TRANSACTION_NOT_FOUND',
551 'p_transaction_type',p_transaction_type,
552 'p_transaction_subtype',p_transaction_subtype,
553 'p_standard',p_standard
554 );
555 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
556 when others then
557 x_msg := substr(SQLERRM,1,200);
558 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
559 end retrieve_external_transaction;
560
561 /**
562 This Retrieve_Ext_Process API is used to retrieve an existing external
563 process definition from the ECX_EXT_PROCESSES table.
564 **/
565 /* Bug #2183619, Added two additional input parameters for
566 External Type and Subtype */
567 procedure retrieve_external_transaction
568 (
569 x_return_status Out NOCOPY pls_integer,
570 x_msg Out NOCOPY varchar2,
571 X_ext_process_id Out NOCOPY pls_integer,
572 p_transaction_id in pls_integer,
573 p_standard In Varchar2,
574 p_direction In Varchar2,
575 x_transaction_description Out NOCOPY Varchar2,
576 x_ext_type Out NOCOPY Varchar2,
577 x_ext_subtype Out NOCOPY Varchar2,
578 x_standard_id Out NOCOPY pls_integer,
579 x_queue_name Out NOCOPY Varchar2,
580 x_created_by Out NOCOPY pls_integer,
581 x_creation_date Out NOCOPY date,
582 x_last_updated_by Out NOCOPY pls_integer,
583 x_last_update_date Out NOCOPY date,
584 p_ext_type In Varchar2 ,
585 p_ext_subtype In Varchar2 ,
586 p_standard_type IN varchar2
587 )
588 is
589 begin
590 x_return_status := ECX_UTIL_API.G_NO_ERROR;
591 x_msg := null;
592 x_ext_process_id :=-1;
593
594 If p_transaction_id is null
595 then
596 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
597 x_msg := ecx_debug.getTranslatedMessage('ECX_TRANSACTION_ID_NOT_NULL');
598 return;
599 end if;
600
601 if ( p_direction is null )
602 then
603 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
604 x_msg := ecx_debug.getTranslatedMessage('ECX_DIRECTION_NOT_NULL');
605 return;
606 end if;
607
608 if ( p_standard is null )
609 then
610 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
611 x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_NULL');
612 return;
613 end if;
614
615 if NOT (ECX_UTIL_API.validate_direction(p_direction))
616 then
617 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_DIRECTION','p_direction',p_direction);
618 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
619 return;
620 end if;
621
622 -- make sure it is a valid standard.
623 begin
624 select standard_id
625 into x_standard_id
626 from ecx_standards
627 where standard_code = p_standard
628 and standard_type = p_standard_type;
629 exception
630 when no_data_found then
631 x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_FOUND',
632 'p_standard',p_standard,
633 'p_std_type', p_standard_type);
634 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
635 return;
636 end;
637
638 -- select data from ECX_EXT_PROCESSES
639 /* Bug #2183619,Modified to add check for External Type and Sub type */
640 select TRANSACTION_DESCRIPTION,
641 EXT_PROCESS_ID,
642 EXT_TYPE,
643 EXT_SUBTYPE,
644 QUEUE_NAME,
645 eep.CREATED_BY,
646 eep.CREATION_DATE,
647 eep.LAST_UPDATED_BY,
648 eep.LAST_UPDATE_DATE
649 into x_transaction_description,
650 x_ext_process_id,
651 x_ext_type,
652 x_ext_subtype,
653 x_queue_name,
654 x_created_by,
655 x_creation_date,
656 x_last_updated_by,
657 x_last_update_date
658 from ECX_EXT_PROCESSES eep,
659 ECX_TRANSACTIONS_VL et
660 where et.transaction_id = p_transaction_id
661 and et.transaction_id = eep.transaction_id
662 and eep.standard_id = x_standard_id
663 and eep.direction = p_direction
664 and (p_ext_type is null or eep.ext_type=p_ext_type)
665 and (p_ext_subtype is null or eep.ext_subtype=p_ext_subtype);
666 exception
667 when too_many_rows then
668 x_return_status := ECX_UTIL_API.G_TOO_MANY_ROWS;
669 x_msg := ecx_debug.getTranslatedMessage('ECX_EXT_PROCESS1_TOO_MANY_ROWS', 'p_transaction_id', p_transaction_id,
670 'p_standard', p_standard,
671 'p_direction', p_direction
672 );
673
674 when no_data_found then
675 x_msg := ecx_debug.getTranslatedMessage('ECX_EXT_TRANSACTION1_NOT_FOUND',
676 'p_transaction_id', p_transaction_id,
677 'p_standard',p_standard,
678 'p_direction',p_direction
679 );
680 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
681 when others then
682 x_msg := substr(SQLERRM,1,200);
683 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
684 end retrieve_external_transaction;
685
686 /**
687 This Create_Ext_Process API is used to create a new external process definition in the ECX_EXT_PROCESSES table.
688 If the transaction doesnt exists, then create one.
689 **/
690
691 procedure create_external_transaction
692 (
693 x_return_status Out NOCOPY pls_integer,
694 x_msg Out NOCOPY varchar2,
695 X_ext_process_id Out NOCOPY Pls_integer,
696 p_transaction_id in Pls_integer,
697 p_ext_type In Varchar2,
698 p_ext_subtype In Varchar2,
699 p_standard In Varchar2,
700 p_queue_name In Varchar2,
701 p_direction In Varchar2,
702 p_owner in varchar2 ,
703 p_standard_type IN varchar2
704 )
705 is
706 I_stand_id pls_integer;
707 x_return_code pls_integer;
708 i_last_updated_by pls_integer;
709 begin
710 x_return_status := ECX_UTIL_API.G_NO_ERROR;
711 x_msg := null;
712 x_ext_process_id :=-1;
713
714 If (p_transaction_id is null)
715 then
716 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
717 x_msg := ecx_debug.getTranslatedMessage('ECX_TRANSACTION_ID_NOT_NULL');
718 return;
719 end if;
720
721 If (p_ext_type is null)
722 then
723 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
724 x_msg := ecx_debug.getTranslatedMessage('ECX_EXT_TYPE_NOT_NULL');
725 return;
726 end if;
727
728 If (p_ext_subtype is null)
729 then
730 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
731 x_msg := ecx_debug.getTranslatedMessage('ECX_EXT_SUBTYPE_NOT_NULL');
732 return;
733 end if;
734
735 if ( p_direction is null )
736 then
737 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
738 x_msg := ecx_debug.getTranslatedMessage('ECX_DIRECTION_NOT_NULL');
739 return;
740 end if;
741
742 if ( p_standard is null )
743 then
744 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
745 x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_NULL');
746 return;
747 end if;
748
749 if NOT (ECX_UTIL_API.validate_direction(p_direction))
750 then
751 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_DIRECTION','p_direction',p_direction);
752 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
753 return;
754 end if;
755
756 if p_direction = 'IN'
757 then
758 if ( p_queue_name is null )
759 then
760 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
761 x_msg := ecx_debug.getTranslatedMessage('ECX_QUEUE_NAME_NOT_NULL');
762 return;
763 end if;
764
765 -- make sure it is a valid queue name.
766 If NOT (ECX_UTIL_API.validate_queue_name(p_queue_name))
767 then
768 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_QUEUE_NAME','p_queue_name',p_queue_name);
769 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
770 return;
771 end if;
772 end if;
773
774 -- make sure it is a valid standard.
775 begin
776 select standard_id
777 into I_stand_id
778 from ecx_standards
779 where standard_code = p_standard
780 and standard_type = p_standard_type;
781 exception
782 when no_data_found then
783 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
784 x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_FOUND',
785 'p_standard', p_standard,
786 'p_std_type', p_standard_type);
787 return;
788 end;
789
790 -- make sure the ext_type and ext_subtype doesnt exists.
791 -- If the code is already exists, then
792 -- return an error status G_DUP_ERROR and return.
793
794 select ecx_ext_processes_s.nextval
795 into x_ext_process_id
796 from dual;
797
798 if p_owner = 'SEED'
799 then
800 i_last_updated_by :=1;
801 else
802 i_last_updated_by :=0;
803 end if;
804
805 -- Insert data into ECX_EXT_PROCESSES
806 insert into ecx_ext_processes
807 (
808 EXT_PROCESS_ID,
809 EXT_TYPE,
810 EXT_SUBTYPE,
811 TRANSACTION_ID,
812 STANDARD_ID,
813 QUEUE_NAME,
814 DIRECTION,
815 CREATED_BY,
816 CREATION_DATE,
817 LAST_UPDATED_BY,
818 LAST_UPDATE_DATE
819 )
820 Values (
821 X_ext_process_id,
822 P_ext_type,
823 p_ext_subtype,
824 p_transaction_id,
825 I_stand_id,
826 p_queue_name,
827 p_direction,
828 i_last_updated_by,
829 sysdate,
830 i_last_updated_by,
831 sysdate
832 );
833
834 exception
835 when dup_val_on_index then
836 x_msg := ecx_debug.getTranslatedMessage('ECX_EXT_PROCESSES_EXISTS',
837 'p_transaction_id', p_transaction_id,
838 'p_ext_type', p_ext_type,
839 'p_ext_subtype', p_ext_subtype
840 );
841 x_return_status := ECX_UTIL_API.G_DUP_ERROR;
842 when others then
843 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
844 x_msg := substr(SQLERRM,1,200);
845 end create_external_transaction;
846
847 /**
848 This Create_Ext_Process API is used to create a new external process definition in the ECX_EXT_PROCESSES table.
849 If the transaction doesnt exists, then create one.
850 **/
851
852 procedure create_external_transaction
853 (
854 x_return_status Out NOCOPY pls_integer,
855 x_msg Out NOCOPY varchar2,
856 X_ext_process_id Out NOCOPY Pls_integer,
857 X_transaction_id Out NOCOPY Pls_integer,
858 p_transaction_type In Varchar2,
859 p_transaction_subtype In Varchar2,
860 p_party_type In Varchar2,
861 p_ext_type In Varchar2,
862 p_ext_subtype In Varchar2,
863 p_standard In Varchar2,
864 p_queue_name In Varchar2,
865 p_direction In Varchar2,
866 p_owner IN varchar2 ,
867 p_standard_type IN varchar2
868 )
869 is
870 i_transaction_description varchar2(256);
871 i_created_by pls_integer;
872 i_creation_date date;
873 i_last_updated_by pls_integer;
874 i_last_update_date date;
875 begin
876 x_return_status := ECX_UTIL_API.G_NO_ERROR;
877 x_msg := null;
878 x_transaction_id :=-1;
879 x_ext_process_id :=-1;
880 ecx_transactions_api.retrieve_transaction
881 (
882 x_return_status => x_return_status,
883 x_msg => x_msg,
884 x_transaction_id => x_transaction_id,
885 p_transaction_type => p_transaction_type,
886 p_transaction_subtype => p_transaction_subtype,
887 p_party_type => p_party_type,
888 x_transaction_description => i_transaction_description,
889 x_created_by => i_created_by,
890 x_creation_date => i_creation_date,
891 x_last_updated_by => i_last_updated_by,
892 x_last_update_date => i_last_update_date
893 );
894 if (x_transaction_id = -1)
895 then
896 return;
897 end if;
898
899 ecx_transactions_api.create_external_transaction
900 (
901 x_return_status => x_return_status,
902 x_msg => x_msg,
903 x_ext_process_id => x_ext_process_id,
904 p_transaction_id => x_transaction_id,
905 p_ext_type => p_ext_type,
906 p_ext_subtype => p_ext_subtype,
907 p_standard => p_standard,
908 p_queue_name => p_queue_name,
909 p_direction => p_direction,
910 p_owner => p_owner,
911 p_standard_type => p_standard_type
912 );
913 exception
914 when others then
915 x_msg := substr(SQLERRM,1,200);
916 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
917 end create_external_transaction;
918
919 /**
920 Update_Ext_Process API is used to update an existing external process definition in the ECX_EXT_PROCESSES table.
921 This API allows users to update the ext_type, ext_subtype, standard, queue_name and direction by
922 specifying the ext_process_id.
923 **/
924 procedure update_external_transaction
925 (
926 x_return_status Out NOCOPY pls_integer,
927 x_msg Out NOCOPY varchar2,
928 p_ext_process_id In pls_integer,
929 p_ext_type In Varchar2,
930 p_ext_subtype In Varchar2,
931 p_standard In Varchar2,
932 p_queue_name In Varchar2,
933 p_direction In Varchar2,
934 p_owner in varchar2 ,
935 p_standard_type IN varchar2
936 )
937 is
938 I_stand_id pls_integer;
939 i_last_updated_by pls_integer;
940 begin
941 x_return_status := ECX_UTIL_API.G_NO_ERROR;
942 x_msg := null;
943
944 If ( p_ext_process_id is null )
945 then
946 x_msg := ecx_debug.getTranslatedMessage('ECX_EXT_PROCESS_ID_NOT_NULL','p_ext_process_id',p_ext_process_id);
947 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
948 return;
949 end if;
950
951 If (p_ext_type is null)
952 then
953 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
954 x_msg := ecx_debug.getTranslatedMessage('ECX_EXT_TYPE_NOT_NULL');
955 return;
956 end if;
957
958 If (p_ext_subtype is null)
959 then
960 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
961 x_msg := ecx_debug.getTranslatedMessage('ECX_EXT_SUBTYPE_NOT_NULL');
962 return;
963 end if;
964
965 if ( p_direction is null )
966 then
967 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
968 x_msg := ecx_debug.getTranslatedMessage('ECX_DIRECTION_NOT_NULL');
969 return;
970 end if;
971
972 if ( p_standard is null )
973 then
974 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
975 x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_NULL');
976 return;
977 end if;
978
979 if NOT (ECX_UTIL_API.validate_direction(p_direction))
980 then
981 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_DIRECTION','p_direction',p_direction);
982 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
983 return;
984 end if;
985
986 if p_direction = 'IN'
987 then
988 if ( p_queue_name is null )
989 then
990 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
991 x_msg := ecx_debug.getTranslatedMessage('ECX_QUEUE_NAME_NOT_NULL');
992 return;
993 end if;
994
995 -- make sure it is a valid queue name.
996 If NOT (ECX_UTIL_API.validate_queue_name(p_queue_name))
997 then
998 x_msg := ecx_debug.getTranslatedMessage('ECX_INVALID_QUEUE_NAME','p_queue_name',p_queue_name);
999 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1000 return;
1001 end if;
1002 end if;
1003
1004 -- make sure it is a valid standard.
1005 begin
1006 select standard_id
1007 into I_stand_id
1008 from ecx_standards
1009 where standard_code = p_standard
1010 and standard_type = p_standard_type;
1011 exception
1012 when no_data_found then
1013 x_msg := ecx_debug.getTranslatedMessage('ECX_STANDARD_NOT_FOUND',
1014 'p_standard', p_standard,
1015 'p_std_type', p_standard_type);
1016 x_return_status := ECX_UTIL_API.G_INVALID_PARAM;
1017 return;
1018 end;
1019
1020 if p_owner = 'SEED'
1021 then
1022 i_last_updated_by :=1;
1023 else
1024 i_last_updated_by :=0;
1025 end if;
1026
1027 -- update data into ECX_EXT_PROCESSES
1028 -- SHould we allow to update the Unqiue itself?
1029 update ECX_EXT_PROCESSES
1030 set ext_type = p_ext_type,
1031 ext_subtype = p_ext_subtype,
1032 standard_id = I_stand_id,
1033 queue_name = p_queue_name,
1034 direction = p_direction,
1035 last_updated_by = i_last_updated_by,
1036 last_update_date = sysdate
1037 where ext_process_id = p_ext_process_id;
1038
1039 if (sql%rowcount = 0)
1040 then
1041 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
1042 x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
1043 'p_table', 'ecx_ext_processes', 'p_key', p_ext_process_id);
1044 return;
1045 end if;
1046
1047 exception
1048 when dup_val_on_index then
1049 x_msg := ecx_debug.getTranslatedMessage('ECX_EXT_PROCESS_EXISTS',
1050 'p_ext_process_id', p_ext_process_id,
1051 'p_ext_type', p_ext_type,
1052 'p_ext_subtype', p_ext_subtype
1053 );
1054 x_return_status := ECX_UTIL_API.G_DUP_ERROR;
1055 when others then
1056 x_msg := substr(SQLERRM,1,200);
1057 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
1058 end update_external_transaction;
1059
1060 /**
1061 This Delete_Ext_Process API is used to delete an existing external process definition in the ECX_EXT_PROCESSES table.
1062 This API allows users to delete a process definition by specifying the ext_process_id.
1063 **/
1064 procedure delete_external_transaction
1065 (
1066 x_return_status Out NOCOPY pls_integer,
1067 x_msg Out NOCOPY varchar2,
1068 p_ext_process_id In pls_integer
1069 )
1070 is
1071 num pls_integer;
1072 begin
1073 x_msg := null;
1074 x_return_status := ECX_UTIL_API.G_NO_ERROR;
1075 if (p_ext_process_id is null)
1076 then
1077 x_msg := ecx_debug.getTranslatedMessage('ECX_EXT_PROCESS_ID_NOT_NULL');
1078 x_return_status := ECX_UTIL_API.G_NULL_PARAM;
1079 return;
1080 end if;
1081
1082 -- make sure that is no TP using this process first.
1083 select count(*)
1084 into num
1085 from ecx_tp_details
1086 where ext_process_id = p_ext_process_id;
1087
1088 if (num > 0)
1089 then
1090 x_msg := ecx_debug.getTranslatedMessage('ECX_TP_DETAIL_EXISTS','p_ext_process_id',p_ext_process_id);
1091 x_return_status := ECX_UTIL_API.G_REFER_ERROR;
1092 return;
1093 end if;
1094
1095 delete from ecx_ext_processes
1096 where ext_process_id = p_ext_process_id;
1097
1098 if (sql%rowcount = 0)
1099 then
1100 x_return_status := ECX_UTIL_API.G_NO_DATA_ERROR;
1101 x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_DELETED',
1102 'p_table', 'ECX_EXT_PROCESSES', 'p_key', p_ext_process_id);
1103 return;
1104 end if;
1105
1106 exception
1107 when others then
1108 x_msg := substr(SQLERRM,1,200);
1109 x_return_status := ECX_UTIL_API.G_UNEXP_ERROR;
1110 end delete_external_transaction;
1111
1112 END ECX_TRANSACTIONS_API;