[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_conc_req IS
BEGIN
DELETE from AR_CONC_PROCESS_REQUESTS
where CONCURRENT_PROGRAM_NAME = 'IEX_POPULATE_UWQ_SUM';
INSERT INTO AR_CONC_PROCESS_REQUESTS
(CONCURRENT_PROGRAM_NAME, REQUEST_ID)
values ('IEX_POPULATE_UWQ_SUM',FND_GLOBAL.conc_request_id);
END insert_conc_req;
Insert_Summary(x_errbuf,x_retcode,FROM_DATE,'CP');
PROCEDURE Insert_Summary(
x_errbuf OUT nocopy VARCHAR2,
x_retcode OUT nocopy VARCHAR2,
FROM_DATE IN VARCHAR2,
P_MODE IN VARCHAR2 DEFAULT 'CP')
IS
CURSOR c_get_level IS
SELECT PREFERENCE_VALUE FROM IEX_APP_PREFERENCES_VL WHERE PREFERENCE_NAME = 'COLLECTIONS STRATEGY LEVEL';
select OWNER || '.' || TABLE_NAME from sys.all_tables where table_name = 'IEX_DLN_UWQ_SUMMARY';
SELECT
trx_summ.org_id,
max(ac.collector_id),
max(ac.resource_id),
max(ac.resource_type),
objb.object_function ieu_object_function,
objb.object_parameters || ' DISPLAYCBO=IEXTRMAN' ieu_object_parameters,
'' ieu_media_type_uuid,
'CUSTOMER_SITE_USE_ID' ieu_param_pk_col,
to_char(trx_summ.site_use_id) ieu_param_pk_value,
1 resource_id,
'RS_EMPLOYEE' resource_type,
party.party_id party_id,
party.party_name party_name,
trx_summ.cust_account_id cust_account_id,
acc.account_name account_name,
acc.account_number account_number,
trx_summ.site_use_id site_use_id,
site_uses.location location,
max(gl.CURRENCY_CODE) currency,
SUM(trx_summ.op_invoices_count) op_invoices_count,
SUM(trx_summ.op_debit_memos_count) op_debit_memos_count,
SUM(trx_summ.op_deposits_count) op_deposits_count,
SUM(trx_summ.op_bills_receivables_count) op_bills_receivables_count,
SUM(trx_summ.op_chargeback_count) op_chargeback_count,
SUM(trx_summ.op_credit_memos_count) op_credit_memos_count,
SUM(trx_summ.unresolved_cash_count) unresolved_cash_count,
SUM(trx_summ.disputed_inv_count) disputed_inv_count,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.best_current_receivables,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.best_current_receivables))) best_current_receivables,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_invoices_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_invoices_value))) op_invoices_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_debit_memos_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_debit_memos_value))) op_debit_memos_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_deposits_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_deposits_value))) op_deposits_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_bills_receivables_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_bills_receivables_value))) op_bills_receivables_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_chargeback_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_chargeback_value))) op_chargeback_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_credit_memos_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_credit_memos_value))) op_credit_memos_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.unresolved_cash_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.unresolved_cash_value))) unresolved_cash_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.receipts_at_risk_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.receipts_at_risk_value))) receipts_at_risk_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.inv_amt_in_dispute,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.inv_amt_in_dispute))) inv_amt_in_dispute,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
(SELECT SUM(b.acctd_amount_due_remaining)
FROM iex_delinquencies_all a,
ar_payment_schedules_all b
WHERE a.customer_site_use_id = trx_summ.site_use_id
AND a.payment_schedule_id = b.payment_schedule_id
AND b.status = 'OP'
AND a.status IN('DELINQUENT', 'PREDELINQUENT')
AND b.org_id = trx_summ.org_id) past_due_inv_value,
SUM(trx_summ.past_due_inv_inst_count) past_due_inv_inst_count,
MAX(trx_summ.last_payment_date) last_payment_date,
MAX(iex_uwq_view_pkg.get_last_payment_amount(0, 0, trx_summ.site_use_id)) last_payment_amount,
max(gl.CURRENCY_CODE) last_payment_amount_curr,
MAX(iex_uwq_view_pkg.get_last_payment_number(0, 0, trx_summ.site_use_id)) last_payment_number,
MAX(trx_summ.last_update_date) last_update_date,
MAX(trx_summ.last_updated_by) last_updated_by,
MAX(trx_summ.creation_date) creation_date,
MAX(trx_summ.created_by) created_by,
MAX(trx_summ.last_update_login) last_update_login,
(SELECT COUNT(1)
FROM iex_delinquencies_all
WHERE customer_site_use_id = trx_summ.site_use_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id)
number_of_delinquencies,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE customer_site_use_id = trx_summ.site_use_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id
AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
AND uwq_status = 'PENDING')))
)
active_delinquencies,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE customer_site_use_id = trx_summ.site_use_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id
AND(uwq_status = 'COMPLETE'
AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
)
complete_delinquencies,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all where customer_site_use_id = trx_summ.site_use_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id
AND(uwq_status = 'PENDING'
AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
)
pending_delinquencies,
(SELECT a.score_value
FROM iex_score_histories a
WHERE a.creation_date =
(SELECT MAX(creation_date)
FROM iex_score_histories
WHERE score_object_code = 'IEX_BILLTO'
AND score_object_id = trx_summ.site_use_id)
AND rownum < 2
AND a.score_object_code = 'IEX_BILLTO'
AND a.score_object_id = trx_summ.site_use_id)
score,
party.address1 address1,
party.city city,
party.state state,
party.county county,
fnd_terr.territory_short_name country,
party.province province,
party.postal_code postal_code,
phone.phone_country_code phone_country_code,
phone.phone_area_code phone_area_code,
phone.phone_number phone_number,
phone.phone_extension phone_extension,
(SELECT COUNT(1) FROM iex_bankruptcies bkr WHERE bkr.party_id = party.party_id and NVL(BKR.DISPOSITION_CODE,'GRANTED') in ('GRANTED','NEGOTIATION')) number_of_bankruptcies, -- Changed for bug#7693986
(SELECT COUNT(1) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID and del.customer_site_use_id = TRX_SUMM.site_use_ID AND
PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
NOT IN ('CURRENT', 'CLOSE')
or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
AND DEL.org_id = trx_summ.org_id) number_of_promises,
(SELECT SUM(AMOUNT_DUE_REMAINING) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID and del.customer_site_use_id = TRX_SUMM.site_use_ID AND
PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
NOT IN ('CURRENT', 'CLOSE')
or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
AND DEL.org_id = trx_summ.org_id) BROKEN_PROMISE_AMOUNT ,
(SELECT SUM(PROMISE_AMOUNT) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID and del.customer_site_use_id = TRX_SUMM.site_use_ID AND
PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
NOT IN ('CURRENT', 'CLOSE')
or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
AND DEL.org_id = trx_summ.org_id) PROMISE_AMOUNT,
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1
FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
WHERE pro.cust_account_id = trx_summ.cust_account_id
and del.customer_site_use_id = TRX_SUMM.site_use_ID
AND pro.state = 'BROKEN_PROMISE'
AND(pro.uwq_status IS NULL OR pro.uwq_status = 'ACTIVE' OR(TRUNC(pro.uwq_active_date) <= TRUNC(sysdate)
AND pro.uwq_status = 'PENDING')))
)
) active_promises,
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1
FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
WHERE pro.cust_account_id = trx_summ.cust_account_id
and del.customer_site_use_id = TRX_SUMM.site_use_ID
AND pro.state = 'BROKEN_PROMISE'
AND(pro.uwq_status = 'COMPLETE'
AND(TRUNC(pro.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
)
) complete_promises,
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1
FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
WHERE pro.cust_account_id = trx_summ.cust_account_id
and del.customer_site_use_id = TRX_SUMM.site_use_ID
AND pro.state = 'BROKEN_PROMISE'
AND(pro.uwq_status = 'PENDING'
AND(TRUNC(pro.uwq_active_date) > TRUNC(sysdate))))
)
) pending_promises
FROM ar_trx_bal_summary trx_summ,
hz_cust_accounts acc,
hz_parties party,
jtf_objects_b objb,
hz_contact_points phone,
fnd_territories_tl fnd_terr,
hz_cust_site_uses_all site_uses,
hz_customer_profiles prf,
ar_collectors ac,
GL_SETS_OF_BOOKS gl,
AR_SYSTEM_PARAMETERS_all sys
WHERE
P_MODE = 'CP'
AND trx_summ.reference_1 = '1'
AND trx_summ.site_use_id = site_uses.site_use_id
AND trx_summ.cust_account_id = acc.cust_account_id
AND acc.party_id = party.party_id
AND objb.object_code = 'IEX_BILLTO'
AND party.country = fnd_terr.territory_code(+)
AND fnd_terr.LANGUAGE(+) = userenv('LANG')
AND phone.owner_table_id(+) = party.party_id
AND phone.owner_table_name(+) = 'HZ_PARTIES'
AND phone.contact_point_type(+) = 'PHONE'
AND phone.primary_by_purpose(+) = 'Y'
AND phone.contact_point_purpose(+) = 'COLLECTIONS'
AND phone.phone_line_type(+) NOT IN('PAGER', 'FAX')
AND phone.status(+) = 'A'
AND nvl(phone.do_not_use_flag(+), 'N') = 'N'
and prf.SITE_USE_ID(+) = trx_summ.site_use_id
and ac.collector_id(+) = prf.collector_id
and gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
and trx_summ.org_id = sys.org_id
GROUP BY trx_summ.org_id,
objb.object_function,
objb.object_parameters,
party.party_id,
party.party_name,
trx_summ.cust_account_id,
acc.account_name,
acc.account_number,
trx_summ.site_use_id,
site_uses.location,
party.address1,
party.city,
party.state,
party.county,
fnd_terr.territory_short_name,
party.province,
party.postal_code,
phone.phone_country_code,
phone.phone_area_code,
phone.phone_number,
phone.phone_extension;
SELECT
trx_summ.org_id,
max(ac.collector_id),
max(ac.resource_id),
max(ac.resource_type),
objb.object_function ieu_object_function,
objb.object_parameters || ' DISPLAYCBO=IEXTRMAN' ieu_object_parameters,
'' ieu_media_type_uuid,
'CUSTOMER_SITE_USE_ID' ieu_param_pk_col,
to_char(trx_summ.site_use_id) ieu_param_pk_value,
1 resource_id,
'RS_EMPLOYEE' resource_type,
party.party_id party_id,
party.party_name party_name,
trx_summ.cust_account_id cust_account_id,
acc.account_name account_name,
acc.account_number account_number,
trx_summ.site_use_id site_use_id,
site_uses.location location,
max(gl.CURRENCY_CODE) currency,
SUM(trx_summ.op_invoices_count) op_invoices_count,
SUM(trx_summ.op_debit_memos_count) op_debit_memos_count,
SUM(trx_summ.op_deposits_count) op_deposits_count,
SUM(trx_summ.op_bills_receivables_count) op_bills_receivables_count,
SUM(trx_summ.op_chargeback_count) op_chargeback_count,
SUM(trx_summ.op_credit_memos_count) op_credit_memos_count,
SUM(trx_summ.unresolved_cash_count) unresolved_cash_count,
SUM(trx_summ.disputed_inv_count) disputed_inv_count,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.best_current_receivables,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.best_current_receivables))) best_current_receivables,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_invoices_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_invoices_value))) op_invoices_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_debit_memos_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_debit_memos_value))) op_debit_memos_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_deposits_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_deposits_value))) op_deposits_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_bills_receivables_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_bills_receivables_value))) op_bills_receivables_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_chargeback_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_chargeback_value))) op_chargeback_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_credit_memos_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_credit_memos_value))) op_credit_memos_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.unresolved_cash_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.unresolved_cash_value))) unresolved_cash_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.receipts_at_risk_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.receipts_at_risk_value))) receipts_at_risk_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.inv_amt_in_dispute,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.inv_amt_in_dispute))) inv_amt_in_dispute,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
(SELECT SUM(b.acctd_amount_due_remaining)
FROM iex_delinquencies_all a,
ar_payment_schedules_all b
WHERE a.customer_site_use_id = trx_summ.site_use_id
AND a.payment_schedule_id = b.payment_schedule_id
AND b.status = 'OP'
AND a.status IN('DELINQUENT', 'PREDELINQUENT')
AND b.org_id = trx_summ.org_id) past_due_inv_value,
SUM(trx_summ.past_due_inv_inst_count) past_due_inv_inst_count,
MAX(trx_summ.last_payment_date) last_payment_date,
MAX(iex_uwq_view_pkg.get_last_payment_amount(0, 0, trx_summ.site_use_id)) last_payment_amount,
max(gl.CURRENCY_CODE) last_payment_amount_curr,
MAX(iex_uwq_view_pkg.get_last_payment_number(0, 0, trx_summ.site_use_id)) last_payment_number,
MAX(trx_summ.last_update_date) last_update_date,
MAX(trx_summ.last_updated_by) last_updated_by,
MAX(trx_summ.creation_date) creation_date,
MAX(trx_summ.created_by) created_by,
MAX(trx_summ.last_update_login) last_update_login,
(SELECT COUNT(1)
FROM iex_delinquencies_all
WHERE customer_site_use_id = trx_summ.site_use_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id)
number_of_delinquencies,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE customer_site_use_id = trx_summ.site_use_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id
AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
AND uwq_status = 'PENDING')))
)
active_delinquencies,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE customer_site_use_id = trx_summ.site_use_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id
AND(uwq_status = 'COMPLETE'
AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
)
complete_delinquencies,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all where customer_site_use_id = trx_summ.site_use_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id
AND(uwq_status = 'PENDING'
AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
)
pending_delinquencies,
(SELECT a.score_value
FROM iex_score_histories a
WHERE a.creation_date =
(SELECT MAX(creation_date)
FROM iex_score_histories
WHERE score_object_code = 'IEX_BILLTO'
AND score_object_id = trx_summ.site_use_id)
AND rownum < 2
AND a.score_object_code = 'IEX_BILLTO'
AND a.score_object_id = trx_summ.site_use_id)
score,
party.address1 address1,
party.city city,
party.state state,
party.county county,
fnd_terr.territory_short_name country,
party.province province,
party.postal_code postal_code,
phone.phone_country_code phone_country_code,
phone.phone_area_code phone_area_code,
phone.phone_number phone_number,
phone.phone_extension phone_extension,
(SELECT COUNT(1) FROM iex_bankruptcies bkr WHERE bkr.party_id = party.party_id and NVL(BKR.DISPOSITION_CODE,'GRANTED') in ('GRANTED','NEGOTIATION')) number_of_bankruptcies, -- Changed for bug#7693986
(SELECT COUNT(1) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID and del.customer_site_use_id = TRX_SUMM.site_use_ID AND
PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
NOT IN ('CURRENT', 'CLOSE')
or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV')) --Added for Bug 6446848 06-Jan-2009 barathsr
AND DEL.org_id = trx_summ.org_id) number_of_promises,
(SELECT SUM(AMOUNT_DUE_REMAINING) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID and del.customer_site_use_id = TRX_SUMM.site_use_ID AND
PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
NOT IN ('CURRENT', 'CLOSE')
or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV')) --Added for Bug 6446848 06-Jan-2009 barathsr
AND DEL.org_id = trx_summ.org_id) BROKEN_PROMISE_AMOUNT ,
(SELECT SUM(PROMISE_AMOUNT) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID and del.customer_site_use_id = TRX_SUMM.site_use_ID AND
PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
NOT IN ('CURRENT', 'CLOSE')
or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV')) --Added for Bug 6446848 06-Jan-2009 barathsr
AND DEL.org_id = trx_summ.org_id) PROMISE_AMOUNT,
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1
FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
WHERE pro.cust_account_id = trx_summ.cust_account_id
and del.customer_site_use_id = TRX_SUMM.site_use_ID
AND pro.state = 'BROKEN_PROMISE'
AND(pro.uwq_status IS NULL OR pro.uwq_status = 'ACTIVE' OR(TRUNC(pro.uwq_active_date) <= TRUNC(sysdate)
AND pro.uwq_status = 'PENDING')))
)
) active_promises,
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1
FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
WHERE pro.cust_account_id = trx_summ.cust_account_id
and del.customer_site_use_id = TRX_SUMM.site_use_ID
AND pro.state = 'BROKEN_PROMISE'
AND(pro.uwq_status = 'COMPLETE'
AND(TRUNC(pro.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
)
) complete_promises,
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1
FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
WHERE pro.cust_account_id = trx_summ.cust_account_id
and del.customer_site_use_id = TRX_SUMM.site_use_ID
AND pro.state = 'BROKEN_PROMISE'
AND(pro.uwq_status = 'PENDING'
AND(TRUNC(pro.uwq_active_date) > TRUNC(sysdate))))
)
) pending_promises
FROM ar_trx_bal_summary trx_summ,
hz_cust_accounts acc,
hz_parties party,
jtf_objects_b objb,
hz_contact_points phone,
fnd_territories_tl fnd_terr,
hz_cust_site_uses_all site_uses,
hz_customer_profiles prf,
ar_collectors ac,
GL_SETS_OF_BOOKS gl,
AR_SYSTEM_PARAMETERS_all sys
WHERE
trx_summ.reference_1 = '1'
AND trx_summ.site_use_id = site_uses.site_use_id
AND trx_summ.cust_account_id = acc.cust_account_id
AND acc.party_id = party.party_id
AND objb.object_code = 'IEX_BILLTO'
AND party.country = fnd_terr.territory_code(+)
AND fnd_terr.LANGUAGE(+) = userenv('LANG')
AND phone.owner_table_id(+) = party.party_id
AND phone.owner_table_name(+) = 'HZ_PARTIES'
AND phone.contact_point_type(+) = 'PHONE'
AND phone.primary_by_purpose(+) = 'Y'
AND phone.contact_point_purpose(+) = 'COLLECTIONS'
AND phone.phone_line_type(+) NOT IN('PAGER', 'FAX')
AND phone.status(+) = 'A'
AND nvl(phone.do_not_use_flag(+), 'N') = 'N'
and prf.SITE_USE_ID(+) = trx_summ.site_use_id
and ac.collector_id(+) = prf.collector_id
and gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
and trx_summ.org_id = sys.org_id
-- start bug 5762888 gnramasa 13-July-2007
/* and (trx_summ.cust_account_id, trx_summ.site_use_id, trx_summ.org_id) in
(select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(p_from_date))
*/
and trunc(trx_summ.last_update_date) >= trunc(p_from_date)
-- end bug 5762888 gnramasa 13-July-2007
GROUP BY trx_summ.org_id,
objb.object_function,
objb.object_parameters,
party.party_id,
party.party_name,
trx_summ.cust_account_id,
acc.account_name,
acc.account_number,
trx_summ.site_use_id,
site_uses.location,
party.address1,
party.city,
party.state,
party.county,
fnd_terr.territory_short_name,
party.province,
party.postal_code,
phone.phone_country_code,
phone.phone_area_code,
phone.phone_number,
phone.phone_extension;
SELECT
trx_summ.org_id,
max(ac.collector_id),
max(ac.resource_id),
max(ac.resource_type),
objb.object_function ieu_object_function,
objb.object_parameters || ' DISPLAYCBO=IEXTRMAN' ieu_object_parameters,
'' ieu_media_type_uuid,
'CUST_ACCOUNT_ID' ieu_param_pk_col,
to_char(trx_summ.cust_account_id) ieu_param_pk_value,
1 resource_id,
'RS_EMPLOYEE' resource_type,
party.party_id party_id,
party.party_name party_name,
trx_summ.cust_account_id cust_account_id,
acc.account_name account_name,
acc.account_number account_number,
to_number(null) site_use_id,
null location,
max(gl.CURRENCY_CODE) currency,
SUM(trx_summ.op_invoices_count) op_invoices_count,
SUM(trx_summ.op_debit_memos_count) op_debit_memos_count,
SUM(trx_summ.op_deposits_count) op_deposits_count,
SUM(trx_summ.op_bills_receivables_count) op_bills_receivables_count,
SUM(trx_summ.op_chargeback_count) op_chargeback_count,
SUM(trx_summ.op_credit_memos_count) op_credit_memos_count,
SUM(trx_summ.unresolved_cash_count) unresolved_cash_count,
SUM(trx_summ.disputed_inv_count) disputed_inv_count,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.best_current_receivables,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.best_current_receivables))) best_current_receivables,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_invoices_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_invoices_value))) op_invoices_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_debit_memos_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_debit_memos_value))) op_debit_memos_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_deposits_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_deposits_value))) op_deposits_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_bills_receivables_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_bills_receivables_value))) op_bills_receivables_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_chargeback_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_chargeback_value))) op_chargeback_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_credit_memos_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_credit_memos_value))) op_credit_memos_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.unresolved_cash_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.unresolved_cash_value))) unresolved_cash_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.receipts_at_risk_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.receipts_at_risk_value))) receipts_at_risk_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.inv_amt_in_dispute,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.inv_amt_in_dispute))) inv_amt_in_dispute,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
(SELECT SUM(b.acctd_amount_due_remaining)
FROM iex_delinquencies_all a,
ar_payment_schedules_all b
WHERE a.cust_account_id = trx_summ.cust_account_id
AND a.payment_schedule_id = b.payment_schedule_id
AND b.status = 'OP'
AND a.status IN('DELINQUENT', 'PREDELINQUENT')
AND b.org_id = trx_summ.org_id) past_due_inv_value,
SUM(trx_summ.past_due_inv_inst_count) past_due_inv_inst_count,
MAX(trx_summ.last_payment_date) last_payment_date,
MAX(iex_uwq_view_pkg.get_last_payment_amount(0, trx_summ.cust_account_id, 0)) last_payment_amount,
max(gl.CURRENCY_CODE) last_payment_amount_curr,
MAX(iex_uwq_view_pkg.get_last_payment_number(0, trx_summ.cust_account_id, 0)) last_payment_number,
MAX(trx_summ.last_update_date) last_update_date,
MAX(trx_summ.last_updated_by) last_updated_by,
MAX(trx_summ.creation_date) creation_date,
MAX(trx_summ.created_by) created_by,
MAX(trx_summ.last_update_login) last_update_login,
(SELECT COUNT(1)
FROM iex_delinquencies_all
WHERE cust_account_id = trx_summ.cust_account_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id)
number_of_delinquencies,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE cust_account_id = trx_summ.cust_account_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id
AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
AND uwq_status = 'PENDING')))
)
active_delinquencies,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE cust_account_id = trx_summ.cust_account_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id
AND(uwq_status = 'COMPLETE'
AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
)
complete_delinquencies,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all WHERE cust_account_id = trx_summ.cust_account_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id
AND(uwq_status = 'PENDING'
AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
)
pending_delinquencies,
(SELECT a.score_value
FROM iex_score_histories a
WHERE a.creation_date =
(SELECT MAX(creation_date)
FROM iex_score_histories
WHERE score_object_code = 'IEX_ACCOUNT'
AND score_object_id = trx_summ.cust_account_id)
AND rownum < 2
AND a.score_object_code = 'IEX_ACCOUNT'
AND a.score_object_id = trx_summ.cust_account_id)
score,
party.address1 address1,
party.city city,
party.state state,
party.county county,
fnd_terr.territory_short_name country,
party.province province,
party.postal_code postal_code,
phone.phone_country_code phone_country_code,
phone.phone_area_code phone_area_code,
phone.phone_number phone_number,
phone.phone_extension phone_extension,
(SELECT COUNT(1) FROM iex_bankruptcies bkr WHERE bkr.party_id = party.party_id and NVL(BKR.DISPOSITION_CODE,'GRANTED') in ('GRANTED','NEGOTIATION')) number_of_bankruptcies, -- Changed for bug#7693986
(SELECT COUNT(1) FROM iex_promise_details PRO, IEX_DELINQUENCIES_all DEL
WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID AND
PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
NOT IN ('CURRENT', 'CLOSE')
or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
AND DEL.org_id = trx_summ.org_id) NUMBER_OF_PROMISES ,
(SELECT SUM(AMOUNT_DUE_REMAINING) FROM IEX_PROMISE_DETAILS PRO, IEX_DELINQUENCIES_all DEL
WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID AND
PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
NOT IN ('CURRENT', 'CLOSE')
or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
AND DEL.org_id = trx_summ.org_id) BROKEN_PROMISE_AMOUNT ,
(SELECT SUM(PROMISE_AMOUNT) FROM IEX_PROMISE_DETAILS PRO, IEX_DELINQUENCIES_all DEL
WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID AND
PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
NOT IN ('CURRENT', 'CLOSE')
or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
AND DEL.org_id = trx_summ.org_id) PROMISE_AMOUNT,
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1
FROM iex_promise_details
WHERE cust_account_id = trx_summ.cust_account_id
AND state = 'BROKEN_PROMISE'
AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
AND uwq_status = 'PENDING')))
)
) active_promises,
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1
FROM iex_promise_details
WHERE cust_account_id = trx_summ.cust_account_id
AND state = 'BROKEN_PROMISE'
AND(uwq_status = 'COMPLETE'
AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
)
) complete_promises,
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1
FROM iex_promise_details
WHERE cust_account_id = trx_summ.cust_account_id
AND state = 'BROKEN_PROMISE'
AND(uwq_status = 'PENDING'
AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
)
) pending_promises
FROM ar_trx_bal_summary trx_summ,
hz_cust_accounts acc,
hz_parties party,
jtf_objects_b objb,
hz_contact_points phone,
fnd_territories_tl fnd_terr,
hz_customer_profiles prf,
ar_collectors ac,
GL_SETS_OF_BOOKS gl,
AR_SYSTEM_PARAMETERS_all sys
WHERE
P_MODE = 'CP'
AND trx_summ.reference_1 = '1'
AND trx_summ.cust_account_id = acc.cust_account_id
AND acc.party_id = party.party_id
AND objb.object_code = 'IEX_ACCOUNT'
AND party.country = fnd_terr.territory_code(+)
AND fnd_terr.LANGUAGE(+) = userenv('LANG')
AND phone.owner_table_id(+) = party.party_id
AND phone.owner_table_name(+) = 'HZ_PARTIES'
AND phone.contact_point_type(+) = 'PHONE'
AND phone.primary_by_purpose(+) = 'Y'
AND phone.contact_point_purpose(+) = 'COLLECTIONS'
AND phone.phone_line_type(+) NOT IN('PAGER', 'FAX')
AND phone.status(+) = 'A'
AND nvl(phone.do_not_use_flag(+), 'N') = 'N'
and prf.CUST_ACCOUNT_ID = trx_summ.CUST_ACCOUNT_ID
and prf.SITE_USE_ID is null
and ac.collector_id(+) = prf.collector_id
and gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
and trx_summ.org_id = sys.org_id
GROUP BY trx_summ.org_id,
objb.object_function,
objb.object_parameters,
party.party_id,
party.party_name,
trx_summ.cust_account_id,
acc.account_name,
acc.account_number,
party.address1,
party.city,
party.state,
party.county,
fnd_terr.territory_short_name,
party.province,
party.postal_code,
phone.phone_country_code,
phone.phone_area_code,
phone.phone_number,
phone.phone_extension;
SELECT
trx_summ.org_id,
max(ac.collector_id),
max(ac.resource_id),
max(ac.resource_type),
objb.object_function ieu_object_function,
objb.object_parameters || ' DISPLAYCBO=IEXTRMAN' ieu_object_parameters,
'' ieu_media_type_uuid,
'CUST_ACCOUNT_ID' ieu_param_pk_col,
to_char(trx_summ.cust_account_id) ieu_param_pk_value,
1 resource_id,
'RS_EMPLOYEE' resource_type,
party.party_id party_id,
party.party_name party_name,
trx_summ.cust_account_id cust_account_id,
acc.account_name account_name,
acc.account_number account_number,
to_number(null) site_use_id,
null location,
max(gl.CURRENCY_CODE) currency,
SUM(trx_summ.op_invoices_count) op_invoices_count,
SUM(trx_summ.op_debit_memos_count) op_debit_memos_count,
SUM(trx_summ.op_deposits_count) op_deposits_count,
SUM(trx_summ.op_bills_receivables_count) op_bills_receivables_count,
SUM(trx_summ.op_chargeback_count) op_chargeback_count,
SUM(trx_summ.op_credit_memos_count) op_credit_memos_count,
SUM(trx_summ.unresolved_cash_count) unresolved_cash_count,
SUM(trx_summ.disputed_inv_count) disputed_inv_count,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.best_current_receivables,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.best_current_receivables))) best_current_receivables,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_invoices_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_invoices_value))) op_invoices_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_debit_memos_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_debit_memos_value))) op_debit_memos_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_deposits_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_deposits_value))) op_deposits_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_bills_receivables_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_bills_receivables_value))) op_bills_receivables_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_chargeback_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_chargeback_value))) op_chargeback_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_credit_memos_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_credit_memos_value))) op_credit_memos_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.unresolved_cash_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.unresolved_cash_value))) unresolved_cash_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.receipts_at_risk_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.receipts_at_risk_value))) receipts_at_risk_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.inv_amt_in_dispute,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.inv_amt_in_dispute))) inv_amt_in_dispute,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
(SELECT SUM(b.acctd_amount_due_remaining)
FROM iex_delinquencies_all a,
ar_payment_schedules_all b
WHERE a.cust_account_id = trx_summ.cust_account_id
AND a.payment_schedule_id = b.payment_schedule_id
AND b.status = 'OP'
AND a.status IN('DELINQUENT', 'PREDELINQUENT')
AND b.org_id = trx_summ.org_id) past_due_inv_value,
SUM(trx_summ.past_due_inv_inst_count) past_due_inv_inst_count,
MAX(trx_summ.last_payment_date) last_payment_date,
MAX(iex_uwq_view_pkg.get_last_payment_amount(0, trx_summ.cust_account_id, 0)) last_payment_amount,
max(gl.CURRENCY_CODE) last_payment_amount_curr,
MAX(iex_uwq_view_pkg.get_last_payment_number(0, trx_summ.cust_account_id, 0)) last_payment_number,
MAX(trx_summ.last_update_date) last_update_date,
MAX(trx_summ.last_updated_by) last_updated_by,
MAX(trx_summ.creation_date) creation_date,
MAX(trx_summ.created_by) created_by,
MAX(trx_summ.last_update_login) last_update_login,
(SELECT COUNT(1)
FROM iex_delinquencies_all
WHERE cust_account_id = trx_summ.cust_account_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id)
number_of_delinquencies,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE cust_account_id = trx_summ.cust_account_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id
AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
AND uwq_status = 'PENDING')))
)
active_delinquencies,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE cust_account_id = trx_summ.cust_account_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id
AND(uwq_status = 'COMPLETE'
AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
)
complete_delinquencies,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all WHERE cust_account_id = trx_summ.cust_account_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id
AND(uwq_status = 'PENDING'
AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
)
pending_delinquencies,
(SELECT a.score_value
FROM iex_score_histories a
WHERE a.creation_date =
(SELECT MAX(creation_date)
FROM iex_score_histories
WHERE score_object_code = 'IEX_ACCOUNT'
AND score_object_id = trx_summ.cust_account_id)
AND rownum < 2
AND a.score_object_code = 'IEX_ACCOUNT'
AND a.score_object_id = trx_summ.cust_account_id)
score,
party.address1 address1,
party.city city,
party.state state,
party.county county,
fnd_terr.territory_short_name country,
party.province province,
party.postal_code postal_code,
phone.phone_country_code phone_country_code,
phone.phone_area_code phone_area_code,
phone.phone_number phone_number,
phone.phone_extension phone_extension,
(SELECT COUNT(1) FROM iex_bankruptcies bkr WHERE bkr.party_id = party.party_id and NVL(BKR.DISPOSITION_CODE,'GRANTED') in ('GRANTED','NEGOTIATION')) number_of_bankruptcies, -- Changed for bug#7693986
(SELECT COUNT(1) FROM iex_promise_details PRO, IEX_DELINQUENCIES_all DEL
WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID AND
PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
NOT IN ('CURRENT', 'CLOSE')
or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
AND DEL.org_id = trx_summ.org_id) NUMBER_OF_PROMISES ,
(SELECT SUM(AMOUNT_DUE_REMAINING) FROM IEX_PROMISE_DETAILS PRO, IEX_DELINQUENCIES_all DEL
WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID AND
PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
NOT IN ('CURRENT', 'CLOSE')
or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
AND DEL.org_id = trx_summ.org_id) BROKEN_PROMISE_AMOUNT ,
(SELECT SUM(PROMISE_AMOUNT) FROM IEX_PROMISE_DETAILS PRO, IEX_DELINQUENCIES_all DEL
WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID AND
PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
NOT IN ('CURRENT', 'CLOSE')
or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
AND DEL.org_id = trx_summ.org_id) PROMISE_AMOUNT,
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1
FROM iex_promise_details
WHERE cust_account_id = trx_summ.cust_account_id
AND state = 'BROKEN_PROMISE'
AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
AND uwq_status = 'PENDING')))
)
) active_promises,
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1
FROM iex_promise_details
WHERE cust_account_id = trx_summ.cust_account_id
AND state = 'BROKEN_PROMISE'
AND(uwq_status = 'COMPLETE'
AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
)
) complete_promises,
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1 FROM dual WHERE EXISTS
(SELECT 1
FROM iex_promise_details
WHERE cust_account_id = trx_summ.cust_account_id
AND state = 'BROKEN_PROMISE'
AND(uwq_status = 'PENDING'
AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
)
) pending_promises
FROM ar_trx_bal_summary trx_summ,
hz_cust_accounts acc,
hz_parties party,
jtf_objects_b objb,
hz_contact_points phone,
fnd_territories_tl fnd_terr,
hz_customer_profiles prf,
ar_collectors ac,
GL_SETS_OF_BOOKS gl,
AR_SYSTEM_PARAMETERS_all sys
WHERE
trx_summ.reference_1 = '1'
AND trx_summ.cust_account_id = acc.cust_account_id
AND acc.party_id = party.party_id
AND objb.object_code = 'IEX_ACCOUNT'
AND party.country = fnd_terr.territory_code(+)
AND fnd_terr.LANGUAGE(+) = userenv('LANG')
AND phone.owner_table_id(+) = party.party_id
AND phone.owner_table_name(+) = 'HZ_PARTIES'
AND phone.contact_point_type(+) = 'PHONE'
AND phone.primary_by_purpose(+) = 'Y'
AND phone.contact_point_purpose(+) = 'COLLECTIONS'
AND phone.phone_line_type(+) NOT IN('PAGER', 'FAX')
AND phone.status(+) = 'A'
AND nvl(phone.do_not_use_flag(+), 'N') = 'N'
and prf.CUST_ACCOUNT_ID = trx_summ.CUST_ACCOUNT_ID
and prf.SITE_USE_ID is null
and ac.collector_id(+) = prf.collector_id
and gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
and trx_summ.org_id = sys.org_id
-- start bug 5762888 gnramasa 13-July-2007
/* and (trx_summ.cust_account_id, trx_summ.site_use_id, trx_summ.org_id) in */
/* changed for bug 5677415 by gnramasa on 27/11/2006 */
/* (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(sysdate)) */
/* (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(p_from_date)) */
and trunc(trx_summ.last_update_date) >= trunc(p_from_date)
-- end bug 5762888 gnramasa 13-July-2007
GROUP BY trx_summ.org_id,
objb.object_function,
objb.object_parameters,
party.party_id,
party.party_name,
trx_summ.cust_account_id,
acc.account_name,
acc.account_number,
party.address1,
party.city,
party.state,
party.county,
fnd_terr.territory_short_name,
party.province,
party.postal_code,
phone.phone_country_code,
phone.phone_area_code,
phone.phone_number,
phone.phone_extension ;
SELECT
trx_summ.org_id,
max(ac.collector_id),
max(ac.resource_id),
max(ac.resource_type),
objb.object_function ieu_object_function,
objb.object_parameters || ' DISPLAYCBO=IEXTRMAN' ieu_object_parameters,
'' ieu_media_type_uuid,
'PARTY_ID' ieu_param_pk_col,
to_char(party.party_id) ieu_param_pk_value,
1 resource_id,
'RS_EMPLOYEE' resource_type,
party.party_id party_id,
party.party_name party_name,
to_number(null) cust_account_id,
null account_name,
null account_number,
to_number(null) site_use_id,
null location,
max(gl.CURRENCY_CODE) currency,
SUM(trx_summ.op_invoices_count) op_invoices_count,
SUM(trx_summ.op_debit_memos_count) op_debit_memos_count,
SUM(trx_summ.op_deposits_count) op_deposits_count,
SUM(trx_summ.op_bills_receivables_count) op_bills_receivables_count,
SUM(trx_summ.op_chargeback_count) op_chargeback_count,
SUM(trx_summ.op_credit_memos_count) op_credit_memos_count,
SUM(trx_summ.unresolved_cash_count) unresolved_cash_count,
SUM(trx_summ.disputed_inv_count) disputed_inv_count,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.best_current_receivables,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.best_current_receivables))) best_current_receivables,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_invoices_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_invoices_value))) op_invoices_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_debit_memos_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_debit_memos_value))) op_debit_memos_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_deposits_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_deposits_value))) op_deposits_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_bills_receivables_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_bills_receivables_value))) op_bills_receivables_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_chargeback_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_chargeback_value))) op_chargeback_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_credit_memos_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_credit_memos_value))) op_credit_memos_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.unresolved_cash_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.unresolved_cash_value))) unresolved_cash_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.receipts_at_risk_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.receipts_at_risk_value))) receipts_at_risk_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.inv_amt_in_dispute,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.inv_amt_in_dispute))) inv_amt_in_dispute,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
(SELECT SUM(b.acctd_amount_due_remaining)
FROM iex_delinquencies_all a,
ar_payment_schedules_all b
WHERE a.party_cust_id = party.party_id
AND a.payment_schedule_id = b.payment_schedule_id
AND b.status = 'OP'
AND a.status IN('DELINQUENT', 'PREDELINQUENT')
AND b.org_id = trx_summ.org_id) past_due_inv_value,
SUM(trx_summ.past_due_inv_inst_count) past_due_inv_inst_count,
MAX(trx_summ.last_payment_date) last_payment_date,
MAX(iex_uwq_view_pkg.get_last_payment_amount(party.party_id, 0, 0)) last_payment_amount,
max(gl.CURRENCY_CODE) last_payment_amount_curr,
MAX(iex_uwq_view_pkg.get_last_payment_number(party.party_id, 0, 0)) last_payment_number,
MAX(trx_summ.last_update_date) last_update_date,
MAX(trx_summ.last_updated_by) last_updated_by,
MAX(trx_summ.creation_date) creation_date,
MAX(trx_summ.created_by) created_by,
MAX(trx_summ.last_update_login) last_update_login,
(SELECT COUNT(1)
FROM iex_delinquencies_all
WHERE party_cust_id = party.party_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id)
number_of_delinquencies,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE party_cust_id = party.party_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id
AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
AND uwq_status = 'PENDING')))
)
active_delinquencies,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE party_cust_id = party.party_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id
AND(uwq_status = 'COMPLETE'
AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
)
complete_delinquencies,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE party_cust_id = party.party_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id
AND(uwq_status = 'PENDING'
AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
)
pending_delinquencies,
(SELECT a.score_value
FROM iex_score_histories a
WHERE a.creation_date =
(SELECT MAX(creation_date)
FROM iex_score_histories
WHERE score_object_code = 'PARTY'
AND score_object_id = party.party_id)
AND rownum < 2
AND a.score_object_code = 'PARTY'
AND a.score_object_id = party.party_id)
score,
party.address1 address1,
party.city city,
party.state state,
party.county county,
fnd_terr.territory_short_name country,
party.province province,
party.postal_code postal_code,
phone.phone_country_code phone_country_code,
phone.phone_area_code phone_area_code,
phone.phone_number phone_number,
phone.phone_extension phone_extension,
(SELECT COUNT(1) FROM iex_bankruptcies bkr WHERE bkr.party_id = party.party_id and NVL(BKR.DISPOSITION_CODE,'GRANTED') in ('GRANTED','NEGOTIATION')) number_of_bankruptcies, -- Changed for bug#7693986
iex_uwq_view_pkg.get_pro_count(party.party_id, NULL, NULL, NULL, trx_summ.org_id) number_of_promises,
iex_uwq_view_pkg.get_broken_prm_amt(party.party_id, NULL, NULL, trx_summ.org_id) broken_promise_amount,
iex_uwq_view_pkg.get_prm_amt(party.party_id, NULL, NULL, trx_summ.org_id) promise_amount,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_promise_details pd,
hz_cust_accounts b
WHERE b.party_id = party.party_id
AND pd.cust_account_id = b.cust_account_id
AND 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')))
)
active_promises,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_promise_details pd,
hz_cust_accounts b
WHERE b.party_id = party.party_id
AND pd.cust_account_id = b.cust_account_id
AND pd.state = 'BROKEN_PROMISE'
AND(pd.uwq_status = 'COMPLETE'
AND(TRUNC(pd.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
)
complete_promises,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_promise_details pd,
hz_cust_accounts b
WHERE b.party_id = party.party_id
AND pd.cust_account_id = b.cust_account_id
and pd.state = 'BROKEN_PROMISE'
AND(pd.uwq_status = 'PENDING'
AND(TRUNC(pd.uwq_active_date) > TRUNC(sysdate))))
)
pending_promises
FROM ar_trx_bal_summary trx_summ,
hz_cust_accounts acc,
hz_parties party,
jtf_objects_b objb,
hz_contact_points phone,
fnd_territories_tl fnd_terr,
hz_customer_profiles prf,
ar_collectors ac,
GL_SETS_OF_BOOKS gl,
AR_SYSTEM_PARAMETERS_all sys
WHERE
P_MODE = 'CP'
AND trx_summ.reference_1 = '1'
AND trx_summ.cust_account_id = acc.cust_account_id
AND acc.party_id = party.party_id
AND objb.object_code = 'IEX_CUSTOMER'
AND party.country = fnd_terr.territory_code(+)
AND fnd_terr.LANGUAGE(+) = userenv('LANG')
AND phone.owner_table_id(+) = party.party_id
AND phone.owner_table_name(+) = 'HZ_PARTIES'
AND phone.contact_point_type(+) = 'PHONE'
and phone.primary_by_purpose(+) = 'Y'
AND phone.contact_point_purpose(+) = 'COLLECTIONS'
AND phone.phone_line_type(+) NOT IN('PAGER', 'FAX')
AND phone.status(+) = 'A'
AND nvl(phone.do_not_use_flag(+), 'N') = 'N'
AND acc.party_id = prf.party_id
and prf.CUST_ACCOUNT_ID = -1
-- and prf.CUST_ACCOUNT_ID = trx_summ.CUST_ACCOUNT_ID
and prf.SITE_USE_ID is null
and ac.collector_id(+) = prf.collector_id
and gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
and trx_summ.org_id = sys.org_id
GROUP BY trx_summ.org_id,
objb.object_function,
objb.object_parameters,
party.party_id,
party.party_name,
party.address1,
party.city,
party.state,
party.county,
fnd_terr.territory_short_name,
party.province,
party.postal_code,
phone.phone_country_code,
phone.phone_area_code,
phone.phone_number,
phone.phone_extension;
SELECT
trx_summ.org_id,
max(ac.collector_id),
max(ac.resource_id),
max(ac.resource_type),
objb.object_function ieu_object_function,
objb.object_parameters || ' DISPLAYCBO=IEXTRMAN' ieu_object_parameters,
'' ieu_media_type_uuid,
'PARTY_ID' ieu_param_pk_col,
to_char(party.party_id) ieu_param_pk_value,
1 resource_id,
'RS_EMPLOYEE' resource_type,
party.party_id party_id,
party.party_name party_name,
to_number(null) cust_account_id,
null account_name,
null account_number,
to_number(null) site_use_id,
null location,
max(gl.CURRENCY_CODE) currency,
SUM(trx_summ.op_invoices_count) op_invoices_count,
SUM(trx_summ.op_debit_memos_count) op_debit_memos_count,
SUM(trx_summ.op_deposits_count) op_deposits_count,
SUM(trx_summ.op_bills_receivables_count) op_bills_receivables_count,
SUM(trx_summ.op_chargeback_count) op_chargeback_count,
SUM(trx_summ.op_credit_memos_count) op_credit_memos_count,
SUM(trx_summ.unresolved_cash_count) unresolved_cash_count,
SUM(trx_summ.disputed_inv_count) disputed_inv_count,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.best_current_receivables,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.best_current_receivables))) best_current_receivables,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_invoices_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_invoices_value))) op_invoices_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_debit_memos_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_debit_memos_value))) op_debit_memos_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_deposits_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_deposits_value))) op_deposits_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_bills_receivables_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_bills_receivables_value))) op_bills_receivables_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_chargeback_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_chargeback_value))) op_chargeback_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_credit_memos_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_credit_memos_value))) op_credit_memos_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.unresolved_cash_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.unresolved_cash_value))) unresolved_cash_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.receipts_at_risk_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.receipts_at_risk_value))) receipts_at_risk_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.inv_amt_in_dispute,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.inv_amt_in_dispute))) inv_amt_in_dispute,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
(SELECT SUM(b.acctd_amount_due_remaining)
FROM iex_delinquencies_all a,
ar_payment_schedules_all b
WHERE a.party_cust_id = party.party_id
AND a.payment_schedule_id = b.payment_schedule_id
AND b.status = 'OP'
AND a.status IN('DELINQUENT', 'PREDELINQUENT')
AND b.org_id = trx_summ.org_id) past_due_inv_value,
SUM(trx_summ.past_due_inv_inst_count) past_due_inv_inst_count,
MAX(trx_summ.last_payment_date) last_payment_date,
MAX(iex_uwq_view_pkg.get_last_payment_amount(party.party_id, 0, 0)) last_payment_amount,
max(gl.CURRENCY_CODE) last_payment_amount_curr,
MAX(iex_uwq_view_pkg.get_last_payment_number(party.party_id, 0, 0)) last_payment_number,
MAX(trx_summ.last_update_date) last_update_date,
MAX(trx_summ.last_updated_by) last_updated_by,
MAX(trx_summ.creation_date) creation_date,
MAX(trx_summ.created_by) created_by,
MAX(trx_summ.last_update_login) last_update_login,
(SELECT COUNT(1)
FROM iex_delinquencies_all
WHERE party_cust_id = party.party_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id)
number_of_delinquencies,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE party_cust_id = party.party_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id
AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
AND uwq_status = 'PENDING')))
)
active_delinquencies,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE party_cust_id = party.party_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id
AND(uwq_status = 'COMPLETE'
AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
)
complete_delinquencies,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_delinquencies_all
WHERE party_cust_id = party.party_id
AND status IN('DELINQUENT', 'PREDELINQUENT')
AND org_id = trx_summ.org_id
AND(uwq_status = 'PENDING'
AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
)
pending_delinquencies,
(SELECT a.score_value
FROM iex_score_histories a
WHERE a.creation_date =
(SELECT MAX(creation_date)
FROM iex_score_histories
WHERE score_object_code = 'PARTY'
AND score_object_id = party.party_id)
AND rownum < 2
AND a.score_object_code = 'PARTY'
AND a.score_object_id = party.party_id)
score,
party.address1 address1,
party.city city,
party.state state,
party.county county,
fnd_terr.territory_short_name country,
party.province province,
party.postal_code postal_code,
phone.phone_country_code phone_country_code,
phone.phone_area_code phone_area_code,
phone.phone_number phone_number,
phone.phone_extension phone_extension,
(SELECT COUNT(1) FROM iex_bankruptcies bkr WHERE bkr.party_id = party.party_id and NVL(BKR.DISPOSITION_CODE,'GRANTED') in ('GRANTED','NEGOTIATION')) number_of_bankruptcies, -- Changed for bug#7693986
iex_uwq_view_pkg.get_pro_count(party.party_id, NULL, NULL, NULL, trx_summ.org_id) number_of_promises,
iex_uwq_view_pkg.get_broken_prm_amt(party.party_id, NULL, NULL, trx_summ.org_id) broken_promise_amount,
iex_uwq_view_pkg.get_prm_amt(party.party_id, NULL, NULL, trx_summ.org_id) promise_amount,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_promise_details pd,
hz_cust_accounts b
WHERE b.party_id = party.party_id
AND pd.cust_account_id = b.cust_account_id
AND 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')))
)
active_promises,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_promise_details pd,
hz_cust_accounts b
WHERE b.party_id = party.party_id
AND pd.cust_account_id = b.cust_account_id
AND pd.state = 'BROKEN_PROMISE'
AND(pd.uwq_status = 'COMPLETE'
AND(TRUNC(pd.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
)
complete_promises,
(SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM iex_promise_details pd,
hz_cust_accounts b
WHERE b.party_id = party.party_id
AND pd.cust_account_id = b.cust_account_id
and pd.state = 'BROKEN_PROMISE'
AND(pd.uwq_status = 'PENDING'
AND(TRUNC(pd.uwq_active_date) > TRUNC(sysdate))))
)
pending_promises
FROM ar_trx_bal_summary trx_summ,
hz_cust_accounts acc,
hz_parties party,
jtf_objects_b objb,
hz_contact_points phone,
fnd_territories_tl fnd_terr,
hz_customer_profiles prf,
ar_collectors ac,
GL_SETS_OF_BOOKS gl,
AR_SYSTEM_PARAMETERS_all sys
WHERE trx_summ.reference_1 = '1'
AND trx_summ.cust_account_id = acc.cust_account_id
AND acc.party_id = party.party_id
AND objb.object_code = 'IEX_CUSTOMER'
AND party.country = fnd_terr.territory_code(+)
AND fnd_terr.LANGUAGE(+) = userenv('LANG')
AND phone.owner_table_id(+) = party.party_id
AND phone.owner_table_name(+) = 'HZ_PARTIES'
AND phone.contact_point_type(+) = 'PHONE'
and phone.primary_by_purpose(+) = 'Y'
AND phone.contact_point_purpose(+) = 'COLLECTIONS'
AND phone.phone_line_type(+) NOT IN('PAGER', 'FAX')
AND phone.status(+) = 'A'
AND nvl(phone.do_not_use_flag(+), 'N') = 'N'
AND acc.party_id = prf.party_id
and prf.CUST_ACCOUNT_ID = -1
-- and prf.CUST_ACCOUNT_ID = trx_summ.CUST_ACCOUNT_ID
and prf.SITE_USE_ID is null
and ac.collector_id(+) = prf.collector_id
and gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
and trx_summ.org_id = sys.org_id
-- start bug 5762888 gnramasa 13-July-2007
/* and (trx_summ.cust_account_id, trx_summ.site_use_id, trx_summ.org_id) in */
/* changed for bug 5677415 by gnramasa on 27/11/2006 */
/* (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(sysdate)) */
/* (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(p_from_date)) */
and trunc(trx_summ.last_update_date) >= trunc(p_from_date)
-- end bug 5762888 gnramasa 13-July-2007
GROUP BY trx_summ.org_id,
objb.object_function,
objb.object_parameters,
party.party_id,
party.party_name,
party.address1,
party.city,
party.state,
party.county,
fnd_terr.territory_short_name,
party.province,
party.postal_code,
phone.phone_country_code,
phone.phone_area_code,
phone.phone_number,
phone.phone_extension;
select strat.jtf_object_id,
wkitem.WORK_ITEM_ID,
wkitem.schedule_start schedule_start,
wkitem.schedule_end schedule_end,
stry_temp_wkitem.category_type category,
stry_temp_wkitem.WORK_TYPE,
stry_temp_wkitem.PRIORITY_TYPE,
wkitem.resource_id,
wkitem.strategy_id,
strat.strategy_template_id,
wkitem.work_item_template_id,
wkitem.status_code,
wkitem.creation_date start_time,
wkitem.execute_end end_time, -- snuthala 28/08/2008 bug #6745580
wkitem.work_item_order wkitem_order,
wkitem.escalated_yn --Added for bug#6981126 by schekuri on 27-Jul-2008
from iex_strategies strat,
iex_strategy_work_items wkitem,
iex_stry_temp_work_items_b stry_temp_wkitem,
IEX_DLN_UWQ_SUMMARY sum
where strat.jtf_object_type = decode(p_level, 'CUSTOMER', 'PARTY', 'ACCOUNT', 'IEX_ACCOUNT', 'BILL_TO', 'IEX_BILLTO')
AND strat.status_code IN('OPEN', 'ONHOLD')
AND wkitem.strategy_id = strat.strategy_id
AND wkitem.status_code IN('OPEN', 'ONHOLD')
AND wkitem.work_item_template_id = stry_temp_wkitem.work_item_temp_id
AND strat.jtf_object_id = decode(p_level, 'CUSTOMER', sum.PARTY_ID, 'ACCOUNT', sum.CUST_ACCOUNT_ID, 'BILL_TO', sum.SITE_USE_ID)
AND trunc(sum.LAST_UPDATE_DATE) = trunc(sysdate);
SELECT
DISTINCT
ar.resource_id,
ar.resource_type,
ar.collector_id
FROM
ar_collectors ar,
iex_dln_uwq_summary ids
WHERE
ar.collector_id = ids.collector_id
AND ar.resource_id is NOT NULL
AND ar.resource_id <> ids.collector_resource_id
AND trunc(ar.last_update_date) >= TRUNC(P_FROM_DATE);
SELECT
hp.collector_id,
ids.party_id,
ids.cust_account_id,
ids.site_use_id
FROM
hz_customer_profiles hp,
iex_dln_uwq_summary ids
WHERE
hp.party_id = ids.party_id
AND decode(hp.cust_account_id,null,1,hp.cust_account_id)
= decode(ids.cust_account_id,null,1,ids.cust_account_id)
AND decode(hp.site_use_id,null,1,hp.site_use_id)
= decode(ids.site_use_id,null,1,ids.site_use_id)
AND hp.collector_id <> ids.collector_id
AND trunc(hp.last_update_date) >= TRUNC(P_FROM_DATE);
SELECT
party.party_id,
party.address1 address1,
party.city city,
party.state state,
party.county county,
fnd_terr.territory_short_name country,
party.province province,
party.postal_code postal_code
FROM
hz_parties party,
iex_dln_uwq_summary ids,
fnd_territories_tl fnd_terr
WHERE
party.party_id = ids.party_id
AND party.country = fnd_terr.territory_code(+)
AND fnd_terr.LANGUAGE(+) = userenv('LANG')
AND trunc(party.last_update_date) >= TRUNC(P_FROM_DATE);
SELECT
ids.party_id party_id,
phone.phone_country_code phone_country_code,
phone.phone_area_code phone_area_code,
phone.phone_number phone_number,
phone.phone_extension phone_extension
FROM
hz_contact_points phone,
iex_dln_uwq_summary ids
WHERE
phone.owner_table_id = ids.party_id
AND phone.owner_table_name = 'HZ_PARTIES'
AND phone.contact_point_type = 'PHONE'
and phone.primary_by_purpose = 'Y'
AND phone.contact_point_purpose = 'COLLECTIONS'
AND phone.phone_line_type NOT IN('PAGER', 'FAX')
AND phone.status = 'A'
AND nvl(phone.do_not_use_flag, 'N') = 'N'
AND trunc(phone.last_update_date) >= TRUNC(P_FROM_DATE);
SELECT DISTINCT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS
WHERE ARS.REFERENCE_1 IS Null
AND EXISTS (SELECT 1 FROM IEX_DELINQUENCIES_ALL IED WHERE
IED.STATUS IN ('DELINQUENT', 'PREDELINQUENT')
AND ARS.CUST_ACCOUNT_ID = IED.CUST_ACCOUNT_ID);
SELECT DISTINCT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS
WHERE ARS.REFERENCE_1 = 1
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);
SELECT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS
WHERE ARS.REFERENCE_1 = 1
AND ARS.CUST_ACCOUNT_ID=P_CUST_ACCOUNT_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
) for update of reference_1 nowait;
SELECT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS
WHERE ARS.REFERENCE_1 IS Null
AND ARS.CUST_ACCOUNT_ID=P_CUST_ACCOUNT_ID
for update of reference_1 nowait;
L_LAST_UPDATE_DATE date_list;
L_LAST_UPDATED_BY number_list;
L_LAST_UPDATE_LOGIN number_list;
insert_conc_req;
l_cash := IEX_UTILITIES.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', 'SELECT DEFAULT_EXCHANGE_RATE_TYPE FROM AR_CMGT_SETUP_OPTIONS');
/* Begin Kasreeni 3/1/2007 Bug 5905023 We will update everytime instead of once */
if (p_mode = 'CP') then
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updating Reference_1 of AR_TRX_BAL_SUMMARY for Delinquent Customers');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'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 reference_1 is null;
l_cust_account_id_1.delete;
IEX_DEBUG_PUB.LOGMESSAGE(SQL%ROWCOUNT || ' Rows updated in ar_trx_bal_summary with reference_1 = 1');
l_cust_account_id_1.delete;
UPDATE AR_TRX_BAL_SUMMARY
SET REFERENCE_1 = '1'
WHERE cust_account_id=l_cust_account_id1
and REFERENCE_1 is null;
FND_FILE.PUT_LINE(FND_FILE.LOG,'updated records '||sql%rowcount);
IEX_DEBUG_PUB.LOGMESSAGE(l_cust_account_id_1.count || ' rows updated in ar_trx_bal_summary with reference_1 = 1');
l_cust_account_id_1.delete;
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Starting to update ar_trx_bal_summary with reference_1 = Null...');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'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)
and reference_1='1';
l_cust_account_id_n.delete;
LogMessage(FND_LOG.LEVEL_UNEXPECTED,SQL%ROWCOUNT || 'Rows updated in ar_trx_bal_summary with reference_1 = Null');
l_cust_account_id_n.delete;
UPDATE AR_TRX_BAL_SUMMARY
SET REFERENCE_1 = null
WHERE cust_account_id=l_cust_account_id1
and REFERENCE_1 = '1';
FND_FILE.PUT_LINE(FND_FILE.LOG,'updated records '||sql%rowcount);
IEX_DEBUG_PUB.LOGMESSAGE(l_cust_account_id_1.count || ' rows updated in ar_trx_bal_summary with reference_1 = 1');
l_cust_account_id_1.delete;
/*update ar_trx_bal_summary set reference_1 = '1'
where cust_account_id in
( select distinct cust_account_id
from iex_delinquencies_all
where status in ('DELINQUENT','PREDELINQUENT'));*/
/* End Kasreeni 3/1/2007 Bug 5905023 We will update everytime instead of once */
if (l_from_date is null and p_mode = 'CP' ) then --Bug5691098
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fully repopulating IEX_DLN_UWQ_SUMMARY table...');
delete from IEX_DLN_UWQ_SUMMARY where party_id in
(select party_id from ar_trx_bal_summary trb, hz_cust_accounts hza
where hza.cust_account_id = trb.cust_account_id and trunc(trb.LAST_UPDATE_DATE) >= trunc(l_from_date));
delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, org_id) in
(select cust_account_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date));
delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, site_use_id, org_id) in
(select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date));
/* delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, site_use_id, org_id) in
(select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date)); */
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No of rows deleted: ' || SQL%ROWCOUNT);
LogMessage(FND_LOG.LEVEL_STATEMENT,'Deleted rows that will be repopulated');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'1. Fetching and inserting data into the table...');
L_ORG_ID.delete;
L_COLLECTOR_ID.delete;
L_COLLECTOR_RESOURCE_ID.delete;
L_COLLECTOR_RES_TYPE.delete;
L_IEU_OBJECT_FUNCTION.delete;
L_IEU_OBJECT_PARAMETERS.delete;
L_IEU_MEDIA_TYPE_UUID.delete;
L_IEU_PARAM_PK_COL.delete;
L_IEU_PARAM_PK_VALUE.delete;
L_RESOURCE_ID.delete;
L_RESOURCE_TYPE.delete;
L_PARTY_ID.delete;
L_PARTY_NAME.delete;
L_CUST_ACCOUNT_ID.delete;
L_ACCOUNT_NAME.delete;
L_ACCOUNT_NUMBER.delete;
L_SITE_USE_ID.delete;
L_LOCATION.delete;
L_CURRENCY.delete;
L_OP_INVOICES_COUNT.delete;
L_OP_DEBIT_MEMOS_COUNT.delete;
L_OP_DEPOSITS_COUNT.delete;
L_OP_BILLS_RECEIVABLES_COUNT.delete;
L_OP_CHARGEBACK_COUNT.delete;
L_OP_CREDIT_MEMOS_COUNT.delete;
L_UNRESOLVED_CASH_COUNT.delete;
L_DISPUTED_INV_COUNT.delete;
L_BEST_CURRENT_RECEIVABLES.delete;
L_OP_INVOICES_VALUE.delete;
L_OP_DEBIT_MEMOS_VALUE.delete;
L_OP_DEPOSITS_VALUE.delete;
L_OP_BILLS_RECEIVABLES_VALUE.delete;
L_OP_CHARGEBACK_VALUE.delete;
L_OP_CREDIT_MEMOS_VALUE.delete;
L_UNRESOLVED_CASH_VALUE.delete;
L_RECEIPTS_AT_RISK_VALUE.delete;
L_INV_AMT_IN_DISPUTE.delete;
L_PENDING_ADJ_VALUE.delete;
L_PAST_DUE_INV_VALUE.delete;
L_PAST_DUE_INV_INST_COUNT.delete;
L_LAST_PAYMENT_DATE.delete;
L_LAST_PAYMENT_AMOUNT.delete;
L_LAST_PAYMENT_AMOUNT_CURR.delete;
L_LAST_PAYMENT_NUMBER.delete;
L_LAST_UPDATE_DATE.delete;
L_LAST_UPDATED_BY.delete;
L_CREATION_DATE.delete;
L_CREATED_BY.delete;
L_LAST_UPDATE_LOGIN.delete;
L_NUMBER_OF_DELINQUENCIES.delete;
L_ACTIVE_DELINQUENCIES.delete;
L_COMPLETE_DELINQUENCIES.delete;
L_PENDING_DELINQUENCIES.delete;
L_SCORE.delete;
L_ADDRESS1.delete;
L_CITY.delete;
L_STATE.delete;
L_COUNTY.delete;
L_COUNTRY.delete;
L_PROVINCE.delete;
L_POSTAL_CODE.delete;
L_PHONE_COUNTRY_CODE.delete;
L_PHONE_AREA_CODE.delete;
L_PHONE_NUMBER.delete;
L_PHONE_EXTENSION.delete;
L_NUMBER_OF_BANKRUPTCIES.delete;
L_NUMBER_OF_PROMISES.delete;
L_BROKEN_PROMISE_AMOUNT.delete;
L_PROMISE_AMOUNT.delete;
L_ACTIVE_PROMISES.delete;
L_COMPLETE_PROMISES.delete;
L_PENDING_PROMISES.delete;
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_LOGIN,
L_NUMBER_OF_DELINQUENCIES,
L_ACTIVE_DELINQUENCIES,
L_COMPLETE_DELINQUENCIES,
L_PENDING_DELINQUENCIES,
L_SCORE,
L_ADDRESS1,
L_CITY,
L_STATE,
L_COUNTY,
L_COUNTRY,
L_PROVINCE,
L_POSTAL_CODE,
L_PHONE_COUNTRY_CODE,
L_PHONE_AREA_CODE,
L_PHONE_NUMBER,
L_PHONE_EXTENSION,
L_NUMBER_OF_BANKRUPTCIES,
L_NUMBER_OF_PROMISES,
L_BROKEN_PROMISE_AMOUNT,
L_PROMISE_AMOUNT,
L_ACTIVE_PROMISES,
L_COMPLETE_PROMISES,
L_PENDING_PROMISES
limit l_max_fetches;
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_LOGIN,
L_NUMBER_OF_DELINQUENCIES,
L_ACTIVE_DELINQUENCIES,
L_COMPLETE_DELINQUENCIES,
L_PENDING_DELINQUENCIES,
L_SCORE,
L_ADDRESS1,
L_CITY,
L_STATE,
L_COUNTY,
L_COUNTRY,
L_PROVINCE,
L_POSTAL_CODE,
L_PHONE_COUNTRY_CODE,
L_PHONE_AREA_CODE,
L_PHONE_NUMBER,
L_PHONE_EXTENSION,
L_NUMBER_OF_BANKRUPTCIES,
L_NUMBER_OF_PROMISES,
L_BROKEN_PROMISE_AMOUNT,
L_PROMISE_AMOUNT,
L_ACTIVE_PROMISES,
L_COMPLETE_PROMISES,
L_PENDING_PROMISES
limit l_max_fetches;
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_LOGIN,
L_NUMBER_OF_DELINQUENCIES,
L_ACTIVE_DELINQUENCIES,
L_COMPLETE_DELINQUENCIES,
L_PENDING_DELINQUENCIES,
L_SCORE,
L_ADDRESS1,
L_CITY,
L_STATE,
L_COUNTY,
L_COUNTRY,
L_PROVINCE,
L_POSTAL_CODE,
L_PHONE_COUNTRY_CODE,
L_PHONE_AREA_CODE,
L_PHONE_NUMBER,
L_PHONE_EXTENSION,
L_NUMBER_OF_BANKRUPTCIES,
L_NUMBER_OF_PROMISES,
L_BROKEN_PROMISE_AMOUNT,
L_PROMISE_AMOUNT,
L_ACTIVE_PROMISES,
L_COMPLETE_PROMISES,
L_PENDING_PROMISES
limit l_max_fetches;
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_LOGIN,
L_NUMBER_OF_DELINQUENCIES,
L_ACTIVE_DELINQUENCIES,
L_COMPLETE_DELINQUENCIES,
L_PENDING_DELINQUENCIES,
L_SCORE,
L_ADDRESS1,
L_CITY,
L_STATE,
L_COUNTY,
L_COUNTRY,
L_PROVINCE,
L_POSTAL_CODE,
L_PHONE_COUNTRY_CODE,
L_PHONE_AREA_CODE,
L_PHONE_NUMBER,
L_PHONE_EXTENSION,
L_NUMBER_OF_BANKRUPTCIES,
L_NUMBER_OF_PROMISES,
L_BROKEN_PROMISE_AMOUNT,
L_PROMISE_AMOUNT,
L_ACTIVE_PROMISES,
L_COMPLETE_PROMISES,
L_PENDING_PROMISES
limit l_max_fetches;
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_LOGIN,
L_NUMBER_OF_DELINQUENCIES,
L_ACTIVE_DELINQUENCIES,
L_COMPLETE_DELINQUENCIES,
L_PENDING_DELINQUENCIES,
L_SCORE,
L_ADDRESS1,
L_CITY,
L_STATE,
L_COUNTY,
L_COUNTRY,
L_PROVINCE,
L_POSTAL_CODE,
L_PHONE_COUNTRY_CODE,
L_PHONE_AREA_CODE,
L_PHONE_NUMBER,
L_PHONE_EXTENSION,
L_NUMBER_OF_BANKRUPTCIES,
L_NUMBER_OF_PROMISES,
L_BROKEN_PROMISE_AMOUNT,
L_PROMISE_AMOUNT,
L_ACTIVE_PROMISES,
L_COMPLETE_PROMISES,
L_PENDING_PROMISES
limit l_max_fetches;
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_LOGIN,
L_NUMBER_OF_DELINQUENCIES,
L_ACTIVE_DELINQUENCIES,
L_COMPLETE_DELINQUENCIES,
L_PENDING_DELINQUENCIES,
L_SCORE,
L_ADDRESS1,
L_CITY,
L_STATE,
L_COUNTY,
L_COUNTRY,
L_PROVINCE,
L_POSTAL_CODE,
L_PHONE_COUNTRY_CODE,
L_PHONE_AREA_CODE,
L_PHONE_NUMBER,
L_PHONE_EXTENSION,
L_NUMBER_OF_BANKRUPTCIES,
L_NUMBER_OF_PROMISES,
L_BROKEN_PROMISE_AMOUNT,
L_PROMISE_AMOUNT,
L_ACTIVE_PROMISES,
L_COMPLETE_PROMISES,
L_PENDING_PROMISES
limit l_max_fetches;
LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
INSERT INTO IEX_DLN_UWQ_SUMMARY
(DLN_UWQ_SUMMARY_ID
,ORG_ID
,COLLECTOR_ID
,COLLECTOR_RESOURCE_ID
,COLLECTOR_RES_TYPE
,IEU_OBJECT_FUNCTION
,IEU_OBJECT_PARAMETERS
,IEU_MEDIA_TYPE_UUID
,IEU_PARAM_PK_COL
,IEU_PARAM_PK_VALUE
,RESOURCE_ID
,RESOURCE_TYPE
,PARTY_ID
,PARTY_NAME
,CUST_ACCOUNT_ID
,ACCOUNT_NAME
,ACCOUNT_NUMBER
,SITE_USE_ID
,LOCATION
,CURRENCY
,OP_INVOICES_COUNT
,OP_DEBIT_MEMOS_COUNT
,OP_DEPOSITS_COUNT
,OP_BILLS_RECEIVABLES_COUNT
,OP_CHARGEBACK_COUNT
,OP_CREDIT_MEMOS_COUNT
,UNRESOLVED_CASH_COUNT
,DISPUTED_INV_COUNT
,BEST_CURRENT_RECEIVABLES
,OP_INVOICES_VALUE
,OP_DEBIT_MEMOS_VALUE
,OP_DEPOSITS_VALUE
,OP_BILLS_RECEIVABLES_VALUE
,OP_CHARGEBACK_VALUE
,OP_CREDIT_MEMOS_VALUE
,UNRESOLVED_CASH_VALUE
,RECEIPTS_AT_RISK_VALUE
,INV_AMT_IN_DISPUTE
,PENDING_ADJ_VALUE
,PAST_DUE_INV_VALUE
,PAST_DUE_INV_INST_COUNT
,LAST_PAYMENT_DATE
,LAST_PAYMENT_AMOUNT
,LAST_PAYMENT_AMOUNT_CURR
,LAST_PAYMENT_NUMBER
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,NUMBER_OF_DELINQUENCIES
,ACTIVE_DELINQUENCIES
,COMPLETE_DELINQUENCIES
,PENDING_DELINQUENCIES
,SCORE
,ADDRESS1
,CITY
,STATE
,COUNTY
,COUNTRY
,PROVINCE
,POSTAL_CODE
,PHONE_COUNTRY_CODE
,PHONE_AREA_CODE
,PHONE_NUMBER
,PHONE_EXTENSION
,NUMBER_OF_BANKRUPTCIES
,NUMBER_OF_PROMISES
,BROKEN_PROMISE_AMOUNT
,PROMISE_AMOUNT
,ACTIVE_PROMISES
,COMPLETE_PROMISES
,PENDING_PROMISES)
VALUES
(IEX_DLN_UWQ_SUMMARY_S.nextval,
L_ORG_ID(i),
L_COLLECTOR_ID(i),
L_COLLECTOR_RESOURCE_ID(i),
L_COLLECTOR_RES_TYPE(i),
L_IEU_OBJECT_FUNCTION(i),
L_IEU_OBJECT_PARAMETERS(i),
L_IEU_MEDIA_TYPE_UUID(i),
L_IEU_PARAM_PK_COL(i),
L_IEU_PARAM_PK_VALUE(i),
L_RESOURCE_ID(i),
L_RESOURCE_TYPE(i),
L_PARTY_ID(i),
L_PARTY_NAME(i),
L_CUST_ACCOUNT_ID(i),
L_ACCOUNT_NAME(i),
L_ACCOUNT_NUMBER(i),
L_SITE_USE_ID(i),
L_LOCATION(i),
L_CURRENCY(i),
L_OP_INVOICES_COUNT(i),
L_OP_DEBIT_MEMOS_COUNT(i),
L_OP_DEPOSITS_COUNT(i),
L_OP_BILLS_RECEIVABLES_COUNT(i),
L_OP_CHARGEBACK_COUNT(i),
L_OP_CREDIT_MEMOS_COUNT(i),
L_UNRESOLVED_CASH_COUNT(i),
L_DISPUTED_INV_COUNT(i),
L_BEST_CURRENT_RECEIVABLES(i),
L_OP_INVOICES_VALUE(i),
L_OP_DEBIT_MEMOS_VALUE(i),
L_OP_DEPOSITS_VALUE(i),
L_OP_BILLS_RECEIVABLES_VALUE(i),
L_OP_CHARGEBACK_VALUE(i),
L_OP_CREDIT_MEMOS_VALUE(i),
L_UNRESOLVED_CASH_VALUE(i),
L_RECEIPTS_AT_RISK_VALUE(i),
L_INV_AMT_IN_DISPUTE(i),
L_PENDING_ADJ_VALUE(i),
L_PAST_DUE_INV_VALUE(i),
L_PAST_DUE_INV_INST_COUNT(i),
L_LAST_PAYMENT_DATE(i),
L_LAST_PAYMENT_AMOUNT(i),
L_LAST_PAYMENT_AMOUNT_CURR(i),
L_LAST_PAYMENT_NUMBER(i),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID,
L_NUMBER_OF_DELINQUENCIES(i),
L_ACTIVE_DELINQUENCIES(i),
L_COMPLETE_DELINQUENCIES(i),
L_PENDING_DELINQUENCIES(i),
L_SCORE(i),
L_ADDRESS1(i),
L_CITY(i),
L_STATE(i),
L_COUNTY(i),
L_COUNTRY(i),
L_PROVINCE(i),
L_POSTAL_CODE(i),
L_PHONE_COUNTRY_CODE(i),
L_PHONE_AREA_CODE(i),
L_PHONE_NUMBER(i),
L_PHONE_EXTENSION(i),
L_NUMBER_OF_BANKRUPTCIES(i),
L_NUMBER_OF_PROMISES(i),
L_BROKEN_PROMISE_AMOUNT(i),
L_PROMISE_AMOUNT(i),
L_ACTIVE_PROMISES(i),
L_COMPLETE_PROMISES(i),
L_PENDING_PROMISES(i));
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Inserted ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Total inserted ' || l_total || ' rows');
L_JTF_OBJECT_ID.delete;
L_WORK_ITEM_ID.delete;
L_SCHEDULE_START.delete;
L_SCHEDULE_END.delete;
L_WORK_TYPE.delete;
L_CATEGORY_TYPE.delete;
L_PRIORITY_TYPE.delete;
L_wkitem_RESOURCE_ID.delete; --schekuri
L_STRATEGY_ID.delete;
L_STRATEGY_TEMPLATE_ID.delete;
L_WORK_ITEM_TEMPLATE_ID.delete;
L_STATUS_CODE.delete;
L_START_TIME.delete;
L_END_TIME.delete;
L_WORK_ITEM_ORDER.delete;
L_ESCALATED_YN.delete; --Added for bug#6981126 by schekuri on 27-Jun-2008
UPDATE IEX_DLN_UWQ_SUMMARY
SET WORK_ITEM_ID = L_WORK_ITEM_ID(i),
SCHEDULE_START = L_SCHEDULE_START(i),
SCHEDULE_END = L_SCHEDULE_END(i),
WORK_TYPE = L_WORK_TYPE(i),
CATEGORY_TYPE = L_CATEGORY_TYPE(i),
PRIORITY_TYPE = L_PRIORITY_TYPE(i),
WKITEM_RESOURCE_ID = L_WKITEM_RESOURCE_ID(i), --schekuri
STRATEGY_ID = L_STRATEGY_ID(i),
STRATEGY_TEMPLATE_ID = L_STRATEGY_TEMPLATE_ID(i),
WORK_ITEM_TEMPLATE_ID = L_WORK_ITEM_TEMPLATE_ID(i),
STATUS_CODE = L_STATUS_CODE(i),
START_TIME = L_START_TIME(i),
END_TIME = L_END_TIME(i),
WORK_ITEM_ORDER = L_WORK_ITEM_ORDER(i),
WKITEM_ESCALATED_YN = L_ESCALATED_YN(i) --Added for bug#6981126 by schekuri on 27-Jun-2008
WHERE PARTY_ID = L_JTF_OBJECT_ID(i);
UPDATE IEX_DLN_UWQ_SUMMARY
SET WORK_ITEM_ID = L_WORK_ITEM_ID(i),
SCHEDULE_START = L_SCHEDULE_START(i),
SCHEDULE_END = L_SCHEDULE_END(i),
WORK_TYPE = L_WORK_TYPE(i),
CATEGORY_TYPE = L_CATEGORY_TYPE(i),
PRIORITY_TYPE = L_PRIORITY_TYPE(i),
WKITEM_RESOURCE_ID = L_WKITEM_RESOURCE_ID(i), --schekuri
STRATEGY_ID = L_STRATEGY_ID(i),
STRATEGY_TEMPLATE_ID = L_STRATEGY_TEMPLATE_ID(i),
WORK_ITEM_TEMPLATE_ID = L_WORK_ITEM_TEMPLATE_ID(i),
STATUS_CODE = L_STATUS_CODE(i),
START_TIME = L_START_TIME(i),
END_TIME = L_END_TIME(i),
WORK_ITEM_ORDER = L_WORK_ITEM_ORDER(i),
WKITEM_ESCALATED_YN = L_ESCALATED_YN(i) --Added for bug#6981126 by schekuri on 27-Jun-2008
WHERE CUST_ACCOUNT_ID = L_JTF_OBJECT_ID(i);
UPDATE IEX_DLN_UWQ_SUMMARY
SET WORK_ITEM_ID = L_WORK_ITEM_ID(i),
SCHEDULE_START = L_SCHEDULE_START(i),
SCHEDULE_END = L_SCHEDULE_END(i),
WORK_TYPE = L_WORK_TYPE(i),
CATEGORY_TYPE = L_CATEGORY_TYPE(i),
PRIORITY_TYPE = L_PRIORITY_TYPE(i),
WKITEM_RESOURCE_ID = L_WKITEM_RESOURCE_ID(i), --schekuri
STRATEGY_ID = L_STRATEGY_ID(i),
STRATEGY_TEMPLATE_ID = L_STRATEGY_TEMPLATE_ID(i),
WORK_ITEM_TEMPLATE_ID = L_WORK_ITEM_TEMPLATE_ID(i),
STATUS_CODE = L_STATUS_CODE(i),
START_TIME = L_START_TIME(i),
END_TIME = L_END_TIME(i),
WORK_ITEM_ORDER = L_WORK_ITEM_ORDER(i),
WKITEM_ESCALATED_YN = L_ESCALATED_YN(i) --Added for bug#6981126 by schekuri on 27-Jun-2008
WHERE SITE_USE_ID = L_JTF_OBJECT_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updated ' || L_JTF_OBJECT_ID.COUNT || ' rows');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Total updated ' || l_total || ' rows with strategy info');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'TCA Update Started at : ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
L_PARTY_ID.delete;
L_ADDRESS1.delete;
L_CITY.delete;
L_STATE.delete;
L_COUNTY.delete;
L_COUNTRY.delete;
L_PROVINCE.delete;
L_POSTAL_CODE.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET address1 = L_ADDRESS1(i),
city = L_CITY(i),
state = L_STATE(i),
county = L_COUNTY(i),
country = L_COUNTRY(i),
province = L_PROVINCE(i),
postal_code = L_POSTAL_CODE(i),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE PARTY_ID = L_PARTY_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Changed_Party Cursor updated ' ||L_PARTY_ID.count || ' rows ');
L_PARTY_ID.delete;
L_CUST_ACCOUNT_ID.delete;
L_SITE_USE_ID.delete;
L_COLLECTOR_ID.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET COLLECTOR_ID = L_COLLECTOR_ID(i),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE
PARTY_ID = L_PARTY_ID(i)
AND nvl(CUST_ACCOUNT_ID,1) = nvl(L_CUST_ACCOUNT_ID(i),1)
AND nvl(SITE_USE_ID,1) = nvl(L_SITE_USE_ID(i),1);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Changed_profiles updated ' || L_PARTY_ID.count || ' rows ' );
L_COLLECTOR_RESOURCE_ID.delete ;
L_COLLECTOR_ID.delete;
L_RESOURCE_TYPE.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET COLLECTOR_RESOURCE_ID = L_COLLECTOR_RESOURCE_ID(i),
COLLECTOR_RES_TYPE = L_RESOURCE_TYPE(i),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE
COLLECTOR_ID = L_COLLECTOR_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Changed_collector updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Changed Collector update received' || SQLERRM);
L_PARTY_ID.delete;
L_PHONE_COUNTRY_CODE.delete;
L_PHONE_AREA_CODE.delete;
L_PHONE_NUMBER.delete;
L_PHONE_EXTENSION.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET PHONE_COUNTRY_CODE = L_PHONE_COUNTRY_CODE(i),
PHONE_AREA_CODE = L_PHONE_AREA_CODE(i),
PHONE_NUMBER = L_PHONE_NUMBER(i),
PHONE_EXTENSION = L_PHONE_EXTENSION(i),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE PARTY_ID = L_PARTY_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Changed_contact Cursor updated ' ||L_PARTY_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'TCA Update Finished at : ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
DELETE from AR_CONC_PROCESS_REQUESTS
WHERE REQUEST_ID = FND_GLOBAL.conc_request_id;
DELETE from AR_CONC_PROCESS_REQUESTS
where REQUEST_ID = FND_GLOBAL.conc_request_id;
DELETE from AR_CONC_PROCESS_REQUESTS
where REQUEST_ID = FND_GLOBAL.conc_request_id;
SELECT
trx_summ.org_id,
objb.object_function ieu_object_function,
objb.object_parameters || ' DISPLAYCBO=IEXTRMAN' ieu_object_parameters,
'' ieu_media_type_uuid,
'CUSTOMER_SITE_USE_ID' ieu_param_pk_col,
to_char(trx_summ.site_use_id) ieu_param_pk_value,
1 resource_id,
'RS_EMPLOYEE' resource_type,
party.party_id party_id,
party.party_name party_name,
trx_summ.cust_account_id cust_account_id,
acc.account_name account_name,
acc.account_number account_number,
trx_summ.site_use_id site_use_id,
site_uses.location location,
max(gl.CURRENCY_CODE) currency,
SUM(trx_summ.op_invoices_count) op_invoices_count,
SUM(trx_summ.op_debit_memos_count) op_debit_memos_count,
SUM(trx_summ.op_deposits_count) op_deposits_count,
SUM(trx_summ.op_bills_receivables_count) op_bills_receivables_count,
SUM(trx_summ.op_chargeback_count) op_chargeback_count,
SUM(trx_summ.op_credit_memos_count) op_credit_memos_count,
SUM(trx_summ.unresolved_cash_count) unresolved_cash_count,
SUM(trx_summ.disputed_inv_count) disputed_inv_count,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.best_current_receivables,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.best_current_receivables))) best_current_receivables,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_invoices_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_invoices_value))) op_invoices_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_debit_memos_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_debit_memos_value))) op_debit_memos_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_deposits_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_deposits_value))) op_deposits_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_bills_receivables_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_bills_receivables_value))) op_bills_receivables_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_chargeback_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_chargeback_value))) op_chargeback_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_credit_memos_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_credit_memos_value))) op_credit_memos_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.unresolved_cash_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.unresolved_cash_value))) unresolved_cash_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.receipts_at_risk_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.receipts_at_risk_value))) receipts_at_risk_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.inv_amt_in_dispute,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.inv_amt_in_dispute))) inv_amt_in_dispute,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
SUM(trx_summ.past_due_inv_inst_count) past_due_inv_inst_count,
MAX(trx_summ.last_payment_date) last_payment_date,
--MAX(iex_uwq_view_pkg.get_last_payment_amount(0, 0, trx_summ.site_use_id)) last_payment_amount,
max(gl.CURRENCY_CODE) last_payment_amount_curr,
-- MAX(iex_uwq_view_pkg.get_last_payment_number(0, 0, trx_summ.site_use_id)) last_payment_number,
MAX(trx_summ.last_update_date) last_update_date,
MAX(trx_summ.last_updated_by) last_updated_by,
MAX(trx_summ.creation_date) creation_date,
MAX(trx_summ.created_by) created_by,
MAX(trx_summ.last_update_login) last_update_login,
party.address1 address1,
party.city city,
party.state state,
party.county county,
fnd_terr.territory_short_name country,
party.province province,
party.postal_code postal_code
FROM ar_trx_bal_summary trx_summ,
hz_cust_accounts acc,
hz_parties party,
jtf_objects_b objb,
fnd_territories_tl fnd_terr,
hz_cust_site_uses_all site_uses,
GL_SETS_OF_BOOKS gl,
AR_SYSTEM_PARAMETERS_all sys
WHERE trx_summ.reference_1 = '1'
AND trx_summ.site_use_id = site_uses.site_use_id
AND trx_summ.cust_account_id = acc.cust_account_id
AND acc.party_id = party.party_id
AND objb.object_code = 'IEX_BILLTO'
AND party.country = fnd_terr.territory_code(+)
AND fnd_terr.LANGUAGE(+) = userenv('LANG')
and gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
and trx_summ.org_id = sys.org_id
and trx_summ.site_use_id in (select temp.object_id from iex_pop_uwq_summ_gt temp where
temp.org_id=trx_summ.org_id)
GROUP BY trx_summ.org_id,
objb.object_function,
objb.object_parameters,
party.party_id,
party.party_name,
trx_summ.cust_account_id,
acc.account_name,
acc.account_number,
trx_summ.site_use_id,
site_uses.location,
party.address1,
party.city,
party.state,
party.county,
fnd_terr.territory_short_name,
party.province,
party.postal_code;
select strat.jtf_object_id,
wkitem.WORK_ITEM_ID,
wkitem.schedule_start schedule_start,
wkitem.schedule_end schedule_end,
stry_temp_wkitem.category_type category,
stry_temp_wkitem.WORK_TYPE,
stry_temp_wkitem.PRIORITY_TYPE,
wkitem.resource_id,
wkitem.strategy_id,
strat.strategy_template_id,
wkitem.work_item_template_id,
wkitem.status_code,
wkitem.creation_date start_time,
wkitem.execute_end end_time, -- snuthala 28/08/2008 bug #6745580
wkitem.work_item_order wkitem_order,
wkitem.escalated_yn --Added for bug#6981126 by schekuri on 27-Jul-2008
from iex_strategies strat,
iex_strategy_work_items wkitem,
iex_stry_temp_work_items_b stry_temp_wkitem,
iex_pop_uwq_summ_gt temp
where strat.jtf_object_type = temp.object_type
AND strat.status_code IN('OPEN', 'ONHOLD')
AND wkitem.strategy_id = strat.strategy_id
AND wkitem.status_code IN('OPEN', 'ONHOLD')
AND wkitem.work_item_template_id = stry_temp_wkitem.work_item_temp_id
AND strat.jtf_object_id = temp.object_id;
SELECT
hp.collector_id,
ac.resource_id,
ac.resource_type,
hp.party_id,
hp.cust_account_id,
hp.site_use_id
FROM
hz_customer_profiles hp,
ar_collectors ac,
iex_pop_uwq_summ_gt temp
WHERE
hp.site_use_id=temp.object_id
and hp.collector_id=ac.collector_id;
SELECT
ids.party_id party_id,
phone.phone_country_code phone_country_code,
phone.phone_area_code phone_area_code,
phone.phone_number phone_number,
phone.phone_extension phone_extension
FROM
hz_contact_points phone,
iex_dln_uwq_summary ids,
iex_pop_uwq_summ_gt temp
WHERE
phone.owner_table_id = ids.party_id
AND phone.owner_table_name = 'HZ_PARTIES'
AND phone.contact_point_type = 'PHONE'
and phone.primary_by_purpose = 'Y'
AND phone.contact_point_purpose = 'COLLECTIONS'
AND phone.phone_line_type NOT IN('PAGER', 'FAX')
AND phone.status = 'A'
AND nvl(phone.do_not_use_flag, 'N') = 'N'
AND ids.site_use_id = temp.object_id;
L_LAST_UPDATE_DATE date_list;
L_LAST_UPDATED_BY number_list;
L_LAST_UPDATE_LOGIN number_list;
select del.CUSTOMER_SITE_USE_ID,
count(1) number_of_delinquencies,
max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
from iex_delinquencies_all del,
iex_pop_uwq_summ_gt temp
WHERE del.customer_site_use_id = temp.object_id AND
del.org_id = temp.org_id and
del.status IN('DELINQUENT', 'PREDELINQUENT')
group by del.CUSTOMER_SITE_USE_ID;
select del.CUSTOMER_SITE_USE_ID,
max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
max(decode(pd.uwq_status,'COMPLETE',(decode(sign(TRUNC(pd.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_promises,
max(decode(pd.uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_promises
from iex_promise_details pd,
IEX_DELINQUENCIES_ALL DEL,
iex_pop_uwq_summ_gt temp
WHERE pd.cust_account_id = del.cust_account_id
and pd.delinquency_id = del.delinquency_id
and del.customer_site_use_id = temp.object_id
and del.org_id = temp.org_id
and pd.state = 'BROKEN_PROMISE'
group by del.CUSTOMER_SITE_USE_ID;
SELECT del.customer_site_use_id,
COUNT(1) number_of_promises,
SUM(amount_due_remaining) broken_promise_amount,
SUM(promise_amount) promise_amount
FROM iex_promise_details pd,
iex_delinquencies_all del,
iex_pop_uwq_summ_gt temp
WHERE pd.cust_account_id = del.cust_account_id
AND pd.delinquency_id = del.delinquency_id
AND pd.status IN('COLLECTABLE', 'PENDING')
AND pd.state = 'BROKEN_PROMISE'
AND pd.amount_due_remaining > 0
AND (del.status NOT IN('CURRENT', 'CLOSE')
or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
and del.customer_site_use_id = temp.object_id
and del.org_id = temp.org_id
GROUP BY del.customer_site_use_id;
SELECT sh.score_object_id, sh.score_value score
FROM iex_score_histories sh,
iex_pop_uwq_summ_gt temp
WHERE sh.creation_date = (SELECT MAX(creation_date)
FROM iex_score_histories sh1
WHERE sh1.score_object_code = 'IEX_BILLTO'
AND sh1.score_object_id = sh.score_object_id)
-- AND rownum < 2
AND sh.score_object_code = 'IEX_BILLTO'
AND sh.score_object_id = temp.object_id;
SELECT a.customer_site_use_id,
SUM(b.acctd_amount_due_remaining) past_due_inv_value
FROM iex_delinquencies_all a,
ar_payment_schedules_all b,
iex_pop_uwq_summ_gt temp
WHERE a.customer_site_use_id = temp.object_id
AND a.payment_schedule_id = b.payment_schedule_id
AND b.status = 'OP'
AND a.status IN('DELINQUENT', 'PREDELINQUENT')
AND temp.org_id = a.org_id
GROUP BY a.customer_site_use_id;
SELECT o_summ.site_use_id,
o_summ.last_payment_number last_payment_number,
iex_uwq_view_pkg.convert_amount(o_summ.last_payment_amount,o_summ.currency) last_payment_amount
FROM ar_trx_bal_summary o_summ
WHERE o_summ.site_use_id in (select object_id from iex_pop_uwq_summ_gt)
AND o_summ.last_payment_date = (SELECT MAX(last_payment_date)
FROM ar_trx_bal_summary
WHERE site_use_id = o_summ.site_use_id);
select sua.site_use_id,
COUNT(1) number_of_bankruptcies
FROM iex_bankruptcies bkr,hz_cust_accounts ca,
hz_cust_acct_sites cas,hz_cust_site_uses_all sua
where sua.site_use_id in (select object_id from iex_pop_uwq_summ_gt)
and bkr.party_id=ca.party_id
and ca.cust_account_id=cas.cust_account_id
and cas.cust_acct_site_id=sua.cust_acct_site_id
and NVL(BKR.DISPOSITION_CODE,'GRANTED') in ('GRANTED','NEGOTIATION') -- Changed for bug#7693986
group by sua.site_use_id;
select del.customer_site_use_id,
sum(decode(del.status,'DELINQUENT',1,'PREDELINQUENT',1,0)) number_of_delinquencies,
sum(decode(del.status,'DELINQUENT',ps.acctd_amount_due_remaining,'PREDELINQUENT',ps.acctd_amount_due_remaining,0)) past_due_inv_value,
max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies,
del.org_id org_id
from iex_delinquencies del,
ar_payment_schedules ps
WHERE del.payment_schedule_id = ps.payment_schedule_id AND
del.org_id = ps.org_id and
exists(select 1 from iex_delinquencies del1
where del1.last_update_date>=trunc(sysdate)
and del.customer_site_use_id=del1.customer_site_use_id
and del.org_id=del1.org_id)
group by del.customer_site_use_id, del.org_id;
L_SITE_USE_ID.delete;
L_NUMBER_OF_DELINQUENCIES.delete;
L_PENDING_DELINQUENCIES.delete;
L_COMPLETE_DELINQUENCIES.delete;
L_ACTIVE_DELINQUENCIES.delete;
L_PAST_DUE_INV_VALUE.delete;
L_ORG_ID.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET NUMBER_OF_DELINQUENCIES = L_NUMBER_OF_DELINQUENCIES(i),
PAST_DUE_INV_VALUE = L_PAST_DUE_INV_VALUE(i),
ACTIVE_DELINQUENCIES = L_ACTIVE_DELINQUENCIES(i),
COMPLETE_DELINQUENCIES = L_COMPLETE_DELINQUENCIES(i),
PENDING_DELINQUENCIES = L_PENDING_DELINQUENCIES(i)
WHERE
SITE_USE_ID = L_SITE_USE_ID(i)
AND ORG_ID=L_ORG_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_del_dln updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Delinquency update received' || SQLERRM);
insert into iex_pop_uwq_summ_gt(object_id,object_type,org_id)
select customer_site_use_id,'IEX_BILLTO',org_id from iex_delinquencies del
where status in ('DELINQUENT','PRE-DELINQUENT')
and not exists(select 1 from IEX_DLN_UWQ_SUMMARY dus where dus.site_use_id=del.customer_site_use_id and
dus.org_id=del.org_id)
group by customer_site_use_id,org_id;
L_ORG_ID.delete;
L_COLLECTOR_ID.delete;
L_COLLECTOR_RESOURCE_ID.delete;
L_COLLECTOR_RES_TYPE.delete;
L_IEU_OBJECT_FUNCTION.delete;
L_IEU_OBJECT_PARAMETERS.delete;
L_IEU_MEDIA_TYPE_UUID.delete;
L_IEU_PARAM_PK_COL.delete;
L_IEU_PARAM_PK_VALUE.delete;
L_RESOURCE_ID.delete;
L_RESOURCE_TYPE.delete;
L_PARTY_ID.delete;
L_PARTY_NAME.delete;
L_CUST_ACCOUNT_ID.delete;
L_ACCOUNT_NAME.delete;
L_ACCOUNT_NUMBER.delete;
L_SITE_USE_ID.delete;
L_LOCATION.delete;
L_CURRENCY.delete;
L_OP_INVOICES_COUNT.delete;
L_OP_DEBIT_MEMOS_COUNT.delete;
L_OP_DEPOSITS_COUNT.delete;
L_OP_BILLS_RECEIVABLES_COUNT.delete;
L_OP_CHARGEBACK_COUNT.delete;
L_OP_CREDIT_MEMOS_COUNT.delete;
L_UNRESOLVED_CASH_COUNT.delete;
L_DISPUTED_INV_COUNT.delete;
L_BEST_CURRENT_RECEIVABLES.delete;
L_OP_INVOICES_VALUE.delete;
L_OP_DEBIT_MEMOS_VALUE.delete;
L_OP_DEPOSITS_VALUE.delete;
L_OP_BILLS_RECEIVABLES_VALUE.delete;
L_OP_CHARGEBACK_VALUE.delete;
L_OP_CREDIT_MEMOS_VALUE.delete;
L_UNRESOLVED_CASH_VALUE.delete;
L_RECEIPTS_AT_RISK_VALUE.delete;
L_INV_AMT_IN_DISPUTE.delete;
L_PENDING_ADJ_VALUE.delete;
L_PAST_DUE_INV_VALUE.delete;
L_PAST_DUE_INV_INST_COUNT.delete;
L_LAST_PAYMENT_DATE.delete;
L_LAST_PAYMENT_AMOUNT.delete;
L_LAST_PAYMENT_AMOUNT_CURR.delete;
L_LAST_PAYMENT_NUMBER.delete;
L_LAST_UPDATE_DATE.delete;
L_LAST_UPDATED_BY.delete;
L_CREATION_DATE.delete;
L_CREATED_BY.delete;
L_LAST_UPDATE_LOGIN.delete;
L_NUMBER_OF_DELINQUENCIES.delete;
L_ACTIVE_DELINQUENCIES.delete;
L_COMPLETE_DELINQUENCIES.delete;
L_PENDING_DELINQUENCIES.delete;
L_SCORE.delete;
L_ADDRESS1.delete;
L_CITY.delete;
L_STATE.delete;
L_COUNTY.delete;
L_COUNTRY.delete;
L_PROVINCE.delete;
L_POSTAL_CODE.delete;
L_PHONE_COUNTRY_CODE.delete;
L_PHONE_AREA_CODE.delete;
L_PHONE_NUMBER.delete;
L_PHONE_EXTENSION.delete;
L_NUMBER_OF_BANKRUPTCIES.delete;
L_NUMBER_OF_PROMISES.delete;
L_BROKEN_PROMISE_AMOUNT.delete;
L_PROMISE_AMOUNT.delete;
L_ACTIVE_PROMISES.delete;
L_COMPLETE_PROMISES.delete;
L_PENDING_PROMISES.delete;
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_LOGIN,
L_ADDRESS1,
L_CITY,
L_STATE,
L_COUNTY,
L_COUNTRY,
L_PROVINCE,
L_POSTAL_CODE
limit l_max_fetches;
LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
INSERT INTO IEX_DLN_UWQ_SUMMARY
(DLN_UWQ_SUMMARY_ID
,ORG_ID
,IEU_OBJECT_FUNCTION
,IEU_OBJECT_PARAMETERS
,IEU_MEDIA_TYPE_UUID
,IEU_PARAM_PK_COL
,IEU_PARAM_PK_VALUE
,RESOURCE_ID
,RESOURCE_TYPE
,PARTY_ID
,PARTY_NAME
,CUST_ACCOUNT_ID
,ACCOUNT_NAME
,ACCOUNT_NUMBER
,SITE_USE_ID
,LOCATION
,CURRENCY
,OP_INVOICES_COUNT
,OP_DEBIT_MEMOS_COUNT
,OP_DEPOSITS_COUNT
,OP_BILLS_RECEIVABLES_COUNT
,OP_CHARGEBACK_COUNT
,OP_CREDIT_MEMOS_COUNT
,UNRESOLVED_CASH_COUNT
,DISPUTED_INV_COUNT
,BEST_CURRENT_RECEIVABLES
,OP_INVOICES_VALUE
,OP_DEBIT_MEMOS_VALUE
,OP_DEPOSITS_VALUE
,OP_BILLS_RECEIVABLES_VALUE
,OP_CHARGEBACK_VALUE
,OP_CREDIT_MEMOS_VALUE
,UNRESOLVED_CASH_VALUE
,RECEIPTS_AT_RISK_VALUE
,INV_AMT_IN_DISPUTE
,PENDING_ADJ_VALUE
,PAST_DUE_INV_INST_COUNT
,LAST_PAYMENT_DATE
,LAST_PAYMENT_AMOUNT_CURR
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ADDRESS1
,CITY
,STATE
,COUNTY
,COUNTRY
,PROVINCE
,POSTAL_CODE
,NUMBER_OF_DELINQUENCIES
,NUMBER_OF_PROMISES
,NUMBER_OF_BANKRUPTCIES)
VALUES
(IEX_DLN_UWQ_SUMMARY_S.nextval,
L_ORG_ID(i),
L_IEU_OBJECT_FUNCTION(i),
L_IEU_OBJECT_PARAMETERS(i),
L_IEU_MEDIA_TYPE_UUID(i),
L_IEU_PARAM_PK_COL(i),
L_IEU_PARAM_PK_VALUE(i),
L_RESOURCE_ID(i),
L_RESOURCE_TYPE(i),
L_PARTY_ID(i),
L_PARTY_NAME(i),
L_CUST_ACCOUNT_ID(i),
L_ACCOUNT_NAME(i),
L_ACCOUNT_NUMBER(i),
L_SITE_USE_ID(i),
L_LOCATION(i),
L_CURRENCY(i),
L_OP_INVOICES_COUNT(i),
L_OP_DEBIT_MEMOS_COUNT(i),
L_OP_DEPOSITS_COUNT(i),
L_OP_BILLS_RECEIVABLES_COUNT(i),
L_OP_CHARGEBACK_COUNT(i),
L_OP_CREDIT_MEMOS_COUNT(i),
L_UNRESOLVED_CASH_COUNT(i),
L_DISPUTED_INV_COUNT(i),
L_BEST_CURRENT_RECEIVABLES(i),
L_OP_INVOICES_VALUE(i),
L_OP_DEBIT_MEMOS_VALUE(i),
L_OP_DEPOSITS_VALUE(i),
L_OP_BILLS_RECEIVABLES_VALUE(i),
L_OP_CHARGEBACK_VALUE(i),
L_OP_CREDIT_MEMOS_VALUE(i),
L_UNRESOLVED_CASH_VALUE(i),
L_RECEIPTS_AT_RISK_VALUE(i),
L_INV_AMT_IN_DISPUTE(i),
L_PENDING_ADJ_VALUE(i),
L_PAST_DUE_INV_INST_COUNT(i),
L_LAST_PAYMENT_DATE(i),
L_LAST_PAYMENT_AMOUNT_CURR(i),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID,
L_ADDRESS1(i),
L_CITY(i),
L_STATE(i),
L_COUNTY(i),
L_COUNTRY(i),
L_PROVINCE(i),
L_POSTAL_CODE(i),
0,
0,
0);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Inserted ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows');
L_JTF_OBJECT_ID.delete;
L_WORK_ITEM_ID.delete;
L_SCHEDULE_START.delete;
L_SCHEDULE_END.delete;
L_WORK_TYPE.delete;
L_CATEGORY_TYPE.delete;
L_PRIORITY_TYPE.delete;
L_wkitem_RESOURCE_ID.delete;
L_STRATEGY_ID.delete;
L_STRATEGY_TEMPLATE_ID.delete;
L_WORK_ITEM_TEMPLATE_ID.delete;
L_STATUS_CODE.delete;
L_START_TIME.delete;
L_END_TIME.delete;
L_WORK_ITEM_ORDER.delete;
L_ESCALATED_YN.delete; --Added for bug#6981126 by schekuri on 27-Jun-2008
UPDATE IEX_DLN_UWQ_SUMMARY
SET WORK_ITEM_ID = L_WORK_ITEM_ID(i),
SCHEDULE_START = L_SCHEDULE_START(i),
SCHEDULE_END = L_SCHEDULE_END(i),
WORK_TYPE = L_WORK_TYPE(i),
CATEGORY_TYPE = L_CATEGORY_TYPE(i),
PRIORITY_TYPE = L_PRIORITY_TYPE(i),
WKITEM_RESOURCE_ID = L_WKITEM_RESOURCE_ID(i),
STRATEGY_ID = L_STRATEGY_ID(i),
STRATEGY_TEMPLATE_ID = L_STRATEGY_TEMPLATE_ID(i),
WORK_ITEM_TEMPLATE_ID = L_WORK_ITEM_TEMPLATE_ID(i),
STATUS_CODE = L_STATUS_CODE(i),
START_TIME = L_START_TIME(i),
END_TIME = L_END_TIME(i),
WORK_ITEM_ORDER = L_WORK_ITEM_ORDER(i),
WKITEM_ESCALATED_YN = L_ESCALATED_YN(i) --Added for bug#6981126 by schekuri on 27-Jun-2008
WHERE SITE_USE_ID = L_JTF_OBJECT_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updated ' || L_JTF_OBJECT_ID.COUNT || ' rows');
L_COLLECTOR_RESOURCE_ID.delete ;
L_COLLECTOR_ID.delete;
L_RESOURCE_TYPE.delete;
L_PARTY_ID.delete;
L_CUST_ACCOUNT_ID.delete;
L_SITE_USE_ID.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET COLLECTOR_RESOURCE_ID = L_COLLECTOR_RESOURCE_ID(i),
COLLECTOR_RES_TYPE = L_RESOURCE_TYPE(i),
collector_id = l_collector_id(i),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE
SITE_USE_ID = L_SITE_USE_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' C_COLLECTOR_PROF updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'C_COLLECTOR_PROF update received' || SQLERRM);
L_PARTY_ID.delete;
L_PHONE_COUNTRY_CODE.delete;
L_PHONE_AREA_CODE.delete;
L_PHONE_NUMBER.delete;
L_PHONE_EXTENSION.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET PHONE_COUNTRY_CODE = L_PHONE_COUNTRY_CODE(i),
PHONE_AREA_CODE = L_PHONE_AREA_CODE(i),
PHONE_NUMBER = L_PHONE_NUMBER(i),
PHONE_EXTENSION = L_PHONE_EXTENSION(i),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE PARTY_ID = L_PARTY_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_contact_point Cursor updated ' ||L_PARTY_ID.count || ' rows ');
L_SITE_USE_ID.delete;
L_NUMBER_OF_DELINQUENCIES.delete;
L_PENDING_DELINQUENCIES.delete;
L_COMPLETE_DELINQUENCIES.delete;
L_ACTIVE_DELINQUENCIES.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET NUMBER_OF_DELINQUENCIES = L_NUMBER_OF_DELINQUENCIES(i),
ACTIVE_DELINQUENCIES = L_ACTIVE_DELINQUENCIES(i),
COMPLETE_DELINQUENCIES = L_COMPLETE_DELINQUENCIES(i),
PENDING_DELINQUENCIES = L_PENDING_DELINQUENCIES(i)
WHERE
SITE_USE_ID = L_SITE_USE_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_del updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Delinquency update received' || SQLERRM);
L_SITE_USE_ID.delete;
L_ACTIVE_PROMISES.delete;
L_COMPLETE_PROMISES.delete;
L_PENDING_PROMISES.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET ACTIVE_PROMISES = L_ACTIVE_PROMISES(i),
COMPLETE_PROMISES = L_COMPLETE_PROMISES(i),
PENDING_PROMISES = L_PENDING_PROMISES(i)
WHERE
SITE_USE_ID = L_SITE_USE_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_pro updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Promise update received' || SQLERRM);
L_SITE_USE_ID.delete;
L_NUMBER_OF_PROMISES.delete;
L_BROKEN_PROMISE_AMOUNT .delete;
L_PROMISE_AMOUNT.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET NUMBER_OF_PROMISES = L_NUMBER_OF_PROMISES(i),
BROKEN_PROMISE_AMOUNT = L_BROKEN_PROMISE_AMOUNT(i),
PROMISE_AMOUNT = L_PROMISE_AMOUNT(i)
WHERE
SITE_USE_ID = L_SITE_USE_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_pro_summ updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Broken Promise update received' || SQLERRM);
L_SITE_USE_ID.delete;
L_SCORE.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET SCORE = L_SCORE(i)
WHERE
SITE_USE_ID = L_SITE_USE_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_score updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Score update received' || SQLERRM);
L_SITE_USE_ID.delete;
L_PAST_DUE_INV_VALUE.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET PAST_DUE_INV_VALUE = L_PAST_DUE_INV_VALUE(i)
WHERE
SITE_USE_ID = L_SITE_USE_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_past_due updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Past due invoice update received' || SQLERRM);
L_SITE_USE_ID.delete;
L_LAST_PAYMENT_NUMBER.delete;
L_LAST_PAYMENT_AMOUNT.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET LAST_PAYMENT_NUMBER = L_LAST_PAYMENT_NUMBER(i),
LAST_PAYMENT_AMOUNT = L_LAST_PAYMENT_AMOUNT(i)
WHERE
SITE_USE_ID = L_SITE_USE_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_last_payment_no_amount updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Last payment no and amount update received' || SQLERRM);
L_SITE_USE_ID.delete;
L_NUMBER_OF_BANKRUPTCIES.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET NUMBER_OF_BANKRUPTCIES = L_NUMBER_OF_BANKRUPTCIES(i)
WHERE
SITE_USE_ID = L_SITE_USE_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_bankruptcies updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Last payment no and amount update received' || SQLERRM);
SELECT
trx_summ.org_id,
objb.object_function ieu_object_function,
objb.object_parameters || ' DISPLAYCBO=IEXTRMAN' ieu_object_parameters,
'' ieu_media_type_uuid,
'CUST_ACCOUNT_ID' ieu_param_pk_col,
to_char(trx_summ.cust_account_id) ieu_param_pk_value,
1 resource_id,
'RS_EMPLOYEE' resource_type,
party.party_id party_id,
party.party_name party_name,
trx_summ.cust_account_id cust_account_id,
acc.account_name account_name,
acc.account_number account_number,
to_number(null) site_use_id,
null location,
max(gl.CURRENCY_CODE) currency,
SUM(trx_summ.op_invoices_count) op_invoices_count,
SUM(trx_summ.op_debit_memos_count) op_debit_memos_count,
SUM(trx_summ.op_deposits_count) op_deposits_count,
SUM(trx_summ.op_bills_receivables_count) op_bills_receivables_count,
SUM(trx_summ.op_chargeback_count) op_chargeback_count,
SUM(trx_summ.op_credit_memos_count) op_credit_memos_count,
SUM(trx_summ.unresolved_cash_count) unresolved_cash_count,
SUM(trx_summ.disputed_inv_count) disputed_inv_count,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.best_current_receivables,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.best_current_receivables))) best_current_receivables,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_invoices_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_invoices_value))) op_invoices_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_debit_memos_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_debit_memos_value))) op_debit_memos_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_deposits_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_deposits_value))) op_deposits_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_bills_receivables_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_bills_receivables_value))) op_bills_receivables_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_chargeback_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_chargeback_value))) op_chargeback_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_credit_memos_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_credit_memos_value))) op_credit_memos_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.unresolved_cash_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.unresolved_cash_value))) unresolved_cash_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.receipts_at_risk_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.receipts_at_risk_value))) receipts_at_risk_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.inv_amt_in_dispute,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.inv_amt_in_dispute))) inv_amt_in_dispute,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
SUM(trx_summ.past_due_inv_inst_count) past_due_inv_inst_count,
MAX(trx_summ.last_payment_date) last_payment_date,
max(gl.CURRENCY_CODE) last_payment_amount_curr,
MAX(trx_summ.last_update_date) last_update_date,
MAX(trx_summ.last_updated_by) last_updated_by,
MAX(trx_summ.creation_date) creation_date,
MAX(trx_summ.created_by) created_by,
MAX(trx_summ.last_update_login) last_update_login,
party.address1 address1,
party.city city,
party.state state,
party.county county,
fnd_terr.territory_short_name country,
party.province province,
party.postal_code postal_code
FROM ar_trx_bal_summary trx_summ,
hz_cust_accounts acc,
hz_parties party,
jtf_objects_b objb,
fnd_territories_tl fnd_terr,
GL_SETS_OF_BOOKS gl,
AR_SYSTEM_PARAMETERS_all sys
WHERE
trx_summ.reference_1 = '1'
AND trx_summ.cust_account_id = acc.cust_account_id
AND acc.party_id = party.party_id
AND objb.object_code = 'IEX_ACCOUNT'
AND party.country = fnd_terr.territory_code(+)
AND fnd_terr.LANGUAGE(+) = userenv('LANG')
and gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
and trx_summ.org_id = sys.org_id
and trx_summ.cust_account_id in (select temp.object_id from iex_pop_uwq_summ_gt temp where
temp.org_id=trx_summ.org_id)
GROUP BY trx_summ.org_id,
objb.object_function,
objb.object_parameters,
party.party_id,
party.party_name,
trx_summ.cust_account_id,
acc.account_name,
acc.account_number,
party.address1,
party.city,
party.state,
party.county,
fnd_terr.territory_short_name,
party.province,
party.postal_code;
select strat.jtf_object_id,
wkitem.WORK_ITEM_ID,
wkitem.schedule_start schedule_start,
wkitem.schedule_end schedule_end,
stry_temp_wkitem.category_type category,
stry_temp_wkitem.WORK_TYPE,
stry_temp_wkitem.PRIORITY_TYPE,
wkitem.resource_id,
wkitem.strategy_id,
strat.strategy_template_id,
wkitem.work_item_template_id,
wkitem.status_code,
wkitem.creation_date start_time,
wkitem.execute_end end_time,-- snuthala 28/08/2008 bug #6745580
wkitem.work_item_order wkitem_order,
wkitem.escalated_yn --Added for bug#6981126 by schekuri on 27-Jul-2008
from iex_strategies strat,
iex_strategy_work_items wkitem,
iex_stry_temp_work_items_b stry_temp_wkitem,
iex_pop_uwq_summ_gt temp
where strat.jtf_object_type = temp.object_type
AND strat.status_code IN('OPEN', 'ONHOLD')
AND wkitem.strategy_id = strat.strategy_id
AND wkitem.status_code IN('OPEN', 'ONHOLD')
AND wkitem.work_item_template_id = stry_temp_wkitem.work_item_temp_id
AND strat.jtf_object_id = temp.object_id;
SELECT
hp.collector_id,
ac.resource_id,
ac.resource_type,
hp.party_id,
hp.cust_account_id
FROM
hz_customer_profiles hp,
ar_collectors ac,
iex_pop_uwq_summ_gt temp
WHERE
hp.site_use_id is null
and hp.cust_account_id=temp.object_id
and hp.collector_id=ac.collector_id;
SELECT
ids.party_id party_id,
phone.phone_country_code phone_country_code,
phone.phone_area_code phone_area_code,
phone.phone_number phone_number,
phone.phone_extension phone_extension
FROM
hz_contact_points phone,
iex_dln_uwq_summary ids,
iex_pop_uwq_summ_gt temp
WHERE
phone.owner_table_id = ids.party_id
AND phone.owner_table_name = 'HZ_PARTIES'
AND phone.contact_point_type = 'PHONE'
and phone.primary_by_purpose = 'Y'
AND phone.contact_point_purpose = 'COLLECTIONS'
AND phone.phone_line_type NOT IN('PAGER', 'FAX')
AND phone.status = 'A'
AND nvl(phone.do_not_use_flag, 'N') = 'N'
AND ids.cust_account_id = temp.object_id;
L_LAST_UPDATE_DATE date_list;
L_LAST_UPDATED_BY number_list;
L_LAST_UPDATE_LOGIN number_list;
select del.cust_account_id,
count(1) number_of_delinquencies,
max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
from iex_delinquencies_all del,
iex_pop_uwq_summ_gt temp
WHERE del.cust_account_id = temp.object_id AND
del.org_id = temp.org_id and
del.status IN('DELINQUENT', 'PREDELINQUENT')
group by del.cust_account_id;
select del.cust_account_id,
max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
max(decode(pd.uwq_status,'COMPLETE',(decode(sign(TRUNC(pd.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_promises,
max(decode(pd.uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_promises
from iex_promise_details pd,
IEX_DELINQUENCIES_ALL DEL,
iex_pop_uwq_summ_gt temp
WHERE pd.cust_account_id = del.cust_account_id
and pd.delinquency_id = del.delinquency_id
and del.cust_account_id = temp.object_id
and del.org_id = temp.org_id
and pd.state = 'BROKEN_PROMISE'
group by del.cust_account_id;
SELECT del.cust_account_id,
COUNT(1) number_of_promises,
SUM(amount_due_remaining) broken_promise_amount,
SUM(promise_amount) promise_amount
FROM iex_promise_details pd,
iex_delinquencies_all del,
iex_pop_uwq_summ_gt temp
WHERE pd.cust_account_id = del.cust_account_id
AND pd.delinquency_id = del.delinquency_id
AND pd.status IN('COLLECTABLE', 'PENDING')
AND pd.state = 'BROKEN_PROMISE'
AND pd.amount_due_remaining > 0
AND (del.status NOT IN('CURRENT', 'CLOSE')
or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
and del.cust_account_id = temp.object_id
and del.org_id = temp.org_id
GROUP BY del.cust_account_id;
SELECT sh.score_object_id, sh.score_value score
FROM iex_score_histories sh,
iex_pop_uwq_summ_gt temp
WHERE sh.creation_date = (SELECT MAX(creation_date)
FROM iex_score_histories sh1
WHERE sh1.score_object_code = 'IEX_ACCOUNT'
AND sh1.score_object_id = sh.score_object_id)
-- AND rownum < 2
AND sh.score_object_code = 'IEX_ACCOUNT'
AND sh.score_object_id = temp.object_id;
SELECT a.cust_account_id,
SUM(b.acctd_amount_due_remaining) past_due_inv_value
FROM iex_delinquencies_all a,
ar_payment_schedules_all b,
iex_pop_uwq_summ_gt temp
WHERE a.cust_account_id = temp.object_id
AND a.payment_schedule_id = b.payment_schedule_id
AND b.status = 'OP'
AND a.status IN('DELINQUENT', 'PREDELINQUENT')
AND temp.org_id = a.org_id
GROUP BY a.cust_account_id;
SELECT o_summ.cust_account_id,
o_summ.last_payment_number last_payment_number,
iex_uwq_view_pkg.convert_amount(o_summ.last_payment_amount,o_summ.currency) last_payment_amount
FROM ar_trx_bal_summary o_summ
WHERE o_summ.cust_account_id in (select object_id from iex_pop_uwq_summ_gt)
AND o_summ.last_payment_date = (SELECT MAX(last_payment_date)
FROM ar_trx_bal_summary
WHERE cust_account_id = o_summ.cust_account_id);
select ca.cust_account_id,
COUNT(1) number_of_bankruptcies
FROM iex_bankruptcies bkr,hz_cust_accounts ca
where ca.cust_account_id in (select object_id from iex_pop_uwq_summ_gt)
and bkr.party_id=ca.party_id
and NVL(BKR.DISPOSITION_CODE,'GRANTED') in ('GRANTED','NEGOTIATION') -- Changed for bug#7693986
group by ca.cust_account_id;
select del.cust_account_id,
sum(decode(del.status,'DELINQUENT',1,'PREDELINQUENT',1,0)) number_of_delinquencies,
sum(decode(del.status,'DELINQUENT',ps.acctd_amount_due_remaining,'PREDELINQUENT',ps.acctd_amount_due_remaining,0)) past_due_inv_value,
max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies,
del.org_id org_id
from iex_delinquencies del,
ar_payment_schedules ps
WHERE del.payment_schedule_id = ps.payment_schedule_id AND
del.org_id = ps.org_id and
exists(select 1 from iex_delinquencies del1
where del1.last_update_date>=trunc(sysdate)
and del.cust_account_id=del1.cust_account_id
and del.org_id=del1.org_id)
group by del.cust_account_id, del.org_id;
L_CUST_ACCOUNT_ID.delete;
L_NUMBER_OF_DELINQUENCIES.delete;
L_PENDING_DELINQUENCIES.delete;
L_COMPLETE_DELINQUENCIES.delete;
L_ACTIVE_DELINQUENCIES.delete;
L_PAST_DUE_INV_VALUE.delete;
L_ORG_ID.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET NUMBER_OF_DELINQUENCIES = L_NUMBER_OF_DELINQUENCIES(i),
PAST_DUE_INV_VALUE = L_PAST_DUE_INV_VALUE(i),
ACTIVE_DELINQUENCIES = L_ACTIVE_DELINQUENCIES(i),
COMPLETE_DELINQUENCIES = L_COMPLETE_DELINQUENCIES(i),
PENDING_DELINQUENCIES = L_PENDING_DELINQUENCIES(i)
WHERE
CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i)
AND ORG_ID=L_ORG_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_del_dln updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Delinquency update received' || SQLERRM);
insert into iex_pop_uwq_summ_gt(object_id,object_type,org_id)
select cust_account_id,'IEX_ACCOUNT',org_id from iex_delinquencies del
where status in ('DELINQUENT','PRE-DELINQUENT')
and not exists(select 1 from IEX_DLN_UWQ_SUMMARY dus where dus.cust_account_id=del.cust_account_id and
dus.site_use_id is null and
dus.org_id=del.org_id)
group by cust_account_id,org_id;
L_ORG_ID.delete;
L_COLLECTOR_ID.delete;
L_COLLECTOR_RESOURCE_ID.delete;
L_COLLECTOR_RES_TYPE.delete;
L_IEU_OBJECT_FUNCTION.delete;
L_IEU_OBJECT_PARAMETERS.delete;
L_IEU_MEDIA_TYPE_UUID.delete;
L_IEU_PARAM_PK_COL.delete;
L_IEU_PARAM_PK_VALUE.delete;
L_RESOURCE_ID.delete;
L_RESOURCE_TYPE.delete;
L_PARTY_ID.delete;
L_PARTY_NAME.delete;
L_CUST_ACCOUNT_ID.delete;
L_ACCOUNT_NAME.delete;
L_ACCOUNT_NUMBER.delete;
L_SITE_USE_ID.delete;
L_LOCATION.delete;
L_CURRENCY.delete;
L_OP_INVOICES_COUNT.delete;
L_OP_DEBIT_MEMOS_COUNT.delete;
L_OP_DEPOSITS_COUNT.delete;
L_OP_BILLS_RECEIVABLES_COUNT.delete;
L_OP_CHARGEBACK_COUNT.delete;
L_OP_CREDIT_MEMOS_COUNT.delete;
L_UNRESOLVED_CASH_COUNT.delete;
L_DISPUTED_INV_COUNT.delete;
L_BEST_CURRENT_RECEIVABLES.delete;
L_OP_INVOICES_VALUE.delete;
L_OP_DEBIT_MEMOS_VALUE.delete;
L_OP_DEPOSITS_VALUE.delete;
L_OP_BILLS_RECEIVABLES_VALUE.delete;
L_OP_CHARGEBACK_VALUE.delete;
L_OP_CREDIT_MEMOS_VALUE.delete;
L_UNRESOLVED_CASH_VALUE.delete;
L_RECEIPTS_AT_RISK_VALUE.delete;
L_INV_AMT_IN_DISPUTE.delete;
L_PENDING_ADJ_VALUE.delete;
L_PAST_DUE_INV_VALUE.delete;
L_PAST_DUE_INV_INST_COUNT.delete;
L_LAST_PAYMENT_DATE.delete;
L_LAST_PAYMENT_AMOUNT.delete;
L_LAST_PAYMENT_AMOUNT_CURR.delete;
L_LAST_PAYMENT_NUMBER.delete;
L_LAST_UPDATE_DATE.delete;
L_LAST_UPDATED_BY.delete;
L_CREATION_DATE.delete;
L_CREATED_BY.delete;
L_LAST_UPDATE_LOGIN.delete;
L_NUMBER_OF_DELINQUENCIES.delete;
L_ACTIVE_DELINQUENCIES.delete;
L_COMPLETE_DELINQUENCIES.delete;
L_PENDING_DELINQUENCIES.delete;
L_SCORE.delete;
L_ADDRESS1.delete;
L_CITY.delete;
L_STATE.delete;
L_COUNTY.delete;
L_COUNTRY.delete;
L_PROVINCE.delete;
L_POSTAL_CODE.delete;
L_PHONE_COUNTRY_CODE.delete;
L_PHONE_AREA_CODE.delete;
L_PHONE_NUMBER.delete;
L_PHONE_EXTENSION.delete;
L_NUMBER_OF_BANKRUPTCIES.delete;
L_NUMBER_OF_PROMISES.delete;
L_BROKEN_PROMISE_AMOUNT.delete;
L_PROMISE_AMOUNT.delete;
L_ACTIVE_PROMISES.delete;
L_COMPLETE_PROMISES.delete;
L_PENDING_PROMISES.delete;
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_LOGIN,
L_ADDRESS1,
L_CITY,
L_STATE,
L_COUNTY,
L_COUNTRY,
L_PROVINCE,
L_POSTAL_CODE
limit l_max_fetches;
LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
INSERT INTO IEX_DLN_UWQ_SUMMARY
(DLN_UWQ_SUMMARY_ID
,ORG_ID
,IEU_OBJECT_FUNCTION
,IEU_OBJECT_PARAMETERS
,IEU_MEDIA_TYPE_UUID
,IEU_PARAM_PK_COL
,IEU_PARAM_PK_VALUE
,RESOURCE_ID
,RESOURCE_TYPE
,PARTY_ID
,PARTY_NAME
,CUST_ACCOUNT_ID
,ACCOUNT_NAME
,ACCOUNT_NUMBER
,SITE_USE_ID
,LOCATION
,CURRENCY
,OP_INVOICES_COUNT
,OP_DEBIT_MEMOS_COUNT
,OP_DEPOSITS_COUNT
,OP_BILLS_RECEIVABLES_COUNT
,OP_CHARGEBACK_COUNT
,OP_CREDIT_MEMOS_COUNT
,UNRESOLVED_CASH_COUNT
,DISPUTED_INV_COUNT
,BEST_CURRENT_RECEIVABLES
,OP_INVOICES_VALUE
,OP_DEBIT_MEMOS_VALUE
,OP_DEPOSITS_VALUE
,OP_BILLS_RECEIVABLES_VALUE
,OP_CHARGEBACK_VALUE
,OP_CREDIT_MEMOS_VALUE
,UNRESOLVED_CASH_VALUE
,RECEIPTS_AT_RISK_VALUE
,INV_AMT_IN_DISPUTE
,PENDING_ADJ_VALUE
,PAST_DUE_INV_INST_COUNT
,LAST_PAYMENT_DATE
,LAST_PAYMENT_AMOUNT_CURR
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ADDRESS1
,CITY
,STATE
,COUNTY
,COUNTRY
,PROVINCE
,POSTAL_CODE
,NUMBER_OF_DELINQUENCIES
,NUMBER_OF_PROMISES
,NUMBER_OF_BANKRUPTCIES)
VALUES
(IEX_DLN_UWQ_SUMMARY_S.nextval,
L_ORG_ID(i),
L_IEU_OBJECT_FUNCTION(i),
L_IEU_OBJECT_PARAMETERS(i),
L_IEU_MEDIA_TYPE_UUID(i),
L_IEU_PARAM_PK_COL(i),
L_IEU_PARAM_PK_VALUE(i),
L_RESOURCE_ID(i),
L_RESOURCE_TYPE(i),
L_PARTY_ID(i),
L_PARTY_NAME(i),
L_CUST_ACCOUNT_ID(i),
L_ACCOUNT_NAME(i),
L_ACCOUNT_NUMBER(i),
L_SITE_USE_ID(i),
L_LOCATION(i),
L_CURRENCY(i),
L_OP_INVOICES_COUNT(i),
L_OP_DEBIT_MEMOS_COUNT(i),
L_OP_DEPOSITS_COUNT(i),
L_OP_BILLS_RECEIVABLES_COUNT(i),
L_OP_CHARGEBACK_COUNT(i),
L_OP_CREDIT_MEMOS_COUNT(i),
L_UNRESOLVED_CASH_COUNT(i),
L_DISPUTED_INV_COUNT(i),
L_BEST_CURRENT_RECEIVABLES(i),
L_OP_INVOICES_VALUE(i),
L_OP_DEBIT_MEMOS_VALUE(i),
L_OP_DEPOSITS_VALUE(i),
L_OP_BILLS_RECEIVABLES_VALUE(i),
L_OP_CHARGEBACK_VALUE(i),
L_OP_CREDIT_MEMOS_VALUE(i),
L_UNRESOLVED_CASH_VALUE(i),
L_RECEIPTS_AT_RISK_VALUE(i),
L_INV_AMT_IN_DISPUTE(i),
L_PENDING_ADJ_VALUE(i),
L_PAST_DUE_INV_INST_COUNT(i),
L_LAST_PAYMENT_DATE(i),
L_LAST_PAYMENT_AMOUNT_CURR(i),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID,
L_ADDRESS1(i),
L_CITY(i),
L_STATE(i),
L_COUNTY(i),
L_COUNTRY(i),
L_PROVINCE(i),
L_POSTAL_CODE(i),
0,
0,
0);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Inserted ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows');
L_JTF_OBJECT_ID.delete;
L_WORK_ITEM_ID.delete;
L_SCHEDULE_START.delete;
L_SCHEDULE_END.delete;
L_WORK_TYPE.delete;
L_CATEGORY_TYPE.delete;
L_PRIORITY_TYPE.delete;
L_wkitem_RESOURCE_ID.delete;
L_STRATEGY_ID.delete;
L_STRATEGY_TEMPLATE_ID.delete;
L_WORK_ITEM_TEMPLATE_ID.delete;
L_STATUS_CODE.delete;
L_START_TIME.delete;
L_END_TIME.delete;
L_WORK_ITEM_ORDER.delete;
L_ESCALATED_YN.delete; --Added for bug#6981126 by schekuri on 27-Jun-2008
UPDATE IEX_DLN_UWQ_SUMMARY
SET WORK_ITEM_ID = L_WORK_ITEM_ID(i),
SCHEDULE_START = L_SCHEDULE_START(i),
SCHEDULE_END = L_SCHEDULE_END(i),
WORK_TYPE = L_WORK_TYPE(i),
CATEGORY_TYPE = L_CATEGORY_TYPE(i),
PRIORITY_TYPE = L_PRIORITY_TYPE(i),
WKITEM_RESOURCE_ID = L_WKITEM_RESOURCE_ID(i),
STRATEGY_ID = L_STRATEGY_ID(i),
STRATEGY_TEMPLATE_ID = L_STRATEGY_TEMPLATE_ID(i),
WORK_ITEM_TEMPLATE_ID = L_WORK_ITEM_TEMPLATE_ID(i),
STATUS_CODE = L_STATUS_CODE(i),
START_TIME = L_START_TIME(i),
END_TIME = L_END_TIME(i),
WORK_ITEM_ORDER = L_WORK_ITEM_ORDER(i),
WKITEM_ESCALATED_YN = L_ESCALATED_YN(i) --Added for bug#6981126 by schekuri on 27-Jun-2008
WHERE cust_account_id = L_JTF_OBJECT_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updated ' || L_JTF_OBJECT_ID.COUNT || ' rows');
L_COLLECTOR_RESOURCE_ID.delete ;
L_COLLECTOR_ID.delete;
L_RESOURCE_TYPE.delete;
L_PARTY_ID.delete;
L_CUST_ACCOUNT_ID.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET COLLECTOR_RESOURCE_ID = L_COLLECTOR_RESOURCE_ID(i),
COLLECTOR_RES_TYPE = L_RESOURCE_TYPE(i),
collector_id = l_collector_id(i),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE
CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_collector_prof updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Collector profile update received' || SQLERRM);
L_PARTY_ID.delete;
L_PHONE_COUNTRY_CODE.delete;
L_PHONE_AREA_CODE.delete;
L_PHONE_NUMBER.delete;
L_PHONE_EXTENSION.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET PHONE_COUNTRY_CODE = L_PHONE_COUNTRY_CODE(i),
PHONE_AREA_CODE = L_PHONE_AREA_CODE(i),
PHONE_NUMBER = L_PHONE_NUMBER(i),
PHONE_EXTENSION = L_PHONE_EXTENSION(i),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE PARTY_ID = L_PARTY_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Contact point Cursor updated ' ||L_PARTY_ID.count || ' rows ');
L_CUST_ACCOUNT_ID.delete;
L_NUMBER_OF_DELINQUENCIES.delete;
L_PENDING_DELINQUENCIES.delete;
L_COMPLETE_DELINQUENCIES.delete;
L_ACTIVE_DELINQUENCIES.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET NUMBER_OF_DELINQUENCIES = L_NUMBER_OF_DELINQUENCIES(i),
ACTIVE_DELINQUENCIES = L_ACTIVE_DELINQUENCIES(i),
COMPLETE_DELINQUENCIES = L_COMPLETE_DELINQUENCIES(i),
PENDING_DELINQUENCIES = L_PENDING_DELINQUENCIES(i)
WHERE
CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_del updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Delinquency update received' || SQLERRM);
L_CUST_ACCOUNT_ID.delete;
L_ACTIVE_PROMISES.delete;
L_COMPLETE_PROMISES.delete;
L_PENDING_PROMISES.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET ACTIVE_PROMISES = L_ACTIVE_PROMISES(i),
COMPLETE_PROMISES = L_COMPLETE_PROMISES(i),
PENDING_PROMISES = L_PENDING_PROMISES(i)
WHERE
CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_pro updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Promise update received' || SQLERRM);
L_CUST_ACCOUNT_ID.delete;
L_NUMBER_OF_PROMISES.delete;
L_BROKEN_PROMISE_AMOUNT .delete;
L_PROMISE_AMOUNT.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET NUMBER_OF_PROMISES = L_NUMBER_OF_PROMISES(i),
BROKEN_PROMISE_AMOUNT = L_BROKEN_PROMISE_AMOUNT(i),
PROMISE_AMOUNT = L_PROMISE_AMOUNT(i)
WHERE
CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_pro_summ updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Broken Promise update received' || SQLERRM);
L_CUST_ACCOUNT_ID.delete;
L_SCORE.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET SCORE = L_SCORE(i)
WHERE
CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_score updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Score update received' || SQLERRM);
L_CUST_ACCOUNT_ID.delete;
L_PAST_DUE_INV_VALUE.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET PAST_DUE_INV_VALUE = L_PAST_DUE_INV_VALUE(i)
WHERE
CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_past_due updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Past due invoice update received' || SQLERRM);
L_CUST_ACCOUNT_ID.delete;
L_LAST_PAYMENT_NUMBER.delete;
L_LAST_PAYMENT_AMOUNT.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET LAST_PAYMENT_NUMBER = L_LAST_PAYMENT_NUMBER(i),
LAST_PAYMENT_AMOUNT = L_LAST_PAYMENT_AMOUNT(i)
WHERE
CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_last_payment_no_amount updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Last payment no and amount update received' || SQLERRM);
L_CUST_ACCOUNT_ID.delete;
L_NUMBER_OF_BANKRUPTCIES.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET NUMBER_OF_BANKRUPTCIES = L_NUMBER_OF_BANKRUPTCIES(i)
WHERE
CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_bankruptcies updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Last payment no and amount update received' || SQLERRM);
SELECT trx_summ.org_id,
objb.object_function ieu_object_function,
objb.object_parameters || ' DISPLAYCBO=IEXTRMAN' ieu_object_parameters,
'' ieu_media_type_uuid,
'PARTY_ID' ieu_param_pk_col,
to_char(party.party_id) ieu_param_pk_value,
1 resource_id,
'RS_EMPLOYEE' resource_type,
party.party_id party_id,
party.party_name party_name,
to_number(null) cust_account_id,
null account_name,
null account_number,
to_number(null) site_use_id,
null location,
max(gl.CURRENCY_CODE) currency,
SUM(trx_summ.op_invoices_count) op_invoices_count,
SUM(trx_summ.op_debit_memos_count) op_debit_memos_count,
SUM(trx_summ.op_deposits_count) op_deposits_count,
SUM(trx_summ.op_bills_receivables_count) op_bills_receivables_count,
SUM(trx_summ.op_chargeback_count) op_chargeback_count,
SUM(trx_summ.op_credit_memos_count) op_credit_memos_count,
SUM(trx_summ.unresolved_cash_count) unresolved_cash_count,
SUM(trx_summ.disputed_inv_count) disputed_inv_count,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.best_current_receivables,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.best_current_receivables))) best_current_receivables,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_invoices_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_invoices_value))) op_invoices_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_debit_memos_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_debit_memos_value))) op_debit_memos_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_deposits_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_deposits_value))) op_deposits_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_bills_receivables_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_bills_receivables_value))) op_bills_receivables_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_chargeback_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_chargeback_value))) op_chargeback_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_credit_memos_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_credit_memos_value))) op_credit_memos_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.unresolved_cash_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.unresolved_cash_value))) unresolved_cash_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.receipts_at_risk_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.receipts_at_risk_value))) receipts_at_risk_value,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.inv_amt_in_dispute,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.inv_amt_in_dispute))) inv_amt_in_dispute,
SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
SUM(trx_summ.past_due_inv_inst_count) past_due_inv_inst_count,
MAX(trx_summ.last_payment_date) last_payment_date,
max(gl.CURRENCY_CODE) last_payment_amount_curr,
MAX(trx_summ.last_update_date) last_update_date,
MAX(trx_summ.last_updated_by) last_updated_by,
MAX(trx_summ.creation_date) creation_date,
MAX(trx_summ.created_by) created_by,
MAX(trx_summ.last_update_login) last_update_login,
party.address1 address1,
party.city city,
party.state state,
party.county county,
fnd_terr.territory_short_name country,
party.province province,
party.postal_code postal_code
FROM ar_trx_bal_summary trx_summ,
hz_cust_accounts acc,
hz_parties party,
jtf_objects_b objb,
fnd_territories_tl fnd_terr,
GL_SETS_OF_BOOKS gl,
AR_SYSTEM_PARAMETERS_all sys
WHERE trx_summ.reference_1 = '1'
AND trx_summ.cust_account_id = acc.cust_account_id
AND acc.party_id = party.party_id
AND objb.object_code = 'IEX_CUSTOMER'
AND party.country = fnd_terr.territory_code(+)
AND fnd_terr.LANGUAGE(+) = userenv('LANG')
and gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
and trx_summ.org_id = sys.org_id
and acc.party_id in
(select temp.object_id from iex_pop_uwq_summ_gt temp where temp.org_id=trx_summ.org_id)
GROUP BY trx_summ.org_id,
objb.object_function,
objb.object_parameters,
party.party_id,
party.party_name,
party.address1,
party.city,
party.state,
party.county,
fnd_terr.territory_short_name,
party.province,
party.postal_code;
select strat.jtf_object_id,
wkitem.WORK_ITEM_ID,
wkitem.schedule_start schedule_start,
wkitem.schedule_end schedule_end,
stry_temp_wkitem.category_type category,
stry_temp_wkitem.WORK_TYPE,
stry_temp_wkitem.PRIORITY_TYPE,
wkitem.resource_id,
wkitem.strategy_id,
strat.strategy_template_id,
wkitem.work_item_template_id,
wkitem.status_code,
wkitem.creation_date start_time,
wkitem.execute_end end_time,-- snuthala 28/08/2008 bug #6745580
wkitem.work_item_order wkitem_order,
wkitem.escalated_yn --Added for bug#6981126 by schekuri on 27-Jul-2008
from iex_strategies strat,
iex_strategy_work_items wkitem,
iex_stry_temp_work_items_b stry_temp_wkitem,
iex_pop_uwq_summ_gt temp
where strat.status_code IN('OPEN', 'ONHOLD')
AND wkitem.strategy_id = strat.strategy_id
AND wkitem.status_code IN('OPEN', 'ONHOLD')
AND wkitem.work_item_template_id = stry_temp_wkitem.work_item_temp_id
AND strat.jtf_object_id = temp.object_id;
SELECT
hp.collector_id,
ac.resource_id,
ac.resource_type,
hp.party_id
FROM
hz_customer_profiles hp,
ar_collectors ac,
iex_pop_uwq_summ_gt temp
WHERE
hp.cust_account_id = -1
and hp.site_use_id is null
and hp.party_id=temp.object_id
and hp.collector_id=ac.collector_id;
SELECT
phone.owner_table_id party_id,
phone.phone_country_code phone_country_code,
phone.phone_area_code phone_area_code,
phone.phone_number phone_number,
phone.phone_extension phone_extension
FROM
hz_contact_points phone,
iex_pop_uwq_summ_gt temp
WHERE
phone.owner_table_id = temp.object_id
AND phone.owner_table_name = 'HZ_PARTIES'
AND phone.contact_point_type = 'PHONE'
and phone.primary_by_purpose = 'Y'
AND phone.contact_point_purpose = 'COLLECTIONS'
AND phone.phone_line_type NOT IN('PAGER', 'FAX')
AND phone.status = 'A'
AND nvl(phone.do_not_use_flag, 'N') = 'N';
L_LAST_UPDATE_DATE date_list;
L_LAST_UPDATED_BY number_list;
L_LAST_UPDATE_LOGIN number_list;
select del.party_cust_id,
count(1) number_of_delinquencies,
max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
from iex_delinquencies_all del,
iex_pop_uwq_summ_gt temp
WHERE del.party_cust_id = temp.object_id AND
del.org_id = temp.org_id and
del.status IN('DELINQUENT', 'PREDELINQUENT')
group by del.party_cust_id;
select del.party_cust_id,
max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
max(decode(pd.uwq_status,'COMPLETE',(decode(sign(TRUNC(pd.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_promises,
max(decode(pd.uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_promises
from iex_promise_details pd,
IEX_DELINQUENCIES_ALL DEL,
iex_pop_uwq_summ_gt temp
WHERE pd.cust_account_id = del.cust_account_id
AND pd.delinquency_id = del.delinquency_id
and del.party_cust_id = temp.object_id
and del.org_id = temp.org_id
and pd.state = 'BROKEN_PROMISE'
group by del.party_cust_id;
SELECT del.party_cust_id,
COUNT(1) number_of_promises,
SUM(amount_due_remaining) broken_promise_amount,
SUM(promise_amount) promise_amount
FROM iex_promise_details pd,
iex_delinquencies_all del,
iex_pop_uwq_summ_gt temp
WHERE pd.cust_account_id = del.cust_account_id
AND pd.delinquency_id = del.delinquency_id
AND pd.status IN('COLLECTABLE','PENDING')
AND pd.state = 'BROKEN_PROMISE'
AND pd.amount_due_remaining > 0
AND (del.status NOT IN('CURRENT','CLOSE')
or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
and del.party_cust_id = temp.object_id
and del.org_id = temp.org_id
GROUP BY del.party_cust_id;
SELECT sh.score_object_id, sh.score_value score
FROM iex_score_histories sh,
iex_pop_uwq_summ_gt temp
WHERE sh.creation_date = (SELECT MAX(creation_date)
FROM iex_score_histories sh1
WHERE sh1.score_object_code = 'PARTY'
AND sh1.score_object_id = sh.score_object_id)
AND sh.score_object_code = 'PARTY'
AND sh.score_object_id = temp.object_id;
SELECT a.party_cust_id,
SUM(b.acctd_amount_due_remaining) past_due_inv_value
FROM iex_delinquencies_all a,
ar_payment_schedules_all b,
iex_pop_uwq_summ_gt temp
WHERE a.party_cust_id = temp.object_id
AND a.payment_schedule_id = b.payment_schedule_id
AND b.status = 'OP'
AND a.status IN('DELINQUENT', 'PREDELINQUENT')
AND temp.org_id = a.org_id
GROUP BY a.party_cust_id;
SELECT o_acc.party_id,
o_summ.last_payment_number last_payment_number,
iex_uwq_view_pkg.convert_amount(o_summ.last_payment_amount,o_summ.currency) last_payment_amount
FROM ar_trx_bal_summary o_summ,
hz_cust_accounts o_acc
WHERE o_summ.cust_account_id = o_acc.cust_account_id
and o_acc.party_id in (select object_id from iex_pop_uwq_summ_gt)
AND o_summ.last_payment_date = (SELECT MAX(summ.last_payment_date)
FROM ar_trx_bal_summary summ,
hz_cust_accounts acc
WHERE acc.cust_account_id = summ.cust_account_id
and acc.party_id=o_acc.party_id);
select bkr.party_id,
COUNT(1) number_of_bankruptcies
FROM iex_bankruptcies bkr
where bkr.party_id in (select object_id from iex_pop_uwq_summ_gt)
and NVL(BKR.DISPOSITION_CODE,'GRANTED') in ('GRANTED','NEGOTIATION') -- Changed for bug#7693986
group by bkr.party_id;
select del.party_cust_id,
sum(decode(del.status,'DELINQUENT',1,'PREDELINQUENT',1,0)) number_of_delinquencies,
sum(decode(del.status,'DELINQUENT',ps.acctd_amount_due_remaining,'PREDELINQUENT',ps.acctd_amount_due_remaining,0)) past_due_inv_value,
max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies,
del.org_id org_id
from iex_delinquencies del,
ar_payment_schedules ps
WHERE del.payment_schedule_id = ps.payment_schedule_id AND
del.org_id = ps.org_id and
exists(select 1 from iex_delinquencies del1
where del1.last_update_date>=trunc(sysdate)
and del.party_cust_id = del1.party_cust_id
and del1.org_id=del.org_id)
group by del.party_cust_id, del.org_id;
L_PARTY_ID.delete;
L_NUMBER_OF_DELINQUENCIES.delete;
L_PENDING_DELINQUENCIES.delete;
L_COMPLETE_DELINQUENCIES.delete;
L_ACTIVE_DELINQUENCIES.delete;
L_PAST_DUE_INV_VALUE.delete;
L_ORG_ID.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET NUMBER_OF_DELINQUENCIES = L_NUMBER_OF_DELINQUENCIES(i),
PAST_DUE_INV_VALUE = L_PAST_DUE_INV_VALUE(i),
ACTIVE_DELINQUENCIES = L_ACTIVE_DELINQUENCIES(i),
COMPLETE_DELINQUENCIES = L_COMPLETE_DELINQUENCIES(i),
PENDING_DELINQUENCIES = L_PENDING_DELINQUENCIES(i)
WHERE
PARTY_ID = L_PARTY_ID(i)
AND ORG_ID=L_ORG_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_del_dln updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Delinquency update received' || SQLERRM);
insert into iex_pop_uwq_summ_gt(object_id,object_type,org_id)
select del.party_cust_id,'PARTY',org_id from iex_delinquencies del
where status in ('DELINQUENT','PRE-DELINQUENT')
and not exists(select 1 from IEX_DLN_UWQ_SUMMARY dus where dus.party_id=del.party_cust_id and
dus.cust_account_id is null and
dus.org_id=del.org_id)
group by del.party_cust_id,org_id;
L_ORG_ID.delete;
L_COLLECTOR_ID.delete;
L_COLLECTOR_RESOURCE_ID.delete;
L_COLLECTOR_RES_TYPE.delete;
L_IEU_OBJECT_FUNCTION.delete;
L_IEU_OBJECT_PARAMETERS.delete;
L_IEU_MEDIA_TYPE_UUID.delete;
L_IEU_PARAM_PK_COL.delete;
L_IEU_PARAM_PK_VALUE.delete;
L_RESOURCE_ID.delete;
L_RESOURCE_TYPE.delete;
L_PARTY_ID.delete;
L_PARTY_NAME.delete;
L_CUST_ACCOUNT_ID.delete;
L_ACCOUNT_NAME.delete;
L_ACCOUNT_NUMBER.delete;
L_SITE_USE_ID.delete;
L_LOCATION.delete;
L_CURRENCY.delete;
L_OP_INVOICES_COUNT.delete;
L_OP_DEBIT_MEMOS_COUNT.delete;
L_OP_DEPOSITS_COUNT.delete;
L_OP_BILLS_RECEIVABLES_COUNT.delete;
L_OP_CHARGEBACK_COUNT.delete;
L_OP_CREDIT_MEMOS_COUNT.delete;
L_UNRESOLVED_CASH_COUNT.delete;
L_DISPUTED_INV_COUNT.delete;
L_BEST_CURRENT_RECEIVABLES.delete;
L_OP_INVOICES_VALUE.delete;
L_OP_DEBIT_MEMOS_VALUE.delete;
L_OP_DEPOSITS_VALUE.delete;
L_OP_BILLS_RECEIVABLES_VALUE.delete;
L_OP_CHARGEBACK_VALUE.delete;
L_OP_CREDIT_MEMOS_VALUE.delete;
L_UNRESOLVED_CASH_VALUE.delete;
L_RECEIPTS_AT_RISK_VALUE.delete;
L_INV_AMT_IN_DISPUTE.delete;
L_PENDING_ADJ_VALUE.delete;
L_PAST_DUE_INV_VALUE.delete;
L_PAST_DUE_INV_INST_COUNT.delete;
L_LAST_PAYMENT_DATE.delete;
L_LAST_PAYMENT_AMOUNT.delete;
L_LAST_PAYMENT_AMOUNT_CURR.delete;
L_LAST_PAYMENT_NUMBER.delete;
L_LAST_UPDATE_DATE.delete;
L_LAST_UPDATED_BY.delete;
L_CREATION_DATE.delete;
L_CREATED_BY.delete;
L_LAST_UPDATE_LOGIN.delete;
L_NUMBER_OF_DELINQUENCIES.delete;
L_ACTIVE_DELINQUENCIES.delete;
L_COMPLETE_DELINQUENCIES.delete;
L_PENDING_DELINQUENCIES.delete;
L_SCORE.delete;
L_ADDRESS1.delete;
L_CITY.delete;
L_STATE.delete;
L_COUNTY.delete;
L_COUNTRY.delete;
L_PROVINCE.delete;
L_POSTAL_CODE.delete;
L_PHONE_COUNTRY_CODE.delete;
L_PHONE_AREA_CODE.delete;
L_PHONE_NUMBER.delete;
L_PHONE_EXTENSION.delete;
L_NUMBER_OF_BANKRUPTCIES.delete;
L_NUMBER_OF_PROMISES.delete;
L_BROKEN_PROMISE_AMOUNT.delete;
L_PROMISE_AMOUNT.delete;
L_ACTIVE_PROMISES.delete;
L_COMPLETE_PROMISES.delete;
L_PENDING_PROMISES.delete;
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_LOGIN,
L_ADDRESS1,
L_CITY,
L_STATE,
L_COUNTY,
L_COUNTRY,
L_PROVINCE,
L_POSTAL_CODE
limit l_max_fetches;
LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
INSERT INTO IEX_DLN_UWQ_SUMMARY
(DLN_UWQ_SUMMARY_ID
,ORG_ID
,IEU_OBJECT_FUNCTION
,IEU_OBJECT_PARAMETERS
,IEU_MEDIA_TYPE_UUID
,IEU_PARAM_PK_COL
,IEU_PARAM_PK_VALUE
,RESOURCE_ID
,RESOURCE_TYPE
,PARTY_ID
,PARTY_NAME
,CUST_ACCOUNT_ID
,ACCOUNT_NAME
,ACCOUNT_NUMBER
,SITE_USE_ID
,LOCATION
,CURRENCY
,OP_INVOICES_COUNT
,OP_DEBIT_MEMOS_COUNT
,OP_DEPOSITS_COUNT
,OP_BILLS_RECEIVABLES_COUNT
,OP_CHARGEBACK_COUNT
,OP_CREDIT_MEMOS_COUNT
,UNRESOLVED_CASH_COUNT
,DISPUTED_INV_COUNT
,BEST_CURRENT_RECEIVABLES
,OP_INVOICES_VALUE
,OP_DEBIT_MEMOS_VALUE
,OP_DEPOSITS_VALUE
,OP_BILLS_RECEIVABLES_VALUE
,OP_CHARGEBACK_VALUE
,OP_CREDIT_MEMOS_VALUE
,UNRESOLVED_CASH_VALUE
,RECEIPTS_AT_RISK_VALUE
,INV_AMT_IN_DISPUTE
,PENDING_ADJ_VALUE
,PAST_DUE_INV_INST_COUNT
,LAST_PAYMENT_DATE
,LAST_PAYMENT_AMOUNT_CURR
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ADDRESS1
,CITY
,STATE
,COUNTY
,COUNTRY
,PROVINCE
,POSTAL_CODE
,NUMBER_OF_DELINQUENCIES
,NUMBER_OF_PROMISES
,NUMBER_OF_BANKRUPTCIES)
VALUES
(IEX_DLN_UWQ_SUMMARY_S.nextval,
L_ORG_ID(i),
L_IEU_OBJECT_FUNCTION(i),
L_IEU_OBJECT_PARAMETERS(i),
L_IEU_MEDIA_TYPE_UUID(i),
L_IEU_PARAM_PK_COL(i),
L_IEU_PARAM_PK_VALUE(i),
L_RESOURCE_ID(i),
L_RESOURCE_TYPE(i),
L_PARTY_ID(i),
L_PARTY_NAME(i),
L_CUST_ACCOUNT_ID(i),
L_ACCOUNT_NAME(i),
L_ACCOUNT_NUMBER(i),
L_SITE_USE_ID(i),
L_LOCATION(i),
L_CURRENCY(i),
L_OP_INVOICES_COUNT(i),
L_OP_DEBIT_MEMOS_COUNT(i),
L_OP_DEPOSITS_COUNT(i),
L_OP_BILLS_RECEIVABLES_COUNT(i),
L_OP_CHARGEBACK_COUNT(i),
L_OP_CREDIT_MEMOS_COUNT(i),
L_UNRESOLVED_CASH_COUNT(i),
L_DISPUTED_INV_COUNT(i),
L_BEST_CURRENT_RECEIVABLES(i),
L_OP_INVOICES_VALUE(i),
L_OP_DEBIT_MEMOS_VALUE(i),
L_OP_DEPOSITS_VALUE(i),
L_OP_BILLS_RECEIVABLES_VALUE(i),
L_OP_CHARGEBACK_VALUE(i),
L_OP_CREDIT_MEMOS_VALUE(i),
L_UNRESOLVED_CASH_VALUE(i),
L_RECEIPTS_AT_RISK_VALUE(i),
L_INV_AMT_IN_DISPUTE(i),
L_PENDING_ADJ_VALUE(i),
L_PAST_DUE_INV_INST_COUNT(i),
L_LAST_PAYMENT_DATE(i),
L_LAST_PAYMENT_AMOUNT_CURR(i),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID,
L_ADDRESS1(i),
L_CITY(i),
L_STATE(i),
L_COUNTY(i),
L_COUNTRY(i),
L_PROVINCE(i),
L_POSTAL_CODE(i),
0,
0,
0);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Inserted ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows');
L_JTF_OBJECT_ID.delete;
L_WORK_ITEM_ID.delete;
L_SCHEDULE_START.delete;
L_SCHEDULE_END.delete;
L_WORK_TYPE.delete;
L_CATEGORY_TYPE.delete;
L_PRIORITY_TYPE.delete;
L_wkitem_RESOURCE_ID.delete;
L_STRATEGY_ID.delete;
L_STRATEGY_TEMPLATE_ID.delete;
L_WORK_ITEM_TEMPLATE_ID.delete;
L_STATUS_CODE.delete;
L_START_TIME.delete;
L_END_TIME.delete;
L_WORK_ITEM_ORDER.delete;
L_ESCALATED_YN.delete; --Added for bug#6981126 by schekuri on 27-Jun-2008
UPDATE IEX_DLN_UWQ_SUMMARY
SET WORK_ITEM_ID = L_WORK_ITEM_ID(i),
SCHEDULE_START = L_SCHEDULE_START(i),
SCHEDULE_END = L_SCHEDULE_END(i),
WORK_TYPE = L_WORK_TYPE(i),
CATEGORY_TYPE = L_CATEGORY_TYPE(i),
PRIORITY_TYPE = L_PRIORITY_TYPE(i),
WKITEM_RESOURCE_ID = L_WKITEM_RESOURCE_ID(i),
STRATEGY_ID = L_STRATEGY_ID(i),
STRATEGY_TEMPLATE_ID = L_STRATEGY_TEMPLATE_ID(i),
WORK_ITEM_TEMPLATE_ID = L_WORK_ITEM_TEMPLATE_ID(i),
STATUS_CODE = L_STATUS_CODE(i),
START_TIME = L_START_TIME(i),
END_TIME = L_END_TIME(i),
WORK_ITEM_ORDER = L_WORK_ITEM_ORDER(i),
WKITEM_ESCALATED_YN = L_ESCALATED_YN(i) --Added for bug#6981126 by schekuri on 27-Jun-2008
WHERE party_id = L_JTF_OBJECT_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updated ' || L_JTF_OBJECT_ID.COUNT || ' rows');
L_COLLECTOR_RESOURCE_ID.delete ;
L_COLLECTOR_ID.delete;
L_RESOURCE_TYPE.delete;
L_PARTY_ID.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET COLLECTOR_RESOURCE_ID = L_COLLECTOR_RESOURCE_ID(i),
COLLECTOR_RES_TYPE = L_RESOURCE_TYPE(i),
collector_id = l_collector_id(i),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE
party_id = L_PARTY_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_collector_prof updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Collector profile update received' || SQLERRM);
L_PARTY_ID.delete;
L_PHONE_COUNTRY_CODE.delete;
L_PHONE_AREA_CODE.delete;
L_PHONE_NUMBER.delete;
L_PHONE_EXTENSION.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET PHONE_COUNTRY_CODE = L_PHONE_COUNTRY_CODE(i),
PHONE_AREA_CODE = L_PHONE_AREA_CODE(i),
PHONE_NUMBER = L_PHONE_NUMBER(i),
PHONE_EXTENSION = L_PHONE_EXTENSION(i),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE PARTY_ID = L_PARTY_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_contact_point Cursor updated ' ||L_PARTY_ID.count || ' rows ');
L_PARTY_ID.delete;
L_NUMBER_OF_DELINQUENCIES.delete;
L_PENDING_DELINQUENCIES.delete;
L_COMPLETE_DELINQUENCIES.delete;
L_ACTIVE_DELINQUENCIES.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET NUMBER_OF_DELINQUENCIES = L_NUMBER_OF_DELINQUENCIES(i),
ACTIVE_DELINQUENCIES = L_ACTIVE_DELINQUENCIES(i),
COMPLETE_DELINQUENCIES = L_COMPLETE_DELINQUENCIES(i),
PENDING_DELINQUENCIES = L_PENDING_DELINQUENCIES(i)
WHERE
PARTY_ID = L_PARTY_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_del updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Delinquency update received' || SQLERRM);
L_PARTY_ID.delete;
L_ACTIVE_PROMISES.delete;
L_COMPLETE_PROMISES.delete;
L_PENDING_PROMISES.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET ACTIVE_PROMISES = L_ACTIVE_PROMISES(i),
COMPLETE_PROMISES = L_COMPLETE_PROMISES(i),
PENDING_PROMISES = L_PENDING_PROMISES(i)
WHERE
PARTY_ID = L_PARTY_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_pro updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Promise update received' || SQLERRM);
L_PARTY_ID.delete;
L_NUMBER_OF_PROMISES.delete;
L_BROKEN_PROMISE_AMOUNT .delete;
L_PROMISE_AMOUNT.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET NUMBER_OF_PROMISES = L_NUMBER_OF_PROMISES(i),
BROKEN_PROMISE_AMOUNT = L_BROKEN_PROMISE_AMOUNT(i),
PROMISE_AMOUNT = L_PROMISE_AMOUNT(i)
WHERE
PARTY_ID = L_PARTY_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_pro_summ updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Broken Promise update received' || SQLERRM);
L_PARTY_ID.delete;
L_SCORE.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET SCORE = L_SCORE(i)
WHERE
PARTY_ID = L_PARTY_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_score updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Score update received' || SQLERRM);
L_PARTY_ID.delete;
L_PAST_DUE_INV_VALUE.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET PAST_DUE_INV_VALUE = L_PAST_DUE_INV_VALUE(i)
WHERE
PARTY_ID = L_PARTY_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_past_due updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Past due invoice update received' || SQLERRM);
L_PARTY_ID.delete;
L_LAST_PAYMENT_NUMBER.delete;
L_LAST_PAYMENT_AMOUNT.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET LAST_PAYMENT_NUMBER = L_LAST_PAYMENT_NUMBER(i),
LAST_PAYMENT_AMOUNT = L_LAST_PAYMENT_AMOUNT(i)
WHERE
PARTY_ID = L_PARTY_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_last_payment_no_amount updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Last payment no and amount update received' || SQLERRM);
L_PARTY_ID.delete;
L_NUMBER_OF_BANKRUPTCIES.delete;
UPDATE IEX_DLN_UWQ_SUMMARY
SET NUMBER_OF_BANKRUPTCIES = L_NUMBER_OF_BANKRUPTCIES(i)
WHERE
PARTY_ID = L_PARTY_ID(i);
LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_bankruptcies updated ' || L_COLLECTOR_ID.count || ' rows ');
LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Last payment no and amount update received' || SQLERRM);
SELECT PREFERENCE_VALUE FROM IEX_APP_PREFERENCES_VL WHERE PREFERENCE_NAME = 'COLLECTIONS STRATEGY LEVEL';