[Home] [Help]
PACKAGE BODY: APPS.MRP_KANBAN_FUNCTIONS_PK
Source
1 PACKAGE BODY MRP_KANBAN_FUNCTIONS_PK AS
2 /* $Header: MRPPKANB.pls 115.7 2003/05/30 10:12:01 nrajpal ship $ */
3
4 PROCEDURE UPDATE_PULL_SEQUENCES
5 (x_return_status OUT NOCOPY VARCHAR2,
6 x_msg_count OUT NOCOPY NUMBER,
7 x_msg_data OUT NOCOPY VARCHAR2,
8 p_pull_sequence_id IN NUMBER,
9 p_organization_id IN NUMBER,
10 p_kanban_plan_id IN NUMBER,
11 p_inventory_item_id IN NUMBER,
12 p_subinventory_name IN VARCHAR2,
13 p_locator_id IN NUMBER,
14 p_kanban_size IN NUMBER,
15 p_number_of_cards IN NUMBER,
16 p_source_type IN NUMBER := FND_API.G_MISS_NUM,
17 p_source_organization_id IN NUMBER := FND_API.G_MISS_NUM,
18 p_source_subinventory IN VARCHAR2 := FND_API.G_MISS_CHAR,
19 p_source_locator_id IN NUMBER := FND_API.G_MISS_NUM,
20 p_line_id IN NUMBER := FND_API.G_MISS_NUM,
21 p_supplier_id IN NUMBER := FND_API.G_MISS_NUM,
22 p_supplier_site_id IN NUMBER := FND_API.G_MISS_NUM,
23 p_calculate_kanban_flag IN NUMBER := FND_API.G_MISS_NUM,
24 p_replenishment_lead_time IN NUMBER := FND_API.G_MISS_NUM,
25 p_release_kanban_flag IN NUMBER := FND_API.G_MISS_NUM,
26 p_minimum_order_quantity IN NUMBER := FND_API.G_MISS_NUM,
27 p_fixed_lot_multiplier IN NUMBER := FND_API.G_MISS_NUM,
28 p_safety_stock_days IN NUMBER := FND_API.G_MISS_NUM) IS
29
30 l_pull_sequence_rec INV_Kanban_PVT.pull_sequence_rec_type;
31 l_return_status VARCHAR2(1);
32 BEGIN
33
34 l_pull_sequence_rec.pull_sequence_id := p_pull_sequence_id;
35 l_pull_sequence_rec.organization_id := p_organization_id;
36 l_pull_sequence_rec.kanban_plan_id := p_kanban_plan_id;
37 l_pull_sequence_rec.inventory_item_id := p_inventory_item_id;
38 l_pull_sequence_rec.subinventory_name := p_subinventory_name;
39 l_pull_sequence_rec.locator_id := p_locator_id;
40 l_pull_sequence_rec.kanban_size := p_kanban_size;
41 l_pull_sequence_rec.number_of_cards := p_number_of_cards;
42
43 -- If source type is passed as -1 then only update size and number
44 -- information. Set values to default.
45 IF (p_source_type = -1) THEN
46 l_pull_sequence_rec.source_type := FND_API.G_MISS_NUM;
47 l_pull_sequence_rec.source_organization_id := FND_API.G_MISS_NUM;
48 l_pull_sequence_rec.source_subinventory := FND_API.G_MISS_CHAR;
49 l_pull_sequence_rec.source_locator_id := FND_API.G_MISS_NUM;
50 l_pull_sequence_rec.wip_line_id := FND_API.G_MISS_NUM;
51 l_pull_sequence_rec.supplier_id := FND_API.G_MISS_NUM;
52 l_pull_sequence_rec.supplier_site_id := FND_API.G_MISS_NUM;
53 l_pull_sequence_rec.calculate_kanban_flag := FND_API.G_MISS_NUM;
54 l_pull_sequence_rec.replenishment_lead_time := FND_API.G_MISS_NUM;
55
56 l_pull_sequence_rec.release_kanban_flag := FND_API.G_MISS_NUM;
57 l_pull_sequence_rec.minimum_order_quantity := FND_API.G_MISS_NUM;
58 l_pull_sequence_rec.fixed_lot_multiplier := FND_API.G_MISS_NUM;
59 l_pull_sequence_rec.safety_stock_days := FND_API.G_MISS_NUM;
60 ELSE
61 l_pull_sequence_rec.source_type := p_source_type;
62 l_pull_sequence_rec.source_organization_id := p_source_organization_id;
63 l_pull_sequence_rec.source_subinventory := p_source_subinventory;
64 l_pull_sequence_rec.source_locator_id := p_source_locator_id;
65 l_pull_sequence_rec.wip_line_id := p_line_id;
66 l_pull_sequence_rec.supplier_id := p_supplier_id;
67 l_pull_sequence_rec.supplier_site_id := p_supplier_site_id;
68 l_pull_sequence_rec.calculate_kanban_flag := p_calculate_kanban_flag;
69 l_pull_sequence_rec.replenishment_lead_time := p_replenishment_lead_time;
70
71 l_pull_sequence_rec.release_kanban_flag := p_release_kanban_flag;
72 l_pull_sequence_rec.minimum_order_quantity := p_minimum_order_quantity;
73 l_pull_sequence_rec.fixed_lot_multiplier := p_fixed_lot_multiplier;
74 l_pull_sequence_rec.safety_stock_days := p_safety_stock_days;
75 END IF;
76
77
78 INV_Kanban_PVT.update_pull_sequence(l_return_status,
79 l_pull_sequence_rec);
80
81 x_return_status := l_return_status;
82
83 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
84 RAISE FND_API.G_EXC_ERROR;
85 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
86 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
87 ELSE
88 commit work;
89 END IF;
90
91 EXCEPTION
92 WHEN FND_API.G_EXC_ERROR THEN
93 FND_MSG_PUB.count_and_get
94 (p_count => x_msg_count,
95 p_data => x_msg_data );
96 WHEN OTHERS THEN
97 FND_MSG_PUB.count_and_get
98 (p_count => x_msg_count,
99 p_data => x_msg_data );
100
101 END UPDATE_PULL_SEQUENCES;
102
103 PROCEDURE DELETE_PULL_SEQUENCES
104 (x_return_status OUT NOCOPY VARCHAR2,
105 x_msg_count OUT NOCOPY NUMBER,
106 x_msg_data OUT NOCOPY VARCHAR2,
107 p_kanban_plan_id IN NUMBER) IS
108 l_return_status VARCHAR2(1);
109 BEGIN
110 INV_Kanban_PVT.delete_pull_sequence(l_return_status,
111 p_kanban_plan_id);
112 x_return_status := l_return_status;
113
114 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
115 RAISE FND_API.G_EXC_ERROR;
116 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
117 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
118 END IF;
119
120 EXCEPTION
121 WHEN FND_API.G_EXC_ERROR THEN
122 FND_MSG_PUB.count_and_get
123 (p_count => x_msg_count,
124 p_data => x_msg_data );
125 WHEN OTHERS THEN
126 FND_MSG_PUB.count_and_get
127 (p_count => x_msg_count,
128 p_data => x_msg_data );
129
130 END DELETE_PULL_SEQUENCES;
131
132
133 PROCEDURE INSERT_PULL_SEQUENCES (
134 x_return_status OUT NOCOPY VARCHAR2,
135 x_msg_count OUT NOCOPY NUMBER,
136 x_msg_data OUT NOCOPY VARCHAR2,
137 p_plan_pull_sequence_id IN NUMBER ) IS
138 l_return_status VARCHAR2(1);
139 l_Pull_Sequence_Rec INV_Kanban_PVT.Pull_Sequence_Rec_Type;
140
141 BEGIN
142 l_pull_sequence_rec := INV_PullSequence_PKG.Query_Row(p_plan_pull_sequence_id);
143 l_pull_sequence_rec.pull_sequence_id := NULL;
144 l_pull_sequence_rec.kanban_plan_id :=-1;
145
146 INV_Kanban_PVT.Insert_Pull_Sequence(l_return_status,l_pull_sequence_rec);
147
148 x_return_status := l_return_status;
149
150 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
151 RAISE FND_API.G_EXC_ERROR;
152 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
154 ELSE
155 commit work;
156 END IF;
157
158 EXCEPTION
159 WHEN FND_API.G_EXC_ERROR THEN
160 FND_MSG_PUB.count_and_get
161 (p_count => x_msg_count,
162 p_data => x_msg_data );
163 WHEN OTHERS THEN
164 FND_MSG_PUB.count_and_get
165 (p_count => x_msg_count,
166 p_data => x_msg_data );
167 End INSERT_PULL_SEQUENCES;
168
169 PROCEDURE UPDATE_AND_PRINT_KANBANS (
170 x_return_status OUT NOCOPY VARCHAR2,
171 x_msg_count OUT NOCOPY NUMBER,
172 x_msg_data OUT NOCOPY VARCHAR2,
173 p_query_id IN NUMBER,
174 p_update_flag IN VARCHAR2 ) IS
175
176 l_pull_seq_id NUMBER;
177 l_counter NUMBER;
178 l_return_status VARCHAR2(1);
179 l_pull_seq_tbl INV_Kanban_PVT.Pull_sequence_Id_Tbl_Type;
180 l_operation_tbl INV_Kanban_PVT.operation_tbl_type;
181
182 l_update constant number := 0;
183 l_insert constant number := 1;
184 l_delplan constant number :=2;
185 l_delplanprod constant number :=3;
186 l_delplanprodcards constant number :=4;
187 l_flag number :=0; /*For the operation being performed*/
188 l_prod_pull_Sequence_id mtl_kanban_pull_sequences.pull_sequence_id%TYPE;
189
190 Cursor cur_pull_seq is
191 SELECT number1,nvl(number2,l_update),nvl(number3,0)
192 FROM mrp_form_query
193 WHERE query_id = p_query_id;
194
195 BEGIN
196
197 OPEN cur_pull_seq;
198 l_counter := 0;
199
200 -- load up the pull_seq_tbl to be passed to the INV API
201 WHILE TRUE LOOP
202
203 FETCH cur_pull_seq
204 INTO l_pull_seq_id,l_flag,l_prod_pull_Sequence_id;
205
206 EXIT WHEN cur_pull_seq%NOTFOUND;
207
208 if(( l_flag = l_update) or (l_flag=l_insert)) then
209 l_counter := l_counter + 1;
210 l_pull_seq_tbl(l_counter) := l_pull_seq_id;
211 if( l_flag = l_update) then
212 l_operation_tbl(l_counter) :=l_update;
213 else
214 l_operation_tbl(l_counter) :=l_insert;
215 end if;
216 else
217 if(l_flag=l_delplanprodcards) then --Delete cards
218 DELETE
219 FROM MTL_KANBAN_CARDS
220 WHERE pull_sequence_id = l_prod_pull_sequence_id;
221 end if;
222 if( (l_flag=l_delplanprodcards ) or ( l_flag = l_delplanprod) ) then -- Delete Production
223 INV_pullsequence_PKG.delete_row(l_return_status,l_prod_pull_sequence_id);
224 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
225 RAISE FND_API.G_EXC_ERROR;
226 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
228 END IF;
229
230 end if;
231 -- Delete planning pull sequence
232
233 INV_pullsequence_pkg.delete_row(l_return_status,l_pull_seq_id);
234 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
235 RAISE FND_API.G_EXC_ERROR;
236 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
237 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
238 END IF;
239 commit; --The above procedure does not do a commit ,so we add it here
240 end if;
241
242 END LOOP;
243
244 -- call the INV API that updates/inserts
245 INV_Kanban_PVT.Update_Pull_sequence_Tbl (
246 l_return_status,
247 l_pull_seq_tbl,
248 p_update_flag,
249 l_operation_tbl);
250
251 x_return_status := l_return_status;
252
253 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
254 RAISE FND_API.G_EXC_ERROR;
255 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
257 ELSE
258
259 DELETE FROM mrp_form_query
260 WHERE query_id = p_query_id;
261
262 COMMIT WORK;
263 END IF;
264
265 EXCEPTION
266 WHEN FND_API.G_EXC_ERROR THEN
267 FND_MSG_PUB.count_and_get
268 (p_count => x_msg_count,
269 p_data => x_msg_data );
270 WHEN OTHERS THEN
271 FND_MSG_PUB.count_and_get
272 (p_count => x_msg_count,
273 p_data => x_msg_data );
274
275 END UPDATE_AND_PRINT_KANBANS;
276
277 END MRP_KANBAN_FUNCTIONS_PK;