[Home] [Help]
PACKAGE BODY: APPS.JA_CN_PS_SI_PRJ_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_PS_SI_PRJ_EXPORT_PKG AS
2 --$Header: JACNPRJB.pls 120.1 2010/08/25 09:38:38 wuliu noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNPRJB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export Project Information |
13 --| for Public Sector |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE Add_Project_Information |
17 --| |
18 --| |
19 --| HISTORY |
20 --| 06/AUG/2010 Wuhua Liu Created |
21 --+======================================================================*/
22
23 --==========================================================================
24 -- PROCEDURE NAME:
25 --
26 -- Add_Project_Information Public
27 --
28 -- DESCRIPTION:
29 --
30 -- This procedure is to export Project Information for
31 -- public sector
32 --
33 -- PARAMETERS:
34 -- In: pn_coa_id Chart of Account ID
35 -- pn_accounting_year Accounting Year
36 --
37 -- DESIGN REFERENCES:
38 -- TDD_1213_FIN_GL_P_CNAOV2_SI.doc
39 --
40 -- CHANGE HISTORY:
41 --
42 -- 06-AUG-2010 Wuhua Liu created
43
44 PROCEDURE Add_Project_Information
45 ( pn_coa_id IN NUMBER
46 , pn_accounting_year IN NUMBER
47 )
48 IS
49 lv_procedure_name VARCHAR(30) := 'Add_Project_Information';
50 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
51 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
52 ln_row_count NUMBER := 0;
53 lv_prj_seg VARCHAR2(20);
54 lv_prj_id_seg VARCHAR2(20) := 'FLEX_VALUE';
55 lv_prj_name_seg VARCHAR2(20) := 'DESCRIPTION';
56 lv_prj_level_attr VARCHAR2(20);
57 lv_prj_type_attr VARCHAR2(20);
58 lv_prj_category_attr VARCHAR2(20);
59 lv_prj_attribute_attr VARCHAR2(20);
60 lv_prj_start_date_attr VARCHAR2(20);
61 lv_prj_end_date_attr VARCHAR2(20);
62 lv_cursor_sql VARCHAR2(2000);
63 ln_prj_value_set_id NUMBER;
64 lv_project_id VARCHAR2(40);
65 lv_project_name VARCHAR2(40);
66 lv_project_level VARCHAR2(40);
67 lv_project_type VARCHAR2(40);
68 lv_project_category VARCHAR2(40);
69 lv_project_attribute VARCHAR2(40);
70 lv_project_start_date VARCHAR2(40);
71 lv_project_end_date VARCHAR2(40);
72 v_row cur_type;
73
74 BEGIN
75 --logging for debug
76 IF (ln_proc_level >= ln_dbg_level)
77 THEN
78 FND_LOG.STRING( ln_proc_level
79 , GV_MODULE_PREFIX || '.' || lv_procedure_name || '.begin'
80 , 'Enter procedure');
81 --logging the parameters
82 FND_LOG.STRING( ln_proc_level
83 , GV_MODULE_PREFIX || '.' || lv_procedure_name
84 || '.parameters'
85 , 'pn_coa_id=' || pn_coa_id || ','
86 || 'pv_accounting_year=' || pn_accounting_year);
87 END IF; --(ln_proc_level>=ln_dbg_level)
88 FND_FILE.PUT_LINE( FND_FILE.log
89 , GV_MODULE_PREFIX || '.' || lv_procedure_name
90 || '.parameters '
91 || 'pn_coa_id=' || pn_coa_id || ','
92 || 'pv_accounting_year=' || pn_accounting_year);
93
94 lv_prj_seg := JA_CN_UTILITY.Get_Attribute_Column
95 ( 'CPRO'
96 , 101
97 , pn_coa_id
98 );
99 lv_prj_id_seg := 'FLEX_VALUE';
100 lv_prj_name_seg := 'DESCRIPTION';
101 lv_prj_level_attr := JA_CN_UTILITY.Get_Attribute_Column
102 ( 'APLE'
103 , 0
104 , pn_coa_id
105 );
106 lv_prj_type_attr := JA_CN_UTILITY.Get_Attribute_Column
107 ( 'APTY'
108 , 0
109 , pn_coa_id
110 );
111 lv_prj_category_attr := JA_CN_UTILITY.Get_Attribute_Column
112 ( 'APCA'
113 , 0
114 , pn_coa_id
115 );
116 lv_prj_attribute_attr := JA_CN_UTILITY.Get_Attribute_Column
117 ( 'APAT'
118 , 0
119 , pn_coa_id
120 );
121 lv_prj_start_date_attr := JA_CN_UTILITY.Get_Attribute_Column
122 ( 'APSD'
123 , 0
124 , pn_coa_id
125 );
126 lv_prj_end_date_attr := JA_CN_UTILITY.Get_Attribute_Column
127 ( 'APED'
128 , 0
129 , pn_coa_id
130 );
131 IF(ln_proc_level >= ln_dbg_level)
132 THEN
133 FND_LOG.STRING( ln_proc_level
134 , lv_procedure_name || '.variables'
135 , 'lv_prj_seg='|| lv_prj_seg ||','
136 || 'lv_prj_level_attr=' || lv_prj_level_attr||','
137 || 'lv_prj_type_attr=' || lv_prj_type_attr||','
138 || 'lv_prj_category_attr=' || lv_prj_category_attr || ','
139 || 'lv_prj_attribute_attr=' || lv_prj_attribute_attr||','
140 || 'lv_prj_start_date_attr='||lv_prj_start_date_attr||','
141 || 'lv_prj_end_date_attr='||lv_prj_end_date_attr);
142 END IF; --(ln_proc_level >= ln_dbg_level)
143 --logging for debug
144 FND_FILE.PUT_LINE( FND_FILE.log
145 , 'lv_prj_seg='||lv_prj_seg||','
146 || 'lv_prj_level_attr='||lv_prj_level_attr||','
147 || 'lv_prj_type_attr='||lv_prj_type_attr||','
148 || 'lv_prj_category_attr='||lv_prj_category_attr||','
149 || 'lv_prj_attribute_attr='||lv_prj_attribute_attr||','
150 ||'lv_prj_start_date_attr='||lv_prj_start_date_attr||','
151 || 'lv_prj_end_date_attr='||lv_prj_end_date_attr);
152 --Get the project key flex field segment's value set id.
153 SELECT
154 flex_value_set_id
155 INTO
156 ln_prj_value_set_id
157 FROM
158 fnd_id_flex_segments
159 WHERE application_id = 101
160 AND id_flex_code = 'GL#'
161 AND id_flex_num = pn_coa_id -- coa id parameter
162 AND application_column_name = lv_prj_seg; -- variable lv_prj_seg
163
164 --Construct the Dymanic SQL for the project information.
165 lv_cursor_sql := 'SELECT '
166 --project id attribute
167 || lv_prj_id_seg || ' project_id,'
168 --project name attribute
169 || lv_prj_name_seg || ' project_name,'
170 --project level attribute
171 || lv_prj_level_attr || ' project_level,'
172 --project type attribute
173 || lv_prj_type_attr || ' project_type,'
174 --project category
175 || lv_prj_category_attr || ' project_category,'
176 --project attribute
177 || lv_prj_attribute_attr || ' project_attribute,'
178 --project start date
179 || lv_prj_start_date_attr || ' project_start_date,'
180 --project end date
181 || lv_prj_end_date_attr || ' project_end_date '
182 || 'FROM '
183 || ' fnd_flex_values_vl ' -- view fnd_flex_values_vl
184 || 'WHERE flex_value_set_id = ' || ln_prj_value_set_id
185 --accounting year parameter and project start date
186 || ' AND ('
187 || lv_prj_start_date_attr || ' IS NULL '
188 || ' OR '
189 || 'EXTRACT(YEAR FROM
190 TO_DATE(' || lv_prj_start_date_attr ||
191 ',' ||
192 '''YYYY/MM/DD HH24:MI:SS'''
193 || '))' ||
194 ' <= ' ||
195 pn_accounting_year || ')'
196 --accounting year parameter and project end date
197 || ' AND ('
198 || lv_prj_end_date_attr || ' IS NULL '
199 || ' OR '
200 || 'EXTRACT(YEAR FROM
201 TO_DATE(' || lv_prj_end_date_attr ||
202 ',' ||
203 '''YYYY/MM/DD HH24:MI:SS'''
204 || '))' ||
205 ' >= ' ||
206 pn_accounting_year ||
207 ')';
208 --logging for debug
209 IF( ln_proc_level >= ln_dbg_level )
210 THEN
211 FND_LOG.STRING( ln_proc_level
212 , lv_procedure_name || '.variables'
213 , 'lv_cursor_sql=' || lv_cursor_sql );
214 END IF ;
215 FND_FILE.PUT_LINE( FND_FILE.log
216 , 'lv_cursor_sql=' || lv_cursor_sql);
217 --Get the project Information
218 OPEN v_row FOR lv_cursor_sql;
219 LOOP
220 FETCH
221 v_row
222 INTO
223 lv_project_id
224 , lv_project_name
225 , lv_project_level
226 , lv_project_type
227 , lv_project_category
228 , lv_project_attribute
229 , lv_project_start_date
230 , lv_project_end_date;
231 EXIT WHEN v_row%NOTFOUND;
232 ln_row_count := ln_row_count + 1;
233 JA_CN_UTILITY.Add_Sub_Root_Node( 'PROJECT'
234 , JA_CN_UTILITY.GV_TAG_TYPE_START
235 );
236 JA_CN_UTILITY.Add_Child_Node( 'PROJECT_ID'
237 , lv_project_id
238 );
239 JA_CN_UTILITY.Add_Child_Node( 'PROJECT_NAME'
240 , lv_project_name
241 );
242 JA_CN_UTILITY.Add_Child_Node( 'PROJECT_LEVEL'
243 , lv_project_level
244 , JA_CN_UTILITY.GV_TYPE_NUMBER
245 );
246 JA_CN_UTILITY.Add_Child_Node( 'PROJECT_TYPE'
247 , lv_project_type
248 );
249 JA_CN_UTILITY.Add_Child_Node( 'PROJECT_CATEGORY'
250 , lv_project_category
251 );
252 JA_CN_UTILITY.Add_Child_Node( 'PROJECT_ATTRIBUTE'
253 , lv_project_attribute
254 );
255 IF(lv_project_start_date IS NOT NULL)
256 THEN
257 lv_project_start_date := TO_CHAR( FND_DATE.canonical_to_date
258 ( lv_project_start_date)
259 , 'YYYYMMDD'
260 );
261 ELSE
262 lv_project_start_date := '';
263 END IF; --(lv_project_starting_date IS NOT NULL)
264 JA_CN_UTILITY.Add_Fixed_Child_Node( 'PROJECT_STARTING_DATE'
265 , lv_project_start_date
266 , 8
267 );
268 IF(lv_project_end_date IS NOT NULL)
269 THEN
270 lv_project_end_date := TO_CHAR( FND_DATE.canonical_to_date
271 ( lv_project_end_date)
272 , 'YYYYMMDD'
273 );
274 ELSE
275 lv_project_end_date := '';
276 END IF; --(lv_project_date IS NOT NULL)
277 JA_CN_UTILITY.Add_Fixed_Child_Node( 'PROJECT_ENDING_DATE'
278 , lv_project_end_date
279 , 8
280 );
281 JA_CN_UTILITY.Add_Sub_Root_Node( 'PROJECT'
282 , JA_CN_UTILITY.GV_TAG_TYPE_END
283 );
284 END LOOP; -- v_row LOOP
285 CLOSE v_row;
286
287 --Add empty content
288 IF (ln_row_count = 0)
289 THEN
290 JA_CN_UTILITY.Print_No_Data_Found_For_Log( 'PROJECT'
291 , JA_CN_UTILITY.GV_MODULE_GLSI
292 );
293 JA_CN_UTILITY.Add_Sub_Root_Node( 'PROJECT'
294 , JA_CN_UTILITY.GV_TAG_TYPE_START
295 );
296 JA_CN_UTILITY.Add_Child_Node( 'PROJECT_ID'
297 , NULL
298 );
299 JA_CN_UTILITY.Add_Child_Node( 'PROJECT_NAME'
300 , NULL
301 );
302 JA_CN_UTILITY.Add_Child_Node( 'PROJECT_LEVEL'
303 , NULL
304 , JA_CN_UTILITY.GV_TYPE_NUMBER
305 );
306 JA_CN_UTILITY.Add_Child_Node( 'PROJECT_TYPE'
307 , NULL
308 );
309 JA_CN_UTILITY.Add_Child_Node( 'PROJECT_CATEGORY'
310 , NULL
311 );
312 JA_CN_UTILITY.Add_Child_Node( 'PROJECT_ATTRIBUTE'
313 , NULL
314 );
315 JA_CN_UTILITY.Add_Fixed_Child_Node( 'PROJECT_STARTING_DATE'
316 , NULL
317 , 8
318 );
319 JA_CN_UTILITY.Add_Fixed_Child_Node( 'PROJECT_ENDING_DATE'
320 , NULL
321 , 8
322 );
323 JA_CN_UTILITY.Add_Sub_Root_Node( 'PROJECT'
324 , JA_CN_UTILITY.GV_TAG_TYPE_END
325 );
326 END IF; -- (ln_row_count = 0)
327 --logging for debug
328 IF (ln_proc_level >= ln_dbg_level)
329 THEN
330 FND_LOG.STRING( ln_proc_level
331 , GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end'
332 , 'Exit Procedure');
333 END IF; --(ln_proc_level >= ln_dgb_level)
334 EXCEPTION
335 WHEN OTHERS THEN
336 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
337 THEN
338 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
339 , GV_MODULE_PREFIX || '.' || lv_procedure_name ||
340 '.Other Exception'
341 , SQLCODE || SQLERRM);
342 END IF; --(FND_LOG.LEVEL.UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
343 FND_FILE.put_line( FND_FILE.log
344 , lv_procedure_name || SQLCODE || SQLERRM);
345 RAISE;
346 END Add_Project_Information;
347
348 END JA_CN_PS_SI_PRJ_EXPORT_PKG;
349