[Home] [Help]
144: where payment_schedule_id in
145: ( select /*+ unnest */ r.payment_schedule_id
146: from ar_receipts_gt r,
147: ar_receivable_applications ra,
148: ra_customer_trx ct,
149: ar_receipt_methods rm,
150: ar_receipt_classes rc
151: where r.gt_id = p_gt_id
152: and ra.applied_customer_trx_id = r.customer_trx_id
158: and rc.confirm_flag = 'N'
159: UNION ALL
160: select r.payment_schedule_id
161: from ar_receipts_gt r,
162: ra_customer_trx ct
163: where r.gt_id = p_gt_id
164: and ct.customer_trx_id = r.customer_trx_id
165: and ct.cc_error_flag = 'Y'
166: );
1564: p_due_date_l IN ar_payment_schedules.due_date%TYPE,
1565: p_due_date_h IN ar_payment_schedules.due_date%TYPE,
1566: p_trx_num_l IN ar_payment_schedules.trx_number%TYPE,
1567: p_trx_num_h IN ar_payment_schedules.trx_number%TYPE,
1568: p_doc_num_l IN ra_customer_trx.doc_sequence_value%TYPE,
1569: p_doc_num_h IN ra_customer_trx.doc_sequence_value%TYPE,
1570: p_customer_number_l IN hz_cust_accounts.account_number%TYPE, --Bug6734688
1571: p_customer_number_h IN hz_cust_accounts.account_number%TYPE, --Bug6734688
1572: p_customer_name_l IN hz_parties.party_name%TYPE, --Bug6734688
1565: p_due_date_h IN ar_payment_schedules.due_date%TYPE,
1566: p_trx_num_l IN ar_payment_schedules.trx_number%TYPE,
1567: p_trx_num_h IN ar_payment_schedules.trx_number%TYPE,
1568: p_doc_num_l IN ra_customer_trx.doc_sequence_value%TYPE,
1569: p_doc_num_h IN ra_customer_trx.doc_sequence_value%TYPE,
1570: p_customer_number_l IN hz_cust_accounts.account_number%TYPE, --Bug6734688
1571: p_customer_number_h IN hz_cust_accounts.account_number%TYPE, --Bug6734688
1572: p_customer_name_l IN hz_parties.party_name%TYPE, --Bug6734688
1573: p_customer_name_h IN hz_parties.party_name%TYPE, --Bug6734688
1685: FROM hz_customer_profiles cust_cp,
1686: hz_customer_profiles site_cp,
1687: hz_cust_profile_amts cust_cpa,
1688: hz_cust_profile_amts site_cpa,
1689: ra_customer_trx ct,
1690: IBY_FNDCPT_TX_EXTENSIONS X,
1691: IBY_PMT_INSTR_USES_ALL U,
1692: IBY_FNDCPT_PMT_CHNNLS_B P,
1693: ar_payment_schedules ps,
2326:
2327:
2328: IF p_pay_process_fail = 'Y' THEN
2329:
2330: UPDATE /*+ index(ct ra_customer_trx_u1) */ ra_customer_trx_all ct
2331: SET cc_error_flag = null,
2332: cc_error_code = null,
2333: cc_error_text = null
2334: WHERE customer_trx_id in
2415: r.receipt_number rec_num,
2416: r.cash_receipt_id cash_receipt_id,
2417: ps.org_id org_id
2418: from ar_payment_schedules ps,
2419: ra_customer_trx trx,
2420: ar_receipts_gt r
2421: where trx.customer_trx_id = ps.customer_trx_id
2422: and trx.cc_error_flag = 'Y'
2423: and r.payment_schedule_id = ps.payment_schedule_id
2584: status = 'OP'
2585: where payment_schedule_id in
2586: ( select ps.payment_schedule_id
2587: from ar_payment_schedules ps,
2588: ra_customer_trx trx,
2589: ar_receipts_gt r
2590: where r.gt_id = p_gt_id
2591: AND r.payment_schedule_id = ps.payment_schedule_id
2592: AND trx.customer_trx_id = ps.customer_trx_id
2770: WHERE cash_receipt_id IN (
2771: SELECT crh.cash_receipt_id
2772: FROM ar_cash_receipt_history crh,
2773: ar_receivable_applications ra,
2774: ra_customer_trx ct,
2775: iby_fndcpt_tx_extensions ext
2776: WHERE crh.batch_id = p_batch_id
2777: AND crh.current_record_flag = 'Y'
2778: AND crh.status = 'CONFIRMED'
2855: p_customer_id IN NUMBER,
2856: p_installment IN NUMBER,
2857: p_cust_site_use_id IN NUMBER,
2858: p_receipt_method_id IN ar_cash_receipts.receipt_method_id%TYPE,
2859: p_pmt_trxn_ext_id IN RA_CUSTOMER_TRX.PAYMENT_TRXN_EXTENSION_ID%TYPE,
2860: p_called_from IN VARCHAR2,
2861: p_request_id IN NUMBER,
2862: p_batch_id IN NUMBER,
2863: p_exchange_rate IN ar_batches.exchange_rate%TYPE,
3067: END receipt_application;
3068:
3069:
3070: PROCEDURE process_payments( p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
3071: p_pmt_trxn_id IN RA_CUSTOMER_TRX.PAYMENT_TRXN_EXTENSION_ID%TYPE,
3072: p_request_id IN NUMBER,
3073: p_batch_id IN NUMBER,
3074: p_called_from IN VARCHAR2,
3075: p_cc_error_code OUT NOCOPY VARCHAR2,
3078: l_msg_count NUMBER;
3079: l_msg_data VARCHAR2(240);
3080: l_count NUMBER;
3081: l_response_error_code VARCHAR2(20);
3082: l_pmt_trxn_id RA_CUSTOMER_TRX.PAYMENT_TRXN_EXTENSION_ID%TYPE;
3083:
3084: /* Bug 7639165 - Declaration Begin. */
3085: l_first_msg VARCHAR2(240);
3086: /* Bug 7639165 - Declaration End. */
3336: -1 gt_id
3337: from ar_cash_receipt_history crh,
3338: ar_cash_receipts cr,
3339: ar_payment_schedules ps,
3340: ra_customer_trx ct,
3341: iby_fndcpt_tx_xe_copies cp
3342: where crh.batch_id = :p_batch_id
3343: and cr.status = ''UNAPP''
3344: and ps.customer_trx_id = ct.customer_trx_id
3397: -1 gt_id
3398: from ar_cash_receipt_history crh,
3399: ar_cash_receipts cr,
3400: ar_payment_schedules ps,
3401: ra_customer_trx ct
3402: where crh.batch_id = :p_batch_id
3403: and cr.status = ''UNAPP''
3404: and ps.customer_trx_id = ct.customer_trx_id
3405: and ps.selected_for_receipt_batch_id = crh.batch_id
3451: -1 gt_id
3452: from ar_cash_receipt_history crh,
3453: ar_cash_receipts cr,
3454: ar_payment_schedules ps,
3455: ra_customer_trx ct
3456: where crh.batch_id = :p_batch_id
3457: and cr.status = ''UNAPP''
3458: and ps.customer_trx_id = ct.customer_trx_id
3459: and ps.selected_for_receipt_batch_id = crh.batch_id
3526: OVER( PARTITION BY ct.bill_to_site_use_id,ps.due_date) group_amount,
3527: ct.bill_to_site_use_id,
3528: ps.customer_trx_id
3529: FROM ar_payment_schedules ps,
3530: ra_customer_trx ct
3531: WHERE ps.customer_trx_id = ct.customer_trx_id
3532: AND ps.selected_for_receipt_batch_id = :batch_id
3533: ) ps
3534: where crh.batch_id = ps.selected_for_receipt_batch_id
3607: SUM( ps.amount_due_remaining )
3608: OVER( PARTITION BY ct.bill_to_customer_id,ps.due_date) group_amount,
3609: ps.customer_trx_id
3610: FROM ar_payment_schedules ps,
3611: ra_customer_trx ct
3612: WHERE ps.customer_trx_id = ct.customer_trx_id
3613: AND ps.selected_for_receipt_batch_id = :batch_id
3614: ) ps
3615: where crh.batch_id = ps.selected_for_receipt_batch_id
3790: p_receipt_num_array OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE,
3791: p_count IN NUMBER) IS
3792: l_error_flag VARCHAR2(1) := 'N';
3793: l_status NUMBER;
3794: l_doc_sequence_value ra_customer_trx.doc_sequence_value%TYPE;
3795: l_doc_sequence_id ra_customer_trx.doc_sequence_id%TYPE;
3796: BEGIN
3797: IF PG_DEBUG in ('Y', 'C') THEN
3798: arp_debug.debug('populate_receipt_numbers()+');
3791: p_count IN NUMBER) IS
3792: l_error_flag VARCHAR2(1) := 'N';
3793: l_status NUMBER;
3794: l_doc_sequence_value ra_customer_trx.doc_sequence_value%TYPE;
3795: l_doc_sequence_id ra_customer_trx.doc_sequence_id%TYPE;
3796: BEGIN
3797: IF PG_DEBUG in ('Y', 'C') THEN
3798: arp_debug.debug('populate_receipt_numbers()+');
3799: arp_debug.debug('p_rcpt_method_name :'||p_rcpt_method_name);
3959: SELECT ard.source_id, ard.source_id_secondary, trx.upgrade_method,
3960: ra.applied_customer_trx_id, ra.payment_schedule_id, ra.applied_payment_schedule_id
3961: FROM ar_receivable_applications ra,
3962: ar_distributions ard,
3963: ra_customer_trx trx,
3964: ar_receipts_gt rgt
3965: WHERE ra.cash_receipt_id = p_cr_id
3966: AND ra.applied_customer_trx_id IS NOT NULL
3967: AND ard.source_id_secondary = ra.receivable_application_id
3993: uned.code_combination_id uned_disc_ccid,
3994: crh.batch_id,
3995: rgt.customer_trx_id,
3996: (select 'Y' rev_rec_flag
3997: from ra_customer_trx_lines ctl
3998: where ctl.customer_trx_id = rgt.customer_trx_id
3999: and ctl.autorule_complete_flag||'' = 'N'
4000: and rownum = 1 ) rev_rec_flag,
4001: (select 'Y' def_tax_flag
4682: WHERE payment_schedule_id = rec.payment_schedule_id;
4683:
4684: ELSIF l_err_code_array(k) = 'PROCESS_PAYMENT_FAILED'
4685: AND rec.cash_receipt_id is not null THEN
4686: /* update the error flag in ra_customer_trx */
4687: UPDATE ra_customer_trx
4688: SET cc_error_flag = 'Y',
4689: cc_error_code = l_cc_err_code_array(k),
4690: cc_error_text = l_cc_err_text_array(k),
4683:
4684: ELSIF l_err_code_array(k) = 'PROCESS_PAYMENT_FAILED'
4685: AND rec.cash_receipt_id is not null THEN
4686: /* update the error flag in ra_customer_trx */
4687: UPDATE ra_customer_trx
4688: SET cc_error_flag = 'Y',
4689: cc_error_code = l_cc_err_code_array(k),
4690: cc_error_text = l_cc_err_text_array(k),
4691: last_updated_by = pg_last_updated_by,
4732:
4733: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4734: l_pay_process_fail := 'Y';
4735:
4736: UPDATE ra_customer_trx
4737: SET cc_error_flag = 'Y',
4738: cc_error_code = l_cc_error_code,
4739: cc_error_text = l_cc_error_text,
4740: last_updated_by = pg_last_updated_by,
4946:
4947: arp_standard.debug('Reset Invoice Line Level Balances If maintained on the invoice');
4948: IF unbal_rec_appln.upgrade_method IN ('R12', 'R12_11IMFAR') THEN
4949:
4950: UPDATE RA_CUSTOMER_TRX_LINES TL SET (
4951: AMOUNT_DUE_REMAINING,
4952: ACCTD_AMOUNT_DUE_REMAINING,
4953: CHRG_AMOUNT_REMAINING,
4954: CHRG_ACCTD_AMOUNT_REMAINING,
5019: ard.ref_account_class,
5020: ard.activity_bucket,
5021: ctl.line_type
5022: FROM ar_distributions ard,
5023: ra_customer_trx_lines ctl
5024: WHERE ctl.customer_trx_id =
5025: unbal_rec_appln.applied_customer_trx_id
5026: AND ctl.customer_trx_line_id = ard.ref_customer_trx_line_id (+)
5027: GROUP BY ctl.customer_trx_line_id,
5177: fnd_file.put_line(FND_FILE.LOG,'control_check()+');
5178: fnd_file.put_line(FND_FILE.LOG,'Detect receipts with Auth failure');
5179: END IF;
5180:
5181: UPDATE /*+ index(ct ra_customer_trx_u1) */ ra_customer_trx_all ct
5182: SET cc_error_flag = 'Y',
5183: last_updated_by = pg_last_updated_by,
5184: last_update_date = sysdate,
5185: last_update_login = pg_last_update_login,