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 AR_PAYMENT_SCHEDULES

Line 1535: from ar_payment_schedules_all

1531:
1532: --Added for bug 9550221 gnramasa 20th Apr 2010
1533: cursor c_get_acc_info_from_inv(p_cust_trx_id number) is
1534: select customer_id
1535: from ar_payment_schedules_all
1536: where customer_trx_id = p_cust_trx_id;
1537:
1538: begin
1539:

Line 2809: ' ar_payment_schedules arp ' ||

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 ' ||
2813: ' and arp.invoice_currency_code = :p_inv_curr ' ||

Line 2913: ' ar_payment_schedules arp ' ||

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 ' ||
2917: ' and arp.status = ''OP'' ' ||

Line 2989: ' ,ar_payment_schedules arp ' ||

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'') ' ||
2993: ' and del.staged_dunning_level = :p_stage_no ' ||

Line 3099: ' ,ar_payment_schedules arp ' ||

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'' ' ||
3103: ' and del.staged_dunning_level = :p_stage_no ' ||

Line 3197: ' from ar_payment_schedules arp, ' ||

3193:
3194: if (p_running_level = 'CUSTOMER') then
3195: vPLSQL2 := 'select arp.customer_trx_id, ' ||
3196: ' arp.payment_schedule_id ' ||
3197: ' from ar_payment_schedules arp, ' ||
3198: ' hz_cust_accounts hca ' ||
3199: ' where arp.customer_id = hca.cust_account_id ' ||
3200: ' and hca.party_id = :p_party_id ' ||
3201: ' and trunc(arp.due_date) > trunc(:p_corr_date) ' ||

Line 3211: ' from ar_payment_schedules arp ' ||

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 ' ||
3211: ' from ar_payment_schedules arp ' ||
3212: ' where arp.customer_id = :p_cust_acct_id ' ||
3213: ' and trunc(arp.due_date) > trunc(:p_corr_date) ' ||
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

Line 3223: ' from ar_payment_schedules arp ' ||

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 ' ||
3223: ' from ar_payment_schedules arp ' ||
3224: -- ' where arp.customer_site_use_id = :p_site_use_id ' || -- Bills Receivables
3225: ' where arp.customer_site_use_id IN ( :p_site_use_id, '||t_id||')' || -- Bills Receivables
3226: ' and trunc(arp.due_date) > trunc(:p_corr_date) ' ||
3227: ' and arp.status = ''OP'' ' ||

Line 3288: ' ar_payment_schedules arp ' ||

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 ' ||
3292: ' and (trunc(arp.due_date) + :p_min_days_bw_dun) > :p_corr_date ' ||

Line 3394: ' from ar_payment_schedules arp, ' ||

3390: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Start insert unapplied receipts');
3391:
3392: if (p_running_level = 'CUSTOMER') then
3393: vPLSQL2 := 'select arp.payment_schedule_id ' ||
3394: ' from ar_payment_schedules arp, ' ||
3395: ' hz_cust_accounts hca ' ||
3396: ' where arp.customer_id = hca.cust_account_id ' ||
3397: ' and hca.party_id = :p_party_id ' ||
3398: ' and arp.status = ''OP'' ' ||

Line 3404: ' from ar_payment_schedules arp ' ||

3400: ' and arp.class = ''PMT'' ' ||
3401: ' order by arp.payment_schedule_id';
3402: elsif (p_running_level = 'ACCOUNT') then
3403: vPLSQL2 := 'select arp.payment_schedule_id ' ||
3404: ' from ar_payment_schedules arp ' ||
3405: ' where arp.customer_id = :p_cust_acct_id ' ||
3406: ' and arp.status = ''OP'' ' ||
3407: ' and arp.amount_due_remaining <> 0 ' ||
3408: ' and arp.class = ''PMT'' ' ||

Line 3412: ' from ar_payment_schedules arp ' ||

3408: ' and arp.class = ''PMT'' ' ||
3409: ' order by arp.payment_schedule_id';
3410: elsif (p_running_level = 'BILL_TO') then
3411: vPLSQL2 := 'select arp.payment_schedule_id ' ||
3412: ' from ar_payment_schedules arp ' ||
3413: -- ' where arp.customer_site_use_id IN ( :p_site_use_id,24388) ' ||
3414: ' where arp.customer_site_use_id IN ( :p_site_use_id, '||t_id||')' || -- Bills Receivables
3415: ' and arp.status = ''OP'' ' ||
3416: ' and arp.amount_due_remaining <> 0 ' ||

