eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IEX_DUNNING_PVT dependencies on IEX_DELINQUENCIES

Line 1302: from iex_delinquencies_all

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:

Line 1510: from iex_delinquencies_all

1506: l_dispute_id number;
1507:
1508: cursor c_get_del_info(p_delinquency_id number) is
1509: select party_cust_id, cust_account_id, customer_site_use_id
1510: from iex_delinquencies_all
1511: where delinquency_id = p_delinquency_id;
1512:
1513: cursor c_get_acct_info(p_cust_account_id number) is
1514: select party_id

Line 1994: FROM iex_delinquencies

1990: )
1991: IS
1992: CURSOR C_GET_Del (IN_del_ID NUMBER) IS
1993: SELECT delinquency_id
1994: FROM iex_delinquencies
1995: WHERE delinquency_ID = IN_del_ID;
1996: --
1997: l_delinquency_id NUMBER;
1998:

Line 2808: ' from iex_delinquencies del, ' ||

2804:
2805: vPLSQL := 'select del.delinquency_id, ' ||
2806: ' del.transaction_id, ' ||
2807: ' del.payment_schedule_id ' ||
2808: ' from iex_delinquencies del, ' ||
2809: ' ar_payment_schedules arp ' ||
2810: ' where del.payment_schedule_id = arp.payment_schedule_id ' ||
2811: ' and del.status in (''DELINQUENT'',''PREDELINQUENT'') ' ||
2812: ' and del.staged_dunning_level is NULL ' ||

Line 2896: update iex_delinquencies_all

2892: END IF;
2893:
2894: /*
2895: if p_dunning_mode <> 'DRAFT' then
2896: update iex_delinquencies_all
2897: set staged_dunning_level = i
2898: where delinquency_id = l_delinquency_id;
2899: end if;
2900: */

Line 2912: ' from iex_delinquencies del, ' ||

2908: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Start Include past due fully disputed invoices');
2909: vPLSQL1 := 'select del.delinquency_id, ' ||
2910: ' del.transaction_id, ' ||
2911: ' del.payment_schedule_id ' ||
2912: ' from iex_delinquencies del, ' ||
2913: ' ar_payment_schedules arp ' ||
2914: ' where del.payment_schedule_id = arp.payment_schedule_id ' ||
2915: ' and del.status = ''CURRENT'' ' ||
2916: ' and del.staged_dunning_level is NULL ' ||

Line 2968: update iex_delinquencies_all

2964: END IF;
2965:
2966: /*
2967: if p_dunning_mode <> 'DRAFT' then
2968: update iex_delinquencies_all
2969: set staged_dunning_level = i
2970: where delinquency_id = l_delinquency_id;
2971: end if;
2972: */

Line 2988: ' from iex_delinquencies del ' ||

2984:
2985: vPLSQL := 'select del.delinquency_id, ' ||
2986: ' del.transaction_id, ' ||
2987: ' del.payment_schedule_id ' ||
2988: ' from iex_delinquencies del ' ||
2989: ' ,ar_payment_schedules arp ' ||
2990: ' where ' ||
2991: ' del.payment_schedule_id = arp.payment_schedule_id and ' ||
2992: ' del.status in (''DELINQUENT'',''PREDELINQUENT'') ' ||

Line 3081: update iex_delinquencies_all

3077: END IF;
3078:
3079: /*
3080: if p_dunning_mode <> 'DRAFT' then
3081: update iex_delinquencies_all
3082: set staged_dunning_level = i
3083: where delinquency_id = l_delinquency_id;
3084: end if;
3085: */

Line 3098: ' from iex_delinquencies del ' ||

3094: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Start Include past due fully disputed invoices');
3095: vPLSQL1 := 'select del.delinquency_id, ' ||
3096: ' del.transaction_id, ' ||
3097: ' del.payment_schedule_id ' ||
3098: ' from iex_delinquencies del ' ||
3099: ' ,ar_payment_schedules arp ' ||
3100: ' where ' ||
3101: ' del.payment_schedule_id = arp.payment_schedule_id and ' ||
3102: ' del.status = ''CURRENT'' ' ||

Line 3168: update iex_delinquencies_all

3164: END IF;
3165:
3166: /*
3167: if p_dunning_mode <> 'DRAFT' then
3168: update iex_delinquencies_all
3169: set staged_dunning_level = i
3170: where delinquency_id = l_delinquency_id;
3171: end if;
3172: */

Line 3206: ' and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ||

3202: ' and arp.status = ''OP'' ' ||
3203: ' and arp.amount_due_remaining <> 0 ' ||--' and arp.amount_due_remaining > 0 ' || Changed to fix 12552027 by snuthala 5/19/2011
3204: ' and (arp.class = ''INV'' or arp.class = ''BR'') ' || -- Bills Receivables change
3205: ' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
3206: ' and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ||
3207: ' order by arp.payment_schedule_id';
3208: elsif (p_running_level = 'ACCOUNT') then
3209: vPLSQL2 := 'select arp.customer_trx_id, ' ||
3210: ' arp.payment_schedule_id ' ||

Line 3218: ' and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'') ' ||

3214: ' and arp.status = ''OP'' ' ||
3215: ' and arp.amount_due_remaining <> 0 ' ||--' and arp.amount_due_remaining > 0 ' || Changed to fix 12552027 by snuthala 5/19/2011
3216: ' and (arp.class = ''INV'' or arp.class = ''BR'') ' || -- Bills Receivables change
3217: ' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
3218: ' and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'') ' ||
3219: ' order by arp.payment_schedule_id';
3220: elsif (p_running_level = 'BILL_TO') then
3221: vPLSQL2 := 'select arp.customer_trx_id, ' ||
3222: ' arp.payment_schedule_id ' ||

Line 3231: ' and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'') ' ||

3227: ' and arp.status = ''OP'' ' ||
3228: ' and arp.amount_due_remaining <> 0 ' ||--' and arp.amount_due_remaining > 0 ' || Changed to fix 12552027 by snuthala 5/19/2011
3229: ' and (arp.class = ''INV'' or arp.class = ''BR'') ' || -- Bills Receivables change
3230: ' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
3231: ' and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'') ' ||
3232: ' order by arp.payment_schedule_id';
3233: end if;
3234: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - vPLSQL2 :'||vPLSQL2);
3235: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_object_id :'||l_object_id);

Line 3287: ' from iex_delinquencies del, ' ||

3283:
3284: vPLSQL := 'select del.delinquency_id, ' ||
3285: ' del.transaction_id, ' ||
3286: ' del.payment_schedule_id ' ||
3287: ' from iex_delinquencies del, ' ||
3288: ' ar_payment_schedules arp ' ||
3289: ' where del.payment_schedule_id = arp.payment_schedule_id ' ||
3290: ' and del.status in (''DELINQUENT'',''PREDELINQUENT'') ' ||
3291: ' and del.staged_dunning_level is NULL ' ||

