[Home] [Help]
PACKAGE BODY: APPS.JA_CN_JRCFI_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_JRCFI_EXPORT_PKG AS
2 --$Header: JACNCFIB.pls 120.9 2011/01/14 06:57:10 choli noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNCFIB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export journals related to cash flow items |
13 --| |
14 --| PROCEDURE LIST |
15 --| PROCEDURE Add_Journal_Cash_Flow_Items |
16 --| |
17 --| |
18 --| HISTORY |
19 --| 01/20/2010 Jason Liu Created |
20 --| 01/05/2011 Jianchao Chi Updated for the new solution to change |
21 --| the logic of Voucher Number and Legal |
22 --| Entity |
23 --+======================================================================*/
24 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_JRCFI_EXPORT_PKG';
25
26 --==========================================================================
27 -- PROCEDURE NAME:
28 --
29 -- Add_Journal_Cash_Flow_Items Public
30 --
31 -- DESCRIPTION:
32 --
33 -- This procedure is to export journals related to cash flow items
34 --
35 -- PARAMETERS:
36 -- In: pn_legal_entity_id LEGAL_ENTITY_ID
37 -- pn_ledger_id Ledger ID
38 -- pv_accounting_year Accounting Year
39 -- pn_coa_id Chart of Accounts ID
40 -- pv_period_from Period From
41 -- pv_period_to Period To
42 -- pn_cfs_report_id cash flow statement report id
43 -- DESIGN REFERENCES:
44 -- CNAO_V2_GL_TD.doc
45 --
46 -- CHANGE HISTORY:
47 --
48 -- 20-JAN-2010 Jason Liu created
49 -- 04-Jan-2011 Jianchao Chi Updated for the new solution to change
50 -- the logic of Voucher Number and Legal Entity
51 --===========================================================================
52
53 PROCEDURE Add_Journal_Cash_Flow_Items(pn_legal_entity_id IN NUMBER,
54 pn_ledger_id IN NUMBER,
55 pv_accounting_year IN VARCHAR2,
56 pn_coa_id IN NUMBER,
57 pv_period_from IN VARCHAR2,
58 pv_period_to IN VARCHAR2,
59 pn_cfs_report_id NUMBER) IS
60 lv_procedure_name VARCHAR2(40) := 'Add_Journal_Cash_Flow_Items';
61 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
62 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
63 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
64 NO_DATA EXCEPTION;
65 ln_cash_flow_item_rule NUMBER;
66 ld_start_date DATE;
67 ld_end_date DATE;
68 ln_row_count NUMBER;
69
70 -- to get the cash flow item rule
71 CURSOR cash_flow_item_rule_cur(pn_cfs_report_id NUMBER) IS
72 SELECT length(MAX(rra.axis_seq))
73 FROM rg_reports rr, rg_report_axes rra
74 WHERE rr.report_id = pn_cfs_report_id
75 AND rr.ROW_SET_ID = rra.axis_set_id;
76
77 -- to get the cash flow items
78 CURSOR journal_cash_flow_items_cur(pn_cash_flow_item_rule NUMBER) IS
79 SELECT DISTINCT *
80 FROM (SELECT jcjl.je_category,
81 jcjl.je_header_id,
82 --jcjl.journal_number --comment by Jianchao Chi for voucher number change, on 04-JAN-2011, and add the following line
83 jcvn.voucher_number journal_number,-- Update from "jcjl.journal_number"
84 jcjl.je_line_num,
85 jccaa.original_curr_code,
86 jcjl.description,
87 lpad(jccasa.axis_seq, pn_cash_flow_item_rule, '0') axis_seq,
88 DECODE(jccasa1.item_attribute,
89 'Inflow',
90 '1',
91 'Outflow',
92 '0',
93 'Others',
94 '2',
95 '9') item_attribute,
96 jccaa.original_amount,
97 jccaa.func_amount,
98 gps.start_date
99 FROM ja_cn_cfs_activities_all jccaa,
100 ja_cn_journal_lines jcjl,
101 ja_cn_cfs_assignments_all jccasa,
102 ja_cn_cfs_assign_sup_all jccasa1,
103 rg_reports rr,
104 gl_period_statuses gps,
105 ja_cn_voucher_number jcvn
106 --Update by Jianchao Chi, for cnaov2 upgrade 04-JAN-2011
107 --Add ja_cn_voucher_number table, voucher number and legal entity
108 --are fetched from ja_cn_voucher_number
109 --The new one is
110 ----------------------------------------------------
111 --AND jcvn.je_header_id = jl.je_header_id
112 --AND jcvn.je_line_number = jl.je_line_num
113 ----------------------------------------------------
114 WHERE jccaa.je_header_id = jcjl.je_header_id
115 AND jccaa.je_line_num = jcjl.je_line_num
116 --Add the follow two conditions by Jianchao Chi to change the logic of legal entity, 05-JAN-2011
117 AND jcvn.je_header_id = jcjl.je_header_id
118 AND jcvn.je_line_number = jcjl.je_line_num
119 AND jccaa.legal_entity_id = jcvn.legal_entity_id
120 AND jccaa.ledger_id = jcjl.ledger_id
121 AND gps.ledger_id = pn_ledger_id
122 AND gps.application_id = 101
123 AND gps.period_name = jccaa.period_name
124 AND (ABS(jccaa.original_amount) = ABS(jcjl.entered_dr) OR
125 ABS(jccaa.original_amount) = ABS(jcjl.entered_cr))
126 AND ABS(jccaa.original_amount) > 0
127 AND jccaa.transaction_type IN ('SLA', 'AGIS')
128 AND jccaa.legal_entity_id = pn_legal_entity_id
129 AND jccasa.chart_of_accounts_id = pn_coa_id
130 AND jccasa.detailed_cfs_item = jccaa.detailed_cfs_item
131 AND rr.report_id = pn_cfs_report_id
132 AND jccasa.axis_set_id = rr.row_set_id
133 AND jccasa1.chart_of_accounts_id =
134 jccasa.chart_of_accounts_id
135 AND jccasa1.axis_set_id = jccasa.axis_set_id
136 AND jccasa1.axis_seq = jccasa.axis_seq
137 AND jccaa.balancing_segment IN
138 (SELECT bal_seg_value
139 FROM ja_cn_ledger_le_bsv_gt
140 WHERE legal_entity_id = pn_legal_entity_id
141 AND ledger_id = pn_ledger_id)
142 AND jccaa.period_name IN
143 (SELECT period_name
144 FROM gl_period_statuses
145 WHERE ledger_id = pn_ledger_id
146 AND application_id = 101
147 AND ((start_date BETWEEN ld_start_date AND
148 ld_end_date) AND
149 (end_date BETWEEN ld_start_date AND ld_end_date)))
150 AND jccaa.ledger_id = pn_ledger_id
151
152 UNION ALL
153
154 SELECT jcjl.je_category,
155 jcjl.je_header_id,
156 --jcjl.journal_number --comment by Jianchao Chi for voucher number change, on 04-JAN-2011, and add the following line
157 jcvn.voucher_number journal_number,-- Update from "jcjl.journal_number"
158 jcjl.je_line_num,
159 jccaa.original_curr_code,
160 jcjl.description,
161 lpad(jccasa.axis_seq, pn_cash_flow_item_rule, '0') axis_seq,
162 DECODE(jccasa1.item_attribute,
163 'Inflow',
164 '1',
165 'Outflow',
166 '0',
167 'Others',
168 '2',
169 '9') item_attribute,
170 jccaa.original_amount,
171 jccaa.func_amount,
172 gps.start_date
173 FROM ja_cn_cfs_activities_all jccaa,
174 ja_cn_journal_lines jcjl,
175 ja_cn_cfs_assignments_all jccasa,
176 ja_cn_cfs_assign_sup_all jccasa1,
177 rg_reports rr,
178 gl_period_statuses gps,
179 ja_cn_voucher_number jcvn
180 --Update by Jianchao Chi, for cnaov2 upgrade 04-JAN-2011
181 --Add ja_cn_voucher_number table, voucher number and legal entity
182 --are fetched from ja_cn_voucher_number
183 --The new one is
184 ----------------------------------------------------
185 --AND jcvn.je_header_id = jl.je_header_id
186 --AND jcvn.je_line_number = jl.je_line_num
187 ----------------------------------------------------
188 WHERE jccaa.trx_id = jcjl.je_header_id
189 AND jccaa.trx_line_id = jcjl.je_line_num
190 --Add the follow two conditions by Jianchao Chi to change the logic of legal entity, 05-JAN-2011
191 AND jcvn.je_header_id = jcjl.je_header_id
192 AND jcvn.je_line_number = jcjl.je_line_num
193 AND jccaa.legal_entity_id = jcvn.legal_entity_id
194 AND jccaa.ledger_id = jcjl.ledger_id
195 AND gps.ledger_id = pn_ledger_id
196 AND gps.application_id = 101
197 AND gps.period_name = jccaa.period_name
198 AND (ABS(jccaa.original_amount) = ABS(jcjl.entered_dr) OR
199 ABS(jccaa.original_amount) = ABS(jcjl.entered_cr))
200 AND ABS(jccaa.original_amount) > 0
201 AND jccaa.transaction_type = 'JOURNAL'
202 AND jccaa.legal_entity_id = pn_legal_entity_id
203 AND jccasa.chart_of_accounts_id = pn_coa_id
204 AND jccasa.detailed_cfs_item = jccaa.detailed_cfs_item
205 AND rr.report_id = pn_cfs_report_id
206 AND jccasa.axis_set_id = rr.row_set_id
207 AND jccasa1.chart_of_accounts_id =
208 jccasa.chart_of_accounts_id
209 AND jccasa1.axis_set_id = jccasa.axis_set_id
210 AND jccasa1.axis_seq = jccasa.axis_seq
211 AND jccaa.balancing_segment IN
212 (SELECT bal_seg_value
213 FROM ja_cn_ledger_le_bsv_gt
214 WHERE legal_entity_id = pn_legal_entity_id
215 AND ledger_id = pn_ledger_id)
216 AND jccaa.period_name IN
217 (SELECT period_name
218 FROM gl_period_statuses
219 WHERE ledger_id = pn_ledger_id
220 AND application_id = 101
221 AND ((start_date BETWEEN ld_start_date AND
222 ld_end_date) AND
223 (end_date BETWEEN ld_start_date AND ld_end_date)))
224 AND jccaa.ledger_id = pn_ledger_id)
225 ORDER BY 11, 3, 4;
226
227 BEGIN
228 --logging for debug
229 IF (ln_proc_level >= ln_dbg_level) THEN
230 FND_LOG.STRING(ln_proc_level,
231 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
232 '.begin',
233 'Enter procedure');
234 -- logging the parameters
235 FND_LOG.STRING(ln_proc_level,
236 lv_procedure_name || '.parameters',
237 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
238 'pn_ledger_id=' || pn_ledger_id || ',' ||
239 'pv_accounting_year=' || pv_accounting_year || ',' ||
240 'pn_coa_id=' || pn_coa_id || ',' || 'pv_period_from=' ||
241 pv_period_from || ',' || 'pv_period_to=' ||
242 pv_period_to || ',' || 'pn_cfs_report_id=' ||
243 pn_cfs_report_id);
244 END IF; --l_proc_level>=l_dbg_level
245
246 FND_FILE.put_line(FND_FILE.log,
247 lv_procedure_name || '.parameters:' ||
248 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
249 'pn_ledger_id=' || pn_ledger_id || ',' ||
250 'pv_accounting_year=' || pv_accounting_year || ',' ||
251 'pn_coa_id=' || pn_coa_id || ',' || 'pv_period_from=' ||
252 pv_period_from || ',' || 'pv_period_to=' ||
253 pv_period_to || ',' || 'pn_cfs_report_id=' ||
254 pn_cfs_report_id);
255
256 -- retrive the cash flow item rule
257 OPEN cash_flow_item_rule_cur(pn_cfs_report_id);
258 FETCH cash_flow_item_rule_cur
259 INTO ln_cash_flow_item_rule;
260 CLOSE cash_flow_item_rule_cur;
261
262 --logging the variables
263 IF (ln_proc_level >= ln_dbg_level) THEN
264 FND_LOG.STRING(ln_proc_level,
265 lv_procedure_name,
266 'The cash flow item rule is:' ||
267 ln_cash_flow_item_rule);
268 END IF; --(ln_proc_level >= ln_dbg_level)
272 SELECT start_date
269
270 --Fetch start date and end date
271 IF pv_period_from IS NOT NULL THEN
273 INTO ld_start_date
274 FROM GL_PERIOD_STATUSES
275 WHERE ledger_id = pn_ledger_id
276 AND application_id = 101
277 AND period_name = pv_period_from
278 AND to_char(period_year) = pv_accounting_year;
279 ELSE
280 ld_start_date := to_date(pv_accounting_year || '0101', 'YYYYMMDD');
281 END IF; --pv_period_from IS NOT NULL
282
283 IF pv_period_to IS NOT NULL THEN
284 SELECT end_date
285 INTO ld_end_date
286 FROM GL_PERIOD_STATUSES
287 WHERE ledger_id = pn_ledger_id
288 AND application_id = 101
289 AND period_name = pv_period_to
290 AND to_char(period_year) = pv_accounting_year;
291 ELSE
292 ld_end_date := to_date(pv_accounting_year || '1231', 'YYYYMMDD');
293 END IF; --pv_period_to IS NOT NULL
294
295 --logging the variables
296 IF (ln_statement_level >= ln_dbg_level) THEN
297 FND_LOG.STRING(ln_statement_level,
298 lv_procedure_name,
299 'ld_start_date' || ld_start_date || ',' ||
300 'ld_end_date=' || ld_end_date);
301 END IF; --(ln_statement_level >= ln_dbg_level)
302
303 FND_FILE.put_line(FND_FILE.log,
304 lv_procedure_name || '.variables:' ||
305 'ld_start_date=' || ld_start_date || ',' ||
306 'ld_end_date=' || ld_end_date);
307
308 ln_row_count := 0;
309 -- add the XML tags
310
311 FOR v_row IN journal_cash_flow_items_cur(ln_cash_flow_item_rule) LOOP
312 ln_row_count := ln_row_count + 1;
313 Ja_Cn_Utility.Add_Sub_Root_Node('CASH_FLOW_JOURNAL_ITEM_DATA',
314 Ja_Cn_Utility.GV_TAG_TYPE_START);
315
316 Ja_Cn_Utility.Add_Child_Node('JOURNAL_CATEGORY_NUMBER',
317 v_row.je_category);
318 Ja_Cn_Utility.Add_Child_Node('JOURNAL_NUMBER', v_row.journal_number);
319 Ja_Cn_Utility.Add_Child_Node('CURRENCY_CODE',
320 v_row.original_curr_code);
321 Ja_Cn_Utility.Add_Child_Node('CASH_FLOW_LINE_NUMBER', ln_row_count);
322 Ja_Cn_Utility.Add_Child_Node('CASH_FLOW_DESCRIPTION',
323 v_row.description);
324 Ja_Cn_Utility.Add_Child_Node('CASH_FLOW_ITEM_NUMBER', v_row.axis_seq);
325 Ja_Cn_Utility.Add_Fixed_Child_Node('CASH_FLOW_ITEM_ATTRIBUTE',
326 v_row.item_attribute,
327 1);
328 Ja_Cn_Utility.Add_Child_Node('CASH_FLOW_ENTERED_AMOUNT',
329 v_row.original_amount,
330 Ja_Cn_Utility.GV_TYPE_NUMBER);
331 Ja_Cn_Utility.Add_Child_Node('CASH_FLOW_FUNCTIONAL_AMOUNT',
332 v_row.func_amount,
333 Ja_Cn_Utility.GV_TYPE_NUMBER);
334
335 Ja_Cn_Utility.Add_Sub_Root_Node('CASH_FLOW_JOURNAL_ITEM_DATA',
336 Ja_Cn_Utility.GV_TAG_TYPE_END);
337 END LOOP; --v_row IN journal_cash_flow_items_cur(ln_cash_flow_item_rule)
338
339 -- if the row count is 0
340 -- no need to handle if minOccurs=0 specified in xml schema
341 IF (ln_row_count = 0) THEN
342 Ja_Cn_Utility.Print_No_Data_Found_For_Log('CASH_FLOW_JOURNAL_ITEM_DATA');
343
344 Ja_Cn_Utility.Add_Sub_Root_Node('CASH_FLOW_JOURNAL_ITEM_DATA',
345 Ja_Cn_Utility.GV_TAG_TYPE_START);
346
347 Ja_Cn_Utility.Add_Child_Node('JOURNAL_CATEGORY_NUMBER', NULL);
348 Ja_Cn_Utility.Add_Child_Node('JOURNAL_NUMBER', NULL);
349 Ja_Cn_Utility.Add_Child_Node('CURRENCY_CODE', NULL);
350 Ja_Cn_Utility.Add_Child_Node('CASH_FLOW_LINE_NUMBER', NULL);
351 Ja_Cn_Utility.Add_Child_Node('CASH_FLOW_DESCRIPTION', NULL);
352 Ja_Cn_Utility.Add_Child_Node('CASH_FLOW_ITEM_NUMBER', NULL);
353 Ja_Cn_Utility.Add_Fixed_Child_Node('CASH_FLOW_ITEM_ATTRIBUTE',
354 NULL,
355 1);
356 Ja_Cn_Utility.Add_Child_Node('CASH_FLOW_ENTERED_AMOUNT',
357 NULL,
358 Ja_Cn_Utility.GV_TYPE_NUMBER);
359 Ja_Cn_Utility.Add_Child_Node('CASH_FLOW_FUNCTIONAL_AMOUNT',
360 NULL,
361 Ja_Cn_Utility.GV_TYPE_NUMBER);
362
363 Ja_Cn_Utility.Add_Sub_Root_Node('CASH_FLOW_JOURNAL_ITEM_DATA',
364 Ja_Cn_Utility.GV_TAG_TYPE_END);
365 END IF; --(ln_row_count = 0)
366
367 --logging for debug
368 IF (ln_proc_level >= ln_dbg_level) THEN
369 FND_LOG.STRING(ln_proc_level,
370 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
371 'Exit procedure');
372 END IF; -- (ln_proc_level>=ln_dbg_level)
373
374 EXCEPTION
375 WHEN OTHERS THEN
376 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
377 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
378 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
379 '.Other_Exception ',
380 SQLCODE || SQLERRM);
381 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
382 FND_FILE.put_line(FND_FILE.log,
383 lv_procedure_name || SQLCODE || SQLERRM);
384 RAISE;
385 END Add_Journal_Cash_Flow_Items;
386
387 END JA_CN_JRCFI_EXPORT_PKG;
388