DBA Data[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;