[Home] [Help]
PACKAGE BODY: APPS.JA_CN_UDRV_EXPORT
Source
1 PACKAGE BODY JA_CN_UDRV_EXPORT AS
2 --$Header: JACNUDVB.pls 120.5 2011/05/04 06:44:34 choli noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNUDVB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export user-defined record values. |
13 --| |
14 --| PROCEDURE LIST |
15 --| Add_User_Defined_Record_Value Export User Defined Record Value |
16 --| |
17 --| |
18 --| HISTORY |
19 --| 03/10/2010 Shujuan Yan Created |
20 --| 03/15/2010 Qingyi Wang Add the service logice to export UDRV|
21 --| |
22 --| 01/05/2011 Jianchao Chi Updated for the new solution to change |
23 --| the logic of Voucher Number and Legal |
24 --| Entity |
25 --| 27-Apr-2011 Chongwu Li Updated, fix bug 12392517 |
26 --+======================================================================*/
27
28 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_UDRV_EXPORT';
29
30 --==========================================================================
31 -- PROCEDURE NAME:
32 --
33 -- Add_User_Defined_Record_Value Public
34 --
35 -- DESCRIPTION:
36 --
37 -- This procedure is User-Defined Record Valaue
38 --
39 -- PARAMETERS:
40 -- In: pn_coa_id NUMBER identifier of chart of account
41 -- pn_le_id NUMBER identifier of legal entity
42 -- pn_ledger_id NUMBER identifier of ledger
43 -- pv_accounting_year VARCHAR2 accounting year
44 -- DESIGN REFERENCES:
45 -- GL_Shujuan.doc
46 --
47 -- CHANGE HISTORY:
48 -- 10-Mar-2010 Shujuan yan created
49 --| 15-Mar-2010 Qingyi Wang add the service logice to export UDRV|
50 -- 05-Jan-2011 Jianchao Chi Update the new solution to change the logic of legal entity
51 -- 27-Apr-2011 Chongwu Li Updated, fix bug 12392517
52 --==========================================================================
53 PROCEDURE Add_User_Defined_Record_Value
54 ( pn_coa_id IN NUMBER
55 , pn_le_id IN NUMBER
56 , pn_ledger_id IN NUMBER
57 , pv_accounting_year IN VARCHAR2
58 )
59 IS
60 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
61 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
62 lv_procedure_name VARCHAR2(40) := 'Add_User_Defined_Record_Values';
63 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
64 ln_journal_number NUMBER;
65 lv_sub_segment_code VARCHAR2(20);
66 lv_source_code VARCHAR2(30);
67 lv_context_code VARCHAR2(30);
68 lv_record_value_number VARCHAR2(20);
69 lv_record_value_desc VARCHAR2(240);
70 lv_record_value_name VARCHAR2(60);
71 ln_record_number NUMBER;
72 NO_DATA EXCEPTION;
73
74 CURSOR user_defined_record_value_cur
75 ( pn_ledger_id NUMBER
76 , pn_le_id NUMBER
77 , pv_accounting_year VARCHAR2
78 )
79 IS
80 SELECT
81 subsidiary_segment_code
82 , sources_code
83 , context_code
84 FROM
85 ja_cn_sub_acc_mapping
86 WHERE chart_of_accounts_id = pn_coa_id
87 AND sources_code in ('COA', 'PROJECT MODULE')
88 --choli add to fix bug 12392517
89 ORDER BY subsidiary_segment_code;
90
91 CURSOR coa_value_name_desc_cur
92 ( pn_ledger_id NUMBER
93 , pn_le_id NUMBER
94 , pv_accounting_year VARCHAR2
95 )
96 IS
97 SELECT
98 distinct decode(lv_sub_segment_code,
99 'SEGMENT1',jcjl.segment1, 'SEGMENT2',jcjl.segment2, 'SEGMENT3',jcjl.segment3,
100 'SEGMENT4',jcjl.segment4, 'SEGMENT5',jcjl.segment5, 'SEGMENT6',jcjl.segment6,
101 'SEGMENT7',jcjl.segment7, 'SEGMENT8',jcjl.segment8, 'SEGMENT9',jcjl.segment9,
102 'SEGMENT10',jcjl.segment10, 'SEGMENT11',jcjl.segment11, 'SEGMENT12',jcjl.segment12,
103 'SEGMENT13',jcjl.segment13, 'SEGMENT14',jcjl.segment14, 'SEGMENT15',jcjl.segment15,
104 'SEGMENT16',jcjl.segment16, 'SEGMENT17',jcjl.segment17, 'SEGMENT18',jcjl.segment18,
105 'SEGMENT19',jcjl.segment19, 'SEGMENT20',jcjl.segment20, 'SEGMENT21',jcjl.segment21,
106 'SEGMENT22',jcjl.segment22, 'SEGMENT23',jcjl.segment23, 'SEGMENT24',jcjl.segment24,
107 'SEGMENT25',jcjl.segment25, 'SEGMENT26',jcjl.segment26, 'SEGMENT27',jcjl.segment27,
108 'SEGMENT28',jcjl.segment28, 'SEGMENT29',jcjl.segment29, 'SEGMENT30',jcjl.segment30) value_number
109 , ffvt.description
110 FROM
111 ja_cn_journal_lines jcjl
112 , gl_ledgers gl, gl_periods gp
113 , fnd_flex_values_tl ffvt
114 , fnd_flex_values ffv
115 , fnd_id_flex_segments fifs
116 , ja_cn_voucher_number jcvn
117 WHERE fifs.id_flex_num = pn_coa_id
118 AND fifs.id_flex_code = 'GL#'
119 AND fifs.application_column_name = lv_context_code
120 AND fifs.flex_value_set_id = ffv.flex_value_set_id
121 AND gl.ledger_id = pn_ledger_id
122 AND gl.period_set_name = gp.period_set_name
123 AND gp.period_name = jcjl.period_name
124 --Update by Jianchao Chi, for cnaov2 upgrade 05-JAN-2011
125 --Add ja_cn_voucher_number table, voucher number and legal entity
126 --are fetched from ja_cn_voucher_number
127 --The new one is
128 ----------------------------------------------------
129 --AND jcvn.je_header_id = jl.je_header_id
130 --AND jcvn.je_line_number = jl.je_line_num
131 --AND jcvn.legal_entity_id = pn_le_id;
132 ----------------------------------------------------
133 AND jcvn.je_header_id = jcjl.je_header_id
134 AND jcvn.je_line_number = jcjl.je_line_num
135 --Comment by Jianchao Chi to change the logic of legal entity, 05-JAN-2011
136 --AND jcjl.legal_entity_id = pn_le_id
137 AND jcvn.legal_entity_id = pn_le_id
138 AND jcjl.ledger_id = pn_ledger_id
139 AND gp.period_year = pv_accounting_year
140 --and ffv.flex_value_set_id = l_flex_value_set_id
141 AND ffv.flex_value_id = ffvt.flex_value_id
142 AND ffvt.language = userenv('LANG')
143 AND decode(lv_sub_segment_code,
144 'SEGMENT1',jcjl.segment1, 'SEGMENT2',jcjl.segment2, 'SEGMENT3',jcjl.segment3,
145 'SEGMENT4',jcjl.segment4, 'SEGMENT5',jcjl.segment5, 'SEGMENT6',jcjl.segment6,
146 'SEGMENT7',jcjl.segment7, 'SEGMENT8',jcjl.segment8, 'SEGMENT9',jcjl.segment9,
147 'SEGMENT10',jcjl.segment10, 'SEGMENT11',jcjl.segment11, 'SEGMENT12',jcjl.segment12,
148 'SEGMENT13',jcjl.segment13, 'SEGMENT14',jcjl.segment14, 'SEGMENT15',jcjl.segment15,
149 'SEGMENT16',jcjl.segment16, 'SEGMENT17',jcjl.segment17, 'SEGMENT18',jcjl.segment18,
150 'SEGMENT19',jcjl.segment19, 'SEGMENT20',jcjl.segment20, 'SEGMENT21',jcjl.segment21,
151 'SEGMENT22',jcjl.segment22, 'SEGMENT23',jcjl.segment23, 'SEGMENT24',jcjl.segment24,
152 'SEGMENT25',jcjl.segment25, 'SEGMENT26',jcjl.segment26, 'SEGMENT27',jcjl.segment27,
153 'SEGMENT28',jcjl.segment28, 'SEGMENT29',jcjl.segment29, 'SEGMENT30',jcjl.segment30) = ffv.flex_value
154 --choli add to fix bug 12392517
155 ORDER BY value_number;
156
157 CURSOR project_value_name_desc_cur
158 ( pn_ledger_id NUMBER
159 , pn_le_id NUMBER
160 , pv_accounting_year VARCHAR2
161 )
162 IS
163 SELECT
164 distinct decode(lv_sub_segment_code,
165 'SEGMENT1',jcjl.segment1, 'SEGMENT2',jcjl.segment2, 'SEGMENT3',jcjl.segment3,
166 'SEGMENT4',jcjl.segment4, 'SEGMENT5',jcjl.segment5, 'SEGMENT6',jcjl.segment6,
167 'SEGMENT7',jcjl.segment7, 'SEGMENT8',jcjl.segment8, 'SEGMENT9',jcjl.segment9,
168 'SEGMENT10',jcjl.segment10, 'SEGMENT11',jcjl.segment11, 'SEGMENT12',jcjl.segment12,
169 'SEGMENT13',jcjl.segment13, 'SEGMENT14',jcjl.segment14, 'SEGMENT15',jcjl.segment15,
170 'SEGMENT16',jcjl.segment16, 'SEGMENT17',jcjl.segment17, 'SEGMENT18',jcjl.segment18,
171 'SEGMENT19',jcjl.segment19, 'SEGMENT20',jcjl.segment20, 'SEGMENT21',jcjl.segment21,
172 'SEGMENT22',jcjl.segment22, 'SEGMENT23',jcjl.segment23, 'SEGMENT24',jcjl.segment24,
173 'SEGMENT25',jcjl.segment25, 'SEGMENT26',jcjl.segment26, 'SEGMENT27',jcjl.segment27,
174 'SEGMENT28',jcjl.segment28, 'SEGMENT29',jcjl.segment29, 'SEGMENT30',jcjl.segment30) value_number
175 , ppa.name,ppa.description
176 FROM
177 ja_cn_journal_lines jcjl
178 , gl_ledgers gl
179 , gl_periods gp
180 , pa_projects_all ppa
181 , ja_cn_voucher_number jcvn
182 WHERE gl.ledger_id = pn_ledger_id
183 --Update by Jianchao Chi, for cnaov2 upgrade 05-JAN-2011
184 --Add ja_cn_voucher_number table, voucher number and legal entity
185 --are fetched from ja_cn_voucher_number
186 --The new one is
187 ----------------------------------------------------
188 --AND jcvn.je_header_id = jl.je_header_id
189 --AND jcvn.je_line_number = jl.je_line_num
190 --AND jcvn.legal_entity_id = pn_le_id;
191 ----------------------------------------------------
192 AND jcvn.je_header_id = jcjl.je_header_id
193 AND jcvn.je_line_number = jcjl.je_line_num
194 AND gl.period_set_name = gp.period_set_name
195 AND gp.period_name = jcjl.period_name
196 --Comment by Jianchao Chi to change the logic of legal entity, 05-JAN-2011
197 --AND jcjl.legal_entity_id = pn_le_id
198 AND jcvn.legal_entity_id = pn_le_id
199 AND jcjl.ledger_id = pn_ledger_id
200 AND gp.period_year = pv_accounting_year
201 --and ffv.flex_value_set_id = l_flex_value_set_id
202 AND decode(lv_sub_segment_code,
203 'SEGMENT1',jcjl.segment1, 'SEGMENT2',jcjl.segment2, 'SEGMENT3',jcjl.segment3,
204 'SEGMENT4',jcjl.segment4, 'SEGMENT5',jcjl.segment5, 'SEGMENT6',jcjl.segment6,
205 'SEGMENT7',jcjl.segment7, 'SEGMENT8',jcjl.segment8, 'SEGMENT9',jcjl.segment9,
206 'SEGMENT10',jcjl.segment10, 'SEGMENT11',jcjl.segment11, 'SEGMENT12',jcjl.segment12,
207 'SEGMENT13',jcjl.segment13, 'SEGMENT14',jcjl.segment14, 'SEGMENT15',jcjl.segment15,
208 'SEGMENT16',jcjl.segment16, 'SEGMENT17',jcjl.segment17, 'SEGMENT18',jcjl.segment18,
209 'SEGMENT19',jcjl.segment19, 'SEGMENT20',jcjl.segment20, 'SEGMENT21',jcjl.segment21,
210 'SEGMENT22',jcjl.segment22, 'SEGMENT23',jcjl.segment23, 'SEGMENT24',jcjl.segment24,
211 'SEGMENT25',jcjl.segment25, 'SEGMENT26',jcjl.segment26, 'SEGMENT27',jcjl.segment27,
212 'SEGMENT28',jcjl.segment28, 'SEGMENT29',jcjl.segment29, 'SEGMENT30',jcjl.segment30) = ppa.segment1
213 --choli add to fix bug 12392517
214 ORDER BY value_number;
215
216 BEGIN
217 --logging for debug
218 IF (ln_proc_level>=ln_dbg_level)
219 THEN
220 FND_LOG.STRING( ln_proc_level
221 , GV_MODULE_PREFIX ||'.' || lv_procedure_name ||
222 '.begin'
223 , 'Enter procedure'
224 );
225
226 -- logging the parameters
227 FND_LOG.STRING(ln_proc_level,
228 lv_procedure_name ||
229 '.parameters',
230 'pn_legal_entity_id=' || pn_le_id|| ',' ||
231 'pn_ledger_id=' || pn_ledger_id || ',' ||
232 'pv_accounting_year=' || pv_accounting_year || ',' ||
233 'pn_coa_id=' || pn_coa_id);
234 END IF; --l_proc_level>=l_dbg_level
235
236 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
237 '.parameters:' ||
238 'pn_legal_entity_id=' || pn_le_id|| ',' ||
239 'pn_ledger_id=' || pn_ledger_id || ',' ||
240 'pv_accounting_year=' || pv_accounting_year || ',' ||
241 'pn_coa_id=' || pn_coa_id);
242
243 SELECT count(*)
244 INTO ln_journal_number
245 FROM (SELECT DISTINCT * --Add by Jianchao Chi, 10-Jan-2011, for one hearder may has two same
246 FROM ja_cn_journal_lines jl, --line numbers in ja_cn_journal_lines.
247 gl_ledgers gl,
248 gl_periods gp,
249 ja_cn_voucher_number jcvn
250 --Update by Jianchao Chi, for cnaov2 upgrade 05-JAN-2011
251 --Add ja_cn_voucher_number table, voucher number and legal entity
252 --are fetched from ja_cn_voucher_number
253 --The new one is
254 ----------------------------------------------------
255 --AND jcvn.je_header_id = jl.je_header_id
256 --AND jcvn.je_line_number = jl.je_line_num
257 --AND jcvn.legal_entity_id = pn_le_id;
258 ----------------------------------------------------
259 WHERE jl.period_name in gp.period_name
260 AND gl.ledger_id = pn_ledger_id
261 AND gl.period_set_name = gp.period_set_name
262 AND jl.period_name in gp.period_name
263 AND gp.period_year = pv_accounting_year
264 --Add the follow two conditions by Jianchao Chi to change the logic of legal entity, 05-JAN-2011
265 AND jcvn.je_header_id = jl.je_header_id
266 AND jcvn.je_line_number = jl.je_line_num
267 --Comment by Jianchao Chi to change the logic of legal entity, 05-JAN-2011
268 --AND jcjl.legal_entity_id = pn_le_id
269 AND jcvn.legal_entity_id = pn_le_id);
270
271 --logging the variables
272 IF (ln_statement_level >= ln_dbg_level)
273 THEN
274 FND_LOG.STRING(ln_statement_level,
275 lv_procedure_name,
276 'Journal number is:' || ln_journal_number);
277 END IF; --(ln_statement_level >= ln_dbg_level)
278 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
279 '.variable:' ||
280 'Journal number is:' || ln_journal_number);
281
282 IF ln_journal_number > 0
283 THEN
284 OPEN user_defined_record_value_cur(pn_ledger_id
285 ,pn_le_id
286 ,pv_accounting_year);
287 LOOP
288 FETCH user_defined_record_value_cur INTO lv_sub_segment_code,lv_source_code,lv_context_code;
289 EXIT WHEN user_defined_record_value_cur%NOTFOUND;
290
291 -- Get User-Defined Record Number
292 IF length(lv_sub_segment_code) = 8
293 THEN
294 SELECT substr(lv_sub_segment_code,8,1)
295 INTO ln_record_number
296 FROM dual;
297 ELSE
298 IF length(lv_sub_segment_code) = 9
299 THEN
300 SELECT substr(lv_sub_segment_code,8,2)
301 INTO ln_record_number
302 FROM dual;
303 END IF;
304 END IF;
305
306 IF lv_source_code = 'COA'
307 THEN
308 -- Get COA value name and record description
309 OPEN coa_value_name_desc_cur(pn_ledger_id
310 ,pn_le_id
311 ,pv_accounting_year);
312 LOOP
313 FETCH coa_value_name_desc_cur INTO lv_record_value_number,lv_record_value_desc;
314 EXIT WHEN coa_value_name_desc_cur%NOTFOUND;
315
316 Ja_Cn_Utility.Add_Sub_Root_Node( pv_sub_root_tag_name => 'USER_DEFINED_RECORD_VALUE'
317 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_START
318 );
319 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'RECORD_NUMBER'
320 , pv_text_node_value => ln_record_number
321 );
322 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'RECORD_VALUE_NUMBER'
323 , pv_text_node_value => lv_record_value_number
324 );
325 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'RECORD_VALUE_NAME'
326 , pv_text_node_value => lv_record_value_number
327 );
328 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'RECORD_VALUE_DESCRIPTION'
329 , pv_text_node_value => lv_record_value_desc
330 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
331 , pv_required => Ja_Cn_Utility.GV_REQUIRED_NO
332 );
333 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'PARENT_RECORD_VALUE_NUMBER'
334 , pv_text_node_value => null
335 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
336 , pv_required => Ja_Cn_Utility.GV_REQUIRED_NO
337 );
338 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'LEVEL'
339 , pv_text_node_value => '1'
340 );
341
342 Ja_Cn_Utility.Add_Sub_Root_Node( pv_sub_root_tag_name => 'USER_DEFINED_RECORD_VALUE'
343 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_END
344 );
345
346 END LOOP;
347 CLOSE coa_value_name_desc_cur;
348
349 END IF;--(lv_source_code = 'COA)
350
351 IF lv_source_code = 'PROJECT MODULE'
352 THEN
353 -- Get project value name and record description
354 OPEN project_value_name_desc_cur(pn_ledger_id
355 ,pn_le_id
356 ,pv_accounting_year);
357 LOOP
358 FETCH project_value_name_desc_cur INTO lv_record_value_number,lv_record_value_name,lv_record_value_desc;
359 EXIT WHEN project_value_name_desc_cur%NOTFOUND;
360
361 Ja_Cn_Utility.Add_Sub_Root_Node( pv_sub_root_tag_name => 'USER_DEFINED_RECORD_VALUE'
362 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_START
363 );
364 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'RECORD_NUMBER'
365 , pv_text_node_value => ln_record_number
366 );
367 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'RECORD_VALUE_NUMBER'
368 , pv_text_node_value => lv_record_value_number
369 );
370 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'RECORD_VALUE_NAME'
371 , pv_text_node_value => lv_record_value_name
372 );
373 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'RECORD_VALUE_DESCRIPTION'
374 , pv_text_node_value => lv_record_value_desc
375 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
376 , pv_required => Ja_Cn_Utility.GV_REQUIRED_NO
377 );
378 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'PARENT_RECORD_VALUE_NUMBER'
379 , pv_text_node_value => null
380 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
381 , pv_required => Ja_Cn_Utility.GV_REQUIRED_NO
382 );
383 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'LEVEL'
384 , pv_text_node_value => '1'
385 );
386 Ja_Cn_Utility.Add_Sub_Root_Node( pv_sub_root_tag_name => 'USER_DEFINED_RECORD_VALUE'
387 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_END
388 );
389 END LOOP;
390 CLOSE project_value_name_desc_cur;
391 END IF; --(lv_source_code = 'Project Module')
392
393 END LOOP;
394 CLOSE user_defined_record_value_cur;
395 END IF; --(ln_journal_number > 0)
396
397 --logging for debug
398 IF (ln_proc_level>=ln_dbg_level)
399 THEN
400 FND_LOG.STRING( ln_proc_level
401 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
402 , 'Exit procedure'
403 );
404 END IF; -- (ln_proc_level>=ln_dbg_level)
405
406 EXCEPTION
407 WHEN OTHERS THEN
408 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
409 THEN
410 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
411 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
412 '.Other_Exception ',
413 SQLCODE || SQLERRM);
414 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
415 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
416 END Add_User_Defined_Record_Value;
417
418 END JA_CN_UDRV_EXPORT;
419