[Home] [Help]
PACKAGE BODY: APPS.ITA_SETUP_PARAMETER_MIG_PKG
Source
1 package body ITA_SETUP_PARAMETER_MIG_PKG as
2 /* $Header: itatmigb.pls 120.1 2005/09/19 17:08:36 cpetriuc noship $ */
3
4
5 procedure INSERT_ROW (
6 X_MIGRATION_ID in NUMBER,
7 X_OLD_PARAMETER_CODE in VARCHAR2,
8 X_NEW_PARAMETER_CODE in VARCHAR2,
9 X_START_DATE in DATE,
10 X_END_DATE in DATE,
11 X_SECURITY_GROUP_ID in NUMBER,
12 X_OBJECT_VERSION_NUMBER in NUMBER,
13 X_CREATION_DATE in DATE,
14 X_CREATED_BY in NUMBER,
15 X_LAST_UPDATE_DATE in DATE,
16 X_LAST_UPDATED_BY in NUMBER,
17 X_LAST_UPDATE_LOGIN in NUMBER
18 ) is
19 begin
20 insert into ITA_SETUP_PARAMETER_MIG (
21 MIGRATION_ID,
22 OLD_PARAMETER_CODE,
23 NEW_PARAMETER_CODE,
24 START_DATE,
25 END_DATE,
26 SECURITY_GROUP_ID,
27 OBJECT_VERSION_NUMBER,
28 CREATION_DATE,
29 CREATED_BY,
30 LAST_UPDATE_DATE,
31 LAST_UPDATED_BY,
32 LAST_UPDATE_LOGIN
33 ) values (
34 X_MIGRATION_ID,
35 X_OLD_PARAMETER_CODE,
36 X_NEW_PARAMETER_CODE,
37 X_START_DATE,
38 X_END_DATE,
39 X_SECURITY_GROUP_ID,
40 X_OBJECT_VERSION_NUMBER,
41 X_CREATION_DATE,
42 X_CREATED_BY,
43 X_LAST_UPDATE_DATE,
44 X_LAST_UPDATED_BY,
45 X_LAST_UPDATE_LOGIN
46 );
47 end INSERT_ROW;
48
49
50 procedure LOCK_ROW (
51 X_MIGRATION_ID in NUMBER,
52 X_OLD_PARAMETER_CODE in VARCHAR2,
53 X_NEW_PARAMETER_CODE in VARCHAR2,
54 X_START_DATE in DATE,
55 X_END_DATE in DATE,
56 X_SECURITY_GROUP_ID in NUMBER,
57 X_OBJECT_VERSION_NUMBER in NUMBER
58 ) is
59 cursor c is select
60 MIGRATION_ID,
61 OLD_PARAMETER_CODE,
62 NEW_PARAMETER_CODE,
63 START_DATE,
64 END_DATE,
65 SECURITY_GROUP_ID,
66 OBJECT_VERSION_NUMBER
67 from ITA_SETUP_PARAMETER_MIG
68 where MIGRATION_ID = X_MIGRATION_ID
69 for update of MIGRATION_ID nowait;
70 recinfo c%rowtype;
71
72 begin
73 open c;
74 fetch c into recinfo;
75 if (c%notfound) then
76 close c;
77 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
78 app_exception.raise_exception;
79 end if;
80 close c;
81 if ( (recinfo.MIGRATION_ID = X_MIGRATION_ID)
82 and ((recinfo.OLD_PARAMETER_CODE = X_OLD_PARAMETER_CODE)
83 or ((recinfo.OLD_PARAMETER_CODE is null) and (X_OLD_PARAMETER_CODE is null)))
84 and ((recinfo.NEW_PARAMETER_CODE = X_NEW_PARAMETER_CODE)
85 or ((recinfo.NEW_PARAMETER_CODE is null) and (X_NEW_PARAMETER_CODE is null)))
86 and ((recinfo.START_DATE = X_START_DATE)
87 or ((recinfo.START_DATE is null) and (X_START_DATE is null)))
88 and ((recinfo.END_DATE = X_END_DATE)
89 or ((recinfo.END_DATE is null) and (X_END_DATE is null)))
90 and ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
91 or ((recinfo.SECURITY_GROUP_ID is null) and (X_SECURITY_GROUP_ID is null)))
92 and ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
93 or ((recinfo.OBJECT_VERSION_NUMBER is null) and (X_OBJECT_VERSION_NUMBER is null)))
94 ) then
95 null;
96 else
97 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
98 app_exception.raise_exception;
99 end if;
100
101 return;
102 end LOCK_ROW;
103
104
105 procedure UPDATE_ROW (
106 X_MIGRATION_ID in NUMBER,
107 X_OLD_PARAMETER_CODE in VARCHAR2,
108 X_NEW_PARAMETER_CODE in VARCHAR2,
109 X_START_DATE in DATE,
110 X_END_DATE in DATE,
111 X_SECURITY_GROUP_ID in NUMBER,
112 X_OBJECT_VERSION_NUMBER in NUMBER,
113 X_LAST_UPDATE_DATE in DATE,
114 X_LAST_UPDATED_BY in NUMBER,
115 X_LAST_UPDATE_LOGIN in NUMBER
116 ) is
117 begin
118 update ITA_SETUP_PARAMETER_MIG set
119 OLD_PARAMETER_CODE = X_OLD_PARAMETER_CODE,
120 NEW_PARAMETER_CODE = X_NEW_PARAMETER_CODE,
121 START_DATE = nvl(START_DATE, X_START_DATE),
122 END_DATE = nvl(END_DATE, X_END_DATE),
123 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
124 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
125 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
126 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
127 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
128 where MIGRATION_ID = X_MIGRATION_ID;
129
130 if (sql%notfound) then
131 raise no_data_found;
132 end if;
133
134 end UPDATE_ROW;
135
136
137 procedure DELETE_ROW (
138 X_MIGRATION_ID in NUMBER
139 ) is
140 begin
141 delete from ITA_SETUP_PARAMETER_MIG
142 where MIGRATION_ID = X_MIGRATION_ID;
143
144 if (sql%notfound) then
145 raise no_data_found;
146 end if;
147 end DELETE_ROW;
148
149
150 procedure LOAD_ROW (
151 X_MIGRATION_ID in NUMBER,
152 X_OLD_PARAMETER_CODE in VARCHAR2,
153 X_NEW_PARAMETER_CODE in VARCHAR2,
154 X_START_DATE in DATE,
155 X_END_DATE in DATE,
156 X_LAST_UPDATE_DATE in VARCHAR2,
157 X_OWNER in VARCHAR2,
158 X_CUSTOM_MODE in VARCHAR2
159 ) is
160
161 f_luby number; -- entity owner in file
162 f_ludate date; -- entity update date in file
163 db_luby number; -- entity owner in db
164 db_ludate date; -- entity update date in db
165
166 begin
167 -- Translate owner to file_last_updated_by
168 f_luby := fnd_load_util.owner_id(X_OWNER);
169
170 -- Translate char last_update_date to date
171 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
172
173 select LAST_UPDATED_BY, LAST_UPDATE_DATE into db_luby, db_ludate
174 from ITA_SETUP_PARAMETER_MIG
175 where MIGRATION_ID = X_MIGRATION_ID;
176
177 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, X_CUSTOM_MODE))
178 then ITA_SETUP_PARAMETER_MIG_PKG.UPDATE_ROW (
179 X_MIGRATION_ID => X_MIGRATION_ID,
180 X_OLD_PARAMETER_CODE => X_OLD_PARAMETER_CODE,
181 X_NEW_PARAMETER_CODE => X_NEW_PARAMETER_CODE,
182 X_START_DATE => X_START_DATE,
183 X_END_DATE => X_END_DATE,
184 X_SECURITY_GROUP_ID => null,
185 X_OBJECT_VERSION_NUMBER => 1,
186 X_LAST_UPDATE_DATE => f_ludate,
187 X_LAST_UPDATED_BY => f_luby,
188 X_LAST_UPDATE_LOGIN => 0);
189 end if;
190 exception when NO_DATA_FOUND
191 then ITA_SETUP_PARAMETER_MIG_PKG.INSERT_ROW (
192 X_MIGRATION_ID => X_MIGRATION_ID,
193 X_OLD_PARAMETER_CODE => X_OLD_PARAMETER_CODE,
194 X_NEW_PARAMETER_CODE => X_NEW_PARAMETER_CODE,
195 X_START_DATE => X_START_DATE,
196 X_END_DATE => X_END_DATE,
197 X_SECURITY_GROUP_ID => null,
198 X_OBJECT_VERSION_NUMBER => 1,
199 X_CREATION_DATE => f_ludate,
200 X_CREATED_BY => f_luby,
201 X_LAST_UPDATE_DATE => f_ludate,
202 X_LAST_UPDATED_BY => f_luby,
203 X_LAST_UPDATE_LOGIN => 0);
204 end LOAD_ROW;
205
206
207 end ITA_SETUP_PARAMETER_MIG_PKG;