4 --- this procedure will check that the PK value being input is Unique.
1 package body PA_COST_BASES_PKG as
2 --- $Header: PAXCCBAB.pls 120.1 2005/08/23 19:19:00 spunathi noship $
3 --- ---------------------------------------------------------------------------
5
6 procedure check_unique (x_return_status IN OUT NOCOPY number,
7 x_rowid IN varchar2,
8 x_cost_base IN varchar2,
9 x_cost_base_type IN varchar2)
10 is
11 x_dummy number;
12
13 begin
14 x_return_status := 0;
15
16 select 1
17 into x_dummy
18 from sys.dual
19 where not exists
20 (select 1
21 from pa_cost_bases c
22 where c.cost_base = x_cost_base
23 and c.cost_base_type = x_cost_base_type
24 and ((x_rowid is NULL) or (rowid <> x_rowid)));
25
26 x_return_status := 0;
27
28 EXCEPTION
29 WHEN NO_DATA_FOUND then
30 x_return_status := 1;
31
32 WHEN OTHERS then
33 x_return_status := SQLCODE;
34
35 end check_unique;
36 ----------------------------------------------------------------------------
37 ----------------------------------------------------------------------------
38 -- This procedure checks if just the cost base has been used in CBICC or
39 -- Expenditure types
40 procedure check_ref_cost_base(x_return_status IN OUT NOCOPY number,
41 x_stage IN OUT NOCOPY number,
42 x_cost_base IN varchar2)
43 is
44 x_dummy number;
45
46 begin
47 x_return_status := 0;
48 x_stage := 0;
49
50 begin
51 select 1
52 into x_dummy
53 from sys.dual
57 where cbicc.cost_base = x_cost_base);
54 where not exists
55 (select 1
56 from pa_cost_base_cost_codes cbicc
58
59 x_return_status := 0; -- ie. value does not exist is child table
60 -- delete allowed.
61
62 EXCEPTION
63 when NO_DATA_FOUND then -- ie. value exists in child table
64 -- delete NOT allowed.
65 x_return_status := 1;
66 x_stage := 1;
67 return;
68
69 end;
70
71 -- check against the Cost base exp types table.
72
73 begin
74 select 1
75 into x_dummy
76 from sys.dual
77 where not exists
78 (select 1
79 from pa_cost_base_exp_types p
80 where p.cost_base = x_cost_base);
81
82 x_return_status := 0;
83
84 EXCEPTION
85 when NO_DATA_FOUND then
86 x_return_status := 1;
87 x_stage := 5;
88 return;
89
90 end;
91
92 EXCEPTION
93 when OTHERS then
94 x_return_status := SQLCODE;
95
96
97 end check_ref_cost_base;
98
99 ----------------------------------------------------------------------------
100 ----------------------------------------------------------------------------
101 -- this procedure will do a referential integrity check. Cost base appears
102 -- as a FK in 3 tables, PA_COMPILED_MULTIPLIERS, PA_COST_BASE_COST_CODES
103 -- and PA_COST_BASE_EXP_TYPES. However we may not check these tables in
104 -- all cases as it may not be logically relevant. So we will check against
105 -- the following. We will check against PA_COST_BASE_EXP_TYPES and
106 -- PA_COST_BASE_COST_CODES.
107 -- Also if data exists in PA_COST_BASE_COST_CODES, we will check further
108 -- to see if the exp item has been costed.
109
110 procedure check_references(x_return_status IN OUT NOCOPY number,
111 x_stage IN OUT NOCOPY number,
112 x_cost_base IN varchar2,
113 x_cost_base_type IN varchar2)
114 is
115 x_dummy number;
116
117 begin
118 x_return_status := 0;
119 x_stage := 0;
120
121 begin
122 select 1
123 into x_dummy
124 from sys.dual
125 where not exists
126 (select 1
127 from pa_cost_base_cost_codes cbicc
128 where cbicc.cost_base = x_cost_base
129 and cbicc.cost_base_type = x_cost_base_type);
130
131 x_return_status := 0; -- ie. value does not exist is child table
132 -- delete allowed.
133
134 EXCEPTION
135 when NO_DATA_FOUND then -- ie. value exists in child table
136 -- delete NOT allowed.
137 -- start checking further upto exp item level.
138 begin
139 x_return_status := 1; -- since it is part of the exception
140 x_stage := 1;
141 -- if this select returns data, it means
142 -- that exp items have been costed
143 -- However, if no data is found, exp items
144 -- have not been costed, the cost bases
145 -- exist in cbicc.
146 select 1
147 into x_dummy
148 from pa_cost_base_cost_codes cbicc,
149 pa_ind_cost_multipliers mul,
150 pa_ind_compiled_sets ics,
151 pa_expenditure_items ei
152 where cbicc.cost_base = x_cost_base
153 and cbicc.cost_base_type = x_cost_base_type
154 and cbicc.ind_cost_code = mul.ind_cost_code
155 and mul.ind_rate_sch_revision_id = ics.ind_rate_sch_revision_id
156 and ((ics.ind_compiled_set_id =
157 ei.cost_ind_compiled_set_id)
158 or (ics.ind_compiled_set_id =
159 ei.rev_ind_compiled_set_id)
160 or (ics.ind_compiled_set_id =
161 ei.inv_ind_compiled_set_id));
162
163 x_stage := 2; -- if exp items have been costed, ie data was
164 -- found.
165
166 EXCEPTION
167 when NO_DATA_FOUND then
168 x_return_status := 2;
169 return;
170
171 when TOO_MANY_ROWS then
172 x_return_status := 1;
173 x_stage := 2; -- as data is found, even tho' it is an exception.
174 return;
175 end;
176 end;
177
178 -- check against the Cost base exp types table.
179
180 begin
181 select 1
182 into x_dummy
183 from sys.dual
184 where not exists
185 (select 1
186 from pa_cost_base_exp_types p
187 where p.cost_base = x_cost_base
188 and p.cost_base_type = x_cost_base_type);
189
190 x_return_status := 0;
191
192 EXCEPTION
193 when NO_DATA_FOUND then
194 x_return_status := 1;
195 x_stage := 5;
196 return;
197
198 end;
199
200 EXCEPTION
201 when OTHERS then
202 x_return_status := SQLCODE;
203
204
205
206 end check_references;
207 ----------------------------------------------------------------------------
208 end PA_COST_BASES_PKG;
[Home] [Help]