[Home] [Help]
114: ' From hz_cust_acct_sites_all acct_sites, ' ||
115: ' hz_party_sites party_site, ' ||
116: ' hz_cust_accounts ca, ' ||
117: ' hz_cust_site_uses_all site_uses, ' ||
118: ' hz_parties p ' ||
119: ' WHERE acct_sites.cust_account_id = ca.cust_account_id ' ||
120: ' AND acct_sites.party_site_id = party_site.party_site_id ' ||
121: ' AND acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id ' ||
122: ' AND site_uses.site_use_code = ''BILL_TO'' ' ||
126: ' From hz_cust_acct_sites_all acct_sites, ' ||
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 ' ||
3051:
3052: cursor c_cust_strat_wo_ou(p_org_id number)
3053: is select st.strategy_id,p_org_id
3054: from iex_strategies st,
3055: hz_parties hp
3056: where st.object_type='PARTY'
3057: and st.org_id is null
3058: and st.object_id=hp.party_id
3059: and not exists(select 1 from
3667: cursor c_party (p_party_id number)
3668: is
3669: select
3670: party_name
3671: from hz_parties
3672: where party_id = p_party_id;
3673:
3674: cursor c_account (p_cust_acct_id number)
3675: is
3675: is
3676: select
3677: p.party_name,
3678: c.account_number
3679: from hz_parties p,
3680: hz_cust_accounts c
3681: where c.cust_account_id = p_cust_acct_id
3682: and c.party_id = p.party_id;
3683:
3686: select
3687: p.party_name,
3688: c.account_number,
3689: site_uses.location
3690: from hz_parties p,
3691: hz_cust_accounts c,
3692: hz_cust_acct_sites_all acct_sites,
3693: hz_cust_site_uses_all site_uses
3694: where site_uses.site_use_id = p_cust_site_use_id
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,
3713: hz_cust_site_uses_all c
3714: where del.delinquency_id = p_delinquency_id
3925: /*
3926: cursor c_mou_party is
3927: select p.party_id party_id,
3928: p.party_name party_name
3929: from hz_parties p
3930: where p.party_id in (
3931: select
3932: d.party_cust_id
3933: from
3939: select p.party_id party_id,
3940: p.party_name party_name,
3941: ca.account_number account_number,
3942: ca.cust_account_id cust_account_id
3943: from hz_parties p,
3944: hz_cust_accounts ca
3945: where p.party_id = ca.party_id
3946: and ca.cust_account_id in (
3947: select
4001:
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) ';
4046:
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;