[Home] [Help]
PACKAGE BODY: APPS.WMS_DEPT_SUBS_PKG
Source
1 PACKAGE BODY WMS_DEPT_SUBS_PKG AS
2 /* $Header: WMSDPZNB.pls 120.1 2005/06/20 06:10:17 appldev ship $ */
3 --
4 PROCEDURE insert_row
5 (X_Rowid IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
6 X_Organization_Id NUMBER,
7 X_subinventory_code VARCHAR2 ,
8 X_department_Id NUMBER,
9 X_Effective_From_date DATE DEFAULT NULL,
10 X_Effective_To_date DATE DEFAULT NULL,
11 X_Created_By NUMBER,
12 X_Creation_Date DATE,
13 X_Last_Updated_By NUMBER,
14 X_Last_Update_Date DATE,
15 X_Last_Update_Login NUMBER DEFAULT NULL,
16 X_Attribute1 VARCHAR2 DEFAULT NULL,
17 X_Attribute2 VARCHAR2 DEFAULT NULL,
18 X_Attribute3 VARCHAR2 DEFAULT NULL,
19 X_Attribute4 VARCHAR2 DEFAULT NULL,
20 X_Attribute5 VARCHAR2 DEFAULT NULL,
21 X_Attribute6 VARCHAR2 DEFAULT NULL,
22 X_Attribute7 VARCHAR2 DEFAULT NULL,
23 X_Attribute8 VARCHAR2 DEFAULT NULL,
24 X_Attribute9 VARCHAR2 DEFAULT NULL,
25 X_Attribute10 VARCHAR2 DEFAULT NULL,
26 X_Attribute11 VARCHAR2 DEFAULT NULL,
27 X_Attribute12 VARCHAR2 DEFAULT NULL,
28 X_Attribute13 VARCHAR2 DEFAULT NULL,
29 X_Attribute14 VARCHAR2 DEFAULT NULL,
30 X_Attribute15 VARCHAR2 DEFAULT NULL,
31 X_Attribute_Category VARCHAR2 DEFAULT NULL
32 ) IS
33 CURSOR C IS
34 SELECT rowid FROM WMS_DEPARTMENT_SUBINVENTORIES
35 WHERE organization_id = X_Organization_Id
36 AND subinventory_code = X_subinventory_code
37 AND department_id = X_department_Id
38 AND NVL(effective_from_date,TO_DATE('01011900','DDMMYYYY'))
39 = NVL(X_Effective_From_date,TO_DATE('01011900','DDMMYYYY'))
40 AND NVL(effective_to_date, TO_DATE('31124000','DDMMYYYY'))
41 = NVL(X_Effective_To_date, TO_DATE('31124000','DDMMYYYY'));
42
43 BEGIN
44
45 INSERT INTO WMS_DEPARTMENT_SUBINVENTORIES(
46 Organization_Id,
47 subinventory_Code,
48 department_Id,
49 Effective_From_date,
50 Effective_To_date,
51 created_by,
52 creation_date,
53 last_updated_by,
54 last_update_date,
55 last_update_login,
56 attribute1,
57 attribute2,
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 attribute_category
72 ) VALUES (
73 X_Organization_Id,
74 X_subinventory_Code,
75 X_department_Id,
76 X_Effective_From_date,
77 X_Effective_To_date,
78 X_Created_By,
79 X_Creation_Date,
80 X_Last_Updated_By,
81 X_Last_Update_Date,
82 X_Last_Update_Login,
83 X_Attribute1,
84 X_Attribute2,
85 X_Attribute3,
86 X_Attribute4,
87 X_Attribute5,
88 X_Attribute6,
89 X_Attribute7,
90 X_Attribute8,
91 X_Attribute9,
92 X_Attribute10,
93 X_Attribute11,
94 X_Attribute12,
95 X_Attribute13,
96 X_Attribute14,
97 X_Attribute15,
98 X_Attribute_Category
99 );
100
101 OPEN C;
102 FETCH C INTO X_Rowid;
103 if (C%NOTFOUND) then
104 CLOSE C;
105 Raise NO_DATA_FOUND;
106 end if;
107 CLOSE C;
108 END Insert_Row;
109
110 PROCEDURE lock_row
111 (X_Rowid IN VARCHAR2,
112 X_Organization_Id NUMBER,
113 X_subinventory_code VARCHAR2 ,
114 X_department_Id NUMBER,
115 X_Effective_From_date DATE DEFAULT NULL,
116 X_Effective_To_date DATE DEFAULT NULL,
117 X_Created_By NUMBER,
118 X_Creation_Date DATE,
119 X_Last_Updated_By NUMBER,
120 X_Last_Update_Date DATE,
121 X_Last_Update_Login NUMBER DEFAULT NULL,
122 X_Attribute1 VARCHAR2 DEFAULT NULL,
123 X_Attribute2 VARCHAR2 DEFAULT NULL,
124 X_Attribute3 VARCHAR2 DEFAULT NULL,
125 X_Attribute4 VARCHAR2 DEFAULT NULL,
126 X_Attribute5 VARCHAR2 DEFAULT NULL,
127 X_Attribute6 VARCHAR2 DEFAULT NULL,
128 X_Attribute7 VARCHAR2 DEFAULT NULL,
129 X_Attribute8 VARCHAR2 DEFAULT NULL,
130 X_Attribute9 VARCHAR2 DEFAULT NULL,
131 X_Attribute10 VARCHAR2 DEFAULT NULL,
132 X_Attribute11 VARCHAR2 DEFAULT NULL,
133 X_Attribute12 VARCHAR2 DEFAULT NULL,
134 X_Attribute13 VARCHAR2 DEFAULT NULL,
135 X_Attribute14 VARCHAR2 DEFAULT NULL,
136 X_Attribute15 VARCHAR2 DEFAULT NULL,
137 X_Attribute_Category VARCHAR2 DEFAULT NULL
138 ) IS
139 CURSOR C IS SELECT *
140 FROM WMS_DEPARTMENT_SUBINVENTORIES
141 WHERE rowid = X_Rowid
142 FOR UPDATE of department_Id NOWAIT;
143 Recinfo C%ROWTYPE;
144
145 BEGIN
146 OPEN C;
147 FETCH C INTO Recinfo;
148 if (C%NOTFOUND) then
149 CLOSE C;
150 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
151 APP_EXCEPTION.Raise_Exception;
152 end if;
153 CLOSE C;
154 if ( (Recinfo.organization_id = X_Organization_Id)
155 AND (Recinfo.subinventory_code = X_subinventory_Code)
156 AND (Recinfo.department_id = X_department_Id)
157 AND ( (Recinfo.effective_from_date = X_Effective_From_date)
158 OR ( (Recinfo.effective_from_date IS NULL)
159 AND (X_Effective_From_date IS NULL)))
160 AND ( (Recinfo.effective_to_date = X_Effective_To_date)
161 OR ( (Recinfo.effective_to_date IS NULL)
162 AND (X_Effective_To_date IS NULL)))
163 AND ( (Recinfo.attribute1 = X_Attribute1)
164 OR ( (Recinfo.attribute1 IS NULL)
165 AND (X_Attribute1 IS NULL)))
166 AND ( (Recinfo.attribute2 = X_Attribute2)
167 OR ( (Recinfo.attribute2 IS NULL)
168 AND (X_Attribute2 IS NULL)))
169 AND ( (Recinfo.attribute3 = X_Attribute3)
170 OR ( (Recinfo.attribute3 IS NULL)
171 AND (X_Attribute3 IS NULL)))
172 AND ( (Recinfo.attribute4 = X_Attribute4)
173 OR ( (Recinfo.attribute4 IS NULL)
174 AND (X_Attribute4 IS NULL)))
175 AND ( (Recinfo.attribute5 = X_Attribute5)
176 OR ( (Recinfo.attribute5 IS NULL)
177 AND (X_Attribute5 IS NULL)))
178 AND ( (Recinfo.attribute6 = X_Attribute6)
179 OR ( (Recinfo.attribute6 IS NULL)
180 AND (X_Attribute6 IS NULL)))
181 AND ( (Recinfo.attribute7 = X_Attribute7)
182 OR ( (Recinfo.attribute7 IS NULL)
183 AND (X_Attribute7 IS NULL)))
184 AND ( (Recinfo.attribute8 = X_Attribute8)
185 OR ( (Recinfo.attribute8 IS NULL)
186 AND (X_Attribute8 IS NULL)))
187 AND ( (Recinfo.attribute9 = X_Attribute9)
188 OR ( (Recinfo.attribute9 IS NULL)
189 AND (X_Attribute9 IS NULL)))
190 AND ( (Recinfo.attribute10 = X_Attribute10)
191 OR ( (Recinfo.attribute10 IS NULL)
192 AND (X_Attribute10 IS NULL)))
193 AND ( (Recinfo.attribute11 = X_Attribute11)
194 OR ( (Recinfo.attribute11 IS NULL)
195 AND (X_Attribute11 IS NULL)))
196 AND ( (Recinfo.attribute12 = X_Attribute12)
197 OR ( (Recinfo.attribute12 IS NULL)
198 AND (X_Attribute12 IS NULL)))
199 AND ( (Recinfo.attribute13 = X_Attribute13)
200 OR ( (Recinfo.attribute13 IS NULL)
201 AND (X_Attribute13 IS NULL)))
202 AND ( (Recinfo.attribute14 = X_Attribute14)
203 OR ( (Recinfo.attribute14 IS NULL)
204 AND (X_Attribute14 IS NULL)))
205 AND ( (Recinfo.attribute15 = X_Attribute15)
206 OR ( (Recinfo.attribute15 IS NULL)
207 AND (X_Attribute15 IS NULL)))
208 AND ( (Recinfo.attribute_category = X_Attribute_Category)
209 OR ( (Recinfo.attribute_category IS NULL)
210 AND (X_Attribute_Category IS NULL)))
211 ) then
212 return;
213 else
214 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
215 APP_EXCEPTION.Raise_Exception;
216 end if;
217 END Lock_Row;
218
219 PROCEDURE update_row
220 (X_Rowid IN VARCHAR2,
221 X_Organization_Id NUMBER,
222 X_subinventory_code VARCHAR2 ,
223 X_department_Id NUMBER,
224 X_Effective_From_date DATE DEFAULT NULL,
225 X_Effective_To_date DATE DEFAULT NULL,
226 X_Created_By NUMBER,
227 X_Creation_Date DATE,
228 X_Last_Updated_By NUMBER,
229 X_Last_Update_Date DATE,
230 X_Last_Update_Login NUMBER DEFAULT NULL,
231 X_Attribute1 VARCHAR2 DEFAULT NULL,
232 X_Attribute2 VARCHAR2 DEFAULT NULL,
233 X_Attribute3 VARCHAR2 DEFAULT NULL,
234 X_Attribute4 VARCHAR2 DEFAULT NULL,
235 X_Attribute5 VARCHAR2 DEFAULT NULL,
236 X_Attribute6 VARCHAR2 DEFAULT NULL,
237 X_Attribute7 VARCHAR2 DEFAULT NULL,
238 X_Attribute8 VARCHAR2 DEFAULT NULL,
239 X_Attribute9 VARCHAR2 DEFAULT NULL,
240 X_Attribute10 VARCHAR2 DEFAULT NULL,
241 X_Attribute11 VARCHAR2 DEFAULT NULL,
242 X_Attribute12 VARCHAR2 DEFAULT NULL,
243 X_Attribute13 VARCHAR2 DEFAULT NULL,
244 X_Attribute14 VARCHAR2 DEFAULT NULL,
245 X_Attribute15 VARCHAR2 DEFAULT NULL,
246 X_Attribute_Category VARCHAR2 DEFAULT NULL
247 ) IS
248 BEGIN
249 UPDATE WMS_DEPARTMENT_SUBINVENTORIES
250 SET
251 organization_id = X_Organization_Id,
252 subinventory_code = X_subinventory_Code,
253 department_id = X_department_Id,
254 effective_from_date = X_Effective_From_date,
255 effective_to_date = X_Effective_To_date,
256 last_updated_by = X_Last_Updated_By,
257 last_update_date = X_Last_Update_Date,
258 last_update_login = X_Last_Update_Login,
259 attribute1 = X_Attribute1,
260 attribute2 = X_Attribute2,
261 attribute3 = X_Attribute3,
262 attribute4 = X_Attribute4,
263 attribute5 = X_Attribute5,
264 attribute6 = X_Attribute6,
265 attribute7 = X_Attribute7,
266 attribute8 = X_Attribute8,
267 attribute9 = X_Attribute9,
268 attribute10 = X_Attribute10,
269 attribute11 = X_Attribute11,
270 attribute12 = X_Attribute12,
271 attribute13 = X_Attribute13,
272 attribute14 = X_Attribute14,
273 attribute15 = X_Attribute15,
274 attribute_category = X_Attribute_Category
275 WHERE rowid = X_Rowid;
276
277 if (SQL%NOTFOUND) then
278 Raise NO_DATA_FOUND;
279 end if;
280 END Update_Row;
281
282 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
283 BEGIN
284 DELETE FROM WMS_DEPARTMENT_SUBINVENTORIES
285 WHERE rowid = X_Rowid;
286
287 if (SQL%NOTFOUND) then
288 Raise NO_DATA_FOUND;
289 end if;
290 END Delete_Row;
291 END WMS_DEPT_SUBS_PKG;