[Home] [Help]
127: ' hz_party_sites party_site, ' ||
128: ' hz_cust_accounts ca, ' ||
129: ' hz_cust_site_uses_all site_uses, ' ||
130: ' hz_parties p,' ||
131: ' iex_delinquencies_all del ' ||
132: ' WHERE acct_sites.cust_account_id = ca.cust_account_id ' ||
133: ' AND acct_sites.party_site_id = party_site.party_site_id ' ||
134: ' AND acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id ' ||
135: ' AND site_uses.site_use_code = ''BILL_TO'' ' ||
409: l_del_query := 'select d.party_cust_id, null, null, null, null, null,';
410: l_del_query := l_del_query || ' d.party_cust_id object_id, ''PARTY'' object_type, null';
411: l_del_query := l_del_query || ' , 10 strategy_level, d.PARTY_CUST_ID jtf_object_id, ''PARTY'' jtf_object_type';
412: l_del_query := l_del_query || ' , null status';
413: l_del_query := l_del_query || ' from iex_delinquencies_all d';
414: -- Start for bug 8708271 multi level strategy
415: if l_party_override = 'Y' then
416: l_del_query := l_del_query || ' , hz_party_preferences partyPrf ';
417: end if;
462: l_del_query := 'select d.party_cust_id, d.cust_account_id, null, null, null, null,';
463: l_del_query := l_del_query || ' d.cust_account_id object_id, ''ACCOUNT'' object_type, null,';
464: l_del_query := l_del_query || ' 20 strategy_level, d.cust_account_id jtf_object_id, ''IEX_ACCOUNT'' jtf_object_type';
465: l_del_query := l_del_query || ' , null status';
466: l_del_query := l_del_query || ' from iex_delinquencies_all d';
467: -- Start for bug 8708271 multi level strategy
468: if l_party_override = 'Y' then
469: l_del_query := l_del_query || ' , hz_party_preferences partyPrf ';
470: end if;
513: l_del_query := 'select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, null, null, null,';
514: l_del_query := l_del_query || ' d.customer_site_use_id object_id, ''BILL_TO'' object_type, null,';
515: l_del_query := l_del_query || ' 30 strategy_level, d.customer_site_use_id jtf_object_id, ''IEX_BILLTO'' jtf_object_type';
516: l_del_query := l_del_query || ' , null status';
517: l_del_query := l_del_query || ' from iex_delinquencies_all d';
518: -- Start for bug 8708271 multi level strategy
519: if l_party_override = 'Y' then
520: l_del_query := l_del_query || ' , hz_party_preferences partyPrf ';
521: end if;
568: l_del_query := l_del_query || ' d.delinquency_id object_id, ''DELINQUENT'' object_type ,';
569: l_del_query := l_del_query || ' d.score_value, 40 strategy_level, d.delinquency_id jtf_object_id,';
570: l_del_query := l_del_query || ' ''IEX_DELINQUENCY'' jtf_object_type';
571: l_del_query := l_del_query || ' , d.status status';
572: l_del_query := l_del_query || ' from iex_delinquencies_all d';
573: -- Start for bug 8708271 multi level strategy
574: if l_party_override = 'Y' then
575: l_del_query := l_del_query || ' , hz_party_preferences partyPrf ';
576: end if;
612: l_del_query := l_del_query || ' d.delinquency_id object_id, ''DELINQUENT'' object_type ,';
613: l_del_query := l_del_query || ' d.score_value, 40 strategy_level, d.delinquency_id jtf_object_id,';
614: l_del_query := l_del_query || ' ''IEX_DELINQUENCY'' jtf_object_type';
615: l_del_query := l_del_query || ' , d.status status';
616: l_del_query := l_del_query || ' from iex_delinquencies_all d';
617: -- Start for bug 8708271 multi level strategy
618: if l_party_override = 'Y' then
619: l_del_query := l_del_query || ' , hz_party_preferences partyPrf ';
620: end if;
776: write_log(FND_LOG.LEVEL_PROCEDURE, 'l_gracePeriod-Bina ' || l_gracePeriod );
777: END IF;
778: IF l_DefaultStrategyLevel = 10 THEN
779: OPEN c_gracePeriod FOR
780: select c.creation_date from iex_delinquencies_all c
781: where (c.status = l_DelStatusDel or c.status = l_delStatusPreDel)
782: and c.party_cust_id = l_stry_cnt_rec.PARTY_CUST_ID
783: order by c.creation_date asc; -- Changed for bug#8248285 by PNAVEENK on 13-2-2009
784: elsif l_DefaultStrategyLevel = 20 THEN
782: and c.party_cust_id = l_stry_cnt_rec.PARTY_CUST_ID
783: order by c.creation_date asc; -- Changed for bug#8248285 by PNAVEENK on 13-2-2009
784: elsif l_DefaultStrategyLevel = 20 THEN
785: OPEN c_gracePeriod FOR
786: select c.creation_date from iex_delinquencies_all c
787: where (c.status = l_DelStatusDel or c.status = l_delStatusPreDel)
788: and c.party_cust_id = l_stry_cnt_rec.PARTY_CUST_ID
789: and c.cust_account_id = l_stry_cnt_rec.CUST_ACCOUNT_ID
790: order by c.creation_date asc; -- Changed for bug#8248285 by PNAVEENK on 13-2-2009
789: and c.cust_account_id = l_stry_cnt_rec.CUST_ACCOUNT_ID
790: order by c.creation_date asc; -- Changed for bug#8248285 by PNAVEENK on 13-2-2009
791: elsif l_DefaultStrategyLevel = 30 THEN
792: OPEN c_gracePeriod FOR
793: select c.creation_date from iex_delinquencies_all c
794: where (c.status = l_DelStatusDel or c.status = l_delStatusPreDel)
795: and c.party_cust_id = l_stry_cnt_rec.PARTY_CUST_ID
796: and c.cust_account_id = l_stry_cnt_rec.CUST_ACCOUNT_ID
797: and c.customer_site_use_id = l_stry_cnt_rec.customer_site_use_ID
797: and c.customer_site_use_id = l_stry_cnt_rec.customer_site_use_ID
798: order by c.creation_date asc; -- Changed for bug#8248285 by PNAVEENK on 13-2-2009
799: ELSE
800: OPEN c_gracePeriod FOR
801: select c.creation_date from iex_delinquencies_all c
802: where (c.status = l_DelStatusDel or c.status = l_delStatusPreDel)
803: and c.party_cust_id = l_stry_cnt_rec.PARTY_CUST_ID
804: and c.cust_account_id = l_stry_cnt_rec.CUST_ACCOUNT_ID
805: and c.customer_site_use_id = l_stry_cnt_rec.customer_site_use_ID
916: write_log(FND_LOG.LEVEL_PROCEDURE, 'Strategy score tolerance = ' || vScoreTolerance );
917: write_log(FND_LOG.LEVEL_PROCEDURE, 'Change Strategy = ' || vChangeStrategy );
918: END IF;
919:
920: -- score in iex_strategies diff from score_histories/iex_delinquencies_all table
921: -- and score history out of the strategy template score tolerance
922: -- and strategy template change strategy flag is Y
923: if (
924: ( (vScoreValue <> l_stry_cnt_rec.score_value)
1117: if l_strategy_rec.strategy_level = 10 then
1118:
1119: SELECT count(1)
1120: into l_unpro_dels
1121: FROM ar_payment_schedules_all ps, iex_delinquencies_all del
1122: WHERE del.party_cust_id=l_strategy_rec.object_id
1123: AND ps.payment_schedule_id = del.payment_schedule_id
1124: AND ps.status = 'OP'
1125: AND del.status IN ('DELINQUENT', 'PREDELINQUENT')
1149: elsif l_strategy_rec.strategy_level = 20 then
1150:
1151: SELECT count(1)
1152: into l_unpro_dels
1153: FROM ar_payment_schedules_all ps, iex_delinquencies_all del
1154: WHERE del.cust_account_id=l_strategy_rec.object_id
1155: AND ps.payment_schedule_id = del.payment_schedule_id
1156: AND ps.status = 'OP'
1157: AND del.status IN ('DELINQUENT', 'PREDELINQUENT')
1181: elsif l_strategy_rec.strategy_level = 30 then
1182:
1183: SELECT count(1)
1184: into l_unpro_dels
1185: FROM ar_payment_schedules_all ps, iex_delinquencies_all del
1186: WHERE del.customer_site_use_id=l_strategy_rec.object_id
1187: AND ps.payment_schedule_id = del.payment_schedule_id
1188: AND ps.status = 'OP'
1189: AND del.status IN ('DELINQUENT', 'PREDELINQUENT')
1213: else
1214:
1215: SELECT count(1)
1216: into l_unpro_dels
1217: FROM ar_payment_schedules_all ps, iex_delinquencies_all del
1218: WHERE del.delinquency_id=l_strategy_rec.object_id
1219: AND ps.payment_schedule_id = del.payment_schedule_id
1220: AND ps.status = 'OP'
1221: AND del.status IN ('DELINQUENT', 'PREDELINQUENT')
1553: --end if;
1554:
1555: --Start adding for bug 8756947 gnramasa 3rd Aug 09
1556: IF l_DefaultStrategyLevel = 10 THEN
1557: vPLSQL := 'select s.strategy_id, s.strategy_template_id, S.STATUS_CODE from iex_strategies s, iex_delinquencies_all d '||
1558: ' where s.strategy_level = ' || l_DefaultStrategyLevel || ' and '||
1559: ' s.status_code IN (''' || l_StratStatusOpen || ''', ''' || l_StratStatusOnhold || ''', ''' || l_StratStatusPending || ''') and '||
1560: /* begin add for bug 4408860 - add checking CLOSE status from case delinquency */
1561: ' (d.status = ''' || l_DelStatusCurrent || ''' or d.status = ''' || l_DelStatusClose || ''') and d.party_cust_id = s.party_id '||
1562: /* end add for bug 4408860 - add checking CLOSE status from case delinquency */
1563: /* Begin add for bug 16568002 gnramasa 29th Mar 2013 */
1564: ' and s.JTF_OBJECT_TYPE = ''PARTY''' ||
1565: /* End add for bug 16568002 gnramasa 29th Mar 2013 */
1566: ' and not exists (select null from iex_delinquencies_all dd where dd.status '||
1567: /* Begin add for bug 16563459 gnramasa 8th Apr 2013 */
1568: --' = ''' || l_DelStatusDel || ''' and dd.party_cust_id = s.party_id) ';
1569: ' in ( ''' || l_DelStatusDel || ''', ''' || l_DelStatusPreDel || ''') and dd.party_cust_id = s.party_id) ';
1570: /* End add for bug 16563459 gnramasa 8th Apr 2013 */
1578: end if;
1579: vPLSQL := vPLSQL || ' group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE';
1580:
1581: elsif l_DefaultStrategyLevel = 20 THEN
1582: vPLSQL := 'select s.strategy_id, s.strategy_template_id, S.STATUS_CODE from iex_strategies s, iex_delinquencies_all d '||
1583: ' where s.strategy_level = ' || l_DefaultStrategyLevel ||' and '||
1584: ' s.status_code IN (''' || l_StratStatusOpen || ''', ''' || l_StratStatusOnhold || ''', ''' || l_StratStatusPending || ''') and '||
1585: /* begin add for bug 4408860 - add checking CLOSE status from case delinquency */
1586: ' (d.status = ''' || l_DelStatusCurrent || ''' or d.status = ''' || l_DelStatusClose || ''') and d.CUST_ACCOUNT_id = s.CUST_ACCOUNT_id '||
1587: /* end add for bug 4408860 - add checking CLOSE status from case delinquency */
1588: /* Begin add for bug 16568002 gnramasa 29th Mar 2013 */
1589: ' and s.JTF_OBJECT_TYPE = ''IEX_ACCOUNT''' ||
1590: /* End add for bug 16568002 gnramasa 29th Mar 2013 */
1591: ' and not exists (select null from iex_delinquencies_all dd where dd.status '||
1592: /* Begin add for bug 16563459 gnramasa 8th Apr 2013 */
1593: --' = ''' || l_DelStatusDel || ''' and dd.CUST_ACCOUNT_id = s.CUST_ACCOUNT_id) ';
1594: ' in ( ''' || l_DelStatusDel || ''', ''' || l_DelStatusPreDel || ''') and dd.CUST_ACCOUNT_id = s.CUST_ACCOUNT_id) ';
1595: /* End add for bug 16563459 gnramasa 8th Apr 2013 */
1602: end if;
1603: vPLSQL := vPLSQL || ' group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE';
1604:
1605: elsif l_DefaultStrategyLevel = 30 THEN
1606: vPLSQL := 'select s.strategy_id, s.strategy_template_id, S.STATUS_CODE from iex_strategies s, iex_delinquencies_all d '||
1607: ' where s.strategy_level = ' || l_DefaultStrategyLevel || ' and '||
1608: ' s.status_code IN (''' || l_StratStatusOpen || ''', ''' || l_StratStatusOnhold || ''', ''' || l_StratStatusPending || ''') and '||
1609: /* begin add for bug 4408860 - add checking CLOSE status from case delinquency */
1610: ' (d.status = ''' || l_DelStatusCurrent || ''' or d.status = ''' || l_DelStatusClose || ''') and d.customer_site_use_id = s.customer_site_use_id '||
1611: /* end add for bug 4408860 - add checking CLOSE status from case delinquency */
1612: /* Begin add for bug 16568002 gnramasa 29th Mar 2013 */
1613: ' and s.JTF_OBJECT_TYPE = ''IEX_BILLTO''' ||
1614: /* End add for bug 16568002 gnramasa 29th Mar 2013 */
1615: ' and not exists (select null from iex_delinquencies_all dd where dd.status '||
1616: /* Begin add for bug 16563459 gnramasa 8th Apr 2013 */
1617: --' = ''' || l_DelStatusDel || ''' and dd.customer_site_use_id = s.customer_site_use_id) ';
1618: ' in ( ''' || l_DelStatusDel || ''', ''' || l_DelStatusPreDel || ''') and dd.customer_site_use_id = s.customer_site_use_id) ';
1619: /* End add for bug 16563459 gnramasa 8th Apr 2013 */
1631: /*
1632: OPEN c_open_strategies
1633: FOR
1634: select s.strategy_id, s.strategy_template_id, s.status_code
1635: from iex_strategies s, iex_delinquencies_all d where d.status = l_DelStatusCurrent and
1636: s.strategy_level = l_DefaultStrategyLevel and
1637: s.object_id = d.delinquency_id and
1638: s.status_code IN (l_StratStatusOpen, l_StratStatusOnhold, l_StratStatusPending);
1639: */
1638: s.status_code IN (l_StratStatusOpen, l_StratStatusOnhold, l_StratStatusPending);
1639: */
1640: /* end bug 4253030 by ctlee 03/29/2005 */
1641: vPLSQL := 'select s.strategy_id, s.strategy_template_id, s.status_code '||
1642: ' from iex_strategies s, iex_delinquencies_all d '||
1643: /* begin add for bug 4408860 - add checking CLOSE status from case delinquency */
1644: ' where (d.status = ''' || l_DelStatusCurrent || ''' or d.status = ''' || l_DelStatusClose || ''') and '||
1645: /* end add for bug 4408860 - add checking CLOSE status from case delinquency */
1646: /* Begin add for bug 16568002 gnramasa 1st Apr 2013 */
1736: select s.strategy_id strategy_id,
1737: s.strategy_template_id strategy_template_id,
1738: S.STATUS_CODE STATUS_CODE,
1739: d.party_cust_id party_id
1740: from iex_strategies s, iex_delinquencies_all d
1741: where s.strategy_level = 10 and
1742: s.status_code = 'ONHOLD' and
1743: d.status in ('DELINQUENT','PREDELINQUENT') and
1744: d.party_cust_id = s.party_id and
1753: select s.strategy_id strategy_id,
1754: s.strategy_template_id strategy_template_id,
1755: S.STATUS_CODE STATUS_CODE,
1756: d.cust_account_id cust_account_id
1757: from iex_strategies s, iex_delinquencies_all d
1758: where s.strategy_level = 20 and
1759: s.status_code = 'ONHOLD' and
1760: d.status in ('DELINQUENT','PREDELINQUENT') and
1761: d.CUST_ACCOUNT_id = s.CUST_ACCOUNT_id and
1770: select s.strategy_id strategy_id,
1771: s.strategy_template_id strategy_template_id,
1772: S.STATUS_CODE STATUS_CODE,
1773: d.customer_site_use_id billto_id
1774: from iex_strategies s, iex_delinquencies_all d
1775: where s.strategy_level = 30 and
1776: s.status_code = 'ONHOLD' and
1777: d.status in ('DELINQUENT','PREDELINQUENT') and
1778: d.customer_site_use_id = s.customer_site_use_id and
1813: vPLSQL := 'select s.strategy_id strategy_id, '||
1814: ' s.strategy_template_id strategy_template_id, '||
1815: ' S.STATUS_CODE STATUS_CODE, '||
1816: ' d.party_cust_id party_id '||
1817: ' from iex_strategies s, iex_delinquencies_all d '||
1818: ' where s.strategy_level = 10 and '||
1819: ' s.status_code = ''ONHOLD'' and '||
1820: ' nvl(s.release_date,sysdate) <= SYSDATE and ' || -- Bug 14804876 bibeura
1821: ' d.status in (''DELINQUENT'',''PREDELINQUENT'') and '||
1882: vPLSQL := 'select s.strategy_id strategy_id, '||
1883: ' s.strategy_template_id strategy_template_id, '||
1884: ' S.STATUS_CODE STATUS_CODE, '||
1885: ' d.cust_account_id cust_account_id '||
1886: ' from iex_strategies s, iex_delinquencies_all d '||
1887: ' where s.strategy_level = 20 and '||
1888: ' s.status_code = ''ONHOLD'' and '||
1889: ' nvl(s.release_date,sysdate) <= SYSDATE and ' || -- Bug 14804876 bibeura
1890: ' d.status in (''DELINQUENT'',''PREDELINQUENT'') and '||
1951: vPLSQL := 'select s.strategy_id strategy_id, '||
1952: ' s.strategy_template_id strategy_template_id, '||
1953: ' S.STATUS_CODE STATUS_CODE, '||
1954: ' d.customer_site_use_id billto_id '||
1955: ' from iex_strategies s, iex_delinquencies_all d '||
1956: ' where s.strategy_level = 30 and '||
1957: ' s.status_code = ''ONHOLD'' and '||
1958: ' nvl(s.release_date,sysdate) <= SYSDATE and ' || -- Bug 14804876 bibeura
1959: ' d.status in (''DELINQUENT'',''PREDELINQUENT'') and '||
2019: vPLSQL := 'select s.strategy_id strategy_id, '||
2020: ' s.strategy_template_id strategy_template_id, '||
2021: ' S.STATUS_CODE STATUS_CODE, '||
2022: ' d.delinquency_id delinquency_id '||
2023: ' from iex_strategies s, iex_delinquencies_all d '||
2024: ' where s.strategy_level = 40 and '||
2025: ' s.status_code = ''ONHOLD'' and '||
2026: ' nvl(s.release_date,sysdate) <= SYSDATE and ' || -- Bug 14804876 bibeura
2027: ' d.status in (''DELINQUENT'',''PREDELINQUENT'') and '||
3083:
3084: cursor c_del_strat_wo_ou(p_org_id number)
3085: is select st.strategy_id,del.org_id
3086: from iex_strategies st,
3087: iex_delinquencies_all del
3088: where st.object_type='DELINQUENT'
3089: and st.org_id is null
3090: and st.object_id=del.delinquency_id
3091: and del.org_id = p_org_id;
3704: p.party_name,
3705: aps.trx_number TRANSACTION_NUMBER,
3706: b.account_number,
3707: c.location
3708: from iex_delinquencies_all del,
3709: ar_payment_schedules_all aps ,
3710: hz_parties p,
3711: iex_strategies a,
3712: hz_cust_accounts b,
3930: where p.party_id in (
3931: select
3932: d.party_cust_id
3933: from
3934: iex_delinquencies_all d
3935: group by d.party_cust_id
3936: having count(distinct d.org_id) > 1);
3937:
3938: cursor c_mou_account is
3946: and ca.cust_account_id in (
3947: select
3948: d.cust_account_id
3949: from
3950: iex_delinquencies_all d
3951: group by d.cust_account_id
3952: having count(distinct d.org_id) > 1);
3953:
3954:
4002: writelog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
4003: end if;
4004:
4005: vPLSQL := 'select s.party_id party_id, s.party_name party_name from hz_parties s ' ||
4006: ' where s.party_id in ( select d.party_cust_id from iex_delinquencies_all d '||
4007: ' group by d.party_cust_id having count(distinct d.org_id) > 1)';
4008: if l_custom_select IS NOT NULL then
4009: vPLSQL := vPLSQL || ' and exists ( ' || l_custom_select || '= s.party_id) ';
4010: end if;
4047: writelog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
4048: end if;
4049: vPLSQL1 := 'select s.party_id party_id, s.party_name party_name, cu_ac.account_number account_number, cu_ac.cust_account_id cust_account_id '||
4050: ' from hz_parties s, hz_cust_accounts cu_ac where s.party_id = cu_ac.party_id and cu_ac.cust_account_id in ( '||
4051: ' select d.cust_account_id from iex_delinquencies_all d group by d.cust_account_id having count(distinct d.org_id) > 1)';
4052: if l_custom_select IS NOT NULL then
4053: vPLSQL1 := vPLSQL1 || ' and exists ( ' || l_custom_select || '= cu_ac.cust_account_id) ';
4054: end if;
4055: writelog('gen_xml_append_closetag_sty: vPLSQL1 = ' || vPLSQL1);