4 -- constants
1 package body PA_COST_PLUS_STRUCTURE_PKG as
2 -- $Header: PAXCISTB.pls 120.1 2005/08/23 19:20:17 spunathi noship $
3
5 NO_DATA_FOUND_ERR CONSTANT number := 100;
6
7 procedure check_unique(cp_structure IN varchar2,
8 status IN OUT NOCOPY number) is
9 -- local variable
10 dummy number;
11 begin
12
13 status := 0;
14
15 SELECT 1 INTO dummy FROM sys.dual WHERE NOT EXISTS
16 (SELECT 1 FROM pa_cost_plus_structures
17 WHERE cost_plus_structure = cp_structure);
18
19 exception
20 when OTHERS then
21 status := SQLCODE;
22
23 end check_unique;
24
25
26 procedure check_references(cp_structure IN varchar2,
27 status IN OUT NOCOPY number) is
28
29 -- local variables
30 dummy number;
31
32 begin
33
34 status := 0;
35
36 -- check pa_cost_base_cost_codes table
37 -- check pa_cost_base_exp_types table
38
39 SELECT 1 INTO dummy FROM sys.dual WHERE NOT EXISTS
40 (SELECT 1 FROM pa_cost_base_cost_codes cbicc,
41 pa_cost_base_exp_types cbet
42 WHERE cbicc.cost_plus_structure = cp_structure
43 OR cbet.cost_plus_structure = cp_structure);
44
45 exception
46
47 when OTHERS then
48 -- there are at least one foreign key in details table
49 status := SQLCODE;
50
51 end check_references;
52
53
54 procedure check_revision(cp_structure IN varchar2,
55 status IN OUT NOCOPY number) is
56
57 -- local variables
58 dummy number;
59
60 begin
61
62 status := 0;
63
64 -- check pa_ind_rate_sch_revisions table
65
66 SELECT 1 INTO dummy FROM sys.dual WHERE NOT EXISTS
67 (SELECT 1 FROM pa_ind_rate_sch_revisions irsr
68 WHERE irsr.cost_plus_structure = cp_structure);
69
70 exception
71
72 when OTHERS then
73 -- there are at least one foreign key in details table
74 status := SQLCODE;
75
76 end check_revision;
77
78
79 procedure check_schedule(cp_structure IN varchar2,
80 status IN OUT NOCOPY number) is
81
82 -- local variables
83 dummy number;
84
85 begin
86
87 status := 0;
88
89 SELECT 1 INTO dummy FROM sys.dual WHERE NOT EXISTS
90 (SELECT 1
91 FROM pa_ind_rate_schedules
92 WHERE cost_plus_structure = cp_structure);
93
94 exception
95
96 when OTHERS then
97 -- there are at least one foreign key in details table
98 status := SQLCODE;
99
100 end check_schedule;
101
102
103 procedure check_bcc(cp_structure IN varchar2,
104 status IN OUT NOCOPY number) is
105
106 -- local variables
107 dummy number;
108
109 begin
110
111 status := 0;
112
113 SELECT 1 INTO dummy FROM sys.dual WHERE NOT EXISTS
114 (SELECT 1
115 FROM pa_cost_base_cost_codes
116 WHERE cost_plus_structure = cp_structure);
117
118 exception
119
120 when OTHERS then
121 -- there are at least one foreign key in details table
122 status := SQLCODE;
123
124 end check_bcc;
125
126
127 procedure check_default(status IN OUT NOCOPY number) is
128
129 -- local variables
130 dummy number;
131
132 begin
133
134 status := 0;
135
136 SELECT 1 INTO dummy FROM sys.dual WHERE NOT EXISTS
137 (SELECT 1
138 FROM pa_cost_plus_structures
139 WHERE default_for_override_sch_flag = 'Y');
140
141 exception
142
143 when OTHERS then
144 -- there are at least one foreign key in details table
145 status := SQLCODE;
146
147 end check_default;
148
149
150
151 /*
152 procedure check_default(start_date IN date,
153 end_date IN date,
154 status IN OUT number) is
155
156 -- local variables
157 dummy number;
158
159 begin
160
161 status := 0;
162
163 -- check pa_cost_plus_structures table
164 -- there are six cases
165 -- A
166 -- |--------------------|
167 -- B C D E F
168 -- |----| |----| |----| |----| |----|
169 -- Default
170 -- |--------------|
171 -- Cases A, C, D, and E violate that only one default structure can exist
172 -- any point of time.
173 -- Case B and F are acceptable.
174
175 SELECT 1 INTO dummy FROM sys.dual WHERE NOT EXISTS
176 (SELECT 1 FROM pa_cost_plus_structures cps
177 WHERE cps.default_for_override_sch_flag = 'Y'
178 AND ( -- case A
179 ( (TRUNC(cps.start_date_active)
180 BETWEEN
181 TRUNC(start_date) AND
182 TRUNC(NVL(end_date, cps.start_date_active)))
183 and ( end_date IS NULL
184 or (TRUNC(NVL(cps.end_date_active, end_date))
185 BETWEEN
186 TRUNC(start_date) AND
187 TRUNC(end_date)))
188 )
189 -- case C
190 or ( (TRUNC(cps.start_date_active)
191 BETWEEN
192 TRUNC(start_date) AND
193 TRUNC(NVL(end_date, cps.start_date_active)))
194 and (TRUNC(end_date)
195 BETWEEN
196 TRUNC(cps.start_date_active) AND
197 TRUNC(NVL(cps.end_date_active, end_date)))
198 )
199 -- case D
200 or ( (TRUNC(start_date)
201 BETWEEN
202 TRUNC(cps.start_date_active) AND
203 TRUNC(NVL(cps.end_date_active, end_date)))
204 and (TRUNC(end_date)
205 BETWEEN
206 TRUNC(cps.start_date_active) AND
207 TRUNC(NVL(cps.end_date_active, end_date)))
208 )
209 -- case E
210 or ( (TRUNC(start_date)
211 BETWEEN
212 TRUNC(cps.start_date_active) AND
213 TRUNC(NVL(cps.end_date_active, start_date)))
214 and ( cps.end_date_active IS NULL
215 or TRUNC(cps.end_date_active)
216 BETWEEN
217 TRUNC(start_date) AND
218 TRUNC(NVL(end_date, cps.end_date_active)))
219 )
220 )
221 );
222
223 exception
224
225 when OTHERS then
226 -- there is a default structure already
227 status := SQLCODE;
228
229 end check_default;
230 */
231
232
233 procedure clear_default(cp_structure IN varchar2,
234 status IN OUT NOCOPY number) is
235
236 begin
237
238 status := 0;
239
240 UPDATE pa_cost_plus_structures SET default_for_override_sch_flag = 'N'
241 WHERE cost_plus_structure = cp_structure;
242
243 COMMIT;
244
245 exception
246
247 when OTHERS then
248 -- there is a default structure already
249 status := SQLCODE;
250
251 end clear_default;
252
253 procedure update_precedence(cp_structure IN varchar2,
254 status IN OUT NOCOPY number) is
255
256 begin
257
258 status := 0;
259
260 UPDATE pa_cost_base_cost_codes
261 SET precedence = 1
262 WHERE cost_plus_structure = cp_structure;
263
264 COMMIT;
265
266 exception
267
268 when OTHERS then
269 -- there is a default structure already
270 status := SQLCODE;
271
272 end update_precedence;
273
274 procedure cascade_delete(cp_structure IN varchar2)
275 is
276 begin
277
278 DELETE pa_cost_base_cost_codes
279 WHERE cost_plus_structure = cp_structure;
280
281 DELETE pa_cost_base_exp_types
282 WHERE cost_plus_structure = cp_structure;
283
284 COMMIT;
285
286 end cascade_delete;
287
288 procedure cascade_update(old_cp_structure IN varchar2,
289 new_cp_structure IN varchar2)
290
291 is
292 begin
293
294 UPDATE pa_cost_base_cost_codes
295 SET cost_plus_structure = new_cp_structure
296 WHERE cost_plus_structure = old_cp_structure;
297
298 UPDATE pa_cost_base_exp_types
299 SET cost_plus_structure = new_cp_structure
300 WHERE cost_plus_structure = old_cp_structure;
301
302 COMMIT;
303
304 end cascade_update;
305
306
307
308
309 end PA_COST_PLUS_STRUCTURE_PKG ;
[Home] [Help]