[Home] [Help]
315: END IF;
316: IF l_delinquency_id > 0 THEN
317: select org_id
318: into v_org_id
319: from iex_delinquencies_all
320: where delinquency_id=l_delinquency_id
321: AND status = 'DELINQUENT'
322: and org_id is not null
323: and rownum<=1;
323: and rownum<=1;
324: ELSE IF l_customer_site_use_id >0 THEN
325: select org_id
326: into v_org_id
327: from iex_delinquencies_all
328: where customer_site_use_id=l_customer_site_use_id
329: AND status = 'DELINQUENT'
330: and org_id is not null
331: and rownum<=1;
331: and rownum<=1;
332: ELSE IF l_cust_account_id > 0 THEN
333: select org_id
334: into v_org_id
335: from iex_delinquencies_all
336: where cust_account_id=l_cust_account_id
337: AND status = 'DELINQUENT'
338: and org_id is not null
339: and rownum<=1;
339: and rownum<=1;
340: ELSE IF l_party_id > 0 THEN
341: select org_id
342: into v_org_id
343: from iex_delinquencies_all
344: where party_cust_id= l_party_id
345: AND status = 'DELINQUENT'
346: and org_id is not null
347: and rownum<=1;
671: l_del_tbl(1).delinquency_id := l_delinquency_id;
672: end if;
673:
674: begin
675: select campaign_sched_id into l_campaign_sched_id from iex_delinquencies_all
676: where delinquency_id = l_delinquency_id;
677: l_dunning_rec.campaign_sched_id := l_campaign_sched_id;
678: exception
679: when others then
982: select a.party_id, a.party_type, a.party_name,
983: a.person_first_name, a.person_last_name,
984: b.cust_account_id, b.status, b.payment_schedule_id,
985: b.aging_bucket_line_id, b.customer_site_use_id
986: from iex_delinquencies_all b, hz_parties a
987: where a.party_id(+) = b.party_cust_id
988: and b.delinquency_id = p_delinquency_id;
989:
990: cursor c_get_party(p_work_item_id number) is
995:
996: -- bug 4930376 ctlee sql id 14771930, use _all performance
997: cursor c_get_payment(p_delinquency_id number) is
998: select a.amount_due_remaining
999: from ar_payment_schedules_all a, iex_delinquencies_all b
1000: where a.payment_schedule_id(+) = b.payment_schedule_id
1001: and b.delinquency_id = p_delinquency_id;
1002:
1003: cursor c_get_witem_temp(p_work_item_id NUMBER) is
1734: result := 'COMPLETE:' || 'N';
1735: end;
1736: else -- default level 40
1737: begin
1738: select cust_account_id into l_cust_account_id from iex_delinquencies_all
1739: where delinquency_id = l_delinquency_id;
1740: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1741: iex_debug_pub.logmessage ('check_dunning: 40 ' || ' check dunning, delinquency_id = ' || l_delinquency_id);
1742: END IF;
1748: -- and del.delinquency_id = dun.delinquency_id
1749: -- and dun.status = 'CLOSE'
1750: -- and trunc(sysdate) = trunc(dun.creation_date) );
1751: select count(*) into l_count from iex_dunnings where dunning_id in (
1752: select dun.dunning_id from iex_delinquencies_all del, iex_dunnings dun
1753: where del.cust_account_id = l_cust_account_id
1754: and ((del.delinquency_id = dun.delinquency_id and dun.status = 'CLOSE') or
1755: (del.delinquency_id = dun.delinquency_id and dun.status = 'OPEN' and
1756: del.delinquency_id <> l_delinquency_id )
1851: select a.party_id, a.party_type, a.party_name,
1852: a.person_first_name, a.person_last_name,
1853: b.cust_account_id, b.status, b.payment_schedule_id,
1854: b.aging_bucket_line_id, b.customer_site_use_id
1855: from iex_delinquencies_all b, hz_parties a
1856: where a.party_id(+) = b.party_cust_id
1857: and b.delinquency_id = p_delinquency_id;
1858:
1859: -- bug 4930376 ctlee sql id 14772154, use _all performance
1858:
1859: -- bug 4930376 ctlee sql id 14772154, use _all performance
1860: cursor c_get_payment(p_delinquency_id number) is
1861: select a.amount_due_remaining
1862: from ar_payment_schedules_all a, iex_delinquencies_all b
1863: where a.payment_schedule_id(+) = b.payment_schedule_id
1864: and b.delinquency_id = p_delinquency_id;
1865:
1866: cursor c_get_witem_temp(p_work_item_id NUMBER) is
2094: --begin bug 7425517 by sunagesh on 21-nov-11
2095: IF l_delinquency_id > 0 THEN
2096: select org_id
2097: into v_org_id
2098: from iex_delinquencies_all
2099: where delinquency_id=l_delinquency_id
2100: AND status = 'DELINQUENT'
2101: and org_id is not null
2102: and rownum<=1;
2102: and rownum<=1;
2103: ELSE IF l_customer_site_use_id >0 THEN
2104: select org_id
2105: into v_org_id
2106: from iex_delinquencies_all
2107: where customer_site_use_id=l_customer_site_use_id
2108: AND status = 'DELINQUENT'
2109: and org_id is not null
2110: and rownum<=1;
2110: and rownum<=1;
2111: ELSE IF l_cust_account_id > 0 THEN
2112: select org_id
2113: into v_org_id
2114: from iex_delinquencies_all
2115: where cust_account_id=l_cust_account_id
2116: AND status = 'DELINQUENT'
2117: and org_id is not null
2118: and rownum<=1;
2118: and rownum<=1;
2119: ELSE IF l_party_id > 0 THEN
2120: select org_id
2121: into v_org_id
2122: from iex_delinquencies_all
2123: where party_cust_id= l_party_id
2124: AND status = 'DELINQUENT'
2125: and org_id is not null
2126: and rownum<=1;
2130: END IF;
2131: --end bug 7425517 by sunagesh on 21-nov-11
2132: /*select org_id
2133: into v_org_id
2134: from iex_delinquencies_all
2135: where cust_account_id=l_cust_account_id
2136: and org_id is not null
2137: and rownum<=1;*/
2138: l_count := l_count +1;
2387: l_del_tbl(1).delinquency_id := l_delinquency_id;
2388: end if;
2389:
2390: begin
2391: select campaign_sched_id into l_campaign_sched_id from iex_delinquencies_all
2392: where delinquency_id = l_delinquency_id;
2393: l_dunning_rec.campaign_sched_id := l_campaign_sched_id;
2394: exception
2395: when others then