[Home] [Help]
PACKAGE BODY: APPS.CSD_REPAIRS_PKG
Source
1 PACKAGE BODY CSD_REPAIRS_PKG as
2 /* $Header: csdtdrab.pls 120.10.12010000.2 2008/11/06 07:14:57 subhat ship $ */
3 -- Start of Comments
4 -- Package name : CSD_REPAIRS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSD_REPAIRS_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdtdrab.pls';
13 l_debug NUMBER ;
14
15 PROCEDURE Insert_Row(
16 px_REPAIR_LINE_ID IN OUT NOCOPY NUMBER
17 ,p_REQUEST_ID NUMBER
18 ,p_PROGRAM_ID NUMBER
19 ,p_PROGRAM_APPLICATION_ID NUMBER
20 ,p_PROGRAM_UPDATE_DATE DATE
21 ,p_CREATED_BY NUMBER
22 ,p_CREATION_DATE DATE
23 ,p_LAST_UPDATED_BY NUMBER
24 ,p_LAST_UPDATE_DATE DATE
25 ,p_LAST_UPDATE_LOGIN NUMBER
26 ,p_REPAIR_NUMBER VARCHAR2
27 ,p_INCIDENT_ID NUMBER
28 ,p_INVENTORY_ITEM_ID NUMBER
29 ,p_CUSTOMER_PRODUCT_ID NUMBER
30 ,p_UNIT_OF_MEASURE VARCHAR2
31 ,p_REPAIR_TYPE_ID NUMBER
32 -- RESOURCE_GROUP Added by Vijay 10/28/2004
33 ,p_RESOURCE_GROUP NUMBER
34 ,p_RESOURCE_ID NUMBER
35 ,p_INSTANCE_ID NUMBER
36 ,p_PROJECT_ID NUMBER
37 ,p_TASK_ID NUMBER
38 ,p_UNIT_NUMBER VARCHAR2 -- rfieldma, project integration
39 ,p_CONTRACT_LINE_ID NUMBER
40 ,p_QUANTITY NUMBER
41 ,p_STATUS VARCHAR2
42 ,p_APPROVAL_REQUIRED_FLAG VARCHAR2
43 ,p_DATE_CLOSED DATE
44 ,p_QUANTITY_IN_WIP NUMBER
45 ,p_APPROVAL_STATUS VARCHAR2
46 ,p_QUANTITY_RCVD NUMBER
47 ,p_QUANTITY_SHIPPED NUMBER
48 ,p_CURRENCY_CODE VARCHAR2
49 ,p_DEFAULT_PO_NUM VARCHAR2 := NULL
50 ,p_SERIAL_NUMBER VARCHAR2
51 ,p_PROMISE_DATE DATE
52 ,p_ATTRIBUTE_CATEGORY VARCHAR2
53 ,p_ATTRIBUTE1 VARCHAR2
54 ,p_ATTRIBUTE2 VARCHAR2
55 ,p_ATTRIBUTE3 VARCHAR2
56 ,p_ATTRIBUTE4 VARCHAR2
57 ,p_ATTRIBUTE5 VARCHAR2
58 ,p_ATTRIBUTE6 VARCHAR2
59 ,p_ATTRIBUTE7 VARCHAR2
60 ,p_ATTRIBUTE8 VARCHAR2
61 ,p_ATTRIBUTE9 VARCHAR2
62 ,p_ATTRIBUTE10 VARCHAR2
63 ,p_ATTRIBUTE11 VARCHAR2
64 ,p_ATTRIBUTE12 VARCHAR2
65 ,p_ATTRIBUTE13 VARCHAR2
66 ,p_ATTRIBUTE14 VARCHAR2
67 ,p_ATTRIBUTE15 VARCHAR2
68 --bug#7497907, 12.1 FP, subhat
69 ,p_ATTRIBUTE16 VARCHAR2
70 ,p_ATTRIBUTE17 VARCHAR2
71 ,p_ATTRIBUTE18 VARCHAR2
72 ,p_ATTRIBUTE19 VARCHAR2
73 ,p_ATTRIBUTE20 VARCHAR2
74 ,p_ATTRIBUTE21 VARCHAR2
75 ,p_ATTRIBUTE22 VARCHAR2
76 ,p_ATTRIBUTE23 VARCHAR2
77 ,p_ATTRIBUTE24 VARCHAR2
78 ,p_ATTRIBUTE25 VARCHAR2
79 ,p_ATTRIBUTE26 VARCHAR2
80 ,p_ATTRIBUTE27 VARCHAR2
81 ,p_ATTRIBUTE28 VARCHAR2
82 ,p_ATTRIBUTE29 VARCHAR2
83 ,p_ATTRIBUTE30 VARCHAR2
84 ,p_ORDER_LINE_ID NUMBER
85 ,p_ORIGINAL_SOURCE_REFERENCE VARCHAR2
86 ,p_STATUS_REASON_CODE VARCHAR2
87 ,p_OBJECT_VERSION_NUMBER NUMBER
88 ,p_AUTO_PROCESS_RMA VARCHAR2
89 ,p_REPAIR_MODE VARCHAR2
90 ,p_ITEM_REVISION VARCHAR2
91 ,p_REPAIR_GROUP_ID NUMBER
92 ,p_RO_TXN_STATUS VARCHAR2
93 ,p_ORIGINAL_SOURCE_HEADER_ID NUMBER
94 ,p_ORIGINAL_SOURCE_LINE_ID NUMBER
95 ,p_PRICE_LIST_HEADER_ID NUMBER
96 ,p_Supercession_inv_item_id Number
97 ,p_flow_status_Id Number
98 ,p_Inventory_Org_Id Number
99 ,p_PROBLEM_DESCRIPTION VARCHAR2 -- swai: bug 4666344
100 ,p_RO_PRIORITY_CODE VARCHAR2 -- swai: R12
101 ,p_RESOLVE_BY_DATE DATE -- rfieldma: 5355051
102 ,p_BULLETIN_CHECK_DATE DATE --- := FND_API.G_MISS_DATE
103 ,p_ESCALATION_CODE VARCHAR2 --:= FND_API.G_MISS_CHAR
104 ,p_REPAIR_YIELD_QUANTITY NUMBER --bug#6692459
105 )
106
107 IS
108 CURSOR C2 IS SELECT CSD_REPAIRS_S1.nextval FROM sys.dual;
109 BEGIN
110 If (px_REPAIR_LINE_ID IS NULL) OR (px_REPAIR_LINE_ID = FND_API.G_MISS_NUM) then
111 OPEN C2;
112 FETCH C2 INTO px_REPAIR_LINE_ID;
113 CLOSE C2;
114 End If;
115 INSERT INTO CSD_REPAIRS(
116 REPAIR_LINE_ID
117 ,REQUEST_ID
118 ,PROGRAM_ID
119 ,PROGRAM_APPLICATION_ID
120 ,PROGRAM_UPDATE_DATE
121 ,CREATED_BY
122 ,CREATION_DATE
123 ,LAST_UPDATED_BY
124 ,LAST_UPDATE_DATE
125 ,LAST_UPDATE_LOGIN
126 ,REPAIR_NUMBER
127 ,INCIDENT_ID
128 ,INVENTORY_ITEM_ID
129 ,CUSTOMER_PRODUCT_ID
130 ,UNIT_OF_MEASURE
131 ,REPAIR_TYPE_ID
132 -- RESOURCE_GROUP Added by Vijay 10/28/2004
133 ,OWNING_ORGANIZATION_ID
134 ,RESOURCE_ID
135 ,INSTANCE_ID
136 ,PROJECT_ID
137 ,TASK_ID
138 ,CONTRACT_LINE_ID
139 ,QUANTITY
140 ,STATUS
141 ,APPROVAL_REQUIRED_FLAG
142 ,DATE_CLOSED
143 ,QUANTITY_IN_WIP
144 ,APPROVAL_STATUS
145 ,QUANTITY_RCVD
146 ,QUANTITY_SHIPPED
147 ,CURRENCY_CODE
148 ,DEFAULT_PO_NUM
149 ,SERIAL_NUMBER
150 ,PROMISE_DATE
151 ,ATTRIBUTE_CATEGORY
152 ,ATTRIBUTE1
153 ,ATTRIBUTE2
154 ,ATTRIBUTE3
155 ,ATTRIBUTE4
156 ,ATTRIBUTE5
157 ,ATTRIBUTE6
158 ,ATTRIBUTE7
159 ,ATTRIBUTE8
160 ,ATTRIBUTE9
161 ,ATTRIBUTE10
162 ,ATTRIBUTE11
163 ,ATTRIBUTE12
164 ,ATTRIBUTE13
165 ,ATTRIBUTE14
166 ,ATTRIBUTE15
167 ,ORDER_LINE_ID
168 ,ORIGINAL_SOURCE_REFERENCE
169 ,STATUS_REASON_CODE
170 ,OBJECT_VERSION_NUMBER
171 ,AUTO_PROCESS_RMA
172 ,REPAIR_MODE
173 ,ITEM_REVISION
174 ,REPAIR_GROUP_ID
175 ,RO_TXN_STATUS
176 ,ORIGINAL_SOURCE_HEADER_ID
177 ,ORIGINAL_SOURCE_LINE_ID
178 ,PRICE_LIST_HEADER_ID
179 ,Supercession_Inv_Item_Id
180 ,flow_status_Id
181 ,Inventory_Org_Id
182 ,PROBLEM_DESCRIPTION -- swai: bug 4666344
183 ,UNIT_NUMBER -- rfieldma, project integration
184 ,RO_PRIORITY_CODE -- swai: R12
185 ,RESOLVE_BY_DATE -- rfieldma: 5355051
186 ,BULLETIN_CHECK_DATE
187 ,ESCALATION_CODE
188 ,REPAIR_YIELD_QUANTITY --bug#6692459
189 --bug#7497907, 12.1 FP, subhat
190 ,ATTRIBUTE16
191 ,ATTRIBUTE17
192 ,ATTRIBUTE18
193 ,ATTRIBUTE19
194 ,ATTRIBUTE20
195 ,ATTRIBUTE21
196 ,ATTRIBUTE22
197 ,ATTRIBUTE23
198 ,ATTRIBUTE24
199 ,ATTRIBUTE25
200 ,ATTRIBUTE26
201 ,ATTRIBUTE27
202 ,ATTRIBUTE28
203 ,ATTRIBUTE29
204 ,ATTRIBUTE30
205 ) VALUES (
206 px_REPAIR_LINE_ID
207 ,decode( p_REQUEST_ID, FND_API.G_MISS_NUM, NULL, p_REQUEST_ID)
208 ,decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_ID)
209 ,decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_APPLICATION_ID)
210 ,decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_PROGRAM_UPDATE_DATE)
211 ,decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
212 ,decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE)
213 ,decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
214 ,decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE)
215 ,decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
216 ,decode( p_REPAIR_NUMBER, FND_API.G_MISS_CHAR, NULL, p_REPAIR_NUMBER)
217 ,decode( p_INCIDENT_ID, FND_API.G_MISS_NUM, NULL, p_INCIDENT_ID)
218 ,decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, NULL, p_INVENTORY_ITEM_ID)
219 ,decode( p_CUSTOMER_PRODUCT_ID, FND_API.G_MISS_NUM, NULL, p_CUSTOMER_PRODUCT_ID)
220 ,decode( p_UNIT_OF_MEASURE, FND_API.G_MISS_CHAR, NULL, p_UNIT_OF_MEASURE)
221 ,decode( p_REPAIR_TYPE_ID, FND_API.G_MISS_NUM, NULL, p_REPAIR_TYPE_ID)
222 -- RESOURCE_GROUP Added by Vijay 10/28/2004
223 ,decode( p_RESOURCE_GROUP, FND_API.G_MISS_NUM, NULL, p_RESOURCE_GROUP)
224 ,decode( p_RESOURCE_ID, FND_API.G_MISS_NUM, NULL, p_RESOURCE_ID)
225 ,decode( p_INSTANCE_ID, FND_API.G_MISS_NUM, NULL, p_INSTANCE_ID)
226 ,decode( p_PROJECT_ID, FND_API.G_MISS_NUM, NULL, p_PROJECT_ID)
227 ,decode( p_TASK_ID, FND_API.G_MISS_NUM, NULL, p_TASK_ID)
228 ,decode( p_CONTRACT_LINE_ID, FND_API.G_MISS_NUM, NULL, p_CONTRACT_LINE_ID)
229 ,decode( p_QUANTITY, FND_API.G_MISS_NUM, NULL, p_QUANTITY)
230 ,decode( p_STATUS, FND_API.G_MISS_CHAR, NULL, p_STATUS)
231 ,decode( p_APPROVAL_REQUIRED_FLAG, FND_API.G_MISS_CHAR, NULL, p_APPROVAL_REQUIRED_FLAG)
232 ,decode( p_DATE_CLOSED, FND_API.G_MISS_DATE, TO_DATE(NULL), p_DATE_CLOSED)
233 ,decode( p_QUANTITY_IN_WIP, FND_API.G_MISS_NUM, NULL, p_QUANTITY_IN_WIP)
234 ,decode( p_APPROVAL_STATUS, FND_API.G_MISS_CHAR, NULL, p_APPROVAL_STATUS)
235 ,decode( p_QUANTITY_RCVD, FND_API.G_MISS_NUM, NULL, p_QUANTITY_RCVD)
236 ,decode( p_QUANTITY_SHIPPED, FND_API.G_MISS_NUM, NULL, p_QUANTITY_SHIPPED)
237 ,decode( p_CURRENCY_CODE, FND_API.G_MISS_CHAR, NULL, p_CURRENCY_CODE)
238 ,decode( p_DEFAULT_PO_NUM, FND_API.G_MISS_CHAR, NULL, p_DEFAULT_PO_NUM)
239 ,decode( p_SERIAL_NUMBER, FND_API.G_MISS_CHAR, NULL, p_SERIAL_NUMBER)
240 ,decode( p_PROMISE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_PROMISE_DATE)
241 ,decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_CATEGORY)
242 ,decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1)
243 ,decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2)
244 ,decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3)
245 ,decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4)
246 ,decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5)
247 ,decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6)
248 ,decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7)
249 ,decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8)
250 ,decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9)
251 ,decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10)
252 ,decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11)
253 ,decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12)
254 ,decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13)
255 ,decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14)
256 ,decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15)
257 ,decode( p_ORDER_LINE_ID, FND_API.G_MISS_NUM, NULL, p_ORDER_LINE_ID)
258 ,decode( p_ORIGINAL_SOURCE_REFERENCE, FND_API.G_MISS_CHAR, NULL, p_ORIGINAL_SOURCE_REFERENCE)
259 ,decode( p_STATUS_REASON_CODE, FND_API.G_MISS_CHAR, NULL, p_STATUS_REASON_CODE)
260 ,decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER)
261 ,decode( p_AUTO_PROCESS_RMA, FND_API.G_MISS_CHAR, NULL, p_AUTO_PROCESS_RMA)
262 ,decode( p_REPAIR_MODE, FND_API.G_MISS_CHAR, NULL, p_REPAIR_MODE)
263 ,decode( p_ITEM_REVISION, FND_API.G_MISS_CHAR, NULL, p_ITEM_REVISION)
264 ,decode( p_REPAIR_GROUP_ID, FND_API.G_MISS_NUM, NULL, p_REPAIR_GROUP_ID)
265 ,decode( p_RO_TXN_STATUS, FND_API.G_MISS_CHAR, NULL, p_RO_TXN_STATUS)
266 ,decode( p_ORIGINAL_SOURCE_HEADER_ID, FND_API.G_MISS_NUM, NULL, p_ORIGINAL_SOURCE_HEADER_ID)
267 ,decode( p_ORIGINAL_SOURCE_LINE_ID, FND_API.G_MISS_NUM, NULL, p_ORIGINAL_SOURCE_LINE_ID)
268 ,decode( p_PRICE_LIST_HEADER_ID, FND_API.G_MISS_NUM, NULL, p_PRICE_LIST_HEADER_ID)
269 ,decode( p_Supercession_Inv_Item_ID, FND_API.G_MISS_NUM, NULL, p_Supercession_Inv_Item_ID)
270 ,decode( p_flow_status_Id, FND_API.G_MISS_NUM, NULL, p_flow_status_Id)
271 ,decode( p_Inventory_Org_Id, FND_API.G_MISS_NUM, NULL, p_Inventory_Org_Id)
272 ,decode( p_PROBLEM_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, p_PROBLEM_DESCRIPTION) -- swai: bug 4666344
273 ,decode( p_UNIT_NUMBER, FND_API.G_MISS_CHAR, NULL, p_UNIT_NUMBER) -- rfieldma, project integration
274 ,decode( p_RO_PRIORITY_CODE, FND_API.G_MISS_CHAR, NULL, p_RO_PRIORITY_CODE) -- swai: R12
275 ,decode( p_RESOLVE_BY_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_RESOLVE_BY_DATE)
276 ,decode( p_BULLETIN_CHECK_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_BULLETIN_CHECK_DATE)
277 ,decode( p_ESCALATION_CODE, FND_API.G_MISS_CHAR, NULL, p_ESCALATION_CODE)
278 ,decode( p_REPAIR_YIELD_QUANTITY, FND_API.G_MISS_NUM, NULL, p_REPAIR_YIELD_QUANTITY) --bug#6692459
279 --bug#7497907, 12.1 FP, subhat
280 ,decode( p_ATTRIBUTE16, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE16)
281 ,decode( p_ATTRIBUTE17, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE17)
282 ,decode( p_ATTRIBUTE18, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE18)
283 ,decode( p_ATTRIBUTE19, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE19)
284 ,decode( p_ATTRIBUTE20, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE20)
285 ,decode( p_ATTRIBUTE21, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE21)
286 ,decode( p_ATTRIBUTE22, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE22)
287 ,decode( p_ATTRIBUTE23, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE23)
288 ,decode( p_ATTRIBUTE24, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE24)
289 ,decode( p_ATTRIBUTE25, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE25)
290 ,decode( p_ATTRIBUTE26, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE26)
291 ,decode( p_ATTRIBUTE27, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE27)
292 ,decode( p_ATTRIBUTE28, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE28)
293 ,decode( p_ATTRIBUTE29, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE29)
294 ,decode( p_ATTRIBUTE30, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE30)
295 );
296 End Insert_Row;
297
298 PROCEDURE Update_Row(
299 p_REPAIR_LINE_ID NUMBER
300 ,p_REQUEST_ID NUMBER
301 ,p_PROGRAM_ID NUMBER
302 ,p_PROGRAM_APPLICATION_ID NUMBER
303 ,p_PROGRAM_UPDATE_DATE DATE
304 ,p_CREATED_BY NUMBER
305 ,p_CREATION_DATE DATE
306 ,p_LAST_UPDATED_BY NUMBER
307 ,p_LAST_UPDATE_DATE DATE
308 ,p_LAST_UPDATE_LOGIN NUMBER
309 ,p_REPAIR_NUMBER VARCHAR2
310 ,p_INCIDENT_ID NUMBER
311 ,p_INVENTORY_ITEM_ID NUMBER
312 ,p_CUSTOMER_PRODUCT_ID NUMBER
313 ,p_UNIT_OF_MEASURE VARCHAR2
314 ,p_REPAIR_TYPE_ID NUMBER
315 -- RESOURCE_GROUP Added by Vijay 10/28/2004
316 ,p_RESOURCE_GROUP NUMBER
317 ,p_RESOURCE_ID NUMBER
318 ,p_INSTANCE_ID NUMBER
319 ,p_PROJECT_ID NUMBER
320 ,p_TASK_ID NUMBER
321 ,p_UNIT_NUMBER VARCHAR2 -- rfieldma, project integration
322 ,p_CONTRACT_LINE_ID NUMBER
323 ,p_QUANTITY NUMBER
324 ,p_STATUS VARCHAR2
325 ,p_APPROVAL_REQUIRED_FLAG VARCHAR2
326 ,p_DATE_CLOSED DATE
327 ,p_QUANTITY_IN_WIP NUMBER
328 ,p_APPROVAL_STATUS VARCHAR2
329 ,p_QUANTITY_RCVD NUMBER
330 ,p_QUANTITY_SHIPPED NUMBER
331 ,p_CURRENCY_CODE VARCHAR2
332 ,p_DEFAULT_PO_NUM VARCHAR2 := NULL
333 ,p_SERIAL_NUMBER VARCHAR2
334 ,p_PROMISE_DATE DATE
335 ,p_ATTRIBUTE_CATEGORY VARCHAR2
336 ,p_ATTRIBUTE1 VARCHAR2
337 ,p_ATTRIBUTE2 VARCHAR2
338 ,p_ATTRIBUTE3 VARCHAR2
339 ,p_ATTRIBUTE4 VARCHAR2
340 ,p_ATTRIBUTE5 VARCHAR2
341 ,p_ATTRIBUTE6 VARCHAR2
342 ,p_ATTRIBUTE7 VARCHAR2
343 ,p_ATTRIBUTE8 VARCHAR2
344 ,p_ATTRIBUTE9 VARCHAR2
345 ,p_ATTRIBUTE10 VARCHAR2
346 ,p_ATTRIBUTE11 VARCHAR2
347 ,p_ATTRIBUTE12 VARCHAR2
348 ,p_ATTRIBUTE13 VARCHAR2
349 ,p_ATTRIBUTE14 VARCHAR2
350 ,p_ATTRIBUTE15 VARCHAR2
351 --bug#7497907, 12.1 FP, subhat
352 ,p_ATTRIBUTE16 VARCHAR2
353 ,p_ATTRIBUTE17 VARCHAR2
354 ,p_ATTRIBUTE18 VARCHAR2
355 ,p_ATTRIBUTE19 VARCHAR2
356 ,p_ATTRIBUTE20 VARCHAR2
357 ,p_ATTRIBUTE21 VARCHAR2
358 ,p_ATTRIBUTE22 VARCHAR2
359 ,p_ATTRIBUTE23 VARCHAR2
360 ,p_ATTRIBUTE24 VARCHAR2
361 ,p_ATTRIBUTE25 VARCHAR2
362 ,p_ATTRIBUTE26 VARCHAR2
363 ,p_ATTRIBUTE27 VARCHAR2
364 ,p_ATTRIBUTE28 VARCHAR2
365 ,p_ATTRIBUTE29 VARCHAR2
366 ,p_ATTRIBUTE30 VARCHAR2
367 ,p_ORDER_LINE_ID NUMBER
368 ,p_ORIGINAL_SOURCE_REFERENCE VARCHAR2
369 ,p_STATUS_REASON_CODE VARCHAR2
370 ,p_OBJECT_VERSION_NUMBER NUMBER
371 ,p_AUTO_PROCESS_RMA VARCHAR2
372 ,p_REPAIR_MODE VARCHAR2
373 ,p_ITEM_REVISION VARCHAR2
374 ,p_REPAIR_GROUP_ID NUMBER
375 ,p_RO_TXN_STATUS VARCHAR2
376 ,p_ORIGINAL_SOURCE_HEADER_ID NUMBER
377 ,p_ORIGINAL_SOURCE_LINE_ID NUMBER
378 ,p_PRICE_LIST_HEADER_ID NUMBER
379 ,p_PROBLEM_DESCRIPTION VARCHAR2 -- swai: bug 4666344
380 ,p_RO_PRIORITY_CODE VARCHAR2 -- swai: R12
381 ,p_RESOLVE_BY_DATE DATE -- rfieldma: 5355051
382 ,p_BULLETIN_CHECK_DATE DATE --:= FND_API.G_MISS_DATE
383 ,p_ESCALATION_CODE VARCHAR2 --:= FND_API.G_MISS_CHAR
384 )
385
386 IS
387 BEGIN
388 Update CSD_REPAIRS
389 SET
390 REQUEST_ID = decode( p_REQUEST_ID, FND_API.G_MISS_NUM, REQUEST_ID, p_REQUEST_ID)
391 ,PROGRAM_ID = decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, PROGRAM_ID, p_PROGRAM_ID)
392 ,PROGRAM_APPLICATION_ID = decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, PROGRAM_APPLICATION_ID, p_PROGRAM_APPLICATION_ID)
393 ,PROGRAM_UPDATE_DATE = decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, PROGRAM_UPDATE_DATE, p_PROGRAM_UPDATE_DATE)
394 ,CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY)
395 ,CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE)
396 ,LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY)
397 ,LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE)
398 ,LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN)
399 ,REPAIR_NUMBER = decode( p_REPAIR_NUMBER, FND_API.G_MISS_CHAR, REPAIR_NUMBER, p_REPAIR_NUMBER)
400 ,INCIDENT_ID = decode( p_INCIDENT_ID, FND_API.G_MISS_NUM, INCIDENT_ID, p_INCIDENT_ID)
401 ,INVENTORY_ITEM_ID = decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, INVENTORY_ITEM_ID, p_INVENTORY_ITEM_ID)
402 ,CUSTOMER_PRODUCT_ID = decode( p_CUSTOMER_PRODUCT_ID, FND_API.G_MISS_NUM, CUSTOMER_PRODUCT_ID, p_CUSTOMER_PRODUCT_ID)
403 ,UNIT_OF_MEASURE = decode( p_UNIT_OF_MEASURE, FND_API.G_MISS_CHAR, UNIT_OF_MEASURE, p_UNIT_OF_MEASURE)
404 ,REPAIR_TYPE_ID = decode( p_REPAIR_TYPE_ID, FND_API.G_MISS_NUM, REPAIR_TYPE_ID, p_REPAIR_TYPE_ID)
405 -- RESOURCE_GROUP Added by Vijay 10/28/2004
406 ,OWNING_ORGANIZATION_ID = decode( p_RESOURCE_GROUP, FND_API.G_MISS_NUM, OWNING_ORGANIZATION_ID , p_RESOURCE_GROUP)
407 ,RESOURCE_ID = decode( p_RESOURCE_ID, FND_API.G_MISS_NUM, RESOURCE_ID, p_RESOURCE_ID)
408 ,INSTANCE_ID = decode( p_INSTANCE_ID, FND_API.G_MISS_NUM, INSTANCE_ID, p_INSTANCE_ID)
409 ,PROJECT_ID = decode( p_PROJECT_ID, FND_API.G_MISS_NUM, PROJECT_ID, p_PROJECT_ID)
410 ,TASK_ID = decode( p_TASK_ID, FND_API.G_MISS_NUM, TASK_ID, p_TASK_ID)
411 ,CONTRACT_LINE_ID = decode( p_CONTRACT_LINE_ID, FND_API.G_MISS_NUM, CONTRACT_LINE_ID, p_CONTRACT_LINE_ID)
412 ,QUANTITY = decode( p_QUANTITY, FND_API.G_MISS_NUM, QUANTITY, p_QUANTITY)
413 -- For R12 Flex Flow, We can no more update status via Update_Repair_Order API.
414 -- ,STATUS = decode( p_STATUS, FND_API.G_MISS_CHAR, STATUS, p_STATUS)
415 ,APPROVAL_REQUIRED_FLAG = decode( p_APPROVAL_REQUIRED_FLAG, FND_API.G_MISS_CHAR, APPROVAL_REQUIRED_FLAG, p_APPROVAL_REQUIRED_FLAG)
416 ,DATE_CLOSED = decode( p_DATE_CLOSED, FND_API.G_MISS_DATE, DATE_CLOSED, p_DATE_CLOSED)
417 ,QUANTITY_IN_WIP = decode( p_QUANTITY_IN_WIP, FND_API.G_MISS_NUM, QUANTITY_IN_WIP, p_QUANTITY_IN_WIP)
418 ,APPROVAL_STATUS = decode( p_APPROVAL_STATUS, FND_API.G_MISS_CHAR, APPROVAL_STATUS, p_APPROVAL_STATUS)
419 ,QUANTITY_RCVD = decode( p_QUANTITY_RCVD, FND_API.G_MISS_NUM, QUANTITY_RCVD, p_QUANTITY_RCVD)
420 ,QUANTITY_SHIPPED = decode( p_QUANTITY_SHIPPED, FND_API.G_MISS_NUM, QUANTITY_SHIPPED, p_QUANTITY_SHIPPED)
421 ,CURRENCY_CODE = decode( p_CURRENCY_CODE, FND_API.G_MISS_CHAR, CURRENCY_CODE, p_CURRENCY_CODE)
422 ,DEFAULT_PO_NUM = decode( p_DEFAULT_PO_NUM, FND_API.G_MISS_CHAR, DEFAULT_PO_NUM, p_DEFAULT_PO_NUM)
423 ,SERIAL_NUMBER = decode( p_SERIAL_NUMBER, FND_API.G_MISS_CHAR, SERIAL_NUMBER, p_SERIAL_NUMBER)
424 ,PROMISE_DATE = decode( p_PROMISE_DATE, FND_API.G_MISS_DATE, PROMISE_DATE, p_PROMISE_DATE)
425 ,ATTRIBUTE_CATEGORY = decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, ATTRIBUTE_CATEGORY, p_ATTRIBUTE_CATEGORY)
426 ,ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, ATTRIBUTE1, p_ATTRIBUTE1)
427 ,ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, ATTRIBUTE2, p_ATTRIBUTE2)
428 ,ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, ATTRIBUTE3, p_ATTRIBUTE3)
429 ,ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, ATTRIBUTE4, p_ATTRIBUTE4)
430 ,ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, ATTRIBUTE5, p_ATTRIBUTE5)
431 ,ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, ATTRIBUTE6, p_ATTRIBUTE6)
432 ,ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, ATTRIBUTE7, p_ATTRIBUTE7)
433 ,ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, ATTRIBUTE8, p_ATTRIBUTE8)
434 ,ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, ATTRIBUTE9, p_ATTRIBUTE9)
435 ,ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, ATTRIBUTE10, p_ATTRIBUTE10)
436 ,ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, ATTRIBUTE11, p_ATTRIBUTE11)
437 ,ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, ATTRIBUTE12, p_ATTRIBUTE12)
438 ,ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, ATTRIBUTE13, p_ATTRIBUTE13)
439 ,ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, ATTRIBUTE14, p_ATTRIBUTE14)
440 ,ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, ATTRIBUTE15, p_ATTRIBUTE15)
441 ,ORDER_LINE_ID = decode( p_ORDER_LINE_ID, FND_API.G_MISS_NUM, ORDER_LINE_ID, p_ORDER_LINE_ID)
442 ,ORIGINAL_SOURCE_REFERENCE = decode( p_ORIGINAL_SOURCE_REFERENCE, FND_API.G_MISS_CHAR, ORIGINAL_SOURCE_REFERENCE, p_ORIGINAL_SOURCE_REFERENCE)
443 ,STATUS_REASON_CODE = decode( p_STATUS_REASON_CODE, FND_API.G_MISS_CHAR, STATUS_REASON_CODE, p_STATUS_REASON_CODE)
444 ,OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER)
445 ,AUTO_PROCESS_RMA = decode( p_AUTO_PROCESS_RMA, FND_API.G_MISS_CHAR, AUTO_PROCESS_RMA, p_AUTO_PROCESS_RMA)
446 ,REPAIR_MODE = decode( p_REPAIR_MODE, FND_API.G_MISS_CHAR, REPAIR_MODE, p_REPAIR_MODE)
447 ,ITEM_REVISION = decode( p_ITEM_REVISION, FND_API.G_MISS_CHAR, ITEM_REVISION, p_ITEM_REVISION)
448 ,REPAIR_GROUP_ID = decode( p_REPAIR_GROUP_ID, FND_API.G_MISS_NUM, REPAIR_GROUP_ID, p_REPAIR_GROUP_ID)
449 ,RO_TXN_STATUS = decode( p_RO_TXN_STATUS, FND_API.G_MISS_CHAR, RO_TXN_STATUS, p_RO_TXN_STATUS)
450 ,ORIGINAL_SOURCE_HEADER_ID = decode( p_ORIGINAL_SOURCE_HEADER_ID , FND_API.G_MISS_NUM, ORIGINAL_SOURCE_HEADER_ID, p_ORIGINAL_SOURCE_HEADER_ID)
451 ,ORIGINAL_SOURCE_LINE_ID = decode( p_ORIGINAL_SOURCE_LINE_ID , FND_API.G_MISS_NUM, ORIGINAL_SOURCE_LINE_ID, p_ORIGINAL_SOURCE_LINE_ID)
452 ,PRICE_LIST_HEADER_ID = decode( p_PRICE_LIST_HEADER_ID , FND_API.G_MISS_NUM, PRICE_LIST_HEADER_ID, p_PRICE_LIST_HEADER_ID)
453 ,PROBLEM_DESCRIPTION = decode( p_PROBLEM_DESCRIPTION , FND_API.G_MISS_CHAR, PROBLEM_DESCRIPTION, p_PROBLEM_DESCRIPTION) -- swai: bug 4666344
454 ,UNIT_NUMBER = decode( p_UNIT_NUMBER, FND_API.G_MISS_CHAR, UNIT_NUMBER, p_UNIT_NUMBER) -- rfieldma, project integration
455 ,RO_PRIORITY_CODE = decode( p_RO_PRIORITY_CODE , FND_API.G_MISS_CHAR, RO_PRIORITY_CODE, p_RO_PRIORITY_CODE) -- swai: R12
456 ,RESOLVE_BY_DATE = decode( p_RESOLVE_BY_DATE, FND_API.G_MISS_DATE, RESOLVE_BY_DATE, p_RESOLVE_BY_DATE) -- rfieldma: 5355051
457 ,BULLETIN_CHECK_DATE = decode( p_BULLETIN_CHECK_DATE, FND_API.G_MISS_DATE, BULLETIN_CHECK_DATE, p_BULLETIN_CHECK_DATE)
458 ,ESCALATION_CODE = decode( p_ESCALATION_CODE , FND_API.G_MISS_CHAR, ESCALATION_CODE, p_ESCALATION_CODE)
459 --bug#7497907, 12.1 FP, subhat
460 ,ATTRIBUTE16 = decode( p_ATTRIBUTE16, FND_API.G_MISS_CHAR, ATTRIBUTE16, p_ATTRIBUTE16)
461 ,ATTRIBUTE17 = decode( p_ATTRIBUTE17, FND_API.G_MISS_CHAR, ATTRIBUTE17, p_ATTRIBUTE17)
462 ,ATTRIBUTE18 = decode( p_ATTRIBUTE18, FND_API.G_MISS_CHAR, ATTRIBUTE18, p_ATTRIBUTE18)
463 ,ATTRIBUTE19 = decode( p_ATTRIBUTE19, FND_API.G_MISS_CHAR, ATTRIBUTE19, p_ATTRIBUTE19)
464 ,ATTRIBUTE20 = decode( p_ATTRIBUTE20, FND_API.G_MISS_CHAR, ATTRIBUTE20, p_ATTRIBUTE20)
465 ,ATTRIBUTE21 = decode( p_ATTRIBUTE21, FND_API.G_MISS_CHAR, ATTRIBUTE21, p_ATTRIBUTE21)
466 ,ATTRIBUTE22 = decode( p_ATTRIBUTE22, FND_API.G_MISS_CHAR, ATTRIBUTE22, p_ATTRIBUTE22)
467 ,ATTRIBUTE23 = decode( p_ATTRIBUTE23, FND_API.G_MISS_CHAR, ATTRIBUTE23, p_ATTRIBUTE23)
468 ,ATTRIBUTE24 = decode( p_ATTRIBUTE24, FND_API.G_MISS_CHAR, ATTRIBUTE24, p_ATTRIBUTE24)
469 ,ATTRIBUTE25 = decode( p_ATTRIBUTE25, FND_API.G_MISS_CHAR, ATTRIBUTE25, p_ATTRIBUTE25)
470 ,ATTRIBUTE26 = decode( p_ATTRIBUTE26, FND_API.G_MISS_CHAR, ATTRIBUTE26, p_ATTRIBUTE26)
471 ,ATTRIBUTE27 = decode( p_ATTRIBUTE27, FND_API.G_MISS_CHAR, ATTRIBUTE27, p_ATTRIBUTE27)
472 ,ATTRIBUTE28 = decode( p_ATTRIBUTE28, FND_API.G_MISS_CHAR, ATTRIBUTE28, p_ATTRIBUTE28)
473 ,ATTRIBUTE29 = decode( p_ATTRIBUTE29, FND_API.G_MISS_CHAR, ATTRIBUTE29, p_ATTRIBUTE29)
474 ,ATTRIBUTE30 = decode( p_ATTRIBUTE30, FND_API.G_MISS_CHAR, ATTRIBUTE30, p_ATTRIBUTE30)
475 where REPAIR_LINE_ID = p_REPAIR_LINE_ID;
476
477 If (SQL%NOTFOUND) then
478 RAISE NO_DATA_FOUND;
479 End If;
480 END Update_Row;
481
482 PROCEDURE Delete_Row(
483 p_REPAIR_LINE_ID NUMBER)
484 IS
485 BEGIN
486 DELETE FROM CSD_REPAIRS
487 WHERE REPAIR_LINE_ID = p_REPAIR_LINE_ID;
488 If (SQL%NOTFOUND) then
489 RAISE NO_DATA_FOUND;
490 End If;
491 END Delete_Row;
492
493 PROCEDURE Lock_Row
494 (
495 p_REPAIR_LINE_ID NUMBER
496 ,p_OBJECT_VERSION_NUMBER NUMBER
497 )
498
499 IS
500 CURSOR C IS
501 SELECT *
502 FROM CSD_REPAIRS
503 WHERE REPAIR_LINE_ID = p_REPAIR_LINE_ID
504 FOR UPDATE of REPAIR_LINE_ID NOWAIT;
505 Recinfo C%ROWTYPE;
506 BEGIN
507 OPEN C;
508 FETCH C INTO Recinfo;
509 If (C%NOTFOUND) then
510 CLOSE C;
511 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
512 APP_EXCEPTION.RAISE_EXCEPTION;
513 End If;
514 CLOSE C;
515
516 l_debug := csd_gen_utility_pvt.g_debug_level;
517 IF l_debug > 0 THEN
518 csd_gen_utility_pvt.add('CSD_REPAIRS_PKG Recinfo.OBJECT_VERSION_NUMBER : '||Recinfo.OBJECT_VERSION_NUMBER);
519 csd_gen_utility_pvt.add('CSD_REPAIRS_PKG p_OBJECT_VERSION_NUMBER : '||p_OBJECT_VERSION_NUMBER);
520 END IF;
521
522 If ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER) then
523 return;
524 else
525 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
526 APP_EXCEPTION.RAISE_EXCEPTION;
527 End If;
528
529 END Lock_Row;
530
531 End CSD_REPAIRS_PKG;