[Home] [Help]
715: cr.receipt_date as_of_date,
716: sum(nvl(ra.amount_applied_from,ra.amount_applied)) receipt_amount
717: from ar_receivable_applications ra,
718: ar_payment_schedules rps,
719: ar_cash_receipts cr
720: where ra.request_id = p_req_id
721: and ra.status = 'UNAPP'
722: and sign(ra.amount_applied) = 1
723: and rps.payment_schedule_id = ra.payment_schedule_id
750: nvl(cr.customer_site_use_id,-99) customer_site_use_id,
751: cr.currency_code currency,
752: cr.org_id,
753: max(cr.receipt_date) last_cash_receipt_date
754: from ar_cash_receipts cr,
755: ar_receivable_applications ra
756: where ra.request_id = p_req_id
757: and ra.status = 'UNAPP'
758: and sign(ra.amount_applied) = 1
760: group by cr.pay_from_customer,
761: nvl(cr.customer_site_use_id,-99),
762: cr.currency_code,
763: cr.org_id) a,
764: ar_cash_receipts b
765: where a.last_cash_receipt_date = b.receipt_date
766: and a.customer_id = b.pay_from_customer
767: and a.customer_site_use_id = nvl(b.customer_site_use_id,-99)
768: and a.org_id = b.org_id
769: and a.currency = b.currency_code
770: group by a.customer_id,
771: a.customer_site_use_id,
772: a.currency) c,
773: ar_cash_receipts cr1
774: WHERE cr1.cash_receipt_id = c.cash_receipt_id;
775:
776: cursor application_info(p_req_id IN NUMBER) IS
777: select ps.customer_id trx_customer_id,
3888: 'ACC', nvl(ra.amount_applied_from,ra.amount_applied),
3889: 'OTHER ACC',nvl(ra.amount_applied_from,ra.amount_applied),
3890: null)) unresolved_cash
3891: FROM ar_payment_schedules ps,
3892: ar_cash_receipts cr,
3893: ar_cash_receipt_history crh,
3894: ar_receivable_applications ra
3895: WHERE ps.payment_schedule_id = p_ps_id
3896: and ps.cash_receipt_id = cr.cash_receipt_id
3923: CURSOR get_last_pmt (p_customer_id IN NUMBER,
3924: p_site_use_id IN NUMBER,
3925: p_currency IN VARCHAR2) IS
3926: select receipt_number, amount, receipt_date
3927: from ar_cash_receipts
3928: where cash_receipt_id =
3929: (select max(cr.cash_receipt_id)
3930: from ar_cash_receipts cr,
3931: ar_cash_receipt_history crh --apandit
3926: select receipt_number, amount, receipt_date
3927: from ar_cash_receipts
3928: where cash_receipt_id =
3929: (select max(cr.cash_receipt_id)
3930: from ar_cash_receipts cr,
3931: ar_cash_receipt_history crh --apandit
3932: where cr.pay_from_customer = p_customer_id
3933: and cr.cash_receipt_id = crh.cash_receipt_id --apandit
3934: and crh.current_record_flag = 'Y'
4818: CURSOR get_last_pmt (p_customer_id IN NUMBER,
4819: p_site_use_id IN NUMBER,
4820: p_currency IN VARCHAR2) IS
4821: SELECT receipt_number, amount, receipt_date
4822: FROM ar_cash_receipts
4823: WHERE cash_receipt_id =
4824: (SELECT MAX(cr.cash_receipt_id)
4825: FROM ar_cash_receipts cr,
4826: ar_cash_receipt_history crh
4821: SELECT receipt_number, amount, receipt_date
4822: FROM ar_cash_receipts
4823: WHERE cash_receipt_id =
4824: (SELECT MAX(cr.cash_receipt_id)
4825: FROM ar_cash_receipts cr,
4826: ar_cash_receipt_history crh
4827: WHERE cr.pay_from_customer = p_customer_id
4828: AND cr.cash_receipt_id = crh.cash_receipt_id
4829: AND crh.current_record_flag = 'Y'
7223: p_customer_id_tab IN ar_bus_event_sub_pvt.generic_id_type,
7224: p_site_use_id_tab IN ar_bus_event_sub_pvt.generic_id_type,
7225: p_currency_tab IN ar_bus_event_sub_pvt.currency_type,
7226: p_org_id_tab IN ar_bus_event_sub_pvt.generic_id_type,
7227: p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE default null ) IS
7228:
7229: l_rows NUMBER;
7230:
7231: BEGIN
7240: UPDATE ar_trx_bal_summary main_sum
7241: SET receipts_at_risk_value =
7242: (SELECT SUM(DECODE(rap.applied_payment_schedule_id, -2, 0,
7243: crh.amount))
7244: FROM ar_cash_receipts_all cr,
7245: ar_cash_receipt_history_all crh,
7246: ar_receivable_applications_all rap
7247: WHERE nvl(cr.confirmed_flag, 'Y') = 'Y'
7248: AND cr.reversal_date IS NULL
7282: UPDATE ar_trx_bal_summary main_sum
7283: SET receipts_at_risk_value = receipts_at_risk_value -
7284: (SELECT SUM(DECODE(rap.applied_payment_schedule_id, -2, 0,
7285: crh.amount))
7286: FROM ar_cash_receipts_all cr,
7287: ar_cash_receipt_history_all crh,
7288: ar_receivable_applications_all rap
7289: WHERE nvl(cr.confirmed_flag, 'Y') = 'Y'
7290: AND cr.reversal_date IS NULL