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