[Home] [Help]
PACKAGE BODY: APPS.GL_JE_LINES_RECON_PKG
Source
1 PACKAGE BODY GL_JE_LINES_RECON_PKG as
2 /* $Header: glirclnb.pls 120.9.12010000.2 2009/01/16 22:49:53 kdong ship $ */
3
4 PROCEDURE insert_rows_for_batch(X_Je_Batch_Id NUMBER,
5 X_Last_Updated_By NUMBER,
6 X_Last_Update_Login NUMBER) IS
7 BEGIN
8 INSERT INTO gl_je_lines_recon
9 (je_header_id, je_line_num, ledger_id,
10 creation_date, created_by, last_update_date,
11 last_updated_by, last_update_login, jgzz_recon_ref)
12 SELECT jeh.je_header_id, jel.je_line_num, jeh.ledger_id,
13 sysdate, X_Last_Updated_By, sysdate,
14 X_Last_Updated_By, X_Last_Update_Login, jeh.jgzz_recon_ref
15 FROM gl_je_batches jeb, gl_je_headers jeh, gl_ledgers lgr, gl_je_lines jel,
16 gl_code_combinations cc
17 WHERE jeb.je_batch_id = X_Je_Batch_Id
18 AND jeb.average_journal_flag = 'N'
19 AND jeh.je_batch_id = X_Je_Batch_Id
20 AND jeh.actual_flag = 'A'
21 AND jeh.je_source NOT IN ('Move/Merge', 'Move/Merge Reversal')
22 AND lgr.ledger_id = jeh.ledger_id
23 AND lgr.enable_reconciliation_flag = 'Y'
24 AND jel.je_header_id = jeh.je_header_id
25 AND cc.code_combination_id = jel.code_combination_id
26 AND cc.jgzz_recon_flag = 'Y';
27 END insert_rows_for_batch;
28
29 -- **********************************************************************
30
31 PROCEDURE insert_rows_for_journal(X_Je_Header_Id NUMBER,
32 X_Last_Updated_By NUMBER,
33 X_Last_Update_Login NUMBER) IS
34 BEGIN
35 INSERT INTO gl_je_lines_recon
36 (je_header_id, je_line_num, ledger_id,
37 creation_date, created_by, last_update_date,
38 last_updated_by, last_update_login, jgzz_recon_ref)
39 SELECT jel.je_header_id, jel.je_line_num, jel.ledger_id,
40 sysdate, X_Last_Updated_By, sysdate,
41 X_Last_Updated_By, X_Last_Update_Login, jeh.jgzz_recon_ref
42 FROM gl_je_headers jeh, gl_je_batches jeb, gl_ledgers lgr, gl_je_lines jel,
43 gl_code_combinations cc
44 WHERE jeh.je_header_id = X_Je_Header_Id
45 AND jeh.actual_flag = 'A'
46 AND jeh.je_source NOT IN ('Move/Merge', 'Move/Merge Reversal')
47 AND jeb.je_batch_id = jeh.je_batch_id
48 AND jeb.average_journal_flag = 'N'
49 AND jel.je_header_id = X_Je_Header_Id
50 AND lgr.ledger_id = jel.ledger_id
51 AND lgr.enable_reconciliation_flag = 'Y'
52 AND cc.code_combination_id = jel.code_combination_id
53 AND cc.jgzz_recon_flag = 'Y';
54 END insert_rows_for_journal;
55
56 -- **********************************************************************
57
58 PROCEDURE insert_rows_for_line(X_Je_Header_Id NUMBER,
59 X_Je_Line_Num NUMBER,
60 X_Last_Updated_By NUMBER,
61 X_Last_Update_Login NUMBER) IS
62 BEGIN
63 INSERT INTO gl_je_lines_recon
64 (je_header_id, je_line_num, ledger_id,
65 creation_date, created_by, last_update_date,
66 last_updated_by, last_update_login, jgzz_recon_ref)
67 SELECT jel.je_header_id, jel.je_line_num, jel.ledger_id,
68 sysdate, X_Last_Updated_By, sysdate,
69 X_Last_Updated_By, X_Last_Update_Login, jeh.jgzz_recon_ref
70 FROM gl_je_headers jeh, gl_je_batches jeb, gl_ledgers lgr, gl_je_lines jel,
71 gl_code_combinations cc
72 WHERE jeh.je_header_id = X_Je_Header_Id
73 AND jeh.actual_flag = 'A'
74 AND jeh.je_source NOT IN ('Move/Merge', 'Move/Merge Reversal')
75 AND jeb.je_batch_id = jeh.je_batch_id
76 AND jeb.average_journal_flag = 'N'
77 AND jel.je_header_id = X_Je_Header_Id
78 AND jel.je_line_num = X_Je_Line_Num
79 AND lgr.ledger_id = jel.ledger_id
80 AND lgr.enable_reconciliation_flag = 'Y'
81 AND cc.code_combination_id = jel.code_combination_id
82 AND cc.jgzz_recon_flag = 'Y';
83 END insert_rows_for_line;
84
85 -- **********************************************************************
86
87 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
88 X_Je_Header_Id NUMBER,
89 X_Je_Line_Num NUMBER,
90 X_Ledger_Id NUMBER,
91 X_Jgzz_Recon_Status VARCHAR2,
92 X_Jgzz_Recon_Date DATE,
93 X_Jgzz_Recon_Id NUMBER,
94 X_Jgzz_Recon_Ref VARCHAR2,
95 X_Last_Update_Date DATE,
96 X_Last_Updated_By NUMBER,
97 X_Last_Update_Login NUMBER
98 ) IS
99 CURSOR C IS SELECT rowid FROM GL_JE_LINES_RECON
100 WHERE je_header_id = X_Je_Header_Id
101 AND je_line_num = X_Je_Line_Num;
102
103 BEGIN
104
105 INSERT INTO GL_JE_LINES_RECON (
106 je_header_id,
107 je_line_num,
108 ledger_id,
109 jgzz_recon_status,
110 jgzz_recon_date,
111 jgzz_recon_id,
112 jgzz_recon_ref,
113 creation_date,
114 created_by,
115 last_update_date,
116 last_updated_by,
117 last_update_login
118 ) VALUES (
119 X_Je_Header_Id,
120 X_Je_Line_Num,
121 X_Ledger_id,
122 X_Jgzz_Recon_Status,
123 X_Jgzz_Recon_Date,
124 X_Jgzz_Recon_Id,
125 X_Jgzz_Recon_Ref,
126 X_Last_Update_Date,
127 X_Last_Updated_By,
128 X_Last_Update_Date,
129 X_Last_Updated_By,
130 X_Last_Update_Login
131 );
132
133 OPEN C;
134 FETCH C INTO X_Rowid;
135 if (C%NOTFOUND) then
136 CLOSE C;
137 Raise NO_DATA_FOUND;
138 end if;
139 CLOSE C;
140
141 END Insert_Row;
142
143 -- **********************************************************************
144
145 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
146 X_Je_Header_Id NUMBER,
147 X_Je_Line_Num NUMBER,
148 X_Ledger_Id NUMBER,
149 X_Jgzz_Recon_Status VARCHAR2,
150 X_Jgzz_Recon_Date DATE,
151 X_Jgzz_Recon_Id NUMBER,
152 X_Jgzz_Recon_Ref VARCHAR2
153 ) IS
154 CURSOR C IS
155 SELECT *
156 FROM GL_JE_LINES_RECON
157 WHERE rowid = X_Rowid
158 FOR UPDATE of Je_Header_Id NOWAIT;
159 Recinfo C%ROWTYPE;
160 BEGIN
161 OPEN C;
162 FETCH C INTO Recinfo;
163 if (C%NOTFOUND) then
164 CLOSE C;
165 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
166 APP_EXCEPTION.Raise_Exception;
167 end if;
168 CLOSE C;
169
170 if (
171 (Recinfo.je_header_id = X_Je_Header_Id)
172 AND (Recinfo.je_line_num = X_Je_Line_Num)
173 AND (Recinfo.ledger_id = X_Ledger_id)
174 AND ( (Recinfo.jgzz_recon_status = X_Jgzz_Recon_Status)
175 OR ( (rtrim(Recinfo.jgzz_recon_status,' ') IS NULL)
176 AND (X_Jgzz_Recon_Status IS NULL)))
177 AND ( (Recinfo.jgzz_recon_date = X_Jgzz_Recon_Date)
178 OR ( (rtrim(Recinfo.jgzz_recon_date,' ') IS NULL)
179 AND (X_Jgzz_Recon_Date IS NULL)))
180 AND ( (Recinfo.jgzz_recon_id = X_Jgzz_Recon_Id)
181 OR ( (rtrim(Recinfo.jgzz_recon_id,' ') IS NULL)
182 AND (X_Jgzz_Recon_Id IS NULL)))
183 AND ( (Recinfo.jgzz_recon_ref = X_Jgzz_Recon_Ref)
184 OR ( (rtrim(Recinfo.jgzz_recon_ref,' ') IS NULL)
185 AND (X_Jgzz_Recon_Ref IS NULL)))
186 ) then
187
188 RETURN;
189 else
190 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
191 APP_EXCEPTION.RAISE_EXCEPTION;
192 end if;
193 END Lock_Row;
194
195 -- **********************************************************************
196
197 PROCEDURE Update_Row(X_Rowid VARCHAR2,
198 X_Je_Header_Id NUMBER,
199 X_Je_Line_Num NUMBER,
200 X_Ledger_Id NUMBER,
201 X_Jgzz_Recon_Status VARCHAR2,
202 X_Jgzz_Recon_Date DATE,
203 X_Jgzz_Recon_Id NUMBER,
204 X_Jgzz_Recon_Ref VARCHAR2,
205 X_Last_Update_Date DATE,
206 X_Last_Updated_By NUMBER,
207 X_Last_Update_Login NUMBER
208 ) IS
209 BEGIN
210 UPDATE GL_JE_LINES_RECON
211 SET
212 je_header_id = X_Je_Header_Id,
213 je_line_num = X_Je_Line_Num,
214 ledger_id = X_Ledger_Id,
215 jgzz_recon_status = X_Jgzz_Recon_Status,
216 jgzz_recon_date = X_Jgzz_Recon_Date,
217 jgzz_recon_id = X_Jgzz_Recon_Id,
218 jgzz_recon_ref = X_Jgzz_Recon_Ref,
219 last_update_date = X_Last_Update_Date,
220 last_updated_by = X_Last_Updated_By,
221 last_update_login = X_Last_Update_Login
222 WHERE rowid = X_rowid;
223
224 if (SQL%NOTFOUND) then
225 Raise NO_DATA_FOUND;
226 end if;
227
228 END Update_Row;
229
230 -- **********************************************************************
231
232 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
233 BEGIN
234 DELETE FROM GL_JE_LINES_RECON
235 WHERE rowid = X_Rowid;
236
237 if (SQL%NOTFOUND) then
238 Raise NO_DATA_FOUND;
239 end if;
240
241 END Delete_Row;
242
243 -- **********************************************************************
244
245 FUNCTION insert_gen_line_recon_lines( X_Je_Header_Id NUMBER,
246 X_From_Je_Line_Num NUMBER,
247 X_Last_Updated_By NUMBER,
248 X_Last_Update_Login NUMBER)
249 RETURN NUMBER
250 IS
251 num_rows NUMBER;
252 BEGIN
253 INSERT INTO gl_je_lines_recon
254 (je_header_id, je_line_num, ledger_id,
255 creation_date, created_by, last_update_date,
256 last_updated_by, last_update_login, jgzz_recon_ref)
257 SELECT jel.je_header_id, jel.je_line_num, jel.ledger_id,
258 sysdate, X_Last_Updated_By, sysdate,
259 X_Last_Updated_By, X_Last_Update_Login, jeh.jgzz_recon_ref
260 FROM gl_je_headers jeh, gl_je_batches jeb, gl_ledgers lgr, gl_je_lines jel,
261 gl_code_combinations cc
262 WHERE jeh.je_header_id = X_Je_Header_Id
263 AND jeh.actual_flag = 'A'
264 AND jeh.je_source NOT IN ('Move/Merge', 'Move/Merge Reversal')
265 AND jeh.display_alc_journal_flag IS NULL
266 AND jeb.je_batch_id = jeh.je_batch_id
267 AND jeb.average_journal_flag = 'N'
268 AND jel.je_header_id = X_Je_Header_Id
269 AND jel.je_line_num >= X_From_Je_Line_Num
270 AND lgr.ledger_id = jel.ledger_id
271 AND lgr.enable_reconciliation_flag = 'Y'
272 AND cc.code_combination_id = jel.code_combination_id
273 AND cc.jgzz_recon_flag = 'Y';
274
275 num_rows := SQL%ROWCOUNT;
276 RETURN (num_rows);
277
278 EXCEPTION
279 WHEN app_exceptions.application_exception THEN
280 RAISE;
281 WHEN OTHERS THEN
282 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
283 fnd_message.set_token('PROCEDURE',
284 'gl_je_lines_recon_pkg.insert_gen_line_recon_lines');
285 RAISE;
286 END insert_gen_line_recon_lines;
287
288 -- **********************************************************************
289
290 FUNCTION insert_alc_recon_lines( X_Prun_Id NUMBER,
291 X_Last_Updated_By NUMBER,
292 X_Last_Update_Login NUMBER)
293 RETURN NUMBER
294 IS
295 num_rows NUMBER;
296 BEGIN
297 INSERT INTO gl_je_lines_recon
298 (je_header_id, je_line_num, ledger_id,
299 creation_date, created_by, last_update_date,
300 last_updated_by, last_update_login, jgzz_recon_ref)
301 SELECT jel.je_header_id, jel.je_line_num, jel.ledger_id,
302 sysdate, X_Last_Updated_By, sysdate,
303 X_Last_Updated_By, X_Last_Update_Login, jeh.jgzz_recon_ref
304 FROM gl_je_headers jeh, gl_je_batches jeb, gl_ledgers lgr, gl_je_lines jel,
305 gl_code_combinations cc
306 WHERE jeb.posting_run_id = X_Prun_Id
307 AND jeb.status = 'I'
308 AND jeb.average_journal_flag = 'N'
309 AND jeh.je_batch_id = jeb.je_batch_id
310 AND jeh.display_alc_journal_flag = 'N'
311 AND jeh.parent_je_header_id IS NOT NULL
312 AND jeh.actual_flag = 'A'
313 AND jeh.je_source NOT IN ('Move/Merge', 'Move/Merge Reversal')
314 AND jel.je_header_id = jeh.je_header_id
315 AND lgr.ledger_id = jel.ledger_id
316 AND lgr.enable_reconciliation_flag = 'Y'
317 AND cc.code_combination_id = jel.code_combination_id
318 AND cc.jgzz_recon_flag = 'Y';
319
320 num_rows := SQL%ROWCOUNT;
321 RETURN (num_rows);
322
323 EXCEPTION
324 WHEN app_exceptions.application_exception THEN
325 RAISE;
326 WHEN OTHERS THEN
327 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
328 fnd_message.set_token('PROCEDURE',
329 'gl_je_lines_recon_pkg.insert_alc_recon_lines');
330 RAISE;
331 END insert_alc_recon_lines;
332
333 -- **********************************************************************
334
335 FUNCTION insert_sl_recon_lines( X_Prun_Id NUMBER,
336 X_Last_Updated_By NUMBER,
337 X_Last_Update_Login NUMBER)
338 RETURN NUMBER
339 IS
340 num_rows NUMBER;
341 BEGIN
342 INSERT INTO gl_je_lines_recon
343 (je_header_id, je_line_num, ledger_id,
344 creation_date, created_by, last_update_date,
345 last_updated_by, last_update_login, jgzz_recon_ref)
346 SELECT sljel.je_header_id, sljel.je_line_num, sljel.ledger_id,
347 sysdate, X_Last_Updated_By, sysdate,
348 X_Last_Updated_By, X_Last_Update_Login, jeh.jgzz_recon_ref
349 FROM gl_je_headers jeh, gl_je_batches jeb, gl_ledgers lgr,
350 gl_je_batches sljeb, gl_je_headers sljeh, gl_je_lines sljel,
351 gl_code_combinations cc
352 WHERE jeb.posting_run_id = X_Prun_Id
353 AND jeb.status = 'I'
354 AND jeh.je_batch_id = jeb.je_batch_id
355 AND sljeh.parent_je_header_id = jeh.je_header_id
356 AND sljeh.display_alc_journal_flag IS NULL
357 AND sljeh.actual_flag = 'A'
358 AND sljeh.je_source NOT IN ('Move/Merge', 'Move/Merge Reversal')
359 AND sljeb.je_batch_id = sljeh.je_batch_id
360 AND sljeb.average_journal_flag = 'N'
361 AND sljel.je_header_id = sljeh.je_header_id
362 AND lgr.ledger_id = sljel.ledger_id
363 AND lgr.enable_reconciliation_flag = 'Y'
364 AND cc.code_combination_id = sljel.code_combination_id
365 AND cc.jgzz_recon_flag = 'Y';
366
367 num_rows := SQL%ROWCOUNT;
368 RETURN (num_rows);
369
370 EXCEPTION
371 WHEN app_exceptions.application_exception THEN
372 RAISE;
373 WHEN OTHERS THEN
374 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
375 fnd_message.set_token('PROCEDURE',
376 'gl_je_lines_recon_pkg.insert_sl_recon_lines');
377 RAISE;
378 END insert_sl_recon_lines;
379
380 -- **********************************************************************
381
382 END GL_JE_LINES_RECON_PKG;