[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Update_AG_DN_XREF
(p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
P_AG_DN_XREF_TBL IN IEX_DUNNING_PUB.AG_DN_XREF_TBL_TYPE ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_AG_DN_XREF';
SAVEPOINT UPDATE_AG_DN_PUB;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn: Start ');
IEX_DUNNING_PVT.Update_AG_DN_XREF(
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_ag_dn_xref_rec => l_ag_dn_xref_rec
, p_ag_dn_xref_id => l_ag_dn_xref_rec.ag_dn_Xref_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn: end ');
ROLLBACK TO Update_Ag_Dn_PUB;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:Exc Exception');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:error='||errmsg);
ROLLBACK TO Update_Ag_Dn_PUB;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:Exc Exception');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:error='||errmsg);
ROLLBACK TO Update_Ag_Dn_PUB;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:Exc Exception');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:error='||errmsg);
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:Exc Exception');
END Update_AG_DN_XREF;
Procedure Delete_AG_DN_XREF
(p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
P_AG_DN_XREF_ID IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_AG_DN_XREF_id NUMBER ;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_AG_DN_XREF';
SAVEPOINT DELETE_AG_DN_PUB;
IEX_DUNNING_PVT.Delete_AG_DN_XREF(
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_AG_DN_XREF_id => p_AG_DN_XREF_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
ROLLBACK TO Delete_Ag_Dn_PUB;
ROLLBACK TO Delete_Ag_Dn_PUB;
ROLLBACK TO Delete_Ag_Dn_PUB;
END Delete_AG_DN_XREF;
p_custom_select OUT NOCOPY VARCHAR2)
IS
l_custom_select varchar2(2000);
l_custom_select := ' SELECT p.party_name ' ||
' From hz_cust_acct_sites_all acct_sites, ' ||
' hz_party_sites party_site, ' ||
' hz_cust_accounts ca, ' ||
' hz_cust_site_uses_all site_uses, ' ||
' hz_parties p ' ||
' WHERE acct_sites.cust_account_id = ca.cust_account_id ' ||
' AND acct_sites.party_site_id = party_site.party_site_id ' ||
' AND acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id ' ||
--' AND site_uses.site_use_code = ''BILL_TO'' ' || -- Bills Receivables
' AND (site_uses.site_use_code = ''BILL_TO'' or site_uses.site_use_code = ''DRAWEE'') ' || -- Bills Receivables
' AND ca.party_id = p.party_id ';
l_custom_select := 'SELECT p.party_name ' ||
' From hz_cust_acct_sites_all acct_sites, ' ||
' hz_party_sites party_site, ' ||
' hz_cust_accounts ca, ' ||
' hz_cust_site_uses_all site_uses, ' ||
' hz_parties p,' ||
' iex_delinquencies_all delin ' ||
' WHERE acct_sites.cust_account_id = ca.cust_account_id ' ||
' AND acct_sites.party_site_id = party_site.party_site_id ' ||
' AND acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id ' ||
' AND site_uses.site_use_code = ''BILL_TO'' ' ||
' AND ca.party_id = p.party_id ' ||
' AND delin.customer_site_use_id = site_uses.site_use_id ';
l_custom_select := l_custom_select || ' AND upper(p.party_name) >= upper(''' || replace(p_customer_name_low,'''','''''') || ''') ';
l_custom_select := l_custom_select || ' AND upper(p.party_name) <= upper(''' || replace(p_customer_name_high,'''','''''') || ''') ';
l_custom_select := l_custom_select || ' AND upper(ca.account_number) >= upper(''' || replace(p_account_number_low,'''','''''') || ''') ';
l_custom_select := l_custom_select || ' AND upper(ca.account_number) <= upper(''' || replace(p_account_number_high,'''','''''') || ''') ';
l_custom_select := l_custom_select || ' AND upper(site_uses.location) >= upper(''' || replace(p_billto_location_low,'''','''''') || ''') ';
l_custom_select := l_custom_select || ' AND upper(site_uses.location) <= upper(''' || replace(p_billto_location_high,'''','''''') || ''') ';
l_custom_select := l_custom_select || ' AND p.party_id ';
l_custom_select := l_custom_select || ' AND ca.cust_account_id ';
l_custom_select := l_custom_select || ' AND site_uses.site_use_id ';
l_custom_select := l_custom_select || ' AND delin.delinquency_id ';
p_custom_select := l_custom_select;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_custom_select : '||l_custom_select);
select aging_bucket_id from iex_dunning_plans_vl
where dunning_plan_id = p_dunning_plan_id;
vSelectColumn varchar2(25);
select sc.score_id
,sc.score_name
from iex_dunning_plans_vl d
,iex_scores sc
where d.dunning_plan_id = p_dunning_plan_id
and sc.score_id = d.score_id;
select iof.select_column, iof.entity_name
from IEX_OBJECT_FILTERS iof,iex_dunning_plans_vl ipd, IEX_SCORES isc
where ipd.dunning_plan_id = p_dunning_plan_id
and ipd.score_id=isc.score_id
and isc.score_id=iof.object_id
and object_filter_type = 'IEXSCORE';
l_select_column varchar2(50);
l_custom_select varchar2(2000);
l_con_update_re_st boolean;
l_update_cp_as_err boolean := FALSE;
select dunning_mode
from iex_dunnings
where request_id = p_req_id;
select count(1)
from iex_dunnings
where request_id = p_req_id
and confirmation_mode = 'CONFIRMED';
select count(1)
from iex_dunnings id
where id.request_id = p_req_id
and id.delivery_status is not null
and id.object_type <> 'IEX_INVOICES'
and id.dunning_id = (select max(d.dunning_id) from iex_dunnings d
where d.dunning_object_id = id.dunning_object_id
and d.dunning_level = id.dunning_level and d.request_id = id.request_id
and d.object_type <> 'IEX_INVOICES');
select count(1)
from iex_dunnings id
where id.request_id = p_req_id
and id.delivery_status is null
and id.object_type <> 'IEX_INVOICES'
and id.dunning_id = (select max(d.dunning_id) from iex_dunnings d
where d.dunning_object_id = id.dunning_object_id
and d.dunning_level = id.dunning_level and d.request_id = id.request_id
and d.object_type <> 'IEX_INVOICES');
select count(1)
from iex_dunnings id
where id.request_id = p_req_id
and id.delivery_status is null
and id.object_type = 'IEX_INVOICES'
and id.dunning_id = (select max(d.dunning_id) from iex_dunnings d
where d.dunning_object_id = id.dunning_object_id
and d.dunning_level = id.dunning_level and d.request_id = id.request_id
and d.object_type = 'IEX_INVOICES');
select count(idt.cust_trx_id)
from iex_dunning_transactions idt,
iex_dunnings dunn,
iex_ag_dn_xref xref,
ra_customer_trx trx
where idt.dunning_id = dunn.dunning_id
and dunn.request_id = p_conc_req_id
and dunn.ag_dn_xref_id = xref.ag_dn_xref_id
and xref.invoice_copies = 'Y'
and idt.cust_trx_id is not null
and trx.customer_trx_id = idt.cust_trx_id
and trx.printing_option = 'PRI';
select count(idt.cust_trx_id)
from iex_dunning_transactions idt,
iex_dunnings dunn,
iex_ag_dn_xref xref,
ra_customer_trx trx
where idt.dunning_id = dunn.dunning_id
and dunn.request_id = p_conc_req_id
and dunn.ag_dn_xref_id = xref.ag_dn_xref_id
and xref.invoice_copies = 'Y'
and idt.cust_trx_id is not null
and idt.dunning_trx_id > p_max_dunn_trx_id
and trx.customer_trx_id = idt.cust_trx_id
and trx.printing_option = 'PRI';
select max(idt.dunning_trx_id)
from iex_dunning_transactions idt,
iex_dunnings dunn
where idt.dunning_id = dunn.dunning_id
and dunn.request_id = p_conc_req_id;
select object_type
from iex_dunnings
where request_id = p_req_id
order by dunning_id;
select nvl(dunning_type,'DAYS_OVERDUE'),dunning_letter_set_id --Added for Bug 11656175 01-Mar-2011 barathsr
into l_dunning_type,l_dunning_letter_set_id
from IEX_DUNNING_PLANS_B
where dunning_plan_id = p_dunning_plan_id;
Fetch c_filter_object into l_select_column,l_entity_name;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_select_column: '|| l_select_column);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_select_column: '|| l_select_column);
p_custom_select => l_custom_select);
WriteLog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
vSelectColumn := 'party_cust_id';
vPLSQL2 := ' select ' ||
' par_site.party_id ' ||
' ,acct_site.cust_account_id ' ||
' ,site_use.site_use_id ' ||
' ,decode(site_use.site_use_code, ' ||
' ''DUN'', 1, ' ||
' ''BILL_TO'', decode(site_use.primary_flag, ''Y'', 2, 3)) Display_Order ' ||
'from HZ_CUST_SITE_USES site_use ' ||
' ,HZ_CUST_ACCT_SITES acct_site ' ||
' ,hz_party_sites par_site ' ||
' ,iex_dunning_plans_vl ' ||
'where ' ||
' par_site.party_id = :1 and ' ||
' par_site.status = ''A'' and ' ||
' par_site.party_site_id = acct_site.party_site_id and ' ||
' acct_site.status = ''A'' and ' ||
' acct_site.cust_acct_site_id = site_use.cust_acct_site_id and ' ||
' site_use.status = ''A'' and ' ||
' iex_dunning_plans_vl.dunning_plan_id = :p_dunning_plan_id and' ||
' exists (select ' || l_select_column || ' from ' || l_entity_name || ' where '|| l_select_column ||' = par_site.party_id) ' ;
if l_custom_select IS NOT NULL then
vPLSQL2 := vPLSQL2 || ' and exists ( ' || l_custom_select ||' = par_site.party_id) ' ;
vSelectColumn := 'cust_account_id';
vPLSQL2 := ' select ' ||
' par_site.party_id ' ||
' ,acct_site.cust_account_id ' ||
' ,site_use.site_use_id ' ||
' ,decode(site_use.site_use_code, ' ||
' ''DUN'', 1, ' ||
' ''BILL_TO'', decode(site_use.primary_flag, ''Y'', 2, 3)) Display_Order ' ||
'from HZ_CUST_SITE_USES site_use ' ||
' ,HZ_CUST_ACCT_SITES acct_site ' ||
' ,hz_party_sites par_site ' ||
' ,iex_dunning_plans_vl ' ||
'where ' ||
' acct_site.cust_account_id = :1 and ' ||
' par_site.status = ''A'' and ' ||
' par_site.party_site_id = acct_site.party_site_id and ' ||
' acct_site.status = ''A'' and ' ||
' acct_site.cust_acct_site_id = site_use.cust_acct_site_id and ' ||
' site_use.status = ''A'' and ' ||
' iex_dunning_plans_vl.dunning_plan_id = :p_dunning_plan_id and' ||
' exists (select ' || l_select_column || ' from ' || l_entity_name || ' where '|| l_select_column ||' = acct_site.cust_account_id) ';
if l_custom_select IS NOT NULL then
vPLSQL2 := vPLSQL2 || ' and exists ( ' || l_custom_select ||' = acct_site.cust_account_id) ';
vPLSQL2 := vPLSQL2 || ' and exists (select 1 from hz_customer_profiles prof where prof.cust_account_id = acct_site.cust_account_id '||
' and prof.site_use_id is null and prof.dunning_letter_set_id = '||l_dunning_letter_set_id||')';
vSelectColumn := 'customer_site_use_id';
vPLSQL2 := ' select ' ||
' par_site.party_id ' ||
' ,acct_site.cust_account_id ' ||
' ,site_use.site_use_id ' ||
' ,decode(site_use.site_use_code, ' ||
' ''DUN'', 1, ' ||
' ''BILL_TO'', decode(site_use.primary_flag, ''Y'', 2, 3)) Display_Order ' ||
'from HZ_CUST_SITE_USES site_use ' ||
' ,HZ_CUST_ACCT_SITES acct_site ' ||
' ,hz_party_sites par_site ' ||
' ,iex_dunning_plans_vl ' ||
'where ' ||
' site_use.site_use_id = :1 and ' ||
' par_site.status = ''A'' and ' ||
' par_site.party_site_id = acct_site.party_site_id and ' ||
' acct_site.status = ''A'' and ' ||
' acct_site.cust_acct_site_id = site_use.cust_acct_site_id and ' ||
' site_use.status = ''A'' and ' ||
' iex_dunning_plans_vl.dunning_plan_id = :p_dunning_plan_id and' ||
' exists (select ' || l_select_column || ' from ' || l_entity_name || ' where '|| l_select_column ||' = site_use.site_use_id) ';
if l_custom_select IS NOT NULL then
vPLSQL2 := vPLSQL2 || ' and exists ( ' || l_custom_select ||' = site_use.site_use_id) ';
vPLSQL2 := vPLSQL2 || ' and exists (select 1 from hz_customer_profiles prof where prof.cust_account_id = acct_site.cust_account_id '||
' and prof.site_use_id = site_use.site_use_id and prof.dunning_letter_set_id = '||l_dunning_letter_set_id||')';
vSelectColumn := 'delinquency_id';
vPLSQL2 := 'SELECT delinquency_ID ' ||
' ,party_cust_id ' ||
' ,cust_account_id ' ||
' ,customer_site_use_id ' ||
--' ,score_value ' ||
' FROM IEX_DELINQUENCIES del' ||
' ,iex_dunning_plans_vl ' ||
' WHERE STATUS in (''DELINQUENT'', ''PREDELINQUENT'') ' ||
' AND DELINQUENCY_ID = :1 ' ||
' AND iex_dunning_plans_vl.dunning_plan_id = :p_dunning_plan_id ' ;
--' AND exists (select ' || l_select_column || ' from ' || l_entity_name || ' where '|| l_select_column ||' = del.DELINQUENCY_ID) ';
if l_select_column = 'PAYMENT_SCHEDULE_ID' then
vPLSQL2 := vPLSQL2 || ' AND exists (select 1 from ' || l_entity_name ||' len where len.PAYMENT_SCHEDULE_ID = nvl(del.PAYMENT_SCHEDULE_ID,0)) ';
elsif l_select_column = 'DELINQUENCY_ID' then
vPLSQL2 := vPLSQL2 ||' AND exists (select 1 from ' || l_entity_name || ' len where len.delinquency_id = del.DELINQUENCY_ID) ' ;
if l_custom_select IS NOT NULL then
vPLSQL2 := vPLSQL2 || ' and exists ( ' || l_custom_select ||' = del.DELINQUENCY_ID) ';
vPLSQL2 := vPLSQL2 || ' and exists (select 1 from hz_customer_profiles prof where prof.cust_account_id = del.cust_account_id '||
' and prof.site_use_id = del.customer_site_use_id and prof.dunning_letter_set_id = '||l_dunning_letter_set_id||')';
select count(*) into t_cnt from iex_delinquencies , iex_dunning_plans_vl
where (status = DECODE(include_current,'Y','CURRENT','') or status in ('PREDELINQUENT','DELINQUENT'))
and exists (select 1 from hz_cust_site_uses_all site_uses where site_use_code = 'BILL_TO' and site_uses.site_use_id = customer_site_use_id)
and iex_dunning_plans_vl.dunning_plan_id = p_dunning_plan_id;
vPLSQL := ' SELECT distinct ' || vSelectColumn ||
' FROM IEX_DELINQUENCIES , IEX_DUNNING_PLANS_VL '||
' where (status = DECODE(include_current,''Y'',''CURRENT'','' '') or status in (''PREDELINQUENT'',''DELINQUENT'')) ' || -- Bills Receivables
' and iex_dunning_plans_vl.dunning_plan_id = :p_dunning_plan_id ' ||
' order by ' || vSelectColumn;
vPLSQL := ' SELECT distinct ' || vSelectColumn ||
' FROM IEX_DELINQUENCIES , IEX_DUNNING_PLANS_VL '||
' where (status = DECODE(include_current,''Y'',''CURRENT'','' '') or status in (''PREDELINQUENT'',''DELINQUENT'')) ' || -- Bills Receivables
' and exists (select 1 from hz_cust_site_uses_all site_uses where site_use_code = ''BILL_TO'' and site_uses.site_use_id = customer_site_use_id) ' ||
-- ' WHERE STATUS in (''DELINQUENT'', ''PREDELINQUENT'') ' || Bills Receivables
' and iex_dunning_plans_vl.dunning_plan_id = :p_dunning_plan_id ' ||
' order by ' || vSelectColumn;
vPLSQL := ' SELECT distinct ' || vSelectColumn ||
' FROM IEX_DELINQUENCIES , IEX_DUNNING_PLANS_VL '||
' where (status = DECODE(include_current,''Y'',''CURRENT'','' '') or status in (''PREDELINQUENT'',''DELINQUENT'')) '|| -- Bills Receivables
' and exists (select 1 from hz_cust_site_uses_all site_uses where (site_use_code = ''DRAWEE'' or site_use_code = ''BILL_TO'') and site_uses.site_use_id = customer_site_use_id) ' ||
' and iex_dunning_plans_vl.dunning_plan_id = :p_dunning_plan_id ' ||
' order by ' || vSelectColumn;
vPLSQL := ' SELECT distinct object_id ' ||
' FROM IEX_DUNNINGS ID ' ||
--' WHERE DELIVERY_STATUS = ''ERROR'' ' ||
' WHERE DELIVERY_STATUS IS NOT NULL ' ||
--' AND STATUS = ''OPEN'' ' ||
' AND REQUEST_ID = :1 ' ||
' AND DUNNING_LEVEL = :2 ' ||
' AND ID.object_type <> ''IEX_INVOICES'' ' ||
' AND dunning_id = (select max(d.dunning_id) from iex_dunnings d ' ||
' where d.dunning_object_id = id.dunning_object_id ' ||
' and d.dunning_level=id.dunning_level and d.request_id = id.request_id ' ||
' and d.object_type <> ''IEX_INVOICES'' )';
l_update_cp_as_err := TRUE;
l_update_cp_as_err := TRUE;
--open sql_cur2 for vPLSQL2 using l_object_id,p_dunning_plan_id,l_select_column,l_entity_name,l_select_column;
vPLSQL3 := 'select dun.dunning_id, dun.dunning_object_id, dun.dunning_level, dun.ag_dn_xref_id ' ||
'from iex_dunnings dun ' ||
'where dun.request_id = :1 ' ||
' and dun.delivery_status IS NULL ' ||
' and dun.confirmation_mode is null ';
if l_custom_select IS NOT NULL then
--Start for bug 9818696 gnramasa 16th June 10
--vPLSQL3 := vPLSQL3 || ' and exists ( ' || l_custom_select ||' = dun.object_id) ';
vPLSQL3 := vPLSQL3 || ' and exists ( ' || l_custom_select ||' = dun.dunning_object_id) ';
IEX_DUNNING_PVT.Update_DUNNING(
p_api_version => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_TRUE
, p_dunning_rec => l_dunning_rec_upd
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
vPLSQL4 := 'select xml.xml_request_id ' ||
'from iex_dunnings dun, iex_xml_request_histories xml ' ||
'where xml.conc_request_id = :1 ' ||
' and xml.xml_request_id = dun.xml_request_id ' ||
' and xml.confirmation_mode is null ';
if l_custom_select IS NOT NULL then
--vPLSQL4 := vPLSQL4 || ' and exists ( ' || l_custom_select ||' = dun.object_id) ';
vPLSQL4 := vPLSQL4 || ' and exists ( ' || l_custom_select ||' = dun.dunning_object_id) ';
IEX_XML_PKG.update_row(
p_xml_request_id => l_xml_request_id
, p_status => l_status
, p_confirmation_mode => l_conf_mode
);
vPLSQL6 := 'select xml.xml_request_id ' ||
'from iex_dunnings dun, iex_xml_request_histories xml ' ||
'where xml.conc_request_id = :1 ' ||
' and xml.xml_request_id = dun.xml_request_id ' ||
' and xml.confirmation_mode is null ' ||
' and dun.object_type = ''INX_INVOICES'' ';
if l_custom_select IS NOT NULL then
--vPLSQL6 := vPLSQL6 || ' and exists ( ' || l_custom_select ||' = dun.object_id) ';
vPLSQL6 := vPLSQL6 || ' and exists ( ' || l_custom_select ||' = dun.dunning_object_id) ';
IEX_XML_PKG.update_row(
p_xml_request_id => l_xml_request_id
, p_status => l_status
, p_confirmation_mode => l_conf_mode
);
vPLSQL5 := 'select iet.payment_schedule_id, iet.stage_number ' ||
'from iex_dunnings dun, iex_dunning_transactions iet ' ||
'where dun.request_id = :1 ' ||
' and dun.dunning_id = iet.dunning_id ' ||
' and dun.delivery_status is null ' ||
' and iet.cust_trx_id is not null ' ||
' and iet.stage_number is not null ' ||
' and dun.object_type <> ''INX_INVOICES'' ';
if l_custom_select IS NOT NULL then
--vPLSQL5 := vPLSQL5 || ' and exists ( ' || l_custom_select ||' = dun.object_id) ';
vPLSQL5 := vPLSQL5 || ' and exists ( ' || l_custom_select ||' = dun.dunning_object_id) ';
update iex_delinquencies_all
set staged_dunning_level = l_stage_number
where payment_schedule_id = l_payment_schedule_id;
if l_update_cp_as_err then
if l_con_proc_mode = 'FINALDRAFT' then
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - You can''t run this request id: ' || p_parent_request_id || ' in PREVIEW mode, because request has been created in direct FINAL mode.');
l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'ERROR',
message => l_err_message);
l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
message => 'At least one dunning record is failed to process');
SELECT preference_value
FROM IEX_APP_PREFERENCES_VL
WHERE upper(PREFERENCE_NAME) = 'COLLECTIONS DUNNING LEVEL';
select dunning_level from iex_dunning_plans_vl
where dunning_plan_id = iex_dunning_plan_id;
select aging_bucket_id from iex_dunning_plans_vl
where dunning_plan_id = iex_dunning_plan_id;
l_con_update_re_st boolean;
select nvl(dunning_type,'DAYS_OVERDUE')
from IEX_DUNNING_PLANS_B
where dunning_plan_id = p_dunn_plan_id;
select dunning_mode
from iex_dunnings
where request_id = p_req_id;
select define_ou_running_level
from iex_questionnaire_items;
select collections_methods
from iex_questionnaire_items;
select business_level
from iex_questionnaire_items;
select collections_method
from iex_app_preferences_b where
org_id = p_org_id
and enabled_flag ='Y';
l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
message => 'Concurrent program failed to run as Dunning Plan level is '||l_running_level ||' does not match the current business level '||l_business_level);
l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
message => 'Concurrent program failed to run as collections method is set up as strategies');
l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
message => 'This opearting unit is not registered.Please check setup');
l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
message => 'Opearting Unit is Not Setup for Dunning please check Setup');
l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'ERROR',
message => ' When Dunning level is Account, value of profile "IEX: Minimum Dunning Amount Profile Check" can''t be "Bill To" or "Bill To and Account". ');
l_no_updated_rows number := 0;
l_con_update_re_st boolean;
select dunning_letter_set_id,
name,
description,
status,
grace_days,
dun_disputed_items,
include_unused_payments_flag,
dunning_type,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
from ar_dunning_letter_sets
where dunning_type = 'STAGED_DUNNING';
SELECT language_code
FROM fnd_languages
WHERE installed_flag IN ('B','I');
select dnb.dunning_plan_id dunning_plan_id,
dnb.dunning_level dunning_level,
dntl.name name,
dntl.description description,
dnb.mig_dunning_letter_set_id mig_dunning_letter_set_id
from iex_dunning_plans_b dnb,
iex_dunning_plans_tl dntl
where dnb.dunning_plan_id = dntl.dunning_plan_id
and dntl.language = userenv('LANG')
and dnb.dunning_type = 'STAGED_DUNNING'
and dnb.mig_dunning_letter_set_id is not null
and dnb.enabled_flag = 'Y'
and dnb.end_date is null
order by dnb.dunning_plan_id;
select dunning_letter_set_id,
dunning_line_num,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
dunning_letter_id,
include_current,
invoice_copies,
range_of_dunning_level_from,
range_of_dunning_level_to,
min_days_between_dunning
from AR_DUNNING_LETTER_SET_LINES
where dunning_letter_set_id = p_dunn_letter_set_id;
select template_id
from xdo_templates_vl
where template_code = 'IEXSTGDN';
select ar.staged_dunning_level,
ar.payment_schedule_id
from iex_delinquencies_all iex,
ar_payment_schedules_all ar
where iex.payment_schedule_id = ar.payment_schedule_id
and ar.staged_dunning_level is not null
and iex.staged_dunning_level is null
and iex.status in ('DELINQUENT','PREDELINQUENT')
and ar.status = 'OP';
select business_level
from iex_questionnaire_items
where questionnaire_item_id = 1;
UPDATE IEX_DUNNING_PLANS_B
SET LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID,
END_DATE = nvl(END_DATE,sysdate),
ENABLED_FLAG = 'N'
WHERE MIG_DUNNING_LETTER_SET_ID is not null
AND (ENABLED_FLAG = 'Y' OR END_DATE IS NULL);
select IEX_DUNNING_PLANS_S.nextval
into l_dunning_plan_id
from dual;
INSERT INTO IEX_DUNNING_PLANS_B
(DUNNING_PLAN_ID,
START_DATE,
ENABLED_FLAG,
AGING_BUCKET_ID,
SCORE_ID,
DUNNING_LEVEL,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
DUNNING_TYPE,
DUN_DISPUTED_ITEMS,
GRACE_DAYS,
INCLUDE_UNUSED_PAYMENTS_FLAG,
DUNNING_LETTER_SET_ID,
MIG_DUNNING_LETTER_SET_ID)
VALUES
(l_dunning_plan_id,
sysdate,
decode(dunn_letter_set_rec.status,'A','Y','N'),
null,
l_score_id,
l_business_level,
1,
dunn_letter_set_rec.creation_date,
dunn_letter_set_rec.created_by,
dunn_letter_set_rec.last_update_date,
dunn_letter_set_rec.last_updated_by,
dunn_letter_set_rec.last_update_login,
dunn_letter_set_rec.dunning_type,
dunn_letter_set_rec.dun_disputed_items,
dunn_letter_set_rec.grace_days,
dunn_letter_set_rec.include_unused_payments_flag,
dunn_letter_set_rec.dunning_letter_set_id,
dunn_letter_set_rec.dunning_letter_set_id);
INSERT INTO IEX_DUNNING_PLANS_TL
(DUNNING_PLAN_ID,
NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES
(l_dunning_plan_id,
dunn_letter_set_rec.name,
dunn_letter_set_rec.description,
fnd_languages.language_code, --'US',
'US',
dunn_letter_set_rec.created_by,
dunn_letter_set_rec.creation_date,
dunn_letter_set_rec.last_update_date,
dunn_letter_set_rec.last_updated_by,
dunn_letter_set_rec.last_update_login);
select IEX_AG_DN_XREF_S.nextval
into l_ag_dn_xref_id
from dual;
INSERT INTO IEX_AG_DN_XREF
(AG_DN_XREF_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
OBJECT_VERSION_NUMBER,
CALLBACK_FLAG,
CALLBACK_DAYS,
FM_METHOD,
SCORE_RANGE_LOW,
SCORE_RANGE_HIGH,
TEMPLATE_ID,
DUNNING_LEVEL,
XDO_TEMPLATE_ID,
DUNNING_PLAN_ID,
INVOICE_COPIES,
MIN_DAYS_BETWEEN_DUNNING,
RANGE_OF_DUNNING_LEVEL_FROM,
RANGE_OF_DUNNING_LEVEL_TO)
VALUES
(l_ag_dn_xref_id,
dunn_letter_set_lines_rec.last_update_date,
dunn_letter_set_lines_rec.last_updated_by,
dunn_letter_set_lines_rec.last_update_login,
dunn_letter_set_lines_rec.created_by,
dunn_letter_set_lines_rec.creation_date,
1,
'N',
null,
'PRINTER',
1,
100,
l_template_id,
l_business_level,
l_template_id,
l_dunning_plan_id,
dunn_letter_set_lines_rec.invoice_copies,
dunn_letter_set_lines_rec.min_days_between_dunning,
dunn_letter_set_lines_rec.range_of_dunning_level_from,
dunn_letter_set_lines_rec.range_of_dunning_level_to);
UPDATE IEX_DUNNING_PLANS_B
SET INCLUDE_CURRENT = l_include_current
WHERE
DUNNING_PLAN_ID = l_dunning_plan_id;
l_staged_dunning_level.delete;
l_payment_schedule_id.delete;
--It will update only the records that have staged_dunning_level as NULL
BEGIN
OPEN c_staged_dunning_level;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - No of rows updated in iex_delinquencies_all is: ' || l_no_updated_rows);
FND_FILE.PUT_LINE(FND_FILE.LOG, '3. Copied Transaction''s stage level from AR to Advanced Collections. No of rows updated in iex_delinquencies_all table is: ' || l_no_updated_rows);
FND_FILE.PUT_LINE(FND_FILE.LOG, ' UPDATE IEX_DELINQUENCIES_ALL IEX ');
UPDATE IEX_DELINQUENCIES_ALL IEX
SET STAGED_DUNNING_LEVEL = l_staged_dunning_level (I)
WHERE PAYMENT_SCHEDULE_ID = l_payment_schedule_id(I);
l_no_updated_rows := l_no_updated_rows + l_staged_dunning_level.count;
l_staged_dunning_level.delete;
l_payment_schedule_id.delete;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' Rows updated in iex_delinquencies_all staged_dunning_level...');
--Update dunning_type as 'DAYS_OVERDUE' for existing records in IEX_DUNNING_PLANS_B table.
update iex_dunning_plans_b
set dunning_type = 'DAYS_OVERDUE'
where dunning_type is null
and aging_bucket_id is not null;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Updated dunning_type as DAYS_OVERDUE for existing records in IEX_DUNNING_PLANS_B table.');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Profile IEX: IPP Printer Name value updated with NOPRINT ');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Profile IEX: IPP Printer Name updated failed ');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Profile IEX: AR Dunning to IEX Dunning Migrated? value updated with Y ');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Profile IEX: AR Dunning to IEX Dunning Migrated? updated failed ');
l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
message => 'Set Profile IEX: AR Dunning to IEX Dunning Migrated? value to No and then run the cp.');