[Home] [Help]
PACKAGE: APPS.CE_AUTO_BANK_MATCH
Source
1 PACKAGE CE_AUTO_BANK_MATCH AUTHID CURRENT_USER AS
2 /* $Header: ceabrmas.pls 120.16 2008/01/23 13:20:22 kbabu ship $ */
3 --
4 -- Global variables
5 --
6 ar_accounting_method AR_SYSTEM_PARAMETERS_ALL.accounting_method%TYPE;
7
8 av_101_inserted_flag VARCHAR2(1) DEFAULT 'N';
9 av_200_inserted_flag VARCHAR2(1) DEFAULT 'N';
10 av_222_inserted_flag VARCHAR2(1) DEFAULT 'N';
11 av_260_inserted_flag VARCHAR2(1) DEFAULT 'N';
12 av_260_cf_inserted_flag VARCHAR2(1) DEFAULT 'N';
13 av_801_inserted_flag VARCHAR2(1) DEFAULT 'N';
14 av_801_eft_inserted_flag VARCHAR2(1) DEFAULT 'N';
15 av_999_inserted_flag VARCHAR2(1) DEFAULT 'N';
16 av_185_inserted_flag VARCHAR2(1) DEFAULT 'N';
17
18 gt_seq_id NUMBER(15);
19 gt_seq_id2 NUMBER(15);
20
21
22
23 -- Statement headers
24 --
25 csh_rowid VARCHAR2(100);
26 csh_statement_header_id CE_STATEMENT_HEADERS.statement_header_id%TYPE;
27 csh_statement_date CE_STATEMENT_HEADERS.statement_date%TYPE;
28 csh_statement_gl_date CE_STATEMENT_HEADERS.gl_date%TYPE;
29 csh_check_digits CE_STATEMENT_HEADERS.check_digits%TYPE;
30 csh_bank_account_id CE_STATEMENT_HEADERS.bank_account_id%TYPE;
31 csh_statement_number CE_STATEMENT_HEADERS.statement_number%TYPE;
32 csh_statement_complete_flag CE_STATEMENT_HEADERS.statement_complete_flag%TYPE;
33
34 --
35 -- Bank Accounts/Bank Account Uses
36 --
37 aba_bank_currency CE_BANK_ACCOUNTS.currency_code%TYPE;
38 aba_asset_code_combination_id number; /*for JEC --AP_BANK_ACCOUNTS_ALL.asset_code_combination_id*/
39 aba_multi_currency_flag CE_BANK_ACCOUNTS.MULTI_CURRENCY_ALLOWED_FLAG%TYPE;
40 aba_check_digits CE_BANK_ACCOUNTS.check_digits%TYPE;
41 ba_ap_amount_tolerance CE_BANK_ACCOUNTS.ap_amount_tolerance%TYPE;
42 ba_ap_percent_tolerance CE_BANK_ACCOUNTS.ap_percent_tolerance%TYPE;
43 ba_ar_amount_tolerance CE_BANK_ACCOUNTS.ar_amount_tolerance%TYPE;
44 ba_ar_percent_tolerance CE_BANK_ACCOUNTS.ar_percent_tolerance%TYPE;
45 ba_ce_amount_tolerance CE_BANK_ACCOUNTS.ce_amount_tolerance%TYPE;
46 ba_ce_percent_tolerance CE_BANK_ACCOUNTS.ce_percent_tolerance%TYPE;
47 /*ba_xtr_amount_tolerance CE_BANK_ACCOUNTS.xtr_amount_tolerance%TYPE;
48 ba_xtr_percent_tolerance CE_BANK_ACCOUNTS.xtr_percent_tolerance%TYPE;
49 ba_pay_amount_tolerance CE_BANK_ACCOUNTS.pay_amount_tolerance%TYPE;
50 ba_pay_percent_tolerance CE_BANK_ACCOUNTS.pay_percent_tolerance%TYPE;
51 */
52 BA_ROWID VARCHAR2(100);
53 BA_OWNER_LE_ID NUMBER;
54
55 BA_RECON_OI_AMOUNT_TOLERANCE NUMBER;
56 BA_RECON_OI_PERCENT_TOLERANCE NUMBER;
57 BA_RECON_AP_FX_DIFF_HANDLING VARCHAR2(30);
58 BA_RECON_AR_FX_DIFF_HANDLING VARCHAR2(30);
59 BA_RECON_CE_FX_DIFF_HANDLING VARCHAR2(30);
60
61 -- FROM CE_BANK_ACCT_USES_ALL
62
63 BAU_ROWID VARCHAR2(100);
64 bau_bank_acct_use_id CE_BANK_ACCT_USES_ALL.bank_acct_use_id%TYPE;
65 bau_ap_use_enable_flag CE_BANK_ACCT_USES_ALL.ap_use_enable_flag%TYPE;
66 bau_ar_use_enable_flag CE_BANK_ACCT_USES_ALL.ar_use_enable_flag%TYPE;
67 bau_xtr_use_enable_flag CE_BANK_ACCT_USES_ALL.xtr_use_enable_flag%TYPE;
68 bau_pay_use_enable_flag CE_BANK_ACCT_USES_ALL.pay_use_enable_flag%TYPE;
69 bau_org_id CE_BANK_ACCT_USES_ALL.org_id%TYPE;
70 bau_legal_entity_id CE_BANK_ACCT_USES_ALL.legal_entity_id%TYPE;
71
72 trx_bank_acct_use_id CE_BANK_ACCT_USES_ALL.bank_acct_use_id%TYPE;
73
74 G_receivables_trx_id NUMBER(15);
75 G_receivables_trx_dsp VARCHAR2(100);
76
77
78 -- LE global values
79 /*
80 G_le_fx_difference_handling VARCHAR2(30);
81 G_le_amount_tolerance NUMBER;
82 G_le_percent_tolerance NUMBER;
83 */
84 --
85 -- Statement Lines
86 --
87 csl_rowid VARCHAR2(100);
88 csl_statement_line_id CE_STATEMENT_LINES.statement_line_id%TYPE;
89 csl_line_number CE_STATEMENT_LINES.line_number%TYPE;
90 csl_trx_date CE_STATEMENT_LINES.trx_date%TYPE;
91 csl_trx_type CE_STATEMENT_LINES.trx_type%TYPE;
92 csl_trx_code_id CE_STATEMENT_LINES.trx_code_id%TYPE;
93 csl_bank_trx_number CE_STATEMENT_LINES.bank_trx_number%TYPE;
94 csl_invoice_text CE_STATEMENT_LINES.invoice_text%TYPE;
95 csl_bank_account_text CE_STATEMENT_LINES.bank_account_text%TYPE;
96 csl_amount CE_STATEMENT_LINES.amount%TYPE;
97 csl_charges_amount CE_STATEMENT_LINES.charges_amount%TYPE;
98 corr_csl_amount CE_STATEMENT_LINES.amount%TYPE;
99 calc_csl_amount CE_STATEMENT_LINES.amount%TYPE;
100 csl_receivables_trx_id CE_TRANSACTION_CODES.receivables_trx_id%TYPE;
101 csl_receipt_method_id CE_TRANSACTION_CODES.receipt_method_id%TYPE;
102 csl_create_misc_trx_flag CE_TRANSACTION_CODES.create_misc_trx_flag%TYPE;
103 csl_matching_against CE_TRANSACTION_CODES.matching_against%TYPE;
104 csl_correction_method CE_TRANSACTION_CODES.correction_method%TYPE;
105 csl_reconcile_flag CE_TRANSACTION_CODES.reconcile_flag%TYPE;
106 csl_receipt_method_name AR_RECEIPT_METHODS.name%TYPE;
107 csl_currency_code FND_CURRENCIES.currency_code%TYPE;
108 csl_line_trx_type CE_STATEMENT_LINES.trx_type%TYPE;
109 csl_exchange_rate_type GL_DAILY_RATES.conversion_type%TYPE;
110 csl_exchange_rate_date GL_DAILY_RATES.conversion_date%TYPE;
111 csl_exchange_rate GL_DAILY_RATES.conversion_rate%TYPE;
112 csl_match_found FND_LOOKUP_VALUES.lookup_code%TYPE;
113 csl_original_amount CE_STATEMENT_LINES.original_amount%TYPE;
114 csl_payroll_payment_format PAY_PAYMENT_TYPES.payment_type_name%TYPE;
115 csl_clearing_trx_type FND_LOOKUP_VALUES.lookup_code%TYPE;
119 csl_accounting_date CE_STATEMENT_LINES.accounting_date%TYPE;
116 csl_customer_text CE_STATEMENT_LINES.customer_text%TYPE;
117 csl_effective_date CE_STATEMENT_LINES.effective_date%TYPE;
118 csl_je_status_flag CE_STATEMENT_LINES.je_status_flag%TYPE;
120 csl_cashflow_id CE_STATEMENT_LINES.cashflow_id%TYPE;
121
122 reconcile_to_statement_flag CE_STATEMENT_LINES.reconcile_to_statement_flag%TYPE;
123 foreign_exchange_defaulted VARCHAR2(1) DEFAULT 'N';
124 csl_event_id NUMBER(15);
125
126 --
127 -- Transactions
128 --
129 trx_id AR_CASH_RECEIPT_HISTORY_ALL.cash_receipt_history_id%TYPE;
130 trx_cash_receipt_id AR_CASH_RECEIPT_HISTORY_ALL.cash_receipt_id%TYPE;
131 trx_rowid VARCHAR2(100);
132 trx_date DATE;
133 trx_currency_code FND_CURRENCIES.currency_code%TYPE;
134 trx_amount AR_CASH_RECEIPT_HISTORY_ALL.amount%TYPE;
135 trx_base_amount AR_CASH_RECEIPT_HISTORY_ALL.acctd_amount%TYPE;
136 trx_cleared_amount AR_CASH_RECEIPT_HISTORY_ALL.amount%TYPE;
137 trx_curr_amount AR_CASH_RECEIPT_HISTORY_ALL.amount%TYPE;
138 trx_currency_type FND_LOOKUP_VALUES.lookup_code%TYPE;
139 trx_status AR_CASH_RECEIPT_HISTORY_ALL.status%TYPE;
140 trx_errors_amount CE_STATEMENT_LINES.amount%TYPE;
141 trx_charges_amount CE_STATEMENT_LINES.amount%TYPE;
142 trx_prorate_amount CE_STATEMENT_LINES.amount%TYPE;
143 trx_exchange_rate_type GL_DAILY_RATES.conversion_type%TYPE;
144 trx_exchange_rate_date GL_DAILY_RATES.conversion_date%TYPE;
145 trx_exchange_rate GL_DAILY_RATES.conversion_rate%TYPE;
146 trx_customer_id NUMBER; --RA_CUSTOMERS.customer_id%TYPE;
147 reversed_receipt_flag VARCHAR2(1);
148 trx_gl_date DATE;
149 trx_group VARCHAR2(100);
150 trx_count NUMBER;
151 trx_org_id NUMBER;
152 trx_legal_entity_id NUMBER;
153 trx_reference_type VARCHAR2(60);
154 trx_value_date DATE;
155 trx_cleared_date DATE;
156 trx_deposit_date DATE;
157 LOGICAL_GROUP_REFERENCE IBY_PAYMENTS_ALL.LOGICAL_GROUP_REFERENCE%type; -- FOR SEPA ER 6700007
158 --
159 -- Adjustment Statement lines
160 --
161 trx_id2 CE_STATEMENT_LINES.statement_line_id%TYPE;
162 trx_rowid2 VARCHAR2(100);
163 trx_date2 DATE;
164 trx_currency_code2 FND_CURRENCIES.currency_code%TYPE;
165 trx_amount2 AR_CASH_RECEIPT_HISTORY_ALL.amount%TYPE;
166 trx_base_amount2 AR_CASH_RECEIPT_HISTORY_ALL.acctd_amount%TYPE;
167 trx_cleared_amount2 AR_CASH_RECEIPT_HISTORY_ALL.amount%TYPE;
168 trx_curr_amount2 AR_CASH_RECEIPT_HISTORY_ALL.amount%TYPE;
169 trx_currency_type2 FND_LOOKUP_VALUES.lookup_code%TYPE;
170 trx_status2 AR_CASH_RECEIPT_HISTORY_ALL.status%TYPE;
171 trx_errors_amount2 CE_STATEMENT_LINES.amount%TYPE;
172 trx_charges_amount2 CE_STATEMENT_LINES.amount%TYPE;
173 trx_prorate_amount2 CE_STATEMENT_LINES.amount%TYPE;
174 trx_exchange_rate_type2 GL_DAILY_RATES.conversion_type%TYPE;
175 trx_exchange_rate_date2 GL_DAILY_RATES.conversion_date%TYPE;
176 trx_type2 FND_LOOKUP_VALUES.lookup_code%TYPE;
177 trx_exchange_rate2 GL_DAILY_RATES.conversion_rate%TYPE;
178 --
179 -- csl_match_type is blank when there is no matching transaction
180 -- it is one of the following when the match is found:
181 --
182 --
183 csl_match_type FND_LOOKUP_VALUES.lookup_code%TYPE;
184 csl_match_type2 FND_LOOKUP_VALUES.lookup_code%TYPE;
185 trx_match_type FND_LOOKUP_VALUES.lookup_code%TYPE;
186 csl_match_correction_type FND_LOOKUP_VALUES.lookup_code%TYPE;
187 reconciled_this_run VARCHAR2(1);
188 tolerance_amount CE_STATEMENT_LINES.amount%TYPE;
189 acctd_exchange_rate CE_STATEMENT_LINES.exchange_rate%TYPE;
190 nsf_info_flag VARCHAR2(1) DEFAULT 'N';
191 trx_clr_flag VARCHAR2(1) DEFAULT 'N';
192
193 --
194 -- Original transaction info
195 --
196 batch_exchange_rate_type AP_INV_SELECTION_CRITERIA_ALL.exchange_rate_type%TYPE;
197 batch_exchange_rate AP_INV_SELECTION_CRITERIA_ALL.exchange_rate%TYPE;
198 batch_exchange_rate_date AP_INV_SELECTION_CRITERIA_ALL.exchange_date%TYPE;
199
200 --
201 -- Variables required for SQL functions
202 --
203 yes_101 NUMBER DEFAULT 0;
204 yes_200 NUMBER DEFAULT 0;
205 yes_200_GROUP NUMBER DEFAULT 0; -- FOR SEPA ER 6700007
206 yes_222 NUMBER DEFAULT 0;
207 yes_260 NUMBER DEFAULT 0;
208 yes_801 NUMBER DEFAULT 0;
209 yes_999 NUMBER DEFAULT 0;
210 display_inverse_rate VARCHAR2(1) DEFAULT 'N';
211
212 G_spec_revision VARCHAR2(1000) := '$Revision: 120.16 $';
213
214
215 --
216 -- Functions/procedures required for SQL functions
217 --
218 FUNCTION spec_revision RETURN VARCHAR2;
219 FUNCTION body_revision RETURN VARCHAR2;
220 PROCEDURE set_101;
221 PROCEDURE set_200;
222 PROCEDURE set_200_group; -- FOR SEPA ER 6700007
223 PROCEDURE set_222;
224 PROCEDURE set_260;
225 PROCEDURE set_801;
226 PROCEDURE set_999;
227 PROCEDURE set_all;
228
229 PROCEDURE unset_101;
230 PROCEDURE unset_200;
231 PROCEDURE unset_200_group; -- FOR SEPA ER 6700007
232 PROCEDURE unset_222;
233 PROCEDURE unset_260;
234 PROCEDURE unset_801;
235 PROCEDURE unset_999;
236 PROCEDURE unset_all;
237
238 FUNCTION get_101 RETURN NUMBER;
239 PRAGMA RESTRICT_REFERENCES(get_101,WNDS,WNPS);
240
241 FUNCTION get_200 RETURN NUMBER;
242 PRAGMA RESTRICT_REFERENCES(get_200,WNDS,WNPS);
243
244 FUNCTION get_200_group RETURN NUMBER; -- FOR SEPA ER 6700007
245 PRAGMA RESTRICT_REFERENCES(get_200,WNDS,WNPS);
246
247 FUNCTION get_222 RETURN NUMBER;
248 PRAGMA RESTRICT_REFERENCES(get_222,WNDS,WNPS);
249
250 FUNCTION get_260 RETURN NUMBER;
251 PRAGMA RESTRICT_REFERENCES(get_260,WNDS,WNPS);
252
253 FUNCTION get_801 RETURN NUMBER;
254 PRAGMA RESTRICT_REFERENCES(get_801,WNDS,WNPS);
255
259 -- Function to return value of account type, based on payroll security profile
256 FUNCTION get_999 RETURN NUMBER;
257 PRAGMA RESTRICT_REFERENCES(get_999,WNDS,WNPS);
258 --
260 --
261 FUNCTION get_security_account_type(p_account_type VARCHAR2) RETURN VARCHAR2;
262 PRAGMA RESTRICT_REFERENCES(get_security_account_type,WNDS,WNPS);
263 --
264 -- Function to return the value for the
265 -- DISPLAY_INVERSE_RATE
266 --
267 PROCEDURE set_inverse_rate(inverse_rate VARCHAR2);
268
269 FUNCTION get_inverse_rate RETURN VARCHAR2;
270 PRAGMA RESTRICT_REFERENCES(get_inverse_rate,WNDS,WNPS);
271
272 --FUNCTION get_vat_tax_id RETURN NUMBER;
273 PROCEDURE get_vat_tax_id (X_pass_mode VARCHAR2,
274 l_vat_tax_id OUT NOCOPY NUMBER,
275 X_tax_rate OUT NOCOPY NUMBER);
276
277 FUNCTION convert_amount_tolerance (amount_to_convert NUMBER) RETURN NUMBER;
278
279 FUNCTION validate_payment_method RETURN BOOLEAN;
280
281 FUNCTION trx_validation (no_of_currencies NUMBER) RETURN BOOLEAN;
282
283 PROCEDURE calc_actual_tolerance;
284
285 PROCEDURE match_process;
286
287 /* ---------------------------------------------------------------------
288 | PUBLIC PROCEDURE |
289 | populate_available_gt |
290 | |
291 | DESCRIPTION |
292 | populate ce_available_transactions_tmp for auto reconciliation |
293 | |
294 | CALLED BY |
295 | match_process |
296 | |
297 | HISTORY |
298 | 11-MAY-2006 Xin Wang Created |
299 --------------------------------------------------------------------- */
300 PROCEDURE populate_available_gt (p_bank_account_id NUMBER);
301
302 /* ---------------------------------------------------------------------
303 | PUBLIC PROCEDURE |
304 | update_gt_reconciled_status |
305 | |
306 | DESCRIPTION |
307 | update the reconciled_status_flag of table |
308 | ce_available_transactions_tmp |
309 | mainly used to update the status to 'Y' |
310 | |
311 | CALLED BY |
312 | match_process |
313 | match_stmt_line_JE |
314 | CE_AUTO_BANK_CLEAR1.reconcile_pbatch |
315 | CE_AUTO_BANK_CLEAR1.reconcile_rbatch |
316 | CE_AUTO_BANK_CLEAR1.reconcile_pay_eft |
317 | |
318 | HISTORY |
319 | 11-MAY-2006 Xin Wang Created |
320 --------------------------------------------------------------------- */
321 PROCEDURE update_gt_reconciled_status(p_seq_id NUMBER,
322 p_status VARCHAR2);
323
324 /* ---------------------------------------------------------------------
325 | PUBLIC PROCEDURE |
326 | update_gt_reconciled_status |
327 | |
328 | DESCRIPTION |
329 | update the reconciled_status_flag of table |
330 | ce_available_transactions_tmp |
331 | mainly used to update the status to 'N' during unreconciliation |
332 | |
333 | CALLED BY |
334 | CE_AUTO_BANK_CLEAR1.unclear_process |
335 | |
336 | HISTORY |
337 | 11-MAY-2006 Xin Wang Created |
338 --------------------------------------------------------------------- */
339 PROCEDURE update_gt_reconciled_status(p_application_id NUMBER,
340 p_trx_id NUMBER,
341 p_reconciled_status VARCHAR2);
342
343 /* ---------------------------------------------------------------------
344 | PUBLIC PROCEDURE |
345 | update_gt_reconciled_status |
346 | |
347 | DESCRIPTION |
348 | update the reconciled_status_flag of table |
349 | ce_available_transactions_tmp |
350 | mainly used to update the status to 'N' during |
351 | auto unreconciliation |
352 | |
353 | CALLED BY |
354 | CE_AUTO_BANK_CLEAR1.unclear_process |
355 | |
356 | HISTORY |
357 | 11-MAY-2006 Xin Wang Created |
358 --------------------------------------------------------------------- */
359 PROCEDURE update_gt_reconciled_status(p_reconciled_status VARCHAR2);
360
361 PROCEDURE lock_transaction ( X_RECONCILE_FLAG VARCHAR2,
362 X_CALL_MODE VARCHAR2,
363 X_TRX_TYPE VARCHAR2,
364 X_CLEARING_TRX_TYPE VARCHAR2,
365 X_TRX_ROWID VARCHAR2,
366 X_BATCH_BA_AMOUNT NUMBER,
367 X_MATCH_CORRECTION_TYPE VARCHAR2 DEFAULT NULL,
368 X_LOGICAL_GROUP_REFERENCE VARCHAR2 DEFAULT NULL);
369
370
371 END CE_AUTO_BANK_MATCH;