[Home] [Help]
PACKAGE BODY: APPS.JA_CN_FA_AU_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_FA_AU_EXPORT_PKG AS
2 --$Header: JACNAUEB.pls 120.1 2010/05/19 09:17:13 qingywan noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNAUEB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export asset Usage Information. |
13 --| |
14 --| PROCEDURE LIST |
15 --| Add_FA_Usage Export asset Usage Information |
16 --| |
17 --| |
18 --| HISTORY |
19 --| 04/08/2010 Qingyi Wang created |
20 --+======================================================================*/
21 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_FA_AU_EXPORT';
22 --==========================================================================
23 -- PROCEDURE NAME:
24 --
25 -- Add_FA_Usage Public
26 --
27 -- DESCRIPTION:
28 --
29 -- This procedure is used to export usage information 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 -- pn_coa_id Identifier of chart of account
38 -- DESIGN REFERENCES:
39 -- CNAO_V2_FA_TD.doc
40 --
41 -- CHANGE HISTORY:
42 --| 04/08/2010 Qingyi Wang created
43 --==========================================================================
44 PROCEDURE Add_FA_Usage
45 ( pn_legal_entity_id IN NUMBER
46 , pn_ledger_id IN NUMBER
47 , pv_accounting_year IN VARCHAR2
48 , pv_period_from IN VARCHAR2
49 , pv_period_to IN VARCHAR2
50 , pn_coa_id NUMBER
51 )IS
52 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
53 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
54 lv_procedure_name VARCHAR2(40) := 'Add_FA_Usage';
55 lv_segment VARCHAR2(30);
56 lv_usage_status_code VARCHAR2(150);
57 lv_usage_name VARCHAR2(240);
58 ln_row_count NUMBER; -- row count for asset usage
59 NO_DATA EXCEPTION;
60
61 CURSOR asset_detail_cur IS
62 SELECT
63 DISTINCT ffv.flex_value
64 ,ffvt.description
65 FROM
66 fnd_id_flex_segments fifs
67 , fa_additions_v faav
68 , fnd_flex_values_tl ffvt
69 , fnd_flex_values ffv
70
71 WHERE fifs.id_flex_code = 'KEY#'
72 AND fifs.application_id = 140
73 AND fifs.application_column_name = lv_segment
74 AND fifs.flex_value_set_id = ffv.flex_value_set_id
75 AND ffv.flex_value_id = ffvt.flex_value_id
76 AND ffvt.language = userenv('LANG');
77
78 BEGIN
79 --logging for debug
80 IF (ln_proc_level>=ln_dbg_level)
81 THEN
82 FND_LOG.STRING( ln_proc_level
83 , GV_MODULE_PREFIX ||'.' || lv_procedure_name ||
84 '.begin'
85 , 'Enter procedure'
86 );
87
88 -- logging the parameters
89 FND_LOG.STRING(ln_proc_level,
90 lv_procedure_name ||
91 '.parameters',
92 'pn_legal_entity_id=' || pn_legal_entity_id|| ',' ||
93 'pn_ledger_id=' || pn_ledger_id || ',' ||
94 'pv_accounting_year=' || pv_accounting_year || ',' ||
95 'pv_period_from=' || pv_period_from || ',' ||
96 'pv_period_to=' || pv_period_to || ',' ||
97 'pn_coa_id =' || pn_coa_id);
98 END IF; --l_proc_level>=l_dbg_level
99
100 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
101 '.parameters:' ||
102 'pn_legal_entity_id=' || pn_legal_entity_id|| ',' ||
103 'pn_ledger_id=' || pn_ledger_id || ',' ||
104 'pv_accounting_year=' || pv_accounting_year || ',' ||
105 'pv_period_from=' || pv_period_from || ',' ||
106 'pv_period_to=' || pv_period_to || ',' ||
107 'pn_coa_id =' || pn_coa_id);
108
109 --get the segment.
110 SELECT dffa.ATTRIBUTE_COLUMN
111 INTO lv_segment
112 FROM ja_cn_dff_assignments_v dffa
113 WHERE dffa.LOOKUP_CODE = 'FAAU'
114 AND dffa.chart_of_accounts_id = pn_coa_id;
115
116 ln_row_count := 0;
117
118 OPEN asset_detail_cur;
119 LOOP
120 BEGIN
121 FETCH asset_detail_cur
122 INTO lv_usage_status_code
123 , lv_usage_name;
124 EXIT WHEN asset_detail_cur%NOTFOUND;
125 ln_row_count := ln_row_count + 1;
126
127 Ja_Cn_Utility.Add_Sub_Root_Node( pv_sub_root_tag_name => 'ASSET_USAGE'
128 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_START
129 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
130 );
131 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'USAGE_STATUS_CODE'
132 , pv_text_node_value => lv_usage_status_code
133 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
134 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
135 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
136 );
137 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'USAGE_STATUS_NAME'
138 , pv_text_node_value => lv_usage_name
139 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
140 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
141 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
142 );
143 Ja_Cn_Utility.Add_Sub_Root_Node( pv_sub_root_tag_name => 'ASSET_USAGE'
144 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_END
145 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
146 );
147 EXCEPTION
148 WHEN OTHERS THEN
149 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
150 THEN
151 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
152 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
153 '.Other_Exception ',
154 SQLCODE || SQLERRM);
155 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
156 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
157 END;
158 END LOOP;
159 CLOSE asset_detail_cur;
160
161 IF (ln_row_count = 0)
162 THEN
163
164 Ja_Cn_Utility.Print_No_Data_Found_For_Log('ASSET_USAGE',
165 Ja_Cn_Utility.GV_MODULE_FA);
166
167 Ja_Cn_Utility.Add_Sub_Root_Node( pv_sub_root_tag_name => 'ASSET_USAGE'
168 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_START
169 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
170 );
171 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'USAGE_STATUS_CODE'
172 , pv_text_node_value => NULL
173 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
174 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
175 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
176 );
177 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'USAGE_STATUS_NAME'
178 , pv_text_node_value => NULL
179 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
180 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
181 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
182 );
183 Ja_Cn_Utility.Add_Sub_Root_Node( pv_sub_root_tag_name => 'ASSET_USAGE'
184 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_END
185 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
186 );
187 END IF; --(ln_row_count = 0)
188 --logging for debug
189 IF (ln_proc_level>=ln_dbg_level)
190 THEN
191 FND_LOG.STRING( ln_proc_level
192 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
193 , 'Exit procedure'
194 );
195 END IF; -- (ln_proc_level>=ln_dbg_level)
196
197 EXCEPTION
198 WHEN OTHERS THEN
199 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
200 THEN
201 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
202 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
203 '.Other_Exception ',
204 SQLCODE || SQLERRM);
205 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
206 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
207
208 END Add_FA_Usage;
209 END JA_CN_FA_AU_EXPORT_PKG;
210