Line 3997: FROM IEX_DELINQUENCIES

3993:
3994: IS
3995: CURSOR C_GET_DEL (IN_del_ID NUMBER) IS
3996: SELECT delinquency_ID
3997: FROM IEX_DELINQUENCIES
3998: WHERE delinquency_ID = in_del_ID;
3999: --
4000: -- begin bug 4914799 ctlee 12/30/2005 add p_dunning_plan_id
4001: CURSOR C_GET_SCORE (IN_ID NUMBER, IN_CODE VARCHAR2, p_dunning_plan_id number) IS

Line 4054: from iex_delinquencies d

4050: -- cursor for checking if the number of delinquencies NOT disputed
4051: /*
4052: CURSOR C_DISPUTED_AMOUNT(P_PARTY_ID NUMBER, P_CUST_ACCOUNT_ID NUMBER, P_SITE_USE_ID NUMBER ) IS
4053: select sum(a.amount_in_dispute) - sum(a.amount_due_remaining)
4054: from iex_delinquencies d
4055: ,ar_payment_schedules a
4056: where a.payment_schedule_id = d.payment_schedule_id
4057: and d.party_cust_id = nvl(p_party_id, d.party_cust_id)
4058: and d.cust_account_id = nvl(P_CUST_ACCOUNT_ID, d.cust_account_id )

Line 4064: from iex_delinquencies d

4060: and d.status IN ('DELINQUENT', 'PREDELINQUENT');
4061: */
4062: CURSOR C_DISPUTED_AMOUNT_PARTY(P_PARTY_ID NUMBER) IS
4063: select sum(a.amount_in_dispute) - sum(a.amount_due_remaining)
4064: from iex_delinquencies d
4065: ,ar_payment_schedules a
4066: where a.payment_schedule_id = d.payment_schedule_id
4067: and d.party_cust_id = p_party_id
4068: and d.status IN ('DELINQUENT', 'PREDELINQUENT');

Line 4072: from iex_delinquencies d

4068: and d.status IN ('DELINQUENT', 'PREDELINQUENT');
4069:
4070: CURSOR C_DISPUTED_AMOUNT_ACCOUNT(P_CUST_ACCOUNT_ID NUMBER) IS
4071: select sum(a.amount_in_dispute) - sum(a.amount_due_remaining)
4072: from iex_delinquencies d
4073: ,ar_payment_schedules a
4074: where a.payment_schedule_id = d.payment_schedule_id
4075: and d.cust_account_id = P_CUST_ACCOUNT_ID
4076: and d.status IN ('DELINQUENT', 'PREDELINQUENT');

Line 4080: from iex_delinquencies d

4076: and d.status IN ('DELINQUENT', 'PREDELINQUENT');
4077:
4078: CURSOR C_DISPUTED_AMOUNT_BILLTO(P_SITE_USE_ID NUMBER ) IS
4079: select sum(a.amount_in_dispute) - sum(a.amount_due_remaining)
4080: from iex_delinquencies d
4081: ,ar_payment_schedules a
4082: where a.payment_schedule_id = d.payment_schedule_id
4083: and d.customer_site_use_id = p_site_use_id
4084: and d.status IN ('DELINQUENT', 'PREDELINQUENT');

Line 4091: FROM ar_payment_schedules_all ps, iex_delinquencies_all del

4087:
4088: -- Start for the bug#8408162 by PNAVEENK on 7-4-2009
4089: cursor c_fully_promised_party(p_party_id number) is
4090: SELECT count(1)
4091: FROM ar_payment_schedules_all ps, iex_delinquencies_all del
4092: WHERE del.party_cust_id=p_party_id
4093: AND ps.payment_schedule_id = del.payment_schedule_id
4094: AND ps.status = 'OP'
4095: AND del.status IN ('DELINQUENT', 'PREDELINQUENT')

Line 4104: FROM ar_payment_schedules_all ps, iex_delinquencies_all del

4100: group by pd.delinquency_id
4101: having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
4102: cursor c_fully_promised_account(p_cust_account_id number) is
4103: SELECT count(1)
4104: FROM ar_payment_schedules_all ps, iex_delinquencies_all del
4105: WHERE del.cust_account_id=p_cust_account_id
4106: AND ps.payment_schedule_id = del.payment_schedule_id
4107: AND ps.status = 'OP'
4108: AND del.status IN ('DELINQUENT', 'PREDELINQUENT')

Line 4117: FROM ar_payment_schedules_all ps, iex_delinquencies_all del

4113: group by pd.delinquency_id
4114: having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
4115: cursor c_fully_promised_billto(p_site_use_id number) is
4116: SELECT count(1)
4117: FROM ar_payment_schedules_all ps, iex_delinquencies_all del
4118: WHERE del.customer_site_use_id= p_site_use_id
4119: AND ps.payment_schedule_id = del.payment_schedule_id
4120: AND ps.status = 'OP'
4121: AND del.status IN ('DELINQUENT', 'PREDELINQUENT')

Line 4158: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE

4154: l_msg_data VARCHAR2(32767);
4155: errmsg VARCHAR2(32767);
4156: --
4157: nIdx NUMBER := 0;
4158: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE
4159: INDEX BY BINARY_INTEGER;
4160: Del_Tbl Del_ID_TBL_TYPE;
4161: l_bind_tbl IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL;
4162: l_bind_rec IEX_DUNNING_PVT.FULFILLMENT_BIND_REC;

Line 4243: from ar_payment_schedules_all aps,iex_delinquencies_all dd,hz_cust_accounts hzca

4239: where dunning_plan_id = p_dunn_plan_id;
4240:
4241: cursor c_cust_acct_amt_due_rem(p_party_id number,p_org_id number, p_corr_date date, p_grace_days number, p_include_dis_items varchar) is
4242: select nvl(sum(aps.acctd_amount_due_remaining),0)
4243: from ar_payment_schedules_all aps,iex_delinquencies_all dd,hz_cust_accounts hzca
4244: where dd.payment_schedule_id = aps.payment_schedule_id
4245: and aps.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP', 'BR') -- Bills Receivables change
4246: and aps.status='OP'
4247: and (trunc(aps.due_date) + p_grace_days) <= p_corr_date

Line 4689: ' from iex_delinquencies del, ' ||

4685: vPLSQL1 := 'select count(*) from ( ' ||
4686: ' select del.delinquency_id, ' ||
4687: ' del.transaction_id, ' ||
4688: ' del.payment_schedule_id ' ||
4689: ' from iex_delinquencies del, ' ||
4690: ' ar_payment_schedules arp ' ||
4691: ' where del.payment_schedule_id = arp.payment_schedule_id ' ||
4692: ' and del.status in (''DELINQUENT'',''PREDELINQUENT'') ' ||
4693: ' and (trunc(arp.due_date) + :p_grace_days) <= :p_corr_date ' ||

Line 4709: --'and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) and hca.party_id = :p_party_id ' ; -- Bills Receivables

