[Home] [Help]
2996: ' and nvl( ' ||
2997: ' ( ' ||
2998: ' (select trunc(correspondence_date) from iex_dunnings ' ||
2999: ' where dunning_id = ' ||
3000: ' (select max(iet.DUNNING_ID) from iex_dunning_transactions iet, ' ||
3001: ' iex_dunnings dunn ' ||
3002: ' where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id ' ||
3003: ' and dunn.dunning_id = iet.dunning_id ' ||
3004: ' and ((dunn.dunning_mode = ''DRAFT'' and dunn.confirmation_mode = ''CONFIRMED'') ' ||
3108: ' and nvl( ' ||
3109: ' ( ' ||
3110: ' (select trunc(correspondence_date) from iex_dunnings ' ||
3111: ' where dunning_id = ' ||
3112: ' (select max(iet.DUNNING_ID) from iex_dunning_transactions iet, ' ||
3113: ' iex_dunnings dunn ' ||
3114: ' where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id ' ||
3115: ' and dunn.dunning_id = iet.dunning_id ' ||
3116: ' and ((dunn.dunning_mode = ''DRAFT'' and dunn.confirmation_mode = ''CONFIRMED'') ' ||
3295: --' and (trunc(arp.due_date) + 0) <= :p_corr_date ' ||
3296: ' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ';
3297: if (p_running_level = 'CUSTOMER') then
3298: vPLSQL3 := ' and del.party_cust_id = :p_party_id ' ||
3299: ' and del.payment_schedule_id not in (select payment_schedule_id from iex_dunning_transactions where dunning_id = :p_dunning_id and PAYMENT_SCHEDULE_ID = del.payment_schedule_id ) ' ||
3300: ' order by del.payment_schedule_id';
3301: elsif (p_running_level = 'ACCOUNT') then
3302: vPLSQL3 := ' and del.cust_account_id = :p_cust_acct_id ' ||
3303: --Begin Bug 10401991 03-feb-2011 barathsr
3306: ' FROM hz_cust_profile_amts '||
3307: ' WHERE site_use_id IS NULL '||
3308: ' AND cust_account_id = arp.customer_id '||
3309: ' AND currency_code = arp.invoice_currency_code),0) '||
3310: ' and del.payment_schedule_id not in (select payment_schedule_id from iex_dunning_transactions where dunning_id = :p_dunning_id and PAYMENT_SCHEDULE_ID = del.payment_schedule_id ) ' ||
3311: ' order by del.payment_schedule_id';
3312: --End Bug 10401991 03-feb-2011 barathsr
3313: elsif (p_running_level = 'BILL_TO') then
3314: -- vPLSQL3 := ' and del.customer_site_use_id iN ( :p_site_use_id,24388) ' ||
3317: ' and arp.amount_due_remaining >= nvl ((select nvl(min(min_dunning_invoice_amount),0) '||
3318: ' from hz_cust_profile_amts '||
3319: ' where site_use_id = arp.CUSTOMER_SITE_USE_ID '||
3320: ' and currency_code = arp.invoice_currency_code),0) '||
3321: ' and del.payment_schedule_id not in (select payment_schedule_id from iex_dunning_transactions where dunning_id = :p_dunning_id and PAYMENT_SCHEDULE_ID = del.payment_schedule_id ) ' ||
3322: ' order by del.payment_schedule_id';
3323: --End Bug 10401991 03-feb-2011 barathsr
3324: end if;
3325: vPLSQL := vPLSQL || vPLSQL3;
3340: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - sql_cur.l_delinquency_id :'||l_delinquency_id);
3341: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - sql_cur.l_transaction_id :'||l_transaction_id);
3342: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - sql_cur.l_payment_schedule_id : '||l_payment_schedule_id);
3343:
3344: select count(*) into iex_dunn_trx_counter from iex_dunning_transactions where dunning_id = p_dunning_id and PAYMENT_SCHEDULE_ID = l_payment_schedule_id;
3345:
3346: if iex_dunn_trx_counter = 0 then
3347:
3348: IEX_Dunnings_PKG.insert_staged_dunning_row(
5748: (
5749: (select trunc(correspondence_date) from iex_dunnings
5750: where dunning_id =
5751: (select max(iet.DUNNING_ID)
5752: from iex_dunning_transactions iet,
5753: iex_dunnings dunn
5754: where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
5755: and dunn.dunning_id = iet.dunning_id
5756: and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
5775: (
5776: (select trunc(correspondence_date) from iex_dunnings
5777: where dunning_id =
5778: (select max(iet.DUNNING_ID)
5779: from iex_dunning_transactions iet,
5780: iex_dunnings dunn
5781: where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
5782: and dunn.dunning_id = iet.dunning_id
5783: and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
5802: (
5803: (select trunc(correspondence_date) from iex_dunnings
5804: where dunning_id =
5805: (select max(iet.DUNNING_ID)
5806: from iex_dunning_transactions iet,
5807: iex_dunnings dunn
5808: where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
5809: and dunn.dunning_id = iet.dunning_id
5810: and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
6104: ' and nvl( ' ||
6105: ' ( ' ||
6106: ' (select trunc(correspondence_date) from iex_dunnings ' ||
6107: ' where dunning_id = ' ||
6108: ' (select max(iet.DUNNING_ID) from iex_dunning_transactions iet, ' ||
6109: ' iex_dunnings dunn ' ||
6110: ' where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id ' ||
6111: ' and dunn.dunning_id = iet.dunning_id ' ||
6112: ' and ((dunn.dunning_mode = ''DRAFT'' and dunn.confirmation_mode = ''CONFIRMED'') ' ||
6153: ' and nvl( ' ||
6154: ' ( ' ||
6155: ' (select trunc(correspondence_date) from iex_dunnings ' ||
6156: ' where dunning_id = ' ||
6157: ' (select max(iet.DUNNING_ID) from iex_dunning_transactions iet, ' ||
6158: ' iex_dunnings dunn ' ||
6159: ' where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id ' ||
6160: ' and dunn.dunning_id = iet.dunning_id ' ||
6161: ' and ((dunn.dunning_mode = ''DRAFT'' and dunn.confirmation_mode = ''CONFIRMED'') ' ||
6744: where del.party_cust_id = l_object_id
6745: and staged_dunning_level = 98
6746: and status in ('DELINQUENT','PREDELINQUENT')
6747: and exists (select count(iet.DUNNING_ID)
6748: from iex_dunning_transactions iet,
6749: iex_dunnings dunn
6750: where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
6751: and dunn.dunning_id = iet.dunning_id
6752: and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
6761: where del.cust_account_id = l_object_id
6762: and staged_dunning_level = 98
6763: and status in ('DELINQUENT','PREDELINQUENT')
6764: and exists (select count(iet.DUNNING_ID)
6765: from iex_dunning_transactions iet,
6766: iex_dunnings dunn
6767: where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
6768: and dunn.dunning_id = iet.dunning_id
6769: and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
6778: where del.customer_site_use_id = l_object_id
6779: and staged_dunning_level = 98
6780: and status in ('DELINQUENT','PREDELINQUENT')
6781: and exists (select count(iet.DUNNING_ID)
6782: from iex_dunning_transactions iet,
6783: iex_dunnings dunn
6784: where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
6785: and dunn.dunning_id = iet.dunning_id
6786: and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
8206: and nvl(
8207: (
8208: (select trunc(correspondence_date) from iex_dunnings
8209: where dunning_id =
8210: (select max(iet.DUNNING_ID) from iex_dunning_transactions iet,
8211: iex_dunnings dunn
8212: where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
8213: and dunn.dunning_id = iet.dunning_id
8214: and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
8242: and nvl(
8243: (
8244: (select trunc(correspondence_date) from iex_dunnings
8245: where dunning_id =
8246: (select distinct DUNNING_ID from iex_dunning_transactions
8247: where PAYMENT_SCHEDULE_ID = del.payment_schedule_id
8248: and STAGE_NUMBER = p_stage_no))
8249: + p_min_days_bw_dun )
8250: , p_corr_date )
8455: (
8456: (select trunc(correspondence_date) from iex_dunnings
8457: where dunning_id =
8458: (select max(iet.DUNNING_ID)
8459: from iex_dunning_transactions iet,
8460: iex_dunnings dunn
8461: where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
8462: and dunn.dunning_id = iet.dunning_id
8463: and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
9046: where delinquency_id = l_delinquency_id
9047: and staged_dunning_level = 98
9048: and status in ('DELINQUENT','PREDELINQUENT')
9049: and exists (select count(iet.DUNNING_ID)
9050: from iex_dunning_transactions iet,
9051: iex_dunnings dunn
9052: where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
9053: and dunn.dunning_id = iet.dunning_id
9054: and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
14410: END;
14411:
14412: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_transaction_id: ' || l_transaction_id);
14413: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_payment_schedule_id: ' || l_payment_schedule_id);
14414: --insert invoice list in to iex_dunning_transactions table for all correspondence.
14415: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Calling IEX_Dunnings_PKG.insert_staged_dunning_row');
14416: IEX_Dunnings_PKG.insert_staged_dunning_row(
14417: px_rowid => l_rowid
14418: , px_dunning_trx_id => x_dunning_trx_id
14428: , p_last_update_login => FND_GLOBAL.USER_ID
14429: , p_object_version_number => 1.0
14430: );
14431: if x_dunning_trx_id is null then
14432: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Cannot insert record in to iex_dunning_transactions table');
14433: else
14434: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Insert record in to iex_dunning_transactions table is success. x_dunning_trx_id ' || x_dunning_trx_id);
14435: end if;
14436: end if;
14430: );
14431: if x_dunning_trx_id is null then
14432: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Cannot insert record in to iex_dunning_transactions table');
14433: else
14434: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Insert record in to iex_dunning_transactions table is success. x_dunning_trx_id ' || x_dunning_trx_id);
14435: end if;
14436: end if;
14437:
14438: l_bind_count := l_fulfillment_bind_tbl.count;
14790: END;
14791:
14792: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_transaction_id: ' || l_transaction_id);
14793: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_payment_schedule_id: ' || l_payment_schedule_id);
14794: --insert invoice list in to iex_dunning_transactions table for all correspondence.
14795: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Calling IEX_Dunnings_PKG.insert_staged_dunning_row');
14796: IEX_Dunnings_PKG.insert_staged_dunning_row(
14797: px_rowid => l_rowid
14798: , px_dunning_trx_id => x_dunning_trx_id
14808: , p_last_update_login => FND_GLOBAL.USER_ID
14809: , p_object_version_number => 1.0
14810: );
14811: if x_dunning_trx_id is null then
14812: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Cannot insert record in to iex_dunning_transactions table');
14813: else
14814: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Insert record in to iex_dunning_transactions table is success. x_dunning_trx_id ' || x_dunning_trx_id);
14815: end if;
14816: end if;
14810: );
14811: if x_dunning_trx_id is null then
14812: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Cannot insert record in to iex_dunning_transactions table');
14813: else
14814: WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Insert record in to iex_dunning_transactions table is success. x_dunning_trx_id ' || x_dunning_trx_id);
14815: end if;
14816: end if;
14817:
14818: l_bind_count := l_fulfillment_bind_tbl.count;
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;
17708: BEGIN
18171: cursor c_get_invoice_ct (p_conc_req_id number) is
18172: select idt.cust_trx_id,
18173: dunn.object_id,
18174: dunn.object_type
18175: from iex_dunning_transactions idt,
18176: iex_dunnings dunn,
18177: iex_ag_dn_xref xref
18178: where idt.dunning_id = dunn.dunning_id
18179: and dunn.request_id = p_conc_req_id
18194: vPLSQL1 := 'select idt.cust_trx_id, ' ||
18195: ' dunn.object_id, ' ||
18196: ' dunn.object_type, ' ||
18197: ' dunn.dunning_method ' ||
18198: ' from iex_dunning_transactions idt, ' ||
18199: ' iex_dunnings dunn, ' ||
18200: ' iex_ag_dn_xref xref, ' ||
18201: ' ra_customer_trx trx ' ||
18202: ' where idt.dunning_id = dunn.dunning_id ' ||
18633: vPLSQL3 := ' and del.delinquency_id = :p_delinquency_id ' ;
18634: -- ' order by del.payment_schedule_id'; added below 12/23/2011 fix 13519242
18635: end if;
18636: -- start added 12/23/2011 fix 13519242 to stop inserting duplicate rows
18637: vPLSQL3 := vPLSQL3 || ' and not exists (select 1 from iex_dunning_transactions dun_trx '||
18638: ' where dun_trx.payment_schedule_id = del.payment_schedule_id '||
18639: ' and dun_trx.dunning_id = :p_dunning_id ) ' ||
18640: ' order by del.payment_schedule_id';
18641: -- end added 12/23/2011 fix 13519242
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, ' ||
18791: ' arp.payment_schedule_id ' ||
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, ' ||
18805: ' arp.payment_schedule_id ' ||
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;
18819: if vPLSQL2 is not null then -- If running level is delinquency then Account Credit memos is not requied so vplsq2 will be null