[Home] [Help]
14: -- Cursor to select all cust_account_ids in case user passes party_id
15: CURSOR AR_AGING_PARTY_CUR(p_party Number)
16: IS
17: SELECT cust_account_id
18: FROM HZ_CUST_ACCOUNTS
19: WHERE party_id = p_party ;
20:
21: -- Cursor to select all cust_account_ids in case user passes party_id for paying relationship
22: CURSOR AR_AGING_PAYING_PARTY_CUR(p_party Number)
21: -- Cursor to select all cust_account_ids in case user passes party_id for paying relationship
22: CURSOR AR_AGING_PAYING_PARTY_CUR(p_party Number)
23: IS
24: SELECT cust_account_id
25: FROM HZ_CUST_ACCOUNTS
26: WHERE party_id IN
27: (SELECT p_party FROM dual
28: UNION
29: SELECT ar.related_party_id
910: ps.acctd_amount_due_remaining,
911: ps.amount_due_remaining)), 0)
912: FROM ar_payment_schedules ps,
913: ra_cust_trx_line_gl_dist gld,
914: hz_cust_accounts hzca
915: WHERE ps.customer_id = hzca.cust_account_id
916: AND hzca.party_id = p_filter_id
917: AND decode(p_customer_site_use_id,
918: NULL, ps.customer_site_use_id,
932: ps.acctd_amount_due_remaining,
933: ps.amount_due_remaining)), 0)
934: FROM ar_payment_schedules ps,
935: ra_cust_trx_line_gl_dist gld,
936: hz_cust_accounts hzca
937: WHERE ps.customer_id = hzca.cust_account_id
938: AND hzca.party_id IN
939: (SELECT p_filter_id FROM dual
940: UNION
1083: -app.acctd_amount_applied_from,
1084: -app.amount_applied),0), 0) ), 0)
1085: FROM ar_receivable_applications app,
1086: ar_payment_schedules ps,
1087: hz_cust_accounts hzca
1088: WHERE ps.customer_id = hzca.cust_account_id
1089: AND hzca.party_id = p_filter_id
1090: AND decode(p_customer_site_use_id,
1091: NULL, nvl(ps.customer_site_use_id,-10),
1119: -app.acctd_amount_applied_from,
1120: -app.amount_applied),0), 0) ), 0)
1121: FROM ar_receivable_applications app,
1122: ar_payment_schedules ps,
1123: hz_cust_accounts hzca
1124: WHERE ps.customer_id = hzca.cust_account_id
1125: AND hzca.party_id IN
1126: (SELECT p_filter_id FROM dual
1127: UNION
1363: SELECT NVL( SUM( DECODE(p_currency_code, NULL, crh.acctd_amount,
1364: crh.amount)), 0)
1365: FROM ar_cash_receipts cr,
1366: ar_cash_receipt_history crh,
1367: hz_cust_accounts hzca
1368: WHERE cr.pay_from_customer = hzca.cust_account_id
1369: AND hzca.party_id = p_filter_id
1370: /* bug no : 1274152. Aging form did not consider the receipts done
1371: without customer location for the calculation of receipt at risk.
1394: SELECT NVL( SUM( DECODE(p_currency_code, NULL, crh.acctd_amount,
1395: crh.amount)), 0)
1396: FROM ar_cash_receipts cr,
1397: ar_cash_receipt_history crh,
1398: hz_cust_accounts hzca
1399: WHERE cr.pay_from_customer = hzca.cust_account_id
1400: AND hzca.party_id IN
1401: (SELECT p_filter_id FROM dual
1402: UNION
1436: ar_cash_receipt_history crh,
1437: /* begin add for bug 4930373 to use AR_CASH_RECEIPTS_N2 */
1438: hz_cust_acct_sites_all acct_site,
1439: hz_cust_site_uses_all site_uses,
1440: hz_cust_accounts_all cust_acct
1441: /* end add for bug 4930373 to use AR_CASH_RECEIPTS_N2 */
1442: WHERE cr.customer_site_use_id = p_filter_id
1443: /* bug no : 1274152. Aging form did not consider the receipts done
1444: without customer location for the calculation of receipt at risk.
1609: SELECT NVL(SUM(decode(p_currency_code, NULL,
1610: ROUND(ps.amount_in_dispute * nvl(ps.exchange_rate,1),l_round_decimal),
1611: ps.amount_in_dispute)),0)
1612: FROM ar_payment_schedules ps,
1613: hz_cust_accounts hzca
1614: WHERE ps.customer_id = hzca.cust_Account_id
1615: AND hzca.party_id = p_filter_id
1616: AND decode(p_customer_site_use_id,
1617: NULL, ps.customer_site_use_id,
1629: SELECT NVL(SUM(decode(p_currency_code, NULL,
1630: ROUND(ps.amount_in_dispute * nvl(ps.exchange_rate,1),l_round_decimal),
1631: ps.amount_in_dispute)),0)
1632: FROM ar_payment_schedules ps,
1633: hz_cust_accounts hzca
1634: WHERE ps.customer_id = hzca.cust_Account_id
1635: AND hzca.party_id IN
1636: (SELECT p_filter_id FROM dual
1637: UNION
1783: NVL(ps.exchange_rate, 1)),0), l_round_decimal),
1784: ROUND(NVL(SUM(ps.amount_adjusted_pending *
1785: NVL(ps.exchange_rate, 1)),0), l_round_decimal)
1786: FROM ar_payment_schedules ps,
1787: hz_cust_accounts hzca
1788: WHERE ps.customer_id = hzca.cust_account_id
1789: AND hzca.party_id = p_filter_id
1790: AND decode(p_customer_site_use_id,
1791: NULL, ps.customer_site_use_id,
1806: NVL(ps.exchange_rate, 1)),0), l_round_decimal),
1807: ROUND(NVL(SUM(ps.amount_adjusted_pending *
1808: NVL(ps.exchange_rate, 1)),0), l_round_decimal)
1809: FROM ar_payment_schedules ps,
1810: hz_cust_accounts hzca
1811: WHERE ps.customer_id = hzca.cust_account_id
1812: AND hzca.party_id IN
1813: (SELECT p_filter_id FROM dual
1814: UNION
1876: SELECT
1877: ROUND(NVL(SUM( ps.receivables_charges_charged
1878: * NVL(ps.exchange_rate,1)),0),l_round_decimal)
1879: FROM ar_payment_schedules ps,
1880: hz_cust_accounts hzca
1881: WHERE ps.customer_id = hzca.cust_account_id
1882: --- Begin - Andre Araujo - 11/09/2004 - Performance fix
1883: AND ps.status = 'OP'
1884: --- End - Andre Araujo - 11/09/2004 - Performance fix
1896: SELECT
1897: ROUND(NVL(SUM( ps.receivables_charges_charged
1898: * NVL(ps.exchange_rate,1)),0),l_round_decimal)
1899: FROM ar_payment_schedules ps,
1900: hz_cust_accounts hzca
1901: WHERE ps.customer_id = hzca.cust_account_id
1902: AND hzca.party_id IN
1903: (SELECT p_filter_id FROM dual
1904: UNION
2952: l_collectible_bkt_amt_tbl
2953: from ar_aging_bucket_lines aabl,
2954: ar_payment_schedules aps,
2955: iex_delinquencies id,
2956: hz_cust_accounts hzca
2957: where id.payment_schedule_id = aps.payment_schedule_id
2958: --BEGIN-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
2959: -- and id.status IN ('DELINQUENT', 'PRE-DELINQUENT')
2960: and id.status IN ('DELINQUENT', 'PREDELINQUENT')
2988: (SELECT DISTINCT id.transaction_id
2989: FROM ar_aging_bucket_lines aabl,
2990: ar_payment_schedules aps,
2991: iex_delinquencies id,
2992: hz_cust_accounts hzca
2993: WHERE id.payment_schedule_id = aps.payment_schedule_id
2994: AND id.status IN('DELINQUENT', 'PREDELINQUENT')
2995: AND TRUNC(sysdate) -aps.due_date >= aabl.days_start
2996: AND TRUNC(sysdate) -aps.due_date <= aabl.days_to
3021: l_collectible_bkt_amt_tbl
3022: from ar_aging_bucket_lines aabl,
3023: ar_payment_schedules aps,
3024: iex_delinquencies id,
3025: hz_cust_accounts hzca
3026: where id.payment_schedule_id = aps.payment_schedule_id
3027: --BEGIN-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
3028: -- and id.status IN ('DELINQUENT', 'PRE-DELINQUENT')
3029: and id.status IN ('DELINQUENT', 'PREDELINQUENT')
3049: (SELECT DISTINCT id.transaction_id
3050: FROM ar_aging_bucket_lines aabl,
3051: ar_payment_schedules aps,
3052: iex_delinquencies id,
3053: hz_cust_accounts hzca
3054: WHERE id.payment_schedule_id = aps.payment_schedule_id
3055: AND id.status IN('DELINQUENT', 'PREDELINQUENT')
3056: AND TRUNC(sysdate) -aps.due_date >= aabl.days_start
3057: AND TRUNC(sysdate) -aps.due_date <= aabl.days_to
3410: v_paying_party_tran_sql :=
3411: 'SELECT NVL(count(1), 0), NVL(SUM(ACCTD_AMOUNT_DUE_REMAINING), 0)
3412: FROM ar_payment_schedules arp,
3413: ar_aging_bucket_lines aabl,
3414: hz_cust_accounts hzca
3415: WHERE trunc(sysdate)-trunc(arp.due_date) >= aabl.days_start
3416: and trunc(sysdate)-trunc(arp.due_date) <= aabl.days_to
3417: and arp.class = :class
3418: and arp.status = ''OP''
3432: v_party_tran_sql :=
3433: -- 'SELECT NVL(count(1), 0), NVL(SUM(ACCTD_AMOUNT_DUE_REMAINING), 0)
3434: -- FROM ar_payment_schedules arp,
3435: -- ar_aging_bucket_lines aabl,
3436: -- hz_cust_accounts hzca
3437: -- WHERE sysdate-arp.due_date >= aabl.days_start
3438: -- and sysdate-arp.due_date <= aabl.days_to
3439: -- and arp.class = :class
3440: -- and arp.status = ''OP''
3443: -- and hzca.party_id = :party_id ' ;
3444: --Bug5170294. Fix by LKKUMAR. Use Trunc on Dates. Start.
3445: 'SELECT NVL(count(1), 0), NVL(SUM(ACCTD_AMOUNT_DUE_REMAINING), 0)
3446: FROM ar_payment_schedules arp
3447: WHERE (arp.customer_id in (select cust_account_id from hz_cust_accounts where party_id = :party_id)
3448: and arp.status = ''OP'')
3449: and
3450: (
3451: trunc(sysdate)-trunc(arp.due_date) >= :days_start
3460: v_party_billto_tran_sql :=
3461: -- 'SELECT NVL(count(1), 0), NVL(SUM(ACCTD_AMOUNT_DUE_REMAINING), 0)
3462: -- FROM ar_payment_schedules arp,
3463: -- ar_aging_bucket_lines aabl,
3464: -- hz_cust_accounts hzca
3465: -- WHERE sysdate-arp.due_date >= aabl.days_start
3466: -- and sysdate-arp.due_date <= aabl.days_to
3467: -- and arp.class = :class
3468: -- and arp.status = ''OP''
3612: ra_customer_trx rct,
3613: okl_cnsld_ar_strms_b ocasb,
3614: OKL_CNSLD_AR_LINES_B ocalb,
3615: OKL_CNSLD_AR_HDRS_B ocahb,
3616: HZ_CUST_ACCOUNTS hzca
3617: WHERE (:l_date - arp.due_date) >= aabl.days_start
3618: and (:l_date - arp.due_date) <= aabl.days_to
3619: and arp.class = :l_class
3620: and rct.customer_trx_id = arp.customer_trx_id
3644: ra_customer_trx rct,
3645: okl_cnsld_ar_strms_b ocasb,
3646: OKL_CNSLD_AR_LINES_B ocalb,
3647: OKL_CNSLD_AR_HDRS_B ocahb,
3648: HZ_CUST_ACCOUNTS hzca
3649: WHERE (:l_date - arp.due_date) >= aabl.days_start
3650: and (:l_date - arp.due_date) <= aabl.days_to
3651: and arp.class = :l_class
3652: and rct.customer_trx_id = arp.customer_trx_id
3676: ra_customer_trx rct,
3677: okl_cnsld_ar_strms_b ocasb,
3678: OKL_CNSLD_AR_LINES_B ocalb,
3679: OKL_CNSLD_AR_HDRS_B ocahb,
3680: HZ_CUST_ACCOUNTS hzca
3681: WHERE (:l_date - arp.due_date) >= aabl.days_start
3682: and (:l_date - arp.due_date) <= aabl.days_to
3683: and arp.class = :l_class
3684: and rct.customer_trx_id = arp.customer_trx_id