[Home] [Help]
PACKAGE BODY: APPS.JA_CN_ETC_CONC_PROG
Source
1 PACKAGE BODY Ja_Cn_Etc_Conc_Prog AS
2 --$Header: JACNETCB.pls 120.1.12000000.1 2007/08/13 14:09:32 qzhao noship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation
5 --| Redwood Shores, CA, USA
6 --| All rights reserved.
7 --+=======================================================================
8 --| FILENAME
9 --| JACNETCB.pls
10 --|
11 --| DESCRIPTION
12 --|
13 --| This is a wrapper package for submission of export
14 --| statement related concurrent programs
15 --|
16 --|
17 --| PROCEDURE LIST
18 --| itemize_ap_journals;
19 --| itemize_ar_journals;
20 --| itemize_pa_journals;
21 --| itemize_po_journals;
22 --| itemize_inv_journals;
23 --| itemize_fa_journals;
24 --| transfer_gl_journals;
25 --| get_description_from_gis;
26 --| post_journal_itemized;
27 --| EAB_Export;
28 --| MultiOrg_Maintain;
29 --| Export_Coa
30 --| Ent_GSSM_Export
31 --| Pub_GSSM_Export
32 --| JOURNAL_ENTRY_EXPORT
33 --| ACCOUNT_BALANCE_EXPORT
34 --| JOURNAL_ENTRY_GENERATION
35 --| ACCOUNT_BALANCE_GENERATION
36 --|
37 --| HISTORY
38 --| 29-Mar-2006 Qingjun Zhao Created
39 --| 29-Mar-2006 Jackey Li Added two procedures
40 --| EAB_Export and MultiOrg_Maintain
41 --| 29-Mar-2006 Andrew Liu Added Export_Coa
42 --| 17-May-2006 Andrew Liu Added Ent_GSSM_Export and Pub_GSSM_Export
43 --| 19-Jun-2006 Joseph Wang added JOURNAL_ENTRY_EXPORT and ACCOUNT_BALANCE_EXPORT
44 --| 20-Jun-2006 Jackey Li added one procedure EAB_Export_Wrapper
45 --| 21-Jun-2006 Joseph Wang added JOURNAL_ENTRY_GENERATION and ACCOUNT_BALANCE_GENERATION
46 --| 05-July-2006 Joseph Wang modified the procedure JOURNAL_ENTRY_GENERATION, ACCOUNT_BALANCE_GENERATION
47 --| JOURNAL_ENTRY_EXPORT and ACCOUNT_BALANCE_EXPORT for adding report range support
48 --+======================================================================*/
49 l_Module_Prefix VARCHAR2(100) := 'JA_CN_ETC_CONC_PROG';
50
51 --==========================================================================
52 -- PROCEDURE NAME:
53 --
54 -- transfer_gl_sla_to_cnao Public
55 --
56 -- DESCRIPTION:
57 --
58 -- The 'transfer_gl_sla_to_cnao' procedure accepts parameters from
59 -- concurrent program 'Account and Journal Itemization Program' and
60 -- calls another procedure
61 -- 'JA_CN_ACC_JE_ITEMIZATION_PKG.transfer_gl_sla_to_cnao'
62 -- with parameters after processing.
63 --
64 -- PARAMETERS:
65 -- In: p_legal_entity_id Identifier of legal entity
66 -- parameter for FSG report
67 -- p_period_name GL period Name
68 --
69 -- Out: errbuf
70 -- retcode
71 --
72 --
73 -- DESIGN REFERENCES:
74 --
75 --
76 -- CHANGE HISTORY:
77 --
78 -- 29-Mar-2006 Qingjun Zhao Created
79 --
80 --===========================================================================
81
82 PROCEDURE Transfer_Gl_Sla_To_Cnao(Errbuf OUT NOCOPY VARCHAR2,
83 Retcode OUT NOCOPY VARCHAR2,
84 p_Chart_Of_Accounts_Id IN NUMBER,
85 p_Ledger_Id IN NUMBER,
86 p_Legal_Entity_Id IN NUMBER,
87 p_Period_Name IN VARCHAR2) IS
88
89 l_Flag NUMBER;
90 l_Error_Flag VARCHAR2(1) := 'N';
91 l_Error_Status BOOLEAN;
92
93 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
94 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
95 l_Proc_Name VARCHAR2(100) := 'transfer_gl_sla_to_cnao';
96
97 BEGIN
98 --log for debug
99 IF (l_Proc_Level >= l_Dbg_Level) THEN
100 Fnd_Log.STRING(l_Proc_Level,
101 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
102 'Enter procedure');
103 Fnd_Log.STRING(l_Proc_Level,
104 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
105 'p_period_name:' || p_Period_Name);
106 Fnd_Log.STRING(l_Proc_Level,
107 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
108 'p_legal_entity_id:' || p_Legal_Entity_Id);
109 END IF; --(l_proc_level >= l_dbg_level)
110
111 IF Ja_Cn_Utility.Check_Profile THEN
112 Ja_Cn_Acc_Je_Itemization_Pkg.Transfer_Gl_Sla_To_Cnao(Errbuf => Errbuf,
113 Retcode => Retcode,
114 p_Chart_Of_Accounts_Id => p_Chart_Of_Accounts_Id,
115 p_Ledger_Id => p_Ledger_Id,
116 p_Legal_Entity_Id => p_Legal_Entity_Id,
117 p_Period_Name => p_Period_Name);
118 ELSE
119 l_Error_Flag := 'Y';
120 END IF; --JA_CN_UTILITY.Check_Profile
121
122 --If above check failed, then set status of concurrent program as warning
123
124 IF l_Error_Flag = 'Y' THEN
125 l_Error_Status := Fnd_Concurrent.Set_Completion_Status(Status => 'WARNING',
126 Message => '');
127
128 END IF; --l_error_flag = 'Y'
129
130 --log for debug
131 IF (l_Proc_Level >= l_Dbg_Level) THEN
132 Fnd_Log.STRING(l_Proc_Level,
133 l_Module_Prefix || '.' || l_Proc_Name || '.end',
134 'Exit procedure');
135 END IF; --( l_proc_level >= l_dbg_level )
136
137 EXCEPTION
138 WHEN OTHERS THEN
139 IF (l_Proc_Level >= l_Dbg_Level) THEN
140 Fnd_Log.STRING(l_Proc_Level,
141 l_Module_Prefix || '.' || l_Proc_Name ||
142 '. Other_Exception ',
143 SQLCODE || ':' || SQLERRM);
144 END IF; --(l_proc_level >= l_dbg_level)
145 END Transfer_Gl_Sla_To_Cnao;
146
147 --==========================================================================
148 -- PROCEDURE NAME:
149 -- post_journal_itemized Public
150 --
151 -- DESCRIPTION:
152 -- The ' post_journal_itemized' procedure accepts parameters from
153 -- concurrent program 'Post itemized journals'
154 -- and calls another procedure
155 -- 'JA_CN_POST_UTILITY_PK.post_journal_itemized'
156 -- with parameters after processing.
157 --
158 -- PARAMETERS:
159 -- Out: errbuf Mandatory parameter for PL/SQL concurrent programs
160 -- Out: retcode Mandatory parameter for PL/SQL concurrent programs
161 -- In: p_period_name the end period name in which
162 -- the CNAO journal should be processed
163 -- p_ledger_id ledger ID
164 -- p_legal_entity_ID Legal entity id
165
166 --
167 -- DESIGN REFERENCES:
168 -- None
169 --
170 -- CHANGE HISTORY:
171 -- 29-Mar-2006 Qingjun Zhao Created
172 --===========================================================================
173 PROCEDURE Post_Journal_Itemized(Errbuf OUT NOCOPY VARCHAR2,
174 Retcode OUT NOCOPY VARCHAR2,
175 p_Period_Name IN VARCHAR2,
176 p_ledger_Id IN NUMBER,
177 p_Legal_Entity_Id IN NUMBER) IS
178 l_Flag NUMBER;
179 l_Error_Flag VARCHAR2(1) := 'N';
180 l_Error_Status BOOLEAN;
181
182 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
183 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
184 l_Proc_Name VARCHAR2(100) := 'post_journal_itemized';
185
186 BEGIN
187 --log for debug
188 IF (l_Proc_Level >= l_Dbg_Level) THEN
189 Fnd_Log.STRING(l_Proc_Level,
190 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
191 'Enter procedure');
192 Fnd_Log.STRING(l_Proc_Level,
193 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
194 'p_period_name:' || p_Period_Name);
195 Fnd_Log.STRING(l_Proc_Level,
196 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
197 'p_Ledger_id :' || P_ledger_Id);
198 Fnd_Log.STRING(l_Proc_Level,
199 l_Module_Prefix || '.' || l_Proc_Name || '.parameters',
200 'p_legal_entity_ID:' || p_Legal_Entity_Id);
201 END IF; --(l_proc_level >= l_dbg_level)
202
203 IF Ja_Cn_Utility.Check_Profile THEN
204 Ja_Cn_Post_Utility_Pkg.Post_Journal_Itemized(p_Period_Name => p_Period_Name,
205 p_ledger_Id => p_ledger_Id,
206 p_Legal_Entity_Id => p_Legal_Entity_Id);
207 ELSE
208 l_Error_Flag := 'Y';
209 END IF; --JA_CN_UTILITY.Check_Profile
210
211 --If above check failed, then set status of concurrent program as warning
212 IF l_Error_Flag = 'Y' THEN
213 l_Error_Status := Fnd_Concurrent.Set_Completion_Status(Status => 'WARNING',
214 Message => '');
215
216 END IF; --l_error_flag = 'Y'
217
218 --log for debug
219 IF (l_Proc_Level >= l_Dbg_Level) THEN
220 Fnd_Log.STRING(l_Proc_Level,
221 l_Module_Prefix || '.' || l_Proc_Name || '.end',
222 'Exit procedure');
223 END IF; --( l_proc_level >= l_dbg_level )
224
225 EXCEPTION
226 WHEN OTHERS THEN
227 IF (l_Proc_Level >= l_Dbg_Level) THEN
228 Fnd_Log.STRING(l_Proc_Level,
229 l_Module_Prefix || '.' || l_Proc_Name ||
230 '. Other_Exception ',
231 SQLCODE || ':' || SQLERRM);
232 END IF; --(l_proc_level >= l_dbg_level)
233 RAISE;
234 END Post_Journal_Itemized;
235
236 --==========================================================================
237 -- PROCEDURE NAME:
238 -- EAB_Export Public
239 --
240 -- DESCRIPTION:
241 -- This procedure accepts parameters from concurrent program
242 -- 'Electronic Accounting Book Export'
243 -- and calls another procedure 'JA_CN_EAB_EXPORT_PKG.Execute_Export'
244 -- with parameters after processing.
245 --
246 -- PARAMETERS:
247 -- In: P_COA_ID Current chart of accounts ID
248 -- p_le_id current legal entity ID
249 -- P_LEDGER_ID Current ledger ID
250 -- p_fiscal_year fiscal year under current sob
251 --
252 -- Out: errbuf
253 -- retcode
254 --
255 -- DESIGN REFERENCES:
256 -- CNAO_Electronic_Accounting_Book_Export.doc
257 --
258 -- CHANGE HISTORY:
259 -- 03/29/2006 Jackey Li Created
260 -- 05/09/2007 Yucheng Sun Updated
261 --===========================================================================
262
263 PROCEDURE EAB_Export(ERRBUF OUT NOCOPY VARCHAR2
264 ,RETCODE OUT NOCOPY VARCHAR2
265 ,P_COA_ID IN NUMBER
266 ,P_LE_ID IN NUMBER
267 ,P_LEDGER_ID IN NUMBER
268 ,p_fiscal_year VARCHAR2) IS
269
270 l_ledger_id NUMBER:= P_LEDGER_ID ;
271 l_le_id NUMBER:=P_LE_ID;
272 l_coa_id NUMBER:=P_COA_ID;
273
274 l_flag NUMBER:=0;
275 l_error_flag VARCHAR2(1) := 'N';
276 l_error_status BOOLEAN;
277
278 l_dbg_level NUMBER := FND_LOG.G_Current_Runtime_Level;
279 l_proc_level NUMBER := FND_LOG.Level_Procedure;
280 l_proc_name VARCHAR2(100) := 'EAB_Export';
281
282 BEGIN
283 --log for debug
284 IF (l_proc_level >= l_dbg_level)
285 THEN
286 FND_LOG.STRING(l_proc_level,
287 l_module_prefix || '.' || l_proc_name || '.begin',
288 'Enter procedure');
289 FND_LOG.STRING(l_proc_level,
290 l_module_prefix || '.' || l_proc_name || '.parameters',
291 'p_le_id is ' || p_le_id);
292 FND_LOG.STRING(l_proc_level,
293 l_module_prefix || '.' || l_proc_name || '.parameters',
294 'p_fiscal_year is ' || p_fiscal_year);
295 END IF; --(l_proc_level >= l_dbg_level)
296
297 IF JA_CN_UTILITY.Check_Profile THEN
298 JA_CN_EAB_EXPORT_PKG.Execute_Export(P_COA_ID => l_coa_id
299 ,p_le_id => l_le_id
300 ,P_LEDGER_ID => l_ledger_id
301 ,p_fiscal_year => p_fiscal_year);
302 ELSE
303 l_error_flag := 'Y';
304 retcode := 1;
305 errbuf := '';
306 RETURN;
307 END IF; --JA_CN_UTILITY.Check_Profile
308
309 --If above check failed, then set status of concurrent program as warning
310
311 /* IF l_error_flag = 'Y'
312 THEN
313 l_error_status := FND_CONCURRENT.Set_Completion_Status(status => 'WARNING',
314 message => '');
315 END IF; --l_error_flag = 'Y'
316 */
317 --log for debug
318 IF (l_proc_level >= l_dbg_level)
319 THEN
320 FND_LOG.STRING(l_proc_level,
321 l_module_prefix || '.' || l_proc_name || '.end',
322 'Exit procedure');
323 END IF; --( l_proc_level >= l_dbg_level )
324
325 EXCEPTION
326 WHEN OTHERS THEN
327 IF (l_proc_level >= l_dbg_level)
328 THEN
329 FND_LOG.STRING(l_proc_level,
330 l_module_prefix || '.' || l_proc_name ||
331 '. Other_Exception ',
332 SQLCODE || ':' || SQLERRM);
333 END IF; --(l_proc_level >= l_dbg_level)
334 RAISE;
335
336 END EAB_Export;
337
338 --==========================================================================
339 -- PROCEDURE NAME:
340 -- EAB_Export_Wrapper Public
341 --
342 -- DESCRIPTION:
343 --
344 --
345 -- PARAMETERS:
346 -- In: P_COA_ID current chart of accounts ID
347 -- p_ledger_id current ledger ID
348 -- p_le_id current legal entity ID
349 -- p_fiscal_year fiscal year under current sob
350 -- p_src_charset
351 -- p_dest_charset
352 -- p_separator
353 -- p_file_name
354 --
355 -- Out: errbuf
356 -- retcode
357 --
358 -- DESIGN REFERENCES:
359 -- CNAO_Electronic_Accounting_Book_Export.doc
360 --
361 -- CHANGE HISTORY:
362 -- 03/29/2006 Jackey Li Created
363 -- 05/09/2007 Yucheng Sun Updated
364 --===========================================================================
365 PROCEDURE Eab_Export_Wrapper(Errbuf OUT NOCOPY VARCHAR2,
366 Retcode OUT NOCOPY VARCHAR2,
367 P_COA_Id IN NUMBER,
368 p_Le_Id IN NUMBER,
369 p_ledger_id IN NUMBER,
370 p_Fiscal_Year IN VARCHAR2,
371 p_Src_Charset IN VARCHAR2,
372 p_Dest_Charset IN VARCHAR2,
373 p_Separator IN VARCHAR2,
374 p_File_Name IN VARCHAR2) IS
375
376 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
377 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
378 l_Proc_Name VARCHAR2(100) := 'EAB_Export_Wrapper';
379
380 l_Reqid NUMBER(15); -- Request id for the 'EAB export'
381 l_Chr_Reqid NUMBER(15); -- Request id for the 'Charset Convert'
382 l_Fn_Reqid NUMBER(15); -- Request id for the 'Change filename'
383 l_Chr_Result VARCHAR2(10);
384 l_Fn_Result VARCHAR2(10);
385
386 l_Err_Msg VARCHAR2(1000) := NULL;
387 l_Err_Code VARCHAR(1) := 'N';
388
389 l_Req_Phase Fnd_Lookup_Values.Lookup_Code%TYPE;
390 l_Req_Status Fnd_Lookup_Values.Lookup_Code%TYPE;
391 l_User_Phase Fnd_Lookup_Values.Meaning%TYPE;
392 l_User_Status Fnd_Lookup_Values.Meaning%TYPE;
393
394 BEGIN
395 --log for debug
396 IF (l_Proc_Level >= l_Dbg_Level) THEN
397 Fnd_Log.STRING(l_Proc_Level,
398 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
399 'Enter procedure');
400 END IF; --(l_proc_level >= l_dbg_level)
401
402 -- submit EAB export CP
403 l_Reqid := Fnd_Request.Submit_Request(Application => 'JA',
404 Program => 'JACNEABE',
405 Argument1 => P_COA_Id,
406 Argument2 => p_Le_Id,
407 Argument3 => p_ledger_id,
408 argument4 => p_Fiscal_Year);
409 COMMIT;
410
411 --log for debug
412 IF (l_Proc_Level >= l_Dbg_Level) THEN
413 Fnd_Log.STRING(l_Proc_Level,
414 l_Module_Prefix || '.' || l_Proc_Name,
415 'submit EAB export CP');
416 END IF; --( g_stmt_level >= g_dbg_level)
417
418 IF l_Reqid <> 0 THEN
419 IF Fnd_Concurrent.Wait_For_Request(Request_Id => l_Reqid,
420 INTERVAL => 5,
421 Phase => l_User_Phase,
422 Status => l_User_Status,
423 Dev_Phase => l_Req_Phase,
424 Dev_Status => l_Req_Status,
425 Message => l_Err_Msg) THEN
426 IF l_Req_Status = 'NORMAL' THEN
427 -- submit charset conversiong
428 Ja_Cn_Utility.Submit_Charset_Conversion(p_Xml_Request_Id => l_Reqid,
429 p_Source_Charset => p_Src_Charset,
430 p_Destination_Charset => p_Dest_Charset,
431 p_Source_Separator => p_Separator,
432 x_Charset_Request_Id => l_Chr_Reqid,
433 x_Result_Flag => l_Chr_Result);
434 IF l_Chr_Result = 'Success' THEN
435 -- submit change output filename
436 Ja_Cn_Utility.Change_Output_Filename(p_Xml_Request_Id => l_Reqid,
437 p_Destination_Charset => p_Dest_Charset,
438 p_Destination_Filename => p_File_Name,
439 x_Filename_Request_Id => l_Fn_Reqid,
440 x_Result_Flag => l_Fn_Result);
441 IF l_Fn_Result = 'Success' THEN
442 l_Err_Code := 'N';
443 Retcode := 0;
444 ELSIF l_Fn_Result = 'Warning' THEN
445 l_Err_Code := 'W';
446 Retcode := 1;
447 ELSE
448 l_Err_Code := 'E';
449 Retcode := 2;
450 END IF; -- IF l_fn_result = 'Success'
451
452 ELSIF l_Chr_Result = 'Warning' THEN
453 l_Err_Code := 'W';
454 Retcode := 1;
455 ELSE
456 l_Err_Code := 'E';
457 Retcode := 2;
458 END IF; --l_chr_result = 'Success'
459
460 ELSIF l_Req_Status = 'WARNING' THEN
461 l_Err_Code := 'W';
462 Retcode := 1;
463 Errbuf := l_Err_Msg;
464 ELSE
465 l_Err_Code := 'E';
466 Retcode := 2;
467 Errbuf := l_Err_Msg;
468 END IF; -- IF l_user_status = 'NORMAL'
469 END IF; --FND_CONCURRENT.Wait_For_Request
470 END IF; --l_reqid <> 0
471
472 IF (l_Proc_Level >= l_Dbg_Level) THEN
473 Fnd_Log.STRING(l_Proc_Level,
474 l_Module_Prefix || '.' || l_Proc_Name || '.end',
475 'Exit procedure');
476 END IF;
477
478 EXCEPTION
479 WHEN OTHERS THEN
480 IF (l_Proc_Level >= l_Dbg_Level) THEN
481 Fnd_Log.STRING(l_Proc_Level,
482 l_Module_Prefix || '.' || l_Proc_Name ||
483 '. Other_Exception ',
484 SQLCODE || ':' || SQLERRM);
485 END IF; --(l_proc_level >= l_dbg_level)
486 RAISE;
487
488 END Eab_Export_Wrapper;
489
490
491 --==========================================================================
492 -- PROCEDURE NAME:
493 -- Export_Coa Public
494 --
495 -- DESCRIPTION:
496 -- This procedure checks profile and General Information of
497 -- System Options Form, then calls program of COA Export, including
498 -- Natural Account and 4 Subsidiary Account of "Project", "Third Party",
499 -- "Cost Center" and "Personnel".
500 --
501 -- PARAMETERS:
502 -- Out: errbuf NOCOPY VARCHAR2
503 -- Out: retcode NOCOPY VARCHAR2
504 -- In: P_COA_ID NUMBER Chart of accounts ID
505 -- In: P_LE_ID NUMBER ID of Legal Entity
506 -- In: P_LEDGER_ID NUMBER ID of ledger
507 -- In: P_ACCOUNT_TYPE VARCHAR2 Type of the account
508 -- In: P_XML_TEMPLATE_LANGUAGE VARCHAR2 template language of NA exception report
509 -- In: P_XML_TEMPLATE_TERRITORY VARCHAR2 template territory of NA exception report
510 -- In: P_XML_OUTPUT_FORMAT VARCHAR2 output format of NA exception report
511 --
512 -- DESIGN REFERENCES:
513 -- None
514 --
515 -- CHANGE HISTORY:
516 -- 03/03/2006 Andrew Liu Created
517 -- 04/24/2007 Yucheng Sun Updated
518 --===========================================================================
519
520 PROCEDURE Export_Coa(errbuf OUT NOCOPY VARCHAR2,
521 retcode OUT NOCOPY VARCHAR2,
522 P_COA_ID IN NUMBER,
523 P_LE_ID IN NUMBER,
524 P_Ledger_Id IN NUMBER,
525 P_ACCOUNT_TYPE IN VARCHAR2,
526 P_XML_TEMPLATE_LANGUAGE IN VARCHAR2 default 'zh',
527 P_XML_TEMPLATE_TERRITORY IN VARCHAR2 default '00',
528 P_XML_OUTPUT_FORMAT IN VARCHAR2 default 'RTF') IS
529 l_dbg_level NUMBER := FND_LOG.G_Current_Runtime_Level;
530 l_proc_level NUMBER := FND_LOG.Level_Procedure;
531 l_proc_name VARCHAR2(100) := 'Export_Coa';
532
533 JA_CN_MISSING_BOOK_INFO exception;
534 l_msg_miss_book_info varchar2(2000);
535
536 l_le_id NUMBER := P_LE_ID;
537 l_ledger_id NUMBER := P_Ledger_Id;
538 l_sob_id NUMBER;
539 l_coa_id NUMBER := P_COA_ID;
540 l_sob_coa_flag NUMBER;
541
542 l_flag NUMBER;
543 l_book_name JA_CN_SYSTEM_PARAMETERS_ALL.BOOK_NAME%TYPE;
544 l_book_num JA_CN_SYSTEM_PARAMETERS_ALL.BOOK_NUM%TYPE;
545 l_com_name JA_CN_SYSTEM_PARAMETERS_ALL.COMPANY_NAME%TYPE;
546 l_org_id JA_CN_SYSTEM_PARAMETERS_ALL.ORGANIZATION_ID%TYPE;
547 l_ent_qty JA_CN_SYSTEM_PARAMETERS_ALL.ENT_QUALITY%TYPE;
548 l_ent_ind JA_CN_SYSTEM_PARAMETERS_ALL.ENT_INDUSTRY%TYPE;
549 --l_acc_str JA_CN_SYSTEM_PARAMETERS_ALL..ACCOUNT_STRUCTURE_ID%TYPE;
550 --l_ent_flg JA_CN_SYSTEM_PARAMETERS_ALL.ENT_FLAG%TYPE;
551
552 --Cursor to get General Information from table JA_CN_SYSTEM_PARAMETERS_ALL of current SOB.
553 CURSOR c_general_info IS
554 SELECT BOOK_NAME,
555 BOOK_NUM,
556 COMPANY_NAME,
557 ORGANIZATION_ID,
558 ENT_QUALITY,
559 ENT_INDUSTRY
560 --,ACCOUNT_STRUCTURE_ID
561 --,ENT_FLAG
562 FROM JA_CN_SYSTEM_PARAMETERS_ALL
563 WHERE LEGAL_ENTITY_ID = l_le_id;
564
565 BEGIN
566 --log for debug
567 IF (l_proc_level >= l_dbg_level) THEN
568 FND_LOG.String(l_proc_level,
569 l_module_prefix || '.' || l_proc_name || '.begin',
570 'Enter procedure');
571 FND_LOG.String(l_proc_level,
572 l_module_prefix || '.' || l_proc_name || '.parameters',
573 'P_LE_ID ' || P_LE_ID);
574 FND_LOG.String(l_proc_level,
575 l_module_prefix || '.' || l_proc_name || '.parameters',
576 'P_ACCOUNT_TYPE ' || P_ACCOUNT_TYPE);
577 END IF; --(l_proc_level >= l_dbg_level)
578
579 l_flag := 0;
580 --Call the function JA_CN_CFS_UTILITY.Check_Profile to check if all required profiles has been
581 -- properly set for current responsibility. If No, the function will raise corresponding error
582 -- messages, and the concurrent program will not continue performing next logics,these required
583 -- profiles include ' JG: Product', which should be set to 'Asia/Pacific Localizations',
584 -- 'JG: Territory', which should be set to 'China' and 'JA: CNAO Legal Entity', which should be
585 -- NOT NULL.
586 IF JA_CN_UTILITY.Check_Profile THEN
587 /*--Get sob id
588 JA_CN_UTILITY.Get_SOB_And_COA(l_le_id,
589 l_sob_id,
590 l_coa_id,
591 l_sob_coa_flag);
592
593 IF l_sob_coa_flag = -1 THEN
594 retcode := 1;
595 errbuf := '';
596 RETURN;
597 END IF;*/
598
599 --Check whether any field in the "Accounting Book information" area of tab
600 -- "General Information" of form "System options" defined or not
601 OPEN c_general_info;
602 FETCH c_general_info
603 INTO l_book_name, l_book_num, l_com_name, l_org_id, l_ent_qty, l_ent_ind
604 --,l_acc_str
605 --,l_ent_flg
606 ;
607 IF c_general_info%NOTFOUND OR l_book_name is null OR
608 l_book_num is null OR l_com_name is null OR l_org_id is null OR
609 l_ent_qty is null OR l_ent_ind is null
610 -- OR l_acc_str is null --OR l_ent_flg is null
611 THEN
612 l_flag := 1;
613 RAISE JA_CN_MISSING_BOOK_INFO;
614 END IF;
615 CLOSE c_general_info;
616 ELSE
617 retcode := 1;
618 errbuf := '';
619 RETURN;
620 END IF; -- JA_CN_UTILITY.Check_Profile
621
622 IF l_flag = 0 THEN
623 JA_CN_COA_EXP_PKG.Coa_Export(errbuf => errbuf,
624 retcode => retcode,
625 p_COA_ID => l_coa_id,
626 P_LEDGER_ID => l_ledger_id,
627 P_LE_ID => l_le_id,
628 P_ACCOUNT_TYPE => P_ACCOUNT_TYPE,
629 P_XML_TEMPLATE_LANGUAGE => P_XML_TEMPLATE_LANGUAGE,
630 P_XML_TEMPLATE_TERRITORY => P_XML_TEMPLATE_TERRITORY,
631 P_XML_OUTPUT_FORMAT => P_XML_OUTPUT_FORMAT);
632 END IF;
633
634
635 --log for debug
636 IF (l_proc_level >= l_dbg_level) THEN
637 FND_LOG.String(l_proc_level,
638 l_module_prefix || '.' || l_proc_name || '.end',
639 'Exit procedure');
640 END IF; --(l_proc_level >= l_dbg_level)
641
642 EXCEPTION
643 WHEN JA_CN_MISSING_BOOK_INFO THEN
644 FND_MESSAGE.Set_Name(APPLICATION => 'JA',
645 NAME => 'JA_CN_MISSING_BOOK_INFO');
646 l_msg_miss_book_info := FND_MESSAGE.Get;
647
648 FND_FILE.put_line(FND_FILE.output, l_msg_miss_book_info);
649 IF (l_proc_level >= l_dbg_level) THEN
650 FND_LOG.String(l_proc_level,
651 l_module_prefix || '.' || l_proc_name ||
652 '.JA_CN_MISSING_BOOK_INFO ',
653 l_msg_miss_book_info);
654 END IF;
655 retcode := 1;
656 errbuf := l_msg_miss_book_info;
657 WHEN OTHERS THEN
658 IF (l_proc_level >= l_dbg_level) THEN
659 FND_LOG.String(l_proc_level,
660 l_module_prefix || '.' || l_proc_name ||
661 '.Other_Exception ',
662 SQLCODE || ':' || SQLERRM);
663 END IF; --(l_proc_level >= l_dbg_level)
664 retcode := 2;
665 errbuf := SQLCODE || ':' || SQLERRM;
666 END Export_Coa;
667
668 --==========================================================================
669 -- PROCEDURE NAME:
670 -- Export_Coa_Entry Public
671 --
672 -- DESCRIPTION:
673 -- This procedure submits the program of COA Exports, and then changes
674 -- the output file's CharSet and file name.
675 --
676 -- PARAMETERS:
677 -- Out: errbuf NOCOPY VARCHAR2
678 -- Out: retcode NOCOPY VARCHAR2
679 -- In: P_COA_ID NUMBER Chart of accounts ID
680 -- In: P_LE_ID NUMBER ID of Legal Entity
681 -- In: P_ACCOUNT_TYPE VARCHAR2 Type of the account
682 -- In: P_SOURCE_CHARSET VARCHAR2 source charset for convert
683 -- In: P_DESTINATION_CHARSET VARCHAR2 destination charset for convert
684 -- In: P_SOURCE_SEPARATOR VARCHAR2 source separator for replacement
685 -- In: P_DESTINATION_FILENAME VARCHAR2 output file name
686 -- In: P_XML_TEMPLATE_LANGUAGE VARCHAR2 template language of NA exception report
687 -- In: P_XML_TEMPLATE_TERRITORY VARCHAR2 template territory of NA exception report
688 -- In: P_XML_OUTPUT_FORMAT VARCHAR2 output format of NA exception report
689 --
690 -- DESIGN REFERENCES:
691 -- None
692 --
693 -- CHANGE HISTORY:
694 -- 03/03/2006 Andrew Liu Created
695 --===========================================================================
696 PROCEDURE Export_Coa_Entry(Errbuf OUT NOCOPY VARCHAR2,
697 Retcode OUT NOCOPY VARCHAR2,
698 P_COA_ID IN NUMBER,
699 p_Le_Id IN NUMBER,
700 p_Ledger_Id IN NUMBER,
701 p_Account_Type IN VARCHAR2,
702 p_Source_Charset IN VARCHAR2,
703 p_Destination_Charset IN VARCHAR2,
704 p_Source_Separator IN VARCHAR2,
705 p_Destination_Filename IN VARCHAR2,
706 p_Xml_Template_Language IN VARCHAR2 DEFAULT 'zh',
707 p_Xml_Template_Territory IN VARCHAR2 DEFAULT '00',
708 p_Xml_Output_Format IN VARCHAR2 DEFAULT 'RTF') IS
709 l_Reqid NUMBER;
710 l_Chr_Reqid NUMBER; -- Request id for the 'Charset Convert'
711 l_Fn_Reqid NUMBER; -- Request id for the 'Change filename'
712
713 l_Chr_Result VARCHAR2(10);
714 l_Fn_Result VARCHAR2(10);
715
716 l_Req_Phase Fnd_Lookup_Values.Lookup_Code%TYPE;
717 l_Req_Status Fnd_Lookup_Values.Lookup_Code%TYPE;
718 l_User_Phase Fnd_Lookup_Values.Meaning%TYPE;
719 l_User_Status Fnd_Lookup_Values.Meaning%TYPE;
720 l_Err_Msg VARCHAR2(1000) := NULL;
721
722 --
723 l_program_name varchar(100) := '';
724
725 BEGIN
726
727 IF P_ACCOUNT_TYPE = 'NA' THEN
728 l_program_name := 'JACNNAGN';
729 ELSIF P_ACCOUNT_TYPE = 'PJ' THEN
730 l_program_name := 'JACNPJGN';
731 ELSIF P_ACCOUNT_TYPE = 'TP' THEN
732 l_program_name := 'JACNTPGN';
733 ELSIF P_ACCOUNT_TYPE = 'CC' THEN
734 l_program_name := 'JACNCCGN';
735 ELSIF P_ACCOUNT_TYPE = 'PERSON' THEN
736 l_program_name := 'JACNPSGN';
737 END IF;
738
739 l_reqid := FND_REQUEST.Submit_Request(application => 'JA',
740 program => l_program_name,
741 argument1 => P_COA_ID,
742 argument2 => P_LE_ID,
743 argument3 => P_LEDGER_ID,
744 argument4 => P_ACCOUNT_TYPE,
745 argument5 => P_XML_TEMPLATE_LANGUAGE,
746 argument6 => P_XML_TEMPLATE_TERRITORY,
747 argument7 => P_XML_OUTPUT_FORMAT);
748 COMMIT;
749
750 IF l_Reqid <> 0 THEN
751 IF Fnd_Concurrent.Wait_For_Request(Request_Id => l_Reqid,
752 INTERVAL => 5,
753 Phase => l_User_Phase,
754 Status => l_User_Status,
755 Dev_Phase => l_Req_Phase,
756 Dev_Status => l_Req_Status,
757 Message => l_Err_Msg) THEN
758
759 /* fnd_file.PUT_LINE(fnd_file.LOG,'l_user_phase: '||l_user_phase);
760 fnd_file.PUT_LINE(fnd_file.LOG,'l_user_status: '||l_user_status);
761 fnd_file.PUT_LINE(fnd_file.LOG,'l_req_phase: '||l_req_phase);
762 fnd_file.PUT_LINE(fnd_file.LOG,'l_req_status: '||l_req_status);
763 fnd_file.PUT_LINE(fnd_file.LOG,'l_err_msg: '||l_err_msg);*/
764
765 IF l_Req_Status = 'NORMAL' THEN
766 -- submit charset conversiong
767 Ja_Cn_Utility.Submit_Charset_Conversion(p_Xml_Request_Id => l_Reqid,
768 p_Source_Charset => p_Source_Charset,
769 p_Destination_Charset => p_Destination_Charset,
770 p_Source_Separator => p_Source_Separator,
771 x_Charset_Request_Id => l_Chr_Reqid,
772 x_Result_Flag => l_Chr_Result);
773
774 IF l_Chr_Result = 'Success' THEN
775 -- submit change output filename
776 Ja_Cn_Utility.Change_Output_Filename(p_Xml_Request_Id => l_Reqid,
777 p_Destination_Charset => p_Destination_Charset,
778 p_Destination_Filename => p_Destination_Filename,
779 x_Filename_Request_Id => l_Fn_Reqid,
780 x_Result_Flag => l_Fn_Result);
781 IF l_Fn_Result = 'Success' THEN
782 Retcode := 0;
783 RETURN;
784 ELSIF l_Fn_Result = 'Warning' THEN
785 Retcode := 1;
786 ELSE
787 Retcode := 2;
788 END IF;
789 ELSIF l_Chr_Result = 'Warning' THEN
790 Retcode := 1;
791 ELSE
792 Retcode := 2;
793 END IF;
794 END IF;
795 END IF;
796 END IF;
797 Retcode := 1;
798 END Export_Coa_Entry;
799
800 --==========================================================================
801 -- PROCEDURE NAME:
802 -- Ent_GSSM_Export Public
803 --
804 -- DESCRIPTION:
805 -- This procedure calls GSSM Export program to export GSSM for
806 -- Enterprise.
807 --
808 -- PARAMETERS:
809 -- Out: errbuf NOCOPY VARCHAR2
810 -- Out: retcode NOCOPY VARCHAR2
811 --
812 -- DESIGN REFERENCES:
813 -- None
814 --
815 -- CHANGE HISTORY:
816 -- 05/17/2006 Andrew Liu Created
817 --===========================================================================
818 PROCEDURE Ent_Gssm_Export(Errbuf OUT NOCOPY VARCHAR2,
819 Retcode OUT NOCOPY VARCHAR2) IS
820 BEGIN
821 Ja_Cn_Gssm_Exp_Pkg.Gssm_Export(Errbuf => Errbuf,
822 Retcode => Retcode,
823 p_Gssm_Type => 'ENT');
824 END Ent_Gssm_Export;
825
826 --==========================================================================
827 -- PROCEDURE NAME:
828 -- Ent_GSSM_Export_Entry Public
829 --
830 -- DESCRIPTION:
831 -- This procedure submits the program of Enterprise's GSSM Export, and
832 -- then changes the output file's CharSet and file name.
833 --
834 -- PARAMETERS:
835 -- Out: errbuf NOCOPY VARCHAR2
836 -- Out: retcode NOCOPY VARCHAR2
837 -- In: P_SOURCE_CHARSET VARCHAR2 source charset for convert
838 -- In: P_DESTINATION_CHARSET VARCHAR2 destination charset for convert
839 -- In: P_SOURCE_SEPARATOR VARCHAR2 source separator for replacement
840 -- In: P_DESTINATION_FILENAME VARCHAR2 output file name
841 --
842 -- DESIGN REFERENCES:
843 -- None
844 --
845 -- CHANGE HISTORY:
846 -- 03/03/2006 Andrew Liu Created
847 --===========================================================================
848 PROCEDURE Ent_Gssm_Export_Entry(Errbuf OUT NOCOPY VARCHAR2,
849 Retcode OUT NOCOPY VARCHAR2,
850 p_Source_Charset IN VARCHAR2,
851 p_Destination_Charset IN VARCHAR2,
852 p_Source_Separator IN VARCHAR2,
853 p_Destination_Filename IN VARCHAR2) IS
854 l_Reqid NUMBER;
855 l_Chr_Reqid NUMBER; -- Request id for the 'Charset Convert'
856 l_Fn_Reqid NUMBER; -- Request id for the 'Change filename'
857
858 l_Chr_Result VARCHAR2(10);
859 l_Fn_Result VARCHAR2(10);
860
861 l_Req_Phase Fnd_Lookup_Values.Lookup_Code%TYPE;
862 l_Req_Status Fnd_Lookup_Values.Lookup_Code%TYPE;
863 l_User_Phase Fnd_Lookup_Values.Meaning%TYPE;
864 l_User_Status Fnd_Lookup_Values.Meaning%TYPE;
865 l_Err_Msg VARCHAR2(1000) := NULL;
866
867 BEGIN
868 --Call Concurrent of 'Ent_GSSM_Export'
869 l_Reqid := Fnd_Request.Submit_Request(Application => 'JA',
870 Program => 'JACNEGSG');
871 COMMIT;
872
873 IF l_Reqid <> 0 THEN
874 IF Fnd_Concurrent.Wait_For_Request(Request_Id => l_Reqid,
875 INTERVAL => 5,
876 Phase => l_User_Phase,
877 Status => l_User_Status,
878 Dev_Phase => l_Req_Phase,
879 Dev_Status => l_Req_Status,
880 Message => l_Err_Msg) THEN
881 /*fnd_file.PUT_LINE(fnd_file.LOG,'l_user_phase: '||l_user_phase);
882 fnd_file.PUT_LINE(fnd_file.LOG,'l_user_status: '||l_user_status);
883 fnd_file.PUT_LINE(fnd_file.LOG,'l_req_phase: '||l_req_phase);
884 fnd_file.PUT_LINE(fnd_file.LOG,'l_req_status: '||l_req_status);
885 fnd_file.PUT_LINE(fnd_file.LOG,'l_err_msg: '||l_err_msg);*/
886 IF l_Req_Status = 'NORMAL' THEN
887 -- submit charset conversiong
888 Ja_Cn_Utility.Submit_Charset_Conversion(p_Xml_Request_Id => l_Reqid,
889 p_Source_Charset => p_Source_Charset,
890 p_Destination_Charset => p_Destination_Charset,
891 p_Source_Separator => p_Source_Separator,
892 x_Charset_Request_Id => l_Chr_Reqid,
893 x_Result_Flag => l_Chr_Result);
894 IF l_Chr_Result = 'Success' THEN
895 -- submit change output filename
896 Ja_Cn_Utility.Change_Output_Filename(p_Xml_Request_Id => l_Reqid,
897 p_Destination_Charset => p_Destination_Charset,
898 p_Destination_Filename => p_Destination_Filename,
899 x_Filename_Request_Id => l_Fn_Reqid,
900 x_Result_Flag => l_Fn_Result);
901 IF l_Fn_Result = 'Success' THEN
902 Retcode := 0;
903 RETURN;
904 ELSIF l_Fn_Result = 'Warning' THEN
905 Retcode := 1;
906 ELSE
907 Retcode := 2;
908 END IF;
909 ELSIF l_Chr_Result = 'Warning' THEN
910 Retcode := 1;
911 ELSE
912 Retcode := 2;
913 END IF;
914 END IF;
915 END IF;
916 END IF;
917 Retcode := 1;
918 END Ent_Gssm_Export_Entry;
919
920 --==========================================================================
921 -- PROCEDURE NAME:
922 -- Pub_GSSM_Export Public
923 --
924 -- DESCRIPTION:
925 -- This procedure calls GSSM Export program to export GSSM for
926 -- Public Sector.
927 --
928 -- PARAMETERS:
929 -- Out: errbuf NOCOPY VARCHAR2
930 -- Out: retcode NOCOPY VARCHAR2
931 --
932 -- DESIGN REFERENCES:
933 -- None
934 --
935 -- CHANGE HISTORY:
936 -- 05/17/2006 Andrew Liu Created
937 --===========================================================================
938 PROCEDURE Pub_Gssm_Export(Errbuf OUT NOCOPY VARCHAR2,
939 Retcode OUT NOCOPY VARCHAR2) IS
940 BEGIN
941 Ja_Cn_Gssm_Exp_Pkg.Gssm_Export(Errbuf => Errbuf,
942 Retcode => Retcode,
943 p_Gssm_Type => 'PUB');
944 END Pub_Gssm_Export;
945
946 --==========================================================================
947 -- PROCEDURE NAME:
948 -- Pub_GSSM_Export_Entry Public
949 --
950 -- DESCRIPTION:
951 -- This procedure submits the program of Public Sector's GSSM Export, and
952 -- then changes the output file's CharSet and file name.
953 --
954 -- PARAMETERS:
955 -- Out: errbuf NOCOPY VARCHAR2
956 -- Out: retcode NOCOPY VARCHAR2
957 -- In: P_SOURCE_CHARSET VARCHAR2 source charset for convert
958 -- In: P_DESTINATION_CHARSET VARCHAR2 destination charset for convert
959 -- In: P_SOURCE_SEPARATOR VARCHAR2 source separator for replacement
960 -- In: P_DESTINATION_FILENAME VARCHAR2 output file name
961 --
962 -- DESIGN REFERENCES:
963 -- None
964 --
965 -- CHANGE HISTORY:
966 -- 03/03/2006 Andrew Liu Created
967 --===========================================================================
968 PROCEDURE Pub_Gssm_Export_Entry(Errbuf OUT NOCOPY VARCHAR2,
969 Retcode OUT NOCOPY VARCHAR2,
970 p_Source_Charset IN VARCHAR2,
971 p_Destination_Charset IN VARCHAR2,
972 p_Source_Separator IN VARCHAR2,
973 p_Destination_Filename IN VARCHAR2) IS
974 l_Reqid NUMBER;
975 l_Chr_Reqid NUMBER; -- Request id for the 'Charset Convert'
976 l_Fn_Reqid NUMBER; -- Request id for the 'Change filename'
977
978 l_Chr_Result VARCHAR2(10);
979 l_Fn_Result VARCHAR2(10);
980
981 l_Req_Phase Fnd_Lookup_Values.Lookup_Code%TYPE;
982 l_Req_Status Fnd_Lookup_Values.Lookup_Code%TYPE;
983 l_User_Phase Fnd_Lookup_Values.Meaning%TYPE;
984 l_User_Status Fnd_Lookup_Values.Meaning%TYPE;
985 l_Err_Msg VARCHAR2(1000) := NULL;
986
987 BEGIN
988 --Call Concurrent of 'Pub_GSSM_Export'
989 l_Reqid := Fnd_Request.Submit_Request(Application => 'JA',
990 Program => 'JACNPGSG');
991 COMMIT;
992
993 IF l_Reqid <> 0 THEN
994 IF Fnd_Concurrent.Wait_For_Request(Request_Id => l_Reqid,
995 INTERVAL => 5,
996 Phase => l_User_Phase,
997 Status => l_User_Status,
998 Dev_Phase => l_Req_Phase,
999 Dev_Status => l_Req_Status,
1000 Message => l_Err_Msg) THEN
1001 /*fnd_file.PUT_LINE(fnd_file.LOG,'l_user_phase: '||l_user_phase);
1002 fnd_file.PUT_LINE(fnd_file.LOG,'l_user_status: '||l_user_status);
1003 fnd_file.PUT_LINE(fnd_file.LOG,'l_req_phase: '||l_req_phase);
1004 fnd_file.PUT_LINE(fnd_file.LOG,'l_req_status: '||l_req_status);
1005 fnd_file.PUT_LINE(fnd_file.LOG,'l_err_msg: '||l_err_msg);*/
1006 IF l_Req_Status = 'NORMAL' THEN
1007 -- submit charset conversiong
1008 Ja_Cn_Utility.Submit_Charset_Conversion(p_Xml_Request_Id => l_Reqid,
1009 p_Source_Charset => p_Source_Charset,
1010 p_Destination_Charset => p_Destination_Charset,
1011 p_Source_Separator => p_Source_Separator,
1012 x_Charset_Request_Id => l_Chr_Reqid,
1013 x_Result_Flag => l_Chr_Result);
1014 IF l_Chr_Result = 'Success' THEN
1015 -- submit change output filename
1016 Ja_Cn_Utility.Change_Output_Filename(p_Xml_Request_Id => l_Reqid,
1017 p_Destination_Charset => p_Destination_Charset,
1018 p_Destination_Filename => p_Destination_Filename,
1019 x_Filename_Request_Id => l_Fn_Reqid,
1020 x_Result_Flag => l_Fn_Result);
1021 IF l_Fn_Result = 'Success' THEN
1022 Retcode := 0;
1023 RETURN;
1024 ELSIF l_Fn_Result = 'Warning' THEN
1025 Retcode := 1;
1026 ELSE
1027 Retcode := 2;
1028 END IF;
1029 ELSIF l_Chr_Result = 'Warning' THEN
1030 Retcode := 1;
1031 ELSE
1032 Retcode := 2;
1033 END IF;
1034 END IF;
1035 END IF;
1036 END IF;
1037 Retcode := 1;
1038 END Pub_Gssm_Export_Entry;
1039
1040 --==========================================================================
1041 -- PROCEDURE NAME:
1042 --
1043 -- ACCOUNT_BALANCE_EXPORT Public
1044 --
1045 -- DESCRIPTION:
1046 --
1047 -- This procedure is used to export the account balances.
1048 --
1049 --
1050 -- PARAMETERS:
1051 -- Out: errbuf Mandatory parameter for PL/SQL concurrent programs
1052 -- Out: retcode Mandatory parameter for PL/SQL concurrent programs
1053 -- In: p_legal_entity Legal entity ID
1054 -- In: p_start_period start period name
1055 -- In: P_end_period end period name
1056 -- In: P_XML_TEMPLATE_LANGUAGE template language of exception report
1057 -- In: P_XML_TEMPLATE_TERRITORY template territory of exception report
1058 -- In: P_XML_OUTPUT_FORMAT output format of exception report
1059 -- In: P_SOURCE_CHARSET source charset for convert
1060 -- In: P_DESTINATION_CHARSET destination charset for convert
1061 -- In: P_SOURCE_SEPARATOR source separator for replacement
1062 -- In: P_DESTINATION_FILENAME output file name
1063 --
1064 -- DESIGN REFERENCES:
1065 --
1066 --
1067 -- CHANGE HISTORY:
1068 --
1069 -- 19-Jun-2006 Joseph Wang Created
1070 -- 05-July-2006 Add parameters p_start_period and P_end_period
1071 -- remove the parameter p_period
1072 --===========================================================================
1073 PROCEDURE Account_Balance_Export(Errbuf OUT NOCOPY VARCHAR2,
1074 Retcode OUT NOCOPY VARCHAR2,
1075 p_coa_id IN NUMBER,
1076 p_ledger_id IN NUMBER,
1077 p_Legal_Entity IN NUMBER,
1078 p_Start_Period IN VARCHAR2,
1079 p_End_Period IN VARCHAR2,
1080 p_Xml_Template_Language IN VARCHAR2,
1081 p_Xml_Template_Territory IN VARCHAR2,
1082 p_Xml_Output_Format IN VARCHAR2,
1083 p_Source_Charset VARCHAR2,
1084 p_Destination_Charset VARCHAR2,
1085 p_Source_Separator VARCHAR2,
1086 p_Destination_Filename VARCHAR2) IS
1087
1088 l_Reqid NUMBER;
1089 l_Chr_Reqid NUMBER; -- Request id for the 'Charset Convert'
1090 l_Fn_Reqid NUMBER; -- Request id for the 'Change filename'
1091
1092 l_Chr_Result VARCHAR2(10);
1093 l_Fn_Result VARCHAR2(10);
1094
1095 l_Req_Phase Fnd_Lookup_Values.Lookup_Code%TYPE;
1096 l_Req_Status Fnd_Lookup_Values.Lookup_Code%TYPE;
1097 l_User_Phase Fnd_Lookup_Values.Meaning%TYPE;
1098 l_User_Status Fnd_Lookup_Values.Meaning%TYPE;
1099 l_Err_Msg VARCHAR2(1000) := NULL;
1100
1101 BEGIN
1102
1103 l_Reqid := Fnd_Request.Submit_Request(Application => 'JA',
1104 Program => 'JACNABGE',
1105 Argument1 => p_coa_id,
1106 Argument2 => p_Ledger_Id, --added by lyb
1107 Argument3 => p_Legal_Entity, --added by lyb
1108 Argument4 => p_Start_Period,
1109 Argument5 => p_End_Period,
1110 Argument6 => p_Xml_Template_Language,
1111 Argument7 => p_Xml_Template_Territory,
1112 Argument8 => p_Xml_Output_Format);
1113 COMMIT;
1114
1115 IF l_Reqid <> 0 THEN
1116 IF Fnd_Concurrent.Wait_For_Request(Request_Id => l_Reqid,
1117 INTERVAL => 5,
1118 Phase => l_User_Phase,
1119 Status => l_User_Status,
1120 Dev_Phase => l_Req_Phase,
1121 Dev_Status => l_Req_Status,
1122 Message => l_Err_Msg) THEN
1123 /*fnd_file.PUT_LINE(fnd_file.LOG,'l_user_phase: '||l_user_phase);
1124 fnd_file.PUT_LINE(fnd_file.LOG,'l_user_status: '||l_user_status);
1125 fnd_file.PUT_LINE(fnd_file.LOG,'l_req_phase: '||l_req_phase);
1126 fnd_file.PUT_LINE(fnd_file.LOG,'l_req_status: '||l_req_status);
1127 fnd_file.PUT_LINE(fnd_file.LOG,'l_err_msg: '||l_err_msg);*/
1128 IF l_Req_Status = 'NORMAL' THEN
1129 -- submit charset conversiong
1130 Ja_Cn_Utility.Submit_Charset_Conversion(p_Xml_Request_Id => l_Reqid,
1131 p_Source_Charset => p_Source_Charset,
1132 p_Destination_Charset => p_Destination_Charset,
1133 p_Source_Separator => p_Source_Separator,
1134 x_Charset_Request_Id => l_Chr_Reqid,
1135 x_Result_Flag => l_Chr_Result);
1136 IF l_Chr_Result = 'Success' THEN
1137 -- submit change output filename
1138 Ja_Cn_Utility.Change_Output_Filename(p_Xml_Request_Id => l_Reqid,
1139 p_Destination_Charset => p_Destination_Charset,
1140 p_Destination_Filename => p_Destination_Filename,
1141 x_Filename_Request_Id => l_Fn_Reqid,
1142 x_Result_Flag => l_Fn_Result);
1143 IF l_Fn_Result = 'Success' THEN
1144 Retcode := 0;
1145 RETURN;
1146 ELSIF l_Fn_Result = 'Warning' THEN
1147 Retcode := 1;
1148 ELSE
1149 Retcode := 2;
1150 END IF;
1151 ELSIF l_Chr_Result = 'Warning' THEN
1152 Retcode := 1;
1153 ELSE
1154 Retcode := 2;
1155 END IF;
1156 END IF;
1157 END IF;
1158 END IF;
1159 Retcode := 1;
1160
1161 END Account_Balance_Export;
1162
1163 --==========================================================================
1164 -- PROCEDURE NAME:
1165 --
1166 -- ACCOUNT_BALANCE_GENERATION Public
1167 --
1168 -- DESCRIPTION:
1169 --
1170 -- This procedure is used to generate the account balances.
1171 --
1172 --
1173 -- PARAMETERS:
1174 -- Out: errbuf Mandatory parameter for PL/SQL concurrent programs
1175 -- Out: retcode Mandatory parameter for PL/SQL concurrent programs
1176 -- In: p_legal_entity Legal entity ID
1177 -- In: p_start_period start period name
1178 -- In: P_end_period end period name
1179 -- In: P_XML_TEMPLATE_LANGUAGE template language of exception report
1180 -- In: P_XML_TEMPLATE_TERRITORY template territory of exception report
1181 -- In: P_XML_OUTPUT_FORMAT output format of exception report
1182 --
1183 -- DESIGN REFERENCES:
1184 --
1185 --
1186 -- CHANGE HISTORY:
1187 --
1188 -- 19-Jun-2006 Joseph Wang Created
1189 -- 05-July-2006 Add parameters p_start_period and P_end_period
1190 -- remove the parameter p_period
1191 --===========================================================================
1192
1193 PROCEDURE Account_Balance_Generation(Errbuf OUT NOCOPY VARCHAR2,
1194 Retcode OUT NOCOPY VARCHAR2,
1195 p_coa_id IN NUMBER,
1196 p_ledger_id IN NUMBER,
1197 p_Legal_Entity IN NUMBER,
1198 p_Start_Period IN VARCHAR2,
1199 p_End_Period IN VARCHAR2,
1200 p_Xml_Template_Language IN VARCHAR2,
1201 p_Xml_Template_Territory IN VARCHAR2,
1202 p_Xml_Output_Format IN VARCHAR2) IS
1203 BEGIN
1204 Ja_Cn_Ab_Exp_Pkg.Run_Export(Errbuf => Errbuf,
1205 Retcode => Retcode,
1206 p_coa_id => p_coa_id,
1207 p_ledger_id => p_ledger_id,
1208 p_Legal_Entity => p_Legal_Entity,
1209 p_Start_Period => p_Start_Period,
1210 p_End_Period => p_End_Period,
1211 p_Xml_Template_Language => p_Xml_Template_Language,
1212 p_Xml_Template_Territory => p_Xml_Template_Territory,
1213 p_Xml_Output_Format => p_Xml_Output_Format);
1214 END Account_Balance_Generation;
1215
1216 --==========================================================================
1217 -- PROCEDURE NAME:
1218 --
1219 -- JOURNAL_ENTRY_EXPORT Public
1220 --
1221 -- DESCRIPTION:
1222 --
1223 -- This procedure is used to export the journal entries.
1224 --
1225 --
1226 -- PARAMETERS:
1227 -- Out: errbuf Mandatory parameter for PL/SQL concurrent programs
1228 -- Out: retcode Mandatory parameter for PL/SQL concurrent programs
1229 -- In: p_legal_entity Legal entity ID
1230 -- In: p_start_period start period name
1231 -- In: P_end_period end period name
1232 -- In: P_SOURCE_CHARSET source charset for convert
1233 -- In: P_DESTINATION_CHARSET destination charset for convert
1234 -- In: P_SOURCE_SEPARATOR source separator for replacement
1235 -- In: P_DESTINATION_FILENAME output file name
1236 --
1237 -- DESIGN REFERENCES:
1238 --
1239 --
1240 -- CHANGE HISTORY:
1241 --
1242 -- 01-Mar-2006 Joseph Wang Created
1243 -- 15-Jun-2006 Add parameters P_XML_TEMPLATE_LANGUAGE, P_XML_TEMPLATE_TERRITORY, P_XML_OUTPUT_FORMAT
1244 -- 19-Jun-2006 Add parameters P_SOURCE_CHARSET, P_DESTINATION_CHARSET,P_SOURCE_SEPARATOR,
1245 -- P_DESTINATION_FILENAME
1246 -- 05-July-2006 Add parameters p_start_period and P_end_period
1247 -- remove the parameter p_period
1248 --
1249 --===========================================================================
1250
1251 PROCEDURE Journal_Entry_Export(Errbuf OUT NOCOPY VARCHAR2,
1252 Retcode OUT NOCOPY VARCHAR2,
1253 p_coa_id IN NUMBER, --added by lyb
1254 p_ledger_id IN NUMBER, --added by lyb
1255 p_Legal_Entity IN NUMBER,
1256 p_Start_Period IN VARCHAR2,
1257 p_End_Period IN VARCHAR2,
1258 p_Source_Charset VARCHAR2,
1259 p_Destination_Charset VARCHAR2,
1260 p_Source_Separator VARCHAR2,
1261 p_Destination_Filename VARCHAR2) IS
1262
1263 l_Reqid NUMBER;
1264 l_Chr_Reqid NUMBER; -- Request id for the 'Charset Convert'
1265 l_Fn_Reqid NUMBER; -- Request id for the 'Change filename'
1266
1267 l_Chr_Result VARCHAR2(10);
1268 l_Fn_Result VARCHAR2(10);
1269
1270 l_Req_Phase Fnd_Lookup_Values.Lookup_Code%TYPE;
1271 l_Req_Status Fnd_Lookup_Values.Lookup_Code%TYPE;
1272 l_User_Phase Fnd_Lookup_Values.Meaning%TYPE;
1273 l_User_Status Fnd_Lookup_Values.Meaning%TYPE;
1274 l_Err_Msg VARCHAR2(1000) := NULL;
1275
1276 BEGIN
1277 l_Reqid := Fnd_Request.Submit_Request(Application => 'JA',
1278 Program => 'JACNJEGE',
1279 Argument1 => p_coa_id,
1280 argument2 => p_ledger_id,
1281 Argument3 => p_Legal_Entity,
1282 Argument4 => p_Start_Period,
1283 Argument5 => p_End_Period);
1284 COMMIT;
1285
1286 IF l_Reqid <> 0 THEN
1287 IF Fnd_Concurrent.Wait_For_Request(Request_Id => l_Reqid,
1288 INTERVAL => 5,
1289 Phase => l_User_Phase,
1290 Status => l_User_Status,
1291 Dev_Phase => l_Req_Phase,
1292 Dev_Status => l_Req_Status,
1293 Message => l_Err_Msg) THEN
1294 IF l_Req_Status = 'NORMAL' THEN
1295 -- submit charset conversiong
1296 Ja_Cn_Utility.Submit_Charset_Conversion(p_Xml_Request_Id => l_Reqid,
1297 p_Source_Charset => p_Source_Charset,
1298 p_Destination_Charset => p_Destination_Charset,
1299 p_Source_Separator => p_Source_Separator,
1300 x_Charset_Request_Id => l_Chr_Reqid,
1301 x_Result_Flag => l_Chr_Result);
1302 IF l_Chr_Result = 'Success' THEN
1303 -- submit change output filename
1304 Ja_Cn_Utility.Change_Output_Filename(p_Xml_Request_Id => l_Reqid,
1305 p_Destination_Charset => p_Destination_Charset,
1306 p_Destination_Filename => p_Destination_Filename,
1307 x_Filename_Request_Id => l_Fn_Reqid,
1308 x_Result_Flag => l_Fn_Result);
1309 IF l_Fn_Result = 'Success' THEN
1310 Retcode := 0;
1311 RETURN;
1312 ELSIF l_Fn_Result = 'Warning' THEN
1313 Retcode := 1;
1314 ELSE
1315 Retcode := 2;
1316 END IF;
1317 ELSIF l_Chr_Result = 'Warning' THEN
1318 Retcode := 1;
1319 ELSE
1320 Retcode := 2;
1321 END IF;
1322 END IF;
1323 END IF;
1324 END IF;
1325 Retcode := 1;
1326 END Journal_Entry_Export;
1327
1328 --==========================================================================
1329 -- PROCEDURE NAME:
1330 --
1331 -- JOURNAL_ENTRY_GENERATION Public
1332 --
1333 -- DESCRIPTION:
1334 --
1335 -- This procedure is used to generate the journal entries.
1336 --
1337 --
1338 -- PARAMETERS:
1339 -- Out: errbuf Mandatory parameter for PL/SQL concurrent programs
1340 -- Out: retcode Mandatory parameter for PL/SQL concurrent programs
1341 -- In: p_legal_entity Legal entity ID
1342 -- In: p_start_period start period name
1343 -- In: P_end_period end period name
1344 --
1345 -- DESIGN REFERENCES:
1346 --
1347 --
1348 -- CHANGE HISTORY:
1349 --
1350 -- 01-Mar-2006 Joseph Wang Created
1351 -- 15-Jun-2006 Add parameters P_XML_TEMPLATE_LANGUAGE, P_XML_TEMPLATE_TERRITORY, P_XML_OUTPUT_FORMAT
1352 -- 19-Jun-2006 Add parameters P_SOURCE_CHARSET, P_DESTINATION_CHARSET,P_SOURCE_SEPARATOR,
1353 -- P_DESTINATION_FILENAME
1354 -- 15-Jun-2006 Add parameters p_start_period and P_end_period
1355 -- remove the parameter p_period
1356 --
1357 --===========================================================================
1358
1359 PROCEDURE JOURNAL_ENTRY_GENERATION(errbuf OUT NOCOPY VARCHAR2,
1360 retcode OUT NOCOPY VARCHAR2,
1361 p_coa_id IN NUMBER,
1362 p_ledger_id IN NUMBER ,
1363 p_legal_entity IN NUMBER,
1364 p_start_period IN VARCHAR2,
1365 p_end_period IN VARCHAR2) IS
1366
1367 BEGIN
1368
1369 JA_CN_JE_EXP_PKG.Run_Export(errbuf => errbuf,
1370 retcode => retcode,
1371 p_coa_id => p_coa_id,
1372 p_ledger_id => p_ledger_id,
1373 p_legal_entity_id => p_legal_entity,
1374 p_start_period => p_start_period,
1375 p_end_period => p_end_period);
1376
1377 END JOURNAL_ENTRY_GENERATION;
1378
1379 END Ja_Cn_Etc_Conc_Prog;
1380