eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE: APPS.CE_AUTO_BANK_MATCH

Source


1 PACKAGE CE_AUTO_BANK_MATCH 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;
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;
119 csl_accounting_date 		CE_STATEMENT_LINES.accounting_date%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 
256 FUNCTION get_999 RETURN NUMBER;
257 PRAGMA   RESTRICT_REFERENCES(get_999,WNDS,WNPS);
258 --
259 -- Function to return value of account type, based on payroll security profile
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;