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