[Home] [Help]
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;
329: -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
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;
329: -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
330: END IF;
331: IF(g_module_type = 'JSP')THEN
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);
405: FND_MSG_PUB.ADD;
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;
409: --dbms_output.put_line('done converting vendor_name to id');
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);
434: FND_MSG_PUB.ADD;
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;
438: END IF;
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);
462: FND_MSG_PUB.ADD;
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;
466: END IF;
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);
491: FND_MSG_PUB.ADD;
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;
495: END IF;
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;
508: CLOSE contract_id_csr;
506: p_x_osp_order_rec.contract_id := l_contract_id;
507: END IF;
508: CLOSE contract_id_csr;
509: END IF;
510: IF FND_MSG_PUB.count_msg > 0 THEN
511: RAISE FND_API.G_EXC_ERROR;
512: -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
513: END IF;
514: -- finding unique key osp_order_id when operation flag is not 'C'
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;
528: END IF;
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;
542: END IF;
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;
554: END IF;
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;
564: IF FND_MSG_PUB.count_msg > 0 THEN
560: FND_MSG_PUB.ADD;
561: END IF;
562: END IF;
563: END IF;
564: IF FND_MSG_PUB.count_msg > 0 THEN
565: RAISE FND_API.G_EXC_ERROR;
566: -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
567: END IF;
568: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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;
605: FETCH get_vendor_cert INTO l_exist;
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;
613: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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
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;
657: FETCH get_vendor_cert INTO l_exist;
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;
665: END IF;
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;
694: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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;
722: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) 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
762: IF(p_contract_id IS NOT NULL AND p_contract_id <> FND_API.G_MISS_NUM) THEN
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
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;
778: END IF;
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
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;
815: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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;
875: -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
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;
875: -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
876: END IF;
877: -- Note added by jaramana on January 8, 2008 for the Requisition ER 6034236
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);
890: -- jaramana modified on January 8, 2008 for the Requisition ER 6034236 (Added po_req_header_id)
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.
908: IF(l_osp_order_rec.status_code = G_OSP_CLOSED_STATUS) THEN
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;
914: g_old_type_code := l_osp_order_rec.order_type_code;
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
926: --if the passed status is G_OSP_SUBMITTED_STATUS and the old status is not G_OSP_SUBMITTED_STATUS
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;
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
965: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_ORD_DT');
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
970: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_OPUNIT');
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:
975: -- Commented out by jaramana on January 8, 2008 for the Requisition ER 6034236
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
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
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;
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;
1005: -- Commented out by jaramana on January 8, 2008 for the Requisition ER 6034236
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');
1021: FND_MSG_PUB.ADD;
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
1025: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_NUM_INV');
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
1031: IF(TRUNC(p_x_osp_order_rec.order_date) <> TRUNC(SYSDATE)) THEN
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;
1047: END IF;
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');
1055: FND_MSG_PUB.ADD;
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');
1059: FND_MSG_PUB.ADD;
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');
1063: FND_MSG_PUB.ADD;
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');
1067: FND_MSG_PUB.ADD;
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
1071: AND p_x_osp_order_rec.status_code <> G_OSP_ENTERED_STATUS) THEN
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;
1077: --g_order_status_for_update := G_OSP_ENTERED_STATUS;
1078: END IF;
1079: --dbms_output.put_line('Create mode defaulting done');
1080: */
1081: END IF;
1082: IF FND_MSG_PUB.count_msg > 0 THEN
1083: RAISE FND_API.G_EXC_ERROR;
1084: -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1085: END IF;
1086: ----------------------------
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');
1102: FND_MESSAGE.Set_Token('CUSTOMER_ID', p_x_osp_order_rec.customer_id);
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');
1107: FND_MSG_PUB.ADD;
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');
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');
1121: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_x_osp_order_rec.vendor_site_id);
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
1126: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
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
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
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');
1138: FND_MSG_PUB.ADD;
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');
1142: FND_MSG_PUB.ADD;
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');
1146: FND_MSG_PUB.ADD;
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
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
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:
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
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;
1162: --dbms_output.put_line('ensured null columns');
1159: END IF;
1160:
1161: END IF;
1162: --dbms_output.put_line('ensured null columns');
1163: IF FND_MSG_PUB.count_msg > 0 THEN
1164: RAISE FND_API.G_EXC_ERROR;
1165: -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1166: END IF;
1167: IF (p_x_osp_order_rec.order_type_code IN ( G_OSP_ORDER_TYPE_SERVICE, G_OSP_ORDER_TYPE_EXCHANGE)) THEN --item exchange enhancement add G_OSP_ORDER_TYPE_EXCHANGE condition
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:
1188: -- validate vendor_id.
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;
1195: --dbms_output.put_line('validate vendor site');
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;
1203:
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;
1215: */
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.
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
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:
1241: -- Modified by jaramana on January 9, 2008 for the Requisition ER 6034236
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;
1251: ELSIF (g_old_status_code = G_OSP_SUB_FAILED_STATUS AND p_x_osp_order_rec.status_code = G_OSP_ENTERED_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;
1254: END IF;
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: /*
1257: 1. If the current status is G_OSP_ENTERED_STATUS, the status cannot be changed to G_OSP_SUB_FAILED_STATUS or
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
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
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
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;
1294: -- validate vendor_site_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);
1301: validate_vendor_site(p_x_osp_order_rec.vendor_id, p_x_osp_order_rec.vendor_site_id);
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
1312: --G_MISS/Null conversion has already been made in default_unchanged_order_header
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,
1319: p_x_osp_order_rec.vendor_site_id,
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;
1331: -- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
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;
1349: -- End addition by jaramana on January 9, 2008
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
1364: -- req_header_id should be null.
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:
1371: -- single_instance_flag cant change
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
1378: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CHG');
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
1383: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_STE_CHG');
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
1388: -- This should have been present with ISO changes. Adding now.
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:
1396: -- Buyer cant change
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
1403: --G_OSP_SUBMITTED_STATUS -> G_OSP_PO_CREATED_STATUS are not done from this API and are handled by the PO Synch
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
1415: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
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: /*
1423: -- validate vendor_id.
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;
1430: -- validate vendor_site_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);
1437: validate_vendor_site(p_x_osp_order_rec.vendor_id, p_x_osp_order_rec.vendor_site_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;
1452: -- set status_code for lines
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;
1466: -- single_instance_flag cant change
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
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
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
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
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
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
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;
1493: */
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
1501: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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
1514: -- req_header_id should be null.
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:
1521: -- single_instance_flag cant change
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
1528: -- vendor_id cant change
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
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
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: */
1543: -- Changes by jaramana on January 9, 2008 for the Requisition ER 6034236
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
1553: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_STE_CHG');
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
1558: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CTCT_CHG ');
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:
1563: -- Buyer cant change
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;
1570: ELSE
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
1576: IF(p_x_osp_order_rec.status_code IS NULL OR p_x_osp_order_rec.status_code = G_OSP_REQ_SUBMITTED_STATUS) THEN
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.
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
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
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
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
1600: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CHG');
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
1605: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_STE_CHG');
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
1610: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CTCT_CHG ');
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
1615: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_BUYER_CHG');
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');
1620: FND_MSG_PUB.ADD;
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
1624: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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
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
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
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
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
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
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
1655: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_STE_CHG');
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
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
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
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
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;
1672: ELSE
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
1678: END IF; --IF (p_x_osp_order_rec.order_type_code IN ( G_OSP_ORDER_TYPE_SERVICE, G_OSP_ORDER_TYPE_EXCHANGE)) 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.
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
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;
1701: -- validate contract_id
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: */
1713: 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
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.
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
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;
1734: -- validate contract_id
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: */
1746: 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
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
1761: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CUST_CHG');
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
1766: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CTRCT_CHG');
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
1771: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
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
1776: ELSIF (p_x_osp_order_rec.order_type_code = G_OSP_ORDER_TYPE_BORROW) 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.
1788: IF(p_x_osp_order_rec.customer_id IS NOT NULL) THEN
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;
1799: */
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;
1815: */
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
1830: validate_customer(p_x_osp_order_rec.customer_id);
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;
1843: ELSIF(p_x_osp_order_rec.vendor_id IS NOT NULL OR p_x_osp_order_rec.contract_id IS NOT NULL) 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);
1846: p_x_osp_order_rec.contract_id := NVL(p_x_osp_order_rec.contract_id, l_osp_order_rec.contract_id);
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;
1859: */
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
1881: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CUST_CHG');
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
1886: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEND_CHG');
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
1891: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CTRCT_CHG');
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;
1896: ELSE
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
1902: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_TYPE');
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
1907: -- Changed by jaramana on January 9, 2008 for the Requisition ER 6034236 (Added G_OSP_REQ_SUB_FAILED_STATUS)
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
1914: -- calling to delete SO HEADER
1919: END IF;
1920: END IF;
1921: -- jaramana End
1922: END IF;
1923: IF FND_MSG_PUB.count_msg > 0 THEN
1924: RAISE FND_API.G_EXC_ERROR;
1925: -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1926: END IF;
1927: IF (FND_LOG.LEVEL_PROCEDURE >= 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
1984: p_x_osp_order_rec.osp_order_number := l_osp_order_rec.osp_order_number;
2289: IF(p_x_osp_order_rec.vendor_contact_id = FND_API.G_MISS_NUM) THEN
2290: p_x_osp_order_rec.vendor_contact_id := null;
2291: END IF;
2292: END IF;
2293: IF FND_MSG_PUB.count_msg > 0 THEN
2294: RAISE FND_API.G_EXC_ERROR;
2295: -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2296: END IF;
2297: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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;
2350: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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
2407: IF ( p_order_type_code = G_OSP_ORDER_TYPE_SERVICE) THEN -- item exchange enhancement
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.
2422: -- So, a mismatch is OK
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.
2447: -- So, user entered value can be null
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;
2486: END IF;
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
2552: OPEN val_service_item_id_wo_csr(p_workorder_id, p_service_item_id);
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.
2566: -- So, a mismatch is OK
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.
2590: -- So, user entered value can be null
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;
2638: ELSE
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;
2650: END IF;
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;
2681: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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;
2719: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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
2752: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'End Procedure');
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');
2842: FND_MSG_PUB.ADD;
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');
2846: FND_MSG_PUB.ADD;
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');
2850: FND_MSG_PUB.ADD;
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
2854: IF (p_x_osp_order_rec.po_req_header_id IS NOT NULL) THEN
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
2869: OPEN val_order_lines_csr(p_x_osp_order_rec.osp_order_id);
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
2882: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_UOM_NL');
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
2888: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_QTY_NL');
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
2894: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_NBD_NL');
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
2900: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_PO_LNTYP_NL');
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;
2906: IF FND_MSG_PUB.count_msg > 0 THEN
2902: FND_MSG_PUB.ADD;
2903: END IF;
2904: END LOOP;
2905: CLOSE val_order_lines_csr;
2906: IF FND_MSG_PUB.count_msg > 0 THEN
2907: RAISE FND_API.G_EXC_ERROR;
2908: -- Changed by jaramana on January 9, 2008 for the Requisition ER 6034236
2909: ELSIF(p_x_osp_order_rec.status_code = G_OSP_SUBMITTED_STATUS) THEN
2910: -- jaramana End
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;
2933: p_x_osp_order_rec.po_interface_header_id := l_interface_header_id;
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;
2965: p_x_osp_order_rec.po_batch_id := l_batch_id;
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;
2987: ELSE --This would mean either po_header_id is not null OR po_req_header_id is not NULL
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
2990: AHL_OSP_PO_PVT.Is_PO_Closed(p_x_osp_order_rec.po_header_id) = G_NO_FLAG ) THEN
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
2996: AHL_OSP_PO_REQ_PVT.Is_PO_Req_Closed(p_x_osp_order_rec.po_req_header_id) = G_NO_FLAG ) THEN
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
3002: IF(p_x_osp_order_rec.oe_header_id IS NOT NULL) THEN
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.
3009: OPEN val_all_wo_closed(p_x_osp_order_rec.osp_order_id);
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;
3017: END IF;
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
3038: IF(p_x_osp_order_rec.oe_header_id IS NULL) THEN
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);
3044: l_count := 0;
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;
3056: END IF;
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
3064: RAISE FND_API.G_EXC_ERROR;
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
3064: RAISE FND_API.G_EXC_ERROR;
3065: ELSE
3066: l_shipment_IDs_Tbl(1) := p_x_osp_order_rec.oe_header_id;
3067: AHL_OSP_SHIPMENT_PUB.Book_Order(
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
3092: --dbms_output.put_line('Sales order is not closed');
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);
3098: FETCH val_all_wo_closed INTO l_exist;
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;
3105: END IF;
3103: CLOSE val_all_wo_closed;
3104: END IF;
3105: END IF;
3106: END IF;
3107: IF FND_MSG_PUB.count_msg > 0 THEN
3108: RAISE FND_API.G_EXC_ERROR;
3109: END IF;
3110: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3111: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'End Procedure');
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
3167: RETURN;
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;
3262: IF FND_MSG_PUB.count_msg > 0 THEN
3258: FND_MSG_PUB.ADD;
3259: END IF;
3260: END IF;
3261: END IF;
3262: IF FND_MSG_PUB.count_msg > 0 THEN
3263: RAISE FND_API.G_EXC_ERROR;
3264: -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3265: END IF;
3266: 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;
3298: return false;
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;
3306: return false;
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;
3317: CLOSE order_header_status_csr;
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;
3329: CLOSE order_has_ship_return_csr;
3449: END IF;
3450:
3451: --Initialize message list if p_init_msg_list is set to TRUE.
3452: IF FND_API.to_boolean(p_init_msg_list) THEN
3453: FND_MSG_PUB.initialize;
3454: END IF;
3455:
3456: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3457: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
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
3471: --In case only header record is to be processed, then NULL is supposed to be passed
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;
3481: END IF;
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
3530: IF (l_status_code IN (G_OSP_ENTERED_STATUS, G_OSP_SUB_FAILED_STATUS, G_OSP_REQ_SUB_FAILED_STATUS)) THEN
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
3552: --Create(Add) new line record
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:
3593: IF l_valid_vendors_tbl.count > 0 THEN
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
3611: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ITEM_VENDOR_MISMATCH');
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;
3616: END IF;
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
3640: --Delete OSP order Header/Line records
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
3667: l_del_cancel_so_lines_tbl(i).osp_order_id := p_x_osp_order_rec.osp_order_id;
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
3702: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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
3735: IF (l_status_code IN (G_OSP_ENTERED_STATUS, G_OSP_SUB_FAILED_STATUS, G_OSP_REQ_SUB_FAILED_STATUS)) THEN
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);
3757: ELSIF (p_x_osp_order_lines_tbl(i).operation_flag = G_OP_UPDATE) THEN
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
3792: IF l_valid_vendors_tbl.count > 0 THEN
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
3810: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ITEM_VENDOR_MISMATCH');
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;
3815: ELSIF (p_x_osp_order_rec.operation_flag = G_OP_CREATE) THEN
3870: END IF;
3871: END IF;
3872:
3873: -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
3874: l_msg_count := FND_MSG_PUB.count_msg;
3875: IF l_msg_count > 0 THEN
3876: x_msg_count := l_msg_count;
3877: RAISE FND_API.G_EXC_ERROR;
3878: END IF;
3882: COMMIT;
3883: END IF;
3884:
3885: -- Count and Get messages (optional)
3886: FND_MSG_PUB.count_and_get(
3887: p_encoded => FND_API.G_FALSE,
3888: p_count => x_msg_count,
3889: p_data => x_msg_data);
3890:
3891: EXCEPTION
3892: WHEN FND_API.G_EXC_ERROR THEN
3893: ROLLBACK TO process_osp_order;
3894: x_return_status := FND_API.G_RET_STS_ERROR;
3895: FND_MSG_PUB.count_and_get(
3896: p_encoded => FND_API.G_FALSE,
3897: p_count => x_msg_count,
3898: p_data => x_msg_data);
3899:
3899:
3900: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3901: ROLLBACK TO process_osp_order;
3902: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3903: FND_MSG_PUB.count_and_get(
3904: p_encoded => FND_API.G_FALSE,
3905: p_count => x_msg_count,
3906: p_data => x_msg_data);
3907:
3907:
3908: WHEN OTHERS THEN
3909: ROLLBACK TO process_osp_order;
3910: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3911: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3912: THEN
3913: FND_MSG_PUB.add_exc_msg(
3914: p_pkg_name => G_PKG_NAME,
3915: p_procedure_name => l_api_name,
3909: ROLLBACK TO process_osp_order;
3910: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3911: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3912: THEN
3913: FND_MSG_PUB.add_exc_msg(
3914: p_pkg_name => G_PKG_NAME,
3915: p_procedure_name => l_api_name,
3916: p_error_text => SUBSTRB(SQLERRM,1,240));
3917: END IF;
3914: p_pkg_name => G_PKG_NAME,
3915: p_procedure_name => l_api_name,
3916: p_error_text => SUBSTRB(SQLERRM,1,240));
3917: END IF;
3918: FND_MSG_PUB.count_and_get(
3919: p_encoded => FND_API.G_FALSE,
3920: p_count => x_msg_count,
3921: p_data => x_msg_data);
3922: END process_osp_order;
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:
3992: -- Added by jaramana on January 10, 2008 for the Requisition ER 6034236
4011: -- Validate the combination of vendor/site/contact
4012: validate_vendor_site_contact(p_x_osp_order_rec.vendor_id,
4013: p_x_osp_order_rec.vendor_site_id,
4014: p_x_osp_order_rec.vendor_contact_id);
4015: IF FND_MSG_PUB.count_msg > 0 THEN
4016: -- Vendor/Vendor Site/Vendor Contact is Invalid
4017: RAISE FND_API.G_EXC_ERROR;
4018: END IF;
4019: -- If valid, use the input values
4023: ELSE
4024: -- Validate the vendor and vendor site
4025: validate_vendor_site(p_x_osp_order_rec.vendor_id,
4026: p_x_osp_order_rec.vendor_site_id);
4027: IF FND_MSG_PUB.count_msg > 0 THEN
4028: -- Vendor/Vendor Site/Vendor Contact is Invalid
4029: RAISE FND_API.G_EXC_ERROR;
4030: END IF;
4031: -- If valid, use the input values
4034: END IF;
4035: ELSE
4036: -- Validate the Vendor
4037: validate_vendor(p_x_osp_order_rec.vendor_id);
4038: IF FND_MSG_PUB.count_msg > 0 THEN
4039: -- Vendor/Vendor Site/Vendor Contact is Invalid
4040: RAISE FND_API.G_EXC_ERROR;
4041: END IF;
4042: -- If valid, use the input values
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;
4094: 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
4123: IF (p_x_osp_order_rec.vendor_contact_id IS NOT NULL) THEN
4124: -- Check if p_x_osp_order_rec.vendor_contact_id is valid
4125: validate_vendor_site_contact(p_x_osp_order_rec.vendor_id,
4126: p_x_osp_order_rec.vendor_site_id,
4127: p_x_osp_order_rec.vendor_contact_id);
4128: IF FND_MSG_PUB.count_msg > 0 THEN
4129: -- Vendor/Vendor Site/Vendor Contact is Invalid
4130: RAISE FND_API.G_EXC_ERROR;
4131: END IF;
4132: -- Vendor Contact is valid: Use it
4144:
4145: IF (p_x_osp_order_rec.po_agent_id IS NOT NULL) THEN
4146: -- Validate the buyer
4147: validate_buyer(p_x_osp_order_rec.po_agent_id);
4148: IF FND_MSG_PUB.count_msg > 0 THEN
4149: -- Buyer is Invalid
4150: RAISE FND_API.G_EXC_ERROR;
4151: END IF;
4152: -- Buyer is valid: Use it
4459:
4460: --yazhou 22-Aug-2006 starts
4461: -- Bug fix#5479266
4462:
4463: IF FND_MSG_PUB.count_msg > 0 THEN
4464: RAISE FND_API.G_EXC_ERROR;
4465: END IF;
4466:
4467: --yazhou 22-Aug-2006 ends
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
4542: -- bug fix#5376907
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
4569: FROM mtl_system_items_kfv
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);
4579: END IF;
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;
4844: l_oe_header_rec.osp_order_id := l_osp_order_id;
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)
5180: --po_line_id should be passed only along with the work_order_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);
5200: --Validate physical item
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);
5208: FETCH check_physical_item INTO l_check_physical_item;
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,
5218: p_x_osp_order_line_rec.sub_inventory);
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: /*
5235: Comment Added by mpothuku on 27-Feb-06: May need to revise this logic
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;
5248: END IF;
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
5251: OPEN check_lot_number(p_x_osp_order_line_rec.inventory_item_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
5261: --Subinventory_code added by mpothuku on 27-Feb-06 to fix the Perf bug #4919164
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
5275: --l_uom_code is primary_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;
5293: END IF;
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
5366: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_TYPE_NLL');
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
5372: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_UOM_NLL');
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
5378: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_QUANT_NLL');
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
5384: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_NBDATE_NLL');
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;
5390:
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
5407: --over-write the ones defined at the workorder route level
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
5438: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_NLL');
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
5443: --validate_vendor_service_item(p_x_osp_order_line_rec);
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;
5474: --yazhou 22-Aug-2006 ends
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
5489: --The cursor query contains the case which service_item_id is null
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;
5504: END IF;
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;
5638: IF (osp_order_line_csr%NOTFOUND) THEN
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
5644: IF(p_x_osp_order_line_rec.osp_order_id IS NULL OR
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
5652: p_x_osp_order_line_rec.osp_line_number <> l_osp_order_line_rec.osp_line_number) THEN
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
5659: (p_x_osp_order_line_rec.workorder_id IS NOT NULL AND l_osp_order_line_rec.workorder_id IS NULL) 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
5667: p_x_osp_order_line_rec.inventory_item_id <> l_osp_order_line_rec.inventory_item_id) THEN
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
5674: p_x_osp_order_line_rec.inventory_org_id <> l_osp_order_line_rec.inventory_org_id) THEN
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
5681: p_x_osp_order_line_rec.inventory_item_uom <> l_osp_order_line_rec.inventory_item_uom) THEN
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
5688: p_x_osp_order_line_rec.inventory_item_quantity <> l_osp_order_line_rec.inventory_item_quantity) THEN
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
5695: (p_x_osp_order_line_rec.sub_inventory IS NOT NULL AND l_osp_order_line_rec.sub_inventory IS NULL) 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
5703: (p_x_osp_order_line_rec.lot_number IS NOT NULL AND l_osp_order_line_rec.lot_number IS NULL) 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
5711: (p_x_osp_order_line_rec.serial_number IS NOT NULL AND l_osp_order_line_rec.serial_number IS NULL) 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.
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
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
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.
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
5747: -- this API does not allow to update PO line information, PO API needs to update OSP tables directly
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.
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
5767: --If the status is other than 'ENTERED', 'SUBMISSION_FAILED', 'REQ_SUBMISSION_FAILED' we should not allow updates of
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.
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.
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:
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
5812: IF(p_x_osp_order_line_rec.quantity IS NULL OR
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
5820: IF(p_x_osp_order_line_rec.need_by_date IS NULL OR
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
5828:
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');
5854: l_serial_prefix := FND_PROFILE.VALUE('AHL_OSP_POL_SER_PREFIX');
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: /*
5868: IF Overwrite Svc Description profile set to No l_item_description would have been
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
5882: FROM mtl_system_items_kfv
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);
5892: END IF;
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
5908: --changed in PO API?
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?)
5921: /* Commented out on 05/31/2005 by Jerry
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)
5929:
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
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
5968: --quantity cannot be null when UOM is not null
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
5975: --validate_vendor_service_item(p_x_osp_order_line_rec);
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
5996: */
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;
6011: 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;
6323: FETCH val_vendor_id_csr INTO l_exist;
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;
6331: FETCH get_vendor_cert INTO l_exist;
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
6339: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
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;
6345: IF(val_vendor_site_id_csr%NOTFOUND) THEN
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;
6352: FETCH get_site_cert INTO l_exist;
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
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;
6369: FETCH get_contact_cert INTO l_exist;
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;
6377: END IF;
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;
6539: ELSIF(p_x_osp_order_line_rec.osp_order_id = FND_API.G_MISS_NUM) THEN
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;
7002: CLOSE workorder_id_csr;
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;
7015: CLOSE service_item_id_csr;
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;
7028: CLOSE po_line_type_id_csr;
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;
7041: CLOSE exchange_instance_id_csr;
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;
7054: CLOSE physical_item_id_csr;
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;
7069: CLOSE osp_order_line_on_csr;
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;
7092: IF FND_MSG_PUB.count_msg > 0 THEN
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
7093: RAISE FND_API.G_EXC_ERROR;
7094: -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7095: END IF;
7096: --dbms_output.put_line('Exiting : convert_order_lines_val_to_id');
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;
7135:
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
7178: --there exists a special case where vendor_contact_id is null in
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
7207: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
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);
7235: FETCH verify_vendor INTO g_dummy_num;
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,
7243: --p_count => l_msg_count,
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,
7243: --p_count => l_msg_count,
7244: --p_data => l_msg_data);
7245: END IF;