1 PACKAGE BODY rcv_express_sv AS
2 /* $Header: RCVTXEXB.pls 120.9.12010000.4 2008/10/09 19:13:41 vthevark ship $*/
3
4 /** Bug:5855096
5 Modified the Signature of the function val_rcv_trx_interface to
6 pass the value of X_txn_from_web and X_txn_from_wf which tells
7 whether the call is made from iProcurement page.
8 Reason:
9 -------
10 It is not possible to find out whether the call is made from
11 iProcurement page in val_rcv_trx_interface(), as the rti.transaction_type
12 is modifed in val_express_transactions() and set_trx_defaults()
13 before calling val_rcv_trx_interface().
14 */
15 FUNCTION val_rcv_trx_interface (
16 rcv_trx IN OUT NOCOPY rcv_transactions_interface%ROWTYPE,
17 X_txn_from_web IN BOOLEAN,--Bug 5855096
18 X_txn_from_wf IN BOOLEAN)--Bug 5855096
19 RETURN BOOLEAN;
20
21 PROCEDURE set_trx_defaults (
22 rcv_trx IN OUT NOCOPY rcv_transactions_interface%ROWTYPE);
23
24 PROCEDURE print_record (
25 rcv_trx IN OUT NOCOPY rcv_transactions_interface%ROWTYPE);
26
27 PROCEDURE insert_interface_errors ( rcv_trx IN OUT NOCOPY rcv_transactions_interface%ROWTYPE,
28 X_column_name IN VARCHAR2,
29 X_err_message IN VARCHAR2);
30
31 /*===========================================================================
32
33 PROCEDURE NAME: val_express_transactions
34
35 ===========================================================================*/
36
37 PROCEDURE val_express_transactions (X_group_id IN NUMBER,
38 X_rows_succeeded OUT NOCOPY NUMBER,
39 X_rows_failed OUT NOCOPY NUMBER) IS
40
41 CURSOR rcv_get_interface_rows IS
42 SELECT *
43 FROM rcv_transactions_interface
44 WHERE group_id = X_group_id
45 AND transaction_status_code in ( 'EXPRESS', 'CONFIRM' )
46 ORDER BY interface_transaction_id;
47
48 rcv_trx rcv_transactions_interface%ROWTYPE;
49 record_num NUMBER := 0;
50 rows_succeeded NUMBER := 0;
51 rows_failed NUMBER := 0;
52 delivery_rows_succeeded NUMBER := 0;
53 delivery_rows_failed NUMBER := 0;
54 done BOOLEAN := FALSE;
55 transaction_ok BOOLEAN := FALSE;
56 x_first_error BOOLEAN := TRUE;
57 X_progress VARCHAR2(4) := '000';
58 x_column_name po_interface_errors.column_name%type;
59 x_message_text fnd_new_messages.message_text%type;
60 x_message_name varchar2(30);
61 X_txn_from_web BOOLEAN := FALSE;
62 X_txn_from_wf BOOLEAN := FALSE;
63 X_language_code varchar2(4);
64 X_language_id number;
65 x_output_message fnd_new_messages.message_text%type := null;
66
67 l_matching_basis po_lines.matching_basis%type ;
68 /* The following variables were added for bug 2734333 */
69 x_base_currency_code varchar2(30) := ''; -- Bug 2734333
70 x_match_option varchar2(25);
71 v_rateDate DATE;
72 v_rate NUMBER;
73 v_sobid NUMBER;
74 --Bugfix5213454: Variable declaration for QA API.
75 l_qa_eval_result VARCHAR2(10);
76 l_qa_return_status VARCHAR2(5);
77 l_qa_msg_count NUMBER;
78 l_qa_msg_data VARCHAR2(2400);
79
80 /*
81 ** The get interface rows cursor is used to select all the rows that
82 ** were inserted during the express transaction from the form. We will
83 ** will loop through each of these rows to ensure that the row can
84 ** be transacted. For now the only feedback that the user will receive
85 ** is how many rows passed validation and how many failed. We should
86 ** add some kind of notification to this so the user can see which records
87 ** had problems.
88 **
89 ** This function assumes that if you are performing an
90 ** express direct receipt or an express delivery that you will insert all
91 ** the distributions into the transaction_interface table.
92 **
93 ** The process is read a record into memory, set defaults in memory,
94 ** validate trx, if transaction passes validation then write it out
95 ** to database with all the defaults and updated values, otherwise
96 ** delete it from the interface.
97 */
98
99 /* Modified this procedure to validate input from the Receive Orders
100 ** Web Page.
101 */
102
103 BEGIN
104
105 X_progress := '010';
106
107 /* Open the cursor for the fetch */
108 OPEN rcv_get_interface_rows;
109
110 /*
111 ** Loop:
112 ** Select the rows from the rcv_transactions_interface table
113 ** that will be transacted with this group_id
114 */
115 X_progress := '020';
116
117 LOOP
118
119 record_num := record_num + 1;
120
121 FETCH rcv_get_interface_rows INTO
122 rcv_trx;
123
124 EXIT WHEN rcv_get_interface_rows%NOTFOUND;
125
126 /*
127 ** DEBUG: Need a lock row routine for both the shipment line and
128 ** the line location. Will locking here collide with a potention
129 ** lock in the form.
130 ** Lock the shipment to prevent two users receiving against the
131 ** PO at the same time. If rows are not locked it is possible to
132 ** over-receive the PO.
133 */
134 X_progress := '030';
135
136 -- po_line_locations_sv.lock_row (line_location_id);
137 /* Bug 4773978: Added the following code for logging error messages
138 in PO_INTERFACE_ERRORS table. */
139 RCV_ERROR_PKG.initialize(rcv_trx.transaction_type,
140 rcv_trx.group_id,
141 rcv_trx.header_interface_id,
142 rcv_trx.interface_transaction_id);
143 /* Bug 4773978 end */
144
145 /*
146 ** Make sure that this is a express transaction that you're
147 ** processing
148 */
149 IF (rcv_trx.transaction_type IN ('EXPRESS RECEIPT','EXPRESS DIRECT','EXPRESS DELIVER','CONFIRM RECEIPT',
150 'CONFIRM RECEIPT(WF)')) then
151
152 /*
153 ** set any default values that might be required for this receipt
154 ** transaction. This should not be done if the transaction type
155 ** is CONFIRM RECEIPT as the quantity and uom would have been
156 ** entered by the user.We should not be defaulting in such a
157 ** case.
158 */
159 X_progress := '040';
160
161 if (rcv_trx.transaction_type = 'CONFIRM RECEIPT') then
162 x_txn_from_web := TRUE;
163 else
164 x_txn_from_web := FALSE;
165 end if;
166
167 -- bug 513848
168 -- Set flag to indicate from workflow
169 if (rcv_trx.transaction_type = 'CONFIRM RECEIPT(WF)') then
170 x_txn_from_wf := TRUE;
171 else
172 x_txn_from_wf := FALSE;
173 end if;
174
175
176 /* FPJ SERVICES.
177 * We support services only through ROI and IP. This part of
178 * the code should work only when it comes through IP. Through
179 * forms we cannot receive Service PO shipments. From IP, we
180 * will have po_line_id. We dont need to call set_trx_defaults
181 * and val_rcv_trx_interface for service based amounts since
182 * they are already done in IP.
183 */
184 /* R12 Complex work.
185 * We get matching_basis from the shipment level and not
186 * from line level.
187 */
188 begin
189 select nvl(matching_basis,'QUANTITY')
190 into l_matching_basis
191 from po_line_locations
192 where line_location_id =rcv_trx.po_line_location_id;
193 exception
194 /* Bug 3417961 : If the receipt_source_code is not PO then the above sql
195 will fetch null in l_matching_basis. This will cause set_trx_defaults()
196 and val_rcv_trx_interface() not getting called for RMA, Internal Order
197 and Intransit shipment Receipts. Defaulting l_matching_basis to 'QUANTITY'
198 */
199
200 when no_data_found then
201 l_matching_basis := 'QUANTITY';
202 end;
203
204 If (l_matching_basis <> 'AMOUNT') then --{
205
206 rcv_express_sv.set_trx_defaults (rcv_trx);
207 else
208 IF (rcv_trx.transaction_type = 'EXPRESS RECEIPT') THEN
209
210 rcv_trx.transaction_type := 'RECEIVE';
211 rcv_trx.auto_transact_code := 'RECEIVE';
212
213 ELSIF (rcv_trx.transaction_type = 'EXPRESS DIRECT') THEN
214
215 rcv_trx.transaction_type := 'RECEIVE';
216 rcv_trx.auto_transact_code := 'DELIVER';
217
218 ELSIF (rcv_trx.transaction_type = 'EXPRESS DELIVER') THEN
219
220 rcv_trx.transaction_type := 'DELIVER';
221 rcv_trx.auto_transact_code := '';
222
223 ELSIF (rcv_trx.transaction_type = 'CONFIRM RECEIPT') THEN
224
225 rcv_trx.transaction_type := 'RECEIVE';
226 rcv_trx.auto_transact_code := 'DELIVER';
227 X_txn_from_web := TRUE;
228
229 ELSIF (rcv_trx.transaction_type = 'CONFIRM RECEIPT(WF)') THEN
230
231 rcv_trx.transaction_type := 'RECEIVE';
232 rcv_trx.auto_transact_code := 'DELIVER';
233 X_txn_from_web := FALSE;
234
235 -- bug 513848
236 X_txn_from_wf := TRUE;
237
238 END IF;
239
240 rcv_trx.processing_status_code := 'PENDING';
241 rcv_trx.transaction_status_code := 'PENDING';
242 end if; --}
243
244 /* Bug 2734333 - Getting the currency conversion rate for the receipt creation date when the match option is Receipt */
245
246 IF (rcv_trx.source_document_code = 'PO') THEN
247
248 /* Added the following pl/sql block to get the functional currency - Bug 2734333 */
249
250 /* <R12 MOAC START>
251 ** Moved the following Begin-End block into the LOOP and
252 ** added the predicate for Org_id in the where clause
253 */
254
255 BEGIN
256 SELECT GSB.currency_code,FSP.set_of_books_id
257 INTO x_base_currency_code,
258 v_sobid
259 FROM FINANCIALS_SYSTEM_PARAMETERS FSP,
260 GL_SETS_OF_BOOKS GSB
261 WHERE FSP.set_of_books_id = GSB.set_of_books_id
262 AND FSP.org_id = rcv_trx.org_id;
263 EXCEPTION
264 WHEN OTHERS THEN
265 RAISE;
266 END;
267
268 --<R12 MOAC END>
269
270 IF (rcv_trx.currency_code <> nvl(x_base_currency_code,rcv_trx.currency_code)) then
271
272 /* getting the match option from the PO */
273
274 select match_option
275 into x_match_option
276 from po_line_locations
277 where line_location_id = rcv_trx.po_line_location_id;
278
279 /* bug 4356092 - currency conversion rate code was incorrect */
280 IF (x_match_option = 'R') THEN
281 /* For enter receipts form */
282 IF (rcv_trx.parent_transaction_id IS NULL) THEN
283 IF (rcv_trx.currency_conversion_type = 'User') THEN
284 rcv_trx.currency_conversion_date := rcv_trx.creation_date;
285 ELSE --rcv_trx.currency_converstion_type <> 'User'
286 BEGIN
287 /* attempt to to get rate at creation time */
288 v_rate := gl_currency_api.get_rate(v_sobid,
289 rcv_trx.currency_code,
290 rcv_trx.creation_date,
291 rcv_trx.currency_conversion_type
292 );
293 /* if successfull then set the currency_conversion_date to the date used above */
294 rcv_trx.currency_conversion_date := rcv_trx.creation_date;
295 EXCEPTION
296 WHEN OTHERS THEN
297 /* Bug 4773978: Removed the code to get currency conversion rate using currency conversion date
298 defined in PO, if currency conversion rate is not defined for the receipt date.
299 We have to error out the transaction in PO_INTERFACE_ERRORS table for the POs created
300 with invoice match set to 'Receipts', if currency conversion rate is not defined for
301 the receipt date. */
302 v_rate := fnd_api.g_miss_num;
303 /* Bug 4773978 end */
304 END;
305
306 rcv_trx.currency_conversion_rate := v_rate;
307 END IF; -- conversion type is not user
308 ELSE
309 /* For receiving transactions form */
310 SELECT currency_conversion_date,
311 currency_conversion_rate
312 INTO v_ratedate,
313 v_rate
314 FROM rcv_transactions
315 WHERE transaction_id = rcv_trx.parent_transaction_id;
316
317 rcv_trx.currency_conversion_date := v_ratedate;
318 rcv_trx.currency_conversion_rate := v_rate;
319 END IF; -- parent transaction id
320 END IF; -- match option is R
321
322 END IF; --currency code
323
324 END IF; -- source document code is po
325
326 /* Validate that the transaction can be express received */
327
328 If (l_matching_basis <> 'AMOUNT') then
329 transaction_ok := rcv_express_sv.val_rcv_trx_interface (rcv_trx,X_txn_from_web,X_txn_from_wf);--Bug 5855096
330 else
331 /* Set transaction_ok to TRUE since we are not calling
332 * val_rcv_trx_interface.
333 */
334 transaction_ok := TRUE;
335 end if;
336
337 /*
338 ** If the transaction passes all validation requirements
339 ** then go ahead and process the rows so that it gets picked
340 ** up by the transaction procesor
341 */
342 IF (transaction_ok) THEN
343
344 X_progress := '050';
345
346 /*
347 ** Set all the columns for this receipt transaction row`
348 ** so that it can be picked up by the transaction processor
349 */
350 --Bugfix5213454 Start: Called QA API after RTI record validation.
351
352 /* Bugfix 5855096 : Modified the condtion rcv_trx.auto_transact_code <> 'CUSTOMER'
353 with rcv_trx.receipt_source_code = 'VENDOR'*/
354
355 IF (rcv_trx.routing_header_id = 2 AND
356 rcv_trx.receipt_source_code = 'VENDOR' AND --Bug 5855096
357 rcv_trx.quantity > 0) THEN
358
359 QA_SKIPLOT_RCV_GRP.EVALUATE_LOT
360 (p_api_version => 1.0,
361 p_init_msg_list => NULL,
362 p_commit => 'T',
363 p_validation_level => NULL,
364 p_interface_txn_id => rcv_trx.interface_transaction_id,
365 p_organization_id => rcv_trx.to_organization_id,
366 p_vendor_id => rcv_trx.vendor_id,
367 p_vendor_site_id => rcv_trx.vendor_site_id,
368 p_item_id => rcv_trx.item_id,
369 p_item_revision => rcv_trx.item_revision,
370 p_item_category_id => rcv_trx.category_id,
371 p_project_id => rcv_trx.project_id,
372 p_task_id => rcv_trx.task_id,
373 p_manufacturer_id => NULL,
374 p_source_inspected => NULL,
375 p_receipt_qty => rcv_trx.quantity,
376 p_receipt_date => rcv_trx.transaction_date,
377 p_primary_uom => rcv_trx.primary_unit_of_measure,
378 p_transaction_uom => rcv_trx.unit_of_measure,
379 p_po_header_id => rcv_trx.po_header_id,
380 p_po_line_id => rcv_trx.po_line_id,
381 p_po_line_location_id => rcv_trx.po_line_location_id,
382 p_po_distribution_id => rcv_trx.po_distribution_id,
383 x_evaluation_result => l_qa_eval_result,
384 x_return_status => l_qa_return_status,
385 x_msg_count => l_qa_msg_count,
386 x_msg_data => l_qa_msg_data);
387
388 IF l_qa_return_status <> 'S' THEN
389 l_qa_eval_result := 'INSPECT';
390 END IF;
391
392 IF l_qa_eval_result = 'STANDARD' THEN
393 rcv_trx.routing_header_id := 1;
394 END IF;
395 END IF;
396 --Bugfix5213454 End: Called QA API after RTI record validation.
397
398 rcv_trx_interface_trx_upd_pkg.update_rcv_transaction (rcv_trx);
399
400 /*
401 ** If the row passes all validation then set the succeed
402 ** count appropriately
403 */
404
405 -- if (x_txn_from_web) then
406 -- rcv_express_sv.print_record (rcv_trx);
407 -- end if;
408
409 rows_succeeded := rows_succeeded + 1;
410
411 ELSE
412
413 if (x_txn_from_web) then
414
415
416 X_progress := 70;
417
418 --Bug 5230922. Changed the where clause. Previously it was matching
419 --po.interface_transaction_id = rcv_trx.interface_transaction_id
420 --that was wrong because rcv_trx.interface_transaction_id would be
421 --stored as interface_line_id in the table po_interface_errors
422 SELECT column_name, error_message_name
423 INTO x_column_name, x_message_name
424 FROM po_interface_errors po
425 WHERE po.interface_line_id = rcv_trx.interface_transaction_id; --Bug 5230922
426
427 /* Get the translated message for the
428 ** column that failed validation
429 */
430
431 X_message_text := fnd_message.get_string('PO',x_message_name);
432
433 --Bug#2869368.The error in htp call was causing error message not
434 --displayed on the browser for the receipt done through SSP.
435 --Added exception to handle this situation.
436 Begin
437 if (x_first_error) then
438 x_output_message := fnd_message.get_string('PO','RCV_CONFIRM_ERRORS');
439 htp.teletype(x_output_message);
440 htp.nl;
441 htp.teletype('=============================================================================');
442 htp.nl;
443 x_first_error := FALSE;
444 end if;
445
446 htp.teletype(x_column_name); htp.nl;
447 htp.teletype(x_message_text); htp.nl;
448 htp.teletype('------------'); htp.nl;
449
450 Exception
451 When others then null;
452 End;
453
454
455 end if;
456
457 /*
458 ** Bug 3438171 - Don't delete the transaction, update it.
459 ** If the transactions fails validation then update it
460 ** in the interface to error
461 */
462 rcv_trx.processing_status_code := 'COMPLETED';
463 rcv_trx.transaction_status_code := 'ERROR';
464 rcv_trx_interface_trx_upd_pkg.update_rcv_transaction (rcv_trx);
465
466
467 /*
468 ** If the row fails a validation step then set the failure
469 ** count appropriately
470 */
471 rows_failed := rows_failed + 1;
472
473 END IF;
474
475 END IF;
476
477 END LOOP;
478
479 X_rows_succeeded := rows_succeeded;
480 X_rows_failed := rows_failed;
481
482 /* Bug 4891693 fixed. deleting records from RTI which has quantity = 0 */
483 BEGIN
484 DELETE FROM rcv_transactions_interface
485 WHERE group_id = X_group_id
486 AND quantity = 0;
487 EXCEPTION
488 WHEN OTHERS THEN
489 NULL;
490 END;
491
492 RETURN;
493
494 EXCEPTION
495 WHEN OTHERS THEN
496 po_message_s.sql_error('val_express_transactions', X_progress, sqlcode);
497 RAISE;
498
499 END val_express_transactions;
500
501 /*===========================================================================
502
503 FUNCTION NAME: val_rcv_trx_interface
504
505 ===========================================================================*/
506
507 FUNCTION val_rcv_trx_interface (rcv_trx IN OUT NOCOPY rcv_transactions_interface%ROWTYPE,
508 X_txn_from_web IN BOOLEAN,--Bug 5855096
509 X_txn_from_wf IN BOOLEAN)--Bug 5855096
510 RETURN BOOLEAN IS
511
512 X_progress VARCHAR2(4) := '000';
513 under_lot_control BOOLEAN := FALSE;
514 under_serial_control BOOLEAN := FALSE;
515 valid_receiving_controls NUMBER := 0;
516 valid_deliver_dest NUMBER := 0;
517 valid_wip_info NUMBER :=0;
518 X_column_name VARCHAR2(30);
519 X_err_message VARCHAR2(240);
520 x_sob_id org_organization_definitions.set_of_books_id%type;
521 x_is_val_period BOOLEAN := FALSE; -- bug 626224
522 x_item_name VARCHAR2(50) := ''; --bug 2706571
523 x_stock_enabled_flag VARCHAR2(2) := ''; --bug 2706571
524 x_allow_express_delivery_flag VARCHAR2(2) := ''; --bug 5498095
525 BEGIN
526
527 /* Fix for Bug 5498095
528 If the item is not express allowed in the destination organization
529 for an inventory destination receipt then an error message will be
530 inserted into po_interface errors.
531 */
532
533 /* Bug: 5855096
534 We neeed to by pass the validation for allow_express_delivery flag
535 mentioned at item level for the transactions made through the
536 web page(iProcurement).
537 For the transactions made through iProcurement possible
538 transaction_types are 'CONFIRM RECEIPT' and 'CONFIRM RECEIPT(WF)'
539 and the variables X_txn_from_web or X_txn_from_wf set to TRUE.
540
541 Changed nvl(msi.allow_express_delivery_flag,'N') to
542 nvl(msi.allow_express_delivery_flag,'U'), to bypass the
543 allow_express_delivery flag validation if that flag value
544 is to NULL in the Master Items form.
545 */
546 if ( not(X_txn_from_web or X_txn_from_wf) ) then--Bug 5855096
547 if (nvl(rcv_trx.item_id,0) <> 0) then
548 select nvl(msi.allow_express_delivery_flag,'U') --Bug 5855096
549 into x_allow_express_delivery_flag
550 from mtl_system_items msi
551 where msi.inventory_item_id =rcv_trx.item_id
552 and msi.organization_id = rcv_trx.to_organization_id;
553
554 if x_allow_express_delivery_flag = 'N' then
555 X_column_name := 'ITEM_NUMBER: ' || x_item_name;
556 X_err_message := 'PO_RI_INVALID_EXPRESS_ITEM';
557
558 rcv_express_sv.insert_interface_errors(rcv_trx,
559 X_column_name,
560 X_err_message);
561 return FALSE;
562 end if;
563 end if; /* fix end for Bug 5498095 */
564 end if; /* fix end for Bug 5855096 */
565
566 /* Fix for bug 2706571
567 If the item is not stock enabled in the destination organization
568 for an inventory destination receipt then an error message will be
569 inserted into po_interface errors.
570 */
571
572 if ( (rcv_trx.destination_type_code = 'INVENTORY' or
573 (rcv_trx.source_document_code = 'RMA' and
574 (rcv_trx.transaction_type = 'DELIVER' or rcv_trx.auto_transact_code = 'DELIVER'))) and
575 nvl(rcv_trx.item_id,0) <> 0) then
576
577 select msi.segment1,
578 msi.stock_enabled_flag
579 into x_item_name,
580 x_stock_enabled_flag
581 from mtl_system_items msi
582 where msi.inventory_item_id = rcv_trx.item_id
583 and msi.organization_id = rcv_trx.to_organization_id;
584
585
586 if nvl(x_stock_enabled_flag,'N') = 'N' then
587
588 X_column_name := 'ITEM_NUMBER: ' || x_item_name;
589 X_err_message := 'PO_RI_INVALID_DEST_ORG_ITEM';
590
591 rcv_express_sv.insert_interface_errors(rcv_trx,
592 X_column_name,
593 X_err_message);
594
595 return FALSE;
596 end if;
597
598 end if; /* End of bug 2706571 */
599
600 if (rcv_trx.transaction_date > sysdate) then
601
602 X_column_name := 'TRANSACTION_DATE';
603 X_err_message := 'RCV_TRX_FUTURE_DATE_NA';
604
605 rcv_express_sv.insert_interface_errors(rcv_trx,
606 X_column_name,
607 X_err_message);
608
609 return FALSE;
610
611 end if;
612
613 --Perf bugfix 5220058
614 select GSOB.SET_OF_BOOKS_ID
615 into x_sob_id
616 from
617 GL_SETS_OF_BOOKS GSOB,
618 HR_ORGANIZATION_INFORMATION HOI
619 where
620 HOI.ORGANIZATION_ID = rcv_trx.to_organization_id
621 AND ( HOI.ORG_INFORMATION_CONTEXT || '') ='Accounting Information'
622 AND HOI.ORG_INFORMATION1 = TO_CHAR(GSOB.SET_OF_BOOKS_ID);
623
624 -- bug 626224 if period is not defined, just say it's not open
625
626 BEGIN
627 x_is_val_period := PO_DATES_S.val_open_period(rcv_trx.transaction_date,x_sob_id,'SQLGL',
628 rcv_trx.to_organization_id);
629 EXCEPTION
630 WHEN OTHERS THEN
631 x_is_val_period := FALSE;
632 END;
633
634 if (not x_is_val_period) then
635
636 X_column_name := 'TRANSACTION_DATE';
637 X_err_message := 'PO_PO_ENTER_OPEN_GL_DATE';
638
639 rcv_express_sv.insert_interface_errors(rcv_trx,
640 X_column_name,
641 X_err_message);
642
643 return FALSE;
644
645 end if;
646
647 BEGIN
648 x_is_val_period := PO_DATES_S.val_open_period(rcv_trx.transaction_date,x_sob_id,'INV',
649 rcv_trx.to_organization_id);
650 EXCEPTION
651 WHEN OTHERS THEN
652 x_is_val_period := FALSE;
653 END;
654
655 if (not x_is_val_period) then
656
657 X_column_name := 'TRANSACTION_DATE';
658 X_err_message := 'PO_INV_NO_OPEN_PERIOD';
659
660 rcv_express_sv.insert_interface_errors(rcv_trx,
661 X_column_name,
662 X_err_message);
663
664 return FALSE;
665 end if;
666
667 BEGIN
668 x_is_val_period := PO_DATES_S.val_open_period(rcv_trx.transaction_date,x_sob_id,'PO',
669 rcv_trx.to_organization_id);
670 EXCEPTION
671 WHEN OTHERS THEN
672 x_is_val_period := FALSE;
673 END;
674
675 if (not x_is_val_period) then
676
677 X_column_name := 'TRANSACTION_DATE';
678 X_err_message := 'PO_PO_ENTER_OPEN_GL_DATE';
679
680 rcv_express_sv.insert_interface_errors(rcv_trx,
681 X_column_name,
682 X_err_message);
683
684 return FALSE;
685 end if;
686
687 /*
688 ** Check for 0 transaction quantity. If it's 0 then fail and delete it.
689 */
690 /* Bug 4891693 fixed.
691 No need for validation of zero quantity RTI records since we
692 are deleting them down the line.
693
694 IF (rcv_trx.quantity = 0) THEN
695
696 X_column_name := 'QUANTITY';
697 X_err_message := 'PO_ALL_ENTER_VALUE_GT_ZERO';
698
699 rcv_express_sv.insert_interface_errors(rcv_trx,
700 X_column_name,
701 X_err_message);
702
703
704 return FALSE;
705
706 END IF;
707 */
708
709 /*
710 ** check for valid currency conversion rate. bug 4356092
711 */
712 IF (rcv_trx.currency_conversion_rate = fnd_api.g_miss_num) THEN
713 /*
714 ** Push the failed Row into the interface_errors table
715 ** so we can provide feedback to the user.
716 */
717 X_column_name := 'CURRENCY_CONVERSION_RATE';
718 X_err_message := 'PO_CPO_NO_RATE_FOR_DATE';
719
720 rcv_express_sv.insert_interface_errors(rcv_trx,
721 X_column_name,
722 X_err_message);
723
724
725 return FALSE;
726
727 END IF;
728
729
730 /*
731 ** Check for a receipt transaction. If it is then do all the validation
732 ** required to perform a receipt ncluding:
733 **
734 ** 1. Does the receipt match the receiving controls like date tolerances
735 ** 2. Does the receipt have the required revision value
736 ** 3. If this is an express receipt make sure there are no pending
737 ** transactions for this line.
738 */
739 IF (rcv_trx.transaction_type = 'RECEIVE') THEN
740 /*
741 ** Check the receiving controls for this transaction.
742 */
743 X_progress := '100';
744
745 -- htp.p ('validating receiving controls'); -- htp.nl;
746
747 valid_receiving_controls := rcv_transactions_sv.val_receiving_controls (
748 rcv_trx.transaction_type,
749 rcv_trx.auto_transact_code,
750 rcv_trx.expected_receipt_date,
751 rcv_trx.transaction_date,
752 rcv_trx.routing_header_id,
753 rcv_trx.po_line_location_id,
754 rcv_trx.item_id,
755 rcv_trx.vendor_id,
756 rcv_trx.to_organization_id);
757
758 if valid_receiving_controls<> 0 THEN
759
760 -- htp.p ('receiving controls validation failed'); -- htp.nl;
761
762 /*
763 ** Push the failed Row into the interface_errors table
764 ** so we can provide feedback to the user.The variable valid
765 ** _receiving_controls will hold a 1 if the receipt date
766 ** exceeded tolerance . It will have a value of 2 if the
767 ** routing information was incorrect.If the routing info
768 ** fails, the only way the user can receive is thru the
769 ** 10sc apps. We do not have Routing info as enterable fields
770 ** on the Receive Orders Web Page yet.
771 */
772 if valid_receiving_controls = 1 then
773 X_column_name := 'TRANSACTION_DATE';
774 X_err_message := 'RCV_ALL_DATE_OUT_OF_RANGE';
775 elsif valid_receiving_controls = 2 then
776 X_column_name := 'ROUTING_HEADER_ID';
777 X_err_message := 'RCV_ROUTING_OVERRIDE_NA';
778
779 /* Bug 3724862 : Error handling for dropship case */
780 elsif valid_receiving_controls = 3 then
781 X_column_name := 'ROUTING_HEADER_ID';
782 X_err_message := 'RCV_DROPSHIP_DIRECT_ONLY';
783 end if;
784
785 rcv_express_sv.insert_interface_errors(rcv_trx,
786 X_column_name,
787 X_err_message);
788
789 RETURN (FALSE);
790
791 END IF;
792
793 -- htp.p ('validating item rev controls'); -- htp.nl;
794 /*
795 ** Check that the item rev control matches is satisfied
796 */
797 X_progress := '110';
798 IF (NOT po_items_sv2.val_item_rev_controls (
799 rcv_trx.transaction_type,
800 rcv_trx.auto_transact_code,
801 rcv_trx.po_line_location_id,
802 rcv_trx.shipment_line_id,
803 rcv_trx.to_organization_id,
804 rcv_trx.destination_type_code,
805 rcv_trx.item_id,
806 rcv_trx.item_revision)) THEN
807
808 -- htp.p ('item rev controls validation'); -- htp.nl;
809
810 X_column_name := 'ITEM_REVISION';
811 X_err_message := 'RCV_ITEM_IN_REV_CONTROL';
812
813 rcv_express_sv.insert_interface_errors(rcv_trx,
814 X_column_name,
815 X_err_message);
816
817 RETURN (FALSE);
818
819 END IF;
820
821 /*
822 ** Bug 3417961 : Item revision needs to be validated for an Express Receipt
823 ** against an Internal Order, RMA and Inventory Inter Org Transfers.
824 */
825
826 IF ( rcv_trx.receipt_source_code IN ('INTERNAL ORDER','CUSTOMER','INVENTORY') ) THEN
827
828 DECLARE
829 l_valid_revision NUMBER := NULL;
830 l_item_rev_control NUMBER := NULL;
831 BEGIN
832
833 SELECT msi.revision_qty_control_code
834 INTO l_item_rev_control
835 FROM mtl_system_items_kfv msi
836 WHERE msi.inventory_item_id = rcv_trx.item_id
837 AND msi.organization_id = rcv_trx.to_organization_id;
838
839 IF ( nvl(l_item_rev_control,1) = 2 ) THEN
840
841 SELECT count(*)
842 INTO l_valid_revision
843 FROM mtl_item_revisions
844 WHERE inventory_item_id = rcv_trx.item_id
845 AND organization_id = rcv_trx.to_organization_id
846 AND revision = rcv_trx.item_revision;
847
848 IF ( l_valid_revision = 0 ) THEN
849
850 IF ( rcv_trx.receipt_source_code = 'CUSTOMER') THEN
851 X_column_name := 'ITEM_REVISION';
852 X_err_message := 'PO_RI_INVALID_ITEM_REVISION';
853 ELSE
854 X_column_name := 'ITEM_REVISION';
855 X_err_message := 'PO_RI_INVALID_DEST_REVISION';
856 END IF;
857
858 rcv_express_sv.insert_interface_errors(rcv_trx,
859 X_column_name,
860 X_err_message);
861
862 RETURN (FALSE);
863 END IF;
864 END IF;
865 END;
866 END IF; -- if receipt_source_code = 'INTERNAL ORDER'
867
868 /* End of Bug 3417961 */
869
870 IF (rcv_trx.auto_transact_code = 'RECEIVE') THEN
871
872 -- htp.p ('validating ship to location id'); -- htp.nl;
873 /*
874 ** If this is a express receipt then make sure
875 ** you have a ship_to_location_id
876 */
877 IF (rcv_trx.ship_to_location_id IS NULL OR
878 rcv_trx.ship_to_location_id = 0) THEN
879
880 X_column_name := 'SHIP_TO_LOCATION_ID';
881 X_err_message := 'RCV_SHIP_TO_LOC_NA';
882
883 rcv_express_sv.insert_interface_errors(rcv_trx,
884 X_column_name,
885 X_err_message);
886 RETURN FALSE;
887
888 END IF;
889
890 END IF; -- (rcv_trx.auto_transact_code = 'RECEIVE')
891
892 END IF; -- (rcv_trx.transaction_type = 'RECEIVE')
893
894 IF (rcv_trx.auto_transact_code = 'DELIVER' OR
895 rcv_trx.transaction_type = 'DELIVER') THEN
896
897 -- htp.p ('validating destination info : '); -- htp.nl;
898
899 /*
900 ** Check that the destination information is valid
901 */
902 X_progress := '130';
903 valid_deliver_dest := rcv_transactions_sv.val_deliver_destination (
904 rcv_trx.to_organization_id,
905 rcv_trx.item_id,
906 rcv_trx.destination_type_code,
907 rcv_trx.deliver_to_location_id,
908 rcv_trx.subinventory);
909
910 -- htp.p ('The var Valid_Deliver_dest is :' || to_char(valid_deliver_dest)); -- htp.nl;
911 IF valid_deliver_dest <> 0 THEN
912
913 -- htp.p ('destination info validation failed: '); -- htp.nl;
914
915 if valid_deliver_dest = 10 then
916
917 /* The Destination Org is not defined */
918
919 X_column_name := 'TO_ORGANIZATION_ID';
920 X_err_message := 'RCV_DEST_ORG_NA';
921
922 elsif valid_deliver_dest = 20 then
923
924 /* The Deliver To Location is not defined */
925
926 X_column_name := 'DELIVER_TO_LOCATION_ID';
927 X_err_message := 'RCV_DELIVER_TO_LOC_NA';
928
929 elsif valid_deliver_dest = 30 then
930
931 /* The deliver to Location is invalid */
932
933 X_column_name := 'DELIVER_TO_LOCATION_ID';
934 X_err_message := 'RCV_DELIVER_TO_LOC_INVALID';
935
936 elsif valid_deliver_dest = 40 then
937
938 /* The Sub is not defined */
939
940 X_column_name := 'DESTINATION_SUBINVENTORY';
941 X_err_message := 'RCV_DEST_SUB_NA';
942
943 elsif valid_deliver_dest = 50 then
944
945 /* The Sub is invalid */
946 X_column_name := 'DESTINATION_SUBINVENTORY';
947 X_err_message := 'RCV_DEST_SUB_INVALID';
948
949 elsif valid_deliver_dest = 60 then
950
951 /* Destination Type Code is Invalid */
952 X_column_name := 'DESTINATION_TYPE_CODE';
953 X_err_message := 'RCV_DEST_TYPE_CODE_INVALID';
954
955 end if;
956 rcv_express_sv.insert_interface_errors(rcv_trx,
957 X_column_name,
958 X_err_message);
959 RETURN (FALSE);
960
961 END IF;
962
963
964 /*
965 ** The required info for inventory is the
966 ** subinventory, and locator and that the item not be under
967 ** lot or serial control
968 */
969 /*
970 ** Check if an item/org is under lot and/or
971 ** serial control.
972 */
973 X_progress := '140';
974
975
976 /* Bug# 2166549.We should not validate for expense items which are
977 * lot/serial controlled even when express functionality is used.
978 */
979
980 IF (rcv_trx.destination_type_code <> 'EXPENSE') THEN
981
982 IF rcv_trx.use_mtl_lot in (2, 5) THEN
983
984 -- htp.p ('lot control validation failed: '); -- htp.nl;
985 X_column_name := 'USE_MTL_LOT';
986 X_err_message := 'RCV_MTL_LOT_CONTROL_FAIL';
987 rcv_express_sv.insert_interface_errors(rcv_trx,
988 X_column_name,
989 X_err_message);
990 RETURN (FALSE);
991
992 END IF;
993
994 END IF;
995
996
997 IF (rcv_trx.destination_type_code <> 'EXPENSE') THEN
998
999 IF rcv_trx.use_mtl_serial in (2, 5) THEN
1000
1001 -- htp.p ('serial control validation failed: '); -- htp.nl;
1002 X_column_name := 'USE_MTL_SERIAL';
1003 X_err_message := 'RCV_MTL_SERIAL_CONTROL_FAIL';
1004 rcv_express_sv.insert_interface_errors(rcv_trx,
1005 X_column_name,
1006 X_err_message);
1007
1008 RETURN (FALSE);
1009
1010 END IF;
1011
1012 END IF;
1013
1014 -- htp.p ('validating locator control : '); -- htp.nl;
1015
1016 /*
1017 ** Check that a locator is not required for this transaction since a user
1018 ** would never have the opportunity to enter one. The only way a
1019 ** delivery to an inventory destination could work is if a default
1020 ** locator was defined for the subinventory
1021 */
1022 IF (rcv_trx.destination_type_code = 'INVENTORY') THEN
1023
1024 X_progress := '150';
1025 IF (NOT po_subinventories_s.val_locator_control (
1026 rcv_trx.to_organization_id,
1027 rcv_trx.item_id,
1028 rcv_trx.subinventory,
1029 rcv_trx.locator_id)) THEN
1030
1031 X_column_name := 'LOCATOR_ID';
1032 X_err_message := 'RCV_LOCATOR_CONTROL_INVALID';
1033 rcv_express_sv.insert_interface_errors(rcv_trx,
1034 X_column_name,
1035 X_err_message);
1036
1037 -- htp.p ('locator control validation failed: '); -- htp.nl;
1038
1039 RETURN (FALSE);
1040
1041 END IF;
1042
1043 ELSE
1044
1045 /* BUG: 5435353
1046 ** Mark the locator id as null if it is not an inventory transaction
1047 */
1048 rcv_trx.locator_id := NULL;
1049
1050 END IF;
1051
1052 -- htp.p ('validating wip info : '); -- htp.nl;
1053
1054 /*
1055 ** if this is a shop floor destination then make sure that the job
1056 ** information is still valid
1057 */
1058 IF (rcv_trx.destination_type_code = 'SHOP FLOOR') THEN
1059
1060 X_progress := '160';
1061
1062 valid_wip_info := rcv_transactions_sv.val_wip_info (
1063 rcv_trx.to_organization_id,
1064 rcv_trx.wip_entity_id,
1065 rcv_trx.wip_operation_seq_num,
1066 rcv_trx.wip_resource_seq_num,
1067 rcv_trx.wip_line_id,
1068 rcv_trx.wip_repetitive_schedule_id,
1069 rcv_trx.po_line_id); -- bug 2619164
1070
1071 IF (valid_wip_info <> 0) THEN
1072
1073 if valid_wip_info = 10 then
1074
1075 X_column_name := 'TO_ORGANIZATION_ID';
1076 X_err_message := 'RCV_DEST_ORG_NA';
1077
1078 elsif valid_wip_info = 20 then
1079
1080 X_column_name := 'WIP_ENTITY_ID';
1081 X_err_message := 'RCV_WIP_ENTITY_ID_NA';
1082
1083 elsif valid_wip_info = 30 then
1084
1085 X_column_name := 'WIP_OP_SEQ_NUM';
1086 X_err_message := 'RCV_WIP_OP_SEQ_NUM_NA';
1087
1088 elsif valid_wip_info = 40 then
1089
1090 X_column_name := 'WIP_RES_SEQ_NUM';
1091 X_err_message := 'RCV_WIP_RES_SEQ_NUM_NA';
1092
1093 elsif valid_wip_info = 50 then
1094
1095 X_column_name := 'WIP_REPETITIVE_SCHEDULE_ID';
1096 X_err_message := 'RCV_WIP_REP_SCH_JOB_NOT_OPEN';
1097
1098 elsif valid_wip_info = 60 then
1099
1100 X_column_name := '_WIP_ENTITY_ID';
1101 X_err_message := 'RCV_WIP_JOB_NOT_OPEN';
1102
1103 end if;
1104
1105 rcv_express_sv.insert_interface_errors(rcv_trx,
1106 X_column_name,
1107 X_err_message);
1108
1109 -- htp.p ('wip info validation failed : '); -- htp.nl;
1110
1111 RETURN (FALSE);
1112
1113 END IF;
1114
1115 END IF;
1116
1117 /*
1118 ** DEBUG: This needs to be moved out of this function since it's not
1119 ** generic unless we want to pass this function an express flag and
1120 ** then only execute this check if its express.
1121 ** If you're doing an express direct/deliver then make sure that there
1122 ** are no receipts that have not been delivered. Otherwise we
1123 ** won't be able to distribute them properly.
1124 */
1125
1126 -- htp.p ('validating pending delivery transactions'); -- htp.nl;
1127
1128 /*
1129 ** If this is a receipt transaction. Then check that there are is no
1130 ** receipt supply for this line location
1131 **
1132 */
1133 IF (rcv_trx.source_document_code = 'PO' AND
1134 (rcv_trx.transaction_type = 'RECEIVE' OR
1135 rcv_trx.auto_transact_code = 'RECEIVE')) THEN
1136
1137 X_progress := '120';
1138 IF (NOT rcv_transactions_sv.val_pending_receipt_trx (
1139 rcv_trx.po_line_location_id,
1140 rcv_trx.group_id)) THEN
1141
1142 X_column_name := 'PO_LINE_LOCATION_ID';
1143 X_err_message := 'RCV_PENDING_DELIVERY_TXN_FAILED';
1144 rcv_express_sv.insert_interface_errors(rcv_trx,
1145 X_column_name,
1146 X_err_message);
1147 -- htp.p ('pending delivery transactions validation failed'); -- htp.nl;
1148
1149 RETURN (FALSE);
1150
1151 END IF; -- (NOT rcv_transactions_sv.val_pending_transactions)
1152
1153 END IF; -- (rcv_trx.source_document_code = 'PO')
1154
1155 END IF; -- (rcv_trx.auto_transact_code = 'DELIVER' ...)
1156
1157 RETURN TRUE;
1158
1159 EXCEPTION
1160 WHEN OTHERS THEN
1161 po_message_s.sql_error('val_rcv_trx_interface', x_progress, sqlcode);
1162 RAISE;
1163
1164 END val_rcv_trx_interface;
1165
1166 /*===========================================================================
1167
1168 PROCEDURE NAME: set_trx_defaults
1169
1170 ===========================================================================*/
1171
1172 PROCEDURE set_trx_defaults (
1173 rcv_trx IN OUT NOCOPY rcv_transactions_interface%ROWTYPE)
1174 IS
1175
1176 inventory_receipt BOOLEAN := FALSE;
1177 item_rev_exists BOOLEAN := FALSE;
1178 X_item_rev_control NUMBER := 1;
1179 X_sysdate DATE;
1180 locator_control NUMBER := 0;
1181 X_parent_id NUMBER := 0;
1182 X_tolerable_quantity NUMBER := 0;
1183 X_transaction_type VARCHAR2(30) := NULL;
1184 X_uom VARCHAR2(30) := NULL;
1185 X_receipt_source_code VARCHAR2(30) := NULL;
1186 X_available_quantity NUMBER := 0;
1187 X_progress VARCHAR2(4) := '000';
1188 x_default_subinventory VARCHAR2(10);
1189 X_default_locator_id NUMBER;
1190 X_SUCCESS BOOLEAN := FALSE;
1191 X_txn_from_web BOOLEAN := FALSE;
1192 X_txn_from_wf BOOLEAN := FALSE;
1193 /*Bug 2869806 */
1194 x_dist_avail_qty NUMBER := 0;
1195 x_dist_tol_qty NUMBER := 0;
1196 x_dist_uom VARCHAR2(30) := NULL;
1197 x_distribution_id NUMBER := 0;
1198 x_dist_qty_in_trx_uom NUMBER := 0;
1199 x_dist_count NUMBER := 0;
1200 x_ship_qty_in_trx_uom NUMBER := 0;
1201 x_trx_uom VARCHAR2(30) := NULL;
1202 /*Bug 1548597 */
1203 X_secondary_available_qty NUMBER := 0;
1204 /* Bug# 2834411 */
1205 x_project_id NUMBER;
1206 x_task_id NUMBER;
1207 /* bug 2994421 */
1208 x_uom_code VARCHAR2(5) := NULL;
1209
1210 BEGIN
1211
1212 /*
1213 ** Set the transaction type and the auto transact code and the
1214 ** quantity
1215 */
1216 IF (rcv_trx.transaction_type = 'EXPRESS RECEIPT') THEN
1217
1218 rcv_trx.transaction_type := 'RECEIVE';
1219 rcv_trx.auto_transact_code := 'RECEIVE';
1220
1221 ELSIF (rcv_trx.transaction_type = 'EXPRESS DIRECT') THEN
1222
1223 rcv_trx.transaction_type := 'RECEIVE';
1224 rcv_trx.auto_transact_code := 'DELIVER';
1225
1226 ELSIF (rcv_trx.transaction_type = 'EXPRESS DELIVER') THEN
1227
1228 rcv_trx.transaction_type := 'DELIVER';
1229 rcv_trx.auto_transact_code := '';
1230
1231 ELSIF (rcv_trx.transaction_type = 'CONFIRM RECEIPT') THEN
1232
1233 rcv_trx.transaction_type := 'RECEIVE';
1234 rcv_trx.auto_transact_code := 'DELIVER';
1235 X_txn_from_web := TRUE;
1236
1237 ELSIF (rcv_trx.transaction_type = 'CONFIRM RECEIPT(WF)') THEN
1238
1239 rcv_trx.transaction_type := 'RECEIVE';
1240 rcv_trx.auto_transact_code := 'DELIVER';
1241 X_txn_from_web := FALSE;
1242
1243 -- bug 513848
1244 X_txn_from_wf := TRUE;
1245
1246 END IF;
1247
1248 /*
1249 ** Set the status of the record to pending
1250 */
1251 rcv_trx.processing_status_code := 'PENDING';
1252 rcv_trx.transaction_status_code := 'PENDING';
1253
1254 /*
1255 ** Set the default item rev if one is not set on the transaction
1256 */
1257 inventory_receipt := rcv_transactions_sv.val_if_inventory_destination (
1258 rcv_trx.po_line_location_id, rcv_trx.shipment_line_id);
1259
1260 /*
1261 ** If this is has an inventory destination and there is not item rev
1262 ** specified and it's under item rev control then try to go get the
1263 ** latest implemented item rev
1264 */
1265 IF (inventory_receipt AND
1266 rcv_trx.item_id IS NOT NULL AND
1267 rcv_trx.item_revision IS NULL) THEN
1268
1269 X_progress := 400;
1270
1271 SELECT msi.revision_qty_control_code
1272 INTO X_item_rev_control
1273 FROM mtl_system_items_kfv msi
1274 WHERE rcv_trx.item_id = msi.inventory_item_id
1275 AND rcv_trx.to_organization_id = msi.organization_id;
1276
1277 /*
1278 ** If this item is under rev control which is the code 2
1279 ** then go get the latest implemented version
1280 */
1281 IF (X_item_rev_control = 2 AND rcv_trx.item_revision IS NULL) THEN
1282
1283 X_progress := 410;
1284 po_items_sv2.get_latest_item_rev (rcv_trx.item_id,
1285 rcv_trx.to_organization_id,
1286 rcv_trx.item_revision,
1287 item_rev_exists);
1288 END IF;
1289
1290 END IF; -- (inventory_receipt ...)
1291
1292 X_receipt_source_code := rcv_trx.receipt_source_code;
1293 /*
1294 ** Get the available transaction quantity for this receipt
1295 */
1296 IF (rcv_trx.transaction_type = 'RECEIVE' AND
1297 rcv_trx.auto_transact_code = 'RECEIVE') THEN
1298
1299 /*
1300 ** If this is a vendor receipt then the parent id is the line
1301 ** location id otherwise its an internal shipment so the parent
1302 ** id is the shipment_line_id.
1303 */
1304 IF (rcv_trx.receipt_source_code = 'VENDOR') THEN
1305 X_parent_id := rcv_trx.po_line_location_id;
1306
1307 /*
1308 ** If this is an asn then we need to override the parameters to the
1309 ** get_available call in this case.
1310 */
1311 ELSIF (rcv_trx.receipt_source_code in ('ASN','LCM')) THEN -- lcm changes
1312
1313 X_parent_id := rcv_trx.shipment_line_id;
1314 X_transaction_type := 'RECEIVE';
1315 X_receipt_source_code := 'INVENTORY';
1316
1317 ELSE
1318
1319 X_parent_id := rcv_trx.shipment_line_id;
1320
1321 END IF;
1322
1323 X_transaction_type := 'RECEIVE';
1324
1325 X_progress := '1100';
1326
1327 /*Bug 1548597 */
1328
1329 rcv_quantities_s.get_available_quantity (
1330 X_transaction_type, X_parent_id,
1331 X_receipt_source_code, NULL, 0, NULL, X_available_quantity,
1332 X_tolerable_quantity, X_uom,X_secondary_available_qty);
1333
1334
1335 ELSIF (rcv_trx.transaction_type = 'RECEIVE' AND
1336 rcv_trx.auto_transact_code = 'DELIVER')THEN
1337
1338 /*
1339 ** If this is a vendor receipt then the parent id is the line
1340 ** location id otherwise its an internal shipment so the parent
1341 ** id is the shipment_line_id.
1342 */
1343 IF (rcv_trx.receipt_source_code = 'VENDOR') THEN
1344
1345 X_parent_id := rcv_trx.po_distribution_id;
1346 X_transaction_type := 'DIRECT RECEIPT';
1347
1348 /*
1349 ** If the shipment_line_id is populated then this must be sourced
1350 ** from an asn. We need to override the parameters to the get_available
1351 ** call in this case.
1352 */
1353 ELSIF (rcv_trx.receipt_source_code in ('ASN','LCM')) THEN -- lcm changes
1354
1355
1356 /* Bug 2869806 - Added the following piece of code to get the available
1357 distribution qty if the distribution id is populated.We are also
1358 getting the count(po_distribution_id) to check if this is the last
1359 distribution. When doing a express delivery on an overshipped ASN
1360 we need to allocate the excess qty only to the last distribution.
1361 If the following select returns one then this is the last distribution
1362 and the excess quantity should be allocated against this distribution.
1363 */
1364
1365 select count(po_distribution_id)
1366 into x_dist_count
1367 from rcv_transactions_interface
1368 where transaction_type = 'EXPRESS DIRECT'
1369 and po_line_location_id = rcv_trx.po_line_location_id
1370 and shipment_line_id = rcv_trx.shipment_line_id;
1371
1372
1373 if (rcv_trx.po_distribution_id is not null) then
1374
1375 x_distribution_id := rcv_trx.po_distribution_id;
1376 x_transaction_type := 'DIRECT RECEIPT';
1377
1378 rcv_quantities_s.get_available_quantity (
1379 X_transaction_type, x_distribution_id,
1380 X_receipt_source_code, NULL, 0, NULL, x_dist_avail_qty,
1381 x_dist_tol_qty, x_dist_uom,X_secondary_available_qty);
1382
1383
1384 end if;
1385
1386
1387 X_parent_id := rcv_trx.shipment_line_id;
1388 X_transaction_type := 'RECEIVE';
1389 X_receipt_source_code := 'INVENTORY';
1390
1391
1392 ELSE
1393
1394 X_parent_id := rcv_trx.shipment_line_id;
1395 X_transaction_type := 'RECEIVE';
1396
1397 END IF;
1398
1399 X_progress := '1100';
1400
1401 /*Bug 1548597 */
1402 rcv_quantities_s.get_available_quantity (
1403 X_transaction_type, X_parent_id,
1404 X_receipt_source_code, NULL, 0, NULL, X_available_quantity,
1405 X_tolerable_quantity, X_uom,X_secondary_available_qty);
1406
1407 /*
1408 ** If this is a delivery then you need to give the parent transaction
1409 ** id as the the parent id
1410 */
1411 ELSIF (rcv_trx.transaction_type = 'DELIVER') THEN
1412
1413
1414 /* Chk avaliable qty and UOM for the distribution transaction */
1415 /*Bug 1548597 */
1416
1417 IF (rcv_trx.receipt_source_code = 'VENDOR') THEN
1418
1419 /* Bug 7040004 */
1420 /* Call this API only when receipt_source_code is Vendor */
1421 /* Donot calculate the available quantity in case of internal orders and inter-org transfers */
1422
1423 rcv_quantities_s.get_available_quantity ('STANDARD DELIVER',
1424 rcv_trx.po_distribution_id,
1425 null,
1426 null,
1427 rcv_trx.parent_transaction_id,
1428 null,
1429 X_available_quantity,
1430 X_tolerable_quantity, X_uom,X_secondary_available_qty);
1431 ELSE
1432
1433 X_available_quantity := rcv_trx.quantity;
1434 X_tolerable_quantity := rcv_trx.quantity;
1435 X_uom := rcv_trx.unit_of_measure;
1436 X_secondary_available_qty:=rcv_trx.secondary_quantity;
1437
1438 END IF;
1439
1440 /* End bug 7040004 */
1441
1442 ELSE
1443 null;
1444 -- htp.p ('ERROR: Invalid transaction type' || rcv_trx.receipt_source_code); htp.nl;
1445
1446 END IF;
1447
1448
1449 -- htp.p ('set_trx_defaults : available quantity = ' || TO_CHAR(X_available_quantity)); htp.nl;
1450 -- htp.p ('set_trx_defaults : transaction status code = ' || rcv_trx.transaction_status_code); htp.nl;
1451 /*
1452 ** Set the transactions quantity to the quantity available to transact
1453 ** only if the transaction_status_code is not CONFIRM.
1454 ** If the order is received via the Receive Orders Page on the Web,
1455 ** we set the variable X_txn_from_web to TRUE in the beg. of this procedure.
1456 */
1457
1458 -- bug 513848
1459 -- Do not set the transactions quantity if it is from Confirm Receipt
1460 -- Web page or Workflow
1461
1462 if X_txn_from_web or
1463 X_txn_from_wf then
1464 NULL;
1465 else
1466 rcv_trx.quantity := X_available_quantity;
1467 end if;
1468
1469
1470 /* Bug 3927688.
1471 Passing ASN uom to RTI record for ASN Express Receipt.
1472 */
1473 IF (rcv_trx.transaction_type = 'RECEIVE' AND
1474 rcv_trx.auto_transact_code = 'RECEIVE' AND
1475 rcv_trx.receipt_source_code in ('ASN','LCM'))THEN -- lcm changes
1476
1477 rcv_trx.unit_of_measure := x_uom;
1478
1479 END IF;
1480
1481
1482 /* Bug 2869806 As we have the available qty from ASN,available qunatity from
1483 the distribution and distribtion count we allocate the available qty
1484 from the distribution as transaction qty.*/
1485
1486
1487
1488 IF (rcv_trx.transaction_type = 'RECEIVE' AND
1489 rcv_trx.auto_transact_code = 'DELIVER' AND
1490 rcv_trx.receipt_source_code in ('ASN','LCM'))THEN -- lcm changes
1491
1492 x_trx_uom := x_uom; --3927688
1493
1494 if (x_dist_uom <> x_trx_uom) then
1495
1496 po_uom_s.uom_convert(x_dist_avail_qty, x_dist_uom, rcv_trx.item_id,
1497 x_trx_uom, x_dist_qty_in_trx_uom);
1498
1499 else
1500
1501 x_dist_qty_in_trx_uom := x_dist_avail_qty;
1502
1503 end if;
1504
1505
1506 if(x_uom <> x_trx_uom ) then
1507
1508 po_uom_s.uom_convert(x_available_quantity, x_uom, rcv_trx.item_id,
1509 x_trx_uom, x_ship_qty_in_trx_uom);
1510
1511 else
1512
1513 x_ship_qty_in_trx_uom := x_available_quantity;
1514
1515 end if;
1516
1517
1518
1519 if (rcv_trx.po_distribution_id is not null) then
1520
1521 if((x_ship_qty_in_trx_uom > x_dist_qty_in_trx_uom) and
1522 (x_dist_count > 1)) then
1523
1524 rcv_trx.quantity := x_dist_qty_in_trx_uom;
1525 rcv_trx.unit_of_measure := x_trx_uom; --3927688
1526 else
1527 rcv_trx.quantity := x_ship_qty_in_trx_uom;
1528 rcv_trx.unit_of_measure := x_trx_uom; --3927688
1529
1530 end if;
1531
1532 end if;
1533
1534 END IF;
1535
1536 --It is required to move the bugfix of 2994421 to this
1537 --location after the bugfix of 3927688 to populate the
1538 --correct uom_code in RTI.
1539
1540 /* Fix for bug 2994421.
1541 Populating uom_code into rti. Uom_code is required
1542 for receipts done against drop ship POs as this is
1543 used at the time of Sales Order Issue transaction.
1544 */
1545
1546 if (rcv_trx.uom_code is null) then
1547
1548 select uom_code
1549 into x_uom_code
1550 from mtl_units_of_measure
1551 where unit_of_measure = rcv_trx.unit_of_measure;
1552
1553 rcv_trx.uom_code := x_uom_code;
1554
1555 end if;
1556 -- htp.p ('set_trx_defaults : rcv_trx.quantity = ' || TO_CHAR(rcv_trx.quantity)); htp.nl;
1557
1558 IF (rcv_trx.destination_type_code = 'INVENTORY' AND
1559 (rcv_trx.auto_transact_code = 'DELIVER' OR
1560 rcv_trx.transaction_type = 'DELIVER')) THEN
1561
1562 /*
1563 ** A subinventory must have been defined on the po or a default
1564 ** must be available for the item. If it's not already defined
1565 ** then go get it out of inventory. If you're using express
1566 ** then it's ok to get the default rather than having it be
1567 ** defined on the record
1568 */
1569 IF (rcv_trx.subinventory IS NULL) THEN
1570
1571 /*
1572 ** If you're using express then it's ok to get the default
1573 ** rather than having it be defined on the record
1574 */
1575 X_progress := '1200';
1576 po_subinventories_s.get_default_subinventory (
1577 rcv_trx.to_organization_id,
1578 rcv_trx.item_id,
1579 rcv_trx.subinventory);
1580
1581 END IF; -- (rcv_trx.subinventory IS NULL)
1582
1583 /*
1584 ** See if org/sub/item is under locator control. If the sub is
1585 ** not available then don't do this call since it won't matter
1586 ** because the row will fail without a sub
1587 */
1588 IF (rcv_trx.subinventory IS NOT NULL) THEN
1589
1590 X_progress := '1220';
1591 po_subinventories_s.get_locator_control
1592 (rcv_trx.to_organization_id,
1593 rcv_trx.subinventory,
1594 rcv_trx.item_id,
1595 locator_control);
1596
1597 /*
1598 ** If locator control is 2 which means it is under predefined
1599 ** locator contol or 3 which means it's under dynamic (any value)
1600 ** locator control then you need to go get the default locator id
1601 */
1602 IF (locator_control = 2 OR locator_control = 3) THEN
1603
1604 X_progress := '1230';
1605
1606 po_subinventories_s.get_default_locator (
1607 rcv_trx.to_organization_id,
1608 rcv_trx.item_id,
1609 rcv_trx.subinventory,
1610 rcv_trx.locator_id);
1611
1612 /* Bug# 2834411 - Added the following logic to default the
1613 project_id and task_id */
1614 IF (rcv_trx.receipt_source_code <> 'CUSTOMER') THEN
1615
1616 IF (rcv_trx.po_distribution_id IS NOT NULL AND
1617 rcv_trx.locator_id IS NOT NULL) THEN
1618
1619 SELECT project_id, task_id
1620 INTO x_project_id, x_task_id
1621 FROM po_distributions
1622 WHERE po_distribution_id = rcv_trx.po_distribution_id;
1623
1624 ELSIF (rcv_trx.requisition_line_id is not null and
1625 rcv_trx.locator_id is not null) then
1626
1627 SELECT project_id, task_id
1628 INTO x_project_id,x_task_id
1629 FROM po_req_distributions
1630 WHERE requisition_line_id = rcv_trx.requisition_line_id;
1631
1632 END IF;
1633
1634 IF (x_project_id IS NOT NULL) THEN
1635 begin
1636 PJM_PROJECT_LOCATOR.Get_DefaultProjectLocator(
1637 rcv_trx.to_organization_id,
1638 rcv_trx.locator_id,
1639 x_project_id,
1640 x_task_id,
1641 rcv_trx.locator_id);
1642 exception
1643 when others then null;
1644 end;
1645
1646 END IF;
1647
1648 END IF;
1649 /* Bug# 2834411 - End */
1650
1651 END IF;
1652
1653
1654 END IF;
1655
1656 END IF; -- (rcv_trx.destination_type_code = 'INVENTORY' AND...)
1657
1658 /*
1659 ** DEBUG: If this is an express direct then check the quantity left
1660 ** on the receipt against what you are attempting to deliver and
1661 ** see if there is enough to deliver. If not, takes what's left
1662 ** and then terminate the loop up above on the distributions.
1663 ** The problem here is that I could have over received a distribution
1664 ** in an earlier manual transaction so we can't assume we can deliver
1665 ** what's on the distribution. Need to modify Sanjay's get quantity
1666 ** routines to perform this function.
1667 */
1668
1669 /*
1670 ** Set all the quantity information.
1671 ** You must also set the primary_quantity and uom properly
1672 */
1673
1674 X_progress := '900';
1675
1676 RCV_QUANTITIES_S.get_primary_qty_uom (
1677 rcv_trx.quantity,
1678 rcv_trx.unit_of_measure,
1679 rcv_trx.item_id,
1680 rcv_trx.to_organization_id,
1681 rcv_trx.primary_quantity,
1682 rcv_trx.primary_unit_of_measure);
1683
1684 /*
1685 ** Override the receipt_source_code if it is set to ASN
1686 */
1687 IF (rcv_trx.receipt_source_code in ('ASN','LCM'))THEN -- lcm changes
1688
1689 rcv_trx.receipt_source_code := 'VENDOR';
1690
1691 END IF;
1692
1693 IF (rcv_trx.destination_type_code = 'INVENTORY' AND
1694 (rcv_trx.auto_transact_code = 'DELIVER' OR
1695 rcv_trx.transaction_type = 'DELIVER')) THEN
1696
1697 X_default_subinventory := rcv_trx.subinventory;
1698 X_default_locator_id := rcv_trx.locator_id;
1699
1700 /*
1701 ** Call the put away function
1702 */
1703 X_success := rcv_sub_locator_sv.put_away_api (
1704 rcv_trx.po_line_location_id,
1705 rcv_trx.po_distribution_id,
1706 rcv_trx.shipment_line_id,
1707 rcv_trx.receipt_source_code ,
1708 rcv_trx.from_organization_id,
1709 rcv_trx.to_organization_id,
1710 rcv_trx.item_id,
1711 rcv_trx.item_revision,
1712 rcv_trx.vendor_id,
1713 rcv_trx.ship_to_location_id,
1714 rcv_trx.deliver_to_location_id,
1715 rcv_trx.deliver_to_person_id ,
1716 rcv_trx.quantity,
1717 rcv_trx.primary_quantity,
1718 rcv_trx.primary_unit_of_measure,
1719 x_tolerable_quantity ,
1720 rcv_trx.unit_of_measure,
1721 rcv_trx.routing_header_id,
1722 x_default_subinventory ,
1723 x_default_locator_id ,
1724 rcv_trx.subinventory,
1725 rcv_trx.locator_id);
1726
1727 END IF;
1728
1729 RETURN;
1730
1731 EXCEPTION
1732 WHEN OTHERS THEN
1733 po_message_s.sql_error('set_trx_defaults', X_progress, sqlcode);
1734 RAISE;
1735
1736 END set_trx_defaults;
1737
1738
1739 /*===========================================================================
1740
1741 PROCEDURE NAME: print_receord
1742
1743 ===========================================================================*/
1744
1745 /*
1746 ** Prints a transaction record
1747 */
1748
1749 PROCEDURE print_record (rcv_trx IN OUT NOCOPY rcv_transactions_interface%ROWTYPE) IS
1750
1751 BEGIN
1752 htp.code('-------------- Transactions Definition ------------------'); htp.nl;
1753 htp.code('interface_transaction_id : ' ||
1754 to_char(rcv_trx.interface_transaction_id)); htp.nl;
1755 htp.code('transaction_type : ' ||
1756 rcv_trx.transaction_type); htp.nl;
1757 htp.code('auto_transact_code : ' ||
1758 rcv_trx.auto_transact_code); htp.nl;
1759 htp.code('transaction_date : ' ||
1760 to_char(rcv_trx.transaction_date)); htp.nl;
1761 htp.code('quantity : ' ||
1762 TO_CHAR(rcv_trx.quantity)); htp.nl;
1763 htp.code('unit_of_measure : ' ||
1764 rcv_trx.unit_of_measure); htp.nl;
1765 htp.code('po_line_location_id : ' ||
1766 to_char(rcv_trx.po_line_location_id)); htp.nl;
1767 htp.code('shipment_line_id : ' ||
1768 to_char(rcv_trx.shipment_line_id)); htp.nl;
1769 htp.code('item_id : ' ||
1770 to_char(rcv_trx.item_id)); htp.nl;
1771 htp.code('item_revision : ' ||
1772 rcv_trx.item_revision); htp.nl;
1773 htp.code('vendor_id : ' ||
1774 to_char(rcv_trx.vendor_id)); htp.nl;
1775 htp.code('from_organization_id : ' ||
1776 to_char(rcv_trx.from_organization_id)); htp.nl;
1777 htp.code('to_organization_id : ' ||
1778 to_char(rcv_trx.to_organization_id)); htp.nl;
1779 htp.code('expected_receipt_date : ' ||
1780 to_char(rcv_trx.expected_receipt_date)); htp.nl;
1781 htp.code('routing_header_id : ' ||
1782 to_char(rcv_trx.routing_header_id)); htp.nl;
1783 htp.code('destination_type_code : ' ||
1784 rcv_trx.destination_type_code); htp.nl;
1785 htp.code('po_distribution_id : ' ||
1786 TO_CHAR(rcv_trx.po_distribution_id)); htp.nl;
1787 htp.code('deliver_to_person_id : ' ||
1788 TO_CHAR(rcv_trx.deliver_to_person_id)); htp.nl;
1789 htp.code('deliver_to_location_id : ' ||
1790 TO_CHAR(rcv_trx.deliver_to_location_id)); htp.nl;
1791 htp.code('subinventory : ' ||
1792 rcv_trx.subinventory); htp.nl;
1793 htp.code('locator_id : ' ||
1794 TO_CHAR(rcv_trx.locator_id)); htp.nl;
1795 htp.code('wip_entity_id : ' ||
1796 TO_CHAR(rcv_trx.wip_entity_id)); htp.nl;
1797 htp.code('wip_line_id : ' ||
1798 TO_CHAR(rcv_trx.wip_line_id)); htp.nl;
1799 htp.code('wip_repetitive_schedule_id : ' ||
1800 TO_CHAR(rcv_trx.wip_repetitive_schedule_id)); htp.nl;
1801 htp.code('wip_operation_seq_num : ' ||
1802 TO_CHAR(rcv_trx.wip_operation_seq_num)); htp.nl;
1803 htp.code('wip_resource_seq_num : ' ||
1804 TO_CHAR(rcv_trx.wip_resource_seq_num)); htp.nl;
1805 htp.code('bom_resource_id : ' ||
1806 TO_CHAR(rcv_trx.bom_resource_id)); htp.nl; htp.nl;
1807
1808 END print_record;
1809
1810 /*===========================================================================
1811
1812 PROCEDURE NAME: Insert_Interface_Errors
1813
1814 ===========================================================================*/
1815
1816 /*
1817 ** Insert into PO_INTERFACE_ERRORS table
1818 */
1819
1820 PROCEDURE insert_interface_errors ( rcv_trx IN OUT NOCOPY rcv_transactions_interface%ROWTYPE,
1821 X_column_name IN VARCHAR2,
1822 X_err_message IN VARCHAR2) as
1823
1824 X_progress VARCHAR2(3) := '000';
1825
1826 begin
1827
1828 X_progress := '050';
1829
1830 /* Bug 4773978: Added the following code for logging error messages
1831 in PO_INTERFACE_ERRORS table and removed the Insert
1832 statements to insert into PO_INTERFACE_ERRORS
1833 table, as the fields error_message, interface_line_id
1834 and interface_header_id are not populated. */
1835 RCV_ERROR_PKG.set_error_message(X_err_message);
1836 RCV_ERROR_PKG.log_interface_error(X_column_name,FALSE);
1837
1838 end insert_interface_errors;
1839
1840 End rcv_express_sv;
[Home] [Help]