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.AHL_MR_HEADERS_PKG

Source


1 PACKAGE BODY AHL_MR_HEADERS_PKG as
2 /* $Header: AHLLMRHB.pls 120.0.12010000.2 2008/12/29 00:58:28 sracha ship $ */
3 procedure INSERT_ROW (
4   X_MR_HEADER_ID in OUT NOCOPY NUMBER,
5   X_ATTRIBUTE11 in VARCHAR2,
6   X_ATTRIBUTE12 in VARCHAR2,
7   X_ATTRIBUTE13 in VARCHAR2,
8   X_ATTRIBUTE14 in VARCHAR2,
9   X_ATTRIBUTE15 in VARCHAR2,
10   X_OBJECT_VERSION_NUMBER in NUMBER,
11   X_TITLE in VARCHAR2,
12   X_VERSION_NUMBER in NUMBER,
13   X_ATTRIBUTE4 in VARCHAR2,
14   X_ATTRIBUTE5 in VARCHAR2,
15   X_ATTRIBUTE6 in VARCHAR2,
16   X_ATTRIBUTE7 in VARCHAR2,
17   X_EFFECTIVE_FROM in DATE,
18   X_EFFECTIVE_TO in DATE,
19   X_REVISION in VARCHAR2,
20   X_ATTRIBUTE_CATEGORY in VARCHAR2,
21   X_ATTRIBUTE1 in VARCHAR2,
22   X_ATTRIBUTE2 in VARCHAR2,
23   X_ATTRIBUTE3 in VARCHAR2,
24   X_ATTRIBUTE10 in VARCHAR2,
25   X_COPY_ACCOMPLISHMENT_FLAG in VARCHAR2,
26   X_PROGRAM_TYPE_CODE in VARCHAR2,
27   X_PROGRAM_SUBTYPE_CODE in VARCHAR2,
28   X_CATEGORY_CODE in VARCHAR2,
29   X_SERVICE_TYPE_CODE in VARCHAR2,
30   X_MR_STATUS_CODE in VARCHAR2,
31   X_IMPLEMENT_STATUS_CODE in VARCHAR2,
32   X_REPETITIVE_FLAG in VARCHAR2,
33   X_SHOW_REPETITIVE_CODE in VARCHAR2,
34   X_WHICHEVER_FIRST_CODE in VARCHAR2,
35   X_PRECEDING_MR_HEADER_ID in NUMBER,
36   X_SERVICE_REQUEST_TEMPLATE_ID in NUMBER,
37   X_TYPE_CODE in VARCHAR2,
38   X_DOWN_TIME in NUMBER,
39   X_UOM_CODE in VARCHAR2,
40   X_ATTRIBUTE8 in VARCHAR2,
41   X_ATTRIBUTE9 in VARCHAR2,
42   X_DESCRIPTION in VARCHAR2,
43   X_COMMENTS in VARCHAR2,
44   X_SPACE_CATEGORY_CODE in VARCHAR2,
45   X_QA_INSPECTION_TYPE_CODE in VARCHAR2,
46   X_BILLING_ITEM_ID in NUMBER,
47   X_AUTO_SIGNOFF_FLAG in VARCHAR2,
48   X_COPY_INIT_ACCOMPL_FLAG in VARCHAR2,
49   X_COPY_DEFERRALS_FLAG in VARCHAR2,
50   X_CREATION_DATE in DATE,
51   X_CREATED_BY in NUMBER,
52   X_LAST_UPDATE_DATE in DATE,
53   X_LAST_UPDATED_BY in NUMBER,
54   X_LAST_UPDATE_LOGIN in NUMBER
55 ) is
56 begin
57   insert into AHL_MR_HEADERS_B (
58     ATTRIBUTE11,
59     ATTRIBUTE12,
60     ATTRIBUTE13,
61     ATTRIBUTE14,
62     ATTRIBUTE15,
63     MR_HEADER_ID,
64     OBJECT_VERSION_NUMBER,
65     TITLE,
66     VERSION_NUMBER,
67     ATTRIBUTE4,
68     ATTRIBUTE5,
69     ATTRIBUTE6,
70     ATTRIBUTE7,
71     EFFECTIVE_FROM,
72     EFFECTIVE_TO,
73     REVISION,
74     ATTRIBUTE_CATEGORY,
75     ATTRIBUTE1,
76     ATTRIBUTE2,
77     ATTRIBUTE3,
78     ATTRIBUTE10,
79     COPY_ACCOMPLISHMENT_FLAG,
80     PROGRAM_TYPE_CODE,
81     PROGRAM_SUBTYPE_CODE,
82     CATEGORY_CODE,
83     SERVICE_TYPE_CODE,
84     MR_STATUS_CODE,
85     IMPLEMENT_STATUS_CODE,
86     REPETITIVE_FLAG,
87     SHOW_REPETITIVE_CODE,
88     WHICHEVER_FIRST_CODE,
89     PRECEDING_MR_HEADER_ID,
90     SERVICE_REQUEST_TEMPLATE_ID,
91     TYPE_CODE,
92     DOWN_TIME,
93     UOM_CODE,
94     SPACE_CATEGORY_CODE,
95     QA_INSPECTION_TYPE,
96     BILLING_ITEM_ID ,
97     AUTO_SIGNOFF_FLAG,
98     COPY_INIT_ACCOMPL_FLAG,
99     COPY_DEFERRALS_FLAG,
100     ATTRIBUTE8,
101     ATTRIBUTE9,
102     CREATION_DATE,
103     CREATED_BY,
104     LAST_UPDATE_DATE,
105     LAST_UPDATED_BY,
106     LAST_UPDATE_LOGIN,
107     APPLICATION_USG_CODE
108   ) values (
109     X_ATTRIBUTE11,
110     X_ATTRIBUTE12,
111     X_ATTRIBUTE13,
112     X_ATTRIBUTE14,
113     X_ATTRIBUTE15,
114     AHL_MR_HEADERS_B_S.NEXTVAL,
115     X_OBJECT_VERSION_NUMBER,
116     X_TITLE,
117     X_VERSION_NUMBER,
118     X_ATTRIBUTE4,
119     X_ATTRIBUTE5,
120     X_ATTRIBUTE6,
121     X_ATTRIBUTE7,
122     X_EFFECTIVE_FROM,
123     X_EFFECTIVE_TO,
124     X_REVISION,
125     X_ATTRIBUTE_CATEGORY,
126     X_ATTRIBUTE1,
127     X_ATTRIBUTE2,
128     X_ATTRIBUTE3,
129     X_ATTRIBUTE10,
130     X_COPY_ACCOMPLISHMENT_FLAG,
131     X_PROGRAM_TYPE_CODE,
132     X_PROGRAM_SUBTYPE_CODE,
133     X_CATEGORY_CODE,
134     X_SERVICE_TYPE_CODE,
135     X_MR_STATUS_CODE,
136     X_IMPLEMENT_STATUS_CODE,
137     X_REPETITIVE_FLAG,
138     X_SHOW_REPETITIVE_CODE,
139     X_WHICHEVER_FIRST_CODE,
140     X_PRECEDING_MR_HEADER_ID,
141     X_SERVICE_REQUEST_TEMPLATE_ID,
142     X_TYPE_CODE,
143     X_DOWN_TIME,
144     X_UOM_CODE,
145     X_SPACE_CATEGORY_CODE,
146     X_QA_INSPECTION_TYPE_CODE ,
147     X_BILLING_ITEM_ID ,
148     X_AUTO_SIGNOFF_FLAG,
149     X_COPY_INIT_ACCOMPL_FLAG,
150     X_COPY_DEFERRALS_FLAG,
151     X_ATTRIBUTE8,
152     X_ATTRIBUTE9,
153     X_CREATION_DATE,
154     X_CREATED_BY,
155     X_LAST_UPDATE_DATE,
156     X_LAST_UPDATED_BY,
157     X_LAST_UPDATE_LOGIN,
158     FND_PROFILE.VALUE('AHL_APPLN_USAGE')
159   )
160   RETURNING MR_HEADER_ID INTO X_MR_HEADER_ID;
161 
162   insert into AHL_MR_HEADERS_TL (
163     COMMENTS,
164     DESCRIPTION,
165     CREATED_BY,
166     LAST_UPDATE_LOGIN,
167     LAST_UPDATE_DATE,
168     LAST_UPDATED_BY,
169     CREATION_DATE,
170     MR_HEADER_ID,
171     LANGUAGE,
172     SOURCE_LANG
173   ) select
174     X_COMMENTS,
175     X_DESCRIPTION,
176     X_CREATED_BY,
177     X_LAST_UPDATE_LOGIN,
178     X_LAST_UPDATE_DATE,
179     X_LAST_UPDATED_BY,
180     X_CREATION_DATE,
181     X_MR_HEADER_ID,
182     L.LANGUAGE_CODE,
183     userenv('LANG')
184   from FND_LANGUAGES L
185   where L.INSTALLED_FLAG in ('I', 'B')
186   and not exists
187     (select NULL
188     from AHL_MR_HEADERS_TL T
189     where T.MR_HEADER_ID = X_MR_HEADER_ID
190     and T.LANGUAGE = L.LANGUAGE_CODE);
191 
192 end INSERT_ROW;
193 
194 procedure UPDATE_ROW (
195   X_MR_HEADER_ID in NUMBER,
196   X_ATTRIBUTE11 in VARCHAR2,
197   X_ATTRIBUTE12 in VARCHAR2,
198   X_ATTRIBUTE13 in VARCHAR2,
199   X_ATTRIBUTE14 in VARCHAR2,
200   X_ATTRIBUTE15 in VARCHAR2,
201   X_OBJECT_VERSION_NUMBER in NUMBER,
202   X_TITLE in VARCHAR2,
203   X_VERSION_NUMBER in NUMBER,
204   X_ATTRIBUTE4 in VARCHAR2,
205   X_ATTRIBUTE5 in VARCHAR2,
206   X_ATTRIBUTE6 in VARCHAR2,
207   X_ATTRIBUTE7 in VARCHAR2,
208   X_EFFECTIVE_FROM in DATE,
209   X_EFFECTIVE_TO in DATE,
210   X_REVISION in VARCHAR2,
211   X_ATTRIBUTE_CATEGORY in VARCHAR2,
212   X_ATTRIBUTE1 in VARCHAR2,
213   X_ATTRIBUTE2 in VARCHAR2,
214   X_ATTRIBUTE3 in VARCHAR2,
215   X_ATTRIBUTE10 in VARCHAR2,
216   X_COPY_ACCOMPLISHMENT_FLAG in VARCHAR2,
217   X_PROGRAM_TYPE_CODE in VARCHAR2,
218   X_PROGRAM_SUBTYPE_CODE in VARCHAR2,
219   X_CATEGORY_CODE in VARCHAR2,
220   X_SERVICE_TYPE_CODE in VARCHAR2,
221   X_MR_STATUS_CODE in VARCHAR2,
222   X_IMPLEMENT_STATUS_CODE in VARCHAR2,
223   X_REPETITIVE_FLAG in VARCHAR2,
224   X_SHOW_REPETITIVE_CODE in VARCHAR2,
225   X_WHICHEVER_FIRST_CODE in VARCHAR2,
226   X_PRECEDING_MR_HEADER_ID in NUMBER,
227   X_SERVICE_REQUEST_TEMPLATE_ID in NUMBER,
228   X_TYPE_CODE in VARCHAR2,
229   X_DOWN_TIME in NUMBER,
230   X_UOM_CODE in VARCHAR2,
231   X_ATTRIBUTE8 in VARCHAR2,
232   X_ATTRIBUTE9 in VARCHAR2,
233   X_DESCRIPTION in VARCHAR2,
234   X_COMMENTS in VARCHAR2,
235   X_SPACE_CATEGORY_CODE in VARCHAR2,
236   X_QA_INSPECTION_TYPE_CODE in VARCHAR2,
237   X_BILLING_ITEM_ID in NUMBER,
238   X_AUTO_SIGNOFF_FLAG in VARCHAR2,
239   X_COPY_INIT_ACCOMPL_FLAG in VARCHAR2,
240   X_COPY_DEFERRALS_FLAG in VARCHAR2,
241   X_LAST_UPDATE_DATE in DATE,
242   X_LAST_UPDATED_BY in NUMBER,
243   X_LAST_UPDATE_LOGIN in NUMBER
244 ) is
245 begin
246   update AHL_MR_HEADERS_B set
247     ATTRIBUTE11 = X_ATTRIBUTE11,
248     ATTRIBUTE12 = X_ATTRIBUTE12,
249     ATTRIBUTE13 = X_ATTRIBUTE13,
250     ATTRIBUTE14 = X_ATTRIBUTE14,
251     ATTRIBUTE15 = X_ATTRIBUTE15,
252     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER + 1,
253 --    TITLE = X_TITLE,
254 --    VERSION_NUMBER = X_VERSION_NUMBER,
255     ATTRIBUTE4 = X_ATTRIBUTE4,
256     ATTRIBUTE5 = X_ATTRIBUTE5,
257     ATTRIBUTE6 = X_ATTRIBUTE6,
258     ATTRIBUTE7 = X_ATTRIBUTE7,
259     EFFECTIVE_FROM = X_EFFECTIVE_FROM,
260 --    EFFECTIVE_TO = X_EFFECTIVE_TO,
261     REVISION = X_REVISION,
262     ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
263     ATTRIBUTE1 = X_ATTRIBUTE1,
264     ATTRIBUTE2 = X_ATTRIBUTE2,
265     ATTRIBUTE3 = X_ATTRIBUTE3,
266     ATTRIBUTE10 = X_ATTRIBUTE10,
267     COPY_ACCOMPLISHMENT_FLAG = X_COPY_ACCOMPLISHMENT_FLAG,
268     PROGRAM_TYPE_CODE = X_PROGRAM_TYPE_CODE,
269     PROGRAM_SUBTYPE_CODE = X_PROGRAM_SUBTYPE_CODE,
270     CATEGORY_CODE = X_CATEGORY_CODE,
271     SERVICE_TYPE_CODE = X_SERVICE_TYPE_CODE,
272     MR_STATUS_CODE = X_MR_STATUS_CODE,
273     IMPLEMENT_STATUS_CODE = X_IMPLEMENT_STATUS_CODE,
274     REPETITIVE_FLAG = X_REPETITIVE_FLAG,
275     SHOW_REPETITIVE_CODE = X_SHOW_REPETITIVE_CODE,
276     WHICHEVER_FIRST_CODE = X_WHICHEVER_FIRST_CODE,
277     PRECEDING_MR_HEADER_ID = X_PRECEDING_MR_HEADER_ID,
278     SERVICE_REQUEST_TEMPLATE_ID =X_SERVICE_REQUEST_TEMPLATE_ID,
279     TYPE_CODE=X_TYPE_CODE,
280     DOWN_TIME=X_DOWN_TIME,
281     UOM_CODE= X_UOM_CODE,
282     SPACE_CATEGORY_CODE=X_SPACE_CATEGORY_CODE ,
283     QA_INSPECTION_TYPE=X_QA_INSPECTION_TYPE_CODE ,
284     BILLING_ITEM_ID=X_BILLING_ITEM_ID ,
285     AUTO_SIGNOFF_FLAG=X_AUTO_SIGNOFF_FLAG,
286     COPY_INIT_ACCOMPL_FLAG = X_COPY_INIT_ACCOMPL_FLAG,
287     COPY_DEFERRALS_FLAG = X_COPY_DEFERRALS_FLAG,
288     ATTRIBUTE8 = X_ATTRIBUTE8,
289     ATTRIBUTE9 = X_ATTRIBUTE9,
290     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
291     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
292     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
293   where MR_HEADER_ID = X_MR_HEADER_ID
294   and   OBJECT_VERSION_NUMBER=X_OBJECT_VERSION_NUMBER;
295   if sql%rowcount=0 then
296            FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
297            FND_MSG_PUB.ADD;
298   else
299   update AHL_MR_HEADERS_TL set
300     DESCRIPTION = X_DESCRIPTION,
301     COMMENTS = X_COMMENTS,
302     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
303     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
304     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
305     SOURCE_LANG = userenv('LANG')
306   where MR_HEADER_ID = X_MR_HEADER_ID
307   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
308  end if;
309 end UPDATE_ROW;
310 
311 procedure DELETE_ROW (
312   X_MR_HEADER_ID in NUMBER
313 ) is
314 begin
315   delete from AHL_MR_HEADERS_TL
316   where MR_HEADER_ID = X_MR_HEADER_ID;
317 
318   delete from AHL_MR_HEADERS_B
319   where MR_HEADER_ID = X_MR_HEADER_ID;
320 end DELETE_ROW;
321 
322 procedure ADD_LANGUAGE
323 is
324 begin
325   delete from AHL_MR_HEADERS_TL T
326   where not exists
327     (select NULL
328     from AHL_MR_HEADERS_B B
329     where B.MR_HEADER_ID = T.MR_HEADER_ID
330     );
331 
332   update AHL_MR_HEADERS_TL T set (
333       DESCRIPTION,
334       COMMENTS
335     ) = (select
336       B.DESCRIPTION,
337       B.COMMENTS
338     from AHL_MR_HEADERS_TL B
339     where B.MR_HEADER_ID = T.MR_HEADER_ID
340     and B.LANGUAGE = T.SOURCE_LANG)
341   where (
342       T.MR_HEADER_ID,
343       T.LANGUAGE
344   ) in (select
345       SUBT.MR_HEADER_ID,
346       SUBT.LANGUAGE
347     from AHL_MR_HEADERS_TL SUBB, AHL_MR_HEADERS_TL SUBT
348     where SUBB.MR_HEADER_ID = SUBT.MR_HEADER_ID
349     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
350     and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
351       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
352       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
353       or SUBB.COMMENTS <> SUBT.COMMENTS
354       or (SUBB.COMMENTS is null and SUBT.COMMENTS is not null)
355       or (SUBB.COMMENTS is not null and SUBT.COMMENTS is null)
356   ));
357 
358   insert into AHL_MR_HEADERS_TL (
359     COMMENTS,
360     DESCRIPTION,
361     CREATED_BY,
362     LAST_UPDATE_LOGIN,
363     LAST_UPDATE_DATE,
364     LAST_UPDATED_BY,
365     CREATION_DATE,
366     MR_HEADER_ID,
367     LANGUAGE,
368     SOURCE_LANG
369   ) select
370     B.COMMENTS,
371     B.DESCRIPTION,
372     B.CREATED_BY,
373     B.LAST_UPDATE_LOGIN,
374     B.LAST_UPDATE_DATE,
375     B.LAST_UPDATED_BY,
376     B.CREATION_DATE,
377     B.MR_HEADER_ID,
378     L.LANGUAGE_CODE,
379     B.SOURCE_LANG
380   from AHL_MR_HEADERS_TL B, FND_LANGUAGES L
381   where L.INSTALLED_FLAG in ('I', 'B')
382   and B.LANGUAGE = userenv('LANG')
383   and not exists
384     (select NULL
385     from AHL_MR_HEADERS_TL T
386     where T.MR_HEADER_ID = B.MR_HEADER_ID
387     and T.LANGUAGE = L.LANGUAGE_CODE);
388 end ADD_LANGUAGE;
389 
390 end AHL_MR_HEADERS_PKG;