eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CS_INCIDENTS_PURGE_AUDIT_PKG

Source


1 package body CS_INCIDENTS_PURGE_AUDIT_PKG as
2 /* $Header: csthipab.pls 120.0 2005/10/27 15:45:08 aneemuch noship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out nocopy VARCHAR2,
5   X_INCIDENT_ID in NUMBER,
6   X_PURGED_BY in NUMBER,
7   X_INCIDENT_NUMBER in VARCHAR2,
8   X_INCIDENT_TYPE_ID in NUMBER,
9   X_CUSTOMER_ID in NUMBER,
10   X_INVENTORY_ITEM_ID in NUMBER,
11   X_INV_ORGANIZATION_ID in NUMBER,
12   X_CUSTOMER_PRODUCT_ID in NUMBER,
13   X_INC_CREATION_DATE in DATE,
14   X_INC_LAST_UPDATE_DATE in DATE,
15   X_PURGED_DATE in DATE,
16   X_PURGE_ID in NUMBER,
17   X_SUMMARY in VARCHAR2,
18   X_CREATION_DATE in DATE,
19   X_CREATED_BY in NUMBER,
20   X_LAST_UPDATE_DATE in DATE,
21   X_LAST_UPDATED_BY in NUMBER,
22   X_LAST_UPDATE_LOGIN in NUMBER
23 ) is
24   cursor C is select ROWID from CS_INCIDENTS_PURGE_AUDIT_B
25     where INCIDENT_ID = X_INCIDENT_ID
26     ;
27 begin
28   insert into CS_INCIDENTS_PURGE_AUDIT_B (
29     PURGED_BY,
30     INCIDENT_ID,
31     INCIDENT_NUMBER,
32     INCIDENT_TYPE_ID,
33     CUSTOMER_ID,
34     INVENTORY_ITEM_ID,
35     INV_ORGANIZATION_ID,
36     CUSTOMER_PRODUCT_ID,
37     INC_CREATION_DATE,
38     INC_LAST_UPDATE_DATE,
39     PURGED_DATE,
40     PURGE_ID,
41     CREATION_DATE,
42     CREATED_BY,
43     LAST_UPDATE_DATE,
44     LAST_UPDATED_BY,
45     LAST_UPDATE_LOGIN
46   ) values (
47     X_PURGED_BY,
48     X_INCIDENT_ID,
49     X_INCIDENT_NUMBER,
50     X_INCIDENT_TYPE_ID,
51     X_CUSTOMER_ID,
52     X_INVENTORY_ITEM_ID,
53     X_INV_ORGANIZATION_ID,
54     X_CUSTOMER_PRODUCT_ID,
55     X_INC_CREATION_DATE,
56     X_INC_LAST_UPDATE_DATE,
57     X_PURGED_DATE,
58     X_PURGE_ID,
59     X_CREATION_DATE,
60     X_CREATED_BY,
61     X_LAST_UPDATE_DATE,
62     X_LAST_UPDATED_BY,
63     X_LAST_UPDATE_LOGIN
64   );
65 
66   insert into CS_INCIDENTS_PURGE_AUDIT_TL (
67     PURGE_ID,
68     INCIDENT_ID,
69     SUMMARY,
70     CREATION_DATE,
71     CREATED_BY,
72     LAST_UPDATE_DATE,
73     LAST_UPDATED_BY,
74     LAST_UPDATE_LOGIN,
75     LANGUAGE,
76     SOURCE_LANG
77   ) select
78     X_PURGE_ID,
79     X_INCIDENT_ID,
80     X_SUMMARY,
81     X_CREATION_DATE,
82     X_CREATED_BY,
83     X_LAST_UPDATE_DATE,
84     X_LAST_UPDATED_BY,
85     X_LAST_UPDATE_LOGIN,
86     L.LANGUAGE_CODE,
87     userenv('LANG')
88   from FND_LANGUAGES L
89   where L.INSTALLED_FLAG in ('I', 'B')
90   and not exists
91     (select NULL
92     from CS_INCIDENTS_PURGE_AUDIT_TL T
93     where T.INCIDENT_ID = X_INCIDENT_ID
94     and T.LANGUAGE = L.LANGUAGE_CODE);
95 
96   open c;
97   fetch c into X_ROWID;
98   if (c%notfound) then
99     close c;
100     raise no_data_found;
101   end if;
102   close c;
103 
104 end INSERT_ROW;
105 
106 procedure LOCK_ROW (
107   X_INCIDENT_ID in NUMBER,
108   X_PURGED_BY in NUMBER,
109   X_INCIDENT_NUMBER in VARCHAR2,
110   X_INCIDENT_TYPE_ID in NUMBER,
111   X_CUSTOMER_ID in NUMBER,
112   X_INVENTORY_ITEM_ID in NUMBER,
113   X_INV_ORGANIZATION_ID in NUMBER,
114   X_CUSTOMER_PRODUCT_ID in NUMBER,
115   X_INC_CREATION_DATE in DATE,
116   X_INC_LAST_UPDATE_DATE in DATE,
117   X_PURGED_DATE in DATE,
118   X_PURGE_ID in NUMBER,
119   X_SUMMARY in VARCHAR2
120 ) is
121   cursor c is select
122       PURGED_BY,
123       INCIDENT_NUMBER,
124       INCIDENT_TYPE_ID,
125       CUSTOMER_ID,
126       INVENTORY_ITEM_ID,
127       INV_ORGANIZATION_ID,
128       CUSTOMER_PRODUCT_ID,
129       INC_CREATION_DATE,
130       INC_LAST_UPDATE_DATE,
131       PURGED_DATE,
132       PURGE_ID
133     from CS_INCIDENTS_PURGE_AUDIT_B
134     where INCIDENT_ID = X_INCIDENT_ID
135     for update of INCIDENT_ID nowait;
136   recinfo c%rowtype;
137 
138   cursor c1 is select
139       SUMMARY,
140       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
141     from CS_INCIDENTS_PURGE_AUDIT_TL
142     where INCIDENT_ID = X_INCIDENT_ID
143     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
144     for update of INCIDENT_ID nowait;
145 begin
146   open c;
147   fetch c into recinfo;
148   if (c%notfound) then
149     close c;
150     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
151     app_exception.raise_exception;
152   end if;
153   close c;
154   if (    (recinfo.PURGED_BY = X_PURGED_BY)
155       AND (recinfo.INCIDENT_NUMBER = X_INCIDENT_NUMBER)
156       AND (recinfo.INCIDENT_TYPE_ID = X_INCIDENT_TYPE_ID)
157       AND ((recinfo.CUSTOMER_ID = X_CUSTOMER_ID)
158            OR ((recinfo.CUSTOMER_ID is null) AND (X_CUSTOMER_ID is null)))
159       AND ((recinfo.INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID)
160            OR ((recinfo.INVENTORY_ITEM_ID is null) AND (X_INVENTORY_ITEM_ID is null)))
161       AND ((recinfo.INV_ORGANIZATION_ID = X_INV_ORGANIZATION_ID)
162            OR ((recinfo.INV_ORGANIZATION_ID is null) AND (X_INV_ORGANIZATION_ID is null)))
163       AND ((recinfo.CUSTOMER_PRODUCT_ID = X_CUSTOMER_PRODUCT_ID)
164            OR ((recinfo.CUSTOMER_PRODUCT_ID is null) AND (X_CUSTOMER_PRODUCT_ID is null)))
165       AND (recinfo.INC_CREATION_DATE = X_INC_CREATION_DATE)
166       AND (recinfo.INC_LAST_UPDATE_DATE = X_INC_LAST_UPDATE_DATE)
167       AND (recinfo.PURGED_DATE = X_PURGED_DATE)
168       AND (recinfo.PURGE_ID = X_PURGE_ID)
169   ) then
170     null;
171   else
172     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
173     app_exception.raise_exception;
174   end if;
175 
176   for tlinfo in c1 loop
177     if (tlinfo.BASELANG = 'Y') then
178       if (    (tlinfo.SUMMARY = X_SUMMARY)
179       ) then
180         null;
181       else
182         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
183         app_exception.raise_exception;
184       end if;
185     end if;
186   end loop;
187   return;
188 end LOCK_ROW;
189 
190 procedure UPDATE_ROW (
191   X_INCIDENT_ID in NUMBER,
192   X_PURGED_BY in NUMBER,
193   X_INCIDENT_NUMBER in VARCHAR2,
194   X_INCIDENT_TYPE_ID in NUMBER,
195   X_CUSTOMER_ID in NUMBER,
196   X_INVENTORY_ITEM_ID in NUMBER,
197   X_INV_ORGANIZATION_ID in NUMBER,
198   X_CUSTOMER_PRODUCT_ID in NUMBER,
199   X_INC_CREATION_DATE in DATE,
200   X_INC_LAST_UPDATE_DATE in DATE,
201   X_PURGED_DATE in DATE,
202   X_PURGE_ID in NUMBER,
203   X_SUMMARY in VARCHAR2,
204   X_LAST_UPDATE_DATE in DATE,
205   X_LAST_UPDATED_BY in NUMBER,
206   X_LAST_UPDATE_LOGIN in NUMBER
207 ) is
208 begin
209   update CS_INCIDENTS_PURGE_AUDIT_B set
210     PURGED_BY = X_PURGED_BY,
211     INCIDENT_NUMBER = X_INCIDENT_NUMBER,
212     INCIDENT_TYPE_ID = X_INCIDENT_TYPE_ID,
213     CUSTOMER_ID = X_CUSTOMER_ID,
214     INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID,
215     INV_ORGANIZATION_ID = X_INV_ORGANIZATION_ID,
216     CUSTOMER_PRODUCT_ID = X_CUSTOMER_PRODUCT_ID,
217     INC_CREATION_DATE = X_INC_CREATION_DATE,
218     INC_LAST_UPDATE_DATE = X_INC_LAST_UPDATE_DATE,
219     PURGED_DATE = X_PURGED_DATE,
220     PURGE_ID = X_PURGE_ID,
221     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
222     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
223     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
224   where INCIDENT_ID = X_INCIDENT_ID;
225 
226   if (sql%notfound) then
227     raise no_data_found;
228   end if;
229 
230   update CS_INCIDENTS_PURGE_AUDIT_TL set
231     SUMMARY = X_SUMMARY,
232     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
233     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
234     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
235     SOURCE_LANG = userenv('LANG')
236   where INCIDENT_ID = X_INCIDENT_ID
237   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
238 
239   if (sql%notfound) then
240     raise no_data_found;
241   end if;
242 end UPDATE_ROW;
243 
244 procedure DELETE_ROW (
245   X_INCIDENT_ID in NUMBER
246 ) is
247 begin
248   delete from CS_INCIDENTS_PURGE_AUDIT_TL
249   where INCIDENT_ID = X_INCIDENT_ID;
250 
251   if (sql%notfound) then
252     raise no_data_found;
253   end if;
254 
255   delete from CS_INCIDENTS_PURGE_AUDIT_B
256   where INCIDENT_ID = X_INCIDENT_ID;
257 
258   if (sql%notfound) then
259     raise no_data_found;
260   end if;
261 end DELETE_ROW;
262 
263 procedure ADD_LANGUAGE
264 is
265 begin
266   delete from CS_INCIDENTS_PURGE_AUDIT_TL T
267   where not exists
268     (select NULL
269     from CS_INCIDENTS_PURGE_AUDIT_B B
270     where B.INCIDENT_ID = T.INCIDENT_ID
271     );
272 
273   update CS_INCIDENTS_PURGE_AUDIT_TL T set (
274       SUMMARY
275     ) = (select
276       B.SUMMARY
277     from CS_INCIDENTS_PURGE_AUDIT_TL B
278     where B.INCIDENT_ID = T.INCIDENT_ID
279     and B.LANGUAGE = T.SOURCE_LANG)
280   where (
281       T.INCIDENT_ID,
282       T.LANGUAGE
283   ) in (select
284       SUBT.INCIDENT_ID,
285       SUBT.LANGUAGE
286     from CS_INCIDENTS_PURGE_AUDIT_TL SUBB, CS_INCIDENTS_PURGE_AUDIT_TL SUBT
287     where SUBB.INCIDENT_ID = SUBT.INCIDENT_ID
288     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
289     and (SUBB.SUMMARY <> SUBT.SUMMARY
290   ));
291 
292   insert into CS_INCIDENTS_PURGE_AUDIT_TL (
293     PURGE_ID,
294     INCIDENT_ID,
295     SUMMARY,
296     CREATION_DATE,
297     CREATED_BY,
298     LAST_UPDATE_DATE,
299     LAST_UPDATED_BY,
300     LAST_UPDATE_LOGIN,
301     LANGUAGE,
302     SOURCE_LANG
303   ) select /*+ ORDERED */
304     B.PURGE_ID,
305     B.INCIDENT_ID,
306     B.SUMMARY,
307     B.CREATION_DATE,
308     B.CREATED_BY,
309     B.LAST_UPDATE_DATE,
310     B.LAST_UPDATED_BY,
311     B.LAST_UPDATE_LOGIN,
312     L.LANGUAGE_CODE,
313     B.SOURCE_LANG
314   from CS_INCIDENTS_PURGE_AUDIT_TL B, FND_LANGUAGES L
315   where L.INSTALLED_FLAG in ('I', 'B')
316   and B.LANGUAGE = userenv('LANG')
317   and not exists
318     (select NULL
319     from CS_INCIDENTS_PURGE_AUDIT_TL T
320     where T.INCIDENT_ID = B.INCIDENT_ID
321     and T.LANGUAGE = L.LANGUAGE_CODE);
322 end ADD_LANGUAGE;
323 
324 end CS_INCIDENTS_PURGE_AUDIT_PKG;