[Home] [Help]
PACKAGE BODY: APPS.OKC_XPRT_IMPORT_VARIABLES_PVT
Source
1 PACKAGE BODY OKC_XPRT_IMPORT_VARIABLES_PVT AS
2 /* $Header: OKCVXVARB.pls 120.7.12020000.2 2012/07/16 16:21:44 harchand ship $ */
3
4 ---------------------------------------------------------------------------
5 -- GLOBAL VARIABLES
6 ---------------------------------------------------------------------------
7 G_RUN_ID NUMBER;
8
9 ------------------------------------------------------------------------------
10 -- GLOBAL CONSTANTS
11 ------------------------------------------------------------------------------
12 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_XPRT_IMPORT_VARIABLES_PVT';
13 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
14
15 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
16 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||g_pkg_name||'.';
17 G_APPLICATION_ID CONSTANT NUMBER :=510; -- OKC Application
18
19 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
20 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
21
22 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
23 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
24 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
25
26 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
27 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
28 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
29 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
30
31
32 ------------------------------------------------------------------------------
33 -- Orig_sys_ref
34 ------------------------------------------------------------------------------
35 G_VARIABLE_MODEL_OSR CONSTANT VARCHAR2(255) := 'OKC:VARIABLEMODEL:-99:';
36 G_VARIABLE_MODEL_TOPNODE_OSR CONSTANT VARCHAR2(255) := 'OKC:VARIABLEMODELTOPNODE:-99:' ;
37 G_VARIABLE_MODEL_FEATURE_OSR CONSTANT VARCHAR2(255) := 'OKC:VARIABLEMODELFEATURE:-99:' ;
38 G_VARIABLE_MODEL_OPTION_OSR CONSTANT VARCHAR2(255) := 'OKC:VARIABLEMODELOPTION:-99:' ;
39
40 G_VAR_MODEL_TEXT_FEATURE_OSR CONSTANT VARCHAR2(255) := 'OKC:VARIABLEMODELTEXTFEATURE:-99:' ;
41 G_VAR_MODEL_DEVI_FEATURE_OSR CONSTANT VARCHAR2(255) := 'OKC:VARIABLEMODELDEVFEATURE:-99:' ;
42
43
44 /*
45 ---------------------------------------------------
46 -- PRIVATE Procedures and Functions
47 ---------------------------------------------------
48 */
49 /*====================================================================+
50 Procedure Name : create_variable_model
51 Description : This is a private API that creates the Variable Model
52 Variable Model is created for Intent with Org as -99
53 Parameters:
54 p_intent - Intent of the variable model
55 p_model_id - If model exists then refresh the model
56
57 +====================================================================*/
58
59 PROCEDURE create_variable_model
60 (
61 p_intent IN VARCHAR2,
62 p_model_id IN NUMBER,
63 x_return_status OUT NOCOPY VARCHAR2,
64 x_msg_data OUT NOCOPY VARCHAR2,
65 x_msg_count OUT NOCOPY NUMBER
66 ) IS
67
68 l_cz_imp_devl_project_rec CZ_IMP_DEVL_PROJECT%ROWTYPE;
69 l_api_name CONSTANT VARCHAR2(30) := 'create_variable_model';
70 l_model_desc CZ_IMP_DEVL_PROJECT.DESC_TEXT%TYPE;
71
72 BEGIN
73
74 -- start debug log
75 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
76 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
77 G_MODULE||l_api_name,
78 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
79 END IF;
80
81 x_return_status := G_RET_STS_SUCCESS;
82
83 -- Get the Variable Model Name
84 FND_MESSAGE.set_name('OKC','OKC_EXPRT_VAR_MODEL_TITLE');
85 FND_MESSAGE.set_token('INTENT_MEANING',okc_util.decode_lookup('OKC_ARTICLE_INTENT',p_intent));
86 l_model_desc := FND_MESSAGE.get;
87
88 -- populate the l_cz_imp_devl_project_rec
89 l_cz_imp_devl_project_rec.DEVL_PROJECT_ID:= NULL;
90 l_cz_imp_devl_project_rec.INTL_TEXT_ID:= NULL;
91 l_cz_imp_devl_project_rec.ORGANIZATION_ID:= -99;
92 l_cz_imp_devl_project_rec.NAME:= G_VARIABLE_MODEL_OSR||p_intent ;
93 l_cz_imp_devl_project_rec.GSL_FILENAME:= NULL;
94 l_cz_imp_devl_project_rec.TOP_ITEM_ID:= 1;
95 l_cz_imp_devl_project_rec.VERSION:= NULL;
96 l_cz_imp_devl_project_rec.EXPLOSION_TYPE:= NULL;
97 l_cz_imp_devl_project_rec.DESC_TEXT:= l_model_desc;
98 l_cz_imp_devl_project_rec.ORIG_SYS_REF:= G_VARIABLE_MODEL_OSR||p_intent ;
99 l_cz_imp_devl_project_rec.CREATION_DATE:= SYSDATE;
100 l_cz_imp_devl_project_rec.LAST_UPDATE_DATE:= SYSDATE;
101 l_cz_imp_devl_project_rec.DELETED_FLAG:= '0'; -- '0' Not deleted
102 l_cz_imp_devl_project_rec.EFF_FROM:= NULL;
103 l_cz_imp_devl_project_rec.EFF_TO:= NULL;
104 l_cz_imp_devl_project_rec.CREATED_BY:= FND_GLOBAL.USER_ID;
105 l_cz_imp_devl_project_rec.LAST_UPDATED_BY:= FND_GLOBAL.USER_ID;
106 l_cz_imp_devl_project_rec.SECURITY_MASK:= NULL;
107 l_cz_imp_devl_project_rec.EFF_MASK:= NULL;
108 l_cz_imp_devl_project_rec.CHECKOUT_USER:= NULL;
109 l_cz_imp_devl_project_rec.RUN_ID:= G_RUN_ID;
110 l_cz_imp_devl_project_rec.REC_STATUS:= NULL;
111 l_cz_imp_devl_project_rec.DISPOSITION:= NULL;
112 l_cz_imp_devl_project_rec.FSK_INTLTEXT_1_1:= NULL;
113 l_cz_imp_devl_project_rec.MODEL_ID:= p_model_id;
114 l_cz_imp_devl_project_rec.PLAN_LEVEL:= 0;
115 l_cz_imp_devl_project_rec.PERSISTENT_PROJECT_ID:= NULL;
116 l_cz_imp_devl_project_rec.MODEL_TYPE:= 'C'; -- non BOM Model
117 l_cz_imp_devl_project_rec.INVENTORY_ITEM_ID:= NULL;
118 l_cz_imp_devl_project_rec.PRODUCT_KEY:= NULL;
119 l_cz_imp_devl_project_rec.LAST_UPDATE_LOGIN:= FND_GLOBAL.LOGIN_ID;
120 l_cz_imp_devl_project_rec.BOM_CAPTION_RULE_ID:= NULL;
121 l_cz_imp_devl_project_rec.NONBOM_CAPTION_RULE_ID:= OKC_XPRT_CZ_INT_PVT.G_CAPTION_RULE_DESC; -- display desc in runtime UIs
122 l_cz_imp_devl_project_rec.SEEDED_FLAG:= '1'; -- '0' unseeded , '1' seeded
123
124 --
125
126 -- insert the Variable Model Record into cz_devl_project
127 INSERT INTO cz_imp_devl_project
128 (
129 DEVL_PROJECT_ID,
130 INTL_TEXT_ID,
131 ORGANIZATION_ID,
132 NAME,
133 GSL_FILENAME,
134 TOP_ITEM_ID,
135 VERSION,
136 EXPLOSION_TYPE,
137 DESC_TEXT,
138 ORIG_SYS_REF,
139 CREATION_DATE,
140 LAST_UPDATE_DATE,
141 DELETED_FLAG,
142 EFF_FROM,
143 EFF_TO,
144 CREATED_BY,
145 LAST_UPDATED_BY,
146 SECURITY_MASK,
147 EFF_MASK,
148 CHECKOUT_USER,
149 RUN_ID,
150 REC_STATUS,
151 DISPOSITION,
152 FSK_INTLTEXT_1_1,
153 MODEL_ID,
154 PLAN_LEVEL,
155 PERSISTENT_PROJECT_ID,
156 MODEL_TYPE,
157 INVENTORY_ITEM_ID,
158 PRODUCT_KEY,
159 LAST_UPDATE_LOGIN,
160 BOM_CAPTION_RULE_ID,
161 NONBOM_CAPTION_RULE_ID,
162 SEEDED_FLAG
163 )
164 VALUES
165 (
166 l_cz_imp_devl_project_rec.DEVL_PROJECT_ID,
167 l_cz_imp_devl_project_rec.INTL_TEXT_ID,
168 l_cz_imp_devl_project_rec.ORGANIZATION_ID,
169 l_cz_imp_devl_project_rec.NAME,
170 l_cz_imp_devl_project_rec.GSL_FILENAME,
171 l_cz_imp_devl_project_rec.TOP_ITEM_ID,
172 l_cz_imp_devl_project_rec.VERSION,
173 l_cz_imp_devl_project_rec.EXPLOSION_TYPE,
174 l_cz_imp_devl_project_rec.DESC_TEXT,
175 l_cz_imp_devl_project_rec.ORIG_SYS_REF,
176 l_cz_imp_devl_project_rec.CREATION_DATE,
177 l_cz_imp_devl_project_rec.LAST_UPDATE_DATE,
178 l_cz_imp_devl_project_rec.DELETED_FLAG,
179 l_cz_imp_devl_project_rec.EFF_FROM,
180 l_cz_imp_devl_project_rec.EFF_TO,
181 l_cz_imp_devl_project_rec.CREATED_BY,
182 l_cz_imp_devl_project_rec.LAST_UPDATED_BY,
183 l_cz_imp_devl_project_rec.SECURITY_MASK,
184 l_cz_imp_devl_project_rec.EFF_MASK,
185 l_cz_imp_devl_project_rec.CHECKOUT_USER,
186 l_cz_imp_devl_project_rec.RUN_ID,
187 l_cz_imp_devl_project_rec.REC_STATUS,
188 l_cz_imp_devl_project_rec.DISPOSITION,
189 l_cz_imp_devl_project_rec.FSK_INTLTEXT_1_1,
190 l_cz_imp_devl_project_rec.MODEL_ID,
191 l_cz_imp_devl_project_rec.PLAN_LEVEL,
192 l_cz_imp_devl_project_rec.PERSISTENT_PROJECT_ID,
193 l_cz_imp_devl_project_rec.MODEL_TYPE,
194 l_cz_imp_devl_project_rec.INVENTORY_ITEM_ID,
195 l_cz_imp_devl_project_rec.PRODUCT_KEY,
196 l_cz_imp_devl_project_rec.LAST_UPDATE_LOGIN,
197 l_cz_imp_devl_project_rec.BOM_CAPTION_RULE_ID,
198 l_cz_imp_devl_project_rec.NONBOM_CAPTION_RULE_ID,
199 l_cz_imp_devl_project_rec.SEEDED_FLAG
200 );
201
202
203 -- Standard call to get message count and if count is 1, get message info.
204 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
205
206 -- end debug log
207 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
208 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
209 G_MODULE||l_api_name,
210 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
211 END IF;
212
213
214 EXCEPTION
215 WHEN OTHERS THEN
216 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
217 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
218 G_MODULE||l_api_name,
219 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
220 END IF;
221
222 x_return_status := G_RET_STS_UNEXP_ERROR ;
223
224 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
225 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
226 END IF;
227
228 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
229
230
231 END create_variable_model;
232
233 /*====================================================================+
234 Procedure Name : create_variable_component
235 Description : This is a private API that creates the Variable Model
236 dummy Component
237 Parameters:
238 p_intent - Intent of the variable model
239
240 +====================================================================*/
241
242 PROCEDURE create_variable_component
243 (
244 p_intent IN VARCHAR2,
245 x_return_status OUT NOCOPY VARCHAR2,
246 x_msg_data OUT NOCOPY VARCHAR2,
247 x_msg_count OUT NOCOPY NUMBER
248 ) IS
249
250 CURSOR csr_installed_languages IS
251 SELECT L.LANGUAGE_CODE
252 FROM FND_LANGUAGES L
253 WHERE L.INSTALLED_FLAG IN ('I', 'B');
254
255 l_language FND_LANGUAGES.LANGUAGE_CODE%TYPE;
256
257 l_cz_imp_ps_nodes_rec CZ_IMP_PS_NODES%ROWTYPE;
258 l_api_name CONSTANT VARCHAR2(30) := 'create_variable_component';
259 l_model_component_name CZ_IMP_LOCALIZED_TEXTS.LOCALIZED_STR%TYPE;
260
261 BEGIN
262
263 -- start debug log
264 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
265 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
266 G_MODULE||l_api_name,
267 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
268 END IF;
269
270 x_return_status := G_RET_STS_SUCCESS;
271
272 -- Get the Variable Model Component Name
273 FND_MESSAGE.set_name('OKC','OKC_EXPRT_VAR_MODEL_TNOD_TITLE');
274 FND_MESSAGE.set_token('INTENT_MEANING',okc_util.decode_lookup('OKC_ARTICLE_INTENT',p_intent));
275 l_model_component_name := FND_MESSAGE.get;
276
277 -- Put the Name in the description Column of cz_ps_nodes
278
279 OPEN csr_installed_languages;
280 LOOP
281 FETCH csr_installed_languages INTO l_language;
282 EXIT WHEN csr_installed_languages%NOTFOUND;
283
284 -- Insert into cz_imp_localized_text
285
286 INSERT INTO CZ_IMP_LOCALIZED_TEXTS
287 (
288 LAST_UPDATE_LOGIN,
289 LOCALE_ID,
290 LOCALIZED_STR,
291 INTL_TEXT_ID,
292 CREATION_DATE,
293 LAST_UPDATE_DATE,
294 DELETED_FLAG,
295 EFF_FROM,
296 EFF_TO,
297 CREATED_BY,
298 LAST_UPDATED_BY,
299 SECURITY_MASK,
300 EFF_MASK,
301 CHECKOUT_USER,
302 LANGUAGE,
303 ORIG_SYS_REF,
304 SOURCE_LANG,
305 RUN_ID,
306 REC_STATUS,
307 DISPOSITION,
308 MODEL_ID,
309 FSK_DEVLPROJECT_1_1,
310 MESSAGE,
311 SEEDED_FLAG
312 )
313 VALUES
314 (
315 FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN
316 NULL, -- LOCALE_ID
317 l_model_component_name, --LOCALIZED_STR
318 NULL, -- INTL_TEXT_ID
319 SYSDATE, -- CREATION_DATE
320 SYSDATE, -- LAST_UPDATE_DATE
321 '0', -- DELETED_FLAG
322 NULL, -- EFF_FROM
323 NULL, -- EFF_TO
324 FND_GLOBAL.USER_ID, -- CREATED_BY
325 FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
326 NULL, -- SECURITY_MASK
327 NULL, -- EFF_MASK
328 NULL, -- CHECKOUT_USER
329 l_language, --LANGUAGE
330 G_VARIABLE_MODEL_TOPNODE_OSR||p_intent, -- ORIG_SYS_REF
331 USERENV('LANG'), --SOURCE_LANG
332 G_RUN_ID, -- RUN_ID
333 NULL, -- REC_STATUS
334 NULL, -- DISPOSITION
335 NULL, -- MODEL_ID
336 G_VARIABLE_MODEL_OSR||p_intent , -- FSK_DEVLPROJECT_1_1
337 NULL, -- MESSAGE
338 NULL -- SEEDED_FLAG
339 );
340
341 END LOOP; -- for all installed languages
342 CLOSE csr_installed_languages;
343
344
345 -- Populate the cz_imp_ps_nodes record
346
347 l_cz_imp_ps_nodes_rec.PS_NODE_ID:= NULL;
348 l_cz_imp_ps_nodes_rec.DEVL_PROJECT_ID:= NULL;
349 l_cz_imp_ps_nodes_rec.FROM_POPULATOR_ID:= NULL;
350 l_cz_imp_ps_nodes_rec.PROPERTY_BACKPTR:= NULL;
351 l_cz_imp_ps_nodes_rec.ITEM_TYPE_BACKPTR:= NULL;
352 l_cz_imp_ps_nodes_rec.INTL_TEXT_ID:= NULL;
353 l_cz_imp_ps_nodes_rec.SUB_CONS_ID:= NULL;
354 l_cz_imp_ps_nodes_rec.ORGANIZATION_ID:= -99;
355 l_cz_imp_ps_nodes_rec.ITEM_ID:= NULL;
356 l_cz_imp_ps_nodes_rec.EXPLOSION_TYPE:= NULL;
357 l_cz_imp_ps_nodes_rec.NAME:= G_VARIABLE_MODEL_TOPNODE_OSR||p_intent;
358 l_cz_imp_ps_nodes_rec.ORIG_SYS_REF:= G_VARIABLE_MODEL_TOPNODE_OSR||p_intent;
359 l_cz_imp_ps_nodes_rec.RESOURCE_FLAG:= NULL;
360 l_cz_imp_ps_nodes_rec.TOP_ITEM_ID:= 1; -- same value as in cz_imp_devl_projects
361 l_cz_imp_ps_nodes_rec.INITIAL_VALUE:= NULL;
362 l_cz_imp_ps_nodes_rec.PARENT_ID:= NULL;
363 l_cz_imp_ps_nodes_rec.MINIMUM:= 1;
364 l_cz_imp_ps_nodes_rec.MAXIMUM:= 1;
365 l_cz_imp_ps_nodes_rec.PS_NODE_TYPE:= 259; -- Component
366 l_cz_imp_ps_nodes_rec.FEATURE_TYPE:= NULL;
367 l_cz_imp_ps_nodes_rec.PRODUCT_FLAG:= 0; -- 0 for Model and null for others
368 l_cz_imp_ps_nodes_rec.REFERENCE_ID:= NULL;
369 l_cz_imp_ps_nodes_rec.MULTI_CONFIG_FLAG:= NULL ; -- unused
370 l_cz_imp_ps_nodes_rec.ORDER_SEQ_FLAG:= NULL ; -- unused
371 l_cz_imp_ps_nodes_rec.SYSTEM_NODE_FLAG:= NULL ; -- unused
372 l_cz_imp_ps_nodes_rec.TREE_SEQ:= 1;
373 l_cz_imp_ps_nodes_rec.COUNTED_OPTIONS_FLAG:= '0';
374 l_cz_imp_ps_nodes_rec.UI_OMIT:= '0'; -- 0 for Root Node
375 l_cz_imp_ps_nodes_rec.UI_SECTION:= 0;
376 l_cz_imp_ps_nodes_rec.BOM_TREATMENT:= NULL;
377 l_cz_imp_ps_nodes_rec.RUN_ID:= G_RUN_ID;
378 l_cz_imp_ps_nodes_rec.REC_STATUS:= NULL;
379 l_cz_imp_ps_nodes_rec.DISPOSITION:= NULL;
380 l_cz_imp_ps_nodes_rec.DELETED_FLAG := 0;
381 l_cz_imp_ps_nodes_rec.EFF_FROM:= NULL;
382 l_cz_imp_ps_nodes_rec.EFF_TO:= NULL;
383 l_cz_imp_ps_nodes_rec.EFF_MASK:= NULL;
384 l_cz_imp_ps_nodes_rec.USER_STR01:= NULL;
385 l_cz_imp_ps_nodes_rec.USER_STR02:= NULL;
386 l_cz_imp_ps_nodes_rec.USER_STR03:= NULL;
387 l_cz_imp_ps_nodes_rec.USER_STR04:= NULL;
388 l_cz_imp_ps_nodes_rec.USER_NUM01:= NULL;
389 l_cz_imp_ps_nodes_rec.USER_NUM02:= NULL;
390 l_cz_imp_ps_nodes_rec.USER_NUM03:= NULL;
391 l_cz_imp_ps_nodes_rec.USER_NUM04:= NULL;
392 l_cz_imp_ps_nodes_rec.CHECKOUT_USER:= NULL;
393 l_cz_imp_ps_nodes_rec.CREATION_DATE:= SYSDATE;
394 l_cz_imp_ps_nodes_rec.LAST_UPDATE_DATE:= SYSDATE;
395 l_cz_imp_ps_nodes_rec.CREATED_BY:= FND_GLOBAL.USER_ID;
396 l_cz_imp_ps_nodes_rec.LAST_UPDATED_BY:= FND_GLOBAL.USER_ID;
397 l_cz_imp_ps_nodes_rec.SECURITY_MASK:= NULL;
398 l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_1:= G_VARIABLE_MODEL_TOPNODE_OSR||p_intent;
399 l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_EXT:= NULL;
400 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_1:= NULL;
401 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_EXT:= NULL;
402 l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_1:= NULL;
403 l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_EXT:= NULL;
404 l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_1:= NULL;
405 l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_EXT:= NULL;
406 l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_1:= G_VARIABLE_MODEL_OSR ||p_intent;
407 l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_EXT:= NULL;
408 l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_ID:= NULL;
409 l_cz_imp_ps_nodes_rec.COMPONENT_CODE:= NULL;
410 l_cz_imp_ps_nodes_rec.PLAN_LEVEL:= 0; --Plan Level for Component:0
411 l_cz_imp_ps_nodes_rec.BOM_ITEM_TYPE:= NULL;
412 l_cz_imp_ps_nodes_rec.SO_ITEM_TYPE_CODE:= NULL;
413 l_cz_imp_ps_nodes_rec.MINIMUM_SELECTED:= NULL;
414 l_cz_imp_ps_nodes_rec.MAXIMUM_SELECTED:= NULL;
415 l_cz_imp_ps_nodes_rec.BOM_REQUIRED:= NULL;
416 l_cz_imp_ps_nodes_rec.MUTUALLY_EXCLUSIVE_OPTIONS:= NULL;
417 l_cz_imp_ps_nodes_rec.OPTIONAL:= NULL;
418 l_cz_imp_ps_nodes_rec.FSK_EXPLNODE_1_1:= NULL;
419 l_cz_imp_ps_nodes_rec.FSK_PSNODE_6_1:= NULL;
420 l_cz_imp_ps_nodes_rec.EFFECTIVE_FROM:= OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_BEGIN;
421 l_cz_imp_ps_nodes_rec.EFFECTIVE_UNTIL:= OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_END;
422 l_cz_imp_ps_nodes_rec.EFFECTIVE_USAGE_MASK:= NULL;
423 l_cz_imp_ps_nodes_rec.EFFECTIVITY_SET_ID:= NULL;
424 l_cz_imp_ps_nodes_rec.FSK_EFFSET_7_1:= NULL;
425 l_cz_imp_ps_nodes_rec.DECIMAL_QTY_FLAG:= 0; -- 0 for all nodes
426 l_cz_imp_ps_nodes_rec.QUOTEABLE_FLAG:= NULL;
427 l_cz_imp_ps_nodes_rec.PRIMARY_UOM_CODE:= NULL;
428 l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_PATH:= NULL; -- Must be NULL
429 l_cz_imp_ps_nodes_rec.BOM_SORT_ORDER:= NULL;
430 l_cz_imp_ps_nodes_rec.IB_TRACKABLE:= NULL;
431 l_cz_imp_ps_nodes_rec.LAST_UPDATE_LOGIN:= FND_GLOBAL.LOGIN_ID;
432 l_cz_imp_ps_nodes_rec.INITIAL_NUM_VALUE:= NULL;
433 l_cz_imp_ps_nodes_rec.SRC_APPLICATION_ID:= G_APPLICATION_ID;
434 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_2:= NULL;
435 l_cz_imp_ps_nodes_rec.INSTANTIABLE_FLAG:= NULL;
436 l_cz_imp_ps_nodes_rec.DISPLAY_IN_SUMMARY_FLAG:= NULL;
437
438 -- insert top node for Variable Model into cz_imp_ps_nodes
439
440 INSERT INTO cz_imp_ps_nodes
441 (
442 PS_NODE_ID,
443 DEVL_PROJECT_ID,
444 FROM_POPULATOR_ID,
445 PROPERTY_BACKPTR,
446 ITEM_TYPE_BACKPTR,
447 INTL_TEXT_ID,
448 SUB_CONS_ID,
449 ORGANIZATION_ID,
450 ITEM_ID,
451 EXPLOSION_TYPE,
452 NAME,
453 ORIG_SYS_REF,
454 RESOURCE_FLAG,
455 TOP_ITEM_ID,
456 INITIAL_VALUE,
457 PARENT_ID,
458 MINIMUM,
459 MAXIMUM,
460 PS_NODE_TYPE,
461 FEATURE_TYPE,
462 PRODUCT_FLAG,
463 REFERENCE_ID,
464 MULTI_CONFIG_FLAG,
465 ORDER_SEQ_FLAG,
466 SYSTEM_NODE_FLAG,
467 TREE_SEQ,
468 COUNTED_OPTIONS_FLAG,
469 UI_OMIT,
470 UI_SECTION,
471 BOM_TREATMENT,
472 RUN_ID,
473 REC_STATUS,
474 DISPOSITION,
475 DELETED_FLAG ,
476 EFF_FROM,
477 EFF_TO,
478 EFF_MASK,
479 USER_STR01,
480 USER_STR02,
481 USER_STR03,
482 USER_STR04,
483 USER_NUM01,
484 USER_NUM02,
485 USER_NUM03,
486 USER_NUM04,
487 CHECKOUT_USER,
488 CREATION_DATE,
489 LAST_UPDATE_DATE,
490 CREATED_BY,
491 LAST_UPDATED_BY,
492 SECURITY_MASK,
493 FSK_INTLTEXT_1_1,
494 FSK_INTLTEXT_1_EXT,
495 FSK_ITEMMASTER_2_1,
496 FSK_ITEMMASTER_2_EXT,
497 FSK_PSNODE_3_1,
498 FSK_PSNODE_3_EXT,
499 FSK_PSNODE_4_1,
500 FSK_PSNODE_4_EXT,
501 FSK_DEVLPROJECT_5_1,
502 FSK_DEVLPROJECT_5_EXT,
503 COMPONENT_SEQUENCE_ID,
504 COMPONENT_CODE,
505 PLAN_LEVEL,
506 BOM_ITEM_TYPE,
507 SO_ITEM_TYPE_CODE,
508 MINIMUM_SELECTED,
509 MAXIMUM_SELECTED,
510 BOM_REQUIRED,
511 MUTUALLY_EXCLUSIVE_OPTIONS,
512 OPTIONAL,
513 FSK_EXPLNODE_1_1,
514 FSK_PSNODE_6_1,
515 EFFECTIVE_FROM,
516 EFFECTIVE_UNTIL,
517 EFFECTIVE_USAGE_MASK,
518 EFFECTIVITY_SET_ID,
519 FSK_EFFSET_7_1,
520 DECIMAL_QTY_FLAG,
521 QUOTEABLE_FLAG,
522 PRIMARY_UOM_CODE,
523 COMPONENT_SEQUENCE_PATH,
524 BOM_SORT_ORDER,
525 IB_TRACKABLE,
526 LAST_UPDATE_LOGIN,
527 INITIAL_NUM_VALUE,
528 SRC_APPLICATION_ID,
529 FSK_ITEMMASTER_2_2,
530 INSTANTIABLE_FLAG,
531 DISPLAY_IN_SUMMARY_FLAG
532 )
533 VALUES
534 (
535 l_cz_imp_ps_nodes_rec.PS_NODE_ID,
536 l_cz_imp_ps_nodes_rec.DEVL_PROJECT_ID,
537 l_cz_imp_ps_nodes_rec.FROM_POPULATOR_ID,
538 l_cz_imp_ps_nodes_rec.PROPERTY_BACKPTR,
539 l_cz_imp_ps_nodes_rec.ITEM_TYPE_BACKPTR,
540 l_cz_imp_ps_nodes_rec.INTL_TEXT_ID,
541 l_cz_imp_ps_nodes_rec.SUB_CONS_ID,
542 l_cz_imp_ps_nodes_rec.ORGANIZATION_ID,
543 l_cz_imp_ps_nodes_rec.ITEM_ID,
544 l_cz_imp_ps_nodes_rec.EXPLOSION_TYPE,
545 l_cz_imp_ps_nodes_rec.NAME,
546 l_cz_imp_ps_nodes_rec.ORIG_SYS_REF,
547 l_cz_imp_ps_nodes_rec.RESOURCE_FLAG,
548 l_cz_imp_ps_nodes_rec.TOP_ITEM_ID,
549 l_cz_imp_ps_nodes_rec.INITIAL_VALUE,
550 l_cz_imp_ps_nodes_rec.PARENT_ID,
551 l_cz_imp_ps_nodes_rec.MINIMUM,
552 l_cz_imp_ps_nodes_rec.MAXIMUM,
553 l_cz_imp_ps_nodes_rec.PS_NODE_TYPE,
554 l_cz_imp_ps_nodes_rec.FEATURE_TYPE,
555 l_cz_imp_ps_nodes_rec.PRODUCT_FLAG,
556 l_cz_imp_ps_nodes_rec.REFERENCE_ID,
557 l_cz_imp_ps_nodes_rec.MULTI_CONFIG_FLAG,
558 l_cz_imp_ps_nodes_rec.ORDER_SEQ_FLAG,
559 l_cz_imp_ps_nodes_rec.SYSTEM_NODE_FLAG,
560 l_cz_imp_ps_nodes_rec.TREE_SEQ,
561 l_cz_imp_ps_nodes_rec.COUNTED_OPTIONS_FLAG,
562 l_cz_imp_ps_nodes_rec.UI_OMIT,
563 l_cz_imp_ps_nodes_rec.UI_SECTION,
564 l_cz_imp_ps_nodes_rec.BOM_TREATMENT,
565 l_cz_imp_ps_nodes_rec.RUN_ID,
566 l_cz_imp_ps_nodes_rec.REC_STATUS,
567 l_cz_imp_ps_nodes_rec.DISPOSITION,
568 l_cz_imp_ps_nodes_rec.DELETED_FLAG ,
569 l_cz_imp_ps_nodes_rec.EFF_FROM,
570 l_cz_imp_ps_nodes_rec.EFF_TO,
571 l_cz_imp_ps_nodes_rec.EFF_MASK,
572 l_cz_imp_ps_nodes_rec.USER_STR01,
573 l_cz_imp_ps_nodes_rec.USER_STR02,
574 l_cz_imp_ps_nodes_rec.USER_STR03,
575 l_cz_imp_ps_nodes_rec.USER_STR04,
576 l_cz_imp_ps_nodes_rec.USER_NUM01,
577 l_cz_imp_ps_nodes_rec.USER_NUM02,
578 l_cz_imp_ps_nodes_rec.USER_NUM03,
579 l_cz_imp_ps_nodes_rec.USER_NUM04,
580 l_cz_imp_ps_nodes_rec.CHECKOUT_USER,
581 l_cz_imp_ps_nodes_rec.CREATION_DATE,
582 l_cz_imp_ps_nodes_rec.LAST_UPDATE_DATE,
583 l_cz_imp_ps_nodes_rec.CREATED_BY,
584 l_cz_imp_ps_nodes_rec.LAST_UPDATED_BY,
585 l_cz_imp_ps_nodes_rec.SECURITY_MASK,
586 l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_1,
587 l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_EXT,
588 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_1,
589 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_EXT,
590 l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_1,
591 l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_EXT,
592 l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_1,
593 l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_EXT,
594 l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_1,
595 l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_EXT,
596 l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_ID,
597 l_cz_imp_ps_nodes_rec.COMPONENT_CODE,
598 l_cz_imp_ps_nodes_rec.PLAN_LEVEL,
599 l_cz_imp_ps_nodes_rec.BOM_ITEM_TYPE,
600 l_cz_imp_ps_nodes_rec.SO_ITEM_TYPE_CODE,
601 l_cz_imp_ps_nodes_rec.MINIMUM_SELECTED,
602 l_cz_imp_ps_nodes_rec.MAXIMUM_SELECTED,
603 l_cz_imp_ps_nodes_rec.BOM_REQUIRED,
604 l_cz_imp_ps_nodes_rec.MUTUALLY_EXCLUSIVE_OPTIONS,
605 l_cz_imp_ps_nodes_rec.OPTIONAL,
606 l_cz_imp_ps_nodes_rec.FSK_EXPLNODE_1_1,
607 l_cz_imp_ps_nodes_rec.FSK_PSNODE_6_1,
608 l_cz_imp_ps_nodes_rec.EFFECTIVE_FROM,
609 l_cz_imp_ps_nodes_rec.EFFECTIVE_UNTIL,
610 l_cz_imp_ps_nodes_rec.EFFECTIVE_USAGE_MASK,
611 l_cz_imp_ps_nodes_rec.EFFECTIVITY_SET_ID,
612 l_cz_imp_ps_nodes_rec.FSK_EFFSET_7_1,
613 l_cz_imp_ps_nodes_rec.DECIMAL_QTY_FLAG,
614 l_cz_imp_ps_nodes_rec.QUOTEABLE_FLAG,
615 l_cz_imp_ps_nodes_rec.PRIMARY_UOM_CODE,
616 l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_PATH,
617 l_cz_imp_ps_nodes_rec.BOM_SORT_ORDER,
618 l_cz_imp_ps_nodes_rec.IB_TRACKABLE,
619 l_cz_imp_ps_nodes_rec.LAST_UPDATE_LOGIN,
620 l_cz_imp_ps_nodes_rec.INITIAL_NUM_VALUE,
621 l_cz_imp_ps_nodes_rec.SRC_APPLICATION_ID,
622 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_2,
623 l_cz_imp_ps_nodes_rec.INSTANTIABLE_FLAG,
624 l_cz_imp_ps_nodes_rec.DISPLAY_IN_SUMMARY_FLAG
625 );
626
627
628 -- Standard call to get message count and if count is 1, get message info.
629 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
630
631 -- end debug log
632 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
633 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
634 G_MODULE||l_api_name,
635 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
636 END IF;
637
638
639 EXCEPTION
640 WHEN OTHERS THEN
641 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
642 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
643 G_MODULE||l_api_name,
644 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
645 END IF;
646
647 x_return_status := G_RET_STS_UNEXP_ERROR ;
648
649 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
650 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
651 END IF;
652
653 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
654
655
656 END create_variable_component;
657
658
659 /*====================================================================+
660 Procedure Name : create_variable_features
661 Description : This is a private API that creates the Variable Model
662 features.
663 All variables and constants are created as features
664 Parameters:
665 p_intent - Intent of the variable model
666
667 +====================================================================*/
668
669 PROCEDURE create_variable_features
670 (
671 p_intent IN VARCHAR2,
672 x_return_status OUT NOCOPY VARCHAR2,
673 x_msg_data OUT NOCOPY VARCHAR2,
674 x_msg_count OUT NOCOPY NUMBER
675 ) IS
676
677
678 CURSOR csr_variables(p_language IN VARCHAR2) IS
679 -- Expert Enabled System Variables
680 -- Item and Item Categories will be seeded as type 'T' and expert enabled
681 SELECT distinct DECODE(SUBSTR(rcon.object_code,1,3),'OKC',rcon.object_code,'USER$' || rcon.object_code) variable_code, -- LHS of Condition
682 t.variable_name variable_name,
683 v.variable_datatype variable_datatype,
684 t.LANGUAGE language,
685 t.source_lang source_lang
686 FROM okc_xprt_rule_hdrs_all rhdr,
687 okc_xprt_rule_conditions rcon,
688 okc_bus_variables_b v,
689 okc_bus_variables_tl t
690 WHERE rhdr.rule_id = rcon.rule_id
691 AND rhdr.intent = p_intent
692 AND rcon.object_type = 'VARIABLE'
693 AND rhdr.status_code NOT IN ('DRAFT','INACTIVE')
694 AND rcon.object_code = v.variable_code
695 AND v.variable_code = t.variable_code
696 AND t.LANGUAGE = USERENV('LANG')
697 --AND t.language = NVL(p_language,t.language)
698 UNION
699 SELECT distinct DECODE(SUBSTR(rcon.object_value_code,1,3),'OKC',rcon.object_value_code,'USER$' || rcon.object_value_code) variable_code, -- RHS of Condition
700 t.variable_name variable_name,
701 v.variable_datatype variable_datatype,
702 t.LANGUAGE language,
703 t.source_lang source_lang
704 FROM okc_xprt_rule_hdrs_all rhdr,
705 okc_xprt_rule_conditions rcon,
706 okc_bus_variables_b v,
707 okc_bus_variables_tl t
708 WHERE rhdr.rule_id = rcon.rule_id
709 AND rhdr.intent = p_intent
710 AND rcon.object_value_type = 'VARIABLE'
711 AND rhdr.status_code NOT IN ('DRAFT','INACTIVE')
712 AND rcon.object_value_code = v.variable_code
713 AND v.variable_code = t.variable_code
714 AND t.LANGUAGE = USERENV('LANG')
715 --AND t.language = NVL(p_language,t.language)
716 UNION
717 SELECT distinct 'CONSTANT$' || to_char(q.question_id) variable_code, -- Query for Constants used
718 ql.question_name variable_name,
719 q.question_datatype variable_datatype,
720 ql.LANGUAGE language,
721 ql.source_lang source_lang
722 FROM okc_xprt_rule_hdrs_all rhdr,
723 okc_xprt_rule_conditions rcon,
724 okc_xprt_questions_b q,
725 okc_xprt_questions_tl ql
726 WHERE rhdr.rule_id = rcon.rule_id
727 AND rhdr.intent = p_intent
728 AND rcon.object_value_type = 'CONSTANT'
729 AND rhdr.status_code NOT IN ('DRAFT','INACTIVE')
730 AND rcon.object_value_code = to_char(q.question_id)
731 AND q.question_id = ql.question_id
732 AND ql.LANGUAGE = USERENV('LANG')
733 --AND ql.language = NVL(p_language,ql.language)
734 UNION
735 SELECT distinct to_char(rh.rule_id) variable_code, -- Deviation Rule
736 rh.rule_name variable_name,
737 'DR' variable_datatype, -- Will use this for decoding Deviation Rule to Option Feature
738 USERENV('LANG') language,
739 USERENV('LANG') source_lang
740 FROM okc_xprt_rule_hdrs_all rh
741 WHERE rh.rule_type = 'TERM_DEVIATION'
742 AND rh.status_code NOT IN ('DRAFT','INACTIVE')
743 AND rh.intent = p_intent
744 UNION
745 SELECT 'LINE_NUMBER' variable_code, -- Dummy Text feature
746 'LINE_NUMBER' variable_name,
747 'LN' variable_datatype, -- Will use this for decoding to Line number to Text Feature
748 USERENV('LANG') language,
749 USERENV('LANG') source_lang
750 FROM dual;
751
752 l_api_name CONSTANT VARCHAR2(30) := 'create_variable_features';
753
754 TYPE VariableCodeList IS TABLE OF cz_imp_ps_nodes.name%TYPE INDEX BY BINARY_INTEGER;
755 TYPE VariableNameList IS TABLE OF okc_bus_variables_tl.VARIABLE_NAME%TYPE INDEX BY BINARY_INTEGER;
756 --TYPE VariableDatatypeList IS TABLE OF okc_bus_variables_b.VARIABLE_DATATYPE%TYPE INDEX BY BINARY_INTEGER;
757 TYPE VariableDatatypeList IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER; -- changed for R12
758 TYPE LanguageList IS TABLE OF okc_bus_variables_tl.language%TYPE INDEX BY BINARY_INTEGER;
759 TYPE SourceLangList IS TABLE OF okc_bus_variables_tl.source_lang%TYPE INDEX BY BINARY_INTEGER;
760 TYPE SeqNoList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
761
762
763 variableCode_tbl VariableCodeList;
764 variableName_tbl VariableNameList;
765 variableDatatype_tbl VariableDatatypeList;
766 language_tbl LanguageList;
767 sourceLang_tbl SourceLangList;
768 SeqNoList_tbl SeqNoList;
769
770 CURSOR csr_installed_languages IS
771 SELECT L.LANGUAGE_CODE
772 FROM FND_LANGUAGES L
773 WHERE L.INSTALLED_FLAG IN ('I', 'B');
774
775 l_language FND_LANGUAGES.LANGUAGE_CODE%TYPE;
776
777 BEGIN
778
779 -- start debug log
780 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
781 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
782 G_MODULE||l_api_name,
783 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
784 END IF;
785
786 x_return_status := G_RET_STS_SUCCESS;
787
788 -- Get the variables to be imported as Features Under the Variable Model
789 -- insert ALL installed language records into cz_imp_localized_texts
790 OPEN csr_variables(p_language => NULL);
791 FETCH csr_variables BULK COLLECT INTO variableCode_tbl,
792 variableName_tbl,
793 variableDatatype_tbl,
794 language_tbl,
795 sourceLang_tbl;
796 CLOSE csr_variables;
797
798 FOR i IN 1..variableCode_tbl.COUNT
799 LOOP
800 SeqNoList_tbl(i) := i;
801 END LOOP;
802
803 IF variableCode_tbl.COUNT > 0 THEN
804
805
806 OPEN csr_installed_languages;
807 LOOP
808 FETCH csr_installed_languages INTO l_language;
809 EXIT WHEN csr_installed_languages%NOTFOUND;
810
811 FORALL i IN variableCode_tbl.FIRST..variableCode_tbl.LAST
812 -- Insert into cz_imp_localized_text
813
814 INSERT INTO CZ_IMP_LOCALIZED_TEXTS
815 (
816 LAST_UPDATE_LOGIN,
817 LOCALE_ID,
818 LOCALIZED_STR,
819 INTL_TEXT_ID,
820 CREATION_DATE,
821 LAST_UPDATE_DATE,
822 DELETED_FLAG,
823 EFF_FROM,
824 EFF_TO,
825 CREATED_BY,
826 LAST_UPDATED_BY,
827 SECURITY_MASK,
828 EFF_MASK,
829 CHECKOUT_USER,
830 LANGUAGE,
831 ORIG_SYS_REF,
832 SOURCE_LANG,
833 RUN_ID,
834 REC_STATUS,
835 DISPOSITION,
836 MODEL_ID,
837 FSK_DEVLPROJECT_1_1,
838 MESSAGE,
839 SEEDED_FLAG
840 )
841 VALUES
842 (
843 FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN
844 NULL, -- LOCALE_ID
845 variableName_tbl(i), --LOCALIZED_STR
846 NULL, -- INTL_TEXT_ID
847 SYSDATE, -- CREATION_DATE
848 SYSDATE, -- LAST_UPDATE_DATE
849 '0', -- DELETED_FLAG
850 NULL, -- EFF_FROM
851 NULL, -- EFF_TO
852 FND_GLOBAL.USER_ID, -- CREATED_BY
853 FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
854 NULL, -- SECURITY_MASK
855 NULL, -- EFF_MASK
856 NULL, -- CHECKOUT_USER
857 l_language,
858 --language_tbl(i), --LANGUAGE
859 DECODE(variableDatatype_tbl(i), 'LN', G_VAR_MODEL_TEXT_FEATURE_OSR||p_intent||':'||variableCode_tbl(i), -- ORIG_SYS_REF for Line Number
860 'DR', G_VAR_MODEL_DEVI_FEATURE_OSR||p_intent||':'||variableCode_tbl(i), -- ORIG_SYS_REF for Deviation Rule
861 G_VARIABLE_MODEL_FEATURE_OSR||p_intent||':'||variableCode_tbl(i)), -- ORIG_SYS_REF
862 --sourceLang_tbl(i), --SOURCE_LANG
863 USERENV('LANG'), --SOURCE_LANG
864 G_RUN_ID, -- RUN_ID
865 NULL, -- REC_STATUS
866 NULL, -- DISPOSITION
867 NULL, -- MODEL_ID
868 G_VARIABLE_MODEL_OSR||p_intent , -- FSK_DEVLPROJECT_1_1
869 NULL, -- MESSAGE
870 NULL -- SEEDED_FLAG
871 );
872 END LOOP; -- for all installed languages
873 CLOSE csr_installed_languages;
874
875
876 -- Insert only the Current language records in cz_imp_ps_nodes
877 OPEN csr_variables(p_language => USERENV('LANG'));
878 FETCH csr_variables BULK COLLECT INTO variableCode_tbl,
879 variableName_tbl,
880 variableDatatype_tbl,
881 language_tbl,
882 sourceLang_tbl;
883 CLOSE csr_variables;
884
885 FORALL i IN variableCode_tbl.FIRST..variableCode_tbl.LAST
886
887 -- Insert into cz_imp_ps_nodes
888
889 INSERT INTO cz_imp_ps_nodes
890 (
891 PS_NODE_ID,
892 DEVL_PROJECT_ID,
893 FROM_POPULATOR_ID,
894 PROPERTY_BACKPTR,
895 ITEM_TYPE_BACKPTR,
896 INTL_TEXT_ID,
897 SUB_CONS_ID,
898 ORGANIZATION_ID,
899 ITEM_ID,
900 EXPLOSION_TYPE,
901 NAME,
902 ORIG_SYS_REF,
903 RESOURCE_FLAG,
904 TOP_ITEM_ID,
905 INITIAL_VALUE,
906 PARENT_ID,
907 MINIMUM,
908 MAXIMUM,
909 PS_NODE_TYPE,
910 FEATURE_TYPE,
911 PRODUCT_FLAG,
912 REFERENCE_ID,
913 MULTI_CONFIG_FLAG,
914 ORDER_SEQ_FLAG,
915 SYSTEM_NODE_FLAG,
916 TREE_SEQ,
917 COUNTED_OPTIONS_FLAG,
918 UI_OMIT,
919 UI_SECTION,
920 BOM_TREATMENT,
921 RUN_ID,
922 REC_STATUS,
923 DISPOSITION,
924 DELETED_FLAG ,
925 EFF_FROM,
926 EFF_TO,
927 EFF_MASK,
928 USER_STR01,
929 USER_STR02,
930 USER_STR03,
931 USER_STR04,
932 USER_NUM01,
933 USER_NUM02,
934 USER_NUM03,
935 USER_NUM04,
936 CHECKOUT_USER,
937 CREATION_DATE,
938 LAST_UPDATE_DATE,
939 CREATED_BY,
940 LAST_UPDATED_BY,
941 SECURITY_MASK,
942 FSK_INTLTEXT_1_1,
943 FSK_INTLTEXT_1_EXT,
944 FSK_ITEMMASTER_2_1,
945 FSK_ITEMMASTER_2_EXT,
946 FSK_PSNODE_3_1,
947 FSK_PSNODE_3_EXT,
948 FSK_PSNODE_4_1,
949 FSK_PSNODE_4_EXT,
950 FSK_DEVLPROJECT_5_1,
951 FSK_DEVLPROJECT_5_EXT,
952 COMPONENT_SEQUENCE_ID,
953 COMPONENT_CODE,
954 PLAN_LEVEL,
955 BOM_ITEM_TYPE,
956 SO_ITEM_TYPE_CODE,
957 MINIMUM_SELECTED,
958 MAXIMUM_SELECTED,
959 BOM_REQUIRED,
960 MUTUALLY_EXCLUSIVE_OPTIONS,
961 OPTIONAL,
962 FSK_EXPLNODE_1_1,
963 FSK_PSNODE_6_1,
964 EFFECTIVE_FROM,
965 EFFECTIVE_UNTIL,
966 EFFECTIVE_USAGE_MASK,
967 EFFECTIVITY_SET_ID,
968 FSK_EFFSET_7_1,
969 DECIMAL_QTY_FLAG,
970 QUOTEABLE_FLAG,
971 PRIMARY_UOM_CODE,
972 COMPONENT_SEQUENCE_PATH,
973 BOM_SORT_ORDER,
974 IB_TRACKABLE,
975 LAST_UPDATE_LOGIN,
976 INITIAL_NUM_VALUE,
977 SRC_APPLICATION_ID,
978 FSK_ITEMMASTER_2_2,
979 INSTANTIABLE_FLAG,
980 DISPLAY_IN_SUMMARY_FLAG
981 )
982 VALUES
983 (
984 NULL, --PS_NODE_ID,
985 NULL, --DEVL_PROJECT_ID,
986 NULL, --FROM_POPULATOR_ID,
987 NULL, --PROPERTY_BACKPTR,
988 NULL, --ITEM_TYPE_BACKPTR,
989 NULL, --INTL_TEXT_ID,
990 NULL, --SUB_CONS_ID,
991 -99, --ORGANIZATION_ID
992 NULL, --ITEM_ID,
993 NULL, --EXPLOSION_TYPE,
994 variableCode_tbl(i), --NAME
995 --G_VARIABLE_MODEL_FEATURE_OSR||p_intent||':'||variableCode_tbl(i), --ORIG_SYS_REF
996 DECODE(variableDatatype_tbl(i), 'LN', G_VAR_MODEL_TEXT_FEATURE_OSR||p_intent||':'||variableCode_tbl(i), -- ORIG_SYS_REF for Line Number
997 'DR', G_VAR_MODEL_DEVI_FEATURE_OSR||p_intent||':'||variableCode_tbl(i), -- ORIG_SYS_REF for Deviation Rule
998 G_VARIABLE_MODEL_FEATURE_OSR||p_intent||':'||variableCode_tbl(i)), -- ORIG_SYS_REF
999 NULL, --RESOURCE_FLAG
1000 1, --TOP_ITEM_ID -- same value as in cz_imp_devl_projects
1001 NULL, --INITIAL_VALUE
1002 NULL, --PARENT_ID
1003 -- 0, --MINIMUM -- Commented for Bug 4090738
1004 DECODE(variableDatatype_tbl(i),'N',NULL,0), --MINIMUM -- Added for Bug 4090738
1005 NULL, --MAXIMUM
1006 261, --PS_NODE_TYPE 261:Feature
1007 DECODE(variableDatatype_tbl(i),'LN',4,'N',2,0), -- FEATURE_TYPE 2:Decimal and 0:option and 4: Text feature
1008 --DECODE(variableDatatype_tbl(i),'N',2,0), --FEATURE_TYPE 2:Decimal and 0:option
1009 NULL, --PRODUCT_FLAG,
1010 NULL, --REFERENCE_ID,
1011 NULL, --MULTI_CONFIG_FLAG,
1012 NULL, --ORDER_SEQ_FLAG,
1013 NULL, --SYSTEM_NODE_FLAG
1014 SeqNoList_tbl(i), --TREE_SEQ
1015 '0', --COUNTED_OPTIONS_FLAG
1016 '1', --UI_OMIT
1017 0, --UI_SECTION
1018 NULL, --BOM_TREATMENT,
1019 G_RUN_ID, --RUN_ID
1020 NULL, --REC_STATUS,
1021 NULL, --DISPOSITION,
1022 '0', --DELETED_FLAG
1023 NULL, --EFF_FROM,
1024 NULL, --EFF_TO,
1025 NULL, --EFF_MASK,
1026 NULL, --USER_STR01,
1027 NULL, --USER_STR02,
1028 NULL, --USER_STR03,
1029 NULL, --USER_STR04,
1030 NULL, --USER_NUM01,
1031 NULL, --USER_NUM02,
1032 NULL, --USER_NUM03,
1033 NULL, --USER_NUM04,
1034 NULL, --CHECKOUT_USER,
1035 SYSDATE, --CREATION_DATE
1036 SYSDATE, --LAST_UPDATE_DATE
1037 FND_GLOBAL.USER_ID, --CREATED_BY
1038 FND_GLOBAL.USER_ID, --LAST_UPDATED_BY
1039 NULL, --SECURITY_MASK,
1040 --G_VARIABLE_MODEL_FEATURE_OSR||p_intent||':'||variableCode_tbl(i), --FSK_INTLTEXT_1_1
1041 DECODE(variableDatatype_tbl(i), 'LN', G_VAR_MODEL_TEXT_FEATURE_OSR||p_intent||':'||variableCode_tbl(i), -- FSK_INTLTEXT_1_1 for Line Number
1042 'DR', G_VAR_MODEL_DEVI_FEATURE_OSR||p_intent||':'||variableCode_tbl(i), -- FSK_INTLTEXT_1_1 for Deviation Rule
1043 G_VARIABLE_MODEL_FEATURE_OSR||p_intent||':'||variableCode_tbl(i)), -- FSK_INTLTEXT_1_1
1044 NULL, --FSK_INTLTEXT_1_EXT,
1045 NULL, --FSK_ITEMMASTER_2_1,
1046 NULL, --FSK_ITEMMASTER_2_EXT,
1047 G_VARIABLE_MODEL_TOPNODE_OSR||p_intent, --FSK_PSNODE_3_1
1048 NULL, --FSK_PSNODE_3_EXT,
1049 NULL, --FSK_PSNODE_4_1,
1050 NULL, --FSK_PSNODE_4_EXT,
1051 G_VARIABLE_MODEL_OSR||p_intent, --FSK_DEVLPROJECT_5_1
1052 NULL, --FSK_DEVLPROJECT_5_EXT,
1053 NULL, --COMPONENT_SEQUENCE_ID,
1054 NULL, --COMPONENT_CODE,
1055 1, --PLAN_LEVEL -- Plan Level for Feature:1
1056 NULL, --BOM_ITEM_TYPE,
1057 NULL, --SO_ITEM_TYPE_CODE,
1058 NULL, --MINIMUM_SELECTED,
1059 NULL, --MAXIMUM_SELECTED,
1060 NULL, --BOM_REQUIRED,
1061 NULL, --MUTUALLY_EXCLUSIVE_OPTIONS,
1062 NULL, --OPTIONAL,
1063 NULL, --FSK_EXPLNODE_1_1,
1064 NULL, --FSK_PSNODE_6_1,
1065 OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_BEGIN, --EFFECTIVE_FROM
1066 OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_END, --EFFECTIVE_UNTIL
1067 NULL, --EFFECTIVE_USAGE_MASK,
1068 NULL, --EFFECTIVITY_SET_ID,
1069 NULL, --FSK_EFFSET_7_1,
1070 '0', --DECIMAL_QTY_FLAG -- 0 for all nodes
1071 NULL, --QUOTEABLE_FLAG
1072 NULL, --PRIMARY_UOM_CODE,
1073 NULL, --COMPONENT_SEQUENCE_PATH, -- Must be NULL
1074 NULL, --BOM_SORT_ORDER,
1075 NULL, --IB_TRACKABLE,
1076 FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN,
1077 NULL, --INITIAL_NUM_VALUE,
1078 G_APPLICATION_ID, --SRC_APPLICATION_ID
1079 NULL, --FSK_ITEMMASTER_2_2,
1080 NULL, --INSTANTIABLE_FLAG,
1081 NULL --DISPLAY_IN_SUMMARY_FLAG
1082 );
1083
1084 END IF; -- if row count > 0
1085
1086
1087
1088
1089
1090 -- Standard call to get message count and if count is 1, get message info.
1091 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1092
1093 -- end debug log
1094 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1095 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1096 G_MODULE||l_api_name,
1097 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1098 END IF;
1099
1100
1101 EXCEPTION
1102 WHEN OTHERS THEN
1103 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1104 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1105 G_MODULE||l_api_name,
1106 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1107 END IF;
1108
1109 x_return_status := G_RET_STS_UNEXP_ERROR ;
1110
1111 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1112 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1113 END IF;
1114
1115 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1116
1117
1118 END create_variable_features;
1119
1120
1121 /*====================================================================+
1122 Procedure Name : create_variable_options
1123 Description : This is a private API that creates the Variable Model
1124 options.
1125 All variable values used in rules are created as options under
1126 the variable feature
1127 Parameters:
1128 p_intent - Intent of the variable model
1129
1130 +====================================================================*/
1131
1132 PROCEDURE create_variable_options
1133 (
1134 p_intent IN VARCHAR2,
1135 x_return_status OUT NOCOPY VARCHAR2,
1136 x_msg_data OUT NOCOPY VARCHAR2,
1137 x_msg_count OUT NOCOPY NUMBER
1138 ) IS
1139
1140
1141 CURSOR csr_installed_languages IS
1142 SELECT L.LANGUAGE_CODE
1143 FROM FND_LANGUAGES L
1144 WHERE L.INSTALLED_FLAG IN ('I', 'B');
1145
1146
1147 CURSOR csr_variable_options IS
1148 -- send only distinct variable code values
1149
1150 SELECT DISTINCT DECODE(SUBSTR(c.object_code,1,3),'OKC',c.object_code,'USER$' || c.object_code) variable_code,
1151 v.object_value_code variable_value,
1152 okc_xprt_util_pvt.get_value_display(v.rule_condition_id,v.object_value_code) variable_value_desc
1153 FROM okc_xprt_rule_cond_vals v,
1154 okc_xprt_rule_conditions c,
1155 okc_xprt_rule_hdrs_all r
1156 WHERE v.rule_condition_id = c.rule_condition_id
1157 AND c.rule_id = r.rule_id
1158 AND c.object_type = 'VARIABLE'
1159 AND c.object_value_type = 'VALUE'
1160 AND r.intent = p_intent
1161 AND r.status_code NOT IN ('DRAFT','INACTIVE'); -- Added inactive status for bug 4758803
1162
1163 l_api_name CONSTANT VARCHAR2(30) := 'create_variable_options';
1164 l_language FND_LANGUAGES.LANGUAGE_CODE%TYPE;
1165
1166 TYPE VariableCodeList IS TABLE OF okc_xprt_rule_conditions.object_code%TYPE INDEX BY BINARY_INTEGER;
1167 TYPE VariableOptionList IS TABLE OF okc_xprt_rule_conditions.object_value_code%TYPE INDEX BY BINARY_INTEGER;
1168 TYPE VariableOptionDesc IS TABLE OF cz_imp_localized_texts.localized_str%TYPE INDEX BY BINARY_INTEGER;
1169 TYPE SeqNoList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1170
1171 variableCode_tbl VariableCodeList;
1172 variableOption_tbl VariableOptionList;
1173 variableOptionDesc_tbl VariableOptionDesc;
1174 SeqNoList_tbl SeqNoList;
1175
1176
1177 BEGIN
1178
1179 -- start debug log
1180 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1181 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1182 G_MODULE||l_api_name,
1183 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1184 END IF;
1185
1186 x_return_status := G_RET_STS_SUCCESS;
1187
1188 -- Get the variable values to be imported as Options Under the Variables
1189 OPEN csr_variable_options;
1190 FETCH csr_variable_options BULK COLLECT INTO variableCode_tbl,
1191 variableOption_tbl,
1192 variableOptionDesc_tbl;
1193 CLOSE csr_variable_options;
1194
1195 FOR i IN 1..variableCode_tbl.COUNT
1196 LOOP
1197 SeqNoList_tbl(i) := i;
1198 END LOOP;
1199
1200 IF variableCode_tbl.COUNT > 0 THEN
1201
1202 -- For all installed languages variable_value_desc put the in the description Column of cz_ps_nodes
1203 OPEN csr_installed_languages;
1204 LOOP
1205 FETCH csr_installed_languages INTO l_language;
1206 EXIT WHEN csr_installed_languages%NOTFOUND;
1207
1208 FORALL i IN variableOptionDesc_tbl.FIRST..variableOptionDesc_tbl.LAST
1209
1210 -- Insert into cz_imp_localized_text
1211
1212 INSERT INTO CZ_IMP_LOCALIZED_TEXTS
1213 (
1214 LAST_UPDATE_LOGIN,
1215 LOCALE_ID,
1216 LOCALIZED_STR,
1217 INTL_TEXT_ID,
1218 CREATION_DATE,
1219 LAST_UPDATE_DATE,
1220 DELETED_FLAG,
1221 EFF_FROM,
1222 EFF_TO,
1223 CREATED_BY,
1224 LAST_UPDATED_BY,
1225 SECURITY_MASK,
1226 EFF_MASK,
1227 CHECKOUT_USER,
1228 LANGUAGE,
1229 ORIG_SYS_REF,
1230 SOURCE_LANG,
1231 RUN_ID,
1232 REC_STATUS,
1233 DISPOSITION,
1234 MODEL_ID,
1235 FSK_DEVLPROJECT_1_1,
1236 MESSAGE,
1237 SEEDED_FLAG
1238 )
1239 VALUES
1240 (
1241 FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN
1242 NULL, -- LOCALE_ID
1243 variableOptionDesc_tbl(i), --LOCALIZED_STR
1244 NULL, -- INTL_TEXT_ID
1245 SYSDATE, -- CREATION_DATE
1246 SYSDATE, -- LAST_UPDATE_DATE
1247 '0', -- DELETED_FLAG
1248 NULL, -- EFF_FROM
1249 NULL, -- EFF_TO
1250 FND_GLOBAL.USER_ID, -- CREATED_BY
1251 FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
1252 NULL, -- SECURITY_MASK
1253 NULL, -- EFF_MASK
1254 NULL, -- CHECKOUT_USER
1255 l_language, --LANGUAGE
1256 G_VARIABLE_MODEL_OPTION_OSR||p_intent||':'||variableCode_tbl(i)||':'||variableOption_tbl(i), --ORIG_SYS_REF
1257 USERENV('LANG'), --SOURCE_LANG
1258 G_RUN_ID, -- RUN_ID
1259 NULL, -- REC_STATUS
1260 NULL, -- DISPOSITION
1261 NULL, -- MODEL_ID
1262 G_VARIABLE_MODEL_OSR||p_intent , -- FSK_DEVLPROJECT_1_1
1263 NULL, -- MESSAGE
1264 NULL -- SEEDED_FLAG
1265 );
1266
1267 END LOOP; -- for all installed languages
1268 CLOSE csr_installed_languages;
1269
1270
1271
1272 END IF; -- variableCode_tbl.COUNT > 0
1273
1274
1275
1276
1277
1278 IF variableCode_tbl.COUNT > 0 THEN
1279
1280 FORALL i IN variableCode_tbl.FIRST..variableCode_tbl.LAST
1281
1282 -- Insert into cz_imp_ps_nodes
1283
1284 INSERT INTO cz_imp_ps_nodes
1285 (
1286 PS_NODE_ID,
1287 DEVL_PROJECT_ID,
1288 FROM_POPULATOR_ID,
1289 PROPERTY_BACKPTR,
1290 ITEM_TYPE_BACKPTR,
1291 INTL_TEXT_ID,
1292 SUB_CONS_ID,
1293 ORGANIZATION_ID,
1294 ITEM_ID,
1295 EXPLOSION_TYPE,
1296 NAME,
1297 ORIG_SYS_REF,
1298 RESOURCE_FLAG,
1299 TOP_ITEM_ID,
1300 INITIAL_VALUE,
1301 PARENT_ID,
1302 MINIMUM,
1303 MAXIMUM,
1304 PS_NODE_TYPE,
1305 FEATURE_TYPE,
1306 PRODUCT_FLAG,
1307 REFERENCE_ID,
1308 MULTI_CONFIG_FLAG,
1309 ORDER_SEQ_FLAG,
1310 SYSTEM_NODE_FLAG,
1311 TREE_SEQ,
1312 COUNTED_OPTIONS_FLAG,
1313 UI_OMIT,
1314 UI_SECTION,
1315 BOM_TREATMENT,
1316 RUN_ID,
1317 REC_STATUS,
1318 DISPOSITION,
1319 DELETED_FLAG ,
1320 EFF_FROM,
1321 EFF_TO,
1322 EFF_MASK,
1323 USER_STR01,
1324 USER_STR02,
1325 USER_STR03,
1326 USER_STR04,
1327 USER_NUM01,
1328 USER_NUM02,
1329 USER_NUM03,
1330 USER_NUM04,
1331 CHECKOUT_USER,
1332 CREATION_DATE,
1333 LAST_UPDATE_DATE,
1334 CREATED_BY,
1335 LAST_UPDATED_BY,
1336 SECURITY_MASK,
1337 FSK_INTLTEXT_1_1,
1338 FSK_INTLTEXT_1_EXT,
1339 FSK_ITEMMASTER_2_1,
1340 FSK_ITEMMASTER_2_EXT,
1341 FSK_PSNODE_3_1,
1342 FSK_PSNODE_3_EXT,
1343 FSK_PSNODE_4_1,
1344 FSK_PSNODE_4_EXT,
1345 FSK_DEVLPROJECT_5_1,
1346 FSK_DEVLPROJECT_5_EXT,
1347 COMPONENT_SEQUENCE_ID,
1348 COMPONENT_CODE,
1349 PLAN_LEVEL,
1350 BOM_ITEM_TYPE,
1351 SO_ITEM_TYPE_CODE,
1352 MINIMUM_SELECTED,
1353 MAXIMUM_SELECTED,
1354 BOM_REQUIRED,
1355 MUTUALLY_EXCLUSIVE_OPTIONS,
1356 OPTIONAL,
1357 FSK_EXPLNODE_1_1,
1358 FSK_PSNODE_6_1,
1359 EFFECTIVE_FROM,
1360 EFFECTIVE_UNTIL,
1361 EFFECTIVE_USAGE_MASK,
1362 EFFECTIVITY_SET_ID,
1363 FSK_EFFSET_7_1,
1364 DECIMAL_QTY_FLAG,
1365 QUOTEABLE_FLAG,
1366 PRIMARY_UOM_CODE,
1367 COMPONENT_SEQUENCE_PATH,
1368 BOM_SORT_ORDER,
1369 IB_TRACKABLE,
1370 LAST_UPDATE_LOGIN,
1371 INITIAL_NUM_VALUE,
1372 SRC_APPLICATION_ID,
1373 FSK_ITEMMASTER_2_2,
1374 INSTANTIABLE_FLAG,
1375 DISPLAY_IN_SUMMARY_FLAG
1376 )
1377 VALUES
1378 (
1379 NULL, --PS_NODE_ID,
1380 NULL, --DEVL_PROJECT_ID,
1381 NULL, --FROM_POPULATOR_ID,
1382 NULL, --PROPERTY_BACKPTR,
1383 NULL, --ITEM_TYPE_BACKPTR,
1384 NULL, --INTL_TEXT_ID,
1385 NULL, --SUB_CONS_ID,
1386 -99, --ORGANIZATION_ID
1387 NULL, --ITEM_ID,
1388 NULL, --EXPLOSION_TYPE,
1389 variableOption_tbl(i), --NAME
1390 G_VARIABLE_MODEL_OPTION_OSR||p_intent||':'||variableCode_tbl(i)||':'||variableOption_tbl(i), --ORIG_SYS_REF
1391 NULL, --RESOURCE_FLAG
1392 1, --TOP_ITEM_ID -- same value as in cz_imp_devl_projects
1393 NULL, --INITIAL_VALUE
1394 NULL, --PARENT_ID
1395 0, --MINIMUM
1396 NULL, --MAXIMUM
1397 262, --PS_NODE_TYPE 262:Option
1398 NULL, --FEATURE_TYPE
1399 NULL, --PRODUCT_FLAG,
1400 NULL, --REFERENCE_ID,
1401 NULL, --MULTI_CONFIG_FLAG,
1402 NULL, --ORDER_SEQ_FLAG,
1403 NULL, --SYSTEM_NODE_FLAG
1404 SeqNoList_tbl(i) , --TREE_SEQ
1405 '0', --COUNTED_OPTIONS_FLAG
1406 '1', --UI_OMIT
1407 NULL, --UI_SECTION
1408 NULL, --BOM_TREATMENT,
1409 G_RUN_ID, --RUN_ID
1410 NULL, --REC_STATUS,
1411 NULL, --DISPOSITION,
1412 '0', --DELETED_FLAG
1413 NULL, --EFF_FROM,
1414 NULL, --EFF_TO,
1415 NULL, --EFF_MASK,
1416 NULL, --USER_STR01,
1417 NULL, --USER_STR02,
1418 NULL, --USER_STR03,
1419 NULL, --USER_STR04,
1420 NULL, --USER_NUM01,
1421 NULL, --USER_NUM02,
1422 NULL, --USER_NUM03,
1423 NULL, --USER_NUM04,
1424 NULL, --CHECKOUT_USER,
1425 SYSDATE, --CREATION_DATE
1426 SYSDATE, --LAST_UPDATE_DATE
1427 FND_GLOBAL.USER_ID, --CREATED_BY
1428 FND_GLOBAL.USER_ID, --LAST_UPDATED_BY
1429 NULL, --SECURITY_MASK,
1430 G_VARIABLE_MODEL_OPTION_OSR||p_intent||':'||variableCode_tbl(i)||':'||variableOption_tbl(i), --FSK_INTLTEXT_1_1
1431 NULL, --FSK_INTLTEXT_1_EXT,
1432 NULL, --FSK_ITEMMASTER_2_1,
1433 NULL, --FSK_ITEMMASTER_2_EXT,
1434 G_VARIABLE_MODEL_FEATURE_OSR||p_intent||':'||variableCode_tbl(i) , --FSK_PSNODE_3_1
1435 NULL, --FSK_PSNODE_3_EXT,
1436 NULL, --FSK_PSNODE_4_1,
1437 NULL, --FSK_PSNODE_4_EXT,
1438 G_VARIABLE_MODEL_OSR||p_intent, --FSK_DEVLPROJECT_5_1
1439 NULL, --FSK_DEVLPROJECT_5_EXT,
1440 NULL, --COMPONENT_SEQUENCE_ID,
1441 NULL, --COMPONENT_CODE,
1442 2, --PLAN_LEVEL --Plan Level for Option:2
1443 NULL, --BOM_ITEM_TYPE,
1444 NULL, --SO_ITEM_TYPE_CODE,
1445 NULL, --MINIMUM_SELECTED,
1446 NULL, --MAXIMUM_SELECTED,
1447 NULL, --BOM_REQUIRED,
1448 NULL, --MUTUALLY_EXCLUSIVE_OPTIONS,
1449 NULL, --OPTIONAL,
1450 NULL, --FSK_EXPLNODE_1_1,
1451 NULL, --FSK_PSNODE_6_1,
1452 OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_BEGIN, --EFFECTIVE_FROM
1453 OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_END, --EFFECTIVE_UNTIL
1454 NULL, --EFFECTIVE_USAGE_MASK,
1455 NULL, --EFFECTIVITY_SET_ID,
1456 NULL, --FSK_EFFSET_7_1,
1457 '0', --DECIMAL_QTY_FLAG -- 0 for all nodes
1458 NULL, --QUOTEABLE_FLAG
1459 NULL, --PRIMARY_UOM_CODE,
1460 NULL, --COMPONENT_SEQUENCE_PATH, -- Must be NULL
1461 NULL, --BOM_SORT_ORDER,
1462 NULL, --IB_TRACKABLE,
1463 FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN,
1464 NULL, --INITIAL_NUM_VALUE,
1465 G_APPLICATION_ID, --SRC_APPLICATION_ID
1466 NULL, --FSK_ITEMMASTER_2_2,
1467 NULL, --INSTANTIABLE_FLAG,
1468 NULL --DISPLAY_IN_SUMMARY_FLAG
1469 );
1470
1471 END IF; -- if row count > 0
1472
1473
1474
1475
1476
1477 -- Standard call to get message count and if count is 1, get message info.
1478 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1479
1480 -- end debug log
1481 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1482 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1483 G_MODULE||l_api_name,
1484 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1485 END IF;
1486
1487
1488 EXCEPTION
1489 WHEN OTHERS THEN
1490 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1491 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1492 G_MODULE||l_api_name,
1493 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1494 END IF;
1495
1496 x_return_status := G_RET_STS_UNEXP_ERROR ;
1497
1498 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1499 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1500 END IF;
1501
1502 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1503
1504
1505 END create_variable_options;
1506
1507
1508 /*
1509 ---------------------------------------------------
1510 -- PUBLIC Procedures and Functions
1511 ---------------------------------------------------
1512 */
1513 /*====================================================================+
1514 Procedure Name : import_variables
1515 Description : This is a PUBLIC API that imports Variables and Constants
1516 This API is called from publish rules concurrent program
1517 Parameters:
1518 p_org_id - Org Id of the rules to be published
1519
1520 +====================================================================*/
1521 PROCEDURE import_variables
1522 (
1523 p_api_version IN NUMBER,
1524 p_init_msg_list IN VARCHAR2,
1525 p_commit IN VARCHAR2,
1526 p_org_id IN NUMBER,
1527 x_return_status OUT NOCOPY VARCHAR2,
1528 x_msg_data OUT NOCOPY VARCHAR2,
1529 x_msg_count OUT NOCOPY NUMBER
1530 ) IS
1531
1532 CURSOR csr_cz_run_id IS
1533 SELECT cz_xfr_run_infos_s.NEXTVAL
1534 FROM dual;
1535
1536 -- CURSOR csr_variable_model_id(p_intent IN VARCHAR2) IS
1537 CURSOR csr_variable_model_id(p_orig_sys_ref IN VARCHAR2) IS
1538 SELECT devl_project_id
1539 FROM cz_devl_projects
1540 WHERE orig_sys_ref = p_orig_sys_ref
1541 AND devl_project_id = persistent_project_id
1542 AND deleted_flag = 0;
1543
1544 -- WHERE orig_sys_ref = G_VARIABLE_MODEL_OSR||p_intent
1545
1546 CURSOR csr_intent IS
1547 SELECT DISTINCT INTENT
1548 FROM okc_xprt_rule_hdrs_all
1549 WHERE org_id = p_org_id
1550 AND status_code = 'PENDINGPUB';
1551
1552
1553 l_intent VARCHAR2(1);
1554 l_api_version CONSTANT NUMBER := 1;
1555 l_api_name CONSTANT VARCHAR2(30) := 'import_variables';
1556 l_variable_model_id NUMBER :=NULL;
1557 l_run_id NUMBER;
1558 l_import_status VARCHAR2(1);
1559 l_orig_sys_ref cz_devl_projects.orig_sys_ref%TYPE;
1560
1561
1562 BEGIN
1563
1564 -- start debug log
1565 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1566 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1567 G_MODULE||l_api_name,
1568 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1569 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1570 G_MODULE||l_api_name,
1571 '100: Parameters ');
1572 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1573 G_MODULE||l_api_name,
1574 '100: p_org_id '||p_org_id);
1575 END IF;
1576
1577 -- Standard call to check for call compatibility.
1578 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1580 END IF;
1581
1582 -- Initialize message list if p_init_msg_list is set to TRUE.
1583 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1584 FND_MSG_PUB.initialize;
1585 END IF;
1586
1587 -- Initialize API return status to success
1588 x_return_status := FND_API.G_RET_STS_SUCCESS;
1589
1590 /*
1591 -- Generate the Run Id
1592 OPEN csr_cz_run_id;
1593 FETCH csr_cz_run_id INTO G_RUN_ID;
1594 CLOSE csr_cz_run_id;
1595 */
1596
1597 OPEN csr_intent;
1598 LOOP
1599 FETCH csr_intent INTO l_intent;
1600 EXIT WHEN csr_intent%NOTFOUND;
1601
1602 -- Initialize l_variable_model_id
1603 l_variable_model_id := NULL;
1604
1605 -- build variable model osr
1606 l_orig_sys_ref := G_VARIABLE_MODEL_OSR||l_intent;
1607
1608 -- check if Variable Model Already exists in CZ and get the Model Id
1609 -- OPEN csr_variable_model_id(p_intent => l_intent);
1610
1611 OPEN csr_variable_model_id(p_orig_sys_ref => l_orig_sys_ref);
1612 FETCH csr_variable_model_id INTO l_variable_model_id;
1613 CLOSE csr_variable_model_id;
1614
1615 -- CZ allows ONLY 1 model import for each run_id
1616 -- Generate the Run Id
1617 OPEN csr_cz_run_id;
1618 FETCH csr_cz_run_id INTO G_RUN_ID;
1619 CLOSE csr_cz_run_id;
1620
1621 -- debug log
1622 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1623 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1624 G_MODULE||l_api_name,
1625 '150: Run Id :'||G_RUN_ID);
1626 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1627 G_MODULE||l_api_name,
1628 '150: Variable Model Id :'||l_variable_model_id);
1629 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1630 G_MODULE||l_api_name,
1631 '150: Intent :'||l_intent);
1632 END IF;
1633
1634 create_variable_model
1635 (
1636 p_intent => l_intent,
1637 p_model_id => l_variable_model_id,
1638 x_return_status => x_return_status,
1639 x_msg_data => x_msg_data,
1640 x_msg_count => x_msg_count
1641 );
1642
1643 -- debug log
1644 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1645 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1646 G_MODULE||l_api_name,
1647 '200: After Calling create_variable_model x_return_status : '||x_return_status);
1648 END IF;
1649
1650 --- If any errors happen abort API
1651 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1652 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1653 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1654 RAISE FND_API.G_EXC_ERROR;
1655 END IF;
1656
1657 create_variable_component
1658 (
1659 p_intent => l_intent,
1660 x_return_status => x_return_status,
1661 x_msg_data => x_msg_data,
1662 x_msg_count => x_msg_count
1663 );
1664
1665 -- debug log
1666 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1667 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1668 G_MODULE||l_api_name,
1669 '300: After Calling create_variable_component x_return_status : '||x_return_status);
1670 END IF;
1671
1672 --- If any errors happen abort API
1673 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1675 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1676 RAISE FND_API.G_EXC_ERROR;
1677 END IF;
1678
1679 create_variable_features
1680 (
1681 p_intent => l_intent,
1682 x_return_status => x_return_status,
1683 x_msg_data => x_msg_data,
1684 x_msg_count => x_msg_count
1685 );
1686
1687 -- debug log
1688 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1689 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1690 G_MODULE||l_api_name,
1691 '400: After Calling create_variable_feature x_return_status : '||x_return_status);
1692 END IF;
1693
1694 --- If any errors happen abort API
1695 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1696 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1697 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1698 RAISE FND_API.G_EXC_ERROR;
1699 END IF;
1700
1701 create_variable_options
1702 (
1703 p_intent => l_intent,
1704 x_return_status => x_return_status,
1705 x_msg_data => x_msg_data,
1706 x_msg_count => x_msg_count
1707 );
1708
1709 -- debug log
1710 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1711 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1712 G_MODULE||l_api_name,
1713 '500: After Calling create_variable_options x_return_status : '||x_return_status);
1714 END IF;
1715
1716 --- If any errors happen abort API
1717 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1719 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1720 RAISE FND_API.G_EXC_ERROR;
1721 END IF;
1722
1723
1724 -- END LOOP;
1725 --CLOSE csr_intent;
1726
1727 -- debug log
1728 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1729 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1730 G_MODULE||l_api_name,
1731 '550: Calling OKC_XPRT_CZ_INT_PVT.import_generic ');
1732 END IF;
1733
1734 -- Call the CZ Generic Import to push data to CZ
1735 OKC_XPRT_CZ_INT_PVT.import_generic
1736 (
1737 p_api_version => l_api_version,
1738 p_run_id => G_RUN_ID,
1739 p_rp_folder_id => OKC_XPRT_CZ_INT_PVT.G_VARIABLE_FOLDER_ID ,
1740 x_run_id => l_run_id,
1741 x_return_status => l_import_status,
1742 x_msg_data => x_msg_data,
1743 x_msg_count => x_msg_count
1744 );
1745
1746 -- debug log
1747 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1748 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1749 G_MODULE||l_api_name,
1750 '600: After Calling OKC_XPRT_CZ_INT_PVT.import_generic x_return_status : '||
1751 l_import_status);
1752 END IF;
1753
1754 -- Log the Import Status and check if any records in the import tables have status not 'OK'
1755 OKC_XPRT_UTIL_PVT.check_import_status
1756 (
1757 p_run_id => G_RUN_ID,
1758 p_import_status => l_import_status,
1759 p_model_type => 'V', -- Variable Model
1760 x_return_status => x_return_status,
1761 x_msg_data => x_msg_data,
1762 x_msg_count => x_msg_count
1763 );
1764
1765 -- debug log
1766 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1767 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1768 G_MODULE||l_api_name,
1769 '700: After Calling OKC_XPRT_UTIL_PVT.check_import_status x_return_status : '||
1770 x_return_status);
1771 END IF;
1772
1773 --- If any errors happen abort API
1774 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1775 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1776 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1777 RAISE FND_API.G_EXC_ERROR;
1778 END IF;
1779
1780
1781 END LOOP;
1782 CLOSE csr_intent;
1783
1784 IF FND_API.To_Boolean( p_commit ) THEN
1785 COMMIT WORK;
1786 END IF;
1787
1788 -- Standard call to get message count and if count is 1, get message info.
1789 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1790
1791 -- end debug log
1792 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1793 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1794 G_MODULE||l_api_name,
1795 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1796 END IF;
1797
1798 EXCEPTION
1799 WHEN FND_API.G_EXC_ERROR THEN
1800 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1801 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1802 G_MODULE||l_api_name,
1803 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1804 END IF;
1805
1806 x_return_status := G_RET_STS_ERROR ;
1807 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1808
1809 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1810 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1811 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1812 G_MODULE||l_api_name,
1813 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1814 END IF;
1815
1816 x_return_status := G_RET_STS_UNEXP_ERROR ;
1817 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1818
1819 WHEN OTHERS THEN
1820 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1821 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1822 G_MODULE||l_api_name,
1823 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1824 END IF;
1825
1826 x_return_status := G_RET_STS_UNEXP_ERROR ;
1827 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1828 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1829 END IF;
1830 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1831
1832
1833 END import_variables;
1834
1835
1836
1837
1838
1839 END OKC_XPRT_IMPORT_VARIABLES_PVT;