1 PACKAGE BODY CE_999_PKG AS
2 /* $Header: ceab999b.pls 115.11 2002/01/25 16:51:43 pkm ship $ */
3 /* ---------------------------------------------------------------------
4 | PUBLIC FUNCTION |
5 | lock_row |
6 | |
7 | DESCRIPTION |
8 | This procedure would be called when open-interface transactions |
9 | need to be locked |
10 | |
11 | |
12 | HISTORY |
13 --------------------------------------------------------------------- */
14 PROCEDURE lock_row( X_call_mode VARCHAR2,
15 X_trx_type VARCHAR2,
16 X_trx_rowid VARCHAR2 ) IS
17
18 /* Example lock_open cursor
19
20 trx_id ROWID;
21
22 CURSOR lock_open IS
23 select rowid
24 from ce_999_interface_v
25 where rowid = X_trx_rowid
26 for update of trx_id nowait;
27
28 */
29
30 /* Note: ce_999_interface_v is the base table of ce_999_interface_v in this example. */
31
32 BEGIN
33
34 null;
35
36 /* Example of lock_row procedure with cursor and exception handling
37
38 OPEN lock_open;
39 FETCH lock_open INTO trx_id;
40 IF (lock_open%NOTFOUND) THEN
41 CLOSE lock_open;
42 RAISE NO_DATA_FOUND;
43 END IF;
44 CLOSE lock_open;
45
46 EXCEPTION
47 WHEN NO_DATA_FOUND THEN
48 IF lock_open%ISOPEN THEN
49 CLOSE lock_open;
50 END IF;
51 RAISE NO_DATA_FOUND;
52 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
53 IF lock_open%ISOPEN THEN
54 CLOSE lock_open;
55 END IF;
56 RAISE APP_EXCEPTION.RECORD_LOCK_EXCEPTION;
57 */
58
59 END lock_row;
60
61 /* ---------------------------------------------------------------------
62 | PUBLIC PROCEDURE |
63 | clear |
64 | |
65 | DESCRIPTION |
66 | This procedure would be called during clearing phase |
67 | |
68 | |
69 | HISTORY |
70 --------------------------------------------------------------------- */
71 PROCEDURE clear(
72 X_trx_id NUMBER, -- transaction id
73 X_trx_type VARCHAR2, -- transaction type ('PAYMENT'/'CASH')
74 X_status VARCHAR2, -- status
75 X_trx_number VARCHAR2, -- transaction number
76 X_trx_date DATE, -- transaction date
77 X_trx_currency VARCHAR2, -- transaction currency code
78 X_gl_date DATE, -- gl date
79 X_bank_currency VARCHAR2, -- bank currency code
80 X_cleared_amount NUMBER, -- amount to be cleared
81 X_cleared_date DATE, -- cleared date
82 X_charges_amount NUMBER, -- charges amount
83 X_errors_amount NUMBER, -- errors amount
84 X_exchange_date DATE, -- exchange rate date
85 X_exchange_type VARCHAR2, -- exchange rate type
86 X_exchange_rate NUMBER -- exchange rate
87 )IS
88
89 BEGIN
90
91 /* Note: ce_999_interface_v is the base table of ce_999_interface_v in this
92 example */
93
94 /* Note: You are required to pass the status column to your proprietary
95 database. The Reconciliation Open Interface feature requires a
96 non-null status column to function correctly. */
97
98 /* Note: If you have not defined the value for the open interface clear status
99 in the System Parameter Form, i.e. OPEN_INTERFACE_CLEAR_STATUS column
100 in the CE_SYSTEM_PARAMETERS_ALL table, you are required to do so. */
101
102 /* Example of clear procedure
103
104 update ce_999_interface_v
105 set status = X_status,
106 gl_date = X_gl_date,
107 cleared_amount = X_cleared_amount,
108 cleared_date = X_cleared_date,
109 charges_amount = X_charges_amount,
110 error_amount = X_errors_amount,
111 exchange_rate_date = X_exchange_date,
112 exchange_rate_type = X_exchange_type,
113 exchange_rate = X_exchange_rate
114 where trx_id = X_trx_id;
115
116 */
117 null;
118
119 /* Reconciliation Accounting Logic goes here */
120
121 END clear;
122
123 /* ---------------------------------------------------------------------
124 | PUBLIC PROCEDURE |
125 | unclear |
126 | |
127 | DESCRIPTION |
128 | This procedure would be called during unclearing phase |
129 | |
130 | HISTORY |
131 --------------------------------------------------------------------- */
132 PROCEDURE unclear(
133 X_trx_id NUMBER, -- transaction id
134 X_trx_type VARCHAR2, -- transaction type ('PAYMENT'/'CASH')
135 X_status VARCHAR2, -- status
136 X_trx_date DATE, -- transaction date
137 X_gl_date DATE -- gl date
138 )IS
139
140 BEGIN
141
142 /* Note: ce_999_interface_v is the base table of ce_999_interface_v in this
143 example */
144
145 /* Note: You are required to pass the status column to your proprietary
146 database. The Reconciliation Open Interface feature requires a
147 non-null status column to function correctly. */
148
149 /* Note: If you have not defined the value for the open interface float status
150 in the System Parameter Form, i.e. OPEN_INTERFACE_FLOAT_STATUS column
151 in the CE_SYSTEM_PARAMETERS_ALL table, you are required to do so. */
152
153 /* Example of unclear procedure
154
155 update ce_999_interface_v
156 set status = X_status,
157 gl_date = NULL,
158 cleared_amount = NULL,
159 cleared_date = NULL,
160 charges_amount = NULL,
161 error_amount = NULL,
162 exchange_rate_date = NULL,
163 exchange_rate_type = NULL,
164 exchange_rate = NULL
165 where trx_id = X_trx_id;
166 */
167
168 /* Reconciliation Accounting Logic goes here */
169 null;
170
171 END unclear;
172
173 END CE_999_PKG;