1 PACKAGE BODY rcv_headers_interface_sv AS
2 /* $Header: RCVHISVB.pls 120.1.12010000.2 2008/08/04 08:41:44 rramasam ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_asn_debug VARCHAR2(1) := NVL(fnd_profile.VALUE('RCV_DEBUG_MODE'), 'N');
6
7 -- Bug 2506961
8 PROCEDURE genreceiptnum(
9 p_header_record IN OUT NOCOPY rcv_shipment_header_sv.headerrectype
10 );
11
12 vendor_record rcv_shipment_header_sv.vendorrectype;
13 vendor_site_record rcv_shipment_header_sv.vendorsiterectype;
14 from_org_record rcv_shipment_object_sv.organization_id_record_type;
15 ship_to_org_record rcv_shipment_object_sv.organization_id_record_type;
16 loc_record rcv_shipment_object_sv.location_id_record_type;
17 emp_record rcv_shipment_object_sv.employee_id_record_type;
18 pay_record rcv_shipment_header_sv.payrectype;
19 freight_record rcv_shipment_header_sv.freightrectype;
20 lookup_record rcv_shipment_header_sv.lookuprectype;
21 currency_record rcv_shipment_header_sv.currectype;
22 invoice_record rcv_shipment_header_sv.invrectype;
23 tax_record rcv_shipment_header_sv.taxrectype;
24 x_sysdate DATE := SYSDATE;
25 x_count NUMBER := 0;
26 x_location_id NUMBER;
27
28 /*===========================================================================+
29 | |
30 | PROCEDURE NAME: derive_shipment_header() |
31 | |
32 +===========================================================================*/
33 PROCEDURE derive_shipment_header(
34 p_header_record IN OUT NOCOPY rcv_shipment_header_sv.headerrectype
35 ) IS
36 BEGIN
37 /* Derive Vendor Information */
38 IF p_header_record.error_record.error_status IN('S', 'W') THEN
39 vendor_record.vendor_name := p_header_record.header_record.vendor_name;
40 vendor_record.vendor_num := p_header_record.header_record.vendor_num;
41 vendor_record.vendor_id := p_header_record.header_record.vendor_id;
42 vendor_record.error_record := p_header_record.error_record;
43
44 IF (g_asn_debug = 'Y') THEN
45 asn_debug.put_line('In Vendor Procedure');
46 END IF;
47
48 po_vendors_sv.derive_vendor_info(vendor_record);
49
50 IF (g_asn_debug = 'Y') THEN
51 asn_debug.put_line(TO_CHAR(vendor_record.vendor_id));
52 asn_debug.put_line(vendor_record.vendor_name);
53 asn_debug.put_line(vendor_record.vendor_num);
54 asn_debug.put_line(vendor_record.error_record.error_status);
55 asn_debug.put_line(vendor_record.error_record.error_message);
56 END IF;
57
58 p_header_record.header_record.vendor_name := vendor_record.vendor_name;
59 p_header_record.header_record.vendor_num := vendor_record.vendor_num;
60 p_header_record.header_record.vendor_id := vendor_record.vendor_id;
61 p_header_record.error_record := vendor_record.error_record;
62 END IF;
63
64 /* Derive Ship To Organization Information */
65 /* organization_id is uk. org_organization_definitions is a view */
66 IF p_header_record.error_record.error_status IN('S', 'W') THEN
67 /*
68 ** If the shipment header ship to organization code is null then try
69 ** to pull it off the rcv_transactions_interface to_organization_code or
70 ** the ship_to_location_code.
71 */
72 IF ( p_header_record.header_record.ship_to_organization_code IS NULL
73 AND p_header_record.header_record.ship_to_organization_id IS NULL) THEN
74 rcv_headers_interface_sv.derive_ship_to_org_from_rti(p_header_record);
75 END IF;
76
77 ship_to_org_record.organization_code := p_header_record.header_record.ship_to_organization_code;
78 ship_to_org_record.organization_id := p_header_record.header_record.ship_to_organization_id;
79 ship_to_org_record.error_record := p_header_record.error_record;
80
81 IF (g_asn_debug = 'Y') THEN
82 asn_debug.put_line('In Ship to Organization Procedure');
83 END IF;
84
85 po_orgs_sv.derive_org_info(ship_to_org_record);
86
87 IF (g_asn_debug = 'Y') THEN
88 asn_debug.put_line(ship_to_org_record.organization_code);
89 asn_debug.put_line(TO_CHAR(ship_to_org_record.organization_id));
90 asn_debug.put_line(ship_to_org_record.error_record.error_status);
91 END IF;
92
93 p_header_record.header_record.ship_to_organization_code := ship_to_org_record.organization_code;
94 p_header_record.header_record.ship_to_organization_id := ship_to_org_record.organization_id;
95 p_header_record.error_record := ship_to_org_record.error_record;
96 END IF;
97
98 /* derive from organization information */
99 IF p_header_record.error_record.error_status IN('S', 'W')
100 AND p_header_record.header_record.transaction_type <> 'CANCEL' THEN -- added for support of cancel
101 from_org_record.organization_code := p_header_record.header_record.from_organization_code;
102 from_org_record.organization_id := p_header_record.header_record.from_organization_id;
103 from_org_record.error_record := p_header_record.error_record;
104
105 IF (g_asn_debug = 'Y') THEN
106 asn_debug.put_line('In From Organization Procedure');
107 END IF;
108
109 po_orgs_sv.derive_org_info(from_org_record);
110
111 IF (g_asn_debug = 'Y') THEN
112 asn_debug.put_line(from_org_record.organization_code);
113 asn_debug.put_line(TO_CHAR(from_org_record.organization_id));
114 asn_debug.put_line(from_org_record.error_record.error_status);
115 END IF;
116
117 p_header_record.header_record.from_organization_code := from_org_record.organization_code;
118 p_header_record.header_record.from_organization_id := from_org_record.organization_id;
119 p_header_record.error_record := from_org_record.error_record;
120 END IF;
121
122 /* derive vendor site information */
123 /* Call derive vendor_site_procedure here */
124 /* UK1 -> vendor_site_id
125 UK2 -> vendor_site_code + vendor_id + org_id */
126 IF p_header_record.error_record.error_status IN('S', 'W')
127 AND ( p_header_record.header_record.vendor_site_code IS NOT NULL
128 OR p_header_record.header_record.vendor_site_id IS NOT NULL) THEN
129 vendor_site_record.vendor_site_code := p_header_record.header_record.vendor_site_code;
130 vendor_site_record.vendor_id := p_header_record.header_record.vendor_id;
131 vendor_site_record.vendor_site_id := p_header_record.header_record.vendor_site_id;
132 vendor_site_record.organization_id := p_header_record.header_record.ship_to_organization_id;
133 vendor_site_record.error_record := p_header_record.error_record;
134
135 IF (g_asn_debug = 'Y') THEN
136 asn_debug.put_line('In Vendor Site Procedure');
137 END IF;
138
139 po_vendor_sites_sv.derive_vendor_site_info(vendor_site_record);
140
141 IF (g_asn_debug = 'Y') THEN
142 asn_debug.put_line(vendor_site_record.vendor_site_code);
143 asn_debug.put_line(vendor_site_record.vendor_site_id);
144 END IF;
145
146 p_header_record.header_record.vendor_site_code := vendor_site_record.vendor_site_code;
147 p_header_record.header_record.vendor_id := vendor_site_record.vendor_id;
148 p_header_record.header_record.vendor_site_id := vendor_site_record.vendor_site_id;
149 p_header_record.header_record.ship_to_organization_id := vendor_site_record.organization_id;
150 p_header_record.error_record := vendor_site_record.error_record;
151 END IF;
152
153 /* Derive Location Information */
154 /* HR_LOCATION has 2 unique indexes
155 1 -> location_id
156 2 -> location_code */
157 IF p_header_record.error_record.error_status IN('S', 'W')
158 AND ( p_header_record.header_record.location_code IS NOT NULL
159 OR p_header_record.header_record.location_id IS NOT NULL) THEN
160 loc_record.location_code := p_header_record.header_record.location_code;
161 loc_record.location_id := p_header_record.header_record.location_id;
162 loc_record.error_record := p_header_record.error_record;
163
164 IF (g_asn_debug = 'Y') THEN
165 asn_debug.put_line('In Location Code Procedure');
166 END IF;
167
168 po_locations_s.derive_location_info(loc_record);
169
170 IF (g_asn_debug = 'Y') THEN
171 asn_debug.put_line(loc_record.location_code);
172 asn_debug.put_line(TO_CHAR(loc_record.location_id));
173 asn_debug.put_line(loc_record.error_record.error_status);
174 END IF;
175
176 p_header_record.header_record.location_code := loc_record.location_code;
177 p_header_record.header_record.location_id := loc_record.location_id;
178 p_header_record.error_record := loc_record.error_record;
179 END IF;
180
181 /* Derive Payment Terms Information */
182 IF p_header_record.error_record.error_status IN('S', 'W')
183 AND p_header_record.header_record.transaction_type <> 'CANCEL'
184 AND -- added for support of cancel
185 ( p_header_record.header_record.payment_terms_id IS NOT NULL
186 OR p_header_record.header_record.payment_terms_name IS NOT NULL) THEN
187 pay_record.payment_term_id := p_header_record.header_record.payment_terms_id;
188 pay_record.payment_term_name := p_header_record.header_record.payment_terms_name;
189 pay_record.error_record := p_header_record.error_record;
190
191 IF (g_asn_debug = 'Y') THEN
192 asn_debug.put_line('In Derive Payment Terms ');
193 END IF;
194
195 po_terms_sv.derive_payment_terms_info(pay_record);
196
197 IF (g_asn_debug = 'Y') THEN
198 asn_debug.put_line(pay_record.payment_term_name);
199 asn_debug.put_line(TO_CHAR(pay_record.payment_term_id));
200 asn_debug.put_line(pay_record.error_record.error_status);
201 END IF;
202
203 p_header_record.header_record.payment_terms_id := pay_record.payment_term_id;
204 p_header_record.header_record.payment_terms_name := pay_record.payment_term_name;
205 p_header_record.error_record := pay_record.error_record;
206 END IF;
207
208 /* derive receiver information */
209 IF p_header_record.error_record.error_status IN('S', 'W')
210 AND p_header_record.header_record.transaction_type <> 'CANCEL'
211 AND -- added for support of cancel
212 ( p_header_record.header_record.employee_name IS NOT NULL
213 OR p_header_record.header_record.employee_id IS NOT NULL) THEN
214 emp_record.employee_name := p_header_record.header_record.employee_name;
215 emp_record.employee_id := p_header_record.header_record.employee_id;
216 emp_record.error_record := p_header_record.error_record;
217
218 IF (g_asn_debug = 'Y') THEN
219 asn_debug.put_line('In Derive Receiver Information');
220 END IF;
221
222 po_employees_sv.derive_employee_info(emp_record);
223
224 IF (g_asn_debug = 'Y') THEN
225 asn_debug.put_line(emp_record.employee_name);
226 asn_debug.put_line(TO_CHAR(emp_record.employee_id));
227 asn_debug.put_line(emp_record.error_record.error_status);
228 END IF;
229
230 p_header_record.header_record.employee_name := emp_record.employee_name;
231 p_header_record.header_record.employee_id := emp_record.employee_id;
232 p_header_record.error_record := emp_record.error_record;
233 END IF;
234
235 /* Derive shipment_header_id if transaction type = CANCEL */
236
237 -- added for support of cancel
238
239 IF p_header_record.error_record.error_status IN('S', 'W')
240 AND p_header_record.header_record.transaction_type = 'CANCEL'
241 AND p_header_record.header_record.shipment_num IS NOT NULL THEN
242 IF (g_asn_debug = 'Y') THEN
243 asn_debug.put_line('Derive shipment info');
244 END IF;
245
246 rcv_core_s.derive_shipment_info(p_header_record);
247 END IF;
248 END derive_shipment_header;
249
250 /*===========================================================================+
251 | |
252 | PROCEDURE NAME: default_shipment_header() |
253 | |
254 +===========================================================================*/
255 PROCEDURE default_shipment_header(
256 p_header_record IN OUT NOCOPY rcv_shipment_header_sv.headerrectype
257 ) IS
258 v_rcv_type po_system_parameters.user_defined_receipt_num_code%TYPE;
259 v_count NUMBER := 0;
260 temp_count NUMBER;
261 x_po_header_id NUMBER;
262 x_document_num VARCHAR2(20);
263 BEGIN
264 IF (g_asn_debug = 'Y') THEN
265 asn_debug.put_line('In default');
266 END IF;
267
268 /* last_update_date */
269 IF p_header_record.header_record.last_update_date IS NULL THEN
270 p_header_record.header_record.last_update_date := x_sysdate;
271
272 IF (g_asn_debug = 'Y') THEN
273 asn_debug.put_line('defaulting last update date');
274 END IF;
275 END IF;
276
277 /* last_updated_by */
278 IF p_header_record.header_record.last_updated_by IS NULL THEN
279 p_header_record.header_record.last_updated_by := fnd_global.user_id;
280
281 IF (g_asn_debug = 'Y') THEN
282 asn_debug.put_line('defaulting last update by');
283 END IF;
284 END IF;
285
286 /* creation_date */
287 IF p_header_record.header_record.creation_date IS NULL THEN
288 p_header_record.header_record.creation_date := x_sysdate;
289
290 IF (g_asn_debug = 'Y') THEN
291 asn_debug.put_line('defaulting creation date');
292 END IF;
293 END IF;
294
295 /* created_by */
296 IF p_header_record.header_record.created_by IS NULL THEN
297 p_header_record.header_record.created_by := fnd_global.user_id;
298
299 IF (g_asn_debug = 'Y') THEN
300 asn_debug.put_line('defaulting created by ');
301 END IF;
302 END IF;
303
304 /* last_update_login */
305 IF p_header_record.header_record.last_update_login IS NULL THEN
306 p_header_record.header_record.last_update_login := fnd_global.login_id;
307
308 IF (g_asn_debug = 'Y') THEN
309 asn_debug.put_line('defaulting last update login');
310 END IF;
311 END IF;
312
313 /* Default STD into asn_type for null asn_type */
314 IF p_header_record.header_record.asn_type IS NULL THEN
315 p_header_record.header_record.asn_type := 'STD';
316
317 IF (g_asn_debug = 'Y') THEN
318 asn_debug.put_line('defaulting asn type to STD');
319 END IF;
320 END IF;
321
322 /* SHIPMENT NUMBER FOR ASBN if shipment_num IS NULL */
323 /* First choice for ASBN */
324 IF p_header_record.header_record.asn_type = 'ASBN'
325 AND p_header_record.header_record.shipment_num IS NULL THEN
326 p_header_record.header_record.shipment_num := p_header_record.header_record.invoice_num;
327
328 IF (g_asn_debug = 'Y') THEN
329 asn_debug.put_line('defaulted shipment number');
330 END IF;
331 END IF;
332
333 /* SHIPMENT NUMBER FOR ASBN/ASN if shipment_num IS NULL */
334 /* First choice for ASN/ Second Choice for ASN */
335 IF p_header_record.header_record.shipment_num IS NULL THEN
336 p_header_record.header_record.shipment_num := p_header_record.header_record.packing_slip;
337
338 IF (g_asn_debug = 'Y') THEN
339 asn_debug.put_line('defaulted shipment number');
340 END IF;
341 END IF;
342
343 /* generate the shipment_header_id */
344 /* shipment_header_id - receipt_header_id is the same */
345 IF p_header_record.header_record.receipt_header_id IS NULL
346 AND p_header_record.header_record.transaction_type <> 'CANCEL' THEN -- added for support of cancel
347 SELECT rcv_shipment_headers_s.NEXTVAL
348 INTO p_header_record.header_record.receipt_header_id
349 FROM SYS.DUAL;
350
351 IF (g_asn_debug = 'Y') THEN
352 asn_debug.put_line('defaulted receipt_id');
353 END IF;
354 END IF;
355
356 /* receipt_num */
357
358 -- We will not generate a receipt num for auto transact code = SHIP
359 -- This will help minimise locking problems
360
361 -- If Receipt Generation is set to Manual then we need to default it based
362 -- on the Shipment number. If shipment_num is also null then we will use the
363 -- shipment_header_id. We need a Receipt num in case of RECEIVE/DELIVER as
364 -- some of the views of the receiving form have the condition of receipt_num not
365 -- null added to it.
366
367 -- IF the transaction type is CANCEL then no need to generate a receipt num
368
369 -- We cannot depend on the auto_transact_code from the rcv_headers_interface
370 -- to figure out whether we need to generate a receipt_num
371 -- We will look at the transactions_interface.auto_transact_code/transaction_type
372 -- to figure out whether we need to do the generation
373
374 IF p_header_record.header_record.receipt_num IS NULL
375 AND p_header_record.header_record.transaction_type <> 'CANCEL' THEN -- added for support of cancel
376 SELECT COUNT(*)
377 INTO v_count
378 FROM rcv_transactions_interface rti
379 WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id
380 AND ( rti.auto_transact_code IN('RECEIVE', 'DELIVER')
381 OR rti.transaction_type IN('RECEIVE', 'DELIVER'));
382
383 IF v_count > 0 THEN -- We need to generate a receipt_num
384 BEGIN
385 SELECT user_defined_receipt_num_code
386 INTO v_rcv_type
387 FROM rcv_parameters
388 WHERE organization_id = p_header_record.header_record.ship_to_organization_id;
389
390 /* assuming that the ship_to_organization_id is populated at the header level of
391 rcv_headers_interface */
392 IF (g_asn_debug = 'Y') THEN
393 asn_debug.put_line(v_rcv_type || ' Generation ');
394 END IF;
395
396 IF v_rcv_type = 'AUTOMATIC' THEN
397 --bug 2506961
398 rcv_headers_interface_sv.genreceiptnum(p_header_record);
399 ELSE -- MANUAL
400 IF p_header_record.header_record.shipment_num IS NOT NULL THEN
401 p_header_record.header_record.receipt_num := p_header_record.header_record.shipment_num;
402 END IF;
403
404 /* If receipt_num is still null then use the shipment_header_id */
405 IF p_header_record.header_record.receipt_num IS NULL THEN
406 p_header_record.header_record.receipt_num := TO_CHAR(p_header_record.header_record.receipt_header_id);
407 END IF;
408 END IF; -- v_rcv_type
409 EXCEPTION
410 WHEN OTHERS THEN
411 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
412 rcv_error_pkg.set_sql_error_message('default_shipment_header', '010');
413 p_header_record.error_record.error_message := rcv_error_pkg.get_last_message;
414 END;
415 ELSE -- of v_count
416 IF (g_asn_debug = 'Y') THEN
417 asn_debug.put_line('No need to generate a receipt_number');
418 END IF;
419 END IF; -- of v_count
420
421 IF (g_asn_debug = 'Y') THEN
422 asn_debug.put_line('defaulted receipt_num ' || p_header_record.header_record.receipt_num);
423 END IF;
424 END IF;
425
426 /* vendor_site_id po_vendor_sites_sv.default_purchasing_site */
427 /* Check for whether we need more conditions in the where clause of the
428 procedure like pay_site_flag etc */
429
430 /* For transaction_type = CANCEL we should have picked up the vendor_site_id in
431 the derive_shipment_info stage */
432 IF p_header_record.header_record.vendor_site_id IS NULL
433 AND p_header_record.header_record.vendor_site_code IS NULL
434 AND p_header_record.header_record.vendor_id IS NOT NULL THEN -- added for support of cancel
435 IF (g_asn_debug = 'Y') THEN
436 asn_debug.put_line('Need to get default vendor site id');
437 END IF;
438
439 po_vendor_sites_sv.get_def_vendor_site(p_header_record.header_record.vendor_id,
440 p_header_record.header_record.vendor_site_id,
441 p_header_record.header_record.vendor_site_code,
442 'RCV'
443 );
444
445 IF (g_asn_debug = 'Y') THEN
446 asn_debug.put_line('defaulted vendor_site info');
447 END IF;
448 END IF;
449
450 /* ship_to_location_id mtl_org_organizations.default */
451 IF p_header_record.header_record.location_code IS NULL
452 AND p_header_record.header_record.location_id IS NULL
453 AND p_header_record.header_record.transaction_type <> 'CANCEL'
454 AND -- added for support of cancel
455 p_header_record.header_record.ship_to_organization_id IS NOT NULL THEN
456 /* Changed hr_locations to hr_locations_all since we are searching
457 * using inventory_organization_id and for drop ship POs inventory
458 * orgid does not have any meaning.
459 */
460 SELECT MAX(hr_locations_all.location_id),
461 COUNT(*)
462 INTO x_location_id,
463 x_count
464 FROM hr_locations_all
465 WHERE hr_locations_all.inventory_organization_id = p_header_record.header_record.ship_to_organization_id
466 AND NVL(hr_locations_all.inactive_date, x_sysdate + 1) > x_sysdate
467 AND NVL(hr_locations_all.receiving_site_flag, 'N') = 'Y';
468
469 IF (g_asn_debug = 'Y') THEN
470 asn_debug.put_line('count in hr_locations_all ' || x_count);
471 END IF;
472
473 IF x_count = 1 THEN
474 p_header_record.header_record.location_id := x_location_id;
475
476 /* Bug 1904996. If this is a drop ship PO, then we dont want
477 * to default this value since this is the location for the
478 * inventory org id in which the drop ship PO for created and
479 * not the drop ship location.
480 */
481 SELECT MAX(rti.po_header_id),
482 MAX(document_num)
483 INTO x_po_header_id,
484 x_document_num
485 FROM rcv_transactions_interface rti
486 WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id;
487
488 IF ( x_po_header_id IS NULL
489 AND x_document_num IS NOT NULL) THEN
490 SELECT po_header_id
491 INTO x_po_header_id
492 FROM po_headers
493 WHERE segment1 = x_document_num
494 AND type_lookup_code IN('STANDARD', 'BLANKET', 'PLANNED');
495 END IF;
496
497 IF (x_po_header_id IS NOT NULL) THEN
498 SELECT COUNT(*)
499 INTO temp_count
500 FROM oe_drop_ship_sources
501 WHERE po_header_id = x_po_header_id;
502
503 IF (temp_count <> 0) THEN -- this is a drop ship
504 IF (g_asn_debug = 'Y') THEN
505 asn_debug.put_line('drop ship PO');
506 END IF;
507
508 p_header_record.header_record.location_id := NULL;
509 END IF;
510 END IF;
511 END IF;
512
513 IF (g_asn_debug = 'Y') THEN
514 asn_debug.put_line('defaulted location info');
515 END IF;
516 END IF;
517
518 /* Currency Code if ASBN invoice_currency_code po_vendor_sites_sv.default */
519
520 /* RECEIPT SOURCE CODE */
521 IF p_header_record.header_record.receipt_source_code IS NULL THEN
522 p_header_record.header_record.receipt_source_code := 'VENDOR';
523
524 IF (g_asn_debug = 'Y') THEN
525 asn_debug.put_line('defaulted receipt_source_code info');
526 END IF;
527 END IF;
528
529 -- added for support of cancel
530 -- default any shipment info
531
532 IF p_header_record.header_record.transaction_type = 'CANCEL'
533 AND ( p_header_record.header_record.receipt_header_id IS NULL
534 OR p_header_record.header_record.shipment_num IS NULL) THEN
535 IF (g_asn_debug = 'Y') THEN
536 asn_debug.put_line('Into default shipment info');
537 END IF;
538
539 rcv_core_s.default_shipment_info(p_header_record);
540 END IF;
541
542 IF (g_asn_debug = 'Y') THEN
543 asn_debug.put_line('Out of default');
544 END IF;
545 END default_shipment_header;
546
547 /*===========================================================================+
548 | |
549 | PROCEDURE NAME: validate_shipment_header() |
550 | |
551 +===========================================================================*/
552 PROCEDURE validate_shipment_header(
553 p_header_record IN OUT NOCOPY rcv_shipment_header_sv.headerrectype
554 ) IS
555 BEGIN
556 /* Validate Transaction Type */
557 IF (g_asn_debug = 'Y') THEN
558 asn_debug.put_line('In validate routine');
559 END IF;
560
561 IF p_header_record.error_record.error_status NOT IN('S', 'W') THEN
562 RETURN;
563 END IF;
564
565 lookup_record.lookup_code := p_header_record.header_record.transaction_type;
566 lookup_record.lookup_type := 'TRANSACTION_TYPE';
567 lookup_record.error_record := p_header_record.error_record;
568 po_core_s.validate_lookup_info(lookup_record);
569 p_header_record.error_record.error_status := lookup_record.error_record.error_status;
570 /* po_core_s doesn't follow error paradigm */
571 rcv_error_pkg.set_error_message(lookup_record.error_record.error_message, p_header_record.error_record.error_message);
572
573 IF (p_header_record.error_record.error_message = 'RCV_TRX_TYPE_INVALID') THEN
574 rcv_error_pkg.set_token('TYPE', lookup_record.lookup_code);
575 END IF;
576
577 rcv_error_pkg.log_interface_message(p_header_record.error_record.error_status,
578 'RCV_HEADERS_INTERFACE',
579 'TRANSACTION_TYPE'
580 );
581
582 IF (g_asn_debug = 'Y') THEN
583 asn_debug.put_line('validated transaction type');
584 END IF;
585
586 /* Validate Document type */
587 IF p_header_record.header_record.asn_type IS NOT NULL
588 AND p_header_record.header_record.asn_type <> 'STD' THEN
589 lookup_record.lookup_code := p_header_record.header_record.asn_type;
590 lookup_record.lookup_type := 'ASN_TYPE';
591 lookup_record.error_record := p_header_record.error_record;
592 po_core_s.validate_lookup_info(lookup_record);
593 p_header_record.error_record.error_status := lookup_record.error_record.error_status;
594 /* po_core_s doesn't follow error paradigm */
595 rcv_error_pkg.set_error_message(lookup_record.error_record.error_message, p_header_record.error_record.error_message);
596
597 IF (p_header_record.error_record.error_message = 'PO_PDOI_INVALID_TYPE_LKUP_CD') THEN
598 rcv_error_pkg.set_token('TYPE', lookup_record.lookup_code);
599 END IF;
600
601 rcv_error_pkg.log_interface_message(p_header_record.error_record.error_status,
602 'RCV_HEADERS_INTERFACE',
603 'ASN_TYPE'
604 );
605
606 IF (g_asn_debug = 'Y') THEN
607 asn_debug.put_line('validated asn type');
608 END IF;
609 ELSE
610 p_header_record.header_record.asn_type := 'STD'; -- Not an ASN/ASBN
611 END IF;
612
613 /* Validate Currency Code */
614 IF p_header_record.header_record.transaction_type <> 'CANCEL'
615 AND p_header_record.header_record.asn_type = 'ASBN'
616 AND p_header_record.header_record.currency_code IS NOT NULL THEN
617 currency_record.currency_code := p_header_record.header_record.currency_code;
618 currency_record.error_record := p_header_record.error_record;
619 po_currency_sv.validate_currency_info(currency_record);
620 p_header_record.error_record.error_status := currency_record.error_record.error_status;
621
622 /* po_currency_s doesn't follow error paradigm */
623 IF (currency_record.error_record.error_message IN('CURRENCY_DISABLED', 'CURRENCY_INVALID')) THEN
624 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_CURRENCY', p_header_record.error_record.error_message);
625 rcv_error_pkg.set_token('VALUE', p_header_record.header_record.currency_code);
626 ELSE
627 rcv_error_pkg.set_error_message(currency_record.error_record.error_message, p_header_record.error_record.error_message);
628 END IF;
629
630 rcv_error_pkg.log_interface_message(p_header_record.error_record.error_status,
631 'RCV_HEADERS_INTERFACE',
632 'CURRENCY_CODE'
633 );
634
635 IF (g_asn_debug = 'Y') THEN
636 asn_debug.put_line('validated currency info');
637 END IF;
638 END IF;
639
640 /* Validation for Shipment Date > System Date and not NULL,blank,zero */
641 IF NVL(p_header_record.header_record.shipped_date, x_sysdate + 1) > x_sysdate THEN
642 IF p_header_record.header_record.shipped_date IS NULL
643 AND p_header_record.header_record.asn_type = 'STD' THEN
644 IF (g_asn_debug = 'Y') THEN
645 asn_debug.put_line('Shipped date can be blank for STD');
646 END IF;
647 ELSE
648 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
649 rcv_error_pkg.set_error_message('RCV_SHIP_DATE_INVALID', p_header_record.error_record.error_message);
650 rcv_error_pkg.set_token('SHIP_DATE', p_header_record.header_record.shipped_date);
651 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'SHIPPED_DATE');
652 END IF;
653 END IF;
654
655 IF (g_asn_debug = 'Y') THEN
656 asn_debug.put_line('validated for shipment_date > system date');
657 END IF;
658
659 /* Validation for Receipt Date > Shipped Date if Receipt Date is specified */
660 IF p_header_record.header_record.transaction_type <> 'CANCEL' THEN
661 IF p_header_record.header_record.expected_receipt_date IS NOT NULL THEN
662 IF p_header_record.header_record.expected_receipt_date < /* nwang: allow expected_receipt_date to be the same as shipped_date */
663 p_header_record.header_record.shipped_date THEN
664 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
665 rcv_error_pkg.set_error_message('RCV_DELIV_DATE_INVALID', p_header_record.error_record.error_message);
666 rcv_error_pkg.set_token('DELIVERY DATE', p_header_record.header_record.shipped_date);
667 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'EXPECTED_RECEIPT_DATE');
668 END IF;
669 END IF;
670
671 IF (g_asn_debug = 'Y') THEN
672 asn_debug.put_line('validated for Receipt Date > Shipped Date if Receipt Date is specified');
673 END IF;
674 END IF;
675
676 /* Validation expected_receipt_date is not missing BUG 628316 */
677 IF p_header_record.header_record.transaction_type <> 'CANCEL' THEN
678 IF p_header_record.header_record.expected_receipt_date IS NULL THEN
679 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
680 rcv_error_pkg.set_error_message('RCV_ASN_EXPECTED_RECEIPT_DATE', p_header_record.error_record.error_message);
681 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'EXPECTED_RECEIPT_DATE');
682 END IF;
683
684 IF (g_asn_debug = 'Y') THEN
685 asn_debug.put_line('validated expected_receipt_date is not missing');
686 END IF;
687 END IF;
688
689 /* Validate Receipt Number */
690 IF p_header_record.header_record.receipt_num IS NULL
691 AND p_header_record.header_record.asn_type = 'STD'
692 AND p_header_record.header_record.transaction_type <> 'CANCEL' THEN -- added for support of cancel
693 IF (g_asn_debug = 'Y') THEN
694 asn_debug.put_line('Receipt Number is mandatory for STD');
695 END IF;
696
697 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
698 rcv_error_pkg.set_error_message('RCV_RECEIPT_NUM_REQ', p_header_record.error_record.error_message);
699 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'RECEIPT_NUM');
700 END IF;
701
702 IF p_header_record.header_record.receipt_num IS NOT NULL
703 AND p_header_record.header_record.transaction_type <> 'CANCEL' THEN -- added for support of cancel
704 SELECT COUNT(*)
705 INTO x_count
706 FROM rcv_shipment_headers
707 WHERE rcv_shipment_headers.receipt_num = p_header_record.header_record.receipt_num
708 AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id;
709
710 IF x_count > 0 THEN
711 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
712 rcv_error_pkg.set_error_message('PO_PDOI_RECEIPT_NUM_UNIQUE', p_header_record.error_record.error_message);
713 rcv_error_pkg.set_token('VALUE', p_header_record.header_record.receipt_num);
714 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'RECEIPT_NUM');
715 END IF;
716
717 IF (g_asn_debug = 'Y') THEN
718 asn_debug.put_line('validated receipt number');
719 END IF;
720 END IF;
721
722 /* Validate Vendor Information */
723 IF p_header_record.header_record.vendor_id IS NULL
724 AND p_header_record.header_record.vendor_name IS NULL
725 AND p_header_record.header_record.vendor_num IS NULL THEN
726 IF (g_asn_debug = 'Y') THEN
727 asn_debug.put_line('validated vendor info is all null');
728 END IF;
729
730 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
731 rcv_error_pkg.set_error_message('PO_PDOI_COLUMN_NOT_NULL', p_header_record.error_record.error_message);
732 rcv_error_pkg.set_token('COLUMN_NAME', 'VENDOR_ID');
733 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'VENDOR_ID');
734 END IF;
735
736 vendor_record.vendor_name := p_header_record.header_record.vendor_name;
737 vendor_record.vendor_num := p_header_record.header_record.vendor_num;
738 vendor_record.vendor_id := p_header_record.header_record.vendor_id;
739 vendor_record.error_record := p_header_record.error_record;
740
741 IF (g_asn_debug = 'Y') THEN
742 asn_debug.put_line('In Vendor Validation Procedure');
743 END IF;
744
745 po_vendors_sv.validate_vendor_info(vendor_record);
746 p_header_record.error_record.error_status := vendor_record.error_record.error_status;
747
748 /* po_core_s doesn't follow error paradigm */
749 IF (vendor_record.error_record.error_message = 'VEN_DISABLED') THEN
750 IF NVL(p_header_record.header_record.asn_type,'STD') = 'STD' THEN
751 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
752 ELSE
753 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_VENDOR', p_header_record.error_record.error_message);
754 rcv_error_pkg.set_token('VALUE', vendor_record.vendor_id);
755 END IF;
756 ELSIF(vendor_record.error_record.error_message = 'VEN_HOLD') THEN
757 IF p_header_record.header_record.transaction_type = 'CANCEL'
758 OR NVL(p_header_record.header_record.asn_type,'STD') = 'STD' THEN
759 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
760 ELSE
761 rcv_error_pkg.set_error_message('PO_PO_VENDOR_ON_HOLD', p_header_record.error_record.error_message);
762 rcv_error_pkg.set_token('VALUE', vendor_record.vendor_id);
763 END IF;
764 ELSIF(vendor_record.error_record.error_message = 'VEN_ID') THEN
765 rcv_error_pkg.set_error_message('RCV_VEN_ID', p_header_record.error_record.error_message);
766 rcv_error_pkg.set_token('SUPPLIER', vendor_record.vendor_id);
767 END IF;
768
769 rcv_error_pkg.log_interface_message(p_header_record.error_record.error_status,
770 'RCV_HEADERS_INTERFACE',
771 'VENDOR_ID'
772 );
773
774 IF (g_asn_debug = 'Y') THEN
775 asn_debug.put_line('Validated vendor info');
776 END IF;
777
778 /* Validate Ship To Organization Information */
779 IF p_header_record.header_record.asn_type IN('ASN', 'ASBN', 'STD') THEN
780 ship_to_org_record.organization_code := p_header_record.header_record.ship_to_organization_code;
781 ship_to_org_record.organization_id := p_header_record.header_record.ship_to_organization_id;
782 ship_to_org_record.error_record := p_header_record.error_record;
783
784 IF (g_asn_debug = 'Y') THEN
785 asn_debug.put_line('In Validate Ship to Organization Procedure');
786 END IF;
787
788 po_orgs_sv.validate_org_info(ship_to_org_record);
789 p_header_record.error_record.error_status := ship_to_org_record.error_record.error_status;
790
791 /* po_core_s doesn't follow error paradigm */
792 IF (ship_to_org_record.error_record.error_message = 'ORG_ID') THEN
793 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_SHIP_TO_ORG_ID', p_header_record.error_record.error_message);
794 rcv_error_pkg.set_token('VALUE', ship_to_org_record.organization_id);
795 ELSIF(ship_to_org_record.error_record.error_message = 'ORG_DISABLED') THEN
796 IF p_header_record.header_record.transaction_type = 'CANCEL' THEN
797 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
798 ELSE
799 rcv_error_pkg.set_error_message('RCV_SHIPTO_ORG_DISABLED', p_header_record.error_record.error_message);
800 rcv_error_pkg.set_token('ORGANIZATION', ship_to_org_record.organization_id);
801 END IF;
802 END IF;
803
804 rcv_error_pkg.log_interface_message(p_header_record.error_record.error_status,
805 'RCV_HEADERS_INTERFACE',
806 'SHIP_TO_ORGANIZATION_ID'
807 );
808
809 IF (g_asn_debug = 'Y') THEN
810 asn_debug.put_line('validated ship to organization info');
811 END IF;
812 END IF;
813
814 /* Bug# 3662698.
815 Verify if any of the lines tied to the header have destination organization
816 different to that of the header's org (which is either populated or derived).
817 */
818 IF ( p_header_record.header_record.asn_type IN('ASN', 'ASBN', 'STD')
819 AND p_header_record.header_record.transaction_type <> 'CANCEL') THEN
820 /* Check if there is atleast one RTI record of this header with a
821 different org than the header's org. Here we consider those
822 RTI records which have to_organization_code or to_organization_id
823 as not null. Later below we check for those RTI records which have
824 to_organization_code and to_organization_id as null.
825 This logic is followed keeping in view of the performance problems.
826 */
827 IF (p_header_record.header_record.ship_to_organization_code IS NOT NULL) THEN
828 IF (g_asn_debug = 'Y') THEN
829 asn_debug.put_line('Checking if any RTI has different destn org than that of the header');
830 END IF;
831
832 SELECT COUNT(*)
833 INTO x_count
834 FROM rcv_transactions_interface rti,
835 rcv_headers_interface rhi
836 WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id
837 AND rhi.header_interface_id = rti.header_interface_id
838 AND ( ( rti.to_organization_code IS NOT NULL
839 AND rti.to_organization_code <> p_header_record.header_record.ship_to_organization_code)
840 OR ( rti.to_organization_id IS NOT NULL
841 AND rti.to_organization_id <> p_header_record.header_record.ship_to_organization_id)
842 );
843
844 IF x_count >= 1 THEN
845 IF (g_asn_debug = 'Y') THEN
846 asn_debug.put_line('Atleast one of the RTIs has a different org id/code than that of the header');
847 END IF;
848
849 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
850 rcv_error_pkg.set_error_message('RCV_MUL_DESTN_ORGS_FOR_LINES', p_header_record.error_record.error_message);
851 rcv_error_pkg.set_token('VALUE', p_header_record.header_record.ship_to_organization_id);
852 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'SHIP_TO_ORGANIZATION_ID');
853 ELSE
854 IF (g_asn_debug = 'Y') THEN
855 asn_debug.put_line('In the ELSE part');
856 END IF;
857
858 /* Check if there is atleast one RTI record in this header with a different
859 ship to org than the header's org. Here we consider those RTI records
860 which have to_organization_code and to_rganization_id as null and
861 ship_to_location_id as not null. Records with all the above four columns
862 as null need not be checked as header's org will be set to the line's org
863 during the line level organization derivation.
864 */
865 SELECT COUNT(*)
866 INTO x_count
867 FROM rcv_transactions_interface rti,
868 hr_locations_all hl, --Bug 5219141. Replace hr_locations by hr_locations_all
869 mtl_parameters org --Replaced org_organization_definitions
870 WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id
871 AND rti.to_organization_code IS NULL
872 AND rti.to_organization_id IS NULL
873 AND rti.ship_to_location_id IS NOT NULL
874 AND rti.ship_to_location_id = hl.location_id
875 AND hl.inventory_organization_id = org.organization_id
876 AND org.organization_code <> p_header_record.header_record.ship_to_organization_code;
877
878 IF (g_asn_debug = 'Y') THEN
879 asn_debug.put_line('Count is ' || TO_CHAR(x_count));
880 END IF;
881
882 /* Check if there is atleast one RTI record in this header with a different
883 ship to org than the header's org. Here we consider those RTI records
884 which have to_organization_code and to_rganization_id as null and
885 ship_to_location_code as not null. A seperate sql is written using
886 ship_location_code instead of adding it to the the WHERE caluse of the
887 above sql to avoid full table scans on hr_locations.
888 */
889 IF x_count = 0 THEN
890 SELECT COUNT(*)
891 INTO x_count
892 FROM rcv_transactions_interface rti,
893 hr_locations_all hl, --Bug 5219141. Replace hr_locations by hr_locations_all
894 mtl_parameters org --Replaced org_organization_definitions
895 WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id
896 AND rti.to_organization_code IS NULL
897 AND rti.to_organization_id IS NULL
898 AND rti.ship_to_location_code IS NOT NULL
899 AND rti.ship_to_location_code = hl.location_code
900 AND hl.inventory_organization_id = org.organization_id
901 AND org.organization_code <> p_header_record.header_record.ship_to_organization_code;
902 END IF;
903
904 IF x_count >= 1 THEN
905 IF (g_asn_debug = 'Y') THEN
906 asn_debug.put_line('For one of the RTI records a different org id/code is derived');
907 END IF;
908
909 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
910 rcv_error_pkg.set_error_message('RCV_MUL_DESTN_ORGS_FOR_LINES', p_header_record.error_record.error_message);
911 rcv_error_pkg.set_token('VALUE', p_header_record.header_record.ship_to_organization_id);
912 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE', 'SHIP_TO_ORGANIZATION_ID');
913 END IF;
914 END IF;
915
916 IF (g_asn_debug = 'Y') THEN
917 asn_debug.put_line('Validated ship to org of all the RTIs tied to the header');
918 END IF;
919 END IF;
920 END IF; --End of bug# 3662698.
921
922 /* validate from organization information */
923 IF p_header_record.header_record.transaction_type <> 'CANCEL' THEN
924 IF from_org_record.organization_code IS NOT NULL
925 OR from_org_record.organization_id IS NOT NULL THEN
926 from_org_record.organization_code := p_header_record.header_record.from_organization_code;
927 from_org_record.organization_id := p_header_record.header_record.from_organization_id;
928 from_org_record.error_record := p_header_record.error_record;
929
930 IF (g_asn_debug = 'Y') THEN
931 asn_debug.put_line('In Validate From Organization Procedure');
932 END IF;
933
934 po_orgs_sv.validate_org_info(from_org_record);
935 p_header_record.error_record.error_status := from_org_record.error_record.error_status;
936
937 /* po_core_s doesn't follow error paradigm */
938 IF (from_org_record.error_record.error_message = 'ORG_ID') THEN
939 rcv_error_pkg.set_error_message('RCV_FROM_ORG_ID', p_header_record.error_record.error_message);
940 rcv_error_pkg.set_token('VALUE', from_org_record.organization_id);
941 ELSIF(from_org_record.error_record.error_message = 'ORG_DISABLED') THEN
942 IF p_header_record.header_record.transaction_type = 'CANCEL' THEN
943 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
944 ELSE
945 rcv_error_pkg.set_error_message('RCV_FROM_ORG_DISABLED', p_header_record.error_record.error_message);
946 rcv_error_pkg.set_token('ORGANIZATION', from_org_record.organization_id);
947 END IF;
948 END IF;
949
950 rcv_error_pkg.log_interface_message(p_header_record.error_record.error_status,
951 'RCV_HEADERS_INTERFACE',
952 'SHIP_TO_ORGANIZATION_ID'
953 );
954 END IF;
955 END IF;
956
957 /* validate vendor site information */
958 IF p_header_record.error_record.error_status IN('S', 'W')
959 AND ( p_header_record.header_record.vendor_site_code IS NOT NULL
960 OR p_header_record.header_record.vendor_site_id IS NOT NULL) THEN
961 vendor_site_record.vendor_site_code := p_header_record.header_record.vendor_site_code;
962 vendor_site_record.vendor_id := p_header_record.header_record.vendor_id;
963 vendor_site_record.vendor_site_id := p_header_record.header_record.vendor_site_id;
964 vendor_site_record.organization_id := NULL;
965 vendor_site_record.error_record := p_header_record.error_record;
966
967 IF (g_asn_debug = 'Y') THEN
968 asn_debug.put_line('In Validate Vendor Site Procedure');
969 END IF;
970
971 po_vendor_sites_sv.validate_vendor_site_info(vendor_site_record);
972
973 /* if supplier site is not defined as pay on receipt site then
974 the validate_vendor_site proc returns error_message =
975 'VEN_SITE_NOT_POR_SITE'. This error is applicable only for asn_type=ASBN.
976 Also invoice_status_code needs to be set to a predefined value in case we hit this
977 error as invoice cannot be auto created.
978
979 In case asn_type = ASN then we reset the error_status and message */
980
981 /*
982 * Bug #933119
983 * When the hold_all_payments flag is set for a vendor site,
984 * the pre-processor used to error out which was incorrect. This error
985 * is applicable only for asn_type=ASBN. In case asn_type=ASN then we
986 * now we reset the error_status and message.
987 */
988 IF ( vendor_site_record.error_record.error_message = 'VEN_SITE_NOT_POR_SITE'
989 OR vendor_site_record.error_record.error_message = 'VEN_SITE_HOLD_PMT') THEN
990 IF p_header_record.header_record.asn_type = 'ASBN'
991 AND p_header_record.header_record.transaction_type <> 'CANCEL' THEN
992 vendor_site_record.error_record.error_message := 'PO_INV_CR_INVALID_PAY_SITE';
993 vendor_site_record.error_record.error_status := rcv_error_pkg.g_ret_sts_warning;
994 rcv_error_pkg.set_error_message('PO_INV_CR_INVALID_PAY_SITE', vendor_site_record.error_record.error_message);
995 rcv_error_pkg.set_token('VENDOR_SITE_ID', vendor_site_record.vendor_site_id);
996 p_header_record.header_record.invoice_status_code := 'RCV_ASBN_NO_AUTO_INVOICE';
997 ELSE
998 vendor_site_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
999 vendor_site_record.error_record.error_message := NULL;
1000 END IF;
1001 ELSIF vendor_site_record.error_record.error_message = 'VEN_SITE_DISABLED' THEN
1002 /* Fix for bug 2830103.
1003 Validation for inactive vendor site should happen only for
1004 ASNs and ASBNs. Hence adding the following IF condition
1005 below so that no validation happens for STD receipts.
1006 */
1007 IF NVL(p_header_record.header_record.asn_type, 'STD') IN('ASN', 'ASBN') THEN
1008 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_VENDOR_SITE', vendor_site_record.error_record.error_message);
1009 rcv_error_pkg.set_token('VALUE', vendor_site_record.vendor_site_id);
1010 ELSE
1011 vendor_site_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
1012 vendor_site_record.error_record.error_message := NULL;
1013 END IF;
1014 ELSIF vendor_site_record.error_record.error_message IN('VEN_SITE_NOT_PURCH', 'VEN_SITE_ID') THEN
1015 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_VENDOR_SITE', vendor_site_record.error_record.error_message);
1016 rcv_error_pkg.set_token('VALUE', vendor_site_record.vendor_site_id);
1017 ELSIF vendor_site_record.error_record.error_status <> rcv_error_pkg.g_ret_sts_success THEN
1018 rcv_error_pkg.set_error_message(vendor_site_record.error_record.error_message, vendor_site_record.error_record.error_message); -- to set any other errors
1019 END IF;
1020
1021 p_header_record.error_record := vendor_site_record.error_record;
1022 rcv_error_pkg.log_interface_message(p_header_record.error_record.error_status,
1023 'RCV_HEADERS_INTERFACE',
1024 'VENDOR_ID'
1025 );
1026
1027 IF (g_asn_debug = 'Y') THEN
1028 asn_debug.put_line('Validated vendor site info');
1029 END IF;
1030 END IF;
1031
1032 /* Validate Location Information */
1033 IF p_header_record.header_record.transaction_type <> 'CANCEL'
1034 AND p_header_record.header_record.asn_type IN('ASN', 'ASBN', 'STD')
1035 AND ( p_header_record.header_record.location_code IS NOT NULL
1036 OR p_header_record.header_record.location_id IS NOT NULL) THEN
1037 loc_record.location_code := p_header_record.header_record.location_code;
1038 loc_record.location_id := p_header_record.header_record.location_id;
1039 loc_record.organization_id := p_header_record.header_record.ship_to_organization_id;
1040 loc_record.error_record := p_header_record.error_record;
1041
1042 IF (g_asn_debug = 'Y') THEN
1043 asn_debug.put_line('In Validate Location Code Procedure');
1044 END IF;
1045
1046 po_locations_s.validate_location_info(loc_record);
1047
1048 IF loc_record.error_record.error_message IN('LOC_ID', 'LOC_DISABLED') THEN
1049 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_SHIP_TO_LOC_ID', loc_record.error_record.error_message);
1050 rcv_error_pkg.set_token('VALUE', loc_record.location_id);
1051 ELSIF loc_record.error_record.error_message = 'LOC_NOT_RCV_SITE' THEN
1052 rcv_error_pkg.set_error_message('RCV_LOC_NOT_RCV_SITE', loc_record.error_record.error_message);
1053 rcv_error_pkg.set_token('LOCATION', loc_record.location_id);
1054 ELSIF loc_record.error_record.error_message = 'LOC_NOT_IN_ORG' THEN
1055 rcv_error_pkg.set_error_message('RCV_LOC_NOT_IN_ORG', loc_record.error_record.error_message);
1056 rcv_error_pkg.set_token('LOCATION', loc_record.location_id);
1057 rcv_error_pkg.set_token('ORGANIZATION', loc_record.organization_id);
1058 ELSIF loc_record.error_record.error_status <> rcv_error_pkg.g_ret_sts_success THEN
1059 rcv_error_pkg.set_error_message(loc_record.error_record.error_message, loc_record.error_record.error_message); -- to set any other errors
1060 END IF;
1061
1062 p_header_record.error_record := loc_record.error_record;
1063 rcv_error_pkg.log_interface_message(p_header_record.error_record.error_status,
1064 'RCV_HEADERS_INTERFACE',
1065 'LOCATION_ID'
1066 );
1067
1068 IF (g_asn_debug = 'Y') THEN
1069 asn_debug.put_line(loc_record.error_record.error_status);
1070 asn_debug.put_line(loc_record.error_record.error_message);
1071 asn_debug.put_line('Validated location info');
1072 END IF;
1073 END IF;
1074
1075 /* Validate Payment Terms Information */
1076 IF ( p_header_record.header_record.payment_terms_name IS NOT NULL
1077 OR p_header_record.header_record.payment_terms_id IS NOT NULL)
1078 AND p_header_record.header_record.transaction_type <> 'CANCEL' THEN
1079 pay_record.payment_term_id := p_header_record.header_record.payment_terms_id;
1080 pay_record.payment_term_name := p_header_record.header_record.payment_terms_name;
1081 pay_record.error_record := p_header_record.error_record;
1082
1083 IF (g_asn_debug = 'Y') THEN
1084 asn_debug.put_line('In Validate Payment Terms ');
1085 END IF;
1086
1087 po_terms_sv.validate_payment_terms_info(pay_record);
1088
1089 IF pay_record.error_record.error_message = 'PAY_TERMS_DISABLED' THEN
1090 IF p_header_record.header_record.asn_type = 'ASBN' THEN
1091 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_PAY_TERMS', pay_record.error_record.error_message);
1092 rcv_error_pkg.set_token('VALUE', pay_record.payment_term_id);
1093 ELSE
1094 pay_record.error_record.error_message := NULL;
1095 pay_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
1096 END IF;
1097 ELSIF pay_record.error_record.error_message = 'PAY_TERMS_ID' THEN
1098 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_PAY_TERMS', pay_record.error_record.error_message);
1099 rcv_error_pkg.set_token('VALUE', pay_record.payment_term_id);
1100 ELSIF pay_record.error_record.error_status <> rcv_error_pkg.g_ret_sts_success THEN
1101 rcv_error_pkg.set_error_message(pay_record.error_record.error_message, pay_record.error_record.error_message); -- to set any other errors
1102 END IF;
1103
1104 p_header_record.error_record := pay_record.error_record;
1105 rcv_error_pkg.log_interface_message(p_header_record.error_record.error_status,
1106 'RCV_HEADERS_INTERFACE',
1107 'PAYMENT_TERM_ID'
1108 );
1109
1110 IF (g_asn_debug = 'Y') THEN
1111 asn_debug.put_line('Validated payment info');
1112 END IF;
1113 END IF;
1114
1115 /* validate receiver information */
1116 IF p_header_record.header_record.transaction_type <> 'CANCEL'
1117 AND p_header_record.header_record.auto_transact_code = 'RECEIVE'
1118 AND ( p_header_record.header_record.employee_name IS NOT NULL
1119 OR p_header_record.header_record.employee_id IS NOT NULL) THEN
1120 emp_record.employee_name := p_header_record.header_record.employee_name;
1121 emp_record.employee_id := p_header_record.header_record.employee_id;
1122 emp_record.error_record := p_header_record.error_record;
1123
1124 IF (g_asn_debug = 'Y') THEN
1125 asn_debug.put_line('In Validate Receiver Information');
1126 END IF;
1127
1128 po_employees_sv.validate_employee_info(emp_record);
1129
1130 IF (g_asn_debug = 'Y') THEN
1131 asn_debug.put_line(emp_record.error_record.error_status);
1132 END IF;
1133
1134 IF emp_record.error_record.error_message = 'RECEIVER_ID' THEN
1135 rcv_error_pkg.set_error_message('RCV_RECEIVER_ID', emp_record.error_record.error_message);
1136 rcv_error_pkg.set_token('NAME', emp_record.employee_name);
1137 ELSIF emp_record.error_record.error_status <> rcv_error_pkg.g_ret_sts_success THEN
1138 rcv_error_pkg.set_error_message(emp_record.error_record.error_message, emp_record.error_record.error_message); -- to set any other errors
1139 END IF;
1140
1141 p_header_record.error_record := emp_record.error_record;
1142 rcv_error_pkg.log_interface_message(p_header_record.error_record.error_status,
1143 'RCV_HEADERS_INTERFACE',
1144 'EMPLOYEE_ID'
1145 );
1146
1147 IF (g_asn_debug = 'Y') THEN
1148 asn_debug.put_line('Validated receiver info');
1149 END IF;
1150 END IF;
1151
1152 /* validate freight carrier information */
1153 /* ASN and ASBN, al transaction_types except CANCEL */
1154 /* Carrier is specified */
1155 IF p_header_record.header_record.transaction_type <> 'CANCEL'
1156 AND p_header_record.header_record.freight_carrier_code IS NOT NULL THEN
1157 freight_record.freight_carrier_code := p_header_record.header_record.freight_carrier_code;
1158 freight_record.organization_id := p_header_record.header_record.ship_to_organization_id;
1159 freight_record.error_record := p_header_record.error_record;
1160
1161 IF (g_asn_debug = 'Y') THEN
1162 asn_debug.put_line('In Validate Freight Carrier Information');
1163 END IF;
1164
1165 po_terms_sv.validate_freight_carrier_info(freight_record);
1166
1167 IF freight_record.error_record.error_message IN('CARRIER_DISABLED', 'CARRIER_INVALID') THEN
1168 rcv_error_pkg.set_error_message('RCV_CARRIER_DISABLED', freight_record.error_record.error_message);
1169 rcv_error_pkg.set_token('CARRIER', freight_record.freight_carrier_code);
1170 ELSIF freight_record.error_record.error_status <> rcv_error_pkg.g_ret_sts_success THEN
1171 rcv_error_pkg.set_error_message(freight_record.error_record.error_message, freight_record.error_record.error_message); -- to set any other errors
1172 END IF;
1173
1174 p_header_record.error_record := freight_record.error_record;
1175 rcv_error_pkg.log_interface_message(p_header_record.error_record.error_status,
1176 'RCV_HEADERS_INTERFACE',
1177 'FREIGHT_CARRIER_CODE'
1178 );
1179
1180 IF (g_asn_debug = 'Y') THEN
1181 asn_debug.put_line('Validated freight carrier info');
1182 END IF;
1183 END IF;
1184
1185 /* Validate Invoice Amount > 0 */
1186 /* Invoice amount Vs Supplier Site Limit */
1187 IF p_header_record.header_record.transaction_type <> 'CANCEL'
1188 AND p_header_record.header_record.asn_type = 'ASBN' THEN
1189 invoice_record.total_invoice_amount := p_header_record.header_record.total_invoice_amount;
1190 invoice_record.vendor_id := p_header_record.header_record.vendor_id;
1191 invoice_record.vendor_site_id := p_header_record.header_record.vendor_site_id;
1192 invoice_record.error_record := p_header_record.error_record;
1193 rcv_headers_interface_sv.validate_invoice_amount(invoice_record);
1194 p_header_record.error_record := invoice_record.error_record;
1195 rcv_error_pkg.log_interface_message(p_header_record.error_record.error_status,
1196 'RCV_HEADERS_INTERFACE',
1197 'TOTAL_INVOICE_AMOUNT'
1198 );
1199
1200 IF (g_asn_debug = 'Y') THEN
1201 asn_debug.put_line('Validated invoice amount');
1202 END IF;
1203 END IF;
1204
1205 /* Validate that both Invoice number and shipment number are not
1206 missing */
1207 IF p_header_record.header_record.transaction_type <> 'CANCEL'
1208 AND p_header_record.header_record.asn_type = 'ASBN' THEN
1209 IF p_header_record.header_record.shipment_num IS NULL
1210 AND -- Should we assign shipment_num to null.invoice_num
1211 p_header_record.header_record.invoice_num IS NULL THEN
1212 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1213 rcv_error_pkg.set_error_message('RCV_ASBN_INVOICE_NUM', p_header_record.error_record.error_message);
1214 END IF;
1215
1216 rcv_error_pkg.log_interface_message(p_header_record.error_record.error_status,
1217 'RCV_HEADERS_INTERFACE',
1218 'SHIPMENT_NUM'
1219 );
1220
1221 IF (g_asn_debug = 'Y') THEN
1222 asn_debug.put_line('Validated invoice number/shipment number are not missing');
1223 END IF;
1224 END IF;
1225
1226 /* Validate invoice_date is not missing */
1227
1228 /* bug 628316 make sure invoice_date is not missing for ASBN */
1229 IF p_header_record.header_record.transaction_type <> 'CANCEL'
1230 AND p_header_record.header_record.asn_type = 'ASBN' THEN
1231 IF p_header_record.header_record.invoice_date IS NULL THEN
1232 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1233 rcv_error_pkg.set_error_message('RCV_ASBN_INVOICE_DATE', p_header_record.error_record.error_message);
1234 END IF;
1235
1236 rcv_error_pkg.log_interface_message(p_header_record.error_record.error_status,
1237 'RCV_HEADERS_INTERFACE',
1238 'INVOICE_DATE'
1239 );
1240
1241 IF (g_asn_debug = 'Y') THEN
1242 asn_debug.put_line('Validated invoice date is not missing');
1243 END IF;
1244 END IF;
1245
1246 /* Validate Invoice Tax Code */
1247 IF p_header_record.header_record.transaction_type <> 'CANCEL'
1248 AND p_header_record.header_record.asn_type = 'ASBN' THEN
1249 IF p_header_record.header_record.tax_name IS NOT NULL THEN
1250 tax_record.tax_name := p_header_record.header_record.tax_name;
1251 tax_record.tax_amount := p_header_record.header_record.tax_amount;
1252 tax_record.error_record := p_header_record.error_record;
1253 po_locations_s.validate_tax_info(tax_record);
1254
1255 IF tax_record.error_record.error_message IN('TAX_CODE_INVALID', 'TAX_CODE_DISABLED') THEN
1256 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_TAX_NAME', tax_record.error_record.error_message);
1257 rcv_error_pkg.set_token('VALUE', tax_record.tax_name);
1258 ELSIF tax_record.error_record.error_status <> rcv_error_pkg.g_ret_sts_success THEN
1259 rcv_error_pkg.set_error_message(tax_record.error_record.error_message, tax_record.error_record.error_message); -- to set any other errors
1260 END IF;
1261
1262 p_header_record.error_record := tax_record.error_record;
1263 rcv_error_pkg.log_interface_message(p_header_record.error_record.error_status,
1264 'RCV_HEADERS_INTERFACE',
1265 'TAX_NAME'
1266 );
1267 END IF;
1268
1269 IF (g_asn_debug = 'Y') THEN
1270 asn_debug.put_line('Validated tax info');
1271 END IF;
1272 END IF;
1273
1274 /* Validations on shipment number */
1275 rcv_core_s.validate_shipment_number(p_header_record);
1276 rcv_error_pkg.log_interface_message(p_header_record.error_record.error_status,
1277 'RCV_HEADERS_INTERFACE',
1278 'SHIPMENT_NUM'
1279 );
1280
1281 IF (g_asn_debug = 'Y') THEN
1282 asn_debug.put_line('Validations for shipment_number ' || p_header_record.header_record.shipment_num);
1283 END IF;
1284
1285 /* Validate gross_weight_uom_code */
1286
1287 /* Validate net_weight_uom_code */
1288
1289 /* Validate tare_weight_uom_code */
1290
1291 /* Validate Carrier_method */
1292
1293 /* Validate Special handling code */
1294
1295 /* Validate Hazard Code */
1296
1297 /* Validate Hazard Class */
1298
1299 /* Validate Freight Terms */
1300
1301 /* Validate Excess Transportation Reason */
1302
1303 /* Validate Excess Transportation Responsible */
1304
1305 /* Validate Invoice Status Code */
1306 IF (g_asn_debug = 'Y') THEN
1307 asn_debug.put_line('Other Validations');
1308 END IF;
1309 EXCEPTION
1310 WHEN rcv_error_pkg.e_fatal_error THEN
1311 NULL;
1312 END validate_shipment_header;
1313
1314 PROCEDURE validate_invoice_amount(
1315 p_inv_rec IN OUT NOCOPY rcv_shipment_header_sv.invrectype
1316 ) IS
1317 CURSOR c IS
1318 SELECT invoice_amount_limit
1319 FROM po_vendor_sites_all --Bug 5219141 Replace po_vendor_sites by po_vendor_sites_all
1320 WHERE po_vendor_sites_all.vendor_site_id = p_inv_rec.vendor_site_id
1321 AND po_vendor_sites_all.vendor_id = p_inv_rec.vendor_id;
1322
1323 x_inv_rec c%ROWTYPE;
1324 BEGIN
1325 /*Commenting out the following check because if this is called
1326 from the web supliers, the amount will be null. This amount is
1327 explicitly calculated before creating the invoice header .*/
1328 IF p_inv_rec.vendor_site_id IS NOT NULL THEN
1329 OPEN c;
1330 FETCH c INTO x_inv_rec;
1331
1332 IF NVL(x_inv_rec.invoice_amount_limit, 0) > 0 THEN
1333 IF x_inv_rec.invoice_amount_limit < p_inv_rec.total_invoice_amount THEN
1334 p_inv_rec.error_record.error_status := rcv_error_pkg.g_ret_sts_error;
1335 rcv_error_pkg.set_error_message('RCV_ASBN_INVOICE_AMT_LIMIT', p_inv_rec.error_record.error_message);
1336 rcv_error_pkg.set_token('AMOUNT', p_inv_rec.total_invoice_amount);
1337 END IF;
1338 END IF;
1339 END IF;
1340 EXCEPTION
1341 WHEN OTHERS THEN
1342 rcv_error_pkg.set_sql_error_message('validate_invoice_amount', '000');
1343 p_inv_rec.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1344 p_inv_rec.error_record.error_message := rcv_error_pkg.get_last_message;
1345 END validate_invoice_amount;
1346
1347 PROCEDURE insert_shipment_header(
1348 p_header_record IN OUT NOCOPY rcv_shipment_header_sv.headerrectype
1349 ) IS
1350 BEGIN
1351 -- Set asn_type to null if asn_type is STD as the UI gets confused
1352
1353 IF NVL(p_header_record.header_record.asn_type, 'STD') = 'STD' THEN
1354 p_header_record.header_record.asn_type := NULL;
1355 END IF;
1356
1357 /* Bug - 1086088 - Ship_to_org_id needs to get populated in the
1358 * RCV_SHIPMENT_HEADERS table */
1359 INSERT INTO rcv_shipment_headers
1360 (shipment_header_id,
1361 last_update_date,
1362 last_updated_by,
1363 creation_date,
1364 created_by,
1365 last_update_login,
1366 receipt_source_code,
1367 vendor_id,
1368 vendor_site_id,
1369 organization_id,
1370 shipment_num,
1371 receipt_num,
1372 ship_to_location_id,
1373 ship_to_org_id,
1374 bill_of_lading,
1375 packing_slip,
1376 shipped_date,
1377 freight_carrier_code,
1378 expected_receipt_date,
1379 employee_id,
1380 num_of_containers,
1381 waybill_airbill_num,
1382 comments,
1383 attribute_category,
1384 attribute1,
1385 attribute2,
1386 attribute3,
1387 attribute4,
1388 attribute5,
1389 attribute6,
1390 attribute7,
1391 attribute8,
1392 attribute9,
1393 attribute10,
1394 attribute11,
1395 attribute12,
1396 attribute13,
1397 attribute14,
1398 attribute15,
1399 ussgl_transaction_code,
1400 government_context,
1401 request_id,
1402 program_application_id,
1403 program_id,
1404 program_update_date,
1405 asn_type,
1406 edi_control_num,
1407 notice_creation_date,
1408 gross_weight,
1409 gross_weight_uom_code,
1410 net_weight,
1411 net_weight_uom_code,
1412 tar_weight,
1413 tar_weight_uom_code,
1414 packaging_code,
1415 carrier_method,
1416 carrier_equipment,
1417 carrier_equipment_num,
1418 carrier_equipment_alpha,
1419 special_handling_code,
1420 hazard_code,
1421 hazard_class,
1422 hazard_description,
1423 freight_terms,
1424 freight_bill_number,
1425 invoice_date,
1426 invoice_amount,
1427 tax_name,
1428 tax_amount,
1429 freight_amount,
1430 invoice_status_code,
1431 asn_status,
1432 currency_code,
1433 conversion_rate_type,
1434 conversion_rate,
1435 conversion_date,
1436 payment_terms_id,
1437 invoice_num,
1438 ship_from_location_id
1439 )
1440 VALUES (p_header_record.header_record.receipt_header_id,
1441 p_header_record.header_record.last_update_date,
1442 p_header_record.header_record.last_updated_by,
1443 p_header_record.header_record.creation_date,
1444 p_header_record.header_record.created_by,
1445 p_header_record.header_record.last_update_login,
1446 p_header_record.header_record.receipt_source_code,
1447 p_header_record.header_record.vendor_id,
1448 p_header_record.header_record.vendor_site_id,
1449 TO_NUMBER(NULL), -- this is the from organization id and shld be null instead of ship_to_org_id
1450 p_header_record.header_record.shipment_num,
1451 p_header_record.header_record.receipt_num,
1452 p_header_record.header_record.location_id,
1453 p_header_record.header_record.ship_to_organization_id,
1454 p_header_record.header_record.bill_of_lading,
1455 p_header_record.header_record.packing_slip,
1456 p_header_record.header_record.shipped_date,
1457 p_header_record.header_record.freight_carrier_code,
1458 p_header_record.header_record.expected_receipt_date,
1459 p_header_record.header_record.employee_id,
1460 p_header_record.header_record.num_of_containers,
1461 p_header_record.header_record.waybill_airbill_num,
1462 p_header_record.header_record.comments,
1463 p_header_record.header_record.attribute_category,
1464 p_header_record.header_record.attribute1,
1465 p_header_record.header_record.attribute2,
1466 p_header_record.header_record.attribute3,
1467 p_header_record.header_record.attribute4,
1468 p_header_record.header_record.attribute5,
1469 p_header_record.header_record.attribute6,
1470 p_header_record.header_record.attribute7,
1471 p_header_record.header_record.attribute8,
1472 p_header_record.header_record.attribute9,
1473 p_header_record.header_record.attribute10,
1474 p_header_record.header_record.attribute11,
1475 p_header_record.header_record.attribute12,
1476 p_header_record.header_record.attribute13,
1477 p_header_record.header_record.attribute14,
1478 p_header_record.header_record.attribute15,
1479 p_header_record.header_record.usggl_transaction_code,
1480 NULL, -- p_header_record.header_record.Government_Context
1481 fnd_global.conc_request_id,
1482 fnd_global.prog_appl_id,
1483 fnd_global.conc_program_id,
1484 x_sysdate,
1485 p_header_record.header_record.asn_type,
1486 p_header_record.header_record.edi_control_num,
1487 p_header_record.header_record.notice_creation_date,
1488 p_header_record.header_record.gross_weight,
1489 p_header_record.header_record.gross_weight_uom_code,
1490 p_header_record.header_record.net_weight,
1491 p_header_record.header_record.net_weight_uom_code,
1492 p_header_record.header_record.tar_weight,
1493 p_header_record.header_record.tar_weight_uom_code,
1494 p_header_record.header_record.packaging_code,
1495 p_header_record.header_record.carrier_method,
1496 p_header_record.header_record.carrier_equipment,
1497 NULL, -- p_header_record.header_record.Carrier_Equipment_Num
1498 NULL, -- p_header_record.header_record.Carrier_Equipment_Alpha
1499 p_header_record.header_record.special_handling_code,
1500 p_header_record.header_record.hazard_code,
1501 p_header_record.header_record.hazard_class,
1502 p_header_record.header_record.hazard_description,
1503 p_header_record.header_record.freight_terms,
1504 p_header_record.header_record.freight_bill_number,
1505 p_header_record.header_record.invoice_date,
1506 p_header_record.header_record.total_invoice_amount,
1507 p_header_record.header_record.tax_name,
1508 p_header_record.header_record.tax_amount,
1509 p_header_record.header_record.freight_amount,
1510 p_header_record.header_record.invoice_status_code,
1511 NULL, -- p_header_record.header_record.Asn_Status
1512 p_header_record.header_record.currency_code,
1513 p_header_record.header_record.conversion_rate_type,
1514 p_header_record.header_record.conversion_rate,
1515 p_header_record.header_record.conversion_rate_date,
1516 p_header_record.header_record.payment_terms_id,
1517 p_header_record.header_record.invoice_num,
1518 p_header_record.header_record.ship_from_location_id
1519 );
1520 EXCEPTION
1521 WHEN OTHERS THEN
1522 rcv_error_pkg.set_sql_error_message('insert_shipment_header', '000');
1523 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1524 p_header_record.error_record.error_message := rcv_error_pkg.get_last_message;
1525 END insert_shipment_header;
1526
1527 /*===========================================================================+
1528 | |
1529 | PROCEDURE NAME: derive_ship_to_org_from_rti() |
1530 | |
1531 +===========================================================================*/
1532 PROCEDURE derive_ship_to_org_from_rti(
1533 p_header_record IN OUT NOCOPY rcv_shipment_header_sv.headerrectype
1534 ) IS
1535 x_header_interface_id NUMBER;
1536 x_to_organization_code VARCHAR2(3);
1537 BEGIN
1538 x_header_interface_id := p_header_record.header_record.header_interface_id;
1539
1540 IF (g_asn_debug = 'Y') THEN
1541 asn_debug.put_line('No ship to org specified at the header');
1542 asn_debug.put_line('Trying to retrieve from lines');
1543 END IF;
1544
1545 SELECT MAX(rti.to_organization_code)
1546 INTO x_to_organization_code
1547 FROM rcv_transactions_interface rti
1548 WHERE rti.header_interface_id = x_header_interface_id;
1549
1550 /* Bug# 1465730 - If Ship To Organization Code is not specified at lines
1551 * then derive it from the To Organization Id and if this is also not
1552 * specified then derive it from Ship To Location Code/Id which ever is
1553 * specified. */
1554 IF (x_to_organization_code IS NULL) THEN
1555 IF (g_asn_debug = 'Y') THEN
1556 asn_debug.put_line('No ship to org specified at the lines either');
1557 asn_debug.put_line('Trying to retrieve from to_organization_id');
1558 END IF;
1559
1560 /* ksareddy RVCTP performance fix 2481798 - select from mtl_parameters instead
1561 SELECT MAX(ORG.ORGANIZATION_CODE)
1562 INTO X_TO_ORGANIZATION_CODE
1563 FROM RCV_TRANSACTIONS_INTERFACE RTI,
1564 ORG_ORGANIZATION_DEFINITIONS ORG
1565 WHERE RTI.HEADER_INTERFACE_ID = X_HEADER_INTERFACE_ID
1566 AND ORG.ORGANIZATION_ID = RTI.TO_ORGANIZATION_ID;
1567 */
1568 SELECT MAX(mtl.organization_code)
1569 INTO x_to_organization_code
1570 FROM rcv_transactions_interface rti,
1571 mtl_parameters mtl
1572 WHERE rti.header_interface_id = x_header_interface_id
1573 AND mtl.organization_id = rti.to_organization_id;
1574 END IF;
1575
1576 IF (x_to_organization_code IS NULL) THEN
1577 IF (g_asn_debug = 'Y') THEN
1578 asn_debug.put_line('Trying to retrieve from ship to location');
1579 END IF;
1580
1581 /* Bug# 3924530 FP from 11i9 fix. Replaced the sql statement below with a
1582 * new one where we select the organization_code from table MTL_PARAMETERS
1583 * instead of the expensive nonmergible view ORG_ORGANIZATION_DEFINITIONS.
1584
1585 SELECT MAX(org.organization_code)
1586 INTO x_to_organization_code
1587 FROM rcv_transactions_interface rti,
1588 hr_locations hl,
1589 org_organization_definitions org
1590 WHERE rti.header_interface_id = x_header_interface_id
1591 AND ( rti.ship_to_location_code = hl.location_code
1592 OR rti.ship_to_location_id = hl.location_id)
1593 AND hl.inventory_organization_id = org.organization_id;*/
1594
1595 SELECT MAX(MTL.ORGANIZATION_CODE)
1596 INTO X_TO_ORGANIZATION_CODE
1597 FROM RCV_TRANSACTIONS_INTERFACE RTI,
1598 HR_LOCATIONS_ALL HL, --BUG 5219141 Replaced HR_LOCATIONS
1599 MTL_PARAMETERS MTL
1600 WHERE RTI.HEADER_INTERFACE_ID = X_HEADER_INTERFACE_ID
1601 AND (RTI.SHIP_TO_LOCATION_CODE = HL.LOCATION_CODE
1602 OR RTI.SHIP_TO_LOCATION_ID = HL.LOCATION_ID)
1603 AND HL.INVENTORY_ORGANIZATION_ID = MTL.ORGANIZATION_ID;
1604 END IF;
1605
1606 IF ( p_header_record.header_record.ship_to_organization_code IS NULL
1607 AND p_header_record.header_record.ship_to_organization_id IS NULL) THEN
1608 IF (x_to_organization_code IS NOT NULL) THEN
1609 IF (g_asn_debug = 'Y') THEN
1610 asn_debug.put_line('A ship to location relating to an org was found');
1611 END IF;
1612
1613 p_header_record.header_record.ship_to_organization_code := x_to_organization_code;
1614 ELSE
1615 IF (g_asn_debug = 'Y') THEN
1616 asn_debug.put_line('A ship to location relating to an org was NOT found');
1617 asn_debug.put_line('This will cause an ERROR later');
1618 END IF;
1619 END IF;
1620 END IF;
1621 EXCEPTION
1622 WHEN OTHERS THEN
1623 rcv_error_pkg.set_sql_error_message('insert_shipment_header', '000');
1624 p_header_record.error_record.error_status := rcv_error_pkg.g_ret_sts_unexp_error;
1625 p_header_record.error_record.error_message := rcv_error_pkg.get_last_message;
1626 END derive_ship_to_org_from_rti;
1627
1628 /* ksareddy - 2506961 - need to support automatic receipt in parallel processing
1629 lock and release the rcv_parameters table only to get the receipt number
1630 */
1631 PROCEDURE genreceiptnum(
1632 p_header_record IN OUT NOCOPY rcv_shipment_header_sv.headerrectype
1633 ) IS
1634 l_count NUMBER;
1635 PRAGMA AUTONOMOUS_TRANSACTION;
1636 BEGIN
1637 BEGIN
1638 SELECT (next_receipt_num + 1)
1639 INTO p_header_record.header_record.receipt_num
1640 FROM rcv_parameters
1641 WHERE organization_id = p_header_record.header_record.ship_to_organization_id
1642 FOR UPDATE OF next_receipt_num;
1643
1644 LOOP
1645 SELECT COUNT(*)
1646 INTO l_count
1647 FROM rcv_shipment_headers
1648 WHERE receipt_num = p_header_record.header_record.receipt_num
1649 AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id;
1650
1651 IF l_count = 0 THEN
1652 UPDATE rcv_parameters
1653 SET next_receipt_num = p_header_record.header_record.receipt_num
1654 WHERE organization_id = p_header_record.header_record.ship_to_organization_id;
1655
1656 EXIT;
1657 ELSE
1658 p_header_record.header_record.receipt_num := TO_CHAR(TO_NUMBER(p_header_record.header_record.receipt_num) + 1);
1659 END IF;
1660 END LOOP;
1661
1662 COMMIT;
1663 EXCEPTION
1664 WHEN OTHERS THEN
1665 ROLLBACK;
1666 END;
1667 END genreceiptnum;
1668 END rcv_headers_interface_sv;
[Home] [Help]