[Home] [Help]
PACKAGE BODY: APPS.GL_JE_BATCHES_POST_PKG
Source
1 PACKAGE BODY GL_JE_BATCHES_POST_PKG as
2 /* $Header: glijebpb.pls 120.5 2005/09/19 18:39:19 kvora ship $ */
3
4 PROCEDURE set_access_set_id ( X_access_set_id NUMBER) IS
5 BEGIN
6 gl_je_batches_post_pkg.access_set_id := X_access_set_id;
7 END set_access_set_id;
8
9 FUNCTION get_access_set_id RETURN NUMBER IS
10 BEGIN
11 RETURN gl_je_batches_post_pkg.access_set_id;
12 END get_access_set_id;
13
14 FUNCTION get_unique_id RETURN NUMBER IS
15 CURSOR get_new_id IS
16 SELECT gl_je_posting_s.NEXTVAL
17 FROM dual;
18 new_id number;
19 BEGIN
20 OPEN get_new_id;
21 FETCH get_new_id INTO new_id;
22 IF get_new_id%FOUND THEN
23 CLOSE get_new_id;
24 return(new_id);
25 ELSE
26 CLOSE get_new_id;
27 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
28 fnd_message.set_token('SEQUENCE', 'GL_JE_POSTING_S');
29 app_exception.raise_exception;
30 END IF;
31 EXCEPTION
32 WHEN app_exceptions.application_exception THEN
33 RAISE;
34 WHEN OTHERS THEN
35 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
36 fnd_message.set_token('PROCEDURE', 'gl_je_batches_post_pkg.get_unique_id');
37 RAISE;
38 END get_unique_id;
39
40
41 FUNCTION check_budget_status( X_je_batch_id NUMBER,
42 X_period_year NUMBER ) RETURN VARCHAR2 IS
43 CURSOR check_frozen IS
44 SELECT
45 'BF'
46 FROM
47 dual
48 WHERE EXISTS
49 ( SELECT 'Frozen budget'
50 FROM GL_BUDGET_VERSIONS BV,
51 GL_JE_HEADERS JH
52 WHERE BV.status in ('I', 'F')
53 AND BV.budget_version_id = JH.budget_version_id
54 AND JH.je_batch_id = X_je_batch_id );
55
56 CURSOR check_unopened_year IS
57 SELECT
58 'BU'
59 FROM
60 dual
61 WHERE EXISTS
62 ( SELECT 'Unopened budget year'
63 FROM GL_BUDGETS GB,
64 GL_BUDGET_VERSIONS BV,
65 GL_JE_HEADERS JH
66 WHERE GB.budget_name = BV.budget_name
67 AND GB.budget_type = BV.budget_type
68 AND ( GB.latest_opened_year IS NULL
69 OR GB.latest_opened_year < X_period_year )
70 AND BV.budget_version_id = JH.budget_version_id
71 AND JH.je_batch_id = X_je_batch_id );
72
73 batch_status VARCHAR2(2);
74 BEGIN
75 OPEN check_frozen;
76 FETCH check_frozen INTO batch_status;
77 IF check_frozen%FOUND THEN
78 CLOSE check_frozen;
79 RETURN( batch_status );
80 ELSE
81 CLOSE check_frozen;
82 OPEN check_unopened_year;
83 FETCH check_unopened_year INTO batch_status;
84 IF check_unopened_year%FOUND THEN
85 CLOSE check_unopened_year;
86 RETURN( batch_status );
87 ELSE
88 CLOSE check_unopened_year;
89 RETURN( 'OK' );
90 END IF;
91 END IF;
92 END check_budget_status;
93
94
95 FUNCTION check_unbal_monetary_headers (X_je_batch_id NUMBER) RETURN BOOLEAN IS
96 CURSOR chk_unbal_monetary_headers IS
97 SELECT 'Out of Balance'
98 FROM SYS.DUAL
99 WHERE EXISTS
100 ( SELECT 'x'
101 FROM GL_JE_HEADERS
102 WHERE je_batch_id = X_je_batch_id
103 AND nvl(display_alc_journal_flag, 'Y') = 'Y'
104 AND currency_code <> 'STAT'
105 AND nvl(running_total_dr, 0) <> nvl(running_total_cr, 0));
106 dummy VARCHAR2(100);
107 BEGIN
108 OPEN chk_unbal_monetary_headers;
109 FETCH chk_unbal_monetary_headers INTO dummy;
110
111 IF chk_unbal_monetary_headers%FOUND THEN
112 CLOSE chk_unbal_monetary_headers;
113 return (TRUE);
114 ELSE
115 CLOSE chk_unbal_monetary_headers;
116 return (FALSE);
117 END IF;
118 END check_unbal_monetary_headers;
119
120
121 FUNCTION check_untax_monetary_headers (X_je_batch_id NUMBER) RETURN BOOLEAN IS
122 CURSOR chk_untax_monetary_headers IS
123 SELECT 'Untaxed'
124 FROM SYS.DUAL
125 WHERE EXISTS
126 ( SELECT 'x'
127 FROM GL_JE_HEADERS
128 WHERE je_batch_id = X_je_batch_id
129 AND nvl(display_alc_journal_flag, 'Y') = 'Y'
130 AND currency_code <> 'STAT'
131 AND tax_status_code = 'R');
132 dummy VARCHAR2(100);
133 BEGIN
134 OPEN chk_untax_monetary_headers;
135 FETCH chk_untax_monetary_headers INTO dummy;
136
137 IF chk_untax_monetary_headers%FOUND THEN
138 CLOSE chk_untax_monetary_headers;
139 return (TRUE);
140
141 ELSE
142 CLOSE chk_untax_monetary_headers;
143 return (FALSE);
144 END IF;
145 END check_untax_monetary_headers;
146
147
148 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
149 X_Je_Batch_Id NUMBER,
150 X_Chart_Of_Accounts_Id NUMBER,
151 X_Name VARCHAR2,
152 X_Status VARCHAR2,
153 X_Status_Verified VARCHAR2,
154 X_Actual_Flag VARCHAR2,
155 X_Budgetary_Control_Status VARCHAR2,
156 X_Default_Period_Name VARCHAR2,
157 X_Control_Total NUMBER,
158 X_Running_Total_Dr NUMBER,
159 X_Running_Total_Cr NUMBER,
160 X_Posting_Run_Id NUMBER,
161 X_Request_Id NUMBER
162 ) IS
163 CURSOR C IS
164 SELECT *
165 FROM gl_je_batches
166 WHERE rowid = X_Rowid
167 FOR UPDATE of Je_Batch_Id NOWAIT;
168 Recinfo C%ROWTYPE;
169 BEGIN
170 OPEN C;
171 FETCH C INTO Recinfo;
172 if (C%NOTFOUND) then
173 CLOSE C;
174 RAISE NO_DATA_FOUND;
175 end if;
176 CLOSE C;
177 if (
178 (Recinfo.je_batch_id = X_Je_Batch_Id)
179 AND (Recinfo.chart_of_accounts_id = X_Chart_Of_Accounts_Id)
180 AND (Recinfo.name = X_Name)
181 AND (Recinfo.status = X_Status)
182 AND (Recinfo.status_verified = X_Status_Verified)
183 AND (Recinfo.actual_flag = X_Actual_Flag)
184 AND (Recinfo.budgetary_control_status = X_Budgetary_Control_Status)
185 AND ( (Recinfo.default_period_name = X_Default_Period_Name)
186 OR ( (Recinfo.default_period_name IS NULL)
187 AND (X_Default_Period_Name IS NULL)))
188 AND ( (Recinfo.control_total = X_Control_Total)
189 OR ( (Recinfo.control_total IS NULL)
190 AND (X_Control_Total IS NULL)))
191 AND ( (Recinfo.running_total_dr = X_Running_Total_Dr)
192 OR ( (Recinfo.running_total_dr IS NULL)
193 AND (X_Running_Total_Dr IS NULL)))
194 AND ( (Recinfo.running_total_cr = X_Running_Total_Cr)
195 OR ( (Recinfo.running_total_cr IS NULL)
196 AND (X_Running_Total_Cr IS NULL)))
197 AND ( (Recinfo.posting_run_id = X_Posting_Run_Id)
198 OR ( (Recinfo.posting_run_id IS NULL)
199 AND (X_Posting_Run_Id IS NULL)))
200 AND ( (Recinfo.request_id = X_Request_Id)
201 OR ( (Recinfo.request_id IS NULL)
202 AND (X_Request_Id IS NULL)))
203 ) then
204 return;
205 else
206 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
207 APP_EXCEPTION.RAISE_EXCEPTION;
208 end if;
209 END Lock_Row;
210
211
212 PROCEDURE Update_Row(X_Rowid VARCHAR2,
213 X_Je_Batch_Id NUMBER,
214 X_Last_Update_Date DATE,
215 X_Last_Updated_By NUMBER,
216 X_Chart_Of_Accounts_Id NUMBER,
217 X_Name VARCHAR2,
218 X_Status VARCHAR2,
219 X_Status_Verified VARCHAR2,
220 X_Actual_Flag VARCHAR2,
221 X_Budgetary_Control_Status VARCHAR2,
222 X_Last_Update_Login NUMBER,
223 X_Default_Period_Name VARCHAR2,
224 X_Control_Total NUMBER,
225 X_Running_Total_Dr NUMBER,
226 X_Running_Total_Cr NUMBER,
227 X_Posting_Run_Id NUMBER,
228 X_Request_Id NUMBER
229 ) IS
230 BEGIN
231 UPDATE gl_je_batches
232 SET
233 je_batch_id = X_Je_Batch_Id,
234 last_update_date = X_Last_Update_Date,
235 last_updated_by = X_Last_Updated_By,
236 chart_of_accounts_id = X_Chart_Of_Accounts_Id,
237 name = X_Name,
238 status = X_Status,
239 status_verified = X_Status_Verified,
240 actual_flag = X_Actual_Flag,
241 budgetary_control_status = X_Budgetary_Control_Status,
242 last_update_login = X_Last_Update_Login,
243 default_period_name = X_Default_Period_Name,
244 control_total = X_Control_Total,
245 running_total_dr = X_Running_Total_Dr,
246 running_total_cr = X_Running_Total_Cr,
247 posting_run_id = X_Posting_Run_Id,
248 request_id = X_Request_Id
249 WHERE rowid = X_rowid;
250
251 if (SQL%NOTFOUND) then
252 RAISE NO_DATA_FOUND;
253 end if;
254
255 -- Update the status of the consolidation batch to 'PS' in
256 -- gl_consolidation_history for Consolidation Workbench.
257 UPDATE GL_CONSOLIDATION_HISTORY
258 SET status = 'PS',
259 request_id = X_Request_Id
260 WHERE je_batch_id = X_Je_Batch_Id;
261
262 -- Update the status of the consolidation batch to 'PS' in
263 -- gl_elimination_history for Consolidation Workbench.
264 UPDATE GL_ELIMINATION_HISTORY
265 SET status_code = 'PS',
266 request_id = X_Request_Id
267 WHERE je_batch_id = X_Je_Batch_Id;
268
269 END Update_Row;
270
271
272
273 END GL_JE_BATCHES_POST_PKG;