[Home] [Help]
PACKAGE BODY: APPS.CSD_LOGISTICS_PVT
Source
1 PACKAGE BODY CSD_LOGISTICS_PVT as
2 /* $Header: csdvlogb.pls 120.8.12010000.2 2008/11/10 23:59:00 takwong 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 CURSOR ProdTxn_Cur_Type IS
156 SELECT A.Product_Transaction_Id,
157 A.Action_Type,
158 A.Action_Code,
159 A.Inventory_Item_Id,
160 A.Txn_Billing_Type_Id,
161 A.Price_List_Header_ID,
162 ABS(A.Estimate_Quantity),
163 A.Revision,
164 A.source_Serial_Number,
165 A.non_source_Serial_Number,
166 A.Lot_Number,
167 A.Sub_Inventory,
168 A.Return_Reason_Code,
169 A.Return_By_Date,
170 A.PO_Number,
171 A.Invoice_To_Org_Id,
172 A.Ship_To_Org_Id,
173 A.Unit_Of_Measure,
174 A.Charge,
175 A.No_Charge_Flag,
176 A.New_Order_Flag,
177 A.Object_Version_Number,
178 A.Transaction_Status,
179 A.Interface_To_OM_Flag,
180 A.Book_Sales_Order_flag,
181 A.Status,
182 A.Release_Sales_Order_Flag,
183 A.Ship_Sales_Order_Flag,
184 A.Order_Header_Id,
185 A.Order_Line_Id,
186 A.Repair_Line_Id,
187 A.Order_Number,
188 A.Estimate_Detail_Id,
189 A.Contract_Line_Id,
190 A.Business_Process_id,
191 B.incident_id,
192 A.Reference_Number
193 FROM CSD_Product_Txns_v A, CSD_REPAIRS B
194 WHERE A.REPAIR_LINE_ID = B.REPAIR_LINE_ID
195 AND Product_Transaction_Id =
196 p_Upd_ProdTxn_Rec.Product_Transaction_Id;
197
198 -- Define a cursor that gets Repair NUMBER AND Repair Status meaning for a given
199 -- repair line id
200 CURSOR RepairStatus_Cur_Type(p_Repair_Line_Id IN NUMBER) IS
201 SELECT dra.Repair_NUMBER, fndl2.meaning Status_Meaning
202 FROM Csd_Repairs dra, fnd_lookups fndl2
203 WHERE dra.status = fndl2.lookup_code
204 and fndl2.lookup_type = 'CSD_REPAIR_STATUS'
205 and dra.repair_line_id = p_Repair_line_id;
206
207 -- Define a cursor that gets currency code for a given Price List Id
208 -- VP:SU:03/01
209 Cursor PL_CurrencyCode_Cur_Type(p_Price_List_Id Number) Is
210 SELECT currency_code
211 FROM qp_list_headers_b
212 WHERE list_header_id = p_price_list_id;
213
214 BEGIN
215 -- Procedure Body
216
217 debug('Entered update_logistics_line pvt');
218
219 -- Create a Save Point before calling Update program
220 SAVEPOINT Update_Logistics_Line_Pvt;
221
222 -- Initialize API return status to success
223 x_return_status := FND_API.G_RET_STS_SUCCESS;
224
225 -- Standard call to check for API Version compatibility.
226 IF Not (FND_API.Compatible_API_Call(c_api_version,
227 p_api_version,
228 c_api_name,
229 G_PKG_NAME))
230 THEN
231 RAISE FND_API.G_Exc_UnExpected_Error;
232 END IF;
233
234 -- Initialize message list if p_init_msg_list is set to TRUE.
235 IF FND_API.to_Boolean(p_init_msg_list)
236 THEN
237 FND_MSG_PUB.initialize;
238 END IF;
239
240 debug('fetching the current record..Product_Transaction_Id['||p_Upd_ProdTxn_Rec.Product_Transaction_Id||']');
241 OPEN ProdTxn_Cur_Type;
242 FETCH ProdTxn_Cur_Type
243 INTO l_Product_Txn_Rec.Product_Transaction_Id,
244 l_Product_Txn_Rec.Action_Type,
245 l_Product_Txn_Rec.Action_Code,
246 l_Product_Txn_Rec.Inventory_Item_Id,
247 l_Product_Txn_Rec.Txn_Billing_Type_Id,
248 l_Product_Txn_Rec.price_list_id,
249 l_Product_Txn_Rec.Quantity,
250 l_Product_Txn_Rec.Revision,
251 l_Product_Txn_Rec.source_Serial_Number,
252 l_Product_Txn_Rec.non_source_Serial_Number,
253 l_Product_Txn_Rec.Lot_Number,
254 l_Product_Txn_Rec.Sub_Inventory,
255 l_Product_Txn_Rec.return_reason,
256 l_Product_Txn_Rec.Return_By_Date,
257 l_Product_Txn_Rec.PO_Number,
258 l_Product_Txn_Rec.Invoice_To_Org_Id,
259 l_Product_Txn_Rec.Ship_To_Org_Id,
260 l_Product_Txn_Rec.unit_of_measure_code,
261 l_Product_Txn_Rec.after_warranty_cost,
262 l_Product_Txn_Rec.No_Charge_Flag,
263 l_Product_Txn_Rec.New_Order_Flag,
264 l_Product_Txn_Rec.Object_Version_Number,
265 l_Product_Txn_Rec.Prod_Txn_Status,
266 l_Product_Txn_Rec.Interface_To_OM_Flag,
267 l_Product_Txn_Rec.Book_Sales_Order_flag,
268 l_ro_status,
269 l_Product_Txn_Rec.Release_Sales_Order_Flag,
270 l_Product_Txn_Rec.Ship_Sales_Order_Flag,
271 l_Product_Txn_Rec.Order_Header_Id,
272 l_Product_Txn_Rec.Order_Line_Id,
273 l_Product_Txn_Rec.Repair_Line_Id,
274 l_Product_Txn_Rec.Order_Number,
275 l_Product_Txn_Rec.Estimate_Detail_Id,
276 l_Product_Txn_Rec.Contract_Id,
277 l_Product_Txn_Rec.Business_Process_id,
278 l_Product_Txn_Rec.incident_id,
279 l_Product_Txn_Rec.source_instance_number;
280
281 CLOSE ProdTxn_Cur_Type;
282 debug('object version number fetched ['||l_Product_Txn_Rec.Object_Version_NUMBER||']');
283
284 -- Validate Product_Transaction_Id is null
285 CSD_PROCESS_UTIL.Check_Reqd_Param(p_param_value => p_Upd_ProdTxn_Rec.Product_Transaction_id,
286 p_param_name => 'Product Transaction ID',
287 p_api_name => C_API_Name);
288
289 debug('Validating the prod txn id');
290 -- Validate the Product_Transaction_Id exists in csd_product_transactions
291 IF NOT
292 (CSD_PROCESS_UTIL.Validate_prod_txn_id(p_prod_txn_id => p_Upd_ProdTxn_Rec.Product_Transaction_id))
293 THEN
294 RAISE FND_API.G_EXC_ERROR;
295 END IF;
296
297 debug('ro status check...');
298 -- Check Repair Order Status if it Hold or Closed then raise exception
299 IF l_ro_Status <> C_RO_Status_OPEN
300 THEN
301
302 OPEN RepairStatus_Cur_Type(l_Product_Txn_Rec.Repair_Line_Id);
303 FETCH RepairStatus_Cur_Type
304 INTO l_RO_NUMBER, l_RO_Status_Meaning;
305 CLOSE RepairStatus_Cur_Type;
306 -- RAISE Error message
307 FND_MESSAGE.SET_NAME('CSD', 'CSD_RO_NOT_OPEN_NO_PRODTXN_UPD');
308 FND_MESSAGE.SET_TOKEN('RO_NUMBER', l_RO_NUMBER);
309 FND_MESSAGE.SET_TOKEN('RO_STATUS', l_RO_Status_Meaning);
310 FND_MSG_PUB.ADD;
311 RAISE FND_API.G_EXC_ERROR;
312
313 END IF;
314
315 debug('object version number check, db['||l_Product_Txn_Rec.Object_Version_NUMBER||']');
316 debug('input ['||p_Upd_ProdTxn_Rec.Object_Version_Number||']');
317 -- Check Object Version NUMBER. Object Version NUMBER FROM parameter should match the value in Database.
318 IF l_Product_Txn_Rec.Object_Version_NUMBER <>
319 p_Upd_ProdTxn_Rec.Object_Version_Number
320 THEN
321
322 FND_MESSAGE.SET_NAME('CSD', 'CSD_OBJECT_VERSION_NUMBER');
323 FND_MSG_PUB.ADD;
324 RAISE FND_API.G_EXC_ERROR;
325
326 END IF;
327
328 -- Set flag attributes values for record UpdateProdTrxn_Rec
329 Csd_Logistics_Util.Set_ProductTrxnRec_Flags(p_Upd_ProdTxn_Rec => p_Upd_ProdTxn_Rec,
330 x_Product_Txn_Rec => l_Product_Txn_Rec);
331
332 -- Some of the attributes that are passed as input parameters to the API can not
333 -- be updated by user, so make sure that those attributes are not updated
334
335 IF l_Product_Txn_Rec.Prod_Txn_Status <> C_PROD_TXN_STS_ENTERED
336 THEN
337
338 -- Once product transaction line is interfaced then user can make changes
339 -- to some attributes. List of these are listed in following API
340 Csd_Logistics_Util.Compare_ProductTrxnRec(p_Upd_ProdTxn_Rec => p_Upd_ProdTxn_Rec,
341 p_Product_Txn_Rec => l_Product_Txn_Rec,
342 x_Return_Status => x_Return_Status,
343 x_Msg_Data => x_Msg_Data,
344 x_Msg_Count => x_Msg_Count);
345
346 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS
347 THEN
348 RAISE FND_API.G_EXC_ERROR;
349 END IF;
350
351 END IF;
352
353 -- Build Product Txn Rec,
354 Csd_Logistics_Util.Build_ProductTxnRec(p_Upd_ProdTxn_Rec => p_Upd_ProdTxn_Rec,
355 x_Product_Txn_Rec => l_Product_Txn_Rec);
356
357
358 --bug#6075825
359 /* Don't need this validation here.
360 -- Call Validate_Product_Txn_Rec API to validate each attributes values.
361 Csd_Logistics_Util.Validate_ProductTrxnRec(p_Upd_ProdTxn_Rec => p_Upd_ProdTxn_Rec,
362 p_Product_Txn_Rec => l_Product_Txn_Rec,
363 x_Return_Status => x_Return_Status,
364 x_Msg_Data => x_Msg_Data,
365 x_Msg_Count => x_Msg_Count);
366
367 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS
368 THEN
369 --dbms_output.put_line('Validation failed');
370 RAISE FND_API.G_EXC_ERROR;
371 END IF;
372 */
373 --Get Currency Code for a given Price List Id
374 Open PL_CurrencyCOde_Cur_Type(l_Product_Txn_Rec.Price_List_ID);
375 Fetch PL_CurrencyCode_Cur_Type
376 Into l_Product_Txn_Rec.Currency_Code;
377 Close PL_CurrencyCode_Cur_TYpe;
378
379 -- Call Private API to Update Product Transaction Record
380 -- CSD_LOGISTICS_PVT.Update_product_txn
381 --dbms_output.put_line('Calling update');
382 CSD_LOGISTICS_PVT.Update_product_txn(p_api_version => 1.0,
383 p_commit => fnd_api.g_false,
384 p_init_msg_list => fnd_api.g_false,
385 p_validation_level => fnd_api.g_valid_level_full,
386 x_product_txn_rec => l_Product_Txn_Rec,
387 x_return_status => x_Return_Status,
388 x_msg_count => x_Msg_Count,
389 x_msg_data => x_Msg_Data);
390
391 IF x_Return_Status = FND_API.G_RET_STS_ERROR
392 THEN
393 RAISE FND_API.G_EXC_ERROR;
394 ELSIF x_Return_Status = FND_API.G_RET_STS_UNEXP_ERROR
395 THEN
396 RAISE Fnd_Api.G_Exc_UnExpected_Error;
397 END IF;
398
399 x_object_version_number := l_Product_Txn_Rec.object_version_number;
400 x_order_header_id := l_Product_Txn_Rec.order_header_id;
401 x_order_line_id := l_Product_Txn_Rec.order_line_id;
402
403
404 EXCEPTION
405
406 WHEN Fnd_Api.G_Exc_Error THEN
407
408 x_return_status := Fnd_Api.G_Ret_Sts_Error;
409
410 ROLLBACK TO Update_Logistics_Line_pvt;
411
412 Fnd_Msg_Pub.Count_And_Get(p_count => x_msg_count,
413 p_data => x_msg_data);
414
415 WHEN Fnd_Api.G_Exc_UnExpected_Error THEN
416
417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
418
419 ROLLBACK TO Update_Logistics_Line_pvt;
420
421 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
422 p_data => x_msg_data);
423
424 WHEN OTHERS THEN
425
426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
427
428 ROLLBACK TO Update_Logistics_Line_pvt;
429
430 IF Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_Msg_Lvl_Unexp_Error)
431 THEN
432 Fnd_Msg_Pub.Add_Exc_Msg(G_PKG_NAME, C_api_name);
433 END IF;
434
435 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
436 p_data => x_msg_data);
437
438 END Update_Logistics_Line;
439
440
441
442 /*----------------------------------------------------------------*/
443 /* procedure name: update_product_txn */
444 /* description : procedure to update product txn lines.It is */
445 /* called from update_logistics_line API */
446 /* This is different from the process_pvt api */
447 /* in the sense that this does not commit if */
448 /* something fails (booking or release). */
449 /*----------------------------------------------------------------*/
450
451 PROCEDURE update_product_txn(p_api_version IN NUMBER,
452 p_commit IN VARCHAR2,
453 p_init_msg_list IN VARCHAR2,
454 p_validation_level IN NUMBER,
455 x_product_txn_rec IN OUT NOCOPY CSD_PROCESS_PVT.PRODUCT_TXN_REC,
456 x_return_status OUT NOCOPY VARCHAR2,
457 x_msg_count OUT NOCOPY NUMBER,
458 x_msg_data OUT NOCOPY VARCHAR2) IS
459
460 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PRODUCT_TXN';
461 l_api_version CONSTANT NUMBER := 1.0;
462 l_msg_count NUMBER;
463 l_msg_data VARCHAR2(2000);
464 l_msg_index NUMBER;
465 l_estimate_Rec cs_charge_details_pub.charges_rec_type;
466 l_prodtxn_db_attr CSD_LOGISTICS_UTIL.PRODTXN_DB_ATTR_REC;
467 l_order_Rec csd_process_pvt.om_interface_rec;
468 l_est_detail_id NUMBER;
469 l_add_to_order_id NUMBER;
470 l_add_to_order_flag VARCHAR2(1);
471 l_transaction_type_id NUMBER;
472 l_repair_line_id NUMBER;
473
474 create_order EXCEPTION;
475 book_order EXCEPTION;
476 release_order EXCEPTION;
477 ship_order EXCEPTION;
478
479
480 -- Variables used in FND Log
481 l_error_level number := FND_LOG.LEVEL_ERROR;
482 l_mod_name varchar2(2000) := 'csd.plsql.csd_logistics_pvt.update_product_txn';
483
484 l_return_Status varchar2(1);
485
486 BEGIN
487
488 SAVEPOINT UPDATE_PRODUCT_TXN_PVT;
489
490 l_prodtxn_db_attr := CSD_LOGISTICS_UTIL.get_prodtxn_db_attr(x_product_txn_rec.product_transaction_id);
491
492 -- Debug message
493 -----------------------------------------------------------------------------
494 /*********************************************************************
495 /* Code here got moved to CSD_LOGISTICS_UTIL.upd_prodtxn_n_chrgline
496 **********************************************************************/
497 -----------------------------------------------------------------------------
498 CSD_LOGISTICS_UTIL.upd_prodtxn_n_chrgline(x_product_txn_rec ,
499 l_prodtxn_db_attr,
500 l_est_detail_id,
501 l_repair_line_id,
502 l_add_to_order_flag,
503 l_add_to_order_id,
504 l_transaction_type_id);
505
506 x_product_txn_Rec.estimate_detail_id := l_est_detail_id;
507 x_product_txn_Rec.repair_line_id := l_repair_line_id;
508 x_product_txn_Rec.add_to_order_flag := l_add_to_order_flag;
509 x_product_txn_Rec.order_header_id := l_add_to_order_id;
510 x_product_txn_rec.transaction_type_id := l_transaction_type_id;
511
512
513 Debug('process_txn_flag =' ||
514 x_product_txn_rec.process_txn_flag);
515 Debug('interface_to_om_flag =' ||
516 x_product_txn_rec.interface_to_om_flag);
517 Debug('book_sales_order_flag =' ||
518 x_product_txn_rec.book_sales_order_flag);
519 Debug('release_sales_order_flag =' ||
520 x_product_txn_rec.release_sales_order_flag);
521 Debug('ship_sales_order_flag =' ||
522 x_product_txn_rec.ship_sales_order_flag);
523
524
525 IF x_product_txn_rec.process_txn_flag = 'Y'
526 THEN
527
528
529 l_order_Rec := CSD_LOGISTICS_UTIL.get_order_rec(x_product_txn_rec.repair_line_id);
530
531 CSD_LOGISTICS_UTIL.interface_prodtxn( x_return_status => l_return_status,
532 p_product_txn_rec => x_product_txn_rec,
533 p_prodtxn_db_attr => l_prodtxn_db_attr,
534 px_order_rec => l_order_rec);
535 if NOT (l_Return_status = FND_API.G_RET_STS_SUCCESS) THEN
536 raise create_order;
537 END IF;
538
539 IF l_prodtxn_db_attr.curr_book_order_flag <>
540 x_product_txn_rec.book_sales_order_flag AND
541 x_product_txn_rec.book_sales_order_flag = 'Y'
542 THEN
543 CSD_LOGISTICS_UTIL.book_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 book_order;
549 END IF;
550
551
552 END IF; -- end of book order
553
554 IF l_prodtxn_db_attr.curr_release_order_flag <>
555 x_product_txn_rec.release_sales_order_flag AND
556 x_product_txn_rec.release_sales_order_flag = 'Y'
557 THEN
558 CSD_LOGISTICS_UTIL.pickrelease_prodtxn( x_return_status => l_return_status,
559 p_product_txn_rec => x_product_txn_rec,
560 p_prodtxn_db_attr => l_prodtxn_db_attr,
561 px_order_rec => l_order_rec);
562 if(l_Return_status = FND_API.G_RET_STS_SUCCESS) THEN
563 raise release_order;
564 END IF;
565 END IF; --end of pick-release sales order
566
567 IF l_prodtxn_db_attr.curr_ship_order_flag <>
568 x_product_txn_rec.ship_sales_order_flag AND
569 x_product_txn_rec.ship_sales_order_flag = 'Y'
570 THEN
571 CSD_LOGISTICS_UTIL.ship_prodtxn( x_return_status => l_return_status,
572 p_product_txn_rec => x_product_txn_rec,
573 p_prodtxn_db_attr => l_prodtxn_db_attr,
574 px_order_rec => l_order_rec);
575
576 if(l_Return_status = FND_API.G_RET_STS_SUCCESS) THEN
577 raise ship_order;
578 END IF;
579 END IF; -- end of ship sales order
580
581 END IF; --end of process txn
582
583 -- Api body ends here
584
585 -- Standard check of p_commit.
586 IF FND_API.To_Boolean(p_commit)
587 THEN
588 COMMIT WORK;
589 END IF;
590
591 -- Standard call to get message count and IF count is get message info.
592 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
593 p_data => x_msg_data);
594 EXCEPTION
595 WHEN CREATE_ORDER THEN
596 rollback to UPDATE_PRODUCT_TXN_PVT;
597 x_return_status := FND_API.G_RET_STS_ERROR;
598 WHEN BOOK_ORDER THEN
599 rollback to UPDATE_PRODUCT_TXN_PVT;
600 x_return_status := FND_API.G_RET_STS_ERROR;
601 WHEN RELEASE_ORDER THEN
602 rollback to UPDATE_PRODUCT_TXN_PVT;
603 x_return_status := FND_API.G_RET_STS_ERROR;
604 WHEN SHIP_ORDER THEN
605 rollback to UPDATE_PRODUCT_TXN_PVT;
606 x_return_status := FND_API.G_RET_STS_ERROR;
607 WHEN FND_API.G_EXC_ERROR THEN
608 rollback to UPDATE_PRODUCT_TXN_PVT;
609 x_return_status := FND_API.G_RET_STS_ERROR;
610 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
611 p_data => x_msg_data);
612 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
613 rollback to UPDATE_PRODUCT_TXN_PVT;
614 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
615 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
616 p_data => x_msg_data);
617 WHEN OTHERS THEN
618 rollback to UPDATE_PRODUCT_TXN_PVT;
619 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
620 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
621 THEN
622 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
623 END IF;
624 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
625 p_data => x_msg_data);
626 END update_product_txn;
627
628 End CSD_LOGISTICS_PVT;