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