[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
1459: 'PRD.delinquency_id, ' ||
1460: 'DEL.payment_schedule_id ' ||
1461: 'FROM ' ||
1462: 'iex_promise_details prd, ' ||
1463: 'iex_delinquencies del ' ||
1464: 'WHERE ' ||
1465: 'prd.delinquency_id is not null and ' ||
1466: 'del.delinquency_id = prd.delinquency_id and ' ||
1467: 'prd.status = ''COLLECTABLE'' and ' ||
1537: last_updated_by = G_USER_ID
1538: WHERE
1539: promise_detail_id in
1540: (select prd.promise_detail_id
1541: from iex_promise_details prd, iex_delinquencies del, ar_payment_schedules aps --added for Bug 6446848 08-Dec-2008 barathsr
1542: where prd.delinquency_id is not null and
1543: prd.delinquency_id = del.delinquency_id and
1544: prd.org_id = del.org_id and --Added for bug 7237026 barathsr 31-Dec-2008
1545: del.payment_schedule_id=aps.payment_schedule_id and --added for Bug 6446848 08-Dec-2008 barathsr
2651: pro.resource_id,
2652: pro.cust_account_id,
2653: idel.customer_site_use_id,
2654: idel.delinquency_id
2655: FROM IEX_PROMISE_DETAILS pro, HZ_CUST_ACCOUNTS hca, iex_delinquencies_all idel
2656: WHERE
2657: pro.cust_account_id = hca.cust_account_id
2658: AND idel.delinquency_id(+) = pro.delinquency_id
2659: AND pro.state = 'BROKEN_PROMISE'