[Home] [Help]
PACKAGE BODY: APPS.IBY_PAYMENT_DOC_PUB_PKG
Source
1 PACKAGE BODY IBY_PAYMENT_DOC_PUB_PKG AS
2 /*$Header: ibypdocb.pls 120.2.12020000.2 2012/07/12 15:02:15 sgogula ship $*/
3
4 /*
5 * Declare global variables
6 */
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_PAYMENT_DOC_PUB_PKG';
8 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
10
11
12 /*--------------------------------------------------------------------
13 | NAME:
14 | print_debuginfo
15 |
16 | PURPOSE:
17 |
18 |
19 | PARAMETERS:
20 | IN
21 |
22 |
23 | OUT
24 |
25 |
26 | RETURNS:
27 |
28 | NOTES:
29 |
30 *---------------------------------------------------------------------*/
31 PROCEDURE print_debuginfo(
32 p_module IN VARCHAR2,
33 p_debug_text IN VARCHAR2,
34 p_debug_level IN VARCHAR2
35 )
36 IS
37 l_default_debug_level VARCHAR2(200);
38 BEGIN
39
40 /*
41 * Set the debug level to the value passed in
42 * (provided this value is not null).
43 */
44 IF (p_debug_level IS NOT NULL) THEN
45 l_default_debug_level := p_debug_level;
46
47
48 /*
49 * Write the debug message to the concurrent manager log file.
50 */
51 IF (l_default_debug_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
52 iby_build_utils_pkg.print_debuginfo(p_module, p_debug_text,
53 p_debug_level);
54 END IF;
55 END IF;
56
57 END print_debuginfo;
58
59 /*------------------------------------------------------------------------------
60 | NAME:
61 | void_pmt_doc_numbers
62 |
63 | PURPOSE:
64 | This procedure would be called from Payment Document UI to void
65 | unused payment documents.
66 |
67 | None of these methods will perform a COMMIT. It is the responsibility
68 | of the caller to perform a COMMIT / ROLLBACK depending upon the
69 | the response status.
70 |
71 | PARAMETERS:
72 | IN
73 | p_payment_document_id
74 | p_from_doc_num
75 | p_to_doc_num
76 |
77 | OUT
78 |
79 |
80 | RETURNS:
81 |
82 | NOTES:
83 |
84 *-------------------------------------------------------------------------------*/
85 PROCEDURE void_pmt_doc_numbers(
86 p_payment_document_id IN NUMBER,
87 p_from_doc_num IN NUMBER,
88 p_to_doc_num IN NUMBER,
89 p_void_date IN DATE,
90 x_return_status OUT NOCOPY VARCHAR2,
91 x_msg_count OUT NOCOPY NUMBER,
92 x_msg_data OUT NOCOPY VARCHAR2
93 )
94 IS
95 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME
96 || '.void_pmt_doc_numbers';
97 l_pmt_instruction_id NUMBER;
98 l_first_avail_num NUMBER;
99 l_last_avail_num NUMBER;
100 l_last_issued_num NUMBER;
101 l_used number :=0;
102 l_skipped_document_number NUMBER;
103 BEGIN
104 print_debuginfo(l_module_name, 'ENTER',G_LEVEL_STATEMENT);
105 print_debuginfo(l_module_name, 'Payment Document Id::'||p_payment_document_id,G_LEVEL_STATEMENT);
106 print_debuginfo(l_module_name, 'From Document Number::'||p_from_doc_num,G_LEVEL_STATEMENT);
107 print_debuginfo(l_module_name, 'To Document Number::'||p_to_doc_num,G_LEVEL_STATEMENT);
108 print_debuginfo(l_module_name, 'Void Date::'||p_void_date,G_LEVEL_STATEMENT);
109
110 x_return_status := FND_API.G_RET_STS_SUCCESS;
111 /* Initializing Message Stack */
112 FND_MSG_PUB.initialize;
113
114 BEGIN
115 select payment_instruction_id,
116 first_available_document_num,
117 last_available_document_number,
118 last_issued_document_number
119 into l_pmt_instruction_id,
120 l_first_avail_num,
121 l_last_avail_num,
122 l_last_issued_num
123 from ce_payment_documents
124 where payment_document_id = p_payment_document_id;
125 EXCEPTION
126 WHEN NO_DATA_FOUND THEN
127
128 fnd_message.set_name('IBY', 'IBY_INV_PMT_DOC');
129 fnd_msg_pub.add;
130
131 print_debuginfo(l_module_name, 'Error:: Invalid Payment Document',G_LEVEL_STATEMENT);
132
133 RAISE fnd_api.g_exc_error;
134
135 END;
136
137 print_debuginfo(l_module_name, 'Payment Instruction Id::'||l_pmt_instruction_id,G_LEVEL_STATEMENT);
138 print_debuginfo(l_module_name, 'First Available Document Number::'||l_first_avail_num,G_LEVEL_STATEMENT);
139 print_debuginfo(l_module_name, 'Last Available Document Number::'||l_last_avail_num,G_LEVEL_STATEMENT);
140 print_debuginfo(l_module_name, 'Last Issued Document Number::'||l_last_issued_num,G_LEVEL_STATEMENT);
141
142
143 if(l_pmt_instruction_id is NOT NULL) then
144 fnd_message.set_name('IBY', 'IBY_PMT_DOC_LOCKED');
145 fnd_message.set_Token('INSTR_ID', l_pmt_instruction_id);
146 fnd_msg_pub.add;
147
148 print_debuginfo(l_module_name, 'Payment Document '||p_payment_document_id ||
149 'is locked by Instruction -'||l_pmt_instruction_id,G_LEVEL_STATEMENT);
150
151 RAISE fnd_api.g_exc_error;
152 end if;
153
154 if(p_from_doc_num<l_first_avail_num OR
155 p_to_doc_num>l_last_avail_num) then
156
157 fnd_message.set_name('IBY', 'IBY_INV_DOC_NUMS');
158 fnd_msg_pub.add;
159
160 print_debuginfo(l_module_name, 'Provided document numbers are OUT OF RANGE',G_LEVEL_STATEMENT);
161
162 RAISE fnd_api.g_exc_error;
163 end if;
164
165 BEGIN
166 select 1
167 into l_used
168 from dual
169 where exists(
170 select 'USED'
171 from IBY_USED_PAYMENT_DOCS
172 where payment_document_id = p_payment_document_id
173 and used_document_number between p_from_doc_num and p_to_doc_num
174 and document_use in ('SPOILED', 'ISSUED', 'UNUSED_VOIDED', 'SETUP', 'VOID' , 'OVERFLOW', 'PRINTED'));
175
176 if(l_used =1) then
177 fnd_message.set_name('IBY', 'IBY_PMT_DOC_NUM_USED');
178 fnd_msg_pub.add;
179
180 print_debuginfo(l_module_name, 'USED DOCS: One or more document numbers are already used',G_LEVEL_STATEMENT);
181
182 RAISE fnd_api.g_exc_error;
183 end if;
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 l_used:=0;
187 WHEN OTHERS THEN
188 RAISE;
189 END;
190
191
192
193 BEGIN
194 select 1
195 into l_used
196 from dual
197 where exists(
198 select 'USED'
199 FROM
200 iby_payments_all pmt,
201 iby_pay_instructions_all inst
202 WHERE
203 inst.payment_document_id = p_payment_document_id
204 AND inst.payment_instruction_id = pmt.payment_instruction_id
205 AND pmt.paper_document_number BETWEEN p_from_doc_num and p_to_doc_num);
206
207
208 if(l_used =1) then
209 fnd_message.set_name('IBY', 'IBY_DOC_NUM_USED');
210 fnd_msg_pub.add;
211
212 print_debuginfo(l_module_name, 'PMTS: One or more document numbers are already used',G_LEVEL_STATEMENT);
213
214 RAISE fnd_api.g_exc_error;
215 end if;
216 EXCEPTION
217 WHEN NO_DATA_FOUND THEN
218 l_used:=0;
219 WHEN OTHERS THEN
220 RAISE;
221 END;
222
223
224 FOR i IN 1 .. ((p_to_doc_num - p_from_doc_num)+1) LOOP
225
226 BEGIN
227 -- checking if the document is already skipped earlier
228 SELECT cedocs.used_document_number
229 INTO l_skipped_document_number
230 FROM iby_used_payment_docs cedocs
231 WHERE cedocs.payment_document_id = p_payment_document_id
232 AND cedocs.used_document_number = (p_from_doc_num + i -1)
233 AND cedocs.document_use = 'SKIPPED';
234
235 -- updating the document_use for skipped
236 UPDATE IBY_USED_PAYMENT_DOCS
237 SET DOCUMENT_USE = 'UNUSED_VOIDED',
238 LAST_UPDATED_BY = fnd_global.user_id,
239 LAST_UPDATE_DATE = SYSDATE,
240 LAST_UPDATE_LOGIN = fnd_global.login_id,
241 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1
242 WHERE payment_document_id = p_payment_document_id
243 AND used_document_number = (p_from_doc_num + i -1)
244 AND document_use = 'SKIPPED';
245
246 EXCEPTION
247 WHEN NO_DATA_FOUND THEN
248
249 print_debuginfo(l_module_name, 'Document number not found, inserting new',G_LEVEL_STATEMENT);
250
251 --if the document is not skipped earlier insert new
252 INSERT INTO IBY_USED_PAYMENT_DOCS (
253 PAYMENT_DOCUMENT_ID,
254 USED_DOCUMENT_NUMBER,
255 DATE_USED,
256 DOCUMENT_USE,
257 CREATED_BY,
258 CREATION_DATE,
259 LAST_UPDATED_BY,
260 LAST_UPDATE_DATE,
261 LAST_UPDATE_LOGIN,
262 OBJECT_VERSION_NUMBER
263 )
264 VALUES (
265 p_payment_document_id,
266 (p_from_doc_num + i -1),
267 p_void_date,
268 'UNUSED_VOIDED',
269 fnd_global.user_id,
270 sysdate,
271 fnd_global.user_id,
272 sysdate,
273 fnd_global.login_id,
274 1
275 );
276
277
278 END;
279
280 END LOOP;
281
282
283
284 IF (p_from_doc_num <= p_to_doc_num AND p_to_doc_num>l_last_issued_num) THEN
285
286 print_debuginfo(l_module_name, 'Last Issued Number is less than Range. Updating last_issued_document_number',G_LEVEL_STATEMENT);
287
288 UPDATE ce_payment_documents
289 set last_issued_document_number = p_to_doc_num
290 where payment_document_id = p_payment_document_id;
291
292 END IF;
293
294
295 print_debuginfo(l_module_name, 'EXIT',G_LEVEL_STATEMENT);
296 EXCEPTION
297 WHEN OTHERS THEN
298 x_return_status := fnd_api.g_ret_sts_error;
299
300 print_debuginfo('Exception : ' || SQLERRM,G_LEVEL_STATEMENT);
301
302 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
303 p_count => x_msg_count,
304 p_data => x_msg_data);
305
306 END void_pmt_doc_numbers;
307
308
309
310 END IBY_PAYMENT_DOC_PUB_PKG;
311