[Home] [Help]
PACKAGE BODY: APPS.JA_CN_CFS_IMA_PKG
Source
1 PACKAGE BODY JA_CN_CFS_IMA_PKG AS
2 --$Header: JACNIMAB.pls 120.5 2011/05/19 09:26:06 choli ship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation
5 --| Redwood Shores, CA, USA
6 --| All rights reserved.
7 --+=======================================================================
8 --| FILENAME
9 --| JACNIMAB.pls
10 --|
11 --| DESCRIPTION
12 --|
13 --| This package is to provide share procedures for CNAO programs
14 --|
15 --| PROCEDURE LIST
16 --|
17 --| Item_Mapping_Analysis_Report
18 --|
19 --|
20 --| HISTORY
21 --| 27-APR-2007 Joy Liu Created
22 --| 03-SEP-2008 Chaoqun Wu Fixed bug# 7373268
23 --+======================================================================*/
24
25 --==========================================================================
26 -- PROCEDURE NAME:
27 --
28 -- Item_Mapping_Analysis_Report Public
29 --
30 -- DESCRIPTION:
31 --
32 -- This procedure is used to export the record which item mapping form saved.
33 -- It can help the audience know the cash flow of the company and do cash forecasting based on it
34 --
35 --
36 -- PARAMETERS:
37 -- Out: errbuf Mandatory parameter for PL/SQL concurrent programs
38 -- Out: retcode Mandatory parameter for PL/SQL concurrent programs
39 -- In: P_APLICATION_ID Application ID
40 -- In: P_EVENT_CLASS_CODE Event class code
41 -- In: P_SUPPORTING_REFERENCE_CODE Supporting reference code
42 -- In: P_CHART_OF_ACCOUNTS_ID Chart of Accounts ID
43
44 --
45 -- DESIGN REFERENCES:
46 --
47 --
48 -- CHANGE HISTORY:
49 --
50 -- 27-APR-2007 Joy Liu Created
51 -- 03-SEP-2008 Chaoqun Wu Fixed bug# 7373268
52 -- 15-Dec-2008 Shujuan Yan Fixed bug# 7626489
53 --===========================================================================
54
55 PROCEDURE Item_Mapping_Analysis_Report(errbuf OUT NOCOPY VARCHAR2
56 ,retcode OUT NOCOPY VARCHAR2
57 ,P_APLICATION_ID IN Number
58 ,P_EVENT_CLASS_CODE IN Varchar2
59 ,P_SUPPORTING_REFERENCE_CODE IN Varchar2
60 ,P_CHART_OF_ACCOUNTS_ID IN NUMBER)AS
61
62 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
63 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
64 l_proc_name VARCHAR2(100) :='Cash Flow Item Mappings Report';
65
66 l_Application_Id Ja_Cn_Cfs_Item_Mapping_Hdrs.Application_Id%type:=P_APLICATION_ID;
67 l_Event_Class_Code Ja_Cn_Cfs_Item_Mapping_Hdrs.Event_Class_Code%type:=P_EVENT_CLASS_CODE;
68 l_Analyciatl_Criterion_Code Ja_Cn_Cfs_Item_Mapping_Hdrs.Analytical_Criterion_Code%type:=P_SUPPORTING_REFERENCE_CODE;
69 l_Chart_Of_Accounts_Id Ja_Cn_Cfs_Item_Mapping_Hdrs.Chart_Of_Accounts_Id%type:=P_CHART_OF_ACCOUNTS_ID;
70 l_Mapping_Header_Id Ja_Cn_Cfs_Item_Mapping_Hdrs.Mapping_Header_Id%type;
71 l_h_effective_start_date Ja_Cn_Cfs_Item_Mapping_Hdrs.Effective_Start_Date%type;
72 l_h_effective_end_date Ja_Cn_Cfs_Item_Mapping_Hdrs.Effective_End_Date%type;
73 l_Application_Name FND_APPLICATION_TL.Application_Name%type;
74 l_Event_Class_Name xla_event_classes_TL.Name%type;
75 l_source_name Xla_Sources_tl.Name%type;
76 l_org_id ja_cn_cfs_item_mapping_lines.org_id%type;
77 l_org_name hr_all_organization_units_tl.name%type;
78 l_ac_value ja_cn_cfs_item_mapping_lines.ac_value%type;
79 l_detailed_cfs_item ja_cn_cfs_item_mapping_lines.detailed_cfs_item%type;
80 l_cash_flow_item_desc Fnd_Flex_Values_Tl.Description%type;
81 l_effective_start_date Ja_Cn_Cfs_Item_Mapping_lines.Effective_Start_Date%type;
82 l_effective_end_date Ja_Cn_Cfs_Item_Mapping_lines.Effective_End_Date%type;
83
84 l_varchar_test varchar2(4000);
85
86
87
88 l_xml_report XMLTYPE;
89 l_xml_parameter XMLTYPE;
90 l_xml_head XMLTYPE;
91 l_xml_line XMLTYPE;
92 l_xml_item XMLTYPE;
93 l_xml_head_line XMLTYPE;
94 l_xml_root XMLTYPE;
95 l_characterset varchar(245);
96
97
98 CURSOR c_mapping_headers is
99 SELECT Hdr.Mapping_Header_Id,
100 hdr.effective_start_date,
101 hdr.effective_end_date,
102 hdr.application_id,
103 hdr.event_class_code,
104 hdr.analytical_criterion_code
105 FROM Ja_Cn_Cfs_Item_Mapping_Hdrs Hdr
106 WHERE Hdr.Application_Id = nvl(l_Application_Id,Hdr.Application_Id)
107 AND Hdr.Event_Class_Code = nvl(l_Event_Class_Code,Hdr.Event_Class_Code)
108 AND Hdr.Analytical_Criterion_Code = nvl(l_Analyciatl_Criterion_Code,Hdr.Analytical_Criterion_Code)
109 AND Hdr.Chart_Of_Accounts_Id = l_Chart_Of_Accounts_Id;
110
111 CURSOR c_mapping_lines is
112 SELECT Ac_Value,
113 Detailed_Cfs_Item,
114 Effective_Start_Date,
115 Effective_End_Date,
116 org_id
117 FROM ja_cn_cfs_item_mapping_lines Lin
118 WHERE Lin.Mapping_Header_Id = l_Mapping_Header_Id;
119
120 --get organization name
121 CURSOR c_org_name is
122 SELECT NAME
123 FROM Hr_All_Organization_Units_Tl
124 WHERE Organization_Id = l_org_id
125 AND LANGUAGE = USERENV('LANG');
126
127 --get the application name
128 CURSOR c_Application_Name is
129 select Application_Name
130 from FND_APPLICATION_TL
131 where Application_Id = l_Application_Id
132 and LANGUAGE = USERENV('LANG');
133
134 --get the event_class-name
135 CURSOR c_Event_Class_Name is
136 select name
137 FROM xla_event_classes_TL
138 where Event_Class_Code = l_Event_Class_Code
139 and LANGUAGE = USERENV('LANG');
140
141 --get cash flow item description
142 /* CURSOR c_cash_flow_item_desc is
143 SELECT Ffvt.Description DESCRIPTION
144 FROM Fnd_Flex_Values_Tl Ffvt,Ja_Cn_Cfs_Item_Mapping_Lines JCCIM
145 WHERE Ffvt.Flex_Value_Meaning=JCCIM.DETAILED_CFS_ITEM
146 AND JCCIM.MAPPING_HEADER_ID=l_Mapping_Header_Id
147 and JCCIM.DETAILED_CFS_ITEM=l_detailed_cfs_item
148 and Ffvt.LANGUAGE = USERENV('LANG');*/
149
150 CURSOR c_cash_flow_item_desc IS --Fixed bug# 7373268
151 SELECT DISTINCT FFVT.DESCRIPTION DESCRIPTION
152 FROM FND_FLEX_VALUE_SETS FFVS
153 ,FND_FLEX_VALUES_TL FFVT
154 ,FND_FLEX_VALUES FFV
155 ,Ja_Cn_Cfs_Item_Mapping_Lines JCCIML
156 ,JA_CN_CASH_VALUESETS_ALL JCCVA
157 WHERE FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID
158 AND JCCVA.CHART_OF_ACCOUNTS_ID = l_Chart_Of_Accounts_Id
159 AND JCCVA.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
160 AND FFVT.FLEX_VALUE_ID = FFV.FLEX_VALUE_ID
161 AND FFV.FLEX_VALUE = JCCIML.DETAILED_CFS_ITEM
162 AND JCCIML.MAPPING_HEADER_ID=l_Mapping_Header_Id
163 and JCCIML.DETAILED_CFS_ITEM=l_detailed_cfs_item
164 AND FFVT.LANGUAGE = USERENV('LANG');
165
166 --get source name
167 CURSOR c_source_name is
168 SELECT St.Name
169 FROM Ja_Cn_Cfs_Item_Mapping_Hdrs Cim,
170 Xla_Analytical_Hdrs_Tl Ah,
171 Xla_Analytical_Dtls_Tl Ad,
172 Xla_Analytical_Dtls_b Adl,
173 Xla_Analytical_Sources Sur,
174 Xla_Sources_tl st,
175 Xla_Event_Classes_Tl Ev,
176 Fnd_Application_Tl App
177 WHERE Cim.Application_Id = Sur.Source_Application_Id
178 AND Cim.Amb_Context_Code = Ah.Amb_Context_Code
179 AND cim.analytical_criterion_code = ah.analytical_criterion_code
180 AND cim.analytical_criterion_type_code = ah.analytical_criterion_type_code
181 AND Ah.Amb_Context_Code = Ad.Amb_Context_Code
182 AND ah.analytical_criterion_code = ad.analytical_criterion_code
183 AND ah.analytical_criterion_type_code = ad.analytical_criterion_type_code
184 AND Ah.Amb_Context_Code = Adl.Amb_Context_Code
185 AND ah.analytical_criterion_code = adl.analytical_criterion_code
186 AND ah.analytical_criterion_type_code = adl.analytical_criterion_type_code
187 AND adl.grouping_order = 1
188 AND adl.analytical_detail_code = ad.analytical_detail_code
189 AND Ah.Analytical_Criterion_Code = Sur.Analytical_Criterion_Code
190 AND ah.amb_context_code = sur.amb_context_code
191 AND ah.analytical_criterion_type_code = sur.analytical_criterion_type_code
192 AND ad.analytical_detail_code = sur.analytical_detail_code
193 AND cim.application_id = sur.application_id
194 AND app.application_id = cim.application_id
195 AND st.Application_Id = App.Application_Id
196 AND st.Source_Code = Sur.Source_Code
197 AND st.Source_Type_Code = Sur.Source_Type_Code
198 AND sur.application_id = ev.application_id
199 AND sur.entity_code = ev.entity_code
200 AND sur.event_class_code = ev.event_class_code
201 AND App.LANGUAGE = Userenv('LANG')
202 AND Ev.LANGUAGE = Userenv('LANG')
203 AND Ah.LANGUAGE = Userenv('LANG')
204 AND Ad.LANGUAGE = Userenv('LANG')
205 AND st.language = Userenv('LANG')
206 AND Cim.Mapping_Header_Id= l_Mapping_Header_Id;
207
208
209 BEGIN
210 IF (l_proc_level >= l_dbg_level)
211 THEN
212 FND_LOG.String( l_proc_level
213 ,l_module_prefix||'.'||l_proc_name||'.begin'
214 ,'Enter procedure'
215 );
216 FND_LOG.String( l_proc_level
217 ,l_module_prefix||'.'||l_proc_name||'.parameters'
218 ,'P_APLICATION_ID '||P_APLICATION_ID
219 );
220 FND_LOG.String( l_proc_level
221 ,l_module_prefix||'.'||l_proc_name||'.parameters'
222 ,'P_EVENT_CLASS_CODE '||P_EVENT_CLASS_CODE
223 );
224 FND_LOG.String(l_proc_level
225 ,l_module_prefix||'.'||l_proc_name||'.parameters'
226 ,'P_SUPPORTING_REFERENCE_CODE '||P_SUPPORTING_REFERENCE_CODE
227 );
228
229 FND_LOG.String(l_proc_level
230 ,l_module_prefix||'.'||l_proc_name||'.parameters'
231 ,'P_CHART_OF_ACCOUNTS_ID '||P_CHART_OF_ACCOUNTS_ID
232 );
233
234 END IF; --(l_proc_level >= l_dbg_level)
235
236
237 --call JA_CN_UTILITY.Check_Profile, if it doesn't return true, exit
238 IF JA_CN_UTILITY.Check_Profile() <> TRUE THEN
239 IF (l_proc_level >= l_dbg_level) THEN
240 FND_LOG.STRING(l_proc_level,
241 l_proc_name,
242 'Check profile failed!');
243 END IF; --l_exception_level >= l_runtime_level
244 retcode := 1;
245 errbuf := '';
246 RETURN;
247 END IF; --JA_CN_UTILITY.Check_Profile() != TRUE
248
249
250 OPEN c_Application_Name;
251 FETCH c_Application_Name INTO l_Application_Name;
252 CLOSE c_Application_Name;
253 open c_Event_Class_Name;
254 FETCH C_Event_Class_Name INTO l_Event_Class_Name;
255 CLOSE c_Event_Class_Name;
256
257 --write the parameter infomation into variable l_xml_parameter and last into l_xml_report
258 -- Updated by shujuan for bug 7626489
259 l_characterset :=Fnd_Profile.VALUE(NAME => 'ICX_CLIENT_IANA_ENCODING');
260 FND_FILE.put_line(FND_FILE.output,'<?xml version="1.0" encoding= '||'"'||l_characterset||'"?>');
261 --FND_FILE.put_line(FND_FILE.output,'<?xml version="1.0" encoding="utf-8" ?>');
262 l_xml_report := NULL;
263 SELECT XMLELEMENT("P_APPLICATION_NAME",l_Application_Name) INTO l_xml_item FROM dual;
264 l_xml_parameter := l_xml_item;
265 SELECT XMLELEMENT("P_SUPPORTING_REFERENCE_CODE",l_Analyciatl_Criterion_Code ) INTO l_xml_item FROM dual;
266 SELECT XMLCONCAT(l_xml_parameter,l_xml_item) INTO l_xml_parameter FROM dual;
267 SELECT XMLELEMENT("P_EVENT_CLASS_NAME",l_Event_Class_Name) INTO l_xml_item FROM dual;
268 SELECT XMLCONCAT(l_xml_parameter,l_xml_item) INTO l_xml_parameter FROM dual;
269 SELECT XMLCONCAT(l_xml_report,l_xml_parameter) INTO l_xml_report FROM dual;
270
271 --get head infomation according to the parameters inputed.
272 --for each head, get its lines infomation.
273
274
275 OPEN c_mapping_headers;
276 LOOP
277 FETCH c_mapping_headers INTO l_Mapping_Header_Id,
278 l_h_effective_start_date,
279 l_h_effective_end_date,
280 l_Application_Id,
281 l_Event_Class_Code,
282 l_Analyciatl_Criterion_Code ;
283
284 EXIT WHEN c_mapping_headers%NOTFOUND;
285
286 l_xml_head_line:=NULL;
287 OPEN c_Application_Name;
288 FETCH c_Application_Name INTO l_Application_Name;
289 CLOSE c_Application_Name;
290 open c_Event_Class_Name;
291 FETCH C_Event_Class_Name INTO l_Event_Class_Name;
292 CLOSE c_Event_Class_Name;
293
294 OPEN c_source_name;
295 FETCH c_source_name INTO l_source_name;
296 CLOSE c_source_name;
297 --write head infomation to l_xml_head, then into l_xml_head_line
298 l_xml_head:=NULL;
299 SELECT XMLELEMENT("APPLICATION_NAME",l_Application_Name) INTO l_xml_item FROM dual;
300 l_xml_head:=l_xml_item;
301 SELECT XMLELEMENT("EVENT_CLASS_NAME",l_Event_Class_Name) INTO l_xml_item FROM dual;
302 SELECT XMLCONCAT(l_xml_head,l_xml_item) INTO l_xml_head FROM dual;
303 SELECT XMLELEMENT("SUPPORTING_REFERENCE_CODE",l_Analyciatl_Criterion_Code) INTO l_xml_item FROM dual;
304 SELECT XMLCONCAT(l_xml_head,l_xml_item) INTO l_xml_head FROM dual;
305 SELECT XMLELEMENT("SOURCE_NAME",l_source_name) INTO l_xml_item FROM dual;
306 SELECT XMLCONCAT(l_xml_head,l_xml_item) INTO l_xml_head FROM dual;
307 SELECT XMLELEMENT("H_EFFECTIVE_START_DATE",l_h_effective_start_date) INTO l_xml_item FROM dual;
308 SELECT XMLCONCAT(l_xml_head,l_xml_item) INTO l_xml_head FROM dual;
309 SELECT XMLELEMENT("H_EFFECTIVE_END_DATE",l_h_effective_end_date) INTO l_xml_item FROM dual;
310 SELECT XMLCONCAT(l_xml_head,l_xml_item) INTO l_xml_head FROM dual;
311 SELECT XMLCONCAT(l_xml_head_line,l_xml_head) INTO l_xml_head_line FROM dual;
312
313 --get the lines infomation for this head
314 OPEN c_mapping_lines;
315 LOOP
316 FETCH c_mapping_lines INTO l_ac_value,
317 l_detailed_cfs_item,
318 l_effective_start_date,
319 l_effective_end_date,
320 l_org_id;
321 EXIT WHEN c_mapping_lines%NOTFOUND;
322 l_cash_flow_item_desc:='';
323 OPEN c_cash_flow_item_desc;
324 FETCH c_cash_flow_item_desc INTO l_cash_flow_item_desc;
325 CLOSE c_cash_flow_item_desc;
326
327 l_org_name:='';
328 OPEN c_org_name;
329 FETCH c_org_name INTO l_org_name;
330 close c_org_name;
331 --write lines infomation into l_xml_line, then concat to l_xml_head_line into l_xml_head_line.
332 l_xml_line:=NULL;
333 SELECT XMLELEMENT("ORG_NAME",l_org_name) INTO l_xml_item FROM dual;
334 l_xml_line:=l_xml_item;
335 SELECT XMLELEMENT("AC_VALUE",l_ac_value) INTO l_xml_item FROM dual;
336 SELECT XMLCONCAT(l_xml_line,l_xml_item) INTO l_xml_line FROM dual;
337 SELECT XMLELEMENT("DETAILED_CFS_ITEM",l_detailed_cfs_item) INTO l_xml_item FROM dual;
338 SELECT XMLCONCAT(l_xml_line,l_xml_item) INTO l_xml_line FROM dual;
339 SELECT XMLELEMENT("DETAILED_ITEM_DESC",l_cash_flow_item_desc) INTO l_xml_item FROM dual;
340 SELECT XMLCONCAT(l_xml_line,l_xml_item) INTO l_xml_line FROM dual;
341 SELECT XMLELEMENT("EFFECTIVE_START_DATE",l_effective_start_date) INTO l_xml_item FROM dual;
342 SELECT XMLCONCAT(l_xml_line,l_xml_item) INTO l_xml_line FROM dual;
343 SELECT XMLELEMENT("EFFECTIVE_END_DATE",l_effective_end_date ) INTO l_xml_item FROM dual;
344 SELECT XMLCONCAT(l_xml_line,l_xml_item) INTO l_xml_line FROM dual;
345 SELECT XMLELEMENT("LINE",l_xml_line) INTO l_xml_line FROM dual;--line circulation
346 SELECT XMLCONCAT(l_xml_head_line,l_xml_line) INTO l_xml_head_line FROM dual;
347
348 END LOOP;
349 CLOSE c_mapping_lines;
350 SELECT XMLELEMENT("HEAD",l_xml_head_line) INTO l_xml_item FROM dual;--head circulation
351 SELECT XMLCONCAT(l_xml_report,l_xml_item) INTO l_xml_report FROM dual;
352 END LOOP;
353 CLOSE c_mapping_headers;
354
355 SELECT XMLELEMENT( "REPORT",l_xml_report) INTO l_xml_root FROM dual;--generate the whole report.
356 JA_CN_UTILITY.Output_Conc(l_xml_root.getclobval());
357
358 IF (l_proc_level >= l_dbg_level)
359 THEN
360 FND_LOG.STRING(l_proc_level,
361 l_module_prefix|| '.' || l_proc_name || '.end',
362 'end procedure');
363 END IF;
364
365 EXCEPTION
366 WHEN OTHERS THEN
367 IF (l_proc_level >= l_dbg_level)
368 THEN
369 FND_LOG.String( l_proc_level
370 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
371 ,SQLCODE||':'||SQLERRM);
372 END IF; --(l_proc_level >= l_dbg_level)
373 retcode := 2;
374 errbuf := SQLCODE||':'||SQLERRM;
375
376 END Item_Mapping_Analysis_Report;
377
378
379 END JA_CN_CFS_IMA_PKG;