1 PACKAGE BODY cz_atp_callback_util AS
2 /* $Header: czatpcub.pls 120.1 2008/01/09 16:20:06 qmao ship $ */
3
4 ITEM_KEY_DELIMITER CONSTANT VARCHAR2(1) := ':';
5 COMP_CODE_DELIMITER CONSTANT VARCHAR2(1) := '-';
6
7 -- Value used for parent_config_item_id when root is a BOM model (hack for iStore).
8 NO_PARENT_VALUE CONSTANT INTEGER := -1;
9
10 ------------------------------------------------------------------------------
11 FUNCTION org_id_from_item_key(p_item_key IN VARCHAR2)
12 RETURN NUMBER
13 IS
14 l_temp VARCHAR2(100);
15 l_result VARCHAR2(100);
16 BEGIN
17 IF (p_item_key IS NULL) THEN
18 RETURN NULL;
19 END IF;
20
21 l_temp := substr(p_item_key, instr(p_item_key, ITEM_KEY_DELIMITER, -1, 2)+1);
22 l_result := substr(l_temp, 1, instr(l_temp, ITEM_KEY_DELIMITER)-1);
23 RETURN cz_utils.conv_num(l_result);
24 END org_id_from_item_key;
25
26 ------------------------------------------------------------------------------
27 PROCEDURE delete_atp_recs (p_config_session_key IN VARCHAR2) IS
28 PRAGMA AUTONOMOUS_TRANSACTION;
29 BEGIN
30 DELETE FROM cz_atp_requests WHERE configurator_session_key = p_config_session_key;
31 COMMIT;
32 END delete_atp_recs;
33
34 PROCEDURE insert_atp_rec (p_config_session_key IN VARCHAR2,
35 p_seq_nbr IN NUMBER,
36 p_ps_node_id IN NUMBER,
37 p_item_key IN VARCHAR2,
38 p_quantity IN NUMBER,
39 p_uom_code IN VARCHAR2,
40 p_config_item_id IN NUMBER,
41 p_parent_config_item_id IN NUMBER,
42 p_ato_config_item_id IN NUMBER,
43 p_component_sequence_id IN NUMBER) IS
44 PRAGMA AUTONOMOUS_TRANSACTION;
45 l_item_key_type NUMBER;
46 l_item_id NUMBER;
47 l_org_id NUMBER;
48 l_component_sequence_id NUMBER := p_component_sequence_id;
49
50 BEGIN
51 IF p_item_key IS NULL THEN
52 l_item_key_type := G_ITEM_KEY_PS_NODE;
53 ELSE
54 l_item_key_type := G_ITEM_KEY_BOM_NODE;
55 END IF;
56
57 IF (l_component_sequence_id IS NULL AND
58 p_item_key IS NOT NULL) THEN
59 l_item_id := inv_item_id_from_item_key(p_item_key);
60 l_org_id := org_id_from_item_key(p_item_key);
61
62 BEGIN
63 SELECT bill_sequence_id INTO l_component_sequence_id
64 FROM bom_bill_of_materials
65 WHERE assembly_item_id = l_item_id
66 AND organization_id = l_org_id AND alternate_bom_designator IS NULL;
67 EXCEPTION
68 WHEN NO_DATA_FOUND THEN
69 RAISE_APPLICATION_ERROR(-20001, 'Error in retrieving component_sequence_id for ' ||
70 'config item ' || p_config_item_id || ': no bill_sequence_id found for ' ||
71 'inventory item ' || l_item_id || ' with organization_id ' || l_org_id);
72 END;
73 END IF;
74
75 INSERT INTO CZ_ATP_REQUESTS (atp_request_id,
76 configurator_session_key,
77 seq_no,
78 ps_node_id,
79 item_key, item_key_type,
80 quantity, uom_code,
81 config_item_id, parent_config_item_id,
82 ato_config_item_id,
83 component_sequence_id)
84 VALUES (cz_atp_requests_s.nextval, p_config_session_key, p_seq_nbr,
85 p_ps_node_id, p_item_key, l_item_key_type, p_quantity, p_uom_code,
86 p_config_item_id,
87 decode(p_parent_config_item_id, NO_PARENT_VALUE, NULL,
88 p_parent_config_item_id),
89 p_ato_config_item_id,
90 l_component_sequence_id);
91
92 COMMIT;
93 END insert_atp_rec;
94
95
96 PROCEDURE run_atp_callback(p_pkg_name IN VARCHAR2,
97 p_proc_name IN VARCHAR2,
98 p_config_session_key IN VARCHAR2,
99 p_warehouse_id IN NUMBER,
100 p_ship_to_org_id IN NUMBER,
101 p_customer_id IN NUMBER,
102 p_customer_site_id IN NUMBER,
103 p_requested_date IN DATE,
104 p_ship_to_group_date OUT NOCOPY DATE) IS
105 PRAGMA AUTONOMOUS_TRANSACTION;
106 block_1 VARCHAR2(100);
107 block_2 VARCHAR2(100);
108 block_3 VARCHAR2(100);
109 block_4 VARCHAR2(100);
110 block_5 VARCHAR2(100);
111 final_block VARCHAR2(500);
112 BEGIN
113
114 block_1 := 'begin ' || p_pkg_name || '.' || p_proc_name;
115 block_2 := '(:configurator_session_key, :warehouse_id, :ship_to_org, ';
116 block_3 := ':customer_id, :customer_site_id, :requested_date, ';
117 block_4 := ':ship_to_group_date); ';
118 block_5 := 'end;';
119 final_block := block_1 || block_2 || block_3 || block_4 || block_5;
120
121 EXECUTE IMMEDIATE final_block USING IN p_config_session_key,
122 IN p_warehouse_id, IN p_ship_to_org_id, IN p_customer_id,
123 IN p_customer_site_id, IN p_requested_date, OUT p_ship_to_group_date;
124
125 COMMIT;
126
127 END run_atp_callback;
128
129 -- Returns inventory item id from item_key, which should have the
130 -- form [component code]:[explosion type]:[org ID]:[top item id].
131 -- Component code is a concatenation of inv item IDs with "-" in
132 -- between each pair. We're interested in the last ID in the
133 -- component code.
134 -- Returns null if item_id can't be found
135 FUNCTION inv_item_id_from_item_key(p_item_key IN VARCHAR2)
136 RETURN NUMBER IS
137
138 l_item_id_str cz_atp_requests.item_key%TYPE;
139
140 BEGIN
141
142 IF p_item_key IS NULL THEN
143 return NULL;
144 END IF;
145
146 SELECT
147 substr(component_code_from_item_key(p_item_key),
148 instr(decode(substr(p_item_key,1,3),'PRJ',null,'PRD',
149 substr(p_item_key,
150 instr(p_item_key,ITEM_KEY_DELIMITER,-1,1)+1),
151 substr(p_item_key,1,instr(p_item_key,ITEM_KEY_DELIMITER)-1)),
152 COMP_CODE_DELIMITER,-1,1)+1) INTO l_item_id_str FROM dual;
153
154 return cz_utils.conv_num(l_item_id_str);
155
156 END inv_item_id_from_item_key;
157
158
159 FUNCTION component_code_from_item_key(p_item_key IN VARCHAR2)
160 RETURN VARCHAR2 IS
161
162 l_comp_code_str bom_explosions.component_code%TYPE;
163
164 BEGIN
165 IF p_item_key IS NULL THEN
166 return NULL;
167 END IF;
168
169 SELECT
170 decode(substr(p_item_key,1,3),
171 'PRJ',null,
172 'PRD',
173 substr(p_item_key,
174 instr(p_item_key,ITEM_KEY_DELIMITER,-1,1)+1),
175 substr(p_item_key,1,instr(p_item_key,ITEM_KEY_DELIMITER)-1))
176 INTO l_comp_code_str FROM dual;
177
178 return l_comp_code_str;
179 END component_code_from_item_key;
180
181 FUNCTION break_str_by_delim(p_input_str IN VARCHAR2,
182 p_delimiter IN VARCHAR2)
183 RETURN char30_arr IS
184
185 l_input_str VARCHAR2(2000) := p_input_str;
186 l_return_arr char30_arr;
187 l_token VARCHAR2(20);
188 l_delim_pos NUMBER;
189 l_index NUMBER := 1;
190
191 BEGIN
192
193 LOOP
194 select instr(l_input_str,p_delimiter)
195 into l_delim_pos from dual;
196
197 IF l_delim_pos = 0 THEN
198 l_token := l_input_str;
199 ELSIF l_delim_pos IS NULL THEN
200 return NULL;
201 ELSE
202 l_token := substr(l_input_str,1,l_delim_pos-1);
203 END IF;
204
205 IF l_index = 1 THEN
206 l_return_arr := char30_arr(l_token);
207 ELSE
208 l_return_arr.extend(1);
209 l_return_arr(l_index) := l_token;
210 END IF;
211
212 EXIT WHEN l_delim_pos = 0;
213 l_input_str := substr(l_input_str, l_delim_pos+1,
214 length(l_input_str));
215 l_index := l_index + 1;
216 END LOOP;
217
218 return l_return_arr;
219 END break_str_by_delim;
220
221
222 FUNCTION component_code_tokens(p_component_code IN VARCHAR2)
223 RETURN char30_arr IS
224 BEGIN
225 return break_str_by_delim(p_component_code, COMP_CODE_DELIMITER);
226 END component_code_tokens;
227
228
229 FUNCTION item_key_tokens(p_item_key IN VARCHAR2)
230 RETURN char30_arr IS
231 BEGIN
232 return break_str_by_delim(p_item_key, ITEM_KEY_DELIMITER);
233 END item_key_tokens;
234
235
236 FUNCTION validation_org_for_cfg_model(p_config_session_key IN VARCHAR2)
237 RETURN NUMBER IS
238
239 l_temp VARCHAR2(100);
240 l_result VARCHAR2(100);
241 l_item_key cz_atp_requests.item_key%TYPE;
242
243 BEGIN
244 begin
245 select item_key into l_item_key from cz_atp_requests where
246 configurator_session_key = p_config_session_key and seq_no = 1;
247 exception
248 when others then
249 return NULL;
250 end;
251
252 return org_id_from_item_key(l_item_key);
253
254 END validation_org_for_cfg_model;
255
256 --This function returns the config_item_id of the root BOM model in a configuration
257 --identified by the p_config_session_id parameter.
258
259 FUNCTION root_bom_config_item_id(p_config_session_key IN VARCHAR2) RETURN NUMBER IS
260
261 TYPE typeItemKeyTable IS TABLE OF cz_atp_requests.item_key%TYPE INDEX BY BINARY_INTEGER;
262 TYPE typeItemIdTable IS TABLE OF cz_atp_requests.config_item_id%TYPE INDEX BY BINARY_INTEGER;
263
264 tabItemKey typeItemKeyTable;
265 tabConfigItemId typeItemIdTable;
266 vComponentCode cz_atp_requests.item_key%TYPE;
267
268 BEGIN
269
270 --Read only the configuration lines corresponding to BOM items.
271
272 SELECT item_key, config_item_id BULK COLLECT INTO tabItemKey, tabConfigItemId
273 FROM cz_atp_requests
274 WHERE configurator_session_key = p_config_session_key
275 AND item_key_type = G_ITEM_KEY_BOM_NODE;
276
277 FOR i IN 1..tabItemKey.COUNT LOOP
278
279 vComponentCode := component_code_from_item_key(tabItemKey(i));
280
281 IF(INSTR(vComponentCode, COMP_CODE_DELIMITER) = 0)THEN
282
283 --For every line, we extract the component code and see if it contains the delimiter
284 --symbol. If there is no delimiter, we assume the component code is just a single id
285 --and so this is the root model. As soon as one such line found we exit.
286
287 RETURN tabConfigItemId(i);
288 END IF;
289 END LOOP;
290
291 --No root model found, return NULL.
292
293 RETURN NULL;
294
295 EXCEPTION
296 WHEN OTHERS THEN
297
298 --Any error occured, return NULL.
299
300 RETURN NULL;
301 END root_bom_config_item_id;
302
303 END cz_atp_callback_util;
[Home] [Help]