[Home] [Help]
PACKAGE BODY: APPS.AHL_SCHEDULE_MATERIALS_PKG
Source
1 package body AHL_SCHEDULE_MATERIALS_PKG as
2 /* $Header: AHLLSMTB.pls 120.0.12020000.2 2012/12/06 22:41:15 sareepar ship $ */
3 PROCEDURE INSERT_ROW (
4 X_SCHEDULED_MATERIAL_ID IN NUMBER,
5 X_OBJECT_VERSION_NUMBER IN NUMBER,
6 X_LAST_UPDATE_DATE IN DATE,
7 X_LAST_UPDATED_BY IN NUMBER,
8 X_CREATION_DATE IN DATE,
9 X_CREATED_BY IN NUMBER,
10 X_LAST_UPDATE_LOGIN IN NUMBER,
11 X_INVENTORY_ITEM_ID IN NUMBER,
12 X_SCHEDULE_DESIGNATOR IN VARCHAR2,
13 X_VISIT_ID IN NUMBER,
14 X_VISIT_START_DATE IN DATE,
15 X_VISIT_TASK_ID IN NUMBER,
16 X_ORGANIZATION_ID IN NUMBER,
17 X_SCHEDULED_DATE IN DATE,
18 X_REQUEST_ID IN NUMBER,
19 X_REQUESTED_DATE IN DATE,
20 X_SCHEDULED_QUANTITY IN NUMBER,
21 X_PROCESS_STATUS IN NUMBER,
22 X_ERROR_MESSAGE IN VARCHAR2,
23 X_TRANSACTION_ID IN NUMBER,
24 X_UOM IN VARCHAR2,
25 X_RT_OPER_MATERIAL_ID IN NUMBER,
26 X_OPERATION_CODE IN VARCHAR2,
27 X_OPERATION_SEQUENCE IN NUMBER,
28 X_ITEM_GROUP_ID IN NUMBER,
29 X_REQUESTED_QUANTITY IN NUMBER,
30 X_PROGRAM_ID IN NUMBER,
31 X_PROGRAM_UPDATE_DATE IN DATE,
32 X_LAST_UPDATED_DATE IN DATE,
33 X_WORKORDER_OPERATION_ID IN NUMBER,
34 X_POSITION_PATH_ID IN NUMBER,
35 X_RELATIONSHIP_ID IN NUMBER,
36 X_UNIT_EFFECTIVITY_ID IN NUMBER,
37 X_MR_ROUTE_ID IN NUMBER,
38 X_MATERIAL_REQUEST_TYPE IN VARCHAR2,
39 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
40 X_ATTRIBUTE1 IN VARCHAR2,
41 X_ATTRIBUTE2 IN VARCHAR2,
42 X_ATTRIBUTE3 IN VARCHAR2,
43 X_ATTRIBUTE4 IN VARCHAR2,
44 X_ATTRIBUTE5 IN VARCHAR2,
45 X_ATTRIBUTE6 IN VARCHAR2,
46 X_ATTRIBUTE7 IN VARCHAR2,
47 X_ATTRIBUTE8 IN VARCHAR2,
48 X_ATTRIBUTE9 IN VARCHAR2,
49 X_ATTRIBUTE10 IN VARCHAR2,
50 X_ATTRIBUTE11 IN VARCHAR2,
51 X_ATTRIBUTE12 IN VARCHAR2,
52 X_ATTRIBUTE13 IN VARCHAR2,
53 X_ATTRIBUTE14 IN VARCHAR2,
54 X_ATTRIBUTE15 IN VARCHAR2,
55 --sukhwsin: SB Effectivity Code changes - starts
56 X_MC_HEADER_ID IN NUMBER,
57 X_POSITION_KEY IN NUMBER
58 --sukhwsin: SB Effectivity Code changes - ends
59 ) IS
60
61 BEGIN
62
63 INSERT INTO AHL_SCHEDULE_MATERIALS(
64 SCHEDULED_MATERIAL_ID,
65 OBJECT_VERSION_NUMBER,
66 LAST_UPDATE_DATE,
67 LAST_UPDATED_BY,
68 CREATION_DATE,
69 CREATED_BY,
70 LAST_UPDATE_LOGIN,
71 INVENTORY_ITEM_ID,
72 SCHEDULE_DESIGNATOR,
73 VISIT_ID,
74 VISIT_START_DATE,
75 VISIT_TASK_ID,
76 ORGANIZATION_ID,
77 SCHEDULED_DATE,
78 REQUEST_ID,
79 REQUESTED_DATE,
80 SCHEDULED_QUANTITY,
81 PROCESS_STATUS,
82 ERROR_MESSAGE,
83 TRANSACTION_ID,
84 UOM,
85 RT_OPER_MATERIAL_ID,
86 OPERATION_CODE,
87 OPERATION_SEQUENCE,
88 ITEM_GROUP_ID,
89 REQUESTED_QUANTITY,
90 PROGRAM_ID,
91 PROGRAM_UPDATE_DATE,
92 LAST_UPDATED_DATE,
93 WORKORDER_OPERATION_ID,
94 POSITION_PATH_ID,
95 RELATIONSHIP_ID,
96 UNIT_EFFECTIVITY_ID,
97 MR_ROUTE_ID,
98 MATERIAL_REQUEST_TYPE,
99 ATTRIBUTE_CATEGORY,
100 ATTRIBUTE1,
101 ATTRIBUTE2,
102 ATTRIBUTE3,
103 ATTRIBUTE4,
104 ATTRIBUTE5,
105 ATTRIBUTE6,
106 ATTRIBUTE7,
107 ATTRIBUTE8,
108 ATTRIBUTE9,
109 ATTRIBUTE10,
110 ATTRIBUTE11,
111 ATTRIBUTE12,
112 ATTRIBUTE13,
113 ATTRIBUTE14,
114 ATTRIBUTE15,
115 --sukhwsin: SB Effectivity Code changes - starts
116 MC_HEADER_ID,
117 POSITION_KEY
118 --sukhwsin: SB Effectivity Code changes - ends
119 )
120 VALUES
121 (
122 AHL_SCHEDULE_MATERIALS_S.NEXTVAL,
123 X_OBJECT_VERSION_NUMBER,
124 X_LAST_UPDATE_DATE,
125 X_LAST_UPDATED_BY,
126 X_CREATION_DATE,
127 X_CREATED_BY,
128 X_LAST_UPDATE_LOGIN,
129 X_INVENTORY_ITEM_ID,
130 X_SCHEDULE_DESIGNATOR,
131 X_VISIT_ID,
132 X_VISIT_START_DATE,
133 X_VISIT_TASK_ID,
134 X_ORGANIZATION_ID,
135 X_SCHEDULED_DATE,
136 X_REQUEST_ID,
137 X_REQUESTED_DATE,
138 X_SCHEDULED_QUANTITY,
139 X_PROCESS_STATUS,
140 X_ERROR_MESSAGE,
141 X_TRANSACTION_ID,
142 X_UOM,
143 X_RT_OPER_MATERIAL_ID,
144 X_OPERATION_CODE,
145 X_OPERATION_SEQUENCE,
146 X_ITEM_GROUP_ID,
147 X_REQUESTED_QUANTITY,
148 X_PROGRAM_ID,
149 X_PROGRAM_UPDATE_DATE,
150 X_LAST_UPDATED_DATE,
151 X_WORKORDER_OPERATION_ID,
152 X_POSITION_PATH_ID,
153 X_RELATIONSHIP_ID,
154 X_UNIT_EFFECTIVITY_ID,
155 X_MR_ROUTE_ID,
156 X_MATERIAL_REQUEST_TYPE,
157 X_ATTRIBUTE_CATEGORY,
158 X_ATTRIBUTE1,
159 X_ATTRIBUTE2,
160 X_ATTRIBUTE3,
161 X_ATTRIBUTE4,
162 X_ATTRIBUTE5,
163 X_ATTRIBUTE6,
164 X_ATTRIBUTE7,
165 X_ATTRIBUTE8,
166 X_ATTRIBUTE9,
167 X_ATTRIBUTE10,
168 X_ATTRIBUTE11,
169 X_ATTRIBUTE12,
170 X_ATTRIBUTE13,
171 X_ATTRIBUTE14,
172 X_ATTRIBUTE15,
173 --sukhwsin: SB Effectivity Code changes - starts
174 X_MC_HEADER_ID,
175 X_POSITION_KEY
176 --sukhwsin: SB Effectivity Code changes - ends
177 );
178
179 END INSERT_ROW;
180
181 PROCEDURE UPDATE_ROW (
182 X_SCHEDULED_MATERIAL_ID IN NUMBER,
183 X_OBJECT_VERSION_NUMBER IN NUMBER,
184 X_LAST_UPDATE_DATE IN DATE,
185 X_LAST_UPDATED_BY IN NUMBER,
186 --X_CREATION_DATE IN DATE,
187 --X_CREATED_BY IN NUMBER,
188 X_LAST_UPDATE_LOGIN IN NUMBER,
189 X_INVENTORY_ITEM_ID IN NUMBER,
190 X_SCHEDULE_DESIGNATOR IN VARCHAR2,
191 X_VISIT_ID IN NUMBER,
192 X_VISIT_START_DATE IN DATE,
193 X_VISIT_TASK_ID IN NUMBER,
194 X_ORGANIZATION_ID IN NUMBER,
195 X_SCHEDULED_DATE IN DATE,
196 X_REQUEST_ID IN NUMBER,
197 X_REQUESTED_DATE IN DATE,
198 X_SCHEDULED_QUANTITY IN NUMBER,
199 X_PROCESS_STATUS IN NUMBER,
200 X_ERROR_MESSAGE IN VARCHAR2,
201 X_TRANSACTION_ID IN NUMBER,
202 X_UOM IN VARCHAR2,
203 X_RT_OPER_MATERIAL_ID IN NUMBER,
204 X_OPERATION_CODE IN VARCHAR2,
205 X_OPERATION_SEQUENCE IN NUMBER,
206 X_ITEM_GROUP_ID IN NUMBER,
207 X_REQUESTED_QUANTITY IN NUMBER,
208 X_PROGRAM_ID IN NUMBER,
209 X_PROGRAM_UPDATE_DATE IN DATE,
210 X_LAST_UPDATED_DATE IN DATE,
211 X_WORKORDER_OPERATION_ID IN NUMBER,
212 X_POSITION_PATH_ID IN NUMBER,
213 X_RELATIONSHIP_ID IN NUMBER,
214 X_UNIT_EFFECTIVITY_ID IN NUMBER,
215 X_MR_ROUTE_ID IN NUMBER,
216 X_MATERIAL_REQUEST_TYPE IN VARCHAR2,
217 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
218 X_ATTRIBUTE1 IN VARCHAR2,
219 X_ATTRIBUTE2 IN VARCHAR2,
220 X_ATTRIBUTE3 IN VARCHAR2,
221 X_ATTRIBUTE4 IN VARCHAR2,
222 X_ATTRIBUTE5 IN VARCHAR2,
223 X_ATTRIBUTE6 IN VARCHAR2,
224 X_ATTRIBUTE7 IN VARCHAR2,
225 X_ATTRIBUTE8 IN VARCHAR2,
226 X_ATTRIBUTE9 IN VARCHAR2,
227 X_ATTRIBUTE10 IN VARCHAR2,
228 X_ATTRIBUTE11 IN VARCHAR2,
229 X_ATTRIBUTE12 IN VARCHAR2,
230 X_ATTRIBUTE13 IN VARCHAR2,
231 X_ATTRIBUTE14 IN VARCHAR2,
232 X_ATTRIBUTE15 IN VARCHAR2,
233 --sukhwsin: SB Effectivity Code changes - starts
234 X_MC_HEADER_ID IN NUMBER,
235 X_POSITION_KEY IN NUMBER
236 --sukhwsin: SB Effectivity Code changes - ends
237 ) IS
238 BEGIN
239 UPDATE AHL_SCHEDULE_MATERIALS SET
240 SCHEDULED_MATERIAL_ID = X_SCHEDULED_MATERIAL_ID,
241 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER + 1,
242 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
243 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
244 --CREATION_DATE = X_CREATION_DATE,
245 --CREATED_BY = X_CREATED_BY,
246 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
247 INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID,
248 SCHEDULE_DESIGNATOR = X_SCHEDULE_DESIGNATOR,
249 VISIT_ID = X_VISIT_ID,
250 VISIT_START_DATE = X_VISIT_START_DATE,
251 VISIT_TASK_ID = X_VISIT_TASK_ID,
252 ORGANIZATION_ID = X_ORGANIZATION_ID,
253 SCHEDULED_DATE = X_SCHEDULED_DATE,
254 REQUEST_ID = X_REQUEST_ID,
255 REQUESTED_DATE = X_REQUESTED_DATE,
256 SCHEDULED_QUANTITY = X_SCHEDULED_QUANTITY,
257 PROCESS_STATUS = X_PROCESS_STATUS,
258 ERROR_MESSAGE = X_ERROR_MESSAGE,
259 TRANSACTION_ID = X_TRANSACTION_ID,
260 UOM = X_UOM,
261 RT_OPER_MATERIAL_ID = X_RT_OPER_MATERIAL_ID,
262 OPERATION_CODE = X_OPERATION_CODE,
263 OPERATION_SEQUENCE = X_OPERATION_SEQUENCE,
264 ITEM_GROUP_ID = X_ITEM_GROUP_ID,
265 REQUESTED_QUANTITY = X_REQUESTED_QUANTITY,
266 PROGRAM_ID = X_PROGRAM_ID,
267 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
268 LAST_UPDATED_DATE = X_LAST_UPDATED_DATE,
269 WORKORDER_OPERATION_ID = X_WORKORDER_OPERATION_ID,
270 POSITION_PATH_ID = X_POSITION_PATH_ID,
271 RELATIONSHIP_ID = X_RELATIONSHIP_ID,
272 UNIT_EFFECTIVITY_ID = X_UNIT_EFFECTIVITY_ID,
273 MR_ROUTE_ID = X_MR_ROUTE_ID,
274 MATERIAL_REQUEST_TYPE = X_MATERIAL_REQUEST_TYPE,
275 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
276 ATTRIBUTE1 = X_ATTRIBUTE1,
277 ATTRIBUTE2 = X_ATTRIBUTE2,
278 ATTRIBUTE3 = X_ATTRIBUTE3,
279 ATTRIBUTE4 = X_ATTRIBUTE4,
280 ATTRIBUTE5 = X_ATTRIBUTE5,
281 ATTRIBUTE6 = X_ATTRIBUTE6,
282 ATTRIBUTE7 = X_ATTRIBUTE7,
283 ATTRIBUTE8 = X_ATTRIBUTE8,
284 ATTRIBUTE9 = X_ATTRIBUTE9,
285 ATTRIBUTE10 = X_ATTRIBUTE10,
286 ATTRIBUTE11 = X_ATTRIBUTE11,
287 ATTRIBUTE12 = X_ATTRIBUTE12,
288 ATTRIBUTE13 = X_ATTRIBUTE13,
289 ATTRIBUTE14 = X_ATTRIBUTE14,
290 ATTRIBUTE15 = X_ATTRIBUTE15,
291 --sukhwsin: SB Effectivity Code changes - starts
292 MC_HEADER_ID = X_MC_HEADER_ID,
293 POSITION_KEY = X_POSITION_KEY
294 --sukhwsin: SB Effectivity Code changes - ends
295 WHERE SCHEDULED_MATERIAL_ID = X_SCHEDULED_MATERIAL_ID
296 AND OBJECT_VERSION_NUMBER=X_OBJECT_VERSION_NUMBER;
297 IF (SQL%ROWCOUNT=0)
298 THEN
299 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
300 FND_MSG_PUB.ADD;
301 END IF;
302 END UPDATE_ROW;
303
304 PROCEDURE DELETE_ROW (
305 X_SCHEDULED_MATERIAL_ID IN NUMBER
306 ) IS
307 BEGIN
308 DELETE AHL_SCHEDULE_MATERIALS
309 WHERE SCHEDULED_MATERIAL_ID = X_SCHEDULED_MATERIAL_ID;
310
311 IF (SQL%NOTFOUND) THEN
312 RAISE NO_DATA_FOUND;
313 END IF;
314
315 END DELETE_ROW;
316
317 END AHL_SCHEDULE_MATERIALS_PKG;