[Home] [Help]
PACKAGE BODY: APPS.CST_UPD_GIR_MTA_WTA
Source
1 PACKAGE BODY cst_upd_gir_mta_wta AS
2 /* $Header: CSTGIRMWB.pls 120.1.12010000.1 2008/10/28 18:53:03 hyu noship $ */
3
4 -- Local procedures and variables
5
6 PG_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
7
8 PROCEDURE debug
9 ( line IN VARCHAR2,
10 msg_prefix IN VARCHAR2 DEFAULT 'CST',
11 msg_module IN VARCHAR2 DEFAULT 'cst_upd_gir_mta_wta',
12 msg_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT);
13
14 --
15
16 PROCEDURE cst_sl_link_upg_mta (p_je_batch_id IN NUMBER) AS
17
18 CURSOR cu_gir(p_je_batch_id IN NUMBER) IS
19 SELECT jle.code_combination_id,
20 gir.rowid,
21 gir.reference_1,
22 xla_gl_sl_link_id_s.nextval
23 FROM gl_je_headers gh,
24 gl_import_references gir,
25 gl_je_lines jle
26 WHERE gh.je_batch_id = p_je_batch_id
27 AND gh.je_header_id = jle.je_header_id
28 AND gir.je_header_id = jle.je_header_id
29 AND gir.je_line_num = jle.je_line_num
30 AND gir.gl_sl_link_table = 'MTA'
31 AND gir.gl_sl_link_id IS NULL;
32
33 ccid_tab DBMS_SQL.NUMBER_TABLE;
34 gir_rowid_tab DBMS_SQL.VARCHAR2_TABLE;
35 gl_batch_id_tab DBMS_SQL.NUMBER_TABLE;
36 gl_sl_link_id_tab DBMS_SQL.NUMBER_TABLE;
37 l_inv_sl_id_tab DBMS_SQL.NUMBER_TABLE;
38
39
40 CURSOR cu_xla_line IS
41 SELECT l.rowid,
42 m.gl_sl_link_id
43 FROM mtl_transaction_accounts m,
44 cst_lists_temp c,
45 xla_distribution_links lk,
46 xla_ae_lines l
47 WHERE lk.application_id = 707
48 AND lk.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
49 AND lk.source_distribution_id_num_1 = c.number_1
50 AND lk.application_id = 707
51 AND lk.ae_header_id = l.ae_header_id
52 AND lk.ae_line_num = l.ae_line_num
53 AND l.application_id = 707
54 AND m.transaction_id = c.number_2
55 AND m.inv_sub_ledger_id = c.number_1
56 AND m.reference_account = c.number_3;
57
58 xline_rowid_tab DBMS_SQL.VARCHAR2_TABLE;
59 xl_link_id_tab DBMS_SQL.NUMBER_TABLE;
60
61 l_last_fetch BOOLEAN := FALSE;
62 bulk_size NUMBER := 10000;
63 l_sql NUMBER := 0;
64
65 CURSOR cv IS
66 SELECT NULL
67 FROM cst_lists_temp
68 WHERE number_1 IS NULL
69 AND list_id = 999;
70
71 l_check VARCHAR2(1);
72 BEGIN
73
74 debug('cst_sl_link_upg_mta +');
75 debug(' p_je_batch_id :'||p_je_batch_id);
76
77
78 OPEN cu_gir(p_je_batch_id);
79 LOOP
80 FETCH cu_gir BULK COLLECT INTO
81 ccid_tab ,
82 gir_rowid_tab ,
83 gl_batch_id_tab ,
84 gl_sl_link_id_tab LIMIT bulk_size;
85
86 IF cu_gir%NOTFOUND THEN
87 l_last_fetch := TRUE;
88 END IF;
89
90 IF (gir_rowid_tab.COUNT = 0) AND (l_last_fetch) THEN
91 EXIT;
92 END IF;
93
94 debug(' Update GIR.GL_SL_LINK_ID for MTA');
95
96 FORALL i IN gir_rowid_tab.FIRST .. gir_rowid_tab.LAST
97 UPDATE gl_import_references
98 SET gl_sl_link_id = gl_sl_link_id_tab(i)
99 WHERE rowid = gir_rowid_tab(i);
100
101 l_sql := SQL%ROWCOUNT;
102
103 IF l_sql <> 0 THEN
104
105 debug(' Update MTA.GL_SL_LINK_ID for MTA');
106
107 FORALL i IN gir_rowid_tab.FIRST .. gir_rowid_tab.LAST
108 UPDATE mtl_transaction_accounts
109 SET gl_sl_link_id = gl_sl_link_id_tab(i)
110 WHERE gl_batch_id = gl_batch_id_tab(i)
111 AND reference_account = ccid_tab(i);
112
113 debug(' Insert Cst_Lists_Temp for MTA');
114
115 FORALL i IN gir_rowid_tab.FIRST .. gir_rowid_tab.LAST
116 INSERT INTO cst_lists_temp
117 (list_id ,
118 number_1,
119 number_2,
120 number_3,
121 varchar_1)
122 SELECT 999,
123 inv_sub_ledger_id,
124 transaction_id,
125 reference_account,
126 NULL
127 FROM mtl_transaction_accounts
128 WHERE gl_batch_id = gl_batch_id_tab(i)
129 AND reference_account = ccid_tab(i);
130 END IF;
131
132 OPEN cv;
133 FETCH cv INTO l_check;
134 IF cv%NOTFOUND THEN
135
136 debug(' Update XAL.GL_SL_LINK_ID for MTA');
137
138 OPEN cu_xla_line;
139 FETCH cu_xla_line BULK COLLECT INTO
140 xline_rowid_tab,
141 xl_link_id_tab ;
142 FORALL i IN xline_rowid_tab.FIRST .. xline_rowid_tab.LAST
143 UPDATE xla_ae_lines
144 SET gl_sl_link_id = xl_link_id_tab(i)
145 WHERE rowid = xline_rowid_tab(i);
146 CLOSE cu_xla_line;
147 END IF;
148 CLOSE cv;
149 DELETE FROM cst_lists_temp;
150
151
152 END LOOP;
153 CLOSE cu_gir;
154
155 debug('cst_sl_link_upg_mta -');
156
157 EXCEPTION
158 WHEN OTHERS THEN
159 IF cu_gir%ISOPEN THEN CLOSE cu_gir; END IF;
160 IF cu_xla_line%ISOPEN THEN CLOSE cu_xla_line; END IF;
161 IF cv%ISOPEN THEN CLOSE cv; END IF;
162 debug('EXCEPTION OTHERS cst_sl_link_upg_mta :'||SQLERRM);
163 RAISE;
164 END cst_sl_link_upg_mta;
165
166
167
168
169
170 PROCEDURE cst_sl_link_upg_wta (p_je_batch_id IN NUMBER) AS
171
172 CURSOR cu_gir(p_je_batch_id IN NUMBER) IS
173 SELECT jle.code_combination_id,
174 gir.rowid,
175 gir.reference_1,
176 xla_gl_sl_link_id_s.nextval
177 FROM gl_je_headers gh,
178 gl_import_references gir,
179 gl_je_lines jle
180 WHERE gh.je_batch_id = p_je_batch_id
181 AND gh.je_header_id = jle.je_header_id
182 AND gir.je_header_id = jle.je_header_id
183 AND gir.je_line_num = jle.je_line_num
184 AND gir.gl_sl_link_table = 'WTA'
185 AND gir.gl_sl_link_id IS NULL;
186
187 CURSOR cu_xla_line IS
188 SELECT l.rowid,
189 m.gl_sl_link_id
190 FROM wip_transaction_accounts m,
191 cst_lists_temp c,
192 xla_distribution_links lk,
193 xla_ae_lines l
194 WHERE lk.application_id = 707
195 AND lk.source_distribution_type = 'WIP_TRANSACTION_ACCOUNTS'
196 AND lk.source_distribution_id_num_1 = c.number_1
197 AND lk.application_id = 707
198 AND lk.ae_header_id = l.ae_header_id
199 AND lk.ae_line_num = l.ae_line_num
200 AND l.application_id = 707
201 AND m.transaction_id = c.number_2
202 AND m.wip_sub_ledger_id = c.number_1
203 AND m.reference_account = c.number_3;
204
205 CURSOR cv IS
206 SELECT NULL
207 FROM cst_lists_temp
208 WHERE number_1 IS NULL
209 AND list_id = 999;
210
211 xline_rowid_tab DBMS_SQL.VARCHAR2_TABLE;
212 xl_link_id_tab DBMS_SQL.NUMBER_TABLE;
213 ccid_tab DBMS_SQL.NUMBER_TABLE;
214 gir_rowid_tab DBMS_SQL.VARCHAR2_TABLE;
215 gl_batch_id_tab DBMS_SQL.NUMBER_TABLE;
216 gl_sl_link_id_tab DBMS_SQL.NUMBER_TABLE;
217 l_inv_sl_id_tab DBMS_SQL.NUMBER_TABLE;
218 l_last_fetch BOOLEAN := FALSE;
219 bulk_size NUMBER := 10000;
220 l_sql NUMBER := 0;
221 l_check VARCHAR2(1);
222
223 BEGIN
224 debug('cst_sl_link_upg_wta +');
225
226 debug(' p_je_batch_id :'||p_je_batch_id);
227
228 OPEN cu_gir(p_je_batch_id);
229 LOOP
230 FETCH cu_gir BULK COLLECT INTO
231 ccid_tab ,
232 gir_rowid_tab ,
233 gl_batch_id_tab ,
234 gl_sl_link_id_tab LIMIT bulk_size;
235
236 IF cu_gir%NOTFOUND THEN
237 l_last_fetch := TRUE;
238 END IF;
239
240 IF (gir_rowid_tab.COUNT = 0) AND (l_last_fetch) THEN
241 EXIT;
242 END IF;
243
244 debug(' Update GIR.GL_SL_LINK_ID for WTA');
245
246 FORALL i IN gir_rowid_tab.FIRST .. gir_rowid_tab.LAST
247 UPDATE gl_import_references
248 SET gl_sl_link_id = gl_sl_link_id_tab(i)
249 WHERE rowid = gir_rowid_tab(i);
250
251 l_sql := SQL%ROWCOUNT;
252
253 IF l_sql <> 0 THEN
254
255 debug(' Update WTA.GL_SL_LINK_ID for WTA');
256
257 FORALL i IN gir_rowid_tab.FIRST .. gir_rowid_tab.LAST
258 UPDATE wip_transaction_accounts
259 SET gl_sl_link_id = gl_sl_link_id_tab(i)
260 WHERE gl_batch_id = gl_batch_id_tab(i)
261 AND reference_account = ccid_tab(i);
262
263 debug(' Insert Cst_Lists_Temp for WTA');
264
265 FORALL i IN gir_rowid_tab.FIRST .. gir_rowid_tab.LAST
266 INSERT INTO cst_lists_temp
267 (list_id ,
268 number_1,
269 number_2,
270 number_3,
271 varchar_1)
272 SELECT 999,
273 wip_sub_ledger_id,
274 transaction_id,
275 reference_account,
276 NULL
277 FROM wip_transaction_accounts
278 WHERE gl_batch_id = gl_batch_id_tab(i)
279 AND reference_account = ccid_tab(i);
280 END IF;
281
282 OPEN cv;
283 FETCH cv INTO l_check;
284 IF cv%NOTFOUND THEN
285
286 debug(' Insert XAL.GL_SL_LINK_ID for WTA');
287
288 OPEN cu_xla_line;
289 FETCH cu_xla_line BULK COLLECT INTO
290 xline_rowid_tab,
291 xl_link_id_tab ;
292 FORALL i IN xline_rowid_tab.FIRST .. xline_rowid_tab.LAST
293 UPDATE xla_ae_lines
294 SET gl_sl_link_id = xl_link_id_tab(i)
295 WHERE rowid = xline_rowid_tab(i);
296 CLOSE cu_xla_line;
297 END IF;
298 CLOSE cv;
299 DELETE FROM cst_lists_temp;
300
301
302 END LOOP;
303 CLOSE cu_gir;
304
305 debug('cst_sl_link_upg_wta -');
306 EXCEPTION
307 WHEN OTHERS THEN
308 IF cu_gir%ISOPEN THEN CLOSE cu_gir; END IF;
309 IF cu_xla_line%ISOPEN THEN CLOSE cu_xla_line; END IF;
310 IF cv%ISOPEN THEN CLOSE cv; END IF;
311 debug('EXCEPTION OTHERS cst_sl_link_upg_wta :'||SQLERRM);
312 RAISE;
313 END cst_sl_link_upg_wta;
314
315
316
317
318 PROCEDURE update_mta_wta
319 (errbuf OUT NOCOPY VARCHAR2,
320 retcode OUT NOCOPY NUMBER,
321 p_from_date IN VARCHAR2,
322 p_to_date IN VARCHAR2,
323 p_ledger_id IN NUMBER)
324 IS
325 CURSOR c_glb(l_from_date IN DATE, l_to_date IN DATE)
326 IS
327 select a.je_batch_id
328 from gl_je_batches a,
329 gl_period_statuses b
330 where a.set_of_books_id_11i = b.set_of_books_id
331 and b.set_of_books_id = p_ledger_id
332 and a.default_effective_date >= l_from_date
333 and a.default_effective_date <= l_to_date
334 and b.migration_status_code = 'U'
335 and b.application_id = 401
336 and a.name like '%Inventory%';
337 l_je_batch_id NUMBER;
338 l_from_date DATE;
339 l_to_date DATE;
340 BEGIN
341 debug('update_mta_wta +');
342 debug(' p_from_date :'||p_from_date);
343 debug(' p_to_date :'||p_to_date);
344 debug(' p_ledger_id :'||p_ledger_id);
345
346
347 -- l_from_date := to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
348 -- l_to_date := to_date(p_to_date,'YYYY/MM/DD HH24:MI:SS');
349
350 l_from_date := to_date(p_from_date,'YYYY/MM/DD');
351 l_to_date := to_date(p_to_date,'YYYY/MM/DD');
352
353
354 OPEN c_glb(l_from_date, l_to_date);
355 LOOP
356 FETCH c_glb INTO l_je_batch_id;
357 debug(' l_je_batch_id :'||l_je_batch_id);
358 EXIT WHEN c_glb%NOTFOUND;
359 cst_sl_link_upg_mta (p_je_batch_id => l_je_batch_id);
360 cst_sl_link_upg_wta (p_je_batch_id => l_je_batch_id);
361 COMMIT;
362 END LOOP;
363 CLOSE c_glb;
364
365 debug('update_mta_wta -');
366
367 EXCEPTION
368 WHEN OTHERS THEN
369 IF c_glb%ISOPEN THEN CLOSE c_glb; END IF;
370 debug('EXCEPTION OTHERS update_mta_wta :'||SQLERRM);
371 RAISE;
372 END update_mta_wta;
373
374
375 PROCEDURE debug
376 ( line IN VARCHAR2,
377 msg_prefix IN VARCHAR2 DEFAULT 'CST',
378 msg_module IN VARCHAR2 DEFAULT 'cst_upd_gir_mta_wta',
379 msg_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT)
380 IS
381 l_msg_prefix VARCHAR2(64);
382 l_msg_level NUMBER;
383 l_msg_module VARCHAR2(256);
384 l_beg_end_suffix VARCHAR2(15);
385 l_org_cnt NUMBER;
386 l_line VARCHAR2(32767);
387 BEGIN
388
389 l_line := line;
390 l_msg_prefix := msg_prefix;
391 l_msg_level := msg_level;
392 l_msg_module := msg_module;
393
394 IF (INSTRB(upper(l_line), 'EXCEPTION') <> 0) THEN
395 l_msg_level := FND_LOG.LEVEL_EXCEPTION;
396 END IF;
397
398 IF l_msg_level <> FND_LOG.LEVEL_EXCEPTION AND PG_DEBUG = 'N' THEN
399 RETURN;
400 END IF;
401
402 IF ( l_msg_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
403 FND_LOG.STRING(l_msg_level, l_msg_module, SUBSTRB(l_line,1,4000));
404 END IF;
405
406 EXCEPTION
407 WHEN OTHERS THEN RAISE;
408 END debug;
409
410
411 END;