[Home] [Help]
PACKAGE BODY: APPS.CSD_MIGRATE_FROM_115X_PKG4
Source
1 PACKAGE BODY CSD_Migrate_From_115X_PKG4
2 /* $Header: csdmig4b.pls 120.1 2007/04/24 18:51:06 rfieldma ship $ */
3 AS
4
5 /*-------------------------------------------------------------------------------*/
6
7 /* procedure name: CSD_GENERIC_MESSAGES_MIG4 */
8
9 /* description : procedure for migrating CSD_GENERIC_ERRMSGS table data */
10
11 /* from 11.5.9 to 11.5.10 */
12
13 /* */
14
15 /*-------------------------------------------------------------------------------*/
16
17 PROCEDURE csd_generic_messages_mig4
18 IS
19
20
21 --Changed since the 8i does no tsupport the collections type.
22 --TYPE SN_ERRS_REC_ARRAY_TYPE IS VARRAY(1000) OF CSD.CSD_MASS_RO_SN_ERRORS%ROWTYPE;
23 TYPE MRS_ERROR_ID_ARRAY_TYPE IS VARRAY(1000)
24 OF CSD.CSD_MASS_RO_SN_ERRORS.MASS_RO_SN_ERROR_ID%TYPE;
25 TYPE MASS_RO_SN_ID_ARRAY_TYPE IS VARRAY(1000)
26 OF CSD.CSD_MASS_RO_SN_ERRORS.MASS_RO_SN_ID%TYPE;
27 TYPE REPAIR_LINE_ID_ARRAY_TYPE IS VARRAY(1000)
28 OF CSD.CSD_MASS_RO_SN_ERRORS.REPAIR_LINE_ID%TYPE;
29 TYPE ERROR_TYPE_ARRAY_TYPE IS VARRAY(1000)
30 OF CSD.CSD_MASS_RO_SN_ERRORS.ERROR_TYPE%TYPE;
31 TYPE ERROR_MSG_ARRAY_TYPE IS VARRAY(1000)
32 OF CSD.CSD_MASS_RO_SN_ERRORS.ERROR_MSG%TYPE;
33 TYPE CREATED_BY_ARRAY_TYPE IS VARRAY(1000)
34 OF CSD.CSD_MASS_RO_SN_ERRORS.CREATED_BY%TYPE;
35 TYPE CREATION_DT_ARRAY_TYPE IS VARRAY(1000)
36 OF CSD.CSD_MASS_RO_SN_ERRORS.CREATION_DATE%TYPE;
37 TYPE LAST_UPD_BY_ARRAY_TYPE IS VARRAY(1000)
38 OF CSD.CSD_MASS_RO_SN_ERRORS.LAST_UPDATED_BY%TYPE;
39 TYPE LAST_UPD_DT_ARRAY_TYPE IS VARRAY(1000)
40 OF CSD.CSD_MASS_RO_SN_ERRORS.LAST_UPDATE_DATE%TYPE;
41 TYPE LAST_UPD_LGN_ARRAY_TYPE IS VARRAY(1000)
42 OF CSD.CSD_MASS_RO_SN_ERRORS.LAST_UPDATE_LOGIN%TYPE;
43 TYPE OBJ_VER_NUM_ARRAY_TYPE IS VARRAY(1000)
44 OF CSD.CSD_MASS_RO_SN_ERRORS.OBJECT_VERSION_NUMBER%TYPE;
45
46 --sn_errors_arr SN_ERRS_REC_ARRAY_TYPE;
47 mrs_error_id_arr MRS_ERROR_ID_ARRAY_TYPE;
48 mASS_RO_SN_id_arr MASS_RO_SN_ID_ARRAY_TYPE;
49 REPAIR_LINE_ID_arr REPAIR_LINE_ID_ARRAY_TYPE ;
50 ERROR_TYPE_arr ERROR_TYPE_ARRAY_TYPE ;
51 ERROR_MSG_arr ERROR_MSG_ARRAY_TYPE ;
52 CREATED_BY_arr CREATED_BY_ARRAY_TYPE ;
53 CREATION_DT_arr CREATION_DT_ARRAY_TYPE ;
54 LAST_UPD_BY_arr LAST_UPD_BY_ARRAY_TYPE ;
55 LAST_UPD_DT_arr LAST_UPD_DT_ARRAY_TYPE ;
56 LAST_UPD_LGN_arr LAST_UPD_LGN_ARRAY_TYPE ;
57 OBJ_VER_NUM_arr OBJ_VER_NUM_ARRAY_TYPE ;
58
59
60
61
62 v_min NUMBER;
63 v_max NUMBER;
64 v_error_text VARCHAR2(2000);
65 MAX_BUFFER_SIZE NUMBER := 500;
66 l_generic_errmsgs_id NUMBER;
67 error_process EXCEPTION;
68 l_dummy varchar2(1);
69
70 CURSOR get_mass_sn_errors
71 IS
72 SELECT
73 MASS_RO_SN_ERROR_ID, REPAIR_LINE_ID, MASS_RO_SN_ID,
74 ERROR_TYPE, ERROR_MSG, CREATED_BY,
75 CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
76 lAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER
77 FROM csd_mass_ro_sn_errors;
78
79 CURSOR cur_check_exists(p_rep_line_id NUMBER, p_id2 NUMBER) IS
80 SELECT 'x'
81 FROM CSD_GENERIC_ERRMSGS
82 WHERE MODULE_CODE ='SN'
83 AND SOURCE_ENTITY_ID1= p_rep_line_id
84 AND SOURCE_ENTITY_ID2 = p_id2
85 AND SOURCE_ENTITY_TYPE_CODE = 'SERIAL_NUMBER';
86
87 BEGIN
88
89 -- Migration code for Generic Error messages
90 OPEN get_mass_sn_errors;
91
92 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
93 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
94 'CSD.PLSQL.CSD_Migrate_From_115X_PKG4.csd_generic_messages_mig4',
95 'Opened the input cursor');
96 end if;
97
98 LOOP
99 --FETCH get_mass_sn_errors BULK COLLECT INTO sn_errors_arr LIMIT MAX_BUFFER_SIZE;
100 FETCH get_mass_sn_errors BULK COLLECT INTO
101 MRS_ERROR_ID_arr, REPAIR_LINE_ID_arr, MASS_RO_SN_ID_arr,
102 ERROR_TYPE_arr, ERROR_MSG_arr, CREATED_BY_arr,
103 CREATION_DT_arr, LAST_UPD_BY_arr,LAST_UPD_DT_arr,
104 LAST_UPD_LGN_arr, OBJ_VER_NUM_arr;
105 --FOR j IN 1..sn_errors_arr.COUNT
106 FOR j IN 1..mrs_error_id_arr.COUNT
107 LOOP
108 SAVEPOINT CSD_MASS_RO_SN_ERRORS;
109
110 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
111 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
112 'CSD.PLSQL.CSD_Migrate_From_115X_PKG4.csd_generic_messages_mig4',
113 'SAving in the record in the new table,j['
114 ||j||']');
115 end if;
116 BEGIN
117 --OPEN cur_check_exists(sn_errors_arr(j).repair_line_id,sn_errors_arr(j).mass_ro_sn_id);
118 OPEN cur_check_exists(repair_line_id_arr(j),mass_ro_sn_id_arr(j));
119 FETCH cur_check_exists INTO l_dummy;
120 IF(cur_check_exists%NOTFOUND) THEN
121
122 /* rfieldma forward port 5600336
123 base r10 bug#5598542
124 l_generic_errmsgs_id is initialized to null, because PX_GENERIC_ERRMSGS_ID is 'in-out'
125 parameter. Without re initialization it would retain the value from last call and
126 would cause the unique constraint violation error.
127 */
128 l_generic_errmsgs_id :=NULL;
129 APPS.CSD_GENERIC_ERRMSGS_PKG.INSERT_ROW(PX_GENERIC_ERRMSGS_ID => l_generic_errmsgs_id,
130 P_MODULE_CODE => 'SN',
131 --P_SOURCE_ENTITY_ID1 => sn_errors_arr(j).repair_line_id,
132 P_SOURCE_ENTITY_ID1 => repair_line_id_arr(j),
133 --P_SOURCE_ENTITY_ID2 => sn_errors_arr(j).mass_ro_sn_id,
134 P_SOURCE_ENTITY_ID2 => mass_ro_sn_id_arr(j),
135 P_SOURCE_ENTITY_TYPE_CODE => 'SERIAL_NUMBER',
136 P_MSG_TYPE_CODE => 'E',
137 --P_MSG => sn_errors_arr(j).ERROR_MSG,
138 P_MSG => ERROR_MSG_arr(j),
139 P_MSG_STATUS => 'O',
140 P_CREATED_BY => fnd_global.user_id,
141 P_CREATION_DATE => sysdate,
142 P_LAST_UPDATED_BY => fnd_global.user_id,
143 P_LAST_UPDATE_DATE => sysdate,
144 P_LAST_UPDATE_LOGIN => fnd_global.login_id,
145 P_OBJECT_VERSION_NUMBER => 1);
146 IF SQL%NOTFOUND
147 THEN
148 RAISE error_process;
149 END IF;
150 END IF;
151 CLOSE cur_check_exists;
152
153
154 EXCEPTION
155 WHEN error_process THEN
156 CLOSE cur_check_exists;
157 ROLLBACK TO CSD_MASS_RO_SN_ERRORS;
158 v_error_text := substr(sqlerrm, 1, 1000)
159 || 'Mass ro sn Id:'
160 --|| sn_errors_arr(j).mass_ro_sn_error_id;
161 || mrs_error_id_arr(j);
162
163 INSERT INTO CSD_UPG_ERRORS
164 (ORIG_SYSTEM_REFERENCE,
165 TARGET_SYSTEM_REFERENCE,
166 ORIG_SYSTEM_REFERENCE_ID,
167 UPGRADE_DATETIME,
168 ERROR_MESSAGE,
169 MIGRATION_PHASE)
170 VALUES ('CSD_MASS_RO_SN_ERRORS',
171 'CSD_GENERIC_ERRMSGS',
172 --sn_errors_arr(j).mass_ro_sn_error_id,
173 mrs_error_id_arr(j),
174 sysdate,
175 v_error_text,
176 '11.5.10');
177
178 commit;
179
180 raise_application_error( -20000, 'Error while migrating CSD_GENERIC_ERRMSGS table data: Error while inserting into CSD_GENERIC_ERRMSGS. '|| v_error_text);
181
182 END;
183 END LOOP;
184 COMMIT;
185 EXIT WHEN get_mass_sn_errors%NOTFOUND;
186 END LOOP;
187
188 IF get_mass_sn_errors%ISOPEN
189 THEN
190 CLOSE get_mass_sn_errors;
191 END IF;
192 COMMIT;
193
194 END csd_generic_messages_mig4;
195
196
197
198 END CSD_Migrate_From_115X_PKG4;