[Home] [Help]
PACKAGE BODY: APPS.GLF02220_BJE_PKG
Source
1 PACKAGE BODY glf02220_bje_pkg AS
2 /* $Header: glfbdejb.pls 120.5 2005/07/29 16:57:39 djogg ship $ */
3
4 --
5 -- PUBLIC PROCEDURES
6 --
7
8 FUNCTION bje_trans_exists( X_status_number IN NUMBER,
9 X_ledger_id IN NUMBER,
10 X_period_year IN NUMBER,
11 X_start_period_num IN NUMBER,
12 X_end_period_num IN NUMBER ) RETURN BOOLEAN IS
13 CURSOR bte IS
14 SELECT
15 'Budget Journals Transactions exist'
16 FROM
17 DUAL
18 WHERE
19 EXISTS
20 (SELECT
21 'Budget Journals Transactions exist'
22 FROM
23 GL_BUDGET_RANGE_INTERIM BI,
24 GL_PERIOD_STATUSES PS
25 WHERE
26 BI.ledger_id = X_ledger_id
27 AND BI.status_number = X_status_number
28 AND PS.application_id = 101
29 AND PS.ledger_id = X_ledger_id
30 AND PS.period_year = X_period_year
31 AND PS.period_num BETWEEN X_start_period_num
32 AND X_end_period_num
33 AND PS.period_num =
34 decode(mod(PS.period_num - 1, 13) + 1,
35 1, decode(nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0),
36 0, -1, PS.period_num),
37 2, decode(nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0),
38 0, -1, PS.period_num),
39 3, decode(nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0),
40 0, -1, PS.period_num),
41 4, decode(nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0),
42 0, -1, PS.period_num),
43 5, decode(nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0),
44 0, -1, PS.period_num),
45 6, decode(nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0),
46 0, -1, PS.period_num),
47 7, decode(nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0),
48 0, -1, PS.period_num),
49 8, decode(nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0),
50 0, -1, PS.period_num),
51 9, decode(nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0),
52 0, -1, PS.period_num),
53 10, decode(nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0),
54 0, -1, PS.period_num),
55 11, decode(nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0),
56 0, -1, PS.period_num),
57 12, decode(nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0),
58 0, -1, PS.period_num),
59 13, decode(nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0),
60 0, -1, PS.period_num)));
61
62 dummy VARCHAR2(100);
63
64 BEGIN
65
66 OPEN bte;
67 FETCH bte INTO dummy;
68
69 IF bte%FOUND THEN
70 CLOSE bte;
71 return(TRUE);
72 ELSE
73 CLOSE bte;
74 return(FALSE);
75 END IF;
76
77 EXCEPTION
78 WHEN app_exceptions.application_exception THEN
79 RAISE;
80 WHEN OTHERS THEN
81 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
82 fnd_message.set_token('PROCEDURE', 'glf02220_bje_pkg.bje_trans_exists');
83 RAISE;
84
85 END bje_trans_exists;
86
87
88 PROCEDURE insert_bc_packet( X_packet_id IN OUT NOCOPY NUMBER,
89 X_status_number IN NUMBER,
90 X_ledger_id IN NUMBER,
91 X_je_category_name IN VARCHAR2,
92 X_fc_mode IN VARCHAR2,
93 X_je_batch_name IN VARCHAR2,
94 X_period_year IN NUMBER,
95 X_start_period_num IN NUMBER,
96 X_end_period_num IN NUMBER,
97 X_session_id IN NUMBER,
98 X_serial_id IN NUMBER) IS
99 BEGIN
100
101 -- Get packet id for this check/reserve process
102 X_packet_id := gl_bc_packets_pkg.get_unique_id;
103
104 INSERT INTO GL_BC_PACKETS
105 (packet_id,
106 ledger_id,
107 je_source_name,
108 je_category_name,
109 code_combination_id,
110 actual_flag,
111 period_name,
112 period_year,
113 period_num,
114 quarter_num,
115 currency_code,
116 status_code,
117 last_update_date,
118 last_updated_by,
119 budget_version_id,
120 entered_dr,
121 entered_cr,
122 accounted_dr,
123 accounted_cr,
124 je_batch_name,
125 je_line_description,
126 application_id,
127 session_id,
128 serial_id)
129 SELECT
130 X_packet_id,
131 X_ledger_id,
132 'Budget Journal',
133 X_je_category_name,
134 BI.code_combination_id,
135 'B',
136 PS.period_name,
137 PS.period_year,
138 PS.period_num,
139 PS.quarter_num,
140 BI.currency_code,
141 decode(X_fc_mode, 'R', 'P', 'C'),
142 sysdate,
143 BI.last_updated_by,
144 BI.budget_version_id,
145 decode(mod(PS.period_num - 1, 13) + 1,
146 1, decode(BI.dr_flag ||
147 decode(sign(nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0)),
148 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
149 1, 1, -1), '1', 0, NULL, '0'),
150 'Y1', nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0),
151 'N0', -1 * (nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0)),
152 NULL),
153 2, decode(BI.dr_flag ||
154 decode(sign(nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0)),
155 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
156 1, 1, -1), '1', 0, NULL, '0'),
157 'Y1', nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0),
158 'N0', -1 * (nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0)),
159 NULL),
160 3, decode(BI.dr_flag ||
161 decode(sign(nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0)),
162 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
163 1, 1, -1), '1', 0, NULL, '0'),
164 'Y1', nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0),
165 'N0', -1 * (nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0)),
166 NULL),
167 4, decode(BI.dr_flag ||
168 decode(sign(nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0)),
169 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
170 1, 1, -1), '1', 0, NULL, '0'),
171 'Y1', nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0),
172 'N0', -1 * (nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0)),
173 NULL),
174 5, decode(BI.dr_flag ||
175 decode(sign(nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0)),
176 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
177 1, 1, -1), '1', 0, NULL, '0'),
178 'Y1', nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0),
179 'N0', -1 * (nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0)),
180 NULL),
181 6, decode(BI.dr_flag ||
182 decode(sign(nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0)),
183 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
184 1, 1, -1), '1', 0, NULL, '0'),
185 'Y1', nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0),
186 'N0', -1 * (nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0)),
187 NULL),
188 7, decode(BI.dr_flag ||
189 decode(sign(nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0)),
190 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
191 1, 1, -1), '1', 0, NULL, '0'),
192 'Y1', nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0),
193 'N0', -1 * (nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0)),
194 NULL),
195 8, decode(BI.dr_flag ||
196 decode(sign(nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0)),
197 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
198 1, 1, -1), '1', 0, NULL, '0'),
199 'Y1', nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0),
200 'N0', -1 * (nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0)),
201 NULL),
202 9, decode(BI.dr_flag ||
203 decode(sign(nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0)),
204 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
205 1, 1, -1), '1', 0, NULL, '0'),
206 'Y1', nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0),
207 'N0', -1 * (nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0)),
208 NULL),
209 10, decode(BI.dr_flag ||
210 decode(sign(nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0)),
211 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
212 1, 1, -1), '1', 0, NULL, '0'),
213 'Y1', nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0),
214 'N0', -1 * (nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0)),
215 NULL),
216 11, decode(BI.dr_flag ||
217 decode(sign(nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0)),
218 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
219 1, 1, -1), '1', 0, NULL, '0'),
220 'Y1', nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0),
221 'N0', -1 * (nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0)),
222 NULL),
223 12, decode(BI.dr_flag ||
224 decode(sign(nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0)),
225 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
226 1, 1, -1), '1', 0, NULL, '0'),
227 'Y1', nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0),
228 'N0', -1 * (nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0)),
229 NULL),
230 13, decode(BI.dr_flag ||
231 decode(sign(nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0)),
232 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
233 1, 1, -1), '1', 0, NULL, '0'),
234 'Y1', nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0),
235 'N0', -1 * (nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0)),
236 NULL)),
237 decode(mod(PS.period_num - 1, 13) + 1,
238 1, decode(BI.dr_flag ||
239 decode(sign(nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0)),
240 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
241 1, 1, -1), '1', 0, NULL, '0'),
242 'N1', nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0),
243 'Y0', -1 * (nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0)),
244 NULL),
245 2, decode(BI.dr_flag ||
246 decode(sign(nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0)),
247 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
248 1, 1, -1), '1', 0, NULL, '0'),
249 'N1', nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0),
250 'Y0', -1 * (nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0)),
251 NULL),
252 3, decode(BI.dr_flag ||
253 decode(sign(nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0)),
254 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
255 1, 1, -1), '1', 0, NULL, '0'),
256 'N1', nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0),
257 'Y0', -1 * (nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0)),
258 NULL),
259 4, decode(BI.dr_flag ||
260 decode(sign(nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0)),
261 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
262 1, 1, -1), '1', 0, NULL, '0'),
263 'N1', nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0),
264 'Y0', -1 * (nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0)),
265 NULL),
266 5, decode(BI.dr_flag ||
267 decode(sign(nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0)),
268 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
269 1, 1, -1), '1', 0, NULL, '0'),
270 'N1', nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0),
271 'Y0', -1 * (nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0)),
272 NULL),
273 6, decode(BI.dr_flag ||
274 decode(sign(nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0)),
275 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
276 1, 1, -1), '1', 0, NULL, '0'),
277 'N1', nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0),
278 'Y0', -1 * (nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0)),
279 NULL),
280 7, decode(BI.dr_flag ||
281 decode(sign(nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0)),
282 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
283 1, 1, -1), '1', 0, NULL, '0'),
284 'N1', nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0),
285 'Y0', -1 * (nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0)),
286 NULL),
287 8, decode(BI.dr_flag ||
288 decode(sign(nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0)),
289 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
290 1, 1, -1), '1', 0, NULL, '0'),
291 'N1', nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0),
292 'Y0', -1 * (nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0)),
293 NULL),
294 9, decode(BI.dr_flag ||
295 decode(sign(nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0)),
296 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
297 1, 1, -1), '1', 0, NULL, '0'),
298 'N1', nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0),
299 'Y0', -1 * (nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0)),
300 NULL),
301 10, decode(BI.dr_flag ||
302 decode(sign(nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0)),
303 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
304 1, 1, -1), '1', 0, NULL, '0'),
305 'N1', nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0),
306 'Y0', -1 * (nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0)),
307 NULL),
308 11, decode(BI.dr_flag ||
309 decode(sign(nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0)),
310 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
311 1, 1, -1), '1', 0, NULL, '0'),
312 'N1', nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0),
313 'Y0', -1 * (nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0)),
314 NULL),
315 12, decode(BI.dr_flag ||
316 decode(sign(nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0)),
317 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
318 1, 1, -1), '1', 0, NULL, '0'),
319 'N1', nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0),
320 'Y0', -1 * (nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0)),
321 NULL),
322 13, decode(BI.dr_flag ||
323 decode(sign(nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0)),
324 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
325 1, 1, -1), '1', 0, NULL, '0'),
326 'N1', nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0),
327 'Y0', -1 * (nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0)),
328 NULL)),
329 decode(mod(PS.period_num - 1, 13) + 1,
330 1, decode(BI.dr_flag ||
331 decode(sign(nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0)),
332 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
333 1, 1, -1), '1', 0, NULL, '0'),
334 'Y1', nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0),
335 'N0', -1 * (nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0)),
336 NULL),
337 2, decode(BI.dr_flag ||
338 decode(sign(nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0)),
339 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
340 1, 1, -1), '1', 0, NULL, '0'),
341 'Y1', nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0),
342 'N0', -1 * (nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0)),
343 NULL),
344 3, decode(BI.dr_flag ||
345 decode(sign(nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0)),
346 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
347 1, 1, -1), '1', 0, NULL, '0'),
348 'Y1', nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0),
349 'N0', -1 * (nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0)),
350 NULL),
351 4, decode(BI.dr_flag ||
352 decode(sign(nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0)),
353 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
354 1, 1, -1), '1', 0, NULL, '0'),
355 'Y1', nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0),
356 'N0', -1 * (nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0)),
357 NULL),
358 5, decode(BI.dr_flag ||
359 decode(sign(nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0)),
360 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
361 1, 1, -1), '1', 0, NULL, '0'),
362 'Y1', nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0),
363 'N0', -1 * (nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0)),
364 NULL),
365 6, decode(BI.dr_flag ||
366 decode(sign(nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0)),
367 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
368 1, 1, -1), '1', 0, NULL, '0'),
369 'Y1', nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0),
370 'N0', -1 * (nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0)),
371 NULL),
372 7, decode(BI.dr_flag ||
373 decode(sign(nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0)),
374 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
375 1, 1, -1), '1', 0, NULL, '0'),
376 'Y1', nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0),
377 'N0', -1 * (nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0)),
378 NULL),
379 8, decode(BI.dr_flag ||
380 decode(sign(nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0)),
381 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
382 1, 1, -1), '1', 0, NULL, '0'),
383 'Y1', nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0),
384 'N0', -1 * (nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0)),
385 NULL),
386 9, decode(BI.dr_flag ||
387 decode(sign(nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0)),
388 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
389 1, 1, -1), '1', 0, NULL, '0'),
390 'Y1', nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0),
391 'N0', -1 * (nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0)),
392 NULL),
393 10, decode(BI.dr_flag ||
394 decode(sign(nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0)),
395 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
396 1, 1, -1), '1', 0, NULL, '0'),
397 'Y1', nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0),
398 'N0', -1 * (nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0)),
399 NULL),
400 11, decode(BI.dr_flag ||
401 decode(sign(nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0)),
402 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
403 1, 1, -1), '1', 0, NULL, '0'),
404 'Y1', nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0),
405 'N0', -1 * (nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0)),
406 NULL),
407 12, decode(BI.dr_flag ||
408 decode(sign(nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0)),
409 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
410 1, 1, -1), '1', 0, NULL, '0'),
411 'Y1', nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0),
412 'N0', -1 * (nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0)),
413 NULL),
414 13, decode(BI.dr_flag ||
415 decode(sign(nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0)),
416 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
417 1, 1, -1), '1', 0, NULL, '0'),
418 'Y1', nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0),
419 'N0', -1 * (nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0)),
420 NULL)),
421 decode(mod(PS.period_num - 1, 13) + 1,
422 1, decode(BI.dr_flag ||
423 decode(sign(nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0)),
424 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
425 1, 1, -1), '1', 0, NULL, '0'),
426 'N1', nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0),
427 'Y0', -1 * (nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0)),
428 NULL),
429 2, decode(BI.dr_flag ||
430 decode(sign(nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0)),
431 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
432 1, 1, -1), '1', 0, NULL, '0'),
433 'N1', nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0),
434 'Y0', -1 * (nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0)),
435 NULL),
436 3, decode(BI.dr_flag ||
437 decode(sign(nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0)),
438 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
439 1, 1, -1), '1', 0, NULL, '0'),
440 'N1', nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0),
441 'Y0', -1 * (nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0)),
442 NULL),
443 4, decode(BI.dr_flag ||
444 decode(sign(nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0)),
445 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
446 1, 1, -1), '1', 0, NULL, '0'),
447 'N1', nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0),
448 'Y0', -1 * (nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0)),
449 NULL),
450 5, decode(BI.dr_flag ||
451 decode(sign(nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0)),
452 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
453 1, 1, -1), '1', 0, NULL, '0'),
454 'N1', nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0),
455 'Y0', -1 * (nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0)),
456 NULL),
457 6, decode(BI.dr_flag ||
458 decode(sign(nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0)),
459 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
460 1, 1, -1), '1', 0, NULL, '0'),
461 'N1', nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0),
462 'Y0', -1 * (nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0)),
463 NULL),
464 7, decode(BI.dr_flag ||
465 decode(sign(nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0)),
466 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
467 1, 1, -1), '1', 0, NULL, '0'),
468 'N1', nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0),
469 'Y0', -1 * (nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0)),
470 NULL),
471 8, decode(BI.dr_flag ||
472 decode(sign(nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0)),
473 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
474 1, 1, -1), '1', 0, NULL, '0'),
475 'N1', nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0),
476 'Y0', -1 * (nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0)),
477 NULL),
478 9, decode(BI.dr_flag ||
479 decode(sign(nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0)),
480 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
481 1, 1, -1), '1', 0, NULL, '0'),
482 'N1', nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0),
483 'Y0', -1 * (nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0)),
484 NULL),
485 10, decode(BI.dr_flag ||
486 decode(sign(nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0)),
487 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
488 1, 1, -1), '1', 0, NULL, '0'),
489 'N1', nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0),
490 'Y0', -1 * (nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0)),
491 NULL),
492 11, decode(BI.dr_flag ||
493 decode(sign(nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0)),
494 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
495 1, 1, -1), '1', 0, NULL, '0'),
496 'N1', nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0),
497 'Y0', -1 * (nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0)),
498 NULL),
499 12, decode(BI.dr_flag ||
500 decode(sign(nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0)),
501 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
502 1, 1, -1), '1', 0, NULL, '0'),
503 'N1', nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0),
504 'Y0', -1 * (nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0)),
505 NULL),
506 13, decode(BI.dr_flag ||
507 decode(sign(nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0)),
508 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
509 1, 1, -1), '1', 0, NULL, '0'),
510 'N1', nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0),
511 'Y0', -1 * (nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0)),
512 NULL)),
513 X_je_batch_name,
514 decode( mod(PS.period_num - 1, 13) + 1,
515 1, BI.je_line_description1,
516 2, BI.je_line_description2,
517 3, BI.je_line_description3,
518 4, BI.je_line_description4,
519 5, BI.je_line_description5,
520 6, BI.je_line_description6,
521 7, BI.je_line_description7,
522 8, BI.je_line_description8,
523 9, BI.je_line_description9,
524 10, BI.je_line_description10,
525 11, BI.je_line_description11,
526 12, BI.je_line_description12,
527 13, BI.je_line_description13 ),
528 101,
529 X_Session_Id,
530 X_Serial_Id
531 FROM
532 GL_BUDGET_RANGE_INTERIM BI,
533 GL_PERIOD_STATUSES PS
534 WHERE
535 BI.ledger_id = X_ledger_id
536 AND BI.status_number = X_status_number
537 AND PS.application_id = 101
538 AND PS.ledger_id = X_ledger_id
539 AND PS.period_year = X_period_year
540 AND PS.period_num BETWEEN X_start_period_num
541 AND X_end_period_num
542 AND PS.period_num =
543 decode(mod(PS.period_num - 1, 13) + 1,
544 1, decode(nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0),
545 0, -1, PS.period_num),
546 2, decode(nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0),
547 0, -1, PS.period_num),
548 3, decode(nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0),
549 0, -1, PS.period_num),
550 4, decode(nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0),
551 0, -1, PS.period_num),
552 5, decode(nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0),
553 0, -1, PS.period_num),
554 6, decode(nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0),
555 0, -1, PS.period_num),
556 7, decode(nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0),
557 0, -1, PS.period_num),
558 8, decode(nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0),
559 0, -1, PS.period_num),
560 9, decode(nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0),
561 0, -1, PS.period_num),
562 10, decode(nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0),
563 0, -1, PS.period_num),
564 11, decode(nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0),
565 0, -1, PS.period_num),
566 12, decode(nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0),
567 0, -1, PS.period_num),
568 13, decode(nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0),
569 0, -1, PS.period_num));
570
571 EXCEPTION
572 WHEN app_exceptions.application_exception THEN
573 RAISE;
574 WHEN OTHERS THEN
575 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
576 fnd_message.set_token('PROCEDURE', 'glf02220_bje_pkg.insert_bc_packet');
577 RAISE;
578
579 END insert_bc_packet;
580
581
582 PROCEDURE insert_interface_rows( X_group_id IN OUT NOCOPY NUMBER,
583 X_status_number IN NUMBER,
584 X_ledger_id IN NUMBER,
585 X_user_je_category_name IN VARCHAR2,
586 X_je_batch_name IN VARCHAR2,
587 X_period_year IN NUMBER,
588 X_start_period_num IN NUMBER,
589 X_end_period_num IN NUMBER) IS
590 X_je_source_name VARCHAR2(25) := 'Budget Journal';
591 X_user_je_source_name VARCHAR2(25);
592 X_effective_date_rule_code VARCHAR2(1);
593 X_override_edits_flag VARCHAR2(1);
594 X_Journal_Approval_Flag VARCHAR2(1);
595 BEGIN
596
597 -- Get group_id for this Budget Journals process
598 X_group_id := gl_interface_control_pkg.get_unique_id;
599
600 -- Get translation for je_source_name 'Budget Journal'
601 gl_je_sources_pkg.select_columns( X_je_source_name,
602 X_user_je_source_name,
603 X_effective_date_rule_code,
604 X_override_edits_flag,
605 x_journal_approval_flag);
606
607 INSERT INTO GL_INTERFACE
608 (status,
609 ledger_id,
610 code_combination_id,
611 user_je_source_name,
612 user_je_category_name,
613 accounting_date,
614 currency_code,
615 date_created,
616 created_by,
617 actual_flag,
618 budget_version_id,
619 period_name,
620 group_id,
621 entered_dr,
622 entered_cr,
623 reference1,
624 reference10,
625 stat_amount)
626 SELECT
627 'NEW',
628 X_ledger_id,
629 BI.code_combination_id,
630 X_user_je_source_name,
631 X_user_je_category_name,
632 PS.start_date,
633 BI.currency_code,
634 sysdate,
635 BI.last_updated_by,
636 'B',
637 BI.budget_version_id,
638 PS.period_name,
639 X_group_id,
640 decode(mod(PS.period_num - 1, 13) + 1,
641 1, decode(BI.dr_flag ||
642 decode(sign(nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0)),
643 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
644 1, 1, -1), '1', 0, NULL, '0'),
645 'Y1', nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0),
646 'N0', -1 * (nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0)),
647 NULL),
648 2, decode(BI.dr_flag ||
649 decode(sign(nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0)),
650 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
651 1, 1, -1), '1', 0, NULL, '0'),
652 'Y1', nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0),
653 'N0', -1 * (nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0)),
654 NULL),
655 3, decode(BI.dr_flag ||
656 decode(sign(nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0)),
657 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
658 1, 1, -1), '1', 0, NULL, '0'),
659 'Y1', nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0),
660 'N0', -1 * (nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0)),
661 NULL),
662 4, decode(BI.dr_flag ||
663 decode(sign(nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0)),
664 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
665 1, 1, -1), '1', 0, NULL, '0'),
666 'Y1', nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0),
667 'N0', -1 * (nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0)),
668 NULL),
669 5, decode(BI.dr_flag ||
670 decode(sign(nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0)),
671 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
672 1, 1, -1), '1', 0, NULL, '0'),
673 'Y1', nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0),
674 'N0', -1 * (nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0)),
675 NULL),
676 6, decode(BI.dr_flag ||
677 decode(sign(nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0)),
678 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
679 1, 1, -1), '1', 0, NULL, '0'),
680 'Y1', nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0),
681 'N0', -1 * (nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0)),
682 NULL),
683 7, decode(BI.dr_flag ||
684 decode(sign(nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0)),
685 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
686 1, 1, -1), '1', 0, NULL, '0'),
687 'Y1', nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0),
688 'N0', -1 * (nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0)),
689 NULL),
690 8, decode(BI.dr_flag ||
691 decode(sign(nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0)),
692 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
693 1, 1, -1), '1', 0, NULL, '0'),
694 'Y1', nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0),
695 'N0', -1 * (nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0)),
696 NULL),
697 9, decode(BI.dr_flag ||
698 decode(sign(nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0)),
699 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
700 1, 1, -1), '1', 0, NULL, '0'),
701 'Y1', nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0),
702 'N0', -1 * (nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0)),
703 NULL),
704 10, decode(BI.dr_flag ||
705 decode(sign(nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0)),
706 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
707 1, 1, -1), '1', 0, NULL, '0'),
708 'Y1', nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0),
709 'N0', -1 * (nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0)),
710 NULL),
711 11, decode(BI.dr_flag ||
712 decode(sign(nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0)),
713 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
714 1, 1, -1), '1', 0, NULL, '0'),
715 'Y1', nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0),
716 'N0', -1 * (nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0)),
717 NULL),
718 12, decode(BI.dr_flag ||
719 decode(sign(nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0)),
720 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
721 1, 1, -1), '1', 0, NULL, '0'),
722 'Y1', nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0),
723 'N0', -1 * (nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0)),
724 NULL),
725 13, decode(BI.dr_flag ||
726 decode(sign(nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0)),
727 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
728 1, 1, -1), '1', 0, NULL, '0'),
729 'Y1', nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0),
730 'N0', -1 * (nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0)),
731 NULL)),
732 decode(mod(PS.period_num - 1, 13) + 1,
733 1, decode(BI.dr_flag ||
734 decode(sign(nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0)),
735 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
736 1, 1, -1), '1', 0, NULL, '0'),
737 'N1', nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0),
738 'Y0', -1 * (nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0)),
739 NULL),
740 2, decode(BI.dr_flag ||
741 decode(sign(nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0)),
742 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
743 1, 1, -1), '1', 0, NULL, '0'),
744 'N1', nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0),
745 'Y0', -1 * (nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0)),
746 NULL),
747 3, decode(BI.dr_flag ||
748 decode(sign(nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0)),
749 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
750 1, 1, -1), '1', 0, NULL, '0'),
751 'N1', nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0),
752 'Y0', -1 * (nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0)),
753 NULL),
754 4, decode(BI.dr_flag ||
755 decode(sign(nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0)),
756 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
757 1, 1, -1), '1', 0, NULL, '0'),
758 'N1', nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0),
759 'Y0', -1 * (nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0)),
760 NULL),
761 5, decode(BI.dr_flag ||
762 decode(sign(nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0)),
763 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
764 1, 1, -1), '1', 0, NULL, '0'),
765 'N1', nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0),
766 'Y0', -1 * (nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0)),
767 NULL),
768 6, decode(BI.dr_flag ||
769 decode(sign(nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0)),
770 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
771 1, 1, -1), '1', 0, NULL, '0'),
772 'N1', nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0),
773 'Y0', -1 * (nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0)),
774 NULL),
775 7, decode(BI.dr_flag ||
776 decode(sign(nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0)),
777 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
778 1, 1, -1), '1', 0, NULL, '0'),
779 'N1', nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0),
780 'Y0', -1 * (nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0)),
781 NULL),
782 8, decode(BI.dr_flag ||
783 decode(sign(nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0)),
784 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
785 1, 1, -1), '1', 0, NULL, '0'),
786 'N1', nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0),
787 'Y0', -1 * (nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0)),
788 NULL),
789 9, decode(BI.dr_flag ||
790 decode(sign(nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0)),
791 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
792 1, 1, -1), '1', 0, NULL, '0'),
793 'N1', nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0),
794 'Y0', -1 * (nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0)),
795 NULL),
796 10, decode(BI.dr_flag ||
797 decode(sign(nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0)),
798 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
799 1, 1, -1), '1', 0, NULL, '0'),
800 'N1', nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0),
801 'Y0', -1 * (nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0)),
802 NULL),
803 11, decode(BI.dr_flag ||
804 decode(sign(nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0)),
805 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
806 1, 1, -1), '1', 0, NULL, '0'),
807 'N1', nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0),
808 'Y0', -1 * (nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0)),
809 NULL),
810 12, decode(BI.dr_flag ||
811 decode(sign(nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0)),
812 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
813 1, 1, -1), '1', 0, NULL, '0'),
814 'N1', nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0),
815 'Y0', -1 * (nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0)),
816 NULL),
817 13, decode(BI.dr_flag ||
818 decode(sign(nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0)),
819 decode(substr(BI.je_drcr_sign_reference, mod(PS.period_num-1,13)+1, 1),
820 1, 1, -1), '1', 0, NULL, '0'),
821 'N1', nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0),
822 'Y0', -1 * (nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0)),
823 NULL)),
824 X_je_batch_name,
825 decode( mod(PS.period_num - 1, 13) + 1,
826 1, BI.je_line_description1,
827 2, BI.je_line_description2,
828 3, BI.je_line_description3,
829 4, BI.je_line_description4,
830 5, BI.je_line_description5,
831 6, BI.je_line_description6,
832 7, BI.je_line_description7,
833 8, BI.je_line_description8,
834 9, BI.je_line_description9,
835 10, BI.je_line_description10,
836 11, BI.je_line_description11,
837 12, BI.je_line_description12,
838 13, BI.je_line_description13 ),
839 decode( mod(PS.period_num - 1, 13) + 1,
840 1, BI.stat_amount1,
841 2, BI.stat_amount2,
842 3, BI.stat_amount3,
843 4, BI.stat_amount4,
844 5, BI.stat_amount5,
845 6, BI.stat_amount6,
846 7, BI.stat_amount7,
847 8, BI.stat_amount8,
848 9, BI.stat_amount9,
849 10, BI.stat_amount10,
850 11, BI.stat_amount11,
851 12, BI.stat_amount12,
852 13, BI.stat_amount13 )
853 FROM
854 GL_BUDGET_RANGE_INTERIM BI,
855 GL_PERIOD_STATUSES PS
856 WHERE
857 BI.ledger_id = X_ledger_id
858 AND BI.status_number = X_status_number
859 AND PS.application_id = 101
860 AND PS.ledger_id = X_ledger_id
861 AND PS.period_year = X_period_year
862 AND PS.period_num BETWEEN X_start_period_num
863 AND X_end_period_num
864 AND PS.period_num =
865 decode(mod(PS.period_num - 1, 13) + 1,
866 1, decode(nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0),
867 0, -1, PS.period_num),
868 2, decode(nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0),
869 0, -1, PS.period_num),
870 3, decode(nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0),
871 0, -1, PS.period_num),
872 4, decode(nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0),
873 0, -1, PS.period_num),
874 5, decode(nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0),
875 0, -1, PS.period_num),
876 6, decode(nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0),
877 0, -1, PS.period_num),
878 7, decode(nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0),
879 0, -1, PS.period_num),
880 8, decode(nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0),
881 0, -1, PS.period_num),
882 9, decode(nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0),
883 0, -1, PS.period_num),
884 10, decode(nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0),
885 0, -1, PS.period_num),
886 11, decode(nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0),
887 0, -1, PS.period_num),
888 12, decode(nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0),
889 0, -1, PS.period_num),
890 13, decode(nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0),
891 0, -1, PS.period_num));
892
893 EXCEPTION
894 WHEN app_exceptions.application_exception THEN
895 RAISE;
896 WHEN OTHERS THEN
897 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
898 fnd_message.set_token('PROCEDURE', 'glf02220_bje_pkg.insert_interface_rows');
899 RAISE;
900
901 END insert_interface_rows;
902
903
904 PROCEDURE delete_range_interim_records( X_status_number IN NUMBER ) IS
905 BEGIN
906
907 DELETE FROM GL_BUDGET_RANGE_INTERIM
908 WHERE
909 status_number = X_status_number;
910
911 END delete_range_interim_records;
912
913
914 END glf02220_bje_pkg;