[Home] [Help]
PACKAGE BODY: APPS.CSP_NOTIFICATIONS_ORDER_PKG
Source
1 PACKAGE BODY CSP_NOTIFICATIONS_ORDER_PKG AS
2 /* $Header: cspgnopb.pls 120.0.12020000.5 2013/03/21 04:14:05 sunarasi noship $ */
3 -- Start of Comments
4 -- Package name :CSP_NOTIFICATIONS_ORDER_PKG
5 -- Purpose :
6 -- History :
7 -- 01-Mar-13, sunarasi
8 --
9 -- NOTE :
10 -- End of Comments
11 g_pkg_name constant varchar2(30):='CSP_NOTIFICATIONS_ORDER_PKG';
12 FUNCTION Create_Order(p_item_id NUMBER,
13 p_Dest_orgn_id NUMBER,
14 p_src_orgn_id NUMBER,
15 p_src_subinv VARCHAR2,
16 p_src_type VARCHAR2,
17 p_repair_supplier_id NUMBER,
18 p_qty NUMBER,
19 p_need_date date,
20 p_notification_id NUMBER) RETURN notif_order_cur AS
21 CURSOR item_attr_cur IS
22 SELECT c.description item_Description,
23 c.planning_make_buy_code mbf,
24 c.primary_uom_code uom,
25 p.ap_accrual_account accru_Acct,
26 p.invoice_price_var_account ipv_acct,
27 nvl(p.encumbrance_account, c.encumbrance_account) budget_Acct,
28 decode(c.inventory_asset_flag, 'Y', p.material_account,
29 NVL(c.expense_Account, p.expense_Account)) charge_Acct,
30 NVL(c.source_type, p.source_type) src_type,
31 DECODE(c.source_type, NULL,
32 DECODE(p.source_type, NULL, NULL, p.source_organization_id),
33 c.source_organization_id) src_org,
34 DECODE(c.source_type, NULL,
35 DECODE(p.source_type, NULL, NULL, p.source_subinventory),
36 c.source_subinventory) src_subinv,
37 c.purchasing_enabled_flag purch_flag,
38 c.internal_order_enabled_flag order_flag,
39 c.mtl_transactions_enabled_flag transact_flag,
40 c.list_price_per_unit unit_price,
41 c.planner_code planner,
42 build_in_wip_flag build_in_wip,
43 pick_components_flag pick_components
44 FROM mtl_system_items c, mtl_parameters p
45 WHERE c.inventory_item_id = p_item_id
46 AND c.organization_id = p.organization_id
47 AND p.organization_id = p_dest_orgn_id;
48
49 l_item_Attr_rec item_attr_cur%ROWTYPE;
50 l_user_id NUMBER;
54 l_encum_flag VARCHAR2(30) := 'N';
51 l_employee_id NUMBER;
52 l_location_id NUMBER;
53 l_po_org_id NUMBER;
55 l_wip_id NUMBER;
56 l_src_type NUMBER;
57 l_api_name VARCHAR2(30) := 'Create_Order';
58 l_header_rec csp_parts_requirement.header_rec_type;
59 l_line_tbl csp_parts_requirement.line_tbl_type;
60 l_msg_data VARCHAR2(10000);
61 l_msg_count NUMBER;
62 l_return_status VARCHAR2(1);
63 l_requisition_header_id NUMBER;
64 l_requisition_line_id NUMBER;
65 l_order_number NUMBER;
66 l_notif_detail_id number;
67 l_notif_order_cur notif_order_cur;
68 x_ret_status number;
69 x_msg varchar2(10000);
70 CURSOR employee_id_cur IS
71 SELECT employee_id
72 FROM fnd_user
73 WHERE user_id = l_user_id;
74
75 CURSOR location_id_cur IS
76 SELECT location_id
77 FROM hr_organization_units
78 WHERE organization_id = p_dest_orgn_id;
79
80 CURSOR po_org_id_cur IS
81 SELECT operating_unit
82 FROM org_organization_definitions
83 WHERE organization_id = p_dest_orgn_id;
84
85 CURSOR repair_to_item_cur IS
86 SELECT related_item_id
87 FROM mtl_related_items_view
88 WHERE relationship_type_id = 18
89 AND inventory_item_id = p_item_id;
90 l_repair_to_item NUMBER;
91
92 BEGIN
93
94 OPEN item_attr_cur;
95 FETCH item_attr_cur INTO l_item_attr_Rec;
96 CLOSE item_attr_cur;
97
98 l_user_id := nvl(fnd_global.user_id, 0);
99
100 IF ((p_src_type IS NULL AND (l_item_attr_rec.mbf = 2)) OR
101 ((p_src_type <> 'WIP') AND (p_src_type <> 'REPAIR'))) THEN
102
103 -- Get location id
104 OPEN location_id_cur;
105 FETCH location_id_cur INTO l_location_id;
106 CLOSE location_id_cur;
107
108 IF (p_src_type = 'EXCESS' OR p_src_type = 'IO') THEN
109 l_src_type := 1;
110 ELSIF (p_src_type = 'PO') THEN
111 l_Src_type := 2;
112 END IF;
113
114 IF ((l_src_type IS NULL AND l_item_attr_Rec.src_type IS NULL) OR
115 (nvl(l_src_type,l_item_Attr_Rec.src_type) = 1 AND l_item_attr_rec.order_flag <> 'Y') OR
116 (nvl(l_src_type,l_item_attr_rec.src_type) = 2 AND l_item_attr_rec.purch_flag <> 'Y')) THEN
117 x_ret_status := -1;
118 END IF;
119
120 IF (nvl(l_src_type, l_item_attr_rec.src_type) = 1) THEN
121 l_header_rec.dest_organization_id := p_dest_orgn_id;
122 l_header_Rec.need_by_date := p_need_Date;
123 l_header_rec.operation := 'CREATE';
124 l_header_rec.ship_to_location_id := l_location_id;
125 FND_PROFILE.GET('CSP_ORDER_TYPE', l_header_rec.order_type_id);
126
127 l_line_tbl(1).line_num := 1;
128 l_line_tbl(1).inventory_item_id := p_item_id;
129 l_line_tbl(1).quantity := p_qty;
130 l_line_tbl(1).ordered_quantity := p_qty;
131 l_line_Tbl(1).unit_of_measure := l_item_Attr_rec.uom;
132 l_line_Tbl(1).source_organization_id := p_src_orgn_id;
133 l_line_Tbl(1).source_subinventory := p_src_subinv;
134 l_line_tbl(1).booked_flag := 'Y';
135
136 -- call process order
137 csp_parts_order.process_order(
138 p_api_version => 1.0
139 ,p_Init_Msg_List => null
140 ,p_commit => null
141 ,px_header_rec => l_header_Rec
142 ,px_line_table => l_Line_Tbl
143 ,p_process_type => 'BOTH'
144 ,x_return_status => l_return_status
145 ,x_msg_count => l_msg_count
146 ,x_msg_data => l_msg_data
147 );
148
149 IF (l_return_status <> csp_pick_utils.Get_Ret_Sts_Success) THEN
150 if nvl(l_msg_count,0) > 0 then
151 -- dbms_output.put_line(l_msg_data);
152 jtf_plsql_api.Get_Messages(
153 p_message_count =>l_msg_count,
154 x_msgs => l_msg_data);
155 x_ret_status := -1;
156 x_msg := l_msg_data;
157 ELSE
158 BEGIN
159 SELECT order_number
160 INTO l_order_number
161 FROM oe_order_headers_all
162 WHERE header_id = l_header_rec.order_header_id;
163 EXCEPTION
164 WHEN no_data_found THEN
165 l_order_number := null;
166 END;
167
168 CSP_Notification_Details_PKG.Insert_Row(
169 px_NOTIFICATION_DETAIL_ID => l_notif_detail_id
170 ,p_NOTIFICATION_ID => p_notification_id
171 ,p_INVENTORY_ITEM_ID => p_item_id
172 ,p_AVAILABLE_QUANTITY => p_qty
173 ,p_ORDER_BY_DATE => p_need_Date
174 ,p_SOURCE_TYPE => p_src_type
175 ,p_SOURCE_ORGANIZATION_ID => p_Src_orgn_id
176 ,p_SOURCE_SUBINVENTORY => p_src_subinv
177 ,p_CREATED_BY => nvl(fnd_global.user_id, 0)
178 ,p_CREATION_DATE => sysdate
179 ,p_LAST_UPDATED_BY => nvl(fnd_global.user_id, 0)
180 ,p_LAST_UPDATE_DATE => sysdate
181 ,p_LAST_UPDATE_LOGIN => nvl(fnd_global.login_id, -1)
182 ,p_ATTRIBUTE_CATEGORY => null
183 ,p_ATTRIBUTE1 => null
184 ,p_ATTRIBUTE2 => null
185 ,p_ATTRIBUTE3 => null
186 ,p_ATTRIBUTE4 => null
187 ,p_ATTRIBUTE5 => null
191 ,p_ATTRIBUTE9 => null
188 ,p_ATTRIBUTE6 => null
189 ,p_ATTRIBUTE7 => null
190 ,p_ATTRIBUTE8 => null
192 ,p_ATTRIBUTE10 => null
193 ,p_ATTRIBUTE11 => null
194 ,p_ATTRIBUTE12 => null
195 ,p_ATTRIBUTE13 => null
196 ,p_ATTRIBUTE14 => null
197 ,p_ATTRIBUTE15 => null
198 ,p_REPAIR_SUPPLIER_ID => null
199 ,p_ORDER_NUMBER => l_order_number
200 );
201 END IF;
202 ELSIF (nvl(l_src_type, l_item_attr_rec.src_type) = 2) THEN
203
204 OPEN employee_id_cur;
205 FETCH employee_id_cur INTO l_employee_id;
206 CLOSE employee_id_cur;
207
208 OPEN po_org_id_cur;
209 FETCH po_org_id_cur INTO l_po_org_id;
210 CLOSE po_org_id_cur;
211
212 BEGIN
213 select nvl(req_encumbrance_flag, 'N')
214 into l_encum_flag
215 from financials_system_params_all
216 where nvl(org_id, -11) = nvl(l_po_org_id, -11);
217 EXCEPTION
218 WHEN OTHERS THEN
219 l_encum_flag := 'N';
220 END;
221
222 IF (l_item_attr_Rec.charge_Acct IS NULL) OR
223 (l_item_attr_rec.accru_acct IS NULL) OR
224 (l_item_attr_Rec.ipv_acct IS NULL) OR
225 ((l_encum_flag <> 'N') AND (l_item_attr_rec.budget_acct IS NULL)) THEN
226 x_ret_status := -1;
227 x_msg := 'CSP_ACCTS_NOT_SETUP';
228 END IF;
229
230 -- requisition_header_id
231 SELECT po_requisition_headers_s.nextval
232 INTO l_requisition_header_id
233 FROM sys.dual;
234
235 -- Get unique requisition_line_id
236 SELECT po_requisition_lines_s.nextval
237 INTO l_requisition_line_id
238 FROM sys.dual;
239
240 -- create po order
241 INSERT INTO po_requisitions_interface_all(
242 LAST_UPDATE_DATE,
243 LAST_UPDATED_BY,
244 ITEM_DESCRIPTION,
245 CREATION_DATE,
246 CREATED_BY,
247 PREPARER_ID,
248 INTERFACE_SOURCE_CODE,
249 REQUISITION_TYPE,
250 AUTHORIZATION_STATUS,
251 SOURCE_TYPE_CODE,
252 SOURCE_ORGANIZATION_ID,
253 SOURCE_SUBINVENTORY,
254 DESTINATION_ORGANIZATION_ID,
255 DESTINATION_SUBINVENTORY,
256 DELIVER_TO_REQUESTOR_ID,
257 DESTINATION_TYPE_CODE,
258 UOM_CODE,
259 DELIVER_TO_LOCATION_ID,
260 ITEM_ID,
261 ITEM_REVISION,
262 QUANTITY,
263 NEED_BY_DATE,
264 GL_DATE,
265 CHARGE_ACCOUNT_ID,
266 ACCRUAL_ACCOUNT_ID,
267 VARIANCE_ACCOUNT_ID,
268 BUDGET_ACCOUNT_ID,
269 AUTOSOURCE_FLAG,
270 ORG_ID,
271 UNIT_PRICE)
272 VALUES (
273 sysdate,
274 l_user_id,
275 l_item_attr_rec.item_Description,
276 sysdate,
277 nvl(fnd_global.login_id, -1),
278 l_employee_id,
279 'CSP',
280 'PURCHASE',
281 'APPROVED',
282 'VENDOR',
283 nvl(p_src_orgn_id, l_item_attr_rec.src_org),
284 nvl(p_src_subinv, l_item_attr_rec.src_subinv),
285 p_dest_orgn_id,
286 NULL, -- destination subinv
287 l_employee_id,
288 'INVENTORY',
289 l_item_Attr_Rec.uom,
290 l_location_id,
291 p_item_id,
292 NULL, -- DECODE(l_item_revision,'@@@',NULL,l_item_revision),
293 p_qty,
294 trunc(p_need_date),
295 SYSDATE,
296 l_item_attr_rec.charge_acct,
297 l_item_attr_rec.accru_acct,
298 l_item_attr_rec.ipv_acct,
299 l_item_attr_rec.budget_acct,
300 'P',
301 l_po_org_id,
302 l_item_attr_rec.unit_price);
303
304 CSP_Notification_Details_PKG.Insert_Row(
305 px_NOTIFICATION_DETAIL_ID => l_notif_detail_id
306 ,p_NOTIFICATION_ID => p_notification_id
307 ,p_INVENTORY_ITEM_ID => p_item_id
308 ,p_AVAILABLE_QUANTITY => p_qty
309 ,p_ORDER_BY_DATE => p_need_date
310 ,p_SOURCE_TYPE => 'PO'
311 ,p_SOURCE_ORGANIZATION_ID => p_Src_orgn_id
312 ,p_SOURCE_SUBINVENTORY => p_src_subinv
313 ,p_CREATED_BY => nvl(fnd_global.user_id, 0)
314 ,p_CREATION_DATE => sysdate
315 ,p_LAST_UPDATED_BY => nvl(fnd_global.user_id, 0)
316 ,p_LAST_UPDATE_DATE => sysdate
317 ,p_LAST_UPDATE_LOGIN => nvl(fnd_global.login_id, -1)
321 ,p_ATTRIBUTE3 => null
318 ,p_ATTRIBUTE_CATEGORY => null
319 ,p_ATTRIBUTE1 => null
320 ,p_ATTRIBUTE2 => null
322 ,p_ATTRIBUTE4 => null
323 ,p_ATTRIBUTE5 => null
324 ,p_ATTRIBUTE6 => null
325 ,p_ATTRIBUTE7 => null
326 ,p_ATTRIBUTE8 => null
327 ,p_ATTRIBUTE9 => null
328 ,p_ATTRIBUTE10 => null
329 ,p_ATTRIBUTE11 => null
330 ,p_ATTRIBUTE12 => null
331 ,p_ATTRIBUTE13 => null
332 ,p_ATTRIBUTE14 => null
333 ,p_ATTRIBUTE15 => null
334 ,p_REPAIR_SUPPLIER_ID => null
335 ,p_ORDER_NUMBER => null
336 );
337
338 END IF;
339 ELSIF (p_src_type = 'WIP') THEN
340 SELECT WIP_JOB_SCHEDULE_INTERFACE_S.nextval
341 INTO l_wip_id
342 FROM dual;
343
344 IF (l_item_attr_rec.build_in_wip <> 'Y' OR
345 l_item_attr_rec.pick_components <> 'N') THEN
346 x_ret_status := -1;
347 x_msg := 'CSP_WIP_ORDER_ERROR';
348 END IF;
349
350 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
351 LAST_UPDATE_DATE,
352 LAST_UPDATED_BY,
353 CREATION_DATE,
354 CREATED_BY,
355 GROUP_ID,
356 PROCESS_PHASE,
357 PROCESS_STATUS,
358 ORGANIZATION_ID,
359 LOAD_TYPE,
360 LAST_UNIT_COMPLETION_DATE,
361 PRIMARY_ITEM_ID,
362 START_QUANTITY,STATUS_TYPE)
363 VALUES(
364 sysdate,
365 l_user_id,
366 sysdate,
367 nvl(fnd_global.login_id, 0),
368 l_wip_id,
369 2,
370 1,
371 p_dest_orgn_id,
372 1,
373 p_need_date,
374 p_item_id,
375 p_qty,
376 3); -- Approved
377
378 CSP_Notification_Details_PKG.Insert_Row(
379 px_NOTIFICATION_DETAIL_ID => l_notif_detail_id
380 ,p_NOTIFICATION_ID => p_notification_id
381 ,p_INVENTORY_ITEM_ID => p_item_id
382 ,p_AVAILABLE_QUANTITY => p_qty
383 ,p_ORDER_BY_DATE => p_need_date
384 ,p_SOURCE_TYPE => 'WIP'
385 ,p_SOURCE_ORGANIZATION_ID => p_Src_orgn_id
386 ,p_SOURCE_SUBINVENTORY => p_src_subinv
387 ,p_CREATED_BY => nvl(fnd_global.user_id, 0)
388 ,p_CREATION_DATE => sysdate
389 ,p_LAST_UPDATED_BY => nvl(fnd_global.user_id, 0)
390 ,p_LAST_UPDATE_DATE => sysdate
391 ,p_LAST_UPDATE_LOGIN => nvl(fnd_global.login_id, -1)
392 ,p_ATTRIBUTE_CATEGORY => null
393 ,p_ATTRIBUTE1 => null
394 ,p_ATTRIBUTE2 => null
395 ,p_ATTRIBUTE3 => null
396 ,p_ATTRIBUTE4 => null
397 ,p_ATTRIBUTE5 => null
398 ,p_ATTRIBUTE6 => null
399 ,p_ATTRIBUTE7 => null
400 ,p_ATTRIBUTE8 => null
401 ,p_ATTRIBUTE9 => null
402 ,p_ATTRIBUTE10 => null
403 ,p_ATTRIBUTE11 => null
404 ,p_ATTRIBUTE12 => null
405 ,p_ATTRIBUTE13 => null
406 ,p_ATTRIBUTE14 => null
407 ,p_ATTRIBUTE15 => null
408 ,p_REPAIR_SUPPLIER_ID => null
409 ,p_ORDER_NUMBER => null
410 );
411
412 ELSIF (p_src_type = 'REPAIR') THEN
413 IF (p_repair_supplier_id IS NULL) THEN
414 x_ret_status := -1;
415 x_msg := 'CSP_NO_REPAIR_SUPPLIER';
416 END IF;
417
418 l_header_rec.dest_organization_id := p_dest_orgn_id;
419 l_header_Rec.need_by_date := p_need_Date;
420 l_line_tbl(1).inventory_item_id := p_item_id;
421 l_line_tbl(1).quantity := p_qty;
422 l_line_tbl(1).ordered_quantity := p_qty;
423 l_line_Tbl(1).unit_of_measure := l_item_Attr_rec.uom;
424 l_line_Tbl(1).source_organization_id := p_src_orgn_id;
425 l_line_Tbl(1).source_subinventory := p_src_subinv;
426 l_line_tbl(1).booked_flag := 'Y';
427
428 csp_parts_repair.create_orders(
429 p_api_Version => 1.0,
430 p_init_msg_list => null,
431 p_commit => null,
432 px_header_rec => l_header_rec,
433 px_line_table => l_line_tbl,
434 p_repair_supplier_id => p_repair_supplier_id,
435 x_return_status => l_return_status,
436 x_msg_count => l_msg_count,
437 x_msg_data => l_msg_data
438 );
439
440 IF (l_return_status <> csp_pick_utils.Get_Ret_Sts_Success) THEN
441 jtf_plsql_api.Get_Messages(
442 p_message_count =>l_msg_count,
443 x_msgs => l_msg_data);
444 x_ret_status := -1;
445 x_msg := l_msg_data;
446 ELSE
447 x_ret_status := -1;
448 x_msg := 'CSP_UNKNOWN_ERRORS';
449 END IF;
450 ELSE
451 BEGIN
452 SELECT order_number
453 INTO l_order_number
454 FROM oe_order_headers_all
455 WHERE header_id = l_header_rec.order_header_id;
456 EXCEPTION
457 WHEN no_data_found THEN
458 l_order_number := null;
459 END;
460 OPEN repair_to_item_cur;
461 FETCH repair_to_item_cur INTO l_repair_to_item;
462 CLOSE repair_to_item_cur;
463
464 CSP_Notification_Details_PKG.Insert_Row(
465 px_NOTIFICATION_DETAIL_ID => l_notif_detail_id
466 ,p_NOTIFICATION_ID => p_notification_id
467 ,p_INVENTORY_ITEM_ID => nvl(l_repair_to_item, p_item_id)
468 ,p_AVAILABLE_QUANTITY => p_qty
469 ,p_ORDER_BY_DATE => p_need_date
470 ,p_SOURCE_TYPE => p_src_type
471 ,p_SOURCE_ORGANIZATION_ID => p_repair_supplier_id
472 ,p_SOURCE_SUBINVENTORY => null
473 ,p_CREATED_BY => nvl(fnd_global.user_id, 0)
474 ,p_CREATION_DATE => sysdate
475 ,p_LAST_UPDATED_BY => nvl(fnd_global.user_id, 0)
476 ,p_LAST_UPDATE_DATE => sysdate
477 ,p_LAST_UPDATE_LOGIN => nvl(fnd_global.login_id, -1)
478 ,p_ATTRIBUTE_CATEGORY => null
479 ,p_ATTRIBUTE1 => null
480 ,p_ATTRIBUTE2 => null
481 ,p_ATTRIBUTE3 => null
482 ,p_ATTRIBUTE4 => null
483 ,p_ATTRIBUTE5 => null
484 ,p_ATTRIBUTE6 => null
485 ,p_ATTRIBUTE7 => null
486 ,p_ATTRIBUTE8 => null
487 ,p_ATTRIBUTE9 => null
488 ,p_ATTRIBUTE10 => null
489 ,p_ATTRIBUTE11 => null
490 ,p_ATTRIBUTE12 => null
491 ,p_ATTRIBUTE13 => null
492 ,p_ATTRIBUTE14 => null
493 ,p_ATTRIBUTE15 => null
494 ,p_REPAIR_SUPPLIER_ID => null
495 ,p_ORDER_NUMBER => l_order_number
496 );
497 END IF;
498 end if;
499 open l_notif_order_cur for
500 select x_ret_status,x_msg from dual;
501 Return l_notif_order_cur;
502 END;
503 END;