1 PACKAGE BODY CSD_MIGRATE_FROM_12X_PKG9 AS
2 /* $Header: csdmig9b.pls 120.0 2011/07/21 18:37:03 yvchen noship $ */
3
4
5 /* Procedure Name: CSD_AHL_W_CONTRACT_XREF_MIG9 */
6 /* This procedure updates the existing CSD_AHL_W_CONTRACT_XREF table to */
7 /* support the supplier warranty enhancement. The instance_id for the */
8 /* existing records will be added to the new column INSTANCE_ID. */
9 /* @param. None */
10
11
12 PROCEDURE CSD_AHL_W_CONTRACT_XREF_MIG9 IS
13
14 -- Definitions --
15 TYPE W_CONTRACT_XREF_ID_ARRAY_TYPE IS VARRAY (1000)
16 OF CSD_AHL_W_CONTRACT_XREF.WARRANTY_CONTRACT_XREF_ID%TYPE;
17 TYPE INSTANCE_ID_ARRAY_TYPE IS VARRAY (1000)
18 OF CSD_REPAIRS.CUSTOMER_PRODUCT_ID%TYPE;
19
20 -- Variables --
21 w_contract_xref_id_arr W_CONTRACT_XREF_ID_ARRAY_TYPE;
22 instance_id_arr INSTANCE_ID_ARRAY_TYPE;
23 v_error_text VARCHAR2(2000);
24
25 -- Constants --
26 MAX_BUFFER_SIZE NUMBER := 500;
27
28 -- Exceptions --
29 error_process EXCEPTION;
30
31 -- Cursors --
32 -- find item instance_id for rows where instance_id is NULL
33 CURSOR get_instance_id_cursor
34 IS
35 SELECT DISTINCT
36 wc.warranty_contract_xref_id, rep.customer_product_id
37 FROM
38 CSD_AHL_W_CONTRACT_XREF wc,
39 CSD_REPAIRS rep
40 WHERE wc.instance_id IS NULL
41 AND rep.repair_line_id = wc.repair_line_id;
42
43 BEGIN
44
45 -- Update all rows that do not have a value for instance_id
46
47 OPEN get_instance_id_cursor;
48
49 LOOP
50
51 FETCH get_instance_id_cursor
52 BULK COLLECT INTO w_contract_xref_id_arr, instance_id_arr LIMIT MAX_BUFFER_SIZE;
53
54 FOR i IN 1..w_contract_xref_id_arr.COUNT LOOP
55
56 UPDATE
57 CSD_AHL_W_CONTRACT_XREF
58 SET
59 instance_id = instance_id_arr(i)
60 WHERE warranty_contract_xref_id = w_contract_xref_id_arr(i);
61
62 END LOOP;
63
64 COMMIT;
65
66 EXIT WHEN get_instance_id_cursor%NOTFOUND;
67
68 END LOOP;
69
70 IF get_instance_id_cursor%ISOPEN THEN
71 CLOSE get_instance_id_cursor;
72 END IF;
73
74 COMMIT;
75
76 END CSD_AHL_W_CONTRACT_XREF_MIG9;
77
78 END CSD_MIGRATE_FROM_12X_PKG9;
[Home] [Help]