4705: -- 'where arp.status = ''OP'' ' || -- Bills Receivables
4706: 'and arp.amount_due_remaining <> 0 ' ||
4707: 'and (arp.class = ''INV'' or arp.class = ''BR'') ' || -- Bills Receivables change
4708: 'and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
4709: --'and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) and hca.party_id = :p_party_id ' ; -- Bills Receivables
4710: 'and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ; -- Bills Receivables
4711: else
4712: vPLSQL2 := ' ';
4713: end if;*/

Line 4710: 'and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ; -- Bills Receivables

4706: 'and arp.amount_due_remaining <> 0 ' ||
4707: 'and (arp.class = ''INV'' or arp.class = ''BR'') ' || -- Bills Receivables change
4708: 'and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
4709: --'and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) and hca.party_id = :p_party_id ' ; -- Bills Receivables
4710: 'and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ; -- Bills Receivables
4711: else
4712: vPLSQL2 := ' ';
4713: end if;*/
4714:

Line 5264: FROM IEX_DELINQUENCIES_ALL

5260:
5261: IS
5262: CURSOR C_GET_DEL (IN_del_ID NUMBER) IS
5263: SELECT delinquency_ID
5264: FROM IEX_DELINQUENCIES_ALL
5265: WHERE delinquency_ID = in_del_ID;
5266: --
5267: -- begin bug 4914799 ctlee 12/30/2005 add p_dunning_plan_id
5268: CURSOR C_GET_SCORE (IN_ID NUMBER, IN_CODE VARCHAR2, p_dunning_plan_id number) IS

Line 5382: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE

5378: l_msg_data VARCHAR2(32767);
5379: errmsg VARCHAR2(32767);
5380: --
5381: nIdx NUMBER := 0;
5382: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE
5383: INDEX BY BINARY_INTEGER;
5384: Del_Tbl Del_ID_TBL_TYPE;
5385: l_bind_tbl IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL;
5386: l_bind_rec IEX_DUNNING_PVT.FULFILLMENT_BIND_REC;

Line 5742: update iex_delinquencies del

5738: close c_min_days_between_dunn_99;
5739: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_min_days_between_dunn_99 :'|| l_min_days_between_dunn_99);
5740:
5741: if (p_running_level = 'CUSTOMER') then
5742: update iex_delinquencies del
5743: set staged_dunning_level = 98
5744: where del.party_cust_id = l_object_id
5745: and staged_dunning_level = 99
5746: and status in ('DELINQUENT','PREDELINQUENT')

Line 5769: update iex_delinquencies del

5765: )
5766: <= p_correspondence_date ;
5767:
5768: elsif (p_running_level = 'ACCOUNT') then
5769: update iex_delinquencies del
5770: set staged_dunning_level = 98
5771: where del.cust_account_id = l_object_id
5772: and staged_dunning_level = 99
5773: and status in ('DELINQUENT','PREDELINQUENT')

Line 5796: update iex_delinquencies del

5792: )
5793: <= p_correspondence_date ;
5794:
5795: elsif (p_running_level = 'BILL_TO') then
5796: update iex_delinquencies del
5797: set staged_dunning_level = 98
5798: where del.customer_site_use_id = l_object_id
5799: and staged_dunning_level = 99
5800: and status in ('DELINQUENT','PREDELINQUENT')

Line 6008: ' from iex_delinquencies del, ' ||

6004: vPLSQL1 := 'select count(*) from ( ' ||
6005: ' select del.delinquency_id, ' ||
6006: ' del.transaction_id, ' ||
6007: ' del.payment_schedule_id ' ||
6008: ' from iex_delinquencies del, ' ||
6009: ' ar_payment_schedules arp ' ||
6010: ' where del.payment_schedule_id = arp.payment_schedule_id ' ||
6011: ' and del.status in (''DELINQUENT'',''PREDELINQUENT'') ' ||
6012: ' and del.staged_dunning_level is NULL ' ||

Line 6029: 'and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ;*/

6025: 'and arp.status = ''OP'' ' ||
6026: 'and arp.amount_due_remaining <> 0 ' ||--' and arp.amount_due_remaining > 0 ' || Changed to fix 12552027 by snuthala 5/19/2011
6027: 'and arp.class = ''INV'' ' ||
6028: 'and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
6029: 'and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ;*/
6030:
6031: else
6032: vPLSQL2 := ' ';
6033:

Line 6097: ' from iex_delinquencies del ' ||

6093: vPLSQL1 := 'select count(*) from ( ' ||
6094: ' select del.delinquency_id, ' ||
6095: ' del.transaction_id, ' ||
6096: ' del.payment_schedule_id ' ||
6097: ' from iex_delinquencies del ' ||
6098: ' ,ar_payment_schedules arp ' ||
6099: ' where ' ||
6100: ' del.payment_schedule_id = arp.payment_schedule_id and ' ||
6101: ' del.status in (''DELINQUENT'',''PREDELINQUENT'') ' ||

Line 6139: 'and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ;

