[Home] [Help]
PACKAGE BODY: APPS.MRP_CREATE_SCHEDULE_ISO
Source
1 PACKAGE BODY MRP_CREATE_SCHEDULE_ISO AS
2 /* $Header: MRPCISOB.pls 120.12.12010000.3 2008/12/22 23:54:59 ahoque ship $ */
3
4 l_debug varchar2(30) := FND_PROFILE.Value('MRP_DEBUG');
5
6
7 /********************************************************
8 PROCEDURE : log_message
9 ********************************************************/
10
11 PROCEDURE log_message( p_user_info IN VARCHAR2) IS
12 BEGIN
13 FND_FILE.PUT_LINE(FND_FILE.LOG, p_user_info);
14 EXCEPTION
15 WHEN OTHERS THEN
16 RAISE;
17 END log_message;
18
19 PROCEDURE log_output( p_user_info IN VARCHAR2) IS
20 BEGIN
21 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, p_user_info);
22 EXCEPTION
23 WHEN OTHERS THEN
24 RAISE;
25 END log_output;
26
27 PROCEDURE debug_message( p_user_info IN VARCHAR2) IS
28 BEGIN
29 IF l_debug = 'Y' THEN
30 log_message(p_user_info);
31 END IF;
32 EXCEPTION
33 WHEN OTHERS THEN
34 RAISE;
35 END debug_message;
36
37
38 /**********************************************************
39 Procedure: MSC_RELEASE_ISO
40 ***********************************************************/
41 PROCEDURE MSC_RELEASE_ISO( p_batch_id IN number,
42 p_load_type IN number,
43 arg_int_req_load_id IN OUT NOCOPY Number,
44 arg_int_req_resched_id IN OUT NOCOPY Number ) IS
45
46
47 l_request number;
48 l_result BOOLEAN;
49 BEGIN
50
51
52 l_result := fnd_request.set_mode(TRUE);
53 l_request := FND_REQUEST.SUBMIT_REQUEST
54 ('MSC',
55 'MSC_RELEASE_ISO',
56 'MSC ISO release Program',
57 null,
58 FALSE,
59 p_batch_id);
60
61 IF nvl(l_request,0) = 0 THEN
62 LOG_MESSAGE('Error in MSC_RELEASE_ISO');
63 ELSE
64 IF p_load_type = DRP_REQ_LOAD THEN
65 arg_int_req_load_id := l_request;
66 arg_int_req_resched_id := null;
67 LOG_MESSAGE('Concurrent Request ID For ISO Load : ' || arg_int_req_load_id);
68 ELSE
69 arg_int_req_load_id := null;
70 arg_int_req_resched_id := l_request;
71 LOG_MESSAGE('Concurrent Request ID For ISO Re-Schedule: ' || arg_int_req_resched_id);
72 END IF;
73
74 LOG_MESSAGE('MSC_RELEASE_ISO completed successfully');
75 END IF;
76
77 END MSC_RELEASE_ISO;
78
79 /**********************************************************
80 Procedure: Create_IR_ISO creates 1 IR + creates 1 ISO for the specific row in mrp_org_transfer_Release
81 ***********************************************************/
82 PROCEDURE Create_IR_ISO( errbuf OUT NOCOPY VARCHAR2,
83 retcode OUT NOCOPY VARCHAR2,
84 p_Ireq_header_id OUT NOCOPY number,
85 p_ISO_header_id OUT NOCOPY number,
86 p_Transaction_id IN number,
87 p_batch_id IN number) IS
88
89 CURSOR c_trans_rel(l_transaction_id number) IS
90 SELECT
91 batch_id, item_id, src_organization_id,
92 sr_instance_id, to_organization_id, to_sr_instance_id,
93 src_operating_unit, to_operating_unit,
94 sales_order_line_id, sales_order_number,
95 quantity, need_by_date, ship_date,
96 deliver_to_location_id, deliver_to_requestor_id, preparer_id,
97 uom_code, charge_account_id, group_code, item_revision,
98 project_id, task_id, end_item_number, load_type, firm_demand_flag,
99 ship_method, earliest_ship_date,plan_type,part_condition
100 FROM
101 MRP_ORG_TRANSFER_RELEASE
102 WHERE
103 transaction_id = l_transaction_id
104 ORDER BY src_operating_unit;
105
106 l_org_trans_rel_cur c_trans_rel%ROWTYPE;
107
108 CURSOR c_security (l_org_id number,
109 l_user_id number,
110 l_appl_id number) IS
111 SELECT level_id, level_value
112 FROM fnd_profile_options opt,
113 fnd_profile_option_values opt_vals,
114 fnd_user_resp_groups user_resp
115 WHERE opt.profile_option_name = 'ORG_ID'
116 AND opt.profile_option_id = opt_vals.profile_option_id
117 AND opt_vals.profile_option_value = to_char(l_org_id)
118 AND opt_vals.level_id = 10003 -- responsibility level
119 AND user_resp.user_id = l_user_id
120 AND user_resp.responsibility_id = opt_vals.level_value
121 AND user_resp.responsibility_application_id = l_appl_id
122 AND rownum = 1;
123
124 CURSOR c_order_type_id(p_org_id number) IS
125 SELECT ORDER_TYPE_ID
126 FROM PO_SYSTEM_PARAMETERS_ALL
127 WHERE nvl(ORG_ID,-1) = p_org_id;
128
129
130 CURSOR c_bill_to_location_id(p_customer_id number) IS
131 select org.organization_id
132 from oe_invoice_to_orgs_v org, hz_parties hp, hz_cust_accounts hca
133 where org.customer_id=hca.cust_account_id
134 and hp.party_id = hca.party_id
135 and hca.cust_account_id = p_customer_id
136 and rownum < 2;
137
138 CURSOR c_ship_to_location_id(p_customer_id number, p_site_use_id number) IS
139 select org.organization_id
140 from oe_ship_to_orgs_v org, hz_parties hp, hz_cust_accounts hca
141 where org.customer_id = hca.cust_account_id
142 and hp.party_id = hca.party_id
143 and hca.cust_account_id = p_customer_id
144 and org.site_use_id = p_site_use_id
145 and rownum < 2;
146
147 CURSOR c_header_id(p_order_number number) IS
148 select header_id from oe_order_headers_all
149 where order_number = p_order_number;
150
151
152
153 /* For security */
154 l_user_id NUMBER;
155 l_appl_id NUMBER;
156 l_src_org_id number := 0;
157 l_prev_src_org_id number := -99999;
158 l_level_id number;
159 l_level_value number;
160
161 /* Variables for Process Requisition */
162 l_int_req_Ret_sts varchar2(30);
163 l_req_header_rec PO_CREATE_REQUISITION_SV.Header_rec_Type;
164 l_req_line_tbl PO_CREATE_REQUISITION_SV.Line_Tbl_Type;
165 l_msg_count number;
166 l_msg_data varchar2(2000);
167 k number := 1;
168 j number := 1;
169
170 /* Variables for Process Order */
171 l_api_version_number CONSTANT NUMBER := 1.0;
172 lv_action_rec OE_Order_PUB.Request_Rec_Type;
173 lv_action_req_tbl OE_Order_PUB.Request_Tbl_Type;
174 l_oe_header_rec oe_order_pub.header_rec_type := OE_ORDER_PUB.G_MISS_HEADER_REC;
175 l_oe_line_tbl oe_order_pub.line_tbl_type := OE_ORDER_PUB.G_MISS_LINE_TBL;
176 l_oe_line_rec oe_order_pub.line_rec_type := OE_ORDER_PUB.G_MISS_LINE_REC;
177
178 -- OUT variables
179 l_header_rec OE_Order_PUB.Header_Rec_Type;
180 l_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
181 l_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
182 l_Header_Adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
183 l_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type;
184 l_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type;
185 l_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
186 l_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
187 l_Header_Scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
188 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
189 l_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
190 l_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
191 l_Line_Adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
192 l_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type;
193 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type;
194 l_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type;
195 l_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
196 l_Line_Scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
197 l_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
198 l_Lot_Serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
199 l_action_request_tbl OE_Order_PUB.Request_Tbl_Type;
200
201
202 l_order_number number := 0;
203 l_header_id number;
204 l_order_source_id number := 10;
205 l_price_list_id NUMBER;
206 l_currency_code varchar2(30);
207 l_order_line_type_id number;
208 l_line_price_list_id number;
209 l_order_line_category_code varchar2(30);
210 l_order_line_id number;
211
212 l_order_type_id number; /* DWK need to be modified */
213 l_return_status varchar2(30);
214 l_deliver_to_org_id number:=0;
215 b_has_error BOOLEAN := FALSE;
216
217 l_customer_id NUMBER;
218 l_address_id NUMBER;
219 l_site_use_id NUMBER;
220
221 l_invoice_to_org_id number;
222 l_ship_to_org_id number;
223
224 -- For Loop Back API
225 l_sch_rec OE_SCHEDULE_GRP.sch_rec_type;
226 l_sch_rec_tbl OE_SCHEDULE_GRP.Sch_Tbl_Type;
227 l_request_id NUMBER:= 0;
228 v_success NUMBER:= 0;
229 v_failure NUMBER:= 0;
230 v_load_type VARCHAR2(30);
231
232 l_output_error VARCHAR2(200);
233 lv_sql_query VARCHAR2(4000);
234 lv_source_subinv VARCHAR2(200);
235 lv_Dest_subinv VARCHAR2(200);
236
237 BEGIN
238
239 l_user_id := fnd_global.user_id();
240 l_appl_id := 724; -- Application id for Advanced Supply Chain Planning
241 retcode := 0;
242 log_message('Transaction_id :'||p_transaction_id);
243
244
245 -- For l_org_trans_rel_cur in c_trans_rel(p_transaction_id ) LOOP
246 Open c_trans_rel(p_transaction_id );
247 Fetch c_trans_rel into
248 l_org_trans_rel_cur.batch_id, l_org_trans_rel_cur.item_id, l_org_trans_rel_cur.src_organization_id,
249 l_org_trans_rel_cur.sr_instance_id, l_org_trans_rel_cur.to_organization_id,
250 l_org_trans_rel_cur.to_sr_instance_id,l_org_trans_rel_cur.src_operating_unit,
251 l_org_trans_rel_cur.to_operating_unit,l_org_trans_rel_cur.sales_order_line_id, l_org_trans_rel_cur.sales_order_number,
252 l_org_trans_rel_cur.quantity, l_org_trans_rel_cur.need_by_date, l_org_trans_rel_cur.ship_date,
253 l_org_trans_rel_cur.deliver_to_location_id, l_org_trans_rel_cur.deliver_to_requestor_id, l_org_trans_rel_cur.preparer_id,
254 l_org_trans_rel_cur.uom_code, l_org_trans_rel_cur.charge_account_id, l_org_trans_rel_cur.group_code, l_org_trans_rel_cur.item_revision,
255 l_org_trans_rel_cur.project_id, l_org_trans_rel_cur.task_id, l_org_trans_rel_cur.end_item_number,
256 l_org_trans_rel_cur.load_type, l_org_trans_rel_cur.firm_demand_flag,l_org_trans_rel_cur.ship_method,
257 l_org_trans_rel_cur.earliest_ship_date,l_org_trans_rel_cur.plan_type,l_org_trans_rel_cur.part_condition
258 ;
259
260
261 log_message('Load_type :'||l_org_trans_rel_cur.load_type);
262
263 DECLARE
264 e_creating_iso_err EXCEPTION;
265 e_update_rescheduling_err EXCEPTION;
266 BEGIN
267
268 Savepoint Before_Requisition ;
269
270 l_int_req_ret_sts := FND_API.G_RET_STS_SUCCESS;
271
272 -- Get responsibility id
273 OPEN c_security(l_org_trans_rel_cur.to_operating_unit, l_user_id, l_appl_id);
274 FETCH c_security INTO l_level_id, l_level_value; -- resp_id
275 CLOSE c_security;
276
277 fnd_global.apps_initialize(l_user_id, l_level_value, l_appl_id);
278
279
280 IF (l_org_trans_rel_cur.load_type = DRP_REQ_LOAD) THEN
281 --Pass the Internal Sales Order Header values to the internal req header record
282 l_req_header_rec.preparer_id := l_org_trans_rel_cur.preparer_id;
283 l_req_header_rec.summary_flag := 'N';
284 l_req_header_rec.enabled_flag := 'Y';
285 l_req_header_rec.authorization_status := 'APPROVED';
286 l_req_header_rec.type_lookup_code := 'INTERNAL';
287 l_req_header_rec.transferred_to_oe_flag := 'Y';
288 l_req_header_rec.org_id := l_org_trans_rel_cur.to_operating_unit;
289
290
291 --Pass the Internal Sales Order Line values to the internal req Line table
292 /* DWK Header and line willl be 1 to 1 relationship for creating ISO.
293 There is no reason to make req_line to table.
294 For the consistence, I will use it as a table, but only pass
295 first index */
296 j := 1;
297
298 l_req_line_tbl(j).line_num := j;
299 l_req_line_tbl(j).uom_code := l_org_trans_rel_cur.uom_code;
300 l_req_line_tbl(j).quantity := l_org_trans_rel_cur.quantity;
301 l_req_line_tbl(j).deliver_to_location_id := l_org_trans_rel_cur.deliver_to_location_id;
302 l_req_line_tbl(j).destination_type_code := 'INVENTORY';
303 l_req_line_tbl(j).destination_organization_id := l_org_trans_rel_cur.to_organization_id;
304 l_req_line_tbl(j).to_person_id := l_org_trans_rel_cur.preparer_id;
305 l_req_line_tbl(j).source_type_code := 'INVENTORY';
306 l_req_line_tbl(j).item_id := l_org_trans_rel_cur.item_id;
307 l_req_line_tbl(j).need_by_date := l_org_trans_rel_cur.need_by_date;
308 l_req_line_tbl(j).source_organization_id := l_org_trans_rel_cur.src_organization_id;
309 l_req_line_tbl(j).org_id := l_org_trans_rel_cur.to_operating_unit;
310
311 If (l_org_trans_rel_cur.plan_type = 8) Then
312 lv_sql_query := 'select subinventory_code
313 from CSP_RS_SUBINVENTORIES_V
314 where organization_id = :l_organization_id
315 And condition_type = :l_condition_type
316 AND OWNER_RESOURCE_TYPE='||'''RS_EMPLOYEE'''||
317 'AND OWNER_FLAG='||'''Y'''||
318 'And rownum <2';
319 Begin
320
321 Execute Immediate lv_sql_query
322 into lv_source_subinv
323 using l_org_trans_rel_cur.src_organization_id,
324 l_org_trans_rel_cur.part_condition ;
325
326 Execute Immediate lv_sql_query
327 into lv_Dest_subinv
328 using l_org_trans_rel_cur.to_organization_id,
329 l_org_trans_rel_cur.part_condition ;
330
331 Exception
332 When others then
333 LOG_MESSAGE('Unable to locate a good/bad subinventory in the organziation'||to_char(l_org_trans_rel_cur.src_organization_id));
334 RAISE FND_API.G_EXC_ERROR;
335 End;
336
337 l_req_line_tbl(j).source_subinventory := lv_source_subinv;
338 l_req_line_tbl(j).destination_subinventory := lv_Dest_subinv;
339
340 End if; -- For Plan_Type = 8 SRP Plan
341
342 mo_global.init('PO'); -- MOAC Change
343 mo_global.set_policy_context('S',l_org_trans_rel_cur.to_operating_unit); -- MOAC Change
344
345 /* Call the PO API and pass the internal req header record
346 and line tables to Create the Internal Req */
347 PO_CREATE_REQUISITION_SV.process_requisition(px_header_rec => l_req_header_rec
348 ,px_line_table => l_req_line_tbl
349 ,x_return_status => l_int_req_Ret_sts
350 ,x_msg_count => l_msg_count
351 ,x_msg_data => l_msg_data );
352 --Check return status of the Purchasing API
353 IF l_int_req_Ret_sts = FND_API.G_RET_STS_UNEXP_ERROR THEN
354 LOG_MESSAGE('Error in process_requisition : FND_API.G_RET_STS_UNEXP_ERROR');
355 oe_debug_pub.add(' PO API call returned unexpected error '||l_msg_data,2);
356 log_message('Item ID :' || l_req_line_tbl(j).item_id );
357 log_message('Quantity :' || l_req_line_tbl(j).quantity);
358 log_message('Deliver To loc ID :' || l_req_line_tbl(j).deliver_to_location_id);
359 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
360 ELSIF l_int_req_Ret_sts = FND_API.G_RET_STS_ERROR THEN
361 LOG_MESSAGE('Error in process_requisition : FND_API.G_RET_STS_ERROR');
362 oe_debug_pub.add(' PO API call returned error '||l_msg_data,2);
363 log_message('Error Msg from PO API: ' || l_msg_data );
364 log_message('Item ID :' || l_req_line_tbl(j).item_id );
365 log_message('Quantity :' || l_req_line_tbl(j).quantity);
366 log_message('Deliver To loc ID :' || l_req_line_tbl(j).deliver_to_location_id);
367 RAISE FND_API.G_EXC_ERROR;
368 ELSIF l_int_req_ret_sts = FND_API.G_RET_STS_SUCCESS THEN
369 p_Ireq_header_id:= l_req_header_rec.requisition_header_id;
370 log_message('Successful in Creating requisition.');
371 log_message('Requisition Header ID :' ||l_req_header_rec.requisition_header_id);
372 log_message('Internal Requisition Number : ' || l_req_header_rec.segment1);
373 log_message('Requisition Line Num : ' || l_req_line_tbl(j).line_num);
374 log_message('Requisition Line ID : ' ||l_req_line_tbl(j).requisition_line_id);
375 log_message('Item ID : ' || l_req_line_tbl(j).item_id );
376 log_message('Quantity : ' || l_req_line_tbl(j).quantity);
377 log_message('Deliver To loc ID :' || l_req_line_tbl(j).deliver_to_location_id);
378 END IF;
379
380 END IF; /* End of Creating new requisitions*/
381
382 /* if it returns success Update the Internal Sales Order
383 with the Req header id and Req line Ids */
384 IF l_int_req_ret_sts = FND_API.G_RET_STS_SUCCESS THEN
385
386 /* For Security */
387 IF l_prev_src_org_id <> l_org_trans_rel_cur.src_operating_unit THEN
388 l_prev_src_org_id := l_org_trans_rel_cur.src_operating_unit;
389
390 -- Get Source Operating Unit
391 l_src_org_id := l_org_trans_rel_cur.src_operating_unit;
392
393 -- Get order type id
394 OPEN c_order_type_id(l_src_org_id);
395 FETCH c_order_type_id INTO l_order_type_id;
396 CLOSE c_order_type_id;
397
398 -- Get responsibility id
399 OPEN c_security(l_src_org_id, l_user_id, l_appl_id);
403 fnd_global.apps_initialize(l_user_id, l_level_value, l_appl_id);
400 FETCH c_security INTO l_level_id, l_level_value; -- resp_id
401 CLOSE c_security;
402
404
405 END IF;
406
407 mo_global.init('ONT'); -- MOAC Change
408 mo_global.set_policy_context('S',l_org_trans_rel_cur.src_operating_unit); -- MOAC Change
409
410 /********* Process Order ********************************/
411 -- SETTING UP THE HEADER RECORD
412
413 l_oe_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
414 l_oe_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
415
416 /* For Creating ISO */
417 log_message('DRP_REQ_LOAD :'||DRP_REQ_LOAD);
418 log_message('l_org_trans_rel_cur.load_type :'||l_org_trans_rel_cur.load_type);
419 IF (l_org_trans_rel_cur.load_type = DRP_REQ_LOAD) THEN
420 v_load_type := 'Release';
421
422 l_oe_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
423 l_oe_header_rec.org_id := l_org_trans_rel_cur.src_operating_unit;
424 l_oe_header_rec.order_type_id := l_order_type_id;
425
426 -- requisition number
427 l_oe_header_rec.ORIG_SYS_DOCUMENT_REF := l_req_header_rec.segment1;
428 l_oe_header_rec.SOURCE_DOCUMENT_ID := l_req_header_rec.requisition_header_id;
429 l_oe_header_rec.source_document_type_id := l_order_source_id;
430 l_oe_header_rec.order_source_id := l_order_source_id;
431 l_oe_header_rec.open_flag := 'Y';
432 l_oe_header_rec.booked_flag := 'N';
433 l_oe_header_rec.ship_from_org_id := l_org_trans_rel_cur.src_organization_id;
434 l_oe_header_rec.shipping_method_code := l_org_trans_rel_cur.ship_method;
435
436
437 /* For the line */
438 l_oe_line_rec.operation := OE_GLOBALS.G_OPR_CREATE;
439 l_oe_line_rec.ORIG_SYS_DOCUMENT_REF := l_req_header_rec.segment1;
440 l_oe_line_rec.SOURCE_DOCUMENT_ID := l_req_header_rec.requisition_header_id;
441 l_oe_line_rec.source_document_type_id := l_order_source_id;
442 l_oe_line_rec.order_source_id := l_order_source_id;
443 l_oe_line_rec.ORIG_SYS_LINE_REF := l_req_line_tbl(j).line_num;
444 l_oe_line_rec.source_document_line_id := l_req_line_tbl(j).requisition_line_id;
445 l_oe_line_rec.ordered_quantity := l_org_trans_rel_cur.quantity;
446 l_oe_line_rec.inventory_item_id := l_req_line_tbl(j).item_id;
447 l_oe_line_rec.request_date := l_org_trans_rel_cur.ship_date; -- xxx dsting
448 l_oe_line_rec.promise_date := l_org_trans_rel_cur.ship_date; -- Bug# 4227424
449 l_oe_line_rec.schedule_arrival_date := l_org_trans_rel_cur.need_by_date;
450 l_oe_line_rec.schedule_ship_date := l_org_trans_rel_cur.ship_date;
451 l_oe_line_rec.open_flag := 'Y';
452 l_oe_line_rec.booked_flag := 'N';
453 l_oe_line_rec.ship_from_org_id := l_org_trans_rel_cur.src_organization_id; --l_oe_header_rec.org_id; xxx dsting
454 l_oe_line_rec.firm_demand_flag :=
455 l_org_trans_rel_cur.firm_demand_flag;
456 l_oe_line_rec.Earliest_ship_date := l_org_trans_rel_cur.Earliest_ship_date; -- Bug# 4306515
457
458 l_oe_line_rec.shipping_method_code := l_org_trans_rel_cur.ship_method;
459 lv_action_rec.request_type := OE_GLOBALS.G_BOOK_ORDER;
460 lv_action_rec.entity_code := OE_GLOBALS.G_ENTITY_HEADER;
461
462 If (l_org_trans_rel_cur.plan_type = 8) Then
463 l_oe_line_rec.subinventory := lv_Dest_subinv;
464 END IF;
465 -- Get Customer ID
466 log_message('Source organization id => ' || l_org_trans_rel_cur.src_organization_id);
467 po_customers_sv.get_cust_details(l_org_trans_rel_cur.deliver_to_location_id,
468 l_customer_id,
469 l_address_id,
470 l_site_use_id,
471 l_org_trans_rel_cur.src_organization_id);
472
473 IF l_customer_id is not null THEN
474 l_oe_header_rec.sold_to_org_id := l_customer_id;
475 log_message('Customer ID : ' || l_customer_id);
476 ELSE
477 log_message('ERROR: OE Header Rec:Sold_To_Org_id(Customer_ID)is null for Deliver to location id:'
478 || l_org_trans_rel_cur.deliver_to_location_id);
479 l_output_error := 'Error in getting Customer ID for Deliver to location id.';
480 raise e_creating_iso_err;
481 END IF;
482
483
484 OPEN c_bill_to_location_id(l_customer_id);
485 FETCH c_bill_to_location_id INTO l_invoice_to_org_id;
486 CLOSE c_bill_to_location_id;
487
488 IF l_invoice_to_org_id IS NOT NULL THEN
489 l_oe_header_rec.invoice_to_org_id := l_invoice_to_org_id;
490 END IF;
491
492
493 OPEN c_ship_to_location_id(l_customer_id, l_site_use_id);
494 FETCH c_ship_to_location_id INTO l_ship_to_org_id;
495 CLOSE c_ship_to_location_id;
496
497 IF l_ship_to_org_id IS NOT NULL THEN
498 l_oe_header_rec.ship_to_org_id := l_ship_to_org_id;
499 END IF;
500
501 l_oe_line_tbl(j) := l_oe_line_rec;
505 -- IN variables
502 lv_action_req_Tbl(j) := lv_action_rec;
503 --Add Process_order
504 OE_Order_PUB.Process_Order(
506 p_api_version_number => 1.0,
507 p_header_rec => l_oe_header_rec,
508 p_line_tbl => l_oe_line_tbl,
509 p_action_request_tbl => lv_action_req_Tbl,
510 p_org_id => l_src_org_id,
511 -- OUT variables
512 x_header_rec => l_header_rec,
513 x_header_val_rec => l_header_val_rec,
514 x_Header_Adj_tbl => l_Header_Adj_tbl,
515 x_Header_Adj_val_tbl => l_Header_Adj_val_tbl,
516 x_Header_price_Att_tbl => l_Header_price_Att_tbl,
517 x_Header_Adj_Att_tbl => l_Header_Adj_Att_tbl,
518 x_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl,
519 x_Header_Scredit_tbl => l_Header_Scredit_tbl,
520 x_Header_Scredit_val_tbl => l_Header_Scredit_val_tbl,
521 x_line_tbl => l_Line_Tbl,
522 x_line_val_tbl => l_line_val_tbl,
523 x_Line_Adj_tbl => l_Line_Adj_tbl,
524 x_Line_Adj_val_tbl => l_Line_Adj_val_tbl,
525 x_Line_price_Att_tbl => l_Line_price_Att_tbl,
526 x_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl,
527 x_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl,
528 x_Line_Scredit_tbl => l_Line_Scredit_tbl,
529 x_Line_Scredit_val_tbl => l_Line_Scredit_val_tbl,
530 x_Lot_Serial_tbl => l_Lot_Serial_tbl,
531 x_Lot_Serial_val_tbl => l_Lot_Serial_val_tbl,
532 x_action_request_tbl => l_action_request_tbl,
533 x_return_status => l_return_status,
534 x_msg_count => l_msg_count,
535 x_msg_data => l_msg_data);
536
537 if (l_msg_count > 0) then
538 for lv_index in 1..l_msg_count loop
539 l_msg_data := OE_MSG_PUB.get(p_msg_index => lv_index, p_encoded => 'F');
540 log_message('Error :'||lv_index|| ' '|| l_msg_data);
541 end loop;
542 end if;
543
544
545 if (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
546 p_ISO_header_id:= l_header_rec.header_id ;
547 log_message('Successful in loading Sales order.');
548 log_message('Header ID : ' ||l_header_rec.header_id);
549 log_message('Internal Sales Order Number : ' ||
550 l_header_rec.order_number);
551 log_message('Inventory Item ID :' ||
552 l_line_tbl(1).inventory_item_id);
553 log_message('UOM :' ||
554 l_line_tbl(1).order_quantity_uom);
555 log_message('Item Identifier Type :' ||
556 l_line_tbl(1).item_identifier_type);
560 l_line_tbl(1).ship_tolerance_above);
557 log_message('Line ID :' ||
558 l_line_tbl(1).line_id);
559 log_message('Ship above : '||
561 log_message('Ordered quantity : '||
562 l_line_tbl(1).ordered_quantity);
563 log_message('Request Date : ' ||
564 to_char(l_line_tbl(1).request_date,
565 'DD-MON-YYYY HH24:MI:SS'));
566 log_message('Schdeule Ship Date :' ||
567 to_char(l_line_tbl(1).schedule_Ship_date,
568 'DD-MON-YYYY HH24:MI:SS'));
569 log_message('Schdeule Arrival Date :' ||
570 to_char(l_line_tbl(1).schedule_arrival_date,
571 'DD-MON-YYYY HH24:MI:SS'));
572 else
573 log_message('Error in loading sales order.');
574 l_output_error := ' Error while creating Internal Sales Order.';
575 RAISE e_creating_iso_err;
576 end if;
577
578 l_sch_rec.header_id := l_header_rec.header_id;
579 l_sch_rec.line_id := l_line_tbl(1).line_id;
580
581 ELSE /* For Re-Schedule ISO */
582 v_load_type := 'Reschedule';
583 l_oe_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
584
585 OPEN c_header_id(l_org_trans_rel_cur.sales_order_number);
586 FETCH c_header_id INTO l_header_id;
587 CLOSE c_header_id;
588
589 IF nvl(l_header_id,0) = 0 THEN
590 log_message('Error: Can not find Header_ID from given Order Number: ' || l_org_trans_rel_cur.sales_order_number);
591 l_output_error := 'Error while finding Header_ID from given Order Number.';
592 raise e_creating_iso_err;
593 END IF;
594
595 l_oe_header_rec.header_id := l_header_id;
596 l_oe_header_rec.order_number := l_org_trans_rel_cur.sales_order_number;
597
598 l_oe_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
599 l_oe_line_rec.line_id := l_org_trans_rel_cur.sales_order_line_id;
600 l_oe_line_rec.ordered_quantity := l_org_trans_rel_cur.quantity;
601 l_oe_line_rec.schedule_ship_date := l_org_trans_rel_cur.ship_date;
602 l_oe_line_rec.change_reason := 'SYSTEM';
603 l_oe_line_rec.change_comments := 'Updating';
604 l_oe_line_rec.Earliest_ship_date := l_org_trans_rel_cur.Earliest_ship_date; -- Bug# 4306515
605
606 l_oe_line_tbl(j) := l_oe_line_rec;
607
608 /* Pass Null action req table in case of rescheduling */
609 lv_action_req_Tbl.delete;
610 l_sch_rec.header_id := l_header_id;
611 l_sch_rec.line_id := l_org_trans_rel_cur.sales_order_line_id;
612 l_oe_header_rec.shipping_method_code := l_org_trans_rel_cur.ship_method;
613
614 END IF;
615 --IRISO Enhancement - No need to call process_order API to reschedule
616 /*
617
618 */
619 -- IRISO Enhancement, Call only Update_Scheduling_Results for rescheduling.
620 /* Call Loop Back API to re-schedule ship date and arrival date */
621
622 l_sch_rec.org_id := l_org_trans_rel_cur.to_operating_unit; --l_oe_header_rec.org_id;
623 l_sch_rec.Schedule_ship_date := l_org_trans_rel_cur.ship_date;
624 l_sch_rec.Schedule_arrival_date := l_org_trans_rel_cur.need_by_date;
625 l_sch_rec.shipping_method_code := l_org_trans_rel_cur.ship_method;
626 l_sch_rec.ordered_quantity := l_org_trans_rel_cur.quantity; -- IRISO
627
628
629 l_sch_rec_tbl(1) := l_sch_rec;
630
631 OE_SCHEDULE_GRP.Update_Scheduling_Results(p_x_sch_tbl => l_sch_rec_tbl,
632 p_request_id => l_request_id,
633 x_return_status => l_return_status);
634
635 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
636 log_message('Successful in update_scheduling_results.');
637 log_message('Header ID : ' || l_sch_rec_tbl(1).header_id );
638 log_message('Line ID : ' || l_sch_rec_tbl(1).line_id );
639 log_message('Org ID : ' || l_sch_rec_tbl(1).org_id );
640 log_message('Schedule Ship Date : ' || to_char(l_sch_rec_tbl(1).Schedule_ship_date,'DD-MON-YYYY HH24:MI:SS'));
641 log_message('Schedule Arrival Date : '|| to_char(l_sch_rec_tbl(1).Schedule_arrival_date, 'DD-MON-YYYY HH24:MI:SS'));
642 log_message(' ');
643 log_message(' ');
644 log_message('---------------------------------------------------------------------');
645
646 IF (l_org_trans_rel_cur.load_type = DRP_REQ_LOAD) THEN
647 log_output(rpad(l_org_trans_rel_cur.sales_order_number,14,' ')||
648 rpad(v_load_type,14,' ')||
649 rpad(to_char(l_header_rec.order_number),11,' ')||
650 rpad(l_oe_line_rec.ordered_quantity,11,' ')||
651 rpad(to_char(l_sch_rec_tbl(1).Schedule_ship_date,'DD-MON-YYYY HH24:MI:SS'),25,' ')||
655 );
652 rpad(to_char(l_sch_rec_tbl(1).Schedule_arrival_date, 'DD-MON-YYYY HH24:MI:SS'),25,' ')||
653 rpad(l_req_header_rec.segment1,11,' ')||
654 rpad(to_char(l_req_line_tbl(j).need_by_date, 'DD-MON-YYYY HH24:MI:SS'),25,' ')
656 ELSE
657 log_output(rpad(l_org_trans_rel_cur.sales_order_number,14,' ')||
658 rpad(v_load_type,14,' ')||
659 rpad(to_char(l_header_rec.order_number),11,' ')||
660 rpad(l_oe_line_rec.ordered_quantity,11,' ')||
661 rpad(to_char(l_sch_rec_tbl(1).Schedule_ship_date,'DD-MON-YYYY HH24:MI:SS'),25,' ')||
662 rpad(to_char(l_sch_rec_tbl(1).Schedule_arrival_date, 'DD-MON-YYYY HH24:MI:SS'),25,' ')
663 );
664 END IF;
665
666 v_success := v_success +1;
667
668 ELSE
669
670 log_message('Error in update_scheduling_results: ' || l_return_status);
671 log_message('Header ID : ' || l_sch_rec_tbl(1).header_id );
672 log_message('Line ID : ' || l_sch_rec_tbl(1).line_id );
673 log_message('Org ID : ' || l_sch_rec_tbl(1).org_id );
674 log_message('Schedule Ship Date : ' || to_char(l_sch_rec_tbl(1).Schedule_ship_date,'DD-MON-YYYY HH24:MI:SS'));
675 log_message('Schedule Arrival Date : '|| to_char(l_sch_rec_tbl(1).Schedule_arrival_date, 'DD-MON-YYYY HH24:MI:SS'));
676
677 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
678 log_message('FND_API.G_RET_STS_ERROR ');
679 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
680 log_message('FND_API.G_RET_STS_UNEXP_ERROR ');
681 END IF;
682 RAISE e_update_rescheduling_err;
683 END IF;
684
685
686 END IF; /* End of l_int_req_ret_sts = FND_API.G_RET_STS_SUCCESS */
687
688
689
690 /* Set the req line and req table to null so that
691 they are re-initialized after each loop.
692 This prevent to have unique violation on requisition line id */
693 l_req_header_rec := null;
694 l_req_line_tbl(1) := null;
695 l_sch_rec := NULL;
696 l_sch_rec_tbl.delete;
697 l_msg_count := 0;
698 l_msg_data:= '';
699 lv_action_req_tbl.delete;
700
701
702
703
704 EXCEPTION
705 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
706 LOG_MESSAGE('Error in CREATE_AND_SCHEDULE_ISO : FND_API.G_EXC_UNEXPECTED_ERROR');
707 LOG_MESSAGE(SQLERRM);
708 retcode := 1;
709 b_has_error := TRUE;
710 v_failure := v_failure + 1;
711 log_output(rpad(l_org_trans_rel_cur.sales_order_number,14,' ')||rpad(v_load_type,14,' ')||' - Unexpected Error while creating requisition.');
712 rollback to Savepoint Before_Requisition ;
713 log_message('Transaction rolled back');
714 log_message('----------------------------------------------------------');
715
716 WHEN FND_API.G_EXC_ERROR THEN
717 LOG_MESSAGE('Error in CREATE_AND_SCHEDULE_ISO : FND_API.G_EXC_ERROR');
718 LOG_MESSAGE(SQLERRM);
719 retcode := 1;
720 b_has_error := TRUE;
721 v_failure := v_failure + 1;
722 log_output(rpad(l_org_trans_rel_cur.sales_order_number,14,' ')||rpad(v_load_type,14,' ')||' - Error while creating requisition.');
723 rollback to Savepoint Before_Requisition ;
724 log_message('Transaction rolled back');
725 log_message('----------------------------------------------------------');
726
727 WHEN NO_DATA_FOUND THEN
728 LOG_MESSAGE('Error in CREATE_AND_SCHEDULE_ISO : Err No Data Found');
729 LOG_MESSAGE(SQLERRM);
730 retcode := 1;
731 b_has_error := TRUE;
732 v_failure := v_failure + 1;
733 log_output(rpad(l_org_trans_rel_cur.sales_order_number,14,' ')||rpad(v_load_type,14,' ')||' - Unexpected NO_DATA_FOUND Error');
734 rollback to Savepoint Before_Requisition ;
735 log_message('Transaction rolled back');
736 log_message('----------------------------------------------------------');
737
738 WHEN e_creating_iso_err THEN
739 LOG_MESSAGE('Error in CREATE_AND_SCHEDULE_ISO : e_creating_iso_err');
740 LOG_MESSAGE(SQLERRM);
741 retcode := 1;
742 b_has_error := TRUE;
743 v_failure := v_failure + 1;
744 log_output(rpad(l_org_trans_rel_cur.sales_order_number,14,' ')||rpad(v_load_type,14,' ')||l_output_error);
745 rollback to Savepoint Before_Requisition ;
746 log_message('Transaction rolled back');
747 log_message('----------------------------------------------------------');
748
749 WHEN e_update_rescheduling_err THEN
750 LOG_MESSAGE('Error in UPDATE_RESCHEUDLING_RESULTS : e_update_rescheduling_err');
751 LOG_MESSAGE(SQLERRM);
752 retcode := 1;
753 b_has_error := TRUE;
754 v_failure := v_failure + 1;
755 log_output(rpad(l_org_trans_rel_cur.sales_order_number,14,' ')||rpad(v_load_type,14,' ')||' - Error while updating scheduling results.');
756 rollback to Savepoint Before_Requisition ;
757 log_message('Transaction rolled back');
758 log_message('----------------------------------------------------------');
759
760 WHEN OTHERS THEN
761 LOG_MESSAGE('Error in CREATE_AND_SCHEDULE_ISO : Err OTHERS');
762 LOG_MESSAGE(SQLERRM);
763 retcode := 1;
764 b_has_error := TRUE;
765 v_failure := v_failure + 1;
766 log_output(rpad(l_org_trans_rel_cur.sales_order_number,14,' ')||rpad(v_load_type,14,' ')||' - Unexpected Error');
767 rollback to Savepoint Before_Requisition ;
768 log_message('Transaction rolled back');
769 log_message('----------------------------------------------------------');
770
771 END;
772 log_output('');
773
774 log_output(' ');
775 log_output(v_success||' out of '||(v_success + v_failure)||' Internal Req/ISO released successfully. ');
776
777 log_message('p_ISO_header_id returned:'||p_ISO_header_id);
778 log_message('p_Ireq_header_id returned:'||p_Ireq_header_id);
779
780
781 Close c_trans_rel;
782
783
784 --Handle Exceptions
785 EXCEPTION
786
787 WHEN NO_DATA_FOUND THEN
788 LOG_MESSAGE('Error in CREATE_AND_SCHEDULE_ISO : Err No Data Found');
789 LOG_MESSAGE(SQLERRM);
790 retcode := 1;
791
792 WHEN OTHERS THEN
793 LOG_MESSAGE('Error in CREATE_AND_SCHEDULE_ISO : Err OTHERS');
794 LOG_MESSAGE(SQLERRM);
795 retcode := 1;
796
797 END CREATE_IR_ISO;
798 /**********************************************************
799 Procedure: CREATE_AND_SCHEDULE_ISO
800 ***********************************************************/
801 PROCEDURE CREATE_AND_SCHEDULE_ISO(
802 errbuf OUT NOCOPY VARCHAR2,
803 retcode OUT NOCOPY VARCHAR2,
804 p_batch_id IN number) IS
805
806 -- Cursors
807 CURSOR c_org_trans_rel(l_batch_id number) IS
808 SELECT
809 batch_id, transaction_id
810 FROM
811 MRP_ORG_TRANSFER_RELEASE
812 WHERE
813 batch_id = l_batch_id
814 ORDER BY src_operating_unit;
815
816 l_req_header_id number ;
817 l_iso_header_id number ;
818 l_user_id NUMBER;
819 l_appl_id NUMBER;
820
821
822 BEGIN
823
824
825 l_user_id := fnd_global.user_id();
826 l_appl_id := 724; -- Application id for Advanced Supply Chain Planning
827 retcode := 0;
828
829 log_output(' Internal Requisition/ISO Release and Reschedule Report');
830 log_output(' ------------------------------------------------------');
831 log_output('');
832 log_output('Order No Load Type ISO No Quantity Schedule Shipment Date Schedule Arrival Date Internal Need By Date');
833 log_output('in PWB Req No. ');
834 log_output('----------- ----------- -------- -------- ---------------------- ---------------------- -------- ----------------------');
835
836
837 FOR l_org_trans_rel_cur IN c_org_trans_rel(p_batch_id) LOOP
838 Create_IR_ISO(errbuf,retcode ,l_req_header_id,l_iso_header_id, l_org_trans_rel_cur.transaction_id , l_org_trans_rel_cur.batch_id );
839
840 log_message('p_ISO_header_id returned:'||l_iso_header_id);
841 log_message('p_Ireq_header_id returned:'||l_req_header_id);
842 commit;
843
844 END LOOP;
845
846 DELETE from MRP_ORG_TRANSFER_RELEASE
847 WHERE batch_id = p_batch_id;
848 COMMIT;
849
850
851 EXCEPTION
852
853 WHEN NO_DATA_FOUND THEN
854 LOG_MESSAGE('Error in CREATE_AND_SCHEDULE_ISO : Err No Data Found');
855 LOG_MESSAGE(SQLERRM);
856 retcode := 1;
857
858 WHEN OTHERS THEN
859 LOG_MESSAGE('Error in CREATE_AND_SCHEDULE_ISO : Err OTHERS');
860 LOG_MESSAGE(SQLERRM);
861 retcode := 1;
862
863 END CREATE_AND_SCHEDULE_ISO;
864
865
866 END MRP_CREATE_SCHEDULE_ISO;