[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
164: ELSIF p_cust_account_id > 0 THEN
165: l_pro_clause := 'SELECT COUNT(1) ' ||
166: 'FROM (' ||
167: ' SELECT pro.promise_detail_id, pro.uwq_status, pro.uwq_active_date, pro.uwq_complete_date' ||
168: ' FROM iex_promise_details pro, iex_delinquencies_all del ' ||
169: ' WHERE pro.delinquency_id = del.delinquency_id ' ||
170: ' AND pro.state = ''BROKEN_PROMISE'' ' ||
171: ' AND pro.status IN (''COLLECTABLE'', ''PENDING'') ' ||
172: ' AND pro.amount_due_remaining > 0 ' || -- added by jypark 01/02/2003
198: ELSIF p_site_use_id > 0 THEN
199: l_pro_clause := 'SELECT COUNT(1) ' ||
200: 'FROM (' ||
201: ' SELECT pro.promise_detail_id, pro.uwq_status, pro.uwq_active_date, pro.uwq_complete_date' ||
202: ' FROM iex_promise_details pro, iex_delinquencies_all del ' ||
203: ' WHERE pro.state = ''BROKEN_PROMISE'' ' ||
204: ' AND pro.status IN (''COLLECTABLE'', ''PENDING'') ' ||
205: ' AND pro.amount_due_remaining > 0 ' || -- added by jypark 01/02/2003
206: --BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
607: -- AND pro.delinquency_id = del.delinquency_id(+)
608: -- AND del.status(+) <> 'CURRENT';
609: SELECT SUM(amount_due_remaining)
610: FROM ( SELECT amount_due_remaining
611: FROM iex_promise_details pro, iex_delinquencies_all del
612: WHERE del.party_cust_id = p_party_id
613: AND pro.status in ('COLLECTABLE', 'PENDING')
614: AND pro.state = 'BROKEN_PROMISE'
615: AND pro.amount_due_remaining > 0
630: --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
631:
632: CURSOR c_acc_amt IS
633: SELECT SUM(amount_due_remaining)
634: FROM iex_promise_details pro, iex_delinquencies_all del
635: WHERE pro.cust_account_id = p_cust_account_id
636: AND pro.status in ('COLLECTABLE', 'PENDING')
637: AND pro.state = 'BROKEN_PROMISE'
638: AND pro.amount_due_remaining > 0
659: -- AND del.status <> 'CURRENT';
660: -- AND del.status IN ('DELINQUENT', 'PREDELINQUENT');
661: SELECT SUM(amount_due_remaining)
662: FROM ( SELECT amount_due_remaining
663: FROM iex_promise_details pro, iex_delinquencies_all del
664: WHERE del.customer_site_use_id = p_site_use_id
665: AND pro.status in ('COLLECTABLE', 'PENDING')
666: AND pro.state = 'BROKEN_PROMISE'
667: AND pro.amount_due_remaining > 0
718: -- AND pro.delinquency_id = del.delinquency_id
719: -- AND del.status IN ('DELINQUENT', 'PREDELINQUENT');
720: SELECT SUM(promise_amount)
721: FROM ( SELECT promise_amount
722: FROM iex_promise_details pro, iex_delinquencies_all del
723: WHERE del.party_cust_id = p_party_id
724: AND pro.status in ('COLLECTABLE', 'PENDING')
725: AND pro.state = 'BROKEN_PROMISE'
726: AND pro.amount_due_remaining > 0
741: --END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
742:
743: CURSOR c_acc_amt IS
744: SELECT SUM(promise_amount)
745: FROM iex_promise_details pro, iex_delinquencies_all del
746: WHERE pro.cust_account_id = p_cust_account_id
747: AND pro.amount_due_remaining > 0
748: AND pro.status in ('COLLECTABLE', 'PENDING')
749: AND pro.state = 'BROKEN_PROMISE'
769: -- AND pro.delinquency_id = del.delinquency_id
770: -- AND del.status <> 'CURRENT';
771: SELECT SUM(promise_amount)
772: FROM ( SELECT promise_amount
773: FROM iex_promise_details pro, iex_delinquencies_all del
774: WHERE del.customer_site_use_id = p_site_use_id
775: AND pro.status in ('COLLECTABLE', 'PENDING')
776: AND pro.state = 'BROKEN_PROMISE'
777: AND pro.amount_due_remaining > 0