[Home] [Help]
PACKAGE BODY: APPS.CSD_LOGISTICS_PVT
Source
1 PACKAGE BODY CSD_LOGISTICS_PVT as
2 /* $Header: csdvlogb.pls 120.14 2012/03/27 23:29:59 swai ship $ */
3 -- Start of Comments
4 -- Package name : CSD_LOGISTICS_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSD_LOGISTICS_PVT';
11 G_FILE_NAME CONSTANT VARCHAR2(15) := 'csdvlogb.pls';
12
13 /*----------------------------------------------------------------*/
14
15 /*----------------------------------------------------------------*/
16
17 procedure debug(p_msg VARCHAR2) is
18 begin
19 --dbms_output.put_line(p_msg);
20 null;
21 end;
22
23
24 PROCEDURE Create_Logistics_Line(
25 p_api_version IN NUMBER,
26 p_commit IN VARCHAR2 := fnd_api.g_false,
27 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
28 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
29 x_return_status OUT NOCOPY VARCHAR2,
30 x_msg_count OUT NOCOPY NUMBER,
31 x_msg_data OUT NOCOPY VARCHAR2,
32 p_product_txn_rec IN OUT NOCOPY csd_process_pvt.product_txn_rec,
33 p_add_to_order_flag IN VARCHAR2
34 )
35 IS
36
37 Begin
38
39 --need to add more validation here
40 csd_mass_rcv_pvt.create_product_txn (
41 p_api_version => p_api_version,
42 p_commit => p_commit,
43 p_init_msg_list => p_init_msg_list,
44 p_validation_level => p_validation_level,
45 x_return_status => x_return_status,
46 x_msg_count => x_msg_count,
47 x_msg_data => x_msg_data,
48 p_product_txn_rec => p_product_txn_rec,
49 p_add_to_order_flag => p_add_to_order_flag
50 );
51
52 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
53 RAISE FND_API.G_EXC_ERROR;
54 END IF;
55
56
57 End Create_Logistics_Line;
58
59
60 PROCEDURE Create_Default_Logistics
61 ( p_api_version IN NUMBER,
62 p_commit IN VARCHAR2 := fnd_api.g_false,
63 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
64 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
65 p_repair_line_id IN NUMBER,
66 x_return_status OUT NOCOPY VARCHAR2,
67 x_msg_count OUT NOCOPY NUMBER,
68 x_msg_data OUT NOCOPY VARCHAR2
69 )
70
71 IS
72
73 Begin
74
75 --need to add more validation here
76 Csd_Process_Pvt.create_default_prod_txn
77 (p_api_version => p_api_version,
78 p_commit => p_commit,
79 p_init_msg_list => p_init_msg_list,
80 p_validation_level => p_validation_level,
81 p_repair_line_id => p_repair_line_id,
82 x_return_status => x_return_status,
83 x_msg_count => x_msg_count,
84 x_msg_data => x_msg_data);
85
86 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
87 RAISE FND_API.G_EXC_ERROR;
88 END IF;
89
90
91 End Create_Default_Logistics;
92
93
94 /********************** ************* ****************************************/
95 /*---------------------------------------------------------------------------*/
96 /* procedure name: UUpdate_Logistics_Line */
97 /* description */
98 /* : This is a private API, whose main functionality is to update product*/
99 /* transaction line, interface with OM and progress product trxn line */
100 /* status through BOOKED status. Once all validations are done it will */
101 /* private API Update_PRoduct_Txn which will continue to do some more */
102 /* more validations before actually perfoming user intended action. */
103 /* Note: */
104 /* Contract_Line_Id from CSD_Product_Txns_v is assigned to contract_id */
105 /* in l_Product_Txn_Rec */
106 /* Parameters */
107 /* p_api_version IN Standard API paramater */
108 /* p_commit IN Standard API paramater */
109 /* p_init_msg_list IN Standard API paramater */
110 /* x_return_status OUT Standard API paramater */
111 /* x_msg_count OUT Standard API paramater */
112 /* x_msg_data OUT Standard API paramater */
113 /* p_UpdateProductTrxn_Rec IN user input values are stored in this record */
114 /* X_Product_Txn_Rec OUT database values are stored in this record */
115 /*---------------------------------------------------------------------------*/
116 procedure Update_Logistics_Line(p_api_version IN NUMBER,
117 p_commit IN VARCHAR2,
118 p_init_msg_list IN VARCHAR2,
119 p_validation_level IN NUMBER,
120 x_return_status OUT NOCOPY VARCHAR2,
121 x_msg_count OUT NOCOPY NUMBER,
122 x_msg_data OUT NOCOPY VARCHAR2,
123 p_Upd_ProdTxn_Rec IN CSD_LOGISTICS_PUB.Upd_ProdTxn_Rec_Type,
124 x_object_version_number OUT NOCOPY NUMBER,
125 x_order_header_id OUT NOCOPY NUMBER,
126 x_order_line_id OUT NOCOPY NUMBER ) IS
127
128 -- Define local constants
129 C_API_NAME CONSTANT VARCHAR2(30) := 'Update_Logistics_Line';
130 C_API_VERSION CONSTANT NUMBER := 1.0;
131 C_RO_STATUS_OPEN CONSTANT VARCHAR2(1) := 'O';
132 C_YES CONSTANT VARCHAR2(1) := 'Y';
133 C_NO CONSTANT VARCHAR2(1) := 'N';
134
135 C_PROD_TXN_STS_ENTERED CONSTANT VARCHAR2(30) := 'ENTERED';
136 C_PROD_TXN_STS_BOOKED CONSTANT VARCHAR2(30) := 'BOOKED';
137 C_PROD_TXN_STS_RELEASED CONSTANT VARCHAR2(30) := 'RELEASED';
138 C_PROD_TXN_STS_SHIPPED CONSTANT VARCHAR2(30) := 'SHIPPED';
139 C_PROD_TXN_STS_RECEIVED CONSTANT VARCHAR2(30) := 'RECEIVED';
140 C_PROD_TXN_STS_SUBMITTED CONSTANT VARCHAR2(30) := 'SUBMITTED';
141
142 -- Define Local Variables
143 l_RO_Status_Meaning VARCHAR2(80);
144 l_RO_NUMBER VARCHAR2(30);
145 l_Order_Header_ID Number;
146 l_ro_Status CSD_REPAIRS.Status%type;
147
148 -- Define a record of type
149 l_Product_Txn_Rec CSD_PROCESS_PVT.Product_Txn_Rec;
150
151 -- Define a cursor that gets current values for product_Txn_V for a given
152 -- product_Transaction_id
153 -- RMA is qty is stored as negative in charges but in depot it is captured as
154 -- positive value, so while retrieving from charges, should use ABS function.
155 -- bug#8589873, FP of bug#8579443. subhat.
156 -- select the existing value for picking_rule_id before calling update.
157 CURSOR ProdTxn_Cur_Type IS
158 SELECT A.Product_Transaction_Id,
159 A.Action_Type,
160 A.Action_Code,
161 A.Inventory_Item_Id,
162 A.Txn_Billing_Type_Id,
163 A.Price_List_Header_ID,
164 ABS(A.Estimate_Quantity),
165 A.Revision,
166 A.source_Serial_Number,
167 A.non_source_Serial_Number,
168 A.Lot_Number,
169 A.Sub_Inventory,
170 A.Return_Reason_Code,
171 A.Return_By_Date,
172 A.PO_Number,
173 A.Invoice_To_Org_Id,
174 A.Ship_To_Org_Id,
175 A.Unit_Of_Measure,
176 A.Charge,
177 A.No_Charge_Flag,
178 A.New_Order_Flag,
179 A.Object_Version_Number,
180 A.Transaction_Status,
181 A.Interface_To_OM_Flag,
182 A.Book_Sales_Order_flag,
183 A.Status,
184 A.Release_Sales_Order_Flag,
185 A.Ship_Sales_Order_Flag,
186 A.Order_Header_Id,
187 A.Order_Line_Id,
188 A.Repair_Line_Id,
189 A.Order_Number,
190 A.Estimate_Detail_Id,
191 A.Contract_Line_Id,
192 A.Business_Process_id,
193 B.incident_id,
194 A.Reference_Number,
195 A.picking_rule_id,
196 A.inventory_org_id -- bug#13114174
197 FROM CSD_Product_Txns_v A, CSD_REPAIRS B
198 WHERE A.REPAIR_LINE_ID = B.REPAIR_LINE_ID
199 AND Product_Transaction_Id =
200 p_Upd_ProdTxn_Rec.Product_Transaction_Id;
201
202 -- Define a cursor that gets Repair NUMBER AND Repair Status meaning for a given
203 -- repair line id
204 CURSOR RepairStatus_Cur_Type(p_Repair_Line_Id IN NUMBER) IS
205 SELECT dra.Repair_NUMBER, fndl2.meaning Status_Meaning
206 FROM Csd_Repairs dra, fnd_lookups fndl2
207 WHERE dra.status = fndl2.lookup_code
208 and fndl2.lookup_type = 'CSD_REPAIR_STATUS'
209 and dra.repair_line_id = p_Repair_line_id;
210
211 -- Define a cursor that gets currency code for a given Price List Id
212 -- VP:SU:03/01
213 Cursor PL_CurrencyCode_Cur_Type(p_Price_List_Id Number) Is
214 SELECT currency_code
215 FROM qp_list_headers_b
216 WHERE list_header_id = p_price_list_id;
217
218 BEGIN
219 -- Procedure Body
220
221 debug('Entered update_logistics_line pvt');
222
223 -- Create a Save Point before calling Update program
224 SAVEPOINT Update_Logistics_Line_Pvt;
225
226 -- Initialize API return status to success
227 x_return_status := FND_API.G_RET_STS_SUCCESS;
228
229 -- Standard call to check for API Version compatibility.
230 IF Not (FND_API.Compatible_API_Call(c_api_version,
231 p_api_version,
232 c_api_name,
233 G_PKG_NAME))
234 THEN
235 RAISE FND_API.G_Exc_UnExpected_Error;
236 END IF;
237
238 -- Initialize message list if p_init_msg_list is set to TRUE.
239 IF FND_API.to_Boolean(p_init_msg_list)
240 THEN
241 FND_MSG_PUB.initialize;
242 END IF;
243
244 debug('fetching the current record..Product_Transaction_Id['||p_Upd_ProdTxn_Rec.Product_Transaction_Id||']');
245 -- bug#8589873, FP of bug#8579443. subhat.
246 -- select the existing value for picking_rule_id before calling update.
247 OPEN ProdTxn_Cur_Type;
248 FETCH ProdTxn_Cur_Type
249 INTO l_Product_Txn_Rec.Product_Transaction_Id,
250 l_Product_Txn_Rec.Action_Type,
251 l_Product_Txn_Rec.Action_Code,
252 l_Product_Txn_Rec.Inventory_Item_Id,
253 l_Product_Txn_Rec.Txn_Billing_Type_Id,
254 l_Product_Txn_Rec.price_list_id,
255 l_Product_Txn_Rec.Quantity,
256 l_Product_Txn_Rec.Revision,
257 l_Product_Txn_Rec.source_Serial_Number,
258 l_Product_Txn_Rec.non_source_Serial_Number,
259 l_Product_Txn_Rec.Lot_Number,
260 l_Product_Txn_Rec.Sub_Inventory,
261 l_Product_Txn_Rec.return_reason,
262 l_Product_Txn_Rec.Return_By_Date,
263 l_Product_Txn_Rec.PO_Number,
264 l_Product_Txn_Rec.Invoice_To_Org_Id,
265 l_Product_Txn_Rec.Ship_To_Org_Id,
266 l_Product_Txn_Rec.unit_of_measure_code,
267 l_Product_Txn_Rec.after_warranty_cost,
268 l_Product_Txn_Rec.No_Charge_Flag,
269 l_Product_Txn_Rec.New_Order_Flag,
270 l_Product_Txn_Rec.Object_Version_Number,
271 l_Product_Txn_Rec.Prod_Txn_Status,
272 l_Product_Txn_Rec.Interface_To_OM_Flag,
273 l_Product_Txn_Rec.Book_Sales_Order_flag,
274 l_ro_status,
275 l_Product_Txn_Rec.Release_Sales_Order_Flag,
276 l_Product_Txn_Rec.Ship_Sales_Order_Flag,
277 l_Product_Txn_Rec.Order_Header_Id,
278 l_Product_Txn_Rec.Order_Line_Id,
279 l_Product_Txn_Rec.Repair_Line_Id,
280 l_Product_Txn_Rec.Order_Number,
281 l_Product_Txn_Rec.Estimate_Detail_Id,
282 l_Product_Txn_Rec.Contract_Id,
283 l_Product_Txn_Rec.Business_Process_id,
284 l_Product_Txn_Rec.incident_id,
285 l_Product_Txn_Rec.source_instance_number,
286 l_Product_Txn_Rec.picking_rule_id,
287 l_Product_Txn_Rec.inventory_org_id; -- bug#13114174
288
289 CLOSE ProdTxn_Cur_Type;
290 debug('object version number fetched ['||l_Product_Txn_Rec.Object_Version_NUMBER||']');
291
292 --bug#9503748
293 l_Product_Txn_Rec.organization_id := csd_process_util.get_org_id(l_Product_Txn_Rec.incident_id);
294 --bug#9503748
295
296 -- Validate Product_Transaction_Id is null
297 CSD_PROCESS_UTIL.Check_Reqd_Param(p_param_value => p_Upd_ProdTxn_Rec.Product_Transaction_id,
298 p_param_name => 'Product Transaction ID',
299 p_api_name => C_API_Name);
300
301 debug('Validating the prod txn id');
302 -- Validate the Product_Transaction_Id exists in csd_product_transactions
303 IF NOT
304 (CSD_PROCESS_UTIL.Validate_prod_txn_id(p_prod_txn_id => p_Upd_ProdTxn_Rec.Product_Transaction_id))
305 THEN
306 RAISE FND_API.G_EXC_ERROR;
307 END IF;
308
309 debug('ro status check...');
310 -- Check Repair Order Status if it Hold or Closed then raise exception
311 IF l_ro_Status <> C_RO_Status_OPEN
312 THEN
313
314 OPEN RepairStatus_Cur_Type(l_Product_Txn_Rec.Repair_Line_Id);
315 FETCH RepairStatus_Cur_Type
316 INTO l_RO_NUMBER, l_RO_Status_Meaning;
317 CLOSE RepairStatus_Cur_Type;
318 -- RAISE Error message
319 FND_MESSAGE.SET_NAME('CSD', 'CSD_RO_NOT_OPEN_NO_PRODTXN_UPD');
320 FND_MESSAGE.SET_TOKEN('RO_NUMBER', l_RO_NUMBER);
321 FND_MESSAGE.SET_TOKEN('RO_STATUS', l_RO_Status_Meaning);
322 FND_MSG_PUB.ADD;
323 RAISE FND_API.G_EXC_ERROR;
324
325 END IF;
326
327 debug('object version number check, db['||l_Product_Txn_Rec.Object_Version_NUMBER||']');
328 debug('input ['||p_Upd_ProdTxn_Rec.Object_Version_Number||']');
329 -- Check Object Version NUMBER. Object Version NUMBER FROM parameter should match the value in Database.
330 IF l_Product_Txn_Rec.Object_Version_NUMBER <>
331 p_Upd_ProdTxn_Rec.Object_Version_Number
332 THEN
333
334 FND_MESSAGE.SET_NAME('CSD', 'CSD_OBJECT_VERSION_NUMBER');
335 FND_MSG_PUB.ADD;
336 RAISE FND_API.G_EXC_ERROR;
337
338 END IF;
339
340 -- Set flag attributes values for record UpdateProdTrxn_Rec
341 Csd_Logistics_Util.Set_ProductTrxnRec_Flags(p_Upd_ProdTxn_Rec => p_Upd_ProdTxn_Rec,
342 x_Product_Txn_Rec => l_Product_Txn_Rec);
343
344 -- Some of the attributes that are passed as input parameters to the API can not
345 -- be updated by user, so make sure that those attributes are not updated
346
347 IF l_Product_Txn_Rec.Prod_Txn_Status <> C_PROD_TXN_STS_ENTERED
348 THEN
349
350 -- Once product transaction line is interfaced then user can make changes
351 -- to some attributes. List of these are listed in following API
352 Csd_Logistics_Util.Compare_ProductTrxnRec(p_Upd_ProdTxn_Rec => p_Upd_ProdTxn_Rec,
353 p_Product_Txn_Rec => l_Product_Txn_Rec,
354 x_Return_Status => x_Return_Status,
355 x_Msg_Data => x_Msg_Data,
356 x_Msg_Count => x_Msg_Count);
357
358 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS
359 THEN
360 RAISE FND_API.G_EXC_ERROR;
361 END IF;
362
363 END IF;
364
365 -- Build Product Txn Rec,
366 Csd_Logistics_Util.Build_ProductTxnRec(p_Upd_ProdTxn_Rec => p_Upd_ProdTxn_Rec,
367 x_Product_Txn_Rec => l_Product_Txn_Rec);
368
369
370 --bug#6075825
371 /* Don't need this validation here.
372 -- Call Validate_Product_Txn_Rec API to validate each attributes values.
373 Csd_Logistics_Util.Validate_ProductTrxnRec(p_Upd_ProdTxn_Rec => p_Upd_ProdTxn_Rec,
374 p_Product_Txn_Rec => l_Product_Txn_Rec,
375 x_Return_Status => x_Return_Status,
376 x_Msg_Data => x_Msg_Data,
377 x_Msg_Count => x_Msg_Count);
378
379 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS
380 THEN
381 --dbms_output.put_line('Validation failed');
382 RAISE FND_API.G_EXC_ERROR;
383 END IF;
384 */
385 --Get Currency Code for a given Price List Id
386 Open PL_CurrencyCOde_Cur_Type(l_Product_Txn_Rec.Price_List_ID);
387 Fetch PL_CurrencyCode_Cur_Type
388 Into l_Product_Txn_Rec.Currency_Code;
389 Close PL_CurrencyCode_Cur_TYpe;
390
391 -- Call Private API to Update Product Transaction Record
392 -- CSD_LOGISTICS_PVT.Update_product_txn
393 --dbms_output.put_line('Calling update');
394 CSD_LOGISTICS_PVT.Update_product_txn(p_api_version => 1.0,
395 p_commit => fnd_api.g_false,
396 p_init_msg_list => fnd_api.g_false,
397 p_validation_level => fnd_api.g_valid_level_full,
398 x_product_txn_rec => l_Product_Txn_Rec,
399 x_return_status => x_Return_Status,
400 x_msg_count => x_Msg_Count,
401 x_msg_data => x_Msg_Data);
402
403 IF x_Return_Status = FND_API.G_RET_STS_ERROR
404 THEN
405 RAISE FND_API.G_EXC_ERROR;
406 ELSIF x_Return_Status = FND_API.G_RET_STS_UNEXP_ERROR
407 THEN
408 RAISE Fnd_Api.G_Exc_UnExpected_Error;
409 END IF;
410
411 x_object_version_number := l_Product_Txn_Rec.object_version_number;
412 x_order_header_id := l_Product_Txn_Rec.order_header_id;
413 x_order_line_id := l_Product_Txn_Rec.order_line_id;
414
415
416 EXCEPTION
417
418 WHEN Fnd_Api.G_Exc_Error THEN
419
420 x_return_status := Fnd_Api.G_Ret_Sts_Error;
421
422 ROLLBACK TO Update_Logistics_Line_pvt;
423
424 Fnd_Msg_Pub.Count_And_Get(p_count => x_msg_count,
425 p_data => x_msg_data);
426
427 WHEN Fnd_Api.G_Exc_UnExpected_Error THEN
428
429 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
430
431 ROLLBACK TO Update_Logistics_Line_pvt;
432
433 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
434 p_data => x_msg_data);
435
436 WHEN OTHERS THEN
437
438 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
439
440 ROLLBACK TO Update_Logistics_Line_pvt;
441
442 IF Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_Msg_Lvl_Unexp_Error)
443 THEN
444 Fnd_Msg_Pub.Add_Exc_Msg(G_PKG_NAME, C_api_name);
445 END IF;
446
447 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
448 p_data => x_msg_data);
449
450 END Update_Logistics_Line;
451
452
453
454 /*----------------------------------------------------------------*/
455 /* procedure name: update_product_txn */
456 /* description : procedure to update product txn lines.It is */
457 /* called from update_logistics_line API */
458 /* This is different from the process_pvt api */
459 /* in the sense that this does not commit if */
460 /* something fails (booking or release). */
461 /*----------------------------------------------------------------*/
462
463 PROCEDURE update_product_txn(p_api_version IN NUMBER,
464 p_commit IN VARCHAR2,
465 p_init_msg_list IN VARCHAR2,
466 p_validation_level IN NUMBER,
467 x_product_txn_rec IN OUT NOCOPY CSD_PROCESS_PVT.PRODUCT_TXN_REC,
468 x_return_status OUT NOCOPY VARCHAR2,
469 x_msg_count OUT NOCOPY NUMBER,
470 x_msg_data OUT NOCOPY VARCHAR2) IS
471
472 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PRODUCT_TXN';
473 l_api_version CONSTANT NUMBER := 1.0;
474 l_msg_count NUMBER;
475 l_msg_data VARCHAR2(2000);
476 l_msg_index NUMBER;
477 l_estimate_Rec cs_charge_details_pub.charges_rec_type;
478 l_prodtxn_db_attr CSD_LOGISTICS_UTIL.PRODTXN_DB_ATTR_REC;
479 l_order_Rec csd_process_pvt.om_interface_rec;
480 l_est_detail_id NUMBER;
481 l_add_to_order_id NUMBER;
482 l_add_to_order_flag VARCHAR2(1);
483 l_transaction_type_id NUMBER;
484 l_repair_line_id NUMBER;
485
486 create_order EXCEPTION;
487 book_order EXCEPTION;
488 release_order EXCEPTION;
489 ship_order EXCEPTION;
490
491
492 -- Variables used in FND Log
493 l_error_level number := FND_LOG.LEVEL_ERROR;
494 l_mod_name varchar2(2000) := 'csd.plsql.csd_logistics_pvt.update_product_txn';
495
496 l_return_Status varchar2(1);
497
498 BEGIN
499
500 SAVEPOINT UPDATE_PRODUCT_TXN_PVT;
501
502 l_prodtxn_db_attr := CSD_LOGISTICS_UTIL.get_prodtxn_db_attr(x_product_txn_rec.product_transaction_id);
503
504 -- Debug message
505 -----------------------------------------------------------------------------
506 /*********************************************************************
507 /* Code here got moved to CSD_LOGISTICS_UTIL.upd_prodtxn_n_chrgline
508 **********************************************************************/
509 -----------------------------------------------------------------------------
510 CSD_LOGISTICS_UTIL.upd_prodtxn_n_chrgline(x_product_txn_rec ,
511 l_prodtxn_db_attr,
512 l_est_detail_id,
513 l_repair_line_id,
514 l_add_to_order_flag,
515 l_add_to_order_id,
516 l_transaction_type_id);
517
518 x_product_txn_Rec.estimate_detail_id := l_est_detail_id;
519 x_product_txn_Rec.repair_line_id := l_repair_line_id;
520 x_product_txn_Rec.add_to_order_flag := l_add_to_order_flag;
521 x_product_txn_Rec.order_header_id := l_add_to_order_id;
522 x_product_txn_rec.transaction_type_id := l_transaction_type_id;
523
524
525 Debug('process_txn_flag =' ||
526 x_product_txn_rec.process_txn_flag);
527 Debug('interface_to_om_flag =' ||
528 x_product_txn_rec.interface_to_om_flag);
529 Debug('book_sales_order_flag =' ||
530 x_product_txn_rec.book_sales_order_flag);
531 Debug('release_sales_order_flag =' ||
532 x_product_txn_rec.release_sales_order_flag);
533 Debug('ship_sales_order_flag =' ||
534 x_product_txn_rec.ship_sales_order_flag);
535
536
537 IF x_product_txn_rec.process_txn_flag = 'Y'
538 THEN
539
540
541 l_order_Rec := CSD_LOGISTICS_UTIL.get_order_rec(x_product_txn_rec.repair_line_id);
542
543 CSD_LOGISTICS_UTIL.interface_prodtxn( x_return_status => l_return_status,
544 p_product_txn_rec => x_product_txn_rec,
545 p_prodtxn_db_attr => l_prodtxn_db_attr,
546 px_order_rec => l_order_rec);
547 if NOT (l_Return_status = FND_API.G_RET_STS_SUCCESS) THEN
548 raise create_order;
549 END IF;
550
551 IF l_prodtxn_db_attr.curr_book_order_flag <>
552 x_product_txn_rec.book_sales_order_flag AND
553 x_product_txn_rec.book_sales_order_flag = 'Y'
554 THEN
555 CSD_LOGISTICS_UTIL.book_prodtxn( x_return_status => l_return_status,
556 p_product_txn_rec => x_product_txn_rec,
557 p_prodtxn_db_attr => l_prodtxn_db_attr,
558 px_order_rec => l_order_rec);
559 if NOT (l_Return_status = FND_API.G_RET_STS_SUCCESS) THEN
560 raise book_order;
561 END IF;
562
563
564 END IF; -- end of book order
565
566 IF l_prodtxn_db_attr.curr_release_order_flag <>
567 x_product_txn_rec.release_sales_order_flag AND
568 x_product_txn_rec.release_sales_order_flag = 'Y'
569 THEN
570 CSD_LOGISTICS_UTIL.pickrelease_prodtxn( x_return_status => l_return_status,
571 p_product_txn_rec => x_product_txn_rec,
572 p_prodtxn_db_attr => l_prodtxn_db_attr,
573 px_order_rec => l_order_rec);
574 if(l_Return_status = FND_API.G_RET_STS_SUCCESS) THEN
575 raise release_order;
576 END IF;
577 END IF; --end of pick-release sales order
578
579 IF l_prodtxn_db_attr.curr_ship_order_flag <>
580 x_product_txn_rec.ship_sales_order_flag AND
581 x_product_txn_rec.ship_sales_order_flag = 'Y'
582 THEN
583 CSD_LOGISTICS_UTIL.ship_prodtxn( x_return_status => l_return_status,
584 p_product_txn_rec => x_product_txn_rec,
585 p_prodtxn_db_attr => l_prodtxn_db_attr,
586 px_order_rec => l_order_rec);
587
588 if(l_Return_status = FND_API.G_RET_STS_SUCCESS) THEN
589 raise ship_order;
590 END IF;
591 END IF; -- end of ship sales order
592
593 END IF; --end of process txn
594
595 -- Api body ends here
596
597 -- Standard check of p_commit.
598 IF FND_API.To_Boolean(p_commit)
599 THEN
600 COMMIT WORK;
601 END IF;
602
603 -- Standard call to get message count and IF count is get message info.
604 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
605 p_data => x_msg_data);
606 EXCEPTION
607 WHEN CREATE_ORDER THEN
608 rollback to UPDATE_PRODUCT_TXN_PVT;
609 x_return_status := FND_API.G_RET_STS_ERROR;
610 WHEN BOOK_ORDER THEN
611 rollback to UPDATE_PRODUCT_TXN_PVT;
612 x_return_status := FND_API.G_RET_STS_ERROR;
613 WHEN RELEASE_ORDER THEN
614 rollback to UPDATE_PRODUCT_TXN_PVT;
615 x_return_status := FND_API.G_RET_STS_ERROR;
616 WHEN SHIP_ORDER THEN
617 rollback to UPDATE_PRODUCT_TXN_PVT;
618 x_return_status := FND_API.G_RET_STS_ERROR;
619 WHEN FND_API.G_EXC_ERROR THEN
620 rollback to UPDATE_PRODUCT_TXN_PVT;
621 x_return_status := FND_API.G_RET_STS_ERROR;
622 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
623 p_data => x_msg_data);
624 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
625 rollback to UPDATE_PRODUCT_TXN_PVT;
626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
627 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
628 p_data => x_msg_data);
629 WHEN OTHERS THEN
630 rollback to UPDATE_PRODUCT_TXN_PVT;
631 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
632 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
633 THEN
634 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
635 END IF;
636 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
637 p_data => x_msg_data);
638 END update_product_txn;
639
640 End CSD_LOGISTICS_PVT;