1 PACKAGE BODY CZ_IMP_SINGLE AS
2 /* $Header: czisngb.pls 120.32.12020000.3 2012/10/04 20:46:48 lamrute ship $ */
3
4 /*******************************
5 Stuller's changes :
6
7 1. extr_intl_text - orig_sys_ref has inventory_item_id:expl_type:orgId (instead of description)
8 2. Select - Distinct - items
9 3. Select - Distinct - item_property_value
10 4. Call cz_ref.delete_duplicates after populate_table
11 5. extr_intl_text change to query bill_sequence_id and use in query
12
13 ********************************/
14
15 G_BOM_APPLICATION_ID CONSTANT NUMBER := 702;
16 G_EGO_APPLICATION_ID CONSTANT NUMBER := 431;
17
18 G_PKG_NAME CONSTANT VARCHAR2(50) := 'CZ_IMP_SINGLE';
19
20 DECIMAL_TYPE CONSTANT NUMBER := 2;
21 TEXT_TYPE CONSTANT NUMBER := 4;
22 TL_TEXT_TYPE CONSTANT NUMBER := 8;
23
24 l_Batch_Size NUMBER := 100;
25
26 g_ItemCatalogTable SYSTEM.CZ_ITEM_CATALOG_TBL := SYSTEM.CZ_ITEM_CATALOG_TBL(SYSTEM.CZ_ITEM_CATALOG_REC(NULL));
27 g_CONFIG_ENGINE_TYPE VARCHAR2(10);
28
29 TYPE tCatalogGroupId IS TABLE OF cz_exv_item_master.item_catalog_group_id%TYPE INDEX BY BINARY_INTEGER;
30 TYPE tInventoryItemId IS TABLE OF cz_exv_item_master.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
31 TYPE tInt IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
32
33 itemCatalogGroupId tCatalogGroupId;
34 repCatalogGroupId tCatalogGroupId;
35 inventoryItemId tInventoryItemId;
36 repItemId tInventoryItemId;
37 hashCatalog tInt;
38
39 -- This table contains top model IDs of explosions that have been processed within an import run
40 processed_expls_tbl tInt;
41
42 --LA 9595142 This table contains model IDs that have been enabled for import
43 import_enabled_tbl tInt;
44 --Variable to store db setting value for disabling refresh of multiple copies of same model
45 disableMultipleCopyImport PLS_INTEGER;
46
47 --This variable stores a value of db setting, introduced for Pella, to be used across two procedures.
48
49 allowDecimalOptionClass PLS_INTEGER;
50
51 --9496782
52 czGatherStatsCnt NUMBER := 0;
53
54 TYPE number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
55 TYPE varchar_tbl_type IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
56 TYPE long_varchar_tbl_type IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
57 TYPE varchar_arr_tbl_type IS TABLE OF varchar_tbl_type INDEX BY BINARY_INTEGER;
58 TYPE number_arr_tbl_type IS TABLE OF number_tbl_type INDEX BY BINARY_INTEGER;
59 TYPE varchar_iv_tbl_type IS TABLE OF VARCHAR2(255) INDEX BY VARCHAR2(255);
60
61 TYPE rec_cols_rec IS RECORD (col_name VARCHAR2(255),col_num NUMBER);
62 TYPE rec_cols_tbl IS TABLE OF rec_cols_rec INDEX BY VARCHAR2(255);
63
64 PROCEDURE get_App_Info(p_app_short_name IN VARCHAR2,
65 x_oracle_schema OUT NOCOPY VARCHAR2) IS
66
67 v_status VARCHAR2(255);
68 v_industry VARCHAR2(255);
69 v_ret BOOLEAN;
70 BEGIN
71 v_ret := FND_INSTALLATION.GET_APP_INFO(APPLICATION_SHORT_NAME => p_app_short_name,
72 STATUS => v_status,
73 INDUSTRY => v_industry,
74 ORACLE_SCHEMA => x_oracle_schema);
75 END;
76
77 ------------------------------------------------------------------------------------------
78 -- Returns true if the input child model should be refreshed within this import run.
79 -- This check was added for Stuller but isn't currently in use. To do this correctly,
80 -- the logic in this procedure would need to be replaced for a check to see if this
81 -- model was modified in BOM.
82
83 FUNCTION importChildModel(inRunId IN NUMBER,
84 inOrgId IN NUMBER,
85 inTopId IN NUMBER,
86 inExplType IN VARCHAR2)
87 RETURN BOOLEAN IS
88
89 --LA 9595142 Create dynamic cursor
90 TYPE childModelCurType IS REF CURSOR;
91 c_childModel childModelCurType;
92 nCurStmt VARCHAR2(400);
93
94 xERROR BOOLEAN:=FALSE;
95 nChildFound BOOLEAN:=FALSE;
96 nDevlProjectId cz_devl_projects.devl_project_id%TYPE;
97
98 BEGIN
99
100 --LA 9595142 Changed the cursor sql to use import enabled flag
101 nCurStmt := 'SELECT p.model_ps_node_id FROM cz_xfr_project_bills p, cz_devl_projects d '
102 || ' WHERE p.organization_id = :1 '
103 || ' AND p.top_item_id = :2 '
104 || ' AND p.explosion_type = :3 '
105 || ' AND d.deleted_flag = ''0'' '
106 || ' AND NVL(d.config_engine_type,''L'') = :4 ' --LA 12548898 Engine Sync
107 || ' AND p.model_ps_node_id = d.devl_project_id ';
108
109 IF (disableMultipleCopyImport = 1) THEN
110 nCurStmt := nCurStmt || ' AND d.import_enabled = ''1'' ';
111 ELSE
112 nCurStmt := nCurStmt || ' AND d.devl_project_id = d.persistent_project_id ' ;
113 END IF;
114
115 --OPEN c_childModel;
116 OPEN c_childModel FOR nCurStmt USING inOrgId, inTopId, inExplType, g_CONFIG_ENGINE_TYPE;
117
118
119 FETCH c_childModel INTO nDevlProjectId;
120 nChildFound:=c_childModel%FOUND;
121 CLOSE c_childModel;
122 return nChildFound;
123 END;
124 ------------------------------------------------------------------------------------------
125
126 PROCEDURE EXTR_ITEM_MASTER(inRun_ID IN PLS_INTEGER,
127 nOrg_ID IN NUMBER,
128 nTop_ID IN NUMBER,
129 sExpl_type IN VARCHAR2)
130 IS
131 DFLT_REFPARTNBR CZ_DB_SETTINGS.VALUE%TYPE;
132 xERROR BOOLEAN :=FALSE;
133
134 v_settings_id VARCHAR2(40);
135 v_section_name VARCHAR2(30);
136
137 CURSOR C_REF_PART_NBR IS
138 SELECT UPPER(VALUE) FROM CZ_DB_SETTINGS
139 WHERE UPPER(SECTION_NAME) = v_section_name
140 AND UPPER(SETTING_ID) = v_settings_id;
141
142 TYPE tItemDesc IS TABLE OF cz_exv_items.item_desc%TYPE INDEX BY BINARY_INTEGER;
143 TYPE tBomItemType IS TABLE OF cz_exv_items.bom_item_type%TYPE INDEX BY BINARY_INTEGER;
144 TYPE tOrganizationId IS TABLE OF cz_exv_items.organization_id%TYPE INDEX BY BINARY_INTEGER;
145 TYPE tInventoryItemId IS TABLE OF cz_exv_items.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
146 TYPE tSegment1 IS TABLE OF cz_exv_items.segment1%TYPE INDEX BY BINARY_INTEGER;
147 TYPE tConcatenatedSegments IS TABLE OF cz_exv_items.concatenated_segments%TYPE INDEX BY BINARY_INTEGER;
148 TYPE tFixedLeadTime IS TABLE OF cz_exv_items.fixed_lead_time%TYPE INDEX BY BINARY_INTEGER;
149 TYPE tItemStatusCode IS TABLE OF cz_exv_items.inventory_item_status_code%TYPE INDEX BY BINARY_INTEGER;
150 TYPE tCatalogId IS TABLE OF cz_exv_items.item_catalog_group_id%TYPE INDEX BY BINARY_INTEGER;
151 TYPE tIndivisibleFlag IS TABLE OF cz_exv_items.indivisible_flag%TYPE INDEX BY BINARY_INTEGER;
152 TYPE tCustomerOrderFlag IS TABLE OF cz_exv_items.customer_order_enabled_flag%TYPE INDEX BY BINARY_INTEGER;
153 TYPE tPrimaryUomCode IS TABLE OF cz_exv_items.primary_uom_code%TYPE INDEX BY BINARY_INTEGER;
154
155 TYPE tOrigSysRef IS TABLE OF cz_imp_item_master.orig_sys_ref%TYPE INDEX BY BINARY_INTEGER;
156 TYPE tRefPartNbr IS TABLE OF cz_imp_item_master.ref_part_nbr%TYPE INDEX BY BINARY_INTEGER;
157 TYPE tDescText IS TABLE OF cz_imp_item_master.desc_text%TYPE INDEX BY BINARY_INTEGER;
158 TYPE tLeadTime IS TABLE OF cz_imp_item_master.lead_time%TYPE INDEX BY BINARY_INTEGER;
159 TYPE tQuoteableFlag IS TABLE OF cz_imp_item_master.quoteable_flag%TYPE INDEX BY BINARY_INTEGER;
160 TYPE tDeletedFlag IS TABLE OF cz_imp_item_master.deleted_flag%TYPE INDEX BY BINARY_INTEGER;
161 TYPE tItemUomCode IS TABLE OF cz_imp_item_master.primary_uom_code%TYPE INDEX BY BINARY_INTEGER;
162 TYPE tDecimalQtyFlag IS TABLE OF cz_imp_item_master.decimal_qty_flag%TYPE INDEX BY BINARY_INTEGER;
163 TYPE tFskItemType IS TABLE OF cz_imp_item_master.fsk_itemtype_1_1%TYPE INDEX BY BINARY_INTEGER;
164 TYPE tSrcApplicationId IS TABLE OF cz_imp_item_master.src_application_id%TYPE INDEX BY BINARY_INTEGER;
165 TYPE tSrcTypeCode IS TABLE OF cz_imp_item_master.src_type_code%TYPE INDEX BY BINARY_INTEGER;
166
167 InventoryItemId tInventoryItemId;
168 OrganizationId tOrganizationId;
169 Segment1 tSegment1;
170 ConcatenatedSegments tConcatenatedSegments;
171 ItemDesc tItemDesc;
172 FixedLeadTime tFixedLeadTime;
173 BomItemType tBomItemType;
174 CustomerOrderFlag tCustomerOrderFlag;
175 ItemStatusCode tItemStatusCode;
176 PrimaryUomCode tPrimaryUomCode;
177 IndivisibleFlag tIndivisibleFlag;
178 CatalogId tCatalogId;
179 SrcApplicationId tSrcApplicationId;
180
181 iOrigSysRef tOrigSysRef;
182 iRefPartNbr tRefPartNbr;
183 iDescText tDescText;
184 iLeadTime tLeadTime;
185 iQuoteableFlag tQuoteableFlag;
186 iDeletedFlag tDeletedFlag;
187 iPrimaryUomCode tItemUomCode;
188 iDecimalQtyFlag tDecimalQtyFlag;
189 iFskItemType tFskItemType;
190 iSrcApplicationId tSrcApplicationId;
191 iSrcTypeCode tSrcTypeCode;
192 nIndex PLS_INTEGER := 1;
193
194 st_time number;
195 end_time number;
196 loop_end_time number;
197 insert_end_time number;
198 d_str varchar2(255);
199
200 BEGIN
201
202 v_settings_id := 'REFPARTNBR';
203 v_section_name := 'ORAAPPS_INTEGRATE';
204
205 OPEN C_REF_PART_NBR;
206 FETCH C_REF_PART_NBR INTO DFLT_REFPARTNBR;
207 CLOSE C_REF_PART_NBR;
208
209 if (CZ_IMP_ALL.get_time) then
210 st_time := dbms_utility.get_time();
211 end if;
212
213 SELECT DISTINCT INVENTORY_ITEM_ID, ORGANIZATION_ID, SEGMENT1, CONCATENATED_SEGMENTS, ITEM_DESC,
214 FIXED_LEAD_TIME, BOM_ITEM_TYPE, CUSTOMER_ORDER_ENABLED_FLAG,
215 INVENTORY_ITEM_STATUS_CODE, PRIMARY_UOM_CODE, INDIVISIBLE_FLAG, ITEM_CATALOG_GROUP_ID,
216 INV_APPLICATION_ID
217 BULK COLLECT INTO
218 InventoryItemId, OrganizationId, Segment1, ConcatenatedSegments, ItemDesc,
219 FixedLeadTime, BomItemType, CustomerOrderFlag,
220 ItemStatusCode, PrimaryUomCode, IndivisibleFlag, CatalogId,
221 SrcApplicationId
222 FROM CZ_EXV_ITEMS
223 WHERE ORGANIZATION_ID=nOrg_ID AND TOP_ITEM_ID=nTop_ID
224 AND EXPLOSION_TYPE=sExpl_type;
225
226 if (CZ_IMP_ALL.get_time) then
227 end_time := dbms_utility.get_time();
228 d_str := inRun_Id || ' Bulk collect item master (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
229 xERROR:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_id);
230 end if;
231
232 FOR i IN 1..InventoryItemId.COUNT LOOP
233
234 iOrigSysRef(nIndex) := CZ_ORAAPPS_INTEGRATE.ITEM_SURROGATE_KEY(to_char(InventoryItemId(i)),to_char(OrganizationId(i)));
235 IF(DFLT_REFPARTNBR = 'SEGMENT1')THEN
236 iRefPartNbr(nIndex) := Segment1(i);
237 ELSIF(DFLT_REFPARTNBR = 'CONCATENATED_SEGMENTS')THEN
238 iRefPartNbr(nIndex) := ConcatenatedSegments(i);
239 ELSE
240 iRefPartNbr(nIndex) := ItemDesc(i);
241 END IF;
242 iDescText(nIndex) := ItemDesc(i);
243 iLeadTime(nIndex) := FixedLeadTime(i);
244 IF(CustomerOrderFlag(i) = 'N')THEN
245 IF(BomItemType(i) = cnStandard)THEN
246 iQuoteableFlag(nIndex) := '1';
247 ELSE
248 iQuoteableFlag(nIndex) := '0';
249 END IF;
250 ELSE
251 iQuoteableFlag(nIndex) := '1';
252 END IF;
253 /* Bug 8210696 - no need to check for 'OBSOLETE'
254 IF(ItemStatusCode(i) = 'OBSOLETE')THEN
255 iDeletedFlag(nIndex) := '1';
256 ELSE
257 iDeletedFlag(nIndex) := '0';
258 END IF;*/
259 iDeletedFlag(nIndex) := '0';
260 iPrimaryUomCode(nIndex) := PrimaryUomCode(i);
261
262 /* Added the profile condition for the fix of bug # 3074328 jjujjava 10/06/03 */
263 IF(UPPER(FND_PROFILE.VALUE('CZ_IMP_DECIMAL_QTY_FLAG')) = 'Y')THEN
264 IF(IndivisibleFlag(i) = 'Y')THEN
265 iDecimalQtyFlag(nIndex) := '0';
266 ELSIF(BomItemType(i) = cnStandard OR (BomItemType(i) = cnOptionClass AND allowDecimalOptionClass = 1)) THEN
267 iDecimalQtyFlag(nIndex) := '1';
268 ELSE
269 iDecimalQtyFlag(nIndex) := '0';
270 END IF;
271 ELSE
272 iDecimalQtyFlag(nIndex) := '0';
273 END IF;
274
275 iFskItemType(nIndex) := CatalogId(i);
276 iSrcApplicationId(nIndex) := SrcApplicationId(i);
277 iSrcTypeCode(nIndex) := BomItemType(i);
278
279 nIndex := nIndex + 1;
280 END LOOP;
281
282 if (CZ_IMP_ALL.get_time) then
283 loop_end_time := dbms_utility.get_time();
284 --dbms_output.put_line ('loop over coll. (' || nTop_Id || ') :' || (loop_end_time-end_time)/100.00);
285 end if;
286
287 FORALL i IN 1..iOrigSysRef.COUNT
288 INSERT /*+ APPEND */ INTO CZ_IMP_ITEM_MASTER
289 (ORIG_SYS_REF, REF_PART_NBR, DESC_TEXT, LEAD_TIME,
290 QUOTEABLE_FLAG, DELETED_FLAG,
291 RUN_ID, PRIMARY_UOM_CODE, DECIMAL_QTY_FLAG,
292 FSK_ITEMTYPE_1_1, FSK_ITEMTYPE_1_EXT,
293 SRC_APPLICATION_ID,SRC_TYPE_CODE)
294 VALUES
295 (iOrigSysRef(i), iRefPartNbr(i), ItemDesc(i), iLeadTime(i),
296 iQuoteableFlag(i), iDeletedFlag(i),
297 inRun_ID, iPrimaryUomCode(i), iDecimalQtyFlag(i),
298 iFskItemType(i), iFskItemType(i),
299 iSrcApplicationId(i), iSrcTypeCode(i));
300
301 if (CZ_IMP_ALL.get_time) then
302 insert_end_time := dbms_utility.get_time();
303 --dbms_output.put_line ('Insert imp item master (' || nTop_Id || ') :' || (insert_end_time-end_time)/100.00);
304 end if;
305
306
307 COMMIT;
308
309 EXCEPTION
310 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
311 RAISE;
312 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
313 RAISE;
314 WHEN OTHERS THEN
315 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
316 xERROR:=cz_utils.log_report(d_str,1,'EXTR_ITEM_MASTER',11276,inRun_Id);
317 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
318 END;
319 ------------------------------------------------------------------------------------------
320
321 PROCEDURE setFCEMinMax (minVal IN OUT NOCOPY NUMBER,
322 maxVal IN OUT NOCOPY NUMBER,
323 defaultVal IN NUMBER,
324 p_decimal_item_flag IN VARCHAR2,
325 p_use_defaults IN VARCHAR2,
326 p_set_decimals IN VARCHAR2,
327 p_default_dec IN NUMBER,
328 p_default_int IN NUMBER ) IS
329 l_max_boundary_val NUMBER;
330
331 FUNCTION setFCEMaxBoundary RETURN NUMBER IS
332 l_max_boundary_val NUMBER;
333 BEGIN
334 --
335 -- by default set max boundary value to FND_PROFILE.VALUE('CZ_DEFAULT_MAX_QTY_INT')
336 --
337 l_max_boundary_val := p_default_int;
338
339 --
340 -- if Imports sets decimal quantity <=> FND_PROFILE.VALUE('CZ_IMP_DECIMAL_QTY_FLAG')='Y'
341 -- and item is decimal <=> p_decimal_item_flag='1'
342 -- then set set max boundary value to FND_PROFILE.VALUE('CZ_DEFAULT_MAX_QTY_DEC')
343 --
344 IF (UPPER(p_set_decimals)='Y' AND p_decimal_item_flag='1') THEN
345 l_max_boundary_val := p_default_dec;
346 END IF;
347 RETURN l_max_boundary_val;
348 END setFCEMaxBoundary;
349
350 FUNCTION setFCEMin RETURN NUMBER IS
351 BEGIN
352 IF p_set_decimals='Y' AND p_decimal_item_flag='1' THEN
353 RETURN 0; -- decimals are used and decimal flag = true
354 END IF;
355 RETURN 1; -- integer or decimals , but decimals are not used
356 END setFCEMin;
357
358 BEGIN
359 -- get value of max boundary
360 l_max_boundary_val := setFCEMaxBoundary();
361
362 IF defaultVal IS NULL THEN
363 --
364 -- min=NULL, max=NULL, default value=NULL
365 --
366 IF minVal IS NULL AND (maxVal IS NULL OR maxVal IN(0,-1)) THEN
367 minVal := setFCEMin();
368 maxVal := l_max_boundary_val;
369 RETURN;
370 END IF;
371
372 --
373 -- min=NULL, max IS NOT NULL, default value=NULL
374 --
375 IF minVal IS NULL AND NOT(maxVal IS NULL OR maxVal IN(0,-1)) THEN
376 minVal := setFCEMin();
377 RETURN;
378 END IF;
379
380 --
381 -- min IS NOT NULL, max IS NOT NULL, default value=NULL
382 --
383 IF minVal IS NOT NULL AND NOT(maxVal IS NULL OR maxVal IN(0,-1)) THEN
384 RETURN;
385 END IF;
386
387 --
388 -- min IS NOT NULL, max IS NULL, default value=NULL
389 --
390 IF minVal IS NOT NULL AND (maxVal IS NULL OR maxVal IN(0,-1)) THEN
391 maxVal := l_max_boundary_val;
392 RETURN;
393 END IF;
394
395 ELSE -- there is a not null default value
396
397 --
398 -- min=NULL, max=NULL, default value IS NOT NULL
399 --
400 IF minVal IS NULL AND (maxVal IS NULL OR maxVal IN(0,-1)) THEN
401 IF p_use_defaults='Y' THEN
402 minVal := defaultVal;
403 maxVal := defaultVal;
404 ELSE
405 minVal := setFCEMin();
406 maxVal := l_max_boundary_val;
407 END IF;
408 RETURN;
409 END IF;
410
411 --
412 -- min=NULL, max IS NOT NULL, default value IS NOT NULL
413 --
414 IF minVal IS NULL AND NOT(maxVal IS NULL OR maxVal IN(0,-1)) THEN
415 IF p_use_defaults='Y' THEN
416 IF defaultVal <= maxVal THEN
417 minVal := defaultVal;
418 ELSE
419 minVal := setFCEMin();
420 END IF;
421 ELSE -- do not use defaults case
422 minVal := setFCEMin();
423 END IF;
424 RETURN;
425 END IF;
426
427 --
428 -- min IS NOT NULL, max IS NOT NULL, default value IS NOT NULL
429 --
430 IF minVal IS NOT NULL AND NOT(maxVal IS NULL OR maxVal IN(0,-1)) THEN
431 RETURN;
432 END IF;
433
434 --
435 -- min IS NOT NULL, max IS NULL, default value IS NOT NULL
436 --
437 IF minVal IS NOT NULL AND (maxVal IS NULL OR maxVal IN(0,-1)) THEN
438
439 IF p_use_defaults='Y' THEN
440 IF defaultVal >= minVal THEN
441 maxVal := defaultVal;
442 ELSE
443 maxVal := l_max_boundary_val;
444 END IF;
445 ELSE -- do not use defaults case
446 maxVal := l_max_boundary_val;
447 END IF;
448
449 RETURN;
450 END IF;
451
452 END IF;
453
454
455 END setFCEMinMax;
456
457 ------------------------------------------------------------------------------------------
458 PROCEDURE EXTR_PS_NODE(inRun_ID IN PLS_INTEGER,
459 nOrg_ID IN NUMBER,
460 nTop_ID IN NUMBER,
461 sExpl_type IN VARCHAR2,
462 nModelId IN NUMBER)
463 IS
464
465 TYPE tIntegerArray IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;
466 TYPE tStringArray IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
467 TYPE tNumberArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
468
469 TYPE tComponentSequenceId IS TABLE OF cz_exv_item_master.component_sequence_id%TYPE INDEX BY BINARY_INTEGER;
470 TYPE tComponentCode IS TABLE OF cz_exv_item_master.component_code%TYPE INDEX BY BINARY_INTEGER;
471 TYPE tCreationDate IS TABLE OF cz_exv_item_master.creation_date%TYPE INDEX BY BINARY_INTEGER;
472 TYPE tCreatedBy IS TABLE OF cz_exv_item_master.created_by%TYPE INDEX BY BINARY_INTEGER;
473 TYPE tLastUpdateDate IS TABLE OF cz_exv_item_master.last_update_date%TYPE INDEX BY BINARY_INTEGER;
474 TYPE tLastUpdatedBy IS TABLE OF cz_exv_item_master.last_updated_by%TYPE INDEX BY BINARY_INTEGER;
475 TYPE tEffectivityDate IS TABLE OF cz_exv_item_master.effectivity_date%TYPE INDEX BY BINARY_INTEGER;
476 TYPE tDisableDate IS TABLE OF cz_exv_item_master.disable_date%TYPE INDEX BY BINARY_INTEGER;
477 TYPE tDescription IS TABLE OF cz_exv_item_master.description%TYPE INDEX BY BINARY_INTEGER;
478 TYPE tLowQuantity IS TABLE OF cz_exv_item_master.low_quantity%TYPE INDEX BY BINARY_INTEGER;
479 TYPE tHighQuantity IS TABLE OF cz_exv_item_master.high_quantity%TYPE INDEX BY BINARY_INTEGER;
480 TYPE tSortOrder IS TABLE OF cz_exv_item_master.sort_order%TYPE INDEX BY BINARY_INTEGER;
481 TYPE tBomItemType IS TABLE OF cz_exv_item_master.bom_item_type%TYPE INDEX BY BINARY_INTEGER;
482 TYPE tComponentItemId IS TABLE OF cz_exv_item_master.component_item_id%TYPE INDEX BY BINARY_INTEGER;
483 TYPE tMutuallyExclusiveOptions IS TABLE OF cz_exv_item_master.mutually_exclusive_options%TYPE INDEX BY BINARY_INTEGER;
484 TYPE tPickComponentsFlag IS TABLE OF cz_exv_item_master.pick_components_flag%TYPE INDEX BY BINARY_INTEGER;
485 TYPE tComponentQuantity IS TABLE OF cz_exv_item_master.component_quantity%TYPE INDEX BY BINARY_INTEGER;
486 TYPE tOptional IS TABLE OF cz_exv_item_master.optional%TYPE INDEX BY BINARY_INTEGER;
487 TYPE tOrganizationId IS TABLE OF cz_exv_item_master.organization_id%TYPE INDEX BY BINARY_INTEGER;
488 TYPE tTopItemId IS TABLE OF cz_exv_item_master.top_item_id%TYPE INDEX BY BINARY_INTEGER;
489 TYPE tExplosionType IS TABLE OF cz_exv_item_master.explosion_type%TYPE INDEX BY BINARY_INTEGER;
490 TYPE tPlanLevel IS TABLE OF cz_exv_item_master.plan_level%TYPE INDEX BY BINARY_INTEGER;
491 TYPE tIndivisibleFlag IS TABLE OF cz_exv_item_master.indivisible_flag%TYPE INDEX BY BINARY_INTEGER;
492 TYPE tCustomerOrderEnabledFlag IS TABLE OF cz_exv_item_master.customer_order_enabled_flag%TYPE INDEX BY BINARY_INTEGER;
493 TYPE tPrimaryUomCode IS TABLE OF cz_exv_item_master.primary_uom_code%TYPE INDEX BY BINARY_INTEGER;
494 TYPE tConfigModelType IS TABLE OF cz_exv_item_master.config_model_type%TYPE INDEX BY BINARY_INTEGER;
495 TYPE tModelType IS TABLE OF cz_exv_item_master.model_type%TYPE INDEX BY BINARY_INTEGER;
496
497 TYPE tPsNodeType IS TABLE OF cz_imp_ps_nodes.ps_node_type%TYPE INDEX BY BINARY_INTEGER;
498 TYPE tOrigSysRef IS TABLE OF cz_imp_ps_nodes.orig_sys_ref%TYPE INDEX BY BINARY_INTEGER;
499 TYPE tName IS TABLE OF cz_imp_ps_nodes.name%TYPE INDEX BY BINARY_INTEGER;
500 TYPE tMinimum IS TABLE OF cz_imp_ps_nodes.minimum%TYPE INDEX BY BINARY_INTEGER;
501 TYPE tMaximum IS TABLE OF cz_imp_ps_nodes.maximum%TYPE INDEX BY BINARY_INTEGER;
502 TYPE tTreeSeq IS TABLE OF cz_imp_ps_nodes.tree_seq%TYPE INDEX BY BINARY_INTEGER;
503 TYPE tBomTreatment IS TABLE OF cz_imp_ps_nodes.bom_treatment%TYPE INDEX BY BINARY_INTEGER;
504 TYPE tUiOmit IS TABLE OF cz_imp_ps_nodes.ui_omit%TYPE INDEX BY BINARY_INTEGER;
505 TYPE tUiSection IS TABLE OF cz_imp_ps_nodes.ui_section%TYPE INDEX BY BINARY_INTEGER;
506 TYPE tProductFlag IS TABLE OF cz_imp_ps_nodes.product_flag%TYPE INDEX BY BINARY_INTEGER;
507 TYPE tfskIntlText IS TABLE OF cz_imp_ps_nodes.fsk_intltext_1_1%TYPE INDEX BY BINARY_INTEGER;
508 TYPE tfskItemMaster IS TABLE OF cz_imp_ps_nodes.fsk_itemmaster_2_1%TYPE INDEX BY BINARY_INTEGER;
509 TYPE tfskItemMaster22 IS TABLE OF cz_imp_ps_nodes.fsk_itemmaster_2_2%TYPE INDEX BY BINARY_INTEGER;
510 TYPE tfskParentNode IS TABLE OF cz_imp_ps_nodes.fsk_psnode_3_1%TYPE INDEX BY BINARY_INTEGER;
511 TYPE tfskDevlProject IS TABLE OF cz_imp_ps_nodes.fsk_devlproject_5_1%TYPE INDEX BY BINARY_INTEGER;
512 TYPE tEffectiveFrom IS TABLE OF cz_imp_ps_nodes.effective_from%TYPE INDEX BY BINARY_INTEGER;
513 TYPE tEffectiveUntil IS TABLE OF cz_imp_ps_nodes.effective_until%TYPE INDEX BY BINARY_INTEGER;
514 TYPE tRunId IS TABLE OF cz_imp_ps_nodes.run_id%TYPE INDEX BY BINARY_INTEGER;
515 TYPE tSoItemTypeCode IS TABLE OF cz_imp_ps_nodes.so_item_type_code%TYPE INDEX BY BINARY_INTEGER;
516 TYPE tMinimumSelected IS TABLE OF cz_imp_ps_nodes.minimum_selected%TYPE INDEX BY BINARY_INTEGER;
517 TYPE tMaximumSelected IS TABLE OF cz_imp_ps_nodes.maximum_selected%TYPE INDEX BY BINARY_INTEGER;
518 TYPE tBomRequired IS TABLE OF cz_imp_ps_nodes.bom_required%TYPE INDEX BY BINARY_INTEGER;
519 TYPE tInitialValue IS TABLE OF cz_imp_ps_nodes.initial_value%TYPE INDEX BY BINARY_INTEGER;
520 TYPE tfskReference IS TABLE OF cz_imp_ps_nodes.fsk_psnode_6_1%TYPE INDEX BY BINARY_INTEGER;
521 TYPE tDecimalQtyFlag IS TABLE OF cz_imp_ps_nodes.decimal_qty_flag%TYPE INDEX BY BINARY_INTEGER;
522 TYPE tUserNum IS TABLE OF cz_imp_ps_nodes.user_num03%TYPE INDEX BY BINARY_INTEGER;
523 TYPE tQuoteableFlag IS TABLE OF cz_imp_ps_nodes.quoteable_flag%TYPE INDEX BY BINARY_INTEGER;
524 TYPE tPsPrimaryUomCode IS TABLE OF cz_imp_ps_nodes.primary_uom_code%TYPE INDEX BY BINARY_INTEGER;
525 TYPE tBomSortOrder IS TABLE OF cz_imp_ps_nodes.bom_sort_order%TYPE INDEX BY BINARY_INTEGER;
526 TYPE tComponentSequencePath IS TABLE OF cz_imp_ps_nodes.component_sequence_path%TYPE INDEX BY BINARY_INTEGER;
527 TYPE tTrackableFlag IS TABLE OF cz_imp_ps_nodes.ib_trackable%TYPE INDEX BY BINARY_INTEGER;
528 TYPE tInitNumVal IS TABLE OF cz_imp_ps_nodes.initial_num_value%TYPE INDEX BY BINARY_INTEGER;
529 TYPE tSrcApplicationId IS TABLE OF cz_imp_ps_nodes.src_application_id%TYPE INDEX BY BINARY_INTEGER;
530 TYPE tIBLinkItemFlag IS TABLE OF cz_imp_ps_nodes.ib_link_item_flag%TYPE INDEX BY BINARY_INTEGER;
531
532 -- changes for TSO --
533
534 TYPE tShippableItemFlag IS TABLE OF cz_exv_item_master.shippable_item_flag%TYPE
535 INDEX BY BINARY_INTEGER;
536 TYPE tTransEnabledFlag IS TABLE OF cz_exv_item_master.mtl_transactions_enabled_flag%TYPE
537 INDEX BY BINARY_INTEGER;
538 TYPE tReplenishToOrderFlag IS TABLE OF cz_exv_item_master.replenish_to_order_flag%TYPE
539 INDEX BY BINARY_INTEGER;
540 TYPE tSerialNumberControlCode IS TABLE OF cz_exv_item_master.serial_number_control_code%TYPE
541 INDEX BY BINARY_INTEGER;
542
543 TYPE tPSShippableItemFlag IS TABLE OF cz_imp_ps_nodes.shippable_item_flag%TYPE
544 INDEX BY BINARY_INTEGER;
545 TYPE tInventoryTransactableFlag IS TABLE OF cz_imp_ps_nodes.inventory_transactable_flag%TYPE
546 INDEX BY BINARY_INTEGER;
547 TYPE tAssembleToOrderFlag IS TABLE OF cz_imp_ps_nodes.assemble_to_order_flag%TYPE
548 INDEX BY BINARY_INTEGER;
549 TYPE tSerializableItemFlag IS TABLE OF cz_imp_ps_nodes.serializable_item_flag%TYPE
550 INDEX BY BINARY_INTEGER;
551
552 ShippableItemFlag tShippableItemFlag;
553 TransEnabledFlag tTransEnabledFlag;
554 ReplenishToOrderFlag tReplenishToOrderFlag;
555 SerialNumberControlCode tSerialNumberControlCode;
556
557 iShippableItemFlag tPSShippableItemFlag;
558 iInventoryTransactableFlag tInventoryTransactableFlag;
559 iAssembleToOrder tAssembleToOrderFlag;
560 iSerializableItemFlag tSerializableItemFlag;
561
562 -- end of changes for TSO --
563
564 n_SortWidth NUMBER := cz_imp_ps_node.n_SortWidth;
565 x_usesurr_itemmaster PLS_INTEGER:=CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_ITEM_MASTERS', 'IMPORT');
566 x_usesurr_intltext PLS_INTEGER:=CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_LOCALIZED_TEXTS', 'IMPORT');
567 x_usesurr_psnode PLS_INTEGER:=CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_PS_NODES', 'IMPORT');
568 x_usesurr_devlproject PLS_INTEGER:=CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_DEVL_PROJECTS', 'IMPORT');
569 x_error BOOLEAN:=FALSE;
570
571 ComponentSequenceId tComponentSequenceId;
572 ComponentCode tComponentCode;
573 CreationDate tCreationDate;
574 CreatedBy tCreatedBy;
575 LastUpdateDate tLastUpdateDate;
576 LastUpdatedBy tLastUpdatedBy;
577 EffectivityDate tEffectivityDate;
578 DisableDate tDisableDate;
579 Description tDescription;
580 LowQuantity tLowQuantity;
581 HighQuantity tHighQuantity;
582 SortOrder tSortOrder;
583 BomItemType tBomItemType;
584 ComponentItemId tComponentItemId;
585 MutuallyExclusiveOptions tMutuallyExclusiveOptions;
586 PickComponentsFlag tPickComponentsFlag;
587 ComponentQuantity tComponentQuantity;
588 v_Optional tOptional;
589 OrganizationId tOrganizationId;
590 TopItemId tTopItemId;
591 ExplosionType tExplosionType;
592 PlanLevel tPlanLevel;
593 IndivisibleFlag tIndivisibleFlag;
594 CustomerOrderEnabledFlag tCustomerOrderEnabledFlag;
595 PrimaryUomCode tPrimaryUomCode;
596 ConfigModelType tConfigModelType;
597 ModelType tModelType;
598 TrackableFlag tTrackableFlag;
599 SrcApplicationId tSrcApplicationId;
600 FSKItemMaster22 tfskItemMaster22;
601 IBLinkItemFlag tIBLinkItemFlag;
602
603 iComponentCode tComponentCode;
604 iPsNodeType tPsNodeType;
605 iOrigSysRef tOrigSysRef;
606 iPlanLevel tPlanLevel;
607 iName tName;
608 iMinimum tMinimum;
609 iMaximum tMaximum;
610 iTreeSeq tTreeSeq;
611 iBomTreatment tBomTreatment;
612 iUiOmit tUiOmit;
613 iUiSection tUiSection;
614 iProductFlag tProductFlag;
615 ifskIntlText tfskIntlText;
616 ifskIntlTextExt tfskIntlText;
617 ifskItemMaster tfskItemMaster;
618 ifskItemMasterExt tfskItemMaster;
619 ifskItemMaster22 tfskItemMaster22;
620 ifskParentNode tfskParentNode;
621 ifskParentNodeExt tfskParentNode;
622 ifskDevlProject tfskDevlProject;
623 ifskDevlProjectExt tfskDevlProject;
624 ifskReference tfskReference;
625 iMutuallyExclusive tMutuallyExclusiveOptions;
626 iOptional tOptional;
627 iCreationDate tCreationDate;
628 iCreatedBy tCreatedBy;
629 iLastUpdateDate tLastUpdateDate;
630 iLastUpdatedBy tLastUpdatedBy;
631 iEffectiveFrom tEffectiveFrom;
632 iEffectiveUntil tEffectiveUntil;
633 iComponentSequenceId tComponentSequenceId;
634 iRunId tRunId;
635 iSoItemTypeCode tSoItemTypeCode;
636 iMinimumSelected tMinimumSelected;
637 iMaximumSelected tMaximumSelected;
638 iBomRequired tBomRequired;
639 iInitialValue tInitialValue;
640 iOrganizationId tOrganizationId;
641 iTopItemId tTopItemId;
642 iExplosionType tExplosionType;
643 iDecimalQtyFlag tDecimalQtyFlag;
644 iQuoteableFlag tQuoteableFlag;
645 iPrimaryUomCode tPsPrimaryUomCode;
646 iBomSortOrder tBomSortOrder;
647 iComponentSequencePath tComponentSequencePath;
648 iBTrackableFlag tTrackableFlag;
649 iInitNumVal tInitNumVal;
650 iSrcApplicationId tSrcApplicationId;
651 iIBLinkItemFlag tIBLinkItemFlag;
652
653 nIndex PLS_INTEGER := 1;
654 nStack PLS_INTEGER := 1;
655 nCount PLS_INTEGER;
656 genStatisticsCz PLS_INTEGER;
657
658 l_use_defaults VARCHAR2(1);
659 l_set_decimals VARCHAR2(1);
660 l_default_dec NUMBER;
661 l_default_int NUMBER;
662
663 l_imp_decimal_qty_flag VARCHAR2(1) := UPPER(FND_PROFILE.VALUE('CZ_IMP_DECIMAL_QTY_FLAG'));
664 l_max_boundary_value NUMBER;
665
666 StackPlanLevel tPlanLevel;
667 StackComponentCode tComponentCode;
668 StackTopItemId tTopItemId;
669 StackModelType tModelType;
670 StackProcessFlag tIntegerArray;
671 AllModels tStringArray;
672 thisOrigSysRef cz_imp_ps_nodes.orig_sys_ref%TYPE;
673
674 ComponentSequencePath cz_imp_ps_nodes.component_sequence_path%TYPE;
675 previousPlanLevel PLS_INTEGER;
676
677 nDebug PLS_INTEGER;
678
679 st_time number;
680 end_time number;
681 loop_end_time number;
682 insert_end_time number;
683 d_str varchar2(255);
684 l_lang VARCHAR2(4);
685
686 MemoryBulkSize NATURAL;
687 startFlag BOOLEAN := TRUE;
688
689 v_settings_id VARCHAR2(40);
690 v_section_name VARCHAR2(30);
691 --9496782
692 v_batchSize NUMBER;
693
694
695 CURSOR c_data (inLang VARCHAR2) IS
696 SELECT
697 NVL(COMMON_COMPONENT_SEQUENCE_ID, COMPONENT_SEQUENCE_ID), COMPONENT_CODE, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
698 LAST_UPDATED_BY, EFFECTIVITY_DATE, DISABLE_DATE, ITEM_DESC, LOW_QUANTITY, HIGH_QUANTITY,
699 SORT_ORDER, BOM_ITEM_TYPE, COMPONENT_ITEM_ID, MUTUALLY_EXCLUSIVE_OPTIONS, plan_level,
700 PICK_COMPONENTS_FLAG, COMPONENT_QUANTITY, OPTIONAL, organization_id, top_item_id, explosion_type,
701 INDIVISIBLE_FLAG, CUSTOMER_ORDER_ENABLED_FLAG, PRIMARY_UOM_CODE, MODEL_TYPE, COMMS_NL_TRACKABLE_FLAG,
702 CONFIG_MODEL_TYPE, BOM_APPLICATION_ID, INV_APPLICATION_ID, IB_LINK_ITEM_FLAG,
703 DECODE(SHIPPABLE_ITEM_FLAG,'Y','1','N','0','0'),
704 DECODE(MTL_TRANSACTIONS_ENABLED_FLAG,'Y','1','N','0','0'),
705 DECODE(REPLENISH_TO_ORDER_FLAG,'Y','1','N','0','0'),
706 SERIAL_NUMBER_CONTROL_CODE
707 FROM cz_exv_item_master
708 WHERE organization_id = nOrg_ID
709 AND top_item_id = nTop_ID
710 AND explosion_type = sExpl_type
711 AND language = inLang
712 ORDER BY sort_order, component_code;
713
714 parentOrigSysRef tOrigSysRef;
715 childCount tNumberArray;
716
717 CURSOR c_parent IS
718 SELECT fsk_psnode_3_1, COUNT(*) FROM cz_imp_ps_nodes
719 WHERE run_id = inRun_ID
720 AND rec_status IS NULL
721 AND optional = OraNo
722 GROUP BY fsk_psnode_3_1;
723 ---------------------------------------------------------------------------------------------------
724 FUNCTION ShiftComponentCode(inComponentCode IN VARCHAR2, inPlanLevel IN NUMBER)
725 RETURN VARCHAR2 IS
726 BEGIN
727 IF(inPlanLevel = 0)THEN
728 RETURN inComponentCode;
729 ELSE
730 RETURN SUBSTR(inComponentCode, INSTR(inComponentCode, '-', 1, inPlanLevel) + 1);
731 END IF;
732 END;
733 ---------------------------------------------------------------------------------------------------
734 BEGIN
735
736 nDebug := 0;
737
738 v_settings_id := 'memorybulksize';
739 v_section_name := 'import';
740
741 BEGIN
742
743 SELECT TO_NUMBER(value) INTO MemoryBulkSize
744 FROM cz_db_settings
745 WHERE LOWER(setting_id) = v_settings_id
746 AND LOWER(section_name) = v_section_name;
747
748 EXCEPTION
749 WHEN OTHERS THEN
750 MemoryBulkSize := 10000000;
751 END;
752
753 l_lang := userenv('LANG');
754 OPEN c_data(l_lang);
755
756 LOOP
757
758 ComponentSequenceId.DELETE;
759 ComponentCode.DELETE;
760 CreationDate.DELETE;
761 CreatedBy.DELETE;
762 LastUpdateDate.DELETE;
763 LastUpdatedBy.DELETE;
764 EffectivityDate.DELETE;
765 DisableDate.DELETE;
766 Description.DELETE;
767 LowQuantity.DELETE;
768 HighQuantity.DELETE;
769 SortOrder.DELETE;
770 BomItemType.DELETE;
771 ComponentItemId.DELETE;
772 MutuallyExclusiveOptions.DELETE;
773 PlanLevel.DELETE;
774 PickComponentsFlag.DELETE;
775 ComponentQuantity.DELETE;
776 v_Optional.DELETE;
777 OrganizationId.DELETE;
778 TopItemId.DELETE;
779 ExplosionType.DELETE;
780 IndivisibleFlag.DELETE;
781 CustomerOrderEnabledFlag.DELETE;
782 PrimaryUomCode.DELETE;
783 ConfigModelType.DELETE;
784 ModelType.DELETE;
785 SrcApplicationId.DELETE;
786 FSKItemMaster22.DELETE;
787 IBLinkItemFlag.DELETE;
788
789 ShippableItemFlag.DELETE;
790 TransEnabledFlag.DELETE;
791 ReplenishToOrderFlag.DELETE;
792 SerialNumberControlCode.DELETE;
793
794 iName.DELETE;
795 iOrigSysRef.DELETE;
796 iMinimum.DELETE;
797 iMaximum.DELETE;
798 iTreeSeq.DELETE;
799 iPsNodeType.DELETE;
800 iBomTreatment.DELETE;
801 iUiOmit.DELETE;
802 iUiSection.DELETE;
803 iProductFlag.DELETE;
804 ifskIntlText.DELETE;
805 ifskIntlTextExt.DELETE;
806 ifskItemMaster.DELETE;
807 ifskItemMasterExt.DELETE;
808 ifskItemMaster22.DELETE;
809 ifskParentNode.DELETE;
810 ifskParentNodeExt.DELETE;
811 iMutuallyExclusive.DELETE;
812 iOptional.DELETE;
813 ifskDevlProject.DELETE;
814 ifskDevlProjectExt.DELETE;
815 iCreationDate.DELETE;
816 iCreatedBy.DELETE;
817 iLastUpdateDate.DELETE;
818 iLastUpdatedBy.DELETE;
819 iEffectiveFrom.DELETE;
820 iEffectiveUntil.DELETE;
821 iComponentSequenceId.DELETE;
822 iComponentCode.DELETE;
823 iPlanLevel.DELETE;
824 iRunId.DELETE;
825 iSoItemTypeCode.DELETE;
826 iMinimumSelected.DELETE;
827 iBomRequired.DELETE;
828 iInitialValue.DELETE;
829 iOrganizationId.DELETE;
830 iTopItemId.DELETE;
831 iExplosionType.DELETE;
832 ifskReference.DELETE;
833 iMaximumSelected.DELETE;
834 iDecimalQtyFlag.DELETE;
835 iQuoteableFlag.DELETE;
836 iPrimaryUomCode.DELETE;
837 iBomSortOrder.DELETE;
838 iComponentSequencePath.DELETE;
839 iSrcApplicationId.DELETE;
840 iIBLinkItemFlag.DELETE;
841
842 iShippableItemFlag.DELETE;
843 iInventoryTransactableFlag.DELETE;
844 iAssembleToOrder.DELETE;
845 iSerializableItemFlag.DELETE;
846
847
848 nIndex := 1;
849
850 if (CZ_IMP_ALL.get_time) then
851 st_time := dbms_utility.get_time();
852 end if;
853
854 l_lang := userenv('LANG');
855
856 FETCH c_data BULK COLLECT INTO
857 ComponentSequenceId, ComponentCode, CreationDate, CreatedBy, LastUpdateDate,
858 LastUpdatedBy, EffectivityDate, DisableDate, Description, LowQuantity, HighQuantity,
859 SortOrder, BomItemType, ComponentItemId, MutuallyExclusiveOptions, PlanLevel,
860 PickComponentsFlag, ComponentQuantity, v_Optional, OrganizationId, TopItemId, ExplosionType,
861 IndivisibleFlag, CustomerOrderEnabledFlag, PrimaryUomCode, ModelType, TrackableFlag, ConfigModelType,
862 SrcApplicationId, FSKItemMaster22, IBLinkItemFlag,
863 ShippableItemFlag, TransEnabledFlag, ReplenishToOrderFlag, SerialNumberControlCode -- changes for TSO
864 LIMIT MemoryBulkSize;
865
866 IF(ComponentItemId.COUNT = 0)THEN
867 IF(startFlag)THEN
868 --'No BOM data to extract. Verify that the bill you want to import exists on the import-enabled server.'
869 x_error:=cz_utils.log_report(CZ_UTILS.GET_TEXT('CZ_IMP_BOM_NO_DATA'),1,'CZ_IMP_SINGLE.EXTR_PS_NODE',11276,inRun_Id);
870 RETURN;
871 ELSE
872 EXIT;
873 END IF;
874 END IF;
875
876 if (CZ_IMP_ALL.get_time) then
877 end_time := dbms_utility.get_time();
878 d_str := inRun_Id || ' Extract ps structure (' || nTop_Id || ' - count - ' || componentItemId.COUNT || ' ) :' || (end_time-st_time)/100.00;
879 x_ERROR:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
880 end if;
881
882 if (CZ_IMP_ALL.get_time) then
883 st_time := dbms_utility.get_time();
884 end if;
885
886 --DBMS_OUTPUT.PUT_LINE('COUNT IS:'||ComponentItemId.COUNT);
887 FOR i IN ComponentItemId.FIRST..ComponentItemId.LAST LOOP
888
889 --Need to account for bug #1710684.
890
891 IF(EffectivityDate(i) < cz_utils.EPOCH_BEGIN_)THEN EffectivityDate(i) := cz_utils.EPOCH_BEGIN_; END IF;
892 IF(DisableDate(i) > cz_utils.EPOCH_END_)THEN DisableDate(i) := cz_utils.EPOCH_END_; END IF;
893
894 IF(PlanLevel(i) = 0)THEN
895 nStack := 1;
896 StackComponentCode(nStack) := ComponentCode(i);
897 StackTopItemId(nStack) := TopItemId(i);
898 StackPlanLevel(nStack) := 0;
899 StackProcessFlag(nStack) := 1;
900 --DBMS_OUTPUT.PUT_LINE('IF plan level ... ModelType: '||ModelType(i));
901 StackModelType(nStack) := ModelType(i);
902 END IF;
903
904 IF((startFlag AND i = ComponentItemId.FIRST) OR PlanLevel(i) = 0)THEN
905 ComponentSequencePath := NULL;
906 previousPlanLevel := 0;
907 ELSE
908 IF(previousPlanLevel < PlanLevel(i))THEN
909 IF(ComponentSequencePath IS NOT NULL)THEN ComponentSequencePath := ComponentSequencePath || '-'; END IF;
910 ComponentSequencePath := ComponentSequencePath || ComponentSequenceId(i);
911 ELSE
912 ComponentSequencePath := SUBSTR(ComponentSequencePath, 1, INSTR(ComponentSequencePath, '-', -1, previousPlanLevel - PlanLevel(i) + 1) - 1);
913 IF(ComponentSequencePath IS NOT NULL)THEN ComponentSequencePath := ComponentSequencePath || '-'; END IF;
914 ComponentSequencePath := ComponentSequencePath || ComponentSequenceId(i);
915 END IF;
916 previousPlanLevel := PlanLevel(i);
917 END IF;
918
919 --DBMS_OUTPUT.PUT_LINE('New element: i='||i||', index='||nIndex);
920 --DBMS_OUTPUT.PUT_LINE('Parameters: BomItemType='||BomItemType(i)||', ComponentCode='||ComponentCode(i));
921 --DBMS_OUTPUT.PUT_LINE('Stack: nStack='||nStack||', StackComponentCode='||StackComponentCode(nStack)||', StackTopItemId='||StackTopItemId(nStack)||', StackPlanLevel='||StackPlanLevel(nStack)||', StackProcessFlag='||StackProcessFlag(nStack));
922
923 --The dash ('-') added to the end of StackComponentCode is a fix for the bug #1956683. To be on
924 --the safe side, we are also changing INSTR(...) = 0 to be INSTR(...) <> 1, but because of the
925 --ordering by component_code, both comparisons are equivalent.
926
927 IF(INSTR(ComponentCode(i), StackComponentCode(nStack) || '-') <> 1 OR
928 (ComponentCode(i) = StackComponentCode(nStack) AND BomItemType(i) = cnModel))THEN
929
930 --DBMS_OUTPUT.PUT_LINE('End of model, stack back');
931 --DBMS_OUTPUT.PUT_LINE('PlanLevel='||PlanLevel(i));
932
933 FOR j IN PlanLevel(i)..StackPlanLevel(nStack) LOOP
934 nStack := nStack - 1;
935 END LOOP;
936 IF(nStack < 1)THEN nStack := 1; END IF;
937
938 --DBMS_OUTPUT.PUT_LINE('New StackPlanLevel='||StackPlanLevel(nStack));
939
940 END IF;
941 IF(StackProcessFlag(nStack) = 1)THEN
942 IF(BomItemType(i) = cnModel)THEN
943 IF(PlanLevel(i) > 0)THEN
944
945 --DBMS_OUTPUT.PUT_LINE('Creating reference...');
946
947 iPsNodeType(nIndex) := cnReference;
948 iComponentCode(nIndex) := ShiftComponentCode(ComponentCode(i), StackPlanLevel(nStack));
949 iComponentSequencePath(nIndex) := ComponentSequencePath;
950 iOrigSysRef(nIndex) := CZ_ORAAPPS_INTEGRATE.COMPONENT_SURROGATE_KEY(iComponentCode(nIndex),ExplosionType(i),TO_CHAR(OrganizationId(i)),TO_CHAR(StackTopItemId(nStack)));
951 iPlanLevel(nIndex) := PlanLevel(i) - StackPlanLevel(nStack);
952
953 --DBMS_OUTPUT.PUT_LINE('PlanLevel('||i||')='||PlanLevel(i));
954 --DBMS_OUTPUT.PUT_LINE('StackPlanLevel('||nStack||')='||StackPlanLevel(nStack));
955 --DBMS_OUTPUT.PUT_LINE('iPlanLevel('||nIndex||')='||iPlanLevel(nIndex));
956
957 IF(x_usesurr_psnode = 0)THEN
958 ifskParentNode(nIndex) :=
959 CZ_ORAAPPS_INTEGRATE.COMPONENT_SURROGATE_KEY(SUBSTR(iComponentCode(nIndex),1,INSTR(iComponentCode(nIndex),'-',-1,1)-1),
960 ExplosionType(i),TO_CHAR(OrganizationId(i)),TO_CHAR(StackTopItemId(nStack)));
961 ifskParentNodeExt(nIndex) := NULL;
962 ELSE
963 ifskParentNode(nIndex) := NULL;
964 ifskParentNodeExt(nIndex) :=
965 CZ_ORAAPPS_INTEGRATE.COMPONENT_SURROGATE_KEY(SUBSTR(iComponentCode(nIndex),1,INSTR(iComponentCode(nIndex),'-',-1,1)-1),
966 ExplosionType(i),TO_CHAR(OrganizationId(i)),TO_CHAR(StackTopItemId(nStack)));
967 END IF;
968
969 IF(x_usesurr_devlproject = 0)THEN
970 ifskDevlProject(nIndex) := CZ_ORAAPPS_INTEGRATE.PROJECT_SURROGATE_KEY(ExplosionType(i),TO_CHAR(OrganizationId(i)),TO_CHAR(StackTopItemId(nStack)));
971 ifskDevlProjectExt(nIndex) := NULL;
972 ELSE
973 ifskDevlProject(nIndex) := NULL;
974 ifskDevlProjectExt(nIndex) := CZ_ORAAPPS_INTEGRATE.PROJECT_SURROGATE_KEY(ExplosionType(i),TO_CHAR(OrganizationId(i)),TO_CHAR(StackTopItemId(nStack)));
975 END IF;
976
977 ifskReference(nIndex) := CZ_ORAAPPS_INTEGRATE.COMPONENT_SURROGATE_KEY(TO_CHAR(ComponentItemId(i)),ExplosionType(i),TO_CHAR(OrganizationId(i)),TO_CHAR(ComponentItemId(i)));
978 iOrganizationId(nIndex) := OrganizationId(i);
979 iTopItemId(nIndex) := StackTopItemId(nStack);
980 iExplosionType(nIndex) := ExplosionType(i);
981
982 --DBMS_OUTPUT.PUT_LINE('Reference parameters: ComponentCode='||iComponentCode(nIndex)||', Reference='||ifskReference(nIndex)||', TopItemId='||iTopItemId(nIndex));
983
984 iName(nIndex) := SUBSTR(Description(i), 1, 240);
985 iMinimum(nIndex) := 1;
986 iMaximum(nIndex) := 1;
987 iTreeSeq(nIndex) := TO_NUMBER(SUBSTR(SortOrder(i),LENGTH(SortOrder(i))-n_SortWidth+1,n_SortWidth));
988 iBomSortOrder(nIndex) := SortOrder(i);
989 iBomTreatment(nIndex) := cnNormal;
990 iUiOmit(nIndex) := '0';
991 iUiSection(nIndex) := 1;
992 iProductFlag(nIndex) := '0';
993
994 iShippableItemFlag(nIndex) := ShippableItemFlag(i);
995 iInventoryTransactableFlag(nIndex) := TransEnabledFlag(i);
996 iAssembleToOrder(nIndex) := ReplenishToOrderFlag(i);
997
998 IF SerialNumberControlCode(i)<>1 THEN
999 iSerializableItemFlag(nIndex) := '1';
1000 ELSE
1001 iSerializableItemFlag(nIndex) := '0';
1002 END IF;
1003
1004 -- Performance fix for Stuller: FSK should be orig_sys_ref, not description.
1005 IF(x_usesurr_intltext = 0)THEN
1006 -- ifskIntlText(nIndex) := Description(i);
1007 ifskIntlText(nIndex) := ComponentItemId(i) || ':' || ExplosionType(i) || ':' || OrganizationId(i) || ':'|| ComponentSequenceId(i);
1008 ifskIntlTextExt(nIndex) := NULL;
1009 ELSE
1010 ifskIntlText(nIndex) := NULL;
1011 -- ifskIntlTextExt(nIndex) := Description(i);
1012 ifskIntlTextExt(nIndex) := ComponentItemId(i) || ':' || ExplosionType(i) || ':' || OrganizationId(i) || ':'|| ComponentSequenceId(i);
1013 END IF;
1014
1015 IF(x_usesurr_itemmaster = 0)THEN
1016 ifskItemMaster(nIndex) := CZ_ORAAPPS_INTEGRATE.ITEM_SURROGATE_KEY(TO_CHAR(ComponentItemId(i)),TO_CHAR(OrganizationId(i)));
1017 ifskItemMasterExt(nIndex) := NULL;
1018 ELSE
1019 ifskItemMaster(nIndex) := NULL;
1020 ifskItemMasterExt(nIndex) := CZ_ORAAPPS_INTEGRATE.ITEM_SURROGATE_KEY(TO_CHAR(ComponentItemId(i)),TO_CHAR(OrganizationId(i)));
1021 END IF;
1022
1023 iMutuallyExclusive(nIndex) := MutuallyExclusiveOptions(i);
1024 iOptional(nIndex) := v_Optional(i);
1025 iCreationDate(nIndex) := CreationDate(i);
1026 iCreatedBy(nIndex) := CreatedBy(i);
1027 iLastUpdateDate(nIndex) := LastUpdateDate(i);
1028 iLastUpdatedBy(nIndex) := LastUpdatedBy(i);
1029 iEffectiveFrom(nIndex) := EffectivityDate(i);
1030 iEffectiveUntil(nIndex) := DisableDate(i);
1031 iComponentSequenceId(nIndex) := ComponentSequenceId(i);
1032 iRunId(nIndex) := inRun_ID;
1033 iSoItemTypeCode(nIndex) := NULL;
1034
1035 --minimum_selected, maximum_selected of the reference inherit the values of minimum, maximum
1036 --of the child model because it's nominal minimum, maximum will be 0, -1.
1037
1038 IF(LowQuantity(i) IS NULL)THEN
1039 IF g_CONFIG_ENGINE_TYPE='F' THEN
1040 -- this will be converted to default value later
1041 iMinimumSelected(nIndex) := NULL;
1042 ELSE
1043 iMinimumSelected(nIndex) := 0;
1044 END IF;
1045 ELSE
1046 iMinimumSelected(nIndex) := LowQuantity(i);
1047 END IF;
1048
1049 IF(HighQuantity(i) IS NULL OR HighQuantity(i) = 0)THEN
1050 iMaximumSelected(nIndex) := -1;
1051 ELSE
1052 iMaximumSelected(nIndex) := HighQuantity(i);
1053 END IF;
1054
1055 iPrimaryUomCode(nIndex) := PrimaryUomCode(i);
1056 iQuoteableFlag(nIndex) := '1';
1057 IF(CustomerOrderEnabledFlag(i) = 'N')THEN
1058 IF(BomItemType(i) <> 4)THEN
1059 iQuoteableFlag(nIndex) := '0';
1060 END IF;
1061 END IF;
1062
1063 IF(UPPER(FND_PROFILE.VALUE('CZ_IMP_DECIMAL_QTY_FLAG')) = 'Y')THEN
1064
1065 IF(IndivisibleFlag(i) = 'Y')THEN
1066 iDecimalQtyFlag(nIndex) := '0';
1067 ELSIF(StackModelType(nStack) = 'A' AND (BomItemType(i) = cnStandard OR (BomItemType(i) = cnOptionClass AND allowDecimalOptionClass = 1)))THEN
1068 iDecimalQtyFlag(nIndex) := '1';
1069 ELSE
1070 iDecimalQtyFlag(nIndex) := '0';
1071 END IF;
1072 ELSE
1073 iDecimalQtyFlag(nIndex) := '0';
1074 END IF;
1075
1076 IF(v_Optional(i) = OraNo)THEN
1077 iBomRequired(nIndex) := '1';
1078 ELSE
1079 iBomRequired(nIndex) := '0';
1080 END IF;
1081
1082 IF(ComponentQuantity(i) IS NULL OR ComponentQuantity(i) = 0)THEN
1083 iInitNumVal(nIndex) := 1;
1084 ELSE
1085 iInitNumVal(nIndex) := ComponentQuantity(i);
1086 END IF;
1087
1088 IF(TrackableFlag(i) = 'Y')THEN
1089 iBTrackableFlag(nIndex) := '1';
1090 ELSE
1091 iBTrackableFlag(nIndex) := '0';
1092 END IF;
1093 iSrcApplicationId(nIndex) := SrcApplicationId(i);
1094 ifskItemMaster22(nIndex) := FSKItemMaster22(i);
1095 iIBLinkItemFlag(nIndex) := IBLinkItemFlag(i);
1096 nIndex := nIndex + 1;
1097
1098 END IF;
1099
1100 IF(i > ComponentItemId.FIRST OR (NOT startFlag))THEN
1101 nStack := nStack + 1;
1102 StackComponentCode(nStack) := ComponentCode(i);
1103 StackTopItemId(nStack) := ComponentItemId(i);
1104 StackPlanLevel(nStack) := PlanLevel(i);
1105 StackProcessFlag(nStack) := 1;
1106 --DBMS_OUTPUT.PUT_LINE('IF i> comp ... ModelType: '||ModelType(i));
1107 StackModelType(nStack) := ModelType(i);
1108
1109 END IF;
1110 thisOrigSysRef := CZ_ORAAPPS_INTEGRATE.COMPONENT_SURROGATE_KEY(TO_CHAR(ComponentItemId(i)),ExplosionType(i),TO_CHAR(OrganizationId(i)),TO_CHAR(ComponentItemId(i)));
1111
1112 --Check if the model has already been processed in this session
1113
1114 --DBMS_OUTPUT.PUT_LINE('Verifying model...');
1115
1116 IF(PlanLevel(i) > 0)THEN
1117
1118 --DBMS_OUTPUT.PUT_LINE('Child model - skip...');
1119
1120 StackProcessFlag(nStack) := 0;
1121 ELSIF(AllModels.LAST IS NOT NULL)THEN
1122 FOR j IN AllModels.FIRST..AllModels.LAST LOOP
1123 IF(AllModels(j) = thisOrigSysRef)THEN
1124 StackProcessFlag(nStack) := 0;
1125
1126 --DBMS_OUTPUT.PUT_LINE('Model already exists...');
1127
1128 EXIT;
1129 END IF;
1130 END LOOP;
1131 END IF;
1132
1133 IF(StackProcessFlag(nStack) = 1)THEN
1134 AllModels(NVL(AllModels.LAST, 0) + 1) := thisOrigSysRef;
1135 END IF;
1136 END IF;
1137
1138 IF(StackProcessFlag(nStack) = 1)THEN
1139
1140 --DBMS_OUTPUT.PUT_LINE('Processing simple node...');
1141
1142 IF(BomItemType(i) = cnModel)THEN
1143 iPsNodeType(nIndex) := bomModel;
1144 ELSIF(BomItemType(i) = cnOptionClass)THEN
1145 iPsNodeType(nIndex) := bomOptionClass;
1146 ELSIF(BomItemType(i) = cnStandard)THEN
1147 iPsNodeType(nIndex) := bomStandard;
1148 END IF;
1149 iComponentCode(nIndex) := ShiftComponentCode(ComponentCode(i), StackPlanLevel(nStack));
1150 iComponentSequencePath(nIndex) := ComponentSequencePath;
1151 iOrigSysRef(nIndex) := CZ_ORAAPPS_INTEGRATE.COMPONENT_SURROGATE_KEY(iComponentCode(nIndex),ExplosionType(i),TO_CHAR(OrganizationId(i)),TO_CHAR(StackTopItemId(nStack)));
1152 iPlanLevel(nIndex) := PlanLevel(i) - StackPlanLevel(nStack);
1153
1154 --DBMS_OUTPUT.PUT_LINE('PlanLevel('||i||')='||PlanLevel(i));
1155 --DBMS_OUTPUT.PUT_LINE('StackPlanLevel('||nStack||')='||StackPlanLevel(nStack));
1156 --DBMS_OUTPUT.PUT_LINE('iPlanLevel('||nIndex||')='||iPlanLevel(nIndex));
1157
1158 IF(x_usesurr_psnode = 0)THEN
1159 IF(BomItemType(i) = cnModel)THEN
1160 ifskParentNode(nIndex) := NULL;
1161 ELSE
1162 ifskParentNode(nIndex) :=
1163 CZ_ORAAPPS_INTEGRATE.COMPONENT_SURROGATE_KEY(SUBSTR(iComponentCode(nIndex),1,INSTR(iComponentCode(nIndex),'-',-1,1)-1),
1164 ExplosionType(i),TO_CHAR(OrganizationId(i)),TO_CHAR(StackTopItemId(nStack)));
1165 END IF;
1166 ifskParentNodeExt(nIndex) := NULL;
1167 ELSE
1168 ifskParentNode(nIndex) := NULL;
1169 IF(BomItemType(i) = cnModel)THEN
1170 ifskParentNodeExt(nIndex) := NULL;
1171 ELSE
1172 ifskParentNodeExt(nIndex) :=
1173 CZ_ORAAPPS_INTEGRATE.COMPONENT_SURROGATE_KEY(SUBSTR(iComponentCode(nIndex),1,INSTR(iComponentCode(nIndex),'-',-1,1)-1),
1174 ExplosionType(i),TO_CHAR(OrganizationId(i)),TO_CHAR(StackTopItemId(nStack)));
1175 END IF;
1176 END IF;
1177
1178 IF(x_usesurr_devlproject = 0)THEN
1179 ifskDevlProject(nIndex) := CZ_ORAAPPS_INTEGRATE.PROJECT_SURROGATE_KEY(ExplosionType(i),TO_CHAR(OrganizationId(i)),TO_CHAR(StackTopItemId(nStack)));
1180 ifskDevlProjectExt(nIndex) := NULL;
1181 ELSE
1182 ifskDevlProject(nIndex) := NULL;
1183 ifskDevlProjectExt(nIndex) := CZ_ORAAPPS_INTEGRATE.PROJECT_SURROGATE_KEY(ExplosionType(i),TO_CHAR(OrganizationId(i)),TO_CHAR(StackTopItemId(nStack)));
1184 END IF;
1185
1186 ifskReference(nIndex) := NULL;
1187 iOrganizationId(nIndex) := OrganizationId(i);
1188 iTopItemId(nIndex) := StackTopItemId(nStack);
1189 iExplosionType(nIndex) := ExplosionType(i);
1190
1191 iName(nIndex) := SUBSTR(Description(i), 1, 240);
1192
1193 IF(BomItemType(i) = cnModel)THEN
1194
1195 --This 'child' model is becoming a 'root' model so it's minimum, maximum should be 0, -1
1196 --The real values have been preserved in the corresponding reference's minimum_, maximum_selected.
1197
1198 iMinimum(nIndex) := 0;
1199 iMaximum(nIndex) := -1;
1200 iTreeSeq(nIndex) := 1;
1201 iBomSortOrder(nIndex) := LPAD('1', n_SortWidth, '0');
1202 ELSE
1203 IF(LowQuantity(i) IS NULL)THEN
1204 IF g_CONFIG_ENGINE_TYPE='F' THEN
1205 -- this will be converted to default value later
1206 iMinimum(nIndex) := NULL;
1207 ELSE
1208 iMinimum(nIndex) := 0;
1209 END IF;
1210 ELSE
1211 iMinimum(nIndex) := LowQuantity(i);
1212 END IF;
1213 IF(HighQuantity(i) IS NULL OR HighQuantity(i) = 0)THEN
1214 iMaximum(nIndex) := -1;
1215 ELSE
1216 iMaximum(nIndex) := HighQuantity(i);
1217 END IF;
1218 iTreeSeq(nIndex) := TO_NUMBER(SUBSTR(SortOrder(i),LENGTH(SortOrder(i))-n_SortWidth+1,n_SortWidth));
1219 iBomSortOrder(nIndex) := SortOrder(i);
1220 END IF;
1221
1222 IF(BomItemType(i) = cnStandard AND v_Optional(i) = OraNo)THEN
1223 iBomTreatment(nIndex) := cnSkip;
1224 ELSE
1225 iBomTreatment(nIndex) := cnNormal;
1226 END IF;
1227
1228 iUiOmit(nIndex) := '0';
1229 iUiSection(nIndex) := 1;
1230
1231 IF(BomItemType(i) = cnModel)THEN
1232 iProductFlag(nIndex) := '1';
1233 ELSE
1234 iProductFlag(nIndex) := '0';
1235 END IF;
1236
1237 iShippableItemFlag(nIndex) := ShippableItemFlag(i);
1238 iInventoryTransactableFlag(nIndex) := TransEnabledFlag(i);
1239 iAssembleToOrder(nIndex) := ReplenishToOrderFlag(i);
1240
1241 IF SerialNumberControlCode(i)<>1 THEN
1242 iSerializableItemFlag(nIndex) := '1';
1243 ELSE
1244 iSerializableItemFlag(nIndex) := '0';
1245 END IF;
1246
1247 IF(x_usesurr_intltext = 0)THEN
1248 -- ifskIntlText(nIndex) := Description(i);
1249 ifskIntlText(nIndex) := ComponentItemId(i) || ':' || ExplosionType(i) || ':' || OrganizationId(i) || ':'|| ComponentSequenceId(i);
1250 ifskIntlTextExt(nIndex) := NULL;
1251 ELSE
1252 ifskIntlText(nIndex) := NULL;
1253 ifskIntlTextExt(nIndex) := ComponentItemId(i) || ':' || ExplosionType(i) || ':' || OrganizationId(i) || ':'|| ComponentSequenceId(i);
1254 -- ifskIntlTextExt(nIndex) := Description(i);
1255 END IF;
1256
1257 IF(x_usesurr_itemmaster = 0)THEN
1258 ifskItemMaster(nIndex) := CZ_ORAAPPS_INTEGRATE.ITEM_SURROGATE_KEY(TO_CHAR(ComponentItemId(i)),TO_CHAR(OrganizationId(i)));
1259 ifskItemMasterExt(nIndex) := NULL;
1260 ELSE
1261 ifskItemMaster(nIndex) := NULL;
1262 ifskItemMasterExt(nIndex) := CZ_ORAAPPS_INTEGRATE.ITEM_SURROGATE_KEY(TO_CHAR(ComponentItemId(i)),TO_CHAR(OrganizationId(i)));
1263 END IF;
1264
1265 iMutuallyExclusive(nIndex) := MutuallyExclusiveOptions(i);
1266 iOptional(nIndex) := v_Optional(i);
1267 iCreationDate(nIndex) := CreationDate(i);
1268 iCreatedBy(nIndex) := CreatedBy(i);
1269 iLastUpdateDate(nIndex) := LastUpdateDate(i);
1270 iLastUpdatedBy(nIndex) := LastUpdatedBy(i);
1271
1272 IF(BomItemType(i) = cnModel)THEN
1273 iEffectiveFrom(nIndex) := CZ_UTILS.EPOCH_BEGIN;
1274 iEffectiveUntil(nIndex) := CZ_UTILS.EPOCH_END;
1275 iComponentSequenceId(nIndex) := NULL;
1276 ELSE
1277 iEffectiveFrom(nIndex) := EffectivityDate(i);
1278 iEffectiveUntil(nIndex) := DisableDate(i);
1279 iComponentSequenceId(nIndex) := ComponentSequenceId(i);
1280 END IF;
1281
1282 iRunId(nIndex) := inRun_ID;
1283
1284 IF(BomItemType(i) = cnModel)THEN
1285 iSoItemTypeCode(nIndex) := 'MODEL';
1286 ELSIF(BomItemType(i) = cnModel)THEN
1287 iSoItemTypeCode(nIndex) := 'CLASS';
1288 ELSE
1289 IF(PickComponentsFlag(i) = 'Y')THEN
1290 iSoItemTypeCode(nIndex) := 'KIT';
1291 ELSE
1292 iSoItemTypeCode(nIndex) := 'STANDARD';
1293 END IF;
1294 END IF;
1295
1296 iMinimumSelected(nIndex) := 0;
1297 iMaximumSelected(nIndex) := NULL;
1298 iPrimaryUomCode(nIndex) := PrimaryUomCode(i);
1299 iQuoteableFlag(nIndex) := '1';
1300 IF(CustomerOrderEnabledFlag(i) = 'N')THEN
1301 IF(BomItemType(i) <> 4)THEN
1302 iQuoteableFlag(nIndex) := '0';
1303 END IF;
1304 END IF;
1305
1306 IF(UPPER(FND_PROFILE.VALUE('CZ_IMP_DECIMAL_QTY_FLAG')) = 'Y')THEN
1307
1308 IF(IndivisibleFlag(i) = 'Y')THEN
1309 iDecimalQtyFlag(nIndex) := '0';
1310 ELSIF(StackModelType(nStack) = 'A' AND (BomItemType(i) = cnStandard OR (BomItemType(i) = cnOptionClass AND allowDecimalOptionClass = 1)))THEN
1311 iDecimalQtyFlag(nIndex) := '1';
1312 ELSE
1313 iDecimalQtyFlag(nIndex) := '0';
1314 END IF;
1315 ELSE
1316 iDecimalQtyFlag(nIndex) := '0';
1317 END IF;
1318
1319 IF(v_Optional(i) = OraNo)THEN
1320 iBomRequired(nIndex) := '1';
1321 ELSE
1322 iBomRequired(nIndex) := '0';
1323 END IF;
1324
1325 IF(ComponentQuantity(i) IS NULL OR ComponentQuantity(i) = 0)THEN
1326 iInitNumVal(nIndex) := 1;
1327 ELSE
1328 iInitNumVal(nIndex) := ComponentQuantity(i);
1329 END IF;
1330
1331 IF(TrackableFlag(i) = 'Y')THEN
1332 iBTrackableFlag(nIndex) := '1';
1333 ELSE
1334 iBTrackableFlag(nIndex) := '0';
1335 END IF;
1336 iSrcApplicationId(nIndex) := SrcApplicationId(i);
1337 ifskItemMaster22(nIndex) := FSKItemMaster22(i);
1338 iIBLinkItemFlag(nIndex) := IBLinkItemFlag(i);
1339 nIndex := nIndex + 1;
1340
1341 END IF;
1342 END IF;
1343 END LOOP;
1344
1345 if (CZ_IMP_ALL.get_time) then
1346 end_time := dbms_utility.get_time();
1347 d_str := inRun_Id || ' loop ps structure (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
1348 x_error:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
1349 end if;
1350
1351 if (CZ_IMP_ALL.get_time) then
1352 st_time := dbms_utility.get_time();
1353 end if;
1354
1355
1356
1357 --
1358 -- changes for Solver --
1359 --
1360 IF g_CONFIG_ENGINE_TYPE='F' THEN
1361
1362 l_use_defaults := FND_PROFILE.VALUE('CZ_BOM_DEFAULT_QTY_DOMN');
1363 l_set_decimals := FND_PROFILE.VALUE('CZ_IMP_DECIMAL_QTY_FLAG');
1364 l_default_dec := FND_PROFILE.VALUE('CZ_DEFAULT_MAX_QTY_DEC');
1365 l_default_int := FND_PROFILE.VALUE('CZ_DEFAULT_MAX_QTY_INT');
1366
1367 FOR j IN 1..iOrigSysRef.COUNT
1368 LOOP
1369 -- this a special case of root of BOM Model
1370 IF iSoItemTypeCode(j) = 'MODEL' THEN
1371 iMINIMUM(j) := 0;
1372 iMAXIMUM(j) := -1;
1373 GOTO CONTINUE;
1374 END IF;
1375 IF iPsNodeType(j) = 263 THEN -- reference
1376 setFCEMinMax (minVal => iMINIMUMSELECTED(j),
1377 maxVal => iMAXIMUMSELECTED(j),
1378 defaultVal => iInitNumVal(j),
1379 p_decimal_item_flag => iDecimalQtyFlag(j),
1380 p_use_defaults => l_use_defaults,
1381 p_set_decimals => l_set_decimals,
1382 p_default_dec => l_default_dec,
1383 p_default_int => l_default_int );
1384 ELSE -- not a reference
1385 setFCEMinMax (minVal => iMINIMUM(j),
1386 maxVal => iMAXIMUM(j),
1387 defaultVal => iInitNumVal(j),
1388 p_decimal_item_flag => iDecimalQtyFlag(j),
1389 p_use_defaults => l_use_defaults,
1390 p_set_decimals => l_set_decimals,
1391 p_default_dec => l_default_dec,
1392 p_default_int => l_default_int );
1393
1394 END IF;
1395 <<CONTINUE>> NULL;
1396 END LOOP;
1397 END IF;
1398
1399 FORALL i IN 1..iOrigSysRef.COUNT
1400 INSERT INTO cz_imp_ps_nodes
1401 (NAME, ORIG_SYS_REF, MINIMUM, MAXIMUM, TREE_SEQ, PS_NODE_TYPE, BOM_TREATMENT, UI_OMIT,
1402 UI_SECTION, PRODUCT_FLAG, FSK_INTLTEXT_1_1, FSK_INTLTEXT_1_EXT, FSK_ITEMMASTER_2_1,
1403 FSK_ITEMMASTER_2_EXT, FSK_PSNODE_3_1, FSK_PSNODE_3_EXT, MUTUALLY_EXCLUSIVE_OPTIONS, OPTIONAL,
1404 FSK_DEVLPROJECT_5_1, FSK_DEVLPROJECT_5_EXT, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
1405 LAST_UPDATED_BY, EFFECTIVE_FROM, EFFECTIVE_UNTIL, COMPONENT_SEQUENCE_ID, COMPONENT_CODE,
1406 PLAN_LEVEL, RUN_ID, SO_ITEM_TYPE_CODE, MINIMUM_SELECTED, BOM_REQUIRED, initial_num_value,
1407 ORGANIZATION_ID, TOP_ITEM_ID, EXPLOSION_TYPE, fsk_psnode_6_1, MAXIMUM_SELECTED,
1408 DECIMAL_QTY_FLAG, QUOTEABLE_FLAG,PRIMARY_UOM_CODE,BOM_SORT_ORDER,
1409 COMPONENT_SEQUENCE_PATH,IB_TRACKABLE,SRC_APPLICATION_ID, FSK_ITEMMASTER_2_2, IB_LINK_ITEM_FLAG,
1410 SHIPPABLE_ITEM_FLAG,INVENTORY_TRANSACTABLE_FLAG, ASSEMBLE_TO_ORDER_FLAG,SERIALIZABLE_ITEM_FLAG) -- changes for TSO
1411 VALUES
1412 (iName(i), iOrigSysRef(i), iMinimum(i), iMaximum(i), iTreeSeq(i), iPsNodeType(i), iBomTreatment(i),
1413 iUiOmit(i), iUiSection(i), iProductFlag(i), ifskIntlText(i), ifskIntlTextExt(i), ifskItemMaster(i),
1414 ifskItemMasterExt(i), ifskParentNode(i), ifskParentNodeExt(i), iMutuallyExclusive(i), iOptional(i),
1415 ifskDevlProject(i), ifskDevlProjectExt(i), iCreationDate(i), iCreatedBy(i), iLastUpdateDate(i),
1416 iLastUpdatedBy(i), iEffectiveFrom(i), iEffectiveUntil(i), iComponentSequenceId(i),
1417 iComponentCode(i), iPlanLevel(i), iRunId(i), iSoItemTypeCode(i), iMinimumSelected(i),
1418 iBomRequired(i), iInitNumVal(i), iOrganizationId(i), iTopItemId(i), iExplosionType(i),
1419 ifskReference(i), iMaximumSelected(i), iDecimalQtyFlag(i),
1420 iQuoteableFlag(i),iPrimaryUomCode(i),iBomSortOrder(i), iComponentSequencePath(i),iBTrackableFlag(i),
1421 iSrcApplicationId(i), ifskItemMaster22(i), iIBLinkItemFlag(i),
1422 iShippableItemFlag(i), iInventoryTransactableFlag(i), iAssembleToOrder(i), iSerializableItemFlag(i)); -- changes for TSO
1423
1424 if (CZ_IMP_ALL.get_time) then
1425 end_time := dbms_utility.get_time();
1426 d_str := inRun_Id || ' Insert ps nodes (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
1427 x_error:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
1428 end if;
1429
1430 -- 9496782
1431 czGatherStatsCnt := czGatherStatsCnt + iOrigSysRef.COUNT ;
1432
1433 COMMIT;
1434 startFlag := FALSE;
1435 END LOOP; -- FETCH c_data BULK COLLECT
1436 CLOSE c_data;
1437
1438 --Depending on db setting, generate the statistics here for the tables used in the queries bellow.
1439
1440 v_settings_id := 'GENSTATISTICSCZ';
1441 v_section_name := 'IMPORT';
1442
1443 BEGIN
1444 SELECT decode(upper(VALUE),'TRUE','1','FALSE','0','T','1','F','0','1','1','0','0','YES','1','NO','0','Y','1','N','0','0')
1445 INTO genStatisticsCz FROM CZ_DB_SETTINGS
1446 WHERE upper(SETTING_ID)=v_settings_id AND SECTION_NAME=v_section_name;
1447 EXCEPTION
1448 WHEN OTHERS THEN
1449 genStatisticsCz := 0;
1450 END;
1451
1452 IF(genStatisticsCz = 1)THEN
1453 --bug 9496782 make this call conditionalize which is really helpful when there are lot of references
1454 -- and gather stats will run after every 10000+ (batchsize setting) nodes range
1455
1456 --9496782 Get BatchSize
1457
1458 v_settings_id := 'BATCHSIZE';
1459 v_section_name := 'SCHEMA';
1460
1461 BEGIN
1462 SELECT VALUE
1463 INTO v_batchSize FROM CZ_DB_SETTINGS
1464 WHERE upper(SETTING_ID)=v_settings_id AND SECTION_NAME=v_section_name;
1465 EXCEPTION
1466 WHEN OTHERS THEN
1467 v_batchSize := 5000;
1468 END;
1469
1470 -- Due to bug 6749205, inPlanLevel is still not available in this
1471 -- version so changing original IF condition . Once that is fixed
1472 -- uncomment following line to replace existing IF condition with new one.
1473 --IF (czGatherStatsCnt > v_batchSize OR inPlanLevel = 0) THEN
1474 IF (czGatherStatsCnt > v_batchSize) THEN
1475 czGatherStatsCnt := 0;
1476 x_error:=cz_utils.log_report('Gather Stats : Start' ,1,'EXTRACTION',11299,inRun_Id);
1477 fnd_stats.gather_table_stats('CZ', 'CZ_IMP_PS_NODES');
1478 --bug 9496782 comment out following call
1479 -- fnd_stats.gather_table_stats('CZ', 'CZ_PS_NODES');
1480 fnd_stats.gather_table_stats('CZ', 'CZ_XFR_PROJECT_BILLS');
1481 x_error:=cz_utils.log_report('Gather Stats : End',1,'EXTRACTION',11299,inRun_Id);
1482
1483 if (CZ_IMP_ALL.get_time) then
1484 end_time := dbms_utility.get_time();
1485 d_str := inRun_Id || ' Gather Stats :' || (end_time-st_time)/100.00;
1486 x_error:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
1487 end if;
1488 END IF;
1489 END IF;
1490
1491 --AT: The calculation of maximum_selected is moved here as we do reading into memory in batches and cannot
1492 --calculate this inside the cycle which is possibly incomplete.
1493
1494 BEGIN
1495
1496 --Bug #2737004. The cursor doesn't return records for option classes with only optional children.
1497 --As a result, such option classes are missing from the update.
1498
1499 UPDATE cz_imp_ps_nodes SET maximum_selected = 1
1500 WHERE run_id = inRun_ID
1501 AND rec_status IS NULL
1502 AND mutually_exclusive_options = OraYes
1503 AND ps_node_type = bomOptionClass;
1504
1505 COMMIT;
1506
1507 OPEN c_parent;
1508 LOOP
1509
1510 parentOrigSysRef.DELETE;
1511 childCount.DELETE;
1512
1513 FETCH c_parent BULK COLLECT INTO parentOrigSysRef, childCount LIMIT MemoryBulkSize;
1514 EXIT WHEN childCount.COUNT = 0;
1515
1516 FORALL i IN 1..childCount.COUNT
1517 UPDATE cz_imp_ps_nodes SET maximum_selected = childCount(i) + 1
1518 WHERE run_id = inRun_ID
1519 AND rec_status IS NULL
1520 AND mutually_exclusive_options = OraYes
1521 AND orig_sys_ref = parentOrigSysRef(i);
1522
1523 COMMIT;
1524 END LOOP;
1525 CLOSE c_parent;
1526
1527 EXCEPTION
1528 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
1529 RAISE;
1530 WHEN OTHERS THEN
1531 IF(c_parent%ISOPEN)THEN CLOSE c_parent; END IF;
1532 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
1533 x_error:=cz_utils.log_report(d_str,1,'CZ_IMP_SINGLE.maximum_selected',11276,inRun_Id);
1534 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
1535 END;
1536
1537 ------------------------------------------------------------------------------
1538 ---Check the online PS_NODE table for obsolete ps_nodes, i.e. ps_nodes -
1539 ---which are not present in the current import data under the current project,-
1540 ---and so their corresponding BOMs were likely deleted in Oracle Applications.-
1541 ---For such ps_nodes DELETED_FLAG field should be set to '1'. -
1542 ---BOM_EXPLOSIONS is supposed to be correct, and so no verification is done -
1543 ---for parent-child relationships after some of the ps_nodes are marked as -
1544 ---deleted. -
1545 -------------------------------------------------------------------------------
1546
1547 DECLARE
1548 CURSOR c_onl_ps_node IS
1549 SELECT B.ORIG_SYS_REF, B.COMPONENT_SEQUENCE_PATH, B.ps_node_id, B.ps_node_type
1550 FROM CZ_XFR_PROJECT_BILLS P,CZ_PS_NODES B
1551 WHERE B.DEVL_PROJECT_ID=P.model_ps_node_id
1552 AND P.organization_id = nORg_ID
1553 AND P.top_item_id = nTop_ID
1554 AND P.explosion_type = sExpl_type
1555 AND P.DELETED_FLAG='0'
1556 AND B.DELETED_FLAG='0'
1557 AND B.ORIG_SYS_REF IS NOT NULL
1558 AND B.PS_NODE_TYPE <> 259
1559 AND B.devl_project_id = nModelId
1560 FOR UPDATE OF B.DELETED_FLAG;
1561
1562 x_onl_ps_node_f BOOLEAN:=FALSE;
1563 x_imp_ps_node_f BOOLEAN:=FALSE;
1564 sOnlOrigSysRef CZ_PS_NODES.ORIG_SYS_REF%TYPE;
1565 v_PsNodeId CZ_PS_NODES.PS_NODE_ID%TYPE;
1566 v_PsNodeType CZ_PS_NODES.PS_NODE_TYPE%TYPE;
1567 v_SequencePath CZ_PS_NODES.COMPONENT_SEQUENCE_PATH%TYPE;
1568 cDefaultChar CHAR(1);
1569 p_out_err INTEGER;
1570
1571 BEGIN
1572
1573 if (CZ_IMP_ALL.get_time) then
1574 st_time := dbms_utility.get_time();
1575 end if;
1576
1577 OPEN c_onl_ps_node;
1578
1579 LOOP
1580
1581 sOnlOrigSysRef:=NULL;
1582 FETCH c_onl_ps_node INTO sOnlOrigSysRef, v_SequencePath, v_PsNodeId, v_PsNodeType;
1583 x_onl_ps_node_f:=c_onl_ps_node%FOUND;
1584 EXIT WHEN NOT x_onl_ps_node_f;
1585
1586 DECLARE
1587 CURSOR c_imp_ps_node IS
1588 SELECT 'F' FROM CZ_IMP_PS_NODES
1589 WHERE ORIG_SYS_REF=sOnlOrigSysRef AND RUN_ID=inRun_ID
1590 AND NVL(COMPONENT_SEQUENCE_PATH, -1) = NVL(v_SequencePath, -1)
1591 AND REC_STATUS IS NULL;
1592 BEGIN
1593 OPEN c_imp_ps_node;
1594 FETCH c_imp_ps_node INTO cDefaultChar;
1595 x_imp_ps_node_f:=c_imp_ps_node%FOUND;
1596 CLOSE c_imp_ps_node;
1597 END;
1598
1599 IF(NOT x_imp_ps_node_f) THEN
1600 UPDATE CZ_PS_NODES SET
1601 DELETED_FLAG='1'
1602 WHERE CURRENT OF c_onl_ps_node;
1603
1604 --Here to call cz_refs api
1605 IF(v_PsNodeType IN (bomModel, cnReference))THEN
1606 cz_refs.delete_Node(v_PsNodeId, v_PsNodeType, p_out_err, '1');
1607 IF (p_out_err > 0) THEN
1608 BEGIN
1609 SELECT message INTO d_str FROM cz_db_logs WHERE run_id = p_out_err;
1610 EXCEPTION
1611 WHEN NO_DATA_FOUND THEN
1612 d_str := NULL;
1613 END;
1614 RAISE CZ_REFS_DELNODE_EXCP;
1615 END IF;
1616 END IF;
1617
1618 END IF;
1619
1620 END LOOP;
1621
1622 if (CZ_IMP_ALL.get_time) then
1623 end_time := dbms_utility.get_time();
1624 d_str := inRun_Id || ' Deleted ps node check (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
1625 x_error:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
1626 end if;
1627
1628 CLOSE c_onl_ps_node;
1629 COMMIT;
1630
1631 EXCEPTION
1632 WHEN CZ_REFS_DELNODE_EXCP THEN
1633 IF d_str IS NULL THEN d_str := 'NO MESSAGE FOUND'; END IF;
1634 d_str := CZ_UTILS.GET_TEXT('CZ_IMP_CZREFS_DELNODE', 'MSG', d_str);
1635 x_error :=cz_utils.log_report(d_str,1,'CZ_IMP_SINGLE.EXTR_PS_NODE: delete obsolete nodes',11276,inRun_Id);
1636 ROLLBACK;
1637 RAISE;
1638 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
1639 ROLLBACK;
1640 RAISE;
1641 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
1642 RAISE;
1643 WHEN OTHERS THEN
1644 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
1645 x_error:=cz_utils.log_report(d_str,1,'CZ_IMP_SINGLE.EXTR_PS_NODE: delete obsolete nodes',11276,inRun_Id);
1646 ROLLBACK;
1647 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
1648 END;
1649
1650 EXCEPTION
1651 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
1652 RAISE;
1653 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
1654 RAISE;
1655 WHEN OTHERS THEN
1656 IF(c_data%ISOPEN)THEN CLOSE c_data; END IF;
1657 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
1658 x_error:=cz_utils.log_report(d_str,1,'CZ_IMP_SINGLE.EXTR_PS_NODE',11276,inRun_Id);
1659 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
1660 END;
1661 ------------------------------------------------------------------------------------------
1662 PROCEDURE EXTR_INTL_TEXT(inRun_ID IN PLS_INTEGER,
1663 nOrg_ID IN NUMBER,
1664 nTop_ID IN NUMBER,
1665 sExpl_type IN VARCHAR2,
1666 nModelId IN NUMBER)
1667 IS
1668 xERROR BOOLEAN:=FALSE;
1669
1670 TYPE tCompSeqId IS TABLE OF cz_exv_intl_text.component_sequence_id%TYPE INDEX BY BINARY_INTEGER;
1671 TYPE tOrgId IS TABLE OF cz_exv_intl_text.organization_id%TYPE INDEX BY BINARY_INTEGER;
1672 TYPE tCompItemId IS TABLE OF cz_exv_intl_text.component_item_id%TYPE INDEX BY BINARY_INTEGER;
1673 TYPE tCompCode IS TABLE OF cz_exv_intl_text.component_code%TYPE INDEX BY BINARY_INTEGER;
1674
1675 TYPE tOrigSysRef IS TABLE OF cz_imp_localized_texts.orig_sys_ref%TYPE INDEX BY BINARY_INTEGER;
1676 TYPE tLocalizedStr IS TABLE OF cz_imp_localized_texts.localized_str%TYPE INDEX BY BINARY_INTEGER;
1677 TYPE tLanguage IS TABLE OF cz_imp_localized_texts.language%TYPE INDEX BY BINARY_INTEGER;
1678 TYPE tSrcLang IS TABLE OF cz_imp_localized_texts.source_lang%TYPE INDEX BY BINARY_INTEGER;
1679 TYPE tFSKDevlProject IS TABLE OF cz_imp_localized_texts.fsk_devlproject_1_1%TYPE INDEX BY BINARY_INTEGER;
1680
1681 TYPE tTextStr IS TABLE OF cz_imp_intl_text.text_str%TYPE INDEX BY BINARY_INTEGER;
1682
1683 CompSeqId tCompSeqId;
1684 OrgId tOrgId;
1685 CompCode tCompCode;
1686 CompItemId tCompItemId;
1687
1688 iOrigSysRef tOrigSysRef;
1689 iLocalizedStr tLocalizedStr;
1690 iLanguage tLanguage;
1691 iSrcLang tSrcLang;
1692 -- iFSKDevlProject tFSKDevlProject;
1693 iFSKDevlProject cz_imp_localized_texts.fsk_devlproject_1_1%TYPE;
1694 nTopBillSequenceId BOM_EXPLOSIONS.top_bill_sequence_id%type;
1695
1696 st_time number;
1697 end_time number;
1698 loop_end_time number;
1699 insert_end_time number;
1700 d_str varchar2(255);
1701
1702 MemoryBulkSize NATURAL;
1703
1704 CURSOR c_data IS
1705 SELECT DISTINCT component_item_id,
1706 NVL(common_component_sequence_id, component_sequence_id) AS component_sequence_id,
1707 component_code,
1708 description, language, source_lang
1709 FROM cz_exv_intl_text
1710 WHERE organization_id = nOrg_ID
1711 AND top_item_id = nTop_ID
1712 AND explosion_type = sExpl_type;
1713
1714 l_comp_code bom_explosions.component_code%TYPE;
1715 x_model_found BOOLEAN := FALSE;
1716 l_next_model_id NUMBER;
1717 l_parent_model_id NUMBER;
1718 l_next_item_id NUMBER;
1719
1720 v_settings_id VARCHAR2(40);
1721 v_section_name VARCHAR2(30);
1722
1723 BEGIN
1724
1725 v_settings_id := 'memorybulksize';
1726 v_section_name := 'import';
1727
1728 BEGIN
1729
1730 SELECT TO_NUMBER(value) INTO MemoryBulkSize
1731 FROM cz_db_settings
1732 WHERE LOWER(setting_id) = v_settings_id
1733 AND LOWER(section_name) = v_section_name;
1734
1735 EXCEPTION
1736 WHEN OTHERS THEN
1737 MemoryBulkSize := 10000000;
1738 END;
1739
1740 OPEN c_data;
1741 LOOP
1742 iOrigSysRef.DELETE;
1743 iLocalizedStr.DELETE;
1744 iLanguage.DELETE;
1745 iSrcLang.DELETE;
1746
1747 CompSeqId.DELETE;
1748 CompItemId.DELETE;
1749 CompCode.DELETE;
1750
1751 FETCH c_data
1752 BULK COLLECT
1753 INTO CompItemId, CompSeqId, CompCode, iLocalizedStr, iLanguage, iSrcLang
1754 LIMIT MemoryBulkSize;
1755 EXIT WHEN c_data%NOTFOUND AND CompItemId.COUNT = 0;
1756
1757 IF (CompItemId.COUNT > 0) THEN
1758
1759 FOR i IN 1..CompItemId.COUNT LOOP
1760
1761 l_comp_code := CompCode(i);
1762 x_model_found := FALSE;
1763
1764 -- need to find the containing parent model, use the component_code
1765 IF (instr(l_comp_code,'-') > 0) THEN
1766
1767 WHILE (instr(l_comp_code,'-') > 0 ) AND (x_model_found = FALSE) LOOP
1768 -- get the next item in the component_code
1769 l_next_item_id := substr(l_comp_code, instr(l_comp_code,'-',-1) + 1 );
1770
1771 -- if no imported models in models cache then return
1772 IF (gModelItemId_tbl.COUNT = 0) THEN
1773 RETURN;
1774 END IF;
1775
1776 -- find the item in the list of models in the global array
1777 FOR j IN gModelItemId_tbl.FIRST..gModelItemId_tbl.LAST LOOP
1778
1779 --
1780 -- skip the model item itself, we need its parent
1781 -- because this text belongs to the reference node
1782 -- so the parent model must be found
1783 --
1784 IF (to_number(l_next_item_id) = gModelItemId_tbl(j)
1785 AND gModelItemId_tbl(j) <> CompItemId(i) ) THEN
1786
1787 -- found the containing model: the parent model
1788 l_parent_model_id := gModelItemId_tbl(j);
1789 x_model_found := TRUE;
1790 EXIT;
1791
1792 END IF ;
1793
1794 END LOOP;
1795
1796 -- get the rest of the component_code and repreat
1797 l_comp_code := substr(l_comp_code, 1, instr(l_comp_code,'-',-1)-1);
1798
1799 END LOOP;
1800
1801 IF (x_model_found = FALSE) THEN
1802 l_parent_model_id := nTop_ID; -- the parent is the left-most item, the top item
1803 END IF;
1804 ElSE
1805 l_parent_model_id := nTop_ID; -- the item belongs to the top model
1806 END IF;
1807
1808 iFSKDevlProject := CZ_ORAAPPS_INTEGRATE.PROJECT_SURROGATE_KEY(sExpl_type,to_char(nOrg_ID),to_char(l_parent_model_id));
1809
1810 INSERT INTO CZ_IMP_LOCALIZED_TEXTS
1811 (LOCALIZED_STR, LANGUAGE, SOURCE_LANG, RUN_ID, ORIG_SYS_REF, FSK_DEVLPROJECT_1_1)
1812 VALUES (iLocalizedStr(i), iLanguage(i), iSrcLang(i), inRun_ID,
1813 CompItemId(i)||':'||sExpl_type||':'||nOrg_ID||':'||CompSeqId(i), iFSKDevlProject);
1814 END LOOP;
1815 COMMIT;
1816 END IF;
1817
1818 END LOOP;
1819 CLOSE c_data;
1820
1821 --
1822 -- Insert a text for each child model root node in this top model
1823 IF (gModelItemId_tbl.COUNT > 0) THEN
1824 FOR j IN gModelItemId_tbl.FIRST..gModelItemId_tbl.LAST LOOP
1825
1826 IF ( gModelItemId_tbl(j) <> nTop_ID) THEN
1827
1828 INSERT INTO CZ_IMP_LOCALIZED_TEXTS
1829 (LOCALIZED_STR, LANGUAGE, SOURCE_LANG, RUN_ID, ORIG_SYS_REF, FSK_DEVLPROJECT_1_1)
1830 SELECT description, language, source_lang, inRun_ID,
1831 gModelItemId_tbl(j)||':'||sExpl_type||':'||nOrg_ID||':'||
1832 NVL(common_component_sequence_id, component_sequence_id),
1833 CZ_ORAAPPS_INTEGRATE.PROJECT_SURROGATE_KEY(sExpl_type,to_char(nOrg_ID),to_char(gModelItemId_tbl(j)))
1834 FROM cz_exv_intl_text
1835 WHERE organization_id = nOrg_ID
1836 AND top_item_id = gModelItemId_tbl(j)
1837 AND explosion_type = sExpl_type
1838 AND component_item_id = top_item_id;
1839 END IF;
1840
1841 END LOOP;
1842 END IF;
1843
1844 if (CZ_IMP_ALL.get_time) then
1845 end_time := dbms_utility.get_time();
1846 d_str := inRun_Id || ' Extract text (' || nTop_Id || ' - Count - ' || iLocalizedStr.COUNT || ') :' || (end_time-st_time)/100.00;
1847 xError:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
1848 end if;
1849
1850 COMMIT;
1851 EXCEPTION
1852 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
1853 RAISE;
1854 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
1855 RAISE;
1856 WHEN OTHERS THEN
1857 IF(c_data%ISOPEN)THEN CLOSE c_data; END IF;
1858 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
1859 xERROR:=cz_utils.log_report(d_str,1,'CZ_IMP_SINGLE.EXTR_INTL_TEXT',11276,inRun_Id);
1860 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
1861 END;
1862 ------------------------------------------------------------------------------------------
1863 PROCEDURE EXTR_DEVL_PROJECT(inRun_ID IN PLS_INTEGER,
1864 nOrg_ID IN NUMBER,
1865 nTop_ID IN NUMBER,
1866 sExpl_type IN VARCHAR2,
1867 nModelId IN NUMBER)
1868 IS
1869 xERROR BOOLEAN:=FALSE;
1870
1871 TYPE tDescription IS TABLE OF cz_exv_item_master.description%TYPE INDEX BY BINARY_INTEGER;
1872 TYPE tComponentItemId IS TABLE OF cz_exv_item_master.component_item_id%TYPE INDEX BY BINARY_INTEGER;
1873 TYPE tOrganizationId IS TABLE OF cz_exv_item_master.organization_id%TYPE INDEX BY BINARY_INTEGER;
1874 TYPE tExplosionType IS TABLE OF cz_exv_item_master.explosion_type%TYPE INDEX BY BINARY_INTEGER;
1875 TYPE tPlanLevel IS TABLE OF cz_exv_item_master.plan_level%TYPE INDEX BY BINARY_INTEGER;
1876 TYPE tModelType IS TABLE OF cz_exv_item_master.model_type%TYPE INDEX BY BINARY_INTEGER;
1877
1878 TYPE tOrigSysRef IS TABLE OF cz_imp_devl_project.orig_sys_ref%TYPE INDEX BY BINARY_INTEGER;
1879 TYPE tName IS TABLE OF cz_imp_devl_project.name%TYPE INDEX BY BINARY_INTEGER;
1880 TYPE tDescText IS TABLE OF cz_imp_devl_project.desc_text%TYPE INDEX BY BINARY_INTEGER;
1881 TYPE tTopItemId IS TABLE OF cz_imp_devl_project.top_item_id%TYPE INDEX BY BINARY_INTEGER;
1882 TYPE tProductKey IS TABLE OF cz_imp_devl_project.product_key%TYPE INDEX BY BINARY_INTEGER;
1883
1884 Description tDescription;
1885 OrganizationId tOrganizationId;
1886 ComponentItemId tComponentItemId;
1887 ExplosionType tExplosionType;
1888 PlanLevel tPlanLevel;
1889 ModelType tModelType;
1890 ConfigModelType tModelType;
1891
1892 iOrigSysRef tOrigSysRef;
1893 iPlanLevel tPlanLevel;
1894 iName tName;
1895 iDescText tDescText;
1896 iTopItemId tTopItemId;
1897 iModelType tModelType;
1898 iConfigModelType tModelType;
1899 iOrganizationId tOrganizationId;
1900 iInventoryItemId tComponentItemId;
1901 iProductKey tProductKey;
1902
1903 nIndex PLS_INTEGER := 1;
1904
1905 st_time number;
1906 end_time number;
1907 loop_end_time number;
1908 insert_end_time number;
1909 d_str varchar2(255);
1910 l_lang VARCHAR2(4);
1911
1912 BEGIN
1913 gModelItemId_tbl.delete;
1914 if (CZ_IMP_ALL.get_time) then
1915 st_time := dbms_utility.get_time();
1916 end if;
1917
1918 l_lang := userenv('LANG');
1919
1920 SELECT
1921 ORGANIZATION_ID, COMPONENT_ITEM_ID, EXPLOSION_TYPE,
1922 DESCRIPTION, PLAN_LEVEL, MODEL_TYPE, CONFIG_MODEL_TYPE
1923 BULK COLLECT INTO
1924 OrganizationId, ComponentItemId, ExplosionType, Description, PlanLevel, ModelType, ConfigModelType
1925 FROM CZ_EXV_ITEM_MASTER WHERE bom_item_type = cz_imp_ps_node.cnModel
1926 AND ORGANIZATION_ID=nOrg_ID
1927 AND TOP_ITEM_ID=nTop_ID AND EXPLOSION_TYPE=sExpl_type
1928 AND language = l_lang;
1929
1930 if (CZ_IMP_ALL.get_time) then
1931 end_time := dbms_utility.get_time();
1932 d_str := inRun_Id || ' Extract projects (' || nTop_Id || ' - Count - ' || OrganizationId.COUNT || ') :' || (end_time-st_time)/100.00;
1933 xError:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
1934 end if;
1935
1936 FOR i IN 1..OrganizationId.COUNT LOOP
1937
1938 iOrigSysRef(nIndex) := CZ_ORAAPPS_INTEGRATE.PROJECT_SURROGATE_KEY(ExplosionType(i),to_char(OrganizationId(i)),to_char(ComponentItemId(i)));
1939 iName(nIndex) := Description(i) || '(' || to_char(OrganizationId(i)) || ' ' || to_char(ComponentItemId(i)) || ')';
1940 iDescText(nIndex) := SUBSTR(Description(i), 1, 255);
1941 iPlanLevel(nIndex) := PlanLevel(i);
1942 iTopItemId(nIndex) := ComponentItemId(i);
1943 gModelItemId_tbl(nIndex) := ComponentItemId(i); -- store for access by extr_intl_text procedure
1944 iOrganizationId(nIndex) := OrganizationId(i);
1945 iInventoryItemId(nIndex) := ComponentItemId(i);
1946 iProductKey(nIndex) := OrganizationId(i)||':'||ComponentItemId(i);
1947
1948 IF(ConfigModelType(i) = 'N')THEN
1949
1950 iModelType(nIndex) := 'N';
1951 ELSE
1952
1953 iModelType(nIndex) := ModelType(i);
1954 END IF;
1955
1956 nIndex := nIndex + 1;
1957 END LOOP;
1958
1959 if (CZ_IMP_ALL.get_time) then
1960 loop_end_time := dbms_utility.get_time();
1961 d_str := inRun_Id || ' Loop Projects (' || nTop_Id || ') :' || (loop_end_time-end_time)/100.00;
1962 xError:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
1963 end if;
1964
1965 FORALL i IN 1..iOrigSysRef.COUNT
1966 INSERT INTO CZ_IMP_DEVL_PROJECT
1967 (ORGANIZATION_ID, TOP_ITEM_ID, EXPLOSION_TYPE, ORIG_SYS_REF, VERSION,
1968 RUN_ID, NAME, FSK_INTLTEXT_1_1, DESC_TEXT, PLAN_LEVEL, MODEL_ID, MODEL_TYPE,
1969 INVENTORY_ITEM_ID, PRODUCT_KEY, BOM_CAPTION_RULE_ID, NONBOM_CAPTION_RULE_ID, CONFIG_ENGINE_TYPE)
1970 VALUES
1971 (nOrg_ID, iTopItemId(i), sExpl_type, iOrigSysRef(i), 1,
1972 inRun_ID, iName(i), iDescText(i), iDescText(i), iPlanLevel(i), nModelId, iModelType(i),
1973 iInventoryItemId(i), iProductKey(i), G_CAPTION_RULE_DESC, G_CAPTION_RULE_NAME, g_CONFIG_ENGINE_TYPE);
1974
1975 if (CZ_IMP_ALL.get_time) then
1976 insert_end_time := dbms_utility.get_time();
1977 d_str := inRun_Id || ' Insert projects (' || nTop_Id || ') :' || (insert_end_time-loop_end_time)/100.00;
1978 xError:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
1979 end if;
1980
1981 COMMIT;
1982
1983 EXCEPTION
1984 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
1985 RAISE;
1986 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
1987 RAISE;
1988 WHEN OTHERS THEN
1989 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
1990 xERROR:=cz_utils.log_report(d_str,1,'EXTR_DEVL_PROJECT',11276,inRun_Id);
1991 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
1992 END;
1993 ------------------------------------------------------------------------------------------
1994 PROCEDURE EXTR_ITEM_TYPES(inRun_ID IN PLS_INTEGER,
1995 nOrg_ID IN NUMBER,
1996 nTop_ID IN NUMBER,
1997 sExpl_type IN VARCHAR2)
1998 IS
1999 xERROR BOOLEAN:=FALSE;
2000
2001 st_time number;
2002 end_time number;
2003 loop_end_time number;
2004 insert_end_time number;
2005 d_str varchar2(255);
2006 l_use_segments BOOLEAN:=FALSE;
2007 l_name_method VARCHAR2(255);
2008
2009 BEGIN
2010 if (CZ_IMP_ALL.get_time) then
2011 st_time := dbms_utility.get_time();
2012 end if;
2013
2014 fnd_profile.get('CZ_SEGS_FOR_ITEMTYPE_NAME', l_name_method);
2015
2016 IF (l_name_method = 'CZ_CAT_DESC') THEN
2017 l_use_segments := FALSE;
2018 ELSIF (l_name_method = 'CZ_CAT_CONCAT_SEGS') THEN
2019 l_use_segments := TRUE;
2020 END IF;
2021
2022 IF (l_use_segments = TRUE) THEN
2023 FORALL i IN 1..itemCatalogGroupId.COUNT
2024 INSERT INTO CZ_IMP_ITEM_TYPE
2025 (DESC_TEXT, NAME, ORIG_SYS_REF,SRC_APPLICATION_ID,RUN_ID)
2026 SELECT DESCRIPTION, CATALOG_CONCAT_SEGS, ITEM_CATALOG_GROUP_ID, G_BOM_APPLICATION_ID, inRUN_ID
2027 FROM CZ_EXV_ITEM_TYPES
2028 WHERE ITEM_CATALOG_GROUP_ID = itemCatalogGroupId(i);
2029 ELSE
2030 FORALL i IN 1..itemCatalogGroupId.COUNT
2031 INSERT INTO CZ_IMP_ITEM_TYPE
2032 (DESC_TEXT, NAME, ORIG_SYS_REF, SRC_APPLICATION_ID, RUN_ID)
2033 SELECT DESCRIPTION, DESCRIPTION, ITEM_CATALOG_GROUP_ID, G_BOM_APPLICATION_ID, inRUN_ID
2034 FROM CZ_EXV_ITEM_TYPES
2035 WHERE ITEM_CATALOG_GROUP_ID = itemCatalogGroupId(i);
2036 END IF;
2037
2038 if (CZ_IMP_ALL.get_time) then
2039 end_time := dbms_utility.get_time();
2040 d_str := inRun_Id || ' Insert item type (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
2041 xError:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
2042 end if;
2043 COMMIT;
2044
2045 EXCEPTION
2046 WHEN NO_DATA_FOUND THEN
2047 NULL;
2048 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
2049 RAISE;
2050 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
2051 RAISE;
2052 WHEN OTHERS THEN
2053 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
2054 xERROR:=cz_utils.log_report(d_str,1,'EXTR_ITEM_TYPES',11276,inRun_Id);
2055 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
2056 END;
2057 ------------------------------------------------------------------------------------------
2058
2059 FUNCTION get_Col_Num(p_col_name IN VARCHAR2,p_col_tbl IN OUT NOCOPY rec_cols_tbl) RETURN NUMBER IS
2060 BEGIN
2061 IF p_col_tbl.EXISTS(p_col_name) THEN
2062 RETURN p_col_tbl(p_col_name).col_num;
2063 END IF;
2064 RETURN -1;
2065 END get_Col_Num;
2066
2067 FUNCTION get_ItemCatalogTable RETURN SYSTEM.CZ_ITEM_CATALOG_TBL IS
2068 BEGIN
2069 RETURN g_ItemCatalogTable;
2070 END get_ItemCatalogTable;
2071
2072 PROCEDURE EXTR_APC_PROPERTIES(p_run_id IN NUMBER,
2073 p_org_id IN NUMBER,
2074 p_top_item_id IN NUMBER,
2075 p_expl_type IN VARCHAR2) IS
2076
2077 xERROR BOOLEAN:=FALSE;
2078 st_time NUMBER;
2079 end_time NUMBER;
2080 loop_end_time NUMBER;
2081 insert_end_time NUMBER;
2082 d_str VARCHAR2(255);
2083
2084 l_prev_item_catalog_tbl SYSTEM.CZ_ITEM_CATALOG_TBL := SYSTEM.CZ_ITEM_CATALOG_TBL(SYSTEM.CZ_ITEM_CATALOG_REC(NULL));
2085
2086 l_cursor NUMBER;
2087 l_exec NUMBER;
2088 l_error BOOLEAN;
2089
2090 l_col_tbl rec_cols_tbl;
2091 l_rec_tab DBMS_SQL.desc_tab;
2092 l_att_names_tbl varchar_tbl_type;
2093 l_db_columns_tbl varchar_tbl_type;
2094 l_num_value_tbl number_tbl_type;
2095
2096 l_str_value VARCHAR2(1000);
2097 l_num_value NUMBER;
2098
2099 l_column_num NUMBER;
2100 l_rec_counter NUMBER;
2101 l_inventory_item_id NUMBER;
2102 l_org_id NUMBER;
2103 l_col_num NUMBER;
2104 l_current_attr_group_id NUMBER;
2105 l_col_cnt NUMBER;
2106 l_col_length NUMBER;
2107 l_attr_group_id NUMBER;
2108 l_current_inv_item_id NUMBER;
2109 l_attr_group_col_num NUMBER;
2110 l_inv_item_col_num NUMBER;
2111 l_org_id_col_num NUMBER;
2112 l_lang_col_num NUMBER;
2113 l_source_lang_col_num NUMBER;
2114 l_language VARCHAR2(255);
2115 l_col_name VARCHAR2(255);
2116 l_source_lang VARCHAR2(255);
2117 l_column_value VARCHAR2(1000);
2118 l_item_catalog_path VARCHAR2(4000);
2119
2120 l_attr_group_id_tbl number_tbl_type;
2121 l_temp_map number_tbl_type;
2122 l_attr_group_name_tbl varchar_tbl_type;
2123 l_attr_group_type_tbl varchar_tbl_type;
2124 l_attr_id_tbl number_tbl_type;
2125 l_attr_name_tbl varchar_tbl_type;
2126 l_property_type_tbl number_tbl_type;
2127 l_database_column_tbl varchar_tbl_type;
2128 l_db_column_types_tbl number_tbl_type;
2129 l_description_tbl varchar_tbl_type;
2130 l_data_type_code_tbl varchar_tbl_type;
2131 l_item_catalog_group_id_tbl number_tbl_type;
2132 l_def_value_tbl long_varchar_tbl_type;
2133 l_item_catalog_group_map_tbl number_tbl_type;
2134 l_sql VARCHAR2(32000);
2135 l_api_name VARCHAR2(255) := 'EXTR_APC_PROPERTIES';
2136 l_ndebug NUMBER;
2137 l_check_for_stub_views NUMBER;
2138
2139 l_prop_values_tbl long_varchar_tbl_type;
2140 l_prop_orig_sys_ref_tbl long_varchar_tbl_type;
2141 l_localtext_orig_sys_ref_tbl long_varchar_tbl_type;
2142 l_item_master_orig_sys_ref_tbl varchar_tbl_type;
2143 l_group_attr_names_tbl varchar_arr_tbl_type;
2144 l_group_database_columns_tbl varchar_arr_tbl_type;
2145 l_group_db_column_types_tbl number_arr_tbl_type;
2146 l_item_cat_tbl number_tbl_type;
2147 l_item_cat_temp_tbl number_tbl_type;
2148 l_attr_list_item_cat_tbl number_tbl_type;
2149 l_used_db_columns_tbl varchar_iv_tbl_type;
2150 l_hier_item_cat_tbl number_arr_tbl_type;
2151 l_temp_hier_item_cat_tbl number_arr_tbl_type;
2152 l_attr_list_cat_tbl varchar_tbl_type;
2153
2154 l_nh_attr_list_cat_tbl varchar_arr_tbl_type;
2155 l_t_attr_list_cat_tbl varchar_tbl_type;
2156
2157 l_attr_item_cat_tbl varchar_arr_tbl_type;
2158 l_attr_by_cat_tbl varchar_arr_tbl_type;
2159
2160 l_attr_list_temp_tbl varchar_tbl_type;
2161 l_item_cat_path_tbl varchar_tbl_type;
2162 l_no_attr_item_cat_tbl number_tbl_type;
2163 l_no_attr_catalog NUMBER;
2164 l_no_attr_cat_index NUMBER;
2165 l_attr_counter NUMBER;
2166
2167 BEGIN
2168
2169 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2170 l_ndebug := 0;
2171 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
2172 'EXTR_APC_PROPERTIES is called ('||TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS')||') :',
2173 fnd_log.LEVEL_STATEMENT);
2174 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
2175 'parameters : RUN_ID='||TO_CHAR(p_run_id)||',p_org_id='||TO_CHAR(p_org_id)||
2176 ', p_top_item_id='||TO_CHAR(p_top_item_id)||',p_expl_type='||p_expl_type ,
2177 fnd_log.LEVEL_STATEMENT);
2178 END IF;
2179
2180 --
2181 -- if l_check_for_stub_views > 0 then stub views are used
2182 --
2183 SELECT COUNT(*) INTO l_check_for_stub_views FROM CZ_EXV_APC_PROPERTIES
2184 WHERE ATTR_GROUP_ID = -1 AND
2185 ITEM_CATALOG_GROUP_ID = -1 AND
2186 ATTR_ID = -1 AND
2187 APPLICATION_ID = -1;
2188
2189 DELETE FROM CZ_IMP_TMP_ITEMCAT WHERE run_id=p_run_id;
2190 COMMIT;
2191
2192 --
2193 -- if list of item catalog group is empty or stub views are used then just return
2194 --
2195 IF itemCatalogGroupId.COUNT=0 OR l_check_for_stub_views > 0 THEN
2196 RETURN;
2197 END IF;
2198
2199 --
2200 -- Definitions of global SQL types which are used in TABLE(CAST(...))
2201 --
2202 -- CREATE OR REPLACE TYPE SYSTEM.CZ_ITEM_CATALOG_REC IS OBJECT(item_catalog_group_id number);
2203 --
2204 -- CREATE OR REPLACE TYPE SYSTEM.CZ_ITEM_CATALOG_TBL IS TABLE OF SYSTEM.CZ_ITEM_CATALOG_REC;
2205 --
2206
2207 FOR item_index IN itemCatalogGroupId.FIRST..itemCatalogGroupId.LAST
2208 LOOP
2209 l_item_catalog_path := '';
2210
2211 FOR i IN(SELECT item_catalog_group_id,parent_catalog_group_id,description,catalog_concat_segs
2212 FROM CZ_EXV_ITEM_TYPES
2213 START WITH item_catalog_group_id = itemCatalogGroupId(item_index)
2214 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id)
2215 LOOP
2216 l_prev_item_catalog_tbl.extend;
2217 l_prev_item_catalog_tbl(l_prev_item_catalog_tbl.LAST) := SYSTEM.CZ_ITEM_CATALOG_REC(i.item_catalog_group_id);
2218
2219 IF i.item_catalog_group_id<>itemCatalogGroupId(item_index) THEN
2220 l_item_cat_tbl(l_item_cat_tbl.COUNT+1) := i.item_catalog_group_id;
2221 l_item_catalog_path := l_item_catalog_path || '-' || TO_CHAR(i.item_catalog_group_id);
2222 END IF;
2223
2224 END LOOP;
2225
2226 IF l_item_cat_tbl.COUNT>0 THEN
2227 l_hier_item_cat_tbl(itemCatalogGroupId(item_index)) := l_item_cat_tbl;
2228 l_item_cat_path_tbl(itemCatalogGroupId(item_index)) := l_item_catalog_path;
2229 l_item_cat_tbl.DELETE;
2230 END IF;
2231
2232 END LOOP;
2233
2234 INSERT INTO CZ_IMP_TMP_ITEMCAT(run_id, item_catalog_group_id)
2235 SELECT p_run_id,item_catalog_group_id
2236 FROM (SELECT DISTINCT item_catalog_group_id
2237 FROM TABLE(CAST(l_prev_item_catalog_tbl AS SYSTEM.CZ_ITEM_CATALOG_TBL)) WHERE item_catalog_group_id IS NOT NULL);
2238
2239
2240 IF SQL%ROWCOUNT=0 THEN
2241 RETURN;
2242 END IF;
2243
2244 --
2245 -- l_prev_item_catalog_tbl will not be in use anymore - remove all elements from it
2246 --
2247 l_prev_item_catalog_tbl.DELETE;
2248
2249 --
2250 -- retrieve meta data for current itemCatalogGroupId = itemCatalogGroupId(item_index)
2251 --
2252 l_rec_counter := 0;
2253
2254
2255 FOR attr IN(SELECT attr_group_id, attr_group_name||'.'||attr_name AS attribute_name,
2256 DECODE(data_type_code,'N',DECIMAL_TYPE,'C',TEXT_TYPE,'A',TL_TEXT_TYPE,TEXT_TYPE) AS property_type,
2257 database_column,description,apcprops.item_catalog_group_id,apcprops.default_value
2258 FROM CZ_EXV_APC_PROPERTIES apcprops,
2259 CZ_IMP_TMP_ITEMCAT itemtypes
2260 WHERE apcprops.item_catalog_group_id=itemtypes.item_catalog_group_id AND
2261 itemtypes.run_id=p_run_id
2262 ORDER BY attr_group_id)
2263 LOOP
2264 l_rec_counter := l_rec_counter + 1;
2265 l_attr_group_id_tbl(l_rec_counter) := attr.attr_group_id;
2266 l_attr_name_tbl(l_rec_counter) := attr.attribute_name;
2267 l_property_type_tbl(l_rec_counter) := attr.property_type;
2268 l_database_column_tbl(l_rec_counter) := attr.database_column;
2269 l_description_tbl(l_rec_counter) := attr.description;
2270 l_item_catalog_group_id_tbl(l_rec_counter) := attr.item_catalog_group_id;
2271 l_def_value_tbl(l_rec_counter) := attr.default_value;
2272
2273 IF l_attr_by_cat_tbl.EXISTS(attr.item_catalog_group_id) THEN
2274 l_attr_counter := l_attr_by_cat_tbl(attr.item_catalog_group_id).COUNT + 1;
2275 l_attr_by_cat_tbl(attr.item_catalog_group_id)(l_attr_counter) := attr.attribute_name;
2276 ELSE
2277 l_attr_by_cat_tbl(attr.item_catalog_group_id)(1) := attr.attribute_name;
2278 END IF;
2279
2280 l_used_db_columns_tbl(attr.database_column) := attr.database_column;
2281
2282 END LOOP;
2283
2284 IF l_attr_name_tbl.COUNT = 0 THEN
2285 DELETE FROM CZ_IMP_TMP_ITEMCAT where run_id=p_run_id;
2286 RETURN;
2287 END IF;
2288
2289 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2290 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
2291 'APC meta data have been retrieved ('||TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS')||') ',
2292 fnd_log.LEVEL_STATEMENT);
2293 END IF;
2294
2295 FOR ii IN (SELECT item_catalog_group_id FROM CZ_IMP_TMP_ITEMCAT WHERE run_id=p_run_id)
2296 LOOP
2297 IF l_hier_item_cat_tbl.EXISTS(ii.item_catalog_group_id) THEN
2298
2299 l_item_cat_tbl := l_hier_item_cat_tbl(ii.item_catalog_group_id);
2300
2301 IF l_item_cat_tbl.COUNT > 0 THEN
2302
2303 FOR jj IN l_item_cat_tbl.First..l_item_cat_tbl.Last
2304 LOOP
2305 IF l_attr_by_cat_tbl.EXISTS(l_item_cat_tbl(jj)) AND l_attr_by_cat_tbl(l_item_cat_tbl(jj)).COUNT > 0 THEN
2306 FOR kk IN 1..l_attr_by_cat_tbl(l_item_cat_tbl(jj)).COUNT
2307 LOOP
2308 IF l_attr_item_cat_tbl.EXISTS(ii.item_catalog_group_id) THEN
2309 l_attr_counter := l_attr_item_cat_tbl(ii.item_catalog_group_id).COUNT + 1;
2310 l_attr_item_cat_tbl(ii.item_catalog_group_id)(l_attr_counter) := l_attr_by_cat_tbl(l_item_cat_tbl(jj))(kk);
2311 ELSE
2312 l_attr_item_cat_tbl(ii.item_catalog_group_id)(1) := l_attr_by_cat_tbl(l_item_cat_tbl(jj))(kk);
2313 END IF;
2314 END LOOP;
2315 END IF;
2316
2317 END LOOP;
2318 END IF;
2319 END IF;
2320 END LOOP;
2321
2322
2323 --
2324 -- populate Import Property table with APC properties meta data for Items
2325 -- format of ORIG_SYS_REF : <CZ_ATTR_NAME> || ':' || <EGO_APPLICATION_ID>
2326 -- where CZ_ATTR_NAME = <APC attr_group_name>||'.'||<APC attr_name>
2327 --
2328 FOR i IN l_attr_name_tbl.FIRST..l_attr_name_tbl.LAST
2329 LOOP
2330 INSERT INTO CZ_IMP_PROPERTY
2331 (DESC_TEXT,NAME,ORIG_SYS_REF,RUN_ID,DATA_TYPE,DEF_VALUE,DEF_NUM_VALUE,SRC_APPLICATION_ID)
2332 VALUES (l_description_tbl(i),
2333 l_attr_name_tbl(i),
2334 l_attr_name_tbl(i)||'-'||TO_CHAR(G_EGO_APPLICATION_ID),
2335 p_run_id,
2336 l_property_type_tbl(i),
2337 l_def_value_tbl(i),
2338 DECODE(l_property_type_tbl(i),DECIMAL_TYPE,TO_NUMBER(l_def_value_tbl(i)),NULL),
2339 G_EGO_APPLICATION_ID);
2340 END LOOP;
2341
2342 COMMIT;
2343
2344 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2345 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
2346 'CZ_IMP_PROPERTY has been populated ('||TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS')||') ',
2347 fnd_log.LEVEL_STATEMENT);
2348 END IF;
2349
2350 --
2351 -- populate Import Property table with APC properties meta data for Item Types
2352 -- format of ORIG_SYS_REF : <ITEM CATALOG_GROUP_ID> || ':' || <CZ_ATTR_NAME> || ':' || <EGO_APPLICATION_ID>
2353 -- where CZ_ATTR_NAME = <APC attr_group_name>||'.'||<APC attr_name>
2354 --
2355 FORALL i IN l_attr_name_tbl.FIRST..l_attr_name_tbl.LAST
2356 INSERT INTO CZ_IMP_ITEM_TYPE_PROPERTY
2357 (FSK_ITEMTYPE_1_1,FSK_ITEMTYPE_1_EXT,FSK_PROPERTY_2_1,FSK_PROPERTY_2_EXT,RUN_ID,
2358 ORIG_SYS_REF,SRC_APPLICATION_ID)
2359 VALUES (l_item_catalog_group_id_tbl(i),
2360 l_item_catalog_group_id_tbl(i),
2361 l_attr_name_tbl(i)||'-'||TO_CHAR(G_EGO_APPLICATION_ID),
2362 l_attr_name_tbl(i)||'-'||TO_CHAR(G_EGO_APPLICATION_ID),
2363 p_run_id,
2364 TO_CHAR(l_item_catalog_group_id_tbl(i)) || ':' ||l_attr_name_tbl(i)||'-'||TO_CHAR(G_EGO_APPLICATION_ID),
2365 G_EGO_APPLICATION_ID);
2366
2367 COMMIT;
2368
2369 FOR ii IN (SELECT item_catalog_group_id FROM CZ_IMP_TMP_ITEMCAT WHERE run_id=p_run_id)
2370 LOOP
2371 IF l_attr_item_cat_tbl.EXISTS(ii.item_catalog_group_id) THEN
2372 l_attr_list_temp_tbl := l_attr_item_cat_tbl(ii.item_catalog_group_id);
2373
2374 IF l_attr_list_temp_tbl.COUNT > 0 THEN
2375 FOR jj IN l_attr_list_temp_tbl.First..l_attr_list_temp_tbl.Last
2376 LOOP
2377 INSERT INTO CZ_IMP_ITEM_TYPE_PROPERTY
2378 (FSK_ITEMTYPE_1_1,FSK_ITEMTYPE_1_EXT,FSK_PROPERTY_2_1,FSK_PROPERTY_2_EXT,RUN_ID,
2379 ORIG_SYS_REF,SRC_APPLICATION_ID)
2380 VALUES (ii.item_catalog_group_id,
2381 ii.item_catalog_group_id,
2382 l_attr_list_temp_tbl(jj)||'-'||TO_CHAR(G_EGO_APPLICATION_ID),
2383 l_attr_list_temp_tbl(jj)||'-'||TO_CHAR(G_EGO_APPLICATION_ID),
2384 p_run_id,
2385 TO_CHAR(ii.item_catalog_group_id)|| ':'||
2386 l_attr_list_temp_tbl(jj)||'-'||TO_CHAR(G_EGO_APPLICATION_ID) || '-' || l_item_cat_path_tbl(ii.item_catalog_group_id) ,
2387 G_EGO_APPLICATION_ID);
2388
2389 END LOOP;
2390 END IF;
2391
2392 END IF;
2393 END LOOP;
2394
2395 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2396 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
2397 'CZ_IMP_ITEM_TYPE_PROPERTY has been populated ('||TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS')||') ',
2398 fnd_log.LEVEL_STATEMENT);
2399 END IF;
2400
2401 --
2402 -- collect APC meta data for properties :
2403 --
2404 -- l_att_names_tbl - array of attribute names ( one array per attribute group )
2405 -- l_db_columns_tbl - array of column names ( one array per attribute group )
2406 -- l_db_column_types_tbl - array of column types ( one array per attribute group )
2407 --
2408 -- l_att_names_tbl, l_db_columns_tbl and l_db_column_types_tbl are temp arrays which are
2409 -- used for populating 2-dim arrays l_group_attr_names_tbl, l_group_database_columns_tbl, l_group_db_column_types_tbl
2410 --
2411 -- l_group_attr_names_tbl - array of array l_att_names_tbl ( stores arrays l_att_names_tbl for attribute groups )
2412 -- index of array is attribute group id
2413 -- l_group_database_columns_tbl - array of array l_db_columns_tbl ( stores arrays l_db_columns_tbl for attribute groups )
2414 -- index of array is attribute group id
2415 -- l_group_db_column_types_tbl - array of array l_group_db_column_types_tbl ( stores arrays l_group_db_column_types_tbl for attribute groups )
2416 -- index of array is attribute group id
2417 --
2418
2419 l_current_attr_group_id := 0;
2420
2421 FOR i IN l_attr_name_tbl.FIRST..l_attr_name_tbl.LAST
2422 LOOP
2423
2424 IF l_current_attr_group_id=l_attr_group_id_tbl(i) THEN
2425 l_att_names_tbl(l_att_names_tbl.COUNT+1) := l_attr_name_tbl(i);
2426 l_db_columns_tbl(l_db_columns_tbl.COUNT+1) := l_database_column_tbl(i);
2427 l_db_column_types_tbl(l_db_column_types_tbl.COUNT+1) := l_property_type_tbl(i);
2428 ELSE
2429 IF l_current_attr_group_id=0 THEN
2430 l_current_attr_group_id := l_attr_group_id_tbl(i);
2431 l_att_names_tbl(l_att_names_tbl.COUNT+1) := l_attr_name_tbl(i);
2432 l_db_columns_tbl(l_db_columns_tbl.COUNT+1) := l_database_column_tbl(i);
2433 l_db_column_types_tbl(l_db_column_types_tbl.COUNT+1) := l_property_type_tbl(i);
2434 ELSE
2435 l_group_attr_names_tbl(l_current_attr_group_id) := l_att_names_tbl;
2436 l_group_database_columns_tbl(l_current_attr_group_id) := l_db_columns_tbl;
2437 l_group_db_column_types_tbl(l_current_attr_group_id) := l_db_column_types_tbl;
2438
2439 l_att_names_tbl.DELETE;
2440 l_db_columns_tbl.DELETE;
2441 l_db_column_types_tbl.DELETE;
2442
2443 l_current_attr_group_id := l_attr_group_id_tbl(i);
2444
2445 l_att_names_tbl(l_att_names_tbl.COUNT+1) := l_attr_name_tbl(i);
2446 l_db_columns_tbl(l_db_columns_tbl.COUNT+1) := l_database_column_tbl(i);
2447 l_db_column_types_tbl(l_db_column_types_tbl.COUNT+1) := l_property_type_tbl(i);
2448
2449 END IF;
2450 END IF;
2451 END LOOP;
2452
2453 -- populate 2-dim arrays
2454 l_group_attr_names_tbl(l_current_attr_group_id) := l_att_names_tbl;
2455 l_group_database_columns_tbl(l_current_attr_group_id) := l_db_columns_tbl;
2456 l_group_db_column_types_tbl(l_current_attr_group_id) := l_db_column_types_tbl;
2457
2458 -- release temp arrays
2459 l_att_names_tbl.DELETE;
2460 l_db_columns_tbl.DELETE;
2461 l_db_column_types_tbl.DELETE;
2462
2463 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2464 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
2465 'Internal arrays have been populated ('||TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS')||') ',
2466 fnd_log.LEVEL_STATEMENT);
2467 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
2468 'Retrieving of APC property values will be started ('||TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS')||') ',
2469 fnd_log.LEVEL_STATEMENT);
2470 END IF;
2471
2472 ---Bug Fix - 8519380
2473 l_sql := 'SELECT apcpropvals.* FROM CZ_EXV_ITEM_APC_PROP_VALUES apcpropvals,'||
2474 'CZ_IMP_TMP_ITEMCAT itemtypes '||
2475 'WHERE apcpropvals.item_catalog_group_id = itemtypes.item_catalog_group_id '||
2476 'AND EXISTS (SELECT NULL FROM CZ_EXV_ITEMS '||
2477 'WHERE inventory_item_id = apcpropvals.inventory_item_id '||
2478 ' AND organization_id = '|| p_org_id ||
2479 ' AND top_item_id = '||p_top_item_id||
2480 ' AND explosion_type = '''||p_expl_type||''')'||
2481 ' AND organization_id = '|| p_org_id ||
2482 ' AND inventory_item_id IS NOT NULL ORDER BY INVENTORY_ITEM_ID';
2483 --
2484 -- define cursor and dynamically describe columns of this cursor
2485 --
2486 l_cursor := DBMS_SQL.OPEN_CURSOR;
2487 DBMS_SQL.PARSE(l_cursor, l_sql, DBMS_SQL.NATIVE);
2488 l_exec := DBMS_SQL.EXECUTE(l_cursor);
2489 DBMS_SQL.DESCRIBE_COLUMNS(l_cursor, l_col_cnt, l_rec_tab);
2490
2491 --
2492 -- dynamically define columns of record set
2493 --
2494 l_col_num := l_rec_tab.FIRST;
2495 IF (l_col_num IS NOT NULL) THEN
2496 LOOP
2497 l_col_name:=UPPER(l_rec_tab(l_col_num).col_name);
2498 l_col_length:=l_rec_tab(l_col_num).col_max_len;
2499
2500 l_col_tbl(l_col_name).col_name := l_col_name;
2501 l_col_tbl(l_col_name).col_num := l_col_num;
2502
2503 IF l_col_name IN('ATTR_GROUP_ID') THEN
2504 DBMS_SQL.DEFINE_COLUMN(l_cursor,l_col_num,l_num_value);
2505 l_attr_group_col_num := l_col_num;
2506 ELSIF l_col_name IN('INVENTORY_ITEM_ID') THEN
2507 DBMS_SQL.DEFINE_COLUMN(l_cursor,l_col_num,l_num_value);
2508 l_inv_item_col_num := l_col_num;
2509 ELSIF l_col_name IN('ORGANIZATION_ID') THEN
2510 DBMS_SQL.DEFINE_COLUMN(l_cursor,l_col_num,l_num_value);
2511 l_org_id_col_num := l_col_num;
2512 ELSIF l_col_name IN('LANGUAGE') THEN
2513 DBMS_SQL.DEFINE_COLUMN(l_cursor,l_col_num,l_str_value,l_col_length);
2514 l_lang_col_num := l_col_num;
2515 ELSIF l_col_name IN('SOURCE_LANG') THEN
2516 DBMS_SQL.DEFINE_COLUMN(l_cursor,l_col_num,l_str_value,l_col_length);
2517 l_source_lang_col_num := l_col_num;
2518 END IF;
2519
2520 IF l_used_db_columns_tbl.EXISTS(l_col_name) THEN
2521 DBMS_SQL.DEFINE_COLUMN(l_cursor,l_col_num,l_str_value,l_col_length);
2522 END IF;
2523
2524 l_col_num := l_rec_tab.NEXT(l_col_num);
2525 EXIT WHEN (l_col_num IS NULL);
2526
2527 END LOOP; -- end of loop through l_rec_tab
2528 END IF; -- end of IF (l_col_num IS NOT NULL) THEN
2529
2530 l_current_inv_item_id := 0;
2531
2532 l_rec_counter := 0;
2533
2534 --
2535 -- fetch records from dynamic cursor with sql = l_sql
2536 --
2537 LOOP
2538
2539 IF DBMS_SQL.FETCH_ROWS(l_cursor)=0 THEN
2540 EXIT;
2541 ELSE
2542 DBMS_SQL.COLUMN_VALUE(l_cursor,l_attr_group_col_num,l_attr_group_id);
2543 DBMS_SQL.COLUMN_VALUE(l_cursor,l_inv_item_col_num,l_inventory_item_id);
2544 DBMS_SQL.COLUMN_VALUE(l_cursor,l_org_id_col_num,l_org_id);
2545 DBMS_SQL.COLUMN_VALUE(l_cursor,l_lang_col_num,l_language);
2546 DBMS_SQL.COLUMN_VALUE(l_cursor,l_source_lang_col_num,l_source_lang);
2547
2548 l_att_names_tbl := l_group_attr_names_tbl(l_attr_group_id);
2549 l_db_columns_tbl := l_group_database_columns_tbl(l_attr_group_id);
2550 l_db_column_types_tbl := l_group_db_column_types_tbl(l_attr_group_id);
2551
2552 --
2553 -- handle each EXT column in this loop
2554 --
2555 FOR n IN l_db_columns_tbl.FIRST..l_db_columns_tbl.LAST
2556 LOOP
2557 -- find column num of column with name l_db_columns_tbl(m)
2558 l_column_num := get_Col_Num(l_db_columns_tbl(n), l_col_tbl);
2559
2560 --
2561 -- find value of property which is stored in column l_db_columns_tbl(n)
2562 --
2563 DBMS_SQL.COLUMN_VALUE(l_cursor,l_column_num,l_column_value);
2564
2565 IF l_column_value IS NOT NULL THEN
2566
2567 l_rec_counter := l_rec_counter + 1;
2568
2569 l_prop_values_tbl(l_rec_counter) := l_column_value;
2570 l_prop_orig_sys_ref_tbl(l_rec_counter) := l_att_names_tbl(n)||'-'||TO_CHAR(G_EGO_APPLICATION_ID);
2571 l_item_master_orig_sys_ref_tbl(l_rec_counter) := TO_CHAR(l_inventory_item_id)||':'||TO_CHAR(l_org_id);
2572
2573 l_localtext_orig_sys_ref_tbl(l_rec_counter) := NULL;
2574
2575 --
2576 -- if it is translatable property then populate l_num_value_tbl () with intl_text_id and
2577 -- add record to CZ_IMP_LOCALIZED_TEXTS with
2578 -- ORIG_SYS_REF = l_prop_orig_sys_ref_tbl(l_rec_counter)
2579 --
2580 IF l_db_column_types_tbl(n)=TL_TEXT_TYPE THEN -- translatable text
2581 l_localtext_orig_sys_ref_tbl(l_rec_counter) := l_prop_orig_sys_ref_tbl(l_rec_counter)||':'||l_db_columns_tbl(n)||':'||TO_CHAR(l_inventory_item_id);
2582
2583 INSERT INTO CZ_IMP_LOCALIZED_TEXTS
2584 (LOCALIZED_STR, LANGUAGE, SOURCE_LANG, RUN_ID, ORIG_SYS_REF, MODEL_ID)
2585 VALUES
2586 (l_column_value,l_language, l_source_lang, p_run_id, l_localtext_orig_sys_ref_tbl(l_rec_counter),0);
2587 l_num_value_tbl (l_rec_counter) := NULL;
2588 ELSIF l_db_column_types_tbl(n)=DECIMAL_TYPE THEN -- DECIMAL
2589 l_num_value_tbl (l_rec_counter) := TO_NUMBER(l_column_value);
2590 l_prop_values_tbl(l_rec_counter) := l_column_value;
2591 ELSE
2592 l_num_value_tbl (l_rec_counter) := NULL;
2593 END IF;
2594
2595 END IF;
2596
2597 END LOOP; -- end of FOR n IN ...
2598
2599 END IF; -- end of IF DBMS_SQL.FETCH_ROWS(l_cursor)=0
2600
2601 IF l_prop_values_tbl.COUNT > 0 AND (l_rec_counter=l_Batch_Size OR FLOOR(l_rec_counter/l_Batch_Size)=0) THEN
2602
2603 --
2604 -- insert portion of data limited by l_Batch_Size to Import Property Values
2605 --
2606 FORALL i IN l_prop_values_tbl.FIRST..l_prop_values_tbl.LAST
2607 INSERT INTO CZ_IMP_ITEM_PROPERTY_VALUE
2608 (PROPERTY_VALUE, FSK_PROPERTY_1_1,FSK_PROPERTY_1_EXT,
2609 FSK_ITEMMASTER_2_1,FSK_ITEMMASTER_2_EXT,RUN_ID,ORIG_SYS_REF,FSK_LOCALIZEDTEXT_3_1,PROPERTY_NUM_VALUE,
2610 SRC_APPLICATION_ID)
2611 VALUES
2612 (l_prop_values_tbl(i), l_prop_orig_sys_ref_tbl(i),l_prop_orig_sys_ref_tbl(i),
2613 l_item_master_orig_sys_ref_tbl(i), l_item_master_orig_sys_ref_tbl(i),
2614 p_run_id,
2615 l_item_master_orig_sys_ref_tbl(i) || ':' || l_prop_orig_sys_ref_tbl(i),
2616 l_localtext_orig_sys_ref_tbl(i),
2617 l_num_value_tbl(i),
2618 G_EGO_APPLICATION_ID);
2619
2620 COMMIT;
2621
2622 FORALL i IN l_prop_values_tbl.FIRST..l_prop_values_tbl.LAST
2623 INSERT INTO CZ_IMP_LOCALIZED_TEXTS
2624 (LOCALIZED_STR, LANGUAGE, SOURCE_LANG, RUN_ID, ORIG_SYS_REF, MODEL_ID)
2625 SELECT
2626 LOCALIZED_STR, LANG.LANGUAGE_CODE, SOURCE_LANG, RUN_ID, ORIG_SYS_REF, MODEL_ID
2627 FROM CZ_IMP_LOCALIZED_TEXTS intl,fnd_languages lang
2628 WHERE intl.run_id=p_run_id AND intl.orig_sys_ref=l_localtext_orig_sys_ref_tbl(i) AND installed_flag in( 'B', 'I')
2629 AND LANGUAGE_CODE<>SOURCE_LANG
2630 AND EXISTS
2631 (SELECT 1 FROM CZ_IMP_LOCALIZED_TEXTS loc,fnd_languages lang
2632 WHERE loc.run_id=p_run_id AND orig_sys_ref = intl.orig_sys_ref
2633 AND lang.language_code <> loc.language
2634 );
2635 COMMIT;
2636
2637 l_rec_counter := 0;
2638 l_prop_values_tbl.DELETE;
2639 l_prop_orig_sys_ref_tbl.DELETE;
2640 l_item_master_orig_sys_ref_tbl.DELETE;
2641 l_num_value_tbl.DELETE;
2642 l_localtext_orig_sys_ref_tbl.DELETE;
2643 END IF;
2644
2645
2646 END LOOP; -- end of looping through l_cursor
2647
2648 -- close cursor
2649 DBMS_SQL.CLOSE_CURSOR(l_cursor);
2650
2651 DELETE FROM CZ_IMP_TMP_ITEMCAT WHERE run_id=p_run_id;
2652 COMMIT;
2653
2654 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2655 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
2656 'CZ_IMP_ITEM_PROPERTY_VALUE has been populated ('||TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS')||') ',
2657 fnd_log.LEVEL_STATEMENT);
2658 END IF;
2659
2660 EXCEPTION
2661 WHEN OTHERS THEN
2662 DELETE FROM CZ_IMP_TMP_ITEMCAT WHERE run_id=p_run_id;
2663 COMMIT;
2664 l_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'EXTR_APC_PROPERTIES',11276,p_run_id);
2665 RAISE;
2666 END EXTR_APC_PROPERTIES;
2667
2668 ------------------------------------------------------------------------------------------
2669 PROCEDURE EXTR_PROPERTIES(inRun_ID IN PLS_INTEGER,
2670 nOrg_ID IN NUMBER,
2671 nTop_ID IN NUMBER,
2672 sExpl_type IN VARCHAR2)
2673 IS
2674 DECIMAL_TYPE CONSTANT NUMBER := 2;
2675 TEXT_TYPE CONSTANT NUMBER := 4;
2676
2677 xERROR BOOLEAN:=FALSE;
2678 sName CZ_IMP_PROPERTY.ORIG_SYS_REF%TYPE;
2679 nType CZ_IMP_PROPERTY.DATA_TYPE%TYPE;
2680 sResolve CZ_DB_SETTINGS.VALUE%TYPE;
2681
2682 TYPE tNumber IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2683 tabValues tNumber;
2684
2685 CURSOR C_DATATYPE IS
2686 SELECT ORIG_SYS_REF FROM CZ_IMP_PROPERTY WHERE RUN_ID=inRUN_ID AND REC_STATUS IS NULL
2687 FOR UPDATE;
2688
2689 st_time number;
2690 end_time number;
2691 loop_end_time number;
2692 insert_end_time number;
2693 d_str varchar2(255);
2694 l_noupdate cz_xfr_fields.noupdate%TYPE;
2695 l_check_values BOOLEAN :=FALSE;
2696 l_onl_type cz_properties.data_type%TYPE;
2697
2698 v_settings_id VARCHAR2(40);
2699 v_section_name VARCHAR2(30);
2700
2701 BEGIN
2702
2703 if (CZ_IMP_ALL.get_time) then
2704 st_time := dbms_utility.get_time();
2705 end if;
2706
2707 FORALL i IN 1..itemCatalogGroupId.COUNT
2708 INSERT INTO CZ_IMP_PROPERTY
2709 (DESC_TEXT,NAME,ORIG_SYS_REF,RUN_ID,DATA_TYPE,SRC_APPLICATION_ID)
2710 SELECT DESCRIPTION, ELEMENT_NAME, ELEMENT_NAME, inRUN_ID, TEXT_TYPE, G_BOM_APPLICATION_ID
2711 FROM CZ_EXV_ITEM_PROPERTIES
2712 WHERE ITEM_CATALOG_GROUP_ID = itemCatalogGroupId(i);
2713
2714 if (CZ_IMP_ALL.get_time) then
2715 end_time := dbms_utility.get_time();
2716 d_str := inRun_Id || ' Insert property (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
2717 xError:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
2718 end if;
2719
2720 COMMIT;
2721
2722 v_settings_id := 'ResolvePropertyDataType';
2723 v_section_name := 'ORAAPPS_INTEGRATE';
2724
2725 BEGIN
2726 SELECT VALUE INTO sResolve FROM CZ_DB_SETTINGS
2727 WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
2728 EXCEPTION
2729 WHEN OTHERS THEN
2730 sResolve:='NO';
2731 END;
2732
2733 BEGIN
2734 SELECT noupdate INTO l_noupdate
2735 FROM cz_xfr_fields f, cz_xfr_tables t
2736 WHERE t.order_seq=f.order_seq
2737 AND t.dst_table='CZ_PROPERTIES'
2738 AND f.xfr_group='IMPORT'
2739 AND t.xfr_group=f.xfr_group
2740 AND f.dst_field='DATA_TYPE'
2741 AND rownum < 2;
2742 EXCEPTION
2743 WHEN NO_DATA_FOUND THEN
2744 l_noupdate:='0';
2745 END;
2746
2747 OPEN C_DATATYPE;
2748 LOOP
2749 FETCH C_DATATYPE INTO sName;
2750 EXIT WHEN C_DATATYPE%NOTFOUND;
2751
2752 BEGIN
2753 SELECT data_type INTO l_onl_type
2754 FROM cz_properties
2755 WHERE orig_sys_ref=sName
2756 AND deleted_flag='0'
2757 AND rownum <2;
2758 nType := l_onl_type;
2759 EXCEPTION
2760 WHEN NO_DATA_FOUND THEN
2761 l_onl_type:= -1;
2762 END;
2763
2764 IF (l_onl_type = -1 AND sResolve = 'YES') THEN
2765 l_check_values := TRUE;
2766 ELSIF (l_onl_type = -1 AND sResolve = 'NO') THEN
2767 l_check_values := FALSE;
2768 nType := TEXT_TYPE;
2769 ELSIF (l_onl_type = TEXT_TYPE AND l_noupdate = '1') THEN
2770 l_check_values := FALSE;
2771 nType := TEXT_TYPE;
2772 ELSIF (l_onl_type = TEXT_TYPE AND l_noupdate = '0' AND sResolve='YES') THEN
2773 l_check_values := TRUE;
2774 ELSIF (l_onl_type = DECIMAL_TYPE AND l_noupdate = '1') THEN
2775 l_check_values := FALSE;
2776 nType := DECIMAL_TYPE;
2777 ELSIF (l_onl_type = DECIMAL_TYPE AND l_noupdate = '0') THEN
2778 l_check_values := TRUE;
2779 ELSE
2780 l_check_values := FALSE;
2781 nType := TEXT_TYPE;
2782 END IF;
2783
2784 IF (l_check_values = TRUE) THEN
2785 BEGIN
2786 tabValues.DELETE;
2787
2788 SELECT TO_NUMBER(element_value) BULK COLLECT INTO tabValues
2789 FROM cz_exv_descr_element_values
2790 WHERE element_name = sName;
2791
2792 IF(tabValues.COUNT > 0)THEN
2793 nType := DECIMAL_TYPE;
2794 END IF;
2795 EXCEPTION
2796 WHEN INVALID_NUMBER THEN
2797 nType := TEXT_TYPE;
2798 END;
2799 END IF;
2800
2801 IF(nType=DECIMAL_TYPE)THEN
2802 UPDATE CZ_IMP_PROPERTY SET DATA_TYPE=DECIMAL_TYPE,DEF_NUM_VALUE=0
2803 WHERE CURRENT OF C_DATATYPE;
2804 END IF;
2805 END LOOP;
2806 CLOSE C_DATATYPE;
2807 if (CZ_IMP_ALL.get_time) then
2808 loop_end_time := dbms_utility.get_time();
2809 d_str := inRun_Id || ' Resolve property datatype (' || nTop_Id || ') :' || (loop_end_time-end_time)/100.00;
2810 xError:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
2811 end if;
2812 EXCEPTION
2813 WHEN NO_DATA_FOUND THEN
2814 NULL;
2815 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
2816 RAISE;
2817 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
2818 RAISE;
2819 WHEN OTHERS THEN
2820 IF(c_datatype%ISOPEN)THEN CLOSE c_datatype; END IF;
2821 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
2822 xERROR:=cz_utils.log_report(d_str,1,'EXTR_PROPERTIES',11276,inRun_Id);
2823 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
2824 END;
2825 ------------------------------------------------------------------------------------------
2826 PROCEDURE EXTR_ITEM_TYPE_PROPERTIES(inRun_ID IN PLS_INTEGER,
2827 nOrg_ID IN NUMBER,
2828 nTop_ID IN NUMBER,
2829 sExpl_type IN VARCHAR2)
2830 IS
2831 xERROR BOOLEAN:=FALSE;
2832
2833 st_time number;
2834 end_time number;
2835 loop_end_time number;
2836 insert_end_time number;
2837 d_str varchar2(255);
2838
2839 BEGIN
2840
2841 if (CZ_IMP_ALL.get_time) then
2842 st_time := dbms_utility.get_time();
2843 end if;
2844
2845 FORALL i IN 1..itemCatalogGroupId.COUNT
2846 INSERT INTO CZ_IMP_ITEM_TYPE_PROPERTY
2847 (FSK_ITEMTYPE_1_1,FSK_ITEMTYPE_1_EXT,FSK_PROPERTY_2_1,FSK_PROPERTY_2_EXT,RUN_ID,
2848 ORIG_SYS_REF,SRC_APPLICATION_ID)
2849 SELECT ITEM_CATALOG_GROUP_ID, ITEM_CATALOG_GROUP_ID, ELEMENT_NAME, ELEMENT_NAME, inRUN_ID,
2850 TO_CHAR(ITEM_CATALOG_GROUP_ID) || ':' || ELEMENT_NAME, G_BOM_APPLICATION_ID
2851 FROM CZ_EXV_ITEM_PROPERTIES
2852 WHERE ITEM_CATALOG_GROUP_ID = itemCatalogGroupId(i);
2853
2854 if (CZ_IMP_ALL.get_time) then
2855 end_time := dbms_utility.get_time();
2856 d_str := inRun_Id || ' Insert item Type prop (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
2857 xError:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
2858 end if;
2859
2860 COMMIT;
2861
2862 EXCEPTION
2863 WHEN NO_DATA_FOUND THEN
2864 NULL;
2865 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
2866 RAISE;
2867 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
2868 RAISE;
2869 WHEN OTHERS THEN
2870 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
2871 xERROR:=cz_utils.log_report(d_str,1,'EXTR_ITEM_TYPE_PROPERTIES',11276,inRun_Id);
2872 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
2873 END;
2874 ------------------------------------------------------------------------------------------
2875 PROCEDURE EXTR_ITEM_PROPERTY_VALUES(inRun_ID IN PLS_INTEGER,
2876 nOrg_ID IN NUMBER,
2877 nTop_ID IN NUMBER,
2878 sExpl_type IN VARCHAR2)
2879 IS
2880 xERROR BOOLEAN:=FALSE;
2881
2882 st_time number;
2883 end_time number;
2884 loop_end_time number;
2885 d_str varchar2(255);
2886
2887 BEGIN
2888
2889 if(CZ_IMP_ALL.get_time) then
2890 st_time := dbms_utility.get_time();
2891 end if;
2892
2893 FORALL i IN 1..inventoryItemId.COUNT
2894 INSERT INTO CZ_IMP_ITEM_PROPERTY_VALUE
2895 (PROPERTY_VALUE, FSK_PROPERTY_1_1, FSK_PROPERTY_1_EXT,
2896 FSK_ITEMMASTER_2_1,
2897 FSK_ITEMMASTER_2_EXT,
2898 RUN_ID,
2899 ORIG_SYS_REF, SRC_APPLICATION_ID)
2900 SELECT ELEMENT_VALUE, ELEMENT_NAME, ELEMENT_NAME,
2901 CZ_ORAAPPS_INTEGRATE.ITEM_SURROGATE_KEY(to_char(INVENTORY_ITEM_ID), to_char(ORGANIZATION_ID)),
2902 CZ_ORAAPPS_INTEGRATE.ITEM_SURROGATE_KEY(to_char(INVENTORY_ITEM_ID), to_char(ORGANIZATION_ID)),
2903 inRun_ID,
2904 CZ_ORAAPPS_INTEGRATE.ITEM_SURROGATE_KEY(to_char(INVENTORY_ITEM_ID), to_char(ORGANIZATION_ID))|| ':' || ELEMENT_NAME,
2905 G_BOM_APPLICATION_ID
2906 FROM CZ_EXV_ITEM_PROPERTY_VALUES
2907 WHERE INVENTORY_ITEM_ID = inventoryItemId(i)
2908 AND ORGANIZATION_ID = nOrg_ID
2909 AND ELEMENT_VALUE IS NOT NULL;
2910
2911 if (CZ_IMP_ALL.get_time) then
2912 loop_end_time := dbms_utility.get_time();
2913 d_str := inRun_Id || ' Bulk collect prop val (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
2914 xError:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
2915 end if;
2916
2917 COMMIT;
2918 EXCEPTION
2919 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
2920 RAISE;
2921 WHEN NO_DATA_FOUND THEN
2922 NULL;
2923 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
2924 RAISE;
2925 WHEN OTHERS THEN
2926 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
2927 xERROR:=cz_utils.log_report(d_str,1,'EXTR_ITEM_PROPERTY_VALUES',11276,inRun_Id);
2928 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
2929 END;
2930 ------------------------------------------------------------------------------------------
2931 PROCEDURE extract_table(inRun_ID IN PLS_INTEGER,
2932 table_name IN VARCHAR2,
2933 nOrg_ID IN NUMBER,
2934 nTop_ID IN NUMBER,
2935 sExpl_type IN VARCHAR2,
2936 nModelId IN NUMBER)
2937 IS
2938 lower_table_name VARCHAR2(50) := LOWER(table_name);
2939 xERROR BOOLEAN:=FALSE;
2940 st_time number;
2941 end_time number;
2942 d_str varchar2(255);
2943 BEGIN
2944 --DBMS_OUTPUT.ENABLE;
2945 --DBMS_OUTPUT.PUT_LINE('EXTRACTING TABLE: ' || lower_table_name);
2946 IF(lower_table_name='cz_item_masters') THEN
2947 if (CZ_IMP_ALL.get_time) then
2948 st_time := dbms_utility.get_time();
2949 end if;
2950 EXTR_ITEM_MASTER(inRun_ID,nOrg_ID,nTop_ID,sExpl_type);
2951 if (CZ_IMP_ALL.get_time) then
2952 end_time := dbms_utility.get_time();
2953 d_str := inRun_Id || ' Extract item master (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
2954 xERROR:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
2955 end if;
2956 ELSIF(lower_table_name='cz_ps_nodes') THEN
2957 if (CZ_IMP_ALL.get_time) then
2958 st_time := dbms_utility.get_time();
2959 end if;
2960 EXTR_PS_NODE(inRun_ID,nOrg_ID,nTop_ID,sExpl_type,nModelId);
2961 if (CZ_IMP_ALL.get_time) then
2962 end_time := dbms_utility.get_time();
2963 d_str :=inRun_Id || ' Extract ps structure (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
2964 xERROR:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
2965 end if;
2966 ELSIF(lower_table_name='cz_localized_texts') THEN
2967 if (CZ_IMP_ALL.get_time) then
2968 st_time := dbms_utility.get_time();
2969 end if;
2970 EXTR_INTL_TEXT(inRun_ID,nOrg_ID,nTop_ID,sExpl_type,nModelId);
2971 if (CZ_IMP_ALL.get_time) then
2972 end_time := dbms_utility.get_time();
2973 d_str := inRun_Id || ' Extract intl text (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
2974 xERROR:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
2975 end if;
2976 ELSIF(lower_table_name='cz_devl_projects') THEN
2977 if (CZ_IMP_ALL.get_time) then
2978 st_time := dbms_utility.get_time();
2979 end if;
2980 EXTR_DEVL_PROJECT(inRun_ID,nOrg_ID,nTop_ID,sExpl_type,nModelId);
2981 if (CZ_IMP_ALL.get_time) then
2982 end_time := dbms_utility.get_time();
2983 d_str := inRun_Id || ' Extract devl project (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
2984 xERROR:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
2985 end if;
2986 ELSIF(lower_table_name='cz_item_types') THEN
2987 if (CZ_IMP_ALL.get_time) then
2988 st_time := dbms_utility.get_time();
2989 end if;
2990 EXTR_ITEM_TYPES(inRun_ID,nOrg_ID,nTop_ID,sExpl_type);
2991 if (CZ_IMP_ALL.get_time) then
2992 end_time := dbms_utility.get_time();
2993 d_str := inRun_Id || ' Extract item types (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
2994 xERROR:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
2995 end if;
2996 ELSIF(lower_table_name='cz_properties') THEN
2997 if (CZ_IMP_ALL.get_time) then
2998 st_time := dbms_utility.get_time();
2999 end if;
3000 EXTR_PROPERTIES(inRun_ID,nOrg_ID,nTop_ID,sExpl_type);
3001
3002 EXTR_APC_PROPERTIES(p_run_id => inRun_ID,
3003 p_org_id => nOrg_ID,
3004 p_top_item_id => nTop_ID,
3005 p_expl_type => sExpl_type);
3006
3007 if (CZ_IMP_ALL.get_time) then
3008 end_time := dbms_utility.get_time();
3009 d_str := inRun_Id || ' Extract property (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
3010 xERROR:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
3011 end if;
3012 ELSIF(lower_table_name='cz_item_type_properties') THEN
3013 if (CZ_IMP_ALL.get_time) then
3014 st_time := dbms_utility.get_time();
3015 end if;
3016 EXTR_ITEM_TYPE_PROPERTIES(inRun_ID,nOrg_ID,nTop_ID,sExpl_type);
3017 if (CZ_IMP_ALL.get_time) then
3018 end_time := dbms_utility.get_time();
3019 d_str := inRun_Id || ' Extract item type property (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
3020 xERROR:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
3021 end if;
3022 ELSIF(lower_table_name='cz_item_property_values') THEN
3023 if (CZ_IMP_ALL.get_time) then
3024 st_time := dbms_utility.get_time();
3025 end if;
3026 EXTR_ITEM_PROPERTY_VALUES(inRun_ID,nOrg_ID,nTop_ID,sExpl_type);
3027 if (CZ_IMP_ALL.get_time) then
3028 end_time := dbms_utility.get_time();
3029 d_str := inRun_Id || ' Extract item property value (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
3030 xERROR:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,inRun_Id);
3031 end if;
3032 ELSE
3033 --DBMS_OUTPUT.PUT_LINE(CZ_UTILS.GET_TEXT('CZ_IMP_INVALID_TABLE_EXTRACT','TABLENAME',table_name));
3034 xERROR:=cz_utils.log_report(CZ_UTILS.GET_TEXT('CZ_IMP_INVALID_TABLE_EXTRACT','TABLENAME',table_name),1,'SINGLEBILL.EXTRACT_TABLE',11276,inRun_Id);
3035 END IF;
3036 END extract_table;
3037 ------------------------------------------------------------------------------------------
3038 PROCEDURE populate_table(inRun_ID IN PLS_INTEGER,
3039 table_name IN VARCHAR2,
3040 commit_size IN PLS_INTEGER,
3041 max_err IN PLS_INTEGER,
3042 inXFR_GROUP IN VARCHAR2,
3043 p_rp_folder_id IN NUMBER,
3044 x_failed IN OUT NOCOPY NUMBER
3045 )
3046 IS
3047 lower_table_name VARCHAR2(50) := LOWER(table_name);
3048 xERROR BOOLEAN:=FALSE;
3049 Inserts PLS_INTEGER;
3050 Updates PLS_INTEGER;
3051 Dups PLS_INTEGER;
3052 NoChange PLS_INTEGER;
3053 st_time number;
3054 end_time number;
3055 d_str varchar2(255);
3056 BEGIN
3057 --DBMS_OUTPUT.ENABLE;
3058 --DBMS_OUTPUT.PUT_LINE('IMPORTING TABLE: ' || lower_table_name);
3059 IF(lower_table_name='cz_item_masters') THEN
3060 if (CZ_IMP_ALL.get_time) then
3061 st_time := dbms_utility.get_time();
3062 end if;
3063 CZ_IMP_IM_MAIN.MAIN_ITEM_MASTER(inRun_ID, commit_size, max_err,
3064 Inserts, Updates, x_failed, Dups,NoChange, inXFR_GROUP);
3065 if (CZ_IMP_ALL.get_time) then
3066 end_time := dbms_utility.get_time();
3067 d_str := inRun_Id || ' Populate items :' || (end_time-st_time)/100.00;
3068 xERROR:=cz_utils.log_report(d_str,1,'POPULATE',11299,inRun_Id);
3069 end if;
3070 ELSIF(lower_table_name='cz_ps_nodes') THEN
3071 if (CZ_IMP_ALL.get_time) then
3072 st_time := dbms_utility.get_time();
3073 end if;
3074 CZ_IMP_PS_NODE.MAIN_PS_NODE(inRun_ID, commit_size, max_err,
3075 Inserts, Updates, x_failed, Dups, NoChange,inXFR_GROUP);
3076 if (CZ_IMP_ALL.get_time) then
3077 end_time := dbms_utility.get_time();
3078 d_str := inRun_Id || ' Populate product structure :' || (end_time-st_time)/100.00;
3079 xERROR:=cz_utils.log_report(d_str,1,'POPULATE',11299,inRun_Id);
3080 end if;
3081 ELSIF(lower_table_name='cz_localized_texts') THEN
3082 if (CZ_IMP_ALL.get_time) then
3083 st_time := dbms_utility.get_time();
3084 end if;
3085 CZ_IMP_PS_NODE.MAIN_INTL_TEXT(inRun_ID, commit_size, max_err,
3086 Inserts, Updates, x_failed, Dups, NoChange,inXFR_GROUP);
3087 if (CZ_IMP_ALL.get_time) then
3088 end_time := dbms_utility.get_time();
3089 d_str := inRun_Id || ' Populate intl text :' || (end_time-st_time)/100.00;
3090 xERROR:=cz_utils.log_report(d_str,1,'POPULATE',11299,inRun_Id);
3091 end if;
3092 ELSIF(lower_table_name='cz_devl_projects') THEN
3093 if (CZ_IMP_ALL.get_time) then
3094 st_time := dbms_utility.get_time();
3095 end if;
3096 CZ_IMP_PS_NODE.MAIN_DEVL_PROJECT(inRun_ID, commit_size, max_err,
3097 Inserts, Updates, x_failed, Dups,NoChange,
3098 inXFR_GROUP,p_rp_folder_id, g_CONFIG_ENGINE_TYPE);
3099 if (CZ_IMP_ALL.get_time) then
3100 end_time := dbms_utility.get_time();
3101 d_str := inRun_Id || ' Populate project :' || (end_time-st_time)/100.00;
3102 xERROR:=cz_utils.log_report(d_str,1,'POPULATE',11299,inRun_Id);
3103 end if;
3104 ELSIF(lower_table_name='cz_item_property_values') THEN
3105 if (CZ_IMP_ALL.get_time) then
3106 st_time := dbms_utility.get_time();
3107 end if;
3108 CZ_IMP_IM_MAIN.MAIN_ITEM_PROPERTY_VALUE(inRun_ID, commit_size, max_err,
3109 Inserts, Updates, x_failed, Dups, NoChange,inXFR_GROUP);
3110 if (CZ_IMP_ALL.get_time) then
3111 end_time := dbms_utility.get_time();
3112 d_str := inRun_Id || ' Populate item property val :' || (end_time-st_time)/100.00;
3113 xERROR:=cz_utils.log_report(d_str,1,'POPULATE',11299,inRun_Id);
3114 end if;
3115 ELSIF(lower_table_name='cz_item_types') THEN
3116 if (CZ_IMP_ALL.get_time) then
3117 st_time := dbms_utility.get_time();
3118 end if;
3119 CZ_IMP_IM_MAIN.MAIN_ITEM_TYPE(inRun_ID, commit_size, max_err,
3120 Inserts, Updates, x_failed, Dups, NoChange,inXFR_GROUP);
3121 if (CZ_IMP_ALL.get_time) then
3122 end_time := dbms_utility.get_time();
3123 d_str := inRun_Id || ' Populate item type :' || (end_time-st_time)/100.00;
3124 xERROR:=cz_utils.log_report(d_str,1,'POPULATE',11299,inRun_Id);
3125 end if;
3126 ELSIF(lower_table_name='cz_item_type_properties') THEN
3127 if (CZ_IMP_ALL.get_time) then
3128 st_time := dbms_utility.get_time();
3129 end if;
3130 CZ_IMP_IM_MAIN.MAIN_ITEM_TYPE_PROPERTY(inRun_ID, commit_size, max_err,
3131 Inserts, Updates, x_failed, Dups, inXFR_GROUP);
3132 if (CZ_IMP_ALL.get_time) then
3133 end_time := dbms_utility.get_time();
3134 d_str := inRun_Id || ' Populate item type property :' || (end_time-st_time)/100.00;
3135 xERROR:=cz_utils.log_report(d_str,1,'POPULATE',11299,inRun_Id);
3136 end if;
3137 ELSIF(lower_table_name='cz_properties') THEN
3138 if (CZ_IMP_ALL.get_time) then
3139 st_time := dbms_utility.get_time();
3140 end if;
3141 CZ_IMP_IM_MAIN.MAIN_PROPERTY(inRun_ID, commit_size, max_err,
3142 Inserts, Updates, x_failed, Dups, NoChange,inXFR_GROUP, p_rp_folder_id);
3143 if (CZ_IMP_ALL.get_time) then
3144 end_time := dbms_utility.get_time();
3145 d_str := inRun_Id || ' Populate property :' || (end_time-st_time)/100.00;
3146 xERROR:=cz_utils.log_report(d_str,1,'POPULATE',11299,inRun_Id);
3147 end if;
3148 ELSE
3149 --DBMS_OUTPUT.PUT_LINE(CZ_UTILS.GET_TEXT('CZ_IMP_INVALID_TABLE_IMPORT','TABLENAME',table_name));
3150 xERROR:=cz_utils.log_report(CZ_UTILS.GET_TEXT('CZ_IMP_INVALID_TABLE_IMPORT','TABLENAME',table_name),1,'CZ_IMP_SINGLE.POPULATE_TABLE',11276,inRun_Id);
3151 END IF;
3152 --DBMS_OUTPUT.PUT_LINE('INSERTS: '||to_char(Inserts));
3153 --DBMS_OUTPUT.PUT_LINE('UPDATES: '||to_char(Updates));
3154 --DBMS_OUTPUT.PUT_LINE('FAILED: '||to_char(x_failed));
3155 --DBMS_OUTPUT.PUT_LINE('DUPLICATES: '||to_char(Dups));
3156 EXCEPTION
3157 WHEN IMP_CHILD_MULTI_COPIES THEN
3158 RAISE;
3159 END populate_table;
3160 ------------------------------------------------------------------------------------------
3161 FUNCTION get_remote_import
3162 RETURN BOOLEAN IS
3163
3164 v_enabled VARCHAR2(1) := '1';
3165 v_local VARCHAR2(8) := 'LOCAL';
3166
3167 CURSOR check_remote_import IS
3168 SELECT server_local_id FROM CZ_SERVERS
3169 WHERE local_name = v_local
3170 AND import_enabled = v_enabled;
3171
3172 l_server_id CZ_SERVERS.server_local_id%type;
3173 x_get_remote_import_f BOOLEAN := false;
3174 xError boolean := false;
3175 d_str VARCHAR2(2000);
3176
3177 BEGIN
3178 -- check if this import session is local or remote. If remote do not use autonomous
3179 -- transactions
3180 OPEN check_remote_import;
3181 FETCH check_remote_import INTO l_server_id;
3182 x_get_remote_import_f := check_remote_import%FOUND;
3183 CLOSE check_remote_import;
3184
3185 IF (x_get_remote_import_f) THEN
3186 RETURN FALSE;
3187 ELSE
3188 RETURN TRUE;
3189 END IF;
3190 EXCEPTION
3191 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
3192 RAISE;
3193 WHEN OTHERS THEN
3194 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3195 xERROR:=cz_utils.log_report(d_str,1,'CZ_IMP_SINGLE.GET_REMOTE_IMPORT',11276);
3196 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
3197 END GET_REMOTE_IMPORT;
3198 ------------------------------------------------------------------------------------------
3199 FUNCTION isAppsVersion11i(fndLinkName IN VARCHAR2)
3200 RETURN BOOLEAN
3201 IS
3202
3203 -- Check version of apps for existence of mtl_system_items_tl table
3204 -- This table was introduced for Multilingual support in 11i
3205
3206 xERROR BOOLEAN:=FALSE;
3207 vString VARCHAR2(255);
3208 v_count NUMBER := 0;
3209 v_inv_oracle_schema VARCHAR2(255);
3210 d_str varchar2(255);
3211 BEGIN
3212 get_App_Info('INV', v_inv_oracle_schema);
3213 vString := 'select count(*) from all_tables' ||fndLinkName||
3214 ' where owner='''||v_inv_oracle_schema||''' AND table_name = ''MTL_SYSTEM_ITEMS_TL'' AND ROWNUM<2';
3215
3216 EXECUTE IMMEDIATE vString into v_count;
3217 IF (v_count > 0) then
3218 return true;
3219 ELSE
3220 return false;
3221 END IF;
3222
3223 EXCEPTION
3224 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
3225 RAISE;
3226 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
3227 RAISE;
3228 WHEN OTHERS THEN
3229 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3230 xERROR:=cz_utils.log_report(d_str,1,'CZ_IMP_SINGLE.isAppsVersion11i',11276);
3231 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
3232 END;
3233
3234 ------------------------------------------------------------------------------------------
3235 FUNCTION ExtractPsNode(inRunId IN NUMBER,
3236 inOrgId IN NUMBER,
3237 inTopId IN NUMBER,
3238 inExplType IN VARCHAR2,
3239 inServerId IN NUMBER,
3240 inRunExploder IN PLS_INTEGER,
3241 inRevDate IN DATE,
3242 inDateFormat IN VARCHAR2,
3243 inRefreshModelId IN NUMBER,
3244 inCopyRootModel IN VARCHAR2,
3245 inCopyChildModels IN VARCHAR2,
3246 inGenStatistics IN PLS_INTEGER)
3247 RETURN NUMBER IS
3248
3249 CURSOR c_expl (inLang VARCHAR2) IS
3250 SELECT component_item_id, component_code FROM cz_exv_item_master
3251 WHERE top_item_id = inTopId
3252 AND organization_id = inOrgId
3253 AND explosion_type = inExplType
3254 AND bom_item_type = 1
3255 AND plan_level > 0
3256 AND language = inLang
3257 order by plan_level;
3258
3259 --LA 9595142 14544881
3260 CURSOR c_ref IS
3261 SELECT component_code, component_sequence_id, component_sequence_path, -- bom_sort_order,
3262 substr(fsk_itemmaster_2_1,1,instr(fsk_itemmaster_2_1,':')-1),
3263 organization_id, explosion_type
3264 FROM cz_imp_ps_nodes
3265 WHERE run_id = inRunId
3266 AND organization_id = inOrgId
3267 AND top_item_id = inTopId
3268 AND ps_node_type = 263;
3269
3270 TYPE tStringArray IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
3271 tabCompCode tStringArray;
3272 tabTraversedChildCode tStringArray;
3273
3274 nModelId NUMBER;
3275 nSecondaryId NUMBER;
3276 outGrp_ID NUMBER;
3277 outError_code NUMBER;
3278 outErr_msg VARCHAR2(255);
3279 xERROR BOOLEAN:=FALSE;
3280 nTopItemId NUMBER;
3281 compCode VARCHAR2(2000);
3282 SubModelFlag PLS_INTEGER;
3283 l_check PLS_INTEGER;
3284
3285 lastCompCode VARCHAR2(2000);
3286 nChildTopItemId NUMBER;
3287
3288 bom_explode_st number;
3289 bom_explode_end number;
3290 d_str varchar2(255);
3291
3292 childDevlProjectId cz_devl_projects.devl_project_id%TYPE;
3293 l_lang VARCHAR2(4);
3294
3295 --LA 9595142
3296 l_curr_ref_id NUMBER;
3297 nImpEnabledModelId NUMBER;
3298 lCompCode CZ_IMP_PS_NODES.COMPONENT_CODE%TYPE;
3299 --lBomSortOrder CZ_IMP_PS_NODES.BOM_SORT_ORDER%TYPE; -- 14544881
3300 lCompSeqId CZ_IMP_PS_NODES.COMPONENT_SEQUENCE_ID%TYPE; -- 14544881
3301 lCompSeqPath CZ_IMP_PS_NODES.COMPONENT_SEQUENCE_PATH%TYPE; -- 14544881
3302 lInvItemId NUMBER;
3303 lOrgId NUMBER;
3304 lExplType CZ_IMP_PS_NODES.EXPLOSION_TYPE%TYPE;
3305 lDevlPrjOSR CZ_IMP_PS_NODES.FSK_DEVLPROJECT_5_1%TYPE;
3306
3307 ------------------------------------------------------------------------------------------
3308 --LA 9595142
3309 PROCEDURE getImpEnabledModelId (inDevlPrjOSR IN VARCHAR2,
3310 inRunId IN PLS_INTEGER,
3311 xModelId IN OUT NOCOPY NUMBER ) IS
3312 BEGIN
3313 SELECT d.devl_project_id INTO xModelId FROM cz_devl_projects d
3314 WHERE d.orig_sys_ref = inDevlPrjOSR
3315 AND d.deleted_flag = '0'
3316 AND d.import_enabled = '1'
3317 AND NVL(d.config_engine_type,'L') = g_CONFIG_ENGINE_TYPE --LA 12548898 Engine Sync
3318 AND EXISTS (SELECT NULL FROM CZ_RP_ENTRIES WHERE deleted_flag = '0'
3319 AND object_type = 'PRJ' AND object_id = d.devl_project_id) ;
3320 EXCEPTION
3321 WHEN TOO_MANY_ROWS THEN
3322 d_str:= 'Multiple copies of the model enabled for Import : (' || inDevlPrjOSR || ')' ;
3323 xERROR:=cz_utils.log_report(d_str,1,'ExtractPsNode.ImpEnabledChk',11276,inRunId);
3324 --xModelId := -1;
3325 xModelId := NULL;
3326 RAISE IMP_MODEL_NOT_ENABLED ;
3327 WHEN NO_DATA_FOUND THEN
3328 -- It could error out when model is not enabled for import
3329 -- or there is no entry in either cz_rp_entries or cz_devl_projects
3330 -- for given orig_sys_ref
3331 SELECT count(*) INTO xModelId FROM cz_devl_projects d, cz_rp_entries r
3332 WHERE d.orig_sys_ref = inDevlPrjOSR
3333 AND d.deleted_flag = '0'
3334 AND r.deleted_flag = '0'
3335 AND r.object_type = 'PRJ'
3336 AND NVL(d.import_enabled,'0') = '0'
3337 AND NVL(d.config_engine_type,'L') = g_CONFIG_ENGINE_TYPE --LA 12548898 Engine Sync
3338 AND d.devl_project_id = r.object_id;
3339 IF (xModelId > 0) THEN
3340 d_str:= 'Either model does not exist in Repository or not enabled for Import :'
3341 || inDevlPrjOSR ;
3342 xERROR:=cz_utils.log_report(d_str,1,'ExtractPsNode.ImpEnabledChk',11276,inRunId);
3343 --xModelId := -1;
3344 xModelId := NULL;
3345 RAISE IMP_MODEL_NOT_ENABLED ;
3346 END IF;
3347 xModelId := NULL;
3348 WHEN OTHERS THEN
3349 --xModelId := -1;
3350 xModelId := NULL;
3351 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3352 xERROR:=cz_utils.log_report(d_str,1,'ExtractPsNode.ImpEnabledChk',11276,inRunId);
3353 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
3354 END getImpEnabledModelId;
3355
3356 -------------
3357 PROCEDURE checkImpEnabled (inDevlPrjOSR IN VARCHAR2,
3358 inInvItemId IN NUMBER,
3359 inRunId IN PLS_INTEGER,
3360 inCurrModelId IN NUMBER,
3361 xImpEnabledModelId IN OUT NOCOPY NUMBER,
3362 inProcessingModel IN NUMBER) IS
3363
3364
3365 BEGIN
3366
3367 --DBMS_OUTPUT.PUT_LINE('inInvItemId=' || inInvItemId || ' inDevlPrjOSR=' || inDevlPrjOSR ||
3368 --' inCurrModelId=' || inCurrModelId || ' xImpEnabledModelId=' || xImpEnabledModelId );
3369
3370 --Commenting out inCurrModelId <> -999 condition as during import of new model
3371 --it'll go in false condition and will fail.
3372 --IF (inCurrModelId <> -999 AND NOT import_enabled_tbl.EXISTS(inInvItemId)) THEN
3373 IF (NOT import_enabled_tbl.EXISTS(inInvItemId)) THEN
3374 getImpEnabledModelId(inDevlPrjOSR, inRunId, xImpEnabledModelId) ;
3375 IF (xImpEnabledModelId IS NOT NULL) THEN
3376 import_enabled_tbl(inInvItemId) := xImpEnabledModelId;
3377 ELSE
3378 xImpEnabledModelId := -1;
3379 END IF;
3380
3381 --DBMS_OUTPUT.PUT_LINE('*** ' || inDevlPrjOSR || ' = ' || xImpEnabledModelId);
3382 ELSE
3383
3384 xImpEnabledModelId := import_enabled_tbl(inInvItemId);
3385 --DBMS_OUTPUT.PUT_LINE('---Exists ' || inDevlPrjOSR || ' = ' || xImpEnabledModelId);
3386 END IF;
3387
3388 IF (inCurrModelId <> -999 AND xImpEnabledModelId > 0 AND xImpEnabledModelId <> inCurrModelId) THEN
3389
3390 d_str:= 'Imported referenced model is different than the one that is enabled ' ;
3391 IF (inProcessingModel > 0) THEN
3392 d_str:= d_str || ' in model id ' || inProcessingModel ;
3393 END IF;
3394 d_str:= d_str || ' for Model- ' || inDevlPrjOSR || ' Current ModelId=' || inCurrModelId ||
3395 ' Expecting ModelId=' || xImpEnabledModelId;
3396
3397 --DBMS_OUTPUT.PUT_LINE(d_str);
3398
3399 xERROR:=cz_utils.log_report(d_str,1,'ExtractPsNode.checkImpEnabled',11276,inRunId);
3400 RAISE IMP_MODEL_NOT_ENABLED ;
3401 END IF;
3402
3403
3404 END checkImpEnabled;
3405 ------------------------------------------------------------------------
3406
3407 BEGIN -- ExtractPsNode
3408
3409
3410 IF(inCopyRootModel = '1')THEN
3411
3412 SELECT CZ_XFR_PROJECT_BILLS_S.NEXTVAL INTO nModelId FROM DUAL;
3413
3414 INSERT INTO CZ_XFR_PROJECT_BILLS
3415 (ORGANIZATION_ID,TOP_ITEM_ID,EXPLOSION_TYPE,DELETED_FLAG,MODEL_PS_NODE_ID,copy_addl_child_models,
3416 source_server)
3417 SELECT inOrgId,inTopId,inExplType,'0',nModelId,inCopyChildModels,inServerId
3418 FROM DUAL;
3419
3420 ELSE
3421
3422 IF(inRefreshModelId IS NULL OR inRefreshModelId = -1)THEN
3423
3424 nModelId := NULL;
3425
3426 --LA 9595142 If multiple copies refresh disabled check whether the given model is enabled
3427 IF (disableMultipleCopyImport = 1) THEN
3428 checkImpEnabled(inExplType || ':' || inOrgId || ':' || inTopId, inTopId, inRunId, -999,nModelId, -1);
3429 ELSE
3430 BEGIN
3431 SELECT p.model_ps_node_id INTO nModelId
3432 FROM cz_xfr_project_bills p, cz_devl_projects d
3433 WHERE p.organization_id = inOrgId
3434 AND p.top_item_id = inTopId
3435 AND p.explosion_type = inExplType
3436 AND d.deleted_flag = '0'
3437 AND d.devl_project_id = d.persistent_project_id
3438 AND NVL(d.config_engine_type,'L') = g_CONFIG_ENGINE_TYPE --LA 12548898 Engine Sync
3439 AND p.model_ps_node_id = d.devl_project_id;
3440 EXCEPTION
3441 WHEN OTHERS THEN
3442 nModelId := NULL;
3443 END;
3444 END IF;
3445
3446 IF(nModelId IS NOT NULL AND nModelId > 0) THEN
3447
3448 --LA 9595142
3449 IF (disableMultipleCopyImport = 1) THEN
3450 -- If record does not exist in cz_xfr_project_bills for model that is import_enabled
3451 -- then insert that record in cz_xfr_project_bills
3452 -- This'll be required when copy is enabled for refresh
3453 INSERT INTO CZ_XFR_PROJECT_BILLS
3454 (ORGANIZATION_ID,TOP_ITEM_ID,EXPLOSION_TYPE,DELETED_FLAG,MODEL_PS_NODE_ID,copy_addl_child_models,
3455 source_server)
3456 SELECT inOrgId,inTopId,inExplType,'0',d.devl_project_id,inCopyChildModels,inServerId
3457 FROM CZ_DEVL_PROJECTS d
3458 WHERE d.orig_sys_ref = inExplType || ':' || inOrgId || ':' || inTopId
3459 AND NVL(d.config_engine_type,'L') = g_CONFIG_ENGINE_TYPE --LA 12548898 Engine Sync
3460 AND d.deleted_flag = '0' and d.import_enabled = '1' AND EXISTS
3461 (SELECT NULL FROM CZ_RP_ENTRIES WHERE deleted_flag = '0' and object_type = 'PRJ'
3462 AND object_id = d.devl_project_id) AND NOT EXISTS
3463 (SELECT NULL FROM CZ_XFR_PROJECT_BILLS WHERE model_ps_node_id = d.devl_project_id);
3464 END IF;
3465
3466 UPDATE cz_xfr_project_bills SET
3467 deleted_flag = '0',
3468 copy_addl_child_models = inCopyChildModels,
3469 source_server = inServerId
3470 WHERE model_ps_node_id = nModelId;
3471
3472 ELSE
3473
3474 SELECT CZ_XFR_PROJECT_BILLS_S.NEXTVAL INTO nModelId FROM DUAL;
3475
3476 INSERT INTO CZ_XFR_PROJECT_BILLS
3477 (ORGANIZATION_ID,TOP_ITEM_ID,EXPLOSION_TYPE,DELETED_FLAG,MODEL_PS_NODE_ID,copy_addl_child_models,
3478 source_server)
3479 SELECT inOrgId,inTopId,inExplType,'0',nModelId,inCopyChildModels,inServerId
3480 FROM DUAL;
3481
3482 END IF;
3483
3484 ELSE -- (inRefreshModelId IS NULL OR inRefreshModelId = -1)
3485
3486 nModelId := inRefreshModelId;
3487
3488 --LA 9595142
3489 IF (disableMultipleCopyImport = 1) THEN
3490 checkImpEnabled(inExplType || ':' || inOrgId || ':' || inTopId, inTopId, inRunId, inRefreshModelId,nModelId, -1);
3491 END IF;
3492
3493 INSERT INTO CZ_XFR_PROJECT_BILLS
3494 (ORGANIZATION_ID,TOP_ITEM_ID,EXPLOSION_TYPE,DELETED_FLAG,MODEL_PS_NODE_ID,copy_addl_child_models,
3495 source_server)
3496 SELECT inOrgId,inTopId,inExplType,'0',inRefreshModelId,inCopyChildModels,inServerId
3497 FROM DUAL WHERE NOT EXISTS
3498 (SELECT NULL FROM CZ_XFR_PROJECT_BILLS WHERE model_ps_node_id = inRefreshModelId);
3499
3500 UPDATE cz_xfr_project_bills SET
3501 deleted_flag = '0',
3502 copy_addl_child_models = inCopyChildModels,
3503 source_server = inServerId
3504 WHERE model_ps_node_id = inRefreshModelId;
3505
3506 END IF;
3507 END IF;
3508
3509 if (CZ_IMP_ALL.get_time) then
3510 bom_explode_st := dbms_utility.get_time();
3511 end if;
3512
3513 IF((inRunExploder = 1) and (NOT(GET_REMOTE_IMPORT))) THEN
3514
3515 DELETE FROM bom_explosions WHERE explosion_type = inExplType AND organization_id = inOrgId
3516 AND top_item_id = inTopId;
3517 COMMIT;
3518
3519 BOMPNORD.bmxporder_explode_for_order(inOrgId, 2, inExplType, 1, outGrp_ID,
3520 0, 60, inTopId, '', SYSDATE-1000, TO_CHAR(inRevDate, inDateFormat),
3521 0, 'N', outErr_msg, outError_code);
3522
3523 COMMIT;
3524
3525 IF(inGenStatistics = 1)THEN
3526
3527 fnd_stats.gather_table_stats('BOM', 'BOM_EXPLOSIONS');
3528 END IF;
3529
3530 IF(outError_code <> 0)THEN
3531 xERROR:=cz_utils.log_report(outErr_msg, 1, 'BOM_EXPLODER', 11276,inRunId);
3532 CZ_ORAAPPS_INTEGRATE.mRETCODE := 2;
3533 CZ_ORAAPPS_INTEGRATE.mERRBUF := outErr_msg;
3534 RETURN nModelId;
3535 END IF;
3536 ELSE
3537
3538 --Bug #5347969. If exploding is disabled or import is remote (we do not explode remotely),
3539 --we need to check if the available explosion is up-to-date and fail if it is not,
3540 --otherwise it is possible to delete active items from Configurator.
3541
3542 BEGIN
3543
3544 SELECT 1 INTO l_check FROM cz_exv_bom_explosions
3545 WHERE top_item_id = inTopId
3546 AND organization_id= inOrgId
3547 AND explosion_type = inExplType
3548 AND rexplode_flag = 1
3549 AND ROWNUM = 1;
3550
3551 --If found, explosion is not up-to-date.
3552
3553 outErr_msg := CZ_UTILS.GET_TEXT('CZ_IMP_STALE_EXPLOSION');
3554 xERROR:=CZ_IMP_ALL.REPORT(outErr_msg, 1, 'BOM_EXPLODER', 11276);
3555 CZ_ORAAPPS_INTEGRATE.mRETCODE := 2;
3556 CZ_ORAAPPS_INTEGRATE.mERRBUF := outErr_msg;
3557 RETURN nModelId;
3558
3559 EXCEPTION
3560 WHEN NO_DATA_FOUND THEN
3561
3562 --This exception means we can continue.
3563
3564 NULL;
3565 END;
3566 END IF;
3567
3568 if (CZ_IMP_ALL.get_time) then
3569 bom_explode_end := dbms_utility.get_time();
3570 d_str := inRunId || ' Bom Exploder (' || inTopId || ') :' || (bom_explode_end-bom_explode_st)/100.00;
3571 xERROR:=cz_utils.log_report(d_str,1,'BOM_EXPLODER',11299,inRunId);
3572 end if;
3573
3574 extract_table(inRunId, 'CZ_PS_NODES', inOrgId, inTopId, inExplType, nModelId);
3575
3576 --LA 9595142
3577 --After adding the data to import table check for the references that'll be imported
3578 --against import enabled models
3579 --
3580 IF (disableMultipleCopyImport = 1) THEN
3581 OPEN c_ref ;
3582 LOOP
3583 BEGIN
3584 --14544881
3585 FETCH c_ref INTO lCompCode, lCompSeqId, lCompSeqPath, --lBomSortOrder,
3586 lInvItemId, lOrgId, lExplType ;
3587 EXIT WHEN c_ref%NOTFOUND;
3588
3589 nImpEnabledModelId := -1;
3590
3591 BEGIN
3592 SELECT reference_id INTO l_curr_ref_id FROM cz_ps_nodes
3593 WHERE devl_project_id = nModelId
3594 AND deleted_flag = '0' and substr(orig_sys_ref,1,instr(orig_sys_ref,':') - 1) = lCompCode
3595 AND component_sequence_id = lCompSeqId
3596 AND component_sequence_path = lCompSeqPath ; -- 14544881 Added CompSeqId and CompSeqPath
3597 --AND bom_sort_order = lBomSortOrder; -- 14544881 Added CompSeqId and CompSeqPath
3598
3599 EXCEPTION
3600 WHEN NO_DATA_FOUND THEN
3601 --Model might be getting added first time.
3602 --Will continue to add it to array for the time being
3603 --DBMS_OUTPUT.PUT_LINE('No Data Found.....nModelId = ' || nModelId || ' and CompCode=' || lCompCode );
3604
3605 -- 14544881
3606 --NULL;
3607 l_curr_ref_id := -999 ;
3608
3609 WHEN OTHERS THEN
3610 --DBMS_OUTPUT.PUT_LINE('Error retrieveing reference_id: ' || SQLERRM);
3611 --DBMS_OUTPUT.PUT_LINE('.....nModelId = ' || nModelId || ' and CompCode=' || lCompCode );
3612
3613 -- 14544881
3614 d_str := 'Error retrieveing reference_id: ' || SQLERRM ;
3615 xERROR:=cz_utils.log_report(d_str,1,'ExtractPsNode.RECURSION',11276,inRunId);
3616 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
3617 END;
3618 --DBMS_OUTPUT.PUT_LINE('lInvItemId=' || lInvItemId || ' lCompCode=' || lCompCode || ' nImpEnabledModelId=' || nImpEnabledModelId);
3619 lDevlPrjOSR := lExplType || ':' || lOrgId || ':' || lInvItemId ;
3620 checkImpEnabled(lDevlPrjOSR, lInvItemId, inRunId, l_curr_ref_id,nImpEnabledModelId, nModelId);
3621
3622 EXCEPTION
3623 WHEN IMP_MODEL_NOT_ENABLED THEN
3624 RAISE ;
3625 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
3626 RAISE;
3627 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
3628 RAISE;
3629 WHEN OTHERS THEN
3630 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3631 d_str:= d_str || ' Current info:' || inOrgId || ':'|| nTopItemId;
3632 xERROR:=cz_utils.log_report(d_str,1,'ExtractPsNode.RECURSION',11276,inRunId);
3633 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
3634 END;
3635 END LOOP;
3636 CLOSE c_ref;
3637 END IF;
3638
3639 l_lang := userenv('LANG');
3640
3641 OPEN c_expl (l_lang);
3642 LOOP
3643 BEGIN
3644 FETCH c_expl INTO nTopItemId, compCode;
3645 EXIT WHEN c_expl%NOTFOUND;
3646
3647 -- if this model has been processed within this import session, it doesn't need
3648 -- to be processed again
3649 IF(NOT processed_expls_tbl.EXISTS(nTopItemId)) THEN
3650 SubModelFlag := 0;
3651 /*
3652 -- This code may be obsoleted by use of processed_expls_tbl, but it shouldn't hurt
3653 -- to leave it in.
3654
3655 IF(tabCompCode.COUNT > 0)THEN
3656 FOR i IN 1..tabCompCode.COUNT LOOP
3657 IF(INSTR(compCode,tabCompCode(i)) <> 0)THEN
3658 SubModelFlag := 1;
3659 EXIT;
3660 END IF;
3661 END LOOP;
3662 END IF;
3663 --Commenting this piece of code as this is obsoleted by use of processed_expls_tbl.//Bug6979513
3664 */
3665 IF(SubModelFlag = 0)THEN
3666
3667 -- Skip if not refreshing unchanged child models.
3668 IF(importUnchangedChildModels = 0) THEN
3669 IF(NOT (importChildModel(inRunId, inOrgId, nTopItemId,inExplType))) THEN
3670
3671 nSecondaryId := ExtractPsNode(inRunId, inOrgId, nTopItemId, inExplType, inServerId,
3672 inRunExploder, inRevDate, inDateFormat, -1, 0, 0, 0);
3673 END IF;
3674 ELSE
3675 nSecondaryId := ExtractPsNode(inRunId, inOrgId, nTopItemId, inExplType, inServerId,
3676 inRunExploder, inRevDate, inDateFormat, -1, 0, 0, 0);
3677 END IF;
3678
3679 tabCompCode(tabCompCode.COUNT + 1) := compCode;
3680 IF(CZ_ORAAPPS_INTEGRATE.mRETCODE = 2)THEN RETURN nModelId; END IF;
3681 END IF;
3682
3683 --LA 9595142
3684 -- Explosion has been processed, add to array
3685 IF (disableMultipleCopyImport = 1) THEN
3686 processed_expls_tbl(nTopItemId) := nSecondaryId;
3687 ELSE
3688 processed_expls_tbl(nTopItemId) := 1;
3689 END IF;
3690
3691 END IF; -- NOT processed_expls_tbl(nTopItemId).EXISTS
3692
3693 EXCEPTION
3694 WHEN IMP_CHILD_MULTI_COPIES THEN --LA 9595142
3695 RAISE;
3696 WHEN IMP_MODEL_NOT_ENABLED THEN --LA 9595142
3697 RAISE ;
3698 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
3699 RAISE;
3700 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
3701 RAISE;
3702 WHEN OTHERS THEN
3703 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3704 d_str:= d_str || ' Current info:' || inOrgId || ':'|| nTopItemId;
3705 xERROR:=cz_utils.log_report(d_str,1,'ExtractPsNode.RECURSION',11276);
3706 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
3707 END;
3708 END LOOP;
3709 CLOSE c_expl;
3710
3711 RETURN nModelId;
3712
3713 EXCEPTION
3714 WHEN IMP_CHILD_MULTI_COPIES THEN --LA 9595142
3715 RAISE;
3716 WHEN IMP_MODEL_NOT_ENABLED THEN --LA 9595142
3717 RAISE ;
3718 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
3719 RAISE;
3720 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
3721 RAISE;
3722 WHEN OTHERS THEN
3723 IF(c_ref%ISOPEN)THEN CLOSE c_ref; END IF;
3724 IF(c_expl%ISOPEN)THEN CLOSE c_expl; END IF;
3725 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3726 xERROR:=cz_utils.log_report(d_str,1,'ExtractPsNode',11276,inRunId);
3727 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
3728 END;
3729 ------------------------------------------------------------------------------------------
3730 PROCEDURE ImportSingleBill(nOrg_ID IN NUMBER,nTop_ID IN NUMBER,
3731 COPY_CHILD_MODELS IN VARCHAR2,
3732 REFRESH_MODEL_ID IN NUMBER,
3733 COPY_ROOT_MODEL IN VARCHAR2,
3734 sExpl_type IN VARCHAR2,dRev_date IN DATE,
3735 x_run_id OUT NOCOPY NUMBER) IS
3736
3737 TYPE tUIDetailedTypeTbl IS TABLE OF CZ_UI_TYPEDPSN_V.detailed_type_id%TYPE INDEX BY VARCHAR2(15);-- kdande; Bug 6885757; 12-Mar-2008
3738
3739 genRun_ID PLS_INTEGER;
3740 nCommit_size PLS_INTEGER DEFAULT 1;
3741 nMax_err PLS_INTEGER DEFAULT 10000;
3742 sTableName CZ_XFR_TABLES.DST_TABLE%TYPE;
3743 xERROR BOOLEAN:=FALSE;
3744 nRunExploder PLS_INTEGER;
3745 cFound CHAR(1);
3746 dDateFormat VARCHAR2(40);
3747 sVersion CZ_DB_SETTINGS.VALUE%TYPE;
3748 server_id cz_servers.server_local_id%TYPE;
3749 nModelId NUMBER;
3750 topModelId NUMBER;
3751 genStatisticsBom PLS_INTEGER;
3752 genStatisticsCz PLS_INTEGER;
3753 st_time number;
3754 end_time number;
3755 extract_st number;
3756 extract_end number;
3757 d_str varchar2(255);
3758 l_failed NUMBER :=0;
3759 l_msg_tmp varchar2(2000);
3760 l_lang VARCHAR2(4);
3761 l_compare_detailed_types BOOLEAN;
3762 l_current_date DATE;
3763 l_detailed_types_tbl tUIDetailedTypeTbl;
3764 l_update_model_timestamp BOOLEAN := FALSE;
3765
3766
3767 CURSOR C_EXTRACTION_ORDER IS
3768 SELECT DST_TABLE FROM CZ_XFR_TABLES
3769 WHERE XFR_GROUP='EXTRACT' AND DISABLED='0'
3770 AND DST_TABLE IN ('CZ_ITEM_TYPES','CZ_PROPERTIES','CZ_ITEM_TYPE_PROPERTIES',
3771 'CZ_ITEM_MASTERS','CZ_ITEM_PROPERTY_VALUES','CZ_LOCALIZED_TEXTS','CZ_DEVL_PROJECTS')
3772 ORDER BY ORDER_SEQ;
3773 CURSOR C_IMPORT_ORDER IS
3774 SELECT DST_TABLE FROM CZ_XFR_TABLES
3775 WHERE XFR_GROUP='IMPORT' AND DISABLED='0'
3776 AND DST_TABLE IN ('CZ_ITEM_TYPES','CZ_PROPERTIES','CZ_ITEM_TYPE_PROPERTIES',
3777 'CZ_ITEM_MASTERS','CZ_ITEM_PROPERTY_VALUES','CZ_LOCALIZED_TEXTS','CZ_DEVL_PROJECTS',
3778 'CZ_PS_NODES')
3779 ORDER BY ORDER_SEQ;
3780 CURSOR C_BILL_OF_MATERIAL IS
3781 SELECT 'F' FROM CZ_EXV_BILL_OF_MATERIALS
3782 WHERE ORGANIZATION_ID=nOrg_ID AND ASSEMBLY_ITEM_ID=nTop_ID;
3783 NOUPDATE_SOURCE_BILL_DELETED NUMBER;
3784
3785 CZ_LANGUAGES_DO_NOT_MATCH EXCEPTION;
3786
3787 l_msg_data VARCHAR2(2000);
3788 l_msg_count NUMBER := 0;
3789 l_return_status VARCHAR2(1);
3790 l_locked_models cz_security_pvt.number_type_tbl;
3791 l_checkout_user cz_security_pvt.varchar_type_tbl;
3792 HAS_NO_PRIV EXCEPTION;
3793 PRIV_CHECK_ERR EXCEPTION;
3794 FAILED_TO_LOCK_MODEL EXCEPTION;
3795
3796 v_settings_id VARCHAR2(40);
3797 v_section_name VARCHAR2(30);
3798 v_enabled VARCHAR2(1) := '1';
3799
3800 -- LA 13733007 : OC with No children
3801 TYPE tNoChildOCName IS TABLE OF cz_ps_nodes.name%TYPE INDEX BY BINARY_INTEGER;
3802 TYPE tNoChildOCModelName IS TABLE OF cz_rp_entries.name%TYPE INDEX BY BINARY_INTEGER;
3803 lNoChildOCName tNoChildOCName ;
3804 lNoChildOCModelName tNoChildOCModelName;
3805
3806 BEGIN
3807
3808 IF (REFRESH_MODEL_ID IS NULL OR REFRESH_MODEL_ID=-1) THEN
3809 BEGIN
3810 SELECT NVL(d.config_engine_type,'L') INTO g_CONFIG_ENGINE_TYPE
3811 FROM cz_xfr_project_bills p, cz_devl_projects d
3812 WHERE p.organization_id = nOrg_ID
3813 AND p.top_item_id = nTop_ID
3814 AND p.explosion_type = sExpl_type
3815 AND d.deleted_flag = '0'
3816 AND d.devl_project_id = d.persistent_project_id
3817 AND p.model_ps_node_id = d.devl_project_id;
3818 EXCEPTION
3819 WHEN NO_DATA_FOUND THEN
3820 g_CONFIG_ENGINE_TYPE := FND_PROFILE.VALUE('CZ_CONFIG_ENGINE_NEW_MODELS');
3821 END;
3822 ELSE
3823 SELECT NVL(config_engine_type,'L') INTO g_CONFIG_ENGINE_TYPE FROM CZ_DEVL_PROJECTS
3824 WHERE devl_project_id=REFRESH_MODEL_ID;
3825 END IF;
3826
3827 CZ_ADMIN.SPX_SYNC_IMPORTSESSIONS;
3828 DBMS_APPLICATION_INFO.SET_MODULE('CZIMPORT','');
3829
3830 BEGIN
3831 SELECT server_local_id INTO server_id
3832 FROM cz_servers
3833 WHERE import_enabled = v_enabled;
3834 EXCEPTION
3835 WHEN TOO_MANY_ROWS THEN
3836 xERROR:=cz_utils.log_report(CZ_UTILS.GET_TEXT('CZ_IMP_TOO_MANY_SERVERS'),1,'CZ_IMP_ALL.ImportSingleBill',11276);
3837 RAISE CZ_ADMIN.IMP_TOO_MANY_SERVERS;
3838 WHEN NO_DATA_FOUND THEN
3839 xERROR:=cz_utils.log_report(CZ_UTILS.GET_TEXT('CZ_IMP_NO_IMP_SERVERS'),1,'CZ_IMP_ALL.ImportSingleBill',11276);
3840 RAISE CZ_ADMIN.IMP_NO_IMP_SERVER;
3841 END;
3842
3843 IF(server_id > 0)THEN
3844 IF(CZ_UTILS.check_installed_lang(server_id) <> 0)THEN
3845 xERROR:=cz_utils.log_report(CZ_UTILS.GET_TEXT('CZ_LANGUAGES_DO_NOT_MATCH'),1,'CZ_IMP_ALL.ImportSingleBill',11276);
3846 RAISE CZ_ADMIN.CZ_LANGUAGES_DO_NOT_MATCH;
3847 END IF;
3848 END IF;
3849
3850 --Determines whether unchanged child models should be refreshed during this run, default - YES.
3851
3852 v_settings_id := 'IMPORTCHILDMODEL';
3853 v_section_name := 'IMPORT';
3854
3855 BEGIN
3856 SELECT decode(upper(VALUE),'TRUE','1','FALSE','0','T','1','F','0','1','1','0','0','YES','1','NO','0','Y','1','N','0','1')
3857 INTO importUnchangedChildModels FROM CZ_DB_SETTINGS
3858 WHERE upper(SETTING_ID)=v_settings_id AND SECTION_NAME=v_section_name;
3859 EXCEPTION
3860 WHEN OTHERS THEN
3861 importUnchangedChildModels := 1;
3862 END;
3863
3864 --Generate statistics on BOM_EXPLOSIONS after root explosion, default - NO.
3865
3866 v_settings_id := 'GENSTATISTICSBOM';
3867 v_section_name := 'IMPORT';
3868
3869 BEGIN
3870 SELECT decode(upper(VALUE),'TRUE','1','FALSE','0','T','1','F','0','1','1','0','0','YES','1','NO','0','Y','1','N','0','0')
3871 INTO genStatisticsBom FROM CZ_DB_SETTINGS
3872 WHERE upper(SETTING_ID)=v_settings_id AND SECTION_NAME=v_section_name;
3873 EXCEPTION
3874 WHEN OTHERS THEN
3875 genStatisticsBom := 0;
3876 END;
3877
3878 --Generate statistics on IMPORT tables after extraction, default - NO.
3879
3880 v_settings_id := 'GENSTATISTICSCZ';
3881 v_section_name := 'IMPORT';
3882
3883 BEGIN
3884 SELECT decode(upper(VALUE),'TRUE','1','FALSE','0','T','1','F','0','1','1','0','0','YES','1','NO','0','Y','1','N','0','0')
3885 INTO genStatisticsCz FROM CZ_DB_SETTINGS
3886 WHERE upper(SETTING_ID)=v_settings_id AND SECTION_NAME=v_section_name;
3887 EXCEPTION
3888 WHEN OTHERS THEN
3889 genStatisticsCz := 0;
3890 END;
3891
3892 --Relaxing decimal quantity restriction for Pella. If this setting is set to 'YES', BOM indivisible_flag will be imported not
3893 --only for Standard Items (the default behavior), but also for Option Classes.
3894 --This setting must be present and set to 'YES' to change the default behavior.
3895 --Bug #4717871.
3896
3897 v_settings_id := 'ALLOWDECIMALOPTIONCLASS';
3898 v_section_name := 'IMPORT';
3899
3900 BEGIN
3901 SELECT decode(upper(VALUE),'TRUE',1,'FALSE',0,'T',1,'F',0,'1',1,'0',0,'YES',1,'NO',0,'Y',1,'N',0,0)
3902 INTO allowDecimalOptionClass FROM CZ_DB_SETTINGS
3903 WHERE upper(SETTING_ID)=v_settings_id AND SECTION_NAME=v_section_name;
3904 EXCEPTION
3905 WHEN OTHERS THEN
3906 allowDecimalOptionClass := 0;
3907 END;
3908
3909 BEGIN
3910 SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO genRun_ID FROM DUAL;
3911 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY,COMPLETED)
3912 VALUES (genRun_ID,SYSDATE,SYSDATE,'0');
3913 x_run_id := genRun_ID;
3914 EXCEPTION
3915 WHEN OTHERS THEN
3916 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3917 xERROR:=cz_utils.log_report(d_str,1,'SINGLEBILL:CZ_XFR_RUN_INFOS',11276,genRun_ID);
3918 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
3919 END;
3920 COMMIT;
3921
3922 ----1) Run the BOM EXPLODER for the specified bills which are not deleted
3923
3924 v_settings_id := 'RUN_BILL_EXPLODER';
3925 v_section_name := 'ORAAPPS_INTEGRATE';
3926
3927 BEGIN
3928 SELECT DECODE(UPPER(value), 'YES', 1, 'Y', 1, '1', 1, 'TRUE', 1, 'T', 1,
3929 'NO', 0, 'N', 0, '0', 0, 'FALSE', 0, 'F', 0,
3930 0)
3931 INTO nRunExploder FROM CZ_DB_SETTINGS
3932 WHERE UPPER(SECTION_NAME) = v_section_name
3933 AND UPPER(SETTING_ID) = v_settings_id;
3934 EXCEPTION
3935 WHEN OTHERS THEN
3936 nRunExploder:=0;
3937 END;
3938
3939 v_settings_id := 'BOM_REVISION';
3940 v_section_name := 'ORAAPPS_INTEGRATE';
3941
3942 BEGIN
3943 SELECT VALUE INTO sVersion FROM CZ_DB_SETTINGS
3944 WHERE UPPER(SECTION_NAME) = v_section_name
3945 AND UPPER(SETTING_ID) = v_settings_id;
3946 EXCEPTION
3947 WHEN OTHERS THEN
3948 sVersion:='11.5.0';
3949 END;
3950
3951 SELECT DECODE(SUBSTR(sVersion,1,INSTR(sVersion,'.',1,2)-1),
3952 '11.5','YYYY/MM/DD HH24:MI','DD-MON-RR HH24:MI')
3953 INTO dDateFormat FROM DUAL;
3954
3955 BEGIN
3956
3957 v_settings_id := 'COMMITSIZE';
3958 v_section_name := 'IMPORT';
3959
3960 SELECT VALUE INTO nCommit_size FROM CZ_DB_SETTINGS
3961 WHERE UPPER(SETTING_ID) = v_settings_id
3962 AND UPPER(SECTION_NAME) = v_section_name;
3963
3964 v_settings_id := 'MAXIMUMERRORS';
3965 v_section_name := 'IMPORT';
3966
3967 SELECT VALUE INTO nMax_err FROM CZ_DB_SETTINGS
3968 WHERE UPPER(SETTING_ID) = v_settings_id
3969 AND UPPER(SECTION_NAME) = v_section_name;
3970 EXCEPTION
3971 WHEN OTHERS THEN
3972 xERROR:=cz_utils.log_report(SQLERRM,1,'SINGLEBILL:COMMITSIZE',11276,x_run_id);
3973 END;
3974
3975 --LA 9595142 : Read settings that disables refresh of multiple copies of the model
3976 v_settings_id := 'DISABLEMULTIPLECOPYIMPORT';
3977 v_section_name := 'IMPORT';
3978
3979 BEGIN
3980 SELECT decode(upper(VALUE),'TRUE',1,'FALSE',0,'T',1,'F',0,'1',1,'0',0,'YES',1,'NO',0,'Y',1,'N',0,0)
3981 INTO disableMultipleCopyImport FROM CZ_DB_SETTINGS
3982 WHERE upper(SETTING_ID)=v_settings_id AND SECTION_NAME=v_section_name;
3983 EXCEPTION
3984 WHEN OTHERS THEN
3985 disableMultipleCopyImport := 0;
3986 END;
3987
3988
3989 NOUPDATE_SOURCE_BILL_DELETED:=CZ_UTILS.GET_NOUPDATE_FLAG('CZ_XFR_PROJECT_BILLS','SOURCE_BILL_DELETED', 'IMPORT');
3990 BEGIN
3991 OPEN C_BILL_OF_MATERIAL;
3992 FETCH C_BILL_OF_MATERIAL INTO cFound;
3993 IF(C_BILL_OF_MATERIAL%FOUND)THEN
3994
3995 if (CZ_IMP_ALL.get_time) then
3996 extract_st := dbms_utility.get_time();
3997 end if;
3998
3999 --LA 9595142 Clear import_enabled_tbl before and after extraction
4000 import_enabled_tbl.DELETE;
4001 -- Clear processed_expl_tbl before and after ps node extraction.
4002 processed_expls_tbl.DELETE;
4003 nModelId := ExtractPsNode(genRun_ID, nOrg_ID, nTop_ID, sExpl_type, server_id, nRunExploder,
4004 dRev_date, dDateFormat, REFRESH_MODEL_ID, COPY_ROOT_MODEL, COPY_CHILD_MODELS,
4005 genStatisticsBom);
4006 processed_expls_tbl.DELETE;
4007 --LA 9595142
4008 import_enabled_tbl.DELETE;
4009
4010
4011 ----------deep-lock the model if it exists
4012 FOR j IN (SELECT devl_project_id FROM cz_devl_projects
4013 WHERE devl_project_id = nModelId AND deleted_flag='0')
4014 LOOP
4015 cz_security_pvt.lock_model(
4016 p_api_version => 1.0,
4017 p_model_id => nModelId,
4018 p_lock_child_models => FND_API.G_TRUE,
4019 p_commit_flag => FND_API.G_TRUE,
4020 x_locked_entities => l_locked_models,
4021 x_return_status => l_return_status,
4022 x_msg_count => l_msg_count,
4023 x_msg_data => l_msg_data);
4024 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4025 FOR i IN 1..l_msg_count LOOP
4026 l_msg_data := fnd_msg_pub.GET(i,fnd_api.g_false);
4027 xERROR:=cz_utils.log_report(l_msg_data,1,'CZ_IMP_SINGLE.IMPORTSINGLEBILL',20001,genRun_ID);
4028 END LOOP;
4029 RAISE FAILED_TO_LOCK_MODEL;
4030 END IF;
4031 END LOOP;
4032
4033 if (CZ_IMP_ALL.get_time) then
4034 extract_end := dbms_utility.get_time();
4035 d_str := genRun_Id || ' EXTRACT PS - TOTAL (' || nTop_Id || ') :' || (extract_end-extract_st)/100.00;
4036 xERROR:=cz_utils.log_report(d_str,1,'EXTRACT_PS_NODE',11299,genRun_ID);
4037 end if;
4038
4039 IF(CZ_ORAAPPS_INTEGRATE.mRETCODE = 2)THEN RETURN; END IF;
4040
4041 UPDATE CZ_XFR_PROJECT_BILLS SET
4042 SOURCE_BILL_DELETED=DECODE(NOUPDATE_SOURCE_BILL_DELETED,0,'0',SOURCE_BILL_DELETED)
4043 WHERE ORGANIZATION_ID=nOrg_ID AND TOP_ITEM_ID=nTop_ID AND EXPLOSION_TYPE=sExpl_type;
4044 ELSE
4045 UPDATE CZ_XFR_PROJECT_BILLS SET
4046 SOURCE_BILL_DELETED=DECODE(NOUPDATE_SOURCE_BILL_DELETED,0,'1',SOURCE_BILL_DELETED)
4047 WHERE ORGANIZATION_ID=nOrg_ID AND TOP_ITEM_ID=nTop_ID AND EXPLOSION_TYPE=sExpl_type;
4048 END IF;
4049 CLOSE C_BILL_OF_MATERIAL;
4050 EXCEPTION
4051 WHEN PRIV_CHECK_ERR THEN
4052 xERROR:=cz_utils.log_report(l_msg_data,1,'SINGLEBILL.GENERAL',20001,genRun_ID);
4053 DBMS_APPLICATION_INFO.SET_MODULE('','');
4054 RAISE;
4055 WHEN HAS_NO_PRIV THEN
4056 xERROR:=cz_utils.log_report(l_msg_data,1,'SINGLEBILL.GENERAL',20001,genRun_ID);
4057 DBMS_APPLICATION_INFO.SET_MODULE('','');
4058 RAISE;
4059 WHEN FAILED_TO_LOCK_MODEL THEN
4060 xERROR:=cz_utils.log_report(l_msg_data,1,'SINGLEBILL.GENERAL',20001,genRun_ID);
4061 COMMIT;
4062 DBMS_APPLICATION_INFO.SET_MODULE('','');
4063 RAISE;
4064 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
4065 IF C_BILL_OF_MATERIAL%ISOPEN THEN CLOSE C_BILL_OF_MATERIAL; END IF;
4066 processed_expls_tbl.DELETE;
4067 --LA 9595142
4068 import_enabled_tbl.DELETE;
4069 RAISE;
4070 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
4071 RAISE;
4072 --LA 9595142
4073 WHEN IMP_CHILD_MULTI_COPIES THEN
4074 IF C_BILL_OF_MATERIAL%ISOPEN THEN CLOSE C_BILL_OF_MATERIAL; END IF;
4075 processed_expls_tbl.DELETE;
4076 import_enabled_tbl.DELETE;
4077 DBMS_APPLICATION_INFO.SET_MODULE('','');
4078 RAISE ;
4079 WHEN IMP_MODEL_NOT_ENABLED THEN
4080 IF C_BILL_OF_MATERIAL%ISOPEN THEN CLOSE C_BILL_OF_MATERIAL; END IF;
4081 processed_expls_tbl.DELETE;
4082 import_enabled_tbl.DELETE;
4083 DBMS_APPLICATION_INFO.SET_MODULE('','');
4084 RAISE ;
4085 WHEN OTHERS THEN
4086 IF C_BILL_OF_MATERIAL%ISOPEN THEN CLOSE C_BILL_OF_MATERIAL; END IF;
4087 processed_expls_tbl.DELETE;
4088 --LA 9595142
4089 import_enabled_tbl.DELETE;
4090 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
4091 xERROR:=cz_utils.log_report(d_str,1,'EXTRACT_PS_NODE',11299,genRun_ID);
4092 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
4093 END;
4094
4095 -------Create the table of all relevant item catalog group ids which will be used for extraction of property related data.
4096
4097 itemCatalogGroupId.DELETE;
4098 repCatalogGroupId.DELETE;
4099 inventoryItemId.DELETE;
4100 repItemId.DELETE;
4101 hashCatalog.DELETE;
4102
4103 if (CZ_IMP_ALL.get_time) then
4104 st_time := dbms_utility.get_time();
4105 end if;
4106
4107 l_lang := userenv('LANG');
4108
4109 BEGIN
4110 SELECT item_catalog_group_id, inventory_item_id
4111 BULK COLLECT INTO repCatalogGroupId, repItemId
4112 FROM cz_exv_item_master
4113 WHERE organization_id = nOrg_ID
4114 AND top_item_id = nTop_ID
4115 AND explosion_type = sExpl_type
4116 AND item_catalog_group_id IS NOT NULL
4117 AND language = l_lang;
4118
4119 --itemCatalogGroupId should be unique.
4120
4121 FOR i IN 1..repCatalogGroupId.COUNT LOOP
4122
4123 IF(NOT hashCatalog.EXISTS(repCatalogGroupId(i)))THEN
4124
4125 itemCatalogGroupId(itemCatalogGroupId.COUNT + 1) := repCatalogGroupId(i);
4126 hashCatalog(repCatalogGroupId(i)) := 1;
4127 END IF;
4128 END LOOP;
4129
4130 hashCatalog.DELETE;
4131
4132 --inventoryItemId should be unique.
4133
4134 FOR i IN 1..repItemId.COUNT LOOP
4135
4136 IF(NOT hashCatalog.EXISTS(repItemId(i)))THEN
4137
4138 inventoryItemId(inventoryItemId.COUNT + 1) := repItemId(i);
4139 hashCatalog(repItemId(i)) := 1;
4140 END IF;
4141 END LOOP;
4142
4143 EXCEPTION
4144 WHEN NO_DATA_FOUND THEN
4145 xERROR:=cz_utils.log_report(SQLERRM,1,'CZ_IMP_SINGLE.IMPORTSINGLEBILL.CATALOG',11276,genRun_ID);
4146 END;
4147
4148 if (CZ_IMP_ALL.get_time) then
4149 end_time := dbms_utility.get_time();
4150 d_str := genRun_Id ||' Collect Catalog group id (' || nTop_Id || ') :' || (end_time-st_time)/100.00;
4151 xERROR:=cz_utils.log_report(d_str,1,'EXTRACTION',11299,genRun_ID);
4152 end if;
4153
4154 ----2) Populate the table for UI refresh using the extracted list of models
4155
4156 FOR model IN (SELECT DISTINCT component_id FROM cz_model_ref_expls WHERE model_id = nModelId
4157 AND deleted_flag = '0' AND (ps_node_type IN (263, 264) OR parent_expl_node_id IS NULL))LOOP
4158
4159 FOR j IN (SELECT devl_project_id FROM cz_devl_projects
4160 WHERE devl_project_id = model.component_id AND deleted_flag='0')
4161 LOOP
4162 l_compare_detailed_types := TRUE;
4163 l_current_date := SYSDATE;
4164 FOR i IN (SELECT ps_node_id, detailed_type_id
4165 FROM CZ_UI_TYPEDPSN_V
4166 WHERE devl_project_Id = model.component_id
4167 AND ps_node_type IN (CZ_TYPES.PS_NODE_TYPE_BOM_MODEL, CZ_TYPES.PS_NODE_TYPE_BOM_OPTION_CLASS)
4168 AND deleted_flag = '0')
4169 LOOP
4170 l_detailed_types_tbl(i.ps_node_id) := i.detailed_type_id;
4171 END LOOP;
4172 END LOOP;
4173 END LOOP;
4174
4175 ----3) Call all the extract procedures in the order specified by ORDER_SEQ field of CZ_XFR_TABLES with XFR_GROUP='EXTRACT'
4176
4177 OPEN C_EXTRACTION_ORDER;
4178 LOOP
4179 BEGIN
4180 FETCH C_EXTRACTION_ORDER INTO sTableName;
4181 EXIT WHEN C_EXTRACTION_ORDER%NOTFOUND;
4182
4183 extract_table(genRun_ID, sTableName, nOrg_ID, nTop_ID, sExpl_type, nModelId);
4184
4185 EXCEPTION
4186 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
4187 RAISE;
4188 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
4189 RAISE;
4190 WHEN NO_DATA_FOUND THEN
4191 xERROR:=cz_utils.log_report(sTableName||':'||SQLERRM,1,'CZ_IMP_SINGLE.IMPORTSINGLEBILL.extract_table',11276,genRun_ID);
4192 WHEN OTHERS THEN
4193 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
4194 xERROR:=cz_utils.log_report(d_str,1,'SINGLEBILL:EXTRACTION',11276,genRun_ID);
4195 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
4196 END;
4197 END LOOP;
4198 CLOSE C_EXTRACTION_ORDER;
4199
4200 ----3.5 Gather statistics on IMPORT so that best possible plans are used during key resolution and transfer.
4201
4202 IF(genStatisticsCz = 1)THEN
4203 FOR i IN (SELECT src_table FROM cz_xfr_tables
4204 WHERE xfr_group='IMPORT'
4205 AND disabled='0') LOOP
4206 fnd_stats.GATHER_TABLE_STATS('CZ',i.src_table);
4207 END LOOP;
4208 END IF;
4209
4210 ----4) Call all the import procedures in the order specified by ORDER_SEQ field of CZ_XFR_TABLES with XFR_GROUP='IMPORT'
4211
4212 OPEN C_IMPORT_ORDER;
4213 LOOP
4214 BEGIN
4215 FETCH C_IMPORT_ORDER INTO sTableName;
4216 EXIT WHEN C_IMPORT_ORDER%NOTFOUND;
4217 populate_table(genRun_ID, sTableName, nCommit_size, nMax_err, 'IMPORT', CZ_IMP_SINGLE.RP_ROOT_FOLDER, l_failed);
4218 EXCEPTION
4219 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
4220 RAISE;
4221 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
4222 RAISE;
4223 WHEN NO_DATA_FOUND THEN
4224 xERROR:=cz_utils.log_report(sTableName||':'||SQLERRM,1,'CZ_IMP_SINGLE.IMPORTSINGLEBILL.populate_table',11276,genRun_ID);
4225 WHEN OTHERS THEN
4226 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
4227 xERROR:=cz_utils.log_report(d_str,1,'CZ_IMP_SINGLE.GO.IMPORT',11276,genRun_ID);
4228 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
4229 END;
4230 END LOOP;
4231 CLOSE C_IMPORT_ORDER;
4232
4233 --Populate has_trackable_children flag in cz_model_ref_expls. It is sufficient to call the procedure
4234 --only once for the very root project. For every run_id there is exactly one record in the table for
4235 --the root project (this is not necessarily true for the child models).
4236 BEGIN
4237 SELECT model_ps_node_id INTO nModelId
4238 FROM cz_xfr_project_bills
4239 WHERE organization_id = nOrg_ID
4240 AND top_item_id = nTop_ID
4241 AND explosion_type = sExpl_type
4242 AND last_import_run_id = genRun_ID;
4243 EXCEPTION
4244 WHEN OTHERS THEN
4245 xERROR:=cz_utils.log_report(nTop_ID||':'||SQLERRM,1,'CZ_IMP_SINGLE.IMPORTSINGLEBILL.XFRPROJECTS',11276,genRun_ID);
4246 END;
4247
4248 BEGIN
4249 cz_refs.set_Trackable_Children_Flag(nModelId);
4250 EXCEPTION
4251 WHEN OTHERS THEN
4252 xERROR:=cz_utils.log_report(nModelId||':'||SQLERRM,1,'CZ_IMP_SINGLE.IMPORTSINGLEBILL.TRACKABLE',11276,genRun_ID);
4253 END;
4254
4255 -- LA 13733007: No children OC
4256 -- Report option classes with no children or all ineffective children
4257 -- This code is also used in logic gen for LCE and FCE.
4258 -- Currently repeated this code in all the packages but later need to move to some common place
4259 -- This query is used to report all the OCs under Root model and it's reference chain
4260
4261 BEGIN
4262 SELECT MODEL.NAME, PARENT.NAME
4263 BULK COLLECT INTO lNoChildOCModelName, lNoChildOCName
4264 FROM cz_ps_nodes PARENT, cz_rp_entries MODEL
4265 WHERE PARENT.deleted_flag = '0'
4266 AND MODEL.deleted_flag = '0'
4267 AND PARENT.devl_project_id = MODEL.object_id
4268 AND MODEL.object_type = 'PRJ'
4269 AND parent.devl_project_id IN
4270 (SELECT DISTINCT component_id FROM cz_model_ref_expls
4271 WHERE deleted_flag = '0' AND model_id = nModelId
4272 AND (ps_node_type = CZ_TYPES.PS_NODE_TYPE_REFERENCE
4273 OR ps_node_type = CZ_TYPES.PS_NODE_TYPE_BOM_MODEL))
4274 AND PARENT.ps_node_type = CZ_TYPES.PS_NODE_TYPE_BOM_OPTION_CLASS
4275 AND PARENT.effective_until > SYSDATE
4276 AND NOT EXISTS
4277 (SELECT 1 FROM cz_ps_nodes CHILD
4278 WHERE CHILD.deleted_flag = '0'
4279 AND CHILD.devl_project_id = PARENT.devl_project_id
4280 AND CHILD.parent_id = PARENT.ps_node_id
4281 AND CHILD.effective_until > SYSDATE);
4282
4283 IF (lNoChildOCModelName.COUNT > 0) THEN
4284 FOR i IN 1..lNoChildOCModelName.COUNT
4285 LOOP
4286 xERROR :=CZ_UTILS.LOG_REPORT('Model ''' || lNoChildOCModelName(i) ||
4287 ''' contains an option class that has no effective children, option class ''' || lNoChildOCName(i) || '''.',
4288 1, 'CZ_IMP_SINGLE.IMPORTSINGLEBILL.NOCHILD_OC', 11276,genRun_ID);
4289 END LOOP;
4290 cz_imp_all.setReturnCode(cz_imp_all.CONCURRENT_WARNING,'Model contains option class that does not have effective children.');
4291 END IF;
4292 EXCEPTION
4293 WHEN OTHERS THEN
4294 xERROR:=cz_utils.log_report(nModelId||':'||SQLERRM,1,'CZ_IMP_SINGLE.IMPORTSINGLEBILL.NOCHILD_OC',11276,genRun_ID);
4295 END;
4296
4297 ----5) Finally update the (LAST_ACTIVITY,COMPLETED) fields of CZ_XFR_RUN_INFOS
4298 BEGIN
4299 UPDATE CZ_XFR_RUN_INFOS SET
4300 LAST_ACTIVITY=SYSDATE,
4301 COMPLETED='1'
4302 WHERE RUN_ID=genRun_ID;
4303 EXCEPTION
4304 WHEN OTHERS THEN
4305 xERROR:=cz_utils.log_report(genRun_ID||':'||SQLERRM,1,'CZ_IMP_SINGLE.IMPORTSINGLEBILL.RUNINFOS',11276,genRun_ID);
4306 END;
4307
4308 --DBMS_OUTPUT.PUT_LINE(CZ_UTILS.GET_TEXT('CZ_IMP_IMPORT_COMPLETED','RUNID',TO_CHAR(genRun_ID)));
4309
4310 IF l_compare_detailed_types THEN
4311
4312 FOR model IN (SELECT devl_project_id FROM cz_imp_devl_project WHERE run_id = genRun_ID
4313 AND rec_status = 'OK')LOOP
4314 FOR i IN (SELECT ps_node_id, detailed_type_id
4315 FROM CZ_UI_TYPEDPSN_V
4316 WHERE devl_project_Id = model.devl_project_id
4317 AND ps_node_type IN (CZ_TYPES.PS_NODE_TYPE_BOM_MODEL, CZ_TYPES.PS_NODE_TYPE_BOM_OPTION_CLASS)
4318 AND creation_date <= l_current_date
4319 AND deleted_flag = '0')
4320 LOOP
4321 IF l_detailed_types_tbl.EXISTS( i.ps_node_id ) THEN
4322 IF l_detailed_types_tbl(i.ps_node_id) <> i.detailed_type_id THEN
4323
4324 UPDATE CZ_PS_NODES set UI_TIMESTAMP_CHANGETYPE = SYSDATE
4325 WHERE devl_project_id = model.devl_project_id
4326 AND ps_node_id = i.ps_node_id;
4327
4328 l_update_model_timestamp := TRUE;
4329
4330 END IF;
4331 END IF;
4332 END LOOP;
4333
4334 IF l_update_model_timestamp THEN
4335 UPDATE cz_devl_projects set ui_timestamp_struct_update = SYSDATE
4336 WHERE devl_project_id = model.devl_project_id;
4337 END IF;
4338 END LOOP;
4339 END IF;
4340
4341 COMMIT;
4342 DBMS_APPLICATION_INFO.SET_MODULE('','');
4343
4344 ----6) Unlock model
4345 IF ( l_locked_models.COUNT > 0 ) THEN
4346 BEGIN
4347 cz_security_pvt.unlock_model(
4348 p_api_version => 1.0,
4349 p_commit_flag => FND_API.G_TRUE,
4350 p_models_to_unlock => l_locked_models,
4351 x_return_status => l_return_status,
4352 x_msg_count => l_msg_count,
4353 x_msg_data => l_msg_data);
4354 EXCEPTION
4355 WHEN OTHERS THEN
4356 xERROR:=cz_utils.log_report(nModelId||':'||SQLERRM,1,'CZ_IMP_SINGLE.IMPORTSINGLEBILL.UNLOCK',11276,genRun_ID);
4357 END;
4358 END IF;
4359 EXCEPTION
4360 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
4361 RAISE;
4362 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
4363 xERROR:=cz_utils.log_report(CZ_UTILS.GET_TEXT('CZ_IMP_ACTIVE_SESSION_EXISTS'),1,'SINGLEBILL.GENERAL',11276,genRun_ID);
4364 DBMS_APPLICATION_INFO.SET_MODULE('','');
4365 RAISE;
4366 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
4367 RAISE;
4368 WHEN NO_DATA_FOUND THEN
4369 xERROR:=cz_utils.log_report(SQLERRM,1,'SINGLEBILL.GENERAL',11276,genRun_ID);
4370 WHEN OTHERS THEN
4371 IF ( l_locked_models.COUNT > 0 ) THEN
4372 BEGIN
4373 cz_security_pvt.unlock_model(
4374 p_api_version => 1.0,
4375 p_commit_flag => FND_API.G_TRUE,
4376 p_models_to_unlock => l_locked_models,
4377 x_return_status => l_return_status,
4378 x_msg_count => l_msg_count,
4379 x_msg_data => l_msg_data);
4380 EXCEPTION
4381 WHEN OTHERS THEN
4382 xERROR:=cz_utils.log_report(nModelId||':'||SQLERRM,1,'SINGLEBILL.GENERAL.unlock',11276,genRun_ID);
4383 END;
4384 END IF;
4385 d_str:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
4386 xERROR:=cz_utils.log_report(d_str,1,'SINGLEBILL.GENERAL',11276,genRun_ID);
4387 DBMS_APPLICATION_INFO.SET_MODULE('','');
4388 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
4389 END ImportSingleBill;
4390 ------------------------------------------------------------------------------------------
4391
4392 END CZ_IMP_SINGLE;
[Home] [Help]