[Home] [Help]
78: CURSOR c_get_level IS
79: SELECT PREFERENCE_VALUE FROM IEX_APP_PREFERENCES_VL WHERE PREFERENCE_NAME = 'COLLECTIONS STRATEGY LEVEL';
80:
81: CURSOR c_get_table IS
82: select OWNER || '.' || TABLE_NAME from sys.all_tables where table_name = 'IEX_DLN_UWQ_SUMMARY';
83:
84: --Start bug 6634879 gnramasa 20th Nov 07
85: CURSOR c_iex_billto_uwq_summary IS
86: SELECT
1639: wkitem.escalated_yn --Added for bug#6981126 by schekuri on 27-Jul-2008
1640: from iex_strategies strat,
1641: iex_strategy_work_items wkitem,
1642: iex_stry_temp_work_items_b stry_temp_wkitem,
1643: IEX_DLN_UWQ_SUMMARY sum
1644: where strat.jtf_object_type = decode(p_level, 'CUSTOMER', 'PARTY', 'ACCOUNT', 'IEX_ACCOUNT', 'BILL_TO', 'IEX_BILLTO')
1645: AND strat.status_code IN('OPEN', 'ONHOLD')
1646: AND wkitem.strategy_id = strat.strategy_id
1647: AND wkitem.status_code IN('OPEN', 'ONHOLD')
1658: ar.resource_type,
1659: ar.collector_id
1660: FROM
1661: ar_collectors ar,
1662: iex_dln_uwq_summary ids
1663: WHERE
1664: ar.collector_id = ids.collector_id
1665: AND ar.resource_id is NOT NULL
1666: AND ar.resource_id <> ids.collector_resource_id
1674: ids.cust_account_id,
1675: ids.site_use_id
1676: FROM
1677: hz_customer_profiles hp,
1678: iex_dln_uwq_summary ids
1679: WHERE
1680: hp.party_id = ids.party_id
1681: AND decode(hp.cust_account_id,null,1,hp.cust_account_id)
1682: = decode(ids.cust_account_id,null,1,ids.cust_account_id)
1696: party.province province,
1697: party.postal_code postal_code
1698: FROM
1699: hz_parties party,
1700: iex_dln_uwq_summary ids,
1701: fnd_territories_tl fnd_terr
1702: WHERE
1703: party.party_id = ids.party_id
1704: AND party.country = fnd_terr.territory_code(+)
1713: phone.phone_number phone_number,
1714: phone.phone_extension phone_extension
1715: FROM
1716: hz_contact_points phone,
1717: iex_dln_uwq_summary ids
1718: WHERE
1719: phone.owner_table_id = ids.party_id
1720: AND phone.owner_table_name = 'HZ_PARTIES'
1721: AND phone.contact_point_type = 'PHONE'
2093: end if;
2094: /* End Kasreeni 3/1/2007 Bug 5905023 We will update everytime instead of once */
2095:
2096: if (l_from_date is null and p_mode = 'CP' ) then --Bug5691098
2097: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fully repopulating IEX_DLN_UWQ_SUMMARY table...');
2098:
2099: OPEN c_get_table;
2100: FETCH c_get_table INTO l_truncate_table;
2101: CLOSE c_get_table;
2104: EXECUTE IMMEDIATE 'truncate table ' || l_truncate_table;
2105: LogMessage(FND_LOG.LEVEL_STATEMENT,'Truncated whole table');
2106:
2107: elsiF (l_from_date is not null) then --Bug5691098
2108: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Partially repopulating IEX_DLN_UWQ_SUMMARY table...');
2109: /* Begin gnramasa Modified for bug 5677415 27/11/2006 */
2110: IF l_level = 'CUSTOMER' THEN
2111: delete from IEX_DLN_UWQ_SUMMARY where party_id in
2112: (select party_id from ar_trx_bal_summary trb, hz_cust_accounts hza
2107: elsiF (l_from_date is not null) then --Bug5691098
2108: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Partially repopulating IEX_DLN_UWQ_SUMMARY table...');
2109: /* Begin gnramasa Modified for bug 5677415 27/11/2006 */
2110: IF l_level = 'CUSTOMER' THEN
2111: delete from IEX_DLN_UWQ_SUMMARY where party_id in
2112: (select party_id from ar_trx_bal_summary trb, hz_cust_accounts hza
2113: where hza.cust_account_id = trb.cust_account_id and trunc(trb.LAST_UPDATE_DATE) >= trunc(l_from_date));
2114: ELSIF l_level = 'ACCOUNT' THEN
2115: delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, org_id) in
2111: delete from IEX_DLN_UWQ_SUMMARY where party_id in
2112: (select party_id from ar_trx_bal_summary trb, hz_cust_accounts hza
2113: where hza.cust_account_id = trb.cust_account_id and trunc(trb.LAST_UPDATE_DATE) >= trunc(l_from_date));
2114: ELSIF l_level = 'ACCOUNT' THEN
2115: delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, org_id) in
2116: (select cust_account_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date));
2117: ELSIF l_level = 'BILL_TO' THEN
2118: delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, site_use_id, org_id) in
2119: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date));
2114: ELSIF l_level = 'ACCOUNT' THEN
2115: delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, org_id) in
2116: (select cust_account_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date));
2117: ELSIF l_level = 'BILL_TO' THEN
2118: delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, site_use_id, org_id) in
2119: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date));
2120: END IF;
2121: /* delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, site_use_id, org_id) in
2122: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date)); */
2117: ELSIF l_level = 'BILL_TO' THEN
2118: delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, site_use_id, org_id) in
2119: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date));
2120: END IF;
2121: /* delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, site_use_id, org_id) in
2122: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date)); */
2123: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No of rows deleted: ' || SQL%ROWCOUNT);
2124: /* End gnramasa Modified for bug 5677415 27/11/2006 */
2125: LogMessage(FND_LOG.LEVEL_STATEMENT,'Deleted rows that will be repopulated');
2725: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
2726: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2727:
2728: forall i IN L_IEU_OBJECT_FUNCTION.FIRST .. L_IEU_OBJECT_FUNCTION.LAST
2729: INSERT INTO IEX_DLN_UWQ_SUMMARY
2730: (DLN_UWQ_SUMMARY_ID
2731: ,ORG_ID
2732: ,COLLECTOR_ID
2733: ,COLLECTOR_RESOURCE_ID
2800: ,ACTIVE_PROMISES
2801: ,COMPLETE_PROMISES
2802: ,PENDING_PROMISES)
2803: VALUES
2804: (IEX_DLN_UWQ_SUMMARY_S.nextval,
2805: L_ORG_ID(i),
2806: L_COLLECTOR_ID(i),
2807: L_COLLECTOR_RESOURCE_ID(i),
2808: L_COLLECTOR_RES_TYPE(i),
2993:
2994: IF l_level = 'CUSTOMER' THEN
2995:
2996: forall i IN L_JTF_OBJECT_ID.FIRST .. L_JTF_OBJECT_ID.LAST
2997: UPDATE IEX_DLN_UWQ_SUMMARY
2998: SET WORK_ITEM_ID = L_WORK_ITEM_ID(i),
2999: SCHEDULE_START = L_SCHEDULE_START(i),
3000: SCHEDULE_END = L_SCHEDULE_END(i),
3001: WORK_TYPE = L_WORK_TYPE(i),
3014:
3015: ELSIF l_level = 'ACCOUNT' THEN
3016:
3017: forall i IN L_JTF_OBJECT_ID.FIRST .. L_JTF_OBJECT_ID.LAST
3018: UPDATE IEX_DLN_UWQ_SUMMARY
3019: SET WORK_ITEM_ID = L_WORK_ITEM_ID(i),
3020: SCHEDULE_START = L_SCHEDULE_START(i),
3021: SCHEDULE_END = L_SCHEDULE_END(i),
3022: WORK_TYPE = L_WORK_TYPE(i),
3035:
3036: ELSIF l_level = 'BILL_TO' THEN
3037:
3038: forall i IN L_JTF_OBJECT_ID.FIRST .. L_JTF_OBJECT_ID.LAST
3039: UPDATE IEX_DLN_UWQ_SUMMARY
3040: SET WORK_ITEM_ID = L_WORK_ITEM_ID(i),
3041: SCHEDULE_START = L_SCHEDULE_START(i),
3042: SCHEDULE_END = L_SCHEDULE_END(i),
3043: WORK_TYPE = L_WORK_TYPE(i),
3115: ELSE
3116:
3117: forall i IN L_PARTY_ID.FIRST .. L_PARTY_ID.LAST
3118:
3119: UPDATE IEX_DLN_UWQ_SUMMARY
3120: SET address1 = L_ADDRESS1(i),
3121: city = L_CITY(i),
3122: state = L_STATE(i),
3123: county = L_COUNTY(i),
3165:
3166: ELSE
3167:
3168: forall i IN L_PARTY_ID.FIRST .. L_PARTY_ID.LAST
3169: UPDATE IEX_DLN_UWQ_SUMMARY
3170: SET COLLECTOR_ID = L_COLLECTOR_ID(i),
3171: last_update_date = SYSDATE,
3172: last_updated_by = FND_GLOBAL.USER_ID
3173: WHERE
3210:
3211: ELSE
3212:
3213: forall i IN L_COLLECTOR_ID.FIRST .. L_COLLECTOR_ID.LAST
3214: UPDATE IEX_DLN_UWQ_SUMMARY
3215: SET COLLECTOR_RESOURCE_ID = L_COLLECTOR_RESOURCE_ID(i),
3216: COLLECTOR_RES_TYPE = L_RESOURCE_TYPE(i),
3217: last_update_date = SYSDATE,
3218: last_updated_by = FND_GLOBAL.USER_ID
3261: ELSE
3262:
3263: forall i IN L_PARTY_ID.FIRST .. L_PARTY_ID.LAST
3264:
3265: UPDATE IEX_DLN_UWQ_SUMMARY
3266: SET PHONE_COUNTRY_CODE = L_PHONE_COUNTRY_CODE(i),
3267: PHONE_AREA_CODE = L_PHONE_AREA_CODE(i),
3268: PHONE_NUMBER = L_PHONE_NUMBER(i),
3269: PHONE_EXTENSION = L_PHONE_EXTENSION(i),
3571: phone.phone_number phone_number,
3572: phone.phone_extension phone_extension
3573: FROM
3574: hz_contact_points phone,
3575: iex_dln_uwq_summary ids,
3576: iex_pop_uwq_summ_gt temp
3577: WHERE
3578: phone.owner_table_id = ids.party_id
3579: AND phone.owner_table_name = 'HZ_PARTIES'
3825: EXIT;
3826: ELSE
3827:
3828: forall i IN L_SITE_USE_ID.FIRST .. L_SITE_USE_ID.LAST
3829: UPDATE IEX_DLN_UWQ_SUMMARY
3830: SET NUMBER_OF_DELINQUENCIES = L_NUMBER_OF_DELINQUENCIES(i),
3831: PAST_DUE_INV_VALUE = L_PAST_DUE_INV_VALUE(i),
3832: ACTIVE_DELINQUENCIES = L_ACTIVE_DELINQUENCIES(i),
3833: COMPLETE_DELINQUENCIES = L_COMPLETE_DELINQUENCIES(i),
3849: -- End Bug #6251657 bibeura 25-OCT-2007
3850: insert into iex_pop_uwq_summ_gt(object_id,object_type,org_id)
3851: select customer_site_use_id,'IEX_BILLTO',org_id from iex_delinquencies del
3852: where status in ('DELINQUENT','PRE-DELINQUENT')
3853: and not exists(select 1 from IEX_DLN_UWQ_SUMMARY dus where dus.site_use_id=del.customer_site_use_id and
3854: dus.org_id=del.org_id)
3855: group by customer_site_use_id,org_id;
3856: if sql%rowcount<=0 then
3857: return;
4006: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
4007: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4008: LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
4009: forall i IN L_IEU_OBJECT_FUNCTION.FIRST .. L_IEU_OBJECT_FUNCTION.LAST
4010: INSERT INTO IEX_DLN_UWQ_SUMMARY
4011: (DLN_UWQ_SUMMARY_ID
4012: ,ORG_ID
4013: ,IEU_OBJECT_FUNCTION
4014: ,IEU_OBJECT_PARAMETERS
4062: ,NUMBER_OF_DELINQUENCIES
4063: ,NUMBER_OF_PROMISES
4064: ,NUMBER_OF_BANKRUPTCIES)
4065: VALUES
4066: (IEX_DLN_UWQ_SUMMARY_S.nextval,
4067: L_ORG_ID(i),
4068: L_IEU_OBJECT_FUNCTION(i),
4069: L_IEU_OBJECT_PARAMETERS(i),
4070: L_IEU_MEDIA_TYPE_UUID(i),
4191: LogMessage(FND_LOG.LEVEL_STATEMENT,' Updating table...');
4192: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4193:
4194: forall i IN L_JTF_OBJECT_ID.FIRST .. L_JTF_OBJECT_ID.LAST
4195: UPDATE IEX_DLN_UWQ_SUMMARY
4196: SET WORK_ITEM_ID = L_WORK_ITEM_ID(i),
4197: SCHEDULE_START = L_SCHEDULE_START(i),
4198: SCHEDULE_END = L_SCHEDULE_END(i),
4199: WORK_TYPE = L_WORK_TYPE(i),
4253:
4254: ELSE
4255:
4256: forall i IN L_SITE_USE_ID.FIRST .. L_SITE_USE_ID.LAST
4257: UPDATE IEX_DLN_UWQ_SUMMARY
4258: SET COLLECTOR_RESOURCE_ID = L_COLLECTOR_RESOURCE_ID(i),
4259: COLLECTOR_RES_TYPE = L_RESOURCE_TYPE(i),
4260: collector_id = l_collector_id(i),
4261: last_update_date = SYSDATE,
4305: ELSE
4306:
4307: forall i IN L_PARTY_ID.FIRST .. L_PARTY_ID.LAST
4308:
4309: UPDATE IEX_DLN_UWQ_SUMMARY
4310: SET PHONE_COUNTRY_CODE = L_PHONE_COUNTRY_CODE(i),
4311: PHONE_AREA_CODE = L_PHONE_AREA_CODE(i),
4312: PHONE_NUMBER = L_PHONE_NUMBER(i),
4313: PHONE_EXTENSION = L_PHONE_EXTENSION(i),
4353:
4354: ELSE
4355:
4356: forall i IN L_SITE_USE_ID.FIRST .. L_SITE_USE_ID.LAST
4357: UPDATE IEX_DLN_UWQ_SUMMARY
4358: SET NUMBER_OF_DELINQUENCIES = L_NUMBER_OF_DELINQUENCIES(i),
4359: ACTIVE_DELINQUENCIES = L_ACTIVE_DELINQUENCIES(i),
4360: COMPLETE_DELINQUENCIES = L_COMPLETE_DELINQUENCIES(i),
4361: PENDING_DELINQUENCIES = L_PENDING_DELINQUENCIES(i)
4399:
4400: ELSE
4401:
4402: forall i IN L_SITE_USE_ID.FIRST .. L_SITE_USE_ID.LAST
4403: UPDATE IEX_DLN_UWQ_SUMMARY
4404: SET ACTIVE_PROMISES = L_ACTIVE_PROMISES(i),
4405: COMPLETE_PROMISES = L_COMPLETE_PROMISES(i),
4406: PENDING_PROMISES = L_PENDING_PROMISES(i)
4407: WHERE
4444:
4445: ELSE
4446:
4447: forall i IN L_SITE_USE_ID.FIRST .. L_SITE_USE_ID.LAST
4448: UPDATE IEX_DLN_UWQ_SUMMARY
4449: SET NUMBER_OF_PROMISES = L_NUMBER_OF_PROMISES(i),
4450: BROKEN_PROMISE_AMOUNT = L_BROKEN_PROMISE_AMOUNT(i),
4451: PROMISE_AMOUNT = L_PROMISE_AMOUNT(i)
4452: WHERE
4485:
4486: ELSE
4487:
4488: forall i IN L_SITE_USE_ID.FIRST .. L_SITE_USE_ID.LAST
4489: UPDATE IEX_DLN_UWQ_SUMMARY
4490: SET SCORE = L_SCORE(i)
4491: WHERE
4492: SITE_USE_ID = L_SITE_USE_ID(i);
4493: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_score updated ' || L_COLLECTOR_ID.count || ' rows ');
4524:
4525: ELSE
4526:
4527: forall i IN L_SITE_USE_ID.FIRST .. L_SITE_USE_ID.LAST
4528: UPDATE IEX_DLN_UWQ_SUMMARY
4529: SET PAST_DUE_INV_VALUE = L_PAST_DUE_INV_VALUE(i)
4530: WHERE
4531: SITE_USE_ID = L_SITE_USE_ID(i);
4532: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_past_due updated ' || L_COLLECTOR_ID.count || ' rows ');
4565:
4566: ELSE
4567:
4568: forall i IN L_SITE_USE_ID.FIRST .. L_SITE_USE_ID.LAST
4569: UPDATE IEX_DLN_UWQ_SUMMARY
4570: SET LAST_PAYMENT_NUMBER = L_LAST_PAYMENT_NUMBER(i),
4571: LAST_PAYMENT_AMOUNT = L_LAST_PAYMENT_AMOUNT(i)
4572: WHERE
4573: SITE_USE_ID = L_SITE_USE_ID(i);
4605:
4606: ELSE
4607:
4608: forall i IN L_SITE_USE_ID.FIRST .. L_SITE_USE_ID.LAST
4609: UPDATE IEX_DLN_UWQ_SUMMARY
4610: SET NUMBER_OF_BANKRUPTCIES = L_NUMBER_OF_BANKRUPTCIES(i)
4611: WHERE
4612: SITE_USE_ID = L_SITE_USE_ID(i);
4613: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_bankruptcies updated ' || L_COLLECTOR_ID.count || ' rows ');
4799: phone.phone_number phone_number,
4800: phone.phone_extension phone_extension
4801: FROM
4802: hz_contact_points phone,
4803: iex_dln_uwq_summary ids,
4804: iex_pop_uwq_summ_gt temp
4805: WHERE
4806: phone.owner_table_id = ids.party_id
4807: AND phone.owner_table_name = 'HZ_PARTIES'
5049: EXIT;
5050: ELSE
5051:
5052: forall i IN L_CUST_ACCOUNT_ID.FIRST .. L_CUST_ACCOUNT_ID.LAST
5053: UPDATE IEX_DLN_UWQ_SUMMARY
5054: SET NUMBER_OF_DELINQUENCIES = L_NUMBER_OF_DELINQUENCIES(i),
5055: PAST_DUE_INV_VALUE = L_PAST_DUE_INV_VALUE(i),
5056: ACTIVE_DELINQUENCIES = L_ACTIVE_DELINQUENCIES(i),
5057: COMPLETE_DELINQUENCIES = L_COMPLETE_DELINQUENCIES(i),
5074: -- End Bug #6251657 bibeura 25-OCT-2007
5075: insert into iex_pop_uwq_summ_gt(object_id,object_type,org_id)
5076: select cust_account_id,'IEX_ACCOUNT',org_id from iex_delinquencies del
5077: where status in ('DELINQUENT','PRE-DELINQUENT')
5078: and not exists(select 1 from IEX_DLN_UWQ_SUMMARY dus where dus.cust_account_id=del.cust_account_id and
5079: dus.site_use_id is null and
5080: dus.org_id=del.org_id)
5081: group by cust_account_id,org_id;
5082: if sql%rowcount<=0 then
5232: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
5233: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5234: LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
5235: forall i IN L_IEU_OBJECT_FUNCTION.FIRST .. L_IEU_OBJECT_FUNCTION.LAST
5236: INSERT INTO IEX_DLN_UWQ_SUMMARY
5237: (DLN_UWQ_SUMMARY_ID
5238: ,ORG_ID
5239: ,IEU_OBJECT_FUNCTION
5240: ,IEU_OBJECT_PARAMETERS
5288: ,NUMBER_OF_DELINQUENCIES
5289: ,NUMBER_OF_PROMISES
5290: ,NUMBER_OF_BANKRUPTCIES)
5291: VALUES
5292: (IEX_DLN_UWQ_SUMMARY_S.nextval,
5293: L_ORG_ID(i),
5294: L_IEU_OBJECT_FUNCTION(i),
5295: L_IEU_OBJECT_PARAMETERS(i),
5296: L_IEU_MEDIA_TYPE_UUID(i),
5418: LogMessage(FND_LOG.LEVEL_STATEMENT,' Updating table...');
5419: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5420:
5421: forall i IN L_JTF_OBJECT_ID.FIRST .. L_JTF_OBJECT_ID.LAST
5422: UPDATE IEX_DLN_UWQ_SUMMARY
5423: SET WORK_ITEM_ID = L_WORK_ITEM_ID(i),
5424: SCHEDULE_START = L_SCHEDULE_START(i),
5425: SCHEDULE_END = L_SCHEDULE_END(i),
5426: WORK_TYPE = L_WORK_TYPE(i),
5480:
5481: ELSE
5482:
5483: forall i IN L_CUST_ACCOUNT_ID.FIRST .. L_CUST_ACCOUNT_ID.LAST
5484: UPDATE IEX_DLN_UWQ_SUMMARY
5485: SET COLLECTOR_RESOURCE_ID = L_COLLECTOR_RESOURCE_ID(i),
5486: COLLECTOR_RES_TYPE = L_RESOURCE_TYPE(i),
5487: collector_id = l_collector_id(i),
5488: last_update_date = SYSDATE,
5532: ELSE
5533:
5534: forall i IN L_PARTY_ID.FIRST .. L_PARTY_ID.LAST
5535:
5536: UPDATE IEX_DLN_UWQ_SUMMARY
5537: SET PHONE_COUNTRY_CODE = L_PHONE_COUNTRY_CODE(i),
5538: PHONE_AREA_CODE = L_PHONE_AREA_CODE(i),
5539: PHONE_NUMBER = L_PHONE_NUMBER(i),
5540: PHONE_EXTENSION = L_PHONE_EXTENSION(i),
5580:
5581: ELSE
5582:
5583: forall i IN L_CUST_ACCOUNT_ID.FIRST .. L_CUST_ACCOUNT_ID.LAST
5584: UPDATE IEX_DLN_UWQ_SUMMARY
5585: SET NUMBER_OF_DELINQUENCIES = L_NUMBER_OF_DELINQUENCIES(i),
5586: ACTIVE_DELINQUENCIES = L_ACTIVE_DELINQUENCIES(i),
5587: COMPLETE_DELINQUENCIES = L_COMPLETE_DELINQUENCIES(i),
5588: PENDING_DELINQUENCIES = L_PENDING_DELINQUENCIES(i)
5626:
5627: ELSE
5628:
5629: forall i IN L_CUST_ACCOUNT_ID.FIRST .. L_CUST_ACCOUNT_ID.LAST
5630: UPDATE IEX_DLN_UWQ_SUMMARY
5631: SET ACTIVE_PROMISES = L_ACTIVE_PROMISES(i),
5632: COMPLETE_PROMISES = L_COMPLETE_PROMISES(i),
5633: PENDING_PROMISES = L_PENDING_PROMISES(i)
5634: WHERE
5671:
5672: ELSE
5673:
5674: forall i IN L_CUST_ACCOUNT_ID.FIRST .. L_CUST_ACCOUNT_ID.LAST
5675: UPDATE IEX_DLN_UWQ_SUMMARY
5676: SET NUMBER_OF_PROMISES = L_NUMBER_OF_PROMISES(i),
5677: BROKEN_PROMISE_AMOUNT = L_BROKEN_PROMISE_AMOUNT(i),
5678: PROMISE_AMOUNT = L_PROMISE_AMOUNT(i)
5679: WHERE
5712:
5713: ELSE
5714:
5715: forall i IN L_CUST_ACCOUNT_ID.FIRST .. L_CUST_ACCOUNT_ID.LAST
5716: UPDATE IEX_DLN_UWQ_SUMMARY
5717: SET SCORE = L_SCORE(i)
5718: WHERE
5719: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5720: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_score updated ' || L_COLLECTOR_ID.count || ' rows ');
5751:
5752: ELSE
5753:
5754: forall i IN L_CUST_ACCOUNT_ID.FIRST .. L_CUST_ACCOUNT_ID.LAST
5755: UPDATE IEX_DLN_UWQ_SUMMARY
5756: SET PAST_DUE_INV_VALUE = L_PAST_DUE_INV_VALUE(i)
5757: WHERE
5758: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5759: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_past_due updated ' || L_COLLECTOR_ID.count || ' rows ');
5792:
5793: ELSE
5794:
5795: forall i IN L_CUST_ACCOUNT_ID.FIRST .. L_CUST_ACCOUNT_ID.LAST
5796: UPDATE IEX_DLN_UWQ_SUMMARY
5797: SET LAST_PAYMENT_NUMBER = L_LAST_PAYMENT_NUMBER(i),
5798: LAST_PAYMENT_AMOUNT = L_LAST_PAYMENT_AMOUNT(i)
5799: WHERE
5800: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5832:
5833: ELSE
5834:
5835: forall i IN L_CUST_ACCOUNT_ID.FIRST .. L_CUST_ACCOUNT_ID.LAST
5836: UPDATE IEX_DLN_UWQ_SUMMARY
5837: SET NUMBER_OF_BANKRUPTCIES = L_NUMBER_OF_BANKRUPTCIES(i)
5838: WHERE
5839: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5840: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_bankruptcies updated ' || L_COLLECTOR_ID.count || ' rows ');
6270: EXIT;
6271: ELSE
6272:
6273: forall i IN L_PARTY_ID.FIRST .. L_PARTY_ID.LAST
6274: UPDATE IEX_DLN_UWQ_SUMMARY
6275: SET NUMBER_OF_DELINQUENCIES = L_NUMBER_OF_DELINQUENCIES(i),
6276: PAST_DUE_INV_VALUE = L_PAST_DUE_INV_VALUE(i),
6277: ACTIVE_DELINQUENCIES = L_ACTIVE_DELINQUENCIES(i),
6278: COMPLETE_DELINQUENCIES = L_COMPLETE_DELINQUENCIES(i),
6295:
6296: insert into iex_pop_uwq_summ_gt(object_id,object_type,org_id)
6297: select del.party_cust_id,'PARTY',org_id from iex_delinquencies del
6298: where status in ('DELINQUENT','PRE-DELINQUENT')
6299: and not exists(select 1 from IEX_DLN_UWQ_SUMMARY dus where dus.party_id=del.party_cust_id and
6300: dus.cust_account_id is null and
6301: dus.org_id=del.org_id)
6302: group by del.party_cust_id,org_id;
6303: if sql%rowcount<=0 then
6453: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
6454: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6455: LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
6456: forall i IN L_IEU_OBJECT_FUNCTION.FIRST .. L_IEU_OBJECT_FUNCTION.LAST
6457: INSERT INTO IEX_DLN_UWQ_SUMMARY
6458: (DLN_UWQ_SUMMARY_ID
6459: ,ORG_ID
6460: ,IEU_OBJECT_FUNCTION
6461: ,IEU_OBJECT_PARAMETERS
6509: ,NUMBER_OF_DELINQUENCIES
6510: ,NUMBER_OF_PROMISES
6511: ,NUMBER_OF_BANKRUPTCIES)
6512: VALUES
6513: (IEX_DLN_UWQ_SUMMARY_S.nextval,
6514: L_ORG_ID(i),
6515: L_IEU_OBJECT_FUNCTION(i),
6516: L_IEU_OBJECT_PARAMETERS(i),
6517: L_IEU_MEDIA_TYPE_UUID(i),
6639: LogMessage(FND_LOG.LEVEL_STATEMENT,' Updating table...');
6640: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6641:
6642: forall i IN L_JTF_OBJECT_ID.FIRST .. L_JTF_OBJECT_ID.LAST
6643: UPDATE IEX_DLN_UWQ_SUMMARY
6644: SET WORK_ITEM_ID = L_WORK_ITEM_ID(i),
6645: SCHEDULE_START = L_SCHEDULE_START(i),
6646: SCHEDULE_END = L_SCHEDULE_END(i),
6647: WORK_TYPE = L_WORK_TYPE(i),
6697:
6698: ELSE
6699:
6700: forall i IN L_PARTY_ID.FIRST .. L_PARTY_ID.LAST
6701: UPDATE IEX_DLN_UWQ_SUMMARY
6702: SET COLLECTOR_RESOURCE_ID = L_COLLECTOR_RESOURCE_ID(i),
6703: COLLECTOR_RES_TYPE = L_RESOURCE_TYPE(i),
6704: collector_id = l_collector_id(i),
6705: last_update_date = SYSDATE,
6747: ELSE
6748:
6749: forall i IN L_PARTY_ID.FIRST .. L_PARTY_ID.LAST
6750:
6751: UPDATE IEX_DLN_UWQ_SUMMARY
6752: SET PHONE_COUNTRY_CODE = L_PHONE_COUNTRY_CODE(i),
6753: PHONE_AREA_CODE = L_PHONE_AREA_CODE(i),
6754: PHONE_NUMBER = L_PHONE_NUMBER(i),
6755: PHONE_EXTENSION = L_PHONE_EXTENSION(i),
6793:
6794: ELSE
6795:
6796: forall i IN L_PARTY_ID.FIRST .. L_PARTY_ID.LAST
6797: UPDATE IEX_DLN_UWQ_SUMMARY
6798: SET NUMBER_OF_DELINQUENCIES = L_NUMBER_OF_DELINQUENCIES(i),
6799: ACTIVE_DELINQUENCIES = L_ACTIVE_DELINQUENCIES(i),
6800: COMPLETE_DELINQUENCIES = L_COMPLETE_DELINQUENCIES(i),
6801: PENDING_DELINQUENCIES = L_PENDING_DELINQUENCIES(i)
6838:
6839: ELSE
6840:
6841: forall i IN L_PARTY_ID.FIRST .. L_PARTY_ID.LAST
6842: UPDATE IEX_DLN_UWQ_SUMMARY
6843: SET ACTIVE_PROMISES = L_ACTIVE_PROMISES(i),
6844: COMPLETE_PROMISES = L_COMPLETE_PROMISES(i),
6845: PENDING_PROMISES = L_PENDING_PROMISES(i)
6846: WHERE
6882:
6883: ELSE
6884:
6885: forall i IN L_PARTY_ID.FIRST .. L_PARTY_ID.LAST
6886: UPDATE IEX_DLN_UWQ_SUMMARY
6887: SET NUMBER_OF_PROMISES = L_NUMBER_OF_PROMISES(i),
6888: BROKEN_PROMISE_AMOUNT = L_BROKEN_PROMISE_AMOUNT(i),
6889: PROMISE_AMOUNT = L_PROMISE_AMOUNT(i)
6890: WHERE
6922:
6923: ELSE
6924:
6925: forall i IN L_PARTY_ID.FIRST .. L_PARTY_ID.LAST
6926: UPDATE IEX_DLN_UWQ_SUMMARY
6927: SET SCORE = L_SCORE(i)
6928: WHERE
6929: PARTY_ID = L_PARTY_ID(i);
6930: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_score updated ' || L_COLLECTOR_ID.count || ' rows ');
6960:
6961: ELSE
6962:
6963: forall i IN L_PARTY_ID.FIRST .. L_PARTY_ID.LAST
6964: UPDATE IEX_DLN_UWQ_SUMMARY
6965: SET PAST_DUE_INV_VALUE = L_PAST_DUE_INV_VALUE(i)
6966: WHERE
6967: PARTY_ID = L_PARTY_ID(i);
6968: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_past_due updated ' || L_COLLECTOR_ID.count || ' rows ');
7000:
7001: ELSE
7002:
7003: forall i IN L_PARTY_ID.FIRST .. L_PARTY_ID.LAST
7004: UPDATE IEX_DLN_UWQ_SUMMARY
7005: SET LAST_PAYMENT_NUMBER = L_LAST_PAYMENT_NUMBER(i),
7006: LAST_PAYMENT_AMOUNT = L_LAST_PAYMENT_AMOUNT(i)
7007: WHERE
7008: PARTY_ID = L_PARTY_ID(i);
7039:
7040: ELSE
7041:
7042: forall i IN L_PARTY_ID.FIRST .. L_PARTY_ID.LAST
7043: UPDATE IEX_DLN_UWQ_SUMMARY
7044: SET NUMBER_OF_BANKRUPTCIES = L_NUMBER_OF_BANKRUPTCIES(i)
7045: WHERE
7046: PARTY_ID = L_PARTY_ID(i);
7047: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_bankruptcies updated ' || L_COLLECTOR_ID.count || ' rows ');