Line 3465: ' from ar_payment_schedules arp, ' ||

3461:
3462: if (p_running_level = 'CUSTOMER') then
3463: vPLSQL2 := 'select arp.customer_trx_id customer_trx_id, ' ||
3464: ' arp.payment_schedule_id ' ||
3465: ' from ar_payment_schedules arp, ' ||
3466: ' hz_cust_accounts hca ' ||
3467: ' where arp.customer_id = hca.cust_account_id ' ||
3468: ' and hca.party_id = :p_party_id ' ||
3469: ' and arp.amount_due_remaining <> 0 ' ||

Line 3475: ' from ar_payment_schedules arp ' ||

3471: ' and arp.status=''OP'' ';
3472: elsif (p_running_level = 'ACCOUNT') then
3473: vPLSQL2 := 'select arp.customer_trx_id customer_trx_id, ' ||
3474: ' arp.payment_schedule_id ' ||
3475: ' from ar_payment_schedules arp ' ||
3476: ' where arp.customer_id = :p_cust_acct_id ' ||
3477: ' and arp.amount_due_remaining <> 0 ' ||
3478: ' and arp.class =''CM'' ' ||
3479: ' and arp.status=''OP'' ';

Line 3483: ' from ar_payment_schedules arp ' ||

3479: ' and arp.status=''OP'' ';
3480: elsif (p_running_level = 'BILL_TO') then
3481: vPLSQL2 := 'select arp.customer_trx_id customer_trx_id, ' ||
3482: ' arp.payment_schedule_id ' ||
3483: ' from ar_payment_schedules arp ' ||
3484: -- ' where arp.customer_site_use_id IN ( :p_site_use_id,24388) ' ||
3485: ' where arp.customer_site_use_id IN ( :p_site_use_id, '||t_id||')' || -- Bills Receivabless
3486: ' and arp.amount_due_remaining <> 0 ' ||
3487: ' and arp.class =''CM'' ' ||

Line 4055: ,ar_payment_schedules a

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 )
4059: and d.customer_site_use_id = nvl(p_site_use_id, d.customer_site_use_id )

Line 4065: ,ar_payment_schedules a

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');
4069:

Line 4073: ,ar_payment_schedules a

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');
4077:

Line 4081: ,ar_payment_schedules a

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');
4085:

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 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 4256: from ar_payment_schedules_all aps,hz_cust_accounts hzca

4252: and hzca.party_id = p_party_id;
4253:
4254: cursor c_cust_acct_cm_tot_amt_rem (p_party_id number,p_org_id number) is
4255: select nvl(sum(acctd_amount_due_remaining),0)
4256: from ar_payment_schedules_all aps,hz_cust_accounts hzca
4257: where aps.class IN ('CM','PMT')
4258: and aps.status = 'OP'
4259: and aps.customer_id = hzca.cust_account_id
4260: and aps.org_id= p_org_id

Line 4690: ' ar_payment_schedules arp ' ||

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 ' ||
4694: ' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ';

Line 4701: 'from ar_payment_schedules arp ' ||

4697: -- if l_include_current = 'y' then
4698: vPLSQL2 := ' union all ' ||
4699: 'select null, arp.customer_trx_id, ' ||
4700: 'arp.payment_schedule_id ' ||
4701: 'from ar_payment_schedules arp ' ||
4702: ', hz_cust_accounts hca ' || -- Bills Receivables
4703: 'where arp.customer_id = hca.cust_account_id ' || -- Bills Receivables
4704: 'and arp.status = ''OP'' ' || -- Bills Receivables
4705: -- 'where arp.status = ''OP'' ' || -- Bills Receivables

Line 6009: ' ar_payment_schedules arp ' ||

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 ' ||
6013: ' and (trunc(arp.due_date) + :p_min_days_bw_dun) <= :p_corr_date ' ||

Line 6022: 'from ar_payment_schedules arp, ' ||

