[Home] [Help]
PACKAGE BODY: APPS.CZ_CF_API
Source
1 PACKAGE BODY CZ_CF_API AS
2 /* $Header: czcfapib.pls 120.15.12010000.2 2008/11/05 17:26:06 smanna ship $ */
3 ------------------------------------------------------------------------------------------
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CZ_CF_API';
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;
11 last_msg_seq_allocated NUMBER := NULL;
12 next_msg_seq_to_use NUMBER := 0;
13 id_increment INTEGER;
14 DEFAULT_INCR CONSTANT PLS_INTEGER :=20;
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
49 transferTimeout PLS_INTEGER := NULL;
50 defaultTimeout PLS_INTEGER := NULL;
51
52 ----constants used in check deltas
53 ITEM_DELETE_MESSAGE CONSTANT VARCHAR2(30) := 'CZ_BATCH_VAL_ITEM_DELETED';
54 ITEM_ADD_MESSAGE CONSTANT VARCHAR2(30) := 'CZ_BATCH_VAL_ITEM_ADDED';
55 QTY_CHANGE_MESSAGE CONSTANT VARCHAR2(30) := 'CZ_BATCH_VAL_DIFF';
56
57 -- operation code for the old bv behavior: set quantity
58 BV_OPERATION_OLD CONSTANT INTEGER := 0;
59
60 -- pseudo model type
61 BV_MODEL_TYPE CONSTANT VARCHAR2(1) := 'C';
62
63 --------------------------------------------------------------------------------
64 FUNCTION get_db_setting (p_section_name IN VARCHAR2, p_setting IN VARCHAR2)
65 RETURN VARCHAR2 IS
66 l_ret_value cz_db_settings.value%TYPE;
67 BEGIN
68 SELECT value INTO l_ret_value FROM cz_db_settings WHERE Upper(section_name)
69 = Upper(p_section_name) AND Upper(setting_id) = Upper(p_setting);
70 RETURN l_ret_value;
71 END;
72
73 ------------------------------------------------------------------------------------------------
74 FUNCTION usage_id_from_usage_name (p_usage_name IN VARCHAR2)
75 RETURN NUMBER
76 IS
77 v_usage_id NUMBER;
78 BEGIN
79
80 IF p_usage_name IS NOT NULL THEN
81 BEGIN
82 SELECT model_usage_id
83 INTO v_usage_id
84 FROM CZ_MODEL_USAGES
85 WHERE LTRIM(RTRIM(UPPER(CZ_MODEL_USAGES.name))) = LTRIM(RTRIM(UPPER(p_usage_name)))
86 AND cz_model_usages.in_use = '1';
87 EXCEPTION
88 WHEN OTHERS THEN
89 v_usage_id := ANY_USAGE_ID;
90 END;
91 ELSE
92 v_usage_id := ANY_USAGE_ID;
93 END IF;
94
95 RETURN v_usage_id;
96 END usage_id_from_usage_name;
97
98 ------------------------------------------------------------------------------------------
99 -- The next two functions convert between ui_style ('0' and '3')
100 -- and the ui_type input ('DHTML' and 'APPLET')
101
102 FUNCTION ui_style_from_ui_type (ui_type IN VARCHAR2)
103 RETURN VARCHAR2 IS
104 v_ui_style VARCHAR2(3);
105 BEGIN
106 SELECT DECODE(ui_type, 'APPLET', UI_STYLE_APPLET, 'DHTML', UI_STYLE_DHTML,
107 'JRAD', UI_STYLE_JRAD, 'WEGA', UI_STYLE_WEGA, NULL)
108 INTO v_ui_style
109 FROM dual;
110 RETURN v_ui_style;
111 END;
112
113 ------------------------------------------------------------------------------------------
114
115 FUNCTION ui_type_from_ui_style (ui_style IN VARCHAR2)
116 RETURN VARCHAR2 IS
117 v_ui_type VARCHAR2(30);
118 BEGIN
119 SELECT DECODE(ui_style, UI_STYLE_APPLET, 'APPLET', UI_STYLE_DHTML, 'DHTML',
120 UI_STYLE_JRAD, 'JRAD', UI_STYLE_WEGA, 'WEGA', NULL)
121 INTO v_ui_type
122 FROM dual;
123 RETURN v_ui_type;
124 END;
125
126 ------------------------------------------------------------------------------------------
127 FUNCTION NEXT_CONFIG_HDR_ID RETURN INTEGER IS
128 ID_to_return INTEGER;
129 BEGIN
130 IF ( (last_hdr_allocated IS NULL)
131 OR
132 (next_hdr_to_use = (NVL(last_hdr_allocated, 0) + id_increment)) ) THEN
133 SELECT cz_config_hdrs_s.NEXTVAL
134 INTO last_hdr_allocated
135 FROM dual;
136 next_hdr_to_use := last_hdr_allocated;
137 END IF;
138 id_to_return := next_hdr_to_use;
139 next_hdr_to_use := next_hdr_to_use + 1;
140 RETURN id_to_return;
141 END ;
142
143 --------------------------------------------------------------------------------
144 FUNCTION get_next_msg_seq RETURN NUMBER
145 IS
146 l_msg_seq NUMBER;
147
148 BEGIN
149 IF ((last_msg_seq_allocated IS NULL) OR
150 (next_msg_seq_to_use = last_msg_seq_allocated + id_increment)) THEN
151 SELECT cz_config_messages_s.NEXTVAL INTO last_msg_seq_allocated FROM dual;
152 next_msg_seq_to_use := last_msg_seq_allocated;
153 END IF;
154
155 l_msg_seq := next_msg_seq_to_use;
156 next_msg_seq_to_use := next_msg_seq_to_use + 1;
157 RETURN l_msg_seq;
158 END get_next_msg_seq;
159
160 ------------------------------------------------------------------------------------------
161 -- Note: Returns a message having maiximum length of 1000 chars, i.e., message will be
162 -- trancated if its length is more than 1000.
163 FUNCTION retrieve_log_msg(p_run_id IN NUMBER) RETURN VARCHAR2
164 IS
165 l_msg VARCHAR2(3000) := 'RUN_ID=' || to_char(p_run_id) || ':';
166
167 CURSOR log_msg_csr IS
168 SELECT message
169 FROM cz_db_logs
170 WHERE run_id = p_run_id
171 ORDER BY logtime;
172
173 BEGIN
174 FOR msg_rec IN log_msg_csr LOOP
175 l_msg := l_msg || ' ' || msg_rec.message;
176 EXIT WHEN (length(l_msg) > 999) OR log_msg_csr%NOTFOUND;
177 END LOOP;
178
179 RETURN substr(l_msg, 1, 1000);
180 END retrieve_log_msg;
181
182 ------------------------------------------------------------------------------------
183 PROCEDURE copy_configuration(config_hdr_id IN NUMBER,
184 config_rev_nbr IN NUMBER,
185 new_config_flag IN VARCHAR2,
186 out_config_hdr_id IN OUT NOCOPY NUMBER,
187 out_config_rev_nbr IN OUT NOCOPY NUMBER,
188 error_message IN OUT NOCOPY VARCHAR2,
189 return_value IN OUT NOCOPY NUMBER,
190 handle_deleted_flag IN VARCHAR2 DEFAULT NULL,
191 new_name IN VARCHAR2 DEFAULT NULL)
192 IS
193 l_api_version CONSTANT NUMBER := 1.0;
194 l_copy_mode VARCHAR2(1);
195 l_ret_status VARCHAR2(1);
196 l_msg_count INTEGER;
197
198 l_orig_item_id_tbl CZ_API_PUB.number_tbl_type;
199 l_new_item_id_tbl CZ_API_PUB.number_tbl_type;
200 API_CALL_EXC EXCEPTION;
201
202 BEGIN
203 IF ((new_config_flag IS NOT NULL) AND
204 (new_config_flag='0')) THEN
205 l_copy_mode := CZ_API_PUB.G_NEW_REVISION_COPY_MODE;
206 ELSE
207 l_copy_mode := CZ_API_PUB.G_NEW_HEADER_COPY_MODE;
208 END IF;
209
210 cz_config_api_pub.copy_configuration
211 (l_api_version
212 ,config_hdr_id
213 ,config_rev_nbr
214 ,l_copy_mode
215 ,out_config_hdr_id
216 ,out_config_rev_nbr
217 ,l_orig_item_id_tbl
218 ,l_new_item_id_tbl
219 ,l_ret_status
220 ,l_msg_count
221 ,error_message
222 ,handle_deleted_flag
223 ,new_name
224 );
225
226 IF (l_ret_status = FND_API.G_RET_STS_SUCCESS) THEN
227 IF (l_orig_item_id_tbl.count > 0) THEN
228 RAISE API_CALL_EXC;
229 END IF;
230 return_value := 1;
231 ELSE
232 return_value := 0;
233 END IF;
234
235 EXCEPTION
236 WHEN API_CALL_EXC THEN
237 return_value:=0;
238 error_message:=CZ_UTILS.GET_TEXT('CZ_INCOMPAT_COPY_CFG');
239 -- xERROR:=CZ_UTILS.REPORT(error_message,1,'CZ_CF_API: copy configuration',11276);
240 cz_utils.log_report('CZ_CF_API', 'copy_configuration', null,
241 error_message, fnd_log.LEVEL_ERROR);
242
243 WHEN OTHERS THEN
244 return_value:=0;
245 error_message:=SQLERRM;
246 -- xERROR:=CZ_UTILS.REPORT(error_message,1,'CZ_CF_API: copy configuration',11276);
247 cz_utils.log_report('CZ_CF_API', 'copy_configuration', null,
248 error_message, fnd_log.LEVEL_UNEXPECTED);
249 END copy_configuration;
250
251 -------------------------------------------------------------------------------------
252 PROCEDURE copy_configuration_auto(config_hdr_id IN NUMBER,
253 config_rev_nbr IN NUMBER,
254 new_config_flag IN VARCHAR2,
255 out_config_hdr_id IN OUT NOCOPY NUMBER,
256 out_config_rev_nbr IN OUT NOCOPY NUMBER,
257 Error_message IN OUT NOCOPY VARCHAR2,
258 Return_value IN OUT NOCOPY NUMBER,
259 handle_deleted_flag IN VARCHAR2 DEFAULT NULL,
260 new_name IN VARCHAR2 DEFAULT NULL)
261 IS
262
263 PRAGMA AUTONOMOUS_TRANSACTION;
264 BEGIN
265 CZ_CF_API.copy_configuration(config_hdr_id,config_rev_nbr,new_config_flag,out_config_hdr_id,
266 out_config_rev_nbr,Error_message,Return_value,handle_deleted_flag, new_name);
267 COMMIT;
268 END copy_configuration_auto;
269
270 ------------------------------------------------------------------------------------------
271 PROCEDURE delete_configuration(config_hdr_id IN NUMBER,
272 config_rev_nbr IN NUMBER,
273 usage_exists IN OUT NOCOPY NUMBER,
274 Error_message IN OUT NOCOPY VARCHAR2,
275 Return_value IN OUT NOCOPY NUMBER)
276 IS
277 in_config_hdr_id NUMBER := config_hdr_id;
278 in_config_rev_nbr NUMBER := config_rev_nbr;
279 l_model_instantiation_type cz_config_hdrs.model_instantiation_type%TYPE;
280 l_component_instance_type cz_config_hdrs.component_instance_type%TYPE;
281 l_instance_hdr_id_tbl number_tbl_indexby_type;
282 l_instance_rev_nbr_tbl number_tbl_indexby_type;
283 l_run_id NUMBER;
284 l_ndebug NUMBER := 0;
285
286 del_ib_config_exc EXCEPTION;
287 ib_exception EXCEPTION;
288 l_active_instances_found NUMBER;
289 l_number_of_active_instances NUMBER;
290 l_return_revision NUMBER;
291 l_ib_error NUMBER;
292
293 TYPE instype_tbl_type IS TABLE OF cz_config_hdrs.component_instance_type%TYPE INDEX BY PLS_INTEGER;
294 l_instance_type_tbl instype_tbl_type;
295
296 BEGIN
297 Return_value:=1;
298 Error_message:='';
299 usage_exists:=1;
300 l_active_instances_found := 0;
301 l_number_of_active_instances := 0;
302 l_ib_error := 0;
303
304 SAVEPOINT start_transaction;
305
306 -- input config must be network container or non-network config
307 -- i.e., component_instance_type must be 'R'
308 BEGIN
309 SELECT model_instantiation_type, component_instance_type
310 INTO l_model_instantiation_type, l_component_instance_type
311 FROM cz_config_hdrs
312 WHERE config_hdr_id = in_config_hdr_id AND config_rev_nbr = in_config_rev_nbr;
313 EXCEPTION
314 WHEN NO_DATA_FOUND THEN
315 RETURN;
316 END;
317
318 l_ndebug := 1;
319 IF (l_component_instance_type <> ROOT) THEN
320 RAISE CONFIG_HDR_TYPE_EXC;
321 END IF;
322
323 SELECT instance_hdr_id, instance_rev_nbr, component_instance_type
324 BULK COLLECT INTO l_instance_hdr_id_tbl, l_instance_rev_nbr_tbl, l_instance_type_tbl
325 FROM cz_config_items
326 WHERE config_hdr_id = in_config_hdr_id AND config_rev_nbr = in_config_rev_nbr
327 AND deleted_flag = '0'
328 AND component_instance_type IN (GENERIC_INSTANCE_ROOT, NETWORK_INSTANCE_ROOT);
329
330 l_ndebug := 2;
331
332 -- lkattamu; bug 5289742; Loop through the config instances
333 IF (l_model_instantiation_type = NETWORK AND l_instance_hdr_id_tbl.COUNT > 0) THEN
334 FOR i IN l_instance_hdr_id_tbl.FIRST..l_instance_hdr_id_tbl.LAST LOOP
335 l_ndebug := 3;
336 IF l_instance_type_tbl(i) = NETWORK_INSTANCE_ROOT THEN
337 EXECUTE IMMEDIATE
338 'DECLARE ' ||
339 ' p_install_config_rec csi_cz_int.config_rec; ' ||
340 ' p_return_status VARCHAR2(10); ' ||
341 ' p_return_message VARCHAR2(2000); ' ||
342 ' p_instance_level VARCHAR2(1000); ' ||
343 ' api_result NUMBER := 0; ' ||
344 ' l_ib_errored NUMBER := 0; ' ||
345 'BEGIN ' ||
346 ' csi_cz_int.get_configuration_revision ( ' ||
347 ' p_config_header_id => :instance_hdr_id, ' ||
348 ' p_target_commitment_date => NULL, ' ||
349 ' px_instance_level => p_instance_level, ' ||
350 ' x_install_config_rec => p_install_config_rec, ' ||
351 ' x_return_status => p_return_status, ' ||
352 ' x_return_message => p_return_message ' ||
353 ' ); ' ||
354 ' IF (p_return_status <> fnd_api.g_ret_sts_success) THEN ' ||
355 ' l_ib_errored := 1; ' ||
356 ' ELSE ' ||
357 ' IF (p_install_config_rec.config_inst_rev_num IS NULL) THEN ' ||
358 ' api_result := 0; ' ||
359 ' ELSE ' ||
360 ' api_result := 1; ' ||
361 ' END IF; ' ||
362 ' END IF; ' ||
363 ' :result := api_result; ' ||
364 ' :l_return_revision := p_install_config_rec.config_inst_rev_num; ' ||
365 ' :l_ib_error := l_ib_errored; ' ||
366 ' :error_message := p_return_message; ' ||
367 'END; '
368 USING IN l_instance_hdr_id_tbl(i), OUT l_active_instances_found, OUT l_return_revision, OUT l_ib_error, OUT error_message;
369 IF (l_ib_error = 1) THEN
370 -- Return to the caller, without processing other instances, when there is an IB exception
371 RAISE ib_exception; -- This call just takes control to WHEN OTHERS exception handler
372 END IF;
373 -- Check if the active instance's revision is same as the revision in the config instance
374 IF ((l_active_instances_found = 1) AND (l_instance_rev_nbr_tbl(i) = l_return_revision)) THEN
375 l_number_of_active_instances := 1;
376 EXIT;
377 END IF;
378 END IF;
379 END LOOP;
380 END IF;
381
382 l_ndebug := 4;
383 IF (l_number_of_active_instances = 0) THEN
384 usage_exists:=0;
385 -- No ative instance exists, go ahead and delete
386 DELETE FROM CZ_CONFIG_ATTRIBUTES
387 WHERE CONFIG_HDR_ID=in_config_hdr_id
388 AND CONFIG_REV_NBR=in_config_rev_nbr;
389
390 DELETE FROM CZ_CONFIG_ITEMS
391 WHERE CONFIG_HDR_ID=in_config_hdr_id
392 AND CONFIG_REV_NBR=in_config_rev_nbr;
393
394 DELETE FROM CZ_CONFIG_INPUTS
395 WHERE CONFIG_HDR_ID=in_config_hdr_id
396 AND CONFIG_REV_NBR=in_config_rev_nbr;
397
398 DELETE FROM CZ_CONFIG_MESSAGES
399 WHERE CONFIG_HDR_ID=in_config_hdr_id
400 AND CONFIG_REV_NBR=in_config_rev_nbr;
401
402 l_ndebug := 5;
403
404 IF (l_model_instantiation_type = NETWORK AND l_instance_hdr_id_tbl.COUNT > 0) THEN
405 FOR i IN l_instance_hdr_id_tbl.FIRST .. l_instance_hdr_id_tbl.LAST LOOP
406 IF l_instance_type_tbl(i) = NETWORK_INSTANCE_ROOT THEN
407 DELETE FROM CZ_CONFIG_EXT_ATTRIBUTES
408 WHERE CONFIG_HDR_ID = l_instance_hdr_id_tbl(i)
409 AND CONFIG_REV_NBR = l_instance_rev_nbr_tbl(i);
410 END IF;
411 END LOOP;
412 END IF;
413
414 l_ndebug := 6;
415
416 l_instance_hdr_id_tbl(l_instance_hdr_id_tbl.COUNT+1) := in_config_hdr_id;
417 l_instance_rev_nbr_tbl(l_instance_rev_nbr_tbl.COUNT+1) := in_config_rev_nbr;
418 FORALL i IN l_instance_hdr_id_tbl.FIRST .. l_instance_hdr_id_tbl.LAST
419 DELETE FROM CZ_CONFIG_HDRS
420 WHERE CONFIG_HDR_ID = l_instance_hdr_id_tbl(i)
421 AND CONFIG_REV_NBR = l_instance_rev_nbr_tbl(i);
422
423 l_ndebug := 7;
424
425 -- delete ib data if necessary
426 cz_ib_transactions.remove_ib_config(p_session_config_hdr_id => in_config_hdr_id
427 ,p_session_config_rev_nbr => in_config_rev_nbr
428 ,x_run_id => l_run_id
429 );
430 IF (l_run_id <> 0) THEN
431 RAISE del_ib_config_exc;
432 END IF;
433 ELSE
434 -- Mark the configuration session as "To Be Deleted" as few config instances are still active
435 UPDATE cz_config_hdrs
436 SET to_be_deleted_flag = '1'
437 WHERE config_hdr_id=in_config_hdr_id and
438 config_rev_nbr = in_config_rev_nbr;
439 END IF;
440
441 EXCEPTION
442 WHEN CONFIG_HDR_TYPE_EXC THEN
443 Return_value:=0;
444 Error_message:=CZ_UTILS.GET_TEXT('CZ_CFG_DEL_HDR_TYPE', 'id', in_config_hdr_id,
445 'revision', in_config_rev_nbr, 'type', l_component_instance_type);
446 -- xERROR:=CZ_UTILS.REPORT(Error_message,1,'CZ_CF_API.delete_configuration',11276);
447 cz_utils.log_report('CZ_CF_API', 'delete_configuration', l_ndebug, error_message,
448 fnd_log.LEVEL_ERROR);
449
450 WHEN del_ib_config_exc THEN
451 Return_value := 0;
452 Error_message := retrieve_log_msg(l_run_id);
453 ROLLBACK TO start_transaction;
454 cz_utils.log_report('CZ_CF_API', 'delete_configuration', l_ndebug, error_message,
455 fnd_log.LEVEL_ERROR);
456
457 WHEN OTHERS THEN
458 Return_value:=0;
459 IF (error_message IS NOT NULL) THEN
460 Error_message := CZ_UTILS.GET_TEXT('CZ_CFG_DEL_ERROR', 'HDRID', in_config_hdr_id, 'REVNBR', in_config_rev_nbr, 'ERRMSG', error_message);
461 ELSE
462 Error_message := CZ_UTILS.GET_TEXT('CZ_CFG_DEL_ERROR', 'HDRID', in_config_hdr_id, 'REVNBR', in_config_rev_nbr, 'ERRMSG', SQLERRM);
463 END IF;
464 ROLLBACK TO start_transaction;
465 -- xERROR:=CZ_UTILS.REPORT(Error_message,1,'CZ_CF_API.delete_configuration',11276);
466 cz_utils.log_report('CZ_CF_API', 'delete_configuration', l_ndebug, error_message, fnd_log.LEVEL_UNEXPECTED);
467 END delete_configuration;
468
469 ------------------------------------------------------------------------------------------
470 PROCEDURE delete_configuration_usage(calling_application_id IN NUMBER,
471 calling_application_ref_key IN NUMBER,
472 Error_message IN OUT NOCOPY VARCHAR2,
473 Return_value IN OUT NOCOPY NUMBER)
474 IS
475 in_calling_application_id NUMBER:=calling_application_id;
476 in_calling_application_ref_key NUMBER:=calling_application_ref_key;
477
478 BEGIN
479 Return_value:=1;
480 Error_message:='';
481 SAVEPOINT start_transaction;
482 DELETE FROM CZ_CONFIG_USAGES WHERE CALLING_APPLICATION_ID=in_calling_application_id AND
483 CALLING_APPLICATION_REF_KEY=in_calling_application_ref_key;
484 EXCEPTION
485 WHEN OTHERS THEN
486 Return_value:=0;
487 Error_message:=SQLERRM;
488 ROLLBACK TO start_transaction;
489 -- xERROR:=CZ_UTILS.REPORT(Error_message,1,'CZ_CF_API: delete configuration usage',11276);
490 cz_utils.log_report('CZ_CF_API', 'delete_configuration_usage', 1, error_message,
491 fnd_log.LEVEL_UNEXPECTED);
492 END delete_configuration_usage;
493 ------------------------------------------------------------------------------------------
494 PROCEDURE update_configuration_usage(calling_application_id IN NUMBER,
495 calling_application_ref_key IN NUMBER,
496 config_hdr_id IN NUMBER,
497 config_rev_nbr IN NUMBER,
498 config_item_id IN NUMBER,
499 uom_code IN VARCHAR2,
500 list_price IN NUMBER,
501 discounted_price IN NUMBER,
502 auto_discount_id IN NUMBER,
503 auto_discount_line_id IN NUMBER,
504 auto_discount_pct IN NUMBER,
505 manual_discount_id IN NUMBER,
506 manual_discount_line_id IN NUMBER,
507 manual_discount_pct IN NUMBER,
508 Error_message IN OUT NOCOPY VARCHAR2,
509 Return_value IN OUT NOCOPY NUMBER)
510
511 IS
512 in_calling_application_id NUMBER:=calling_application_id;
513 in_calling_application_ref_key NUMBER:=calling_application_ref_key;
514 in_config_hdr_id NUMBER:=config_hdr_id;
515 in_config_rev_nbr NUMBER:=config_rev_nbr;
516 in_config_item_id NUMBER:=config_item_id;
517 in_uom_code VARCHAR2(3):=uom_code;
518 in_list_price NUMBER:=list_price;
519 in_discounted_price NUMBER:=discounted_price;
520 in_auto_discount_id NUMBER:=auto_discount_id;
521 in_auto_discount_line_id NUMBER:=auto_discount_line_id;
522 in_auto_discount_pct NUMBER:=auto_discount_pct;
523 in_manual_discount_id NUMBER:=manual_discount_id;
524 in_manual_discount_line_id NUMBER:=manual_discount_line_id;
525 in_manual_discount_pct NUMBER:=manual_discount_pct;
526
527 BEGIN
528 Return_value:=1;
529 Error_message:='';
530 SAVEPOINT start_transaction;
531 UPDATE CZ_CONFIG_USAGES SET
532 LIST_PRICE=in_list_price,
533 AUTO_DISCOUNT_ID=in_auto_discount_id,
534 AUTO_DISCOUNT_LINE_ID=in_auto_discount_line_id,
535 AUTO_DISCOUNT_PCT=in_auto_discount_pct,
536 MANUAL_DISCOUNT_ID=in_manual_discount_id,
537 MANUAL_DISCOUNT_LINE_ID=in_manual_discount_line_id,
538 MANUAL_DISCOUNT_PCT=in_manual_discount_pct,
539 DISCOUNTED_PRICE=in_discounted_price,
540 UOM_CODE=in_uom_code
541 WHERE
542 CALLING_APPLICATION_ID=in_calling_application_id AND
543 CALLING_APPLICATION_REF_KEY=in_calling_application_ref_key AND
544 CONFIG_HDR_ID=in_config_hdr_id AND
545 CONFIG_REV_NBR=in_config_rev_nbr AND
546 CONFIG_ITEM_ID=in_config_item_id;
547 EXCEPTION
548 WHEN OTHERS THEN
549 Return_value:=0;
550 Error_message:=SQLERRM;
551 ROLLBACK TO start_transaction;
552 -- xERROR:=CZ_UTILS.REPORT(Error_message,1,'CZ_CF_API: update configuration usage',11276);
553 cz_utils.log_report('CZ_CF_API', 'update_configuration_usage', 1, error_message,
554 fnd_log.LEVEL_UNEXPECTED);
555 END update_configuration_usage;
556 ------------------------------------------------------------------------------------------
557 PROCEDURE get_config_hdr(p_xml_string IN VARCHAR2,
558 p_config_header_id IN OUT NOCOPY NUMBER,
559 p_config_rev_nbr IN OUT NOCOPY NUMBER)
560 AS
561
562 v_search_string VARCHAR2(200) := 'config_header_id';
563 v_occurence_position NUMBER := 0;
564 v_pattern_found VARCHAR2(5) := 'FALSE';
565 v_occurence_number NUMBER := 1;
566 l_hdr_str VARCHAR2(30);
567 l_rev_str VARCHAR2(30);
568
569 BEGIN
570 WHILE ( INSTR(v_pattern_found,'FALSE') > 0)
571 LOOP
572 v_occurence_position := INSTR(p_xml_string,v_search_string,1,v_occurence_number);
573 IF v_occurence_position = 0 THEN
574 p_config_header_id := 0;
575 p_config_rev_nbr := 0;
576 RETURN;
577 END IF;
578 v_occurence_position := v_occurence_position+16;
579
580 v_pattern_found := 'TRUE';
581 while((ASCII(SUBSTR(p_xml_string,v_occurence_position,1))<48) OR (ASCII(SUBSTR(p_xml_string,v_occurence_position,1))>57))
582 LOOP
583 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
584 v_pattern_found := 'FALSE';
585 EXIT;
586 END IF;
587 v_occurence_position := v_occurence_position+1;
588 END LOOP;
589 v_occurence_number := v_occurence_number+1;
590 END LOOP;
591
592 l_hdr_str := '';
593 while((ASCII(SUBSTR(p_xml_string,v_occurence_position,1))>47) AND (ASCII(SUBSTR(p_xml_string,v_occurence_position,1))<58))
594 LOOP
595 l_hdr_str := l_hdr_str||SUBSTR(p_xml_string,v_occurence_position,1);
596 v_occurence_position := v_occurence_position+1;
597 END LOOP;
598
599 v_pattern_found := 'FALSE';
600 v_search_string := 'config_rev_nbr';
601 v_occurence_number := 1;
602
603 WHILE ( INSTR(v_pattern_found,'FALSE') > 0)
604 LOOP
605 v_occurence_position := INSTR(p_xml_string,v_search_string,1,v_occurence_number);
606 IF v_occurence_position = 0 THEN
607 p_config_header_id := 0;
608 p_config_rev_nbr := 0;
609 RETURN;
610 END IF;
611 v_occurence_position := v_occurence_position+15;
612
613 v_pattern_found := 'TRUE';
614 while((ASCII(SUBSTR(p_xml_string,v_occurence_position,1))<48) OR (ASCII(SUBSTR(p_xml_string,v_occurence_position,1))>57))
615 LOOP
616 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
617 v_pattern_found := 'FALSE';
618 EXIT;
619 END IF;
620 v_occurence_position := v_occurence_position+1;
621 END LOOP;
622 v_occurence_number := v_occurence_number+1;
623 END LOOP;
624
625
626 l_rev_str := '';
627 while((ASCII(SUBSTR(p_xml_string,v_occurence_position,1))>47) AND (ASCII(SUBSTR(p_xml_string,v_occurence_position,1))<58))
628 LOOP
629 l_rev_str := l_rev_str||SUBSTR(p_xml_string,v_occurence_position,1);
630 v_occurence_position := v_occurence_position+1;
631 END LOOP;
632
633 p_config_header_id := to_number(l_hdr_str);
634 p_config_rev_nbr := to_number(l_rev_str);
635 EXCEPTION
636 WHEN OTHERS THEN
637 p_config_header_id := 0;
638 p_config_rev_nbr := 0;
639 END get_config_hdr;
640 ------------------------------------------------------------
641 PROCEDURE append_instance_nbr(p_node_identifier IN VARCHAR2,
642 x_node_identifier OUT NOCOPY VARCHAR2,
643 x_item_depth OUT NOCOPY NUMBER)
644 IS
645 l_instr INTEGER;
646 l_count PLS_INTEGER;
647 l_str cz_config_items.node_identifier%TYPE;
648 l_ecc cz_config_items.node_identifier%TYPE;
649
650 BEGIN
651 l_count := 1;
652 l_ecc := '';
653 l_str := p_node_identifier;
654 l_instr := INSTR(l_str,'-') - 1;
655 WHILE l_instr > 0 LOOP
656 l_ecc := l_ecc||SUBSTR(l_str,1,l_instr)||'|1-';
657 l_str := SUBSTR(l_str,l_instr+2);
658 l_instr := INSTR(l_str,'-') - 1;
659 l_count := l_count + 1;
660 END LOOP;
661 x_node_identifier := l_ecc||l_str||'|1';
662 x_item_depth := l_count;
663 EXCEPTION
664 WHEN OTHERS THEN
665 RAISE;
666 END append_instance_nbr;
667
668 --------------------------------------------------------------------------------
669 PROCEDURE get_ext_comp_code(p_config_hdr_id IN NUMBER
670 ,p_config_rev_nbr IN NUMBER
671 ,p_config_item_id IN NUMBER
672 ,x_ecc_code OUT NOCOPY VARCHAR2
673 ,x_item_depth OUT NOCOPY NUMBER)
674 IS
675 l_ecc cz_config_items.node_identifier%TYPE;
676 l_inventory_item_id NUMBER;
677 l_instance_nbr NUMBER;
678 l_depth PLS_INTEGER;
679 CURSOR ecc_cur(p_config_item_id NUMBER) IS
680 SELECT inventory_item_id, instance_nbr
681 FROM cz_config_items
682 WHERE deleted_flag = '0'
683 AND inventory_item_id IS NOT NULL
684 AND config_hdr_id = p_config_hdr_id
685 AND config_rev_nbr = p_config_rev_nbr
686 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
687 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
688 ORDER BY ROWNUM DESC;
689 BEGIN
690 l_ecc := '';
691 l_depth := 0;
692 OPEN ecc_cur(p_config_item_id);
693 LOOP
694 FETCH ecc_cur INTO l_inventory_item_id,l_instance_nbr;
695 EXIT WHEN ecc_cur%NOTFOUND;
696 SELECT decode(l_instance_nbr, -1,1,0,1,l_instance_nbr) into l_instance_nbr from dual;
697 l_ecc := l_ecc||l_inventory_item_id||'|'||nvl(l_instance_nbr,1)||'-';
698 l_depth := l_depth + 1;
699 END LOOP;
700 CLOSE ecc_cur;
701 l_ecc := RTRIM(l_ecc, '-');
702 x_ecc_code := l_ecc;
703 x_item_depth := l_depth;
704 EXCEPTION
705 WHEN OTHERS THEN
706 CLOSE ecc_cur;
707 RAISE;
708 END get_ext_comp_code;
709
710 ------------------------------------------------------
711 ------procedure that parses the output terminate message for the output
712 ------header,revision and config status
713 PROCEDURE parse_output_xml (p_xml IN LONG,
714 x_valid_config OUT NOCOPY VARCHAR2,
715 x_complete_config OUT NOCOPY VARCHAR2,
716 x_config_header_id OUT NOCOPY NUMBER,
717 x_config_rev_nbr OUT NOCOPY NUMBER,
718 x_return_status OUT NOCOPY VARCHAR2,
719 x_error_message OUT NOCOPY VARCHAR2)
720 IS
721
722 l_valid_config_start_tag VARCHAR2(30) := '<VALID_CONFIGURATION>';
723 l_valid_config_end_tag VARCHAR2(30) := '</VALID_CONFIGURATION>';
724 l_valid_config_start_pos NUMBER;
725 l_valid_config_end_pos NUMBER;
726 l_complete_config_start_tag VARCHAR2(30) := '<COMPLETE_CONFIGURATION>';
727 l_complete_config_end_tag VARCHAR2(30) := '</COMPLETE_CONFIGURATION>';
728 l_complete_config_start_pos NUMBER;
729 l_complete_config_end_pos NUMBER;
730 l_config_header_id_start_tag VARCHAR2(20) := '<CONFIG_HEADER_ID>';
731 l_config_header_id_end_tag VARCHAR2(20) := '</CONFIG_HEADER_ID>';
732 l_config_header_id_start_pos NUMBER;
733 l_config_header_id_end_pos NUMBER;
734 l_config_rev_nbr_start_tag VARCHAR2(20) := '<CONFIG_REV_NBR>';
735 l_config_rev_nbr_end_tag VARCHAR2(20) := '</CONFIG_REV_NBR>';
736 l_config_rev_nbr_start_pos NUMBER;
737 l_config_rev_nbr_end_pos NUMBER;
738 l_config_header_id NUMBER;
739 l_config_rev_nbr NUMBER;
740 l_valid_config VARCHAR2(10);
741 l_complete_config VARCHAR2(10);
742 l_header_id NUMBER;
743 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
744
745 BEGIN
746 l_valid_config_start_pos := INSTR(UPPER(p_xml),l_valid_config_start_tag,1, 1)
747 + length(l_valid_config_start_tag);
748 l_valid_config_end_pos := INSTR(UPPER(p_xml), l_valid_config_end_tag,1, 1) - 1;
749 l_valid_config := SUBSTR(p_xml,l_valid_config_start_pos,
750 l_valid_config_end_pos - l_valid_config_start_pos + 1);
751 l_complete_config_start_pos := INSTR(UPPER(p_xml),
752 l_complete_config_start_tag,1, 1) + length(l_complete_config_start_tag);
753 l_complete_config_end_pos := INSTR(UPPER(p_xml), l_complete_config_end_tag,1, 1) - 1;
754 l_complete_config := SUBSTR( p_xml, l_complete_config_start_pos,
755 l_complete_config_end_pos - l_complete_config_start_pos + 1);
756
757 -- get the latest config_header_id, and rev_nbr
758 l_config_header_id_start_pos := INSTR(UPPER(p_xml),
759 l_config_header_id_start_tag, 1, 1)
760 + length(l_config_header_id_start_tag);
761 l_config_header_id_end_pos := INSTR(UPPER(p_xml), l_config_header_id_end_tag, 1, 1) - 1;
762 l_config_header_id := to_number(SUBSTR(p_xml,l_config_header_id_start_pos,
763 l_config_header_id_end_pos - l_config_header_id_start_pos + 1));
764 l_config_rev_nbr_start_pos := INSTR(UPPER(p_xml), l_config_rev_nbr_start_tag, 1, 1)
765 + length(l_config_rev_nbr_start_tag);
766 l_config_rev_nbr_end_pos := INSTR(UPPER(p_xml), l_config_rev_nbr_end_tag, 1, 1) - 1;
767 l_config_rev_nbr := to_number(SUBSTR(p_xml,l_config_rev_nbr_start_pos,
768 l_config_rev_nbr_end_pos - l_config_rev_nbr_start_pos + 1));
769
770 x_return_status := l_return_status;
771 x_config_header_id := l_config_header_id;
772 x_config_rev_nbr := l_config_rev_nbr;
773 x_complete_config := nvl(l_complete_config, 'FALSE');
774 x_valid_config := nvl(l_valid_config, 'FALSE');
775
776 EXCEPTION
777 WHEN OTHERS THEN
778 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
779 x_error_message := SQLERRM;
780 END Parse_output_xml;
781 -------------------------------------------------------------------
782 -------procedure that checks if the passed in init message
783 -------has a save config behaviour as new_revision.
784 -------if it is new_revision x_param_flag is set to 'YES'
785 PROCEDURE check_if_new_revision(p_init_message IN VARCHAR2,
786 x_param_flag IN OUT NOCOPY VARCHAR2)
787 IS
788
789 l_new_rev_tag VARCHAR2(20) := 'new_revision';
790 l_save_rev_tag VARCHAR2(30) := 'save_config_behavior';
791 l_new_rev_instr NUMBER := 0;
792 l_save_config_instr NUMBER := 0;
793
794 BEGIN
795 x_param_flag := 'YES';
796 IF (p_init_message IS NOT NULL) THEN
797 ------check if 'save_config_behavior' tag exists
798 l_save_config_instr := INSTR(p_init_message,l_save_rev_tag);
799 ----if it does not exist, then default is new revision
800 ----if it exists then check for new_revision
801 IF (l_save_config_instr = 0) THEN
802 x_param_flag := 'YES';
803 ELSE
804 l_new_rev_instr := INSTR(p_init_message,l_new_rev_tag);
805 IF (l_new_rev_instr = 0) THEN
806 x_param_flag := 'NO';
807 END IF;
808 END IF;
809 END IF;
810 END check_if_new_revision;
811
812 -----------------------------------------------
813 ------Procedure that retrieves description and item name
814 ------from mtl_system_items_kfv for an inventory_item_id and organization_id
815 PROCEDURE get_item_description_and_name (p_inventory_item_id IN mtl_system_items.inventory_item_id%TYPE,
816 p_organization_id IN mtl_system_items.organization_id%TYPE,
817 x_description OUT NOCOPY mtl_system_items_kfv.description%TYPE,
818 x_item_name OUT NOCOPY mtl_system_items_kfv.concatenated_segments%TYPE)
819 IS
820
821 BEGIN
822 ----get description,itemname from mtl_system_items
823 SELECT description,concatenated_segments
824 INTO x_description,x_item_name
825 FROM mtl_system_items_kfv
826 WHERE mtl_system_items_kfv.inventory_item_id = p_inventory_item_id
827 AND mtl_system_items_kfv.organization_id = p_organization_id;
828 END;
829
830 --------------------------------------------------
831 ------procedure that logs a delta message to
832 ------cz_config_messages
833 PROCEDURE log_delta_message (p_inventory_item_id IN mtl_system_items.inventory_item_id%TYPE,
834 p_organization_id IN mtl_system_items.organization_id%TYPE ,
835 p_component_code IN cz_config_details_v.component_code%TYPE,
836 p_current_quantity IN NUMBER,
837 p_new_quantity IN NUMBER,
838 p_config_hdr IN NUMBER,
839 p_config_rev IN NUMBER,
840 p_message_name IN fnd_new_messages.message_name%TYPE)
841
842 IS
843
844 l_description mtl_system_items_kfv.description%TYPE;
845 l_new_item_name mtl_system_items_kfv.concatenated_segments%TYPE;
846 v_OracleSequenceIncr NUMBER := 20;
847 l_msg_seq NUMBER := 0;
848 l_delta_message VARCHAR2(2000);
849
850 BEGIN
851 ----get description,itemname from mtl_system_items
852 get_item_description_and_name (p_inventory_item_id,
853 p_organization_id,
854 l_description,
855 l_new_item_name);
856 l_msg_seq := get_next_msg_seq;
857
858 IF ( (p_message_name = ITEM_DELETE_MESSAGE) OR (p_message_name = ITEM_ADD_MESSAGE ) ) THEN
859 l_delta_message := CZ_UTILS.GET_TEXT(p_message_name,
860 'ITEMNAME',l_new_item_name,
861 'QUANTITY',p_current_quantity,
862 'COMPONENTCODE',p_component_code,
863 'DESCRIPTION',l_description);
864 ELSIF (p_message_name = QTY_CHANGE_MESSAGE ) THEN
865 l_delta_message := CZ_UTILS.GET_TEXT(p_message_name,
866 'ITEMNAME',l_new_item_name,
867 'CURRENT_QUANTITY',p_current_quantity,
868 'NEW_QUANTITY',p_new_quantity,
869 'COMPONENTCODE',p_component_code,
870 'DESCRIPTION',l_description );
871 END IF;
872
873 insert into cz_config_messages (config_hdr_id,config_rev_nbr,constraint_type,
874 message,message_seq,deleted_flag)
875 values (p_config_hdr,p_config_rev,'ITEM DELTA',l_delta_message,l_msg_seq, '0');
876 END;
877
878 ------------------------------------------------------
879 ----- changes for batch validation failure processing
880 ----- this block would set the validation status to fail if
881 ----- the config_input_list is empty and validation_status is CONFIG_PROCESSED
882 ------ and if there are configured item changes
883 ------ The changes are logged to cz_config_messages
884 ------@p_config_input_list input list passed to validate proc
885 ------@p_validation_status --- validation status after BV
886 ------@p_init_message --- init message passed by calling application
887 ------@x_config_messages OUTPUT config_messages
888 ------@x_return_status -- validation status is set to 4 if deltas exist
889 PROCEDURE check_deltas( p_init_message IN VARCHAR2,
890 x_config_messages IN OUT NOCOPY CFG_OUTPUT_PIECES,
891 x_return_status IN OUT NOCOPY VARCHAR2)
892 IS
893
894 v_header_id cz_config_hdrs.config_hdr_id%TYPE;
895 v_rev_nbr cz_config_hdrs.config_rev_nbr%TYPE;
896 v_output_cfg_hdr_id cz_config_hdrs.config_hdr_id%TYPE := 0;
897 v_output_cfg_rev_nbr cz_config_hdrs.config_rev_nbr%TYPE := 0;
898 l_prev_item cz_config_items.config_item_id%TYPE := 0;
899 l_prev_rev cz_config_hdrs.config_rev_nbr%TYPE;
900 l_prev_qty NUMBER := 0;
901 l_description mtl_system_items.description%TYPE;
902 l_new_item cz_config_items.config_item_id%TYPE:= 0;
903 l_new_rev cz_config_hdrs.config_rev_nbr%TYPE := 0;
904 l_new_component_code cz_config_details_v.component_code%TYPE;
905 l_new_qty NUMBER := 0;
906 l_new_inventory_item_id mtl_system_items.inventory_item_id%TYPE;
907 l_new_organization_id mtl_system_items.organization_id%TYPE;
908 v_valid_config VARCHAR2(30);
909 v_complete_config VARCHAR2(30);
910 v_parse_status VARCHAR2(1);
911 l_param_value VARCHAR2(3);
912 v_xml_str LONG := NULL;
913 l_config_err_msg VARCHAR2(2000);
914 PARSE_XML_ERROR EXCEPTION;
915 NO_INPUT_HDR_EXCEP EXCEPTION;
916 l_new_item_name cz_config_items.name%TYPE;
917 l_bv_profile VARCHAR2(100);
918 v_parse_message VARCHAR2(2000);
919 l_len NUMBER := 2000;
920 l_delta_exists VARCHAR2(3) := 'NO';
921 l_qty_changed BOOLEAN := FALSE;
922 l_prev_count NUMBER := 0;
923 l_config_true_tag VARCHAR2(30) := '<valid_configuration>true';
924 l_config_false_tag VARCHAR2(30) := '<valid_configuration>false';
925 l_prev_inventory_item_id mtl_system_items.inventory_item_id%TYPE;
926 l_prev_organization_id mtl_system_items.organization_id%TYPE;
927 l_prev_component_code cz_config_details_v.component_code%TYPE;
928
929 CURSOR c_config_delta (p_old_hdr NUMBER,p_old_rev NUMBER,p_new_hdr NUMBER,p_new_rev NUMBER)
930 IS select config_rev_nbr,config_item_id,quantity,
931 component_code,inventory_item_id,organization_id
932 from cz_config_details_v
933 where (config_hdr_id,config_item_id,quantity)
934 IN (
935 (select config_hdr_id,config_item_id,quantity from cz_config_details_v
936 where config_hdr_id = p_old_hdr and config_rev_nbr = p_old_rev
937 minus
938 select config_hdr_id,config_item_id,quantity from cz_config_details_v
939 where config_hdr_id = p_new_hdr and config_rev_nbr = p_new_rev )
940 union
941 (select config_hdr_id,config_item_id,quantity from cz_config_details_v
942 where config_hdr_id = p_new_hdr and config_rev_nbr = p_new_rev
943 minus
944 select config_hdr_id,config_item_id,quantity from cz_config_details_v
945 where config_hdr_id = p_old_hdr and config_rev_nbr = p_old_rev )
946 )
947 and config_rev_nbr IN (p_old_rev, p_new_rev)
948 ORDER BY config_item_id,config_hdr_id,config_rev_nbr;
949
950 BEGIN
951 x_return_status := FND_API.G_RET_STS_SUCCESS;
952
953 FOR I IN 1..1
954 LOOP
955
956 ----check profile setting
957 l_bv_profile := FND_PROFILE.VALUE('CZ_BV_DELTA');
958
959 IF (NVL(UPPER(l_bv_profile), 'N') = 'N') THEN
960 EXIT;
961 END IF;
962
963 -----check if the init message contains the parameter
964 -----save_config_behavior = new revision
965 check_if_new_revision(p_init_message,l_param_value);
966
967 IF (l_param_value <> 'YES') THEN
968 EXIT;
969 END IF;
970
971 -----get previous config_hdr_id and config_rev_nbr
972 get_config_hdr(p_init_message,v_header_id,v_rev_nbr);
973
974 -----if the init message does not contain input header
975 -----then no need to compute deltas and check_delta must return a
976 -----status of SUCCESS
977 IF (v_header_id = 0) THEN
978 RAISE NO_INPUT_HDR_EXCEP;
979 END IF;
980
981 ------get new config_hdr_id and revision
982 IF (x_config_messages.COUNT > 0) THEN
983 FOR xmlStr IN x_config_messages.FIRST..x_config_messages.LAST
984 LOOP
985 v_xml_str := v_xml_str||x_config_messages(xmlStr);
986 END LOOP;
987 END IF;
988
989 parse_output_xml (v_xml_str ,
990 v_valid_config ,
991 v_complete_config ,
992 v_output_cfg_hdr_id ,
993 v_output_cfg_rev_nbr ,
994 v_parse_status ,
995 v_parse_message) ;
996
997 ----if error in parsing xml raise an exception
998 IF (v_parse_status <> FND_API.G_RET_STS_SUCCESS) THEN
999 RAISE PARSE_XML_ERROR;
1000 END IF;
1001
1002 IF (UPPER(v_valid_config) NOT IN ('TRUE', 'Y')) THEN
1003 EXIT;
1004 END IF;
1005
1006 OPEN c_config_delta (v_header_id,v_rev_nbr,v_output_cfg_hdr_id,v_output_cfg_rev_nbr);
1007 LOOP
1008 FETCH c_config_delta INTO l_new_rev,l_new_item,l_new_qty,l_new_component_code,
1009 l_new_inventory_item_id,l_new_organization_id;
1010 EXIT WHEN(c_config_delta%NOTFOUND);
1011
1012 IF ( l_qty_changed = FALSE ) THEN
1013
1014 IF ( (l_prev_item <> 0) AND (l_prev_item <> l_new_item) ) THEN
1015
1016 IF (l_prev_rev = v_rev_nbr) THEN
1017 --item deleted
1018 l_delta_exists := 'YES';
1019
1020 log_delta_message (l_prev_inventory_item_id,
1021 l_prev_organization_id,
1022 l_prev_component_code,
1023 l_prev_qty,
1024 NULL,
1025 v_output_cfg_hdr_id,
1026 v_output_cfg_rev_nbr,
1027 ITEM_DELETE_MESSAGE);
1028
1029 ELSIF (l_prev_rev = v_output_cfg_rev_nbr) THEN
1030 --- item added
1031 l_delta_exists := 'YES';
1032
1033 log_delta_message (l_prev_inventory_item_id,
1034 l_prev_organization_id,
1035 l_prev_component_code,
1036 l_prev_qty,
1037 NULL,
1038 v_output_cfg_hdr_id,
1039 v_output_cfg_rev_nbr,
1040 ITEM_ADD_MESSAGE);
1041
1042 END IF;
1043
1044 ELSIF (l_prev_item = l_new_item) THEN
1045 ---qty changed
1046 l_qty_changed := TRUE;
1047 l_delta_exists := 'YES';
1048
1049 log_delta_message (l_prev_inventory_item_id,
1050 l_prev_organization_id,
1051 l_new_component_code,
1052 l_prev_qty,
1053 l_new_qty,
1054 v_output_cfg_hdr_id,
1055 v_output_cfg_rev_nbr,
1056 QTY_CHANGE_MESSAGE );
1057 END IF;
1058
1059 ELSE
1060 l_qty_changed := FALSE;
1061 END IF;
1062 l_prev_item := l_new_item; l_prev_rev := l_new_rev; l_prev_qty := l_new_qty;
1063 l_prev_inventory_item_id := l_new_inventory_item_id; l_prev_organization_id := l_new_organization_id;
1064 l_prev_component_code := l_new_component_code;
1065 END LOOP;
1066 CLOSE c_config_delta ;
1067
1068 ----this block of code process the last record if add or delete item
1069 IF ( l_qty_changed = FALSE ) THEN
1070 IF (l_new_rev = v_rev_nbr) THEN
1071 --item deleted
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_DELETE_MESSAGE);
1082 ELSIF (l_new_rev = v_output_cfg_rev_nbr) THEN
1083 --- item added
1084 l_delta_exists := 'YES';
1085
1086 log_delta_message (l_prev_inventory_item_id,
1087 l_prev_organization_id,
1088 l_prev_component_code,
1089 l_prev_qty,
1090 NULL,
1091 v_output_cfg_hdr_id,
1092 v_output_cfg_rev_nbr,
1093 ITEM_ADD_MESSAGE);
1094 END IF;
1095 END IF;
1096
1097 IF (l_delta_exists = 'YES') THEN
1098 v_xml_str := REPLACE(v_xml_str,l_config_true_tag,l_config_false_tag);
1099 x_config_messages.DELETE;
1100 FOR I IN 1..LENGTH(v_xml_str)
1101 LOOP
1102 EXIT WHEN LENGTH(v_xml_str) = 0;
1103 IF (LENGTH(v_xml_str) <= 2000) THEN
1104 l_len := LENGTH(v_xml_str);
1105 ELSE
1106 l_len := 2000;
1107 END IF;
1108 x_config_messages(i) := substr(v_xml_str,1,l_len);
1109 v_xml_str := substr(v_xml_str,l_len + 1);
1110 END LOOP;
1111 END IF;
1112 END LOOP;
1113 COMMIT;
1114 EXCEPTION
1115 WHEN NO_INPUT_HDR_EXCEP THEN
1116 x_return_status := FND_API.G_RET_STS_SUCCESS;
1117 WHEN PARSE_XML_ERROR THEN
1118 ROLLBACK;
1119 -- l_report_status := CZ_UTILS.REPORT (v_parse_message,1,'ITEM DELTA',1);
1120 cz_utils.log_report('CZ_CF_API', 'check_deltas', null, v_parse_message,
1121 fnd_log.LEVEL_ERROR);
1122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1123 WHEN OTHERS THEN
1124 ROLLBACK;
1125 l_config_err_msg := SQLERRM;
1126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1127 -- l_report_status := CZ_UTILS.REPORT (l_config_err_msg,1,'ITEM DELTA',1);
1128 cz_utils.log_report('CZ_CF_API', 'check_deltas', null, l_config_err_msg,
1129 fnd_log.LEVEL_UNEXPECTED);
1130 END check_deltas;
1131 -------------------------------------------------------------------------
1132 FUNCTION batchurlencode(p_str varchar2)
1133 RETURN VARCHAR2
1134 IS
1135
1136 l_tmp varchar2(100);
1137 l_hex varchar2(16) default '0123456789ABCDEF';
1138 l_num number;
1139 l_bad varchar2(100) default ' >%}\~];?@&<#{|^[`/:=$+''"'; l_char char(1);
1140
1141 begin
1142
1143 IF (p_str is null) THEN
1144 return null;
1145 END IF;
1146
1147 FOR I IN 1 .. length(p_str)
1148 LOOP
1149 l_char := substr(p_str, i, 1);
1150 IF (instr(l_bad, l_char) ) > 0 THEN
1151 l_num := ascii(l_char);
1152 l_tmp := l_tmp || '%' || substr(l_hex, mod(trunc (l_num / 16), 16) + 1, 1)
1153 || substr(l_hex, mod(l_num, 16) + 1, 1); else l_tmp := l_tmp || l_char;
1154 END IF;
1155 END LOOP;
1156
1157 RETURN l_tmp;
1158
1159 end;
1160
1161 -----------------------------------------
1162 -----This procedure is used as a workaround for bug# 2687938 which is
1163 -----utl_http_request_failed error from network API during batch validation
1164 ------although the logs show that the validation was successful
1165
1166 -------Changes to this procedure is made for SSL implementation
1167 -------Changes are implemented as suggested in bug# 3594440, 3785732,3785687
1168
1169 PROCEDURE return_html_pieces(FinalURL IN VARCHAR2,
1170 config_messages IN OUT NOCOPY CFG_OUTPUT_PIECES)
1171 IS
1172 PRAGMA AUTONOMOUS_TRANSACTION;
1173 l_ssl_profile_option VARCHAR2(2000); ----SSL profile option value, if ssl then 'https'
1174 l_wallet_path VARCHAR2(2000); ----directory path of the wallet
1175 l_wallet_passwd VARCHAR2(2000) := NULL; ---- password is not necessary for the default wallet
1176
1177 BEGIN
1178 l_ssl_profile_option := FND_PROFILE.VALUE('APPS_SERVLET_AGENT');
1179 l_wallet_path := FND_PROFILE.VALUE('FND_DB_WALLET_DIR');
1180 l_wallet_path := 'file:'||l_wallet_path ;
1181
1182 IF (transferTimeout IS NOT NULL AND defaultTimeout IS NOT NULL) THEN
1183 EXECUTE IMMEDIATE 'BEGIN UTL_HTTP.SET_TRANSFER_TIMEOUT(:1); END;' USING IN transferTimeout;
1184 END IF;
1185
1186 -----if the l_ssl_profile_option is SSL then
1187 -----pass in the wallet path and wallet passwd
1188 -----otherwise pass in the URL only
1189 IF (UPPER(l_ssl_profile_option) LIKE ('%HTTPS%')) THEN
1190 config_messages := UTL_HTTP.request_pieces(url => FinalURL,
1191 wallet_path => l_wallet_path,
1192 wallet_password => l_wallet_passwd);
1193 ELSE
1194 config_messages := UTL_HTTP.request_pieces(url => FinalURL);
1195 END IF;
1196
1197 IF (transferTimeout IS NOT NULL AND defaultTimeout IS NOT NULL) THEN
1198 EXECUTE IMMEDIATE 'BEGIN UTL_HTTP.SET_TRANSFER_TIMEOUT(:1); END;' USING IN defaultTimeout;
1199 END IF;
1200 COMMIT;
1201 EXCEPTION
1202 WHEN OTHERS THEN
1203 ROLLBACK;
1204 RAISE;
1205 END return_html_pieces;
1206
1207 --------------------------------------------------------------------------------
1208 procedure delete_bv_records(p_pseudo_hdr_id IN NUMBER
1209 ,p_check_db_setting IN BOOLEAN
1210 ,p_delete_ext_attr IN BOOLEAN)
1211 IS
1212 l_no_config_del cz_db_settings.value%TYPE;
1213 BEGIN
1214 l_no_config_del := 'NO';
1215 IF p_check_db_setting THEN
1216 BEGIN
1217 SELECT upper(value) INTO l_no_config_del
1218 FROM cz_db_settings
1219 WHERE setting_id = 'BatchValConfigInputDelete';
1220 EXCEPTION
1221 WHEN NO_DATA_FOUND THEN
1222 NULL;
1223 END;
1224 END IF;
1225
1226 IF l_no_config_del = 'YES' THEN
1227 RETURN;
1228 END IF;
1229
1230 IF p_delete_ext_attr THEN
1231 DELETE FROM CZ_CONFIG_EXT_ATTRIBUTES
1232 WHERE config_hdr_id = p_pseudo_hdr_id AND config_rev_nbr = 1;
1233 END IF;
1234 DELETE FROM CZ_CONFIG_ITEMS
1235 WHERE config_hdr_id = p_pseudo_hdr_id AND config_rev_nbr = 1;
1236 DELETE FROM CZ_CONFIG_HDRS
1237 WHERE config_hdr_id = p_pseudo_hdr_id AND config_rev_nbr = 1;
1238 COMMIT;
1239 END delete_bv_records;
1240
1241 -------------------------------------------------------------------------------- pvt
1242 procedure validate(p_pseudo_hdr_id IN NUMBER
1243 ,p_url IN VARCHAR2
1244 ,p_init_msg IN VARCHAR2
1245 ,p_validation_type IN VARCHAR2
1246 ,x_validation_status OUT NOCOPY NUMBER
1247 ,x_config_xml_msg OUT NOCOPY CFG_OUTPUT_PIECES
1248 ,v_detailed_error_message OUT NOCOPY varchar2
1249 )
1250 IS
1251 l_url VARCHAR2(32767);
1252
1253 -- XML building blocks:
1254 l_xml_message_header VARCHAR2(40) := '?XMLmsg=';
1255 l_batch_validate_open VARCHAR2(80) := '<batch_validate>';
1256 l_batch_validate_close VARCHAR2(40) := '</batch_validate>';
1257 l_config_inputs_open VARCHAR2(40) := '<config_inputs>';
1258 l_config_inputs_close VARCHAR2(40) := '</config_inputs>';
1259 l_config_header_id_open VARCHAR2(40) := '<config_header_id>';
1260 l_config_header_id_close VARCHAR2(40) := '</config_header_id>';
1261 l_config_rev_nbr_open VARCHAR2(40) := '<config_rev_nbr>';
1262 l_config_rev_nbr_close VARCHAR2(40) := '</config_rev_nbr>';
1263 l_terminate_open VARCHAR2(40) := '<terminate>';
1264 l_config_info_str VARCHAR2(250):= '';
1265 detailed_excp_flag boolean;
1266 -- n0 integer; n1 integer; n2 integer; msg varchar2(255);
1267
1268 BEGIN
1269 UTL_HTTP.get_detailed_excp_support (detailed_excp_flag);
1270
1271 IF detailed_excp_flag<>true
1272 THEN
1273 UTL_HTTP.set_detailed_excp_support(enable =>TRUE);
1274 END IF;
1275
1276 IF p_pseudo_hdr_id IS NOT NULL THEN
1277 l_config_info_str := l_config_header_id_open || TO_CHAR(p_pseudo_hdr_id)
1278 || l_config_header_id_close || l_config_rev_nbr_open
1279 || '1' || l_config_rev_nbr_close;
1280 END IF;
1281
1282 -- Backdoor for providing an alternate URL through a db setting
1283 BEGIN
1284 l_url := get_db_setting('ORAAPPS_INTEGRATE','ALTBATCHVALIDATEURL');
1285 EXCEPTION
1286 WHEN OTHERS THEN
1287 l_url := p_url;
1288 END;
1289
1290 -- append validation type to batch validate tag
1291 IF p_validation_type IS NULL OR p_validation_type = CZ_API_PUB.VALIDATE_ORDER THEN
1292 l_batch_validate_open := batchurlencode('<batch_validate validation_type="validate_order">');
1293
1294 ELSIF (p_validation_type = CZ_API_PUB.VALIDATE_FULFILLMENT) THEN
1295 l_batch_validate_open := batchurlencode('<batch_validate validation_type="validate_fulfillment">');
1296 ELSIF (p_validation_type = CZ_API_PUB.INTERACTIVE) THEN
1297 l_batch_validate_open := batchurlencode('<batch_validate validation_type="interactive">');
1298 ELSE
1299 l_batch_validate_open := batchurlencode('<batch_validate validation_type="validate_return">');
1300 END IF;
1301
1302 l_url := l_url || l_xml_message_header || l_batch_validate_open || p_init_msg;
1303
1304 IF(l_config_info_str IS NOT NULL)THEN
1305
1306 l_url := l_url || l_config_inputs_open || l_config_info_str || l_config_inputs_close;
1307 END IF;
1308
1309 l_url := l_url || l_batch_validate_close;
1310
1311 /*
1312 n0 := 1;
1313 n1 := instr(l_url, '</', n0);
1314 WHILE n1 > 0 LOOP
1315 n2 := instr(l_url, '>', n1) + 1;
1316 msg := substr(l_url, n0, (n2-n0));
1317 dbms_output.put_line(msg);
1318 n0 := n2;
1319 n1 := instr(l_url, '</', n0);
1320 END LOOP;
1321 IF n0 < length(l_url) THEN
1322 msg := substr(l_url, n0);
1323 dbms_output.put_line(msg);
1324 END IF;
1325 */
1326 IF (LENGTH(l_url) > INIT_MESSAGE_LIMIT) THEN
1327 ROLLBACK;
1328 x_validation_status := INIT_TOO_LONG;
1329 RETURN;
1330 END IF;
1331 COMMIT; -- pseudo config recs
1332
1333 return_html_pieces(l_url, x_config_xml_msg);
1334 IF (x_config_xml_msg.COUNT = 0) THEN
1335 x_validation_status := CONFIG_EXCEPTION;
1336 RETURN;
1337 -- RAISE ZERO_RESPONSE_LENGTH;
1338 END IF;
1339
1340 IF (INSTR(x_config_xml_msg(x_config_xml_msg.FIRST),l_terminate_open)<>0) THEN
1341 x_validation_status := CONFIG_PROCESSED;
1342 ELSE
1343 x_validation_status := CONFIG_PROCESSED_NO_TERMINATE;
1344 END IF;
1345
1346 EXCEPTION
1347 WHEN UTL_HTTP.INIT_FAILED THEN
1348 v_detailed_error_message:=' SQLCODE:'||UTL_HTTP.GET_DETAILED_SQLCODE||' ERROR:'||UTL_HTTP.GET_DETAILED_SQLERRM;
1349 x_validation_status := UTL_HTTP_INIT_FAILED;
1350
1351 IF detailed_excp_flag<>true
1352 THEN
1353 UTL_HTTP.set_detailed_excp_support(enable =>FALSE);
1354 END IF;
1355
1356 WHEN UTL_HTTP.REQUEST_FAILED OR UTL_HTTP.BAD_ARGUMENT
1357 OR UTL_HTTP.BAD_URL OR UTL_HTTP.PROTOCOL_ERROR
1358 OR UTL_HTTP.UNKNOWN_SCHEME OR UTL_HTTP.HEADER_NOT_FOUND
1359 OR UTL_HTTP.END_OF_BODY OR UTL_HTTP.ILLEGAL_CALL
1360 OR UTL_HTTP.HTTP_CLIENT_ERROR OR UTL_HTTP.HTTP_SERVER_ERROR
1361 OR UTL_HTTP.TOO_MANY_REQUESTS OR UTL_HTTP.PARTIAL_MULTIBYTE_CHAR
1362 OR UTL_HTTP.TRANSFER_TIMEOUT THEN
1363 v_detailed_error_message:=' SQLCODE:'||UTL_HTTP.GET_DETAILED_SQLCODE||' ERROR:'||UTL_HTTP.GET_DETAILED_SQLERRM;
1364 x_validation_status:=UTL_HTTP_REQUEST_FAILED;
1365
1366 IF detailed_excp_flag<>true
1367 THEN
1368 UTL_HTTP.set_detailed_excp_support(enable =>FALSE);
1369 END IF;
1370
1371 WHEN OTHERS THEN
1372 IF detailed_excp_flag<>true
1373 THEN
1374 UTL_HTTP.set_detailed_excp_support(enable =>FALSE);
1375 END IF;
1376
1377 IF sqlcode='-12545'
1378 then
1379 x_validation_status:=UTL_HTTP_REQUEST_FAILED;
1380 ELSE
1381 RAISE;
1382 END IF;
1383 END validate; -- pvt
1384
1385 --------------------------------------------------------------------------------
1386
1387 procedure validate(p_api_version IN NUMBER
1388 ,p_config_item_tbl IN config_item_tbl_type
1389 ,p_config_ext_attr_tbl IN config_ext_attr_tbl_type
1390 ,p_url IN VARCHAR2
1391 ,p_init_msg IN VARCHAR2
1392 ,p_validation_type IN VARCHAR2
1393 ,x_config_xml_msg OUT NOCOPY CFG_OUTPUT_PIECES
1394 ,x_return_status OUT NOCOPY VARCHAR2
1395 ,x_msg_count OUT NOCOPY NUMBER
1396 ,x_msg_data OUT NOCOPY VARCHAR2
1397 )
1398 IS
1399 PRAGMA AUTONOMOUS_TRANSACTION;
1400
1401 l_api_version CONSTANT NUMBER := 1.0;
1402 l_api_name CONSTANT VARCHAR2(30) := 'validate:new';
1403 l_miss_num CONSTANT INTEGER := -2147483648; -- java's Integer.MIN_VALUE
1404 l_nDebug PLS_INTEGER;
1405 l_idx PLS_INTEGER;
1406 l_msg VARCHAR2(1000);
1407 l_start INTEGER;
1408 l_end INTEGER;
1409
1410 l_log_stmt BOOLEAN;
1411 l_has_item BOOLEAN;
1412 l_has_attr BOOLEAN;
1413
1414 l_url VARCHAR2(32767);
1415 l_validation_type VARCHAR2(1);
1416 l_validation_status INTEGER;
1417 l_pseudo_hdr_id cz_config_hdrs.config_hdr_id%TYPE;
1418 l_operation_code VARCHAR2(40);
1419 l_rec1_seq INTEGER;
1420 l_config_hdr_id cz_config_hdrs.config_hdr_id%TYPE;
1421 l_config_rev_nbr cz_config_hdrs.config_rev_nbr%TYPE;
1422 l_ext_comp_code cz_config_items.node_identifier%TYPE;
1423 l_item_depth INTEGER;
1424
1425 l_upd_item_map NUMBER_TBL_INDEXBY_CHAR_TYPE;
1426 l_item_seq_map NUMBER_TBL_INDEXBY_TYPE;
1427 l_config_item_tbl NUMBER_TBL_INDEXBY_TYPE;
1428 l_ecc_tbl str1200_tbl_type;
1429 l_seq_nbr_tbl NUMBER_TBL_INDEXBY_TYPE;
1430 l_rec_seq_tbl NUMBER_TBL_INDEXBY_TYPE;
1431 l_operation_tbl NUMBER_TBL_INDEXBY_TYPE;
1432 l_quantity_tbl NUMBER_TBL_INDEXBY_TYPE;
1433 l_instance_name_tbl str255_tbl_type;
1434 l_loc_id_tbl NUMBER_TBL_INDEXBY_TYPE;
1435 l_loc_type_code_tbl str255_tbl_type;
1436 l_attr_nam_tbl str255_tbl_type;
1437 l_attr_grp_tbl str255_tbl_type;
1438 l_attr_val_tbl str255_tbl_type;
1439 v_detailed_error_message varchar2(2000);
1440
1441 procedure set_message(p_msg_name IN VARCHAR2
1442 ,p_token_name1 IN VARCHAR2
1443 ,p_token_value1 IN VARCHAR2
1444 ,p_token_name2 IN VARCHAR2
1445 ,p_token_value2 IN VARCHAR2
1446 ,p_token_name3 IN VARCHAR2
1447 ,p_token_value3 IN VARCHAR2
1448 ,p_token_name4 IN VARCHAR2
1449 ,p_token_value4 IN VARCHAR2
1450 ) IS
1451 BEGIN
1452 fnd_message.set_name('CZ', p_msg_name);
1453 fnd_message.set_token(p_token_name1, p_token_value1);
1454 fnd_message.set_token(p_token_name2, p_token_value2);
1455 fnd_message.set_token(p_token_name3, p_token_value3);
1456 fnd_message.set_token(p_token_name4, p_token_value4);
1457 fnd_msg_pub.add;
1458 END set_message;
1459
1460 BEGIN
1461 l_nDebug := 1;
1462
1463 IF (NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME)) THEN
1464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1465 END IF;
1466
1467 l_validation_type := p_validation_type;
1468 IF l_validation_type IS NULL THEN
1469 l_validation_type := CZ_API_PUB.VALIDATE_ORDER;
1470 ELSIF l_validation_type NOT IN
1471 (CZ_API_PUB.VALIDATE_ORDER, CZ_API_PUB.VALIDATE_FULFILLMENT,
1472 CZ_API_PUB.INTERACTIVE, CZ_API_PUB.VALIDATE_RETURN) THEN
1473 fnd_message.set_name('CZ', 'CZ_BV_INVALID_TYPE');
1474 fnd_message.set_token('TYPE', l_validation_type);
1475 fnd_msg_pub.add;
1476 RAISE FND_API.G_EXC_ERROR;
1477 END IF;
1478
1479 IF p_init_msg IS NULL OR length(p_init_msg) = 0 THEN
1480 fnd_message.set_name('CZ', 'CZ_BV_NULL_INITMSG');
1481 fnd_msg_pub.add;
1482 RAISE FND_API.G_EXC_ERROR;
1483 END IF;
1484
1485 l_url := p_url;
1486 IF l_url IS NULL THEN
1487 l_url := FND_PROFILE.Value('CZ_UIMGR_URL');
1488 END IF;
1489
1490 l_log_stmt := FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1491 IF l_log_stmt AND p_init_msg IS NOT NULL THEN
1492 CZ_UTILS.log_report(G_PKG_NAME,l_api_name,l_nDebug,'URL='||l_url,FND_LOG.LEVEL_STATEMENT);
1493 l_start := 1;
1494 l_end := instr(p_init_msg, '</param>', l_start);
1495 WHILE l_end > 0 LOOP
1496 l_end := l_end + 8;
1497 l_msg := substr(p_init_msg, l_start, (l_end - l_start));
1498 -- dbms_output.put_line(l_msg);
1499 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_STATEMENT);
1500 l_start := l_end;
1501 l_end := instr(p_init_msg, '</param>', l_start);
1502 END LOOP;
1503 IF l_start < length(p_init_msg) THEN
1504 l_msg := substr(p_init_msg, l_start);
1505 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_STATEMENT);
1506 -- dbms_output.put_line(l_msg);
1507 END IF;
1508 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug,
1509 'Validate type: ' || p_validation_type, FND_LOG.LEVEL_STATEMENT);
1510 END IF;
1511
1512 l_nDebug := 2;
1513 l_has_item := p_config_item_tbl IS NOT NULL AND p_config_item_tbl.COUNT > 0;
1514 l_has_attr := p_config_ext_attr_tbl IS NOT NULL AND p_config_ext_attr_tbl.COUNT > 0;
1515 IF l_has_item OR l_has_attr THEN
1516 -- create pseudo hdr rec
1517 l_pseudo_hdr_id := next_config_hdr_id;
1518 INSERT INTO CZ_CONFIG_HDRS (config_hdr_id
1519 ,config_rev_nbr
1520 ,name
1521 ,effective_usage_id
1522 ,component_instance_type
1523 ,model_instantiation_type
1524 ,CONFIG_DELTA_SPEC
1525 ,deleted_flag
1526 ,HAS_FAILURES
1527 )
1528 VALUES (l_pseudo_hdr_id
1529 ,1
1530 ,'new batch'
1531 ,ANY_USAGE_ID
1532 ,ROOT
1533 ,BV_MODEL_TYPE
1534 ,0
1535 ,'0'
1536 ,'0'
1537 );
1538
1539 IF l_log_stmt THEN
1540 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, 'pseudo hdr=' ||
1541 l_pseudo_hdr_id, FND_LOG.LEVEL_STATEMENT);
1542 END IF;
1543
1544 l_nDebug := 3;
1545 l_idx := 0;
1546 -- Note: if supporting new config in the future, then needs either to move the
1547 -- get_config_hdr call or to chg the method to handle no hdr info in init msg
1548 get_config_hdr(p_init_msg, l_config_hdr_id, l_config_rev_nbr);
1549
1550 l_nDebug := 4;
1551 IF l_has_item THEN
1552 l_rec1_seq := p_config_item_tbl(p_config_item_tbl.FIRST).sequence_nbr;
1553 IF l_rec1_seq = FND_API.G_MISS_NUM THEN
1554 l_rec1_seq := NULL;
1555 END IF;
1556
1557 FOR i IN p_config_item_tbl.FIRST..p_config_item_tbl.LAST
1558 LOOP
1559 IF l_log_stmt THEN
1560 l_msg := 'item rec ' || i || ':id=' || p_config_item_tbl(i).config_item_id ||
1561 ',seq=' || p_config_item_tbl(i).sequence_nbr ||
1562 ',opc=' || p_config_item_tbl(i).operation ||
1563 ',qty=' || p_config_item_tbl(i).quantity ||
1564 ',nam=' || p_config_item_tbl(i).instance_name ||
1565 ',lid=' || p_config_item_tbl(i).location_id ||
1566 ',ltc=' || p_config_item_tbl(i).location_type_code;
1567 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_STATEMENT);
1568 END IF;
1569
1570 IF p_config_item_tbl(i).config_item_id IS NULL OR
1571 p_config_item_tbl(i).config_item_id = FND_API.G_MISS_NUM THEN
1572 set_message('CZ_BV_NULL_VAL', 'COLUMN', 'CONFIG_ITEM_ID', 'TYPE', 'ITEM',
1573 'IDX', i, 'SEQ', p_config_item_tbl(i).sequence_nbr);
1574 RAISE FND_API.G_EXC_ERROR;
1575 END IF;
1576
1577 IF p_config_item_tbl(i).operation IS NULL OR
1578 p_config_item_tbl(i).operation = FND_API.G_MISS_NUM THEN
1579 set_message('CZ_BV_NULL_VAL', 'COLUMN', 'OPERATION', 'TYPE', 'ITEM',
1580 'IDX', i, 'SEQ', p_config_item_tbl(i).sequence_nbr);
1581 RAISE FND_API.G_EXC_ERROR;
1582 ELSIF p_config_item_tbl(i).operation NOT IN (BV_OPERATION_UPDATE,BV_OPERATION_DELETE) THEN
1583 IF p_config_item_tbl(i).operation = BV_OPERATION_INSERT THEN
1584 l_operation_code := 'INSERT';
1585 ELSIF p_config_item_tbl(i).operation = BV_OPERATION_REVERT THEN
1586 l_operation_code := 'REVERT';
1587 ELSE
1588 l_operation_code := to_char(p_config_item_tbl(i).operation);
1589 END IF;
1590 fnd_message.set_name('CZ', 'CZ_BV_INVALID_OP');
1591 fnd_message.set_token('CODE', l_operation_code);
1592 fnd_message.set_token('IDX', i);
1593 fnd_message.set_token('SEQ', p_config_item_tbl(i).sequence_nbr);
1594 fnd_msg_pub.add;
1595 RAISE FND_API.G_EXC_ERROR;
1596 ELSIF p_config_item_tbl(i).operation = BV_OPERATION_UPDATE AND
1597 p_config_item_tbl(i).instance_name = FND_API.G_MISS_CHAR THEN
1598 set_message('CZ_BV_NULL_VAL', 'COLUMN', 'INSTANCE_NAME', 'TYPE', 'ITEM',
1599 'IDX', i, 'SEQ', p_config_item_tbl(i).sequence_nbr);
1600 RAISE FND_API.G_EXC_ERROR;
1601 END IF;
1602
1603 IF l_rec1_seq IS NULL AND p_config_item_tbl(i).sequence_nbr IS NOT NULL OR
1604 l_rec1_seq IS NOT NULL AND (p_config_item_tbl(i).sequence_nbr IS NULL OR
1605 p_config_item_tbl(i).sequence_nbr=FND_API.G_MISS_NUM) THEN
1606 set_message('CZ_BV_NULL_VAL', 'COLUMN', 'SEQUENCE_NBR', 'TYPE', 'ITEM',
1607 'IDX', i, 'SEQ', p_config_item_tbl(i).sequence_nbr);
1608 RAISE FND_API.G_EXC_ERROR;
1609 ELSIF p_config_item_tbl(i).sequence_nbr IS NOT NULL THEN
1610 IF l_item_seq_map.exists(p_config_item_tbl(i).sequence_nbr) THEN
1611 fnd_message.set_name('CZ', 'CZ_BV_DUP_SEQ');
1612 fnd_message.set_token('SEQ', p_config_item_tbl(i).sequence_nbr);
1613 fnd_msg_pub.add;
1614 RAISE FND_API.G_EXC_ERROR;
1615 ELSE
1616 l_item_seq_map(p_config_item_tbl(i).sequence_nbr) := i;
1617 END IF;
1618 END IF;
1619
1620 -- construct extended component code
1621 get_ext_comp_code(l_config_hdr_id, l_config_rev_nbr,
1622 p_config_item_tbl(i).config_item_id,
1623 l_ext_comp_code, l_item_depth);
1624
1625 IF l_ext_comp_code IS NULL OR length(l_ext_comp_code) = 0 THEN
1626 fnd_message.set_name('CZ', 'CZ_BV_INVALID_ITEM');
1627 fnd_message.set_token('HDR', l_config_hdr_id);
1628 fnd_message.set_token('REV', l_config_rev_nbr);
1629 fnd_message.set_token('ID', p_config_item_tbl(i).config_item_id);
1630 fnd_message.set_token('TYPE', 'ITEM');
1631 fnd_message.set_token('IND', i);
1632 fnd_message.set_token('SEQ', p_config_item_tbl(i).sequence_nbr);
1633 fnd_msg_pub.add;
1634 RAISE FND_API.G_EXC_ERROR;
1635 ELSIF p_config_item_tbl(i).operation = BV_OPERATION_UPDATE THEN
1636 l_upd_item_map(p_config_item_tbl(i).config_item_id) := p_config_item_tbl(i).config_item_id;
1637 END IF;
1638
1639 l_idx := l_idx + 1;
1640 l_config_item_tbl(l_idx) := p_config_item_tbl(i).config_item_id;
1641 l_ecc_tbl(l_idx) := l_ext_comp_code;
1642 l_seq_nbr_tbl(l_idx) := NVL(p_config_item_tbl(i).sequence_nbr, l_item_depth);
1643 l_operation_tbl(l_idx) := p_config_item_tbl(i).operation;
1644 l_quantity_tbl(l_idx) := p_config_item_tbl(i).quantity;
1645 IF l_quantity_tbl(l_idx) = FND_API.G_MISS_NUM THEN
1646 l_quantity_tbl(l_idx) := l_miss_num;
1647 END IF;
1648 l_instance_name_tbl(l_idx) := p_config_item_tbl(i).instance_name;
1649 l_loc_id_tbl(l_idx) := p_config_item_tbl(i).location_id;
1650 IF l_loc_id_tbl(l_idx) = FND_API.G_MISS_NUM THEN
1651 l_loc_id_tbl(l_idx) := l_miss_num;
1652 END IF;
1653 l_loc_type_code_tbl(l_idx) := p_config_item_tbl(i).location_type_code;
1654 l_rec_seq_tbl(l_idx) := i;
1655
1656 END LOOP;
1657
1658 l_nDebug := 5;
1659 FORALL i IN l_config_item_tbl.first .. l_config_item_tbl.lAST
1660 INSERT INTO CZ_CONFIG_ITEMS
1661 (config_hdr_id
1662 ,config_rev_nbr
1663 ,config_item_id
1664 ,sequence_nbr
1665 ,value_type_code
1666 ,node_identifier
1667 ,item_num_val
1668 ,INSTANCE_HDR_ID
1669 ,INSTANCE_REV_NBR
1670 ,COMPONENT_INSTANCE_TYPE
1671 ,CONFIG_DELTA
1672 ,name
1673 ,location_id
1674 ,location_type_code
1675 )
1676 VALUES (l_pseudo_hdr_id
1677 ,1
1678 ,l_config_item_tbl(i)
1679 ,l_seq_nbr_tbl(i)
1680 ,l_operation_tbl(i)
1681 ,l_ecc_tbl(i)
1682 ,l_quantity_tbl(i)
1683 ,l_pseudo_hdr_id
1684 ,1
1685 ,'T'
1686 ,l_rec_seq_tbl(i)
1687 ,l_instance_name_tbl(i)
1688 ,l_loc_id_tbl(i)
1689 ,l_loc_type_code_tbl(i)
1690 );
1691
1692 -- reoder sequences if generated by this proc
1693 l_nDebug := 6;
1694 IF l_rec1_seq IS NULL THEN
1695 SELECT config_item_id BULK COLLECT INTO l_config_item_tbl
1696 FROM cz_config_items
1697 WHERE config_hdr_id = l_pseudo_hdr_id AND config_rev_nbr = 1
1698 ORDER BY sequence_nbr, config_delta;
1699
1700 FOR i IN l_config_item_tbl.FIRST .. l_config_item_tbl.LAST LOOP
1701 l_seq_nbr_tbl(i) := i;
1702 END LOOP;
1703
1704 FORALL i IN l_config_item_tbl.FIRST .. l_config_item_tbl.LAST
1705 UPDATE cz_config_items
1706 SET sequence_nbr = l_seq_nbr_tbl(i)
1707 WHERE config_hdr_id = l_pseudo_hdr_id AND config_rev_nbr = 1
1708 AND config_item_id = l_config_item_tbl(i);
1709 IF l_log_stmt THEN
1710 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, 'item sequences reordered',
1711 FND_LOG.LEVEL_STATEMENT);
1712 END IF;
1713 END IF;
1714 END IF;
1715
1716 l_nDebug := 7;
1717 -- processing extended attributes
1718 IF l_has_attr THEN
1719 IF l_rec1_seq IS NULL AND NOT l_has_item THEN
1720 l_rec1_seq := p_config_ext_attr_tbl(p_config_ext_attr_tbl.FIRST).sequence_nbr;
1721 IF l_rec1_seq = FND_API.G_MISS_NUM THEN
1722 l_rec1_seq := NULL;
1723 END IF;
1724 END IF;
1725
1726 l_idx := 0;
1727 l_config_item_tbl.DELETE;
1728 l_ecc_tbl.DELETE;
1729 l_seq_nbr_tbl.DELETE;
1730 FOR i In p_config_ext_attr_tbl.FIRST .. p_config_ext_attr_tbl.LAST LOOP
1731 IF l_log_stmt THEN
1732 l_msg := 'attr rec ' || i || ': id=' || p_config_ext_attr_tbl(i).config_item_id ||
1733 ', seq=' || p_config_ext_attr_tbl(i).sequence_nbr ||
1734 ', nam=' || p_config_ext_attr_tbl(i).attribute_name ||
1735 ', grp=' || p_config_ext_attr_tbl(i).attribute_group ||
1736 ', val=' || p_config_ext_attr_tbl(i).attribute_value;
1737 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_STATEMENT);
1738 END IF;
1739
1740 IF p_config_ext_attr_tbl(i).config_item_id IS NULL OR
1741 p_config_ext_attr_tbl(i).config_item_id = FND_API.G_MISS_NUM THEN
1742 set_message('CZ_BV_NULL_VAL', 'COLUMN', 'CONFIG_ITEM_ID', 'TYPE', 'ATTRIBUTE',
1743 'IDX', i, 'SEQ', p_config_ext_attr_tbl(i).sequence_nbr);
1744 RAISE FND_API.G_EXC_ERROR;
1745 END IF;
1746
1747 IF l_rec1_seq IS NULL AND p_config_ext_attr_tbl(i).sequence_nbr IS NOT NULL OR
1748 l_rec1_seq IS NOT NULL AND (p_config_ext_attr_tbl(i).sequence_nbr IS NULL OR
1749 p_config_ext_attr_tbl(i).sequence_nbr=FND_API.G_MISS_NUM) THEN
1750 set_message('CZ_BV_NULL_VAL', 'COLUMN', 'SEQUENCE_NBR', 'TYPE', 'ATTRIBUTE',
1751 'IDX', i, 'SEQ', p_config_ext_attr_tbl(i).sequence_nbr);
1752 RAISE FND_API.G_EXC_ERROR;
1753 ELSIF p_config_ext_attr_tbl(i).sequence_nbr IS NOT NULL THEN
1754 IF l_item_seq_map.exists(p_config_ext_attr_tbl(i).sequence_nbr) THEN
1755 fnd_message.set_name('CZ', 'CZ_BV_DUP_SEQ');
1756 fnd_message.set_token('SEQ', p_config_ext_attr_tbl(i).sequence_nbr);
1757 fnd_msg_pub.add;
1758 RAISE FND_API.G_EXC_ERROR;
1759 ELSE
1760 l_item_seq_map(p_config_ext_attr_tbl(i).sequence_nbr) := i;
1761 END IF;
1762 END IF;
1763
1764 IF p_config_ext_attr_tbl(i).attribute_name IS NULL OR
1765 p_config_ext_attr_tbl(i).attribute_name = FND_API.G_MISS_CHAR THEN
1766 set_message('CZ_BV_NULL_VAL', 'COLUMN', 'ATTRIBUTE_NAME', 'TYPE', 'ATTRIBUTE',
1767 'IDX', i, 'SEQ', p_config_ext_attr_tbl(i).sequence_nbr);
1768 RAISE FND_API.G_EXC_ERROR;
1769 END IF;
1770 IF p_config_ext_attr_tbl(i).attribute_value IS NULL THEN
1771 set_message('CZ_BV_NULL_VAL', 'COLUMN', 'ATTRIBUTE_VALUE', 'TYPE', 'ATTRIBUTE',
1772 'IDX', i, 'SEQ', p_config_ext_attr_tbl(i).sequence_nbr);
1773 RAISE FND_API.G_EXC_ERROR;
1774 END IF;
1775
1776 IF NOT l_upd_item_map.exists(p_config_ext_attr_tbl(i).config_item_id) THEN
1777 get_ext_comp_code(l_config_hdr_id, l_config_rev_nbr,
1778 p_config_ext_attr_tbl(i).config_item_id,
1779 l_ext_comp_code, l_item_depth);
1780
1781 IF l_ext_comp_code IS NULL OR length(l_ext_comp_code) = 0 THEN
1782 fnd_message.set_name('CZ', 'CZ_BV_INVALID_ITEM');
1783 fnd_message.set_token('HDR', l_config_hdr_id);
1784 fnd_message.set_token('REV', l_config_rev_nbr);
1785 fnd_message.set_token('ID', p_config_ext_attr_tbl(i).config_item_id);
1786 fnd_message.set_token('TYPE', 'ATTRIBUTE');
1787 fnd_message.set_token('IND', i);
1788 fnd_message.set_token('SEQ', p_config_ext_attr_tbl(i).sequence_nbr);
1789 fnd_msg_pub.add;
1790 RAISE FND_API.G_EXC_ERROR;
1791 END IF;
1792
1793 -- Create a dummy item with no-op for now, this part of code will be removed
1794 -- or modified after changing to not generate ecc
1795 -- If we do need to keep ecc, do a single bulk insert for both the item recs from
1796 -- inputs and the dummy recs here by adding the dummies to the tbls used in processing
1797 -- inputs and then moving bulk ins stmt to the end of processing attr
1798 INSERT INTO CZ_CONFIG_ITEMS
1799 (config_hdr_id
1800 ,config_rev_nbr
1801 ,config_item_id
1802 ,sequence_nbr
1803 ,value_type_code
1804 ,node_identifier
1805 ,INSTANCE_HDR_ID
1806 ,INSTANCE_REV_NBR
1807 ,COMPONENT_INSTANCE_TYPE
1808 ,CONFIG_DELTA
1809 )
1810 VALUES (l_pseudo_hdr_id
1811 ,1
1812 ,p_config_ext_attr_tbl(i).config_item_id
1813 ,i -- do not matter for no-op item
1814 ,BV_OPERATION_UPDATE
1815 ,l_ext_comp_code
1816 ,l_pseudo_hdr_id
1817 ,1
1818 ,'T'
1819 ,l_item_depth
1820 );
1821 l_upd_item_map(p_config_ext_attr_tbl(i).config_item_id) := p_config_ext_attr_tbl(i).config_item_id;
1822 END IF;
1823
1824 l_idx := l_idx + 1;
1825 l_config_item_tbl(l_idx) := p_config_ext_attr_tbl(i).config_item_id;
1826 l_attr_nam_tbl(l_idx) := p_config_ext_attr_tbl(i).attribute_name;
1827 l_attr_grp_tbl(l_idx) := p_config_ext_attr_tbl(i).attribute_group;
1828 l_attr_val_tbl(l_idx) := p_config_ext_attr_tbl(i).attribute_value;
1829 l_seq_nbr_tbl(l_idx) := NVL(p_config_ext_attr_tbl(i).sequence_nbr, i);
1830 END LOOP;
1831
1832 l_nDebug := 8;
1833 FORALL i IN l_config_item_tbl.first .. l_config_item_tbl.lAST
1834 INSERT INTO cz_config_ext_attributes(config_hdr_id
1835 ,config_rev_nbr
1836 ,config_item_id
1837 ,attribute_name
1838 ,attribute_group
1839 ,attribute_value
1840 ,sequence_nbr
1841 )
1842 VALUES(l_pseudo_hdr_id
1843 ,1
1844 ,l_config_item_tbl(i)
1845 ,l_attr_nam_tbl(i)
1846 ,l_attr_grp_tbl(i)
1847 ,l_attr_val_tbl(i)
1848 ,l_seq_nbr_tbl(i)
1849 );
1850
1851 -- reorder sequences if generated by this proc
1852 l_nDebug := 9;
1853 IF l_rec1_seq IS NULL THEN
1854 l_config_item_tbl.DELETE;
1855 l_seq_nbr_tbl.DELETE;
1856 SELECT attr.config_item_id BULK COLLECT INTO l_config_item_tbl
1857 FROM cz_config_ext_attributes attr, cz_config_items item
1858 WHERE attr.config_hdr_id = l_pseudo_hdr_id AND attr.config_rev_nbr = 1
1859 AND attr.config_hdr_id = item.config_hdr_id AND attr.config_rev_nbr = item.config_rev_nbr
1860 AND attr.config_item_id = item.config_item_id
1861 ORDER BY nvl(length(translate(item.node_identifier,'-0123456789','A')),0), attr.sequence_nbr;
1862
1863 FOR i IN l_config_item_tbl.FIRST .. l_config_item_tbl.LAST LOOP
1864 l_seq_nbr_tbl(i) := i + l_idx;
1865 END LOOP;
1866
1867 FORALL i IN l_config_item_tbl.FIRST .. l_config_item_tbl.LAST
1868 UPDATE cz_config_ext_attributes
1869 SET sequence_nbr = l_seq_nbr_tbl(i)
1870 WHERE config_hdr_id = l_pseudo_hdr_id AND config_rev_nbr = 1
1871 AND config_item_id = l_config_item_tbl(i);
1872
1873 IF l_log_stmt THEN
1874 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, 'attr sequences reordered',
1875 FND_LOG.LEVEL_STATEMENT);
1876 END IF;
1877 END IF;
1878
1879 END IF;
1880 END IF;
1881
1882 l_nDebug := 10;
1883 validate(l_pseudo_hdr_id
1884 ,l_url
1885 ,p_init_msg
1886 ,l_validation_type
1887 ,l_validation_status
1888 ,x_config_xml_msg
1889 ,v_detailed_error_message
1890 );
1891
1892 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, 'validation status: ' ||
1893 l_validation_status||v_detailed_error_message, FND_LOG.LEVEL_PROCEDURE);
1894
1895 IF l_validation_status = INIT_TOO_LONG THEN
1896 fnd_message.set_name('CZ', 'CZ_BV_ERR_INIT_MSG');
1897 fnd_message.SET_TOKEN('ERROR_MSG', v_detailed_error_message);
1898 fnd_msg_pub.add;
1899 RAISE FND_API.G_EXC_ERROR;
1900 ELSIF l_validation_status = UTL_HTTP_INIT_FAILED THEN
1901 fnd_message.set_name('CZ', 'CZ_BV_ERR_HTTP_INIT');
1902 fnd_message.SET_TOKEN('ERROR_MSG', v_detailed_error_message);
1903 fnd_msg_pub.add;
1904 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1905 ELSIF l_validation_status = UTL_HTTP_REQUEST_FAILED THEN
1906 fnd_message.set_name('CZ', 'CZ_BV_ERR_HTTP_REQ');
1907 fnd_message.SET_TOKEN('ERROR_MSG', v_detailed_error_message);
1908 fnd_msg_pub.add;
1909 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1910 ELSIF l_validation_status = CONFIG_PROCESSED_NO_TERMINATE OR
1911 l_validation_status = CONFIG_EXCEPTION THEN
1912 FOR i IN (SELECT message FROM cz_config_messages
1913 WHERE config_hdr_id = l_pseudo_hdr_id AND config_rev_nbr = 1
1914 ORDER BY message_seq) LOOP
1915 fnd_msg_pub.Add_Exc_Msg(p_error_text => i.message);
1916 END LOOP;
1917 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1918 END IF;
1919
1920 l_nDebug := 11;
1921 IF l_validation_status = CONFIG_PROCESSED AND l_validation_type = CZ_API_PUB.VALIDATE_ORDER
1922 AND NOT (l_has_item OR l_has_attr) THEN
1923 check_deltas(p_init_msg, x_config_xml_msg, x_return_status);
1924 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1925 fnd_msg_pub.Add_Exc_Msg(p_error_text => 'Error from check_delta:');
1926 FOR i IN (SELECT message FROM cz_config_messages
1927 WHERE config_hdr_id = l_pseudo_hdr_id AND config_rev_nbr = 1
1928 ORDER BY message_seq) LOOP
1929 fnd_msg_pub.Add_Exc_Msg(p_error_text => i.message);
1930 END LOOP;
1931 RAISE FND_API.G_EXC_ERROR;
1932 END IF;
1933 END IF;
1934
1935 l_nDebug := 12;
1936 delete_bv_records(l_pseudo_hdr_id, TRUE, TRUE);
1937
1938 x_return_status := FND_API.G_RET_STS_SUCCESS;
1939
1940 EXCEPTION
1941 WHEN FND_API.G_EXC_ERROR THEN
1942 x_return_status := FND_API.G_RET_STS_ERROR;
1943 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1944 p_data => x_msg_data);
1945 ROLLBACK;
1946
1947 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1948 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1949 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1950 p_data => x_msg_data);
1951 delete_bv_records(l_pseudo_hdr_id, FALSE, TRUE);
1952
1953 WHEN OTHERS THEN
1954 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1955 IF fnd_msg_pub.check_msg_Level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
1956 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
1957 END IF;
1958
1959 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1960 p_data => x_msg_data);
1961 delete_bv_records(l_pseudo_hdr_id, FALSE, TRUE);
1962 END validate; -- new
1963
1964 --------------------------------------------------------------------------------
1965 -- single-call validation function uses tables to exchange multi-valued data
1966 PROCEDURE VALIDATE (config_input_list IN CFG_INPUT_LIST,
1967 init_message IN VARCHAR2,
1968 config_messages IN OUT NOCOPY CFG_OUTPUT_PIECES,
1969 validation_status IN OUT NOCOPY NUMBER,
1970 URL IN VARCHAR2 DEFAULT FND_PROFILE.Value('CZ_UIMGR_URL'),
1971 p_validation_type IN VARCHAR2 DEFAULT CZ_API_PUB.VALIDATE_ORDER)
1972
1973 IS
1974 PRAGMA AUTONOMOUS_TRANSACTION;
1975
1976 l_api_name CONSTANT VARCHAR2(20) := 'validate:old';
1977 l_nDebug PLS_INTEGER;
1978 l_log_stmt BOOLEAN;
1979 l_msg VARCHAR2(2000);
1980
1981 l_pseudo_hdr_id NUMBER;
1982 l_pseudo_item_id PLS_INTEGER;
1983 l_rec1_seq NUMBER;
1984 l_hdr_id NUMBER;
1985 l_rev_nbr NUMBER;
1986 l_item_id NUMBER;
1987 l_ext_comp_code cz_config_items.node_identifier%TYPE;
1988 l_item_depth INTEGER;
1989 l_delta_status VARCHAR2(1);
1990 l_check_db_setting BOOLEAN;
1991
1992 l_ecc_tbl str1200_tbl_type;
1993 l_item_id_tbl NUMBER_TBL_INDEXBY_TYPE;
1994 l_quantity_tbl NUMBER_TBL_INDEXBY_TYPE;
1995 l_input_seq_tbl NUMBER_TBL_INDEXBY_TYPE;
1996 l_item_depth_tbl NUMBER_TBL_INDEXBY_TYPE;
1997
1998 v_detailed_error_message varchar2(2000);
1999
2000 DELTA_CHECK_FAILURE EXCEPTION;
2001
2002 BEGIN
2003 l_nDebug := 1;
2004 l_log_stmt := FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2005 IF l_log_stmt THEN
2006 l_msg := 'Number of inputs=' || config_input_list.COUNT;
2007 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_STATEMENT);
2008 END IF;
2009
2010 IF config_input_list.COUNT > 0 THEN
2011 BEGIN
2012 l_pseudo_hdr_id := next_config_hdr_id;
2013 INSERT INTO CZ_CONFIG_HDRS (config_hdr_id
2014 ,config_rev_nbr
2015 ,name
2016 ,effective_usage_id
2017 ,deleted_flag
2018 ,CONFIG_DELTA_SPEC
2019 ,COMPONENT_INSTANCE_TYPE
2020 ,MODEL_INSTANTIATION_TYPE
2021 ,HAS_FAILURES)
2022 VALUES (l_pseudo_hdr_id
2023 ,1
2024 ,'old batch'
2025 ,ANY_USAGE_ID
2026 ,'0'
2027 ,0
2028 ,ROOT
2029 ,BV_MODEL_TYPE
2030 ,'0');
2031
2032 l_rec1_seq := config_input_list(config_input_list.FIRST).input_seq;
2033 IF l_log_stmt THEN
2034 l_msg := '1st rec input_seq=' || l_rec1_seq;
2035 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_STATEMENT);
2036 l_msg := 'pseudo hdr=' || l_pseudo_hdr_id;
2037 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_STATEMENT);
2038 END IF;
2039
2040 l_nDebug := 2;
2041 l_hdr_id := 0;
2042 l_pseudo_item_id := -1;
2043 FOR i IN config_input_list.FIRST..config_input_list.LAST
2044 LOOP
2045 IF config_input_list(i).component_code IS NULL OR
2046 config_input_list(i).quantity IS NULL THEN
2047 l_msg := 'The component code or quantity passed in rec ' || i || ' is NULL';
2048 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_ERROR);
2049 RAISE INVALID_OPTION_EXCEPTION;
2050 END IF;
2051
2052 l_quantity_tbl(i) := config_input_list(i).quantity;
2053
2054 IF l_rec1_seq IS NULL THEN
2055 l_input_seq_tbl(i) := i;
2056 ELSE
2057 l_input_seq_tbl(i) := config_input_list(i).input_seq;
2058 END IF;
2059
2060 IF (config_input_list(i).config_item_id IS NOT NULL) THEN
2061 l_item_id := config_input_list(i).config_item_id;
2062 IF l_hdr_id = 0 THEN
2063 get_config_hdr(init_message, l_hdr_id, l_rev_nbr);
2064 IF l_log_stmt THEN
2065 l_msg := 'hdr in init msg=' || l_hdr_id || ',' || l_rev_nbr;
2066 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_STATEMENT);
2067 END IF;
2068 END IF;
2069 get_ext_comp_code(l_hdr_id, l_rev_nbr, l_item_id, l_ext_comp_code, l_item_depth);
2070 IF l_ext_comp_code IS NULL OR l_ext_comp_code = '' THEN
2071 l_msg := 'The config item id '||l_item_id||' passed in rec '||i||' for config header id '||
2072 l_hdr_id || ' and rev nbr ' || l_rev_nbr || ' is invalid';
2073 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_ERROR);
2074 RAISE INVALID_OPTION_EXCEPTION;
2075 END IF;
2076 ELSE
2077 append_instance_nbr(config_input_list(i).component_code, l_ext_comp_code, l_item_depth);
2078 l_item_id := l_pseudo_item_id;
2079 l_pseudo_item_id := l_pseudo_item_id - 1;
2080 END IF;
2081
2082 l_item_id_tbl(i) := l_item_id;
2083 l_ecc_tbl(i) := l_ext_comp_code;
2084 l_item_depth_tbl(i) := l_item_depth;
2085 END LOOP;
2086
2087 l_nDebug := 3;
2088 FORALL i IN l_item_id_tbl.first .. l_item_id_tbl.lAST
2089 INSERT INTO CZ_CONFIG_ITEMS(config_hdr_id
2090 ,config_rev_nbr
2091 ,config_item_id
2092 ,sequence_nbr
2093 ,node_identifier
2094 ,item_num_val
2095 ,value_type_code
2096 ,INSTANCE_HDR_ID
2097 ,INSTANCE_REV_NBR
2098 ,COMPONENT_INSTANCE_TYPE
2099 ,CONFIG_DELTA
2100 )
2101 VALUES (l_pseudo_hdr_id
2102 ,1
2103 ,l_item_id_tbl(i)
2104 ,l_input_seq_tbl(i)
2105 ,l_ecc_tbl(i)
2106 ,l_quantity_tbl(i)
2107 ,BV_OPERATION_OLD
2108 ,l_pseudo_hdr_id
2109 ,1
2110 ,INCLUDED
2111 ,l_item_depth_tbl(i)
2112 );
2113
2114 l_nDebug := 4;
2115 -- Ideally reorder inputs only if the sequences are generated locally
2116 -- But the sequences OM passes to us are actually meaningless. So
2117 -- unfortunately we have to take this performance hit and to reorder
2118 -- the inputsregardless of who generates the seq
2119 -- IF l_rec1_seq IS NULL THEN
2120 l_item_id_tbl.delete;
2121 l_input_seq_tbl.delete;
2122 SELECT config_item_id BULK COLLECT INTO l_item_id_tbl
2123 FROM cz_config_items
2124 WHERE config_hdr_id = l_pseudo_hdr_id
2125 AND config_rev_nbr = 1
2126 ORDER BY config_delta, sequence_nbr;
2127
2128 FOR i IN l_item_id_tbl.FIRST .. l_item_id_tbl.LAST LOOP
2129 l_input_seq_tbl(i) := i;
2130 END LOOP;
2131
2132 FORALL i IN l_item_id_tbl.FIRST .. l_item_id_tbl.LAST
2133 UPDATE cz_config_items
2134 SET sequence_nbr = l_input_seq_tbl(i)
2135 WHERE config_hdr_id = l_pseudo_hdr_id
2136 AND config_rev_nbr = 1
2137 AND config_item_id = l_item_id_tbl(i);
2138 -- END IF;
2139
2140 EXCEPTION
2141 WHEN INVALID_OPTION_EXCEPTION THEN
2142 validation_status:=INVALID_OPTION_REQUEST;
2143 ROLLBACK;
2144 RETURN;
2145
2146 WHEN OTHERS THEN
2147 validation_status:=DATABASE_ERROR;
2148 ROLLBACK;
2149 l_msg := SQLERRM;
2150 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg, FND_LOG.LEVEL_ERROR);
2151 RETURN;
2152 END;
2153
2154 END IF;
2155
2156 l_nDebug := 5;
2157 validate(l_pseudo_hdr_id
2158 ,URL
2159 ,init_message
2160 ,p_validation_type
2161 ,validation_status
2162 ,config_messages
2163 ,v_detailed_error_message
2164 );
2165
2166 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, 'validation status: ' ||
2167 validation_status||v_detailed_error_message, FND_LOG.LEVEL_PROCEDURE);
2168
2169 l_nDebug := 6;
2170 -- this block would set the config status status in the terminate message to FALSE
2171 -- if the config_input_list is empty and validation_status is CONFIG_PROCESSED
2172 -- and if there are configured item changes. The changes are logged to cz_config_messages
2173 IF validation_status = CONFIG_PROCESSED AND p_validation_type = CZ_API_PUB.VALIDATE_ORDER AND
2174 config_input_list.COUNT = 0 THEN
2175 check_deltas(init_message,config_messages,l_delta_status);
2176 IF (l_delta_status <> FND_API.G_RET_STS_SUCCESS) THEN
2177 RAISE DELTA_CHECK_FAILURE;
2178 END IF;
2179 END IF;
2180
2181 l_nDebug := 7;
2182 -- delete based on setting in cz_db_settings
2183 l_check_db_setting := TRUE;
2184 IF validation_status = UTL_HTTP_INIT_FAILED OR
2185 validation_status = UTL_HTTP_REQUEST_FAILED THEN
2186 l_check_db_setting := FALSE;
2187 END IF;
2188
2189 delete_bv_records(l_pseudo_hdr_id, l_check_db_setting, FALSE);
2190
2191 EXCEPTION
2192 WHEN DELTA_CHECK_FAILURE THEN
2193 validation_status:=CONFIG_EXCEPTION;
2194 COMMIT;
2195 l_msg := 'DELTA_CHECK_FAILURE';
2196 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg||v_detailed_error_message, FND_LOG.LEVEL_ERROR);
2197
2198 WHEN OTHERS THEN
2199 delete_bv_records(l_pseudo_hdr_id, FALSE, FALSE);
2200 validation_status:=CONFIG_EXCEPTION;
2201 l_msg := SQLERRM;
2202 CZ_UTILS.log_report(G_PKG_NAME, l_api_name, l_nDebug, l_msg||v_detailed_error_message, FND_LOG.LEVEL_ERROR);
2203 END validate; -- old
2204
2205 ------------------------------------------------------------------------------------------
2206 FUNCTION model_for_item(inventory_item_id NUMBER,
2207 organization_id NUMBER,
2208 config_creation_date DATE,
2209 user_id NUMBER,
2210 responsibility_id NUMBER,
2211 calling_application_id NUMBER
2212 )
2213
2214 RETURN NUMBER
2215 IS
2216 BEGIN
2217 RETURN config_model_for_item(inventory_item_id, organization_id,
2218 config_creation_date, calling_application_id,
2219 NULL);
2220 END model_for_item;
2221
2222 --------------------------------------------------------------------------------------------
2223 FUNCTION config_model_for_item (inventory_item_id IN NUMBER,
2224 organization_id IN NUMBER,
2225 config_lookup_date IN DATE,
2226 calling_application_id IN NUMBER,
2227 usage_name IN VARCHAR2,
2228 publication_mode IN VARCHAR2 DEFAULT NULL,
2229 language IN VARCHAR2 DEFAULT NULL
2230 )
2231 RETURN NUMBER
2232 IS
2233
2234 v_publication_id NUMBER;
2235
2236 BEGIN
2237
2238 v_publication_id := publication_for_item(inventory_item_id,organization_id,
2239 config_lookup_date,
2240 calling_application_id,usage_name,
2241 publication_mode,
2242 language);
2243
2244 IF v_publication_id IS NULL THEN
2245 RETURN NULL;
2246 ELSE
2247 RETURN model_for_publication_id(v_publication_id);
2248 END IF;
2249
2250 END config_model_for_item;
2251
2252 --------------------------------------------------------------------------------
2253 FUNCTION config_models_for_items (inventory_item_id IN NUMBER_TBL_TYPE,
2254 organization_id IN NUMBER_TBL_TYPE,
2255 config_lookup_date IN DATE_TBL_TYPE,
2256 calling_application_id IN NUMBER_TBL_TYPE,
2257 usage_name IN VARCHAR2_TBL_TYPE,
2258 publication_mode IN VARCHAR2_TBL_TYPE,
2259 language IN VARCHAR2_TBL_TYPE
2260 )
2261 RETURN NUMBER_TBL_TYPE
2262 IS
2263
2264 t_models_for_items NUMBER_TBL_TYPE := NUMBER_TBL_TYPE();
2265
2266 nof_inventory_item_id NUMBER;
2267 nof_organization_id NUMBER;
2268 nof_config_lookup_date NUMBER;
2269 nof_calling_application_id NUMBER;
2270 nof_usage_name NUMBER;
2271 nof_language NUMBER;
2272 nof_publication_mode NUMBER;
2273
2274 BEGIN
2275
2276 nof_inventory_item_id := inventory_item_id.COUNT;
2277 nof_organization_id := organization_id.COUNT;
2278 nof_config_lookup_date := config_lookup_date.COUNT;
2279 nof_calling_application_id := calling_application_id.COUNT;
2280 nof_usage_name := usage_name.COUNT;
2281 nof_language := language.COUNT;
2282 nof_publication_mode := publication_mode.COUNT;
2283
2284 IF ( (nof_inventory_item_id <> nof_organization_id) OR
2285 (nof_inventory_item_id <> nof_config_lookup_date) OR
2286 (nof_inventory_item_id <> nof_calling_application_id) OR
2287 (nof_inventory_item_id <> nof_usage_name) OR
2288 (nof_inventory_item_id <> nof_language) OR
2289 (nof_inventory_item_id <> nof_publication_mode) ) THEN
2290 RAISE WRONG_ARRAYS_LENGTH;
2291 END IF;
2292
2293 t_models_for_items.extend(nof_inventory_item_id);
2294
2295 FOR i IN 1..nof_inventory_item_id LOOP
2296 t_models_for_items(i) := config_model_for_item(inventory_item_id(i), organization_id(i),
2297 config_lookup_date(i), calling_application_id(i),
2298 usage_name(i), publication_mode(i), language(i));
2299 END LOOP;
2300 RETURN t_models_for_items;
2301 EXCEPTION
2302 WHEN WRONG_ARRAYS_LENGTH THEN
2303 -- xERROR:=CZ_UTILS.REPORT('The size of input arrays should be the same',1,'CZ_CF_API.CONFIG_MODELS_FOR_ITEMS',11222);
2304 cz_utils.log_report('CZ_CF_API', 'config_models_for_items', null,
2305 'The size of input arrays should be the same',
2306 fnd_log.LEVEL_EXCEPTION);
2307 RAISE_APPLICATION_ERROR (-20001,
2308 'The size of input arrays should be the same');
2309 WHEN OTHERS THEN
2310 -- xERROR:=CZ_UTILS.REPORT(SQLERRM,1,'CZ_CF_API.CONFIG_MODELS_FOR_ITEMS',11222);
2311 cz_utils.log_report('CZ_CF_API', 'config_models_for_items', null, SQLERRM,
2312 fnd_log.LEVEL_UNEXPECTED);
2313 END config_models_for_items;
2314
2315 ----------------------------------------------------------------------------------------
2316 -- Returns the ui (ui_def_id and ui type) specified by input publication_id.
2317 -- If input publication_id is null, inventory_item_id and organization_id will be used
2318 -- to decide whether to return the seeded native bom ui or not.
2319 --
2320 FUNCTION config_ui_for_item_pvt(p_publication_id IN NUMBER
2321 ,px_ui_type IN OUT NOCOPY VARCHAR2
2322 ,p_inventory_item_id IN NUMBER
2323 ,p_organization_id IN NUMBER
2324 )
2325 RETURN NUMBER
2326 IS
2327 l_ui_style CZ_MODEL_PUBLICATIONS.ui_style%TYPE;
2328 l_pub_ui_style CZ_MODEL_PUBLICATIONS.ui_style%TYPE;
2329 l_dummy INTEGER;
2330
2331 BEGIN
2332 IF p_publication_id IS NULL THEN
2333 IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
2334 BEGIN
2335 SELECT 1 INTO l_dummy
2336 FROM mtl_system_items
2337 WHERE inventory_item_id = p_inventory_item_id
2338 AND organization_id = p_organization_id
2339 AND bom_item_type = BOM_ITEM_TYPE_MODEL;
2340 EXCEPTION
2341 WHEN NO_DATA_FOUND THEN
2342 px_ui_type := NULL;
2343 RETURN NULL;
2344 END;
2345
2346 px_ui_type := NATIVEBOM_UI_TYPE;
2347 RETURN NATIVEBOM_UI_DEF;
2348 ELSE
2349 px_ui_type := NULL;
2350 RETURN NULL;
2351 END IF;
2352
2353 ELSE
2354 SELECT ui_style INTO l_pub_ui_style
2355 FROM CZ_MODEL_PUBLICATIONS
2356 WHERE publication_id = p_publication_id;
2357
2358 IF px_ui_type IS NOT NULL THEN
2359 -- If input ui_type is APPLET, either APPLET or DHTML or JRAD is OK.
2360 -- If input ui_type is JRAD or DHTML, the UI associated with the publication
2361 -- MUST be either JRAD, or DHTML, or WEGA. If not, return NULL.
2362 l_ui_style := ui_style_from_ui_type(px_ui_type);
2363 IF l_ui_style IS NULL OR l_ui_style<>UI_STYLE_APPLET AND l_pub_ui_style=UI_STYLE_APPLET THEN
2364 px_ui_type := NULL;
2365 RETURN NULL;
2366 END IF;
2367 END IF;
2368
2369 px_ui_type := ui_type_from_ui_style(l_pub_ui_style);
2370 RETURN ui_for_publication_id(p_publication_id);
2371 END IF;
2372 END config_ui_for_item_pvt;
2373
2374 --------------------------------------------------------------------------------------------
2375 FUNCTION ui_for_item(inventory_item_id NUMBER,
2376 organization_id NUMBER,
2377 config_creation_date DATE,
2378 ui_type VARCHAR2,
2379 user_id NUMBER,
2380 responsibility_id NUMBER,
2381 calling_application_id NUMBER
2382 )
2383 RETURN NUMBER
2384 IS
2385 v_ui_type VARCHAR2(30) := ui_type;
2386 l_return_ui_def_id cz_ui_defs.ui_def_id%TYPE;
2387 l_return_ui_profile VARCHAR2(3);
2388 BEGIN
2389 l_return_ui_def_id := config_ui_for_item(inventory_item_id, organization_id,
2390 config_creation_date, v_ui_type,
2391 calling_application_id, NULL);
2392 IF ( (l_return_ui_def_id IS NOT NULL)
2393 AND (l_return_ui_def_id = NATIVEBOM_UI_DEF) ) THEN
2394 l_return_ui_profile := FND_PROFILE.value('CZGENERICBOMUIPROFILE');
2395 IF (UPPER(l_return_ui_profile) IN ('N', 'NO') ) THEN
2396 l_return_ui_def_id := NULL;
2397 END IF;
2398 END IF;
2399 RETURN l_return_ui_def_id;
2400 END ui_for_item;
2401 ------------------------------------------------------------------------------
2402
2403 FUNCTION config_ui_for_item(inventory_item_id IN NUMBER,
2404 organization_id IN NUMBER,
2405 config_lookup_date IN DATE,
2406 ui_type IN OUT NOCOPY VARCHAR2,
2407 calling_application_id IN NUMBER,
2408 usage_name IN VARCHAR2,
2409 publication_mode IN VARCHAR2 DEFAULT NULL,
2410 language IN VARCHAR2 DEFAULT NULL
2411 )
2412 RETURN NUMBER
2413 IS
2414 l_publication_id NUMBER;
2415
2416 BEGIN
2417 l_publication_id := publication_for_item(inventory_item_id,
2418 organization_id,
2419 config_lookup_date,
2420 calling_application_id,
2421 usage_name,
2422 publication_mode,
2423 language);
2424 RETURN config_ui_for_item_pvt(l_publication_id, ui_type, inventory_item_id,
2425 organization_id);
2426 END config_ui_for_item;
2427
2428 ---------------------------------------------------------------------------
2429
2430 FUNCTION config_ui_for_item_lf (inventory_item_id IN NUMBER,
2431 organization_id IN NUMBER,
2432 config_lookup_date IN DATE,
2433 ui_type IN OUT NOCOPY VARCHAR2,
2434 calling_application_id IN NUMBER,
2435 usage_name IN VARCHAR2,
2436 look_and_feel OUT NOCOPY VARCHAR2,
2437 publication_mode IN VARCHAR2 DEFAULT NULL,
2438 language IN VARCHAR2 DEFAULT NULL
2439 )
2440 RETURN NUMBER
2441 IS
2442
2443 v_ui_def_id NUMBER;
2444
2445 BEGIN
2446 v_ui_def_id := config_ui_for_item(inventory_item_id, organization_id, config_lookup_date,
2447 ui_type, calling_application_id, usage_name, publication_mode, language);
2448
2449 IF v_ui_def_id IS NULL THEN
2450 look_and_feel := NULL;
2451 ELSE
2452 SELECT look_and_feel INTO look_and_feel FROM cz_ui_defs WHERE ui_def_id = v_ui_def_id;
2453 END IF;
2454 RETURN v_ui_def_id;
2455
2456 END config_ui_for_item_lf;
2457
2458 ---------------------------------------------------------------------------
2459
2460 FUNCTION config_uis_for_items (inventory_item_id IN NUMBER_TBL_TYPE,
2461 organization_id IN NUMBER_TBL_TYPE,
2462 config_lookup_date IN DATE_TBL_TYPE,
2463 ui_type IN OUT NOCOPY VARCHAR2_TBL_TYPE,
2464 calling_application_id IN NUMBER_TBL_TYPE,
2465 usage_name IN VARCHAR2_TBL_TYPE,
2466 publication_mode IN VARCHAR2_TBL_TYPE,
2467 language IN VARCHAR2_TBL_TYPE
2468 )
2469 RETURN NUMBER_TBL_TYPE
2470 IS
2471 t_uis_for_items NUMBER_TBL_TYPE := NUMBER_TBL_TYPE();
2472
2473 nof_inventory_item_id NUMBER;
2474 nof_organization_id NUMBER;
2475 nof_config_lookup_date NUMBER;
2476 nof_ui_type NUMBER;
2477 nof_calling_application_id NUMBER;
2478 nof_usage_name NUMBER;
2479 nof_language NUMBER;
2480 nof_publication_mode NUMBER;
2481
2482 BEGIN
2483
2484 nof_inventory_item_id := inventory_item_id.COUNT;
2485 nof_organization_id := organization_id.COUNT;
2486 nof_config_lookup_date := config_lookup_date.COUNT;
2487 nof_ui_type := ui_type.COUNT;
2488 nof_calling_application_id := calling_application_id.COUNT;
2489 nof_usage_name := usage_name.COUNT;
2490 nof_language := language.COUNT;
2491 nof_publication_mode := publication_mode.COUNT;
2492
2493 IF ( (nof_inventory_item_id <> nof_organization_id) OR
2494 (nof_inventory_item_id <> nof_config_lookup_date) OR
2495 (nof_inventory_item_id <> nof_ui_type) OR
2496 (nof_inventory_item_id <> nof_calling_application_id) OR
2497 (nof_inventory_item_id <> nof_usage_name) OR
2498 (nof_inventory_item_id <> nof_language) OR
2499 (nof_inventory_item_id <> nof_publication_mode) ) THEN
2500 RAISE WRONG_ARRAYS_LENGTH;
2501 END IF;
2502 t_uis_for_items.extend(nof_inventory_item_id);
2503 FOR i IN 1..nof_inventory_item_id LOOP
2504 t_uis_for_items(i) := config_ui_for_item(inventory_item_id(i), organization_id(i),
2505 config_lookup_date(i), ui_type(i), calling_application_id(i),
2506 usage_name(i), publication_mode(i), language(i));
2507 END LOOP;
2508 RETURN t_uis_for_items;
2509
2510 EXCEPTION
2511 WHEN WRONG_ARRAYS_LENGTH THEN
2512 -- xERROR:=CZ_UTILS.REPORT('The size of input arrays should be the same',1,'CZ_CF_API.CONFIG_UIS_FOR_ITEMS',11222);
2513 cz_utils.log_report('CZ_CF_API', 'config_uis_for_items', null,
2514 'The size of input arrays should be the same',
2515 fnd_log.LEVEL_EXCEPTION);
2516 RAISE_APPLICATION_ERROR (-20001,
2517 'The size of input arrays should be the same');
2518 WHEN OTHERS THEN
2519 -- xERROR:=CZ_UTILS.REPORT(SQLERRM,1,'CZ_CF_API.CONFIG_UIS_FOR_ITEMS',11222);
2520 cz_utils.log_report('CZ_CF_API', 'config_uis_for_items', null,
2521 SQLERRM, fnd_log.LEVEL_UNEXPECTED);
2522 END config_uis_for_items;
2523
2524 ---------------------------------------------------------------------------
2525 FUNCTION model_for_publication_id (publication_id NUMBER)
2526 RETURN NUMBER
2527 IS
2528
2529 v_publication_id NUMBER := publication_id;
2530 v_model_id NUMBER ;
2531
2532 BEGIN
2533 SELECT model_id
2534 INTO v_model_id
2535 FROM CZ_MODEL_PUBLICATIONS
2536 WHERE CZ_MODEL_PUBLICATIONS.publication_id = v_publication_id
2537 AND CZ_MODEL_PUBLICATIONS.export_status = 'OK'
2538 AND CZ_MODEL_PUBLICATIONS.deleted_flag = '0';
2539
2540 IF v_model_id IS NULL THEN
2541 RETURN NULL;
2542 ELSE
2543 RETURN v_model_id;
2544 END IF;
2545
2546 END;
2547 --------------------------------------------------------------------------------------------------------------
2548
2549 FUNCTION ui_for_publication_id (publication_id NUMBER)
2550 RETURN NUMBER
2551 IS
2552
2553 v_publication_id NUMBER := publication_id;
2554 v_ui_def_id NUMBER ;
2555
2556 BEGIN
2557 SELECT ui_def_id
2558 INTO v_ui_def_id
2559 FROM CZ_MODEL_PUBLICATIONS
2560 WHERE CZ_MODEL_PUBLICATIONS.publication_id = v_publication_id
2561 AND CZ_MODEL_PUBLICATIONS.export_status = 'OK'
2562 AND CZ_MODEL_PUBLICATIONS.deleted_flag = '0';
2563
2564 IF v_ui_def_id IS NULL THEN
2565 RETURN NULL;
2566 ELSE
2567 RETURN v_ui_def_id;
2568 END IF;
2569 END;
2570
2571 ----------------------------------------------------------------------------------------------------------
2572 FUNCTION config_model_for_product ( product_key IN VARCHAR2,
2573 config_lookup_date IN DATE,
2574 calling_application_id IN NUMBER,
2575 usage_name IN VARCHAR2,
2576 publication_mode IN VARCHAR2 DEFAULT NULL,
2577 language IN VARCHAR2 DEFAULT NULL
2578 )
2579 RETURN NUMBER
2580 IS
2581
2582 v_publication_id NUMBER;
2583
2584 BEGIN
2585
2586 v_publication_id := publication_for_product(product_key,
2587 config_lookup_date,
2588 calling_application_id,
2589 usage_name,
2590 publication_mode,
2591 language);
2592
2593 IF v_publication_id IS NULL THEN
2594 RETURN NULL;
2595 ELSE
2596 RETURN model_for_publication_id(v_publication_id);
2597 END IF;
2598
2599 END config_model_for_product;
2600
2601 --------------------------------------------------------------------------------------------
2602 FUNCTION config_models_for_products ( product_key IN VARCHAR2_TBL_TYPE,
2603 config_lookup_date IN DATE_TBL_TYPE,
2604 calling_application_id IN NUMBER_TBL_TYPE,
2605 usage_name IN VARCHAR2_TBL_TYPE,
2606 publication_mode IN VARCHAR2_TBL_TYPE,
2607 language IN VARCHAR2_TBL_TYPE
2608 )
2609 RETURN NUMBER_TBL_TYPE
2610 IS
2611
2612 t_models_for_products NUMBER_TBL_TYPE := NUMBER_TBL_TYPE();
2613
2614 nof_product_key NUMBER;
2615 nof_config_lookup_date NUMBER;
2616 nof_calling_application_id NUMBER;
2617 nof_usage_name NUMBER;
2618 nof_language NUMBER;
2619 nof_publication_mode NUMBER;
2620
2621 BEGIN
2622
2623 nof_product_key := product_key.COUNT;
2624 nof_config_lookup_date := config_lookup_date.COUNT;
2625 nof_calling_application_id := calling_application_id.COUNT;
2626 nof_usage_name := usage_name.COUNT;
2627 nof_language := language.COUNT;
2628 nof_publication_mode := publication_mode.COUNT;
2629
2630 IF ( (nof_product_key <> nof_config_lookup_date) OR
2631 (nof_product_key <> nof_calling_application_id) OR
2632 (nof_product_key <> nof_usage_name) OR
2633 (nof_product_key <> nof_language) OR
2634 (nof_product_key <> nof_publication_mode) ) THEN
2635 RAISE WRONG_ARRAYS_LENGTH;
2636 END IF;
2637
2638 t_models_for_products.extend(nof_product_key);
2639
2640 FOR i IN 1..nof_product_key LOOP
2641 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));
2642 END LOOP;
2643 RETURN t_models_for_products;
2644 EXCEPTION
2645 WHEN WRONG_ARRAYS_LENGTH THEN
2646 -- xERROR:=CZ_UTILS.REPORT('The size of input arrays should be the same',1,'CZ_CF_API.CONFIG_MODELS_FOR_PRODUCTS',11222);
2647 cz_utils.log_report('CZ_CF_API', 'config_models_for_products', null,
2648 'The size of input arrays should be the same', fnd_log.LEVEL_EXCEPTION);
2649 RAISE_APPLICATION_ERROR (-20001,
2650 'The size of input arrays should be the same');
2651 WHEN OTHERS THEN
2652 -- xERROR:=CZ_UTILS.REPORT(SQLERRM,1,'CZ_CF_API.CONFIG_MODELS_FOR_PRODUCTS',11222);
2653 cz_utils.log_report('CZ_CF_API', 'config_models_for_products', null,
2654 SQLERRM, fnd_log.LEVEL_UNEXPECTED);
2655 END config_models_for_products;
2656
2657 -----------------------------------------------------------------------------------
2658
2659 FUNCTION config_ui_for_product(product_key IN VARCHAR2,
2660 config_lookup_date IN DATE,
2661 ui_type IN OUT NOCOPY VARCHAR2,
2662 calling_application_id IN NUMBER,
2663 usage_name IN VARCHAR2,
2664 publication_mode IN VARCHAR2 DEFAULT NULL,
2665 language IN VARCHAR2 DEFAULT NULL
2666 )
2667 RETURN NUMBER
2668 IS
2669 l_publication_id NUMBER;
2670
2671 l_inventory_item_id NUMBER := NULL;
2672 l_organization_id NUMBER := NULL;
2673 l_colon_pos INTEGER;
2674
2675 BEGIN
2676 l_publication_id := publication_for_product(product_key,
2677 config_lookup_date,
2678 calling_application_id,
2679 usage_name,
2680 publication_mode,
2681 language);
2682
2683 IF l_publication_id IS NULL THEN
2684 l_colon_pos := instr(product_key, ':');
2685 IF l_colon_pos > 0 THEN
2686 l_organization_id := cz_utils.conv_num(substr(product_key, 1, l_colon_pos-1));
2687 l_inventory_item_id := cz_utils.conv_num(substr(product_key, l_colon_pos+1));
2688 END IF;
2689
2690 IF l_organization_id IS NULL OR l_inventory_item_id IS NULL THEN
2691 ui_type := NULL;
2692 RETURN NULL;
2693 END IF;
2694 END IF;
2695
2696 RETURN config_ui_for_item_pvt(l_publication_id, ui_type, l_inventory_item_id,
2697 l_organization_id);
2698 END config_ui_for_product;
2699
2700 --------------------------------------------------------------------------------------------
2701
2702 FUNCTION config_uis_for_products (product_key IN VARCHAR2_TBL_TYPE,
2703 config_lookup_date IN DATE_TBL_TYPE,
2704 ui_type IN OUT NOCOPY VARCHAR2_TBL_TYPE,
2705 calling_application_id IN NUMBER_TBL_TYPE,
2706 usage_name IN VARCHAR2_TBL_TYPE,
2707 publication_mode IN VARCHAR2_TBL_TYPE,
2708 language IN VARCHAR2_TBL_TYPE
2709 )
2710 RETURN NUMBER_TBL_TYPE
2711 IS
2712
2713 t_uis_for_products NUMBER_TBL_TYPE := NUMBER_TBL_TYPE();
2714
2715 nof_product_key NUMBER;
2716 nof_config_lookup_date NUMBER;
2717 nof_ui_type NUMBER;
2718 nof_calling_application_id NUMBER;
2719 nof_usage_name NUMBER;
2720 nof_language NUMBER;
2721 nof_publication_mode NUMBER;
2722
2723 BEGIN
2724
2725 nof_product_key := product_key.COUNT;
2726 nof_config_lookup_date := config_lookup_date.COUNT;
2727 nof_ui_type := ui_type.COUNT;
2728 nof_calling_application_id := calling_application_id.COUNT;
2729 nof_usage_name := usage_name.COUNT;
2730 nof_language := language.COUNT;
2731 nof_publication_mode := publication_mode.COUNT;
2732
2733 IF ( (nof_product_key <> nof_config_lookup_date) OR
2734 (nof_product_key <> nof_ui_type) OR
2735 (nof_product_key <> nof_calling_application_id) OR
2736 (nof_product_key <> nof_usage_name) OR
2737 (nof_product_key <> nof_language) OR
2738 (nof_product_key <> nof_publication_mode) ) THEN
2739 RAISE WRONG_ARRAYS_LENGTH;
2740 END IF;
2741
2742 t_uis_for_products.extend(nof_product_key);
2743
2744 FOR i IN 1..nof_product_key LOOP
2745 t_uis_for_products(i) := config_ui_for_product(product_key(i), config_lookup_date(i),
2746 ui_type(i), calling_application_id(i), usage_name(i), publication_mode(i), language(i));
2747 END LOOP;
2748 RETURN t_uis_for_products;
2749 EXCEPTION
2750 WHEN WRONG_ARRAYS_LENGTH THEN
2751 -- xERROR:=CZ_UTILS.REPORT('The size of input arrays should be the same',1,'CZ_CF_API.CONFIG_UIS_FOR_PRODUCTS',11222);
2752 cz_utils.log_report('CZ_CF_API', 'config_uis_for_products', null,
2753 'The size of input arrays should be the same', fnd_log.LEVEL_EXCEPTION);
2754 RAISE_APPLICATION_ERROR (-20001,
2755 'The size of input arrays should be the same');
2756 WHEN OTHERS THEN
2757 -- xERROR:=CZ_UTILS.REPORT(SQLERRM,1,'CZ_CF_API.CONFIG_UIS_FOR_PRODUCTS',11222);
2758 cz_utils.log_report('CZ_CF_API', 'config_uis_for_products', null,
2759 SQLERRM, fnd_log.LEVEL_UNEXPECTED);
2760 END config_uis_for_products;
2761
2762 -----------------------------------------------------------------------------------
2763
2764 FUNCTION publication_for_item ( inventory_item_id IN NUMBER,
2765 organization_id IN NUMBER,
2766 config_lookup_date IN DATE,
2767 calling_application_id IN NUMBER,
2768 usage_name IN VARCHAR2,
2769 publication_mode IN VARCHAR2 DEFAULT NULL,
2770 language IN VARCHAR2 DEFAULT NULL
2771 )
2772 RETURN NUMBER
2773 IS
2774
2775 v_inventory_item_id NUMBER := inventory_item_id ;
2776 v_organization_id NUMBER := organization_id ;
2777 v_config_lookup_date DATE := config_lookup_date ;
2778 v_usage_id NUMBER ;
2779 v_language VARCHAR2(4) := language;
2780 v_publication_mode VARCHAR2(1) := publication_mode ;
2781 v_usage_name VARCHAR2(255) := usage_name ;
2782 v_application_id NUMBER := calling_application_id;
2783 v_source_target_flag VARCHAR2(3) := 'T' ;
2784 v_publication_id NUMBER ;
2785 v_pb_count NUMBER ;
2786 c_inventory_item_id NUMBER ;
2787 c_organization_id NUMBER;
2788
2789 CURSOR pub_cur IS
2790 SELECT publication_id
2791 FROM cz_model_applicabilities_v
2792 WHERE inventory_item_id = v_inventory_item_id
2793 AND bom_explosion_org_id = v_organization_id
2794 AND UPPER(publication_mode) = LTRIM(RTRIM(UPPER(v_publication_mode)))
2795 AND fnd_application_id = v_application_id
2796 AND usage_id = v_usage_id
2797 AND Source_Target_Flag = v_source_target_flag
2798 AND deleted_flag = '0'
2799 AND language = v_language
2800 AND (start_date <= v_config_lookup_date)
2801 AND (v_config_lookup_date < disable_date)
2802 ORDER BY start_date DESC;
2803
2804 CURSOR no_appl_pub_cur IS
2805 SELECT publication_id
2806 FROM cz_model_applicabilities_v
2807 WHERE inventory_item_id = v_inventory_item_id
2808 AND bom_explosion_org_id = v_organization_id
2809 AND UPPER(publication_mode) = LTRIM(RTRIM(UPPER(v_publication_mode)))
2810 AND usage_id = v_usage_id
2811 AND Source_Target_Flag = v_source_target_flag
2812 AND deleted_flag = '0'
2813 AND language = v_language
2814 AND start_date <= v_config_lookup_date
2815 AND v_config_lookup_date < disable_date
2816 ORDER BY start_date DESC;
2817
2818 BEGIN
2819
2820 -- Required because Istore passes FND_API.G_MISS_DATE
2821 IF v_config_lookup_date = FND_API.G_MISS_DATE THEN
2822 v_config_lookup_date := SYSDATE;
2823 END IF;
2824
2825 IF v_language IS NULL THEN
2826 SELECT userenv('LANG') INTO v_language FROM dual;
2827 END IF;
2828
2829 -- check usage_name: if null, get the profile option value from db
2830 IF v_usage_name IS NULL THEN
2831 fnd_profile.get('CZ_PUBLICATION_USAGE', v_usage_name);
2832 END IF;
2833
2834 v_usage_id := usage_id_from_usage_name(v_usage_name);
2835
2836 -- Keep these values to lookup common bill, if required
2837 if (c_application_id is null) then
2838 c_application_id := calling_application_id;
2839 end if;
2840 if (c_usage_name is null) then
2841 c_usage_name := v_usage_name;
2842 end if;
2843
2844 -- check publication mode: if null, get the profile option value from db.
2845 -- if still null, use the default 'P'.
2846 IF v_publication_mode IS NULL THEN
2847 fnd_profile.get('CZ_PUBLICATION_MODE', v_publication_mode);
2848 IF v_publication_mode IS NULL THEN
2849 v_publication_mode := 'P';
2850 END IF;
2851 END IF;
2852
2853 v_pb_count := 0;
2854 IF v_application_id IS NULL THEN
2855 OPEN no_appl_pub_cur;
2856 LOOP
2857 EXIT WHEN (no_appl_pub_cur%NOTFOUND OR v_pb_count > 0);
2858 FETCH no_appl_pub_cur INTO v_publication_id;
2859 v_pb_count := v_pb_count + 1;
2860 END LOOP;
2861 CLOSE no_appl_pub_cur;
2862 ELSE
2863 OPEN pub_cur;
2864 LOOP
2865 EXIT WHEN ( (pub_cur%NOTFOUND) OR (v_pb_count > 0) ) ;
2866 FETCH pub_cur INTO v_publication_id;
2867 v_pb_count := v_pb_count + 1;
2868 END LOOP;
2869 CLOSE pub_cur;
2870 END IF;
2871
2872 -- if not found check for "any usage" and/or "any application" publication
2873 -- or "common bill"
2874 IF v_publication_id IS NULL THEN
2875 IF v_usage_id <> ANY_USAGE_ID THEN
2876
2877 -- passing NULL is NOT the same as passing "any usage"
2878 RETURN publication_for_item(v_inventory_item_id, v_organization_id,
2879 v_config_lookup_date, v_application_id,
2880 ANY_USAGE_NAME, v_publication_mode, v_language);
2881 ELSIF v_application_id IS NOT NULL AND v_application_id <> ANY_APPLICATION_ID THEN
2882 RETURN publication_for_item(v_inventory_item_id, v_organization_id,
2883 v_config_lookup_date, ANY_APPLICATION_ID,
2884 v_usage_name, v_publication_mode, v_language);
2885 ELSE
2886 BEGIN
2887 -- else get publication id of the common bill, if any
2888 common_bill_for_item(v_inventory_item_id, v_organization_id,
2889 c_inventory_item_id, c_organization_id);
2890 IF (((c_inventory_item_id is not null) and (c_organization_id is not null))
2891 and ((c_inventory_item_id <> v_inventory_item_id) or (c_organization_id <> v_organization_id))) THEN
2892 RETURN publication_for_item(c_inventory_item_id, c_organization_id,
2893 v_config_lookup_date, c_application_id,
2894 c_usage_name, v_publication_mode, v_language);
2895 ELSE
2896 c_application_id := null;
2897 c_usage_name := null;
2898 RETURN NULL;
2899 END IF;
2900 END;
2901 END IF;
2902 ELSE
2903 c_application_id := null;
2904 c_usage_name := null;
2905 RETURN v_publication_id;
2906 END IF;
2907 END;
2908
2909 -----------------------------------------------------------------------------
2910 -- Retrieves inventory_item_id and organization_id of root bom referenced
2911 -- by the non bom model specified by the input model_id
2912 -- private
2913 PROCEDURE find_root_bom_inv_org(p_model_id IN NUMBER
2914 ,x_inventory_item_id OUT NOCOPY NUMBER
2915 ,x_organization_id OUT NOCOPY NUMBER)
2916 IS
2917 CURSOR reference_cursor IS
2918 SELECT prj.inventory_item_id, prj.organization_id
2919 FROM cz_devl_projects prj, cz_model_ref_expls expl
2920 WHERE expl.model_id = p_model_id
2921 AND expl.ps_node_type = PS_NODE_TYPE_REFERENCE
2922 AND expl.deleted_flag = '0'
2923 AND prj.devl_project_id = expl.component_id
2924 AND prj.deleted_flag = '0'
2925 AND prj.inventory_item_id IS NOT NULL
2926 AND prj.organization_id IS NOT NULL
2927 ORDER BY expl.node_depth;
2928 BEGIN
2929 FOR ref_rec IN reference_cursor LOOP
2930 EXIT WHEN reference_cursor%rowcount > 1 OR reference_cursor%NOTFOUND;
2931 x_inventory_item_id := ref_rec.inventory_item_id;
2932 x_organization_id := ref_rec.organization_id;
2933 END LOOP;
2934
2935 END find_root_bom_inv_org;
2936
2937 -----------------------------------------------------------------------------
2938 FUNCTION publication_for_saved_config (config_hdr_id IN NUMBER,
2939 config_rev_nbr IN NUMBER,
2940 config_lookup_date IN DATE,
2941 calling_application_id IN NUMBER,
2942 usage_name IN VARCHAR2,
2943 publication_mode IN VARCHAR2 DEFAULT NULL,
2944 language IN VARCHAR2 DEFAULT NULL
2945 )
2946 RETURN NUMBER
2947 IS
2948 v_config_hdr_id NUMBER := config_hdr_id;
2949 v_config_rev_nbr NUMBER := config_rev_nbr;
2950 v_inventory_item_id NUMBER;
2951 v_organization_id NUMBER;
2952 v_product_key cz_devl_projects.product_key%TYPE;
2953 v_component_id NUMBER;
2954 v_model_identifier cz_config_hdrs.model_identifier%TYPE;
2955 v_ndebug NUMBER := 0;
2956
2957 BEGIN
2958 BEGIN
2959 SELECT component_id, model_identifier
2960 INTO v_component_id, v_model_identifier
2961 FROM CZ_CONFIG_HDRS
2962 WHERE config_hdr_id = v_config_hdr_id AND config_rev_nbr = v_config_rev_nbr;
2963 EXCEPTION
2964 WHEN NO_DATA_FOUND THEN
2965 RETURN NULL;
2966 END;
2967 v_ndebug := 1;
2968
2969 IF v_model_identifier IS NOT NULL THEN
2970 v_inventory_item_id := SUBSTR(v_model_identifier, 1, INSTR(v_model_identifier, ':')-1);
2971 v_organization_id := SUBSTR(v_model_identifier, INSTR(v_model_identifier, ':')+1,
2972 INSTR(v_model_identifier, ':', 1, 2) - (INSTR(v_model_identifier, ':')+1));
2973
2974 ELSE
2975 -- Old logic for finding orig_sys_ref is flawed: orig_sys_ref could be null but there
2976 -- is a publication. If the model is a non-BOM or a mixed model in which the root is non
2977 -- BOM orig_sys_ref will be NULL. In the customer case of bug 2475218, a BOM reference
2978 -- was added to a copy of the original non-bom model. The fix for bug 2475218 was:
2979 -- New logic if the root ps node orig_sys_ref lookup fails:
2980 -- a. Look for model corresponding to cz_config_hdrs.component_id. If it exists, find
2981 -- its BOM model.
2982 -- b. If a. fails, iterate over all projects that have persistent_project_id equal to
2983 -- the config's persistent_component_id. Iterate until a root BOM model is found.
2984
2985 -- The way using orig_sys_ref is still flawed: a not null orig_sys_ref might not be
2986 -- used to get inv and org for publication lookup. For example, the generic imported
2987 -- contract model in bug 3189078 has orig_sys_ref of 510:TEMPLATEMODELTOPNODE:202:B:4352
2988 -- on the root but inv and org cannot be retrieved from it for pub lookup.
2989 -- The fix for 3189078: using product_key/inv/org info (added to project table in 21)
2990 -- instead of using cz_ps_nodes.orig_sys_ref. The basic logic is still the same except
2991 -- if found a product_key on a root project, we will lookup pub by the key, which
2992 -- depends on the fact that product_key is carried over from source model to published
2993 -- model during publishing.
2994 -- Future? could just use one query by persistent_component_id (modify prj_cursor)
2995 -- do the whole lookup
2996
2997 v_ndebug := 2;
2998 BEGIN
2999 SELECT inventory_item_id, organization_id, product_key
3000 INTO v_inventory_item_id, v_organization_id, v_product_key
3001 FROM CZ_DEVL_PROJECTS
3002 WHERE deleted_flag = '0' AND devl_project_id = v_component_id;
3003 EXCEPTION
3004 WHEN no_data_found THEN
3005 v_ndebug := 3;
3006 BEGIN
3007 SELECT inventory_item_id, organization_id
3008 INTO v_inventory_item_id, v_organization_id
3009 FROM cz_config_items
3010 WHERE config_hdr_id = v_config_hdr_id AND config_rev_nbr = v_config_rev_nbr
3011 AND deleted_flag = '0' AND inventory_item_id IS NOT NULL
3012 AND to_char(inventory_item_id) = node_identifier;
3013 EXCEPTION
3014 WHEN no_data_found THEN
3015 v_inventory_item_id := NULL;
3016 END;
3017 END;
3018 END IF;
3019
3020 IF v_product_key IS NOT NULL THEN
3021 RETURN publication_for_product(v_product_key
3022 ,config_lookup_date
3023 ,calling_application_id
3024 ,usage_name
3025 ,publication_mode
3026 ,language
3027 );
3028
3029 ELSIF v_inventory_item_id IS NOT NULL THEN
3030 RETURN publication_for_item(v_inventory_item_id
3031 ,v_organization_id
3032 ,config_lookup_date
3033 ,calling_application_id
3034 ,usage_name
3035 ,publication_mode
3036 ,language
3037 );
3038 ELSE
3039 RETURN NULL;
3040 END IF;
3041
3042 EXCEPTION
3043 WHEN OTHERS THEN
3044 cz_utils.log_report('CZ_CF_API', 'publication_for_saved_config', v_ndebug,
3045 SQLERRM, fnd_log.LEVEL_UNEXPECTED);
3046 RAISE;
3047 END publication_for_saved_config;
3048
3049 -----------------------------------------------------------------------------
3050
3051 FUNCTION publication_for_product (product_key IN VARCHAR2,
3052 config_lookup_date IN DATE,
3053 calling_application_id IN NUMBER,
3054 usage_name IN VARCHAR2,
3055 publication_mode IN VARCHAR2 DEFAULT NULL,
3056 language IN VARCHAR2 DEFAULT NULL
3057 )
3058 RETURN NUMBER
3059 IS
3060
3061 v_product_key VARCHAR2(40) := product_key ;
3062 v_config_lookup_date DATE := config_lookup_date ;
3063 v_usage_id NUMBER ;
3064 v_publication_mode VARCHAR2(1) := publication_mode ;
3065 v_usage_name VARCHAR2(255) := usage_name ;
3066 v_language VARCHAR2(4) := language;
3067 v_application_id NUMBER := calling_application_id;
3068 v_source_target_flag VARCHAR2(3) := 'T' ;
3069 v_publication_id NUMBER ;
3070 v_pb_count NUMBER;
3071 c_product_key VARCHAR2(40);
3072
3073 CURSOR pub_cur IS
3074 SELECT publication_id
3075 FROM cz_model_applicabilities_v
3076 WHERE product_key = v_product_key
3077 AND UPPER(publication_mode) = LTRIM(RTRIM(UPPER(v_publication_mode)))
3078 AND fnd_application_id = v_application_id
3079 AND usage_id = v_usage_id
3080 AND Source_Target_Flag = v_source_target_flag
3081 AND deleted_flag = '0'
3082 AND language = v_language
3083 AND (start_date <= v_config_lookup_date)
3084 AND (v_config_lookup_date < disable_date)
3085 ORDER BY start_date DESC;
3086 CURSOR no_appl_pub_cur IS
3087 SELECT publication_id
3088 FROM cz_model_applicabilities_v
3089 WHERE product_key = v_product_key
3090 AND UPPER(publication_mode) = LTRIM(RTRIM(UPPER(v_publication_mode)))
3091 AND usage_id = v_usage_id
3092 AND Source_Target_Flag = v_source_target_flag
3093 AND deleted_flag = '0'
3094 AND language = v_language
3095 AND (start_date <= v_config_lookup_date)
3096 AND (v_config_lookup_date < disable_date)
3097 ORDER BY start_date DESC;
3098
3099 BEGIN
3100
3101 -- Required because Istore passes FND_API.G_MISS_DATE
3102 IF v_config_lookup_date = FND_API.G_MISS_DATE THEN
3103 v_config_lookup_date := SYSDATE;
3104 END IF;
3105
3106 IF v_language IS NULL THEN
3107 SELECT userenv('LANG') INTO v_language FROM dual;
3108 END IF;
3109
3110 -- check usage_name: if null, get the profile option value from db
3111 IF v_usage_name IS NULL THEN
3112 fnd_profile.get('CZ_PUBLICATION_USAGE', v_usage_name);
3113 END IF;
3114
3115 v_usage_id := usage_id_from_usage_name(v_usage_name);
3116
3117 -- Keep these values to lookup common bill, if required
3118 if (c_application_id is null) then
3119 c_application_id := calling_application_id;
3120 end if;
3121 if (c_usage_name is null) then
3122 c_usage_name := v_usage_name;
3123 end if;
3124
3125 -- check publication mode: if null, get the profile option value from db.
3126 -- if still null, use the default 'P'.
3127 IF v_publication_mode IS NULL THEN
3128 fnd_profile.get('CZ_PUBLICATION_MODE', v_publication_mode);
3129 IF v_publication_mode IS NULL THEN
3130 v_publication_mode := 'P';
3131 END IF;
3132 END IF;
3133
3134 -- Bug 5103620- The count was not being
3135 -- initialized, and therefore restoring from saved configs
3136 -- returns the wrong publication record.
3137
3138 v_pb_count := 0;
3139 IF v_application_id IS NULL THEN
3140 OPEN no_appl_pub_cur;
3141 LOOP
3142 EXIT WHEN (no_appl_pub_cur%NOTFOUND OR v_pb_count > 0);
3143 FETCH no_appl_pub_cur INTO v_publication_id;
3144 v_pb_count := v_pb_count + 1;
3145 END LOOP;
3146 CLOSE no_appl_pub_cur;
3147 ELSE
3148 OPEN pub_cur;
3149 LOOP
3150 EXIT WHEN ( (pub_cur%NOTFOUND) OR (v_pb_count > 0) ) ;
3151 FETCH pub_cur INTO v_publication_id;
3152 v_pb_count := v_pb_count + 1;
3153 END LOOP;
3154 CLOSE pub_cur;
3155 END IF;
3156
3157 -- if not found check for "any usage" and/or "any application"
3158 -- publications or common bill
3159 IF v_publication_id IS NULL THEN
3160 IF v_usage_id <> ANY_USAGE_ID THEN
3161 -- passing NULL is NOT the same as passing "any usage"
3162 RETURN publication_for_product(v_product_key, v_config_lookup_date,
3163 v_application_id, ANY_USAGE_NAME,
3164 v_publication_mode, v_language);
3165 ELSIF v_application_id IS NOT NULL AND v_application_id <> ANY_APPLICATION_ID THEN
3166 RETURN publication_for_product(v_product_key, v_config_lookup_date,
3167 ANY_APPLICATION_ID,
3168 v_usage_name, v_publication_mode, v_language);
3169 ELSE
3170 common_bill_for_product(v_product_key, c_product_key);
3171 IF ((c_product_key is not null) and (c_product_key <> v_product_key)) THEN
3172 RETURN publication_for_product(c_product_key,v_config_lookup_date, c_application_id,
3173 c_usage_name, v_publication_mode, v_language);
3174 ELSE
3175 c_application_id := null;
3176 c_usage_name := null;
3177 RETURN NULL;
3178 END IF;
3179 END IF;
3180 ELSE
3181 c_application_id := null;
3182 c_usage_name := null;
3183 RETURN v_publication_id;
3184 END IF;
3185 END;
3186
3187 -------------------------------------------------------
3188
3189 PROCEDURE DEFAULT_NEW_CFG_DATES(p_creation_date IN OUT NOCOPY DATE,
3190 p_lookup_date IN OUT NOCOPY DATE,
3191 p_effective_date IN OUT NOCOPY DATE) IS
3192 BEGIN
3193 ----SELECT NVL(p_creation_date, SYSDATE) INTO p_creation_date FROM dual;
3194 IF (p_creation_date IS NULL) THEN
3195 p_creation_date := SYSDATE;
3196 END iF;
3197
3198 ----SELECT NVL(p_lookup_date, p_creation_date) INTO p_lookup_date FROM dual;
3199 IF (p_lookup_date IS NULL) THEN
3200 p_lookup_date := p_creation_date;
3201 END IF;
3202
3203 -----SELECT NVL(p_effective_date, p_creation_date) INTO p_effective_date FROM dual;
3204 IF (p_effective_date IS NULL) THEN
3205 p_effective_date := p_creation_date;
3206 END IF;
3207
3208 END DEFAULT_NEW_CFG_DATES;
3209
3210 -------------------------------------------------------
3211
3212 PROCEDURE DEFAULT_RESTORED_CFG_DATES(p_config_hdr_id IN NUMBER,
3213 p_config_rev_nbr IN NUMBER,
3214 p_creation_date IN OUT NOCOPY DATE,
3215 p_lookup_date IN OUT NOCOPY DATE,
3216 p_effective_date IN OUT NOCOPY DATE) IS
3217 l_config_creation_date DATE;
3218 l_config_effective_date DATE;
3219 l_rest_cfg_lookup_setting cz_db_settings.value%TYPE := ' ';
3220
3221 BEGIN
3222 IF p_config_hdr_id IS NULL OR p_config_rev_nbr IS NULL THEN
3223 RAISE_APPLICATION_ERROR(-20001, 'Config header ID AND config rev nbr ' ||
3224 'are required arguments TO CZ_CF_API.' ||
3225 'default_restored_cfg_dates');
3226 END IF;
3227
3228 BEGIN
3229 SELECT creation_date, effective_date INTO l_config_creation_date,
3230 l_config_effective_date FROM CZ_CONFIG_HDRS WHERE config_hdr_id =
3231 p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr;
3232 EXCEPTION
3233 WHEN NO_DATA_FOUND THEN
3234 -- just set l_config_creation_date and l_config_effective_date
3235 -- to NULL, since it is not the role
3236 -- of this procedure to report missing configurations
3237 l_config_effective_date := NULL;
3238 l_config_creation_date := NULL;
3239 END;
3240
3241 -- config creation date defaults to creation date of saved config
3242 SELECT NVL(p_creation_date, NVL(l_config_creation_date, SYSDATE)) INTO
3243 p_creation_date FROM dual;
3244
3245 --- get value for setting_id RestoredConfigDefaultModelLookupDate
3246 --- bug# 2406680 fix for Agilent
3247 --- Section_name for this should be ORAAPPS_INTEGRATE. Not adding this condition since it could break
3248 --- at a customer site.
3249 BEGIN
3250 SELECT value
3251 INTO l_rest_cfg_lookup_setting
3252 FROM cz_db_settings WHERE setting_id = 'RestoredConfigDefaultModelLookupDate';
3253 EXCEPTION
3254 WHEN OTHERS THEN
3255 l_rest_cfg_lookup_setting := NULL;
3256 END;
3257
3258 ---if l_rest_cfg_lookup_setting is set and p_lookup_date is NULL
3259 --- then use config creation date else use sysdate
3260 -- lookup date defaults to sysdate
3261 IF ( ( UPPER(LTRIM(RTRIM(l_rest_cfg_lookup_setting))) = UPPER('config_creation_date') )
3262 AND (p_lookup_date IS NULL) ) THEN
3263 p_lookup_date := p_creation_date ;
3264 ELSE
3265 SELECT NVL(p_lookup_date, SYSDATE) INTO p_lookup_date FROM dual;
3266 END IF;
3267 -- effective date defaults to effective date of saved config
3268 SELECT NVL(p_effective_date, NVL(l_config_effective_date, SYSDATE)) INTO
3269 p_effective_date FROM dual;
3270 END DEFAULT_RESTORED_CFG_DATES;
3271
3272 -------------------------------------------------
3273 FUNCTION icx_session_ticket RETURN VARCHAR2 IS
3274 PRAGMA AUTONOMOUS_TRANSACTION;
3275 l_user_id NUMBER;
3276 l_resp_id NUMBER;
3277 l_resp_appl_id NUMBER;
3278 l_session_id NUMBER;
3279 l_icx_exc EXCEPTION;
3280 BEGIN
3281 l_user_id := fnd_profile.value('USER_ID');
3282 l_resp_id := fnd_profile.value('RESP_ID');
3283 l_resp_appl_id := fnd_profile.value('RESP_APPL_ID');
3284 l_session_id := FND_SESSION_MANAGEMENT.g_session_id;
3285 IF (l_session_id = -1) THEN
3286 IF l_user_id IS NULL OR l_resp_id IS NULL OR l_resp_appl_id IS NULL THEN
3287 RAISE l_icx_exc;
3288 END IF;
3289 l_session_id := fnd_session_management.createsession(l_user_id);
3290 END IF;
3291
3292 IF l_session_id = -1 THEN
3293 RAISE l_icx_exc;
3294 ELSE
3295 COMMIT;
3296 END IF;
3297 RETURN icx_call.encrypt3(l_session_id);
3298 EXCEPTION
3299 WHEN l_icx_exc THEN
3300 COMMIT;
3301 RETURN NULL;
3302 END icx_session_ticket;
3303 ----------------------------
3304 FUNCTION icx_session_ticket (p_session_id IN NUMBER)
3305 RETURN VARCHAR2 IS
3306 BEGIN
3307 RETURN icx_session_ticket;
3308 END icx_session_ticket;
3309
3310 ------------------------------------------------------------------------------------------------
3311
3312 PROCEDURE common_bill_for_item ( in_inventory_item_id IN NUMBER,
3313 in_organization_id IN NUMBER,
3314 common_inventory_item_id OUT NOCOPY NUMBER,
3315 common_organization_id OUT NOCOPY NUMBER
3316 )
3317 IS
3318
3319 BEGIN
3320
3321 select ORGANIZATION_ID, ASSEMBLY_ITEM_ID
3322 into COMMON_ORGANIZATION_ID, COMMON_INVENTORY_ITEM_ID
3323 from BOM_BILL_OF_MATERIALS
3324 where BILL_SEQUENCE_ID in (select SOURCE_BILL_SEQUENCE_ID from BOM_BILL_OF_MATERIALS
3325 where ORGANIZATION_ID = in_organization_id
3326 and ASSEMBLY_ITEM_ID = in_inventory_item_id);
3327
3328
3329 EXCEPTION
3330 WHEN OTHERS THEN
3331 -- xERROR := cz_utils.REPORT('No common bill found for ' || in_inventory_item_id || ' ' || SQLERRM, 1, 'CZ_CF_API.common_bill_for_item', NULL);
3332 cz_utils.log_report('CZ_CF_API', 'common_bill_for_item', null,
3333 'No common bill found for ' || in_inventory_item_id || ' ' || SQLERRM,
3334 fnd_log.LEVEL_UNEXPECTED);
3335 END common_bill_for_item;
3336
3337 ------------------------------------------------------------------------------------------------
3338
3339 PROCEDURE common_bill_for_product(v_product_key IN VARCHAR2, c_product_key OUT NOCOPY VARCHAR2)
3340 IS
3341
3342 v_inventory_item_id NUMBER;
3343 v_organization_id NUMBER;
3344 c_inventory_item_id NUMBER;
3345 c_organization_id NUMBER;
3346
3347 BEGIN
3348
3349 --get inv and org id from product key
3350 v_organization_id := to_number(substr(v_product_key,1,instr(v_product_key,':')-1));
3351 v_inventory_item_id := to_number(substr(v_product_key,instr(v_product_key,':')+1));
3352
3353 common_bill_for_item(v_inventory_item_id,v_organization_id,c_inventory_item_id,c_organization_id);
3354
3355 -- build the product key for the common bill
3356 c_product_key := c_organization_id || ':' || c_inventory_item_id;
3357
3358
3359 EXCEPTION
3360 WHEN OTHERS THEN
3361 -- xERROR := cz_utils.REPORT('No common bill found for ' || v_product_key || ' ' || SQLERRM, 1, 'CZ_CF_API.common_bill_for_product', NULL);
3362 cz_utils.log_report('CZ_CF_API', 'common_bill_for_product', null,
3363 'No common bill found for ' || v_product_key || ' ' || SQLERRM,
3364 fnd_log.LEVEL_UNEXPECTED);
3365 END common_bill_for_product;
3366
3367 --------------------------------------------------------------------------------
3368 PROCEDURE pub_for_item_mobile_pvt
3369 (p_inventory_item_id IN NUMBER
3370 ,p_organization_id IN NUMBER
3371 ,p_application_id IN NUMBER
3372 ,p_usage_id IN NUMBER
3373 ,p_language IN VARCHAR2
3374 ,p_pub_start_date IN DATE
3375 ,p_pub_end_date IN DATE
3376 ,x_publication_ids OUT NOCOPY number_tbl_indexby_type
3377 ,x_model_ids OUT NOCOPY number_tbl_indexby_type
3378 ,x_ui_def_ids OUT NOCOPY number_tbl_indexby_type
3379 ,x_start_dates OUT NOCOPY date_tbl_indexby_type
3380 ,x_last_update_dates OUT NOCOPY date_tbl_indexby_type
3381 ,x_model_type OUT NOCOPY VARCHAR2
3382 )
3383 IS
3384 TYPE model_type_tbl_type IS TABLE OF cz_devl_projects.model_type%TYPE
3385 INDEX BY BINARY_INTEGER;
3386 l_model_type_tbl model_type_tbl_type;
3387 BEGIN
3388 SELECT pub.publication_id, pub.model_id, pub.ui_def_id, pub.start_date,
3389 pub.last_update_date, prj.model_type
3390 BULK COLLECT INTO x_publication_ids, x_model_ids, x_ui_def_ids,
3391 x_start_dates, x_last_update_dates, l_model_type_tbl
3392 FROM cz_model_applicabilities_v pub, cz_devl_projects prj
3393 WHERE pub.model_id = prj.devl_project_id
3394 AND prj.deleted_flag ='0' AND pub.deleted_flag = '0'
3395 AND pub.inventory_item_id = p_inventory_item_id
3396 AND bom_explosion_org_id = p_organization_id
3397 AND fnd_application_id = p_application_id
3398 AND usage_id = p_usage_id AND language = p_language
3399 AND publication_mode = cz_api_pub.G_PRODUCTION_PUB_MODE
3400 AND source_target_flag = TARGET_PUBLICATION
3401 AND ui_style = UI_STYLE_DHTML
3402 AND ( (p_pub_start_date >= start_date AND p_pub_start_date < disable_date) OR
3403 (P_pub_end_date > start_date AND p_pub_end_date <= disable_date) OR
3404 (start_date >= p_pub_start_date AND start_date < p_pub_end_date) OR
3405 (disable_date > p_pub_start_date AND disable_date <= p_pub_end_date) );
3406
3407 x_model_type := l_model_type_tbl(1);
3408 EXCEPTION
3409 WHEN NO_DATA_FOUND THEN
3410 NULL;
3411 END pub_for_item_mobile_pvt;
3412
3413 --------------------------------------------------------------------------------
3414 PROCEDURE publication_for_item_mobile
3415 (p_inventory_item_id IN NUMBER
3416 ,p_organization_id IN NUMBER
3417 ,p_calling_application_id IN NUMBER
3418 ,p_usage_name IN VARCHAR2
3419 ,p_pub_start_date IN DATE
3420 ,p_pub_end_date IN DATE
3421 ,x_publication_id_tbl OUT NOCOPY number_tbl_indexby_type
3422 ,x_model_id_tbl OUT NOCOPY number_tbl_indexby_type
3423 ,x_ui_def_id_tbl OUT NOCOPY number_tbl_indexby_type
3424 ,x_start_date_tbl OUT NOCOPY date_tbl_indexby_type
3425 ,x_last_update_date_tbl OUT NOCOPY date_tbl_indexby_type
3426 ,x_model_type OUT NOCOPY VARCHAR2
3427 )
3428 IS
3429 l_usage_name VARCHAR2(255) := p_usage_name;
3430 l_usage_id NUMBER;
3431 l_language VARCHAR2(4) := userenv('LANG');
3432 l_inventory_item_id NUMBER ;
3433 l_organization_id NUMBER;
3434
3435 BEGIN
3436 IF l_usage_name IS NULL THEN
3437 fnd_profile.get('CZ_PUBLICATION_USAGE', l_usage_name);
3438 END IF;
3439 l_usage_id := usage_id_from_usage_name(l_usage_name);
3440
3441 -- Keep these values to lookup common bill, if required
3442 IF (c_application_id IS NULL) THEN
3443 c_application_id := p_calling_application_id;
3444 END IF;
3445 IF (c_usage_name IS NULL) THEN
3446 c_usage_name := l_usage_name;
3447 END IF;
3448
3449 pub_for_item_mobile_pvt(p_inventory_item_id
3450 ,p_organization_id
3451 ,p_calling_application_id
3452 ,l_usage_id
3453 ,l_language
3454 ,p_pub_start_date
3455 ,p_pub_end_date
3456 ,x_publication_id_tbl
3457 ,x_model_id_tbl
3458 ,x_ui_def_id_tbl
3459 ,x_start_date_tbl
3460 ,x_last_update_date_tbl
3461 ,x_model_type
3462 );
3463
3464 IF (x_publication_id_tbl.COUNT = 0) THEN
3465 IF l_usage_id <> ANY_USAGE_ID THEN
3466 -- passing NULL is NOT the same as passing "any usage"
3467 pub_for_item_mobile_pvt(p_inventory_item_id
3468 ,p_organization_id
3469 ,p_calling_application_id
3470 ,ANY_USAGE_ID
3471 ,l_language
3472 ,p_pub_start_date
3473 ,p_pub_end_date
3474 ,x_publication_id_tbl
3475 ,x_model_id_tbl
3476 ,x_ui_def_id_tbl
3477 ,x_start_date_tbl
3478 ,x_last_update_date_tbl
3479 ,x_model_type
3480 );
3481 ELSE
3482 -- else get publication id of the common bill, if any
3483 common_bill_for_item(p_inventory_item_id, p_organization_id,
3484 l_inventory_item_id, l_organization_id);
3485 IF ( l_inventory_item_id IS NOT NULL AND
3486 l_organization_id IS NOT NULL AND
3487 (l_inventory_item_id <> p_inventory_item_id OR
3488 l_organization_id <> p_organization_id) ) THEN
3489 pub_for_item_mobile_pvt(l_inventory_item_id
3490 ,l_organization_id
3491 ,c_application_id
3492 ,c_usage_name
3493 ,l_language
3494 ,p_pub_start_date
3495 ,p_pub_end_date
3496 ,x_publication_id_tbl
3497 ,x_model_id_tbl
3498 ,x_ui_def_id_tbl
3499 ,x_start_date_tbl
3500 ,x_last_update_date_tbl
3501 ,x_model_type
3502 );
3503 ELSE
3504 c_application_id := NULL;
3505 c_usage_name := NULL;
3506 END IF;
3507 END IF;
3508 ELSE
3509 c_application_id := NULL;
3510 c_usage_name := NULL;
3511 END IF;
3512
3513 END publication_for_item_mobile;
3514 --------------------------------------------------------------------------------
3515
3516 BEGIN
3517 id_increment := cz_utils.conv_num(get_db_setting('SCHEMA', 'ORACLESEQUENCEINCR'));
3518 IF id_increment IS NULL THEN
3519 id_increment := default_incr;
3520 END IF;
3521
3522 BEGIN
3523 transferTimeout := To_number(get_db_setting('SCHEMA', 'UTLHTTPTRANSFERTIMEOUT'));
3524 EXECUTE IMMEDIATE 'BEGIN UTL_HTTP.GET_TRANSFER_TIMEOUT(:1); END;' USING IN OUT defaultTimeout;
3525 EXCEPTION
3526 WHEN OTHERS THEN
3527 transferTimeout := NULL;
3528 defaultTimeout := NULL;
3529 END;
3530 END CZ_CF_API;