1 PACKAGE BODY cz_prc_callback_util AS
2 /* $Header: czprcbub.pls 115.16 2004/03/02 15:42:31 askhacha ship $ */
3
4 COMP_CODE_DELIMITER CONSTANT VARCHAR2(1) := '-';
5
6 -- Value used for parent_config_item_id when root is a BOM model (hack for iStore).
7 NO_PARENT_VALUE CONSTANT INTEGER := -1;
8
9 PROCEDURE insert_pricing_table (p_pricing_tbl IN system.cz_price_tbl_type)
10 IS
11 PRAGMA AUTONOMOUS_TRANSACTION;
12
13 BEGIN
14
15 FOR i IN 1..p_pricing_tbl.COUNT LOOP
16 INSERT INTO CZ_PRICING_STRUCTURES (configurator_session_key,
17 seq_nbr,
18 ps_node_id,
19 item_key,
20 item_key_type,
21 quantity,
22 uom_code,
23 config_item_id,
24 parent_config_item_id)
25 VALUES (p_pricing_tbl(i).config_session_key,
26 p_pricing_tbl(i).seq_nbr,
27 p_pricing_tbl(i).ps_node_id,
28 nvl(p_pricing_tbl(i).item_key, to_char(p_pricing_tbl(i).ps_node_id)),
29 decode(p_pricing_tbl(i).item_key, NULL, G_ITEM_KEY_PS_NODE,
30 G_ITEM_KEY_BOM_NODE),
31 p_pricing_tbl(i).quantity,
32 p_pricing_tbl(i).uom_code,
33 p_pricing_tbl(i).config_item_id,
34 decode(p_pricing_tbl(i).parent_config_item_id, NO_PARENT_VALUE, NULL,
35 p_pricing_tbl(i).parent_config_item_id));
36 END LOOP;
37 COMMIT;
38 END insert_pricing_table;
39
40 PROCEDURE insert_pricing_rec (p_config_session_key IN VARCHAR2,
41 p_seq_nbr IN NUMBER,
42 p_ps_node_id IN NUMBER,
43 p_item_key IN VARCHAR2,
44 p_quantity IN NUMBER,
45 p_uom_code IN VARCHAR2,
46 p_config_item_id IN NUMBER,
47 p_parent_config_item_id IN NUMBER) IS
48 PRAGMA AUTONOMOUS_TRANSACTION;
49 l_item_key_type NUMBER;
50 l_item_key cz_pricing_structures.item_key%TYPE;
51
52 BEGIN
53
54 IF p_item_key IS NULL THEN
55 l_item_key_type := G_ITEM_KEY_PS_NODE;
56 l_item_key := TO_CHAR(p_ps_node_id);
57 ELSE
58 l_item_key_type := G_ITEM_KEY_BOM_NODE;
59 l_item_key := p_item_key;
60 END IF;
61
62 INSERT INTO cz_pricing_structures (configurator_session_key,
63 seq_nbr,
64 ps_node_id, item_key,
65 item_key_type, quantity,
66 uom_code, config_item_id,
67 parent_config_item_id)
68 VALUES (p_config_session_key,
69 p_seq_nbr,
70 p_ps_node_id, l_item_key,
71 l_item_key_type, p_quantity, p_uom_code, p_config_item_id,
72 decode(p_parent_config_item_id, NO_PARENT_VALUE, NULL,
73 p_parent_config_item_id));
74 COMMIT;
75 END insert_pricing_rec;
76
77 PROCEDURE delete_pricing_recs (p_config_session_key IN VARCHAR2) IS
78 PRAGMA AUTONOMOUS_TRANSACTION;
79 BEGIN
80 DELETE FROM CZ_PRICING_STRUCTURES
81 WHERE configurator_session_key = p_config_session_key;
82 COMMIT;
83 END delete_pricing_recs;
84
85
86 PROCEDURE run_pricing_callback(p_pkg_name IN VARCHAR2,
87 p_proc_name IN VARCHAR2,
88 p_config_session_key IN VARCHAR2,
89 p_price_type IN VARCHAR2,
90 p_total_price OUT NOCOPY NUMBER) IS
91 PRAGMA AUTONOMOUS_TRANSACTION;
92 block_1 VARCHAR2(100);
93 block_2 VARCHAR2(100);
94 block_3 VARCHAR2(100);
95 final_block VARCHAR2(300);
96 BEGIN
97
98 block_1 := 'begin ' || p_pkg_name || '.' || p_proc_name;
99 block_2 := '(:configurator_session_key, :price_type, :config_total_price); ';
100 block_3 := 'end;';
101 final_block := block_1 || block_2 || block_3;
102
103 EXECUTE IMMEDIATE final_block USING IN p_config_session_key, IN p_price_type,
104 OUT p_total_price;
105
106 COMMIT;
107
108 END run_pricing_callback;
109
110 PROCEDURE run_mls_pricing_callback(p_pkg_name IN VARCHAR2,
111 p_proc_name IN VARCHAR2,
112 p_config_session_key IN VARCHAR2,
113 p_price_type IN VARCHAR2,
114 p_total_price OUT NOCOPY NUMBER,
115 p_currency_code OUT NOCOPY VARCHAR2) IS
116 PRAGMA AUTONOMOUS_TRANSACTION;
117 block_1 VARCHAR2(100);
118 block_2 VARCHAR2(100);
119 block_3 VARCHAR2(100);
120 block_4 VARCHAR2(100);
121 final_block VARCHAR2(400);
122 BEGIN
123
124 block_1 := 'begin ' || p_pkg_name || '.' || p_proc_name;
125 block_2 := '(:configurator_session_key, :price_type, :config_total_price, ';
126 block_3 := ':currency_code); ';
127 block_4 := 'end;';
128 final_block := block_1 || block_2 || block_3 || block_4;
129
130 EXECUTE IMMEDIATE final_block USING IN p_config_session_key, IN p_price_type,
131 OUT p_total_price, OUT p_currency_code;
132
133 COMMIT;
134
135 END run_mls_pricing_callback;
136
137 --This function returns the config_item_id of the root BOM model in a configuration
138 --identified by the p_config_session_id parameter.
139
140 FUNCTION root_bom_config_item_id(p_config_session_key IN VARCHAR2) RETURN NUMBER IS
141
142 TYPE typeItemKeyTable IS TABLE OF cz_pricing_structures.item_key%TYPE INDEX BY BINARY_INTEGER;
143 TYPE typeItemIdTable IS TABLE OF cz_pricing_structures.config_item_id%TYPE INDEX BY BINARY_INTEGER;
144
145 tabItemKey typeItemKeyTable;
146 tabConfigItemId typeItemIdTable;
147 vComponentCode cz_pricing_structures.item_key%TYPE;
148
149 BEGIN
150
151 --Read only the configuration lines corresponding to BOM items.
152
153 SELECT item_key, config_item_id BULK COLLECT INTO tabItemKey, tabConfigItemId
154 FROM cz_pricing_structures
155 WHERE configurator_session_key = p_config_session_key
156 AND item_key_type = G_ITEM_KEY_BOM_NODE;
157
158 FOR i IN 1..tabItemKey.COUNT LOOP
159
160 vComponentCode := cz_atp_callback_util.component_code_from_item_key(tabItemKey(i));
161
162 IF(INSTR(vComponentCode, COMP_CODE_DELIMITER) = 0)THEN
163
164 --For every line, we extract the component code and see if it contains the delimiter
165 --symbol. If there is no delimiter, we assume the component code is just a single id
166 --and so this is the root model. As soon as one such line found we exit.
167
168 RETURN tabConfigItemId(i);
169 END IF;
170 END LOOP;
171
172 --No root model found, return NULL.
173
174 RETURN NULL;
175
176 EXCEPTION
177 WHEN OTHERS THEN
178
179 --Any error occured, return NULL.
180
181 RETURN NULL;
182 END root_bom_config_item_id;
183
184 END cz_prc_callback_util;
[Home] [Help]