[Home] [Help]
1298: acc_site.cust_account_id = cust.cust_account_id;
1299:
1300: cursor c_get_party_from_del(p_delinquency_id number) is
1301: select party_cust_id, customer_site_use_id
1302: from iex_delinquencies_all
1303: where delinquency_id = p_delinquency_id;
1304:
1305: begin
1306:
1508: l_dispute_id number;
1509:
1510: cursor c_get_del_info(p_delinquency_id number) is
1511: select party_cust_id, cust_account_id, customer_site_use_id
1512: from iex_delinquencies_all
1513: where delinquency_id = p_delinquency_id;
1514:
1515: cursor c_get_acct_info(p_cust_account_id number) is
1516: select party_id
3096: l_msg_data VARCHAR2(32767);
3097: errmsg VARCHAR2(32767);
3098: --
3099: nIdx NUMBER := 0;
3100: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE
3101: INDEX BY BINARY_INTEGER;
3102: Del_Tbl Del_ID_TBL_TYPE;
3103: l_bind_tbl IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL;
3104: l_bind_rec IEX_DUNNING_PVT.FULFILLMENT_BIND_REC;
3778: cursor c_amount (IN_ID number) is
3779: select ps.amount_due_remaining,
3780: ps.invoice_currency_code
3781: from ar_payment_schedules_all ps,
3782: --iex_delinquencies_all del
3783: iex_delinquencies del
3784: where ps.payment_schedule_id (+)= del.payment_schedule_id
3785: and del.delinquency_id = in_id;
3786: --
3813: l_msg_data VARCHAR2(32767);
3814: errmsg VARCHAR2(32767);
3815: --
3816: nIdx NUMBER := 0;
3817: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE
3818: INDEX BY BINARY_INTEGER;
3819: Del_Tbl Del_ID_TBL_TYPE;
3820: l_bind_tbl IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL;
3821: l_bind_rec IEX_DUNNING_PVT.FULFILLMENT_BIND_REC;
4453: l_msg_data VARCHAR2(32767);
4454: errmsg VARCHAR2(32767);
4455: --
4456: nIdx NUMBER := 0;
4457: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE
4458: INDEX BY BINARY_INTEGER;
4459: Del_Tbl Del_ID_TBL_TYPE;
4460: l_bind_tbl IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL;
4461: l_bind_rec IEX_DUNNING_PVT.FULFILLMENT_BIND_REC;
5123: cursor c_amount (IN_ID number) is
5124: select ps.amount_due_remaining,
5125: ps.invoice_currency_code
5126: from ar_payment_schedules_all ps,
5127: --iex_delinquencies_all del
5128: iex_delinquencies del
5129: where ps.payment_schedule_id (+)= del.payment_schedule_id
5130: and del.delinquency_id = in_id;
5131: --
5156: l_msg_data VARCHAR2(32767);
5157: errmsg VARCHAR2(32767);
5158: --
5159: nIdx NUMBER := 0;
5160: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE
5161: INDEX BY BINARY_INTEGER;
5162: Del_Tbl Del_ID_TBL_TYPE;
5163: l_bind_tbl IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL;
5164: l_bind_rec IEX_DUNNING_PVT.FULFILLMENT_BIND_REC;
8890: ||' and ref.object_id=dun.dunning_id'
8891: ||' and dun.dunning_level='''||p_running_level||'''';
8892:
8893: IF (p_running_level = 'CUSTOMER') THEN
8894: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
8895: ||' where del.status in (''DELINQUENT'',''PREDELINQUENT'') '
8896: ||' and dun.dunning_object_id=del.party_cust_id)';
8897: ELSIF(p_running_level = 'ACCOUNT') THEN
8898: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
8894: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
8895: ||' where del.status in (''DELINQUENT'',''PREDELINQUENT'') '
8896: ||' and dun.dunning_object_id=del.party_cust_id)';
8897: ELSIF(p_running_level = 'ACCOUNT') THEN
8898: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
8899: ||' where del.status in (''DELINQUENT'',''PREDELINQUENT'') '
8900: ||' and dun.dunning_object_id=del.cust_account_id)';
8901: ELSIF(p_running_level = 'BILL_TO') THEN
8902: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
8898: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
8899: ||' where del.status in (''DELINQUENT'',''PREDELINQUENT'') '
8900: ||' and dun.dunning_object_id=del.cust_account_id)';
8901: ELSIF(p_running_level = 'BILL_TO') THEN
8902: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
8903: ||' where del.status in (''DELINQUENT'',''PREDELINQUENT'') '
8904: ||' and dun.dunning_object_id=del.customer_site_use_id)';
8905: ELSE
8906: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
8902: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
8903: ||' where del.status in (''DELINQUENT'',''PREDELINQUENT'') '
8904: ||' and dun.dunning_object_id=del.customer_site_use_id)';
8905: ELSE
8906: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
8907: ||' where del.status in (''DELINQUENT'',''PREDELINQUENT'') '
8908: ||' and dun.dunning_object_id=del.delinquency_id)';
8909: END IF;
8910: WriteLog(G_PKG_NAME || ' ' || l_api_name || 'Query: '||l_task_query);
9855: IS
9856: cursor c_amount (IN_ID number) is
9857: select ps.invoice_currency_code
9858: from ar_payment_schedules_all ps,
9859: --iex_delinquencies_all del
9860: iex_delinquencies del
9861: where ps.payment_schedule_id (+)= del.payment_schedule_id
9862: and del.cust_account_id = in_id;
9863: l_code VARCHAR2(15);
9939: IS
9940: cursor c_amount (IN_ID number) is
9941: select sum(ps.acctd_amount_due_remaining)
9942: from ar_payment_schedules_all ps,
9943: --iex_delinquencies_all del
9944: iex_delinquencies del
9945: where ps.payment_schedule_id (+)= del.payment_schedule_id
9946: and del.cust_account_id = in_id;
9947: l_sum number;