[Home] [Help]
PACKAGE BODY: APPS.CS_SPLIT_REPAIRS
Source
1 PACKAGE BODY CS_SPLIT_REPAIRS as
2 /* $Header: csxdsplb.pls 115.0 99/07/16 09:07:59 porting ship $ */
3
4 /*
5 This procedure is called from the Repairs form, Split window. It allows users
6 to split one repiar line into 2 user-defined quantities or multiple quantities
7 of 1 each. A history record indicating a split is written for all repair lines
8
9 Paramaters:
10
11 x_user_id user who performed the split
12 x_repair_line_id repair line that is being split
13 x_first_quantity user-defined quantity. Null if the user
14 has indicated split into quantities of 1 each
15 x_total_quantity quantity_received in CS_REPAIRS table.
16
17 How it works:
18 If we have to split into multiple quantites of 1 each (x_first_quantity
19 is NULL).
20
21 1. The repair record is updated with quantity_received = 1 and
22 a history is written with a status of SPLIT.
23 2. LOOP from 1 to (x_total_quantity - 1)
24 Get the next repair_line_id from cs_repairs_s
25 Insert into cs_repairs (quantity_received=1) by setting all
26 other columns the same as in the original repair line
27 Insert repair history records.
28
29 If we have to split into 2 lines with user_defined quantities
30 1. The original repair record is updated with quantity_received =
31 x_first_quantity and history is written indicating the split.
32 2. Get the next repair_line_id from cs_repairs_s
33 3. Insert into cs_repairs (quantity_received = x_total_quantity -
34 x_first_quantity)
35 4. Insert history records.
36 */
37
38 procedure CS_SPLIT_REPAIRS(X_user_id IN NUMBER,
39 X_repair_line_id IN NUMBER,
40 X_first_quantity IN NUMBER,
41 X_total_quantity IN NUMBER) IS
42 Split_lines NUMBER;
43 new_line_id NUMBER;
44 new_repair_number varchar2(30) ;
45 dummy_date DATE;
46 BEGIN
47
48 /* Split into multiple lines of quantity 1 each */
49 /* First update the quantity in the existing record to 1 */
50 /* Insert a history record to indicate the split */
51
52 IF (X_first_quantity IS NULL) THEN
53
54 UPDATE cs_repairs
55 SET quantity_received = 1
56 where repair_line_id = X_repair_line_id;
57
58 INSERT INTO cs_repair_history
59 (
60 REPAIR_LINE_ID,
61 LAST_UPDATE_DATE,
62 LAST_UPDATED_BY,
63 CREATION_DATE,
64 CREATED_BY,
65 LAST_UPDATE_LOGIN,
66 REQUEST_ID,
67 TRANSACTION_DATE,
68 STATUS,
69 ATTRIBUTE1,
70 ATTRIBUTE2,
71 ATTRIBUTE3,
72 ATTRIBUTE4,
73 ATTRIBUTE5,
74 ATTRIBUTE6,
75 ATTRIBUTE7,
76 ATTRIBUTE8,
77 ATTRIBUTE9,
78 ATTRIBUTE10,
79 ATTRIBUTE11,
80 ATTRIBUTE12,
81 ATTRIBUTE13,
82 ATTRIBUTE14,
83 ATTRIBUTE15,
84 CONTEXT,
85 PROGRAM_ID,
86 PROGRAM_APPLICATION_ID,
87 PROGRAM_UPDATE_DATE,
88 REPAIR_HISTORY_ID
89 )
90 VALUES
91 (
92 x_repair_line_id
93 ,sysdate
94 ,x_user_id
95 ,sysdate
96 ,x_user_id
97 ,x_user_id
98 ,NULL
99 ,sysdate
100 ,'SPLIT'
101 ,NULL
102 ,NULL
103 ,NULL
104 ,NULL
105 ,NULL
106 ,NULL
107 ,NULL
108 ,NULL
109 ,NULL
110 ,NULL
111 ,NULL
112 ,NULL
113 ,NULL
114 ,NULL
115 ,NULL
116 ,NULL
117 ,NULL
118 ,NULL
119 ,NULL
120 ,cs_repair_history_s.nextval
121 );
122
123 /* Insert the remaining (quantity - 1) lines using values from the */
124 /* original repair line. */
125
126 FOR split_lines in 1..(X_total_quantity - 1) LOOP
127
128 SELECT cs_repairs_s.nextval
129 INTO new_line_id
130 FROM dual;
131
132 SELECT to_number(cs_repair_number_s.nextval)
133 INTO new_repair_number
134 FROM dual;
135 INSERT INTO cs_repairs
136 (
137 repair_line_id,
138 last_update_date,
139 last_updated_by,
140 creation_date,
141 created_by,
142 last_update_login,
143 request_id,
144 program_id,
145 program_application_id,
146 program_update_date,
147 rma_header_id,
148 rma_line_id,
149 estimate_id,
150 wip_entity_id,
151 repair_header_id,
152 replace_header_id,
153 loaner_header_id,
154 customer_product_id,
155 inventory_item_id,
156 serial_number,
157 quantity_received,
158 quantity_scrapped,
159 quantity_replaced,
160 repair_unit_of_measure_code,
161 status,
162 attribute1,
163 attribute2,
164 attribute3,
165 attribute4,
166 attribute5,
167 attribute6,
168 attribute7,
169 attribute8,
170 attribute9,
171 attribute10,
172 attribute11,
173 attribute12,
174 attribute13,
175 attribute14,
176 attribute15,
177 context,
178 group_id,
179 quantity_complete,
180 org_id,
181 organization_id,
182 original_system_reference,
183 original_system_line_reference,
184 repair_order_line_id,
185 repair_duration,
186 received_date,
187 shipped_date,
188 rma_customer_id,
189 rma_number,
190 rma_type_id,
191 rma_date,
192 rma_line_number,
193 recvd_organization_id,
194 repair_number,
195 mtl_transaction_id,
196 allow_job,
197 incident_id,
198 estimate_business_group_id,
199 diagnosis_id,
200 diagnosed_by_id,
201 job_completion_date,
202 promised_delivery_date
203 )
204 SELECT new_line_id,
205 last_update_date,
206 last_updated_by,
207 creation_date,
208 created_by,
209 last_update_login,
210 request_id,
211 program_id,
212 program_application_id,
213 program_update_date,
214 rma_header_id,
215 rma_line_id,
216 estimate_id,
217 wip_entity_id,
218 repair_header_id,
219 replace_header_id,
220 loaner_header_id,
221 customer_product_id,
222 inventory_item_id,
223 serial_number,
224 1,
225 quantity_scrapped,
226 quantity_replaced,
227 repair_unit_of_measure_code,
228 status,
229 attribute1,
230 attribute2,
231 attribute3,
232 attribute4,
233 attribute5,
234 attribute6,
235 attribute7,
236 attribute8,
237 attribute9,
238 attribute10,
239 attribute11,
240 attribute12,
241 attribute13,
242 attribute14,
243 attribute15,
244 context,
245 group_id,
246 quantity_complete,
247 org_id,
248 organization_id,
249 original_system_reference,
250 original_system_line_reference,
251 repair_order_line_id,
252 repair_duration,
253 received_date,
254 shipped_date,
255 rma_customer_id,
256 rma_number,
257 rma_type_id,
258 rma_date,
259 rma_line_number,
260 recvd_organization_id,
261 new_repair_number,
262 mtl_transaction_id,
263 allow_job,
264 incident_id,
265 estimate_business_group_id,
266 diagnosis_id,
267 diagnosed_by_id,
268 job_completion_date,
269 promised_delivery_date
270 FROM cs_repairs
271 WHERE repair_line_id = X_repair_line_id;
272
273 /* Insert history records for the new lines */
274
275 INSERT INTO cs_repair_history
276 (
277 REPAIR_LINE_ID,
278 LAST_UPDATE_DATE,
279 LAST_UPDATED_BY,
280 CREATION_DATE,
281 CREATED_BY,
282 LAST_UPDATE_LOGIN,
283 REQUEST_ID,
284 TRANSACTION_DATE,
285 STATUS,
286 ATTRIBUTE1,
287 ATTRIBUTE2,
288 ATTRIBUTE3,
289 ATTRIBUTE4,
290 ATTRIBUTE5,
291 ATTRIBUTE6,
292 ATTRIBUTE7,
293 ATTRIBUTE8,
294 ATTRIBUTE9,
295 ATTRIBUTE10,
296 ATTRIBUTE11,
297 ATTRIBUTE12,
298 ATTRIBUTE13,
299 ATTRIBUTE14,
300 ATTRIBUTE15,
301 CONTEXT,
302 PROGRAM_ID,
303 PROGRAM_APPLICATION_ID,
304 PROGRAM_UPDATE_DATE,
305 REPAIR_HISTORY_ID
306 )
307 SELECT new_line_id
308 ,last_update_date
309 ,last_updated_by
310 ,creation_date
311 ,created_by
312 ,last_update_login
313 ,request_id
314 ,transaction_date
315 ,status
316 ,attribute1
317 ,attribute2
318 ,attribute3
319 ,attribute4
320 ,attribute5
321 ,attribute6
322 ,attribute7
323 ,attribute8
324 ,attribute9
325 ,attribute10
326 ,attribute11
327 ,attribute12
328 ,attribute13
329 ,attribute14
330 ,attribute15
331 ,context
332 ,program_id
333 ,program_application_id
334 ,program_update_date
335 ,cs_repair_history_s.nextval
336 FROM cs_repair_history
337 WHERE repair_line_id = x_repair_line_id;
338
339 END LOOP;
340
341 ELSE
342
343 /*
344 Means split into 2 lines with user-defined quantities
345 First update the quantity in the existing record to first quantity
346 Insert a history record to indicate the split
347 */
348
349 UPDATE cs_repairs
350 SET quantity_received = x_first_quantity
351 where repair_line_id = X_repair_line_id;
352
353 INSERT INTO cs_repair_history
354 (
355 REPAIR_LINE_ID,
356 LAST_UPDATE_DATE,
357 LAST_UPDATED_BY,
358 CREATION_DATE,
359 CREATED_BY,
360 LAST_UPDATE_LOGIN,
361 REQUEST_ID,
362 TRANSACTION_DATE,
363 STATUS,
364 ATTRIBUTE1,
365 ATTRIBUTE2,
366 ATTRIBUTE3,
367 ATTRIBUTE4,
368 ATTRIBUTE5,
369 ATTRIBUTE6,
370 ATTRIBUTE7,
371 ATTRIBUTE8,
372 ATTRIBUTE9,
373 ATTRIBUTE10,
374 ATTRIBUTE11,
375 ATTRIBUTE12,
376 ATTRIBUTE13,
377 ATTRIBUTE14,
378 ATTRIBUTE15,
379 CONTEXT,
380 PROGRAM_ID,
381 PROGRAM_APPLICATION_ID,
382 PROGRAM_UPDATE_DATE,
383 REPAIR_HISTORY_ID
384 )
385 VALUES
386 (
387 x_repair_line_id
388 ,sysdate
389 ,x_user_id
390 ,sysdate
391 ,x_user_id
392 ,x_user_id
393 ,NULL
394 ,sysdate
395 ,'SPLIT'
396 ,NULL
397 ,NULL
398 ,NULL
399 ,NULL
400 ,NULL
401 ,NULL
402 ,NULL
403 ,NULL
404 ,NULL
405 ,NULL
406 ,NULL
407 ,NULL
408 ,NULL
409 ,NULL
410 ,NULL
411 ,NULL
412 ,NULL
413 ,NULL
414 ,NULL
415 ,cs_repair_history_s.nextval
416 );
417
418 /* Insert a repair line with the total_quantity - first_quantity */
419
420 /* dbms_output.put_line('New repair record'); */
421
422 SELECT cs_repairs_s.nextval
423 INTO new_line_id
424 FROM dual;
425
426 SELECT to_number(cs_repair_number_s.nextval)
427 INTO new_repair_number
428 FROM dual;
429
430 INSERT INTO cs_repairs
431 (
432 repair_line_id,
433 last_update_date,
434 last_updated_by,
435 creation_date,
436 created_by,
437 last_update_login,
438 request_id,
439 program_id,
440 program_application_id,
441 program_update_date,
442 rma_header_id,
443 rma_line_id,
444 estimate_id,
445 wip_entity_id,
446 repair_header_id,
447 replace_header_id,
448 loaner_header_id,
449 customer_product_id,
450 inventory_item_id,
451 serial_number,
452 quantity_received,
453 quantity_scrapped,
454 quantity_replaced,
455 repair_unit_of_measure_code,
456 status,
457 attribute1,
458 attribute2,
459 attribute3,
460 attribute4,
461 attribute5,
462 attribute6,
463 attribute7,
464 attribute8,
465 attribute9,
466 attribute10,
467 attribute11,
468 attribute12,
469 attribute13,
470 attribute14,
471 attribute15,
472 context,
473 group_id,
474 quantity_complete,
475 org_id,
476 organization_id,
477 original_system_reference,
478 original_system_line_reference,
479 repair_order_line_id,
480 repair_duration,
481 received_date,
482 shipped_date,
483 rma_customer_id,
484 rma_number,
485 rma_type_id,
486 rma_date,
487 rma_line_number,
488 recvd_organization_id,
489 repair_number,
490 mtl_transaction_id,
491 allow_job,
492 incident_id,
493 estimate_business_group_id,
494 diagnosis_id,
495 diagnosed_by_id,
496 job_completion_date,
497 promised_delivery_date
498 )
499 SELECT new_line_id,
500 last_update_date,
501 last_updated_by,
502 creation_date,
503 created_by,
504 last_update_login,
505 request_id,
506 program_id,
507 program_application_id,
508 program_update_date,
509 rma_header_id,
510 rma_line_id,
511 estimate_id,
512 wip_entity_id,
513 repair_header_id,
514 replace_header_id,
515 loaner_header_id,
516 customer_product_id,
517 inventory_item_id,
518 serial_number,
519 (x_total_quantity - x_first_quantity),
520 quantity_scrapped,
521 quantity_replaced,
522 repair_unit_of_measure_code,
523 status,
524 attribute1,
525 attribute2,
526 attribute3,
527 attribute4,
528 attribute5,
529 attribute6,
530 attribute7,
531 attribute8,
532 attribute9,
533 attribute10,
534 attribute11,
535 attribute12,
536 attribute13,
537 attribute14,
538 attribute15,
539 context,
540 group_id,
541 quantity_complete,
542 org_id,
543 organization_id,
544 original_system_reference,
545 original_system_line_reference,
546 repair_order_line_id,
547 repair_duration,
548 received_date,
549 shipped_date,
550 rma_customer_id,
551 rma_number,
552 rma_type_id,
553 rma_date,
554 rma_line_number,
555 recvd_organization_id,
556 new_repair_number,
557 mtl_transaction_id,
558 allow_job,
559 incident_id,
560 estimate_business_group_id,
561 diagnosis_id,
562 diagnosed_by_id,
563 job_completion_date,
564 promised_delivery_date
565 FROM cs_repairs
569
566 WHERE repair_line_id = X_repair_line_id;
567
568 /* Insert history records for the new line */
570 INSERT INTO cs_repair_history
571 (
572 REPAIR_LINE_ID,
573 LAST_UPDATE_DATE,
574 LAST_UPDATED_BY,
575 CREATION_DATE,
576 CREATED_BY,
577 LAST_UPDATE_LOGIN,
578 REQUEST_ID,
579 TRANSACTION_DATE,
580 STATUS,
581 ATTRIBUTE1,
582 ATTRIBUTE2,
583 ATTRIBUTE3,
584 ATTRIBUTE4,
585 ATTRIBUTE5,
586 ATTRIBUTE6,
587 ATTRIBUTE7,
588 ATTRIBUTE8,
589 ATTRIBUTE9,
590 ATTRIBUTE10,
591 ATTRIBUTE11,
592 ATTRIBUTE12,
593 ATTRIBUTE13,
594 ATTRIBUTE14,
595 ATTRIBUTE15,
596 CONTEXT,
597 PROGRAM_ID,
598 PROGRAM_APPLICATION_ID,
599 PROGRAM_UPDATE_DATE,
600 REPAIR_HISTORY_ID
601 )
602 SELECT new_line_id
603 ,last_update_date
604 ,last_updated_by
605 ,creation_date
606 ,created_by
607 ,last_update_login
608 ,request_id
609 ,transaction_date
610 ,status
611 ,attribute1
612 ,attribute2
613 ,attribute3
614 ,attribute4
615 ,attribute5
616 ,attribute6
617 ,attribute7
618 ,attribute8
619 ,attribute9
620 ,attribute10
621 ,attribute11
622 ,attribute12
623 ,attribute13
624 ,attribute14
625 ,attribute15
626 ,context
627 ,program_id
628 ,program_application_id
629 ,program_update_date
630 ,cs_repair_history_s.nextval
631 FROM cs_repair_history
632 WHERE repair_line_id = x_repair_line_id;
633
634 END IF;
635
636 commit;
637
638 END;
639
640 END CS_SPLIT_REPAIRS;