[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
from ast_locations_v where party_site_id =
(select party_site_id
from hz_cust_acct_sites_all where cust_acct_site_id =
(select address_id
from ar_contacts_v where contact_party_id = P_CONTACT_ID));
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;
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_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
);
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
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
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
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_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
);
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_previous_request_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_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.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 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');
* 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
);
* 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 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 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;
* 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');
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');
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,
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.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 ;
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';
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.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 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;
* 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');
* 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
CURSOR C_GET_PARTY_DAYS (in_party_id number) IS
SELECT max(sysdate - 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(sysdate - 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(sysdate - 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 (sysdate - 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 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 SYSDATE + ';
vstr4 := 'SELECT SYSDATE + ' ;
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, 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;
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,
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
FROM IEX_DELINQUENCIES d,
HZ_PARTIES h,
HZ_CUST_ACCOUNTS a
WHERE d.customer_site_use_id = in_SITE_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.');
* 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 del.party_cust_id
from iex_delinquencies del
where del.cust_account_id = in_id;