[Home] [Help]
PACKAGE BODY: APPS.JA_CN_UPDATE_JL_SEQ_PKG
Source
1 PACKAGE BODY JA_CN_UPDATE_JL_SEQ_PKG AS
2 --$Header: JACNVJSB.pls 120.1.12000000.1 2007/08/13 14:09:55 qzhao noship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNVJSB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package is used to fetch a sequence number |
13 --| for Journal Itemization program |
14 --| |
15 --| PROCEDURE LIST |
16 --| FUNCTION Fetch_JL_Seq |
17 --| PROCEDURE Create_JL_Seq |
18 --| PROCEDURE Update_JL_Seq |
19 --| |
20 --| HISTORY |
21 --| 03/13/2006 Jackey Li Created |
22 -- 04/28/2007 Qingjun Zhao Add column Ledger_id to table |
23 -- ja_cn_journal_numbering |
24 --+======================================================================*/
25
26 --==== Golbal Variables ============
27 g_module_name VARCHAR2(30) := 'JA_CN_UPDATE_JL_SEQ_PKG';
28 g_dbg_level NUMBER := FND_LOG.G_Current_Runtime_Level;
29 g_proc_level NUMBER := FND_LOG.Level_Procedure;
30 g_stmt_level NUMBER := FND_LOG.Level_Statement;
31
32 --==========================================================================
33 -- PROCEDURE NAME:
34 -- Create_JL_Seq Private
35 --
36 -- DESCRIPTION:
37 -- This procedure is used to create a sequence number under the
38 -- Legal Entity, ledger and Period Name with the initial value '2'
39 --
40 -- PARAMETERS:
41 -- In: p_legal_entity_ID legal entity ID
42 -- p_period_name period_name
43 --
44 -- DESIGN REFERENCES:
45 -- CNAO_Update_Journal_Sequence_PKG_TD.doc
46 --
47 -- CHANGE HISTORY:
48 -- 03/13/2006 Jackey Li Created
49 -- 04/28/2007 Qingjun Zhao Add column Ledger_id to table
50 -- ja_cn_journal_numbering
51 --===========================================================================
52 PROCEDURE Create_JL_Seq(p_legal_entity_ID IN NUMBER,
53 p_ledger_id in number,
54 p_period_name IN VARCHAR2) IS
55
56 l_procedure_name VARCHAR2(30) := 'Create_JL_Seq';
57
58 BEGIN
59 --log for debug
60 IF (g_proc_level >= g_dbg_level) THEN
61 FND_LOG.STRING(g_proc_level,
62 g_module_name || '.' || l_procedure_name || '.begin',
63 'begin procedure');
64 END IF; --( g_proc_level >= g_dbg_level)
65
66 --insert 1 into JA_CN_JOURNAL_NUMBERING table
67 INSERT INTO JA_CN_JOURNAL_NUMBERING
68 (legal_entity_id,
69 ledger_id,
70 Period_Name,
71 NEXT_NUMBER,
72 CREATED_BY,
73 CREATION_DATE,
74 LAST_UPDATED_BY,
75 LAST_UPDATE_DATE,
76 LAST_UPDATE_LOGIN)
77 VALUES
78 (p_legal_entity_ID,
79 p_ledger_id,
80 p_period_name,
81 2,
82 fnd_global.USER_ID,
83 SYSDATE,
84 fnd_global.USER_ID,
85 SYSDATE,
86 fnd_global.LOGIN_ID);
87
88 --log for debug
89 IF (g_proc_level >= g_dbg_level) THEN
90 FND_LOG.STRING(g_proc_level,
91 g_module_name || '.' || l_procedure_name || '.end',
92 'end procedure');
93 END IF; --( g_proc_level >= g_dbg_level)
94
95 EXCEPTION
96 WHEN OTHERS THEN
97 IF (FND_LOG.LEVEL_UNEXPECTED >= g_dbg_level) THEN
98 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
99 g_module_name || ',' || l_procedure_name ||
100 '.OTHER_EXCEPTION',
101 SQLCODE || ':' || SQLERRM);
102 END IF;
103 ROLLBACK;
104
105 END Create_JL_Seq;
106
107 --==========================================================================
108 -- PROCEDURE NAME:
109 -- Update_JL_Seq Private
110 --
111 -- DESCRIPTION:
112 -- This procedure is used to update a sequence number under the
113 -- Legal Entity, Ledger and Period Name with old number plus '1'
114 --
115 -- PARAMETERS:
116 -- In: p_legal_entity_ID legal entity ID
117 -- p_ledger_id ledger ID
118 -- p_period_name period_name
119 -- p_next_number next number
120 --
121 -- DESIGN REFERENCES:
122 -- CNAO_Update_Journal_Sequence_PKG_TD.doc
123 --
124 -- CHANGE HISTORY:
125 -- 03/13/2006 Jackey Li Created
126 -- 04/28/2007 Qingjun Zhao Add column Ledger_id to table
127 -- ja_cn_journal_numbering
128 --===========================================================================
129 PROCEDURE Update_JL_Seq(p_legal_entity_ID IN NUMBER,
130 p_ledger_id in number,
131 p_period_name IN VARCHAR2,
132 p_next_number IN NUMBER) IS
133
134 l_procedure_name VARCHAR2(30) := 'Update_JL_Seq';
135
136 BEGIN
137 --log for debug
138 IF (g_proc_level >= g_dbg_level) THEN
139 FND_LOG.STRING(g_proc_level,
140 g_module_name || '.' || l_procedure_name || '.begin',
141 'begin procedure');
142 END IF; --( g_proc_level >= g_dbg_level)
143
144 -- update the JA_CN_JOURNAL_NUMBERING table with new next_number
145 UPDATE JA_CN_JOURNAL_NUMBERING jcjn
146 SET jcjn.next_number = p_next_number,
147 jcjn.last_updated_by = fnd_global.USER_ID,
148 jcjn.last_update_date = SYSDATE,
149 jcjn.last_update_login = fnd_global.LOGIN_ID
150 WHERE jcjn.legal_entity_id = nvl(p_legal_entity_ID, -1)
151 and jcjn.ledger_id = p_ledger_id
152 AND jcjn.period_name = p_period_name;
153
154 --log for debug
155 IF (g_proc_level >= g_dbg_level) THEN
156 FND_LOG.STRING(g_proc_level,
157 g_module_name || '.' || l_procedure_name || '.end',
158 'end procedure');
159 END IF; --( g_proc_level >= g_dbg_level)
160
161 EXCEPTION
162 WHEN OTHERS THEN
163 IF (FND_LOG.LEVEL_UNEXPECTED >= g_dbg_level) THEN
164 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
165 g_module_name || ',' || l_procedure_name ||
166 '.OTHER_EXCEPTION',
167 SQLCODE || ':' || SQLERRM);
168 END IF;
169 ROLLBACK;
170
171 END Update_JL_Seq;
172
173 --==========================================================================
174 -- PROCEDURE NAME:
175 -- Fetch_JL_Seq Public
176 --
177 -- DESCRIPTION:
178 -- This procedure is used to fetch a sequence number under the
179 -- Legal Entity and Period Name for Journal Itemization program
180 --
181 -- PARAMETERS:
182 -- In: p_legal_entity_ID legal entity ID
183 -- p_ledger_id ledger ID
184 -- p_period_name period_name
185 --
186 -- DESIGN REFERENCES:
187 -- CNAO_Update_Journal_Sequence_PKG_TD.doc
188 --
189 -- CHANGE HISTORY:
190 -- 03/13/2006 Jackey Li Created
191 -- 04/28/2007 Qingjun Zhao Add column Ledger_id to table
192 -- ja_cn_journal_numbering
193 --===========================================================================
194 FUNCTION Fetch_JL_Seq(p_legal_entity_ID IN NUMBER,
195 p_ledger_id in number,
196 p_period_name IN VARCHAR2) RETURN NUMBER IS
197
198 l_procedure_name VARCHAR2(30) := 'Fetch_JL_Seq';
199 l_next_number NUMBER;
200
201 l_exc_invalid_argument EXCEPTION;
202
203 -- this cursor is used to get stored next_number
204 CURSOR c_sequence IS
205 SELECT next_number
206 FROM JA_CN_JOURNAL_NUMBERING jcjn
207 WHERE jcjn.legal_entity_id = NVL(p_legal_entity_ID, -1)
208 and jcjn.ledger_id = P_ledger_id
209 AND jcjn.period_name = p_period_name;
210 BEGIN
211
212 --log for debug
213 IF (g_proc_level >= g_dbg_level) THEN
214 FND_LOG.STRING(g_proc_level,
215 g_module_name || '.' || l_procedure_name || '.begin',
216 'begin procedure');
217 END IF; --( g_proc_level >= g_dbg_level)
218
219 -- if some parameter is null, return 0
220 IF p_legal_entity_ID IS NULL OR p_period_name IS NULL or
221 p_ledger_id is null THEN
222 RAISE l_exc_invalid_argument;
223 END IF;
224
225 OPEN c_sequence;
226 FETCH c_sequence
227 INTO l_next_number;
228 IF c_sequence%NOTFOUND THEN
229 CLOSE c_sequence;
230 Create_JL_Seq(p_legal_entity_ID, p_ledger_id, p_period_name);
231 RETURN 1;
232 ELSE
233 CLOSE c_sequence;
234 Update_JL_Seq(p_legal_entity_ID,
235 p_ledger_id,
236 p_period_name,
237 l_next_number + 1);
238 RETURN l_next_number;
239 END IF; --c_sequence%NOTFOUND
240
241 --log for debug
242 IF (g_proc_level >= g_dbg_level) THEN
243 FND_LOG.STRING(g_proc_level,
244 g_module_name || '.' || l_procedure_name || '.end',
245 'end procedure');
246 END IF; --( g_proc_level >= g_dbg_level)
247
248 EXCEPTION
249 WHEN l_exc_invalid_argument THEN
250 /*
251 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level) THEN
252 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
253 g_module_prefix ||','|| l_procedure_name ||
254 '.INVALID_ARGUMENT',
255 'invalid_argument';
256 END IF;
257 */
258 RETURN 0;
259
260 END Fetch_JL_Seq;
261
262 END JA_CN_UPDATE_JL_SEQ_PKG;