[Home] [Help]
341: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
342: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Begin Procedure');
343: END IF;
344: 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
345: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INVOP');
346: FND_MSG_PUB.ADD;
347: END IF;
348: IF FND_MSG_PUB.count_msg > 0 THEN
349: RAISE FND_API.G_EXC_ERROR;
416: END LOOP;
417: CLOSE vendor_id_csr;
418: IF(l_count = 0 ) THEN
419: --dbms_output.put_line('checkpoint 1');
420: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_NAME_INV');
421: FND_MESSAGE.Set_Token('VENDOR_NAME', p_x_osp_order_rec.vendor_name);
422: FND_MSG_PUB.ADD;
423: ELSIF (l_count > 1) THEN
424: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_INV_NON_UNQ');
417: CLOSE vendor_id_csr;
418: IF(l_count = 0 ) THEN
419: --dbms_output.put_line('checkpoint 1');
420: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_NAME_INV');
421: FND_MESSAGE.Set_Token('VENDOR_NAME', p_x_osp_order_rec.vendor_name);
422: FND_MSG_PUB.ADD;
423: ELSIF (l_count > 1) THEN
424: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_INV_NON_UNQ');
425: FND_MESSAGE.Set_Token('VENDOR_NAME', p_x_osp_order_rec.vendor_name);
420: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_NAME_INV');
421: FND_MESSAGE.Set_Token('VENDOR_NAME', p_x_osp_order_rec.vendor_name);
422: FND_MSG_PUB.ADD;
423: ELSIF (l_count > 1) THEN
424: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_INV_NON_UNQ');
425: FND_MESSAGE.Set_Token('VENDOR_NAME', p_x_osp_order_rec.vendor_name);
426: FND_MSG_PUB.ADD;
427: ELSE
428: p_x_osp_order_rec.vendor_id := l_vendor_id;
421: FND_MESSAGE.Set_Token('VENDOR_NAME', p_x_osp_order_rec.vendor_name);
422: FND_MSG_PUB.ADD;
423: ELSIF (l_count > 1) THEN
424: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_INV_NON_UNQ');
425: FND_MESSAGE.Set_Token('VENDOR_NAME', p_x_osp_order_rec.vendor_name);
426: FND_MSG_PUB.ADD;
427: ELSE
428: p_x_osp_order_rec.vendor_id := l_vendor_id;
429: END IF;
445: END IF;
446: END LOOP;
447: CLOSE vendor_site_id_csr;
448: IF(l_count = 0 ) THEN
449: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_INV');
450: FND_MESSAGE.Set_Token('VENDOR_SITE', p_x_osp_order_rec.vendor_site_code);
451: FND_MSG_PUB.ADD;
452: ELSIF (l_count > 1) THEN
453: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_INV_NON_UNQ');
446: END LOOP;
447: CLOSE vendor_site_id_csr;
448: IF(l_count = 0 ) THEN
449: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_INV');
450: FND_MESSAGE.Set_Token('VENDOR_SITE', p_x_osp_order_rec.vendor_site_code);
451: FND_MSG_PUB.ADD;
452: ELSIF (l_count > 1) THEN
453: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_INV_NON_UNQ');
454: FND_MESSAGE.Set_Token('VENDOR_SITE', p_x_osp_order_rec.vendor_site_code);
449: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_INV');
450: FND_MESSAGE.Set_Token('VENDOR_SITE', p_x_osp_order_rec.vendor_site_code);
451: FND_MSG_PUB.ADD;
452: ELSIF (l_count > 1) THEN
453: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_INV_NON_UNQ');
454: FND_MESSAGE.Set_Token('VENDOR_SITE', p_x_osp_order_rec.vendor_site_code);
455: FND_MSG_PUB.ADD;
456: ELSE
457: p_x_osp_order_rec.vendor_site_id := l_vendor_site_id;
450: FND_MESSAGE.Set_Token('VENDOR_SITE', p_x_osp_order_rec.vendor_site_code);
451: FND_MSG_PUB.ADD;
452: ELSIF (l_count > 1) THEN
453: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_INV_NON_UNQ');
454: FND_MESSAGE.Set_Token('VENDOR_SITE', p_x_osp_order_rec.vendor_site_code);
455: FND_MSG_PUB.ADD;
456: ELSE
457: p_x_osp_order_rec.vendor_site_id := l_vendor_site_id;
458: END IF;
473: END IF;
474: END LOOP;
475: CLOSE po_agent_id_csr;
476: IF(l_count = 0 ) THEN
477: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_INV');
478: FND_MESSAGE.Set_Token('BUYER_NAME', p_x_osp_order_rec.buyer_name);
479: FND_MSG_PUB.ADD;
480: ELSIF (l_count > 1) THEN
481: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_INV_NON_UNQ');
474: END LOOP;
475: CLOSE po_agent_id_csr;
476: IF(l_count = 0 ) THEN
477: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_INV');
478: FND_MESSAGE.Set_Token('BUYER_NAME', p_x_osp_order_rec.buyer_name);
479: FND_MSG_PUB.ADD;
480: ELSIF (l_count > 1) THEN
481: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_INV_NON_UNQ');
482: FND_MESSAGE.Set_Token('BUYER_NAME', p_x_osp_order_rec.buyer_name);
477: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_INV');
478: FND_MESSAGE.Set_Token('BUYER_NAME', p_x_osp_order_rec.buyer_name);
479: FND_MSG_PUB.ADD;
480: ELSIF (l_count > 1) THEN
481: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_INV_NON_UNQ');
482: FND_MESSAGE.Set_Token('BUYER_NAME', p_x_osp_order_rec.buyer_name);
483: FND_MSG_PUB.ADD;
484: ELSE
485: p_x_osp_order_rec.po_agent_id := l_po_agent_id;
478: FND_MESSAGE.Set_Token('BUYER_NAME', p_x_osp_order_rec.buyer_name);
479: FND_MSG_PUB.ADD;
480: ELSIF (l_count > 1) THEN
481: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_INV_NON_UNQ');
482: FND_MESSAGE.Set_Token('BUYER_NAME', p_x_osp_order_rec.buyer_name);
483: FND_MSG_PUB.ADD;
484: ELSE
485: p_x_osp_order_rec.po_agent_id := l_po_agent_id;
486: END IF;
502: END IF;
503: END LOOP;
504: CLOSE customer_id_csr;
505: IF(l_count = 0 ) THEN
506: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_INV');
507: FND_MESSAGE.Set_Token('CUST_NAME', p_x_osp_order_rec.customer_name);
508: FND_MSG_PUB.ADD;
509: ELSIF (l_count > 1) THEN
510: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_INV_NON_UNQ');
503: END LOOP;
504: CLOSE customer_id_csr;
505: IF(l_count = 0 ) THEN
506: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_INV');
507: FND_MESSAGE.Set_Token('CUST_NAME', p_x_osp_order_rec.customer_name);
508: FND_MSG_PUB.ADD;
509: ELSIF (l_count > 1) THEN
510: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_INV_NON_UNQ');
511: FND_MESSAGE.Set_Token('CUST_NAME', p_x_osp_order_rec.customer_name);
506: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_INV');
507: FND_MESSAGE.Set_Token('CUST_NAME', p_x_osp_order_rec.customer_name);
508: FND_MSG_PUB.ADD;
509: ELSIF (l_count > 1) THEN
510: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_INV_NON_UNQ');
511: FND_MESSAGE.Set_Token('CUST_NAME', p_x_osp_order_rec.customer_name);
512: FND_MSG_PUB.ADD;
513: ELSE
514: p_x_osp_order_rec.customer_id := l_customer_id;
507: FND_MESSAGE.Set_Token('CUST_NAME', p_x_osp_order_rec.customer_name);
508: FND_MSG_PUB.ADD;
509: ELSIF (l_count > 1) THEN
510: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_INV_NON_UNQ');
511: FND_MESSAGE.Set_Token('CUST_NAME', p_x_osp_order_rec.customer_name);
512: FND_MSG_PUB.ADD;
513: ELSE
514: p_x_osp_order_rec.customer_id := l_customer_id;
515: END IF;
519: --dbms_output.put_line('converting contract_number to id');
520: OPEN contract_id_csr(p_x_osp_order_rec.contract_number);
521: FETCH contract_id_csr INTO l_contract_id;
522: IF(contract_id_csr%NOTFOUND) THEN
523: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CONTRACT_INV');
524: FND_MESSAGE.Set_Token('CONTRACT_NUM', p_x_osp_order_rec.contract_number);
525: FND_MSG_PUB.ADD;
526: ELSE
527: p_x_osp_order_rec.contract_id := l_contract_id;
520: OPEN contract_id_csr(p_x_osp_order_rec.contract_number);
521: FETCH contract_id_csr INTO l_contract_id;
522: IF(contract_id_csr%NOTFOUND) THEN
523: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CONTRACT_INV');
524: FND_MESSAGE.Set_Token('CONTRACT_NUM', p_x_osp_order_rec.contract_number);
525: FND_MSG_PUB.ADD;
526: ELSE
527: p_x_osp_order_rec.contract_id := l_contract_id;
528: END IF;
539: IF (p_x_osp_order_rec.osp_order_number IS NOT NULL) THEN
540: OPEN osp_order_on_csr(p_x_osp_order_rec.osp_order_number);
541: FETCH osp_order_on_csr INTO l_osp_order_id,l_object_version_number;
542: IF(osp_order_on_csr%NOTFOUND) THEN
543: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_NUM_INV');
544: FND_MESSAGE.Set_Token('ORDER_NUMBER', p_x_osp_order_rec.osp_order_number);
545: FND_MSG_PUB.ADD;
546: ELSE
547: p_x_osp_order_rec.osp_order_id := l_osp_order_id;
540: OPEN osp_order_on_csr(p_x_osp_order_rec.osp_order_number);
541: FETCH osp_order_on_csr INTO l_osp_order_id,l_object_version_number;
542: IF(osp_order_on_csr%NOTFOUND) THEN
543: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_NUM_INV');
544: FND_MESSAGE.Set_Token('ORDER_NUMBER', p_x_osp_order_rec.osp_order_number);
545: FND_MSG_PUB.ADD;
546: ELSE
547: p_x_osp_order_rec.osp_order_id := l_osp_order_id;
548: p_x_osp_order_rec.object_version_number := l_object_version_number;
553: ELSIF (p_x_osp_order_rec.po_header_id IS NOT NULL AND p_x_osp_order_rec.po_header_id <> FND_API.G_MISS_NUM) THEN
554: OPEN osp_order_po_csr(p_x_osp_order_rec.po_header_id);
555: FETCH osp_order_po_csr INTO l_osp_order_id,l_object_version_number;
556: IF(osp_order_po_csr%NOTFOUND) THEN
557: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PO_HEADER_INV');
558: FND_MESSAGE.Set_Token('PO_HEADER_ID', p_x_osp_order_rec.po_header_id);
559: FND_MSG_PUB.ADD;
560: ELSE
561: p_x_osp_order_rec.osp_order_id := l_osp_order_id;
554: OPEN osp_order_po_csr(p_x_osp_order_rec.po_header_id);
555: FETCH osp_order_po_csr INTO l_osp_order_id,l_object_version_number;
556: IF(osp_order_po_csr%NOTFOUND) THEN
557: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PO_HEADER_INV');
558: FND_MESSAGE.Set_Token('PO_HEADER_ID', p_x_osp_order_rec.po_header_id);
559: FND_MSG_PUB.ADD;
560: ELSE
561: p_x_osp_order_rec.osp_order_id := l_osp_order_id;
562: p_x_osp_order_rec.object_version_number := l_object_version_number;
565: ELSIF (p_x_osp_order_rec.oe_header_id IS NOT NULL AND p_x_osp_order_rec.oe_header_id <> FND_API.G_MISS_NUM) THEN
566: OPEN osp_order_oe_csr(p_x_osp_order_rec.oe_header_id);
567: FETCH osp_order_oe_csr INTO l_osp_order_id,l_object_version_number;
568: IF(osp_order_oe_csr%NOTFOUND) THEN
569: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_OE_HEADER_INV');
570: FND_MESSAGE.Set_Token('OE_HEADER_ID', p_x_osp_order_rec.oe_header_id);
571: FND_MSG_PUB.ADD;
572: ELSE
573: p_x_osp_order_rec.osp_order_id := l_osp_order_id;
566: OPEN osp_order_oe_csr(p_x_osp_order_rec.oe_header_id);
567: FETCH osp_order_oe_csr INTO l_osp_order_id,l_object_version_number;
568: IF(osp_order_oe_csr%NOTFOUND) THEN
569: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_OE_HEADER_INV');
570: FND_MESSAGE.Set_Token('OE_HEADER_ID', p_x_osp_order_rec.oe_header_id);
571: FND_MSG_PUB.ADD;
572: ELSE
573: p_x_osp_order_rec.osp_order_id := l_osp_order_id;
574: p_x_osp_order_rec.object_version_number := l_object_version_number;
576: CLOSE osp_order_oe_csr;
577: */
578: -- jaramana End
579: ELSE
580: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_HEADER_INV');
581: FND_MSG_PUB.ADD;
582: END IF;
583: END IF;
584: END IF;
616: IF(p_vendor_id IS NOT NULL AND p_vendor_id <> FND_API.G_MISS_NUM) THEN
617: OPEN val_vendor_id_csr(p_vendor_id);
618: FETCH val_vendor_id_csr INTO l_exist;
619: IF(val_vendor_id_csr%NOTFOUND) THEN
620: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
621: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
622: FND_MSG_PUB.ADD;
623: END IF;
624: CLOSE val_vendor_id_csr;
617: OPEN val_vendor_id_csr(p_vendor_id);
618: FETCH val_vendor_id_csr INTO l_exist;
619: IF(val_vendor_id_csr%NOTFOUND) THEN
620: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
621: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
622: FND_MSG_PUB.ADD;
623: END IF;
624: CLOSE val_vendor_id_csr;
625: OPEN get_vendor_cert;
624: CLOSE val_vendor_id_csr;
625: OPEN get_vendor_cert;
626: FETCH get_vendor_cert INTO l_exist;
627: IF(get_vendor_cert%NOTFOUND) THEN
628: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
629: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
630: FND_MSG_PUB.ADD;
631: END IF;
632: CLOSE get_vendor_cert;
625: OPEN get_vendor_cert;
626: FETCH get_vendor_cert INTO l_exist;
627: IF(get_vendor_cert%NOTFOUND) THEN
628: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
629: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
630: FND_MSG_PUB.ADD;
631: END IF;
632: CLOSE get_vendor_cert;
633: END IF;
659: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Begin Procedure');
660: END IF;
661: IF (p_vendor_id IS NULL OR p_vendor_id = FND_API.G_MISS_NUM) THEN
662: IF(p_vendor_site_id IS NOT NULL AND p_vendor_site_id <> FND_API.G_MISS_NUM) THEN
663: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VEND_ID_NLL');
664: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
665: FND_MSG_PUB.ADD;
666: END IF;
667: ELSE
660: END IF;
661: IF (p_vendor_id IS NULL OR p_vendor_id = FND_API.G_MISS_NUM) THEN
662: IF(p_vendor_site_id IS NOT NULL AND p_vendor_site_id <> FND_API.G_MISS_NUM) THEN
663: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VEND_ID_NLL');
664: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
665: FND_MSG_PUB.ADD;
666: END IF;
667: ELSE
668: IF(p_vendor_site_id IS NOT NULL AND p_vendor_site_id <> FND_API.G_MISS_NUM)THEN
668: IF(p_vendor_site_id IS NOT NULL AND p_vendor_site_id <> FND_API.G_MISS_NUM)THEN
669: OPEN val_vendor_site_id_csr(p_vendor_id, p_vendor_site_id);
670: FETCH val_vendor_site_id_csr INTO l_exist;
671: IF(val_vendor_site_id_csr%NOTFOUND) THEN
672: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
673: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
674: FND_MSG_PUB.ADD;
675: END IF;
676: CLOSE val_vendor_site_id_csr;
669: OPEN val_vendor_site_id_csr(p_vendor_id, p_vendor_site_id);
670: FETCH val_vendor_site_id_csr INTO l_exist;
671: IF(val_vendor_site_id_csr%NOTFOUND) THEN
672: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
673: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
674: FND_MSG_PUB.ADD;
675: END IF;
676: CLOSE val_vendor_site_id_csr;
677: OPEN get_vendor_cert;
676: CLOSE val_vendor_site_id_csr;
677: OPEN get_vendor_cert;
678: FETCH get_vendor_cert INTO l_exist;
679: IF(get_vendor_cert%NOTFOUND) THEN
680: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
681: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
682: FND_MSG_PUB.ADD;
683: END IF;
684: CLOSE get_vendor_cert;
677: OPEN get_vendor_cert;
678: FETCH get_vendor_cert INTO l_exist;
679: IF(get_vendor_cert%NOTFOUND) THEN
680: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
681: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
682: FND_MSG_PUB.ADD;
683: END IF;
684: CLOSE get_vendor_cert;
685: END IF;
705: IF(p_customer_id IS NOT NULL AND p_customer_id <> FND_API.G_MISS_NUM) THEN
706: OPEN val_customer_id_csr(p_customer_id);
707: FETCH val_customer_id_csr INTO l_exist;
708: IF(val_customer_id_csr%NOTFOUND) THEN
709: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_ID_INV');
710: FND_MESSAGE.Set_Token('CUSTOMER_ID', p_customer_id);
711: FND_MSG_PUB.ADD;
712: END IF;
713: CLOSE val_customer_id_csr;
706: OPEN val_customer_id_csr(p_customer_id);
707: FETCH val_customer_id_csr INTO l_exist;
708: IF(val_customer_id_csr%NOTFOUND) THEN
709: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_ID_INV');
710: FND_MESSAGE.Set_Token('CUSTOMER_ID', p_customer_id);
711: FND_MSG_PUB.ADD;
712: END IF;
713: CLOSE val_customer_id_csr;
714: END IF;
733: IF(p_po_agent_id IS NOT NULL AND p_po_agent_id <> FND_API.G_MISS_NUM) THEN
734: OPEN val_po_agent_id_csr(p_po_agent_id);
735: FETCH val_po_agent_id_csr INTO l_exist;
736: IF(val_po_agent_id_csr%NOTFOUND) THEN
737: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_ID_INV');
738: FND_MESSAGE.Set_Token('BUYER_ID', p_po_agent_id);
739: FND_MSG_PUB.ADD;
740: END IF;
741: CLOSE val_po_agent_id_csr;
734: OPEN val_po_agent_id_csr(p_po_agent_id);
735: FETCH val_po_agent_id_csr INTO l_exist;
736: IF(val_po_agent_id_csr%NOTFOUND) THEN
737: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_ID_INV');
738: FND_MESSAGE.Set_Token('BUYER_ID', p_po_agent_id);
739: FND_MSG_PUB.ADD;
740: END IF;
741: CLOSE val_po_agent_id_csr;
742: END IF;
774: ELSIF (p_order_type_code = G_OSP_ORDER_TYPE_LOAN) THEN
775: l_buy_or_sell := 'S';
776: l_object_code := 'OKX_PARTY';
777: ELSE
778: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_CTRCT');
779: FND_MSG_PUB.ADD;
780: RETURN;
781: END IF;
782: IF(p_party_vendor_id IS NULL OR p_party_vendor_id = FND_API.G_MISS_NUM) THEN
780: RETURN;
781: END IF;
782: IF(p_party_vendor_id IS NULL OR p_party_vendor_id = FND_API.G_MISS_NUM) THEN
783: IF(p_contract_id IS NOT NULL AND p_contract_id <> FND_API.G_MISS_NUM) THEN
784: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VEND_PTY_ID_NLL');
785: FND_MESSAGE.Set_Token('CONTRACT_ID', p_contract_id);
786: FND_MSG_PUB.ADD;
787: END IF;
788: ELSE
781: END IF;
782: IF(p_party_vendor_id IS NULL OR p_party_vendor_id = FND_API.G_MISS_NUM) THEN
783: IF(p_contract_id IS NOT NULL AND p_contract_id <> FND_API.G_MISS_NUM) THEN
784: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VEND_PTY_ID_NLL');
785: FND_MESSAGE.Set_Token('CONTRACT_ID', p_contract_id);
786: FND_MSG_PUB.ADD;
787: END IF;
788: ELSE
789: IF(p_contract_id IS NOT NULL AND p_contract_id <> FND_API.G_MISS_NUM) THEN
789: IF(p_contract_id IS NOT NULL AND p_contract_id <> FND_API.G_MISS_NUM) THEN
790: OPEN val_contract_id_csr(p_contract_id, p_party_vendor_id, l_buy_or_sell, p_authoring_org_id, l_object_code);
791: FETCH val_contract_id_csr INTO l_exist;
792: IF(val_contract_id_csr%NOTFOUND) THEN
793: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CTRCT');
794: FND_MESSAGE.Set_Token('CONTRACT_ID', p_contract_id);
795: FND_MSG_PUB.ADD;
796: END IF;
797: CLOSE val_contract_id_csr;
790: OPEN val_contract_id_csr(p_contract_id, p_party_vendor_id, l_buy_or_sell, p_authoring_org_id, l_object_code);
791: FETCH val_contract_id_csr INTO l_exist;
792: IF(val_contract_id_csr%NOTFOUND) THEN
793: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CTRCT');
794: FND_MESSAGE.Set_Token('CONTRACT_ID', p_contract_id);
795: FND_MSG_PUB.ADD;
796: END IF;
797: CLOSE val_contract_id_csr;
798: END IF;
818: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
819: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Begin Procedure');
820: END IF;
821: IF(p_osp_order_id IS NULL OR p_osp_order_id = FND_API.G_MISS_NUM) THEN
822: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_HEADER_INV');
823: FND_MSG_PUB.ADD;
824: RETURN;
825: END IF;
826: IF(p_po_header_id IS NOT NULL AND p_po_header_id <> FND_API.G_MISS_NUM) THEN
826: IF(p_po_header_id IS NOT NULL AND p_po_header_id <> FND_API.G_MISS_NUM) THEN
827: OPEN val_po_header_id_csr(p_po_header_id, p_osp_order_id);
828: FETCH val_po_header_id_csr INTO l_exist;
829: IF(val_po_header_id_csr%NOTFOUND) THEN
830: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PO_HEADER_INV');
831: FND_MESSAGE.Set_Token('PO_HEADER_ID', p_po_header_id);
832: FND_MSG_PUB.ADD;
833: END IF;
834: CLOSE val_po_header_id_csr;
827: OPEN val_po_header_id_csr(p_po_header_id, p_osp_order_id);
828: FETCH val_po_header_id_csr INTO l_exist;
829: IF(val_po_header_id_csr%NOTFOUND) THEN
830: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PO_HEADER_INV');
831: FND_MESSAGE.Set_Token('PO_HEADER_ID', p_po_header_id);
832: FND_MSG_PUB.ADD;
833: END IF;
834: CLOSE val_po_header_id_csr;
835: END IF;
887: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Begin Procedure');
888: END IF;
889: l_operating_unit_id := mo_global.get_current_org_id();
890: IF (l_operating_unit_id IS NULL) THEN
891: FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_ORG_NOT_SET');
892: FND_MSG_PUB.ADD;
893: END IF;
894: IF FND_MSG_PUB.count_msg > 0 THEN
895: RAISE FND_API.G_EXC_ERROR;
902: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
903: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '.notCreate', 'operation is not create, it is:'||p_x_osp_order_rec.operation_flag);
904: END IF;
905: IF(p_x_osp_order_rec.osp_order_id IS NULL OR p_x_osp_order_rec.object_version_number IS NULL) THEN
906: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_OBJ_MISS');
907: FND_MSG_PUB.ADD;
908: ELSE
909: -- fetch record and verify the latest record is being dealt with.
910: OPEN osp_order_csr(p_x_osp_order_rec.osp_order_id, p_x_osp_order_rec.object_version_number);
920: 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);
921: END IF;
922: -- if record not found, raise error and declare that record has been modified.
923: IF (osp_order_csr%NOTFOUND) THEN
924: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INVOP_OSP_NFOUND');
925: FND_MSG_PUB.ADD;
926: END IF;
927: CLOSE osp_order_csr;
928: -- check existing status. If closed, raise error.
926: END IF;
927: CLOSE osp_order_csr;
928: -- check existing status. If closed, raise error.
929: IF(l_osp_order_rec.status_code = G_OSP_CLOSED_STATUS) THEN
930: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
931: FND_MSG_PUB.ADD;
932: ELSE
933: g_old_status_code := l_osp_order_rec.status_code;
934: --g_order_status_for_update := l_osp_order_rec.status_code;
938: -- Added by jaramana on January 8, 2008 for the Requisition ER 6034236
939: -- User should not pass G_OSP_REQ_SUBMITTED_STATUS directly. User need to pass G_OSP_SUBMITTED_STATUS and
940: -- depening on the 'Initialize Purchase Requisition' profile we deduce whether Requisition needs to be created
941: 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
942: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
943: FND_MSG_PUB.ADD;
944: END IF;
945:
946: --If the Profile 'Initialize Purchase Requisition' is set, then set the status to G_OSP_REQ_SUBMITTED_STATUS
968: -- jaramana End
969: g_old_type_code := l_osp_order_rec.order_type_code;
970: g_new_type_code := p_x_osp_order_rec.order_type_code;
971: ELSE
972: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_TYPE');
973: FND_MSG_PUB.ADD;
974: END IF;
975: ELSE
976: p_x_osp_order_rec.order_type_code := l_osp_order_rec.order_type_code;
976: p_x_osp_order_rec.order_type_code := l_osp_order_rec.order_type_code;
977: END IF;
978: -- osp order number can not be changed.
979: 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
980: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_NUM_INV');
981: FND_MESSAGE.Set_Token('ORDER_NUMBER', p_x_osp_order_rec.osp_order_number);
982: FND_MSG_PUB.ADD;
983: END IF;
984: -- order date can not be changed.
977: END IF;
978: -- osp order number can not be changed.
979: 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
980: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_NUM_INV');
981: FND_MESSAGE.Set_Token('ORDER_NUMBER', p_x_osp_order_rec.osp_order_number);
982: FND_MSG_PUB.ADD;
983: END IF;
984: -- order date can not be changed.
985: 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
982: FND_MSG_PUB.ADD;
983: END IF;
984: -- order date can not be changed.
985: 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
986: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_ORD_DT');
987: FND_MSG_PUB.ADD;
988: END IF;
989: -- operating_unit_id can not change
990: 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
987: FND_MSG_PUB.ADD;
988: END IF;
989: -- operating_unit_id can not change
990: 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
991: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_OPUNIT');
992: FND_MSG_PUB.ADD;
993: END IF;
994:
995:
1009: END IF;
1010: */
1011: -- contract terms should be null.
1012: 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
1013: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_CO');
1014: FND_MSG_PUB.ADD;
1015: END IF;
1016: -- shipping info is not allowed to update in this API
1017: IF(p_x_osp_order_rec.oe_header_id IS NOT NULL ) THEN
1015: END IF;
1016: -- shipping info is not allowed to update in this API
1017: IF(p_x_osp_order_rec.oe_header_id IS NOT NULL ) THEN
1018: 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
1019: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_OE'); FND_MSG_PUB.ADD;
1020: ELSIF(p_x_osp_order_rec.oe_header_id <> l_osp_order_rec.oe_header_id) THEN
1021: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_OE');
1022: FND_MSG_PUB.ADD;
1023: END IF;
1017: IF(p_x_osp_order_rec.oe_header_id IS NOT NULL ) THEN
1018: 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
1019: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_OE'); FND_MSG_PUB.ADD;
1020: ELSIF(p_x_osp_order_rec.oe_header_id <> l_osp_order_rec.oe_header_id) THEN
1021: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_OE');
1022: FND_MSG_PUB.ADD;
1023: END IF;
1024: END IF;
1025: END IF;
1033: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1034: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '.create', 'operation is create');
1035: END IF;
1036: 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
1037: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_NNLL');
1038: FND_MSG_PUB.ADD;
1039: END IF;
1040: 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
1041: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_OBJV_NNLL');
1037: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_NNLL');
1038: FND_MSG_PUB.ADD;
1039: END IF;
1040: 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
1041: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_OBJV_NNLL');
1042: FND_MSG_PUB.ADD;
1043: END IF;
1044: -- populate order number
1045: 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
1042: FND_MSG_PUB.ADD;
1043: END IF;
1044: -- populate order number
1045: 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
1046: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_NUM_INV');
1047: FND_MESSAGE.Set_Token('ORDER_NUMBER', p_x_osp_order_rec.osp_order_number);
1048: FND_MSG_PUB.ADD;
1049: END IF;
1050: -- populate order_date
1043: END IF;
1044: -- populate order number
1045: 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
1046: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_NUM_INV');
1047: FND_MESSAGE.Set_Token('ORDER_NUMBER', p_x_osp_order_rec.osp_order_number);
1048: FND_MSG_PUB.ADD;
1049: END IF;
1050: -- populate order_date
1051: 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
1049: END IF;
1050: -- populate order_date
1051: 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
1052: IF(TRUNC(p_x_osp_order_rec.order_date) <> TRUNC(SYSDATE)) THEN
1053: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_ORD_DAT');
1054: FND_MESSAGE.Set_Token('ORDER_DATE', p_x_osp_order_rec.order_date);
1055: FND_MSG_PUB.ADD;
1056: END IF;
1057: ELSE
1050: -- populate order_date
1051: 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
1052: IF(TRUNC(p_x_osp_order_rec.order_date) <> TRUNC(SYSDATE)) THEN
1053: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_ORD_DAT');
1054: FND_MESSAGE.Set_Token('ORDER_DATE', p_x_osp_order_rec.order_date);
1055: FND_MSG_PUB.ADD;
1056: END IF;
1057: ELSE
1058: p_x_osp_order_rec.order_date := TRUNC(SYSDATE);
1059: END IF;
1060: -- populate operating_unit_id
1061: 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
1062: IF(p_x_osp_order_rec.operating_unit_id <> l_operating_unit_id) THEN
1063: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_OP_UNIT');
1064: FND_MSG_PUB.ADD;
1065: END IF;
1066: ELSE
1067: p_x_osp_order_rec.operating_unit_id := l_operating_unit_id;
1067: p_x_osp_order_rec.operating_unit_id := l_operating_unit_id;
1068: END IF;
1069: -- po_header_id, oe_header_id, po_batch_id, po_request_id, po_interface_header_id, contract_terms should be null for CREATE.
1070: 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
1071: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1072: FND_MSG_PUB.ADD;
1073: END IF;
1074: 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
1075: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_OE_NNLL');
1071: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1072: FND_MSG_PUB.ADD;
1073: END IF;
1074: 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
1075: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_OE_NNLL');
1076: FND_MSG_PUB.ADD;
1077: END IF;
1078: 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
1079: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1075: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_OE_NNLL');
1076: FND_MSG_PUB.ADD;
1077: END IF;
1078: 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
1079: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1080: FND_MSG_PUB.ADD;
1081: END IF;
1082: 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
1083: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1079: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1080: FND_MSG_PUB.ADD;
1081: END IF;
1082: 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
1083: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1084: FND_MSG_PUB.ADD;
1085: END IF;
1086: 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
1087: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1083: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1084: FND_MSG_PUB.ADD;
1085: END IF;
1086: 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
1087: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1088: FND_MSG_PUB.ADD;
1089: END IF;
1090: -- status_code should be null or 'ENTERED'. IF null, default to 'ENTERED'
1091: IF(p_x_osp_order_rec.status_code IS NOT NULL AND p_x_osp_order_rec.status_code <> FND_API.G_MISS_CHAR
1089: END IF;
1090: -- status_code should be null or 'ENTERED'. IF null, default to 'ENTERED'
1091: IF(p_x_osp_order_rec.status_code IS NOT NULL AND p_x_osp_order_rec.status_code <> FND_API.G_MISS_CHAR
1092: AND p_x_osp_order_rec.status_code <> G_OSP_ENTERED_STATUS) THEN
1093: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1094: FND_MSG_PUB.ADD;
1095: ELSE
1096: p_x_osp_order_rec.status_code := G_OSP_ENTERED_STATUS;
1097: g_old_status_code := G_OSP_ENTERED_STATUS;
1113: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '.type', 'Update Order type is Service or Exchange');
1114: END IF;
1115: -- contract_id and customer_id must be null for SERVICE or EXCHANGE
1116: 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
1117: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_CTRCT');
1118: FND_MESSAGE.Set_Token('CONTRACT_ID', p_x_osp_order_rec.contract_id);
1119: FND_MSG_PUB.ADD;
1120: END IF;
1121: 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
1114: END IF;
1115: -- contract_id and customer_id must be null for SERVICE or EXCHANGE
1116: 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
1117: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_CTRCT');
1118: FND_MESSAGE.Set_Token('CONTRACT_ID', p_x_osp_order_rec.contract_id);
1119: FND_MSG_PUB.ADD;
1120: END IF;
1121: 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
1122: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_ID_INV');
1118: FND_MESSAGE.Set_Token('CONTRACT_ID', p_x_osp_order_rec.contract_id);
1119: FND_MSG_PUB.ADD;
1120: END IF;
1121: 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
1122: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_ID_INV');
1123: FND_MESSAGE.Set_Token('CUSTOMER_ID', p_x_osp_order_rec.customer_id);
1124: FND_MSG_PUB.ADD;
1125: END IF;
1126: 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
1119: FND_MSG_PUB.ADD;
1120: END IF;
1121: 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
1122: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUST_ID_INV');
1123: FND_MESSAGE.Set_Token('CUSTOMER_ID', p_x_osp_order_rec.customer_id);
1124: FND_MSG_PUB.ADD;
1125: END IF;
1126: 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
1127: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_CO');
1123: FND_MESSAGE.Set_Token('CUSTOMER_ID', p_x_osp_order_rec.customer_id);
1124: FND_MSG_PUB.ADD;
1125: END IF;
1126: 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
1127: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_CO');
1128: FND_MSG_PUB.ADD;
1129: END IF;
1130: ELSIF (p_x_osp_order_rec.order_type_code IN( G_OSP_ORDER_TYPE_LOAN,G_OSP_ORDER_TYPE_BORROW) ) THEN
1131: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1132: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '.type', 'Update Order type is Loan or Borrow');
1133: END IF;
1134: -- po_agent_id,vendor_id(FOR LOAN only) and vendor_site_id must be null.
1135: 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
1136: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_ID_INV');
1137: FND_MESSAGE.Set_Token('BUYER_ID', p_x_osp_order_rec.po_agent_id);
1138: FND_MSG_PUB.ADD;
1139: END IF;
1140: 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
1133: END IF;
1134: -- po_agent_id,vendor_id(FOR LOAN only) and vendor_site_id must be null.
1135: 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
1136: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_ID_INV');
1137: FND_MESSAGE.Set_Token('BUYER_ID', p_x_osp_order_rec.po_agent_id);
1138: FND_MSG_PUB.ADD;
1139: END IF;
1140: 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
1141: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
1137: FND_MESSAGE.Set_Token('BUYER_ID', p_x_osp_order_rec.po_agent_id);
1138: FND_MSG_PUB.ADD;
1139: END IF;
1140: 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
1141: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
1142: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_x_osp_order_rec.vendor_site_id);
1143: FND_MSG_PUB.ADD;
1144: END IF;
1145: IF(p_x_osp_order_rec.order_type_code = G_OSP_ORDER_TYPE_LOAN) THEN
1138: FND_MSG_PUB.ADD;
1139: END IF;
1140: 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
1141: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
1142: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_x_osp_order_rec.vendor_site_id);
1143: FND_MSG_PUB.ADD;
1144: END IF;
1145: IF(p_x_osp_order_rec.order_type_code = G_OSP_ORDER_TYPE_LOAN) THEN
1146: 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
1143: FND_MSG_PUB.ADD;
1144: END IF;
1145: IF(p_x_osp_order_rec.order_type_code = G_OSP_ORDER_TYPE_LOAN) THEN
1146: 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
1147: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
1148: FND_MESSAGE.Set_Token('VENDOR_ID', p_x_osp_order_rec.vendor_id);
1149: FND_MSG_PUB.ADD;
1150: END IF;
1151: END IF;
1144: END IF;
1145: IF(p_x_osp_order_rec.order_type_code = G_OSP_ORDER_TYPE_LOAN) THEN
1146: 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
1147: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
1148: FND_MESSAGE.Set_Token('VENDOR_ID', p_x_osp_order_rec.vendor_id);
1149: FND_MSG_PUB.ADD;
1150: END IF;
1151: END IF;
1152: -- po_header_id, po_batch_id, po_request_id, po_interface_header_id, contract_terms should be null
1150: END IF;
1151: END IF;
1152: -- po_header_id, po_batch_id, po_request_id, po_interface_header_id, contract_terms should be null
1153: 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
1154: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1155: FND_MSG_PUB.ADD;
1156: END IF;
1157: 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
1158: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1154: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1155: FND_MSG_PUB.ADD;
1156: END IF;
1157: 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
1158: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1159: FND_MSG_PUB.ADD;
1160: END IF;
1161: 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
1162: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1158: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1159: FND_MSG_PUB.ADD;
1160: END IF;
1161: 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
1162: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1163: FND_MSG_PUB.ADD;
1164: END IF;
1165: 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
1166: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1162: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1163: FND_MSG_PUB.ADD;
1164: END IF;
1165: 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
1166: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1167: FND_MSG_PUB.ADD;
1168: END IF;
1169:
1170: -- Added by jaramana on January 8, 2008 for the Requisition ER 6034236
1168: END IF;
1169:
1170: -- Added by jaramana on January 8, 2008 for the Requisition ER 6034236
1171: 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
1172: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_REQ_NNLL');
1173: FND_MSG_PUB.ADD;
1174: END IF;
1175: -- jaramana End
1176:
1174: END IF;
1175: -- jaramana End
1176:
1177: 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
1178: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_CO');
1179: FND_MSG_PUB.ADD;
1180: END IF;
1181:
1182: END IF;
1199: --set single_instance_flag
1200: IF(p_x_osp_order_rec.single_instance_flag IS NULL OR p_x_osp_order_rec.single_instance_flag = FND_API.G_MISS_CHAR)THEN
1201: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1202: ELSIF (p_x_osp_order_rec.single_instance_flag NOT IN(G_NO_FLAG,G_YES_FLAG))THEN
1203: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1204: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1205: FND_MSG_PUB.ADD;
1206: END IF;
1207: --dbms_output.put_line('validate vendor');
1200: IF(p_x_osp_order_rec.single_instance_flag IS NULL OR p_x_osp_order_rec.single_instance_flag = FND_API.G_MISS_CHAR)THEN
1201: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1202: ELSIF (p_x_osp_order_rec.single_instance_flag NOT IN(G_NO_FLAG,G_YES_FLAG))THEN
1203: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1204: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1205: FND_MSG_PUB.ADD;
1206: END IF;
1207: --dbms_output.put_line('validate vendor');
1208:
1207: --dbms_output.put_line('validate vendor');
1208:
1209: -- validate vendor_id.
1210: IF(p_x_osp_order_rec.vendor_id IS NULL OR p_x_osp_order_rec.vendor_id = FND_API.G_MISS_NUM) THEN
1211: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_NLL');
1212: FND_MSG_PUB.ADD;
1213: ELSE
1214: validate_vendor(p_x_osp_order_rec.vendor_id);
1215: END IF;
1215: END IF;
1216: --dbms_output.put_line('validate vendor site');
1217: -- validate vendor_site_id.
1218: 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
1219: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_NLL');
1220: FND_MSG_PUB.ADD;
1221: ELSE
1222: validate_vendor_site(p_x_osp_order_rec.vendor_id, p_x_osp_order_rec.vendor_site_id);
1223: END IF;
1227: p_x_osp_order_rec.vendor_contact_id);
1228: -- validate po_agent_id.
1229: --dbms_output.put_line('validate buyer');
1230: 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
1231: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_ID_NLL');
1232: FND_MSG_PUB.ADD;
1233: ELSE
1234: validate_buyer(p_x_osp_order_rec.po_agent_id);
1235: END IF;
1247: || 'g_old_status_code' || g_old_status_code);
1248: END IF;
1249: -- po_header_id should be null.
1250: 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
1251: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1252: FND_MSG_PUB.ADD;
1253: END IF;
1254: -- Modified by jaramana on January 9, 2008 for the Requisition ER 6034236
1255: -- req_header_id should be null.
1253: END IF;
1254: -- Modified by jaramana on January 9, 2008 for the Requisition ER 6034236
1255: -- req_header_id should be null.
1256: 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
1257: FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_OSP_ORD_REQ_NNLL');
1258: FND_MSG_PUB.ADD;
1259: END IF;
1260: -- jaramana End
1261:
1263: -- status_code should be null,'ENTERED', 'SUBMISSION_FAILED' or 'SUBMITTED'
1264: /*
1265: IF(p_x_osp_order_rec.status_code IS NOT NULL AND
1266: p_x_osp_order_rec.status_code NOT IN(G_OSP_ENTERED_STATUS, G_OSP_SUB_FAILED_STATUS, G_OSP_SUBMITTED_STATUS)) THEN
1267: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1268: FND_MSG_PUB.ADD;
1269: ELSIF (g_old_status_code = G_OSP_ENTERED_STATUS AND p_x_osp_order_rec.status_code = G_OSP_SUB_FAILED_STATUS) THEN
1270: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1271: FND_MSG_PUB.ADD;
1266: p_x_osp_order_rec.status_code NOT IN(G_OSP_ENTERED_STATUS, G_OSP_SUB_FAILED_STATUS, G_OSP_SUBMITTED_STATUS)) THEN
1267: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1268: FND_MSG_PUB.ADD;
1269: ELSIF (g_old_status_code = G_OSP_ENTERED_STATUS AND p_x_osp_order_rec.status_code = G_OSP_SUB_FAILED_STATUS) THEN
1270: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1271: FND_MSG_PUB.ADD;
1272: ELSIF (g_old_status_code = G_OSP_SUB_FAILED_STATUS AND p_x_osp_order_rec.status_code = G_OSP_ENTERED_STATUS) THEN
1273: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1274: FND_MSG_PUB.ADD;
1269: ELSIF (g_old_status_code = G_OSP_ENTERED_STATUS AND p_x_osp_order_rec.status_code = G_OSP_SUB_FAILED_STATUS) THEN
1270: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1271: FND_MSG_PUB.ADD;
1272: ELSIF (g_old_status_code = G_OSP_SUB_FAILED_STATUS AND p_x_osp_order_rec.status_code = G_OSP_ENTERED_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: /*
1290: In all the above the changes status is either itself or G_OSP_SUBMITTED_STATUS or G_OSP_REQ_SUBMITTED_STATUS
1291: */
1292:
1293: 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
1294: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1295: FND_MSG_PUB.ADD;
1296: END IF;
1297:
1298: --
1299: --set single_instance_flag
1300: IF(p_x_osp_order_rec.single_instance_flag = FND_API.G_MISS_CHAR)THEN
1301: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1302: 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
1303: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1304: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1305: FND_MSG_PUB.ADD;
1306: END IF;
1307: /*
1300: IF(p_x_osp_order_rec.single_instance_flag = FND_API.G_MISS_CHAR)THEN
1301: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1302: 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
1303: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1304: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1305: FND_MSG_PUB.ADD;
1306: END IF;
1307: /*
1308: -- validate vendor_id
1306: END IF;
1307: /*
1308: -- validate vendor_id
1309: 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
1310: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_NLL');
1311: FND_MSG_PUB.ADD;
1312: ELSIF (p_x_osp_order_rec.vendor_id IS NOT NULL) THEN
1313: validate_vendor(p_x_osp_order_rec.vendor_id);
1314: END IF;
1313: validate_vendor(p_x_osp_order_rec.vendor_id);
1314: END IF;
1315: -- validate vendor_site_id.
1316: 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
1317: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_NLL');
1318: FND_MSG_PUB.ADD;
1319: ELSE
1320: p_x_osp_order_rec.vendor_site_id := NVL(p_x_osp_order_rec.vendor_site_id,l_osp_order_rec.vendor_site_id);
1321: p_x_osp_order_rec.vendor_id := NVL(p_x_osp_order_rec.vendor_id, l_osp_order_rec.vendor_id);
1324: */
1325: --When updating OSP order header, vendor_id is required
1326: --G_MISS/Null conversion has already been made in default_unchanged_order_header
1327: IF(p_x_osp_order_rec.vendor_id IS NULL) THEN
1328: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_NLL');
1329: FND_MSG_PUB.ADD;
1330: END IF;
1331:
1332: --When updating OSP order header, vendor_site_id is required
1331:
1332: --When updating OSP order header, vendor_site_id is required
1333: --G_MISS/Null conversion has already been made in default_unchanged_order_header
1334: IF(p_x_osp_order_rec.vendor_site_id IS NULL) THEN
1335: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_NLL');
1336: FND_MSG_PUB.ADD;
1337: END IF;
1338:
1339: validate_vendor_site_contact(p_x_osp_order_rec.vendor_id,
1343: --dbms_output.put_line('validate buyer in update');
1344: --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
1345: --G_MISS/Null conversion has already been made in default_unchanged_order_header
1346: IF(p_x_osp_order_rec.po_agent_id IS NULL) THEN
1347: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_ID_NLL');
1348: FND_MSG_PUB.ADD;
1349: ELSIF (p_x_osp_order_rec.po_agent_id IS NOT NULL) THEN
1350: validate_buyer(p_x_osp_order_rec.po_agent_id);
1351: END IF;
1360: p_x_osp_order_rec.status_code IN (G_OSP_SUBMITTED_STATUS,G_OSP_REQ_SUBMITTED_STATUS)) THEN
1361: OPEN chk_requisition_exists_csr(p_x_osp_order_rec.osp_order_id);
1362: FETCH chk_requisition_exists_csr INTO l_req_num;
1363: IF (chk_requisition_exists_csr%FOUND) THEN
1364: FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_OSP_REQ_NOT_DELETED');
1365: FND_MESSAGE.Set_Token('REQ_NUM', l_req_num);
1366: FND_MSG_PUB.ADD;
1367: END IF;
1368: CLOSE chk_requisition_exists_csr;
1361: OPEN chk_requisition_exists_csr(p_x_osp_order_rec.osp_order_id);
1362: FETCH chk_requisition_exists_csr INTO l_req_num;
1363: IF (chk_requisition_exists_csr%FOUND) THEN
1364: FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_OSP_REQ_NOT_DELETED');
1365: FND_MESSAGE.Set_Token('REQ_NUM', l_req_num);
1366: FND_MSG_PUB.ADD;
1367: END IF;
1368: CLOSE chk_requisition_exists_csr;
1369: END IF;
1376: || 'g_old_status_code: ' || g_old_status_code || 'new status: ' || p_x_osp_order_rec.status_code );
1377: END IF;
1378: -- po_header_id should be null.
1379: 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
1380: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1381: FND_MSG_PUB.ADD;
1382: END IF;
1383:
1384: -- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
1383:
1384: -- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
1385: -- req_header_id should be null.
1386: 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
1387: FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_OSP_ORD_REQ_NNLL');
1388: FND_MSG_PUB.ADD;
1389: END IF;
1390: -- jaramana End
1391:
1390: -- jaramana End
1391:
1392: -- single_instance_flag cant change
1393: 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
1394: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_INST_FLG_CHG');
1395: FND_MSG_PUB.ADD;
1396: END IF;
1397: -- vendor_id cant change
1398: 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
1395: FND_MSG_PUB.ADD;
1396: END IF;
1397: -- vendor_id cant change
1398: 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
1399: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CHG');
1400: FND_MSG_PUB.ADD;
1401: END IF;
1402: -- vendor_site_id cant change
1403: 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
1400: FND_MSG_PUB.ADD;
1401: END IF;
1402: -- vendor_site_id cant change
1403: 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
1404: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_STE_CHG');
1405: FND_MSG_PUB.ADD;
1406: END IF;
1407:
1408: -- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
1408: -- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
1409: -- This should have been present with ISO changes. Adding now.
1410: -- vendor_contact_id cant change
1411: 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
1412: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CTCT_CHG ');
1413: FND_MSG_PUB.ADD;
1414: END IF;
1415: -- jaramana End
1416:
1415: -- jaramana End
1416:
1417: -- Buyer cant change
1418: 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
1419: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_BUYER_CHG');
1420: FND_MSG_PUB.ADD;
1421: END IF;
1422: -- jaramana modified on January 9, 2008 for the Requisition ER 6034236
1423: --The transitions from G_OSP_SUBMITTED_STATUS -> G_OSP_SUB_FAILED_STATUS and
1427: /*
1428: ELSIF (p_x_osp_order_rec.status_code = G_OSP_SUB_FAILED_STATUS) THEN
1429: -- po_header_id should be null.
1430: 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
1431: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1432: FND_MSG_PUB.ADD;
1433: END IF;
1434: --set single_instance_flag
1435: IF(p_x_osp_order_rec.single_instance_flag = FND_API.G_MISS_CHAR)THEN
1434: --set single_instance_flag
1435: IF(p_x_osp_order_rec.single_instance_flag = FND_API.G_MISS_CHAR)THEN
1436: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1437: 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
1438: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1439: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1440: FND_MSG_PUB.ADD;
1441: END IF;
1442: */
1435: IF(p_x_osp_order_rec.single_instance_flag = FND_API.G_MISS_CHAR)THEN
1436: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1437: 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
1438: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1439: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1440: FND_MSG_PUB.ADD;
1441: END IF;
1442: */
1443: /*
1442: */
1443: /*
1444: -- validate vendor_id.
1445: 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
1446: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_NLL');
1447: FND_MSG_PUB.ADD;
1448: ELSIF (p_x_osp_order_rec.vendor_id IS NOT NULL) THEN
1449: validate_vendor(p_x_osp_order_rec.vendor_id);
1450: END IF;
1449: validate_vendor(p_x_osp_order_rec.vendor_id);
1450: END IF;
1451: -- validate vendor_site_id.
1452: 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
1453: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_NLL');
1454: FND_MSG_PUB.ADD;
1455: ELSE
1456: p_x_osp_order_rec.vendor_site_id := NVL(p_x_osp_order_rec.vendor_site_id,l_osp_order_rec.vendor_site_id);
1457: p_x_osp_order_rec.vendor_id := NVL(p_x_osp_order_rec.vendor_id, l_osp_order_rec.vendor_id);
1464: p_x_osp_order_rec.vendor_contact_id);
1465: -- validate po_agent_id.
1466: --dbms_output.put_line('validate buyer in update');
1467: 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
1468: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_BUYER_ID_NLL');
1469: FND_MSG_PUB.ADD;
1470: ELSIF (p_x_osp_order_rec.po_agent_id IS NOT NULL) THEN
1471: validate_buyer(p_x_osp_order_rec.po_agent_id);
1472: END IF;
1478: || 'g_old_status_code: ' || g_old_status_code || 'new status: ' || p_x_osp_order_rec.status_code );
1479: END IF;
1480: -- po_header_id should be not be null.
1481: 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
1482: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_PO_NLL');
1483: FND_MSG_PUB.ADD;
1484: ELSE
1485: validate_po_header(p_x_osp_order_rec.osp_order_id,p_x_osp_order_rec.po_header_id);
1486: END IF;
1485: validate_po_header(p_x_osp_order_rec.osp_order_id,p_x_osp_order_rec.po_header_id);
1486: END IF;
1487: -- single_instance_flag cant change
1488: 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
1489: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_INST_FLG_CHG');
1490: FND_MSG_PUB.ADD;
1491: END IF;
1492: --PO Synch enhancement vendor id and vendor sitecode can change if the change come from PO
1493: -- vendor_id cannot change unless it was change in PO
1492: --PO Synch enhancement vendor id and vendor sitecode can change if the change come from PO
1493: -- vendor_id cannot change unless it was change in PO
1494: 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
1495: IF( vendor_id_exist_in_PO(l_osp_order_rec.po_header_id, p_x_osp_order_rec.vendor_id) = false) THEN
1496: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CHG');
1497: FND_MSG_PUB.ADD;
1498: END IF;
1499: END IF;
1500: -- vendor_site_id cannot change unless it was changed in PO
1499: END IF;
1500: -- vendor_site_id cannot change unless it was changed in PO
1501: 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
1502: IF( vendor_site_id_exist_in_PO(l_osp_order_rec.po_header_id, p_x_osp_order_rec.vendor_site_id) = false) THEN
1503: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_STE_CHG');
1504: FND_MSG_PUB.ADD;
1505: END IF;
1506: END IF;
1507: -- Buyer cant change
1505: END IF;
1506: END IF;
1507: -- Buyer cant change
1508: 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
1509: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_BUYER_CHG');
1510: FND_MSG_PUB.ADD;
1511: END IF;
1512: -- set status_code for lines
1513: g_order_status_for_update := G_OSP_PO_CREATED_STATUS;
1513: g_order_status_for_update := G_OSP_PO_CREATED_STATUS;
1514: */
1515: -- jaramana End
1516: ELSE
1517: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1518: FND_MSG_PUB.ADD;
1519: END IF;
1520:
1521: ELSIF (g_old_status_code = G_OSP_PO_CREATED_STATUS) THEN
1526: -- status_code should be null, 'PO_CREATED' or 'CLOSED'
1527: 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
1528: -- po_header_id cant change
1529: 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
1530: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_PO_CHG');
1531: FND_MSG_PUB.ADD;
1532: END IF;
1533:
1534: -- jaramana modified on January 9, 2008 for the Requisition ER 6034236
1533:
1534: -- jaramana modified on January 9, 2008 for the Requisition ER 6034236
1535: -- req_header_id should be null.
1536: 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
1537: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_REQ_NNLL');
1538: FND_MSG_PUB.ADD;
1539: END IF;
1540: -- jaramana End
1541:
1540: -- jaramana End
1541:
1542: -- single_instance_flag cant change
1543: 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
1544: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_INST_FLG_CHG');
1545: FND_MSG_PUB.ADD;
1546: END IF;
1547: /*
1548: --PO Synch enhancement vendor id and vendor sitecode can change if the change come from PO
1548: --PO Synch enhancement vendor id and vendor sitecode can change if the change come from PO
1549: -- vendor_id cant change
1550: 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
1551: IF( vendor_id_exist_in_PO(l_osp_order_rec.po_header_id, p_x_osp_order_rec.vendor_id) = false) THEN
1552: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CHG');
1553: FND_MSG_PUB.ADD;
1554: END IF;
1555: END IF;
1556: -- vendor_site_id cant change
1555: END IF;
1556: -- vendor_site_id cant change
1557: 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
1558: IF( vendor_site_id_exist_in_PO(l_osp_order_rec.po_header_id, p_x_osp_order_rec.vendor_site_id) = false) THEN
1559: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_STE_CHG');
1560: FND_MSG_PUB.ADD;
1561: END IF;
1562: END IF;
1563: */
1565: -- We are not planning to support this PO attribute continuous synch anymore.
1566:
1567: -- vendor_id cant change
1568: 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
1569: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CHG');
1570: FND_MSG_PUB.ADD;
1571: END IF;
1572: -- vendor_site_id cant change
1573: 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
1570: FND_MSG_PUB.ADD;
1571: END IF;
1572: -- vendor_site_id cant change
1573: 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
1574: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_STE_CHG');
1575: FND_MSG_PUB.ADD;
1576: END IF;
1577: -- vendor_contact_id cant change
1578: 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
1575: FND_MSG_PUB.ADD;
1576: END IF;
1577: -- vendor_contact_id cant change
1578: 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
1579: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CTCT_CHG ');
1580: FND_MSG_PUB.ADD;
1581: END IF;
1582: -- jaramana End
1583:
1582: -- jaramana End
1583:
1584: -- Buyer cant change
1585: 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
1586: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_BUYER_CHG');
1587: FND_MSG_PUB.ADD;
1588: END IF;
1589: -- set status_code for lines
1590: --g_order_status_for_update := G_OSP_PO_CREATED_STATUS;
1588: END IF;
1589: -- set status_code for lines
1590: --g_order_status_for_update := G_OSP_PO_CREATED_STATUS;
1591: ELSE
1592: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1593: FND_MSG_PUB.ADD;
1594: END IF;
1595: -- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
1596: ELSIF (g_old_status_code = G_OSP_REQ_SUBMITTED_STATUS) THEN
1600: 'g_old_status_code: ' || g_old_status_code || 'new status: ' || p_x_osp_order_rec.status_code );
1601: END IF;
1602: -- po_header_id should be null.
1603: 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
1604: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1605: FND_MSG_PUB.ADD;
1606: END IF;
1607:
1608: -- req_header_id should be null.
1606: END IF;
1607:
1608: -- req_header_id should be null.
1609: 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
1610: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_REQ_NNLL');
1611: FND_MSG_PUB.ADD;
1612: END IF;
1613:
1614: -- single_instance_flag cant change
1612: END IF;
1613:
1614: -- single_instance_flag cant change
1615: 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
1616: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_INST_FLG_CHG');
1617: FND_MSG_PUB.ADD;
1618: END IF;
1619: -- vendor_id cant change
1620: 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
1617: FND_MSG_PUB.ADD;
1618: END IF;
1619: -- vendor_id cant change
1620: 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
1621: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CHG');
1622: FND_MSG_PUB.ADD;
1623: END IF;
1624: -- vendor_site_id cant change
1625: 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
1622: FND_MSG_PUB.ADD;
1623: END IF;
1624: -- vendor_site_id cant change
1625: 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
1626: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_STE_CHG');
1627: FND_MSG_PUB.ADD;
1628: END IF;
1629: -- vendor_contact_id cant change
1630: 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
1627: FND_MSG_PUB.ADD;
1628: END IF;
1629: -- vendor_contact_id cant change
1630: 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
1631: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CTCT_CHG ');
1632: FND_MSG_PUB.ADD;
1633: END IF;
1634: -- Buyer cant change
1635: 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
1632: FND_MSG_PUB.ADD;
1633: END IF;
1634: -- Buyer cant change
1635: 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
1636: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_BUYER_CHG');
1637: FND_MSG_PUB.ADD;
1638: END IF;
1639: ELSE
1640: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1636: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_BUYER_CHG');
1637: FND_MSG_PUB.ADD;
1638: END IF;
1639: ELSE
1640: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1641: FND_MSG_PUB.ADD;
1642: END IF;
1643:
1644: ELSIF (g_old_status_code = G_OSP_REQ_CREATED_STATUS) THEN
1649: -- status_code should be null, 'REQ_CREATED' or 'CLOSED'
1650: 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
1651: -- req_header_id cant change
1652: 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
1653: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_REQ_CHG');
1654: FND_MSG_PUB.ADD;
1655: END IF;
1656:
1657: --po_header_id should be null
1655: END IF;
1656:
1657: --po_header_id should be null
1658: 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
1659: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_PO_NNLL');
1660: FND_MSG_PUB.ADD;
1661: END IF;
1662:
1663: -- single_instance_flag cant change
1661: END IF;
1662:
1663: -- single_instance_flag cant change
1664: 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
1665: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_INST_FLG_CHG');
1666: FND_MSG_PUB.ADD;
1667: END IF;
1668:
1669: -- vendor_id cant change
1667: END IF;
1668:
1669: -- vendor_id cant change
1670: 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
1671: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CHG');
1672: FND_MSG_PUB.ADD;
1673: END IF;
1674: -- vendor_site_id cant change
1675: 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
1672: FND_MSG_PUB.ADD;
1673: END IF;
1674: -- vendor_site_id cant change
1675: 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
1676: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_STE_CHG');
1677: FND_MSG_PUB.ADD;
1678: END IF;
1679:
1680: -- vendor_contact_id cant change
1678: END IF;
1679:
1680: -- vendor_contact_id cant change
1681: 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
1682: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEN_CTCT_CHG ');
1683: FND_MSG_PUB.ADD;
1684: END IF;
1685:
1686: -- Buyer cant change
1684: END IF;
1685:
1686: -- Buyer cant change
1687: 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
1688: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_BUYER_CHG');
1689: FND_MSG_PUB.ADD;
1690: END IF;
1691: -- set status_code for lines
1692: --g_order_status_for_update := G_OSP_PO_CREATED_STATUS;
1690: END IF;
1691: -- set status_code for lines
1692: --g_order_status_for_update := G_OSP_PO_CREATED_STATUS;
1693: ELSE
1694: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1695: FND_MSG_PUB.ADD;
1696: END IF;
1697: --mpothuku End
1698: END IF; --IF(p_x_osp_order_rec.operation_flag = G_OP_UPDATE) THEN
1706: IF(p_x_osp_order_rec.single_instance_flag IS NULL OR p_x_osp_order_rec.single_instance_flag = FND_API.G_MISS_CHAR
1707: OR p_x_osp_order_rec.single_instance_flag = G_NO_FLAG )THEN
1708: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1709: ELSE
1710: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1711: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1712: FND_MSG_PUB.ADD;
1713: END IF;
1714: IF(p_x_osp_order_rec.operation_flag = G_OP_CREATE) THEN
1707: OR p_x_osp_order_rec.single_instance_flag = G_NO_FLAG )THEN
1708: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1709: ELSE
1710: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1711: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1712: FND_MSG_PUB.ADD;
1713: END IF;
1714: IF(p_x_osp_order_rec.operation_flag = G_OP_CREATE) THEN
1715: -- validate customer_id.
1713: END IF;
1714: IF(p_x_osp_order_rec.operation_flag = G_OP_CREATE) THEN
1715: -- validate customer_id.
1716: IF(p_x_osp_order_rec.customer_id IS NULL OR p_x_osp_order_rec.customer_id = FND_API.G_MISS_NUM) THEN
1717: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUSTOMER_ID_NLL');
1718: FND_MSG_PUB.ADD;
1719: ELSE
1720: validate_customer(p_x_osp_order_rec.customer_id);
1721: END IF;
1725: */
1726: -- Changes begin
1727: /*
1728: IF (p_x_osp_order_rec.contract_id IS NULL OR p_x_osp_order_rec.contract_id = FND_API.G_MISS_NUM) THEN
1729: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CONTRACT_ID_NLL');
1730: FND_MSG_PUB.ADD;
1731: ELSE
1732: 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);
1733: */
1740: -- Changed by jaramana on January 9, 2008 for the Requisition ER 6034236
1741: -- status_code should be null,'ENTERED', or 'SUBMITTED' or 'REQ_SUBMITTED'
1742: IF(p_x_osp_order_rec.status_code IS NOT NULL AND
1743: p_x_osp_order_rec.status_code NOT IN(G_OSP_ENTERED_STATUS, G_OSP_SUBMITTED_STATUS, G_OSP_REQ_SUBMITTED_STATUS)) THEN
1744: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1745: FND_MSG_PUB.ADD;
1746: END IF;
1747: -- jaramana End
1748: -- validate customer_id.
1746: END IF;
1747: -- jaramana End
1748: -- validate customer_id.
1749: 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
1750: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CUSTOMER_ID_NLL');
1751: FND_MSG_PUB.ADD;
1752: ELSIF(p_x_osp_order_rec.customer_id IS NOT NULL) THEN
1753: validate_customer(p_x_osp_order_rec.customer_id);
1754: END IF;
1758: */
1759: -- Changes begin
1760: /*
1761: 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
1762: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CONTRACT_ID_NLL');
1763: FND_MSG_PUB.ADD;
1764: ELSIF ( p_x_osp_order_rec.contract_id IS NOT NULL) THEN
1765: 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);
1766: */
1773: ELSIF(g_old_status_code = G_OSP_SUBMITTED_STATUS) THEN
1774: -- status_code should be null,'ENTERED', or 'SUBMITTED'
1775: IF(p_x_osp_order_rec.status_code IS NOT NULL AND
1776: p_x_osp_order_rec.status_code NOT IN(G_OSP_SUBMITTED_STATUS, G_OSP_CLOSED_STATUS)) THEN
1777: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1778: FND_MSG_PUB.ADD;
1779: END IF;
1780: -- CUSTOMER_ID cant change
1781: 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
1778: FND_MSG_PUB.ADD;
1779: END IF;
1780: -- CUSTOMER_ID cant change
1781: 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
1782: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CUST_CHG');
1783: FND_MSG_PUB.ADD;
1784: END IF;
1785: -- contract_id cant change
1786: 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
1783: FND_MSG_PUB.ADD;
1784: END IF;
1785: -- contract_id cant change
1786: 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
1787: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CTRCT_CHG');
1788: FND_MSG_PUB.ADD;
1789: END IF;
1790: --g_order_status_for_update := G_OSP_SUBMITTED_STATUS;
1791: ELSE
1788: FND_MSG_PUB.ADD;
1789: END IF;
1790: --g_order_status_for_update := G_OSP_SUBMITTED_STATUS;
1791: ELSE
1792: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1793: FND_MSG_PUB.ADD;
1794: END IF;
1795: END IF;
1796: -- validate BORROW order header
1799: IF(p_x_osp_order_rec.single_instance_flag IS NULL OR p_x_osp_order_rec.single_instance_flag = FND_API.G_MISS_CHAR
1800: OR p_x_osp_order_rec.single_instance_flag = G_NO_FLAG )THEN
1801: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1802: ELSE
1803: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1804: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1805: FND_MSG_PUB.ADD;
1806: END IF;
1807: IF(p_x_osp_order_rec.operation_flag = G_OP_CREATE) THEN
1800: OR p_x_osp_order_rec.single_instance_flag = G_NO_FLAG )THEN
1801: p_x_osp_order_rec.single_instance_flag := G_NO_FLAG;
1802: ELSE
1803: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_INST_FLG');
1804: FND_MESSAGE.Set_Token('INST_FLG', p_x_osp_order_rec.single_instance_flag);
1805: FND_MSG_PUB.ADD;
1806: END IF;
1807: IF(p_x_osp_order_rec.operation_flag = G_OP_CREATE) THEN
1808: -- validate customer_id.
1811: END IF;
1812: /*
1813: -- validate vendor_id.
1814: IF(p_x_osp_order_rec.vendor_id IS NULL OR p_x_osp_order_rec.vendor_id = FND_API.G_MISS_NUM) THEN
1815: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_NLL');
1816: FND_MSG_PUB.ADD;
1817: ELSE
1818: validate_vendor(p_x_osp_order_rec.vendor_id);
1819: END IF;
1827: */
1828: -- Changes begin
1829: /*
1830: IF (p_x_osp_order_rec.contract_id IS NULL OR p_x_osp_order_rec.contract_id = FND_API.G_MISS_NUM) THEN
1831: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CONTRACT_ID_NLL');
1832: FND_MSG_PUB.ADD;
1833: ELSE
1834: 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);
1835: END IF;
1842: IF(g_old_status_code = G_OSP_ENTERED_STATUS) THEN
1843: -- status_code should be null,'ENTERED', or 'SUBMITTED'
1844: IF(p_x_osp_order_rec.status_code IS NOT NULL AND
1845: p_x_osp_order_rec.status_code NOT IN(G_OSP_ENTERED_STATUS, G_OSP_SUBMITTED_STATUS)) THEN
1846: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1847: FND_MSG_PUB.ADD;
1848: END IF;
1849: -- validate customer_id.
1850: 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
1855: */
1856: -- Changes begin
1857: /*
1858: 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
1859: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CONTRACT_ID_NLL');
1860: FND_MSG_PUB.ADD;
1861: 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
1862: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_NLL');
1863: FND_MSG_PUB.ADD;
1858: 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
1859: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_CONTRACT_ID_NLL');
1860: FND_MSG_PUB.ADD;
1861: 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
1862: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_NLL');
1863: FND_MSG_PUB.ADD;
1864: ELSIF(p_x_osp_order_rec.vendor_id IS NOT NULL OR p_x_osp_order_rec.contract_id IS NOT NULL) THEN
1865: -- validate vendor_id.
1866: validate_vendor(p_x_osp_order_rec.vendor_id);
1871: */
1872: /*
1873: -- validate vendor_id.
1874: IF(p_x_osp_order_rec.vendor_id IS NULL OR p_x_osp_order_rec.vendor_id = FND_API.G_MISS_NUM) THEN
1875: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_NLL');
1876: FND_MSG_PUB.ADD;
1877: ELSE
1878: validate_vendor(p_x_osp_order_rec.vendor_id);
1879: END IF;
1893: ELSIF(g_old_status_code = G_OSP_SUBMITTED_STATUS) THEN
1894: -- status_code should be null,'ENTERED', or 'SUBMITTED'
1895: IF(p_x_osp_order_rec.status_code IS NOT NULL AND
1896: p_x_osp_order_rec.status_code NOT IN(G_OSP_SUBMITTED_STATUS, G_OSP_CLOSED_STATUS)) THEN
1897: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1898: FND_MSG_PUB.ADD;
1899: END IF;
1900: -- CUSTOMER_ID cant change
1901: 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
1898: FND_MSG_PUB.ADD;
1899: END IF;
1900: -- CUSTOMER_ID cant change
1901: 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
1902: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CUST_CHG');
1903: FND_MSG_PUB.ADD;
1904: END IF;
1905: -- vendor_id cant change
1906: 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
1903: FND_MSG_PUB.ADD;
1904: END IF;
1905: -- vendor_id cant change
1906: 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
1907: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_VEND_CHG');
1908: FND_MSG_PUB.ADD;
1909: END IF;
1910: -- contract_id cant change
1911: 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
1908: FND_MSG_PUB.ADD;
1909: END IF;
1910: -- contract_id cant change
1911: 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
1912: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CTRCT_CHG');
1913: FND_MSG_PUB.ADD;
1914: END IF;
1915: -- set status
1916: --g_order_status_for_update := G_OSP_SUBMITTED_STATUS;
1914: END IF;
1915: -- set status
1916: --g_order_status_for_update := G_OSP_SUBMITTED_STATUS;
1917: ELSE
1918: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_STATUS');
1919: FND_MSG_PUB.ADD;
1920: END IF;
1921: END IF;
1922: ELSE
1919: FND_MSG_PUB.ADD;
1920: END IF;
1921: END IF;
1922: ELSE
1923: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INV_TYPE');
1924: FND_MSG_PUB.ADD;
1925: END IF;
1926: -- delete header
1927: IF(p_x_osp_order_rec.operation_flag = G_OP_DELETE) THEN
1926: -- delete header
1927: IF(p_x_osp_order_rec.operation_flag = G_OP_DELETE) THEN
1928: -- Changed by jaramana on January 9, 2008 for the Requisition ER 6034236 (Added G_OSP_REQ_SUB_FAILED_STATUS)
1929: IF(g_old_status_code NOT IN( G_OSP_ENTERED_STATUS, G_OSP_SUB_FAILED_STATUS, G_OSP_REQ_SUB_FAILED_STATUS ))THEN
1930: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INVOP');
1931: FND_MSG_PUB.ADD;
1932: ELSE
1933: --g_order_status_for_update := G_OSP_DELETED_STATUS;
1934: IF(l_osp_order_rec.oe_header_id IS NOT NULL) THEN
1996: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1997: 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);
1998: END IF;
1999: IF (osp_order_csr%NOTFOUND) THEN
2000: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INVOP_OSP_NFOUND');
2001: FND_MSG_PUB.ADD;
2002: ELSE
2003: --dbms_output.put_line('l_osp_order_rec.osp_order_number ' || l_osp_order_rec.osp_order_number);
2004: IF (p_x_osp_order_rec.osp_order_number IS NULL) THEN
2361: IF(p_workorder_id IS NOT NULL) THEN
2362: OPEN val_workorder_id_csr(p_workorder_id);
2363: FETCH val_workorder_id_csr INTO l_exist;
2364: IF(val_workorder_id_csr%NOTFOUND) THEN
2365: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_WO');
2366: FND_MESSAGE.Set_Token('WORKORDER_ID', p_workorder_id);
2367: FND_MSG_PUB.ADD;
2368: END IF;
2369: CLOSE val_workorder_id_csr;
2362: OPEN val_workorder_id_csr(p_workorder_id);
2363: FETCH val_workorder_id_csr INTO l_exist;
2364: IF(val_workorder_id_csr%NOTFOUND) THEN
2365: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_WO');
2366: FND_MESSAGE.Set_Token('WORKORDER_ID', p_workorder_id);
2367: FND_MSG_PUB.ADD;
2368: END IF;
2369: CLOSE val_workorder_id_csr;
2370: END IF;
2418: IF(p_service_item_id IS NOT NULL AND p_service_item_id <> FND_API.G_MISS_NUM) THEN
2419: OPEN val_service_item_id_inv_csr(p_service_item_id, p_workorder_id);
2420: FETCH val_service_item_id_inv_csr INTO l_exist;
2421: IF(val_service_item_id_inv_csr%NOTFOUND) THEN
2422: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM');
2423: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2424: FND_MSG_PUB.ADD;
2425: --dbms_output.put_line('Invalid service item not in inventory');
2426: ELSE
2419: OPEN val_service_item_id_inv_csr(p_service_item_id, p_workorder_id);
2420: FETCH val_service_item_id_inv_csr INTO l_exist;
2421: IF(val_service_item_id_inv_csr%NOTFOUND) THEN
2422: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM');
2423: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2424: FND_MSG_PUB.ADD;
2425: --dbms_output.put_line('Invalid service item not in inventory');
2426: ELSE
2427: -- Valid service item, exists in WO Org
2433: OPEN item_exists_in_wo_org_csr(p_workorder_id);
2434: FETCH item_exists_in_wo_org_csr INTO l_exist;
2435: IF (item_exists_in_wo_org_csr%FOUND) THEN
2436: -- WO Service Item does exist in WO Org. So, should have matched!
2437: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM_MISWO');
2438: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2439: FND_MSG_PUB.ADD;
2440: --dbms_output.put_line('Invalid service item , does not match in wo');
2441: ELSE
2434: FETCH item_exists_in_wo_org_csr INTO l_exist;
2435: IF (item_exists_in_wo_org_csr%FOUND) THEN
2436: -- WO Service Item does exist in WO Org. So, should have matched!
2437: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM_MISWO');
2438: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2439: FND_MSG_PUB.ADD;
2440: --dbms_output.put_line('Invalid service item , does not match in wo');
2441: ELSE
2442: -- WO Service Item does not exist in WO Org.
2459: OPEN item_exists_in_wo_org_csr(p_workorder_id);
2460: FETCH item_exists_in_wo_org_csr INTO l_exist;
2461: IF (item_exists_in_wo_org_csr%FOUND) THEN
2462: -- WO Service Item does exist in WO Org. So, should not have been null
2463: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM_NLL');
2464: FND_MSG_PUB.ADD;
2465: --dbms_output.put_line('Passed service item is null, in wo its not null');
2466: ELSE
2467: -- WO Service Item does not exist in WO Org.
2496: IF(p_service_item_id IS NOT NULL AND p_service_item_id <> FND_API.G_MISS_NUM) THEN
2497: OPEN val_service_item_desc_csr(p_service_item_id , p_service_item_description);
2498: FETCH val_service_item_desc_csr INTO l_exist;
2499: IF(val_service_item_desc_csr%NOTFOUND) THEN
2500: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM_DESC');
2501: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2502: FND_MESSAGE.Set_Token('SERVICE_ITEM_DESC', p_service_item_description);
2503: FND_MSG_PUB.ADD;
2504: --dbms_output.put_line('Invalid service item description');
2497: OPEN val_service_item_desc_csr(p_service_item_id , p_service_item_description);
2498: FETCH val_service_item_desc_csr INTO l_exist;
2499: IF(val_service_item_desc_csr%NOTFOUND) THEN
2500: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM_DESC');
2501: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2502: FND_MESSAGE.Set_Token('SERVICE_ITEM_DESC', p_service_item_description);
2503: FND_MSG_PUB.ADD;
2504: --dbms_output.put_line('Invalid service item description');
2505: END IF;
2498: FETCH val_service_item_desc_csr INTO l_exist;
2499: IF(val_service_item_desc_csr%NOTFOUND) THEN
2500: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM_DESC');
2501: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2502: FND_MESSAGE.Set_Token('SERVICE_ITEM_DESC', p_service_item_description);
2503: FND_MSG_PUB.ADD;
2504: --dbms_output.put_line('Invalid service item description');
2505: END IF;
2506: CLOSE val_service_item_desc_csr;
2563:
2564: OPEN val_service_item_id_inv_csr(p_service_item_id, p_workorder_id);
2565: FETCH val_service_item_id_inv_csr INTO l_exist;
2566: IF(val_service_item_id_inv_csr%NOTFOUND) THEN
2567: FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_OSP_LN_INV_SVC_ITEM');
2568: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2569: FND_MSG_PUB.ADD;
2570: --dbms_output.put_line('Invalid service item not in inventory');
2571: ELSE
2564: OPEN val_service_item_id_inv_csr(p_service_item_id, p_workorder_id);
2565: FETCH val_service_item_id_inv_csr INTO l_exist;
2566: IF(val_service_item_id_inv_csr%NOTFOUND) THEN
2567: FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_OSP_LN_INV_SVC_ITEM');
2568: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2569: FND_MSG_PUB.ADD;
2570: --dbms_output.put_line('Invalid service item not in inventory');
2571: ELSE
2572: -- Valid service item, exists in WO Org
2577: OPEN item_exists_in_wo_org_csr(p_workorder_id);
2578: FETCH item_exists_in_wo_org_csr INTO l_exist;
2579: IF (item_exists_in_wo_org_csr%FOUND) THEN
2580: -- WO Service Item does exist in WO Org. So, should have matched!
2581: FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_OSP_LN_INV_SVC_ITEM_MISWO');
2582: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2583: FND_MSG_PUB.ADD;
2584: --dbms_output.put_line('Invalid service item , does not match in wo');
2585: ELSE
2578: FETCH item_exists_in_wo_org_csr INTO l_exist;
2579: IF (item_exists_in_wo_org_csr%FOUND) THEN
2580: -- WO Service Item does exist in WO Org. So, should have matched!
2581: FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_OSP_LN_INV_SVC_ITEM_MISWO');
2582: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2583: FND_MSG_PUB.ADD;
2584: --dbms_output.put_line('Invalid service item , does not match in wo');
2585: ELSE
2586: -- WO Service Item does not exist in WO Org.
2602: OPEN item_exists_in_wo_org_csr(p_workorder_id);
2603: FETCH item_exists_in_wo_org_csr INTO l_exist;
2604: IF (item_exists_in_wo_org_csr%FOUND) THEN
2605: -- WO Service Item does exist in WO Org. So, should not have been null
2606: FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_OSP_LN_INV_SVC_ITEM_NLL');
2607: FND_MSG_PUB.ADD;
2608: --dbms_output.put_line('Passed service item is null, in wo its not null');
2609: ELSE
2610: -- WO Service Item does not exist in WO Org.
2648: IF(p_service_item_uom_code IS NOT NULL AND p_service_item_uom_code <> FND_API.G_MISS_CHAR) THEN
2649: OPEN val_service_item_uom_csr(p_service_item_id,p_service_item_uom_code,p_org_id);
2650: FETCH val_service_item_uom_csr INTO l_exist;
2651: IF(val_service_item_uom_csr%NOTFOUND) THEN
2652: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_UOM');
2653: FND_MESSAGE.Set_Token('UOM_CODE', p_service_item_uom_code);
2654: -- FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2655: FND_MSG_PUB.ADD;
2656: END IF;
2649: OPEN val_service_item_uom_csr(p_service_item_id,p_service_item_uom_code,p_org_id);
2650: FETCH val_service_item_uom_csr INTO l_exist;
2651: IF(val_service_item_uom_csr%NOTFOUND) THEN
2652: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_UOM');
2653: FND_MESSAGE.Set_Token('UOM_CODE', p_service_item_uom_code);
2654: -- FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2655: FND_MSG_PUB.ADD;
2656: END IF;
2657: CLOSE val_service_item_uom_csr;
2650: FETCH val_service_item_uom_csr INTO l_exist;
2651: IF(val_service_item_uom_csr%NOTFOUND) THEN
2652: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_UOM');
2653: FND_MESSAGE.Set_Token('UOM_CODE', p_service_item_uom_code);
2654: -- FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2655: FND_MSG_PUB.ADD;
2656: END IF;
2657: CLOSE val_service_item_uom_csr;
2658: END IF;
2660: IF(p_service_item_uom_code IS NOT NULL AND p_service_item_uom_code <> FND_API.G_MISS_CHAR) THEN
2661: OPEN val_uom_code_csr(p_service_item_uom_code);
2662: FETCH val_uom_code_csr INTO l_exist;
2663: IF(val_uom_code_csr%NOTFOUND) THEN
2664: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_UOM');
2665: FND_MESSAGE.Set_Token('UOM_CODE', p_service_item_uom_code);
2666: -- FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2667: FND_MSG_PUB.ADD;
2668: END IF;
2661: OPEN val_uom_code_csr(p_service_item_uom_code);
2662: FETCH val_uom_code_csr INTO l_exist;
2663: IF(val_uom_code_csr%NOTFOUND) THEN
2664: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_UOM');
2665: FND_MESSAGE.Set_Token('UOM_CODE', p_service_item_uom_code);
2666: -- FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2667: FND_MSG_PUB.ADD;
2668: END IF;
2669: CLOSE val_uom_code_csr;
2662: FETCH val_uom_code_csr INTO l_exist;
2663: IF(val_uom_code_csr%NOTFOUND) THEN
2664: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_UOM');
2665: FND_MESSAGE.Set_Token('UOM_CODE', p_service_item_uom_code);
2666: -- FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
2667: FND_MSG_PUB.ADD;
2668: END IF;
2669: CLOSE val_uom_code_csr;
2670: END IF;
2692: IF(p_po_line_type_id IS NOT NULL AND p_po_line_type_id <> FND_API.G_MISS_NUM) THEN
2693: OPEN val_po_line_type_id_csr(p_po_line_type_id);
2694: FETCH val_po_line_type_id_csr INTO l_exist;
2695: IF(val_po_line_type_id_csr%NOTFOUND) THEN
2696: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_LNTYP_ID');
2697: FND_MESSAGE.Set_Token('LINE_TYPE_ID', p_po_line_type_id);
2698: FND_MSG_PUB.ADD;
2699: END IF;
2700: CLOSE val_po_line_type_id_csr;
2693: OPEN val_po_line_type_id_csr(p_po_line_type_id);
2694: FETCH val_po_line_type_id_csr INTO l_exist;
2695: IF(val_po_line_type_id_csr%NOTFOUND) THEN
2696: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_LNTYP_ID');
2697: FND_MESSAGE.Set_Token('LINE_TYPE_ID', p_po_line_type_id);
2698: FND_MSG_PUB.ADD;
2699: END IF;
2700: CLOSE val_po_line_type_id_csr;
2701: END IF;
2730: IF(p_po_line_id IS NOT NULL AND p_po_line_id <> FND_API.G_MISS_NUM) THEN
2731: OPEN val_po_line_id_csr(p_po_line_id, p_osp_order_id);
2732: FETCH val_po_line_id_csr INTO l_exist;
2733: IF(val_po_line_id_csr%NOTFOUND) THEN
2734: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PO_LINE_INV');
2735: FND_MESSAGE.Set_Token('PO_LINE_ID', p_po_line_id);
2736: FND_MSG_PUB.ADD;
2737: END IF;
2738: CLOSE val_po_line_id_csr;
2731: OPEN val_po_line_id_csr(p_po_line_id, p_osp_order_id);
2732: FETCH val_po_line_id_csr INTO l_exist;
2733: IF(val_po_line_id_csr%NOTFOUND) THEN
2734: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PO_LINE_INV');
2735: FND_MESSAGE.Set_Token('PO_LINE_ID', p_po_line_id);
2736: FND_MSG_PUB.ADD;
2737: END IF;
2738: CLOSE val_po_line_id_csr;
2739: END IF;
2763: IF(p_exchange_instance_id IS NOT NULL AND p_exchange_instance_id <> FND_API.G_MISS_NUM) THEN
2764: OPEN val_instance_id_csr(p_exchange_instance_id);
2765: FETCH val_instance_id_csr INTO l_exist;
2766: IF (val_instance_id_csr %NOTFOUND) THEN
2767: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INSTANCE_ID_INV');
2768: FND_MESSAGE.Set_Token('EXCHANGE_INSTANCE_ID', p_exchange_instance_id);
2769: FND_MSG_PUB.ADD;
2770: END IF;
2771: END IF;
2764: OPEN val_instance_id_csr(p_exchange_instance_id);
2765: FETCH val_instance_id_csr INTO l_exist;
2766: IF (val_instance_id_csr %NOTFOUND) THEN
2767: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INSTANCE_ID_INV');
2768: FND_MESSAGE.Set_Token('EXCHANGE_INSTANCE_ID', p_exchange_instance_id);
2769: FND_MSG_PUB.ADD;
2770: END IF;
2771: END IF;
2772: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2861: p_x_osp_order_rec.status_code IN (G_OSP_SUBMITTED_STATUS, G_OSP_REQ_SUBMITTED_STATUS)) THEN
2862: -- jaramana End
2863: -- validate fields and submit for PO creation
2864: IF(p_x_osp_order_rec.vendor_id IS NULL) THEN
2865: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_VEN_NLL');
2866: FND_MSG_PUB.ADD;
2867: END IF;
2868: IF (p_x_osp_order_rec.vendor_site_id IS NULL) THEN
2869: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_VENST_NLL');
2865: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_VEN_NLL');
2866: FND_MSG_PUB.ADD;
2867: END IF;
2868: IF (p_x_osp_order_rec.vendor_site_id IS NULL) THEN
2869: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_VENST_NLL');
2870: FND_MSG_PUB.ADD;
2871: END IF;
2872: IF (p_x_osp_order_rec.po_agent_id IS NULL) THEN
2873: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_BUYER_NLL');
2869: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_VENST_NLL');
2870: FND_MSG_PUB.ADD;
2871: END IF;
2872: IF (p_x_osp_order_rec.po_agent_id IS NULL) THEN
2873: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_BUYER_NLL');
2874: FND_MSG_PUB.ADD;
2875: END IF;
2876: IF (p_x_osp_order_rec.po_header_id IS NOT NULL) THEN
2877: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_PO_NNLL');
2873: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_BUYER_NLL');
2874: FND_MSG_PUB.ADD;
2875: END IF;
2876: IF (p_x_osp_order_rec.po_header_id IS NOT NULL) THEN
2877: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_PO_NNLL');
2878: FND_MSG_PUB.ADD;
2879: END IF;
2880:
2881: -- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
2879: END IF;
2880:
2881: -- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
2882: IF (p_x_osp_order_rec.po_req_header_id IS NOT NULL) THEN
2883: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_REQ_NNLL');
2884: FND_MSG_PUB.ADD;
2885: END IF;
2886: -- jaramana End
2887:
2888: -- check whether order has lines
2889: OPEN val_order_has_lines(p_x_osp_order_rec.osp_order_id);
2890: FETCH val_order_has_lines INTO l_exist;
2891: IF(val_order_has_lines%NOTFOUND) THEN
2892: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_NO_LNS');
2893: FND_MSG_PUB.ADD;
2894: END IF;
2895: CLOSE val_order_has_lines;
2896: -- check null values in order lines
2900: l_service_item_uom_code, l_quantity, l_need_by_date, l_po_line_type_id;
2901: EXIT WHEN val_order_lines_csr%NOTFOUND;
2902: --service item id and service item description cannot be both null
2903: IF(l_service_item_description IS NULL AND l_service_item_id IS NULL) THEN
2904: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_ITMID_NL');
2905: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2906: FND_MSG_PUB.ADD;
2907: END IF;
2908: --l_service_item_uom_code cannot be null
2901: EXIT WHEN val_order_lines_csr%NOTFOUND;
2902: --service item id and service item description cannot be both null
2903: IF(l_service_item_description IS NULL AND l_service_item_id IS NULL) THEN
2904: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_ITMID_NL');
2905: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2906: FND_MSG_PUB.ADD;
2907: END IF;
2908: --l_service_item_uom_code cannot be null
2909: IF(l_service_item_uom_code IS NULL) THEN
2906: FND_MSG_PUB.ADD;
2907: END IF;
2908: --l_service_item_uom_code cannot be null
2909: IF(l_service_item_uom_code IS NULL) THEN
2910: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_UOM_NL');
2911: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2912: FND_MSG_PUB.ADD;
2913: END IF;
2914: --quantity cannot be null
2907: END IF;
2908: --l_service_item_uom_code cannot be null
2909: IF(l_service_item_uom_code IS NULL) THEN
2910: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_UOM_NL');
2911: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2912: FND_MSG_PUB.ADD;
2913: END IF;
2914: --quantity cannot be null
2915: IF(l_quantity IS NULL) THEN
2912: FND_MSG_PUB.ADD;
2913: END IF;
2914: --quantity cannot be null
2915: IF(l_quantity IS NULL) THEN
2916: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_QTY_NL');
2917: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2918: FND_MSG_PUB.ADD;
2919: END IF;
2920: --need_by_date cannot be null
2913: END IF;
2914: --quantity cannot be null
2915: IF(l_quantity IS NULL) THEN
2916: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_QTY_NL');
2917: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2918: FND_MSG_PUB.ADD;
2919: END IF;
2920: --need_by_date cannot be null
2921: IF(l_need_by_date IS NULL) THEN
2918: FND_MSG_PUB.ADD;
2919: END IF;
2920: --need_by_date cannot be null
2921: IF(l_need_by_date IS NULL) THEN
2922: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_NBD_NL');
2923: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2924: FND_MSG_PUB.ADD;
2925: END IF;
2926: --po_line_type_id cannot be null
2919: END IF;
2920: --need_by_date cannot be null
2921: IF(l_need_by_date IS NULL) THEN
2922: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_NBD_NL');
2923: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2924: FND_MSG_PUB.ADD;
2925: END IF;
2926: --po_line_type_id cannot be null
2927: IF(l_po_line_type_id IS NULL) THEN
2924: FND_MSG_PUB.ADD;
2925: END IF;
2926: --po_line_type_id cannot be null
2927: IF(l_po_line_type_id IS NULL) THEN
2928: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_PO_LNTYP_NL');
2929: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2930: FND_MSG_PUB.ADD;
2931: END IF;
2932: END LOOP;
2925: END IF;
2926: --po_line_type_id cannot be null
2927: IF(l_po_line_type_id IS NULL) THEN
2928: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_PO_LNTYP_NL');
2929: FND_MESSAGE.Set_Token('LINE_NUM', l_osp_line_number);
2930: FND_MSG_PUB.ADD;
2931: END IF;
2932: END LOOP;
2933: CLOSE val_order_lines_csr;
2952: FETCH get_old_status INTO l_temp_status_code, l_new_ovn;
2953: CLOSE get_old_status;
2954: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS OR
2955: l_new_ovn <> l_old_ovn+1) THEN
2956: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_PO_FLD');
2957: FND_MSG_PUB.ADD;
2958: ELSE
2959: p_x_osp_order_rec.object_version_number := l_new_ovn;
2960: p_x_osp_order_rec.po_request_id := l_request_id;
2984: FETCH get_old_status INTO l_temp_status_code, l_new_ovn;
2985: CLOSE get_old_status;
2986: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS OR
2987: l_new_ovn <> l_old_ovn+1) THEN
2988: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_REQ_FLD');
2989: FND_MSG_PUB.ADD;
2990: ELSE
2991: p_x_osp_order_rec.object_version_number := l_new_ovn;
2992: p_x_osp_order_rec.po_request_id := l_request_id;
3006: ELSIF(l_old_status_code IN (G_OSP_PO_CREATED_STATUS,G_OSP_REQ_CREATED_STATUS) AND p_x_osp_order_rec.status_code = G_OSP_CLOSED_STATUS) THEN
3007:
3008: -- validate fields and ask whether PO and SO is closed.
3009: IF(l_old_status_code = G_OSP_PO_CREATED_STATUS AND p_x_osp_order_rec.po_header_id IS NULL) THEN
3010: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_PO_NLL');
3011: FND_MSG_PUB.ADD;
3012: ELSIF(l_old_status_code = G_OSP_REQ_CREATED_STATUS AND p_x_osp_order_rec.po_req_header_id IS NULL) THEN
3013: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_REQ_NLL');
3014: FND_MSG_PUB.ADD;
3009: IF(l_old_status_code = G_OSP_PO_CREATED_STATUS AND p_x_osp_order_rec.po_header_id IS NULL) THEN
3010: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_PO_NLL');
3011: FND_MSG_PUB.ADD;
3012: ELSIF(l_old_status_code = G_OSP_REQ_CREATED_STATUS AND p_x_osp_order_rec.po_req_header_id IS NULL) THEN
3013: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_REQ_NLL');
3014: FND_MSG_PUB.ADD;
3015: ELSE --This would mean either po_header_id is not null OR po_req_header_id is not NULL
3016: -- If the status is PO_CREATED, ask PO whether it is closed/cancelled.
3017: IF (l_old_status_code = G_OSP_PO_CREATED_STATUS AND
3015: ELSE --This would mean either po_header_id is not null OR po_req_header_id is not NULL
3016: -- If the status is PO_CREATED, ask PO whether it is closed/cancelled.
3017: IF (l_old_status_code = G_OSP_PO_CREATED_STATUS AND
3018: AHL_OSP_PO_PVT.Is_PO_Closed(p_x_osp_order_rec.po_header_id) = G_NO_FLAG ) THEN
3019: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_PO_OPEN');
3020: FND_MSG_PUB.ADD;
3021: END IF;
3022: -- If the status is REQ_CREATED, ask Requisition whether the order can be closed.
3023: IF(l_old_status_code = G_OSP_REQ_CREATED_STATUS AND
3021: END IF;
3022: -- If the status is REQ_CREATED, ask Requisition whether the order can be closed.
3023: IF(l_old_status_code = G_OSP_REQ_CREATED_STATUS AND
3024: AHL_OSP_PO_REQ_PVT.Is_PO_Req_Closed(p_x_osp_order_rec.po_req_header_id) = G_NO_FLAG ) THEN
3025: FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_INV_CL_REQ_OPEN');
3026: FND_MSG_PUB.ADD;
3027: END IF;
3028:
3029: -- ask SO whether can close
3028:
3029: -- ask SO whether can close
3030: IF(p_x_osp_order_rec.oe_header_id IS NOT NULL) THEN
3031: IF NOT(FND_API.TO_BOOLEAN(AHL_OSP_SHIPMENT_PUB.Is_Order_Header_Closed( p_x_osp_order_rec.oe_header_id))) THEN
3032: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_SO_OPEN');
3033: FND_MSG_PUB.ADD;
3034: END IF;
3035: END IF;
3036: -- check whether all the workorders are closed.
3036: -- check whether all the workorders are closed.
3037: OPEN val_all_wo_closed(p_x_osp_order_rec.osp_order_id);
3038: FETCH val_all_wo_closed INTO l_exist;
3039: IF(val_all_wo_closed%FOUND) THEN
3040: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_WO_OPEN');
3041: FND_MSG_PUB.ADD;
3042: END IF;
3043: CLOSE val_all_wo_closed;
3044: -- jrotich added for osp receiving project
3045: -- check whether all mr's are accomplished
3046: OPEN val_all_acc_closed(p_x_osp_order_rec.osp_order_id);
3047: FETCH val_all_acc_closed INTO l_exist;
3048: IF(val_all_acc_closed%FOUND) THEN
3049: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_ACC_OPEN');
3050: FND_MSG_PUB.ADD;
3051: END IF;
3052: CLOSE val_all_acc_closed;
3053: -- jrotich end
3058: IF(l_old_status_code = G_OSP_ENTERED_STATUS AND
3059: p_x_osp_order_rec.status_code = G_OSP_SUBMITTED_STATUS) THEN
3060: 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
3061: --only loan type requires customer id for borrow it's optional (bug fix).
3062: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_CUST_NLL');
3063: FND_MSG_PUB.ADD;
3064: END IF;
3065: /* Change made by mpothuku to make the contract_id optional on 12/16/04
3066: This is a work around and has to be revoked after PM comes up with the Service Contract Integration
3067: */
3068: -- Changes start
3069: /*
3070: IF(p_x_osp_order_rec.contract_id IS NULL) THEN
3071: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_CTRCT_NLL');
3072: FND_MSG_PUB.ADD;
3073: END IF;
3074: */
3075: -- Changes by mpothuku End
3073: END IF;
3074: */
3075: -- Changes by mpothuku End
3076: IF(p_x_osp_order_rec.oe_header_id IS NULL) THEN
3077: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_OE_NLL');
3078: FND_MSG_PUB.ADD;
3079: END IF;
3080: -- check whether order has lines and all lines have shipping information
3081: OPEN val_order_has_ship_lines(p_x_osp_order_rec.osp_order_id);
3084: FETCH val_order_has_ship_lines INTO l_oe_ship_line_id, l_oe_return_line_id, l_line_number;
3085: IF(val_order_has_ship_lines%NOTFOUND) THEN
3086: EXIT;
3087: ELSIF (l_oe_ship_line_id IS NULL AND l_oe_return_line_id IS NULL) THEN
3088: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_NO_SHIP_LN');
3089: FND_MESSAGE.Set_Token('LINE_NUMBER', l_line_number);
3090: FND_MSG_PUB.ADD;
3091: l_count := l_count + 1;
3092: ELSE
3085: IF(val_order_has_ship_lines%NOTFOUND) THEN
3086: EXIT;
3087: ELSIF (l_oe_ship_line_id IS NULL AND l_oe_return_line_id IS NULL) THEN
3088: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_NO_SHIP_LN');
3089: FND_MESSAGE.Set_Token('LINE_NUMBER', l_line_number);
3090: FND_MSG_PUB.ADD;
3091: l_count := l_count + 1;
3092: ELSE
3093: l_count := l_count + 1;
3093: l_count := l_count + 1;
3094: END IF;
3095: END LOOP;
3096: IF(l_count < 1) THEN
3097: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_NO_LNS');
3098: FND_MSG_PUB.ADD;
3099: END IF;
3100: CLOSE val_order_has_ship_lines;
3101: IF FND_MSG_PUB.count_msg > 0 THEN
3112: x_msg_count => l_msg_count,
3113: x_msg_data => l_msg_data
3114: );
3115: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3116: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SUB_SO_FLD');
3117: FND_MSG_PUB.ADD;
3118: END IF;
3119: END IF;
3120: --ELSIF(g_old_status_code = G_OSP_SUBMITTED_STATUS AND p_x_osp_order_rec.status_code = G_OSP_CLOSED_STATUS) THEN
3121: ELSIF(l_old_status_code = G_OSP_SUBMITTED_STATUS AND p_x_osp_order_rec.status_code = G_OSP_CLOSED_STATUS) THEN
3122: -- validate fields and ask SO whether order can be closed.
3123: --dbms_output.put_line('Sales order closed ');
3124: IF(p_x_osp_order_rec.oe_header_id IS NULL) THEN
3125: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_OE_NLL');
3126: FND_MSG_PUB.ADD;
3127: ELSE
3128: --dbms_output.put_line('Sales order closed ' || AHL_OSP_SHIPMENT_PUB.Is_Order_Header_Closed( p_x_osp_order_rec.oe_header_id));
3129: IF NOT(FND_API.TO_BOOLEAN(AHL_OSP_SHIPMENT_PUB.Is_Order_Header_Closed( p_x_osp_order_rec.oe_header_id))) THEN
3127: ELSE
3128: --dbms_output.put_line('Sales order closed ' || AHL_OSP_SHIPMENT_PUB.Is_Order_Header_Closed( p_x_osp_order_rec.oe_header_id));
3129: IF NOT(FND_API.TO_BOOLEAN(AHL_OSP_SHIPMENT_PUB.Is_Order_Header_Closed( p_x_osp_order_rec.oe_header_id))) THEN
3130: --dbms_output.put_line('Sales order is not closed');
3131: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_SO_OPEN');
3132: FND_MSG_PUB.ADD;
3133: END IF;
3134: -- check whether all the workorders are closed.
3135: OPEN val_all_wo_closed(p_x_osp_order_rec.osp_order_id);
3134: -- check whether all the workorders are closed.
3135: OPEN val_all_wo_closed(p_x_osp_order_rec.osp_order_id);
3136: FETCH val_all_wo_closed INTO l_exist;
3137: IF(val_all_wo_closed%FOUND) THEN
3138: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CL_WO_OPEN');
3139: FND_MSG_PUB.ADD;
3140: END IF;
3141: CLOSE val_all_wo_closed;
3142: END IF;
3196: x_msg_count => l_msg_count ,
3197: x_msg_data => l_msg_data
3198: );
3199: IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3200: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SO_CAN_FLD');
3201: FND_MSG_PUB.ADD;
3202: END IF;
3203: ELSIF ( p_del_cancel_so_lines_tbl IS NOT NULL) THEN
3204: IF NOT(p_del_cancel_so_lines_tbl.COUNT > 0) THEN
3291: x_msg_count => l_msg_count ,
3292: x_msg_data => l_msg_data
3293: );
3294: IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3295: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_SO_LN_CAN_FLD');
3296: FND_MSG_PUB.ADD;
3297: END IF;
3298: END IF;
3299: END IF;
3327: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3328: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Begin Function');
3329: END IF;
3330: IF(p_old_type_code NOT IN (G_OSP_ORDER_TYPE_SERVICE, G_OSP_ORDER_TYPE_EXCHANGE)) THEN
3331: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CONV_FR_TYPE');
3332: FND_MSG_PUB.ADD;
3333: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3334: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY ||'.error', 'Cannot Convert from Order Type is not Service or Exchange');
3335: END IF;
3335: END IF;
3336: return false;
3337: END IF;
3338: IF(p_new_type_code NOT IN (G_OSP_ORDER_TYPE_SERVICE, G_OSP_ORDER_TYPE_EXCHANGE)) THEN
3339: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CONV_TO_TYPE');
3340: FND_MSG_PUB.ADD;
3341: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3342: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY ||'.error', 'Cannot Convert to an order that is not of type Service or Exchange');
3343: END IF;
3346: -- check if the order is closed
3347: OPEN order_header_status_csr(p_osp_order_id);
3348: FETCH order_header_status_csr INTO l_status_code;
3349: IF(l_status_code = G_OSP_CLOSED_STATUS) THEN
3350: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CONV_CLOSED');
3351: FND_MSG_PUB.ADD;
3352: CLOSE order_header_status_csr;
3353: return false;
3354: END IF;
3358: /*
3359: OPEN order_has_ship_return_csr(p_osp_order_id);
3360: FETCH order_has_ship_return_csr INTO l_exist;
3361: IF( order_has_ship_return_csr %FOUND) THEN
3362: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INV_CONV_HAS_RET_LINE');
3363: FND_MSG_PUB.ADD;
3364: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3365: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY ||'.error', 'can_convert: Cannot Convert. Order has shipment return line');
3366: END IF;
3505: --Validate the operation_flag of the header record, and it could be NULL which
3506: --means no change to the header record
3507: IF (p_x_osp_order_rec.operation_flag IS NOT NULL AND
3508: p_x_osp_order_rec.operation_flag NOT IN (G_OP_CREATE, G_OP_UPDATE, G_OP_DELETE)) THEN
3509: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORD_INVOP');
3510: FND_MSG_PUB.add;
3511: RAISE FND_API.G_EXC_ERROR;
3512: END IF;
3513: --Validate the operation_flag of the line records and it couldn't be NULL
3515: --to the lines table
3516: IF (p_x_osp_order_lines_tbl.COUNT > 0) THEN
3517: FOR i IN p_x_osp_order_lines_tbl.FIRST..p_x_osp_order_lines_tbl.LAST LOOP
3518: IF (p_x_osp_order_lines_tbl(i).operation_flag NOT IN (G_OP_CREATE, G_OP_UPDATE, G_OP_DELETE)) THEN
3519: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORD_INVOP');
3520: FND_MSG_PUB.add;
3521: RAISE FND_API.G_EXC_ERROR;
3522: END IF;
3523: END LOOP;
3563: FROM ahl_osp_orders_b
3564: WHERE osp_order_id = l_osp_order_id;
3565: EXCEPTION
3566: WHEN NO_DATA_FOUND THEN
3567: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_LN_INV');
3568: FND_MESSAGE.Set_Token('OSP_LINE_ID',p_x_osp_order_lines_tbl(i).osp_order_line_id);
3569: FND_MSG_PUB.ADD;
3570: RAISE FND_API.G_EXC_ERROR;
3571: END;
3564: WHERE osp_order_id = l_osp_order_id;
3565: EXCEPTION
3566: WHEN NO_DATA_FOUND THEN
3567: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_LN_INV');
3568: FND_MESSAGE.Set_Token('OSP_LINE_ID',p_x_osp_order_lines_tbl(i).osp_order_line_id);
3569: FND_MSG_PUB.ADD;
3570: RAISE FND_API.G_EXC_ERROR;
3571: END;
3572: -- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
3617: delete_cancel_so(
3618: p_oe_header_id => NULL,
3619: p_del_cancel_so_lines_tbl => l_del_cancel_so_lines_tbl);
3620: ELSE
3621: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INVOP');
3622: FND_MSG_PUB.ADD;
3623: RAISE FND_API.G_EXC_ERROR;
3624: END IF;
3625: ELSIF (p_x_osp_order_lines_tbl(i).operation_flag = G_OP_CREATE) THEN
3740: FROM ahl_osp_orders_b
3741: WHERE osp_order_id = p_x_osp_order_lines_tbl(l_first_index).osp_order_id;
3742: EXCEPTION
3743: WHEN NO_DATA_FOUND THEN
3744: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORD_INVALID');
3745: FND_MSG_PUB.add;
3746: RAISE FND_API.G_EXC_ERROR;
3747: END;
3748:
3758: END IF;
3759: END LOOP;
3760: END IF;
3761: IF NOT l_validate_pass_flag THEN
3762: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ITEM_VENDOR_MISMATCH');
3763: FND_MSG_PUB.add;
3764: RAISE FND_API.G_EXC_ERROR;
3765: END IF;
3766: ELSE
3763: FND_MSG_PUB.add;
3764: RAISE FND_API.G_EXC_ERROR;
3765: END IF;
3766: ELSE
3767: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ITEM_VENDOR_MISMATCH');
3768: FND_MSG_PUB.add;
3769: RAISE FND_API.G_EXC_ERROR;
3770: END IF;
3771: END IF;
3787: FROM ahl_osp_orders_b
3788: WHERE osp_order_id = p_x_osp_order_rec.osp_order_id;
3789: EXCEPTION
3790: WHEN NO_DATA_FOUND THEN
3791: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORD_INVALID');
3792: FND_MSG_PUB.add;
3793: RAISE FND_API.G_EXC_ERROR;
3794: END;
3795: --check the osp order could be deleted, if Yes then
3813: FROM ahl_osp_order_lines
3814: WHERE osp_order_line_id = l_get_order_lines.osp_order_line_id;
3815: EXCEPTION
3816: WHEN NO_DATA_FOUND THEN
3817: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_LN_INV');
3818: FND_MESSAGE.Set_Token('OSP_LINE_ID',l_get_order_lines.osp_order_line_id);
3819: FND_MSG_PUB.ADD;
3820: RAISE FND_API.G_EXC_ERROR;
3821: END;
3814: WHERE osp_order_line_id = l_get_order_lines.osp_order_line_id;
3815: EXCEPTION
3816: WHEN NO_DATA_FOUND THEN
3817: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_LN_INV');
3818: FND_MESSAGE.Set_Token('OSP_LINE_ID',l_get_order_lines.osp_order_line_id);
3819: FND_MSG_PUB.ADD;
3820: RAISE FND_API.G_EXC_ERROR;
3821: END;
3822: IF (l_oe_ship_line_id IS NOT NULL OR l_oe_return_line_id IS NOT NULL) THEN
3882: G_LOG_PREFIX||l_api_name,
3883: 'After deleting header.');
3884: END IF;
3885: ELSE
3886: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INVOP');
3887: FND_MSG_PUB.ADD;
3888: RAISE FND_API.G_EXC_ERROR;
3889: END IF;
3890: ELSIF (p_x_osp_order_rec.operation_flag = G_OP_UPDATE) THEN
3914: FROM ahl_osp_orders_b
3915: WHERE osp_order_id = l_osp_order_id;
3916: EXCEPTION
3917: WHEN NO_DATA_FOUND THEN
3918: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_LN_INV');
3919: FND_MESSAGE.Set_Token('OSP_LINE_ID',p_x_osp_order_lines_tbl(i).osp_order_line_id);
3920: FND_MSG_PUB.ADD;
3921: RAISE FND_API.G_EXC_ERROR;
3922: END;
3915: WHERE osp_order_id = l_osp_order_id;
3916: EXCEPTION
3917: WHEN NO_DATA_FOUND THEN
3918: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_LN_INV');
3919: FND_MESSAGE.Set_Token('OSP_LINE_ID',p_x_osp_order_lines_tbl(i).osp_order_line_id);
3920: FND_MSG_PUB.ADD;
3921: RAISE FND_API.G_EXC_ERROR;
3922: END;
3923: -- Added by jaramana on January 9, 2008 for the Requisition ER 6034236
3970: delete_cancel_so(
3971: p_oe_header_id => NULL,
3972: p_del_cancel_so_lines_tbl => l_del_cancel_so_lines_tbl);
3973: ELSE
3974: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INVOP');
3975: FND_MSG_PUB.ADD;
3976: RAISE FND_API.G_EXC_ERROR;
3977: END IF;
3978: --AHL_OSP_ORDER_LINES_PKG.delete_row(p_x_osp_order_lines_tbl(i).osp_order_line_id);
4085: FROM ahl_osp_orders_b
4086: WHERE osp_order_id = p_x_osp_order_rec.osp_order_id;
4087: EXCEPTION
4088: WHEN NO_DATA_FOUND THEN
4089: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORD_INVALID');
4090: FND_MSG_PUB.add;
4091: RAISE FND_API.G_EXC_ERROR;
4092: END;
4093: IF l_any_vendor_flag <> 'Y' THEN
4103: END IF;
4104: END LOOP;
4105: END IF;
4106: IF NOT l_validate_pass_flag THEN
4107: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ITEM_VENDOR_MISMATCH');
4108: FND_MSG_PUB.add;
4109: RAISE FND_API.G_EXC_ERROR;
4110: END IF;
4111: ELSE
4108: FND_MSG_PUB.add;
4109: RAISE FND_API.G_EXC_ERROR;
4110: END IF;
4111: ELSE
4112: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ITEM_VENDOR_MISMATCH');
4113: FND_MSG_PUB.add;
4114: RAISE FND_API.G_EXC_ERROR;
4115: END IF;
4116: END IF;
4316: l_osp_order_number := l_osp_order_id;
4317: --Derive operating_unit_id
4318: l_operating_unit_id :=mo_global.get_current_org_id();
4319: IF (l_operating_unit_id IS NULL) THEN
4320: FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_ORG_NOT_SET');
4321: FND_MSG_PUB.ADD;
4322: RAISE FND_API.G_EXC_ERROR;
4323: END IF;
4324: --Validate the only user passed parameter order_type_code
4325: IF (p_x_osp_order_rec.order_type_code IS NULL OR
4326: p_x_osp_order_rec.order_type_code NOT IN (G_OSP_ORDER_TYPE_SERVICE,
4327: G_OSP_ORDER_TYPE_EXCHANGE,
4328: G_OSP_ORDER_TYPE_BORROW)) THEN
4329: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORDER_TYPE_INVALID');
4330: FND_MSG_PUB.add;
4331: RAISE FND_API.G_EXC_ERROR;
4332: END IF;
4333:
4426: l_item_service_rels_tbl(i).inv_item_id := l_inventory_item_id;
4427: l_item_service_rels_tbl(i).service_item_id := l_service_item_id;
4428: EXCEPTION
4429: WHEN NO_DATA_FOUND THEN
4430: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_WO');
4431: FND_MESSAGE.Set_Token('WORKORDER_ID', p_x_osp_order_lines_tbl(i).workorder_id);
4432: FND_MSG_PUB.ADD;
4433: RAISE FND_API.G_EXC_ERROR;
4434: END;
4427: l_item_service_rels_tbl(i).service_item_id := l_service_item_id;
4428: EXCEPTION
4429: WHEN NO_DATA_FOUND THEN
4430: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_WO');
4431: FND_MESSAGE.Set_Token('WORKORDER_ID', p_x_osp_order_lines_tbl(i).workorder_id);
4432: FND_MSG_PUB.ADD;
4433: RAISE FND_API.G_EXC_ERROR;
4434: END;
4435: END LOOP;
4455: END LOOP;
4456: END IF;
4457: IF (l_vendor_valid_flag = FALSE) THEN
4458: -- The vendor or vendor + vendor site passed in is not a valid: Throw Error
4459: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
4460: FND_MESSAGE.Set_Token('VENDOR_ID', p_x_osp_order_rec.vendor_id);
4461: FND_MSG_PUB.ADD;
4462: RAISE FND_API.G_EXC_ERROR;
4463: END IF;
4456: END IF;
4457: IF (l_vendor_valid_flag = FALSE) THEN
4458: -- The vendor or vendor + vendor site passed in is not a valid: Throw Error
4459: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
4460: FND_MESSAGE.Set_Token('VENDOR_ID', p_x_osp_order_rec.vendor_id);
4461: FND_MSG_PUB.ADD;
4462: RAISE FND_API.G_EXC_ERROR;
4463: END IF;
4464: IF (p_x_osp_order_rec.vendor_site_id IS NOT NULL) THEN
4874: WHERE inventory_item_id = p_x_osp_order_line_rec.service_item_id
4875: AND organization_id = p_x_osp_order_line_rec.inventory_org_id;
4876: EXCEPTION
4877: WHEN NO_DATA_FOUND THEN
4878: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM');
4879: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_x_osp_order_line_rec.service_item_id);
4880: FND_MSG_PUB.ADD;
4881: END;
4882:
4875: AND organization_id = p_x_osp_order_line_rec.inventory_org_id;
4876: EXCEPTION
4877: WHEN NO_DATA_FOUND THEN
4878: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM');
4879: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_x_osp_order_line_rec.service_item_id);
4880: FND_MSG_PUB.ADD;
4881: END;
4882:
4883: -- yazhou 06-Jul-2006 starts
4901: l_serial_prefix := FND_PROFILE.VALUE('AHL_OSP_POL_SER_PREFIX');
4902:
4903: IF(l_owrite_svc_desc_prf = 'Y') THEN --Overwrite Svc Description profile set to Yes
4904: IF(NVL(l_desc_update_flag, 'N') = 'N') THEN --Allow Description update set to No
4905: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_SVC_DESC_NOCHNG');
4906: FND_MESSAGE.Set_Token('SERVICE_ITEM_NUMBER', l_svc_item_number);
4907: FND_MSG_PUB.ADD;
4908: ELSE
4909: BEGIN
4902:
4903: IF(l_owrite_svc_desc_prf = 'Y') THEN --Overwrite Svc Description profile set to Yes
4904: IF(NVL(l_desc_update_flag, 'N') = 'N') THEN --Allow Description update set to No
4905: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_SVC_DESC_NOCHNG');
4906: FND_MESSAGE.Set_Token('SERVICE_ITEM_NUMBER', l_svc_item_number);
4907: FND_MSG_PUB.ADD;
4908: ELSE
4909: BEGIN
4910: SELECT concatenated_segments INTO l_inv_item_number
4912: WHERE inventory_item_id = p_x_osp_order_line_rec.inventory_item_id
4913: AND organization_id = p_x_osp_order_line_rec.inventory_org_id;
4914: EXCEPTION
4915: WHEN NO_DATA_FOUND THEN
4916: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PHY_ITEM_INVALID');
4917: FND_MSG_PUB.ADD;
4918: END;
4919: -- SALOGAN added an if condition to avoid adding trailing spaces for Non Serial Item
4920: -- for bug #9826606 Starts
5185: FROM ahl_osp_orders_b
5186: WHERE osp_order_id = l_osp_order_id;
5187: EXCEPTION
5188: WHEN NO_DATA_FOUND THEN
5189: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_INVALID');
5190: FND_MSG_PUB.ADD;
5191: RAISE FND_API.G_EXC_ERROR;
5192: END;
5193: l_oe_header_rec.header_id := l_oe_header_id;
5521: -- yazhou 28-jul-2006 ends
5522:
5523: EXCEPTION
5524: WHEN NO_DATA_FOUND THEN
5525: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORD_INVALID');
5526: FND_MSG_PUB.add;
5527: END;
5528:
5529: -- Added by jaramana on January 10, 2008 for the Requisition ER 6034236 (partial fix for the Bug 5358438/5967633/5417460)
5532: --we would have derived the workorder_id
5533: 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
5534: validate_po_line(p_x_osp_order_line_rec.po_line_id, p_x_osp_order_line_rec.osp_order_id);
5535: 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
5536: FND_MESSAGE.set_name('AHL', 'AHL_OSP_WO_NLL_CR_POL');
5537: FND_MSG_PUB.add;
5538: END IF;
5539: -- jaramna End
5540:
5541: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5542: 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);
5543: END IF;
5544: IF (p_x_osp_order_line_rec.workorder_id IS NULL AND p_x_osp_order_line_rec.inventory_item_id IS NULL) THEN
5545: FND_MESSAGE.set_name('AHL', 'AHL_OSP_WO_ITEM_ALL_NULL');
5546: FND_MSG_PUB.add;
5547: --Validate workorder_id (borrow the old code)
5548: ELSIF(p_x_osp_order_line_rec.workorder_id IS NOT NULL) THEN
5549: validate_workorder(p_x_osp_order_line_rec.workorder_id);
5549: validate_workorder(p_x_osp_order_line_rec.workorder_id);
5550: --Validate physical item
5551: ELSIF (p_x_osp_order_line_rec.inventory_item_id IS NOT NULL) THEN
5552: IF (p_x_osp_order_line_rec.inventory_org_id IS NULL) THEN
5553: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ITEM_ORG_NULL');
5554: FND_MSG_PUB.add;
5555: END IF;
5556: OPEN check_physical_item(p_x_osp_order_line_rec.inventory_item_id,
5557: p_x_osp_order_line_rec.inventory_org_id);
5559: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5560: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_LOG_PREFIX || '.validate_order_line_creation', 'track_flag='||l_check_physical_item.comms_nl_trackable_flag);
5561: END IF;
5562: IF check_physical_item%NOTFOUND THEN
5563: FND_MESSAGE.set_name('AHL', 'AHL_OSP_PHY_ITEM_INVALID');
5564: FND_MSG_PUB.add;
5565: ELSE
5566: IF (p_x_osp_order_line_rec.sub_inventory IS NOT NULL) THEN
5567: OPEN check_sub_inventory(p_x_osp_order_line_rec.inventory_org_id,
5567: OPEN check_sub_inventory(p_x_osp_order_line_rec.inventory_org_id,
5568: p_x_osp_order_line_rec.sub_inventory);
5569: FETCH check_sub_inventory INTO g_dummy_char;
5570: IF check_sub_inventory%NOTFOUND THEN
5571: FND_MESSAGE.set_name('AHL', 'AHL_OSP_SUB_INV_INVALID');
5572: FND_MSG_PUB.add;
5573: END IF;
5574: CLOSE check_sub_inventory;
5575: END IF;
5576:
5577: --Non tracked physical item's quantity (to be serviced) is manadatory
5578: IF (nvl(l_check_physical_item.comms_nl_trackable_flag, 'N')='N' AND
5579: (p_x_osp_order_line_rec.inventory_item_quantity IS NULL)) THEN
5580: FND_MESSAGE.set_name('AHL', 'AHL_OSP_PHY_ITEM_QTY_REQUIRED');
5581: FND_MSG_PUB.add;
5582: END IF;
5583:
5584: /*
5589: OPEN check_serial_number(p_x_osp_order_line_rec.inventory_item_id,
5590: p_x_osp_order_line_rec.serial_number);
5591: FETCH check_serial_number INTO g_dummy_char;
5592: IF check_serial_number%NOTFOUND THEN
5593: FND_MESSAGE.set_name('AHL', 'AHL_OSP_PHY_ITEM_SN_INVALID');
5594: FND_MSG_PUB.add;
5595: ELSIF p_x_osp_order_line_rec.inventory_item_quantity <> 1 THEN
5596: FND_MESSAGE.set_name('AHL', 'AHL_OSP_PHY_ITEM_QT_INVALID');
5597: FND_MSG_PUB.add;
5592: IF check_serial_number%NOTFOUND THEN
5593: FND_MESSAGE.set_name('AHL', 'AHL_OSP_PHY_ITEM_SN_INVALID');
5594: FND_MSG_PUB.add;
5595: ELSIF p_x_osp_order_line_rec.inventory_item_quantity <> 1 THEN
5596: FND_MESSAGE.set_name('AHL', 'AHL_OSP_PHY_ITEM_QT_INVALID');
5597: FND_MSG_PUB.add;
5598: END IF;
5599: CLOSE check_serial_number;
5600: ELSIF l_check_physical_item.lot_control_code = 2 THEN
5602: p_x_osp_order_line_rec.inventory_org_id,
5603: p_x_osp_order_line_rec.lot_number);
5604: FETCH check_lot_number INTO g_dummy_char;
5605: IF check_lot_number%NOTFOUND THEN
5606: FND_MESSAGE.set_name('AHL', 'AHL_OSP_PHY_ITEM_LOT_INVALID');
5607: FND_MSG_PUB.add;
5608: END IF;
5609: CLOSE check_lot_number;
5610: --Validate the onhand quantity with the quantity to be serviced
5616: --Added by mpothuku on 23rd Aug, 06 to fix the Bug 5252627
5617: p_x_osp_order_line_rec.lot_number);
5618: FETCH get_onhand_quantity INTO l_onhand_quantity, l_uom_code;
5619: IF get_onhand_quantity%NOTFOUND THEN
5620: FND_MESSAGE.set_name('AHL', 'AHL_OSP_PHY_ITEM_INVALID');
5621: FND_MSG_PUB.add;
5622: END IF;
5623: CLOSE get_onhand_quantity;
5624: IF (l_onhand_quantity IS NOT NULL AND l_uom_code IS NOT NULL) THEN
5634: to_unit => l_uom_code,
5635: from_name => NULL,
5636: to_name => NULL);
5637: IF (l_quantity > l_onhand_quantity) THEN
5638: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ITEM_ONHAND_LESS_SERV');
5639: FND_MSG_PUB.add;
5640: END IF;
5641: END IF;
5642: END IF;
5706: END IF;
5707:
5708:
5709: IF (l_po_line_attrs.item_id is null and l_po_line_attrs.item_description is null) THEN
5710: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_ITEM_ID_DESC_NLL');
5711: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5712: FND_MSG_PUB.ADD;
5713: END IF;
5714:
5707:
5708:
5709: IF (l_po_line_attrs.item_id is null and l_po_line_attrs.item_description is null) THEN
5710: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_ITEM_ID_DESC_NLL');
5711: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5712: FND_MSG_PUB.ADD;
5713: END IF;
5714:
5715: IF (l_po_line_attrs.line_type_id is null) THEN
5712: FND_MSG_PUB.ADD;
5713: END IF;
5714:
5715: IF (l_po_line_attrs.line_type_id is null) THEN
5716: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_TYPE_NLL');
5717: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5718: FND_MSG_PUB.ADD;
5719: END IF;
5720:
5713: END IF;
5714:
5715: IF (l_po_line_attrs.line_type_id is null) THEN
5716: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_TYPE_NLL');
5717: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5718: FND_MSG_PUB.ADD;
5719: END IF;
5720:
5721: IF (l_po_line_attrs.uom_code is null) THEN
5718: FND_MSG_PUB.ADD;
5719: END IF;
5720:
5721: IF (l_po_line_attrs.uom_code is null) THEN
5722: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_UOM_NLL');
5723: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5724: FND_MSG_PUB.ADD;
5725: END IF;
5726:
5719: END IF;
5720:
5721: IF (l_po_line_attrs.uom_code is null) THEN
5722: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_UOM_NLL');
5723: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5724: FND_MSG_PUB.ADD;
5725: END IF;
5726:
5727: IF (l_po_line_attrs.quantity is null) THEN
5724: FND_MSG_PUB.ADD;
5725: END IF;
5726:
5727: IF (l_po_line_attrs.quantity is null) THEN
5728: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_QUANT_NLL');
5729: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5730: FND_MSG_PUB.ADD;
5731: END IF;
5732:
5725: END IF;
5726:
5727: IF (l_po_line_attrs.quantity is null) THEN
5728: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_QUANT_NLL');
5729: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5730: FND_MSG_PUB.ADD;
5731: END IF;
5732:
5733: IF (l_po_line_attrs.need_by_date is null) THEN
5730: FND_MSG_PUB.ADD;
5731: END IF;
5732:
5733: IF (l_po_line_attrs.need_by_date is null) THEN
5734: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_NBDATE_NLL');
5735: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5736: FND_MSG_PUB.ADD;
5737: END IF;
5738:
5731: END IF;
5732:
5733: IF (l_po_line_attrs.need_by_date is null) THEN
5734: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_POL_NBDATE_NLL');
5735: FND_MESSAGE.Set_Token('LINE_NUM', l_po_line_attrs.line_num);
5736: FND_MSG_PUB.ADD;
5737: END IF;
5738:
5739: END IF;
5747: */
5748: val_svc_item_vs_wo_svc_item (p_x_osp_order_line_rec.workorder_id, l_po_line_attrs.item_id);
5749: 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);
5750: IF(l_po_line_attrs.quantity <=0) THEN
5751: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_VAL');
5752: FND_MESSAGE.Set_Token('QUANTITY', p_x_osp_order_line_rec.quantity);
5753: FND_MSG_PUB.ADD;
5754: END IF;
5755: validate_po_line_type(l_po_line_attrs.line_type_id);
5748: val_svc_item_vs_wo_svc_item (p_x_osp_order_line_rec.workorder_id, l_po_line_attrs.item_id);
5749: 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);
5750: IF(l_po_line_attrs.quantity <=0) THEN
5751: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_VAL');
5752: FND_MESSAGE.Set_Token('QUANTITY', p_x_osp_order_line_rec.quantity);
5753: FND_MSG_PUB.ADD;
5754: END IF;
5755: validate_po_line_type(l_po_line_attrs.line_type_id);
5756: --Assign the attributes from the po line to the osp line
5778: validate_service_item_uom(p_x_osp_order_line_rec.service_item_id, p_x_osp_order_line_rec.service_item_uom_code, p_x_osp_order_line_rec.inventory_org_id);
5779: --validate sercice_item_quantity(quantity)
5780: 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
5781: IF(p_x_osp_order_line_rec.quantity <=0) THEN
5782: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_VAL');
5783: FND_MESSAGE.Set_Token('QUANTITY', p_x_osp_order_line_rec.quantity);
5784: FND_MSG_PUB.ADD;
5785: END IF;
5786: --quantity cannot be null when UOM is not null
5779: --validate sercice_item_quantity(quantity)
5780: 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
5781: IF(p_x_osp_order_line_rec.quantity <=0) THEN
5782: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_VAL');
5783: FND_MESSAGE.Set_Token('QUANTITY', p_x_osp_order_line_rec.quantity);
5784: FND_MSG_PUB.ADD;
5785: END IF;
5786: --quantity cannot be null when UOM is not null
5787: 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
5784: FND_MSG_PUB.ADD;
5785: END IF;
5786: --quantity cannot be null when UOM is not null
5787: 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
5788: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_NLL');
5789: FND_MSG_PUB.ADD;
5790: END IF;
5791:
5792: --Validate header vendor with physical, service item combination in line
5815:
5816: FETCH check_osp_order_unique INTO g_dummy_char;
5817:
5818: IF check_osp_order_unique%FOUND THEN
5819: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PHY_ITEM_UNIUE');
5820: FND_MSG_PUB.ADD;
5821: END IF;
5822: CLOSE check_osp_order_unique;
5823: END IF;
5830: OPEN check_phy_item_unique(p_x_osp_order_line_rec.osp_order_id,
5831: p_x_osp_order_line_rec.inventory_item_id);
5832: FETCH check_phy_item_unique INTO g_dummy_char;
5833: IF check_phy_item_unique%FOUND THEN
5834: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_PHY_ITEM_UNIUE');
5835: FND_MSG_PUB.ADD;
5836: END IF;
5837: CLOSE check_phy_item_unique;
5838: --ELSIF (p_x_osp_order_line_rec.service_item_id IS NOT NULL) THEN
5845: p_x_osp_order_line_rec.lot_number,
5846: p_x_osp_order_line_rec.serial_number);
5847: FETCH check_phy_ser_item_unique INTO g_dummy_char;
5848: IF check_phy_ser_item_unique%FOUND THEN
5849: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_PHY_SER_ITEM_UNIUE');
5850: FND_MSG_PUB.ADD;
5851: END IF;
5852: CLOSE check_phy_ser_item_unique;
5853: END IF;
6060: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6061: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Begin Procedure');
6062: END IF;
6063: 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
6064: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_ID_OBJ_MISS');
6065: FND_MSG_PUB.ADD;
6066: ELSE
6067: OPEN osp_order_line_csr;
6068: FETCH osp_order_line_csr INTO l_osp_order_line_rec;
6066: ELSE
6067: OPEN osp_order_line_csr;
6068: FETCH osp_order_line_csr INTO l_osp_order_line_rec;
6069: IF (osp_order_line_csr%NOTFOUND) THEN
6070: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INVOP_OSP_LN_NFOUND');
6071: FND_MSG_PUB.ADD;
6072: ELSE
6073: --The following attributes can't be changed once created
6074: --osp_order_id can't be changed, and osp_order_id is required
6073: --The following attributes can't be changed once created
6074: --osp_order_id can't be changed, and osp_order_id is required
6075: IF(p_x_osp_order_line_rec.osp_order_id IS NULL OR
6076: p_x_osp_order_line_rec.osp_order_id <> l_osp_order_line_rec.osp_order_id) THEN
6077: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_ORD_ID_CHG');
6078: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6079: FND_MSG_PUB.ADD;
6080: END IF;
6081: --osp_order_number can't be changed and osp_order_number is required
6074: --osp_order_id can't be changed, and osp_order_id is required
6075: IF(p_x_osp_order_line_rec.osp_order_id IS NULL OR
6076: p_x_osp_order_line_rec.osp_order_id <> l_osp_order_line_rec.osp_order_id) THEN
6077: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_ORD_ID_CHG');
6078: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6079: FND_MSG_PUB.ADD;
6080: END IF;
6081: --osp_order_number can't be changed and osp_order_number is required
6082: IF(p_x_osp_order_line_rec.osp_line_number IS NULL OR
6080: END IF;
6081: --osp_order_number can't be changed and osp_order_number is required
6082: IF(p_x_osp_order_line_rec.osp_line_number IS NULL OR
6083: p_x_osp_order_line_rec.osp_line_number <> l_osp_order_line_rec.osp_line_number) THEN
6084: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_LN_NUM_CHG');
6085: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6086: FND_MSG_PUB.ADD;
6087: END IF;
6088: --workorder_id can't be changed but workorder can be null
6081: --osp_order_number can't be changed and osp_order_number is required
6082: IF(p_x_osp_order_line_rec.osp_line_number IS NULL OR
6083: p_x_osp_order_line_rec.osp_line_number <> l_osp_order_line_rec.osp_line_number) THEN
6084: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_LN_NUM_CHG');
6085: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6086: FND_MSG_PUB.ADD;
6087: END IF;
6088: --workorder_id can't be changed but workorder can be null
6089: IF((p_x_osp_order_line_rec.workorder_id <> l_osp_order_line_rec.workorder_id) OR
6088: --workorder_id can't be changed but workorder can be null
6089: IF((p_x_osp_order_line_rec.workorder_id <> l_osp_order_line_rec.workorder_id) OR
6090: (p_x_osp_order_line_rec.workorder_id IS NOT NULL AND l_osp_order_line_rec.workorder_id IS NULL) OR
6091: (p_x_osp_order_line_rec.workorder_id IS NULL AND l_osp_order_line_rec.workorder_id IS NOT NULL)) THEN
6092: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_WO_CHG');
6093: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6094: FND_MSG_PUB.ADD;
6095: END IF;
6096: --inventory_item_id can't be changed and inventory_item_id is always populated
6089: IF((p_x_osp_order_line_rec.workorder_id <> l_osp_order_line_rec.workorder_id) OR
6090: (p_x_osp_order_line_rec.workorder_id IS NOT NULL AND l_osp_order_line_rec.workorder_id IS NULL) OR
6091: (p_x_osp_order_line_rec.workorder_id IS NULL AND l_osp_order_line_rec.workorder_id IS NOT NULL)) THEN
6092: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_WO_CHG');
6093: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6094: FND_MSG_PUB.ADD;
6095: END IF;
6096: --inventory_item_id can't be changed and inventory_item_id is always populated
6097: IF(p_x_osp_order_line_rec.inventory_item_id IS NULL OR
6095: END IF;
6096: --inventory_item_id can't be changed and inventory_item_id is always populated
6097: IF(p_x_osp_order_line_rec.inventory_item_id IS NULL OR
6098: p_x_osp_order_line_rec.inventory_item_id <> l_osp_order_line_rec.inventory_item_id) THEN
6099: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_ITEM_CHG');
6100: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6101: FND_MSG_PUB.ADD;
6102: END IF;
6103: --inventory_org_id can't be changed and inventory_org_id is always popluated
6096: --inventory_item_id can't be changed and inventory_item_id is always populated
6097: IF(p_x_osp_order_line_rec.inventory_item_id IS NULL OR
6098: p_x_osp_order_line_rec.inventory_item_id <> l_osp_order_line_rec.inventory_item_id) THEN
6099: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_ITEM_CHG');
6100: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6101: FND_MSG_PUB.ADD;
6102: END IF;
6103: --inventory_org_id can't be changed and inventory_org_id is always popluated
6104: IF(p_x_osp_order_line_rec.inventory_org_id IS NULL OR
6102: END IF;
6103: --inventory_org_id can't be changed and inventory_org_id is always popluated
6104: IF(p_x_osp_order_line_rec.inventory_org_id IS NULL OR
6105: p_x_osp_order_line_rec.inventory_org_id <> l_osp_order_line_rec.inventory_org_id) THEN
6106: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_ORG_CHG');
6107: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6108: FND_MSG_PUB.ADD;
6109: END IF;
6110: --inventory_item_uom can't be changed and inventory_item_uom is always populated
6103: --inventory_org_id can't be changed and inventory_org_id is always popluated
6104: IF(p_x_osp_order_line_rec.inventory_org_id IS NULL OR
6105: p_x_osp_order_line_rec.inventory_org_id <> l_osp_order_line_rec.inventory_org_id) THEN
6106: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_ORG_CHG');
6107: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6108: FND_MSG_PUB.ADD;
6109: END IF;
6110: --inventory_item_uom can't be changed and inventory_item_uom is always populated
6111: IF(p_x_osp_order_line_rec.inventory_item_uom IS NULL OR
6109: END IF;
6110: --inventory_item_uom can't be changed and inventory_item_uom is always populated
6111: IF(p_x_osp_order_line_rec.inventory_item_uom IS NULL OR
6112: p_x_osp_order_line_rec.inventory_item_uom <> l_osp_order_line_rec.inventory_item_uom) THEN
6113: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_UOM_CHG');
6114: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6115: FND_MSG_PUB.ADD;
6116: END IF;
6117: --inventory_item_quantity can't be changed and inventory_item_quantity is always populated
6110: --inventory_item_uom can't be changed and inventory_item_uom is always populated
6111: IF(p_x_osp_order_line_rec.inventory_item_uom IS NULL OR
6112: p_x_osp_order_line_rec.inventory_item_uom <> l_osp_order_line_rec.inventory_item_uom) THEN
6113: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_UOM_CHG');
6114: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6115: FND_MSG_PUB.ADD;
6116: END IF;
6117: --inventory_item_quantity can't be changed and inventory_item_quantity is always populated
6118: IF(p_x_osp_order_line_rec.inventory_item_quantity IS NULL OR
6116: END IF;
6117: --inventory_item_quantity can't be changed and inventory_item_quantity is always populated
6118: IF(p_x_osp_order_line_rec.inventory_item_quantity IS NULL OR
6119: p_x_osp_order_line_rec.inventory_item_quantity <> l_osp_order_line_rec.inventory_item_quantity) THEN
6120: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QTY_CHG');
6121: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6122: FND_MSG_PUB.ADD;
6123: END IF;
6124: --sub_inventory can't be changed but sub_inventory can be null
6117: --inventory_item_quantity can't be changed and inventory_item_quantity is always populated
6118: IF(p_x_osp_order_line_rec.inventory_item_quantity IS NULL OR
6119: p_x_osp_order_line_rec.inventory_item_quantity <> l_osp_order_line_rec.inventory_item_quantity) THEN
6120: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QTY_CHG');
6121: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6122: FND_MSG_PUB.ADD;
6123: END IF;
6124: --sub_inventory can't be changed but sub_inventory can be null
6125: IF((p_x_osp_order_line_rec.sub_inventory <> l_osp_order_line_rec.sub_inventory) OR
6124: --sub_inventory can't be changed but sub_inventory can be null
6125: IF((p_x_osp_order_line_rec.sub_inventory <> l_osp_order_line_rec.sub_inventory) OR
6126: (p_x_osp_order_line_rec.sub_inventory IS NOT NULL AND l_osp_order_line_rec.sub_inventory IS NULL) OR
6127: (p_x_osp_order_line_rec.sub_inventory IS NULL AND l_osp_order_line_rec.sub_inventory IS NOT NULL)) THEN
6128: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SUB_CHG');
6129: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6130: FND_MSG_PUB.ADD;
6131: END IF;
6132: --lot_number can't be changed but lot_number can be null
6125: IF((p_x_osp_order_line_rec.sub_inventory <> l_osp_order_line_rec.sub_inventory) OR
6126: (p_x_osp_order_line_rec.sub_inventory IS NOT NULL AND l_osp_order_line_rec.sub_inventory IS NULL) OR
6127: (p_x_osp_order_line_rec.sub_inventory IS NULL AND l_osp_order_line_rec.sub_inventory IS NOT NULL)) THEN
6128: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SUB_CHG');
6129: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6130: FND_MSG_PUB.ADD;
6131: END IF;
6132: --lot_number can't be changed but lot_number can be null
6133: IF((p_x_osp_order_line_rec.lot_number <> l_osp_order_line_rec.lot_number) OR
6132: --lot_number can't be changed but lot_number can be null
6133: IF((p_x_osp_order_line_rec.lot_number <> l_osp_order_line_rec.lot_number) OR
6134: (p_x_osp_order_line_rec.lot_number IS NOT NULL AND l_osp_order_line_rec.lot_number IS NULL) OR
6135: (p_x_osp_order_line_rec.lot_number IS NULL AND l_osp_order_line_rec.lot_number IS NOT NULL)) THEN
6136: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_LOT_CHG');
6137: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6138: FND_MSG_PUB.ADD;
6139: END IF;
6140: --serial_number can't be changed but serial_number can be null
6133: IF((p_x_osp_order_line_rec.lot_number <> l_osp_order_line_rec.lot_number) OR
6134: (p_x_osp_order_line_rec.lot_number IS NOT NULL AND l_osp_order_line_rec.lot_number IS NULL) OR
6135: (p_x_osp_order_line_rec.lot_number IS NULL AND l_osp_order_line_rec.lot_number IS NOT NULL)) THEN
6136: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_LOT_CHG');
6137: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6138: FND_MSG_PUB.ADD;
6139: END IF;
6140: --serial_number can't be changed but serial_number can be null
6141: IF((p_x_osp_order_line_rec.serial_number <> l_osp_order_line_rec.serial_number) OR
6140: --serial_number can't be changed but serial_number can be null
6141: IF((p_x_osp_order_line_rec.serial_number <> l_osp_order_line_rec.serial_number) OR
6142: (p_x_osp_order_line_rec.serial_number IS NOT NULL AND l_osp_order_line_rec.serial_number IS NULL) OR
6143: (p_x_osp_order_line_rec.serial_number IS NULL AND l_osp_order_line_rec.serial_number IS NOT NULL)) THEN
6144: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SERIAL_CHG');
6145: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6146: FND_MSG_PUB.ADD;
6147: END IF;
6148: -- this API does not allow to update SO line information, Shipment API needs to update
6141: IF((p_x_osp_order_line_rec.serial_number <> l_osp_order_line_rec.serial_number) OR
6142: (p_x_osp_order_line_rec.serial_number IS NOT NULL AND l_osp_order_line_rec.serial_number IS NULL) OR
6143: (p_x_osp_order_line_rec.serial_number IS NULL AND l_osp_order_line_rec.serial_number IS NOT NULL)) THEN
6144: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SERIAL_CHG');
6145: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6146: FND_MSG_PUB.ADD;
6147: END IF;
6148: -- this API does not allow to update SO line information, Shipment API needs to update
6149: -- OSP tables directly (is this the best approach?)
6150: --oe_ship_line_id can't be changed from this API, and it could be null.
6151: IF((p_x_osp_order_line_rec.oe_ship_line_id <> l_osp_order_line_rec.oe_ship_line_id) OR
6152: (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
6153: (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
6154: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SHIP_ID_CHG');
6155: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6156: FND_MSG_PUB.ADD;
6157: END IF;
6158: --oe_return_line_id can't be changed from this API, and it could be null.
6151: IF((p_x_osp_order_line_rec.oe_ship_line_id <> l_osp_order_line_rec.oe_ship_line_id) OR
6152: (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
6153: (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
6154: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SHIP_ID_CHG');
6155: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6156: FND_MSG_PUB.ADD;
6157: END IF;
6158: --oe_return_line_id can't be changed from this API, and it could be null.
6159: IF((p_x_osp_order_line_rec.oe_return_line_id <> l_osp_order_line_rec.oe_return_line_id) OR
6158: --oe_return_line_id can't be changed from this API, and it could be null.
6159: IF((p_x_osp_order_line_rec.oe_return_line_id <> l_osp_order_line_rec.oe_return_line_id) OR
6160: (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
6161: (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
6162: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_RET_ID_CHG');
6163: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6164: FND_MSG_PUB.ADD;
6165: END IF;
6166: -- this API does not allow to update PO line information, PO API needs to update
6159: IF((p_x_osp_order_line_rec.oe_return_line_id <> l_osp_order_line_rec.oe_return_line_id) OR
6160: (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
6161: (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
6162: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_RET_ID_CHG');
6163: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6164: FND_MSG_PUB.ADD;
6165: END IF;
6166: -- this API does not allow to update PO line information, PO API needs to update
6167: -- OSP tables directly (is this the best approach?)
6168: --po_line_id can't be changed from this API, and it could be null.
6169: IF((p_x_osp_order_line_rec.po_line_id <> l_osp_order_line_rec.po_line_id) OR
6170: (p_x_osp_order_line_rec.po_line_id IS NOT NULL AND l_osp_order_line_rec.po_line_id IS NULL) OR
6171: (p_x_osp_order_line_rec.po_line_id IS NULL AND l_osp_order_line_rec.po_line_id IS NOT NULL)) THEN
6172: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_POLINE_ID_CHG');
6173: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6174: FND_MSG_PUB.ADD;
6175: END IF;
6176:
6169: IF((p_x_osp_order_line_rec.po_line_id <> l_osp_order_line_rec.po_line_id) OR
6170: (p_x_osp_order_line_rec.po_line_id IS NOT NULL AND l_osp_order_line_rec.po_line_id IS NULL) OR
6171: (p_x_osp_order_line_rec.po_line_id IS NULL AND l_osp_order_line_rec.po_line_id IS NOT NULL)) THEN
6172: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_POLINE_ID_CHG');
6173: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6174: FND_MSG_PUB.ADD;
6175: END IF;
6176:
6177: -- Added by jaramana on January 10, 2008 for the Requisition ER 6034236
6179: --po_req_line_id can't be changed from this API, and it could be null.
6180: IF((p_x_osp_order_line_rec.po_req_line_id <> l_osp_order_line_rec.po_req_line_id) OR
6181: (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
6182: (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
6183: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_REQLINE_ID_CHG');
6184: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6185: FND_MSG_PUB.ADD;
6186: END IF;
6187:
6180: IF((p_x_osp_order_line_rec.po_req_line_id <> l_osp_order_line_rec.po_req_line_id) OR
6181: (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
6182: (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
6183: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_REQLINE_ID_CHG');
6184: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6185: FND_MSG_PUB.ADD;
6186: END IF;
6187:
6188: --po_line_type_id can't be changed from this API, and it could be null.
6188: --po_line_type_id can't be changed from this API, and it could be null.
6189: IF((p_x_osp_order_line_rec.po_line_type_id <> l_osp_order_line_rec.po_line_type_id) OR
6190: (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
6191: (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
6192: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_POTYPE_ID_CHG');
6193: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6194: FND_MSG_PUB.ADD;
6195: END IF;
6196:
6189: IF((p_x_osp_order_line_rec.po_line_type_id <> l_osp_order_line_rec.po_line_type_id) OR
6190: (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
6191: (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
6192: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_POTYPE_ID_CHG');
6193: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6194: FND_MSG_PUB.ADD;
6195: END IF;
6196:
6197: -- Added by jaramana on January 10, 2008 for the Requisition ER 6034236
6205: --service_item_id can't be changed from this API, and it could be null.
6206: IF((p_x_osp_order_line_rec.service_item_id <> l_osp_order_line_rec.service_item_id) OR
6207: (p_x_osp_order_line_rec.service_item_id IS NOT NULL AND l_osp_order_line_rec.service_item_id IS NULL) OR
6208: (p_x_osp_order_line_rec.service_item_id IS NULL AND l_osp_order_line_rec.service_item_id IS NOT NULL)) THEN
6209: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SER_ID_CHG');
6210: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6211: FND_MSG_PUB.ADD;
6212: END IF;
6213:
6206: IF((p_x_osp_order_line_rec.service_item_id <> l_osp_order_line_rec.service_item_id) OR
6207: (p_x_osp_order_line_rec.service_item_id IS NOT NULL AND l_osp_order_line_rec.service_item_id IS NULL) OR
6208: (p_x_osp_order_line_rec.service_item_id IS NULL AND l_osp_order_line_rec.service_item_id IS NOT NULL)) THEN
6209: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SER_ID_CHG');
6210: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6211: FND_MSG_PUB.ADD;
6212: END IF;
6213:
6214: --service_item_description can't be changed from this API, and it could be null.
6214: --service_item_description can't be changed from this API, and it could be null.
6215: IF((p_x_osp_order_line_rec.service_item_description <> l_osp_order_line_rec.service_item_description) OR
6216: (p_x_osp_order_line_rec.service_item_description IS NOT NULL AND l_osp_order_line_rec.service_item_description IS NULL) OR
6217: (p_x_osp_order_line_rec.service_item_description IS NULL AND l_osp_order_line_rec.service_item_description IS NOT NULL)) THEN
6218: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SER_DESC_CHG');
6219: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6220: FND_MSG_PUB.ADD;
6221: END IF;
6222:
6215: IF((p_x_osp_order_line_rec.service_item_description <> l_osp_order_line_rec.service_item_description) OR
6216: (p_x_osp_order_line_rec.service_item_description IS NOT NULL AND l_osp_order_line_rec.service_item_description IS NULL) OR
6217: (p_x_osp_order_line_rec.service_item_description IS NULL AND l_osp_order_line_rec.service_item_description IS NOT NULL)) THEN
6218: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SER_DESC_CHG');
6219: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6220: FND_MSG_PUB.ADD;
6221: END IF;
6222:
6223: /*
6224: --exchange_instance_id can't be changed from this API, and it could be null.
6225: IF((p_x_osp_order_line_rec.exchange_instance_id <> l_osp_order_line_rec.exchange_instance_id) OR
6226: (p_x_osp_order_line_rec.exchange_instance_id IS NOT NULL AND l_osp_order_line_rec.exchange_instance_id IS NULL) OR
6227: (p_x_osp_order_line_rec.exchange_instance_id IS NULL AND l_osp_order_line_rec.exchange_instance_id IS NOT NULL)) THEN
6228: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_EXCH_ID_CHG');
6229: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6230: FND_MSG_PUB.ADD;
6231: END IF;
6232: */
6225: IF((p_x_osp_order_line_rec.exchange_instance_id <> l_osp_order_line_rec.exchange_instance_id) OR
6226: (p_x_osp_order_line_rec.exchange_instance_id IS NOT NULL AND l_osp_order_line_rec.exchange_instance_id IS NULL) OR
6227: (p_x_osp_order_line_rec.exchange_instance_id IS NULL AND l_osp_order_line_rec.exchange_instance_id IS NOT NULL)) THEN
6228: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_EXCH_ID_CHG');
6229: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6230: FND_MSG_PUB.ADD;
6231: END IF;
6232: */
6233:
6233:
6234: --service_item_uom cannot change and cannot be null
6235: IF(p_x_osp_order_line_rec.service_item_uom_code IS NULL OR
6236: (p_x_osp_order_line_rec.service_item_uom_code <> l_osp_order_line_rec.service_item_uom_code) ) THEN
6237: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SER_UOM_CHG');
6238: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6239: FND_MSG_PUB.ADD;
6240: END IF;
6241:
6234: --service_item_uom cannot change and cannot be null
6235: IF(p_x_osp_order_line_rec.service_item_uom_code IS NULL OR
6236: (p_x_osp_order_line_rec.service_item_uom_code <> l_osp_order_line_rec.service_item_uom_code) ) THEN
6237: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SER_UOM_CHG');
6238: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6239: FND_MSG_PUB.ADD;
6240: END IF;
6241:
6242: --service_item_quantity cannot change and cannot be null
6241:
6242: --service_item_quantity cannot change and cannot be null
6243: IF(p_x_osp_order_line_rec.quantity IS NULL OR
6244: (p_x_osp_order_line_rec.quantity <> l_osp_order_line_rec.quantity) ) THEN
6245: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SER_QTY_CHG');
6246: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6247: FND_MSG_PUB.ADD;
6248: END IF;
6249:
6242: --service_item_quantity cannot change and cannot be null
6243: IF(p_x_osp_order_line_rec.quantity IS NULL OR
6244: (p_x_osp_order_line_rec.quantity <> l_osp_order_line_rec.quantity) ) THEN
6245: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SER_QTY_CHG');
6246: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6247: FND_MSG_PUB.ADD;
6248: END IF;
6249:
6250: --Neeed By Date cannot change and cannot be null
6249:
6250: --Neeed By Date cannot change and cannot be null
6251: IF(p_x_osp_order_line_rec.need_by_date IS NULL OR
6252: (trunc(p_x_osp_order_line_rec.need_by_date) <> trunc(l_osp_order_line_rec.need_by_date) )) THEN
6253: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_NBD_CHG');
6254: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6255: FND_MSG_PUB.ADD;
6256: END IF;
6257: END IF;
6250: --Neeed By Date cannot change and cannot be null
6251: IF(p_x_osp_order_line_rec.need_by_date IS NULL OR
6252: (trunc(p_x_osp_order_line_rec.need_by_date) <> trunc(l_osp_order_line_rec.need_by_date) )) THEN
6253: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_NBD_CHG');
6254: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6255: FND_MSG_PUB.ADD;
6256: END IF;
6257: END IF;
6258: -- jaramana End
6275: WHERE inventory_item_id = p_x_osp_order_line_rec.service_item_id
6276: AND organization_id = p_x_osp_order_line_rec.inventory_org_id;
6277: EXCEPTION
6278: WHEN NO_DATA_FOUND THEN
6279: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM');
6280: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_x_osp_order_line_rec.service_item_id);
6281: FND_MSG_PUB.ADD;
6282: END;
6283: l_owrite_svc_desc_prf := NVL(FND_PROFILE.VALUE('AHL_OSP_OWRITE_SVC_DESC'), 'N');
6276: AND organization_id = p_x_osp_order_line_rec.inventory_org_id;
6277: EXCEPTION
6278: WHEN NO_DATA_FOUND THEN
6279: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM');
6280: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_x_osp_order_line_rec.service_item_id);
6281: FND_MSG_PUB.ADD;
6282: END;
6283: l_owrite_svc_desc_prf := NVL(FND_PROFILE.VALUE('AHL_OSP_OWRITE_SVC_DESC'), 'N');
6284: l_item_prefix := FND_PROFILE.VALUE('AHL_OSP_POL_ITEM_PREFIX');
6289: IF(l_desc_update_flag <> 'Y' AND
6290: ((p_x_osp_order_line_rec.service_item_description <> l_item_description) OR
6291: (p_x_osp_order_line_rec.service_item_description IS NOT NULL AND l_item_description IS NULL) OR
6292: (p_x_osp_order_line_rec.service_item_description IS NULL AND l_item_description IS NOT NULL))) THEN
6293: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVCITMDSC_CHG');
6294: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6295: FND_MSG_PUB.ADD;
6296: END IF;
6297: */
6290: ((p_x_osp_order_line_rec.service_item_description <> l_item_description) OR
6291: (p_x_osp_order_line_rec.service_item_description IS NOT NULL AND l_item_description IS NULL) OR
6292: (p_x_osp_order_line_rec.service_item_description IS NULL AND l_item_description IS NOT NULL))) THEN
6293: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVCITMDSC_CHG');
6294: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6295: FND_MSG_PUB.ADD;
6296: END IF;
6297: */
6298: /*
6302: */
6303:
6304: IF(l_owrite_svc_desc_prf = 'Y') THEN --Overwrite Svc Description profile set to Yes
6305: IF(NVL(l_desc_update_flag, 'N') = 'N') THEN --Allow Description update set to No
6306: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVCITMDSC_CHG');
6307: FND_MESSAGE.Set_Token('SERVICE_ITEM_NUMBER', l_svc_item_number);
6308: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6309: FND_MSG_PUB.ADD;
6310: ELSE
6303:
6304: IF(l_owrite_svc_desc_prf = 'Y') THEN --Overwrite Svc Description profile set to Yes
6305: IF(NVL(l_desc_update_flag, 'N') = 'N') THEN --Allow Description update set to No
6306: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVCITMDSC_CHG');
6307: FND_MESSAGE.Set_Token('SERVICE_ITEM_NUMBER', l_svc_item_number);
6308: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6309: FND_MSG_PUB.ADD;
6310: ELSE
6311: BEGIN
6304: IF(l_owrite_svc_desc_prf = 'Y') THEN --Overwrite Svc Description profile set to Yes
6305: IF(NVL(l_desc_update_flag, 'N') = 'N') THEN --Allow Description update set to No
6306: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVCITMDSC_CHG');
6307: FND_MESSAGE.Set_Token('SERVICE_ITEM_NUMBER', l_svc_item_number);
6308: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6309: FND_MSG_PUB.ADD;
6310: ELSE
6311: BEGIN
6312: SELECT concatenated_segments INTO l_inv_item_number
6314: WHERE inventory_item_id = p_x_osp_order_line_rec.inventory_item_id
6315: AND organization_id = p_x_osp_order_line_rec.inventory_org_id;
6316: EXCEPTION
6317: WHEN NO_DATA_FOUND THEN
6318: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PHY_ITEM_INVALID');
6319: FND_MSG_PUB.ADD;
6320: END;
6321: -- SALOGAN added an if condition to avoid adding trailing spaces for Non Serial Item
6322: -- for bug #9826606 Starts
6347: --Enforcing that either service_item_id and description should be not null
6348: --This is only enforced during update. Maybe it is better if delaying this validation
6349: --until submitting the OSP order?
6350: 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
6351: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_SVCITMDSC_BOTH_NLL');
6352: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6353: FND_MSG_PUB.ADD;
6354: END IF;
6355: --validate status_code change
6348: --This is only enforced during update. Maybe it is better if delaying this validation
6349: --until submitting the OSP order?
6350: 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
6351: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_SVCITMDSC_BOTH_NLL');
6352: FND_MESSAGE.Set_Token('LINE_NUMBER', l_osp_order_line_rec.osp_line_number);
6353: FND_MSG_PUB.ADD;
6354: END IF;
6355: --validate status_code change
6356: --it looks like only changing to 'CLOSED' occurs in this API, other statuses will be
6360: -- Modified by jaramana on August 11, 2006 to not do this Date check if the line is PO Cancelled or PO Deleted (Bug 5478764)
6361: -- Added by jaramana on January 10, 2008 for the Requisition ER 6034236 (Added the Requisition related statuses)
6362: IF(TRUNC(p_x_osp_order_line_rec.need_by_date) < TRUNC(SYSDATE) AND
6363: (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
6364: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_NEED_DT');
6365: FND_MESSAGE.Set_Token('NEED_BY_DATE', p_x_osp_order_line_rec.need_by_date);
6366: FND_MSG_PUB.ADD;
6367: END IF;
6368: -- jaramana End
6361: -- Added by jaramana on January 10, 2008 for the Requisition ER 6034236 (Added the Requisition related statuses)
6362: IF(TRUNC(p_x_osp_order_line_rec.need_by_date) < TRUNC(SYSDATE) AND
6363: (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
6364: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_NEED_DT');
6365: FND_MESSAGE.Set_Token('NEED_BY_DATE', p_x_osp_order_line_rec.need_by_date);
6366: FND_MSG_PUB.ADD;
6367: END IF;
6368: -- jaramana End
6369: --validate ship_by_date (is this necessary?)
6368: -- jaramana End
6369: --validate ship_by_date (is this necessary?)
6370: /* Commented out on 05/31/2005 by Jerry
6371: IF(TRUNC(p_x_osp_order_line_rec.ship_by_date) < TRUNC(SYSDATE))THEN
6372: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SHIP_DT');
6373: FND_MESSAGE.Set_Token('SHIP_BY_DATE', p_x_osp_order_line_rec.ship_by_date);
6374: FND_MSG_PUB.ADD;
6375: END IF;
6376: */
6369: --validate ship_by_date (is this necessary?)
6370: /* Commented out on 05/31/2005 by Jerry
6371: IF(TRUNC(p_x_osp_order_line_rec.ship_by_date) < TRUNC(SYSDATE))THEN
6372: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SHIP_DT');
6373: FND_MESSAGE.Set_Token('SHIP_BY_DATE', p_x_osp_order_line_rec.ship_by_date);
6374: FND_MSG_PUB.ADD;
6375: END IF;
6376: */
6377: --validate service_item_id (simplfied the original one)
6397: */
6398: -- jaramana End
6399: 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
6400: IF(p_x_osp_order_line_rec.quantity <= 0) THEN
6401: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_VAL');
6402: FND_MESSAGE.Set_Token('QUANTITY', p_x_osp_order_line_rec.quantity);
6403: FND_MSG_PUB.ADD;
6404: END IF;
6405: -- Added by jaramana on January 10, 2008 for the Requisition ER 6034236
6398: -- jaramana End
6399: 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
6400: IF(p_x_osp_order_line_rec.quantity <= 0) THEN
6401: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_VAL');
6402: FND_MESSAGE.Set_Token('QUANTITY', p_x_osp_order_line_rec.quantity);
6403: FND_MSG_PUB.ADD;
6404: END IF;
6405: -- Added by jaramana on January 10, 2008 for the Requisition ER 6034236
6406: --Included the equal to zero clause above. Since we are modifying the OSP Line Views and are refraining from
6407: --synching up the quantity from PO Lines, the equal to clause can be imposed on all the OSP line statuses
6408: /*
6409: IF (p_x_osp_order_line_rec.quantity = 0 AND
6410: l_header_status_code NOT IN (G_OSP_PO_CREATED_STATUS, G_OSP_CLOSED_STATUS)) THEN
6411: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_VAL');
6412: FND_MESSAGE.Set_Token('QUANTITY', p_x_osp_order_line_rec.quantity);
6413: FND_MSG_PUB.ADD;
6414: END IF;
6415: */
6408: /*
6409: IF (p_x_osp_order_line_rec.quantity = 0 AND
6410: l_header_status_code NOT IN (G_OSP_PO_CREATED_STATUS, G_OSP_CLOSED_STATUS)) THEN
6411: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_VAL');
6412: FND_MESSAGE.Set_Token('QUANTITY', p_x_osp_order_line_rec.quantity);
6413: FND_MSG_PUB.ADD;
6414: END IF;
6415: */
6416: -- jaramana End
6415: */
6416: -- jaramana End
6417: --quantity cannot be null when UOM is not null
6418: 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
6419: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_QUANT_NLL');
6420: FND_MSG_PUB.ADD;
6421: END IF;
6422:
6423: -- validate service/physical item combination with vendor
6436: OPEN check_phy_item_unique(p_x_osp_order_line_rec.osp_order_id,
6437: p_x_osp_order_line_rec.inventory_item_id);
6438: FETCH check_phy_item_unique INTO g_dummy_char;
6439: IF check_phy_item_unique%FOUND THEN
6440: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_PHY_ITEM_UNIUE');
6441: FND_MSG_PUB.ADD;
6442: END IF;
6443: CLOSE check_phy_item_unique;
6444: ELSE
6451: p_x_osp_order_line_rec.lot_number,
6452: p_x_osp_order_line_rec.serial_number);
6453: FETCH check_phy_ser_item_unique INTO g_dummy_char;
6454: IF check_phy_ser_item_unique%FOUND THEN
6455: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_PHY_SER_ITEM_UNIUE');
6456: FND_MSG_PUB.ADD;
6457: END IF;
6458: CLOSE check_phy_ser_item_unique;
6459: END IF;
6762: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Begin Procedure');
6763: END IF;
6764: IF (p_vendor_id IS NULL) THEN
6765: IF (p_vendor_site_id IS NOT NULL OR p_vendor_contact_id IS NOT NULL) THEN
6766: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
6767: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
6768: FND_MSG_PUB.ADD;
6769: END IF;
6770: ELSE
6763: END IF;
6764: IF (p_vendor_id IS NULL) THEN
6765: IF (p_vendor_site_id IS NOT NULL OR p_vendor_contact_id IS NOT NULL) THEN
6766: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
6767: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
6768: FND_MSG_PUB.ADD;
6769: END IF;
6770: ELSE
6771: OPEN val_vendor_id_csr;
6770: ELSE
6771: OPEN val_vendor_id_csr;
6772: FETCH val_vendor_id_csr INTO l_exist;
6773: IF(val_vendor_id_csr%NOTFOUND) THEN
6774: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
6775: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
6776: FND_MSG_PUB.ADD;
6777: END IF;
6778: CLOSE val_vendor_id_csr;
6771: OPEN val_vendor_id_csr;
6772: FETCH val_vendor_id_csr INTO l_exist;
6773: IF(val_vendor_id_csr%NOTFOUND) THEN
6774: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
6775: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
6776: FND_MSG_PUB.ADD;
6777: END IF;
6778: CLOSE val_vendor_id_csr;
6779: OPEN get_vendor_cert;
6778: CLOSE val_vendor_id_csr;
6779: OPEN get_vendor_cert;
6780: FETCH get_vendor_cert INTO l_exist;
6781: IF(get_vendor_cert%NOTFOUND) THEN
6782: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
6783: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
6784: FND_MSG_PUB.ADD;
6785: END IF;
6786: CLOSE get_vendor_cert;
6779: OPEN get_vendor_cert;
6780: FETCH get_vendor_cert INTO l_exist;
6781: IF(get_vendor_cert%NOTFOUND) THEN
6782: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
6783: FND_MESSAGE.Set_Token('VENDOR_ID', p_vendor_id);
6784: FND_MSG_PUB.ADD;
6785: END IF;
6786: CLOSE get_vendor_cert;
6787: IF (p_vendor_site_id IS NULL AND p_vendor_contact_id IS NOT NULL) THEN
6784: FND_MSG_PUB.ADD;
6785: END IF;
6786: CLOSE get_vendor_cert;
6787: IF (p_vendor_site_id IS NULL AND p_vendor_contact_id IS NOT NULL) THEN
6788: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
6789: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
6790: FND_MSG_PUB.ADD;
6791: ELSIF (p_vendor_site_id IS NOT NULL) THEN
6792: OPEN val_vendor_site_id_csr;
6785: END IF;
6786: CLOSE get_vendor_cert;
6787: IF (p_vendor_site_id IS NULL AND p_vendor_contact_id IS NOT NULL) THEN
6788: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
6789: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
6790: FND_MSG_PUB.ADD;
6791: ELSIF (p_vendor_site_id IS NOT NULL) THEN
6792: OPEN val_vendor_site_id_csr;
6793: FETCH val_vendor_site_id_csr INTO l_exist;
6791: ELSIF (p_vendor_site_id IS NOT NULL) THEN
6792: OPEN val_vendor_site_id_csr;
6793: FETCH val_vendor_site_id_csr INTO l_exist;
6794: IF(val_vendor_site_id_csr%NOTFOUND) THEN
6795: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
6796: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
6797: FND_MSG_PUB.ADD;
6798: END IF;
6799: CLOSE val_vendor_site_id_csr;
6792: OPEN val_vendor_site_id_csr;
6793: FETCH val_vendor_site_id_csr INTO l_exist;
6794: IF(val_vendor_site_id_csr%NOTFOUND) THEN
6795: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
6796: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
6797: FND_MSG_PUB.ADD;
6798: END IF;
6799: CLOSE val_vendor_site_id_csr;
6800: OPEN get_site_cert;
6799: CLOSE val_vendor_site_id_csr;
6800: OPEN get_site_cert;
6801: FETCH get_site_cert INTO l_exist;
6802: IF(get_site_cert%NOTFOUND) THEN
6803: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
6804: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
6805: FND_MSG_PUB.ADD;
6806: END IF;
6807: CLOSE get_site_cert;
6800: OPEN get_site_cert;
6801: FETCH get_site_cert INTO l_exist;
6802: IF(get_site_cert%NOTFOUND) THEN
6803: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENSITE_ID_INV');
6804: FND_MESSAGE.Set_Token('VENDOR_SITE_ID', p_vendor_site_id);
6805: FND_MSG_PUB.ADD;
6806: END IF;
6807: CLOSE get_site_cert;
6808: IF (p_vendor_contact_id IS NOT NULL) THEN
6808: IF (p_vendor_contact_id IS NOT NULL) THEN
6809: OPEN val_vendor_contact_id_csr;
6810: FETCH val_vendor_contact_id_csr INTO l_exist;
6811: IF(val_vendor_contact_id_csr%NOTFOUND) THEN
6812: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VEN_CONTACT_ID_INV');
6813: FND_MESSAGE.Set_Token('VENDOR_CONTACT_ID', p_vendor_contact_id);
6814: FND_MSG_PUB.ADD;
6815: END IF;
6816: CLOSE val_vendor_contact_id_csr;
6809: OPEN val_vendor_contact_id_csr;
6810: FETCH val_vendor_contact_id_csr INTO l_exist;
6811: IF(val_vendor_contact_id_csr%NOTFOUND) THEN
6812: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VEN_CONTACT_ID_INV');
6813: FND_MESSAGE.Set_Token('VENDOR_CONTACT_ID', p_vendor_contact_id);
6814: FND_MSG_PUB.ADD;
6815: END IF;
6816: CLOSE val_vendor_contact_id_csr;
6817: OPEN get_contact_cert;
6816: CLOSE val_vendor_contact_id_csr;
6817: OPEN get_contact_cert;
6818: FETCH get_contact_cert INTO l_exist;
6819: IF(get_contact_cert%NOTFOUND) THEN
6820: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VEN_CONTACT_ID_INV');
6821: FND_MESSAGE.Set_Token('VENDOR_CONTACT_ID', p_vendor_contact_id);
6822: FND_MSG_PUB.ADD;
6823: END IF;
6824: CLOSE get_contact_cert;
6817: OPEN get_contact_cert;
6818: FETCH get_contact_cert INTO l_exist;
6819: IF(get_contact_cert%NOTFOUND) THEN
6820: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VEN_CONTACT_ID_INV');
6821: FND_MESSAGE.Set_Token('VENDOR_CONTACT_ID', p_vendor_contact_id);
6822: FND_MSG_PUB.ADD;
6823: END IF;
6824: CLOSE get_contact_cert;
6825: END IF;
6979: IF(p_x_osp_order_line_rec.operation_flag = G_OP_UPDATE) THEN
6980: OPEN osp_order_lines_csr;
6981: FETCH osp_order_lines_csr INTO l_osp_order_line_rec;
6982: IF (osp_order_lines_csr%NOTFOUND) THEN
6983: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_INVOP_OSP_LN_NFOUND');
6984: FND_MSG_PUB.ADD;
6985: ELSE
6986: IF (p_x_osp_order_line_rec.osp_order_id IS NULL) THEN
6987: p_x_osp_order_line_rec.osp_order_id := l_osp_order_line_rec.osp_order_id;
7441: IF (p_x_osp_order_line_rec.job_number IS NOT NULL AND p_x_osp_order_line_rec.job_number <> FND_API.G_MISS_CHAR) THEN
7442: OPEN workorder_id_csr(p_x_osp_order_line_rec.job_number);
7443: FETCH workorder_id_csr INTO l_workorder_id;
7444: IF(workorder_id_csr%NOTFOUND) THEN
7445: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_JOB_NUM_INV');
7446: FND_MESSAGE.Set_Token('JOB_NUMBER', p_x_osp_order_line_rec.job_number);
7447: FND_MSG_PUB.ADD;
7448: ELSE
7449: p_x_osp_order_line_rec.workorder_id := l_workorder_id;
7442: OPEN workorder_id_csr(p_x_osp_order_line_rec.job_number);
7443: FETCH workorder_id_csr INTO l_workorder_id;
7444: IF(workorder_id_csr%NOTFOUND) THEN
7445: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_JOB_NUM_INV');
7446: FND_MESSAGE.Set_Token('JOB_NUMBER', p_x_osp_order_line_rec.job_number);
7447: FND_MSG_PUB.ADD;
7448: ELSE
7449: p_x_osp_order_line_rec.workorder_id := l_workorder_id;
7450: END IF;
7454: IF (p_x_osp_order_line_rec.service_item_number IS NOT NULL AND p_x_osp_order_line_rec.service_item_number <> FND_API.G_MISS_CHAR) THEN
7455: OPEN service_item_id_csr(p_x_osp_order_line_rec.service_item_number, p_x_osp_order_line_rec.inventory_org_id);
7456: FETCH service_item_id_csr INTO l_service_item_id;
7457: IF(service_item_id_csr%NOTFOUND) THEN
7458: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_SVC_ITEM_INV');
7459: FND_MESSAGE.Set_Token('SERVICE_ITEM', p_x_osp_order_line_rec.service_item_number);
7460: FND_MSG_PUB.ADD;
7461: ELSE
7462: p_x_osp_order_line_rec.service_item_id := l_service_item_id;
7455: OPEN service_item_id_csr(p_x_osp_order_line_rec.service_item_number, p_x_osp_order_line_rec.inventory_org_id);
7456: FETCH service_item_id_csr INTO l_service_item_id;
7457: IF(service_item_id_csr%NOTFOUND) THEN
7458: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_SVC_ITEM_INV');
7459: FND_MESSAGE.Set_Token('SERVICE_ITEM', p_x_osp_order_line_rec.service_item_number);
7460: FND_MSG_PUB.ADD;
7461: ELSE
7462: p_x_osp_order_line_rec.service_item_id := l_service_item_id;
7463: END IF;
7467: IF (p_x_osp_order_line_rec.po_line_type IS NOT NULL AND p_x_osp_order_line_rec.po_line_type <> FND_API.G_MISS_CHAR) THEN
7468: OPEN po_line_type_id_csr(p_x_osp_order_line_rec.po_line_type);
7469: FETCH po_line_type_id_csr INTO l_po_line_type_id;
7470: IF(po_line_type_id_csr%NOTFOUND) THEN
7471: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_PO_LN_TYPE_INV');
7472: FND_MESSAGE.Set_Token('PO_LINE_TYPE', p_x_osp_order_line_rec.po_line_type);
7473: FND_MSG_PUB.ADD;
7474: ELSE
7475: p_x_osp_order_line_rec.po_line_type_id := l_po_line_type_id;
7468: OPEN po_line_type_id_csr(p_x_osp_order_line_rec.po_line_type);
7469: FETCH po_line_type_id_csr INTO l_po_line_type_id;
7470: IF(po_line_type_id_csr%NOTFOUND) THEN
7471: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_PO_LN_TYPE_INV');
7472: FND_MESSAGE.Set_Token('PO_LINE_TYPE', p_x_osp_order_line_rec.po_line_type);
7473: FND_MSG_PUB.ADD;
7474: ELSE
7475: p_x_osp_order_line_rec.po_line_type_id := l_po_line_type_id;
7476: END IF;
7480: IF (p_x_osp_order_line_rec.exchange_instance_number IS NOT NULL AND p_x_osp_order_line_rec.exchange_instance_number <> FND_API.G_MISS_CHAR) THEN
7481: OPEN exchange_instance_id_csr(p_x_osp_order_line_rec.exchange_instance_number);
7482: FETCH exchange_instance_id_csr INTO l_exchange_instance_id;
7483: IF(exchange_instance_id_csr%NOTFOUND) THEN
7484: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_X_INST_NUM_INV');
7485: FND_MESSAGE.Set_Token('INTANCE_NUMBER', p_x_osp_order_line_rec.exchange_instance_number);
7486: FND_MSG_PUB.ADD;
7487: ELSE
7488: p_x_osp_order_line_rec.exchange_instance_id := l_exchange_instance_id;
7481: OPEN exchange_instance_id_csr(p_x_osp_order_line_rec.exchange_instance_number);
7482: FETCH exchange_instance_id_csr INTO l_exchange_instance_id;
7483: IF(exchange_instance_id_csr%NOTFOUND) THEN
7484: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_X_INST_NUM_INV');
7485: FND_MESSAGE.Set_Token('INTANCE_NUMBER', p_x_osp_order_line_rec.exchange_instance_number);
7486: FND_MSG_PUB.ADD;
7487: ELSE
7488: p_x_osp_order_line_rec.exchange_instance_id := l_exchange_instance_id;
7489: END IF;
7493: IF (p_x_osp_order_line_rec.item_number IS NOT NULL AND p_x_osp_order_line_rec.inventory_item_id IS NULL) THEN
7494: OPEN physical_item_id_csr(p_x_osp_order_line_rec.item_number, p_x_osp_order_line_rec.inventory_org_id);
7495: FETCH physical_item_id_csr INTO l_physical_item_id;
7496: IF(physical_item_id_csr%NOTFOUND) THEN
7497: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_PHY_ITEM_INV');
7498: FND_MESSAGE.Set_Token('PHSICAL_ITEM', p_x_osp_order_line_rec.item_number);
7499: FND_MSG_PUB.ADD;
7500: ELSE
7501: p_x_osp_order_line_rec.inventory_item_id := l_physical_item_id;
7494: OPEN physical_item_id_csr(p_x_osp_order_line_rec.item_number, p_x_osp_order_line_rec.inventory_org_id);
7495: FETCH physical_item_id_csr INTO l_physical_item_id;
7496: IF(physical_item_id_csr%NOTFOUND) THEN
7497: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_PHY_ITEM_INV');
7498: FND_MESSAGE.Set_Token('PHSICAL_ITEM', p_x_osp_order_line_rec.item_number);
7499: FND_MSG_PUB.ADD;
7500: ELSE
7501: p_x_osp_order_line_rec.inventory_item_id := l_physical_item_id;
7502: END IF;
7508: IF (p_x_osp_order_line_rec.osp_order_id IS NOT NULL AND p_x_osp_order_line_rec.osp_line_number IS NOT NULL) THEN
7509: OPEN osp_order_line_on_csr(p_x_osp_order_line_rec.osp_order_id , p_x_osp_order_line_rec.osp_line_number);
7510: FETCH osp_order_line_on_csr INTO l_osp_order_line_id;
7511: IF(osp_order_line_on_csr%NOTFOUND) THEN
7512: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_LN_INV');
7513: FND_MESSAGE.Set_Token('OSP_LINE_NUMBER', p_x_osp_order_line_rec.osp_line_number);
7514: FND_MSG_PUB.ADD;
7515: ELSE
7516: p_x_osp_order_line_rec.osp_order_line_id := l_osp_order_line_id;
7509: OPEN osp_order_line_on_csr(p_x_osp_order_line_rec.osp_order_id , p_x_osp_order_line_rec.osp_line_number);
7510: FETCH osp_order_line_on_csr INTO l_osp_order_line_id;
7511: IF(osp_order_line_on_csr%NOTFOUND) THEN
7512: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_ORD_ID_LN_INV');
7513: FND_MESSAGE.Set_Token('OSP_LINE_NUMBER', p_x_osp_order_line_rec.osp_line_number);
7514: FND_MSG_PUB.ADD;
7515: ELSE
7516: p_x_osp_order_line_rec.osp_order_line_id := l_osp_order_line_id;
7517: END IF;
7522: ELSIF (p_x_osp_order_line_rec.po_line_id IS NOT NULL AND p_x_osp_order_line_rec.po_line_id <> FND_API.G_MISS_NUM) THEN
7523: OPEN osp_order_line_po_csr(p_x_osp_order_line_rec.po_line_id );
7524: FETCH osp_order_line_po_csr INTO l_osp_order_line_id;
7525: IF(osp_order_line_po_csr%NOTFOUND) THEN
7526: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PO_LINE_INV');
7527: FND_MESSAGE.Set_Token('PO_LINE_ID', p_x_osp_order_line_rec.po_line_id);
7528: FND_MSG_PUB.ADD;
7529: ELSE
7530: p_x_osp_order_line_rec.osp_order_line_id := l_osp_order_line_id;
7523: OPEN osp_order_line_po_csr(p_x_osp_order_line_rec.po_line_id );
7524: FETCH osp_order_line_po_csr INTO l_osp_order_line_id;
7525: IF(osp_order_line_po_csr%NOTFOUND) THEN
7526: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_PO_LINE_INV');
7527: FND_MESSAGE.Set_Token('PO_LINE_ID', p_x_osp_order_line_rec.po_line_id);
7528: FND_MSG_PUB.ADD;
7529: ELSE
7530: p_x_osp_order_line_rec.osp_order_line_id := l_osp_order_line_id;
7531: END IF;
7532: CLOSE osp_order_line_po_csr;
7533: */
7534: -- jaramana End
7535: ELSE
7536: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV');
7537: FND_MESSAGE.Set_Token('OSP_LINE_NUMBER', p_x_osp_order_line_rec.osp_line_number); FND_MSG_PUB.ADD;
7538: END IF;
7539: END IF;
7540: END IF;
7533: */
7534: -- jaramana End
7535: ELSE
7536: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV');
7537: FND_MESSAGE.Set_Token('OSP_LINE_NUMBER', p_x_osp_order_line_rec.osp_line_number); FND_MSG_PUB.ADD;
7538: END IF;
7539: END IF;
7540: END IF;
7541: IF FND_MSG_PUB.count_msg > 0 THEN
7574: IF(p_service_item_id IS NOT NULL AND p_service_item_id <> FND_API.G_MISS_NUM) THEN
7575: OPEN val_service_item_id_csr;
7576: FETCH val_service_item_id_csr INTO g_dummy_char;
7577: IF(val_service_item_id_csr%NOTFOUND) THEN
7578: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM');
7579: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
7580: FND_MSG_PUB.ADD;
7581: END IF;
7582: CLOSE val_service_item_id_csr;
7575: OPEN val_service_item_id_csr;
7576: FETCH val_service_item_id_csr INTO g_dummy_char;
7577: IF(val_service_item_id_csr%NOTFOUND) THEN
7578: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_LN_INV_SVC_ITEM');
7579: FND_MESSAGE.Set_Token('SERVICE_ITEM_ID', p_service_item_id);
7580: FND_MSG_PUB.ADD;
7581: END IF;
7582: CLOSE val_service_item_id_csr;
7583: END IF;
7618: END IF;
7619: --IF (p_osp_order_line_rec.workorder_id IS NULL) THEN --commented out on 06/10/2006
7620: /*(From Jay said on 06/07/2005) service_item_id is not mandatory even for update
7621: IF p_osp_order_line_rec.service_item_id IS NULL THEN
7622: FND_MESSAGE.set_name('AHL', 'AHL_OSP_SERVICE_ITEM_NULL');
7623: FND_MSG_PUB.add;
7624: END IF;
7625: */
7626: --(Note: 05/31/2005 Jerry)It is not good to use ahl_osp_orders_v here but
7647: WHERE osp.osp_order_id = p_osp_order_line_rec.osp_order_id;
7648: --mpothuku End
7649: EXCEPTION
7650: WHEN NO_DATA_FOUND THEN
7651: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORDER_INVALID');
7652: FND_MSG_PUB.add;
7653: END;
7654:
7655: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
7674: AND trunc(active_start_date) <= trunc(SYSDATE)
7675: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
7676: EXCEPTION
7677: WHEN NO_DATA_FOUND THEN
7678: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
7679: FND_MESSAGE.Set_Token('VENDOR_ID', l_vendor_id);
7680: FND_MSG_PUB.ADD;
7681: END;
7682:
7675: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
7676: EXCEPTION
7677: WHEN NO_DATA_FOUND THEN
7678: FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_OSP_VENDOR_ID_INV');
7679: FND_MESSAGE.Set_Token('VENDOR_ID', l_vendor_id);
7680: FND_MSG_PUB.ADD;
7681: END;
7682:
7683: OPEN verify_vendor(l_vendor_id, l_vendor_site_id);
7682:
7683: OPEN verify_vendor(l_vendor_id, l_vendor_site_id);
7684: FETCH verify_vendor INTO g_dummy_num;
7685: IF verify_vendor%NOTFOUND THEN
7686: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ITEM_VENDOR_MISMATCH');
7687: FND_MESSAGE.set_token('ITEM_ID', p_osp_order_line_rec.inventory_item_id);
7688: FND_MSG_PUB.add;
7689: RAISE FND_API.G_EXC_ERROR;
7690: --FND_MSG_PUB.count_and_get(
7683: OPEN verify_vendor(l_vendor_id, l_vendor_site_id);
7684: FETCH verify_vendor INTO g_dummy_num;
7685: IF verify_vendor%NOTFOUND THEN
7686: FND_MESSAGE.set_name('AHL', 'AHL_OSP_ITEM_VENDOR_MISMATCH');
7687: FND_MESSAGE.set_token('ITEM_ID', p_osp_order_line_rec.inventory_item_id);
7688: FND_MSG_PUB.add;
7689: RAISE FND_API.G_EXC_ERROR;
7690: --FND_MSG_PUB.count_and_get(
7691: --p_encoded => FND_API.G_FALSE,
8030: --OPEN c_get_contract_id(p_x_osp_order_line_rec.contract_number);
8031: --FETCH c_get_contract_id into l_contract_id;
8032: --CLOSE c_get_contract_id;
8033: IF(l_contract_id is null) THEN
8034: FND_MESSAGE.set_name('AHL','AHL_COM_INVALID_WARRANTY_CONTRACT');
8035: FND_MESSAGE.set_token('CONT_NUM', p_x_osp_order_line_rec.contract_number);
8036: FND_MSG_PUB.add;
8037: END IF;
8038: l_entl_status := 'APPROVAL_PENDING';
8031: --FETCH c_get_contract_id into l_contract_id;
8032: --CLOSE c_get_contract_id;
8033: IF(l_contract_id is null) THEN
8034: FND_MESSAGE.set_name('AHL','AHL_COM_INVALID_WARRANTY_CONTRACT');
8035: FND_MESSAGE.set_token('CONT_NUM', p_x_osp_order_line_rec.contract_number);
8036: FND_MSG_PUB.add;
8037: END IF;
8038: l_entl_status := 'APPROVAL_PENDING';
8039: ELSE -- If contact number is not associated/removed