[Home] [Help]
PACKAGE BODY: APPS.JTF_IH_MEDIA_LC_TY_SEED_PKG
Source
1 PACKAGE BODY jtf_ih_media_lc_ty_seed_pkg AS
2 /* $Header: JTFIHMTB.pls 120.2 2005/07/08 07:55:37 nchouras ship $ */
3 PROCEDURE insert_row(
4 x_rowid IN OUT NOCOPY VARCHAR2
5 , x_milcs_type_id NUMBER
6 , x_object_version_number NUMBER
7 , x_created_by NUMBER
8 , x_creation_date DATE
9 , x_last_updated_by NUMBER
10 , x_last_update_date DATE
11 , x_last_update_login NUMBER
12 , x_milcs_code VARCHAR2
13 , x_short_description VARCHAR2
14 ) IS
15 CURSOR l_insert IS
16 SELECT ROWID
17 FROM jtf_ih_media_itm_lc_seg_tys
18 WHERE milcs_type_id = x_milcs_type_id;
19 BEGIN
20 INSERT INTO jtf_ih_media_itm_lc_seg_tys (
21 milcs_type_id
22 , object_version_number
23 , created_by
24 , creation_date
25 , last_updated_by
26 , last_update_date
27 , last_update_login
28 , milcs_code
29 , short_description
30 ) VALUES (
31 x_milcs_type_id
32 , DECODE(x_object_version_number,FND_API.G_MISS_NUM,NULL,x_object_version_number)
33 , DECODE(x_created_by,FND_API.G_MISS_NUM,NULL,x_created_by)
34 , DECODE(x_creation_date,FND_API.G_MISS_DATE,NULL,x_creation_date)
35 , DECODE(x_last_updated_by,FND_API.G_MISS_NUM,NULL,x_last_updated_by)
36 , DECODE(x_last_update_date,FND_API.G_MISS_DATE,NULL,x_last_update_date)
37 , DECODE(x_last_update_login,FND_API.G_MISS_NUM,NULL,x_last_update_login)
38 , DECODE(x_milcs_code,FND_API.G_MISS_CHAR,NULL,x_milcs_code)
39 , DECODE(x_short_description,FND_API.G_MISS_CHAR,NULL,x_short_description)
40 );
41
42 OPEN l_insert;
43 FETCH l_insert INTO x_rowid;
44 IF (l_insert%NOTFOUND) THEN
45 CLOSE l_insert;
46 RAISE NO_DATA_FOUND;
47 END IF;
48 END insert_row;
49
50 PROCEDURE delete_row(
51 x_milcs_type_id NUMBER
52 ) IS
53 BEGIN
54 DELETE FROM jtf_ih_media_itm_lc_seg_tys
55 WHERE milcs_type_id = x_milcs_type_id;
56 IF (SQL%NOTFOUND) THEN
57 RAISE NO_DATA_FOUND;
58 END IF;
59 END delete_row;
60
61 PROCEDURE update_row(
62 x_milcs_type_id NUMBER
63 , x_object_version_number NUMBER
64 , x_last_updated_by NUMBER
65 , x_last_update_date DATE
66 , x_last_update_login NUMBER
67 , x_milcs_code VARCHAR2
68 , x_short_description VARCHAR2
69 ) IS
70 BEGIN
71 UPDATE jtf_ih_media_itm_lc_seg_tys
72 SET
73 milcs_type_id=DECODE(x_milcs_type_id,FND_API.G_MISS_NUM,NULL,x_milcs_type_id)
74 , object_version_number=DECODE(x_object_version_number,FND_API.G_MISS_NUM,NULL,x_object_version_number)
75 , last_updated_by=DECODE(x_last_updated_by,FND_API.G_MISS_NUM,NULL,x_last_updated_by)
76 , last_update_date=DECODE(x_last_update_date,FND_API.G_MISS_DATE,NULL,x_last_update_date)
77 , last_update_login=DECODE(x_last_update_login,FND_API.G_MISS_NUM,NULL,x_last_update_login)
78 , milcs_code=DECODE(x_milcs_code,FND_API.G_MISS_CHAR,NULL,x_milcs_code)
79 , short_description=DECODE(x_short_description,FND_API.G_MISS_CHAR,NULL,x_short_description)
80 WHERE milcs_type_id = x_milcs_type_id;
81 IF (SQL%NOTFOUND) THEN
82 RAISE NO_DATA_FOUND;
83 END IF;
84 END update_row;
85
86 PROCEDURE lock_row(
87 x_rowid VARCHAR2
88 , x_milcs_type_id NUMBER
89 , x_object_version_number NUMBER
90 , x_created_by NUMBER
91 , x_creation_date DATE
92 , x_last_updated_by NUMBER
93 , x_last_update_date DATE
94 , x_last_update_login NUMBER
95 , x_milcs_code VARCHAR2
96 , x_short_description VARCHAR2
97 ) IS
98 CURSOR l_lock IS
99 SELECT *
100 FROM jtf_ih_media_itm_lc_seg_tys
101 WHERE rowid = x_rowid
102 FOR UPDATE OF milcs_type_id NOWAIT;
103 l_table_rec l_lock%ROWTYPE;
104 BEGIN
105 OPEN l_lock;
106 FETCH l_lock INTO l_table_rec;
107 IF (l_lock%NOTFOUND) THEN
108 CLOSE l_lock;
109 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
110 APP_EXCEPTION.RAISE_EXCEPTION;
111 END IF;
112 CLOSE l_lock;
113 IF (
114 ((l_table_rec.milcs_type_id = x_milcs_type_id)
115 OR ((l_table_rec.milcs_type_id IS NULL)
116 AND ( x_milcs_type_id IS NULL)))
117 AND ((l_table_rec.object_version_number = x_object_version_number)
118 OR ((l_table_rec.object_version_number IS NULL)
119 AND ( x_object_version_number IS NULL)))
120 AND ((l_table_rec.created_by = x_created_by)
121 OR ((l_table_rec.created_by IS NULL)
122 AND ( x_created_by IS NULL)))
123 AND ((l_table_rec.creation_date = x_creation_date)
124 OR ((l_table_rec.creation_date IS NULL)
125 AND ( x_creation_date IS NULL)))
126 AND ((l_table_rec.last_updated_by = x_last_updated_by)
127 OR ((l_table_rec.last_updated_by IS NULL)
128 AND ( x_last_updated_by IS NULL)))
129 AND ((l_table_rec.last_update_date = x_last_update_date)
130 OR ((l_table_rec.last_update_date IS NULL)
131 AND ( x_last_update_date IS NULL)))
132 AND ((l_table_rec.last_update_login = x_last_update_login)
133 OR ((l_table_rec.last_update_login IS NULL)
134 AND ( x_last_update_login IS NULL)))
135 AND ((l_table_rec.milcs_code = x_milcs_code)
136 OR ((l_table_rec.milcs_code IS NULL)
137 AND ( x_milcs_code IS NULL)))
138 AND ((l_table_rec.short_description = x_short_description)
139 OR ((l_table_rec.short_description IS NULL)
140 AND ( x_short_description IS NULL)))
141 ) THEN
142 RETURN;
143 ELSE
144 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
145 APP_EXCEPTION.RAISE_EXCEPTION;
146 END IF;
147 END lock_row;
148
149 procedure LOAD_ROW (
150 X_MILCS_TYPE_ID in NUMBER,
151 X_MILCS_CODE in VARCHAR2,
152 X_OBJECT_VERSION_NUMBER in NUMBER,
153 X_SHORT_DESCRIPTION in VARCHAR2,
154 X_OWNER IN VARCHAR2
155 ) IS
156 begin
157 declare
158 user_id NUMBER := 0;
159 row_id VARCHAR2(64);
160 l_api_version NUMBER := 1.0;
161 l_return_status VARCHAR2(1);
162 l_msg_count NUMBER;
163 l_msg_data VARCHAR2(100);
164 l_init_msg_list VARCHAR2(1) := 'F';
165 l_commit VARCHAR2(1) := 'F';
166 l_validation_level NUMBER := 100;
167 l_milcs_type_id NUMBER;
168 l_object_version_number NUMBER;
169 l_milcs_code VARCHAR2(80);
170 l_short_description VARCHAR2(240);
171 l_last_update_date DATE;
172 l_last_updated_by NUMBER;
173 l_last_update_login NUMBER;
174 l_creation_date DATE;
175 l_created_by NUMBER;
176
177 begin
178 --if (x_owner = 'SEED') then
179 -- user_id := 1;
180 --end if;
181 user_id := fnd_load_util.owner_id(x_owner);
182 l_milcs_type_id := X_MILCS_TYPE_ID;
183 l_object_version_number := 1;
184 l_milcs_code := X_MILCS_CODE;
185 l_short_description := X_SHORT_DESCRIPTION;
186 l_last_update_date := sysdate;
187 l_last_updated_by := user_id;
188 l_last_update_login := 0;
189
190
191 UPDATE_ROW(
192 X_MILCS_TYPE_ID => l_milcs_type_id,
193 X_OBJECT_VERSION_NUMBER => l_object_version_number,
194 X_MILCS_CODE => l_milcs_code,
195 X_SHORT_DESCRIPTION => l_short_description,
196 X_LAST_UPDATE_DATE => l_last_update_date,
197 X_LAST_UPDATED_BY => l_last_updated_by,
198 X_LAST_UPDATE_LOGIN => l_last_update_login);
199 EXCEPTION
200 when no_data_found then
201 l_creation_date := sysdate;
202 l_created_by := user_id;
203 INSERT_ROW(
204 row_id,
205 X_MILCS_TYPE_ID => l_milcs_type_id,
206 X_OBJECT_VERSION_NUMBER => l_object_version_number,
207 X_MILCS_CODE => l_milcs_code,
208 X_SHORT_DESCRIPTION => l_short_description,
209 X_CREATION_DATE => l_creation_date,
210 X_CREATED_BY => l_created_by,
211 X_LAST_UPDATE_DATE => l_last_update_date,
212 X_LAST_UPDATED_BY => l_last_updated_by,
213 X_LAST_UPDATE_LOGIN => l_last_update_login);
214 end;
215 end LOAD_ROW;
216
217
218 procedure LOAD_SEED_ROW (
219 X_MILCS_TYPE_ID in NUMBER,
220 X_MILCS_CODE in VARCHAR2,
221 X_OBJECT_VERSION_NUMBER in NUMBER,
222 X_SHORT_DESCRIPTION in VARCHAR2,
223 X_OWNER IN VARCHAR2,
224 X_UPLOAD_MODE in VARCHAR2
225 ) IS
226 begin
227 if (X_UPLOAD_MODE = 'NLS') then
228 null;
229 else
230 jtf_ih_media_lc_ty_seed_pkg.LOAD_ROW (
231 X_MILCS_TYPE_ID,
232 X_MILCS_CODE,
233 X_OBJECT_VERSION_NUMBER,
234 X_SHORT_DESCRIPTION,
235 X_OWNER);
236 end if;
237
238 end LOAD_SEED_ROW;
239
240 END jtf_ih_media_lc_ty_seed_pkg;