[Home] [Help]
PACKAGE BODY: APPS.CE_PAYMENT_TRXN_PKG
Source
1 PACKAGE BODY CE_PAYMENT_TRXN_PKG as
2 /* $Header: cebtpthb.pls 120.0 2005/07/26 08:43:20 jikumar noship $ */
3
4
5
6 FUNCTION body_revision RETURN VARCHAR2 IS
7 BEGIN
8
9 RETURN '$Revision: 120.0 $';
10
11 END body_revision;
12
13 FUNCTION spec_revision RETURN VARCHAR2 IS
14 BEGIN
15
16 RETURN G_spec_revision;
17
18 END spec_revision;
19
20 PROCEDURE INSERT_ROW (
21 X_ROWID IN OUT NOCOPY VARCHAR2,
22 X_TRXN_REFERENCE_NUMBER IN OUT NOCOPY NUMBER,
23 X_SETTLE_BY_SYSTEM_FLAG VARCHAR2,
24 X_TRANSACTION_TYPE VARCHAR2,
25 X_TRXN_SUBTYPE_CODE_ID NUMBER,
26 X_TRANSACTION_DATE DATE,
27 X_ANTICIPATED_VALUE_DATE DATE,
28 X_TRANSACTION_DESCRIPTION VARCHAR2,
29 X_PAYMENT_CURRENCY_CODE VARCHAR2,
30 X_PAYMENT_AMOUNT NUMBER,
31 X_SOURCE_PARTY_ID NUMBER,
32 X_SOURCE_LEGAL_ENTITY_ID NUMBER,
33 X_SOURCE_BANK_ACCOUNT_ID NUMBER,
34 X_DEST_PARTY_ID NUMBER,
35 X_DEST_LEGAL_ENTITY_ID NUMBER,
36 X_DEST_BANK_ACCOUNT_ID NUMBER,
37 X_DEST_PARTY_SITE_ID NUMBER,
38 X_REPETITIVE_PAYMENT_CODE VARCHAR2,
39 X_TRXN_STATUS_CODE VARCHAR2,
40 X_PAYMENT_METHOD_CODE VARCHAR2,
41 X_AUTHORIZE_FLAG VARCHAR2,
42 X_BANK_CHARGE_BEARER VARCHAR2,
43 X_PAYMENT_REASON_CODE VARCHAR2,
44 X_PAYMENT_REASON_COMMENTS VARCHAR2,
45 X_REMITTANCE_MESSAGE1 VARCHAR2,
46 X_REMITTANCE_MESSAGE2 VARCHAR2,
47 X_REMITTANCE_MESSAGE3 VARCHAR2,
48 X_CREATED_FROM_DIR VARCHAR2,
49 X_CREATE_FROM_STMTLINE_ID NUMBER,
50 X_BANK_TRXN_NUMBER VARCHAR2,
51 X_PAYMENT_REQUEST_NUMBER NUMBER,
52 X_PAPER_DOCUMENT_NUMBER NUMBER,
53 X_DOC_SEQUENCE_ID NUMBER,
54 X_DOC_SEQUENCE_VALUE NUMBER,
55 X_DOC_CATEGORY_CODE VARCHAR2,
56 X_PAYMENT_OFFSET_CCID NUMBER,
57 X_RECEIPT_OFFSET_CCID NUMBER,
58 X_CASHPOOL_ID NUMBER,
59 X_EXT_BANK_ACCOUNT_ID NUMBER,
60 X_ATTRIBUTE_CATEGORY VARCHAR2,
61 X_ATTRIBUTE1 VARCHAR2,
62 X_ATTRIBUTE2 VARCHAR2,
63 X_ATTRIBUTE3 VARCHAR2,
64 X_ATTRIBUTE4 VARCHAR2,
65 X_ATTRIBUTE5 VARCHAR2,
66 X_ATTRIBUTE6 VARCHAR2,
67 X_ATTRIBUTE7 VARCHAR2,
68 X_ATTRIBUTE8 VARCHAR2,
69 X_ATTRIBUTE9 VARCHAR2,
70 X_ATTRIBUTE10 VARCHAR2,
71 X_ATTRIBUTE11 VARCHAR2,
72 X_ATTRIBUTE12 VARCHAR2,
73 X_ATTRIBUTE13 VARCHAR2,
74 X_ATTRIBUTE14 VARCHAR2,
75 X_ATTRIBUTE15 VARCHAR2,
76 X_CREATED_BY NUMBER,
77 X_CREATION_DATE DATE,
78 X_LAST_UPDATED_BY NUMBER,
79 X_LAST_UPDATE_DATE DATE,
80 X_LAST_UPDATE_LOGIN NUMBER
81 ) IS
82
83 CURSOR C IS SELECT ROWID FROM CE_PAYMENT_TRANSACTIONS
84 WHERE TRXN_REFERENCE_NUMBER = X_TRXN_REFERENCE_NUMBER;
85 CURSOR C1 IS SELECT CE_PAYMENT_TRANSACTIONS_S.NEXTVAL FROM DUAL;
86
87 BEGIN
88 IF ( X_TRXN_REFERENCE_NUMBER ) IS NULL THEN
89 OPEN C1;
90 FETCH C1 INTO X_TRXN_REFERENCE_NUMBER;
91 CLOSE C1;
92 END IF;
93
94 INSERT INTO CE_PAYMENT_TRANSACTIONS
95 ( TRXN_REFERENCE_NUMBER,
96 SETTLE_BY_SYSTEM_FLAG,
97 TRANSACTION_TYPE,
98 TRXN_SUBTYPE_CODE_ID,
99 TRANSACTION_DATE,
100 ANTICIPATED_VALUE_DATE,
101 TRANSACTION_DESCRIPTION,
102 PAYMENT_CURRENCY_CODE,
103 PAYMENT_AMOUNT,
104 SOURCE_PARTY_ID,
105 SOURCE_LEGAL_ENTITY_ID,
106 SOURCE_BANK_ACCOUNT_ID,
107 DESTINATION_PARTY_ID,
108 DESTINATION_LEGAL_ENTITY_ID,
109 DESTINATION_BANK_ACCOUNT_ID,
110 DESTINATION_PARTY_SITE_ID,
111 REPETITIVE_PAYMENT_CODE,
112 TRXN_STATUS_CODE,
113 PAYMENT_METHOD_CODE,
114 AUTHORIZE_FLAG,
115 BANK_CHARGE_BEARER,
116 PAYMENT_REASON_CODE,
117 PAYMENT_REASON_COMMENTS,
118 REMITTANCE_MESSAGE1,
119 REMITTANCE_MESSAGE2,
120 REMITTANCE_MESSAGE3,
121 CREATED_FROM_DIR,
122 CREATE_FROM_STMTLINE_ID,
123 BANK_TRXN_NUMBER,
124 PAYMENT_REQUEST_NUMBER,
125 PAPER_DOCUMENT_NUMBER,
126 DOC_SEQUENCE_ID,
127 DOC_SEQUENCE_VALUE,
128 DOC_CATEGORY_CODE,
129 PAYMENT_OFFSET_CCID,
130 RECEIPT_OFFSET_CCID,
131 CASHPOOL_ID,
132 EXT_BANK_ACCOUNT_ID,
133 ATTRIBUTE_CATEGORY,
134 ATTRIBUTE1,
135 ATTRIBUTE2,
136 ATTRIBUTE3,
137 ATTRIBUTE4,
138 ATTRIBUTE5,
139 ATTRIBUTE6,
140 ATTRIBUTE7,
141 ATTRIBUTE8,
142 ATTRIBUTE9,
143 ATTRIBUTE10,
144 ATTRIBUTE11,
145 ATTRIBUTE12,
146 ATTRIBUTE13,
147 ATTRIBUTE14,
148 ATTRIBUTE15,
149 CREATED_BY,
150 CREATION_DATE,
151 LAST_UPDATED_BY,
152 LAST_UPDATE_DATE,
153 LAST_UPDATE_LOGIN,
154 OBJECT_VERSION_NUMBER
155 ) VALUES
156 ( X_TRXN_REFERENCE_NUMBER,
157 X_SETTLE_BY_SYSTEM_FLAG,
158 X_TRANSACTION_TYPE,
159 X_TRXN_SUBTYPE_CODE_ID,
160 X_TRANSACTION_DATE,
161 X_ANTICIPATED_VALUE_DATE,
162 X_TRANSACTION_DESCRIPTION,
163 X_PAYMENT_CURRENCY_CODE,
164 X_PAYMENT_AMOUNT,
165 X_SOURCE_PARTY_ID,
166 X_SOURCE_LEGAL_ENTITY_ID,
167 X_SOURCE_BANK_ACCOUNT_ID,
168 X_DEST_PARTY_ID,
169 X_DEST_LEGAL_ENTITY_ID,
170 X_DEST_BANK_ACCOUNT_ID,
171 X_DEST_PARTY_SITE_ID,
172 X_REPETITIVE_PAYMENT_CODE,
173 X_TRXN_STATUS_CODE,
174 X_PAYMENT_METHOD_CODE,
175 X_AUTHORIZE_FLAG,
176 X_BANK_CHARGE_BEARER,
177 X_PAYMENT_REASON_CODE,
178 X_PAYMENT_REASON_COMMENTS,
179 X_REMITTANCE_MESSAGE1,
180 X_REMITTANCE_MESSAGE2,
181 X_REMITTANCE_MESSAGE3,
182 X_CREATED_FROM_DIR,
183 X_CREATE_FROM_STMTLINE_ID,
184 X_BANK_TRXN_NUMBER,
185 X_PAYMENT_REQUEST_NUMBER,
186 X_PAPER_DOCUMENT_NUMBER,
187 X_DOC_SEQUENCE_ID,
188 X_DOC_SEQUENCE_VALUE,
189 X_DOC_CATEGORY_CODE,
190 X_PAYMENT_OFFSET_CCID,
191 X_RECEIPT_OFFSET_CCID,
192 X_CASHPOOL_ID,
193 X_EXT_BANK_ACCOUNT_ID,
194 X_ATTRIBUTE_CATEGORY,
195 X_ATTRIBUTE1,
196 X_ATTRIBUTE2,
197 X_ATTRIBUTE3,
198 X_ATTRIBUTE4,
199 X_ATTRIBUTE5,
200 X_ATTRIBUTE6,
201 X_ATTRIBUTE7,
202 X_ATTRIBUTE8,
203 X_ATTRIBUTE9,
204 X_ATTRIBUTE10,
205 X_ATTRIBUTE11,
206 X_ATTRIBUTE12,
207 X_ATTRIBUTE13,
208 X_ATTRIBUTE14,
209 X_ATTRIBUTE15,
210 X_CREATED_BY,
211 X_CREATION_DATE,
212 X_LAST_UPDATED_BY,
213 X_LAST_UPDATE_DATE,
214 X_LAST_UPDATE_LOGIN,
215 '1' );
216 OPEN C;
217 FETCH C INTO X_rowid;
218 IF (C%NOTFOUND) THEN
219 CLOSE C;
220 Raise NO_DATA_FOUND;
221 END IF;
222 CLOSE C;
223
224 END insert_row;
225
226
227 PROCEDURE UPDATE_ROW (
228 X_ROWID VARCHAR2,
229 X_TRXN_REFERENCE_NUMBER NUMBER,
230 X_SETTLE_BY_SYSTEM_FLAG VARCHAR2,
231 X_TRANSACTION_TYPE VARCHAR2,
232 X_TRXN_SUBTYPE_CODE_ID NUMBER,
233 X_TRANSACTION_DATE DATE,
234 X_ANTICIPATED_VALUE_DATE DATE,
235 X_TRANSACTION_DESCRIPTION VARCHAR2,
236 X_PAYMENT_CURRENCY_CODE VARCHAR2,
237 X_PAYMENT_AMOUNT NUMBER,
238 X_SOURCE_PARTY_ID NUMBER,
239 X_SOURCE_LEGAL_ENTITY_ID NUMBER,
240 X_SOURCE_BANK_ACCOUNT_ID NUMBER,
241 X_DEST_PARTY_ID NUMBER,
242 X_DEST_LEGAL_ENTITY_ID NUMBER,
243 X_DEST_BANK_ACCOUNT_ID NUMBER,
244 X_DEST_PARTY_SITE_ID NUMBER,
245 X_REPETITIVE_PAYMENT_CODE VARCHAR2,
246 X_TRXN_STATUS_CODE VARCHAR2,
247 X_PAYMENT_METHOD_CODE VARCHAR2,
248 X_AUTHORIZE_FLAG VARCHAR2,
249 X_BANK_CHARGE_BEARER VARCHAR2,
250 X_PAYMENT_REASON_CODE VARCHAR2,
251 X_PAYMENT_REASON_COMMENTS VARCHAR2,
252 X_REMITTANCE_MESSAGE1 VARCHAR2,
253 X_REMITTANCE_MESSAGE2 VARCHAR2,
254 X_REMITTANCE_MESSAGE3 VARCHAR2,
255 X_CREATED_FROM_DIR VARCHAR2,
256 X_CREATE_FROM_STMTLINE_ID NUMBER,
257 X_BANK_TRXN_NUMBER VARCHAR2,
258 X_PAYMENT_REQUEST_NUMBER NUMBER,
259 X_PAPER_DOCUMENT_NUMBER NUMBER,
260 X_DOC_SEQUENCE_ID NUMBER,
261 X_DOC_SEQUENCE_VALUE NUMBER,
262 X_DOC_CATEGORY_CODE VARCHAR2,
263 X_PAYMENT_OFFSET_CCID NUMBER,
264 X_RECEIPT_OFFSET_CCID NUMBER,
265 X_CASHPOOL_ID NUMBER,
266 X_EXT_BANK_ACCOUNT_ID NUMBER,
267 X_ATTRIBUTE_CATEGORY VARCHAR2,
268 X_ATTRIBUTE1 VARCHAR2,
269 X_ATTRIBUTE2 VARCHAR2,
270 X_ATTRIBUTE3 VARCHAR2,
271 X_ATTRIBUTE4 VARCHAR2,
272 X_ATTRIBUTE5 VARCHAR2,
273 X_ATTRIBUTE6 VARCHAR2,
274 X_ATTRIBUTE7 VARCHAR2,
275 X_ATTRIBUTE8 VARCHAR2,
276 X_ATTRIBUTE9 VARCHAR2,
277 X_ATTRIBUTE10 VARCHAR2,
278 X_ATTRIBUTE11 VARCHAR2,
279 X_ATTRIBUTE12 VARCHAR2,
280 X_ATTRIBUTE13 VARCHAR2,
281 X_ATTRIBUTE14 VARCHAR2,
282 X_ATTRIBUTE15 VARCHAR2,
283 X_LAST_UPDATED_BY NUMBER,
284 X_LAST_UPDATE_DATE DATE,
285 X_LAST_UPDATE_LOGIN NUMBER,
286 X_OBJECT_VERSION_NUMBER NUMBER ) IS
287 BEGIN
288 UPDATE CE_PAYMENT_TRANSACTIONS
289 SET
290 TRXN_REFERENCE_NUMBER = X_TRXN_REFERENCE_NUMBER,
291 SETTLE_BY_SYSTEM_FLAG = X_SETTLE_BY_SYSTEM_FLAG,
292 TRANSACTION_TYPE = X_TRANSACTION_TYPE,
293 TRXN_SUBTYPE_CODE_ID = X_TRXN_SUBTYPE_CODE_ID,
294 TRANSACTION_DATE = X_TRANSACTION_DATE,
295 ANTICIPATED_VALUE_DATE = X_ANTICIPATED_VALUE_DATE,
296 TRANSACTION_DESCRIPTION = X_TRANSACTION_DESCRIPTION,
297 PAYMENT_CURRENCY_CODE = X_PAYMENT_CURRENCY_CODE,
298 PAYMENT_AMOUNT = X_PAYMENT_AMOUNT,
299 SOURCE_PARTY_ID = X_SOURCE_PARTY_ID,
300 SOURCE_LEGAL_ENTITY_ID = X_SOURCE_LEGAL_ENTITY_ID,
301 SOURCE_BANK_ACCOUNT_ID = X_SOURCE_BANK_ACCOUNT_ID,
302 DESTINATION_PARTY_ID = X_DEST_PARTY_ID,
303 DESTINATION_LEGAL_ENTITY_ID = X_DEST_LEGAL_ENTITY_ID,
304 DESTINATION_BANK_ACCOUNT_ID = X_DEST_BANK_ACCOUNT_ID,
305 DESTINATION_PARTY_SITE_ID = X_DEST_PARTY_SITE_ID,
306 REPETITIVE_PAYMENT_CODE = X_REPETITIVE_PAYMENT_CODE,
307 TRXN_STATUS_CODE = X_TRXN_STATUS_CODE,
308 PAYMENT_METHOD_CODE = X_PAYMENT_METHOD_CODE,
309 AUTHORIZE_FLAG = X_AUTHORIZE_FLAG,
310 BANK_CHARGE_BEARER = X_BANK_CHARGE_BEARER,
311 PAYMENT_REASON_CODE = X_PAYMENT_REASON_CODE,
312 PAYMENT_REASON_COMMENTS = X_PAYMENT_REASON_COMMENTS,
313 REMITTANCE_MESSAGE1 = X_REMITTANCE_MESSAGE1,
314 REMITTANCE_MESSAGE2 = X_REMITTANCE_MESSAGE2,
315 REMITTANCE_MESSAGE3 = X_REMITTANCE_MESSAGE3,
316 CREATED_FROM_DIR = X_CREATED_FROM_DIR,
317 CREATE_FROM_STMTLINE_ID = X_CREATE_FROM_STMTLINE_ID,
318 BANK_TRXN_NUMBER = X_BANK_TRXN_NUMBER,
319 PAYMENT_REQUEST_NUMBER = X_PAYMENT_REQUEST_NUMBER,
320 PAPER_DOCUMENT_NUMBER = X_PAPER_DOCUMENT_NUMBER,
321 DOC_SEQUENCE_ID = X_DOC_SEQUENCE_ID,
322 DOC_SEQUENCE_VALUE = X_DOC_SEQUENCE_VALUE,
323 DOC_CATEGORY_CODE = X_DOC_CATEGORY_CODE,
324 PAYMENT_OFFSET_CCID = X_PAYMENT_OFFSET_CCID,
325 RECEIPT_OFFSET_CCID = X_RECEIPT_OFFSET_CCID,
326 CASHPOOL_ID = X_CASHPOOL_ID,
327 EXT_BANK_ACCOUNT_ID = X_EXT_BANK_ACCOUNT_ID,
328 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
329 ATTRIBUTE1 = X_ATTRIBUTE1,
330 ATTRIBUTE2 = X_ATTRIBUTE2,
331 ATTRIBUTE3 = X_ATTRIBUTE3,
332 ATTRIBUTE4 = X_ATTRIBUTE4,
333 ATTRIBUTE5 = X_ATTRIBUTE5,
334 ATTRIBUTE6 = X_ATTRIBUTE6,
335 ATTRIBUTE7 = X_ATTRIBUTE7,
336 ATTRIBUTE8 = X_ATTRIBUTE8,
337 ATTRIBUTE9 = X_ATTRIBUTE9,
338 ATTRIBUTE10 = X_ATTRIBUTE10,
339 ATTRIBUTE11 = X_ATTRIBUTE11,
340 ATTRIBUTE12 = X_ATTRIBUTE12,
341 ATTRIBUTE13 = X_ATTRIBUTE13,
342 ATTRIBUTE14 = X_ATTRIBUTE14,
343 ATTRIBUTE15 = X_ATTRIBUTE15,
344 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
345 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
346 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
347 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
348 WHERE
349 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER AND
350 ROWID = X_ROWID;
351
352 IF (SQL%NOTFOUND) THEN
353 Raise NO_DATA_FOUND;
354 END IF;
355 END UPDATE_ROW;
356
357 PROCEDURE Delete_Row( X_Row_id VARCHAR2 ) IS
358 BEGIN
359 DELETE FROM CE_PAYMENT_TRANSACTIONS
360 WHERE rowid = X_Row_Id;
361 IF (SQL%NOTFOUND) THEN
362 Raise NO_DATA_FOUND;
363 END IF;
364 END Delete_Row;
365
366 PROCEDURE Update_Transfer_Status ( X_TRXN_NUMBER NUMBER, X_STATUS VARCHAR2) IS
367 BEGIN
368 UPDATE CE_PAYMENT_TRANSACTIONS
369 SET TRXN_STATUS_CODE = X_STATUS
370 WHERE TRXN_REFERENCE_NUMBER = X_TRXN_NUMBER;
371 EXCEPTION
372 WHEN OTHERS THEN
373 RAISE;
374 END Update_Transfer_Status;
375
376 END CE_PAYMENT_TRXN_PKG;