The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- filter passed promises; we need only BROKEN_PROMISE promises for update
l_SQL := 'SELECT state ' ||
'FROM IEX_PROMISE_DETAILS ' ||
'WHERE ' ||
'PROMISE_DETAIL_ID = :P_PROMISE_ID';
update iex_promise_details
set UWQ_STATUS = P_STATUS,
UWQ_ACTIVE_DATE = l_uwq_active_date,
UWQ_COMPLETE_DATE = l_uwq_complete_date,
last_update_date = sysdate,
last_updated_by = G_USER_ID
where
promise_detail_id = l_broken_promises(i);
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': nothing to update');
SELECT delinquency_id, cust_account_id, CNSLD_INVOICE_ID, CONTRACT_ID
FROM IEX_PROMISE_DETAILS
WHERE PROMISE_DETAIL_ID = P_PROMISE_ID;*/
SELECT del.party_cust_id,del.cust_account_id,del.CUSTOMER_SITE_USE_ID,
del.delinquency_id, prd.CNSLD_INVOICE_ID, prd.CONTRACT_ID
FROM IEX_PROMISE_DETAILS prd,
IEX_DELINQUENCIES_ALL del
WHERE prd.PROMISE_DETAIL_ID = P_PROMISE_ID
and prd.delinquency_id = del.delinquency_id;
select strategy_level
from iex_strategies
where party_id= p_party_id
and status_code in ('OPEN', 'ONHOLD');
/*l_SQL := 'SELECT delinquency_id, cust_account_id, CNSLD_INVOICE_ID, CONTRACT_ID ' ||
'FROM IEX_PROMISE_DETAILS ' ||
'WHERE ' ||
'PROMISE_DETAIL_ID = :P_PROMISE_ID';
select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 40)
into l_DefaultStrategyLevel
from iex_app_preferences_b
where preference_name = 'COLLECTIONS STRATEGY LEVEL'
and enabled_flag = 'Y'
and org_id is null; -- changed for bug 8708271 pnaveenk
select IEX_PTP_WF_S.NEXTVAL from dual;
PROCEDURE VALIDATE_INSERT_INPUT(P_PROMISE_REC IN IEX_PROMISES_PUB.PRO_INSRT_REC_TYPE)
IS
Type refCur is Ref Cursor;
l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_INSERT_INPUT';
l_SQL := 'SELECT ''X'' ' ||
'FROM IEX_LOOKUPS_V ' ||
'WHERE ' ||
'LOOKUP_TYPE = ''IEX_PAYMENT_TYPES'' AND LOOKUP_CODE = :P_PAYMENT_METHOD AND ' ||
'ENABLED_FLAG = ''Y''';
l_SQL := 'SELECT COUNT(1) ' ||
'FROM IEX_PROMISE_DETAILS ' ||
'WHERE ' ||
'DELINQUENCY_ID IS NULL AND CNSLD_INVOICE_ID IS NULL AND CONTRACT_ID IS NULL AND ' ||
'CUST_ACCOUNT_ID IS NOT NULL AND CUST_ACCOUNT_ID = :P_CUST_ACCOUNT_ID AND ' ||
'PROMISE_DATE = :P_PROMISE_DATE AND ' ||
'STATUS in (''COLLECTABLE'', ''PENDING'')';
l_SQL := 'SELECT COUNT(1) ' ||
'FROM IEX_PROMISE_DETAILS ' ||
'WHERE ' ||
'CNSLD_INVOICE_ID IS NULL AND CONTRACT_ID IS NULL AND ' ||
'CUST_ACCOUNT_ID IS NOT NULL AND CUST_ACCOUNT_ID = :P_CUST_ACCOUNT_ID AND ' ||
'DELINQUENCY_ID IS NOT NULL AND DELINQUENCY_ID = :P_DELINQUENCY_ID AND ' ||
'PROMISE_DATE = :P_PROMISE_DATE AND ' ||
'STATUS in (''COLLECTABLE'', ''PENDING'')';
l_SQL := 'SELECT PSA.INVOICE_CURRENCY_CODE ' ||
'FROM AR_PAYMENT_SCHEDULES PSA, IEX_DELINQUENCIES DEL ' ||
'WHERE ' ||
'DEL.DELINQUENCY_ID = :P_DELINQUENCY_ID AND ' ||
'DEL.PAYMENT_SCHEDULE_ID = PSA.PAYMENT_SCHEDULE_ID';
l_SQL := 'SELECT COUNT(1) ' ||
'FROM IEX_PROMISE_DETAILS ' ||
'WHERE ' ||
'DELINQUENCY_ID IS NULL AND CONTRACT_ID IS NULL AND ' ||
'CUST_ACCOUNT_ID IS NOT NULL AND CUST_ACCOUNT_ID = :P_CUST_ACCOUNT_ID AND ' ||
'CNSLD_INVOICE_ID IS NOT NULL AND CNSLD_INVOICE_ID = :P_CNSLD_INVOICE_ID AND ' ||
'PROMISE_DATE = :P_PROMISE_DATE AND ' ||
'STATUS in (''COLLECTABLE'', ''PENDING'')';
l_SQL := 'SELECT CNSLD.CURRENCY_CODE ' ||
'FROM IEX_BPD_CNSLD_INV_REMAINING_V CNSLD ' ||
'WHERE ' ||
'CNSLD.CONSOLIDATED_INVOICE_ID = :P_CNSLD_ID';
l_SQL := 'SELECT COUNT(1) ' ||
'FROM IEX_PROMISE_DETAILS ' ||
'WHERE ' ||
'DELINQUENCY_ID IS NULL AND CNSLD_INVOICE_ID IS NULL AND ' ||
'CUST_ACCOUNT_ID IS NOT NULL AND CUST_ACCOUNT_ID = :P_CUST_ACCOUNT_ID AND ' ||
'CONTRACT_ID IS NOT NULL AND CONTRACT_ID = :P_CONTRACT_ID AND ' ||
'PROMISE_DATE = :P_PROMISE_DATE AND ' ||
'STATUS in (''COLLECTABLE'', ''PENDING'')';
l_SQL := 'SELECT cntr.CURRENCY_CODE ' ||
'FROM iex_pay_okl_contracts_v cntr ' ||
'WHERE ' ||
'cntr.CONTRACT_ID = :P_CONTRACT_ID'; */
l_SQL := 'SELECT CURRENCY_CODE ' ||
'FROM OKC_K_HEADERS_B ' ||
'WHERE ' ||
'ID = :P_CONTRACT_ID';
PROCEDURE INSERT_PROMISE(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
P_COMMIT IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
P_VALIDATION_LEVEL IN NUMBER, -- DEFAULT FND_API.G_VALID_LEVEL_FULL,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2,
P_PROMISE_REC IN IEX_PROMISES_PUB.PRO_INSRT_REC_TYPE,
X_PRORESP_REC OUT NOCOPY IEX_PROMISES_PUB.PRO_RESP_REC_TYPE)
IS
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_PROMISE';
select IEX_PROMISE_DETAILS_S.NEXTVAL from dual;
select org_id
from iex_delinquencies_all
where delinquency_id = p_del_id;
SAVEPOINT INSERT_PROMISE_PVT;
VALIDATE_INSERT_INPUT(P_PROMISE_REC);
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Doing insert...');
INSERT INTO IEX_PROMISE_DETAILS
(
PROMISE_DETAIL_ID,
OBJECT_VERSION_NUMBER,
PROGRAM_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
PROMISE_DATE,
PROMISE_AMOUNT,
PROMISE_PAYMENT_METHOD,
STATUS,
ACCOUNT,
PROMISE_ITEM_NUMBER,
CURRENCY_CODE,
CAMPAIGN_SCHED_ID,
DELINQUENCY_ID,
RESOURCE_ID,
PROMISE_MADE_BY,
CUST_ACCOUNT_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CNSLD_INVOICE_ID,
CONTRACT_ID,
BROKEN_ON_DATE,
AMOUNT_DUE_REMAINING,
STATE,
ORG_ID
)
VALUES
(
l_promise_id,
1.0,
G_APP_ID,
sysdate,
G_USER_ID,
G_LOGIN_ID,
sysdate,
G_USER_ID,
P_PROMISE_REC.PROMISE_DATE,
P_PROMISE_REC.PROMISE_AMOUNT,
P_PROMISE_REC.PROMISE_PAYMENT_METHOD,
l_promise_status,
P_PROMISE_REC.ACCOUNT,
P_PROMISE_REC.PROMISE_ITEM_NUMBER,
P_PROMISE_REC.CURRENCY_CODE,
P_PROMISE_REC.CAMPAIGN_SCHED_ID,
P_PROMISE_REC.DELINQUENCY_ID,
P_PROMISE_REC.TAKEN_BY_RESOURCE_ID,
P_PROMISE_REC.PROMISED_BY_PARTY_PER_ID,
P_PROMISE_REC.CUST_ACCOUNT_ID,
P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE_CATEGORY,
P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE1,
P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE2,
P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE3,
P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE4,
P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE5,
P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE6,
P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE7,
P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE8,
P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE9,
P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE10,
P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE11,
P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE12,
P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE13,
P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE14,
P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE15,
P_PROMISE_REC.CNSLD_INVOICE_ID,
P_PROMISE_REC.CONTRACT_ID,
l_broken_on_date,
P_PROMISE_REC.PROMISE_AMOUNT,
l_promise_state,
l_org_id
);
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Insert OK');
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling update_del_stage_level');
update_del_stage_level (
p_promise_id => l_promise_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to update_del_stage_level');
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': update_del_stage_level failed');
-- inserting a note
if P_PROMISE_REC.NOTE is not null then
IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Going to build context for note...');
ROLLBACK TO INSERT_PROMISE_PVT;
ROLLBACK TO INSERT_PROMISE_PVT;
ROLLBACK TO INSERT_PROMISE_PVT;
PROCEDURE VALIDATE_UPDATE_INPUT(P_PROMISE_REC IN IEX_PROMISES_PUB.PRO_UPDT_REC_TYPE)
IS
Type refCur is Ref Cursor;
l_procedure varchar2(50); -- := 'VALIDATE_UPDATE_INPUT';
l_str_select varchar2(1000); -- := 'SELECT COUNT(1) ' ||
l_procedure := 'VALIDATE_UPDATE_INPUT';
l_str_select := 'SELECT COUNT(1) ' ||
'FROM IEX_PROMISE_DETAILS ' ||
'WHERE ';
l_SQL := 'SELECT CUST_ACCOUNT_ID, DELINQUENCY_ID, CNSLD_INVOICE_ID, CONTRACT_ID, STATUS, STATE, PROMISE_AMOUNT, AMOUNT_DUE_REMAINING ' ||
'FROM IEX_PROMISE_DETAILS ' ||
'WHERE ' ||
'PROMISE_DETAIL_ID = :P_PROMISE_ID';
l_SQL := l_str_select || l_where_clause || l_str_cond;
l_SQL := 'SELECT COUNT(1) ' ||
'FROM IEX_PROMISE_DETAILS ' ||
'WHERE ' || l_where_clause || ' AND ' ||
'promise_detail_id <> :P_PROMISE_ID AND ' ||
'PROMISE_DATE = :P_PROMISE_DATE AND ' ||
'STATUS in (''COLLECTABLE'', ''PENDING'')';
l_SQL := 'SELECT ''X'' ' ||
'FROM IEX_LOOKUPS_V ' ||
'WHERE ' ||
'LOOKUP_TYPE = ''IEX_PAYMENT_TYPES'' AND LOOKUP_CODE = :P_PAYMENT_METHOD AND ' ||
'ENABLED_FLAG = ''Y''';
PROCEDURE UPDATE_PROMISE(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
P_COMMIT IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
P_VALIDATION_LEVEL IN NUMBER, -- DEFAULT FND_API.G_VALID_LEVEL_FULL,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2,
P_PROMISE_REC IN IEX_PROMISES_PUB.PRO_UPDT_REC_TYPE,
X_PRORESP_REC OUT NOCOPY IEX_PROMISES_PUB.PRO_RESP_REC_TYPE)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PROMISE';
SAVEPOINT UPDATE_PROMISE_PVT;
VALIDATE_UPDATE_INPUT(P_PROMISE_REC);
/* do update */
IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Doing update...');
UPDATE iex_promise_details
SET PROMISE_AMOUNT = P_PROMISE_REC.PROMISE_AMOUNT,
AMOUNT_DUE_REMAINING = P_PROMISE_REC.PROMISE_AMOUNT,
PROMISE_DATE = P_PROMISE_REC.PROMISE_DATE,
BROKEN_ON_DATE = l_broken_on_date,
STATUS = l_promise_status,
PROMISE_PAYMENT_METHOD = P_PROMISE_REC.PROMISE_PAYMENT_METHOD,
ACCOUNT = P_PROMISE_REC.ACCOUNT,
PROMISE_ITEM_NUMBER = P_PROMISE_REC.PROMISE_ITEM_NUMBER,
CAMPAIGN_SCHED_ID = P_PROMISE_REC.CAMPAIGN_SCHED_ID,
ATTRIBUTE_CATEGORY = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE1,
ATTRIBUTE2 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE2,
ATTRIBUTE3 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE3,
ATTRIBUTE4 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE4,
ATTRIBUTE5 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE5,
ATTRIBUTE6 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE6,
ATTRIBUTE7 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE7,
ATTRIBUTE8 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE8,
ATTRIBUTE9 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE9,
ATTRIBUTE10 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE10,
ATTRIBUTE11 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE11,
ATTRIBUTE12 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE12,
ATTRIBUTE13 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE13,
ATTRIBUTE14 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE14,
ATTRIBUTE15 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE15,
RESOURCE_ID = P_PROMISE_REC.TAKEN_BY_RESOURCE_ID,
PROMISE_MADE_BY = P_PROMISE_REC.PROMISED_BY_PARTY_PER_ID,
PROGRAM_ID = G_APP_ID,
last_update_date = sysdate,
last_updated_by = G_USER_ID,
LAST_UPDATE_LOGIN = G_LOGIN_ID
where promise_detail_id = P_PROMISE_REC.PROMISE_ID;
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Update OK');
l_SQL := 'SELECT state FROM IEX_PROMISE_DETAILS WHERE PROMISE_DETAIL_ID = :P_PROMISE_ID';
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling update_del_stage_level');
update_del_stage_level (
p_promise_id => P_PROMISE_REC.PROMISE_ID,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to update_del_stage_level');
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': update_del_stage_level failed');
-- inserting a note
if P_PROMISE_REC.NOTE is not null then
IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Going to build context for note...');
l_SQL := 'SELECT CUST_ACCOUNT_ID ' ||
'FROM IEX_PROMISE_DETAILS ' ||
'WHERE ' ||
'PROMISE_DETAIL_ID = :P_PROMISE_ID';
-- commit if promise updated successfully
IF FND_API.To_Boolean( p_commit ) THEN
COMMIT WORK;
ROLLBACK TO UPDATE_PROMISE_PVT;
ROLLBACK TO UPDATE_PROMISE_PVT;
ROLLBACK TO UPDATE_PROMISE_PVT;
/* do update */
IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Doing update...');
UPDATE iex_promise_details
SET STATUS = l_promise_status,
RESOURCE_ID = P_PROMISE_REC.TAKEN_BY_RESOURCE_ID,
PROMISE_MADE_BY = P_PROMISE_REC.PROMISED_BY_PARTY_PER_ID,
PROGRAM_ID = G_APP_ID,
last_update_date = sysdate,
last_updated_by = G_USER_ID,
LAST_UPDATE_LOGIN = G_LOGIN_ID,
ATTRIBUTE_CATEGORY = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE_CATEGORY, -- start added by snuthala for bug 10373547 12/16/2010
ATTRIBUTE1 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE1,
ATTRIBUTE2 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE2,
ATTRIBUTE3 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE3,
ATTRIBUTE4 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE4,
ATTRIBUTE5 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE5,
ATTRIBUTE6 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE6,
ATTRIBUTE7 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE7,
ATTRIBUTE8 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE8,
ATTRIBUTE9 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE9,
ATTRIBUTE10 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE10,
ATTRIBUTE11 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE11,
ATTRIBUTE12 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE12,
ATTRIBUTE13 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE13,
ATTRIBUTE14 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE14,
ATTRIBUTE15 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE15 -- end added by snuthala for bug 10373547 12/16/2010
where promise_detail_id = P_PROMISE_REC.PROMISE_ID;
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Update OK');
l_SQL := 'SELECT state FROM IEX_PROMISE_DETAILS WHERE PROMISE_DETAIL_ID = :P_PROMISE_ID';
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling update_del_stage_level');
update_del_stage_level (
p_promise_id => P_PROMISE_REC.PROMISE_ID,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to update_del_stage_level');
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': update_del_stage_level failed');
-- inserting a note
if P_PROMISE_REC.NOTE is not null then
IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Going to build context for note...');
l_SQL := 'SELECT CUST_ACCOUNT_ID ' ||
'FROM IEX_PROMISE_DETAILS ' ||
'WHERE ' ||
'PROMISE_DETAIL_ID = :P_PROMISE_ID';
-- commit if promise updated successfully
IF FND_API.To_Boolean( p_commit ) THEN
COMMIT WORK;
l_SQL := 'SELECT count(distinct CUST_ACCOUNT_ID) from iex_delinquencies where delinquency_id in (';
l_SQL := 'SELECT ''X'' ' ||
'FROM IEX_LOOKUPS_V ' ||
'WHERE ' ||
'LOOKUP_TYPE = ''IEX_PAYMENT_TYPES'' AND LOOKUP_CODE = :P_PAYMENT_METHOD AND ' ||
'ENABLED_FLAG = ''Y''';
select IEX_PROMISE_DETAILS_S.NEXTVAL from dual;
select org_id
from iex_delinquencies_all
where delinquency_id = p_del_id;
l_SQL := 'SELECT psa.CUSTOMER_ID, psa.CUSTOMER_SITE_USE_ID, del.payment_schedule_id ' ||
' FROM iex_delinquencies del, ar_payment_schedules psa ' ||
' WHERE psa.payment_schedule_id = del.payment_schedule_id and psa.status = ''OP'' and ' ||
' psa.AMOUNT_DUE_REMAINING > 0 and del.DELINQUENCY_ID = :P_DEL_ID';
l_SQL := 'SELECT PROMISE_DETAIL_ID, PROMISE_AMOUNT, CURRENCY_CODE, STATUS, STATE, AMOUNT_DUE_REMAINING ' ||
'FROM IEX_PROMISE_DETAILS ' ||
'WHERE DELINQUENCY_ID = :P_DEL_ID AND STATUS in (''COLLECTABLE'', ''PENDING'') ' ||
'UNION ' ||
'SELECT PROMISE_DETAIL_ID, PROMISE_AMOUNT, CURRENCY_CODE, STATUS, STATE, AMOUNT_DUE_REMAINING ' ||
'FROM IEX_PROMISE_DETAILS ' ||
'WHERE CUST_ACCOUNT_ID = :P_CUST_ACCOUNT_ID AND ' ||
'DELINQUENCY_ID IS NULL AND CNSLD_INVOICE_ID IS NULL AND CONTRACT_ID IS NULL AND ' ||
'TRUNC(promise_date) = TRUNC(:P_PROMISE_DATE) AND ' ||
'STATUS in (''COLLECTABLE'', ''PENDING'')';
UPDATE iex_promise_details
SET STATUS = 'CANCELLED',
RESOURCE_ID = P_MASS_PROMISE_REC.TAKEN_BY_RESOURCE_ID,
PROMISE_MADE_BY = P_MASS_PROMISE_REC.PROMISED_BY_PARTY_PER_ID,
PROGRAM_ID = G_APP_ID,
last_update_date = sysdate,
last_updated_by = G_USER_ID,
LAST_UPDATE_LOGIN = G_LOGIN_ID
where
PROMISE_DETAIL_ID = l_promise_id;
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling update_del_stage_level');
update_del_stage_level (
p_promise_id => l_promise_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to update_del_stage_level');
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': update_del_stage_level failed');
-- fixed a perf bug 4930381 l_SQL := 'SELECT CUSTOMER_ID, AMOUNT_DUE_REMAINING, INVOICE_CURRENCY_CODE, CUSTOMER_SITE_USE_ID FROM iex_pay_invoices_v WHERE DELINQUENCY_ID = :P_DEL_ID';
l_SQL := 'SELECT psa.CUSTOMER_ID, psa.AMOUNT_DUE_REMAINING, psa.INVOICE_CURRENCY_CODE, psa.CUSTOMER_SITE_USE_ID '||
' FROM iex_delinquencies del, ar_payment_schedules psa ' ||
' WHERE psa.payment_schedule_id = del.payment_schedule_id and psa.status = ''OP'' and ' ||
' psa.AMOUNT_DUE_REMAINING > 0 and del.DELINQUENCY_ID = :P_DEL_ID';
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Inserting new promise...');
INSERT INTO IEX_PROMISE_DETAILS
(
PROMISE_DETAIL_ID,
OBJECT_VERSION_NUMBER,
PROGRAM_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
PROMISE_DATE,
PROMISE_AMOUNT,
PROMISE_PAYMENT_METHOD,
STATUS,
ACCOUNT,
PROMISE_ITEM_NUMBER,
CURRENCY_CODE,
CAMPAIGN_SCHED_ID,
DELINQUENCY_ID,
RESOURCE_ID,
PROMISE_MADE_BY,
CUST_ACCOUNT_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CNSLD_INVOICE_ID,
CONTRACT_ID,
BROKEN_ON_DATE,
AMOUNT_DUE_REMAINING,
STATE,
ORG_ID
)
VALUES
(
l_promise_id,
1.0,
G_APP_ID,
sysdate,
G_USER_ID,
G_LOGIN_ID,
sysdate,
G_USER_ID,
P_MASS_PROMISE_REC.PROMISE_DATE,
l_remaining_amount,
P_MASS_PROMISE_REC.PROMISE_PAYMENT_METHOD,
l_status,
P_MASS_PROMISE_REC.ACCOUNT,
P_MASS_PROMISE_REC.PROMISE_ITEM_NUMBER,
l_currency,
P_MASS_PROMISE_REC.CAMPAIGN_SCHED_ID,
P_MASS_IDS_TBL(i),
P_MASS_PROMISE_REC.TAKEN_BY_RESOURCE_ID,
P_MASS_PROMISE_REC.PROMISED_BY_PARTY_PER_ID,
l_cust_account_id,
P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE_CATEGORY,
P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE1,
P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE2,
P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE3,
P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE4,
P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE5,
P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE6,
P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE7,
P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE8,
P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE9,
P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE10,
P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE11,
P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE12,
P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE13,
P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE14,
P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE15,
null,
null,
l_broken_on_date,
l_remaining_amount,
'PROMISE',
l_org_id
);
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Insert OK');
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling update_del_stage_level');
update_del_stage_level (
p_promise_id => l_promise_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to update_del_stage_level');
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': update_del_stage_level failed');
-- inserting a note
if P_MASS_PROMISE_REC.NOTE is not null and l_note_type is not null then
IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Going to build context for note...');
-- commit if promise updated successfully
IF FND_API.To_Boolean( p_commit ) THEN
COMMIT WORK;
Procedure update_del_stage_level (
p_promise_id IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
is
l_api_name CONSTANT VARCHAR2(30) := 'update_del_stage_level';
SAVEPOINT UPDATE_DEL_STAGE_PVT;
select delinquency_id into l_promised_delinquency_id
from iex_promise_details
where promise_detail_id = p_promise_id;
SELECT sum(promise_amount) into l_total_already_pro_amt
from iex_promise_details where delinquency_id = l_promised_delinquency_id
and status = 'COLLECTABLE'
and state = 'PROMISE';
SELECT amount_due_remaining into l_amt_due_remaining
from ar_payment_schedules pay,
iex_delinquencies del
where
del.payment_schedule_id = pay.payment_schedule_id
and del.delinquency_id = l_promised_delinquency_id;
update iex_delinquencies_all
set staged_dunning_level = 0
where delinquency_id = l_promised_delinquency_id;
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || 'Updated the staged dunning level to 0 for delinquency id: ' || l_promised_delinquency_id);
update iex_delinquencies_all
set staged_dunning_level = l_stage_number
where delinquency_id = l_promised_delinquency_id
and staged_dunning_level=0; --added for bug 14318148
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || 'Updated the staged dunning level to ' || l_stage_number || ' for delinquency id: ' || l_promised_delinquency_id);
ROLLBACK TO UPDATE_DEL_STAGE_PVT;
ROLLBACK TO UPDATE_DEL_STAGE_PVT;
ROLLBACK TO UPDATE_DEL_STAGE_PVT;
end update_del_stage_level;