[Home] [Help]
PACKAGE BODY: APPS.JA_CN_UDR_EXPORT
Source
1 PACKAGE BODY JA_CN_UDR_EXPORT AS
2 --$Header: JACNUDRB.pls 120.5 2011/05/04 06:43:58 choli noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNUDRB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| To export User-Defined Records. |
13 --| |
14 --| PROCEDURE LIST |
15 --| PROCEDURE Add_User_Defined_Records |
16 --| |
17 --| |
18 --| HISTORY |
19 --| 10-Mar-2010 Shujuan Yan Created |
20 --| 17-Mar-2010 Qingyi Wang Modify the code according to |
21 --| the changes from Ja_Cn_Utility |
22 --| 05-Jan-2011 Jianchao Chi Updated for the new solution to change |
23 --| the logic of Voucher Number and Legal |
24 --| Entity |
25 --| 04-May-2011 Chongwu Li Updated, fix bug 12392517 |
26 --+======================================================================*/
27
28 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_UDR_EXPORT';
29
30 --==========================================================================
31 -- PROCEDURE NAME:
32 --
33 -- Add_User_Defined_Records Public
34 --
35 -- DESCRIPTION:
36 --
37 -- This procedure is used to export user defined records in Shared
38 -- Information
39 --
40 -- PARAMETERS:
41 -- In: pn_coa_id NUMBER identifier of chart of account
42 -- pn_le_id NUMBER identifier of legal entity
43 -- pn_ledger_id NUMBER identifier of ledger
44 -- pv_accounting_year VARCHAR2 accounting year
45 -- DESIGN REFERENCES:
46 -- GL_Shujuan.doc
47 --
48 -- CHANGE HISTORY:
49 -- 10-Mar-2010 Shujuan yan created
50 -- 05-Jan-2011 Jianchao Chi Update the new solution to change the logic of legal entity
51 --| 04-May-2011 Chongwu Li Updated, fix bug 12392517 |
52 --
53 --==========================================================================
54 PROCEDURE Add_User_Defined_Records(pn_coa_id NUMBER,
55 pn_le_id NUMBER,
56 pn_ledger_id NUMBER,
57 pv_accounting_year VARCHAR2) IS
58 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
59 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
60 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
61 lv_procedure_name VARCHAR2(40) := 'Add_User_Defined_Records';
62 ln_journal_number NUMBER;
63 lv_sub_segment_code VARCHAR2(20);
64 lv_source_code VARCHAR2(30);
65 lv_context_code VARCHAR2(30);
66 lv_record_description VARCHAR2(240);
67 lv_record_name VARCHAR2(60);
68 ln_record_number NUMBER;
69 NO_DATA EXCEPTION;
70
71 CURSOR user_defined_records_cur(pn_ledger_id NUMBER,
72 pn_le_id NUMBER,
73 pv_accounting_year VARCHAR2) IS
74 SELECT subsidiary_segment_code, sources_code, context_code
75 FROM ja_cn_sub_acc_mapping
76 WHERE chart_of_accounts_id = pn_coa_id
77 AND sources_code in ('COA', 'PROJECT MODULE')
78 --choli add to fix bug 12392517
79 ORDER BY subsidiary_segment_code;
80
81 BEGIN
82 --logging for debug
83 IF (ln_proc_level >= ln_dbg_level) THEN
84 FND_LOG.STRING(ln_proc_level,
85 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
86 '.begin',
87 'Enter procedure');
88
89 -- logging the parameters
90 FND_LOG.STRING(ln_proc_level,
91 lv_procedure_name || '.parameters',
92 'pn_legal_entity_id=' || pn_le_id || ',' ||
93 'pn_ledger_id=' || pn_ledger_id || ',' ||
94 'pv_accounting_year=' || pv_accounting_year || ',' ||
95 'pn_coa_id=' || pn_coa_id);
96 END IF; --l_proc_level>=l_dbg_level
97
98 FND_FILE.put_line(FND_FILE.log,
99 lv_procedure_name || '.parameters:' ||
100 'pn_legal_entity_id=' || pn_le_id || ',' ||
101 'pn_ledger_id=' || pn_ledger_id || ',' ||
102 'pv_accounting_year=' || pv_accounting_year || ',' ||
103 'pn_coa_id=' || pn_coa_id);
104
105 SELECT COUNT(*)
106 INTO ln_journal_number
107 FROM (SELECT DISTINCT *--Add by Jianchao Chi, 10-Jan-2011, for one hearder may has two same
108 FROM ja_cn_journal_lines jl, --line numbers in ja_cn_journal_lines.
109 gl_ledgers gl,
110 gl_periods gp,
111 ja_cn_voucher_number jcvn
112 --Update by Jianchao Chi, for cnaov2 upgrade
113 --Add ja_cn_voucher_number table, voucher number and legal entity
114 --are fetched from ja_cn_voucher_number
115 --The new one is
116 ----------------------------------------------------
117 --AND jcvn.je_header_id = jl.je_header_id
118 --AND jcvn.je_line_number = jl.je_line_num
119 --AND jcvn.legal_entity_id = pn_le_id;
120 ----------------------------------------------------
121 WHERE jl.period_name in gp.period_name
122 AND gl.ledger_id = pn_ledger_id
123 AND gl.period_set_name = gp.period_set_name
124 AND jl.period_name in gp.period_name
125 AND gp.period_year = pv_accounting_year
126 --Add the follow two conditions by Jianchao Chi to change the logic of legal entity, 05-JAN-2011
127 AND jcvn.je_header_id = jl.je_header_id
128 AND jcvn.je_line_number = jl.je_line_num
129 --Comment by Jianchao Chi to change the logic of legal entity, 05-JAN-2011
130 --AND jcjl.legal_entity_id = pn_le_id
131 AND jcvn.legal_entity_id = pn_le_id);
132
133 --logging the variables
134 IF (ln_statement_level >= ln_dbg_level) THEN
135 FND_LOG.STRING(ln_statement_level,
136 lv_procedure_name,
137 'Journal number is:' || ln_journal_number);
138 END IF; --(ln_statement_level >= ln_dbg_level)
139 FND_FILE.put_line(FND_FILE.log,
140 lv_procedure_name || '.variable:' ||
141 'Journal number is:' || ln_journal_number);
142
143 IF ln_journal_number > 0 THEN
144 OPEN user_defined_records_cur(pn_ledger_id,
145 pn_le_id,
146 pv_accounting_year);
147 LOOP
148 FETCH user_defined_records_cur
149 INTO lv_sub_segment_code, lv_source_code, lv_context_code;
150 EXIT WHEN user_defined_records_cur%NOTFOUND;
151
152 Ja_Cn_Utility.Add_Sub_Root_Node(pv_sub_root_tag_name => 'USER_DEFINED_RECORDS',
153 pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_START);
154
155 -- Get User-Defined Record Number
156 IF length(lv_sub_segment_code) = 8 THEN
157 SELECT substr(lv_sub_segment_code, 8, 1)
158 INTO ln_record_number
159 FROM dual;
160 ELSE
161 IF length(lv_sub_segment_code) = 9 THEN
162 SELECT substr(lv_sub_segment_code, 8, 2)
163 INTO ln_record_number
164 FROM dual;
165 END IF;
166 END IF;
167
168 /*FND_FILE.put_line( FND_FILE.log
169 , to_char(ln_record_number)
170 );*/
171
172 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'RECORD_NUMBER',
173 pv_text_node_value => ln_record_number);
174
175 -- Get Record Description and Record Name when source is COA
176 IF lv_source_code = 'COA' THEN
177 --Get the COA record name from context field.
178 SELECT DISTINCT FIFS.SEGMENT_NAME
179 into lv_record_name
180 FROM FND_ID_FLEX_SEGMENTS FIFS,
181 FND_SEGMENT_ATTRIBUTE_VALUES FSAV,
182 GL_LEDGERS LED,
183 FND_FLEX_VALUE_SETS FFVS,
184 JA_CN_SUB_ACC_MAPPING SAM
185 WHERE FIFS.ID_FLEX_NUM = FSAV.ID_FLEX_NUM
186 AND FIFS.APPLICATION_COLUMN_NAME =
187 FSAV.APPLICATION_COLUMN_NAME
188 AND (FSAV.SEGMENT_ATTRIBUTE_TYPE <> 'GL_GLOBAL' OR
189 (FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_GLOBAL' AND NOT EXISTS
190 (SELECT *
191 FROM FND_SEGMENT_ATTRIBUTE_VALUES FSAV1
192 WHERE FSAV1.APPLICATION_ID = FSAV.APPLICATION_ID
193 AND FSAV1.ID_FLEX_CODE = FSAV.ID_FLEX_CODE
194 AND FSAV1.ID_FLEX_NUM = FSAV.ID_FLEX_NUM
195 AND FSAV1.APPLICATION_COLUMN_NAME =
196 FSAV.APPLICATION_COLUMN_NAME
197 AND FSAV1.ATTRIBUTE_VALUE = 'Y'
198 AND FSAV1.SEGMENT_ATTRIBUTE_TYPE <> 'GL_GLOBAL')))
199 AND FSAV.ATTRIBUTE_VALUE = 'Y'
200 AND FIFS.APPLICATION_ID = 101
201 AND FSAV.ID_FLEX_CODE = FIFS.ID_FLEX_CODE
202 AND FSAV.ID_FLEX_CODE = 'GL#'
203 AND FIFS.APPLICATION_ID = FSAV.APPLICATION_ID
204 AND LED.CHART_OF_ACCOUNTS_ID = FIFS.ID_FLEX_NUM
205 AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
206 AND LED.CHART_OF_ACCOUNTS_ID = pn_coa_id --parameter: pn_chart_of_account_id
207 AND SAM.CHART_OF_ACCOUNTS_ID = LED.CHART_OF_ACCOUNTS_ID
208 AND SAM.CONTEXT_CODE = FSAV.APPLICATION_COLUMN_NAME
209 AND SAM.SOURCES_CODE = 'COA'
210 AND FSAV.APPLICATION_COLUMN_NAME = lv_context_code;
211
212 BEGIN
213 SELECT ffvs.description
214 INTO lv_record_description
215 FROM fnd_id_flex_segments fifs, fnd_flex_value_sets ffvs
216 WHERE fifs.id_flex_num = pn_coa_id
217 AND fifs.id_flex_code = 'GL#'
218 AND fifs.application_column_name = lv_context_code
219 AND fifs.flex_value_set_id = ffvs.flex_value_set_id;
220 EXCEPTION
221 WHEN NO_DATA_FOUND THEN
222 lv_record_description := '';
223 lv_record_name := '';
224 END;
225 END IF;
226
227 -- Get Record Description and Record Name when source is Project Module
228 IF lv_source_code = 'PROJECT MODULE' THEN
229 lv_record_name := lv_context_code;
230 BEGIN
231 SELECT description
232 INTO lv_record_description
233 FROM xla_analytical_hdrs_tl
234 WHERE analytical_criterion_code = 'PROJECT_NUMBER'
235 AND LANGUAGE = Userenv('LANG');
236 EXCEPTION
237 WHEN NO_DATA_FOUND THEN
238 lv_record_description := '';
239 END;
240 END IF;
241 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'RECORD_NAME',
242 pv_text_node_value => lv_record_name);
243 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'RECORD_DESCRIPTION',
244 pv_text_node_value => lv_record_description,
245 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
246 pv_required => Ja_Cn_Utility.GV_REQUIRED_NO);
247 Ja_Cn_Utility.Add_Fixed_Child_Node(pv_child_tag_name => 'IS_MULTIPLE_LEVEL',
248 pv_text_node_value => '0',
249 pn_fixed_length => 1);
250 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'RECORD_CODE_CONVENTION',
251 pv_text_node_value => null,
252 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
253 pv_required => Ja_Cn_Utility.GV_REQUIRED_NO);
254 Ja_Cn_Utility.Add_Sub_Root_Node(pv_sub_root_tag_name => 'USER_DEFINED_RECORDS',
255 pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_END);
256 END LOOP;
257 CLOSE user_defined_records_cur;
258 END IF;
259
260 --logging for debug
261 IF (ln_proc_level >= ln_dbg_level) THEN
262 FND_LOG.STRING(ln_proc_level,
263 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
264 'Exit procedure');
265 END IF; -- (ln_proc_level>=ln_dbg_level)
266
267 END Add_User_Defined_Records;
268
269 END JA_CN_UDR_EXPORT;
270