[Home] [Help]
PACKAGE BODY: APPS.PAY_CUST_RESTRICTIONS_PKG
Source
1 PACKAGE BODY PAY_CUST_RESTRICTIONS_PKG as
2 /* $Header: pypcr01t.pkb 115.8 2003/07/02 05:56:37 tvankayl ship $ */
3 --
4 procedure unique_chk(x_form_name in VARCHAR2, x_name in VARCHAR2,X_LEGISLATION_CODE in VARCHAR2)
5 is
6 result varchar2(255);
7 Begin
8 SELECT count(*) INTO result
9 FROM PAY_CUSTOMIZED_RESTRICTIONS
10 WHERE UPPER(FORM_NAME) = UPPER(x_form_name)
11 and UPPER(NAME) = UPPER(x_name)
12 and BUSINESS_GROUP_ID is NULL
13 and nvl(LEGISLATION_CODE,'~null~') = nvl(X_LEGISLATION_CODE,'~null~');
14 --
15 IF (result>1) THEN
16 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
17 hr_utility.set_message_token('PROCEDURE','PAY_CUST_RESTRICTIONS_PKG.UNIQUE_CHK');
18 hr_utility.set_message_token('STEP','1');
19 hr_utility.raise_error;
20 END IF;
21 EXCEPTION
22 when NO_DATA_FOUND then
23 null;
24 end unique_chk;
25 --
26
27 PROCEDURE LOAD_ROW( X_APPLICATION_SHORT_NAME VARCHAR2,
28 X_LEGISLATION_CODE VARCHAR2,
29 X_FORM_NAME VARCHAR2,
30 X_NAME VARCHAR2,
31 X_ENABLED_FLAG VARCHAR2,
32 X_COMMENTS VARCHAR2,
33 X_LEGISLATION_SUBGROUP VARCHAR2,
34 X_OWNER VARCHAR2,
35 X_QUERY_FORM_TITLE VARCHAR2,
36 X_STANDARD_FORM_TITLE VARCHAR2
37 )
38 is
39 l_proc VARCHAR2(61) := 'PAY_CUST_RESTRICTIONS_PKG.LOAD_ROW';
40 l_rowid rowid;
41 l_created_by PAY_CUSTOMIZED_RESTRICTIONS.created_by%TYPE := 0;
42 l_creation_date PAY_CUSTOMIZED_RESTRICTIONS.creation_date%TYPE := SYSDATE;
43 l_last_update_date PAY_CUSTOMIZED_RESTRICTIONS.last_update_date%TYPE := SYSDATE;
44 l_last_updated_by PAY_CUSTOMIZED_RESTRICTIONS.last_updated_by%TYPE := 0;
45 l_last_update_login PAY_CUSTOMIZED_RESTRICTIONS.last_update_login%TYPE := 0;
46 l_cust_rest_id PAY_CUSTOMIZED_RESTRICTIONS.customized_restriction_id%TYPE ;
47 l_appl_id PAY_CUSTOMIZED_RESTRICTIONS.application_id%TYPE;
48 l_legislation_subgroup PAY_CUSTOMIZED_RESTRICTIONS.legislation_subgroup%TYPE;
49
50
51
52 CURSOR C_APPL IS
53 select application_id
54 from fnd_application
55 where application_short_name = upper(X_APPLICATION_SHORT_NAME);
56
57 CURSOR C1 IS
58 select customized_restriction_id,application_id,legislation_subgroup
59 from PAY_CUSTOMIZED_RESTRICTIONS pcr
60 where upper(pcr.form_name) = upper(X_FORM_NAME)
61 and upper(pcr.name) = upper(X_NAME)
62 and pcr.business_group_id is null
63 and nvl(pcr.legislation_code,'~null~') = nvl(X_LEGISLATION_CODE,'~null~');
64
65
66 begin
67
68 -- Translate developer keys to internal parameters
69
70 if X_OWNER = 'SEED' then
71 l_created_by := 1;
72 l_last_updated_by := 1;
73 end if;
74
75 -- Update or insert row as appropriate
76 begin
77
78
79 OPEN C1;
80 FETCH C1 INTO l_cust_rest_id , l_appl_id, l_legislation_subgroup;
81 IF C1%NOTFOUND THEN
82 close C1;
83 raise no_data_found;
84 ELSE
85 close C1;
86 END IF;
87
88
89 PER_CUSTOMIZED_RESTR_PKG.UPDATE_ROW
90 ( X_CUSTOMIZED_RESTRICTION_ID => l_cust_rest_id
91 ,X_APPLICATION_ID => l_appl_id
92 ,X_FORM_NAME => X_FORM_NAME
93 ,X_NAME => X_NAME
94 ,X_BUSINESS_GROUP_ID => null
95 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE
96 ,X_ENABLED_FLAG => X_ENABLED_FLAG
97 ,X_QUERY_FORM_TITLE => X_QUERY_FORM_TITLE
98 ,X_STANDARD_FORM_TITLE => X_STANDARD_FORM_TITLE
99 ,X_COMMENTS => X_COMMENTS
100 ,X_LEGISLATION_SUBGROUP => nvl(X_LEGISLATION_SUBGROUP,l_legislation_subgroup)
101 ,X_LAST_UPDATE_DATE => l_last_update_date
102 ,X_LAST_UPDATED_BY => l_last_updated_by
103 ,X_LAST_UPDATE_LOGIN => l_last_update_login
104 );
105 exception
106 when no_data_found then
107
108 OPEN C_APPL;
109 FETCH C_APPL INTO l_appl_id;
110 CLOSE C_APPL;
111
112
113 PER_CUSTOMIZED_RESTR_PKG.INSERT_ROW
114 (X_ROWID => l_rowid
115 ,X_CUSTOMIZED_RESTRICTION_ID => l_cust_rest_id
116 ,X_APPLICATION_ID => l_appl_id
117 ,X_FORM_NAME => X_FORM_NAME
118 ,X_NAME => X_NAME
119 ,X_BUSINESS_GROUP_ID => null
120 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE
121 ,X_ENABLED_FLAG => X_ENABLED_FLAG
122 ,X_QUERY_FORM_TITLE => X_QUERY_FORM_TITLE
123 ,X_STANDARD_FORM_TITLE => X_STANDARD_FORM_TITLE
124 ,X_COMMENTS => X_COMMENTS
125 ,X_LEGISLATION_SUBGROUP => X_LEGISLATION_SUBGROUP
126 ,X_CREATED_BY => l_created_by
127 ,X_CREATION_DATE => l_creation_date
128 ,X_LAST_UPDATE_DATE => l_last_update_date
129 ,X_LAST_UPDATED_BY => l_last_updated_by
130 ,X_LAST_UPDATE_LOGIN => l_last_update_login
131 );
132 end;
133 end LOAD_ROW;
134
135
136 PROCEDURE TRANSLATE_ROW( X_LEGISLATION_CODE VARCHAR2,
137 X_FORM_NAME VARCHAR2,
138 X_NAME VARCHAR2,
139 X_OWNER VARCHAR2,
140 X_QUERY_FORM_TITLE VARCHAR2,
141 X_STANDARD_FORM_TITLE VARCHAR2 )
142 is
143
144 l_cust_rest_id PAY_CUSTOMIZED_RESTRICTIONS.customized_restriction_id%TYPE ;
145
146 CURSOR C1 IS
147 select customized_restriction_id
148 from PAY_CUSTOMIZED_RESTRICTIONS pcr
149 where upper(pcr.form_name) = upper(X_FORM_NAME)
150 and upper(pcr.name) = upper(X_NAME)
151 and pcr.business_group_id is null
152 and nvl(pcr.legislation_code,'~null~') = nvl(X_LEGISLATION_CODE,'~null~') ;
153 begin
154 -- unique_chk(X_FORM_NAME,X_NAME,X_LEGISLATION_CODE);
155 --
156
157
158 OPEN C1;
159 FETCH C1 INTO l_cust_rest_id ;
160 if C1%FOUND then
161 UPDATE PAY_CUSTOM_RESTRICTIONS_TL
162 SET QUERY_FORM_TITLE=nvl(X_QUERY_FORM_TITLE,QUERY_FORM_TITLE),
163 STANDARD_FORM_TITLE=nvl(X_STANDARD_FORM_TITLE,STANDARD_FORM_TITLE),
164 last_update_date = SYSDATE,
165 last_updated_by = decode(x_owner,'SEED',1,0),
166 last_update_login = 0,
167 SOURCE_LANG = userenv('LANG')
168 WHERE customized_restriction_id = l_cust_rest_id
169 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
170 END IF;
171
172 CLOSE C1;
173
174 --
175 if (sql%notfound) then -- trap system errors during update
176 -- hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
177 -- hr_utility.set_message_token ('PROCEDURE','PAY_CUST_RESTRICTIONS_PKG.TRANSLATE_ROW');
178 -- hr_utility.set_message_token('STEP','1');
179 -- hr_utility.raise_error;
180 null;
181 end if;
182 end TRANSLATE_ROW;
183
184 -----------------------------------------------------------------------------
185 END PAY_CUST_RESTRICTIONS_PKG;