[Home] [Help]
PACKAGE BODY: APPS.CZ_IMP_ALL
Source
1 PACKAGE BODY CZ_IMP_ALL AS
2 /* $Header: cziallb.pls 120.6.12020000.2 2012/08/17 14:51:40 smanna 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
69 IF(retcode > CZ_ORAAPPS_INTEGRATE.mRETCODE)THEN
66 ------------------------------------------------------------------------------------------
67 PROCEDURE setReturnCode(retcode IN NUMBER, errbuf IN VARCHAR2) IS
68 BEGIN
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;
204 outError_code NUMBER;
201 bAutoCreateUsers CZ_DB_SETTINGS.VALUE%TYPE:='NO';
202 bRunExploder CZ_DB_SETTINGS.VALUE%TYPE:='NO';
203 outGrp_ID 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'
342 ----5) and create database users if necessary
339 WHERE RUN_ID=genRun_ID;
340 COMMIT;
341
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 NoChange PLS_INTEGER;
396 d_str varchar2(255);
397 l_CONFIG_ENGINE_TYPE VARCHAR2(10); --LA 12548898
398 BEGIN
399 --DBMS_OUTPUT.ENABLE;
400 --DBMS_OUTPUT.PUT_LINE('IMPORTING TABLE: ' || lower_table_name);
401 IF(lower_table_name='cz_item_masters') THEN
402 CZ_IMP_IM_MAIN.MAIN_ITEM_MASTER(inRun_ID, commit_size, max_err,
403 Inserts, Updates, x_failed, Dups, NoChange,inXFR_GROUP);
404 ELSIF(lower_table_name='cz_ps_nodes') THEN
405 CZ_IMP_PS_NODE.MAIN_PS_NODE(inRun_ID, commit_size, max_err,
406 Inserts, Updates, x_failed, Dups,NoChange, inXFR_GROUP);
407 ELSIF(lower_table_name='cz_customers') THEN
408 CZ_IMP_AC_MAIN.MAIN_CUSTOMER(inRun_ID, commit_size, max_err,
409 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
410 ELSIF(lower_table_name='cz_customer_end_users') THEN
411 CZ_IMP_AC_MAIN.MAIN_CUSTOMER_END_USER(inRun_ID, commit_size, max_err,
412 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
413 ELSIF(lower_table_name='cz_addresses') THEN
414 CZ_IMP_AC_MAIN.MAIN_ADDRESS(inRun_ID, commit_size, max_err,
415 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
416 ELSIF(lower_table_name='cz_address_uses') THEN
417 CZ_IMP_AC_MAIN.MAIN_ADDRESS_USES(inRun_ID, commit_size, max_err,
418 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
419 ELSIF(lower_table_name='cz_contacts') THEN
420 CZ_IMP_AC_MAIN.MAIN_CONTACT(inRun_ID, commit_size, max_err,
421 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
422 ELSIF(lower_table_name='cz_prices') THEN
423 CZ_IMP_PR_MAIN.MAIN_PRICE(inRun_ID, commit_size, max_err,
424 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
425 ELSIF(lower_table_name='cz_price_groups') THEN
426 CZ_IMP_PR_MAIN.MAIN_PRICE_GROUP(inRun_ID, commit_size, max_err,
427 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
428 ELSIF(lower_table_name='cz_localized_texts') THEN
429 CZ_IMP_PS_NODE.MAIN_INTL_TEXT(inRun_ID, commit_size, max_err,
430 Inserts, Updates, x_failed, Dups, NoChange, inXFR_GROUP);
431 ELSIF(lower_table_name='cz_devl_projects') THEN
432 --LA 12548898
433 l_CONFIG_ENGINE_TYPE := FND_PROFILE.VALUE('CZ_CONFIG_ENGINE_NEW_MODELS');
434 CZ_IMP_PS_NODE.MAIN_DEVL_PROJECT(inRun_ID, commit_size, max_err,
435 Inserts, Updates, x_failed, Dups, NoChange, inXFR_GROUP, p_rp_folder_id,l_CONFIG_ENGINE_TYPE); -- sselahi rpf
436 ELSIF(lower_table_name='cz_end_users') THEN
437 CZ_IMP_AC_MAIN.MAIN_END_USER(inRun_ID, commit_size, max_err,
438 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
439 ELSIF(lower_table_name='cz_end_user_groups') THEN
440 CZ_IMP_AC_MAIN.MAIN_END_USER_GROUP(inRun_ID, commit_size, max_err,
441 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
442 ELSIF(lower_table_name='cz_item_property_values') THEN
443 CZ_IMP_IM_MAIN.MAIN_ITEM_PROPERTY_VALUE(inRun_ID, commit_size, max_err,
444 Inserts, Updates, x_failed, Dups, NoChange,inXFR_GROUP);
445 ELSIF(lower_table_name='cz_item_types') THEN
446 CZ_IMP_IM_MAIN.MAIN_ITEM_TYPE(inRun_ID, commit_size, max_err,
447 Inserts, Updates, x_failed, Dups, NoChange,inXFR_GROUP);
448 ELSIF(lower_table_name='cz_item_type_properties') THEN
452 CZ_IMP_IM_MAIN.MAIN_PROPERTY(inRun_ID, commit_size, max_err,
449 CZ_IMP_IM_MAIN.MAIN_ITEM_TYPE_PROPERTY(inRun_ID, commit_size, max_err,
450 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
451 ELSIF(lower_table_name='cz_properties') THEN
453 Inserts, Updates, x_failed, Dups,NoChange, inXFR_GROUP, p_rp_folder_id);
454 ELSIF(lower_table_name='cz_user_groups') THEN
455 CZ_IMP_AC_MAIN.MAIN_USER_GROUP(inRun_ID, commit_size, max_err,
456 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
457 ELSE
458 --DBMS_OUTPUT.PUT_LINE(CZ_UTILS.GET_TEXT('CZ_IMP_INVALID_TABLE_IMPORT','TABLENAME',table_name));
459 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);
460 END IF;
461 --DBMS_OUTPUT.PUT_LINE('INSERTS: '||to_char(Inserts));
462 --DBMS_OUTPUT.PUT_LINE('UPDATES: '||to_char(Updates));
463 --DBMS_OUTPUT.PUT_LINE('FAILED: '||to_char(x_failed));
464 --DBMS_OUTPUT.PUT_LINE('DUPLICATES: '||to_char(Dups));
465 EXCEPTION
466 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
467 RAISE;
468 WHEN OTHERS THEN
469 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED', 'ERRORTEXT', SQLERRM);
470 xERROR:=cz_utils.log_report(d_str,1,'CZ_IMP_ALL.POPULATE_TABLE',11276,inRun_ID);
471 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
472 END populate_table;
473 ------------------------------------------------------------------------------------------
474 PROCEDURE import_before_start IS
475 CURSOR C_GET_ID(nPriceGroupID number) IS
476 SELECT substr(ORIG_SYS_REF,1,instr(ORIG_SYS_REF,'.')-1)
477 FROM CZ_PRICE_GROUPS
478 WHERE to_number(substr(ORIG_SYS_REF,instr(ORIG_SYS_REF,'.')+1))=nPriceGroupID
479 AND instr(ORIG_SYS_REF,'.')<>0;
480 CURSOR C_SET_ID IS
481 SELECT PRICE_GROUP_ID FROM CZ_PRICE_GROUPS
482 WHERE ORIG_SYS_REF IS NULL FOR UPDATE;
483
484 nPriceGroupID CZ_PRICE_GROUPS.PRICE_GROUP_ID%TYPE;
485 sOrigSysRef CZ_PRICE_GROUPS.ORIG_SYS_REF%TYPE;
486 bIdFound BOOLEAN := FALSE;
487 xERROR BOOLEAN := FALSE;
488
489 BEGIN
490 OPEN C_SET_ID;
491 LOOP
492 FETCH C_SET_ID INTO nPriceGroupID;
493 EXIT WHEN C_SET_ID%NOTFOUND;
494
495 OPEN C_GET_ID(nPriceGroupID);
496 FETCH C_GET_ID INTO sOrigSysRef;
497 bIdFound := C_GET_ID%FOUND;
498 CLOSE C_GET_ID;
499
500 IF(bIdFound) THEN
501 BEGIN
502 UPDATE CZ_PRICE_GROUPS SET ORIG_SYS_REF=sOrigSysRef WHERE CURRENT OF C_SET_ID;
503 EXCEPTION
504 WHEN OTHERS THEN
505 xERROR:=cz_utils.log_report(SQLERRM,1,'CZ_IMP_ALL.IMPORT_BEFORE_START',11276);
506 END;
507 END IF;
508
509 END LOOP;
510 CLOSE C_SET_ID;
511 COMMIT;
512 END import_before_start;
513 ------------------------------------------------------------------------------------------
514 PROCEDURE import_after_complete(inRUN_ID IN PLS_INTEGER) IS
515 CURSOR C_GET_RELATED(sOrigSysRef CZ_PRICE_GROUPS.ORIG_SYS_REF%TYPE) IS
516 SELECT PRICE_GROUP_ID FROM CZ_PRICE_GROUPS
517 WHERE instr(ORIG_SYS_REF,'.')<>0 AND
518 substr(ORIG_SYS_REF,1,instr(ORIG_SYS_REF,'.')-1)=sOrigSysRef;
519 CURSOR C_GET_HOST IS
520 SELECT * FROM CZ_PRICE_GROUPS
521 WHERE ORIG_SYS_REF IS NOT NULL AND instr(ORIG_SYS_REF,'.')=0
522 FOR UPDATE;
523 P_GET_HOST C_GET_HOST%ROWTYPE;
524
525 nPriceGroupID CZ_PRICE_GROUPS.PRICE_GROUP_ID%TYPE;
526 bRelatedFound BOOLEAN := FALSE;
527 xERROR BOOLEAN := FALSE;
528 nAllocateBlock PLS_INTEGER:=1;
529 nAllocateCounter PLS_INTEGER;
530 nNextValue NUMBER;
531
532 v_settings_id VARCHAR2(40);
533 v_section_name VARCHAR2(30);
534
535 BEGIN
536
537 declare
538
539 cursor c_listcontrol is
540 select price_list_id from cz_xfr_price_lists
541 where deleted_flag='0';
542 cursor c_pricegroup(nPriceListId number) is
543 select desc_text from cz_price_groups
544 where orig_sys_ref=to_char(nPriceListId)
545 and deleted_flag='0';
546
547 nPriceListId cz_xfr_price_lists.price_list_id%type;
548 sDescription cz_price_groups.desc_text%type;
549 sDisabled cz_xfr_tables.disabled%type;
550 xERROR BOOLEAN := FALSE;
551
552 begin
553
554 begin
555 select disabled into sDisabled
556 from cz_xfr_tables where dst_table='CZ_PRICE_GROUPS' and xfr_group='IMPORT';
557 exception
558 when others then
559 sDisabled:='1';
560 end;
561
562 if(sDisabled='0')then
563 open c_listcontrol;
564 loop
565
566 fetch c_listcontrol into nPriceListId;
567 exit when c_listcontrol%notfound;
568
569 open c_pricegroup(nPriceListId);
570 fetch c_pricegroup into sDescription;
571 begin
572 if(c_pricegroup%found)then
573 update cz_xfr_price_lists set
574 description=sDescription,
575 last_import_run_id=inRUN_ID,
576 last_import_date=sysdate
577 where
578 price_list_id=nPriceListId;
579 else
580 update cz_xfr_price_lists set
581 source_price_deleted='1'
582 where
583 price_list_id=nPriceListId;
584 end if;
585 exception
586 when others then
587 xERROR:=cz_utils.log_report(SQLERRM,1,'CZ_IMP_ALL.IMPORT_AFTER_COMPLETE',11276,inRUN_ID);
588 end;
589 close c_pricegroup;
590 end loop;
591 close c_listcontrol;
592 commit;
593 end if;
594 end;
595
596 OPEN C_GET_HOST;
597
601 BEGIN
598 v_settings_id := 'OracleSequenceIncr';
599 v_section_name := 'SCHEMA';
600
602 SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
603 WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
604 EXCEPTION
605 WHEN OTHERS THEN
606 nAllocateBlock:=1;
607 END;
608 nAllocateCounter:=nAllocateBlock-1;
609
610 LOOP
611 FETCH C_GET_HOST INTO P_GET_HOST;
612 EXIT WHEN C_GET_HOST%NOTFOUND;
613
614 OPEN C_GET_RELATED(P_GET_HOST.ORIG_SYS_REF);
615 FETCH C_GET_RELATED INTO nPriceGroupID;
616 bRelatedFound := C_GET_RELATED%FOUND;
617 CLOSE C_GET_RELATED;
618
619 IF(bRelatedFound)THEN
620 BEGIN
621 UPDATE CZ_PRICE_GROUPS SET
622 DESC_TEXT=P_GET_HOST.DESC_TEXT,
623 CURRENCY=P_GET_HOST.CURRENCY,
624 NAME=P_GET_HOST.NAME,
625 USER_NUM01=P_GET_HOST.USER_NUM01,
626 USER_NUM02=P_GET_HOST.USER_NUM02,
627 USER_NUM03=P_GET_HOST.USER_NUM03,
628 USER_NUM04=P_GET_HOST.USER_NUM04,
629 USER_STR01=P_GET_HOST.USER_STR01,
630 USER_STR02=P_GET_HOST.USER_STR02,
631 USER_STR03=P_GET_HOST.USER_STR03,
632 USER_STR04=P_GET_HOST.USER_STR04,
633 CREATION_DATE=P_GET_HOST.CREATION_DATE,
634 LAST_UPDATE_DATE=P_GET_HOST.LAST_UPDATE_DATE,
635 DELETED_FLAG=P_GET_HOST.DELETED_FLAG,
636 CREATED_BY=P_GET_HOST.CREATED_BY,
637 LAST_UPDATED_BY=P_GET_HOST.LAST_UPDATED_BY,
638 SECURITY_MASK=P_GET_HOST.SECURITY_MASK,
639 CHECKOUT_USER=P_GET_HOST.CHECKOUT_USER
640 WHERE PRICE_GROUP_ID=nPriceGroupID;
641 UPDATE CZ_PRICE_GROUPS SET ORIG_SYS_REF=NULL
642 WHERE CURRENT OF C_GET_HOST;
643 EXCEPTION
644 WHEN OTHERS THEN
645 xERROR:=cz_utils.log_report(SQLERRM,1,'CZ_IMP_ALL.IMPORT_AFTER_COMPLETE',11276,inRUN_ID);
646 END;
647 ELSE
648 BEGIN
649 nAllocateCounter:=nAllocateCounter+1;
650 IF(nAllocateCounter=nAllocateBlock)THEN
651 nAllocateCounter:=0;
652 SELECT CZ_PRICE_GROUPS_S.NEXTVAL INTO nNextValue FROM DUAL;
653 END IF;
654 INSERT INTO CZ_PRICE_GROUPS
655 (PRICE_GROUP_ID,DESC_TEXT,CURRENCY,ORIG_SYS_REF,
656 NAME,USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,
657 USER_STR01,USER_STR02,USER_STR03,USER_STR04,DELETED_FLAG,
658 SECURITY_MASK,CHECKOUT_USER,
659 CREATION_DATE,LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY)
660 VALUES
661 (nNextValue+nAllocateCounter,P_GET_HOST.DESC_TEXT,P_GET_HOST.CURRENCY,
662 P_GET_HOST.ORIG_SYS_REF||'.'||P_GET_HOST.PRICE_GROUP_ID,P_GET_HOST.NAME,
663 P_GET_HOST.USER_NUM01,P_GET_HOST.USER_NUM02,
664 P_GET_HOST.USER_NUM03,P_GET_HOST.USER_NUM04,
665 P_GET_HOST.USER_STR01,P_GET_HOST.USER_STR02,
666 P_GET_HOST.USER_STR03,P_GET_HOST.USER_STR04,P_GET_HOST.DELETED_FLAG,
667 P_GET_HOST.SECURITY_MASK,
668 P_GET_HOST.CHECKOUT_USER,
669 SYSDATE,SYSDATE,1,1);
670 UPDATE CZ_PRICE_GROUPS SET ORIG_SYS_REF=NULL
671 WHERE CURRENT OF C_GET_HOST;
672 EXCEPTION
673 WHEN OTHERS THEN
674 xERROR:=cz_utils.log_report(SQLERRM,1,'CZ_IMP_ALL.IMPORT_AFTER_COMPLETE',11276,inRUN_ID);
675 END;
676 END IF;
677
678 END LOOP;
679 CLOSE C_GET_HOST;
680 COMMIT;
681
682 END import_after_complete;
683 ------------------------------------------------------------------------------------------
684 PROCEDURE goSingleBill(nOrg_ID IN NUMBER,nTop_ID IN NUMBER,
685 COPY_CHILD_MODELS IN VARCHAR2,
686 REFRESH_MODEL_ID IN NUMBER,
687 COPY_ROOT_MODEL IN VARCHAR2,
688 x_run_id OUT NOCOPY NUMBER) IS -- sselahi: added x_run_id
689 xERROR BOOLEAN:=FALSE;
690 imp_st number;
691 imp_end number;
692 d_str varchar2(255);
693 nLogTime BOOLEAN := FALSE;
694 nEnableTrace BOOLEAN := FALSE;
695 BEGIN
696
697 nLogTime := isTimingLogEnabled;
698 nEnableTrace := isTracingEnabled;
699
700 if (nLogTime) then
701 get_time := TRUE;
702 end if;
703
704 if (get_time) then
705 imp_st := dbms_utility.get_time();
706 end if;
707
708 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
709 if (get_time) then
710 imp_end := dbms_utility.get_time();
711 d_str := 'Import (' || nTop_Id || ') :' || (imp_end-imp_st)/100.00;
712 xERROR:=cz_utils.log_report(d_str,1,'IMPORT',11299,NULL);
713 end if;
714 EXCEPTION
715 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
716 RAISE;
717 WHEN OTHERS THEN
718 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
719 xERROR:=cz_utils.log_report(d_str,1,'CZ_IMP_ALL.goSingleBill',11276,NULL);
720 RAISE;
721 END goSingleBill;
722 ------------------------------------------------------------------------------------------
723 PROCEDURE AddBillToImport(errbuf OUT NOCOPY VARCHAR2,retcode OUT NOCOPY NUMBER,nOrg_ID IN NUMBER,nTop_ID IN NUMBER,
724 COPY_CHILD_MODELS IN VARCHAR2) IS
725 xERROR BOOLEAN:=FALSE;
726 server_id cz_servers.server_local_id%TYPE;
727 v_enabled VARCHAR2(1) := '1';
728
729 BEGIN
730 retcode:=0;
731 errbuf:='';
732
733 BEGIN
734 SELECT server_local_id INTO server_id
735 FROM cz_servers
736 WHERE import_enabled = v_enabled;
737 EXCEPTION
738 WHEN TOO_MANY_ROWS THEN
739 retcode:=2;
740 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_TOO_MANY_SERVERS');
744 retcode:=2;
741 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.ADDBILLTOIMPORT',11276,NULL);
742 RAISE;
743 WHEN NO_DATA_FOUND THEN
745 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_NO_SERVERS');
746 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.ADDBILLTOIMPORT',11276,NULL);
747 RAISE;
748 END;
749
750 insert into cz_xfr_project_bills (top_item_id,organization_id,deleted_flag,explosion_type,model_ps_node_id,
751 copy_addl_child_models,source_server)
752 select nTop_ID,nOrg_ID,'0','OPTIONAL',cz_xfr_project_bills_s.NEXTVAL,COPY_CHILD_MODELS,server_id
753 from dual where not exists
754 (select 1 from cz_xfr_project_bills where organization_id=nOrg_ID and
755 top_item_id=nTop_ID and explosion_type='OPTIONAL' and source_server = server_id);
756 update cz_xfr_project_bills set deleted_flag='0',copy_addl_child_models = COPY_CHILD_MODELS
757 where organization_id=nOrg_ID and
758 top_item_id=nTop_ID and explosion_type='OPTIONAL' and source_server = server_id;
759 COMMIT;
760 EXCEPTION
761 WHEN OTHERS THEN
762 retcode:=2;
763 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
764 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.ADDBILLTOIMPORT',11276,NULL);
765 RAISE;
766 END;
767 ------------------------------------------------------------------------------------------
768 PROCEDURE SetSingleBillState(errbuf OUT NOCOPY VARCHAR2,retcode OUT NOCOPY NUMBER,nOrg_ID IN NUMBER,nTop_ID IN NUMBER,sState IN VARCHAR2) IS
769 xERROR BOOLEAN:=FALSE;
770 server_id cz_servers.server_local_id%TYPE;
771 v_enabled VARCHAR2(1) := '1';
772
773 BEGIN
774 retcode:=0;
775 errbuf:='';
776
777 BEGIN
778 SELECT server_local_id INTO server_id
779 FROM cz_servers
780 WHERE import_enabled = v_enabled;
781 EXCEPTION
782 WHEN TOO_MANY_ROWS THEN
783 retcode:=2;
784 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_TOO_MANY_SERVERS');
785 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.SETSINGLEBILLSTATE',11276,NULL);
786 RAISE;
787 WHEN NO_DATA_FOUND THEN
788 retcode:=2;
789 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_NO_SERVERS');
790 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.SETSINGLEBILLSTATE',11276,NULL);
791 RAISE;
792 END;
793
794 update cz_xfr_project_bills set
795 deleted_flag=DECODE(UPPER(sState),'0','1','OFF','1','NO','1','DISABLE','1',
796 '1','0','ON','0','YES','0','ENABLE','0',DELETED_FLAG)
797 where organization_id=nOrg_ID and top_item_id=nTop_ID and explosion_type='OPTIONAL' AND
798 source_server = server_id;
799 COMMIT;
800 EXCEPTION
801 WHEN OTHERS THEN
802 retcode:=2;
803 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
804 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.SETSINGLEBILLSTATE',11276,NULL);
805 END;
806 ------------------------------------------------------------------------------------------
807 PROCEDURE RemoveModel(errbuf OUT NOCOPY VARCHAR2,retcode OUT NOCOPY NUMBER,sOrg_ID IN VARCHAR2,
808 dsOrg_ID IN VARCHAR2,sTop_ID IN VARCHAR2) IS
809
810 xERROR BOOLEAN:=FALSE;
811 nTop_ID CZ_XFR_PROJECT_BILLS.TOP_ITEM_ID%TYPE;
812 nOrg_ID CZ_XFR_PROJECT_BILLS.ORGANIZATION_ID%TYPE;
813 server_id cz_servers.server_local_id%TYPE;
814 v_enabled VARCHAR2(1) := '1';
815
816 BEGIN
817 retcode:=0;
818 errbuf:='';
819
820 BEGIN
821 SELECT server_local_id INTO server_id
822 FROM cz_servers
823 WHERE import_enabled = v_enabled;
824 EXCEPTION
825 WHEN TOO_MANY_ROWS THEN
826 retcode:=2;
827 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_TOO_MANY_SERVERS');
828 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.RemoveModel',11276,NULL);
829 RAISE;
830 WHEN NO_DATA_FOUND THEN
831 retcode:=2;
832 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_NO_SERVERS');
833 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.RemoveModel',11276,NULL);
834 RAISE;
835 END;
836
837 SELECT ORGANIZATION_ID INTO nOrg_ID FROM CZ_EXV_ORGANIZATIONS
838 WHERE ORGANIZATION_CODE = sOrg_ID;
839
840 SELECT inventory_item_id INTO nTop_ID
841 FROM cz_exv_mtl_system_items
842 WHERE bom_item_type = 1
843 AND organization_id = nOrg_ID
844 AND concatenated_segments = sTop_ID
845 AND rownum = 1;
846
847 update cz_xfr_project_bills set
848 deleted_flag='1'
849 where organization_id=nOrg_ID and top_item_id=nTop_ID
850 and source_server = server_id;
851
852 COMMIT;
853 EXCEPTION
854 WHEN OTHERS THEN
855 retcode:=2;
856 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
857 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.RemoveModel',11276,NULL);
858 END;
859 ------------------------------------------------------------------------------------------
860 PROCEDURE PopulateModels_cp(errbuf OUT NOCOPY VARCHAR2,retcode OUT NOCOPY NUMBER,
861 sOrg_ID IN VARCHAR2,dsOrg_ID IN VARCHAR2,
862 sFrom IN VARCHAR2,sTo IN VARCHAR,
863 COPY_CHILD_MODELS IN VARCHAR2 DEFAULT '0') IS
864 CURSOR C_GETMODELS(Org_ID NUMBER) IS
865 SELECT A.ASSEMBLY_ITEM_ID FROM CZ_EXV_BILL_OF_MATERIALS A,CZ_EXV_MTL_SYSTEM_ITEMS B
866 WHERE A.ASSEMBLY_ITEM_ID=B.INVENTORY_ITEM_ID
867 AND A.ORGANIZATION_ID=B.ORGANIZATION_ID
868 AND B.BOM_ITEM_TYPE=1
869 AND A.ORGANIZATION_ID=Org_ID
870 AND B.CONCATENATED_SEGMENTS BETWEEN sFrom AND NVL(sTo,sFrom);
871
872 xERROR BOOLEAN:=FALSE;
873 nTop_ID CZ_XFR_PROJECT_BILLS.TOP_ITEM_ID%TYPE;
874 nOrg_ID CZ_XFR_PROJECT_BILLS.ORGANIZATION_ID%TYPE;
875 v_copy_child_models CHAR(1);
876 l_run_id NUMBER; -- sselahi: added to pass to call goSingleBill
877 BEGIN
878
879 CZ_ORAAPPS_INTEGRATE.mERRBUF := '';
883
880 CZ_ORAAPPS_INTEGRATE.mRETCODE := 0;
881
882 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;
884 SELECT ORGANIZATION_ID INTO nOrg_ID FROM CZ_EXV_ORGANIZATIONS
885 WHERE ORGANIZATION_CODE = sOrg_ID;
886
887 OPEN C_GETMODELS(nOrg_ID);
888 LOOP
889 FETCH C_GETMODELS INTO nTop_ID;
890 EXIT WHEN C_GETMODELS%NOTFOUND;
891
892 /* Log a message if the bill being imported (not refreshed) refers to a common bill */
893 check_for_common_bill(errbuf,retcode,nORG_ID,nTOP_ID);
894 goSingleBill(nOrg_ID,nTop_ID,v_COPY_CHILD_MODELS,-1, '0',l_run_id); -- sselahi: added l_run_id
895
896 IF(CZ_ORAAPPS_INTEGRATE.mRETCODE = 2)THEN
897 errbuf := CZ_ORAAPPS_INTEGRATE.mERRBUF;
898 retcode := CZ_ORAAPPS_INTEGRATE.mRETCODE;
899 RETURN;
900 END IF;
901
902 END LOOP;
903 CLOSE C_GETMODELS;
904 COMMIT;
905
906 errbuf := CZ_ORAAPPS_INTEGRATE.mERRBUF;
907 retcode := CZ_ORAAPPS_INTEGRATE.mRETCODE;
908
909 EXCEPTION
910 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
911 RAISE;
912 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
913 retcode:=2;
914 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_ACTIVE_SESSION_EXISTS');
915 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.PopulateModels_cp',11276,NULL);
916 RAISE;
917 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
918 retcode:=2;
919 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED');
920 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.PopulateModels_cp',11276,NULL);
921 RAISE;
922 WHEN OTHERS THEN
923 retcode:=2;
924 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
925 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.PopulateModels_cp',11276,NULL);
926 RAISE;
927 END;
928 ------------------------------------------------------------------------------------------
929
930 -- Bug10011026 This procedure was introduced for Sun implementation where new Concurrent Program
931 -- was introduced with same (segment1) value for sFrom and sTo parameters and cursor C_GETMODELS
932 -- in original proc was changed to go against only cz_exv_mtl_system_items for performance improvement
933 PROCEDURE PopulateSingleModel_cp(errbuf OUT NOCOPY VARCHAR2,retcode OUT NOCOPY NUMBER,
934 sOrg_ID IN VARCHAR2,dsOrg_ID IN VARCHAR2,
935 sFrom IN VARCHAR2,sTo IN VARCHAR,
936 COPY_CHILD_MODELS IN VARCHAR2 DEFAULT '0') IS
937 CURSOR C_GETMODELS(Org_ID NUMBER) IS
938 SELECT B.INVENTORY_ITEM_ID FROM CZ_EXV_MTL_SYSTEM_ITEMS B
939 WHERE B.BOM_ITEM_TYPE=1
940 AND B.ORGANIZATION_ID=Org_ID
941 AND B.CONCATENATED_SEGMENTS = sFrom ;
942
943 xERROR BOOLEAN:=FALSE;
944 nTop_ID CZ_XFR_PROJECT_BILLS.TOP_ITEM_ID%TYPE;
945 nOrg_ID CZ_XFR_PROJECT_BILLS.ORGANIZATION_ID%TYPE;
946 v_copy_child_models CHAR(1);
947 l_run_id NUMBER; -- sselahi: added to pass to call goSingleBill
948 BEGIN
949
950 CZ_ORAAPPS_INTEGRATE.mERRBUF := '';
951 CZ_ORAAPPS_INTEGRATE.mRETCODE := 0;
952
953 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;
954
955 SELECT ORGANIZATION_ID INTO nOrg_ID FROM CZ_EXV_ORGANIZATIONS
956 WHERE ORGANIZATION_CODE = sOrg_ID;
957
958 OPEN C_GETMODELS(nOrg_ID);
959 LOOP
960 FETCH C_GETMODELS INTO nTop_ID;
961 EXIT WHEN C_GETMODELS%NOTFOUND;
962
963 /* Log a message if the bill being imported (not refreshed) refers to a common bill */
964 check_for_common_bill(errbuf,retcode,nORG_ID,nTOP_ID);
965 goSingleBill(nOrg_ID,nTop_ID,v_COPY_CHILD_MODELS,-1, '0',l_run_id); -- sselahi: added l_run_id
966
967 IF(CZ_ORAAPPS_INTEGRATE.mRETCODE = 2)THEN
968 errbuf := CZ_ORAAPPS_INTEGRATE.mERRBUF;
969 retcode := CZ_ORAAPPS_INTEGRATE.mRETCODE;
970 RETURN;
971 END IF;
972
973 END LOOP;
974 CLOSE C_GETMODELS;
975 COMMIT;
976
977 errbuf := CZ_ORAAPPS_INTEGRATE.mERRBUF;
978 retcode := CZ_ORAAPPS_INTEGRATE.mRETCODE;
979
980 EXCEPTION
981 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
982 RAISE;
983 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
984 retcode:=2;
985 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_ACTIVE_SESSION_EXISTS');
986 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.PopulateSingleModel_cp',11276,NULL);
987 RAISE;
988 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
989 retcode:=2;
990 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED');
991 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.PopulateSingleModel_cp',11276,NULL);
992 RAISE;
993 WHEN OTHERS THEN
994 retcode:=2;
995 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
996 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.PopulateSingleModel_cp',11276,NULL);
997 RAISE;
998 END;
999 ------------------------------------------------------------------------------------------
1000
1001 PROCEDURE RefreshModels(errbuf OUT NOCOPY VARCHAR2,retcode OUT NOCOPY NUMBER) IS
1002 BEGIN
1003 CZ_ORAAPPS_INTEGRATE.go_cp(errbuf, retcode);
1004 END;
1005 ------------------------------------------------------------------------------------------
1006 PROCEDURE setRunID(inRun_ID IN PLS_INTEGER,table_name IN VARCHAR2) IS
1007 DC_CURSOR INTEGER;
1008 RESULT INTEGER;
1009 BEGIN
1010 DC_CURSOR:=DBMS_SQL.OPEN_CURSOR;
1011 DBMS_SQL.PARSE(DC_CURSOR,'UPDATE '||table_name||' SET RUN_ID='||inRun_ID||
1012 ',REC_STATUS=NULL,DISPOSITION=NULL WHERE RUN_ID IS NULL',DBMS_SQL.NATIVE);
1013 RESULT:=DBMS_SQL.EXECUTE(DC_CURSOR);
1014 DBMS_SQL.CLOSE_CURSOR(DC_CURSOR);
1015 EXCEPTION
1016 WHEN OTHERS THEN
1020 PROCEDURE setRecStatus(inRun_ID IN PLS_INTEGER,table_name IN VARCHAR2) IS
1017 DBMS_SQL.CLOSE_CURSOR(DC_CURSOR);
1018 END;
1019 ------------------------------------------------------------------------------------------
1021 DC_CURSOR INTEGER;
1022 RESULT INTEGER;
1023 BEGIN
1024 DC_CURSOR:=DBMS_SQL.OPEN_CURSOR;
1025 DBMS_SQL.PARSE(DC_CURSOR,'UPDATE '||table_name||
1026 ' SET REC_STATUS=NULL,DISPOSITION=NULL WHERE RUN_ID='||inRun_ID,DBMS_SQL.NATIVE);
1027 RESULT:=DBMS_SQL.EXECUTE(DC_CURSOR);
1028 DBMS_SQL.CLOSE_CURSOR(DC_CURSOR);
1029 EXCEPTION
1030 WHEN OTHERS THEN
1031 DBMS_SQL.CLOSE_CURSOR(DC_CURSOR);
1032 END;
1033 ------------------------------------------------------------------------------------------
1034
1035 PROCEDURE check_for_common_bill
1036 (errbuf OUT NOCOPY VARCHAR2,
1037 retcode OUT NOCOPY NUMBER,
1038 nORG_ID IN NUMBER,
1039 nTOP_ID IN NUMBER)
1040 IS
1041 commonInvId CZ_XFR_PROJECT_BILLS.TOP_ITEM_ID%TYPE;
1042 commonOrgId CZ_XFR_PROJECT_BILLS.ORGANIZATION_ID%TYPE;
1043 xERROR BOOLEAN:=FALSE;
1044 BEGIN
1045 errbuf:='';
1046 retcode:=0;
1047
1048 /* Log a message if the bill being imported (not refreshed) refers to a common bill */
1049 SELECT ORGANIZATION_ID, ASSEMBLY_ITEM_ID
1050 INTO commonOrgId, commonInvId
1051 FROM CZ_EXV_BILL_OF_MATERIALS
1052 WHERE BILL_SEQUENCE_ID in
1053 (SELECT COMMON_BILL_SEQUENCE_ID FROM CZ_EXV_BILL_OF_MATERIALS
1054 WHERE ORGANIZATION_ID = nOrg_ID
1055 AND ASSEMBLY_ITEM_ID = nTop_ID);
1056 IF ((commonOrgId <> nOrg_ID) or (commonInvId <> nTop_ID)) THEN
1057 retcode := 1;
1058 errbuf := CZ_UTILS.get_text('CZ_HAS_COMMON_BILL','ORGID',nOrg_ID,'INVID',nTop_ID,'C_ORGID',commonOrgId,'C_INVID',commonInvId);
1059 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL:CZ_COMMON_BILL_CHECK',11276,NULL);
1060 END IF;
1061 EXCEPTION
1062 WHEN NO_DATA_FOUND THEN
1063 retcode := 2;
1064 errbuf := CZ_UTILS.get_text('CZ_IMP_BOM_NO_DATA');
1065 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL:CZ_COMMON_BILL_CHECK',11276,NULL);
1066 WHEN OTHERS THEN
1067 retcode := 2;
1068 errbuf := 'CZ_IMP_ALL:COMMON_BILL_CHECK' || SQLERRM;
1069 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL:COMMON_BILL_CHECK',11276,NULL);
1070 RAISE;
1071 END check_for_common_bill;
1072
1073 ------------------------------------------------------------------------------------------
1074 FUNCTION REPORT (Msg in VARCHAR2, Urgency in NUMBER, ByCaller in VARCHAR2,
1075 StatusCode in NUMBER) RETURN BOOLEAN IS
1076 xError Boolean;
1077 l_msg VARCHAR2(2000);
1078 BEGIN
1079 -- log msg to both fnd and cz tables
1080 xError := cz_utils.log_report(Msg, urgency, ByCaller, StatusCode);
1081 IF (xError) THEN
1082 commit;
1083 ELSE
1084 rollback;
1085 END IF;
1086
1087 RETURN xError;
1088 EXCEPTION
1089 WHEN OTHERS THEN
1090 ROLLBACK;
1091 RETURN FALSE;
1092 END REPORT;
1093 ------------------------------------------------------------------------------------------
1094 PROCEDURE go_generic_cp(errbuf OUT NOCOPY VARCHAR2,
1095 retcode OUT NOCOPY NUMBER,
1096 inRun_iD IN PLS_INTEGER,
1097 p_rp_folder_id IN NUMBER) IS
1098
1099 outRun_id PLS_INTEGER;
1100 xERROR BOOLEAN:=FALSE;
1101 l_success_msg VARCHAR2(255);
1102
1103 BEGIN
1104 go_generic(outRun_ID,inRun_ID, p_rp_folder_id);
1105 l_success_msg:=CZ_UTILS.get_text('CZ_IMP_GENIMP_SUCCESS_RUNID', 'OUT_RUN_ID', outRun_ID);
1106 EXCEPTION
1107 WHEN OTHERS THEN
1108 retcode:=2;
1109 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
1110 xERROR:=cz_utils.log_report(errbuf,1,'CZ_IMP_ALL.generic_import',11276,NULL);
1111 END go_generic_cp;
1112 ------------------------------------------------------------------------------------------
1113 END CZ_IMP_ALL;