[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select par_site.location_id
from
HZ_CUST_SITE_USES_ALL site_use,
HZ_CUST_ACCT_SITES_ALL acct_site,
hz_party_sites par_site
where
site_use.site_use_id = P_SITE_USE_ID and
acct_site.cust_acct_site_id = site_use.cust_acct_site_id and
par_site.party_site_id = acct_site.party_site_id;
select
decode(P_REL_TYPE, rel.relationship_type,
decode(point.contact_point_purpose,
'DUNNING', decode(point.primary_by_purpose, 'Y', 1, decode(point.primary_flag, 'Y', 2, 3
)
), 4
),
decode(point.contact_point_purpose,
'DUNNING', decode(point.primary_by_purpose, 'Y', 5, decode(point.primary_flag, 'Y', 6, 7
)
), 8
)
) Display_Order,
rel.party_id,
rel.subject_id,
point.contact_point_id,
rel.relationship_type
from HZ_RELATIONSHIPS rel,
hz_contact_points point
where rel.object_id = P_PARTY_ID and
rel.relationship_type in ('DUNNING', 'COLLECTIONS') and
rel.status = 'A' and
rel.party_id = point.owner_table_ID and
point.owner_table_name = 'HZ_PARTIES' and
upper(point.contact_point_type) = decode(P_CONTACT_POINT_TYPE, 'EMAIL', 'EMAIL', 'PHONE', 'PHONE', 'FAX', 'PHONE') and
nvl(point.phone_line_type, 'EMAIL') = decode(P_CONTACT_POINT_TYPE, 'EMAIL', 'EMAIL', 'PHONE', 'GEN', 'FAX', 'FAX') and
NVL(point.do_not_use_flag, 'N') = 'N' and
(point.status = 'A' OR point.status <> 'I')
order by Display_Order;
select
decode(P_REL_TYPE, rel.relationship_type, 1, 2) Display_Order,
rel.party_id,
rel.subject_id,
rel.relationship_type
from HZ_RELATIONSHIPS rel
where rel.object_id = P_PARTY_ID and
rel.relationship_type in ('DUNNING', 'COLLECTIONS') and
rel.status = 'A'
order by Display_Order;
select location_id
from hz_party_sites
where party_id = P_REL_PARTY_ID and
status in ('A', 'I');
select location_id
--Start for bug 8771065 gnramasa 6th-Aug-09
-- from ast_locations_v where party_site_id =
from ast_locations_v where party_site_id in
(select party_site_id
-- from hz_cust_acct_sites_all where cust_acct_site_id =
from hz_cust_acct_sites_all where cust_acct_site_id in
(select address_id
from ar_contacts_v where contact_party_id = P_CONTACT_ID
AND address_id is not null
AND status = 'A'));
select location_id
from ast_locations_v
where party_id = P_ORG_PARTY_ID and
primary_flag = 'Y';
select
decode(role_resp.responsibility_type,
'DUN', decode(role_resp.primary_flag, 'Y', 1, 2),
'BILL_TO', decode(role_resp.primary_flag, 'Y', 3, 4),
'INV', decode(role_resp.primary_flag, 'Y', 5, 6),
'SHIP_TO', decode(role_resp.primary_flag, 'Y', 7, 8), 9
) Display_Order,
role_resp.responsibility_type,
party.party_id,
sub_party.party_id
from
HZ_CUST_SITE_USES_ALL site_use,
HZ_CUST_ACCOUNT_ROLES acct_role,
HZ_ROLE_RESPONSIBILITY role_resp,
HZ_RELATIONSHIPS rel,
hz_parties party,
hz_parties sub_party
where
site_use.site_use_id = P_SITE_USE_ID and
acct_role.cust_acct_site_id = site_use.cust_acct_site_id and
acct_role.status = 'A' and
role_resp.cust_account_role_id = acct_role.cust_account_role_id and
acct_role.party_id = party.party_id and
party.status = 'A' and
rel.party_id = party.party_id and
rel.subject_type = 'PERSON' and
rel.status = 'A' and
decode(rel.object_type, 'PERSON', rel.directional_flag, 1) = decode(rel.object_type, 'PERSON', 'F', 1) and
sub_party.party_id = rel.subject_id and
sub_party.status = 'A'
order by Display_Order, sub_party.party_name;
select
decode(cont_point.contact_point_purpose,
'DUNNING', decode(cont_point.primary_flag, 'Y', 1, decode(cont_point.primary_by_purpose, 'Y', 2, 3)),
'COLLECTIONS', decode(cont_point.primary_flag, 'Y', 4, decode(cont_point.primary_by_purpose, 'Y', 5, 6)),
'BUSINESS', decode(cont_point.primary_flag, 'Y', 7, decode(cont_point.primary_by_purpose, 'Y', 8, 9)),
null, decode(cont_point.primary_flag, 'Y', 10, decode(cont_point.primary_by_purpose, 'Y', 11, 12))
) Display_Order
,cont_point.CONTACT_POINT_ID
from hz_contact_points cont_point
where
cont_point.owner_table_id = P_PARTY_ID and
cont_point.owner_table_name = 'HZ_PARTIES' and
cont_point.contact_point_type = decode(P_CONTACT_POINT_TYPE, 'EMAIL', 'EMAIL', 'PHONE', 'PHONE', 'FAX', 'PHONE') and
nvl(cont_point.phone_line_type, 'EMAIL') = decode(P_CONTACT_POINT_TYPE, 'EMAIL', 'EMAIL', 'PHONE', 'GEN', 'FAX', 'FAX') and
NVL(cont_point.do_not_use_flag, 'N') = 'N' and
(cont_point.status = 'A' OR cont_point.status <> 'I')
order by Display_Order;
select
decode(cont_point.contact_point_purpose,
'DUNNING', decode(cont_point.primary_flag, 'Y', 1, decode(cont_point.primary_by_purpose, 'Y', 2, 3)),
'COLLECTIONS', decode(cont_point.primary_flag, 'Y', 4, decode(cont_point.primary_by_purpose, 'Y', 5, 6)),
'BUSINESS', decode(cont_point.primary_flag, 'Y', 7, decode(cont_point.primary_by_purpose, 'Y', 8, 9)),
null, decode(cont_point.primary_flag, 'Y', 10, decode(cont_point.primary_by_purpose, 'Y', 11, 12))
) Display_Order
,cont_point.CONTACT_POINT_ID
from
HZ_CUST_SITE_USES_ALL site_use,
HZ_CUST_ACCT_SITES_ALL acct_site,
hz_contact_points cont_point
where
site_use.site_use_id = P_SITE_USE_ID and
acct_site.cust_acct_site_id = site_use.cust_acct_site_id and
cont_point.owner_table_id = acct_site.party_site_id and
cont_point.owner_table_name = 'HZ_PARTY_SITES' and
cont_point.contact_point_type = decode(P_CONTACT_POINT_TYPE, 'EMAIL', 'EMAIL', 'PHONE', 'PHONE', 'FAX', 'PHONE') and
nvl(cont_point.phone_line_type, 'EMAIL') = decode(P_CONTACT_POINT_TYPE, 'EMAIL', 'EMAIL', 'PHONE', 'GEN', 'FAX', 'FAX') and
NVL(cont_point.do_not_use_flag, 'N') = 'N' and
(cont_point.status = 'A' OR cont_point.status <> 'I')
order by Display_Order;
select par_site.location_id
from
HZ_CUST_SITE_USES_ALL site_use,
HZ_CUST_ACCT_SITES_ALL acct_site,
hz_party_sites par_site
where
site_use.site_use_id = P_SITE_USE_ID and
acct_site.cust_acct_site_id = site_use.cust_acct_site_id and
par_site.party_site_id = acct_site.party_site_id;
l_dun_contact_level := nvl(fnd_profile.value('IEX_DUNNING_CONTACT_SELECTION_METHOD'), 'ALL'); --Added for bug 6500750 gnramasa 13-Nov-07
select
decode(role_resp.responsibility_type,
'DUN', decode(role_resp.primary_flag, 'Y', 1, 2),
'BILL_TO', decode(role_resp.primary_flag, 'Y', 3, 4),
'INV', decode(role_resp.primary_flag, 'Y', 5, 6),
'SHIP_TO', decode(role_resp.primary_flag, 'Y', 7, 8), 9
) Display_Order,
role_resp.responsibility_type,
party.party_id,
sub_party.party_id,
rel.object_id
from
HZ_CUST_ACCOUNT_ROLES acct_role,
HZ_ROLE_RESPONSIBILITY role_resp,
HZ_RELATIONSHIPS rel,
hz_parties party,
hz_parties sub_party
where
acct_role.cust_account_id = P_CUST_ACCOUNT_ID and
acct_role.cust_acct_site_id is null and
acct_role.status = 'A' and
acct_role.cust_account_role_id = role_resp.cust_account_role_id and
acct_role.party_id = party.party_id and
party.status = 'A' and
rel.party_id = party.party_id and
rel.subject_type = 'PERSON' and
rel.status = 'A' and
decode(rel.object_type, 'PERSON', rel.directional_flag, 1) = decode(rel.object_type, 'PERSON', 'F', 1) and
sub_party.party_id = rel.subject_id and
sub_party.status = 'A'
order by Display_Order, sub_party.party_name;
select
decode(cont_point.contact_point_purpose,
'DUNNING', decode(cont_point.primary_flag, 'Y', 1, decode(cont_point.primary_by_purpose, 'Y', 2, 3)),
'COLLECTIONS', decode(cont_point.primary_flag, 'Y', 4, decode(cont_point.primary_by_purpose, 'Y', 5, 6)),
'BUSINESS', decode(cont_point.primary_flag, 'Y', 7, decode(cont_point.primary_by_purpose, 'Y', 8, 9)),
null, decode(cont_point.primary_flag, 'Y', 10, decode(cont_point.primary_by_purpose, 'Y', 11, 12))
) Display_Order
,cont_point.CONTACT_POINT_ID
from hz_contact_points cont_point
where
cont_point.owner_table_id = P_PARTY_ID and
cont_point.owner_table_name = 'HZ_PARTIES' and
cont_point.contact_point_type = decode(P_CONTACT_POINT_TYPE, 'EMAIL', 'EMAIL', 'PHONE', 'PHONE', 'FAX', 'PHONE') and
nvl(cont_point.phone_line_type, 'EMAIL') = decode(P_CONTACT_POINT_TYPE, 'EMAIL', 'EMAIL', 'PHONE', 'GEN', 'FAX', 'FAX') and
NVL(cont_point.do_not_use_flag, 'N') = 'N' and
(cont_point.status = 'A' OR cont_point.status <> 'I')
order by Display_Order;
select party_id from hz_cust_accounts where cust_account_id = P_CUST_ACCOUNT_ID;
select
decode(site_use.site_use_code,
'DUN', 1,
'BILL_TO', decode(site_use.primary_flag, 'Y', 2, 3)) display_order,
site_use.site_use_code,
par_site.location_id
from
hz_party_sites par_site,
HZ_CUST_ACCT_SITES_ALL acct_site,
HZ_CUST_SITE_USES_ALL site_use
where
acct_site.cust_account_id = P_CUST_ACCOUNT_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
par_site.party_site_id = acct_site.party_site_id and
par_site.status in ('A', 'I')
order by display_order;
select count(1)
from
hz_party_sites par_site,
HZ_CUST_ACCT_SITES_ALL acct_site,
HZ_CUST_SITE_USES_ALL site_use
where
acct_site.cust_account_id = P_CUST_ACCOUNT_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
par_site.party_site_id = acct_site.party_site_id and
par_site.status in ('A', 'I') and
site_use.site_use_code = 'BILL_TO' and
site_use.primary_flag <> 'Y';
select location_id
from ast_locations_v
where party_id = P_ORG_PARTY_ID and
primary_flag = 'Y';
l_dun_contact_level := nvl(fnd_profile.value('IEX_DUNNING_CONTACT_SELECTION_METHOD'), 'ALL'); --Added for bug 6500750 gnramasa 13-Nov-07
select
decode(site_use.site_use_code,
'DUN', decode(par_site.identifying_address_flag, 'Y', 1, 'N', 4),
'BILL_TO', decode(par_site.identifying_address_flag,
'Y', decode(site_use.primary_flag, 'Y', 2, 3),
'N', decode(site_use.primary_flag, 'Y', 5, 6))) Display_Order ,
acct_site.cust_account_id,
site_use.site_use_code,
par_site.identifying_address_flag,
site_use.primary_flag
from HZ_CUST_SITE_USES_ALL site_use,
HZ_CUST_ACCT_SITES_ALL acct_site,
hz_party_sites par_site
where par_site.party_id = P_PARTY_ID and
par_site.status in ('A', 'I') 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'
order by Display_Order;
select count(1)
from HZ_CUST_SITE_USES_ALL site_use,
HZ_CUST_ACCT_SITES_ALL acct_site,
hz_party_sites par_site
where par_site.party_id = P_PARTY_ID and
par_site.status in ('A', 'I') 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
site_use.site_use_code = P_SITE_USE_CODE and
par_site.identifying_address_flag = P_IDENT_FLAG and
site_use.primary_flag = P_PRIMARY_FLAG;
select party_id
from hz_cust_accounts
where cust_account_id = p_cust_account_id;
select cust.party_id
from hz_cust_accounts cust,
hz_cust_acct_sites_all acc_site,
hz_cust_site_uses_all site_use
where site_use.site_use_id = p_site_use_id and
site_use.cust_acct_site_id = acc_site.cust_acct_site_id and
acc_site.cust_account_id = cust.cust_account_id;
select party_cust_id, customer_site_use_id
from iex_delinquencies_all
where delinquency_id = p_delinquency_id;
select party_cust_id, cust_account_id, customer_site_use_id
from iex_delinquencies_all
where delinquency_id = p_delinquency_id;
select party_id
from hz_cust_accounts
where cust_account_id = p_cust_account_id;
select cust.party_id, cust.cust_account_id
from hz_cust_accounts cust,
hz_cust_acct_sites_all acc_site,
hz_cust_site_uses_all site_use
where site_use.site_use_id = p_site_use_id and
site_use.cust_acct_site_id = acc_site.cust_acct_site_id and
acc_site.cust_account_id = cust.cust_account_id;
select dispute_id
from IEX_DISPUTES
where cm_request_id = p_cm_request_id;
select customer_id
from ar_payment_schedules_all
where customer_trx_id = p_cust_trx_id;
WriteLog(l_api_name || ': Updated ' || l_fulfillment_bind_tbl(l_party_index).key_name || ' value in bind table');
WriteLog(l_api_name || ': Updated ' || l_fulfillment_bind_tbl(l_cust_account_index).key_name || ' value in bind table');
WriteLog(l_api_name || ': Updated ' || l_fulfillment_bind_tbl(l_party_index).key_name || ' value in bind table');
WriteLog(l_api_name || ': Updated ' || l_fulfillment_bind_tbl(l_site_use_index).key_name || ' value in bind table');
WriteLog(l_api_name || ': Updated ' || l_fulfillment_bind_tbl(l_party_index).key_name || ' value in bind table');
WriteLog(l_api_name || ': Updated ' || l_fulfillment_bind_tbl(l_cust_account_index).key_name || ' value in bind table');
WriteLog(l_api_name || ': Updated ' || l_fulfillment_bind_tbl(l_del_index).key_name || ' value in bind table');
WriteLog(l_api_name || ': Updated ' || l_fulfillment_bind_tbl(l_party_index).key_name || ' value in bind table');
WriteLog(l_api_name || ': Updated ' || l_fulfillment_bind_tbl(l_cust_account_index).key_name || ' value in bind table');
WriteLog(l_api_name || ': Updated ' || l_fulfillment_bind_tbl(l_site_use_index).key_name || ' value in bind table');
WriteLog(l_api_name || ': Updated ' || l_fulfillment_bind_tbl(l_location_index).key_name || ' value in bind table');
WriteLog(l_api_name || ': Updated ' || l_fulfillment_bind_tbl(l_contact_index).key_name || ' value in bind table');
WriteLog(l_api_name || ': Updated ' || l_fulfillment_bind_tbl(l_contact_point_index).key_name || ' value in bind table');
SELECT delinquency_id
FROM iex_delinquencies
WHERE delinquency_ID = IN_del_ID;
WriteLog(l_api_name || G_PKG_NAME || ' ' || l_api_name || ' -CreateAgDn:InsertRow');
IEX_AG_DN_PKG.insert_row(
px_rowid => l_rowid
, px_AG_DN_XREF_id => x_AG_DN_XREF_id
, p_last_update_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_created_by => FND_GLOBAL.USER_ID
, p_last_update_login => FND_GLOBAL.USER_ID
, p_aging_bucket_id => l_AG_DN_XREF_rec.aging_bucket_id
, p_aging_bucket_line_id => l_AG_DN_XREF_rec.aging_bucket_line_id
, p_callback_flag => l_AG_DN_XREF_rec.callback_flag
, p_callback_days => l_AG_DN_XREF_rec.callback_days
, p_fm_method => l_AG_DN_XREF_rec.fm_method
, p_dunning_level => l_AG_DN_XREF_rec.dunning_level
, p_template_id => l_AG_DN_XREF_rec.template_id
, p_xdo_template_id => l_AG_DN_XREF_rec.xdo_template_id
, p_score_range_low => l_AG_DN_XREF_rec.score_range_low
, p_score_range_high => l_AG_DN_XREF_rec.score_range_high
, p_object_version_number => l_AG_DN_XREF_rec.object_version_number
);
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_REC IN IEX_DUNNING_PUB.AG_DN_XREF_REC_TYPE,
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
CURSOR C_get_AG_DN_XREF_Rec (IN_AG_DN_XREF_ID NUMBER) is
select ROWID,
AG_DN_XREF_ID,
AGING_BUCKET_ID,
AGING_BUCKET_LINE_ID,
CALLBACK_FLAG,
CALLBACK_DAYS,
FM_METHOD,
TEMPLATE_ID,
XDO_TEMPLATE_ID,
SCORE_RANGE_LOW,
SCORE_RANGE_HIGH,
DUNNING_LEVEL,
OBJECT_VERSION_NUMBER ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_LOGIN
from iex_AG_DN_XREF
where AG_DN_XREF_id = in_AG_DN_XREF_id
FOR UPDATE NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_AG_DN';
SAVEPOINT UPDATE_AG_DN_PVT;
l_AG_DN_XREF_ref_rec.LAST_UPDATE_DATE,
l_AG_DN_XREF_ref_rec.LAST_UPDATED_BY,
l_AG_DN_XREF_ref_rec.CREATION_DATE,
l_AG_DN_XREF_ref_rec.CREATED_BY,
l_AG_DN_XREF_ref_rec.LAST_UPDATE_LOGIN;
FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
If (l_ag_Dn_xref_rec.last_update_date is NULL or
l_ag_Dn_xref_rec.last_update_date = FND_API.G_MISS_Date )
Then
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_ID');
FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateRow ');
IEX_AG_DN_PKG.update_row(
p_rowid => l_rowid
, p_AG_DN_XREF_id => p_AG_DN_XREF_id
, p_last_update_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_creation_date => l_AG_DN_XREF_rec.creation_date
, p_created_by => l_AG_DN_XREF_rec.created_by
, p_last_update_login => FND_GLOBAL.USER_ID
, p_aging_bucket_id => l_AG_DN_XREF_rec.aging_bucket_id
, p_aging_bucket_line_id => l_AG_DN_XREF_rec.aging_bucket_line_id
, p_callback_flag => l_AG_DN_XREF_rec.callback_flag
, p_callback_days => l_AG_DN_XREF_rec.callback_days
, p_fm_method => l_AG_DN_XREF_rec.fm_method
, p_template_id => l_AG_DN_XREF_rec.template_id
, p_xdo_template_id => l_AG_DN_XREF_rec.xdo_template_id
, p_score_range_low => l_AG_DN_XREF_rec.score_range_low
, p_score_range_high => l_AG_DN_XREF_rec.score_range_high
, p_dunning_level => l_AG_DN_XREF_rec.dunning_level
, p_object_version_number => l_ag_dn_xref_rec.object_version_number
);
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:End ');
ROLLBACK TO UPDATE_AG_DN_PVT;
ROLLBACK TO UPDATE_AG_DN_PVT;
ROLLBACK TO UPDATE_AG_DN_PVT;
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
CURSOR C_GET_AG_DN_XREF (IN_AG_DN_XREF_ID NUMBER) IS
SELECT AG_DN_XREF_ID
FROM IEX_AG_DN_XREF
WHERE AG_DN_XREF_ID = IN_AG_DN_XREF_ID;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_AG_DN';
SAVEPOINT DELETE_AG_DN_PVT;
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Delete Row');
IEX_AG_DN_PKG.Delete_Row(
p_AG_DN_XREF_ID => l_AG_DN_XREF_ID);
ROLLBACK TO DELETE_AG_DN_PVT;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - DeleteAgDn:error='||SQLERRM);
ROLLBACK TO DELETE_AG_DN_PVT;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - DeleteAgDn:error='||SQLERRM);
ROLLBACK TO DELETE_AG_DN_PVT;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - DeleteAgDn:error='||SQLERRM);
END Delete_AG_DN_XREF;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - InsertRow');
IEX_Dunnings_PKG.insert_row(
px_rowid => l_rowid
, px_dunning_id => x_dunning_id
, p_last_update_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_created_by => FND_GLOBAL.USER_ID
, p_last_update_login => FND_GLOBAL.USER_ID
-- , p_request_id => FND_GLOBAL.CONC_REQUEST_ID
, p_request_id => l_dunning_rec.request_id
, p_template_id => l_dunning_rec.template_id
, p_callback_yn => l_dunning_rec.callback_yn
, p_callback_date => l_dunning_rec.callback_date
, p_campaign_sched_id => l_dunning_rec.campaign_sched_id
, p_status => l_dunning_rec.status
, p_delinquency_id => l_dunning_rec.delinquency_id
, p_ffm_request_id => l_dunning_rec.ffm_request_id
, p_xml_request_id => l_dunning_rec.xml_request_id
, p_xml_template_id => l_dunning_rec.xml_template_id
, p_object_id => l_dunning_rec.object_id
, p_object_type => l_dunning_rec.object_type
, p_dunning_object_id => l_dunning_rec.dunning_object_id
, p_dunning_level => l_dunning_rec.dunning_level
, p_dunning_method => l_dunning_rec.dunning_method
, p_amount_due_remaining => l_dunning_rec.amount_due_remaining
, p_currency_code => l_dunning_rec.currency_code
, p_delivery_status => l_dunning_rec.delivery_status
, p_parent_dunning_id => l_dunning_rec.PARENT_DUNNING_ID
, p_dunning_plan_id => l_dunning_rec.dunning_plan_id
, p_contact_destination => l_dunning_rec.contact_destination
, p_contact_party_id => l_dunning_rec.contact_party_id
, p_dunning_mode => l_dunning_rec.dunning_mode
, p_confirmation_mode => l_dunning_rec.confirmation_mode
, p_org_id => l_dunning_rec.org_id
, p_ag_dn_xref_id => l_dunning_rec.ag_dn_xref_id
, p_correspondence_date => nvl(l_dunning_rec.correspondence_date,trunc(sysdate))
, p_addt_xml_template_id => l_dunning_rec.addt_xml_template_id --added for bug 9970624 gnramasa 4th Aug 10
, p_addt_delivery_status => l_dunning_rec.addt_delivery_status --added for bug 9970624 gnramasa 4th Aug 10
, p_as_of_date => nvl(l_dunning_rec.as_of_date,trunc(sysdate))
);
select ag_dn_xref_id,
range_of_dunning_level_from,
range_of_dunning_level_to,
min_days_between_dunning
from iex_ag_dn_xref
where AG_DN_XREF_ID = p_ag_dn_xref_id
order by AG_DN_XREF_ID;
select nvl(dunn.include_current ,'N'),
nvl(dunn.include_unused_payments_flag,'N')
from iex_ag_dn_xref xref,
iex_dunning_plans_b dunn
where AG_DN_XREF_ID = p_ag_dn_xref_id
and xref.dunning_plan_id = dunn.dunning_plan_id;
select site_use_code into t_code from hz_cust_site_uses_all where site_use_id = l_object_id;
vPLSQL := 'select del.delinquency_id, ' ||
' del.transaction_id, ' ||
' del.payment_schedule_id ' ||
' from iex_delinquencies del, ' ||
' ar_payment_schedules arp ' ||
' where del.payment_schedule_id = arp.payment_schedule_id ' ||
' and del.status in (''DELINQUENT'',''PREDELINQUENT'') ' ||
' and del.staged_dunning_level is NULL ' ||
' and arp.invoice_currency_code = :p_inv_curr ' ||
' and (trunc(arp.due_date) + :p_min_days_bw_dun) <= :p_corr_date ' ||
' and (trunc(arp.due_date) + :p_gra_days) <= :p_corr_date ' ||
' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ';
' (SELECT NVL(min(min_dunning_invoice_amount),0) '||
' FROM hz_cust_profile_amts '||
' WHERE site_use_id IS NULL '||
' AND cust_account_id = arp.customer_id '||
' AND currency_code = arp.invoice_currency_code),0) '||
' order by del.payment_schedule_id';
' and arp.amount_due_remaining >= nvl ((select nvl(min(min_dunning_invoice_amount),0) '||
' from hz_cust_profile_amts '||
' where site_use_id = arp.CUSTOMER_SITE_USE_ID '||
' and currency_code = arp.invoice_currency_code),0) '||
' order by del.payment_schedule_id';
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - InsertRow');
IEX_Dunnings_PKG.insert_staged_dunning_row(
px_rowid => l_rowid
, px_dunning_trx_id => x_dunning_trx_id
, p_dunning_id => p_dunning_id
, p_cust_trx_id => l_transaction_id
, p_payment_schedule_id => l_payment_schedule_id
, p_ag_dn_xref_id => p_ag_dn_xref_id
, p_stage_number => i
, p_created_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_last_update_date => sysdate
, p_last_update_login => FND_GLOBAL.USER_ID
, p_object_version_number => 1.0
);
update iex_delinquencies_all
set staged_dunning_level = i
where delinquency_id = l_delinquency_id;
--reset the x_dunning_trx_id, so that will get new no when inserting 2nd record.
x_dunning_trx_id := null;
vPLSQL1 := 'select del.delinquency_id, ' ||
' del.transaction_id, ' ||
' del.payment_schedule_id ' ||
' from iex_delinquencies del, ' ||
' ar_payment_schedules arp ' ||
' where del.payment_schedule_id = arp.payment_schedule_id ' ||
' and del.status = ''CURRENT'' ' ||
' and del.staged_dunning_level is NULL ' ||
' and arp.status = ''OP'' ' ||
' and (arp.class = ''INV'' or arp.class = ''BR'') ' || -- Bills Receivables change
' and arp.invoice_currency_code = :p_inv_curr ' ||
' and (trunc(arp.due_date) + :p_min_days_bw_dun) <= :p_corr_date ' ||
' and (trunc(arp.due_date) + :p_gra_days) <= :p_corr_date ' ||
' and arp.amount_in_dispute >= decode(:p_include_dis_items, ''Y'', arp.amount_due_remaining, (arp.amount_due_original + 1)) ';
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - InsertRow');
IEX_Dunnings_PKG.insert_staged_dunning_row(
px_rowid => l_rowid
, px_dunning_trx_id => x_dunning_trx_id
, p_dunning_id => p_dunning_id
, p_cust_trx_id => l_transaction_id
, p_payment_schedule_id => l_payment_schedule_id
, p_ag_dn_xref_id => p_ag_dn_xref_id
, p_stage_number => i
, p_created_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_last_update_date => sysdate
, p_last_update_login => FND_GLOBAL.USER_ID
, p_object_version_number => 1.0
);
update iex_delinquencies_all
set staged_dunning_level = i
where delinquency_id = l_delinquency_id;
--reset the x_dunning_trx_id, so that will get new no when inserting 2nd record.
x_dunning_trx_id := null;
vPLSQL := 'select del.delinquency_id, ' ||
' del.transaction_id, ' ||
' del.payment_schedule_id ' ||
' from iex_delinquencies del ' ||
' ,ar_payment_schedules arp ' ||
' where ' ||
' del.payment_schedule_id = arp.payment_schedule_id and ' ||
' del.status in (''DELINQUENT'',''PREDELINQUENT'') ' ||
' and del.staged_dunning_level = :p_stage_no ' ||
' and arp.invoice_currency_code = :p_inv_curr ' ||
' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
' and nvl( ' ||
' ( ' ||
' (select trunc(correspondence_date) from iex_dunnings ' ||
' where dunning_id = ' ||
' (select max(iet.DUNNING_ID) from iex_dunning_transactions iet, ' ||
' iex_dunnings dunn ' ||
' where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id ' ||
' and dunn.dunning_id = iet.dunning_id ' ||
' and ((dunn.dunning_mode = ''DRAFT'' and dunn.confirmation_mode = ''CONFIRMED'') ' ||
' OR (dunn.dunning_mode = ''FINAL'')) ' ||
' and iet.STAGE_NUMBER = :p_stage_no and dunn.delivery_status is null)) ' ||
' + :p_min_days_bw_dun ) ' ||
' , :p_corr_date ) ' ||
' <= :p_corr_date ';
' (SELECT NVL(min(min_dunning_invoice_amount),0) '||
' FROM hz_cust_profile_amts '||
' WHERE site_use_id IS NULL '||
' AND cust_account_id = arp.customer_id '||
' AND currency_code = arp.invoice_currency_code),0) '||
' order by del.payment_schedule_id';
' and arp.amount_due_remaining >= nvl ((select nvl(min(min_dunning_invoice_amount),0) '||
' from hz_cust_profile_amts '||
' where site_use_id = arp.CUSTOMER_SITE_USE_ID '||
' and currency_code = arp.invoice_currency_code),0) '||
' order by del.payment_schedule_id';
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - InsertRow');
IEX_Dunnings_PKG.insert_staged_dunning_row(
px_rowid => l_rowid
, px_dunning_trx_id => x_dunning_trx_id
, p_dunning_id => p_dunning_id
, p_cust_trx_id => l_transaction_id
, p_payment_schedule_id => l_payment_schedule_id
, p_ag_dn_xref_id => p_ag_dn_xref_id
, p_stage_number => i
, p_created_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_last_update_date => sysdate
, p_last_update_login => FND_GLOBAL.USER_ID
, p_object_version_number => 1.0
);
update iex_delinquencies_all
set staged_dunning_level = i
where delinquency_id = l_delinquency_id;
--reset the x_dunning_trx_id, so that will get new no when inserting 2nd record.
x_dunning_trx_id := null;
vPLSQL1 := 'select del.delinquency_id, ' ||
' del.transaction_id, ' ||
' del.payment_schedule_id ' ||
' from iex_delinquencies del ' ||
' ,ar_payment_schedules arp ' ||
' where ' ||
' del.payment_schedule_id = arp.payment_schedule_id and ' ||
' del.status = ''CURRENT'' ' ||
' and del.staged_dunning_level = :p_stage_no ' ||
' and arp.status = ''OP'' ' ||
' and (arp.class = ''INV'' or arp.class = ''BR'') ' || -- Bills Receivables change
' and arp.invoice_currency_code = :p_inv_curr ' ||
' and arp.amount_in_dispute >= decode(:p_include_dis_items, ''Y'', arp.amount_due_remaining, (arp.amount_due_original + 1)) ' ||
' and nvl( ' ||
' ( ' ||
' (select trunc(correspondence_date) from iex_dunnings ' ||
' where dunning_id = ' ||
' (select max(iet.DUNNING_ID) from iex_dunning_transactions iet, ' ||
' iex_dunnings dunn ' ||
' where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id ' ||
' and dunn.dunning_id = iet.dunning_id ' ||
' and ((dunn.dunning_mode = ''DRAFT'' and dunn.confirmation_mode = ''CONFIRMED'') ' ||
' OR (dunn.dunning_mode = ''FINAL'')) ' ||
' and iet.STAGE_NUMBER = :p_stage_no and dunn.delivery_status is null)) ' ||
' + :p_min_days_bw_dun ) ' ||
' , :p_corr_date ) ' ||
' <= :p_corr_date';
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - InsertRow');
IEX_Dunnings_PKG.insert_staged_dunning_row(
px_rowid => l_rowid
, px_dunning_trx_id => x_dunning_trx_id
, p_dunning_id => p_dunning_id
, p_cust_trx_id => l_transaction_id
, p_payment_schedule_id => l_payment_schedule_id
, p_ag_dn_xref_id => p_ag_dn_xref_id
, p_stage_number => i
, p_created_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_last_update_date => sysdate
, p_last_update_login => FND_GLOBAL.USER_ID
, p_object_version_number => 1.0
);
update iex_delinquencies_all
set staged_dunning_level = i
where delinquency_id = l_delinquency_id;
--reset the x_dunning_trx_id, so that will get new no when inserting 2nd record.
x_dunning_trx_id := null;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Insert current invoices');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Start insert current invoices');
vPLSQL2 := 'select arp.customer_trx_id, ' ||
' arp.payment_schedule_id ' ||
' from ar_payment_schedules arp, ' ||
' hz_cust_accounts hca ' ||
' where arp.customer_id = hca.cust_account_id ' ||
' and hca.party_id = :p_party_id ' ||
' and trunc(arp.due_date) > trunc(:p_corr_date) ' ||
' and arp.status = ''OP'' ' ||
' and arp.amount_due_remaining <> 0 ' ||--' and arp.amount_due_remaining > 0 ' || Changed to fix 12552027 by snuthala 5/19/2011
' and (arp.class = ''INV'' or arp.class = ''BR'') ' || -- Bills Receivables change
' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
' and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ||
' order by arp.payment_schedule_id';
vPLSQL2 := 'select arp.customer_trx_id, ' ||
' arp.payment_schedule_id ' ||
' from ar_payment_schedules arp ' ||
' where arp.customer_id = :p_cust_acct_id ' ||
' and trunc(arp.due_date) > trunc(:p_corr_date) ' ||
' and arp.status = ''OP'' ' ||
' and arp.amount_due_remaining <> 0 ' ||--' and arp.amount_due_remaining > 0 ' || Changed to fix 12552027 by snuthala 5/19/2011
' and (arp.class = ''INV'' or arp.class = ''BR'') ' || -- Bills Receivables change
' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
' and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'') ' ||
' order by arp.payment_schedule_id';
vPLSQL2 := 'select arp.customer_trx_id, ' ||
' arp.payment_schedule_id ' ||
' from ar_payment_schedules arp ' ||
-- ' where arp.customer_site_use_id = :p_site_use_id ' || -- Bills Receivables
' where arp.customer_site_use_id IN ( :p_site_use_id, '||t_id||')' || -- Bills Receivables
' and trunc(arp.due_date) > trunc(:p_corr_date) ' ||
' and arp.status = ''OP'' ' ||
' and arp.amount_due_remaining <> 0 ' ||--' and arp.amount_due_remaining > 0 ' || Changed to fix 12552027 by snuthala 5/19/2011
' and (arp.class = ''INV'' or arp.class = ''BR'') ' || -- Bills Receivables change
' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
' and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'') ' ||
' order by arp.payment_schedule_id';
IEX_Dunnings_PKG.insert_staged_dunning_row(
px_rowid => l_rowid
, px_dunning_trx_id => x_dunning_trx_id
, p_dunning_id => p_dunning_id
, p_cust_trx_id => l_customer_trx_id
, p_payment_schedule_id => l_payment_schedule_id
, p_ag_dn_xref_id => p_ag_dn_xref_id
, p_stage_number => null
, p_created_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_last_update_date => sysdate
, p_last_update_login => FND_GLOBAL.USER_ID
, p_object_version_number => 1.0
);
--reset the x_dunning_trx_id, so that will get new no when inserting 2nd record.
x_dunning_trx_id := null;
vPLSQL := 'select del.delinquency_id, ' ||
' del.transaction_id, ' ||
' del.payment_schedule_id ' ||
' from iex_delinquencies del, ' ||
' ar_payment_schedules arp ' ||
' where del.payment_schedule_id = arp.payment_schedule_id ' ||
' and del.status in (''DELINQUENT'',''PREDELINQUENT'') ' ||
' and del.staged_dunning_level is NULL ' ||
' and (trunc(arp.due_date) + :p_min_days_bw_dun) > :p_corr_date ' ||
' and (trunc(arp.due_date) + :p_gra_days) > :p_corr_date ' ||
' and (trunc(arp.due_date) + 0) < :p_corr_date ' ||
--' and (trunc(arp.due_date) + 0) <= :p_corr_date ' ||
' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ';
' 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 ) ' ||
' order by del.payment_schedule_id';
' (SELECT NVL(min(min_dunning_invoice_amount),0) '||
' FROM hz_cust_profile_amts '||
' WHERE site_use_id IS NULL '||
' AND cust_account_id = arp.customer_id '||
' AND currency_code = arp.invoice_currency_code),0) '||
' 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 ) ' ||
' order by del.payment_schedule_id';
' and arp.amount_due_remaining >= nvl ((select nvl(min(min_dunning_invoice_amount),0) '||
' from hz_cust_profile_amts '||
' where site_use_id = arp.CUSTOMER_SITE_USE_ID '||
' and currency_code = arp.invoice_currency_code),0) '||
' 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 ) ' ||
' order by del.payment_schedule_id';
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - InsertRow');
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;
IEX_Dunnings_PKG.insert_staged_dunning_row(
px_rowid => l_rowid
, px_dunning_trx_id => x_dunning_trx_id
, p_dunning_id => p_dunning_id
, p_cust_trx_id => l_transaction_id
, p_payment_schedule_id => l_payment_schedule_id
, p_ag_dn_xref_id => p_ag_dn_xref_id
, p_stage_number => null
, p_created_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_last_update_date => sysdate
, p_last_update_login => FND_GLOBAL.USER_ID
, p_object_version_number => 1.0
);
--reset the x_dunning_trx_id, so that will get new no when inserting 2nd record.
x_dunning_trx_id := null;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - End insert current invoices');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Insert unapplied receipts and On Account Credit memos');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Start insert unapplied receipts');
vPLSQL2 := 'select arp.payment_schedule_id ' ||
' from ar_payment_schedules arp, ' ||
' hz_cust_accounts hca ' ||
' where arp.customer_id = hca.cust_account_id ' ||
' and hca.party_id = :p_party_id ' ||
' and arp.status = ''OP'' ' ||
' and arp.amount_due_remaining <> 0 ' ||
' and arp.class = ''PMT'' ' ||
' order by arp.payment_schedule_id';
vPLSQL2 := 'select arp.payment_schedule_id ' ||
' from ar_payment_schedules arp ' ||
' where arp.customer_id = :p_cust_acct_id ' ||
' and arp.status = ''OP'' ' ||
' and arp.amount_due_remaining <> 0 ' ||
' and arp.class = ''PMT'' ' ||
' order by arp.payment_schedule_id';
vPLSQL2 := 'select arp.payment_schedule_id ' ||
' from ar_payment_schedules arp ' ||
-- ' where arp.customer_site_use_id IN ( :p_site_use_id,24388) ' ||
' where arp.customer_site_use_id IN ( :p_site_use_id, '||t_id||')' || -- Bills Receivables
' and arp.status = ''OP'' ' ||
' and arp.amount_due_remaining <> 0 ' ||
' and arp.class = ''PMT'' ' ||
' order by arp.payment_schedule_id';
IEX_Dunnings_PKG.insert_staged_dunning_row(
px_rowid => l_rowid
, px_dunning_trx_id => x_dunning_trx_id
, p_dunning_id => p_dunning_id
--, p_cust_trx_id => l_customer_trx_id
, p_payment_schedule_id => l_payment_schedule_id
, p_ag_dn_xref_id => p_ag_dn_xref_id
, p_stage_number => null
, p_created_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_last_update_date => sysdate
, p_last_update_login => FND_GLOBAL.USER_ID
, p_object_version_number => 1.0
);
--reset the x_dunning_trx_id, so that will get new no when inserting 2nd record.
x_dunning_trx_id := null;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - End insert unapplied receipts');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Start insert On Account Credit memos');
vPLSQL2 := 'select arp.customer_trx_id customer_trx_id, ' ||
' arp.payment_schedule_id ' ||
' from ar_payment_schedules arp, ' ||
' hz_cust_accounts hca ' ||
' where arp.customer_id = hca.cust_account_id ' ||
' and hca.party_id = :p_party_id ' ||
' and arp.amount_due_remaining <> 0 ' ||
' and arp.class =''CM'' ' ||
' and arp.status=''OP'' ';
vPLSQL2 := 'select arp.customer_trx_id customer_trx_id, ' ||
' arp.payment_schedule_id ' ||
' from ar_payment_schedules arp ' ||
' where arp.customer_id = :p_cust_acct_id ' ||
' and arp.amount_due_remaining <> 0 ' ||
' and arp.class =''CM'' ' ||
' and arp.status=''OP'' ';
vPLSQL2 := 'select arp.customer_trx_id customer_trx_id, ' ||
' arp.payment_schedule_id ' ||
' from ar_payment_schedules arp ' ||
-- ' where arp.customer_site_use_id IN ( :p_site_use_id,24388) ' ||
' where arp.customer_site_use_id IN ( :p_site_use_id, '||t_id||')' || -- Bills Receivabless
' and arp.amount_due_remaining <> 0 ' ||
' and arp.class =''CM'' ' ||
' and arp.status=''OP'' ';
IEX_Dunnings_PKG.insert_staged_dunning_row(
px_rowid => l_rowid
, px_dunning_trx_id => x_dunning_trx_id
, p_dunning_id => p_dunning_id
, p_cust_trx_id => l_customer_trx_id
, p_payment_schedule_id => l_payment_schedule_id
, p_ag_dn_xref_id => p_ag_dn_xref_id
, p_stage_number => null
, p_created_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_last_update_date => sysdate
, p_last_update_login => FND_GLOBAL.USER_ID
, p_object_version_number => 1.0
);
--reset the x_dunning_trx_id, so that will get new no when inserting 2nd record.
x_dunning_trx_id := null;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - End insert On Account Credit memos');
Procedure Update_Dunning
(p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
P_Dunning_REC IN IEX_DUNNING_PUB.DUNNING_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
--Start adding for bug 8489610 by gnramasa 14-May-09
--Start adding for bug 9970624 gnramasa 4th Aug 10
CURSOR C_get_DUNNING_Rec (IN_DUNNING_ID NUMBER) is
select ROWID,
DUNNING_ID,
TEMPLATE_ID,
CALLBACK_YN,
CALLBACK_DATE,
CAMPAIGN_SCHED_ID,
STATUS,
DELINQUENCY_ID,
FFM_REQUEST_ID,
XML_REQUEST_ID,
XML_TEMPLATE_ID,
OBJECT_ID,
OBJECT_TYPE,
DUNNING_OBJECT_ID,
DUNNING_LEVEL,
DUNNING_METHOD,
AMOUNT_DUE_REMAINING,
CURRENCY_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_LOGIN,
delivery_status,
PARENT_DUNNING_ID,
financial_charge, -- bug 3955222
letter_name, -- bug 3955222
interest_amt, -- bug 3955222
dunning_plan_id, -- bug 3955222
contact_destination, -- bug 3955222
contact_party_id, -- bug 3955222
dunning_mode,
confirmation_mode,
request_id,
ag_dn_xref_id,
correspondence_date,
addt_xml_template_id,
addt_delivery_status
from iex_DUNNINGS
where dunning_id = in_dunning_id
FOR UPDATE NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_DUNNING';
SAVEPOINT UPDATE_dunning_PVT;
l_DUNNING_REF_REC.LAST_UPDATE_DATE,
l_DUNNING_REF_REC.LAST_UPDATED_BY,
l_DUNNING_REF_REC.CREATION_DATE,
l_DUNNING_REF_REC.CREATED_BY,
l_DUNNING_REF_REC.LAST_UPDATE_LOGIN,
l_DUNNING_REF_REC.delivery_status,
l_DUNNING_REF_REC.parent_dunning_id,
l_DUNNING_REF_REC.financial_charge, -- bug 3955222
l_DUNNING_REF_REC.letter_name, -- bug 3955222
l_DUNNING_REF_REC.interest_amt, -- bug 3955222
l_DUNNING_REF_REC.dunning_plan_id, -- bug 3955222
l_DUNNING_REF_REC.contact_destination, -- bug 3955222
l_DUNNING_REF_REC.contact_party_id, -- bug 3955222
l_DUNNING_REF_REC.dunning_mode,
l_DUNNING_REF_REC.confirmation_mode,
l_DUNNING_REF_REC.request_id,
l_DUNNING_REF_REC.ag_dn_xref_id,
l_DUNNING_REF_REC.correspondence_date,
l_DUNNING_REF_REC.addt_xml_template_id,
l_DUNNING_REF_REC.addt_delivery_status;
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
If (l_dunning_rec.last_update_date is NULL or
l_dunning_rec.last_update_date = FND_API.G_MISS_Date )
Then
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_ID');
FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Update Row');
IEX_DUNNINGS_PKG.Update_Row(
p_rowid => l_rowid
, p_dunning_id => l_dunning_rec.dunning_id
, p_last_update_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_creation_date => l_dunning_rec.creation_date
, p_created_by => l_dunning_rec.created_by
, p_last_update_login => FND_GLOBAL.USER_ID
--, p_request_id => FND_GLOBAL.CONC_REQUEST_ID
, p_request_id => l_dunning_rec.request_id
, p_template_id => l_dunning_rec.template_id
, p_callback_yn => l_dunning_rec.callback_yn
, p_callback_date => l_dunning_rec.callback_date
, p_campaign_sched_id => l_dunning_rec.campaign_sched_id
, p_status => l_dunning_rec.status
, p_delinquency_id => l_dunning_rec.delinquency_id
, p_ffm_request_id => l_dunning_rec.ffm_request_id
, p_xml_request_id => l_dunning_rec.xml_request_id
, p_xml_template_id => l_dunning_rec.xml_template_id
, p_object_id => l_dunning_rec.object_id
, p_object_type => l_dunning_rec.object_type
, p_dunning_object_id => l_dunning_rec.dunning_object_id
, p_dunning_level => l_dunning_rec.dunning_level
, p_dunning_method => l_dunning_rec.dunning_method
, p_amount_due_remaining => l_dunning_rec.amount_due_remaining
, p_currency_code => l_dunning_rec.currency_code
, p_delivery_status => l_dunning_rec.delivery_status
, p_parent_dunning_id => l_dunning_rec.PARENT_DUNNING_ID
, p_financial_charge => l_dunning_rec.financial_charge -- bug 3955222
, p_letter_name => l_dunning_rec.letter_name -- bug 3955222
, p_interest_amt => l_dunning_rec.interest_amt -- bug 3955222
, p_dunning_plan_id => l_dunning_rec.dunning_plan_id -- bug 3955222
, p_contact_destination => l_dunning_rec.contact_destination -- bug 3955222
, p_contact_party_id => l_dunning_rec.contact_party_id -- bug 3955222
, p_dunning_mode => l_dunning_rec.dunning_mode
, p_confirmation_mode => l_dunning_rec.confirmation_mode
, p_ag_dn_xref_id => l_dunning_rec.ag_dn_xref_id
, p_correspondence_date => l_dunning_rec.correspondence_date
, p_addt_xml_template_id => l_dunning_rec.addt_xml_template_id
, p_addt_delivery_status => l_dunning_rec.addt_delivery_status
);
ROLLBACK TO UPDATE_DUNNING_PVT;
ROLLBACK TO UPDATE_DUNNING_PVT;
ROLLBACK TO UPDATE_DUNNING_PVT;
END Update_DUNNING;
clchang update 10/16/2002 -
Send Dunning can be in Customer, Account and Delinquency levels in 11.5.9;
clchang update 04/21/2003 -
new level 'BILL_TO' in 11.5.10.
*=========================================================================*/
Procedure Send_Level_Dunning
(p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_running_level IN VARCHAR2,
p_dunning_plan_id in number,
p_resend_flag IN VARCHAR2,
p_delinquencies_tbl IN IEX_DELINQUENCY_PUB.DELINQUENCY_TBL_TYPE,
p_parent_request_id IN NUMBER,
p_dunning_mode IN VARCHAR2, -- added by gnramasa for bug 8489610 14-May-09
p_confirmation_mode IN VARCHAR2, -- added by gnramasa for bug 8489610 14-May-09
p_correspondence_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR C_GET_DEL (IN_del_ID NUMBER) IS
SELECT delinquency_ID
FROM IEX_DELINQUENCIES
WHERE delinquency_ID = in_del_ID;
SELECT a.score_value
FROM IEX_SCORE_HISTORIES a
, IEX_DUNNING_PLANS_VL c -- bug 4914799 ctlee 12/30/2005
WHERE a.score_object_ID = in_ID
AND a.score_object_code = IN_CODE
and c.score_id = a.score_id -- bug 4914799 ctlee 12/30/2005
and c.dunning_plan_id = p_dunning_plan_id -- bug 4914799 ctlee 12/30/2005
AND a.creation_date = (select max(b.creation_date)
from iex_score_histories b
where b.score_object_id = in_id
AND b.score_object_code = IN_CODE
AND b.score_id = a.score_id );
SELECT x.ag_dn_xref_id,
x.template_id,
x.xdo_template_id,
x.fm_method,
upper(x.callback_flag),
x.callback_days
FROM IEX_AG_DN_XREF x,
ar_aging_buckets ar,
iex_dunning_plans_vl d
WHERE x.aging_bucket_line_ID = l_line_ID
and x.dunning_plan_id = p_dunning_plan_id
AND l_score between x.score_range_low and x.score_range_high
AND x.aging_bucket_id = ar.aging_bucket_id
and ar.aging_bucket_id = d.aging_bucket_id
AND ar.status = 'A'
AND x.dunning_level = IN_LEVEL ;
select 'Active'
from xdo_templates_vl xdo
where xdo.template_id = l_template_id
and trunc(sysdate) >= TRUNC (NVL(xdo.start_date, sysdate))
and trunc(sysdate) < TRUNC(NVL(xdo.end_date, sysdate + 1));
select template_name
from xdo_templates_vl xdo
where xdo.template_id = l_template_id;
select sum(a.amount_in_dispute) - sum(a.amount_due_remaining)
from iex_delinquencies d
,ar_payment_schedules a
where a.payment_schedule_id = d.payment_schedule_id
and d.party_cust_id = nvl(p_party_id, d.party_cust_id)
and d.cust_account_id = nvl(P_CUST_ACCOUNT_ID, d.cust_account_id )
and d.customer_site_use_id = nvl(p_site_use_id, d.customer_site_use_id )
and d.status IN ('DELINQUENT', 'PREDELINQUENT');
select sum(a.amount_in_dispute) - sum(a.amount_due_remaining)
from iex_delinquencies d
,ar_payment_schedules a
where a.payment_schedule_id = d.payment_schedule_id
and d.party_cust_id = p_party_id
and d.status IN ('DELINQUENT', 'PREDELINQUENT');
select sum(a.amount_in_dispute) - sum(a.amount_due_remaining)
from iex_delinquencies d
,ar_payment_schedules a
where a.payment_schedule_id = d.payment_schedule_id
and d.cust_account_id = P_CUST_ACCOUNT_ID
and d.status IN ('DELINQUENT', 'PREDELINQUENT');
select sum(a.amount_in_dispute) - sum(a.amount_due_remaining)
from iex_delinquencies d
,ar_payment_schedules a
where a.payment_schedule_id = d.payment_schedule_id
and d.customer_site_use_id = p_site_use_id
and d.status IN ('DELINQUENT', 'PREDELINQUENT');
SELECT count(1)
FROM ar_payment_schedules_all ps, iex_delinquencies_all del
WHERE del.party_cust_id=p_party_id
AND ps.payment_schedule_id = del.payment_schedule_id
AND ps.status = 'OP'
AND del.status IN ('DELINQUENT', 'PREDELINQUENT')
and not exists(select 1
from iex_promise_details pd where pd.delinquency_id=del.delinquency_id
and pd.status='COLLECTABLE'
and pd.state='PROMISE'
group by pd.delinquency_id
having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
SELECT count(1)
FROM ar_payment_schedules_all ps, iex_delinquencies_all del
WHERE del.cust_account_id=p_cust_account_id
AND ps.payment_schedule_id = del.payment_schedule_id
AND ps.status = 'OP'
AND del.status IN ('DELINQUENT', 'PREDELINQUENT')
and not exists(select 1
from iex_promise_details pd where pd.delinquency_id=del.delinquency_id
and pd.status='COLLECTABLE'
and pd.state='PROMISE'
group by pd.delinquency_id
having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
SELECT count(1)
FROM ar_payment_schedules_all ps, iex_delinquencies_all del
WHERE del.customer_site_use_id= p_site_use_id
AND ps.payment_schedule_id = del.payment_schedule_id
AND ps.status = 'OP'
AND del.status IN ('DELINQUENT', 'PREDELINQUENT')
and not exists(select 1
from iex_promise_details pd where pd.delinquency_id=del.delinquency_id
and pd.status='COLLECTABLE'
and pd.state='PROMISE'
group by pd.delinquency_id
having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
select nvl(count(*),0)
from iex_bankruptcies
where party_id = p_par_id
and (disposition_code in ('GRANTED','NEGOTIATION')
OR (disposition_code is NULL));
select nvl(dunn.INCLUDE_CURRENT,'N'),
nvl(dunn.grace_days ,'N'),
nvl(dunn.dun_disputed_items, 'N')
from iex_dunning_plans_b dunn
where dunning_plan_id = p_dunn_plan_id;
select nvl(sum(aps.acctd_amount_due_remaining),0)
from ar_payment_schedules_all aps,iex_delinquencies_all dd,hz_cust_accounts hzca
where dd.payment_schedule_id = aps.payment_schedule_id
and aps.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP', 'BR') -- Bills Receivables change
and aps.status='OP'
and (trunc(aps.due_date) + p_grace_days) <= p_corr_date
and nvl(aps.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(aps.amount_in_dispute,0), 0)
and aps.amount_due_remaining > 0
and aps.customer_id=hzca.cust_account_id
and aps.org_id=p_org_id
and hzca.party_id = p_party_id;
select nvl(sum(acctd_amount_due_remaining),0)
from ar_payment_schedules_all aps,hz_cust_accounts hzca
where aps.class IN ('CM','PMT')
and aps.status = 'OP'
and aps.customer_id = hzca.cust_account_id
and aps.org_id= p_org_id
and hzca.party_id = p_party_id;
select fnd_profile.value(nvl('IEX_ALLOW_DUN_FULL_PROMISE','N')) into l_allow_send from dual; -- Added for bug#8408162
vPLSQL1 := 'select count(*) from ( ' ||
' select del.delinquency_id, ' ||
' del.transaction_id, ' ||
' del.payment_schedule_id ' ||
' from iex_delinquencies del, ' ||
' ar_payment_schedules arp ' ||
' where del.payment_schedule_id = arp.payment_schedule_id ' ||
' and del.status in (''DELINQUENT'',''PREDELINQUENT'') ' ||
' and (trunc(arp.due_date) + :p_grace_days) <= :p_corr_date ' ||
' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ';
'select null, arp.customer_trx_id, ' ||
'arp.payment_schedule_id ' ||
'from ar_payment_schedules arp ' ||
', hz_cust_accounts hca ' || -- Bills Receivables
'where arp.customer_id = hca.cust_account_id ' || -- Bills Receivables
'and arp.status = ''OP'' ' || -- Bills Receivables
-- 'where arp.status = ''OP'' ' || -- Bills Receivables
'and arp.amount_due_remaining <> 0 ' ||
'and (arp.class = ''INV'' or arp.class = ''BR'') ' || -- Bills Receivables change
'and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
--'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
'and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ; -- Bills Receivables
' (SELECT NVL(min(min_dunning_invoice_amount),0) '||
' FROM hz_cust_profile_amts '||
' WHERE site_use_id IS NULL '||
' AND cust_account_id = arp.customer_id '||
' AND currency_code = arp.invoice_currency_code),0) ';
' (SELECT NVL(min(min_dunning_invoice_amount),0) '||
' FROM hz_cust_profile_amts '||
' WHERE site_use_id IS NULL '||
' AND cust_account_id = arp.customer_id '||
' AND currency_code = arp.invoice_currency_code),0)';
' and arp.amount_due_remaining >= nvl ((select nvl(min(min_dunning_invoice_amount),0) '||
' from hz_cust_profile_amts '||
' where site_use_id = arp.CUSTOMER_SITE_USE_ID '||
' and currency_code = arp.invoice_currency_code),0)' ;
' and arp.amount_due_remaining >= nvl ((select nvl(min(min_dunning_invoice_amount),0) '||
' from hz_cust_profile_amts '||
' where site_use_id = arp.CUSTOMER_SITE_USE_ID '||
' and currency_code = arp.invoice_currency_code),0)';
* clchang updated 02/13/2003
* callback_days could be null if callback_yn = 'N';
INSERT_DUNNING_TRANSACTION(
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_delinquencies_tbl => l_del_tbl
, p_ag_dn_xref_id => l_ag_dn_xref_id
, p_dunning_id => l_dunning_id
, p_correspondence_date => p_correspondence_date
, p_running_level => p_running_level
, p_grace_days => l_grace_days
, p_include_dispute_items => l_dun_disputed_items
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
,p_workitem_id => null); -- bug 14772139
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
);
SELECT delinquency_ID
FROM IEX_DELINQUENCIES_ALL
WHERE delinquency_ID = in_del_ID;
SELECT a.score_value
FROM IEX_SCORE_HISTORIES a
, IEX_DUNNING_PLANS_VL c -- bug 4914799 ctlee 12/30/2005
WHERE a.score_object_ID = in_ID
AND a.score_object_code = IN_CODE
and c.score_id = a.score_id -- bug 4914799 ctlee 12/30/2005
and c.dunning_plan_id = p_dunning_plan_id -- bug 4914799 ctlee 12/30/2005
AND a.creation_date = (select max(b.creation_date)
from iex_score_histories b
where b.score_object_id = in_id
AND b.score_object_code = IN_CODE
AND b.score_id = a.score_id );
SELECT x.template_id,
x.xdo_template_id,
x.fm_method,
upper(x.callback_flag),
x.callback_days
FROM IEX_AG_DN_XREF x,
ar_aging_buckets ar,
iex_dunning_plans_vl d
WHERE x.aging_bucket_line_ID = l_line_ID
and x.dunning_plan_id = p_dunning_plan_id
AND l_score between x.score_range_low and x.score_range_high
AND x.aging_bucket_id = ar.aging_bucket_id
and ar.aging_bucket_id = d.aging_bucket_id
AND ar.status = 'A'
AND x.dunning_level = IN_LEVEL ;
select 'Active'
from xdo_templates_vl xdo
where xdo.template_id = l_template_id
and trunc(sysdate) >= TRUNC (NVL(xdo.start_date, sysdate))
and trunc(sysdate) < TRUNC(NVL(xdo.end_date, sysdate + 1));
select template_name
from xdo_templates_vl xdo
where xdo.template_id = l_template_id;
select ag_dn_xref_id,
dunning_level,
template_id,
xdo_template_id,
fm_method,
upper(callback_flag) callback_flag,
callback_days,
range_of_dunning_level_from,
range_of_dunning_level_to,
min_days_between_dunning,
invoice_copies
from iex_ag_dn_xref
where dunning_plan_id = p_dunn_plan_id
order by AG_DN_XREF_ID ;
select nvl(dunn.INCLUDE_CURRENT,'N'),
nvl(dunn.grace_days ,'N'),
nvl(dunn.dun_disputed_items, 'N')
from iex_dunning_plans_b dunn
where dunning_plan_id = p_dunn_plan_id;
select nvl(count(*),0)
from iex_bankruptcies
where party_id = p_par_id
and (disposition_code in ('GRANTED','NEGOTIATION')
OR (disposition_code is NULL));
select min_days_between_dunning
from iex_ag_dn_xref
where dunning_plan_id = p_dunn_plan_id
and p_stage_no between range_of_dunning_level_from and range_of_dunning_level_to
and p_score_val between score_range_low and score_range_high;
select site_use_code into t_code from hz_cust_site_uses_all where site_use_id = l_object_id;
update iex_delinquencies del
set staged_dunning_level = 98
where del.party_cust_id = l_object_id
and staged_dunning_level = 99
and status in ('DELINQUENT','PREDELINQUENT')
and nvl(
(
(select trunc(correspondence_date) from iex_dunnings
where dunning_id =
(select max(iet.DUNNING_ID)
from iex_dunning_transactions iet,
iex_dunnings dunn
where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
and dunn.dunning_id = iet.dunning_id
and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
OR (dunn.dunning_mode = 'FINAL'))
and iet.STAGE_NUMBER = 99
and dunn.delivery_status is null
)
)
+ l_min_days_between_dunn_99
)
, p_correspondence_date
)
<= p_correspondence_date ;
update iex_delinquencies del
set staged_dunning_level = 98
where del.cust_account_id = l_object_id
and staged_dunning_level = 99
and status in ('DELINQUENT','PREDELINQUENT')
and nvl(
(
(select trunc(correspondence_date) from iex_dunnings
where dunning_id =
(select max(iet.DUNNING_ID)
from iex_dunning_transactions iet,
iex_dunnings dunn
where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
and dunn.dunning_id = iet.dunning_id
and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
OR (dunn.dunning_mode = 'FINAL'))
and iet.STAGE_NUMBER = 99
and dunn.delivery_status is null
)
)
+ l_min_days_between_dunn_99
)
, p_correspondence_date
)
<= p_correspondence_date ;
update iex_delinquencies del
set staged_dunning_level = 98
where del.customer_site_use_id = l_object_id
and staged_dunning_level = 99
and status in ('DELINQUENT','PREDELINQUENT')
and nvl(
(
(select trunc(correspondence_date) from iex_dunnings
where dunning_id =
(select max(iet.DUNNING_ID)
from iex_dunning_transactions iet,
iex_dunnings dunn
where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
and dunn.dunning_id = iet.dunning_id
and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
OR (dunn.dunning_mode = 'FINAL'))
and iet.STAGE_NUMBER = 99
and dunn.delivery_status is null
)
)
+ l_min_days_between_dunn_99
)
, p_correspondence_date
)
<= p_correspondence_date ;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' Updated : ' || SQL%ROWCOUNT || ' number of row''s staged_dunning_level from 99 to 98');
vPLSQL := ' select ag_dn_xref_id, ' ||
' dunning_level, ' ||
' template_id, ' ||
' xdo_template_id, ' ||
' fm_method, ' ||
' upper(callback_flag) callback_flag, ' ||
' callback_days, ' ||
' range_of_dunning_level_from, ' ||
' range_of_dunning_level_to, ' ||
' min_days_between_dunning, ' ||
' invoice_copies ' ||
' from iex_ag_dn_xref ' ||
' where dunning_plan_id = :p_dunning_plan_id ' ||
' AND :p_score between score_range_low and score_range_high ' ||
' order by range_of_dunning_level_from ' || l_orderby;
vPLSQL1 := 'select count(*) from ( ' ||
' select del.delinquency_id, ' ||
' del.transaction_id, ' ||
' del.payment_schedule_id ' ||
' from iex_delinquencies del, ' ||
' ar_payment_schedules arp ' ||
' where del.payment_schedule_id = arp.payment_schedule_id ' ||
' and del.status in (''DELINQUENT'',''PREDELINQUENT'') ' ||
' and del.staged_dunning_level is NULL ' ||
' and (trunc(arp.due_date) + :p_min_days_bw_dun) <= :p_corr_date ' ||
' and (trunc(arp.due_date) + :p_grace_days) <= :p_corr_date ' ||
' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ';
'select null, arp.customer_trx_id, ' ||
'arp.payment_schedule_id ' ||
'from ar_payment_schedules arp, ' ||
'hz_cust_accounts hca ' ||
'where arp.customer_id = hca.cust_account_id ' ||
'and arp.status = ''OP'' ' ||
'and arp.amount_due_remaining <> 0 ' ||--' and arp.amount_due_remaining > 0 ' || Changed to fix 12552027 by snuthala 5/19/2011
'and arp.class = ''INV'' ' ||
'and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
'and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ;*/
' (SELECT NVL(min(min_dunning_invoice_amount),0) '||
' FROM hz_cust_profile_amts '||
' WHERE site_use_id IS NULL '||
' AND cust_account_id = arp.customer_id '||
' AND currency_code = arp.invoice_currency_code),0))';
' and arp.amount_due_remaining >= nvl ((select nvl(min(min_dunning_invoice_amount),0) '||
' from hz_cust_profile_amts '||
' where site_use_id = arp.CUSTOMER_SITE_USE_ID '||
' and currency_code = arp.invoice_currency_code),0))';
vPLSQL1 := 'select count(*) from ( ' ||
' select del.delinquency_id, ' ||
' del.transaction_id, ' ||
' del.payment_schedule_id ' ||
' from iex_delinquencies del ' ||
' ,ar_payment_schedules arp ' ||
' where ' ||
' del.payment_schedule_id = arp.payment_schedule_id and ' ||
' del.status in (''DELINQUENT'',''PREDELINQUENT'') ' ||
' and del.staged_dunning_level = :p_stage_no ' ||
' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
' and nvl( ' ||
' ( ' ||
' (select trunc(correspondence_date) from iex_dunnings ' ||
' where dunning_id = ' ||
' (select max(iet.DUNNING_ID) from iex_dunning_transactions iet, ' ||
' iex_dunnings dunn ' ||
' where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id ' ||
' and dunn.dunning_id = iet.dunning_id ' ||
' and ((dunn.dunning_mode = ''DRAFT'' and dunn.confirmation_mode = ''CONFIRMED'') ' ||
' OR (dunn.dunning_mode = ''FINAL'')) ' ||
' and iet.STAGE_NUMBER = :p_stage_no ' ||
' and dunn.delivery_status is null' ||
' ) ' ||
' ) ' ||
' + :p_min_days_bw_dun ' ||
' ) ' ||
' , :p_corr_date ' ||
' ) ' ||
' <= :p_corr_date ';
'select null, arp.customer_trx_id, ' ||
'arp.payment_schedule_id ' ||
'from ar_payment_schedules arp, ' ||
'hz_cust_accounts hca ' ||
'where arp.customer_id = hca.cust_account_id ' ||
'and arp.status = ''OP'' ' ||
'and arp.amount_due_remaining <> 0 ' ||--' and arp.amount_due_remaining > 0 ' || Changed to fix 12552027 by snuthala 5/19/2011
'and arp.class = ''INV'' ' ||
'and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
'and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ;
' select del.delinquency_id, ' ||
' del.transaction_id, ' ||
' del.payment_schedule_id ' ||
' from iex_delinquencies del ' ||
' ,ar_payment_schedules arp ' ||
' where ' ||
' del.payment_schedule_id = arp.payment_schedule_id and ' ||
' del.status = ''CURRENT'' ' ||
' and del.staged_dunning_level = :p_stage_no ' ||
' and arp.status = ''OP'' ' ||
' and arp.class = ''INV'' ' ||
' and arp.amount_in_dispute >= decode(:p_include_dis_items, ''Y'', arp.amount_due_remaining, (arp.amount_due_original + 1)) ' ||
' and nvl( ' ||
' ( ' ||
' (select trunc(correspondence_date) from iex_dunnings ' ||
' where dunning_id = ' ||
' (select max(iet.DUNNING_ID) from iex_dunning_transactions iet, ' ||
' iex_dunnings dunn ' ||
' where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id ' ||
' and dunn.dunning_id = iet.dunning_id ' ||
' and ((dunn.dunning_mode = ''DRAFT'' and dunn.confirmation_mode = ''CONFIRMED'') ' ||
' OR (dunn.dunning_mode = ''FINAL'')) ' ||
' and iet.STAGE_NUMBER = :p_stage_no and dunn.delivery_status is null)) ' ||
' + :p_min_days_bw_dun ) ' ||
' , :p_corr_date ) ' ||
' <= :p_corr_date ' ;*/
' (SELECT NVL(min(min_dunning_invoice_amount),0) '||
' FROM hz_cust_profile_amts '||
' WHERE site_use_id IS NULL '||
' AND cust_account_id = arp.customer_id '||
' AND currency_code = arp.invoice_currency_code),0)) ';
' (SELECT NVL(min(min_dunning_invoice_amount),0) '||
' FROM hz_cust_profile_amts '||
' WHERE site_use_id IS NULL '||
' AND cust_account_id = arp.customer_id '||
' AND currency_code = arp.invoice_currency_code),0))';*/
' and arp.amount_due_remaining >= nvl ((select nvl(min(min_dunning_invoice_amount),0) '||
' from hz_cust_profile_amts '||
' where site_use_id = arp.CUSTOMER_SITE_USE_ID '||
' and currency_code = arp.invoice_currency_code),0))';/* ||
' and arp.amount_due_remaining >= nvl ((select nvl(min(min_dunning_invoice_amount),0) '||
' from hz_cust_profile_amts '||
' where site_use_id = arp.CUSTOMER_SITE_USE_ID '||
' and currency_code = arp.invoice_currency_code),0))';*/
* clchang updated 02/13/2003
* callback_days could be null if callback_yn = 'N';
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
);
select template_id
into l_xdo_template_id
from iex_dunnings
where dunning_id = l_dunning_id;
select xml_template_id
into l_xdo_template_id
from iex_dunnings
where dunning_id = l_dunning_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
);
update iex_delinquencies del
set staged_dunning_level = 99
where del.party_cust_id = l_object_id
and staged_dunning_level = 98
and status in ('DELINQUENT','PREDELINQUENT')
and exists (select count(iet.DUNNING_ID)
from iex_dunning_transactions iet,
iex_dunnings dunn
where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
and dunn.dunning_id = iet.dunning_id
and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
OR (dunn.dunning_mode = 'FINAL'))
and iet.STAGE_NUMBER = 99
and dunn.delivery_status is null
);
update iex_delinquencies del
set staged_dunning_level = 99
where del.cust_account_id = l_object_id
and staged_dunning_level = 98
and status in ('DELINQUENT','PREDELINQUENT')
and exists (select count(iet.DUNNING_ID)
from iex_dunning_transactions iet,
iex_dunnings dunn
where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
and dunn.dunning_id = iet.dunning_id
and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
OR (dunn.dunning_mode = 'FINAL'))
and iet.STAGE_NUMBER = 99
and dunn.delivery_status is null
);
update iex_delinquencies del
set staged_dunning_level = 99
where del.customer_site_use_id = l_object_id
and staged_dunning_level = 98
and status in ('DELINQUENT','PREDELINQUENT')
and exists (select count(iet.DUNNING_ID)
from iex_dunning_transactions iet,
iex_dunnings dunn
where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
and dunn.dunning_id = iet.dunning_id
and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
OR (dunn.dunning_mode = 'FINAL'))
and iet.STAGE_NUMBER = 99
and dunn.delivery_status is null
);
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' Updated : ' || SQL%ROWCOUNT || ' number of row''s staged_dunning_level from 98 to 99');
* clchang updated 09/19/2002 -
* insert ffm_request_id into iex_dunnings after CALL_FFM successfully;
* Create_Dunning and Update_Dunning also updated;
SELECT delinquency_ID,
party_cust_id,
cust_account_id,
customer_site_use_id,
score_value
FROM IEX_DELINQUENCIES
WHERE delinquency_ID = in_del_ID;
SELECT a.score_value
FROM IEX_SCORE_HISTORIES a
WHERE a.score_object_ID = in_ID
AND a.score_object_code = 'IEX_DELINQUENCY'
AND a.creation_date = (select max(b.creation_date)
from iex_score_histories b
where b.score_object_id = in_id
AND b.score_object_code = 'IEX_DELINQUENCY');
select score_value
from iex_score_histories
where score_object_id = p_object_id
and score_object_code = p_object_type
order by creation_date desc;
select isc.jtf_object_code
from iex_dunning_plans_vl ipd, IEX_SCORES isc
where ipd.dunning_plan_id = p_dunn_plan_id
and ipd.score_id=isc.score_id;
SELECT x.ag_dn_xref_id,
x.template_id,
x.xdo_template_id,
x.fm_method,
upper(x.callback_flag),
x.callback_days,
ar.bucket_name
FROM IEX_AG_DN_XREF x,
ar_aging_buckets ar,
iex_dunning_plans_vl d
WHERE x.aging_bucket_line_ID = l_line_ID
and x.dunning_plan_id = p_dunning_plan_id
AND l_score between x.score_range_low and x.score_range_high
AND x.aging_bucket_id = ar.aging_bucket_id
and ar.aging_bucket_id = d.aging_bucket_id
AND ar.status = 'A'
AND x.dunning_level = 'DELINQUENCY' ;
select 'Active'
from xdo_templates_vl xdo
where xdo.template_id = l_template_id
and trunc(sysdate) >= TRUNC (NVL(xdo.start_date, sysdate))
and trunc(sysdate) < TRUNC(NVL(xdo.end_date, sysdate + 1));
select template_name
from xdo_templates_vl xdo
where xdo.template_id = l_template_id;
select nvl(ps.amount_due_remaining,0),
nvl(ps.invoice_currency_code,'USD')
from ar_payment_schedules_all ps,
--iex_delinquencies_all del
iex_delinquencies del
where ps.payment_schedule_id (+)= del.payment_schedule_id
and del.delinquency_id = in_id;
select nvl(dunn.grace_days ,'N'),
nvl(dunn.dun_disputed_items, 'N')
from iex_dunning_plans_b dunn
where dunning_plan_id = p_dunn_plan_id;
select currency_code, nvl(min_dunning_invoice_amount,0) from hz_cust_profile_amts
where site_use_id = p_customer_site_use_id and currency_code = (
SELECT sob.currency_code FROM ar_system_parameters_all sp, gl_sets_of_books sob
WHERE sob.set_of_books_id = sp.set_of_books_id
and sp.org_id = p_org_id);
select nvl(min_dunning_amount,0), nvl(min_dunning_invoice_amount,0)
from hz_cust_profile_amts
where site_use_id = p_site_use_id
and currency_code = p_currency_code;
select nvl(count(*),0)
from iex_bankruptcies
where party_id = p_par_id
and (disposition_code in ('GRANTED','NEGOTIATION')
OR (disposition_code is NULL));
select payment_schedule_id into l_del_object_id
from iex_delinquencies_all
where delinquency_id = l_delinquency_id;
SELECT count(1) into l_fully_promised
FROM ar_payment_schedules_all ps, iex_delinquencies_all del
WHERE del.delinquency_id= l_delinquency_id
AND ps.payment_schedule_id = del.payment_schedule_id
AND ps.status = 'OP'
AND del.status IN ('DELINQUENT', 'PREDELINQUENT')
and not exists(select 1 from iex_promise_details pd where pd.delinquency_id=del.delinquency_id
and pd.status='COLLECTABLE'
and pd.state='PROMISE'
group by pd.delinquency_id
having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
select fnd_profile.value(nvl('IEX_ALLOW_DUN_FULL_PROMISE','N')) into l_allow_send from dual;
select sum(a.amount_in_dispute) - sum(a.amount_due_remaining) into l_dispute_amount
from iex_delinquencies d
,ar_payment_schedules a
where a.payment_schedule_id = d.payment_schedule_id
and d.delinquency_id = l_delinquency_id;
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
* clchang updated 02/13/2003
* callback_days could be null if callback_yn = 'N';
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - InsertRow');
INSERT_DUNNING_TRANSACTION(
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_delinquencies_tbl => l_del_tbl
, p_ag_dn_xref_id => l_ag_dn_xref_id
, p_dunning_id => l_dunning_id
, p_correspondence_date => p_correspondence_date
, p_running_level => l_running_level
, p_grace_days => l_grace_days
, p_include_dispute_items => l_dun_disputed_items
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
,p_workitem_id => null); -- bug 14772139
IEX_DUNNING_PVT.Update_DUNNING(p_api_version => 1.0
, p_init_msg_list => FND_API.G_TRUE
, 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);
* Update Delinquency
* Set DUNN_YN = 'N'
*===========================================*/
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateDel');
SELECT delinquency_ID,
party_cust_id,
cust_account_id,
customer_site_use_id,
score_value
FROM IEX_DELINQUENCIES
WHERE delinquency_ID = in_del_ID;
SELECT a.score_value
FROM IEX_SCORE_HISTORIES a
WHERE a.score_object_ID = in_ID
AND a.score_object_code = 'IEX_DELINQUENCY'
AND a.creation_date = (select max(b.creation_date)
from iex_score_histories b
where b.score_object_id = in_id
AND b.score_object_code = 'IEX_DELINQUENCY');
select score_value
from iex_score_histories
where score_object_id = p_object_id
and score_object_code = p_object_type
order by creation_date desc;
select isc.jtf_object_code
from iex_dunning_plans_vl ipd, IEX_SCORES isc
where ipd.dunning_plan_id = p_dunn_plan_id
and ipd.score_id=isc.score_id;
SELECT
x.template_id,
x.xdo_template_id,
x.fm_method,
upper(x.callback_flag),
x.callback_days,
ar.bucket_name
FROM IEX_AG_DN_XREF x,
ar_aging_buckets ar,
iex_dunning_plans_vl d
WHERE x.aging_bucket_line_ID = l_line_ID
and x.dunning_plan_id = p_dunning_plan_id
AND l_score between x.score_range_low and x.score_range_high
AND x.aging_bucket_id = ar.aging_bucket_id
and ar.aging_bucket_id = d.aging_bucket_id
AND ar.status = 'A'
AND x.dunning_level = 'DELINQUENCY' ;
select 'Active'
from xdo_templates_vl xdo
where xdo.template_id = l_template_id
and trunc(sysdate) >= TRUNC (NVL(xdo.start_date, sysdate))
and trunc(sysdate) < TRUNC(NVL(xdo.end_date, sysdate + 1));
select template_name
from xdo_templates_vl xdo
where xdo.template_id = l_template_id;
select ps.amount_due_remaining,
ps.invoice_currency_code
from ar_payment_schedules_all ps,
--iex_delinquencies_all del
iex_delinquencies del
where ps.payment_schedule_id (+)= del.payment_schedule_id
and del.delinquency_id = in_id;
select nvl(dunn.grace_days ,'N'),
nvl(dunn.dun_disputed_items, 'N')
from iex_dunning_plans_b dunn
where dunning_plan_id = p_dunn_plan_id;
select nvl(min_dunning_amount,0), nvl(min_dunning_invoice_amount,0)
from hz_cust_profile_amts
where site_use_id = p_site_use_id
and currency_code = p_currency_code;
select arp.invoice_currency_code,
arp.amount_due_remaining,
del.staged_dunning_level,
del.transaction_id,
del.payment_schedule_id
from iex_delinquencies del,
ar_payment_schedules arp
where del.delinquency_id = p_delinquency_id
and del.payment_schedule_id = arp.payment_schedule_id;
select ag_dn_xref_id,
dunning_level,
template_id,
xdo_template_id,
fm_method,
upper(callback_flag) callback_flag,
callback_days,
range_of_dunning_level_from,
range_of_dunning_level_to,
min_days_between_dunning ,
invoice_copies
from iex_ag_dn_xref
where dunning_plan_id = p_dunning_plan_id
--and range_of_dunning_level_from >= p_stage
--and range_of_dunning_level_to <= p_stage
and p_stage between range_of_dunning_level_from and range_of_dunning_level_to
and p_score between score_range_low and score_range_high;
select count(*) from (
select del.delinquency_id,
del.transaction_id,
del.payment_schedule_id
from iex_delinquencies del,
ar_payment_schedules arp
where del.payment_schedule_id = arp.payment_schedule_id
and del.status in ('DELINQUENT','PREDELINQUENT')
and del.delinquency_id = p_del_id
and del.staged_dunning_level is NULL
and (trunc(arp.due_date) + p_min_days_bw_dun) <= p_corr_date
and (trunc(arp.due_date) + p_grace_days) <= p_corr_date
and nvl(arp.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(arp.amount_in_dispute,0), 0)
/*
union
select del.delinquency_id,
del.transaction_id,
del.payment_schedule_id
from iex_delinquencies del,
ar_payment_schedules arp
where del.payment_schedule_id = arp.payment_schedule_id
and del.status = 'CURRENT'
and del.delinquency_id = p_del_id
and del.staged_dunning_level is NULL
and arp.status = 'OP'
and arp.class = 'INV'
and (trunc(arp.due_date) + p_min_days_bw_dun) <= p_corr_date
and (trunc(arp.due_date) + p_grace_days) <= p_corr_date
and arp.amount_in_dispute >= decode(p_include_dis_items, 'Y', arp.amount_due_remaining, (arp.amount_due_original + 1))
*/
);
select count(*) from (
select del.delinquency_id,
del.transaction_id,
del.payment_schedule_id
from iex_delinquencies del
,ar_payment_schedules arp
where
del.payment_schedule_id = arp.payment_schedule_id and
del.status in ('DELINQUENT','PREDELINQUENT')
and del.delinquency_id = p_del_id
and del.staged_dunning_level = p_stage_no
and nvl(arp.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(arp.amount_in_dispute,0), 0)
and nvl(
(
(select trunc(correspondence_date) from iex_dunnings
where dunning_id =
(select max(iet.DUNNING_ID) from iex_dunning_transactions iet,
iex_dunnings dunn
where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
and dunn.dunning_id = iet.dunning_id
and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
OR (dunn.dunning_mode = 'FINAL'))
and iet.STAGE_NUMBER = p_stage_no
and dunn.delivery_status is null
--group by iet.dunning_id
)
)
+ p_min_days_bw_dun
)
, p_corr_date
)
<= p_corr_date
/*
union
select del.delinquency_id,
del.transaction_id,
del.payment_schedule_id
from iex_delinquencies del
,ar_payment_schedules arp
where
del.payment_schedule_id = arp.payment_schedule_id and
del.status = 'CURRENT'
and del.party_cust_id = p_party_id
and del.cust_account_id = p_cust_acct_id
and del.staged_dunning_level = p_stage_no
and arp.status = 'OP'
and arp.class = 'INV'
and arp.amount_in_dispute >= decode(p_include_dis_items, 'Y', arp.amount_due_remaining, (arp.amount_due_original + 1))
and nvl(
(
(select trunc(correspondence_date) from iex_dunnings
where dunning_id =
(select distinct DUNNING_ID from iex_dunning_transactions
where PAYMENT_SCHEDULE_ID = del.payment_schedule_id
and STAGE_NUMBER = p_stage_no))
+ p_min_days_bw_dun )
, p_corr_date )
<= p_corr_date
*/
);
select nvl(count(*),0)
from iex_bankruptcies
where party_id = p_par_id
and (disposition_code in ('GRANTED','NEGOTIATION')
OR (disposition_code is NULL));
select min_days_between_dunning
from iex_ag_dn_xref
where dunning_plan_id = p_dunn_plan_id
and p_stage_no between range_of_dunning_level_from and range_of_dunning_level_to
and p_score_val between score_range_low and score_range_high;
select payment_schedule_id into l_del_object_id
from iex_delinquencies_all
where delinquency_id = l_delinquency_id;
update iex_delinquencies del
set staged_dunning_level = 98
where delinquency_id = l_delinquency_id
and staged_dunning_level = 99
and status in ('DELINQUENT','PREDELINQUENT')
and nvl(
(
(select trunc(correspondence_date) from iex_dunnings
where dunning_id =
(select max(iet.DUNNING_ID)
from iex_dunning_transactions iet,
iex_dunnings dunn
where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
and dunn.dunning_id = iet.dunning_id
and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
OR (dunn.dunning_mode = 'FINAL'))
and iet.STAGE_NUMBER = 99
and dunn.delivery_status is null
)
)
+ l_min_days_between_dunn_99
)
, p_correspondence_date
)
<= p_correspondence_date ;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' Updated : ' || SQL%ROWCOUNT || ' number of row''s staged_dunning_level from 99 to 98');
--FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
* clchang updated 02/13/2003
* callback_days could be null if callback_yn = 'N';
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - InsertRow');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - InsertRow');
IEX_Dunnings_PKG.insert_staged_dunning_row(
px_rowid => l_rowid
, px_dunning_trx_id => x_dunning_trx_id
, p_dunning_id => l_dunning_id
, p_cust_trx_id => l_transaction_id
, p_payment_schedule_id => l_payment_schedule_id
, p_ag_dn_xref_id => l_ag_dn_xref_id
, p_stage_number => l_stage
, p_created_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_last_update_date => sysdate
, p_last_update_login => FND_GLOBAL.USER_ID
, p_object_version_number => 1.0
);
update iex_delinquencies_all
set staged_dunning_level = l_stage
where delinquency_id = l_delinquency_id;
--reset the x_dunning_trx_id, so that will get new no when inserting 2nd record.
x_dunning_trx_id := null;
IEX_DUNNING_PVT.Update_DUNNING(p_api_version => 1.0
, p_init_msg_list => FND_API.G_TRUE
, 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);
* Update Delinquency
* Set DUNN_YN = 'N'
*===========================================*/
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateDel');
update iex_delinquencies del
set staged_dunning_level = 99
where delinquency_id = l_delinquency_id
and staged_dunning_level = 98
and status in ('DELINQUENT','PREDELINQUENT')
and exists (select count(iet.DUNNING_ID)
from iex_dunning_transactions iet,
iex_dunnings dunn
where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
and dunn.dunning_id = iet.dunning_id
and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
OR (dunn.dunning_mode = 'FINAL'))
and iet.STAGE_NUMBER = 99
and dunn.delivery_status is null
);
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' Updated : ' || SQL%ROWCOUNT || ' number of row''s staged_dunning_level from 98 to 99');
clchang updated 04/21/2003 -
added one new level 'BILL_TO' in 11.5.10.
*=========================================================================*/
Procedure Resend_Level_Dunning
(p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_dunning_plan_id in number,
p_running_level IN VARCHAR2,
p_delinquencies_tbl IN IEX_DELINQUENCY_PUB.DELINQUENCY_TBL_TYPE,
p_org_id in number,
p_dunning_id in Number,
x_request_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR C_GET_DEL (IN_del_ID NUMBER) IS
SELECT delinquency_ID
FROM IEX_DELINQUENCIES
WHERE delinquency_ID = in_del_ID;
SELECT a.score_value
FROM IEX_SCORE_HISTORIES a
, IEX_DUNNING_PLANS_VL c -- bug 4914799 ctlee 12/30/2005
WHERE a.score_object_ID = in_ID
AND a.score_object_code = IN_CODE
and c.score_id = a.score_id -- bug 4914799 ctlee 12/30/2005
and c.dunning_plan_id = p_dunning_plan_id -- bug 4914799 ctlee 12/30/2005
AND a.creation_date = (select max(b.creation_date)
from iex_score_histories b
where b.score_object_id = in_id
AND b.score_object_code = IN_CODE);
SELECT x.ag_dn_xref_id,
x.template_id,
x.xdo_template_id,
x.fm_method,
upper(x.callback_flag),
x.callback_days
FROM IEX_AG_DN_XREF x,
ar_aging_buckets ar,
iex_dunning_plans_vl d
WHERE x.aging_bucket_line_ID = l_line_ID
and x.dunning_plan_id = p_dunning_plan_id
AND l_score between x.score_range_low and x.score_range_high
AND x.aging_bucket_id = ar.aging_bucket_id
and ar.aging_bucket_id = d.aging_bucket_id
AND ar.status = 'A'
AND x.dunning_level = IN_LEVEL ;
select 'Active'
from xdo_templates_vl xdo
where xdo.template_id = l_template_id
and trunc(sysdate) >= TRUNC (NVL(xdo.start_date, sysdate))
and trunc(sysdate) < TRUNC(NVL(xdo.end_date, sysdate + 1));
select template_name
from xdo_templates_vl xdo
where xdo.template_id = l_template_id;
select nvl(dunn.grace_days ,'N'),
nvl(dunn.dun_disputed_items, 'N')
from iex_dunning_plans_b dunn
where dunning_plan_id = p_dunn_plan_id;
select nvl(dunn.as_of_date,sysdate)
from iex_dunnings dunn
where dunning_id = p_dunning_id;
select nvl(sum(aps.acctd_amount_due_remaining),0)
from ar_payment_schedules_all aps,iex_delinquencies_all dd,hz_cust_accounts hzca
where dd.payment_schedule_id = aps.payment_schedule_id
and aps.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP')
and aps.status='OP'
and (trunc(aps.due_date) + p_grace_days) <= p_corr_date
and nvl(aps.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(aps.amount_in_dispute,0), 0)
and aps.amount_due_remaining > 0
and aps.customer_id=hzca.cust_account_id
and aps.org_id=p_org_id
and hzca.party_id = p_party_id;
select nvl(sum(acctd_amount_due_remaining),0)
from ar_payment_schedules_all aps,hz_cust_accounts hzca
where aps.class IN ('CM','PMT')
and aps.status = 'OP'
and aps.customer_id = hzca.cust_account_id
and aps.org_id= p_org_id
and hzca.party_id = p_party_id;
del_tbl(nIdx) := l_delinquency_id; --in order to update del)
vPLSQL1 := 'select count(*) from ( ' ||
' select del.delinquency_id, ' ||
' del.transaction_id, ' ||
' del.payment_schedule_id ' ||
' from iex_delinquencies del, ' ||
' ar_payment_schedules arp ' ||
' where del.payment_schedule_id = arp.payment_schedule_id ' ||
' and del.status in (''DELINQUENT'',''PREDELINQUENT'') ' ||
' and (trunc(arp.due_date) + :p_grace_days) <= :p_corr_date ' ||
' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ';
'select null, arp.customer_trx_id, ' ||
'arp.payment_schedule_id ' ||
'from ar_payment_schedules arp, ' ||
'hz_cust_accounts hca ' ||
'where arp.customer_id = hca.cust_account_id ' ||
'and hca.party_id = :p_party_id ' ||
'and arp.status = ''OP'' ' ||
'and arp.amount_due_remaining <> 0 ' ||--' and arp.amount_due_remaining > 0 ' || Changed to fix 12552027 by snuthala 5/19/2011
'and arp.class = ''INV'' ' ||
'and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
'and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ;
' (SELECT NVL(min(min_dunning_invoice_amount),0) '||
' FROM hz_cust_profile_amts '||
' WHERE site_use_id IS NULL '||
' AND cust_account_id = arp.customer_id '||
' AND currency_code = arp.invoice_currency_code),0) '||
vPLSQL2 ||
' and del.cust_account_id = :p_cust_acct_id '||
' and arp.amount_due_remaining >= NVL ( '||
' (SELECT NVL(min(min_dunning_invoice_amount),0) '||
' FROM hz_cust_profile_amts '||
' WHERE site_use_id IS NULL '||
' AND cust_account_id = arp.customer_id '||
' AND currency_code = arp.invoice_currency_code),0))';
' and arp.amount_due_remaining >= nvl ((select nvl(min(min_dunning_invoice_amount),0) '||
' from hz_cust_profile_amts '||
' where site_use_id = arp.CUSTOMER_SITE_USE_ID '||
' and currency_code = arp.invoice_currency_code),0) ' ||
vPLSQL2 ||
' and del.customer_site_use_id = :p_site_use_id ' ||
' and arp.amount_due_remaining >= nvl ((select nvl(min(min_dunning_invoice_amount),0) '||
' from hz_cust_profile_amts '||
' where site_use_id = arp.CUSTOMER_SITE_USE_ID '||
' and currency_code = arp.invoice_currency_code),0))';
* clchang updated 02/13/2003
* callback_days could be null if callback_yn = 'N';
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Insert Row', l_write);
INSERT_DUNNING_TRANSACTION(
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_delinquencies_tbl => l_del_tbl
, p_ag_dn_xref_id => l_ag_dn_xref_id
, p_dunning_id => l_dunning_id
, p_correspondence_date => l_as_of_date
, p_running_level => p_running_level
, p_grace_days => l_grace_days
, p_include_dispute_items => l_dun_disputed_items
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
,p_workitem_id => null); -- bug 14772139
* Update Delinquency
* Set DUNN_YN = 'N'
*===========================================*/
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - ==========Summary===========');
SELECT delinquency_ID
FROM IEX_DELINQUENCIES
WHERE delinquency_ID = in_del_ID;
SELECT a.score_value
FROM IEX_SCORE_HISTORIES a
, IEX_DUNNING_PLANS_VL c -- bug 4914799 ctlee 12/30/2005
WHERE a.score_object_ID = in_ID
AND a.score_object_code = IN_CODE
and c.score_id = a.score_id -- bug 4914799 ctlee 12/30/2005
and c.dunning_plan_id = p_dunning_plan_id -- bug 4914799 ctlee 12/30/2005
AND a.creation_date = (select max(b.creation_date)
from iex_score_histories b
where b.score_object_id = in_id
AND b.score_object_code = IN_CODE);
SELECT x.template_id,
x.xdo_template_id,
x.fm_method,
upper(x.callback_flag),
x.callback_days
FROM IEX_AG_DN_XREF x,
ar_aging_buckets ar,
iex_dunning_plans_vl d
WHERE x.aging_bucket_line_ID = l_line_ID
and x.dunning_plan_id = p_dunning_plan_id
AND l_score between x.score_range_low and x.score_range_high
AND x.aging_bucket_id = ar.aging_bucket_id
and ar.aging_bucket_id = d.aging_bucket_id
AND ar.status = 'A'
AND x.dunning_level = IN_LEVEL ;
select 'Active'
from xdo_templates_vl xdo
where xdo.template_id = l_template_id
and trunc(sysdate) >= TRUNC (NVL(xdo.start_date, sysdate))
and trunc(sysdate) < TRUNC(NVL(xdo.end_date, sysdate + 1));
select template_name
from xdo_templates_vl xdo
where xdo.template_id = l_template_id;
SELECT dunning_object_id,
delinquency_id,
dunning_level,
xml_template_id,
ag_dn_xref_id,
xml_request_id,
addt_xml_template_id,
dunning_method,
amount_due_remaining,
currency_code,
nvl(as_of_date,sysdate)
FROM iex_dunnings
WHERE dunning_id = in_dunning_id;
SELECT
--x.fm_method,
upper(x.callback_flag),
x.callback_days
FROM IEX_AG_DN_XREF x
WHERE x.ag_dn_xref_id = p_ag_dn_xref_id;
SELECT resource_id
FROM iex_xml_request_histories
WHERE xml_request_id = p_xml_req_id;
select nvl(dunn.as_of_date,sysdate)
from iex_dunnings dunn
where dunning_id = p_dunning_id;
del_tbl(nIdx) := l_delinquency_id; --in order to update del)
* clchang updated 02/13/2003
* callback_days could be null if callback_yn = 'N';
select parent_dunning_id into temp_dunning_id
from iex_dunnings
where dunning_id = p_dunning_id;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Insert Row', l_write);
* Update Delinquency
* Set DUNN_YN = 'N'
*===========================================*/
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - ==========Summary===========');
SELECT delinquency_ID,
party_cust_id,
cust_account_id,
customer_site_use_id,
score_value
FROM IEX_DELINQUENCIES
WHERE delinquency_ID = in_del_ID;
SELECT a.score_value
FROM IEX_SCORE_HISTORIES a
WHERE a.score_object_ID = in_ID
AND a.score_object_code = 'IEX_DELINQUENCY'
AND a.creation_date = (select max(b.creation_date)
from iex_score_histories b
where b.score_object_id = in_id
AND b.score_object_code = 'IEX_DELINQUENCY');
SELECT x.ag_dn_xref_id,x.template_id,
x.xdo_template_id,
x.fm_method,
upper(x.callback_flag),
x.callback_days,
ar.bucket_name
FROM IEX_AG_DN_XREF x,
ar_aging_buckets ar,
iex_dunning_plans_vl d
WHERE x.aging_bucket_line_ID = l_line_ID
and x.dunning_plan_id = p_dunning_plan_id
AND l_score between x.score_range_low and x.score_range_high
AND x.aging_bucket_id = ar.aging_bucket_id
and ar.aging_bucket_id = d.aging_bucket_id
AND ar.status = 'A'
AND x.dunning_level = 'DELINQUENCY' ;
select 'Active'
from xdo_templates_vl xdo
where xdo.template_id = l_template_id
and trunc(sysdate) >= TRUNC (NVL(xdo.start_date, sysdate))
and trunc(sysdate) < TRUNC(NVL(xdo.end_date, sysdate + 1));
select template_name
from xdo_templates_vl xdo
where xdo.template_id = l_template_id;
select ps.amount_due_remaining,
ps.invoice_currency_code
from ar_payment_schedules_all ps,
--iex_delinquencies_all del
iex_delinquencies del
where ps.payment_schedule_id (+)= del.payment_schedule_id
and del.delinquency_id = in_id;
select nvl(dunn.grace_days ,'N'),
nvl(dunn.dun_disputed_items, 'N')
from iex_dunning_plans_b dunn
where dunning_plan_id = p_dunn_plan_id;
select nvl(dunn.as_of_date,sysdate)
from iex_dunnings dunn
where dunning_id = p_dunning_id;
* clchang updated 02/13/2003
* callback_days could be null if callback_yn = 'N';
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - InsertRow');
INSERT_DUNNING_TRANSACTION(
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_delinquencies_tbl => l_del_tbl
, p_ag_dn_xref_id => l_ag_dn_xref_id
, p_dunning_id => l_dunning_id
, p_correspondence_date => l_as_of_date
, p_running_level => l_running_level
, p_grace_days => l_grace_days
, p_include_dispute_items => l_dun_disputed_items
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
,p_workitem_id => null); -- bug 14772139
* Update Delinquency
* Set DUNN_YN = 'N'
*===========================================*/
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateDel');
SELECT delinquency_ID,
party_cust_id,
cust_account_id,
customer_site_use_id,
score_value
FROM IEX_DELINQUENCIES
WHERE delinquency_ID = in_del_ID;
SELECT a.score_value
FROM IEX_SCORE_HISTORIES a
WHERE a.score_object_ID = in_ID
AND a.score_object_code = 'IEX_DELINQUENCY'
AND a.creation_date = (select max(b.creation_date)
from iex_score_histories b
where b.score_object_id = in_id
AND b.score_object_code = 'IEX_DELINQUENCY');
SELECT x.template_id,
x.xdo_template_id,
x.fm_method,
upper(x.callback_flag),
x.callback_days,
ar.bucket_name
FROM IEX_AG_DN_XREF x,
ar_aging_buckets ar,
iex_dunning_plans_vl d
WHERE x.aging_bucket_line_ID = l_line_ID
and x.dunning_plan_id = p_dunning_plan_id
AND l_score between x.score_range_low and x.score_range_high
AND x.aging_bucket_id = ar.aging_bucket_id
and ar.aging_bucket_id = d.aging_bucket_id
AND ar.status = 'A'
AND x.dunning_level = 'DELINQUENCY' ;
select 'Active'
from xdo_templates_vl xdo
where xdo.template_id = l_template_id
and trunc(sysdate) >= TRUNC (NVL(xdo.start_date, sysdate))
and trunc(sysdate) < TRUNC(NVL(xdo.end_date, sysdate + 1));
select template_name
from xdo_templates_vl xdo
where xdo.template_id = l_template_id;
select ps.amount_due_remaining,
ps.invoice_currency_code
from ar_payment_schedules_all ps,
--iex_delinquencies_all del
iex_delinquencies del
where ps.payment_schedule_id (+)= del.payment_schedule_id
and del.delinquency_id = in_id;
SELECT dunning_object_id,
delinquency_id,
dunning_level,
xml_template_id,
ag_dn_xref_id,
xml_request_id,
addt_xml_template_id
FROM iex_dunnings
WHERE dunning_id = in_dunning_id;
SELECT x.fm_method,
upper(x.callback_flag),
x.callback_days
FROM IEX_AG_DN_XREF x
WHERE x.ag_dn_xref_id = p_ag_dn_xref_id;
select nvl(dunn.as_of_date,sysdate)
from iex_dunnings dunn
where dunning_id = p_dunning_id;
* clchang updated 02/13/2003
* callback_days could be null if callback_yn = 'N';
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
select parent_dunning_id into temp_dunning_id
from iex_dunnings
where dunning_id = p_dunning_id;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - InsertRow');
* Update Delinquency
* Set DUNN_YN = 'N'
*===========================================*/
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateDel');
updated for 11.5.11 - 11/22/04
dunning support aging in 11.5.11 at all levels.
added 2 more parameters: p_object_code, and p_object_id.
so this procedure can age not just del level, but all levels.
*/
Procedure AGING_DEL(
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_delinquency_id IN NUMBER,
p_dunning_plan_id in number,
p_bucket IN VARCHAR2,
p_object_code IN VARCHAR2,
p_object_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_AGING_Bucket_line_ID OUT NOCOPY NUMBER)
IS
-- begin bug 4914799, add max function and check del status
-- begin bug 9393778 gnramasa 19th Feb 10, add trunc function
CURSOR C_GET_PARTY_DAYS (in_party_id number) IS
--SELECT max(sysdate - ar.due_date) days
SELECT max(trunc(sysdate) - trunc(ar.due_date)) days
FROM iex_delinquencies del,
ar_payment_schedules ar
WHERE del.party_cust_id = in_party_id
AND del.payment_schedule_id = ar.payment_schedule_id
and del.status in ('DELINQUENT', 'PREDELINQUENT');
SELECT max(trunc(sysdate) - trunc(ar.due_date)) days
FROM iex_delinquencies del,
ar_payment_schedules ar
WHERE del.cust_account_id = in_acct_id
AND del.payment_schedule_id = ar.payment_schedule_id
and del.status in ('DELINQUENT', 'PREDELINQUENT');
SELECT max(trunc(sysdate) - trunc(ar.due_date)) days
FROM iex_delinquencies del,
ar_payment_schedules ar
WHERE del.customer_site_use_id = in_site_id
AND del.payment_schedule_id = ar.payment_schedule_id
and del.status in ('DELINQUENT', 'PREDELINQUENT');
SELECT (trunc(sysdate) - trunc(ar.due_date)) days
FROM iex_delinquencies del,
ar_payment_schedules ar
WHERE delinquency_ID = in_DEL_ID
AND del.payment_schedule_id = ar.payment_schedule_id
and del.status in ('DELINQUENT', 'PREDELINQUENT');
SELECT l.aging_bucket_line_id
FROM ar_aging_bucket_lines l,
ar_aging_buckets b,
iex_dunning_plans_vl d
WHERE d.dunning_plan_id = p_dunning_plan_id
and d.aging_bucket_id = b.aging_bucket_id
and b.aging_bucket_id = l.aging_bucket_id
--AND round(IN_DAYS) between l.days_start and l.days_to
AND IN_DAYS between l.days_start and l.days_to
and exists (select 1 from iex_ag_dn_xref x
where d.dunning_plan_id = x.dunning_plan_id
and d.aging_bucket_id = x.aging_bucket_id
and x.aging_bucket_line_id = l.aging_bucket_line_id);
SELECT --content_NUMBER,
upper(substr(content_name,instr(content_name,'.')+1,length(content_name)-instr(content_name,'.'))) DocType,
mes_doc_ID
FROM JTF_FM_TEMPLATE_CONTENTS
WHERE template_ID = in_template_ID
AND nvl(upper(f_deletedflag),'0') <>'D'
--bug 3090268
--ORDER BY content_number;
SELECT email_address
FROM HZ_PARTIES
WHERE party_ID = in_party_ID;
SELECT email_address
FROM HZ_CONTACT_POINTS
WHERE owner_table_ID = in_party_ID
AND Contact_point_type = 'EMAIL'
AND primary_flag = 'Y';
SELECT mes.query_id
FROM jtf_FM_query_mes mes,
jtf_FM_query q
WHERE mes.MES_DOC_ID = in_mes_doc_id
AND mes.query_id = q.query_id;
FND_MESSAGE.Set_Token ('INFO', 'No Contents for selected template ');
vstr1 := 'SELECT to_date('' ';
vstr4 := 'SELECT to_date('' ' ;
v_create_string := 'SELECT SYSDATE + ' || l_callback_days ||
', TO_NUMBER(TO_CHAR(SYSDATE + ' || l_callback_days || ',' || '''D''' || ')) FROM DUAL ';
SELECT --content_NUMBER,
upper(substr(content_name,instr(content_name,'.')+1,length(content_name)-instr(content_name,'.'))) DocType,
mes_doc_ID
FROM JTF_FM_TEMPLATE_CONTENTS
WHERE template_ID = in_template_ID
AND nvl(upper(f_deletedflag),'0') <>'D'
--bug 3090268
--ORDER BY content_number;
SELECT email_address
FROM HZ_PARTIES
WHERE party_ID = in_party_ID;
SELECT email_address
FROM HZ_CONTACT_POINTS
WHERE owner_table_ID = in_party_ID
AND Contact_point_type = 'EMAIL'
AND primary_flag = 'Y';
SELECT mes.query_id
FROM jtf_FM_query_mes mes,
jtf_FM_query q
WHERE mes.MES_DOC_ID = in_mes_doc_id
AND mes.query_id = q.query_id;
* if no rows selected based on the bind data,
* skip IEXFmAcctDel.htm content;
* if no rows selected based on the bind data,
* skip IEXFmPart2.htm and IEXFmAcctPreDel.htm contents;
* FFM engine set status FAILURE if no rows selected for one content.
* then our template will be FAILURE
* just because one content has no table data;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - InsertRow');
SELECT q.query_id
FROM iex_query_temp_xref xref,
iex_xml_queries q
WHERE xref.template_id = IN_TEMPLATE_ID
AND xref.query_id = q.query_id
AND q.query_level = IN_LEVEL;
SELECT j.resource_id
FROM jtf_rs_resource_extns j
WHERE j.user_id = in_user_id;
SELECT email_address
FROM HZ_PARTIES
WHERE party_ID = in_party_ID;
SELECT email_address
FROM HZ_CONTACT_POINTS
WHERE owner_table_ID = in_party_ID
AND Contact_point_type = 'EMAIL'
AND primary_flag = 'Y';
select decode(p_contact_type , 'EMAIL', c.email_address,
'PHONE', c.phone_country_code || c.phone_area_code || c.phone_number,
'FAX', c.phone_country_code || c.phone_area_code || c.phone_number, null)
from hz_contact_points c
where contact_point_id = p_contact_point_id;
select QUERY_TEMP_ID, ADDT_QUERY_TEMP_ID, DESTINATION
from IEX_XML_REQUEST_HISTORIES
where XML_REQUEST_ID = p_request_id;
SELECT j.user_id
FROM jtf_rs_resource_extns j
WHERE j.resource_id = p_resource_id;
select query_id from IEX_QUERY_TEMP_XREF
where query_temp_id = p_query_temp_id;
select user_id
from jtf_rs_resource_extns
where resource_id = p_resource_id;
select 'Active'
from xdo_templates_vl xdo
where xdo.template_id = l_template_id
and trunc(sysdate) >= TRUNC (NVL(xdo.start_date, sysdate))
and trunc(sysdate) < TRUNC(NVL(xdo.end_date, sysdate + 1));
select template_name
from xdo_templates_vl xdo
where xdo.template_id = l_template_id;
select nvl(plan.dunning_type,'DAYS_OVERDUE')
into l_dunning_type
from IEX_DUNNING_PLANS_B plan, iex_dunnings dunn
where dunn.xml_request_id = p_request_id
and plan.dunning_plan_id = dunn.dunning_plan_id;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - InsertRow');
select customer_trx_id, payment_schedule_id
into l_transaction_id, l_payment_schedule_id
from ar_payment_schedules_all
where customer_trx_id = p_object_id;
--insert invoice list in to iex_dunning_transactions table for all correspondence.
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Calling IEX_Dunnings_PKG.insert_staged_dunning_row');
IEX_Dunnings_PKG.insert_staged_dunning_row(
px_rowid => l_rowid
, px_dunning_trx_id => x_dunning_trx_id
, p_dunning_id => l_dunning_id
, p_cust_trx_id => l_transaction_id
, p_payment_schedule_id => l_payment_schedule_id
, p_ag_dn_xref_id => null
, p_stage_number => null
, p_created_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_last_update_date => sysdate
, p_last_update_login => FND_GLOBAL.USER_ID
, p_object_version_number => 1.0
);
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Cannot insert record in to iex_dunning_transactions table');
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);
ELSE --xml creation is success, so update the dunning_id rec with xml_request_id
--For staged dunning, dunning record will be created with request_id from resend procedure.
if l_dunning_type <> 'STAGED_DUNNING' then
if l_request_id is not null then
--l_dunning_upd_rec.DUNNING_ID := l_dunning_id;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Update dunning Row with xml request 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_upd_rec
, x_return_status => l_return_status1
, x_msg_count => l_msg_count1
, x_msg_data => l_msg_data1);
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Update Dunning status='|| l_return_status1);
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - InsertRow');
select customer_trx_id, payment_schedule_id
into l_transaction_id, l_payment_schedule_id
from ar_payment_schedules_all
where customer_trx_id = p_object_id;
--insert invoice list in to iex_dunning_transactions table for all correspondence.
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Calling IEX_Dunnings_PKG.insert_staged_dunning_row');
IEX_Dunnings_PKG.insert_staged_dunning_row(
px_rowid => l_rowid
, px_dunning_trx_id => x_dunning_trx_id
, p_dunning_id => l_dunning_id
, p_cust_trx_id => l_transaction_id
, p_payment_schedule_id => l_payment_schedule_id
, p_ag_dn_xref_id => null
, p_stage_number => null
, p_created_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_last_update_date => sysdate
, p_last_update_login => FND_GLOBAL.USER_ID
, p_object_version_number => 1.0
);
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Cannot insert record in to iex_dunning_transactions table');
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);
ELSE --xml creation is success, so update the dunning_id rec with xml_request_id
if l_request_id is not null then
--l_dunning_upd_rec.DUNNING_ID := l_dunning_id;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Update dunning Row with xml request 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_upd_rec
, x_return_status => l_return_status1
, x_msg_count => l_msg_count1
, x_msg_data => l_msg_data1);
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Update Dunning status='|| l_return_status1);
/* We need to update the delivary status for all correspondence.
So commenting the if condition */
/*
IF(p_object_code = 'PARTY' or
p_object_code = 'IEX_ACCOUNT' or
p_object_code = 'IEX_BILLTO' or
p_object_code = 'IEX_DELINQUENCY' or
p_object_code = 'IEX_STRATEGY') THEN
*/
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Update dunning with delivery status: ' || l_dunning_rec.DELIVERY_STATUS);
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateRow');
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
, x_return_status => l_return_status1
, x_msg_count => l_msg_count1
, x_msg_data => l_msg_data1);
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateDunning status='|| l_return_status1);
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - InsertRow');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - InsertRow');
* clchang updated 07/08/2003
* for the new design of using 'Contact Purpose'.
* - contact point is based on purpose, primary,
* type, status;
* clchang updated 01/17/2004
* - the party_id could be rel_party_id, person_party_id, or
* org_party_id.
* 1. chk the party_type by chk_party_type;
SELECT email_address
FROM HZ_PARTIES
WHERE party_ID = in_party_ID;
SELECT party_id
FROM HZ_RELATIONSHIPS
WHERE object_id = in_party_ID
AND relationship_type = 'DUNNING'
AND status = 'A';
SELECT r.object_id --org party id
FROM HZ_PARTIES p, HZ_RELATIONSHIPS r
WHERE r.party_id = in_rel_party_ID
AND p.party_id = r.object_id
AND p.party_type = 'ORGANIZATION';
SELECT p.party_type
FROM HZ_PARTIES p
WHERE p.party_id = in_party_ID;
x_contact_party_id := p_party_id; -- default to origal party_id until updated #3955222
x_contact_party_id := l_party_id; -- default to org party_id until updated #3955222
x_contact_party_id := l_dunning_party_id; -- default to dunning party_id until updated #3955222
x_contact_party_id := l_party_id; -- default to origal party_id until updated #3955222
SELECT email_address,
decode(primary_by_purpose, 'Y',1,2) purpose,
decode(primary_flag, 'Y',1,2) primary
FROM HZ_CONTACT_POINTS
WHERE owner_table_ID = in_party_ID
AND owner_table_name = 'HZ_PARTIES'
--AND Contact_point_type = 'EMAIL'
AND upper(Contact_point_type) = IN_TYPE
AND Contact_point_purpose = 'DUNNING'
AND NVL(do_not_use_flag, 'N') = 'N'
AND (status = 'A' OR status <> 'I')
order by purpose, primary;
SELECT email_address,
decode(primary_flag, 'Y',1,2) primary
FROM HZ_CONTACT_POINTS
WHERE owner_table_ID = in_party_ID
AND owner_table_name = 'HZ_PARTIES'
--AND Contact_point_type = 'EMAIL'
AND upper(Contact_point_type) = IN_TYPE
AND NVL(do_not_use_flag, 'N') = 'N'
AND (status = 'A' OR status <> 'I')
order by primary;
SELECT phone_country_code || phone_area_code||phone_number faxnum,
decode(primary_by_purpose, 'Y',1,2) purpose,
decode(primary_flag, 'Y', 1, 2) primary
FROM HZ_CONTACT_POINTS
WHERE owner_table_ID = in_party_ID
AND owner_table_name = 'HZ_PARTIES'
AND upper(Contact_point_type) = 'PHONE'
AND upper(phone_line_type) = 'FAX'
AND Contact_point_purpose = 'DUNNING'
AND NVL(do_not_use_flag, 'N') = 'N'
AND (status = 'A' OR status <> 'I')
order by purpose, primary;
SELECT phone_country_code || phone_area_code||phone_number faxnum,
decode(primary_flag, 'Y', 1, 2) primary
FROM HZ_CONTACT_POINTS
WHERE owner_table_ID = in_party_ID
AND owner_table_name = 'HZ_PARTIES'
AND upper(Contact_point_type) = 'PHONE'
AND upper(phone_line_type) = 'FAX'
AND NVL(do_not_use_flag, 'N') = 'N'
AND (status = 'A' OR status <> 'I')
order by primary;
SELECT upper(query_string)
FROM jtf_fm_queries_all
WHERE query_id = IN_ID
AND nvl(upper(f_deletedflag),'0') <>'D';
-- clchang updated for sql bind var 05/07/2003
vstr1 VARCHAR2(100) ;
vstr1 := 'SELECT COUNT(*) ';
SELECT dunning_ID
FROM IEX_DUNNINGS
WHERE
STATUS = 'OPEN'
AND dunning_level = IN_TYPE
AND dunning_object_id = IN_ID;
UPDATE IEX_DUNNINGS
SET CALLBACK_YN = 'N',
STATUS = 'CLOSE',
LAST_UPDATE_DATE = sysdate
WHERE Dunning_id = dunning_tbl(i);
SELECT dunning_ID
FROM IEX_DUNNINGS
WHERE
STATUS = 'OPEN'
AND dunning_level = IN_TYPE
AND dunning_object_id = IN_ID
AND (ag_dn_xref_id = IN_DUNN_PLAN_LINE_ID OR
ag_dn_xref_id IS NULL);
UPDATE IEX_DUNNINGS
SET CALLBACK_YN = 'N',
STATUS = 'CLOSE',
LAST_UPDATE_DATE = sysdate
WHERE Dunning_id = dunning_tbl(i);
SELECT dunning_ID,
delinquency_id,
dunning_object_id,
to_char(callback_date, 'YYYYMMDD')
FROM IEX_DUNNINGS
WHERE STATUS = 'OPEN'
AND CALLBACK_YN = 'Y'
AND dunning_level = IN_LEVEL
AND to_char(callback_date, 'YYYYMMDD') <= to_char(sysdate,'YYYYMMDD');
SELECT 1
FROM IEX_DELINQUENCIES d
WHERE d.delinquency_ID = in_del_ID
AND STATUS in ('DELINQUENT', 'PREDELINQUENT');
SELECT 1
FROM IEX_DELINQUENCIES d
WHERE d.cust_account_ID = in_ACCT_ID
AND STATUS in ('DELINQUENT', 'PREDELINQUENT');
SELECT 1
FROM IEX_DELINQUENCIES d
WHERE d.party_cust_id = in_party_id
AND STATUS in ('DELINQUENT', 'PREDELINQUENT');
SELECT 1
FROM IEX_DELINQUENCIES d
WHERE d.customer_site_use_id = in_SITE_ID
AND STATUS in ('DELINQUENT', 'PREDELINQUENT');
* Update Dunning
*=====================*/
IF (l_skip = 0) THEN
nCnt := nCnt + 1;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - update_dunnings cnt='||nCnt);
UPDATE IEX_DUNNINGS
SET CALLBACK_YN = 'N',
LAST_UPDATE_DATE = sysdate
WHERE Dunning_id = dunning_tbl(i);
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - DunnUpdatedNum='||nCnt);
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - DunnUpdatedNum='||dunning_tbl.count);
l_task_query := 'select tsk.task_id,'
||'tsk.object_version_number'
||' from jtf_tasks_b tsk,'
||' jtf_task_types_tl typ,'
||' jtf_task_statuses_b st,'
||' jtf_task_references_b ref,'
||' iex_dunnings dun'
||' where tsk.task_type_id=typ.task_type_id'
||' and typ.name=''Callback'''
||' and tsk.task_status_id=st.task_status_id'
||' and nvl(st.closed_flag, ''N'') <>''Y'''
||' and nvl(st.cancelled_flag, ''N'')<>''Y'''
||' and nvl(st.completed_flag, ''N'')<>''Y'''
||' and tsk.task_id=ref.task_id'
||' and ref.object_type_code=''IEX_DUNNING'''
||' and ref.object_id=dun.dunning_id'
||' and dun.dunning_level='''||p_running_level||'''';
l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
||' where del.status in (''DELINQUENT'',''PREDELINQUENT'') '
||' and dun.dunning_object_id=del.party_cust_id)';
l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
||' where del.status in (''DELINQUENT'',''PREDELINQUENT'') '
||' and dun.dunning_object_id=del.cust_account_id)';
l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
||' where del.status in (''DELINQUENT'',''PREDELINQUENT'') '
||' and dun.dunning_object_id=del.customer_site_use_id)';
l_task_query:=l_task_query||' and not exists(select 1 from iex_delinquencies_all del '
||' where del.status in (''DELINQUENT'',''PREDELINQUENT'') '
||' and dun.dunning_object_id=del.delinquency_id)';
JTF_TASKS_PUB.UPDATE_TASK(
P_API_VERSION => p_api_version,
P_INIT_MSG_LIST => p_init_msg_list,
P_COMMIT => p_commit,
P_OBJECT_VERSION_NUMBER => l_object_version_number,
P_TASK_ID => l_inv_task_id,
P_TASK_STATUS_NAME => 'Cancelled',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT d.delinquency_ID,
d.party_cust_id,
d.cust_account_id,
a.account_number,
d.customer_site_use_id,
ar.trx_number,
ar.payment_schedule_id,
h.party_name
FROM IEX_DELINQUENCIES d,
HZ_PARTIES h,
HZ_CUST_ACCOUNTS a,
AR_PAYMENT_SCHEDULES ar
WHERE d.delinquency_ID = in_del_ID
AND h.party_id = d.party_cust_id
AND d.cust_account_id = a.cust_account_id
AND ar.payment_schedule_id = d.payment_schedule_id
AND ar.class = 'INV';
SELECT d.party_cust_id,
d.cust_account_id,
a.account_number,
d.customer_site_use_id,
h.party_name,
u.location -- added for bug 14738490
FROM IEX_DELINQUENCIES d,
HZ_PARTIES h,
HZ_CUST_ACCOUNTS a,
HZ_CUST_SITE_USES u -- -- added for bug 14738490
WHERE d.customer_site_use_id = in_SITE_ID
AND u.site_use_id = d.customer_site_use_id
AND h.party_id = d.party_cust_id
AND d.cust_account_id = a.cust_account_id;
SELECT d.party_cust_id,
d.cust_account_id,
a.account_number,
h.party_name
FROM IEX_DELINQUENCIES d,
HZ_PARTIES h,
HZ_CUST_ACCOUNTS a
WHERE d.cust_account_ID = in_ACCT_ID
AND h.party_id = d.party_cust_id
AND d.cust_account_id = a.cust_account_id;
SELECT d.party_cust_id,
h.party_name
FROM IEX_DELINQUENCIES d,
HZ_PARTIES h
WHERE d.party_cust_id = in_party_id
AND h.party_id = d.party_cust_id;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'No default collector available to assign to callback. Please update default collector.');
SELECT jvl.name into l_taskname
FROM jtf_task_types_vl jvl , jtf_task_types_tl jtl
WHERE jvl.task_type_id= jtl.task_type_id
AND jtl.language='US'
AND jtl.name = 'Callback'
AND trunc(NVL (jvl.end_date_active, SYSDATE)) >= trunc(SYSDATE)
AND trunc(NVL (jvl.start_date_active, SYSDATE)) <= trunc(SYSDATE);
SELECT jvl.name into l_statusname
FROM jtf_task_statuses_vl jvl , jtf_task_statuses_tl jtl
WHERE jvl.task_status_id= jtl.task_status_id
AND jtl.language='US'
AND jtl.name = 'Open'
AND trunc(NVL (jvl.end_date_active, SYSDATE)) >= trunc(SYSDATE)
AND trunc(NVL (jvl.start_date_active, SYSDATE)) <= trunc(SYSDATE);
* Clchang updated 09/19/2002 for Bug 2242346
* to create a callback,
* we got resource_id from iex_delinquencyies before;
SELECT customer_id
FROM jtf_tasks_vl
WHERE customer_id = p_party_id;*/
SELECT DISTINCT person_id, salesforce_id
FROM as_accesses acc, jtf_rs_resource_extns rs
WHERE acc.customer_id = p_party_id and rs.resource_id = acc.salesforce_id
and rs.user_id is not null;
SELECT DISTINCT rs.resource_id, 0
FROM as_rpt_managers_v m, as_accesses acc, jtf_rs_resource_extns rs
WHERE m.person_id = acc.person_id
AND m.manager_person_id = rs.source_id
AND acc.customer_id = p_party_id
AND nvl(rs.end_date_active,sysdate) >= trunc(sysdate);
SELECT t.owner_id, count(t.owner_id)
FROM jtf_tasks_vl t, jtf_task_statuses_vl s
WHERE t.customer_id = p_party_id
AND upper(t.owner_type_code) = 'RS_EMPLOYEE'
AND t.task_name = 'Dunning Callback'
AND t.task_status_id = s.task_status_id
AND upper(s.name) = 'OPEN'
AND t.owner_id in ( select DISTINCT rs.resource_id
from as_rpt_managers_v m, as_accesses acc,
jtf_rs_resource_extns rs
where m.person_id = acc.person_id
and m.manager_person_id = rs.source_id
and acc.customer_id = p_party_id
AND nvl(rs.end_date_active,sysdate) >= trunc(sysdate) )
GROUP BY t.owner_id;
SELECT DISTINCT rs.resource_id, 0
FROM as_rpt_managers_v m, as_accesses acc, jtf_rs_resource_extns rs
WHERE m.person_id = acc.person_id
AND m.manager_person_id = rs.source_id
AND acc.customer_id = p_party_id
AND nvl(rs.end_date_active,sysdate) >= trunc(sysdate)
AND rs.resource_id not in ( select distinct t2.owner_id
from jtf_tasks_vl t2,
jtf_task_statuses_vl s2
where t2.customer_id = p_party_id
and upper(t2.owner_type_code) = 'RS_EMPLOYEE'
AND t2.task_status_id = s2.task_status_id
AND upper(s2.name) = 'OPEN'
and t2.task_name = 'Dunning Callback');
SELECT t.owner_id --, count(t.owner_id)
FROM jtf_tasks_vl t, jtf_task_statuses_vl s
WHERE t.customer_id = p_party_id
AND upper(t.owner_type_code) = 'RS_EMPLOYEE'
AND t.task_name = 'Dunning Callback'
AND t.task_status_id = s.task_status_id
AND upper(s.name) = 'OPEN'
GROUP BY t.owner_id
HAVING COUNT(t.owner_id) = (select min(count(t2.owner_id))
from jtf_tasks_vl t2, jtf_task_statuses_vl s2
WHERE t2.customer_id = p_party_id
AND upper(t2.owner_type_code) = 'RS_EMPLOYEE'
AND t2.task_name = 'Dunning Callback'
AND t2.task_status_id = s2.task_status_id
AND upper(s2.name) = 'OPEN'
group by t2.owner_id );
SELECT t.owner_id --, count(t.owner_id)
FROM jtf_tasks_vl t, jtf_task_statuses_vl s
WHERE t.customer_id = p_party_id
AND upper(t.owner_type_code) = 'RS_EMPLOYEE'
AND t.task_name = 'Dunning Callback'
AND t.task_status_id = s.task_status_id
AND upper(s.name) = 'OPEN'
AND t.owner_id in ( select DISTINCT rs.resource_id
from as_accesses acc,
jtf_rs_resource_extns rs
where acc.customer_id = p_party_id
and rs.resource_id = acc.salesforce_id
and rs.category = 'EMPLOYEE'
and rs.user_id is not null
AND nvl(rs.end_date_active,sysdate) >= trunc(sysdate) )
GROUP BY t.owner_id
HAVING COUNT(t.owner_id) = (select min(count(t2.owner_id))
from jtf_tasks_vl t2, jtf_task_statuses_vl s2
WHERE t2.customer_id = p_party_id
AND upper(t2.owner_type_code) = 'RS_EMPLOYEE'
AND t2.task_name = 'Dunning Callback'
AND t2.task_status_id = s2.task_status_id
AND upper(s2.name) = 'OPEN'
AND t2.owner_id in ( select DISTINCT rs.resource_id
from as_accesses acc,
jtf_rs_resource_extns rs
where acc.customer_id = p_party_id
and rs.resource_id = acc.salesforce_id
and rs.category = 'EMPLOYEE'
and rs.user_id is not null
AND nvl(rs.end_date_active,sysdate) >= trunc(sysdate) )
group by t2.owner_id );
SELECT t.owner_id, count(t.owner_id)
FROM jtf_tasks_vl t, jtf_task_statuses_vl s
WHERE t.customer_id = p_party_id
AND upper(t.owner_type_code) = 'RS_EMPLOYEE'
AND t.task_name = 'Dunning Callback'
AND t.task_status_id = s.task_status_id
AND upper(s.name) = 'OPEN'
AND t.owner_id in ( select DISTINCT rs.resource_id
from as_accesses acc,
jtf_rs_resource_extns rs
where acc.customer_id = p_party_id
and rs.resource_id = acc.salesforce_id
and rs.category = 'EMPLOYEE'
and rs.user_id is not null
AND nvl(rs.end_date_active,sysdate) >= trunc(sysdate) )
GROUP BY t.owner_id;
SELECT DISTINCT rs.resource_id, 0
FROM as_accesses acc, jtf_rs_resource_extns rs
WHERE acc.customer_id = p_party_id
AND nvl(rs.end_date_active,sysdate) >= trunc(sysdate)
AND rs.resource_id = acc.salesforce_id
and rs.category = 'EMPLOYEE'
AND rs.user_id is not null
AND rs.resource_id not in ( select distinct t2.owner_id
from jtf_tasks_vl t2,
jtf_task_statuses_vl s2
where t2.customer_id = p_party_id
and upper(t2.owner_type_code) = 'RS_EMPLOYEE'
AND t2.task_status_id = s2.task_status_id
AND upper(s2.name) = 'OPEN'
and t2.task_name = 'Dunning Callback');
select ps.invoice_currency_code
from ar_payment_schedules_all ps,
iex_delinquencies del
where ps.payment_schedule_id (+)= del.payment_schedule_id
and del.party_cust_id = in_id;
select ps.invoice_currency_code
from ar_payment_schedules_all ps,
--iex_delinquencies_all del
iex_delinquencies del
where ps.payment_schedule_id (+)= del.payment_schedule_id
and del.cust_account_id = in_id;
select ps.invoice_currency_code
from ar_payment_schedules_all ps,
iex_delinquencies del
where ps.payment_schedule_id (+)= del.payment_schedule_id
and del.customer_site_use_id = in_id;
select sum(ps.acctd_amount_due_remaining)
from ar_payment_schedules_all ps,
iex_delinquencies del
where ps.payment_schedule_id (+)= del.payment_schedule_id
and del.party_cust_id = in_id;
select sum(ps.acctd_amount_due_remaining)
from ar_payment_schedules_all ps,
--iex_delinquencies_all del
iex_delinquencies del
where ps.payment_schedule_id (+)= del.payment_schedule_id
and del.cust_account_id = in_id;
select sum(ps.acctd_amount_due_remaining)
from ar_payment_schedules_all ps,
iex_delinquencies del
where ps.payment_schedule_id (+)= del.payment_schedule_id
and del.customer_site_use_id = in_id;
select sum(ps.acctd_amount_due_remaining)
from ar_payment_schedules_all ps,
iex_dunning_transactions dtrx
where ps.payment_schedule_id = dtrx.payment_schedule_id
and dtrx.dunning_id = dunn_id;
select del.party_cust_id
from iex_delinquencies del
where del.cust_account_id = in_id;
SELECT
count(*) no_of_rec_processed,
sum(decode(delivery_status,NULL,1,0)) no_of_success_records,
sum(decode(delivery_status,NULL,0,1)) no_of_failure_records
FROM iex_dunnings id
WHERE id.request_id = p_req_id
AND id.object_type <> 'IEX_INVOICES'
AND id.dunning_id = (SELECT MAX(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 ' ||
'id.dunning_object_id PARTY_ID, ' ||
'p.party_name PARTY_NAME, ' ||
'id.dunning_object_id DUNNING_OBJECT_ID, ' ||
'id.xml_template_id TEMPLATE_ID, ' ||
'xtvl.template_name TEMPLATE_NAME, ' ||
'id.delivery_status ERROR, ' ||
'fnd_message.get_string(''IEX'',id.delivery_status) ERROR_DESC, ' ||
'iex_utilities.get_lookup_meaning(''IEX_FULFILLMENT_SEND_METHOD'',id.dunning_method) DUNNING_METHOD, '||
'ixr.destination DESTINATION, ' ||
'decode(id.DUNNING_MODE, ''DRAFT'', iex_utilities.get_lookup_meaning(''IEX_CONFIRMATION_MODE'', nvl(id.CONFIRMATION_MODE,''DRAFT'')),iex_utilities.get_lookup_meaning(''IEX_CONFIRMATION_MODE'',''CONFIRMED'')) CONFIRMATION_STATUS '||
--'ixr.xml_request_id REQUEST_ID ' ||
--' ''http://techcm.us.oracle.com:8000/OA_HTML/IEXDUNCPRRedirect.jsp?RequestId='' || ixr.xml_request_id DOWNLOAD_URL ' ||
'from iex_xml_request_histories ixr, ' ||
'hz_parties p, ' ||
'iex_dunnings id, ' ||
'XDO_TEMPLATES_B xtb, ' ||
'XDO_TEMPLATES_TL xtvl ' ||
'where id.dunning_object_id = p.party_id ' ||
'and ixr.xml_request_id (+) = id.xml_request_id ' ||
'and id.request_id = :l_request_id ' ||
'and xtb.template_code=xtvl.template_code ' ||
'and xtb.template_id = id.xml_template_id ' ||
'and xtvl.language=userenv(''LANG'') ' ||
'AND id.object_type <> ''IEX_INVOICES'' ' ||
'and id.dunning_id = (select max(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'' ) ' ||
--'order by lower(p.party_name)' ;
'select ' ||
'p.party_id PARTY_ID, ' ||
'p.party_name PARTY_NAME, ' ||
'hcu.account_number ACCOUNT_NUMBER, ' ||
'id.dunning_object_id DUNNING_OBJECT_ID, ' ||
'id.xml_template_id TEMPLATE_ID, ' ||
'xtvl.template_name TEMPLATE_NAME, ' ||
'id.delivery_status ERROR, ' ||
'fnd_message.get_string(''IEX'',id.delivery_status) ERROR_DESC, ' ||
'iex_utilities.get_lookup_meaning(''IEX_FULFILLMENT_SEND_METHOD'',id.dunning_method) DUNNING_METHOD, '||
'ixr.destination DESTINATION, ' ||
'decode(id.DUNNING_MODE, ''DRAFT'', iex_utilities.get_lookup_meaning(''IEX_CONFIRMATION_MODE'', nvl(id.CONFIRMATION_MODE,''DRAFT'')),iex_utilities.get_lookup_meaning(''IEX_CONFIRMATION_MODE'',''CONFIRMED'')) CONFIRMATION_STATUS '||
--'ixr.xml_request_id REQUEST_ID ' ||
--' ''http://techcm.us.oracle.com:8000/OA_HTML/IEXDUNCPRRedirect.jsp?RequestId='' || ixr.xml_request_id DOWNLOAD_URL ' ||
'from iex_xml_request_histories ixr, ' ||
'hz_parties p, ' ||
'hz_cust_accounts hcu, ' ||
'iex_dunnings id, ' ||
'XDO_TEMPLATES_B xtb, ' ||
'XDO_TEMPLATES_TL xtvl ' ||
'where id.dunning_object_id = hcu.cust_account_id ' ||
'and hcu.party_id = p.party_id ' ||
'and ixr.xml_request_id (+) = id.xml_request_id ' ||
'and id.request_id = :l_request_id ' ||
'and xtb.template_code=xtvl.template_code ' ||
'and xtb.template_id = id.xml_template_id ' ||
'and xtvl.language=userenv(''LANG'') ' ||
'AND id.object_type <> ''IEX_INVOICES'' ' ||
'and id.dunning_id = (select max(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'' ) ' ||
--'order by lower(p.party_name)' ;
'select ' ||
'p.party_id PARTY_ID, ' ||
'p.party_name PARTY_NAME, ' ||
'hcu.account_number ACCOUNT_NUMBER, ' ||
'site_uses.location LOCATION, ' ||
'id.dunning_object_id DUNNING_OBJECT_ID, ' ||
'id.xml_template_id TEMPLATE_ID, ' ||
'xtvl.template_name TEMPLATE_NAME, ' ||
'id.delivery_status ERROR, ' ||
'fnd_message.get_string(''IEX'',id.delivery_status) ERROR_DESC, ' ||
'iex_utilities.get_lookup_meaning(''IEX_FULFILLMENT_SEND_METHOD'',id.dunning_method) DUNNING_METHOD, '||
'ixr.destination DESTINATION, ' ||
'decode(id.DUNNING_MODE, ''DRAFT'', iex_utilities.get_lookup_meaning(''IEX_CONFIRMATION_MODE'', nvl(id.CONFIRMATION_MODE,''DRAFT'')),iex_utilities.get_lookup_meaning(''IEX_CONFIRMATION_MODE'',''CONFIRMED'')) CONFIRMATION_STATUS '||
--'ixr.xml_request_id REQUEST_ID ' ||
--' ''http://techcm.us.oracle.com:8000/OA_HTML/IEXDUNCPRRedirect.jsp?RequestId='' || ixr.xml_request_id DOWNLOAD_URL ' ||
'from iex_xml_request_histories ixr, ' ||
'hz_parties p, ' ||
'hz_cust_accounts hcu,' ||
'hz_cust_acct_sites_all acct_sites, ' ||
'hz_cust_site_uses_all site_uses, ' ||
'iex_dunnings id, ' ||
'XDO_TEMPLATES_B xtb, ' ||
'XDO_TEMPLATES_TL xtvl ' ||
'where id.dunning_object_id = site_uses.site_use_id ' ||
'and acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id ' ||
'and hcu.cust_account_id = acct_sites.cust_account_id ' ||
'and p.party_id = hcu.party_id ' ||
'and ixr.xml_request_id (+) = id.xml_request_id ' ||
'and id.request_id = :l_request_id ' ||
'and xtb.template_code=xtvl.template_code ' ||
'and xtb.template_id = id.xml_template_id ' ||
'and xtvl.language=userenv(''LANG'') ' ||
'AND id.object_type <> ''IEX_INVOICES'' ' ||
--'and nvl(id.confirmation_mode,''CONFIRMED'') <> ''REJECTED'' ' ||
'and id.dunning_id = (select max(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'' ) ' ||
-- 'order by lower(p.party_name)' ;
'select ' ||
'id.dunning_object_id PARTY_ID, ' ||
'p.party_name PARTY_NAME, ' ||
'aps.trx_number TRANSACTION_NUMBER, ' ||
'id.dunning_object_id DUNNING_OBJECT_ID, ' ||
'id.xml_template_id TEMPLATE_ID, ' ||
'xtvl.template_name TEMPLATE_NAME, ' ||
'id.delivery_status ERROR, ' ||
'fnd_message.get_string(''IEX'',id.delivery_status) ERROR_DESC, ' ||
'iex_utilities.get_lookup_meaning(''IEX_FULFILLMENT_SEND_METHOD'',id.dunning_method) DUNNING_METHOD, '||
'ixr.destination DESTINATION, ' ||
'decode(id.DUNNING_MODE, ''DRAFT'', iex_utilities.get_lookup_meaning(''IEX_CONFIRMATION_MODE'', nvl(id.CONFIRMATION_MODE,''DRAFT'')),iex_utilities.get_lookup_meaning(''IEX_CONFIRMATION_MODE'',''CONFIRMED'')) CONFIRMATION_STATUS '||
--'ixr.xml_request_id REQUEST_ID ' ||
--' ''http://techcm.us.oracle.com:8000/OA_HTML/IEXDUNCPRRedirect.jsp?RequestId='' || ixr.xml_request_id DOWNLOAD_URL ' ||
'from iex_xml_request_histories ixr, ' ||
'hz_parties p, ' ||
'iex_dunnings id, ' ||
'XDO_TEMPLATES_B xtb, ' ||
'XDO_TEMPLATES_TL xtvl, ' ||
'iex_delinquencies_all del, ' ||
'ar_payment_schedules_all aps ' ||
'where id.dunning_object_id = del.delinquency_id ' ||
'and del.payment_Schedule_id = aps.payment_Schedule_id ' ||
'and del.party_cust_id = p.party_id ' ||
'and ixr.xml_request_id (+) = id.xml_request_id ' ||
'and id.request_id = :l_request_id ' ||
'and xtb.template_code=xtvl.template_code ' ||
'and xtb.template_id = id.xml_template_id ' ||
'and xtvl.language=userenv(''LANG'') ' ||
'AND id.object_type <> ''IEX_INVOICES'' ' ||
'and id.dunning_id = (select max(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'' ) ' ||
-- 'order by lower(p.party_name)' ;
select to_char(sysdate, 'YYYY-MM-DD')
into l_report_date
from dual;
select name
into l_dunning_plan
from iex_dunning_plans_vl
where dunning_plan_id= p_dunning_plan_id;
select idt.cust_trx_id,
dunn.object_id,
dunn.object_type
from iex_dunning_transactions idt,
iex_dunnings dunn,
iex_ag_dn_xref xref
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;
vPLSQL1 := 'select idt.cust_trx_id, ' ||
' dunn.object_id, ' ||
' dunn.object_type, ' ||
' dunn.dunning_method ' ||
' 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 ca.party_id into l_party_id
FROM hz_cust_site_uses site_uses,
hz_cust_acct_sites acct_sites,
hz_cust_accounts ca
WHERE site_uses.site_use_id = l_object_id
AND acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id
AND ca.cust_account_id = acct_sites.cust_account_id;
SELECT ca.PARTY_CUST_ID into l_party_id
FROM IEX_DELINQUENCIES ca
WHERE ca.DELINQUENCY_ID = l_object_id;
SELECT ca.party_id into l_party_id
FROM hz_cust_accounts ca
WHERE ca.cust_account_id = l_object_id;
select location_id into l_location_id
from ast_locations_v
where party_id = l_party_id
AND primary_flag = 'Y';
Procedure INSERT_DUNNING_TRANSACTION
(p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_delinquencies_tbl IN IEX_DELINQUENCY_PUB.DELINQUENCY_TBL_TYPE,
p_dunning_id IN NUMBER,
p_correspondence_date IN DATE,
p_ag_dn_xref_id IN NUMBER ,
p_running_level IN VARCHAR2,
p_grace_days IN NUMBER,
p_include_dispute_items IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
,p_workitem_id IN NUMBER) -- bug 14772139
IS
l_api_name CONSTANT VARCHAR2(30) := 'Inser_dunning_transaction';
select nvl(dunn.include_current ,'N'),
nvl(dunn.include_unused_payments_flag,'N')
from iex_ag_dn_xref xref,
iex_dunning_plans_b dunn
where AG_DN_XREF_ID = p_ag_dn_xref_id
and xref.dunning_plan_id = dunn.dunning_plan_id;
SAVEPOINT INSERT_DUNNING_TRANSACTION_PVT;
select nvl(b.INCLUDE_CURRENT,'N') ,nvl(INCLUDE_UNUSED_PAYMENTS_FLAG,'N')
into l_include_curr_inv, l_include_unapp_rec
from iex_strategy_work_items a, IEX_STRY_TEMP_WORK_ITEMS_VL b
where a.work_item_template_id = b.work_item_temp_id
and a.work_item_id = p_workitem_id -- p_grace_days -- indicating workitem Id when p_ag_dn_xref_id is 0 from iexpstmb.pls
and b.work_type in ('AUTOMATIC','WORKFLOW'); -- bug 14772139
vPLSQL := 'select del.delinquency_id, ' ||
' del.transaction_id, ' ||
' del.payment_schedule_id ' ||
' from iex_delinquencies del, ' ||
' ar_payment_schedules arp ' ||
' where del.payment_schedule_id = arp.payment_schedule_id ' ||
' and del.status in (''DELINQUENT'',''PREDELINQUENT'') ' ||
-- ' and del.staged_dunning_level is NULL ' || commented to fix 12621875 snuthala 6/6/2011
' and (trunc(arp.due_date) + :p_gra_days) <= :p_corr_date ' ||
' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) '; -- bug 14772139
' (SELECT NVL(min(min_dunning_invoice_amount),0) '||
' FROM hz_cust_profile_amts '||
' WHERE site_use_id IS NULL '||
' AND cust_account_id = arp.customer_id '||
' AND currency_code = arp.invoice_currency_code),0) '||
' order by del.payment_schedule_id';
' and arp.amount_due_remaining >= nvl ((select nvl(min(min_dunning_invoice_amount),0) '||
' from hz_cust_profile_amts '||
' where site_use_id = arp.CUSTOMER_SITE_USE_ID '||
' and currency_code = arp.invoice_currency_code),0) '||
' order by del.payment_schedule_id';
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - InsertRow');
IEX_Dunnings_PKG.insert_staged_dunning_row(
px_rowid => l_rowid
, px_dunning_trx_id => x_dunning_trx_id
, p_dunning_id => p_dunning_id
, p_cust_trx_id => l_transaction_id
, p_payment_schedule_id => l_payment_schedule_id
, p_ag_dn_xref_id => p_ag_dn_xref_id
, p_stage_number => -1
, p_created_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_last_update_date => sysdate
, p_last_update_login => FND_GLOBAL.USER_ID
, p_object_version_number => 1.0);
--reset the x_dunning_trx_id, so that will get new no when inserting 2nd record.
x_dunning_trx_id := null;
vPLSQL := 'select del.delinquency_id, ' ||
' del.transaction_id, ' ||
' del.payment_schedule_id ' ||
' from iex_delinquencies del, ' ||
' ar_payment_schedules arp ' ||
' where del.payment_schedule_id = arp.payment_schedule_id ' ||
' and del.status in (''DELINQUENT'',''PREDELINQUENT'') ' ||
--' and del.staged_dunning_level is NULL ' || commented to fix 12621875 snuthala 6/6/2011
' and (trunc(arp.due_date) + 0) <= :p_corr_date ' ||
' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ';
' (SELECT NVL(min(min_dunning_invoice_amount),0) '||
' FROM hz_cust_profile_amts '||
' WHERE site_use_id IS NULL '||
' AND cust_account_id = arp.customer_id '||
' AND currency_code = arp.invoice_currency_code),0) ';
' and arp.amount_due_remaining >= nvl ((select nvl(min(min_dunning_invoice_amount),0) '||
' from hz_cust_profile_amts '||
' where site_use_id = arp.CUSTOMER_SITE_USE_ID '||
' and currency_code = arp.invoice_currency_code),0) ';
-- start added 12/23/2011 fix 13519242 to stop inserting duplicate rows
vPLSQL3 := vPLSQL3 || ' and not exists (select 1 from iex_dunning_transactions dun_trx '||
' where dun_trx.payment_schedule_id = del.payment_schedule_id '||
' and dun_trx.dunning_id = :p_dunning_id ) ' ||
' order by del.payment_schedule_id';
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - InsertRow');
IEX_Dunnings_PKG.insert_staged_dunning_row(
px_rowid => l_rowid
, px_dunning_trx_id => x_dunning_trx_id
, p_dunning_id => p_dunning_id
, p_cust_trx_id => l_transaction_id
, p_payment_schedule_id => l_payment_schedule_id
, p_ag_dn_xref_id => p_ag_dn_xref_id
, p_stage_number => 0
, p_created_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_last_update_date => sysdate
, p_last_update_login => FND_GLOBAL.USER_ID
, p_object_version_number => 1.0
);
--reset the x_dunning_trx_id, so that will get new no when inserting 2nd record.
x_dunning_trx_id := null;
'select del.delinquency_id, ' ||
' del.transaction_id, ' ||
' del.payment_schedule_id ' ||
' from iex_delinquencies del, ar_payment_schedules arp ' ||
' where del.payment_schedule_id = arp.payment_schedule_id ' ||
' and del.status = ''CURRENT'' ' ||
-- ' and del.staged_dunning_level is NULL ' || -- commented for bug#15932721 schekuri 30-Nov-12
' and arp.status = ''OP'' ' ||
-- ' and arp.class = ''INV'' ' || -- Bills Receivables
' and (arp.class = ''INV'' or arp.class = ''BR'') ' || -- Bills Receivables
' and (trunc(arp.due_date) + :p_gra_days) <= :p_corr_date ' || -- Bills Receivables
' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - InsertRow');
IEX_Dunnings_PKG.insert_staged_dunning_row(
px_rowid => l_rowid
, px_dunning_trx_id => x_dunning_trx_id
, p_dunning_id => p_dunning_id
, p_cust_trx_id => l_transaction_id
, p_payment_schedule_id => l_payment_schedule_id
, p_ag_dn_xref_id => p_ag_dn_xref_id
, p_stage_number => -1 --changed by schekuri bug#15932721
, p_created_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_last_update_date => sysdate
, p_last_update_login => FND_GLOBAL.USER_ID
, p_object_version_number => 1.0
);
--reset the x_dunning_trx_id, so that will get new no when inserting 2nd record.
x_dunning_trx_id := null;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Insert current invoices');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Start insert current invoices');
vPLSQL2 := 'select arp.customer_trx_id, ' ||
' arp.payment_schedule_id ' ||
' from ar_payment_schedules arp, hz_cust_accounts hca ' ||
' where arp.customer_id = hca.cust_account_id ' ||
' and hca.party_id = :p_party_id ' ||
-- ' and trunc(arp.due_date) > trunc(:p_corr_date) ' ||
' and arp.status = ''OP'' ' ||
' and arp.amount_due_remaining <> 0 ' ||
--' and arp.class = ''INV'' ' || -- Bills Receivables
' and (arp.class = ''INV'' or arp.class = ''BR'') ' || -- Bills Receivables
' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
' and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ||
' 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
' order by arp.payment_schedule_id';
vPLSQL2 := 'select arp.customer_trx_id, ' ||
' arp.payment_schedule_id ' ||
' from ar_payment_schedules arp ' ||
' where arp.customer_id = :p_cust_acct_id ' ||
--' and trunc(arp.due_date) > trunc(:p_corr_date) ' ||
' and arp.status = ''OP'' ' ||
' and arp.amount_due_remaining <> 0 ' ||
--' and arp.class = ''INV'' ' || -- Bills Receivables
' and (arp.class = ''INV'' or arp.class = ''BR'') ' || -- Bills Receivables
' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
' and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ||
' 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
' order by arp.payment_schedule_id';
vPLSQL2 := 'select arp.customer_trx_id, ' ||
' arp.payment_schedule_id ' ||
' from ar_payment_schedules arp ' ||
' where arp.customer_site_use_id = :p_site_use_id ' ||
--' and trunc(arp.due_date) > trunc(:p_corr_date) ' ||
' and arp.status = ''OP'' ' ||
' and arp.amount_due_remaining <> 0 ' ||
--' and arp.class = ''INV'' ' || -- Bills Receivables
' and (arp.class = ''INV'' or arp.class = ''BR'') ' || -- Bills Receivables
' and nvl(arp.amount_in_dispute,0) = decode(:p_include_dis_items, ''Y'', nvl(arp.amount_in_dispute,0), 0) ' ||
' and not exists (select 1 from iex_delinquencies del where del.payment_schedule_id = arp.payment_schedule_id and del.status <> ''CURRENT'' ) ' ||
' 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
' order by arp.payment_schedule_id';
IEX_Dunnings_PKG.insert_staged_dunning_row(
px_rowid => l_rowid
, px_dunning_trx_id => x_dunning_trx_id
, p_dunning_id => p_dunning_id
, p_cust_trx_id => l_customer_trx_id
, p_payment_schedule_id => l_payment_schedule_id
, p_ag_dn_xref_id => p_ag_dn_xref_id
, p_stage_number => 0
, p_created_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_last_update_date => sysdate
, p_last_update_login => FND_GLOBAL.USER_ID
, p_object_version_number => 1.0
);
--reset the x_dunning_trx_id, so that will get new no when inserting 2nd record.
x_dunning_trx_id := null;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - End insert current invoices');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Insert unapplied receipts and On Account Credit memos');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Start insert unapplied receipts');
vPLSQL2 := 'select arp.payment_schedule_id ' ||
' from ar_payment_schedules arp, ' ||
' hz_cust_accounts hca ' ||
' where arp.customer_id = hca.cust_account_id ' ||
' and hca.party_id = :p_party_id ' ||
' and arp.status = ''OP'' ' ||
' and arp.amount_due_remaining <> 0 ' ||
' and arp.class = ''PMT'' ' ||
' order by arp.payment_schedule_id';
vPLSQL2 := 'select arp.payment_schedule_id ' ||
' from ar_payment_schedules arp ' ||
' where arp.customer_id = :p_cust_acct_id ' ||
' and arp.status = ''OP'' ' ||
' and arp.amount_due_remaining <> 0 ' ||
' and arp.class = ''PMT'' ' ||
' order by arp.payment_schedule_id';
vPLSQL2 := 'select arp.payment_schedule_id ' ||
' from ar_payment_schedules arp ' ||
' where arp.customer_site_use_id = :p_site_use_id ' ||
' and arp.status = ''OP'' ' ||
' and arp.amount_due_remaining <> 0 ' ||
' and arp.class = ''PMT'' ' ||
' order by arp.payment_schedule_id';
IEX_Dunnings_PKG.insert_staged_dunning_row(
px_rowid => l_rowid
, px_dunning_trx_id => x_dunning_trx_id
, p_dunning_id => p_dunning_id
--, p_cust_trx_id => l_customer_trx_id
, p_payment_schedule_id => l_payment_schedule_id
, p_ag_dn_xref_id => p_ag_dn_xref_id
, p_stage_number => null
, p_created_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_last_update_date => sysdate
, p_last_update_login => FND_GLOBAL.USER_ID
, p_object_version_number => 1.0
);
--reset the x_dunning_trx_id, so that will get new no when inserting 2nd record.
x_dunning_trx_id := null;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - End insert unapplied receipts');
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Start insert On Account Credit memos');
vPLSQL2 := 'select arp.customer_trx_id customer_trx_id, ' ||
' arp.payment_schedule_id ' ||
' from ar_payment_schedules arp, ' ||
' hz_cust_accounts hca ' ||
' where arp.customer_id = hca.cust_account_id ' ||
' and hca.party_id = :p_party_id ' ||
' and arp.amount_due_remaining <> 0 ' ||
' and arp.class =''CM'' ' ||
' and arp.status=''OP'' ';
vPLSQL2 := 'select arp.customer_trx_id customer_trx_id, ' ||
' arp.payment_schedule_id ' ||
' from ar_payment_schedules arp ' ||
' where arp.customer_id = :p_cust_acct_id ' ||
' and arp.amount_due_remaining <> 0 ' ||
' and arp.class =''CM'' ' ||
' and arp.status=''OP'' ';
vPLSQL2 := 'select arp.customer_trx_id customer_trx_id, ' ||
' arp.payment_schedule_id ' ||
' from ar_payment_schedules arp ' ||
' where arp.customer_site_use_id = :p_site_use_id ' ||
' and arp.amount_due_remaining <> 0 ' ||
' and arp.class =''CM'' ' ||
' and arp.status=''OP'' ';
IEX_Dunnings_PKG.insert_staged_dunning_row(
px_rowid => l_rowid
, px_dunning_trx_id => x_dunning_trx_id
, p_dunning_id => p_dunning_id
, p_cust_trx_id => l_customer_trx_id
, p_payment_schedule_id => l_payment_schedule_id
, p_ag_dn_xref_id => p_ag_dn_xref_id
, p_stage_number => null
, p_created_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_last_update_date => sysdate
, p_last_update_login => FND_GLOBAL.USER_ID
, p_object_version_number => 1.0
);
--reset the x_dunning_trx_id, so that will get new no when inserting 2nd record.
x_dunning_trx_id := null;
WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - End insert On Account Credit memos');
ROLLBACK TO INSERT_DUNNING_TRANSACTION_PVT;
ROLLBACK TO INSERT_DUNNING_TRANSACTION_PVT;
ROLLBACK TO INSERT_DUNNING_TRANSACTION_PVT;
END INSERT_DUNNING_TRANSACTION;