1 PACKAGE BODY gl_summary_bc_options_pkg AS
2 /* $Header: glistbcb.pls 120.4.12010000.1 2008/07/28 13:26:46 appldev ship $ */
3
4 --
5 -- PUBLIC FUNCTIONS
6 --
7
8 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
9 X_Funds_Check_Level_Code VARCHAR2,
10 X_Dr_Cr_Code VARCHAR2,
11 X_Amount_Type VARCHAR2,
12 X_Boundary_Code VARCHAR2,
13 X_Template_Id NUMBER,
14 X_Last_Update_Date DATE,
15 X_Last_Updated_by NUMBER,
16 X_Created_By NUMBER,
17 X_Creation_Date DATE,
18 X_Last_Update_Login NUMBER,
19 X_Funding_Budget_Version_Id NUMBER
20 ) IS
21
22 CURSOR check_overlaps IS
23 SELECT 'Overlap'
24 FROM DUAL
25 WHERE EXISTS
26 (SELECT 'X'
27 FROM gl_budgets b1,
28 gl_budget_versions bv1,
29 gl_summary_bc_options sa,
30 gl_period_statuses pf1,
31 gl_period_statuses pl1,
32 gl_budgets b2,
33 gl_budget_versions bv2,
34 gl_period_statuses pf2,
35 gl_period_statuses pl2
36 WHERE b1.current_version_id = bv1.version_num
37 AND b1.budget_name = bv1.budget_name
38 AND bv1.budget_version_id = sa.funding_budget_version_id
39 AND b1.first_valid_period_name = pf1.period_name
40 AND b1.last_valid_period_name = pl1.period_name
41 AND b2.current_version_id = bv2.version_num
42 AND b2.budget_name = bv2.budget_name
43 AND bv2.budget_version_id = x_funding_budget_version_id
44 AND b2.first_valid_period_name = pf2.period_name
45 AND b2.last_valid_period_name = pl2.period_name
46 AND sa.template_id = X_Template_Id
47 AND pf1.application_id = 101
48 AND pf1.ledger_id = b1.ledger_id
49 AND pl1.application_id = 101
50 AND pl1.ledger_id = b1.ledger_id
51 AND pf2.application_id = 101
52 AND pf2.ledger_id = b2.ledger_id
53 AND pl2.application_id = 101
54 AND pl2.ledger_id = b2.ledger_id
55 AND NOT ( (pl1.effective_period_num < pf2.effective_period_num)
56 OR (pf1.effective_period_num > pl2.effective_period_num)
57 )
58 );
59
60
61
62 CURSOR C IS
63 SELECT rowid
64 FROM gl_summary_bc_options
65 WHERE template_id = X_Template_Id
66 AND funding_budget_version_id = X_Funding_Budget_Version_Id;
67
68 dummy VARCHAR2(100);
69 BEGIN
70
71 OPEN check_overlaps;
72 FETCH check_overlaps into dummy;
73 IF check_overlaps%FOUND THEN
74 CLOSE check_overlaps;
75 fnd_message.set_name('SQLGL', 'GL_BC_BUDGET_OVERLAP');
76 app_exception.raise_exception;
77 ELSE
78 CLOSE check_overlaps;
79 END IF;
80
81 INSERT INTO GL_SUMMARY_BC_OPTIONS(
82 template_id,
83 last_update_date,
84 last_updated_by,
85 funds_check_level_code,
86 amount_type,
87 boundary_code,
88 created_by,
89 creation_date,
90 dr_cr_code,
91 last_update_login,
92 funding_budget_version_id)
93 VALUES (
94 X_Template_Id,
95 X_Last_Update_Date,
96 X_Last_Updated_By,
97 X_Funds_Check_Level_Code,
98 X_Amount_Type,
99 X_Boundary_Code,
100 X_Created_By,
101 X_Creation_Date,
102 X_Dr_Cr_Code,
103 X_Last_Update_Login,
104 X_Funding_Budget_Version_Id);
105
106 OPEN C;
107 FETCH C INTO X_Rowid;
108 if (C%NOTFOUND) then
109 CLOSE C;
110 RAISE NO_DATA_FOUND;
111 end if;
112 CLOSE C;
113 END Insert_Row;
114
115
116 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
117 X_Funds_Check_Level_Code VARCHAR2,
118 X_Dr_Cr_Code VARCHAR2,
119 X_Amount_Type VARCHAR2,
120 X_Boundary_Code VARCHAR2,
121 X_Template_Id NUMBER,
122 X_Last_Update_Date DATE,
123 X_Last_Updated_by NUMBER,
124 X_Created_By NUMBER,
125 X_Creation_Date DATE,
126 X_Last_Update_Login NUMBER,
127 X_Funding_Budget_Version_Id NUMBER
128 ) IS
129 CURSOR C IS
130 SELECT *
131 FROM GL_SUMMARY_BC_OPTIONS
132 WHERE rowid = X_Rowid
133 FOR UPDATE of template_id NOWAIT;
134 Recinfo C%ROWTYPE;
135 BEGIN
136 OPEN C;
137 FETCH C INTO Recinfo;
138 if (C%NOTFOUND) then
139 CLOSE C;
140 RAISE NO_DATA_FOUND;
141 end if;
142 CLOSE C;
143 if(
144 ( (Recinfo.template_id = X_Template_Id)
145 OR ( (Recinfo.template_id IS NULL)
146 AND (X_Template_Id IS NULL)))
147 AND ( (Recinfo.last_update_date = X_Last_Update_Date)
148 OR ( (Recinfo.last_update_date IS NULL)
149 AND (X_Last_Update_Date IS NULL)))
150 AND ( (Recinfo.last_updated_by = X_Last_Updated_By)
151 OR ( (Recinfo.last_updated_by IS NULL)
152 AND (X_Last_Updated_By IS NULL)))
153 AND ( (Recinfo.funds_check_level_code = X_Funds_Check_Level_Code)
154 OR ( (Recinfo.funds_check_level_code IS NULL)
155 AND (X_Funds_Check_Level_Code IS NULL)))
156 AND ( (Recinfo.amount_type = X_Amount_Type)
157 OR ( (Recinfo.amount_type IS NULL)
158 AND (X_Amount_Type IS NULL)))
159 AND ( (Recinfo.boundary_code = X_Boundary_Code)
160 OR ( (Recinfo.boundary_code IS NULL)
161 AND (X_Boundary_Code IS NULL)))
162 AND ( (Recinfo.created_by = X_Created_By)
163 OR ( (Recinfo.created_by IS NULL)
164 AND (X_Created_By IS NULL)))
165 AND ( (Recinfo.creation_date = X_Creation_Date)
166 OR ( (Recinfo.creation_date IS NULL)
167 AND (X_Creation_Date IS NULL)))
168 AND ( (Recinfo.dr_cr_code = X_Dr_Cr_Code)
169 OR ( (Recinfo.dr_cr_code IS NULL)
170 AND (X_Dr_Cr_Code IS NULL)))
171 AND ( (Recinfo.last_update_login = X_Last_Update_Login)
172 OR ( (Recinfo.last_update_login IS NULL)
173 AND (X_Last_Update_Login IS NULL)))
174 AND ( (Recinfo.funding_budget_version_id = X_Funding_Budget_Version_Id)
175 OR ( (Recinfo.funding_budget_version_id IS NULL)
176 AND (X_Funding_Budget_Version_Id IS NULL)))
177 ) then
178 return;
179 else
180 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
181 APP_EXCEPTION.RAISE_EXCEPTION;
182 end if;
183 END Lock_Row;
184
185 PROCEDURE Insert_BC_Options(X_Rowid IN OUT NOCOPY VARCHAR2,
186 X_Funds_Check_Level_Code VARCHAR2,
187 X_Dr_Cr_Code VARCHAR2,
188 X_Amount_Type VARCHAR2,
189 X_Boundary_Code VARCHAR2,
190 X_Template_Id NUMBER,
191 X_Last_Update_Date DATE,
192 X_Last_Updated_by NUMBER,
193 X_Created_By NUMBER,
194 X_Creation_Date DATE,
195 X_Last_Update_Login NUMBER,
196 X_Funding_Budget_Version_Id NUMBER
197 ) IS
198
199 CURSOR check_funds_check_level IS
200 SELECT 'X'
201 FROM GL_LOOKUPS
202 WHERE LOOKUP_TYPE = 'FUNDS_CHECK_LEVEL'
203 AND lookup_code = X_Funds_Check_Level_Code;
204
205 CURSOR check_amount_type IS
206 SELECT 'X'
207 FROM GL_LOOKUPS_AMOUNT_TYPES_V
208 WHERE amount_type = X_Amount_Type;
209
210 CURSOR check_boundary_code IS
211 SELECT 'X'
212 FROM GL_LOOKUPS_BOUNDARIES_V
213 WHERE boundary_code = X_Boundary_Code;
214
215 CURSOR check_budget_version IS
216 SELECT 'X'
217 FROM GL_BUDGET_VERSIONS
218 WHERE budget_version_id = X_Funding_Budget_Version_Id;
219
220 CURSOR check_dr_cr_code IS
221 SELECT 'X'
222 FROM GL_LOOKUPS
223 WHERE LOOKUP_TYPE = 'DR_CR'
224 AND lookup_code = X_Dr_Cr_Code;
225
226 L_Budgetary_Control_Flag VARCHAR2(1);
227 dummy VARCHAR2(80);
228
229 BEGIN
230
231
232 -- Validate Funds Check Level
233 IF (X_Funds_Check_Level_Code IN ('D', 'B')) THEN
234 OPEN check_funds_check_level;
235 FETCH check_funds_check_level INTO dummy;
236 IF check_funds_check_level%NOTFOUND THEN
237 CLOSE check_funds_check_level;
238 fnd_message.set_name('SQLGL', 'GL_API_VALUE_NOT_EXIST');
239 fnd_message.set_token('VALUE', X_Funds_Check_Level_Code);
240 fnd_message.set_token('ATTRIBUTE', 'FundsCheckLevelCode');
241 app_exception.raise_exception;
242 END IF;
243 CLOSE check_funds_check_level;
244 ELSIF (X_Funds_Check_Level_Code IS NULL) THEN
245 fnd_message.set_name('SQLGL', 'GL_API_NULL_VALUE_ERROR');
246 fnd_message.set_token('ATTRIBUTE', 'FundsCheckLevelCode');
247 app_exception.raise_exception;
248 ELSE
249 fnd_message.set_name('SQLGL', 'GL_API_INVALID_VALUE');
250 fnd_message.set_token('VALUE', X_Funds_Check_Level_Code);
251 fnd_message.set_token('ATTRIBUTE', 'FundsCheckLevelCode');
252 app_exception.raise_exception;
253 END IF;
254
255 -- Validate Amount Type
256 IF (X_Amount_Type IS NOT NULL) THEN
257 OPEN check_amount_type;
258 FETCH check_amount_type INTO dummy;
259 IF check_amount_type%NOTFOUND THEN
260 CLOSE check_amount_type;
261 fnd_message.set_name('SQLGL', 'GL_API_VALUE_NOT_EXIST');
262 fnd_message.set_token('VALUE', X_Amount_Type);
263 fnd_message.set_token('ATTRIBUTE', 'AmountType');
264 app_exception.raise_exception;
265 END IF;
266 CLOSE check_amount_type;
267 ELSE
268 fnd_message.set_name('SQLGL', 'GL_API_NULL_VALUE_ERROR');
269 fnd_message.set_token('ATTRIBUTE', 'AmountType');
270 app_exception.raise_exception;
271 END IF;
272
273 -- Validate Boundary Code
274 IF (X_Boundary_Code IS NOT NULL) THEN
275 OPEN check_boundary_code;
276 FETCH check_boundary_code INTO dummy;
277 IF check_boundary_code%NOTFOUND THEN
278 CLOSE check_boundary_code;
279 fnd_message.set_name('SQLGL', 'GL_API_VALUE_NOT_EXIST');
280 fnd_message.set_token('VALUE', X_Boundary_Code);
281 fnd_message.set_token('ATTRIBUTE', 'BoundaryCode');
282 app_exception.raise_exception;
283 END IF;
284 CLOSE check_boundary_code;
285 ELSE
286 fnd_message.set_name('SQLGL', 'GL_API_NULL_VALUE_ERROR');
287 fnd_message.set_token('ATTRIBUTE', 'BoundaryCode');
288 app_exception.raise_exception;
289 END IF;
290
291 -- Validate Budget Version
292 IF (X_Funding_Budget_Version_Id IS NOT NULL) THEN
293 OPEN check_budget_version;
294 FETCH check_budget_version INTO dummy;
295 IF check_budget_version%NOTFOUND THEN
296 CLOSE check_budget_version;
297 fnd_message.set_name('SQLGL', 'GL_API_VALUE_NOT_EXIST');
298 fnd_message.set_token('VALUE', X_Funding_Budget_Version_Id);
299 fnd_message.set_token('ATTRIBUTE', 'FundingBudgetVersionId');
300 app_exception.raise_exception;
301 END IF;
302 CLOSE check_budget_version;
303 ELSE
304 fnd_message.set_name('SQLGL', 'GL_API_NULL_VALUE_ERROR');
305 fnd_message.set_token('ATTRIBUTE', 'FundingBudgetVersionId');
306 app_exception.raise_exception;
307 END IF;
308
309 -- Validate Debit/Credit Code
310 IF (X_Dr_Cr_Code IS NOT NULL) THEN
311 OPEN check_dr_cr_code;
312 FETCH check_dr_cr_code INTO dummy;
313 IF check_dr_cr_code%NOTFOUND THEN
314 CLOSE check_dr_cr_code;
315 fnd_message.set_name('SQLGL', 'GL_API_VALUE_NOT_EXIST');
316 fnd_message.set_token('VALUE', X_Dr_Cr_Code);
317 fnd_message.set_token('ATTRIBUTE', 'DrCrCode');
318 app_exception.raise_exception;
319 END IF;
320 CLOSE check_dr_cr_code;
321 ELSE
322 fnd_message.set_name('SQLGL', 'GL_API_NULL_VALUE_ERROR');
323 fnd_message.set_token('ATTRIBUTE', 'DrCrCode');
324 app_exception.raise_exception;
325 END IF;
326
327 SELECT gl1.enable_budgetary_control_flag
328 INTO L_Budgetary_Control_Flag
329 FROM gl_ledgers gl1, gl_summary_templates gl2
330 WHERE gl2.template_id = X_Template_Id
331 AND gl1.ledger_id = gl2.ledger_id;
332
333
334 -- Validate that funds check level code is D or B only if the set of
335 -- books is budgetary control enabled.
336 IF (X_Funds_Check_Level_Code = 'D' OR X_Funds_Check_Level_Code = 'B') THEN
337 IF (L_Budgetary_Control_Flag = 'Y') THEN
338 NULL;
339 ELSE
340 fnd_message.set_name('SQLGL', 'GL_API_SUMMTEMPL_FUNDS_LEVEL');
341 app_exception.raise_exception;
342 END IF;
343 END IF;
344
345 -- Validate that boundary code is a logical selection depending on the
346 -- amount type
347 IF (X_Amount_Type = 'PTD') THEN
348 IF (X_Boundary_Code <> 'P') THEN
349 fnd_message.set_name('SQLGL', 'GL_API_SUMMTEMPL_BOUNDARY_ERR');
350 app_exception.raise_exception;
351 END IF;
352 ELSIF (X_Amount_Type = 'QTD') THEN
353 IF (X_Boundary_Code NOT IN ('P', 'Q')) THEN
354 fnd_message.set_name('SQLGL', 'GL_API_SUMMTEMPL_BOUNDARY_ERR');
355 app_exception.raise_exception;
356 END IF;
357 ELSIF (X_Amount_Type = 'YTD') THEN
358 IF (X_Boundary_Code NOT IN ('P', 'Q', 'Y')) THEN
359 fnd_message.set_name('SQLGL', 'GL_API_SUMMTEMPL_BOUNDARY_ERR');
360 app_exception.raise_exception;
361 END IF;
362 ELSIF (X_Amount_Type = 'PJTD') THEN
363 -- Already checked boundary code is J, P, Q or Y
364 NULL;
365 END IF;
366
367 Insert_Row(X_Rowid,
368 X_Funds_Check_Level_Code,
369 X_Dr_Cr_Code,
370 X_Amount_Type,
371 X_Boundary_Code,
372 X_Template_Id,
373 X_Last_Update_Date,
374 X_Last_Updated_by,
375 X_Created_By,
376 X_Creation_Date,
377 X_Last_Update_Login,
378 X_Funding_Budget_Version_Id
379 );
380
381
382 EXCEPTION
383 WHEN app_exceptions.application_exception THEN
384 RAISE;
385 WHEN OTHERS THEN
386 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
387 fnd_message.set_token('PROCEDURE',
388 'GL_SUMMARY_BC_OPTIONS_PKG.Insert_BC_Options');
389 RAISE;
390
391 END Insert_BC_Options;
392
393 END gl_summary_bc_options_pkg;
[Home] [Help]