1 PACKAGE BODY PA_TRANSACTION_SOURCES_PKG AS
2 /* $Header: PAXTIXSB.pls 120.2 2005/08/03 13:58:37 aaggarwa noship $ */
3
4 -- =====================================================================
5 -- This procedure performs a referential integrity check for
6
7 PROCEDURE check_references( X_trx_source IN VARCHAR2
8 , status IN OUT NOCOPY NUMBER
9 , outcome IN OUT NOCOPY VARCHAR2 ) IS
10 dummy NUMBER;
11
12 BEGIN
13 outcome := NULL; -- initialize
14 status := 0;
15
16 SELECT count(1)
17 INTO dummy
18 FROM dual
19 WHERE EXISTS
20 ( SELECT 1
21 FROM pa_transaction_xface_control txc
22 WHERE txc.transaction_source = X_trx_source )
23 OR EXISTS
24 ( SELECT 1
25 FROM pa_expenditure_items_all ei
26 WHERE ei.transaction_source = X_trx_source );
27
28 IF ( dummy = 1 ) THEN
29 outcome := 'PA_TR_TRX_SRC_IN_USE';
30 status := 1;
31 END IF;
32
33 EXCEPTION
34 WHEN OTHERS THEN
35 status := SQLCODE;
36
37 END check_references;
38
39
40 -- =====================================================================
41 -- This procedure checks if the
42 -- exists, and if so, returns an error message.
43
44 PROCEDURE check_unique( X_trx_source IN VARCHAR2
45 , X_user_trx_source IN VARCHAR2
46 , X_rowid IN VARCHAR2
47 , status IN OUT NOCOPY NUMBER
48 , outcome IN OUT NOCOPY VARCHAR2 ) IS
49 dummy NUMBER;
50
51 BEGIN
52 outcome := NULL;
53 status := 0;
54
55 SELECT 1
56 INTO dummy
57 FROM dual
58 WHERE NOT EXISTS
59 ( SELECT 1
60 FROM pa_transaction_sources
61 WHERE ( transaction_source = nvl(X_trx_source, -99)
62 OR user_transaction_source = X_user_trx_source)
63 AND ( ( X_rowid IS NULL )
64 OR ( rowid <> X_rowid ) ) );
65
66 EXCEPTION
67 WHEN NO_DATA_FOUND THEN
68 status := 1;
69 outcome := 'PA_SU_NAME_ALREADY_EXISTS';
70 WHEN OTHERS THEN
71 status := SQLCODE;
72
73 END check_unique;
74
75 END PA_TRANSACTION_SOURCES_PKG;
[Home] [Help]