[Home] [Help]
PACKAGE BODY: APPS.FLM_MULTIPLE_SUPPLIERS
Source
1 PACKAGE BODY FLM_MULTIPLE_SUPPLIERS
2 /* $Header: flmmsupb.pls 120.1.12020000.2 2012/07/13 11:04:35 sisankar ship $ */
3 AS
4 NO_SUPPLIER NUMBER := 0;
5 SINGLE_SUPPLIER NUMBER := 1;
6 MULTIPLE_SUPPLIER NUMBER := 2;
7
8 SUPPLIER_PERC_MISMATCH EXCEPTION;
9
10 /* Internal Procedure to log debug messages */
11 PROCEDURE log(p_proc IN VARCHAR2,
12 p_msg IN VARCHAR2,
13 x_returnStatus OUT NOCOPY VARCHAR2) IS
14 BEGIN
15 inv_log_util.trace(p_msg, p_proc, 9);
16 END log;
17
18
19 /* This procedure validates the supplier percentages */
20 PROCEDURE validate_supplier_percentages(p_pull_seq_id IN NUMBER,
21 x_supplier OUT NOCOPY NUMBER,
22 x_retcode OUT NOCOPY VARCHAR2,
23 x_err_msg OUT NOCOPY VARCHAR2) IS
24
25 l_sup_flag NUMBER := 0;
26 l_total_perc NUMBER;
27 l_returnStatus VARCHAR2(1);
28
29 BEGIN
30
31 x_retcode := fnd_api.g_ret_sts_success;
32
33 BEGIN
34 SELECT Sum(sourcing_percentage), Count(mpss.supplier_id)
35 INTO l_total_perc, l_sup_flag
36 FROM mtl_pull_seq_suppliers mpss
37 WHERE pull_sequence_id = p_pull_seq_id;
38 EXCEPTION
39 WHEN No_Data_Found THEN
40 l_sup_flag := 0;
41 END;
42
43 IF (l_sup_flag = 0) THEN
44 x_supplier := NO_SUPPLIER;
45 log('NO_SUPPLIER', 'flm_multiple_suppliers.validate_supplier_percentages' , l_returnStatus);
46 RETURN;
47 ELSIF (l_total_perc = 100) THEN
48 IF (l_sup_flag = 1) THEN
49 x_supplier := SINGLE_SUPPLIER;
50 log('SINGLE_SUPPLIER', 'flm_multiple_suppliers.validate_supplier_percentages' , l_returnStatus);
51 RETURN;
52 ELSE
53 x_supplier := MULTIPLE_SUPPLIER;
54 log('MULTIPLE_SUPPLIER', 'flm_multiple_suppliers.validate_supplier_percentages' , l_returnStatus);
55 RETURN;
56 END IF;
57 ELSE
58 RAISE SUPPLIER_PERC_MISMATCH;
59 END IF;
60
61 EXCEPTION
62
63 WHEN SUPPLIER_PERC_MISMATCH THEN
64 x_retcode := fnd_api.g_ret_sts_error;
65 fnd_message.set_name('FLM','FLM_SUP_PERC_MISMATCH_ERR');
66 x_err_msg := fnd_message.get;
67 log('Suppliers percentages dont sum to 100%', 'flm_multiple_suppliers.validate_supplier_percentages' , l_returnStatus);
68
69 WHEN OTHERS THEN
70 x_retcode := fnd_api.g_ret_sts_error;
71 x_err_msg := SQLCODE || substr(SQLERRM, 1, 200);
72 log('exception occurred ' || x_err_msg, 'flm_multiple_suppliers.validate_supplier_percentages' , l_returnStatus);
73
74 END validate_supplier_percentages;
75
76 PROCEDURE GET_SUPPLIER(p_pull_seq_id IN NUMBER,
77 p_org_id IN NUMBER,
78 p_cardstatus IN NUMBER ,
79 x_supplier_id OUT NOCOPY NUMBER,
80 x_supplier_site_id OUT NOCOPY NUMBER,
81 x_retcode OUT NOCOPY VARCHAR2,
82 x_err_msg OUT NOCOPY VARCHAR2) IS
83
84 CURSOR multiple_suppliers_cur IS
85 SELECT mks.supplier_id,
86 mks.supplier_site_id,
87 mks.organization_id,
88 mks.number_of_cards actual_supplier_cards,
89 Count(mkc.kanban_card_id) existing_supplier_cards,
90 mks.number_of_cards - Nvl(Count(mkc.kanban_card_id),0) deficiency
91 FROM mtl_pull_seq_suppliers mks,
92 mtl_kanban_cards mkc
93 WHERE mks.pull_sequence_id = p_pull_seq_id
94 AND mks.pull_sequence_id = mkc.pull_sequence_id(+)
95 AND mks.supplier_id = mkc.supplier_id(+)
96 AND Nvl(mks.supplier_site_id, -1 ) = Nvl(mkc.supplier_site_id(+) ,-1)
97 AND mkc.card_status(+) in ( INV_Kanban_PVT.G_Card_Status_Active,INV_Kanban_PVT.G_Card_Status_Hold)
98 GROUP BY mks.supplier_id, mks.supplier_site_id , mks.organization_id, mks.number_of_cards , mks.creation_date
99 ORDER BY mks.creation_date;
100
101 CURSOR multiple_suppliers_plancur IS
102 SELECT mks.supplier_id,
103 mks.supplier_site_id,
104 mks.organization_id,
105 mks.future_no_of_cards actual_supplier_cards,
106 Count(mkc.kanban_card_id) existing_supplier_cards,
107 mks.future_no_of_cards - Nvl(Count(mkc.kanban_card_id),0) deficiency
108 FROM mtl_pull_seq_suppliers mks ,
109 mtl_kanban_cards mkc
110 WHERE mks.pull_sequence_id = p_pull_seq_id
111 AND mks.pull_sequence_id = mkc.pull_sequence_id(+)
112 AND mks.supplier_id = mkc.supplier_id(+)
113 and NVL(MKS.SUPPLIER_SITE_ID, -1 ) = NVL(MKC.SUPPLIER_SITE_ID(+) ,-1)
114 AND mkc.card_status in ( INV_Kanban_PVT.G_Card_Status_Active,INV_Kanban_PVT.G_Card_Status_Hold,INV_Kanban_PVT.G_Card_Status_Planned)
115 GROUP BY mks.supplier_id, mks.supplier_site_id , mks.organization_id, mks.future_no_of_cards , mks.creation_date
116 ORDER BY mks.creation_date;
117
118 TYPE mul_suprecord is RECORD ( supplier_id NUMBER,
119 supplier_site_id NUMBER,
120 organization_id NUMBER,
121 ACTUAL_SUPPLIER_CARDS number,
122 existing_supplier_cards NUMBER,
123 deficiency NUMBER);
124
125 TYPE mul_sup IS TABLE OF mul_suprecord;
126 mul_sup_rec mul_sup;
127 l_returnStatus VARCHAR2(1);
128 l_supplier NUMBER;
129 l_invalid_supplier BOOLEAN;
130 l_invalid_site BOOLEAN;
131 l_max_deficiency NUMBER;
132 l_max_act_sup_cards NUMBER;
133
134 BEGIN
135 x_retcode := fnd_api.g_ret_sts_success;
136
137 validate_supplier_percentages(p_pull_seq_id => p_pull_seq_id,
138 x_supplier => l_supplier,
139 x_retcode => x_retcode,
140 x_err_msg => x_err_msg);
141 IF(x_retcode <> fnd_api.g_ret_sts_success) THEN
142 raise fnd_api.g_exc_unexpected_error;
143 END IF;
144 log('After validating supplier', 'flm_multiple_suppliers.get_supplier' , l_returnStatus);
145
146 --get the supplier_id and site_id from custom hook
147 flm_kanban_custom_pkg.get_default_supplier(p_pull_seq_id => p_pull_seq_id,
148 x_supplier_id => x_supplier_id,
149 x_suppier_site_id => x_supplier_site_id,
150 x_retcode => x_retcode,
151 x_err_msg => x_err_msg);
152 IF(x_retcode <> fnd_api.g_ret_sts_success) THEN
153 raise fnd_api.g_exc_unexpected_error;
154 END IF;
155 log('Custom hook returned supplier: ' || x_supplier_id || ' supplier site: ' || x_supplier_site_id,
156 'flm_multiple_suppliers.get_supplier' , l_returnStatus);
157
158 /* Check if the supplier returned by the custom hook is valid in the organization or not */
159 IF (x_supplier_id IS NOT NULL ) THEN
160 l_invalid_supplier := FLM_KANBAN_PUB.is_supplier_id_invalid(x_supplier_id);
161 l_invalid_site := FLM_KANBAN_PUB.is_supplier_site_id_invalid(x_supplier_id,x_supplier_site_id,p_org_id);
162
163 IF (l_invalid_supplier = TRUE OR l_invalid_site = TRUE) THEN
164 x_supplier_id := NULL;
165 x_supplier_site_id := NULL;
166 ELSE
167 RETURN;
168 END IF;
169
170 END IF; -- check for valid supplier
171
172 log('Using default logic to derive supplier', 'flm_multiple_suppliers.get_supplier' , l_returnStatus);
173
174 /* No supplier defined at pull sequence. Return null */
175 IF (l_supplier = NO_SUPPLIER) THEN
176 RETURN;
177 END IF; -- no supplier
178
179 /* Return the single supplier id*/
180 IF (l_supplier = SINGLE_SUPPLIER ) THEN
181 SELECT supplier_id, supplier_site_id
182 INTO x_supplier_id, x_supplier_site_id
183 FROM mtl_pull_seq_suppliers
184 WHERE pull_sequence_id = p_pull_seq_id;
185 RETURN;
186 END IF; -- single supplier
187
188 /* Multiple suppliers exist for the pull sequence. Either the custom hook returned no supplier or an invalid supplier.
189 * Hence, we need to calculate the supplier for the next card
190 * 1. Check the supplier which has max deficiency of its existing cards than its required cards
191 * 2. If there are many suppliers with equal deficient cards - then select the supplier with maximum number of required cards
192 * 3. If there is a tie - then select the first supplier from the pull sequence
193 */
194 IF p_cardstatus=INV_Kanban_PVT.G_Card_Status_Planned THEN
195 OPEN multiple_suppliers_plancur;
196 FETCH multiple_suppliers_plancur BULK COLLECT INTO mul_sup_rec;
197 CLOSE multiple_suppliers_plancur;
198 ELSE
199 OPEN multiple_suppliers_cur;
200 FETCH multiple_suppliers_cur BULK COLLECT INTO mul_sup_rec;
201 CLOSE multiple_suppliers_cur;
202 END IF;
203 l_max_deficiency := -100000;
204 FOR i IN 1..mul_sup_rec.Count LOOP
205 IF l_max_deficiency < mul_sup_rec(i).deficiency THEN
206 l_max_deficiency := mul_sup_rec(i).deficiency;
207 END IF;
208 END LOOP;
209 FOR i IN 1..mul_sup_rec.Count LOOP
210 IF l_max_deficiency <> mul_sup_rec(i).deficiency THEN
211 mul_sup_rec.DELETE(i);
212 END IF;
213 END LOOP;
214 IF (mul_sup_rec.Count = 1) THEN
215 x_supplier_id := mul_sup_rec(mul_sup_rec.first).supplier_id;
216 x_supplier_site_id := mul_sup_rec(mul_sup_rec.first).supplier_site_id;
217 log('Multiple supplier: ' || x_supplier_id || ' site id: ' || x_supplier_site_id ,
218 'flm_multiple_suppliers.get_supplier' , l_returnStatus);
219 RETURN;
220 END IF;
221 l_max_act_sup_cards := 0;
222 FOR i IN 1..mul_sup_rec.Count LOOP
223 BEGIN
224 IF l_max_act_sup_cards < mul_sup_rec(i).actual_supplier_cards THEN
225 l_max_act_sup_cards := mul_sup_rec(i).actual_supplier_cards;
226 END IF;
227 EXCEPTION
228 WHEN NO_DATA_FOUND THEN
229 NULL;
230 END;
231 END LOOP;
232 FOR i IN 1..mul_sup_rec.Count LOOP
233 BEGIN
234 IF l_max_act_sup_cards <> mul_sup_rec(i).actual_supplier_cards THEN
235 mul_sup_rec.DELETE(i);
236 END IF;
237 EXCEPTION
238 WHEN NO_DATA_FOUND THEN
239 NULL;
240 END;
241 END LOOP;
242 x_supplier_id := mul_sup_rec(mul_sup_rec.first).supplier_id;
243 x_supplier_site_id := mul_sup_rec(mul_sup_rec.first).supplier_site_id;
244
245 log('Multiple supplier: ' || x_supplier_id || ' site id: ' || x_supplier_site_id ,
246 'flm_multiple_suppliers.get_supplier' , l_returnStatus);
247
248 RETURN;
249
250 EXCEPTION
251 WHEN OTHERS THEN
252 x_retcode := fnd_api.g_ret_sts_error;
253 x_err_msg := SQLCODE || substr(SQLERRM, 1, 200);
254 log('Exception: ' || x_err_msg, 'flm_multiple_suppliers.get_supplier' , l_returnStatus);
255 END GET_SUPPLIER;
256
257
258 PROCEDURE multiple_supplier_kanban_cards(p_pull_seq_id IN NUMBER,
259 x_retcode OUT NOCOPY VARCHAR2,
260 x_err_msg OUT NOCOPY VARCHAR2) IS
261 CURSOR supplier_cur IS SELECT mks.*
262 FROM mtl_pull_seq_suppliers mks
263 WHERE mks.pull_sequence_id = p_pull_seq_id
264 FOR UPDATE;
265
266 supplier_rec supplier_cur%ROWTYPE;
267 supplier_rec_last supplier_cur%ROWTYPE;
268 l_returnStatus VARCHAR2(1);
269 l_supplier NUMBER;
270 l_total_cards NUMBER;
271 l_supplier_cards NUMBER;
272 l_tot_sup_cards NUMBER;
273 l_tot_fut_cards NUMBER;
274 l_future_cards NUMBER;
275 l_fut_sup_cards NUMBER;
276
277 BEGIN
278 x_retcode := fnd_api.g_ret_sts_success;
279 validate_supplier_percentages(p_pull_seq_id => p_pull_seq_id,
280 x_supplier => l_supplier,
281 x_retcode => x_retcode,
282 x_err_msg => x_err_msg);
283 IF(x_retcode <> fnd_api.g_ret_sts_success) THEN
284 raise fnd_api.g_exc_unexpected_error;
285 END IF;
286 IF (l_supplier = NO_SUPPLIER) THEN
287 RETURN;
288 END IF;
289 /* Update all the suppliers except last with cards = round(Total Number of cards*Sourcing %)
290 * For the last supplier update with the difference between Total Number of cards and cards for other suppliers
291 */
292 log('Calculating percentages', 'flm_multiple_suppliers.multiple_supplier_kanban_cards' , l_returnStatus);
293 SELECT number_of_cards,future_no_of_cards
294 INTO l_total_cards,l_tot_fut_cards
295 FROM mtl_kanban_pull_sequences
296 WHERE pull_sequence_id = p_pull_seq_id;
297
298 IF (l_supplier = SINGLE_SUPPLIER ) THEN
299 UPDATE mtl_pull_seq_suppliers
300 SET number_of_cards = l_total_cards,
301 future_no_of_cards=l_tot_fut_cards
302 WHERE pull_sequence_id = p_pull_seq_id;
303 RETURN;
304
305 ELSIF (l_supplier = MULTIPLE_SUPPLIER) THEN
306 l_tot_sup_cards := 0;
307 l_fut_sup_cards := 0;
308 OPEN supplier_cur;
309
310 FETCH supplier_cur INTO supplier_rec;
311 WHILE (supplier_cur%FOUND) LOOP
312 l_supplier_cards := round(supplier_rec.sourcing_percentage/100 * l_total_cards);
313 l_future_cards:=round(supplier_rec.sourcing_percentage/100 * l_tot_fut_cards);
314
315 UPDATE mtl_pull_seq_suppliers
316 SET number_of_cards = l_supplier_cards,
317 future_no_of_cards=l_future_cards
318 WHERE CURRENT OF supplier_cur;
319
320 log('Supplier: ' || supplier_rec.supplier_id || ' cards: ' || l_supplier_cards||' Future Cards:'||l_future_cards,
321 'flm_multiple_suppliers.multiple_supplier_kanban_cards' , l_returnStatus);
322
323 supplier_rec_last := supplier_rec;
324 FETCH supplier_cur INTO supplier_rec;
325 EXIT WHEN supplier_cur%NOTFOUND;
326
327 l_tot_sup_cards := l_tot_sup_cards + l_supplier_cards;
328 l_fut_sup_cards := l_fut_sup_cards + l_future_cards;
329
330 END LOOP;
331
332 /*Calculate cards for last supplier */
333 l_supplier_cards := l_total_cards - l_tot_sup_cards;
334 l_future_cards := l_tot_fut_cards - l_fut_sup_cards;
335
336 UPDATE mtl_pull_seq_suppliers
337 SET number_of_cards = l_supplier_cards,
338 future_no_of_cards=l_future_cards
339 WHERE pull_sequence_id = supplier_rec_last.pull_sequence_id
340 AND supplier_id = supplier_rec_last.supplier_id
341 AND Nvl(supplier_site_id,-1) = Nvl(supplier_rec_last.supplier_site_id,-1);
342
343 CLOSE supplier_cur;
344 END IF;
345
346 EXCEPTION
347 WHEN fnd_api.g_exc_unexpected_error THEN
348 RETURN;
349 WHEN OTHERS THEN
350 x_retcode := fnd_api.g_ret_sts_error;
351 x_err_msg := SQLCODE || substr(SQLERRM, 1, 200);
352
353 END multiple_supplier_kanban_cards;
354
355 END FLM_MULTIPLE_SUPPLIERS;