[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select request_id
from AR_CONC_PROCESS_REQUESTS
where CONCURRENT_PROGRAM_NAME in ('ARSUMREF','IEX_POPULATE_UWQ_SUM');
3. Update IEX_DELIQUENCIES_ALL table
*/
PROCEDURE Close_Delinquencies(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_payments_tbl IN IEX_PAYMENTS_BATCH_PUB.CL_INV_TBL_TYPE,
p_security_check IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_return_status VARCHAR2(1);
select delinquency_id into l_del_id
from iex_delinquencies
where payment_schedule_id = p_payments_tbl(i);
IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'before update');
UPDATE IEX_DELINQUENCIES_ALL
SET STATUS='CLOSE',
DUNN_YN='N',
LAST_UPDATE_DATE=sysdate
WHERE DELINQUENCY_ID = l_del_tbl(j);
IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'after update');
*REQUEST_ID -- request id of the concurrent program which created/update the
--delinquencies
*NOOFDELCREATED -number of delinquencies created
*NOOFDELUPDATED -number of delinquencies updated
**/
PROCEDURE RAISE_EVENT(
P_REQUEST_ID IN NUMBER,
p_del_create_count IN NUMBER,
p_del_update_count IN NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2) IS
l_parameter_list wf_parameter_list_t;
l_del_update_count NUMBER;
l_del_update_count := p_del_update_count ;
select iex_del_wf_s.nextval INTO l_seq from dual;
||'No of Del Updated =' ||l_del_update_count
);
wf_event.AddParameterToList('NOOFDELUPDATED',
to_char(l_del_update_count),
l_parameter_list);
l_parameter_list.DELETE;
l_del_insert_count Number := 0 ;
l_del_update_count Number := 0 ;
SELECT
HZCA.cust_Account_id,
ARPS.customer_trx_id,
IDB.score_object_id,
IDS.del_status
FROM HZ_CUST_ACCOUNTS HZCA,
IEX_DEL_BUFFERS IDB,
AR_PAYMENT_SCHEDULES ARPS,
IEX_DEL_STATUSES IDS
WHERE
NOT EXISTS
(Select 1
from iex_delinquencies
where payment_schedule_id = idb.score_object_id)
AND NOT EXISTS
(select 1
from dual
where IDS.del_status = vf_current)
AND HZCA.cust_account_id = ARPS.customer_id
AND ARPS.payment_schedule_id = IDB.score_object_id
AND IDB.score_value between
IDS.score_value_low and IDS.score_value_high
AND IDB.score_id = IDS.score_id
AND IDB.request_id = p_request_id
ORDER By IDB.score_object_id;
SELECT
id.delinquency_id,
ids.del_status buf_status,
id.status del_status,
id.payment_schedule_id
FROM iex_delinquencies id,
iex_del_buffers idb,
iex_del_statuses ids
where NOT EXISTS
(select 1
from dual
where id.status = ids.del_status)
and idb.score_id = ids.score_id
and idb.score_value between ids.score_value_low
and ids.score_value_high
and idb.score_object_id = id.payment_schedule_id
and idb.request_id = p_request_id;
SELECT DISTINCT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS,
AR_SYSTEM_PARAMETERS ARP
WHERE ARS.REFERENCE_1 IS Null
AND ARS.ORG_ID = ARP.ORG_ID
AND EXISTS (SELECT 1 FROM IEX_DELINQUENCIES_ALL IED WHERE
IED.STATUS IN ('DELINQUENT', 'PREDELINQUENT')
AND ARS.CUST_ACCOUNT_ID = IED.CUST_ACCOUNT_ID
AND ARS.ORG_ID = IED.ORG_ID);
SELECT DISTINCT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS,
AR_SYSTEM_PARAMETERS ARP
WHERE ARS.REFERENCE_1 = 1
AND ARS.ORG_ID = ARP.ORG_ID
AND NOT EXISTS (SELECT 1 FROM IEX_DELINQUENCIES_ALL IED WHERE
IED.STATUS IN ('DELINQUENT', 'PREDELINQUENT')
AND ARS.CUST_ACCOUNT_ID = IED.CUST_ACCOUNT_ID
AND ARS.ORG_ID = IED.ORG_ID);
SELECT score_object_code,
score_id
INTO v_object,
v_score
FROM iex_del_buffers
WHERE request_id = p_request_id
AND rownum = 1 ;
Select --fnd_profile.value('ORG_ID'), --Commneted for MOAC
fnd_profile.value('USER_ID'),
fnd_profile.value('IEX_DEBUG_LEVEL'),
sysdate
into --v_org_id, --Commneted for MOAC
v_user_id,
v_debug_level,
v_today
From dual ;
Select count(1)
into v_score_range
from iex_del_statuses
where score_id = v_score ;
SELECT
id.delinquency_id,
ids.del_status buf_status,
id.status del_status,
id.payment_schedule_id
BULK COLLECT INTO
vt_del_id,
vt_buf_status,
vt_del_status,
vt_pmt_schd_id
FROM iex_delinquencies id,
iex_del_buffers idb,
iex_del_statuses ids
where NOT EXISTS
(select 1
from dual
-- Begin - Andre Araujo - 12/21/2004 - Remove the pre-del 2 del constraint bug#4072687
--where (id.status = vf_delinquent
-- and ids.del_status = vf_predelinquent)
-- OR id.status = ids.del_status)
where id.status = ids.del_status)
-- End - Andre Araujo - 12/21/2004 - Remove the pre-del 2 del constraint bug#4072687
and idb.score_id = ids.score_id
and idb.score_value between ids.score_value_low
and ids.score_value_high
and idb.score_object_id = id.payment_schedule_id
and idb.request_id = p_request_id;
IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Row Count after Update Select ');
ERRBUF := ' FIRST SELECT - Error Code = ' || SQLCODE ||
' Error Msg ' || SQLERRM ;
UPDATE IEX_DELINQUENCIES
SET status = vt_buf_status(v_count),
last_update_date = v_today,
last_updated_by = v_user_id,
dunn_yn = decode(vt_buf_status(v_count), vf_current, 'N'),
object_version_number = object_version_number + 1,
request_id = p_request_id
WHERE delinquency_id = vt_del_id(v_count);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number Of Delinquencies Updated..>> '|| vt_del_id.count) ;
l_del_update_count := vt_del_id.count ;
IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Rows Updated for ' ||
to_char(vt_del_id(i)) || ' is ' ||
to_char(SQL%BULK_ROWCOUNT(i)));
ERRBUF := 'INVOICE - Matching Delinquencies Update --> Error Code '
|| SQLCODE || ' Error Mesg ' || SQLERRM ;
IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleted Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '--------- Insert Candidate Rows ----------');
INSERT INTO IEX_DELINQUENCIES_ALL
( DELINQUENCY_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
OBJECT_VERSION_NUMBER ,
DUNN_YN ,
PARTY_CUST_ID ,
CUST_ACCOUNT_ID ,
CUSTOMER_SITE_USE_ID , -- added by clchang for bill_to
TRANSACTION_ID ,
PAYMENT_SCHEDULE_ID ,
STATUS ,
ORG_ID ,
SOURCE_PROGRAM_NAME ,
SCORE_ID ,
SCORE_VALUE ,
REQUEST_ID )
SELECT
IEX_DELINQUENCIES_S.NEXTVAL ,
v_today,
v_user_id,
v_today,
v_user_id,
1 ,
'Y' ,
HZCA.party_id ,
HZCA.cust_Account_id ,
ARPS.customer_site_use_id , -- added by clchang for bill_to
ARPS.customer_trx_id ,
IDB.score_object_id ,
IDS.del_status ,
-- v_org_id ,
--jsanju for bug 3581105
--get payment schedule org ID
ARPS.org_id,
l_source_module ,
IDB.score_id ,
IDB.score_value ,
p_Request_id
FROM HZ_CUST_ACCOUNTS HZCA ,
IEX_DEL_BUFFERS IDB ,
AR_PAYMENT_SCHEDULES ARPS ,
IEX_DEL_STATUSES IDS
WHERE
NOT EXISTS
(Select 1
from iex_delinquencies_all --added by barathsr for bug#7366451 10-Oct-08
--iex_delinquencies
where payment_schedule_id = idb.score_object_id)
-- start bug 9794774 sunagesh 11 jul 2011
/* AND NOT EXISTS
(select 1
from dual
where IDS.del_status = vf_current) */
AND IDS.del_status <> vf_current
-- end bug 9794774 sunagesh 11 jul 2011
AND HZCA.cust_account_id = ARPS.customer_id
AND ARPS.payment_schedule_id = IDB.score_object_id
AND IDB.score_value between
IDS.score_value_low and IDS.score_value_high
AND IDB.score_id = IDS.score_id
AND IDB.request_id = p_request_id ;
l_del_insert_count := SQL%ROWCOUNT ;
('MANAGE_DELINQUENCIES: ' || 'Number of Rows Inserted --> ' || to_char(SQL%ROWCOUNT));
ERRBUF := 'INSERT - Error Code = ' || SQLCODE || ' Error Msg ' || SQLERRM ;
SELECT ico.object_id, ids.del_status, ico.delinquency_status
BULK COLLECT INTO vt_contract_id, vt_buf_status, vt_del_status
FROM iex_case_objects ico,
iex_del_buffers idb,
iex_del_statuses ids
WHERE idb.score_id = ids.score_id
AND idb.score_value BETWEEN ids.score_value_low and ids.score_value_high
AND idb.score_object_id = ico.object_id
AND ico.object_code = 'CONTRACTS'
AND idb.request_id = p_request_id;
('MANAGE_DELINQUENCIES: ' || 'CONTRACT - Row Count after Update Select ' || to_char(vt_contract_id.COUNT));
ERRBUF := 'CONTRACT - Matching Delinquencies Select -->' || SQLCODE || ' Error Msg ' || SQLERRM ;
UPDATE IEX_CASE_OBJECTS
SET delinquency_status = vt_buf_status(v_count),
last_update_date = v_today,
last_updated_by = v_user_id,
object_version_number = object_version_number + 1,
request_id = p_request_id
WHERE object_id = vt_contract_id(v_count);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number Of Contracts Updated..>> '|| vt_contract_id.count) ;
l_del_update_count := vt_del_id.count ;
IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Rows Updated for ' || to_char(vt_del_id(i)) || ' is ' ||
to_char(SQL%BULK_ROWCOUNT(i)));
ERRBUF := 'CONTRACT Updating... - Matching Delinquencies Update --> ' || SQLCODE || ' Error Msg ' || SQLERRM ;
SELECT
id.delinquency_id,
ids.del_status buf_status,
id.status del_status,
id.case_id
BULK COLLECT INTO
vt_del_id,
vt_buf_status,
vt_del_status,
vt_case_id
FROM iex_delinquencies id,
iex_del_buffers idb,
iex_del_statuses ids
where
NOT EXISTS
(select 1
from dual
where (id.status = vf_delinquent and ids.del_status = vf_predelinquent)
OR id.status = ids.del_status)
and idb.score_id = ids.score_id
and idb.score_value between ids.score_value_low
and ids.score_value_high
and idb.score_object_id = id.case_id
and idb.request_id = p_request_id ;
SELECT ic.cas_id, ids.del_status, ic.status_code
BULK COLLECT INTO vt_case_id, vt_buf_status, vt_del_status
FROM iex_cases_all_b ic,
iex_del_buffers idb,
iex_del_statuses ids
WHERE idb.score_id = ids.score_id
AND idb.score_value BETWEEN ids.score_value_low and ids.score_value_high
AND idb.score_object_id = ic.cas_id
AND idb.request_id = p_request_id;
('MANAGE_DELINQUENCIES: ' || 'CASE - Row Count after Update Select ' || to_char(vt_case_id.COUNT));
ERRBUF := 'CASE - Matching Delinquencies Select -->' || SQLCODE || ' Error Msg ' || SQLERRM ;
UPDATE IEX_DELINQUENCIES
SET status = vt_buf_status(v_count),
last_update_date = v_today,
last_updated_by = v_user_id,
dunn_yn = decode(vt_buf_status(v_count), vf_current, 'N'),
object_version_number = object_version_number + 1,
request_id = p_request_id
WHERE delinquency_id = vt_del_id(v_count);
UPDATE IEX_CASES_ALL_B
SET status_code = vt_buf_status(v_count),
last_update_date = v_today,
last_updated_by = v_user_id,
object_version_number = object_version_number + 1,
request_id = p_request_id
WHERE cas_id = vt_case_id(v_count);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number Of Cases Updated..>> '|| vt_case_id.count) ; -- 6785378
l_del_update_count := vt_del_id.count ;
IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Rows Updated for ' ||
to_char(vt_del_id(i)) || ' is ' ||
to_char(SQL%BULK_ROWCOUNT(i)));
ERRBUF := 'CASE - Matching Delinquencies Update --> ' ||
SQLCODE || ' Error Msg ' || SQLERRM ;
INSERT INTO IEX_DELINQUENCIES_ALL
( DELINQUENCY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
OBJECT_VERSION_NUMBER,
DUNN_YN,
PARTY_CUST_ID,
CUST_ACCOUNT_ID,
CUSTOMER_SITE_USE_ID, -- added by clchang for BILL_TO
CASE_ID,
STATUS,
ORG_ID,
SOURCE_PROGRAM_NAME,
SCORE_ID ,
SCORE_VALUE ,
REQUEST_ID )
SELECT
IEX_DELINQUENCIES_S.NEXTVAL,
v_today ,
v_user_id ,
v_today ,
v_user_id ,
1 ,
'Y' ,
ICV.party_id ,
ICD.column_value,
ICD2.column_value,
IDB.score_object_id ,
IDS.del_status ,
--v_org_id ,
ICV.org_id, --Modified for MOAC
l_source_module,
IDB.Score_id,
IDB.score_value ,
p_REQUEST_ID
FROM IEX_DEL_BUFFERS IDB,
IEX_CASES_VL ICV,
IEX_CASE_DEFINITIONS ICD,
IEX_CASES_VL ICV2,
IEX_CASE_DEFINITIONS ICD2,
IEX_DEL_STATUSES IDS
WHERE
NOT EXISTS
(Select 1
from iex_delinquencies
where case_id = idb.score_object_id)
AND NOT EXISTS
(select 1
from dual
where IDS.del_status = vf_current)
AND ICV.cas_id = IDB.score_object_id
AND IDB.score_value between
IDS.score_value_low and IDS.score_value_high
AND IDB.score_id = IDS.score_id
AND IDB.request_id = p_request_id
AND ICV.cas_id = ICD.cas_id
AND ICD.column_name = 'CUSTOMER_ACCOUNT'
AND ICV2.cas_id = ICV.cas_id
AND ICV2.cas_id = ICD2.cas_id
AND ICD2.column_name = 'BILL_TO_ADDRESS_ID';
l_del_insert_count := SQL%ROWCOUNT ;
IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Number of Rows Inserted --> ' || to_char(SQL%ROWCOUNT));
ERRBUF := 'INSERT - Error Code = ' || SQLCODE || ' Error Msg ' || SQLERRM ;
IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'CASE INSERT >> Deleting Buffer Table after RollBack due to Error');
IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleted Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
FND_FILE.PUT_LINE(FND_FILE.LOG,'Starting to update ar_trx_bal_summary....'); --Added by PNAVEENK
IEX_DEBUG_PUB.LOGMESSAGE('Starting to update ar_trx_bal_summary with reference_1 = 1...');
UPDATE AR_TRX_BAL_SUMMARY ARS
SET REFERENCE_1 = 1
WHERE CUST_ACCOUNT_ID = l_cust_account_id_1(I);
IEX_DEBUG_PUB.LOGMESSAGE(SQL%ROWCOUNT || ' Rows updated in ar_trx_bal_summary with reference_1 = 1');
select count(we.name)
into l_noof_active_busi_events
from wf_event_subscriptions wes,
wf_events we,
fnd_application fa
where we.guid = wes.event_filter_guid
and we.name like 'oracle.apps.ar.%'
and fa.application_id=695
and WES.owner_tag = fa.application_short_name
and wes.status='ENABLED'
and we.status='ENABLED';
IEX_DEBUG_PUB.LOGMESSAGE('Starting to update ar_trx_bal_summary with reference_1 = Null...');
IEX_DEBUG_PUB.LOGMESSAGE('Starting to update ar_trx_bal_summary with reference_1 = 1...');
UPDATE AR_TRX_BAL_SUMMARY ARS
SET REFERENCE_1 = '1'
WHERE CUST_ACCOUNT_ID = l_cust_account_id_1(I)
AND ORG_ID = FND_PROFILE.VALUE('ORG_ID');
IEX_DEBUG_PUB.LOGMESSAGE(SQL%ROWCOUNT || ' Rows updated in ar_trx_bal_summary with reference_1 = 1');
IEX_DEBUG_PUB.LOGMESSAGE('Starting to update ar_trx_bal_summary with reference_1 = Null...');
IEX_DEBUG_PUB.LOGMESSAGE('Exit after Update ar_trx_bal_summary on complete with reference_1 = Null...');
UPDATE AR_TRX_BAL_SUMMARY ARS
SET REFERENCE_1 = Null
WHERE CUST_ACCOUNT_ID = l_cust_account_id_n(I);
FND_FILE.PUT_LINE(FND_FILE.LOG,SQL%ROWCOUNT || ' Rows updated in ar_trx_bal_summary with reference_1 = NULL');
IEX_DEBUG_PUB.LOGMESSAGE(SQL%ROWCOUNT || 'Rows updated in ar_trx_bal_summary with reference_1 = Null');
if l_del_insert_count > 0 OR l_del_update_count > 0 then
if l_enable_business_events = 'Y' then
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Business Events Processing Enabled... ' ) ;
p_del_create_count =>l_del_insert_count,
p_del_update_count =>l_del_update_count,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
x_del_id_tbl.DELETE ;
Select
--fnd_profile.value('ORG_ID'), --Commneted for MOAC
NVL(fnd_profile.value('USER_ID'), -1),
sysdate
into
--v_org_id , --Commneted for MOAC
v_user_id ,
v_today
From dual ;
Select IEX_DELINQUENCIES_S.NEXTVAL
into x_del_id_tbl(v_count)
From dual ;
INSERT INTO IEX_DELINQUENCIES_ALL
(DELINQUENCY_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
OBJECT_VERSION_NUMBER,
DUNN_YN ,
PARTY_CUST_ID ,
CUST_ACCOUNT_ID ,
CASE_ID ,
STATUS ,
ORG_ID ,
SOURCE_PROGRAM_NAME )
VALUES
(x_del_id_tbl(v_count) ,
v_today ,
v_user_id ,
v_today ,
v_user_id ,
1 ,
'N' ,
p_party_id ,
NULL ,
p_object_id_tbl(cnt),
vf_delinquent ,
v_org_id ,
p_source_module ) ;
IEX_DEBUG_PUB.LOGMESSAGE('In for Current invoice insertion....');
select IEX_DELINQUENCIES_S.NEXTVAL
into l_deln_id
from dual;
select org_id,customer_id,customer_site_use_id,customer_trx_id--Added for Bug 8517550 14-May-2009 barathsr
into l_org_id,l_cust_acct_id,l_cust_site_use_id,l_cust_trx_id --29/12
from ar_payment_schedules_all
where payment_schedule_id= p_object_id_tbl(i);
INSERT INTO IEX_DELINQUENCIES_ALL
(DELINQUENCY_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
OBJECT_VERSION_NUMBER,
DUNN_YN ,
PARTY_CUST_ID ,
CUST_ACCOUNT_ID ,
transaction_id, --Added for Bug 8517550 14-May-2009 barathsr
payment_schedule_id,
STATUS ,
ORG_ID ,
SOURCE_PROGRAM_NAME,
CUSTOMER_SITE_USE_ID)
VALUES
(l_deln_id,
v_today ,
v_user_id ,
v_today ,
v_user_id ,
1 ,
'N' ,
p_party_id ,
l_cust_acct_id, --29/12
l_cust_trx_id, --Added for Bug 8517550 14-May-2009 barathsr
p_object_id_tbl(i),
vf_current,
l_org_id ,
p_source_module,
l_cust_site_use_id) ; --29/12
IEX_DEBUG_PUB.LOGMESSAGE('Error in CURRENT invoice selection/insertion activity.....');
|| Overview: will update the delinquency header table once the
|| scoring engine for delinquencies is run
||
|| Parameter: p_request_id => request_id of score engine run
||
|| Source Tables: IEX_DEL_BUFFERS
||
|| Target Tables: IEX_DELINQUENCIES_ALL
||
|| Creation date: 03/19/02 10:04:AM
||
|| Major Modifications: when who what
|| 03/19/02 10:04:AM raverma created
*/
procedure SCORE_DELINQUENCIES (ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
p_request_id Number) IS
type t_ids is table of number
index by binary_integer;
select score_object_id,
score_value
from iex_del_buffers
where request_id = p_request_id;
Select score_id into l_score_id
from iex_del_buffers
Where request_id = p_request_id and
rownum = 1;
UPDATE IEX_DELINQUENCIES_ALL
SET SCORE_ID = l_score_id,
Score_value = v_score_values(r),
last_update_date = sysdate,
request_id = FND_GLOBAL.CONC_REQUEST_ID
WHERE DELINQUENCY_ID = v_score_objects(r);
SELECT score_object_id,
score_value
BULK COLLECT INTO
LIMIT NVL(FND_PROFILE.VALUE('IEX_BATCH_SIZE'), 1000)
FROM iex_del_buffers
WHERE request_id = p_request_id;
SELECT PREFERENCE_VALUE FROM IEX_APP_PREFERENCES_VL WHERE PREFERENCE_NAME = 'COLLECTIONS STRATEGY LEVEL';
update iex_delinquencies_all
set UWQ_STATUS = P_UWQ_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
delinquency_id = p_delinquency_id_tbl(i);
update IEX_DLN_UWQ_SUMMARY sum
set
sum.active_delinquencies =
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE party_cust_id = sum.party_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
AND uwq_status = 'PENDING'))
)
),
sum.complete_delinquencies =
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE party_cust_id = sum.party_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND(uwq_status = 'COMPLETE'
AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
),
sum.pending_delinquencies =
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE party_cust_id = sum.party_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND(uwq_status = 'PENDING'
AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
)
WHERE sum.party_id = (select party_cust_id
from iex_delinquencies_all
where delinquency_id = p_delinquency_id_tbl(i));
update IEX_DLN_UWQ_SUMMARY sum
set
sum.active_delinquencies =
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE party_cust_id = party_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
AND uwq_status = 'PENDING'))
)
),
sum.complete_delinquencies =
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE cust_account_id = sum.cust_account_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND(uwq_status = 'COMPLETE'
AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
),
sum.pending_delinquencies =
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE cust_account_id = sum.cust_account_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND(uwq_status = 'PENDING'
AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
)
WHERE sum.cust_account_id = (select cust_account_id
from iex_delinquencies_all
where delinquency_id = p_delinquency_id_tbl(i));
update IEX_DLN_UWQ_SUMMARY sum
set
sum.active_delinquencies =
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE customer_site_use_id = sum.site_use_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
AND uwq_status = 'PENDING'))
)
),
sum.complete_delinquencies =
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE customer_site_use_id = sum.site_use_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND(uwq_status = 'COMPLETE'
AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
),
sum.pending_delinquencies =
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE customer_site_use_id = sum.site_use_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND(uwq_status = 'PENDING'
AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
)
WHERE sum.site_use_id = (select customer_site_use_id
from iex_delinquencies_all
where delinquency_id = p_delinquency_id_tbl(i));
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Updated ' || SQL%ROWCOUNT || ' rows in IEX_DLN_UWQ_SUMMARY');
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': nothing to update');
clchang updated 04/18/2003 for BILL_TO.
in 11.5.10, one more level BILL_TO for dunning level.
--jsanju 08/04/05 for bug#4505461
--change SQL stmts
------------------------------------------------------------------------ */
PROCEDURE CLOSE_DUNNINGS(ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
DUNNING_LEVEL Varchar2)
IS
BEGIN
SAVEPOINT close_dunn ;
UPDATE iex_dunnings idun
set status = 'CLOSE'
where idun.dunning_level = 'DELINQUENCY'
and idun.status = 'OPEN'
and EXISTS
(select delinquency_id
from iex_delinquencies id
where status = 'CURRENT'
and id.delinquency_id = dunning_object_id) ;
UPDATE iex_dunnings idun
set status = 'CLOSE'
where idun.dunning_level = 'BILL_TO'
and idun.status = 'OPEN'
and dunning_object_id IN
(select DISTINCT id.customer_site_use_id
from iex_delinquencies id
where NOT EXISTS
(SELECT customer_site_use_id
FROM IEX_DELINQUENCIES id2
where id2.status IN ('PREDELINQUENT', 'DELINQUENT')
and id2.customer_site_use_id = id.customer_site_use_id)) ;
UPDATE iex_dunnings idun
set status = 'CLOSE'
where idun.dunning_level = 'ACCOUNT'
and idun.status = 'OPEN'
and dunning_object_id IN
(select DISTINCT id.cust_account_id
from iex_delinquencies id
where NOT EXISTS
(SELECT CUST_ACCOUNT_ID
FROM IEX_DELINQUENCIES id2
where id2.status IN ('PREDELINQUENT', 'DELINQUENT')
and id2.cust_account_id = id.cust_account_id)) ;
UPDATE iex_dunnings idun
set status = 'CLOSE'
where idun.dunning_level = 'CUSTOMER'
and idun.status = 'OPEN'
and dunning_object_id IN
(select DISTINCT id.party_cust_id
from iex_delinquencies id
where NOT EXISTS
(SELECT PARTY_CUST_ID
FROM IEX_DELINQUENCIES id2
where id2.status IN ('PREDELINQUENT', 'DELINQUENT')
and id2.party_cust_id = id.party_cust_id)) ;
UPDATE IEX_DUNNINGS IDUN
SET STATUS = 'CLOSE'
WHERE IDUN.DUNNING_LEVEL = 'CUSTOMER'
AND IDUN.STATUS = 'OPEN'
and not exists (SELECT 'x'
FROM IEX_DELINQUENCIES ID
where ID.PARTY_CUST_ID = idun.DUNNING_OBJECT_ID
and ID.STATUS IN ('PREDELINQUENT', 'DELINQUENT'));
|| Parameter: P_REQUEST is the request Id we need to delete, if it is -1 we delete the whole table
||
|| Source Tables: None
||
|| Target Tables: IEX_DEL_BUFFERS
||
|| Creation date: 01/25/05 3:29:PM
||
|| Major Modifications: when who what
|| 01/25/05 acaraujo created
*/
PROCEDURE CLEAR_BUFFERS2(P_REQUEST IN NUMBER) IS
iCount number;
--For big customers delete from IEX_DEL_BUFFERS process takes hours
l_del_count number;
select count(1) into l_del_count
from fnd_conc_req_summary_v
where program_application_id = 695 and
program_short_name in ('IEXDLMGB', 'IEX_SCORE_OBJECTS') and
phase_code in ('P', 'R')
and status_code <> 'Q'; -- changed for bug 9251590