6135: 'and arp.status = ''OP'' ' ||
6136: 'and arp.amount_due_remaining <> 0 ' ||--' and arp.amount_due_remaining > 0 ' || Changed to fix 12552027 by snuthala 5/19/2011
6137: 'and arp.class = ''INV'' ' ||
6138: 'and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
6139: 'and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ;
6140: /*vPLSQL2 := ' union all' || --Added for Bug 10401991 07-Feb-2011 barathsr
6141: ' select del.delinquency_id, ' ||
6142: ' del.transaction_id, ' ||
6143: ' del.payment_schedule_id ' ||

Line 6144: ' from iex_delinquencies del ' ||

6140: /*vPLSQL2 := ' union all' || --Added for Bug 10401991 07-Feb-2011 barathsr
6141: ' select del.delinquency_id, ' ||
6142: ' del.transaction_id, ' ||
6143: ' del.payment_schedule_id ' ||
6144: ' from iex_delinquencies del ' ||
6145: ' ,ar_payment_schedules arp ' ||
6146: ' where ' ||
6147: ' del.payment_schedule_id = arp.payment_schedule_id and ' ||
6148: ' del.status = ''CURRENT'' ' ||

Line 6742: update iex_delinquencies del

6738:
6739: --Start bug 9696806 gnramasa 27th May 10
6740: if p_dunning_mode = 'DRAFT' then
6741: if (p_running_level = 'CUSTOMER') then
6742: update iex_delinquencies del
6743: set staged_dunning_level = 99
6744: where del.party_cust_id = l_object_id
6745: and staged_dunning_level = 98
6746: and status in ('DELINQUENT','PREDELINQUENT')

Line 6759: update iex_delinquencies del

6755: and dunn.delivery_status is null
6756: );
6757:
6758: elsif (p_running_level = 'ACCOUNT') then
6759: update iex_delinquencies del
6760: set staged_dunning_level = 99
6761: where del.cust_account_id = l_object_id
6762: and staged_dunning_level = 98
6763: and status in ('DELINQUENT','PREDELINQUENT')

Line 6776: update iex_delinquencies del

6772: and dunn.delivery_status is null
6773: );
6774:
6775: elsif (p_running_level = 'BILL_TO') then
6776: update iex_delinquencies del
6777: set staged_dunning_level = 99
6778: where del.customer_site_use_id = l_object_id
6779: and staged_dunning_level = 98
6780: and status in ('DELINQUENT','PREDELINQUENT')

Line 6903: FROM IEX_DELINQUENCIES

6899: party_cust_id,
6900: cust_account_id,
6901: customer_site_use_id,
6902: score_value
6903: FROM IEX_DELINQUENCIES
6904: WHERE delinquency_ID = in_del_ID;
6905: --
6906: CURSOR C_GET_SCORE (IN_ID NUMBER) IS
6907: SELECT a.score_value

Line 6976: --iex_delinquencies_all del

6972: cursor c_amount (IN_ID number) is
6973: select nvl(ps.amount_due_remaining,0),
6974: nvl(ps.invoice_currency_code,'USD')
6975: from ar_payment_schedules_all ps,
6976: --iex_delinquencies_all del
6977: iex_delinquencies del
6978: where ps.payment_schedule_id (+)= del.payment_schedule_id
6979: and del.delinquency_id = in_id;
6980: --

Line 6977: iex_delinquencies del

6973: select nvl(ps.amount_due_remaining,0),
6974: nvl(ps.invoice_currency_code,'USD')
6975: from ar_payment_schedules_all ps,
6976: --iex_delinquencies_all del
6977: iex_delinquencies del
6978: where ps.payment_schedule_id (+)= del.payment_schedule_id
6979: and del.delinquency_id = in_id;
6980: --
6981: cursor c_dunning_plan_dtl (p_dunn_plan_id number) is

Line 7033: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE

7029: l_msg_data VARCHAR2(32767);
7030: errmsg VARCHAR2(32767);
7031: --
7032: nIdx NUMBER := 0;
7033: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE
7034: INDEX BY BINARY_INTEGER;
7035: Del_Tbl Del_ID_TBL_TYPE;
7036: l_bind_tbl IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL;
7037: l_bind_rec IEX_DUNNING_PVT.FULFILLMENT_BIND_REC;

Line 7154: from iex_delinquencies_all

7150: FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_del_object_type: '|| l_del_object_type);
7151:
7152: if l_del_object_type = 'IEX_INVOICES' then
7153: select payment_schedule_id into l_del_object_id
7154: from iex_delinquencies_all
7155: where delinquency_id = l_delinquency_id;
7156: elsif l_del_object_type = 'IEX_DELINQUENCY' then
7157: l_del_object_id := l_delinquency_id;
7158: end if;

Line 7191: FROM ar_payment_schedules_all ps, iex_delinquencies_all del

7187: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - '||l_score);
7188: -- Start for the bug#8408162 by PNAVEENK
7189: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' Start Fully Promise Check');
7190: SELECT count(1) into l_fully_promised
7191: FROM ar_payment_schedules_all ps, iex_delinquencies_all del
7192: WHERE del.delinquency_id= l_delinquency_id
7193: AND ps.payment_schedule_id = del.payment_schedule_id
7194: AND ps.status = 'OP'
7195: AND del.status IN ('DELINQUENT', 'PREDELINQUENT')

Line 7220: from iex_delinquencies d

7216: if l_dun_disputed_items = 'N' then
7217:
7218: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' Start Dispute Check');
7219: select sum(a.amount_in_dispute) - sum(a.amount_due_remaining) into l_dispute_amount
7220: from iex_delinquencies d
7221: ,ar_payment_schedules a
7222: where a.payment_schedule_id = d.payment_schedule_id
7223: and d.delinquency_id = l_delinquency_id;
7224:

Line 7949: FROM IEX_DELINQUENCIES

7945: party_cust_id,
7946: cust_account_id,
7947: customer_site_use_id,
7948: score_value
7949: FROM IEX_DELINQUENCIES
7950: WHERE delinquency_ID = in_del_ID;
7951: --
7952: CURSOR C_GET_SCORE (IN_ID NUMBER) IS
7953: SELECT a.score_value

Line 8022: --iex_delinquencies_all del

8018: cursor c_amount (IN_ID number) is
8019: select ps.amount_due_remaining,
8020: ps.invoice_currency_code
8021: from ar_payment_schedules_all ps,
8022: --iex_delinquencies_all del
8023: iex_delinquencies del
8024: where ps.payment_schedule_id (+)= del.payment_schedule_id
8025: and del.delinquency_id = in_id;
8026: --

Line 8023: iex_delinquencies del

8019: select ps.amount_due_remaining,
8020: ps.invoice_currency_code
8021: from ar_payment_schedules_all ps,
8022: --iex_delinquencies_all del
8023: iex_delinquencies del
8024: where ps.payment_schedule_id (+)= del.payment_schedule_id
8025: and del.delinquency_id = in_id;
8026: --
8027:

Line 8057: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE

8053: l_msg_data VARCHAR2(32767);
8054: errmsg VARCHAR2(32767);
8055: --
8056: nIdx NUMBER := 0;
8057: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE
8058: INDEX BY BINARY_INTEGER;
8059: Del_Tbl Del_ID_TBL_TYPE;
8060: l_bind_tbl IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL;
8061: l_bind_rec IEX_DUNNING_PVT.FULFILLMENT_BIND_REC;

Line 8134: from iex_delinquencies del,

8130: arp.amount_due_remaining,
8131: del.staged_dunning_level,
8132: del.transaction_id,
8133: del.payment_schedule_id
8134: from iex_delinquencies del,
8135: ar_payment_schedules arp
8136: where del.delinquency_id = p_delinquency_id
8137: and del.payment_schedule_id = arp.payment_schedule_id;
8138:

Line 8164: from iex_delinquencies del,

8160: select count(*) from (
8161: select del.delinquency_id,
8162: del.transaction_id,
8163: del.payment_schedule_id
8164: from iex_delinquencies del,
8165: ar_payment_schedules arp
8166: where del.payment_schedule_id = arp.payment_schedule_id
8167: and del.status in ('DELINQUENT','PREDELINQUENT')
8168: and del.delinquency_id = p_del_id

Line 8178: from iex_delinquencies del,

8174: union
8175: select del.delinquency_id,
8176: del.transaction_id,
8177: del.payment_schedule_id
8178: from iex_delinquencies del,
8179: ar_payment_schedules arp
8180: where del.payment_schedule_id = arp.payment_schedule_id
8181: and del.status = 'CURRENT'
8182: and del.delinquency_id = p_del_id

Line 8198: from iex_delinquencies del

8194: select count(*) from (
8195: select del.delinquency_id,
8196: del.transaction_id,
8197: del.payment_schedule_id
8198: from iex_delinquencies del
8199: ,ar_payment_schedules arp
8200: where
8201: del.payment_schedule_id = arp.payment_schedule_id and
8202: del.status in ('DELINQUENT','PREDELINQUENT')

Line 8231: from iex_delinquencies del

8227: union
8228: select del.delinquency_id,
8229: del.transaction_id,
8230: del.payment_schedule_id
8231: from iex_delinquencies del
8232: ,ar_payment_schedules arp
8233: where
8234: del.payment_schedule_id = arp.payment_schedule_id and
8235: del.status = 'CURRENT'

Line 8364: from iex_delinquencies_all

8360: FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_del_object_type: '|| l_del_object_type);
8361:
8362: if l_del_object_type = 'IEX_INVOICES' then
8363: select payment_schedule_id into l_del_object_id
8364: from iex_delinquencies_all
8365: where delinquency_id = l_delinquency_id;
8366: elsif l_del_object_type = 'IEX_DELINQUENCY' then
8367: l_del_object_id := l_delinquency_id;
8368: end if;

Line 8449: update iex_delinquencies del

8445: fetch c_min_days_between_dunn_99 into l_min_days_between_dunn_99;
8446: close c_min_days_between_dunn_99;
8447: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_min_days_between_dunn_99 :'|| l_min_days_between_dunn_99);
8448:
8449: update iex_delinquencies del
8450: set staged_dunning_level = 98
8451: where delinquency_id = l_delinquency_id
8452: and staged_dunning_level = 99
8453: and status in ('DELINQUENT','PREDELINQUENT')

Line 8863: update iex_delinquencies_all

8859: END IF;
8860:
8861: /*
8862: if p_dunning_mode <> 'DRAFT' then
8863: update iex_delinquencies_all
8864: set staged_dunning_level = l_stage
8865: where delinquency_id = l_delinquency_id;
8866: end if;
8867: */

