[Home] [Help]
PACKAGE BODY: APPS.CSP_PLANNED_ORDERS
Source
1 PACKAGE BODY CSP_PLANNED_ORDERS AS
2 /* $Header: cspvppob.pls 120.11 2008/03/26 17:11:46 hhaugeru ship $ */
3 --
4 -- Purpose: To create planned orders for a warehouse
5 --
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 -- phegde 6/13/2005 Created package body
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'csp_planned_orders';
12 G_FILE_NAME CONSTANT VARCHAR2(30) := 'cspvppob.pls';
13
14 PROCEDURE create_orders
15 ( p_api_version IN NUMBER
16 , p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
17 , p_commit IN VARCHAR2 := FND_API.G_FALSE
18 , p_organization_id NUMBER
19 , p_inventory_item_id NUMBER
20 , px_line_tbl CSP_PLANNED_ORDERS.Line_Tbl_Type
21 , x_return_status OUT NOCOPY VARCHAR2
22 , x_msg_count OUT NOCOPY NUMBER
23 , x_msg_data OUT NOCOPY VARCHAR2)
24 IS
25 l_api_version_number CONSTANT NUMBER := 1.0;
26 l_api_name CONSTANT VARCHAR2(30) := 'create_orders';
27 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
28 l_msg_count NUMBER;
29 l_msg_data VARCHAR2(2000);
30 l_line_tbl csp_planned_orders.line_tbl_type;
31 l_order_line_tbl csp_parts_requirement.line_tbl_type;
32 l_order_hdr_rec csp_parts_requirement.header_rec_type;
33 l_po_line_tbl csp_parts_requirement.line_tbl_type;
34 l_po_hdr_rec csp_parts_requirement.header_rec_type;
35 l_defective_parts_tbl csp_repair_po_grp.defective_parts_tbl_Type;
36 l_int_rpr_line_tbl csp_parts_Requirement.line_Tbl_Type;
37 l_int_rpr_header_rec csp_parts_Requirement.header_rec_type;
38 J NUMBER := 1;
39 K NUMBER := 1;
40 l_wip_id NUMBER;
41 l_user_id NUMBER;
42 l_location_id NUMBER;
43 l_repair_supplier_id NUMBER;
44 l_repair_supplier_org_id NUMBER;
45 l_repair_organization_id NUMBER;
46 l_requisition_header_id NUMBER;
47 l_source_type NUMBER;
48 l_repair_program NUMBER;
49
50 CURSOR item_attr_cur IS
51 SELECT c.description item_Description,
52 c.planning_make_buy_code mbf,
53 c.primary_uom_code uom,
54 p.ap_accrual_account accru_Acct,
55 p.invoice_price_var_account ipv_acct,
56 nvl(p.encumbrance_account,
57 c.encumbrance_account) budget_Acct,
58 decode(c.inventory_asset_flag, 'Y', p.material_account,
59 NVL(c.expense_Account, p.expense_Account)) charge_Acct,
60 NVL(c.source_type, p.source_type) src_type,
61 DECODE(c.source_type, NULL,
62 DECODE(p.source_type, NULL, NULL, p.source_organization_id),
63 c.source_organization_id) src_org,
64 DECODE(c.source_type, NULL,
65 DECODE(p.source_type, NULL, NULL, p.source_subinventory),
66 c.source_subinventory) src_subinv,
67 c.purchasing_enabled_flag purch_flag,
68 c.internal_order_enabled_flag order_flag,
69 c.mtl_transactions_enabled_flag transact_flag,
70 c.list_price_per_unit unit_price,
71 c.planner_code planner,
72 build_in_wip_flag build_in_wip,
73 pick_components_flag pick_components
74 FROM mtl_system_items c,
75 mtl_parameters p
76 WHERE c.inventory_item_id = p_inventory_item_id
77 AND c.organization_id = p.organization_id
78 AND p.organization_id = p_organization_id;
79
80 l_item_attr_rec item_attr_cur%ROWTYPE;
81 EXCP_USER_DEFINED EXCEPTION;
82
83 CURSOR uom_code_cur(t_organization_id NUMBER,
84 t_item_id NUMBER) IS
85 SELECT primary_uom_code
86 FROM mtl_system_items_b
87 WHERE organization_id = t_organization_id
88 AND inventory_item_id = t_item_id;
89
90 CURSOR C_supplier (p_organization_id NUMBER,l_supplied_item_id NUMBER)
91 is select misl.source_type, misl.source_organization_id, misl.vendor_id
92 into l_source_type, l_repair_organization_id, l_repair_supplier_id
93 from MRP_ITEM_SOURCING_LEVELS_V misl, csp_planning_parameters cpp
94 where cpp.organization_id = p_organization_id
95 and misl.organization_id = cpp.organization_id
96 and misl.assignment_set_id =cpp.repair_assignment_set_id
97 and inventory_item_id = l_supplied_item_id --l_line_tbl(I).supplied_item_id
98 and SOURCE_TYPE in (1,3)
99 and sourcing_level = (select min(sourcing_level) from MRP_ITEM_SOURCING_LEVELS_V
100 where organization_id = p_organization_id
101 and assignment_set_id = cpp.repair_assignment_set_id
102 and inventory_item_id = l_supplied_item_id --l_line_tbl(I).supplied_item_id
103 and sourcing_level not in (2,9))
104 order by misl.rank,misl.source_type;
105
106 l_supplier_count NUMBER;
107 l_supplier_available NUMBER := 0;
108
109 BEGIN
110 SAVEPOINT Create_Orders_PUB;
111 -- initialize return status
112 x_return_status := FND_API.G_RET_STS_SUCCESS;
113 SELECT fnd_global.user_id INTO l_user_id from dual;
114
115 l_line_tbl := px_line_tbl;
116 -- get ship to of warehouse
117 begin
118 select location_id
119 into l_location_id
120 from hr_organization_units
121 where organization_id = p_organization_id;
122 exception
123 when no_data_found then
124 null;
125 end;
126
127 FOR I IN 1..l_line_tbl.COUNT LOOP
128 IF (l_line_tbl(I).uom_code IS NULL) THEN
129 OPEN uom_code_cur(l_line_tbl(I).source_organization_id,
130 l_line_tbl(I).supplied_item_id);
131 FETCH uom_code_cur INTO l_line_tbl(I).uom_code;
132 CLOSE uom_code_cur;
133 END IF;
134
135 IF l_line_Tbl(I).planned_order_type = 4110 THEN -- excess
136 IF l_order_hdr_rec.dest_organization_id IS NULL THEN
137 l_order_hdr_rec.dest_organization_id := p_organization_id;
138 FND_PROFILE.GET('CSP_ORDER_TYPE', l_order_hdr_rec.order_type_id);
139 l_order_hdr_Rec.ship_to_location_id := l_location_id;
140 END IF;
141 l_order_line_tbl(J).inventory_item_id := l_line_Tbl(I).supplied_item_id;
142 l_order_line_tbl(J).quantity := l_line_Tbl(I).quantity;
143 l_order_line_tbl(J).ordered_quantity := l_line_Tbl(I).quantity;
144 l_order_line_tbl(J).unit_of_measure := l_line_Tbl(I).uom_code;
145 l_order_line_tbl(J).source_organization_id := l_line_Tbl(I).source_organization_id;
146 l_order_line_tbl(J).need_by_Date := l_line_tbl(I).plan_Date;
147 l_order_line_tbl(J).line_num := J;
148 J := J + 1;
149 ELSIF l_line_tbl(I).planned_order_type = 4210 THEN -- repair
150 -- check if this is an internal or external repair supplier
151
152 Open c_supplier(p_organization_id,l_line_tbl(I).supplied_item_id);
153 Loop
154 Fetch c_supplier into l_source_type, l_repair_organization_id, l_repair_supplier_id;
155 Exit when c_supplier%NotFound;
156 If (l_source_type = 3 and
157 l_repair_organization_id IS NOT NULL and
158 l_repair_supplier_id IS NOT NULL) Then
159
160 Select count(*)
161 into l_supplier_count
162 from hr_organization_information
163 where ORG_INFORMATION_CONTEXT = 'Customer/Supplier Association'
164 and org_information3 = l_repair_supplier_id
165 and organization_id = l_repair_organization_id;
166
167 If l_supplier_count > 0 then
168 l_supplier_available := 1;
169 Exit;
170 End if;
171 Elsif (l_source_type = 1 and l_repair_organization_id IS NOT NULL) then
172 l_supplier_available := 1;
173 Exit;
174 End if;
175 End loop;
176 Close c_supplier;
177
178 If l_supplier_available = 0 then
179 FND_MESSAGE.SET_NAME('CSP', 'CSP_REPAIR_ASSIGNMENT_NULL');
180 FND_MSG_PUB.ADD;
181 RAISE EXCP_USER_DEFINED;
182 End if;
183
184 /*
185 begin
186 select misl.source_type, misl.source_organization_id, misl.vendor_id
187 into l_source_type, l_repair_organization_id, l_repair_supplier_id
188 from MRP_ITEM_SOURCING_LEVELS_V misl, csp_planning_parameters cpp
189 where cpp.organization_id = p_organization_id
190 and misl.organization_id = cpp.organization_id
191 and misl.assignment_set_id =cpp.repair_assignment_set_id
192 and inventory_item_id = l_line_tbl(I).supplied_item_id
193 and SOURCE_TYPE in (1,3)
194 and sourcing_level = (select min(sourcing_level) from MRP_ITEM_SOURCING_LEVELS_V
195 where organization_id = p_organization_id
196 and assignment_set_id = cpp.repair_assignment_set_id
197 and inventory_item_id = l_line_tbl(I).supplied_item_id
198 and sourcing_level not in (2,9))
199 order by misl.rank;
200 */
201 IF (l_source_type = 3 and l_repair_supplier_id IS NULL) THEN
202 FND_MESSAGE.SET_NAME('CSP', 'CSP_REPAIR_SUPPLIER_NULL');
203 FND_MSG_PUB.ADD;
204 RAISE EXCP_USER_DEFINED;
205 END IF;
206 /*
207 exception
208 WHEN NO_DATA_FOUND THEN
209 FND_MESSAGE.SET_NAME('CSP', 'CSP_REPAIR_ASSIGNMENT_NULL');
210 FND_MSG_PUB.ADD;
211 RAISE EXCP_USER_DEFINED;
212 end;
213 */
214 IF (l_source_type = 1) THEN
215 l_int_rpr_header_rec.dest_organization_id := p_organization_id;
216 l_int_rpr_header_rec.need_by_date := l_line_Tbl(I).plan_date;
217 l_int_rpr_line_tbl(1).inventory_item_id := l_line_Tbl(I).supplied_item_id;
218 l_int_rpr_line_tbl(1).quantity := l_line_Tbl(I).quantity;
219 l_int_rpr_line_tbl(1).ordered_quantity := l_line_Tbl(I).quantity;
220 l_int_rpr_line_tbl(1).unit_of_measure := l_line_Tbl(I).uom_code;
221 l_int_rpr_line_tbl(1).source_organization_id := l_line_Tbl(I).source_organization_id;
222
223 csp_parts_repair.create_orders(
224 p_api_Version => 1.0,
225 p_init_msg_list => null,
226 p_commit => null,
227 px_header_rec => l_int_rpr_header_rec,
228 px_line_table => l_int_rpr_line_tbl,
229 p_repair_supplier_id => l_repair_organization_id,
230 x_return_status => l_return_status,
231 x_msg_count => l_msg_count,
232 x_msg_data => l_msg_data
233 );
234 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
235 RAISE FND_API.G_EXC_ERROR;
236 END If;
237 ELSE
238 begin
239 Select organization_id
240 into l_repair_supplier_org_id
241 from hr_organization_information
242 where ORG_INFORMATION_CONTEXT = 'Customer/Supplier Association'
243 and org_information3 = l_repair_supplier_id;
244
245 Begin
246 SELECT repair_program
247 INTO l_repair_program
248 FROM mtl_system_items_b
249 WHERE organization_id = l_repair_supplier_org_id
250 AND inventory_item_id = p_inventory_item_id;
251 Exception
252 When no_data_found then
253 l_repair_program := 3; --Repair Return
254 End;
255
256 IF l_Repair_Supplier_id IS NOT NULL THEN
257
258 l_Defective_parts_tbl(1).defective_item_id := l_line_tbl(I).supplied_item_id;
259 l_Defective_parts_tbl(1).defective_quantity := l_line_tbl(I).quantity;
260 CSP_REPAIR_PO_GRP.CREATE_REPAIR_PO
261 (p_api_version => 1.0
262 ,p_Init_Msg_List => null
263 ,p_commit => FND_API.G_FALSE
264 ,P_repair_supplier_id => l_repair_supplier_id
265 ,P_repair_supplier_org_id => l_repair_supplier_org_id
266 ,P_repair_program => l_repair_program
267 ,P_dest_organization_id => p_organization_id
268 ,P_source_organization_id => l_line_Tbl(I).source_organization_id
269 ,P_repair_to_item_id => p_inventory_item_id
270 ,P_quantity => l_line_Tbl(I).quantity
271 ,P_need_by_date => l_line_Tbl(I).plan_date
272 ,P_defective_parts_tbl => l_defective_parts_tbl
273 ,x_requisition_header_id => l_requisition_header_id
274 ,x_return_status => l_Return_status
275 ,x_msg_count => l_msg_count
276 ,x_msg_data => l_msg_data
277 );
278 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
279 RAISE FND_API.G_EXC_ERROR;
280 END If;
281 ELSE
282 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_REPAIR_SUPPLIER_ORG');
283 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_NAME', to_char(null), TRUE);
284 FND_MSG_PUB.ADD;
285 RAISE EXCP_USER_DEFINED;
286 END IF;
287 exception
288 when no_data_found then
289 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_REPAIR_SUPPLIER_ORG');
290 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_NAME', to_char(null), TRUE);
291 FND_MSG_PUB.ADD;
292 RAISE EXCP_USER_DEFINED;
293 end;
294 END IF;
295 ELSIF l_line_tbl(I).planned_order_type = 4310 THEN
296 -- check item make buy code
297 IF (l_item_attr_rec.uom IS NULL) THEN
298 OPEN item_Attr_cur;
299 FETCH item_attr_cur INTO l_item_attr_rec;
300 CLOSE item_attr_cur;
301 END IF;
302
303 /* IF (l_item_attr_rec.mbf = 1) THEN
304 SELECT WIP_JOB_SCHEDULE_INTERFACE_S.nextval
305 INTO l_wip_id
306 FROM dual;
307
308 IF (l_item_attr_rec.build_in_wip <> 'Y' OR
309 l_item_attr_rec.pick_components <> 'N') THEN
310 FND_MESSAGE.SET_NAME('CSP', 'CSP_WIP_ORDER_ERROR');
311 FND_MSG_PUB.ADD;
312 RAISE EXCP_USER_DEFINED;
313 END IF;
314
315 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
316 LAST_UPDATE_DATE,
317 LAST_UPDATED_BY,
318 CREATION_DATE,
319 CREATED_BY,
320 GROUP_ID,
321 PROCESS_PHASE,
322 PROCESS_STATUS,
323 ORGANIZATION_ID,
324 LOAD_TYPE,
325 LAST_UNIT_COMPLETION_DATE,
326 PRIMARY_ITEM_ID,
327 START_QUANTITY,STATUS_TYPE)
328 VALUES(
329 sysdate,
330 l_user_id,
331 sysdate,
332 nvl(fnd_global.login_id, 0),
333 l_wip_id,
334 2,
335 1,
336 p_organization_id,
337 1,
338 l_line_tbl(I).plan_date,
339 l_line_tbl(I).supplied_item_id,
340 l_line_tbl(I).quantity,
341 3);
342 ELSE
343 */
344 IF ((l_item_attr_Rec.src_type IS NULL) OR
345 (l_item_Attr_Rec.src_type = 1 AND l_item_attr_rec.order_flag <> 'Y') OR
346 (l_item_attr_rec.src_type = 2 AND l_item_attr_rec.purch_flag <> 'Y')) THEN
347 FND_MESSAGE.SET_NAME('CSP', 'CSP_CREATE_REQ_ERROR');
348 FND_MSG_PUB.ADD;
349 RAISE EXCP_USER_DEFINED;
350 END IF;
351
352 IF (l_item_attr_rec.src_type = 2) THEN
353 IF (l_po_hdr_rec.dest_organization_id IS NULL) THEN
354 l_po_hdr_rec.dest_organization_id := p_organization_id;
355 l_po_hdr_rec.ship_To_location_id := l_location_id;
356 END IF;
357 l_po_line_tbl(K).inventory_item_id := l_line_Tbl(I).supplied_item_id;
358 l_po_line_tbl(K).quantity := l_line_tbl(I).quantity;
359 l_po_line_tbl(K).ordered_quantity := l_line_Tbl(I).quantity;
360 l_po_line_tbl(K).unit_of_measure := l_line_Tbl(I).uom_code;
361 l_po_line_tbl(K).line_num := K;
362 l_po_line_tbl(K).need_by_date := l_line_Tbl(I).plan_date;
363 K := K + 1;
364 ELSIF (l_item_attr_rec.src_type = 1) THEN
365 IF l_order_hdr_rec.dest_organization_id IS NULL THEN
366 l_order_hdr_rec.dest_organization_id := p_organization_id;
367 FND_PROFILE.GET('CSP_ORDER_TYPE', l_order_hdr_rec.order_type_id);
368 l_order_hdr_Rec.ship_to_location_id := l_location_id;
369 END IF;
370 l_order_line_tbl(J).inventory_item_id := l_line_Tbl(I).supplied_item_id;
371 l_order_line_tbl(J).quantity := l_line_Tbl(I).quantity;
372 l_order_line_tbl(J).ordered_quantity := l_line_Tbl(I).quantity;
373 l_order_line_tbl(J).unit_of_measure := l_line_Tbl(I).uom_code;
374 l_order_line_tbl(J).source_organization_id := l_item_attr_rec.src_org; --l_line_Tbl(I).source_organization_id;
375 l_order_line_tbl(J).need_by_Date := l_line_tbl(I).plan_Date;
376 l_order_line_tbl(J).line_num := J;
377 J := J + 1;
378 END If;
379 --END IF;
380 END IF;
381 END LOOP;
382
383 IF (l_order_line_tbl.COUNT > 0) THEN
384 l_order_hdr_rec.operation := 'CREATE';
385 l_order_hdr_rec.need_by_date :=
386 nvl(l_order_hdr_rec.need_by_date,l_order_line_tbl(1).need_by_date);
387 csp_parts_order.process_order(
388 p_api_version => l_api_version_number
389 ,p_Init_Msg_List => p_init_msg_list
390 ,p_commit => p_commit
391 ,px_header_rec => l_order_hdr_rec
392 ,px_line_table => l_order_line_tbl
393 ,x_return_status => l_return_status
394 ,x_msg_count => l_msg_count
395 ,x_msg_data => l_msg_data
396 );
397 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
398 RAISE FND_API.G_EXC_ERROR;
399 END IF;
400 END IF;
401
402 IF (l_po_line_tbl.COUNT > 0) THEN
403 csp_parts_order.process_purchase_req(
404 p_api_version => l_api_version_number
405 ,p_init_msg_list => p_init_msg_list
406 ,p_commit => p_commit
407 ,px_header_rec => l_po_hdr_Rec
408 ,px_line_Table => l_po_line_tbl
409 ,x_return_status => l_return_status
410 ,x_msg_count => l_msg_count
411 ,x_msg_data => l_msg_data
412 );
413
414 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
415 RAISE FND_API.G_EXC_ERROR;
416 END IF;
417 END IF;
418
419 COMMIT;
420 EXCEPTION
421 WHEN FND_API.G_EXC_ERROR THEN
422 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
423 P_API_NAME => L_API_NAME
424 ,P_PKG_NAME => G_PKG_NAME
425 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
426 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
427 ,X_MSG_COUNT => X_MSG_COUNT
428 ,X_MSG_DATA => X_MSG_DATA
429 ,X_RETURN_STATUS => X_RETURN_STATUS);
430 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
431 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
432 P_API_NAME => L_API_NAME
433 ,P_PKG_NAME => G_PKG_NAME
434 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
435 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
436 ,X_MSG_COUNT => X_MSG_COUNT
437 ,X_MSG_DATA => X_MSG_DATA
438 ,X_RETURN_STATUS => X_RETURN_STATUS);
439 WHEN OTHERS THEN
440 Rollback to create_orders_pub;
441 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
442 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, FALSE);
443 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, FALSE);
444 FND_MSG_PUB.ADD;
445 fnd_msg_pub.count_and_get
446 ( p_count => x_msg_count
447 , p_data => x_msg_data);
448 x_return_status := FND_API.G_RET_STS_ERROR;
449
450 END;
451 END;