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