[Home] [Help]
PACKAGE BODY: APPS.AHL_OSP_ORDER_LINES_PKG
Source
1 PACKAGE BODY AHL_OSP_ORDER_LINES_PKG as
2 /* $Header: AHLLOSLB.pls 120.2 2008/01/30 22:20:13 jaramana ship $ */
3 PROCEDURE INSERT_ROW (
4 P_X_OSP_ORDER_LINE_ID IN OUT NOCOPY NUMBER,
5 P_OBJECT_VERSION_NUMBER IN NUMBER,
6 P_LAST_UPDATE_DATE IN DATE,
7 P_LAST_UPDATED_BY IN NUMBER,
8 P_CREATION_DATE IN DATE,
9 P_CREATED_BY IN NUMBER,
10 P_LAST_UPDATE_LOGIN IN NUMBER,
11 P_OSP_ORDER_ID IN NUMBER,
12 P_OSP_LINE_NUMBER IN NUMBER,
13 P_STATUS_CODE IN VARCHAR2,
14 P_PO_LINE_TYPE_ID IN NUMBER,
15 P_SERVICE_ITEM_ID IN NUMBER,
16 P_SERVICE_ITEM_DESCRIPTION IN VARCHAR2,
17 P_SERVICE_ITEM_UOM_CODE IN VARCHAR2,
18 P_NEED_BY_DATE IN DATE,
19 P_SHIP_BY_DATE IN DATE,
20 P_PO_LINE_ID IN NUMBER,
21 P_OE_SHIP_LINE_ID IN NUMBER,
22 P_OE_RETURN_LINE_ID IN NUMBER,
23 P_WORKORDER_ID IN NUMBER,
24 P_OPERATION_ID IN NUMBER,
25 P_QUANTITY IN NUMBER,
26 P_EXCHANGE_INSTANCE_ID IN NUMBER,
27 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
28 P_ATTRIBUTE1 IN VARCHAR2,
29 P_ATTRIBUTE2 IN VARCHAR2,
30 P_ATTRIBUTE3 IN VARCHAR2,
31 P_ATTRIBUTE4 IN VARCHAR2,
32 P_ATTRIBUTE5 IN VARCHAR2,
33 P_ATTRIBUTE6 IN VARCHAR2,
34 P_ATTRIBUTE7 IN VARCHAR2,
35 P_ATTRIBUTE8 IN VARCHAR2,
36 P_ATTRIBUTE9 IN VARCHAR2,
37 P_ATTRIBUTE10 IN VARCHAR2,
38 P_ATTRIBUTE11 IN VARCHAR2,
39 P_ATTRIBUTE12 IN VARCHAR2,
40 P_ATTRIBUTE13 IN VARCHAR2,
41 P_ATTRIBUTE14 IN VARCHAR2,
42 P_ATTRIBUTE15 IN VARCHAR2,
43 P_INVENTORY_ITEM_ID IN NUMBER,
44 P_INVENTORY_ORG_ID IN NUMBER,
45 P_SUB_INVENTORY IN VARCHAR2,
46 P_LOT_NUMBER IN VARCHAR2,
47 P_SERIAL_NUMBER IN VARCHAR2,
48 P_INVENTORY_ITEM_UOM IN VARCHAR2,
49 P_INVENTORY_ITEM_QUANTITY IN NUMBER,
50 P_PO_REQ_LINE_ID IN NUMBER -- Added by jaramana on January 14, 2008 for the Requisition ER 6034236
51 ) IS
52
53
54 BEGIN
55 insert into AHL_OSP_ORDER_LINES (
56 OSP_ORDER_LINE_ID,
57 OBJECT_VERSION_NUMBER,
58 LAST_UPDATE_DATE,
59 LAST_UPDATED_BY,
60 CREATION_DATE,
61 CREATED_BY,
62 LAST_UPDATE_LOGIN,
63 OSP_ORDER_ID,
64 OSP_LINE_NUMBER,
65 STATUS_CODE,
66 PO_LINE_TYPE_ID,
67 SERVICE_ITEM_ID,
68 SERVICE_ITEM_DESCRIPTION,
69 SERVICE_ITEM_UOM_CODE,
70 NEED_BY_DATE,
71 SHIP_BY_DATE,
72 PO_LINE_ID,
73 OE_SHIP_LINE_ID,
74 OE_RETURN_LINE_ID,
75 WORKORDER_ID,
76 OPERATION_ID,
77 QUANTITY,
78 EXCHANGE_INSTANCE_ID,
79 ATTRIBUTE_CATEGORY,
80 ATTRIBUTE1,
81 ATTRIBUTE2,
82 ATTRIBUTE3,
83 ATTRIBUTE4,
84 ATTRIBUTE5,
85 ATTRIBUTE6,
86 ATTRIBUTE7,
87 ATTRIBUTE8,
88 ATTRIBUTE9,
89 ATTRIBUTE10,
90 ATTRIBUTE11,
91 ATTRIBUTE12,
92 ATTRIBUTE13,
93 ATTRIBUTE14,
94 ATTRIBUTE15,
95 INVENTORY_ITEM_ID,
96 INVENTORY_ORG_ID,
97 SUB_INVENTORY,
98 LOT_NUMBER,
99 SERIAL_NUMBER,
100 INVENTORY_ITEM_UOM,
101 INVENTORY_ITEM_QUANTITY,
102 PO_REQ_LINE_ID -- Added by jaramana on January 14, 2008 for the Requisition ER 6034236
103 ) values (
104 P_X_OSP_ORDER_LINE_ID,
105 P_OBJECT_VERSION_NUMBER,
106 P_LAST_UPDATE_DATE,
107 P_LAST_UPDATED_BY,
108 P_CREATION_DATE,
109 P_CREATED_BY,
110 P_LAST_UPDATE_LOGIN,
111 P_OSP_ORDER_ID,
112 P_OSP_LINE_NUMBER,
113 P_STATUS_CODE,
114 P_PO_LINE_TYPE_ID,
115 P_SERVICE_ITEM_ID,
116 P_SERVICE_ITEM_DESCRIPTION,
117 P_SERVICE_ITEM_UOM_CODE,
118 P_NEED_BY_DATE,
119 P_SHIP_BY_DATE,
120 P_PO_LINE_ID,
121 P_OE_SHIP_LINE_ID,
122 P_OE_RETURN_LINE_ID,
123 P_WORKORDER_ID,
124 P_OPERATION_ID,
125 P_QUANTITY,
126 P_EXCHANGE_INSTANCE_ID,
127 P_ATTRIBUTE_CATEGORY,
128 P_ATTRIBUTE1,
129 P_ATTRIBUTE2,
130 P_ATTRIBUTE3,
131 P_ATTRIBUTE4,
132 P_ATTRIBUTE5,
133 P_ATTRIBUTE6,
134 P_ATTRIBUTE7,
135 P_ATTRIBUTE8,
136 P_ATTRIBUTE9,
137 P_ATTRIBUTE10,
138 P_ATTRIBUTE11,
139 P_ATTRIBUTE12,
140 P_ATTRIBUTE13,
141 P_ATTRIBUTE14,
142 P_ATTRIBUTE15,
143 P_INVENTORY_ITEM_ID,
144 P_INVENTORY_ORG_ID,
145 P_SUB_INVENTORY,
146 P_LOT_NUMBER,
147 P_SERIAL_NUMBER,
148 P_INVENTORY_ITEM_UOM,
149 P_INVENTORY_ITEM_QUANTITY,
150 P_PO_REQ_LINE_ID -- Added by jaramana on January 14, 2008 for the Requisition ER 6034236
151 ) RETURNING OSP_ORDER_LINE_ID INTO P_X_OSP_ORDER_LINE_ID;
152
153 EXCEPTION
154 WHEN OTHERS THEN
155 IF(SQLCODE = -1) THEN
156 FND_MESSAGE.Set_Name('AHL','AHL_OSP_DUP_ORDER_LINE');
157 FND_MESSAGE.Set_Token('OSP_ORDER_ID',P_OSP_ORDER_ID);
158 FND_MESSAGE.Set_Token('OSP_LINE_NUMBER',P_OSP_LINE_NUMBER);
159 FND_MSG_PUB.ADD;
160 ELSE
161 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
162 END IF;
163
164 END INSERT_ROW;
165
166
167 PROCEDURE UPDATE_ROW (
168 P_OSP_ORDER_LINE_ID IN NUMBER,
169 P_OBJECT_VERSION_NUMBER IN NUMBER,
170 P_LAST_UPDATE_DATE IN DATE,
171 P_LAST_UPDATED_BY IN NUMBER,
172 P_LAST_UPDATE_LOGIN IN NUMBER,
173 P_OSP_ORDER_ID IN NUMBER,
174 P_OSP_LINE_NUMBER IN NUMBER,
175 P_STATUS_CODE IN VARCHAR2,
176 P_PO_LINE_TYPE_ID IN NUMBER,
177 P_SERVICE_ITEM_ID IN NUMBER,
178 P_SERVICE_ITEM_DESCRIPTION IN VARCHAR2,
179 P_SERVICE_ITEM_UOM_CODE IN VARCHAR2,
180 P_NEED_BY_DATE IN DATE,
181 P_SHIP_BY_DATE IN DATE,
182 P_PO_LINE_ID IN NUMBER,
183 P_OE_SHIP_LINE_ID IN NUMBER,
184 P_OE_RETURN_LINE_ID IN NUMBER,
185 P_WORKORDER_ID IN NUMBER,
186 P_OPERATION_ID IN NUMBER,
187 P_QUANTITY IN NUMBER,
188 P_EXCHANGE_INSTANCE_ID IN NUMBER,
189 P_INVENTORY_ITEM_ID IN NUMBER,
190 P_INVENTORY_ORG_ID IN NUMBER,
191 P_INVENTORY_ITEM_UOM IN VARCHAR2,
192 P_INVENTORY_ITEM_QUANTITY IN NUMBER,
193 P_SUB_INVENTORY IN VARCHAR2,
194 P_LOT_NUMBER IN VARCHAR2,
195 P_SERIAL_NUMBER IN VARCHAR2,
196 P_PO_REQ_LINE_ID IN NUMBER, -- Added by jaramana on January 14, 2008 for the Requisition ER 6034236
197 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
198 P_ATTRIBUTE1 IN VARCHAR2,
199 P_ATTRIBUTE2 IN VARCHAR2,
200 P_ATTRIBUTE3 IN VARCHAR2,
201 P_ATTRIBUTE4 IN VARCHAR2,
202 P_ATTRIBUTE5 IN VARCHAR2,
203 P_ATTRIBUTE6 IN VARCHAR2,
204 P_ATTRIBUTE7 IN VARCHAR2,
205 P_ATTRIBUTE8 IN VARCHAR2,
206 P_ATTRIBUTE9 IN VARCHAR2,
207 P_ATTRIBUTE10 IN VARCHAR2,
208 P_ATTRIBUTE11 IN VARCHAR2,
209 P_ATTRIBUTE12 IN VARCHAR2,
210 P_ATTRIBUTE13 IN VARCHAR2,
211 P_ATTRIBUTE14 IN VARCHAR2,
212 P_ATTRIBUTE15 IN VARCHAR2
213 ) IS
214
215
216 BEGIN
217 update AHL_OSP_ORDER_LINES set
218 OSP_ORDER_LINE_ID = P_OSP_ORDER_LINE_ID,
219 OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER ,
220 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
221 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
222 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
223 OSP_ORDER_ID = P_OSP_ORDER_ID,
224 OSP_LINE_NUMBER = P_OSP_LINE_NUMBER,
225 STATUS_CODE = P_STATUS_CODE,
226 PO_LINE_TYPE_ID = P_PO_LINE_TYPE_ID,
227 SERVICE_ITEM_ID = P_SERVICE_ITEM_ID,
228 SERVICE_ITEM_DESCRIPTION = P_SERVICE_ITEM_DESCRIPTION,
229 SERVICE_ITEM_UOM_CODE = P_SERVICE_ITEM_UOM_CODE,
230 NEED_BY_DATE = P_NEED_BY_DATE,
231 SHIP_BY_DATE = P_SHIP_BY_DATE,
232 PO_LINE_ID = P_PO_LINE_ID,
233 OE_SHIP_LINE_ID = P_OE_SHIP_LINE_ID,
234 OE_RETURN_LINE_ID = P_OE_RETURN_LINE_ID,
235 WORKORDER_ID = P_WORKORDER_ID,
236 OPERATION_ID = P_OPERATION_ID,
237 QUANTITY = P_QUANTITY,
238 EXCHANGE_INSTANCE_ID = P_EXCHANGE_INSTANCE_ID,
239 INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID,
240 INVENTORY_ORG_ID = P_INVENTORY_ORG_ID,
241 INVENTORY_ITEM_UOM = P_INVENTORY_ITEM_UOM,
242 INVENTORY_ITEM_QUANTITY = P_INVENTORY_ITEM_QUANTITY,
243 SUB_INVENTORY = P_SUB_INVENTORY,
244 LOT_NUMBER = P_LOT_NUMBER,
245 SERIAL_NUMBER = P_SERIAL_NUMBER,
246 PO_REQ_LINE_ID = P_PO_REQ_LINE_ID, -- Added by jaramana on January 14, 2008 for the Requisition ER 6034236
247 ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY,
248 ATTRIBUTE1 = P_ATTRIBUTE1,
249 ATTRIBUTE2 = P_ATTRIBUTE2,
250 ATTRIBUTE3 = P_ATTRIBUTE3,
251 ATTRIBUTE4 = P_ATTRIBUTE4,
252 ATTRIBUTE5 = P_ATTRIBUTE5,
253 ATTRIBUTE6 = P_ATTRIBUTE6,
254 ATTRIBUTE7 = P_ATTRIBUTE7,
255 ATTRIBUTE8 = P_ATTRIBUTE8,
256 ATTRIBUTE9 = P_ATTRIBUTE9,
257 ATTRIBUTE10 = P_ATTRIBUTE10,
258 ATTRIBUTE11 = P_ATTRIBUTE11,
259 ATTRIBUTE12 = P_ATTRIBUTE12,
260 ATTRIBUTE13 = P_ATTRIBUTE13,
261 ATTRIBUTE14 = P_ATTRIBUTE14,
262 ATTRIBUTE15 = P_ATTRIBUTE15
263 where OSP_ORDER_LINE_ID = P_OSP_ORDER_LINE_ID
264 and OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER - 1;
265
266 IF (SQL%NOTFOUND) then
267 RAISE no_data_found;
268 END IF;
269
270 EXCEPTION
271 WHEN OTHERS THEN
272 IF(SQLCODE = -1) THEN
273 FND_MESSAGE.Set_Name('AHL','AHL_OSP_DUP_ORDER_LINE');
274 FND_MESSAGE.Set_Token('OSP_ORDER_ID',P_OSP_ORDER_ID);
275 FND_MESSAGE.Set_Token('OSP_LINE_NUMBER',P_OSP_LINE_NUMBER);
276 FND_MSG_PUB.ADD;
277 ELSE
278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
279 END IF;
280
281 END UPDATE_ROW;
282
283 PROCEDURE DELETE_ROW (
284 P_OSP_ORDER_LINE_ID in NUMBER
285 ) is
286
287 BEGIN
288
289 delete from AHL_OSP_ORDER_LINES
290 where OSP_ORDER_LINE_ID = P_OSP_ORDER_LINE_ID;
291
292 IF (SQL%NOTFOUND) then
293 RAISE no_data_found;
294 END IF;
295
296 END DELETE_ROW;
297
298 END AHL_OSP_ORDER_LINES_PKG; -- Package spec
299