[Home] [Help]
PACKAGE BODY: APPS.JA_CN_SIOA_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_SIOA_EXPORT_PKG AS
2 --$Header: JACNSOAB.pls 120.1 2010/04/06 06:58:50 wuwu noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNSOAB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| To export Subsidiary Account information which is |
13 --| subsidizing natural account. |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE Add_Sub_Item_Of_Account |
17 --| |
18 --| |
19 --| HISTORY |
20 --| 02-Mar-2010 Chaoqun Wu Created |
21 --+======================================================================*/
22
23 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_SIOA_EXPORT_PROG';
24
25 --==========================================================================
26 -- PROCEDURE NAME:
27 --
28 -- Add_Sub_Item_Of_Account Public
29 --
30 -- DESCRIPTION:
31 --
32 -- This procedure is To export Subsidiary Account information which is
33 -- subsidizing natural account.
34 --
35 -- PARAMETERS:
36 -- In: pn_ledger_id ledger id
37 -- pn_legal_entity_id legal entity id
38 -- pn_chart_of_account_id chart of account id
39 -- pv_accounting_year accounting year
40 --
41 -- DESIGN REFERENCES:
42 -- GL_Chaoqun.doc
43 --
44 -- CHANGE HISTORY:
45 -- 02-Mar-2010 Chaoqun Wu created
46 --==========================================================================
47
48 PROCEDURE Add_Sub_Item_Of_Account
49 (pn_ledger_id NUMBER
50 ,pn_legal_entity_id NUMBER
51 ,pn_chart_of_account_id NUMBER
52 ,pv_accounting_year VARCHAR2
53 /*,pv_period_from VARCHAR2
54 ,pv_period_to VARCHAR2*/
55 )
56 IS
57 lv_procedure_name VARCHAR2(40) := 'Add_Sub_Item_Of_Account';
58 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
59 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
60 NO_DATA EXCEPTION;
61
62 --Get account number from itemized balance table
63 --The data is fetched by ledger id and legal entity id
64 CURSOR account_number_cur
65 (pn_ledger_id NUMBER
66 ,pn_legal_entity_id NUMBER
67 ,pv_accounting_year VARCHAR2
68 /*,pv_period_from VARCHAR2
69 ,pv_period_to VARCHAR2*/)
70 IS
71 SELECT DISTINCT JCAB.ACCOUNT_SEGMENT --Account Number
72 FROM JA_CN_ACCOUNT_BALANCES JCAB,
73 GL_PERIODS GP,
74 GL_LEDGERS LED
75 WHERE JCAB.LEDGER_ID = pn_ledger_id --parameter: pn_ledger_id
76 AND JCAB.LEGAL_ENTITY_ID = pn_legal_entity_id --parameter: pn_legal_entity_id
77 AND JCAB.PERIOD_NAME = GP.PERIOD_NAME
78 AND LED.LEDGER_ID = JCAB.LEDGER_ID
79 AND LED.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
80 AND LED.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
81 AND GP.PERIOD_YEAR = TO_NUMBER(pv_accounting_year) --parameter: pv_accounting_year
82 /* AND GP.START_DATE BETWEEN
83 (SELECT START_DATE
84 FROM GL_PERIODS GP
85 WHERE LED.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
86 AND LED.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
87 AND GP.PERIOD_NAME = pv_period_from) --parameter: pv_period_from
88 AND (SELECT START_DATE
89 FROM GL_PERIODS GP
90 WHERE LED.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
91 AND LED.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
92 AND GP.PERIOD_NAME = pv_period_to) --parameter: pv_period_to*/
93 ORDER BY JCAB.ACCOUNT_SEGMENT;
94
95 --Get account relevant subsidiary segment from itemized balance table
96 --The data is fetched by ledger id and legal entity id
97 CURSOR sub_segment_cur
98 (pn_ledger_id NUMBER
99 ,pn_legal_entity_id NUMBER
100 ,pv_accounting_year VARCHAR2
101 ,lv_account_number VARCHAR2
102 /*,pv_period_from VARCHAR2
103 ,pv_period_to VARCHAR2*/)
104 IS
105 SELECT MAX(JCAB.SEGMENT1) SEGMENT1
106 ,MAX(JCAB.SEGMENT2) SEGMENT2
107 ,MAX(JCAB.SEGMENT3) SEGMENT3
108 ,MAX(JCAB.SEGMENT4) SEGMENT4
109 ,MAX(JCAB.SEGMENT5) SEGMENT5
110 ,MAX(JCAB.SEGMENT6) SEGMENT6
111 ,MAX(JCAB.SEGMENT7) SEGMENT7
112 ,MAX(JCAB.SEGMENT8) SEGMENT8
113 ,MAX(JCAB.SEGMENT9) SEGMENT9
114 ,MAX(JCAB.SEGMENT10) SEGMENT10
115 ,MAX(JCAB.SEGMENT11) SEGMENT11
116 ,MAX(JCAB.SEGMENT12) SEGMENT12
117 ,MAX(JCAB.SEGMENT13) SEGMENT13
118 ,MAX(JCAB.SEGMENT14) SEGMENT14
119 ,MAX(JCAB.SEGMENT15) SEGMENT15
120 ,MAX(JCAB.SEGMENT16) SEGMENT16
121 ,MAX(JCAB.SEGMENT17) SEGMENT17
122 ,MAX(JCAB.SEGMENT18) SEGMENT18
123 ,MAX(JCAB.SEGMENT19) SEGMENT19
124 ,MAX(JCAB.SEGMENT20) SEGMENT20
125 ,MAX(JCAB.SEGMENT21) SEGMENT21
126 ,MAX(JCAB.SEGMENT22) SEGMENT22
127 ,MAX(JCAB.SEGMENT23) SEGMENT23
128 ,MAX(JCAB.SEGMENT24) SEGMENT24
129 ,MAX(JCAB.SEGMENT25) SEGMENT25
130 ,MAX(JCAB.SEGMENT26) SEGMENT26
131 ,MAX(JCAB.SEGMENT27) SEGMENT27
132 ,MAX(JCAB.SEGMENT28) SEGMENT28
133 ,MAX(JCAB.SEGMENT29) SEGMENT29
134 ,MAX(JCAB.SEGMENT30) SEGMENT30
135 FROM JA_CN_ACCOUNT_BALANCES JCAB,
136 GL_PERIODS GP,
137 GL_LEDGERS LED
138 WHERE JCAB.LEDGER_ID = pn_ledger_id --parameter: pn_ledger_id
139 AND JCAB.LEGAL_ENTITY_ID = pn_legal_entity_id --parameter: pn_legal_entity_id
140 AND JCAB.PERIOD_NAME = GP.PERIOD_NAME
141 AND LED.LEDGER_ID = JCAB.LEDGER_ID
142 AND LED.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
143 AND LED.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
144 AND GP.PERIOD_YEAR = TO_NUMBER(pv_accounting_year) --parameter: pv_accounting_year
145 AND JCAB.ACCOUNT_SEGMENT = lv_account_number --Account Number
146 /* AND GP.START_DATE BETWEEN
147 (SELECT START_DATE
148 FROM GL_PERIODS GP
149 WHERE LED.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
150 AND LED.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
151 AND GP.PERIOD_NAME = pv_period_from) --parameter: pv_period_from
152 AND (SELECT START_DATE
153 FROM GL_PERIODS GP
154 WHERE LED.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
155 AND LED.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
156 AND GP.PERIOD_NAME = pv_period_to) --parameter: pv_period_to*/
157 ;
158
159 --Get subsidiary item information
160 --Only those subsidiary item having values in balance table and defined in Subsidiary Account Source form could be considerred.
161 CURSOR sub_item_detail_cur
162 (pn_chart_of_account_id NUMBER
163 ,lv_segment1 VARCHAR2
164 ,lv_segment2 VARCHAR2
165 ,lv_segment3 VARCHAR2
166 ,lv_segment4 VARCHAR2
167 ,lv_segment5 VARCHAR2
168 ,lv_segment6 VARCHAR2
169 ,lv_segment7 VARCHAR2
170 ,lv_segment8 VARCHAR2
171 ,lv_segment9 VARCHAR2
172 ,lv_segment10 VARCHAR2
173 ,lv_segment11 VARCHAR2
174 ,lv_segment12 VARCHAR2
175 ,lv_segment13 VARCHAR2
176 ,lv_segment14 VARCHAR2
177 ,lv_segment15 VARCHAR2
178 ,lv_segment16 VARCHAR2
179 ,lv_segment17 VARCHAR2
180 ,lv_segment18 VARCHAR2
181 ,lv_segment19 VARCHAR2
182 ,lv_segment20 VARCHAR2
183 ,lv_segment21 VARCHAR2
184 ,lv_segment22 VARCHAR2
185 ,lv_segment23 VARCHAR2
186 ,lv_segment24 VARCHAR2
187 ,lv_segment25 VARCHAR2
188 ,lv_segment26 VARCHAR2
189 ,lv_segment27 VARCHAR2
190 ,lv_segment28 VARCHAR2
191 ,lv_segment29 VARCHAR2
192 ,lv_segment30 VARCHAR2)
193 IS
194 SELECT *
195 --COA source
196 FROM (SELECT DISTINCT TO_NUMBER(SUBSTR(SUBSIDIARY_SEGMENT_CODE,
197 8)) AS SUB_ITEM_NUM, --Subsidiary Item Number
198 FIFS.SEGMENT_NAME AS SUB_ITEM_NAME, --Subsidiary Item Name
199 FIFS.SEGMENT_NAME AS CORR_RECORD, --Corresponding Record
200 FFVS.DESCRIPTION AS SUB_ITEM_DESC --Subsidiary Item Description
201 FROM FND_ID_FLEX_SEGMENTS FIFS,
202 FND_SEGMENT_ATTRIBUTE_VALUES FSAV,
203 GL_LEDGERS LED,
204 FND_FLEX_VALUE_SETS FFVS,
205 JA_CN_SUB_ACC_MAPPING SAM
206 WHERE FIFS.ID_FLEX_NUM = FSAV.ID_FLEX_NUM
207 AND FIFS.APPLICATION_COLUMN_NAME =
208 FSAV.APPLICATION_COLUMN_NAME
209 AND (FSAV.SEGMENT_ATTRIBUTE_TYPE <> 'GL_GLOBAL' OR
210 (FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_GLOBAL' AND
211 NOT EXISTS
212 (SELECT *
213 FROM FND_SEGMENT_ATTRIBUTE_VALUES FSAV1
214 WHERE FSAV1.APPLICATION_ID = FSAV.APPLICATION_ID
215 AND FSAV1.ID_FLEX_CODE = FSAV.ID_FLEX_CODE
216 AND FSAV1.ID_FLEX_NUM = FSAV.ID_FLEX_NUM
217 AND FSAV1.APPLICATION_COLUMN_NAME =
218 FSAV.APPLICATION_COLUMN_NAME
219 AND FSAV1.ATTRIBUTE_VALUE = 'Y'
220 AND FSAV1.SEGMENT_ATTRIBUTE_TYPE <> 'GL_GLOBAL')))
221 AND FSAV.ATTRIBUTE_VALUE = 'Y'
222 AND FIFS.APPLICATION_ID = 101
223 AND FSAV.ID_FLEX_CODE = FIFS.ID_FLEX_CODE
224 AND FSAV.ID_FLEX_CODE = 'GL#'
225 AND FIFS.APPLICATION_ID = FSAV.APPLICATION_ID
226 AND LED.CHART_OF_ACCOUNTS_ID = FIFS.ID_FLEX_NUM
227 AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
228 AND LED.CHART_OF_ACCOUNTS_ID = pn_chart_of_account_id --parameter: pn_chart_of_account_id
229 AND SAM.CHART_OF_ACCOUNTS_ID = LED.CHART_OF_ACCOUNTS_ID
230 AND SAM.CONTEXT_CODE = FSAV.APPLICATION_COLUMN_NAME
231 AND SAM.SOURCES_CODE = 'COA'
232 UNION
233 --SLA source
234 SELECT TO_NUMBER(SUBSTR(SUBSIDIARY_SEGMENT_CODE,
235 8)) AS SUB_ITEM_NUM, --Subsidiary Item Number
236 FLV1.MEANING AS SUB_ITEM_NAME, --Subsidiary Item Name
237 FLV2.MEANING AS CORR_RECORD, --Corresponding Record
238 FLV1.MEANING AS SUB_ITEM_DESC --Subsidiary Item Description
239 FROM FND_LOOKUP_VALUES FLV1,
240 FND_LOOKUP_VALUES FLV2,
241 JA_CN_SUB_ACC_MAPPING SAM
242 WHERE FLV1.LOOKUP_TYPE = 'JA_CN_SUB_ACC_SLA_CONTEXT'
243 AND FLV2.LOOKUP_TYPE = 'JA_CN_SLA_CORR_RECO'
244 AND DECODE(FLV1.LOOKUP_CODE, 'CUSTOMER', 'CUSTOMER RECORD',
245 'SUPPLIER', 'SUPPLIER RECORD',
246 'EMPLOYEE_SUPPLIER', 'EMPLOYEE RECORD')
247 = FLV2.LOOKUP_CODE
248 AND SAM.SOURCES_CODE = 'SLA'
249 AND SAM.CHART_OF_ACCOUNTS_ID = pn_chart_of_account_id --parameter: pn_chart_of_account_id
250 AND SAM.CONTEXT_CODE = FLV1.LOOKUP_CODE
251 AND FLV1.LANGUAGE = USERENV('LANG')
252 AND FLV2.LANGUAGE = USERENV('LANG')
253 UNION
254 --Project module source
255 SELECT TO_NUMBER(SUBSTR(SUBSIDIARY_SEGMENT_CODE,
256 8)) AS SUB_ITEM_NUM, --Subsidiary Item Number
257 XAHB.ANALYTICAL_CRITERION_CODE AS SUB_ITEM_NAME, --Subsidiary Item Name
258 XAHB.ANALYTICAL_CRITERION_CODE AS CORR_RECORD, --Corresponding Record
259 XAHT.DESCRIPTION AS SUB_ITEM_DESC --Subsidiary Item Description
260 FROM XLA_ANALYTICAL_HDRS_B XAHB,
261 XLA_ANALYTICAL_HDRS_TL XAHT,
262 JA_CN_SUB_ACC_MAPPING SAM
263 WHERE XAHB.ANALYTICAL_CRITERION_CODE = 'PROJECT_NUMBER'
264 AND XAHB.ANALYTICAL_CRITERION_CODE =
265 XAHT.ANALYTICAL_CRITERION_CODE
266 AND XAHB.ANALYTICAL_CRITERION_TYPE_CODE =
267 XAHT.ANALYTICAL_CRITERION_TYPE_CODE
268 AND XAHB.AMB_CONTEXT_CODE = XAHT.AMB_CONTEXT_CODE
269 AND XAHB.ANALYTICAL_CRITERION_CODE = SAM.CONTEXT_CODE
270 AND SAM.SOURCES_CODE = 'PROJECT MODULE'
271 AND SAM.CHART_OF_ACCOUNTS_ID = pn_chart_of_account_id --parameter: pn_chart_of_account_id
272 AND XAHT.LANGUAGE = USERENV('LANG'))
273 WHERE DECODE(SUB_ITEM_NUM, 1, lv_segment1, 2, lv_segment2, 3, lv_segment3, 4, lv_segment4, 5, lv_segment5,
274 6, lv_segment6, 7, lv_segment7, 8, lv_segment8, 9, lv_segment9, 10, lv_segment10,
275 11, lv_segment11, 12, lv_segment12, 13, lv_segment13, 14, lv_segment14, 15, lv_segment15,
276 16, lv_segment16, 17, lv_segment17, 18, lv_segment18, 19, lv_segment19, 20, lv_segment20,
277 21, lv_segment21, 22, lv_segment22, 23, lv_segment23, 24, lv_segment24, 25, lv_segment25,
278 26, lv_segment26, 27, lv_segment27, 28, lv_segment28, 29, lv_segment29, 30, lv_segment30,
279 NULL) IS NOT NULL
280 ORDER BY SUB_ITEM_NUM;
281
282 BEGIN
283 --logging for debug
284 IF (ln_proc_level >= ln_dbg_level)
285 THEN
286 FND_LOG.STRING(ln_proc_level,
287 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
288 '.begin',
289 'Enter procedure');
290 -- logging the parameters
291 FND_LOG.STRING(ln_proc_level,
292 lv_procedure_name ||
293 '.parameters',
294 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
295 'pn_ledger_id=' || pn_ledger_id || ',' ||
296 'pv_accounting_year=' || pv_accounting_year || ',' ||
297 'pn_chart_of_account_id=' || pn_chart_of_account_id);
298 END IF; --l_proc_level>=l_dbg_level
299 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
300 '.parameters:'||
301 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
302 'pn_ledger_id=' || pn_ledger_id || ',' ||
303 'pv_accounting_year=' || pv_accounting_year || ',' ||
304 'pn_chart_of_account_id=' || pn_chart_of_account_id);
305
306 FOR v_acc_row IN account_number_cur(pn_ledger_id
307 ,pn_legal_entity_id
308 ,pv_accounting_year
309 /*,pv_period_from
310 ,pv_period_to*/)
311 LOOP
312 FOR v_seg_row IN sub_segment_cur(pn_ledger_id
313 ,pn_legal_entity_id
314 ,pv_accounting_year
315 ,v_acc_row.Account_Segment
316 /*,pv_period_from
317 ,pv_period_to*/)
318 LOOP
319 --Add subsidiary item information
320 FOR v_sub_row IN sub_item_detail_cur(pn_chart_of_account_id
321 ,v_seg_row.segment1
322 ,v_seg_row.segment2
323 ,v_seg_row.segment3
324 ,v_seg_row.segment4
325 ,v_seg_row.segment5
326 ,v_seg_row.segment6
327 ,v_seg_row.segment7
328 ,v_seg_row.segment8
329 ,v_seg_row.segment9
330 ,v_seg_row.segment10
331 ,v_seg_row.segment11
332 ,v_seg_row.segment12
333 ,v_seg_row.segment13
334 ,v_seg_row.segment14
335 ,v_seg_row.segment15
336 ,v_seg_row.segment16
337 ,v_seg_row.segment17
338 ,v_seg_row.segment18
339 ,v_seg_row.segment19
340 ,v_seg_row.segment20
341 ,v_seg_row.segment21
342 ,v_seg_row.segment22
343 ,v_seg_row.segment23
344 ,v_seg_row.segment24
345 ,v_seg_row.segment25
346 ,v_seg_row.segment26
347 ,v_seg_row.segment27
348 ,v_seg_row.segment28
349 ,v_seg_row.segment29
350 ,v_seg_row.segment30)
351 LOOP
352 Ja_Cn_Utility.Add_Sub_Root_Node('SUBSIDIARY_ITEM_OF_ACCOUNT'
353 ,Ja_Cn_Utility.GV_TAG_TYPE_START
354 );
355 Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER'
356 ,v_acc_row.ACCOUNT_SEGMENT
357 );
358 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NUMBER'
359 ,v_sub_row.SUB_ITEM_NUM
360 ,Ja_Cn_Utility.GV_TYPE_NUMBER
361 );
362 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_NAME'
363 ,v_sub_row.SUB_ITEM_NAME
364 );
365 Ja_Cn_Utility.Add_Child_Node('CORRESPONDING_RECORD'
366 ,v_sub_row.CORR_RECORD
367 );
368 Ja_Cn_Utility.Add_Child_Node('SUBSIDIARY_ITEM_DESCRIPTION'
369 ,v_sub_row.SUB_ITEM_DESC
370 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
371 ,Ja_Cn_Utility.GV_REQUIRED_NO
372 );
373 Ja_Cn_Utility.Add_Sub_Root_Node('SUBSIDIARY_ITEM_OF_ACCOUNT'
374 ,Ja_Cn_Utility.GV_TAG_TYPE_END
375 );
376 END LOOP;--Add subsidiary item infomation
377 END LOOP; --Get subsidiary segment
378 END LOOP;--Get account number
379
380 --logging for debug
381 IF (ln_proc_level >= ln_dbg_level)
382 THEN
383 FND_LOG.STRING(ln_proc_level,
384 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
385 'Exit procedure');
386 END IF; -- (ln_proc_level>=ln_dbg_level)
387
388 EXCEPTION
389 WHEN NO_DATA THEN
390 --logging for debug
391 IF (ln_proc_level >= ln_dbg_level)
392 THEN
393 FND_LOG.STRING(ln_proc_level,
394 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
395 'exception',
396 'No data for Subsidiary Item of Account');
397 END IF; --l_proc_level>=l_dbg_level
398 WHEN OTHERS THEN
399 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
400 THEN
401 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
402 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
403 '.Other_Exception ',
404 SQLCODE || SQLERRM);
405 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
406 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
407
408 END Add_Sub_Item_Of_Account;
409
410 END JA_CN_SIOA_EXPORT_PKG;