1 PACKAGE BODY po_job_associations_pkg AS
2 /* $Header: POXTIJAB.pls 115.0 2003/09/08 19:58:25 tpoon noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_JOB_ID in NUMBER,
7 X_CATEGORY_ID in NUMBER,
8 X_INACTIVE_DATE in DATE,
9 X_JOB_DESCRIPTION in VARCHAR2,
10 X_JOB_LONG_DESCRIPTION in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17 cursor C is select ROWID from PO_JOB_ASSOCIATIONS_B
18 where JOB_ID = X_JOB_ID
19 ;
20 begin
21 insert into PO_JOB_ASSOCIATIONS_B (
22 JOB_ID,
23 CATEGORY_ID,
24 INACTIVE_DATE,
25 CREATION_DATE,
26 CREATED_BY,
27 LAST_UPDATE_DATE,
28 LAST_UPDATED_BY,
29 LAST_UPDATE_LOGIN
30 ) values (
31 X_JOB_ID,
32 X_CATEGORY_ID,
33 X_INACTIVE_DATE,
34 X_CREATION_DATE,
35 X_CREATED_BY,
36 X_LAST_UPDATE_DATE,
37 X_LAST_UPDATED_BY,
38 X_LAST_UPDATE_LOGIN
39 );
40
41 insert into PO_JOB_ASSOCIATIONS_TL (
42 JOB_ID,
43 JOB_DESCRIPTION,
44 JOB_LONG_DESCRIPTION,
45 LAST_UPDATE_DATE,
46 LAST_UPDATED_BY,
47 LAST_UPDATE_LOGIN,
48 CREATED_BY,
49 CREATION_DATE,
50 LANGUAGE,
51 SOURCE_LANG
52 ) select
53 X_JOB_ID,
54 X_JOB_DESCRIPTION,
55 X_JOB_LONG_DESCRIPTION,
56 X_LAST_UPDATE_DATE,
57 X_LAST_UPDATED_BY,
58 X_LAST_UPDATE_LOGIN,
59 X_CREATED_BY,
60 X_CREATION_DATE,
61 L.LANGUAGE_CODE,
62 userenv('LANG')
63 from FND_LANGUAGES L
64 where L.INSTALLED_FLAG in ('I', 'B')
65 and not exists
66 (select NULL
67 from PO_JOB_ASSOCIATIONS_TL T
68 where T.JOB_ID = X_JOB_ID
69 and T.LANGUAGE = L.LANGUAGE_CODE);
70
71 open c;
72 fetch c into X_ROWID;
73 if (c%notfound) then
74 close c;
75 raise no_data_found;
76 end if;
77 close c;
78
79 end INSERT_ROW;
80
81 procedure LOCK_ROW (
82 X_JOB_ID in NUMBER,
83 X_CATEGORY_ID in NUMBER,
84 X_INACTIVE_DATE in DATE,
85 X_JOB_DESCRIPTION in VARCHAR2,
86 X_JOB_LONG_DESCRIPTION in VARCHAR2
87 ) is
88 cursor c is select
89 CATEGORY_ID,
90 INACTIVE_DATE
91 from PO_JOB_ASSOCIATIONS_B
92 where JOB_ID = X_JOB_ID
93 for update of JOB_ID nowait;
94 recinfo c%rowtype;
95
96 cursor c1 is select
97 JOB_DESCRIPTION,
98 JOB_LONG_DESCRIPTION,
99 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
100 from PO_JOB_ASSOCIATIONS_TL
101 where JOB_ID = X_JOB_ID
102 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
103 for update of JOB_ID nowait;
104 begin
105 open c;
106 fetch c into recinfo;
107 if (c%notfound) then
108 close c;
109 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
110 app_exception.raise_exception;
111 end if;
112 close c;
113 if ( (recinfo.CATEGORY_ID = X_CATEGORY_ID)
114 AND ((recinfo.INACTIVE_DATE = X_INACTIVE_DATE)
115 OR ((recinfo.INACTIVE_DATE is null) AND (X_INACTIVE_DATE is null)))
116 ) then
117 null;
118 else
119 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
120 app_exception.raise_exception;
121 end if;
122
123 for tlinfo in c1 loop
124 if (tlinfo.BASELANG = 'Y') then
125 if ( (tlinfo.JOB_DESCRIPTION = X_JOB_DESCRIPTION)
126 AND ((tlinfo.JOB_LONG_DESCRIPTION = X_JOB_LONG_DESCRIPTION)
127 OR ((tlinfo.JOB_LONG_DESCRIPTION is null) AND (X_JOB_LONG_DESCRIPTION is null)))
128 ) then
129 null;
130 else
131 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
132 app_exception.raise_exception;
133 end if;
134 end if;
135 end loop;
136 return;
137 end LOCK_ROW;
138
139 procedure UPDATE_ROW (
140 X_JOB_ID in NUMBER,
141 X_CATEGORY_ID in NUMBER,
142 X_INACTIVE_DATE in DATE,
143 X_JOB_DESCRIPTION in VARCHAR2,
144 X_JOB_LONG_DESCRIPTION in VARCHAR2,
145 X_LAST_UPDATE_DATE in DATE,
146 X_LAST_UPDATED_BY in NUMBER,
147 X_LAST_UPDATE_LOGIN in NUMBER
148 ) is
149 begin
150 update PO_JOB_ASSOCIATIONS_B set
151 CATEGORY_ID = X_CATEGORY_ID,
152 INACTIVE_DATE = X_INACTIVE_DATE,
153 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
154 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
155 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
156 where JOB_ID = X_JOB_ID;
157
158 if (sql%notfound) then
159 raise no_data_found;
160 end if;
161
162 update PO_JOB_ASSOCIATIONS_TL set
163 JOB_DESCRIPTION = X_JOB_DESCRIPTION,
164 JOB_LONG_DESCRIPTION = X_JOB_LONG_DESCRIPTION,
165 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
166 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
167 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
168 SOURCE_LANG = userenv('LANG')
169 where JOB_ID = X_JOB_ID
170 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
171
172 if (sql%notfound) then
173 raise no_data_found;
174 end if;
175 end UPDATE_ROW;
176
177 procedure DELETE_ROW (
178 X_JOB_ID in NUMBER
179 ) is
180 begin
181 delete from PO_JOB_ASSOCIATIONS_TL
182 where JOB_ID = X_JOB_ID;
183
184 if (sql%notfound) then
185 raise no_data_found;
186 end if;
187
188 delete from PO_JOB_ASSOCIATIONS_B
189 where JOB_ID = X_JOB_ID;
190
191 if (sql%notfound) then
192 raise no_data_found;
193 end if;
194 end DELETE_ROW;
195
196 procedure ADD_LANGUAGE
197 is
198 begin
199 delete from PO_JOB_ASSOCIATIONS_TL T
200 where not exists
201 (select NULL
202 from PO_JOB_ASSOCIATIONS_B B
203 where B.JOB_ID = T.JOB_ID
204 );
205
206 insert into PO_JOB_ASSOCIATIONS_TL (
207 JOB_ID,
208 JOB_DESCRIPTION,
209 JOB_LONG_DESCRIPTION,
210 LAST_UPDATE_DATE,
211 LAST_UPDATED_BY,
212 LAST_UPDATE_LOGIN,
213 CREATED_BY,
214 CREATION_DATE,
215 LANGUAGE,
216 SOURCE_LANG
217 ) select
218 B.JOB_ID,
219 B.JOB_DESCRIPTION,
220 B.JOB_LONG_DESCRIPTION,
221 B.LAST_UPDATE_DATE,
222 B.LAST_UPDATED_BY,
223 B.LAST_UPDATE_LOGIN,
224 B.CREATED_BY,
225 B.CREATION_DATE,
226 L.LANGUAGE_CODE,
227 B.SOURCE_LANG
228 from PO_JOB_ASSOCIATIONS_TL B, FND_LANGUAGES L
229 where L.INSTALLED_FLAG in ('I', 'B')
230 and B.LANGUAGE = userenv('LANG')
231 and not exists
232 (select NULL
233 from PO_JOB_ASSOCIATIONS_TL T
234 where T.JOB_ID = B.JOB_ID
235 and T.LANGUAGE = L.LANGUAGE_CODE);
236 end ADD_LANGUAGE;
237
238 end PO_JOB_ASSOCIATIONS_PKG;
[Home] [Help]