[Home] [Help]
PACKAGE BODY: APPS.FEM_IMPEXP_AM_LOOKUP_PKG
Source
1 package body FEM_IMPEXP_AM_LOOKUP_PKG as
2 /* $Header: FEMILKPB.pls 120.0 2005/06/06 20:13:12 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_OBJECT_TYPE_CODE in VARCHAR2,
6 X_AM_NAME in VARCHAR2,
7 X_CREATION_DATE in DATE,
8 X_CREATED_BY in NUMBER,
9 X_LAST_UPDATE_DATE in DATE,
10 X_LAST_UPDATED_BY in NUMBER,
11 X_LAST_UPDATE_LOGIN in NUMBER
12 ) is
13 cursor C is select ROWID from FEM_IMPEXP_AM_LOOKUP
14 where OBJECT_TYPE_CODE = X_OBJECT_TYPE_CODE
15 ;
16 begin
17 insert into FEM_IMPEXP_AM_LOOKUP(
18 OBJECT_TYPE_CODE,
19 AM_NAME,
20 CREATED_BY,
21 CREATION_DATE,
22 LAST_UPDATED_BY,
23 LAST_UPDATE_DATE,
24 LAST_UPDATE_LOGIN
25 ) values
26 (
27 X_OBJECT_TYPE_CODE,
28 X_AM_NAME,
29 X_CREATED_BY,
30 X_CREATION_DATE,
31 X_LAST_UPDATED_BY,
32 X_LAST_UPDATE_DATE,
33 X_LAST_UPDATE_LOGIN
34 );
35
36 open c;
37 fetch c into X_ROWID;
38 if (c%notfound) then
39 close c;
40 raise no_data_found;
41 end if;
42 close c;
43
44 end INSERT_ROW;
45
46 procedure UPDATE_ROW (
47 X_OBJECT_TYPE_CODE in VARCHAR2,
48 X_AM_NAME in VARCHAR2,
49 X_LAST_UPDATE_DATE in DATE,
50 X_LAST_UPDATED_BY in NUMBER,
51 X_LAST_UPDATE_LOGIN in NUMBER
52 ) is
53 begin
54 update FEM_IMPEXP_AM_LOOKUP set
55 AM_NAME = X_AM_NAME,
56 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
57 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
58 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
59 where OBJECT_TYPE_CODE = X_OBJECT_TYPE_CODE;
60
61 if (sql%notfound) then
62 raise no_data_found;
63 end if;
64 end UPDATE_ROW;
65
66 procedure DELETE_ROW (
67 X_OBJECT_TYPE_CODE in VARCHAR2
68 ) is
69 begin
70 delete from FEM_IMPEXP_AM_LOOKUP
71 where OBJECT_TYPE_CODE = X_OBJECT_TYPE_CODE;
72
73 if (sql%notfound) then
74 raise no_data_found;
75 end if;
76
77 end DELETE_ROW;
78
79 procedure LOAD_ROW(
80 X_OBJECT_TYPE_CODE in VARCHAR2,
81 X_OWNER in VARCHAR2,
82 X_AM_NAME in VARCHAR2,
83 X_LAST_UPDATE_DATE in VARCHAR2,
84 X_CUSTOM_MODE in VARCHAR2) is
85
86 row_id varchar2(64);
87 f_luby number; -- entity owner in file
88 f_ludate date; -- entity update date in file
89 db_luby number; -- entity owner in db
90 db_ludate date; -- entity update date in db
91 begin
92
93 -- Translate owner to file_last_updated_by
94 if (X_OWNER = 'SEED') then
95 f_luby := 1;
96 else
97 f_luby := 0;
98 end if;
99
100 -- Translate char last_update_date to date
101 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'DD/MM/YYYY'), sysdate);
102
103 select LAST_UPDATED_BY, LAST_UPDATE_DATE
104 into db_luby, db_ludate
105 from FEM_IMPEXP_AM_LOOKUP
106 where OBJECT_TYPE_CODE = X_OBJECT_TYPE_CODE;
107
108 -- Update record, honoring customization mode.
109 -- Record should be updated only if:
110 -- a. CUSTOM_MODE = FORCE, or
111 -- b. file owner is CUSTOM, db owner is SEED
112 -- c. owners are the same, and file_date > db_date
113 if ((X_CUSTOM_MODE = 'FORCE') or
114 ((f_luby = 0) and (db_luby = 1)) or
115 ((f_luby = db_luby) and (f_ludate > db_ludate)))
116 then
117 FEM_IMPEXP_AM_LOOKUP_PKG.UPDATE_ROW(
118 X_OBJECT_TYPE_CODE => X_OBJECT_TYPE_CODE,
119 X_AM_NAME => X_AM_NAME,
120 X_LAST_UPDATE_DATE => f_ludate,
121 X_LAST_UPDATED_BY => f_luby,
122 X_LAST_UPDATE_LOGIN => 0);
123 end if;
124 exception
125 when no_data_found then
126 -- Record doesn't exist - insert in all cases
127 FEM_IMPEXP_AM_LOOKUP_PKG.Insert_Row(
128 X_ROWID => row_id,
129 X_OBJECT_TYPE_CODE => X_OBJECT_TYPE_CODE,
130 X_AM_NAME => X_AM_NAME,
131 X_CREATION_DATE => f_ludate,
132 X_CREATED_BY => f_luby,
133 X_LAST_UPDATE_DATE => f_ludate,
134 X_LAST_UPDATED_BY => f_luby,
135 X_LAST_UPDATE_LOGIN => 0);
136 end LOAD_ROW;
137 end FEM_IMPEXP_AM_LOOKUP_PKG;