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