[Home] [Help]
PACKAGE BODY: APPS.GL_JOURNAL_IMPORT_SLA_PKG
Source
1 PACKAGE BODY GL_JOURNAL_IMPORT_SLA_PKG as
2 /* $Header: glujislb.pls 120.6 2006/08/24 00:18:19 kvora ship $ */
3
4 PROCEDURE delete_batches(x_je_source_name VARCHAR2,
5 x_group_id NUMBER) IS
6 BEGIN
7
8 UPDATE GL_BC_PACKETS
9 SET je_batch_id = -1, group_id = NULL
10 WHERE group_id = x_group_id;
11
12 DELETE gl_import_references
13 WHERE je_header_id IN
14 (SELECT jeh.je_header_id
15 FROM gl_je_batches jeb,
16 gl_je_headers jeh
17 WHERE jeb.status = 'U'
18 AND jeb.group_id = x_group_id
19 AND jeh.je_batch_id = jeb.je_batch_id
20 AND jeh.ledger_id < 0
21 AND jeh.je_source = x_je_source_name);
22
23 DELETE gl_je_lines
24 WHERE je_header_id IN
25 (SELECT jeh.je_header_id
26 FROM gl_je_batches jeb,
27 gl_je_headers jeh
28 WHERE jeb.status = 'U'
29 AND jeb.group_id = x_group_id
30 AND jeh.je_batch_id = jeb.je_batch_id
31 AND jeh.ledger_id < 0
32 AND jeh.je_source = x_je_source_name);
33
34 DELETE gl_je_headers jeh
35 WHERE jeh.ledger_id < 0
36 AND jeh.je_source = x_je_source_name
37 AND jeh.je_batch_id IN
38 (SELECT jeb.je_batch_id
39 FROM gl_je_batches jeb
40 WHERE jeb.status = 'U'
41 AND jeb.group_id = x_group_id);
42
43 DELETE gl_je_batches jeb
44 WHERE jeb.status = 'U'
45 AND jeb.group_id = x_group_id
46 AND NOT EXISTS
47 (SELECT 'has journals'
48 FROM gl_je_headers jeh
49 WHERE jeh.je_batch_id = jeb.je_batch_id);
50 END delete_batches;
51
52 PROCEDURE keep_batches(x_je_source_name VARCHAR2,
53 x_group_id NUMBER,
54 start_posting BOOLEAN,
55 data_access_set_id NUMBER,
56 req_id OUT NOCOPY NUMBER) IS
57 prun_id NUMBER;
58 coa_id NUMBER;
59 single_led_id NUMBER;
60 dummy NUMBER;
61 found_batch BOOLEAN;
62
63 CURSOR single_ledger (c_prun_id NUMBER) IS
64 SELECT max(abs(JEH.ledger_id))
65 FROM GL_JE_BATCHES JEB,
66 GL_JE_HEADERS JEH
67 WHERE JEB.status = 'S'
68 AND JEB.posting_run_id = c_prun_id
69 AND JEH.je_batch_id = JEB.je_batch_id
70 GROUP BY JEB.posting_run_id
71 HAVING count(distinct abs(JEH.ledger_id)) = 1;
72
73 CURSOR alc_exists (c_prun_id NUMBER) IS
74 SELECT '1'
75 FROM GL_JE_BATCHES JEB,
76 GL_JE_HEADERS JEH
77 WHERE JEB.status = 'S'
78 AND JEB.posting_run_id = c_prun_id
79 AND JEH.je_batch_id = JEB.je_batch_id
80 AND JEH.actual_flag <> 'B'
81 AND JEH.reversed_je_header_id IS NULL
82 AND EXISTS
83 (SELECT 1
84 FROM GL_LEDGER_RELATIONSHIPS LRL
85 WHERE LRL.source_ledger_id = abs(JEH.ledger_id)
86 AND LRL.target_ledger_category_code = 'ALC'
87 AND LRL.relationship_type_code IN ('JOURNAL', 'SUBLEDGER')
88 AND LRL.application_id = 101
89 AND LRL.relationship_enabled_flag = 'Y'
90 AND JEH.je_source NOT IN
91 (SELECT INC.je_source_name
92 FROM GL_JE_INCLUSION_RULES INC
93 WHERE INC.je_rule_set_id =
94 LRL.gl_je_conversion_set_id
95 AND INC.je_source_name = JEH.je_source
96 AND INC.je_category_name = 'Other'
97 AND INC.include_flag = 'N'
98 AND INC.user_updatable_flag = 'N'));
99
100 BEGIN
101
102 -- If so requested, start posting if there are valid
103 -- batches to post.
104 req_id := 0;
105 prun_id := 0;
106 found_batch := FALSE;
107 IF (start_posting) THEN
108 SELECT GL_JE_POSTING_S.nextval
109 INTO prun_id
110 FROM dual;
111
112 UPDATE gl_je_batches jeb
113 SET status = 'S',
114 posting_run_id = prun_id
115 WHERE jeb.status = 'U'
116 AND jeb.group_id = x_group_id
117 AND jeb.approval_status_code = 'Z'
118 AND NOT EXISTS
119 (SELECT 'not open period'
120 FROM gl_je_headers jeh,
121 gl_ledgers lgr,
122 gl_period_statuses ps
123 WHERE jeh.je_batch_id = jeb.je_batch_id
124 AND lgr.ledger_id = - jeh.ledger_id
125 AND ps.application_id = 101
126 AND ps.ledger_id = - jeh.ledger_id
127 AND ps.period_name = jeh.period_name
128 AND ( ( (jeh.actual_flag = 'A')
129 AND (ps.closing_status <> 'O'))
130 OR ( (jeh.actual_flag = 'E')
131 AND (least(nvl(lgr.latest_encumbrance_year,0),
132 ps.period_year) <> ps.period_year))
133 OR ( (jeh.actual_flag = 'B')
134 AND (NOT EXISTS
135 (SELECT 'open year'
136 FROM gl_budget_period_ranges pr
137 WHERE pr.budget_version_id
138 = jeh.budget_version_id
139 AND pr.period_year = ps.period_year
140 AND ps.period_num
141 between pr.start_period_num
142 and pr.end_period_num)))))
143 AND EXISTS
144 (SELECT 'has negative journals'
145 FROM gl_je_headers jeh
146 WHERE jeh.je_batch_id = jeb.je_batch_id
147 AND jeh.ledger_id < 0
148 AND jeh.je_source = x_je_source_name);
149
150 IF (SQL%FOUND) THEN
151 found_batch := TRUE;
152 END IF;
153
154 -- Start posting if one or more batches found
155 IF (found_batch) THEN
156
157 SELECT chart_of_accounts_id
158 INTO coa_id
159 FROM gl_je_batches jeb
160 WHERE jeb.status = 'S'
161 AND jeb.posting_run_id = prun_id
162 AND rownum = 1;
163
164 -- Set single_ledger_id to the journal ledger id if the batch
165 -- has journals only for a single ledger which has no enabled
166 -- journal or subledger RCs.
167 OPEN single_ledger(prun_id);
168 FETCH single_ledger INTO single_led_id;
169 IF single_ledger%NOTFOUND THEN
170 single_led_id := -99;
171 ELSE
172 OPEN alc_exists(prun_id);
173 FETCH alc_exists INTO dummy;
174 IF alc_exists%FOUND THEN
175 single_led_id := -99;
176 END IF;
177 CLOSE alc_exists;
178 END IF;
179 CLOSE single_ledger;
180
181 -- Submit Posting...
182 IF (single_led_id = -99) THEN
183 req_id := fnd_request.submit_request(
184 'SQLGL',
185 'GLPPOS',
186 '',
187 '',
188 FALSE,
189 to_char(single_led_id),
190 to_char(data_access_set_id),
191 to_char(coa_id),
192 to_char(prun_id),
193 chr(0),
194 '', '', '', '', '', '', '', '', '', '',
195 '', '', '', '', '', '', '', '', '', '',
196 '', '', '', '', '', '', '', '', '', '',
197 '', '', '', '', '', '', '', '', '', '',
198 '', '', '', '', '', '', '', '', '', '',
199 '', '', '', '', '', '', '', '', '', '',
200 '', '', '', '', '', '', '', '', '', '',
201 '', '', '', '', '', '', '', '', '', '',
202 '', '', '', '', '', '', '', '', '', '',
203 '', '', '', '', '');
204 ELSE
205 req_id := fnd_request.submit_request(
206 'SQLGL',
207 'GLPPOSS',
208 '',
209 '',
210 FALSE,
211 to_char(single_led_id),
212 to_char(data_access_set_id),
213 to_char(coa_id),
214 to_char(prun_id),
215 chr(0),
216 '', '', '', '', '', '', '', '', '', '',
217 '', '', '', '', '', '', '', '', '', '',
218 '', '', '', '', '', '', '', '', '', '',
219 '', '', '', '', '', '', '', '', '', '',
220 '', '', '', '', '', '', '', '', '', '',
221 '', '', '', '', '', '', '', '', '', '',
222 '', '', '', '', '', '', '', '', '', '',
223 '', '', '', '', '', '', '', '', '', '',
224 '', '', '', '', '', '', '', '', '', '',
225 '', '', '', '', '');
226 END IF;
227
228 IF (req_id <> 0) THEN
229 UPDATE gl_je_batches jeb
230 SET request_id = req_id
231 WHERE jeb.status = 'S'
232 AND EXISTS
233 (SELECT 'has negative journals'
234 FROM gl_je_headers jeh
235 WHERE jeh.je_batch_id = jeb.je_batch_id
236 AND jeh.ledger_id < 0
237 AND jeh.je_source = x_je_source_name);
238 END IF;
239 END IF;
240 END IF;
241
242 -- Make the negative ledger ids positive
243 UPDATE /*+ INDEX(jel GL_JE_LINES_U1) */
244 gl_je_lines jel
245 SET jel.ledger_id = -jel.ledger_id
246 WHERE jel.je_header_id IN
247 (SELECT /*+ ORDERED
248 INDEX(jeb GL_JE_BATCHES_N1)
249 INDEX(jeh GL_JE_HEADERS_N1)
250 */
251 jeh.je_header_id
252 FROM gl_je_batches jeb,
253 gl_je_headers jeh
254 WHERE jeb.status in ('U','S')
255 AND nvl(jeb.posting_run_id, prun_id) = prun_id
256 AND jeb.group_id = x_group_id
257 AND jeh.je_batch_id = jeb.je_batch_id
258 AND jeh.ledger_id < 0
259 AND jeh.je_source = x_je_source_name);
260
261 -- Make the negative ledger ids positive
262 UPDATE gl_je_headers jeh
263 SET jeh.ledger_id = -jeh.ledger_id
264 WHERE jeh.ledger_id < 0
265 AND jeh.je_source = x_je_source_name
266 AND jeh.je_batch_id IN
267 (SELECT jeb.je_batch_id
268 FROM gl_je_batches jeb
269 WHERE jeb.status in ('U','S')
270 AND nvl(jeb.posting_run_id, prun_id) = prun_id
271 AND jeb.group_id = x_group_id);
272
273 END keep_batches;
274
275 END GL_JOURNAL_IMPORT_SLA_PKG;