DBA Data[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;