[Home] [Help]
PACKAGE BODY: APPS.OKC_XPRT_IMPORT_TEMPLATE_PVT
Source
1 PACKAGE BODY OKC_XPRT_IMPORT_TEMPLATE_PVT AS
2 /* $Header: OKCVXTMPLB.pls 120.3 2006/02/20 21:40:31 asingam noship $ */
3
4 ---------------------------------------------------------------------------
5 -- GLOBAL VARIABLES
6 ---------------------------------------------------------------------------
7 G_RUN_ID NUMBER;
8 G_ORGANIZATION_NAME VARCHAR2(240);
9
10 ------------------------------------------------------------------------------
11 -- GLOBAL CONSTANTS
12 ------------------------------------------------------------------------------
13 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_XPRT_IMPORT_TEMPLATE_PVT';
14 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
15
16 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
17 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||g_pkg_name||'.';
18 G_APPLICATION_ID CONSTANT NUMBER :=510; -- OKC Application
19
20 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
21 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
22
23 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
24 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
25 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
26
27 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
28 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
29 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
30 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
31
32
33 ------------------------------------------------------------------------------
34 -- Orig_sys_ref
35 ------------------------------------------------------------------------------
36 G_TEMPLATE_MODEL_OSR CONSTANT VARCHAR2(255) := 'OKC:TEMPLATEMODEL:';
37 G_TEMPLATE_MODEL_TOPNODE_OSR CONSTANT VARCHAR2(255) := 'OKC:TEMPLATEMODELTOPNODE:' ;
38 G_TEMPLATE_MODEL_FEATURE_OSR CONSTANT VARCHAR2(255) := 'OKC:TEMPLATEMODELFEATURE:' ;
39 G_TEMPLATE_MODEL_OPTION_OSR CONSTANT VARCHAR2(255) := 'OKC:TEMPLATEMODELOPTION:' ;
40 G_TMPL_MODEL_CM_REF_NODE_OSR CONSTANT VARCHAR2(255) := 'OKC:TEMPLATEMODEL-CLAUSEMODEL-REFNODE:' ;
41 G_CLAUSE_MODEL_TOPNODE_OSR CONSTANT VARCHAR2(255) := 'OKC:CLAUSEMODELTOPNODE:' ;
42 G_TEMPLATE_FOLDER_OSR CONSTANT VARCHAR2(255) := 'OKC:TEMPLATEFOLDER:';
43
44
45 ---------------------------------------------------
46 -- PRIVATE Procedures and Functions
47 ---------------------------------------------------
48
49 ---------------------------------------------------
50 -- Forward Declaration Procedure: create_template_options
51 ---------------------------------------------------
52 PROCEDURE create_template_options
53 (
54 p_question_id IN NUMBER,
55 p_value_set_id IN NUMBER,
56 p_derived_template_id IN NUMBER,
57 p_intent IN VARCHAR2,
58 p_org_id IN NUMBER,
59 x_return_status OUT NOCOPY VARCHAR2,
60 x_msg_data OUT NOCOPY VARCHAR2,
61 x_msg_count OUT NOCOPY NUMBER
62 ) ;
63
64 /*====================================================================+
65 Procedure Name : create_template_model
66 Description : This is a private API that creates the Template Model
67 Each template has a corresponding Model in CZ
68 Parameters:
69 p_model_id - If model exists then refresh the model
70 p_template_name - Name of the template
71 p_template_id - Id of the template
72 p_intent - Intent of the template
73 p_org_id - Org Id of the template
74
75 +====================================================================*/
76
77 PROCEDURE create_template_model
78 (
79 p_model_id IN NUMBER,
80 p_template_name IN VARCHAR2,
81 p_template_id IN NUMBER,
82 p_intent IN VARCHAR2,
83 p_org_id IN NUMBER,
84 x_return_status OUT NOCOPY VARCHAR2,
85 x_msg_data OUT NOCOPY VARCHAR2,
86 x_msg_count OUT NOCOPY NUMBER
87 ) IS
88
89 l_cz_imp_devl_project_rec CZ_IMP_DEVL_PROJECT%ROWTYPE;
90 l_api_name CONSTANT VARCHAR2(30) := 'create_template_model';
91
92
93 BEGIN
94
95 -- start debug log
96 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
97 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
98 G_MODULE||l_api_name,
99 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
100 END IF;
101
102 x_return_status := G_RET_STS_SUCCESS;
103
104
105
106 -- populate the l_cz_imp_devl_project_rec
107 l_cz_imp_devl_project_rec.DEVL_PROJECT_ID:= NULL;
108 l_cz_imp_devl_project_rec.INTL_TEXT_ID:= NULL;
109 l_cz_imp_devl_project_rec.ORGANIZATION_ID:= p_org_id;
110 l_cz_imp_devl_project_rec.NAME:= G_TEMPLATE_MODEL_OSR||
111 p_org_id||':'||
112 p_intent||':'||
113 p_template_id;
114 l_cz_imp_devl_project_rec.GSL_FILENAME:= NULL;
115 l_cz_imp_devl_project_rec.TOP_ITEM_ID:= 1;
116 l_cz_imp_devl_project_rec.VERSION:= NULL;
117 l_cz_imp_devl_project_rec.EXPLOSION_TYPE:= NULL;
118 l_cz_imp_devl_project_rec.DESC_TEXT:= p_template_name;
119 l_cz_imp_devl_project_rec.ORIG_SYS_REF:= G_TEMPLATE_MODEL_OSR||
120 p_org_id||':'||
121 p_intent||':'||
122 p_template_id;
123 l_cz_imp_devl_project_rec.CREATION_DATE:= SYSDATE;
124 l_cz_imp_devl_project_rec.LAST_UPDATE_DATE:= SYSDATE;
125 l_cz_imp_devl_project_rec.DELETED_FLAG:= '0'; -- '0' Not deleted
126 l_cz_imp_devl_project_rec.EFF_FROM:= NULL;
127 l_cz_imp_devl_project_rec.EFF_TO:= NULL;
128 l_cz_imp_devl_project_rec.CREATED_BY:= FND_GLOBAL.USER_ID;
129 l_cz_imp_devl_project_rec.LAST_UPDATED_BY:= FND_GLOBAL.USER_ID;
130 l_cz_imp_devl_project_rec.SECURITY_MASK:= NULL;
131 l_cz_imp_devl_project_rec.EFF_MASK:= NULL;
132 l_cz_imp_devl_project_rec.CHECKOUT_USER:= NULL;
133 l_cz_imp_devl_project_rec.RUN_ID:= G_RUN_ID;
134 l_cz_imp_devl_project_rec.REC_STATUS:= NULL;
135 l_cz_imp_devl_project_rec.DISPOSITION:= NULL;
136 l_cz_imp_devl_project_rec.FSK_INTLTEXT_1_1:= NULL;
137 l_cz_imp_devl_project_rec.MODEL_ID:= p_model_id;
138 l_cz_imp_devl_project_rec.PLAN_LEVEL:= 0;
139 l_cz_imp_devl_project_rec.PERSISTENT_PROJECT_ID:= NULL;
140 l_cz_imp_devl_project_rec.MODEL_TYPE:= 'C'; -- non BOM Model
141 l_cz_imp_devl_project_rec.INVENTORY_ITEM_ID:= NULL;
142 l_cz_imp_devl_project_rec.PRODUCT_KEY:= G_TEMPLATE_MODEL_OSR||
143 p_org_id||':'||
144 p_intent||':'||
145 p_template_id;
146 l_cz_imp_devl_project_rec.LAST_UPDATE_LOGIN:= FND_GLOBAL.LOGIN_ID;
147 l_cz_imp_devl_project_rec.BOM_CAPTION_RULE_ID:= NULL;
148 l_cz_imp_devl_project_rec.NONBOM_CAPTION_RULE_ID:= OKC_XPRT_CZ_INT_PVT.G_CAPTION_RULE_DESC; -- display desc in runtime UIs
149 l_cz_imp_devl_project_rec.SEEDED_FLAG:= '1'; -- '0' unseeded , '1' seeded
150
151 --
152
153 -- insert the Variable Model Record into cz_devl_project
154 INSERT INTO cz_imp_devl_project
155 (
156 DEVL_PROJECT_ID,
157 INTL_TEXT_ID,
158 ORGANIZATION_ID,
159 NAME,
160 GSL_FILENAME,
161 TOP_ITEM_ID,
162 VERSION,
163 EXPLOSION_TYPE,
164 DESC_TEXT,
165 ORIG_SYS_REF,
166 CREATION_DATE,
167 LAST_UPDATE_DATE,
168 DELETED_FLAG,
169 EFF_FROM,
170 EFF_TO,
171 CREATED_BY,
172 LAST_UPDATED_BY,
173 SECURITY_MASK,
174 EFF_MASK,
175 CHECKOUT_USER,
176 RUN_ID,
177 REC_STATUS,
178 DISPOSITION,
179 FSK_INTLTEXT_1_1,
180 MODEL_ID,
181 PLAN_LEVEL,
182 PERSISTENT_PROJECT_ID,
183 MODEL_TYPE,
184 INVENTORY_ITEM_ID,
185 PRODUCT_KEY,
186 LAST_UPDATE_LOGIN,
187 BOM_CAPTION_RULE_ID,
188 NONBOM_CAPTION_RULE_ID,
189 SEEDED_FLAG
190 )
191 VALUES
192 (
193 l_cz_imp_devl_project_rec.DEVL_PROJECT_ID,
194 l_cz_imp_devl_project_rec.INTL_TEXT_ID,
195 l_cz_imp_devl_project_rec.ORGANIZATION_ID,
196 l_cz_imp_devl_project_rec.NAME,
197 l_cz_imp_devl_project_rec.GSL_FILENAME,
198 l_cz_imp_devl_project_rec.TOP_ITEM_ID,
199 l_cz_imp_devl_project_rec.VERSION,
200 l_cz_imp_devl_project_rec.EXPLOSION_TYPE,
201 l_cz_imp_devl_project_rec.DESC_TEXT,
202 l_cz_imp_devl_project_rec.ORIG_SYS_REF,
203 l_cz_imp_devl_project_rec.CREATION_DATE,
204 l_cz_imp_devl_project_rec.LAST_UPDATE_DATE,
205 l_cz_imp_devl_project_rec.DELETED_FLAG,
206 l_cz_imp_devl_project_rec.EFF_FROM,
207 l_cz_imp_devl_project_rec.EFF_TO,
208 l_cz_imp_devl_project_rec.CREATED_BY,
209 l_cz_imp_devl_project_rec.LAST_UPDATED_BY,
210 l_cz_imp_devl_project_rec.SECURITY_MASK,
211 l_cz_imp_devl_project_rec.EFF_MASK,
212 l_cz_imp_devl_project_rec.CHECKOUT_USER,
213 l_cz_imp_devl_project_rec.RUN_ID,
214 l_cz_imp_devl_project_rec.REC_STATUS,
215 l_cz_imp_devl_project_rec.DISPOSITION,
216 l_cz_imp_devl_project_rec.FSK_INTLTEXT_1_1,
217 l_cz_imp_devl_project_rec.MODEL_ID,
218 l_cz_imp_devl_project_rec.PLAN_LEVEL,
219 l_cz_imp_devl_project_rec.PERSISTENT_PROJECT_ID,
220 l_cz_imp_devl_project_rec.MODEL_TYPE,
221 l_cz_imp_devl_project_rec.INVENTORY_ITEM_ID,
222 l_cz_imp_devl_project_rec.PRODUCT_KEY,
223 l_cz_imp_devl_project_rec.LAST_UPDATE_LOGIN,
224 l_cz_imp_devl_project_rec.BOM_CAPTION_RULE_ID,
225 l_cz_imp_devl_project_rec.NONBOM_CAPTION_RULE_ID,
226 l_cz_imp_devl_project_rec.SEEDED_FLAG
227 );
228
229
230 -- Standard call to get message count and if count is 1, get message info.
231 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
232
233 -- end debug log
234 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
235 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
236 G_MODULE||l_api_name,
237 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
238 END IF;
239
240 EXCEPTION
241 WHEN OTHERS THEN
242 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
243 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
244 G_MODULE||l_api_name,
245 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
246 END IF;
247
248 x_return_status := G_RET_STS_UNEXP_ERROR ;
249
250 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
251 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
252 END IF;
253
254 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
255
256
257 END create_template_model;
258
259 /*====================================================================+
260 Procedure Name : create_template_component
261 Description : This is a private API that creates the dummy template model
262 component
263 Parameters:
264 p_template_name - Name of the template
265 p_template_id - Id of the template
266 p_intent - Intent of the template
267 p_org_id - Org Id of the template
268
269
270 +====================================================================*/
271
272 PROCEDURE create_template_component
273 (
274 p_template_name IN VARCHAR2,
275 p_template_id IN NUMBER,
276 p_intent IN VARCHAR2,
277 p_org_id IN NUMBER,
278 x_return_status OUT NOCOPY VARCHAR2,
279 x_msg_data OUT NOCOPY VARCHAR2,
280 x_msg_count OUT NOCOPY NUMBER
281 ) IS
282
283
284 CURSOR csr_installed_languages IS
285 SELECT L.LANGUAGE_CODE
286 FROM FND_LANGUAGES L
287 WHERE L.INSTALLED_FLAG IN ('I', 'B');
288
289 l_language FND_LANGUAGES.LANGUAGE_CODE%TYPE;
290
291 l_cz_imp_ps_nodes_rec CZ_IMP_PS_NODES%ROWTYPE;
292 l_api_name CONSTANT VARCHAR2(30) := 'create_template_component';
293
294 BEGIN
295
296 -- start debug log
297 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
298 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
299 G_MODULE||l_api_name,
300 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
301 END IF;
302
303 x_return_status := G_RET_STS_SUCCESS;
304
305 -- Put the Name in the description Column of cz_ps_nodes
306
307 OPEN csr_installed_languages;
308 LOOP
309 FETCH csr_installed_languages INTO l_language;
310 EXIT WHEN csr_installed_languages%NOTFOUND;
311
312 -- Insert into cz_imp_localized_text
313
314 INSERT INTO CZ_IMP_LOCALIZED_TEXTS
315 (
316 LAST_UPDATE_LOGIN,
317 LOCALE_ID,
318 LOCALIZED_STR,
319 INTL_TEXT_ID,
320 CREATION_DATE,
321 LAST_UPDATE_DATE,
322 DELETED_FLAG,
323 EFF_FROM,
324 EFF_TO,
325 CREATED_BY,
326 LAST_UPDATED_BY,
327 SECURITY_MASK,
328 EFF_MASK,
329 CHECKOUT_USER,
330 LANGUAGE,
331 ORIG_SYS_REF,
332 SOURCE_LANG,
333 RUN_ID,
334 REC_STATUS,
335 DISPOSITION,
336 MODEL_ID,
337 FSK_DEVLPROJECT_1_1,
338 MESSAGE,
339 SEEDED_FLAG
340 )
341 VALUES
342 (
343 FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN
344 NULL, -- LOCALE_ID
345 p_template_name, --LOCALIZED_STR
346 NULL, -- INTL_TEXT_ID
347 SYSDATE, -- CREATION_DATE
348 SYSDATE, -- LAST_UPDATE_DATE
349 '0', -- DELETED_FLAG
350 NULL, -- EFF_FROM
351 NULL, -- EFF_TO
352 FND_GLOBAL.USER_ID, -- CREATED_BY
353 FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
354 NULL, -- SECURITY_MASK
355 NULL, -- EFF_MASK
356 NULL, -- CHECKOUT_USER
357 l_language, --LANGUAGE
358 G_TEMPLATE_MODEL_TOPNODE_OSR||p_org_id||':'||p_intent||':'||p_template_id, --ORIG_SYS_REF
359 USERENV('LANG'), --SOURCE_LANG
360 G_RUN_ID, -- RUN_ID
361 NULL, -- REC_STATUS
362 NULL, -- DISPOSITION
363 NULL, -- MODEL_ID
364 G_TEMPLATE_MODEL_OSR||p_org_id||':'||p_intent||':'||p_template_id, --FSK_DEVLPROJECT_1_1
365 NULL, -- MESSAGE
366 NULL -- SEEDED_FLAG
367 );
368
369 END LOOP; -- for all installed languages
370 CLOSE csr_installed_languages;
371
372
373 -- Populate the cz_imp_ps_nodes record
374
375 l_cz_imp_ps_nodes_rec.PS_NODE_ID:= NULL;
376 l_cz_imp_ps_nodes_rec.DEVL_PROJECT_ID:= NULL;
377 l_cz_imp_ps_nodes_rec.FROM_POPULATOR_ID:= NULL;
378 l_cz_imp_ps_nodes_rec.PROPERTY_BACKPTR:= NULL;
379 l_cz_imp_ps_nodes_rec.ITEM_TYPE_BACKPTR:= NULL;
380 l_cz_imp_ps_nodes_rec.INTL_TEXT_ID:= NULL;
381 l_cz_imp_ps_nodes_rec.SUB_CONS_ID:= NULL;
382 l_cz_imp_ps_nodes_rec.ORGANIZATION_ID:= p_org_id;
383 l_cz_imp_ps_nodes_rec.ITEM_ID:= NULL;
384 l_cz_imp_ps_nodes_rec.EXPLOSION_TYPE:= NULL;
385 l_cz_imp_ps_nodes_rec.NAME:= G_TEMPLATE_MODEL_TOPNODE_OSR||
386 p_org_id||':'||
387 p_intent||':'||
388 p_template_id;
389 l_cz_imp_ps_nodes_rec.ORIG_SYS_REF:= G_TEMPLATE_MODEL_TOPNODE_OSR||
390 p_org_id||':'||
391 p_intent||':'||
392 p_template_id;
393 l_cz_imp_ps_nodes_rec.RESOURCE_FLAG:= NULL;
394 l_cz_imp_ps_nodes_rec.TOP_ITEM_ID:= 1; -- same value as in cz_imp_devl_projects
395 l_cz_imp_ps_nodes_rec.INITIAL_VALUE:= NULL;
396 l_cz_imp_ps_nodes_rec.PARENT_ID:= NULL;
397 l_cz_imp_ps_nodes_rec.MINIMUM:= 1;
398 l_cz_imp_ps_nodes_rec.MAXIMUM:= 1;
399 l_cz_imp_ps_nodes_rec.PS_NODE_TYPE:= 259; -- Component
400 l_cz_imp_ps_nodes_rec.FEATURE_TYPE:= NULL;
401 l_cz_imp_ps_nodes_rec.PRODUCT_FLAG:= '0';
402 l_cz_imp_ps_nodes_rec.REFERENCE_ID:= NULL;
403 l_cz_imp_ps_nodes_rec.MULTI_CONFIG_FLAG:= NULL;
404 l_cz_imp_ps_nodes_rec.ORDER_SEQ_FLAG:= NULL;
405 l_cz_imp_ps_nodes_rec.SYSTEM_NODE_FLAG:= NULL;
406 l_cz_imp_ps_nodes_rec.TREE_SEQ:= 1;
407 l_cz_imp_ps_nodes_rec.COUNTED_OPTIONS_FLAG:= '0';
408 l_cz_imp_ps_nodes_rec.UI_OMIT:= '0'; -- Display in UI
409 l_cz_imp_ps_nodes_rec.UI_SECTION:= 0;
410 l_cz_imp_ps_nodes_rec.BOM_TREATMENT:= NULL;
411 l_cz_imp_ps_nodes_rec.RUN_ID:= G_RUN_ID;
412 l_cz_imp_ps_nodes_rec.REC_STATUS:= NULL;
413 l_cz_imp_ps_nodes_rec.DISPOSITION:= NULL;
414 l_cz_imp_ps_nodes_rec.DELETED_FLAG := 0;
415 l_cz_imp_ps_nodes_rec.EFF_FROM:= NULL;
416 l_cz_imp_ps_nodes_rec.EFF_TO:= NULL;
417 l_cz_imp_ps_nodes_rec.EFF_MASK:= NULL;
418 l_cz_imp_ps_nodes_rec.USER_STR01:= NULL;
419 l_cz_imp_ps_nodes_rec.USER_STR02:= NULL;
420 l_cz_imp_ps_nodes_rec.USER_STR03:= NULL;
421 l_cz_imp_ps_nodes_rec.USER_STR04:= NULL;
422 l_cz_imp_ps_nodes_rec.USER_NUM01:= NULL;
423 l_cz_imp_ps_nodes_rec.USER_NUM02:= NULL;
424 l_cz_imp_ps_nodes_rec.USER_NUM03:= NULL;
425 l_cz_imp_ps_nodes_rec.USER_NUM04:= NULL;
426 l_cz_imp_ps_nodes_rec.CHECKOUT_USER:= NULL;
427 l_cz_imp_ps_nodes_rec.CREATION_DATE:= SYSDATE;
428 l_cz_imp_ps_nodes_rec.LAST_UPDATE_DATE:= SYSDATE;
429 l_cz_imp_ps_nodes_rec.CREATED_BY:= FND_GLOBAL.USER_ID;
430 l_cz_imp_ps_nodes_rec.LAST_UPDATED_BY:= FND_GLOBAL.USER_ID;
431 l_cz_imp_ps_nodes_rec.SECURITY_MASK:= NULL;
432 l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_1:= G_TEMPLATE_MODEL_TOPNODE_OSR||
433 p_org_id||':'||
434 p_intent||':'||
435 p_template_id;
436 l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_EXT:= NULL;
437 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_1:= NULL;
438 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_EXT:= NULL;
439 l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_1:= NULL;
440 l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_EXT:= NULL;
441 l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_1:= NULL;
442 l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_EXT:= NULL;
443 l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_1:= G_TEMPLATE_MODEL_OSR||
444 p_org_id||':'||
445 p_intent||':'||
446 p_template_id;
447 l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_EXT:= NULL;
448 l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_ID:= NULL;
449 l_cz_imp_ps_nodes_rec.COMPONENT_CODE:= NULL;
450 l_cz_imp_ps_nodes_rec.PLAN_LEVEL:= 0; --Plan Level for Component:0
451 l_cz_imp_ps_nodes_rec.BOM_ITEM_TYPE:= NULL;
452 l_cz_imp_ps_nodes_rec.SO_ITEM_TYPE_CODE:= NULL;
453 l_cz_imp_ps_nodes_rec.MINIMUM_SELECTED:= NULL;
454 l_cz_imp_ps_nodes_rec.MAXIMUM_SELECTED:= NULL;
455 l_cz_imp_ps_nodes_rec.BOM_REQUIRED:= NULL;
456 l_cz_imp_ps_nodes_rec.MUTUALLY_EXCLUSIVE_OPTIONS:= NULL;
457 l_cz_imp_ps_nodes_rec.OPTIONAL:= NULL;
458 l_cz_imp_ps_nodes_rec.FSK_EXPLNODE_1_1:= NULL;
459 l_cz_imp_ps_nodes_rec.FSK_PSNODE_6_1:= NULL;
460 l_cz_imp_ps_nodes_rec.EFFECTIVE_FROM:= OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_BEGIN;
461 l_cz_imp_ps_nodes_rec.EFFECTIVE_UNTIL:= OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_END;
462 l_cz_imp_ps_nodes_rec.EFFECTIVE_USAGE_MASK:= NULL;
463 l_cz_imp_ps_nodes_rec.EFFECTIVITY_SET_ID:= NULL;
464 l_cz_imp_ps_nodes_rec.FSK_EFFSET_7_1:= NULL;
465 l_cz_imp_ps_nodes_rec.DECIMAL_QTY_FLAG:= 0; -- 0 for all nodes
466 l_cz_imp_ps_nodes_rec.QUOTEABLE_FLAG:= NULL;
467 l_cz_imp_ps_nodes_rec.PRIMARY_UOM_CODE:= NULL;
468 l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_PATH:= NULL; -- Must be NULL
469 l_cz_imp_ps_nodes_rec.BOM_SORT_ORDER:= NULL;
470 l_cz_imp_ps_nodes_rec.IB_TRACKABLE:= NULL;
471 l_cz_imp_ps_nodes_rec.LAST_UPDATE_LOGIN:= FND_GLOBAL.LOGIN_ID;
472 l_cz_imp_ps_nodes_rec.INITIAL_NUM_VALUE:= NULL;
473 l_cz_imp_ps_nodes_rec.SRC_APPLICATION_ID:= G_APPLICATION_ID;
474 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_2:= NULL;
475 l_cz_imp_ps_nodes_rec.INSTANTIABLE_FLAG:= NULL;
476 l_cz_imp_ps_nodes_rec.DISPLAY_IN_SUMMARY_FLAG:= NULL;
477
478 -- insert top node for Clause Model into cz_imp_ps_nodes
479
480 INSERT INTO cz_imp_ps_nodes
481 (
482 PS_NODE_ID,
483 DEVL_PROJECT_ID,
484 FROM_POPULATOR_ID,
485 PROPERTY_BACKPTR,
486 ITEM_TYPE_BACKPTR,
487 INTL_TEXT_ID,
488 SUB_CONS_ID,
489 ORGANIZATION_ID,
490 ITEM_ID,
491 EXPLOSION_TYPE,
492 NAME,
493 ORIG_SYS_REF,
494 RESOURCE_FLAG,
495 TOP_ITEM_ID,
496 INITIAL_VALUE,
497 PARENT_ID,
498 MINIMUM,
499 MAXIMUM,
500 PS_NODE_TYPE,
501 FEATURE_TYPE,
502 PRODUCT_FLAG,
503 REFERENCE_ID,
504 MULTI_CONFIG_FLAG,
505 ORDER_SEQ_FLAG,
506 SYSTEM_NODE_FLAG,
507 TREE_SEQ,
508 COUNTED_OPTIONS_FLAG,
509 UI_OMIT,
510 UI_SECTION,
511 BOM_TREATMENT,
512 RUN_ID,
513 REC_STATUS,
514 DISPOSITION,
515 DELETED_FLAG ,
516 EFF_FROM,
517 EFF_TO,
518 EFF_MASK,
519 USER_STR01,
520 USER_STR02,
521 USER_STR03,
522 USER_STR04,
523 USER_NUM01,
524 USER_NUM02,
525 USER_NUM03,
526 USER_NUM04,
527 CHECKOUT_USER,
528 CREATION_DATE,
529 LAST_UPDATE_DATE,
530 CREATED_BY,
531 LAST_UPDATED_BY,
532 SECURITY_MASK,
533 FSK_INTLTEXT_1_1,
534 FSK_INTLTEXT_1_EXT,
535 FSK_ITEMMASTER_2_1,
536 FSK_ITEMMASTER_2_EXT,
537 FSK_PSNODE_3_1,
538 FSK_PSNODE_3_EXT,
539 FSK_PSNODE_4_1,
540 FSK_PSNODE_4_EXT,
541 FSK_DEVLPROJECT_5_1,
542 FSK_DEVLPROJECT_5_EXT,
543 COMPONENT_SEQUENCE_ID,
544 COMPONENT_CODE,
545 PLAN_LEVEL,
546 BOM_ITEM_TYPE,
547 SO_ITEM_TYPE_CODE,
548 MINIMUM_SELECTED,
549 MAXIMUM_SELECTED,
550 BOM_REQUIRED,
551 MUTUALLY_EXCLUSIVE_OPTIONS,
552 OPTIONAL,
553 FSK_EXPLNODE_1_1,
554 FSK_PSNODE_6_1,
555 EFFECTIVE_FROM,
556 EFFECTIVE_UNTIL,
557 EFFECTIVE_USAGE_MASK,
558 EFFECTIVITY_SET_ID,
559 FSK_EFFSET_7_1,
560 DECIMAL_QTY_FLAG,
561 QUOTEABLE_FLAG,
562 PRIMARY_UOM_CODE,
563 COMPONENT_SEQUENCE_PATH,
564 BOM_SORT_ORDER,
565 IB_TRACKABLE,
566 LAST_UPDATE_LOGIN,
567 INITIAL_NUM_VALUE,
568 SRC_APPLICATION_ID,
569 FSK_ITEMMASTER_2_2,
570 INSTANTIABLE_FLAG,
571 DISPLAY_IN_SUMMARY_FLAG
572 )
573 VALUES
574 (
575 l_cz_imp_ps_nodes_rec.PS_NODE_ID,
576 l_cz_imp_ps_nodes_rec.DEVL_PROJECT_ID,
577 l_cz_imp_ps_nodes_rec.FROM_POPULATOR_ID,
578 l_cz_imp_ps_nodes_rec.PROPERTY_BACKPTR,
579 l_cz_imp_ps_nodes_rec.ITEM_TYPE_BACKPTR,
580 l_cz_imp_ps_nodes_rec.INTL_TEXT_ID,
581 l_cz_imp_ps_nodes_rec.SUB_CONS_ID,
582 l_cz_imp_ps_nodes_rec.ORGANIZATION_ID,
583 l_cz_imp_ps_nodes_rec.ITEM_ID,
584 l_cz_imp_ps_nodes_rec.EXPLOSION_TYPE,
585 l_cz_imp_ps_nodes_rec.NAME,
586 l_cz_imp_ps_nodes_rec.ORIG_SYS_REF,
587 l_cz_imp_ps_nodes_rec.RESOURCE_FLAG,
588 l_cz_imp_ps_nodes_rec.TOP_ITEM_ID,
589 l_cz_imp_ps_nodes_rec.INITIAL_VALUE,
590 l_cz_imp_ps_nodes_rec.PARENT_ID,
591 l_cz_imp_ps_nodes_rec.MINIMUM,
592 l_cz_imp_ps_nodes_rec.MAXIMUM,
593 l_cz_imp_ps_nodes_rec.PS_NODE_TYPE,
594 l_cz_imp_ps_nodes_rec.FEATURE_TYPE,
595 l_cz_imp_ps_nodes_rec.PRODUCT_FLAG,
596 l_cz_imp_ps_nodes_rec.REFERENCE_ID,
597 l_cz_imp_ps_nodes_rec.MULTI_CONFIG_FLAG,
598 l_cz_imp_ps_nodes_rec.ORDER_SEQ_FLAG,
599 l_cz_imp_ps_nodes_rec.SYSTEM_NODE_FLAG,
600 l_cz_imp_ps_nodes_rec.TREE_SEQ,
601 l_cz_imp_ps_nodes_rec.COUNTED_OPTIONS_FLAG,
602 l_cz_imp_ps_nodes_rec.UI_OMIT,
603 l_cz_imp_ps_nodes_rec.UI_SECTION,
604 l_cz_imp_ps_nodes_rec.BOM_TREATMENT,
605 l_cz_imp_ps_nodes_rec.RUN_ID,
606 l_cz_imp_ps_nodes_rec.REC_STATUS,
607 l_cz_imp_ps_nodes_rec.DISPOSITION,
608 l_cz_imp_ps_nodes_rec.DELETED_FLAG ,
609 l_cz_imp_ps_nodes_rec.EFF_FROM,
610 l_cz_imp_ps_nodes_rec.EFF_TO,
611 l_cz_imp_ps_nodes_rec.EFF_MASK,
612 l_cz_imp_ps_nodes_rec.USER_STR01,
613 l_cz_imp_ps_nodes_rec.USER_STR02,
614 l_cz_imp_ps_nodes_rec.USER_STR03,
615 l_cz_imp_ps_nodes_rec.USER_STR04,
616 l_cz_imp_ps_nodes_rec.USER_NUM01,
617 l_cz_imp_ps_nodes_rec.USER_NUM02,
618 l_cz_imp_ps_nodes_rec.USER_NUM03,
619 l_cz_imp_ps_nodes_rec.USER_NUM04,
620 l_cz_imp_ps_nodes_rec.CHECKOUT_USER,
621 l_cz_imp_ps_nodes_rec.CREATION_DATE,
622 l_cz_imp_ps_nodes_rec.LAST_UPDATE_DATE,
623 l_cz_imp_ps_nodes_rec.CREATED_BY,
624 l_cz_imp_ps_nodes_rec.LAST_UPDATED_BY,
625 l_cz_imp_ps_nodes_rec.SECURITY_MASK,
626 l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_1,
627 l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_EXT,
628 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_1,
629 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_EXT,
630 l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_1,
631 l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_EXT,
632 l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_1,
633 l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_EXT,
634 l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_1,
635 l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_EXT,
636 l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_ID,
637 l_cz_imp_ps_nodes_rec.COMPONENT_CODE,
638 l_cz_imp_ps_nodes_rec.PLAN_LEVEL,
639 l_cz_imp_ps_nodes_rec.BOM_ITEM_TYPE,
640 l_cz_imp_ps_nodes_rec.SO_ITEM_TYPE_CODE,
641 l_cz_imp_ps_nodes_rec.MINIMUM_SELECTED,
642 l_cz_imp_ps_nodes_rec.MAXIMUM_SELECTED,
643 l_cz_imp_ps_nodes_rec.BOM_REQUIRED,
644 l_cz_imp_ps_nodes_rec.MUTUALLY_EXCLUSIVE_OPTIONS,
645 l_cz_imp_ps_nodes_rec.OPTIONAL,
646 l_cz_imp_ps_nodes_rec.FSK_EXPLNODE_1_1,
647 l_cz_imp_ps_nodes_rec.FSK_PSNODE_6_1,
648 l_cz_imp_ps_nodes_rec.EFFECTIVE_FROM,
649 l_cz_imp_ps_nodes_rec.EFFECTIVE_UNTIL,
650 l_cz_imp_ps_nodes_rec.EFFECTIVE_USAGE_MASK,
651 l_cz_imp_ps_nodes_rec.EFFECTIVITY_SET_ID,
652 l_cz_imp_ps_nodes_rec.FSK_EFFSET_7_1,
653 l_cz_imp_ps_nodes_rec.DECIMAL_QTY_FLAG,
654 l_cz_imp_ps_nodes_rec.QUOTEABLE_FLAG,
655 l_cz_imp_ps_nodes_rec.PRIMARY_UOM_CODE,
656 l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_PATH,
657 l_cz_imp_ps_nodes_rec.BOM_SORT_ORDER,
658 l_cz_imp_ps_nodes_rec.IB_TRACKABLE,
659 l_cz_imp_ps_nodes_rec.LAST_UPDATE_LOGIN,
660 l_cz_imp_ps_nodes_rec.INITIAL_NUM_VALUE,
661 l_cz_imp_ps_nodes_rec.SRC_APPLICATION_ID,
662 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_2,
663 l_cz_imp_ps_nodes_rec.INSTANTIABLE_FLAG,
664 l_cz_imp_ps_nodes_rec.DISPLAY_IN_SUMMARY_FLAG
665 );
666
667
668 -- Standard call to get message count and if count is 1, get message info.
669 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
670
671
672
673 -- end debug log
674 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
675 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
676 G_MODULE||l_api_name,
677 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
678 END IF;
679
680 EXCEPTION
681 WHEN OTHERS THEN
682 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
683 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
684 G_MODULE||l_api_name,
685 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
686 END IF;
687
688 x_return_status := G_RET_STS_UNEXP_ERROR ;
689
690 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
691 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
692 END IF;
693
694 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
695
696
697 END create_template_component;
698
699 /*====================================================================+
700 Procedure Name : create_template_feature
701 Description : This is a private API that creates the template model
702 feature.
703 All Questions on the template will be created as features
704 Following features will be created
705 Questions with datatype L or B will be created as Option features
706 All the possible values for the value set will be created as options
707 Questions with datatype N will be created as decimal features
708 No options will be created for decimal features
709 We cannot support Boolean feature for the following reasons:
710 If a boolean feature is on RHS i.e outcome then making the Boolean feature
711 as false also changes the LHS i.e conditions to be false.
712 We don't want any changes to LHS.
713 For the same reason we cannot go with the Requires rules
714
715 All CONSTANTS used in the template rules will be created as Decimal Features
716 Create the Constants as Decimal Features under the Variable Model
717
718
719 Parameters:
720 p_template_name - Name of the template
721 p_derived_template_id - In case of revision template this is the
722 parent template id, else it is template_id
723 p_template_id - Id of the template
724 p_intent - Intent of the template
725 p_org_id - Org Id of the template
726
727
728 +====================================================================*/
729 PROCEDURE create_template_feature
730 (
731 p_template_id IN VARCHAR2,
732 p_derived_template_id IN VARCHAR2,
733 p_intent IN VARCHAR2,
734 p_org_id IN NUMBER,
735 x_return_status OUT NOCOPY VARCHAR2,
736 x_msg_data OUT NOCOPY VARCHAR2,
737 x_msg_count OUT NOCOPY NUMBER
738 ) IS
739
740 l_cz_imp_ps_nodes_rec CZ_IMP_PS_NODES%ROWTYPE;
741 l_api_name CONSTANT VARCHAR2(30) := 'create_template_feature';
742 l_model_feature_name VARCHAR2(255);
743 l_question_id okc_xprt_questions_b.question_id%TYPE;
744 l_sequence_num okc_xprt_question_orders.sequence_num%TYPE;
745 l_value_set_name okc_xprt_questions_b.value_set_name%TYPE;
746 l_feature_type NUMBER;
747 l_feature_min_val NUMBER;
748 l_feature_max_val NUMBER;
749
750 TYPE TmplQstIdList IS TABLE OF okc_xprt_questions_tl.question_id%TYPE INDEX BY BINARY_INTEGER;
751 TYPE TmplQstNameList IS TABLE OF okc_xprt_questions_tl.prompt%TYPE INDEX BY BINARY_INTEGER;
752 TYPE LanguageList IS TABLE OF okc_xprt_questions_tl.language%TYPE INDEX BY BINARY_INTEGER;
753 TYPE SourceLangList IS TABLE OF okc_xprt_questions_tl.source_lang%TYPE INDEX BY BINARY_INTEGER;
754
755 TmplQstId_tbl TmplQstIdList;
756 TmplQstName_tbl TmplQstNameList;
757 language_tbl LanguageList;
758 sourceLang_tbl SourceLangList;
759
760 CURSOR csr_translated_qst IS
761 SELECT q.question_id,
762 DECODE(q.prompt,NULL,q.question_name,q.prompt),
763 q.language,
764 q.source_lang
765 FROM okc_xprt_questions_tl q,
766 okc_xprt_question_orders o
767 WHERE q.question_id = o.question_id
768 AND o.question_rule_status IN ('ACTIVE','PENDINGPUB')
769 AND o.template_id = p_template_id;
770
771 /*
772 Feature Type:
773 If Question datatype is 'N' then Decimal Feature
774 Elsif Question datatype is 'B' then Boolean Feature
775 Else Option Feature
776
777 Minimum:
778 For Option Feature, setting minimum as '1' makes the Option feature Mandatory
779 For Numeric Feature setting minimum value validates the user input to be atleast the
780 minimum value. This also makes the numeric feature as mandatory
781 For Boolean Feature , minimum column is not applicable
782
783 Currently we are supporing dependent questions of type LOV or Boolean(created as LOV)
784 OKC content template has a seeded display rule which only displays features if 'Selected'
785 is TRUE. Features with minimum as 0, will not be initially display in UI
786 CZ does not have any API or mechanism to hide Numeric or Boolean features using seeded
787 display rule.
788
789 If feature type is Option feature (LOV or Boolean) then
790 If Question Can be Ordered(independent) then
791 minimum = 1
792 Else
793 -- Question Cannot be Ordered (dependent)
794 minimum = 0
795 Else
796 -- Question datatype is Numeric
797 minimum = NULL
798 End If;
799
800 Maximum:
801 If feature type is Option feature then
802 maximim = 1
803 Else
804 -- Question datatype is Numeric
805 maximim = NULL
806 End If;
807
808
809 */
810 CURSOR csr_question_dtls IS
811 SELECT o.question_id,
812 o.sequence_num,
813 DECODE(q.question_datatype,'N',NULL,
814 DECODE(NVL(o.mandatory_flag,'N'),'Y',1,0)), -- For LOV or Boolean create 1/1 or 0/1
815 DECODE(q.question_datatype,'N',NULL,1), --For Decimal Feature, create MAX as NULL
816 q.value_set_name,
817 DECODE(q.question_datatype,'N',2,
818 0) --FEATURE_TYPE 2:Decimal 0:option
819 FROM okc_xprt_questions_b q,
820 okc_xprt_question_orders o
821 WHERE q.question_id = o.question_id
822 AND o.question_rule_status IN ('ACTIVE','PENDINGPUB')
823 AND o.template_id = p_template_id
824 ORDER BY sequence_num;
825
826 BEGIN
827
828 -- start debug log
829 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
830 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
831 G_MODULE||l_api_name,
832 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
833 END IF;
834
835 x_return_status := G_RET_STS_SUCCESS;
836
837 -- Insert into cz_imp_localized_text
838 OPEN csr_translated_qst;
839 FETCH csr_translated_qst BULK COLLECT INTO TmplQstId_tbl,
840 TmplQstName_tbl,
841 language_tbl,
842 sourceLang_tbl;
843 CLOSE csr_translated_qst;
844
845
846 IF TmplQstName_tbl.COUNT > 0 THEN
847
848 FORALL i IN TmplQstName_tbl.FIRST..TmplQstName_tbl.LAST
849
850 -- Insert into cz_imp_localized_text
851
852 INSERT INTO CZ_IMP_LOCALIZED_TEXTS
853 (
854 LAST_UPDATE_LOGIN,
855 LOCALE_ID,
856 LOCALIZED_STR,
857 INTL_TEXT_ID,
858 CREATION_DATE,
859 LAST_UPDATE_DATE,
860 DELETED_FLAG,
861 EFF_FROM,
862 EFF_TO,
863 CREATED_BY,
864 LAST_UPDATED_BY,
865 SECURITY_MASK,
866 EFF_MASK,
867 CHECKOUT_USER,
868 LANGUAGE,
869 ORIG_SYS_REF,
870 SOURCE_LANG,
871 RUN_ID,
872 REC_STATUS,
873 DISPOSITION,
874 MODEL_ID,
875 FSK_DEVLPROJECT_1_1,
876 MESSAGE,
877 SEEDED_FLAG
878 )
879 VALUES
880 (
881 FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN
882 NULL, -- LOCALE_ID
883 TmplQstName_tbl(i), --LOCALIZED_STR
884 NULL, -- INTL_TEXT_ID
885 SYSDATE, -- CREATION_DATE
886 SYSDATE, -- LAST_UPDATE_DATE
887 '0', -- DELETED_FLAG
888 NULL, -- EFF_FROM
889 NULL, -- EFF_TO
890 FND_GLOBAL.USER_ID, -- CREATED_BY
891 FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
892 NULL, -- SECURITY_MASK
893 NULL, -- EFF_MASK
894 NULL, -- CHECKOUT_USER
895 language_tbl(i), --LANGUAGE
896 G_TEMPLATE_MODEL_FEATURE_OSR||p_org_id||':'||
897 p_intent||':'||
898 p_derived_template_id||':'||
899 TmplQstId_tbl(i), -- ORIG_SYS_REF
900 sourceLang_tbl(i), --SOURCE_LANG
901 G_RUN_ID, -- RUN_ID
902 NULL, -- REC_STATUS
903 NULL, -- DISPOSITION
904 NULL, -- MODEL_ID
905 G_TEMPLATE_MODEL_OSR||p_org_id||':'||
906 p_intent||':'||
907 p_derived_template_id, -- FSK_DEVLPROJECT_1_1
908 NULL, -- MESSAGE
909 NULL -- SEEDED_FLAG
910 );
911
912 END IF ; --TmplQstName_tbl.COUNT > 0
913
914
915 -- insert into cz_ps_nodes
916 OPEN csr_question_dtls;
917 LOOP
918 -- initialize
919 l_question_id := NULL;
920 l_sequence_num := NULL;
921 l_feature_min_val := NULL;
922 l_feature_max_val := NULL;
923 l_value_set_name := NULL;
924 l_feature_type := NULL;
925
926 FETCH csr_question_dtls INTO l_question_id,
927 l_sequence_num,
928 l_feature_min_val,
929 l_feature_max_val,
930 l_value_set_name,
931 l_feature_type;
932 EXIT WHEN csr_question_dtls%NOTFOUND;
933
934
935 -- Populate the cz_imp_ps_nodes record
936
937 l_cz_imp_ps_nodes_rec.PS_NODE_ID:= NULL;
938 l_cz_imp_ps_nodes_rec.DEVL_PROJECT_ID:= NULL;
939 l_cz_imp_ps_nodes_rec.FROM_POPULATOR_ID:= NULL;
940 l_cz_imp_ps_nodes_rec.PROPERTY_BACKPTR:= NULL;
941 l_cz_imp_ps_nodes_rec.ITEM_TYPE_BACKPTR:= NULL;
942 l_cz_imp_ps_nodes_rec.INTL_TEXT_ID:= NULL;
943 l_cz_imp_ps_nodes_rec.SUB_CONS_ID:= NULL;
944 l_cz_imp_ps_nodes_rec.ORGANIZATION_ID:= p_org_id;
945 l_cz_imp_ps_nodes_rec.ITEM_ID:= NULL;
946 l_cz_imp_ps_nodes_rec.EXPLOSION_TYPE:= NULL;
947 l_cz_imp_ps_nodes_rec.NAME:= l_question_id;
948 l_cz_imp_ps_nodes_rec.ORIG_SYS_REF:= G_TEMPLATE_MODEL_FEATURE_OSR||
949 p_org_id||':'||
950 p_intent||':'||
951 p_derived_template_id||':'||
952 l_question_id;
953 l_cz_imp_ps_nodes_rec.RESOURCE_FLAG:= NULL;
954 l_cz_imp_ps_nodes_rec.TOP_ITEM_ID:= 1; -- same value as in cz_imp_devl_projects
955 l_cz_imp_ps_nodes_rec.INITIAL_VALUE:= NULL;
956 l_cz_imp_ps_nodes_rec.PARENT_ID:= NULL;
957 l_cz_imp_ps_nodes_rec.MINIMUM:= l_feature_min_val ; --0 for dependent and 1 for mandatory Questions
958 l_cz_imp_ps_nodes_rec.MAXIMUM:= l_feature_max_val ;
959 l_cz_imp_ps_nodes_rec.PS_NODE_TYPE:= 261; -- feature
960 l_cz_imp_ps_nodes_rec.FEATURE_TYPE:= l_feature_type;
961 l_cz_imp_ps_nodes_rec.PRODUCT_FLAG:= NULL;
962 l_cz_imp_ps_nodes_rec.REFERENCE_ID:= NULL;
963 l_cz_imp_ps_nodes_rec.MULTI_CONFIG_FLAG:= NULL;
964 l_cz_imp_ps_nodes_rec.ORDER_SEQ_FLAG:= NULL;
965 l_cz_imp_ps_nodes_rec.SYSTEM_NODE_FLAG:= NULL;
966 l_cz_imp_ps_nodes_rec.TREE_SEQ:= l_sequence_num+1; -- As Questions must follow the top ref node
967 l_cz_imp_ps_nodes_rec.COUNTED_OPTIONS_FLAG:= '0';
968 l_cz_imp_ps_nodes_rec.UI_OMIT:= '0';
969 l_cz_imp_ps_nodes_rec.UI_SECTION:= 0;
970 l_cz_imp_ps_nodes_rec.BOM_TREATMENT:= NULL;
971 l_cz_imp_ps_nodes_rec.RUN_ID:= G_RUN_ID;
972 l_cz_imp_ps_nodes_rec.REC_STATUS:= NULL;
973 l_cz_imp_ps_nodes_rec.DISPOSITION:= NULL;
974 l_cz_imp_ps_nodes_rec.DELETED_FLAG := 0;
975 l_cz_imp_ps_nodes_rec.EFF_FROM:= NULL;
976 l_cz_imp_ps_nodes_rec.EFF_TO:= NULL;
977 l_cz_imp_ps_nodes_rec.EFF_MASK:= NULL;
978 l_cz_imp_ps_nodes_rec.USER_STR01:= NULL;
979 l_cz_imp_ps_nodes_rec.USER_STR02:= NULL;
980 l_cz_imp_ps_nodes_rec.USER_STR03:= NULL;
981 l_cz_imp_ps_nodes_rec.USER_STR04:= NULL;
982 l_cz_imp_ps_nodes_rec.USER_NUM01:= NULL;
983 l_cz_imp_ps_nodes_rec.USER_NUM02:= NULL;
984 l_cz_imp_ps_nodes_rec.USER_NUM03:= NULL;
985 l_cz_imp_ps_nodes_rec.USER_NUM04:= NULL;
986 l_cz_imp_ps_nodes_rec.CHECKOUT_USER:= NULL;
987 l_cz_imp_ps_nodes_rec.CREATION_DATE:= SYSDATE;
988 l_cz_imp_ps_nodes_rec.LAST_UPDATE_DATE:= SYSDATE;
989 l_cz_imp_ps_nodes_rec.CREATED_BY:= FND_GLOBAL.USER_ID;
990 l_cz_imp_ps_nodes_rec.LAST_UPDATED_BY:= FND_GLOBAL.USER_ID;
991 l_cz_imp_ps_nodes_rec.SECURITY_MASK:= NULL;
992 l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_1:= G_TEMPLATE_MODEL_FEATURE_OSR||
993 p_org_id||':'||
994 p_intent||':'||
995 p_derived_template_id||':'||
996 l_question_id;
997 l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_EXT:= NULL;
998 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_1:= NULL;
999 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_EXT:= NULL;
1000 l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_1:= G_TEMPLATE_MODEL_TOPNODE_OSR||
1001 p_org_id||':'||
1002 p_intent||':'||
1003 p_derived_template_id;
1004 l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_EXT:= NULL;
1005 l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_1:= NULL;
1006 l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_EXT:= NULL;
1007 l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_1:= G_TEMPLATE_MODEL_OSR||
1008 p_org_id||':'||
1009 p_intent||':'||
1010 p_derived_template_id;
1011 l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_EXT:= NULL;
1012 l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_ID:= NULL;
1013 l_cz_imp_ps_nodes_rec.COMPONENT_CODE:= NULL;
1014 l_cz_imp_ps_nodes_rec.PLAN_LEVEL:= 1; --Plan Level for feature:1
1015 l_cz_imp_ps_nodes_rec.BOM_ITEM_TYPE:= NULL;
1016 l_cz_imp_ps_nodes_rec.SO_ITEM_TYPE_CODE:= NULL;
1017 l_cz_imp_ps_nodes_rec.MINIMUM_SELECTED:= NULL;
1018 l_cz_imp_ps_nodes_rec.MAXIMUM_SELECTED:= NULL;
1019 l_cz_imp_ps_nodes_rec.BOM_REQUIRED:= NULL;
1020 l_cz_imp_ps_nodes_rec.MUTUALLY_EXCLUSIVE_OPTIONS:= NULL;
1021 l_cz_imp_ps_nodes_rec.OPTIONAL:= NULL;
1022 l_cz_imp_ps_nodes_rec.FSK_EXPLNODE_1_1:= NULL;
1023 l_cz_imp_ps_nodes_rec.FSK_PSNODE_6_1:= NULL;
1024 l_cz_imp_ps_nodes_rec.EFFECTIVE_FROM:= OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_BEGIN;
1025 l_cz_imp_ps_nodes_rec.EFFECTIVE_UNTIL:= OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_END;
1026 l_cz_imp_ps_nodes_rec.EFFECTIVE_USAGE_MASK:= NULL;
1027 l_cz_imp_ps_nodes_rec.EFFECTIVITY_SET_ID:= NULL;
1028 l_cz_imp_ps_nodes_rec.FSK_EFFSET_7_1:= NULL;
1029 l_cz_imp_ps_nodes_rec.DECIMAL_QTY_FLAG:= 0; -- 0 for all nodes
1030 l_cz_imp_ps_nodes_rec.QUOTEABLE_FLAG:= NULL;
1031 l_cz_imp_ps_nodes_rec.PRIMARY_UOM_CODE:= NULL;
1032 l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_PATH:= NULL; -- Must be NULL
1033 l_cz_imp_ps_nodes_rec.BOM_SORT_ORDER:= NULL;
1034 l_cz_imp_ps_nodes_rec.IB_TRACKABLE:= NULL;
1035 l_cz_imp_ps_nodes_rec.LAST_UPDATE_LOGIN:= FND_GLOBAL.LOGIN_ID;
1036 l_cz_imp_ps_nodes_rec.INITIAL_NUM_VALUE:= NULL;
1037 l_cz_imp_ps_nodes_rec.SRC_APPLICATION_ID:= G_APPLICATION_ID;
1038 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_2:= NULL;
1039 l_cz_imp_ps_nodes_rec.INSTANTIABLE_FLAG:= NULL;
1040 l_cz_imp_ps_nodes_rec.DISPLAY_IN_SUMMARY_FLAG:= NULL;
1041
1042 -- insert features for Template Model into cz_imp_ps_nodes
1043
1044 INSERT INTO cz_imp_ps_nodes
1045 (
1046 PS_NODE_ID,
1047 DEVL_PROJECT_ID,
1048 FROM_POPULATOR_ID,
1049 PROPERTY_BACKPTR,
1050 ITEM_TYPE_BACKPTR,
1051 INTL_TEXT_ID,
1052 SUB_CONS_ID,
1053 ORGANIZATION_ID,
1054 ITEM_ID,
1055 EXPLOSION_TYPE,
1056 NAME,
1057 ORIG_SYS_REF,
1058 RESOURCE_FLAG,
1059 TOP_ITEM_ID,
1060 INITIAL_VALUE,
1061 PARENT_ID,
1062 MINIMUM,
1063 MAXIMUM,
1064 PS_NODE_TYPE,
1065 FEATURE_TYPE,
1066 PRODUCT_FLAG,
1067 REFERENCE_ID,
1068 MULTI_CONFIG_FLAG,
1069 ORDER_SEQ_FLAG,
1070 SYSTEM_NODE_FLAG,
1071 TREE_SEQ,
1072 COUNTED_OPTIONS_FLAG,
1073 UI_OMIT,
1074 UI_SECTION,
1075 BOM_TREATMENT,
1076 RUN_ID,
1077 REC_STATUS,
1078 DISPOSITION,
1079 DELETED_FLAG ,
1080 EFF_FROM,
1081 EFF_TO,
1082 EFF_MASK,
1083 USER_STR01,
1084 USER_STR02,
1085 USER_STR03,
1086 USER_STR04,
1087 USER_NUM01,
1088 USER_NUM02,
1089 USER_NUM03,
1090 USER_NUM04,
1091 CHECKOUT_USER,
1092 CREATION_DATE,
1093 LAST_UPDATE_DATE,
1094 CREATED_BY,
1095 LAST_UPDATED_BY,
1096 SECURITY_MASK,
1097 FSK_INTLTEXT_1_1,
1098 FSK_INTLTEXT_1_EXT,
1099 FSK_ITEMMASTER_2_1,
1100 FSK_ITEMMASTER_2_EXT,
1101 FSK_PSNODE_3_1,
1102 FSK_PSNODE_3_EXT,
1103 FSK_PSNODE_4_1,
1104 FSK_PSNODE_4_EXT,
1105 FSK_DEVLPROJECT_5_1,
1106 FSK_DEVLPROJECT_5_EXT,
1107 COMPONENT_SEQUENCE_ID,
1108 COMPONENT_CODE,
1109 PLAN_LEVEL,
1110 BOM_ITEM_TYPE,
1111 SO_ITEM_TYPE_CODE,
1112 MINIMUM_SELECTED,
1113 MAXIMUM_SELECTED,
1114 BOM_REQUIRED,
1115 MUTUALLY_EXCLUSIVE_OPTIONS,
1116 OPTIONAL,
1117 FSK_EXPLNODE_1_1,
1118 FSK_PSNODE_6_1,
1119 EFFECTIVE_FROM,
1120 EFFECTIVE_UNTIL,
1121 EFFECTIVE_USAGE_MASK,
1122 EFFECTIVITY_SET_ID,
1123 FSK_EFFSET_7_1,
1124 DECIMAL_QTY_FLAG,
1125 QUOTEABLE_FLAG,
1126 PRIMARY_UOM_CODE,
1127 COMPONENT_SEQUENCE_PATH,
1128 BOM_SORT_ORDER,
1129 IB_TRACKABLE,
1130 LAST_UPDATE_LOGIN,
1131 INITIAL_NUM_VALUE,
1132 SRC_APPLICATION_ID,
1133 FSK_ITEMMASTER_2_2,
1134 INSTANTIABLE_FLAG,
1135 DISPLAY_IN_SUMMARY_FLAG
1136 )
1137 VALUES
1138 (
1139 l_cz_imp_ps_nodes_rec.PS_NODE_ID,
1140 l_cz_imp_ps_nodes_rec.DEVL_PROJECT_ID,
1141 l_cz_imp_ps_nodes_rec.FROM_POPULATOR_ID,
1142 l_cz_imp_ps_nodes_rec.PROPERTY_BACKPTR,
1143 l_cz_imp_ps_nodes_rec.ITEM_TYPE_BACKPTR,
1144 l_cz_imp_ps_nodes_rec.INTL_TEXT_ID,
1145 l_cz_imp_ps_nodes_rec.SUB_CONS_ID,
1146 l_cz_imp_ps_nodes_rec.ORGANIZATION_ID,
1147 l_cz_imp_ps_nodes_rec.ITEM_ID,
1148 l_cz_imp_ps_nodes_rec.EXPLOSION_TYPE,
1149 l_cz_imp_ps_nodes_rec.NAME,
1150 l_cz_imp_ps_nodes_rec.ORIG_SYS_REF,
1151 l_cz_imp_ps_nodes_rec.RESOURCE_FLAG,
1152 l_cz_imp_ps_nodes_rec.TOP_ITEM_ID,
1153 l_cz_imp_ps_nodes_rec.INITIAL_VALUE,
1154 l_cz_imp_ps_nodes_rec.PARENT_ID,
1155 l_cz_imp_ps_nodes_rec.MINIMUM,
1156 l_cz_imp_ps_nodes_rec.MAXIMUM,
1157 l_cz_imp_ps_nodes_rec.PS_NODE_TYPE,
1158 l_cz_imp_ps_nodes_rec.FEATURE_TYPE,
1159 l_cz_imp_ps_nodes_rec.PRODUCT_FLAG,
1160 l_cz_imp_ps_nodes_rec.REFERENCE_ID,
1161 l_cz_imp_ps_nodes_rec.MULTI_CONFIG_FLAG,
1162 l_cz_imp_ps_nodes_rec.ORDER_SEQ_FLAG,
1163 l_cz_imp_ps_nodes_rec.SYSTEM_NODE_FLAG,
1164 l_cz_imp_ps_nodes_rec.TREE_SEQ,
1165 l_cz_imp_ps_nodes_rec.COUNTED_OPTIONS_FLAG,
1166 l_cz_imp_ps_nodes_rec.UI_OMIT,
1167 l_cz_imp_ps_nodes_rec.UI_SECTION,
1168 l_cz_imp_ps_nodes_rec.BOM_TREATMENT,
1169 l_cz_imp_ps_nodes_rec.RUN_ID,
1170 l_cz_imp_ps_nodes_rec.REC_STATUS,
1171 l_cz_imp_ps_nodes_rec.DISPOSITION,
1172 l_cz_imp_ps_nodes_rec.DELETED_FLAG ,
1173 l_cz_imp_ps_nodes_rec.EFF_FROM,
1174 l_cz_imp_ps_nodes_rec.EFF_TO,
1175 l_cz_imp_ps_nodes_rec.EFF_MASK,
1176 l_cz_imp_ps_nodes_rec.USER_STR01,
1177 l_cz_imp_ps_nodes_rec.USER_STR02,
1178 l_cz_imp_ps_nodes_rec.USER_STR03,
1179 l_cz_imp_ps_nodes_rec.USER_STR04,
1180 l_cz_imp_ps_nodes_rec.USER_NUM01,
1181 l_cz_imp_ps_nodes_rec.USER_NUM02,
1182 l_cz_imp_ps_nodes_rec.USER_NUM03,
1183 l_cz_imp_ps_nodes_rec.USER_NUM04,
1184 l_cz_imp_ps_nodes_rec.CHECKOUT_USER,
1185 l_cz_imp_ps_nodes_rec.CREATION_DATE,
1186 l_cz_imp_ps_nodes_rec.LAST_UPDATE_DATE,
1187 l_cz_imp_ps_nodes_rec.CREATED_BY,
1188 l_cz_imp_ps_nodes_rec.LAST_UPDATED_BY,
1189 l_cz_imp_ps_nodes_rec.SECURITY_MASK,
1190 l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_1,
1191 l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_EXT,
1192 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_1,
1193 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_EXT,
1194 l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_1,
1195 l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_EXT,
1196 l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_1,
1197 l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_EXT,
1198 l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_1,
1199 l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_EXT,
1200 l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_ID,
1201 l_cz_imp_ps_nodes_rec.COMPONENT_CODE,
1202 l_cz_imp_ps_nodes_rec.PLAN_LEVEL,
1203 l_cz_imp_ps_nodes_rec.BOM_ITEM_TYPE,
1204 l_cz_imp_ps_nodes_rec.SO_ITEM_TYPE_CODE,
1205 l_cz_imp_ps_nodes_rec.MINIMUM_SELECTED,
1206 l_cz_imp_ps_nodes_rec.MAXIMUM_SELECTED,
1207 l_cz_imp_ps_nodes_rec.BOM_REQUIRED,
1208 l_cz_imp_ps_nodes_rec.MUTUALLY_EXCLUSIVE_OPTIONS,
1209 l_cz_imp_ps_nodes_rec.OPTIONAL,
1210 l_cz_imp_ps_nodes_rec.FSK_EXPLNODE_1_1,
1211 l_cz_imp_ps_nodes_rec.FSK_PSNODE_6_1,
1212 l_cz_imp_ps_nodes_rec.EFFECTIVE_FROM,
1213 l_cz_imp_ps_nodes_rec.EFFECTIVE_UNTIL,
1214 l_cz_imp_ps_nodes_rec.EFFECTIVE_USAGE_MASK,
1215 l_cz_imp_ps_nodes_rec.EFFECTIVITY_SET_ID,
1216 l_cz_imp_ps_nodes_rec.FSK_EFFSET_7_1,
1217 l_cz_imp_ps_nodes_rec.DECIMAL_QTY_FLAG,
1218 l_cz_imp_ps_nodes_rec.QUOTEABLE_FLAG,
1219 l_cz_imp_ps_nodes_rec.PRIMARY_UOM_CODE,
1220 l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_PATH,
1221 l_cz_imp_ps_nodes_rec.BOM_SORT_ORDER,
1222 l_cz_imp_ps_nodes_rec.IB_TRACKABLE,
1223 l_cz_imp_ps_nodes_rec.LAST_UPDATE_LOGIN,
1224 l_cz_imp_ps_nodes_rec.INITIAL_NUM_VALUE,
1225 l_cz_imp_ps_nodes_rec.SRC_APPLICATION_ID,
1226 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_2,
1227 l_cz_imp_ps_nodes_rec.INSTANTIABLE_FLAG,
1228 l_cz_imp_ps_nodes_rec.DISPLAY_IN_SUMMARY_FLAG
1229 );
1230
1231 /*
1232 All Values for the question features of type Option Features would be created as Options
1233 below the question
1234 parameters: p_question_id, p_value_set_id, p_derived_template_id, p_org_id, p_intent
1235 */
1236 IF l_feature_type = 0 THEN
1237
1238 -- debug log
1239 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1240 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1241 G_MODULE||l_api_name,
1242 '500: Creating Options For Question Id '||l_question_id);
1243 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1244 G_MODULE||l_api_name,
1245 '500: Value Set Name '||l_value_set_name);
1246 END IF;
1247
1248 create_template_options
1249 (
1250 p_question_id => l_question_id,
1251 p_value_set_id => okc_xprt_util_pvt.get_value_set_id(l_value_set_name),
1252 p_derived_template_id => p_derived_template_id,
1253 p_intent => p_intent,
1254 p_org_id => p_org_id,
1255 x_return_status => x_return_status,
1256 x_msg_data => x_msg_data,
1257 x_msg_count => x_msg_count
1258 );
1259 END IF; -- create options for non numeric features
1260
1261
1262
1263 END LOOP; -- created features for all questions
1264 CLOSE csr_question_dtls;
1265
1266
1267 -- Standard call to get message count and if count is 1, get message info.
1268 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1269
1270
1271 -- end debug log
1272 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1273 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1274 G_MODULE||l_api_name,
1275 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1276 END IF;
1277
1278 EXCEPTION
1279 WHEN OTHERS THEN
1280 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1281 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1282 G_MODULE||l_api_name,
1283 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1284 END IF;
1285
1286 x_return_status := G_RET_STS_UNEXP_ERROR ;
1287
1288 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1289 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1290 END IF;
1291
1292 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1293
1294
1295 END create_template_feature;
1296
1297 /*====================================================================+
1298 Procedure Name : create_template_options
1299 Description : This is a private API that creates the Template Model
1300 Options.
1301 The list of possible values for the questions are created as
1302 options below the question feature
1303 Parameters:
1304 p_question_id - Question Id of the question
1305 p_value_set_id - Value set id of the question response
1306 p_derived_template_id - Parent template id or template id
1307 p_intent - Template Intent
1308 p_org_id - Org Id of the template
1309
1310 +====================================================================*/
1311
1312 PROCEDURE create_template_options
1313 (
1314 p_question_id IN NUMBER,
1315 p_value_set_id IN NUMBER,
1316 p_derived_template_id IN NUMBER,
1317 p_intent IN VARCHAR2,
1318 p_org_id IN NUMBER,
1319 x_return_status OUT NOCOPY VARCHAR2,
1320 x_msg_data OUT NOCOPY VARCHAR2,
1321 x_msg_count OUT NOCOPY NUMBER
1322 ) IS
1323
1324 -- Check the Value Set Type
1325 -- Support Valuesets of Type Table (F) and Independent (I)
1326 CURSOR csr_value_set_type IS
1327 SELECT validation_type
1328 FROM fnd_flex_value_sets
1329 WHERE flex_value_set_id = p_value_set_id;
1330
1331 -- Create Dynamic sql for the valueset for Table
1332 CURSOR csr_value_set_tab IS
1333 SELECT application_table_name,
1334 value_column_name,
1335 id_column_name,
1336 additional_where_clause
1337 FROM fnd_flex_validation_tables
1338 WHERE flex_value_set_id = p_value_set_id;
1339
1340 -- SQL for Valueset type Independent
1341 CURSOR csr_value_set_ind IS
1342 SELECT NVL(description, flex_value_meaning),
1343 flex_value_id, -- flex_value,
1344 rownum
1345 FROM fnd_flex_values_vl
1346 WHERE flex_value_set_id = p_value_set_id
1347 AND enabled_flag = 'Y'
1348 AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE) AND NVL(end_date_active,SYSDATE+1);
1349
1350 -- Question Name to be displayed in case of error
1351 CURSOR csr_qst_name IS
1352 SELECT question_name
1353 FROM okc_xprt_questions_tl
1354 WHERE question_id = p_question_id
1355 AND language = USERENV('LANG');
1356
1357 -- get the list of installed languages
1358 -- and create records in cz_imp_localized_texts table
1359
1360 CURSOR csr_installed_languages IS
1361 SELECT L.LANGUAGE_CODE
1362 FROM FND_LANGUAGES L
1363 WHERE L.INSTALLED_FLAG IN ('I', 'B');
1364
1365
1366 l_api_name CONSTANT VARCHAR2(30) := 'create_template_options';
1367
1368 TYPE SeqNoList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1369 TYPE NameList IS TABLE OF fnd_flex_validation_tables.value_column_name%TYPE INDEX BY BINARY_INTEGER;
1370 TYPE IdList IS TABLE OF fnd_flex_validation_tables.id_column_name%TYPE INDEX BY BINARY_INTEGER;
1371
1372 i number;
1373 tempSeqNo NUMBER;
1374 tempName fnd_flex_validation_tables.value_column_name%TYPE ;
1375 tempId fnd_flex_validation_tables.id_column_name%TYPE ;
1376
1377 SeqNoList_tbl SeqNoList;
1378 NameList_tbl NameList;
1379 IdList_tbl IdList;
1380
1381
1382 l_table_name fnd_flex_validation_tables.application_table_name%TYPE;
1383 l_name_col fnd_flex_validation_tables.value_column_name%TYPE;
1384 l_id_col fnd_flex_validation_tables.id_column_name%TYPE;
1385 l_additional_where_clause fnd_flex_validation_tables.additional_where_clause%TYPE;
1386 l_sql_stmt LONG;
1387 l_sequence_sql_stmt LONG;
1388 l_error_message VARCHAR2(4000);
1389 l_valueset_type fnd_flex_value_sets.validation_type%TYPE;
1390 l_question_name okc_xprt_questions_tl.question_name%TYPE;
1391 l_language FND_LANGUAGES.LANGUAGE_CODE%TYPE;
1392
1393 TYPE cur_typ IS REF CURSOR;
1394 c_cursor cur_typ;
1395
1396
1397 BEGIN
1398
1399 -- start debug log
1400 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1401 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1402 G_MODULE||l_api_name,
1403 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1404 END IF;
1405
1406 x_return_status := G_RET_STS_SUCCESS;
1407
1408 -- Check the Valueset Type
1409 OPEN csr_value_set_type;
1410 FETCH csr_value_set_type INTO l_valueset_type;
1411 CLOSE csr_value_set_type;
1412
1413 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1414 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1415 G_MODULE||l_api_name,
1416 '101: ValueSet Id : '||p_value_set_id);
1417 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1418 G_MODULE||l_api_name,
1419 '101: Valueset Type : '||l_valueset_type);
1420 END IF;
1421
1422
1423 -- Depending on the ValueSet Type open the cursor
1424 IF l_valueset_type = 'F' THEN
1425 -- Valueset is Table
1426
1427 -- Build the dynamic SQL for the valueset
1428 OPEN csr_value_set_tab;
1429 FETCH csr_value_set_tab INTO l_table_name, l_name_col, l_id_col, l_additional_where_clause;
1430 CLOSE csr_value_set_tab;
1431
1432 l_sql_stmt := 'SELECT '||l_name_col||' , '||l_id_col||
1433 ' FROM '||l_table_name||' '||
1434 l_additional_where_clause ;
1435
1436 l_sequence_sql_stmt := 'SELECT rownum FROM ( '||l_sql_stmt||' )' ;
1437
1438 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1439 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1440 G_MODULE||l_api_name,
1441 '150: l_table_name '||l_table_name);
1442 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1443 G_MODULE||l_api_name,
1444 '150: l_name_col '||l_name_col);
1445 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1446 G_MODULE||l_api_name,
1447 '150: l_id_col '||l_id_col);
1448 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1449 G_MODULE||l_api_name,
1450 '150: l_additional_where_clause '||l_additional_where_clause);
1451 END IF;
1452
1453 -- Get the values to be imported as Options Under the questions
1454
1455 -- execute the dynamic sql
1456 BEGIN
1457 -- EXECUTE IMMEDIATE l_sql_stmt
1458 -- BULK COLLECT INTO NameList_tbl, IdList_tbl ;
1459
1460 i:=0;
1461 OPEN c_cursor FOR l_sql_stmt;
1462 LOOP
1463 FETCH c_cursor INTO tempName, tempId;
1464 EXIT WHEN c_cursor%NOTFOUND;
1465
1466 -- process row here
1467 NameList_tbl(i) := tempName;
1468 IdList_tbl(i) := tempId;
1469 i:=i+1;
1470 END LOOP;
1471 CLOSE c_cursor;
1472
1473 EXCEPTION
1474 WHEN OTHERS THEN
1475 -- Get Question Name
1476 OPEN csr_qst_name;
1477 FETCH csr_qst_name INTO l_question_name;
1478 CLOSE csr_qst_name;
1479 -- Get the error details
1480 FND_MESSAGE.set_name('OKC','OKC_XPRT_QST_VSET_ERROR');
1481 FND_MESSAGE.set_token('QUESTION_NAME',l_question_name);
1482 FND_MESSAGE.set_token('SQL_ERR',SQLERRM);
1483 l_error_message := FND_MESSAGE.get;
1484 -- Write to Concurrent Log File
1485 fnd_file.put_line(FND_FILE.LOG,l_error_message);
1486 -- Write to Debug Log
1487 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1488 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1489 G_MODULE||l_api_name,
1490 '110: Error In Value Set Dynamic Sql for Question : '||l_question_name);
1491 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1492 G_MODULE||l_api_name,
1493 '110: Error Message : ' ||l_error_message);
1494 END IF;
1495 -- Raise Error Exception
1496 x_return_status := G_RET_STS_ERROR ;
1497 RAISE FND_API.G_EXC_ERROR;
1498 END; -- Valueset Type F and SQL
1499
1500 BEGIN
1501 -- get the sequence numbers for the options
1502 -- EXECUTE IMMEDIATE l_sequence_sql_stmt
1503 -- BULK COLLECT INTO SeqNoList_tbl ;
1504
1505 i:=0;
1506 OPEN c_cursor FOR l_sequence_sql_stmt;
1507 LOOP
1508 FETCH c_cursor INTO tempSeqNo;
1509 EXIT WHEN c_cursor%NOTFOUND;
1510
1511 -- process row here
1512 SeqNoList_tbl(i) := tempSeqNo;
1513 i:=i+1;
1514 END LOOP;
1515 CLOSE c_cursor;
1516
1517
1518 EXCEPTION
1519 WHEN OTHERS THEN
1520 -- Get Question Name
1521 OPEN csr_qst_name;
1522 FETCH csr_qst_name INTO l_question_name;
1523 CLOSE csr_qst_name;
1524 -- Get the error details
1525 FND_MESSAGE.set_name('OKC','OKC_XPRT_QST_VSET_SEQ_ERROR');
1526 FND_MESSAGE.set_token('QUESTION_NAME',l_question_name);
1527 FND_MESSAGE.set_token('SQL_ERR',SQLERRM);
1528 l_error_message := FND_MESSAGE.get;
1529 -- Write to Concurrent Log File
1530 fnd_file.put_line(FND_FILE.LOG,l_error_message);
1531 -- Write to Debug Log
1532 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1533 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1534 G_MODULE||l_api_name,
1535 '110: Error In ValueSet Sql for Question Sequence: '||l_question_name);
1536 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1537 G_MODULE||l_api_name,
1538 '110: Error Message : ' ||l_error_message);
1539 END IF;
1540 -- Raise Error Exception
1541 x_return_status := G_RET_STS_ERROR ;
1542 RAISE FND_API.G_EXC_ERROR;
1543 END; -- ValueSet Type F and Sequence
1544
1545 ELSIF l_valueset_type = 'I' THEN
1546 -- validation type is Independent
1547
1548 OPEN csr_value_set_ind;
1549 FETCH csr_value_set_ind BULK COLLECT INTO NameList_tbl, IdList_tbl, SeqNoList_tbl;
1550 CLOSE csr_value_set_ind;
1551
1552 -- Check if the Valueset has atleast 1 value else error
1553 IF NameList_tbl.COUNT = 0 THEN
1554 -- Get Question Name
1555 OPEN csr_qst_name;
1556 FETCH csr_qst_name INTO l_question_name;
1557 CLOSE csr_qst_name;
1558 -- Get the error details
1559 FND_MESSAGE.set_name('OKC','OKC_XPRT_QST_VSET_NOVAL');
1560 FND_MESSAGE.set_token('QUESTION_NAME',l_question_name);
1561 l_error_message := FND_MESSAGE.get;
1562 -- Write to Concurrent Log File
1563 fnd_file.put_line(FND_FILE.LOG,l_error_message);
1564 -- Write to 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 '110: No Values defined for ValueSet in Question : '||l_question_name);
1569 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1570 G_MODULE||l_api_name,
1571 '110: Error Message : ' ||l_error_message);
1572 END IF;
1573 -- Raise Error Exception
1574 x_return_status := G_RET_STS_ERROR ;
1575 RAISE FND_API.G_EXC_ERROR;
1576 END IF; -- Valueset has no values
1577
1578 ELSE
1579 -- Invalid Valueset type
1580
1581 -- Get Question Name
1582 OPEN csr_qst_name;
1583 FETCH csr_qst_name INTO l_question_name;
1584 CLOSE csr_qst_name;
1585
1586 -- Get the error details
1587 FND_MESSAGE.set_name('OKC','OKC_XPRT_QST_VSET_INVALID');
1588 FND_MESSAGE.set_token('QUESTION_NAME',l_question_name);
1589 l_error_message := FND_MESSAGE.get;
1590 -- Write to Concurrent Log File
1591 fnd_file.put_line(FND_FILE.LOG,l_error_message);
1592 -- Write to Debug Log
1593 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1594 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1595 G_MODULE||l_api_name,
1596 '110: No Values defined for ValueSet in Question : '||l_question_name);
1597 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1598 G_MODULE||l_api_name,
1599 '110: Error Message : ' ||l_error_message);
1600 END IF;
1601 -- Raise Error Exception
1602 x_return_status := G_RET_STS_ERROR ;
1603 RAISE FND_API.G_EXC_ERROR;
1604
1605 END IF; -- Valueset Type
1606
1607
1608
1609 IF NameList_tbl.COUNT > 0 THEN
1610
1611 OPEN csr_installed_languages;
1612 LOOP
1613 FETCH csr_installed_languages INTO l_language;
1614 EXIT WHEN csr_installed_languages%NOTFOUND;
1615
1616 FORALL i IN NameList_tbl.FIRST..NameList_tbl.LAST
1617
1618 -- Insert into cz_imp_localized_text for each installed language
1619
1620 INSERT INTO CZ_IMP_LOCALIZED_TEXTS
1621 (
1622 LAST_UPDATE_LOGIN,
1623 LOCALE_ID,
1624 LOCALIZED_STR,
1625 INTL_TEXT_ID,
1626 CREATION_DATE,
1627 LAST_UPDATE_DATE,
1628 DELETED_FLAG,
1629 EFF_FROM,
1630 EFF_TO,
1631 CREATED_BY,
1632 LAST_UPDATED_BY,
1633 SECURITY_MASK,
1634 EFF_MASK,
1635 CHECKOUT_USER,
1636 LANGUAGE,
1637 ORIG_SYS_REF,
1638 SOURCE_LANG,
1639 RUN_ID,
1640 REC_STATUS,
1641 DISPOSITION,
1642 MODEL_ID,
1643 FSK_DEVLPROJECT_1_1,
1644 MESSAGE,
1645 SEEDED_FLAG
1646 )
1647 VALUES
1648 (
1649 FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN
1650 NULL, -- LOCALE_ID
1651 NameList_tbl(i), --LOCALIZED_STR
1652 NULL, -- INTL_TEXT_ID
1653 SYSDATE, -- CREATION_DATE
1654 SYSDATE, -- LAST_UPDATE_DATE
1655 '0', -- DELETED_FLAG
1656 NULL, -- EFF_FROM
1657 NULL, -- EFF_TO
1658 FND_GLOBAL.USER_ID, -- CREATED_BY
1659 FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
1660 NULL, -- SECURITY_MASK
1661 NULL, -- EFF_MASK
1662 NULL, -- CHECKOUT_USER
1663 l_language, --LANGUAGE
1664 G_TEMPLATE_MODEL_OPTION_OSR||
1665 p_org_id||':'||
1666 p_intent||':'||
1667 p_derived_template_id||':'||
1668 p_question_id||':'||
1669 IdList_tbl(i), --ORIG_SYS_REF
1670 USERENV('LANG'), --SOURCE_LANG
1671 G_RUN_ID, -- RUN_ID
1672 NULL, -- REC_STATUS
1673 NULL, -- DISPOSITION
1674 NULL, -- MODEL_ID
1675 G_TEMPLATE_MODEL_OSR||
1676 p_org_id||':'||
1677 p_intent||':'||
1678 p_derived_template_id , -- FSK_DEVLPROJECT_1_1
1679 NULL, -- MESSAGE
1680 NULL -- SEEDED_FLAG
1681 );
1682
1683 END LOOP; -- for all installed languages
1684 CLOSE csr_installed_languages;
1685
1686
1687 FORALL i IN NameList_tbl.FIRST..NameList_tbl.LAST
1688
1689 -- Insert into cz_imp_ps_nodes
1690
1691 INSERT INTO cz_imp_ps_nodes
1692 (
1693 PS_NODE_ID,
1694 DEVL_PROJECT_ID,
1695 FROM_POPULATOR_ID,
1696 PROPERTY_BACKPTR,
1697 ITEM_TYPE_BACKPTR,
1698 INTL_TEXT_ID,
1699 SUB_CONS_ID,
1700 ORGANIZATION_ID,
1701 ITEM_ID,
1702 EXPLOSION_TYPE,
1703 NAME,
1704 ORIG_SYS_REF,
1705 RESOURCE_FLAG,
1706 TOP_ITEM_ID,
1707 INITIAL_VALUE,
1708 PARENT_ID,
1709 MINIMUM,
1710 MAXIMUM,
1711 PS_NODE_TYPE,
1712 FEATURE_TYPE,
1713 PRODUCT_FLAG,
1714 REFERENCE_ID,
1715 MULTI_CONFIG_FLAG,
1716 ORDER_SEQ_FLAG,
1717 SYSTEM_NODE_FLAG,
1718 TREE_SEQ,
1719 COUNTED_OPTIONS_FLAG,
1720 UI_OMIT,
1721 UI_SECTION,
1722 BOM_TREATMENT,
1723 RUN_ID,
1724 REC_STATUS,
1725 DISPOSITION,
1726 DELETED_FLAG ,
1727 EFF_FROM,
1728 EFF_TO,
1729 EFF_MASK,
1730 USER_STR01,
1731 USER_STR02,
1732 USER_STR03,
1733 USER_STR04,
1734 USER_NUM01,
1735 USER_NUM02,
1736 USER_NUM03,
1737 USER_NUM04,
1738 CHECKOUT_USER,
1739 CREATION_DATE,
1740 LAST_UPDATE_DATE,
1741 CREATED_BY,
1742 LAST_UPDATED_BY,
1743 SECURITY_MASK,
1744 FSK_INTLTEXT_1_1,
1745 FSK_INTLTEXT_1_EXT,
1746 FSK_ITEMMASTER_2_1,
1747 FSK_ITEMMASTER_2_EXT,
1748 FSK_PSNODE_3_1,
1749 FSK_PSNODE_3_EXT,
1750 FSK_PSNODE_4_1,
1751 FSK_PSNODE_4_EXT,
1752 FSK_DEVLPROJECT_5_1,
1753 FSK_DEVLPROJECT_5_EXT,
1754 COMPONENT_SEQUENCE_ID,
1755 COMPONENT_CODE,
1756 PLAN_LEVEL,
1757 BOM_ITEM_TYPE,
1758 SO_ITEM_TYPE_CODE,
1759 MINIMUM_SELECTED,
1760 MAXIMUM_SELECTED,
1761 BOM_REQUIRED,
1762 MUTUALLY_EXCLUSIVE_OPTIONS,
1763 OPTIONAL,
1764 FSK_EXPLNODE_1_1,
1765 FSK_PSNODE_6_1,
1766 EFFECTIVE_FROM,
1767 EFFECTIVE_UNTIL,
1768 EFFECTIVE_USAGE_MASK,
1769 EFFECTIVITY_SET_ID,
1770 FSK_EFFSET_7_1,
1771 DECIMAL_QTY_FLAG,
1772 QUOTEABLE_FLAG,
1773 PRIMARY_UOM_CODE,
1774 COMPONENT_SEQUENCE_PATH,
1775 BOM_SORT_ORDER,
1776 IB_TRACKABLE,
1777 LAST_UPDATE_LOGIN,
1778 INITIAL_NUM_VALUE,
1779 SRC_APPLICATION_ID,
1780 FSK_ITEMMASTER_2_2,
1781 INSTANTIABLE_FLAG,
1782 DISPLAY_IN_SUMMARY_FLAG
1783 )
1784 VALUES
1785 (
1786 NULL, --PS_NODE_ID,
1787 NULL, --DEVL_PROJECT_ID,
1788 NULL, --FROM_POPULATOR_ID,
1789 NULL, --PROPERTY_BACKPTR,
1790 NULL, --ITEM_TYPE_BACKPTR,
1791 NULL, --INTL_TEXT_ID,
1792 NULL, --SUB_CONS_ID,
1793 p_org_id, --ORGANIZATION_ID
1794 NULL, --ITEM_ID,
1795 NULL, --EXPLOSION_TYPE,
1796 IdList_tbl(i), --NAME
1797 G_TEMPLATE_MODEL_OPTION_OSR||
1798 p_org_id||':'||
1799 p_intent||':'||
1800 p_derived_template_id||':'||
1801 p_question_id||':'||
1802 IdList_tbl(i), --ORIG_SYS_REF
1803 NULL, --RESOURCE_FLAG
1804 1, --TOP_ITEM_ID -- same value as in cz_imp_devl_projects
1805 NULL, --INITIAL_VALUE
1806 NULL, --PARENT_ID
1807 1, --MINIMUM
1808 1, --MAXIMUM
1809 262, --PS_NODE_TYPE 262:Option
1810 NULL, --FEATURE_TYPE
1811 NULL, --PRODUCT_FLAG,
1812 NULL, --REFERENCE_ID,
1813 NULL, --MULTI_CONFIG_FLAG,
1814 NULL, --ORDER_SEQ_FLAG,
1815 NULL, --SYSTEM_NODE_FLAG
1816 SeqNoList_tbl(i), --TREE_SEQ
1817 '0', --COUNTED_OPTIONS_FLAG
1818 '0', --UI_OMIT
1819 0, --UI_SECTION
1820 NULL, --BOM_TREATMENT,
1821 G_RUN_ID, --RUN_ID
1822 NULL, --REC_STATUS,
1823 NULL, --DISPOSITION,
1824 '0', --DELETED_FLAG
1825 NULL, --EFF_FROM,
1826 NULL, --EFF_TO,
1827 NULL, --EFF_MASK,
1828 NULL, --USER_STR01,
1829 NULL, --USER_STR02,
1830 NULL, --USER_STR03,
1831 NULL, --USER_STR04,
1832 NULL, --USER_NUM01,
1833 NULL, --USER_NUM02,
1834 NULL, --USER_NUM03,
1835 NULL, --USER_NUM04,
1836 NULL, --CHECKOUT_USER,
1837 SYSDATE, --CREATION_DATE
1838 SYSDATE, --LAST_UPDATE_DATE
1839 FND_GLOBAL.USER_ID, --CREATED_BY
1840 FND_GLOBAL.USER_ID, --LAST_UPDATED_BY
1841 NULL, --SECURITY_MASK,
1842 G_TEMPLATE_MODEL_OPTION_OSR||
1843 p_org_id||':'||
1844 p_intent||':'||
1845 p_derived_template_id||':'||
1846 p_question_id||':'||
1847 IdList_tbl(i), --FSK_INTLTEXT_1_1
1848 NULL, --FSK_INTLTEXT_1_EXT,
1849 NULL, --FSK_ITEMMASTER_2_1,
1850 NULL, --FSK_ITEMMASTER_2_EXT,
1851 G_TEMPLATE_MODEL_FEATURE_OSR||
1852 p_org_id||':'||
1853 p_intent||':'||
1854 p_derived_template_id||':'||
1855 p_question_id, --FSK_PSNODE_3_1
1856 NULL, --FSK_PSNODE_3_EXT,
1857 NULL, --FSK_PSNODE_4_1,
1858 NULL, --FSK_PSNODE_4_EXT,
1859 G_TEMPLATE_MODEL_OSR||
1860 p_org_id||':'||
1861 p_intent||':'||
1862 p_derived_template_id , --FSK_DEVLPROJECT_5_1
1863 NULL, --FSK_DEVLPROJECT_5_EXT,
1864 NULL, --COMPONENT_SEQUENCE_ID,
1865 NULL, --COMPONENT_CODE,
1866 2, --PLAN_LEVEL --Plan Level for Option:2
1867 NULL, --BOM_ITEM_TYPE,
1868 NULL, --SO_ITEM_TYPE_CODE,
1869 NULL, --MINIMUM_SELECTED,
1870 NULL, --MAXIMUM_SELECTED,
1871 NULL, --BOM_REQUIRED,
1872 NULL, --MUTUALLY_EXCLUSIVE_OPTIONS,
1873 NULL, --OPTIONAL,
1874 NULL, --FSK_EXPLNODE_1_1,
1875 NULL, --FSK_PSNODE_6_1,
1876 OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_BEGIN, --EFFECTIVE_FROM
1877 OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_END, --EFFECTIVE_UNTIL
1878 NULL, --EFFECTIVE_USAGE_MASK,
1879 NULL, --EFFECTIVITY_SET_ID,
1880 NULL, --FSK_EFFSET_7_1,
1881 '0', --DECIMAL_QTY_FLAG -- 0 for all nodes
1882 NULL, --QUOTEABLE_FLAG
1883 NULL, --PRIMARY_UOM_CODE,
1884 NULL, --COMPONENT_SEQUENCE_PATH, -- Must be NULL
1885 NULL, --BOM_SORT_ORDER,
1886 NULL, --IB_TRACKABLE,
1887 FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN,
1888 NULL, --INITIAL_NUM_VALUE,
1889 G_APPLICATION_ID, --SRC_APPLICATION_ID
1890 NULL, --FSK_ITEMMASTER_2_2,
1891 NULL, --INSTANTIABLE_FLAG,
1892 NULL --DISPLAY_IN_SUMMARY_FLAG
1893 );
1894
1895 END IF; -- if row count > 0
1896
1897 -- Standard call to get message count and if count is 1, get message info.
1898 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1899
1900
1901 -- end debug log
1902 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1903 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1904 G_MODULE||l_api_name,
1905 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1906 END IF;
1907
1908 EXCEPTION
1909 WHEN FND_API.G_EXC_ERROR THEN
1910 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1911 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1912 G_MODULE||l_api_name,
1913 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1914 END IF;
1915
1916 x_return_status := G_RET_STS_ERROR ;
1917 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1918
1919 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1920 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1921 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1922 G_MODULE||l_api_name,
1923 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1924 END IF;
1925
1926 x_return_status := G_RET_STS_UNEXP_ERROR ;
1927 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1928
1929 WHEN OTHERS THEN
1930 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1931 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1932 G_MODULE||l_api_name,
1933 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
1934 END IF;
1935
1936 x_return_status := G_RET_STS_UNEXP_ERROR ;
1937
1938 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1939 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1940 END IF;
1941
1942 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1943
1944
1945 END create_template_options;
1946
1947 /*====================================================================+
1948 Procedure Name : create_clause_model_ref
1949 Description : This is a private API that creates the reference node
1950 of Clause model in the template model
1951 Parameters:
1952 p_template_id - Template Id
1953 p_intent - Intent of the variable model
1954 p_org_id - Org Id of the template
1955
1956 +====================================================================*/
1957
1958 PROCEDURE create_clause_model_ref
1959 (
1960 p_template_id IN NUMBER,
1961 p_intent IN VARCHAR2,
1962 p_org_id IN NUMBER,
1963 x_return_status OUT NOCOPY VARCHAR2,
1964 x_msg_data OUT NOCOPY VARCHAR2,
1965 x_msg_count OUT NOCOPY NUMBER
1966 ) IS
1967
1968
1969 CURSOR csr_installed_languages IS
1970 SELECT L.LANGUAGE_CODE
1971 FROM FND_LANGUAGES L
1972 WHERE L.INSTALLED_FLAG IN ('I', 'B');
1973
1974 l_language FND_LANGUAGES.LANGUAGE_CODE%TYPE;
1975
1976 l_cz_imp_ps_nodes_rec CZ_IMP_PS_NODES%ROWTYPE;
1977 l_api_name CONSTANT VARCHAR2(30) := 'create_clause_model_ref';
1978 l_clause_model_name VARCHAR2(255);
1979
1980 BEGIN
1981
1982 -- start debug log
1983 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1984 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1985 G_MODULE||l_api_name,
1986 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1987 END IF;
1988
1989 x_return_status := G_RET_STS_SUCCESS;
1990
1991 -- Get the Clause Model Name
1992 FND_MESSAGE.set_name('OKC','OKC_EXPRT_ART_MODEL_TITLE');
1993 FND_MESSAGE.set_token('ORG_NAME',G_ORGANIZATION_NAME);
1994 FND_MESSAGE.set_token('INTENT_MEANING',okc_util.decode_lookup('OKC_ARTICLE_INTENT',p_intent));
1995 l_clause_model_name := FND_MESSAGE.get;
1996
1997
1998 -- Put the Name in the description Column of cz_ps_nodes
1999
2000 OPEN csr_installed_languages;
2001 LOOP
2002 FETCH csr_installed_languages INTO l_language;
2003 EXIT WHEN csr_installed_languages%NOTFOUND;
2004
2005 -- Insert into cz_imp_localized_text
2006
2007 INSERT INTO CZ_IMP_LOCALIZED_TEXTS
2008 (
2009 LAST_UPDATE_LOGIN,
2010 LOCALE_ID,
2011 LOCALIZED_STR,
2012 INTL_TEXT_ID,
2013 CREATION_DATE,
2014 LAST_UPDATE_DATE,
2015 DELETED_FLAG,
2016 EFF_FROM,
2017 EFF_TO,
2018 CREATED_BY,
2019 LAST_UPDATED_BY,
2020 SECURITY_MASK,
2021 EFF_MASK,
2022 CHECKOUT_USER,
2023 LANGUAGE,
2024 ORIG_SYS_REF,
2025 SOURCE_LANG,
2026 RUN_ID,
2027 REC_STATUS,
2028 DISPOSITION,
2029 MODEL_ID,
2030 FSK_DEVLPROJECT_1_1,
2031 MESSAGE,
2032 SEEDED_FLAG
2033 )
2034 VALUES
2035 (
2036 FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN
2037 NULL, -- LOCALE_ID
2038 l_clause_model_name, --LOCALIZED_STR
2039 NULL, -- INTL_TEXT_ID
2040 SYSDATE, -- CREATION_DATE
2041 SYSDATE, -- LAST_UPDATE_DATE
2042 '0', -- DELETED_FLAG
2043 NULL, -- EFF_FROM
2044 NULL, -- EFF_TO
2045 FND_GLOBAL.USER_ID, -- CREATED_BY
2046 FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
2047 NULL, -- SECURITY_MASK
2048 NULL, -- EFF_MASK
2049 NULL, -- CHECKOUT_USER
2050 l_language, --LANGUAGE
2051 G_TMPL_MODEL_CM_REF_NODE_OSR|| p_org_id||':'||p_intent||':'||p_template_id, -- ORIG_SYS_REF
2052 USERENV('LANG'), --SOURCE_LANG
2053 G_RUN_ID, -- RUN_ID
2054 NULL, -- REC_STATUS
2055 NULL, -- DISPOSITION
2056 NULL, -- MODEL_ID
2057 G_TEMPLATE_MODEL_OSR||p_org_id||':'||p_intent||':'||p_template_id, --FSK_DEVLPROJECT_1_1
2058 NULL, -- MESSAGE
2059 NULL -- SEEDED_FLAG
2060 );
2061
2062 END LOOP; -- for all installed languages
2063 CLOSE csr_installed_languages;
2064
2065
2066 -- Populate the cz_imp_ps_nodes record
2067
2068 l_cz_imp_ps_nodes_rec.PS_NODE_ID:= NULL;
2069 l_cz_imp_ps_nodes_rec.DEVL_PROJECT_ID:= NULL;
2070 l_cz_imp_ps_nodes_rec.FROM_POPULATOR_ID:= NULL;
2071 l_cz_imp_ps_nodes_rec.PROPERTY_BACKPTR:= NULL;
2072 l_cz_imp_ps_nodes_rec.ITEM_TYPE_BACKPTR:= NULL;
2073 l_cz_imp_ps_nodes_rec.INTL_TEXT_ID:= NULL;
2074 l_cz_imp_ps_nodes_rec.SUB_CONS_ID:= NULL;
2075 l_cz_imp_ps_nodes_rec.ORGANIZATION_ID:= p_org_id;
2076 l_cz_imp_ps_nodes_rec.ITEM_ID:= NULL;
2077 l_cz_imp_ps_nodes_rec.EXPLOSION_TYPE:= NULL;
2078 l_cz_imp_ps_nodes_rec.NAME:= G_TMPL_MODEL_CM_REF_NODE_OSR||
2079 p_org_id||':'||
2080 p_intent||':'||
2081 p_template_id;
2082 l_cz_imp_ps_nodes_rec.ORIG_SYS_REF:= G_TMPL_MODEL_CM_REF_NODE_OSR||
2083 p_org_id||':'||
2084 p_intent||':'||
2085 p_template_id;
2086 l_cz_imp_ps_nodes_rec.RESOURCE_FLAG:= NULL;
2087 l_cz_imp_ps_nodes_rec.TOP_ITEM_ID:= 1; -- same value as in cz_imp_devl_projects
2088 l_cz_imp_ps_nodes_rec.INITIAL_VALUE:= NULL;
2089 l_cz_imp_ps_nodes_rec.PARENT_ID:= NULL;
2090 l_cz_imp_ps_nodes_rec.MINIMUM:= 1;
2091 l_cz_imp_ps_nodes_rec.MAXIMUM:= 1;
2092 l_cz_imp_ps_nodes_rec.PS_NODE_TYPE:= 263; -- Reference Node
2093 l_cz_imp_ps_nodes_rec.FEATURE_TYPE:= 0;
2094 l_cz_imp_ps_nodes_rec.PRODUCT_FLAG:= '0'; -- check Reference Node
2095 l_cz_imp_ps_nodes_rec.REFERENCE_ID:= NULL;
2096 l_cz_imp_ps_nodes_rec.MULTI_CONFIG_FLAG:= NULL;
2097 l_cz_imp_ps_nodes_rec.ORDER_SEQ_FLAG:= NULL;
2098 l_cz_imp_ps_nodes_rec.SYSTEM_NODE_FLAG:= NULL;
2099 l_cz_imp_ps_nodes_rec.TREE_SEQ:= 1; -- create node just below Component node
2100 l_cz_imp_ps_nodes_rec.COUNTED_OPTIONS_FLAG:= '0';
2101 l_cz_imp_ps_nodes_rec.UI_OMIT:= '1';
2102 l_cz_imp_ps_nodes_rec.UI_SECTION:= 0;
2103 l_cz_imp_ps_nodes_rec.BOM_TREATMENT:= NULL;
2104 l_cz_imp_ps_nodes_rec.RUN_ID:= G_RUN_ID;
2105 l_cz_imp_ps_nodes_rec.REC_STATUS:= NULL;
2106 l_cz_imp_ps_nodes_rec.DISPOSITION:= NULL;
2107 l_cz_imp_ps_nodes_rec.DELETED_FLAG := 0;
2108 l_cz_imp_ps_nodes_rec.EFF_FROM:= NULL;
2109 l_cz_imp_ps_nodes_rec.EFF_TO:= NULL;
2110 l_cz_imp_ps_nodes_rec.EFF_MASK:= NULL;
2111 l_cz_imp_ps_nodes_rec.USER_STR01:= NULL;
2112 l_cz_imp_ps_nodes_rec.USER_STR02:= NULL;
2113 l_cz_imp_ps_nodes_rec.USER_STR03:= NULL;
2114 l_cz_imp_ps_nodes_rec.USER_STR04:= NULL;
2115 l_cz_imp_ps_nodes_rec.USER_NUM01:= NULL;
2116 l_cz_imp_ps_nodes_rec.USER_NUM02:= NULL;
2117 l_cz_imp_ps_nodes_rec.USER_NUM03:= NULL;
2118 l_cz_imp_ps_nodes_rec.USER_NUM04:= NULL;
2119 l_cz_imp_ps_nodes_rec.CHECKOUT_USER:= NULL;
2120 l_cz_imp_ps_nodes_rec.CREATION_DATE:= SYSDATE;
2121 l_cz_imp_ps_nodes_rec.LAST_UPDATE_DATE:= SYSDATE;
2122 l_cz_imp_ps_nodes_rec.CREATED_BY:= FND_GLOBAL.USER_ID;
2123 l_cz_imp_ps_nodes_rec.LAST_UPDATED_BY:= FND_GLOBAL.USER_ID;
2124 l_cz_imp_ps_nodes_rec.SECURITY_MASK:= NULL;
2125 l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_1:= G_TMPL_MODEL_CM_REF_NODE_OSR||
2126 p_org_id||':'||
2127 p_intent||':'||
2128 p_template_id;
2129 l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_EXT:= NULL;
2130 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_1:= NULL;
2131 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_EXT:= NULL;
2132 l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_1:= G_TEMPLATE_MODEL_TOPNODE_OSR||
2133 p_org_id||':'||
2134 p_intent||':'||
2135 p_template_id;
2136 l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_EXT:= NULL;
2137 l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_1:= NULL;
2138 l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_EXT:= NULL;
2139 l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_1:= G_TEMPLATE_MODEL_OSR||
2140 p_org_id||':'||
2141 p_intent||':'||
2142 p_template_id;
2143 l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_EXT:= NULL;
2144 l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_ID:= NULL;
2145 l_cz_imp_ps_nodes_rec.COMPONENT_CODE:= NULL;
2146 l_cz_imp_ps_nodes_rec.PLAN_LEVEL:= 1;
2147 l_cz_imp_ps_nodes_rec.BOM_ITEM_TYPE:= NULL;
2148 l_cz_imp_ps_nodes_rec.SO_ITEM_TYPE_CODE:= NULL;
2149 l_cz_imp_ps_nodes_rec.MINIMUM_SELECTED:= NULL;
2150 l_cz_imp_ps_nodes_rec.MAXIMUM_SELECTED:= NULL;
2151 l_cz_imp_ps_nodes_rec.BOM_REQUIRED:= NULL;
2152 l_cz_imp_ps_nodes_rec.MUTUALLY_EXCLUSIVE_OPTIONS:= NULL;
2153 l_cz_imp_ps_nodes_rec.OPTIONAL:= NULL;
2154 l_cz_imp_ps_nodes_rec.FSK_EXPLNODE_1_1:= NULL;
2155 l_cz_imp_ps_nodes_rec.FSK_PSNODE_6_1:= G_CLAUSE_MODEL_TOPNODE_OSR||p_org_id||':'||p_intent;
2156 l_cz_imp_ps_nodes_rec.EFFECTIVE_FROM:= OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_BEGIN;
2157 l_cz_imp_ps_nodes_rec.EFFECTIVE_UNTIL:= OKC_XPRT_CZ_INT_PVT.G_CZ_EPOCH_END;
2158 l_cz_imp_ps_nodes_rec.EFFECTIVE_USAGE_MASK:= NULL;
2159 l_cz_imp_ps_nodes_rec.EFFECTIVITY_SET_ID:= NULL;
2160 l_cz_imp_ps_nodes_rec.FSK_EFFSET_7_1:= NULL;
2161 l_cz_imp_ps_nodes_rec.DECIMAL_QTY_FLAG:= 0; -- 0 for all nodes
2162 l_cz_imp_ps_nodes_rec.QUOTEABLE_FLAG:= NULL;
2163 l_cz_imp_ps_nodes_rec.PRIMARY_UOM_CODE:= NULL;
2164 l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_PATH:= NULL; -- Must be NULL
2165 l_cz_imp_ps_nodes_rec.BOM_SORT_ORDER:= NULL;
2166 l_cz_imp_ps_nodes_rec.IB_TRACKABLE:= NULL;
2167 l_cz_imp_ps_nodes_rec.LAST_UPDATE_LOGIN:= FND_GLOBAL.LOGIN_ID;
2168 l_cz_imp_ps_nodes_rec.INITIAL_NUM_VALUE:= NULL;
2169 l_cz_imp_ps_nodes_rec.SRC_APPLICATION_ID:= G_APPLICATION_ID;
2170 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_2:= NULL;
2171 l_cz_imp_ps_nodes_rec.INSTANTIABLE_FLAG:= NULL;
2172 l_cz_imp_ps_nodes_rec.DISPLAY_IN_SUMMARY_FLAG:= NULL;
2173
2174
2175 INSERT INTO cz_imp_ps_nodes
2176 (
2177 PS_NODE_ID,
2178 DEVL_PROJECT_ID,
2179 FROM_POPULATOR_ID,
2180 PROPERTY_BACKPTR,
2181 ITEM_TYPE_BACKPTR,
2182 INTL_TEXT_ID,
2183 SUB_CONS_ID,
2184 ORGANIZATION_ID,
2185 ITEM_ID,
2186 EXPLOSION_TYPE,
2187 NAME,
2188 ORIG_SYS_REF,
2189 RESOURCE_FLAG,
2190 TOP_ITEM_ID,
2191 INITIAL_VALUE,
2192 PARENT_ID,
2193 MINIMUM,
2194 MAXIMUM,
2195 PS_NODE_TYPE,
2196 FEATURE_TYPE,
2197 PRODUCT_FLAG,
2198 REFERENCE_ID,
2199 MULTI_CONFIG_FLAG,
2200 ORDER_SEQ_FLAG,
2201 SYSTEM_NODE_FLAG,
2202 TREE_SEQ,
2203 COUNTED_OPTIONS_FLAG,
2204 UI_OMIT,
2205 UI_SECTION,
2206 BOM_TREATMENT,
2207 RUN_ID,
2208 REC_STATUS,
2209 DISPOSITION,
2210 DELETED_FLAG ,
2211 EFF_FROM,
2212 EFF_TO,
2213 EFF_MASK,
2214 USER_STR01,
2215 USER_STR02,
2216 USER_STR03,
2217 USER_STR04,
2218 USER_NUM01,
2219 USER_NUM02,
2220 USER_NUM03,
2221 USER_NUM04,
2222 CHECKOUT_USER,
2223 CREATION_DATE,
2224 LAST_UPDATE_DATE,
2225 CREATED_BY,
2226 LAST_UPDATED_BY,
2227 SECURITY_MASK,
2228 FSK_INTLTEXT_1_1,
2229 FSK_INTLTEXT_1_EXT,
2230 FSK_ITEMMASTER_2_1,
2231 FSK_ITEMMASTER_2_EXT,
2232 FSK_PSNODE_3_1,
2233 FSK_PSNODE_3_EXT,
2234 FSK_PSNODE_4_1,
2235 FSK_PSNODE_4_EXT,
2236 FSK_DEVLPROJECT_5_1,
2237 FSK_DEVLPROJECT_5_EXT,
2238 COMPONENT_SEQUENCE_ID,
2239 COMPONENT_CODE,
2240 PLAN_LEVEL,
2241 BOM_ITEM_TYPE,
2242 SO_ITEM_TYPE_CODE,
2243 MINIMUM_SELECTED,
2244 MAXIMUM_SELECTED,
2245 BOM_REQUIRED,
2246 MUTUALLY_EXCLUSIVE_OPTIONS,
2247 OPTIONAL,
2248 FSK_EXPLNODE_1_1,
2249 FSK_PSNODE_6_1,
2250 EFFECTIVE_FROM,
2251 EFFECTIVE_UNTIL,
2252 EFFECTIVE_USAGE_MASK,
2253 EFFECTIVITY_SET_ID,
2254 FSK_EFFSET_7_1,
2255 DECIMAL_QTY_FLAG,
2256 QUOTEABLE_FLAG,
2257 PRIMARY_UOM_CODE,
2258 COMPONENT_SEQUENCE_PATH,
2259 BOM_SORT_ORDER,
2260 IB_TRACKABLE,
2261 LAST_UPDATE_LOGIN,
2262 INITIAL_NUM_VALUE,
2263 SRC_APPLICATION_ID,
2264 FSK_ITEMMASTER_2_2,
2265 INSTANTIABLE_FLAG,
2266 DISPLAY_IN_SUMMARY_FLAG
2267 )
2268 VALUES
2269 (
2270 l_cz_imp_ps_nodes_rec.PS_NODE_ID,
2271 l_cz_imp_ps_nodes_rec.DEVL_PROJECT_ID,
2272 l_cz_imp_ps_nodes_rec.FROM_POPULATOR_ID,
2273 l_cz_imp_ps_nodes_rec.PROPERTY_BACKPTR,
2274 l_cz_imp_ps_nodes_rec.ITEM_TYPE_BACKPTR,
2275 l_cz_imp_ps_nodes_rec.INTL_TEXT_ID,
2276 l_cz_imp_ps_nodes_rec.SUB_CONS_ID,
2277 l_cz_imp_ps_nodes_rec.ORGANIZATION_ID,
2278 l_cz_imp_ps_nodes_rec.ITEM_ID,
2279 l_cz_imp_ps_nodes_rec.EXPLOSION_TYPE,
2280 l_cz_imp_ps_nodes_rec.NAME,
2281 l_cz_imp_ps_nodes_rec.ORIG_SYS_REF,
2282 l_cz_imp_ps_nodes_rec.RESOURCE_FLAG,
2283 l_cz_imp_ps_nodes_rec.TOP_ITEM_ID,
2284 l_cz_imp_ps_nodes_rec.INITIAL_VALUE,
2285 l_cz_imp_ps_nodes_rec.PARENT_ID,
2286 l_cz_imp_ps_nodes_rec.MINIMUM,
2287 l_cz_imp_ps_nodes_rec.MAXIMUM,
2288 l_cz_imp_ps_nodes_rec.PS_NODE_TYPE,
2289 l_cz_imp_ps_nodes_rec.FEATURE_TYPE,
2290 l_cz_imp_ps_nodes_rec.PRODUCT_FLAG,
2291 l_cz_imp_ps_nodes_rec.REFERENCE_ID,
2292 l_cz_imp_ps_nodes_rec.MULTI_CONFIG_FLAG,
2293 l_cz_imp_ps_nodes_rec.ORDER_SEQ_FLAG,
2294 l_cz_imp_ps_nodes_rec.SYSTEM_NODE_FLAG,
2295 l_cz_imp_ps_nodes_rec.TREE_SEQ,
2296 l_cz_imp_ps_nodes_rec.COUNTED_OPTIONS_FLAG,
2297 l_cz_imp_ps_nodes_rec.UI_OMIT,
2298 l_cz_imp_ps_nodes_rec.UI_SECTION,
2299 l_cz_imp_ps_nodes_rec.BOM_TREATMENT,
2300 l_cz_imp_ps_nodes_rec.RUN_ID,
2301 l_cz_imp_ps_nodes_rec.REC_STATUS,
2302 l_cz_imp_ps_nodes_rec.DISPOSITION,
2303 l_cz_imp_ps_nodes_rec.DELETED_FLAG ,
2304 l_cz_imp_ps_nodes_rec.EFF_FROM,
2305 l_cz_imp_ps_nodes_rec.EFF_TO,
2306 l_cz_imp_ps_nodes_rec.EFF_MASK,
2307 l_cz_imp_ps_nodes_rec.USER_STR01,
2308 l_cz_imp_ps_nodes_rec.USER_STR02,
2309 l_cz_imp_ps_nodes_rec.USER_STR03,
2310 l_cz_imp_ps_nodes_rec.USER_STR04,
2311 l_cz_imp_ps_nodes_rec.USER_NUM01,
2312 l_cz_imp_ps_nodes_rec.USER_NUM02,
2313 l_cz_imp_ps_nodes_rec.USER_NUM03,
2314 l_cz_imp_ps_nodes_rec.USER_NUM04,
2315 l_cz_imp_ps_nodes_rec.CHECKOUT_USER,
2316 l_cz_imp_ps_nodes_rec.CREATION_DATE,
2317 l_cz_imp_ps_nodes_rec.LAST_UPDATE_DATE,
2318 l_cz_imp_ps_nodes_rec.CREATED_BY,
2319 l_cz_imp_ps_nodes_rec.LAST_UPDATED_BY,
2320 l_cz_imp_ps_nodes_rec.SECURITY_MASK,
2321 l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_1,
2322 l_cz_imp_ps_nodes_rec.FSK_INTLTEXT_1_EXT,
2323 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_1,
2324 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_EXT,
2325 l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_1,
2326 l_cz_imp_ps_nodes_rec.FSK_PSNODE_3_EXT,
2327 l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_1,
2328 l_cz_imp_ps_nodes_rec.FSK_PSNODE_4_EXT,
2329 l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_1,
2330 l_cz_imp_ps_nodes_rec.FSK_DEVLPROJECT_5_EXT,
2331 l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_ID,
2332 l_cz_imp_ps_nodes_rec.COMPONENT_CODE,
2333 l_cz_imp_ps_nodes_rec.PLAN_LEVEL,
2334 l_cz_imp_ps_nodes_rec.BOM_ITEM_TYPE,
2335 l_cz_imp_ps_nodes_rec.SO_ITEM_TYPE_CODE,
2336 l_cz_imp_ps_nodes_rec.MINIMUM_SELECTED,
2337 l_cz_imp_ps_nodes_rec.MAXIMUM_SELECTED,
2338 l_cz_imp_ps_nodes_rec.BOM_REQUIRED,
2339 l_cz_imp_ps_nodes_rec.MUTUALLY_EXCLUSIVE_OPTIONS,
2340 l_cz_imp_ps_nodes_rec.OPTIONAL,
2341 l_cz_imp_ps_nodes_rec.FSK_EXPLNODE_1_1,
2342 l_cz_imp_ps_nodes_rec.FSK_PSNODE_6_1,
2343 l_cz_imp_ps_nodes_rec.EFFECTIVE_FROM,
2344 l_cz_imp_ps_nodes_rec.EFFECTIVE_UNTIL,
2345 l_cz_imp_ps_nodes_rec.EFFECTIVE_USAGE_MASK,
2346 l_cz_imp_ps_nodes_rec.EFFECTIVITY_SET_ID,
2347 l_cz_imp_ps_nodes_rec.FSK_EFFSET_7_1,
2348 l_cz_imp_ps_nodes_rec.DECIMAL_QTY_FLAG,
2349 l_cz_imp_ps_nodes_rec.QUOTEABLE_FLAG,
2350 l_cz_imp_ps_nodes_rec.PRIMARY_UOM_CODE,
2351 l_cz_imp_ps_nodes_rec.COMPONENT_SEQUENCE_PATH,
2352 l_cz_imp_ps_nodes_rec.BOM_SORT_ORDER,
2353 l_cz_imp_ps_nodes_rec.IB_TRACKABLE,
2354 l_cz_imp_ps_nodes_rec.LAST_UPDATE_LOGIN,
2355 l_cz_imp_ps_nodes_rec.INITIAL_NUM_VALUE,
2356 l_cz_imp_ps_nodes_rec.SRC_APPLICATION_ID,
2357 l_cz_imp_ps_nodes_rec.FSK_ITEMMASTER_2_2,
2358 l_cz_imp_ps_nodes_rec.INSTANTIABLE_FLAG,
2359 l_cz_imp_ps_nodes_rec.DISPLAY_IN_SUMMARY_FLAG
2360 );
2361
2362
2363 -- Standard call to get message count and if count is 1, get message info.
2364 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2365
2366
2367 -- end debug log
2368 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2369 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2370 G_MODULE||l_api_name,
2371 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2372 END IF;
2373
2374 EXCEPTION
2375 WHEN OTHERS THEN
2376 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2377 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2378 G_MODULE||l_api_name,
2379 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2380 END IF;
2381
2382 x_return_status := G_RET_STS_UNEXP_ERROR ;
2383
2384 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2385 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2386 END IF;
2387
2388 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2389
2390
2391 END create_clause_model_ref;
2392
2393
2394
2395 /*
2396 ---------------------------------------------------
2397 -- PUBLIC Procedures and Functions
2398 ---------------------------------------------------
2399 */
2400 /*====================================================================+
2401 Procedure Name : import_template
2402 Description : This is a PUBLIC API that imports template Model
2403 This API is called from template approval concurrent program
2404 Parameters:
2405 p_template_id - Template Id of the template
2406
2407 +====================================================================*/
2408
2409 PROCEDURE import_template
2410 (
2411 p_api_version IN NUMBER,
2412 p_init_msg_list IN VARCHAR2,
2413 p_commit IN VARCHAR2,
2414 p_template_id IN NUMBER,
2415 x_return_status OUT NOCOPY VARCHAR2,
2416 x_msg_data OUT NOCOPY VARCHAR2,
2417 x_msg_count OUT NOCOPY NUMBER
2418 ) IS
2419
2420 CURSOR csr_cz_run_id IS
2421 SELECT cz_xfr_run_infos_s.NEXTVAL
2422 FROM dual;
2423
2424 CURSOR csr_template_dtls IS
2425 SELECT template_name,
2426 DECODE(parent_template_id, NULL, template_id, parent_template_id),
2427 intent,
2428 name,
2429 org_id
2430 FROM okc_terms_templates_all,
2431 hr_operating_units
2432 WHERE organization_id = org_id
2433 AND template_id = p_template_id ;
2434
2435 /*
2436 CURSOR csr_template_model_id(p_org_id IN NUMBER,
2437 p_intent IN VARCHAR2,
2438 p_tmpl_id IN NUMBER) IS
2439 */
2440 CURSOR csr_template_model_id(p_orig_sys_ref IN VARCHAR2) IS
2441 SELECT devl_project_id
2442 FROM cz_devl_projects
2443 WHERE orig_sys_ref = p_orig_sys_ref
2444 AND devl_project_id = persistent_project_id
2445 AND deleted_flag = 0;
2446
2447 -- WHERE orig_sys_ref = G_TEMPLATE_MODEL_OSR||p_org_id||':'||p_intent||':'||p_tmpl_id
2448
2449 /*
2450 CURSOR csr_template_folder(p_org_id IN NUMBER,
2451 p_intent IN VARCHAR2) IS
2452 */
2453 CURSOR csr_template_folder(p_name IN VARCHAR2) IS
2454 SELECT object_id
2455 FROM cz_rp_entries
2456 WHERE enclosing_folder= OKC_XPRT_CZ_INT_PVT.G_TEMPLATE_FOLDER_ID
2457 AND object_type = 'FLD'
2458 AND deleted_flag=0
2459 AND name = p_name;
2460 -- AND name = G_TEMPLATE_FOLDER_OSR||p_org_id||':'||p_intent;
2461
2462 l_api_version CONSTANT NUMBER := 1;
2463 l_api_name CONSTANT VARCHAR2(30) := 'import_template';
2464 l_template_model_id NUMBER :=NULL;
2465 l_run_id NUMBER;
2466 l_template_folder_id NUMBER :=NULL;
2467 l_folder_desc VARCHAR2(255);
2468 l_import_status VARCHAR2(10);
2469
2470 l_template_name OKC_TERMS_TEMPLATES_ALL.template_name%TYPE;
2471 l_template_id OKC_TERMS_TEMPLATES_ALL.template_id%TYPE;
2472 l_intent OKC_TERMS_TEMPLATES_ALL.intent%TYPE;
2473 l_org_id OKC_TERMS_TEMPLATES_ALL.org_id%TYPE;
2474 l_tmpl_orig_sys_ref cz_devl_projects.orig_sys_ref%TYPE;
2475 l_folder_name cz_rp_entries.name%TYPE;
2476
2477 BEGIN
2478
2479 -- start debug log
2480 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2481 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2482 G_MODULE||l_api_name,
2483 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
2484 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2485 G_MODULE||l_api_name,
2486 '100: Parameters ');
2487 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2488 G_MODULE||l_api_name,
2489 '100: p_template_id '||p_template_id);
2490 END IF;
2491
2492 -- Standard call to check for call compatibility.
2493 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2495 END IF;
2496
2497 -- Initialize message list if p_init_msg_list is set to TRUE.
2498 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2499 FND_MSG_PUB.initialize;
2500 END IF;
2501
2502 -- Initialize API return status to success
2503 x_return_status := FND_API.G_RET_STS_SUCCESS;
2504
2505 -- Get Template Details
2506 OPEN csr_template_dtls;
2507 FETCH csr_template_dtls INTO l_template_name,
2508 l_template_id,
2509 l_intent,
2510 G_ORGANIZATION_NAME,
2511 l_org_id;
2512
2513 IF csr_template_dtls%NOTFOUND THEN
2514 -- Log Error
2515 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2516 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2517 G_MODULE||l_api_name,
2518 '110: Invalid Template Id: '||p_template_id);
2519 END IF;
2520 FND_MESSAGE.set_name('OKC','OKC_XPRT_INVALID_TEMPLATE');
2521 RAISE FND_API.G_EXC_ERROR;
2522 END IF;
2523
2524 CLOSE csr_template_dtls;
2525
2526 -- Put the Template Name in Concurrent Request Log File
2527 fnd_file.put_line(FND_FILE.LOG,' ');
2528 fnd_file.put_line(FND_FILE.LOG,'Template Name : '||l_template_name);
2529
2530
2531 -- Update the xprt_request_id for the current template
2532 UPDATE okc_terms_templates_all
2533 SET xprt_request_id = FND_GLOBAL.CONC_REQUEST_ID,
2534 last_update_login = FND_GLOBAL.LOGIN_ID,
2535 last_update_date = SYSDATE,
2536 last_updated_by = FND_GLOBAL.USER_ID
2537 WHERE template_id = p_template_id;
2538
2539 -- debug log
2540 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2541 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2542 G_MODULE||l_api_name,
2543 '110: Calling populate_questions_order');
2544 END IF;
2545
2546 --
2547 -- Call API to populate template questions
2548 --
2549 OKC_XPRT_UTIL_PVT.populate_questions_order
2550 (
2551 p_template_id => p_template_id,
2552 p_commit_flag => 'Y',
2553 p_mode => 'P',
2554 x_return_status => x_return_status,
2555 x_msg_data => x_msg_data,
2556 x_msg_count => x_msg_count
2557 );
2558
2559 -- debug log
2560 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2561 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2562 G_MODULE||l_api_name,
2563 '111: After Calling populate_questions_order x_return_status : '||x_return_status);
2564 END IF;
2565
2566 --- If any errors happen abort API
2567 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2568 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2569 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2570 RAISE FND_API.G_EXC_ERROR;
2571 END IF;
2572
2573 -- build folder name
2574 l_folder_name := G_TEMPLATE_FOLDER_OSR||l_org_id||':'||l_intent;
2575 -- Get the Template folder Id
2576 /*
2577 OPEN csr_template_folder(p_org_id => l_org_id,
2578 p_intent => l_intent);
2579 */
2580 OPEN csr_template_folder(p_name => l_folder_name);
2581 FETCH csr_template_folder INTO l_template_folder_id;
2582 IF csr_template_folder%NOTFOUND THEN
2583 -- Create template folder for Org and Intent
2584
2585 -- Generate Folder Description
2586 FND_MESSAGE.set_name('OKC','OKC_XPRT_TMPL_FOLDER_DESC');
2587 FND_MESSAGE.set_token('ORGANIZATION_NAME',G_ORGANIZATION_NAME);
2588 FND_MESSAGE.set_token('INTENT',okc_util.decode_lookup('OKC_ARTICLE_INTENT',l_intent));
2589 l_folder_desc := FND_MESSAGE.get;
2590
2591 -- folder does not exits so create the folder
2592 OKC_XPRT_CZ_INT_PVT.create_rp_folder(
2593 p_api_version => l_api_version,
2594 p_encl_folder_id => OKC_XPRT_CZ_INT_PVT.G_TEMPLATE_FOLDER_ID,
2595 p_new_folder_name => G_TEMPLATE_FOLDER_OSR||l_org_id||':'||l_intent,
2596 p_folder_desc => l_folder_desc,
2597 p_folder_notes => l_folder_desc,
2598 x_new_folder_id => l_template_folder_id,
2599 x_return_status => x_return_status,
2600 x_msg_count => x_msg_count,
2601 x_msg_data => x_msg_data);
2602
2603 -- debug log
2604 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2605 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2606 G_MODULE||l_api_name,
2607 '111: After Calling OKC_XPRT_CZ_INT_PVT.create_rp_folder x_return_status : '||x_return_status);
2608 END IF;
2609
2610 --- If any errors happen abort API
2611 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2612 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2613 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2614 RAISE FND_API.G_EXC_ERROR;
2615 END IF;
2616
2617 END IF; -- folder does not exists
2618 CLOSE csr_template_folder;
2619
2620 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2621 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2622 G_MODULE||l_api_name,
2623 '120: Template Folder Id: '||l_template_folder_id);
2624 END IF;
2625
2626
2627 -- Generate the Run Id
2628 OPEN csr_cz_run_id;
2629 FETCH csr_cz_run_id INTO G_RUN_ID;
2630 CLOSE csr_cz_run_id;
2631
2632 -- initialize l_template_model_id
2633 l_template_model_id := NULL;
2634
2635 -- build the template OSR
2636 l_tmpl_orig_sys_ref := G_TEMPLATE_MODEL_OSR||l_org_id||':'||l_intent||':'||l_template_id;
2637
2638 -- check if Template Model Already exists in CZ and get the Model Id
2639 /*
2640 OPEN csr_template_model_id(p_org_id => l_org_id,
2641 p_intent => l_intent,
2642 p_tmpl_id => l_template_id);
2643 */
2644 OPEN csr_template_model_id(p_orig_sys_ref => l_tmpl_orig_sys_ref);
2645 FETCH csr_template_model_id INTO l_template_model_id;
2646 CLOSE csr_template_model_id;
2647
2648 -- debug log
2649 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2650 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2651 G_MODULE||l_api_name,
2652 '150: Run Id :'||G_RUN_ID);
2653 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2654 G_MODULE||l_api_name,
2655 '150: Template Name: '||l_template_name);
2656 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2657 G_MODULE||l_api_name,
2658 '150: Derived Template Id: '||l_template_id);
2659 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2660 G_MODULE||l_api_name,
2661 '150: Template Model Id :'||l_template_model_id);
2662 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2663 G_MODULE||l_api_name,
2664 '150: Intent :'||l_intent);
2665 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2666 G_MODULE||l_api_name,
2667 '150: Organization Name :'||G_ORGANIZATION_NAME);
2668 END IF;
2669
2670
2671 create_template_model
2672 (
2673 p_model_id => l_template_model_id,
2674 p_template_name => l_template_name,
2675 p_template_id => l_template_id,
2676 p_intent => l_intent,
2677 p_org_id => l_org_id,
2678 x_return_status => x_return_status,
2679 x_msg_data => x_msg_data,
2680 x_msg_count => x_msg_count
2681 );
2682
2683 -- debug log
2684 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2685 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2686 G_MODULE||l_api_name,
2687 '200: After Calling create_template_model x_return_status : '||x_return_status);
2688 END IF;
2689
2690 --- If any errors happen abort API
2691 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2692 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2693 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2694 RAISE FND_API.G_EXC_ERROR;
2695 END IF;
2696
2697 create_template_component
2698 (
2699 p_template_name => l_template_name,
2700 p_template_id => l_template_id,
2701 p_intent => l_intent,
2702 p_org_id => l_org_id,
2703 x_return_status => x_return_status,
2704 x_msg_data => x_msg_data,
2705 x_msg_count => x_msg_count
2706 );
2707
2708 -- debug log
2709 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2710 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2711 G_MODULE||l_api_name,
2712 '300: After Calling create_template_component x_return_status : '||x_return_status);
2713 END IF;
2714
2715 --- If any errors happen abort API
2716 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2717 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2718 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2719 RAISE FND_API.G_EXC_ERROR;
2720 END IF;
2721
2722 -- Create the Clause Model reference Node
2723 create_clause_model_ref
2724 (
2725 p_template_id => l_template_id,
2726 p_intent => l_intent,
2727 p_org_id => l_org_id,
2728 x_return_status => x_return_status,
2729 x_msg_data => x_msg_data,
2730 x_msg_count => x_msg_count
2731 );
2732
2733 -- debug log
2734 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2735 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2736 G_MODULE||l_api_name,
2737 '400: After Calling create_clause_model_ref x_return_status : '||x_return_status);
2738 END IF;
2739
2740 --- If any errors happen abort API
2741 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2742 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2743 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2744 RAISE FND_API.G_EXC_ERROR;
2745 END IF;
2746
2747 -- Questions on templates are created as features
2748 -- For getting questions, we pass the original template_id and the derived template id
2749 -- for Revision templates, p_template_id = Working copy template Id and
2750 -- p_derived_template_id = parent_template_id
2751
2752 create_template_feature
2753 (
2754 p_template_id => p_template_id, -- get questions using this id
2755 p_derived_template_id => l_template_id,
2756 p_intent => l_intent,
2757 p_org_id => l_org_id,
2758 x_return_status => x_return_status,
2759 x_msg_data => x_msg_data,
2760 x_msg_count => x_msg_count
2761 );
2762
2763 -- debug log
2764 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2765 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2766 G_MODULE||l_api_name,
2767 '400: After Calling create_template_feature x_return_status : '||x_return_status);
2768 END IF;
2769
2770 --- If any errors happen abort API
2771 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2772 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2773 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2774 RAISE FND_API.G_EXC_ERROR;
2775 END IF;
2776
2777 -- Call the CZ Generic Import to push data to CZ
2778 OKC_XPRT_CZ_INT_PVT.import_generic
2779 (
2780 p_api_version => l_api_version,
2781 p_run_id => G_RUN_ID,
2782 p_rp_folder_id => l_template_folder_id,
2783 x_run_id => l_run_id,
2784 x_return_status => l_import_status,
2785 x_msg_data => x_msg_data,
2786 x_msg_count => x_msg_count
2787 );
2788 -- debug log
2789 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2790 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2791 G_MODULE||l_api_name,
2792 '700: After Calling OKC_XPRT_CZ_INT_PVT.import_generic x_return_status : '||
2793 l_import_status);
2794 END IF;
2795
2796 -- Log the Import Status and check if any records in the import tables have status not 'OK'
2797 OKC_XPRT_UTIL_PVT.check_import_status
2798 (
2799 p_run_id => G_RUN_ID,
2800 p_import_status => l_import_status,
2801 p_model_type => 'T', -- Template Model
2802 x_return_status => x_return_status,
2803 x_msg_data => x_msg_data,
2804 x_msg_count => x_msg_count
2805 );
2806
2807 -- debug log
2808 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2809 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2810 G_MODULE||l_api_name,
2811 '700: After Calling OKC_XPRT_UTIL_PVT.check_import_status x_return_status : '||
2812 x_return_status);
2813 END IF;
2814
2815 --- If any errors happen abort API
2816 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2818 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2819 RAISE FND_API.G_EXC_ERROR;
2820 END IF;
2821
2822 -- initialize l_template_model_id
2823 l_template_model_id := NULL;
2824
2825 -- build the template OSR
2826 l_tmpl_orig_sys_ref := G_TEMPLATE_MODEL_OSR||l_org_id||':'||l_intent||':'||l_template_id;
2827
2828 -- Template Import was successful, update template record with the model_id
2829 /*
2830 OPEN csr_template_model_id(p_org_id => l_org_id,
2831 p_intent => l_intent,
2832 p_tmpl_id => l_template_id);
2833 */
2834 OPEN csr_template_model_id(p_orig_sys_ref => l_tmpl_orig_sys_ref);
2835 FETCH csr_template_model_id INTO l_template_model_id;
2836 CLOSE csr_template_model_id;
2837
2838 UPDATE okc_terms_templates_all
2839 SET template_model_id = l_template_model_id
2840 WHERE template_id = p_template_id;
2841
2842
2843
2844 IF FND_API.To_Boolean( p_commit ) THEN
2845 COMMIT WORK;
2846 END IF;
2847
2848 -- Standard call to get message count and if count is 1, get message info.
2849 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2850
2851 -- end debug log
2852 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2853 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2854 G_MODULE||l_api_name,
2855 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2856 END IF;
2857
2858 EXCEPTION
2859 WHEN FND_API.G_EXC_ERROR THEN
2860 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2861 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2862 G_MODULE||l_api_name,
2863 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2864 END IF;
2865
2866 x_return_status := G_RET_STS_ERROR ;
2867 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2868
2869 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2870 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2871 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2872 G_MODULE||l_api_name,
2873 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2874 END IF;
2875
2876 x_return_status := G_RET_STS_UNEXP_ERROR ;
2877 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2878
2879 WHEN OTHERS THEN
2880 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2881 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2882 G_MODULE||l_api_name,
2883 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
2884 END IF;
2885
2886 x_return_status := G_RET_STS_UNEXP_ERROR ;
2887 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2888 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2889 END IF;
2890 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2891
2892
2893 END import_template;
2894
2895
2896 /*====================================================================+
2897 Procedure Name : rebuild_tmpl_pub_disable
2898 Description : This is a PUBLIC API that rebuilds template Models
2899 This API is called from publish and disable rules concurrent programs
2900 Parameters:
2901
2902 +====================================================================*/
2903
2904 PROCEDURE rebuild_tmpl_pub_disable
2905 (
2906 x_return_status OUT NOCOPY VARCHAR2,
2907 x_msg_data OUT NOCOPY VARCHAR2,
2908 x_msg_count OUT NOCOPY NUMBER
2909 ) IS
2910
2911 -- Templates to be rebuilt for Publishing or Disabling Rules
2912 CURSOR csr_local_rules_templates IS
2913 -- Templates on Local Rules
2914 SELECT DISTINCT to_char(r.template_id)
2915 FROM okc_terms_templates_all t,
2916 okc_xprt_template_rules r,
2917 okc_xprt_rule_hdrs_all h
2918 WHERE r.template_id = t.template_id
2919 AND r.rule_id = h.rule_id
2920 AND t.status_code IN ('APPROVED','ON_HOLD')
2921 AND h.request_id = FND_GLOBAL.CONC_REQUEST_ID
2922 UNION
2923 -- templates already pushed to CZ
2924 SELECT DISTINCT SUBSTR(orig_sys_ref, INSTR(orig_sys_ref,':',-1,3)+1,
2925 (INSTR(orig_sys_ref,':',1,5) - (INSTR(orig_sys_ref,':',1,4)+1))
2926 /*SELECT DISTINCT SUBSTR(cz.orig_sys_ref, INSTR(cz.orig_sys_ref,':',-1,2)+1,
2927 (INSTR(cz.orig_sys_ref,':',-1,1) - (INSTR(cz.orig_sys_ref,':',-1,2)+1)) */
2928 )
2929 FROM cz_rules cz,
2930 okc_xprt_rule_hdrs_all h
2931 WHERE SUBSTR(cz.orig_sys_ref,INSTR(cz.orig_sys_ref,':',-1,1)+1) = to_char(h.rule_id)
2932 AND h.request_id = FND_GLOBAL.CONC_REQUEST_ID
2933 AND cz.deleted_flag = '0'
2934 AND cz.rule_type = 200; --Perf Bug#5030272 Added rule_type = 200
2935
2936 CURSOR csr_org_rules_templates(p_org_id IN NUMBER) IS
2937 -- Org Wide Rule Templates
2938 SELECT t.template_id
2939 FROM okc_terms_templates_all t
2940 WHERE t.org_id = p_org_id
2941 AND t.intent IN (
2942 SELECT DISTINCT intent
2943 FROM okc_xprt_rule_hdrs_all
2944 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
2945 )
2946 AND t.contract_expert_enabled = 'Y'
2947 AND t.status_code IN ('APPROVED','ON_HOLD');
2948
2949 -- Cursor to check if any rule is Org Wide
2950 CURSOR csr_org_rule_exists IS
2951 SELECT 'X'
2952 FROM okc_xprt_rule_hdrs_all
2953 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
2954 AND NVL(org_wide_flag,'N') = 'Y';
2955
2956 -- Get the Rule Org Id
2957 CURSOR csr_rule_org_id IS
2958 SELECT org_id
2959 FROM okc_xprt_rule_hdrs_all
2960 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID;
2961
2962 l_api_name CONSTANT VARCHAR2(30) := 'rebuild_tmpl_pub_disable';
2963 l_template_id okc_terms_templates_all.template_id%TYPE;
2964 l_org_rules_yn okc_xprt_rule_hdrs_all.org_wide_flag%TYPE := NULL;
2965 l_org_id okc_xprt_rule_hdrs_all.org_id%TYPE;
2966
2967 BEGIN
2968 -- start debug log
2969 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2970 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2971 G_MODULE||l_api_name,
2972 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
2973 END IF;
2974
2975 -- Initialize API return status to success
2976 x_return_status := FND_API.G_RET_STS_SUCCESS;
2977
2978 -- Check if any rules in current request are Org Wide Rules
2979 OPEN csr_org_rule_exists;
2980 FETCH csr_org_rule_exists INTO l_org_rules_yn;
2981 CLOSE csr_org_rule_exists;
2982
2983 -- Get the Rule Org Id
2984 OPEN csr_rule_org_id;
2985 FETCH csr_rule_org_id INTO l_org_id;
2986 CLOSE csr_rule_org_id;
2987
2988 -- debug log
2989 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2990 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2991 G_MODULE||l_api_name,
2992 '110: l_org_rules_yn '||l_org_rules_yn);
2993 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
2994 G_MODULE||l_api_name,
2995 '110: l_org_id '||l_org_id);
2996 END IF;
2997
2998
2999
3000 IF l_org_rules_yn IS NULL THEN
3001 -- No Org Wide Rules in current concurrent request
3002 -- Open the Local Csr
3003 OPEN csr_local_rules_templates;
3004 LOOP
3005 FETCH csr_local_rules_templates INTO l_template_id;
3006 EXIT WHEN csr_local_rules_templates%NOTFOUND;
3007 import_template
3008 (
3009 p_api_version => 1,
3010 p_init_msg_list => 'T',
3011 p_commit => 'T',
3012 p_template_id => l_template_id,
3013 x_return_status => x_return_status,
3014 x_msg_data => x_msg_data,
3015 x_msg_count => x_msg_count
3016 ) ;
3017
3018 --- If any errors happen abort API
3019 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3020 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3021 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3022 RAISE FND_API.G_EXC_ERROR;
3023 END IF;
3024
3025 END LOOP; -- local
3026 CLOSE csr_local_rules_templates;
3027
3028 ELSE
3029 -- Org Wide Rules exists in current concurrent request
3030 -- Open the Org Wide Cursor
3031 OPEN csr_org_rules_templates(p_org_id => l_org_id);
3032 LOOP
3033 FETCH csr_org_rules_templates INTO l_template_id;
3034 EXIT WHEN csr_org_rules_templates%NOTFOUND;
3035 import_template
3036 (
3037 p_api_version => 1,
3038 p_init_msg_list => 'T',
3039 p_commit => 'T',
3040 p_template_id => l_template_id,
3041 x_return_status => x_return_status,
3042 x_msg_data => x_msg_data,
3043 x_msg_count => x_msg_count
3044 ) ;
3045
3046 --- If any errors happen abort API
3047 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3048 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3049 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3050 RAISE FND_API.G_EXC_ERROR;
3051 END IF;
3052
3053 END LOOP; -- Org Rules Templates
3054 CLOSE csr_org_rules_templates;
3055
3056 END IF;
3057
3058 -- Standard call to get message count and if count is 1, get message info
3059 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3060
3061 -- end debug log
3062 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3063 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3064 G_MODULE||l_api_name,
3065 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
3066 END IF;
3067
3068 EXCEPTION
3069 WHEN FND_API.G_EXC_ERROR THEN
3070 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3071 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3072 G_MODULE||l_api_name,
3073 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
3074 END IF;
3075
3076 x_return_status := G_RET_STS_ERROR ;
3077 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3078
3079 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3080 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3081 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3082 G_MODULE||l_api_name,
3083 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
3084 END IF;
3085
3086 x_return_status := G_RET_STS_UNEXP_ERROR ;
3087 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3088
3089 WHEN OTHERS THEN
3090 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3091 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3092 G_MODULE||l_api_name,
3093 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
3094 END IF;
3095
3096 x_return_status := G_RET_STS_UNEXP_ERROR ;
3097 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3098 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3099 END IF;
3100 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3101
3102
3103 END rebuild_tmpl_pub_disable;
3104
3105
3106
3107
3108 /*====================================================================+
3109 Procedure Name : rebuild_tmpl_sync
3110 Description : This is a PUBLIC API that rebuilds template Models
3111 This API is called from sync template concurrent programs
3112 Parameters:
3113 p_org_id : Org Id in which templates are to be rebuilt
3114 p_intent : Intent of templates to be rebuilt
3115 p_template_id : Template Id to be rebuilt. If the template_id is
3116 NOT passed, ALL templates for the above Org and Intent are
3117 rebuilt
3118 +====================================================================*/
3119
3120 PROCEDURE rebuild_tmpl_sync
3121 (
3122 p_org_id IN NUMBER,
3123 p_intent IN VARCHAR2,
3124 p_template_id IN NUMBER DEFAULT NULL,
3125 x_return_status OUT NOCOPY VARCHAR2,
3126 x_msg_data OUT NOCOPY VARCHAR2,
3127 x_msg_count OUT NOCOPY NUMBER
3128 ) IS
3129
3130 CURSOR csr_templates IS
3131 SELECT t.template_id
3132 FROM okc_terms_templates_all t
3133 WHERE t.org_id = p_org_id
3134 AND t.intent = p_intent
3135 AND t.template_id = NVL(p_template_id, template_id)
3136 AND t.contract_expert_enabled = 'Y'
3137 AND t.status_code IN ('APPROVED','ON_HOLD') ;
3138
3139
3140 l_api_name CONSTANT VARCHAR2(30) := 'rebuild_tmpl_pub_disable';
3141 l_template_id okc_terms_templates_all.template_id%TYPE;
3142
3143
3144 BEGIN
3145 -- start debug log
3146 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3147 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3148 G_MODULE||l_api_name,
3149 '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
3150 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3151 G_MODULE||l_api_name,
3152 '100: Parameters ');
3153 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3154 G_MODULE||l_api_name,
3155 '100: p_org_id '||p_org_id);
3156 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3157 G_MODULE||l_api_name,
3158 '100: p_intent '||p_intent);
3159 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3160 G_MODULE||l_api_name,
3161 '100: p_template_id '||p_template_id);
3162 END IF;
3163
3164 -- Initialize API return status to success
3165 x_return_status := FND_API.G_RET_STS_SUCCESS;
3166
3167 -- Get all the templates to be rebuilt
3168 OPEN csr_templates;
3169 LOOP
3170 FETCH csr_templates INTO l_template_id;
3171 EXIT WHEN csr_templates%NOTFOUND;
3172
3173 import_template
3174 (
3175 p_api_version => 1,
3176 p_init_msg_list => 'T',
3177 p_commit => 'T',
3178 p_template_id => l_template_id,
3179 x_return_status => x_return_status,
3180 x_msg_data => x_msg_data,
3181 x_msg_count => x_msg_count
3182 ) ;
3183
3184 --- If any errors happen abort API
3185 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3187 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3188 RAISE FND_API.G_EXC_ERROR;
3189 END IF;
3190
3191 END LOOP;
3192 CLOSE csr_templates;
3193
3194
3195 -- Standard call to get message count and if count is 1, get message info
3196 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3197
3198 -- end debug log
3199 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3200 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3201 G_MODULE||l_api_name,
3202 '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
3203 END IF;
3204
3205 EXCEPTION
3206 WHEN FND_API.G_EXC_ERROR THEN
3207 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3208 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3209 G_MODULE||l_api_name,
3210 '2000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
3211 END IF;
3212
3213 x_return_status := G_RET_STS_ERROR ;
3214 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3215
3216 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3217 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3218 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3219 G_MODULE||l_api_name,
3220 '3000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
3221 END IF;
3222
3223 x_return_status := G_RET_STS_UNEXP_ERROR ;
3224 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3225
3226 WHEN OTHERS THEN
3227 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3228 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
3229 G_MODULE||l_api_name,
3230 '4000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
3231 END IF;
3232
3233 x_return_status := G_RET_STS_UNEXP_ERROR ;
3234 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3235 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3236 END IF;
3237 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3238
3239
3240 END rebuild_tmpl_sync;
3241
3242
3243
3244
3245 END OKC_XPRT_IMPORT_TEMPLATE_PVT;