DBA Data[Home] [Help]

PACKAGE BODY: APPS.JA_CN_FA_EXPORT_PROG

Source


1 PACKAGE BODY JA_CN_FA_EXPORT_PROG AS
2 --$Header: JACNFAEB.pls 120.0 2010/05/17 08:41:07 choli noship $
3 --+=======================================================================+
4 --|               Copyright (c) 1998 Oracle Corporation                   |
5 --|                       Redwood Shores, CA, USA                         |
6 --|                         All rights reserved.                          |
7 --+=======================================================================+
8 --| FILENAME                                                              |
9 --|     JACNFAEB.pls                                                      |
10 --|                                                                       |
11 --| DESCRIPTION                                                           |
12 --|     Use this package to export Fixed Asset information                |
13 --|                                                                       |
14 --| PROCEDURE LIST                                                        |
15 --|      PROCEDURE Export_Fixed_Assets                                    |
16 --|                Submit_Request                                         |
17 --|                                                                       |
18 --|                                                                       |
19 --| HISTORY                                                               |
20 --|     03/17/2010 Chongwu Li       Created                               |
21 --+======================================================================*/
22 
23 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_FA_EXPORT_PROG';
24 
25 --==========================================================================
26 --  PROCEDURE NAME:
27 --
28 --    Export_Fixed_Assets                        Public
29 --
30 --  DESCRIPTION:
31 --
32 --    This procedure is to export Fixed Asset information
33 --
34 --  PARAMETERS:
35 --      Out: pv_errbuf                    NOCOPY VARCHAR2
36 --           pv_retcode                   NOCOPY VARCHAR2
37 --      In:  pn_coa_id                    NUMBER identifier of chart of account
38 --           pn_legal_entity_id           NUMBER identifier of legal entity
39 --           pn_ledger_id                 NUMBER identifier of ledger
40 --           pv_accounting_year           VARCHAR2 accounting year
41 --           pv_period_from               VARCHAR2 period from
42 --           pv_period_to                 VARCHAR2 period to
43 --
44 --
45 --  DESIGN REFERENCES:
46 --    CNAO_V2_FA_TD.doc
47 --
48 --  CHANGE HISTORY:
49 --
50 --           06-APR-2010   Chongwu Li       created
51 --==========================================================================
52 
53 PROCEDURE Export_Fixed_Assets
54 ( pv_errbuf               OUT NOCOPY VARCHAR2
55 , pv_retcode              OUT NOCOPY VARCHAR2
56 , pn_legal_entity_id      IN  NUMBER
57 , pn_ledger_id            IN  NUMBER
58 , pv_accounting_year      IN  VARCHAR2
59 , pv_period_from          IN  VARCHAR2
60 , pv_period_to            IN  VARCHAR2
61 , pn_coa_id               IN  NUMBER
62 )
63 IS
64 lv_procedure_name      VARCHAR2(40) := 'Export_Fixed_Assets';
65 ln_dbg_level           NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
66 ln_proc_level          NUMBER := FND_LOG.LEVEL_PROCEDURE;
67 ln_statement_level     NUMBER := FND_LOG.LEVEL_STATEMENT;
68 lv_fa_element          VARCHAR2(1000);
69 lv_bsv_flag            VARCHAR2(10);
70 BEGIN
71    --logging for debug
72   IF (ln_proc_level>=ln_dbg_level)
73   THEN
74     FND_LOG.STRING( ln_proc_level
75                   , GV_MODULE_PREFIX ||'.' || lv_procedure_name ||
76                   '.begin'
77                   , 'Enter procedure'
78                   );
79 
80     -- logging the parameters
81     FND_LOG.STRING(ln_proc_level,
82                    lv_procedure_name ||
83                    '.parameters',
84                    'pn_legal_entity_id=' || pn_legal_entity_id|| ',' ||
85                    'pn_ledger_id=' || pn_ledger_id || ',' ||
86                    'pv_accounting_year=' || pv_accounting_year || ',' ||
87                    'pv_period_from=' || pv_period_from || ',' ||
88                    'pv_period_to=' || pv_period_to || ',' ||
89                    'pn_coa_id=' || pn_coa_id);
90   END IF; --l_proc_level>=l_dbg_level
91 
92   FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
93                    '.parameters:' ||
94                    'pn_legal_entity_id=' || pn_legal_entity_id|| ',' ||
95                    'pn_ledger_id=' || pn_ledger_id || ',' ||
96                    'pv_accounting_year=' || pv_accounting_year || ',' ||
97                    'pv_period_from=' || pv_period_from || ',' ||
98                    'pv_period_to=' || pv_period_to || ',' ||
99                    'pn_coa_id=' || pn_coa_id);
100   lv_bsv_flag := JA_CN_UTILITY.Populate_Ledger_Le_Bsv_Gt(p_Ledger_Id       => pn_ledger_id
101                                                         ,p_Legal_Entity_Id => pn_legal_entity_id
102                                                         );
103   -- export the header
104   -- here need to change
105   FND_FILE.put_line(FND_FILE.output,'<?xml version="1.0" encoding="'||JA_CN_UTILITY.Get_XML_Encoding||'"?>');
106   lv_fa_element := '<' || Ja_Cn_Utility.Get_XML_Tag('FIXED_ASSETS',Ja_Cn_Utility.GV_REQUIRED_NO,Ja_Cn_Utility.GV_MODULE_FA)
107                        || '   xsi:schemaLocation="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/SOE/XMLSchema '
108                        || Ja_Cn_Utility.Get_XML_Tag('FIXED_ASSETS',Ja_Cn_Utility.GV_REQUIRED_NO,Ja_Cn_Utility.GV_MODULE_FA)
109                        || '.xsd" xmlns:'
110                        || Ja_Cn_Utility.Get_XML_Tag('SOE')
111                        || '="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/SOE/XMLSchema"
112   xmlns="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/SOE/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">';
113   FND_FILE.put_line(FND_FILE.output, lv_fa_element);
114 
115   -- call the procedures to export the reports seperately here begin
116   Ja_Cn_Utility.Print_Concurrent_Steps_For_Log(pv_step_number => '2.1'
117                                               ,pv_report_name => 'BASIC_INFO_OF_FIXED_ASSET'
118                                               ,pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
119 
120   JA_CN_FA_BI_EXPORT_PKG.Add_Basic_Information(pn_legal_entity_id   => pn_legal_entity_id
121                                               ,pn_ledger_id         => pn_ledger_id
122                                               ,pv_accounting_year   => pv_accounting_year
123                                               ,pv_period_from       => pv_period_from
124                                               ,pv_period_to         => pv_period_to
125                                               );
126   Ja_Cn_Utility.Print_Concurrent_Steps_For_Log(pv_step_number => '2.2'
127                                               ,pv_report_name => 'FIXED_ASSET_CATEGORY_SETTING'
128                                               ,pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
129   JA_CN_FA_ACS_EXPORT_PKG.Add_Asset_Category(pn_legal_entity_id   => pn_legal_entity_id
130                                             ,pn_ledger_id         => pn_ledger_id
131                                             ,pv_accounting_year   => pv_accounting_year
132                                             ,pv_period_from       => pv_period_from
133                                             ,pv_period_to         => pv_period_to
134                                              );
135   Ja_Cn_Utility.Print_Concurrent_Steps_For_Log(pv_step_number => '2.3'
136                                               ,pv_report_name => 'MODIFICATION_METHOD'
137                                               ,pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
138   JA_CN_FA_MM_EXPORT_PKG.Add_FA_Modification_Method(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   Ja_Cn_Utility.Print_Concurrent_Steps_For_Log(pv_step_number => '2.4'
145                                               ,pv_report_name => 'FIXED_ASSET_DEPRN_METHOD'
146                                               ,pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
147   JA_CN_FA_DM_EXPORT_PKG.Add_FA_Depreciation_Method(pn_legal_entity_id   => pn_legal_entity_id
148                                                    ,pv_accounting_year   => pv_accounting_year
149                                                    );
150   Ja_Cn_Utility.Print_Concurrent_Steps_For_Log(pv_step_number => '2.5'
151                                               ,pv_report_name => 'ASSET_USAGE'
152                                               ,pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
153   JA_CN_FA_AU_EXPORT_PKG.Add_FA_Usage(pn_legal_entity_id   => pn_legal_entity_id
154                                      ,pn_ledger_id         => pn_ledger_id
155                                      ,pv_accounting_year   => pv_accounting_year
156                                      ,pv_period_from       => pv_period_from
157                                      ,pv_period_to         => pv_period_to
158                                      ,pn_coa_id            => pn_coa_id
159                                       );
160   Ja_Cn_Utility.Print_Concurrent_Steps_For_Log(pv_step_number => '2.6'
161                                               ,pv_report_name => 'FIXED_ASSET_CARD'
162                                               ,pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
163   JA_CN_FA_AC_EXPORT_PKG.Add_FA_Asset_Card(pn_legal_entity_id   => pn_legal_entity_id
164                                           ,pn_ledger_id         => pn_ledger_id
165                                           ,pv_accounting_year   => pv_accounting_year
166                                           ,pv_period_from       => pv_period_from
167                                           ,pv_period_to         => pv_period_to
168                                           ,pn_coa_id            => pn_coa_id
169                                           );
170   Ja_Cn_Utility.Print_Concurrent_Steps_For_Log(pv_step_number => '2.7'
171                                               ,pv_report_name => 'FIXED_ASSET_CARD-REAL_ASE_INFO'
172                                               ,pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
173   JA_CN_FA_RAI_EXPORT_PKG.Add_FA_Real_Asset_Info(pn_legal_entity_id   => pn_legal_entity_id
174                                                 ,pn_ledger_id         => pn_ledger_id
175                                                 ,pv_accounting_year   => pv_accounting_year
176                                                 ,pv_period_from       => pv_period_from
177                                                 ,pv_period_to         => pv_period_to
178                                                 );
179   Ja_Cn_Utility.Print_Concurrent_Steps_For_Log(pv_step_number => '2.8'
180                                               ,pv_report_name => 'FIXED_ASSET_CARD_USAGE_INFO'
181                                               ,pv_module_name =>Ja_Cn_Utility.GV_MODULE_FA);
182   JA_CN_FA_ACUI_EXPORT_PKG.Add_FA_Usage_Info(pn_legal_entity_id   => pn_legal_entity_id
183                                             ,pn_ledger_id         => pn_ledger_id
184                                             ,pv_accounting_year   => pv_accounting_year
185                                             ,pv_period_from       => pv_period_from
186                                             ,pv_period_to         => pv_period_to
187                                              );
188   Ja_Cn_Utility.Print_Concurrent_Steps_For_Log(pv_step_number => '2.9'
189                                               ,pv_report_name => 'FIXED_ASSET_DECREASE_INFO'
190                                               ,pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
191   JA_CN_FA_ADI_EXPORT_PKG.Add_Asset_Decreasing_Info(pn_legal_entity_id   => pn_legal_entity_id
192                                                    ,pn_ledger_id         => pn_ledger_id
193                                                    ,pv_accounting_year   => pv_accounting_year
194                                                    ,pv_period_from       => pv_period_from
195                                                    ,pv_period_to         => pv_period_to
196                                                    );
197   Ja_Cn_Utility.Print_Concurrent_Steps_For_Log(pv_step_number => '2.10'
198                                               ,pv_report_name => 'FIXED_ASSET_DECR_INFO-REAL_ASE'
199                                               ,pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
200   JA_CN_FA_ADIRA_EXPORT_PKG.Add_Asset_Decreasing_Real_Info(pn_legal_entity_id   => pn_legal_entity_id
201                                                           ,pn_ledger_id         => pn_ledger_id
202                                                           ,pv_accounting_year   => pv_accounting_year
203                                                           ,pv_period_from       => pv_period_from
204                                                           ,pv_period_to         => pv_period_to
205                                                           );
206   Ja_Cn_Utility.Print_Concurrent_Steps_For_Log(pv_step_number => '2.11'
207                                               ,pv_report_name => 'FIXED_ASSET_MODIFICATION_INFO'
208                                               ,pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
209   JA_CN_FA_MI_EXPORT_PKG.Add_Asset_Modification_Info(pn_legal_entity_id   => pn_legal_entity_id
210                                                     ,pn_ledger_id         => pn_ledger_id
211                                                     ,pv_accounting_year   => pv_accounting_year
212                                                     ,pv_period_from       => pv_period_from
213                                                     ,pv_period_to         => pv_period_to
214                                                     );
215 
216   -- call the procedures to export the reports seperately here end
217 
218   FND_FILE.put_line(FND_FILE.output
219                    ,Ja_Cn_Utility.Get_XML_Tag('FIXED_ASSETS', Ja_Cn_Utility.GV_TAG_TYPE_END,Ja_Cn_Utility.GV_MODULE_FA));
220 
221   --logging for debug
222   IF (ln_proc_level >= ln_dbg_level)
223   THEN
224     FND_LOG.STRING( ln_proc_level
225                   , GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
226                    'Exit procedure');
227   END IF; -- (ln_proc_level>=ln_dbg_level)
228 EXCEPTION
229   WHEN OTHERS THEN
230     IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
231     THEN
232       FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
233                      GV_MODULE_PREFIX || '.' || lv_procedure_name ||
234                      '.Other_Exception ',
235                      SQLCODE || SQLERRM);
236     END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
237     FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
238     pv_retcode := 2;
239 END Export_Fixed_Assets;
240 
241 
242 --==========================================================================
243 --  PROCEDURE NAME:
244 --
245 --   Submit_Request                        Public
246 --
247 --  DESCRIPTION:
248 --
249 --    This procedure is to export report for fixed assets.
250 --
251 --  PARAMETERS:
252 --      Out: pv_errbuf                    NOCOPY VARCHAR2
253 --           pv_retcode                   NOCOPY VARCHAR2
254 --      In:  pn_coa_id                    NUMBER identifier of chart of account
255 --           pn_legal_entity_id           NUMBER identifier of legal entity
256 --           pn_ledger_id                 NUMBER identifier of ledger
257 --           pv_accounting_year           VARCHAR2 accounting year
258 --           pv_period_from               VARCHAR2 period from
259 --           pv_period_to                 VARCHAR2 period to
260 --           pv_source_char_set           VARCHAR2 source character set
261 --           pv_dest_char_set             VARCHAR2 destination character set
262 --
263 --  DESIGN REFERENCES:
264 --    CNAO_V2_FA_TD.doc
265 --
266 --  CHANGE HISTORY:
267 --      06-Apr-2010   Chongwu Li  created
268 --==========================================================================
269 
270 PROCEDURE Submit_Request
271 ( pv_errbuf                    OUT NOCOPY VARCHAR2
272 , pv_retcode                   OUT NOCOPY VARCHAR2
273 , pn_legal_entity_id           IN  NUMBER
274 , pn_ledger_id                 IN  NUMBER
275 , pv_accounting_year           IN  VARCHAR2
276 , pv_period_from               IN  VARCHAR2
277 , pv_period_to                 IN  VARCHAR2
278 , pn_coa_id                    IN  NUMBER
279 , pv_source_char_set           IN  VARCHAR2
280 , pv_dest_char_set             IN  VARCHAR2
281 )
282 IS
283 NO_DATA            EXCEPTION;
284 
285 lv_procedure_name         VARCHAR2(40) := 'Submit_Request';
286 ln_dbg_level              NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
287 ln_proc_level             NUMBER := FND_LOG.LEVEL_PROCEDURE;
288 ln_statement_level        NUMBER := FND_LOG.LEVEL_STATEMENT;
289 lv_error_status           BOOLEAN;
290 lv_period_from            VARCHAR2(100);
291 lv_period_to              VARCHAR2(100);
292 
293 ln_reqid_gle               NUMBER;
294 lv_gle_req_phase           fnd_lookup_values.meaning%TYPE;
295 lv_gle_req_status          fnd_lookup_values.meaning%TYPE;
296 lv_gle_req_status_code     fnd_lookup_values.lookup_code%TYPE;
297 
298 lv_error_flag              VARCHAR2(1);
299 
300 ln_waiting_interval        NUMBER   :=10;
301 lv_dev_phase               VARCHAR2(100);
302 lv_dev_status              VARCHAR2(100);
303 lv_message                 VARCHAR2(1000);
304 
305 ln_reqid_cvt               NUMBER;
306 lv_cvt_req_status          VARCHAR2(100);
307 
308 ln_reqid_chg               NUMBER;
309 lv_chg_req_status          VARCHAR2(100);
310 
311 lv_output_file_name        VARCHAR2(100);
312 
313 BEGIN
314   --logging for debug
315   IF (ln_proc_level>=ln_dbg_level)
316   THEN
317     FND_LOG.STRING( ln_proc_level
318                   , GV_MODULE_PREFIX ||'.' || lv_procedure_name ||
319                   '.begin'
320                   , 'Enter procedure'
321                   );
322 
323     -- logging the parameters
324     FND_LOG.STRING(ln_proc_level,
325                    lv_procedure_name ||
326                    '.parameters',
327                    'pn_legal_entity_id=' || pn_legal_entity_id|| ',' ||
328                    'pn_ledger_id=' || pn_ledger_id || ',' ||
329                    'pv_accounting_year=' || pv_accounting_year || ',' ||
330                    'pv_period_from=' || pv_period_from || ',' ||
331                    'pv_period_to=' || pv_period_to || ',' ||
332                    'pn_coa_id=' || pn_coa_id || ',' ||
333                    'pv_source_char_set=' || pv_source_char_set || ',' ||
334                    'pv_dest_char_set=' || pv_dest_char_set);
335   END IF; --l_proc_level>=l_dbg_level
336 
337   FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
338                    '.parameters:' ||
339                    'pn_legal_entity_id=' || pn_legal_entity_id|| ',' ||
340                    'pn_ledger_id=' || pn_ledger_id || ',' ||
341                    'pv_accounting_year=' || pv_accounting_year || ',' ||
342                    'pv_period_from=' || pv_period_from || ',' ||
343                    'pv_period_to=' || pv_period_to || ',' ||
344                    'pn_coa_id=' || pn_coa_id || ',' ||
345 		               'pv_source_char_set=' || pv_source_char_set || ',' ||
346                    'pv_dest_char_set=' || pv_dest_char_set);
347 
348   --Step1: Check system option
349   FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 1: Check System Option');
350   IF NOT(Ja_Cn_Utility.Check_Profile())
351   THEN
352     pv_retcode := 1;
353     pv_errbuf  := '';
354     RETURN;
355   END IF; --(Ja_Cn_Utility.Check_Profile())
356 
357   IF NOT(Check_kff(pn_coa_id))
358   THEN
359     pv_retcode := 1;
360     pv_errbuf  := '';
361     RETURN;
362   END IF; --(Check_kff(pn_coa_id))
363 
364   IF NOT(Check_Depreciation_Assignment(pn_legal_entity_id))
365   THEN
366     pv_retcode := 1;
367     pv_errbuf  := '';
368     RETURN;
369   END IF; --(Check_Depreciation_Assignment(pn_legal_entity_id))
370 
371   IF pv_period_from IS NULL
372   THEN
373     BEGIN
374       SELECT PERIOD_NAME
375         INTO lv_period_from
376         FROM (SELECT PERIOD_NAME
377                 FROM GL_PERIOD_STATUSES
378                WHERE APPLICATION_ID = 101
379                  AND LEDGER_ID = pn_ledger_id
380                  AND PERIOD_YEAR = pv_accounting_year
381                  AND CLOSING_STATUS <> 'N'
382                  AND CLOSING_STATUS <> 'F'
383                ORDER BY START_DATE)
384        WHERE ROWNUM = 1;
385      EXCEPTION
386           WHEN OTHERS THEN
387                NULL;
388      END;
389    ELSE
390      lv_period_from := pv_period_from;
391    END IF;  --pv_period_from IS NULL
392 
393   IF pv_period_to IS NULL
394   THEN
395     BEGIN
396       SELECT PERIOD_NAME
397         INTO lv_period_to
398         FROM (SELECT PERIOD_NAME
399                 FROM GL_PERIOD_STATUSES
400                WHERE APPLICATION_ID = 101
401                  AND LEDGER_ID = pn_ledger_id
402                  AND PERIOD_YEAR = pv_accounting_year
403                  AND CLOSING_STATUS <> 'N'
404                  AND CLOSING_STATUS <> 'F'
405                ORDER BY START_DATE DESC)
406        WHERE ROWNUM = 1;
407      EXCEPTION
408           WHEN OTHERS THEN
409                NULL;
410      END;
411    ELSE
412      lv_period_to := pv_period_to;
413 
414    END IF;  --pv_period_to IS NULL
415 
416 
417   --Step2: Submit FA Export program
418   FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 2: Submit FA Export program');
419   ln_reqid_gle:=FND_REQUEST.Submit_Request( application => 'JA'
420                                           , program     => 'JACNFAGP'
421                                           , argument1   => pn_legal_entity_id
422                                           , argument2   => pn_ledger_id
423                                           , argument3   => pv_accounting_year
424                                           , argument4   => lv_period_from
425                                           , argument5   => lv_period_to
426                                           , argument6   => pn_coa_id
427                                           );
428   COMMIT;
429   --Waiting for the 'Export Report for Fixed Assets' completed
430   --get its status
431 
432   IF ln_reqid_gle<>0
433   THEN
434 
435     IF FND_CONCURRENT.Wait_For_Request( request_id   => ln_reqid_gle
436                                       , interval     => ln_waiting_interval
437                                       , phase        => lv_gle_req_phase
438                                       , status       => lv_gle_req_status
439                                       , dev_phase    => lv_dev_phase
440                                       , dev_status   => lv_dev_status
441                                       , message      => lv_message
442                                       )
443     THEN
444       --To get lookup code for current status
445       SELECT
446         lookup_code
447       INTO
448         lv_gle_req_status_code
449       FROM
450         fnd_lookup_values
451       WHERE lookup_type = 'CP_STATUS_CODE'
452         AND view_application_id=0
453         AND security_group_id=0
454         AND meaning=lv_gle_req_status
455         AND enabled_flag='Y'
456         AND language = USERENV('LANG');
457 
458       --Completed with Normal
459       IF lv_gle_req_status_code='C'
460       THEN
461 
462         --Step3: Submit characrter set conversion program
463         --to convert charaterset of output file
464         FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 3: Submit Characrter Set Conversion program');
465         Ja_Cn_Utility.Submit_XML_Charset_Conversion( p_xml_request_id      => ln_reqid_gle
466                                                    , p_source_charset      => JA_CN_UTILITY.Get_XML_Encoding
467                                                    , p_destination_charset => pv_dest_char_set
468                                                    , p_source_separator    => NULL
469                                                    , x_charset_request_id  => ln_reqid_cvt
470                                                    , x_result_flag         => lv_cvt_req_status
471                                                    );
472         IF lv_cvt_req_status='Success'
473         THEN
474           --Step4: Submit "Change File Name" concurrent program
475           --to change name of output file
476           FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 4: Submit File Name Renaming program');
477           lv_output_file_name := Ja_Cn_Utility.Generate_File_name( pv_module_name     => JA_CN_UTILITY.GV_MODULE_FA
478                                                                  , pv_accounting_year => pv_accounting_year
479                                                                  , pv_ledger_id       => pn_ledger_id
480                                                                  , pv_from_period     => pv_period_from
481                                                                  , pv_to_period       => pv_period_to
482                                                                  );
483           --logging the variables
484           IF (ln_statement_level >= ln_dbg_level)
485           THEN
486             FND_LOG.STRING(ln_statement_level,
487                            lv_procedure_name,
488                            'lv_output_file_name:' || lv_output_file_name);
489           END IF;  --(ln_statement_level >= ln_dbg_level)
490           FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
491                            '.variable:' ||
492                            'lv_output_file_name =' || lv_output_file_name);
493 
494           JA_CN_UTILITY.Change_Output_Filename( p_xml_request_id       => ln_reqid_gle
495                                               , p_destination_charset  => pv_dest_char_set
496                                               , p_destination_filename => lv_output_file_name
497                                               , x_filename_request_id  => ln_reqid_chg
498                                               , x_result_flag          => lv_chg_req_status
499                                               );
500           IF lv_chg_req_status='Success'
501           THEN
502             NULL;
503           ELSIF  lv_chg_req_status='Warning'
504           THEN
505             lv_error_flag:='W';
506           ELSIF  lv_chg_req_status='Error'
507           THEN
508             lv_error_flag:='E';
509           END IF;--l_chg_req_status='Success'
510 
511         ELSIF lv_cvt_req_status='Warning'
512         THEN
513           lv_error_flag:='W';
514         ELSIF lv_cvt_req_status='Error'
515         THEN
516           lv_error_flag:='E';
517         END IF; --l_cvt_req_status='Success'
518 
519       --Completed with 'Warning'
520       ELSIF lv_gle_req_status_code='G'
521       THEN
522         lv_error_flag:='W';
523       --Completed with 'Error'
524       ELSIF lv_gle_req_status_code='E'
525       THEN
526         lv_error_flag:='E';
527       END IF; --FND_CONCURRENT.Wait_For_Request(request_id   => l_reqid_comb
528     ELSE
529       lv_error_flag:='E';
530     END IF; --FND_CONCURRENT.Wait_For_Request
531   ELSE
532     lv_error_flag:='E';
533   END IF; --ln_reqid_gle<>0
534 
535   --If any of above  concurrent porgrams is Warning/Failed, set current SI export
536   --program to status 'Warning'/'Error' accordingly.
537   IF lv_error_flag='W'
538   THEN
539     lv_error_status:=FND_CONCURRENT.Set_Completion_Status( status => 'WARNING'
540                                                          , message => ''
541                                                          );
542   ELSIF lv_error_flag='E'
543   THEN
544     lv_error_status:=FND_CONCURRENT.Set_Completion_Status( status => 'ERROR'
545                                                          , message => ''
546                                                          );
547   END IF;  --lv_error_flag='W'
548 
549   --logging for debug
550   IF (ln_proc_level >= ln_dbg_level)
551   THEN
552     FND_LOG.STRING( ln_proc_level
553                   ,GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
554                    'Exit procedure');
555   END IF; -- (ln_proc_level>=ln_dbg_level)
556 
557   --logging for debug
558   IF (ln_proc_level>=ln_dbg_level)
559   THEN
560     FND_LOG.STRING( ln_proc_level
561                   , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
562                   , 'Exit procedure'
563                   );
564   END IF; -- (ln_proc_level>=ln_dbg_level)
565 
566 EXCEPTION
567   WHEN OTHERS THEN
568   IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
569   THEN
570     FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
571                   , GV_MODULE_PREFIX || '.' || lv_procedure_name ||
572                    '.Other_Exception ',
573                    SQLCODE || SQLERRM);
574   END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
575   FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
576 
577 END Submit_Request;
578 
579 --==========================================================================
580   --  FUNCTION NAME:
581   --
582   --    Check_kff                             Public
583   --
584   --  DESCRIPTION:
585   --
586   --    This function is to check the flexfield assignment form, if any of the fields
587   --    has no value, the error message will be displayed.
588   --
589   --  PARAMETERS:
590   --      In:  pn_coa_id     coa ID
591   --  DESIGN REFERENCES:
592   --    CNAO_V2_FA_TD.doc
593   --
594   --  CHANGE HISTORY:
595   --
596   --           20-Apr-2010   Chongwu Li  created
597 FUNCTION Check_kff(pn_coa_id NUMBER)
598 RETURN BOOLEAN
599 IS
600 lv_False_Flag VARCHAR2(1) := 'N';
601 lv_Error_Msg  VARCHAR2(2000);
602 ln_Dbg_Level  NUMBER := Fnd_Log.g_Current_Runtime_Level;
603 ln_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
604 lv_Proc_Name  VARCHAR2(100) := 'Check_kff';
605 ln_row_count NUMBER := 0;
606 
607 CURSOR kff_cur(ln_coa_id NUMBER) IS
608    SELECT context_code, attribute_column
609      FROM ja_cn_dff_assignments_v
610     WHERE chart_of_accounts_id = pn_coa_id
611       AND (lookup_code = 'FAAU' OR lookup_code = 'FAUM');
612 BEGIN
613 
614     --log for debug
615   IF (ln_Proc_Level >= ln_Dbg_Level)
616   THEN
617       Fnd_Log.STRING(ln_Proc_Level,
618                      GV_MODULE_PREFIX || '.' || lv_Proc_Name || '.begin',
619                      'Enter function');
620   END IF; --(l_proc_level >= l_dbg_level)
621 
622   FOR v_row IN kff_cur(pn_coa_id)
623   LOOP
624       ln_row_count := ln_row_count +1;
625       IF (v_row.context_code IS NULL OR v_row.attribute_column IS NULL)
626       THEN
627         --Raise error message for caller
628         lv_False_Flag := 'Y';
629         Fnd_Message.Set_Name(Application => 'JA',
630                              NAME        => 'JA_CN_FLEXFIELD_ASSIGN_NOT_SET');
631         lv_Error_Msg := Fnd_Message.Get;
632 
633         --Output error message
634         Fnd_File.Put_Line(Fnd_File.Output, lv_Error_Msg);
635       END IF;  --(v_row.context_code IS NULL OR v_row.attribute_column IS NULL)
636   END LOOP;
637 
638 
639   IF (ln_row_count = 0)
640   THEN
641         lv_False_Flag := 'Y';
642         Fnd_Message.Set_Name(Application => 'JA',
643                              NAME        => 'JA_CN_FLEXFIELD_ASSIGN_NOT_SET');
644         lv_Error_Msg := Fnd_Message.Get;
645         --Output error message
646         Fnd_File.Put_Line(Fnd_File.Output, lv_Error_Msg);
647   END IF;  --ln_row_count = 0
648 
649 
650   IF lv_False_Flag = 'N'
651   THEN
652       --log for debug
653       IF (ln_Proc_Level >= ln_Dbg_Level) THEN
654         Fnd_Log.STRING(ln_Proc_Level,
655                        GV_MODULE_PREFIX || '.' || lv_Proc_Name || '.end',
656                        'Exit function');
657       END IF; --( ln_proc_level >= ln_dbg_level )
658       RETURN TRUE;
659   ELSE
660       --log for debug
661     IF (ln_Proc_Level >= ln_Dbg_Level)
662     THEN
663         Fnd_Log.STRING(ln_Proc_Level,
664                        GV_MODULE_PREFIX || '.' || lv_Proc_Name || '.end',
665                        'Exit function');
666     END IF; --( ln_proc_level >= ln_dbg_level )
667     RETURN FALSE;
668   END IF; --lv_false_flage='N'
669 
670 EXCEPTION
671 
672   WHEN OTHERS
673   THEN
674     IF (ln_Proc_Level >= ln_Dbg_Level)
675     THEN
676         Fnd_Log.STRING(ln_Proc_Level,
677                        GV_MODULE_PREFIX || '.' || lv_Proc_Name ||
678                        '. Other_Exception ',
679                        SQLCODE || ':' || SQLERRM);
680     END IF; --(ln_proc_level >= ln_dbg_level)
681 
682 END Check_kff;
683 
684 --==========================================================================
685   --  FUNCTION NAME:
686   --
687   --    Check_Depreciation_Assignment                        Public
688   --
689   --  DESCRIPTION:
690   --
691   --    This function is to check depreciation method assignment form, if there
692   --    is not depreciation method assignment for the legal entity, the error
693   --    message will be displayed.
694   --
695   --  PARAMETERS:
696   --      In:  pn_legal_entity_id     legal_entity ID
697   --  DESIGN REFERENCES:
698   --    CNAO_V2_FA_TD.doc
699   --
700   --  CHANGE HISTORY:
701   --
702   --           12-MAY-2010   Qingyi Wang  created
703 FUNCTION Check_Depreciation_Assignment(pn_legal_entity_id NUMBER)
704 RETURN BOOLEAN
705 IS
706 ln_row_count             NUMBER := 0;
707 lv_Error_Msg             VARCHAR2(2000);
708 ln_dbg_level             NUMBER  := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
709 ln_proc_level            NUMBER  := FND_LOG.LEVEL_PROCEDURE;
710 ln_statement_level       NUMBER  := FND_LOG.LEVEL_STATEMENT;
711 lv_procedure_name        VARCHAR2(40) := 'Check_Depreciation_Assignment';
712 
713 BEGIN
714 
715   IF (ln_proc_level>=ln_dbg_level)
716     THEN
717     FND_LOG.STRING( ln_proc_level
718                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name ||
719                     '.begin'
720                     , 'Enter procedure'
721                     );
722 
723      -- logging the parameters
724      FND_LOG.STRING(ln_proc_level,
725                      lv_procedure_name ||
726                      '.parameters',
727                      'pn_legal_entity_id=' || pn_legal_entity_id);
728   END IF; --l_proc_level>=l_dbg_level
729 
730   SELECT COUNT(*) INTO ln_row_count
731   FROM ja_cn_dep_meth_assignment
732   WHERE legal_entity_id = pn_legal_entity_id;
733 
734   IF(ln_row_count = 0)
735   THEN
736 
737     Fnd_Message.Set_Name(Application => 'JA',
738                              NAME        => 'JA_CN_NO_DEPRN_METHOD');
739         lv_Error_Msg := Fnd_Message.Get;
740         --Output error message
741         Fnd_File.Put_Line(Fnd_File.Output, lv_Error_Msg);
742     --logging for debug
743     IF (ln_proc_level>=ln_dbg_level)
744     THEN
745       FND_LOG.STRING( ln_proc_level
746                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
747                     , 'Exit procedure'
748                     );
749     END IF; -- (ln_proc_level>=ln_dbg_level)
750     RETURN FALSE;
751   ELSE
752     --logging for debug
753     IF (ln_proc_level>=ln_dbg_level)
754     THEN
755       FND_LOG.STRING( ln_proc_level
756                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
757                     , 'Exit procedure'
758                     );
759     END IF; -- (ln_proc_level>=ln_dbg_level)
760     RETURN TRUE;
761   END IF; --(IF(ln_row_count = 0))
762 
763 EXCEPTION
764 
765   WHEN OTHERS THEN
766       IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
767       THEN
768         FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
769                        GV_MODULE_PREFIX || '.' || lv_procedure_name ||
770                        '.Other_Exception ',
771                        SQLCODE || SQLERRM);
772       END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
773       FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
774 
775 END Check_Depreciation_Assignment;
776 
777 END JA_CN_FA_EXPORT_PROG;
778