[Home] [Help]
PACKAGE BODY: APPS.JA_CN_FA_MM_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_FA_MM_EXPORT_PKG AS
2 --$Header: JACNMMEB.pls 120.2 2010/05/20 02:39:12 qingywan noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNMMEB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export fixed asset Modification Method. |
13 --| |
14 --| PROCEDURE LIST |
15 --| Add_FA_Modification_Method Export asset Modification Method |
16 --| |
17 --| |
18 --| HISTORY |
19 --| 04/08/2010 Qingyi Wang created |
20 --+======================================================================*/
21 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_FA_MM_EXPORT';
22 --==========================================================================
23 -- PROCEDURE NAME:
24 --
25 -- Add_FA_Modification_Method Public
26 --
27 -- DESCRIPTION:
28 --
29 -- This procedure is used to export Modification Method in Fixed Asset.
30 --
31 -- PARAMETERS:
32 -- In: pn_legal_entity_id LEGAL_ENTITY_ID
33 -- pn_ledger_id Ledger ID
34 -- pv_accounting_year Accounting Year
35 -- pv_period_from Period From
36 -- pv_period_to Period To
37 -- DESIGN REFERENCES:
38 -- CNAO_V2_FA_TD.doc
39 --
40 -- CHANGE HISTORY:
41 -- 04/08/2010 Qingyi Wang created
42 --==========================================================================
43 PROCEDURE Add_FA_Modification_Method
44 ( pn_legal_entity_id IN NUMBER
45 , pn_ledger_id IN NUMBER
46 , pv_accounting_year IN VARCHAR2
47 , pv_period_from IN VARCHAR2
48 , pv_period_to IN VARCHAR2
49 )
50 IS
51 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
52 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
53 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
54 lv_procedure_name VARCHAR2(40) := 'Add_FA_Modification_Method';
55 lv_tran_type_code VARCHAR2(20);
56 lv_tran_type VARCHAR2(80);
57 ld_start_date DATE;
58 ld_end_date DATE;
59 ln_row_count NUMBER; -- row count for modification method
60 NO_DATA EXCEPTION;
61
62 CURSOR transaction_history_cur IS
63 SELECT
64 DISTINCT fthv.transaction_type_code
65 ,fthv.transaction_type
66 FROM
67 fa_transaction_history_trx_v fthv
68 , fa_distribution_history fdh
69 , gl_code_combinations expense_cc
70 , fa_book_controls_sec fbc
71 , fa_deprn_periods fdp
72 WHERE
73 fthv.book_type_code = fbc.book_type_code
74 AND fbc.book_class IN('CORPORATE', 'TAX')
75 AND fbc.gl_posting_allowed_flag = 'YES'
76 AND fbc.set_of_books_id = pn_ledger_id
77 AND
78 ((SELECT greatest(greatest(dp.calendar_period_open_date,
79 least(sysdate, dp.calendar_period_close_date)),
80 dp.calendar_period_open_date)
81 FROM fa_deprn_periods dp
82 WHERE fthv.book_type_code = dp.book_type_code
83 AND fthv.date_effective BETWEEN dp.period_open_date AND
84 nvl(dp.period_close_date, SYSDATE)) BETWEEN ld_start_date AND ld_end_date)
85 AND fthv.asset_id = fdh.asset_id
86 AND fdp.book_type_code = fthv.book_type_code
87 -- Check legal entity
88 AND fdh.date_effective < nvl(fdp.period_close_date, SYSDATE)
89 AND (fdh.date_ineffective > fdp.period_open_date
90 OR fdh.date_ineffective IS NULL)
91 AND EXISTS
92 (SELECT jclllbg.bal_seg_value
93 FROM ja_cn_ledger_le_bsv_gt jclllbg
94 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(fdh.code_combination_id) =
95 jclllbg.bal_seg_value
96 AND jclllbg.Ledger_Id = pn_ledger_id
97 AND jclllbg.Legal_Entity_Id = pn_legal_entity_id)
98 AND greatest(greatest(fdp.calendar_period_open_date,
99 least(SYSDATE, fdp.calendar_period_close_date)))
100 BETWEEN ld_start_date
101 AND ld_end_date
102 AND
103 fthv.transaction_type_code NOT IN
104 ('ADDITION/VOID', 'TRANSFER IN/VOID', 'TRANSFER IN', 'TRANSFER OUT');
105
106 BEGIN
107
108 --logging for debug
109 IF (ln_proc_level>=ln_dbg_level)
110 THEN
111 FND_LOG.STRING( ln_proc_level
112 , GV_MODULE_PREFIX ||'.' || lv_procedure_name ||
113 '.begin'
114 , 'Enter procedure'
115 );
116
117 -- logging the parameters
118 FND_LOG.STRING(ln_proc_level,
119 lv_procedure_name ||
120 '.parameters',
121 'pn_legal_entity_id=' || pn_legal_entity_id|| ',' ||
122 'pn_ledger_id=' || pn_ledger_id || ',' ||
123 'pv_accounting_year=' || pv_accounting_year || ',' ||
124 'pv_period_from=' || pv_period_from || ',' ||
125 'pv_period_to=' || pv_period_to);
126 END IF; --l_proc_level>=l_dbg_level
127
128 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
129 '.parameters:' ||
130 'pn_legal_entity_id=' || pn_legal_entity_id|| ',' ||
131 'pn_ledger_id=' || pn_ledger_id || ',' ||
132 'pv_accounting_year=' || pv_accounting_year || ',' ||
133 'pv_period_from=' || pv_period_from || ',' ||
134 'pv_period_to=' || pv_period_to);
135
136 --Fetch start data and end date
137 IF pv_period_from IS NOT NULL THEN
138 SELECT start_date
139 INTO ld_start_date
140 FROM gl_period_statuses
141 WHERE ledger_id = pn_ledger_id
142 AND application_id = 101
143 AND period_name = pv_period_from
144 AND to_char(period_year) = pv_accounting_year;
145 ELSE
146 ld_start_date := to_date(pv_accounting_year||'0101','YYYYMMDD');
147 END IF; -- pv_period_from IS NOT NULL
148
149 IF pv_period_to IS NOT NULL THEN
150 SELECT end_date
151 INTO ld_end_date
152 FROM gl_period_statuses
153 WHERE ledger_id = pn_ledger_id
154 AND application_id = 101
155 AND period_name = pv_period_to
156 AND to_char(period_year) = pv_accounting_year;
157 ELSE
158 ld_end_date := to_date(pv_accounting_year||'1231','YYYYMMDD');
159 END IF; -- pv_period_to IS NOT NULL
160
161 --log start data and end date
162 IF (ln_statement_level >= ln_dbg_level)
163 THEN
164 FND_LOG.STRING(ln_statement_level,
165 lv_procedure_name,
166 'Fetched: start date=' ||
167 nvl(to_char(ld_start_date), 'null') || ' end date=' ||
168 nvl(to_char(ld_end_date), 'null'));
169 END IF; --(ln_statement_level >= ln_dbg_level)
170 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
171 '.variable:' ||
172 'start date=' ||
173 nvl(to_char(ld_start_date), 'null') || ' end date=' ||
174 nvl(to_char(ld_end_date), 'null'));
175
176 ln_row_count := 0;
177 OPEN transaction_history_cur;
178 LOOP
179 FETCH transaction_history_cur INTO lv_tran_type_code,lv_tran_type;
180 EXIT WHEN transaction_history_cur%NOTFOUND;
181
182 ln_row_count := ln_row_count + 1;
183 Ja_Cn_Utility.Add_Sub_Root_Node( pv_sub_root_tag_name => 'MODIFICATION_METHOD'
184 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_START
185 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
186 );
187 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'MODIFICATION_METHOD_CODE'
188 , pv_text_node_value => lv_tran_type_code
189 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
190 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
191 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
192 );
193 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'MODIFICATION_METHOD_NAME'
194 , pv_text_node_value => lv_tran_type
195 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
196 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
197 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
198 );
199 Ja_Cn_Utility.Add_Sub_Root_Node( pv_sub_root_tag_name => 'MODIFICATION_METHOD'
200 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_END
201 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
202 );
203
204 END LOOP;
205 CLOSE transaction_history_cur;
206
207 IF (ln_row_count = 0)
208 THEN
209 Ja_Cn_Utility.Print_No_Data_Found_For_Log('MODIFICATION_METHOD',
210 Ja_Cn_Utility.GV_MODULE_FA);
211
212 Ja_Cn_Utility.Add_Sub_Root_Node( pv_sub_root_tag_name => 'MODIFICATION_METHOD'
213 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_START
214 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
215 );
216 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'MODIFICATION_METHOD_CODE'
217 , pv_text_node_value => NULL
218 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
219 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
220 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
221 );
222 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'MODIFICATION_METHOD_NAME'
223 , pv_text_node_value => NULL
224 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
225 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
226 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
227 );
228 Ja_Cn_Utility.Add_Sub_Root_Node( pv_sub_root_tag_name => 'MODIFICATION_METHOD'
229 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_END
230 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
231 );
232 END IF; --(ln_row_count = 0)
233
234
235 --logging for debug
236 IF (ln_proc_level>=ln_dbg_level)
237 THEN
238 FND_LOG.STRING( ln_proc_level
239 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
240 , 'Exit procedure'
241 );
242 END IF; -- (ln_proc_level>=ln_dbg_level)
243
244 EXCEPTION
245 WHEN OTHERS THEN
246 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
247 THEN
248 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
249 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
250 '.Other_Exception ',
251 SQLCODE || SQLERRM);
252 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
253 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
254
255 END Add_FA_Modification_Method;
256 END JA_CN_FA_MM_EXPORT_PKG;
257