[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
vSQL := 'SELECT TO_NUMBER(TO_CHAR(:b + :a, ''D'')) FROM DUAL';
vSQL := 'SELECT :b + :a FROM DUAL';
SELECT
PRD.PROMISE_DETAIL_ID
FROM
IEX_PROMISE_DETAILS PRD,
IEX_DELINQUENCIES_ALL DEL
WHERE
DEL.DELINQUENCY_ID = PRD.DELINQUENCY_ID AND
DEL.CUST_ACCOUNT_ID = PRD.CUST_ACCOUNT_ID AND
DEL.PAYMENT_SCHEDULE_ID = P_PAYMENT_SCHEDULE_ID AND
PRD.STATUS = 'OPEN'
ORDER BY PRD.PROMISE_DETAIL_ID;
LogMessage(G_PKG_NAME || '.' || l_api_name || ':going to update promise ' || l_promise_detail_id || ' with status CLOSED');
UPDATE iex_promise_details
SET STATUS = 'CLOSED',
last_update_date = sysdate,
last_updated_by = G_USER_ID
WHERE promise_detail_id = l_promise_detail_id;
LogMessage(G_PKG_NAME || '.' || l_api_name || ':update failed');
-- P_MESSAGE => 'Failed to update iex_promise_details with STATUS = CLOSED for promise_detail_id = ' || l_promise_detail_id);
LogMessage(G_PKG_NAME || '.' || l_api_name || ':update successfull');
vSQL := 'SELECT ' ||
'PROMISE_DETAIL_ID, status, ''Invoice'' ' ||
'FROM ' ||
'IEX_PROMISE_DETAILS ' ||
'WHERE ' ||
'DELINQUENCY_ID = :del and ' ||
'STATUS in (''OPEN'', ''PENDING'', ''BROKEN'') ' ||
'union ' ||
'SELECT ' ||
'PRD.PROMISE_DETAIL_ID, prd.status, ''Case'' ' ||
'FROM ' ||
'IEX_CASE_OBJECTS CAO, ' ||
'IEX_PROMISE_DETAILS PRD, ' ||
'IEX_DELINQUENCIES DEL ' ||
'WHERE ' ||
'DEL.DELINQUENCY_ID = :del AND ' ||
'DEL.CASE_ID IS NOT NULL AND ' ||
'DEL.CASE_ID = CAO.CAS_ID AND ' ||
'CAO.OBJECT_CODE = ''CONTRACTS'' AND ' ||
'CAO.OBJECT_ID = PRD.CONTRACT_ID AND ' ||
'PRD.DELINQUENCY_ID IS NULL AND ' ||
'PRD.CNSLD_INVOICE_ID IS NULL and ' ||
'PRD.STATUS IN (''OPEN'', ''PENDING'', ''BROKEN'') ' ||
'ORDER BY PROMISE_DETAIL_ID';
UPDATE iex_promise_details
SET STATUS = 'CLOSED',
last_update_date = sysdate,
last_updated_by = G_USER_ID
WHERE promise_detail_id = l_promise_ids_tbl(n);
UPDATE iex_promise_details
SET UWQ_STATUS = 'COMPLETE',
UWQ_COMPLETE_DATE = sysdate,
last_update_date = sysdate,
last_updated_by = G_USER_ID
WHERE promise_detail_id = l_broken_promise_ids_tbl(n);
CURSOR UPDATE_IEX_SUMMARY
IS
SELECT sum(decode(a.status, 'COLLECTABLE', 1, 0) ) numb,
sum(decode(a.status, 'COLLECTABLE', a.amount_due_remaining, 0)) broken_amount,
sum(decode(a.status, 'COLLECTABLE', a.promise_amount, 0)) promise_amount,
d.party_cust_id party_cust_id,
a.cust_account_id cust_account_id,
d.customer_site_use_id customer_site_use_id
FROM iex_promise_details a,
iex_delinquencies d--_all d --Changed for bug 7237026 20-Jan-2009 barathsr
WHERE a.delinquency_id=d.delinquency_id
AND a.state = 'BROKEN_PROMISE'
AND a.status in ('COLLECTABLE','FULFILLED', 'CANCELLED')
AND a.org_id = d.org_id --Added for bug 7237026 31-Dec-2008 barathsr
AND EXISTS (SELECT 1 FROM iex_promise_details b
where TRUNC(b.last_update_date)=TRUNC(SYSDATE) AND a.cust_account_id = b.cust_account_id)
GROUP BY d.party_cust_id,
a.cust_account_id,
d.customer_site_use_id;
CURSOR UPDATE_IEX_ACTIVE_PRO
IS
SELECT sum(decode(pd.status, 'COLLECTABLE', 1, 0)) active_promises,
d.party_cust_id party_cust_id,
pd.cust_account_id cust_account_id,
d.customer_site_use_id customer_site_use_id
FROM iex_promise_details pd,
iex_delinquencies d--_all d --Changed for bug 7237026 20-Jan-2009 barathsr
WHERE pd.delinquency_id=d.delinquency_id
AND pd.state = 'BROKEN_PROMISE'
AND pd.status in ('COLLECTABLE','FULFILLED', 'CANCELLED')
AND pd.org_id =d.org_id --Added for bug 7237026 31-Dec-2008 barathsr
AND EXISTS (SELECT 1 FROM iex_promise_details b
where TRUNC(b.last_update_date)=TRUNC(SYSDATE) AND pd.cust_account_id = b.cust_account_id)
AND(pd.uwq_status IS NULL OR pd.uwq_status = 'ACTIVE'
OR(TRUNC(pd.uwq_active_date) <= TRUNC(sysdate)
AND pd.uwq_status = 'PENDING'))
GROUP BY d.party_cust_id,
pd.cust_account_id,
d.customer_site_use_id;
CURSOR UPDATE_IEX_COMP_PRO
IS
SELECT sum(decode(pd.status, 'COLLECTABLE', 1, 0)) complete_promises,
d.party_cust_id party_cust_id,
pd.cust_account_id cust_account_id,
d.customer_site_use_id customer_site_use_id
FROM iex_promise_details pd,
iex_delinquencies d--_all d --Changed for bug 7237026 20-Jan-2009 barathsr
WHERE pd.delinquency_id=d.delinquency_id
AND pd.state = 'BROKEN_PROMISE'
AND pd.status in ('COLLECTABLE','FULFILLED', 'CANCELLED')
AND pd.org_id = d.org_id --Added for bug 7237026 31-Dec-2008 barathsr
AND EXISTS (SELECT 1 FROM iex_promise_details b
where TRUNC(b.last_update_date)=TRUNC(SYSDATE) AND pd.cust_account_id = b.cust_account_id)
AND(pd.uwq_status = 'COMPLETE'
AND(TRUNC(pd.uwq_complete_date) +
fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate)))
GROUP BY d.party_cust_id,
pd.cust_account_id,
d.customer_site_use_id;
CURSOR UPDATE_IEX_PEND_PRO
IS
SELECT sum(decode(pd.status, 'COLLECTABLE', 1, 0)) pending_promises,
d.party_cust_id party_cust_id,
pd.cust_account_id cust_account_id,
d.customer_site_use_id customer_site_use_id
FROM iex_promise_details pd,
iex_delinquencies d--_all d --Changed for bug 7237026 20-Jan-2009 barathsr
WHERE pd.delinquency_id=d.delinquency_id
AND pd.state = 'BROKEN_PROMISE'
AND pd.status in ('COLLECTABLE','FULFILLED', 'CANCELLED')
AND pd.org_id = d.org_id --Added for bug 7237026 31-Dec-2008 barathsr
AND EXISTS (SELECT 1 FROM iex_promise_details b
where TRUNC(b.last_update_date)=TRUNC(SYSDATE) AND pd.cust_account_id = b.cust_account_id)
AND (pd.uwq_status = 'PENDING'
AND(TRUNC(pd.uwq_active_date) > TRUNC(sysdate)))
GROUP BY d.party_cust_id,
pd.cust_account_id,
d.customer_site_use_id;
CURSOR UPDATE_IEX_SUMMARY IS
SELECT COUNT(CUST_ACCOUNT_ID) NUMB,
sum(AMOUNT_DUE_REMAINING) broken_amount,
sum(PROMISE_AMOUNT) promise_amount,
CUST_ACCOUNT_ID
FROM IEX_PROMISE_DETAILS
WHERE STATE = 'BROKEN_PROMISE'
AND STATUS = 'COLLECTABLE'
AND NVL(AMOUNT_DUE_REMAINING,0) > 0
GROUP BY CUST_ACCOUNT_ID;
CURSOR UPDATE_IEX_ACTIVE_PRO IS
SELECT count(cust_account_id)active_promises,cust_account_id
FROM iex_promise_details pd
WHERE pd.state = 'BROKEN_PROMISE'
AND(pd.uwq_status IS NULL OR pd.uwq_status = 'ACTIVE'
OR(TRUNC(pd.uwq_active_date) <= TRUNC(sysdate)
AND pd.uwq_status = 'PENDING'))
GROUP BY CUST_ACCOUNT_ID;
CURSOR UPDATE_IEX_COMP_PRO IS
SELECT count(cust_account_id)complete_promises,cust_account_id
FROM iex_promise_details pd
WHERE pd.state = 'BROKEN_PROMISE'
AND(pd.uwq_status = 'COMPLETE'
AND(TRUNC(pd.uwq_complete_date) +
fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate)))
GROUP BY CUST_ACCOUNT_ID;
CURSOR UPDATE_IEX_PEND_PRO IS
SELECT count(cust_account_id)pending_promises,cust_account_id
FROM iex_promise_details pd
WHERE pd.state = 'BROKEN_PROMISE' AND(pd.uwq_status = 'PENDING'
AND(TRUNC(pd.uwq_active_date) > TRUNC(sysdate)))
GROUP BY CUST_ACCOUNT_ID;
FOR I IN UPDATE_IEX_SUMMARY
LOOP
UPDATE IEX_DLN_UWQ_SUMMARY
SET NUMBER_OF_PROMISES = I.NUMB,
BROKEN_PROMISE_AMOUNT = I.BROKEN_AMOUNT,
PROMISE_AMOUNT = I.PROMISE_AMOUNT,
LAST_UPDATE_DATE= SYSDATE
WHERE PARTY_ID = I.PARTY_CUST_ID
AND CUST_ACCOUNT_ID = NVL(I.CUST_ACCOUNT_ID,CUST_ACCOUNT_ID)
AND SITE_USE_ID = NVL(I.CUSTOMER_SITE_USE_ID,SITE_USE_ID);
FOR I IN UPDATE_IEX_ACTIVE_PRO
LOOP
UPDATE IEX_DLN_UWQ_SUMMARY
SET ACTIVE_PROMISES = I.ACTIVE_PROMISES,
LAST_UPDATE_DATE= SYSDATE
WHERE PARTY_ID = I.PARTY_CUST_ID
AND CUST_ACCOUNT_ID = NVL(I.CUST_ACCOUNT_ID,CUST_ACCOUNT_ID)
AND SITE_USE_ID = NVL(I.CUSTOMER_SITE_USE_ID,SITE_USE_ID);
FOR I IN UPDATE_IEX_COMP_PRO
LOOP
UPDATE IEX_DLN_UWQ_SUMMARY
SET COMPLETE_PROMISES = I.COMPLETE_PROMISES,
LAST_UPDATE_DATE= SYSDATE
WHERE PARTY_ID = I.PARTY_CUST_ID
AND CUST_ACCOUNT_ID = NVL(I.CUST_ACCOUNT_ID,CUST_ACCOUNT_ID)
AND SITE_USE_ID = NVL(I.CUSTOMER_SITE_USE_ID,SITE_USE_ID);
FOR I IN UPDATE_IEX_PEND_PRO
LOOP
UPDATE IEX_DLN_UWQ_SUMMARY
SET PENDING_PROMISES = I.PENDING_PROMISES,
LAST_UPDATE_DATE = SYSDATE
WHERE PARTY_ID = I.PARTY_CUST_ID
AND CUST_ACCOUNT_ID = NVL(I.CUST_ACCOUNT_ID,CUST_ACCOUNT_ID)
AND SITE_USE_ID = NVL(I.CUSTOMER_SITE_USE_ID,SITE_USE_ID);
vSQL := 'SELECT ' ||
'prd.promise_detail_id, ' ||
'prd.promise_date, ' ||
'prd.status, ' ||
'prd.promise_amount, ' ||
'prd.amount_due_remaining, ' ||
'pax.amount_applied, ' ||
'raa.receivable_application_id ' ||
'FROM ' ||
'AR_RECEIVABLE_APPLICATIONS raa, ' ||
'IEX_prd_appl_xref pax, ' ||
'iex_promise_details prd ' ||
'WHERE ' ||
'raa.receivable_application_id = pax.receivable_application_id and ' ||
'raa.status in (''APP'', ''ACC'') and ' ||
'raa.amount_applied > 0 and ' ||
'raa.reversal_gl_date is not null and ' ||
'pax.reversed_flag is null and ' ||
'pax.reversed_date is null and ' ||
'pax.receivable_application_id is not null and ' ||
'pax.promise_detail_id = prd.promise_detail_id and ' ||
'prd.status in (''COLLECTABLE'', ''FULFILLED'') and ' ||
'prd.org_id = raa.org_id ' || --Added for bug 7237026 barathsr 31-Dec-2008
'ORDER BY raa.receivable_application_id';
vSQL := 'SELECT ' ||
'prd.promise_detail_id, ' ||
'prd.promise_date, ' ||
'prd.status, ' ||
'prd.promise_amount, ' ||
'prd.amount_due_remaining, ' ||
'pax.amount_applied, ' ||
'raa.receivable_application_id ' ||
'FROM ' ||
'IEX_OKL_PAYMENTS_V raa, ' ||
'IEX_prd_appl_xref pax, ' ||
'iex_promise_details prd ' ||
'WHERE ' ||
'raa.receivable_application_id = pax.receivable_application_id and ' ||
'raa.amount_applied > 0 and ' ||
'raa.reversal_gl_date is not null and ' ||
'pax.reversed_flag is null and ' ||
'pax.reversed_date is null and ' ||
'pax.promise_detail_id = prd.promise_detail_id and ' ||
'prd.status in (''COLLECTABLE'', ''FULFILLED'') ' ||
'ORDER BY raa.receivable_application_id';
vSQL := 'SELECT ' ||
'prd.promise_detail_id, ' ||
'prd.promise_date, ' ||
'prd.status, ' ||
'prd.promise_amount, ' ||
'prd.amount_due_remaining, ' ||
'pax.amount_applied, ' ||
'pax.receivable_application_id ' ||
'FROM ' ||
'IEX_prd_appl_xref pax, ' ||
'iex_promise_details prd, ' ||
'AR_SYSTEM_PARAMETERS asp ' ||--Added for bug 73237026 barathsr 31-Dec-2008
'WHERE ' ||
'pax.receivable_application_id IN (select receivable_application_id from IEX_OKL_PAYMENTS_V where ' ||
'amount_applied > 0 and ' ||
'reversal_gl_date is not null) and ' ||
'pax.reversed_flag is null and ' ||
'pax.reversed_date is null and ' ||
'pax.receivable_application_id is not null and ' ||
'pax.promise_detail_id = prd.promise_detail_id and ' ||
'prd.status in (''COLLECTABLE'', ''FULFILLED'') and ' ||
'prd.org_id = asp.org_id ' || --Added for bug 73237026 barathsr
'ORDER BY pax.receivable_application_id';
update IEX_prd_appl_xref
set reversed_flag = 'Y',
reversed_date = sysdate,
last_update_date = sysdate,
last_updated_by = G_USER_ID,
request_id = G_REQUEST_ID
where
receivable_application_id = l_appl_tbl(i).receivable_application_id and
promise_detail_id = l_appl_tbl(i).promise_detail_id;
LogMessage(G_PKG_NAME || '.' || l_api_name || ':update failed');
P_MESSAGE => 'Failed to update record in IEX_prd_appl_xref for promise_detail_id = ' || l_appl_tbl(i).promise_detail_id);
LogMessage(G_PKG_NAME || '.' || l_api_name || ':update successfull');
update IEX_PROMISE_DETAILS
set status = l_new_status,
amount_due_remaining = l_new_remaining_amount,
last_update_date = sysdate,
last_updated_by = G_USER_ID
where promise_detail_id = l_appl_tbl(i).promise_detail_id;
update IEX_PROMISE_DETAILS
set amount_due_remaining = l_new_remaining_amount,
last_update_date = sysdate,
last_updated_by = G_USER_ID
where promise_detail_id = l_appl_tbl(i).promise_detail_id;
LogMessage(G_PKG_NAME || '.' || l_api_name || ':update failed');
P_MESSAGE => 'Failed to update record in IEX_PROMISE_DETAILS for promise_detail_id = ' || l_appl_tbl(i).promise_detail_id);
LogMessage(G_PKG_NAME || '.' || l_api_name || ':update successfull');
vSQL := 'SELECT ' ||
'PRD.promise_detail_id, ' ||
'PRD.creation_date, ' ||
'nvl(PRD.broken_on_date, PRD.promise_date), ' ||
'PRD.status, ' ||
'PRD.state, ' ||
'PRD.promise_amount, ' ||
'PRD.amount_due_remaining, ' ||
'PRD.delinquency_id, ' ||
'DEL.payment_schedule_id ' ||
'FROM ' ||
'iex_promise_details prd, ' ||
'iex_delinquencies del ' ||
'WHERE ' ||
'prd.delinquency_id is not null and ' ||
'del.delinquency_id = prd.delinquency_id and ' ||
'prd.status = ''COLLECTABLE'' and ' ||
'prd.org_id = del.org_id ' || --Added for bug 7237026 barathsr 31-Dec-2008
'order by PRD.promise_date';
UPDATE iex_promise_details
SET STATUS = 'FULFILLED',
last_update_date = sysdate,
last_updated_by = G_USER_ID
WHERE
promise_detail_id in
(select prd.promise_detail_id
from iex_promise_details prd, iex_delinquencies del, ar_payment_schedules aps --added for Bug 6446848 08-Dec-2008 barathsr
where prd.delinquency_id is not null and
prd.delinquency_id = del.delinquency_id and
prd.org_id = del.org_id and --Added for bug 7237026 barathsr 31-Dec-2008
del.payment_schedule_id=aps.payment_schedule_id and --added for Bug 6446848 08-Dec-2008 barathsr
prd.status in ('COLLECTABLE', 'PENDING') and
del.status = 'CURRENT' and
aps.amount_due_remaining = 0);--added for Bug 6446848 08-Dec-2008 barathsr
LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || sql%rowcount || ' rows is updated');
vSQL := 'SELECT ' ||
'PRD.promise_detail_id pro, ' ||
'PRD.creation_date, ' ||
'nvl(PRD.broken_on_date, PRD.promise_date), ' ||
'PRD.status, ' ||
'PRD.state, ' ||
'PRD.promise_amount, ' ||
'PRD.amount_due_remaining, ' ||
'PRD.cust_account_id ' ||
'FROM ' ||
'iex_promise_details prd,' ||
'AR_SYSTEM_PARAMETERS asp ' || --Added for bug 7237026 barathsr 31-Dec-2008
'WHERE ' ||
'prd.delinquency_id is null and ' ||
'prd.CNSLD_INVOICE_ID is null and ' ||
'prd.CONTRACT_ID is null and ' ||
'prd.status = ''COLLECTABLE'' and ' ||
'prd.org_id = asp.org_id ' || --Added for bug 7237026 barathsr 31-Dec-2008
'order by PRD.promise_date';
vSQL := 'SELECT ' ||
'PRD.promise_detail_id, ' ||
'PRD.creation_date, ' ||
'nvl(PRD.broken_on_date, PRD.promise_date), ' ||
'PRD.status, ' ||
'PRD.state, ' ||
'PRD.promise_amount, ' ||
'PRD.amount_due_remaining, ' ||
'PRD.contract_id ' ||
'FROM ' ||
'iex_promise_details prd, ' ||
'AR_SYSTEM_PARAMETERS asp ' || --Added for bug 7237026 barathsr 31-Dec-2008
'WHERE ' ||
'prd.contract_id is not null and ' ||
'prd.status = ''COLLECTABLE'' and ' ||
'prd.org_id = asp.org_id '||--Added for bug 7237026 barathsr 31-Dec-2008
'order by PRD.promise_date';
vSQL_pay_only := 'select ' ||
'raa.receivable_application_id, ' ||
'raa.amount_applied, ' ||
'raa.apply_date, ' ||
'raa.amount_applied - nvl(sum(pax.amount_applied), 0), ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL ' ||
'from ' ||
'AR_RECEIVABLE_APPLICATIONS raa, ' ||
'IEX_prd_appl_xref pax ' ||
'where ' ||
'(trunc(raa.apply_date) between trunc(:PROMISE_CR_DATE) and trunc(sysdate)) and ' ||
'raa.status = ''APP'' and ' ||
'raa.amount_applied > 0 and ' ||
'raa.reversal_gl_date is null and ' ||
'raa.applied_payment_schedule_id = :PSA_ID and ' ||
'raa.receivable_application_id = pax.receivable_application_id(+) and ' ||
'raa.receivable_application_id not in ' ||
'(select receivable_application_id ' ||
'from IEX_prd_appl_xref where promise_detail_id = :PROMISE_ID and ' ||
'REVERSED_FLAG is null and REVERSED_DATE is null and receivable_application_id is NOT NULL) ' ||
'group by raa.receivable_application_id, raa.amount_applied, raa.apply_date ' ||
'order by raa.receivable_application_id';
vSQL_pay_adj := 'SELECT ' ||
'raa.receivable_application_id, ' ||
'raa.amount_applied, ' ||
'raa.apply_date, ' ||
'raa.amount_applied -nvl(SUM(pax.amount_applied), 0), ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL ' ||
'FROM ar_receivable_applications raa, ' ||
'iex_prd_appl_xref pax ' ||
'WHERE(TRUNC(raa.apply_date) BETWEEN TRUNC(:promise_cr_date) ' ||
'AND TRUNC(sysdate)) ' ||
'AND raa.status = ''APP'' ' ||
'AND raa.amount_applied > 0 ' ||
'AND raa.reversal_gl_date IS NULL ' ||
'AND raa.applied_payment_schedule_id = :psa_id ' ||
'AND raa.receivable_application_id = pax.receivable_application_id(+) ' ||
'AND raa.receivable_application_id NOT IN ' ||
'(SELECT receivable_application_id ' ||
'FROM iex_prd_appl_xref ' ||
'WHERE promise_detail_id = :promise_id ' ||
'AND reversed_flag IS NULL ' ||
'AND reversed_date IS NULL AND receivable_application_id is NOT NULL) ' ||
'GROUP BY raa.receivable_application_id, ' ||
'raa.amount_applied, ' ||
'raa.apply_date ' ||
'UNION ALL ' ||
'SELECT NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'ara.adjustment_id, ' ||
'-ara.amount, ' ||
'ara.apply_date, ' ||
'-ara.amount -nvl(SUM(pax.amount_applied), 0) ' ||
'FROM ar_adjustments ara, ' ||
'iex_prd_appl_xref pax ' ||
'WHERE(TRUNC(ara.apply_date) BETWEEN TRUNC(:promise_cr_date) ' ||
'AND TRUNC(sysdate)) ' ||
'AND ara.status = ''A'' ' ||
'AND ara.amount < 0 ' ||
'AND ara.payment_schedule_id = :psa_id ' ||
'AND ara.adjustment_id = pax.adjustment_id(+) ' ||
'AND ara.adjustment_id NOT IN ' ||
'(SELECT adjustment_id ' ||
'FROM iex_prd_appl_xref ' ||
'WHERE promise_detail_id = :promise_id AND adjustment_id is NOT NULL)' ||
'GROUP BY ara.adjustment_id, ' ||
'ara.amount, ' ||
'ara.apply_date';
SELECT count(adjustment_id)
into l_adjustment_count
FROM ar_adjustments
WHERE PAYMENT_SCHEDULE_ID = P_PROMISES_TBL(i).PAYMENT_SCHEDULE_ID;
vSQL := 'select ' ||
'raa.receivable_application_id, ' ||
'raa.amount_applied, ' ||
'raa.apply_date, ' ||
'raa.amount_applied - nvl(sum(pax.amount_applied), 0), ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL ' ||
'from ' ||
'AR_RECEIVABLE_APPLICATIONS raa, ' ||
'IEX_prd_appl_xref pax, ' ||
'AR_PAYMENT_SCHEDULES psa ' ||
'where ' ||
'(trunc(raa.apply_date) between trunc(:PROMISE_CR_DATE) and trunc(sysdate)) and ' ||
'raa.status = ''ACC'' and ' ||
'raa.amount_applied > 0 and ' ||
'raa.reversal_gl_date is null and ' ||
'raa.payment_schedule_id = psa.payment_schedule_id and ' ||
'psa.customer_id = :CUSTOMER_ID and ' ||
'raa.receivable_application_id = pax.receivable_application_id(+) and ' ||
'raa.receivable_application_id not in ' ||
'(select receivable_application_id ' ||
'from IEX_prd_appl_xref where promise_detail_id = :PROMISE_ID and ' ||
'REVERSED_FLAG is null and REVERSED_DATE is null and receivable_application_id is NOT NULL) ' ||
'group by raa.receivable_application_id, raa.amount_applied, raa.apply_date ' ||
'order by raa.receivable_application_id';
vSQL := 'select ' ||
'raa.receivable_application_id, ' ||
'raa.amount_applied, ' ||
'raa.apply_date, ' ||
'raa.amount_applied - nvl(sum(pax.amount_applied), 0) ' ||
'from ' ||
'IEX_OKL_PAYMENTS_V raa, ' ||
'IEX_prd_appl_xref pax ' ||
'where ' ||
'(trunc(raa.apply_date) between trunc(:PROMISE_CR_DATE) and trunc(sysdate)) and ' ||
'raa.amount_applied > 0 and ' ||
'raa.reversal_gl_date is null and ' ||
'raa.contract_id = :CONTRACT_ID and ' ||
'raa.receivable_application_id = pax.receivable_application_id(+) and ' ||
'raa.receivable_application_id not in ' ||
'(select receivable_application_id ' ||
'from IEX_prd_appl_xref where promise_detail_id = :PROMISE_ID and ' ||
'REVERSED_FLAG is null and REVERSED_DATE is null) ' ||
'group by raa.receivable_application_id, raa.amount_applied, raa.apply_date ' ||
'order by raa.receivable_application_id';
vSQL := ' Select '||
' ARAPP.RECEIVABLE_APPLICATION_ID, '||
' ARAPP.AMOUNT_APPLIED, '||
' ARAPP.APPLY_DATE, '||
' ARAPP.AMOUNT_APPLIED - nvl(sum(PAX.amount_applied), 0), '||
' NULL, ' ||
' NULL, ' ||
' NULL, ' ||
' NULL ' ||
' From ' ||
' OKL_CNSLD_AR_STRMS_B CNSLD, '||
' AR_RECEIVABLE_APPLICATIONS ARAPP, '||
' AR_PAYMENT_SCHEDULES PMTSCH, '||
' IEX_prd_appl_xref PAX '||
' Where '||
' CNSLD.khr_id = :CONTRACT_ID '||
' and CNSLD.receivables_invoice_id = PMTSCH.customer_trx_id '||
' and PMTSCH.class = ''INV'' '||
' and PMTSCH.payment_schedule_id = ARAPP.applied_payment_schedule_id '||
' and (trunc(ARAPP.apply_date) between trunc(:PROMISE_CR_DATE) and trunc(sysdate)) '||
' and ARAPP.amount_applied > 0 '||
' and ARAPP.reversal_gl_date is null '||
' and ARAPP.receivable_application_id = PAX.receivable_application_id(+) '||
' and ARAPP.receivable_application_id not in (select receivable_application_id from IEX_prd_appl_xref ' ||
' where promise_detail_id = :PROMISE_ID and REVERSED_FLAG is null and REVERSED_DATE is null ' ||
' and receivable_application_id is NOT NULL) '||
' group by ARAPP.receivable_application_id, ARAPP.amount_applied, ARAPP.apply_date '||
' order by ARAPP.receivable_application_id ';
l_appl_tbl.delete;
UPDATE iex_promise_details
SET amount_due_remaining = P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING,
STATUS = l_status,
STATE = l_state,
CALLBACK_CREATED_YN = 'N',
CALLBACK_DATE = l_callback_date,
last_update_date = sysdate,
last_updated_by = G_USER_ID
WHERE promise_detail_id = P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
UPDATE iex_promise_details
SET amount_due_remaining = P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING,
STATUS = l_status,
STATE = l_state,
last_update_date = sysdate,
last_updated_by = G_USER_ID
WHERE promise_detail_id = P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
LogMessage(G_PKG_NAME || '.' || l_api_name || ':Failed to update iex_promise_details');
P_MESSAGE => 'Failed to update iex_promise_details');
LogMessage(G_PKG_NAME || '.' || l_api_name || ':update successfull');
LogMessage(G_PKG_NAME || '.' || l_api_name || ':Inserting into iex_prd_appl_xref values:');
INSERT INTO iex_prd_appl_xref
(PRD_APPL_XREF_ID
,PROMISE_DETAIL_ID
,RECEIVABLE_APPLICATION_ID
,AMOUNT_APPLIED
,APPLY_DATE
,REVERSED_FLAG
,REVERSED_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY
,PROGRAM_ID
,OBJECT_VERSION_NUMBER
,SECURITY_GROUP_ID
,REQUEST_ID
,ADJUSTMENT_ID)
VALUES (
iex_prd_appl_xref_s.NEXTVAL
,P_PROMISES_TBL(i).PROMISE_DETAIL_ID
,l_appl_tbl(x).receivable_application_id
,l_appl_tbl(x).pro_applied_amount
,sysdate
,null
,null
,SYSDATE
,G_USER_ID
,G_LOGIN_ID
,SYSDATE
,G_USER_ID
,G_PROGRAM_ID
,1.0
,null
,G_REQUEST_ID
,l_appl_tbl(x).adjustment_id);
UPDATE iex_promise_details
SET state = l_state,
CALLBACK_CREATED_YN = 'N',
CALLBACK_DATE = l_callback_date,
last_update_date = sysdate,
last_updated_by = G_USER_ID
WHERE promise_detail_id = P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
UPDATE iex_promise_details
SET state = l_state,
last_update_date = sysdate,
last_updated_by = G_USER_ID
WHERE promise_detail_id = P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
LogMessage(G_PKG_NAME || '.' || l_api_name || ':Failed to update iex_promise_details with STATUS = BROKEN for promise_detail_id = ' || P_PROMISES_TBL(i).PROMISE_DETAIL_ID);
P_MESSAGE => 'Failed to update iex_promise_details with STATUS = BROKEN for promise_detail_id = ' || P_PROMISES_TBL(i).PROMISE_DETAIL_ID);
LogMessage(G_PKG_NAME || '.' || l_api_name || ':update successfull');
select o.name,o.select_id,o.select_name,o.select_details,o.from_table,o.where_clause
from jtf_objects_vl o,
jtf_object_usages u
where trunc(sysdate)
between trunc(nvl(o.start_date_active, sysdate))
and trunc(nvl(o.end_date_active, sysdate))
and u.object_user_code = 'TASK'
and u.object_code = o.object_code
and o.object_code <> 'ESC'
and o.object_code = l_object_type_code;
l_select_id VARCHAR2(200);
l_select_name VARCHAR2(200);
l_select_details VARCHAR2(2000);
l_SelectStmt VARCHAR2(2500);
FETCH Get_Object_Type INTO l_object_type,l_select_id,l_select_name,l_select_details,l_from_table,l_where_clause;
l_SelectStmt := 'SELECT ' || l_select_name;
IF (l_select_details IS NOT NULL) THEN
l_SelectStmt := l_SelectStmt || ',' || l_select_details;
l_SelectStmt := l_SelectStmt || ' FROM '|| l_from_table || ' WHERE ' || l_where_clause;
l_SelectStmt := l_SelectStmt || ' AND ' ;
l_SelectStmt := l_SelectStmt || l_select_id || ' = :source_object_id ';
DBMS_SQL.PARSE(l_CursorID, l_SelectStmt, 1 );
IF (l_select_details IS NOT NULL) THEN
DBMS_SQL.DEFINE_COLUMN(l_CursorID, 2 , l_object_details , 2000 );
IF (l_select_details IS NOT NULL) THEN
DBMS_SQL.COLUMN_VALUE(l_CursorID, 2 , l_object_details );
SELECT
pro.promise_detail_id,
hca.party_id,
pro.resource_id,
pro.cust_account_id,
idel.customer_site_use_id,
idel.delinquency_id
FROM IEX_PROMISE_DETAILS pro, HZ_CUST_ACCOUNTS hca, iex_delinquencies_all idel
WHERE
pro.cust_account_id = hca.cust_account_id
AND idel.delinquency_id(+) = pro.delinquency_id
AND pro.state = 'BROKEN_PROMISE'
AND pro.CALLBACK_CREATED_YN = 'N'
AND trunc(sysdate) >= trunc(pro.callback_date);
select tsk.task_id,
tsk.object_version_number
--,tsk.task_type_id,typ.name task_type, tsk.task_status_id,st.name,tsk.source_object_id
from jtf_tasks_vl tsk,
jtf_task_types_tl typ,
jtf_task_statuses_vl st
where tsk.source_object_type_code='IEX_PROMISE'
and 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 exists(select 1 from iex_promise_details prd where tsk.source_object_id = prd.promise_detail_id
and prd.status<>'COLLECTABLE');
-- update iex_promise_details table
UPDATE iex_promise_details
SET CALLBACK_CREATED_YN = 'Y',
last_update_date = sysdate,
last_updated_by = G_USER_ID
WHERE promise_detail_id = l_promise_detail_id;
LogMessage( 'update failed');
P_MESSAGE => 'Failed to update iex_promise_details for promise_detail_id = ' || l_promise_detail_id);
LogMessage( 'update successfull');
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_obj_version_number,
P_TASK_ID => rec1.task_id,
P_TASK_STATUS_NAME => 'Cancelled',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
vSQL := 'SELECT ' ||
'PROMISE_DETAIL_ID, PROMISE_DATE ' ||
'FROM ' ||
'IEX_PROMISE_DETAILS ' ||
'WHERE ' ||
'DELINQUENCY_ID is not null and ' ||
'DELINQUENCY_ID = :del and ' ||
'STATUS = ''CLOSED'' ' ||
'ORDER BY PROMISE_DETAIL_ID';
UPDATE iex_promise_details
SET STATUS = 'BROKEN',
CALLBACK_CREATED_YN = 'N',
CALLBACK_DATE = l_callback_date,
last_update_date = sysdate,
last_updated_by = G_USER_ID
WHERE promise_detail_id = l_promise_id;
UPDATE iex_promise_details
SET STATUS = 'OPEN',
last_update_date = sysdate,
last_updated_by = G_USER_ID
WHERE promise_detail_id = l_promise_id;
LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: update of promise ' || l_promise_id || ' failed');
-- P_MESSAGE => 'Failed to update iex_promise_details for promise_detail_id = ' || l_promise_id);
LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: update of promise ' || l_promise_id || ' succeeded');