1 PACKAGE BODY hr_accrual_plan_tl AS
2 /* $Header: perpaptl.pkb 120.0.12020000.2 2013/02/12 09:22:05 srannama noship $ */
3 PROCEDURE ins
4 (p_accrual_plan_id IN pay_accrual_plans_tl.accrual_plan_id%TYPE
5 ,p_accrual_plan_name IN pay_accrual_plans_tl.accrual_plan_name%TYPE
6 ,p_description IN pay_accrual_plans_tl.description%TYPE DEFAULT NULL) IS
7 BEGIN
8 INSERT
9 INTO pay_accrual_plans_tl
10 (accrual_plan_id
11 ,language
12 ,source_lang
13 ,accrual_plan_name
14 ,description)
15 SELECT p_accrual_plan_id
16 ,l.language_code
17 ,userenv ('LANG')
18 ,p_accrual_plan_name
19 ,p_description
20 FROM fnd_languages l
21 WHERE l.installed_flag IN ('I','B')
22 AND NOT EXISTS
23 (
24 SELECT NULL
25 FROM pay_accrual_plans_tl t
26 WHERE t.accrual_plan_id = p_accrual_plan_id
27 AND t.language = l.language_code
28 );
29 END ins;
30
31 PROCEDURE upd
32 (p_accrual_plan_id IN pay_accrual_plans_tl.accrual_plan_id%TYPE
33 ,p_description IN pay_accrual_plans_tl.description%TYPE DEFAULT NULL) IS
34 BEGIN
35 UPDATE pay_accrual_plans_tl
36 SET description = p_description
37 ,source_lang = userenv ('LANG')
38 WHERE accrual_plan_id = p_accrual_plan_id
39 AND userenv ('LANG') IN (language,source_lang);
40 END upd;
41
42 PROCEDURE del
43 (p_accrual_plan_id IN pay_accrual_plans_tl.accrual_plan_id%TYPE) IS
44 BEGIN
45 DELETE
46 FROM pay_accrual_plans_tl
47 WHERE accrual_plan_id = p_accrual_plan_id;
48 END del;
49
50 PROCEDURE add_language IS
51 BEGIN
52 DELETE
53 FROM pay_accrual_plans_tl t
54 WHERE NOT EXISTS
55 (
56 SELECT NULL
57 FROM pay_accrual_plans b
58 WHERE b.accrual_plan_id = t.accrual_plan_id
59 );
60
61 UPDATE pay_accrual_plans_tl t
62 SET (accrual_plan_name
63 ,description) =
64 (
65 SELECT b.accrual_plan_name
66 ,b.description
67 FROM pay_accrual_plans_tl b
68 WHERE b.accrual_plan_id = t.accrual_plan_id
69 AND b.language = t.source_lang
70 )
71 WHERE (t.accrual_plan_id,t.language) IN
72 (
73 SELECT subt.accrual_plan_id
74 ,subt.language
75 FROM pay_accrual_plans_tl subb
76 ,pay_accrual_plans_tl subt
77 WHERE subb.accrual_plan_id = subt.accrual_plan_id
78 AND subb.language = subt.source_lang
79 AND (
80 subb.accrual_plan_name <> subt.accrual_plan_name
81 OR subb.description <> subt.description
82 OR (
83 subb.description IS NULL
84 AND subt.description IS NOT NULL
85 )
86 OR (
87 subb.description IS NOT NULL
88 AND subt.description IS NULL
89 )
90 )
91 );
92
93 INSERT
94 INTO pay_accrual_plans_tl
95 (accrual_plan_id
96 ,accrual_plan_name
97 ,description
98 ,language
99 ,source_lang)
100 SELECT b.accrual_plan_id
101 ,b.accrual_plan_name
102 ,b.description
103 ,l.language_code
104 ,b.source_lang
105 FROM pay_accrual_plans_tl b
106 ,fnd_languages l
107 WHERE l.installed_flag IN ('I','B')
108 AND b.language = userenv ('LANG')
109 AND NOT EXISTS
110 (
111 SELECT NULL
112 FROM pay_accrual_plans_tl t
113 WHERE t.accrual_plan_id = b.accrual_plan_id
114 AND t.language = l.language_code
115 );
116 END add_language;
117
118 PROCEDURE validate_translations
119 (p_accrual_plan_id IN number
120 ,language IN varchar2
121 ,p_accrual_plan_name IN varchar2
122 ,p_description IN varchar2 DEFAULT NULL) IS
123
124 CURSOR check_dup
125 (p_business_group_id IN number) IS
126 SELECT 'Y'
127 FROM pay_accrual_plans
128 WHERE accrual_plan_name = p_accrual_plan_name
129 AND business_group_id = p_business_group_id
130 AND (
131 p_accrual_plan_id IS NULL
132 OR p_accrual_plan_id <> accrual_plan_id
133 );
134
135 CURSOR check_dup_tl
136 (p_business_group_id IN number) IS
137 SELECT 'Y'
138 FROM pay_accrual_plans
139 WHERE business_group_id = p_business_group_id
140 AND accrual_plan_id =
141 (
142 SELECT DISTINCT
143 accrual_plan_id
144 FROM pay_accrual_plans_tl
145 WHERE accrual_plan_name = p_accrual_plan_name
146 AND (
147 p_accrual_plan_id IS NULL
148 OR p_accrual_plan_id <> accrual_plan_id
149 )
150 );
151 l_exists varchar2(1);
152 BEGIN
153 OPEN check_dup (hr_general.get_business_group_id);
154 FETCH check_dup INTO l_exists;
155 CLOSE check_dup;
156
157 IF nvl (l_exists,'N') = 'Y' THEN
158 fnd_message.set_name ('PAY','HR_13163_PTO_DUP_PLAN_NAME');
159 fnd_message.raise_error;
160 ELSE
161 OPEN check_dup_tl (hr_general.get_business_group_id);
162 FETCH check_dup_tl INTO l_exists;
163 CLOSE check_dup_tl;
164
165 IF nvl (l_exists,'N') = 'Y' THEN
166 fnd_message.set_name ('PAY','HR_13163_PTO_DUP_PLAN_NAME');
167 fnd_message.raise_error;
168 END IF;
169 END IF;
170 END validate_translations;
171
172 END hr_accrual_plan_tl;
[Home] [Help]