[Home] [Help]
495: l_dunning_rec.dunning_object_id:= l_delinquency_id;
496: end if;
497:
498: begin
499: select campaign_sched_id into l_campaign_sched_id from iex_delinquencies
500: where delinquency_id = l_delinquency_id;
501: l_dunning_rec.campaign_sched_id := l_campaign_sched_id;
502: exception
503: when others then
755: select a.party_id, a.party_type, a.party_name,
756: a.person_first_name, a.person_last_name,
757: b.cust_account_id, b.status, b.payment_schedule_id,
758: b.aging_bucket_line_id, b.customer_site_use_id
759: from iex_delinquencies b, hz_parties a
760: where a.party_id(+) = b.party_cust_id
761: and b.delinquency_id = p_delinquency_id;
762:
763: cursor c_get_party(p_work_item_id number) is
768:
769: -- bug 4930376 ctlee sql id 14771930, use _all performance
770: cursor c_get_payment(p_delinquency_id number) is
771: select a.amount_due_remaining
772: from ar_payment_schedules_all a, iex_delinquencies b
773: where a.payment_schedule_id(+) = b.payment_schedule_id
774: and b.delinquency_id = p_delinquency_id;
775:
776: cursor c_get_witem_temp(p_work_item_id NUMBER) is
1486: result := 'COMPLETE:' || 'N';
1487: end;
1488: else -- default level 40
1489: begin
1490: select cust_account_id into l_cust_account_id from iex_delinquencies
1491: where delinquency_id = l_delinquency_id;
1492: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1493: iex_debug_pub.logmessage ('check_dunning: 40 ' || ' check dunning, delinquency_id = ' || l_delinquency_id);
1494: END IF;
1494: END IF;
1495: if l_cust_account_id is not null then
1496: -- begin bug #4230209 03/09/2005 by ctlee, multiple letters were sent in the same day even unique = 'Y'
1497: -- select count(*) into l_count from iex_dunnings where dunning_id in (
1498: -- select dun.dunning_id from iex_delinquencies del, iex_dunnings dun
1499: -- where del.cust_account_id = l_cust_account_id
1500: -- and del.delinquency_id = dun.delinquency_id
1501: -- and dun.status = 'CLOSE'
1502: -- and trunc(sysdate) = trunc(dun.creation_date) );
1500: -- and del.delinquency_id = dun.delinquency_id
1501: -- and dun.status = 'CLOSE'
1502: -- and trunc(sysdate) = trunc(dun.creation_date) );
1503: select count(*) into l_count from iex_dunnings where dunning_id in (
1504: select dun.dunning_id from iex_delinquencies del, iex_dunnings dun
1505: where del.cust_account_id = l_cust_account_id
1506: and ((del.delinquency_id = dun.delinquency_id and dun.status = 'CLOSE') or
1507: (del.delinquency_id = dun.delinquency_id and dun.status = 'OPEN' and
1508: del.delinquency_id <> l_delinquency_id )
1603: select a.party_id, a.party_type, a.party_name,
1604: a.person_first_name, a.person_last_name,
1605: b.cust_account_id, b.status, b.payment_schedule_id,
1606: b.aging_bucket_line_id, b.customer_site_use_id
1607: from iex_delinquencies b, hz_parties a
1608: where a.party_id(+) = b.party_cust_id
1609: and b.delinquency_id = p_delinquency_id;
1610:
1611: -- bug 4930376 ctlee sql id 14772154, use _all performance
1610:
1611: -- bug 4930376 ctlee sql id 14772154, use _all performance
1612: cursor c_get_payment(p_delinquency_id number) is
1613: select a.amount_due_remaining
1614: from ar_payment_schedules_all a, iex_delinquencies b
1615: where a.payment_schedule_id(+) = b.payment_schedule_id
1616: and b.delinquency_id = p_delinquency_id;
1617:
1618: cursor c_get_witem_temp(p_work_item_id NUMBER) is
2021: l_dunning_rec.dunning_object_id:= l_delinquency_id;
2022: end if;
2023:
2024: begin
2025: select campaign_sched_id into l_campaign_sched_id from iex_delinquencies_all
2026: where delinquency_id = l_delinquency_id;
2027: l_dunning_rec.campaign_sched_id := l_campaign_sched_id;
2028: exception
2029: when others then