1 PACKAGE BODY IBY_TRXN_DOCUMENTS_PKG AS
2 /* $Header: ibytxdcb.pls 120.7.12000000.3 2007/09/05 16:08:21 visundar ship $ */
3
4
5 G_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'iby.plsql.IBY_TRXN_DOCUMENTS_PKG';
6
7 /* Gets the trxnmid based on (transaction id,trxntype id,status) */
8 PROCEDURE getTrxnMID
9 (
10 transactionid_in IN iby_trxn_summaries_all.transactionid%TYPE,
11 trxntypeid_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
12 status_in IN iby_trxn_summaries_all.status%TYPE,
13 trxnmid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE
14 )
15 IS
16 CURSOR c_trxnmid
17 (
18 ci_transactionid iby_trxn_summaries_all.transactionid%TYPE,
19 ci_trxntypeid iby_trxn_summaries_all.trxntypeid%TYPE,
20 ci_status iby_trxn_summaries_all.status%TYPE
21 )
22 IS
23 SELECT trxnmid INTO trxnmid_out
24 FROM iby_trxn_summaries_all
25 WHERE (transactionid=ci_transactionid)
26 AND (status=ci_status)
27 AND ((trxntypeid=NVL(ci_trxntypeid,trxntypeid)) OR (trxntypeid is NULL))
28 ORDER BY creation_date DESC;
29 BEGIN
30 IF (c_trxnmid%ISOPEN) THEN
31 CLOSE c_trxnmid;
32 END IF;
33
34 OPEN c_trxnmid(transactionid_in,trxntypeid_in,status_in);
35 FETCH c_trxnmid INTO trxnmid_out;
36
37 CLOSE c_trxnmid;
38 EXCEPTION
39
40 WHEN no_data_found THEN
41 raise_application_error(-20000, 'IBY_20534#ID='||transactionid_in||'#TYPE='||trxntypeid_in||'#STATUS='||status_in, FALSE);
42
43 WHEN too_many_rows THEN
44 raise_application_error(-20000, 'IBY_20535#ID='||transactionid_in||'#TYPE='||trxntypeid_in||'#STATUS='||status_in, FALSE);
45
46 END getTrxnMID;
47
48
49 /*------*/
50 /* Gets the trxnmid based on (transaction id,trxntype id) */
51 PROCEDURE getTrxnMIDFinancing
52 (
53 transactionid_in IN iby_trxn_summaries_all.transactionid%TYPE,
54 trxntypeid_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
55 trxnmid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE
56 )
57 IS
58 BEGIN
59
60 SELECT trxnmid INTO trxnmid_out
61 FROM iby_trxn_summaries_all
62 WHERE (transactionid=transactionid_in) AND (trxntypeid=trxntypeid_in);
63
64 EXCEPTION
65
66 WHEN no_data_found THEN
67 trxnmid_out := -1;
68
69 WHEN too_many_rows THEN
70 raise_application_error(-20000, 'IBY_20535#ID='||transactionid_in||'#TYPE='||trxntypeid_in, FALSE);
71
72 END getTrxnMIDFinancing;
73 /*------*/
74
75
76 /* Add an empty document based on the master trxn id of a trxn. */
77 PROCEDURE CreateDocument
78 (
79 trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
80 doctype_in IN iby_trxn_documents.doctype%TYPE
81 )
82 IS
83
84 l_docid iby_trxn_documents.trxn_document_id%TYPE;
85
86 BEGIN
87 CreateDocument(trxnmid_in, doctype_in, l_docid);
88 END CreateDocument;
89
90
91 /*
92 * Add an empty document based on master transaction id of a
93 * transaction and return the generated document id.
94 */
95 PROCEDURE CreateDocument
96 (
97 trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
98 doctype_in IN iby_trxn_documents.doctype%TYPE,
99 docid_out OUT NOCOPY iby_trxn_documents.trxn_document_id%TYPE
100 )
101 IS
102
103 trxn_document_id_seq NUMBER;
104
105 BEGIN
106 SELECT iby_trxn_documentid_s.NEXTVAL INTO trxn_document_id_seq FROM dual;
107
108 docid_out := trxn_document_id_seq;
109
110 -- CHANGE: CATCH EXCEPTION FOR DOC UNIQUENESS CONSTRAINTS
111 -- explicitly catch the exception and return an specific
112 -- IBY_XXXX error code?
113 --
114 INSERT INTO iby_trxn_documents (trxnmid,doctype,document,object_version_number,last_update_date,last_updated_by,creation_date,created_by,last_update_login,trxn_document_id, payment_instruction_id)
115 -- object version number is 0 as the document is empty
116 --
117 VALUES (trxnmid_in,doctype_in,empty_clob(),0,sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.login_id,trxn_document_id_seq, NULL);
118 COMMIT;
119 END CreateDocument;
120
121 /*
122 * Retro-fit IBY_TRXN_DOCUMENTS for storing R12 FD extract -
123 * doctype is 100.
124 * TRXNMID is made nullable
125 * the doc will be associated with either IBY_TRXN_SUMMARIES_ALL
126 * or IBY_PAY_INSTRUCTIONS_ALL depending on the doctype.
127 * FZ 3/14/05
128 */
129 PROCEDURE CreateDocument
130 (
131 p_payment_instruction_id IN NUMBER,
132 p_doctype IN NUMBER,
133 p_doc IN CLOB,
134 docid_out OUT NOCOPY iby_trxn_documents.trxn_document_id%TYPE
135 )
136 IS
137
138 trxn_document_id_seq NUMBER;
139
140 BEGIN
141 SELECT iby_trxn_documentid_s.NEXTVAL INTO trxn_document_id_seq FROM dual;
142
143 docid_out := trxn_document_id_seq;
144 BEGIN
145 INSERT INTO iby_trxn_documents (trxnmid,doctype,document,object_version_number,
146 last_update_date,last_updated_by,creation_date,created_by,last_update_login,
147 trxn_document_id, payment_instruction_id)
148 -- object version number is 0 as the document is empty
149 --
150 -- work around for the unique key trxnmid/doctype
151 -- FZ 8/28/2005. This should not be a problem
152 -- for funds capture code as the doctype (100)
153 -- is for R12 disbursement only
154 VALUES (p_payment_instruction_id,p_doctype,p_doc,1,
155 sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.login_id,
156 trxn_document_id_seq, p_payment_instruction_id);
157 EXCEPTION
158 when DUP_VAL_ON_INDEX then null;
159 END;
160 END CreateDocument;
161
162
163
164 /*
165 * First check if a document exists for a given (trxnmid, doctype)
166 * combination. If it exists, return the existing document id
167 * instead of inserting a new row in the table.
168 *
169 * Used in online financing.
170 */
171 PROCEDURE CreateOrUpdateDocument
172 (
173 trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
174 doctype_in IN iby_trxn_documents.doctype%TYPE,
175 docid_out OUT NOCOPY iby_trxn_documents.trxn_document_id%TYPE
176 )
177
178 IS
179
180 l_docid iby_trxn_documents.trxn_document_id%TYPE;
181
182 cursor c_doc(ci_trxnmid in iby_trxn_summaries_all.trxnmid%type,
183 ci_doctype in iby_trxn_documents.doctype%type)
184 is
185 SELECT trxn_document_id
186 FROM iby_trxn_documents
187 WHERE trxnmid = ci_trxnmid
188 AND doctype = ci_doctype;
189
190 BEGIN
191
192 if (c_doc%isopen) then
193 close c_doc;
194 end if;
195
196 open c_doc(trxnmid_in, doctype_in);
197 fetch c_doc into l_docid;
198
199 docid_out := l_docid;
200
201 --
202 -- Insert an empty CLOB in place of the
203 -- existing CLOB. This empty CLOB will
204 -- be overwitten by a new CLOB later.
205 --
206 -- If we don't do this, the existing
207 -- CLOB gets overwritten by a new CLOB,
208 -- but we get XML parse errors.
209 --
210 UPDATE iby_trxn_documents SET
211 document=empty_clob()
212 WHERE
213 trxnmid = trxnmid_in
214 AND doctype = doctype_in;
215
216 if (c_doc%notfound) then
217 CreateDocument(trxnmid_in, doctype_in, docid_out);
218 end if;
219
220 close c_doc;
221
222 END CreateOrUpdateDocument;
223
224
225 /* Add an empty document based on (transaction id, trxntype, status) */
226 PROCEDURE CreateDocument
227 (
228 transactionid_in IN iby_trxn_summaries_all.transactionid%TYPE,
229 trxntypeid_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
230 status_in IN iby_trxn_summaries_all.status%TYPE,
231 doctype_in IN iby_trxn_documents.doctype%TYPE
232 )
233 IS
234 l_mtrxnid iby_trxn_summaries_all.trxnmid%TYPE;
235 BEGIN
236 getTrxnMID(transactionid_in, trxntypeid_in, status_in,l_mtrxnid);
237 CreateDocument(l_mtrxnid,doctype_in);
238 END CreateDocument;
239
240
241 /* Delete a document based on the master trxn id of a trxn. */
242 PROCEDURE DeleteDocument
243 (
244 trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
245 doctype_in IN iby_trxn_documents.doctype%TYPE
246 )
247 IS
248 BEGIN
249 DELETE FROM iby_trxn_documents WHERE (trxnmid=trxnmid_in) AND (doctype=doctype_in);
250 COMMIT;
251 END DeleteDocument;
252
253
254 /* Add a document based on (transaction id, trxntype, status) */
255 PROCEDURE DeleteDocument
256 (
257 transactionid_in IN iby_trxn_summaries_all.transactionid%TYPE,
258 trxntypeid_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
259 status_in IN iby_trxn_summaries_all.status%TYPE,
260 doctype_in IN iby_trxn_documents.doctype%TYPE
261 )
262 IS
263 l_mtrxnid iby_trxn_summaries_all.trxnmid%TYPE;
264 BEGIN
265 getTrxnMID(transactionid_in,trxntypeid_in,status_in,l_mtrxnid);
266 DeleteDocument(l_mtrxnid,doctype_in);
267 END DeleteDocument;
268
269
270 /* Fetches a document based on (transaction id,trxn type id,status,doctype) */
271 PROCEDURE FetchDocument
272 (
273 transactionid_in IN iby_trxn_summaries_all.transactionid%TYPE,
274 trxntypeid_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
275 status_in IN iby_trxn_summaries_all.status%TYPE,
276 doctype_in IN iby_trxn_documents.doctype%TYPE,
277 read_only_in IN INTEGER,
278 document_out OUT NOCOPY iby_trxn_documents.document%TYPE
279 )
280 IS
281 l_trxnmid iby_trxn_summaries_all.trxnmid%TYPE;
282 BEGIN
283 getTrxnMID(transactionid_in,trxntypeid_in,status_in,l_trxnmid);
284 FetchDocument(l_trxnmid,doctype_in,read_only_in,document_out);
285 END FetchDocument;
286
287
288 /* Fetch a document based on (trxnmid,doctype) */
289 PROCEDURE FetchDocument
290 (
291 trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
292 doctype_in IN iby_trxn_documents.doctype%TYPE,
293 read_only_in IN INTEGER,
294 document_out OUT NOCOPY iby_trxn_documents.document%TYPE
295 )
296 IS
297 BEGIN
298 IF (read_only_in=C_FETCH_READWRITE) THEN
299 -- not read only so we update the last change information
300 --
301 UPDATE iby_trxn_documents
302 SET object_version_number=object_version_number+1, last_update_date=sysdate,last_updated_by=fnd_global.user_id,last_update_login=fnd_global.login_id
303 WHERE (trxnmid=trxnmid_in) AND (doctype=doctype_in);
304 COMMIT;
305
306 SELECT document INTO document_out
307 FROM iby_trxn_documents
308 WHERE (trxnmid=trxnmid_in) AND (doctype=doctype_in) FOR UPDATE;
309 -- must select as for update it to be able to write to it
310 -- from JDBC
311 ELSE
312 SELECT document INTO document_out
313 FROM iby_trxn_documents
314 WHERE (trxnmid=trxnmid_in) AND (doctype=doctype_in);
315 END IF;
316
317 EXCEPTION
318
319 WHEN no_data_found THEN
320 raise_application_error(-20000, 'IBY_19005#MID='||trxnmid_in||'#TYPE='||doctype_in, FALSE);
321
322 END FetchDocument;
323
324
325
326 PROCEDURE FetchDisbursementDocument
327 (
328 p_payment_instruction_id IN NUMBER,
329 p_doctype IN NUMBER,
330 read_only_in IN INTEGER DEFAULT C_FETCH_READONLY,
331 document_out OUT NOCOPY iby_trxn_documents.document%TYPE
332 )
333 IS
334 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.FetchDisbursementDocument';
335 CURSOR l_doc_csr (p_payment_instruction_id IN NUMBER, p_doctype IN NUMBER) IS
336 SELECT document INTO document_out
337 FROM iby_trxn_documents
338 WHERE (payment_instruction_id=p_payment_instruction_id) AND (doctype=p_doctype) and rownum = 1
339 ORDER BY trxn_document_id asc;
340
341 BEGIN
342 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
343 debug_level => FND_LOG.LEVEL_PROCEDURE,
344 module => l_Debug_Module);
345
346 iby_debug_pub.add(debug_msg => 'input p_payment_instruction_id: ' || p_payment_instruction_id,
347 debug_level => FND_LOG.LEVEL_STATEMENT,
348 module => l_Debug_Module);
349
350 iby_debug_pub.add(debug_msg => 'input p_doctype: ' || p_doctype,
351 debug_level => FND_LOG.LEVEL_STATEMENT,
352 module => l_Debug_Module);
353
354 iby_debug_pub.add(debug_msg => 'input read_only_in: ' || read_only_in,
355 debug_level => FND_LOG.LEVEL_STATEMENT,
356 module => l_Debug_Module);
357
358
359 IF (read_only_in=C_FETCH_READWRITE) THEN
360 -- read write option is not used
361 document_out := null;
362 /*
363 -- not read only so we update the last change information
364 --
365 UPDATE iby_trxn_documents
366 SET object_version_number=object_version_number+1, last_update_date=sysdate,last_updated_by=fnd_global.user_id,last_update_login=fnd_global.login_id
367 WHERE (payment_instruction_id=p_payment_instruction_id) AND (doctype=p_doctype);
368 COMMIT;
369
370 SELECT document INTO document_out
371 FROM iby_trxn_documents
372 WHERE (payment_instruction_id=p_payment_instruction_id) AND (doctype=p_doctype) FOR UPDATE;
373 -- must select as for update it to be able to write to it
374 -- from JDBC
375 */
376 ELSE
377 OPEN l_doc_csr(p_payment_instruction_id, p_doctype);
378 FETCH l_doc_csr INTO document_out;
379 CLOSE l_doc_csr;
380
381 END IF;
382
383 IF document_out IS NOT NULL THEN
384
385 iby_debug_pub.add(debug_msg => 'After fetch, document_out is not null. ',
386 debug_level => FND_LOG.LEVEL_STATEMENT,
387 module => l_Debug_Module);
388 ELSE
389 iby_debug_pub.add(debug_msg => 'After fetch, document_out is null! ',
390 debug_level => FND_LOG.LEVEL_STATEMENT,
391 module => l_Debug_Module);
392
393 END IF;
394
395
396 EXCEPTION
397
398 WHEN others THEN
399 NULL;
400 -- raise_application_error(-20000, 'IBY_19005#MID='||p_payment_instruction_id||'#TYPE='||p_doctype, FALSE);
401
402 END FetchDisbursementDocument;
403
404
405
406
407 END IBY_TRXN_DOCUMENTS_PKG;
[Home] [Help]