[Home] [Help]
1: PACKAGE BODY CE_ZBA_DEAL_GENERATION AS
2: /* $Header: cezdgenb.pls 120.16.12020000.4 2012/08/28 12:37:13 vnetan ship $ */
3: --
4: -- MAIN CURSORS
5: --
122: IS
123: X_ERROR_MSG VARCHAR2(1000);
124:
125: BEGIN
126: cep_standard.debug('>> CE_ZBA_DEAL_GENERATION.xtr_shared_account stub <<');
127: -- cep_standard.debug('ORG_ID = '|| CE_ZBA_DEAL_GENERATION.csh_org_id);
128: -- cep_standard.debug('BANK_ACCOUNT_ID = '|| CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
129: -- cep_standard.debug('CURRENCY_CODE = '|| CE_ZBA_DEAL_GENERATION.cba_bank_currency);
130: --
123: X_ERROR_MSG VARCHAR2(1000);
124:
125: BEGIN
126: cep_standard.debug('>> CE_ZBA_DEAL_GENERATION.xtr_shared_account stub <<');
127: -- cep_standard.debug('ORG_ID = '|| CE_ZBA_DEAL_GENERATION.csh_org_id);
128: -- cep_standard.debug('BANK_ACCOUNT_ID = '|| CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
129: -- cep_standard.debug('CURRENCY_CODE = '|| CE_ZBA_DEAL_GENERATION.cba_bank_currency);
130: --
131: -- XTR_WRAPPER_API_P.bank_account_verification(
124:
125: BEGIN
126: cep_standard.debug('>> CE_ZBA_DEAL_GENERATION.xtr_shared_account stub <<');
127: -- cep_standard.debug('ORG_ID = '|| CE_ZBA_DEAL_GENERATION.csh_org_id);
128: -- cep_standard.debug('BANK_ACCOUNT_ID = '|| CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
129: -- cep_standard.debug('CURRENCY_CODE = '|| CE_ZBA_DEAL_GENERATION.cba_bank_currency);
130: --
131: -- XTR_WRAPPER_API_P.bank_account_verification(
132: -- P_ORG_ID => CE_ZBA_DEAL_GENERATION.csh_org_id,
125: BEGIN
126: cep_standard.debug('>> CE_ZBA_DEAL_GENERATION.xtr_shared_account stub <<');
127: -- cep_standard.debug('ORG_ID = '|| CE_ZBA_DEAL_GENERATION.csh_org_id);
128: -- cep_standard.debug('BANK_ACCOUNT_ID = '|| CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
129: -- cep_standard.debug('CURRENCY_CODE = '|| CE_ZBA_DEAL_GENERATION.cba_bank_currency);
130: --
131: -- XTR_WRAPPER_API_P.bank_account_verification(
132: -- P_ORG_ID => CE_ZBA_DEAL_GENERATION.csh_org_id,
133: -- P_AP_BANK_ACCOUNT_ID => CE_ZBA_DEAL_GENERATION.csh_bank_account_id,
128: -- cep_standard.debug('BANK_ACCOUNT_ID = '|| CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
129: -- cep_standard.debug('CURRENCY_CODE = '|| CE_ZBA_DEAL_GENERATION.cba_bank_currency);
130: --
131: -- XTR_WRAPPER_API_P.bank_account_verification(
132: -- P_ORG_ID => CE_ZBA_DEAL_GENERATION.csh_org_id,
133: -- P_AP_BANK_ACCOUNT_ID => CE_ZBA_DEAL_GENERATION.csh_bank_account_id,
134: -- P_CURRENCY_CODE => CE_ZBA_DEAL_GENERATION.cba_bank_currency,
135: -- P_RESULT => X_ACCOUNT_RESULT,
136: -- P_ERROR_MSG => X_ERROR_MSG);
129: -- cep_standard.debug('CURRENCY_CODE = '|| CE_ZBA_DEAL_GENERATION.cba_bank_currency);
130: --
131: -- XTR_WRAPPER_API_P.bank_account_verification(
132: -- P_ORG_ID => CE_ZBA_DEAL_GENERATION.csh_org_id,
133: -- P_AP_BANK_ACCOUNT_ID => CE_ZBA_DEAL_GENERATION.csh_bank_account_id,
134: -- P_CURRENCY_CODE => CE_ZBA_DEAL_GENERATION.cba_bank_currency,
135: -- P_RESULT => X_ACCOUNT_RESULT,
136: -- P_ERROR_MSG => X_ERROR_MSG);
137: --
130: --
131: -- XTR_WRAPPER_API_P.bank_account_verification(
132: -- P_ORG_ID => CE_ZBA_DEAL_GENERATION.csh_org_id,
133: -- P_AP_BANK_ACCOUNT_ID => CE_ZBA_DEAL_GENERATION.csh_bank_account_id,
134: -- P_CURRENCY_CODE => CE_ZBA_DEAL_GENERATION.cba_bank_currency,
135: -- P_RESULT => X_ACCOUNT_RESULT,
136: -- P_ERROR_MSG => X_ERROR_MSG);
137: --
138: -- cep_standard.debug('x_account_result = ' || x_account_result);
138: -- cep_standard.debug('x_account_result = ' || x_account_result);
139: -- cep_standard.debug('x_error_msg = ' || x_error_msg);
140: EXCEPTION
141: WHEN OTHERS THEN
142: cep_standard.debug('EXCEPTION: CE_ZBA_DEAL_GENERATION.xtr_shared_account');
143: RAISE;
144: END xtr_shared_account;
145:
146:
169: BEGIN
170: SELECT statement_header_id
171: INTO x_statement_header_id
172: FROM ce_statement_headers
173: WHERE rowid = CE_ZBA_DEAL_GENERATION.csh_rowid
174: FOR UPDATE OF statement_header_id NOWAIT;
175:
176: cep_standard.debug('Regular statement lock OK');
177: lockname := CE_ZBA_DEAL_GENERATION.csh_rowid;
173: WHERE rowid = CE_ZBA_DEAL_GENERATION.csh_rowid
174: FOR UPDATE OF statement_header_id NOWAIT;
175:
176: cep_standard.debug('Regular statement lock OK');
177: lockname := CE_ZBA_DEAL_GENERATION.csh_rowid;
178: timeout := 1;
179: lockmode := 6;
180: expiration_secs := 10;
181: release_on_commit := FALSE;
192:
193: RETURN(TRUE);
194: EXCEPTION
195: WHEN APP_EXCEPTIONS.record_lock_exception THEN
196: cep_standard.debug('EXCEPTION: CE_ZBA_DEAL_GENERATION.lock_statement - record_lock_exception');
197: RETURN(FALSE);
198: WHEN OTHERS THEN
199: cep_standard.debug('EXCEPTION: CE_ZBA_DEAL_GENERATION.lock_statement - others');
200: RAISE;
195: WHEN APP_EXCEPTIONS.record_lock_exception THEN
196: cep_standard.debug('EXCEPTION: CE_ZBA_DEAL_GENERATION.lock_statement - record_lock_exception');
197: RETURN(FALSE);
198: WHEN OTHERS THEN
199: cep_standard.debug('EXCEPTION: CE_ZBA_DEAL_GENERATION.lock_statement - others');
200: RAISE;
201: RETURN(FALSE);
202: END lock_statement;
203:
226: line_number,
227: customer_text,
228: effective_date,
229: original_amount
230: INTO CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
231: CE_ZBA_DEAL_GENERATION.csl_trx_date,
232: CE_ZBA_DEAL_GENERATION.csl_trx_type,
233: CE_ZBA_DEAL_GENERATION.csl_trx_code_id,
234: CE_ZBA_DEAL_GENERATION.csl_bank_trx_number,
227: customer_text,
228: effective_date,
229: original_amount
230: INTO CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
231: CE_ZBA_DEAL_GENERATION.csl_trx_date,
232: CE_ZBA_DEAL_GENERATION.csl_trx_type,
233: CE_ZBA_DEAL_GENERATION.csl_trx_code_id,
234: CE_ZBA_DEAL_GENERATION.csl_bank_trx_number,
235: CE_ZBA_DEAL_GENERATION.csl_invoice_text,
228: effective_date,
229: original_amount
230: INTO CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
231: CE_ZBA_DEAL_GENERATION.csl_trx_date,
232: CE_ZBA_DEAL_GENERATION.csl_trx_type,
233: CE_ZBA_DEAL_GENERATION.csl_trx_code_id,
234: CE_ZBA_DEAL_GENERATION.csl_bank_trx_number,
235: CE_ZBA_DEAL_GENERATION.csl_invoice_text,
236: CE_ZBA_DEAL_GENERATION.csl_bank_account_text,
229: original_amount
230: INTO CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
231: CE_ZBA_DEAL_GENERATION.csl_trx_date,
232: CE_ZBA_DEAL_GENERATION.csl_trx_type,
233: CE_ZBA_DEAL_GENERATION.csl_trx_code_id,
234: CE_ZBA_DEAL_GENERATION.csl_bank_trx_number,
235: CE_ZBA_DEAL_GENERATION.csl_invoice_text,
236: CE_ZBA_DEAL_GENERATION.csl_bank_account_text,
237: CE_ZBA_DEAL_GENERATION.csl_amount,
230: INTO CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
231: CE_ZBA_DEAL_GENERATION.csl_trx_date,
232: CE_ZBA_DEAL_GENERATION.csl_trx_type,
233: CE_ZBA_DEAL_GENERATION.csl_trx_code_id,
234: CE_ZBA_DEAL_GENERATION.csl_bank_trx_number,
235: CE_ZBA_DEAL_GENERATION.csl_invoice_text,
236: CE_ZBA_DEAL_GENERATION.csl_bank_account_text,
237: CE_ZBA_DEAL_GENERATION.csl_amount,
238: CE_ZBA_DEAL_GENERATION.csl_charges_amount,
231: CE_ZBA_DEAL_GENERATION.csl_trx_date,
232: CE_ZBA_DEAL_GENERATION.csl_trx_type,
233: CE_ZBA_DEAL_GENERATION.csl_trx_code_id,
234: CE_ZBA_DEAL_GENERATION.csl_bank_trx_number,
235: CE_ZBA_DEAL_GENERATION.csl_invoice_text,
236: CE_ZBA_DEAL_GENERATION.csl_bank_account_text,
237: CE_ZBA_DEAL_GENERATION.csl_amount,
238: CE_ZBA_DEAL_GENERATION.csl_charges_amount,
239: CE_ZBA_DEAL_GENERATION.csl_currency_code,
232: CE_ZBA_DEAL_GENERATION.csl_trx_type,
233: CE_ZBA_DEAL_GENERATION.csl_trx_code_id,
234: CE_ZBA_DEAL_GENERATION.csl_bank_trx_number,
235: CE_ZBA_DEAL_GENERATION.csl_invoice_text,
236: CE_ZBA_DEAL_GENERATION.csl_bank_account_text,
237: CE_ZBA_DEAL_GENERATION.csl_amount,
238: CE_ZBA_DEAL_GENERATION.csl_charges_amount,
239: CE_ZBA_DEAL_GENERATION.csl_currency_code,
240: CE_ZBA_DEAL_GENERATION.csl_line_number,
233: CE_ZBA_DEAL_GENERATION.csl_trx_code_id,
234: CE_ZBA_DEAL_GENERATION.csl_bank_trx_number,
235: CE_ZBA_DEAL_GENERATION.csl_invoice_text,
236: CE_ZBA_DEAL_GENERATION.csl_bank_account_text,
237: CE_ZBA_DEAL_GENERATION.csl_amount,
238: CE_ZBA_DEAL_GENERATION.csl_charges_amount,
239: CE_ZBA_DEAL_GENERATION.csl_currency_code,
240: CE_ZBA_DEAL_GENERATION.csl_line_number,
241: CE_ZBA_DEAL_GENERATION.csl_customer_text,
234: CE_ZBA_DEAL_GENERATION.csl_bank_trx_number,
235: CE_ZBA_DEAL_GENERATION.csl_invoice_text,
236: CE_ZBA_DEAL_GENERATION.csl_bank_account_text,
237: CE_ZBA_DEAL_GENERATION.csl_amount,
238: CE_ZBA_DEAL_GENERATION.csl_charges_amount,
239: CE_ZBA_DEAL_GENERATION.csl_currency_code,
240: CE_ZBA_DEAL_GENERATION.csl_line_number,
241: CE_ZBA_DEAL_GENERATION.csl_customer_text,
242: CE_ZBA_DEAL_GENERATION.csl_effective_date,
235: CE_ZBA_DEAL_GENERATION.csl_invoice_text,
236: CE_ZBA_DEAL_GENERATION.csl_bank_account_text,
237: CE_ZBA_DEAL_GENERATION.csl_amount,
238: CE_ZBA_DEAL_GENERATION.csl_charges_amount,
239: CE_ZBA_DEAL_GENERATION.csl_currency_code,
240: CE_ZBA_DEAL_GENERATION.csl_line_number,
241: CE_ZBA_DEAL_GENERATION.csl_customer_text,
242: CE_ZBA_DEAL_GENERATION.csl_effective_date,
243: CE_ZBA_DEAL_GENERATION.csl_original_amount
236: CE_ZBA_DEAL_GENERATION.csl_bank_account_text,
237: CE_ZBA_DEAL_GENERATION.csl_amount,
238: CE_ZBA_DEAL_GENERATION.csl_charges_amount,
239: CE_ZBA_DEAL_GENERATION.csl_currency_code,
240: CE_ZBA_DEAL_GENERATION.csl_line_number,
241: CE_ZBA_DEAL_GENERATION.csl_customer_text,
242: CE_ZBA_DEAL_GENERATION.csl_effective_date,
243: CE_ZBA_DEAL_GENERATION.csl_original_amount
244: FROM ce_statement_lines
237: CE_ZBA_DEAL_GENERATION.csl_amount,
238: CE_ZBA_DEAL_GENERATION.csl_charges_amount,
239: CE_ZBA_DEAL_GENERATION.csl_currency_code,
240: CE_ZBA_DEAL_GENERATION.csl_line_number,
241: CE_ZBA_DEAL_GENERATION.csl_customer_text,
242: CE_ZBA_DEAL_GENERATION.csl_effective_date,
243: CE_ZBA_DEAL_GENERATION.csl_original_amount
244: FROM ce_statement_lines
245: WHERE rowid = CE_ZBA_DEAL_GENERATION.csl_rowid
238: CE_ZBA_DEAL_GENERATION.csl_charges_amount,
239: CE_ZBA_DEAL_GENERATION.csl_currency_code,
240: CE_ZBA_DEAL_GENERATION.csl_line_number,
241: CE_ZBA_DEAL_GENERATION.csl_customer_text,
242: CE_ZBA_DEAL_GENERATION.csl_effective_date,
243: CE_ZBA_DEAL_GENERATION.csl_original_amount
244: FROM ce_statement_lines
245: WHERE rowid = CE_ZBA_DEAL_GENERATION.csl_rowid
246: FOR UPDATE OF status NOWAIT;
239: CE_ZBA_DEAL_GENERATION.csl_currency_code,
240: CE_ZBA_DEAL_GENERATION.csl_line_number,
241: CE_ZBA_DEAL_GENERATION.csl_customer_text,
242: CE_ZBA_DEAL_GENERATION.csl_effective_date,
243: CE_ZBA_DEAL_GENERATION.csl_original_amount
244: FROM ce_statement_lines
245: WHERE rowid = CE_ZBA_DEAL_GENERATION.csl_rowid
246: FOR UPDATE OF status NOWAIT;
247:
241: CE_ZBA_DEAL_GENERATION.csl_customer_text,
242: CE_ZBA_DEAL_GENERATION.csl_effective_date,
243: CE_ZBA_DEAL_GENERATION.csl_original_amount
244: FROM ce_statement_lines
245: WHERE rowid = CE_ZBA_DEAL_GENERATION.csl_rowid
246: FOR UPDATE OF status NOWAIT;
247:
248: RETURN(TRUE);
249: EXCEPTION
247:
248: RETURN(TRUE);
249: EXCEPTION
250: WHEN APP_EXCEPTIONS.record_lock_exception THEN
251: cep_standard.debug('EXCEPTION: CE_ZBA_DEAL_GENERATION.lock_statement_line - record_lock_exception');
252: return(FALSE);
253: WHEN OTHERS THEN
254: cep_standard.debug('EXCEPTION: CE_ZBA_DEAL_GENERATION.lock_statement_line - others');
255: RAISE;
250: WHEN APP_EXCEPTIONS.record_lock_exception THEN
251: cep_standard.debug('EXCEPTION: CE_ZBA_DEAL_GENERATION.lock_statement_line - record_lock_exception');
252: return(FALSE);
253: WHEN OTHERS THEN
254: cep_standard.debug('EXCEPTION: CE_ZBA_DEAL_GENERATION.lock_statement_line - others');
255: RAISE;
256: return(FALSE);
257: END lock_statement_line;
258:
274: BEGIN
275: SELECT min(line_number)
276: INTO min_statement_line_num
277: FROM ce_statement_lines
278: WHERE statement_header_id = CE_ZBA_DEAL_GENERATION.csh_statement_header_id;
279:
280: SELECT statement_line_id
281: INTO min_statement_line
282: FROM ce_statement_lines
280: SELECT statement_line_id
281: INTO min_statement_line
282: FROM ce_statement_lines
283: WHERE line_number = min_statement_line_num
284: AND statement_header_id = CE_ZBA_DEAL_GENERATION.csh_statement_header_id;
285:
286: RETURN (min_statement_line);
287: EXCEPTION
288: WHEN OTHERS THEN
285:
286: RETURN (min_statement_line);
287: EXCEPTION
288: WHEN OTHERS THEN
289: cep_standard.debug('EXCEPTION: CE_ZBA_DEAL_GENERATION.get_min_statement_line_id');
290: RAISE;
291: END get_min_statement_line_id;
292:
293: /* ---------------------------------------------------------------------
298: | Procedure to set parameter values into globals |
299: | CALLED BY |
300: | zba_generation |
301: | REQUIRES |
302: | all parameters of CE_ZBA_DEAL_GENERATION.zba_generation |
303: ----------------------------------------------------------------------*/
304: PROCEDURE set_parameters(
305: p_bank_branch_id NUMBER,
306: p_bank_account_id NUMBER,
312: p_debug_path VARCHAR2,
313: p_debug_file VARCHAR2
314: ) IS
315: BEGIN
316: CE_ZBA_DEAL_GENERATION.G_bank_branch_id := p_bank_branch_id;
317: CE_ZBA_DEAL_GENERATION.G_bank_account_id := p_bank_account_id;
318: CE_ZBA_DEAL_GENERATION.G_statement_number_from := p_statement_number_from;
319: CE_ZBA_DEAL_GENERATION.G_statement_number_to := p_statement_number_to;
320: CE_ZBA_DEAL_GENERATION.G_statement_date_from := to_date(p_statement_date_from,'YYYY/MM/DD HH24:MI:SS');
313: p_debug_file VARCHAR2
314: ) IS
315: BEGIN
316: CE_ZBA_DEAL_GENERATION.G_bank_branch_id := p_bank_branch_id;
317: CE_ZBA_DEAL_GENERATION.G_bank_account_id := p_bank_account_id;
318: CE_ZBA_DEAL_GENERATION.G_statement_number_from := p_statement_number_from;
319: CE_ZBA_DEAL_GENERATION.G_statement_number_to := p_statement_number_to;
320: CE_ZBA_DEAL_GENERATION.G_statement_date_from := to_date(p_statement_date_from,'YYYY/MM/DD HH24:MI:SS');
321: CE_ZBA_DEAL_GENERATION.G_statement_date_to := to_date(p_statement_date_to,'YYYY/MM/DD HH24:MI:SS');
314: ) IS
315: BEGIN
316: CE_ZBA_DEAL_GENERATION.G_bank_branch_id := p_bank_branch_id;
317: CE_ZBA_DEAL_GENERATION.G_bank_account_id := p_bank_account_id;
318: CE_ZBA_DEAL_GENERATION.G_statement_number_from := p_statement_number_from;
319: CE_ZBA_DEAL_GENERATION.G_statement_number_to := p_statement_number_to;
320: CE_ZBA_DEAL_GENERATION.G_statement_date_from := to_date(p_statement_date_from,'YYYY/MM/DD HH24:MI:SS');
321: CE_ZBA_DEAL_GENERATION.G_statement_date_to := to_date(p_statement_date_to,'YYYY/MM/DD HH24:MI:SS');
322: CE_ZBA_DEAL_GENERATION.G_display_debug := p_display_debug;
315: BEGIN
316: CE_ZBA_DEAL_GENERATION.G_bank_branch_id := p_bank_branch_id;
317: CE_ZBA_DEAL_GENERATION.G_bank_account_id := p_bank_account_id;
318: CE_ZBA_DEAL_GENERATION.G_statement_number_from := p_statement_number_from;
319: CE_ZBA_DEAL_GENERATION.G_statement_number_to := p_statement_number_to;
320: CE_ZBA_DEAL_GENERATION.G_statement_date_from := to_date(p_statement_date_from,'YYYY/MM/DD HH24:MI:SS');
321: CE_ZBA_DEAL_GENERATION.G_statement_date_to := to_date(p_statement_date_to,'YYYY/MM/DD HH24:MI:SS');
322: CE_ZBA_DEAL_GENERATION.G_display_debug := p_display_debug;
323: CE_ZBA_DEAL_GENERATION.G_debug_path := p_debug_path;
316: CE_ZBA_DEAL_GENERATION.G_bank_branch_id := p_bank_branch_id;
317: CE_ZBA_DEAL_GENERATION.G_bank_account_id := p_bank_account_id;
318: CE_ZBA_DEAL_GENERATION.G_statement_number_from := p_statement_number_from;
319: CE_ZBA_DEAL_GENERATION.G_statement_number_to := p_statement_number_to;
320: CE_ZBA_DEAL_GENERATION.G_statement_date_from := to_date(p_statement_date_from,'YYYY/MM/DD HH24:MI:SS');
321: CE_ZBA_DEAL_GENERATION.G_statement_date_to := to_date(p_statement_date_to,'YYYY/MM/DD HH24:MI:SS');
322: CE_ZBA_DEAL_GENERATION.G_display_debug := p_display_debug;
323: CE_ZBA_DEAL_GENERATION.G_debug_path := p_debug_path;
324: CE_ZBA_DEAL_GENERATION.G_debug_file := p_debug_file;
317: CE_ZBA_DEAL_GENERATION.G_bank_account_id := p_bank_account_id;
318: CE_ZBA_DEAL_GENERATION.G_statement_number_from := p_statement_number_from;
319: CE_ZBA_DEAL_GENERATION.G_statement_number_to := p_statement_number_to;
320: CE_ZBA_DEAL_GENERATION.G_statement_date_from := to_date(p_statement_date_from,'YYYY/MM/DD HH24:MI:SS');
321: CE_ZBA_DEAL_GENERATION.G_statement_date_to := to_date(p_statement_date_to,'YYYY/MM/DD HH24:MI:SS');
322: CE_ZBA_DEAL_GENERATION.G_display_debug := p_display_debug;
323: CE_ZBA_DEAL_GENERATION.G_debug_path := p_debug_path;
324: CE_ZBA_DEAL_GENERATION.G_debug_file := p_debug_file;
325: END set_parameters;
318: CE_ZBA_DEAL_GENERATION.G_statement_number_from := p_statement_number_from;
319: CE_ZBA_DEAL_GENERATION.G_statement_number_to := p_statement_number_to;
320: CE_ZBA_DEAL_GENERATION.G_statement_date_from := to_date(p_statement_date_from,'YYYY/MM/DD HH24:MI:SS');
321: CE_ZBA_DEAL_GENERATION.G_statement_date_to := to_date(p_statement_date_to,'YYYY/MM/DD HH24:MI:SS');
322: CE_ZBA_DEAL_GENERATION.G_display_debug := p_display_debug;
323: CE_ZBA_DEAL_GENERATION.G_debug_path := p_debug_path;
324: CE_ZBA_DEAL_GENERATION.G_debug_file := p_debug_file;
325: END set_parameters;
326:
319: CE_ZBA_DEAL_GENERATION.G_statement_number_to := p_statement_number_to;
320: CE_ZBA_DEAL_GENERATION.G_statement_date_from := to_date(p_statement_date_from,'YYYY/MM/DD HH24:MI:SS');
321: CE_ZBA_DEAL_GENERATION.G_statement_date_to := to_date(p_statement_date_to,'YYYY/MM/DD HH24:MI:SS');
322: CE_ZBA_DEAL_GENERATION.G_display_debug := p_display_debug;
323: CE_ZBA_DEAL_GENERATION.G_debug_path := p_debug_path;
324: CE_ZBA_DEAL_GENERATION.G_debug_file := p_debug_file;
325: END set_parameters;
326:
327: /* ---------------------------------------------------------------------
320: CE_ZBA_DEAL_GENERATION.G_statement_date_from := to_date(p_statement_date_from,'YYYY/MM/DD HH24:MI:SS');
321: CE_ZBA_DEAL_GENERATION.G_statement_date_to := to_date(p_statement_date_to,'YYYY/MM/DD HH24:MI:SS');
322: CE_ZBA_DEAL_GENERATION.G_display_debug := p_display_debug;
323: CE_ZBA_DEAL_GENERATION.G_debug_path := p_debug_path;
324: CE_ZBA_DEAL_GENERATION.G_debug_file := p_debug_file;
325: END set_parameters;
326:
327: /* ---------------------------------------------------------------------
328: | PRIVATE FUNCTION |
345: SELECT statement_header_id
346: FROM ce_statement_headers
347: WHERE bank_account_id = p_ap_bank_account_id;
348: BEGIN
349: cep_standard.debug('<
351: INTO code_row_count
352: FROM ce_transaction_codes
353: WHERE bank_account_id = p_ap_bank_account_id
386: EXIT WHEN G_sweep_flag = TRUE;
387: END LOOP;
388: CLOSE header_cursor;
389: END IF;
390: cep_standard.debug('<
392:
393:
394: /*========================================================================+
469: l_acct_type ce_cashpool_sub_accts.type%TYPE; -- Bug 14512483
470: l_offset_acct_type ce_cashpool_sub_accts.type%TYPE; -- Bug 14512483
471:
472: BEGIN
473: cep_standard.debug('>>CE_ZBA_DEAL_GENERATION.zba_generation');
474: cep_standard.debug('p_bank_branch_id : '|| p_bank_branch_id);
475: cep_standard.debug('p_bank_account_id : '|| p_bank_account_id);
476: cep_standard.debug('p_statement_number_from: '|| p_statement_number_from);
477: cep_standard.debug('p_statement_number_to : '|| p_statement_number_to);
493: p_debug_file);
494:
495: cep_standard.debug('Opening r_branch_cursor');
496: OPEN r_branch_cursor(
497: CE_ZBA_DEAL_GENERATION.G_bank_branch_id,
498: CE_ZBA_DEAL_GENERATION.G_bank_account_id);
499: j := 0;
500: LOOP
501: cep_standard.debug('Fetching r_branch_cursor');
494:
495: cep_standard.debug('Opening r_branch_cursor');
496: OPEN r_branch_cursor(
497: CE_ZBA_DEAL_GENERATION.G_bank_branch_id,
498: CE_ZBA_DEAL_GENERATION.G_bank_account_id);
499: j := 0;
500: LOOP
501: cep_standard.debug('Fetching r_branch_cursor');
502: FETCH r_branch_cursor INTO CE_ZBA_DEAL_GENERATION.csh_bank_account_id;
498: CE_ZBA_DEAL_GENERATION.G_bank_account_id);
499: j := 0;
500: LOOP
501: cep_standard.debug('Fetching r_branch_cursor');
502: FETCH r_branch_cursor INTO CE_ZBA_DEAL_GENERATION.csh_bank_account_id;
503: cep_standard.debug('r_branch_cursor%ROWCOUNT = ' || r_branch_cursor%ROWCOUNT );
504:
505: IF (r_branch_cursor%ROWCOUNT = j)
506: THEN
510: j := r_branch_cursor%ROWCOUNT;
511: END IF;
512:
513: cep_standard.debug('Opening r_bank_cursor');
514: cep_standard.debug('G_statement_number_from = ' || CE_ZBA_DEAL_GENERATION.G_statement_number_from);
515: cep_standard.debug('G_statement_number_to = ' || CE_ZBA_DEAL_GENERATION.G_statement_number_to);
516: cep_standard.debug('G_statement_date_from = ' || CE_ZBA_DEAL_GENERATION.G_statement_date_from);
517: cep_standard.debug('G_statement_date_to = ' || CE_ZBA_DEAL_GENERATION.G_statement_date_to);
518: cep_standard.debug('csh_bank_account_id = ' || CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
511: END IF;
512:
513: cep_standard.debug('Opening r_bank_cursor');
514: cep_standard.debug('G_statement_number_from = ' || CE_ZBA_DEAL_GENERATION.G_statement_number_from);
515: cep_standard.debug('G_statement_number_to = ' || CE_ZBA_DEAL_GENERATION.G_statement_number_to);
516: cep_standard.debug('G_statement_date_from = ' || CE_ZBA_DEAL_GENERATION.G_statement_date_from);
517: cep_standard.debug('G_statement_date_to = ' || CE_ZBA_DEAL_GENERATION.G_statement_date_to);
518: cep_standard.debug('csh_bank_account_id = ' || CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
519:
512:
513: cep_standard.debug('Opening r_bank_cursor');
514: cep_standard.debug('G_statement_number_from = ' || CE_ZBA_DEAL_GENERATION.G_statement_number_from);
515: cep_standard.debug('G_statement_number_to = ' || CE_ZBA_DEAL_GENERATION.G_statement_number_to);
516: cep_standard.debug('G_statement_date_from = ' || CE_ZBA_DEAL_GENERATION.G_statement_date_from);
517: cep_standard.debug('G_statement_date_to = ' || CE_ZBA_DEAL_GENERATION.G_statement_date_to);
518: cep_standard.debug('csh_bank_account_id = ' || CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
519:
520: OPEN r_bank_cursor (
513: cep_standard.debug('Opening r_bank_cursor');
514: cep_standard.debug('G_statement_number_from = ' || CE_ZBA_DEAL_GENERATION.G_statement_number_from);
515: cep_standard.debug('G_statement_number_to = ' || CE_ZBA_DEAL_GENERATION.G_statement_number_to);
516: cep_standard.debug('G_statement_date_from = ' || CE_ZBA_DEAL_GENERATION.G_statement_date_from);
517: cep_standard.debug('G_statement_date_to = ' || CE_ZBA_DEAL_GENERATION.G_statement_date_to);
518: cep_standard.debug('csh_bank_account_id = ' || CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
519:
520: OPEN r_bank_cursor (
521: CE_ZBA_DEAL_GENERATION.G_statement_number_from,
514: cep_standard.debug('G_statement_number_from = ' || CE_ZBA_DEAL_GENERATION.G_statement_number_from);
515: cep_standard.debug('G_statement_number_to = ' || CE_ZBA_DEAL_GENERATION.G_statement_number_to);
516: cep_standard.debug('G_statement_date_from = ' || CE_ZBA_DEAL_GENERATION.G_statement_date_from);
517: cep_standard.debug('G_statement_date_to = ' || CE_ZBA_DEAL_GENERATION.G_statement_date_to);
518: cep_standard.debug('csh_bank_account_id = ' || CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
519:
520: OPEN r_bank_cursor (
521: CE_ZBA_DEAL_GENERATION.G_statement_number_from,
522: CE_ZBA_DEAL_GENERATION.G_statement_number_to,
517: cep_standard.debug('G_statement_date_to = ' || CE_ZBA_DEAL_GENERATION.G_statement_date_to);
518: cep_standard.debug('csh_bank_account_id = ' || CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
519:
520: OPEN r_bank_cursor (
521: CE_ZBA_DEAL_GENERATION.G_statement_number_from,
522: CE_ZBA_DEAL_GENERATION.G_statement_number_to,
523: CE_ZBA_DEAL_GENERATION.G_statement_date_from,
524: CE_ZBA_DEAL_GENERATION.G_statement_date_to,
525: CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
518: cep_standard.debug('csh_bank_account_id = ' || CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
519:
520: OPEN r_bank_cursor (
521: CE_ZBA_DEAL_GENERATION.G_statement_number_from,
522: CE_ZBA_DEAL_GENERATION.G_statement_number_to,
523: CE_ZBA_DEAL_GENERATION.G_statement_date_from,
524: CE_ZBA_DEAL_GENERATION.G_statement_date_to,
525: CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
526: i := 0;
519:
520: OPEN r_bank_cursor (
521: CE_ZBA_DEAL_GENERATION.G_statement_number_from,
522: CE_ZBA_DEAL_GENERATION.G_statement_number_to,
523: CE_ZBA_DEAL_GENERATION.G_statement_date_from,
524: CE_ZBA_DEAL_GENERATION.G_statement_date_to,
525: CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
526: i := 0;
527: LOOP
520: OPEN r_bank_cursor (
521: CE_ZBA_DEAL_GENERATION.G_statement_number_from,
522: CE_ZBA_DEAL_GENERATION.G_statement_number_to,
523: CE_ZBA_DEAL_GENERATION.G_statement_date_from,
524: CE_ZBA_DEAL_GENERATION.G_statement_date_to,
525: CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
526: i := 0;
527: LOOP
528: cep_standard.debug('Fetching r_bank_cursor');
521: CE_ZBA_DEAL_GENERATION.G_statement_number_from,
522: CE_ZBA_DEAL_GENERATION.G_statement_number_to,
523: CE_ZBA_DEAL_GENERATION.G_statement_date_from,
524: CE_ZBA_DEAL_GENERATION.G_statement_date_to,
525: CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
526: i := 0;
527: LOOP
528: cep_standard.debug('Fetching r_bank_cursor');
529: FETCH r_bank_cursor INTO
526: i := 0;
527: LOOP
528: cep_standard.debug('Fetching r_bank_cursor');
529: FETCH r_bank_cursor INTO
530: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
531: CE_ZBA_DEAL_GENERATION.csh_statement_number,
532: CE_ZBA_DEAL_GENERATION.csh_statement_date,
533: CE_ZBA_DEAL_GENERATION.csh_check_digits,
534: CE_ZBA_DEAL_GENERATION.csh_statement_gl_date,
527: LOOP
528: cep_standard.debug('Fetching r_bank_cursor');
529: FETCH r_bank_cursor INTO
530: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
531: CE_ZBA_DEAL_GENERATION.csh_statement_number,
532: CE_ZBA_DEAL_GENERATION.csh_statement_date,
533: CE_ZBA_DEAL_GENERATION.csh_check_digits,
534: CE_ZBA_DEAL_GENERATION.csh_statement_gl_date,
535: CE_ZBA_DEAL_GENERATION.cba_bank_currency,
528: cep_standard.debug('Fetching r_bank_cursor');
529: FETCH r_bank_cursor INTO
530: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
531: CE_ZBA_DEAL_GENERATION.csh_statement_number,
532: CE_ZBA_DEAL_GENERATION.csh_statement_date,
533: CE_ZBA_DEAL_GENERATION.csh_check_digits,
534: CE_ZBA_DEAL_GENERATION.csh_statement_gl_date,
535: CE_ZBA_DEAL_GENERATION.cba_bank_currency,
536: CE_ZBA_DEAL_GENERATION.cba_multi_currency_flag,
529: FETCH r_bank_cursor INTO
530: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
531: CE_ZBA_DEAL_GENERATION.csh_statement_number,
532: CE_ZBA_DEAL_GENERATION.csh_statement_date,
533: CE_ZBA_DEAL_GENERATION.csh_check_digits,
534: CE_ZBA_DEAL_GENERATION.csh_statement_gl_date,
535: CE_ZBA_DEAL_GENERATION.cba_bank_currency,
536: CE_ZBA_DEAL_GENERATION.cba_multi_currency_flag,
537: CE_ZBA_DEAL_GENERATION.cba_check_digits,
530: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
531: CE_ZBA_DEAL_GENERATION.csh_statement_number,
532: CE_ZBA_DEAL_GENERATION.csh_statement_date,
533: CE_ZBA_DEAL_GENERATION.csh_check_digits,
534: CE_ZBA_DEAL_GENERATION.csh_statement_gl_date,
535: CE_ZBA_DEAL_GENERATION.cba_bank_currency,
536: CE_ZBA_DEAL_GENERATION.cba_multi_currency_flag,
537: CE_ZBA_DEAL_GENERATION.cba_check_digits,
538: CE_ZBA_DEAL_GENERATION.csh_rowid,
531: CE_ZBA_DEAL_GENERATION.csh_statement_number,
532: CE_ZBA_DEAL_GENERATION.csh_statement_date,
533: CE_ZBA_DEAL_GENERATION.csh_check_digits,
534: CE_ZBA_DEAL_GENERATION.csh_statement_gl_date,
535: CE_ZBA_DEAL_GENERATION.cba_bank_currency,
536: CE_ZBA_DEAL_GENERATION.cba_multi_currency_flag,
537: CE_ZBA_DEAL_GENERATION.cba_check_digits,
538: CE_ZBA_DEAL_GENERATION.csh_rowid,
539: CE_ZBA_DEAL_GENERATION.csh_statement_complete_flag,
532: CE_ZBA_DEAL_GENERATION.csh_statement_date,
533: CE_ZBA_DEAL_GENERATION.csh_check_digits,
534: CE_ZBA_DEAL_GENERATION.csh_statement_gl_date,
535: CE_ZBA_DEAL_GENERATION.cba_bank_currency,
536: CE_ZBA_DEAL_GENERATION.cba_multi_currency_flag,
537: CE_ZBA_DEAL_GENERATION.cba_check_digits,
538: CE_ZBA_DEAL_GENERATION.csh_rowid,
539: CE_ZBA_DEAL_GENERATION.csh_statement_complete_flag,
540: CE_ZBA_DEAL_GENERATION.csh_org_id;
533: CE_ZBA_DEAL_GENERATION.csh_check_digits,
534: CE_ZBA_DEAL_GENERATION.csh_statement_gl_date,
535: CE_ZBA_DEAL_GENERATION.cba_bank_currency,
536: CE_ZBA_DEAL_GENERATION.cba_multi_currency_flag,
537: CE_ZBA_DEAL_GENERATION.cba_check_digits,
538: CE_ZBA_DEAL_GENERATION.csh_rowid,
539: CE_ZBA_DEAL_GENERATION.csh_statement_complete_flag,
540: CE_ZBA_DEAL_GENERATION.csh_org_id;
541: cep_standard.debug('statement_header_id:' || CE_ZBA_DEAL_GENERATION.csh_statement_header_id );
534: CE_ZBA_DEAL_GENERATION.csh_statement_gl_date,
535: CE_ZBA_DEAL_GENERATION.cba_bank_currency,
536: CE_ZBA_DEAL_GENERATION.cba_multi_currency_flag,
537: CE_ZBA_DEAL_GENERATION.cba_check_digits,
538: CE_ZBA_DEAL_GENERATION.csh_rowid,
539: CE_ZBA_DEAL_GENERATION.csh_statement_complete_flag,
540: CE_ZBA_DEAL_GENERATION.csh_org_id;
541: cep_standard.debug('statement_header_id:' || CE_ZBA_DEAL_GENERATION.csh_statement_header_id );
542: cep_standard.debug('r_bank_cursor%ROWCOUNT = ' || r_bank_cursor%ROWCOUNT );
535: CE_ZBA_DEAL_GENERATION.cba_bank_currency,
536: CE_ZBA_DEAL_GENERATION.cba_multi_currency_flag,
537: CE_ZBA_DEAL_GENERATION.cba_check_digits,
538: CE_ZBA_DEAL_GENERATION.csh_rowid,
539: CE_ZBA_DEAL_GENERATION.csh_statement_complete_flag,
540: CE_ZBA_DEAL_GENERATION.csh_org_id;
541: cep_standard.debug('statement_header_id:' || CE_ZBA_DEAL_GENERATION.csh_statement_header_id );
542: cep_standard.debug('r_bank_cursor%ROWCOUNT = ' || r_bank_cursor%ROWCOUNT );
543: IF (r_bank_cursor%ROWCOUNT = i)
536: CE_ZBA_DEAL_GENERATION.cba_multi_currency_flag,
537: CE_ZBA_DEAL_GENERATION.cba_check_digits,
538: CE_ZBA_DEAL_GENERATION.csh_rowid,
539: CE_ZBA_DEAL_GENERATION.csh_statement_complete_flag,
540: CE_ZBA_DEAL_GENERATION.csh_org_id;
541: cep_standard.debug('statement_header_id:' || CE_ZBA_DEAL_GENERATION.csh_statement_header_id );
542: cep_standard.debug('r_bank_cursor%ROWCOUNT = ' || r_bank_cursor%ROWCOUNT );
543: IF (r_bank_cursor%ROWCOUNT = i)
544: THEN
537: CE_ZBA_DEAL_GENERATION.cba_check_digits,
538: CE_ZBA_DEAL_GENERATION.csh_rowid,
539: CE_ZBA_DEAL_GENERATION.csh_statement_complete_flag,
540: CE_ZBA_DEAL_GENERATION.csh_org_id;
541: cep_standard.debug('statement_header_id:' || CE_ZBA_DEAL_GENERATION.csh_statement_header_id );
542: cep_standard.debug('r_bank_cursor%ROWCOUNT = ' || r_bank_cursor%ROWCOUNT );
543: IF (r_bank_cursor%ROWCOUNT = i)
544: THEN
545: cep_standard.debug('Exiting Bank Loop...');
550: cep_standard.debug('Itertion Number = ' || i);
551:
552: -- Clean up error table
553: CE_ZBA_DEAL_INF_PKG.delete_row(
554: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
555: to_number(NULL));
556:
557: -- Validate existing of unreconciled sweep lines
558: select count(1)
557: -- Validate existing of unreconciled sweep lines
558: select count(1)
559: into row_count
560: from ce_statement_lines
561: where statement_header_id = CE_ZBA_DEAL_GENERATION.csh_statement_header_id
562: and trx_type in ('SWEEP_IN', 'SWEEP_OUT');
563:
564: cep_standard.debug('sweep row_count = '||row_count);
565:
565:
566: IF (row_count = 0)
567: THEN
568: CE_ZBA_DEAL_INF_PKG.insert_row(
569: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
570: to_number(NULL),
571: 'CE_NO_SWEEP_STMT_LINE');
572: -- Bug 11686554 CONTINUE is not supported in oracle 10g
573: -- CONTINUE; -- Bug 12854251 Changed EXIT to CONTINUE
577: THEN
578: SELECT count(1)
579: INTO l_count
580: FROM ce_bank_accounts ba, ce_bank_acct_uses_all bau
581: WHERE ba.bank_account_id = CE_ZBA_DEAL_GENERATION.csh_bank_account_id
582: AND bau.bank_account_id = ba.bank_account_id
583: AND ba.xtr_use_allowed_flag = 'Y'
584: AND bau.authorized_flag = 'Y';
585:
586: cep_standard.debug('authorised l_count = '||row_count);
587: IF l_count = 0
588: THEN
589: CE_ZBA_DEAL_INF_PKG.insert_row (
590: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
591: to_number(NULL),
592: 'CE_XTR_INVALID_ACCT');
593: cep_standard.debug('Exiting For Invalid Account ...');
594: EXIT;
596: END IF;
597: -- END IF; -- Bug 11686554: END IF moved to the end of the loop.
598:
599: cep_standard.debug('check the check digits');
600: IF(NVL(LTRIM(NVL(CE_ZBA_DEAL_GENERATION.csh_check_digits, 'NO DIGIT'),'0'),'0') =
601: NVL(LTRIM(NVL(CE_ZBA_DEAL_GENERATION.cba_check_digits, 'NO DIGIT'),'0'),'0'))
602: THEN
603: cep_standard.debug('csh_check_digits = '||csh_check_digits);
604: cep_standard.debug('cba_check_digits = '||cba_check_digits);
597: -- END IF; -- Bug 11686554: END IF moved to the end of the loop.
598:
599: cep_standard.debug('check the check digits');
600: IF(NVL(LTRIM(NVL(CE_ZBA_DEAL_GENERATION.csh_check_digits, 'NO DIGIT'),'0'),'0') =
601: NVL(LTRIM(NVL(CE_ZBA_DEAL_GENERATION.cba_check_digits, 'NO DIGIT'),'0'),'0'))
602: THEN
603: cep_standard.debug('csh_check_digits = '||csh_check_digits);
604: cep_standard.debug('cba_check_digits = '||cba_check_digits);
605:
612:
613: -- Read in all the lines on the statement for the selected bank
614: -- account.
615: cep_standard.debug('Opening line_cursor');
616: OPEN line_cursor (CE_ZBA_DEAL_GENERATION.csh_statement_header_id);
617: LOOP
618: FETCH line_cursor INTO
619: CE_ZBA_DEAL_GENERATION.csl_rowid,
620: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
615: cep_standard.debug('Opening line_cursor');
616: OPEN line_cursor (CE_ZBA_DEAL_GENERATION.csh_statement_header_id);
617: LOOP
618: FETCH line_cursor INTO
619: CE_ZBA_DEAL_GENERATION.csl_rowid,
620: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
621: CE_ZBA_DEAL_GENERATION.csl_receivables_trx_id,
622: CE_ZBA_DEAL_GENERATION.csl_receipt_method_id,
623: CE_ZBA_DEAL_GENERATION.csl_create_misc_trx_flag,
616: OPEN line_cursor (CE_ZBA_DEAL_GENERATION.csh_statement_header_id);
617: LOOP
618: FETCH line_cursor INTO
619: CE_ZBA_DEAL_GENERATION.csl_rowid,
620: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
621: CE_ZBA_DEAL_GENERATION.csl_receivables_trx_id,
622: CE_ZBA_DEAL_GENERATION.csl_receipt_method_id,
623: CE_ZBA_DEAL_GENERATION.csl_create_misc_trx_flag,
624: CE_ZBA_DEAL_GENERATION.csl_matching_against,
617: LOOP
618: FETCH line_cursor INTO
619: CE_ZBA_DEAL_GENERATION.csl_rowid,
620: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
621: CE_ZBA_DEAL_GENERATION.csl_receivables_trx_id,
622: CE_ZBA_DEAL_GENERATION.csl_receipt_method_id,
623: CE_ZBA_DEAL_GENERATION.csl_create_misc_trx_flag,
624: CE_ZBA_DEAL_GENERATION.csl_matching_against,
625: CE_ZBA_DEAL_GENERATION.csl_correction_method,
618: FETCH line_cursor INTO
619: CE_ZBA_DEAL_GENERATION.csl_rowid,
620: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
621: CE_ZBA_DEAL_GENERATION.csl_receivables_trx_id,
622: CE_ZBA_DEAL_GENERATION.csl_receipt_method_id,
623: CE_ZBA_DEAL_GENERATION.csl_create_misc_trx_flag,
624: CE_ZBA_DEAL_GENERATION.csl_matching_against,
625: CE_ZBA_DEAL_GENERATION.csl_correction_method,
626: CE_ZBA_DEAL_GENERATION.csl_receipt_method_name,
619: CE_ZBA_DEAL_GENERATION.csl_rowid,
620: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
621: CE_ZBA_DEAL_GENERATION.csl_receivables_trx_id,
622: CE_ZBA_DEAL_GENERATION.csl_receipt_method_id,
623: CE_ZBA_DEAL_GENERATION.csl_create_misc_trx_flag,
624: CE_ZBA_DEAL_GENERATION.csl_matching_against,
625: CE_ZBA_DEAL_GENERATION.csl_correction_method,
626: CE_ZBA_DEAL_GENERATION.csl_receipt_method_name,
627: CE_ZBA_DEAL_GENERATION.csl_exchange_rate_type,
620: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
621: CE_ZBA_DEAL_GENERATION.csl_receivables_trx_id,
622: CE_ZBA_DEAL_GENERATION.csl_receipt_method_id,
623: CE_ZBA_DEAL_GENERATION.csl_create_misc_trx_flag,
624: CE_ZBA_DEAL_GENERATION.csl_matching_against,
625: CE_ZBA_DEAL_GENERATION.csl_correction_method,
626: CE_ZBA_DEAL_GENERATION.csl_receipt_method_name,
627: CE_ZBA_DEAL_GENERATION.csl_exchange_rate_type,
628: CE_ZBA_DEAL_GENERATION.csl_exchange_rate_date,
621: CE_ZBA_DEAL_GENERATION.csl_receivables_trx_id,
622: CE_ZBA_DEAL_GENERATION.csl_receipt_method_id,
623: CE_ZBA_DEAL_GENERATION.csl_create_misc_trx_flag,
624: CE_ZBA_DEAL_GENERATION.csl_matching_against,
625: CE_ZBA_DEAL_GENERATION.csl_correction_method,
626: CE_ZBA_DEAL_GENERATION.csl_receipt_method_name,
627: CE_ZBA_DEAL_GENERATION.csl_exchange_rate_type,
628: CE_ZBA_DEAL_GENERATION.csl_exchange_rate_date,
629: CE_ZBA_DEAL_GENERATION.csl_exchange_rate,
622: CE_ZBA_DEAL_GENERATION.csl_receipt_method_id,
623: CE_ZBA_DEAL_GENERATION.csl_create_misc_trx_flag,
624: CE_ZBA_DEAL_GENERATION.csl_matching_against,
625: CE_ZBA_DEAL_GENERATION.csl_correction_method,
626: CE_ZBA_DEAL_GENERATION.csl_receipt_method_name,
627: CE_ZBA_DEAL_GENERATION.csl_exchange_rate_type,
628: CE_ZBA_DEAL_GENERATION.csl_exchange_rate_date,
629: CE_ZBA_DEAL_GENERATION.csl_exchange_rate,
630: CE_ZBA_DEAL_GENERATION.csl_currency_code,
623: CE_ZBA_DEAL_GENERATION.csl_create_misc_trx_flag,
624: CE_ZBA_DEAL_GENERATION.csl_matching_against,
625: CE_ZBA_DEAL_GENERATION.csl_correction_method,
626: CE_ZBA_DEAL_GENERATION.csl_receipt_method_name,
627: CE_ZBA_DEAL_GENERATION.csl_exchange_rate_type,
628: CE_ZBA_DEAL_GENERATION.csl_exchange_rate_date,
629: CE_ZBA_DEAL_GENERATION.csl_exchange_rate,
630: CE_ZBA_DEAL_GENERATION.csl_currency_code,
631: CE_ZBA_DEAL_GENERATION.csl_line_trx_type,
624: CE_ZBA_DEAL_GENERATION.csl_matching_against,
625: CE_ZBA_DEAL_GENERATION.csl_correction_method,
626: CE_ZBA_DEAL_GENERATION.csl_receipt_method_name,
627: CE_ZBA_DEAL_GENERATION.csl_exchange_rate_type,
628: CE_ZBA_DEAL_GENERATION.csl_exchange_rate_date,
629: CE_ZBA_DEAL_GENERATION.csl_exchange_rate,
630: CE_ZBA_DEAL_GENERATION.csl_currency_code,
631: CE_ZBA_DEAL_GENERATION.csl_line_trx_type,
632: CE_ZBA_DEAL_GENERATION.csl_reconcile_flag,
625: CE_ZBA_DEAL_GENERATION.csl_correction_method,
626: CE_ZBA_DEAL_GENERATION.csl_receipt_method_name,
627: CE_ZBA_DEAL_GENERATION.csl_exchange_rate_type,
628: CE_ZBA_DEAL_GENERATION.csl_exchange_rate_date,
629: CE_ZBA_DEAL_GENERATION.csl_exchange_rate,
630: CE_ZBA_DEAL_GENERATION.csl_currency_code,
631: CE_ZBA_DEAL_GENERATION.csl_line_trx_type,
632: CE_ZBA_DEAL_GENERATION.csl_reconcile_flag,
633: CE_ZBA_DEAL_GENERATION.csl_match_found,
626: CE_ZBA_DEAL_GENERATION.csl_receipt_method_name,
627: CE_ZBA_DEAL_GENERATION.csl_exchange_rate_type,
628: CE_ZBA_DEAL_GENERATION.csl_exchange_rate_date,
629: CE_ZBA_DEAL_GENERATION.csl_exchange_rate,
630: CE_ZBA_DEAL_GENERATION.csl_currency_code,
631: CE_ZBA_DEAL_GENERATION.csl_line_trx_type,
632: CE_ZBA_DEAL_GENERATION.csl_reconcile_flag,
633: CE_ZBA_DEAL_GENERATION.csl_match_found,
634: CE_ZBA_DEAL_GENERATION.csl_match_type,
627: CE_ZBA_DEAL_GENERATION.csl_exchange_rate_type,
628: CE_ZBA_DEAL_GENERATION.csl_exchange_rate_date,
629: CE_ZBA_DEAL_GENERATION.csl_exchange_rate,
630: CE_ZBA_DEAL_GENERATION.csl_currency_code,
631: CE_ZBA_DEAL_GENERATION.csl_line_trx_type,
632: CE_ZBA_DEAL_GENERATION.csl_reconcile_flag,
633: CE_ZBA_DEAL_GENERATION.csl_match_found,
634: CE_ZBA_DEAL_GENERATION.csl_match_type,
635: CE_ZBA_DEAL_GENERATION.csl_clearing_trx_type,
628: CE_ZBA_DEAL_GENERATION.csl_exchange_rate_date,
629: CE_ZBA_DEAL_GENERATION.csl_exchange_rate,
630: CE_ZBA_DEAL_GENERATION.csl_currency_code,
631: CE_ZBA_DEAL_GENERATION.csl_line_trx_type,
632: CE_ZBA_DEAL_GENERATION.csl_reconcile_flag,
633: CE_ZBA_DEAL_GENERATION.csl_match_found,
634: CE_ZBA_DEAL_GENERATION.csl_match_type,
635: CE_ZBA_DEAL_GENERATION.csl_clearing_trx_type,
636: CE_ZBA_DEAL_GENERATION.csl_original_amount,
629: CE_ZBA_DEAL_GENERATION.csl_exchange_rate,
630: CE_ZBA_DEAL_GENERATION.csl_currency_code,
631: CE_ZBA_DEAL_GENERATION.csl_line_trx_type,
632: CE_ZBA_DEAL_GENERATION.csl_reconcile_flag,
633: CE_ZBA_DEAL_GENERATION.csl_match_found,
634: CE_ZBA_DEAL_GENERATION.csl_match_type,
635: CE_ZBA_DEAL_GENERATION.csl_clearing_trx_type,
636: CE_ZBA_DEAL_GENERATION.csl_original_amount,
637: CE_ZBA_DEAL_GENERATION.csl_payroll_payment_format;
630: CE_ZBA_DEAL_GENERATION.csl_currency_code,
631: CE_ZBA_DEAL_GENERATION.csl_line_trx_type,
632: CE_ZBA_DEAL_GENERATION.csl_reconcile_flag,
633: CE_ZBA_DEAL_GENERATION.csl_match_found,
634: CE_ZBA_DEAL_GENERATION.csl_match_type,
635: CE_ZBA_DEAL_GENERATION.csl_clearing_trx_type,
636: CE_ZBA_DEAL_GENERATION.csl_original_amount,
637: CE_ZBA_DEAL_GENERATION.csl_payroll_payment_format;
638: EXIT WHEN line_cursor%NOTFOUND OR line_cursor%NOTFOUND IS NULL;
631: CE_ZBA_DEAL_GENERATION.csl_line_trx_type,
632: CE_ZBA_DEAL_GENERATION.csl_reconcile_flag,
633: CE_ZBA_DEAL_GENERATION.csl_match_found,
634: CE_ZBA_DEAL_GENERATION.csl_match_type,
635: CE_ZBA_DEAL_GENERATION.csl_clearing_trx_type,
636: CE_ZBA_DEAL_GENERATION.csl_original_amount,
637: CE_ZBA_DEAL_GENERATION.csl_payroll_payment_format;
638: EXIT WHEN line_cursor%NOTFOUND OR line_cursor%NOTFOUND IS NULL;
639: cep_standard.debug('csl_statement_line_id = '|| CE_ZBA_DEAL_GENERATION.csl_statement_line_id);
632: CE_ZBA_DEAL_GENERATION.csl_reconcile_flag,
633: CE_ZBA_DEAL_GENERATION.csl_match_found,
634: CE_ZBA_DEAL_GENERATION.csl_match_type,
635: CE_ZBA_DEAL_GENERATION.csl_clearing_trx_type,
636: CE_ZBA_DEAL_GENERATION.csl_original_amount,
637: CE_ZBA_DEAL_GENERATION.csl_payroll_payment_format;
638: EXIT WHEN line_cursor%NOTFOUND OR line_cursor%NOTFOUND IS NULL;
639: cep_standard.debug('csl_statement_line_id = '|| CE_ZBA_DEAL_GENERATION.csl_statement_line_id);
640:
633: CE_ZBA_DEAL_GENERATION.csl_match_found,
634: CE_ZBA_DEAL_GENERATION.csl_match_type,
635: CE_ZBA_DEAL_GENERATION.csl_clearing_trx_type,
636: CE_ZBA_DEAL_GENERATION.csl_original_amount,
637: CE_ZBA_DEAL_GENERATION.csl_payroll_payment_format;
638: EXIT WHEN line_cursor%NOTFOUND OR line_cursor%NOTFOUND IS NULL;
639: cep_standard.debug('csl_statement_line_id = '|| CE_ZBA_DEAL_GENERATION.csl_statement_line_id);
640:
641: cep_standard.debug('check if reconciled');
635: CE_ZBA_DEAL_GENERATION.csl_clearing_trx_type,
636: CE_ZBA_DEAL_GENERATION.csl_original_amount,
637: CE_ZBA_DEAL_GENERATION.csl_payroll_payment_format;
638: EXIT WHEN line_cursor%NOTFOUND OR line_cursor%NOTFOUND IS NULL;
639: cep_standard.debug('csl_statement_line_id = '|| CE_ZBA_DEAL_GENERATION.csl_statement_line_id);
640:
641: cep_standard.debug('check if reconciled');
642: select count(*)
643: into rec_status
641: cep_standard.debug('check if reconciled');
642: select count(*)
643: into rec_status
644: from ce_statement_reconcils_all
645: where statement_line_id = CE_ZBA_DEAL_GENERATION.csl_statement_line_id
646: and nvl(status_flag, 'U') = 'M'
647: and nvl(current_record_flag, 'Y') = 'Y';
648: cep_standard.debug('rec_status='||rec_status);
649:
653: --
654: -- Clear ce_zba_deal_inf table
655: --
656: CE_ZBA_DEAL_INF_PKG.delete_row(
657: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
658: CE_ZBA_DEAL_GENERATION.csl_statement_line_id);
659:
660: IF (lock_statement_line)
661: THEN
654: -- Clear ce_zba_deal_inf table
655: --
656: CE_ZBA_DEAL_INF_PKG.delete_row(
657: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
658: CE_ZBA_DEAL_GENERATION.csl_statement_line_id);
659:
660: IF (lock_statement_line)
661: THEN
662: cep_standard.debug('csl_amount = '||csl_amount);
660: IF (lock_statement_line)
661: THEN
662: cep_standard.debug('csl_amount = '||csl_amount);
663: --Validate zero amount line
664: IF (CE_ZBA_DEAL_GENERATION.csl_amount = 0)
665: THEN
666: CE_ZBA_DEAL_INF_PKG.insert_row (
667: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
668: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
663: --Validate zero amount line
664: IF (CE_ZBA_DEAL_GENERATION.csl_amount = 0)
665: THEN
666: CE_ZBA_DEAL_INF_PKG.insert_row (
667: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
668: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
669: 'CE_ZBA_ZERO_AMOUNT');
670: END IF;
671:
664: IF (CE_ZBA_DEAL_GENERATION.csl_amount = 0)
665: THEN
666: CE_ZBA_DEAL_INF_PKG.insert_row (
667: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
668: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
669: 'CE_ZBA_ZERO_AMOUNT');
670: END IF;
671:
672: --Validate different currencies between statement header and line
669: 'CE_ZBA_ZERO_AMOUNT');
670: END IF;
671:
672: --Validate different currencies between statement header and line
673: IF (CE_ZBA_DEAL_GENERATION.cba_bank_currency <>
674: nvl(CE_ZBA_DEAL_GENERATION.csl_currency_code, CE_ZBA_DEAL_GENERATION.cba_bank_currency))
675: THEN
676: CE_ZBA_DEAL_INF_PKG.insert_row (
677: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
670: END IF;
671:
672: --Validate different currencies between statement header and line
673: IF (CE_ZBA_DEAL_GENERATION.cba_bank_currency <>
674: nvl(CE_ZBA_DEAL_GENERATION.csl_currency_code, CE_ZBA_DEAL_GENERATION.cba_bank_currency))
675: THEN
676: CE_ZBA_DEAL_INF_PKG.insert_row (
677: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
678: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
673: IF (CE_ZBA_DEAL_GENERATION.cba_bank_currency <>
674: nvl(CE_ZBA_DEAL_GENERATION.csl_currency_code, CE_ZBA_DEAL_GENERATION.cba_bank_currency))
675: THEN
676: CE_ZBA_DEAL_INF_PKG.insert_row (
677: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
678: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
679: 'CE_ZBA_DIFF_CURRENCY');
680: END IF;
681: cep_standard.debug('csl_currency_code = '||csl_currency_code);
674: nvl(CE_ZBA_DEAL_GENERATION.csl_currency_code, CE_ZBA_DEAL_GENERATION.cba_bank_currency))
675: THEN
676: CE_ZBA_DEAL_INF_PKG.insert_row (
677: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
678: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
679: 'CE_ZBA_DIFF_CURRENCY');
680: END IF;
681: cep_standard.debug('csl_currency_code = '||csl_currency_code);
682: cep_standard.debug('cba_bank_currency = '||cba_bank_currency);
685: BEGIN
686: SELECT bank_account_text
687: INTO l_bank_acct_text
688: FROM ce_statement_lines
689: WHERE statement_line_id = CE_ZBA_DEAL_GENERATION.csl_statement_line_id;
690:
691: SELECT cps. account_id
692: INTO l_dst_bank_acct_id
693: FROM ce_bank_accounts cba,
709: NULL;
710: END;
711: /* Bug # 7829965 End */
712:
713: IF(CE_ZBA_DEAL_GENERATION.csh_bank_account_id <> l_dst_bank_acct_id)
714: THEN -- Bug # 7829965 -- If Source and Destination Accounts are not the same
715: --
716: -- Deal Generation
717: --
715: --
716: -- Deal Generation
717: --
718: cep_standard.debug('Check accounts and cashpool');
719: cep_standard.debug('p_header_bank_account_id=>'||CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
720: cep_standard.debug('p_offset_bank_account_num=>'||CE_ZBA_DEAL_GENERATION.csl_bank_account_text);
721: cep_standard.debug('p_trx_type=>'||CE_ZBA_DEAL_GENERATION.csl_trx_type);
722: cep_standard.debug('p_trx_date=>'||CE_ZBA_DEAL_GENERATION.csl_trx_date);
723: cep_standard.debug('p_offset_bank_account_id='||x_offset_bank_account_id);
716: -- Deal Generation
717: --
718: cep_standard.debug('Check accounts and cashpool');
719: cep_standard.debug('p_header_bank_account_id=>'||CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
720: cep_standard.debug('p_offset_bank_account_num=>'||CE_ZBA_DEAL_GENERATION.csl_bank_account_text);
721: cep_standard.debug('p_trx_type=>'||CE_ZBA_DEAL_GENERATION.csl_trx_type);
722: cep_standard.debug('p_trx_date=>'||CE_ZBA_DEAL_GENERATION.csl_trx_date);
723: cep_standard.debug('p_offset_bank_account_id='||x_offset_bank_account_id);
724: cep_standard.debug('p_cashpool_id='||x_cashpool_id);
717: --
718: cep_standard.debug('Check accounts and cashpool');
719: cep_standard.debug('p_header_bank_account_id=>'||CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
720: cep_standard.debug('p_offset_bank_account_num=>'||CE_ZBA_DEAL_GENERATION.csl_bank_account_text);
721: cep_standard.debug('p_trx_type=>'||CE_ZBA_DEAL_GENERATION.csl_trx_type);
722: cep_standard.debug('p_trx_date=>'||CE_ZBA_DEAL_GENERATION.csl_trx_date);
723: cep_standard.debug('p_offset_bank_account_id='||x_offset_bank_account_id);
724: cep_standard.debug('p_cashpool_id='||x_cashpool_id);
725: cp_match_bool := CE_LEVELING_UTILS.Match_Cashpool(
718: cep_standard.debug('Check accounts and cashpool');
719: cep_standard.debug('p_header_bank_account_id=>'||CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
720: cep_standard.debug('p_offset_bank_account_num=>'||CE_ZBA_DEAL_GENERATION.csl_bank_account_text);
721: cep_standard.debug('p_trx_type=>'||CE_ZBA_DEAL_GENERATION.csl_trx_type);
722: cep_standard.debug('p_trx_date=>'||CE_ZBA_DEAL_GENERATION.csl_trx_date);
723: cep_standard.debug('p_offset_bank_account_id='||x_offset_bank_account_id);
724: cep_standard.debug('p_cashpool_id='||x_cashpool_id);
725: cp_match_bool := CE_LEVELING_UTILS.Match_Cashpool(
726: p_header_bank_account_id => CE_ZBA_DEAL_GENERATION.csh_bank_account_id,
722: cep_standard.debug('p_trx_date=>'||CE_ZBA_DEAL_GENERATION.csl_trx_date);
723: cep_standard.debug('p_offset_bank_account_id='||x_offset_bank_account_id);
724: cep_standard.debug('p_cashpool_id='||x_cashpool_id);
725: cp_match_bool := CE_LEVELING_UTILS.Match_Cashpool(
726: p_header_bank_account_id => CE_ZBA_DEAL_GENERATION.csh_bank_account_id,
727: p_offset_bank_account_num => CE_ZBA_DEAL_GENERATION.csl_bank_account_text,
728: p_trx_type => CE_ZBA_DEAL_GENERATION.csl_trx_type,
729: p_trx_date => CE_ZBA_DEAL_GENERATION.csl_trx_date,
730: p_offset_bank_account_id => x_offset_bank_account_id,
723: cep_standard.debug('p_offset_bank_account_id='||x_offset_bank_account_id);
724: cep_standard.debug('p_cashpool_id='||x_cashpool_id);
725: cp_match_bool := CE_LEVELING_UTILS.Match_Cashpool(
726: p_header_bank_account_id => CE_ZBA_DEAL_GENERATION.csh_bank_account_id,
727: p_offset_bank_account_num => CE_ZBA_DEAL_GENERATION.csl_bank_account_text,
728: p_trx_type => CE_ZBA_DEAL_GENERATION.csl_trx_type,
729: p_trx_date => CE_ZBA_DEAL_GENERATION.csl_trx_date,
730: p_offset_bank_account_id => x_offset_bank_account_id,
731: p_cashpool_id => x_cashpool_id);
724: cep_standard.debug('p_cashpool_id='||x_cashpool_id);
725: cp_match_bool := CE_LEVELING_UTILS.Match_Cashpool(
726: p_header_bank_account_id => CE_ZBA_DEAL_GENERATION.csh_bank_account_id,
727: p_offset_bank_account_num => CE_ZBA_DEAL_GENERATION.csl_bank_account_text,
728: p_trx_type => CE_ZBA_DEAL_GENERATION.csl_trx_type,
729: p_trx_date => CE_ZBA_DEAL_GENERATION.csl_trx_date,
730: p_offset_bank_account_id => x_offset_bank_account_id,
731: p_cashpool_id => x_cashpool_id);
732:
725: cp_match_bool := CE_LEVELING_UTILS.Match_Cashpool(
726: p_header_bank_account_id => CE_ZBA_DEAL_GENERATION.csh_bank_account_id,
727: p_offset_bank_account_num => CE_ZBA_DEAL_GENERATION.csl_bank_account_text,
728: p_trx_type => CE_ZBA_DEAL_GENERATION.csl_trx_type,
729: p_trx_date => CE_ZBA_DEAL_GENERATION.csl_trx_date,
730: p_offset_bank_account_id => x_offset_bank_account_id,
731: p_cashpool_id => x_cashpool_id);
732:
733: IF (cp_match_bool)
731: p_cashpool_id => x_cashpool_id);
732:
733: IF (cp_match_bool)
734: THEN -- found matching cash pool
735: CE_ZBA_DEAL_GENERATION.p_offset_bank_account_id := x_offset_bank_account_id;
736: CE_ZBA_DEAL_GENERATION.p_cashpool_id := x_cashpool_id;
737:
738: -- Bug 14512483 Start
739: -- Validations for Cashpools with 2 Concentration accounts
732:
733: IF (cp_match_bool)
734: THEN -- found matching cash pool
735: CE_ZBA_DEAL_GENERATION.p_offset_bank_account_id := x_offset_bank_account_id;
736: CE_ZBA_DEAL_GENERATION.p_cashpool_id := x_cashpool_id;
737:
738: -- Bug 14512483 Start
739: -- Validations for Cashpools with 2 Concentration accounts
740: SELECT TYPE
740: SELECT TYPE
741: INTO L_ACCT_TYPE
742: FROM CE_CASHPOOL_SUB_ACCTS
743: WHERE CASHPOOL_ID = X_CASHPOOL_ID
744: AND ACCOUNT_ID = CE_ZBA_DEAL_GENERATION.csh_bank_account_id;
745:
746: SELECT TYPE
747: INTO L_OFFSET_ACCT_TYPE
748: FROM CE_CASHPOOL_SUB_ACCTS
751:
752: cep_standard.debug('L_ACCT_TYPE = '||L_ACCT_TYPE);
753: cep_standard.debug('L_OFFSET_ACCT_TYPE = '||L_OFFSET_ACCT_TYPE);
754: cep_standard.debug('X_CASHPOOL_ID = '||x_cashpool_id);
755: cep_standard.debug('CE_ZBA_DEAL_GENERATION.csl_trx_type = '||CE_ZBA_DEAL_GENERATION.csl_trx_type);
756:
757:
758: IF (L_ACCT_TYPE = 'FUND' AND CE_ZBA_DEAL_GENERATION.csl_trx_type = 'SWEEP_IN')
759: THEN
754: cep_standard.debug('X_CASHPOOL_ID = '||x_cashpool_id);
755: cep_standard.debug('CE_ZBA_DEAL_GENERATION.csl_trx_type = '||CE_ZBA_DEAL_GENERATION.csl_trx_type);
756:
757:
758: IF (L_ACCT_TYPE = 'FUND' AND CE_ZBA_DEAL_GENERATION.csl_trx_type = 'SWEEP_IN')
759: THEN
760: cep_standard.debug('Can not sweep in for Funding concentration account..');
761: CE_ZBA_DEAL_INF_PKG.insert_row (
762: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
758: IF (L_ACCT_TYPE = 'FUND' AND CE_ZBA_DEAL_GENERATION.csl_trx_type = 'SWEEP_IN')
759: THEN
760: cep_standard.debug('Can not sweep in for Funding concentration account..');
761: CE_ZBA_DEAL_INF_PKG.insert_row (
762: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
763: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
764: 'CE_ZBA_NO_FUND_SWEEP_IN');
765:
766: ELSIF (L_ACCT_TYPE = 'INV' AND CE_ZBA_DEAL_GENERATION.csl_trx_type = 'SWEEP_OUT')
759: THEN
760: cep_standard.debug('Can not sweep in for Funding concentration account..');
761: CE_ZBA_DEAL_INF_PKG.insert_row (
762: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
763: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
764: 'CE_ZBA_NO_FUND_SWEEP_IN');
765:
766: ELSIF (L_ACCT_TYPE = 'INV' AND CE_ZBA_DEAL_GENERATION.csl_trx_type = 'SWEEP_OUT')
767: THEN
762: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
763: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
764: 'CE_ZBA_NO_FUND_SWEEP_IN');
765:
766: ELSIF (L_ACCT_TYPE = 'INV' AND CE_ZBA_DEAL_GENERATION.csl_trx_type = 'SWEEP_OUT')
767: THEN
768: cep_standard.debug('Can not sweep out for Investment concentration account..');
769: CE_ZBA_DEAL_INF_PKG.insert_row (
770: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
766: ELSIF (L_ACCT_TYPE = 'INV' AND CE_ZBA_DEAL_GENERATION.csl_trx_type = 'SWEEP_OUT')
767: THEN
768: cep_standard.debug('Can not sweep out for Investment concentration account..');
769: CE_ZBA_DEAL_INF_PKG.insert_row (
770: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
771: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
772: 'CE_ZBA_NO_INV_SWEEP_OUT');
773:
774: ELSIF (L_OFFSET_ACCT_TYPE = 'FUND' AND CE_ZBA_DEAL_GENERATION.csl_trx_type = 'SWEEP_OUT' )
767: THEN
768: cep_standard.debug('Can not sweep out for Investment concentration account..');
769: CE_ZBA_DEAL_INF_PKG.insert_row (
770: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
771: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
772: 'CE_ZBA_NO_INV_SWEEP_OUT');
773:
774: ELSIF (L_OFFSET_ACCT_TYPE = 'FUND' AND CE_ZBA_DEAL_GENERATION.csl_trx_type = 'SWEEP_OUT' )
775: THEN
770: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
771: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
772: 'CE_ZBA_NO_INV_SWEEP_OUT');
773:
774: ELSIF (L_OFFSET_ACCT_TYPE = 'FUND' AND CE_ZBA_DEAL_GENERATION.csl_trx_type = 'SWEEP_OUT' )
775: THEN
776: cep_standard.debug('Can not sweep in for Funding concentration account..');
777: CE_ZBA_DEAL_INF_PKG.insert_row (
778: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
774: ELSIF (L_OFFSET_ACCT_TYPE = 'FUND' AND CE_ZBA_DEAL_GENERATION.csl_trx_type = 'SWEEP_OUT' )
775: THEN
776: cep_standard.debug('Can not sweep in for Funding concentration account..');
777: CE_ZBA_DEAL_INF_PKG.insert_row (
778: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
779: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
780: 'CE_ZBA_NO_FUND_SWEEP_IN_SUB');
781:
782: ELSIF (L_OFFSET_ACCT_TYPE = 'INV' AND CE_ZBA_DEAL_GENERATION.csl_trx_type = 'SWEEP_IN')
775: THEN
776: cep_standard.debug('Can not sweep in for Funding concentration account..');
777: CE_ZBA_DEAL_INF_PKG.insert_row (
778: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
779: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
780: 'CE_ZBA_NO_FUND_SWEEP_IN_SUB');
781:
782: ELSIF (L_OFFSET_ACCT_TYPE = 'INV' AND CE_ZBA_DEAL_GENERATION.csl_trx_type = 'SWEEP_IN')
783: THEN
778: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
779: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
780: 'CE_ZBA_NO_FUND_SWEEP_IN_SUB');
781:
782: ELSIF (L_OFFSET_ACCT_TYPE = 'INV' AND CE_ZBA_DEAL_GENERATION.csl_trx_type = 'SWEEP_IN')
783: THEN
784: cep_standard.debug('Can not sweep out for Investment concentration account..');
785: CE_ZBA_DEAL_INF_PKG.insert_row (
786: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
782: ELSIF (L_OFFSET_ACCT_TYPE = 'INV' AND CE_ZBA_DEAL_GENERATION.csl_trx_type = 'SWEEP_IN')
783: THEN
784: cep_standard.debug('Can not sweep out for Investment concentration account..');
785: CE_ZBA_DEAL_INF_PKG.insert_row (
786: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
787: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
788: 'CE_ZBA_NO_INV_SWEEP_OUT_SUB');
789:
790: -- Bug 14512483 End
783: THEN
784: cep_standard.debug('Can not sweep out for Investment concentration account..');
785: CE_ZBA_DEAL_INF_PKG.insert_row (
786: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
787: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
788: 'CE_ZBA_NO_INV_SWEEP_OUT_SUB');
789:
790: -- Bug 14512483 End
791: ELSE
794: -- p_from_bank_account_id will always be the statement line's
795: -- bank account and p_to_bank_Account_id will always be the
796: -- offset bank account
797:
798: CE_ZBA_DEAL_GENERATION.p_from_bank_account_id :=
799: CE_ZBA_DEAL_GENERATION.csh_bank_account_id;
800: CE_ZBA_DEAL_GENERATION.p_to_bank_account_id :=
801: CE_ZBA_DEAL_GENERATION.p_offset_bank_account_id;
802:
795: -- bank account and p_to_bank_Account_id will always be the
796: -- offset bank account
797:
798: CE_ZBA_DEAL_GENERATION.p_from_bank_account_id :=
799: CE_ZBA_DEAL_GENERATION.csh_bank_account_id;
800: CE_ZBA_DEAL_GENERATION.p_to_bank_account_id :=
801: CE_ZBA_DEAL_GENERATION.p_offset_bank_account_id;
802:
803: CE_LEVELING_UTILS.Generate_Fund_Transfer(
796: -- offset bank account
797:
798: CE_ZBA_DEAL_GENERATION.p_from_bank_account_id :=
799: CE_ZBA_DEAL_GENERATION.csh_bank_account_id;
800: CE_ZBA_DEAL_GENERATION.p_to_bank_account_id :=
801: CE_ZBA_DEAL_GENERATION.p_offset_bank_account_id;
802:
803: CE_LEVELING_UTILS.Generate_Fund_Transfer(
804: X_from_bank_account_id => CE_ZBA_DEAL_GENERATION.p_from_bank_account_id,
797:
798: CE_ZBA_DEAL_GENERATION.p_from_bank_account_id :=
799: CE_ZBA_DEAL_GENERATION.csh_bank_account_id;
800: CE_ZBA_DEAL_GENERATION.p_to_bank_account_id :=
801: CE_ZBA_DEAL_GENERATION.p_offset_bank_account_id;
802:
803: CE_LEVELING_UTILS.Generate_Fund_Transfer(
804: X_from_bank_account_id => CE_ZBA_DEAL_GENERATION.p_from_bank_account_id,
805: X_to_bank_account_id => CE_ZBA_DEAL_GENERATION.p_to_bank_account_id,
800: CE_ZBA_DEAL_GENERATION.p_to_bank_account_id :=
801: CE_ZBA_DEAL_GENERATION.p_offset_bank_account_id;
802:
803: CE_LEVELING_UTILS.Generate_Fund_Transfer(
804: X_from_bank_account_id => CE_ZBA_DEAL_GENERATION.p_from_bank_account_id,
805: X_to_bank_account_id => CE_ZBA_DEAL_GENERATION.p_to_bank_account_id,
806: X_cashpool_id => CE_ZBA_DEAL_GENERATION.p_cashpool_id,
807: X_amount => CE_ZBA_DEAL_GENERATION.csl_amount,
808: X_transfer_date => CE_ZBA_DEAL_GENERATION.csl_trx_date,
801: CE_ZBA_DEAL_GENERATION.p_offset_bank_account_id;
802:
803: CE_LEVELING_UTILS.Generate_Fund_Transfer(
804: X_from_bank_account_id => CE_ZBA_DEAL_GENERATION.p_from_bank_account_id,
805: X_to_bank_account_id => CE_ZBA_DEAL_GENERATION.p_to_bank_account_id,
806: X_cashpool_id => CE_ZBA_DEAL_GENERATION.p_cashpool_id,
807: X_amount => CE_ZBA_DEAL_GENERATION.csl_amount,
808: X_transfer_date => CE_ZBA_DEAL_GENERATION.csl_trx_date,
809: X_settlement_authorized => 'Y',
802:
803: CE_LEVELING_UTILS.Generate_Fund_Transfer(
804: X_from_bank_account_id => CE_ZBA_DEAL_GENERATION.p_from_bank_account_id,
805: X_to_bank_account_id => CE_ZBA_DEAL_GENERATION.p_to_bank_account_id,
806: X_cashpool_id => CE_ZBA_DEAL_GENERATION.p_cashpool_id,
807: X_amount => CE_ZBA_DEAL_GENERATION.csl_amount,
808: X_transfer_date => CE_ZBA_DEAL_GENERATION.csl_trx_date,
809: X_settlement_authorized => 'Y',
810: X_accept_limit_error => 'Y',
803: CE_LEVELING_UTILS.Generate_Fund_Transfer(
804: X_from_bank_account_id => CE_ZBA_DEAL_GENERATION.p_from_bank_account_id,
805: X_to_bank_account_id => CE_ZBA_DEAL_GENERATION.p_to_bank_account_id,
806: X_cashpool_id => CE_ZBA_DEAL_GENERATION.p_cashpool_id,
807: X_amount => CE_ZBA_DEAL_GENERATION.csl_amount,
808: X_transfer_date => CE_ZBA_DEAL_GENERATION.csl_trx_date,
809: X_settlement_authorized => 'Y',
810: X_accept_limit_error => 'Y',
811: X_request_id => null,
804: X_from_bank_account_id => CE_ZBA_DEAL_GENERATION.p_from_bank_account_id,
805: X_to_bank_account_id => CE_ZBA_DEAL_GENERATION.p_to_bank_account_id,
806: X_cashpool_id => CE_ZBA_DEAL_GENERATION.p_cashpool_id,
807: X_amount => CE_ZBA_DEAL_GENERATION.csl_amount,
808: X_transfer_date => CE_ZBA_DEAL_GENERATION.csl_trx_date,
809: X_settlement_authorized => 'Y',
810: X_accept_limit_error => 'Y',
811: X_request_id => null,
812: X_deal_type => l_deal_type,
814: X_trx_number => l_transaction_num,
815: X_offset_deal_no => l_offset_deal_num,
816: X_offset_trx_number => l_offset_transaction_num,
817: X_success_flag => l_success_flag,
818: X_statement_line_id => CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
819: X_msg_count => l_msg_count,
820: X_cashflows_created_flag => l_cashflows_created_flag,
821: X_called_by_flag => 'Z');
822:
836: offset_transaction_num,
837: deal_status_flag)
838: VALUES (
839: 'CE',
840: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
841: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
842: sysdate,
843: NVL(FND_GLOBAL.user_id,-1),
844: l_deal_type,
837: deal_status_flag)
838: VALUES (
839: 'CE',
840: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
841: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
842: sysdate,
843: NVL(FND_GLOBAL.user_id,-1),
844: l_deal_type,
845: l_deal_num,
843: NVL(FND_GLOBAL.user_id,-1),
844: l_deal_type,
845: l_deal_num,
846: l_transaction_num,
847: CE_ZBA_DEAL_GENERATION.p_cashpool_id,
848: l_cashflows_created_flag,
849: l_offset_deal_num,
850: l_offset_transaction_num,
851: 'Y');
864: deal_status_flag,
865: cashpool_id)
866: VALUES (
867: 'CE',
868: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
869: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
870: FND_MSG_PUB.get(1, FND_API.G_FALSE),
871: sysdate,
872: NVL(FND_GLOBAL.user_id,-1),
865: cashpool_id)
866: VALUES (
867: 'CE',
868: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
869: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
870: FND_MSG_PUB.get(1, FND_API.G_FALSE),
871: sysdate,
872: NVL(FND_GLOBAL.user_id,-1),
873: 'N',
870: FND_MSG_PUB.get(1, FND_API.G_FALSE),
871: sysdate,
872: NVL(FND_GLOBAL.user_id,-1),
873: 'N',
874: CE_ZBA_DEAL_GENERATION.p_cashpool_id);
875: FND_MSG_PUB.delete_msg(1);
876: END LOOP;
877: END IF;
878: END IF;
879: END IF; -- Bug 14512483 Validation for Cashpool with 2 Concntration Accounts.
880: ELSE -- cp_match_bool = false
881: cep_standard.debug('No matching cashpool found');
882: CE_ZBA_DEAL_INF_PKG.insert_row(
883: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
884: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
885: 'CE_NO_CASHPOOL_MATCH');
886: END IF;
887: /* Bug # 7829965 Start */
880: ELSE -- cp_match_bool = false
881: cep_standard.debug('No matching cashpool found');
882: CE_ZBA_DEAL_INF_PKG.insert_row(
883: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
884: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
885: 'CE_NO_CASHPOOL_MATCH');
886: END IF;
887: /* Bug # 7829965 Start */
888: ELSE -- Source and Destination Accounts are the same
887: /* Bug # 7829965 Start */
888: ELSE -- Source and Destination Accounts are the same
889: cep_standard.debug('Source and Destination Accounts are the same');
890: CE_ZBA_DEAL_INF_PKG.insert_row(
891: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
892: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
893: 'CE_BAT_INVALID_BACCNTS');
894: END IF;
895: /* Bug # 7829965 End */
888: ELSE -- Source and Destination Accounts are the same
889: cep_standard.debug('Source and Destination Accounts are the same');
890: CE_ZBA_DEAL_INF_PKG.insert_row(
891: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
892: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
893: 'CE_BAT_INVALID_BACCNTS');
894: END IF;
895: /* Bug # 7829965 End */
896: ELSE -- statement line is locked
895: /* Bug # 7829965 End */
896: ELSE -- statement line is locked
897: cep_standard.debug('statement line is locked');
898: CE_ZBA_DEAL_INF_PKG.insert_row(
899: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
900: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
901: 'CE_LINE_LOCKED');
902: END IF;
903:
896: ELSE -- statement line is locked
897: cep_standard.debug('statement line is locked');
898: CE_ZBA_DEAL_INF_PKG.insert_row(
899: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
900: CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
901: 'CE_LINE_LOCKED');
902: END IF;
903:
904: IF (statement_line_count = CE_AUTO_BANK_REC.G_lines_per_commit)
914: ELSE -- statement complete flag is Y
915:
916: error_statement_line_id := get_min_statement_line_id;
917: CE_ZBA_DEAL_INF_PKG.delete_row(
918: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
919: error_statement_line_id);
920: CE_ZBA_DEAL_INF_PKG.insert_row(
921: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
922: error_statement_line_id,
917: CE_ZBA_DEAL_INF_PKG.delete_row(
918: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
919: error_statement_line_id);
920: CE_ZBA_DEAL_INF_PKG.insert_row(
921: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
922: error_statement_line_id,
923: 'CE_STATEMENT_COMPLETED');
924: END IF; -- statement complete flag check
925:
923: 'CE_STATEMENT_COMPLETED');
924: END IF; -- statement complete flag check
925:
926: ELSE -- statement is locked
927: CE_ZBA_DEAL_INF_PKG.delete_row(CE_ZBA_DEAL_GENERATION.csh_statement_header_id, to_number(NULL));
928: CE_ZBA_DEAL_INF_PKG.insert_row(
929: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
930: to_number(NULL),
931: 'CE_LOCK_STATEMENT_HEADER_ERR');
925:
926: ELSE -- statement is locked
927: CE_ZBA_DEAL_INF_PKG.delete_row(CE_ZBA_DEAL_GENERATION.csh_statement_header_id, to_number(NULL));
928: CE_ZBA_DEAL_INF_PKG.insert_row(
929: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
930: to_number(NULL),
931: 'CE_LOCK_STATEMENT_HEADER_ERR');
932: END IF; -- statement lock check
933: lock_status := dbms_lock.release(lockhandle);
933: lock_status := dbms_lock.release(lockhandle);
934: ELSE -- check digits failed
935: cep_standard.debug('Account/Statement check digit mismatch');
936: CE_ZBA_DEAL_INF_PKG.delete_row(
937: CE_ZBA_DEAL_GENERATION.csh_statement_header_id, to_number(NULL));
938: CE_ZBA_DEAL_INF_PKG.insert_row(
939: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
940: to_number(NULL),
941: 'CE_CHECK_DIGITS');
935: cep_standard.debug('Account/Statement check digit mismatch');
936: CE_ZBA_DEAL_INF_PKG.delete_row(
937: CE_ZBA_DEAL_GENERATION.csh_statement_header_id, to_number(NULL));
938: CE_ZBA_DEAL_INF_PKG.insert_row(
939: CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
940: to_number(NULL),
941: 'CE_CHECK_DIGITS');
942: END IF; -- check_digits
943: END IF; -- Bug 11686554 Introduced end if to continue loop for next statement header.
1001: ELSE
1002: cep_standard.debug('zba_generation: ' || 'EXECUTION REPORT SUBMITTED');
1003: END IF;
1004:
1005: cep_standard.debug('<
1007: WHEN OTHERS THEN
1008: cep_standard.debug(' EXCEPTION: CE_ZBA_DEAL_GENERATION.zba_generation - OTHERS');
1009: IF r_branch_cursor%ISOPEN THEN
1004:
1005: cep_standard.debug('<
1007: WHEN OTHERS THEN
1008: cep_standard.debug(' EXCEPTION: CE_ZBA_DEAL_GENERATION.zba_generation - OTHERS');
1009: IF r_branch_cursor%ISOPEN THEN
1010: CLOSE r_branch_cursor;
1011: END IF;
1012: IF r_bank_cursor%ISOPEN THEN
1020: cep_standard.debug('DEBUG: sqlerrm:' || sqlerrm);
1021: RAISE;
1022: END zba_generation;
1023:
1024: END CE_ZBA_DEAL_GENERATION;