1 package body JTF_IH_ACT_ACT_ITMS_SEED_PKG as
2 /* $Header: JTFIHAAB.pls 120.2 2005/07/08 07:50:47 nchouras ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_ACTION_ID in NUMBER,
6 X_ACTION_ITEM_ID in NUMBER,
7 X_DEFAULT_WRAP_ID in NUMBER,
8 X_OBJECT_VERSION_NUMBER in NUMBER,
9 X_CREATION_DATE in DATE,
10 X_CREATED_BY in NUMBER,
11 X_LAST_UPDATE_DATE in DATE,
12 X_LAST_UPDATED_BY in NUMBER,
13 X_LAST_UPDATE_LOGIN in NUMBER
14 ) is
15 cursor C is select ROWID from JTF_IH_ACTION_ACTION_ITEMS
16 where ACTION_ID = X_ACTION_ID
17 AND ACTION_ITEM_ID = X_ACTION_ITEM_ID
18 ;
19 begin
20 insert into JTF_IH_ACTION_ACTION_ITEMS (
21 ACTION_ID,
22 ACTION_ITEM_ID,
23 DEFAULT_WRAP_ID,
24 OBJECT_VERSION_NUMBER,
25 CREATION_DATE,
26 CREATED_BY,
27 LAST_UPDATE_DATE,
28 LAST_UPDATED_BY,
29 LAST_UPDATE_LOGIN
30 ) values (
31 X_ACTION_ID,
32 X_ACTION_ITEM_ID,
33 X_DEFAULT_WRAP_ID,
34 X_OBJECT_VERSION_NUMBER,
35 X_CREATION_DATE,
36 X_CREATED_BY,
37 X_LAST_UPDATE_DATE,
38 X_LAST_UPDATED_BY,
39 X_LAST_UPDATE_LOGIN
40 );
41 open c;
42 fetch c into X_ROWID;
43 if (c%notfound) then
44 close c;
45 raise no_data_found;
46 end if;
47 close c;
48 end INSERT_ROW;
49
50 procedure LOCK_ROW (
51 X_ACTION_ID in NUMBER,
52 X_ACTION_ITEM_ID in NUMBER,
53 X_DEFAULT_WRAP_ID in NUMBER,
54 X_OBJECT_VERSION_NUMBER in NUMBER
55 ) is
56 cursor c is select
57 OBJECT_VERSION_NUMBER
58 from JTF_IH_ACTION_ACTION_ITEMS
59 where ACTION_ID = X_ACTION_ID AND ACTION_ITEM_ID = X_ACTION_ITEM_ID
60 for update of ACTION_ID, ACTION_ITEM_ID nowait;
61 recinfo c%rowtype;
62
63 begin
64 open c;
65 fetch c into recinfo;
66 if (c%notfound) then
67 close c;
68 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
69 app_exception.raise_exception;
70 end if;
71 close c;
72 if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
73 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
74 ) then
75 null;
76 else
77 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
78 app_exception.raise_exception;
79 end if;
80 return;
81 end LOCK_ROW;
82
83 /* RDD - Bug 3772863 - Need to insure that the new default wrap_id is applyed to an existing row */
84 procedure UPDATE_ROW (
85 X_ACTION_ID in NUMBER,
86 X_ACTION_ITEM_ID in NUMBER,
87 X_DEFAULT_WRAP_ID in NUMBER,
88 X_OBJECT_VERSION_NUMBER in NUMBER,
89 X_LAST_UPDATE_DATE in DATE,
90 X_LAST_UPDATED_BY in NUMBER,
91 X_LAST_UPDATE_LOGIN in NUMBER
92 ) is
93 -- RDD - Bug 3772863 - Cursoe to get the existing value. If it is not null, then use in place of passed ID
94 cursor C is select DEFAULT_WRAP_ID,LAST_UPDATED_BY
95 from JTF_IH_ACTION_ACTION_ITEMS
96 where ACTION_ID = X_ACTION_ID AND
97 ACTION_ITEM_ID = X_ACTION_ITEM_ID;
98 l_default_wrap_id NUMBER;
99 l_last_updated_by NUMBER;
100
101 begin
102 -- RDD - Bug 3772863 - Get the rows current default ID
103 open c;
104 fetch c into l_default_wrap_id, l_last_updated_by;
105
106 -- RDD - Bug 3772863 - If the row was not found, then don't do the update.
107 if (c%notfound) then
108 raise no_data_found;
109 end if;
110
111 -- RDD - Bug 3772863 - determine if the value on the row is null or empty, then set to what is in the loader
112 if ((l_default_wrap_id is null) or (TRIM(l_default_wrap_id) = '')) then
113 -- set the default to what is passed
114 l_default_wrap_id := X_DEFAULT_WRAP_ID;
115 else
116 -- RDD Bug 3772863 - If the value is not null or empty and has not been changed
117 -- via the IH admin in the instance, then set to new seeded value from loader
118 if (l_last_updated_by = X_LAST_UPDATED_BY) then
119 l_default_wrap_id := X_DEFAULT_WRAP_ID;
120 end if;
121 end if;
122
123
124 update JTF_IH_ACTION_ACTION_ITEMS set
125 DEFAULT_WRAP_ID = l_default_wrap_id,
126 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
127 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
128 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
129 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
130 where ACTION_ID = X_ACTION_ID
131 AND ACTION_ITEM_ID = X_ACTION_ITEM_ID;
132
133 if (sql%notfound) then
134 raise no_data_found;
135 end if;
136 end UPDATE_ROW;
137
138 procedure DELETE_ROW (
139 X_ACTION_ID in NUMBER,
140 X_ACTION_ITEM_ID in NUMBER
141 ) is
142 begin
143 delete from JTF_IH_ACTION_ACTION_ITEMS
144 where ACTION_ID = X_ACTION_ID AND ACTION_ITEM_ID = X_ACTION_ITEM_ID;
145 if (sql%notfound) then
146 raise no_data_found;
147 end if;
148 end DELETE_ROW;
149
150
151 procedure LOAD_ROW (
152 X_ACTION_ID in NUMBER,
153 X_ACTION_ITEM_ID in NUMBER,
154 X_DEFAULT_WRAP_ID in NUMBER,
155 X_OBJECT_VERSION_NUMBER in NUMBER,
156 X_OWNER IN VARCHAR2
157 ) IS
158 l_user_id NUMBER := 0;
159 l_login_id NUMBER := 0;
160 l_row_id ROWID;
161 l_action_id NUMBER;
162 l_action_item_id NUMBER;
163 l_default_wrap_id NUMBER;
164 l_object_version_number NUMBER;
165 l_creation_date DATE;
166 l_created_by NUMBER;
167 begin
168 if (x_owner = 'SEED') then
169 --l_user_id := 1;
170 l_login_id := 0;
171 else
172 --l_user_id := fnd_global.user_id;
173 l_login_id := fnd_global.login_id;
174 end if;
175
176 l_user_id := fnd_load_util.owner_id(x_owner);
177 l_action_id := X_ACTION_ID;
178 l_action_item_id := X_ACTION_ITEM_ID;
179 l_default_wrap_id := X_DEFAULT_WRAP_ID;
180
181 IF X_OBJECT_VERSION_NUMBER IS NULL THEN
182 l_object_version_number := 1;
183 ELSE
184 l_object_version_number := X_OBJECT_VERSION_NUMBER;
185 END IF;
186
187 UPDATE_ROW(
188 X_ACTION_ID => l_action_id,
189 X_ACTION_ITEM_ID => l_action_item_id,
190 X_DEFAULT_WRAP_ID => l_default_wrap_id,
191 X_OBJECT_VERSION_NUMBER => l_object_version_number,
192 X_LAST_UPDATE_DATE => SYSDATE,
193 X_LAST_UPDATED_BY => l_user_id,
194 X_LAST_UPDATE_LOGIN => l_login_id);
195 EXCEPTION
196 when no_data_found then
197 INSERT_ROW(
198 l_row_id,
199 X_ACTION_ID => l_action_id,
200 X_ACTION_ITEM_ID => l_action_item_id,
201 X_DEFAULT_WRAP_ID => l_default_wrap_id,
202 X_OBJECT_VERSION_NUMBER => l_object_version_number,
203 X_CREATION_DATE => SYSDATE,
204 X_CREATED_BY => l_user_id,
205 X_LAST_UPDATE_DATE => sysdate,
206 X_LAST_UPDATED_BY => l_user_id,
207 X_LAST_UPDATE_LOGIN => l_login_id);
208 end LOAD_ROW;
209
210 procedure LOAD_SEED_ROW (
211 X_ACTION_ID in NUMBER,
212 X_ACTION_ITEM_ID in NUMBER,
213 X_DEFAULT_WRAP_ID in NUMBER,
214 X_OBJECT_VERSION_NUMBER in NUMBER,
215 X_OWNER IN VARCHAR2,
216 X_UPLOAD_MODE IN VARCHAR2
217 ) IS
218 BEGIN
219 JTF_IH_ACT_ACT_ITMS_SEED_PKG.LOAD_ROW (
220 X_ACTION_ID,
221 X_ACTION_ITEM_ID,
222 X_DEFAULT_WRAP_ID,
223 X_OBJECT_VERSION_NUMBER,
224 X_OWNER);
225 END LOAD_SEED_ROW;
226
227 end JTF_IH_ACT_ACT_ITMS_SEED_PKG;
[Home] [Help]