Line 9044: update iex_delinquencies del

9040: end loop;
9041:
9042: --Start bug 9696806 gnramasa 27th May 10
9043: if p_dunning_mode = 'DRAFT' then
9044: update iex_delinquencies del
9045: set staged_dunning_level = 99
9046: where delinquency_id = l_delinquency_id
9047: and staged_dunning_level = 98
9048: and status in ('DELINQUENT','PREDELINQUENT')

Line 9140: FROM IEX_DELINQUENCIES

9136:
9137: IS
9138: CURSOR C_GET_DEL (IN_del_ID NUMBER) IS
9139: SELECT delinquency_ID
9140: FROM IEX_DELINQUENCIES
9141: WHERE delinquency_ID = in_del_ID;
9142: --
9143: -- begin bug 4914799 ctlee 12/30/2005 add p_dunning_plan_id
9144: CURSOR C_GET_SCORE (IN_ID NUMBER, IN_CODE VARCHAR2, p_dunning_plan_id number) IS

Line 9204: from ar_payment_schedules_all aps,iex_delinquencies_all dd,hz_cust_accounts hzca

9200: where dunning_id = p_dunning_id;
9201:
9202: cursor c_cust_acct_amt_due_rem(p_party_id number,p_org_id number, p_corr_date date, p_grace_days number, p_include_dis_items varchar) is
9203: select nvl(sum(aps.acctd_amount_due_remaining),0)
9204: from ar_payment_schedules_all aps,iex_delinquencies_all dd,hz_cust_accounts hzca
9205: where dd.payment_schedule_id = aps.payment_schedule_id
9206: and aps.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP')
9207: and aps.status='OP'
9208: and (trunc(aps.due_date) + p_grace_days) <= p_corr_date

Line 9254: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE

9250: l_msg_data VARCHAR2(32767);
9251: errmsg VARCHAR2(32767);
9252: --
9253: nIdx NUMBER := 0;
9254: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE
9255: INDEX BY BINARY_INTEGER;
9256: Del_Tbl Del_ID_TBL_TYPE;
9257: l_bind_tbl IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL;
9258: l_bind_rec IEX_DUNNING_PVT.FULFILLMENT_BIND_REC;

Line 9704: ' from iex_delinquencies del, ' ||

9700: vPLSQL1 := 'select count(*) from ( ' ||
9701: ' select del.delinquency_id, ' ||
9702: ' del.transaction_id, ' ||
9703: ' del.payment_schedule_id ' ||
9704: ' from iex_delinquencies del, ' ||
9705: ' ar_payment_schedules arp ' ||
9706: ' where del.payment_schedule_id = arp.payment_schedule_id ' ||
9707: ' and del.status in (''DELINQUENT'',''PREDELINQUENT'') ' ||
9708: ' and (trunc(arp.due_date) + :p_grace_days) <= :p_corr_date ' ||

Line 9723: 'and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ;

9719: 'and arp.status = ''OP'' ' ||
9720: 'and arp.amount_due_remaining <> 0 ' ||--' and arp.amount_due_remaining > 0 ' || Changed to fix 12552027 by snuthala 5/19/2011
9721: 'and arp.class = ''INV'' ' ||
9722: 'and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
9723: 'and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ;
9724: else
9725: vPLSQL2 := ' ';
9726: end if;
9727: if (p_running_level = 'CUSTOMER') then

Line 10221: FROM IEX_DELINQUENCIES

