4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CZ_CF_API';
1 PACKAGE BODY CZ_CF_API AS
2 /* $Header: czcfapib.pls 120.28.12020000.2 2012/08/17 14:41:13 smanna ship $ */
3 ------------------------------------------------------------------------------------------
5
6 TYPE str255_tbl_type IS TABLE OF VARCHAR2(255) INDEX BY PLS_INTEGER; -- name, ...
7 TYPE str1200_tbl_type IS TABLE OF VARCHAR2(1200) INDEX BY PLS_INTEGER; -- component code,...
8
9 last_hdr_allocated INTEGER :=NULL;
10 next_hdr_to_use INTEGER :=0;
14 DEFAULT_INCR CONSTANT PLS_INTEGER :=20;
11 last_msg_seq_allocated NUMBER := NULL;
12 next_msg_seq_to_use NUMBER := 0;
13 id_increment INTEGER;
15
16 c_application_id VARCHAR2(255);
17 c_usage_name VARCHAR2(255);
18
19 UI_STYLE_DHTML CONSTANT VARCHAR2(3) := '0';
20 UI_STYLE_APPLET CONSTANT VARCHAR2(3) := '3';
21 UI_STYLE_JRAD CONSTANT VARCHAR2(3) := '7';
22 UI_STYLE_WEGA CONSTANT VARCHAR2(3) := '8';
23
24 NATIVEBOM_UI_TYPE CONSTANT VARCHAR2(20) := 'JRAD';
25
26 BOM_ITEM_TYPE_MODEL CONSTANT NUMBER := 1;
27
28 PS_NODE_TYPE_REFERENCE CONSTANT NUMBER := 263;
29
30 ANY_APPLICATION_ID CONSTANT NUMBER := -1;
31 ANY_USAGE_ID CONSTANT NUMBER := -1;
32 ANY_USAGE_NAME CONSTANT VARCHAR2(20) := 'Any Usage';
33 TARGET_PUBLICATION CONSTANT VARCHAR2(1) := 'T';
34
35 -- model_type/model_instantiation_type
36 NETWORK CONSTANT VARCHAR2(1) := 'N';
37
38 -- component_instance_type
39 ROOT CONSTANT VARCHAR2(1) := 'R';
40 GENERIC_INSTANCE_ROOT CONSTANT VARCHAR2(1) := 'C';
41 NETWORK_INSTANCE_ROOT CONSTANT VARCHAR2(1) := 'I';
42 INCLUDED CONSTANT VARCHAR2(1) := 'T';
43
44 INVALID_OPTION_EXCEPTION EXCEPTION;
45 ZERO_RESPONSE_LENGTH EXCEPTION;
46 WRONG_ARRAYS_LENGTH EXCEPTION;
47 CONFIG_HDR_TYPE_EXC EXCEPTION;
48 --vsingava 03 Sep '09 ER8689105
49 MODEL_POOL_EFFINITY_EXC EXCEPTION;
50 -- vsingava 14 Jul '09 bug7674190
51 MISSING_CALLING_APPL_ID EXCEPTION;
52 transferTimeout PLS_INTEGER := NULL;
53 defaultTimeout PLS_INTEGER := NULL;
54
55 ----constants used in check deltas
56 ITEM_DELETE_MESSAGE CONSTANT VARCHAR2(30) := 'CZ_BATCH_VAL_ITEM_DELETED';
57 ITEM_ADD_MESSAGE CONSTANT VARCHAR2(30) := 'CZ_BATCH_VAL_ITEM_ADDED';
58 QTY_CHANGE_MESSAGE CONSTANT VARCHAR2(30) := 'CZ_BATCH_VAL_DIFF';
59
60 -- operation code for the old bv behavior: set quantity
61 BV_OPERATION_OLD CONSTANT INTEGER := 0;
62
63 -- pseudo model type
64 BV_MODEL_TYPE CONSTANT VARCHAR2(1) := 'C';
65
66 --------------------------------------------------------------------------------
67 FUNCTION get_db_setting (p_section_name IN VARCHAR2, p_setting IN VARCHAR2)
68 RETURN VARCHAR2 IS
69 l_ret_value cz_db_settings.value%TYPE;
70 BEGIN
71 SELECT value INTO l_ret_value FROM cz_db_settings WHERE Upper(section_name)
72 = Upper(p_section_name) AND Upper(setting_id) = Upper(p_setting);
73 RETURN l_ret_value;
74 END;
75
76 ------------------------------------------------------------------------------------------------
77 FUNCTION convert_to_date(p_date_in_str IN VARCHAR2)
78 RETURN DATE IS
79 BEGIN
80 -- vsingava 30th Jan '11. Bug11697830.
81 -- CZ has an explicit contract to have date passed in format of MM-DD-RRRR-HH24-MI-SS or MM-DD-RRRR
82 RETURN TO_DATE(p_date_in_str, 'MM-DD-RRRR-HH24-MI-SS');
83 END convert_to_date;
84 ------------------------------------------------------------------------------------------------
85 FUNCTION usage_id_from_usage_name (p_usage_name IN VARCHAR2)
86 RETURN NUMBER
87 IS
88 v_usage_id NUMBER;
89 BEGIN
90
91 IF p_usage_name IS NOT NULL THEN
92 BEGIN
93 SELECT model_usage_id
94 INTO v_usage_id
95 FROM CZ_MODEL_USAGES
96 WHERE LTRIM(RTRIM(UPPER(CZ_MODEL_USAGES.name))) = LTRIM(RTRIM(UPPER(p_usage_name)))
97 AND cz_model_usages.in_use = '1';
98 EXCEPTION
99 WHEN OTHERS THEN
100 v_usage_id := ANY_USAGE_ID;
101 END;
102 ELSE
103 v_usage_id := ANY_USAGE_ID;
104 END IF;
105
106 RETURN v_usage_id;
107 END usage_id_from_usage_name;
108
109 ------------------------------------------------------------------------------------------
110 -- The next two functions convert between ui_style ('0' and '3')
111 -- and the ui_type input ('DHTML' and 'APPLET')
112
113 FUNCTION ui_style_from_ui_type (ui_type IN VARCHAR2)
114 RETURN VARCHAR2 IS
115 v_ui_style VARCHAR2(3);
116 BEGIN
117 SELECT DECODE(ui_type, 'APPLET', UI_STYLE_APPLET, 'DHTML', UI_STYLE_DHTML,
118 'JRAD', UI_STYLE_JRAD, 'WEGA', UI_STYLE_WEGA, NULL)
119 INTO v_ui_style
120 FROM dual;
121 RETURN v_ui_style;
122 END;
123
124 ------------------------------------------------------------------------------------------
125
126 FUNCTION ui_type_from_ui_style (ui_style IN VARCHAR2)
127 RETURN VARCHAR2 IS
128 v_ui_type VARCHAR2(30);
129 BEGIN
130 SELECT DECODE(ui_style, UI_STYLE_APPLET, 'APPLET', UI_STYLE_DHTML, 'DHTML',
131 UI_STYLE_JRAD, 'JRAD', UI_STYLE_WEGA, 'WEGA', NULL)
132 INTO v_ui_type
133 FROM dual;
134 RETURN v_ui_type;
135 END;
136
137 ------------------------------------------------------------------------------------------
138 FUNCTION NEXT_CONFIG_HDR_ID RETURN INTEGER IS
139 ID_to_return INTEGER;
140 BEGIN
141 IF ( (last_hdr_allocated IS NULL)
142 OR
143 (next_hdr_to_use = (NVL(last_hdr_allocated, 0) + id_increment)) ) THEN
144 SELECT cz_config_hdrs_s.NEXTVAL
145 INTO last_hdr_allocated
146 FROM dual;
147 next_hdr_to_use := last_hdr_allocated;
148 END IF;
149 id_to_return := next_hdr_to_use;
150 next_hdr_to_use := next_hdr_to_use + 1;
151 RETURN id_to_return;
152 END ;
153
154 --------------------------------------------------------------------------------
155 FUNCTION get_next_msg_seq RETURN NUMBER
156 IS
157 l_msg_seq NUMBER;
161 (next_msg_seq_to_use = last_msg_seq_allocated + id_increment)) THEN
158
159 BEGIN
160 IF ((last_msg_seq_allocated IS NULL) OR
162 SELECT cz_config_messages_s.NEXTVAL INTO last_msg_seq_allocated FROM dual;
163 next_msg_seq_to_use := last_msg_seq_allocated;
164 END IF;
165
166 l_msg_seq := next_msg_seq_to_use;
167 next_msg_seq_to_use := next_msg_seq_to_use + 1;
168 RETURN l_msg_seq;
172 -- Note: Returns a message having maiximum length of 1000 chars, i.e., message will be
169 END get_next_msg_seq;
170
171 ------------------------------------------------------------------------------------------
173 -- trancated if its length is more than 1000.
174 FUNCTION retrieve_log_msg(p_run_id IN NUMBER) RETURN VARCHAR2
175 IS
176 l_msg VARCHAR2(3000) := 'RUN_ID=' || to_char(p_run_id) || ':';
177
178 CURSOR log_msg_csr IS
179 SELECT message
180 FROM cz_db_logs
181 WHERE run_id = p_run_id
182 ORDER BY logtime;
183
184 BEGIN
185 FOR msg_rec IN log_msg_csr LOOP
186 l_msg := l_msg || ' ' || msg_rec.message;
187 EXIT WHEN (length(l_msg) > 999) OR log_msg_csr%NOTFOUND;
188 END LOOP;
189
190 RETURN substr(l_msg, 1, 1000);
191 END retrieve_log_msg;
192
193 ------------------------------------------------------------------------------------
194 PROCEDURE copy_configuration(config_hdr_id IN NUMBER,
195 config_rev_nbr IN NUMBER,
196 new_config_flag IN VARCHAR2,
197 out_config_hdr_id IN OUT NOCOPY NUMBER,
198 out_config_rev_nbr IN OUT NOCOPY NUMBER,
199 error_message IN OUT NOCOPY VARCHAR2,
200 return_value IN OUT NOCOPY NUMBER,
201 handle_deleted_flag IN VARCHAR2 DEFAULT NULL,
202 new_name IN VARCHAR2 DEFAULT NULL)
203 IS
204 l_api_version CONSTANT NUMBER := 1.0;
205 l_copy_mode VARCHAR2(1);
206 l_ret_status VARCHAR2(1);
207 l_msg_count INTEGER;
208
209 l_orig_item_id_tbl CZ_API_PUB.number_tbl_type;
210 l_new_item_id_tbl CZ_API_PUB.number_tbl_type;
211 API_CALL_EXC EXCEPTION;
212
213 BEGIN
214 IF ((new_config_flag IS NOT NULL) AND
215 (new_config_flag='0')) THEN
216 l_copy_mode := CZ_API_PUB.G_NEW_REVISION_COPY_MODE;
217 ELSE
218 l_copy_mode := CZ_API_PUB.G_NEW_HEADER_COPY_MODE;
219 END IF;
220
221 cz_config_api_pub.copy_configuration
222 (l_api_version
223 ,config_hdr_id
224 ,config_rev_nbr
225 ,l_copy_mode
226 ,out_config_hdr_id
227 ,out_config_rev_nbr
228 ,l_orig_item_id_tbl
229 ,l_new_item_id_tbl
230 ,l_ret_status
231 ,l_msg_count
232 ,error_message
233 ,handle_deleted_flag
234 ,new_name
235 );
236
237 IF (l_ret_status = FND_API.G_RET_STS_SUCCESS) THEN
238 IF (l_orig_item_id_tbl.count > 0) THEN
239 RAISE API_CALL_EXC;
240 END IF;
241 return_value := 1;
242 ELSE
243 return_value := 0;
244 END IF;
245
246 EXCEPTION
247 WHEN API_CALL_EXC THEN
248 return_value:=0;
249 error_message:=CZ_UTILS.GET_TEXT('CZ_INCOMPAT_COPY_CFG');
250 -- xERROR:=CZ_UTILS.REPORT(error_message,1,'CZ_CF_API: copy configuration',11276);
251 cz_utils.log_report('CZ_CF_API', 'copy_configuration', null,
252 error_message, fnd_log.LEVEL_ERROR);
253
254 WHEN OTHERS THEN
255 return_value:=0;
256 error_message:=SQLERRM;
257 -- xERROR:=CZ_UTILS.REPORT(error_message,1,'CZ_CF_API: copy configuration',11276);
258 cz_utils.log_report('CZ_CF_API', 'copy_configuration', null,
259 error_message, fnd_log.LEVEL_UNEXPECTED);
260 END copy_configuration;
261
262 -------------------------------------------------------------------------------------
263 PROCEDURE copy_configuration_auto(config_hdr_id IN NUMBER,
264 config_rev_nbr IN NUMBER,
265 new_config_flag IN VARCHAR2,
266 out_config_hdr_id IN OUT NOCOPY NUMBER,
267 out_config_rev_nbr IN OUT NOCOPY NUMBER,
268 Error_message IN OUT NOCOPY VARCHAR2,
269 Return_value IN OUT NOCOPY NUMBER,
273
270 handle_deleted_flag IN VARCHAR2 DEFAULT NULL,
271 new_name IN VARCHAR2 DEFAULT NULL)
272 IS
274 PRAGMA AUTONOMOUS_TRANSACTION;
275 BEGIN
276 CZ_CF_API.copy_configuration(config_hdr_id,config_rev_nbr,new_config_flag,out_config_hdr_id,
277 out_config_rev_nbr,Error_message,Return_value,handle_deleted_flag, new_name);
278 COMMIT;
279 END copy_configuration_auto;
280
281 ------------------------------------------------------------------------------------------
282 PROCEDURE delete_configuration(config_hdr_id IN NUMBER,
283 config_rev_nbr IN NUMBER,
284 usage_exists IN OUT NOCOPY NUMBER,
285 Error_message IN OUT NOCOPY VARCHAR2,
286 Return_value IN OUT NOCOPY NUMBER)
287 IS
288 in_config_hdr_id NUMBER := config_hdr_id;
289 in_config_rev_nbr NUMBER := config_rev_nbr;
290 l_model_instantiation_type cz_config_hdrs.model_instantiation_type%TYPE;
291 l_component_instance_type cz_config_hdrs.component_instance_type%TYPE;
292 l_instance_hdr_id_tbl number_tbl_indexby_type;
293 l_instance_rev_nbr_tbl number_tbl_indexby_type;
294 l_run_id NUMBER;
295 l_ndebug NUMBER := 0;
296
297 del_ib_config_exc EXCEPTION;
298 ib_exception EXCEPTION;
299 l_active_instances_found NUMBER;
300 l_number_of_active_instances NUMBER;
301 l_return_revision NUMBER;
302 l_ib_error NUMBER;
303
304 v_table_name VARCHAR2(100) := NULL;
305
306 TYPE instype_tbl_type IS TABLE OF cz_config_hdrs.component_instance_type%TYPE INDEX BY PLS_INTEGER;
307 l_instance_type_tbl instype_tbl_type;
308
309 BEGIN
310 Return_value:=1;
311 Error_message:='';
312 usage_exists:=1;
313 l_active_instances_found := 0;
314 l_number_of_active_instances := 0;
315 l_ib_error := 0;
316
317 SAVEPOINT start_transaction;
318
319 -- input config must be network container or non-network config
320 -- i.e., component_instance_type must be 'R'
321 BEGIN
322 SELECT model_instantiation_type, component_instance_type
323 INTO l_model_instantiation_type, l_component_instance_type
324 FROM cz_config_hdrs
325 WHERE config_hdr_id = in_config_hdr_id AND config_rev_nbr = in_config_rev_nbr;
326 EXCEPTION
327 WHEN NO_DATA_FOUND THEN
328 RETURN;
329 END;
330
331 l_ndebug := 1;
332 IF (l_component_instance_type <> ROOT) THEN
333 RAISE CONFIG_HDR_TYPE_EXC;
334 END IF;
335
336 SELECT instance_hdr_id, instance_rev_nbr, component_instance_type
337 BULK COLLECT INTO l_instance_hdr_id_tbl, l_instance_rev_nbr_tbl, l_instance_type_tbl
338 FROM cz_config_items
339 WHERE config_hdr_id = in_config_hdr_id AND config_rev_nbr = in_config_rev_nbr
340 AND deleted_flag = '0'
341 AND component_instance_type IN (GENERIC_INSTANCE_ROOT, NETWORK_INSTANCE_ROOT);
342
343 l_ndebug := 2;
344
345 -- lkattamu; bug 5289742; Loop through the config instances
346 IF (l_model_instantiation_type = NETWORK AND l_instance_hdr_id_tbl.COUNT > 0) THEN
347 FOR i IN l_instance_hdr_id_tbl.FIRST..l_instance_hdr_id_tbl.LAST LOOP
348 l_ndebug := 3;
349 IF l_instance_type_tbl(i) = NETWORK_INSTANCE_ROOT THEN
350 EXECUTE IMMEDIATE
351 'DECLARE ' ||
352 ' p_install_config_rec csi_cz_int.config_rec; ' ||
353 ' p_return_status VARCHAR2(10); ' ||
354 ' p_return_message VARCHAR2(2000); ' ||
355 ' p_instance_level VARCHAR2(1000); ' ||
356 ' api_result NUMBER := 0; ' ||
357 ' l_ib_errored NUMBER := 0; ' ||
358 'BEGIN ' ||
359 ' csi_cz_int.get_configuration_revision ( ' ||
360 ' p_config_header_id => :instance_hdr_id, ' ||
361 ' p_target_commitment_date => NULL, ' ||
362 ' px_instance_level => p_instance_level, ' ||
363 ' x_install_config_rec => p_install_config_rec, ' ||
364 ' x_return_status => p_return_status, ' ||
365 ' x_return_message => p_return_message ' ||
366 ' ); ' ||
367 ' IF (p_return_status <> fnd_api.g_ret_sts_success) THEN ' ||
368 ' l_ib_errored := 1; ' ||
369 ' ELSE ' ||
370 ' IF (p_install_config_rec.config_inst_rev_num IS NULL) THEN ' ||
371 ' api_result := 0; ' ||
372 ' ELSE ' ||
373 ' api_result := 1; ' ||
374 ' END IF; ' ||
375 ' END IF; ' ||
376 ' :result := api_result; ' ||
377 ' :l_return_revision := p_install_config_rec.config_inst_rev_num; ' ||
378 ' :l_ib_error := l_ib_errored; ' ||
379 ' :error_message := p_return_message; ' ||
380 'END; '
381 USING IN l_instance_hdr_id_tbl(i), OUT l_active_instances_found, OUT l_return_revision, OUT l_ib_error, OUT error_message;
382 IF (l_ib_error = 1) THEN
383 -- Return to the caller, without processing other instances, when there is an IB exception
384 RAISE ib_exception; -- This call just takes control to WHEN OTHERS exception handler
385 END IF;
386 -- Check if the active instance's revision is same as the revision in the config instance
387 IF ((l_active_instances_found = 1) AND (l_instance_rev_nbr_tbl(i) = l_return_revision)) THEN
388 l_number_of_active_instances := 1;
389 EXIT;
390 END IF;
391 END IF;
392 END LOOP;
393 END IF;
394
395 l_ndebug := 4;
396 IF (l_number_of_active_instances = 0) THEN
397 usage_exists:=0;
398 -- No ative instance exists, go ahead and delete
399 DELETE FROM CZ_CONFIG_ATTRIBUTES
400 WHERE CONFIG_HDR_ID=in_config_hdr_id
401 AND CONFIG_REV_NBR=in_config_rev_nbr;
402
406
403 DELETE FROM CZ_CONFIG_ITEMS
404 WHERE CONFIG_HDR_ID=in_config_hdr_id
405 AND CONFIG_REV_NBR=in_config_rev_nbr;
407 DELETE FROM CZ_CONFIG_INPUTS
408 WHERE CONFIG_HDR_ID=in_config_hdr_id
409 AND CONFIG_REV_NBR=in_config_rev_nbr;
410
411 DELETE FROM CZ_CONFIG_MESSAGES
412 WHERE CONFIG_HDR_ID=in_config_hdr_id
413 AND CONFIG_REV_NBR=in_config_rev_nbr;
414
415 --Bug10147623: Delete data from custom config table.
416 BEGIN
417 SELECT UPPER(LTRIM(RTRIM(value))) INTO v_table_name FROM cz_db_settings
418 WHERE UPPER(setting_id) = 'CUSTOM_CONFIG_TABLE'
419 AND UPPER(section_name) = 'CONFIGURATION';
420
421 EXCEPTION WHEN NO_DATA_FOUND THEN
422 v_table_name := NULL;
423 END;
424
425 IF (v_table_name IS NOT NULL) THEN
426
427 EXECUTE IMMEDIATE ' DELETE FROM '||v_table_name||' WHERE CONFIG_HDR_ID = :1 AND CONFIG_REV_NBR = :2 '
428 USING in_config_hdr_id, in_config_rev_nbr;
429
430 END IF;
431
432 l_ndebug := 5;
433
434 IF (l_model_instantiation_type = NETWORK AND l_instance_hdr_id_tbl.COUNT > 0) THEN
435 FOR i IN l_instance_hdr_id_tbl.FIRST .. l_instance_hdr_id_tbl.LAST LOOP
436 IF l_instance_type_tbl(i) = NETWORK_INSTANCE_ROOT THEN
437 DELETE FROM CZ_CONFIG_EXT_ATTRIBUTES
438 WHERE CONFIG_HDR_ID = l_instance_hdr_id_tbl(i)
439 AND CONFIG_REV_NBR = l_instance_rev_nbr_tbl(i);
440 END IF;
441 END LOOP;
442 END IF;
443
444 l_ndebug := 6;
445
446 l_instance_hdr_id_tbl(l_instance_hdr_id_tbl.COUNT+1) := in_config_hdr_id;
447 l_instance_rev_nbr_tbl(l_instance_rev_nbr_tbl.COUNT+1) := in_config_rev_nbr;
448 FORALL i IN l_instance_hdr_id_tbl.FIRST .. l_instance_hdr_id_tbl.LAST
449 DELETE FROM CZ_CONFIG_HDRS
450 WHERE CONFIG_HDR_ID = l_instance_hdr_id_tbl(i)
451 AND CONFIG_REV_NBR = l_instance_rev_nbr_tbl(i);
452
453 l_ndebug := 7;
454
455 -- delete ib data if necessary
456 cz_ib_transactions.remove_ib_config(p_session_config_hdr_id => in_config_hdr_id
457 ,p_session_config_rev_nbr => in_config_rev_nbr
458 ,x_run_id => l_run_id
459 );
460 IF (l_run_id <> 0) THEN
461 RAISE del_ib_config_exc;
462 END IF;
463 ELSE
464 -- Mark the configuration session as "To Be Deleted" as few config instances are still active
465 UPDATE cz_config_hdrs
466 SET to_be_deleted_flag = '1'
467 WHERE config_hdr_id=in_config_hdr_id and
468 config_rev_nbr = in_config_rev_nbr;
469 END IF;
470
471 EXCEPTION
472 WHEN CONFIG_HDR_TYPE_EXC THEN
473 Return_value:=0;
474 Error_message:=CZ_UTILS.GET_TEXT('CZ_CFG_DEL_HDR_TYPE', 'id', in_config_hdr_id,
475 'revision', in_config_rev_nbr, 'type', l_component_instance_type);
476 -- xERROR:=CZ_UTILS.REPORT(Error_message,1,'CZ_CF_API.delete_configuration',11276);
477 cz_utils.log_report('CZ_CF_API', 'delete_configuration', l_ndebug, error_message,
478 fnd_log.LEVEL_ERROR);
479
480 WHEN del_ib_config_exc THEN
481 Return_value := 0;
482 Error_message := retrieve_log_msg(l_run_id);
483 ROLLBACK TO start_transaction;
487 WHEN OTHERS THEN
484 cz_utils.log_report('CZ_CF_API', 'delete_configuration', l_ndebug, error_message,
485 fnd_log.LEVEL_ERROR);
486
488 Return_value:=0;
489 IF (error_message IS NOT NULL) THEN
490 Error_message := CZ_UTILS.GET_TEXT('CZ_CFG_DEL_ERROR', 'HDRID', in_config_hdr_id, 'REVNBR', in_config_rev_nbr, 'ERRMSG', error_message);
491 ELSE
492 Error_message := CZ_UTILS.GET_TEXT('CZ_CFG_DEL_ERROR', 'HDRID', in_config_hdr_id, 'REVNBR', in_config_rev_nbr, 'ERRMSG', SQLERRM);
493 END IF;
494 ROLLBACK TO start_transaction;
495 -- xERROR:=CZ_UTILS.REPORT(Error_message,1,'CZ_CF_API.delete_configuration',11276);
496 cz_utils.log_report('CZ_CF_API', 'delete_configuration', l_ndebug, error_message, fnd_log.LEVEL_UNEXPECTED);
497 END delete_configuration;
498
499 ------------------------------------------------------------------------------------------
500 PROCEDURE delete_configuration_usage(calling_application_id IN NUMBER,
501 calling_application_ref_key IN NUMBER,
502 Error_message IN OUT NOCOPY VARCHAR2,
503 Return_value IN OUT NOCOPY NUMBER)
504 IS
505 in_calling_application_id NUMBER:=calling_application_id;
506 in_calling_application_ref_key NUMBER:=calling_application_ref_key;
507
508 BEGIN
512 DELETE FROM CZ_CONFIG_USAGES WHERE CALLING_APPLICATION_ID=in_calling_application_id AND
509 Return_value:=1;
510 Error_message:='';
511 SAVEPOINT start_transaction;
513 CALLING_APPLICATION_REF_KEY=in_calling_application_ref_key;
514 EXCEPTION
515 WHEN OTHERS THEN
516 Return_value:=0;
517 Error_message:=SQLERRM;
518 ROLLBACK TO start_transaction;
519 -- xERROR:=CZ_UTILS.REPORT(Error_message,1,'CZ_CF_API: delete configuration usage',11276);
520 cz_utils.log_report('CZ_CF_API', 'delete_configuration_usage', 1, error_message,
521 fnd_log.LEVEL_UNEXPECTED);
522 END delete_configuration_usage;
523 ------------------------------------------------------------------------------------------
524 PROCEDURE update_configuration_usage(calling_application_id IN NUMBER,
525 calling_application_ref_key IN NUMBER,
526 config_hdr_id IN NUMBER,
527 config_rev_nbr IN NUMBER,
528 config_item_id IN NUMBER,
529 uom_code IN VARCHAR2,
530 list_price IN NUMBER,
531 discounted_price IN NUMBER,
532 auto_discount_id IN NUMBER,
533 auto_discount_line_id IN NUMBER,
534 auto_discount_pct IN NUMBER,
535 manual_discount_id IN NUMBER,
536 manual_discount_line_id IN NUMBER,
537 manual_discount_pct IN NUMBER,
538 Error_message IN OUT NOCOPY VARCHAR2,
539 Return_value IN OUT NOCOPY NUMBER)
540
541 IS
542 in_calling_application_id NUMBER:=calling_application_id;
543 in_calling_application_ref_key NUMBER:=calling_application_ref_key;
544 in_config_hdr_id NUMBER:=config_hdr_id;
545 in_config_rev_nbr NUMBER:=config_rev_nbr;
546 in_config_item_id NUMBER:=config_item_id;
547 in_uom_code VARCHAR2(3):=uom_code;
548 in_list_price NUMBER:=list_price;
549 in_discounted_price NUMBER:=discounted_price;
550 in_auto_discount_id NUMBER:=auto_discount_id;
551 in_auto_discount_line_id NUMBER:=auto_discount_line_id;
552 in_auto_discount_pct NUMBER:=auto_discount_pct;
553 in_manual_discount_id NUMBER:=manual_discount_id;
554 in_manual_discount_line_id NUMBER:=manual_discount_line_id;
555 in_manual_discount_pct NUMBER:=manual_discount_pct;
556
557 BEGIN
558 Return_value:=1;
559 Error_message:='';
560 SAVEPOINT start_transaction;
561 UPDATE CZ_CONFIG_USAGES SET
562 LIST_PRICE=in_list_price,
563 AUTO_DISCOUNT_ID=in_auto_discount_id,
564 AUTO_DISCOUNT_LINE_ID=in_auto_discount_line_id,
565 AUTO_DISCOUNT_PCT=in_auto_discount_pct,
566 MANUAL_DISCOUNT_ID=in_manual_discount_id,
567 MANUAL_DISCOUNT_LINE_ID=in_manual_discount_line_id,
568 MANUAL_DISCOUNT_PCT=in_manual_discount_pct,
569 DISCOUNTED_PRICE=in_discounted_price,
570 UOM_CODE=in_uom_code
571 WHERE
572 CALLING_APPLICATION_ID=in_calling_application_id AND
573 CALLING_APPLICATION_REF_KEY=in_calling_application_ref_key AND
574 CONFIG_HDR_ID=in_config_hdr_id AND
575 CONFIG_REV_NBR=in_config_rev_nbr AND
576 CONFIG_ITEM_ID=in_config_item_id;
577 EXCEPTION
578 WHEN OTHERS THEN
579 Return_value:=0;
580 Error_message:=SQLERRM;
581 ROLLBACK TO start_transaction;
582 -- xERROR:=CZ_UTILS.REPORT(Error_message,1,'CZ_CF_API: update configuration usage',11276);
583 cz_utils.log_report('CZ_CF_API', 'update_configuration_usage', 1, error_message,
584 fnd_log.LEVEL_UNEXPECTED);
585 END update_configuration_usage;
586 ------------------------------------------------------------------------------------------
587 PROCEDURE get_config_hdr(p_xml_string IN VARCHAR2,
588 p_config_header_id IN OUT NOCOPY NUMBER,
589 p_config_rev_nbr IN OUT NOCOPY NUMBER)
590 AS
591
592 v_search_string VARCHAR2(200) := 'config_header_id';
593 v_occurence_position NUMBER := 0;
594 v_pattern_found VARCHAR2(5) := 'FALSE';
595 v_occurence_number NUMBER := 1;
596 l_hdr_str VARCHAR2(30);
597 l_rev_str VARCHAR2(30);
598
599 BEGIN
600 WHILE ( INSTR(v_pattern_found,'FALSE') > 0)
601 LOOP
602 v_occurence_position := INSTR(p_xml_string,v_search_string,1,v_occurence_number);
603 IF v_occurence_position = 0 THEN
604 p_config_header_id := 0;
605 p_config_rev_nbr := 0;
606 RETURN;
607 END IF;
608 v_occurence_position := v_occurence_position+16;
609
610 v_pattern_found := 'TRUE';
611 while((ASCII(SUBSTR(p_xml_string,v_occurence_position,1))<48) OR (ASCII(SUBSTR(p_xml_string,v_occurence_position,1))>57))
612 LOOP
613 IF( NOT((ASCII(SUBSTR(p_xml_string,v_occurence_position,1))=32) OR (ASCII(SUBSTR(p_xml_string,v_occurence_position,1))=34) OR (ASCII(SUBSTR(p_xml_string,v_occurence_position,1))=62))) THEN
614 v_pattern_found := 'FALSE';
615 EXIT;
616 END IF;
617 v_occurence_position := v_occurence_position+1;
618 END LOOP;
619 v_occurence_number := v_occurence_number+1;
620 END LOOP;
621
622 l_hdr_str := '';
623 while((ASCII(SUBSTR(p_xml_string,v_occurence_position,1))>47) AND (ASCII(SUBSTR(p_xml_string,v_occurence_position,1))<58))
624 LOOP
628
625 l_hdr_str := l_hdr_str||SUBSTR(p_xml_string,v_occurence_position,1);
626 v_occurence_position := v_occurence_position+1;
627 END LOOP;
629 v_pattern_found := 'FALSE';
630 v_search_string := 'config_rev_nbr';
631 v_occurence_number := 1;
632
633 WHILE ( INSTR(v_pattern_found,'FALSE') > 0)
634 LOOP
635 v_occurence_position := INSTR(p_xml_string,v_search_string,1,v_occurence_number);
636 IF v_occurence_position = 0 THEN
637 p_config_header_id := 0;
638 p_config_rev_nbr := 0;
639 RETURN;
640 END IF;
641 v_occurence_position := v_occurence_position+15;
642
643 v_pattern_found := 'TRUE';
644 while((ASCII(SUBSTR(p_xml_string,v_occurence_position,1))<48) OR (ASCII(SUBSTR(p_xml_string,v_occurence_position,1))>57))
645 LOOP
646 IF( NOT((ASCII(SUBSTR(p_xml_string,v_occurence_position,1))=32) OR (ASCII(SUBSTR(p_xml_string,v_occurence_position,1))=34) OR (ASCII(SUBSTR(p_xml_string,v_occurence_position,1))=62))) THEN
647 v_pattern_found := 'FALSE';
648 EXIT;
649 END IF;
650 v_occurence_position := v_occurence_position+1;
651 END LOOP;
652 v_occurence_number := v_occurence_number+1;
653 END LOOP;
654
655
656 l_rev_str := '';
657 while((ASCII(SUBSTR(p_xml_string,v_occurence_position,1))>47) AND (ASCII(SUBSTR(p_xml_string,v_occurence_position,1))<58))
658 LOOP
659 l_rev_str := l_rev_str||SUBSTR(p_xml_string,v_occurence_position,1);
660 v_occurence_position := v_occurence_position+1;
661 END LOOP;
662
663 p_config_header_id := to_number(l_hdr_str);
664 p_config_rev_nbr := to_number(l_rev_str);
665 EXCEPTION
666 WHEN OTHERS THEN
667 p_config_header_id := 0;
668 p_config_rev_nbr := 0;
669 END get_config_hdr;
670 ------------------------------------------------------------
671 PROCEDURE append_instance_nbr(p_node_identifier IN VARCHAR2,
672 x_node_identifier OUT NOCOPY VARCHAR2,
673 x_item_depth OUT NOCOPY NUMBER)
674 IS
675 l_instr INTEGER;
676 l_count PLS_INTEGER;
677 l_str cz_config_items.node_identifier%TYPE;
678 l_ecc cz_config_items.node_identifier%TYPE;
679
680 BEGIN
681 l_count := 1;
682 l_ecc := '';
683 l_str := p_node_identifier;
684 l_instr := INSTR(l_str,'-') - 1;
685 WHILE l_instr > 0 LOOP
686 l_ecc := l_ecc||SUBSTR(l_str,1,l_instr)||'|1-';
687 l_str := SUBSTR(l_str,l_instr+2);
688 l_instr := INSTR(l_str,'-') - 1;
689 l_count := l_count + 1;
690 END LOOP;
691 x_node_identifier := l_ecc||l_str||'|1';
692 x_item_depth := l_count;
693 EXCEPTION
694 WHEN OTHERS THEN
695 RAISE;
696 END append_instance_nbr;
697
698 --------------------------------------------------------------------------------
699 PROCEDURE get_ext_comp_code(p_config_hdr_id IN NUMBER
700 ,p_config_rev_nbr IN NUMBER
701 ,p_config_item_id IN NUMBER
702 ,x_ecc_code OUT NOCOPY VARCHAR2
703 ,x_item_depth OUT NOCOPY NUMBER)
704 IS
705 l_ecc cz_config_items.node_identifier%TYPE;
706 l_inventory_item_id NUMBER;
707 l_instance_nbr NUMBER;
708 l_depth PLS_INTEGER;
709 CURSOR ecc_cur(p_config_item_id NUMBER) IS
710 SELECT inventory_item_id, instance_nbr
711 FROM cz_config_items
712 WHERE deleted_flag = '0'
713 AND inventory_item_id IS NOT NULL
714 AND config_hdr_id = p_config_hdr_id
715 AND config_rev_nbr = p_config_rev_nbr
716 START WITH config_hdr_id = p_config_hdr_id and config_rev_nbr = p_config_rev_nbr and config_item_id = p_config_item_id
717 CONNECT BY PRIOR parent_config_item_id = config_item_id and config_hdr_id = p_config_hdr_id and config_rev_nbr = p_config_rev_nbr
718 ORDER BY ROWNUM DESC;
719 BEGIN
720 l_ecc := '';
721 l_depth := 0;
722 OPEN ecc_cur(p_config_item_id);
723 LOOP
724 FETCH ecc_cur INTO l_inventory_item_id,l_instance_nbr;
725 EXIT WHEN ecc_cur%NOTFOUND;
726 SELECT decode(l_instance_nbr, -1,1,0,1,l_instance_nbr) into l_instance_nbr from dual;
727 l_ecc := l_ecc||l_inventory_item_id||'|'||nvl(l_instance_nbr,1)||'-';
728 l_depth := l_depth + 1;
729 END LOOP;
730 CLOSE ecc_cur;
731 l_ecc := RTRIM(l_ecc, '-');
732 x_ecc_code := l_ecc;
733 x_item_depth := l_depth;
734 EXCEPTION
735 WHEN OTHERS THEN
736 CLOSE ecc_cur;
737 RAISE;
738 END get_ext_comp_code;
739
740 ------------------------------------------------------
741 ------procedure that parses the output terminate message for the output
742 ------header,revision and config status
743 PROCEDURE parse_output_xml (p_xml IN LONG,
744 x_valid_config OUT NOCOPY VARCHAR2,
745 x_complete_config OUT NOCOPY VARCHAR2,
746 x_config_header_id OUT NOCOPY NUMBER,
747 x_config_rev_nbr OUT NOCOPY NUMBER,
748 x_return_status OUT NOCOPY VARCHAR2,
749 x_error_message OUT NOCOPY VARCHAR2)
750 IS
751
752 l_valid_config_start_tag VARCHAR2(30) := '<VALID_CONFIGURATION>';
753 l_valid_config_end_tag VARCHAR2(30) := '</VALID_CONFIGURATION>';
754 l_valid_config_start_pos NUMBER;
755 l_valid_config_end_pos NUMBER;
756 l_complete_config_start_tag VARCHAR2(30) := '<COMPLETE_CONFIGURATION>';
757 l_complete_config_end_tag VARCHAR2(30) := '</COMPLETE_CONFIGURATION>';
761 l_config_header_id_end_tag VARCHAR2(20) := '</CONFIG_HEADER_ID>';
758 l_complete_config_start_pos NUMBER;
759 l_complete_config_end_pos NUMBER;
760 l_config_header_id_start_tag VARCHAR2(20) := '<CONFIG_HEADER_ID>';
762 l_config_header_id_start_pos NUMBER;
763 l_config_header_id_end_pos NUMBER;
764 l_config_rev_nbr_start_tag VARCHAR2(20) := '<CONFIG_REV_NBR>';
765 l_config_rev_nbr_end_tag VARCHAR2(20) := '</CONFIG_REV_NBR>';
766 l_config_rev_nbr_start_pos NUMBER;
767 l_config_rev_nbr_end_pos NUMBER;
768 l_config_header_id NUMBER;
769 l_config_rev_nbr NUMBER;
770 l_valid_config VARCHAR2(10);
771 l_complete_config VARCHAR2(10);
772 l_header_id NUMBER;
773 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
774
775 BEGIN
776 l_valid_config_start_pos := INSTR(UPPER(p_xml),l_valid_config_start_tag,1, 1)
777 + length(l_valid_config_start_tag);
778 l_valid_config_end_pos := INSTR(UPPER(p_xml), l_valid_config_end_tag,1, 1) - 1;
779 l_valid_config := SUBSTR(p_xml,l_valid_config_start_pos,
780 l_valid_config_end_pos - l_valid_config_start_pos + 1);
781 l_complete_config_start_pos := INSTR(UPPER(p_xml),
782 l_complete_config_start_tag,1, 1) + length(l_complete_config_start_tag);
783 l_complete_config_end_pos := INSTR(UPPER(p_xml), l_complete_config_end_tag,1, 1) - 1;
784 l_complete_config := SUBSTR( p_xml, l_complete_config_start_pos,
785 l_complete_config_end_pos - l_complete_config_start_pos + 1);
786
787 -- get the latest config_header_id, and rev_nbr
788 l_config_header_id_start_pos := INSTR(UPPER(p_xml),
789 l_config_header_id_start_tag, 1, 1)
790 + length(l_config_header_id_start_tag);
791 l_config_header_id_end_pos := INSTR(UPPER(p_xml), l_config_header_id_end_tag, 1, 1) - 1;
792 l_config_header_id := to_number(SUBSTR(p_xml,l_config_header_id_start_pos,
793 l_config_header_id_end_pos - l_config_header_id_start_pos + 1));
794 l_config_rev_nbr_start_pos := INSTR(UPPER(p_xml), l_config_rev_nbr_start_tag, 1, 1)
795 + length(l_config_rev_nbr_start_tag);
796 l_config_rev_nbr_end_pos := INSTR(UPPER(p_xml), l_config_rev_nbr_end_tag, 1, 1) - 1;
797 l_config_rev_nbr := to_number(SUBSTR(p_xml,l_config_rev_nbr_start_pos,
798 l_config_rev_nbr_end_pos - l_config_rev_nbr_start_pos + 1));
799
800 x_return_status := l_return_status;
801 x_config_header_id := l_config_header_id;
802 x_config_rev_nbr := l_config_rev_nbr;
803 x_complete_config := nvl(l_complete_config, 'FALSE');
804 x_valid_config := nvl(l_valid_config, 'FALSE');
805
806 EXCEPTION
807 WHEN OTHERS THEN
808 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
809 x_error_message := SQLERRM;
810 END Parse_output_xml;
811 -------------------------------------------------------------------
812 -------procedure that checks if the passed in init message
813 -------has a save config behaviour as new_revision.
814 -------if it is new_revision x_param_flag is set to 'YES'
815 PROCEDURE check_if_new_revision(p_init_message IN VARCHAR2,
816 x_param_flag IN OUT NOCOPY VARCHAR2)
817 IS
818
819 l_new_rev_tag VARCHAR2(20) := 'new_revision';
820 l_save_rev_tag VARCHAR2(30) := 'save_config_behavior';
821 l_new_rev_instr NUMBER := 0;
822 l_save_config_instr NUMBER := 0;
823
824 BEGIN
825 x_param_flag := 'YES';
826 IF (p_init_message IS NOT NULL) THEN
827 ------check if 'save_config_behavior' tag exists
828 l_save_config_instr := INSTR(p_init_message,l_save_rev_tag);
829 ----if it does not exist, then default is new revision
830 ----if it exists then check for new_revision
831 IF (l_save_config_instr = 0) THEN
832 x_param_flag := 'YES';
833 ELSE
834 l_new_rev_instr := INSTR(p_init_message,l_new_rev_tag);
835 IF (l_new_rev_instr = 0) THEN
836 x_param_flag := 'NO';
837 END IF;
838 END IF;
839 END IF;
840 END check_if_new_revision;
841
842 -----------------------------------------------
843 ------Procedure that retrieves description and item name
844 ------from mtl_system_items_kfv for an inventory_item_id and organization_id
845 PROCEDURE get_item_description_and_name (p_inventory_item_id IN mtl_system_items.inventory_item_id%TYPE,
846 p_organization_id IN mtl_system_items.organization_id%TYPE,
847 x_description OUT NOCOPY mtl_system_items_kfv.description%TYPE,
848 x_item_name OUT NOCOPY mtl_system_items_kfv.concatenated_segments%TYPE)
849 IS
850
851 BEGIN
852 ----get description,itemname from mtl_system_items
853 SELECT description,concatenated_segments
854 INTO x_description,x_item_name
855 FROM mtl_system_items_kfv
856 WHERE mtl_system_items_kfv.inventory_item_id = p_inventory_item_id
857 AND mtl_system_items_kfv.organization_id = p_organization_id;
858 END;
859
860 --------------------------------------------------
861 ------procedure that logs a delta message to
862 ------cz_config_messages
863 PROCEDURE log_delta_message (p_inventory_item_id IN mtl_system_items.inventory_item_id%TYPE,
864 p_organization_id IN mtl_system_items.organization_id%TYPE ,
865 p_component_code IN cz_config_details_v.component_code%TYPE,
866 p_current_quantity IN NUMBER,
867 p_new_quantity IN NUMBER,
868 p_config_hdr IN NUMBER,
869 p_config_rev IN NUMBER,
870 p_message_name IN fnd_new_messages.message_name%TYPE)
871
872 IS
873
874 l_description mtl_system_items_kfv.description%TYPE;
875 l_new_item_name mtl_system_items_kfv.concatenated_segments%TYPE;
876 v_OracleSequenceIncr NUMBER := 20;
877 l_msg_seq NUMBER := 0;
878 l_delta_message VARCHAR2(2000);
879
880 BEGIN
881 ----get description,itemname from mtl_system_items
882 get_item_description_and_name (p_inventory_item_id,
883 p_organization_id,
884 l_description,
885 l_new_item_name);
886 l_msg_seq := get_next_msg_seq;
887
888 IF ( (p_message_name = ITEM_DELETE_MESSAGE) OR (p_message_name = ITEM_ADD_MESSAGE ) ) THEN
889 l_delta_message := CZ_UTILS.GET_TEXT(p_message_name,
890 'ITEMNAME',l_new_item_name,
891 'QUANTITY',p_current_quantity,
892 'COMPONENTCODE',p_component_code,
893 'DESCRIPTION',l_description);
894 ELSIF (p_message_name = QTY_CHANGE_MESSAGE ) THEN
895 l_delta_message := CZ_UTILS.GET_TEXT(p_message_name,
896 'ITEMNAME',l_new_item_name,
897 'CURRENT_QUANTITY',p_current_quantity,
898 'NEW_QUANTITY',p_new_quantity,
899 'COMPONENTCODE',p_component_code,
900 'DESCRIPTION',l_description );
901 END IF;
902
903 insert into cz_config_messages (config_hdr_id,config_rev_nbr,constraint_type,
904 message,message_seq,deleted_flag)
905 values (p_config_hdr,p_config_rev,'ITEM DELTA',l_delta_message,l_msg_seq, '0');
906 END;
907
908 ----------------------------------------------------------------------------------------------
909 ----- changes for batch validation failure processing
910 ----- this block would set the validation status to fail if
911 ----- the config_input_list is empty and validation_status is CONFIG_PROCESSED
912 ------ and if there are configured item changes
913 ------ The changes are logged to cz_config_messages
914 ------@p_config_input_list input list passed to validate proc
915 ------@p_validation_status --- validation status after BV
916 ------@p_init_message --- init message passed by calling application
917 ------@x_config_messages OUTPUT config_messages
918 ------@x_return_status -- validation status is set to 4 if deltas exist
919 ------ Quoting ER #9348864 implementation ---------------------------------------------------
920 ------@p_check_config_flag passed from caller VALIDATE Proc. Valid value : 'Y'
921 ------@x_return_config_changed will be passed to the caller VALIDATE Proc.
922 ------ Valid values: 'Y' (Changed) and 'N' (Not Changed) when p_check_config_passed. NULL when check flag not passed.
923 ------ For any existing VALIDATE API, IN param is hard coded to 'N' and OUT param is overwritten to NULL
924 ------ Also we will not have any check for new OUT param for any VALIDATE API other than the new Quoting specific API.
925 ----------------------------------------------------------------------------------------------
926 PROCEDURE check_deltas( p_init_message IN VARCHAR2,
927 p_check_config_flag IN VARCHAR2 DEFAULT 'N',
928 x_config_messages IN OUT NOCOPY CFG_OUTPUT_PIECES,
929 x_return_status IN OUT NOCOPY VARCHAR2,
930 x_return_config_changed OUT NOCOPY VARCHAR2)
931 IS
932
933 v_header_id cz_config_hdrs.config_hdr_id%TYPE;
934 v_rev_nbr cz_config_hdrs.config_rev_nbr%TYPE;
935 v_output_cfg_hdr_id cz_config_hdrs.config_hdr_id%TYPE := 0;
936 v_output_cfg_rev_nbr cz_config_hdrs.config_rev_nbr%TYPE := 0;
937 l_prev_item cz_config_items.config_item_id%TYPE := 0;
938 l_prev_rev cz_config_hdrs.config_rev_nbr%TYPE;
939 l_prev_qty NUMBER := 0;
940 l_description mtl_system_items.description%TYPE;
941 l_new_item cz_config_items.config_item_id%TYPE:= 0;
942 l_new_rev cz_config_hdrs.config_rev_nbr%TYPE := 0;
943 l_new_component_code cz_config_details_v.component_code%TYPE;
944 l_new_qty NUMBER := 0;
945 l_new_inventory_item_id mtl_system_items.inventory_item_id%TYPE;
946 l_new_organization_id mtl_system_items.organization_id%TYPE;
947 v_valid_config VARCHAR2(30);
948 v_complete_config VARCHAR2(30);
949 v_parse_status VARCHAR2(1);
950 l_param_value VARCHAR2(3);
951 v_xml_str LONG := NULL;
952 l_config_err_msg VARCHAR2(2000);
953 PARSE_XML_ERROR EXCEPTION;
954 NO_INPUT_HDR_EXCEP EXCEPTION;
955 l_new_item_name cz_config_items.name%TYPE;
956 l_bv_profile VARCHAR2(100);
957 v_parse_message VARCHAR2(2000);
958 l_len NUMBER := 2000;
959 l_delta_exists VARCHAR2(3) := 'NO';
960 l_qty_changed BOOLEAN := FALSE;
961 l_prev_count NUMBER := 0;
962 l_config_true_tag VARCHAR2(30) := '<valid_configuration>true';
963 l_config_false_tag VARCHAR2(30) := '<valid_configuration>false';
964 l_prev_inventory_item_id mtl_system_items.inventory_item_id%TYPE;
965 l_prev_organization_id mtl_system_items.organization_id%TYPE;
966 l_prev_component_code cz_config_details_v.component_code%TYPE;
967
968 CURSOR c_config_delta (p_old_hdr NUMBER,p_old_rev NUMBER,p_new_hdr NUMBER,p_new_rev NUMBER)
969 IS select config_rev_nbr,config_item_id,quantity,
970 component_code,inventory_item_id,organization_id
971 from cz_config_details_v
972 where (config_hdr_id,config_item_id,quantity)
973 IN (
974 (select config_hdr_id,config_item_id,quantity from cz_config_details_v
975 where config_hdr_id = p_old_hdr and config_rev_nbr = p_old_rev
976 minus
977 select config_hdr_id,config_item_id,quantity from cz_config_details_v
978 where config_hdr_id = p_new_hdr and config_rev_nbr = p_new_rev )
979 union
980 (select config_hdr_id,config_item_id,quantity from cz_config_details_v
981 where config_hdr_id = p_new_hdr and config_rev_nbr = p_new_rev
982 minus
983 select config_hdr_id,config_item_id,quantity from cz_config_details_v
984 where config_hdr_id = p_old_hdr and config_rev_nbr = p_old_rev )
985 )
986 and config_rev_nbr IN (p_old_rev, p_new_rev)
987 ORDER BY config_item_id,config_hdr_id,config_rev_nbr;
988
989 BEGIN
990 x_return_status := FND_API.G_RET_STS_SUCCESS;
991
992 FOR I IN 1..1
993 LOOP
994
995 ----check profile setting
996 ------ Quoting ER #9348864. Even if profile value is N, calculate delta if
997 --------the new IN param p_check_config_flag = Y (Making this ER implementation independent of this profile value)
1001 EXIT;
998 l_bv_profile := FND_PROFILE.VALUE('CZ_BV_DELTA');
999
1000 IF (NVL(UPPER(l_bv_profile), 'N') = 'N' AND NVL(UPPER(p_check_config_flag),'N') = 'N') THEN
1002 END IF;
1003
1004 -----check if the init message contains the parameter
1005 -----save_config_behavior = new revision
1006 check_if_new_revision(p_init_message,l_param_value);
1007
1008 IF (l_param_value <> 'YES') THEN
1009 EXIT;
1010 END IF;
1011
1012 -----get previous config_hdr_id and config_rev_nbr
1013 get_config_hdr(p_init_message,v_header_id,v_rev_nbr);
1014
1015 -----if the init message does not contain input header
1016 -----then no need to compute deltas and check_delta must return a
1017 -----status of SUCCESS
1018 IF (v_header_id = 0) THEN
1019 RAISE NO_INPUT_HDR_EXCEP;
1020 END IF;
1021
1022 ------get new config_hdr_id and revision
1023 IF (x_config_messages.COUNT > 0) THEN
1024 FOR xmlStr IN x_config_messages.FIRST..x_config_messages.LAST
1025 LOOP
1026 v_xml_str := v_xml_str||x_config_messages(xmlStr);
1027 END LOOP;
1028 END IF;
1029
1030 parse_output_xml (v_xml_str ,
1031 v_valid_config ,
1032 v_complete_config ,
1033 v_output_cfg_hdr_id ,
1034 v_output_cfg_rev_nbr ,
1035 v_parse_status ,
1036 v_parse_message) ;
1037
1038 ----if error in parsing xml raise an exception
1039 IF (v_parse_status <> FND_API.G_RET_STS_SUCCESS) THEN
1040 RAISE PARSE_XML_ERROR;
1041 END IF;
1042
1043 IF (UPPER(v_valid_config) NOT IN ('TRUE', 'Y')) THEN
1044 EXIT;
1045 END IF;
1046
1047 OPEN c_config_delta (v_header_id,v_rev_nbr,v_output_cfg_hdr_id,v_output_cfg_rev_nbr);
1048 LOOP
1049 FETCH c_config_delta INTO l_new_rev,l_new_item,l_new_qty,l_new_component_code,
1050 l_new_inventory_item_id,l_new_organization_id;
1051 EXIT WHEN(c_config_delta%NOTFOUND);
1052
1053 IF ( l_qty_changed = FALSE ) THEN
1054
1055 IF ( (l_prev_item <> 0) AND (l_prev_item <> l_new_item) ) THEN
1056
1057 IF (l_prev_rev = v_rev_nbr) THEN
1058 --item deleted
1059 l_delta_exists := 'YES';
1060
1061 log_delta_message (l_prev_inventory_item_id,
1062 l_prev_organization_id,
1063 l_prev_component_code,
1064 l_prev_qty,
1065 NULL,
1066 v_output_cfg_hdr_id,
1067 v_output_cfg_rev_nbr,
1068 ITEM_DELETE_MESSAGE);
1069
1070 ELSIF (l_prev_rev = v_output_cfg_rev_nbr) THEN
1071 --- item added
1072 l_delta_exists := 'YES';
1073
1074 log_delta_message (l_prev_inventory_item_id,
1075 l_prev_organization_id,
1076 l_prev_component_code,
1077 l_prev_qty,
1078 NULL,
1079 v_output_cfg_hdr_id,
1080 v_output_cfg_rev_nbr,
1081 ITEM_ADD_MESSAGE);
1082
1083 END IF;
1084
1085 ELSIF (l_prev_item = l_new_item) THEN
1086 ---qty changed
1087 l_qty_changed := TRUE;
1088 l_delta_exists := 'YES';
1089
1090 log_delta_message (l_prev_inventory_item_id,
1091 l_prev_organization_id,
1092 l_new_component_code,
1093 l_prev_qty,
1094 l_new_qty,
1095 v_output_cfg_hdr_id,
1096 v_output_cfg_rev_nbr,
1097 QTY_CHANGE_MESSAGE );
1098 END IF;
1099
1100 ELSE
1101 l_qty_changed := FALSE;
1102 END IF;
1103 l_prev_item := l_new_item; l_prev_rev := l_new_rev; l_prev_qty := l_new_qty;
1104 l_prev_inventory_item_id := l_new_inventory_item_id; l_prev_organization_id := l_new_organization_id;
1105 l_prev_component_code := l_new_component_code;
1106 END LOOP;
1107 CLOSE c_config_delta ;
1108
1109 ----this block of code process the last record if add or delete item
1110 IF ( l_qty_changed = FALSE ) THEN
1111 IF (l_new_rev = v_rev_nbr) THEN
1112 --item deleted
1113 l_delta_exists := 'YES';
1114
1115 log_delta_message (l_prev_inventory_item_id,
1116 l_prev_organization_id,
1117 l_prev_component_code,
1118 l_prev_qty,
1119 NULL,
1120 v_output_cfg_hdr_id,
1121 v_output_cfg_rev_nbr,
1122 ITEM_DELETE_MESSAGE);
1123 ELSIF (l_new_rev = v_output_cfg_rev_nbr) THEN
1124 --- item added
1125 l_delta_exists := 'YES';
1126
1127 log_delta_message (l_prev_inventory_item_id,
1128 l_prev_organization_id,
1129 l_prev_component_code,
1130 l_prev_qty,
1131 NULL,
1132 v_output_cfg_hdr_id,
1133 v_output_cfg_rev_nbr,
1134 ITEM_ADD_MESSAGE);
1135 END IF;
1136 END IF;
1137
1138 ---Quting ER. Initializing the x_return_config_changed flag to N only when the p_check_config_flag is pass to 'Y'.
1139 --- This retunr flag will be reassigned to Y below when there is change in config.
1140 --- If p_check_config_flag is not passed by caller then x_return_config_flag will be NULL
1141
1142 IF UPPER(p_check_config_flag) = 'Y' THEN
1143 x_return_config_changed := 'N';
1144 END IF;
1145
1146 IF (l_delta_exists = 'YES') THEN
1147 IF (NVL(UPPER(p_check_config_flag),'N') = 'N') THEN
1148 v_xml_str := REPLACE(v_xml_str,l_config_true_tag,l_config_false_tag);
1149 ELSE
1150 x_return_config_changed := 'Y';
1151 END IF;
1152 x_config_messages.DELETE;
1153 FOR I IN 1..LENGTH(v_xml_str)
1154 LOOP
1158 l_len := LENGTH(v_xml_str);
1155 --Bug9562050- Need to Check NULL length and convert to 0, else LOOP will continue unnesssarily
1156 EXIT WHEN NVL(LENGTH(v_xml_str),0) = 0;
1157 IF (LENGTH(v_xml_str) <= 2000) THEN
1159 ELSE
1160 l_len := 2000;
1161 END IF;
1162 x_config_messages(i) := substr(v_xml_str,1,l_len);
1163 v_xml_str := substr(v_xml_str,l_len + 1);
1164 END LOOP;
1165 END IF;
1166 END LOOP;
1167 COMMIT;
1168 EXCEPTION
1169 WHEN NO_INPUT_HDR_EXCEP THEN
1170 x_return_status := FND_API.G_RET_STS_SUCCESS;
1171 WHEN PARSE_XML_ERROR THEN
1172 ROLLBACK;
1173 -- l_report_status := CZ_UTILS.REPORT (v_parse_message,1,'ITEM DELTA',1);
1174 cz_utils.log_report('CZ_CF_API', 'check_deltas', null, v_parse_message,
1175 fnd_log.LEVEL_ERROR);
1176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1177 WHEN OTHERS THEN
1178 ROLLBACK;
1179 l_config_err_msg := SQLERRM;
1180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1181 -- l_report_status := CZ_UTILS.REPORT (l_config_err_msg,1,'ITEM DELTA',1);
1182 cz_utils.log_report('CZ_CF_API', 'check_deltas', null, l_config_err_msg,
1183 fnd_log.LEVEL_UNEXPECTED);
1184 END check_deltas;
1185 -------------------------------------------------------------------------
1186 FUNCTION batchurlencode(p_str varchar2)
1187 RETURN VARCHAR2
1188 IS
1189
1190 l_tmp varchar2(100);
1191 l_hex varchar2(16) default '0123456789ABCDEF';
1192 l_num number;
1193 l_bad varchar2(100) default ' >%}\~];?@&<#{|^[`/:=$+''"'; l_char char(1);
1194
1195 begin
1196
1197 IF (p_str is null) THEN
1198 return null;
1199 END IF;
1200
1201 FOR I IN 1 .. length(p_str)
1202 LOOP
1203 l_char := substr(p_str, i, 1);
1204 IF (instr(l_bad, l_char) ) > 0 THEN
1205 l_num := ascii(l_char);
1206 l_tmp := l_tmp || '%' || substr(l_hex, mod(trunc (l_num / 16), 16) + 1, 1)
1207 || substr(l_hex, mod(l_num, 16) + 1, 1); else l_tmp := l_tmp || l_char;
1208 END IF;
1209 END LOOP;
1210
1211 RETURN l_tmp;
1212
1213 end;
1214
1215 -----------------------------------------
1216 -----This procedure is used as a workaround for bug# 2687938 which is
1217 -----utl_http_request_failed error from network API during batch validation
1218 ------although the logs show that the validation was successful
1219
1220 -------Changes to this procedure is made for SSL implementation
1221 -------Changes are implemented as suggested in bug# 3594440, 3785732,3785687
1222
1223 PROCEDURE return_html_pieces(FinalURL IN VARCHAR2,
1224 p_model_routing_enabled_for_bv IN BOOLEAN,
1225 pool_identifier IN VARCHAR2,
1226 config_messages IN OUT NOCOPY CFG_OUTPUT_PIECES)
1227 IS
1228 PRAGMA AUTONOMOUS_TRANSACTION;
1229 l_ssl_profile_option VARCHAR2(2000); ----SSL profile option value, if ssl then 'https'
1230 l_wallet_path VARCHAR2(2000); ----directory path of the wallet
1231 l_wallet_passwd VARCHAR2(2000) := NULL; ---- password is not necessary for the default wallet
1232 l_cookies UTL_HTTP.COOKIE_TABLE;
1233 l_cookie UTL_HTTP.COOKIE;
1234 l_start_index NUMBER;
1235 l_end_index NUMBER;
1236
1237 BEGIN
1238 l_ssl_profile_option := FND_PROFILE.VALUE('APPS_SERVLET_AGENT');
1239 l_wallet_path := FND_PROFILE.VALUE('FND_DB_WALLET_DIR');
1240 l_wallet_path := 'file:'||l_wallet_path ;
1241
1242 IF (transferTimeout IS NOT NULL AND defaultTimeout IS NOT NULL) THEN
1243 EXECUTE IMMEDIATE 'BEGIN UTL_HTTP.SET_TRANSFER_TIMEOUT(:1); END;' USING IN transferTimeout;
1244 END IF;
1245 --vsingava 14 Jul '09 bug7674190
1246 IF(p_model_routing_enabled_for_bv = TRUE) THEN
1247 IF pool_identifier IS NULL THEN
1248 RAISE MODEL_POOL_EFFINITY_EXC;
1249 END IF;
1250 l_start_index := INSTR(FinalURL, '//');
1251 l_end_index := INSTR(FinalURL, '/', l_start_index+2);
1252 l_cookie.domain :=SUBSTR(FinalURL, l_start_index+2, l_end_index - (l_start_index+2));
1253 IF INSTR(l_cookie.domain, ':') > 0 THEN
1254 l_cookie.domain := SUBSTR(l_cookie.domain, 1, INSTR(l_cookie.domain, ':')-1);
1255 END IF;
1256 l_start_index := l_end_index;
1257 l_end_index := INSTR(FinalURL, '/', l_start_index+1);
1258 l_cookie.path := SUBSTR(FinalURL, l_start_index, l_end_index-l_start_index);
1259 UTL_HTTP.GET_COOKIES(l_cookies);
1260 l_cookie.name := 'czPoolToken';
1261 l_cookie.value := pool_identifier;
1262 l_cookie.expire := SYSDATE+99999;
1263 --l_cookie.path :='/OA_HTML';
1264 IF (UPPER(TRIM(FinalURL)) LIKE ('HTTPS%')) THEN
1265 l_cookie.secure := TRUE;
1266 ELSE
1267 l_cookie.secure := FALSE;
1268 END IF;
1269 l_cookie.version := 1;
1270 l_cookies(l_cookies.count+1) := l_cookie;
1271 utl_http.clear_cookies;
1272 utl_http.add_cookies(l_cookies);
1273 END IF;
1274 --vsingava 14 Jul '09 bug7674190
1275 -----if the FinalURL is SSL then
1276 -----pass in the wallet path and wallet passwd
1277 -----otherwise pass in the URL only
1278 IF (UPPER(TRIM(FinalURL)) LIKE ('HTTPS%')) THEN
1279 config_messages := UTL_HTTP.request_pieces(url => FinalURL,
1280 wallet_path => l_wallet_path,
1281 wallet_password => l_wallet_passwd);
1282 ELSE
1283 config_messages := UTL_HTTP.request_pieces(url => FinalURL);
1284 END IF;
1285
1286 IF (transferTimeout IS NOT NULL AND defaultTimeout IS NOT NULL) THEN
1287 EXECUTE IMMEDIATE 'BEGIN UTL_HTTP.SET_TRANSFER_TIMEOUT(:1); END;' USING IN defaultTimeout;
1288 END IF;
1289 COMMIT;
1290 EXCEPTION
1291 WHEN MODEL_POOL_EFFINITY_EXC THEN
1292 CZ_UTILS.LOG_REPORT('cz_cf_api', 'return_html_pieces', null, 'Model to Pool Effinity is not properly defined', FND_LOG.LEVEL_EXCEPTION);
1296 ROLLBACK;
1293 ROLLBACK;
1294 RAISE;
1295 WHEN OTHERS THEN
1297 RAISE;
1298 END return_html_pieces;
1299
1300 --------------------------------------------------------------------------------
1301 procedure delete_bv_records(p_pseudo_hdr_id IN NUMBER
1302 ,p_check_db_setting IN BOOLEAN
1303 ,p_delete_ext_attr IN BOOLEAN)
1304 IS
1305 l_no_config_del cz_db_settings.value%TYPE;
1306 BEGIN
1307 l_no_config_del := 'NO';
1308 IF p_check_db_setting THEN
1309 BEGIN
1310 SELECT upper(value) INTO l_no_config_del
1311 FROM cz_db_settings
1312 WHERE setting_id = 'BatchValConfigInputDelete';
1313 EXCEPTION
1314 WHEN NO_DATA_FOUND THEN
1315 NULL;
1316 END;
1317 END IF;
1318
1319 IF l_no_config_del = 'YES' THEN
1320 RETURN;
1321 END IF;
1322
1323 IF p_delete_ext_attr THEN
1324 DELETE FROM CZ_CONFIG_EXT_ATTRIBUTES
1325 WHERE config_hdr_id = p_pseudo_hdr_id AND config_rev_nbr = 1;
1326 END IF;
1327 DELETE FROM CZ_CONFIG_ITEMS
1328 WHERE config_hdr_id = p_pseudo_hdr_id AND config_rev_nbr = 1;
1329 DELETE FROM CZ_CONFIG_HDRS
1330 WHERE config_hdr_id = p_pseudo_hdr_id AND config_rev_nbr = 1;
1331 COMMIT;
1332 END delete_bv_records;
1333 --------------------------------------------------------------------------------
1334 PROCEDURE publication_for_init_message (p_init_str IN VARCHAR2 ,x_publication_id OUT NOCOPY NUMBER ,x_product_key OUT NOCOPY VARCHAR2, x_calling_appl_id OUT NOCOPY VARCHAR2)
1335 IS
1336 l_param_name VARCHAR2(50);
1337 l_param_value VARCHAR2(1000);
1338 l_model_identifier VARCHAR2(100);
1339 l_index1 NUMBER;
1340 l_index2 NUMBER;
1341 p_creation_date DATE;
1342 p_effective_date DATE;
1343 mHasProductId BOOLEAN := false;
1344 mHasRestoreParams BOOLEAN := false;
1345 mHasNativeBOMParams BOOLEAN := false;
1346 l_domparser xmlparser.parser;
1347 l_xmldoc xmldom.DOMDocument;
1348 l_nodelist xmldom.DOMNodeList;
1349 nl xmldom.DOMNodeList;
1350 len1 number;
1351 len2 number;
1352 l_node xmldom.DOMNode;
1353 l_node2 xmldom.DOMNode;
1354 e xmldom.DOMElement;
1355 l_nodeelement xmldom.DOMElement;
1356 l_nnodemap xmldom.DOMNamedNodeMap;
1357 attrname varchar2(100);
1358 attrval varchar2(1000);
1359 --
1360 p_calling_application_id VARCHAR2(10) := NULL;
1361 p_config_model_lookup_date DATE := NULL;
1362 p_config_effective_usage VARCHAR2(3) := NULL;
1363 p_publication_mode VARCHAR(1):= NULL;
1364 p_product_id cz_devl_projects.product_key%TYPE := NULL;
1365 p_inventory_item_id cz_devl_projects.inventory_item_id%TYPE := NULL;
1366 p_context_org_id cz_devl_projects.organization_id%TYPE := NULL;
1367 p_config_header_id cz_config_hdrs.config_hdr_id%TYPE := NULL;
1368 p_config_rev_nbr cz_config_hdrs.config_rev_nbr%TYPE := NULL;
1369 --
1370 MISSING_CAL_APPL EXCEPTION;
1371 NO_MODEL_INFO_FOUND EXCEPTION;
1372
1373 --
1374 PROCEDURE set_init_param(p_param_name VARCHAR2, p_param_value VARCHAR2) IS
1375 BEGIN
1376 IF LOWER(p_param_name) = 'calling_application_id' THEN
1377 p_calling_application_id := p_param_value;
1378 -- vsingava 18th Dec '10. bug10410329
1379 x_calling_appl_id := p_calling_application_id;
1380 ELSIF LOWER(p_param_name) = 'config_model_lookup_date' THEN
1381 p_config_model_lookup_date := convert_to_date(p_param_value);
1382 ELSIF LOWER(p_param_name) = 'config_effective_usage' THEN
1383 p_config_effective_usage := p_param_value;
1384 ELSIF LOWER(p_param_name) = 'publication_mode' THEN
1385 p_publication_mode := p_param_value;
1386 ELSIF LOWER(p_param_name) = 'product_id' THEN
1387 p_product_id := p_param_value;
1388 ELSIF LOWER(p_param_name) = 'inventory_item_id' OR LOWER(p_param_name) = 'model_id' THEN --TODO : Check documentation
1389 p_inventory_item_id := p_param_value;
1390 ELSIF LOWER(p_param_name) = 'context_org_id' THEN
1391 p_context_org_id := p_param_value;
1392 ELSIF LOWER(p_param_name) = 'config_header_id' THEN
1393 p_config_header_id := p_param_value;
1394 ELSIF LOWER(p_param_name) = 'config_rev_nbr' THEN
1395 p_config_rev_nbr := p_param_value;
1396 END IF;
1397 END set_init_param;
1398 --
1399 BEGIN
1400
1401 l_domparser := xmlparser.newParser;
1402 xmlparser.parseBuffer(l_domparser, p_init_str);
1403 l_xmldoc := xmlparser.getDocument(l_domparser);
1404 l_nodeelement := xmldom.getDocumentElement(l_xmldoc);
1405 --l_nodelist := xmldom.getElementsByTagName(l_xmldoc, '*');
1406 l_nodelist := xmldom.getChildrenByTagName(l_nodeelement, '*');
1407 len1 := xmldom.getLength(l_nodelist);
1408 FOR i IN 0..len1-1 LOOP
1409 l_node := xmldom.ITEM(l_nodelist, i);
1410 IF LOWER(xmldom.getNodeName(l_node)) = 'param' THEN
1411 l_nnodemap := xmldom.getAttributes(l_node);
1412 IF (xmldom.isNull(l_nnodemap) = FALSE) THEN -- Check if attr are none
1413 len2 := xmldom.getLength(l_nnodemap);
1414 FOR l_atrrcount IN 0..len2-1 LOOP
1415 l_node2 := xmldom.item(l_nnodemap, l_atrrcount);
1416 IF LOWER(xmldom.getNodeName(l_node2)) = 'name' THEN
1417 l_param_name := xmldom.getNodeValue(l_node2);
1418 EXIT;
1419 END IF;
1420 END LOOP; --loop for attributes
1421 IF l_param_name IS NOT NULL AND xmldom.HASCHILDNODES(l_node) = TRUE THEN
1422 l_param_value := xmldom.getNodeValue(xmldom.getfirstchild(l_node));
1423 set_init_param(l_param_name, l_param_value);
1424 END IF;
1425 END IF;--attributes
1426 END IF;
1427 END LOOP; --loop for all doc children
1428 xmlparser.freeParser(l_domparser);
1429 /*IF p_calling_application_id IS NULL THEN
1430 RAISE MISSING_CAL_APPL;
1431 END IF;
1435 --Do not care of call_application_id, usage, publication_mode,
1432 IF p_config_effective_usage IS NULL THEN
1433 RAISE MISSING_CAL_APPL;
1434 END IF;*/
1436 --Only check for config_model_lookup_date
1437
1438 IF p_product_id IS NOT NULL THEN
1439 mHasProductId := TRUE;
1440 IF p_config_model_lookup_date IS NULL THEN
1441 default_new_cfg_dates(p_creation_date, p_config_model_lookup_date, p_effective_date);
1442 END IF;
1443 x_publication_id := publication_for_product(p_product_id,
1444 p_config_model_lookup_date,
1445 p_calling_application_id,
1446 p_config_effective_usage,
1447 p_publication_mode
1448 );--check if launguage is a valid param in init message
1449 ELSIF p_inventory_item_id IS NOT NULL AND p_context_org_id IS NOT NULL THEN
1450 mHasNativeBOMParams := TRUE;
1451 IF p_config_model_lookup_date IS NULL THEN
1452 default_new_cfg_dates(p_creation_date, p_config_model_lookup_date, p_effective_date);
1453 END IF;
1454 x_publication_id := publication_for_item(p_inventory_item_id,
1455 p_context_org_id,
1456 p_config_model_lookup_date,
1457 p_calling_application_id,
1458 p_config_effective_usage,
1459 p_publication_mode
1460 );--check if launguage is a valid param in init message
1461 ELSIF p_config_header_id IS NOT NULL AND p_config_rev_nbr IS NOT NULL THEN
1462 IF p_config_model_lookup_date IS NULL THEN
1463 default_restored_cfg_dates(p_config_header_id, p_config_rev_nbr, p_creation_date, p_config_model_lookup_date, p_effective_date);
1464 END IF;
1465 mHasRestoreParams := TRUE;
1466 x_publication_id := publication_for_saved_config(p_config_header_id,
1467 p_config_rev_nbr,
1468 p_config_model_lookup_date,
1469 p_calling_application_id,
1470 p_config_effective_usage,
1471 p_publication_mode
1472 );--check if launguage is a valid param in init message
1473 ELSE
1474 RAISE NO_MODEL_INFO_FOUND;
1475 END IF;
1476
1477 IF x_publication_id IS NULL THEN
1478 IF mHasProductId = TRUE THEN
1479 x_product_key := p_product_id;
1480 ELSIF mHasNativeBOMParams = TRUE THEN
1481 x_product_key := p_context_org_id || ':' || p_inventory_item_id;
1482 ELSIF mHasRestoreParams = TRUE OR (p_config_header_id IS NOT NULL AND p_config_rev_nbr IS NOT NULL) THEN
1483 --12816:204:2008/07/06 05:05
1484 SELECT model_identifier INTO l_model_identifier FROM cz_config_hdrs
1485 WHERE config_hdr_id = p_config_header_id AND config_rev_nbr = p_config_rev_nbr AND deleted_flag = 0;
1486 IF l_model_identifier IS NOT NULL THEN
1487 l_index1 := INSTR(l_model_identifier, ':');
1488 p_inventory_item_id := SUBSTR(l_model_identifier, 1, l_index1-1);
1489 l_index2 := INSTR(l_model_identifier, ':', l_index1+1);
1490 p_context_org_id := SUBSTR(l_model_identifier, l_index1+1, (l_index2-1)-l_index1);
1491 x_product_key := p_context_org_id || ':' || p_inventory_item_id;
1492 END IF;
1493 END IF;
1494
1495 ELSE
1496 SELECT product_key INTO x_product_key FROM cz_model_publications WHERE publication_id = x_publication_id;
1497 END IF;
1498 EXCEPTION
1499 WHEN OTHERS THEN
1500 cz_utils.log_report('cz_cf_api', 'publication_for_init_message', null,
1501 SQLERRM, fnd_log.LEVEL_EXCEPTION);
1502 RAISE;
1503 END publication_for_init_message;
1504
1505 -------------------------------------------------------------------------------- pvt
1506 procedure validate(p_pseudo_hdr_id IN NUMBER
1507 ,p_url IN VARCHAR2
1508 ,p_init_msg IN VARCHAR2
1509 ,p_validation_type IN VARCHAR2
1510 ,x_validation_status OUT NOCOPY NUMBER
1511 ,x_config_xml_msg OUT NOCOPY CFG_OUTPUT_PIECES
1512 ,v_detailed_error_message OUT NOCOPY varchar2
1513 )
1514 IS
1515 l_url VARCHAR2(32767);
1516 l_publication_id NUMBER;
1517 l_product_key cz_devl_projects.product_key%TYPE;
1518 l_index NUMBER;
1519 l_init_message VARCHAR2(32767);
1520 l_pool_identifier cz_model_pool_mappings.pool_identifier%TYPE := NULL;
1521 -- vsingava 18th Dec '10. bug10410329
1522 l_calling_appl_id VARCHAR2(10) := NULL;
1523 l_model_routing_enabled_for_bv BOOLEAN := FALSE;
1524 l_calling_appl_ids VARCHAR2(255) := NULL;
1525 -- XML building blocks:
1526 l_xml_message_header VARCHAR2(40) := '?XMLmsg=';
1527 l_batch_validate_open VARCHAR2(80) := '<batch_validate>';
1528 l_batch_validate_close VARCHAR2(40) := '</batch_validate>';
1529 l_config_inputs_open VARCHAR2(40) := '<config_inputs>';
1530 l_config_inputs_close VARCHAR2(40) := '</config_inputs>';
1531 l_config_header_id_open VARCHAR2(40) := '<config_header_id>';
1532 l_config_header_id_close VARCHAR2(40) := '</config_header_id>';
1533 l_config_rev_nbr_open VARCHAR2(40) := '<config_rev_nbr>';
1534 l_config_rev_nbr_close VARCHAR2(40) := '</config_rev_nbr>';
1535 l_terminate_open VARCHAR2(40) := '<terminate>';
1536 l_config_info_str VARCHAR2(250):= '';
1537 detailed_excp_flag boolean;
1538 -- n0 integer; n1 integer; n2 integer; msg varchar2(255);
1539
1540 BEGIN
1541 UTL_HTTP.get_detailed_excp_support (detailed_excp_flag);
1542
1543 IF detailed_excp_flag<>true
1544 THEN
1545 UTL_HTTP.set_detailed_excp_support(enable =>TRUE);
1546 END IF;
1547
1548 --Need to avoid this if possible
1549 l_init_message := p_init_msg;
1550 --For now we shall augument the init message with publication_id, only when this EMC-LBR-ER solution is enabled
1551 --Once this '+' logic works fine for quite some time, we can allow this unconditionally
1552 IF(UPPER(TRIM(FND_PROFILE.VALUE('CZ_ADD_MODEL_ROUTING_COOKIE'))) LIKE 'Y%') THEN
1553 l_model_routing_enabled_for_bv := TRUE;
1554
1558 IF l_publication_id IS NULL THEN
1555 l_init_message := REPLACE(p_init_msg,'+',' ');
1556 publication_for_init_message (l_init_message, l_publication_id, l_product_key, l_calling_appl_id);
1557
1559 l_publication_id := '-666';
1560 END IF;
1561
1562 l_index := INSTR(LOWER(p_init_msg), '<initialize>') + LENGTH('<initialize>');
1563 l_init_message := SUBSTR(p_init_msg, 1, l_index-1) || '<param+name="publication_id">' || l_publication_id || '</param>' || SUBSTR(p_init_msg , l_index);
1564 --l_init_message := REPLACE(l_init_message,' ','+');
1565
1566
1567 -- vsingava 14 Jul '09 bug7674190
1568 -- See if the current calling apps doesn't want model routing enabled for BV. even if the profile option is enabled
1569 BEGIN
1570 l_calling_appl_ids := get_db_setting('ORAAPPS_INTEGRATE','SkipModelRoutingForBV');
1571 l_calling_appl_ids := REPLACE(l_calling_appl_ids, ' ', '');
1572 EXCEPTION
1573 WHEN OTHERS THEN
1574 l_calling_appl_ids := '';
1575 END;
1576 -- NB: Calling application id is optional for a session to start, especially when the init message has icx_ticket
1577 -- But when model routing is enabled and the above db setting is set, it is critical to have calling_appl_id
1578 -- We can not silently load the model, as though model routing is disabled, as it can defeat whole purpose of controller loading of models
1579 IF (l_calling_appl_ids <> '' AND
1580 (l_calling_appl_id = NULL OR LTRIM(l_calling_appl_id) = '')
1581 ) THEN
1582 RAISE MISSING_CALLING_APPL_ID;
1583 END IF;
1584 -- vsingava 14 Jul '09 bug7674190
1585 -- Model routing is disabled for BV call, in spite of the profile is enabled, when the
1586 -- the current calling application_id is one amongst the ones present in the cz_db_setting
1587 -- NB: The calling apps are comma seperated.
1588 IF (
1589 ( -- It has to be an exact match, if there is only one application set
1590 (INSTR(l_calling_appl_ids, ',') = 0)
1591 AND (l_calling_appl_ids = l_calling_appl_id)
1592 )
1593 OR
1594 ( --Otherwise, it can be at the beginning
1595 l_calling_appl_ids LIKE l_calling_appl_id || ',%'
1596 )
1597 OR
1598 ( --Or, it can be in the middle
1599 l_calling_appl_ids LIKE '%,' || l_calling_appl_id || ',%'
1600 )
1601 OR
1602 ( --Or, it can be at the end
1603 l_calling_appl_ids LIKE '%,' || l_calling_appl_id
1604 )
1605 ) THEN
1606 --vsingava 14 Jul '09 bug7674190
1607 -- That means, we do not want to do model routing for the current validate call
1608 l_model_routing_enabled_for_bv := FALSE;
1609 END IF;
1610 END IF;
1611
1612 IF p_pseudo_hdr_id IS NOT NULL THEN
1613 l_config_info_str := l_config_header_id_open || TO_CHAR(p_pseudo_hdr_id)
1614 || l_config_header_id_close || l_config_rev_nbr_open
1615 || '1' || l_config_rev_nbr_close;
1616 END IF;
1617
1618 -- Backdoor for providing an alternate URL through a db setting
1619 BEGIN
1620 l_url := get_db_setting('ORAAPPS_INTEGRATE','ALTBATCHVALIDATEURL');
1621 /* bug13043488 ensure cz_db_settings value should not be null */
1622 IF l_url IS NULL THEN
1623 l_url:= p_url;
1624 END IF;
1625 /* end bug13043488 */
1626 EXCEPTION
1627 WHEN OTHERS THEN
1628 l_url := p_url;
1629 END;
1630
1631 -- append validation type to batch validate tag
1632 IF p_validation_type IS NULL OR p_validation_type = CZ_API_PUB.VALIDATE_ORDER THEN
1633 l_batch_validate_open := batchurlencode('<batch_validate validation_type="validate_order">');
1634
1635 ELSIF (p_validation_type = CZ_API_PUB.VALIDATE_FULFILLMENT) THEN
1636 l_batch_validate_open := batchurlencode('<batch_validate validation_type="validate_fulfillment">');
1637 ELSIF (p_validation_type = CZ_API_PUB.INTERACTIVE) THEN
1638 l_batch_validate_open := batchurlencode('<batch_validate validation_type="interactive">');
1639 ELSE
1640 l_batch_validate_open := batchurlencode('<batch_validate validation_type="validate_return">');
1641 END IF;
1642
1643 l_url := l_url || l_xml_message_header || l_batch_validate_open || l_init_message;
1644
1645 IF(l_config_info_str IS NOT NULL)THEN
1646
1647 l_url := l_url || l_config_inputs_open || l_config_info_str || l_config_inputs_close;
1648 END IF;
1649
1650 l_url := l_url || l_batch_validate_close;
1651
1652 /*
1653 n0 := 1;
1654 n1 := instr(l_url, '</', n0);
1655 WHILE n1 > 0 LOOP
1656 n2 := instr(l_url, '>', n1) + 1;
1657 msg := substr(l_url, n0, (n2-n0));
1658 dbms_output.put_line(msg);
1659 n0 := n2;
1660 n1 := instr(l_url, '</', n0);
1661 END LOOP;
1662 IF n0 < length(l_url) THEN
1663 msg := substr(l_url, n0);
1664 dbms_output.put_line(msg);
1665 END IF;
1666 */
1667 IF (LENGTH(l_url) > INIT_MESSAGE_LIMIT) THEN
1668 ROLLBACK;
1669 x_validation_status := INIT_TOO_LONG;
1670 RETURN;
1671 END IF;
1672 COMMIT; -- pseudo config recs
1673
1674 IF (l_model_routing_enabled_for_bv = TRUE) THEN
1675 l_pool_identifier := pool_token_for_product_key(l_product_key);
1676 END IF;
1677 return_html_pieces(l_url, l_model_routing_enabled_for_bv, l_pool_identifier, x_config_xml_msg);
1678 IF (x_config_xml_msg.COUNT = 0) THEN
1679 x_validation_status := CONFIG_EXCEPTION;
1680 RETURN;
1681 -- RAISE ZERO_RESPONSE_LENGTH;
1682 END IF;
1683
1684 IF (INSTR(x_config_xml_msg(x_config_xml_msg.FIRST),l_terminate_open)<>0) THEN
1685 x_validation_status := CONFIG_PROCESSED;
1686 ELSE
1687 x_validation_status := CONFIG_PROCESSED_NO_TERMINATE;
1688 END IF;
1689
1690 EXCEPTION
1691 WHEN UTL_HTTP.INIT_FAILED THEN
1695 IF detailed_excp_flag<>true
1692 v_detailed_error_message:=' SQLCODE:'||UTL_HTTP.GET_DETAILED_SQLCODE||' ERROR:'||UTL_HTTP.GET_DETAILED_SQLERRM;
1693 x_validation_status := UTL_HTTP_INIT_FAILED;
1694
1696 THEN
1697 UTL_HTTP.set_detailed_excp_support(enable =>FALSE);
1698 END IF;
1699
1700 WHEN UTL_HTTP.REQUEST_FAILED OR UTL_HTTP.BAD_ARGUMENT
1701 OR UTL_HTTP.BAD_URL OR UTL_HTTP.PROTOCOL_ERROR
1702 OR UTL_HTTP.UNKNOWN_SCHEME OR UTL_HTTP.HEADER_NOT_FOUND
1703 OR UTL_HTTP.END_OF_BODY OR UTL_HTTP.ILLEGAL_CALL
1704 OR UTL_HTTP.HTTP_CLIENT_ERROR OR UTL_HTTP.HTTP_SERVER_ERROR
1705 OR UTL_HTTP.TOO_MANY_REQUESTS OR UTL_HTTP.PARTIAL_MULTIBYTE_CHAR
1706 OR UTL_HTTP.TRANSFER_TIMEOUT THEN
1707 v_detailed_error_message:=' SQLCODE:'||UTL_HTTP.GET_DETAILED_SQLCODE||' ERROR:'||UTL_HTTP.GET_DETAILED_SQLERRM;
1708 x_validation_status:=UTL_HTTP_REQUEST_FAILED;
1709
1710 IF detailed_excp_flag<>true
1711 THEN
1712 UTL_HTTP.set_detailed_excp_support(enable =>FALSE);
1713 END IF;
1714
1715 WHEN MISSING_CALLING_APPL_ID THEN
1716 v_detailed_error_message := 'Batch validation message does not carry calling_application_id param.';
1717 x_validation_status := CONFIG_EXCEPTION;
1718 IF detailed_excp_flag<>true
1719 THEN
1720 UTL_HTTP.set_detailed_excp_support(enable =>FALSE);
1721 END IF;
1722
1723 WHEN OTHERS THEN
1724 IF detailed_excp_flag<>true
1725 THEN
1726 UTL_HTTP.set_detailed_excp_support(enable =>FALSE);
1727 END IF;
1728
1729 IF sqlcode='-12545'
1730 then
1731 x_validation_status:=UTL_HTTP_REQUEST_FAILED;
1732 ELSE
1733 RAISE;
1734 END IF;
1735 END validate; -- pvt
1736
1737 --------------------------------------------------------------------------------
1738
1739 procedure validate(p_api_version IN NUMBER
1740 ,p_config_item_tbl IN config_item_tbl_type
1741 ,p_config_ext_attr_tbl IN config_ext_attr_tbl_type
1742 ,p_url IN VARCHAR2
1743 ,p_init_msg IN VARCHAR2
1744 ,p_validation_type IN VARCHAR2
1745 ,x_config_xml_msg OUT NOCOPY CFG_OUTPUT_PIECES
1746 ,x_return_status OUT NOCOPY VARCHAR2
1747 ,x_msg_count OUT NOCOPY NUMBER
1748 ,x_msg_data OUT NOCOPY VARCHAR2
1749 )
1750 IS
1751 PRAGMA AUTONOMOUS_TRANSACTION;
1752
1753 l_api_version CONSTANT NUMBER := 1.0;
1754 l_api_name CONSTANT VARCHAR2(30) := 'validate:new';
1755 l_miss_num CONSTANT INTEGER := -2147483648; -- java's Integer.MIN_VALUE
1756 l_nDebug PLS_INTEGER;
1757 l_idx PLS_INTEGER;
1758 l_msg VARCHAR2(1000);
1759 l_start INTEGER;
1760 l_end INTEGER;
1761
1762 l_log_stmt BOOLEAN;
1763 l_has_item BOOLEAN;
1764 l_has_attr BOOLEAN;
1765
1766 l_url VARCHAR2(32767);
1767 l_validation_type VARCHAR2(1);
1768 l_validation_status INTEGER;
1769 l_pseudo_hdr_id cz_config_hdrs.config_hdr_id%TYPE;
1770 l_operation_code VARCHAR2(40);
1771 l_rec1_seq INTEGER;
1772 l_config_hdr_id cz_config_hdrs.config_hdr_id%TYPE;
1773 l_config_rev_nbr cz_config_hdrs.config_rev_nbr%TYPE;
1774 l_ext_comp_code cz_config_items.node_identifier%TYPE;
1775 l_item_depth INTEGER;
1776
1777 l_upd_item_map NUMBER_TBL_INDEXBY_CHAR_TYPE;
1778 l_item_seq_map NUMBER_TBL_INDEXBY_TYPE;
1779 l_config_item_tbl NUMBER_TBL_INDEXBY_TYPE;
1780 l_ecc_tbl str1200_tbl_type;
1781 l_seq_nbr_tbl NUMBER_TBL_INDEXBY_TYPE;
1782 l_rec_seq_tbl NUMBER_TBL_INDEXBY_TYPE;
1783 l_operation_tbl NUMBER_TBL_INDEXBY_TYPE;
1784 l_quantity_tbl NUMBER_TBL_INDEXBY_TYPE;
1785 l_instance_name_tbl str255_tbl_type;
1786 l_loc_id_tbl NUMBER_TBL_INDEXBY_TYPE;
1787 l_loc_type_code_tbl str255_tbl_type;
1788 l_attr_nam_tbl str255_tbl_type;
1789 l_attr_grp_tbl str255_tbl_type;
1790 l_attr_val_tbl str255_tbl_type;
1791 v_detailed_error_message varchar2(2000);
1792 v_return_config_changed varchar2(2) := NULL;
1793
1794 procedure set_message(p_msg_name IN VARCHAR2
1795 ,p_token_name1 IN VARCHAR2
1796 ,p_token_value1 IN VARCHAR2
1797 ,p_token_name2 IN VARCHAR2
1798 ,p_token_value2 IN VARCHAR2
1799 ,p_token_name3 IN VARCHAR2
1800 ,p_token_value3 IN VARCHAR2
1801 ,p_token_name4 IN VARCHAR2
1802 ,p_token_value4 IN VARCHAR2
1803 ) IS
1804 BEGIN
1805 fnd_message.set_name('CZ', p_msg_name);
1806 fnd_message.set_token(p_token_name1, p_token_value1);
1807 fnd_message.set_token(p_token_name2, p_token_value2);
1808 fnd_message.set_token(p_token_name3, p_token_value3);
1809 fnd_message.set_token(p_token_name4, p_token_value4);
1810 fnd_msg_pub.add;
1811 END set_message;
1812
1813 BEGIN
1814 l_nDebug := 1;
1815
1816 IF (NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME)) THEN
1817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1818 END IF;
1819
1820 l_validation_type := p_validation_type;
1821 IF l_validation_type IS NULL THEN
1822 l_validation_type := CZ_API_PUB.VALIDATE_ORDER;
1823 ELSIF l_validation_type NOT IN
1824 (CZ_API_PUB.VALIDATE_ORDER, CZ_API_PUB.VALIDATE_FULFILLMENT,
1825 CZ_API_PUB.INTERACTIVE, CZ_API_PUB.VALIDATE_RETURN) THEN
1826 fnd_message.set_name('CZ', 'CZ_BV_INVALID_TYPE');
1827 fnd_message.set_token('TYPE', l_validation_type);
1828 fnd_msg_pub.add;
1832 IF p_init_msg IS NULL OR length(p_init_msg) = 0 THEN
1829 RAISE FND_API.G_EXC_ERROR;
1830 END IF;
1831
1833 fnd_message.set_name('CZ', 'CZ_BV_NULL_INITMSG');
1834 fnd_msg_pub.add;
1835 RAISE FND_API.G_EXC_ERROR;
1836 END IF;
1837
1838 l_url := p_url;
1839 IF l_url IS NULL THEN
1840 l_url := FND_PROFILE.Value('CZ_UIMGR_URL');
1841 END IF;
1842
1843 l_log_stmt := FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1844 IF l_log_stmt AND p_init_msg IS NOT NULL THEN
1845 CZ_UTILS.log_report(G_PKG_NAME,l_api_name,l_nDebug,'URL='||l_url,FND_LOG.LEVEL_STATEMENT);
1846 l_start := 1;
1847 l_end := instr(p_init_msg, '</param>', l_start);
1848 WHILE l_end > 0 LOOP
1849 l_end := l_end + 8;
1850 l_msg := substr(p_init_msg, l_start, (l_end - l_start));
1851 -- dbms_output.put_line(l_msg);
1852 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_STATEMENT);
1853 l_start := l_end;
1854 l_end := instr(p_init_msg, '</param>', l_start);
1855 END LOOP;
1856 IF l_start < length(p_init_msg) THEN
1857 l_msg := substr(p_init_msg, l_start);
1858 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_STATEMENT);
1859 -- dbms_output.put_line(l_msg);
1860 END IF;
1861 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug,
1862 'Validate type: ' || p_validation_type, FND_LOG.LEVEL_STATEMENT);
1863 END IF;
1864
1865 l_nDebug := 2;
1866 l_has_item := p_config_item_tbl IS NOT NULL AND p_config_item_tbl.COUNT > 0;
1867 l_has_attr := p_config_ext_attr_tbl IS NOT NULL AND p_config_ext_attr_tbl.COUNT > 0;
1868 IF l_has_item OR l_has_attr THEN
1869 -- create pseudo hdr rec
1870 l_pseudo_hdr_id := next_config_hdr_id;
1871 INSERT INTO CZ_CONFIG_HDRS (config_hdr_id
1872 ,config_rev_nbr
1873 ,name
1874 ,effective_usage_id
1875 ,component_instance_type
1876 ,model_instantiation_type
1877 ,CONFIG_DELTA_SPEC
1878 ,deleted_flag
1879 ,HAS_FAILURES
1880 )
1881 VALUES (l_pseudo_hdr_id
1882 ,1
1883 ,'new batch'
1884 ,ANY_USAGE_ID
1885 ,ROOT
1886 ,BV_MODEL_TYPE
1887 ,0
1888 ,'0'
1889 ,'0'
1890 );
1891
1892 IF l_log_stmt THEN
1893 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, 'pseudo hdr=' ||
1894 l_pseudo_hdr_id, FND_LOG.LEVEL_STATEMENT);
1895 END IF;
1896
1897 l_nDebug := 3;
1898 l_idx := 0;
1899 -- Note: if supporting new config in the future, then needs either to move the
1900 -- get_config_hdr call or to chg the method to handle no hdr info in init msg
1901 get_config_hdr(p_init_msg, l_config_hdr_id, l_config_rev_nbr);
1902
1903 l_nDebug := 4;
1904 IF l_has_item THEN
1905 l_rec1_seq := p_config_item_tbl(p_config_item_tbl.FIRST).sequence_nbr;
1906 IF l_rec1_seq = FND_API.G_MISS_NUM THEN
1907 l_rec1_seq := NULL;
1908 END IF;
1909
1910 FOR i IN p_config_item_tbl.FIRST..p_config_item_tbl.LAST
1911 LOOP
1912 IF l_log_stmt THEN
1913 l_msg := 'item rec ' || i || ':id=' || p_config_item_tbl(i).config_item_id ||
1914 ',seq=' || p_config_item_tbl(i).sequence_nbr ||
1915 ',opc=' || p_config_item_tbl(i).operation ||
1916 ',qty=' || p_config_item_tbl(i).quantity ||
1917 ',nam=' || p_config_item_tbl(i).instance_name ||
1918 ',lid=' || p_config_item_tbl(i).location_id ||
1919 ',ltc=' || p_config_item_tbl(i).location_type_code;
1920 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_STATEMENT);
1921 END IF;
1922
1923 IF p_config_item_tbl(i).config_item_id IS NULL OR
1924 p_config_item_tbl(i).config_item_id = FND_API.G_MISS_NUM THEN
1925 set_message('CZ_BV_NULL_VAL', 'COLUMN', 'CONFIG_ITEM_ID', 'TYPE', 'ITEM',
1926 'IDX', i, 'SEQ', p_config_item_tbl(i).sequence_nbr);
1927 RAISE FND_API.G_EXC_ERROR;
1928 END IF;
1929
1930 IF p_config_item_tbl(i).operation IS NULL OR
1931 p_config_item_tbl(i).operation = FND_API.G_MISS_NUM THEN
1932 set_message('CZ_BV_NULL_VAL', 'COLUMN', 'OPERATION', 'TYPE', 'ITEM',
1933 'IDX', i, 'SEQ', p_config_item_tbl(i).sequence_nbr);
1934 RAISE FND_API.G_EXC_ERROR;
1935 ELSIF p_config_item_tbl(i).operation NOT IN (BV_OPERATION_UPDATE,BV_OPERATION_DELETE) THEN
1936 IF p_config_item_tbl(i).operation = BV_OPERATION_INSERT THEN
1937 l_operation_code := 'INSERT';
1938 ELSIF p_config_item_tbl(i).operation = BV_OPERATION_REVERT THEN
1939 l_operation_code := 'REVERT';
1940 ELSE
1941 l_operation_code := to_char(p_config_item_tbl(i).operation);
1942 END IF;
1943 fnd_message.set_name('CZ', 'CZ_BV_INVALID_OP');
1944 fnd_message.set_token('CODE', l_operation_code);
1945 fnd_message.set_token('IDX', i);
1946 fnd_message.set_token('SEQ', p_config_item_tbl(i).sequence_nbr);
1947 fnd_msg_pub.add;
1948 RAISE FND_API.G_EXC_ERROR;
1949 ELSIF p_config_item_tbl(i).operation = BV_OPERATION_UPDATE AND
1950 p_config_item_tbl(i).instance_name = FND_API.G_MISS_CHAR THEN
1951 set_message('CZ_BV_NULL_VAL', 'COLUMN', 'INSTANCE_NAME', 'TYPE', 'ITEM',
1952 'IDX', i, 'SEQ', p_config_item_tbl(i).sequence_nbr);
1953 RAISE FND_API.G_EXC_ERROR;
1954 END IF;
1955
1959 set_message('CZ_BV_NULL_VAL', 'COLUMN', 'SEQUENCE_NBR', 'TYPE', 'ITEM',
1956 IF l_rec1_seq IS NULL AND p_config_item_tbl(i).sequence_nbr IS NOT NULL OR
1957 l_rec1_seq IS NOT NULL AND (p_config_item_tbl(i).sequence_nbr IS NULL OR
1958 p_config_item_tbl(i).sequence_nbr=FND_API.G_MISS_NUM) THEN
1960 'IDX', i, 'SEQ', p_config_item_tbl(i).sequence_nbr);
1961 RAISE FND_API.G_EXC_ERROR;
1962 ELSIF p_config_item_tbl(i).sequence_nbr IS NOT NULL THEN
1963 IF l_item_seq_map.exists(p_config_item_tbl(i).sequence_nbr) THEN
1964 fnd_message.set_name('CZ', 'CZ_BV_DUP_SEQ');
1965 fnd_message.set_token('SEQ', p_config_item_tbl(i).sequence_nbr);
1966 fnd_msg_pub.add;
1967 RAISE FND_API.G_EXC_ERROR;
1968 ELSE
1969 l_item_seq_map(p_config_item_tbl(i).sequence_nbr) := i;
1970 END IF;
1971 END IF;
1972
1973 -- construct extended component code
1974 get_ext_comp_code(l_config_hdr_id, l_config_rev_nbr,
1975 p_config_item_tbl(i).config_item_id,
1976 l_ext_comp_code, l_item_depth);
1977
1978 IF l_ext_comp_code IS NULL OR length(l_ext_comp_code) = 0 THEN
1979 fnd_message.set_name('CZ', 'CZ_BV_INVALID_ITEM');
1980 fnd_message.set_token('HDR', l_config_hdr_id);
1981 fnd_message.set_token('REV', l_config_rev_nbr);
1982 fnd_message.set_token('ID', p_config_item_tbl(i).config_item_id);
1983 fnd_message.set_token('TYPE', 'ITEM');
1984 fnd_message.set_token('IND', i);
1985 fnd_message.set_token('SEQ', p_config_item_tbl(i).sequence_nbr);
1986 fnd_msg_pub.add;
1987 RAISE FND_API.G_EXC_ERROR;
1988 ELSIF p_config_item_tbl(i).operation = BV_OPERATION_UPDATE THEN
1989 l_upd_item_map(p_config_item_tbl(i).config_item_id) := p_config_item_tbl(i).config_item_id;
1990 END IF;
1991
1992 l_idx := l_idx + 1;
1993 l_config_item_tbl(l_idx) := p_config_item_tbl(i).config_item_id;
1994 l_ecc_tbl(l_idx) := l_ext_comp_code;
1995 l_seq_nbr_tbl(l_idx) := NVL(p_config_item_tbl(i).sequence_nbr, l_item_depth);
1996 l_operation_tbl(l_idx) := p_config_item_tbl(i).operation;
1997 l_quantity_tbl(l_idx) := p_config_item_tbl(i).quantity;
1998 IF l_quantity_tbl(l_idx) = FND_API.G_MISS_NUM THEN
1999 l_quantity_tbl(l_idx) := l_miss_num;
2000 END IF;
2001 l_instance_name_tbl(l_idx) := p_config_item_tbl(i).instance_name;
2002 l_loc_id_tbl(l_idx) := p_config_item_tbl(i).location_id;
2003 IF l_loc_id_tbl(l_idx) = FND_API.G_MISS_NUM THEN
2004 l_loc_id_tbl(l_idx) := l_miss_num;
2005 END IF;
2006 l_loc_type_code_tbl(l_idx) := p_config_item_tbl(i).location_type_code;
2007 l_rec_seq_tbl(l_idx) := i;
2008
2009 END LOOP;
2010
2011 l_nDebug := 5;
2012 FORALL i IN l_config_item_tbl.first .. l_config_item_tbl.lAST
2013 INSERT INTO CZ_CONFIG_ITEMS
2014 (config_hdr_id
2015 ,config_rev_nbr
2016 ,config_item_id
2017 ,sequence_nbr
2018 ,value_type_code
2019 ,node_identifier
2020 ,item_num_val
2021 ,INSTANCE_HDR_ID
2022 ,INSTANCE_REV_NBR
2023 ,COMPONENT_INSTANCE_TYPE
2024 ,CONFIG_DELTA
2025 ,name
2026 ,location_id
2027 ,location_type_code
2028 )
2029 VALUES (l_pseudo_hdr_id
2030 ,1
2031 ,l_config_item_tbl(i)
2032 ,l_seq_nbr_tbl(i)
2033 ,l_operation_tbl(i)
2034 ,l_ecc_tbl(i)
2035 ,l_quantity_tbl(i)
2036 ,l_pseudo_hdr_id
2037 ,1
2038 ,'T'
2039 ,l_rec_seq_tbl(i)
2040 ,l_instance_name_tbl(i)
2041 ,l_loc_id_tbl(i)
2042 ,l_loc_type_code_tbl(i)
2043 );
2044
2045 -- reoder sequences if generated by this proc
2046 l_nDebug := 6;
2047 IF l_rec1_seq IS NULL THEN
2048 SELECT config_item_id BULK COLLECT INTO l_config_item_tbl
2049 FROM cz_config_items
2050 WHERE config_hdr_id = l_pseudo_hdr_id AND config_rev_nbr = 1
2051 ORDER BY sequence_nbr, config_delta;
2052
2053 FOR i IN l_config_item_tbl.FIRST .. l_config_item_tbl.LAST LOOP
2054 l_seq_nbr_tbl(i) := i;
2055 END LOOP;
2056
2057 FORALL i IN l_config_item_tbl.FIRST .. l_config_item_tbl.LAST
2058 UPDATE cz_config_items
2059 SET sequence_nbr = l_seq_nbr_tbl(i)
2060 WHERE config_hdr_id = l_pseudo_hdr_id AND config_rev_nbr = 1
2061 AND config_item_id = l_config_item_tbl(i);
2062 IF l_log_stmt THEN
2063 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, 'item sequences reordered',
2064 FND_LOG.LEVEL_STATEMENT);
2065 END IF;
2066 END IF;
2067 END IF;
2068
2069 l_nDebug := 7;
2070 -- processing extended attributes
2071 IF l_has_attr THEN
2072 IF l_rec1_seq IS NULL AND NOT l_has_item THEN
2073 l_rec1_seq := p_config_ext_attr_tbl(p_config_ext_attr_tbl.FIRST).sequence_nbr;
2074 IF l_rec1_seq = FND_API.G_MISS_NUM THEN
2075 l_rec1_seq := NULL;
2076 END IF;
2077 END IF;
2078
2079 l_idx := 0;
2080 l_config_item_tbl.DELETE;
2081 l_ecc_tbl.DELETE;
2082 l_seq_nbr_tbl.DELETE;
2083 FOR i In p_config_ext_attr_tbl.FIRST .. p_config_ext_attr_tbl.LAST LOOP
2084 IF l_log_stmt THEN
2085 l_msg := 'attr rec ' || i || ': id=' || p_config_ext_attr_tbl(i).config_item_id ||
2089 ', val=' || p_config_ext_attr_tbl(i).attribute_value;
2086 ', seq=' || p_config_ext_attr_tbl(i).sequence_nbr ||
2087 ', nam=' || p_config_ext_attr_tbl(i).attribute_name ||
2088 ', grp=' || p_config_ext_attr_tbl(i).attribute_group ||
2090 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_STATEMENT);
2091 END IF;
2092
2093 IF p_config_ext_attr_tbl(i).config_item_id IS NULL OR
2094 p_config_ext_attr_tbl(i).config_item_id = FND_API.G_MISS_NUM THEN
2095 set_message('CZ_BV_NULL_VAL', 'COLUMN', 'CONFIG_ITEM_ID', 'TYPE', 'ATTRIBUTE',
2096 'IDX', i, 'SEQ', p_config_ext_attr_tbl(i).sequence_nbr);
2097 RAISE FND_API.G_EXC_ERROR;
2098 END IF;
2099
2100 IF l_rec1_seq IS NULL AND p_config_ext_attr_tbl(i).sequence_nbr IS NOT NULL OR
2101 l_rec1_seq IS NOT NULL AND (p_config_ext_attr_tbl(i).sequence_nbr IS NULL OR
2102 p_config_ext_attr_tbl(i).sequence_nbr=FND_API.G_MISS_NUM) THEN
2103 set_message('CZ_BV_NULL_VAL', 'COLUMN', 'SEQUENCE_NBR', 'TYPE', 'ATTRIBUTE',
2104 'IDX', i, 'SEQ', p_config_ext_attr_tbl(i).sequence_nbr);
2105 RAISE FND_API.G_EXC_ERROR;
2106 ELSIF p_config_ext_attr_tbl(i).sequence_nbr IS NOT NULL THEN
2107 IF l_item_seq_map.exists(p_config_ext_attr_tbl(i).sequence_nbr) THEN
2108 fnd_message.set_name('CZ', 'CZ_BV_DUP_SEQ');
2109 fnd_message.set_token('SEQ', p_config_ext_attr_tbl(i).sequence_nbr);
2110 fnd_msg_pub.add;
2111 RAISE FND_API.G_EXC_ERROR;
2112 ELSE
2113 l_item_seq_map(p_config_ext_attr_tbl(i).sequence_nbr) := i;
2114 END IF;
2115 END IF;
2116
2117 IF p_config_ext_attr_tbl(i).attribute_name IS NULL OR
2118 p_config_ext_attr_tbl(i).attribute_name = FND_API.G_MISS_CHAR THEN
2119 set_message('CZ_BV_NULL_VAL', 'COLUMN', 'ATTRIBUTE_NAME', 'TYPE', 'ATTRIBUTE',
2120 'IDX', i, 'SEQ', p_config_ext_attr_tbl(i).sequence_nbr);
2121 RAISE FND_API.G_EXC_ERROR;
2122 END IF;
2123 IF p_config_ext_attr_tbl(i).attribute_value IS NULL THEN
2124 set_message('CZ_BV_NULL_VAL', 'COLUMN', 'ATTRIBUTE_VALUE', 'TYPE', 'ATTRIBUTE',
2125 'IDX', i, 'SEQ', p_config_ext_attr_tbl(i).sequence_nbr);
2126 RAISE FND_API.G_EXC_ERROR;
2127 END IF;
2128
2129 IF NOT l_upd_item_map.exists(p_config_ext_attr_tbl(i).config_item_id) THEN
2130 get_ext_comp_code(l_config_hdr_id, l_config_rev_nbr,
2131 p_config_ext_attr_tbl(i).config_item_id,
2132 l_ext_comp_code, l_item_depth);
2133
2134 IF l_ext_comp_code IS NULL OR length(l_ext_comp_code) = 0 THEN
2135 fnd_message.set_name('CZ', 'CZ_BV_INVALID_ITEM');
2136 fnd_message.set_token('HDR', l_config_hdr_id);
2137 fnd_message.set_token('REV', l_config_rev_nbr);
2138 fnd_message.set_token('ID', p_config_ext_attr_tbl(i).config_item_id);
2139 fnd_message.set_token('TYPE', 'ATTRIBUTE');
2140 fnd_message.set_token('IND', i);
2141 fnd_message.set_token('SEQ', p_config_ext_attr_tbl(i).sequence_nbr);
2142 fnd_msg_pub.add;
2143 RAISE FND_API.G_EXC_ERROR;
2144 END IF;
2145
2146 -- Create a dummy item with no-op for now, this part of code will be removed
2147 -- or modified after changing to not generate ecc
2148 -- If we do need to keep ecc, do a single bulk insert for both the item recs from
2149 -- inputs and the dummy recs here by adding the dummies to the tbls used in processing
2150 -- inputs and then moving bulk ins stmt to the end of processing attr
2151 INSERT INTO CZ_CONFIG_ITEMS
2152 (config_hdr_id
2153 ,config_rev_nbr
2154 ,config_item_id
2155 ,sequence_nbr
2156 ,value_type_code
2157 ,node_identifier
2158 ,INSTANCE_HDR_ID
2159 ,INSTANCE_REV_NBR
2160 ,COMPONENT_INSTANCE_TYPE
2161 ,CONFIG_DELTA
2162 )
2163 VALUES (l_pseudo_hdr_id
2164 ,1
2165 ,p_config_ext_attr_tbl(i).config_item_id
2166 ,i -- do not matter for no-op item
2167 ,BV_OPERATION_UPDATE
2168 ,l_ext_comp_code
2169 ,l_pseudo_hdr_id
2170 ,1
2171 ,'T'
2172 ,l_item_depth
2173 );
2174 l_upd_item_map(p_config_ext_attr_tbl(i).config_item_id) := p_config_ext_attr_tbl(i).config_item_id;
2175 END IF;
2176
2177 l_idx := l_idx + 1;
2178 l_config_item_tbl(l_idx) := p_config_ext_attr_tbl(i).config_item_id;
2179 l_attr_nam_tbl(l_idx) := p_config_ext_attr_tbl(i).attribute_name;
2180 l_attr_grp_tbl(l_idx) := p_config_ext_attr_tbl(i).attribute_group;
2181 l_attr_val_tbl(l_idx) := p_config_ext_attr_tbl(i).attribute_value;
2182 l_seq_nbr_tbl(l_idx) := NVL(p_config_ext_attr_tbl(i).sequence_nbr, i);
2183 END LOOP;
2184
2185 l_nDebug := 8;
2186 FORALL i IN l_config_item_tbl.first .. l_config_item_tbl.lAST
2187 INSERT INTO cz_config_ext_attributes(config_hdr_id
2188 ,config_rev_nbr
2189 ,config_item_id
2190 ,attribute_name
2191 ,attribute_group
2192 ,attribute_value
2193 ,sequence_nbr
2194 )
2195 VALUES(l_pseudo_hdr_id
2196 ,1
2197 ,l_config_item_tbl(i)
2201 ,l_seq_nbr_tbl(i)
2198 ,l_attr_nam_tbl(i)
2199 ,l_attr_grp_tbl(i)
2200 ,l_attr_val_tbl(i)
2202 );
2203
2204 -- reorder sequences if generated by this proc
2205 l_nDebug := 9;
2206 IF l_rec1_seq IS NULL THEN
2207 l_config_item_tbl.DELETE;
2208 l_seq_nbr_tbl.DELETE;
2209 SELECT attr.config_item_id BULK COLLECT INTO l_config_item_tbl
2210 FROM cz_config_ext_attributes attr, cz_config_items item
2211 WHERE attr.config_hdr_id = l_pseudo_hdr_id AND attr.config_rev_nbr = 1
2212 AND attr.config_hdr_id = item.config_hdr_id AND attr.config_rev_nbr = item.config_rev_nbr
2213 AND attr.config_item_id = item.config_item_id
2214 ORDER BY nvl(length(translate(item.node_identifier,'-0123456789','A')),0), attr.sequence_nbr;
2215
2216 FOR i IN l_config_item_tbl.FIRST .. l_config_item_tbl.LAST LOOP
2217 l_seq_nbr_tbl(i) := i + l_idx;
2218 END LOOP;
2219
2220 FORALL i IN l_config_item_tbl.FIRST .. l_config_item_tbl.LAST
2221 UPDATE cz_config_ext_attributes
2222 SET sequence_nbr = l_seq_nbr_tbl(i)
2223 WHERE config_hdr_id = l_pseudo_hdr_id AND config_rev_nbr = 1
2224 AND config_item_id = l_config_item_tbl(i);
2225
2226 IF l_log_stmt THEN
2227 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, 'attr sequences reordered',
2228 FND_LOG.LEVEL_STATEMENT);
2229 END IF;
2230 END IF;
2231
2232 END IF;
2233 END IF;
2234
2235 l_nDebug := 10;
2236 validate(l_pseudo_hdr_id
2237 ,l_url
2238 ,p_init_msg
2239 ,l_validation_type
2240 ,l_validation_status
2241 ,x_config_xml_msg
2242 ,v_detailed_error_message
2243 );
2244
2245 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, 'validation status: ' ||
2246 l_validation_status||v_detailed_error_message, FND_LOG.LEVEL_PROCEDURE);
2247
2248 IF l_validation_status = INIT_TOO_LONG THEN
2249 fnd_message.set_name('CZ', 'CZ_BV_ERR_INIT_MSG');
2250 fnd_message.SET_TOKEN('ERROR_MSG', v_detailed_error_message);
2251 fnd_msg_pub.add;
2252 RAISE FND_API.G_EXC_ERROR;
2253 ELSIF l_validation_status = UTL_HTTP_INIT_FAILED THEN
2254 fnd_message.set_name('CZ', 'CZ_BV_ERR_HTTP_INIT');
2255 fnd_message.SET_TOKEN('ERROR_MSG', v_detailed_error_message);
2256 fnd_msg_pub.add;
2257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2258 ELSIF l_validation_status = UTL_HTTP_REQUEST_FAILED THEN
2259 fnd_message.set_name('CZ', 'CZ_BV_ERR_HTTP_REQ');
2260 fnd_message.SET_TOKEN('ERROR_MSG', v_detailed_error_message);
2261 fnd_msg_pub.add;
2262 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2263 ELSIF l_validation_status = CONFIG_PROCESSED_NO_TERMINATE OR
2264 l_validation_status = CONFIG_EXCEPTION THEN
2265 FOR i IN (SELECT message FROM cz_config_messages
2266 WHERE config_hdr_id = l_pseudo_hdr_id AND config_rev_nbr = 1
2267 ORDER BY message_seq) LOOP
2268 fnd_msg_pub.Add_Exc_Msg(p_error_text => i.message);
2269 END LOOP;
2270 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2271 END IF;
2272
2273 l_nDebug := 11;
2274 IF l_validation_status = CONFIG_PROCESSED AND l_validation_type = CZ_API_PUB.VALIDATE_ORDER
2275 AND NOT (l_has_item OR l_has_attr) THEN
2276 -- Added 2 new params as per the Quoting ER#9348864 as said earlier. But for
2277 -- this call they are not required though for this VALIDATE API.
2278 check_deltas(p_init_msg,'N',x_config_xml_msg,x_return_status,v_return_config_changed);
2279 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2280 fnd_msg_pub.Add_Exc_Msg(p_error_text => 'Error from check_delta:');
2281 FOR i IN (SELECT message FROM cz_config_messages
2282 WHERE config_hdr_id = l_pseudo_hdr_id AND config_rev_nbr = 1
2283 ORDER BY message_seq) LOOP
2284 fnd_msg_pub.Add_Exc_Msg(p_error_text => i.message);
2285 END LOOP;
2286 RAISE FND_API.G_EXC_ERROR;
2287 END IF;
2288 END IF;
2289
2290 l_nDebug := 12;
2291 delete_bv_records(l_pseudo_hdr_id, TRUE, TRUE);
2292
2293 x_return_status := FND_API.G_RET_STS_SUCCESS;
2294
2295 EXCEPTION
2296 WHEN FND_API.G_EXC_ERROR THEN
2297 x_return_status := FND_API.G_RET_STS_ERROR;
2298 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2299 p_data => x_msg_data);
2300 ROLLBACK;
2301
2302 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2303 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2304 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2305 p_data => x_msg_data);
2306 delete_bv_records(l_pseudo_hdr_id, FALSE, TRUE);
2307
2308 WHEN OTHERS THEN
2309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2310 IF fnd_msg_pub.check_msg_Level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
2311 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
2312 END IF;
2313
2314 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2315 p_data => x_msg_data);
2316 delete_bv_records(l_pseudo_hdr_id, FALSE, TRUE);
2317 END validate; -- new
2318
2319 --------------------------------------------------------------------------------
2320 -- Quoting ER#9348864. Retaining the existing API so that there should NOT be
2321 -- any problem for any existing customer/applications.
2322 -- created a new VALIDATE API for the ER maintioned with extra one IN and one OUT param (right below).
2323 -- So, any existing caller will continue to call this APIi but this will be wrapper of the new VALIDATE API
2324 -- that was created for quote. Hence the new VALIDATE API is called inside this orginal VALIDATE API
2328 config_messages IN OUT NOCOPY CFG_OUTPUT_PIECES,
2325 ---------------------------------------------------------------------------------
2326 PROCEDURE VALIDATE (config_input_list IN CFG_INPUT_LIST,
2327 init_message IN VARCHAR2,
2329 validation_status IN OUT NOCOPY NUMBER,
2330 URL IN VARCHAR2 DEFAULT FND_PROFILE.Value('CZ_UIMGR_URL'),
2331 p_validation_type IN VARCHAR2 DEFAULT CZ_API_PUB.VALIDATE_ORDER)
2332
2333 IS
2334 p_check_config_flag VARCHAR2(2) := 'N';
2335 x_return_config_changed VARCHAR2(2) := NULL;
2336
2337 BEGIN
2338
2339 CZ_CF_API.VALIDATE (config_input_list ,
2340 init_message ,
2341 config_messages ,
2342 validation_status ,
2343 URL ,
2344 p_validation_type ,
2345 p_check_config_flag ,
2346 x_return_config_changed );
2347
2348
2349 -- NO need to handle exception here as the original API has taken care of this
2350 END VALIDATE;
2351 --------------------------------------------------------------------------------------
2352 -- This is the new API created for Quoting ER#9348864
2353 -- Added 2 new params, one IN and one OUT as per designed
2354 -- new IN param: p_check_config_flag should be passed 'Y' by the caller to get the new behavior.
2355 -- passing 'N' will result the same behavior as other VALIDATE signature.
2356 -- new OUT param: p_return_config_changed. Valid values are : 'Y'(Changed), 'N' (Unchanged) and NULL (no request from caller)
2357 -- If the p_check_config_flag is Y then x_return_config_changed will be Y or N depending on change in configuration
2358 -- Else, it will return NULL when p_check_config_flag is not passed by the caller.
2359 -- Purpose: As per the ER, when there is a change in configuration (means delta
2360 -- exists) during BV call, the new VALIDATE should NOT fail but return an indicator to caller saying there is
2361 -- a change in configuration. Details are in ER.
2362 --------------------------------------------------------------------------------------
2363 PROCEDURE VALIDATE (config_input_list IN CFG_INPUT_LIST,
2364 init_message IN VARCHAR2,
2365 config_messages IN OUT NOCOPY CFG_OUTPUT_PIECES,
2366 validation_status IN OUT NOCOPY NUMBER,
2367 URL IN VARCHAR2 DEFAULT FND_PROFILE.Value('CZ_UIMGR_URL'),
2368 p_validation_type IN VARCHAR2 DEFAULT CZ_API_PUB.VALIDATE_ORDER,
2369 p_check_config_flag IN VARCHAR2 DEFAULT 'N',
2370 x_return_config_changed OUT NOCOPY VARCHAR2)
2371
2372 IS
2373 PRAGMA AUTONOMOUS_TRANSACTION;
2374
2375 l_api_name CONSTANT VARCHAR2(20) := 'validate:old';
2376 l_nDebug PLS_INTEGER;
2377 l_log_stmt BOOLEAN;
2378 l_msg VARCHAR2(2000);
2379
2380 l_pseudo_hdr_id NUMBER;
2381 l_pseudo_item_id PLS_INTEGER;
2382 l_rec1_seq NUMBER;
2383 l_hdr_id NUMBER;
2384 l_rev_nbr NUMBER;
2385 l_item_id NUMBER;
2386 l_ext_comp_code cz_config_items.node_identifier%TYPE;
2387 l_item_depth INTEGER;
2388 l_delta_status VARCHAR2(1);
2389 l_check_db_setting BOOLEAN;
2390
2391 l_ecc_tbl str1200_tbl_type;
2392 l_item_id_tbl NUMBER_TBL_INDEXBY_TYPE;
2393 l_quantity_tbl NUMBER_TBL_INDEXBY_TYPE;
2394 l_input_seq_tbl NUMBER_TBL_INDEXBY_TYPE;
2395 l_item_depth_tbl NUMBER_TBL_INDEXBY_TYPE;
2396
2397 v_detailed_error_message varchar2(2000);
2398
2399 DELTA_CHECK_FAILURE EXCEPTION;
2400
2401 BEGIN
2402 l_nDebug := 1;
2403 l_log_stmt := FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2404 IF l_log_stmt THEN
2405 l_msg := 'Number of inputs=' || config_input_list.COUNT;
2406 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_STATEMENT);
2407 END IF;
2408
2409 IF config_input_list.COUNT > 0 THEN
2410 BEGIN
2411 l_pseudo_hdr_id := next_config_hdr_id;
2412 INSERT INTO CZ_CONFIG_HDRS (config_hdr_id
2413 ,config_rev_nbr
2414 ,name
2415 ,effective_usage_id
2416 ,deleted_flag
2417 ,CONFIG_DELTA_SPEC
2418 ,COMPONENT_INSTANCE_TYPE
2419 ,MODEL_INSTANTIATION_TYPE
2420 ,HAS_FAILURES)
2421 VALUES (l_pseudo_hdr_id
2422 ,1
2423 ,'old batch'
2424 ,ANY_USAGE_ID
2425 ,'0'
2426 ,0
2427 ,ROOT
2428 ,BV_MODEL_TYPE
2429 ,'0');
2430
2431 l_rec1_seq := config_input_list(config_input_list.FIRST).input_seq;
2432 IF l_log_stmt THEN
2433 l_msg := '1st rec input_seq=' || l_rec1_seq;
2434 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_STATEMENT);
2435 l_msg := 'pseudo hdr=' || l_pseudo_hdr_id;
2436 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_STATEMENT);
2437 END IF;
2438
2439 l_nDebug := 2;
2440 l_hdr_id := 0;
2441 l_pseudo_item_id := -1;
2442 FOR i IN config_input_list.FIRST..config_input_list.LAST
2443 LOOP
2444 IF config_input_list(i).component_code IS NULL OR
2445 config_input_list(i).quantity IS NULL THEN
2446 l_msg := 'The component code or quantity passed in rec ' || i || ' is NULL';
2447 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_ERROR);
2448 RAISE INVALID_OPTION_EXCEPTION;
2449 END IF;
2450
2454 l_input_seq_tbl(i) := i;
2451 l_quantity_tbl(i) := config_input_list(i).quantity;
2452
2453 IF l_rec1_seq IS NULL THEN
2455 ELSE
2456 l_input_seq_tbl(i) := config_input_list(i).input_seq;
2457 END IF;
2458
2459 IF (config_input_list(i).config_item_id IS NOT NULL) THEN
2460 l_item_id := config_input_list(i).config_item_id;
2461 IF l_hdr_id = 0 THEN
2462 get_config_hdr(init_message, l_hdr_id, l_rev_nbr);
2463 IF l_log_stmt THEN
2464 l_msg := 'hdr in init msg=' || l_hdr_id || ',' || l_rev_nbr;
2465 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_STATEMENT);
2466 END IF;
2467 END IF;
2468 get_ext_comp_code(l_hdr_id, l_rev_nbr, l_item_id, l_ext_comp_code, l_item_depth);
2469 IF l_ext_comp_code IS NULL OR l_ext_comp_code = '' THEN
2470 l_msg := 'The config item id '||l_item_id||' passed in rec '||i||' for config header id '||
2471 l_hdr_id || ' and rev nbr ' || l_rev_nbr || ' is invalid';
2472 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_ERROR);
2473 RAISE INVALID_OPTION_EXCEPTION;
2474 END IF;
2475 ELSE
2476 append_instance_nbr(config_input_list(i).component_code, l_ext_comp_code, l_item_depth);
2477 l_item_id := l_pseudo_item_id;
2478 l_pseudo_item_id := l_pseudo_item_id - 1;
2479 END IF;
2480
2481 l_item_id_tbl(i) := l_item_id;
2482 l_ecc_tbl(i) := l_ext_comp_code;
2483 l_item_depth_tbl(i) := l_item_depth;
2484 END LOOP;
2485
2486 l_nDebug := 3;
2487 FORALL i IN l_item_id_tbl.first .. l_item_id_tbl.lAST
2488 INSERT INTO CZ_CONFIG_ITEMS(config_hdr_id
2489 ,config_rev_nbr
2490 ,config_item_id
2491 ,sequence_nbr
2492 ,node_identifier
2493 ,item_num_val
2494 ,value_type_code
2495 ,INSTANCE_HDR_ID
2496 ,INSTANCE_REV_NBR
2497 ,COMPONENT_INSTANCE_TYPE
2498 ,CONFIG_DELTA
2499 )
2500 VALUES (l_pseudo_hdr_id
2501 ,1
2502 ,l_item_id_tbl(i)
2503 ,l_input_seq_tbl(i)
2504 ,l_ecc_tbl(i)
2505 ,l_quantity_tbl(i)
2506 ,BV_OPERATION_OLD
2507 ,l_pseudo_hdr_id
2508 ,1
2509 ,INCLUDED
2510 ,l_item_depth_tbl(i)
2511 );
2512
2513 l_nDebug := 4;
2514 -- Ideally reorder inputs only if the sequences are generated locally
2515 -- But the sequences OM passes to us are actually meaningless. So
2516 -- unfortunately we have to take this performance hit and to reorder
2517 -- the inputsregardless of who generates the seq
2518 -- IF l_rec1_seq IS NULL THEN
2519 l_item_id_tbl.delete;
2520 l_input_seq_tbl.delete;
2521 SELECT config_item_id BULK COLLECT INTO l_item_id_tbl
2522 FROM cz_config_items
2523 WHERE config_hdr_id = l_pseudo_hdr_id
2524 AND config_rev_nbr = 1
2525 ORDER BY config_delta, sequence_nbr;
2526
2527 FOR i IN l_item_id_tbl.FIRST .. l_item_id_tbl.LAST LOOP
2528 l_input_seq_tbl(i) := i;
2529 END LOOP;
2530
2531 FORALL i IN l_item_id_tbl.FIRST .. l_item_id_tbl.LAST
2532 UPDATE cz_config_items
2533 SET sequence_nbr = l_input_seq_tbl(i)
2534 WHERE config_hdr_id = l_pseudo_hdr_id
2535 AND config_rev_nbr = 1
2536 AND config_item_id = l_item_id_tbl(i);
2537 -- END IF;
2538
2539 EXCEPTION
2540 WHEN INVALID_OPTION_EXCEPTION THEN
2541 validation_status:=INVALID_OPTION_REQUEST;
2542 ROLLBACK;
2543 RETURN;
2544
2545 WHEN OTHERS THEN
2546 validation_status:=DATABASE_ERROR;
2547 ROLLBACK;
2548 l_msg := SQLERRM;
2549 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_ERROR);
2550 RETURN;
2551 END;
2552
2553 END IF;
2554
2555 l_nDebug := 5;
2556 validate(l_pseudo_hdr_id
2557 ,URL
2558 ,init_message
2559 ,p_validation_type
2560 ,validation_status
2561 ,config_messages
2562 ,v_detailed_error_message
2563 );
2564
2565 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, 'validation status: ' ||
2566 validation_status||v_detailed_error_message, FND_LOG.LEVEL_PROCEDURE);
2567
2568 l_nDebug := 6;
2569 -- this block would set the config status status in the terminate message to FALSE
2570 -- if the config_input_list is empty and validation_status is CONFIG_PROCESSED
2571 -- and if there are configured item changes. The changes are logged to cz_config_messages
2572 IF validation_status = CONFIG_PROCESSED AND p_validation_type = CZ_API_PUB.VALIDATE_ORDER AND
2573 config_input_list.COUNT = 0 THEN
2574 -- Added 2 new params as per the Quoting ER#9348864 as said earlier
2575 check_deltas(init_message,p_check_config_flag,config_messages,l_delta_status,x_return_config_changed);
2576 IF (l_delta_status <> FND_API.G_RET_STS_SUCCESS) THEN
2577 RAISE DELTA_CHECK_FAILURE;
2578 END IF;
2579 END IF;
2580
2584 IF validation_status = UTL_HTTP_INIT_FAILED OR
2581 l_nDebug := 7;
2582 -- delete based on setting in cz_db_settings
2583 l_check_db_setting := TRUE;
2585 validation_status = UTL_HTTP_REQUEST_FAILED THEN
2586 l_check_db_setting := FALSE;
2587 END IF;
2588
2589 delete_bv_records(l_pseudo_hdr_id, l_check_db_setting, FALSE);
2590
2591 EXCEPTION
2592 WHEN DELTA_CHECK_FAILURE THEN
2593 validation_status:=CONFIG_EXCEPTION;
2594 COMMIT;
2595 l_msg := 'DELTA_CHECK_FAILURE';
2596 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg||v_detailed_error_message, FND_LOG.LEVEL_ERROR);
2597
2598 WHEN OTHERS THEN
2599 delete_bv_records(l_pseudo_hdr_id, FALSE, FALSE);
2600 validation_status:=CONFIG_EXCEPTION;
2601 l_msg := SQLERRM;
2602 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg||v_detailed_error_message, FND_LOG.LEVEL_ERROR);
2603 END validate; -- old
2604
2605 ------------------------------------------------------------------------------------------
2606 FUNCTION model_for_item(inventory_item_id NUMBER,
2607 organization_id NUMBER,
2608 config_creation_date DATE,
2609 user_id NUMBER,
2610 responsibility_id NUMBER,
2611 calling_application_id NUMBER
2612 )
2613
2614 RETURN NUMBER
2615 IS
2616 BEGIN
2617 RETURN config_model_for_item(inventory_item_id, organization_id,
2618 config_creation_date, calling_application_id,
2619 NULL);
2620 END model_for_item;
2621
2622 --------------------------------------------------------------------------------------------
2623 FUNCTION config_model_for_item (inventory_item_id IN NUMBER,
2624 organization_id IN NUMBER,
2625 config_lookup_date IN DATE,
2626 calling_application_id IN NUMBER,
2627 usage_name IN VARCHAR2,
2628 publication_mode IN VARCHAR2 DEFAULT NULL,
2629 language IN VARCHAR2 DEFAULT NULL
2630 )
2631 RETURN NUMBER
2632 IS
2633
2634 v_publication_id NUMBER;
2635
2636 BEGIN
2637
2638 v_publication_id := publication_for_item(inventory_item_id,organization_id,
2639 config_lookup_date,
2640 calling_application_id,usage_name,
2641 publication_mode,
2642 language);
2643
2644 IF v_publication_id IS NULL THEN
2645 RETURN NULL;
2646 ELSE
2647 RETURN model_for_publication_id(v_publication_id);
2648 END IF;
2649
2650 END config_model_for_item;
2651
2652 --------------------------------------------------------------------------------
2653 FUNCTION config_models_for_items (inventory_item_id IN NUMBER_TBL_TYPE,
2654 organization_id IN NUMBER_TBL_TYPE,
2655 config_lookup_date IN DATE_TBL_TYPE,
2656 calling_application_id IN NUMBER_TBL_TYPE,
2657 usage_name IN VARCHAR2_TBL_TYPE,
2658 publication_mode IN VARCHAR2_TBL_TYPE,
2659 language IN VARCHAR2_TBL_TYPE
2660 )
2661 RETURN NUMBER_TBL_TYPE
2662 IS
2663
2664 t_models_for_items NUMBER_TBL_TYPE := NUMBER_TBL_TYPE();
2665
2666 nof_inventory_item_id NUMBER;
2667 nof_organization_id NUMBER;
2668 nof_config_lookup_date NUMBER;
2669 nof_calling_application_id NUMBER;
2670 nof_usage_name NUMBER;
2671 nof_language NUMBER;
2672 nof_publication_mode NUMBER;
2673
2674 BEGIN
2675
2676 nof_inventory_item_id := inventory_item_id.COUNT;
2677 nof_organization_id := organization_id.COUNT;
2678 nof_config_lookup_date := config_lookup_date.COUNT;
2679 nof_calling_application_id := calling_application_id.COUNT;
2680 nof_usage_name := usage_name.COUNT;
2681 nof_language := language.COUNT;
2682 nof_publication_mode := publication_mode.COUNT;
2683
2684 IF ( (nof_inventory_item_id <> nof_organization_id) OR
2685 (nof_inventory_item_id <> nof_config_lookup_date) OR
2686 (nof_inventory_item_id <> nof_calling_application_id) OR
2687 (nof_inventory_item_id <> nof_usage_name) OR
2688 (nof_inventory_item_id <> nof_language) OR
2689 (nof_inventory_item_id <> nof_publication_mode) ) THEN
2690 RAISE WRONG_ARRAYS_LENGTH;
2691 END IF;
2692
2693 t_models_for_items.extend(nof_inventory_item_id);
2694
2695 FOR i IN 1..nof_inventory_item_id LOOP
2696 t_models_for_items(i) := config_model_for_item(inventory_item_id(i), organization_id(i),
2697 config_lookup_date(i), calling_application_id(i),
2698 usage_name(i), publication_mode(i), language(i));
2699 END LOOP;
2700 RETURN t_models_for_items;
2701 EXCEPTION
2702 WHEN WRONG_ARRAYS_LENGTH THEN
2703 -- xERROR:=CZ_UTILS.REPORT('The size of input arrays should be the same',1,'CZ_CF_API.CONFIG_MODELS_FOR_ITEMS',11222);
2704 cz_utils.log_report('CZ_CF_API', 'config_models_for_items', null,
2705 'The size of input arrays should be the same',
2706 fnd_log.LEVEL_EXCEPTION);
2707 RAISE_APPLICATION_ERROR (-20001,
2708 'The size of input arrays should be the same');
2709 WHEN OTHERS THEN
2710 -- xERROR:=CZ_UTILS.REPORT(SQLERRM,1,'CZ_CF_API.CONFIG_MODELS_FOR_ITEMS',11222);
2711 cz_utils.log_report('CZ_CF_API', 'config_models_for_items', null, SQLERRM,
2712 fnd_log.LEVEL_UNEXPECTED);
2713 END config_models_for_items;
2714
2715 ----------------------------------------------------------------------------------------
2719 --
2716 -- Returns the ui (ui_def_id and ui type) specified by input publication_id.
2717 -- If input publication_id is null, inventory_item_id and organization_id will be used
2718 -- to decide whether to return the seeded native bom ui or not.
2720 FUNCTION config_ui_for_item_pvt(p_publication_id IN NUMBER
2721 ,px_ui_type IN OUT NOCOPY VARCHAR2
2722 ,p_inventory_item_id IN NUMBER
2723 ,p_organization_id IN NUMBER
2724 )
2725 RETURN NUMBER
2726 IS
2727 l_ui_style CZ_MODEL_PUBLICATIONS.ui_style%TYPE;
2728 l_pub_ui_style CZ_MODEL_PUBLICATIONS.ui_style%TYPE;
2729 l_dummy INTEGER;
2730
2731 BEGIN
2732 IF p_publication_id IS NULL THEN
2733 IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
2734 BEGIN
2735 SELECT 1 INTO l_dummy
2736 FROM mtl_system_items
2737 WHERE inventory_item_id = p_inventory_item_id
2738 AND organization_id = p_organization_id
2739 AND bom_item_type = BOM_ITEM_TYPE_MODEL;
2740 EXCEPTION
2741 WHEN NO_DATA_FOUND THEN
2742 px_ui_type := NULL;
2743 RETURN NULL;
2744 END;
2745
2746 px_ui_type := NATIVEBOM_UI_TYPE;
2747 RETURN NATIVEBOM_UI_DEF;
2748 ELSE
2749 px_ui_type := NULL;
2750 RETURN NULL;
2751 END IF;
2752
2753 ELSE
2754 SELECT ui_style INTO l_pub_ui_style
2755 FROM CZ_MODEL_PUBLICATIONS
2756 WHERE publication_id = p_publication_id;
2757
2758 IF px_ui_type IS NOT NULL THEN
2759 -- If input ui_type is APPLET, either APPLET or DHTML or JRAD is OK.
2760 -- If input ui_type is JRAD or DHTML, the UI associated with the publication
2761 -- MUST be either JRAD, or DHTML, or WEGA. If not, return NULL.
2762 l_ui_style := ui_style_from_ui_type(px_ui_type);
2763 IF l_ui_style IS NULL OR l_ui_style<>UI_STYLE_APPLET AND l_pub_ui_style=UI_STYLE_APPLET THEN
2764 px_ui_type := NULL;
2765 RETURN NULL;
2766 END IF;
2767 END IF;
2768
2769 px_ui_type := ui_type_from_ui_style(l_pub_ui_style);
2770 RETURN ui_for_publication_id(p_publication_id);
2771 END IF;
2772 END config_ui_for_item_pvt;
2773
2774 --------------------------------------------------------------------------------------------
2775 FUNCTION ui_for_item(inventory_item_id NUMBER,
2776 organization_id NUMBER,
2777 config_creation_date DATE,
2778 ui_type VARCHAR2,
2779 user_id NUMBER,
2780 responsibility_id NUMBER,
2781 calling_application_id NUMBER
2782 )
2783 RETURN NUMBER
2784 IS
2785 v_ui_type VARCHAR2(30) := ui_type;
2786 l_return_ui_def_id cz_ui_defs.ui_def_id%TYPE;
2787 l_return_ui_profile VARCHAR2(3);
2788 BEGIN
2789 l_return_ui_def_id := config_ui_for_item(inventory_item_id, organization_id,
2790 config_creation_date, v_ui_type,
2791 calling_application_id, NULL);
2792 IF ( (l_return_ui_def_id IS NOT NULL)
2793 AND (l_return_ui_def_id = NATIVEBOM_UI_DEF) ) THEN
2794 l_return_ui_profile := FND_PROFILE.value('CZGENERICBOMUIPROFILE');
2795 IF (UPPER(l_return_ui_profile) IN ('N', 'NO') ) THEN
2796 l_return_ui_def_id := NULL;
2797 END IF;
2798 END IF;
2799 RETURN l_return_ui_def_id;
2800 END ui_for_item;
2801 ------------------------------------------------------------------------------
2802
2803 FUNCTION config_ui_for_item(inventory_item_id IN NUMBER,
2804 organization_id IN NUMBER,
2805 config_lookup_date IN DATE,
2806 ui_type IN OUT NOCOPY VARCHAR2,
2807 calling_application_id IN NUMBER,
2808 usage_name IN VARCHAR2,
2809 publication_mode IN VARCHAR2 DEFAULT NULL,
2810 language IN VARCHAR2 DEFAULT NULL
2811 )
2812 RETURN NUMBER
2813 IS
2814 l_publication_id NUMBER;
2815
2816 BEGIN
2817 l_publication_id := publication_for_item(inventory_item_id,
2818 organization_id,
2819 config_lookup_date,
2820 calling_application_id,
2821 usage_name,
2822 publication_mode,
2823 language);
2824 RETURN config_ui_for_item_pvt(l_publication_id, ui_type, inventory_item_id,
2825 organization_id);
2826 END config_ui_for_item;
2827
2828 ---------------------------------------------------------------------------
2829
2830 FUNCTION config_ui_for_item_lf (inventory_item_id IN NUMBER,
2831 organization_id IN NUMBER,
2832 config_lookup_date IN DATE,
2833 ui_type IN OUT NOCOPY VARCHAR2,
2834 calling_application_id IN NUMBER,
2835 usage_name IN VARCHAR2,
2836 look_and_feel OUT NOCOPY VARCHAR2,
2837 publication_mode IN VARCHAR2 DEFAULT NULL,
2838 language IN VARCHAR2 DEFAULT NULL
2839 )
2840 RETURN NUMBER
2841 IS
2842
2843 v_ui_def_id NUMBER;
2844
2845 BEGIN
2846 v_ui_def_id := config_ui_for_item(inventory_item_id, organization_id, config_lookup_date,
2847 ui_type, calling_application_id, usage_name, publication_mode, language);
2848
2852 SELECT look_and_feel INTO look_and_feel FROM cz_ui_defs WHERE ui_def_id = v_ui_def_id;
2849 IF v_ui_def_id IS NULL THEN
2850 look_and_feel := NULL;
2851 ELSE
2853 END IF;
2854 RETURN v_ui_def_id;
2855
2856 END config_ui_for_item_lf;
2857
2858 ---------------------------------------------------------------------------
2859
2860 FUNCTION config_uis_for_items (inventory_item_id IN NUMBER_TBL_TYPE,
2861 organization_id IN NUMBER_TBL_TYPE,
2862 config_lookup_date IN DATE_TBL_TYPE,
2863 ui_type IN OUT NOCOPY VARCHAR2_TBL_TYPE,
2864 calling_application_id IN NUMBER_TBL_TYPE,
2865 usage_name IN VARCHAR2_TBL_TYPE,
2866 publication_mode IN VARCHAR2_TBL_TYPE,
2867 language IN VARCHAR2_TBL_TYPE
2868 )
2869 RETURN NUMBER_TBL_TYPE
2870 IS
2871 t_uis_for_items NUMBER_TBL_TYPE := NUMBER_TBL_TYPE();
2872
2873 nof_inventory_item_id NUMBER;
2874 nof_organization_id NUMBER;
2875 nof_config_lookup_date NUMBER;
2876 nof_ui_type NUMBER;
2877 nof_calling_application_id NUMBER;
2878 nof_usage_name NUMBER;
2879 nof_language NUMBER;
2880 nof_publication_mode NUMBER;
2881
2882 BEGIN
2883
2884 nof_inventory_item_id := inventory_item_id.COUNT;
2885 nof_organization_id := organization_id.COUNT;
2886 nof_config_lookup_date := config_lookup_date.COUNT;
2887 nof_ui_type := ui_type.COUNT;
2888 nof_calling_application_id := calling_application_id.COUNT;
2889 nof_usage_name := usage_name.COUNT;
2890 nof_language := language.COUNT;
2891 nof_publication_mode := publication_mode.COUNT;
2892
2893 IF ( (nof_inventory_item_id <> nof_organization_id) OR
2894 (nof_inventory_item_id <> nof_config_lookup_date) OR
2895 (nof_inventory_item_id <> nof_ui_type) OR
2896 (nof_inventory_item_id <> nof_calling_application_id) OR
2897 (nof_inventory_item_id <> nof_usage_name) OR
2898 (nof_inventory_item_id <> nof_language) OR
2899 (nof_inventory_item_id <> nof_publication_mode) ) THEN
2900 RAISE WRONG_ARRAYS_LENGTH;
2901 END IF;
2902 t_uis_for_items.extend(nof_inventory_item_id);
2903 FOR i IN 1..nof_inventory_item_id LOOP
2904 t_uis_for_items(i) := config_ui_for_item(inventory_item_id(i), organization_id(i),
2905 config_lookup_date(i), ui_type(i), calling_application_id(i),
2906 usage_name(i), publication_mode(i), language(i));
2907 END LOOP;
2908 RETURN t_uis_for_items;
2909
2910 EXCEPTION
2911 WHEN WRONG_ARRAYS_LENGTH THEN
2912 -- xERROR:=CZ_UTILS.REPORT('The size of input arrays should be the same',1,'CZ_CF_API.CONFIG_UIS_FOR_ITEMS',11222);
2913 cz_utils.log_report('CZ_CF_API', 'config_uis_for_items', null,
2914 'The size of input arrays should be the same',
2915 fnd_log.LEVEL_EXCEPTION);
2916 RAISE_APPLICATION_ERROR (-20001,
2917 'The size of input arrays should be the same');
2918 WHEN OTHERS THEN
2919 -- xERROR:=CZ_UTILS.REPORT(SQLERRM,1,'CZ_CF_API.CONFIG_UIS_FOR_ITEMS',11222);
2920 cz_utils.log_report('CZ_CF_API', 'config_uis_for_items', null,
2921 SQLERRM, fnd_log.LEVEL_UNEXPECTED);
2922 END config_uis_for_items;
2923
2924 ---------------------------------------------------------------------------
2925 FUNCTION model_for_publication_id (publication_id NUMBER)
2926 RETURN NUMBER
2927 IS
2928
2929 v_publication_id NUMBER := publication_id;
2930 v_model_id NUMBER ;
2931
2932 BEGIN
2933 SELECT model_id
2934 INTO v_model_id
2935 FROM CZ_MODEL_PUBLICATIONS
2936 WHERE CZ_MODEL_PUBLICATIONS.publication_id = v_publication_id
2937 AND CZ_MODEL_PUBLICATIONS.export_status = 'OK'
2938 AND CZ_MODEL_PUBLICATIONS.deleted_flag = '0';
2939
2940 IF v_model_id IS NULL THEN
2941 RETURN NULL;
2942 ELSE
2943 RETURN v_model_id;
2944 END IF;
2945
2946 END;
2947 --------------------------------------------------------------------------------------------------------------
2948
2949 FUNCTION ui_for_publication_id (publication_id NUMBER)
2950 RETURN NUMBER
2951 IS
2952
2953 v_publication_id NUMBER := publication_id;
2954 v_ui_def_id NUMBER ;
2955
2956 BEGIN
2957 SELECT ui_def_id
2958 INTO v_ui_def_id
2959 FROM CZ_MODEL_PUBLICATIONS
2960 WHERE CZ_MODEL_PUBLICATIONS.publication_id = v_publication_id
2961 AND CZ_MODEL_PUBLICATIONS.export_status = 'OK'
2962 AND CZ_MODEL_PUBLICATIONS.deleted_flag = '0';
2963
2964 IF v_ui_def_id IS NULL THEN
2965 RETURN NULL;
2966 ELSE
2967 RETURN v_ui_def_id;
2968 END IF;
2969 END;
2970
2971 ----------------------------------------------------------------------------------------------------------
2972 FUNCTION config_model_for_product ( product_key IN VARCHAR2,
2973 config_lookup_date IN DATE,
2974 calling_application_id IN NUMBER,
2975 usage_name IN VARCHAR2,
2976 publication_mode IN VARCHAR2 DEFAULT NULL,
2977 language IN VARCHAR2 DEFAULT NULL
2978 )
2979 RETURN NUMBER
2980 IS
2981
2982 v_publication_id NUMBER;
2983
2984 BEGIN
2985
2986 v_publication_id := publication_for_product(product_key,
2987 config_lookup_date,
2988 calling_application_id,
2989 usage_name,
2993 IF v_publication_id IS NULL THEN
2990 publication_mode,
2991 language);
2992
2994 RETURN NULL;
2995 ELSE
2996 RETURN model_for_publication_id(v_publication_id);
2997 END IF;
2998
2999 END config_model_for_product;
3000
3001 --------------------------------------------------------------------------------------------
3002 FUNCTION config_models_for_products ( product_key IN VARCHAR2_TBL_TYPE,
3003 config_lookup_date IN DATE_TBL_TYPE,
3004 calling_application_id IN NUMBER_TBL_TYPE,
3005 usage_name IN VARCHAR2_TBL_TYPE,
3006 publication_mode IN VARCHAR2_TBL_TYPE,
3007 language IN VARCHAR2_TBL_TYPE
3008 )
3009 RETURN NUMBER_TBL_TYPE
3010 IS
3011
3012 t_models_for_products NUMBER_TBL_TYPE := NUMBER_TBL_TYPE();
3013
3014 nof_product_key NUMBER;
3015 nof_config_lookup_date NUMBER;
3016 nof_calling_application_id NUMBER;
3017 nof_usage_name NUMBER;
3018 nof_language NUMBER;
3019 nof_publication_mode NUMBER;
3020
3021 BEGIN
3022
3023 nof_product_key := product_key.COUNT;
3024 nof_config_lookup_date := config_lookup_date.COUNT;
3025 nof_calling_application_id := calling_application_id.COUNT;
3026 nof_usage_name := usage_name.COUNT;
3027 nof_language := language.COUNT;
3028 nof_publication_mode := publication_mode.COUNT;
3029
3030 IF ( (nof_product_key <> nof_config_lookup_date) OR
3031 (nof_product_key <> nof_calling_application_id) OR
3032 (nof_product_key <> nof_usage_name) OR
3033 (nof_product_key <> nof_language) OR
3034 (nof_product_key <> nof_publication_mode) ) THEN
3035 RAISE WRONG_ARRAYS_LENGTH;
3036 END IF;
3037
3038 t_models_for_products.extend(nof_product_key);
3039
3040 FOR i IN 1..nof_product_key LOOP
3041 t_models_for_products(i) := config_model_for_product(product_key(i), config_lookup_date(i), calling_application_id(i), usage_name(i), publication_mode(i), language(i));
3042 END LOOP;
3043 RETURN t_models_for_products;
3044 EXCEPTION
3045 WHEN WRONG_ARRAYS_LENGTH THEN
3046 -- xERROR:=CZ_UTILS.REPORT('The size of input arrays should be the same',1,'CZ_CF_API.CONFIG_MODELS_FOR_PRODUCTS',11222);
3047 cz_utils.log_report('CZ_CF_API', 'config_models_for_products', null,
3048 'The size of input arrays should be the same', fnd_log.LEVEL_EXCEPTION);
3049 RAISE_APPLICATION_ERROR (-20001,
3050 'The size of input arrays should be the same');
3051 WHEN OTHERS THEN
3052 -- xERROR:=CZ_UTILS.REPORT(SQLERRM,1,'CZ_CF_API.CONFIG_MODELS_FOR_PRODUCTS',11222);
3053 cz_utils.log_report('CZ_CF_API', 'config_models_for_products', null,
3054 SQLERRM, fnd_log.LEVEL_UNEXPECTED);
3055 END config_models_for_products;
3056
3057 -----------------------------------------------------------------------------------
3058
3059 FUNCTION config_ui_for_product(product_key IN VARCHAR2,
3060 config_lookup_date IN DATE,
3061 ui_type IN OUT NOCOPY VARCHAR2,
3062 calling_application_id IN NUMBER,
3063 usage_name IN VARCHAR2,
3064 publication_mode IN VARCHAR2 DEFAULT NULL,
3065 language IN VARCHAR2 DEFAULT NULL
3066 )
3067 RETURN NUMBER
3068 IS
3069 l_publication_id NUMBER;
3070
3071 l_inventory_item_id NUMBER := NULL;
3072 l_organization_id NUMBER := NULL;
3073 l_colon_pos INTEGER;
3074
3075 BEGIN
3076 l_publication_id := publication_for_product(product_key,
3077 config_lookup_date,
3078 calling_application_id,
3079 usage_name,
3080 publication_mode,
3081 language);
3082
3083 IF l_publication_id IS NULL THEN
3084 l_colon_pos := instr(product_key, ':');
3085 IF l_colon_pos > 0 THEN
3086 l_organization_id := cz_utils.conv_num(substr(product_key, 1, l_colon_pos-1));
3087 l_inventory_item_id := cz_utils.conv_num(substr(product_key, l_colon_pos+1));
3088 END IF;
3089
3090 IF l_organization_id IS NULL OR l_inventory_item_id IS NULL THEN
3091 ui_type := NULL;
3092 RETURN NULL;
3093 END IF;
3094 END IF;
3095
3096 RETURN config_ui_for_item_pvt(l_publication_id, ui_type, l_inventory_item_id,
3097 l_organization_id);
3098 END config_ui_for_product;
3099
3100 --------------------------------------------------------------------------------------------
3101
3102 FUNCTION config_uis_for_products (product_key IN VARCHAR2_TBL_TYPE,
3103 config_lookup_date IN DATE_TBL_TYPE,
3104 ui_type IN OUT NOCOPY VARCHAR2_TBL_TYPE,
3105 calling_application_id IN NUMBER_TBL_TYPE,
3106 usage_name IN VARCHAR2_TBL_TYPE,
3107 publication_mode IN VARCHAR2_TBL_TYPE,
3108 language IN VARCHAR2_TBL_TYPE
3109 )
3110 RETURN NUMBER_TBL_TYPE
3111 IS
3112
3113 t_uis_for_products NUMBER_TBL_TYPE := NUMBER_TBL_TYPE();
3114
3115 nof_product_key NUMBER;
3116 nof_config_lookup_date NUMBER;
3117 nof_ui_type NUMBER;
3118 nof_calling_application_id NUMBER;
3119 nof_usage_name NUMBER;
3120 nof_language NUMBER;
3121 nof_publication_mode NUMBER;
3125 nof_product_key := product_key.COUNT;
3122
3123 BEGIN
3124
3126 nof_config_lookup_date := config_lookup_date.COUNT;
3127 nof_ui_type := ui_type.COUNT;
3128 nof_calling_application_id := calling_application_id.COUNT;
3129 nof_usage_name := usage_name.COUNT;
3130 nof_language := language.COUNT;
3131 nof_publication_mode := publication_mode.COUNT;
3132
3133 IF ( (nof_product_key <> nof_config_lookup_date) OR
3134 (nof_product_key <> nof_ui_type) OR
3135 (nof_product_key <> nof_calling_application_id) OR
3136 (nof_product_key <> nof_usage_name) OR
3137 (nof_product_key <> nof_language) OR
3138 (nof_product_key <> nof_publication_mode) ) THEN
3139 RAISE WRONG_ARRAYS_LENGTH;
3140 END IF;
3141
3142 t_uis_for_products.extend(nof_product_key);
3143
3144 FOR i IN 1..nof_product_key LOOP
3145 t_uis_for_products(i) := config_ui_for_product(product_key(i), config_lookup_date(i),
3146 ui_type(i), calling_application_id(i), usage_name(i), publication_mode(i), language(i));
3147 END LOOP;
3148 RETURN t_uis_for_products;
3149 EXCEPTION
3150 WHEN WRONG_ARRAYS_LENGTH THEN
3151 -- xERROR:=CZ_UTILS.REPORT('The size of input arrays should be the same',1,'CZ_CF_API.CONFIG_UIS_FOR_PRODUCTS',11222);
3152 cz_utils.log_report('CZ_CF_API', 'config_uis_for_products', null,
3153 'The size of input arrays should be the same', fnd_log.LEVEL_EXCEPTION);
3154 RAISE_APPLICATION_ERROR (-20001,
3155 'The size of input arrays should be the same');
3156 WHEN OTHERS THEN
3157 -- xERROR:=CZ_UTILS.REPORT(SQLERRM,1,'CZ_CF_API.CONFIG_UIS_FOR_PRODUCTS',11222);
3158 cz_utils.log_report('CZ_CF_API', 'config_uis_for_products', null,
3159 SQLERRM, fnd_log.LEVEL_UNEXPECTED);
3160 END config_uis_for_products;
3161
3162 -----------------------------------------------------------------------------------
3163
3164 FUNCTION publication_for_item ( inventory_item_id IN NUMBER,
3165 organization_id IN NUMBER,
3166 config_lookup_date IN DATE,
3167 calling_application_id IN NUMBER,
3168 usage_name IN VARCHAR2,
3169 publication_mode IN VARCHAR2 DEFAULT NULL,
3170 language IN VARCHAR2 DEFAULT NULL
3171 )
3172 RETURN NUMBER
3173 IS
3174
3175 v_inventory_item_id NUMBER := inventory_item_id ;
3176 v_organization_id NUMBER := organization_id ;
3177 v_config_lookup_date DATE := config_lookup_date ;
3178 v_usage_id NUMBER ;
3179 v_language VARCHAR2(4) := language;
3180 v_publication_mode VARCHAR2(1) := publication_mode ;
3181 v_usage_name VARCHAR2(255) := usage_name ;
3182 v_application_id NUMBER := calling_application_id;
3183 v_source_target_flag VARCHAR2(3) := 'T' ;
3184 v_publication_id NUMBER ;
3185 v_pb_count NUMBER ;
3186 c_inventory_item_id NUMBER ;
3187 c_organization_id NUMBER;
3188
3189 CURSOR pub_cur IS
3190 SELECT publication_id
3191 FROM cz_model_applicabilities_v
3192 WHERE inventory_item_id = v_inventory_item_id
3193 AND bom_explosion_org_id = v_organization_id
3194 AND UPPER(publication_mode) = LTRIM(RTRIM(UPPER(v_publication_mode)))
3195 AND fnd_application_id = v_application_id
3196 AND usage_id = v_usage_id
3197 AND Source_Target_Flag = v_source_target_flag
3198 AND deleted_flag = '0'
3199 AND language = v_language
3200 AND (start_date <= v_config_lookup_date)
3201 AND (v_config_lookup_date < disable_date)
3202 ORDER BY start_date DESC;
3203
3204 CURSOR no_appl_pub_cur IS
3205 SELECT publication_id
3206 FROM cz_model_applicabilities_v
3207 WHERE inventory_item_id = v_inventory_item_id
3208 AND bom_explosion_org_id = v_organization_id
3209 AND UPPER(publication_mode) = LTRIM(RTRIM(UPPER(v_publication_mode)))
3210 AND usage_id = v_usage_id
3211 AND Source_Target_Flag = v_source_target_flag
3212 AND deleted_flag = '0'
3213 AND language = v_language
3214 AND start_date <= v_config_lookup_date
3215 AND v_config_lookup_date < disable_date
3216 ORDER BY start_date DESC;
3217
3218 BEGIN
3219
3220 -- Required because Istore passes FND_API.G_MISS_DATE
3221 IF v_config_lookup_date = FND_API.G_MISS_DATE THEN
3222 v_config_lookup_date := SYSDATE;
3223 END IF;
3224
3225 IF v_language IS NULL THEN
3226 SELECT userenv('LANG') INTO v_language FROM dual;
3227 END IF;
3228
3229 -- check usage_name: if null, get the profile option value from db
3230 IF v_usage_name IS NULL THEN
3231 fnd_profile.get('CZ_PUBLICATION_USAGE', v_usage_name);
3232 END IF;
3233
3234 v_usage_id := usage_id_from_usage_name(v_usage_name);
3235
3236 -- Keep these values to lookup common bill, if required
3237 if (c_application_id is null) then
3238 c_application_id := calling_application_id;
3239 end if;
3240 if (c_usage_name is null) then
3241 c_usage_name := v_usage_name;
3242 end if;
3243
3244 -- check publication mode: if null, get the profile option value from db.
3245 -- if still null, use the default 'P'.
3246 IF v_publication_mode IS NULL THEN
3247 fnd_profile.get('CZ_PUBLICATION_MODE', v_publication_mode);
3248 IF v_publication_mode IS NULL THEN
3249 v_publication_mode := 'P';
3253 v_pb_count := 0;
3250 END IF;
3251 END IF;
3252
3254 IF v_application_id IS NULL THEN
3255 OPEN no_appl_pub_cur;
3256 LOOP
3257 EXIT WHEN (no_appl_pub_cur%NOTFOUND OR v_pb_count > 0);
3258 FETCH no_appl_pub_cur INTO v_publication_id;
3259 v_pb_count := v_pb_count + 1;
3260 END LOOP;
3261 CLOSE no_appl_pub_cur;
3262 ELSE
3263 OPEN pub_cur;
3264 LOOP
3265 EXIT WHEN ( (pub_cur%NOTFOUND) OR (v_pb_count > 0) ) ;
3266 FETCH pub_cur INTO v_publication_id;
3267 v_pb_count := v_pb_count + 1;
3268 END LOOP;
3269 CLOSE pub_cur;
3270 END IF;
3271
3272 -- if not found check for "any usage" and/or "any application" publication
3273 -- or "common bill"
3274 IF v_publication_id IS NULL THEN
3275 IF v_usage_id <> ANY_USAGE_ID THEN
3276
3277 -- passing NULL is NOT the same as passing "any usage"
3278 RETURN publication_for_item(v_inventory_item_id, v_organization_id,
3279 v_config_lookup_date, v_application_id,
3280 ANY_USAGE_NAME, v_publication_mode, v_language);
3281 ELSIF v_application_id IS NOT NULL AND v_application_id <> ANY_APPLICATION_ID THEN
3282 RETURN publication_for_item(v_inventory_item_id, v_organization_id,
3283 v_config_lookup_date, ANY_APPLICATION_ID,
3284 v_usage_name, v_publication_mode, v_language);
3285 ELSE
3286 BEGIN
3287 -- else get publication id of the common bill, if any
3288 common_bill_for_item(v_inventory_item_id, v_organization_id,
3289 c_inventory_item_id, c_organization_id);
3290 IF (((c_inventory_item_id is not null) and (c_organization_id is not null))
3291 and ((c_inventory_item_id <> v_inventory_item_id) or (c_organization_id <> v_organization_id))) THEN
3292 RETURN publication_for_item(c_inventory_item_id, c_organization_id,
3293 v_config_lookup_date, c_application_id,
3294 c_usage_name, v_publication_mode, v_language);
3295 ELSE
3296 c_application_id := null;
3297 c_usage_name := null;
3298 RETURN NULL;
3299 END IF;
3300 END;
3301 END IF;
3302 ELSE
3303 c_application_id := null;
3304 c_usage_name := null;
3305 RETURN v_publication_id;
3306 END IF;
3307 END;
3308
3309 -----------------------------------------------------------------------------
3310 -- Retrieves inventory_item_id and organization_id of root bom referenced
3311 -- by the non bom model specified by the input model_id
3312 -- private
3313 PROCEDURE find_root_bom_inv_org(p_model_id IN NUMBER
3314 ,x_inventory_item_id OUT NOCOPY NUMBER
3315 ,x_organization_id OUT NOCOPY NUMBER)
3316 IS
3317 CURSOR reference_cursor IS
3318 SELECT prj.inventory_item_id, prj.organization_id
3319 FROM cz_devl_projects prj, cz_model_ref_expls expl
3320 WHERE expl.model_id = p_model_id
3321 AND expl.ps_node_type = PS_NODE_TYPE_REFERENCE
3322 AND expl.deleted_flag = '0'
3323 AND prj.devl_project_id = expl.component_id
3324 AND prj.deleted_flag = '0'
3325 AND prj.inventory_item_id IS NOT NULL
3326 AND prj.organization_id IS NOT NULL
3327 ORDER BY expl.node_depth;
3328 BEGIN
3329 FOR ref_rec IN reference_cursor LOOP
3330 EXIT WHEN reference_cursor%rowcount > 1 OR reference_cursor%NOTFOUND;
3331 x_inventory_item_id := ref_rec.inventory_item_id;
3332 x_organization_id := ref_rec.organization_id;
3333 END LOOP;
3334
3335 END find_root_bom_inv_org;
3336
3337 -----------------------------------------------------------------------------
3338 FUNCTION publication_for_saved_config (config_hdr_id IN NUMBER,
3339 config_rev_nbr IN NUMBER,
3340 config_lookup_date IN DATE,
3341 calling_application_id IN NUMBER,
3342 usage_name IN VARCHAR2,
3343 publication_mode IN VARCHAR2 DEFAULT NULL,
3344 language IN VARCHAR2 DEFAULT NULL
3345 )
3346 RETURN NUMBER
3347 IS
3348 v_config_hdr_id NUMBER := config_hdr_id;
3349 v_config_rev_nbr NUMBER := config_rev_nbr;
3350 v_inventory_item_id NUMBER;
3351 v_organization_id NUMBER;
3352 v_product_key cz_devl_projects.product_key%TYPE;
3353 v_component_id NUMBER;
3354 v_model_identifier cz_config_hdrs.model_identifier%TYPE;
3355 v_ndebug NUMBER := 0;
3356
3357 BEGIN
3358 BEGIN
3359 SELECT component_id, model_identifier
3360 INTO v_component_id, v_model_identifier
3361 FROM CZ_CONFIG_HDRS
3362 WHERE config_hdr_id = v_config_hdr_id AND config_rev_nbr = v_config_rev_nbr;
3363 EXCEPTION
3364 WHEN NO_DATA_FOUND THEN
3365 RETURN NULL;
3366 END;
3367 v_ndebug := 1;
3368
3369 IF v_model_identifier IS NOT NULL THEN
3370 v_inventory_item_id := SUBSTR(v_model_identifier, 1, INSTR(v_model_identifier, ':')-1);
3371 v_organization_id := SUBSTR(v_model_identifier, INSTR(v_model_identifier, ':')+1,
3372 INSTR(v_model_identifier, ':', 1, 2) - (INSTR(v_model_identifier, ':')+1));
3373
3374 ELSE
3375 -- Old logic for finding orig_sys_ref is flawed: orig_sys_ref could be null but there
3376 -- is a publication. If the model is a non-BOM or a mixed model in which the root is non
3380 -- a. Look for model corresponding to cz_config_hdrs.component_id. If it exists, find
3377 -- BOM orig_sys_ref will be NULL. In the customer case of bug 2475218, a BOM reference
3378 -- was added to a copy of the original non-bom model. The fix for bug 2475218 was:
3379 -- New logic if the root ps node orig_sys_ref lookup fails:
3381 -- its BOM model.
3382 -- b. If a. fails, iterate over all projects that have persistent_project_id equal to
3383 -- the config's persistent_component_id. Iterate until a root BOM model is found.
3384
3385 -- The way using orig_sys_ref is still flawed: a not null orig_sys_ref might not be
3386 -- used to get inv and org for publication lookup. For example, the generic imported
3387 -- contract model in bug 3189078 has orig_sys_ref of 510:TEMPLATEMODELTOPNODE:202:B:4352
3388 -- on the root but inv and org cannot be retrieved from it for pub lookup.
3389 -- The fix for 3189078: using product_key/inv/org info (added to project table in 21)
3390 -- instead of using cz_ps_nodes.orig_sys_ref. The basic logic is still the same except
3391 -- if found a product_key on a root project, we will lookup pub by the key, which
3392 -- depends on the fact that product_key is carried over from source model to published
3393 -- model during publishing.
3394 -- Future? could just use one query by persistent_component_id (modify prj_cursor)
3395 -- do the whole lookup
3396
3397 v_ndebug := 2;
3398 BEGIN
3399 SELECT inventory_item_id, organization_id, product_key
3400 INTO v_inventory_item_id, v_organization_id, v_product_key
3401 FROM CZ_DEVL_PROJECTS
3402 WHERE deleted_flag = '0' AND devl_project_id = v_component_id;
3403 EXCEPTION
3404 WHEN no_data_found THEN
3405 v_ndebug := 3;
3406 BEGIN
3407 SELECT inventory_item_id, organization_id
3408 INTO v_inventory_item_id, v_organization_id
3409 FROM cz_config_items
3410 WHERE config_hdr_id = v_config_hdr_id AND config_rev_nbr = v_config_rev_nbr
3411 AND deleted_flag = '0' AND inventory_item_id IS NOT NULL
3412 AND to_char(inventory_item_id) = node_identifier;
3413 EXCEPTION
3414 WHEN no_data_found THEN
3415 v_inventory_item_id := NULL;
3416 END;
3417 END;
3418 END IF;
3419
3420 IF v_product_key IS NOT NULL THEN
3421 RETURN publication_for_product(v_product_key
3422 ,config_lookup_date
3423 ,calling_application_id
3424 ,usage_name
3425 ,publication_mode
3426 ,language
3427 );
3428
3429 ELSIF v_inventory_item_id IS NOT NULL THEN
3430 RETURN publication_for_item(v_inventory_item_id
3431 ,v_organization_id
3432 ,config_lookup_date
3433 ,calling_application_id
3434 ,usage_name
3435 ,publication_mode
3436 ,language
3437 );
3438 ELSE
3439 RETURN NULL;
3440 END IF;
3441
3442 EXCEPTION
3443 WHEN OTHERS THEN
3444 cz_utils.log_report('CZ_CF_API', 'publication_for_saved_config', v_ndebug,
3445 SQLERRM, fnd_log.LEVEL_UNEXPECTED);
3446 RAISE;
3447 END publication_for_saved_config;
3448
3449 -----------------------------------------------------------------------------
3450
3451 FUNCTION publication_for_product (product_key IN VARCHAR2,
3452 config_lookup_date IN DATE,
3453 calling_application_id IN NUMBER,
3454 usage_name IN VARCHAR2,
3455 publication_mode IN VARCHAR2 DEFAULT NULL,
3456 language IN VARCHAR2 DEFAULT NULL
3457 )
3458 RETURN NUMBER
3459 IS
3460
3461 v_product_key cz_devl_projects.product_key%TYPE := product_key ;
3462 v_config_lookup_date DATE := config_lookup_date ;
3463 v_usage_id NUMBER ;
3464 v_publication_mode VARCHAR2(1) := publication_mode ;
3465 v_usage_name VARCHAR2(255) := usage_name ;
3466 v_language VARCHAR2(4) := language;
3467 v_application_id NUMBER := calling_application_id;
3468 v_source_target_flag VARCHAR2(3) := 'T' ;
3469 v_publication_id NUMBER ;
3470 v_pb_count NUMBER;
3471 c_product_key cz_devl_projects.product_key%TYPE;
3472
3473 CURSOR pub_cur IS
3474 SELECT publication_id
3475 FROM cz_model_applicabilities_v
3476 WHERE product_key = v_product_key
3477 AND UPPER(publication_mode) = LTRIM(RTRIM(UPPER(v_publication_mode)))
3478 AND fnd_application_id = v_application_id
3479 AND usage_id = v_usage_id
3480 AND Source_Target_Flag = v_source_target_flag
3481 AND deleted_flag = '0'
3482 AND language = v_language
3483 AND (start_date <= v_config_lookup_date)
3484 AND (v_config_lookup_date < disable_date)
3485 ORDER BY start_date DESC;
3486 CURSOR no_appl_pub_cur IS
3487 SELECT publication_id
3488 FROM cz_model_applicabilities_v
3489 WHERE product_key = v_product_key
3490 AND UPPER(publication_mode) = LTRIM(RTRIM(UPPER(v_publication_mode)))
3491 AND usage_id = v_usage_id
3492 AND Source_Target_Flag = v_source_target_flag
3493 AND deleted_flag = '0'
3494 AND language = v_language
3498
3495 AND (start_date <= v_config_lookup_date)
3496 AND (v_config_lookup_date < disable_date)
3497 ORDER BY start_date DESC;
3499 BEGIN
3500
3501 -- Required because Istore passes FND_API.G_MISS_DATE
3502 IF v_config_lookup_date = FND_API.G_MISS_DATE THEN
3503 v_config_lookup_date := SYSDATE;
3504 END IF;
3505
3506 IF v_language IS NULL THEN
3507 SELECT userenv('LANG') INTO v_language FROM dual;
3508 END IF;
3509
3510 -- check usage_name: if null, get the profile option value from db
3511 IF v_usage_name IS NULL THEN
3512 fnd_profile.get('CZ_PUBLICATION_USAGE', v_usage_name);
3513 END IF;
3514
3515 v_usage_id := usage_id_from_usage_name(v_usage_name);
3516
3517 -- Keep these values to lookup common bill, if required
3518 if (c_application_id is null) then
3519 c_application_id := calling_application_id;
3520 end if;
3521 if (c_usage_name is null) then
3522 c_usage_name := v_usage_name;
3523 end if;
3524
3525 -- check publication mode: if null, get the profile option value from db.
3526 -- if still null, use the default 'P'.
3527 IF v_publication_mode IS NULL THEN
3528 fnd_profile.get('CZ_PUBLICATION_MODE', v_publication_mode);
3529 IF v_publication_mode IS NULL THEN
3530 v_publication_mode := 'P';
3531 END IF;
3532 END IF;
3533
3534 -- Bug 5103620- The count was not being
3535 -- initialized, and therefore restoring from saved configs
3536 -- returns the wrong publication record.
3537
3538 v_pb_count := 0;
3539 IF v_application_id IS NULL THEN
3540 OPEN no_appl_pub_cur;
3541 LOOP
3542 EXIT WHEN (no_appl_pub_cur%NOTFOUND OR v_pb_count > 0);
3543 FETCH no_appl_pub_cur INTO v_publication_id;
3544 v_pb_count := v_pb_count + 1;
3545 END LOOP;
3546 CLOSE no_appl_pub_cur;
3547 ELSE
3548 OPEN pub_cur;
3549 LOOP
3550 EXIT WHEN ( (pub_cur%NOTFOUND) OR (v_pb_count > 0) ) ;
3551 FETCH pub_cur INTO v_publication_id;
3552 v_pb_count := v_pb_count + 1;
3553 END LOOP;
3554 CLOSE pub_cur;
3555 END IF;
3556
3557 -- if not found check for "any usage" and/or "any application"
3558 -- publications or common bill
3559 IF v_publication_id IS NULL THEN
3560 IF v_usage_id <> ANY_USAGE_ID THEN
3561 -- passing NULL is NOT the same as passing "any usage"
3562 RETURN publication_for_product(v_product_key, v_config_lookup_date,
3563 v_application_id, ANY_USAGE_NAME,
3564 v_publication_mode, v_language);
3565 ELSIF v_application_id IS NOT NULL AND v_application_id <> ANY_APPLICATION_ID THEN
3566 RETURN publication_for_product(v_product_key, v_config_lookup_date,
3567 ANY_APPLICATION_ID,
3568 v_usage_name, v_publication_mode, v_language);
3569 ELSE
3570 common_bill_for_product(v_product_key, c_product_key);
3571 IF ((c_product_key is not null) and (c_product_key <> v_product_key)) THEN
3572 RETURN publication_for_product(c_product_key,v_config_lookup_date, c_application_id,
3573 c_usage_name, v_publication_mode, v_language);
3574 ELSE
3575 c_application_id := null;
3576 c_usage_name := null;
3577 RETURN NULL;
3578 END IF;
3579 END IF;
3580 ELSE
3581 c_application_id := null;
3582 c_usage_name := null;
3583 RETURN v_publication_id;
3584 END IF;
3585 END;
3586
3587 -------------------------------------------------------
3588
3589 PROCEDURE DEFAULT_NEW_CFG_DATES(p_creation_date IN OUT NOCOPY DATE,
3590 p_lookup_date IN OUT NOCOPY DATE,
3591 p_effective_date IN OUT NOCOPY DATE) IS
3592 BEGIN
3593 ----SELECT NVL(p_creation_date, SYSDATE) INTO p_creation_date FROM dual;
3594 IF (p_creation_date IS NULL) THEN
3595 p_creation_date := SYSDATE;
3596 END iF;
3597
3598 ----SELECT NVL(p_lookup_date, p_creation_date) INTO p_lookup_date FROM dual;
3599 IF (p_lookup_date IS NULL) THEN
3600 p_lookup_date := p_creation_date;
3601 END IF;
3602
3603 -----SELECT NVL(p_effective_date, p_creation_date) INTO p_effective_date FROM dual;
3604 IF (p_effective_date IS NULL) THEN
3605 p_effective_date := p_creation_date;
3606 END IF;
3607
3608 END DEFAULT_NEW_CFG_DATES;
3609
3610 -------------------------------------------------------
3611
3612 PROCEDURE DEFAULT_RESTORED_CFG_DATES(p_config_hdr_id IN NUMBER,
3613 p_config_rev_nbr IN NUMBER,
3614 p_creation_date IN OUT NOCOPY DATE,
3615 p_lookup_date IN OUT NOCOPY DATE,
3616 p_effective_date IN OUT NOCOPY DATE) IS
3617 l_config_creation_date DATE;
3618 l_config_effective_date DATE;
3619 l_rest_cfg_lookup_setting cz_db_settings.value%TYPE := ' ';
3620
3621 BEGIN
3622 IF p_config_hdr_id IS NULL OR p_config_rev_nbr IS NULL THEN
3623 RAISE_APPLICATION_ERROR(-20001, 'Config header ID AND config rev nbr ' ||
3624 'are required arguments TO CZ_CF_API.' ||
3625 'default_restored_cfg_dates');
3626 END IF;
3627
3628 BEGIN
3629 SELECT creation_date, effective_date INTO l_config_creation_date,
3630 l_config_effective_date FROM CZ_CONFIG_HDRS WHERE config_hdr_id =
3631 p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr;
3632 EXCEPTION
3633 WHEN NO_DATA_FOUND THEN
3634 -- just set l_config_creation_date and l_config_effective_date
3638 l_config_creation_date := NULL;
3635 -- to NULL, since it is not the role
3636 -- of this procedure to report missing configurations
3637 l_config_effective_date := NULL;
3639 END;
3640
3641 -- config creation date defaults to creation date of saved config
3642 SELECT NVL(p_creation_date, NVL(l_config_creation_date, SYSDATE)) INTO
3643 p_creation_date FROM dual;
3644
3645 --- get value for setting_id RestoredConfigDefaultModelLookupDate
3646 --- bug# 2406680 fix for Agilent
3647 --- Section_name for this should be ORAAPPS_INTEGRATE. Not adding this condition since it could break
3648 --- at a customer site.
3649 BEGIN
3650 SELECT value
3651 INTO l_rest_cfg_lookup_setting
3652 FROM cz_db_settings WHERE setting_id = 'RestoredConfigDefaultModelLookupDate';
3653 EXCEPTION
3654 WHEN OTHERS THEN
3655 l_rest_cfg_lookup_setting := NULL;
3656 END;
3657
3658 ---if l_rest_cfg_lookup_setting is set and p_lookup_date is NULL
3659 --- then use config creation date else use sysdate
3660 -- lookup date defaults to sysdate
3661 IF ( ( UPPER(LTRIM(RTRIM(l_rest_cfg_lookup_setting))) = UPPER('config_creation_date') )
3662 AND (p_lookup_date IS NULL) ) THEN
3663 p_lookup_date := p_creation_date ;
3664 ELSE
3665 SELECT NVL(p_lookup_date, SYSDATE) INTO p_lookup_date FROM dual;
3666 END IF;
3667 -- effective date defaults to effective date of saved config
3668 SELECT NVL(p_effective_date, NVL(l_config_effective_date, SYSDATE)) INTO
3669 p_effective_date FROM dual;
3670 END DEFAULT_RESTORED_CFG_DATES;
3671
3672 -------------------------------------------------
3673 FUNCTION icx_session_ticket RETURN VARCHAR2 IS
3674 PRAGMA AUTONOMOUS_TRANSACTION;
3675 l_user_id NUMBER;
3676 l_resp_id NUMBER;
3677 l_resp_appl_id NUMBER;
3678 l_session_id NUMBER;
3679 l_icx_exc EXCEPTION;
3680 BEGIN
3681 l_user_id := fnd_profile.value('USER_ID');
3682 l_resp_id := fnd_profile.value('RESP_ID');
3683 l_resp_appl_id := fnd_profile.value('RESP_APPL_ID');
3684 l_session_id := FND_SESSION_MANAGEMENT.g_session_id;
3685 IF (l_session_id = -1) THEN
3686 IF l_user_id IS NULL OR l_resp_id IS NULL OR l_resp_appl_id IS NULL THEN
3687 RAISE l_icx_exc;
3688 END IF;
3689 l_session_id := fnd_session_management.createsession(l_user_id);
3690 END IF;
3691
3692 IF l_session_id = -1 THEN
3693 RAISE l_icx_exc;
3694 ELSE
3695 COMMIT;
3696 END IF;
3697 RETURN icx_call.encrypt3(l_session_id);
3698 EXCEPTION
3699 WHEN l_icx_exc THEN
3700 COMMIT;
3701 RETURN NULL;
3702 END icx_session_ticket;
3703 ----------------------------
3704 FUNCTION icx_session_ticket (p_session_id IN NUMBER)
3705 RETURN VARCHAR2 IS
3706 BEGIN
3707 RETURN icx_session_ticket;
3708 END icx_session_ticket;
3709
3710 ------------------------------------------------------------------------------------------------
3711
3712 PROCEDURE common_bill_for_item ( in_inventory_item_id IN NUMBER,
3713 in_organization_id IN NUMBER,
3714 common_inventory_item_id OUT NOCOPY NUMBER,
3715 common_organization_id OUT NOCOPY NUMBER
3716 )
3717 IS
3718
3719 BEGIN
3720
3721 select ORGANIZATION_ID, ASSEMBLY_ITEM_ID
3722 into COMMON_ORGANIZATION_ID, COMMON_INVENTORY_ITEM_ID
3723 from BOM_BILL_OF_MATERIALS
3724 where BILL_SEQUENCE_ID in (select SOURCE_BILL_SEQUENCE_ID from BOM_BILL_OF_MATERIALS
3725 where ORGANIZATION_ID = in_organization_id
3726 and ASSEMBLY_ITEM_ID = in_inventory_item_id);
3727
3728
3729 EXCEPTION
3730 WHEN OTHERS THEN
3731 -- xERROR := cz_utils.REPORT('No common bill found for ' || in_inventory_item_id || ' ' || SQLERRM, 1, 'CZ_CF_API.common_bill_for_item', NULL);
3732 cz_utils.log_report('CZ_CF_API', 'common_bill_for_item', null,
3733 'No common bill found for ' || in_inventory_item_id || ' ' || SQLERRM,
3734 fnd_log.LEVEL_UNEXPECTED);
3735 END common_bill_for_item;
3736
3737 ------------------------------------------------------------------------------------------------
3738
3739 PROCEDURE common_bill_for_product(v_product_key IN VARCHAR2, c_product_key OUT NOCOPY VARCHAR2)
3740 IS
3741
3742 v_inventory_item_id NUMBER;
3743 v_organization_id NUMBER;
3744 c_inventory_item_id NUMBER;
3745 c_organization_id NUMBER;
3746
3747 BEGIN
3748
3749 --get inv and org id from product key
3750 v_organization_id := to_number(substr(v_product_key,1,instr(v_product_key,':')-1));
3751 v_inventory_item_id := to_number(substr(v_product_key,instr(v_product_key,':')+1));
3752
3753 common_bill_for_item(v_inventory_item_id,v_organization_id,c_inventory_item_id,c_organization_id);
3754
3755 -- build the product key for the common bill
3756 c_product_key := c_organization_id || ':' || c_inventory_item_id;
3757
3758
3759 EXCEPTION
3760 WHEN OTHERS THEN
3761 -- xERROR := cz_utils.REPORT('No common bill found for ' || v_product_key || ' ' || SQLERRM, 1, 'CZ_CF_API.common_bill_for_product', NULL);
3762 cz_utils.log_report('CZ_CF_API', 'common_bill_for_product', null,
3763 'No common bill found for ' || v_product_key || ' ' || SQLERRM,
3764 fnd_log.LEVEL_UNEXPECTED);
3765 END common_bill_for_product;
3766
3767 --------------------------------------------------------------------------------
3768 PROCEDURE pub_for_item_mobile_pvt
3769 (p_inventory_item_id IN NUMBER
3770 ,p_organization_id IN NUMBER
3771 ,p_application_id IN NUMBER
3772 ,p_usage_id IN NUMBER
3773 ,p_language IN VARCHAR2
3774 ,p_pub_start_date IN DATE
3775 ,p_pub_end_date IN DATE
3776 ,x_publication_ids OUT NOCOPY number_tbl_indexby_type
3777 ,x_model_ids OUT NOCOPY number_tbl_indexby_type
3778 ,x_ui_def_ids OUT NOCOPY number_tbl_indexby_type
3779 ,x_start_dates OUT NOCOPY date_tbl_indexby_type
3780 ,x_last_update_dates OUT NOCOPY date_tbl_indexby_type
3781 ,x_model_type OUT NOCOPY VARCHAR2
3782 )
3783 IS
3784 TYPE model_type_tbl_type IS TABLE OF cz_devl_projects.model_type%TYPE
3785 INDEX BY BINARY_INTEGER;
3786 l_model_type_tbl model_type_tbl_type;
3787 BEGIN
3788 SELECT pub.publication_id, pub.model_id, pub.ui_def_id, pub.start_date,
3789 pub.last_update_date, prj.model_type
3790 BULK COLLECT INTO x_publication_ids, x_model_ids, x_ui_def_ids,
3791 x_start_dates, x_last_update_dates, l_model_type_tbl
3792 FROM cz_model_applicabilities_v pub, cz_devl_projects prj
3793 WHERE pub.model_id = prj.devl_project_id
3794 AND prj.deleted_flag ='0' AND pub.deleted_flag = '0'
3795 AND pub.inventory_item_id = p_inventory_item_id
3796 AND bom_explosion_org_id = p_organization_id
3797 AND fnd_application_id = p_application_id
3798 AND usage_id = p_usage_id AND language = p_language
3799 AND UPPER(publication_mode) IN ( cz_api_pub.G_PRODUCTION_PUB_MODE , 'T')
3800 AND source_target_flag = TARGET_PUBLICATION
3801 AND ui_style = UI_STYLE_DHTML
3802 AND ( (p_pub_start_date >= start_date AND p_pub_start_date < disable_date) OR
3803 (P_pub_end_date > start_date AND p_pub_end_date <= disable_date) OR
3804 (start_date >= p_pub_start_date AND start_date < p_pub_end_date) OR
3805 (disable_date > p_pub_start_date AND disable_date <= p_pub_end_date) );
3806
3807 x_model_type := l_model_type_tbl(1);
3808 EXCEPTION
3809 WHEN NO_DATA_FOUND THEN
3810 NULL;
3811 END pub_for_item_mobile_pvt;
3812
3813 --------------------------------------------------------------------------------
3814 PROCEDURE publication_for_item_mobile
3815 (p_inventory_item_id IN NUMBER
3816 ,p_organization_id IN NUMBER
3817 ,p_calling_application_id IN NUMBER
3818 ,p_usage_name IN VARCHAR2
3819 ,p_pub_start_date IN DATE
3820 ,p_pub_end_date IN DATE
3821 ,x_publication_id_tbl OUT NOCOPY number_tbl_indexby_type
3822 ,x_model_id_tbl OUT NOCOPY number_tbl_indexby_type
3823 ,x_ui_def_id_tbl OUT NOCOPY number_tbl_indexby_type
3824 ,x_start_date_tbl OUT NOCOPY date_tbl_indexby_type
3825 ,x_last_update_date_tbl OUT NOCOPY date_tbl_indexby_type
3826 ,x_model_type OUT NOCOPY VARCHAR2
3827 )
3828 IS
3829 l_usage_name VARCHAR2(255) := p_usage_name;
3830 l_usage_id NUMBER;
3831 l_language VARCHAR2(4) := userenv('LANG');
3832 l_inventory_item_id NUMBER ;
3833 l_organization_id NUMBER;
3834
3835 BEGIN
3836 IF l_usage_name IS NULL THEN
3837 fnd_profile.get('CZ_PUBLICATION_USAGE', l_usage_name);
3838 END IF;
3839 l_usage_id := usage_id_from_usage_name(l_usage_name);
3840
3841 -- Keep these values to lookup common bill, if required
3842 IF (c_application_id IS NULL) THEN
3843 c_application_id := p_calling_application_id;
3844 END IF;
3845 IF (c_usage_name IS NULL) THEN
3846 c_usage_name := l_usage_name;
3847 END IF;
3848
3849 pub_for_item_mobile_pvt(p_inventory_item_id
3850 ,p_organization_id
3851 ,p_calling_application_id
3852 ,l_usage_id
3853 ,l_language
3854 ,p_pub_start_date
3855 ,p_pub_end_date
3856 ,x_publication_id_tbl
3857 ,x_model_id_tbl
3858 ,x_ui_def_id_tbl
3859 ,x_start_date_tbl
3860 ,x_last_update_date_tbl
3861 ,x_model_type
3862 );
3863
3864 IF (x_publication_id_tbl.COUNT = 0) THEN
3865 IF l_usage_id <> ANY_USAGE_ID THEN
3866 -- passing NULL is NOT the same as passing "any usage"
3867 pub_for_item_mobile_pvt(p_inventory_item_id
3868 ,p_organization_id
3869 ,p_calling_application_id
3870 ,ANY_USAGE_ID
3871 ,l_language
3872 ,p_pub_start_date
3873 ,p_pub_end_date
3877 ,x_start_date_tbl
3874 ,x_publication_id_tbl
3875 ,x_model_id_tbl
3876 ,x_ui_def_id_tbl
3878 ,x_last_update_date_tbl
3879 ,x_model_type
3880 );
3881 ELSE
3882 -- else get publication id of the common bill, if any
3883 common_bill_for_item(p_inventory_item_id, p_organization_id,
3884 l_inventory_item_id, l_organization_id);
3885 IF ( l_inventory_item_id IS NOT NULL AND
3886 l_organization_id IS NOT NULL AND
3887 (l_inventory_item_id <> p_inventory_item_id OR
3888 l_organization_id <> p_organization_id) ) THEN
3889 pub_for_item_mobile_pvt(l_inventory_item_id
3890 ,l_organization_id
3891 ,c_application_id
3892 ,c_usage_name
3893 ,l_language
3894 ,p_pub_start_date
3895 ,p_pub_end_date
3896 ,x_publication_id_tbl
3897 ,x_model_id_tbl
3898 ,x_ui_def_id_tbl
3899 ,x_start_date_tbl
3900 ,x_last_update_date_tbl
3901 ,x_model_type
3902 );
3903 ELSE
3904 c_application_id := NULL;
3905 c_usage_name := NULL;
3906 END IF;
3907 END IF;
3908 ELSE
3909 c_application_id := NULL;
3910 c_usage_name := NULL;
3911 END IF;
3912
3913 END publication_for_item_mobile;
3914 --------------------------------------------------------------------------------
3915 FUNCTION product_key_for_saved_config(p_config_hdr_id IN NUMBER,
3916 p_config_rev_nbr IN NUMBER
3917 )
3918 RETURN VARCHAR2 IS
3919 v_config_hdr_id NUMBER := p_config_hdr_id;
3920 v_config_rev_nbr NUMBER := p_config_rev_nbr;
3921 v_inventory_item_id NUMBER;
3922 v_organization_id NUMBER;
3923 v_product_key cz_devl_projects.product_key%TYPE := NULL;
3924 v_persist_comp_id NUMBER;
3925 v_model_identifier cz_config_hdrs.model_identifier%TYPE;
3926
3927 CURSOR prj_cursor IS
3928 SELECT product_key
3929 FROM CZ_DEVL_PROJECTS
3930 WHERE persistent_project_id = v_persist_comp_id
3931 AND product_key IS NOT NULL
3932 AND deleted_flag = '0';
3933
3934 BEGIN
3935 BEGIN
3936 SELECT persistent_component_id, model_identifier
3937 INTO v_persist_comp_id, v_model_identifier
3938 FROM CZ_CONFIG_HDRS
3939 WHERE config_hdr_id = v_config_hdr_id AND config_rev_nbr = v_config_rev_nbr;
3940 EXCEPTION
3941 WHEN NO_DATA_FOUND THEN
3942 RETURN NULL;
3943 END;
3944
3945 IF v_model_identifier IS NOT NULL THEN
3946 v_inventory_item_id := SUBSTR(v_model_identifier, 1, INSTR(v_model_identifier, ':')-1);
3947 v_organization_id := SUBSTR(v_model_identifier, INSTR(v_model_identifier, ':')+1,
3948 INSTR(v_model_identifier, ':', 1, 2) - (INSTR(v_model_identifier, ':')+1));
3949 v_product_key := v_organization_id || ':' || v_inventory_item_id;
3950 ELSE
3951 FOR v_prj_rec IN prj_cursor LOOP
3952 EXIT WHEN prj_cursor%rowcount > 1 OR prj_cursor%NOTFOUND;
3953 v_product_key := v_prj_rec.product_key;
3954 END LOOP;
3955
3956 END IF;
3957 RETURN v_product_key;
3958 END product_key_for_saved_config;
3959 --------------------------------------------------------------------------------
3960 FUNCTION pool_token_for_product_key(p_product_key IN VARCHAR2)
3961 RETURN VARCHAR2 IS
3962 v_pool_token cz_model_pool_mappings.pool_identifier%TYPE := NULL;
3963 -- vsingava 03 Feb '11. Bug11693636
3964 -- If we do not find any pool-token set up for the product-key,
3965 -- as a fail-over, read the setting value from cz_db_settings
3966
3967 BEGIN
3968 IF p_product_key IS NOT NULL THEN
3969 SELECT pool_identifier INTO v_pool_token FROM cz_model_pool_mappings WHERE model_product_key = p_product_key;
3970 END IF;
3971 RETURN v_pool_token;
3972 EXCEPTION WHEN NO_DATA_FOUND THEN
3973 BEGIN
3974 SELECT value INTO v_pool_token FROM cz_db_settings WHERE section_name = 'ORAAPPS_INTEGRATE' AND setting_id = 'DefaultPoolIdentifier';
3975 RETURN v_pool_token;
3976 EXCEPTION WHEN NO_DATA_FOUND THEN
3977 RETURN NULL;
3978 END;
3979 END pool_token_for_product_key;
3980
3981 --------------------------------------------------------------------------------
3982 --Bug 13031083 Changed parameters from product key to org_id and inv_item_id
3983 --to support models that are not imported in CZ
3984 PROCEDURE register_model_to_pool_cp(errbuf IN OUT NOCOPY VARCHAR2,
3985 retcode IN OUT NOCOPY NUMBER,
3986 p_pool_identifier IN VARCHAR2,
3987 p_org_id IN VARCHAR2,
3988 dp_org_id IN VARCHAR2,
3989 p_inventory_item_id IN VARCHAR2) AS
3990
3991 CURSOR C_GETMODELS(Org_ID NUMBER) IS
3992 SELECT A.ASSEMBLY_ITEM_ID FROM CZ_EXV_BILL_OF_MATERIALS A,CZ_EXV_MTL_SYSTEM_ITEMS B
3993 WHERE A.ASSEMBLY_ITEM_ID=B.INVENTORY_ITEM_ID
3994 AND A.ORGANIZATION_ID=B.ORGANIZATION_ID
3995 AND B.BOM_ITEM_TYPE=1
3996 AND A.ORGANIZATION_ID= Org_ID
3997 AND B.CONCATENATED_SEGMENTS = p_inventory_item_id;
3998
3999 nTop_ID CZ_XFR_PROJECT_BILLS.TOP_ITEM_ID%TYPE;
4000 nOrg_ID CZ_XFR_PROJECT_BILLS.ORGANIZATION_ID%TYPE;
4001 nModel_product_key CZ_MODEL_PUBLICATIONS.PRODUCT_KEY%TYPE;
4002 nRet BOOLEAN := FALSE;
4003
4007 errbuf := '';
4004 BEGIN
4005
4006 retcode := 0;
4008
4009 IF (NVL(p_org_id,'') = '') OR (NVL(p_inventory_item_id,'')= '') THEN
4010 RAISE INVALID_OPTION_EXCEPTION ;
4011 END IF;
4012
4013 SELECT ORGANIZATION_ID INTO nOrg_ID FROM CZ_EXV_ORGANIZATIONS
4014 WHERE ORGANIZATION_CODE = p_org_id;
4015
4016 OPEN C_GETMODELS(nOrg_ID);
4017 LOOP
4018 FETCH C_GETMODELS INTO nTop_ID;
4019 EXIT WHEN C_GETMODELS%NOTFOUND;
4020 END LOOP;
4021 CLOSE C_GETMODELS;
4022
4023 nModel_product_key := nOrg_ID || ':' || nTop_ID;
4024
4025 register_model_to_pool(errbuf, retcode, p_pool_identifier, nModel_product_key);
4026
4027 EXCEPTION
4028 WHEN MODEL_POOL_EFFINITY_EXC THEN
4029 retcode := 2;
4030 errbuf := 'Model to Pool affinity already exists for product key : ' ||
4031 nModel_product_key || '. Please unregister existing record before defining new affinity.';
4032 nRet := CZ_UTILS.log_report(errbuf,1,'CZ_CF_API.register_model_to_pool_cp',99999,NULL);
4033 WHEN INVALID_OPTION_EXCEPTION THEN
4034 retcode := 2;
4035 errbuf := 'Organization ID OR Inventory Item ID can not be NULL';
4036 nRet := CZ_UTILS.log_report(errbuf,1,'CZ_CF_API.register_model_to_pool_cp',99999,NULL);
4037 WHEN OTHERS THEN
4038 retcode:=2;
4039 errbuf:=CZ_UTILS.GET_TEXT('CZ_FATAL_INSTRUCTIONS','ERRORTEXT',SQLERRM);
4040 nRet := CZ_UTILS.log_report(errbuf,1,'CZ_CF_API.register_model_to_pool_cp',99999,NULL);
4041
4042 END register_model_to_pool_cp;
4043 --------------------------------------------------------------------------------
4044 PROCEDURE register_model_to_pool(errbuf IN OUT NOCOPY VARCHAR2,
4045 retcode IN OUT NOCOPY NUMBER,
4046 p_pool_identifier IN VARCHAR2,
4047 p_model_product_key IN VARCHAR2) AS
4048 PRAGMA AUTONOMOUS_TRANSACTION;
4049
4050 v_count NUMBER;
4051 nRet BOOLEAN := FALSE;
4052
4053 BEGIN
4054
4055 -- Added the check as a result of bug 13103351 to avoid
4056 -- duplicates for model pool affinity
4057 SELECT COUNT(*)
4058 INTO v_count
4059 FROM cz_model_pool_mappings
4060 WHERE model_product_key = p_model_product_key ;
4061
4062 IF v_count > 0 THEN
4063 RAISE MODEL_POOL_EFFINITY_EXC ;
4064 ELSE
4065 INSERT INTO cz_model_pool_mappings(pool_identifier, model_product_key)
4066 VALUES(p_pool_identifier, p_model_product_key);
4067 COMMIT;
4068 END IF;
4069
4070 EXCEPTION
4071 WHEN MODEL_POOL_EFFINITY_EXC THEN
4072 retcode := 2;
4073 errbuf := 'Model to Pool affinity already exists for product key : ' ||
4074 p_model_product_key || '. Please unregister existing record before defining new affinity.';
4075 nRet := CZ_UTILS.log_report(errbuf,1,'CZ_CF_API.register_model_to_pool_cp',99999,NULL);
4076 RAISE ;
4077 WHEN OTHERS THEN
4078 retcode:=2;
4079 errbuf:=CZ_UTILS.GET_TEXT('CZ_FATAL_INSTRUCTIONS','ERRORTEXT',SQLERRM);
4080 nRet := CZ_UTILS.log_report(errbuf,1,'CZ_CF_API.register_model_to_pool_cp',99999,NULL);
4081 RAISE;
4082 END register_model_to_pool;
4083
4084 --------------------------------------------------------------------------------
4085 --Bug 13031083 Changed parameters from product key to org_id and inv_item_id
4086 --to support models that are not imported in CZ
4087 PROCEDURE unregister_model_from_pool_cp(errbuf IN OUT NOCOPY VARCHAR2,
4088 retcode IN OUT NOCOPY NUMBER,
4089 p_pool_identifier IN VARCHAR2,
4090 p_org_id IN VARCHAR2,
4091 dp_org_id IN VARCHAR2,
4092 p_inventory_item_id IN VARCHAR2) AS
4093
4094 CURSOR C_GETMODELS(Org_ID NUMBER) IS
4095 SELECT A.ASSEMBLY_ITEM_ID FROM CZ_EXV_BILL_OF_MATERIALS A,CZ_EXV_MTL_SYSTEM_ITEMS B
4096 WHERE A.ASSEMBLY_ITEM_ID=B.INVENTORY_ITEM_ID
4097 AND A.ORGANIZATION_ID=B.ORGANIZATION_ID
4098 AND B.BOM_ITEM_TYPE=1
4099 AND A.ORGANIZATION_ID= Org_ID
4100 AND B.CONCATENATED_SEGMENTS = p_inventory_item_id;
4101
4102 nTop_ID CZ_XFR_PROJECT_BILLS.TOP_ITEM_ID%TYPE;
4103 nOrg_ID CZ_XFR_PROJECT_BILLS.ORGANIZATION_ID%TYPE;
4104 nModel_product_key CZ_MODEL_PUBLICATIONS.PRODUCT_KEY%TYPE;
4105 nRet BOOLEAN := FALSE;
4106 BEGIN
4107
4108 retcode := 0;
4109 errbuf := '';
4110
4111 IF (NVL(p_org_id,'') = '') OR (NVL(p_inventory_item_id,'')= '') THEN
4112 RAISE INVALID_OPTION_EXCEPTION ;
4113 END IF;
4114
4115 SELECT ORGANIZATION_ID INTO nOrg_ID FROM CZ_EXV_ORGANIZATIONS
4116 WHERE ORGANIZATION_CODE = p_org_id;
4117
4118 OPEN C_GETMODELS(nOrg_ID);
4119 LOOP
4120 FETCH C_GETMODELS INTO nTop_ID;
4121 EXIT WHEN C_GETMODELS%NOTFOUND;
4122 END LOOP;
4123 CLOSE C_GETMODELS;
4124
4125 nModel_product_key := nOrg_ID || ':' || nTop_ID;
4126
4127 unregister_model_from_pool(errbuf, retcode, p_pool_identifier, nModel_product_key);
4128
4129 EXCEPTION
4130 WHEN INVALID_OPTION_EXCEPTION THEN
4131 retcode := 2;
4132 errbuf := 'Organization ID OR Inventory Item ID can not be NULL';
4133 nRet := CZ_UTILS.log_report(errbuf,1,'CZ_CF_API.unregister_model_to_pool_cp',99999,NULL);
4134 WHEN OTHERS THEN
4135 retcode:=2;
4136 errbuf:=CZ_UTILS.GET_TEXT('CZ_FATAL_INSTRUCTIONS','ERRORTEXT',SQLERRM);
4137 nRet := CZ_UTILS.log_report(errbuf,1,'CZ_CF_API.unregister_model_from_pool_cp',99999,NULL);
4138
4139 END unregister_model_from_pool_cp;
4140
4141 --------------------------------------------------------------------------------
4142 PROCEDURE unregister_model_from_pool(errbuf IN OUT NOCOPY VARCHAR2,
4143 retcode IN OUT NOCOPY NUMBER,
4147 nRet BOOLEAN := FALSE;
4144 p_pool_identifier IN VARCHAR2,
4145 p_model_product_key IN VARCHAR2) AS
4146 PRAGMA AUTONOMOUS_TRANSACTION;
4148
4149 BEGIN
4150 DELETE FROM cz_model_pool_mappings WHERE pool_identifier = p_pool_identifier
4151 AND model_product_key = p_model_product_key;
4152 COMMIT;
4153 EXCEPTION
4154 WHEN OTHERS THEN
4155 retcode:=2;
4156 errbuf:=CZ_UTILS.GET_TEXT('CZ_FATAL_INSTRUCTIONS','ERRORTEXT',SQLERRM);
4157 nRet := CZ_UTILS.log_report(errbuf,1,'CZ_CF_API.unregister_model_from_pool_cp',99999,NULL);
4158 RAISE;
4159 END unregister_model_from_pool;
4160
4161 --------------------------------------------------------------------------------
4162 PROCEDURE unregister_pool_cp(errbuf IN OUT NOCOPY VARCHAR2,
4163 retcode IN OUT NOCOPY NUMBER,
4164 p_pool_identifier IN VARCHAR2) AS
4165
4166 nRet BOOLEAN := FALSE;
4167 BEGIN
4168
4169 retcode := 0;
4170 errbuf := '';
4171
4172 unregister_pool(errbuf, retcode, p_pool_identifier);
4173
4174 EXCEPTION
4175 WHEN OTHERS THEN
4176 retcode:=2;
4177 errbuf:=CZ_UTILS.GET_TEXT('CZ_FATAL_INSTRUCTIONS','ERRORTEXT',SQLERRM);
4178 nRet := CZ_UTILS.log_report(errbuf,1,'CZ_CF_API.unregister_pool_cp',99999,NULL);
4179 END unregister_pool_cp;
4180
4181 --------------------------------------------------------------------------------
4182 PROCEDURE unregister_pool(errbuf IN OUT NOCOPY VARCHAR2,
4183 retcode IN OUT NOCOPY NUMBER,
4184 p_pool_identifier VARCHAR2) AS
4185 PRAGMA AUTONOMOUS_TRANSACTION;
4186
4187 nRet BOOLEAN := FALSE;
4188
4189 BEGIN
4190 DELETE FROM cz_model_pool_mappings WHERE pool_identifier = p_pool_identifier;
4191 COMMIT;
4192 EXCEPTION
4193 WHEN OTHERS THEN
4194 retcode:=2;
4195 errbuf:=CZ_UTILS.GET_TEXT('CZ_FATAL_INSTRUCTIONS','ERRORTEXT',SQLERRM);
4196 nRet := CZ_UTILS.log_report(errbuf,1,'CZ_CF_API.unregister_pool_cp',99999,NULL);
4197 RAISE;
4198 END unregister_pool;
4199
4200 --------------------------------------------------------------------------------
4201 --LA 9595142 Procedures to enable the BOM model for Import
4202 PROCEDURE enable_model_for_import(inDevlProjectId IN NUMBER, xStatus IN OUT NOCOPY VARCHAR2)
4203 IS
4204 lOSR CZ_DEVL_PROJECTS.ORIG_SYS_REF%TYPE := NULL;
4205 lModelId CZ_DEVL_PROJECTS.DEVL_PROJECT_ID%TYPE := -1;
4206 xError BOOLEAN := FALSE;
4207 lConfigEngineType CZ_DEVL_PROJECTS.CONFIG_ENGINE_TYPE%TYPE;
4208 BEGIN
4209 xStatus := 'OK' ;
4210 BEGIN
4211 --Check whether model exists in repository
4212 SELECT orig_sys_ref, NVL(config_engine_type,'L') INTO lOSR, lConfigEngineType
4213 FROM cz_devl_projects d
4214 WHERE d.deleted_flag = '0'
4215 AND d.devl_project_id = inDevlProjectId
4216 AND EXISTS (SELECT NULL FROM CZ_RP_ENTRIES WHERE deleted_flag = '0'
4217 AND object_type = 'PRJ' AND object_id = d.devl_project_id)
4218 AND orig_sys_ref IS NOT NULL;
4219 EXCEPTION
4220 WHEN NO_DATA_FOUND THEN
4221 xError := cz_utils.log_report('Provided model with ID= ' || inDevlProjectId
4222 || 'does not exist in the Repository.',1,'enable_model_for_import',9991,null);
4223 xStatus := 'ERR' ;
4224 RAISE;
4225 WHEN OTHERS THEN
4226 xError := cz_utils.log_report('Error retrieving record for Model ID= ' ||
4227 inDevlProjectId || ': ' || SQLERRM,1,'enable_model_for_import',9992,null);
4228 xStatus := 'ERR' ;
4229 RAISE;
4230 END ;
4231
4232 BEGIN
4233 -- check if there is any other model enabled for given orig_sys_ref
4234 SELECT d.devl_project_id INTO lModelId FROM cz_devl_projects d
4235 WHERE d.orig_sys_ref = lOSR
4236 AND d.deleted_flag = '0'
4237 AND NVL(d.import_enabled,'0') = '1'
4238 AND NVL(d.config_engine_type,'L') = lConfigEngineType
4239 AND EXISTS (SELECT NULL FROM CZ_RP_ENTRIES WHERE deleted_flag = '0'
4240 AND object_type = 'PRJ' AND object_id = d.devl_project_id) ;
4241
4242 --dbms_output.put_line('model id from Orig_sys_ref ' || lModelId);
4243
4244 IF (lModelId <> inDevlProjectId) THEN
4245 --dbms_output.put_line('Can not enable model id ' || inDevlProjectId);
4246 xError := cz_utils.log_report('Error: Another copy of the model enabled for Import : '
4247 || lModelId || '. Disable it before enabling model ' ||inDevlProjectId ,1,'enable_model_for_import',9993,null);
4248 xStatus := 'ERR' ;
4249 END IF;
4250 EXCEPTION
4251 WHEN NO_DATA_FOUND THEN
4252 BEGIN
4253 UPDATE cz_devl_projects SET import_enabled = '1'
4254 WHERE deleted_flag = '0'
4255 AND devl_project_id = inDevlProjectId ;
4256 xError := cz_utils.log_report('Model enabled for Import : '
4257 || inDevlProjectId ,1,'enable_model_for_import',9994,null);
4258
4259 EXCEPTION
4260 WHEN OTHERS THEN
4261 xError := cz_utils.log_report('Error enabling model for Import with model id= ' ||
4262 inDevlProjectId || ': ' || SQLERRM,1,'enable_model_for_import',9995,null);
4263 xStatus := 'ERR' ;
4264 RAISE;
4265 END;
4266 WHEN TOO_MANY_ROWS THEN
4267 xError := cz_utils.log_report('Error: Multiple copies of the model enabled for Import : ('
4268 || lOSR || ')',1,'enable_model_for_import',9996,null);
4269 xStatus := 'ERR' ;
4270 RAISE;
4271 WHEN OTHERS THEN
4272 xError := cz_utils.log_report('Error retrieving record for Model with orig_sys_ref= ' ||
4273 lOSR || ': ' || SQLERRM,1,'enable_model_for_import',9997,null);
4274 xStatus := 'ERR' ;
4275 RAISE;
4276 END;
4277 COMMIT;
4278 END enable_model_for_import;
4279
4280 ------------------------------------------------------------------------------
4281 --LA 9595142 Procedure to disable the BOM model for Import
4282 PROCEDURE disable_model_for_import(inDevlProjectId IN NUMBER, xStatus IN OUT NOCOPY VARCHAR2 )
4283 IS
4284 xError BOOLEAN := FALSE;
4285 lOSR CZ_DEVL_PROJECTS.ORIG_SYS_REF%TYPE := NULL;
4286 BEGIN
4287
4288 BEGIN
4289 --Check whether model exists in repository
4290 SELECT orig_sys_ref INTO lOSR FROM cz_devl_projects d
4291 WHERE d.deleted_flag = '0'
4292 AND d.devl_project_id = inDevlProjectId
4293 AND EXISTS (SELECT NULL FROM CZ_RP_ENTRIES WHERE deleted_flag = '0'
4294 AND object_type = 'PRJ' AND object_id = d.devl_project_id)
4295 AND orig_sys_ref IS NOT NULL ;
4296 EXCEPTION
4297 WHEN NO_DATA_FOUND THEN
4298 xError := cz_utils.log_report('Provided model with ID= ' || inDevlProjectId
4299 || 'does not exist in the Repository.',1,'disable_model_for_import',9991,null);
4300 xStatus := 'ERR' ;
4301 RAISE;
4302 WHEN OTHERS THEN
4303 xError := cz_utils.log_report('Error retrieving record for Model ID= ' ||
4304 inDevlProjectId || ': ' || SQLERRM,1,'disable_model_for_import',9992,null);
4305 xStatus := 'ERR' ;
4306 RAISE;
4307 END ;
4308
4309 BEGIN
4310 UPDATE cz_devl_projects SET import_enabled = '0'
4311 WHERE deleted_flag = '0'
4312 AND devl_project_id = inDevlProjectId ;
4313
4314 COMMIT;
4315 xStatus := 'OK' ;
4316 EXCEPTION
4317 WHEN OTHERS THEN
4318 xError := cz_utils.log_report('Error while disabling the model for import. Model ID= ' ||
4319 inDevlProjectId || ': ' || SQLERRM,1,'disable_model_for_import',9999,null);
4320 commit;
4321 xStatus := 'ERR' ;
4322 END;
4323
4324 END disable_model_for_import;
4325 --------------------------------------------------------------------------------
4326
4327 BEGIN
4328 id_increment := cz_utils.conv_num(get_db_setting('SCHEMA', 'ORACLESEQUENCEINCR'));
4329 IF id_increment IS NULL THEN
4330 id_increment := default_incr;
4331 END IF;
4332
4333 BEGIN
4334 transferTimeout := To_number(get_db_setting('SCHEMA', 'UTLHTTPTRANSFERTIMEOUT'));
4335 EXECUTE IMMEDIATE 'BEGIN UTL_HTTP.GET_TRANSFER_TIMEOUT(:1); END;' USING IN OUT defaultTimeout;
4336 EXCEPTION
4337 WHEN OTHERS THEN
4338 transferTimeout := NULL;
4339 defaultTimeout := NULL;
4340 END;
4341 END CZ_CF_API;
[Home] [Help]