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

Source


1 package body PO_LINE_TYPES_PKG as
2 /* $Header: POXTILTB.pls 115.8 2004/03/23 22:04:12 dreddy ship $ */
3 X_progress varchar2(10) := '001';
4 procedure INSERT_ROW (
5   X_ROWID in out NOCOPY VARCHAR2,
6   X_LINE_TYPE_ID in out NOCOPY NUMBER,
7   X_ATTRIBUTE3 in VARCHAR2,
8   X_ATTRIBUTE4 in VARCHAR2,
9   X_ATTRIBUTE5 in VARCHAR2,
10   X_ATTRIBUTE6 in VARCHAR2,
11   X_ATTRIBUTE7 in VARCHAR2,
12   X_ATTRIBUTE8 in VARCHAR2,
13   X_ATTRIBUTE9 in VARCHAR2,
14   X_ATTRIBUTE10 in VARCHAR2,
15   X_ATTRIBUTE11 in VARCHAR2,
16   X_ATTRIBUTE12 in VARCHAR2,
17   X_ATTRIBUTE13 in VARCHAR2,
18   X_ATTRIBUTE14 in VARCHAR2,
19   X_ATTRIBUTE15 in VARCHAR2,
20   X_OUTSIDE_OPERATION_FLAG in VARCHAR2,
21   X_REQUEST_ID in NUMBER,
22   X_RECEIVE_CLOSE_TOLERANCE in NUMBER,
23   X_ORDER_TYPE_LOOKUP_CODE in VARCHAR2,
24   X_PURCHASE_BASIS IN PO_LINE_TYPES_B.purchase_basis%TYPE,  -- <SERVICES FPJ>
25   X_MATCHING_BASIS IN PO_LINE_TYPES_B.matching_basis%TYPE,  -- <SERVICES FPJ>
26   X_CATEGORY_ID in NUMBER,
27   X_UNIT_OF_MEASURE in VARCHAR2,
28   X_UNIT_PRICE in NUMBER,
29   X_RECEIVING_FLAG in VARCHAR2,
30   X_INACTIVE_DATE in DATE,
31   X_ATTRIBUTE_CATEGORY in VARCHAR2,
32   X_ATTRIBUTE1 in VARCHAR2,
33   X_ATTRIBUTE2 in VARCHAR2,
34   X_LINE_TYPE in VARCHAR2,
35   X_DESCRIPTION in VARCHAR2,
36   X_CREATION_DATE in DATE,
37   X_CREATED_BY in NUMBER,
38   X_LAST_UPDATE_DATE in DATE,
39   X_LAST_UPDATED_BY in NUMBER,
40   X_LAST_UPDATE_LOGIN in NUMBER
41 ) is
42   cursor C is select ROWID from PO_LINE_TYPES_B
43     where LINE_TYPE_ID = X_LINE_TYPE_ID ;
44 
45    CURSOR C2 IS SELECT po_line_types_s.nextval FROM sys.dual;
46 
47 begin
48 
49       BEGIN
50 	if (X_Line_Type_Id is NULL) then
51            OPEN C2;
52            FETCH C2 INTO X_Line_Type_ID;
53            CLOSE C2;
54         end if;
55       end;
56   begin
57   insert into PO_LINE_TYPES_B (
58     ATTRIBUTE3,
59     ATTRIBUTE4,
60     ATTRIBUTE5,
61     ATTRIBUTE6,
62     ATTRIBUTE7,
63     ATTRIBUTE8,
64     ATTRIBUTE9,
65     ATTRIBUTE10,
66     ATTRIBUTE11,
67     ATTRIBUTE12,
68     ATTRIBUTE13,
69     ATTRIBUTE14,
70     ATTRIBUTE15,
71     OUTSIDE_OPERATION_FLAG,
72     REQUEST_ID,
73     RECEIVE_CLOSE_TOLERANCE,
74     LINE_TYPE_ID,
75     ORDER_TYPE_LOOKUP_CODE,
76     PURCHASE_BASIS,                   -- <SERVICES FPJ>
77     MATCHING_BASIS,                   -- <SERVICES FPJ>
78     CATEGORY_ID,
79     UNIT_OF_MEASURE,
80     UNIT_PRICE,
81     RECEIVING_FLAG,
82     INACTIVE_DATE,
83     ATTRIBUTE_CATEGORY,
84     ATTRIBUTE1,
85     ATTRIBUTE2,
86     CREATION_DATE,
87     CREATED_BY,
88     LAST_UPDATE_DATE,
89     LAST_UPDATED_BY,
90     LAST_UPDATE_LOGIN
91   ) values (
92     X_ATTRIBUTE3,
93     X_ATTRIBUTE4,
94     X_ATTRIBUTE5,
95     X_ATTRIBUTE6,
96     X_ATTRIBUTE7,
97     X_ATTRIBUTE8,
98     X_ATTRIBUTE9,
99     X_ATTRIBUTE10,
100     X_ATTRIBUTE11,
101     X_ATTRIBUTE12,
102     X_ATTRIBUTE13,
103     X_ATTRIBUTE14,
104     X_ATTRIBUTE15,
105     X_OUTSIDE_OPERATION_FLAG,
106     X_REQUEST_ID,
107     X_RECEIVE_CLOSE_TOLERANCE,
108     X_LINE_TYPE_ID,
109     X_ORDER_TYPE_LOOKUP_CODE,
110     X_PURCHASE_BASIS,                 -- <SERVICES FPJ>
111     X_MATCHING_BASIS,                 -- <SERVICES FPJ>
112     X_CATEGORY_ID,
113     X_UNIT_OF_MEASURE,
114     X_UNIT_PRICE,
115     X_RECEIVING_FLAG,
116     X_INACTIVE_DATE,
117     X_ATTRIBUTE_CATEGORY,
118     X_ATTRIBUTE1,
119     X_ATTRIBUTE2,
120     X_CREATION_DATE,
121     X_CREATED_BY,
122     X_LAST_UPDATE_DATE,
123     X_LAST_UPDATED_BY,
124     X_LAST_UPDATE_LOGIN
125   );
126 
127   exception
128    WHEN OTHERS THEN
129       po_message_s.sql_error('val_destination_info', x_progress, sqlcode);
130    RAISE;
131   end;
132 
133   begin
134 
135  x_progress := '002';
136   insert into PO_LINE_TYPES_TL (
137     LAST_UPDATE_LOGIN,
138     CREATION_DATE,
139     LINE_TYPE_ID,
140     DESCRIPTION,
141     LINE_TYPE,
142     LAST_UPDATE_DATE,
143     LAST_UPDATED_BY,
144     CREATED_BY,
145     LANGUAGE,
146     SOURCE_LANG
147   ) select
148     X_LAST_UPDATE_LOGIN,
149     X_CREATION_DATE,
150     X_LINE_TYPE_ID,
151     X_DESCRIPTION,
152     X_LINE_TYPE,
153     X_LAST_UPDATE_DATE,
154     X_LAST_UPDATED_BY,
155     X_CREATED_BY,
156     L.LANGUAGE_CODE,
157     userenv('LANG')
158   from FND_LANGUAGES L
159   where L.INSTALLED_FLAG in ('I', 'B')
160   and not exists
161     (select NULL
162     from PO_LINE_TYPES_TL T
163     where T.LINE_TYPE_ID = X_LINE_TYPE_ID
164     and T.LANGUAGE = L.LANGUAGE_CODE);
165 
166   open c;
167   fetch c into X_ROWID;
168   if (c%notfound) then
169     close c;
170     raise no_data_found;
171   end if;
172   close c;
173 
174   exception
175 
176    WHEN OTHERS THEN
177       po_message_s.sql_error('val_destination_info', x_progress, sqlcode);
178    RAISE;
179 
180   end;
181 
182 end INSERT_ROW;
183 
184 procedure LOCK_ROW (
185   X_LINE_TYPE_ID in NUMBER,
186   X_ATTRIBUTE3 in VARCHAR2,
187   X_ATTRIBUTE4 in VARCHAR2,
188   X_ATTRIBUTE5 in VARCHAR2,
189   X_ATTRIBUTE6 in VARCHAR2,
190   X_ATTRIBUTE7 in VARCHAR2,
191   X_ATTRIBUTE8 in VARCHAR2,
192   X_ATTRIBUTE9 in VARCHAR2,
193   X_ATTRIBUTE10 in VARCHAR2,
194   X_ATTRIBUTE11 in VARCHAR2,
195   X_ATTRIBUTE12 in VARCHAR2,
196   X_ATTRIBUTE13 in VARCHAR2,
197   X_ATTRIBUTE14 in VARCHAR2,
198   X_ATTRIBUTE15 in VARCHAR2,
199   X_OUTSIDE_OPERATION_FLAG in VARCHAR2,
200   X_REQUEST_ID in NUMBER,
201   X_RECEIVE_CLOSE_TOLERANCE in NUMBER,
202   X_ORDER_TYPE_LOOKUP_CODE in VARCHAR2,
203   X_PURCHASE_BASIS IN PO_LINE_TYPES_B.purchase_basis%TYPE,  -- <SERVICES FPJ>
204   X_MATCHING_BASIS IN PO_LINE_TYPES_B.matching_basis%TYPE,  -- <SERVICES FPJ>
205   X_CATEGORY_ID in NUMBER,
206   X_UNIT_OF_MEASURE in VARCHAR2,
207   X_UNIT_PRICE in NUMBER,
208   X_RECEIVING_FLAG in VARCHAR2,
209   X_INACTIVE_DATE in DATE,
210   X_ATTRIBUTE_CATEGORY in VARCHAR2,
211   X_ATTRIBUTE1 in VARCHAR2,
212   X_ATTRIBUTE2 in VARCHAR2,
213   X_LINE_TYPE in VARCHAR2,
214   X_DESCRIPTION in VARCHAR2
215 ) is
216   cursor c is select
217       ATTRIBUTE3,
218       ATTRIBUTE4,
219       ATTRIBUTE5,
220       ATTRIBUTE6,
221       ATTRIBUTE7,
222       ATTRIBUTE8,
223       ATTRIBUTE9,
224       ATTRIBUTE10,
225       ATTRIBUTE11,
226       ATTRIBUTE12,
227       ATTRIBUTE13,
228       ATTRIBUTE14,
229       ATTRIBUTE15,
230       OUTSIDE_OPERATION_FLAG,
231       REQUEST_ID,
232       RECEIVE_CLOSE_TOLERANCE,
233       ORDER_TYPE_LOOKUP_CODE,
234       PURCHASE_BASIS,              -- <SERVICES FPJ>
235       MATCHING_BASIS,              -- <SERVICES FPJ>
236       CATEGORY_ID,
237       UNIT_OF_MEASURE,
238       UNIT_PRICE,
239       RECEIVING_FLAG,
240       INACTIVE_DATE,
241       ATTRIBUTE_CATEGORY,
242       ATTRIBUTE1,
243       ATTRIBUTE2
244     from PO_LINE_TYPES_B
245     where LINE_TYPE_ID = X_LINE_TYPE_ID
246     for update of LINE_TYPE_ID nowait;
247   recinfo c%rowtype;
248 
249   cursor c1 is select
250       LINE_TYPE,
251       DESCRIPTION,
252       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
253     from PO_LINE_TYPES_TL
254     where LINE_TYPE_ID = X_LINE_TYPE_ID
255     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
256     for update of LINE_TYPE_ID nowait;
257 begin
258   open c;
259   fetch c into recinfo;
260   if (c%notfound) then
261     close c;
262     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
263     app_exception.raise_exception;
264   end if;
265   close c;
266   if (    ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
267            OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
268       AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
269            OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
270       AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
271            OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
272       AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
273            OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
274       AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
275            OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
276       AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
277            OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
278       AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
279            OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
280       AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
281            OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
282       AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
283            OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
284       AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
285            OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
286       AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
287            OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
288       AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
289            OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
290       AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
291            OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
292       AND ((recinfo.OUTSIDE_OPERATION_FLAG = X_OUTSIDE_OPERATION_FLAG)
293            OR ((recinfo.OUTSIDE_OPERATION_FLAG is null) AND (X_OUTSIDE_OPERATION_FLAG is null)))
294       AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
295            OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
296       AND ((recinfo.RECEIVE_CLOSE_TOLERANCE = X_RECEIVE_CLOSE_TOLERANCE)
297            OR ((recinfo.RECEIVE_CLOSE_TOLERANCE is null) AND (X_RECEIVE_CLOSE_TOLERANCE is null)))
298       AND (recinfo.ORDER_TYPE_LOOKUP_CODE = X_ORDER_TYPE_LOOKUP_CODE)
299       AND (recinfo.PURCHASE_BASIS = X_PURCHASE_BASIS)     -- <SERVICES FPJ>
300       AND (recinfo.MATCHING_BASIS = X_MATCHING_BASIS)     -- <SERVICES FPJ>
301       AND ((recinfo.CATEGORY_ID = X_CATEGORY_ID)
302            OR ((recinfo.CATEGORY_ID is null) AND (X_CATEGORY_ID is null)))
303       AND ((recinfo.UNIT_OF_MEASURE = X_UNIT_OF_MEASURE)
304            OR ((recinfo.UNIT_OF_MEASURE is null) AND (X_UNIT_OF_MEASURE is null)))
305       AND ((recinfo.UNIT_PRICE = X_UNIT_PRICE)
306            OR ((recinfo.UNIT_PRICE is null) AND (X_UNIT_PRICE is null)))
307       AND ((recinfo.RECEIVING_FLAG = X_RECEIVING_FLAG)
308            OR ((recinfo.RECEIVING_FLAG is null) AND (X_RECEIVING_FLAG is null)))
309       AND ((recinfo.INACTIVE_DATE = X_INACTIVE_DATE)
310            OR ((recinfo.INACTIVE_DATE is null) AND (X_INACTIVE_DATE is null)))
311       AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
312            OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
313       AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
314            OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
315       AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
316            OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
317   ) then
318     null;
319   else
320     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
321     app_exception.raise_exception;
322   end if;
323 
324   for tlinfo in c1 loop
325     if (tlinfo.BASELANG = 'Y') then
326       if (    (tlinfo.LINE_TYPE = X_LINE_TYPE)
327           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
328                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
329       ) then
330         null;
331       else
332         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
333         app_exception.raise_exception;
334       end if;
335     end if;
336   end loop;
337   return;
338 end LOCK_ROW;
339 
340 procedure UPDATE_ROW (
341   X_LINE_TYPE_ID in NUMBER,
342   X_ATTRIBUTE3 in VARCHAR2,
343   X_ATTRIBUTE4 in VARCHAR2,
344   X_ATTRIBUTE5 in VARCHAR2,
345   X_ATTRIBUTE6 in VARCHAR2,
346   X_ATTRIBUTE7 in VARCHAR2,
347   X_ATTRIBUTE8 in VARCHAR2,
348   X_ATTRIBUTE9 in VARCHAR2,
349   X_ATTRIBUTE10 in VARCHAR2,
350   X_ATTRIBUTE11 in VARCHAR2,
351   X_ATTRIBUTE12 in VARCHAR2,
352   X_ATTRIBUTE13 in VARCHAR2,
353   X_ATTRIBUTE14 in VARCHAR2,
354   X_ATTRIBUTE15 in VARCHAR2,
355   X_OUTSIDE_OPERATION_FLAG in VARCHAR2,
356   X_REQUEST_ID in NUMBER,
357   X_RECEIVE_CLOSE_TOLERANCE in NUMBER,
358   X_ORDER_TYPE_LOOKUP_CODE in VARCHAR2,
359   X_PURCHASE_BASIS IN PO_LINE_TYPES_B.purchase_basis%TYPE,  -- <SERVICES FPJ>
360   X_MATCHING_BASIS IN PO_LINE_TYPES_B.matching_basis%TYPE,  -- <SERVICES FPJ>
361   X_CATEGORY_ID in NUMBER,
362   X_UNIT_OF_MEASURE in VARCHAR2,
363   X_UNIT_PRICE in NUMBER,
364   X_RECEIVING_FLAG in VARCHAR2,
365   X_INACTIVE_DATE in DATE,
366   X_ATTRIBUTE_CATEGORY in VARCHAR2,
367   X_ATTRIBUTE1 in VARCHAR2,
368   X_ATTRIBUTE2 in VARCHAR2,
369   X_LINE_TYPE in VARCHAR2,
370   X_DESCRIPTION in VARCHAR2,
371   X_LAST_UPDATE_DATE in DATE,
372   X_LAST_UPDATED_BY in NUMBER,
373   X_LAST_UPDATE_LOGIN in NUMBER
374 ) is
375 begin
376   update PO_LINE_TYPES_B set
377     ATTRIBUTE3 = X_ATTRIBUTE3,
378     ATTRIBUTE4 = X_ATTRIBUTE4,
379     ATTRIBUTE5 = X_ATTRIBUTE5,
380     ATTRIBUTE6 = X_ATTRIBUTE6,
384     ATTRIBUTE10 = X_ATTRIBUTE10,
381     ATTRIBUTE7 = X_ATTRIBUTE7,
382     ATTRIBUTE8 = X_ATTRIBUTE8,
383     ATTRIBUTE9 = X_ATTRIBUTE9,
385     ATTRIBUTE11 = X_ATTRIBUTE11,
386     ATTRIBUTE12 = X_ATTRIBUTE12,
387     ATTRIBUTE13 = X_ATTRIBUTE13,
388     ATTRIBUTE14 = X_ATTRIBUTE14,
389     ATTRIBUTE15 = X_ATTRIBUTE15,
390     OUTSIDE_OPERATION_FLAG = X_OUTSIDE_OPERATION_FLAG,
391     REQUEST_ID = X_REQUEST_ID,
392     RECEIVE_CLOSE_TOLERANCE = X_RECEIVE_CLOSE_TOLERANCE,
393     ORDER_TYPE_LOOKUP_CODE = X_ORDER_TYPE_LOOKUP_CODE,
394     PURCHASE_BASIS = X_PURCHASE_BASIS,                -- <SERVICES FPJ>
395     MATCHING_BASIS = X_MATCHING_BASIS,                -- <SERVICES FPJ>
396     CATEGORY_ID = X_CATEGORY_ID,
397     UNIT_OF_MEASURE = X_UNIT_OF_MEASURE,
398     UNIT_PRICE = X_UNIT_PRICE,
399     RECEIVING_FLAG = X_RECEIVING_FLAG,
400     INACTIVE_DATE = X_INACTIVE_DATE,
401     ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
402     ATTRIBUTE1 = X_ATTRIBUTE1,
403     ATTRIBUTE2 = X_ATTRIBUTE2,
404     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
405     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
406     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
407   where LINE_TYPE_ID = X_LINE_TYPE_ID;
408 
409   if (sql%notfound) then
410     raise no_data_found;
411   end if;
412 
413   update PO_LINE_TYPES_TL set
414     LINE_TYPE = X_LINE_TYPE,
415     DESCRIPTION = X_DESCRIPTION,
416     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
417     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
418     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
419     SOURCE_LANG = userenv('LANG')
420   where LINE_TYPE_ID = X_LINE_TYPE_ID
421   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
422 
423   if (sql%notfound) then
424     raise no_data_found;
425   end if;
426 end UPDATE_ROW;
427 
428 procedure DELETE_ROW (
429   X_LINE_TYPE_ID in NUMBER
430 ) is
431 begin
432   delete from PO_LINE_TYPES_TL
433   where LINE_TYPE_ID = X_LINE_TYPE_ID;
434 
435   if (sql%notfound) then
436     raise no_data_found;
437   end if;
438 
439   delete from PO_LINE_TYPES_B
440   where LINE_TYPE_ID = X_LINE_TYPE_ID;
441 
442   if (sql%notfound) then
443     raise no_data_found;
444   end if;
445 end DELETE_ROW;
446 
447 procedure ADD_LANGUAGE
448 is
449 begin
450   delete from PO_LINE_TYPES_TL T
451   where not exists
452     (select NULL
453     from PO_LINE_TYPES_B B
454     where B.LINE_TYPE_ID = T.LINE_TYPE_ID
455     );
456 
457   update PO_LINE_TYPES_TL T set (
458       LINE_TYPE,
459       DESCRIPTION
460     ) = (select
461       B.LINE_TYPE,
462       B.DESCRIPTION
463     from PO_LINE_TYPES_TL B
464     where B.LINE_TYPE_ID = T.LINE_TYPE_ID
465     and B.LANGUAGE = T.SOURCE_LANG)
466   where (
467       T.LINE_TYPE_ID,
468       T.LANGUAGE
469   ) in (select
470       SUBT.LINE_TYPE_ID,
471       SUBT.LANGUAGE
472     from PO_LINE_TYPES_TL SUBB, PO_LINE_TYPES_TL SUBT
473     where SUBB.LINE_TYPE_ID = SUBT.LINE_TYPE_ID
474     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
475     and (SUBB.LINE_TYPE <> SUBT.LINE_TYPE
476       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
477       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
478       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
479   ));
480 
481   insert into PO_LINE_TYPES_TL (
482     LAST_UPDATE_LOGIN,
483     CREATION_DATE,
484     LINE_TYPE_ID,
485     DESCRIPTION,
486     LINE_TYPE,
487     LAST_UPDATE_DATE,
488     LAST_UPDATED_BY,
489     CREATED_BY,
490     LANGUAGE,
491     SOURCE_LANG
492   ) select
493     B.LAST_UPDATE_LOGIN,
494     B.CREATION_DATE,
495     B.LINE_TYPE_ID,
496     B.DESCRIPTION,
497     B.LINE_TYPE,
498     B.LAST_UPDATE_DATE,
499     B.LAST_UPDATED_BY,
500     B.CREATED_BY,
501     L.LANGUAGE_CODE,
502     B.SOURCE_LANG
503   from PO_LINE_TYPES_TL B, FND_LANGUAGES L
504   where L.INSTALLED_FLAG in ('I', 'B')
505   and B.LANGUAGE = userenv('LANG')
506   and not exists
507     (select NULL
508     from PO_LINE_TYPES_TL T
509     where T.LINE_TYPE_ID = B.LINE_TYPE_ID
510     and T.LANGUAGE = L.LANGUAGE_CODE);
511 end ADD_LANGUAGE;
512 
513 procedure TRANSLATE_ROW (X_LINE_TYPE_ID in NUMBER,
514                          X_LINE_TYPE    in VARCHAR2,
515                          X_DESCRIPTION  in VARCHAR2,
516                          X_OWNER        in VARCHAR2,
517                          X_LAST_UPDATE_DATE in VARCHAR2,
518                          X_CUSTOM_MODE  in VARCHAR2) IS
519 
520 f_luby    number;  -- entity owner in file
521 f_ludate  date;    -- entity update date in file
522 db_luby   number;  -- entity owner in db
523 db_ludate date;    -- entity update date in db
524 
525 begin
526 
527   f_luby := fnd_load_util.owner_id(X_OWNER);
528   f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'DD/MM/YYYY'), sysdate);
529 
530   select LAST_UPDATED_BY, LAST_UPDATE_DATE
531   into  db_luby, db_ludate
532   from PO_LINE_TYPES_TL
533   where line_type_id = X_LINE_TYPE_ID
534   and  language = userenv('LANG') ;
535 
536   if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
537                                 db_ludate, X_CUSTOM_MODE)) then
538 
539     update PO_LINE_TYPES_TL set
540       line_type = X_LINE_TYPE,
541       description = X_DESCRIPTION,
542       last_update_date  = f_ludate ,
543       last_updated_by   = f_luby,
544       last_update_login = 0,
545       source_lang       = userenv('LANG')
546     where line_type_id = X_LINE_TYPE_ID
547     and  userenv('LANG') in (language, source_lang);
548 
549   end if;
550 
551 exception
552  when no_data_found then
553     -- Do not insert missing translations, skip this row
554     null;
555 end TRANSLATE_ROW;
556 
557 procedure LOAD_ROW
558 (   X_LINE_TYPE_ID in out NOCOPY NUMBER,
559     X_ATTRIBUTE_CATEGORY in VARCHAR2,
560     X_ATTRIBUTE1 in VARCHAR2,
561     X_ATTRIBUTE2 in VARCHAR2,
562     X_ATTRIBUTE3 in VARCHAR2,
563     X_ATTRIBUTE4 in VARCHAR2,
564     X_ATTRIBUTE5 in VARCHAR2,
565     X_ATTRIBUTE6 in VARCHAR2,
566     X_ATTRIBUTE7 in VARCHAR2,
567     X_ATTRIBUTE8 in VARCHAR2,
568     X_ATTRIBUTE9 in VARCHAR2,
569     X_ATTRIBUTE10 in VARCHAR2,
570     X_ATTRIBUTE11 in VARCHAR2,
571     X_ATTRIBUTE12 in VARCHAR2,
572     X_ATTRIBUTE13 in VARCHAR2,
573     X_ATTRIBUTE14 in VARCHAR2,
574     X_ATTRIBUTE15 in VARCHAR2,
575     X_OUTSIDE_OPERATION_FLAG in VARCHAR2,
576     X_REQUEST_ID in NUMBER,
577     X_RECEIVE_CLOSE_TOLERANCE in NUMBER,
578     X_ORDER_TYPE_LOOKUP_CODE in VARCHAR2,
579     X_PURCHASE_BASIS IN PO_LINE_TYPES_B.purchase_basis%TYPE,  -- <SERVICES FPJ>
580     X_MATCHING_BASIS IN PO_LINE_TYPES_B.matching_basis%TYPE,  -- <SERVICES FPJ>
584     X_RECEIVING_FLAG in VARCHAR2,
581     X_CATEGORY_CODE in VARCHAR2,
582     X_UNIT_OF_MEASURE in VARCHAR2,
583     X_UNIT_PRICE in NUMBER,
585     X_INACTIVE_DATE in DATE,
586     X_LINE_TYPE in VARCHAR2,
587     X_DESCRIPTION in VARCHAR2,
588     X_OWNER in VARCHAR2,
589     X_LAST_UPDATE_DATE in VARCHAR2,
590     X_CUSTOM_MODE in VARCHAR2
591 ) IS
592 
593 l_row_id	varchar2(64);
594 l_category_id number;
595 f_luby    number;  -- entity owner in file
596 f_ludate  date;    -- entity update date in file
597 db_luby   number;  -- entity owner in db
598 db_ludate date;    -- entity update date in db
599 
600 begin
601 
602    if x_category_code is not null then
603       begin
604 
605        SELECT mck.category_id
606        into   l_category_id
607        FROM mtl_categories_kfv mck,
608             mtl_category_sets mcs,
609             mtl_default_category_sets mdcs
610        WHERE
611            mck.structure_id = mcs.structure_id
612            AND mcs.category_set_id = mdcs.category_set_id
613            AND mdcs.functional_area_id = 2
614            AND concatenated_segments = x_category_code ;
615       exception
616          when others then null;
617       end;
618 
619    end if;
620 
621   f_luby := fnd_load_util.owner_id(X_OWNER);
622   f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'DD/MM/YYYY'), sysdate);
623 
624   select LAST_UPDATED_BY, LAST_UPDATE_DATE
625   into  db_luby, db_ludate
626   from PO_LINE_TYPES_VL
627   where line_type_id = X_LINE_TYPE_ID;
628 
629     if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
630                                   db_ludate, X_CUSTOM_MODE)) then
631 
632      UPDATE_ROW (X_LINE_TYPE_ID ,
633                  X_ATTRIBUTE3 ,
634                  X_ATTRIBUTE4 ,
635                  X_ATTRIBUTE5 ,
636                  X_ATTRIBUTE6 ,
637                  X_ATTRIBUTE7 ,
638                  X_ATTRIBUTE8 ,
639                  X_ATTRIBUTE9 ,
640                  X_ATTRIBUTE10 ,
641                  X_ATTRIBUTE11 ,
642                  X_ATTRIBUTE12 ,
643                  X_ATTRIBUTE13 ,
644                  X_ATTRIBUTE14 ,
645                  X_ATTRIBUTE15 ,
646                  X_OUTSIDE_OPERATION_FLAG ,
647                  X_REQUEST_ID ,
648                  X_RECEIVE_CLOSE_TOLERANCE ,
649                  X_ORDER_TYPE_LOOKUP_CODE ,
650                  X_PURCHASE_BASIS ,       -- <SERVICES FPJ>
651                  X_MATCHING_BASIS ,       -- <SERVICES FPJ>
652                  L_CATEGORY_ID ,
653                  X_UNIT_OF_MEASURE ,
654                  X_UNIT_PRICE ,
655                  X_RECEIVING_FLAG ,
656                  X_INACTIVE_DATE ,
657                  X_ATTRIBUTE_CATEGORY ,
658                  X_ATTRIBUTE1 ,
659                  X_ATTRIBUTE2 ,
660                  X_LINE_TYPE ,
661                  X_DESCRIPTION ,
662                  f_ludate ,
663                  f_luby ,
664                  0);
665 
666      end if;
667 
668   exception
669      when NO_DATA_FOUND then
670          INSERT_ROW (l_row_id ,
671                      X_LINE_TYPE_ID ,
672                      X_ATTRIBUTE3 ,
673                      X_ATTRIBUTE4 ,
674                      X_ATTRIBUTE5 ,
675                      X_ATTRIBUTE6 ,
676                      X_ATTRIBUTE7 ,
677                      X_ATTRIBUTE8 ,
678                      X_ATTRIBUTE9 ,
679                      X_ATTRIBUTE10 ,
680                      X_ATTRIBUTE11 ,
681                      X_ATTRIBUTE12 ,
682                      X_ATTRIBUTE13 ,
683                      X_ATTRIBUTE14 ,
684                      X_ATTRIBUTE15 ,
685                      X_OUTSIDE_OPERATION_FLAG ,
686                      X_REQUEST_ID ,
687                      X_RECEIVE_CLOSE_TOLERANCE ,
688                      X_ORDER_TYPE_LOOKUP_CODE ,
689                      X_PURCHASE_BASIS ,       -- <SERVICES FPJ>
690                      X_MATCHING_BASIS ,       -- <SERVICES FPJ>
691                      L_CATEGORY_ID ,
692                      X_UNIT_OF_MEASURE ,
693                      X_UNIT_PRICE ,
694                      X_RECEIVING_FLAG ,
695                      X_INACTIVE_DATE ,
696                      X_ATTRIBUTE_CATEGORY ,
697                      X_ATTRIBUTE1 ,
698                      X_ATTRIBUTE2 ,
699                      X_LINE_TYPE ,
700                      X_DESCRIPTION ,
701                      f_ludate ,
702                      f_luby ,
703                      f_ludate ,
704                      f_luby ,
705                      0 );
706 
707 end LOAD_ROW;
708 
709 end PO_LINE_TYPES_PKG;