PACKAGE BODY: APPS.PAY_KR_NPA_FUNC_PKG

Source

1 package body pay_kr_npa_func_pkg as
2 /* $Header: pykrnpaf.pkb 120.0 2005/05/29 06:26:24 appldev noship $ */
3 --
4 -- get_bp_list: Get a comma separated list of business places
5 --              that report to the same National Pension office
6 --              in a business group
7 function get_bp_list(
8 	p_business_group_id	in		hr_organization_units.organization_id%type,
9 	p_bp_np_number		in		hr_organization_information.org_information1%type
10 ) return varchar2
11 is
12 	cursor csr_bp_names is
13 		select distinct
14 			hou.name bp_name
15 		from
16 			hr_organization_units hou,
17 			hr_organization_information npi
18 		where
19 			hou.business_group_id		= p_business_group_id
20 			and npi.organization_id 	= hou.organization_id
21 			and npi.org_information_context = 'KR_NP_INFORMATION'
22 			and npi.org_information1	= p_bp_np_number
23 		order by
24 			1 ;
25 	r_bp_list	varchar2(260) ;
26 	l_trunc_len	number ;
27 	rec		csr_bp_names%rowtype ;
28 begin
29 	l_trunc_len := 256 ;
30 	r_bp_list := '' ;
31 	--
32 	open csr_bp_names ;
33 	loop
34 		fetch csr_bp_names into rec ;
35 		exit when csr_bp_names%notfound ;
36 		if length(r_bp_list) = l_trunc_len then
37 			exit ;
38 		end if ;
39 		r_bp_list := substr(r_bp_list || rec.bp_name, 	0, l_trunc_len) ;
40 		r_bp_list := substr(r_bp_list || ', ', 		0, l_trunc_len) ;
41 	end loop ;
42 	close csr_bp_names ;
43 	--
44 	r_bp_list := rtrim(r_bp_list, ', ') ;
45 	--
46 	return r_bp_list ;
47 	--
48 end get_bp_list ;
49 --
50 end pay_kr_npa_func_pkg ;