1 PACKAGE BODY rcv_roi_header
2 /* $Header: RCVPREHB.pls 120.5.12010000.2 2008/10/09 19:36:32 vthevark ship $*/
3 AS
4 g_asn_debug VARCHAR2(1) := NVL(fnd_profile.VALUE('RCV_DEBUG_MODE'), 'N');
5 x_interface_type VARCHAR2(25) := 'RCV-856';
6 x_sysdate DATE := SYSDATE;
7 x_count NUMBER := 0;
8 x_in_this_op_unit NUMBER := 0; -- Bug 3359613
9
10 PROCEDURE process_header(
11 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
12 ) IS
13 BEGIN
14 NULL;
15 END process_header;
16
17 PROCEDURE process_cancellation(
18 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
19 ) IS
20 BEGIN
21 asn_debug.put_line('new_roi: in process_cancellation');
22 derive_vendor_header(p_header_record);
23
24 IF p_header_record.error_record.error_status IN('S', 'W') THEN
25 default_vendor_header(p_header_record);
26 END IF;
27
28 IF p_header_record.error_record.error_status IN('S', 'W') THEN
29 validate_vendor_header(p_header_record);
30 END IF;
31
32 IF p_header_record.error_record.error_status IN('S', 'W') THEN
33 IF NVL(p_header_record.header_record.test_flag, 'N') <> 'Y' THEN
34 insert_cancelled_asn_lines(p_header_record);
35 END IF;
36 END IF;
37 EXCEPTION
38 WHEN OTHERS THEN
39 IF (g_asn_debug = 'Y') THEN
40 asn_debug.put_line('Exception in process_cancellation ');
41 END IF;
42
43 p_header_record.error_record.error_status := 'U';
44 p_header_record.error_record.error_message := SQLERRM;
45
46 IF (g_asn_debug = 'Y') THEN
47 asn_debug.put_line('Fatal Error');
48 END IF;
49 END process_cancellation;
50
51 PROCEDURE process_vendor_header(
52 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
53 ) IS
54 l_shipment_header_id NUMBER;
55 l_receive_against_asn VARCHAR2(1) := 'N';
56 BEGIN
57 IF (g_asn_debug = 'Y') THEN
58 asn_debug.put_line('new_roi: in process_vendor_header');
59 END IF;
60
61 /* check whether there is already a row in rsh for the given
62 * shipment_num . It will be there if this is a
63 * Receive against an ASN.
64 */
65 IF (g_asn_debug = 'Y') THEN
66 asn_debug.put_line('ASN_TYPE ' || NVL(p_header_record.header_record.asn_type, 'STD'));
67 END IF;
68
69 /* We need to derive vendor header for ASN receive since we can have
70 * same shipment_num for different vendor/vendor_site combinations.
71 */
72 derive_vendor_header(p_header_record);
73
74 IF p_header_record.error_record.error_status IN('S', 'W') THEN
75 default_vendor_header(p_header_record);
76 END IF;
77
78 IF p_header_record.error_record.error_status IN('S', 'W') THEN
79 validate_vendor_header(p_header_record);
80 END IF;
81
82 /* Bug#4523892 - START */
83 IF (g_asn_debug = 'Y') THEN
84 asn_debug.put_line('Transaction against ASN? ' || g_txn_against_asn);
85 END IF;
86 /* Bug#4523892 - END */
87
88 IF p_header_record.error_record.error_status IN('S', 'W') THEN --{
89 IF ( NVL(p_header_record.header_record.test_flag, 'N') <> 'Y'
90 AND g_txn_against_asn <> 'Y') THEN --{
91 IF (g_asn_debug = 'Y') THEN
92 asn_debug.put_line('Call insert_shipment_header');
93 END IF;
94
95 insert_shipment_header(p_header_record);
96
97 IF (g_asn_debug = 'Y') THEN
98 asn_debug.put_line('After insert_shipment_header');
99 END IF;
100 ELSIF(g_txn_against_asn = 'Y') THEN
101 /* Some fields can be changed at the time of
102 * receiving an ASN. We need to update these
103 * in rsh.
104 */
105 IF (g_asn_debug = 'Y') THEN
106 asn_debug.put_line('Before update_shipment_header');
107 END IF;
108
109 update_shipment_header(p_header_record);
110
111 IF (g_asn_debug = 'Y') THEN
112 asn_debug.put_line('After update_shipment_header');
113 END IF;
114 END IF; --}
115 END IF; --}
116 EXCEPTION
117 WHEN OTHERS THEN
118 IF (g_asn_debug = 'Y') THEN
119 asn_debug.put_line('Exception in process_vendor_header ');
120 END IF;
121
122 p_header_record.error_record.error_status := 'U';
123 p_header_record.error_record.error_message := SQLERRM;
124
125 IF (g_asn_debug = 'Y') THEN
126 asn_debug.put_line('Fatal Error');
127 END IF;
128 END process_vendor_header;
129
130 PROCEDURE process_customer_header(
131 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
132 ) IS
133 BEGIN
134 asn_debug.put_line('new_roi: in process_customer_header');
135 rcv_rma_headers.derive_rma_header(p_header_record);
136
137 IF p_header_record.error_record.error_status IN('S', 'W') THEN
138 rcv_rma_headers.default_rma_header(p_header_record);
139 END IF;
140
141 IF p_header_record.error_record.error_status IN('S', 'W') THEN
142 rcv_rma_headers.validate_rma_header(p_header_record);
143 END IF;
144
145 IF p_header_record.error_record.error_status IN('S', 'W') THEN
146 IF NVL(p_header_record.header_record.test_flag, 'N') <> 'Y' THEN
147 rcv_rma_headers.insert_rma_header(p_header_record);
148 END IF;
149 END IF;
150 EXCEPTION
151 WHEN OTHERS THEN
152 IF (g_asn_debug = 'Y') THEN
153 asn_debug.put_line('Exception in process_customer_header ');
154 END IF;
155
156 p_header_record.error_record.error_status := 'U';
157 p_header_record.error_record.error_message := SQLERRM;
158
159 IF (g_asn_debug = 'Y') THEN
160 asn_debug.put_line('Fatal Error');
161 END IF;
162 END process_customer_header;
163
164 PROCEDURE process_internal_header(
165 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
166 ) IS
167 BEGIN
168 asn_debug.put_line('new_roi: in process_internal_header');
169 END process_internal_header;
170
171 PROCEDURE process_internal_order_header(
172 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
173 ) IS
174 BEGIN
175 asn_debug.put_line('new_roi: in process_internal_order_header');
176 derive_internal_order_header(p_header_record);
177
178 IF p_header_record.error_record.error_status IN('S', 'W') THEN
179 default_internal_order_header(p_header_record);
180 END IF;
181
182 IF p_header_record.error_record.error_status IN('S', 'W') THEN
183 validate_internal_order_header(p_header_record);
184 END IF;
185
186 IF p_header_record.error_record.error_status IN('S', 'W') THEN
187 IF NVL(p_header_record.header_record.test_flag, 'N') <> 'Y' THEN
188 rcv_int_order_pp_pvt.update_header(p_header_record);
189 END IF;
190 END IF;
191 EXCEPTION
192 WHEN OTHERS THEN
193 IF (g_asn_debug = 'Y') THEN
194 asn_debug.put_line('Exception in process_internal_order_header ');
195 END IF;
196
197 p_header_record.error_record.error_status := 'U';
198 p_header_record.error_record.error_message := SQLERRM;
199
200 IF (g_asn_debug = 'Y') THEN
201 asn_debug.put_line('Fatal Error');
202 END IF;
203 END process_internal_order_header;
204
205 PROCEDURE derive_vendor_header(
206 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
207 ) IS
208 BEGIN
209 -- Note.: the derive receipt source code was not done. DO we need it ??
210 --??? (RCVHISVB : lines 593 - 600)
211
212 derive_vendor_info(p_header_record);
213 rcv_roi_header_common.derive_ship_to_org_info(p_header_record);
214 rcv_roi_header_common.derive_from_org_info(p_header_record);
215 derive_vendor_site_info(p_header_record);
216 rcv_roi_header_common.derive_location_info(p_header_record);
217 derive_payment_terms_info(p_header_record);
218 rcv_roi_header_common.derive_receiver_info(p_header_record);
219 derive_shipment_header_id(p_header_record);
220 END derive_vendor_header;
221
222 PROCEDURE default_vendor_header(
223 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
224 ) IS
225 BEGIN
226 rcv_roi_header_common.default_last_update_info(p_header_record);
227 rcv_roi_header_common.default_creation_info(p_header_record);
228 rcv_roi_header_common.default_asn_type(p_header_record);
229 rcv_roi_header_common.default_ship_from_loc_info(p_header_record);
230 default_shipment_num(p_header_record);
231
232 IF (g_asn_debug = 'Y') THEN
233 asn_debug.put_line('asn_tyoe ' || NVL(p_header_record.header_record.asn_type, 'STD'));
234 asn_debug.put_line('shipment_num ' || NVL(p_header_record.header_record.shipment_num, -999));
235 asn_debug.put_line('receipt_header_id ' || NVL(p_header_record.header_record.receipt_header_id, -999));
236 END IF;
237
238 g_txn_against_asn := 'Y'; /* Bug#4523892 */
239 IF ( NVL(p_header_record.header_record.asn_type, 'STD') = 'STD'
240 AND ( p_header_record.header_record.shipment_num IS NOT NULL
241 AND p_header_record.header_record.receipt_header_id IS NOT NULL)) THEN
242 IF (g_asn_debug = 'Y') THEN
243 asn_debug.put_line('Into default shipment info for an ASN receive');
244 END IF;
245
246 default_shipment_info(p_header_record);
247 rcv_roi_header_common.default_receipt_info(p_header_record);
248 ELSE /* For all other txns except asn receive */
249 IF (g_asn_debug = 'Y') THEN
250 asn_debug.put_line('Into default shipment info for non ASN receive');
251 END IF;
252
253 default_vendor_site_id(p_header_record);
254 rcv_roi_header_common.default_shipment_header_id(p_header_record);
255 rcv_roi_header_common.default_receipt_info(p_header_record);
256 rcv_roi_header_common.default_ship_to_location_info(p_header_record);
257 END IF;
258
259 -- added for support of cancel
260 -- default any shipment info
261 IF (p_header_record.header_record.transaction_type = 'CANCEL')
262 AND ( p_header_record.header_record.receipt_header_id IS NULL
263 OR p_header_record.header_record.shipment_num IS NULL) THEN
264 IF (g_asn_debug = 'Y') THEN
265 asn_debug.put_line('Into default shipment info');
266 END IF;
267
268 --rcv_core_s.default_shipment_info (p_header_record);
269 default_shipment_info(p_header_record);
270 END IF;
271
272 IF (g_asn_debug = 'Y') THEN
273 asn_debug.put_line('g_txn_against_asn in default_vendor_header:' || g_txn_against_asn);
274 asn_debug.put_line('Out of default');
275 END IF;
276 END default_vendor_header;
277
278 PROCEDURE validate_vendor_header(
279 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
280 ) IS
281 BEGIN
282 rcv_roi_header_common.validate_trx_type(p_header_record);
283 validate_document_type(p_header_record);
284 validate_currency_code(p_header_record);
285 rcv_roi_header_common.validate_shipment_date(p_header_record);
286 validate_receipt_date(p_header_record);
287 rcv_roi_header_common.validate_expected_receipt_date(p_header_record);
288 rcv_roi_header_common.validate_receipt_num(p_header_record);
289 rcv_roi_header_common.validate_ship_from_loc_info(p_header_record);
290
291 IF (p_header_record.header_record.receipt_source_code = 'VENDOR') THEN
292 validate_vendor_info(p_header_record);
293 validate_vendor_site_info(p_header_record);
294 END IF;
295
296 rcv_roi_header_common.validate_ship_to_org_info(p_header_record);
297 rcv_roi_header_common.validate_from_org_info(p_header_record);
298 rcv_roi_header_common.validate_location_info(p_header_record);
299 rcv_roi_header_common.validate_payment_terms_info(p_header_record);
300 rcv_roi_header_common.validate_receiver_info(p_header_record);
301 rcv_roi_header_common.validate_freight_carrier_info(p_header_record);
302
303 /* Bug#4523892 */
304 IF (NVL(p_header_record.header_record.asn_type, 'STD') = 'STD'
305 AND g_txn_against_asn = 'Y') THEN
306 IF (g_asn_debug = 'Y') THEN
307 asn_debug.put_line('End of validations if this is an ASN receive');
308 END IF;
309
310 RETURN;
311 END IF;
312
313 IF (p_header_record.header_record.receipt_source_code = 'VENDOR') THEN
314 validate_asbn_specific_info(p_header_record);
315 rcv_roi_header_common.validate_lcm_info(p_header_record); -- lcm changes
316 END IF;
317
318 /* Validate gross_weight_uom_code */
319 /* Validate net_weight_uom_code */
320 /* Validate tare_weight_uom_code */
321 /* Validate Carrier_method */
322 /* Validate Special handling code */
323 /* Validate Hazard Code */
324 /* Validate Hazard Class */
325 /* Validate Freight Terms */
326 /* Validate Excess Transportation Reason */
327 /* Validate Excess Transportation Responsible */
328 /* Validate Invoice Status Code */
329 IF p_header_record.error_record.error_status IN('S', 'W') THEN
330 IF (g_asn_debug = 'Y') THEN
331 asn_debug.put_line('Other Validations');
332 END IF;
333 END IF;
334 END validate_vendor_header;
335
336 PROCEDURE derive_customer_header(
337 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
338 ) IS
339 BEGIN
340 NULL;
341 END derive_customer_header;
342
343 PROCEDURE default_customer_header(
344 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
345 ) IS
346 BEGIN
347 NULL;
348 END default_customer_header;
349
350 PROCEDURE validate_customer_header(
351 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
352 ) IS
353 BEGIN
354 NULL;
355 END validate_customer_header;
356
357 PROCEDURE derive_internal_header(
358 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
359 ) IS
360 BEGIN
361 NULL;
362 END derive_internal_header;
363
364 PROCEDURE default_internal_header(
365 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
366 ) IS
367 BEGIN
368 NULL;
369 END default_internal_header;
370
371 PROCEDURE validate_internal_header(
372 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
373 ) IS
374 BEGIN
375 NULL;
376 END validate_internal_header;
377
378 -- Wrapper to RCV_INT_ORDER_PP_PVT version, for consistency
379 PROCEDURE derive_internal_order_header(
380 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
381 ) IS
382 BEGIN
383 rcv_int_order_pp_pvt.derive_internal_order_header(p_header_record);
384 END derive_internal_order_header;
385
386 -- WRAPPER to RCV_INT_ORDER_PP_PVT version, for consistency
387 PROCEDURE default_internal_order_header(
388 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
389 ) IS
390 BEGIN
391 rcv_int_order_pp_pvt.default_internal_order_header(p_header_record);
392 END default_internal_order_header;
393
394 -- WRAPPER to RCV_INT_ORDER_PP_PVT version, for consistency
395 PROCEDURE validate_internal_order_header(
396 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
397 ) IS
398 BEGIN
399 rcv_int_order_pp_pvt.validate_internal_order_header(p_header_record);
400 END validate_internal_order_header;
401
402 PROCEDURE derive_vendor_info(
403 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
404 ) IS
405 vendor_record rcv_shipment_header_sv.vendorrectype;
406 BEGIN
407 /* Derive Vendor Information */
408 IF p_header_record.error_record.error_status IN('S', 'W') THEN
409 vendor_record.vendor_name := p_header_record.header_record.vendor_name;
410 vendor_record.vendor_num := p_header_record.header_record.vendor_num;
411 vendor_record.vendor_id := p_header_record.header_record.vendor_id;
412 vendor_record.error_record.error_status := p_header_record.error_record.error_status;
413 vendor_record.error_record.error_message := p_header_record.error_record.error_message;
414
415 IF (g_asn_debug = 'Y') THEN
416 asn_debug.put_line('In Vendor Procedure');
417 END IF;
418
419 po_vendors_sv.derive_vendor_info(vendor_record);
420
421 IF (g_asn_debug = 'Y') THEN
422 asn_debug.put_line(TO_CHAR(vendor_record.vendor_id));
423 asn_debug.put_line(vendor_record.vendor_name);
424 asn_debug.put_line(vendor_record.vendor_num);
425 asn_debug.put_line(vendor_record.error_record.error_status);
426 asn_debug.put_line(vendor_record.error_record.error_message);
427 END IF;
428
429 p_header_record.header_record.vendor_name := vendor_record.vendor_name;
430 p_header_record.header_record.vendor_num := vendor_record.vendor_num;
431 p_header_record.header_record.vendor_id := vendor_record.vendor_id;
432 p_header_record.error_record.error_status := vendor_record.error_record.error_status;
433 p_header_record.error_record.error_message := vendor_record.error_record.error_message;
434 END IF;
435 END derive_vendor_info;
436
437 PROCEDURE derive_vendor_site_info(
438 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
439 ) IS
440 vendor_site_record rcv_shipment_header_sv.vendorsiterectype;
441 BEGIN
442 /* derive vendor site information */
443 /* Call derive vendor_site_procedure here */
444 /* UK1 -> vendor_site_id
445 UK2 -> vendor_site_code + vendor_id + org_id */
446 IF p_header_record.error_record.error_status IN('S', 'W')
447 AND ( p_header_record.header_record.vendor_site_code IS NOT NULL
448 OR p_header_record.header_record.vendor_site_id IS NOT NULL) THEN
449 vendor_site_record.vendor_site_code := p_header_record.header_record.vendor_site_code;
450 vendor_site_record.vendor_id := p_header_record.header_record.vendor_id;
451 vendor_site_record.vendor_site_id := p_header_record.header_record.vendor_site_id;
452 vendor_site_record.organization_id := p_header_record.header_record.ship_to_organization_id;
453 vendor_site_record.error_record.error_status := p_header_record.error_record.error_status;
454 vendor_site_record.error_record.error_message := p_header_record.error_record.error_message;
455
456 IF (g_asn_debug = 'Y') THEN
457 asn_debug.put_line('In Vendor Site Procedure');
458 END IF;
459
460 po_vendor_sites_sv.derive_vendor_site_info(vendor_site_record);
461
462 IF (g_asn_debug = 'Y') THEN
463 asn_debug.put_line(vendor_site_record.vendor_site_code);
464 asn_debug.put_line(vendor_site_record.vendor_site_id);
465 END IF;
466
467 p_header_record.header_record.vendor_site_code := vendor_site_record.vendor_site_code;
468 p_header_record.header_record.vendor_id := vendor_site_record.vendor_id;
469 p_header_record.header_record.vendor_site_id := vendor_site_record.vendor_site_id;
470 p_header_record.header_record.ship_to_organization_id := vendor_site_record.organization_id;
471 p_header_record.error_record.error_status := vendor_site_record.error_record.error_status;
472 p_header_record.error_record.error_message := vendor_site_record.error_record.error_message;
473 END IF;
474 END derive_vendor_site_info;
475
476 PROCEDURE derive_payment_terms_info(
477 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
478 ) IS
479 pay_record rcv_shipment_header_sv.payrectype;
480 BEGIN
481 /* Derive Payment Terms Information */
482 IF p_header_record.error_record.error_status IN('S', 'W')
483 AND p_header_record.header_record.transaction_type <> 'CANCEL'
484 AND -- added for support of cancel
485 ( p_header_record.header_record.payment_terms_id IS NOT NULL
486 OR p_header_record.header_record.payment_terms_name IS NOT NULL) THEN
487 pay_record.payment_term_id := p_header_record.header_record.payment_terms_id;
488 pay_record.payment_term_name := p_header_record.header_record.payment_terms_name;
489 pay_record.error_record.error_status := p_header_record.error_record.error_status;
490 pay_record.error_record.error_message := p_header_record.error_record.error_message;
491
492 IF (g_asn_debug = 'Y') THEN
493 asn_debug.put_line('In Derive Payment Terms ');
494 END IF;
495
496 po_terms_sv.derive_payment_terms_info(pay_record);
497
498 IF (g_asn_debug = 'Y') THEN
499 asn_debug.put_line(pay_record.payment_term_name);
500 asn_debug.put_line(TO_CHAR(pay_record.payment_term_id));
501 asn_debug.put_line(pay_record.error_record.error_status);
502 END IF;
503
504 p_header_record.header_record.payment_terms_id := pay_record.payment_term_id;
505 p_header_record.header_record.payment_terms_name := pay_record.payment_term_name;
506 p_header_record.error_record.error_status := pay_record.error_record.error_status;
507 p_header_record.error_record.error_message := pay_record.error_record.error_message;
508 END IF;
509 END derive_payment_terms_info;
510
511 PROCEDURE derive_shipment_header_id(
512 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
513 ) IS
514 BEGIN
515 /* Derive shipment_header_id if transaction type = CANCEL */
516 -- added for support of cancel
517 IF p_header_record.error_record.error_status IN('S', 'W')
518 AND ( (p_header_record.header_record.transaction_type = 'CANCEL')
519 OR NVL(p_header_record.header_record.asn_type, 'STD') = 'STD')
520 AND p_header_record.header_record.shipment_num IS NOT NULL THEN
521 IF (g_asn_debug = 'Y') THEN
522 asn_debug.put_line('Derive shipment info for CANCEL or Receive against an ASN');
523 END IF;
524
525 --rcv_core_s.derive_shipment_info(p_header_record);
526 derive_shipment_info(p_header_record);
527 END IF;
528 END derive_shipment_header_id;
529
530 PROCEDURE derive_shipment_info(
531 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
532 ) IS
533 BEGIN
534 IF p_header_record.header_record.receipt_header_id IS NOT NULL THEN
535 IF (g_asn_debug = 'Y') THEN
536 asn_debug.put_line('Need to put a cursor to retrieve other values');
537 asn_debug.put_line('Shipment header Id has been provided');
538 END IF;
539
540 RETURN;
541 END IF;
542
543 -- Check that the shipment_num is not null
544 IF ( p_header_record.header_record.shipment_num IS NULL
545 OR p_header_record.header_record.shipment_num = '0'
546 OR REPLACE(p_header_record.header_record.shipment_num,
547 ' ',
548 ''
549 ) IS NULL) THEN
550 IF (g_asn_debug = 'Y') THEN
551 asn_debug.put_line('Cannot derive the shipment_header_id at this point');
552 END IF;
553
554 RETURN;
555 END IF;
556
557 -- Derive the shipment_header_id only for transaction_type = CANCEL
558 IF (g_asn_debug = 'Y') THEN
559 asn_debug.put_line('Will derive shipment_header_id if shipment_num is given');
560 END IF;
561
562 /*
563 * BUGNO: 1708017
564 * The where clause used to have organization_id =
565 * p_header_record.header_record.ship_to_organization_id
566 * This used to be populated with ship_to_organization_id.
567 * Now this is populated as null since it is supposed to
568 * be from organization_id. So changed it to ship_to_org_id.
569 */
570 IF ( (p_header_record.header_record.transaction_type = 'CANCEL')
571 OR NVL(p_header_record.header_record.asn_type, 'STD') = 'STD')
572 AND p_header_record.header_record.receipt_header_id IS NULL THEN
573 BEGIN
574 IF (g_asn_debug = 'Y') THEN
575 asn_debug.put_line('vendor_site_id ' || p_header_record.header_record.vendor_site_id);
576 asn_debug.put_line('vendor_id ' || p_header_record.header_record.vendor_id);
577 asn_debug.put_line('ship_to_organization_id ' || p_header_record.header_record.ship_to_organization_id);
578 asn_debug.put_line('shipment_num ' || p_header_record.header_record.shipment_num);
579 asn_debug.put_line('shipped_date ' || p_header_record.header_record.shipped_date);
580 END IF;
581
582 SELECT MAX(shipment_header_id) -- if we ever have 2 shipments with the same combo
583 INTO p_header_record.header_record.receipt_header_id
584 FROM rcv_shipment_headers
585 WHERE NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, NVL(vendor_site_id, -9999))
586 AND vendor_id = p_header_record.header_record.vendor_id
587 AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
588 AND shipment_num = p_header_record.header_record.shipment_num
589 AND ( ( p_header_record.header_record.transaction_type = 'CANCEL'
590 AND shipped_date >= ADD_MONTHS(p_header_record.header_record.shipped_date, -12))
591 OR ( p_header_record.header_record.transaction_type <> 'CANCEL'
592 AND shipped_date >= NVL(ADD_MONTHS(p_header_record.header_record.shipped_date, -12), shipped_date))
593 );
594
595 IF (g_asn_debug = 'Y') THEN
596 asn_debug.put_line('receipt_header_id ' || p_header_record.header_record.receipt_header_id);
597 END IF;
598 EXCEPTION
599 WHEN OTHERS THEN
600 IF (g_asn_debug = 'Y') THEN
601 asn_debug.put_line('Select stmt failed to get ship_header_id');
602 asn_debug.put_line(SQLERRM);
603 END IF;
604 END;
605
606 RETURN;
607 END IF;
608 EXCEPTION
609 WHEN OTHERS THEN
610 IF (g_asn_debug = 'Y') THEN
611 asn_debug.put_line('Exception in when others in derive_shipment_info ');
612 END IF;
613
614 p_header_record.error_record.error_status := 'U';
615 p_header_record.error_record.error_message := SQLERRM;
616 END derive_shipment_info;
617
618 PROCEDURE default_shipment_num(
619 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
620 ) IS
621 BEGIN
622 /* SHIPMENT NUMBER FOR ASBN if shipment_num IS NULL */
623 /* First choice for ASBN */
624 IF p_header_record.header_record.asn_type = 'ASBN'
625 AND p_header_record.header_record.shipment_num IS NULL THEN
626 p_header_record.header_record.shipment_num := p_header_record.header_record.invoice_num;
627
628 IF (g_asn_debug = 'Y') THEN
629 asn_debug.put_line('defaulted shipment number');
630 END IF;
631 END IF;
632
633 /* SHIPMENT NUMBER FOR ASBN/ASN if shipment_num IS NULL */
634 /* First choice for ASN/ Second Choice for ASN */
635
636 /* Bug3462816 Packing slip should not defaulted for normal Receipts */
637 IF NVL(p_header_record.header_record.asn_type, 'ASN') <> 'STD'
638 AND p_header_record.header_record.shipment_num IS NULL THEN
639 p_header_record.header_record.shipment_num := p_header_record.header_record.packing_slip;
640
641 IF (g_asn_debug = 'Y') THEN
642 asn_debug.put_line('defaulted shipment number');
643 END IF;
644 END IF;
645 END default_shipment_num;
646
647 PROCEDURE default_vendor_site_id(
648 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
649 ) IS
650
651 /* 5953480 - declared below two variables, to get proper site value. */
652 count1 number :=0;
653 x_ven_site_id number;
654
655 BEGIN
656 /* vendor_site_id po_vendor_sites_sv.default_purchasing_site */
657 /* Check for whether we need more conditions in the where clause of the
658 procedure like pay_site_flag etc */
659 /* For transaction_type = CANCEL we should have picked up the vendor_site_id in
660 the derive_shipment_info stage */
661
662 /* Bug 5953480 fixed. we would not default vendor_site here as it is
663 taken care while processing RTI records. Hence commenting the following piece
664 of code which defaults the vendor_site info if only one vendor_site exists
665 for a vendor which could potentially default wrong vendor_site as in the
666 bug 5953480.
667 Added code to default the vendor_site info only if the corresponding RTIs
668 having POs with same vendor_site info.
669 */
670
671 IF p_header_record.header_record.vendor_site_id IS NULL
672 AND p_header_record.header_record.vendor_site_code IS NULL
673 AND p_header_record.header_record.vendor_id IS NOT NULL THEN -- added for support of cancel
674 IF (g_asn_debug = 'Y') THEN
675 asn_debug.put_line('Need to get default vendor site id');
676 END IF;
677
678
679 /*
680 po_vendor_sites_sv.get_def_vendor_site(p_header_record.header_record.vendor_id,
681 p_header_record.header_record.vendor_site_id,
682 p_header_record.header_record.vendor_site_code,
683 'RCV'
684 );
685 */
686
687 /*Commented above code line and added below code block for 5953480 */
688
689 BEGIN
690 SELECT Count(DISTINCT poh.vendor_site_id),poh.vendor_site_id
691 INTO count1,x_ven_site_id
692 FROM rcv_transactions_interface rti, po_headers poh
693 WHERE ((rti.document_num IS NOT NULL AND rti.document_num = poh.segment1) OR
694 (rti.po_header_id is not null AND rti.po_header_id = poh.po_header_id))
695 AND rti.header_interface_id = p_header_record.header_record.header_interface_id
696 GROUP BY poh.vendor_site_id;
697 EXCEPTION
698 WHEN TOO_MANY_ROWS THEN
699 count1 := 2;
700 WHEN NO_DATA_FOUND THEN
701 count1 :=0;
702 END;
703
704
705 IF (count1 = 1) and x_ven_site_id is not null THEN
706 p_header_record.header_record.vendor_site_id := x_ven_site_id;
707 po_vendor_sites_sv.get_vendor_site_name(x_ven_site_id,p_header_record.header_record.vendor_site_code);
708 END IF;
709
710 /*End of added code block for 5953480 */
711
712 IF (g_asn_debug = 'Y') THEN
713 asn_debug.put_line('Vendor Site Code is ='||p_header_record.header_record.vendor_site_code);
714 asn_debug.put_line('defaulted vendor_site info');
715 END IF;
716 END IF;
717 END default_vendor_site_id;
718
719 PROCEDURE default_shipment_info(
720 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
721 ) IS
722 x_count NUMBER;
723 BEGIN
724 -- no need to derive shipment_header_id if it is already provided
725 IF p_header_record.header_record.receipt_header_id IS NOT NULL THEN
726 IF (g_asn_debug = 'Y') THEN
727 asn_debug.put_line('Shipment header Id has been provided');
728 END IF;
729
730 RETURN;
731 END IF;
732
733 -- Check for shipment number which is null, blank , zero
734 IF p_header_record.header_record.asn_type IN('ASN', 'ASBN', 'LCM') /* lcm changes */
735 AND ( p_header_record.header_record.shipment_num IS NULL
736 OR p_header_record.header_record.shipment_num = '0'
737 OR REPLACE(p_header_record.header_record.shipment_num,
738 ' ',
739 ''
740 ) IS NULL) THEN
741 IF (g_asn_debug = 'Y') THEN
742 asn_debug.put_line('Shipment num is still null');
743 END IF;
744
745 RETURN;
746 END IF;
747
748 -- Derive the shipment_header_id based on the shipment_num for transaction_type = CANCEL
749 /*
750 * BUGNO: 1708017
751 * The where clause used to have organization_id =
752 * p_header_record.header_record.ship_to_organization_id
753 * This used to be populated with ship_to_organization_id.
754 * Now this is populated as null since it is supposed to
755 * be from organization_id. So changed it to ship_to_org_id.
756 */
757 IF ( (p_header_record.header_record.transaction_type = 'CANCEL')
758 OR NVL(p_header_record.header_record.asn_type, 'STD') = 'STD')
759 AND p_header_record.header_record.receipt_header_id IS NULL THEN
760 BEGIN
761 IF (g_asn_debug = 'Y') THEN
762 asn_debug.put_line('vendor_site_id ' || p_header_record.header_record.vendor_site_id);
763 asn_debug.put_line('vendor_id ' || p_header_record.header_record.vendor_id);
764 asn_debug.put_line('ship_to_organization_id ' || p_header_record.header_record.ship_to_organization_id);
765 asn_debug.put_line('shipment_num ' || p_header_record.header_record.shipment_num);
766 asn_debug.put_line('shipped_date ' || p_header_record.header_record.shipped_date);
767 END IF;
768
769 SELECT MAX(shipment_header_id) -- if we ever have 2 shipments with the same combo
770 INTO p_header_record.header_record.receipt_header_id
771 FROM rcv_shipment_headers
772 WHERE NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, -9999)
773 AND vendor_id = p_header_record.header_record.vendor_id
774 AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
775 AND shipment_num = p_header_record.header_record.shipment_num
776 AND ( ( p_header_record.header_record.transaction_type = 'CANCEL'
777 AND shipped_date >= ADD_MONTHS(p_header_record.header_record.shipped_date, -12))
778 OR ( p_header_record.header_record.transaction_type <> 'CANCEL'
779 AND shipped_date >= NVL(ADD_MONTHS(p_header_record.header_record.shipped_date, -12), shipped_date))
780 );
781 EXCEPTION
782 WHEN OTHERS THEN
783 IF (g_asn_debug = 'Y') THEN
784 asn_debug.put_line('Exception in derive ship_header in default shipment_info');
785 asn_debug.put_line(SQLERRM);
786 END IF;
787 END;
788
789 RETURN;
790 END IF;
791 EXCEPTION
792 WHEN OTHERS THEN
793 IF (g_asn_debug = 'Y') THEN
794 asn_debug.put_line('Exception in when others in default_shipment_info ');
795 END IF;
796
797 p_header_record.error_record.error_status := 'U';
798 p_header_record.error_record.error_message := SQLERRM;
799 END default_shipment_info;
800
801 PROCEDURE validate_document_type(
802 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
803 ) IS
804 lookup_record rcv_shipment_header_sv.lookuprectype;
805 BEGIN
806 /* Validate Document type */
807 IF p_header_record.error_record.error_status IN('S', 'W') THEN
808 IF p_header_record.header_record.asn_type IS NOT NULL
809 AND p_header_record.header_record.asn_type <> 'STD' THEN
810 lookup_record.lookup_code := p_header_record.header_record.asn_type;
811 lookup_record.lookup_type := 'ASN_TYPE';
812 lookup_record.error_record.error_status := p_header_record.error_record.error_status;
813 lookup_record.error_record.error_message := p_header_record.error_record.error_message;
814 po_core_s.validate_lookup_info(lookup_record);
815
816 IF lookup_record.error_record.error_status IN('E') THEN
817 lookup_record.error_record.error_message := 'PO_PDOI_INVALID_TYPE_LKUP_CD';
818 rcv_error_pkg.set_error_message(lookup_record.error_record.error_message);
819 rcv_error_pkg.set_token('VALUE', lookup_record.lookup_code);
820 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
821 'ASN_TYPE',
822 FALSE
823 );
824 END IF;
825
826 p_header_record.error_record.error_status := lookup_record.error_record.error_status;
827 p_header_record.error_record.error_message := lookup_record.error_record.error_message;
828
829 IF (g_asn_debug = 'Y') THEN
830 asn_debug.put_line('validated asn type');
831 END IF;
832 ELSE
833 p_header_record.header_record.asn_type := 'STD'; -- Not an ASN/ASBN
834 END IF;
835 END IF;
836 END validate_document_type;
837
838 PROCEDURE validate_currency_code(
839 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
840 ) IS
841 currency_record rcv_shipment_header_sv.currectype;
842 BEGIN
843 /* Validate Currency Code */
844 IF p_header_record.error_record.error_status IN('S', 'W')
845 AND p_header_record.header_record.transaction_type <> 'CANCEL'
846 AND p_header_record.header_record.asn_type = 'ASBN'
847 AND p_header_record.header_record.currency_code IS NOT NULL THEN
848 currency_record.currency_code := p_header_record.header_record.currency_code;
849 currency_record.error_record.error_status := p_header_record.error_record.error_status;
850 currency_record.error_record.error_message := p_header_record.error_record.error_message;
851 po_currency_sv.validate_currency_info(currency_record);
852
853 IF currency_record.error_record.error_status = 'E' THEN
854 IF currency_record.error_record.error_message IN('CURRENCY_DISABLED', 'CURRENCY_INVALID') THEN
855 currency_record.error_record.error_message := 'PO_PDOI_INVALID_CURRENCY';
856 rcv_error_pkg.set_error_message(currency_record.error_record.error_message);
857 rcv_error_pkg.set_token('VALUE', currency_record.currency_code);
858 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
859 'CURRECTYPE',
860 FALSE
861 );
862 END IF;
863 END IF;
864
865 p_header_record.error_record.error_status := currency_record.error_record.error_status;
866 p_header_record.error_record.error_message := currency_record.error_record.error_message;
867
868 IF (g_asn_debug = 'Y') THEN
869 asn_debug.put_line('validated currency info');
870 END IF;
871 END IF;
872 END validate_currency_code;
873
874 PROCEDURE validate_receipt_date(
875 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
876 ) IS
877 BEGIN
878 /* Validation for Receipt Date > Shipped Date if Receipt Date is specified */
879 IF p_header_record.error_record.error_status IN('S', 'W')
880 AND p_header_record.header_record.transaction_type <> 'CANCEL' THEN
881 IF p_header_record.header_record.expected_receipt_date IS NOT NULL THEN
882 IF p_header_record.header_record.expected_receipt_date < /* nwang: allow expected_receipt_date to be the same as shipped_date */
883 p_header_record.header_record.shipped_date THEN
884 p_header_record.error_record.error_status := 'E';
885 p_header_record.error_record.error_message := 'RCV_DELIV_DATE_INVALID';
886 rcv_error_pkg.set_error_message(p_header_record.error_record.error_message);
887 rcv_error_pkg.set_token('DELIVERY DATE', fnd_date.date_to_chardate(p_header_record.header_record.expected_receipt_date));
888 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
889 'SHIPPED_DATE',
890 FALSE
891 );
892 END IF;
893 END IF;
894
895 IF (g_asn_debug = 'Y') THEN
896 asn_debug.put_line('validated for Receipt Date > Shipped Date if Receipt Date is specified');
897 END IF;
898 END IF;
899 END validate_receipt_date;
900
901 PROCEDURE validate_vendor_info(
902 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
903 ) IS
904 vendor_record rcv_shipment_header_sv.vendorrectype;
905 BEGIN
906 /* Validate Vendor Information */
907 IF p_header_record.header_record.vendor_id IS NULL
908 AND p_header_record.header_record.vendor_name IS NULL
909 AND p_header_record.header_record.vendor_num IS NULL THEN
910 IF (g_asn_debug = 'Y') THEN
911 asn_debug.put_line('validated vendor info is all null');
912 END IF;
913
914 p_header_record.error_record.error_status := 'E';
915 p_header_record.error_record.error_message := 'TBD';
916 END IF;
917
918 IF p_header_record.error_record.error_status IN('S', 'W') THEN
919 vendor_record.vendor_name := p_header_record.header_record.vendor_name;
920 vendor_record.vendor_num := p_header_record.header_record.vendor_num;
921 vendor_record.vendor_id := p_header_record.header_record.vendor_id;
922 vendor_record.error_record.error_status := p_header_record.error_record.error_status;
923 vendor_record.error_record.error_message := p_header_record.error_record.error_message;
924
925 IF (g_asn_debug = 'Y') THEN
926 asn_debug.put_line('In Vendor Validation Procedure');
927 END IF;
928
929 po_vendors_sv.validate_vendor_info(vendor_record);
930
931 IF vendor_record.error_record.error_status = 'E' THEN
932 IF vendor_record.error_record.error_message = 'VEN_DISABLED' THEN
933 vendor_record.error_record.error_message := 'PO_PDOI_INVALID_VENDOR';
934 rcv_error_pkg.set_error_message(vendor_record.error_record.error_message);
935 rcv_error_pkg.set_token('VALUE', vendor_record.vendor_id);
936 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
937 'VENDOR_ID',
938 FALSE
939 );
940 ELSIF vendor_record.error_record.error_message = 'VEN_HOLD' THEN
941 IF p_header_record.header_record.transaction_type <> 'CANCEL' THEN
942 vendor_record.error_record.error_message := 'PO_PO_VENDOR_ON_HOLD';
943 rcv_error_pkg.set_error_message(vendor_record.error_record.error_message);
944 rcv_error_pkg.set_token('VALUE', vendor_record.vendor_id);
945 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
946 'VENDOR_ID',
947 FALSE
948 );
949 ELSE
950 vendor_record.error_record.error_message := NULL;
951 vendor_record.error_record.error_status := 'S';
952 END IF;
953 ELSIF vendor_record.error_record.error_message = 'VEN_ID' THEN
954 vendor_record.error_record.error_message := 'RCV_VEN_ID';
955 rcv_error_pkg.set_error_message(vendor_record.error_record.error_message);
956 rcv_error_pkg.set_token('SUPPLIER', vendor_record.vendor_id);
957 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
958 'VENDOR_ID',
959 FALSE
960 );
961 END IF;
962 END IF;
963
964 p_header_record.error_record.error_status := vendor_record.error_record.error_status;
965 p_header_record.error_record.error_message := vendor_record.error_record.error_message;
966
967 IF (g_asn_debug = 'Y') THEN
968 asn_debug.put_line(vendor_record.error_record.error_status);
969 asn_debug.put_line(vendor_record.error_record.error_message);
970 END IF;
971
972 IF (g_asn_debug = 'Y') THEN
973 asn_debug.put_line('Validated vendor info');
974 END IF;
975 END IF;
976 END validate_vendor_info;
977
978 PROCEDURE validate_vendor_site_info(
979 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
980 ) IS
981 vendor_site_record rcv_shipment_header_sv.vendorsiterectype;
982 l_proc VARCHAR2(100);
983 BEGIN
984 /* validate vendor site information */
985 IF p_header_record.error_record.error_status IN('S', 'W')
986 AND ( p_header_record.header_record.vendor_site_code IS NOT NULL
987 OR p_header_record.header_record.vendor_site_id IS NOT NULL) THEN
988 vendor_site_record.vendor_site_code := p_header_record.header_record.vendor_site_code;
989 vendor_site_record.vendor_id := p_header_record.header_record.vendor_id;
990 vendor_site_record.vendor_site_id := p_header_record.header_record.vendor_site_id;
991 vendor_site_record.organization_id := NULL;
992 vendor_site_record.error_record.error_status := p_header_record.error_record.error_status;
993 vendor_site_record.error_record.error_message := p_header_record.error_record.error_message;
994
995 IF (g_asn_debug = 'Y') THEN
996 asn_debug.put_line('In Validate Vendor Site Procedure');
997 asn_debug.put_line('Remit_to_site_id ' || NVL(p_header_record.header_record.remit_to_site_id, -999));
998 END IF;
999
1000 /* Bug 3590488.
1001 * We need to send remit_to_site_id since certain flags like
1002 * hold_all_payment_flag and pay_on_site_id should be validated
1003 * using remit_to_site_id instead of vendor_site_id for ASBNs.
1004 */
1005 po_vendor_sites_sv.validate_vendor_site_info(vendor_site_record, p_header_record.header_record.remit_to_site_id);
1006
1007 /* if supplier site is not defined as pay on receipt site then
1008 the validate_vendor_site proc returns error_message =
1009 'VEN_SITE_NOT_POR_SITE'. This error is applicable only for asn_type=ASBN.
1010 Also invoice_status_code needs to be set to a predefined value in case we hit this
1011 error as invoice cannot be auto created.
1012
1013 In case asn_type = ASN then we reset the error_status and message */
1014
1015 /*
1016 * Bug #933119
1017 * When the hold_all_payments flag is set for a vendor site,
1018 * the pre-processor used to error out which was incorrect. This error
1019 * is applicable only for asn_type=ASBN. In case asn_type=ASN then we
1020 * now we reset the error_status and message.
1021 */
1022 IF vendor_site_record.error_record.error_status = 'E'
1023 AND ( vendor_site_record.error_record.error_message = 'VEN_SITE_NOT_POR_SITE'
1024 OR vendor_site_record.error_record.error_message = 'VEN_SITE_HOLD_PMT') THEN
1025 IF p_header_record.header_record.asn_type = 'ASBN'
1026 AND p_header_record.header_record.transaction_type <> 'CANCEL' THEN
1027 vendor_site_record.error_record.error_message := 'PO_INV_CR_INVALID_PAY_SITE';
1028 vendor_site_record.error_record.error_status := 'W';
1029 rcv_error_pkg.set_error_message(vendor_site_record.error_record.error_message);
1030 rcv_error_pkg.set_token('VENDOR_SITE_ID', vendor_site_record.vendor_site_id);
1031 rcv_error_pkg.log_interface_warning('RCV_HEADERS_INTERFACE', 'VENDOR_SITE_ID');
1032 p_header_record.header_record.invoice_status_code := 'RCV_ASBN_NO_AUTO_INVOICE';
1033 ELSE
1034 vendor_site_record.error_record.error_status := 'S';
1035 vendor_site_record.error_record.error_message := NULL;
1036 END IF;
1037 END IF;
1038
1039 IF vendor_site_record.error_record.error_status = 'E' THEN
1040 IF vendor_site_record.error_record.error_message IN('VEN_SITE_DISABLED', 'VEN_SITE_NOT_PURCH') THEN
1041 /* Fix for bug 5953480, replicating and enhancing fix by
1042 2830103.Validation for inactive vendor site and
1043 vendor site not purchasable from anymore should happen
1044 only for ASNs and ASBNs. Hence adding the IF condition
1045 below so that no validation happens for STD receipts.
1046 And in ELSE bock added we make error status as success,
1047 so as to continue normally.
1048 */
1049 IF NVL(p_header_record.header_record.asn_type, 'STD') IN('ASN', 'ASBN', 'LCM') THEN /* lcm changes */
1050 vendor_site_record.error_record.error_message := 'PO_PDOI_INVALID_VENDOR_SITE';
1051 rcv_error_pkg.set_error_message(vendor_site_record.error_record.error_message);
1052 rcv_error_pkg.set_token('VALUE', vendor_site_record.vendor_site_id);
1053 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
1054 'VENDOR_SITE_ID',
1055 FALSE
1056 );
1057 ELSE
1058 vendor_site_record.error_record.error_status := rcv_error_pkg.g_ret_sts_success;
1059 vendor_site_record.error_record.error_message := NULL;
1060 END IF;
1061
1062 /*End of fix added for 5953480, while replicating fix by 2830103*/
1063
1064 ELSIF vendor_site_record.error_record.error_message = 'VEN_SITE_ID' THEN
1065 x_in_this_op_unit := 1;
1066
1067 /*Start Bug#3359613 */
1068 /* R12 Complex Work.
1069 * Added WC to the if stmt below.
1070 */
1071 IF NVL(p_header_record.header_record.asn_type, 'STD') IN('ASN', 'ASBN','WC', 'LCM') THEN /* lcm changes */ --{
1072 BEGIN
1073 SELECT COUNT(*)
1074 INTO x_in_this_op_unit
1075 FROM po_headers poh,
1076 rcv_transactions_interface rti
1077 WHERE poh.vendor_id = p_header_record.header_record.vendor_id
1078 AND poh.segment1 = rti.document_num
1079 AND rti.header_interface_id = p_header_record.header_record.header_interface_id
1080 AND NVL(rti.source_document_code, 'PO') = 'PO';
1081
1082 asn_debug.put_line('The chance of this PO belonging to this operating unit is =' || TO_CHAR(x_in_this_op_unit));
1083 asn_debug.put_line('Vendor Id is =' || TO_CHAR(p_header_record.header_record.vendor_id));
1084
1085 IF x_in_this_op_unit = 0 THEN --{
1086 asn_debug.put_line('Setting the RHI and RTI to Pending as this PO belongs to other operating unit ');
1087 asn_debug.put_line('Updating for Header Interface Id = ' || TO_CHAR(p_header_record.header_record.header_interface_id));
1088
1089 UPDATE rcv_headers_interface
1090 SET processing_status_code = 'PENDING'
1091 WHERE header_interface_id = p_header_record.header_record.header_interface_id;
1092
1093 UPDATE rcv_transactions_interface
1094 SET processing_status_code = 'PENDING'
1095 WHERE header_interface_id = p_header_record.header_record.header_interface_id
1096 AND processing_status_code = 'RUNNING'
1097 AND processing_mode_code = 'BATCH';
1098
1099 p_header_record.error_record.error_status := 'P';
1100 p_header_record.header_record.processing_status_code := 'PENDING';
1101 p_header_record.error_record.error_message := 'DIFFERENT_OU';
1102
1103 IF (g_asn_debug = 'Y') THEN
1104 asn_debug.put_line(vendor_site_record.error_record.error_status);
1105 asn_debug.put_line(vendor_site_record.error_record.error_message);
1106 END IF;
1107
1108 IF (g_asn_debug = 'Y') THEN
1109 asn_debug.put_line('Validated vendor site info');
1110 END IF;
1111
1112 RETURN;
1113 END IF; -- x_in_this_op_unit = 0 --}
1114 END;
1115 ELSE --}{
1116 /*End Bug#3359613 */
1117 vendor_site_record.error_record.error_message := 'PO_PDOI_INVALID_VENDOR_SITE';
1118 rcv_error_pkg.set_error_message(vendor_site_record.error_record.error_message);
1119 rcv_error_pkg.set_token('VALUE', vendor_site_record.vendor_site_id);
1120 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
1121 'VENDOR_SITE_ID',
1122 FALSE
1123 );
1124 END IF; -- Bug#3359613 NVL(p_header_record.header_record.asn_type,'STD') in ('ASN','ASBN')--}
1125 END IF;
1126 END IF;
1127
1128 p_header_record.error_record.error_status := vendor_site_record.error_record.error_status;
1129 p_header_record.error_record.error_message := vendor_site_record.error_record.error_message;
1130
1131 IF (g_asn_debug = 'Y') THEN
1132 asn_debug.put_line(vendor_site_record.error_record.error_status);
1133 asn_debug.put_line(vendor_site_record.error_record.error_message);
1134 END IF;
1135
1136 IF (g_asn_debug = 'Y') THEN
1137 asn_debug.put_line('Validated vendor site info');
1138 END IF;
1139 END IF;
1140 END validate_vendor_site_info;
1141
1142 PROCEDURE validate_asbn_specific_info(
1143 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
1144 ) IS
1145 invoice_record rcv_shipment_header_sv.invrectype;
1146 tax_record rcv_shipment_header_sv.taxrectype;
1147 BEGIN
1148 /* Validate Invoice Amount > 0 */
1149 /* Invoice amount Vs Supplier Site Limit */
1150 IF p_header_record.error_record.error_status IN('S', 'W')
1151 AND p_header_record.header_record.transaction_type <> 'CANCEL'
1152 AND p_header_record.header_record.asn_type = 'ASBN' THEN --{
1153 invoice_record.total_invoice_amount := p_header_record.header_record.total_invoice_amount;
1154 invoice_record.vendor_id := p_header_record.header_record.vendor_id;
1155 invoice_record.vendor_site_id := p_header_record.header_record.vendor_site_id;
1156 invoice_record.error_record.error_status := p_header_record.error_record.error_status;
1157 invoice_record.error_record.error_message := p_header_record.error_record.error_message;
1158 rcv_headers_interface_sv.validate_invoice_amount(invoice_record);
1159
1160 IF invoice_record.error_record.error_status = 'E' THEN --{
1161 IF invoice_record.error_record.error_message = 'RCV_ASBN_INVOICE_AMT' THEN --{
1162 rcv_error_pkg.set_error_message(invoice_record.error_record.error_message);
1163 rcv_error_pkg.set_token('AMOUNT', invoice_record.total_invoice_amount);
1164 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
1165 'TOTAL_INVOICE_AMOUNT',
1166 FALSE
1167 );
1168 ELSIF invoice_record.error_record.error_message = 'RCV_ASBN_INVOICE_AMT_LIMIT' THEN --} {
1169 rcv_error_pkg.set_error_message(invoice_record.error_record.error_message);
1170 rcv_error_pkg.set_token('AMOUNT', invoice_record.total_invoice_amount);
1171 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
1172 'TOTAL_INVOICE_AMOUNT',
1173 FALSE
1174 );
1175 END IF; --} matches if invoice record error status E
1176 END IF;
1177
1178 p_header_record.error_record.error_status := invoice_record.error_record.error_status;
1179 p_header_record.error_record.error_message := invoice_record.error_record.error_message;
1180
1181 IF (g_asn_debug = 'Y') THEN
1182 asn_debug.put_line('Validated invoice amount');
1183 END IF;
1184 END IF;
1185
1186 /* Validate that both Invoice number and shipment number are not missing */
1187 IF p_header_record.error_record.error_status IN('S', 'W')
1188 AND p_header_record.header_record.transaction_type <> 'CANCEL'
1189 AND p_header_record.header_record.asn_type = 'ASBN' THEN --{
1190 IF p_header_record.header_record.shipment_num IS NULL
1191 AND -- Should we assign shipment_num to null.invoice_num
1192 p_header_record.header_record.invoice_num IS NULL THEN --{
1193 p_header_record.error_record.error_status := 'E';
1194 p_header_record.error_record.error_message := 'RCV_ASBN_INVOICE_NUM';
1195 rcv_error_pkg.set_error_message(p_header_record.error_record.error_message);
1196 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
1197 'INVOICE_NUM',
1198 FALSE
1199 );
1200 END IF; --}
1201
1202 IF (g_asn_debug = 'Y') THEN
1203 asn_debug.put_line('Validated invoice number/shipment number are not missing');
1204 END IF;
1205 END IF; --}
1206
1207 /* Validate invoice_date is not missing */
1208 /* bug 628316 make sure invoice_date is not missing for ASBN */
1209 IF p_header_record.error_record.error_status IN('S', 'W')
1210 AND p_header_record.header_record.transaction_type <> 'CANCEL'
1211 AND p_header_record.header_record.asn_type = 'ASBN' THEN --{
1212 IF p_header_record.header_record.invoice_date IS NULL THEN --{
1213 p_header_record.error_record.error_status := 'E';
1214 p_header_record.error_record.error_message := 'RCV_ASBN_INVOICE_DATE';
1215 rcv_error_pkg.set_error_message(p_header_record.error_record.error_message);
1216 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
1217 'INVOICE_DATE',
1218 FALSE
1219 );
1220 END IF; --}
1221
1222 IF (g_asn_debug = 'Y') THEN
1223 asn_debug.put_line('Validated invoice date is not missing');
1224 END IF;
1225 END IF; --}
1226
1227 /* Validate Invoice Tax Code */
1228 IF p_header_record.error_record.error_status IN('S', 'W')
1229 AND 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.tax_name IS NOT NULL THEN --{
1232 tax_record.tax_name := p_header_record.header_record.tax_name;
1233 tax_record.tax_amount := p_header_record.header_record.tax_amount;
1234 tax_record.error_record.error_status := p_header_record.error_record.error_status;
1235 tax_record.error_record.error_message := p_header_record.error_record.error_message;
1236 po_locations_s.validate_tax_info(tax_record);
1237
1238 IF tax_record.error_record.error_status = 'E' THEN --{
1239 IF tax_record.error_record.error_message IN('TAX_CODE_INVALID', 'TAX_CODE_DISABLED') THEN --{
1240 tax_record.error_record.error_message := 'PO_PDOI_INVALID_TAX_NAME';
1241 rcv_error_pkg.set_error_message(tax_record.error_record.error_message);
1242 rcv_error_pkg.set_token('VALUE', tax_record.tax_name);
1243 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
1244 'TAX_NAME',
1245 FALSE
1246 );
1247 END IF; --}
1248 END IF; --} matches error status =E
1249
1250 p_header_record.error_record.error_status := tax_record.error_record.error_status;
1251 p_header_record.error_record.error_message := tax_record.error_record.error_message;
1252 END IF; --}
1253
1254 IF (g_asn_debug = 'Y') THEN
1255 asn_debug.put_line('Validated tax info');
1256 END IF;
1257 END IF; --}
1258
1259 /* Validations on shipment number */
1260 IF p_header_record.error_record.error_status IN('S', 'W') THEN
1261 -- rcv_core_s.validate_shipment_number(p_header_record);
1262 validate_shipment_number(p_header_record);
1263
1264 IF p_header_record.error_record.error_status = 'E' THEN --{
1265 IF p_header_record.error_record.error_message IN('RCV_NO_MATCHING_ASN', 'RCV_ASN_MISMATCH_SHIP_ID', 'RCV_ASN_QTY_RECEIVED') THEN --{
1266 rcv_error_pkg.set_error_message(p_header_record.error_record.error_message);
1267 rcv_error_pkg.set_token('SHIPMENT', p_header_record.header_record.shipment_num);
1268 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
1269 'SHIPMENT_NUM',
1270 FALSE
1271 );
1272 ELSIF p_header_record.error_record.error_message = 'RCV_NO_SHIPMENT_NUM' THEN --} {
1273 rcv_error_pkg.set_error_message(p_header_record.error_record.error_message);
1274 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
1275 'SHIPMENT_NUM',
1276 FALSE
1277 );
1278 ELSIF p_header_record.error_record.error_message = 'RCV_RCV_BEFORE_ASN' THEN --} {
1279 rcv_error_pkg.set_error_message(p_header_record.error_record.error_message);
1280 rcv_error_pkg.set_token('SHIPMENT', p_header_record.header_record.shipment_num);
1281 rcv_error_pkg.set_token('ITEM', '');
1282 rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE',
1283 'SHIPMENT_NUM',
1284 FALSE
1285 );
1286 /* Bug# 1413880
1287 As per the manual Shipment number should be unique for one year period for
1288 given supplier. Commenting out lines below */
1289
1290 -- END IF;
1291 -- ELSIF p_header_record.error_record.error_status = 'W' then
1292 ELSIF p_header_record.error_record.error_message = 'RCV_DUP_SHIPMENT_NUM' THEN --}{
1293 p_header_record.error_record.error_message := 'PO_PDOI_SHIPMENT_NUM_UNIQUE';
1294 rcv_error_pkg.set_error_message(p_header_record.error_record.error_message);
1295 rcv_error_pkg.set_token('VALUE', p_header_record.header_record.shipment_num);
1296 rcv_error_pkg.log_interface_warning('RCV_HEADERS_INTERFACE', 'SHIPMENT_NUM');
1297 END IF; --}
1298 END IF;
1299
1300 IF (g_asn_debug = 'Y') THEN
1301 asn_debug.put_line(p_header_record.header_record.shipment_num);
1302 asn_debug.put_line('Validations for shipment_number');
1303 END IF;
1304 END IF; --}
1305 END validate_asbn_specific_info;
1306
1307 PROCEDURE validate_shipment_number(
1308 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
1309 ) IS
1310 x_shipment_header_id NUMBER;
1311 BEGIN
1312 -- Check for shipment number which is null, blank , zero
1313 IF (g_asn_debug = 'Y') THEN
1314 asn_debug.put_line('Check for shipment number which is null, blank , zero ');
1315 END IF;
1316
1317 /*dbms_output.put_line(nvl(p_header_record.header_record.shipment_num,'@@@'));*/
1318 /* R12 Complex Work.
1319 * Add WC in addition to ASN and ASBN to validate unique
1320 * shipment numbers.
1321 */
1322 IF p_header_record.header_record.asn_type IN('ASN', 'ASBN','WC', 'LCM') /* lcm changes */
1323 AND ( p_header_record.header_record.shipment_num IS NULL
1324 OR p_header_record.header_record.shipment_num = '0'
1325 OR REPLACE(p_header_record.header_record.shipment_num,
1326 ' ',
1327 ''
1328 ) IS NULL) THEN
1329 p_header_record.error_record.error_status := 'E';
1330 p_header_record.error_record.error_message := 'RCV_NO_SHIPMENT_NUM';
1331 RETURN;
1332 END IF;
1333
1334 -- Check for Receipts before ASN
1335 IF (g_asn_debug = 'Y') THEN
1336 asn_debug.put_line('Check for Receipts before ASN ');
1337 END IF;
1338
1339 /*
1340 * BUGNO: 1708017
1341 * The where clause used to have organization_id =
1342 * p_header_record.header_record.ship_to_organization_id
1343 * This used to be populated with ship_to_organization_id.
1344 * Now this is populated as null since it is supposed to
1345 * be from organization_id. So changed it to ship_to_org_id.
1346 */
1347 /* Bug 2485699- commented the condn trunc(Shipped_date) = trunc(header.record.shipped_date).
1348 Added the shipped date is null since we are not populating the same in rcv_shipment_headers
1349 while receiving thru forms.*/
1350 /* R12 Complex Work.
1351 * Add WC in addition to ASN and ASBN to validate unique
1352 * shipment numbers.
1353 */
1354 IF p_header_record.header_record.asn_type IN('ASN', 'ASBN','WC', 'LCM') /* lcm changes */
1355 AND p_header_record.header_record.transaction_type <> 'CANCEL' THEN -- added this for CANCEL
1356 SELECT COUNT(*)
1357 INTO x_count
1358 FROM rcv_shipment_headers
1359 WHERE NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, -9999)
1360 AND vendor_id = p_header_record.header_record.vendor_id
1361 AND --trunc(shipped_date) = trunc(p_header_record.header_record.shipped_date) and
1362 ( shipped_date IS NULL
1363 OR shipped_date >= ADD_MONTHS(x_sysdate, -12))
1364 AND shipment_num = p_header_record.header_record.shipment_num
1365 AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
1366 AND receipt_num IS NOT NULL;
1367
1368 IF x_count > 0 THEN
1369 p_header_record.error_record.error_status := 'E';
1370 p_header_record.error_record.error_message := 'RCV_RCV_BEFORE_ASN';
1371 RETURN;
1372 END IF;
1373 END IF;
1374
1375 -- Change transaction_type to NEW if transaction_type is REPLACE and
1376 -- we cannot locate the shipment notice for the vendor site with the
1377 -- same shipped date
1378 /*
1379 * BUGNO: 1708017
1380 * The where clause used to have organization_id =
1381 * p_header_record.header_record.ship_to_organization_id
1382 * This used to be populated with ship_to_organization_id.
1383 * Now this is populated as null since it is supposed to
1384 * be from organization_id. So changed it to ship_to_org_id.
1385 */
1386 IF p_header_record.header_record.transaction_type = 'REPLACE' THEN
1387 SELECT COUNT(*)
1388 INTO x_count
1389 FROM rcv_shipment_headers
1390 WHERE NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, -9999)
1391 AND vendor_id = p_header_record.header_record.vendor_id
1392 AND TRUNC(shipped_date) = TRUNC(p_header_record.header_record.shipped_date)
1393 AND shipped_date >= ADD_MONTHS(x_sysdate, -12)
1394 AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
1395 AND shipment_num = p_header_record.header_record.shipment_num;
1396
1397 IF x_count = 0 THEN
1398 p_header_record.header_record.transaction_type := 'NEW';
1399 END IF;
1400 END IF;
1401
1402 -- Check for any shipment_num which exist for the same vendor site and within a year
1403 -- of the previous shipment with the same num. This is only done for transaction_type = NEW
1404 /*
1405 * BUGNO: 1708017
1406 * The where clause used to have organization_id =
1407 * p_header_record.header_record.ship_to_organization_id
1408 * This used to be populated with ship_to_organization_id.
1409 * Now this is populated as null since it is supposed to
1410 * be from organization_id. So changed it to ship_to_org_id.
1411 */
1412
1413 /* Fix for bug 2682881.
1414 * No validation on shipment_num was happening if a new ASN
1415 * is created with the same supplier,supplier site, shipment
1416 * num, but with different shipped_date. Shipment_num should
1417 * be unique from the supplier,supplier site for a period of
1418 * one year. Hence commented the condition "trunc(shipped_date)
1419 * = trunc(p_header_record.header_record.shipped_date) and"
1420 * from the following sql which is not required.
1421 */
1422 IF p_header_record.header_record.transaction_type = 'NEW'
1423 AND p_header_record.header_record.asn_type IN('ASN', 'ASBN', 'LCM') THEN /* lcm changes */
1424 SELECT COUNT(*)
1425 INTO x_count
1426 FROM rcv_shipment_headers
1427 WHERE NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, -9999)
1428 AND vendor_id = p_header_record.header_record.vendor_id
1429 AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
1430 AND shipment_num = p_header_record.header_record.shipment_num
1431 AND --trunc(shipped_date) = trunc(p_header_record.header_record.shipped_date) and
1432 shipped_date >= ADD_MONTHS(x_sysdate, -12);
1433
1434 IF x_count > 0 THEN
1435 /* Bug# 1413880
1436 As per the manual Shipment number should be unique for one year period for
1437 given supplier.Changing Warning to Error. */
1438 p_header_record.error_record.error_status := 'E';
1439 p_header_record.error_record.error_message := 'RCV_DUP_SHIPMENT_NUM';
1440 RETURN;
1441 END IF;
1442 END IF;
1443
1444 /*bug 2123721. bgopired
1445 We were not checking the uniqueness of shipment number incase of
1446 Standard Receipts. Used the same logic of Enter Receipt form to check
1447 the uniqueness */
1448 IF p_header_record.header_record.transaction_type = 'NEW'
1449 AND p_header_record.header_record.asn_type IN('STD') THEN
1450 IF NOT rcv_core_s.val_unique_shipment_num(p_header_record.header_record.shipment_num, p_header_record.header_record.vendor_id) THEN
1451 p_header_record.error_record.error_status := 'E';
1452 p_header_record.error_record.error_message := 'RCV_DUP_SHIPMENT_NUM';
1453 RETURN;
1454 END IF;
1455 END IF;
1456
1457 -- Check for matching ASN if ADD, CANCEL
1458 IF (g_asn_debug = 'Y') THEN
1459 asn_debug.put_line('Check for matching ASN if ADD, CANCEL');
1460 END IF;
1461
1462 /*
1463 * BUGNO: 1708017
1464 * The where clause used to have organization_id =
1465 * p_header_record.header_record.ship_to_organization_id
1466 * This used to be populated with ship_to_organization_id.
1467 * Now this is populated as null since it is supposed to
1468 * be from organization_id. So changed it to ship_to_org_id.
1469 */
1470 IF p_header_record.header_record.transaction_type IN('ADD', 'CANCEL')
1471 AND p_header_record.header_record.asn_type IN('ASN', 'ASBN') THEN
1472 SELECT COUNT(*)
1473 INTO x_count
1474 FROM rcv_shipment_headers
1475 WHERE NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, -9999)
1476 AND vendor_id = p_header_record.header_record.vendor_id
1477 AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
1478 AND shipment_num = p_header_record.header_record.shipment_num
1479 AND TRUNC(shipped_date) = TRUNC(p_header_record.header_record.shipped_date)
1480 AND shipped_date >= ADD_MONTHS(x_sysdate, -12);
1481
1482 IF x_count = 0 THEN
1483 p_header_record.error_record.error_status := 'E';
1484 p_header_record.error_record.error_message := 'RCV_NO_MATCHING_ASN';
1485 RETURN;
1486 END IF;
1487 END IF;
1488
1489 -- Check that there are no receipts against the ASN for ADD, CANCEL
1490 IF (g_asn_debug = 'Y') THEN
1491 asn_debug.put_line('Check that there are no receipts against the ASN for ADD, CANCEL');
1492 END IF;
1493
1494 IF p_header_record.header_record.transaction_type IN('ADD', 'CANCEL')
1495 AND p_header_record.header_record.asn_type IN('ASN', 'ASBN') THEN
1496 IF p_header_record.header_record.receipt_header_id IS NOT NULL THEN
1497 SELECT SUM(quantity_received)
1498 INTO x_count
1499 FROM rcv_shipment_lines
1500 WHERE rcv_shipment_lines.shipment_header_id = p_header_record.header_record.receipt_header_id;
1501 ELSE
1502 /*
1503 * BUGNO: 1708017
1504 * The where clause used to have organization_id =
1505 * p_header_record.header_record.ship_to_organization_id
1506 * This used to be populated with ship_to_organization_id.
1507 * Now this is populated as null since it is supposed to
1508 * be from organization_id. So changed it to ship_to_org_id.
1509 */
1510 SELECT SUM(quantity_received)
1511 INTO x_count
1512 FROM rcv_shipment_lines
1513 WHERE EXISTS(SELECT 'x'
1514 FROM rcv_shipment_headers
1515 WHERE rcv_shipment_headers.shipment_header_id = rcv_shipment_lines.shipment_header_id
1516 AND NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, -9999)
1517 AND vendor_id = p_header_record.header_record.vendor_id
1518 AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
1519 AND shipment_num = p_header_record.header_record.shipment_num
1520 AND TRUNC(shipped_date) = TRUNC(p_header_record.header_record.shipped_date)
1521 AND shipped_date >= ADD_MONTHS(x_sysdate, -12));
1522 END IF;
1523
1524 IF NVL(x_count, 0) > 0 THEN -- Some quantity has been received
1525 IF (g_asn_debug = 'Y') THEN
1526 asn_debug.put_line('There are receipts against the ASN ' || p_header_record.header_record.shipment_num);
1527 END IF;
1528
1529 p_header_record.error_record.error_status := 'E';
1530 p_header_record.error_record.error_message := 'RCV_ASN_QTY_RECEIVED';
1531 RETURN;
1532 END IF;
1533 END IF;
1534
1535 -- If we have reached this place that means the shipment exists
1536 -- Make sure we have a shipment header id
1537 IF (g_asn_debug = 'Y') THEN
1538 asn_debug.put_line('Make sure we have a shipment_header_id');
1539 END IF;
1540
1541 /*
1542 * BUGNO: 1708017
1543 * The where clause used to have organization_id =
1544 * p_header_record.header_record.ship_to_organization_id
1545 * This used to be populated with ship_to_organization_id.
1546 * Now this is populated as null since it is supposed to
1547 * be from organization_id. So changed it to ship_to_org_id.
1548 */
1549 IF p_header_record.header_record.transaction_type IN('CANCEL')
1550 AND p_header_record.header_record.receipt_header_id IS NULL THEN
1551 SELECT MAX(shipment_header_id)
1552 INTO p_header_record.header_record.receipt_header_id
1553 FROM rcv_shipment_headers
1554 WHERE NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, -9999)
1555 AND vendor_id = p_header_record.header_record.vendor_id
1556 AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
1557 AND shipment_num = p_header_record.header_record.shipment_num
1558 AND TRUNC(shipped_date) = TRUNC(p_header_record.header_record.shipped_date)
1559 AND shipped_date >= ADD_MONTHS(x_sysdate, -12);
1560 END IF;
1561
1562 -- Verify that the shipment_header_id matches the derived/defaulted shipment_header_id
1563 IF (g_asn_debug = 'Y') THEN
1564 asn_debug.put_line('Verify that the shipment_header_id matches the derived/defaulted shipment_header_id');
1565 END IF;
1566
1567 /*
1568 * BUGNO: 1708017
1569 * The where clause used to have organization_id =
1570 * p_header_record.header_record.ship_to_organization_id
1571 * This used to be populated with ship_to_organization_id.
1572 * Now this is populated as null since it is supposed to
1573 * be from organization_id. So changed it to ship_to_org_id.
1574 */
1575 IF p_header_record.header_record.transaction_type IN('CANCEL')
1576 AND p_header_record.header_record.receipt_header_id IS NOT NULL THEN
1577 SELECT MAX(shipment_header_id)
1578 INTO x_shipment_header_id
1579 FROM rcv_shipment_headers
1580 WHERE NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, -9999)
1581 AND vendor_id = p_header_record.header_record.vendor_id
1582 AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
1583 AND shipment_num = p_header_record.header_record.shipment_num
1584 AND TRUNC(shipped_date) = TRUNC(p_header_record.header_record.shipped_date)
1585 AND shipped_date >= ADD_MONTHS(x_sysdate, -12);
1586
1587 IF x_shipment_header_id <> p_header_record.header_record.receipt_header_id THEN
1588 IF (g_asn_debug = 'Y') THEN
1589 asn_debug.put_line('The shipment_header_id do not match ');
1590 END IF;
1591
1592 p_header_record.error_record.error_status := 'E';
1593 p_header_record.error_record.error_message := 'RCV_ASN_MISMATCH_SHIP_ID';
1594 END IF;
1595 END IF;
1596 EXCEPTION
1597 WHEN OTHERS THEN
1598 IF (g_asn_debug = 'Y') THEN
1599 asn_debug.put_line('Exception in validate_shipment_header ');
1600 END IF;
1601
1602 p_header_record.error_record.error_status := 'U';
1603 p_header_record.error_record.error_message := SQLERRM;
1604 END validate_shipment_number;
1605
1606 PROCEDURE insert_shipment_header(
1607 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
1608 ) IS
1609 BEGIN
1610 -- Set asn_type to null if asn_type is STD as the UI gets confused
1611
1612 IF NVL(p_header_record.header_record.asn_type, 'STD') = 'STD' THEN
1613 p_header_record.header_record.asn_type := NULL;
1614 END IF;
1615
1616 /* Bug - 1086088 - Ship_to_org_id needs to get populated in the
1617 * RCV_SHIPMENT_HEADERS table */
1618 IF (g_asn_debug = 'Y') THEN
1619 asn_debug.put_line('Before insert into rsh ');
1620 END IF;
1621
1622 INSERT INTO rcv_shipment_headers
1623 (shipment_header_id,
1624 last_update_date,
1625 last_updated_by,
1626 creation_date,
1627 created_by,
1628 last_update_login,
1629 receipt_source_code,
1630 vendor_id,
1631 vendor_site_id,
1632 organization_id,
1633 shipment_num,
1634 receipt_num,
1635 ship_to_location_id,
1636 ship_to_org_id,
1637 bill_of_lading,
1638 packing_slip,
1639 shipped_date,
1640 freight_carrier_code,
1641 expected_receipt_date,
1642 employee_id,
1643 num_of_containers,
1644 waybill_airbill_num,
1645 comments,
1646 attribute_category,
1647 attribute1,
1648 attribute2,
1649 attribute3,
1650 attribute4,
1651 attribute5,
1652 attribute6,
1653 attribute7,
1654 attribute8,
1655 attribute9,
1656 attribute10,
1657 attribute11,
1658 attribute12,
1659 attribute13,
1660 attribute14,
1661 attribute15,
1662 ussgl_transaction_code,
1663 government_context,
1664 request_id,
1665 program_application_id,
1666 program_id,
1667 program_update_date,
1668 asn_type,
1669 edi_control_num,
1670 notice_creation_date,
1671 gross_weight,
1672 gross_weight_uom_code,
1673 net_weight,
1674 net_weight_uom_code,
1675 tar_weight,
1676 tar_weight_uom_code,
1677 packaging_code,
1678 carrier_method,
1679 carrier_equipment,
1680 carrier_equipment_num,
1681 carrier_equipment_alpha,
1682 special_handling_code,
1683 hazard_code,
1684 hazard_class,
1685 hazard_description,
1686 freight_terms,
1687 freight_bill_number,
1688 invoice_date,
1689 invoice_amount,
1690 tax_name,
1691 tax_amount,
1692 freight_amount,
1693 invoice_status_code,
1694 asn_status,
1695 currency_code,
1696 conversion_rate_type,
1697 conversion_rate,
1698 conversion_date,
1699 payment_terms_id,
1700 invoice_num,
1701 remit_to_site_id,
1702 ship_from_location_id,
1703 performance_period_from, --Complex Work
1704 performance_period_to, --Complex Work
1705 request_date --Complex Work
1706 )
1707 VALUES (p_header_record.header_record.receipt_header_id,
1708 p_header_record.header_record.last_update_date,
1709 p_header_record.header_record.last_updated_by,
1710 p_header_record.header_record.creation_date,
1711 p_header_record.header_record.created_by,
1712 p_header_record.header_record.last_update_login,
1713 p_header_record.header_record.receipt_source_code,
1714 p_header_record.header_record.vendor_id,
1715 p_header_record.header_record.vendor_site_id,
1716 TO_NUMBER(NULL), -- this is the from organization id and shld be null instead of ship_to_org_id
1717 p_header_record.header_record.shipment_num,
1718 p_header_record.header_record.receipt_num,
1719 p_header_record.header_record.location_id,
1720 p_header_record.header_record.ship_to_organization_id,
1721 p_header_record.header_record.bill_of_lading,
1722 p_header_record.header_record.packing_slip,
1723 p_header_record.header_record.shipped_date,
1724 p_header_record.header_record.freight_carrier_code,
1725 p_header_record.header_record.expected_receipt_date,
1726 p_header_record.header_record.employee_id,
1727 p_header_record.header_record.num_of_containers,
1728 p_header_record.header_record.waybill_airbill_num,
1729 p_header_record.header_record.comments,
1730 p_header_record.header_record.attribute_category,
1731 p_header_record.header_record.attribute1,
1732 p_header_record.header_record.attribute2,
1733 p_header_record.header_record.attribute3,
1734 p_header_record.header_record.attribute4,
1735 p_header_record.header_record.attribute5,
1736 p_header_record.header_record.attribute6,
1737 p_header_record.header_record.attribute7,
1738 p_header_record.header_record.attribute8,
1739 p_header_record.header_record.attribute9,
1740 p_header_record.header_record.attribute10,
1741 p_header_record.header_record.attribute11,
1742 p_header_record.header_record.attribute12,
1743 p_header_record.header_record.attribute13,
1744 p_header_record.header_record.attribute14,
1745 p_header_record.header_record.attribute15,
1746 p_header_record.header_record.usggl_transaction_code,
1747 NULL, -- p_header_record.header_record.Government_Context
1748 fnd_global.conc_request_id,
1749 fnd_global.prog_appl_id,
1750 fnd_global.conc_program_id,
1751 x_sysdate,
1752 p_header_record.header_record.asn_type,
1753 p_header_record.header_record.edi_control_num,
1754 p_header_record.header_record.notice_creation_date,
1755 p_header_record.header_record.gross_weight,
1756 p_header_record.header_record.gross_weight_uom_code,
1757 p_header_record.header_record.net_weight,
1758 p_header_record.header_record.net_weight_uom_code,
1759 p_header_record.header_record.tar_weight,
1760 p_header_record.header_record.tar_weight_uom_code,
1761 p_header_record.header_record.packaging_code,
1762 p_header_record.header_record.carrier_method,
1763 p_header_record.header_record.carrier_equipment,
1764 NULL, -- p_header_record.header_record.Carrier_Equipment_Num
1765 NULL, -- p_header_record.header_record.Carrier_Equipment_Alpha
1766 p_header_record.header_record.special_handling_code,
1767 p_header_record.header_record.hazard_code,
1768 p_header_record.header_record.hazard_class,
1769 p_header_record.header_record.hazard_description,
1770 p_header_record.header_record.freight_terms,
1771 p_header_record.header_record.freight_bill_number,
1772 p_header_record.header_record.invoice_date,
1773 p_header_record.header_record.total_invoice_amount,
1774 p_header_record.header_record.tax_name,
1775 p_header_record.header_record.tax_amount,
1776 p_header_record.header_record.freight_amount,
1777 p_header_record.header_record.invoice_status_code,
1778 'NEW_SHIP', -- p_header_record.header_record.Asn_Status
1779 p_header_record.header_record.currency_code,
1780 p_header_record.header_record.conversion_rate_type,
1781 p_header_record.header_record.conversion_rate,
1782 p_header_record.header_record.conversion_rate_date,
1783 p_header_record.header_record.payment_terms_id,
1784 p_header_record.header_record.invoice_num,
1785 p_header_record.header_record.remit_to_site_id,
1786 p_header_record.header_record.ship_from_location_id,
1787 /* Complex Work. Added new columns */
1788 p_header_record.header_record.performance_period_from,
1789 p_header_record.header_record.performance_period_to,
1790 p_header_record.header_record.request_date
1791 );
1792
1793 IF (g_asn_debug = 'Y') THEN
1794 asn_debug.put_line('After insert into rsh ');
1795 END IF;
1796
1797 EXCEPTION
1798 WHEN OTHERS THEN
1799 IF (g_asn_debug = 'Y') THEN
1800 asn_debug.put_line('Exception in insert_shipment_header ');
1801 END IF;
1802
1803 p_header_record.error_record.error_status := 'U';
1804 p_header_record.error_record.error_message := SQLERRM;
1805 END insert_shipment_header;
1806
1807 PROCEDURE update_shipment_header(
1808 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
1809 ) IS
1810 BEGIN
1811 IF (g_asn_debug = 'Y') THEN
1812 asn_debug.put_line('Enter in update_shipment_header ');
1813 asn_debug.put_line(' Shipment_header_id ' || p_header_record.header_record.receipt_header_id);
1814 END IF;
1815
1816 UPDATE rcv_shipment_headers
1817 SET receipt_num = NVL(receipt_num, p_header_record.header_record.receipt_num),
1818 bill_of_lading = p_header_record.header_record.bill_of_lading,
1819 packing_slip = p_header_record.header_record.packing_slip,
1820 freight_carrier_code = p_header_record.header_record.freight_carrier_code,
1821 expected_receipt_date = p_header_record.header_record.expected_receipt_date,
1822 employee_id = p_header_record.header_record.employee_id,
1823 num_of_containers = p_header_record.header_record.num_of_containers,
1824 waybill_airbill_num = p_header_record.header_record.waybill_airbill_num,
1825 comments = p_header_record.header_record.comments,
1826 attribute1 = p_header_record.header_record.attribute1,
1827 attribute2 = p_header_record.header_record.attribute2,
1828 attribute3 = p_header_record.header_record.attribute3,
1829 attribute4 = p_header_record.header_record.attribute4,
1830 attribute5 = p_header_record.header_record.attribute5,
1831 attribute6 = p_header_record.header_record.attribute6,
1832 attribute7 = p_header_record.header_record.attribute7,
1833 attribute8 = p_header_record.header_record.attribute8,
1834 attribute9 = p_header_record.header_record.attribute9,
1835 attribute10 = p_header_record.header_record.attribute10,
1836 attribute11 = p_header_record.header_record.attribute11,
1837 attribute12 = p_header_record.header_record.attribute12,
1838 attribute13 = p_header_record.header_record.attribute13,
1839 attribute14 = p_header_record.header_record.attribute14,
1840 attribute15 = p_header_record.header_record.attribute15
1841 WHERE shipment_header_id = p_header_record.header_record.receipt_header_id;
1842
1843 IF (g_asn_debug = 'Y') THEN
1844 asn_debug.put_line('After updating rsh ');
1845 END IF;
1846 EXCEPTION
1847 WHEN OTHERS THEN
1848 IF (g_asn_debug = 'Y') THEN
1849 asn_debug.put_line('Exception in update_shipment_header ');
1850 END IF;
1851
1852 p_header_record.error_record.error_status := 'U';
1853 p_header_record.error_record.error_message := SQLERRM;
1854 END update_shipment_header;
1855
1856 PROCEDURE insert_cancelled_asn_lines(
1857 p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
1858 ) IS
1859 BEGIN
1860 -- delete any asn lines that have been sent
1861
1862 asn_debug.put_line('Delete any asn lines that have been sent');
1863
1864 DELETE FROM rcv_transactions_interface
1865 WHERE header_interface_id = p_header_record.header_record.header_interface_id;
1866
1867 -- Insert lines from rcv_shipment_lines into rcv_transactions_interface
1868
1869 -- Make sure we don't inset cancelled lines and lines that are waiting to
1870 -- be cancelled in rti
1871 -- The transaction processor will then cancel the lines
1872
1873 -- Bug 587603 Inserting processing request id for CANCEL otherwise
1874 -- transaction processor will not look at it.
1875
1876 /* <R12 MOAC START>
1877 * Populate the org_id column in rcv_transactions_interface with org_id from
1878 * po_headers_all table
1879 */
1880
1881 INSERT INTO rcv_transactions_interface
1882 (interface_transaction_id,
1883 header_interface_id,
1884 GROUP_ID,
1885 last_update_date,
1886 last_updated_by,
1887 last_update_login,
1888 creation_date,
1889 created_by,
1890 transaction_type,
1891 transaction_date,
1892 processing_status_code,
1893 processing_mode_code,
1894 transaction_status_code,
1895 category_id,
1896 quantity,
1897 unit_of_measure,
1898 interface_source_code,
1899 item_id,
1900 item_description,
1901 employee_id,
1902 auto_transact_code,
1903 receipt_source_code,
1904 vendor_id,
1905 to_organization_id,
1906 source_document_code,
1907 po_header_id,
1908 po_line_id,
1909 po_line_location_id,
1910 shipment_header_id,
1911 shipment_line_id,
1912 destination_type_code,
1913 processing_request_id,
1914 org_id
1915 )
1916 SELECT rcv_transactions_interface_s.NEXTVAL,
1917 p_header_record.header_record.header_interface_id,
1918 p_header_record.header_record.GROUP_ID,
1919 p_header_record.header_record.last_update_date,
1920 p_header_record.header_record.last_updated_by,
1921 p_header_record.header_record.last_update_login,
1922 p_header_record.header_record.creation_date,
1923 p_header_record.header_record.created_by,
1924 'CANCEL',
1925 NVL(p_header_record.header_record.notice_creation_date, SYSDATE),
1926 'RUNNING', -- This has to be set to running otherwise C code in rvtbm
1927 -- will not pick it up
1928 'BATCH',
1929 'PENDING',
1930 rsl.category_id,
1931 rsl.quantity_shipped,
1932 rsl.unit_of_measure,
1933 'RCV',
1934 rsl.item_id,
1935 rsl.item_description,
1936 rsl.employee_id,
1937 'CANCEL',
1938 'VENDOR',
1939 p_header_record.header_record.vendor_id,
1940 rsl.to_organization_id,
1941 'PO',
1942 rsl.po_header_id,
1943 rsl.po_line_id,
1944 rsl.po_line_location_id,
1945 rsl.shipment_header_id,
1946 rsl.shipment_line_id,
1947 rsl.destination_type_code,
1948 p_header_record.header_record.processing_request_id,
1949 poh.org_id
1950 FROM rcv_shipment_lines rsl,
1951 po_headers_all poh
1952 WHERE rsl.shipment_header_id = p_header_record.header_record.receipt_header_id
1953 AND rsl.shipment_line_status_code <> 'CANCELLED'
1954 AND rsl.po_header_id = poh.po_header_id
1955 AND NOT EXISTS(SELECT 'x'
1956 FROM rcv_transactions_interface rti
1957 WHERE rti.shipment_line_id = rsl.shipment_line_id
1958 AND rti.shipment_header_id = rsl.shipment_header_id
1959 AND rti.transaction_type = 'CANCEL'
1960 AND rti.shipment_header_id = p_header_record.header_record.receipt_header_id);
1961 --<R12 MOAC END>
1962 END insert_cancelled_asn_lines;
1963 END rcv_roi_header;
[Home] [Help]