1 PACKAGE BODY PA_IND_COST_CODES_PKG AS
2 /* $Header: PAXCIICB.pls 120.1 2005/08/23 19:19:59 spunathi noship $ */
3
4 -- =====================================================================
5 -- This procedure performs a referential integrity check for indirect cost
6 -- codes. Indirect cost code appears as a foreign key in the following
7 -- tables:
8 -- PA_COST_BASE_COST_CODES
9 -- PA_IND_COST_CODE_MULTIPLIERS
10 -- PA_COMPILED_MULTIPLIERS
11 -- The procedure first checks if the icc is referenced in
12 -- PA_COST_BASE_COST_CODES. If it is, the outcome parameter is set to the
13 -- relevant error message name and processing stops. If it is not, then the
14 -- procedure then checks for references in PA_IND_COST_CODE_MULTIPLIERS. If
15 -- the icc is not referenced in this table, then the outcome parameter is set
16 -- to NULL and processing stops; there is no need to check
17 -- PA_COMPILED_MULTIPLIERS since the multiplier must first exist before it can
18 -- be compiled. IF a icc multiplier does exist, then this procedure checks
19 -- further to see if a compiled mutliplier exists.
20
21 PROCEDURE check_references( X_icc_name IN VARCHAR2
22 , status IN OUT NOCOPY NUMBER
23 , outcome IN OUT NOCOPY VARCHAR2 ) IS
24 dummy NUMBER;
25
26 BEGIN
27 outcome := NULL; -- initialize
28 status := 0;
29
30 SELECT count(1)
31 INTO dummy
32 FROM dual
33 WHERE EXISTS
34 ( SELECT 1
35 FROM pa_cost_base_cost_codes
36 WHERE ind_cost_code = X_icc_name );
37
38 IF ( dummy = 1 ) THEN -- ICC referenced by at least one cost base
39 outcome := 'PA_ICC_IN_COST_BASE';
40 status := 1;
41 ELSE -- Check for icc multipliers
42 SELECT count(1)
43 INTO dummy
44 FROM dual
45 WHERE EXISTS
46 ( SELECT 1
47 FROM pa_ind_cost_multipliers
48 WHERE ind_cost_code = X_icc_name );
49
50 IF ( dummy = 0 ) THEN
51 -- No icc multipliers exist
52 outcome := NULL;
53 -- Outcome is error-free; okay to delete
54 ELSE
55 -- At least one icc multiplier exists; now check
56 -- for compiled multipliers
57 SELECT count(1)
58 INTO dummy
59 FROM dual
60 WHERE EXISTS
61 ( SELECT 1
62 FROM pa_compiled_multipliers
63 WHERE ind_cost_code = X_icc_name );
64
65 IF ( dummy = 0 ) THEN
66 -- No compiled multipliers exist
67 outcome := 'PA_ICC_IN_MULTIPLIER';
68 status := 1;
69 ELSE
70 -- At least one compiled multiplier exists
71 outcome := 'PA_ICC_IN_COMPILED';
72 status := 1;
73 END IF;
74
75 END IF;
76
77 END IF;
78
79 EXCEPTION
80 WHEN OTHERS THEN
81 status := SQLCODE;
82
83 END check_references;
84
85
86 -- =====================================================================
87 -- This procedure checks if the indirect cost code being inserted already
88 -- exists, and if so, returns an error message.
89
90 PROCEDURE check_unique( X_icc_name IN VARCHAR2
91 , X_rowid IN VARCHAR2
92 , status IN OUT NOCOPY NUMBER
93 , outcome IN OUT NOCOPY VARCHAR2 ) IS
94 dummy NUMBER;
95
96 BEGIN
97 outcome := NULL;
98 status := 0;
99
100 SELECT 1
101 INTO dummy
102 FROM dual
103 WHERE NOT EXISTS
104 ( SELECT 1
105 FROM pa_ind_cost_codes
106 WHERE ind_cost_code = X_icc_name
107 AND ( ( X_rowid IS NULL )
108 OR ( rowid <> X_rowid ) ) );
109
110 EXCEPTION
111 WHEN NO_DATA_FOUND THEN
112 status := 1;
113 outcome := 'PA_SU_NAME_ALREADY_EXISTS';
114 WHEN OTHERS THEN
115 status := SQLCODE;
116
117 END check_unique;
118
119 END PA_IND_COST_CODES_PKG;
[Home] [Help]