6018: vPLSQL2 := ' ';
6019: /*vPLSQL2 := ' union all ' || --Added for Bug 10401991 07-Feb-2011 barathsr
6020: 'select null, arp.customer_trx_id, ' ||
6021: 'arp.payment_schedule_id ' ||
6022: 'from ar_payment_schedules arp, ' ||
6023: 'hz_cust_accounts hca ' ||
6024: 'where arp.customer_id = hca.cust_account_id ' ||
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

Line 6098: ' ,ar_payment_schedules arp ' ||

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'') ' ||
6102: ' and del.staged_dunning_level = :p_stage_no ' ||

Line 6132: 'from ar_payment_schedules arp, ' ||

6128:
6129: /*vPLSQL2 := ' union all ' || --Added for Bug 10401991 07-Feb-2011 barathsr
6130: 'select null, arp.customer_trx_id, ' ||
6131: 'arp.payment_schedule_id ' ||
6132: 'from ar_payment_schedules arp, ' ||
6133: 'hz_cust_accounts hca ' ||
6134: 'where arp.customer_id = hca.cust_account_id ' ||
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

Line 6145: ' ,ar_payment_schedules arp ' ||

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'' ' ||
6149: ' and del.staged_dunning_level = :p_stage_no ' ||

Line 6975: from ar_payment_schedules_all ps,

6971: --
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;

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 7221: ,ar_payment_schedules a

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:
7225: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_amount in dispute : ' || l_dispute_amount);

Line 8021: from ar_payment_schedules_all ps,

8017: --
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;

Line 8135: ar_payment_schedules arp

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:
8139: cursor c_dunningplan_lines (p_dunning_plan_id number, p_stage number, p_score number) is

Line 8165: ar_payment_schedules arp

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
8169: and del.staged_dunning_level is NULL

Line 8179: ar_payment_schedules arp

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
8183: and del.staged_dunning_level is NULL

Line 8199: ,ar_payment_schedules arp

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')
8203: and del.delinquency_id = p_del_id

Line 8232: ,ar_payment_schedules arp

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'
8236: and del.party_cust_id = p_party_id

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 9217: from ar_payment_schedules_all aps,hz_cust_accounts hzca

9213: and hzca.party_id = p_party_id;
9214:
9215: cursor c_cust_acct_cm_tot_amt_rem (p_party_id number,p_org_id number) is
9216: select nvl(sum(acctd_amount_due_remaining),0)
9217: from ar_payment_schedules_all aps,hz_cust_accounts hzca
9218: where aps.class IN ('CM','PMT')
9219: and aps.status = 'OP'
9220: and aps.customer_id = hzca.cust_account_id
9221: and aps.org_id= p_org_id

Line 9705: ' ar_payment_schedules arp ' ||

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 ' ||
9709: ' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ';

Line 9715: 'from ar_payment_schedules arp, ' ||

9711: if l_include_current = 'y' then
9712: vPLSQL2 := ' union all ' || --Added for Bug 10401991 07-Feb-2011 barathsr
9713: 'select null, arp.customer_trx_id, ' ||
9714: 'arp.payment_schedule_id ' ||
9715: 'from ar_payment_schedules arp, ' ||
9716: 'hz_cust_accounts hca ' ||
9717: 'where arp.customer_id = hca.cust_account_id ' ||
9718: 'and hca.party_id = :p_party_id ' ||
9719: 'and arp.status = ''OP'' ' ||

Line 11228: from ar_payment_schedules_all ps,

11224: --
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;

Line 12021: from ar_payment_schedules_all ps,

12017: --
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;

Line 12786: ar_payment_schedules ar

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');
12790: --

Line 12795: ar_payment_schedules ar

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');
12799: --

Line 12804: ar_payment_schedules ar

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');
12808: --

Line 12813: ar_payment_schedules ar

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');
12817: -- end bug 4914799, add max function and check del status

Line 14405: from ar_payment_schedules_all

14401: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - p_object_id: ' || p_object_id);
14402:
14403: select customer_trx_id, payment_schedule_id
14404: into l_transaction_id, l_payment_schedule_id
14405: from ar_payment_schedules_all
14406: where customer_trx_id = p_object_id;
14407: EXCEPTION
14408: WHEN OTHERS THEN
14409: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' SQL stmt throws exception ');

Line 14785: from ar_payment_schedules_all

14781: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - p_object_id: ' || p_object_id);
14782:
14783: select customer_trx_id, payment_schedule_id
14784: into l_transaction_id, l_payment_schedule_id
14785: from ar_payment_schedules_all
14786: where customer_trx_id = p_object_id;
14787: EXCEPTION
14788: WHEN OTHERS THEN
14789: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' SQL stmt throws exception ');

Line 16698: AR_PAYMENT_SCHEDULES ar

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
16700: AND h.party_id = d.party_cust_id
16701: AND d.cust_account_id = a.cust_account_id
16702: AND ar.payment_schedule_id = d.payment_schedule_id

Line 17539: from ar_payment_schedules_all ps,

17535: FUNCTION Party_currency_code(p_party_id NUMBER) RETURN VARCHAR2
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);

Line 17565: from ar_payment_schedules_all ps,

17561: FUNCTION acct_currency_code(p_account_id NUMBER) RETURN VARCHAR2
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;

Line 17592: from ar_payment_schedules_all ps,

17588: FUNCTION site_currency_code(p_customer_site_use_id NUMBER) RETURN VARCHAR2
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:

Line 17620: from ar_payment_schedules_all ps,

17616: FUNCTION party_amount_due_remaining(p_party_id NUMBER) RETURN NUMBER
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;

Line 17646: from ar_payment_schedules_all ps,

17642: FUNCTION acct_amount_due_remaining(p_account_id NUMBER) RETURN NUMBER
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;

Line 17673: from ar_payment_schedules_all ps,

17669: FUNCTION site_amount_due_remaining(p_customer_site_use_id NUMBER) RETURN NUMBER
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;

Line 17703: from ar_payment_schedules_all ps,

17699: FUNCTION staged_dunn_amt_due_remaining(p_dunning_id number) RETURN NUMBER
17700: IS
17701: cursor c_amount (dunn_id number) is
17702: select sum(ps.acctd_amount_due_remaining)
17703: from ar_payment_schedules_all ps,
17704: iex_dunning_transactions dtrx
17705: where ps.payment_schedule_id = dtrx.payment_schedule_id
17706: and dtrx.dunning_id = dunn_id;
17707: l_sum number;

Line 17985: 'ar_payment_schedules_all aps ' ||

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 ' ||
17989: 'and ixr.xml_request_id (+) = id.xml_request_id ' ||

Line 18515: ' ar_payment_schedules arp ' ||

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
18519: ' and (trunc(arp.due_date) + :p_gra_days) <= :p_corr_date ' ||

Line 18602: ' ar_payment_schedules arp ' ||

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
18606: ' and (trunc(arp.due_date) + 0) <= :p_corr_date ' ||

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 18777: ' from ar_payment_schedules arp, hz_cust_accounts hca ' ||

18773:
18774: if (p_running_level = 'CUSTOMER') then
18775: vPLSQL2 := 'select arp.customer_trx_id, ' ||
18776: ' arp.payment_schedule_id ' ||
18777: ' from ar_payment_schedules arp, hz_cust_accounts hca ' ||
18778: ' where arp.customer_id = hca.cust_account_id ' ||
18779: ' and hca.party_id = :p_party_id ' ||
18780: -- ' and trunc(arp.due_date) > trunc(:p_corr_date) ' ||
18781: ' and arp.status = ''OP'' ' ||

Line 18792: ' from ar_payment_schedules arp ' ||

18788: ' order by arp.payment_schedule_id';
18789: elsif (p_running_level = 'ACCOUNT') then
18790: vPLSQL2 := 'select arp.customer_trx_id, ' ||
18791: ' arp.payment_schedule_id ' ||
18792: ' from ar_payment_schedules arp ' ||
18793: ' where arp.customer_id = :p_cust_acct_id ' ||
18794: --' and trunc(arp.due_date) > trunc(:p_corr_date) ' ||
18795: ' and arp.status = ''OP'' ' ||
18796: ' and arp.amount_due_remaining <> 0 ' ||

Line 18806: ' from ar_payment_schedules arp ' ||

18802: ' order by arp.payment_schedule_id';
18803: elsif (p_running_level = 'BILL_TO') then
18804: vPLSQL2 := 'select arp.customer_trx_id, ' ||
18805: ' arp.payment_schedule_id ' ||
18806: ' from ar_payment_schedules arp ' ||
18807: ' where arp.customer_site_use_id = :p_site_use_id ' ||
18808: --' and trunc(arp.due_date) > trunc(:p_corr_date) ' ||
18809: ' and arp.status = ''OP'' ' ||
18810: ' and arp.amount_due_remaining <> 0 ' ||

Line 18877: ' from ar_payment_schedules arp, ' ||

18873: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Start insert unapplied receipts');
18874:
18875: if (p_running_level = 'CUSTOMER') then
18876: vPLSQL2 := 'select arp.payment_schedule_id ' ||
18877: ' from ar_payment_schedules arp, ' ||
18878: ' hz_cust_accounts hca ' ||
18879: ' where arp.customer_id = hca.cust_account_id ' ||
18880: ' and hca.party_id = :p_party_id ' ||
18881: ' and arp.status = ''OP'' ' ||

Line 18887: ' from ar_payment_schedules arp ' ||

18883: ' and arp.class = ''PMT'' ' ||
18884: ' order by arp.payment_schedule_id';
18885: elsif (p_running_level = 'ACCOUNT') then
18886: vPLSQL2 := 'select arp.payment_schedule_id ' ||
18887: ' from ar_payment_schedules arp ' ||
18888: ' where arp.customer_id = :p_cust_acct_id ' ||
18889: ' and arp.status = ''OP'' ' ||
18890: ' and arp.amount_due_remaining <> 0 ' ||
18891: ' and arp.class = ''PMT'' ' ||

Line 18895: ' from ar_payment_schedules arp ' ||

18891: ' and arp.class = ''PMT'' ' ||
18892: ' order by arp.payment_schedule_id';
18893: elsif (p_running_level = 'BILL_TO') then
18894: vPLSQL2 := 'select arp.payment_schedule_id ' ||
18895: ' from ar_payment_schedules arp ' ||
18896: ' where arp.customer_site_use_id = :p_site_use_id ' ||
18897: ' and arp.status = ''OP'' ' ||
18898: ' and arp.amount_due_remaining <> 0 ' ||
18899: ' and arp.class = ''PMT'' ' ||

Line 18949: ' from ar_payment_schedules arp, ' ||

18945:
18946: if (p_running_level = 'CUSTOMER') then
18947: vPLSQL2 := 'select arp.customer_trx_id customer_trx_id, ' ||
18948: ' arp.payment_schedule_id ' ||
18949: ' from ar_payment_schedules arp, ' ||
18950: ' hz_cust_accounts hca ' ||
18951: ' where arp.customer_id = hca.cust_account_id ' ||
18952: ' and hca.party_id = :p_party_id ' ||
18953: ' and arp.amount_due_remaining <> 0 ' ||

Line 18959: ' from ar_payment_schedules arp ' ||

18955: ' and arp.status=''OP'' ';
18956: elsif (p_running_level = 'ACCOUNT') then
18957: vPLSQL2 := 'select arp.customer_trx_id customer_trx_id, ' ||
18958: ' arp.payment_schedule_id ' ||
18959: ' from ar_payment_schedules arp ' ||
18960: ' where arp.customer_id = :p_cust_acct_id ' ||
18961: ' and arp.amount_due_remaining <> 0 ' ||
18962: ' and arp.class =''CM'' ' ||
18963: ' and arp.status=''OP'' ';

Line 18967: ' from ar_payment_schedules arp ' ||

18963: ' and arp.status=''OP'' ';
18964: elsif (p_running_level = 'BILL_TO') then
18965: vPLSQL2 := 'select arp.customer_trx_id customer_trx_id, ' ||
18966: ' arp.payment_schedule_id ' ||
18967: ' from ar_payment_schedules arp ' ||
18968: ' where arp.customer_site_use_id = :p_site_use_id ' ||
18969: ' and arp.amount_due_remaining <> 0 ' ||
18970: ' and arp.class =''CM'' ' ||
18971: ' and arp.status=''OP'' ';