[Home] [Help]
PACKAGE BODY: APPS.JA_CN_FA_ADIRA_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_FA_ADIRA_EXPORT_PKG AS
2 --$Header: JACNADRB.pls 120.2 2010/07/01 03:13:10 choli noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNADRB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export Fixed Assets Decreasing Real Information |
13 --| |
14 --| PROCEDURE LIST |
15 --| PROCEDURE Add_Asset_Decreasing_Real_Info |
16 --| |
17 --| |
18 --| HISTORY |
19 --| 23-Apr-2010 Chongwu Li created |
20 --+======================================================================*/
21 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_FA_ADIRA_EXPORT_PKG';
22
23 --==========================================================================
24 -- PROCEDURE NAME:
25 --
26 -- Add_Asset_Decreasing_Real_Info Public
27 --
28 -- DESCRIPTION:
29 --
30 -- This procedure is to export Fixed Assets Decreasing Real Information
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 period from
37 -- pv_period_to period to
38 -- DESIGN REFERENCES:
39 -- CNAO_V2_GL_TD.doc
40 --
41 -- CHANGE HISTORY:
42 --
43 -- 23-Apr-2010 Chongwu Li created
44
45 PROCEDURE Add_Asset_Decreasing_Real_Info(pn_legal_entity_id NUMBER
46 ,pn_ledger_id NUMBER
47 ,pv_accounting_year VARCHAR2
48 ,pv_period_from VARCHAR2
49 ,pv_period_to VARCHAR2) IS
50 lv_procedure_name VARCHAR2(40) := 'Add_Asset_Decreasing_Real_Info';
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 ln_row_count NUMBER;
55 lv_period_name VARCHAR2(10);
56 ld_start_date DATE;
57 ld_end_date DATE;
58 --lv_bsv_flag VARCHAR2(10);
59
60 -- to get Electronic Accounting Book
61 CURSOR asset_decreasing_real_cur(ln_legal_entity_id NUMBER
62 ,ln_ledger_id NUMBER
63 ,ld_date_from DATE
64 ,ld_date_to DATE) IS
65 SELECT DISTINCT fth.transaction_header_id,
66 fa.asset_number,
67 greatest(greatest(fdp.calendar_period_open_date,
68 least(sysdate,
69 fdp.calendar_period_close_date)),
70 fdp.calendar_period_open_date) transaction_date,
71 fa.tag_number
72 FROM fa_retirements fr,
73 fa_transaction_headers fth,
74 fa_additions fa,
75 fa_books fb,
76 fa_book_controls_sec fbcs,
77 fa_distribution_history fdh,
78 fa_deprn_periods fdp
79 --gl_code_combinations gcc
80 WHERE (fth.transaction_type_code = 'FULL RETIREMENT' OR
81 fth.transaction_type_code = 'PARTIAL RETIREMENT')
82 AND fth.asset_id = fa.asset_id
83 AND fr.asset_id = fth.asset_id
84 AND fdh.asset_id = fa.asset_id
85 AND fb.asset_id = fa.asset_id
86 AND fb.transaction_header_id_out = fr.transaction_header_id_in
87 AND fa.asset_type = 'CAPITALIZED'
88 AND fth.book_type_code = fbcs.book_type_code
89 AND (fbcs.book_class = 'CORPORATE' or fbcs.book_class = 'TAX')
90 AND fbcs.gl_posting_allowed_flag = 'YES'
91 --AND fdh.book_type_code = fbcs.book_type_code -- remove this condition, fdh share book_type_code
92 AND fth.book_type_code = fdp.book_type_code
93 AND fdh.book_type_code = fdp.book_type_code
94 --AND fdh.code_combination_id = gcc.code_combination_id
95 AND fth.date_effective between fdp.period_open_date and
96 nvl(fdp.period_close_date, sysdate)
97 AND fdh.date_effective < nvl(fdp.period_close_date, sysdate)
98 AND (fdh.date_ineffective > fdp.period_open_date or
99 fdh.date_ineffective IS NULL)
100 --AND gcc.segment1 IN (SELECT segment_value
101 -- FROM gl_ledger_norm_seg_vals
102 -- WHERE legal_entity_id = ln_legal_entity_id
103 -- AND ledger_id = ln_ledger_id)
104 AND EXISTS
105 (SELECT jclllbg.bal_seg_value
106 FROM ja_cn_ledger_le_bsv_gt jclllbg
107 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(fdh.code_combination_id) =
108 jclllbg.bal_seg_value
109 AND jclllbg.Ledger_Id = ln_ledger_id
110 AND jclllbg.Legal_Entity_Id = ln_legal_entity_id)
111 AND fbcs.set_of_books_id = ln_ledger_id
112 AND greatest(greatest(fdp.calendar_period_open_date,
113 least(sysdate,
114 fdp.calendar_period_close_date))) BETWEEN
115 ld_date_from AND ld_date_to
116 ORDER BY transaction_date, fth.transaction_header_id;
117
118 BEGIN
119 --logging for debug
120 FND_FILE.PUT_LINE(FND_FILE.log, lv_procedure_name);
121 IF (ln_proc_level >= ln_dbg_level)
122 THEN
123 FND_LOG.STRING(ln_proc_level,
124 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
125 '.begin',
126 'Enter procedure');
127 FND_LOG.STRING(ln_proc_level,
128 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 END IF; --ln_proc_level>=ln_dbg_level
137 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
138 '.parameters:' ||
139 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
140 'pn_ledger_id=' || pn_ledger_id || ',' ||
141 'pv_accounting_year=' || pv_accounting_year || ',' ||
142 'pv_period_from=' || pv_period_from || ',' ||
143 'pv_period_to=' || pv_period_to);
144
145 --Fetch start data and end date
146 SELECT start_date
147 INTO ld_start_date
148 FROM GL_PERIOD_STATUSES
149 WHERE ledger_id = pn_ledger_id
150 AND application_id = 101
151 AND period_name = pv_period_from
152 AND period_year = pv_accounting_year;
153
154 SELECT end_date
155 INTO ld_end_date
156 FROM GL_PERIOD_STATUSES
157 WHERE ledger_id = pn_ledger_id
158 AND application_id = 101
159 AND period_name = pv_period_to
160 AND period_year = pv_accounting_year;
161
162
163 --logging the variables
164 IF (ln_statement_level >= ln_dbg_level)
165 THEN
166 FND_LOG.STRING(ln_statement_level,
167 lv_procedure_name,
168 'ld_start_date=' || ld_start_date || ',' ||
169 'ld_end_date=' || ld_end_date);
170 END IF; --(ln_statement_level >= ln_dbg_level)
171 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
172 '.variable:' ||
173 'ld_start_date=' || ld_start_date || ',' ||
174 'ld_end_date=' || ld_end_date);
175
176
177 ln_row_count := 0;
178 FOR v_row IN asset_decreasing_real_cur(pn_legal_entity_id
179 ,pn_ledger_id
180 ,ld_start_date
181 ,ld_end_date)
182 LOOP
183 ln_row_count := ln_row_count + 1;
184 Ja_Cn_Utility.Add_Sub_Root_Node('FIXED_ASSET_DECR_INFO-REAL_ASE'
185 ,Ja_Cn_Utility.GV_TAG_TYPE_START
186 ,Ja_Cn_Utility.GV_MODULE_FA);
187
188 Ja_Cn_Utility.Add_Child_Node('DECRE_TRANSACTION_NUMBER'
189 ,v_row.transaction_header_id
190 ,'VARCHAR2'
191 ,Ja_Cn_Utility.GV_REQUIRED_YES
192 ,Ja_Cn_Utility.GV_MODULE_FA);
193 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_CARD_NUMBER'
194 ,v_row.asset_number
195 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
196 ,Ja_Cn_Utility.GV_REQUIRED_YES
197 ,Ja_Cn_Utility.GV_MODULE_FA);
198 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_TAG_NUMBER'
199 ,v_row.tag_number
200 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
201 ,Ja_Cn_Utility.GV_REQUIRED_YES
202 ,Ja_Cn_Utility.GV_MODULE_FA);
203 SELECT period_num
204 INTO lv_period_name
205 FROM gl_period_statuses gp
206 WHERE period_year = pv_accounting_year
207 AND application_id = 101
208 AND ledger_id = pn_ledger_id
209 AND gp.adjustment_period_flag = 'N'
210 AND v_row.Transaction_Date between gp.start_date and
211 gp.end_date;
212
213 Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD'
214 ,lv_period_name
215 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
216 ,Ja_Cn_Utility.GV_REQUIRED_YES
217 ,Ja_Cn_Utility.GV_MODULE_FA);
218 Ja_Cn_Utility.Add_Sub_Root_Node('FIXED_ASSET_DECR_INFO-REAL_ASE'
219 ,Ja_Cn_Utility.GV_TAG_TYPE_END
220 ,Ja_Cn_Utility.GV_MODULE_FA);
221
222 END LOOP; --v_row IN asset_decreasing_real_cur
223
224 IF (ln_row_count = 0)
225 THEN
226 Ja_Cn_Utility.Print_No_Data_Found_For_Log('FIXED_ASSET_DECR_INFO-REAL_ASE'
227 ,Ja_Cn_Utility.GV_MODULE_FA);
228 Ja_Cn_Utility.Add_Sub_Root_Node('FIXED_ASSET_DECR_INFO-REAL_ASE'
229 ,Ja_Cn_Utility.GV_TAG_TYPE_START
230 ,Ja_Cn_Utility.GV_MODULE_FA);
231
232 Ja_Cn_Utility.Add_Child_Node('DECRE_TRANSACTION_NUMBER'
233 ,NULL
234 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
235 ,Ja_Cn_Utility.GV_REQUIRED_YES
236 ,Ja_Cn_Utility.GV_MODULE_FA);
237 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_CARD_NUMBER'
238 ,NULL,Ja_Cn_Utility.GV_TYPE_VARCHAR2
239 ,Ja_Cn_Utility.GV_REQUIRED_YES
240 ,Ja_Cn_Utility.GV_MODULE_FA);
241 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_TAG_NUMBER'
242 ,NULL
243 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
244 ,Ja_Cn_Utility.GV_REQUIRED_YES
245 ,Ja_Cn_Utility.GV_MODULE_FA);
246 Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD'
247 ,NULL
248 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
249 ,Ja_Cn_Utility.GV_REQUIRED_YES
250 ,Ja_Cn_Utility.GV_MODULE_FA);
251 Ja_Cn_Utility.Add_Sub_Root_Node('FIXED_ASSET_DECR_INFO-REAL_ASE'
252 ,Ja_Cn_Utility.GV_TAG_TYPE_END
253 ,Ja_Cn_Utility.GV_MODULE_FA);
254 END IF; --ln_row_count = 0
255
256 --logging for debug
257 IF (ln_proc_level >= ln_dbg_level) THEN
258 FND_LOG.STRING(ln_proc_level,
259 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
260 'Exit procedure');
261 END IF; -- (ln_proc_level>=ln_dbg_level)
262 EXCEPTION
263 WHEN OTHERS THEN
264 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
265 THEN
266 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
267 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
268 '.Other_Exception ',
269 SQLCODE || SQLERRM);
270 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
271 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
272 RAISE;
273 END Add_Asset_Decreasing_Real_Info;
274
275 END JA_CN_FA_ADIRA_EXPORT_PKG;