[Home] [Help]
320: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
321: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Begin Procedure');
322: END IF;
323: IF (p_x_osp_order_rec.operation_flag IS NOT NULL AND p_x_osp_order_rec.operation_flag NOT IN(G_OP_CREATE, G_OP_UPDATE, G_OP_DELETE)) THEN
324: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INVOP');
325: FND_MSG_PUB.ADD;
326: END IF;
327: IF FND_MSG_PUB.count_msg > 0 THEN
328: RAISE FND_API.G_EXC_ERROR;
395: END LOOP;
396: CLOSE vendor_id_csr;
397: IF(l_count = 0 ) THEN
398: --dbms_output.put_line('checkpoint 1');
399: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_NAME_INV');
400: FND_MESSAGE.Set_Token('VENDOR_NAME', p_x_osp_order_rec.vendor_name);
401: FND_MSG_PUB.ADD;
402: ELSIF (l_count > 1) THEN
403: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_INV_NON_UNQ');
396: CLOSE vendor_id_csr;
397: IF(l_count = 0 ) THEN
398: --dbms_output.put_line('checkpoint 1');
399: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_NAME_INV');
400: FND_MESSAGE.Set_Token('VENDOR_NAME', p_x_osp_order_rec.vendor_name);
401: FND_MSG_PUB.ADD;
402: ELSIF (l_count > 1) THEN
403: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_INV_NON_UNQ');
404: FND_MESSAGE.Set_Token('VENDOR_NAME', p_x_osp_order_rec.vendor_name);
399: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_NAME_INV');
400: FND_MESSAGE.Set_Token('VENDOR_NAME', p_x_osp_order_rec.vendor_name);
401: FND_MSG_PUB.ADD;
402: ELSIF (l_count > 1) THEN
403: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_INV_NON_UNQ');
404: FND_MESSAGE.Set_Token('VENDOR_NAME', p_x_osp_order_rec.vendor_name);
405: FND_MSG_PUB.ADD;
406: ELSE
407: p_x_osp_order_rec.vendor_id := l_vendor_id;
400: FND_MESSAGE.Set_Token('VENDOR_NAME', p_x_osp_order_rec.vendor_name);
401: FND_MSG_PUB.ADD;
402: ELSIF (l_count > 1) THEN
403: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_INV_NON_UNQ');
404: FND_MESSAGE.Set_Token('VENDOR_NAME', p_x_osp_order_rec.vendor_name);
405: FND_MSG_PUB.ADD;
406: ELSE
407: p_x_osp_order_rec.vendor_id := l_vendor_id;
408: END IF;
424: END IF;
425: END LOOP;
426: CLOSE vendor_site_id_csr;
427: IF(l_count = 0 ) THEN
428: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_INV');
429: FND_MESSAGE.Set_Token('VENDOR_SITE', p_x_osp_order_rec.vendor_site_code);
430: FND_MSG_PUB.ADD;
431: ELSIF (l_count > 1) THEN
432: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_INV_NON_UNQ');
425: END LOOP;
426: CLOSE vendor_site_id_csr;
427: IF(l_count = 0 ) THEN
428: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_INV');
429: FND_MESSAGE.Set_Token('VENDOR_SITE', p_x_osp_order_rec.vendor_site_code);
430: FND_MSG_PUB.ADD;
431: ELSIF (l_count > 1) THEN
432: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_INV_NON_UNQ');
433: FND_MESSAGE.Set_Token('VENDOR_SITE', p_x_osp_order_rec.vendor_site_code);
428: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_INV');
429: FND_MESSAGE.Set_Token('VENDOR_SITE', p_x_osp_order_rec.vendor_site_code);
430: FND_MSG_PUB.ADD;
431: ELSIF (l_count > 1) THEN
432: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_INV_NON_UNQ');
433: FND_MESSAGE.Set_Token('VENDOR_SITE', p_x_osp_order_rec.vendor_site_code);
434: FND_MSG_PUB.ADD;
435: ELSE
436: p_x_osp_order_rec.vendor_site_id := l_vendor_site_id;
429: FND_MESSAGE.Set_Token('VENDOR_SITE', p_x_osp_order_rec.vendor_site_code);
430: FND_MSG_PUB.ADD;
431: ELSIF (l_count > 1) THEN
432: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_INV_NON_UNQ');
433: FND_MESSAGE.Set_Token('VENDOR_SITE', p_x_osp_order_rec.vendor_site_code);
434: FND_MSG_PUB.ADD;
435: ELSE
436: p_x_osp_order_rec.vendor_site_id := l_vendor_site_id;
437: END IF;
452: END IF;
453: END LOOP;
454: CLOSE po_agent_id_csr;
455: IF(l_count = 0 ) THEN
456: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_INV');
457: FND_MESSAGE.Set_Token('BUYER_NAME', p_x_osp_order_rec.buyer_name);
458: FND_MSG_PUB.ADD;
459: ELSIF (l_count > 1) THEN
460: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_INV_NON_UNQ');
453: END LOOP;
454: CLOSE po_agent_id_csr;
455: IF(l_count = 0 ) THEN
456: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_INV');
457: FND_MESSAGE.Set_Token('BUYER_NAME', p_x_osp_order_rec.buyer_name);
458: FND_MSG_PUB.ADD;
459: ELSIF (l_count > 1) THEN
460: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_INV_NON_UNQ');
461: FND_MESSAGE.Set_Token('BUYER_NAME', p_x_osp_order_rec.buyer_name);
456: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_INV');
457: FND_MESSAGE.Set_Token('BUYER_NAME', p_x_osp_order_rec.buyer_name);
458: FND_MSG_PUB.ADD;
459: ELSIF (l_count > 1) THEN
460: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_INV_NON_UNQ');
461: FND_MESSAGE.Set_Token('BUYER_NAME', p_x_osp_order_rec.buyer_name);
462: FND_MSG_PUB.ADD;
463: ELSE
464: p_x_osp_order_rec.po_agent_id := l_po_agent_id;
457: FND_MESSAGE.Set_Token('BUYER_NAME', p_x_osp_order_rec.buyer_name);
458: FND_MSG_PUB.ADD;
459: ELSIF (l_count > 1) THEN
460: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_INV_NON_UNQ');
461: FND_MESSAGE.Set_Token('BUYER_NAME', p_x_osp_order_rec.buyer_name);
462: FND_MSG_PUB.ADD;
463: ELSE
464: p_x_osp_order_rec.po_agent_id := l_po_agent_id;
465: END IF;
481: END IF;
482: END LOOP;
483: CLOSE customer_id_csr;
484: IF(l_count = 0 ) THEN
485: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_INV');
486: FND_MESSAGE.Set_Token('CUST_NAME', p_x_osp_order_rec.customer_name);
487: FND_MSG_PUB.ADD;
488: ELSIF (l_count > 1) THEN
489: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_INV_NON_UNQ');
482: END LOOP;
483: CLOSE customer_id_csr;
484: IF(l_count = 0 ) THEN
485: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_INV');
486: FND_MESSAGE.Set_Token('CUST_NAME', p_x_osp_order_rec.customer_name);
487: FND_MSG_PUB.ADD;
488: ELSIF (l_count > 1) THEN
489: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_INV_NON_UNQ');
490: FND_MESSAGE.Set_Token('CUST_NAME', p_x_osp_order_rec.customer_name);
485: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_INV');
486: FND_MESSAGE.Set_Token('CUST_NAME', p_x_osp_order_rec.customer_name);
487: FND_MSG_PUB.ADD;
488: ELSIF (l_count > 1) THEN
489: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_INV_NON_UNQ');
490: FND_MESSAGE.Set_Token('CUST_NAME', p_x_osp_order_rec.customer_name);
491: FND_MSG_PUB.ADD;
492: ELSE
493: p_x_osp_order_rec.customer_id := l_customer_id;
486: FND_MESSAGE.Set_Token('CUST_NAME', p_x_osp_order_rec.customer_name);
487: FND_MSG_PUB.ADD;
488: ELSIF (l_count > 1) THEN
489: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_INV_NON_UNQ');
490: FND_MESSAGE.Set_Token('CUST_NAME', p_x_osp_order_rec.customer_name);
491: FND_MSG_PUB.ADD;
492: ELSE
493: p_x_osp_order_rec.customer_id := l_customer_id;
494: END IF;
498: --dbms_output.put_line('converting contract_number to id');
499: OPEN contract_id_csr(p_x_osp_order_rec.contract_number);
500: FETCH contract_id_csr INTO l_contract_id;
501: IF(contract_id_csr%NOTFOUND) THEN
502: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CONTRACT_INV');
503: FND_MESSAGE.Set_Token('CONTRACT_NUM', p_x_osp_order_rec.contract_number);
504: FND_MSG_PUB.ADD;
505: ELSE
506: p_x_osp_order_rec.contract_id := l_contract_id;
499: OPEN contract_id_csr(p_x_osp_order_rec.contract_number);
500: FETCH contract_id_csr INTO l_contract_id;
501: IF(contract_id_csr%NOTFOUND) THEN
502: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CONTRACT_INV');
503: FND_MESSAGE.Set_Token('CONTRACT_NUM', p_x_osp_order_rec.contract_number);
504: FND_MSG_PUB.ADD;
505: ELSE
506: p_x_osp_order_rec.contract_id := l_contract_id;
507: END IF;
518: IF (p_x_osp_order_rec.osp_order_number IS NOT NULL) THEN
519: OPEN osp_order_on_csr(p_x_osp_order_rec.osp_order_number);
520: FETCH osp_order_on_csr INTO l_osp_order_id,l_object_version_number;
521: IF(osp_order_on_csr%NOTFOUND) THEN
522: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_NUM_INV');
523: FND_MESSAGE.Set_Token('ORDER_NUMBER', p_x_osp_order_rec.osp_order_number);
524: FND_MSG_PUB.ADD;
525: ELSE
526: p_x_osp_order_rec.osp_order_id := l_osp_order_id;
519: OPEN osp_order_on_csr(p_x_osp_order_rec.osp_order_number);
520: FETCH osp_order_on_csr INTO l_osp_order_id,l_object_version_number;
521: IF(osp_order_on_csr%NOTFOUND) THEN
522: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_NUM_INV');
523: FND_MESSAGE.Set_Token('ORDER_NUMBER', p_x_osp_order_rec.osp_order_number);
524: FND_MSG_PUB.ADD;
525: ELSE
526: p_x_osp_order_rec.osp_order_id := l_osp_order_id;
527: p_x_osp_order_rec.object_version_number := l_object_version_number;
532: ELSIF (p_x_osp_order_rec.po_header_id IS NOT NULL AND p_x_osp_order_rec.po_header_id <> FND_API.G_MISS_NUM) THEN
533: OPEN osp_order_po_csr(p_x_osp_order_rec.po_header_id);
534: FETCH osp_order_po_csr INTO l_osp_order_id,l_object_version_number;
535: IF(osp_order_po_csr%NOTFOUND) THEN
536: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PO_HEADER_INV');
537: FND_MESSAGE.Set_Token('PO_HEADER_ID', p_x_osp_order_rec.po_header_id);
538: FND_MSG_PUB.ADD;
539: ELSE
540: p_x_osp_order_rec.osp_order_id := l_osp_order_id;
533: OPEN osp_order_po_csr(p_x_osp_order_rec.po_header_id);
534: FETCH osp_order_po_csr INTO l_osp_order_id,l_object_version_number;
535: IF(osp_order_po_csr%NOTFOUND) THEN
536: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PO_HEADER_INV');
537: FND_MESSAGE.Set_Token('PO_HEADER_ID', p_x_osp_order_rec.po_header_id);
538: FND_MSG_PUB.ADD;
539: ELSE
540: p_x_osp_order_rec.osp_order_id := l_osp_order_id;
541: p_x_osp_order_rec.object_version_number := l_object_version_number;
544: ELSIF (p_x_osp_order_rec.oe_header_id IS NOT NULL AND p_x_osp_order_rec.oe_header_id <> FND_API.G_MISS_NUM) THEN
545: OPEN osp_order_oe_csr(p_x_osp_order_rec.oe_header_id);
546: FETCH osp_order_oe_csr INTO l_osp_order_id,l_object_version_number;
547: IF(osp_order_oe_csr%NOTFOUND) THEN
548: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_OE_HEADER_INV');
549: FND_MESSAGE.Set_Token('OE_HEADER_ID', p_x_osp_order_rec.oe_header_id);
550: FND_MSG_PUB.ADD;
551: ELSE
552: p_x_osp_order_rec.osp_order_id := l_osp_order_id;
545: OPEN osp_order_oe_csr(p_x_osp_order_rec.oe_header_id);
546: FETCH osp_order_oe_csr INTO l_osp_order_id,l_object_version_number;
547: IF(osp_order_oe_csr%NOTFOUND) THEN
548: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_OE_HEADER_INV');
549: FND_MESSAGE.Set_Token('OE_HEADER_ID', p_x_osp_order_rec.oe_header_id);
550: FND_MSG_PUB.ADD;
551: ELSE
552: p_x_osp_order_rec.osp_order_id := l_osp_order_id;
553: p_x_osp_order_rec.object_version_number := l_object_version_number;
555: CLOSE osp_order_oe_csr;
556: */
557: -- jaramana End
558: ELSE
559: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_HEADER_INV');
560: FND_MSG_PUB.ADD;
561: END IF;
562: END IF;
563: END IF;
595: IF(p_vendor_id IS NOT NULL AND p_vendor_id <> FND_API.G_MISS_NUM) THEN
596: OPEN val_vendor_id_csr(p_vendor_id);
597: FETCH val_vendor_id_csr INTO l_exist;
598: IF(val_vendor_id_csr%NOTFOUND) THEN
599: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
600: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
601: FND_MSG_PUB.ADD;
602: END IF;
603: CLOSE val_vendor_id_csr;
596: OPEN val_vendor_id_csr(p_vendor_id);
597: FETCH val_vendor_id_csr INTO l_exist;
598: IF(val_vendor_id_csr%NOTFOUND) THEN
599: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
600: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
601: FND_MSG_PUB.ADD;
602: END IF;
603: CLOSE val_vendor_id_csr;
604: OPEN get_vendor_cert;
603: CLOSE val_vendor_id_csr;
604: OPEN get_vendor_cert;
605: FETCH get_vendor_cert INTO l_exist;
606: IF(get_vendor_cert%NOTFOUND) THEN
607: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
608: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
609: FND_MSG_PUB.ADD;
610: END IF;
611: CLOSE get_vendor_cert;
604: OPEN get_vendor_cert;
605: FETCH get_vendor_cert INTO l_exist;
606: IF(get_vendor_cert%NOTFOUND) THEN
607: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
608: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
609: FND_MSG_PUB.ADD;
610: END IF;
611: CLOSE get_vendor_cert;
612: END IF;
638: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Begin Procedure');
639: END IF;
640: IF (p_vendor_id IS NULL OR p_vendor_id = FND_API.G_MISS_NUM) THEN
641: IF(p_vendor_site_id IS NOT NULL AND p_vendor_site_id <> FND_API.G_MISS_NUM) THEN
642: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VEND_ID_NLL');
643: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
644: FND_MSG_PUB.ADD;
645: END IF;
646: ELSE
639: END IF;
640: IF (p_vendor_id IS NULL OR p_vendor_id = FND_API.G_MISS_NUM) THEN
641: IF(p_vendor_site_id IS NOT NULL AND p_vendor_site_id <> FND_API.G_MISS_NUM) THEN
642: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VEND_ID_NLL');
643: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
644: FND_MSG_PUB.ADD;
645: END IF;
646: ELSE
647: IF(p_vendor_site_id IS NOT NULL AND p_vendor_site_id <> FND_API.G_MISS_NUM)THEN
647: IF(p_vendor_site_id IS NOT NULL AND p_vendor_site_id <> FND_API.G_MISS_NUM)THEN
648: OPEN val_vendor_site_id_csr(p_vendor_id, p_vendor_site_id);
649: FETCH val_vendor_site_id_csr INTO l_exist;
650: IF(val_vendor_site_id_csr%NOTFOUND) THEN
651: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
652: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
653: FND_MSG_PUB.ADD;
654: END IF;
655: CLOSE val_vendor_site_id_csr;
648: OPEN val_vendor_site_id_csr(p_vendor_id, p_vendor_site_id);
649: FETCH val_vendor_site_id_csr INTO l_exist;
650: IF(val_vendor_site_id_csr%NOTFOUND) THEN
651: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
652: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
653: FND_MSG_PUB.ADD;
654: END IF;
655: CLOSE val_vendor_site_id_csr;
656: OPEN get_vendor_cert;
655: CLOSE val_vendor_site_id_csr;
656: OPEN get_vendor_cert;
657: FETCH get_vendor_cert INTO l_exist;
658: IF(get_vendor_cert%NOTFOUND) THEN
659: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
660: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
661: FND_MSG_PUB.ADD;
662: END IF;
663: CLOSE get_vendor_cert;
656: OPEN get_vendor_cert;
657: FETCH get_vendor_cert INTO l_exist;
658: IF(get_vendor_cert%NOTFOUND) THEN
659: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
660: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
661: FND_MSG_PUB.ADD;
662: END IF;
663: CLOSE get_vendor_cert;
664: END IF;
684: IF(p_customer_id IS NOT NULL AND p_customer_id <> FND_API.G_MISS_NUM) THEN
685: OPEN val_customer_id_csr(p_customer_id);
686: FETCH val_customer_id_csr INTO l_exist;
687: IF(val_customer_id_csr%NOTFOUND) THEN
688: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_ID_INV');
689: FND_MESSAGE.Set_Token('CUSTOMER_ID', p_customer_id);
690: FND_MSG_PUB.ADD;
691: END IF;
692: CLOSE val_customer_id_csr;
685: OPEN val_customer_id_csr(p_customer_id);
686: FETCH val_customer_id_csr INTO l_exist;
687: IF(val_customer_id_csr%NOTFOUND) THEN
688: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_ID_INV');
689: FND_MESSAGE.Set_Token('CUSTOMER_ID', p_customer_id);
690: FND_MSG_PUB.ADD;
691: END IF;
692: CLOSE val_customer_id_csr;
693: END IF;
712: IF(p_po_agent_id IS NOT NULL AND p_po_agent_id <> FND_API.G_MISS_NUM) THEN
713: OPEN val_po_agent_id_csr(p_po_agent_id);
714: FETCH val_po_agent_id_csr INTO l_exist;
715: IF(val_po_agent_id_csr%NOTFOUND) THEN
716: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_ID_INV');
717: FND_MESSAGE.Set_Token('BUYER_ID', p_po_agent_id);
718: FND_MSG_PUB.ADD;
719: END IF;
720: CLOSE val_po_agent_id_csr;
713: OPEN val_po_agent_id_csr(p_po_agent_id);
714: FETCH val_po_agent_id_csr INTO l_exist;
715: IF(val_po_agent_id_csr%NOTFOUND) THEN
716: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_ID_INV');
717: FND_MESSAGE.Set_Token('BUYER_ID', p_po_agent_id);
718: FND_MSG_PUB.ADD;
719: END IF;
720: CLOSE val_po_agent_id_csr;
721: END IF;
753: ELSIF (p_order_type_code = G_OSP_ORDER_TYPE_LOAN) THEN
754: l_buy_or_sell := 'S';
755: l_object_code := 'OKX_PARTY';
756: ELSE
757: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_CTRCT');
758: FND_MSG_PUB.ADD;
759: RETURN;
760: END IF;
761: IF(p_party_vendor_id IS NULL OR p_party_vendor_id = FND_API.G_MISS_NUM) THEN
759: RETURN;
760: END IF;
761: IF(p_party_vendor_id IS NULL OR p_party_vendor_id = FND_API.G_MISS_NUM) THEN
762: IF(p_contract_id IS NOT NULL AND p_contract_id <> FND_API.G_MISS_NUM) THEN
763: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VEND_PTY_ID_NLL');
764: FND_MESSAGE.Set_Token('CONTRACT_ID', p_contract_id);
765: FND_MSG_PUB.ADD;
766: END IF;
767: ELSE
760: END IF;
761: IF(p_party_vendor_id IS NULL OR p_party_vendor_id = FND_API.G_MISS_NUM) THEN
762: IF(p_contract_id IS NOT NULL AND p_contract_id <> FND_API.G_MISS_NUM) THEN
763: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VEND_PTY_ID_NLL');
764: FND_MESSAGE.Set_Token('CONTRACT_ID', p_contract_id);
765: FND_MSG_PUB.ADD;
766: END IF;
767: ELSE
768: IF(p_contract_id IS NOT NULL AND p_contract_id <> FND_API.G_MISS_NUM) THEN
768: IF(p_contract_id IS NOT NULL AND p_contract_id <> FND_API.G_MISS_NUM) THEN
769: OPEN val_contract_id_csr(p_contract_id, p_party_vendor_id, l_buy_or_sell, p_authoring_org_id, l_object_code);
770: FETCH val_contract_id_csr INTO l_exist;
771: IF(val_contract_id_csr%NOTFOUND) THEN
772: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CTRCT');
773: FND_MESSAGE.Set_Token('CONTRACT_ID', p_contract_id);
774: FND_MSG_PUB.ADD;
775: END IF;
776: CLOSE val_contract_id_csr;
769: OPEN val_contract_id_csr(p_contract_id, p_party_vendor_id, l_buy_or_sell, p_authoring_org_id, l_object_code);
770: FETCH val_contract_id_csr INTO l_exist;
771: IF(val_contract_id_csr%NOTFOUND) THEN
772: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CTRCT');
773: FND_MESSAGE.Set_Token('CONTRACT_ID', p_contract_id);
774: FND_MSG_PUB.ADD;
775: END IF;
776: CLOSE val_contract_id_csr;
777: END IF;
797: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
798: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Begin Procedure');
799: END IF;
800: IF(p_osp_order_id IS NULL OR p_osp_order_id = FND_API.G_MISS_NUM) THEN
801: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_HEADER_INV');
802: FND_MSG_PUB.ADD;
803: RETURN;
804: END IF;
805: IF(p_po_header_id IS NOT NULL AND p_po_header_id <> FND_API.G_MISS_NUM) THEN
805: IF(p_po_header_id IS NOT NULL AND p_po_header_id <> FND_API.G_MISS_NUM) THEN
806: OPEN val_po_header_id_csr(p_po_header_id, p_osp_order_id);
807: FETCH val_po_header_id_csr INTO l_exist;
808: IF(val_po_header_id_csr%NOTFOUND) THEN
809: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PO_HEADER_INV');
810: FND_MESSAGE.Set_Token('PO_HEADER_ID', p_po_header_id);
811: FND_MSG_PUB.ADD;
812: END IF;
813: CLOSE val_po_header_id_csr;
806: OPEN val_po_header_id_csr(p_po_header_id, p_osp_order_id);
807: FETCH val_po_header_id_csr INTO l_exist;
808: IF(val_po_header_id_csr%NOTFOUND) THEN
809: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PO_HEADER_INV');
810: FND_MESSAGE.Set_Token('PO_HEADER_ID', p_po_header_id);
811: FND_MSG_PUB.ADD;
812: END IF;
813: CLOSE val_po_header_id_csr;
814: END IF;
866: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Begin Procedure');
867: END IF;
868: l_operating_unit_id := mo_global.get_current_org_id();
869: IF (l_operating_unit_id IS NULL) THEN
870: FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_ORG_NOT_SET');
871: FND_MSG_PUB.ADD;
872: END IF;
873: IF FND_MSG_PUB.count_msg > 0 THEN
874: RAISE FND_API.G_EXC_ERROR;
881: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
882: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '.notCreate', 'operation is not create, it is:'||p_x_osp_order_rec.operation_flag);
883: END IF;
884: IF(p_x_osp_order_rec.osp_order_id IS NULL OR p_x_osp_order_rec.object_version_number IS NULL) THEN
885: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_OBJ_MISS');
886: FND_MSG_PUB.ADD;
887: ELSE
888: -- fetch record and verify the latest record is being dealt with.
889: OPEN osp_order_csr(p_x_osp_order_rec.osp_order_id, p_x_osp_order_rec.object_version_number);
899: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'osp_order_id='||p_x_osp_order_rec.osp_order_id|| 'ovn='||p_x_osp_order_rec.object_version_number);
900: END IF;
901: -- if record not found, raise error and declare that record has been modified.
902: IF (osp_order_csr%NOTFOUND) THEN
903: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INVOP_OSP_NFOUND');
904: FND_MSG_PUB.ADD;
905: END IF;
906: CLOSE osp_order_csr;
907: -- check existing status. If closed, raise error.
905: END IF;
906: CLOSE osp_order_csr;
907: -- check existing status. If closed, raise error.
908: IF(l_osp_order_rec.status_code = G_OSP_CLOSED_STATUS) THEN
909: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
910: FND_MSG_PUB.ADD;
911: ELSE
912: g_old_status_code := l_osp_order_rec.status_code;
913: --g_order_status_for_update := l_osp_order_rec.status_code;
917: -- Added by jaramana on January 8, 2008 for the Requisition ER 6034236
918: -- User should not pass G_OSP_REQ_SUBMITTED_STATUS directly. User need to pass G_OSP_SUBMITTED_STATUS and
919: -- depening on the 'Initialize Purchase Requisition' profile we deduce whether Requisition needs to be created
920: IF(p_x_osp_order_rec.status_code is not null AND l_osp_order_rec.status_code <> G_OSP_REQ_SUBMITTED_STATUS AND p_x_osp_order_rec.status_code = G_OSP_REQ_SUBMITTED_STATUS) THEN
921: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
922: FND_MSG_PUB.ADD;
923: END IF;
924:
925: --If the Profile 'Initialize Purchase Requisition' is set, then set the status to G_OSP_REQ_SUBMITTED_STATUS
947: -- jaramana End
948: g_old_type_code := l_osp_order_rec.order_type_code;
949: g_new_type_code := p_x_osp_order_rec.order_type_code;
950: ELSE
951: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_TYPE');
952: FND_MSG_PUB.ADD;
953: END IF;
954: ELSE
955: p_x_osp_order_rec.order_type_code := l_osp_order_rec.order_type_code;
955: p_x_osp_order_rec.order_type_code := l_osp_order_rec.order_type_code;
956: END IF;
957: -- osp order number can not be changed.
958: IF(p_x_osp_order_rec.osp_order_number IS NOT NULL AND p_x_osp_order_rec.osp_order_number <> l_osp_order_rec.osp_order_number) THEN
959: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_NUM_INV');
960: FND_MESSAGE.Set_Token('ORDER_NUMBER', p_x_osp_order_rec.osp_order_number);
961: FND_MSG_PUB.ADD;
962: END IF;
963: -- order date can not be changed.
956: END IF;
957: -- osp order number can not be changed.
958: IF(p_x_osp_order_rec.osp_order_number IS NOT NULL AND p_x_osp_order_rec.osp_order_number <> l_osp_order_rec.osp_order_number) THEN
959: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_NUM_INV');
960: FND_MESSAGE.Set_Token('ORDER_NUMBER', p_x_osp_order_rec.osp_order_number);
961: FND_MSG_PUB.ADD;
962: END IF;
963: -- order date can not be changed.
964: IF(p_x_osp_order_rec.order_date IS NOT NULL AND TRUNC(p_x_osp_order_rec.order_date) <> TRUNC(l_osp_order_rec.order_date)) THEN
961: FND_MSG_PUB.ADD;
962: END IF;
963: -- order date can not be changed.
964: IF(p_x_osp_order_rec.order_date IS NOT NULL AND TRUNC(p_x_osp_order_rec.order_date) <> TRUNC(l_osp_order_rec.order_date)) THEN
965: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_ORD_DT');
966: FND_MSG_PUB.ADD;
967: END IF;
968: -- operating_unit_id can not change
969: IF(p_x_osp_order_rec.operating_unit_id IS NOT NULL AND p_x_osp_order_rec.operating_unit_id <> l_osp_order_rec.operating_unit_id) THEN
966: FND_MSG_PUB.ADD;
967: END IF;
968: -- operating_unit_id can not change
969: IF(p_x_osp_order_rec.operating_unit_id IS NOT NULL AND p_x_osp_order_rec.operating_unit_id <> l_osp_order_rec.operating_unit_id) THEN
970: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_OPUNIT');
971: FND_MSG_PUB.ADD;
972: END IF;
973:
974:
988: END IF;
989: */
990: -- contract terms should be null.
991: IF(p_x_osp_order_rec.contract_terms IS NOT NULL AND p_x_osp_order_rec.contract_terms <> FND_API.G_MISS_NUM) THEN
992: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_CO');
993: FND_MSG_PUB.ADD;
994: END IF;
995: -- shipping info is not allowed to update in this API
996: IF(p_x_osp_order_rec.oe_header_id IS NOT NULL ) THEN
994: END IF;
995: -- shipping info is not allowed to update in this API
996: IF(p_x_osp_order_rec.oe_header_id IS NOT NULL ) THEN
997: IF(l_osp_order_rec.oe_header_id IS NULL AND p_x_osp_order_rec.oe_header_id <> FND_API.G_MISS_NUM) THEN
998: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_OE'); FND_MSG_PUB.ADD;
999: ELSIF(p_x_osp_order_rec.oe_header_id <> l_osp_order_rec.oe_header_id) THEN
1000: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_OE');
1001: FND_MSG_PUB.ADD;
1002: END IF;
996: IF(p_x_osp_order_rec.oe_header_id IS NOT NULL ) THEN
997: IF(l_osp_order_rec.oe_header_id IS NULL AND p_x_osp_order_rec.oe_header_id <> FND_API.G_MISS_NUM) THEN
998: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_OE'); FND_MSG_PUB.ADD;
999: ELSIF(p_x_osp_order_rec.oe_header_id <> l_osp_order_rec.oe_header_id) THEN
1000: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_OE');
1001: FND_MSG_PUB.ADD;
1002: END IF;
1003: END IF;
1004: END IF;
1012: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1013: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '.create', 'operation is create');
1014: END IF;
1015: IF(p_x_osp_order_rec.osp_order_id IS NOT NULL AND p_x_osp_order_rec.osp_order_id <> FND_API.G_MISS_NUM) THEN
1016: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_NNLL');
1017: FND_MSG_PUB.ADD;
1018: END IF;
1019: IF(p_x_osp_order_rec.object_version_number IS NOT NULL AND p_x_osp_order_rec.object_version_number <> FND_API.G_MISS_NUM) THEN
1020: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_OBJV_NNLL');
1016: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_NNLL');
1017: FND_MSG_PUB.ADD;
1018: END IF;
1019: IF(p_x_osp_order_rec.object_version_number IS NOT NULL AND p_x_osp_order_rec.object_version_number <> FND_API.G_MISS_NUM) THEN
1020: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_OBJV_NNLL');
1021: FND_MSG_PUB.ADD;
1022: END IF;
1023: -- populate order number
1024: IF(p_x_osp_order_rec.osp_order_number IS NOT NULL AND p_x_osp_order_rec.osp_order_number <> FND_API.G_MISS_NUM) THEN
1021: FND_MSG_PUB.ADD;
1022: END IF;
1023: -- populate order number
1024: IF(p_x_osp_order_rec.osp_order_number IS NOT NULL AND p_x_osp_order_rec.osp_order_number <> FND_API.G_MISS_NUM) THEN
1025: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_NUM_INV');
1026: FND_MESSAGE.Set_Token('ORDER_NUMBER', p_x_osp_order_rec.osp_order_number);
1027: FND_MSG_PUB.ADD;
1028: END IF;
1029: -- populate order_date
1022: END IF;
1023: -- populate order number
1024: IF(p_x_osp_order_rec.osp_order_number IS NOT NULL AND p_x_osp_order_rec.osp_order_number <> FND_API.G_MISS_NUM) THEN
1025: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_NUM_INV');
1026: FND_MESSAGE.Set_Token('ORDER_NUMBER', p_x_osp_order_rec.osp_order_number);
1027: FND_MSG_PUB.ADD;
1028: END IF;
1029: -- populate order_date
1030: IF(p_x_osp_order_rec.order_date IS NOT NULL AND p_x_osp_order_rec.order_date <> FND_API.G_MISS_DATE) THEN
1028: END IF;
1029: -- populate order_date
1030: IF(p_x_osp_order_rec.order_date IS NOT NULL AND p_x_osp_order_rec.order_date <> FND_API.G_MISS_DATE) THEN
1031: IF(TRUNC(p_x_osp_order_rec.order_date) <> TRUNC(SYSDATE)) THEN
1032: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_ORD_DAT');
1033: FND_MESSAGE.Set_Token('ORDER_DATE', p_x_osp_order_rec.order_date);
1034: FND_MSG_PUB.ADD;
1035: END IF;
1036: ELSE
1029: -- populate order_date
1030: IF(p_x_osp_order_rec.order_date IS NOT NULL AND p_x_osp_order_rec.order_date <> FND_API.G_MISS_DATE) THEN
1031: IF(TRUNC(p_x_osp_order_rec.order_date) <> TRUNC(SYSDATE)) THEN
1032: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_ORD_DAT');
1033: FND_MESSAGE.Set_Token('ORDER_DATE', p_x_osp_order_rec.order_date);
1034: FND_MSG_PUB.ADD;
1035: END IF;
1036: ELSE
1037: p_x_osp_order_rec.order_date := TRUNC(SYSDATE);
1038: END IF;
1039: -- populate operating_unit_id
1040: IF(p_x_osp_order_rec.operating_unit_id IS NOT NULL AND p_x_osp_order_rec.operating_unit_id <> FND_API.G_MISS_NUM) THEN
1041: IF(p_x_osp_order_rec.operating_unit_id <> l_operating_unit_id) THEN
1042: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_OP_UNIT');
1043: FND_MSG_PUB.ADD;
1044: END IF;
1045: ELSE
1046: p_x_osp_order_rec.operating_unit_id := l_operating_unit_id;
1046: p_x_osp_order_rec.operating_unit_id := l_operating_unit_id;
1047: END IF;
1048: -- po_header_id, oe_header_id, po_batch_id, po_request_id, po_interface_header_id, contract_terms should be null for CREATE.
1049: IF(p_x_osp_order_rec.po_header_id IS NOT NULL AND p_x_osp_order_rec.po_header_id <> FND_API.G_MISS_NUM) THEN
1050: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1051: FND_MSG_PUB.ADD;
1052: END IF;
1053: IF(p_x_osp_order_rec.oe_header_id IS NOT NULL AND p_x_osp_order_rec.oe_header_id <> FND_API.G_MISS_NUM) THEN
1054: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_OE_NNLL');
1050: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1051: FND_MSG_PUB.ADD;
1052: END IF;
1053: IF(p_x_osp_order_rec.oe_header_id IS NOT NULL AND p_x_osp_order_rec.oe_header_id <> FND_API.G_MISS_NUM) THEN
1054: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_OE_NNLL');
1055: FND_MSG_PUB.ADD;
1056: END IF;
1057: IF(p_x_osp_order_rec.po_batch_id IS NOT NULL AND p_x_osp_order_rec.po_batch_id <> FND_API.G_MISS_NUM) THEN
1058: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1054: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_OE_NNLL');
1055: FND_MSG_PUB.ADD;
1056: END IF;
1057: IF(p_x_osp_order_rec.po_batch_id IS NOT NULL AND p_x_osp_order_rec.po_batch_id <> FND_API.G_MISS_NUM) THEN
1058: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1059: FND_MSG_PUB.ADD;
1060: END IF;
1061: IF(p_x_osp_order_rec.po_request_id IS NOT NULL AND p_x_osp_order_rec.po_request_id <> FND_API.G_MISS_NUM) THEN
1062: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1058: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1059: FND_MSG_PUB.ADD;
1060: END IF;
1061: IF(p_x_osp_order_rec.po_request_id IS NOT NULL AND p_x_osp_order_rec.po_request_id <> FND_API.G_MISS_NUM) THEN
1062: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1063: FND_MSG_PUB.ADD;
1064: END IF;
1065: IF(p_x_osp_order_rec.po_interface_header_id IS NOT NULL AND p_x_osp_order_rec.po_interface_header_id <> FND_API.G_MISS_NUM) THEN
1066: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1062: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1063: FND_MSG_PUB.ADD;
1064: END IF;
1065: IF(p_x_osp_order_rec.po_interface_header_id IS NOT NULL AND p_x_osp_order_rec.po_interface_header_id <> FND_API.G_MISS_NUM) THEN
1066: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1067: FND_MSG_PUB.ADD;
1068: END IF;
1069: -- status_code should be null or 'ENTERED'. IF null, default to 'ENTERED'
1070: IF(p_x_osp_order_rec.status_code IS NOT NULL AND p_x_osp_order_rec.status_code <> FND_API.G_MISS_CHAR
1068: END IF;
1069: -- status_code should be null or 'ENTERED'. IF null, default to 'ENTERED'
1070: IF(p_x_osp_order_rec.status_code IS NOT NULL AND p_x_osp_order_rec.status_code <> FND_API.G_MISS_CHAR
1071: AND p_x_osp_order_rec.status_code <> G_OSP_ENTERED_STATUS) THEN
1072: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1073: FND_MSG_PUB.ADD;
1074: ELSE
1075: p_x_osp_order_rec.status_code := G_OSP_ENTERED_STATUS;
1076: g_old_status_code := G_OSP_ENTERED_STATUS;
1092: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '.type', 'Update Order type is Service or Exchange');
1093: END IF;
1094: -- contract_id and customer_id must be null for SERVICE or EXCHANGE
1095: IF(p_x_osp_order_rec.contract_id IS NOT NULL AND p_x_osp_order_rec.contract_id <> FND_API.G_MISS_NUM) THEN
1096: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_CTRCT');
1097: FND_MESSAGE.Set_Token('CONTRACT_ID', p_x_osp_order_rec.contract_id);
1098: FND_MSG_PUB.ADD;
1099: END IF;
1100: IF(p_x_osp_order_rec.customer_id IS NOT NULL AND p_x_osp_order_rec.customer_id <> FND_API.G_MISS_NUM) THEN
1093: END IF;
1094: -- contract_id and customer_id must be null for SERVICE or EXCHANGE
1095: IF(p_x_osp_order_rec.contract_id IS NOT NULL AND p_x_osp_order_rec.contract_id <> FND_API.G_MISS_NUM) THEN
1096: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_CTRCT');
1097: FND_MESSAGE.Set_Token('CONTRACT_ID', p_x_osp_order_rec.contract_id);
1098: FND_MSG_PUB.ADD;
1099: END IF;
1100: IF(p_x_osp_order_rec.customer_id IS NOT NULL AND p_x_osp_order_rec.customer_id <> FND_API.G_MISS_NUM) THEN
1101: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_ID_INV');
1097: FND_MESSAGE.Set_Token('CONTRACT_ID', p_x_osp_order_rec.contract_id);
1098: FND_MSG_PUB.ADD;
1099: END IF;
1100: IF(p_x_osp_order_rec.customer_id IS NOT NULL AND p_x_osp_order_rec.customer_id <> FND_API.G_MISS_NUM) THEN
1101: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_ID_INV');
1102: FND_MESSAGE.Set_Token('CUSTOMER_ID', p_x_osp_order_rec.customer_id);
1103: FND_MSG_PUB.ADD;
1104: END IF;
1105: IF(p_x_osp_order_rec.contract_terms IS NOT NULL AND p_x_osp_order_rec.contract_terms <> FND_API.G_MISS_NUM) THEN
1098: FND_MSG_PUB.ADD;
1099: END IF;
1100: IF(p_x_osp_order_rec.customer_id IS NOT NULL AND p_x_osp_order_rec.customer_id <> FND_API.G_MISS_NUM) THEN
1101: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_ID_INV');
1102: FND_MESSAGE.Set_Token('CUSTOMER_ID', p_x_osp_order_rec.customer_id);
1103: FND_MSG_PUB.ADD;
1104: END IF;
1105: IF(p_x_osp_order_rec.contract_terms IS NOT NULL AND p_x_osp_order_rec.contract_terms <> FND_API.G_MISS_NUM) THEN
1106: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_CO');
1102: FND_MESSAGE.Set_Token('CUSTOMER_ID', p_x_osp_order_rec.customer_id);
1103: FND_MSG_PUB.ADD;
1104: END IF;
1105: IF(p_x_osp_order_rec.contract_terms IS NOT NULL AND p_x_osp_order_rec.contract_terms <> FND_API.G_MISS_NUM) THEN
1106: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_CO');
1107: FND_MSG_PUB.ADD;
1108: END IF;
1109: ELSIF (p_x_osp_order_rec.order_type_code IN( G_OSP_ORDER_TYPE_LOAN,G_OSP_ORDER_TYPE_BORROW) ) THEN
1110: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1111: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '.type', 'Update Order type is Loan or Borrow');
1112: END IF;
1113: -- po_agent_id,vendor_id(FOR LOAN only) and vendor_site_id must be null.
1114: IF(p_x_osp_order_rec.po_agent_id IS NOT NULL AND p_x_osp_order_rec.po_agent_id <> FND_API.G_MISS_NUM) THEN
1115: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_ID_INV');
1116: FND_MESSAGE.Set_Token('BUYER_ID', p_x_osp_order_rec.po_agent_id);
1117: FND_MSG_PUB.ADD;
1118: END IF;
1119: IF(p_x_osp_order_rec.vendor_site_id IS NOT NULL AND p_x_osp_order_rec.vendor_site_id <> FND_API.G_MISS_NUM) THEN
1112: END IF;
1113: -- po_agent_id,vendor_id(FOR LOAN only) and vendor_site_id must be null.
1114: IF(p_x_osp_order_rec.po_agent_id IS NOT NULL AND p_x_osp_order_rec.po_agent_id <> FND_API.G_MISS_NUM) THEN
1115: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_ID_INV');
1116: FND_MESSAGE.Set_Token('BUYER_ID', p_x_osp_order_rec.po_agent_id);
1117: FND_MSG_PUB.ADD;
1118: END IF;
1119: IF(p_x_osp_order_rec.vendor_site_id IS NOT NULL AND p_x_osp_order_rec.vendor_site_id <> FND_API.G_MISS_NUM) THEN
1120: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
1116: FND_MESSAGE.Set_Token('BUYER_ID', p_x_osp_order_rec.po_agent_id);
1117: FND_MSG_PUB.ADD;
1118: END IF;
1119: IF(p_x_osp_order_rec.vendor_site_id IS NOT NULL AND p_x_osp_order_rec.vendor_site_id <> FND_API.G_MISS_NUM) THEN
1120: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
1121: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_x_osp_order_rec.vendor_site_id);
1122: FND_MSG_PUB.ADD;
1123: END IF;
1124: IF(p_x_osp_order_rec.order_type_code = G_OSP_ORDER_TYPE_LOAN) THEN
1117: FND_MSG_PUB.ADD;
1118: END IF;
1119: IF(p_x_osp_order_rec.vendor_site_id IS NOT NULL AND p_x_osp_order_rec.vendor_site_id <> FND_API.G_MISS_NUM) THEN
1120: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
1121: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_x_osp_order_rec.vendor_site_id);
1122: FND_MSG_PUB.ADD;
1123: END IF;
1124: IF(p_x_osp_order_rec.order_type_code = G_OSP_ORDER_TYPE_LOAN) THEN
1125: IF(p_x_osp_order_rec.vendor_id IS NOT NULL AND p_x_osp_order_rec.vendor_id <> FND_API.G_MISS_NUM) THEN
1122: FND_MSG_PUB.ADD;
1123: END IF;
1124: IF(p_x_osp_order_rec.order_type_code = G_OSP_ORDER_TYPE_LOAN) THEN
1125: IF(p_x_osp_order_rec.vendor_id IS NOT NULL AND p_x_osp_order_rec.vendor_id <> FND_API.G_MISS_NUM) THEN
1126: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
1127: FND_MESSAGE.Set_Token('VENDOR_ID', p_x_osp_order_rec.vendor_id);
1128: FND_MSG_PUB.ADD;
1129: END IF;
1130: END IF;
1123: END IF;
1124: IF(p_x_osp_order_rec.order_type_code = G_OSP_ORDER_TYPE_LOAN) THEN
1125: IF(p_x_osp_order_rec.vendor_id IS NOT NULL AND p_x_osp_order_rec.vendor_id <> FND_API.G_MISS_NUM) THEN
1126: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
1127: FND_MESSAGE.Set_Token('VENDOR_ID', p_x_osp_order_rec.vendor_id);
1128: FND_MSG_PUB.ADD;
1129: END IF;
1130: END IF;
1131: -- po_header_id, po_batch_id, po_request_id, po_interface_header_id, contract_terms should be null
1129: END IF;
1130: END IF;
1131: -- po_header_id, po_batch_id, po_request_id, po_interface_header_id, contract_terms should be null
1132: IF(p_x_osp_order_rec.po_header_id IS NOT NULL AND p_x_osp_order_rec.po_header_id <> FND_API.G_MISS_NUM) THEN
1133: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1134: FND_MSG_PUB.ADD;
1135: END IF;
1136: IF(p_x_osp_order_rec.po_batch_id IS NOT NULL AND p_x_osp_order_rec.po_batch_id <> FND_API.G_MISS_NUM) THEN
1137: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1133: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1134: FND_MSG_PUB.ADD;
1135: END IF;
1136: IF(p_x_osp_order_rec.po_batch_id IS NOT NULL AND p_x_osp_order_rec.po_batch_id <> FND_API.G_MISS_NUM) THEN
1137: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1138: FND_MSG_PUB.ADD;
1139: END IF;
1140: IF(p_x_osp_order_rec.po_request_id IS NOT NULL AND p_x_osp_order_rec.po_request_id <> FND_API.G_MISS_NUM) THEN
1141: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1137: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1138: FND_MSG_PUB.ADD;
1139: END IF;
1140: IF(p_x_osp_order_rec.po_request_id IS NOT NULL AND p_x_osp_order_rec.po_request_id <> FND_API.G_MISS_NUM) THEN
1141: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1142: FND_MSG_PUB.ADD;
1143: END IF;
1144: IF(p_x_osp_order_rec.po_interface_header_id IS NOT NULL AND p_x_osp_order_rec.po_interface_header_id <> FND_API.G_MISS_NUM) THEN
1145: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1141: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1142: FND_MSG_PUB.ADD;
1143: END IF;
1144: IF(p_x_osp_order_rec.po_interface_header_id IS NOT NULL AND p_x_osp_order_rec.po_interface_header_id <> FND_API.G_MISS_NUM) THEN
1145: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1146: FND_MSG_PUB.ADD;
1147: END IF;
1148:
1149: -- Added by jaramana on January 8, 2008 for the Requisition ER 6034236
1147: END IF;
1148:
1149: -- Added by jaramana on January 8, 2008 for the Requisition ER 6034236
1150: IF(p_x_osp_order_rec.po_req_header_id IS NOT NULL AND p_x_osp_order_rec.po_req_header_id <> FND_API.G_MISS_NUM) THEN
1151: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_REQ_NNLL');
1152: FND_MSG_PUB.ADD;
1153: END IF;
1154: -- jaramana End
1155:
1153: END IF;
1154: -- jaramana End
1155:
1156: IF(p_x_osp_order_rec.contract_terms IS NOT NULL AND p_x_osp_order_rec.contract_terms <> FND_API.G_MISS_NUM) THEN
1157: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_CO');
1158: FND_MSG_PUB.ADD;
1159: END IF;
1160:
1161: END IF;
1178: --set single_instance_flag
1179: IF(p_x_osp_order_rec.single_instance_flag IS NULL OR p_x_osp_order_rec.single_instance_flag = FND_API.G_MISS_CHAR)THEN
1180: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1181: ELSIF (p_x_osp_order_rec.single_instance_flag NOT IN(G_NO_FLAG,G_YES_FLAG))THEN
1182: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1183: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1184: FND_MSG_PUB.ADD;
1185: END IF;
1186: --dbms_output.put_line('validate vendor');
1179: IF(p_x_osp_order_rec.single_instance_flag IS NULL OR p_x_osp_order_rec.single_instance_flag = FND_API.G_MISS_CHAR)THEN
1180: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1181: ELSIF (p_x_osp_order_rec.single_instance_flag NOT IN(G_NO_FLAG,G_YES_FLAG))THEN
1182: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1183: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1184: FND_MSG_PUB.ADD;
1185: END IF;
1186: --dbms_output.put_line('validate vendor');
1187:
1186: --dbms_output.put_line('validate vendor');
1187:
1188: -- validate vendor_id.
1189: IF(p_x_osp_order_rec.vendor_id IS NULL OR p_x_osp_order_rec.vendor_id = FND_API.G_MISS_NUM) THEN
1190: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_NLL');
1191: FND_MSG_PUB.ADD;
1192: ELSE
1193: validate_vendor(p_x_osp_order_rec.vendor_id);
1194: END IF;
1194: END IF;
1195: --dbms_output.put_line('validate vendor site');
1196: -- validate vendor_site_id.
1197: IF(p_x_osp_order_rec.vendor_site_id IS NULL OR p_x_osp_order_rec.vendor_site_id = FND_API.G_MISS_NUM) THEN
1198: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_NLL');
1199: FND_MSG_PUB.ADD;
1200: ELSE
1201: validate_vendor_site(p_x_osp_order_rec.vendor_id, p_x_osp_order_rec.vendor_site_id);
1202: END IF;
1206: p_x_osp_order_rec.vendor_contact_id);
1207: -- validate po_agent_id.
1208: --dbms_output.put_line('validate buyer');
1209: IF(p_x_osp_order_rec.po_agent_id IS NULL OR p_x_osp_order_rec.po_agent_id = FND_API.G_MISS_NUM) THEN
1210: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_ID_NLL');
1211: FND_MSG_PUB.ADD;
1212: ELSE
1213: validate_buyer(p_x_osp_order_rec.po_agent_id);
1214: END IF;
1226: || 'g_old_status_code' || g_old_status_code);
1227: END IF;
1228: -- po_header_id should be null.
1229: IF(p_x_osp_order_rec.po_header_id IS NOT NULL AND p_x_osp_order_rec.po_header_id <> FND_API.G_MISS_NUM) THEN
1230: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1231: FND_MSG_PUB.ADD;
1232: END IF;
1233: -- Modified by jaramana on January 9, 2008 for the Requisition ER 6034236
1234: -- req_header_id should be null.
1232: END IF;
1233: -- Modified by jaramana on January 9, 2008 for the Requisition ER 6034236
1234: -- req_header_id should be null.
1235: IF(p_x_osp_order_rec.po_req_header_id IS NOT NULL AND p_x_osp_order_rec.po_req_header_id <> FND_API.G_MISS_NUM) THEN
1236: FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_OSP_ORD_REQ_NNLL');
1237: FND_MSG_PUB.ADD;
1238: END IF;
1239: -- jaramana End
1240:
1242: -- status_code should be null,'ENTERED', 'SUBMISSION_FAILED' or 'SUBMITTED'
1243: /*
1244: IF(p_x_osp_order_rec.status_code IS NOT NULL AND
1245: p_x_osp_order_rec.status_code NOT IN(G_OSP_ENTERED_STATUS, G_OSP_SUB_FAILED_STATUS, G_OSP_SUBMITTED_STATUS)) THEN
1246: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1247: FND_MSG_PUB.ADD;
1248: ELSIF (g_old_status_code = G_OSP_ENTERED_STATUS AND p_x_osp_order_rec.status_code = G_OSP_SUB_FAILED_STATUS) THEN
1249: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1250: FND_MSG_PUB.ADD;
1245: p_x_osp_order_rec.status_code NOT IN(G_OSP_ENTERED_STATUS, G_OSP_SUB_FAILED_STATUS, G_OSP_SUBMITTED_STATUS)) THEN
1246: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1247: FND_MSG_PUB.ADD;
1248: ELSIF (g_old_status_code = G_OSP_ENTERED_STATUS AND p_x_osp_order_rec.status_code = G_OSP_SUB_FAILED_STATUS) THEN
1249: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1250: FND_MSG_PUB.ADD;
1251: ELSIF (g_old_status_code = G_OSP_SUB_FAILED_STATUS AND p_x_osp_order_rec.status_code = G_OSP_ENTERED_STATUS) THEN
1252: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1253: FND_MSG_PUB.ADD;
1248: ELSIF (g_old_status_code = G_OSP_ENTERED_STATUS AND p_x_osp_order_rec.status_code = G_OSP_SUB_FAILED_STATUS) THEN
1249: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1250: FND_MSG_PUB.ADD;
1251: ELSIF (g_old_status_code = G_OSP_SUB_FAILED_STATUS AND p_x_osp_order_rec.status_code = G_OSP_ENTERED_STATUS) THEN
1252: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1253: FND_MSG_PUB.ADD;
1254: END IF;
1255: */
1256: /*
1269: In all the above the changes status is either itself or G_OSP_SUBMITTED_STATUS or G_OSP_REQ_SUBMITTED_STATUS
1270: */
1271:
1272: IF(p_x_osp_order_rec.status_code IS NOT NULL AND p_x_osp_order_rec.status_code <> g_old_status_code AND p_x_osp_order_rec.status_code NOT IN (G_OSP_SUBMITTED_STATUS,G_OSP_REQ_SUBMITTED_STATUS )) THEN
1273: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1274: FND_MSG_PUB.ADD;
1275: END IF;
1276:
1277: --
1278: --set single_instance_flag
1279: IF(p_x_osp_order_rec.single_instance_flag = FND_API.G_MISS_CHAR)THEN
1280: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1281: ELSIF (p_x_osp_order_rec.single_instance_flag IS NOT NULL AND p_x_osp_order_rec.single_instance_flag NOT IN(G_NO_FLAG,G_YES_FLAG))THEN
1282: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1283: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1284: FND_MSG_PUB.ADD;
1285: END IF;
1286: /*
1279: IF(p_x_osp_order_rec.single_instance_flag = FND_API.G_MISS_CHAR)THEN
1280: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1281: ELSIF (p_x_osp_order_rec.single_instance_flag IS NOT NULL AND p_x_osp_order_rec.single_instance_flag NOT IN(G_NO_FLAG,G_YES_FLAG))THEN
1282: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1283: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1284: FND_MSG_PUB.ADD;
1285: END IF;
1286: /*
1287: -- validate vendor_id
1285: END IF;
1286: /*
1287: -- validate vendor_id
1288: IF(p_x_osp_order_rec.vendor_id IS NOT NULL AND p_x_osp_order_rec.vendor_id = FND_API.G_MISS_NUM) THEN
1289: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_NLL');
1290: FND_MSG_PUB.ADD;
1291: ELSIF (p_x_osp_order_rec.vendor_id IS NOT NULL) THEN
1292: validate_vendor(p_x_osp_order_rec.vendor_id);
1293: END IF;
1292: validate_vendor(p_x_osp_order_rec.vendor_id);
1293: END IF;
1294: -- validate vendor_site_id.
1295: IF(p_x_osp_order_rec.vendor_site_id IS NOT NULL AND p_x_osp_order_rec.vendor_site_id = FND_API.G_MISS_NUM) THEN
1296: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_NLL');
1297: FND_MSG_PUB.ADD;
1298: ELSE
1299: p_x_osp_order_rec.vendor_site_id := NVL(p_x_osp_order_rec.vendor_site_id,l_osp_order_rec.vendor_site_id);
1300: p_x_osp_order_rec.vendor_id := NVL(p_x_osp_order_rec.vendor_id, l_osp_order_rec.vendor_id);
1303: */
1304: --When updating OSP order header, vendor_id is required
1305: --G_MISS/Null conversion has already been made in default_unchanged_order_header
1306: IF(p_x_osp_order_rec.vendor_id IS NULL) THEN
1307: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_NLL');
1308: FND_MSG_PUB.ADD;
1309: END IF;
1310:
1311: --When updating OSP order header, vendor_site_id is required
1310:
1311: --When updating OSP order header, vendor_site_id is required
1312: --G_MISS/Null conversion has already been made in default_unchanged_order_header
1313: IF(p_x_osp_order_rec.vendor_site_id IS NULL) THEN
1314: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_NLL');
1315: FND_MSG_PUB.ADD;
1316: END IF;
1317:
1318: validate_vendor_site_contact(p_x_osp_order_rec.vendor_id,
1322: --dbms_output.put_line('validate buyer in update');
1323: --IF(p_x_osp_order_rec.po_agent_id IS NOT NULL AND p_x_osp_order_rec.po_agent_id = FND_API.G_MISS_NUM) THEN
1324: --G_MISS/Null conversion has already been made in default_unchanged_order_header
1325: IF(p_x_osp_order_rec.po_agent_id IS NULL) THEN
1326: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_ID_NLL');
1327: FND_MSG_PUB.ADD;
1328: ELSIF (p_x_osp_order_rec.po_agent_id IS NOT NULL) THEN
1329: validate_buyer(p_x_osp_order_rec.po_agent_id);
1330: END IF;
1339: p_x_osp_order_rec.status_code IN (G_OSP_SUBMITTED_STATUS,G_OSP_REQ_SUBMITTED_STATUS)) THEN
1340: OPEN chk_requisition_exists_csr(p_x_osp_order_rec.osp_order_id);
1341: FETCH chk_requisition_exists_csr INTO l_req_num;
1342: IF (chk_requisition_exists_csr%FOUND) THEN
1343: FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_OSP_REQ_NOT_DELETED');
1344: FND_MESSAGE.Set_Token('REQ_NUM', l_req_num);
1345: FND_MSG_PUB.ADD;
1346: END IF;
1347: CLOSE chk_requisition_exists_csr;
1340: OPEN chk_requisition_exists_csr(p_x_osp_order_rec.osp_order_id);
1341: FETCH chk_requisition_exists_csr INTO l_req_num;
1342: IF (chk_requisition_exists_csr%FOUND) THEN
1343: FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_OSP_REQ_NOT_DELETED');
1344: FND_MESSAGE.Set_Token('REQ_NUM', l_req_num);
1345: FND_MSG_PUB.ADD;
1346: END IF;
1347: CLOSE chk_requisition_exists_csr;
1348: END IF;
1355: || 'g_old_status_code: ' || g_old_status_code || 'new status: ' || p_x_osp_order_rec.status_code );
1356: END IF;
1357: -- po_header_id should be null.
1358: IF(p_x_osp_order_rec.po_header_id IS NOT NULL AND p_x_osp_order_rec.po_header_id <> FND_API.G_MISS_NUM) THEN
1359: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1360: FND_MSG_PUB.ADD;
1361: END IF;
1362:
1363: -- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
1362:
1363: -- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
1364: -- req_header_id should be null.
1365: IF(p_x_osp_order_rec.po_req_header_id IS NOT NULL AND p_x_osp_order_rec.po_req_header_id <> FND_API.G_MISS_NUM) THEN
1366: FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_OSP_ORD_REQ_NNLL');
1367: FND_MSG_PUB.ADD;
1368: END IF;
1369: -- jaramana End
1370:
1369: -- jaramana End
1370:
1371: -- single_instance_flag cant change
1372: IF(p_x_osp_order_rec.single_instance_flag IS NOT NULL AND p_x_osp_order_rec.single_instance_flag <> l_osp_order_rec.single_instance_flag) THEN
1373: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_INST_FLG_CHG');
1374: FND_MSG_PUB.ADD;
1375: END IF;
1376: -- vendor_id cant change
1377: IF(p_x_osp_order_rec.vendor_id IS NOT NULL AND p_x_osp_order_rec.vendor_id <> l_osp_order_rec.vendor_id) THEN
1374: FND_MSG_PUB.ADD;
1375: END IF;
1376: -- vendor_id cant change
1377: IF(p_x_osp_order_rec.vendor_id IS NOT NULL AND p_x_osp_order_rec.vendor_id <> l_osp_order_rec.vendor_id) THEN
1378: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CHG');
1379: FND_MSG_PUB.ADD;
1380: END IF;
1381: -- vendor_site_id cant change
1382: IF(p_x_osp_order_rec.vendor_site_id IS NOT NULL AND p_x_osp_order_rec.vendor_site_id <> l_osp_order_rec.vendor_site_id) THEN
1379: FND_MSG_PUB.ADD;
1380: END IF;
1381: -- vendor_site_id cant change
1382: IF(p_x_osp_order_rec.vendor_site_id IS NOT NULL AND p_x_osp_order_rec.vendor_site_id <> l_osp_order_rec.vendor_site_id) THEN
1383: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_STE_CHG');
1384: FND_MSG_PUB.ADD;
1385: END IF;
1386:
1387: -- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
1387: -- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
1388: -- This should have been present with ISO changes. Adding now.
1389: -- vendor_contact_id cant change
1390: IF(p_x_osp_order_rec.vendor_contact_id IS NOT NULL AND p_x_osp_order_rec.vendor_contact_id <> l_osp_order_rec.vendor_contact_id) THEN
1391: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CTCT_CHG ');
1392: FND_MSG_PUB.ADD;
1393: END IF;
1394: -- jaramana End
1395:
1394: -- jaramana End
1395:
1396: -- Buyer cant change
1397: IF(p_x_osp_order_rec.po_agent_id IS NOT NULL AND p_x_osp_order_rec.po_agent_id <> l_osp_order_rec.po_agent_id) THEN
1398: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_BUYER_CHG');
1399: FND_MSG_PUB.ADD;
1400: END IF;
1401: -- jaramana modified on January 9, 2008 for the Requisition ER 6034236
1402: --The transitions from G_OSP_SUBMITTED_STATUS -> G_OSP_SUB_FAILED_STATUS and
1406: /*
1407: ELSIF (p_x_osp_order_rec.status_code = G_OSP_SUB_FAILED_STATUS) THEN
1408: -- po_header_id should be null.
1409: IF(p_x_osp_order_rec.po_header_id IS NOT NULL AND p_x_osp_order_rec.po_header_id <> FND_API.G_MISS_NUM) THEN
1410: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1411: FND_MSG_PUB.ADD;
1412: END IF;
1413: --set single_instance_flag
1414: IF(p_x_osp_order_rec.single_instance_flag = FND_API.G_MISS_CHAR)THEN
1413: --set single_instance_flag
1414: IF(p_x_osp_order_rec.single_instance_flag = FND_API.G_MISS_CHAR)THEN
1415: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1416: ELSIF (p_x_osp_order_rec.single_instance_flag IS NOT NULL AND p_x_osp_order_rec.single_instance_flag NOT IN(G_NO_FLAG,G_YES_FLAG))THEN
1417: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1418: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1419: FND_MSG_PUB.ADD;
1420: END IF;
1421: */
1414: IF(p_x_osp_order_rec.single_instance_flag = FND_API.G_MISS_CHAR)THEN
1415: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1416: ELSIF (p_x_osp_order_rec.single_instance_flag IS NOT NULL AND p_x_osp_order_rec.single_instance_flag NOT IN(G_NO_FLAG,G_YES_FLAG))THEN
1417: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1418: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1419: FND_MSG_PUB.ADD;
1420: END IF;
1421: */
1422: /*
1421: */
1422: /*
1423: -- validate vendor_id.
1424: IF(p_x_osp_order_rec.vendor_id IS NOT NULL AND p_x_osp_order_rec.vendor_id = FND_API.G_MISS_NUM) THEN
1425: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_NLL');
1426: FND_MSG_PUB.ADD;
1427: ELSIF (p_x_osp_order_rec.vendor_id IS NOT NULL) THEN
1428: validate_vendor(p_x_osp_order_rec.vendor_id);
1429: END IF;
1428: validate_vendor(p_x_osp_order_rec.vendor_id);
1429: END IF;
1430: -- validate vendor_site_id.
1431: IF(p_x_osp_order_rec.vendor_site_id IS NOT NULL AND p_x_osp_order_rec.vendor_site_id = FND_API.G_MISS_NUM) THEN
1432: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_NLL');
1433: FND_MSG_PUB.ADD;
1434: ELSE
1435: p_x_osp_order_rec.vendor_site_id := NVL(p_x_osp_order_rec.vendor_site_id,l_osp_order_rec.vendor_site_id);
1436: p_x_osp_order_rec.vendor_id := NVL(p_x_osp_order_rec.vendor_id, l_osp_order_rec.vendor_id);
1443: p_x_osp_order_rec.vendor_contact_id);
1444: -- validate po_agent_id.
1445: --dbms_output.put_line('validate buyer in update');
1446: IF(p_x_osp_order_rec.po_agent_id IS NOT NULL AND p_x_osp_order_rec.po_agent_id = FND_API.G_MISS_NUM) THEN
1447: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_ID_NLL');
1448: FND_MSG_PUB.ADD;
1449: ELSIF (p_x_osp_order_rec.po_agent_id IS NOT NULL) THEN
1450: validate_buyer(p_x_osp_order_rec.po_agent_id);
1451: END IF;
1457: || 'g_old_status_code: ' || g_old_status_code || 'new status: ' || p_x_osp_order_rec.status_code );
1458: END IF;
1459: -- po_header_id should be not be null.
1460: IF(p_x_osp_order_rec.po_header_id IS NULL OR p_x_osp_order_rec.po_header_id = FND_API.G_MISS_NUM) THEN
1461: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_PO_NLL');
1462: FND_MSG_PUB.ADD;
1463: ELSE
1464: validate_po_header(p_x_osp_order_rec.osp_order_id,p_x_osp_order_rec.po_header_id);
1465: END IF;
1464: validate_po_header(p_x_osp_order_rec.osp_order_id,p_x_osp_order_rec.po_header_id);
1465: END IF;
1466: -- single_instance_flag cant change
1467: IF(p_x_osp_order_rec.single_instance_flag IS NOT NULL AND p_x_osp_order_rec.single_instance_flag <> l_osp_order_rec.single_instance_flag) THEN
1468: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_INST_FLG_CHG');
1469: FND_MSG_PUB.ADD;
1470: END IF;
1471: --PO Synch enhancement vendor id and vendor sitecode can change if the change come from PO
1472: -- vendor_id cannot change unless it was change in PO
1471: --PO Synch enhancement vendor id and vendor sitecode can change if the change come from PO
1472: -- vendor_id cannot change unless it was change in PO
1473: IF(p_x_osp_order_rec.vendor_id IS NOT NULL AND p_x_osp_order_rec.vendor_id <> l_osp_order_rec.vendor_id) THEN
1474: IF( vendor_id_exist_in_PO(l_osp_order_rec.po_header_id, p_x_osp_order_rec.vendor_id) = false) THEN
1475: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CHG');
1476: FND_MSG_PUB.ADD;
1477: END IF;
1478: END IF;
1479: -- vendor_site_id cannot change unless it was changed in PO
1478: END IF;
1479: -- vendor_site_id cannot change unless it was changed in PO
1480: IF(p_x_osp_order_rec.vendor_site_id IS NOT NULL AND p_x_osp_order_rec.vendor_site_id <> l_osp_order_rec.vendor_site_id) THEN
1481: IF( vendor_site_id_exist_in_PO(l_osp_order_rec.po_header_id, p_x_osp_order_rec.vendor_site_id) = false) THEN
1482: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_STE_CHG');
1483: FND_MSG_PUB.ADD;
1484: END IF;
1485: END IF;
1486: -- Buyer cant change
1484: END IF;
1485: END IF;
1486: -- Buyer cant change
1487: IF(p_x_osp_order_rec.po_agent_id IS NOT NULL AND p_x_osp_order_rec.po_agent_id <> l_osp_order_rec.po_agent_id) THEN
1488: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_BUYER_CHG');
1489: FND_MSG_PUB.ADD;
1490: END IF;
1491: -- set status_code for lines
1492: g_order_status_for_update := G_OSP_PO_CREATED_STATUS;
1492: g_order_status_for_update := G_OSP_PO_CREATED_STATUS;
1493: */
1494: -- jaramana End
1495: ELSE
1496: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1497: FND_MSG_PUB.ADD;
1498: END IF;
1499:
1500: ELSIF (g_old_status_code = G_OSP_PO_CREATED_STATUS) THEN
1505: -- status_code should be null, 'PO_CREATED' or 'CLOSED'
1506: IF(p_x_osp_order_rec.status_code IS NULL OR p_x_osp_order_rec.status_code IN( G_OSP_PO_CREATED_STATUS,G_OSP_CLOSED_STATUS)) THEN
1507: -- po_header_id cant change
1508: IF(p_x_osp_order_rec.po_header_id IS NOT NULL AND p_x_osp_order_rec.po_header_id <> l_osp_order_rec.po_header_id) THEN
1509: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_PO_CHG');
1510: FND_MSG_PUB.ADD;
1511: END IF;
1512:
1513: -- jaramana modified on January 9, 2008 for the Requisition ER 6034236
1512:
1513: -- jaramana modified on January 9, 2008 for the Requisition ER 6034236
1514: -- req_header_id should be null.
1515: IF(p_x_osp_order_rec.po_req_header_id IS NOT NULL AND p_x_osp_order_rec.po_req_header_id <> FND_API.G_MISS_NUM) THEN
1516: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_REQ_NNLL');
1517: FND_MSG_PUB.ADD;
1518: END IF;
1519: -- jaramana End
1520:
1519: -- jaramana End
1520:
1521: -- single_instance_flag cant change
1522: IF(p_x_osp_order_rec.single_instance_flag IS NOT NULL AND p_x_osp_order_rec.single_instance_flag <> l_osp_order_rec.single_instance_flag) THEN
1523: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_INST_FLG_CHG');
1524: FND_MSG_PUB.ADD;
1525: END IF;
1526: /*
1527: --PO Synch enhancement vendor id and vendor sitecode can change if the change come from PO
1527: --PO Synch enhancement vendor id and vendor sitecode can change if the change come from PO
1528: -- vendor_id cant change
1529: IF(p_x_osp_order_rec.vendor_id IS NOT NULL AND p_x_osp_order_rec.vendor_id <> l_osp_order_rec.vendor_id) THEN
1530: IF( vendor_id_exist_in_PO(l_osp_order_rec.po_header_id, p_x_osp_order_rec.vendor_id) = false) THEN
1531: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CHG');
1532: FND_MSG_PUB.ADD;
1533: END IF;
1534: END IF;
1535: -- vendor_site_id cant change
1534: END IF;
1535: -- vendor_site_id cant change
1536: IF(p_x_osp_order_rec.vendor_site_id IS NOT NULL AND p_x_osp_order_rec.vendor_site_id <> l_osp_order_rec.vendor_site_id) THEN
1537: IF( vendor_site_id_exist_in_PO(l_osp_order_rec.po_header_id, p_x_osp_order_rec.vendor_site_id) = false) THEN
1538: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_STE_CHG');
1539: FND_MSG_PUB.ADD;
1540: END IF;
1541: END IF;
1542: */
1544: -- We are not planning to support this PO attribute continuous synch anymore.
1545:
1546: -- vendor_id cant change
1547: IF(p_x_osp_order_rec.vendor_id IS NOT NULL AND p_x_osp_order_rec.vendor_id <> l_osp_order_rec.vendor_id) THEN
1548: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CHG');
1549: FND_MSG_PUB.ADD;
1550: END IF;
1551: -- vendor_site_id cant change
1552: IF(p_x_osp_order_rec.vendor_site_id IS NOT NULL AND p_x_osp_order_rec.vendor_site_id <> l_osp_order_rec.vendor_site_id) THEN
1549: FND_MSG_PUB.ADD;
1550: END IF;
1551: -- vendor_site_id cant change
1552: IF(p_x_osp_order_rec.vendor_site_id IS NOT NULL AND p_x_osp_order_rec.vendor_site_id <> l_osp_order_rec.vendor_site_id) THEN
1553: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_STE_CHG');
1554: FND_MSG_PUB.ADD;
1555: END IF;
1556: -- vendor_contact_id cant change
1557: IF(p_x_osp_order_rec.vendor_contact_id IS NOT NULL AND p_x_osp_order_rec.vendor_contact_id <> l_osp_order_rec.vendor_contact_id) THEN
1554: FND_MSG_PUB.ADD;
1555: END IF;
1556: -- vendor_contact_id cant change
1557: IF(p_x_osp_order_rec.vendor_contact_id IS NOT NULL AND p_x_osp_order_rec.vendor_contact_id <> l_osp_order_rec.vendor_contact_id) THEN
1558: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CTCT_CHG ');
1559: FND_MSG_PUB.ADD;
1560: END IF;
1561: -- jaramana End
1562:
1561: -- jaramana End
1562:
1563: -- Buyer cant change
1564: IF(p_x_osp_order_rec.po_agent_id IS NOT NULL AND p_x_osp_order_rec.po_agent_id <> l_osp_order_rec.po_agent_id) THEN
1565: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_BUYER_CHG');
1566: FND_MSG_PUB.ADD;
1567: END IF;
1568: -- set status_code for lines
1569: --g_order_status_for_update := G_OSP_PO_CREATED_STATUS;
1567: END IF;
1568: -- set status_code for lines
1569: --g_order_status_for_update := G_OSP_PO_CREATED_STATUS;
1570: ELSE
1571: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1572: FND_MSG_PUB.ADD;
1573: END IF;
1574: -- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
1575: ELSIF (g_old_status_code = G_OSP_REQ_SUBMITTED_STATUS) THEN
1579: 'g_old_status_code: ' || g_old_status_code || 'new status: ' || p_x_osp_order_rec.status_code );
1580: END IF;
1581: -- po_header_id should be null.
1582: IF(p_x_osp_order_rec.po_header_id IS NOT NULL AND p_x_osp_order_rec.po_header_id <> FND_API.G_MISS_NUM) THEN
1583: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1584: FND_MSG_PUB.ADD;
1585: END IF;
1586:
1587: -- req_header_id should be null.
1585: END IF;
1586:
1587: -- req_header_id should be null.
1588: IF(p_x_osp_order_rec.po_req_header_id IS NOT NULL AND p_x_osp_order_rec.po_req_header_id <> FND_API.G_MISS_NUM) THEN
1589: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_REQ_NNLL');
1590: FND_MSG_PUB.ADD;
1591: END IF;
1592:
1593: -- single_instance_flag cant change
1591: END IF;
1592:
1593: -- single_instance_flag cant change
1594: IF(p_x_osp_order_rec.single_instance_flag IS NOT NULL AND p_x_osp_order_rec.single_instance_flag <> l_osp_order_rec.single_instance_flag) THEN
1595: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_INST_FLG_CHG');
1596: FND_MSG_PUB.ADD;
1597: END IF;
1598: -- vendor_id cant change
1599: IF(p_x_osp_order_rec.vendor_id IS NOT NULL AND p_x_osp_order_rec.vendor_id <> l_osp_order_rec.vendor_id) THEN
1596: FND_MSG_PUB.ADD;
1597: END IF;
1598: -- vendor_id cant change
1599: IF(p_x_osp_order_rec.vendor_id IS NOT NULL AND p_x_osp_order_rec.vendor_id <> l_osp_order_rec.vendor_id) THEN
1600: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CHG');
1601: FND_MSG_PUB.ADD;
1602: END IF;
1603: -- vendor_site_id cant change
1604: IF(p_x_osp_order_rec.vendor_site_id IS NOT NULL AND p_x_osp_order_rec.vendor_site_id <> l_osp_order_rec.vendor_site_id) THEN
1601: FND_MSG_PUB.ADD;
1602: END IF;
1603: -- vendor_site_id cant change
1604: IF(p_x_osp_order_rec.vendor_site_id IS NOT NULL AND p_x_osp_order_rec.vendor_site_id <> l_osp_order_rec.vendor_site_id) THEN
1605: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_STE_CHG');
1606: FND_MSG_PUB.ADD;
1607: END IF;
1608: -- vendor_contact_id cant change
1609: IF(p_x_osp_order_rec.vendor_contact_id IS NOT NULL AND p_x_osp_order_rec.vendor_contact_id <> l_osp_order_rec.vendor_contact_id) THEN
1606: FND_MSG_PUB.ADD;
1607: END IF;
1608: -- vendor_contact_id cant change
1609: IF(p_x_osp_order_rec.vendor_contact_id IS NOT NULL AND p_x_osp_order_rec.vendor_contact_id <> l_osp_order_rec.vendor_contact_id) THEN
1610: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CTCT_CHG ');
1611: FND_MSG_PUB.ADD;
1612: END IF;
1613: -- Buyer cant change
1614: IF(p_x_osp_order_rec.po_agent_id IS NOT NULL AND p_x_osp_order_rec.po_agent_id <> l_osp_order_rec.po_agent_id) THEN
1611: FND_MSG_PUB.ADD;
1612: END IF;
1613: -- Buyer cant change
1614: IF(p_x_osp_order_rec.po_agent_id IS NOT NULL AND p_x_osp_order_rec.po_agent_id <> l_osp_order_rec.po_agent_id) THEN
1615: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_BUYER_CHG');
1616: FND_MSG_PUB.ADD;
1617: END IF;
1618: ELSE
1619: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1615: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_BUYER_CHG');
1616: FND_MSG_PUB.ADD;
1617: END IF;
1618: ELSE
1619: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1620: FND_MSG_PUB.ADD;
1621: END IF;
1622:
1623: ELSIF (g_old_status_code = G_OSP_REQ_CREATED_STATUS) THEN
1628: -- status_code should be null, 'REQ_CREATED' or 'CLOSED'
1629: IF(p_x_osp_order_rec.status_code IS NULL OR p_x_osp_order_rec.status_code IN( G_OSP_REQ_CREATED_STATUS,G_OSP_CLOSED_STATUS)) THEN
1630: -- req_header_id cant change
1631: IF(p_x_osp_order_rec.po_req_header_id IS NOT NULL AND p_x_osp_order_rec.po_req_header_id <> l_osp_order_rec.po_req_header_id) THEN
1632: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_REQ_CHG');
1633: FND_MSG_PUB.ADD;
1634: END IF;
1635:
1636: --po_header_id should be null
1634: END IF;
1635:
1636: --po_header_id should be null
1637: IF(p_x_osp_order_rec.po_header_id IS NOT NULL AND p_x_osp_order_rec.po_header_id <> FND_API.G_MISS_NUM) THEN
1638: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1639: FND_MSG_PUB.ADD;
1640: END IF;
1641:
1642: -- single_instance_flag cant change
1640: END IF;
1641:
1642: -- single_instance_flag cant change
1643: IF(p_x_osp_order_rec.single_instance_flag IS NOT NULL AND p_x_osp_order_rec.single_instance_flag <> l_osp_order_rec.single_instance_flag) THEN
1644: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_INST_FLG_CHG');
1645: FND_MSG_PUB.ADD;
1646: END IF;
1647:
1648: -- vendor_id cant change
1646: END IF;
1647:
1648: -- vendor_id cant change
1649: IF(p_x_osp_order_rec.vendor_id IS NOT NULL AND p_x_osp_order_rec.vendor_id <> l_osp_order_rec.vendor_id) THEN
1650: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CHG');
1651: FND_MSG_PUB.ADD;
1652: END IF;
1653: -- vendor_site_id cant change
1654: IF(p_x_osp_order_rec.vendor_site_id IS NOT NULL AND p_x_osp_order_rec.vendor_site_id <> l_osp_order_rec.vendor_site_id) THEN
1651: FND_MSG_PUB.ADD;
1652: END IF;
1653: -- vendor_site_id cant change
1654: IF(p_x_osp_order_rec.vendor_site_id IS NOT NULL AND p_x_osp_order_rec.vendor_site_id <> l_osp_order_rec.vendor_site_id) THEN
1655: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_STE_CHG');
1656: FND_MSG_PUB.ADD;
1657: END IF;
1658:
1659: -- vendor_contact_id cant change
1657: END IF;
1658:
1659: -- vendor_contact_id cant change
1660: IF(p_x_osp_order_rec.vendor_contact_id IS NOT NULL AND p_x_osp_order_rec.vendor_contact_id <> l_osp_order_rec.vendor_contact_id) THEN
1661: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CTCT_CHG ');
1662: FND_MSG_PUB.ADD;
1663: END IF;
1664:
1665: -- Buyer cant change
1663: END IF;
1664:
1665: -- Buyer cant change
1666: IF(p_x_osp_order_rec.po_agent_id IS NOT NULL AND p_x_osp_order_rec.po_agent_id <> l_osp_order_rec.po_agent_id) THEN
1667: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_BUYER_CHG');
1668: FND_MSG_PUB.ADD;
1669: END IF;
1670: -- set status_code for lines
1671: --g_order_status_for_update := G_OSP_PO_CREATED_STATUS;
1669: END IF;
1670: -- set status_code for lines
1671: --g_order_status_for_update := G_OSP_PO_CREATED_STATUS;
1672: ELSE
1673: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1674: FND_MSG_PUB.ADD;
1675: END IF;
1676: --mpothuku End
1677: END IF; --IF(p_x_osp_order_rec.operation_flag = G_OP_UPDATE) THEN
1685: IF(p_x_osp_order_rec.single_instance_flag IS NULL OR p_x_osp_order_rec.single_instance_flag = FND_API.G_MISS_CHAR
1686: OR p_x_osp_order_rec.single_instance_flag = G_NO_FLAG )THEN
1687: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1688: ELSE
1689: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1690: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1691: FND_MSG_PUB.ADD;
1692: END IF;
1693: IF(p_x_osp_order_rec.operation_flag = G_OP_CREATE) THEN
1686: OR p_x_osp_order_rec.single_instance_flag = G_NO_FLAG )THEN
1687: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1688: ELSE
1689: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1690: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1691: FND_MSG_PUB.ADD;
1692: END IF;
1693: IF(p_x_osp_order_rec.operation_flag = G_OP_CREATE) THEN
1694: -- validate customer_id.
1692: END IF;
1693: IF(p_x_osp_order_rec.operation_flag = G_OP_CREATE) THEN
1694: -- validate customer_id.
1695: IF(p_x_osp_order_rec.customer_id IS NULL OR p_x_osp_order_rec.customer_id = FND_API.G_MISS_NUM) THEN
1696: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUSTOMER_ID_NLL');
1697: FND_MSG_PUB.ADD;
1698: ELSE
1699: validate_customer(p_x_osp_order_rec.customer_id);
1700: END IF;
1704: */
1705: -- Changes begin
1706: /*
1707: IF (p_x_osp_order_rec.contract_id IS NULL OR p_x_osp_order_rec.contract_id = FND_API.G_MISS_NUM) THEN
1708: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CONTRACT_ID_NLL');
1709: FND_MSG_PUB.ADD;
1710: ELSE
1711: validate_contract(G_OSP_ORDER_TYPE_LOAN, p_x_osp_order_rec.contract_id , p_x_osp_order_rec.customer_id , l_operating_unit_id);
1712: */
1719: -- Changed by jaramana on January 9, 2008 for the Requisition ER 6034236
1720: -- status_code should be null,'ENTERED', or 'SUBMITTED' or 'REQ_SUBMITTED'
1721: IF(p_x_osp_order_rec.status_code IS NOT NULL AND
1722: p_x_osp_order_rec.status_code NOT IN(G_OSP_ENTERED_STATUS, G_OSP_SUBMITTED_STATUS, G_OSP_REQ_SUBMITTED_STATUS)) THEN
1723: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1724: FND_MSG_PUB.ADD;
1725: END IF;
1726: -- jaramana End
1727: -- validate customer_id.
1725: END IF;
1726: -- jaramana End
1727: -- validate customer_id.
1728: IF(p_x_osp_order_rec.customer_id IS NOT NULL AND p_x_osp_order_rec.customer_id = FND_API.G_MISS_NUM) THEN
1729: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUSTOMER_ID_NLL');
1730: FND_MSG_PUB.ADD;
1731: ELSIF(p_x_osp_order_rec.customer_id IS NOT NULL) THEN
1732: validate_customer(p_x_osp_order_rec.customer_id);
1733: END IF;
1737: */
1738: -- Changes begin
1739: /*
1740: IF(p_x_osp_order_rec.contract_id IS NOT NULL AND p_x_osp_order_rec.contract_id = FND_API.G_MISS_NUM) THEN
1741: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CONTRACT_ID_NLL');
1742: FND_MSG_PUB.ADD;
1743: ELSIF ( p_x_osp_order_rec.contract_id IS NOT NULL) THEN
1744: validate_contract(G_OSP_ORDER_TYPE_LOAN, p_x_osp_order_rec.contract_id , p_x_osp_order_rec.customer_id , l_operating_unit_id);
1745: */
1752: ELSIF(g_old_status_code = G_OSP_SUBMITTED_STATUS) THEN
1753: -- status_code should be null,'ENTERED', or 'SUBMITTED'
1754: IF(p_x_osp_order_rec.status_code IS NOT NULL AND
1755: p_x_osp_order_rec.status_code NOT IN(G_OSP_SUBMITTED_STATUS, G_OSP_CLOSED_STATUS)) THEN
1756: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1757: FND_MSG_PUB.ADD;
1758: END IF;
1759: -- CUSTOMER_ID cant change
1760: IF(p_x_osp_order_rec.customer_id IS NOT NULL AND p_x_osp_order_rec.customer_id <> l_osp_order_rec.customer_id) THEN
1757: FND_MSG_PUB.ADD;
1758: END IF;
1759: -- CUSTOMER_ID cant change
1760: IF(p_x_osp_order_rec.customer_id IS NOT NULL AND p_x_osp_order_rec.customer_id <> l_osp_order_rec.customer_id) THEN
1761: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CUST_CHG');
1762: FND_MSG_PUB.ADD;
1763: END IF;
1764: -- contract_id cant change
1765: IF(p_x_osp_order_rec.contract_id IS NOT NULL AND p_x_osp_order_rec.contract_id <> l_osp_order_rec.contract_id) THEN
1762: FND_MSG_PUB.ADD;
1763: END IF;
1764: -- contract_id cant change
1765: IF(p_x_osp_order_rec.contract_id IS NOT NULL AND p_x_osp_order_rec.contract_id <> l_osp_order_rec.contract_id) THEN
1766: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CTRCT_CHG');
1767: FND_MSG_PUB.ADD;
1768: END IF;
1769: --g_order_status_for_update := G_OSP_SUBMITTED_STATUS;
1770: ELSE
1767: FND_MSG_PUB.ADD;
1768: END IF;
1769: --g_order_status_for_update := G_OSP_SUBMITTED_STATUS;
1770: ELSE
1771: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1772: FND_MSG_PUB.ADD;
1773: END IF;
1774: END IF;
1775: -- validate BORROW order header
1778: IF(p_x_osp_order_rec.single_instance_flag IS NULL OR p_x_osp_order_rec.single_instance_flag = FND_API.G_MISS_CHAR
1779: OR p_x_osp_order_rec.single_instance_flag = G_NO_FLAG )THEN
1780: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1781: ELSE
1782: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1783: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1784: FND_MSG_PUB.ADD;
1785: END IF;
1786: IF(p_x_osp_order_rec.operation_flag = G_OP_CREATE) THEN
1779: OR p_x_osp_order_rec.single_instance_flag = G_NO_FLAG )THEN
1780: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1781: ELSE
1782: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1783: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1784: FND_MSG_PUB.ADD;
1785: END IF;
1786: IF(p_x_osp_order_rec.operation_flag = G_OP_CREATE) THEN
1787: -- validate customer_id.
1790: END IF;
1791: /*
1792: -- validate vendor_id.
1793: IF(p_x_osp_order_rec.vendor_id IS NULL OR p_x_osp_order_rec.vendor_id = FND_API.G_MISS_NUM) THEN
1794: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_NLL');
1795: FND_MSG_PUB.ADD;
1796: ELSE
1797: validate_vendor(p_x_osp_order_rec.vendor_id);
1798: END IF;
1806: */
1807: -- Changes begin
1808: /*
1809: IF (p_x_osp_order_rec.contract_id IS NULL OR p_x_osp_order_rec.contract_id = FND_API.G_MISS_NUM) THEN
1810: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CONTRACT_ID_NLL');
1811: FND_MSG_PUB.ADD;
1812: ELSE
1813: validate_contract(G_OSP_ORDER_TYPE_BORROW, p_x_osp_order_rec.contract_id , p_x_osp_order_rec.vendor_id , l_operating_unit_id);
1814: END IF;
1821: IF(g_old_status_code = G_OSP_ENTERED_STATUS) THEN
1822: -- status_code should be null,'ENTERED', or 'SUBMITTED'
1823: IF(p_x_osp_order_rec.status_code IS NOT NULL AND
1824: p_x_osp_order_rec.status_code NOT IN(G_OSP_ENTERED_STATUS, G_OSP_SUBMITTED_STATUS)) THEN
1825: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1826: FND_MSG_PUB.ADD;
1827: END IF;
1828: -- validate customer_id.
1829: IF(p_x_osp_order_rec.customer_id IS NOT NULL AND p_x_osp_order_rec.customer_id <> FND_API.G_MISS_NUM) THEN
1834: */
1835: -- Changes begin
1836: /*
1837: IF (p_x_osp_order_rec.contract_id IS NOT NULL AND p_x_osp_order_rec.contract_id = FND_API.G_MISS_NUM) THEN
1838: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CONTRACT_ID_NLL');
1839: FND_MSG_PUB.ADD;
1840: ELSIF(p_x_osp_order_rec.contract_id IS NOT NULL AND (p_x_osp_order_rec.vendor_id IS NULL OR p_x_osp_order_rec.vendor_id = FND_API.G_MISS_NUM)) THEN
1841: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_NLL');
1842: FND_MSG_PUB.ADD;
1837: IF (p_x_osp_order_rec.contract_id IS NOT NULL AND p_x_osp_order_rec.contract_id = FND_API.G_MISS_NUM) THEN
1838: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CONTRACT_ID_NLL');
1839: FND_MSG_PUB.ADD;
1840: ELSIF(p_x_osp_order_rec.contract_id IS NOT NULL AND (p_x_osp_order_rec.vendor_id IS NULL OR p_x_osp_order_rec.vendor_id = FND_API.G_MISS_NUM)) THEN
1841: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_NLL');
1842: FND_MSG_PUB.ADD;
1843: ELSIF(p_x_osp_order_rec.vendor_id IS NOT NULL OR p_x_osp_order_rec.contract_id IS NOT NULL) THEN
1844: -- validate vendor_id.
1845: validate_vendor(p_x_osp_order_rec.vendor_id);
1850: */
1851: /*
1852: -- validate vendor_id.
1853: IF(p_x_osp_order_rec.vendor_id IS NULL OR p_x_osp_order_rec.vendor_id = FND_API.G_MISS_NUM) THEN
1854: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_NLL');
1855: FND_MSG_PUB.ADD;
1856: ELSE
1857: validate_vendor(p_x_osp_order_rec.vendor_id);
1858: END IF;
1872: ELSIF(g_old_status_code = G_OSP_SUBMITTED_STATUS) THEN
1873: -- status_code should be null,'ENTERED', or 'SUBMITTED'
1874: IF(p_x_osp_order_rec.status_code IS NOT NULL AND
1875: p_x_osp_order_rec.status_code NOT IN(G_OSP_SUBMITTED_STATUS, G_OSP_CLOSED_STATUS)) THEN
1876: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1877: FND_MSG_PUB.ADD;
1878: END IF;
1879: -- CUSTOMER_ID cant change
1880: IF(p_x_osp_order_rec.customer_id IS NOT NULL AND p_x_osp_order_rec.customer_id <> l_osp_order_rec.customer_id) THEN
1877: FND_MSG_PUB.ADD;
1878: END IF;
1879: -- CUSTOMER_ID cant change
1880: IF(p_x_osp_order_rec.customer_id IS NOT NULL AND p_x_osp_order_rec.customer_id <> l_osp_order_rec.customer_id) THEN
1881: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CUST_CHG');
1882: FND_MSG_PUB.ADD;
1883: END IF;
1884: -- vendor_id cant change
1885: IF(p_x_osp_order_rec.vendor_id IS NOT NULL AND p_x_osp_order_rec.vendor_id <> l_osp_order_rec.vendor_id) THEN
1882: FND_MSG_PUB.ADD;
1883: END IF;
1884: -- vendor_id cant change
1885: IF(p_x_osp_order_rec.vendor_id IS NOT NULL AND p_x_osp_order_rec.vendor_id <> l_osp_order_rec.vendor_id) THEN
1886: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEND_CHG');
1887: FND_MSG_PUB.ADD;
1888: END IF;
1889: -- contract_id cant change
1890: IF(p_x_osp_order_rec.contract_id IS NOT NULL AND p_x_osp_order_rec.contract_id <> l_osp_order_rec.contract_id) THEN
1887: FND_MSG_PUB.ADD;
1888: END IF;
1889: -- contract_id cant change
1890: IF(p_x_osp_order_rec.contract_id IS NOT NULL AND p_x_osp_order_rec.contract_id <> l_osp_order_rec.contract_id) THEN
1891: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CTRCT_CHG');
1892: FND_MSG_PUB.ADD;
1893: END IF;
1894: -- set status
1895: --g_order_status_for_update := G_OSP_SUBMITTED_STATUS;
1893: END IF;
1894: -- set status
1895: --g_order_status_for_update := G_OSP_SUBMITTED_STATUS;
1896: ELSE
1897: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1898: FND_MSG_PUB.ADD;
1899: END IF;
1900: END IF;
1901: ELSE
1898: FND_MSG_PUB.ADD;
1899: END IF;
1900: END IF;
1901: ELSE
1902: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_TYPE');
1903: FND_MSG_PUB.ADD;
1904: END IF;
1905: -- delete header
1906: IF(p_x_osp_order_rec.operation_flag = G_OP_DELETE) THEN
1905: -- delete header
1906: IF(p_x_osp_order_rec.operation_flag = G_OP_DELETE) THEN
1907: -- Changed by jaramana on January 9, 2008 for the Requisition ER 6034236 (Added G_OSP_REQ_SUB_FAILED_STATUS)
1908: IF(g_old_status_code NOT IN( G_OSP_ENTERED_STATUS, G_OSP_SUB_FAILED_STATUS, G_OSP_REQ_SUB_FAILED_STATUS ))THEN
1909: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INVOP');
1910: FND_MSG_PUB.ADD;
1911: ELSE
1912: --g_order_status_for_update := G_OSP_DELETED_STATUS;
1913: IF(l_osp_order_rec.oe_header_id IS NOT NULL) THEN
1975: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1976: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'osp_order_id='||p_x_osp_order_rec.osp_order_id|| 'ovn='||p_x_osp_order_rec.object_version_number);
1977: END IF;
1978: IF (osp_order_csr%NOTFOUND) THEN
1979: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INVOP_OSP_NFOUND');
1980: FND_MSG_PUB.ADD;
1981: ELSE
1982: --dbms_output.put_line('l_osp_order_rec.osp_order_number ' || l_osp_order_rec.osp_order_number);
1983: IF (p_x_osp_order_rec.osp_order_number IS NULL) THEN
2340: IF(p_workorder_id IS NOT NULL) THEN
2341: OPEN val_workorder_id_csr(p_workorder_id);
2342: FETCH val_workorder_id_csr INTO l_exist;
2343: IF(val_workorder_id_csr%NOTFOUND) THEN
2344: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_WO');
2345: FND_MESSAGE.Set_Token('WORKORDER_ID', p_workorder_id);
2346: FND_MSG_PUB.ADD;
2347: END IF;
2348: CLOSE val_workorder_id_csr;
2341: OPEN val_workorder_id_csr(p_workorder_id);
2342: FETCH val_workorder_id_csr INTO l_exist;
2343: IF(val_workorder_id_csr%NOTFOUND) THEN
2344: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_WO');
2345: FND_MESSAGE.Set_Token('WORKORDER_ID', p_workorder_id);
2346: FND_MSG_PUB.ADD;
2347: END IF;
2348: CLOSE val_workorder_id_csr;
2349: END IF;
2397: IF(p_service_item_id IS NOT NULL AND p_service_item_id <> FND_API.G_MISS_NUM) THEN
2398: OPEN val_service_item_id_inv_csr(p_service_item_id, p_workorder_id);
2399: FETCH val_service_item_id_inv_csr INTO l_exist;
2400: IF(val_service_item_id_inv_csr%NOTFOUND) THEN
2401: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM');
2402: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2403: FND_MSG_PUB.ADD;
2404: --dbms_output.put_line('Invalid service item not in inventory');
2405: ELSE
2398: OPEN val_service_item_id_inv_csr(p_service_item_id, p_workorder_id);
2399: FETCH val_service_item_id_inv_csr INTO l_exist;
2400: IF(val_service_item_id_inv_csr%NOTFOUND) THEN
2401: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM');
2402: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2403: FND_MSG_PUB.ADD;
2404: --dbms_output.put_line('Invalid service item not in inventory');
2405: ELSE
2406: -- Valid service item, exists in WO Org
2412: OPEN item_exists_in_wo_org_csr(p_workorder_id);
2413: FETCH item_exists_in_wo_org_csr INTO l_exist;
2414: IF (item_exists_in_wo_org_csr%FOUND) THEN
2415: -- WO Service Item does exist in WO Org. So, should have matched!
2416: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM_MISWO');
2417: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2418: FND_MSG_PUB.ADD;
2419: --dbms_output.put_line('Invalid service item , does not match in wo');
2420: ELSE
2413: FETCH item_exists_in_wo_org_csr INTO l_exist;
2414: IF (item_exists_in_wo_org_csr%FOUND) THEN
2415: -- WO Service Item does exist in WO Org. So, should have matched!
2416: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM_MISWO');
2417: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2418: FND_MSG_PUB.ADD;
2419: --dbms_output.put_line('Invalid service item , does not match in wo');
2420: ELSE
2421: -- WO Service Item does not exist in WO Org.
2438: OPEN item_exists_in_wo_org_csr(p_workorder_id);
2439: FETCH item_exists_in_wo_org_csr INTO l_exist;
2440: IF (item_exists_in_wo_org_csr%FOUND) THEN
2441: -- WO Service Item does exist in WO Org. So, should not have been null
2442: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM_NLL');
2443: FND_MSG_PUB.ADD;
2444: --dbms_output.put_line('Passed service item is null, in wo its not null');
2445: ELSE
2446: -- WO Service Item does not exist in WO Org.
2475: IF(p_service_item_id IS NOT NULL AND p_service_item_id <> FND_API.G_MISS_NUM) THEN
2476: OPEN val_service_item_desc_csr(p_service_item_id , p_service_item_description);
2477: FETCH val_service_item_desc_csr INTO l_exist;
2478: IF(val_service_item_desc_csr%NOTFOUND) THEN
2479: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM_DESC');
2480: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2481: FND_MESSAGE.Set_Token('SERVICE_ITEM_DESC', p_service_item_description);
2482: FND_MSG_PUB.ADD;
2483: --dbms_output.put_line('Invalid service item description');
2476: OPEN val_service_item_desc_csr(p_service_item_id , p_service_item_description);
2477: FETCH val_service_item_desc_csr INTO l_exist;
2478: IF(val_service_item_desc_csr%NOTFOUND) THEN
2479: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM_DESC');
2480: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2481: FND_MESSAGE.Set_Token('SERVICE_ITEM_DESC', p_service_item_description);
2482: FND_MSG_PUB.ADD;
2483: --dbms_output.put_line('Invalid service item description');
2484: END IF;
2477: FETCH val_service_item_desc_csr INTO l_exist;
2478: IF(val_service_item_desc_csr%NOTFOUND) THEN
2479: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM_DESC');
2480: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2481: FND_MESSAGE.Set_Token('SERVICE_ITEM_DESC', p_service_item_description);
2482: FND_MSG_PUB.ADD;
2483: --dbms_output.put_line('Invalid service item description');
2484: END IF;
2485: CLOSE val_service_item_desc_csr;
2542:
2543: OPEN val_service_item_id_inv_csr(p_service_item_id, p_workorder_id);
2544: FETCH val_service_item_id_inv_csr INTO l_exist;
2545: IF(val_service_item_id_inv_csr%NOTFOUND) THEN
2546: FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_OSP_LN_INV_SVC_ITEM');
2547: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2548: FND_MSG_PUB.ADD;
2549: --dbms_output.put_line('Invalid service item not in inventory');
2550: ELSE
2543: OPEN val_service_item_id_inv_csr(p_service_item_id, p_workorder_id);
2544: FETCH val_service_item_id_inv_csr INTO l_exist;
2545: IF(val_service_item_id_inv_csr%NOTFOUND) THEN
2546: FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_OSP_LN_INV_SVC_ITEM');
2547: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2548: FND_MSG_PUB.ADD;
2549: --dbms_output.put_line('Invalid service item not in inventory');
2550: ELSE
2551: -- Valid service item, exists in WO Org
2556: OPEN item_exists_in_wo_org_csr(p_workorder_id);
2557: FETCH item_exists_in_wo_org_csr INTO l_exist;
2558: IF (item_exists_in_wo_org_csr%FOUND) THEN
2559: -- WO Service Item does exist in WO Org. So, should have matched!
2560: FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_OSP_LN_INV_SVC_ITEM_MISWO');
2561: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2562: FND_MSG_PUB.ADD;
2563: --dbms_output.put_line('Invalid service item , does not match in wo');
2564: ELSE
2557: FETCH item_exists_in_wo_org_csr INTO l_exist;
2558: IF (item_exists_in_wo_org_csr%FOUND) THEN
2559: -- WO Service Item does exist in WO Org. So, should have matched!
2560: FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_OSP_LN_INV_SVC_ITEM_MISWO');
2561: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2562: FND_MSG_PUB.ADD;
2563: --dbms_output.put_line('Invalid service item , does not match in wo');
2564: ELSE
2565: -- WO Service Item does not exist in WO Org.
2581: OPEN item_exists_in_wo_org_csr(p_workorder_id);
2582: FETCH item_exists_in_wo_org_csr INTO l_exist;
2583: IF (item_exists_in_wo_org_csr%FOUND) THEN
2584: -- WO Service Item does exist in WO Org. So, should not have been null
2585: FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_OSP_LN_INV_SVC_ITEM_NLL');
2586: FND_MSG_PUB.ADD;
2587: --dbms_output.put_line('Passed service item is null, in wo its not null');
2588: ELSE
2589: -- WO Service Item does not exist in WO Org.
2627: IF(p_service_item_uom_code IS NOT NULL AND p_service_item_uom_code <> FND_API.G_MISS_CHAR) THEN
2628: OPEN val_service_item_uom_csr(p_service_item_id,p_service_item_uom_code,p_org_id);
2629: FETCH val_service_item_uom_csr INTO l_exist;
2630: IF(val_service_item_uom_csr%NOTFOUND) THEN
2631: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_UOM');
2632: FND_MESSAGE.Set_Token('UOM_CODE', p_service_item_uom_code);
2633: -- FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2634: FND_MSG_PUB.ADD;
2635: END IF;
2628: OPEN val_service_item_uom_csr(p_service_item_id,p_service_item_uom_code,p_org_id);
2629: FETCH val_service_item_uom_csr INTO l_exist;
2630: IF(val_service_item_uom_csr%NOTFOUND) THEN
2631: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_UOM');
2632: FND_MESSAGE.Set_Token('UOM_CODE', p_service_item_uom_code);
2633: -- FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2634: FND_MSG_PUB.ADD;
2635: END IF;
2636: CLOSE val_service_item_uom_csr;
2629: FETCH val_service_item_uom_csr INTO l_exist;
2630: IF(val_service_item_uom_csr%NOTFOUND) THEN
2631: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_UOM');
2632: FND_MESSAGE.Set_Token('UOM_CODE', p_service_item_uom_code);
2633: -- FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2634: FND_MSG_PUB.ADD;
2635: END IF;
2636: CLOSE val_service_item_uom_csr;
2637: END IF;
2639: IF(p_service_item_uom_code IS NOT NULL AND p_service_item_uom_code <> FND_API.G_MISS_CHAR) THEN
2640: OPEN val_uom_code_csr(p_service_item_uom_code);
2641: FETCH val_uom_code_csr INTO l_exist;
2642: IF(val_uom_code_csr%NOTFOUND) THEN
2643: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_UOM');
2644: FND_MESSAGE.Set_Token('UOM_CODE', p_service_item_uom_code);
2645: -- FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2646: FND_MSG_PUB.ADD;
2647: END IF;
2640: OPEN val_uom_code_csr(p_service_item_uom_code);
2641: FETCH val_uom_code_csr INTO l_exist;
2642: IF(val_uom_code_csr%NOTFOUND) THEN
2643: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_UOM');
2644: FND_MESSAGE.Set_Token('UOM_CODE', p_service_item_uom_code);
2645: -- FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2646: FND_MSG_PUB.ADD;
2647: END IF;
2648: CLOSE val_uom_code_csr;
2641: FETCH val_uom_code_csr INTO l_exist;
2642: IF(val_uom_code_csr%NOTFOUND) THEN
2643: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_UOM');
2644: FND_MESSAGE.Set_Token('UOM_CODE', p_service_item_uom_code);
2645: -- FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2646: FND_MSG_PUB.ADD;
2647: END IF;
2648: CLOSE val_uom_code_csr;
2649: END IF;
2671: IF(p_po_line_type_id IS NOT NULL AND p_po_line_type_id <> FND_API.G_MISS_NUM) THEN
2672: OPEN val_po_line_type_id_csr(p_po_line_type_id);
2673: FETCH val_po_line_type_id_csr INTO l_exist;
2674: IF(val_po_line_type_id_csr%NOTFOUND) THEN
2675: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_LNTYP_ID');
2676: FND_MESSAGE.Set_Token('LINE_TYPE_ID', p_po_line_type_id);
2677: FND_MSG_PUB.ADD;
2678: END IF;
2679: CLOSE val_po_line_type_id_csr;
2672: OPEN val_po_line_type_id_csr(p_po_line_type_id);
2673: FETCH val_po_line_type_id_csr INTO l_exist;
2674: IF(val_po_line_type_id_csr%NOTFOUND) THEN
2675: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_LNTYP_ID');
2676: FND_MESSAGE.Set_Token('LINE_TYPE_ID', p_po_line_type_id);
2677: FND_MSG_PUB.ADD;
2678: END IF;
2679: CLOSE val_po_line_type_id_csr;
2680: END IF;
2709: IF(p_po_line_id IS NOT NULL AND p_po_line_id <> FND_API.G_MISS_NUM) THEN
2710: OPEN val_po_line_id_csr(p_po_line_id, p_osp_order_id);
2711: FETCH val_po_line_id_csr INTO l_exist;
2712: IF(val_po_line_id_csr%NOTFOUND) THEN
2713: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PO_LINE_INV');
2714: FND_MESSAGE.Set_Token('PO_LINE_ID', p_po_line_id);
2715: FND_MSG_PUB.ADD;
2716: END IF;
2717: CLOSE val_po_line_id_csr;
2710: OPEN val_po_line_id_csr(p_po_line_id, p_osp_order_id);
2711: FETCH val_po_line_id_csr INTO l_exist;
2712: IF(val_po_line_id_csr%NOTFOUND) THEN
2713: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PO_LINE_INV');
2714: FND_MESSAGE.Set_Token('PO_LINE_ID', p_po_line_id);
2715: FND_MSG_PUB.ADD;
2716: END IF;
2717: CLOSE val_po_line_id_csr;
2718: END IF;
2742: IF(p_exchange_instance_id IS NOT NULL AND p_exchange_instance_id <> FND_API.G_MISS_NUM) THEN
2743: OPEN val_instance_id_csr(p_exchange_instance_id);
2744: FETCH val_instance_id_csr INTO l_exist;
2745: IF (val_instance_id_csr %NOTFOUND) THEN
2746: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INSTANCE_ID_INV');
2747: FND_MESSAGE.Set_Token('EXCHANGE_INSTANCE_ID', p_exchange_instance_id);
2748: FND_MSG_PUB.ADD;
2749: END IF;
2750: END IF;
2743: OPEN val_instance_id_csr(p_exchange_instance_id);
2744: FETCH val_instance_id_csr INTO l_exist;
2745: IF (val_instance_id_csr %NOTFOUND) THEN
2746: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INSTANCE_ID_INV');
2747: FND_MESSAGE.Set_Token('EXCHANGE_INSTANCE_ID', p_exchange_instance_id);
2748: FND_MSG_PUB.ADD;
2749: END IF;
2750: END IF;
2751: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2833: p_x_osp_order_rec.status_code IN (G_OSP_SUBMITTED_STATUS, G_OSP_REQ_SUBMITTED_STATUS)) THEN
2834: -- jaramana End
2835: -- validate fields and submit for PO creation
2836: IF(p_x_osp_order_rec.vendor_id IS NULL) THEN
2837: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_VEN_NLL');
2838: FND_MSG_PUB.ADD;
2839: END IF;
2840: IF (p_x_osp_order_rec.vendor_site_id IS NULL) THEN
2841: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_VENST_NLL');
2837: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_VEN_NLL');
2838: FND_MSG_PUB.ADD;
2839: END IF;
2840: IF (p_x_osp_order_rec.vendor_site_id IS NULL) THEN
2841: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_VENST_NLL');
2842: FND_MSG_PUB.ADD;
2843: END IF;
2844: IF (p_x_osp_order_rec.po_agent_id IS NULL) THEN
2845: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_BUYER_NLL');
2841: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_VENST_NLL');
2842: FND_MSG_PUB.ADD;
2843: END IF;
2844: IF (p_x_osp_order_rec.po_agent_id IS NULL) THEN
2845: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_BUYER_NLL');
2846: FND_MSG_PUB.ADD;
2847: END IF;
2848: IF (p_x_osp_order_rec.po_header_id IS NOT NULL) THEN
2849: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_PO_NNLL');
2845: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_BUYER_NLL');
2846: FND_MSG_PUB.ADD;
2847: END IF;
2848: IF (p_x_osp_order_rec.po_header_id IS NOT NULL) THEN
2849: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_PO_NNLL');
2850: FND_MSG_PUB.ADD;
2851: END IF;
2852:
2853: -- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
2851: END IF;
2852:
2853: -- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
2854: IF (p_x_osp_order_rec.po_req_header_id IS NOT NULL) THEN
2855: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_REQ_NNLL');
2856: FND_MSG_PUB.ADD;
2857: END IF;
2858: -- jaramana End
2859:
2860: -- check whether order has lines
2861: OPEN val_order_has_lines(p_x_osp_order_rec.osp_order_id);
2862: FETCH val_order_has_lines INTO l_exist;
2863: IF(val_order_has_lines%NOTFOUND) THEN
2864: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_NO_LNS');
2865: FND_MSG_PUB.ADD;
2866: END IF;
2867: CLOSE val_order_has_lines;
2868: -- check null values in order lines
2872: l_service_item_uom_code, l_quantity, l_need_by_date, l_po_line_type_id;
2873: EXIT WHEN val_order_lines_csr%NOTFOUND;
2874: --service item id and service item description cannot be both null
2875: IF(l_service_item_description IS NULL AND l_service_item_id IS NULL) THEN
2876: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_ITMID_NL');
2877: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2878: FND_MSG_PUB.ADD;
2879: END IF;
2880: --l_service_item_uom_code cannot be null
2873: EXIT WHEN val_order_lines_csr%NOTFOUND;
2874: --service item id and service item description cannot be both null
2875: IF(l_service_item_description IS NULL AND l_service_item_id IS NULL) THEN
2876: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_ITMID_NL');
2877: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2878: FND_MSG_PUB.ADD;
2879: END IF;
2880: --l_service_item_uom_code cannot be null
2881: IF(l_service_item_uom_code IS NULL) THEN
2878: FND_MSG_PUB.ADD;
2879: END IF;
2880: --l_service_item_uom_code cannot be null
2881: IF(l_service_item_uom_code IS NULL) THEN
2882: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_UOM_NL');
2883: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2884: FND_MSG_PUB.ADD;
2885: END IF;
2886: --quantity cannot be null
2879: END IF;
2880: --l_service_item_uom_code cannot be null
2881: IF(l_service_item_uom_code IS NULL) THEN
2882: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_UOM_NL');
2883: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2884: FND_MSG_PUB.ADD;
2885: END IF;
2886: --quantity cannot be null
2887: IF(l_quantity IS NULL) THEN
2884: FND_MSG_PUB.ADD;
2885: END IF;
2886: --quantity cannot be null
2887: IF(l_quantity IS NULL) THEN
2888: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_QTY_NL');
2889: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2890: FND_MSG_PUB.ADD;
2891: END IF;
2892: --need_by_date cannot be null
2885: END IF;
2886: --quantity cannot be null
2887: IF(l_quantity IS NULL) THEN
2888: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_QTY_NL');
2889: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2890: FND_MSG_PUB.ADD;
2891: END IF;
2892: --need_by_date cannot be null
2893: IF(l_need_by_date IS NULL) THEN
2890: FND_MSG_PUB.ADD;
2891: END IF;
2892: --need_by_date cannot be null
2893: IF(l_need_by_date IS NULL) THEN
2894: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_NBD_NL');
2895: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2896: FND_MSG_PUB.ADD;
2897: END IF;
2898: --po_line_type_id cannot be null
2891: END IF;
2892: --need_by_date cannot be null
2893: IF(l_need_by_date IS NULL) THEN
2894: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_NBD_NL');
2895: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2896: FND_MSG_PUB.ADD;
2897: END IF;
2898: --po_line_type_id cannot be null
2899: IF(l_po_line_type_id IS NULL) THEN
2896: FND_MSG_PUB.ADD;
2897: END IF;
2898: --po_line_type_id cannot be null
2899: IF(l_po_line_type_id IS NULL) THEN
2900: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_PO_LNTYP_NL');
2901: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2902: FND_MSG_PUB.ADD;
2903: END IF;
2904: END LOOP;
2897: END IF;
2898: --po_line_type_id cannot be null
2899: IF(l_po_line_type_id IS NULL) THEN
2900: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_PO_LNTYP_NL');
2901: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2902: FND_MSG_PUB.ADD;
2903: END IF;
2904: END LOOP;
2905: CLOSE val_order_lines_csr;
2924: FETCH get_old_status INTO l_temp_status_code, l_new_ovn;
2925: CLOSE get_old_status;
2926: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS OR
2927: l_new_ovn <> l_old_ovn+1) THEN
2928: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_PO_FLD');
2929: FND_MSG_PUB.ADD;
2930: ELSE
2931: p_x_osp_order_rec.object_version_number := l_new_ovn;
2932: p_x_osp_order_rec.po_request_id := l_request_id;
2956: FETCH get_old_status INTO l_temp_status_code, l_new_ovn;
2957: CLOSE get_old_status;
2958: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS OR
2959: l_new_ovn <> l_old_ovn+1) THEN
2960: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_REQ_FLD');
2961: FND_MSG_PUB.ADD;
2962: ELSE
2963: p_x_osp_order_rec.object_version_number := l_new_ovn;
2964: p_x_osp_order_rec.po_request_id := l_request_id;
2978: ELSIF(l_old_status_code IN (G_OSP_PO_CREATED_STATUS,G_OSP_REQ_CREATED_STATUS) AND p_x_osp_order_rec.status_code = G_OSP_CLOSED_STATUS) THEN
2979:
2980: -- validate fields and ask whether PO and SO is closed.
2981: IF(l_old_status_code = G_OSP_PO_CREATED_STATUS AND p_x_osp_order_rec.po_header_id IS NULL) THEN
2982: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_PO_NLL');
2983: FND_MSG_PUB.ADD;
2984: ELSIF(l_old_status_code = G_OSP_REQ_CREATED_STATUS AND p_x_osp_order_rec.po_req_header_id IS NULL) THEN
2985: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_REQ_NLL');
2986: FND_MSG_PUB.ADD;
2981: IF(l_old_status_code = G_OSP_PO_CREATED_STATUS AND p_x_osp_order_rec.po_header_id IS NULL) THEN
2982: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_PO_NLL');
2983: FND_MSG_PUB.ADD;
2984: ELSIF(l_old_status_code = G_OSP_REQ_CREATED_STATUS AND p_x_osp_order_rec.po_req_header_id IS NULL) THEN
2985: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_REQ_NLL');
2986: FND_MSG_PUB.ADD;
2987: ELSE --This would mean either po_header_id is not null OR po_req_header_id is not NULL
2988: -- If the status is PO_CREATED, ask PO whether it is closed/cancelled.
2989: IF (l_old_status_code = G_OSP_PO_CREATED_STATUS AND
2987: ELSE --This would mean either po_header_id is not null OR po_req_header_id is not NULL
2988: -- If the status is PO_CREATED, ask PO whether it is closed/cancelled.
2989: IF (l_old_status_code = G_OSP_PO_CREATED_STATUS AND
2990: AHL_OSP_PO_PVT.Is_PO_Closed(p_x_osp_order_rec.po_header_id) = G_NO_FLAG ) THEN
2991: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_PO_OPEN');
2992: FND_MSG_PUB.ADD;
2993: END IF;
2994: -- If the status is REQ_CREATED, ask Requisition whether the order can be closed.
2995: IF(l_old_status_code = G_OSP_REQ_CREATED_STATUS AND
2993: END IF;
2994: -- If the status is REQ_CREATED, ask Requisition whether the order can be closed.
2995: IF(l_old_status_code = G_OSP_REQ_CREATED_STATUS AND
2996: AHL_OSP_PO_REQ_PVT.Is_PO_Req_Closed(p_x_osp_order_rec.po_req_header_id) = G_NO_FLAG ) THEN
2997: FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_INV_CL_REQ_OPEN');
2998: FND_MSG_PUB.ADD;
2999: END IF;
3000:
3001: -- ask SO whether can close
3000:
3001: -- ask SO whether can close
3002: IF(p_x_osp_order_rec.oe_header_id IS NOT NULL) THEN
3003: IF NOT(FND_API.TO_BOOLEAN(AHL_OSP_SHIPMENT_PUB.Is_Order_Header_Closed( p_x_osp_order_rec.oe_header_id))) THEN
3004: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_SO_OPEN');
3005: FND_MSG_PUB.ADD;
3006: END IF;
3007: END IF;
3008: -- check whether all the workorders are closed.
3008: -- check whether all the workorders are closed.
3009: OPEN val_all_wo_closed(p_x_osp_order_rec.osp_order_id);
3010: FETCH val_all_wo_closed INTO l_exist;
3011: IF(val_all_wo_closed%FOUND) THEN
3012: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_WO_OPEN');
3013: FND_MSG_PUB.ADD;
3014: END IF;
3015: CLOSE val_all_wo_closed;
3016: END IF;
3020: IF(l_old_status_code = G_OSP_ENTERED_STATUS AND
3021: p_x_osp_order_rec.status_code = G_OSP_SUBMITTED_STATUS) THEN
3022: IF(p_x_osp_order_rec.customer_id IS NULL AND p_x_osp_order_rec.order_type_code = G_OSP_ORDER_TYPE_LOAN) THEN
3023: --only loan type requires customer id for borrow it's optional (bug fix).
3024: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_CUST_NLL');
3025: FND_MSG_PUB.ADD;
3026: END IF;
3027: /* Change made by mpothuku to make the contract_id optional on 12/16/04
3028: This is a work around and has to be revoked after PM comes up with the Service Contract Integration
3029: */
3030: -- Changes start
3031: /*
3032: IF(p_x_osp_order_rec.contract_id IS NULL) THEN
3033: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_CTRCT_NLL');
3034: FND_MSG_PUB.ADD;
3035: END IF;
3036: */
3037: -- Changes by mpothuku End
3035: END IF;
3036: */
3037: -- Changes by mpothuku End
3038: IF(p_x_osp_order_rec.oe_header_id IS NULL) THEN
3039: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_OE_NLL');
3040: FND_MSG_PUB.ADD;
3041: END IF;
3042: -- check whether order has lines and all lines have shipping information
3043: OPEN val_order_has_ship_lines(p_x_osp_order_rec.osp_order_id);
3046: FETCH val_order_has_ship_lines INTO l_oe_ship_line_id, l_oe_return_line_id, l_line_number;
3047: IF(val_order_has_ship_lines%NOTFOUND) THEN
3048: EXIT;
3049: ELSIF (l_oe_ship_line_id IS NULL AND l_oe_return_line_id IS NULL) THEN
3050: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_NO_SHIP_LN');
3051: FND_MESSAGE.Set_Token('LINE_NUMBER', l_line_number);
3052: FND_MSG_PUB.ADD;
3053: l_count := l_count + 1;
3054: ELSE
3047: IF(val_order_has_ship_lines%NOTFOUND) THEN
3048: EXIT;
3049: ELSIF (l_oe_ship_line_id IS NULL AND l_oe_return_line_id IS NULL) THEN
3050: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_NO_SHIP_LN');
3051: FND_MESSAGE.Set_Token('LINE_NUMBER', l_line_number);
3052: FND_MSG_PUB.ADD;
3053: l_count := l_count + 1;
3054: ELSE
3055: l_count := l_count + 1;
3055: l_count := l_count + 1;
3056: END IF;
3057: END LOOP;
3058: IF(l_count < 1) THEN
3059: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_NO_LNS');
3060: FND_MSG_PUB.ADD;
3061: END IF;
3062: CLOSE val_order_has_ship_lines;
3063: IF FND_MSG_PUB.count_msg > 0 THEN
3074: x_msg_count => l_msg_count,
3075: x_msg_data => l_msg_data
3076: );
3077: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3078: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_SO_FLD');
3079: FND_MSG_PUB.ADD;
3080: END IF;
3081: END IF;
3082: --ELSIF(g_old_status_code = G_OSP_SUBMITTED_STATUS AND p_x_osp_order_rec.status_code = G_OSP_CLOSED_STATUS) THEN
3083: ELSIF(l_old_status_code = G_OSP_SUBMITTED_STATUS AND p_x_osp_order_rec.status_code = G_OSP_CLOSED_STATUS) THEN
3084: -- validate fields and ask SO whether order can be closed.
3085: --dbms_output.put_line('Sales order closed ');
3086: IF(p_x_osp_order_rec.oe_header_id IS NULL) THEN
3087: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_OE_NLL');
3088: FND_MSG_PUB.ADD;
3089: ELSE
3090: --dbms_output.put_line('Sales order closed ' || AHL_OSP_SHIPMENT_PUB.Is_Order_Header_Closed( p_x_osp_order_rec.oe_header_id));
3091: IF NOT(FND_API.TO_BOOLEAN(AHL_OSP_SHIPMENT_PUB.Is_Order_Header_Closed( p_x_osp_order_rec.oe_header_id))) THEN
3089: ELSE
3090: --dbms_output.put_line('Sales order closed ' || AHL_OSP_SHIPMENT_PUB.Is_Order_Header_Closed( p_x_osp_order_rec.oe_header_id));
3091: IF NOT(FND_API.TO_BOOLEAN(AHL_OSP_SHIPMENT_PUB.Is_Order_Header_Closed( p_x_osp_order_rec.oe_header_id))) THEN
3092: --dbms_output.put_line('Sales order is not closed');
3093: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_SO_OPEN');
3094: FND_MSG_PUB.ADD;
3095: END IF;
3096: -- check whether all the workorders are closed.
3097: OPEN val_all_wo_closed(p_x_osp_order_rec.osp_order_id);
3096: -- check whether all the workorders are closed.
3097: OPEN val_all_wo_closed(p_x_osp_order_rec.osp_order_id);
3098: FETCH val_all_wo_closed INTO l_exist;
3099: IF(val_all_wo_closed%FOUND) THEN
3100: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_WO_OPEN');
3101: FND_MSG_PUB.ADD;
3102: END IF;
3103: CLOSE val_all_wo_closed;
3104: END IF;
3158: x_msg_count => l_msg_count ,
3159: x_msg_data => l_msg_data
3160: );
3161: IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3162: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SO_CAN_FLD');
3163: FND_MSG_PUB.ADD;
3164: END IF;
3165: ELSIF ( p_del_cancel_so_lines_tbl IS NOT NULL) THEN
3166: IF NOT(p_del_cancel_so_lines_tbl.COUNT > 0) THEN
3253: x_msg_count => l_msg_count ,
3254: x_msg_data => l_msg_data
3255: );
3256: IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3257: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SO_LN_CAN_FLD');
3258: FND_MSG_PUB.ADD;
3259: END IF;
3260: END IF;
3261: END IF;
3289: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3290: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Begin Function');
3291: END IF;
3292: IF(p_old_type_code NOT IN (G_OSP_ORDER_TYPE_SERVICE, G_OSP_ORDER_TYPE_EXCHANGE)) THEN
3293: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CONV_FR_TYPE');
3294: FND_MSG_PUB.ADD;
3295: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3296: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY ||'.error', 'Cannot Convert from Order Type is not Service or Exchange');
3297: END IF;
3297: END IF;
3298: return false;
3299: END IF;
3300: IF(p_new_type_code NOT IN (G_OSP_ORDER_TYPE_SERVICE, G_OSP_ORDER_TYPE_EXCHANGE)) THEN
3301: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CONV_TO_TYPE');
3302: FND_MSG_PUB.ADD;
3303: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3304: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY ||'.error', 'Cannot Convert to an order that is not of type Service or Exchange');
3305: END IF;
3308: -- check if the order is closed
3309: OPEN order_header_status_csr(p_osp_order_id);
3310: FETCH order_header_status_csr INTO l_status_code;
3311: IF(l_status_code = G_OSP_CLOSED_STATUS) THEN
3312: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CONV_CLOSED');
3313: FND_MSG_PUB.ADD;
3314: CLOSE order_header_status_csr;
3315: return false;
3316: END IF;
3320: /*
3321: OPEN order_has_ship_return_csr(p_osp_order_id);
3322: FETCH order_has_ship_return_csr INTO l_exist;
3323: IF( order_has_ship_return_csr %FOUND) THEN
3324: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CONV_HAS_RET_LINE');
3325: FND_MSG_PUB.ADD;
3326: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3327: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY ||'.error', 'can_convert: Cannot Convert. Order has shipment return line');
3328: END IF;
3462: --Validate the operation_flag of the header record, and it could be NULL which
3463: --means no change to the header record
3464: IF (p_x_osp_order_rec.operation_flag IS NOT NULL AND
3465: p_x_osp_order_rec.operation_flag NOT IN (G_OP_CREATE, G_OP_UPDATE, G_OP_DELETE)) THEN
3466: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORD_INVOP');
3467: FND_MSG_PUB.add;
3468: RAISE FND_API.G_EXC_ERROR;
3469: END IF;
3470: --Validate the operation_flag of the line records and it couldn't be NULL
3472: --to the lines table
3473: IF (p_x_osp_order_lines_tbl.COUNT > 0) THEN
3474: FOR i IN p_x_osp_order_lines_tbl.FIRST..p_x_osp_order_lines_tbl.LAST LOOP
3475: IF (p_x_osp_order_lines_tbl(i).operation_flag NOT IN (G_OP_CREATE, G_OP_UPDATE, G_OP_DELETE)) THEN
3476: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORD_INVOP');
3477: FND_MSG_PUB.add;
3478: RAISE FND_API.G_EXC_ERROR;
3479: END IF;
3480: END LOOP;
3520: FROM ahl_osp_orders_b
3521: WHERE osp_order_id = l_osp_order_id;
3522: EXCEPTION
3523: WHEN NO_DATA_FOUND THEN
3524: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_LN_INV');
3525: FND_MESSAGE.Set_Token('OSP_LINE_ID',p_x_osp_order_lines_tbl(i).osp_order_line_id);
3526: FND_MSG_PUB.ADD;
3527: RAISE FND_API.G_EXC_ERROR;
3528: END;
3521: WHERE osp_order_id = l_osp_order_id;
3522: EXCEPTION
3523: WHEN NO_DATA_FOUND THEN
3524: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_LN_INV');
3525: FND_MESSAGE.Set_Token('OSP_LINE_ID',p_x_osp_order_lines_tbl(i).osp_order_line_id);
3526: FND_MSG_PUB.ADD;
3527: RAISE FND_API.G_EXC_ERROR;
3528: END;
3529: -- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
3543: delete_cancel_so(
3544: p_oe_header_id => NULL,
3545: p_del_cancel_so_lines_tbl => l_del_cancel_so_lines_tbl);
3546: ELSE
3547: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INVOP');
3548: FND_MSG_PUB.ADD;
3549: RAISE FND_API.G_EXC_ERROR;
3550: END IF;
3551: ELSIF (p_x_osp_order_lines_tbl(i).operation_flag = G_OP_CREATE) THEN
3584: FROM ahl_osp_orders_b
3585: WHERE osp_order_id = p_x_osp_order_lines_tbl(l_first_index).osp_order_id;
3586: EXCEPTION
3587: WHEN NO_DATA_FOUND THEN
3588: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORD_INVALID');
3589: FND_MSG_PUB.add;
3590: RAISE FND_API.G_EXC_ERROR;
3591: END;
3592:
3602: END IF;
3603: END LOOP;
3604: END IF;
3605: IF NOT l_validate_pass_flag THEN
3606: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ITEM_VENDOR_MISMATCH');
3607: FND_MSG_PUB.add;
3608: RAISE FND_API.G_EXC_ERROR;
3609: END IF;
3610: ELSE
3607: FND_MSG_PUB.add;
3608: RAISE FND_API.G_EXC_ERROR;
3609: END IF;
3610: ELSE
3611: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ITEM_VENDOR_MISMATCH');
3612: FND_MSG_PUB.add;
3613: RAISE FND_API.G_EXC_ERROR;
3614: END IF;
3615: END IF;
3631: FROM ahl_osp_orders_b
3632: WHERE osp_order_id = p_x_osp_order_rec.osp_order_id;
3633: EXCEPTION
3634: WHEN NO_DATA_FOUND THEN
3635: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORD_INVALID');
3636: FND_MSG_PUB.add;
3637: RAISE FND_API.G_EXC_ERROR;
3638: END;
3639: --check the osp order could be deleted, if Yes then
3657: FROM ahl_osp_order_lines
3658: WHERE osp_order_line_id = l_get_order_lines.osp_order_line_id;
3659: EXCEPTION
3660: WHEN NO_DATA_FOUND THEN
3661: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_LN_INV');
3662: FND_MESSAGE.Set_Token('OSP_LINE_ID',l_get_order_lines.osp_order_line_id);
3663: FND_MSG_PUB.ADD;
3664: RAISE FND_API.G_EXC_ERROR;
3665: END;
3658: WHERE osp_order_line_id = l_get_order_lines.osp_order_line_id;
3659: EXCEPTION
3660: WHEN NO_DATA_FOUND THEN
3661: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_LN_INV');
3662: FND_MESSAGE.Set_Token('OSP_LINE_ID',l_get_order_lines.osp_order_line_id);
3663: FND_MSG_PUB.ADD;
3664: RAISE FND_API.G_EXC_ERROR;
3665: END;
3666: IF (l_oe_ship_line_id IS NOT NULL OR l_oe_return_line_id IS NOT NULL) THEN
3693: G_LOG_PREFIX||l_api_name,
3694: 'After deleting header.');
3695: END IF;
3696: ELSE
3697: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INVOP');
3698: FND_MSG_PUB.ADD;
3699: RAISE FND_API.G_EXC_ERROR;
3700: END IF;
3701: ELSIF (p_x_osp_order_rec.operation_flag = G_OP_UPDATE) THEN
3725: FROM ahl_osp_orders_b
3726: WHERE osp_order_id = l_osp_order_id;
3727: EXCEPTION
3728: WHEN NO_DATA_FOUND THEN
3729: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_LN_INV');
3730: FND_MESSAGE.Set_Token('OSP_LINE_ID',p_x_osp_order_lines_tbl(i).osp_order_line_id);
3731: FND_MSG_PUB.ADD;
3732: RAISE FND_API.G_EXC_ERROR;
3733: END;
3726: WHERE osp_order_id = l_osp_order_id;
3727: EXCEPTION
3728: WHEN NO_DATA_FOUND THEN
3729: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_LN_INV');
3730: FND_MESSAGE.Set_Token('OSP_LINE_ID',p_x_osp_order_lines_tbl(i).osp_order_line_id);
3731: FND_MSG_PUB.ADD;
3732: RAISE FND_API.G_EXC_ERROR;
3733: END;
3734: -- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
3748: delete_cancel_so(
3749: p_oe_header_id => NULL,
3750: p_del_cancel_so_lines_tbl => l_del_cancel_so_lines_tbl);
3751: ELSE
3752: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INVOP');
3753: FND_MSG_PUB.ADD;
3754: RAISE FND_API.G_EXC_ERROR;
3755: END IF;
3756: --AHL_OSP_ORDER_LINES_PKG.delete_row(p_x_osp_order_lines_tbl(i).osp_order_line_id);
3783: FROM ahl_osp_orders_b
3784: WHERE osp_order_id = p_x_osp_order_rec.osp_order_id;
3785: EXCEPTION
3786: WHEN NO_DATA_FOUND THEN
3787: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORD_INVALID');
3788: FND_MSG_PUB.add;
3789: RAISE FND_API.G_EXC_ERROR;
3790: END;
3791: IF l_any_vendor_flag <> 'Y' THEN
3801: END IF;
3802: END LOOP;
3803: END IF;
3804: IF NOT l_validate_pass_flag THEN
3805: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ITEM_VENDOR_MISMATCH');
3806: FND_MSG_PUB.add;
3807: RAISE FND_API.G_EXC_ERROR;
3808: END IF;
3809: ELSE
3806: FND_MSG_PUB.add;
3807: RAISE FND_API.G_EXC_ERROR;
3808: END IF;
3809: ELSE
3810: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ITEM_VENDOR_MISMATCH');
3811: FND_MSG_PUB.add;
3812: RAISE FND_API.G_EXC_ERROR;
3813: END IF;
3814: END IF;
3974: l_osp_order_number := l_osp_order_id;
3975: --Derive operating_unit_id
3976: l_operating_unit_id :=mo_global.get_current_org_id();
3977: IF (l_operating_unit_id IS NULL) THEN
3978: FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_ORG_NOT_SET');
3979: FND_MSG_PUB.ADD;
3980: RAISE FND_API.G_EXC_ERROR;
3981: END IF;
3982: --Validate the only user passed parameter order_type_code
3983: IF (p_x_osp_order_rec.order_type_code IS NULL OR
3984: p_x_osp_order_rec.order_type_code NOT IN (G_OSP_ORDER_TYPE_SERVICE,
3985: G_OSP_ORDER_TYPE_EXCHANGE,
3986: G_OSP_ORDER_TYPE_BORROW)) THEN
3987: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORDER_TYPE_INVALID');
3988: FND_MSG_PUB.add;
3989: RAISE FND_API.G_EXC_ERROR;
3990: END IF;
3991:
4084: l_item_service_rels_tbl(i).inv_item_id := l_inventory_item_id;
4085: l_item_service_rels_tbl(i).service_item_id := l_service_item_id;
4086: EXCEPTION
4087: WHEN NO_DATA_FOUND THEN
4088: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_WO');
4089: FND_MESSAGE.Set_Token('WORKORDER_ID', p_x_osp_order_lines_tbl(i).workorder_id);
4090: FND_MSG_PUB.ADD;
4091: RAISE FND_API.G_EXC_ERROR;
4092: END;
4085: l_item_service_rels_tbl(i).service_item_id := l_service_item_id;
4086: EXCEPTION
4087: WHEN NO_DATA_FOUND THEN
4088: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_WO');
4089: FND_MESSAGE.Set_Token('WORKORDER_ID', p_x_osp_order_lines_tbl(i).workorder_id);
4090: FND_MSG_PUB.ADD;
4091: RAISE FND_API.G_EXC_ERROR;
4092: END;
4093: END LOOP;
4113: END LOOP;
4114: END IF;
4115: IF (l_vendor_valid_flag = FALSE) THEN
4116: -- The vendor or vendor + vendor site passed in is not a valid: Throw Error
4117: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
4118: FND_MESSAGE.Set_Token('VENDOR_ID', p_x_osp_order_rec.vendor_id);
4119: FND_MSG_PUB.ADD;
4120: RAISE FND_API.G_EXC_ERROR;
4121: END IF;
4114: END IF;
4115: IF (l_vendor_valid_flag = FALSE) THEN
4116: -- The vendor or vendor + vendor site passed in is not a valid: Throw Error
4117: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
4118: FND_MESSAGE.Set_Token('VENDOR_ID', p_x_osp_order_rec.vendor_id);
4119: FND_MSG_PUB.ADD;
4120: RAISE FND_API.G_EXC_ERROR;
4121: END IF;
4122: IF (p_x_osp_order_rec.vendor_site_id IS NOT NULL) THEN
4532: WHERE inventory_item_id = p_x_osp_order_line_rec.service_item_id
4533: AND organization_id = p_x_osp_order_line_rec.inventory_org_id;
4534: EXCEPTION
4535: WHEN NO_DATA_FOUND THEN
4536: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM');
4537: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_x_osp_order_line_rec.service_item_id);
4538: FND_MSG_PUB.ADD;
4539: END;
4540:
4533: AND organization_id = p_x_osp_order_line_rec.inventory_org_id;
4534: EXCEPTION
4535: WHEN NO_DATA_FOUND THEN
4536: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM');
4537: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_x_osp_order_line_rec.service_item_id);
4538: FND_MSG_PUB.ADD;
4539: END;
4540:
4541: -- yazhou 06-Jul-2006 starts
4559: l_serial_prefix := FND_PROFILE.VALUE('AHL_OSP_POL_SER_PREFIX');
4560:
4561: IF(l_owrite_svc_desc_prf = 'Y') THEN --Overwrite Svc Description profile set to Yes
4562: IF(NVL(l_desc_update_flag, 'N') = 'N') THEN --Allow Description update set to No
4563: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_SVC_DESC_NOCHNG');
4564: FND_MESSAGE.Set_Token('SERVICE_ITEM_NUMBER', l_svc_item_number);
4565: FND_MSG_PUB.ADD;
4566: ELSE
4567: BEGIN
4560:
4561: IF(l_owrite_svc_desc_prf = 'Y') THEN --Overwrite Svc Description profile set to Yes
4562: IF(NVL(l_desc_update_flag, 'N') = 'N') THEN --Allow Description update set to No
4563: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_SVC_DESC_NOCHNG');
4564: FND_MESSAGE.Set_Token('SERVICE_ITEM_NUMBER', l_svc_item_number);
4565: FND_MSG_PUB.ADD;
4566: ELSE
4567: BEGIN
4568: SELECT concatenated_segments INTO l_inv_item_number
4570: WHERE inventory_item_id = p_x_osp_order_line_rec.inventory_item_id
4571: AND organization_id = p_x_osp_order_line_rec.inventory_org_id;
4572: EXCEPTION
4573: WHEN NO_DATA_FOUND THEN
4574: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PHY_ITEM_INVALID');
4575: FND_MSG_PUB.ADD;
4576: END;
4577: l_item_description :=
4578: SUBSTR(l_item_prefix||l_inv_item_number||' '||l_serial_prefix||p_x_osp_order_line_rec.serial_number,1,240);
4835: FROM ahl_osp_orders_b
4836: WHERE osp_order_id = l_osp_order_id;
4837: EXCEPTION
4838: WHEN NO_DATA_FOUND THEN
4839: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INVALID');
4840: FND_MSG_PUB.ADD;
4841: RAISE FND_API.G_EXC_ERROR;
4842: END;
4843: l_oe_header_rec.header_id := l_oe_header_id;
5171: -- yazhou 28-jul-2006 ends
5172:
5173: EXCEPTION
5174: WHEN NO_DATA_FOUND THEN
5175: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORD_INVALID');
5176: FND_MSG_PUB.add;
5177: END;
5178:
5179: -- Added by jaramana on January 10, 2008 for the Requisition ER 6034236 (partial fix for the Bug 5358438/5967633/5417460)
5182: --we would have derived the workorder_id
5183: IF p_x_osp_order_line_rec.po_line_id is not null and p_x_osp_order_line_rec.workorder_id is not null THEN
5184: validate_po_line(p_x_osp_order_line_rec.po_line_id, p_x_osp_order_line_rec.osp_order_id);
5185: ELSIF (p_x_osp_order_line_rec.po_line_id is not null and p_x_osp_order_line_rec.workorder_id is null) THEN
5186: FND_MESSAGE.set_name('AHL', 'AHL_OSP_WO_NLL_CR_POL');
5187: FND_MSG_PUB.add;
5188: END IF;
5189: -- jaramna End
5190:
5191: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5192: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_LOG_PREFIX || '.validate_order_line_creation', 'item='||p_x_osp_order_line_rec.inventory_item_id||'org='||p_x_osp_order_line_rec.inventory_org_id);
5193: END IF;
5194: IF (p_x_osp_order_line_rec.workorder_id IS NULL AND p_x_osp_order_line_rec.inventory_item_id IS NULL) THEN
5195: FND_MESSAGE.set_name('AHL', 'AHL_OSP_WO_ITEM_ALL_NULL');
5196: FND_MSG_PUB.add;
5197: --Validate workorder_id (borrow the old code)
5198: ELSIF(p_x_osp_order_line_rec.workorder_id IS NOT NULL) THEN
5199: validate_workorder(p_x_osp_order_line_rec.workorder_id);
5199: validate_workorder(p_x_osp_order_line_rec.workorder_id);
5200: --Validate physical item
5201: ELSIF (p_x_osp_order_line_rec.inventory_item_id IS NOT NULL) THEN
5202: IF (p_x_osp_order_line_rec.inventory_org_id IS NULL) THEN
5203: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ITEM_ORG_NULL');
5204: FND_MSG_PUB.add;
5205: END IF;
5206: OPEN check_physical_item(p_x_osp_order_line_rec.inventory_item_id,
5207: p_x_osp_order_line_rec.inventory_org_id);
5209: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5210: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_LOG_PREFIX || '.validate_order_line_creation', 'track_flag='||l_check_physical_item.comms_nl_trackable_flag);
5211: END IF;
5212: IF check_physical_item%NOTFOUND THEN
5213: FND_MESSAGE.set_name('AHL', 'AHL_OSP_PHY_ITEM_INVALID');
5214: FND_MSG_PUB.add;
5215: ELSE
5216: IF (p_x_osp_order_line_rec.sub_inventory IS NOT NULL) THEN
5217: OPEN check_sub_inventory(p_x_osp_order_line_rec.inventory_org_id,
5217: OPEN check_sub_inventory(p_x_osp_order_line_rec.inventory_org_id,
5218: p_x_osp_order_line_rec.sub_inventory);
5219: FETCH check_sub_inventory INTO g_dummy_char;
5220: IF check_sub_inventory%NOTFOUND THEN
5221: FND_MESSAGE.set_name('AHL', 'AHL_OSP_SUB_INV_INVALID');
5222: FND_MSG_PUB.add;
5223: END IF;
5224: CLOSE check_sub_inventory;
5225: END IF;
5226:
5227: --Non tracked physical item's quantity (to be serviced) is manadatory
5228: IF (nvl(l_check_physical_item.comms_nl_trackable_flag, 'N')='N' AND
5229: (p_x_osp_order_line_rec.inventory_item_quantity IS NULL)) THEN
5230: FND_MESSAGE.set_name('AHL', 'AHL_OSP_PHY_ITEM_QTY_REQUIRED');
5231: FND_MSG_PUB.add;
5232: END IF;
5233:
5234: /*
5239: OPEN check_serial_number(p_x_osp_order_line_rec.inventory_item_id,
5240: p_x_osp_order_line_rec.serial_number);
5241: FETCH check_serial_number INTO g_dummy_char;
5242: IF check_serial_number%NOTFOUND THEN
5243: FND_MESSAGE.set_name('AHL', 'AHL_OSP_PHY_ITEM_SN_INVALID');
5244: FND_MSG_PUB.add;
5245: ELSIF p_x_osp_order_line_rec.inventory_item_quantity <> 1 THEN
5246: FND_MESSAGE.set_name('AHL', 'AHL_OSP_PHY_ITEM_QT_INVALID');
5247: FND_MSG_PUB.add;
5242: IF check_serial_number%NOTFOUND THEN
5243: FND_MESSAGE.set_name('AHL', 'AHL_OSP_PHY_ITEM_SN_INVALID');
5244: FND_MSG_PUB.add;
5245: ELSIF p_x_osp_order_line_rec.inventory_item_quantity <> 1 THEN
5246: FND_MESSAGE.set_name('AHL', 'AHL_OSP_PHY_ITEM_QT_INVALID');
5247: FND_MSG_PUB.add;
5248: END IF;
5249: CLOSE check_serial_number;
5250: ELSIF l_check_physical_item.lot_control_code = 2 THEN
5252: p_x_osp_order_line_rec.inventory_org_id,
5253: p_x_osp_order_line_rec.lot_number);
5254: FETCH check_lot_number INTO g_dummy_char;
5255: IF check_lot_number%NOTFOUND THEN
5256: FND_MESSAGE.set_name('AHL', 'AHL_OSP_PHY_ITEM_LOT_INVALID');
5257: FND_MSG_PUB.add;
5258: END IF;
5259: CLOSE check_lot_number;
5260: --Validate the onhand quantity with the quantity to be serviced
5266: --Added by mpothuku on 23rd Aug, 06 to fix the Bug 5252627
5267: p_x_osp_order_line_rec.lot_number);
5268: FETCH get_onhand_quantity INTO l_onhand_quantity, l_uom_code;
5269: IF get_onhand_quantity%NOTFOUND THEN
5270: FND_MESSAGE.set_name('AHL', 'AHL_OSP_PHY_ITEM_INVALID');
5271: FND_MSG_PUB.add;
5272: END IF;
5273: CLOSE get_onhand_quantity;
5274: IF (l_onhand_quantity IS NOT NULL AND l_uom_code IS NOT NULL) THEN
5284: to_unit => l_uom_code,
5285: from_name => NULL,
5286: to_name => NULL);
5287: IF (l_quantity > l_onhand_quantity) THEN
5288: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ITEM_ONHAND_LESS_SERV');
5289: FND_MSG_PUB.add;
5290: END IF;
5291: END IF;
5292: END IF;
5356: END IF;
5357:
5358:
5359: IF (l_po_line_attrs.item_id is null and l_po_line_attrs.item_description is null) THEN
5360: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_ITEM_ID_DESC_NLL');
5361: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5362: FND_MSG_PUB.ADD;
5363: END IF;
5364:
5357:
5358:
5359: IF (l_po_line_attrs.item_id is null and l_po_line_attrs.item_description is null) THEN
5360: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_ITEM_ID_DESC_NLL');
5361: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5362: FND_MSG_PUB.ADD;
5363: END IF;
5364:
5365: IF (l_po_line_attrs.line_type_id is null) THEN
5362: FND_MSG_PUB.ADD;
5363: END IF;
5364:
5365: IF (l_po_line_attrs.line_type_id is null) THEN
5366: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_TYPE_NLL');
5367: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5368: FND_MSG_PUB.ADD;
5369: END IF;
5370:
5363: END IF;
5364:
5365: IF (l_po_line_attrs.line_type_id is null) THEN
5366: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_TYPE_NLL');
5367: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5368: FND_MSG_PUB.ADD;
5369: END IF;
5370:
5371: IF (l_po_line_attrs.uom_code is null) THEN
5368: FND_MSG_PUB.ADD;
5369: END IF;
5370:
5371: IF (l_po_line_attrs.uom_code is null) THEN
5372: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_UOM_NLL');
5373: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5374: FND_MSG_PUB.ADD;
5375: END IF;
5376:
5369: END IF;
5370:
5371: IF (l_po_line_attrs.uom_code is null) THEN
5372: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_UOM_NLL');
5373: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5374: FND_MSG_PUB.ADD;
5375: END IF;
5376:
5377: IF (l_po_line_attrs.quantity is null) THEN
5374: FND_MSG_PUB.ADD;
5375: END IF;
5376:
5377: IF (l_po_line_attrs.quantity is null) THEN
5378: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_QUANT_NLL');
5379: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5380: FND_MSG_PUB.ADD;
5381: END IF;
5382:
5375: END IF;
5376:
5377: IF (l_po_line_attrs.quantity is null) THEN
5378: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_QUANT_NLL');
5379: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5380: FND_MSG_PUB.ADD;
5381: END IF;
5382:
5383: IF (l_po_line_attrs.need_by_date is null) THEN
5380: FND_MSG_PUB.ADD;
5381: END IF;
5382:
5383: IF (l_po_line_attrs.need_by_date is null) THEN
5384: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_NBDATE_NLL');
5385: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5386: FND_MSG_PUB.ADD;
5387: END IF;
5388:
5381: END IF;
5382:
5383: IF (l_po_line_attrs.need_by_date is null) THEN
5384: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_NBDATE_NLL');
5385: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5386: FND_MSG_PUB.ADD;
5387: END IF;
5388:
5389: END IF;
5397: */
5398: val_svc_item_vs_wo_svc_item (p_x_osp_order_line_rec.workorder_id, l_po_line_attrs.item_id);
5399: validate_service_item_uom(l_po_line_attrs.item_id, p_x_osp_order_line_rec.service_item_uom_code, p_x_osp_order_line_rec.inventory_org_id);
5400: IF(l_po_line_attrs.quantity <=0) THEN
5401: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_VAL');
5402: FND_MESSAGE.Set_Token('QUANTITY', p_x_osp_order_line_rec.quantity);
5403: FND_MSG_PUB.ADD;
5404: END IF;
5405: validate_po_line_type(l_po_line_attrs.line_type_id);
5398: val_svc_item_vs_wo_svc_item (p_x_osp_order_line_rec.workorder_id, l_po_line_attrs.item_id);
5399: validate_service_item_uom(l_po_line_attrs.item_id, p_x_osp_order_line_rec.service_item_uom_code, p_x_osp_order_line_rec.inventory_org_id);
5400: IF(l_po_line_attrs.quantity <=0) THEN
5401: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_VAL');
5402: FND_MESSAGE.Set_Token('QUANTITY', p_x_osp_order_line_rec.quantity);
5403: FND_MSG_PUB.ADD;
5404: END IF;
5405: validate_po_line_type(l_po_line_attrs.line_type_id);
5406: --Assign the attributes from the po line to the osp line
5428: validate_service_item_uom(p_x_osp_order_line_rec.service_item_id, p_x_osp_order_line_rec.service_item_uom_code, p_x_osp_order_line_rec.inventory_org_id);
5429: --validate sercice_item_quantity(quantity)
5430: IF (p_x_osp_order_line_rec.quantity IS NOT NULL AND p_x_osp_order_line_rec.quantity <> FND_API.G_MISS_NUM) THEN
5431: IF(p_x_osp_order_line_rec.quantity <=0) THEN
5432: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_VAL');
5433: FND_MESSAGE.Set_Token('QUANTITY', p_x_osp_order_line_rec.quantity);
5434: FND_MSG_PUB.ADD;
5435: END IF;
5436: --quantity cannot be null when UOM is not null
5429: --validate sercice_item_quantity(quantity)
5430: IF (p_x_osp_order_line_rec.quantity IS NOT NULL AND p_x_osp_order_line_rec.quantity <> FND_API.G_MISS_NUM) THEN
5431: IF(p_x_osp_order_line_rec.quantity <=0) THEN
5432: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_VAL');
5433: FND_MESSAGE.Set_Token('QUANTITY', p_x_osp_order_line_rec.quantity);
5434: FND_MSG_PUB.ADD;
5435: END IF;
5436: --quantity cannot be null when UOM is not null
5437: ELSIF (p_x_osp_order_line_rec.service_item_uom_code IS NOT NULL AND p_x_osp_order_line_rec.service_item_uom_code <> FND_API.G_MISS_CHAR) THEN
5434: FND_MSG_PUB.ADD;
5435: END IF;
5436: --quantity cannot be null when UOM is not null
5437: ELSIF (p_x_osp_order_line_rec.service_item_uom_code IS NOT NULL AND p_x_osp_order_line_rec.service_item_uom_code <> FND_API.G_MISS_CHAR) THEN
5438: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_NLL');
5439: FND_MSG_PUB.ADD;
5440: END IF;
5441:
5442: --Validate header vendor with physical, service item combination in line
5465:
5466: FETCH check_osp_order_unique INTO g_dummy_char;
5467:
5468: IF check_osp_order_unique%FOUND THEN
5469: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PHY_ITEM_UNIUE');
5470: FND_MSG_PUB.ADD;
5471: END IF;
5472: CLOSE check_osp_order_unique;
5473: END IF;
5480: OPEN check_phy_item_unique(p_x_osp_order_line_rec.osp_order_id,
5481: p_x_osp_order_line_rec.inventory_item_id);
5482: FETCH check_phy_item_unique INTO g_dummy_char;
5483: IF check_phy_item_unique%FOUND THEN
5484: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_PHY_ITEM_UNIUE');
5485: FND_MSG_PUB.ADD;
5486: END IF;
5487: CLOSE check_phy_item_unique;
5488: --ELSIF (p_x_osp_order_line_rec.service_item_id IS NOT NULL) THEN
5495: p_x_osp_order_line_rec.lot_number,
5496: p_x_osp_order_line_rec.serial_number);
5497: FETCH check_phy_ser_item_unique INTO g_dummy_char;
5498: IF check_phy_ser_item_unique%FOUND THEN
5499: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_PHY_SER_ITEM_UNIUE');
5500: FND_MSG_PUB.ADD;
5501: END IF;
5502: CLOSE check_phy_ser_item_unique;
5503: END IF;
5629: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5630: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Begin Procedure');
5631: END IF;
5632: IF(p_x_osp_order_line_rec.osp_order_line_id IS NULL OR p_x_osp_order_line_rec.object_version_number IS NULL) THEN
5633: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_ID_OBJ_MISS');
5634: FND_MSG_PUB.ADD;
5635: ELSE
5636: OPEN osp_order_line_csr;
5637: FETCH osp_order_line_csr INTO l_osp_order_line_rec;
5635: ELSE
5636: OPEN osp_order_line_csr;
5637: FETCH osp_order_line_csr INTO l_osp_order_line_rec;
5638: IF (osp_order_line_csr%NOTFOUND) THEN
5639: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INVOP_OSP_LN_NFOUND');
5640: FND_MSG_PUB.ADD;
5641: ELSE
5642: --The following attributes can't be changed once created
5643: --osp_order_id can't be changed, and osp_order_id is required
5642: --The following attributes can't be changed once created
5643: --osp_order_id can't be changed, and osp_order_id is required
5644: IF(p_x_osp_order_line_rec.osp_order_id IS NULL OR
5645: p_x_osp_order_line_rec.osp_order_id <> l_osp_order_line_rec.osp_order_id) THEN
5646: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_ORD_ID_CHG');
5647: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5648: FND_MSG_PUB.ADD;
5649: END IF;
5650: --osp_order_number can't be changed and osp_order_number is required
5643: --osp_order_id can't be changed, and osp_order_id is required
5644: IF(p_x_osp_order_line_rec.osp_order_id IS NULL OR
5645: p_x_osp_order_line_rec.osp_order_id <> l_osp_order_line_rec.osp_order_id) THEN
5646: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_ORD_ID_CHG');
5647: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5648: FND_MSG_PUB.ADD;
5649: END IF;
5650: --osp_order_number can't be changed and osp_order_number is required
5651: IF(p_x_osp_order_line_rec.osp_line_number IS NULL OR
5649: END IF;
5650: --osp_order_number can't be changed and osp_order_number is required
5651: IF(p_x_osp_order_line_rec.osp_line_number IS NULL OR
5652: p_x_osp_order_line_rec.osp_line_number <> l_osp_order_line_rec.osp_line_number) THEN
5653: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_LN_NUM_CHG');
5654: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5655: FND_MSG_PUB.ADD;
5656: END IF;
5657: --workorder_id can't be changed but workorder can be null
5650: --osp_order_number can't be changed and osp_order_number is required
5651: IF(p_x_osp_order_line_rec.osp_line_number IS NULL OR
5652: p_x_osp_order_line_rec.osp_line_number <> l_osp_order_line_rec.osp_line_number) THEN
5653: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_LN_NUM_CHG');
5654: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5655: FND_MSG_PUB.ADD;
5656: END IF;
5657: --workorder_id can't be changed but workorder can be null
5658: IF((p_x_osp_order_line_rec.workorder_id <> l_osp_order_line_rec.workorder_id) OR
5657: --workorder_id can't be changed but workorder can be null
5658: IF((p_x_osp_order_line_rec.workorder_id <> l_osp_order_line_rec.workorder_id) OR
5659: (p_x_osp_order_line_rec.workorder_id IS NOT NULL AND l_osp_order_line_rec.workorder_id IS NULL) OR
5660: (p_x_osp_order_line_rec.workorder_id IS NULL AND l_osp_order_line_rec.workorder_id IS NOT NULL)) THEN
5661: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_WO_CHG');
5662: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5663: FND_MSG_PUB.ADD;
5664: END IF;
5665: --inventory_item_id can't be changed and inventory_item_id is always populated
5658: IF((p_x_osp_order_line_rec.workorder_id <> l_osp_order_line_rec.workorder_id) OR
5659: (p_x_osp_order_line_rec.workorder_id IS NOT NULL AND l_osp_order_line_rec.workorder_id IS NULL) OR
5660: (p_x_osp_order_line_rec.workorder_id IS NULL AND l_osp_order_line_rec.workorder_id IS NOT NULL)) THEN
5661: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_WO_CHG');
5662: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5663: FND_MSG_PUB.ADD;
5664: END IF;
5665: --inventory_item_id can't be changed and inventory_item_id is always populated
5666: IF(p_x_osp_order_line_rec.inventory_item_id IS NULL OR
5664: END IF;
5665: --inventory_item_id can't be changed and inventory_item_id is always populated
5666: IF(p_x_osp_order_line_rec.inventory_item_id IS NULL OR
5667: p_x_osp_order_line_rec.inventory_item_id <> l_osp_order_line_rec.inventory_item_id) THEN
5668: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_ITEM_CHG');
5669: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5670: FND_MSG_PUB.ADD;
5671: END IF;
5672: --inventory_org_id can't be changed and inventory_org_id is always popluated
5665: --inventory_item_id can't be changed and inventory_item_id is always populated
5666: IF(p_x_osp_order_line_rec.inventory_item_id IS NULL OR
5667: p_x_osp_order_line_rec.inventory_item_id <> l_osp_order_line_rec.inventory_item_id) THEN
5668: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_ITEM_CHG');
5669: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5670: FND_MSG_PUB.ADD;
5671: END IF;
5672: --inventory_org_id can't be changed and inventory_org_id is always popluated
5673: IF(p_x_osp_order_line_rec.inventory_org_id IS NULL OR
5671: END IF;
5672: --inventory_org_id can't be changed and inventory_org_id is always popluated
5673: IF(p_x_osp_order_line_rec.inventory_org_id IS NULL OR
5674: p_x_osp_order_line_rec.inventory_org_id <> l_osp_order_line_rec.inventory_org_id) THEN
5675: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_ORG_CHG');
5676: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5677: FND_MSG_PUB.ADD;
5678: END IF;
5679: --inventory_item_uom can't be changed and inventory_item_uom is always populated
5672: --inventory_org_id can't be changed and inventory_org_id is always popluated
5673: IF(p_x_osp_order_line_rec.inventory_org_id IS NULL OR
5674: p_x_osp_order_line_rec.inventory_org_id <> l_osp_order_line_rec.inventory_org_id) THEN
5675: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_ORG_CHG');
5676: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5677: FND_MSG_PUB.ADD;
5678: END IF;
5679: --inventory_item_uom can't be changed and inventory_item_uom is always populated
5680: IF(p_x_osp_order_line_rec.inventory_item_uom IS NULL OR
5678: END IF;
5679: --inventory_item_uom can't be changed and inventory_item_uom is always populated
5680: IF(p_x_osp_order_line_rec.inventory_item_uom IS NULL OR
5681: p_x_osp_order_line_rec.inventory_item_uom <> l_osp_order_line_rec.inventory_item_uom) THEN
5682: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_UOM_CHG');
5683: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5684: FND_MSG_PUB.ADD;
5685: END IF;
5686: --inventory_item_quantity can't be changed and inventory_item_quantity is always populated
5679: --inventory_item_uom can't be changed and inventory_item_uom is always populated
5680: IF(p_x_osp_order_line_rec.inventory_item_uom IS NULL OR
5681: p_x_osp_order_line_rec.inventory_item_uom <> l_osp_order_line_rec.inventory_item_uom) THEN
5682: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_UOM_CHG');
5683: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5684: FND_MSG_PUB.ADD;
5685: END IF;
5686: --inventory_item_quantity can't be changed and inventory_item_quantity is always populated
5687: IF(p_x_osp_order_line_rec.inventory_item_quantity IS NULL OR
5685: END IF;
5686: --inventory_item_quantity can't be changed and inventory_item_quantity is always populated
5687: IF(p_x_osp_order_line_rec.inventory_item_quantity IS NULL OR
5688: p_x_osp_order_line_rec.inventory_item_quantity <> l_osp_order_line_rec.inventory_item_quantity) THEN
5689: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QTY_CHG');
5690: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5691: FND_MSG_PUB.ADD;
5692: END IF;
5693: --sub_inventory can't be changed but sub_inventory can be null
5686: --inventory_item_quantity can't be changed and inventory_item_quantity is always populated
5687: IF(p_x_osp_order_line_rec.inventory_item_quantity IS NULL OR
5688: p_x_osp_order_line_rec.inventory_item_quantity <> l_osp_order_line_rec.inventory_item_quantity) THEN
5689: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QTY_CHG');
5690: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5691: FND_MSG_PUB.ADD;
5692: END IF;
5693: --sub_inventory can't be changed but sub_inventory can be null
5694: IF((p_x_osp_order_line_rec.sub_inventory <> l_osp_order_line_rec.sub_inventory) OR
5693: --sub_inventory can't be changed but sub_inventory can be null
5694: IF((p_x_osp_order_line_rec.sub_inventory <> l_osp_order_line_rec.sub_inventory) OR
5695: (p_x_osp_order_line_rec.sub_inventory IS NOT NULL AND l_osp_order_line_rec.sub_inventory IS NULL) OR
5696: (p_x_osp_order_line_rec.sub_inventory IS NULL AND l_osp_order_line_rec.sub_inventory IS NOT NULL)) THEN
5697: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SUB_CHG');
5698: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5699: FND_MSG_PUB.ADD;
5700: END IF;
5701: --lot_number can't be changed but lot_number can be null
5694: IF((p_x_osp_order_line_rec.sub_inventory <> l_osp_order_line_rec.sub_inventory) OR
5695: (p_x_osp_order_line_rec.sub_inventory IS NOT NULL AND l_osp_order_line_rec.sub_inventory IS NULL) OR
5696: (p_x_osp_order_line_rec.sub_inventory IS NULL AND l_osp_order_line_rec.sub_inventory IS NOT NULL)) THEN
5697: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SUB_CHG');
5698: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5699: FND_MSG_PUB.ADD;
5700: END IF;
5701: --lot_number can't be changed but lot_number can be null
5702: IF((p_x_osp_order_line_rec.lot_number <> l_osp_order_line_rec.lot_number) OR
5701: --lot_number can't be changed but lot_number can be null
5702: IF((p_x_osp_order_line_rec.lot_number <> l_osp_order_line_rec.lot_number) OR
5703: (p_x_osp_order_line_rec.lot_number IS NOT NULL AND l_osp_order_line_rec.lot_number IS NULL) OR
5704: (p_x_osp_order_line_rec.lot_number IS NULL AND l_osp_order_line_rec.lot_number IS NOT NULL)) THEN
5705: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_LOT_CHG');
5706: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5707: FND_MSG_PUB.ADD;
5708: END IF;
5709: --serial_number can't be changed but serial_number can be null
5702: IF((p_x_osp_order_line_rec.lot_number <> l_osp_order_line_rec.lot_number) OR
5703: (p_x_osp_order_line_rec.lot_number IS NOT NULL AND l_osp_order_line_rec.lot_number IS NULL) OR
5704: (p_x_osp_order_line_rec.lot_number IS NULL AND l_osp_order_line_rec.lot_number IS NOT NULL)) THEN
5705: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_LOT_CHG');
5706: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5707: FND_MSG_PUB.ADD;
5708: END IF;
5709: --serial_number can't be changed but serial_number can be null
5710: IF((p_x_osp_order_line_rec.serial_number <> l_osp_order_line_rec.serial_number) OR
5709: --serial_number can't be changed but serial_number can be null
5710: IF((p_x_osp_order_line_rec.serial_number <> l_osp_order_line_rec.serial_number) OR
5711: (p_x_osp_order_line_rec.serial_number IS NOT NULL AND l_osp_order_line_rec.serial_number IS NULL) OR
5712: (p_x_osp_order_line_rec.serial_number IS NULL AND l_osp_order_line_rec.serial_number IS NOT NULL)) THEN
5713: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SERIAL_CHG');
5714: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5715: FND_MSG_PUB.ADD;
5716: END IF;
5717: -- this API does not allow to update SO line information, Shipment API needs to update
5710: IF((p_x_osp_order_line_rec.serial_number <> l_osp_order_line_rec.serial_number) OR
5711: (p_x_osp_order_line_rec.serial_number IS NOT NULL AND l_osp_order_line_rec.serial_number IS NULL) OR
5712: (p_x_osp_order_line_rec.serial_number IS NULL AND l_osp_order_line_rec.serial_number IS NOT NULL)) THEN
5713: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SERIAL_CHG');
5714: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5715: FND_MSG_PUB.ADD;
5716: END IF;
5717: -- this API does not allow to update SO line information, Shipment API needs to update
5718: -- OSP tables directly (is this the best approach?)
5719: --oe_ship_line_id can't be changed from this API, and it could be null.
5720: IF((p_x_osp_order_line_rec.oe_ship_line_id <> l_osp_order_line_rec.oe_ship_line_id) OR
5721: (p_x_osp_order_line_rec.oe_ship_line_id IS NOT NULL AND l_osp_order_line_rec.oe_ship_line_id IS NULL) OR
5722: (p_x_osp_order_line_rec.oe_ship_line_id IS NULL AND l_osp_order_line_rec.oe_ship_line_id IS NOT NULL)) THEN
5723: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SHIP_ID_CHG');
5724: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5725: FND_MSG_PUB.ADD;
5726: END IF;
5727: --oe_return_line_id can't be changed from this API, and it could be null.
5720: IF((p_x_osp_order_line_rec.oe_ship_line_id <> l_osp_order_line_rec.oe_ship_line_id) OR
5721: (p_x_osp_order_line_rec.oe_ship_line_id IS NOT NULL AND l_osp_order_line_rec.oe_ship_line_id IS NULL) OR
5722: (p_x_osp_order_line_rec.oe_ship_line_id IS NULL AND l_osp_order_line_rec.oe_ship_line_id IS NOT NULL)) THEN
5723: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SHIP_ID_CHG');
5724: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5725: FND_MSG_PUB.ADD;
5726: END IF;
5727: --oe_return_line_id can't be changed from this API, and it could be null.
5728: IF((p_x_osp_order_line_rec.oe_return_line_id <> l_osp_order_line_rec.oe_return_line_id) OR
5727: --oe_return_line_id can't be changed from this API, and it could be null.
5728: IF((p_x_osp_order_line_rec.oe_return_line_id <> l_osp_order_line_rec.oe_return_line_id) OR
5729: (p_x_osp_order_line_rec.oe_return_line_id IS NOT NULL AND l_osp_order_line_rec.oe_return_line_id IS NULL) OR
5730: (p_x_osp_order_line_rec.oe_return_line_id IS NULL AND l_osp_order_line_rec.oe_return_line_id IS NOT NULL)) THEN
5731: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_RET_ID_CHG');
5732: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5733: FND_MSG_PUB.ADD;
5734: END IF;
5735: -- this API does not allow to update PO line information, PO API needs to update
5728: IF((p_x_osp_order_line_rec.oe_return_line_id <> l_osp_order_line_rec.oe_return_line_id) OR
5729: (p_x_osp_order_line_rec.oe_return_line_id IS NOT NULL AND l_osp_order_line_rec.oe_return_line_id IS NULL) OR
5730: (p_x_osp_order_line_rec.oe_return_line_id IS NULL AND l_osp_order_line_rec.oe_return_line_id IS NOT NULL)) THEN
5731: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_RET_ID_CHG');
5732: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5733: FND_MSG_PUB.ADD;
5734: END IF;
5735: -- this API does not allow to update PO line information, PO API needs to update
5736: -- OSP tables directly (is this the best approach?)
5737: --po_line_id can't be changed from this API, and it could be null.
5738: IF((p_x_osp_order_line_rec.po_line_id <> l_osp_order_line_rec.po_line_id) OR
5739: (p_x_osp_order_line_rec.po_line_id IS NOT NULL AND l_osp_order_line_rec.po_line_id IS NULL) OR
5740: (p_x_osp_order_line_rec.po_line_id IS NULL AND l_osp_order_line_rec.po_line_id IS NOT NULL)) THEN
5741: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_POLINE_ID_CHG');
5742: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5743: FND_MSG_PUB.ADD;
5744: END IF;
5745:
5738: IF((p_x_osp_order_line_rec.po_line_id <> l_osp_order_line_rec.po_line_id) OR
5739: (p_x_osp_order_line_rec.po_line_id IS NOT NULL AND l_osp_order_line_rec.po_line_id IS NULL) OR
5740: (p_x_osp_order_line_rec.po_line_id IS NULL AND l_osp_order_line_rec.po_line_id IS NOT NULL)) THEN
5741: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_POLINE_ID_CHG');
5742: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5743: FND_MSG_PUB.ADD;
5744: END IF;
5745:
5746: -- Added by jaramana on January 10, 2008 for the Requisition ER 6034236
5748: --po_req_line_id can't be changed from this API, and it could be null.
5749: IF((p_x_osp_order_line_rec.po_req_line_id <> l_osp_order_line_rec.po_req_line_id) OR
5750: (p_x_osp_order_line_rec.po_req_line_id IS NOT NULL AND l_osp_order_line_rec.po_req_line_id IS NULL) OR
5751: (p_x_osp_order_line_rec.po_req_line_id IS NULL AND l_osp_order_line_rec.po_req_line_id IS NOT NULL)) THEN
5752: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_REQLINE_ID_CHG');
5753: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5754: FND_MSG_PUB.ADD;
5755: END IF;
5756:
5749: IF((p_x_osp_order_line_rec.po_req_line_id <> l_osp_order_line_rec.po_req_line_id) OR
5750: (p_x_osp_order_line_rec.po_req_line_id IS NOT NULL AND l_osp_order_line_rec.po_req_line_id IS NULL) OR
5751: (p_x_osp_order_line_rec.po_req_line_id IS NULL AND l_osp_order_line_rec.po_req_line_id IS NOT NULL)) THEN
5752: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_REQLINE_ID_CHG');
5753: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5754: FND_MSG_PUB.ADD;
5755: END IF;
5756:
5757: --po_line_type_id can't be changed from this API, and it could be null.
5757: --po_line_type_id can't be changed from this API, and it could be null.
5758: IF((p_x_osp_order_line_rec.po_line_type_id <> l_osp_order_line_rec.po_line_type_id) OR
5759: (p_x_osp_order_line_rec.po_line_type_id IS NOT NULL AND l_osp_order_line_rec.po_line_type_id IS NULL) OR
5760: (p_x_osp_order_line_rec.po_line_type_id IS NULL AND l_osp_order_line_rec.po_line_type_id IS NOT NULL)) THEN
5761: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_POTYPE_ID_CHG');
5762: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5763: FND_MSG_PUB.ADD;
5764: END IF;
5765:
5758: IF((p_x_osp_order_line_rec.po_line_type_id <> l_osp_order_line_rec.po_line_type_id) OR
5759: (p_x_osp_order_line_rec.po_line_type_id IS NOT NULL AND l_osp_order_line_rec.po_line_type_id IS NULL) OR
5760: (p_x_osp_order_line_rec.po_line_type_id IS NULL AND l_osp_order_line_rec.po_line_type_id IS NOT NULL)) THEN
5761: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_POTYPE_ID_CHG');
5762: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5763: FND_MSG_PUB.ADD;
5764: END IF;
5765:
5766: -- Added by jaramana on January 10, 2008 for the Requisition ER 6034236
5774: --service_item_id can't be changed from this API, and it could be null.
5775: IF((p_x_osp_order_line_rec.service_item_id <> l_osp_order_line_rec.service_item_id) OR
5776: (p_x_osp_order_line_rec.service_item_id IS NOT NULL AND l_osp_order_line_rec.service_item_id IS NULL) OR
5777: (p_x_osp_order_line_rec.service_item_id IS NULL AND l_osp_order_line_rec.service_item_id IS NOT NULL)) THEN
5778: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SER_ID_CHG');
5779: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5780: FND_MSG_PUB.ADD;
5781: END IF;
5782:
5775: IF((p_x_osp_order_line_rec.service_item_id <> l_osp_order_line_rec.service_item_id) OR
5776: (p_x_osp_order_line_rec.service_item_id IS NOT NULL AND l_osp_order_line_rec.service_item_id IS NULL) OR
5777: (p_x_osp_order_line_rec.service_item_id IS NULL AND l_osp_order_line_rec.service_item_id IS NOT NULL)) THEN
5778: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SER_ID_CHG');
5779: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5780: FND_MSG_PUB.ADD;
5781: END IF;
5782:
5783: --service_item_description can't be changed from this API, and it could be null.
5783: --service_item_description can't be changed from this API, and it could be null.
5784: IF((p_x_osp_order_line_rec.service_item_description <> l_osp_order_line_rec.service_item_description) OR
5785: (p_x_osp_order_line_rec.service_item_description IS NOT NULL AND l_osp_order_line_rec.service_item_description IS NULL) OR
5786: (p_x_osp_order_line_rec.service_item_description IS NULL AND l_osp_order_line_rec.service_item_description IS NOT NULL)) THEN
5787: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SER_DESC_CHG');
5788: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5789: FND_MSG_PUB.ADD;
5790: END IF;
5791:
5784: IF((p_x_osp_order_line_rec.service_item_description <> l_osp_order_line_rec.service_item_description) OR
5785: (p_x_osp_order_line_rec.service_item_description IS NOT NULL AND l_osp_order_line_rec.service_item_description IS NULL) OR
5786: (p_x_osp_order_line_rec.service_item_description IS NULL AND l_osp_order_line_rec.service_item_description IS NOT NULL)) THEN
5787: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SER_DESC_CHG');
5788: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5789: FND_MSG_PUB.ADD;
5790: END IF;
5791:
5792: /*
5793: --exchange_instance_id can't be changed from this API, and it could be null.
5794: IF((p_x_osp_order_line_rec.exchange_instance_id <> l_osp_order_line_rec.exchange_instance_id) OR
5795: (p_x_osp_order_line_rec.exchange_instance_id IS NOT NULL AND l_osp_order_line_rec.exchange_instance_id IS NULL) OR
5796: (p_x_osp_order_line_rec.exchange_instance_id IS NULL AND l_osp_order_line_rec.exchange_instance_id IS NOT NULL)) THEN
5797: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_EXCH_ID_CHG');
5798: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5799: FND_MSG_PUB.ADD;
5800: END IF;
5801: */
5794: IF((p_x_osp_order_line_rec.exchange_instance_id <> l_osp_order_line_rec.exchange_instance_id) OR
5795: (p_x_osp_order_line_rec.exchange_instance_id IS NOT NULL AND l_osp_order_line_rec.exchange_instance_id IS NULL) OR
5796: (p_x_osp_order_line_rec.exchange_instance_id IS NULL AND l_osp_order_line_rec.exchange_instance_id IS NOT NULL)) THEN
5797: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_EXCH_ID_CHG');
5798: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5799: FND_MSG_PUB.ADD;
5800: END IF;
5801: */
5802:
5802:
5803: --service_item_uom cannot change and cannot be null
5804: IF(p_x_osp_order_line_rec.service_item_uom_code IS NULL OR
5805: (p_x_osp_order_line_rec.service_item_uom_code <> l_osp_order_line_rec.service_item_uom_code) ) THEN
5806: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SER_UOM_CHG');
5807: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5808: FND_MSG_PUB.ADD;
5809: END IF;
5810:
5803: --service_item_uom cannot change and cannot be null
5804: IF(p_x_osp_order_line_rec.service_item_uom_code IS NULL OR
5805: (p_x_osp_order_line_rec.service_item_uom_code <> l_osp_order_line_rec.service_item_uom_code) ) THEN
5806: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SER_UOM_CHG');
5807: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5808: FND_MSG_PUB.ADD;
5809: END IF;
5810:
5811: --service_item_quantity cannot change and cannot be null
5810:
5811: --service_item_quantity cannot change and cannot be null
5812: IF(p_x_osp_order_line_rec.quantity IS NULL OR
5813: (p_x_osp_order_line_rec.quantity <> l_osp_order_line_rec.quantity) ) THEN
5814: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SER_QTY_CHG');
5815: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5816: FND_MSG_PUB.ADD;
5817: END IF;
5818:
5811: --service_item_quantity cannot change and cannot be null
5812: IF(p_x_osp_order_line_rec.quantity IS NULL OR
5813: (p_x_osp_order_line_rec.quantity <> l_osp_order_line_rec.quantity) ) THEN
5814: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SER_QTY_CHG');
5815: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5816: FND_MSG_PUB.ADD;
5817: END IF;
5818:
5819: --Neeed By Date cannot change and cannot be null
5818:
5819: --Neeed By Date cannot change and cannot be null
5820: IF(p_x_osp_order_line_rec.need_by_date IS NULL OR
5821: (trunc(p_x_osp_order_line_rec.need_by_date) <> trunc(l_osp_order_line_rec.need_by_date) )) THEN
5822: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_NBD_CHG');
5823: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5824: FND_MSG_PUB.ADD;
5825: END IF;
5826: END IF;
5819: --Neeed By Date cannot change and cannot be null
5820: IF(p_x_osp_order_line_rec.need_by_date IS NULL OR
5821: (trunc(p_x_osp_order_line_rec.need_by_date) <> trunc(l_osp_order_line_rec.need_by_date) )) THEN
5822: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_NBD_CHG');
5823: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5824: FND_MSG_PUB.ADD;
5825: END IF;
5826: END IF;
5827: -- jaramana End
5844: WHERE inventory_item_id = p_x_osp_order_line_rec.service_item_id
5845: AND organization_id = p_x_osp_order_line_rec.inventory_org_id;
5846: EXCEPTION
5847: WHEN NO_DATA_FOUND THEN
5848: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM');
5849: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_x_osp_order_line_rec.service_item_id);
5850: FND_MSG_PUB.ADD;
5851: END;
5852: l_owrite_svc_desc_prf := NVL(FND_PROFILE.VALUE('AHL_OSP_OWRITE_SVC_DESC'), 'N');
5845: AND organization_id = p_x_osp_order_line_rec.inventory_org_id;
5846: EXCEPTION
5847: WHEN NO_DATA_FOUND THEN
5848: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM');
5849: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_x_osp_order_line_rec.service_item_id);
5850: FND_MSG_PUB.ADD;
5851: END;
5852: l_owrite_svc_desc_prf := NVL(FND_PROFILE.VALUE('AHL_OSP_OWRITE_SVC_DESC'), 'N');
5853: l_item_prefix := FND_PROFILE.VALUE('AHL_OSP_POL_ITEM_PREFIX');
5858: IF(l_desc_update_flag <> 'Y' AND
5859: ((p_x_osp_order_line_rec.service_item_description <> l_item_description) OR
5860: (p_x_osp_order_line_rec.service_item_description IS NOT NULL AND l_item_description IS NULL) OR
5861: (p_x_osp_order_line_rec.service_item_description IS NULL AND l_item_description IS NOT NULL))) THEN
5862: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVCITMDSC_CHG');
5863: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5864: FND_MSG_PUB.ADD;
5865: END IF;
5866: */
5859: ((p_x_osp_order_line_rec.service_item_description <> l_item_description) OR
5860: (p_x_osp_order_line_rec.service_item_description IS NOT NULL AND l_item_description IS NULL) OR
5861: (p_x_osp_order_line_rec.service_item_description IS NULL AND l_item_description IS NOT NULL))) THEN
5862: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVCITMDSC_CHG');
5863: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5864: FND_MSG_PUB.ADD;
5865: END IF;
5866: */
5867: /*
5871: */
5872:
5873: IF(l_owrite_svc_desc_prf = 'Y') THEN --Overwrite Svc Description profile set to Yes
5874: IF(NVL(l_desc_update_flag, 'N') = 'N') THEN --Allow Description update set to No
5875: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVCITMDSC_CHG');
5876: FND_MESSAGE.Set_Token('SERVICE_ITEM_NUMBER', l_svc_item_number);
5877: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5878: FND_MSG_PUB.ADD;
5879: ELSE
5872:
5873: IF(l_owrite_svc_desc_prf = 'Y') THEN --Overwrite Svc Description profile set to Yes
5874: IF(NVL(l_desc_update_flag, 'N') = 'N') THEN --Allow Description update set to No
5875: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVCITMDSC_CHG');
5876: FND_MESSAGE.Set_Token('SERVICE_ITEM_NUMBER', l_svc_item_number);
5877: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5878: FND_MSG_PUB.ADD;
5879: ELSE
5880: BEGIN
5873: IF(l_owrite_svc_desc_prf = 'Y') THEN --Overwrite Svc Description profile set to Yes
5874: IF(NVL(l_desc_update_flag, 'N') = 'N') THEN --Allow Description update set to No
5875: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVCITMDSC_CHG');
5876: FND_MESSAGE.Set_Token('SERVICE_ITEM_NUMBER', l_svc_item_number);
5877: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5878: FND_MSG_PUB.ADD;
5879: ELSE
5880: BEGIN
5881: SELECT concatenated_segments INTO l_inv_item_number
5883: WHERE inventory_item_id = p_x_osp_order_line_rec.inventory_item_id
5884: AND organization_id = p_x_osp_order_line_rec.inventory_org_id;
5885: EXCEPTION
5886: WHEN NO_DATA_FOUND THEN
5887: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PHY_ITEM_INVALID');
5888: FND_MSG_PUB.ADD;
5889: END;
5890: l_item_description :=
5891: SUBSTR(l_item_prefix||l_inv_item_number||' '||l_serial_prefix||p_x_osp_order_line_rec.serial_number,1,240);
5898: --Enforcing that either service_item_id and description should be not null
5899: --This is only enforced during update. Maybe it is better if delaying this validation
5900: --until submitting the OSP order?
5901: IF (p_x_osp_order_line_rec.service_item_id IS NULL AND p_x_osp_order_line_rec.service_item_description IS NULL) THEN
5902: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_SVCITMDSC_BOTH_NLL');
5903: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5904: FND_MSG_PUB.ADD;
5905: END IF;
5906: --validate status_code change
5899: --This is only enforced during update. Maybe it is better if delaying this validation
5900: --until submitting the OSP order?
5901: IF (p_x_osp_order_line_rec.service_item_id IS NULL AND p_x_osp_order_line_rec.service_item_description IS NULL) THEN
5902: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_SVCITMDSC_BOTH_NLL');
5903: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
5904: FND_MSG_PUB.ADD;
5905: END IF;
5906: --validate status_code change
5907: --it looks like only changing to 'CLOSED' occurs in this API, other statuses will be
5911: -- Modified by jaramana on August 11, 2006 to not do this Date check if the line is PO Cancelled or PO Deleted (Bug 5478764)
5912: -- Added by jaramana on January 10, 2008 for the Requisition ER 6034236 (Added the Requisition related statuses)
5913: IF(TRUNC(p_x_osp_order_line_rec.need_by_date) < TRUNC(SYSDATE) AND
5914: (NVL(p_x_osp_order_line_rec.status_code, 'ENTERED') NOT IN (G_OL_PO_CANCELLED_STATUS, G_OL_PO_DELETED_STATUS,G_OL_REQ_CANCELLED_STATUS, G_OL_REQ_DELETED_STATUS))) THEN
5915: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_NEED_DT');
5916: FND_MESSAGE.Set_Token('NEED_BY_DATE', p_x_osp_order_line_rec.need_by_date);
5917: FND_MSG_PUB.ADD;
5918: END IF;
5919: -- jaramana End
5912: -- Added by jaramana on January 10, 2008 for the Requisition ER 6034236 (Added the Requisition related statuses)
5913: IF(TRUNC(p_x_osp_order_line_rec.need_by_date) < TRUNC(SYSDATE) AND
5914: (NVL(p_x_osp_order_line_rec.status_code, 'ENTERED') NOT IN (G_OL_PO_CANCELLED_STATUS, G_OL_PO_DELETED_STATUS,G_OL_REQ_CANCELLED_STATUS, G_OL_REQ_DELETED_STATUS))) THEN
5915: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_NEED_DT');
5916: FND_MESSAGE.Set_Token('NEED_BY_DATE', p_x_osp_order_line_rec.need_by_date);
5917: FND_MSG_PUB.ADD;
5918: END IF;
5919: -- jaramana End
5920: --validate ship_by_date (is this necessary?)
5919: -- jaramana End
5920: --validate ship_by_date (is this necessary?)
5921: /* Commented out on 05/31/2005 by Jerry
5922: IF(TRUNC(p_x_osp_order_line_rec.ship_by_date) < TRUNC(SYSDATE))THEN
5923: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SHIP_DT');
5924: FND_MESSAGE.Set_Token('SHIP_BY_DATE', p_x_osp_order_line_rec.ship_by_date);
5925: FND_MSG_PUB.ADD;
5926: END IF;
5927: */
5920: --validate ship_by_date (is this necessary?)
5921: /* Commented out on 05/31/2005 by Jerry
5922: IF(TRUNC(p_x_osp_order_line_rec.ship_by_date) < TRUNC(SYSDATE))THEN
5923: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SHIP_DT');
5924: FND_MESSAGE.Set_Token('SHIP_BY_DATE', p_x_osp_order_line_rec.ship_by_date);
5925: FND_MSG_PUB.ADD;
5926: END IF;
5927: */
5928: --validate service_item_id (simplfied the original one)
5948: */
5949: -- jaramana End
5950: IF (p_x_osp_order_line_rec.quantity IS NOT NULL AND p_x_osp_order_line_rec.quantity <> FND_API.G_MISS_NUM) THEN
5951: IF(p_x_osp_order_line_rec.quantity <= 0) THEN
5952: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_VAL');
5953: FND_MESSAGE.Set_Token('QUANTITY', p_x_osp_order_line_rec.quantity);
5954: FND_MSG_PUB.ADD;
5955: END IF;
5956: -- Added by jaramana on January 10, 2008 for the Requisition ER 6034236
5949: -- jaramana End
5950: IF (p_x_osp_order_line_rec.quantity IS NOT NULL AND p_x_osp_order_line_rec.quantity <> FND_API.G_MISS_NUM) THEN
5951: IF(p_x_osp_order_line_rec.quantity <= 0) THEN
5952: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_VAL');
5953: FND_MESSAGE.Set_Token('QUANTITY', p_x_osp_order_line_rec.quantity);
5954: FND_MSG_PUB.ADD;
5955: END IF;
5956: -- Added by jaramana on January 10, 2008 for the Requisition ER 6034236
5957: --Included the equal to zero clause above. Since we are modifying the OSP Line Views and are refraining from
5958: --synching up the quantity from PO Lines, the equal to clause can be imposed on all the OSP line statuses
5959: /*
5960: IF (p_x_osp_order_line_rec.quantity = 0 AND
5961: l_header_status_code NOT IN (G_OSP_PO_CREATED_STATUS, G_OSP_CLOSED_STATUS)) THEN
5962: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_VAL');
5963: FND_MESSAGE.Set_Token('QUANTITY', p_x_osp_order_line_rec.quantity);
5964: FND_MSG_PUB.ADD;
5965: END IF;
5966: */
5959: /*
5960: IF (p_x_osp_order_line_rec.quantity = 0 AND
5961: l_header_status_code NOT IN (G_OSP_PO_CREATED_STATUS, G_OSP_CLOSED_STATUS)) THEN
5962: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_VAL');
5963: FND_MESSAGE.Set_Token('QUANTITY', p_x_osp_order_line_rec.quantity);
5964: FND_MSG_PUB.ADD;
5965: END IF;
5966: */
5967: -- jaramana End
5966: */
5967: -- jaramana End
5968: --quantity cannot be null when UOM is not null
5969: ELSIF (p_x_osp_order_line_rec.service_item_uom_code IS NOT NULL AND p_x_osp_order_line_rec.service_item_uom_code <> FND_API.G_MISS_CHAR) THEN
5970: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_NLL');
5971: FND_MSG_PUB.ADD;
5972: END IF;
5973:
5974: -- validate service/physical item combination with vendor
5987: OPEN check_phy_item_unique(p_x_osp_order_line_rec.osp_order_id,
5988: p_x_osp_order_line_rec.inventory_item_id);
5989: FETCH check_phy_item_unique INTO g_dummy_char;
5990: IF check_phy_item_unique%FOUND THEN
5991: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_PHY_ITEM_UNIUE');
5992: FND_MSG_PUB.ADD;
5993: END IF;
5994: CLOSE check_phy_item_unique;
5995: ELSE
6002: p_x_osp_order_line_rec.lot_number,
6003: p_x_osp_order_line_rec.serial_number);
6004: FETCH check_phy_ser_item_unique INTO g_dummy_char;
6005: IF check_phy_ser_item_unique%FOUND THEN
6006: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_PHY_SER_ITEM_UNIUE');
6007: FND_MSG_PUB.ADD;
6008: END IF;
6009: CLOSE check_phy_ser_item_unique;
6010: END IF;
6313: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Begin Procedure');
6314: END IF;
6315: IF (p_vendor_id IS NULL) THEN
6316: IF (p_vendor_site_id IS NOT NULL OR p_vendor_contact_id IS NOT NULL) THEN
6317: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
6318: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
6319: FND_MSG_PUB.ADD;
6320: END IF;
6321: ELSE
6314: END IF;
6315: IF (p_vendor_id IS NULL) THEN
6316: IF (p_vendor_site_id IS NOT NULL OR p_vendor_contact_id IS NOT NULL) THEN
6317: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
6318: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
6319: FND_MSG_PUB.ADD;
6320: END IF;
6321: ELSE
6322: OPEN val_vendor_id_csr;
6321: ELSE
6322: OPEN val_vendor_id_csr;
6323: FETCH val_vendor_id_csr INTO l_exist;
6324: IF(val_vendor_id_csr%NOTFOUND) THEN
6325: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
6326: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
6327: FND_MSG_PUB.ADD;
6328: END IF;
6329: CLOSE val_vendor_id_csr;
6322: OPEN val_vendor_id_csr;
6323: FETCH val_vendor_id_csr INTO l_exist;
6324: IF(val_vendor_id_csr%NOTFOUND) THEN
6325: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
6326: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
6327: FND_MSG_PUB.ADD;
6328: END IF;
6329: CLOSE val_vendor_id_csr;
6330: OPEN get_vendor_cert;
6329: CLOSE val_vendor_id_csr;
6330: OPEN get_vendor_cert;
6331: FETCH get_vendor_cert INTO l_exist;
6332: IF(get_vendor_cert%NOTFOUND) THEN
6333: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
6334: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
6335: FND_MSG_PUB.ADD;
6336: END IF;
6337: CLOSE get_vendor_cert;
6330: OPEN get_vendor_cert;
6331: FETCH get_vendor_cert INTO l_exist;
6332: IF(get_vendor_cert%NOTFOUND) THEN
6333: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
6334: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
6335: FND_MSG_PUB.ADD;
6336: END IF;
6337: CLOSE get_vendor_cert;
6338: IF (p_vendor_site_id IS NULL AND p_vendor_contact_id IS NOT NULL) THEN
6335: FND_MSG_PUB.ADD;
6336: END IF;
6337: CLOSE get_vendor_cert;
6338: IF (p_vendor_site_id IS NULL AND p_vendor_contact_id IS NOT NULL) THEN
6339: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
6340: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
6341: FND_MSG_PUB.ADD;
6342: ELSIF (p_vendor_site_id IS NOT NULL) THEN
6343: OPEN val_vendor_site_id_csr;
6336: END IF;
6337: CLOSE get_vendor_cert;
6338: IF (p_vendor_site_id IS NULL AND p_vendor_contact_id IS NOT NULL) THEN
6339: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
6340: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
6341: FND_MSG_PUB.ADD;
6342: ELSIF (p_vendor_site_id IS NOT NULL) THEN
6343: OPEN val_vendor_site_id_csr;
6344: FETCH val_vendor_site_id_csr INTO l_exist;
6342: ELSIF (p_vendor_site_id IS NOT NULL) THEN
6343: OPEN val_vendor_site_id_csr;
6344: FETCH val_vendor_site_id_csr INTO l_exist;
6345: IF(val_vendor_site_id_csr%NOTFOUND) THEN
6346: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
6347: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
6348: FND_MSG_PUB.ADD;
6349: END IF;
6350: CLOSE val_vendor_site_id_csr;
6343: OPEN val_vendor_site_id_csr;
6344: FETCH val_vendor_site_id_csr INTO l_exist;
6345: IF(val_vendor_site_id_csr%NOTFOUND) THEN
6346: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
6347: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
6348: FND_MSG_PUB.ADD;
6349: END IF;
6350: CLOSE val_vendor_site_id_csr;
6351: OPEN get_site_cert;
6350: CLOSE val_vendor_site_id_csr;
6351: OPEN get_site_cert;
6352: FETCH get_site_cert INTO l_exist;
6353: IF(get_site_cert%NOTFOUND) THEN
6354: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
6355: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
6356: FND_MSG_PUB.ADD;
6357: END IF;
6358: CLOSE get_site_cert;
6351: OPEN get_site_cert;
6352: FETCH get_site_cert INTO l_exist;
6353: IF(get_site_cert%NOTFOUND) THEN
6354: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
6355: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
6356: FND_MSG_PUB.ADD;
6357: END IF;
6358: CLOSE get_site_cert;
6359: IF (p_vendor_contact_id IS NOT NULL) THEN
6359: IF (p_vendor_contact_id IS NOT NULL) THEN
6360: OPEN val_vendor_contact_id_csr;
6361: FETCH val_vendor_contact_id_csr INTO l_exist;
6362: IF(val_vendor_contact_id_csr%NOTFOUND) THEN
6363: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VEN_CONTACT_ID_INV');
6364: FND_MESSAGE.Set_Token('VENDOR_CONTACT_ID', p_vendor_contact_id);
6365: FND_MSG_PUB.ADD;
6366: END IF;
6367: CLOSE val_vendor_contact_id_csr;
6360: OPEN val_vendor_contact_id_csr;
6361: FETCH val_vendor_contact_id_csr INTO l_exist;
6362: IF(val_vendor_contact_id_csr%NOTFOUND) THEN
6363: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VEN_CONTACT_ID_INV');
6364: FND_MESSAGE.Set_Token('VENDOR_CONTACT_ID', p_vendor_contact_id);
6365: FND_MSG_PUB.ADD;
6366: END IF;
6367: CLOSE val_vendor_contact_id_csr;
6368: OPEN get_contact_cert;
6367: CLOSE val_vendor_contact_id_csr;
6368: OPEN get_contact_cert;
6369: FETCH get_contact_cert INTO l_exist;
6370: IF(get_contact_cert%NOTFOUND) THEN
6371: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VEN_CONTACT_ID_INV');
6372: FND_MESSAGE.Set_Token('VENDOR_CONTACT_ID', p_vendor_contact_id);
6373: FND_MSG_PUB.ADD;
6374: END IF;
6375: CLOSE get_contact_cert;
6368: OPEN get_contact_cert;
6369: FETCH get_contact_cert INTO l_exist;
6370: IF(get_contact_cert%NOTFOUND) THEN
6371: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VEN_CONTACT_ID_INV');
6372: FND_MESSAGE.Set_Token('VENDOR_CONTACT_ID', p_vendor_contact_id);
6373: FND_MSG_PUB.ADD;
6374: END IF;
6375: CLOSE get_contact_cert;
6376: END IF;
6530: IF(p_x_osp_order_line_rec.operation_flag = G_OP_UPDATE) THEN
6531: OPEN osp_order_lines_csr;
6532: FETCH osp_order_lines_csr INTO l_osp_order_line_rec;
6533: IF (osp_order_lines_csr%NOTFOUND) THEN
6534: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INVOP_OSP_LN_NFOUND');
6535: FND_MSG_PUB.ADD;
6536: ELSE
6537: IF (p_x_osp_order_line_rec.osp_order_id IS NULL) THEN
6538: p_x_osp_order_line_rec.osp_order_id := l_osp_order_line_rec.osp_order_id;
6992: IF (p_x_osp_order_line_rec.job_number IS NOT NULL AND p_x_osp_order_line_rec.job_number <> FND_API.G_MISS_CHAR) THEN
6993: OPEN workorder_id_csr(p_x_osp_order_line_rec.job_number);
6994: FETCH workorder_id_csr INTO l_workorder_id;
6995: IF(workorder_id_csr%NOTFOUND) THEN
6996: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_JOB_NUM_INV');
6997: FND_MESSAGE.Set_Token('JOB_NUMBER', p_x_osp_order_line_rec.job_number);
6998: FND_MSG_PUB.ADD;
6999: ELSE
7000: p_x_osp_order_line_rec.workorder_id := l_workorder_id;
6993: OPEN workorder_id_csr(p_x_osp_order_line_rec.job_number);
6994: FETCH workorder_id_csr INTO l_workorder_id;
6995: IF(workorder_id_csr%NOTFOUND) THEN
6996: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_JOB_NUM_INV');
6997: FND_MESSAGE.Set_Token('JOB_NUMBER', p_x_osp_order_line_rec.job_number);
6998: FND_MSG_PUB.ADD;
6999: ELSE
7000: p_x_osp_order_line_rec.workorder_id := l_workorder_id;
7001: END IF;
7005: IF (p_x_osp_order_line_rec.service_item_number IS NOT NULL AND p_x_osp_order_line_rec.service_item_number <> FND_API.G_MISS_CHAR) THEN
7006: OPEN service_item_id_csr(p_x_osp_order_line_rec.service_item_number, p_x_osp_order_line_rec.inventory_org_id);
7007: FETCH service_item_id_csr INTO l_service_item_id;
7008: IF(service_item_id_csr%NOTFOUND) THEN
7009: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_SVC_ITEM_INV');
7010: FND_MESSAGE.Set_Token('SERVICE_ITEM', p_x_osp_order_line_rec.service_item_number);
7011: FND_MSG_PUB.ADD;
7012: ELSE
7013: p_x_osp_order_line_rec.service_item_id := l_service_item_id;
7006: OPEN service_item_id_csr(p_x_osp_order_line_rec.service_item_number, p_x_osp_order_line_rec.inventory_org_id);
7007: FETCH service_item_id_csr INTO l_service_item_id;
7008: IF(service_item_id_csr%NOTFOUND) THEN
7009: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_SVC_ITEM_INV');
7010: FND_MESSAGE.Set_Token('SERVICE_ITEM', p_x_osp_order_line_rec.service_item_number);
7011: FND_MSG_PUB.ADD;
7012: ELSE
7013: p_x_osp_order_line_rec.service_item_id := l_service_item_id;
7014: END IF;
7018: IF (p_x_osp_order_line_rec.po_line_type IS NOT NULL AND p_x_osp_order_line_rec.po_line_type <> FND_API.G_MISS_CHAR) THEN
7019: OPEN po_line_type_id_csr(p_x_osp_order_line_rec.po_line_type);
7020: FETCH po_line_type_id_csr INTO l_po_line_type_id;
7021: IF(po_line_type_id_csr%NOTFOUND) THEN
7022: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_PO_LN_TYPE_INV');
7023: FND_MESSAGE.Set_Token('PO_LINE_TYPE', p_x_osp_order_line_rec.po_line_type);
7024: FND_MSG_PUB.ADD;
7025: ELSE
7026: p_x_osp_order_line_rec.po_line_type_id := l_po_line_type_id;
7019: OPEN po_line_type_id_csr(p_x_osp_order_line_rec.po_line_type);
7020: FETCH po_line_type_id_csr INTO l_po_line_type_id;
7021: IF(po_line_type_id_csr%NOTFOUND) THEN
7022: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_PO_LN_TYPE_INV');
7023: FND_MESSAGE.Set_Token('PO_LINE_TYPE', p_x_osp_order_line_rec.po_line_type);
7024: FND_MSG_PUB.ADD;
7025: ELSE
7026: p_x_osp_order_line_rec.po_line_type_id := l_po_line_type_id;
7027: END IF;
7031: IF (p_x_osp_order_line_rec.exchange_instance_number IS NOT NULL AND p_x_osp_order_line_rec.exchange_instance_number <> FND_API.G_MISS_CHAR) THEN
7032: OPEN exchange_instance_id_csr(p_x_osp_order_line_rec.exchange_instance_number);
7033: FETCH exchange_instance_id_csr INTO l_exchange_instance_id;
7034: IF(exchange_instance_id_csr%NOTFOUND) THEN
7035: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_X_INST_NUM_INV');
7036: FND_MESSAGE.Set_Token('INTANCE_NUMBER', p_x_osp_order_line_rec.exchange_instance_number);
7037: FND_MSG_PUB.ADD;
7038: ELSE
7039: p_x_osp_order_line_rec.exchange_instance_id := l_exchange_instance_id;
7032: OPEN exchange_instance_id_csr(p_x_osp_order_line_rec.exchange_instance_number);
7033: FETCH exchange_instance_id_csr INTO l_exchange_instance_id;
7034: IF(exchange_instance_id_csr%NOTFOUND) THEN
7035: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_X_INST_NUM_INV');
7036: FND_MESSAGE.Set_Token('INTANCE_NUMBER', p_x_osp_order_line_rec.exchange_instance_number);
7037: FND_MSG_PUB.ADD;
7038: ELSE
7039: p_x_osp_order_line_rec.exchange_instance_id := l_exchange_instance_id;
7040: END IF;
7044: IF (p_x_osp_order_line_rec.item_number IS NOT NULL AND p_x_osp_order_line_rec.inventory_item_id IS NULL) THEN
7045: OPEN physical_item_id_csr(p_x_osp_order_line_rec.item_number, p_x_osp_order_line_rec.inventory_org_id);
7046: FETCH physical_item_id_csr INTO l_physical_item_id;
7047: IF(physical_item_id_csr%NOTFOUND) THEN
7048: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_PHY_ITEM_INV');
7049: FND_MESSAGE.Set_Token('PHSICAL_ITEM', p_x_osp_order_line_rec.item_number);
7050: FND_MSG_PUB.ADD;
7051: ELSE
7052: p_x_osp_order_line_rec.inventory_item_id := l_physical_item_id;
7045: OPEN physical_item_id_csr(p_x_osp_order_line_rec.item_number, p_x_osp_order_line_rec.inventory_org_id);
7046: FETCH physical_item_id_csr INTO l_physical_item_id;
7047: IF(physical_item_id_csr%NOTFOUND) THEN
7048: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_PHY_ITEM_INV');
7049: FND_MESSAGE.Set_Token('PHSICAL_ITEM', p_x_osp_order_line_rec.item_number);
7050: FND_MSG_PUB.ADD;
7051: ELSE
7052: p_x_osp_order_line_rec.inventory_item_id := l_physical_item_id;
7053: END IF;
7059: IF (p_x_osp_order_line_rec.osp_order_id IS NOT NULL AND p_x_osp_order_line_rec.osp_line_number IS NOT NULL) THEN
7060: OPEN osp_order_line_on_csr(p_x_osp_order_line_rec.osp_order_id , p_x_osp_order_line_rec.osp_line_number);
7061: FETCH osp_order_line_on_csr INTO l_osp_order_line_id;
7062: IF(osp_order_line_on_csr%NOTFOUND) THEN
7063: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_LN_INV');
7064: FND_MESSAGE.Set_Token('OSP_LINE_NUMBER', p_x_osp_order_line_rec.osp_line_number);
7065: FND_MSG_PUB.ADD;
7066: ELSE
7067: p_x_osp_order_line_rec.osp_order_line_id := l_osp_order_line_id;
7060: OPEN osp_order_line_on_csr(p_x_osp_order_line_rec.osp_order_id , p_x_osp_order_line_rec.osp_line_number);
7061: FETCH osp_order_line_on_csr INTO l_osp_order_line_id;
7062: IF(osp_order_line_on_csr%NOTFOUND) THEN
7063: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_LN_INV');
7064: FND_MESSAGE.Set_Token('OSP_LINE_NUMBER', p_x_osp_order_line_rec.osp_line_number);
7065: FND_MSG_PUB.ADD;
7066: ELSE
7067: p_x_osp_order_line_rec.osp_order_line_id := l_osp_order_line_id;
7068: END IF;
7073: ELSIF (p_x_osp_order_line_rec.po_line_id IS NOT NULL AND p_x_osp_order_line_rec.po_line_id <> FND_API.G_MISS_NUM) THEN
7074: OPEN osp_order_line_po_csr(p_x_osp_order_line_rec.po_line_id );
7075: FETCH osp_order_line_po_csr INTO l_osp_order_line_id;
7076: IF(osp_order_line_po_csr%NOTFOUND) THEN
7077: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PO_LINE_INV');
7078: FND_MESSAGE.Set_Token('PO_LINE_ID', p_x_osp_order_line_rec.po_line_id);
7079: FND_MSG_PUB.ADD;
7080: ELSE
7081: p_x_osp_order_line_rec.osp_order_line_id := l_osp_order_line_id;
7074: OPEN osp_order_line_po_csr(p_x_osp_order_line_rec.po_line_id );
7075: FETCH osp_order_line_po_csr INTO l_osp_order_line_id;
7076: IF(osp_order_line_po_csr%NOTFOUND) THEN
7077: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PO_LINE_INV');
7078: FND_MESSAGE.Set_Token('PO_LINE_ID', p_x_osp_order_line_rec.po_line_id);
7079: FND_MSG_PUB.ADD;
7080: ELSE
7081: p_x_osp_order_line_rec.osp_order_line_id := l_osp_order_line_id;
7082: END IF;
7083: CLOSE osp_order_line_po_csr;
7084: */
7085: -- jaramana End
7086: ELSE
7087: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV');
7088: FND_MESSAGE.Set_Token('OSP_LINE_NUMBER', p_x_osp_order_line_rec.osp_line_number); FND_MSG_PUB.ADD;
7089: END IF;
7090: END IF;
7091: END IF;
7084: */
7085: -- jaramana End
7086: ELSE
7087: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV');
7088: FND_MESSAGE.Set_Token('OSP_LINE_NUMBER', p_x_osp_order_line_rec.osp_line_number); FND_MSG_PUB.ADD;
7089: END IF;
7090: END IF;
7091: END IF;
7092: IF FND_MSG_PUB.count_msg > 0 THEN
7125: IF(p_service_item_id IS NOT NULL AND p_service_item_id <> FND_API.G_MISS_NUM) THEN
7126: OPEN val_service_item_id_csr;
7127: FETCH val_service_item_id_csr INTO g_dummy_char;
7128: IF(val_service_item_id_csr%NOTFOUND) THEN
7129: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM');
7130: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
7131: FND_MSG_PUB.ADD;
7132: END IF;
7133: CLOSE val_service_item_id_csr;
7126: OPEN val_service_item_id_csr;
7127: FETCH val_service_item_id_csr INTO g_dummy_char;
7128: IF(val_service_item_id_csr%NOTFOUND) THEN
7129: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM');
7130: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
7131: FND_MSG_PUB.ADD;
7132: END IF;
7133: CLOSE val_service_item_id_csr;
7134: END IF;
7169: END IF;
7170: --IF (p_osp_order_line_rec.workorder_id IS NULL) THEN --commented out on 06/10/2006
7171: /*(From Jay said on 06/07/2005) service_item_id is not mandatory even for update
7172: IF p_osp_order_line_rec.service_item_id IS NULL THEN
7173: FND_MESSAGE.set_name('AHL', 'AHL_OSP_SERVICE_ITEM_NULL');
7174: FND_MSG_PUB.add;
7175: END IF;
7176: */
7177: --(Note: 05/31/2005 Jerry)It is not good to use ahl_osp_orders_v here but
7198: WHERE osp.osp_order_id = p_osp_order_line_rec.osp_order_id;
7199: --mpothuku End
7200: EXCEPTION
7201: WHEN NO_DATA_FOUND THEN
7202: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORDER_INVALID');
7203: FND_MSG_PUB.add;
7204: END;
7205:
7206: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
7225: AND trunc(active_start_date) <= trunc(SYSDATE)
7226: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
7227: EXCEPTION
7228: WHEN NO_DATA_FOUND THEN
7229: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
7230: FND_MESSAGE.Set_Token('VENDOR_ID', l_vendor_id);
7231: FND_MSG_PUB.ADD;
7232: END;
7233:
7226: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
7227: EXCEPTION
7228: WHEN NO_DATA_FOUND THEN
7229: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
7230: FND_MESSAGE.Set_Token('VENDOR_ID', l_vendor_id);
7231: FND_MSG_PUB.ADD;
7232: END;
7233:
7234: OPEN verify_vendor(l_vendor_id, l_vendor_site_id);
7233:
7234: OPEN verify_vendor(l_vendor_id, l_vendor_site_id);
7235: FETCH verify_vendor INTO g_dummy_num;
7236: IF verify_vendor%NOTFOUND THEN
7237: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ITEM_VENDOR_MISMATCH');
7238: FND_MESSAGE.set_token('ITEM_ID', p_osp_order_line_rec.inventory_item_id);
7239: FND_MSG_PUB.add;
7240: RAISE FND_API.G_EXC_ERROR;
7241: --FND_MSG_PUB.count_and_get(
7234: OPEN verify_vendor(l_vendor_id, l_vendor_site_id);
7235: FETCH verify_vendor INTO g_dummy_num;
7236: IF verify_vendor%NOTFOUND THEN
7237: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ITEM_VENDOR_MISMATCH');
7238: FND_MESSAGE.set_token('ITEM_ID', p_osp_order_line_rec.inventory_item_id);
7239: FND_MSG_PUB.add;
7240: RAISE FND_API.G_EXC_ERROR;
7241: --FND_MSG_PUB.count_and_get(
7242: --p_encoded => FND_API.G_FALSE,