[Home] [Help]
PACKAGE BODY: APPS.JA_CN_CFS_INT_PKG
Source
1 package body JA_CN_CFS_INT_PKG AS
2 --$Header: JACNINTB.pls 120.2 2007/12/03 04:20:35 qzhao noship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNINTB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package is used in Collecting CFS Data from SLA |
13 --| in the CNAO Project. |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE Collect_SLA_Data PUBLIC |
17 --| PROCEDURE put_line PRIVATE |
18 --| PROCEDURE put_log PRIVATE |
19 --| PROCEDURE insert_CFS_Data PRIVATE |
20 --| |
21 --| HISTORY |
22 --| 05/09/2007 Shujuan Yan Created |
23 --+======================================================================*/
24 --==========================================================================
25 -- PROCEDURE NAME:
26 -- Put_Line private
27 --
28 -- DESCRIPTION:
29 -- This procedure write data to log file.
30 --
31 -- PARAMETERS:
32 -- In: p_str VARCHAR2
33 --
34 -- DESIGN REFERENCES:
35 -- None
36 --
37 -- CHANGE HISTORY:
38 -- 05/09/2007 Shujuan Yan Created
39 --===========================================================================
40 PROCEDURE put_log(p_module IN VARCHAR2, p_message IN VARCHAR2) AS
41 BEGIN
42 IF (fnd_log.LEVEL_STATEMENT >= g_debug_devel) THEN
43 fnd_log.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
44 MODULE => p_module,
45 MESSAGE => p_message);
46 END IF;
47
48 END put_log;
49 --==========================================================================
50 -- PROCEDURE NAME:
51 -- Put_Line private
52 --
53 -- DESCRIPTION:
54 -- This procedure write data to concurrent output file.
55 --
56 -- PARAMETERS:
57 -- In: p_str VARCHAR2
58 --
59 -- DESIGN REFERENCES:
60 -- None
61 --
62 -- CHANGE HISTORY:
63 -- 05/09/2007 Shujuan Yan Created
64 --===========================================================================
65 PROCEDURE put_line(p_str IN VARCHAR2) AS
66 BEGIN
67 FND_FILE.Put_Line(FND_FILE.Output, p_str);
68 END put_line;
69
70 --==========================================================================
71 -- PROCEDURE NAME:
72 -- insert_sla_data Public
73 --
74 -- DESCRIPTION:
75 -- This procedure is used to insert the record into
76 -- ja_cn_cfs_activities_all from ja_cn_cfs_activities_interface
77 --
78 -- PARAMETERS:
79 -- In: p_coa_id Chart of Accounts id
80 -- p_ledger_id Ledger ID
81 -- p_le_id legal entity ID
82 --
83 -- DESIGN REFERENCES:
84 -- CNAO_CFS_Data_interface_TD.doc
85 --
86 -- CHANGE HISTORY:
87 -- 04/09/2007 Shujuan Yan Created
88 --===========================================================================
89 PROCEDURE insert_CFS_data(P_COA_ID IN NUMBER,
90 P_LEDGER_ID IN NUMBER,
91 P_LEGAL_ENTITY_ID IN NUMBER
92 ) AS
93 BEGIN
94
95 INSERT INTO ja_cn_cfs_activities_all(CFS_ACTIVITY_ID,
96 LEGAL_ENTITY_ID,
97 LEDGER_ID,
98 ORG_ID,
99 TRX_ID,
100 TRX_NUMBER,
101 TRX_LINE_ID,
102 SOURCE,
103 TRANSACTION_TYPE,
104 DOCUMENT_SEQUENCE_NUMBER,
105 TRANSACTION_DATE,
106 GL_DATE,
107 PERIOD_NAME,
108 FUNC_CURR_CODE,
109 FUNC_AMOUNT,
110 ORIGINAL_CURR_CODE,
111 ORIGINAL_AMOUNT,
112 CURRENCY_CONVERSION_RATE,
113 CURRENCY_CONVERSION_TYPE,
114 CURRENCY_CONVERSION_DATE,
115 DESCRIPTION,
116 DETAILED_CFS_ITEM,
117 INTERCOMPANY_FLAG,
118 REFERENCE_NUMBER,
119 THIRD_PARTY_NAME,
120 THIRD_PARTY_NUMBER,
121 EVENT_CLASS_CODE,
122 SOURCE_APPLICATION_ID,
123 ANALYTICAL_CRITERION_CODE,
124 SOURCE_VALUE,
125 CASH_ITEM_DESC,
126 UPGRADE_FLAG,
127 LAST_UPDATE_DATE,
128 LAST_UPDATED_BY,
129 CREATION_DATE,
130 CREATED_BY,
131 LAST_UPDATE_LOGIN)
132 SELECT ja_cn_cfs_activities_s.NEXTVAL,
133 LEGAL_ENTITY_ID,
134 LEDGER_ID,
135 ORG_ID,
136 TRX_ID,
137 TRX_NUMBER,
138 TRX_LINE_ID,
139 SOURCE,
140 TRANSACTION_TYPE,
141 DOCUMENT_SEQUENCE_NUMBER,
142 TRANSACTION_DATE,
143 GL_DATE,
144 PERIOD_NAME,
145 FUNC_CURR_CODE,
146 FUNC_AMOUNT,
147 ORIGINAL_CURR_CODE,
148 ORIGINAL_AMOUNT,
149 CURRENCY_CONVERSION_RATE,
150 CURRENCY_CONVERSION_TYPE,
151 CURRENCY_CONVERSION_DATE,
152 DESCRIPTION,
153 DETAILED_CFS_ITEM,
154 INTERCOMPANY_FLAG,
155 REFERENCE_NUMBER,
156 THIRD_PARTY_NAME,
157 THIRD_PARTY_NUMBER,
158 EVENT_CLASS_CODE,
159 SOURCE_APPLICATION_ID,
160 ANALYTICAL_CRITERION_CODE,
161 SOURCE_VALUE,
162 CASH_ITEM_DESC,
163 'I',
164 SYSDATE,
165 fnd_global.user_id,
166 SYSDATE,
167 fnd_global.user_id,
168 fnd_global.LOGIN_ID
169 FROM ja_cn_cfs_activities_interface
170 WHERE legal_entity_id = p_legal_entity_id
171 AND ledger_id = p_ledger_id
172 AND status = 'S';
173 END;
174 --==========================================================================
175 -- PROCEDURE NAME:
176 -- collect_sla_data Public
177 --
178 -- DESCRIPTION:
179 -- This procedure is used to import the cash flow activity data from
180 -- interface table inot CFS tables.
181 --
182 -- PARAMETERS:
183 -- In: p_coa_id Chart of Accounts id
184 -- p_ledger_id Ledger ID
185 -- p_legal_entity_id legal entity ID
186 -- DESIGN REFERENCES:
187 -- CNAO_CFS_Data_interface_TD.doc
188 --
189 -- CHANGE HISTORY:
190 -- 04/09/2007 Shujuan Yan Created
191 --===========================================================================
192 PROCEDURE import_CFS_data(ERRBUF OUT NOCOPY VARCHAR2,
193 RETCODE OUT NOCOPY VARCHAR2,
194 P_COA_ID IN NUMBER,
195 P_LEDGER_ID IN NUMBER,
196 P_legal_entity_ID IN NUMBER) AS
197 l_procedure_name VARCHAR2(30) := 'import_CFS_data';
198 l_rowid VARCHAR2(300);
199 l_period_name ja_cn_cfs_activities_interface.period_name%TYPE;
200 l_func_curr_code ja_cn_cfs_activities_interface.func_curr_code%TYPE;
201 l_detailed_cfs_item ja_cn_cfs_activities_interface.detailed_cfs_item%TYPE;
202 l_flag VARCHAR2(1);
203 l_number NUMBER;
204 l_trx_number ja_cn_cfs_activities_interface.trx_number%TYPE;
205 l_trx_date ja_cn_cfs_activities_interface.transaction_date%TYPE;
206 l_msg varchar2(2000);
207
208 CURSOR c_activities IS
209 SELECT ROWID,
210 period_name,
211 func_curr_code,
212 detailed_cfs_item,
213 trx_number,
214 transaction_date
215 FROM ja_cn_cfs_activities_interface
216 WHERE ledger_id = p_ledger_id
217 AND legal_entity_id = p_legal_entity_id;
218
219 BEGIN
220
221 --Delete the data whose status is 'Error' and 'Success' in interface table
222 DELETE FROM ja_cn_cfs_activities_interface
223 WHERE ledger_id = p_ledger_id
224 AND legal_entity_id = p_legal_entity_id
225 AND (status = 'E' OR status = 'S');
226
227 IF (G_PROC_LEVEL >= g_debug_devel) THEN
228 FND_LOG.STRING(G_PROC_LEVEL,
229 G_MODULE_PREFIX || l_procedure_name || '.begin',
230 'Begin procedure');
231 END IF; --( G_PROC_LEVEL >= g_debug_devel)
232
233 OPEN c_activities;
234 LOOP
235 FETCH c_activities INTO
236 l_rowid,
237 l_period_name,
238 l_func_curr_code,
239 l_detailed_cfs_item,
240 l_trx_number,
241 l_trx_date;
242 EXIT WHEN c_activities%NOTFOUND;
243
244 l_flag := 'S';
245
246 --Check functional currency code
247 SELECT COUNT(*)
248 INTO l_number
249 FROM gl_ledgers
250 WHERE ledger_id = P_LEDGER_ID
251 AND currency_code = l_func_curr_code;
252
253 IF l_number <> 1 THEN
254 l_flag := 'E';
255 FND_MESSAGE.Set_Name('JA', 'JA_CN_INVALID_CURR');
256 FND_MESSAGE.Set_Token('CURR',l_func_curr_code,true);
257 FND_MESSAGE.Set_Token('TRX',l_trx_number,true);
258 l_msg := FND_MESSAGE.Get;
259 fnd_file.PUT_LINE(fnd_file.LOG, l_msg);
260 RETCODE := 1;
261 END IF;
262
263 --Check period name
264 SELECT COUNT(*)
265 INTO l_number
266 FROM Gl_Periods gp, gl_ledgers gl
267 WHERE gl.ledger_id = p_ledger_id
268 AND gl.period_set_name = gp.period_set_name
269 AND gp.period_name = l_period_name;
270
271 IF l_number <> 1 THEN
272 l_flag := 'E';
273 FND_MESSAGE.Set_Name('JA', 'JA_CN_INVALID_PERIOD');
274 FND_MESSAGE.Set_Token('PERIOD',l_period_name,true);
275 FND_MESSAGE.Set_Token('TRX',l_trx_number,true);
276 l_msg := FND_MESSAGE.Get;
277 fnd_file.PUT_LINE(fnd_file.LOG, l_msg);
278 RETCODE := 1;
279 END IF;
280
281 --Check detailed CFS item
282 SELECT COUNT(*)
283 INTO l_number
284 FROM Fnd_Flex_Values_Tl Ffvt,
285 fnd_flex_values Ffv,
286 ja_cn_cash_valuesets_all Cra
287 WHERE Cra.Chart_Of_Accounts_Id = P_COA_Id
288 AND Ffv.Flex_Value_Set_Id = Cra.Flex_Value_Set_Id
289 AND Ffv.Flex_Value_Id = Ffvt.Flex_Value_Id
290 AND Ffv.Flex_Value = l_detailed_cfs_item
291 AND ffvt.LANGUAGE = userenv('LANG');
292
293 IF l_number <> 1 THEN
294 l_flag := 'E';
295 FND_MESSAGE.Set_Name('JA', 'JA_CN_INVALID_CASH_ITEM');
296 FND_MESSAGE.Set_Token('ITEM',l_period_name,true);
297 FND_MESSAGE.Set_Token('TRX',l_trx_number,true);
298 l_msg := FND_MESSAGE.Get;
299 fnd_file.PUT_LINE(fnd_file.LOG, l_msg);
300 RETCODE := 1;
301 END IF;
302
303 IF l_flag = 'S' THEN
304 UPDATE ja_cn_cfs_activities_interface
305 SET status = 'S'
306 WHERE ROWID = l_rowid;
307 END IF;
308
309 IF l_flag = 'E' THEN
310 UPDATE ja_cn_cfs_activities_interface
311 SET status = 'E'
312 WHERE ROWID = l_rowid;
313 END IF;
314 END LOOP;
315 CLOSE c_activities;
316 COMMIT;
317
318 --insert data into ja_cn_cfs_activities_all
319 insert_CFS_data(P_COA_ID,
320 P_LEDGER_ID,
321 P_legal_entity_ID);
322
323 IF( G_PROC_LEVEL >= g_debug_devel )
324 THEN
325 FND_LOG.STRING(G_PROC_LEVEL
326 ,G_MODULE_PREFIX||l_procedure_name||'.end'
327 ,'End procedure');
328 END IF; --( G_PROC_LEVEL >= g_debug_devel)
329 EXCEPTION
330 WHEN OTHERS THEN
331 IF(FND_LOG.LEVEL_UNEXPECTED >= g_debug_devel)
332 THEN
333 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
334 , G_MODULE_PREFIX || l_procedure_name||'.OTHER_EXCEPTION'
335 , SQLCODE||':'||SQLERRM||p_coa_id);
336 END IF;
337 RAISE;
338 END import_CFS_data;
339
340 end JA_CN_CFS_INT_PKG;