[Home] [Help]
PACKAGE BODY: APPS.PJM_TASK_ATTR_USAGES_PKG
Source
1 PACKAGE BODY PJM_TASK_ATTR_USAGES_PKG AS
2 /* $Header: PJMPTAUB.pls 115.3 2002/10/29 20:15:07 alaw noship $ */
3 PROCEDURE INSERT_ROW
4 ( X_ROWID IN OUT NOCOPY VARCHAR2
5 , X_ASSIGNMENT_TYPE IN VARCHAR2
6 , X_ATTRIBUTE_CODE IN VARCHAR2
7 , X_SEQUENCE_NUMBER IN NUMBER
8 , X_PROMPT IN VARCHAR2
9 , X_CREATION_DATE IN DATE
10 , X_CREATED_BY IN NUMBER
11 , X_LAST_UPDATE_DATE IN DATE
12 , X_LAST_UPDATED_BY IN NUMBER
13 , X_LAST_UPDATE_LOGIN IN NUMBER
14 ) IS
15
16 CURSOR c IS
17 SELECT ROWID
18 FROM PJM_TASK_ATTR_USAGES_B
19 WHERE ASSIGNMENT_TYPE = X_ASSIGNMENT_TYPE
20 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE;
21
22 BEGIN
23
24 INSERT INTO PJM_TASK_ATTR_USAGES_B
25 ( ASSIGNMENT_TYPE
26 , ATTRIBUTE_CODE
27 , SEQUENCE_NUMBER
28 , CREATION_DATE
29 , CREATED_BY
30 , LAST_UPDATE_DATE
31 , LAST_UPDATED_BY
32 , LAST_UPDATE_LOGIN
33 ) VALUES
34 ( X_ASSIGNMENT_TYPE
35 , X_ATTRIBUTE_CODE
36 , X_SEQUENCE_NUMBER
37 , X_CREATION_DATE
38 , X_CREATED_BY
39 , X_LAST_UPDATE_DATE
40 , X_LAST_UPDATED_BY
41 , X_LAST_UPDATE_LOGIN
42 );
43
44 INSERT INTO PJM_TASK_ATTR_USAGES_TL
45 ( ASSIGNMENT_TYPE
46 , ATTRIBUTE_CODE
47 , CREATION_DATE
48 , CREATED_BY
49 , LAST_UPDATE_DATE
50 , LAST_UPDATED_BY
51 , LAST_UPDATE_LOGIN
52 , PROMPT
53 , LANGUAGE
54 , SOURCE_LANG
55 )
56 SELECT X_ASSIGNMENT_TYPE
57 , X_ATTRIBUTE_CODE
58 , X_CREATION_DATE
59 , X_CREATED_BY
60 , X_LAST_UPDATE_DATE
61 , X_LAST_UPDATED_BY
62 , X_LAST_UPDATE_LOGIN
63 , DECODE(A.LANGUAGE , USERENV('LANG') , X_PROMPT , A.ATTRIBUTE_NAME)
64 , A.LANGUAGE
65 , A.SOURCE_LANG
66 FROM FND_LANGUAGES L
67 , PJM_TASK_ATTRIBUTES_TL A
68 WHERE L.INSTALLED_FLAG IN ( 'I' , 'B' )
69 AND A.LANGUAGE = L.LANGUAGE_CODE
70 AND A.ASSIGNMENT_TYPE = X_ASSIGNMENT_TYPE
71 AND A.ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
72 AND NOT EXISTS (
73 SELECT NULL
74 FROM PJM_TASK_ATTR_USAGES_TL T
75 WHERE T.ASSIGNMENT_TYPE = X_ASSIGNMENT_TYPE
76 AND T.ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
77 AND T.LANGUAGE = L.LANGUAGE_CODE);
78
79 OPEN c;
80 FETCH c INTO X_ROWID;
81 IF (c%notfound) THEN
82 CLOSE c;
83 RAISE no_data_found;
84 END IF;
85 CLOSE c;
86
87 END INSERT_ROW;
88
89 PROCEDURE LOCK_ROW
90 ( X_ASSIGNMENT_TYPE IN VARCHAR2
91 , X_ATTRIBUTE_CODE IN VARCHAR2
92 , X_SEQUENCE_NUMBER IN NUMBER
93 , X_PROMPT IN VARCHAR2
94 ) IS
95
96 CURSOR c IS
97 SELECT SEQUENCE_NUMBER
98 FROM PJM_TASK_ATTR_USAGES_B
99 WHERE ASSIGNMENT_TYPE = X_ASSIGNMENT_TYPE
100 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
101 FOR UPDATE OF ASSIGNMENT_TYPE NOWAIT;
102 recinfo c%rowtype;
103
104 CURSOR c1 IS
105 SELECT PROMPT , DECODE(LANGUAGE , USERENV('LANG') , 'Y' , 'N') BASELANG
106 FROM PJM_TASK_ATTR_USAGES_TL
107 WHERE ASSIGNMENT_TYPE = X_ASSIGNMENT_TYPE
108 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
109 AND USERENV('LANG') IN ( LANGUAGE , SOURCE_LANG )
110 FOR UPDATE OF ASSIGNMENT_TYPE NOWAIT;
111
112 BEGIN
113
114 OPEN c;
115 FETCH c INTO recinfo;
116 IF (c%notfound) THEN
117 CLOSE c;
118 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
119 APP_EXCEPTION.RAISE_EXCEPTION;
120 END IF;
121 CLOSE c;
122
123 IF ( (recinfo.SEQUENCE_NUMBER = X_SEQUENCE_NUMBER)
124 ) THEN
125 NULL;
126 ELSE
127 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
128 APP_EXCEPTION.RAISE_EXCEPTION;
129 END IF;
130
131 FOR tlinfo IN c1 LOOP
132 IF (tlinfo.BASELANG = 'Y') then
133 IF ( ((tlinfo.PROMPT = X_PROMPT)
134 OR ((tlinfo.PROMPT is null) AND (X_PROMPT is null)))
135 ) THEN
136 NULL;
137 ELSE
138 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
139 APP_EXCEPTION.RAISE_EXCEPTION;
140 END IF;
141 END IF;
142 END LOOP;
143
144 RETURN;
145
146 END LOCK_ROW;
147
148 PROCEDURE UPDATE_ROW
149 ( X_ASSIGNMENT_TYPE IN VARCHAR2
150 , X_ATTRIBUTE_CODE IN VARCHAR2
151 , X_SEQUENCE_NUMBER IN NUMBER
152 , X_PROMPT IN VARCHAR2
153 , X_LAST_UPDATE_DATE IN DATE
154 , X_LAST_UPDATED_BY IN NUMBER
155 , X_LAST_UPDATE_LOGIN IN NUMBER
156 ) IS
157 BEGIN
158
159 UPDATE PJM_TASK_ATTR_USAGES_B
160 SET SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
161 , LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
162 , LAST_UPDATED_BY = X_LAST_UPDATED_BY
163 , LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
164 WHERE ASSIGNMENT_TYPE = X_ASSIGNMENT_TYPE
165 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE;
166
167 IF (sql%notfound) THEN
168 RAISE no_data_found;
169 END IF;
170
171 UPDATE PJM_TASK_ATTR_USAGES_TL
172 SET PROMPT = X_PROMPT
173 , LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
174 , LAST_UPDATED_BY = X_LAST_UPDATED_BY
175 , LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
176 , SOURCE_LANG = USERENV('LANG')
177 WHERE ASSIGNMENT_TYPE = X_ASSIGNMENT_TYPE
178 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
179 AND USERENV('LANG') IN ( LANGUAGE , SOURCE_LANG );
180
181 IF (sql%notfound) THEN
182 RAISE no_data_found;
183 END IF;
184
185 END UPDATE_ROW;
186
187 PROCEDURE DELETE_ROW
188 ( X_ASSIGNMENT_TYPE IN VARCHAR2
189 , X_ATTRIBUTE_CODE IN VARCHAR2
190 ) IS
191 BEGIN
192
193 DELETE FROM PJM_TASK_ATTR_USAGES_TL
194 WHERE ASSIGNMENT_TYPE = X_ASSIGNMENT_TYPE
195 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE;
196
197 IF (sql%notfound) THEN
198 RAISE no_data_found;
199 END IF;
200
201 DELETE FROM PJM_TASK_ATTR_USAGES_B
202 WHERE ASSIGNMENT_TYPE = X_ASSIGNMENT_TYPE
203 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE;
204
205 IF (sql%notfound) THEN
206 RAISE no_data_found;
207 END IF;
208
209 END DELETE_ROW;
210
211 PROCEDURE ADD_LANGUAGE
212 IS
213 BEGIN
214
215 DELETE FROM PJM_TASK_ATTR_USAGES_TL T
216 WHERE NOT EXISTS (
217 SELECT NULL
218 FROM PJM_TASK_ATTR_USAGES_B B
219 WHERE B.ASSIGNMENT_TYPE = T.ASSIGNMENT_TYPE
220 AND B.ATTRIBUTE_CODE = T.ATTRIBUTE_CODE
221 );
222
223 UPDATE PJM_TASK_ATTR_USAGES_TL T
224 SET ( PROMPT ) = (
225 SELECT B.PROMPT
226 FROM PJM_TASK_ATTR_USAGES_TL B
227 WHERE B.ASSIGNMENT_TYPE = T.ASSIGNMENT_TYPE
228 AND B.ATTRIBUTE_CODE = T.ATTRIBUTE_CODE
229 AND B.LANGUAGE = T.SOURCE_LANG)
230 WHERE ( T.ASSIGNMENT_TYPE
231 , T.ATTRIBUTE_CODE
232 , T.LANGUAGE
233 ) IN (
234 SELECT SUBT.ASSIGNMENT_TYPE
235 , SUBT.ATTRIBUTE_CODE
236 , SUBT.LANGUAGE
237 FROM PJM_TASK_ATTR_USAGES_TL SUBB
238 , PJM_TASK_ATTR_USAGES_TL SUBT
239 WHERE SUBB.ASSIGNMENT_TYPE = SUBT.ASSIGNMENT_TYPE
240 AND SUBB.ATTRIBUTE_CODE = SUBT.ATTRIBUTE_CODE
241 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
242 AND (SUBB.PROMPT <> SUBT.PROMPT
243 OR (SUBB.PROMPT IS NULL AND SUBT.PROMPT IS NOT NULL)
244 OR (SUBB.PROMPT IS NOT NULL AND SUBT.PROMPT IS NULL)
245 ));
246
247 INSERT INTO PJM_TASK_ATTR_USAGES_TL
248 ( ASSIGNMENT_TYPE
249 , ATTRIBUTE_CODE
250 , CREATION_DATE
251 , CREATED_BY
252 , LAST_UPDATE_DATE
253 , LAST_UPDATED_BY
254 , LAST_UPDATE_LOGIN
255 , PROMPT
256 , LANGUAGE
257 , SOURCE_LANG
258 )
259 SELECT A.ASSIGNMENT_TYPE
260 , A.ATTRIBUTE_CODE
261 , A.CREATION_DATE
262 , A.CREATED_BY
263 , A.LAST_UPDATE_DATE
264 , A.LAST_UPDATED_BY
265 , A.LAST_UPDATE_LOGIN
266 , A.ATTRIBUTE_NAME
267 , A.LANGUAGE
268 , A.SOURCE_LANG
269 FROM PJM_TASK_ATTRIBUTES_TL A
270 , PJM_TASK_ATTR_USAGES_B B
271 , FND_LANGUAGES L
272 WHERE L.INSTALLED_FLAG IN ( 'I' , 'B' )
273 AND A.LANGUAGE = L.LANGUAGE_CODE
274 AND B.ASSIGNMENT_TYPE = A.ASSIGNMENT_TYPE
275 AND B.ATTRIBUTE_CODE = A.ATTRIBUTE_CODE
276 AND NOT EXISTS (
277 SELECT NULL
278 FROM PJM_TASK_ATTR_USAGES_TL T
279 WHERE T.ASSIGNMENT_TYPE = B.ASSIGNMENT_TYPE
280 AND T.ATTRIBUTE_CODE = B.ATTRIBUTE_CODE
281 AND T.LANGUAGE = L.LANGUAGE_CODE
282 );
283
284 END ADD_LANGUAGE;
285
286 END PJM_TASK_ATTR_USAGES_PKG;