[Home] [Help]
PACKAGE BODY: APPS.FLM_KANBAN_CONSOLIDATION
Source
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FLM_KANBAN_CONSOLIDATION';
1 PACKAGE BODY FLM_KANBAN_CONSOLIDATION AS
2 /* $Header: flmkconb.pls 120.2 2011/10/19 11:14:34 sisankar noship $ */
3
5 TYPE t_cards_to_consolidate is table of INV_Kanban_PVT.Kanban_Card_Rec_Type index by binary_integer;
6
7 FUNCTION Get_Next_Replenish_Cycle_Id Return Number
8 Is
9 l_next_replenish_cycle_Id Number;
10 Begin
11
12 Select MTL_KANBAN_REPLENISH_CYCLE_S.NextVal Into l_next_replenish_cycle_Id
13 From Dual;
14
15 Return(l_next_replenish_cycle_Id);
16
17 End Get_Next_Replenish_Cycle_Id;
18
19 Procedure Create_Requisition( p_buyer_id IN NUMBER,
20 p_interface_source_code IN VARCHAR2,
21 p_requisition_type IN VARCHAR2,
22 p_approval IN VARCHAR2,
23 p_source_type_code IN VARCHAR2,
24 p_kanban_card_rec_tbl IN t_cards_to_consolidate,
25 p_destination_type_code IN VARCHAR2,
26 p_deliver_location_id IN NUMBER,
27 p_revision IN VARCHAR2,
28 p_item_description IN VARCHAR2,
29 p_primary_uom_code IN VARCHAR2,
30 p_need_by_date IN DATE,
31 p_charge_account_id IN NUMBER,
32 p_accrual_account_id IN NUMBER,
33 p_invoice_var_account_id IN NUMBER,
34 p_budget_account_id IN NUMBER,
35 p_autosource_flag IN VARCHAR2,
36 p_po_org_id IN NUMBER) IS
37
38 l_project_id NUMBER :=null;
39 l_task_id NUMBER := null;
40 l_project_reference_enabled NUMBER;
41 l_project_accounting_context VARCHAR2(30);
42 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
43 l_consolidation_option Number := 1;
44 l_Current_Replenish_Cycle_Id Number;
45 l_group_code varchar2(500) := null;
46 l_Kanban_Card_Rec_Tbl t_cards_to_consolidate;
47 begin
48 fnd_file.put_line(fnd_file.log,'Create_Requisition');
49 l_project_accounting_context := null;
50
51 l_Kanban_Card_Rec_Tbl := p_kanban_card_rec_tbl;
52
53 SELECT NVL(project_reference_enabled,2)
54 INTO l_project_reference_enabled
55 FROM mtl_parameters
56 WHERE organization_id = l_Kanban_Card_Rec_Tbl(1).organization_id;
57
58 l_Current_Replenish_Cycle_Id := Get_Next_Replenish_Cycle_Id;
59 l_group_code := l_Kanban_Card_Rec_Tbl(1).pull_sequence_id||
60 l_Kanban_Card_Rec_Tbl(1).kanban_card_id||
61 l_Kanban_Card_Rec_Tbl(1).organization_id||
62 l_Kanban_Card_Rec_Tbl(1).current_replnsh_cycle_id;
63 fnd_file.put_line(fnd_file.log,'before loop');
64 For l_order_count in 1..l_Kanban_Card_Rec_Tbl.Count Loop
65 fnd_file.put_line(fnd_file.log,'inside loop');
66 IF (l_project_reference_enabled = 1)THEN
67 IF (l_Kanban_Card_Rec_Tbl(1).locator_id IS NOT NULL)THEN
68 SELECT project_id
69 INTO l_project_id
70 FROM mtl_item_locations
71 WHERE inventory_location_id = l_Kanban_Card_Rec_Tbl(l_order_count).locator_id
72 AND organization_id = l_Kanban_Card_Rec_Tbl(l_order_count).organization_id;
73 END IF;
74 IF (l_project_id IS NOT NULL)THEN
75 l_project_accounting_context := 'Y';
76 SELECT task_id
77 INTO l_task_id
78 FROM mtl_item_locations
79 WHERE NVL(project_id,-999) = NVL(l_project_id, -111)
80 AND inventory_location_id =l_Kanban_Card_Rec_Tbl(l_order_count).locator_id
81 AND organization_id = l_Kanban_Card_Rec_Tbl(l_order_count).organization_id;
82 END IF;
83 END IF;
84
85 insert into po_requisitions_interface_all
86 (
87 LAST_UPDATE_DATE,
88 LAST_UPDATED_BY,
89 CREATION_DATE,
93 REQUISITION_TYPE,
90 CREATED_BY,
91 PREPARER_ID,
92 INTERFACE_SOURCE_CODE,
94 AUTHORIZATION_STATUS,
95 SOURCE_TYPE_CODE,
96 SOURCE_ORGANIZATION_ID,
97 SOURCE_SUBINVENTORY,
98 DESTINATION_ORGANIZATION_ID,
99 DESTINATION_SUBINVENTORY,
100 DELIVER_TO_REQUESTOR_ID,
101 DESTINATION_TYPE_CODE,
102 DELIVER_TO_LOCATION_ID,
103 ITEM_ID,
104 ITEM_REVISION,
105 ITEM_DESCRIPTION,
106 UOM_CODE,
107 QUANTITY,
108 NEED_BY_DATE,
109 GL_DATE,
110 CHARGE_ACCOUNT_ID,
111 ACCRUAL_ACCOUNT_ID,
112 VARIANCE_ACCOUNT_ID,
113 BUDGET_ACCOUNT_ID,
114 AUTOSOURCE_FLAG,
115 ORG_ID,
116 SUGGESTED_VENDOR_ID,
117 SUGGESTED_VENDOR_SITE_ID,
118 Kanban_card_Id,
119 Batch_Id,
120 PROJECT_ID,
121 TASK_ID,
122 PROJECT_ACCOUNTING_CONTEXT,
123 GROUP_CODE
124 )
125 Values
126 (
127 sysdate,
128 FND_GLOBAL.USER_ID,
129 sysdate,
130 FND_GLOBAL.USER_ID,
131 p_buyer_Id,
132 p_interface_Source_Code,
133 p_requisition_type,
134 p_approval,
135 p_source_type_code,
136 l_Kanban_Card_Rec_Tbl(l_order_count).Source_organization_Id,
137 l_Kanban_Card_Rec_Tbl(l_order_count).Source_Subinventory,
138 l_Kanban_Card_Rec_Tbl(l_order_count).organization_Id,
139 l_Kanban_Card_Rec_Tbl(l_order_count).Subinventory_Name,
140 p_buyer_Id,
141 p_destination_type_code,
142 p_deliver_location_id,
143 l_Kanban_Card_Rec_Tbl(l_order_count).Inventory_Item_Id,
144 p_revision,
145 p_Item_description,
146 p_Primary_uom_Code,
147 l_Kanban_Card_Rec_Tbl(l_order_count).kanban_size,
148 (trunc(p_need_by_date) + 1 - (1/(24*60*60))),
149 SYSDATE,
150 p_Charge_Account_Id,
151 p_Accrual_Account_Id,
152 p_Invoice_Var_Account_Id,
153 p_Budget_Account_Id,
154 p_autosource_flag,
155 p_po_org_id,
156 l_Kanban_Card_Rec_Tbl(l_order_count).Supplier_ID,
157 l_Kanban_Card_Rec_Tbl(l_order_count).Supplier_Site_ID,
158 l_Kanban_Card_Rec_Tbl(l_order_count).kanban_card_id,
159 l_Current_Replenish_Cycle_Id,
160 l_project_id,
161 l_task_id,
162 l_project_accounting_context,
163 l_group_code);
164
165 l_Kanban_Card_Rec_Tbl(l_order_count).Supply_Status := INV_Kanban_PVT.G_Supply_Status_Empty;
166 l_Kanban_Card_Rec_Tbl(l_order_count).Current_Replnsh_Cycle_Id := l_Current_Replenish_Cycle_Id;
167 l_Kanban_Card_Rec_Tbl(l_order_count).replenishment_count := nvl(l_Kanban_Card_Rec_Tbl(l_order_count).replenishment_count,0)+1;
168 l_Kanban_Card_Rec_Tbl(l_order_count).Last_Update_Date := SYSDATE;
169 l_Kanban_Card_Rec_Tbl(l_order_count).Last_Updated_By := FND_GLOBAL.USER_ID;
170 fnd_file.put_line(fnd_file.log,'after card upd');
171 Update Mtl_Kanban_Cards
172 Set Supply_Status = INV_Kanban_PVT.G_Supply_Status_Empty,
173 Current_Replnsh_Cycle_Id = l_Current_Replenish_Cycle_Id,
174 replenishment_count = nvl(replenishment_count,0)+1,
175 Last_Update_Date = SYSDATE,
176 Last_Updated_By = FND_GLOBAL.USER_ID
177 Where Kanban_Card_Id = l_Kanban_Card_Rec_Tbl(l_order_count).Kanban_card_Id;
178
179 INV_KanbanCard_PKG.Insert_Activity_For_Card(l_Kanban_Card_Rec_Tbl(l_order_count));
180 fnd_file.put_line(fnd_file.log,'after card activity');
181 end loop;
182
183 EXCEPTION
184 WHEN FND_API.G_EXC_ERROR THEN
185 fnd_file.put_line(fnd_file.log,'Error in Create_Requisition '||sqlerrm(sqlcode));
186 Raise FND_API.G_EXC_ERROR;
187 WHEN OTHERS THEN
188 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
189 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Create_Requisition');
190 END IF;
191 fnd_file.put_line(fnd_file.log,'Error in Create_Requisition '||sqlerrm(sqlcode));
192 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
193 end Create_Requisition;
194
195 Procedure Create_Transfer_Order(
196 p_kanban_card_rec_tbl IN t_cards_to_consolidate,
197 p_need_by_date IN DATE,
198 p_primary_uom_code IN VARCHAR2 ) IS
199
200 l_x_trohdr_rec INV_Move_Order_PUB.Trohdr_Rec_Type;
201 l_x_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
202 l_trohdr_rec INV_Move_Order_PUB.Trohdr_Rec_Type;
203 l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
204 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
205 l_msg_count NUMBER;
206 l_msg_data VARCHAR2(240);
207 msg VARCHAR2(2000);
208 l_header_id Number := FND_API.G_MISS_NUM;
209 l_line_num Number := 0;
210 l_item_locator_control_code NUMBER;
211 l_from_sub_locator_type NUMBER;
212 l_to_sub_locator_type NUMBER;
213 l_org_locator_control_code NUMBER;
217 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
214 l_auto_allocate_flag NUMBER;
215 l_mo_request_number VARCHAR2(30);
216 l_secondary_uom_code VARCHAR2(3);
218 l_proc_name CONSTANT VARCHAR2(30) := 'Create_Transfer_Order';
219 l_secondary_qty NUMBER;
220 l_Kanban_Card_Rec_Tbl t_cards_to_consolidate;
221 l_Current_Replenish_Cycle_Id Number;
222 begin
223 fnd_file.put_line(fnd_file.log,'Create_Transfer_Order');
224 l_Kanban_Card_Rec_Tbl := p_kanban_card_rec_tbl;
225 l_Current_Replenish_Cycle_Id := Get_Next_Replenish_Cycle_Id;
226 l_trohdr_rec.created_by := FND_GLOBAL.USER_ID;
227 l_trohdr_rec.creation_date := sysdate;
228 l_trohdr_rec.date_required := p_need_by_date;
229 l_trohdr_rec.from_subinventory_code := l_Kanban_Card_Rec_Tbl(1).source_subinventory;
230 -- l_trohdr_rec.header_id := l_header_id;
231 l_trohdr_rec.header_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
232 l_trohdr_rec.last_updated_by := FND_GLOBAL.USER_ID;
233 l_trohdr_rec.last_update_date := sysdate;
234 l_trohdr_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
235 l_trohdr_rec.organization_id := l_Kanban_Card_Rec_Tbl(1).organization_id;
236 -- l_trohdr_rec.request_number := to_char(l_header_id);
237 l_trohdr_rec.status_date := sysdate;
238 l_trohdr_rec.to_subinventory_code := l_Kanban_Card_Rec_Tbl(1).subinventory_name;
239 l_trohdr_rec.transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
240 l_trohdr_rec.move_order_type := INV_GLOBALS.G_MOVE_ORDER_REPLENISHMENT;
241 l_trohdr_rec.db_flag := FND_API.G_TRUE;
242 l_trohdr_rec.operation := INV_GLOBALS.G_OPR_CREATE;
243 fnd_file.put_line(fnd_file.log,'before loop');
244 For l_order_count in 1..l_Kanban_Card_Rec_Tbl.Count Loop
245 fnd_file.put_line(fnd_file.log,'inside loop');
246 select location_control_code,secondary_uom_code
247 into l_item_locator_control_code,l_secondary_uom_code
248 from mtl_system_items
249 where organization_id = l_Kanban_Card_Rec_Tbl(l_order_count).organization_id
250 and inventory_item_id = l_Kanban_Card_Rec_Tbl(l_order_count).inventory_item_id;
251
252 select locator_type
253 into l_from_sub_locator_type
254 from mtl_secondary_inventories
255 where organization_id = l_Kanban_Card_Rec_Tbl(l_order_count).organization_id
256 and secondary_inventory_name = l_Kanban_Card_Rec_Tbl(l_order_count).source_subinventory;
257
258 select locator_type
259 into l_to_sub_locator_type
260 from mtl_secondary_inventories
261 where organization_id = l_Kanban_Card_Rec_Tbl(l_order_count).organization_id
262 and secondary_inventory_name = l_Kanban_Card_Rec_Tbl(l_order_count).subinventory_name;
263
264 select stock_locator_control_code
265 into l_org_locator_control_code
266 from mtl_parameters
267 where organization_id = l_Kanban_Card_Rec_Tbl(l_order_count).organization_id;
268
269 if l_org_locator_control_code = 1 then
270 l_Kanban_Card_Rec_Tbl(l_order_count).source_locator_id := null;
271 l_Kanban_Card_Rec_Tbl(l_order_count).locator_id := null;
272 elsif l_org_locator_control_code = 4 then
273 if l_from_sub_locator_type = 1 then
274 l_Kanban_Card_Rec_Tbl(l_order_count).source_locator_id := null;
275 elsif l_from_sub_locator_type = 5 then
276 if l_item_locator_control_code = 1 then
277 l_Kanban_Card_Rec_Tbl(l_order_count).source_locator_id := null;
278 end if;
279 end if;
280 if l_to_sub_locator_type = 1 then
281 l_Kanban_Card_Rec_Tbl(l_order_count).locator_id := null;
282 elsif l_to_sub_locator_type = 5 then
283 if l_item_locator_control_code = 1 then
284 l_Kanban_Card_Rec_Tbl(l_order_count).locator_id := null;
285 end if;
286 end if;
287 end if;
288
289 l_line_num := l_line_num + 1;
290 l_trolin_tbl(l_order_count).created_by := FND_GLOBAL.USER_ID;
291 l_trolin_tbl(l_order_count).creation_date := sysdate;
292 l_trolin_tbl(l_order_count).date_required := p_need_by_date;
293 l_trolin_tbl(l_order_count).from_locator_id := l_Kanban_Card_Rec_Tbl(l_order_count).source_locator_id;
294 l_trolin_tbl(l_order_count).from_subinventory_code := l_Kanban_Card_Rec_Tbl(l_order_count).source_subinventory;
295 l_trolin_tbl(l_order_count).inventory_item_id := l_Kanban_Card_Rec_Tbl(l_order_count).inventory_item_id;
296 l_trolin_tbl(l_order_count).last_updated_by := FND_GLOBAL.USER_ID;
297 l_trolin_tbl(l_order_count).last_update_date := sysdate;
298 l_trolin_tbl(l_order_count).last_update_login := FND_GLOBAL.LOGIN_ID;
299 l_trolin_tbl(l_order_count).line_id := FND_API.G_MISS_NUM;
300 l_trolin_tbl(l_order_count).line_number := l_line_num;
301 l_trolin_tbl(l_order_count).line_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
302 l_trolin_tbl(l_order_count).organization_id := l_Kanban_Card_Rec_Tbl(l_order_count).organization_id;
303 l_trolin_tbl(l_order_count).quantity := l_Kanban_Card_Rec_Tbl(l_order_count).kanban_size;
304 l_trolin_tbl(l_order_count).reference_id := l_Kanban_Card_Rec_Tbl(l_order_count).kanban_card_id;
305 l_trolin_tbl(l_order_count).reference_type_code := INV_Transfer_Order_PVT.G_Ref_Type_Kanban;
306 l_trolin_tbl(l_order_count).status_date := sysdate;
307 l_trolin_tbl(l_order_count).to_locator_id := l_Kanban_Card_Rec_Tbl(l_order_count).locator_id;
311 l_trolin_tbl(l_order_count).task_id
308 BEGIN
309 SELECT project_id,task_id
310 INTO l_trolin_tbl(l_order_count).project_id,
312 FROM mtl_item_locations
313 WHERE inventory_location_id = l_Kanban_Card_Rec_Tbl(l_order_count).source_locator_id
314 and organization_id = l_Kanban_Card_Rec_Tbl(l_order_count).organization_id;
315 EXCEPTION
316 WHEN no_data_found THEN
317 NULL;
318 END;
319
320 IF l_secondary_uom_code IS NOT NULL THEN
321 l_secondary_qty := inv_convert.inv_um_convert
322 (item_id => l_Kanban_Card_Rec_Tbl(l_order_count).inventory_item_id
323 ,precision => 5
324 ,from_quantity => l_Kanban_Card_Rec_Tbl(l_order_count).kanban_size
325 ,from_unit => p_primary_uom_code
326 ,to_unit => l_secondary_uom_code
327 ,from_name => NULL
328 ,to_name => NULL);
329 IF l_secondary_qty < 0 THEN
330 fnd_file.put_line(fnd_file.log,'Error in Create_Transfer_Order '||'Uom Conversion Failed for Creating Transfer Order:'||
331 l_Kanban_Card_Rec_Tbl(1).inventory_item_id|| ', '|| l_Kanban_Card_Rec_Tbl(1).organization_id||l_proc_name);
332 RAISE FND_API.g_exc_error;
333 END IF ;
334 ELSE
335 l_secondary_uom_code := NULL ;
336 l_secondary_qty := NULL ;
337 END IF;
338
339 l_trolin_tbl(l_order_count).to_subinventory_code:= l_Kanban_Card_Rec_Tbl(1).subinventory_name;
340 l_trolin_tbl(l_order_count).uom_code := p_primary_uom_code;
341 l_trolin_tbl(l_order_count).transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
342 l_trolin_tbl(l_order_count).db_flag := FND_API.G_TRUE;
343 l_trolin_tbl(l_order_count).operation := INV_GLOBALS.G_OPR_CREATE;
344 l_trolin_tbl(l_order_count).secondary_quantity := l_secondary_qty;
345 l_trolin_tbl(l_order_count).secondary_uom := l_secondary_uom_code;
346
347 END LOOP;
348
349 INV_Transfer_Order_PVT.Process_Transfer_Order
350 ( p_api_version_number => 1.0 ,
351 p_init_msg_list => FND_API.G_TRUE,
352 x_return_status => l_return_status,
353 x_msg_count => l_msg_count,
354 x_msg_data => l_msg_data,
355 p_trohdr_rec => l_trohdr_rec,
356 p_trolin_tbl => l_trolin_tbl,
357 x_trohdr_rec => l_x_trohdr_rec,
358 x_trolin_tbl => l_x_trolin_tbl
359 );
360
361 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
362 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Create_transfer_order');
363 fnd_file.put_line(fnd_file.log,'Error in Create_Transfer_Order '||sqlerrm(sqlcode));
364 RAISE FND_API.g_exc_error;
365 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
366 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Create_transfer_order');
367 fnd_file.put_line(fnd_file.log,'Error in Create_Transfer_Order '||sqlerrm(sqlcode));
368 RAISE FND_API.g_exc_error;
369 END IF;
370
371 BEGIN
372 SELECT MKP.auto_allocate_flag INTO l_auto_Allocate_flag
373 FROM Mtl_Kanban_Pull_Sequences MKP
374 WHERE MKP.pull_sequence_id=l_Kanban_Card_Rec_Tbl(1).pull_sequence_id;
375 EXCEPTION
376 WHEN OTHERS THEN
377 l_auto_Allocate_flag := 0;
378 END;
379
380 For l_order_count in 1 .. l_x_trolin_tbl.count
381 Loop
382 l_Kanban_Card_Rec_Tbl(l_order_count).document_header_id := l_x_trolin_tbl(l_order_count).header_id;
383 l_Kanban_Card_Rec_Tbl(l_order_count).document_detail_id := l_x_trolin_tbl(l_order_count).Line_id;
384 l_Kanban_Card_Rec_Tbl(l_order_count).document_type := 4;
385 l_Kanban_Card_Rec_Tbl(l_order_count).Supply_Status := INV_Kanban_PVT.G_Supply_Status_InProcess;
386 l_Kanban_Card_Rec_Tbl(l_order_count).Current_Replnsh_Cycle_Id := l_Current_Replenish_Cycle_Id;
387 l_Kanban_Card_Rec_Tbl(l_order_count).replenishment_count := nvl(l_Kanban_Card_Rec_Tbl(l_order_count).replenishment_count,0)+1;
388 l_Kanban_Card_Rec_Tbl(l_order_count).Last_Update_Date := SYSDATE;
389 l_Kanban_Card_Rec_Tbl(l_order_count).Last_Updated_By := FND_GLOBAL.USER_ID;
390
391 Update Mtl_Kanban_Cards
392 Set Supply_Status = INV_Kanban_PVT.G_Supply_Status_InProcess,
393 Current_Replnsh_Cycle_Id = l_Current_Replenish_Cycle_Id,
394 replenishment_count = nvl(replenishment_count,0)+1,
395 Last_Update_Date = SYSDATE,
396 Last_Updated_By = FND_GLOBAL.USER_ID
397 Where Kanban_Card_Id = l_Kanban_Card_Rec_Tbl(l_order_count).Kanban_card_Id;
398
399 INV_KanbanCard_PKG.Insert_Activity_For_Card(l_Kanban_Card_Rec_Tbl(l_order_count));
400
401 End Loop;
402
403 IF l_auto_allocate_flag = 1 THEN
404 INV_Kanban_PVT.Auto_Allocate_Kanban(l_Kanban_Card_Rec_Tbl(1).document_header_id,l_return_status, l_msg_count,l_msg_data);
405
406 SELECT MTRH.request_number INTO l_mo_request_number
407 FROM Mtl_Txn_Request_Headers MTRH
408 WHERE MTRH.Header_id = l_Kanban_Card_Rec_Tbl(1).document_header_id;
409
410 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
411 FND_MESSAGE.SET_NAME('INV','INV_KANBAN_MO_ALLOC_SUCCESS');
412 FND_MESSAGE.SET_TOKEN('MOVE_ORDER',l_mo_request_number);
413 FND_MSG_PUB.Add;
414 fnd_file.put_line(fnd_file.log,'Error in Create_Transfer_Order during auto allocate '||fnd_message.get);
415 RAISE FND_API.g_exc_error;
416 ELSE
420 fnd_file.put_line(fnd_file.log,'Error in Create_Transfer_Order during auto allocate '||fnd_message.get);
417 FND_MESSAGE.SET_NAME('INV','INV_MO_ALLOC_FAIL');
418 FND_MESSAGE.SET_TOKEN('MOVE_ORDER',l_mo_request_number);
419 FND_MSG_PUB.Add;
421 RAISE FND_API.g_exc_error;
422 END IF;
423 END IF;
424
425 EXCEPTION
426 WHEN FND_API.G_EXC_ERROR THEN
427 fnd_file.put_line(fnd_file.log,'Error in Create_Transfer_Order '||sqlerrm(sqlcode));
428 Raise FND_API.G_EXC_ERROR;
429 WHEN OTHERS THEN
430 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
431 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,'Create_Transfer_Order');
432 END IF;
433 fnd_file.put_line(fnd_file.log,'Error in Create_Transfer_Order '||sqlerrm(sqlcode));
434 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
435 END Create_Transfer_Order;
436
437
438 PROCEDURE Create_Replenishment
439 (p_Kanban_Cards In t_cards_to_consolidate,
440 p_lead_time In Number,
441 x_return_status Out NOCOPY Number)
442 IS
443
444 l_Item_Description Varchar2(240);
445 l_Source_type_code Varchar2(30);
446 l_Requisition_type Varchar2(30);
447 l_Primary_Uom_Code Varchar2(3);
448 l_deliver_location_Id Number;
449 l_Buyer_Id Number;
450 l_Encumb_Account_Id Number;
451 l_Charge_Account_Id Number;
452 l_Budget_Account_Id Number;
453 l_Accrual_Account_Id Number;
454 l_Invoice_Var_Account_Id Number;
455 l_Inventory_Asset_Flag Varchar2(1);
456 l_Interface_source_code Varchar2(30) := 'INV';
457 l_Destination_type_code Varchar2(30) := 'INVENTORY';
458 l_Approval Varchar2(30) := 'APPROVED';
459 l_Autosource_Flag Varchar2(1) := 'P';
460 l_need_by_date Date;
461 l_need_by_time Number;
462 l_PreProcess_lead_Time Number;
463 l_Process_lead_Time Number;
464 l_PostProcess_lead_Time Number;
465 l_Encumb_Flag Varchar2(1);
466 l_PO_Org_Id Number := null;
467 l_sql_stmt_no Number;
468 l_Revision Varchar2(3);
469 l_Revision_qty_control_code Number;
470 revision_profile Number;
471
472 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
473 l_consolidation_option NUMBER := null;
474 l_Kanban_Card_Rec_Tbl t_cards_to_consolidate;
475 Begin
476 fnd_file.put_line(fnd_file.log,'Create_replenishment');
477 l_Kanban_Card_Rec_Tbl := p_Kanban_Cards;
478
479 l_sql_stmt_no := 5;
480
481 Select msi.Description,Primary_Uom_Code,Inventory_Asset_Flag,
482 nvl(mss.ENCUMBRANCE_ACCOUNT,nvl(msi.Encumbrance_Account,Org.Encumbrance_Account)),
483 decode(msi.inventory_asset_flag, 'Y', mss.material_account,nvl(mss.expense_account,nvl(msi.expense_account,org.expense_account))),
484 Org.Ap_accrual_account,Org.invoice_price_var_account,
485 nvl(mss.preprocessing_lead_time,nvl(msi.preprocessing_lead_time,0)),
486 nvl(mss.processing_lead_time,nvl(msi.full_lead_time,0)),
487 nvl(mss.postprocessing_lead_time,nvl(msi.postprocessing_lead_time,0)),
488 msi.revision_qty_control_code
489 Into l_Item_Description,l_Primary_Uom_Code,l_Inventory_Asset_Flag,
490 l_Encumb_Account_Id,
491 l_Charge_Account_Id,
492 l_Accrual_Account_Id,
493 l_Invoice_Var_Account_Id,
494 l_PreProcess_lead_Time,
495 l_Process_lead_Time,
496 l_PostProcess_lead_Time,
497 l_Revision_qty_control_code
498 From Mtl_System_Items msi,
499 mtl_Parameters org,
500 mtl_secondary_inventories mss
501 Where Msi.Organization_Id = l_kanban_card_Rec_Tbl(1).Organization_id
502 And Msi.Inventory_Item_Id = l_kanban_card_Rec_Tbl(1).Inventory_Item_Id
503 And org.Organization_Id = l_kanban_card_Rec_Tbl(1).Organization_Id
504 And mss.Organization_id = l_kanban_card_Rec_Tbl(1).Organization_id
505 And mss.secondary_inventory_name = l_kanban_card_Rec_Tbl(1).Subinventory_Name;
506
507 Begin
508 Select nvl(ENCUMBRANCE_ACCOUNT,l_Encumb_Account_Id),
509 nvl(preprocessing_lead_time,l_PreProcess_lead_Time)
510 + nvl(processing_lead_time,l_Process_lead_Time)
511 + nvl(postprocessing_lead_time,l_PostProcess_lead_Time)
512 Into l_budget_Account_Id,
513 l_need_by_time
514 From mtl_item_sub_inventories
515 Where Organization_id = l_kanban_card_Rec_Tbl(1).Organization_id
516 And Inventory_Item_Id = l_kanban_card_Rec_Tbl(1).Inventory_Item_Id
517 And secondary_inventory = l_kanban_card_Rec_Tbl(1).Subinventory_Name;
518 Exception
519 When No_data_found
520 Then
521 l_need_by_time := l_PreProcess_lead_Time + l_Process_lead_Time + l_PostProcess_lead_Time;
522 l_budget_Account_Id := l_Encumb_Account_Id;
523 End;
524
525 l_sql_stmt_no := 20;
526 select nvl(f.req_encumbrance_flag,'N'),o.operating_unit
527 into l_encumb_flag,l_po_org_Id
528 from financials_system_params_all f,
529 org_organization_definitions o
530 where o.organization_id = l_kanban_card_Rec_Tbl(1).Organization_id
531 And nvl(f.org_id,-99) = nvl(o.operating_unit,-99);
532
533 IF l_kanban_card_Rec_Tbl(1).need_by_date IS NOT NULL THEN
534 l_need_by_date := l_kanban_card_Rec_Tbl(1).need_by_date;
535 ELSE
539 from mtl_parameters o,
536 l_sql_stmt_no := 30;
537 select c1.calendar_date
538 into l_need_by_date
540 bom_calendar_dates c1,
541 bom_calendar_dates c
542 where o.organization_id = l_kanban_card_Rec_Tbl(1).Organization_id
543 and c1.calendar_code = c.calendar_code
544 and c1.exception_set_id = c.exception_set_id
545 and c1.seq_num = (c.next_seq_num + trunc(nvl(p_lead_time,l_need_by_time)))
546 and c.calendar_code = o.CALENDAR_CODE
547 and c.exception_set_id = o.CALENDAR_EXCEPTION_SET_ID
548 and c.calendar_date = trunc(sysdate);
549 END IF;
550 fnd_file.put_line(fnd_file.log,'sourcetype code');
551 if l_kanban_card_Rec_Tbl(1).source_Type = INV_Kanban_PVT.G_Source_Type_InterOrg Then
552 fnd_file.put_line(fnd_file.log,'inter1');
553 l_sql_stmt_no := 40;
554 select distinct org.location_id
555 into l_deliver_location_id
556 from hr_organization_units org,
557 hr_locations loc,
558 po_location_associations_all pla
559 where org.organization_id =
560 l_kanban_card_Rec_Tbl(1).Organization_id
561 and org.location_id = loc.location_id
562 and pla.location_id = loc.location_id;
563
564 Elsif l_kanban_card_Rec_Tbl(1).source_Type = INV_Kanban_PVT.G_Source_Type_Supplier Then
565 fnd_file.put_line(fnd_file.log,'supplier2');
566 l_sql_stmt_no := 40;
567 select org.location_id
568 into l_deliver_location_id
569 from hr_organization_units org,
570 hr_locations loc
571 where org.organization_id =
572 l_kanban_card_Rec_Tbl(1).Organization_id
573 and org.location_id = loc.location_id;
574 end if;
575
576 revision_profile := fnd_profile.value('INV_PURCHASING_BY_REVISION') ;
577 if revision_profile = 1 then
578
579 l_sql_stmt_no := 50;
580
581 select MAX(revision)
582 into l_revision
583 from mtl_item_revisions mir
584 where inventory_item_id = l_kanban_card_Rec_Tbl(1).Inventory_Item_Id
585 and organization_id = l_kanban_card_Rec_Tbl(1).organization_Id
586 and effectivity_date < SYSDATE
587 and implementation_date is not null
588 and effectivity_date = ( select MAX(effectivity_date)
589 from mtl_item_revisions mir1
590 where mir1.inventory_item_id = mir.inventory_item_id
591 and mir1.organization_id = mir.organization_id
592 and implementation_date is not null
593 and effectivity_date < SYSDATE );
594 end if;
595
596 l_sql_stmt_no := 60;
597
598 select employee_id
599 into l_buyer_id
600 from fnd_user
601 where user_id = FND_GLOBAL.USER_ID;
602
603 If l_kanban_card_Rec_Tbl(1).source_type = INV_Kanban_PVT.G_Source_Type_InterOrg then
604 l_source_type_code := 'INVENTORY';
605 l_Requisition_type := 'INTERNAL';
606 fnd_file.put_line(fnd_file.log,'inter-final');
607 Create_Requisition( l_buyer_id, l_interface_source_code,
608 l_requisition_type, l_approval,
609 l_source_type_code, l_kanban_card_rec_tbl,
610 l_destination_type_code, l_deliver_location_id,
611 l_revision, l_item_description,
612 l_primary_uom_code, l_need_by_date,
613 l_charge_account_id, l_accrual_account_id,
614 l_invoice_var_account_id, l_budget_account_id,
615 l_autosource_flag, l_po_org_id);
616
617 elsIf l_kanban_card_Rec_Tbl(1).source_type = INV_Kanban_PVT.G_Source_Type_Supplier Then
618 l_source_type_code := 'VENDOR';
619 l_Requisition_type := 'PURCHASE';
620 fnd_file.put_line(fnd_file.log,'supplier-final');
621 Create_Requisition( l_buyer_id, l_interface_source_code,
622 l_requisition_type, l_approval,
623 l_source_type_code, l_kanban_card_rec_tbl,
624 l_destination_type_code, l_deliver_location_id,
625 l_revision, l_item_description,
626 l_primary_uom_code, l_need_by_date,
627 l_charge_account_id, l_accrual_account_id,
628 l_invoice_var_account_id, l_budget_account_id,
629 l_autosource_flag, l_po_org_id);
630
631 elsIf l_kanban_card_Rec_Tbl(1).source_type = INV_Kanban_PVT.G_Source_Type_IntraOrg Then
632 l_source_type_code := 'INVENTORY';
633 l_Requisition_type := 'TRANSFER';
634 fnd_file.put_line(fnd_file.log,'intra-final');
635 Create_Transfer_Order(l_kanban_card_Rec_Tbl,l_need_by_date,l_Primary_Uom_Code);
636 else
637 x_return_status := 0;
638 Return;
639 end if;
640 x_return_status := 0;
641 EXCEPTION
642
643 WHEN NO_data_FOUND Then
644 if l_sql_stmt_no = 10 then
645 FND_MESSAGE.SET_NAME('INV','INV-NO ITEM RECORD');
646 elsif l_sql_stmt_no = 20 then
647 FND_MESSAGE.SET_NAME('INV','INV-NO ORG INFORMATION');
648 elsif l_sql_stmt_no = 30 then
649 FND_MESSAGE.SET_NAME('INV','INV-NO CALENDAR DATE');
650 elsif l_sql_stmt_no = 40 then
651 FND_MESSAGE.SET_NAME('INV','INV_DEFAULT_DELIVERY_LOC_REQD');
652 elsif l_sql_stmt_no = 50 then
653 FND_MESSAGE.SET_NAME('INV','INV_INT_REVCODE');
654 elsif l_sql_stmt_no = 60 then
658 FND_MSG_PUB.Add;
655 FND_MESSAGE.SET_NAME('FND','CONC-FDWHOAMI INVALID USERID');
656 FND_MESSAGE.SET_TOKEN('USERID',to_char(FND_GLOBAL.USER_ID));
657 end if;
659 fnd_file.put_line(fnd_file.log,'Error in Create_Replenishment'||'( stmt_num: '||l_sql_stmt_no||') '||fnd_message.get);
660 fnd_file.put_line(fnd_file.log,'Error in Create_Replenishment'||'( stmt_num: '||l_sql_stmt_no||') '||sqlerrm(sqlcode));
661 x_return_status := 1;
662 WHEN FND_API.G_EXC_ERROR THEN
663 fnd_file.put_line(fnd_file.log,'Error in Create_Replenishment'||'( stmt_num: '||l_sql_stmt_no||') '||fnd_message.get);
664 fnd_file.put_line(fnd_file.log,'Error in Create_Replenishment'||'( stmt_num: '||l_sql_stmt_no||') '||sqlerrm(sqlcode));
665 x_return_status := 1;
666 WHEN OTHERS THEN
667 fnd_file.put_line(fnd_file.log,'Error in Create_Replenishment'||'( stmt_num: '||l_sql_stmt_no||') '||fnd_message.get);
668 fnd_file.put_line(fnd_file.log,'Error in Create_Replenishment'||'( stmt_num: '||l_sql_stmt_no||') '||sqlerrm(sqlcode));
669 x_return_status := 1;
670 End Create_Replenishment;
671
672
673 PROCEDURE KANBAN_CONSOLIDATION(errbuf OUT NOCOPY VARCHAR2,
674 retcode OUT NOCOPY NUMBER,
675 p_organization_id IN NUMBER,
676 p_group_code IN VARCHAR2 DEFAULT NULL)
677 IS
678
679 l_Order_Count Number := 0;
680 v_req_id Number;
681
682 v_cards_to_consolidate t_cards_to_consolidate;
683 v_consolidated_card t_cards_to_consolidate;
684
685 l_card_counter Number;
686 l_prev_pull_seq Number := null;
687 l_prev_source_type Number := null;
688 l_prev_supplier Number := null;
689 l_prev_supplier_site Number := null;
690 l_prev_src_org Number := null;
691 l_prev_src_subinv VARCHAR2(10) := null;
692 l_prev_src_locator Number := null;
693 l_lead_time Number;
694 l_count Number := 0;
695 l_runReqImp Number := 0;
696 l_status Number := 0;
697 x_status Number;
698 l_conc_status boolean;
699 l_ou_id Number;
700 begin
701 retcode := 0; -- success
702
703 fnd_file.put_line(fnd_file.log,'Parameters to consolidation program: Ord Id : '||p_organization_id);
704 fnd_file.put_line(fnd_file.log,'Parameters to consolidation program: Group Code : '||p_group_code);
705
706 if p_organization_id is null then
707 fnd_file.put_line(fnd_file.log,'Organization parameter should be passed to consolidation program');
708 raise no_data_found;
709 end if;
710
711 if nvl(fnd_profile.VALUE('FLM_EKB_OPS_FLAG'),0) <> 1 then
712 fnd_message.set_name('FLM', 'FLM_EKB_PROFILE_DISABLED');
713 fnd_file.put_line(fnd_file.log,fnd_message.get);
714 raise no_data_found;
715 end if;
716
717 select mkc.kanban_card_id,mkc.kanban_card_number,mkc.pull_sequence_id,mkc.inventory_item_id,mkc.organization_id,mkc.subinventory_name,mkc.supply_status
718 ,mkc.card_status,mkc.kanban_card_type,mkc.source_type,mkc.kanban_size,mkc.last_update_date,mkc.last_updated_by,mkc.creation_date,mkc.created_by
719 ,mkc.locator_id,mkc.supplier_id,mkc.supplier_site_id,mkc.source_organization_id,mkc.source_subinventory,mkc.source_locator_id,mkc.wip_line_id
720 ,mkc.current_replnsh_cycle_id,null,null,null,mkc.error_code,mkc.last_update_login,mkc.last_print_date,mkc.attribute_category,mkc.attribute1,
721 mkc.attribute2,mkc.attribute3,mkc.attribute4,mkc.attribute5,mkc.attribute6,mkc.attribute7,mkc.attribute8,mkc.attribute9,mkc.attribute10,mkc.attribute11,
722 mkc.attribute12,mkc.attribute13,mkc.attribute14,mkc.attribute15,mkc.request_id,mkc.program_application_id,mkc.program_id,mkc.program_update_date,null,
723 null,null,null,null,null,null,null,null,mkc.replenishment_count,mkc.max_replenishments,mkc.disable_date,mkc.replacement_flag
724 bulk collect into v_cards_to_consolidate
725 from
726 mtl_kanban_cards mkc,
727 mtl_kanban_pull_sequences mkps
728 where mkps.organization_id = p_organization_id
729 and nvl(mkps.consolidation_group,'####') = nvl(p_group_code,'####')
730 and mkps.pull_sequence_id = mkc.pull_sequence_id
731 and trunc(mkc.supply_status) = INV_Kanban_PVT.G_Supply_Status_Consolidate
732 and mkc.card_status = INV_Kanban_PVT.G_Card_Status_Active
733 order by mkc.source_type,mkc.pull_sequence_id,
734 mkc.supplier_id,mkc.supplier_site_id,
735 mkc.source_organization_id,mkc.source_subinventory,mkc.source_locator_id;
736
737 v_consolidated_card.delete;
738 l_card_counter := v_cards_to_consolidate.first;
739 while l_card_counter <= v_cards_to_consolidate.last
740 LOOP
741 if l_card_counter is not null then
742 if v_cards_to_consolidate(l_card_counter).pull_sequence_id <> nvl(l_prev_pull_seq,-100) then
743 select replenishment_lead_time into l_lead_time
744 from mtl_kanban_pull_sequences
745 where pull_sequence_id = v_cards_to_consolidate(l_card_counter).pull_sequence_id;
746 end if;
747 if l_prev_pull_seq is null then
748 l_prev_pull_seq := v_cards_to_consolidate(l_card_counter).pull_sequence_id;
749 l_prev_source_type := v_cards_to_consolidate(l_card_counter).source_type;
750 l_prev_supplier := nvl(v_cards_to_consolidate(l_card_counter).supplier_id,-10);
751 l_prev_supplier_site := nvl(v_cards_to_consolidate(l_card_counter).supplier_site_id,-10);
752 l_prev_src_org := nvl(v_cards_to_consolidate(l_card_counter).source_organization_id,-10);
753 l_prev_src_subinv := nvl(v_cards_to_consolidate(l_card_counter).source_subinventory,'####');
754 l_prev_src_locator := nvl(v_cards_to_consolidate(l_card_counter).source_locator_id,-10);
755 end if;
756 if v_cards_to_consolidate(l_card_counter).source_type = l_prev_source_type and
760 nvl(v_cards_to_consolidate(l_card_counter).supplier_site_id,-10) = l_prev_supplier_site) or
757 v_cards_to_consolidate(l_card_counter).pull_sequence_id = l_prev_pull_seq and
758 ( (v_cards_to_consolidate(l_card_counter).source_type = INV_Kanban_PVT.G_Source_Type_Supplier and
759 nvl(v_cards_to_consolidate(l_card_counter).supplier_id,-10) = l_prev_supplier and
761 (v_cards_to_consolidate(l_card_counter).source_type = INV_Kanban_PVT.G_Source_Type_InterOrg and
762 nvl(v_cards_to_consolidate(l_card_counter).source_organization_id,-10) = l_prev_src_org and
763 nvl(v_cards_to_consolidate(l_card_counter).source_subinventory,'####') = l_prev_src_subinv and
764 nvl(v_cards_to_consolidate(l_card_counter).source_locator_id,-10) = l_prev_src_locator) or
765 (v_cards_to_consolidate(l_card_counter).source_type = INV_Kanban_PVT.G_Source_Type_IntraOrg and
766 nvl(v_cards_to_consolidate(l_card_counter).source_subinventory,'####') = l_prev_src_subinv and
767 nvl(v_cards_to_consolidate(l_card_counter).source_locator_id,-10) = l_prev_src_locator) ) then
768 l_count := l_count +1;
769 v_consolidated_card(l_count) := v_cards_to_consolidate(l_card_counter);
770 else
771 Create_Replenishment(p_Kanban_Cards => v_consolidated_card,
772 p_lead_time => l_lead_time,
773 x_return_status => x_status);
774 if x_status <> 0 then
775 l_status := 1;
776 end if;
777 v_consolidated_card.delete;
778 l_count := 1;
779 v_consolidated_card(l_count) := v_cards_to_consolidate(l_card_counter);
780 end if;
781 if v_cards_to_consolidate(l_card_counter).source_type in (INV_Kanban_PVT.G_Source_Type_InterOrg,
782 INV_Kanban_PVT.G_Source_Type_Supplier) then
786 l_prev_source_type := v_cards_to_consolidate(l_card_counter).source_type;
783 l_runReqImp := 1;
784 end if;
785 l_prev_pull_seq := v_cards_to_consolidate(l_card_counter).pull_sequence_id;
787 l_prev_supplier := nvl(v_cards_to_consolidate(l_card_counter).supplier_id,-10);
788 l_prev_supplier_site := nvl(v_cards_to_consolidate(l_card_counter).supplier_site_id,-10);
789 l_prev_src_org := nvl(v_cards_to_consolidate(l_card_counter).source_organization_id,-10);
790 l_prev_src_subinv := nvl(v_cards_to_consolidate(l_card_counter).source_subinventory,'####');
791 l_prev_src_locator := nvl(v_cards_to_consolidate(l_card_counter).source_locator_id,-10);
792 end if;
793 l_card_counter := v_cards_to_consolidate.next(l_card_counter);
794 if l_card_counter is null then
795 Create_Replenishment(p_Kanban_Cards => v_consolidated_card,
796 p_lead_time => l_lead_time,
797 x_return_status => x_status);
798 if x_status <> 0 then
799 l_status := 1;
800 end if;
801 end if;
802 END LOOP;
803 COMMIT;
804 if l_runReqImp = 1 then
805
806 select to_number(ORG_INFORMATION3) into l_ou_id
807 from HR_ORGANIZATION_INFORMATION
808 where ORGANIZATION_ID = p_organization_id
809 and ORG_INFORMATION_CONTEXT = 'Accounting Information';
810
811 FND_REQUEST.SET_ORG_ID (l_ou_id);
812
813 v_req_id := fnd_request.submit_request('PO', 'REQIMPORT', NULL, NULL, FALSE,'INV', NULL, 'ITEM',
814 NULL ,'N', 'Y' , chr(0), NULL, NULL, NULL,
815 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
816 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
817 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
818 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
819 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
820 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
821 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
822 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
823 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
824 IF v_req_id = 0 THEN
825 fnd_file.put_line(fnd_file.log,'Error While launching REQIMPORT');
826 l_status := 1;
830 COMMIT;
827 END IF;
828 end if;
829 fnd_file.put_line(fnd_file.log,' Completed Kanban Consolidation ');
831
832 IF l_status = 1 THEN
833 retcode := 2;
834 errbuf := 'Unexpected errors during Kanban Consolidation';
835 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',errbuf);
836 else
837 l_conc_status := true;
838 retcode :=0;
839 errbuf := null;
840 END IF;
841
842 EXCEPTION
843 WHEN OTHERS THEN
844 retcode := 2;
845 errbuf := 'Errors encountered during Kanban Consolidation, Please check the log file.';
846 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',errbuf);
847 END KANBAN_CONSOLIDATION;
848
849
850 END FLM_KANBAN_CONSOLIDATION;