[Home] [Help]
PACKAGE BODY: APPS.JA_CN_PS_FA_BI_EXP_PKG
Source
1 PACKAGE BODY JA_CN_PS_FA_BI_EXP_PKG AS
2 --$Header: JACNFAAB.pls 120.0 2010/08/30 05:00:35 wuliu noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNFAAB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export Basic Account Information |
13 --| of Fixed Asset for Public Sector |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE Add_Basic_Information |
17 --| |
18 --| |
19 --| HISTORY |
20 --| 10/AUG/2010 Wuhua Liu Created |
21 --+======================================================================*/
22 --==========================================================================
23 -- PROCEDURE NAME:
24 --
25 -- Add_Basic_Information Public
26 --
27 -- DESCRIPTION:
28 --
29 -- This procedure is to export Basic Account Information
30 -- of Fixed Assets for public sector
31 --
32 -- PARAMETERS:
33 -- In: pn_legal_entity_id legal Entity ID
34 -- pn_ledger_id Ledger ID
35 -- pv_accounting_year Accounting year
36 -- pv_period_from The From Period
37 -- pv_period_to The To Period
38 --
39 -- DESIGN REFERENCES:
40 -- TDD_1213_FIN_GL_P_CNAOV2_FA.doc
41 --
42 -- CHANGE HISTORY:
43 --
44 -- 10-AUG-2010 Wuhua Liu created
45 PROCEDURE Add_Basic_Information
46 ( pn_legal_entity_id IN NUMBER
47 , pn_ledger_id IN NUMBER
48 , pv_accounting_year IN VARCHAR2
49 , pv_period_from IN VARCHAR2
50 , pv_period_to IN VARCHAR2
51 )
52 IS
53 lv_procedure_name VARCHAR2(40) := 'Add_Basic_Information';
54 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
55 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
56 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
57 ld_start_date DATE;
58 ld_end_date DATE;
59 ln_cost_row_count NUMBER := 0;
60
61 -- to get the fixed asset account
62 CURSOR fixed_asset_account_cur
63 ( pn_legal_entity NUMBER
64 , pn_ledger NUMBER
65 , pd_start_date DATE
66 , pd_end_date DATE
67 )
68 IS
69 SELECT
70 DISTINCT fcb.asset_cost_acct
71 FROM
72 fa_book_controls_sec fbcs
73 , fa_category_books fcb
74 , fa_additions_v fav
75 WHERE fbcs.book_type_code = fcb.book_type_code
76 AND fbcs.set_of_books_id = pn_ledger
77 AND fbcs.book_class IN ('CORPORATE', 'TAX')
78 AND fbcs.gl_posting_allowed_flag = 'YES'
79 AND fav.asset_category_id = fcb.category_id
80 AND fav.asset_type = 'CAPITALIZED'
81 AND fav.asset_id IN
82 (SELECT
83 DISTINCT fdh.asset_id
84 FROM
85 fa_distribution_history fdh
86 , fa_transaction_headers fth
87 WHERE fdh.asset_id = fth.asset_id
88 AND fth.transaction_type_code NOT IN ('FULL RETIREMENT')
89 AND EXISTS
90 (
91 SELECT
92 fdp.period_close_date
93 FROM
94 fa_deprn_periods fdp
95 WHERE fdp.book_type_code = fbcs.book_type_code
96 AND fdp.calendar_period_close_date
97 BETWEEN pd_start_date AND pd_end_date
98 AND fdp.period_close_date IS NOT NULL
99 )
100 AND fdh.date_effective <
101 nvl((SELECT MAX(fdp.period_close_date)
102 FROM fa_deprn_periods fdp
103 WHERE fdp.book_type_code = fbcs.book_type_code
104 AND fdp.calendar_period_close_date <= pd_end_date
105 AND fdp.period_close_date IS NOT NULL),
106 SYSDATE)
107 AND (
108 fdh.date_ineffective >
109 nvl((SELECT MAX(fdp.period_close_date)
110 FROM fa_deprn_periods fdp
111 WHERE fdp.book_type_code = fbcs.book_type_code
112 AND fdp.calendar_period_close_date <= pd_end_date
113 AND fdp.period_close_date IS NOT NULL),
114 SYSDATE) OR fdh.date_ineffective IS NULL
115 )
116 AND EXISTS
117 (
118 SELECT jclllbg.bal_seg_value
119 FROM ja_cn_ledger_le_bsv_gt jclllbg
120 WHERE JA_CN_CFS_DATA_CLT_PKG.
121 get_balancing_segment(fdh.code_combination_id) =
122 jclllbg.bal_seg_value
123 AND jclllbg.ledger_id = pn_ledger
124 AND jclllbg.legal_entity_id = pn_legal_entity
125 )
126 )
127 ORDER BY fcb.asset_cost_acct;
128 BEGIN
129 --logging for debug
130 IF (ln_proc_level >= ln_dbg_level)
131 THEN
132 FND_LOG.STRING( ln_proc_level
133 , GV_MODULE_PREFIX || '.' || lv_procedure_name || '.begin'
134 , 'Enter procedure');
135 -- logging the parameters
136 FND_LOG.STRING( ln_proc_level
137 , lv_procedure_name || '.parameters'
138 , 'pn_legal_entity_id=' || pn_legal_entity_id || ','
139 || 'pn_ledger_id=' || pn_ledger_id || ','
140 || 'pv_accounting_year=' || pv_accounting_year || ','
141 || 'pv_period_from=' || pv_period_from || ','
142 || 'pv_period_to=' || pv_period_to
143 );
144 END IF; --l_proc_level>=l_dbg_level
145 FND_FILE.PUT_LINE( FND_FILE.log
146 , lv_procedure_name || '.parameters:'
147 || 'pn_legal_entity_id=' || pn_legal_entity_id || ','
148 || 'pn_ledger_id=' || pn_ledger_id || ','
149 || 'pv_accounting_year=' || pv_accounting_year || ','
150 || 'pv_period_from=' || pv_period_from || ','
151 || 'pv_period_to=' || pv_period_to);
152
153 --Fetch start date and end date
154 IF (pv_period_from IS NOT NULL)
155 THEN
156 SELECT
157 start_date
158 INTO
159 ld_start_date
160 FROM
161 gl_period_statuses
162 WHERE ledger_id = pn_ledger_id
163 AND application_id = 101
164 AND period_name = pv_period_from
165 AND to_char(period_year) = pv_accounting_year;
166 ELSE
167 ld_start_date := TO_DATE(pv_accounting_year||'0101','YYYYMMDD');
168 END IF; --pv_period_from IS NOT NULL
169
170 IF (pv_period_to IS NOT NULL)
171 THEN
172 SELECT
173 end_date
174 INTO
175 ld_end_date
176 FROM
177 gl_period_statuses
178 WHERE ledger_id = pn_ledger_id
179 AND application_id = 101
180 AND period_name = pv_period_to
181 AND to_char(period_year) = pv_accounting_year;
182 ELSE
183 ld_end_date := to_date(pv_accounting_year||'1231','YYYYMMDD');
184 END IF; --(pv_period_to IS NOT NULL)
185
186 --logging the variables
187 IF (ln_statement_level >= ln_dbg_level)
188 THEN
189 FND_LOG.STRING( ln_statement_level
190 , lv_procedure_name
191 , 'ld_start_date=' || ld_start_date || ','
192 || 'ld_end_date=' || ld_end_date
193 );
194 END IF; --(ln_statement_level >= ln_dbg_level)
195
196 FND_FILE.PUT_LINE( FND_FILE.log
197 , lv_procedure_name || '.variables:'
198 || 'ld_start_date=' || ld_start_date || ','
199 || 'ld_end_date=' || ld_end_date
200 );
201
202 --add to XML
203 JA_CN_UTILITY.Add_Sub_Root_Node( 'BASIC_INFO_OF_FIXED_ASSET'
204 , JA_CN_UTILITY.GV_TAG_TYPE_START
205 , JA_CN_UTILITY.GV_MODULE_FA
206 );
207 FOR v_row IN fixed_asset_account_cur( pn_legal_entity_id
208 , pn_ledger_id
209 , ld_start_date
210 , ld_end_date
211 )
212 LOOP
213 ln_cost_row_count := ln_cost_row_count + 1;
214 IF(v_row.asset_cost_acct IS NOT NULL)
215 THEN
216 JA_CN_UTILITY.Add_Child_Node( 'FIXED_ASSET_ACCOUNT'
217 , v_row.asset_cost_acct
218 , JA_CN_UTILITY.GV_TYPE_VARCHAR2
219 , JA_CN_UTILITY.GV_REQUIRED_YES
220 , JA_CN_UTILITY.GV_MODULE_FA
221 );
222 END IF; --(fixed_asset_account_row.asset_cost_acct IS NOT NULL)
223 END LOOP; --fixed_asset_account_row IN fixed_asset_account_cur
224
225 IF(ln_cost_row_count = 0)
226 THEN
227 JA_CN_UTILITY.Add_Child_Node( 'FIXED_ASSET_ACCOUNT'
228 , NULL
229 , JA_CN_UTILITY.GV_TYPE_VARCHAR2
230 , JA_CN_UTILITY.GV_REQUIRED_YES
231 , JA_CN_UTILITY.GV_MODULE_FA
232 );
233 END IF; --(ln_cost_row_count = 0)
234 JA_CN_UTILITY.Add_Sub_Root_Node( 'BASIC_INFO_OF_FIXED_ASSET'
235 , JA_CN_UTILITY.GV_TAG_TYPE_END
236 , JA_CN_UTILITY.GV_MODULE_FA
237 );
238
239
240 --logging for debug
241 IF (ln_proc_level >= ln_dbg_level)
242 THEN
243 FND_LOG.STRING( ln_proc_level
244 , GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end'
245 , 'Exit procedure');
246 END IF; -- (ln_proc_level>=ln_dbg_level)
247 EXCEPTION
248 WHEN OTHERS THEN
249 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
250 THEN
251 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
252 , GV_MODULE_PREFIX || '.' || lv_procedure_name
253 || '.Other_Exception '
254 , SQLCODE || SQLERRM);
255 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
256 FND_FILE.put_line( FND_FILE.log
257 , lv_procedure_name || SQLCODE || SQLERRM);
258 RAISE;
259 END Add_Basic_Information;
260 END JA_CN_PS_FA_BI_EXP_PKG;
261