[Home] [Help]
129: IF p_party_id > 0 THEN
130: l_pro_clause := 'SELECT COUNT(1) ' ||
131: 'FROM (' ||
132: ' SELECT pro.promise_detail_id, pro.uwq_status, pro.uwq_active_date, pro.uwq_complete_date' ||
133: ' FROM iex_promise_details pro, hz_cust_accounts ca, iex_delinquencies_all del ' ||
134: ' WHERE ca.cust_account_id = pro.cust_account_id ' ||
135: ' AND pro.state = ''BROKEN_PROMISE'' ' ||
136: ' AND pro.status IN (''COLLECTABLE'', ''PENDING'') ' ||
137: ' AND pro.amount_due_remaining > 0 ' || -- added by jypark 01/02/2003
165: ELSIF p_cust_account_id > 0 THEN
166: l_pro_clause := 'SELECT COUNT(1) ' ||
167: 'FROM (' ||
168: ' SELECT pro.promise_detail_id, pro.uwq_status, pro.uwq_active_date, pro.uwq_complete_date' ||
169: ' FROM iex_promise_details pro, iex_delinquencies_all del ' ||
170: ' WHERE pro.delinquency_id = del.delinquency_id ' ||
171: ' AND pro.state = ''BROKEN_PROMISE'' ' ||
172: ' AND pro.status IN (''COLLECTABLE'', ''PENDING'') ' ||
173: ' AND pro.amount_due_remaining > 0 ' || -- added by jypark 01/02/2003
200: ELSIF p_site_use_id > 0 THEN
201: l_pro_clause := 'SELECT COUNT(1) ' ||
202: 'FROM (' ||
203: ' SELECT pro.promise_detail_id, pro.uwq_status, pro.uwq_active_date, pro.uwq_complete_date' ||
204: ' FROM iex_promise_details pro, iex_delinquencies_all del ' ||
205: ' WHERE pro.state = ''BROKEN_PROMISE'' ' ||
206: ' AND pro.status IN (''COLLECTABLE'', ''PENDING'') ' ||
207: ' AND pro.amount_due_remaining > 0 ' || -- added by jypark 01/02/2003
208: --BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
578: -- AND pro.delinquency_id = del.delinquency_id(+)
579: -- AND del.status(+) <> 'CURRENT';
580: SELECT SUM(amount_due_remaining)
581: FROM ( SELECT amount_due_remaining
582: FROM iex_promise_details pro, iex_delinquencies_all del
583: WHERE del.party_cust_id = p_party_id
584: AND pro.status in ('COLLECTABLE', 'PENDING')
585: AND pro.state = 'BROKEN_PROMISE'
586: AND pro.amount_due_remaining > 0
601: --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
602:
603: CURSOR c_acc_amt IS
604: SELECT SUM(amount_due_remaining)
605: FROM iex_promise_details pro, iex_delinquencies_all del
606: WHERE pro.cust_account_id = p_cust_account_id
607: AND pro.status in ('COLLECTABLE', 'PENDING')
608: AND pro.state = 'BROKEN_PROMISE'
609: AND pro.amount_due_remaining > 0
630: -- AND del.status <> 'CURRENT';
631: -- AND del.status IN ('DELINQUENT', 'PREDELINQUENT');
632: SELECT SUM(amount_due_remaining)
633: FROM ( SELECT amount_due_remaining
634: FROM iex_promise_details pro, iex_delinquencies_all del
635: WHERE del.customer_site_use_id = p_site_use_id
636: AND pro.status in ('COLLECTABLE', 'PENDING')
637: AND pro.state = 'BROKEN_PROMISE'
638: AND pro.amount_due_remaining > 0
689: -- AND pro.delinquency_id = del.delinquency_id
690: -- AND del.status IN ('DELINQUENT', 'PREDELINQUENT');
691: SELECT SUM(promise_amount)
692: FROM ( SELECT promise_amount
693: FROM iex_promise_details pro, iex_delinquencies_all del
694: WHERE del.party_cust_id = p_party_id
695: AND pro.status in ('COLLECTABLE', 'PENDING')
696: AND pro.state = 'BROKEN_PROMISE'
697: AND pro.amount_due_remaining > 0
712: --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
713:
714: CURSOR c_acc_amt IS
715: SELECT SUM(promise_amount)
716: FROM iex_promise_details pro, iex_delinquencies_all del
717: WHERE pro.cust_account_id = p_cust_account_id
718: AND pro.amount_due_remaining > 0
719: AND pro.status in ('COLLECTABLE', 'PENDING')
720: AND pro.state = 'BROKEN_PROMISE'
740: -- AND pro.delinquency_id = del.delinquency_id
741: -- AND del.status <> 'CURRENT';
742: SELECT SUM(promise_amount)
743: FROM ( SELECT promise_amount
744: FROM iex_promise_details pro, iex_delinquencies_all del
745: WHERE del.customer_site_use_id = p_site_use_id
746: AND pro.status in ('COLLECTABLE', 'PENDING')
747: AND pro.state = 'BROKEN_PROMISE'
748: AND pro.amount_due_remaining > 0