[Home] [Help]
PACKAGE BODY: APPS.CZ_IMP_ALL
Source
1 PACKAGE BODY CZ_IMP_ALL AS
2 /* $Header: cziallb.pls 120.4 2007/11/26 07:59:44 kdande ship $ */
3 ------------------------------------------------------------------------------------------
4 FUNCTION isTracingEnabled RETURN BOOLEAN IS
5 nEnableTrace VARCHAR2(1) := '0';
6 v_settings_id VARCHAR2(40);
7 v_section_name VARCHAR2(30);
8 BEGIN
9
10 v_settings_id := 'ENABLETRACE';
11 v_section_name := 'IMPORT';
12
13 SELECT decode(upper(VALUE),'TRUE','1','FALSE','0','T','1','F','0','1','1',
14 '0','0','YES','1','NO','0','Y','1', 'N', '0','0')
15 INTO nEnableTrace FROM CZ_DB_SETTINGS
16 WHERE upper(SETTING_ID)=v_settings_id AND SECTION_NAME=v_section_name;
17 RETURN (nEnableTrace = '1');
18 EXCEPTION
19 WHEN OTHERS THEN
20 RETURN FALSE;
21 END isTracingEnabled;
22 ------------------------------------------------------------------------------------------
23 FUNCTION isTimingLogEnabled RETURN BOOLEAN IS
24 nEnableLog VARCHAR2(1) := '0';
25 v_settings_id VARCHAR2(40);
26 v_section_name VARCHAR2(30);
27 BEGIN
28
29 v_settings_id := 'TIMEIMPORT';
30 v_section_name := 'IMPORT';
31
32 SELECT decode(upper(VALUE),'TRUE','1','FALSE','0','T','1','F','0','1','1',
33 '0','0','YES','1','NO','0','Y','1', 'N', '0','0')
34 INTO nEnableLog FROM CZ_DB_SETTINGS
35 WHERE upper(SETTING_ID)=v_settings_id AND SECTION_NAME=v_section_name;
36 RETURN (nEnableLog='1');
37 EXCEPTION
38 WHEN OTHERS THEN
39 RETURN FALSE;
40 END isTimingLogEnabled;
41 ------------------------------------------------------------------------------------------
42 PROCEDURE go_cp(errbuf OUT NOCOPY VARCHAR2,retcode OUT NOCOPY NUMBER) IS
43 xERROR BOOLEAN:=FALSE;
44 BEGIN
45 go(errbuf,retcode);
46 errbuf:='';
47 retcode:=0;
48 EXCEPTION
49 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
50 retcode:=2;
51 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_ACTIVE_SESSION_EXISTS');
52 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.go_cp',11276,NULL);
53 RAISE;
54 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
55 retcode:=2;
56 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED');
57 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.go_cp',11276,NULL);
58 RAISE;
59 WHEN OTHERS THEN
60 retcode:=2;
61 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED', 'ERRORTEXT', SQLERRM);
62 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.go_cp',11276,NULL);
63 RAISE;
64 END go_cp;
65
66 ------------------------------------------------------------------------------------------
67 PROCEDURE setReturnCode(retcode IN NUMBER, errbuf IN VARCHAR2) IS
68 BEGIN
69 IF(retcode > CZ_ORAAPPS_INTEGRATE.mRETCODE)THEN
70
71 CZ_ORAAPPS_INTEGRATE.mRETCODE := retcode;
72 CZ_ORAAPPS_INTEGRATE.mERRBUF := errbuf;
73 END IF;
74 END setReturnCode;
75 ------------------------------------------------------------------------------------------
76 PROCEDURE goSingleBill_cp
77 (errbuf IN OUT NOCOPY VARCHAR2,retcode IN OUT NOCOPY NUMBER,nORG_ID IN NUMBER,nTOP_ID IN NUMBER,
78 COPY_CHILD_MODELS IN VARCHAR2 DEFAULT '0',
79 REFRESH_MODEL_ID IN NUMBER DEFAULT -1,
80 COPY_ROOT_MODEL IN VARCHAR2 DEFAULT '0') IS
81
82 xERROR BOOLEAN:=FALSE;
83 l_run_id NUMBER;
84
85 BEGIN
86
87 CZ_ORAAPPS_INTEGRATE.mERRBUF := '';
88 CZ_ORAAPPS_INTEGRATE.mRETCODE := 0;
89
90 /* Log a message if the bill being imported (not refreshed) refers to a common bill */
91 IF (REFRESH_MODEL_ID = -1) THEN
92 check_for_common_bill(errbuf,retcode,nORG_ID,nTOP_ID);
93 END IF;
94 goSingleBill(nORG_ID,nTOP_ID,COPY_CHILD_MODELS,REFRESH_MODEL_ID, '0', l_run_id);
95
96 errbuf := CZ_ORAAPPS_INTEGRATE.mERRBUF;
97 retcode := CZ_ORAAPPS_INTEGRATE.mRETCODE;
98
99 EXCEPTION
100 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
101 retcode:=2;
102 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_ACTIVE_SESSION_EXISTS');
103 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.goSingleBill_cp',11276,NULL);
104 RAISE;
105 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
106 retcode:=2;
107 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED');
108 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.goSingleBill_cp',11276,NULL);
109 RAISE;
110 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
111 RAISE;
112 WHEN OTHERS THEN
113 retcode:=2;
114 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
115 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.goSingleBill_cp',11276,NULL);
116 RAISE;
117 END goSingleBill_cp;
118 ------------------------------------------------------------------------------------------
119 PROCEDURE go(errbuf IN OUT NOCOPY VARCHAR2,retcode IN OUT NOCOPY NUMBER) IS
120
121 xERROR BOOLEAN:=FALSE;
122 nTop_ID CZ_XFR_PROJECT_BILLS.TOP_ITEM_ID%TYPE;
123 nOrg_ID CZ_XFR_PROJECT_BILLS.ORGANIZATION_ID%TYPE;
124 sExplType CZ_XFR_PROJECT_BILLS.EXPLOSION_TYPE%TYPE;
125 sCopyModels CZ_XFR_PROJECT_BILLS.copy_addl_child_models%TYPE;
126 nModelId CZ_XFR_PROJECT_BILLS.model_ps_node_id%TYPE;
127 nServerId cz_servers.server_local_id%TYPE;
128
129 TYPE tOrgId IS TABLE OF cz_xfr_project_bills.organization_id%TYPE INDEX BY BINARY_INTEGER;
130 TYPE tTopItemId IS TABLE OF cz_xfr_project_bills.top_item_id%TYPE INDEX BY BINARY_INTEGER;
131 TYPE tExplType IS TABLE OF cz_xfr_project_bills.explosion_type%TYPE INDEX BY BINARY_INTEGER;
132 TYPE tCopyChildModel IS TABLE OF cz_xfr_project_bills.copy_addl_child_models%TYPE INDEX BY BINARY_INTEGER;
133 TYPE tModelId IS TABLE OF cz_xfr_project_bills.model_ps_node_id%TYPE INDEX BY BINARY_INTEGER;
134
135 orgId tOrgId;
136 topItemId tTopItemId ;
137 explType tExplType;
138 copyChildModel tCopyChildModel;
139 modelId tModelId;
140 l_run_id NUMBER; -- sselahi: added to pass to the call to CZ_IMP_SINGLE.ImportSingleBill
141
142 v_enabled VARCHAR2(1) := '1';
143 BEGIN
144
145 BEGIN
146 SELECT server_local_id INTO nServerId
147 FROM cz_servers
148 WHERE import_enabled = v_enabled;
149 EXCEPTION
150 WHEN TOO_MANY_ROWS THEN
151 retcode:=2;
152 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_TOO_MANY_SERVERS');
153 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.go',11276,NULL);
154 RAISE;
155 WHEN NO_DATA_FOUND THEN
156 retcode:=2;
157 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_NO_SERVERS');
158 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.go',11276,NULL);
159 RAISE;
160 END;
161
162 SELECT organization_id, top_item_id, explosion_type, NVL(copy_addl_child_models, '0'), model_ps_node_id
163 BULK COLLECT INTO
164 orgId,topItemId,explType,copyChildModel,modelId
165 FROM cz_xfr_project_bills
166 WHERE deleted_flag = '0'
167 AND source_server = nServerId;
168
169 IF(orgId.COUNT = 0)THEN
170 retcode:=2;
171 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_NO_IMPORTED_MODELS');
172 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.GO',11276,NULL);
173 RETURN;
174 END IF;
175
176 FOR i IN orgId.FIRST .. orgId.LAST
177 LOOP
178 /* Log a message if the bill being imported (not refreshed) refers to a common bill */
179 check_for_common_bill(errbuf,retcode,orgId(i), topItemId(i));
180 CZ_IMP_SINGLE.ImportSingleBill(orgId(i), topItemId(i), copyChildModel(i), modelId(i), '0', explType(i), SYSDATE, l_run_id); -- sselahi: added l_run_id
181 END LOOP;
182 COMMIT;
183 EXCEPTION
184 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
185 RAISE;
186 WHEN OTHERS THEN
187 retcode:=2;
188 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
189 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.GO',11276,NULL);
190 RAISE;
191 END go;
192 ------------------------------------------------------------------------------------------
193 PROCEDURE go_generic(outRun_ID IN OUT NOCOPY PLS_INTEGER,
194 inRun_ID IN PLS_INTEGER DEFAULT NULL, p_rp_folder_id IN NUMBER) IS
195 genRun_ID PLS_INTEGER;
196 nCommit_size PLS_INTEGER DEFAULT 1;
197 nMax_err PLS_INTEGER DEFAULT 10000;
198 sTableName CZ_XFR_TABLES.DST_TABLE%TYPE;
199 sSrcTableName CZ_XFR_TABLES.SRC_TABLE%TYPE;
200 xERROR BOOLEAN:=FALSE;
201 bAutoCreateUsers CZ_DB_SETTINGS.VALUE%TYPE:='NO';
202 bRunExploder CZ_DB_SETTINGS.VALUE%TYPE:='NO';
203 outGrp_ID NUMBER;
204 outError_code NUMBER;
205 outErr_msg VARCHAR2(255);
206 d_str varchar2(255);
207 l_failed NUMBER :=0;
208
209 CURSOR C_IMPORT_ORDER IS
210 SELECT DST_TABLE,SRC_TABLE FROM CZ_XFR_TABLES
211 WHERE XFR_GROUP='GENERIC' AND DISABLED='0'
212 ORDER BY ORDER_SEQ;
213
214 v_settings_id VARCHAR2(40);
215 v_section_name VARCHAR2(30);
216
217 BEGIN
218
219 CZ_ADMIN.SPX_SYNC_IMPORTSESSIONS;
220 DBMS_APPLICATION_INFO.SET_MODULE('CZIMPORT','');
221
222 ----1) Insert new record into XFR_RUN_INFO and get the generated Run_ID for this import
223
224 IF(inRun_ID IS NOT NULL)THEN
225 genRun_ID:=inRun_ID;
226 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY,COMPLETED)
227 SELECT genRun_ID,SYSDATE,SYSDATE,'0' FROM DUAL WHERE NOT EXISTS
228 (SELECT 1 FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=genRun_ID);
229 UPDATE CZ_XFR_RUN_INFOS SET
230 STARTED=SYSDATE,LAST_ACTIVITY=SYSDATE,COMPLETED='0'
231 WHERE RUN_ID=genRun_ID;
232 COMMIT;
233
234 OPEN C_IMPORT_ORDER;
235 LOOP
236 BEGIN
237 FETCH C_IMPORT_ORDER INTO sTableName,sSrcTableName;
238 EXIT WHEN C_IMPORT_ORDER%NOTFOUND;
239 setRecStatus(genRun_ID,sSrcTableName);
240 EXCEPTION
241 WHEN OTHERS THEN
242 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED', 'ERRORTEXT', SQLERRM);
243 xERROR:=cz_utils.log_report(d_str,1,'GO_GENERIC.SETRECSTATUS',11276,genRun_ID);
244 RAISE;
245 END;
246 END LOOP;
247 CLOSE C_IMPORT_ORDER;
248 ELSE
249 SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO genRun_ID FROM DUAL;
250 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY,COMPLETED)
251 SELECT genRun_ID,SYSDATE,SYSDATE,'0' FROM DUAL WHERE NOT EXISTS
252 (SELECT 1 FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=genRun_ID);
253 UPDATE CZ_XFR_RUN_INFOS SET
254 STARTED=SYSDATE,LAST_ACTIVITY=SYSDATE,COMPLETED='0'
255 WHERE RUN_ID=genRun_ID;
256 COMMIT;
257
258 OPEN C_IMPORT_ORDER;
259 LOOP
260 BEGIN
261 FETCH C_IMPORT_ORDER INTO sTableName,sSrcTableName;
262 EXIT WHEN C_IMPORT_ORDER%NOTFOUND;
263 setRunID(genRun_ID,sSrcTableName);
264 EXCEPTION
265 WHEN OTHERS THEN
266 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED', 'ERRORTEXT', SQLERRM);
267 xERROR:=cz_utils.log_report(d_str,1,'GO_GENERIC.SETRUNID',11276,genRun_ID);
268 RAISE;
269 END;
270 END LOOP;
271 CLOSE C_IMPORT_ORDER;
272 END IF;
273 outRun_ID:=genRun_ID;
274 COMMIT;
275
276 ----2) Get the COMMIT_SIZE and MAX_ERR values from CZ_DB_SETTINGS
277
278 v_settings_id := 'CommitSize';
279 v_section_name := 'IMPORT';
280
281 BEGIN
282 SELECT VALUE INTO nCommit_size FROM CZ_DB_SETTINGS
283 WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
284 EXCEPTION
285 WHEN NO_DATA_FOUND THEN
286 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_NO_COMMITSIZE');
287 xERROR:=cz_utils.log_report(d_str,1,'GO_GENERIC.CZ_DB_SETTINGS',11276,genRun_ID);
288 WHEN OTHERS THEN
289 xERROR:=cz_utils.log_report(SQLERRM,1,'GO_GENERIC.CZ_DB_SETTINGS.COMMITSIZE',11276,genRun_ID);
290 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
291 END;
292
293 v_settings_id := 'MaximumErrors';
294 v_section_name := 'IMPORT';
295
296 BEGIN
297 SELECT VALUE INTO nMax_err FROM CZ_DB_SETTINGS
298 WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
299 EXCEPTION
300 WHEN NO_DATA_FOUND THEN
301 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_NO_MAX_ERR');
302 xERROR:=cz_utils.log_report(d_str,1,'GO_GENERIC.CZ_DB_SETTINGS',11276,genRun_ID);
303 WHEN OTHERS THEN
304 xERROR:=cz_utils.log_report(SQLERRM,1,'GO_GENERIC.CZ_DB_SETTINGS.MAXERRORS',11276,genRun_ID);
305 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
306 END;
307
308 ----3) Call all the import procedures in the order specified by ORDER_SEQ field of
309 ----- CZ_XFR_TABLES with XFR_GROUP='IMPORT'
310
311 import_before_start;
312
313 OPEN C_IMPORT_ORDER;
314 LOOP
315 BEGIN
316 FETCH C_IMPORT_ORDER INTO sTableName,sSrcTableName;
317 EXIT WHEN C_IMPORT_ORDER%NOTFOUND;
318
319 populate_table(genRun_ID,sTableName,nCommit_size,nMax_err,'GENERIC', p_rp_folder_id, l_failed);
320
321 EXCEPTION
322 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
323 RAISE;
324 WHEN OTHERS THEN
325 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED', 'ERRORTEXT', SQLERRM);
326 xERROR:=cz_utils.log_report(d_str,1,'GO_GENERIC.IMPORT',11276,NULL);
327 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
328 END;
329 END LOOP;
330 CLOSE C_IMPORT_ORDER;
331
332 import_after_complete(genRun_ID);
333
334 ----4) Finally update the (LAST_ACTIVITY,COMPLETED) fields of CZ_XFR_RUN_INFOS
335
336 UPDATE CZ_XFR_RUN_INFOS SET
337 LAST_ACTIVITY=SYSDATE,
338 COMPLETED='1'
339 WHERE RUN_ID=genRun_ID;
340 COMMIT;
341
342 ----5) and create database users if necessary
343
344 v_settings_id := 'AUTOCREATE_IMPORTED_USERS';
345 v_section_name := 'ORAAPPS_INTEGRATE';
346
347 BEGIN
348 SELECT VALUE INTO bAutoCreateUsers FROM CZ_DB_SETTINGS
349 WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
350 EXCEPTION
351 WHEN OTHERS THEN
352 bAutoCreateUsers:='NO';
353 xERROR:=cz_utils.log_report(CZ_UTILS.GET_TEXT('CZ_IMP_NO_AUTOCREATE_SETTING'),1,'GO_GENERIC',11276,genRun_ID);
354 END;
355 IF(bAutoCreateUsers='YES' OR bAutoCreateUsers='NAMED_ONLY')THEN
356 CZ_ADMIN.ENABLE_END_USERS;
357 ELSE
358 CZ_ADMIN.VALIDATE_END_USERS;
359 END IF;
360
361 --DBMS_OUTPUT.PUT_LINE(CZ_UTILS.GET_TEXT('CZ_IMP_IMPORT_COMPLETED','RUNID',TO_CHAR(genRun_ID)));
362
363 COMMIT;
364 DBMS_APPLICATION_INFO.SET_MODULE('','');
365
366 EXCEPTION
367 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
368 xERROR:=cz_utils.log_report(CZ_UTILS.GET_TEXT('CZ_IMP_ACTIVE_SESSION_EXISTS'),1,'GO_GENERIC',11276,genRun_ID);
369 DBMS_APPLICATION_INFO.SET_MODULE('','');
370 RAISE;
371 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
372 DBMS_APPLICATION_INFO.SET_MODULE('','');
373 RAISE;
374 WHEN OTHERS THEN
375 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED', 'ERRORTEXT', SQLERRM);
376 xERROR:=cz_utils.log_report(d_str,1,'CZ_IMP_ALL.GO_GENERIC',11276,NULL);
377 DBMS_APPLICATION_INFO.SET_MODULE('','');
378 RAISE;
379 END go_generic;
380 ------------------------------------------------------------------------------------------
381 PROCEDURE populate_table(inRun_ID IN PLS_INTEGER,
382 table_name IN VARCHAR2,
383 commit_size IN PLS_INTEGER,
384 max_err IN PLS_INTEGER,
385 inXFR_GROUP IN VARCHAR2,
386 p_rp_folder_id IN NUMBER,
387 x_failed IN OUT NOCOPY NUMBER)
388 IS
389 lower_table_name VARCHAR2(50) := LOWER(table_name);
390 xERROR BOOLEAN:=FALSE;
391 Inserts PLS_INTEGER;
392 Updates PLS_INTEGER;
393 Failed PLS_INTEGER;
394 Dups PLS_INTEGER;
395 d_str varchar2(255);
396 BEGIN
397 --DBMS_OUTPUT.ENABLE;
398 --DBMS_OUTPUT.PUT_LINE('IMPORTING TABLE: ' || lower_table_name);
399 IF(lower_table_name='cz_item_masters') THEN
400 CZ_IMP_IM_MAIN.MAIN_ITEM_MASTER(inRun_ID, commit_size, max_err,
401 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
402 ELSIF(lower_table_name='cz_ps_nodes') THEN
403 CZ_IMP_PS_NODE.MAIN_PS_NODE(inRun_ID, commit_size, max_err,
404 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
405 ELSIF(lower_table_name='cz_customers') THEN
406 CZ_IMP_AC_MAIN.MAIN_CUSTOMER(inRun_ID, commit_size, max_err,
407 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
408 ELSIF(lower_table_name='cz_customer_end_users') THEN
409 CZ_IMP_AC_MAIN.MAIN_CUSTOMER_END_USER(inRun_ID, commit_size, max_err,
410 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
411 ELSIF(lower_table_name='cz_addresses') THEN
412 CZ_IMP_AC_MAIN.MAIN_ADDRESS(inRun_ID, commit_size, max_err,
413 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
414 ELSIF(lower_table_name='cz_address_uses') THEN
415 CZ_IMP_AC_MAIN.MAIN_ADDRESS_USES(inRun_ID, commit_size, max_err,
416 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
417 ELSIF(lower_table_name='cz_contacts') THEN
418 CZ_IMP_AC_MAIN.MAIN_CONTACT(inRun_ID, commit_size, max_err,
419 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
420 ELSIF(lower_table_name='cz_prices') THEN
421 CZ_IMP_PR_MAIN.MAIN_PRICE(inRun_ID, commit_size, max_err,
422 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
423 ELSIF(lower_table_name='cz_price_groups') THEN
424 CZ_IMP_PR_MAIN.MAIN_PRICE_GROUP(inRun_ID, commit_size, max_err,
425 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
426 ELSIF(lower_table_name='cz_localized_texts') THEN
427 CZ_IMP_PS_NODE.MAIN_INTL_TEXT(inRun_ID, commit_size, max_err,
428 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
429 ELSIF(lower_table_name='cz_devl_projects') THEN
430 CZ_IMP_PS_NODE.MAIN_DEVL_PROJECT(inRun_ID, commit_size, max_err,
431 Inserts, Updates, x_failed, Dups,
432 inXFR_GROUP, p_rp_folder_id); -- sselahi rpf
433 ELSIF(lower_table_name='cz_end_users') THEN
434 CZ_IMP_AC_MAIN.MAIN_END_USER(inRun_ID, commit_size, max_err,
435 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
436 ELSIF(lower_table_name='cz_end_user_groups') THEN
437 CZ_IMP_AC_MAIN.MAIN_END_USER_GROUP(inRun_ID, commit_size, max_err,
438 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
439 ELSIF(lower_table_name='cz_item_property_values') THEN
440 CZ_IMP_IM_MAIN.MAIN_ITEM_PROPERTY_VALUE(inRun_ID, commit_size, max_err,
441 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
442 ELSIF(lower_table_name='cz_item_types') THEN
443 CZ_IMP_IM_MAIN.MAIN_ITEM_TYPE(inRun_ID, commit_size, max_err,
444 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
445 ELSIF(lower_table_name='cz_item_type_properties') THEN
446 CZ_IMP_IM_MAIN.MAIN_ITEM_TYPE_PROPERTY(inRun_ID, commit_size, max_err,
447 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
448 ELSIF(lower_table_name='cz_properties') THEN
449 CZ_IMP_IM_MAIN.MAIN_PROPERTY(inRun_ID, commit_size, max_err,
450 Inserts, Updates, x_failed, Dups, inXFR_GROUP, p_rp_folder_id);
451 ELSIF(lower_table_name='cz_user_groups') THEN
452 CZ_IMP_AC_MAIN.MAIN_USER_GROUP(inRun_ID, commit_size, max_err,
453 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
454 ELSE
455 --DBMS_OUTPUT.PUT_LINE(CZ_UTILS.GET_TEXT('CZ_IMP_INVALID_TABLE_IMPORT','TABLENAME',table_name));
456 xERROR:=cz_utils.log_report(CZ_UTILS.GET_TEXT('CZ_IMP_INVALID_TABLE_IMPORT','TABLENAME',table_name),1,'CZ_IMP_ALL.POPULATE_TABLE',11276,inRun_ID);
457 END IF;
458 --DBMS_OUTPUT.PUT_LINE('INSERTS: '||to_char(Inserts));
459 --DBMS_OUTPUT.PUT_LINE('UPDATES: '||to_char(Updates));
460 --DBMS_OUTPUT.PUT_LINE('FAILED: '||to_char(x_failed));
461 --DBMS_OUTPUT.PUT_LINE('DUPLICATES: '||to_char(Dups));
462 EXCEPTION
463 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
464 RAISE;
465 WHEN OTHERS THEN
466 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED', 'ERRORTEXT', SQLERRM);
467 xERROR:=cz_utils.log_report(d_str,1,'CZ_IMP_ALL.POPULATE_TABLE',11276,inRun_ID);
468 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
469 END populate_table;
470 ------------------------------------------------------------------------------------------
471 PROCEDURE import_before_start IS
472 CURSOR C_GET_ID(nPriceGroupID number) IS
473 SELECT substr(ORIG_SYS_REF,1,instr(ORIG_SYS_REF,'.')-1)
474 FROM CZ_PRICE_GROUPS
475 WHERE to_number(substr(ORIG_SYS_REF,instr(ORIG_SYS_REF,'.')+1))=nPriceGroupID
476 AND instr(ORIG_SYS_REF,'.')<>0;
477 CURSOR C_SET_ID IS
478 SELECT PRICE_GROUP_ID FROM CZ_PRICE_GROUPS
479 WHERE ORIG_SYS_REF IS NULL FOR UPDATE;
480
481 nPriceGroupID CZ_PRICE_GROUPS.PRICE_GROUP_ID%TYPE;
482 sOrigSysRef CZ_PRICE_GROUPS.ORIG_SYS_REF%TYPE;
483 bIdFound BOOLEAN := FALSE;
484 xERROR BOOLEAN := FALSE;
485
486 BEGIN
487 OPEN C_SET_ID;
488 LOOP
489 FETCH C_SET_ID INTO nPriceGroupID;
490 EXIT WHEN C_SET_ID%NOTFOUND;
491
492 OPEN C_GET_ID(nPriceGroupID);
493 FETCH C_GET_ID INTO sOrigSysRef;
494 bIdFound := C_GET_ID%FOUND;
495 CLOSE C_GET_ID;
496
497 IF(bIdFound) THEN
498 BEGIN
499 UPDATE CZ_PRICE_GROUPS SET ORIG_SYS_REF=sOrigSysRef WHERE CURRENT OF C_SET_ID;
500 EXCEPTION
501 WHEN OTHERS THEN
502 xERROR:=cz_utils.log_report(SQLERRM,1,'CZ_IMP_ALL.IMPORT_BEFORE_START',11276);
503 END;
504 END IF;
505
506 END LOOP;
507 CLOSE C_SET_ID;
508 COMMIT;
509 END import_before_start;
510 ------------------------------------------------------------------------------------------
511 PROCEDURE import_after_complete(inRUN_ID IN PLS_INTEGER) IS
512 CURSOR C_GET_RELATED(sOrigSysRef CZ_PRICE_GROUPS.ORIG_SYS_REF%TYPE) IS
513 SELECT PRICE_GROUP_ID FROM CZ_PRICE_GROUPS
514 WHERE instr(ORIG_SYS_REF,'.')<>0 AND
515 substr(ORIG_SYS_REF,1,instr(ORIG_SYS_REF,'.')-1)=sOrigSysRef;
516 CURSOR C_GET_HOST IS
517 SELECT * FROM CZ_PRICE_GROUPS
518 WHERE ORIG_SYS_REF IS NOT NULL AND instr(ORIG_SYS_REF,'.')=0
519 FOR UPDATE;
520 P_GET_HOST C_GET_HOST%ROWTYPE;
521
522 nPriceGroupID CZ_PRICE_GROUPS.PRICE_GROUP_ID%TYPE;
523 bRelatedFound BOOLEAN := FALSE;
524 xERROR BOOLEAN := FALSE;
525 nAllocateBlock PLS_INTEGER:=1;
526 nAllocateCounter PLS_INTEGER;
527 nNextValue NUMBER;
528
529 v_settings_id VARCHAR2(40);
530 v_section_name VARCHAR2(30);
531
532 BEGIN
533
534 declare
535
536 cursor c_listcontrol is
537 select price_list_id from cz_xfr_price_lists
538 where deleted_flag='0';
539 cursor c_pricegroup(nPriceListId number) is
540 select desc_text from cz_price_groups
541 where orig_sys_ref=to_char(nPriceListId)
542 and deleted_flag='0';
543
544 nPriceListId cz_xfr_price_lists.price_list_id%type;
545 sDescription cz_price_groups.desc_text%type;
546 sDisabled cz_xfr_tables.disabled%type;
547 xERROR BOOLEAN := FALSE;
548
549 begin
550
551 begin
552 select disabled into sDisabled
553 from cz_xfr_tables where dst_table='CZ_PRICE_GROUPS' and xfr_group='IMPORT';
554 exception
555 when others then
556 sDisabled:='1';
557 end;
558
559 if(sDisabled='0')then
560 open c_listcontrol;
561 loop
562
563 fetch c_listcontrol into nPriceListId;
564 exit when c_listcontrol%notfound;
565
566 open c_pricegroup(nPriceListId);
567 fetch c_pricegroup into sDescription;
568 begin
569 if(c_pricegroup%found)then
570 update cz_xfr_price_lists set
571 description=sDescription,
572 last_import_run_id=inRUN_ID,
573 last_import_date=sysdate
574 where
575 price_list_id=nPriceListId;
576 else
577 update cz_xfr_price_lists set
578 source_price_deleted='1'
579 where
580 price_list_id=nPriceListId;
581 end if;
582 exception
583 when others then
584 xERROR:=cz_utils.log_report(SQLERRM,1,'CZ_IMP_ALL.IMPORT_AFTER_COMPLETE',11276,inRUN_ID);
585 end;
586 close c_pricegroup;
587 end loop;
588 close c_listcontrol;
589 commit;
590 end if;
591 end;
592
593 OPEN C_GET_HOST;
594
595 v_settings_id := 'OracleSequenceIncr';
596 v_section_name := 'SCHEMA';
597
598 BEGIN
599 SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
600 WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
601 EXCEPTION
602 WHEN OTHERS THEN
603 nAllocateBlock:=1;
604 END;
605 nAllocateCounter:=nAllocateBlock-1;
606
607 LOOP
608 FETCH C_GET_HOST INTO P_GET_HOST;
609 EXIT WHEN C_GET_HOST%NOTFOUND;
610
611 OPEN C_GET_RELATED(P_GET_HOST.ORIG_SYS_REF);
612 FETCH C_GET_RELATED INTO nPriceGroupID;
613 bRelatedFound := C_GET_RELATED%FOUND;
614 CLOSE C_GET_RELATED;
615
616 IF(bRelatedFound)THEN
617 BEGIN
618 UPDATE CZ_PRICE_GROUPS SET
619 DESC_TEXT=P_GET_HOST.DESC_TEXT,
620 CURRENCY=P_GET_HOST.CURRENCY,
621 NAME=P_GET_HOST.NAME,
622 USER_NUM01=P_GET_HOST.USER_NUM01,
623 USER_NUM02=P_GET_HOST.USER_NUM02,
624 USER_NUM03=P_GET_HOST.USER_NUM03,
625 USER_NUM04=P_GET_HOST.USER_NUM04,
626 USER_STR01=P_GET_HOST.USER_STR01,
627 USER_STR02=P_GET_HOST.USER_STR02,
628 USER_STR03=P_GET_HOST.USER_STR03,
629 USER_STR04=P_GET_HOST.USER_STR04,
630 CREATION_DATE=P_GET_HOST.CREATION_DATE,
631 LAST_UPDATE_DATE=P_GET_HOST.LAST_UPDATE_DATE,
632 DELETED_FLAG=P_GET_HOST.DELETED_FLAG,
633 CREATED_BY=P_GET_HOST.CREATED_BY,
634 LAST_UPDATED_BY=P_GET_HOST.LAST_UPDATED_BY,
635 SECURITY_MASK=P_GET_HOST.SECURITY_MASK,
636 CHECKOUT_USER=P_GET_HOST.CHECKOUT_USER
637 WHERE PRICE_GROUP_ID=nPriceGroupID;
638 UPDATE CZ_PRICE_GROUPS SET ORIG_SYS_REF=NULL
639 WHERE CURRENT OF C_GET_HOST;
640 EXCEPTION
641 WHEN OTHERS THEN
642 xERROR:=cz_utils.log_report(SQLERRM,1,'CZ_IMP_ALL.IMPORT_AFTER_COMPLETE',11276,inRUN_ID);
643 END;
644 ELSE
645 BEGIN
646 nAllocateCounter:=nAllocateCounter+1;
647 IF(nAllocateCounter=nAllocateBlock)THEN
648 nAllocateCounter:=0;
649 SELECT CZ_PRICE_GROUPS_S.NEXTVAL INTO nNextValue FROM DUAL;
650 END IF;
651 INSERT INTO CZ_PRICE_GROUPS
652 (PRICE_GROUP_ID,DESC_TEXT,CURRENCY,ORIG_SYS_REF,
653 NAME,USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,
654 USER_STR01,USER_STR02,USER_STR03,USER_STR04,DELETED_FLAG,
655 SECURITY_MASK,CHECKOUT_USER,
656 CREATION_DATE,LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY)
657 VALUES
658 (nNextValue+nAllocateCounter,P_GET_HOST.DESC_TEXT,P_GET_HOST.CURRENCY,
659 P_GET_HOST.ORIG_SYS_REF||'.'||P_GET_HOST.PRICE_GROUP_ID,P_GET_HOST.NAME,
660 P_GET_HOST.USER_NUM01,P_GET_HOST.USER_NUM02,
661 P_GET_HOST.USER_NUM03,P_GET_HOST.USER_NUM04,
662 P_GET_HOST.USER_STR01,P_GET_HOST.USER_STR02,
663 P_GET_HOST.USER_STR03,P_GET_HOST.USER_STR04,P_GET_HOST.DELETED_FLAG,
664 P_GET_HOST.SECURITY_MASK,
665 P_GET_HOST.CHECKOUT_USER,
666 SYSDATE,SYSDATE,1,1);
667 UPDATE CZ_PRICE_GROUPS SET ORIG_SYS_REF=NULL
668 WHERE CURRENT OF C_GET_HOST;
669 EXCEPTION
670 WHEN OTHERS THEN
671 xERROR:=cz_utils.log_report(SQLERRM,1,'CZ_IMP_ALL.IMPORT_AFTER_COMPLETE',11276,inRUN_ID);
672 END;
673 END IF;
674
675 END LOOP;
676 CLOSE C_GET_HOST;
677 COMMIT;
678
679 END import_after_complete;
680 ------------------------------------------------------------------------------------------
681 PROCEDURE goSingleBill(nOrg_ID IN NUMBER,nTop_ID IN NUMBER,
682 COPY_CHILD_MODELS IN VARCHAR2,
683 REFRESH_MODEL_ID IN NUMBER,
684 COPY_ROOT_MODEL IN VARCHAR2,
685 x_run_id OUT NOCOPY NUMBER) IS -- sselahi: added x_run_id
686 xERROR BOOLEAN:=FALSE;
687 imp_st number;
688 imp_end number;
689 d_str varchar2(255);
690 nLogTime BOOLEAN := FALSE;
691 nEnableTrace BOOLEAN := FALSE;
692 BEGIN
693
694 nLogTime := isTimingLogEnabled;
695 nEnableTrace := isTracingEnabled;
696
697 if (nLogTime) then
698 get_time := TRUE;
699 end if;
700
701 if (get_time) then
702 imp_st := dbms_utility.get_time();
703 end if;
704
705 CZ_IMP_SINGLE.ImportSingleBill(nOrg_ID,nTop_ID,COPY_CHILD_MODELS,REFRESH_MODEL_ID,'0', 'OPTIONAL', sysdate, x_run_id); -- sselahi:added x_run_id
706 if (get_time) then
707 imp_end := dbms_utility.get_time();
708 d_str := 'Import (' || nTop_Id || ') :' || (imp_end-imp_st)/100.00;
709 xERROR:=cz_utils.log_report(d_str,1,'IMPORT',11299,NULL);
710 end if;
711 EXCEPTION
712 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
713 RAISE;
714 WHEN OTHERS THEN
715 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
716 xERROR:=cz_utils.log_report(d_str,1,'CZ_IMP_ALL.goSingleBill',11276,NULL);
717 RAISE;
718 END goSingleBill;
719 ------------------------------------------------------------------------------------------
720 PROCEDURE AddBillToImport(errbuf OUT NOCOPY VARCHAR2,retcode OUT NOCOPY NUMBER,nOrg_ID IN NUMBER,nTop_ID IN NUMBER,
721 COPY_CHILD_MODELS IN VARCHAR2) IS
722 xERROR BOOLEAN:=FALSE;
723 server_id cz_servers.server_local_id%TYPE;
724 v_enabled VARCHAR2(1) := '1';
725
726 BEGIN
727 retcode:=0;
728 errbuf:='';
729
730 BEGIN
731 SELECT server_local_id INTO server_id
732 FROM cz_servers
733 WHERE import_enabled = v_enabled;
734 EXCEPTION
735 WHEN TOO_MANY_ROWS THEN
736 retcode:=2;
737 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_TOO_MANY_SERVERS');
738 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.ADDBILLTOIMPORT',11276,NULL);
739 RAISE;
740 WHEN NO_DATA_FOUND THEN
741 retcode:=2;
742 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_NO_SERVERS');
743 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.ADDBILLTOIMPORT',11276,NULL);
744 RAISE;
745 END;
746
747 insert into cz_xfr_project_bills (top_item_id,organization_id,deleted_flag,explosion_type,model_ps_node_id,
748 copy_addl_child_models,source_server)
749 select nTop_ID,nOrg_ID,'0','OPTIONAL',cz_xfr_project_bills_s.NEXTVAL,COPY_CHILD_MODELS,server_id
750 from dual where not exists
751 (select 1 from cz_xfr_project_bills where organization_id=nOrg_ID and
752 top_item_id=nTop_ID and explosion_type='OPTIONAL' and source_server = server_id);
753 update cz_xfr_project_bills set deleted_flag='0',copy_addl_child_models = COPY_CHILD_MODELS
754 where organization_id=nOrg_ID and
755 top_item_id=nTop_ID and explosion_type='OPTIONAL' and source_server = server_id;
756 COMMIT;
757 EXCEPTION
758 WHEN OTHERS THEN
759 retcode:=2;
760 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
761 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.ADDBILLTOIMPORT',11276,NULL);
762 RAISE;
763 END;
764 ------------------------------------------------------------------------------------------
765 PROCEDURE SetSingleBillState(errbuf OUT NOCOPY VARCHAR2,retcode OUT NOCOPY NUMBER,nOrg_ID IN NUMBER,nTop_ID IN NUMBER,sState IN VARCHAR2) IS
766 xERROR BOOLEAN:=FALSE;
767 server_id cz_servers.server_local_id%TYPE;
768 v_enabled VARCHAR2(1) := '1';
769
770 BEGIN
771 retcode:=0;
772 errbuf:='';
773
774 BEGIN
775 SELECT server_local_id INTO server_id
776 FROM cz_servers
777 WHERE import_enabled = v_enabled;
778 EXCEPTION
779 WHEN TOO_MANY_ROWS THEN
780 retcode:=2;
781 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_TOO_MANY_SERVERS');
782 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.SETSINGLEBILLSTATE',11276,NULL);
783 RAISE;
784 WHEN NO_DATA_FOUND THEN
785 retcode:=2;
786 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_NO_SERVERS');
787 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.SETSINGLEBILLSTATE',11276,NULL);
788 RAISE;
789 END;
790
791 update cz_xfr_project_bills set
792 deleted_flag=DECODE(UPPER(sState),'0','1','OFF','1','NO','1','DISABLE','1',
793 '1','0','ON','0','YES','0','ENABLE','0',DELETED_FLAG)
794 where organization_id=nOrg_ID and top_item_id=nTop_ID and explosion_type='OPTIONAL' AND
795 source_server = server_id;
796 COMMIT;
797 EXCEPTION
798 WHEN OTHERS THEN
799 retcode:=2;
800 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
801 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.SETSINGLEBILLSTATE',11276,NULL);
802 END;
803 ------------------------------------------------------------------------------------------
804 PROCEDURE RemoveModel(errbuf OUT NOCOPY VARCHAR2,retcode OUT NOCOPY NUMBER,sOrg_ID IN VARCHAR2,
805 dsOrg_ID IN VARCHAR2,sTop_ID IN VARCHAR2) IS
806
807 xERROR BOOLEAN:=FALSE;
808 nTop_ID CZ_XFR_PROJECT_BILLS.TOP_ITEM_ID%TYPE;
809 nOrg_ID CZ_XFR_PROJECT_BILLS.ORGANIZATION_ID%TYPE;
810 server_id cz_servers.server_local_id%TYPE;
811 v_enabled VARCHAR2(1) := '1';
812
813 BEGIN
814 retcode:=0;
815 errbuf:='';
816
817 BEGIN
818 SELECT server_local_id INTO server_id
819 FROM cz_servers
820 WHERE import_enabled = v_enabled;
821 EXCEPTION
822 WHEN TOO_MANY_ROWS THEN
823 retcode:=2;
824 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_TOO_MANY_SERVERS');
825 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.RemoveModel',11276,NULL);
826 RAISE;
827 WHEN NO_DATA_FOUND THEN
828 retcode:=2;
829 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_NO_SERVERS');
830 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.RemoveModel',11276,NULL);
831 RAISE;
832 END;
833
834 SELECT ORGANIZATION_ID INTO nOrg_ID FROM CZ_EXV_ORGANIZATIONS
835 WHERE ORGANIZATION_CODE = sOrg_ID;
836
837 SELECT inventory_item_id INTO nTop_ID
838 FROM cz_exv_mtl_system_items
839 WHERE bom_item_type = 1
840 AND organization_id = nOrg_ID
841 AND concatenated_segments = sTop_ID
842 AND rownum = 1;
843
844 update cz_xfr_project_bills set
845 deleted_flag='1'
846 where organization_id=nOrg_ID and top_item_id=nTop_ID
847 and source_server = server_id;
848
849 COMMIT;
850 EXCEPTION
851 WHEN OTHERS THEN
852 retcode:=2;
853 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
854 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.RemoveModel',11276,NULL);
855 END;
856 ------------------------------------------------------------------------------------------
857 PROCEDURE PopulateModels_cp(errbuf OUT NOCOPY VARCHAR2,retcode OUT NOCOPY NUMBER,
858 sOrg_ID IN VARCHAR2,dsOrg_ID IN VARCHAR2,
859 sFrom IN VARCHAR2,sTo IN VARCHAR,
860 COPY_CHILD_MODELS IN VARCHAR2 DEFAULT '0') IS
861 CURSOR C_GETMODELS(Org_ID NUMBER) IS
862 SELECT A.ASSEMBLY_ITEM_ID FROM CZ_EXV_BILL_OF_MATERIALS A,CZ_EXV_MTL_SYSTEM_ITEMS B
863 WHERE A.ASSEMBLY_ITEM_ID=B.INVENTORY_ITEM_ID
864 AND A.ORGANIZATION_ID=B.ORGANIZATION_ID
865 AND B.BOM_ITEM_TYPE=1
866 AND A.ORGANIZATION_ID=Org_ID
867 AND B.CONCATENATED_SEGMENTS BETWEEN sFrom AND NVL(sTo,sFrom);
868
869 xERROR BOOLEAN:=FALSE;
870 nTop_ID CZ_XFR_PROJECT_BILLS.TOP_ITEM_ID%TYPE;
871 nOrg_ID CZ_XFR_PROJECT_BILLS.ORGANIZATION_ID%TYPE;
872 v_copy_child_models CHAR(1);
873 l_run_id NUMBER; -- sselahi: added to pass to call goSingleBill
874 BEGIN
875
876 CZ_ORAAPPS_INTEGRATE.mERRBUF := '';
877 CZ_ORAAPPS_INTEGRATE.mRETCODE := 0;
878
879 SELECT DECODE(COPY_CHILD_MODELS,'Y','0','N','1','YES','0','NO','1','TRUE','0','FALSE','1','T','0','F','1','1','1','0') into v_copy_child_models from dual;
880
881 SELECT ORGANIZATION_ID INTO nOrg_ID FROM CZ_EXV_ORGANIZATIONS
882 WHERE ORGANIZATION_CODE = sOrg_ID;
883
884 OPEN C_GETMODELS(nOrg_ID);
885 LOOP
886 FETCH C_GETMODELS INTO nTop_ID;
887 EXIT WHEN C_GETMODELS%NOTFOUND;
888
889 /* Log a message if the bill being imported (not refreshed) refers to a common bill */
890 check_for_common_bill(errbuf,retcode,nORG_ID,nTOP_ID);
891 goSingleBill(nOrg_ID,nTop_ID,v_COPY_CHILD_MODELS,-1, '0',l_run_id); -- sselahi: added l_run_id
892
893 IF(CZ_ORAAPPS_INTEGRATE.mRETCODE = 2)THEN
894 errbuf := CZ_ORAAPPS_INTEGRATE.mERRBUF;
895 retcode := CZ_ORAAPPS_INTEGRATE.mRETCODE;
896 RETURN;
897 END IF;
898
899 END LOOP;
900 CLOSE C_GETMODELS;
901 COMMIT;
902
903 errbuf := CZ_ORAAPPS_INTEGRATE.mERRBUF;
904 retcode := CZ_ORAAPPS_INTEGRATE.mRETCODE;
905
906 EXCEPTION
907 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
908 RAISE;
909 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
910 retcode:=2;
911 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_ACTIVE_SESSION_EXISTS');
912 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.PopulateModels_cp',11276,NULL);
913 RAISE;
914 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
915 retcode:=2;
916 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED');
917 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.PopulateModels_cp',11276,NULL);
918 RAISE;
919 WHEN OTHERS THEN
920 retcode:=2;
921 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
922 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.PopulateModels_cp',11276,NULL);
923 RAISE;
924 END;
925 ------------------------------------------------------------------------------------------
926 PROCEDURE RefreshModels(errbuf OUT NOCOPY VARCHAR2,retcode OUT NOCOPY NUMBER) IS
927 BEGIN
928 CZ_ORAAPPS_INTEGRATE.go_cp(errbuf, retcode);
929 END;
930 ------------------------------------------------------------------------------------------
931 PROCEDURE setRunID(inRun_ID IN PLS_INTEGER,table_name IN VARCHAR2) IS
932 DC_CURSOR INTEGER;
933 RESULT INTEGER;
934 BEGIN
935 DC_CURSOR:=DBMS_SQL.OPEN_CURSOR;
936 DBMS_SQL.PARSE(DC_CURSOR,'UPDATE '||table_name||' SET RUN_ID='||inRun_ID||
937 ',REC_STATUS=NULL,DISPOSITION=NULL WHERE RUN_ID IS NULL',DBMS_SQL.NATIVE);
938 RESULT:=DBMS_SQL.EXECUTE(DC_CURSOR);
939 DBMS_SQL.CLOSE_CURSOR(DC_CURSOR);
940 EXCEPTION
941 WHEN OTHERS THEN
942 DBMS_SQL.CLOSE_CURSOR(DC_CURSOR);
943 END;
944 ------------------------------------------------------------------------------------------
945 PROCEDURE setRecStatus(inRun_ID IN PLS_INTEGER,table_name IN VARCHAR2) IS
946 DC_CURSOR INTEGER;
947 RESULT INTEGER;
948 BEGIN
949 DC_CURSOR:=DBMS_SQL.OPEN_CURSOR;
950 DBMS_SQL.PARSE(DC_CURSOR,'UPDATE '||table_name||
951 ' SET REC_STATUS=NULL,DISPOSITION=NULL WHERE RUN_ID='||inRun_ID,DBMS_SQL.NATIVE);
952 RESULT:=DBMS_SQL.EXECUTE(DC_CURSOR);
953 DBMS_SQL.CLOSE_CURSOR(DC_CURSOR);
954 EXCEPTION
955 WHEN OTHERS THEN
956 DBMS_SQL.CLOSE_CURSOR(DC_CURSOR);
957 END;
958 ------------------------------------------------------------------------------------------
959
960 PROCEDURE check_for_common_bill
961 (errbuf OUT NOCOPY VARCHAR2,
962 retcode OUT NOCOPY NUMBER,
963 nORG_ID IN NUMBER,
964 nTOP_ID IN NUMBER)
965 IS
966 commonInvId CZ_XFR_PROJECT_BILLS.TOP_ITEM_ID%TYPE;
967 commonOrgId CZ_XFR_PROJECT_BILLS.ORGANIZATION_ID%TYPE;
968 xERROR BOOLEAN:=FALSE;
969 BEGIN
970 errbuf:='';
971 retcode:=0;
972
973 /* Log a message if the bill being imported (not refreshed) refers to a common bill */
974 SELECT ORGANIZATION_ID, ASSEMBLY_ITEM_ID
975 INTO commonOrgId, commonInvId
976 FROM CZ_EXV_BILL_OF_MATERIALS
977 WHERE BILL_SEQUENCE_ID in
978 (SELECT COMMON_BILL_SEQUENCE_ID FROM CZ_EXV_BILL_OF_MATERIALS
979 WHERE ORGANIZATION_ID = nOrg_ID
980 AND ASSEMBLY_ITEM_ID = nTop_ID);
981 IF ((commonOrgId <> nOrg_ID) or (commonInvId <> nTop_ID)) THEN
982 retcode := 1;
983 errbuf := CZ_UTILS.get_text('CZ_HAS_COMMON_BILL','ORGID',nOrg_ID,'INVID',nTop_ID,'C_ORGID',commonOrgId,'C_INVID',commonInvId);
984 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL:CZ_COMMON_BILL_CHECK',11276,NULL);
985 END IF;
986 EXCEPTION
987 WHEN NO_DATA_FOUND THEN
988 retcode := 2;
989 errbuf := CZ_UTILS.get_text('CZ_IMP_BOM_NO_DATA');
990 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL:CZ_COMMON_BILL_CHECK',11276,NULL);
991 WHEN OTHERS THEN
992 retcode := 2;
993 errbuf := 'CZ_IMP_ALL:COMMON_BILL_CHECK' || SQLERRM;
994 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL:COMMON_BILL_CHECK',11276,NULL);
995 RAISE;
996 END check_for_common_bill;
997
998 ------------------------------------------------------------------------------------------
999 FUNCTION REPORT (Msg in VARCHAR2, Urgency in NUMBER, ByCaller in VARCHAR2,
1000 StatusCode in NUMBER) RETURN BOOLEAN IS
1001 xError Boolean;
1002 l_msg VARCHAR2(2000);
1003 BEGIN
1004 -- log msg to both fnd and cz tables
1005 xError := cz_utils.log_report(Msg, urgency, ByCaller, StatusCode);
1006 IF (xError) THEN
1007 commit;
1008 ELSE
1009 rollback;
1010 END IF;
1011
1012 RETURN xError;
1013 EXCEPTION
1014 WHEN OTHERS THEN
1015 ROLLBACK;
1016 RETURN FALSE;
1017 END REPORT;
1018 ------------------------------------------------------------------------------------------
1019 PROCEDURE go_generic_cp(errbuf OUT NOCOPY VARCHAR2,
1020 retcode OUT NOCOPY NUMBER,
1021 inRun_iD IN PLS_INTEGER,
1022 p_rp_folder_id IN NUMBER) IS
1023
1024 outRun_id PLS_INTEGER;
1025 xERROR BOOLEAN:=FALSE;
1026 l_success_msg VARCHAR2(255);
1027
1028 BEGIN
1029 go_generic(outRun_ID,inRun_ID, p_rp_folder_id);
1030 l_success_msg:=CZ_UTILS.get_text('CZ_IMP_GENIMP_SUCCESS_RUNID', 'OUT_RUN_ID', outRun_ID);
1031 EXCEPTION
1032 WHEN OTHERS THEN
1033 retcode:=2;
1034 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
1035 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.generic_import',11276,NULL);
1036 END go_generic_cp;
1037 ------------------------------------------------------------------------------------------
1038 END CZ_IMP_ALL;