10217: IS
10218: /*
10219: CURSOR C_GET_DEL (IN_del_ID NUMBER) IS
10220: SELECT delinquency_ID
10221: FROM IEX_DELINQUENCIES
10222: WHERE delinquency_ID = in_del_ID;
10223: --
10224: -- begin bug 4914799 ctlee 12/30/2005 add p_dunning_plan_id
10225: CURSOR C_GET_SCORE (IN_ID NUMBER, IN_CODE VARCHAR2, p_dunning_plan_id number) IS

Line 10301: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE

10297: l_msg_data VARCHAR2(32767);
10298: errmsg VARCHAR2(32767);
10299: --
10300: nIdx NUMBER := 0;
10301: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE
10302: INDEX BY BINARY_INTEGER;
10303: Del_Tbl Del_ID_TBL_TYPE;
10304: l_bind_tbl IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL;
10305: l_bind_rec IEX_DUNNING_PVT.FULFILLMENT_BIND_REC;

Line 11176: FROM IEX_DELINQUENCIES

11172: party_cust_id,
11173: cust_account_id,
11174: customer_site_use_id,
11175: score_value
11176: FROM IEX_DELINQUENCIES
11177: WHERE delinquency_ID = in_del_ID;
11178: --
11179: CURSOR C_GET_SCORE (IN_ID NUMBER) IS
11180: SELECT a.score_value

Line 11229: --iex_delinquencies_all del

11225: cursor c_amount (IN_ID number) is
11226: select ps.amount_due_remaining,
11227: ps.invoice_currency_code
11228: from ar_payment_schedules_all ps,
11229: --iex_delinquencies_all del
11230: iex_delinquencies del
11231: where ps.payment_schedule_id (+)= del.payment_schedule_id
11232: and del.delinquency_id = in_id;
11233: --

Line 11230: iex_delinquencies del

11226: select ps.amount_due_remaining,
11227: ps.invoice_currency_code
11228: from ar_payment_schedules_all ps,
11229: --iex_delinquencies_all del
11230: iex_delinquencies del
11231: where ps.payment_schedule_id (+)= del.payment_schedule_id
11232: and del.delinquency_id = in_id;
11233: --
11234: cursor c_dunning_plan_dtl (p_dunn_plan_id number) is

Line 11267: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE

11263: l_msg_data VARCHAR2(32767);
11264: errmsg VARCHAR2(32767);
11265: --
11266: nIdx NUMBER := 0;
11267: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE
11268: INDEX BY BINARY_INTEGER;
11269: Del_Tbl Del_ID_TBL_TYPE;
11270: l_bind_tbl IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL;
11271: l_bind_rec IEX_DUNNING_PVT.FULFILLMENT_BIND_REC;

Line 11967: FROM IEX_DELINQUENCIES

11963: party_cust_id,
11964: cust_account_id,
11965: customer_site_use_id,
11966: score_value
11967: FROM IEX_DELINQUENCIES
11968: WHERE delinquency_ID = in_del_ID;
11969: /*
11970: --
11971: CURSOR C_GET_SCORE (IN_ID NUMBER) IS

Line 12022: --iex_delinquencies_all del

12018: cursor c_amount (IN_ID number) is
12019: select ps.amount_due_remaining,
12020: ps.invoice_currency_code
12021: from ar_payment_schedules_all ps,
12022: --iex_delinquencies_all del
12023: iex_delinquencies del
12024: where ps.payment_schedule_id (+)= del.payment_schedule_id
12025: and del.delinquency_id = in_id;
12026: --

Line 12023: iex_delinquencies del

12019: select ps.amount_due_remaining,
12020: ps.invoice_currency_code
12021: from ar_payment_schedules_all ps,
12022: --iex_delinquencies_all del
12023: iex_delinquencies del
12024: where ps.payment_schedule_id (+)= del.payment_schedule_id
12025: and del.delinquency_id = in_id;
12026: --
12027:

Line 12055: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE

12051: l_msg_data VARCHAR2(32767);
12052: errmsg VARCHAR2(32767);
12053: --
12054: nIdx NUMBER := 0;
12055: TYPE Del_ID_TBL_type is Table of IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE
12056: INDEX BY BINARY_INTEGER;
12057: Del_Tbl Del_ID_TBL_TYPE;
12058: l_bind_tbl IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL;
12059: l_bind_rec IEX_DUNNING_PVT.FULFILLMENT_BIND_REC;

Line 12757: in send_dunning, aging_bucket_line_id is not from iex_delinquencies;

12753: END Resend_Staged_Dunning;
12754:
12755: /* clchang added (for 11.5.9)
12756: no iex aging in 11.5.9;
12757: in send_dunning, aging_bucket_line_id is not from iex_delinquencies;
12758: we need to get by ourselves;
12759:
12760: updated for 11.5.11 - 11/22/04
12761: dunning support aging in 11.5.11 at all levels.

Line 12785: FROM iex_delinquencies del,

12781: -- begin bug 9393778 gnramasa 19th Feb 10, add trunc function
12782: CURSOR C_GET_PARTY_DAYS (in_party_id number) IS
12783: --SELECT max(sysdate - ar.due_date) days
12784: SELECT max(trunc(sysdate) - trunc(ar.due_date)) days
12785: FROM iex_delinquencies del,
12786: ar_payment_schedules ar
12787: WHERE del.party_cust_id = in_party_id
12788: AND del.payment_schedule_id = ar.payment_schedule_id
12789: and del.status in ('DELINQUENT', 'PREDELINQUENT');

Line 12794: FROM iex_delinquencies del,

12790: --
12791: CURSOR C_GET_ACCT_DAYS (in_acct_id number) IS
12792: --SELECT max(sysdate - ar.due_date) days
12793: SELECT max(trunc(sysdate) - trunc(ar.due_date)) days
12794: FROM iex_delinquencies del,
12795: ar_payment_schedules ar
12796: WHERE del.cust_account_id = in_acct_id
12797: AND del.payment_schedule_id = ar.payment_schedule_id
12798: and del.status in ('DELINQUENT', 'PREDELINQUENT');

Line 12803: FROM iex_delinquencies del,

12799: --
12800: CURSOR C_GET_SITE_DAYS (in_site_id number) IS
12801: --SELECT max(sysdate - ar.due_date) days
12802: SELECT max(trunc(sysdate) - trunc(ar.due_date)) days
12803: FROM iex_delinquencies del,
12804: ar_payment_schedules ar
12805: WHERE del.customer_site_use_id = in_site_id
12806: AND del.payment_schedule_id = ar.payment_schedule_id
12807: and del.status in ('DELINQUENT', 'PREDELINQUENT');

Line 12812: FROM iex_delinquencies del,

12808: --
12809: CURSOR C_GET_DAYS (IN_DEL_ID NUMBER) IS
12810: --SELECT (sysdate - ar.due_date) days
12811: SELECT (trunc(sysdate) - trunc(ar.due_date)) days
12812: FROM iex_delinquencies del,
12813: ar_payment_schedules ar
12814: WHERE delinquency_ID = in_DEL_ID
12815: AND del.payment_schedule_id = ar.payment_schedule_id
12816: and del.status in ('DELINQUENT', 'PREDELINQUENT');

Line 16279: FROM IEX_DELINQUENCIES d

16275: --End adding for bug 8489610 by gnramasa 14-May-09
16276: --
16277: CURSOR C_CHK_DEL (IN_del_ID NUMBER) IS
16278: SELECT 1
16279: FROM IEX_DELINQUENCIES d
16280: WHERE d.delinquency_ID = in_del_ID
16281: AND STATUS in ('DELINQUENT', 'PREDELINQUENT');
16282: --
16283: CURSOR C_CHK_ACCOUNT (IN_ACCT_ID NUMBER) IS

Line 16285: FROM IEX_DELINQUENCIES d

16281: AND STATUS in ('DELINQUENT', 'PREDELINQUENT');
16282: --
16283: CURSOR C_CHK_ACCOUNT (IN_ACCT_ID NUMBER) IS
16284: SELECT 1
16285: FROM IEX_DELINQUENCIES d
16286: WHERE d.cust_account_ID = in_ACCT_ID
16287: AND STATUS in ('DELINQUENT', 'PREDELINQUENT');
16288: --
16289: CURSOR C_CHK_CUSTOMER (IN_PARTY_ID NUMBER) IS

Line 16291: FROM IEX_DELINQUENCIES d

16287: AND STATUS in ('DELINQUENT', 'PREDELINQUENT');
16288: --
16289: CURSOR C_CHK_CUSTOMER (IN_PARTY_ID NUMBER) IS
16290: SELECT 1
16291: FROM IEX_DELINQUENCIES d
16292: WHERE d.party_cust_id = in_party_id
16293: AND STATUS in ('DELINQUENT', 'PREDELINQUENT');
16294: --
16295: CURSOR C_CHK_SITE (IN_SITE_ID NUMBER) IS

Line 16297: FROM IEX_DELINQUENCIES d

16293: AND STATUS in ('DELINQUENT', 'PREDELINQUENT');
16294: --
16295: CURSOR C_CHK_SITE (IN_SITE_ID NUMBER) IS
16296: SELECT 1
16297: FROM IEX_DELINQUENCIES d
16298: WHERE d.customer_site_use_id = in_SITE_ID
16299: AND STATUS in ('DELINQUENT', 'PREDELINQUENT');
16300: --
16301: l_DUNNING_id NUMBER;

Line 16581: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '

16577: ||' and ref.object_id=dun.dunning_id'
16578: ||' and dun.dunning_level='''||p_running_level||'''';
16579:
16580: IF (p_running_level = 'CUSTOMER') THEN
16581: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
16582: ||' where del.status in (''DELINQUENT'',''PREDELINQUENT'') '
16583: ||' and dun.dunning_object_id=del.party_cust_id)';
16584: ELSIF(p_running_level = 'ACCOUNT') THEN
16585: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '

Line 16585: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '

16581: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
16582: ||' where del.status in (''DELINQUENT'',''PREDELINQUENT'') '
16583: ||' and dun.dunning_object_id=del.party_cust_id)';
16584: ELSIF(p_running_level = 'ACCOUNT') THEN
16585: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
16586: ||' where del.status in (''DELINQUENT'',''PREDELINQUENT'') '
16587: ||' and dun.dunning_object_id=del.cust_account_id)';
16588: ELSIF(p_running_level = 'BILL_TO') THEN
16589: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '

Line 16589: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '

16585: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
16586: ||' where del.status in (''DELINQUENT'',''PREDELINQUENT'') '
16587: ||' and dun.dunning_object_id=del.cust_account_id)';
16588: ELSIF(p_running_level = 'BILL_TO') THEN
16589: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
16590: ||' where del.status in (''DELINQUENT'',''PREDELINQUENT'') '
16591: ||' and dun.dunning_object_id=del.customer_site_use_id)';
16592: ELSE
16593: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '

Line 16593: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '

16589: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
16590: ||' where del.status in (''DELINQUENT'',''PREDELINQUENT'') '
16591: ||' and dun.dunning_object_id=del.customer_site_use_id)';
16592: ELSE
16593: l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
16594: ||' where del.status in (''DELINQUENT'',''PREDELINQUENT'') '
16595: ||' and dun.dunning_object_id=del.delinquency_id)';
16596: END IF;
16597: WriteLog(G_PKG_NAME || ' ' || l_api_name || 'Query: '||l_task_query);

Line 16695: FROM IEX_DELINQUENCIES d,

16691: d.customer_site_use_id,
16692: ar.trx_number,
16693: ar.payment_schedule_id,
16694: h.party_name
16695: FROM IEX_DELINQUENCIES d,
16696: HZ_PARTIES h,
16697: HZ_CUST_ACCOUNTS a,
16698: AR_PAYMENT_SCHEDULES ar
16699: WHERE d.delinquency_ID = in_del_ID

Line 16712: FROM IEX_DELINQUENCIES d,

16708: a.account_number,
16709: d.customer_site_use_id,
16710: h.party_name,
16711: u.location -- added for bug 14738490
16712: FROM IEX_DELINQUENCIES d,
16713: HZ_PARTIES h,
16714: HZ_CUST_ACCOUNTS a,
16715: HZ_CUST_SITE_USES u -- -- added for bug 14738490
16716: WHERE d.customer_site_use_id = in_SITE_ID

Line 16726: FROM IEX_DELINQUENCIES d,

16722: SELECT d.party_cust_id,
16723: d.cust_account_id,
16724: a.account_number,
16725: h.party_name
16726: FROM IEX_DELINQUENCIES d,
16727: HZ_PARTIES h,
16728: HZ_CUST_ACCOUNTS a
16729: WHERE d.cust_account_ID = in_ACCT_ID
16730: AND h.party_id = d.party_cust_id

Line 16736: FROM IEX_DELINQUENCIES d,

16732: --
16733: CURSOR C_GET_CUSTOMER (IN_PARTY_ID NUMBER) IS
16734: SELECT d.party_cust_id,
16735: h.party_name
16736: FROM IEX_DELINQUENCIES d,
16737: HZ_PARTIES h
16738: WHERE d.party_cust_id = in_party_id
16739: AND h.party_id = d.party_cust_id;
16740: --

Line 17540: iex_delinquencies del

17536: IS
17537: cursor c_amount (IN_ID number) is
17538: select ps.invoice_currency_code
17539: from ar_payment_schedules_all ps,
17540: iex_delinquencies del
17541: where ps.payment_schedule_id (+)= del.payment_schedule_id
17542: and del.party_cust_id = in_id;
17543: l_code VARCHAR2(15);
17544: BEGIN

Line 17566: --iex_delinquencies_all del

17562: IS
17563: cursor c_amount (IN_ID number) is
17564: select ps.invoice_currency_code
17565: from ar_payment_schedules_all ps,
17566: --iex_delinquencies_all del
17567: iex_delinquencies del
17568: where ps.payment_schedule_id (+)= del.payment_schedule_id
17569: and del.cust_account_id = in_id;
17570: l_code VARCHAR2(15);

Line 17567: iex_delinquencies del

17563: cursor c_amount (IN_ID number) is
17564: select ps.invoice_currency_code
17565: from ar_payment_schedules_all ps,
17566: --iex_delinquencies_all del
17567: iex_delinquencies del
17568: where ps.payment_schedule_id (+)= del.payment_schedule_id
17569: and del.cust_account_id = in_id;
17570: l_code VARCHAR2(15);
17571: BEGIN

Line 17593: iex_delinquencies del

17589: IS
17590: cursor c_amount (IN_ID number) is
17591: select ps.invoice_currency_code
17592: from ar_payment_schedules_all ps,
17593: iex_delinquencies del
17594: where ps.payment_schedule_id (+)= del.payment_schedule_id
17595: and del.customer_site_use_id = in_id;
17596:
17597: l_code VARCHAR2(15);

Line 17621: iex_delinquencies del

17617: IS
17618: cursor c_amount (IN_ID number) is
17619: select sum(ps.acctd_amount_due_remaining)
17620: from ar_payment_schedules_all ps,
17621: iex_delinquencies del
17622: where ps.payment_schedule_id (+)= del.payment_schedule_id
17623: and del.party_cust_id = in_id;
17624: l_sum number;
17625: BEGIN

Line 17647: --iex_delinquencies_all del

17643: IS
17644: cursor c_amount (IN_ID number) is
17645: select sum(ps.acctd_amount_due_remaining)
17646: from ar_payment_schedules_all ps,
17647: --iex_delinquencies_all del
17648: iex_delinquencies del
17649: where ps.payment_schedule_id (+)= del.payment_schedule_id
17650: and del.cust_account_id = in_id;
17651: l_sum number;

Line 17648: iex_delinquencies del

17644: cursor c_amount (IN_ID number) is
17645: select sum(ps.acctd_amount_due_remaining)
17646: from ar_payment_schedules_all ps,
17647: --iex_delinquencies_all del
17648: iex_delinquencies del
17649: where ps.payment_schedule_id (+)= del.payment_schedule_id
17650: and del.cust_account_id = in_id;
17651: l_sum number;
17652: BEGIN

Line 17674: iex_delinquencies del

17670: IS
17671: cursor c_amount (IN_ID number) is
17672: select sum(ps.acctd_amount_due_remaining)
17673: from ar_payment_schedules_all ps,
17674: iex_delinquencies del
17675: where ps.payment_schedule_id (+)= del.payment_schedule_id
17676: and del.customer_site_use_id = in_id;
17677: l_sum number;
17678: --

Line 17730: from iex_delinquencies del

17726: FUNCTION get_party_id(p_account_id NUMBER) RETURN NUMBER
17727: IS
17728: cursor c_party (IN_ID number) is
17729: select del.party_cust_id
17730: from iex_delinquencies del
17731: where del.cust_account_id = in_id;
17732: l_party number;
17733: BEGIN
17734: WriteLog('get_party_id');

Line 17984: 'iex_delinquencies_all del, ' ||

17980: 'hz_parties p, ' ||
17981: 'iex_dunnings id, ' ||
17982: 'XDO_TEMPLATES_B xtb, ' ||
17983: 'XDO_TEMPLATES_TL xtvl, ' ||
17984: 'iex_delinquencies_all del, ' ||
17985: 'ar_payment_schedules_all aps ' ||
17986: 'where id.dunning_object_id = del.delinquency_id ' ||
17987: 'and del.payment_Schedule_id = aps.payment_Schedule_id ' ||
17988: 'and del.party_cust_id = p.party_id ' ||

Line 18248: FROM IEX_DELINQUENCIES ca

18244:
18245: Elsif l_object_type = 'IEX_DELINQUENCY' then
18246: l_source_id := l_object_id;
18247: SELECT ca.PARTY_CUST_ID into l_party_id
18248: FROM IEX_DELINQUENCIES ca
18249: WHERE ca.DELINQUENCY_ID = l_object_id;
18250:
18251: Elsif l_object_type = 'IEX_ACCOUNT' then
18252: l_source_id := l_object_id;

Line 18514: ' from iex_delinquencies del, ' ||

18510:
18511: vPLSQL := 'select del.delinquency_id, ' ||
18512: ' del.transaction_id, ' ||
18513: ' del.payment_schedule_id ' ||
18514: ' from iex_delinquencies del, ' ||
18515: ' ar_payment_schedules arp ' ||
18516: ' where del.payment_schedule_id = arp.payment_schedule_id ' ||
18517: ' and del.status in (''DELINQUENT'',''PREDELINQUENT'') ' ||
18518: -- ' and del.staged_dunning_level is NULL ' || commented to fix 12621875 snuthala 6/6/2011

Line 18601: ' from iex_delinquencies del, ' ||

18597:
18598: vPLSQL := 'select del.delinquency_id, ' ||
18599: ' del.transaction_id, ' ||
18600: ' del.payment_schedule_id ' ||
18601: ' from iex_delinquencies del, ' ||
18602: ' ar_payment_schedules arp ' ||
18603: ' where del.payment_schedule_id = arp.payment_schedule_id ' ||
18604: ' and del.status in (''DELINQUENT'',''PREDELINQUENT'') ' ||
18605: --' and del.staged_dunning_level is NULL ' || commented to fix 12621875 snuthala 6/6/2011

Line 18695: ' from iex_delinquencies del, ar_payment_schedules arp ' ||

18691: vPLSQL1 :=
18692: 'select del.delinquency_id, ' ||
18693: ' del.transaction_id, ' ||
18694: ' del.payment_schedule_id ' ||
18695: ' from iex_delinquencies del, ar_payment_schedules arp ' ||
18696: ' where del.payment_schedule_id = arp.payment_schedule_id ' ||
18697: ' and del.status = ''CURRENT'' ' ||
18698: -- ' and del.staged_dunning_level is NULL ' || -- commented for bug#15932721 schekuri 30-Nov-12
18699: ' and arp.status = ''OP'' ' ||

Line 18786: ' and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ||

18782: ' and arp.amount_due_remaining <> 0 ' ||
18783: --' and arp.class = ''INV'' ' || -- Bills Receivables
18784: ' and (arp.class = ''INV'' or arp.class = ''BR'') ' || -- Bills Receivables
18785: ' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
18786: ' and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ||
18787: ' and not exists (select 1 from iex_dunning_transactions dun_trx where dun_trx.payment_schedule_id = arp.payment_schedule_id and dun_trx.dunning_id = :p_dunning_id ) ' || -- added by a bug 15932721
18788: ' order by arp.payment_schedule_id';
18789: elsif (p_running_level = 'ACCOUNT') then
18790: vPLSQL2 := 'select arp.customer_trx_id, ' ||

Line 18800: ' and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ||

18796: ' and arp.amount_due_remaining <> 0 ' ||
18797: --' and arp.class = ''INV'' ' || -- Bills Receivables
18798: ' and (arp.class = ''INV'' or arp.class = ''BR'') ' || -- Bills Receivables
18799: ' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
18800: ' and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ||
18801: ' and not exists (select 1 from iex_dunning_transactions dun_trx where dun_trx.payment_schedule_id = arp.payment_schedule_id and dun_trx.dunning_id = :p_dunning_id ) ' || -- added by a bug 15932721
18802: ' order by arp.payment_schedule_id';
18803: elsif (p_running_level = 'BILL_TO') then
18804: vPLSQL2 := 'select arp.customer_trx_id, ' ||

Line 18814: ' and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ||

18810: ' and arp.amount_due_remaining <> 0 ' ||
18811: --' and arp.class = ''INV'' ' || -- Bills Receivables
18812: ' and (arp.class = ''INV'' or arp.class = ''BR'') ' || -- Bills Receivables
18813: ' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
18814: ' and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ||
18815: ' and not exists (select 1 from iex_dunning_transactions dun_trx where dun_trx.payment_schedule_id = arp.payment_schedule_id and dun_trx.dunning_id = :p_dunning_id ) ' || -- added by a bug 15932721
18816: ' order by arp.payment_schedule_id';
18817:
18818: end if;