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

Source


1 package body GML_OP_ORDR_TYP_PKG as
2 /* $Header: GMLOTYPB.pls 115.7 2002/11/08 06:27:19 gmangari ship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out NOCOPY VARCHAR2,
5   X_ORDER_TYPE in NUMBER,
6   X_LANG_CODE in VARCHAR2,
7   X_INVEN_TRANS_TYPE in NUMBER,
8   X_SHIPMENT_IND in NUMBER,
9   X_INVOICE_IND in NUMBER,
10   X_PICKINGLST_IND in NUMBER,
11   X_EXPORTDOC_IND in NUMBER,
12   X_TOTALORD_IND in NUMBER,
13   X_UPDATE_SALESHIST in NUMBER,
14   X_CREDITCHK_TYPE in NUMBER,
15   X_PRICE_ORDER in NUMBER,
16   X_CALC_COMM_IND in NUMBER,
17   X_CALC_TAX_IND in NUMBER,
18   X_PRODORDR_IND in NUMBER,
19   X_SHIPTO_REQUIRED in NUMBER,
20   X_UPDATE_AUDITTRAIL in NUMBER,
21   X_ORDER_LEADTIME in NUMBER,
22   X_TRANS_CNT in NUMBER,
23   X_TEXT_CODE in NUMBER,
24   X_DELETE_MARK in NUMBER,
25   X_ORDER_TYPE_CODE in VARCHAR2,
26   X_ORDER_TYPE_DESC in VARCHAR2,
27   X_CREATION_DATE in DATE,
28   X_CREATED_BY in NUMBER,
29   X_LAST_UPDATE_DATE in DATE,
30   X_LAST_UPDATED_BY in NUMBER,
31   X_LAST_UPDATE_LOGIN in NUMBER
32 ) is
33   cursor C is select ROWID from OP_ORDR_TYP_B
34     where ORDER_TYPE = X_ORDER_TYPE
35     ;
36 begin
37   insert into OP_ORDR_TYP_B (
38     ORDER_TYPE,
39     ORDER_TYPE_CODE,
40     LANG_CODE,
41     INVEN_TRANS_TYPE,
42     SHIPMENT_IND,
43     INVOICE_IND,
44     PICKINGLST_IND,
45     EXPORTDOC_IND,
46     TOTALORD_IND,
47     UPDATE_SALESHIST,
48     CREDITCHK_TYPE,
49     PRICE_ORDER,
50     CALC_COMM_IND,
51     CALC_TAX_IND,
52     PRODORDR_IND,
53     SHIPTO_REQUIRED,
54     UPDATE_AUDITTRAIL,
55     ORDER_LEADTIME,
56     TRANS_CNT,
57     TEXT_CODE,
58     DELETE_MARK,
59     CREATION_DATE,
60     CREATED_BY,
61     LAST_UPDATE_DATE,
62     LAST_UPDATED_BY,
63     LAST_UPDATE_LOGIN
64   ) values (
65     X_ORDER_TYPE,
66     X_ORDER_TYPE_CODE,
67     X_LANG_CODE,
68     X_INVEN_TRANS_TYPE,
69     X_SHIPMENT_IND,
70     X_INVOICE_IND,
71     X_PICKINGLST_IND,
72     X_EXPORTDOC_IND,
73     X_TOTALORD_IND,
74     X_UPDATE_SALESHIST,
75     X_CREDITCHK_TYPE,
76     X_PRICE_ORDER,
77     X_CALC_COMM_IND,
78     X_CALC_TAX_IND,
79     X_PRODORDR_IND,
80     X_SHIPTO_REQUIRED,
81     X_UPDATE_AUDITTRAIL,
82     X_ORDER_LEADTIME,
83     X_TRANS_CNT,
84     X_TEXT_CODE,
85     X_DELETE_MARK,
86     X_CREATION_DATE,
87     X_CREATED_BY,
88     X_LAST_UPDATE_DATE,
89     X_LAST_UPDATED_BY,
90     X_LAST_UPDATE_LOGIN
91   );
92 
93   insert into OP_ORDR_TYP_TL (
94     ORDER_TYPE,
95     ORDER_TYPE_CODE,
96     ORDER_TYPE_DESC,
97     CREATED_BY,
98     LAST_UPDATED_BY,
99     CREATION_DATE,
100     LAST_UPDATE_DATE,
101     LAST_UPDATE_LOGIN,
102     LANGUAGE,
103     SOURCE_LANG
104   ) select
105     X_ORDER_TYPE,
106     X_ORDER_TYPE_CODE,
107     X_ORDER_TYPE_DESC,
108     X_CREATED_BY,
109     X_LAST_UPDATED_BY,
110     X_CREATION_DATE,
111     X_LAST_UPDATE_DATE,
112     X_LAST_UPDATE_LOGIN,
113     L.LANGUAGE_CODE,
114     userenv('LANG')
115   from FND_LANGUAGES L
116   where L.INSTALLED_FLAG in ('I', 'B')
117   and not exists
118     (select NULL
119     from OP_ORDR_TYP_TL T
120     where T.ORDER_TYPE = X_ORDER_TYPE
121     and T.LANGUAGE = L.LANGUAGE_CODE);
122 
123   open c;
124   fetch c into X_ROWID;
125   if (c%notfound) then
126     close c;
127     raise no_data_found;
128   end if;
129   close c;
130 
131 end INSERT_ROW;
132 
133 procedure LOCK_ROW (
134   X_ORDER_TYPE in NUMBER,
135   X_LANG_CODE in VARCHAR2,
136   X_INVEN_TRANS_TYPE in NUMBER,
137   X_SHIPMENT_IND in NUMBER,
138   X_INVOICE_IND in NUMBER,
139   X_PICKINGLST_IND in NUMBER,
140   X_EXPORTDOC_IND in NUMBER,
141   X_TOTALORD_IND in NUMBER,
142   X_UPDATE_SALESHIST in NUMBER,
143   X_CREDITCHK_TYPE in NUMBER,
144   X_PRICE_ORDER in NUMBER,
145   X_CALC_COMM_IND in NUMBER,
146   X_CALC_TAX_IND in NUMBER,
147   X_PRODORDR_IND in NUMBER,
148   X_SHIPTO_REQUIRED in NUMBER,
149   X_UPDATE_AUDITTRAIL in NUMBER,
150   X_ORDER_LEADTIME in NUMBER,
151   X_TRANS_CNT in NUMBER,
152   X_TEXT_CODE in NUMBER,
153   X_DELETE_MARK in NUMBER,
154   X_ORDER_TYPE_CODE in VARCHAR2,
155   X_ORDER_TYPE_DESC in VARCHAR2
156 ) is
157   cursor c is select
158       LANG_CODE,
159       INVEN_TRANS_TYPE,
160       SHIPMENT_IND,
161       INVOICE_IND,
162       PICKINGLST_IND,
163       EXPORTDOC_IND,
164       TOTALORD_IND,
165       UPDATE_SALESHIST,
166       CREDITCHK_TYPE,
167       PRICE_ORDER,
168       CALC_COMM_IND,
169       CALC_TAX_IND,
170       PRODORDR_IND,
171       SHIPTO_REQUIRED,
172       UPDATE_AUDITTRAIL,
173       ORDER_LEADTIME,
174       TRANS_CNT,
175       TEXT_CODE,
176       DELETE_MARK
177     from OP_ORDR_TYP_B
178     where ORDER_TYPE = X_ORDER_TYPE
179     for update of ORDER_TYPE nowait;
180   recinfo c%rowtype;
181 
182   cursor c1 is select
183       ORDER_TYPE_CODE,
184       ORDER_TYPE_DESC,
185       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
186     from OP_ORDR_TYP_TL
187     where ORDER_TYPE = X_ORDER_TYPE
188     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
189     for update of ORDER_TYPE nowait;
190 begin
191   open c;
192   fetch c into recinfo;
193   if (c%notfound) then
194     close c;
195     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
196     app_exception.raise_exception;
197   end if;
198   close c;
199   if (    (recinfo.INVEN_TRANS_TYPE = X_INVEN_TRANS_TYPE)
200       AND (recinfo.SHIPMENT_IND = X_SHIPMENT_IND)
201       AND (recinfo.INVOICE_IND = X_INVOICE_IND)
202       AND (recinfo.PICKINGLST_IND = X_PICKINGLST_IND)
203       AND (recinfo.EXPORTDOC_IND = X_EXPORTDOC_IND)
204       AND (recinfo.TOTALORD_IND = X_TOTALORD_IND)
205       AND (recinfo.UPDATE_SALESHIST = X_UPDATE_SALESHIST)
206       AND (recinfo.CREDITCHK_TYPE = X_CREDITCHK_TYPE)
207       AND (recinfo.PRICE_ORDER = X_PRICE_ORDER)
208       AND (recinfo.CALC_COMM_IND = X_CALC_COMM_IND)
209       AND (recinfo.CALC_TAX_IND = X_CALC_TAX_IND)
210       AND (recinfo.PRODORDR_IND = X_PRODORDR_IND)
211       AND (recinfo.SHIPTO_REQUIRED = X_SHIPTO_REQUIRED)
212       AND (recinfo.UPDATE_AUDITTRAIL = X_UPDATE_AUDITTRAIL)
213       AND (recinfo.ORDER_LEADTIME = X_ORDER_LEADTIME)
214       AND (recinfo.TRANS_CNT = X_TRANS_CNT)
215       AND ((recinfo.TEXT_CODE = X_TEXT_CODE)
216            OR ((recinfo.TEXT_CODE is null) AND (X_TEXT_CODE is null)))
217       AND (recinfo.DELETE_MARK = X_DELETE_MARK)
218   ) then
219     null;
220   else
221     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
222     app_exception.raise_exception;
223   end if;
224 
225   for tlinfo in c1 loop
226     if (tlinfo.BASELANG = 'Y') then
227       if (    (tlinfo.ORDER_TYPE_CODE = X_ORDER_TYPE_CODE)
228           AND (tlinfo.ORDER_TYPE_DESC = X_ORDER_TYPE_DESC)
229       ) then
230         null;
231       else
232         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
233         app_exception.raise_exception;
234       end if;
235     end if;
236   end loop;
237   return;
238 end LOCK_ROW;
239 
240 procedure UPDATE_ROW (
241   X_ORDER_TYPE in NUMBER,
242   X_LANG_CODE in VARCHAR2,
243   X_INVEN_TRANS_TYPE in NUMBER,
244   X_SHIPMENT_IND in NUMBER,
245   X_INVOICE_IND in NUMBER,
246   X_PICKINGLST_IND in NUMBER,
247   X_EXPORTDOC_IND in NUMBER,
248   X_TOTALORD_IND in NUMBER,
249   X_UPDATE_SALESHIST in NUMBER,
250   X_CREDITCHK_TYPE in NUMBER,
251   X_PRICE_ORDER in NUMBER,
252   X_CALC_COMM_IND in NUMBER,
253   X_CALC_TAX_IND in NUMBER,
254   X_PRODORDR_IND in NUMBER,
255   X_SHIPTO_REQUIRED in NUMBER,
256   X_UPDATE_AUDITTRAIL in NUMBER,
257   X_ORDER_LEADTIME in NUMBER,
258   X_TRANS_CNT in NUMBER,
259   X_TEXT_CODE in NUMBER,
260   X_DELETE_MARK in NUMBER,
261   X_ORDER_TYPE_CODE in VARCHAR2,
262   X_ORDER_TYPE_DESC in VARCHAR2,
263   X_LAST_UPDATE_DATE in DATE,
264   X_LAST_UPDATED_BY in NUMBER,
265   X_LAST_UPDATE_LOGIN in NUMBER
266 ) is
267 begin
268   update OP_ORDR_TYP_B set
269     LANG_CODE = X_LANG_CODE,
270     INVEN_TRANS_TYPE = X_INVEN_TRANS_TYPE,
271     SHIPMENT_IND = X_SHIPMENT_IND,
272     INVOICE_IND = X_INVOICE_IND,
273     PICKINGLST_IND = X_PICKINGLST_IND,
274     EXPORTDOC_IND = X_EXPORTDOC_IND,
275     TOTALORD_IND = X_TOTALORD_IND,
276     UPDATE_SALESHIST = X_UPDATE_SALESHIST,
277     CREDITCHK_TYPE = X_CREDITCHK_TYPE,
278     PRICE_ORDER = X_PRICE_ORDER,
279     CALC_COMM_IND = X_CALC_COMM_IND,
280     CALC_TAX_IND = X_CALC_TAX_IND,
281     PRODORDR_IND = X_PRODORDR_IND,
282     SHIPTO_REQUIRED = X_SHIPTO_REQUIRED,
283     UPDATE_AUDITTRAIL = X_UPDATE_AUDITTRAIL,
284     ORDER_LEADTIME = X_ORDER_LEADTIME,
285     TRANS_CNT = X_TRANS_CNT,
286     TEXT_CODE = X_TEXT_CODE,
287     DELETE_MARK = X_DELETE_MARK,
288     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
289     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
290     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
291   where ORDER_TYPE = X_ORDER_TYPE;
292 
293   if (sql%notfound) then
294     raise no_data_found;
295   end if;
296 
297   update OP_ORDR_TYP_TL set
298     ORDER_TYPE_CODE = X_ORDER_TYPE_CODE,
299     ORDER_TYPE_DESC = X_ORDER_TYPE_DESC,
300     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
301     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
302     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
303     SOURCE_LANG = userenv('LANG')
304   where ORDER_TYPE = X_ORDER_TYPE
305   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
306 
307   if (sql%notfound) then
308     raise no_data_found;
309   end if;
310 end UPDATE_ROW;
311 
312 procedure DELETE_ROW (
313   X_ORDER_TYPE in NUMBER
314 ) is
315 begin
316   delete from OP_ORDR_TYP_TL
317   where ORDER_TYPE = X_ORDER_TYPE;
318 
319   if (sql%notfound) then
320     raise no_data_found;
321   end if;
322 
323   delete from OP_ORDR_TYP_B
324   where ORDER_TYPE = X_ORDER_TYPE;
325 
326   if (sql%notfound) then
327     raise no_data_found;
328   end if;
329 end DELETE_ROW;
330 
331 procedure ADD_LANGUAGE
332 is
333 begin
334   delete from OP_ORDR_TYP_TL T
335   where not exists
336     (select NULL
337     from OP_ORDR_TYP_B B
338     where B.ORDER_TYPE = T.ORDER_TYPE
339     );
340 
341   update OP_ORDR_TYP_TL T set (
342       ORDER_TYPE_CODE,
343       ORDER_TYPE_DESC
344     ) = (select
345       B.ORDER_TYPE_CODE,
346       B.ORDER_TYPE_DESC
347     from OP_ORDR_TYP_TL B
348     where B.ORDER_TYPE = T.ORDER_TYPE
349     and B.LANGUAGE = T.SOURCE_LANG)
350   where (
351       T.ORDER_TYPE,
352       T.LANGUAGE
353   ) in (select
354       SUBT.ORDER_TYPE,
355       SUBT.LANGUAGE
356     from OP_ORDR_TYP_TL SUBB, OP_ORDR_TYP_TL SUBT
357     where SUBB.ORDER_TYPE = SUBT.ORDER_TYPE
358     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
359     and (SUBB.ORDER_TYPE_CODE <> SUBT.ORDER_TYPE_CODE
360       or SUBB.ORDER_TYPE_DESC <> SUBT.ORDER_TYPE_DESC
361   ));
362 
363   insert into OP_ORDR_TYP_TL (
364     ORDER_TYPE,
365     ORDER_TYPE_CODE,
366     ORDER_TYPE_DESC,
367     CREATED_BY,
368     LAST_UPDATED_BY,
369     CREATION_DATE,
370     LAST_UPDATE_DATE,
371     LAST_UPDATE_LOGIN,
372     LANGUAGE,
373     SOURCE_LANG
374   ) select
375     B.ORDER_TYPE,
376     B.ORDER_TYPE_CODE,
377     B.ORDER_TYPE_DESC,
378     B.CREATED_BY,
379     B.LAST_UPDATED_BY,
380     B.CREATION_DATE,
381     B.LAST_UPDATE_DATE,
382     B.LAST_UPDATE_LOGIN,
383     L.LANGUAGE_CODE,
384     B.SOURCE_LANG
385   from OP_ORDR_TYP_TL B, FND_LANGUAGES L
386   where L.INSTALLED_FLAG in ('I', 'B')
387   and B.LANGUAGE = userenv('LANG')
388   and not exists
389     (select NULL
390     from OP_ORDR_TYP_TL T
391     where T.ORDER_TYPE = B.ORDER_TYPE
392     and T.LANGUAGE = L.LANGUAGE_CODE);
393 end ADD_LANGUAGE;
394 
395 
396 procedure TRANSLATE_ROW (
397         X_ORDER_TYPE in VARCHAR2,
398         X_ORDER_TYPE_CODE in VARCHAR2,
399         X_ORDER_TYPE_DESC in VARCHAR2
400 ) IS
401 
402 BEGIN
403   update OP_ORDR_TYP_TL set
404     ORDER_TYPE_CODE = X_ORDER_TYPE_CODE,
405     ORDER_TYPE_DESC = X_ORDER_TYPE_DESC,
406     SOURCE_LANG = userenv('LANG'),
407     LAST_UPDATE_DATE = sysdate,
408     LAST_UPDATED_BY = 0,
409     LAST_UPDATE_LOGIN = 0
410   where
411     ORDER_TYPE = X_ORDER_TYPE
412     and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
413 end TRANSLATE_ROW;
414 
415 
416 procedure LOAD_ROW (
417   X_ORDER_TYPE_CODE in VARCHAR2,
418   X_ORDER_TYPE in NUMBER,
419   X_LANG_CODE in VARCHAR2,
420   X_INVEN_TRANS_TYPE in NUMBER,
421   X_SHIPMENT_IND in NUMBER,
422   X_INVOICE_IND in NUMBER,
423   X_PICKINGLST_IND in NUMBER,
424   X_EXPORTDOC_IND in NUMBER,
425   X_TOTALORD_IND in NUMBER,
426   X_UPDATE_SALESHIST in NUMBER,
427   X_CREDITCHK_TYPE in NUMBER,
428   X_PRICE_ORDER in NUMBER,
429   X_CALC_COMM_IND in NUMBER,
430   X_CALC_TAX_IND in NUMBER,
431   X_PRODORDR_IND in NUMBER,
432   X_SHIPTO_REQUIRED in NUMBER,
433   X_UPDATE_AUDITTRAIL in NUMBER,
434   X_ORDER_LEADTIME in NUMBER,
435   X_TRANS_CNT in NUMBER,
436   X_TEXT_CODE in NUMBER,
437   X_DELETE_MARK in NUMBER,
438   X_ORDER_TYPE_DESC in VARCHAR2
439 ) IS
440 
441 l_user_id	number :=0;
442 l_row_id	VARCHAR2(64);
443 
444 BEGIN
445 	l_user_id :=1;
446 
447   GML_OP_ORDR_TYP_PKG.UPDATE_ROW (
448     X_ORDER_TYPE => X_ORDER_TYPE ,
449     X_LANG_CODE => X_LANG_CODE,
450     X_INVEN_TRANS_TYPE => X_INVEN_TRANS_TYPE,
451     X_SHIPMENT_IND => X_SHIPMENT_IND,
452     X_INVOICE_IND => X_INVOICE_IND,
453     X_PICKINGLST_IND => X_PICKINGLST_IND,
454     X_EXPORTDOC_IND  => X_EXPORTDOC_IND,
455     X_TOTALORD_IND  => X_TOTALORD_IND ,
456     X_UPDATE_SALESHIST => X_UPDATE_SALESHIST,
457     X_CREDITCHK_TYPE  => X_CREDITCHK_TYPE,
458     X_PRICE_ORDER  => X_CREDITCHK_TYPE ,
459     X_CALC_COMM_IND  => X_CALC_COMM_IND,
460     X_CALC_TAX_IND  => X_CALC_TAX_IND ,
461     X_PRODORDR_IND  => X_PRODORDR_IND ,
462     X_SHIPTO_REQUIRED  => X_SHIPTO_REQUIRED ,
463     X_UPDATE_AUDITTRAIL  => X_UPDATE_AUDITTRAIL,
464     X_ORDER_LEADTIME  => X_UPDATE_AUDITTRAIL ,
465     X_TRANS_CNT  => X_TRANS_CNT ,
466     X_TEXT_CODE  => X_TEXT_CODE ,
467     X_DELETE_MARK  => X_DELETE_MARK,
468     X_ORDER_TYPE_CODE => X_ORDER_TYPE_CODE,
469     X_ORDER_TYPE_DESC  => X_ORDER_TYPE_DESC,
470     X_LAST_UPDATE_DATE  => sysdate,
471     X_LAST_UPDATED_BY  => l_user_id,
472     X_LAST_UPDATE_LOGIN  => 0
473 );
474 
475 EXCEPTION
476   WHEN NO_DATA_FOUND THEN
477 
478 GML_OP_ORDR_TYP_PKG.INSERT_ROW(
479     X_ROWID => l_row_id,
480     X_ORDER_TYPE => X_ORDER_TYPE ,
481     X_LANG_CODE => X_LANG_CODE,
482     X_INVEN_TRANS_TYPE => X_INVEN_TRANS_TYPE,
483     X_SHIPMENT_IND => X_SHIPMENT_IND,
484     X_INVOICE_IND => X_INVOICE_IND,
485     X_PICKINGLST_IND => X_PICKINGLST_IND,
486     X_EXPORTDOC_IND  => X_EXPORTDOC_IND,
487     X_TOTALORD_IND  => X_TOTALORD_IND ,
488     X_UPDATE_SALESHIST => X_UPDATE_SALESHIST,
489     X_CREDITCHK_TYPE  => X_CREDITCHK_TYPE,
490     X_PRICE_ORDER  => X_CREDITCHK_TYPE ,
491     X_CALC_COMM_IND  => X_CALC_COMM_IND,
492     X_CALC_TAX_IND  => X_CALC_TAX_IND ,
493     X_PRODORDR_IND  => X_PRODORDR_IND ,
494     X_SHIPTO_REQUIRED  => X_SHIPTO_REQUIRED ,
495     X_UPDATE_AUDITTRAIL  => X_UPDATE_AUDITTRAIL,
496     X_ORDER_LEADTIME  => X_UPDATE_AUDITTRAIL ,
497     X_TRANS_CNT  => X_TRANS_CNT ,
498     X_TEXT_CODE  => X_TEXT_CODE ,
499     X_DELETE_MARK  => X_DELETE_MARK,
500     X_ORDER_TYPE_CODE => X_ORDER_TYPE_CODE,
501     X_ORDER_TYPE_DESC => X_ORDER_TYPE_DESC,
502     X_CREATION_DATE => sysdate,
503     X_CREATED_BY => l_user_id,
504     X_LAST_UPDATE_DATE => sysdate,
505     X_LAST_UPDATED_BY => l_user_id,
506     X_LAST_UPDATE_LOGIN => 0
507 );
508 
509 END LOAD_ROW;
510 
511 end GML_OP_ORDR_TYP_PKG;