[Home] [Help]
143: SELECT
144: PRD.PROMISE_DETAIL_ID
145: FROM
146: IEX_PROMISE_DETAILS PRD,
147: IEX_DELINQUENCIES_ALL DEL
148: WHERE
149: DEL.DELINQUENCY_ID = PRD.DELINQUENCY_ID AND
150: DEL.CUST_ACCOUNT_ID = PRD.CUST_ACCOUNT_ID AND
151: DEL.PAYMENT_SCHEDULE_ID = P_PAYMENT_SCHEDULE_ID AND
411: 'PRD.PROMISE_DETAIL_ID, prd.status, ''Case'' ' ||
412: 'FROM ' ||
413: 'IEX_CASE_OBJECTS CAO, ' ||
414: 'IEX_PROMISE_DETAILS PRD, ' ||
415: 'IEX_DELINQUENCIES DEL ' ||
416: 'WHERE ' ||
417: 'DEL.DELINQUENCY_ID = :del AND ' ||
418: 'DEL.CASE_ID IS NOT NULL AND ' ||
419: 'DEL.CASE_ID = CAO.CAS_ID AND ' ||
612: d.party_cust_id party_cust_id,
613: a.cust_account_id cust_account_id,
614: d.customer_site_use_id customer_site_use_id
615: FROM iex_promise_details a,
616: iex_delinquencies d--_all d --Changed for bug 7237026 20-Jan-2009 barathsr
617: WHERE a.delinquency_id=d.delinquency_id
618: AND a.state = 'BROKEN_PROMISE'
619: AND a.status in ('COLLECTABLE','FULFILLED', 'CANCELLED')
620: AND a.org_id = d.org_id --Added for bug 7237026 31-Dec-2008 barathsr
630: d.party_cust_id party_cust_id,
631: pd.cust_account_id cust_account_id,
632: d.customer_site_use_id customer_site_use_id
633: FROM iex_promise_details pd,
634: iex_delinquencies d--_all d --Changed for bug 7237026 20-Jan-2009 barathsr
635: WHERE pd.delinquency_id=d.delinquency_id
636: AND pd.state = 'BROKEN_PROMISE'
637: AND pd.status in ('COLLECTABLE','FULFILLED', 'CANCELLED')
638: AND pd.org_id =d.org_id --Added for bug 7237026 31-Dec-2008 barathsr
651: d.party_cust_id party_cust_id,
652: pd.cust_account_id cust_account_id,
653: d.customer_site_use_id customer_site_use_id
654: FROM iex_promise_details pd,
655: iex_delinquencies d--_all d --Changed for bug 7237026 20-Jan-2009 barathsr
656: WHERE pd.delinquency_id=d.delinquency_id
657: AND pd.state = 'BROKEN_PROMISE'
658: AND pd.status in ('COLLECTABLE','FULFILLED', 'CANCELLED')
659: AND pd.org_id = d.org_id --Added for bug 7237026 31-Dec-2008 barathsr
672: d.party_cust_id party_cust_id,
673: pd.cust_account_id cust_account_id,
674: d.customer_site_use_id customer_site_use_id
675: FROM iex_promise_details pd,
676: iex_delinquencies d--_all d --Changed for bug 7237026 20-Jan-2009 barathsr
677: WHERE pd.delinquency_id=d.delinquency_id
678: AND pd.state = 'BROKEN_PROMISE'
679: AND pd.status in ('COLLECTABLE','FULFILLED', 'CANCELLED')
680: AND pd.org_id = d.org_id --Added for bug 7237026 31-Dec-2008 barathsr
691: CURSOR UPDATE_NOT_FULLY_PRO_DEL
692: IS
693: SELECT pd.delinquency_id delinquency_id
694: from iex_promise_details pd,
695: iex_delinquencies d
696: where pd.delinquency_id = d.delinquency_id
697: AND pd.status = 'COLLECTABLE'
698: AND pd.state = 'PROMISE'
699: AND d.staged_dunning_level = 0
698: AND pd.state = 'PROMISE'
699: AND d.staged_dunning_level = 0
700: group by pd.delinquency_id
701: having sum(pd.promise_amount) < (select amount_due_remaining from ar_payment_schedules pay,
702: iex_delinquencies del
703: where pay.payment_schedule_id = del.payment_schedule_id
704: and del.delinquency_id = pd.delinquency_id);
705:
706: CURSOR UPDATE_FULLY_PRO_DEL
706: CURSOR UPDATE_FULLY_PRO_DEL
707: IS
708: SELECT pd.delinquency_id delinquency_id
709: from iex_promise_details pd,
710: iex_delinquencies d
711: where pd.delinquency_id = d.delinquency_id
712: AND pd.status = 'COLLECTABLE'
713: AND pd.state = 'PROMISE'
714: AND d.staged_dunning_level <> 0
713: AND pd.state = 'PROMISE'
714: AND d.staged_dunning_level <> 0
715: group by pd.delinquency_id
716: having sum(pd.promise_amount) >= (select amount_due_remaining from ar_payment_schedules pay,
717: iex_delinquencies del
718: where pay.payment_schedule_id = del.payment_schedule_id
719: and del.delinquency_id = pd.delinquency_id);
720: */
721: CURSOR UPDATE_PRO_DEL
1009: LOOP
1010:
1011: iex_utilities.MaxStageForanDelinquency (p_delinquency_id => I.delinquency_id
1012: , p_stage_number => l_stage_number);
1013: update iex_delinquencies_all
1014: set staged_dunning_level = l_stage_number
1015: where delinquency_id = I.delinquency_id;
1016: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1017: LogMessage(G_PKG_NAME || '.' || l_api_name || 'Updated the staged dunning level to ' || l_stage_number || ' for delinquency id: ' || I.delinquency_id);
1021:
1022: FOR J IN UPDATE_FULLY_PRO_DEL
1023: LOOP
1024:
1025: update iex_delinquencies_all
1026: set staged_dunning_level = 0
1027: where delinquency_id = J.delinquency_id;
1028: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1029: LogMessage(G_PKG_NAME || '.' || l_api_name || 'Updated the staged dunning level to 0 for delinquency id: ' || J.delinquency_id);
1599: 'PRD.delinquency_id, ' ||
1600: 'DEL.payment_schedule_id ' ||
1601: 'FROM ' ||
1602: 'iex_promise_details prd, ' ||
1603: 'iex_delinquencies del ' ||
1604: 'WHERE ' ||
1605: 'prd.delinquency_id is not null and ' ||
1606: 'del.delinquency_id = prd.delinquency_id and ' ||
1607: 'prd.status = ''COLLECTABLE'' and ' ||
1677: last_updated_by = G_USER_ID
1678: WHERE
1679: promise_detail_id in
1680: (select prd.promise_detail_id
1681: from iex_promise_details prd, iex_delinquencies del, ar_payment_schedules aps --added for Bug 6446848 08-Dec-2008 barathsr
1682: where prd.delinquency_id is not null and
1683: prd.delinquency_id = del.delinquency_id and
1684: prd.org_id = del.org_id and --Added for bug 7237026 barathsr 31-Dec-2008
1685: del.payment_schedule_id=aps.payment_schedule_id and --added for Bug 6446848 08-Dec-2008 barathsr
2797: pro.resource_id,
2798: pro.cust_account_id,
2799: idel.customer_site_use_id,
2800: idel.delinquency_id
2801: FROM IEX_PROMISE_DETAILS pro, HZ_CUST_ACCOUNTS hca, iex_delinquencies idel
2802: WHERE
2803: pro.cust_account_id = hca.cust_account_id
2804: AND idel.delinquency_id(+) = pro.delinquency_id
2805: AND pro.status = 'COLLECTABLE'