[Home] [Help]
PACKAGE BODY: APPS.JA_CN_ERT_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_ERT_EXPORT_PKG AS
2 --$Header: JACNERTB.pls 120.3 2011/01/14 07:00:29 choli noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNERTB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export Exchange Rate Types |
13 --| |
14 --| PROCEDURE LIST |
15 --| PROCEDURE Add_Exchange_Rate_Types |
16 --| |
17 --| |
18 --| HISTORY |
19 --| 01/20/2010 Chongwu Li Created |
20 --| 01/05/2011 Jianchao Chi Updated for the new solution to change |
21 --| the logic of Voucher Number and Legal |
22 --| Entity |
23 --+======================================================================*/
24 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_ERT_EXPORT_PKG';
25
26 --==========================================================================
27 -- PROCEDURE NAME:
28 --
29 -- Add_Exchange_Rate_Types Public
30 --
31 -- DESCRIPTION:
32 --
33 -- This procedure is to export Exchange Rate Types
34 --
35 -- PARAMETERS:
36 -- In: pn_legal_entity_id legal_entity ID
37 -- pn_ledger_id ledger ID
38 -- pn_accounting_year accounting year
39 -- DESIGN REFERENCES:
40 -- CNAO_V2_GL_TD.doc
41 --
42 -- CHANGE HISTORY:
43 --
44 -- 20-JAN-2010 Chongwu Li Created
45 -- 05-JAN-2011 Jianchao Chi Updated for the new solution to change
46 -- the logic of Voucher Number and Legal Entity
47 --===========================================================================
48
49 PROCEDURE Add_Exchange_Rate_Types(pn_legal_entity_id NUMBER,
50 pn_ledger_id NUMBER,
51 pn_accounting_year NUMBER) IS
52 lv_procedure_name VARCHAR2(40) := 'Add_Exchange_Rate_Types';
53 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
54 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
55 ln_row_count NUMBER;
56 --NO_DATA EXCEPTION;
57
58 -- to get Electronic Accounting Book
59 CURSOR exchange_rate_types_cur(ln_legal_entity_id NUMBER,
60 ln_accounting_year NUMBER) IS
61 SELECT DISTINCT gdct.conversion_type, gdct.user_conversion_type
62 FROM gl_daily_conversion_types gdct,
63 ja_cn_journal_lines jcjl,
64 gl_je_headers gjh,
65 ja_cn_voucher_number jcvn
66 --Update by Jianchao Chi, for cnaov2 upgrade 05-JAN-2011
67 --Add ja_cn_voucher_number table, voucher number and legal entity
68 --are fetched from ja_cn_voucher_number
69 --The new one is
70 ----------------------------------------------------
71 --AND jcvn.je_header_id = jl.je_header_id
72 --AND jcvn.je_line_number = jl.je_line_num
73 --AND jcvn.legal_entity_id = ln_legal_entity_id;
74 ----------------------------------------------------
75 WHERE gdct.conversion_type = gjh.currency_conversion_type
76 AND jcjl.je_header_id = gjh.je_header_id
77 --Add the follow two conditions by Jianchao Chi to change the logic of legal entity, 05-JAN-2011
78 AND jcvn.je_header_id = jcjl.je_header_id
79 AND jcvn.je_line_number = jcjl.je_line_num
80 AND jcjl.period_name IN
81 (SELECT gp.period_name
82 FROM gl_periods gp, gl_ledgers gl
83 WHERE gp.period_year = ln_accounting_year
84 AND gp.period_set_name = gl.period_set_name)
85 --Comment by Jianchao Chi to change the logic of legal entity, 05-JAN-2011
86 --AND jcjl.legal_entity_id = ln_legal_entity_id
87 AND jcvn.legal_entity_id = ln_legal_entity_id;
88
89 BEGIN
90 --logging for debug
91 IF (ln_proc_level >= ln_dbg_level) THEN
92 FND_LOG.STRING(ln_proc_level,
93 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
94 '.begin',
95 'Enter procedure');
96 FND_LOG.STRING(ln_proc_level,
97 lv_procedure_name || '.parameters',
98 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
99 'pn_ledger_id=' || pn_ledger_id || ',' ||
100 'pn_accounting_year=' || pn_accounting_year);
101
102 END IF; --ln_proc_level>=ln_dbg_level
103 FND_FILE.put_line(FND_FILE.log,
104 lv_procedure_name || '.parameters:' ||
105 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
106 'pn_ledger_id=' || pn_ledger_id || ',' ||
107 'pn_accounting_year=' || pn_accounting_year);
108
109 ln_row_count := 0;
110 FOR v_row IN exchange_rate_types_cur(pn_legal_entity_id,
111 pn_accounting_year) LOOP
112 ln_row_count := ln_row_count + 1;
113 Ja_Cn_Utility.Add_Sub_Root_Node('EXCHANGE_RATE_TYPE',
114 Ja_Cn_Utility.GV_TAG_TYPE_START);
115 Ja_Cn_Utility.Add_Child_Node('EXCHANGE_RATE_TYPE_NUMBER',
116 v_row.Conversion_Type);
117 Ja_Cn_Utility.Add_Child_Node('EXCHANGE_RATE_TYPE_NAME',
118 v_row.User_Conversion_Type);
119 Ja_Cn_Utility.Add_Sub_Root_Node('EXCHANGE_RATE_TYPE',
120 Ja_Cn_Utility.GV_TAG_TYPE_END);
121
122 END LOOP;
123
124 IF (ln_row_count = 0) THEN
125 Ja_Cn_Utility.Print_No_Data_Found_For_Log('EXCHANGE_RATE_TYPE');
126 Ja_Cn_Utility.Add_Sub_Root_Node('EXCHANGE_RATE_TYPE',
127 Ja_Cn_Utility.GV_TAG_TYPE_START);
128 Ja_Cn_Utility.Add_Child_Node('EXCHANGE_RATE_TYPE_NUMBER', NULL);
129 Ja_Cn_Utility.Add_Child_Node('EXCHANGE_RATE_TYPE_NAME', NULL);
130 Ja_Cn_Utility.Add_Sub_Root_Node('EXCHANGE_RATE_TYPE',
131 Ja_Cn_Utility.GV_TAG_TYPE_END);
132
133 END IF; --ln_row_count = 0
134 --logging for debug
135 IF (ln_proc_level >= ln_dbg_level) THEN
136 FND_LOG.STRING(ln_proc_level,
137 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
138 'Exit procedure');
139 END IF; -- (ln_proc_level>=ln_dbg_level)
140 EXCEPTION
141 WHEN OTHERS THEN
142 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
143 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
144 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
145 '.Other_Exception ',
146 SQLCODE || SQLERRM);
147 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
148 FND_FILE.put_line(FND_FILE.log,
149 lv_procedure_name || SQLCODE || SQLERRM);
150 RAISE;
151 END Add_Exchange_Rate_Types;
152
153 END JA_CN_ERT_EXPORT_PKG;