878:
879: select count(*)
880: into ct_prof
881: from hz_cust_profile_amts cpa,
882: hz_customer_profiles cp
883: where cp.cust_account_id = customer_id
884: -- and cp.site_use_id = site_use_id
885: and cp.site_use_id = site_use_id_1
886: and cp.cust_account_profile_id = cpa.cust_account_profile_id;
928: l_cred_summ_terms,
929: l_cred_summ_exempt_dun,
930: l_cred_summ_collector,
931: l_customer_profile_id
932: from hz_customer_profiles cp,
933: ar_collectors coll,
934: ar_lookups lk,
935: ra_terms term
936: where cp.collector_id = coll.collector_id
965: l_cred_summ_exempt_dun,
966: l_cred_summ_collector,
967: l_customer_profile_id
968:
969: from hz_customer_profiles cp,
970: ar_collectors coll,
971: ar_lookups lk,
972: ra_terms term
973:
2124: CURSOR C_STATEMENT IS
2125: SELECT AR_STATEMENT_CYCLES.NAME,
2126: AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE,
2127: TRUNC(TRUNC(SYSDATE) - AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE)
2128: FROM HZ_CUSTOMER_PROFILES, AR_STATEMENT_CYCLES,
2129: AR_STATEMENT_CYCLE_DATES
2130: WHERE HZ_CUSTOMER_PROFILES.STATEMENT_CYCLE_ID =
2131: AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID
2132: AND AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID =
2126: AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE,
2127: TRUNC(TRUNC(SYSDATE) - AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE)
2128: FROM HZ_CUSTOMER_PROFILES, AR_STATEMENT_CYCLES,
2129: AR_STATEMENT_CYCLE_DATES
2130: WHERE HZ_CUSTOMER_PROFILES.STATEMENT_CYCLE_ID =
2131: AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID
2132: AND AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID =
2133: AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_ID
2134: AND AR_STATEMENT_CYCLE_DATES.PRINTED = 'Y'
2131: AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID
2132: AND AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID =
2133: AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_ID
2134: AND AR_STATEMENT_CYCLE_DATES.PRINTED = 'Y'
2135: AND HZ_CUSTOMER_PROFILES.CUST_ACCOUNT_ID = CUSTOMER_ID
2136: AND HZ_CUSTOMER_PROFILES.site_use_id = site_use_id
2137: ORDER BY AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE DESC,
2138: AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_DATE_ID DESC ;
2139:
2132: AND AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID =
2133: AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_ID
2134: AND AR_STATEMENT_CYCLE_DATES.PRINTED = 'Y'
2135: AND HZ_CUSTOMER_PROFILES.CUST_ACCOUNT_ID = CUSTOMER_ID
2136: AND HZ_CUSTOMER_PROFILES.site_use_id = site_use_id
2137: ORDER BY AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE DESC,
2138: AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_DATE_ID DESC ;
2139:
2140: CURSOR C_STATEMENT_DEFAULT IS
2140: CURSOR C_STATEMENT_DEFAULT IS
2141: SELECT AR_STATEMENT_CYCLES.NAME,
2142: AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE,
2143: TRUNC(TRUNC(SYSDATE) - AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE)
2144: FROM HZ_CUSTOMER_PROFILES, AR_STATEMENT_CYCLES,
2145: AR_STATEMENT_CYCLE_DATES
2146: WHERE HZ_CUSTOMER_PROFILES.STATEMENT_CYCLE_ID =
2147: AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID
2148: AND AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID =
2142: AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE,
2143: TRUNC(TRUNC(SYSDATE) - AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE)
2144: FROM HZ_CUSTOMER_PROFILES, AR_STATEMENT_CYCLES,
2145: AR_STATEMENT_CYCLE_DATES
2146: WHERE HZ_CUSTOMER_PROFILES.STATEMENT_CYCLE_ID =
2147: AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID
2148: AND AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID =
2149: AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_ID
2150: AND AR_STATEMENT_CYCLE_DATES.PRINTED = 'Y'
2147: AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID
2148: AND AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID =
2149: AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_ID
2150: AND AR_STATEMENT_CYCLE_DATES.PRINTED = 'Y'
2151: AND HZ_CUSTOMER_PROFILES.CUST_ACCOUNT_ID = CUSTOMER_ID
2152: AND HZ_CUSTOMER_PROFILES.site_use_id is null
2153: ORDER BY AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE DESC,
2154: AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_DATE_ID DESC
2155: ;
2148: AND AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID =
2149: AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_ID
2150: AND AR_STATEMENT_CYCLE_DATES.PRINTED = 'Y'
2151: AND HZ_CUSTOMER_PROFILES.CUST_ACCOUNT_ID = CUSTOMER_ID
2152: AND HZ_CUSTOMER_PROFILES.site_use_id is null
2153: ORDER BY AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE DESC,
2154: AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_DATE_ID DESC
2155: ;
2156:
2199:
2200: BEGIN
2201: SELECT MIN(AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE)
2202: INTO l_last_stmnt_next_trx_date
2203: FROM HZ_CUSTOMER_PROFILES,
2204: AR_STATEMENT_CYCLE_DATES
2205: WHERE HZ_CUSTOMER_PROFILES.STATEMENT_CYCLE_ID =
2206: AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_ID
2207: AND AR_STATEMENT_CYCLE_DATES.PRINTED = 'N'
2201: SELECT MIN(AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE)
2202: INTO l_last_stmnt_next_trx_date
2203: FROM HZ_CUSTOMER_PROFILES,
2204: AR_STATEMENT_CYCLE_DATES
2205: WHERE HZ_CUSTOMER_PROFILES.STATEMENT_CYCLE_ID =
2206: AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_ID
2207: AND AR_STATEMENT_CYCLE_DATES.PRINTED = 'N'
2208: AND AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE > to_date(c_last_st_date,'DD-MM-YYYY')
2209: AND HZ_CUSTOMER_PROFILES.CUST_ACCOUNT_ID = CUSTOMER_ID
2205: WHERE HZ_CUSTOMER_PROFILES.STATEMENT_CYCLE_ID =
2206: AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_ID
2207: AND AR_STATEMENT_CYCLE_DATES.PRINTED = 'N'
2208: AND AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE > to_date(c_last_st_date,'DD-MM-YYYY')
2209: AND HZ_CUSTOMER_PROFILES.CUST_ACCOUNT_ID = CUSTOMER_ID
2210: AND HZ_CUSTOMER_PROFILES.SITE_USE_ID IS NULL
2211: ORDER BY AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE,
2212: AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_DATE_ID
2213: ;
2206: AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_ID
2207: AND AR_STATEMENT_CYCLE_DATES.PRINTED = 'N'
2208: AND AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE > to_date(c_last_st_date,'DD-MM-YYYY')
2209: AND HZ_CUSTOMER_PROFILES.CUST_ACCOUNT_ID = CUSTOMER_ID
2210: AND HZ_CUSTOMER_PROFILES.SITE_USE_ID IS NULL
2211: ORDER BY AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE,
2212: AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_DATE_ID
2213: ;
2214:
2958: into l_entity_type,
2959: l_entity_id,
2960: l_customer_id
2961: ,l_site_use_id
2962: from hz_customer_profiles
2963: where cust_account_profile_id = c_customer_profile_id;
2964: /*srw.message(592, 'calling gli for '||l_entity_type||' '||l_entity_id||' '||l_trx_curr_code);*/null;
2965:
2966: oe_credit_check_pvt.currency_list