[Home] [Help]
PACKAGE BODY: APPS.JA_CN_CFI_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_CFI_EXPORT_PKG AS
2 --$Header: JACNCFEB.pls 120.4 2010/06/04 05:05:44 wuwu noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNCFEB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| To export Cash Flow Item related information which defined |
13 --| in the system. |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE Add_Cash_Flow_Items |
17 --| |
18 --| |
19 --| HISTORY |
20 --| 02-Mar-2010 Chaoqun Wu Created |
21 --| 18-May-2010 Chaoqun Wu Bug# 9722570, new way to handle|
22 --| no data found case |
23 --| 03-Jun-2010 Chaoqun Wu Bug# 9771082, Cash flow items |
24 --| do not order by line number |
25 --+======================================================================*/
26
27 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_CFI_EXPORT_PKG';
28
29 --==========================================================================
30 -- PROCEDURE NAME:
31 --
32 -- Add_Cash_Flow_Items Public
33 --
34 -- DESCRIPTION:
35 --
36 -- This procedure is to export Cash Flow Item related information
37 -- which defined in the system.
38 --
39 -- PARAMETERS:
40 -- In: pn_cfs_report_id NUMBER identifier of cash flow statment report
41 -- pn_chart_of_account_id NUMBER identifier of chart of account
42 --
43 -- DESIGN REFERENCES:
44 -- GL_Chaoqun.doc
45 --
46 -- CHANGE HISTORY:
47 -- 02-Mar-2010 Chaoqun Wu created
48 -- 18-May-2010 Chaoqun Wu Bug# 9722570, new way to handle no data found case
49 -- 03-Jun-2010 Chaoqun Wu Bug# 9771082, Cash flow items do not order by line number
50 --==========================================================================
51
52 PROCEDURE Add_Cash_Flow_Items
53 (pn_cfs_report_id IN NUMBER
54 ,pn_chart_of_account_id IN NUMBER
55 )
56 IS
57 lv_procedure_name VARCHAR2(40) := 'Add_Cash_Flow_Items';
58 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
59 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
60 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
61 lv_error_msg VARCHAR2(2000) := NULL;
62 lv_added_zero VARCHAR2(30) := '';
63 ln_max_length NUMBER := 0;
64 lv_report_name VARCHAR2(100) := NULL;
65 ln_rec_count NUMBER := 0;
66
67 --Cursor for getting Cash Flow Item related information by report id and COA id
68 --
69 CURSOR cash_flow_item_detail_cur
70 (lv_added_zero VARCHAR2
71 ,pn_cfs_report_id NUMBER
72 ,pn_chart_of_account_id NUMBER)
73 IS
74 SELECT SUBSTR(lv_added_zero || TO_CHAR(CASA.AXIS_SEQ),
75 LENGTH(TO_CHAR(CASA.AXIS_SEQ))+1,
76 LENGTH(lv_added_zero || TO_CHAR(CASA.AXIS_SEQ))) AS CFI_NUM, --Cash Flow Item Number
77 RRA.DESCRIPTION AS CFI_NAME, --Cash Flow Item Name.
78 RRA.DESCRIPTION AS CFI_DESC, --Cash Flow Item Description
79 '1' AS IS_LAST_LEVEL, --Is the Last Level
80 '1' AS CFI_LEVEL, --Cash Flow Item Level
81 '' AS PARENT_CFI_LEVEL, --Parent Cash Flow Item Number
82 DECODE(CASA.ITEM_SOURCE, --Cash Flow Item Source
83 'Main Statement',
84 '1',
85 'Supplementary Schedule',
86 '0',
87 NULL) AS CFI_SOURCE,
88 DECODE(CASA.ITEM_ATTRIBUTE,
89 'Inflow',
90 '1',
91 'Outflow',
92 '0',
93 'Others',
94 '2',
95 ' ') AS CFI_ATTRIBUTE --Cash Flow Item Attribute
96 FROM RG_REPORT_AXES RRA,
97 JA_CN_CFS_ASSIGN_SUP_ALL CASA,
98 RG_REPORTS RP
99 WHERE RRA.AXIS_SET_ID = CASA.AXIS_SET_ID
100 AND RRA.AXIS_SEQ = CASA.AXIS_SEQ
101 AND RRA.AXIS_SET_ID = RP.ROW_SET_ID --CFS Row set id
102 AND CASA.CHART_OF_ACCOUNTS_ID = pn_chart_of_account_id --parameter: pn_chart_of_account_id
103 AND RP.REPORT_ID = pn_cfs_report_id --parameter: pn_cfs_report_id
104 ORDER BY CFI_NUM; --Added for fixing bug# 9771082
105
106 BEGIN
107 --logging for debug
108 IF (ln_proc_level >= ln_dbg_level)
109 THEN
110 FND_LOG.STRING(ln_proc_level,
111 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
112 '.begin',
113 'Enter procedure');
114 -- logging the parameters
115 FND_LOG.STRING(ln_proc_level,
116 lv_procedure_name ||
117 '.parameters',
118 'pn_chart_of_account_id=' || pn_chart_of_account_id || ',' ||
119 'pn_cfs_report_id=' || pn_cfs_report_id);
120 END IF; --ln_proc_level>=ln_dbg_level
121 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
122 '.parameters:' ||
123 'pn_chart_of_account_id=' || pn_chart_of_account_id || ',' ||
124 'pn_cfs_report_id=' || pn_cfs_report_id);
125
126 --Get the max length of cfs row line number
127 BEGIN
128 SELECT MAX(LENGTH(RRA.AXIS_SEQ))
129 INTO ln_max_length
130 FROM RG_REPORT_AXES RRA, RG_REPORTS RP
131 WHERE RP.ROW_SET_ID = RRA.AXIS_SET_ID
132 AND RP.REPORT_ID = pn_cfs_report_id; --parameter: pn_cfs_report_id
133
134 EXCEPTION
135 WHEN NO_DATA_FOUND THEN
136 ln_max_length := 0;
137 --RAISE NO_DATA; --Deleted for bug# 9722570
138 END;
139
140 FOR i IN 1..ln_max_length LOOP
141 lv_added_zero := lv_added_zero || '0';
142 END LOOP;
143
144
145 FOR v_row IN cash_flow_item_detail_cur(lv_added_zero
146 ,pn_cfs_report_id
147 ,pn_chart_of_account_id)
148 LOOP
149 Ja_Cn_Utility.Add_Sub_Root_Node('CASH_FLOW_ITEMS'
150 ,Ja_Cn_Utility.GV_TAG_TYPE_START
151 );
152 Ja_Cn_Utility.Add_Child_Node('CASH_FLOW_ITEM_NUMBER'
153 ,v_row.CFI_NUM
154 );
155 Ja_Cn_Utility.Add_Child_Node('CASH_FLOW_ITEM_NAME',
156 v_row.CFI_NAME);
157 Ja_Cn_Utility.Add_Child_Node('CASH_FLOW_ITEM_DESCRIPTION'
158 ,v_row.CFI_DESC
159 );
160 Ja_Cn_Utility.Add_Fixed_Child_Node('IS_THE_LAST_LEVEL'
161 ,v_row.IS_LAST_LEVEL
162 ,1
163 );
164 Ja_Cn_Utility.Add_Child_Node('CASH_FLOW_ITEM_LEVEL'
165 ,v_row.CFI_LEVEL
166 );
167 Ja_Cn_Utility.Add_Child_Node('PARENT_CASH_FLOW_ITEM_NUMBER'
168 ,v_row.PARENT_CFI_LEVEL
169 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
170 ,Ja_Cn_Utility.GV_REQUIRED_NO
171 );
172 Ja_Cn_Utility.Add_Fixed_Child_Node('CASH_FLOW_ITEM_SOURCE'
173 ,v_row.CFI_SOURCE
174 ,1
175 );
176 Ja_Cn_Utility.Add_Fixed_Child_Node('CASH_FLOW_ITEM_ATTRIBUTE'
177 ,v_row.CFI_ATTRIBUTE
178 ,1
179 );
180 Ja_Cn_Utility.Add_Sub_Root_Node('CASH_FLOW_ITEMS'
181 ,Ja_Cn_Utility.GV_TAG_TYPE_END
182 );
183 ln_rec_count := ln_rec_count + 1;
184
185 END LOOP;
186
187 --logging the variables
188 IF (ln_statement_level >= ln_dbg_level)
189 THEN
190 FND_LOG.STRING(ln_statement_level,
191 lv_procedure_name,
192 'ln_rec_count:' || ln_rec_count);
193 END IF; --(ln_statement_level >= ln_dbg_level)
194 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
195 '.variable:' ||
196 'ln_rec_count=' || ln_rec_count);
197
198 -- if the row count is 0, indicate that there is no assignment
199 --in tab Cash Flow Items of Cash Flow Statement Assignment Form
200 -- no need to handle if minOccurs=0 specified in xml schema
201 IF ln_rec_count = 0
202 THEN
203 --Begin: Added for bug# 9722570
204 Ja_Cn_Utility.Print_No_Data_Found_For_Log('CASH_FLOW_ITEMS');
205 Ja_Cn_Utility.Add_Sub_Root_Node('CASH_FLOW_ITEMS'
206 ,Ja_Cn_Utility.GV_TAG_TYPE_START
207 );
208 Ja_Cn_Utility.Add_Child_Node('CASH_FLOW_ITEM_NUMBER'
209 ,NULL
210 );
211 Ja_Cn_Utility.Add_Child_Node('CASH_FLOW_ITEM_NAME'
212 ,NULL
213 );
214 Ja_Cn_Utility.Add_Child_Node('CASH_FLOW_ITEM_DESCRIPTION'
215 ,NULL
216 );
217 Ja_Cn_Utility.Add_Fixed_Child_Node('IS_THE_LAST_LEVEL'
218 ,NULL
219 ,1
220 );
221 Ja_Cn_Utility.Add_Child_Node('CASH_FLOW_ITEM_LEVEL'
222 ,NULL
223 );
224 Ja_Cn_Utility.Add_Child_Node('PARENT_CASH_FLOW_ITEM_NUMBER'
225 ,NULL
226 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
227 ,Ja_Cn_Utility.GV_REQUIRED_NO
228 );
229 Ja_Cn_Utility.Add_Fixed_Child_Node('CASH_FLOW_ITEM_SOURCE'
230 ,NULL
231 ,1
232 );
233 Ja_Cn_Utility.Add_Fixed_Child_Node('CASH_FLOW_ITEM_ATTRIBUTE'
234 ,NULL
235 ,1
236 );
237 Ja_Cn_Utility.Add_Sub_Root_Node('CASH_FLOW_ITEMS'
238 ,Ja_Cn_Utility.GV_TAG_TYPE_END
239 );
240 --End: Added for bug# 9722570
241 --Get cfs report name for error message
242 BEGIN
243 SELECT RP.NAME
244 INTO lv_report_name
245 FROM RG_REPORTS RP
246 WHERE RP.REPORT_ID = pn_cfs_report_id; --parameter: pn_cfs_report_id
247 EXCEPTION
248 WHEN OTHERS THEN
249 lv_report_name := '';
250 END;
251
252 Fnd_Message.Set_Name(Application => 'JA',
253 NAME => 'JA_CN_MISSING_CASH_FLOW_ASSIGN'
254 );
255 Fnd_Message.SET_TOKEN(TOKEN => 'REPORT_NAME',
256 VALUE => lv_report_name
257 );
258 lv_error_msg := Fnd_Message.Get;
259
260 --Output error message
261 Fnd_File.Put_Line(Fnd_File.Log, lv_error_msg);
262 --RAISE NO_DATA; --Deleted for bug# 9722570
263 END IF; --ln_rec_count = 0
264
265 --logging for debug
266 IF (ln_proc_level >= ln_dbg_level)
267 THEN
268 FND_LOG.STRING(ln_proc_level,
269 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
270 'Exit procedure');
271 END IF; -- (ln_proc_level>=ln_dbg_level)
272
273 EXCEPTION
274 --Begin: Deleted for bug# 9722570
275 /* WHEN NO_DATA THEN
276 --logging for debug
277 IF (ln_proc_level >= ln_dbg_level)
278 THEN
279 FND_LOG.STRING(ln_proc_level,
280 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
281 'exception',
282 'No data for Cash Flow Items');
283 END IF; --ln_proc_level>=ln_dbg_level
284 Ja_Cn_Utility.Print_No_Data_Found_For_Log('CASH_FLOW_ITEMS');
285 RAISE;*/
286 --End: Deleted for bug# 9722570
287 WHEN OTHERS THEN
288 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
289 THEN
290 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
291 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
292 '.Other_Exception ',
293 SQLCODE || SQLERRM);
294 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
295 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
296 RAISE;
297 END Add_Cash_Flow_Items;
298
299 END JA_CN_CFI_EXPORT_PKG;