[Home] [Help]
254: IF p_cust_prof_class_name_from IS NOT NULL OR p_cust_prof_class_name_to IS NOT NULL THEN
255: IF p_cust_prof_class_name_from = p_cust_prof_class_name_to THEN
256: v_cust_prof_class_clause := 'AND EXISTS
257: (SELECT HCP.cust_account_id
258: FROM HZ_CUSTOMER_PROFILES HCP,
259: HZ_CUST_PROFILE_CLASSES HCPC
260: WHERE HCP.profile_class_id = HCPC.profile_class_id
261: AND HCP.cust_account_id = OH.sold_to_org_id
262: AND HCPC.name = :cust_prof_class_from) ';
262: AND HCPC.name = :cust_prof_class_from) ';
263: ELSIF p_cust_prof_class_name_to IS NULL THEN
264: v_cust_prof_class_clause := 'AND EXISTS
265: (SELECT HCP.cust_account_id
266: FROM HZ_CUSTOMER_PROFILES HCP,
267: HZ_CUST_PROFILE_CLASSES HCPC
268: WHERE HCP.profile_class_id = HCPC.profile_class_id
269: AND HCP.cust_account_id = OH.sold_to_org_id
270: AND HCPC.name >= :cust_prof_class_from) ';
270: AND HCPC.name >= :cust_prof_class_from) ';
271: ELSIF p_cust_prof_class_name_from IS NULL THEN
272: v_cust_prof_class_clause := 'AND EXISTS
273: (SELECT HCP.cust_account_id
274: FROM HZ_CUSTOMER_PROFILES HCP,
275: HZ_CUST_PROFILE_CLASSES HCPC
276: WHERE HCP.profile_class_id = HCPC.profile_class_id
277: AND HCP.cust_account_id = OH.sold_to_org_id
278: AND HCPC.name <= :cust_prof_class_to) ';
278: AND HCPC.name <= :cust_prof_class_to) ';
279: ELSE
280: v_cust_prof_class_clause := 'AND EXISTS
281: (SELECT HCP.cust_account_id
282: FROM HZ_CUSTOMER_PROFILES HCP,
283: HZ_CUST_PROFILE_CLASSES HCPC
284: WHERE HCP.profile_class_id = HCPC.profile_class_id
285: AND HCP.cust_account_id = OH.sold_to_org_id
286: AND HCPC.name BETWEEN :cust_prof_class_from AND
1798: SELECT
1799: cpa.currency_code party_currency_code
1800: , cpa.overall_credit_limit party_overall_limit
1801: FROM
1802: hz_customer_profiles cp
1803: , hz_cust_profile_amts cpa
1804: WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
1805: AND cp.site_use_id IS NULL
1806: AND cp.cust_account_id=-1
1839: AND NVL(p_customer_name_high, party.party_name)
1840: AND c.account_number BETWEEN NVL(p_cust_number_low, c.account_number )
1841: AND NVL(p_cust_number_high, c.account_number )
1842: AND c.cust_account_id IN (SELECT cp.cust_account_id
1843: FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
1844: WHERE cp.profile_class_id=cpc.profile_class_id
1845: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
1846: AND NVL(p_prof_class_high, cpc.name));
1847: */
1852: SELECT
1853: cpa.currency_code cust_currency_code
1854: , cpa.overall_credit_limit cust_overall_limit
1855: FROM
1856: hz_customer_profiles cp
1857: , hz_cust_profile_amts cpa
1858: WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
1859: AND cp.site_use_id IS NULL
1860: AND cp.cust_account_id=p_customer_id;
1882: SELECT
1883: cpa.currency_code site_currency_code
1884: , cpa.overall_credit_limit site_overall_limit
1885: FROM
1886: hz_customer_profiles cp
1887: , hz_cust_profile_amts cpa
1888: WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
1889: AND cp.site_use_id =p_site_id
1890: AND cp.cust_account_id=p_customer_id;
2223: -----check if the party has any credit profiles
2224: SELECT COUNT(*)
2225: INTO l_prof_count
2226: FROM
2227: hz_customer_profiles cp
2228: , hz_cust_profile_amts cpa
2229: WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
2230: AND cp.site_use_id IS NULL
2231: AND cp.cust_account_id=-1
2562: -----check if the party has any credit profiles
2563: SELECT COUNT(*)
2564: INTO l_prof_count
2565: FROM
2566: hz_customer_profiles cp
2567: , hz_cust_profile_amts cpa
2568: WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
2569: AND cp.site_use_id IS NULL
2570: AND cp.cust_account_id=-1
2859: -----check if the customer has any credit profiles
2860: SELECT COUNT(*)
2861: INTO l_prof_count
2862: FROM
2863: hz_customer_profiles cp
2864: , hz_cust_profile_amts cpa
2865: WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
2866: AND cp.site_use_id IS NULL
2867: AND cp.cust_account_id=party_cust_csr_rec.customer_id;
3163: -----check if the party has any credit profiles
3164: SELECT COUNT(*)
3165: INTO l_prof_count
3166: FROM
3167: hz_customer_profiles cp
3168: , hz_cust_profile_amts cpa
3169: WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
3170: AND cp.site_use_id IS NULL
3171: AND cp.cust_account_id=-1
3460: -----check if the customer has any credit profiles
3461: SELECT COUNT(*)
3462: INTO l_prof_count
3463: FROM
3464: hz_customer_profiles cp
3465: , hz_cust_profile_amts cpa
3466: WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
3467: AND cp.site_use_id IS NULL
3468: AND cp.cust_account_id=party_cust_csr_rec.customer_id;
3576: AND NVL(p_customer_name_high, party.party_name)
3577: AND c.account_number BETWEEN p_cust_number_low
3578: AND NVL(p_cust_number_high, c.account_number )
3579: AND c.cust_account_id IN (SELECT cp.cust_account_id
3580: FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
3581: WHERE cp.profile_class_id=cpc.profile_class_id
3582: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3583: AND NVL(p_prof_class_high, cpc.name)) ;
3584: ELSIF (p_customer_name_high IS NOT NULL) AND (p_cust_number_low IS NOT NULL) THEN
3598: AND party.party_name <= p_customer_name_high
3599: AND c.account_number BETWEEN p_cust_number_low
3600: AND NVL(p_cust_number_high, c.account_number )
3601: AND c.cust_account_id IN (SELECT cp.cust_account_id
3602: FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
3603: WHERE cp.profile_class_id=cpc.profile_class_id
3604: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3605: AND NVL(p_prof_class_high, cpc.name)) ;
3606: ELSIF (p_customer_name_low IS NOT NULL) AND (p_cust_number_high IS NOT NULL) THEN
3620: AND party.party_name BETWEEN p_customer_name_low
3621: AND NVL(p_customer_name_high, party.party_name)
3622: AND c.account_number <=p_cust_number_high
3623: AND c.cust_account_id IN (SELECT cp.cust_account_id
3624: FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
3625: WHERE cp.profile_class_id=cpc.profile_class_id
3626: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3627: AND NVL(p_prof_class_high, cpc.name)) ;
3628: ELSIF (p_customer_name_high IS NOT NULL) AND (p_cust_number_high IS NOT NULL) THEN
3641: AND c.party_id = party.party_id
3642: AND party.party_name <= p_customer_name_high
3643: AND c.account_number <= p_cust_number_high
3644: AND c.cust_account_id IN (SELECT cp.cust_account_id
3645: FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
3646: WHERE cp.profile_class_id=cpc.profile_class_id
3647: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3648: AND NVL(p_prof_class_high, cpc.name)) ;
3649: ELSIF (p_customer_name_low IS NOT NULL) THEN
3661: AND c.party_id = party.party_id
3662: AND party.party_name BETWEEN p_customer_name_low
3663: AND NVL(p_customer_name_high, party.party_name)
3664: AND c.cust_account_id IN (SELECT cp.cust_account_id
3665: FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
3666: WHERE cp.profile_class_id=cpc.profile_class_id
3667: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3668: AND NVL(p_prof_class_high, cpc.name)) ;
3669: ELSIF (p_customer_name_high IS NOT NULL) THEN
3680: WHERE c.status='A'
3681: AND c.party_id = party.party_id
3682: AND party.party_name <= p_customer_name_high
3683: AND c.cust_account_id IN (SELECT cp.cust_account_id
3684: FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
3685: WHERE cp.profile_class_id=cpc.profile_class_id
3686: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3687: AND NVL(p_prof_class_high, cpc.name)) ;
3688: ELSIF (p_cust_number_low IS NOT NULL) THEN
3700: AND c.party_id = party.party_id
3701: AND c.account_number BETWEEN p_cust_number_low
3702: AND NVL(p_cust_number_high, c.account_number )
3703: AND c.cust_account_id IN (SELECT cp.cust_account_id
3704: FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
3705: WHERE cp.profile_class_id=cpc.profile_class_id
3706: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3707: AND NVL(p_prof_class_high, cpc.name)) ;
3708: ELSIF (p_cust_number_high IS NOT NULL) THEN
3719: WHERE c.status='A'
3720: AND c.party_id = party.party_id
3721: AND c.account_number <= p_cust_number_high
3722: AND c.cust_account_id IN (SELECT cp.cust_account_id
3723: FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
3724: WHERE cp.profile_class_id=cpc.profile_class_id
3725: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3726: AND NVL(p_prof_class_high, cpc.name)) ;
3727: ELSIF (p_customer_name_low IS NULL) AND (p_customer_name_high IS NULL) AND
3741: , hz_parties party
3742: WHERE c.status='A'
3743: AND c.party_id = party.party_id
3744: AND c.cust_account_id IN (SELECT cp.cust_account_id
3745: FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
3746: WHERE cp.profile_class_id=cpc.profile_class_id
3747: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3748: AND NVL(p_prof_class_high, cpc.name)) ;
3749: END IF ;
3961: -----check if the customer has any credit profiles
3962: SELECT COUNT(*)
3963: INTO l_prof_count
3964: FROM
3965: hz_customer_profiles cp
3966: , hz_cust_profile_amts cpa
3967: WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
3968: AND cp.site_use_id IS NULL
3969: AND cp.cust_account_id=cust_csr_rec.customer_id;
4241: -----check if the customer site has any credit profiles
4242: SELECT COUNT(*)
4243: INTO l_prof_count1
4244: FROM
4245: hz_customer_profiles cp
4246: , hz_cust_profile_amts cpa
4247: WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
4248: AND cp.site_use_id =site_csr_rec.site_id
4249: AND cp.cust_account_id=cust_csr_rec.customer_id;