[Home] [Help]
PACKAGE BODY: APPS.CSFW_ORDER_PARTS
Source
1 Package Body CSFW_ORDER_PARTS AS
2 /* $Header: csfwordb.pls 120.3 2007/12/18 13:52:02 htank ship $ */
3 --
4 -- Purpose: To create parts order for Field Service Wireless
5 --
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 -- mmerchan 10/23/01 Created new package body
10 -- mmerchan 05/23/02 Added task_id, task_assignment_id
11 -- htank 20-Jun-2006 Bug # 5171584
12 -- hgotur 20-Feb-2006 Bug 5465343: TRANSACT_MOVE_ORDER - returning messages from TRANSACT_MATERIAL
13 -- htank 18-Dec-2007 Bug # 5242440
14
15 G_PKG_NAME CONSTANT VARCHAR2(30) := 'csfw_order_parts';
16 G_FILE_NAME CONSTANT VARCHAR2(30) := 'csfwordb.pls';
17
18 -- This procedure is to create single order line.
19 PROCEDURE process_order (
20 order_type_id IN NUMBER,
21 ship_to_location_id IN NUMBER,
22 shipping_method_code IN VARCHAR2,
23 task_id IN NUMBER,
24 task_assignment_id IN NUMBER,
25 need_by_date IN DATE ,
26 dest_organization_id IN NUMBER,
27 operation IN VARCHAR2,
28 resource_type IN VARCHAR2,
29 resource_id IN NUMBER,
30 inventory_item_id IN NUMBER,
31 revision IN VARCHAR2,
32 unit_of_measure IN VARCHAR2,
33 source_organization_id IN NUMBER,
34 source_subinventory IN VARCHAR2,
35 ordered_quantity IN NUMBER,
36 order_number OUT NOCOPY NUMBER,
37 x_return_status OUT NOCOPY VARCHAR2,
38 x_error_msg OUT NOCOPY VARCHAR2
39 )IS
40
41 CURSOR C_ORDER_NUMBER(v_header_id NUMBER)
42 IS
43 SELECT order_number
44 FROM oe_order_headers_all
45 where header_id = v_header_id;
46
47 l_return_status VARCHAR2(1);
48 l_msg_count NUMBER;
49 l_msg_data VARCHAR2(2000);
50 l_header_rec csp_parts_requirement.header_rec_type;
51 l_line_rec csp_parts_requirement.line_rec_type;
52 l_line_tbl csp_parts_requirement.line_tbl_type;
53 v_OPEN_REQUIREMENT varchar2(240);
54 l_destination_org_id number;
55 l_dest_sub_inv varchar2(30);
56
57 cursor c_default_org (v_resource_id Number) is
58 Select ORGANIZATION_ID ,SUBINVENTORY_CODE
59 from csp_inv_loc_assignments
60 where resource_id = v_resource_id
61 and DEFAULT_CODE = 'IN'
62 and sysdate between nvl(EFFECTIVE_DATE_START, sysdate) and nvl(EFFECTIVE_DATE_END, sysdate);
63
64 r_default_org c_default_org%ROWTYPE;
65
66
67 BEGIN
68 x_return_status := 'S'; order_number := -1;
69
70 l_header_rec.order_type_id := order_type_id;
71 l_header_rec.ship_to_location_id := ship_to_location_id;
72
73 IF (task_id <> 0) then
74 l_header_rec.task_id := task_id;
75 end if;
76
77 if (task_assignment_id <> 0) then
78 l_header_rec.task_assignment_id :=task_assignment_id;
79 end if;
80
81 l_header_rec.need_by_date := need_by_date;
82 l_header_rec.dest_organization_id := dest_organization_id;
83
84 l_header_rec.operation := operation;
85 l_header_rec.resource_type := resource_type;
86 l_header_rec.resource_id := resource_id;
87
88 -- Adding the resource default sub inventory
89 open c_default_org(l_header_rec.resource_id);
90 fetch c_default_org into r_default_org;
91 close c_default_org;
92
93 l_destination_org_id := r_default_org.ORGANIZATION_ID;
94 l_dest_sub_inv := r_default_org.SUBINVENTORY_CODE;
95
96
97 l_header_rec.dest_organization_id := l_destination_org_id ;
98 l_header_rec.dest_subinventory := l_dest_sub_inv;
99
100 --END OF HEADER RECORD. Lets Set the Line record
101
102 l_line_rec.inventory_item_id := inventory_item_id;
103 l_line_rec.revision := revision;
104 l_line_rec.unit_of_measure := unit_of_measure;
105 l_line_rec.ordered_quantity := ordered_quantity;
106 l_line_rec.quantity := ordered_quantity;
107 l_line_rec.source_organization_id := source_organization_id;
108
109 if (source_subinventory is not null and source_subinventory <> '') then
110 l_line_rec.source_subinventory := source_subinventory;
111 end if;
112
113 l_line_rec.shipping_method_code := shipping_method_code;
114 l_line_rec.order_by_date :=sysdate;
115 l_line_rec.line_num := 1;
116 l_line_tbl(1) := l_line_rec;
117
118 --we would call with true commit flag
119 CSP_PARTS_REQUIREMENT.process_requirement(p_api_version => 1.0
120 ,p_Init_Msg_List => FND_API.G_TRUE
121 ,p_commit => FND_API.G_TRUE
122 ,px_header_rec => l_header_rec
123 ,px_line_table => l_Line_Tbl
124 ,p_create_order_flag => 'Y'
125 ,x_return_status => l_return_Status
126 ,x_msg_count => l_msg_count
127 ,x_msg_data => l_msg_data
128 );
129
130 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
131 x_return_status := 'E';
132 x_error_msg := l_msg_data;
133
134 -- This if is only for the Debug purpose
135 if l_msg_count > 0 THEN
136 FOR l_counter IN 1 .. l_msg_count
137 LOOP
138 fnd_msg_pub.get
139 ( p_msg_index => l_counter
140 , p_encoded => FND_API.G_FALSE
141 , p_data => l_msg_data
142 , p_msg_index_out => l_msg_count
143 );
144 END LOOP;
145 --dbms_output.put_line(x_error_msg || 'API error');
146 end if;
147 ELSE
148 -- This is successful. But if there is a message data then it's a failure
149 IF l_msg_data IS NOT NULL THEN
150 x_return_status := 'E';
151 x_error_msg := l_msg_data;
152 ELSE
153 -- we can get the Order Number here
154 BEGIN
155 OPEN C_ORDER_NUMBER(l_header_rec.order_header_id);
156 FETCH C_ORDER_NUMBER INTO order_number;
157 IF C_ORDER_NUMBER%NOTFOUND THEN
158 order_number := -2; /* Fatal Error */
159 END IF;
160 CLOSE C_ORDER_NUMBER;
161
162 EXCEPTION
163 WHEN OTHERS THEN
164 order_number := -3; /* Fatal Error */
165 END;
166 END IF;
167
168 END IF;
169
170 END PROCESS_ORDER;
171
172
173
174 PROCEDURE process_order
175 ( order_type_id IN NUMBER,
176 ship_to_location_id IN NUMBER,
177 dest_organization_id IN NUMBER,
178 operation IN VARCHAR2,
179 need_by_date IN DATE,
180 inventory_item_id IN NUMBER,
181 revision IN VARCHAR2,
182 unit_of_measure IN VARCHAR2,
183 ordered_quantity IN NUMBER,
184 task_id IN NUMBER,
185 task_assignment_id IN NUMBER,
186 order_number OUT NOCOPY NUMBER,
187 x_return_status OUT NOCOPY VARCHAR2,
188 x_error_msg OUT NOCOPY VARCHAR2
189 ) IS
190
191 CURSOR C_ORDER_NUMBER(v_header_id NUMBER)
192 IS
193 SELECT order_number
194 FROM oe_order_headers_all
195 where header_id = v_header_id;
196
197
198 l_return_status VARCHAR2(1);
199 l_msg_count NUMBER;
200 l_msg_data VARCHAR2(2000);
201 l_header_rec csp_parts_requirement.header_rec_type;
202 l_line_rec csp_parts_requirement.line_rec_type;
203 l_line_tbl csp_parts_requirement.line_tbl_type;
204 v_OPEN_REQUIREMENT varchar2(240);
205 l_resource_id number;
206 l_resource_type varchar2(30);
207 l_destination_org_id NUMBER;
208 l_dest_sub_inv VARCHAR2(30);
209
210 cursor c_default_org (v_resource_id Number) is
211 Select ORGANIZATION_ID ,SUBINVENTORY_CODE
212 from csp_inv_loc_assignments
213 where resource_id = v_resource_id
214 and DEFAULT_CODE = 'IN'
215 and sysdate between nvl(EFFECTIVE_DATE_START, sysdate) and nvl(EFFECTIVE_DATE_END, sysdate);
216
217 r_default_org c_default_org%ROWTYPE;
218
219 cursor c_resource( v_user_id number) is
220 select a.resource_id resource_id,
221 b.resource_type resource_type
222 from jtf_rs_resource_extns a, CSP_RS_RESOURCES_V b
223 where a.user_id = v_user_id
224 and a.resource_id = b.resource_id;
225
226 r_resource c_resource%ROWTYPE;
227
228 BEGIN
229 x_return_status := 'S'; order_number := -1;
230 l_header_rec.order_type_id := order_type_id;
231
232 l_header_rec.ship_to_location_id := ship_to_location_id;
233 l_header_rec.dest_organization_id := dest_organization_id;
234 l_header_rec.operation := 'CREATE';
235 l_header_rec.need_by_date := need_by_date;
236 l_header_rec.task_id := task_id;
237 l_header_rec.task_assignment_id := task_assignment_id;
238
239 l_line_rec.inventory_item_id := inventory_item_id;
240 l_line_rec.revision := revision;
241 l_line_rec.unit_of_measure := unit_of_measure;
242 l_line_rec.ordered_quantity := ordered_quantity;
243 l_line_rec.quantity := ordered_quantity;
244 -- l_line_rec.source_organization_id := dest_organization_id;
245 l_line_rec.line_num := 1;
246 l_line_tbl(1) := l_line_rec;
247
248 -- the Resource_id and type
249 open c_resource(FND_GLOBAL.USER_ID);
250 fetch c_resource into r_resource;
251 close c_resource;
252
253 l_resource_id := r_resource.resource_id;
254 l_resource_type := r_resource.resource_type;
255
256
257 l_header_rec.resource_id := l_resource_id;
258 l_header_rec.resource_type := l_resource_type;
259
260 -- Adding the resource default sub inventory
261 open c_default_org(l_resource_id);
262 fetch c_default_org into r_default_org;
263 close c_default_org;
264
265 l_destination_org_id := r_default_org.ORGANIZATION_ID;
266 l_dest_sub_inv := r_default_org.SUBINVENTORY_CODE;
267
268 l_header_rec.dest_organization_id := l_destination_org_id ;
269 l_header_rec.dest_subinventory := l_dest_sub_inv;
270
271
272
273
274 --CSP_PARTS_ORDER.process_order(p_api_version => 1.0
275 CSP_PARTS_REQUIREMENT.process_requirement(p_api_version => 1.0
276 ,p_Init_Msg_List => FND_API.G_TRUE
277 ,p_commit => FND_API.G_TRUE
278 ,px_header_rec => l_header_rec
279 ,px_line_table => l_Line_Tbl
280 ,p_create_order_flag => 'Y'
281 ,x_return_status => l_return_Status
282 ,x_msg_count => l_msg_count
283 ,x_msg_data => l_msg_data
284 );
285
286 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
287 x_return_status := 'E';
288 x_error_msg := l_msg_data;
289
290 -- This if is only for the Debug purpose
291 if l_msg_count > 0 THEN
292 FOR l_counter IN 1 .. l_msg_count
293 LOOP
294 fnd_msg_pub.get
295 ( p_msg_index => l_counter
296 , p_encoded => FND_API.G_FALSE
297 , p_data => l_msg_data
298 , p_msg_index_out => l_msg_count
299 );
300 END LOOP;
301 --dbms_output.put_line(x_error_msg || 'API error');
302 end if;
303 ELSE
304 -- This is successful. But if there is a message data then it's a failure
305 IF l_msg_data IS NOT NULL THEN
306 x_return_status := 'E';
307 x_error_msg := l_msg_data;
308 ELSE
309 -- we can get the Order Number here
310 BEGIN
311 OPEN C_ORDER_NUMBER(l_header_rec.order_header_id);
312 FETCH C_ORDER_NUMBER INTO order_number;
313 IF C_ORDER_NUMBER%NOTFOUND THEN
314 order_number := -2; /* Fatal Error */
315 END IF;
316 CLOSE C_ORDER_NUMBER;
317
318 EXCEPTION
319 WHEN OTHERS THEN
320 order_number := -3; /* Fatal Error */
321 END;
322 END IF;
323
324 END IF;
325
326 END PROCESS_ORDER;
327
328
329
330
331
332 PROCEDURE CREATE_MOVE_ORDER
333 ( p_organization_id IN NUMBER
334 ,p_from_subinventory_code IN VARCHAR2
335 ,p_from_locator_id IN NUMBER
336 ,p_inventory_item_id IN NUMBER
337 ,p_revision IN VARCHAR2
338 ,p_lot_number IN VARCHAR2
339 ,p_serial_number_start IN VARCHAR2
340 ,p_serial_number_end IN VARCHAR2
341 ,p_quantity IN NUMBER
342 ,p_uom_code IN VARCHAR2
343 ,p_to_subinventory_code IN VARCHAR2
344 ,p_to_locator_id IN NUMBER
345 ,p_date_required IN DATE
346 ,p_comments IN VARCHAR2
347 ,x_move_order_number OUT NOCOPY VARCHAR2
348 ,x_return_status OUT NOCOPY VARCHAR2
349 ,x_msg_count OUT NOCOPY NUMBER
350 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
351
352 l_api_version_number CONSTANT NUMBER := 1.0;
353 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
354 l_msg_count NUMBER;
355 l_msg_data VARCHAR2(240);
356 l_commit VARCHAR2(1) := FND_API.G_FALSE;
357 l_move_order_id number;
358 lx_line_id number;
359 l_api_name CONSTANT VARCHAR2(30) := 'CSFW.Create_Move_Order';
360 l_locator_id_to number;
361 l_locator_id_from number;
362 l_revision varchar2(100);
363 l_sr_number_start varchar2(100);
364 l_sr_number_end varchar2(100);
365 l_lot_number varchar2(100);
366 EXCP_USER_DEFINED EXCEPTION;
367 l_date_required DATE;
368 l_msg_count2 number;
369
370 CURSOR c_REQUEST_NUMBER (v_move_order_id number )IS
371 SELECT REQUEST_NUMBER
372 FROM MTL_TXN_REQUEST_HEADERS
373 WHERE HEADER_ID = v_move_order_id ;
374
375
376 BEGIN
377
378 -- for now lets get the sysdate for p_date_required
379 IF (p_date_required IS NULL) THEN
380 l_date_required := sysdate;
381 ELSE
382 l_date_required := p_date_required;
383 END IF;
384
385
386 IF (p_to_locator_id = 0) THEN
387 l_locator_id_to := null;
388 ELSE
389 l_locator_id_to := p_to_locator_id;
390 END IF;
391
392
393 IF (p_from_locator_id = 0) THEN
394 l_locator_id_from := null;
395 ELSE
396 l_locator_id_from := p_from_locator_id;
397 END IF;
398
399
400 IF (p_revision = '') THEN
401 l_revision := null;
402 ELSE
403 l_revision := p_revision;
404 END IF;
405
406
407 IF (p_lot_number = '') THEN
408 l_lot_number := null;
409 ELSE
410 l_lot_number := p_lot_number;
411 END IF;
412
413
414 IF (p_serial_number_start = '') THEN
415 l_sr_number_start := null;
416 ELSE
417 l_sr_number_start := p_serial_number_start;
418 END IF;
419
420 IF (p_serial_number_end = '') THEN
421 l_sr_number_end := null;
422 ELSE
423 l_sr_number_end := p_serial_number_end;
424 END IF;
425
426
427 -- WE will first call for the header and then for the line
428 -- after we are done ... we can return the number
429 CSP_TRANSACTIONS_PUB.CREATE_MOVE_ORDER_HEADER(
430 px_header_id => l_move_order_id
431 ,p_request_number => null
432 ,p_api_version => l_api_version_number
433 ,p_Init_Msg_List => FND_API.G_FALSE
434 ,p_commit => l_commit
435 ,p_date_required => l_date_required
436 ,p_organization_id => p_organization_id
437 ,p_from_subinventory_code => p_from_subinventory_code
438 ,p_to_subinventory_code => p_to_subinventory_code
439 ,p_address1 => null
440 ,p_address2 => null
441 ,p_address3 => null
442 ,p_address4 => null
443 ,p_city => null
444 ,p_postal_code => null
448 ,p_freight_carrier => null
445 ,p_state => null
446 ,p_province => null
447 ,p_country => null
449 ,p_shipment_method => null
450 ,p_autoreceipt_flag => null
451 ,x_return_status => l_return_status
452 ,x_msg_count => l_msg_count
453 ,x_msg_data => l_msg_data );
454
455 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
456
457 -- bug # 5171584
458 fnd_msg_pub.get
459 ( p_msg_index => l_msg_count
460 , p_encoded => FND_API.G_FALSE
461 , p_data => x_msg_data
462 , p_msg_index_out => l_msg_count2
463 );
464
465 RAISE FND_API.G_EXC_ERROR;
466 ELSE
467
468 --call the Line
469 CSP_TRANSACTIONS_PUB.CREATE_MOVE_ORDER_LINE
470 (p_api_version => l_api_version_number
471 ,p_Init_Msg_List => FND_API.G_FALSE
472 ,p_commit => FND_API.G_FALSE
473 ,px_line_id => lx_line_id
474 ,p_header_id => l_move_order_id
475 ,p_organization_id => p_organization_id
476 ,p_from_subinventory_code => p_from_subinventory_code
477 ,p_from_locator_id => l_locator_id_from
478 ,p_inventory_item_id => p_inventory_item_id
479 ,p_revision => l_revision
480 ,p_lot_number => l_lot_number
481 ,p_serial_number_start => l_sr_number_start
482 ,p_serial_number_end => l_sr_number_end
483 ,p_quantity => p_quantity
484 ,p_uom_code => p_uom_code
485 ,p_quantity_delivered => null
486 ,p_to_subinventory_code => p_to_subinventory_code
487 ,p_to_locator_id => l_locator_id_to
488 ,p_to_organization_id => p_organization_id
489 ,p_service_request => null
490 ,p_task_id => null
491 ,p_task_assignment_id => null
492 ,p_customer_po => null
493 ,p_date_required => l_date_required
494 ,p_comments => p_comments
495 ,x_return_status => l_return_status
496 ,x_msg_count => l_msg_count
497 ,x_msg_data => l_msg_data ) ;
498
499
500 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
501
502 -- bug # 5171584
503 fnd_msg_pub.get
504 ( p_msg_index => l_msg_count
505 , p_encoded => FND_API.G_FALSE
506 , p_data => x_msg_data
507 , p_msg_index_out => l_msg_count2
508 );
509
510 RAISE FND_API.G_EXC_ERROR;
511 ELSE
512 x_return_status := FND_API.G_RET_STS_SUCCESS ;
513 COMMIT WORK;
514 open c_REQUEST_NUMBER(l_move_order_id);
515 fetch c_REQUEST_NUMBER into x_move_order_number;
516 close c_REQUEST_NUMBER;
517 END IF;
518
519 END IF;
520
521 EXCEPTION
522 WHEN OTHERS THEN
523 x_return_status := FND_API.G_RET_STS_ERROR;
524
525 END CREATE_MOVE_ORDER;
526
527
528 PROCEDURE TRANSACT_MOVE_ORDER
529 ( p_type_of_transaction IN VARCHAR2,
530 p_inventory_item_id IN NUMBER,
531 p_organization_id IN NUMBER,
532 p_source_sub IN VARCHAR2,
533 p_source_locator IN NUMBER,
534 p_lot IN VARCHAR2,
535 p_revision IN VARCHAR2,
536 p_serial_number IN VARCHAR2,
537 p_qty IN NUMBER,
538 p_uom IN VARCHAR2,
539 p_line_id IN NUMBER,
540 p_dest_sub IN VARCHAR2,
541 p_dest_org_id IN NUMBER, -- this will be same as p_organization_id in case of move order
542 p_dest_locator IN NUMBER,
543 p_waybill IN VARCHAR2,
544 p_ship_Nr IN VARCHAR2,
545 p_freight_code IN VARCHAR2,
546 p_exp_del_date IN VARCHAR2
547 ,x_return_status OUT NOCOPY VARCHAR2
548 ,x_msg_count OUT NOCOPY NUMBER
549 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
550
551 l_txn_type_id number;
552 l_transaction_id number;
553 l_transaction_header_id number;
554 l_msg_count number;
555 l_msg_data varchar2(4000);
556 l_return_status varchar2(1);
557 l_source_locator NUMBER;
558 l_lot VARCHAR2(100);
559 l_revision VARCHAR2(100);
560 l_serial_number VARCHAR2(100);
561 l_dest_locator NUMBER;
562 l_api_name CONSTANT VARCHAR2(30) := 'CSFW.Transact_Move_Order';
563 EXCP_USER_DEFINED EXCEPTION;
564
565 BEGIN
566 if (p_source_locator = 0) THEN
567 l_source_locator := null;
568 else
569 l_source_locator := p_source_locator;
570 end if;
571
572
573 if (p_lot = '') THEN
574 l_lot := null;
575 else
576 l_lot := p_lot;
577 end if;
578
579 if (p_revision = '') THEN
580 l_revision := null;
581 else
582 l_revision := p_revision;
583 end if;
584
585 if (p_serial_number = '') THEN
586 l_serial_number := null;
587 else
588 l_serial_number := p_serial_number;
589 end if;
590
594 l_dest_locator := p_dest_locator;
591 if (p_dest_locator = 0) THEN
592 l_dest_locator := null;
593 else
595 end if;
596
597 if (p_type_of_transaction = 'MOVE_ORDER') THEN
598 l_txn_type_id := 64;
599 else
600 l_txn_type_id := to_number(p_type_of_transaction);
601 end if;
602
603
604 -- CALL THE API
605 csp_transactions_pub.TRANSACT_MATERIAL
606 (p_api_version => 1.0
607 ,px_transaction_id => l_transaction_id
608 ,px_transaction_header_id => l_transaction_header_id
609 ,p_inventory_item_id => p_inventory_item_id
610 ,p_organization_id => p_organization_id
611 ,p_subinventory_code => p_source_sub
612 ,p_locator_id => l_source_locator
613 ,p_lot_number => l_lot
614 ,p_lot_expiration_date => null
615 ,p_revision => l_revision
616 ,p_serial_number => l_serial_number
617 ,p_to_serial_number => null
618 ,p_quantity => p_qty
619 ,p_uom => p_uom
620 ,p_source_id => null
621 ,p_source_line_id => p_line_id
622 ,p_transaction_type_id => l_txn_type_id
623 ,p_account_id => null
624 ,p_transfer_to_subinventory => p_dest_sub
625 ,p_transfer_to_locator => l_dest_locator
626 ,p_transfer_to_organization => p_dest_org_id
627 ,p_online_process_flag => TRUE
628 ,p_transaction_source_id => null
629 ,p_trx_source_line_id => null
630 ,p_transaction_source_name => null
631 ,p_waybill_airbill => p_waybill
632 ,p_shipment_number => p_ship_Nr
633 ,p_freight_code => p_freight_code
634 ,p_reason_id => null
635 ,p_transaction_reference => null
636 ,p_transaction_date => sysdate()
637 ,p_expected_delivery_date => to_date(p_exp_del_date,'YYYY-MM-DD')
638 ,x_return_status => l_return_status
639 ,x_msg_count => x_msg_count
640 ,x_msg_data => l_msg_data);
641
642 -- Bug 5465343: returning messages from TRANSACT_MATERIAL
643 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
644 x_return_status := l_return_status;
645 x_msg_count := l_msg_count;
646 x_msg_data := l_msg_data;
647 RAISE FND_API.G_EXC_ERROR;
648 ELSE
649 x_return_status := FND_API.G_RET_STS_SUCCESS ;
650 x_msg_count := 0;
651 x_msg_data := 'Success';
652 END IF;
653
654 EXCEPTION
655 WHEN OTHERS THEN
656 x_return_status := FND_API.G_RET_STS_ERROR;
657 END TRANSACT_MOVE_ORDER;
658
659
660
661 PROCEDURE RECEIVE_HEADER
662 (p_header_id in NUMBER,
663 x_return_status OUT NOCOPY VARCHAR2,
664 x_msg_count OUT NOCOPY NUMBER,
665 x_msg_data OUT NOCOPY VARCHAR2 )IS
666
667
668 receiveRec csp_receive_pvt.rcv_rec_tbl_type;
669 hdrrec csp_receive_pvt.rcv_hdr_rec_type;
670
671 --x_return_status varchar2(1);
672 --x_msg_count number;
673 --x_msg_data varchar2(2000);
674 x_message VARCHAR2(2000);
675 i number := 0;
676 l_api_name CONSTANT VARCHAR2(30) := 'CSFW:receive_shipments';
677
678 cursor c_hdr (v_header_id number ) IS
679 select * from csp_receive_headers_v
680 where RCV_SHIPMENT_HEADER_ID = v_header_id;
681 rcvhdrrec c_hdr%rowtype;
682
683 cursor c_lines (v_header_id number ) IS
684 select * from csp_receive_lines_v
685 where RCV_SHIPMENT_HEADER_ID = v_header_id;
686 linesrec c_lines%rowtype;
687
688 BEGIN
689
690 --First LETS GET THE HEADERS
691 OPEN c_hdr(p_header_id);
692 FETCH c_hdr into rcvhdrrec;
693 CLOSE c_hdr;
694
695 -- LETS POPULATE THE HEADER REC
696 hdrrec.header_interface_id := null;
697 hdrrec.group_id := null;
698 hdrrec.source_type_code := rcvhdrrec.source_type_code;
699 hdrrec.receipt_source_code := rcvhdrrec.receipt_source_code;
700 hdrrec.vendor_id := rcvhdrrec.vendor_id;
701 hdrrec.vendor_site_id := rcvhdrrec.vendor_site_id;
702 hdrrec.ship_to_org_id := rcvhdrrec.ship_to_organization_id;
703 hdrrec.rcv_shipment_num := rcvhdrrec.rcv_shipment_number;
704 hdrrec.receipt_header_id := rcvhdrrec.rcv_shipment_header_id;
705 hdrrec.receipt_num := null;
706 hdrrec.bill_of_lading := rcvhdrrec.bill_of_lading;
707 hdrrec.packing_slip := rcvhdrrec.packing_slip;
708 hdrrec.shipped_date := rcvhdrrec.shipped_date;
709 hdrrec.freight_carrier_code := rcvhdrrec.freight_carrier_code;
710 hdrrec.expected_receipt_date := rcvhdrrec.expected_receipt_date;
711 hdrrec.employee_id := null;
712 hdrrec.waybill_airbill_num := rcvhdrrec.waybill_airbill_num;
713 hdrrec.usggl_transaction_code := rcvhdrrec.ussgl_transaction_code;
714 hdrrec.processing_request_id := null;
715 hdrrec.customer_id := null;
716 hdrrec.customer_site_id := null;
717 --Now for this header id lets find all the lines
718
719 for linesrec in c_lines(p_header_id) loop
720 i := i + 1;
724 receiveRec(i).item_id := linesrec.item_id;
721 receiveRec(i).product_code := 'RCV';
722 receiveRec(i).source_type_code := linesrec.source_type_code;
723 receiveRec(i).order_type_code := linesrec.order_type_code;
725 receiveRec(i).item_revision := linesrec.item_revision;
726 receiveRec(i).item_category_id := linesrec.item_category_id;
727 receiveRec(i).item_description := linesrec.item_description;
728 receiveRec(i).from_organization_id := linesrec.from_organization_id;
729 receiveRec(i).ordered_qty := linesrec.ordered_qty;
730 receiveRec(i).ordered_uom := linesrec.ordered_uom;
731 receiveRec(i).serial_number_control_code := linesrec.serial_number_control_code;
732
733 if (linesrec.SERIAL_NUM IS NOT NULL ) then
734 receiveRec(i).transaction_quantity := 1;
735 else
736 receiveRec(i).transaction_quantity := linesrec.transaction_qty;
737 end if;
738
739 receiveRec(i).transaction_uom := linesrec.primary_uom;
740 receiveRec(i).rcv_shipment_header_id := linesrec.rcv_shipment_header_id;
741 receiveRec(i).rcv_shipment_line_id := linesrec.rcv_shipment_line_id;
742 receiveRec(i).po_header_id := linesrec.po_header_id;
743 receiveRec(i).po_line_id := linesrec.po_line_id;
744 receiveRec(i).po_line_location_id := linesrec.po_line_location_id;
745 receiveRec(i).req_line_id := linesrec.req_line_id;
746 receiveRec(i).oe_order_header_id := linesrec.oe_order_header_id;
747 receiveRec(i).oe_order_line_id := linesrec.oe_order_line_id;
748 receiveRec(i).receipt_source_code := linesrec.receipt_source_code;
749 receiveRec(i).po_release_id := linesrec.po_release_id;
750 receiveRec(i).po_distribution_id := linesrec.po_distribution_id;
751 receiveRec(i).lot_number := linesrec.lot_num;
752 receiveRec(i).lot_control_code := linesrec.lot_control_code;
753 receiveRec(i).vendor_id := linesrec.vendor_id;
754 receiveRec(i).vendor_site_id := linesrec.vendor_site_id;
755 receiveRec(i).fm_serial_number := linesrec.serial_num;
756 receiveRec(i).to_serial_number := linesrec.serial_num;
757 receiveRec(i).vendor_lot_number := linesrec.vendor_lot_num;
758 receiveRec(i).to_organization_id := linesrec.to_organization_id;
759 receiveRec(i).destination_subinventory := linesrec.destination_subinventory;
760 receiveRec(i).destination_type_code := linesrec.destination_type_code;
761 receiveRec(i).routing_id := linesrec.routing_id;
762 receiveRec(i).ship_to_location_id := linesrec.ship_to_location_id;
763 receiveRec(i).enforce_ship_to_location_code := linesrec.enforce_ship_to_location_code;
764 receiveRec(i).SET_OF_BOOKS_ID_SOB := linesrec.set_of_books_id_sob;
765 receiveRec(i).CURRENCY_CODE_SOB := linesrec.currency_code_sob;
766 receiveRec(i).lot_primary_quantity := linesrec.lot_primary_quantity;
767 receiveRec(i).lot_quantity := linesrec.lot_quantity;
768 receiveRec(i).locator_id := null;
769 receiveRec(i).interface_transaction_id := null;
770 receiveRec(i).transaction_interface_id := null;
771 receiveRec(i).header_interface_id := null;
772 receiveRec(i).group_id := null;
773 receiveRec(i).primary_quantity := null;
774 receiveRec(i).primary_uom := linesrec.primary_uom;
775 receiveRec(i).primary_uom_class := linesrec.primary_uom_class;
776 receiveRec(i).expiration_date := null;
777 receiveRec(i).status_id := null;
778 receiveRec(i).product_transaction_id := null;
779 receiveRec(i).att_exist := null;
780 receiveRec(i).update_mln := null;
781 receiveRec(i).description := null;
782 receiveRec(i).vendor_name := null;
783 receiveRec(i).supplier_lot_number := null;
784 receiveRec(i).origination_date := null;
785 receiveRec(i).date_code := null;
786 receiveRec(i).grade_code := null;
787 receiveRec(i).change_date := null;
788 receiveRec(i).maturity_date := null;
789 receiveRec(i).retest_date := null;
790 receiveRec(i).age := null;
791 receiveRec(i).item_size := null;
792 receiveRec(i).color := null;
793 receiveRec(i).volume := null;
794 receiveRec(i).volume_uom := null;
795 receiveRec(i).place_of_origin := null;
796 receiveRec(i).best_by_date := null;
797 receiveRec(i).length := null;
798 receiveRec(i).length_uom := null;
799 receiveRec(i).recycled_content := null;
800 receiveRec(i).thickness := null;
801 receiveRec(i).thickness_uom := null;
802 receiveRec(i).width := null;
803 receiveRec(i).width_uom := null;
804 receiveRec(i).curl_wrinkle_fold := null;
805 receiveRec(i).territory_code := null;
806 receiveRec(i).update_msn := null;
810 receiveRec(i).cycles_since_new := null;
807 receiveRec(i).vendor_serial_number := null;
808 receiveRec(i).parent_serial_number := null;
809 receiveRec(i).time_since_new := null;
811 receiveRec(i).time_since_overhaul := null;
812 receiveRec(i).cycles_since_overhaul := null;
813 receiveRec(i).time_since_repair := null;
814 receiveRec(i).cycles_since_repair := null;
815 receiveRec(i).time_since_visit := null;
816 receiveRec(i).cycles_since_visit := null;
817 receiveRec(i).time_since_mark := null;
818 receiveRec(i).cycles_since_mark := null;
819 receiveRec(i).number_of_repairs := null;
820 receiveRec(i).employee_id := null;
821 end loop;
822 --Now Lets Call The API
823 csp_receive_pvt.receive_shipments
824 (P_Api_Version_Number => 1.0
825 ,P_init_Msg_List => FND_API.G_TRUE
826 ,P_Commit => FND_API.G_TRUE
827 ,P_Validation_Level => FND_API.G_VALID_LEVEL_FULL
828 ,p_receive_hdr_rec => hdrrec
829 ,p_receive_rec_tbl => receiveRec
830 ,X_Return_Status => x_return_status
831 ,X_Msg_Count => x_msg_count
832 ,X_Msg_Data => x_msg_data);
833
834 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
835 x_return_status := x_return_status;
836 x_msg_data := x_Msg_Data;
837 if x_msg_count > 0 THEN
838 FOR l_counter IN 1 .. x_msg_count
839 LOOP
840 fnd_msg_pub.get
841 ( p_msg_index => l_counter
842 , p_encoded => FND_API.G_FALSE
843 , p_data => x_msg_data
844 , p_msg_index_out => x_msg_count
845 );
846 END LOOP;
847 --dbms_output.put_line(x_error_msg || 'API error');
848 end if;
849 END IF;
850
851 EXCEPTION
852 WHEN FND_API.G_EXC_ERROR THEN
853 x_return_status := FND_API.G_RET_STS_ERROR;
854 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
855 P_API_NAME => L_API_NAME
856 ,P_PKG_NAME => G_PKG_NAME
857 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
858 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
859 ,X_MSG_COUNT => X_MSG_COUNT
860 ,X_MSG_DATA => X_MESSAGE
861 ,X_RETURN_STATUS => X_RETURN_STATUS);
862 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
863 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
864 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
865 P_API_NAME => L_API_NAME
866 ,P_PKG_NAME => G_PKG_NAME
867 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
868 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
869 ,X_MSG_COUNT => X_MSG_COUNT
870 ,X_MSG_DATA => X_MESSAGE
871 ,X_RETURN_STATUS => X_RETURN_STATUS);
872 WHEN OTHERS THEN
873 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
874 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
875 P_API_NAME => L_API_NAME
876 ,P_PKG_NAME => G_PKG_NAME
877 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
878 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
879 ,X_MSG_COUNT => X_MSG_COUNT
880 ,X_MSG_DATA => X_MESSAGE
881 ,X_RETURN_STATUS => X_RETURN_STATUS);
882
883
884 END RECEIVE_HEADER;
885
886 END CSFW_ORDER_PARTS;