[Home] [Help]
55: p_parameters => l_parameter_list);
56:
57: EXCEPTION
58: WHEN OTHERS THEN
59: RAISE Fnd_Api.g_exc_error;
60: IF G_DEBUG THEN
61: ozf_utility_pvt.debug_message('Exception in raising business event');
62: END IF;
63:
152: p_parameters => l_parameter_list);
153:
154: EXCEPTION
155: WHEN OTHERS THEN
156: RAISE Fnd_Api.g_exc_error;
157: IF G_DEBUG THEN
158: ozf_utility_pvt.debug_message('Exception in raising XML Gateway business event');
159: END IF;
160: END;
171: RETURN VARCHAR2
172: IS
173: BEGIN
174: IF p_value <= 0 THEN
175: RETURN FND_API.g_false;
176: ELSE
177: RETURN FND_API.g_true;
178: END IF;
179: END check_zero;
173: BEGIN
174: IF p_value <= 0 THEN
175: RETURN FND_API.g_false;
176: ELSE
177: RETURN FND_API.g_true;
178: END IF;
179: END check_zero;
180: -----------------------------------------------------------------------
181: -- FUNCTION
316: CLOSE c_external_transition;
317: END IF;
318:
319: IF l_owner_count = 0 AND l_pm_count = 0 AND l_owner_pm_count= 0 THEN
320: RETURN FND_API.g_false;
321: ELSE
322: RETURN FND_API.g_true;
323: END IF;
324:
318:
319: IF l_owner_count = 0 AND l_pm_count = 0 AND l_owner_pm_count= 0 THEN
320: RETURN FND_API.g_false;
321: ELSE
322: RETURN FND_API.g_true;
323: END IF;
324:
325: ELSIF p_internal_flag ='Y' THEN
326:
356: CLOSE c_internal_transition;
357: END IF;
358:
359: IF l_owner_count = 0 AND l_pm_count = 0 AND l_owner_pm_count= 0 THEN
360: RETURN FND_API.g_false;
361: ELSE
362: RETURN FND_API.g_true;
363: END IF;
364:
358:
359: IF l_owner_count = 0 AND l_pm_count = 0 AND l_owner_pm_count= 0 THEN
360: RETURN FND_API.g_false;
361: ELSE
362: RETURN FND_API.g_true;
363: END IF;
364:
365: END IF;
366:
510: WHERE order_number = p_internal_order_number
511: AND org_id = p_org_id;
512:
513: BEGIN
514: x_return_status := FND_API.G_RET_STS_SUCCESS;
515:
516: --// User ID validation
517: IF p_SDR_hdr_rec.user_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.user_id IS NOT NULL THEN
518: OPEN c_user(p_SDR_hdr_rec.user_id);
513: BEGIN
514: x_return_status := FND_API.G_RET_STS_SUCCESS;
515:
516: --// User ID validation
517: IF p_SDR_hdr_rec.user_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.user_id IS NOT NULL THEN
518: OPEN c_user(p_SDR_hdr_rec.user_id);
519: FETCH c_user INTO l_user_id;
520: CLOSE c_user;
521:
524: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_USER_ID');
525: --//User Id is invalid, Please re-enter
526: FND_MSG_PUB.add;
527: END IF;
528: x_return_status := fnd_api.g_ret_sts_error;
529: RETURN;
530: ELSE --// Check if User is a valid resource or not
531: OPEN c_resource_id(p_SDR_hdr_rec.user_id);
532: FETCH c_resource_id INTO l_resource_id;
536: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
537: FND_MESSAGE.set_name('OZF', 'OZF_SD_USER_IS_NOT_RESOURCE');
538: FND_MSG_PUB.add;
539: END IF;
540: x_return_status := fnd_api.g_ret_sts_error;
541: RETURN;
542: END IF;
543: IF g_debug THEN
544: OZF_UTILITY_PVT.debug_message('l_resource_id of the user :'||l_resource_id);
550: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_USER_ID');
551: --//User Id is Mandatory
552: FND_MSG_PUB.add;
553: END IF;
554: x_return_status := fnd_api.g_ret_sts_error;
555: RETURN;
556: END IF;
557:
558: --// Organization id Validation
555: RETURN;
556: END IF;
557:
558: --// Organization id Validation
559: IF p_SDR_hdr_rec.org_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.org_id IS NOT NULL THEN
560: OPEN c_org_id(p_SDR_hdr_rec.org_id);
561: FETCH c_org_id INTO l_org_id;
562: CLOSE c_org_id;
563:
566: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ORG_ID');
567: --//Organization id entered is invalid
568: FND_MSG_PUB.add;
569: END IF;
570: x_return_status := fnd_api.g_ret_sts_error;
571: RETURN;
572: END IF;
573: --//Set Org ID to Global Variable
574: -- G_ORG_ID := p_SDR_hdr_rec.org_id;
577: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_ORG_ID');
578: --//Organization id is Mandatory
579: FND_MSG_PUB.add;
580: END IF;
581: x_return_status := fnd_api.g_ret_sts_error;
582: RETURN;
583: END IF;
584:
585: --// Requestor ID Validation (owner)
582: RETURN;
583: END IF;
584:
585: --// Requestor ID Validation (owner)
586: IF p_SDR_hdr_rec.requestor_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.requestor_id IS NOT NULL THEN
587: IF G_DEBUG THEN
588: OZF_UTILITY_PVT.debug_message('Requestor NOT NULL');
589: END IF;
590: OPEN c_requestor_id(p_SDR_hdr_rec.requestor_id);
599: OZF_UTILITY_PVT.debug_message('Requestor id entered is invalid');
600: END IF;
601: FND_MSG_PUB.add;
602: END IF;
603: x_return_status := fnd_api.g_ret_sts_error;
604: RETURN;
605: END IF;
606: ELSE
607: p_SDR_hdr_rec.requestor_id := l_resource_id;
614: --//Supplier Validations
615: p_SDR_hdr_rec.cust_account_id := NULL;
616:
617: --//Supplier ID
618: IF p_SDR_hdr_rec.supplier_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.supplier_id IS NOT NULL THEN
619: OPEN c_supp_id(p_SDR_hdr_rec.supplier_id);
620: FETCH c_supp_id INTO l_supplier_id;
621: CLOSE c_supp_id;
622:
625: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPPLIER_ID');
626: --//Supplier id entered is invalid
627: FND_MSG_PUB.add;
628: END IF;
629: x_return_status := fnd_api.g_ret_sts_error;
630: RETURN;
631: END IF;
632:
633: ELSE
635: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_SUPPLIER_ID');
636: --//Supplier id is Mandatory
637: FND_MSG_PUB.add;
638: END IF;
639: x_return_status := fnd_api.g_ret_sts_error;
640: RETURN;
641: END IF;
642:
643: --//Supplier site ID
640: RETURN;
641: END IF;
642:
643: --//Supplier site ID
644: IF p_SDR_hdr_rec.supplier_site_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.supplier_site_id IS NOT NULL THEN
645: OPEN c_supplier_site_id(p_SDR_hdr_rec.supplier_site_id
646: ,p_SDR_hdr_rec.supplier_id
647: ,p_SDR_hdr_rec.org_id);
648:
654: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPPLIER_SITE_ID');
655: --//Supplier site id entered is invalid
656: FND_MSG_PUB.add;
657: END IF;
658: x_return_status := fnd_api.g_ret_sts_error;
659: RETURN;
660: END IF;
661:
662: ELSE
664: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_SUPPLIER_SITE_ID');
665: --//Supplier site id is Mandatory
666: FND_MSG_PUB.add;
667: END IF;
668: x_return_status := fnd_api.g_ret_sts_error;
669: RETURN;
670: END IF;
671:
672: --//Supplier Contact ID
670: END IF;
671:
672: --//Supplier Contact ID
673: --// Bugfix : 7822442
674: IF p_SDR_hdr_rec.supplier_contact_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.supplier_contact_id IS NOT NULL THEN
675:
676: OPEN c_sup_contacts(p_SDR_hdr_rec.supplier_site_id
677: ,p_SDR_hdr_rec.supplier_contact_id);
678:
681:
682: IF l_supplier_contact_id IS NOT NULL THEN
683: p_SDR_hdr_rec.supplier_contact_name := l_sup_contact_full_name;
684: ELSE
685: IF p_SDR_hdr_rec.supplier_contact_name = FND_API.g_miss_char OR p_SDR_hdr_rec.supplier_contact_name IS NULL THEN
686: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
687: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPP_CONTACT_ID');
688: --//Supplier contact id entered is invalid
689: FND_MSG_PUB.add;
687: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPP_CONTACT_ID');
688: --//Supplier contact id entered is invalid
689: FND_MSG_PUB.add;
690: END IF;
691: x_return_status := fnd_api.g_ret_sts_error;
692: RETURN;
693: ELSE
694: p_SDR_hdr_rec.supplier_contact_id := NULL;
695: END IF;
696: END IF;
697: END IF;
698:
699: --Supplier Contact Info
700: IF p_SDR_hdr_rec.supplier_contact_email_address = FND_API.g_miss_char OR p_SDR_hdr_rec.supplier_contact_email_address IS NULL THEN
701: p_SDR_hdr_rec.supplier_contact_email_address :=l_supp_email;
702: END IF;
703: IF p_SDR_hdr_rec.supplier_contact_phone_number = FND_API.g_miss_char OR p_SDR_hdr_rec.supplier_contact_phone_number IS NULL THEN
704: p_SDR_hdr_rec.supplier_contact_phone_number :=l_supp_phone;
699: --Supplier Contact Info
700: IF p_SDR_hdr_rec.supplier_contact_email_address = FND_API.g_miss_char OR p_SDR_hdr_rec.supplier_contact_email_address IS NULL THEN
701: p_SDR_hdr_rec.supplier_contact_email_address :=l_supp_email;
702: END IF;
703: IF p_SDR_hdr_rec.supplier_contact_phone_number = FND_API.g_miss_char OR p_SDR_hdr_rec.supplier_contact_phone_number IS NULL THEN
704: p_SDR_hdr_rec.supplier_contact_phone_number :=l_supp_phone;
705: END IF;
706:
707: --supplier_response_by_date
704: p_SDR_hdr_rec.supplier_contact_phone_number :=l_supp_phone;
705: END IF;
706:
707: --supplier_response_by_date
708: IF p_SDR_hdr_rec.supplier_response_by_date <> FND_API.g_miss_date AND p_SDR_hdr_rec.supplier_response_by_date IS NOT NULL THEN
709: p_SDR_hdr_rec.supplier_response_by_date := TRUNC(p_SDR_hdr_rec.supplier_response_by_date);
710:
711: IF p_SDR_hdr_rec.supplier_response_by_date NOT BETWEEN p_SDR_hdr_rec.request_start_date AND
712: p_SDR_hdr_rec.request_end_date THEN
714: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
715: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPRESPB_DATE');
716: FND_MSG_PUB.add;
717: END IF;
718: x_return_status := fnd_api.g_ret_sts_error;
719: RETURN;
720: END IF;
721: END IF;
722:
720: END IF;
721: END IF;
722:
723: --supplier_response_date
724: IF p_SDR_hdr_rec.supplier_response_date <> FND_API.g_miss_date AND p_SDR_hdr_rec.supplier_response_date IS NOT NULL THEN
725: p_SDR_hdr_rec.supplier_response_date := TRUNC(p_SDR_hdr_rec.supplier_response_date);
726:
727: IF p_SDR_hdr_rec.supplier_response_date NOT BETWEEN p_SDR_hdr_rec.request_start_date AND
728: p_SDR_hdr_rec.request_end_date THEN
730: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
731: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPRESP_DATE');
732: FND_MSG_PUB.add;
733: END IF;
734: x_return_status := fnd_api.g_ret_sts_error;
735: RETURN;
736: END IF;
737: END IF;
738:
736: END IF;
737: END IF;
738:
739: --supplier_submission_date
740: IF p_SDR_hdr_rec.supplier_submission_date <> FND_API.g_miss_date AND p_SDR_hdr_rec.supplier_submission_date IS NOT NULL THEN
741: p_SDR_hdr_rec.supplier_submission_date := TRUNC(p_SDR_hdr_rec.supplier_submission_date);
742:
743: IF p_SDR_hdr_rec.supplier_submission_date NOT BETWEEN p_SDR_hdr_rec.request_start_date AND
744: p_SDR_hdr_rec.request_end_date THEN
746: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
747: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPSUB_DATE');
748: FND_MSG_PUB.add;
749: END IF;
750: x_return_status := fnd_api.g_ret_sts_error;
751: RETURN;
752: END IF;
753: END IF;
754:
764: IF G_DEBUG THEN
765: OZF_UTILITY_PVT.debug_message('Assignee resource_id ID :'||p_SDR_hdr_rec.assignee_resource_id);
766: END IF;
767:
768: IF p_SDR_hdr_rec.assignee_resource_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.assignee_resource_id IS NOT NULL THEN
769:
770: OPEN c_requestor_id(p_SDR_hdr_rec.assignee_resource_id);
771: FETCH c_requestor_id INTO l_assignee_resource_id;
772: CLOSE c_requestor_id;
776: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ASSIGNEE_ID');
777: --//Assignee id entered is invalid (Approver)
778: FND_MSG_PUB.add;
779: END IF;
780: x_return_status := fnd_api.g_ret_sts_error;
781: RETURN;
782: END IF;
783: ELSE
784: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
785: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_ASSIGNEE_ID');
786: --//Assignee resource id is Mandatory (Approver is mandatory)
787: FND_MSG_PUB.add;
788: END IF;
789: x_return_status := fnd_api.g_ret_sts_error;
790: RETURN;
791: END IF;
792:
793: --asignee_response_by_date
790: RETURN;
791: END IF;
792:
793: --asignee_response_by_date
794: IF p_SDR_hdr_rec.assignee_response_by_date <> FND_API.g_miss_date AND p_SDR_hdr_rec.assignee_response_by_date IS NOT NULL THEN
795: p_SDR_hdr_rec.assignee_response_by_date := TRUNC(p_SDR_hdr_rec.assignee_response_by_date);
796:
797: IF p_SDR_hdr_rec.assignee_response_by_date NOT BETWEEN p_SDR_hdr_rec.request_start_date AND
798: p_SDR_hdr_rec.request_end_date THEN
800: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
801: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ASRESPB_DATE');
802: FND_MSG_PUB.add;
803: END IF;
804: x_return_status := fnd_api.g_ret_sts_error;
805: RETURN;
806: END IF;
807: END IF;
808:
806: END IF;
807: END IF;
808:
809: --asignee_response_date
810: IF p_SDR_hdr_rec.assignee_response_date <> FND_API.g_miss_date AND p_SDR_hdr_rec.assignee_response_date IS NOT NULL THEN
811: p_SDR_hdr_rec.assignee_response_date := TRUNC(p_SDR_hdr_rec.assignee_response_date);
812:
813: IF p_SDR_hdr_rec.assignee_response_date NOT BETWEEN p_SDR_hdr_rec.request_start_date AND
814: p_SDR_hdr_rec.request_end_date THEN
816: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
817: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ASRESP_DATE');
818: FND_MSG_PUB.add;
819: END IF;
820: x_return_status := fnd_api.g_ret_sts_error;
821: RETURN;
822: END IF;
823: END IF;
824: ELSE
843: p_SDR_hdr_rec.assignee_response_by_date :=NULL;
844: p_SDR_hdr_rec.assignee_response_date :=NULL;
845:
846:
847: IF p_SDR_hdr_rec.cust_account_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.cust_account_id IS NOT NULL THEN
848:
849: OPEN c_cust_account_id(p_SDR_hdr_rec.cust_account_id);
850: FETCH c_cust_account_id INTO l_cust_account_id;
851: CLOSE c_cust_account_id;
855: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_ACCOUNT_ID');
856: --//Cust Account ID entered is invalid
857: FND_MSG_PUB.add;
858: END IF;
859: x_return_status := fnd_api.g_ret_sts_error;
860: RETURN;
861: END IF;
862:
863: ELSE
865: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_CUST_ACCOUNT_ID');
866: --//Cust Account id is Mandatory
867: FND_MSG_PUB.add;
868: END IF;
869: x_return_status := fnd_api.g_ret_sts_error;
870: RETURN;
871: END IF;
872: END IF;
873:
871: END IF;
872: END IF;
873:
874: --//Bugfix : 7607795 - Start Date/End date Validations
875: IF p_SDR_hdr_rec.request_start_date <> FND_API.g_miss_date AND p_SDR_hdr_rec.request_start_date IS NOT NULL THEN
876:
877: IF (p_SDR_hdr_rec.request_end_date = FND_API.g_miss_date OR p_SDR_hdr_rec.request_end_date IS NULL) AND p_mode = 'CREATE' THEN
878: --//Generate End Date from Supplier trade profile
879: OPEN c_authorization_period(p_SDR_hdr_rec.supplier_id
873:
874: --//Bugfix : 7607795 - Start Date/End date Validations
875: IF p_SDR_hdr_rec.request_start_date <> FND_API.g_miss_date AND p_SDR_hdr_rec.request_start_date IS NOT NULL THEN
876:
877: IF (p_SDR_hdr_rec.request_end_date = FND_API.g_miss_date OR p_SDR_hdr_rec.request_end_date IS NULL) AND p_mode = 'CREATE' THEN
878: --//Generate End Date from Supplier trade profile
879: OPEN c_authorization_period(p_SDR_hdr_rec.supplier_id
880: ,p_SDR_hdr_rec.supplier_site_id
881: ,p_SDR_hdr_rec.org_id);
892: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_END_DATE');
893: --//End date should be greater than start date
894: FND_MSG_PUB.add;
895: END IF;
896: x_return_status := fnd_api.g_ret_sts_error;
897: RETURN;
898: END IF;
899: END IF;
900:
902: p_SDR_hdr_rec.request_start_date := TRUNC(p_SDR_hdr_rec.request_start_date);
903: p_SDR_hdr_rec.request_end_date := TRUNC(p_SDR_hdr_rec.request_end_date);
904:
905: --Request Currency code
906: IF p_SDR_hdr_rec.request_currency_code <> FND_API.g_miss_char AND p_SDR_hdr_rec.request_currency_code IS NOT NULL THEN
907: OPEN c_currency (p_SDR_hdr_rec.request_currency_code);
908: FETCH c_currency INTO l_currency_code;
909: CLOSE c_currency;
910:
912: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
913: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CURRENCY_CODE');
914: FND_MSG_PUB.add;
915: END IF;
916: x_return_status := fnd_api.g_ret_sts_error;
917: RETURN;
918: END IF;
919: ELSE
920: --//Get currency code from profile :JTF_PROFILE_DEFAULT_CURRENCY
926: FND_MESSAGE.set_name('OZF', 'OZF_SD_ADD_PROFILE_CURRENCY');
927: --//Please set default Currency in profile JTF_PROFILE_DEFAULT_CURRENCY
928: FND_MSG_PUB.add;
929: END IF;
930: x_return_status := fnd_api.g_ret_sts_error;
931: RETURN;
932: END IF;
933: END IF;
934:
932: END IF;
933: END IF;
934:
935: --Request Outcome
936: IF p_SDR_hdr_rec.request_outcome <> FND_API.g_miss_char AND p_SDR_hdr_rec.request_outcome IS NOT NULL THEN
937: l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
938: p_lookup_table_name =>'OZF_LOOKUPS'
939: ,p_lookup_type =>'OZF_SD_REQUEST_OUTCOME'
940: ,p_lookup_code => p_SDR_hdr_rec.request_outcome);
938: p_lookup_table_name =>'OZF_LOOKUPS'
939: ,p_lookup_type =>'OZF_SD_REQUEST_OUTCOME'
940: ,p_lookup_code => p_SDR_hdr_rec.request_outcome);
941:
942: IF l_lookup_stat = FND_API.g_false THEN
943: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
944: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQ_OUTCOME');
945: FND_MSG_PUB.add;
946: END IF;
943: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
944: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQ_OUTCOME');
945: FND_MSG_PUB.add;
946: END IF;
947: x_return_status := fnd_api.g_ret_sts_error;
948: RETURN;
949: END IF;
950: END IF;
951:
949: END IF;
950: END IF;
951:
952: --Internal Order Number
953: IF p_SDR_hdr_rec.internal_order_number <> FND_API.g_miss_num AND p_SDR_hdr_rec.internal_order_number IS NOT NULL THEN
954: OPEN c_order_no (p_SDR_hdr_rec.internal_order_number,p_SDR_hdr_rec.org_id);
955: FETCH c_order_no INTO l_internal_order_number;
956: CLOSE c_order_no;
957:
959: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
960: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ORDER_NUMBER'); --//Seed
961: FND_MSG_PUB.add;
962: END IF;
963: x_return_status := fnd_api.g_ret_sts_error;
964: RETURN;
965: END IF;
966: END IF;
967:
965: END IF;
966: END IF;
967:
968: --Sales Order Currency
969: IF p_SDR_hdr_rec.sales_order_currency <> FND_API.g_miss_char AND p_SDR_hdr_rec.sales_order_currency IS NOT NULL THEN
970: OPEN c_currency (p_SDR_hdr_rec.sales_order_currency);
971: FETCH c_currency INTO l_sales_order_currency;
972: CLOSE c_currency;
973:
976: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SO_CURRENCY');
977: --//Invalid Sales order Currency.
978: FND_MSG_PUB.add;
979: END IF;
980: x_return_status := fnd_api.g_ret_sts_error;
981: RETURN;
982: END IF;
983: END IF;
984:
982: END IF;
983: END IF;
984:
985: --internal_submission_date
986: IF p_SDR_hdr_rec.internal_submission_date <> FND_API.g_miss_date AND p_SDR_hdr_rec.internal_submission_date IS NOT NULL THEN
987: p_SDR_hdr_rec.internal_submission_date := TRUNC(p_SDR_hdr_rec.internal_submission_date);
988:
989: IF p_SDR_hdr_rec.internal_submission_date NOT BETWEEN p_SDR_hdr_rec.request_start_date AND
990: p_SDR_hdr_rec.request_end_date THEN
992: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
993: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_INTSUB_DATE');
994: FND_MSG_PUB.add;
995: END IF;
996: x_return_status := fnd_api.g_ret_sts_error;
997: RETURN;
998: END IF;
999: END IF;
1000:
1185: WHERE request_line_id = p_request_line_id;
1186:
1187: BEGIN
1188:
1189: x_return_status := FND_API.G_RET_STS_SUCCESS;
1190:
1191: --//Get the master organization id
1192: OPEN c_master_org_id(p_SDR_hdr_rec.org_id);
1193: FETCH c_master_org_id INTO G_ITEM_ORG_ID;
1201: FOR i IN p_SDR_lines_tbl.FIRST..p_SDR_lines_tbl.LAST LOOP
1202: --//Update Mode
1203: IF p_mode ='UPDATE' THEN
1204:
1205: IF p_SDR_lines_tbl(i).request_line_id <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).request_line_id IS NOT NULL THEN
1206: --Updating existing lines
1207: OPEN c_request_line_id(p_SDR_lines_tbl(i).request_line_id,p_SDR_hdr_rec.request_header_id);
1208: FETCH c_request_line_id INTO l_request_line_id;
1209: CLOSE c_request_line_id;
1213: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQUEST_LINE_ID');
1214: --//Request Line id is Invalid , Please Re-enter
1215: FND_MSG_PUB.add;
1216: END IF;
1217: x_return_status := fnd_api.g_ret_sts_error;
1218: RETURN;
1219: ELSE
1220:
1221: --//Reset the values of new record from old record if NULL.
1333:
1334: --//Update Mode End
1335:
1336: --Product Context
1337: IF p_SDR_lines_tbl(i).product_context <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).product_context IS NOT NULL THEN
1338:
1339: l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
1340: p_lookup_table_name =>'OZF_LOOKUPS'
1341: ,p_lookup_type =>'OZF_SD_PRODUCT_CONTEXT'
1340: p_lookup_table_name =>'OZF_LOOKUPS'
1341: ,p_lookup_type =>'OZF_SD_PRODUCT_CONTEXT'
1342: ,p_lookup_code => p_SDR_lines_tbl(i).product_context);
1343:
1344: IF l_lookup_stat = FND_API.g_false THEN
1345: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1346: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_PRODUCT_TYPE');
1347: FND_MSG_PUB.add;
1348: END IF;
1345: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1346: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_PRODUCT_TYPE');
1347: FND_MSG_PUB.add;
1348: END IF;
1349: x_return_status := fnd_api.g_ret_sts_error;
1350: RETURN;
1351: END IF;
1352: ELSE
1353: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1354: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_PRODUCT_CONTEXT');
1355: --//Product context is Mandatory
1356: FND_MSG_PUB.add;
1357: END IF;
1358: x_return_status := fnd_api.g_ret_sts_error;
1359: RETURN;
1360: END IF;
1361:
1362: --//PRODUCT
1365:
1366: p_SDR_lines_tbl(i).prod_catg_id := NULL;
1367: p_SDR_lines_tbl(i).product_cat_set_id := NULL;
1368:
1369: IF p_SDR_lines_tbl(i).inventory_item_id <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).inventory_item_id IS NOT NULL THEN
1370:
1371: OPEN c_inventory_item_id(p_SDR_lines_tbl(i).inventory_item_id,G_ITEM_ORG_ID);
1372: FETCH c_inventory_item_id INTO l_inventory_item_id;
1373: CLOSE c_inventory_item_id;
1377: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_INVENTORY_ITEM_ID');
1378: --//Inventory item id is Invalid
1379: FND_MSG_PUB.add;
1380: END IF;
1381: x_return_status := fnd_api.g_ret_sts_error;
1382: RETURN;
1383: END IF;
1384: --//Populating Vendor Item code
1385: --// Bugfix 7372272 gmiss_num changed to gmiss_char
1382: RETURN;
1383: END IF;
1384: --//Populating Vendor Item code
1385: --// Bugfix 7372272 gmiss_num changed to gmiss_char
1386: IF p_SDR_lines_tbl(i).vendor_item_code = FND_API.g_miss_char OR p_SDR_lines_tbl(i).vendor_item_code IS NULL THEN
1387:
1388: OPEN c_vendor_item_code(p_SDR_hdr_rec.supplier_id,p_SDR_hdr_rec.supplier_site_id,p_SDR_hdr_rec.org_id,p_SDR_lines_tbl(i).inventory_item_id);
1389: FETCH c_vendor_item_code INTO p_SDR_lines_tbl(i).vendor_item_code;
1390: CLOSE c_vendor_item_code;
1389: FETCH c_vendor_item_code INTO p_SDR_lines_tbl(i).vendor_item_code;
1390: CLOSE c_vendor_item_code;
1391: END IF;
1392:
1393: ELSIF p_SDR_lines_tbl(i).vendor_item_code <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).vendor_item_code IS NOT NULL THEN
1394:
1395: l_external_code := NULL;
1396: l_internal_code := NULL;
1397: OPEN c_ext_int_code(p_SDR_hdr_rec.supplier_id,p_SDR_hdr_rec.supplier_site_id,p_SDR_hdr_rec.org_id);
1402: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1403: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_VENDOR_ITEM_CODE');
1404: FND_MSG_PUB.add;
1405: END IF;
1406: x_return_status := fnd_api.g_ret_sts_error;
1407: RETURN;
1408: END IF;
1409: p_SDR_lines_tbl(i).inventory_item_id := l_internal_code;
1410:
1413: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_INVENTORY_ITEM_ID');
1414: --//Inventory item id is mandatory
1415: FND_MSG_PUB.add;
1416: END IF;
1417: x_return_status := fnd_api.g_ret_sts_error;
1418: RETURN;
1419: END IF;
1420:
1421: IF p_SDR_lines_tbl(i).item_uom <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).item_uom IS NOT NULL THEN
1417: x_return_status := fnd_api.g_ret_sts_error;
1418: RETURN;
1419: END IF;
1420:
1421: IF p_SDR_lines_tbl(i).item_uom <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).item_uom IS NOT NULL THEN
1422:
1423: OPEN c_item_uom(p_SDR_lines_tbl(i).item_uom
1424: ,p_SDR_lines_tbl(i).inventory_item_id
1425: ,G_ITEM_ORG_ID);
1431: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ITEM_UOM');
1432: --//Unit of Measurement is Invalid
1433: FND_MSG_PUB.add;
1434: END IF;
1435: x_return_status := fnd_api.g_ret_sts_error;
1436: RETURN;
1437: END IF;
1438: ELSE
1439: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1440: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_ITEM_UOM');
1441: --//Unit of Measurement is mandatory
1442: FND_MSG_PUB.add;
1443: END IF;
1444: x_return_status := fnd_api.g_ret_sts_error;
1445: RETURN;
1446: END IF;
1447:
1448: --//Product Cost Validation Bugfix: 7501046
1445: RETURN;
1446: END IF;
1447:
1448: --//Product Cost Validation Bugfix: 7501046
1449: IF p_SDR_lines_tbl(i).product_cost = FND_API.g_miss_num OR p_SDR_lines_tbl(i).product_cost IS NULL THEN
1450: OPEN c_product_cost(p_SDR_lines_tbl(i).inventory_item_id,G_ITEM_ORG_ID);
1451: FETCH c_product_cost INTO p_SDR_lines_tbl(i).product_cost;
1452: CLOSE c_product_cost;
1453:
1456: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_PRODUCT_COST');
1457: --//A value must be entered for Product Cost
1458: FND_MSG_PUB.add;
1459: END IF;
1460: x_return_status := fnd_api.g_ret_sts_error;
1461: RETURN;
1462: END IF;
1463: END IF;
1464:
1470: p_SDR_lines_tbl(i).vendor_item_code := NULL;
1471: p_SDR_lines_tbl(i).item_uom := NULL;
1472:
1473: --//Category ID
1474: IF p_SDR_lines_tbl(i).prod_catg_id <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).prod_catg_id IS NOT NULL THEN
1475:
1476: OPEN c_category_id(p_SDR_lines_tbl(i).prod_catg_id);
1477: FETCH c_category_id INTO l_category_id;
1478: CLOSE c_category_id;
1482: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_PRODUCT_CATEGORY');
1483: --//Product category is Invalid
1484: FND_MSG_PUB.add;
1485: END IF;
1486: x_return_status := fnd_api.g_ret_sts_error;
1487: RETURN;
1488: END IF;
1489: ELSE
1490: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1491: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_PRODUCT_CATEGORY');
1492: --//Product category is mandatory
1493: FND_MSG_PUB.add;
1494: END IF;
1495: x_return_status := fnd_api.g_ret_sts_error;
1496: RETURN;
1497: END IF;
1498:
1499: --//Category set id
1496: RETURN;
1497: END IF;
1498:
1499: --//Category set id
1500: IF p_SDR_lines_tbl(i).product_cat_set_id <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).product_cat_set_id IS NOT NULL THEN
1501:
1502: OPEN c_category_set_id(p_SDR_lines_tbl(i).prod_catg_id
1503: ,p_SDR_lines_tbl(i).product_cat_set_id);
1504: FETCH c_category_set_id INTO l_category_set_id;
1509: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_PRODUCT_CATEGORY_SET');
1510: --//Product category set is Invalid
1511: FND_MSG_PUB.add;
1512: END IF;
1513: x_return_status := fnd_api.g_ret_sts_error;
1514: RETURN;
1515: END IF;
1516: END IF;
1517:
1517:
1518: END IF;
1519:
1520: --//Requested discount type
1521: IF p_SDR_lines_tbl(i).requested_discount_type <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).requested_discount_type IS NOT NULL THEN
1522: l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
1523: p_lookup_table_name =>'OZF_LOOKUPS'
1524: ,p_lookup_type =>'OZF_SD_REQUEST_DISTYPE'
1525: ,p_lookup_code => p_SDR_lines_tbl(i).requested_discount_type);
1523: p_lookup_table_name =>'OZF_LOOKUPS'
1524: ,p_lookup_type =>'OZF_SD_REQUEST_DISTYPE'
1525: ,p_lookup_code => p_SDR_lines_tbl(i).requested_discount_type);
1526:
1527: IF l_lookup_stat = FND_API.g_false THEN
1528: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1529: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_DISCOUNT_TYPE');
1530: FND_MSG_PUB.add;
1531: END IF;
1528: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1529: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_DISCOUNT_TYPE');
1530: FND_MSG_PUB.add;
1531: END IF;
1532: x_return_status := fnd_api.g_ret_sts_error;
1533: RETURN;
1534: END IF;
1535: ELSE
1536: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1536: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1537: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_DISCOUNT_TYPE');
1538: FND_MSG_PUB.add;
1539: END IF;
1540: x_return_status := fnd_api.g_ret_sts_error;
1541: RETURN;
1542: END IF;
1543:
1544: --//Requested discount Value
1542: END IF;
1543:
1544: --//Requested discount Value
1545:
1546: IF p_SDR_lines_tbl(i).requested_discount_value = FND_API.g_miss_num OR p_SDR_lines_tbl(i).requested_discount_value IS NULL THEN
1547: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1548: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_DISC_VALUE');
1549: FND_MSG_PUB.add;
1550: END IF;
1547: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1548: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_DISC_VALUE');
1549: FND_MSG_PUB.add;
1550: END IF;
1551: x_return_status := fnd_api.g_ret_sts_error;
1552: RETURN;
1553: ELSE
1554: l_status := check_zero(p_SDR_lines_tbl(i).requested_discount_value);
1555: IF l_status = FND_API.g_false THEN
1551: x_return_status := fnd_api.g_ret_sts_error;
1552: RETURN;
1553: ELSE
1554: l_status := check_zero(p_SDR_lines_tbl(i).requested_discount_value);
1555: IF l_status = FND_API.g_false THEN
1556: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1557: FND_MESSAGE.set_name('OZF', 'OZF_SD_DISCOUNT_VALUE_IS_ZERO'); --//Check the message!
1558: --//Discount value should not be zero
1559: FND_MSG_PUB.add;
1557: FND_MESSAGE.set_name('OZF', 'OZF_SD_DISCOUNT_VALUE_IS_ZERO'); --//Check the message!
1558: --//Discount value should not be zero
1559: FND_MSG_PUB.add;
1560: END IF;
1561: x_return_status := fnd_api.g_ret_sts_error;
1562: RETURN;
1563: END IF;
1564: END IF;
1565:
1571: CLOSE c_cost_basis;
1572: p_SDR_lines_tbl(i).cost_basis := l_cost_basis;
1573:
1574: --//Max Qty
1575: IF p_SDR_lines_tbl(i).max_qty <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).max_qty IS NOT NULL THEN
1576: l_status := check_zero(p_SDR_lines_tbl(i).max_qty);
1577: IF l_status = FND_API.g_false THEN
1578: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1579: FND_MESSAGE.set_name('OZF', 'OZF_SD_MAX_QTY_ZERO_CHECK'); --//Check the message!
1573:
1574: --//Max Qty
1575: IF p_SDR_lines_tbl(i).max_qty <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).max_qty IS NOT NULL THEN
1576: l_status := check_zero(p_SDR_lines_tbl(i).max_qty);
1577: IF l_status = FND_API.g_false THEN
1578: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1579: FND_MESSAGE.set_name('OZF', 'OZF_SD_MAX_QTY_ZERO_CHECK'); --//Check the message!
1580: --//Discount value should not be zero
1581: FND_MSG_PUB.add;
1579: FND_MESSAGE.set_name('OZF', 'OZF_SD_MAX_QTY_ZERO_CHECK'); --//Check the message!
1580: --//Discount value should not be zero
1581: FND_MSG_PUB.add;
1582: END IF;
1583: x_return_status := fnd_api.g_ret_sts_error;
1584: RETURN;
1585: END IF;
1586:
1587: --//Set limit Qty
1595:
1596: END IF;
1597:
1598: --//End Customer price
1599: IF p_SDR_lines_tbl(i).end_customer_price <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).end_customer_price IS NOT NULL THEN
1600: l_status := check_zero(p_SDR_lines_tbl(i).end_customer_price);
1601: IF l_status = FND_API.g_false THEN
1602: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1603: FND_MESSAGE.set_name('OZF', 'OZF_SD_END_CUST_PRICE_VALUE_IS_ZERO');
1597:
1598: --//End Customer price
1599: IF p_SDR_lines_tbl(i).end_customer_price <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).end_customer_price IS NOT NULL THEN
1600: l_status := check_zero(p_SDR_lines_tbl(i).end_customer_price);
1601: IF l_status = FND_API.g_false THEN
1602: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1603: FND_MESSAGE.set_name('OZF', 'OZF_SD_END_CUST_PRICE_VALUE_IS_ZERO');
1604: --//Discount value should not be zero
1605: FND_MSG_PUB.add;
1603: FND_MESSAGE.set_name('OZF', 'OZF_SD_END_CUST_PRICE_VALUE_IS_ZERO');
1604: --//Discount value should not be zero
1605: FND_MSG_PUB.add;
1606: END IF;
1607: x_return_status := fnd_api.g_ret_sts_error;
1608: RETURN;
1609: END IF;
1610: END IF;
1611:
1609: END IF;
1610: END IF;
1611:
1612: --Vendor Approved Flag
1613: IF p_SDR_lines_tbl(i).vendor_approved_flag <> FND_API.g_miss_char OR p_SDR_lines_tbl(i).vendor_approved_flag IS NULL THEN
1614: p_SDR_lines_tbl(i).vendor_approved_flag := 'Y';
1615: ELSE
1616: IF p_SDR_lines_tbl(i).vendor_approved_flag NOT IN ('Y','N')THEN
1617: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1617: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1618: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_VENDOR_APPR_FLAG');
1619: FND_MSG_PUB.add;
1620: END IF;
1621: x_return_status := fnd_api.g_ret_sts_error;
1622: RETURN;
1623: END IF;
1624: END IF;
1625: --//Bugfix : 7607795
1623: END IF;
1624: END IF;
1625: --//Bugfix : 7607795
1626: IF p_mode ='CREATE' THEN
1627: IF p_SDR_lines_tbl(i).start_date = FND_API.g_miss_date OR p_SDR_lines_tbl(i).start_date IS NULL THEN
1628: p_SDR_lines_tbl(i).start_date :=p_SDR_hdr_rec.request_start_date;
1629:
1630: ELSIF TRUNC(p_SDR_lines_tbl(i).start_date) NOT BETWEEN
1631: TRUNC(p_SDR_hdr_rec.request_start_date) AND TRUNC(p_SDR_hdr_rec.request_end_date) THEN
1634: FND_MESSAGE.set_name('OZF', 'OZF_SD_LINE_START_DATE_CHECK');
1635: --//Start Date should fall in between Header Start date and End Date
1636: FND_MSG_PUB.add;
1637: END IF;
1638: x_return_status := fnd_api.g_ret_sts_error;
1639: RETURN;
1640: END IF;
1641:
1642: --End Date
1639: RETURN;
1640: END IF;
1641:
1642: --End Date
1643: IF p_SDR_lines_tbl(i).end_date = FND_API.g_miss_date OR p_SDR_lines_tbl(i).end_date IS NULL THEN
1644: p_SDR_lines_tbl(i).end_date :=p_SDR_hdr_rec.request_end_date;
1645:
1646: ELSIF TRUNC(p_SDR_lines_tbl(i).end_date) NOT BETWEEN
1647: TRUNC(p_SDR_hdr_rec.request_start_date) AND TRUNC(p_SDR_hdr_rec.request_end_date) THEN
1650: FND_MESSAGE.set_name('OZF', 'OZF_SD_LINE_END_DATE_CHECK');
1651: --//End Date should fall in between Header Start date and End Date
1652: FND_MSG_PUB.add;
1653: END IF;
1654: x_return_status := fnd_api.g_ret_sts_error;
1655: RETURN;
1656: END IF;
1657: END IF;
1658:
1661: FND_MESSAGE.set_name('OZF', 'OZF_SD_LINE_END_DATE_GT_CHECK');
1662: --//End Date should be greater than start date
1663: FND_MSG_PUB.add;
1664: END IF;
1665: x_return_status := fnd_api.g_ret_sts_error;
1666: RETURN;
1667: END IF;
1668:
1669: p_SDR_lines_tbl(i).start_date := TRUNC(p_SDR_lines_tbl(i).start_date);
1669: p_SDR_lines_tbl(i).start_date := TRUNC(p_SDR_lines_tbl(i).start_date);
1670: p_SDR_lines_tbl(i).end_date := TRUNC(p_SDR_lines_tbl(i).end_date);
1671:
1672: --//Price Type
1673: IF p_SDR_lines_tbl(i).end_customer_price_type <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).end_customer_price_type IS NOT NULL THEN
1674: l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
1675: p_lookup_table_name =>'OZF_LOOKUPS'
1676: ,p_lookup_type =>'OZF_SD_PRICE_TYPE'
1677: ,p_lookup_code => p_SDR_lines_tbl(i).end_customer_price_type);
1675: p_lookup_table_name =>'OZF_LOOKUPS'
1676: ,p_lookup_type =>'OZF_SD_PRICE_TYPE'
1677: ,p_lookup_code => p_SDR_lines_tbl(i).end_customer_price_type);
1678:
1679: IF l_lookup_stat = FND_API.g_false THEN
1680: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1681: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_PRICE_TYPE');
1682: FND_MSG_PUB.add;
1683: END IF;
1680: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1681: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_PRICE_TYPE');
1682: FND_MSG_PUB.add;
1683: END IF;
1684: x_return_status := fnd_api.g_ret_sts_error;
1685: RETURN;
1686: END IF;
1687: END IF;
1688:
1686: END IF;
1687: END IF;
1688:
1689: --Tolerance Type
1690: IF p_SDR_lines_tbl(i).end_customer_tolerance_type <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).end_customer_tolerance_type IS NOT NULL THEN
1691: l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
1692: p_lookup_table_name =>'OZF_LOOKUPS'
1693: ,p_lookup_type =>'OZF_SD_TOLERANCE_TYPE'
1694: ,p_lookup_code => p_SDR_lines_tbl(i).end_customer_tolerance_type);
1692: p_lookup_table_name =>'OZF_LOOKUPS'
1693: ,p_lookup_type =>'OZF_SD_TOLERANCE_TYPE'
1694: ,p_lookup_code => p_SDR_lines_tbl(i).end_customer_tolerance_type);
1695:
1696: IF l_lookup_stat = FND_API.g_false THEN
1697: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1698: FND_MESSAGE.set_name('OZF', 'OZF_SD_BAD_TOLERANCE_TYPE');
1699: FND_MSG_PUB.add;
1700: END IF;
1697: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1698: FND_MESSAGE.set_name('OZF', 'OZF_SD_BAD_TOLERANCE_TYPE');
1699: FND_MSG_PUB.add;
1700: END IF;
1701: x_return_status := fnd_api.g_ret_sts_error;
1702: RETURN;
1703: END IF;
1704: END IF;
1705:
1703: END IF;
1704: END IF;
1705:
1706: --//Rejection Code
1707: IF p_SDR_lines_tbl(i).rejection_code <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).rejection_code IS NOT NULL THEN
1708: l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
1709: p_lookup_table_name =>'OZF_LOOKUPS'
1710: ,p_lookup_type =>'OZF_SD_REQ_LINE_REJECT_CODE'
1711: ,p_lookup_code => p_SDR_lines_tbl(i).rejection_code);
1709: p_lookup_table_name =>'OZF_LOOKUPS'
1710: ,p_lookup_type =>'OZF_SD_REQ_LINE_REJECT_CODE'
1711: ,p_lookup_code => p_SDR_lines_tbl(i).rejection_code);
1712:
1713: IF l_lookup_stat = FND_API.g_false THEN
1714: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1715: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REJECTION_CODE');
1716: FND_MSG_PUB.add;
1717: END IF;
1714: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1715: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REJECTION_CODE');
1716: FND_MSG_PUB.add;
1717: END IF;
1718: x_return_status := fnd_api.g_ret_sts_error;
1719: RETURN;
1720: END IF;
1721: END IF;
1722: l_status_code := get_system_status_code(p_SDR_hdr_rec.user_status_id);
1729: OZF_UTILITY_PVT.debug_message('Validate Product Lines Status Code INSIDE');
1730: END IF;
1731:
1732: --//Approved discount type
1733: IF p_SDR_lines_tbl(i).approved_discount_type <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).approved_discount_type IS NOT NULL THEN
1734: l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
1735: p_lookup_table_name =>'OZF_LOOKUPS'
1736: ,p_lookup_type =>'OZF_SP_REQUEST_DISTYPE'
1737: ,p_lookup_code => p_SDR_lines_tbl(i).approved_discount_type);
1735: p_lookup_table_name =>'OZF_LOOKUPS'
1736: ,p_lookup_type =>'OZF_SP_REQUEST_DISTYPE'
1737: ,p_lookup_code => p_SDR_lines_tbl(i).approved_discount_type);
1738:
1739: IF l_lookup_stat =FND_API.g_false THEN
1740: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1741: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_APPROVED_DISC_TYPE');
1742: FND_MSG_PUB.add;
1743: END IF;
1740: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1741: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_APPROVED_DISC_TYPE');
1742: FND_MSG_PUB.add;
1743: END IF;
1744: x_return_status := fnd_api.g_ret_sts_error;
1745: RETURN;
1746: END IF;
1747: ELSE
1748: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1748: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1749: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_APPROVED_DISC_TYPE');
1750: FND_MSG_PUB.add;
1751: END IF;
1752: x_return_status := fnd_api.g_ret_sts_error;
1753: RETURN;
1754: END IF;
1755:
1756: --//Approved discount Value
1753: RETURN;
1754: END IF;
1755:
1756: --//Approved discount Value
1757: IF p_SDR_lines_tbl(i).approved_discount_value <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).approved_discount_value IS NOT NULL THEN
1758: l_status := check_zero(p_SDR_lines_tbl(i).approved_discount_value);
1759: IF l_status = FND_API.g_false THEN
1760: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1761: FND_MESSAGE.set_name('OZF', 'OZF_SD_APPR_DISC_VALUE_IS_ZERO');
1755:
1756: --//Approved discount Value
1757: IF p_SDR_lines_tbl(i).approved_discount_value <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).approved_discount_value IS NOT NULL THEN
1758: l_status := check_zero(p_SDR_lines_tbl(i).approved_discount_value);
1759: IF l_status = FND_API.g_false THEN
1760: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1761: FND_MESSAGE.set_name('OZF', 'OZF_SD_APPR_DISC_VALUE_IS_ZERO');
1762: FND_MSG_PUB.add;
1763: END IF;
1760: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1761: FND_MESSAGE.set_name('OZF', 'OZF_SD_APPR_DISC_VALUE_IS_ZERO');
1762: FND_MSG_PUB.add;
1763: END IF;
1764: x_return_status := fnd_api.g_ret_sts_error;
1765: RETURN;
1766: END IF;
1767: ELSE
1768: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1768: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1769: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_APPROVED_DISC_VALUE');
1770: FND_MSG_PUB.add;
1771: END IF;
1772: x_return_status := fnd_api.g_ret_sts_error;
1773: RETURN;
1774: END IF;
1775:
1776: --//Approved Max Qty
1773: RETURN;
1774: END IF;
1775:
1776: --//Approved Max Qty
1777: IF p_SDR_lines_tbl(i).approved_max_qty <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).approved_max_qty IS NOT NULL THEN
1778: l_status := check_zero(p_SDR_lines_tbl(i).approved_max_qty);
1779: IF l_status = FND_API.g_false THEN
1780: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1781: FND_MESSAGE.set_name('OZF', 'OZF_SD_APPR_MAX_QTY_VALUE_IS_ZERO');
1775:
1776: --//Approved Max Qty
1777: IF p_SDR_lines_tbl(i).approved_max_qty <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).approved_max_qty IS NOT NULL THEN
1778: l_status := check_zero(p_SDR_lines_tbl(i).approved_max_qty);
1779: IF l_status = FND_API.g_false THEN
1780: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1781: FND_MESSAGE.set_name('OZF', 'OZF_SD_APPR_MAX_QTY_VALUE_IS_ZERO');
1782: FND_MSG_PUB.add;
1783: END IF;
1780: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1781: FND_MESSAGE.set_name('OZF', 'OZF_SD_APPR_MAX_QTY_VALUE_IS_ZERO');
1782: FND_MSG_PUB.add;
1783: END IF;
1784: x_return_status := fnd_api.g_ret_sts_error;
1785: RETURN;
1786: END IF;
1787: /* BugFix : 7501013
1788: ELSE
1789: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1790: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_APPROVED_MAX_QTY');
1791: FND_MSG_PUB.add;
1792: END IF;
1793: x_return_status := fnd_api.g_ret_sts_error;
1794: RETURN; */
1795: END IF;
1796: ELSE
1797: p_SDR_lines_tbl(i).approved_discount_type := NULL;
1890: WHERE request_customer_id = p_request_customer_id;
1891:
1892: BEGIN
1893:
1894: x_return_status := FND_API.G_RET_STS_SUCCESS;
1895:
1896: FOR j IN p_SDR_cust_tbl.FIRST..p_SDR_cust_tbl.LAST LOOP
1897:
1898: --//Update Mode Check
1897:
1898: --//Update Mode Check
1899: IF p_mode ='UPDATE' THEN
1900:
1901: IF p_SDR_cust_tbl(j).request_customer_id <> FND_API.g_miss_num AND p_SDR_cust_tbl(j).request_customer_id IS NOT NULL THEN
1902: OPEN c_request_customer_id (p_SDR_cust_tbl(j).request_customer_id,G_REQUEST_HEADER_ID);
1903: FETCH c_request_customer_id INTO l_request_customer_id;
1904: CLOSE c_request_customer_id;
1905:
1907: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1908: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQUEST_CUSTOMER_ID');
1909: FND_MSG_PUB.add;
1910: END IF;
1911: x_return_status := fnd_api.g_ret_sts_error;
1912: RETURN;
1913: ELSE
1914: --//Set old value to New If NULL.
1915: OPEN c_old_cust_details(p_SDR_cust_tbl(j).request_customer_id);
1964: END IF;
1965: END IF;
1966:
1967: --//End Customer Flag check
1968: IF p_SDR_cust_tbl(j).end_customer_flag <> FND_API.g_miss_char AND p_SDR_cust_tbl(j).end_customer_flag IS NOT NULL THEN
1969:
1970: IF p_SDR_cust_tbl(j).end_customer_flag NOT IN ('N','Y') THEN
1971:
1972: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1973: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_END_CUST_FLAG');
1974: --//Invalid End Customer flag
1975: FND_MSG_PUB.add;
1976: END IF;
1977: x_return_status := fnd_api.g_ret_sts_error;
1978: RETURN;
1979: END IF;
1980: ELSE
1981: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1982: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_END_CUST_FLAG');
1983: --//End Customer Flag is Mandatory
1984: FND_MSG_PUB.add;
1985: END IF;
1986: x_return_status := fnd_api.g_ret_sts_error;
1987: RETURN;
1988: END IF;
1989:
1990: --//Customer usage code Check
1987: RETURN;
1988: END IF;
1989:
1990: --//Customer usage code Check
1991: IF p_SDR_cust_tbl(j).cust_usage_code <> FND_API.g_miss_char AND p_SDR_cust_tbl(j).cust_usage_code IS NOT NULL THEN
1992:
1993: l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
1994: p_lookup_table_name =>'OZF_LOOKUPS'
1995: ,p_lookup_type =>'OZF_SD_CUSTOMER_TYPE'
1994: p_lookup_table_name =>'OZF_LOOKUPS'
1995: ,p_lookup_type =>'OZF_SD_CUSTOMER_TYPE'
1996: ,p_lookup_code => p_SDR_cust_tbl(j).cust_usage_code);
1997:
1998: IF l_lookup_stat = FND_API.g_false THEN
1999: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2000: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_ADDR_TYPE');
2001: FND_MSG_PUB.add;
2002: END IF;
1999: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2000: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_ADDR_TYPE');
2001: FND_MSG_PUB.add;
2002: END IF;
2003: x_return_status := fnd_api.g_ret_sts_error;
2004: RETURN;
2005: END IF;
2006:
2007: --//if end_customer_flag = 'Y' then cust_usage_code should be CUSTOMER only
2010: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2011: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_COMB');
2012: FND_MSG_PUB.add;
2013: END IF;
2014: x_return_status := fnd_api.g_ret_sts_error;
2015: RETURN;
2016: END IF;
2017:
2018: --//Party ID is mandatory for All Combinations
2015: RETURN;
2016: END IF;
2017:
2018: --//Party ID is mandatory for All Combinations
2019: IF p_SDR_cust_tbl(j).party_id <> FND_API.g_miss_num AND p_SDR_cust_tbl(j).party_id IS NOT NULL THEN
2020: OPEN c_party_id (p_SDR_cust_tbl(j).party_id);
2021: FETCH c_party_id INTO l_party_id;
2022: CLOSE c_party_id;
2023:
2025: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2026: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_PARTY_ID'); --//To set in design
2027: FND_MSG_PUB.add;
2028: END IF;
2029: x_return_status := fnd_api.g_ret_sts_error;
2030: RETURN;
2031: END IF;
2032: ELSE
2033: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2034: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_PARTY_ID');
2035: --//Party Id is mandatory
2036: FND_MSG_PUB.add;
2037: END IF;
2038: x_return_status := fnd_api.g_ret_sts_error;
2039: RETURN;
2040: END IF;
2041:
2042: IF p_SDR_cust_tbl(j).cust_usage_code ='CUSTOMER' THEN
2041:
2042: IF p_SDR_cust_tbl(j).cust_usage_code ='CUSTOMER' THEN
2043:
2044: --//Cust Account ID
2045: IF p_SDR_cust_tbl(j).cust_account_id <> FND_API.g_miss_num AND p_SDR_cust_tbl(j).cust_account_id IS NOT NULL THEN
2046: OPEN c_cust_account_id (p_SDR_cust_tbl(j).cust_account_id);
2047: FETCH c_cust_account_id INTO l_cust_account_id;
2048: CLOSE c_cust_account_id;
2049:
2051: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2052: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_ACCOUNT_ID');
2053: FND_MSG_PUB.add;
2054: END IF;
2055: x_return_status := fnd_api.g_ret_sts_error;
2056: RETURN;
2057: END IF;
2058: ELSE
2059: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2059: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2060: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_CUST_ACCOUNT_ID');
2061: FND_MSG_PUB.add;
2062: END IF;
2063: x_return_status := fnd_api.g_ret_sts_error;
2064: RETURN;
2065: END IF;
2066:
2067: --//For Bill To/Ship To Site Use Id is Mandatory
2067: --//For Bill To/Ship To Site Use Id is Mandatory
2068: ELSIF p_SDR_cust_tbl(j).cust_usage_code IN ('BILL_TO','SHIP_TO') THEN
2069:
2070: --//Cust Account ID
2071: IF p_SDR_cust_tbl(j).cust_account_id <> FND_API.g_miss_num AND p_SDR_cust_tbl(j).cust_account_id IS NOT NULL THEN
2072: OPEN c_cust_account_id (p_SDR_cust_tbl(j).cust_account_id);
2073: FETCH c_cust_account_id INTO l_cust_account_id;
2074: CLOSE c_cust_account_id;
2075:
2077: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2078: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_ACCOUNT_ID');
2079: FND_MSG_PUB.add;
2080: END IF;
2081: x_return_status := fnd_api.g_ret_sts_error;
2082: RETURN;
2083: END IF;
2084: END IF;
2085:
2083: END IF;
2084: END IF;
2085:
2086: --//Site Use Id
2087: IF p_SDR_cust_tbl(j).site_use_id <> FND_API.g_miss_num AND p_SDR_cust_tbl(j).site_use_id IS NOT NULL THEN
2088: OPEN c_site_use_id (p_SDR_cust_tbl(j).cust_account_id
2089: ,p_SDR_cust_tbl(j).party_id
2090: ,p_SDR_cust_tbl(j).site_use_id
2091: ,p_SDR_cust_tbl(j).cust_usage_code);
2096: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2097: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SITE_USE_ID');
2098: FND_MSG_PUB.add;
2099: END IF;
2100: x_return_status := fnd_api.g_ret_sts_error;
2101: RETURN;
2102: END IF;
2103: ELSE
2104: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2104: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2105: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_SITE_USE_ID');
2106: FND_MSG_PUB.add;
2107: END IF;
2108: x_return_status := fnd_api.g_ret_sts_error;
2109: RETURN;
2110: END IF;
2111: END IF;
2112: ELSE
2114: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_CUST_ADDR_TYPE');
2115: --//Invalid Customer Usage Code
2116: FND_MSG_PUB.add;
2117: END IF;
2118: x_return_status := fnd_api.g_ret_sts_error;
2119: RETURN;
2120: END IF;
2121:
2122: END LOOP;
2153: WHERE custom_setup_id = p_request_type_setup_id;
2154:
2155: BEGIN
2156:
2157: x_return_status := FND_API.G_RET_STS_SUCCESS;
2158:
2159: IF G_DEBUG THEN
2160: OZF_UTILITY_PVT.debug_message('Inside INSERT HEADER RECORD Procedure ');
2161: END IF;
2168: FETCH c_reqest_header_seq INTO l_req_hdr_seq;
2169: CLOSE c_reqest_header_seq;
2170:
2171: --Generate Request Number, If Null
2172: IF l_SDR_hdr_rec.request_number = FND_API.g_miss_char OR l_SDR_hdr_rec.request_number IS NULL THEN
2173:
2174: OPEN c_code_prefix(p_SDR_hdr_rec.request_type_setup_id);
2175: FETCH c_code_prefix INTO l_code_prefix;
2176: CLOSE c_code_prefix;
2263:
2264: x_request_header_id := l_req_hdr_seq;
2265:
2266: EXCEPTION
2267: WHEN FND_API.G_EXC_ERROR THEN
2268: x_return_status := FND_API.G_RET_STS_ERROR;
2269: WHEN OTHERS THEN
2270: x_return_status := FND_API.g_ret_sts_unexp_error;
2271: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2264: x_request_header_id := l_req_hdr_seq;
2265:
2266: EXCEPTION
2267: WHEN FND_API.G_EXC_ERROR THEN
2268: x_return_status := FND_API.G_RET_STS_ERROR;
2269: WHEN OTHERS THEN
2270: x_return_status := FND_API.g_ret_sts_unexp_error;
2271: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2272: FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
2266: EXCEPTION
2267: WHEN FND_API.G_EXC_ERROR THEN
2268: x_return_status := FND_API.G_RET_STS_ERROR;
2269: WHEN OTHERS THEN
2270: x_return_status := FND_API.g_ret_sts_unexp_error;
2271: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2272: FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
2273: FND_MSG_PUB.add;
2274: END IF;
2288: x_return_status OUT NOCOPY VARCHAR2)
2289: IS
2290:
2291: BEGIN
2292: x_return_status := FND_API.G_RET_STS_SUCCESS;
2293:
2294: IF p_mode ='CREATE' OR p_mode ='COPY' THEN
2295:
2296: INSERT INTO ozf_sd_request_headers_all_tl
2343:
2344: END IF;
2345:
2346: EXCEPTION
2347: WHEN FND_API.G_EXC_ERROR THEN
2348: x_return_status := FND_API.G_RET_STS_ERROR;
2349: WHEN OTHERS THEN
2350: x_return_status := FND_API.g_ret_sts_unexp_error;
2351: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2344: END IF;
2345:
2346: EXCEPTION
2347: WHEN FND_API.G_EXC_ERROR THEN
2348: x_return_status := FND_API.G_RET_STS_ERROR;
2349: WHEN OTHERS THEN
2350: x_return_status := FND_API.g_ret_sts_unexp_error;
2351: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2352: FND_MESSAGE.set_name('OZF', 'OZF_SD_TL_POPULATION_ERROR');
2346: EXCEPTION
2347: WHEN FND_API.G_EXC_ERROR THEN
2348: x_return_status := FND_API.G_RET_STS_ERROR;
2349: WHEN OTHERS THEN
2350: x_return_status := FND_API.g_ret_sts_unexp_error;
2351: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2352: FND_MESSAGE.set_name('OZF', 'OZF_SD_TL_POPULATION_ERROR');
2353: FND_MSG_PUB.add;
2354: END IF;
2379: FROM dual;
2380:
2381: BEGIN
2382: -- Initialize API return status to sucess
2383: x_return_status := FND_API.G_RET_STS_SUCCESS;
2384:
2385: FOR p IN p_SDR_lines_tbl.FIRST..p_SDR_lines_tbl.LAST LOOP
2386:
2387: IF p_SDR_lines_tbl(p).request_line_id = FND_API.g_miss_num OR p_SDR_lines_tbl(p).request_line_id IS NULL THEN
2383: x_return_status := FND_API.G_RET_STS_SUCCESS;
2384:
2385: FOR p IN p_SDR_lines_tbl.FIRST..p_SDR_lines_tbl.LAST LOOP
2386:
2387: IF p_SDR_lines_tbl(p).request_line_id = FND_API.g_miss_num OR p_SDR_lines_tbl(p).request_line_id IS NULL THEN
2388:
2389: IF G_DEBUG THEN
2390: OZF_UTILITY_PVT.debug_message('populate_product_lines - Create Mode');
2391: OZF_UTILITY_PVT.debug_message('Count :'||p_SDR_lines_tbl.count);
2526: END IF;
2527: END LOOP;
2528:
2529: EXCEPTION
2530: WHEN FND_API.G_EXC_ERROR THEN
2531: x_return_status := FND_API.G_RET_STS_ERROR;
2532: WHEN OTHERS THEN
2533: x_return_status := FND_API.g_ret_sts_unexp_error;
2534: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2527: END LOOP;
2528:
2529: EXCEPTION
2530: WHEN FND_API.G_EXC_ERROR THEN
2531: x_return_status := FND_API.G_RET_STS_ERROR;
2532: WHEN OTHERS THEN
2533: x_return_status := FND_API.g_ret_sts_unexp_error;
2534: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2535: FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
2529: EXCEPTION
2530: WHEN FND_API.G_EXC_ERROR THEN
2531: x_return_status := FND_API.G_RET_STS_ERROR;
2532: WHEN OTHERS THEN
2533: x_return_status := FND_API.g_ret_sts_unexp_error;
2534: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2535: FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
2536: FND_MSG_PUB.add;
2537: END IF;
2572: AND end_customer_flag = p_end_customer_flag;
2573:
2574: BEGIN
2575: -- Initialize API return status to SUCCESS
2576: x_return_status := FND_API.G_RET_STS_SUCCESS;
2577:
2578: IF G_DEBUG THEN
2579: OZF_UTILITY_PVT.debug_message('Inside populate_customer_details');
2580: END IF;
2609: FND_MESSAGE.set_name('OZF', 'OZF_SD_REQ_DUP_CUSTOMER');
2610: END IF;
2611: FND_MSG_PUB.add;
2612: END IF;
2613: x_return_status := fnd_api.g_ret_sts_error;
2614: RETURN;
2615: END IF;
2616:
2617: IF p_SDR_cust_tbl(c).request_customer_id = FND_API.g_miss_num OR p_SDR_cust_tbl(c).request_customer_id IS NULL THEN
2613: x_return_status := fnd_api.g_ret_sts_error;
2614: RETURN;
2615: END IF;
2616:
2617: IF p_SDR_cust_tbl(c).request_customer_id = FND_API.g_miss_num OR p_SDR_cust_tbl(c).request_customer_id IS NULL THEN
2618: --//Create mode
2619: OPEN c_reqest_cust_seq;
2620: FETCH c_reqest_cust_seq INTO l_request_cust_seq;
2621: CLOSE c_reqest_cust_seq;
2684:
2685: END IF;
2686: END LOOP;
2687: EXCEPTION
2688: WHEN FND_API.G_EXC_ERROR THEN
2689: x_return_status := FND_API.G_RET_STS_ERROR;
2690: WHEN OTHERS THEN
2691: x_return_status := FND_API.g_ret_sts_unexp_error;
2692: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2685: END IF;
2686: END LOOP;
2687: EXCEPTION
2688: WHEN FND_API.G_EXC_ERROR THEN
2689: x_return_status := FND_API.G_RET_STS_ERROR;
2690: WHEN OTHERS THEN
2691: x_return_status := FND_API.g_ret_sts_unexp_error;
2692: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2693: FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
2687: EXCEPTION
2688: WHEN FND_API.G_EXC_ERROR THEN
2689: x_return_status := FND_API.G_RET_STS_ERROR;
2690: WHEN OTHERS THEN
2691: x_return_status := FND_API.g_ret_sts_unexp_error;
2692: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2693: FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
2694: FND_MSG_PUB.add;
2695: END IF;
2726: WHERE qp_list_header_id = p_qp_list_header_id;
2727: */
2728:
2729: BEGIN
2730: x_return_status := FND_API.G_RET_STS_SUCCESS;
2731:
2732: --//Update process
2733: IF G_DEBUG THEN
2734: OZF_UTILITY_PVT.debug_message('Invokes Update Row');
2816: OZF_UTILITY_PVT.debug_message('End update_header_record');
2817: END IF;
2818:
2819: EXCEPTION
2820: WHEN FND_API.G_EXC_ERROR THEN
2821: x_return_status := FND_API.G_RET_STS_ERROR;
2822: WHEN OTHERS THEN
2823: IF G_DEBUG THEN
2824: OZF_UTILITY_PVT.debug_message(SQLERRM);
2817: END IF;
2818:
2819: EXCEPTION
2820: WHEN FND_API.G_EXC_ERROR THEN
2821: x_return_status := FND_API.G_RET_STS_ERROR;
2822: WHEN OTHERS THEN
2823: IF G_DEBUG THEN
2824: OZF_UTILITY_PVT.debug_message(SQLERRM);
2825: END IF;
2822: WHEN OTHERS THEN
2823: IF G_DEBUG THEN
2824: OZF_UTILITY_PVT.debug_message(SQLERRM);
2825: END IF;
2826: x_return_status := FND_API.g_ret_sts_unexp_error;
2827: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2828: FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
2829: FND_MSG_PUB.add;
2830: END IF;
2839: ---------------------------------------------------------------------
2840:
2841: PROCEDURE create_sd_request(
2842: p_api_version_number IN NUMBER,
2843: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2844: p_commit IN VARCHAR2 := FND_API.G_FALSE,
2845: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2846: x_return_status OUT NOCOPY VARCHAR2,
2847: x_msg_count OUT NOCOPY NUMBER,
2840:
2841: PROCEDURE create_sd_request(
2842: p_api_version_number IN NUMBER,
2843: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2844: p_commit IN VARCHAR2 := FND_API.G_FALSE,
2845: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2846: x_return_status OUT NOCOPY VARCHAR2,
2847: x_msg_count OUT NOCOPY NUMBER,
2848: x_msg_data OUT NOCOPY VARCHAR2,
2841: PROCEDURE create_sd_request(
2842: p_api_version_number IN NUMBER,
2843: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2844: p_commit IN VARCHAR2 := FND_API.G_FALSE,
2845: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2846: x_return_status OUT NOCOPY VARCHAR2,
2847: x_msg_count OUT NOCOPY NUMBER,
2848: x_msg_data OUT NOCOPY VARCHAR2,
2849: p_SDR_hdr_rec IN SDR_Hdr_rec_type,
2884: BEGIN
2885: -- Standard Start of API savepoint
2886: SAVEPOINT CREATE_SDR_PUB;
2887: -- Standard call to check for call compatibility.
2888: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2889: p_api_version_number,
2890: l_api_name,
2891: G_PKG_NAME)
2892: THEN
2889: p_api_version_number,
2890: l_api_name,
2891: G_PKG_NAME)
2892: THEN
2893: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2894: END IF;
2895:
2896: -- Initialize message list if p_init_msg_list is set to TRUE.
2897: IF FND_API.to_Boolean( p_init_msg_list )
2893: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2894: END IF;
2895:
2896: -- Initialize message list if p_init_msg_list is set to TRUE.
2897: IF FND_API.to_Boolean( p_init_msg_list )
2898: THEN
2899: FND_MSG_PUB.initialize;
2900: END IF;
2901: -- Debug Message
2902: IF G_DEBUG THEN
2903: OZF_UTILITY_PVT.debug_message('Public API: ' || l_api_name || ' PUB start');
2904: END IF;
2905: -- Initialize API return status to SUCCESS
2906: x_return_status := FND_API.G_RET_STS_SUCCESS;
2907:
2908: --//API Body
2909: --========================================================================
2910: --//Validations
2909: --========================================================================
2910: --//Validations
2911:
2912: --//Accrual type Validation
2913: IF l_SDR_rec.accrual_type <> FND_API.g_miss_char AND l_SDR_rec.accrual_type IS NOT NULL THEN
2914:
2915: l_lookup_check :=OZF_UTILITY_PVT.check_lookup_exists(
2916: p_lookup_table_name =>'OZF_LOOKUPS'
2917: ,p_lookup_type =>'OZF_SDR_ACCRUAL_TYPE'
2916: p_lookup_table_name =>'OZF_LOOKUPS'
2917: ,p_lookup_type =>'OZF_SDR_ACCRUAL_TYPE'
2918: ,p_lookup_code => l_SDR_rec.accrual_type);
2919:
2920: IF l_lookup_check = FND_API.g_false THEN
2921: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2922: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ACCRUAL_TYPE');
2923: FND_MSG_PUB.add;
2924: END IF;
2921: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2922: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ACCRUAL_TYPE');
2923: FND_MSG_PUB.add;
2924: END IF;
2925: x_return_status := fnd_api.g_ret_sts_error;
2926: RETURN;
2927: END IF;
2928: ELSE
2929: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2930: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_ACCRUAL_TYPE');
2931: --//Accrual type is Mandatory
2932: FND_MSG_PUB.add;
2933: END IF;
2934: x_return_status := fnd_api.g_ret_sts_error;
2935: RETURN;
2936: END IF;
2937:
2938: --//Set default user status id
2935: RETURN;
2936: END IF;
2937:
2938: --//Set default user status id
2939: IF l_SDR_rec.user_status_id = FND_API.g_miss_num OR l_SDR_rec.user_status_id IS NULL THEN
2940: l_SDR_rec.user_status_id :=get_user_status_id('DRAFT');
2941: END IF;
2942: l_system_status_code := get_system_status_code(l_SDR_rec.user_status_id);
2943:
2946: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_USER_STATUS_ID');
2947: --//User status id entered is invalid
2948: FND_MSG_PUB.add;
2949: END IF;
2950: x_return_status := fnd_api.g_ret_sts_error;
2951: RETURN;
2952: END IF;
2953:
2954: --//Accrual type wise status check
2958: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CREATE_STATUS');
2959: --//User status id entered is invalid for Create
2960: FND_MSG_PUB.add;
2961: END IF;
2962: x_return_status := fnd_api.g_ret_sts_error;
2963: RETURN;
2964: END IF;
2965:
2966: ELSIF l_SDR_rec.accrual_type ='INTERNAL' THEN
2969: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CREATE_STATUS_I');
2970: --//User status id entered is invalid for Create
2971: FND_MSG_PUB.add;
2972: END IF;
2973: x_return_status := fnd_api.g_ret_sts_error;
2974: RETURN;
2975: END IF;
2976: END IF;
2977:
2985: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_PRODUCT_RECORDS');
2986: --//Product Line records are mandatory
2987: FND_MSG_PUB.add;
2988: END IF;
2989: x_return_status := fnd_api.g_ret_sts_error;
2990: RETURN;
2991: END IF;
2992: --//Request Number Validation
2993: IF l_SDR_rec.request_number <> FND_API.g_miss_char AND l_SDR_rec.request_number IS NOT NULL THEN
2989: x_return_status := fnd_api.g_ret_sts_error;
2990: RETURN;
2991: END IF;
2992: --//Request Number Validation
2993: IF l_SDR_rec.request_number <> FND_API.g_miss_char AND l_SDR_rec.request_number IS NOT NULL THEN
2994: OPEN c_request_number(l_SDR_rec.request_number);
2995: FETCH c_request_number INTO l_request_number;
2996: CLOSE c_request_number;
2997:
2999: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3000: FND_MESSAGE.set_name('OZF', 'OZF_SD_DUP_SOURCE_REQ_NO');
3001: FND_MSG_PUB.add;
3002: END IF;
3003: x_return_status := fnd_api.g_ret_sts_error;
3004: RETURN;
3005: END IF;
3006: END IF;
3007:
3005: END IF;
3006: END IF;
3007:
3008: --//Request type validation
3009: IF l_SDR_rec.request_type_setup_id <> FND_API.g_miss_num AND l_SDR_rec.request_type_setup_id IS NOT NULL THEN
3010: OPEN c_request_type_setup(l_SDR_rec.request_type_setup_id);
3011: FETCH c_request_type_setup INTO l_request_type_setup_id,l_request_type;
3012: CLOSE c_request_type_setup;
3013:
3016: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQUEST_TYPE_SETUP');
3017: --//Request type setup id is Mandatory
3018: FND_MSG_PUB.add;
3019: END IF;
3020: x_return_status := fnd_api.g_ret_sts_error;
3021: RETURN;
3022: ELSE
3023: IF ((l_request_type = 'BID')
3024: AND (l_system_status_code <> 'DRAFT')
3027: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3028: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_CUSTOMER_DETAILS');
3029: FND_MSG_PUB.add;
3030: END IF;
3031: x_return_status := fnd_api.g_ret_sts_error;
3032: RETURN;
3033: END IF;
3034: END IF;
3035: ELSE
3037: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_REQUEST_TYPE_SETUP');
3038: --//Request type setup id is Mandatory
3039: FND_MSG_PUB.add;
3040: END IF;
3041: x_return_status := fnd_api.g_ret_sts_error;
3042: RETURN;
3043: END IF;
3044:
3045: IF G_DEBUG THEN
3049: validate_header_items(p_SDR_hdr_rec => l_SDR_rec
3050: ,p_mode =>'CREATE'
3051: ,x_return_status => x_return_status);
3052:
3053: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3054: RAISE fnd_api.g_exc_unexpected_error;
3055: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3056: RAISE fnd_api.g_exc_error;
3057: END IF;
3050: ,p_mode =>'CREATE'
3051: ,x_return_status => x_return_status);
3052:
3053: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3054: RAISE fnd_api.g_exc_unexpected_error;
3055: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3056: RAISE fnd_api.g_exc_error;
3057: END IF;
3058:
3051: ,x_return_status => x_return_status);
3052:
3053: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3054: RAISE fnd_api.g_exc_unexpected_error;
3055: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3056: RAISE fnd_api.g_exc_error;
3057: END IF;
3058:
3059: --//Validate Product Lines
3052:
3053: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3054: RAISE fnd_api.g_exc_unexpected_error;
3055: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3056: RAISE fnd_api.g_exc_error;
3057: END IF;
3058:
3059: --//Validate Product Lines
3060: IF G_DEBUG THEN
3065: ,p_SDR_hdr_rec => l_SDR_rec
3066: ,p_mode => 'CREATE'
3067: ,x_return_status => x_return_status);
3068:
3069: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3070: RAISE fnd_api.g_exc_unexpected_error;
3071: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3072: RAISE fnd_api.g_exc_error;
3073: END IF;
3066: ,p_mode => 'CREATE'
3067: ,x_return_status => x_return_status);
3068:
3069: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3070: RAISE fnd_api.g_exc_unexpected_error;
3071: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3072: RAISE fnd_api.g_exc_error;
3073: END IF;
3074: l_line_rec_flag :='Y';
3067: ,x_return_status => x_return_status);
3068:
3069: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3070: RAISE fnd_api.g_exc_unexpected_error;
3071: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3072: RAISE fnd_api.g_exc_error;
3073: END IF;
3074: l_line_rec_flag :='Y';
3075: END IF;
3068:
3069: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3070: RAISE fnd_api.g_exc_unexpected_error;
3071: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3072: RAISE fnd_api.g_exc_error;
3073: END IF;
3074: l_line_rec_flag :='Y';
3075: END IF;
3076:
3082: validate_customer_items(p_SDR_cust_tbl => l_SDR_cust_tbl
3083: ,p_mode => 'CREATE'
3084: ,x_return_status => x_return_status);
3085:
3086: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3087: RAISE fnd_api.g_exc_unexpected_error;
3088: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3089: RAISE fnd_api.g_exc_error;
3090: END IF;
3083: ,p_mode => 'CREATE'
3084: ,x_return_status => x_return_status);
3085:
3086: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3087: RAISE fnd_api.g_exc_unexpected_error;
3088: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3089: RAISE fnd_api.g_exc_error;
3090: END IF;
3091: l_cust_rec_flag :='Y';
3084: ,x_return_status => x_return_status);
3085:
3086: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3087: RAISE fnd_api.g_exc_unexpected_error;
3088: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3089: RAISE fnd_api.g_exc_error;
3090: END IF;
3091: l_cust_rec_flag :='Y';
3092: END IF;
3085:
3086: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3087: RAISE fnd_api.g_exc_unexpected_error;
3088: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3089: RAISE fnd_api.g_exc_error;
3090: END IF;
3091: l_cust_rec_flag :='Y';
3092: END IF;
3093:
3100: ,p_request_source => 'API'
3101: ,x_request_header_id => x_request_header_id
3102: ,x_return_status => x_return_status);
3103:
3104: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3105: RAISE fnd_api.g_exc_unexpected_error;
3106: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3107: RAISE fnd_api.g_exc_error;
3108: END IF;
3101: ,x_request_header_id => x_request_header_id
3102: ,x_return_status => x_return_status);
3103:
3104: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3105: RAISE fnd_api.g_exc_unexpected_error;
3106: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3107: RAISE fnd_api.g_exc_error;
3108: END IF;
3109:
3102: ,x_return_status => x_return_status);
3103:
3104: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3105: RAISE fnd_api.g_exc_unexpected_error;
3106: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3107: RAISE fnd_api.g_exc_error;
3108: END IF;
3109:
3110: IF G_DEBUG THEN
3103:
3104: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3105: RAISE fnd_api.g_exc_unexpected_error;
3106: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3107: RAISE fnd_api.g_exc_error;
3108: END IF;
3109:
3110: IF G_DEBUG THEN
3111: OZF_UTILITY_PVT.debug_message('Populating Translation table');
3117: ,p_org_id =>l_SDR_rec.org_id
3118: ,p_mode =>'CREATE'
3119: ,x_return_status => x_return_status);
3120:
3121: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3122: RAISE fnd_api.g_exc_unexpected_error;
3123: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3124: RAISE fnd_api.g_exc_error;
3125: END IF;
3118: ,p_mode =>'CREATE'
3119: ,x_return_status => x_return_status);
3120:
3121: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3122: RAISE fnd_api.g_exc_unexpected_error;
3123: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3124: RAISE fnd_api.g_exc_error;
3125: END IF;
3126:
3119: ,x_return_status => x_return_status);
3120:
3121: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3122: RAISE fnd_api.g_exc_unexpected_error;
3123: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3124: RAISE fnd_api.g_exc_error;
3125: END IF;
3126:
3127: IF G_DEBUG THEN
3120:
3121: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3122: RAISE fnd_api.g_exc_unexpected_error;
3123: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3124: RAISE fnd_api.g_exc_error;
3125: END IF;
3126:
3127: IF G_DEBUG THEN
3128: OZF_UTILITY_PVT.debug_message('Populate SD Access table for Requestor');
3131: --Populate SD Access table
3132:
3133: OZF_APPROVAL_PVT.Add_SD_Access(
3134: p_api_version =>p_api_version_number
3135: ,p_init_msg_list =>FND_API.G_FALSE
3136: ,p_commit =>FND_API.G_FALSE
3137: ,p_validation_level =>p_validation_level
3138: ,p_request_header_id =>x_request_header_id
3139: ,p_user_id =>NULL
3132:
3133: OZF_APPROVAL_PVT.Add_SD_Access(
3134: p_api_version =>p_api_version_number
3135: ,p_init_msg_list =>FND_API.G_FALSE
3136: ,p_commit =>FND_API.G_FALSE
3137: ,p_validation_level =>p_validation_level
3138: ,p_request_header_id =>x_request_header_id
3139: ,p_user_id =>NULL
3140: ,p_resource_id =>l_SDR_rec.requestor_id
3146: ,x_msg_count =>x_msg_count
3147: ,x_msg_data =>x_msg_data);
3148:
3149:
3150: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3151: RAISE fnd_api.g_exc_unexpected_error;
3152: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3153: RAISE fnd_api.g_exc_error;
3154: END IF;
3147: ,x_msg_data =>x_msg_data);
3148:
3149:
3150: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3151: RAISE fnd_api.g_exc_unexpected_error;
3152: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3153: RAISE fnd_api.g_exc_error;
3154: END IF;
3155:
3148:
3149:
3150: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3151: RAISE fnd_api.g_exc_unexpected_error;
3152: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3153: RAISE fnd_api.g_exc_error;
3154: END IF;
3155:
3156: IF l_SDR_rec.assignee_resource_id IS NOT NULL THEN
3149:
3150: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3151: RAISE fnd_api.g_exc_unexpected_error;
3152: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3153: RAISE fnd_api.g_exc_error;
3154: END IF;
3155:
3156: IF l_SDR_rec.assignee_resource_id IS NOT NULL THEN
3157: IF G_DEBUG THEN
3160: END IF;
3161: --//Assignee Entry
3162: OZF_APPROVAL_PVT.Add_SD_Access(
3163: p_api_version =>p_api_version_number
3164: ,p_init_msg_list =>FND_API.G_FALSE
3165: ,p_commit =>FND_API.G_FALSE
3166: ,p_validation_level =>p_validation_level
3167: ,p_request_header_id =>x_request_header_id
3168: ,p_user_id =>NULL
3161: --//Assignee Entry
3162: OZF_APPROVAL_PVT.Add_SD_Access(
3163: p_api_version =>p_api_version_number
3164: ,p_init_msg_list =>FND_API.G_FALSE
3165: ,p_commit =>FND_API.G_FALSE
3166: ,p_validation_level =>p_validation_level
3167: ,p_request_header_id =>x_request_header_id
3168: ,p_user_id =>NULL
3169: ,p_resource_id =>l_SDR_rec.assignee_resource_id
3175: ,x_msg_count =>x_msg_count
3176: ,x_msg_data =>x_msg_data);
3177:
3178:
3179: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3180: RAISE fnd_api.g_exc_unexpected_error;
3181: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3182: RAISE fnd_api.g_exc_error;
3183: END IF;
3176: ,x_msg_data =>x_msg_data);
3177:
3178:
3179: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3180: RAISE fnd_api.g_exc_unexpected_error;
3181: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3182: RAISE fnd_api.g_exc_error;
3183: END IF;
3184: END IF;
3177:
3178:
3179: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3180: RAISE fnd_api.g_exc_unexpected_error;
3181: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3182: RAISE fnd_api.g_exc_error;
3183: END IF;
3184: END IF;
3185:
3178:
3179: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3180: RAISE fnd_api.g_exc_unexpected_error;
3181: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3182: RAISE fnd_api.g_exc_error;
3183: END IF;
3184: END IF;
3185:
3186:
3193: p_request_header_id => x_request_header_id
3194: ,p_SDR_lines_tbl => l_SDR_lines_tbl
3195: ,x_return_status => x_return_status);
3196:
3197: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3198: RAISE fnd_api.g_exc_unexpected_error;
3199: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3200: RAISE fnd_api.g_exc_error;
3201: END IF;
3194: ,p_SDR_lines_tbl => l_SDR_lines_tbl
3195: ,x_return_status => x_return_status);
3196:
3197: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3198: RAISE fnd_api.g_exc_unexpected_error;
3199: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3200: RAISE fnd_api.g_exc_error;
3201: END IF;
3202: END IF;
3195: ,x_return_status => x_return_status);
3196:
3197: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3198: RAISE fnd_api.g_exc_unexpected_error;
3199: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3200: RAISE fnd_api.g_exc_error;
3201: END IF;
3202: END IF;
3203:
3196:
3197: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3198: RAISE fnd_api.g_exc_unexpected_error;
3199: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3200: RAISE fnd_api.g_exc_error;
3201: END IF;
3202: END IF;
3203:
3204: IF ((l_cust_rec_flag ='Y') AND (l_request_type = 'BID' ))THEN
3211: p_request_header_id => x_request_header_id
3212: ,p_SDR_cust_tbl => l_SDR_cust_tbl
3213: ,x_return_status => x_return_status);
3214:
3215: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3216: RAISE fnd_api.g_exc_unexpected_error;
3217: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3218: RAISE fnd_api.g_exc_error;
3219: END IF;
3212: ,p_SDR_cust_tbl => l_SDR_cust_tbl
3213: ,x_return_status => x_return_status);
3214:
3215: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3216: RAISE fnd_api.g_exc_unexpected_error;
3217: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3218: RAISE fnd_api.g_exc_error;
3219: END IF;
3220: END IF;
3213: ,x_return_status => x_return_status);
3214:
3215: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3216: RAISE fnd_api.g_exc_unexpected_error;
3217: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3218: RAISE fnd_api.g_exc_error;
3219: END IF;
3220: END IF;
3221: --========================================================================
3214:
3215: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3216: RAISE fnd_api.g_exc_unexpected_error;
3217: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3218: RAISE fnd_api.g_exc_error;
3219: END IF;
3220: END IF;
3221: --========================================================================
3222: --// Commit the process
3223: IF G_DEBUG THEN
3224: OZF_UTILITY_PVT.debug_message('Request Header Id: '||x_request_header_id);
3225: OZF_UTILITY_PVT.debug_message('Public API: '|| l_api_name||' End');
3226: END IF;
3227: IF FND_API.to_Boolean( p_commit )
3228: THEN
3229: COMMIT WORK;
3230: END IF;
3231:
3229: COMMIT WORK;
3230: END IF;
3231:
3232: FND_MSG_PUB.Count_And_Get (
3233: p_encoded => FND_API.G_FALSE,
3234: p_count => x_msg_count,
3235: p_data => x_msg_data
3236: );
3237:
3235: p_data => x_msg_data
3236: );
3237:
3238: EXCEPTION
3239: WHEN FND_API.G_EXC_ERROR THEN
3240: ROLLBACK TO CREATE_SDR_PUB;
3241: x_return_status := FND_API.G_RET_STS_ERROR;
3242: -- Standard call to get message count and if count=1, get the message
3243: FND_MSG_PUB.Count_And_Get (
3237:
3238: EXCEPTION
3239: WHEN FND_API.G_EXC_ERROR THEN
3240: ROLLBACK TO CREATE_SDR_PUB;
3241: x_return_status := FND_API.G_RET_STS_ERROR;
3242: -- Standard call to get message count and if count=1, get the message
3243: FND_MSG_PUB.Count_And_Get (
3244: p_encoded => FND_API.G_FALSE,
3245: p_count => x_msg_count,
3240: ROLLBACK TO CREATE_SDR_PUB;
3241: x_return_status := FND_API.G_RET_STS_ERROR;
3242: -- Standard call to get message count and if count=1, get the message
3243: FND_MSG_PUB.Count_And_Get (
3244: p_encoded => FND_API.G_FALSE,
3245: p_count => x_msg_count,
3246: p_data => x_msg_data
3247: );
3248: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3244: p_encoded => FND_API.G_FALSE,
3245: p_count => x_msg_count,
3246: p_data => x_msg_data
3247: );
3248: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3249: ROLLBACK TO CREATE_SDR_PUB;
3250: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3251: -- Standard call to get message count and if count=1, get the message
3252: FND_MSG_PUB.Count_And_Get (
3246: p_data => x_msg_data
3247: );
3248: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3249: ROLLBACK TO CREATE_SDR_PUB;
3250: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3251: -- Standard call to get message count and if count=1, get the message
3252: FND_MSG_PUB.Count_And_Get (
3253: p_encoded => FND_API.G_FALSE,
3254: p_count => x_msg_count,
3249: ROLLBACK TO CREATE_SDR_PUB;
3250: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3251: -- Standard call to get message count and if count=1, get the message
3252: FND_MSG_PUB.Count_And_Get (
3253: p_encoded => FND_API.G_FALSE,
3254: p_count => x_msg_count,
3255: p_data => x_msg_data
3256: );
3257: WHEN OTHERS THEN
3255: p_data => x_msg_data
3256: );
3257: WHEN OTHERS THEN
3258: ROLLBACK TO CREATE_SDR_PUB;
3259: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3260: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3261: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3262: END IF;
3263: -- Standard call to get message count and if count=1, get the message
3261: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3262: END IF;
3263: -- Standard call to get message count and if count=1, get the message
3264: FND_MSG_PUB.Count_And_Get (
3265: p_encoded => FND_API.G_FALSE,
3266: p_count => x_msg_count,
3267: p_data => x_msg_data
3268: );
3269: End create_sd_request;
3275: -- Public API for updating Ship & Debit Request
3276: ---------------------------------------------------------------------
3277: PROCEDURE update_sd_request(
3278: p_api_version_number IN NUMBER,
3279: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3280: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3281: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
3282: x_return_status OUT NOCOPY VARCHAR2,
3283: x_msg_count OUT NOCOPY NUMBER,
3276: ---------------------------------------------------------------------
3277: PROCEDURE update_sd_request(
3278: p_api_version_number IN NUMBER,
3279: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3280: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3281: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
3282: x_return_status OUT NOCOPY VARCHAR2,
3283: x_msg_count OUT NOCOPY NUMBER,
3284: x_msg_data OUT NOCOPY VARCHAR2,
3277: PROCEDURE update_sd_request(
3278: p_api_version_number IN NUMBER,
3279: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3280: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3281: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
3282: x_return_status OUT NOCOPY VARCHAR2,
3283: x_msg_count OUT NOCOPY NUMBER,
3284: x_msg_data OUT NOCOPY VARCHAR2,
3285: p_SDR_hdr_rec IN SDR_Hdr_rec_type,
3439: BEGIN
3440: -- Standard Start of API savepoint
3441: SAVEPOINT UPDATE_SDR_PUB;
3442: -- Standard call to check for call compatibility.
3443: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3444: p_api_version_number,
3445: l_api_name,
3446: G_PKG_NAME)
3447: THEN
3444: p_api_version_number,
3445: l_api_name,
3446: G_PKG_NAME)
3447: THEN
3448: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3449: END IF;
3450:
3451: -- Initialize message list if p_init_msg_list is set to TRUE.
3452: IF FND_API.to_Boolean( p_init_msg_list )
3448: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3449: END IF;
3450:
3451: -- Initialize message list if p_init_msg_list is set to TRUE.
3452: IF FND_API.to_Boolean( p_init_msg_list )
3453: THEN
3454: FND_MSG_PUB.initialize;
3455: END IF;
3456: -- Debug Message
3457: IF G_DEBUG THEN
3458: OZF_UTILITY_PVT.debug_message('Public API: ' || l_api_name || ' PUB start');
3459: END IF;
3460: -- Initialize API return status to SUCCESS
3461: x_return_status := FND_API.G_RET_STS_SUCCESS;
3462: --==============================================================================
3463: IF l_new_sdr_hdr_rec.request_header_id = FND_API.g_miss_num OR l_new_sdr_hdr_rec.request_header_id IS NULL THEN
3464:
3465: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3459: END IF;
3460: -- Initialize API return status to SUCCESS
3461: x_return_status := FND_API.G_RET_STS_SUCCESS;
3462: --==============================================================================
3463: IF l_new_sdr_hdr_rec.request_header_id = FND_API.g_miss_num OR l_new_sdr_hdr_rec.request_header_id IS NULL THEN
3464:
3465: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3466: FND_MESSAGE.set_name('OZF', 'OZF_SD_REQUEST_HEADER_ID_NULL');
3467: FND_MSG_PUB.add;
3465: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3466: FND_MESSAGE.set_name('OZF', 'OZF_SD_REQUEST_HEADER_ID_NULL');
3467: FND_MSG_PUB.add;
3468: END IF;
3469: x_return_status := fnd_api.g_ret_sts_error;
3470: RETURN;
3471: ELSE
3472: OPEN c_request_header_id(l_new_sdr_hdr_rec.request_header_id);
3473: FETCH c_request_header_id INTO l_request_header_id;
3478: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQ_HEADER_ID');
3479: --//Request id is invalid. Please re-enter
3480: FND_MSG_PUB.add;
3481: END IF;
3482: x_return_status := fnd_api.g_ret_sts_error;
3483: RETURN;
3484: END IF;
3485: END IF;
3486: --//Set the request Header ID to Global var
3489: OZF_UTILITY_PVT.debug_message('G_REQUEST_HEADER_ID: ' ||G_REQUEST_HEADER_ID);
3490: END IF;
3491:
3492: --//Request Number Validation
3493: IF l_new_sdr_hdr_rec.request_number <> FND_API.g_miss_char AND l_new_sdr_hdr_rec.request_number IS NOT NULL THEN
3494: OPEN c_request_number(l_new_sdr_hdr_rec.request_number);
3495: FETCH c_request_number INTO l_request_number;
3496: CLOSE c_request_number;
3497:
3499: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3500: FND_MESSAGE.set_name('OZF', 'OZF_SD_DUP_SOURCE_REQ_NO');
3501: FND_MSG_PUB.add;
3502: END IF;
3503: x_return_status := fnd_api.g_ret_sts_error;
3504: RETURN;
3505: END IF;
3506: END IF;
3507:
3562: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3563: FND_MESSAGE.Set_Name('OZF', 'OZF_API_RESOURCE_LOCKED');
3564: FND_MSG_PUB.ADD;
3565: END IF;
3566: RAISE FND_API.G_EXC_ERROR;
3567: END IF;
3568:
3569: OPEN c_old_sdr_tl(l_new_sdr_hdr_rec.request_header_id);
3570: FETCH c_old_sdr_tl INTO l_old_sdr_hdr_rec.request_description;
3573: IF G_DEBUG THEN
3574: OZF_UTILITY_PVT.debug_message('Validating User');
3575: END IF;
3576: --//User Check
3577: IF l_new_sdr_hdr_rec.user_id <> FND_API.g_miss_num AND l_new_sdr_hdr_rec.user_id IS NOT NULL THEN
3578: OPEN c_user(l_new_sdr_hdr_rec.user_id);
3579: FETCH c_user INTO l_user_id;
3580: CLOSE c_user;
3581:
3584: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_USER_ID');
3585: --//User Id is invalid, Please re-enter
3586: FND_MSG_PUB.add;
3587: END IF;
3588: x_return_status := fnd_api.g_ret_sts_error;
3589: RETURN;
3590: ELSE --// Check if User is a valid resource or not
3591: OPEN c_resource_id(p_SDR_hdr_rec.user_id);
3592: FETCH c_resource_id INTO l_resource_id;
3596: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3597: FND_MESSAGE.set_name('OZF', 'OZF_SD_USER_IS_NOT_RESOURCE');
3598: FND_MSG_PUB.add;
3599: END IF;
3600: x_return_status := fnd_api.g_ret_sts_error;
3601: RETURN;
3602: END IF;
3603: END IF;
3604:
3607: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_USER_ID');
3608: --//User Id is Mandatory
3609: FND_MSG_PUB.add;
3610: END IF;
3611: x_return_status := fnd_api.g_ret_sts_error;
3612: RETURN;
3613: END IF;
3614:
3615: --//Admin check
3651: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_USER_PERMISSIONS');
3652: --//User has no previlage to update the record.
3653: FND_MSG_PUB.add;
3654: END IF;
3655: x_return_status := fnd_api.g_ret_sts_error;
3656: RETURN;
3657: END IF;
3658:
3659: --//Get the internal and external flags
3674: l_old_user_status_id :=NVL(l_old_sdr_hdr_rec.user_status_id,0);
3675: l_new_user_status_id :=NVL(l_new_sdr_hdr_rec.user_status_id,l_old_sdr_hdr_rec.user_status_id);
3676:
3677: --//Status Transition check
3678: IF l_new_sdr_hdr_rec.user_status_id <> FND_API.g_miss_num AND l_new_sdr_hdr_rec.user_status_id IS NOT NULL THEN
3679: l_old_status_code := get_system_status_code(l_old_sdr_hdr_rec.user_status_id);
3680: l_new_status_code := get_system_status_code(l_new_sdr_hdr_rec.user_status_id);
3681: ELSE
3682: l_old_status_code := get_system_status_code(l_old_sdr_hdr_rec.user_status_id);
3699: ,p_pm_flag =>l_approver_flag
3700: ,p_internal_flag =>l_internal_flag
3701: ,p_external_flag =>l_external_flag);
3702:
3703: IF l_is_stat_trns_allowed = FND_API.g_false THEN
3704: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3705: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_STATUS_TRANS');
3706: --//Status transition is invalid
3707: FND_MSG_PUB.add;
3705: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_STATUS_TRANS');
3706: --//Status transition is invalid
3707: FND_MSG_PUB.add;
3708: END IF;
3709: x_return_status := fnd_api.g_ret_sts_error;
3710: RETURN;
3711: END IF;
3712: l_old_sdr_hdr_rec.user_status_id := l_new_sdr_hdr_rec.user_status_id;
3713:
3764: l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3765: l_old_sdr_hdr_rec.request_basis :=NVL(l_new_sdr_hdr_rec.request_basis,l_old_sdr_hdr_rec.request_basis);
3766:
3767: --//Bugfix 7822442
3768: IF ((l_new_sdr_hdr_rec.supplier_contact_id = FND_API.g_miss_num OR l_new_sdr_hdr_rec.supplier_contact_id IS NULL)
3769: AND (l_new_sdr_hdr_rec.supplier_contact_name <> FND_API.g_miss_char AND l_new_sdr_hdr_rec.supplier_contact_name IS NOT NULL)) THEN
3770: l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
3771: l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
3772: ELSE
3765: l_old_sdr_hdr_rec.request_basis :=NVL(l_new_sdr_hdr_rec.request_basis,l_old_sdr_hdr_rec.request_basis);
3766:
3767: --//Bugfix 7822442
3768: IF ((l_new_sdr_hdr_rec.supplier_contact_id = FND_API.g_miss_num OR l_new_sdr_hdr_rec.supplier_contact_id IS NULL)
3769: AND (l_new_sdr_hdr_rec.supplier_contact_name <> FND_API.g_miss_char AND l_new_sdr_hdr_rec.supplier_contact_name IS NOT NULL)) THEN
3770: l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
3771: l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
3772: ELSE
3773: l_old_sdr_hdr_rec.supplier_contact_id :=NVL(l_new_sdr_hdr_rec.supplier_contact_id,l_old_sdr_hdr_rec.supplier_contact_id);
3838: l_old_sdr_hdr_rec.internal_order_number :=NVL(l_new_sdr_hdr_rec.internal_order_number,l_old_sdr_hdr_rec.internal_order_number);
3839: l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3840:
3841: --//Bugfix 7822442
3842: IF ((l_new_sdr_hdr_rec.supplier_contact_id = FND_API.g_miss_num OR l_new_sdr_hdr_rec.supplier_contact_id IS NULL)
3843: AND (l_new_sdr_hdr_rec.supplier_contact_name <> FND_API.g_miss_char AND l_new_sdr_hdr_rec.supplier_contact_name IS NOT NULL)) THEN
3844: l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
3845: l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
3846: ELSE
3839: l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3840:
3841: --//Bugfix 7822442
3842: IF ((l_new_sdr_hdr_rec.supplier_contact_id = FND_API.g_miss_num OR l_new_sdr_hdr_rec.supplier_contact_id IS NULL)
3843: AND (l_new_sdr_hdr_rec.supplier_contact_name <> FND_API.g_miss_char AND l_new_sdr_hdr_rec.supplier_contact_name IS NOT NULL)) THEN
3844: l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
3845: l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
3846: ELSE
3847: l_old_sdr_hdr_rec.supplier_contact_id :=NVL(l_new_sdr_hdr_rec.supplier_contact_id,l_old_sdr_hdr_rec.supplier_contact_id);
3870: l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3871: l_old_sdr_hdr_rec.request_basis :=NVL(l_new_sdr_hdr_rec.request_basis,l_old_sdr_hdr_rec.request_basis);
3872:
3873: --//Bugfix 7822442
3874: IF ((l_new_sdr_hdr_rec.supplier_contact_id = FND_API.g_miss_num OR l_new_sdr_hdr_rec.supplier_contact_id IS NULL)
3875: AND (l_new_sdr_hdr_rec.supplier_contact_name <> FND_API.g_miss_char AND l_new_sdr_hdr_rec.supplier_contact_name IS NOT NULL)) THEN
3876: l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
3877: l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
3878: ELSE
3871: l_old_sdr_hdr_rec.request_basis :=NVL(l_new_sdr_hdr_rec.request_basis,l_old_sdr_hdr_rec.request_basis);
3872:
3873: --//Bugfix 7822442
3874: IF ((l_new_sdr_hdr_rec.supplier_contact_id = FND_API.g_miss_num OR l_new_sdr_hdr_rec.supplier_contact_id IS NULL)
3875: AND (l_new_sdr_hdr_rec.supplier_contact_name <> FND_API.g_miss_char AND l_new_sdr_hdr_rec.supplier_contact_name IS NOT NULL)) THEN
3876: l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
3877: l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
3878: ELSE
3879: l_old_sdr_hdr_rec.supplier_contact_id :=NVL(l_new_sdr_hdr_rec.supplier_contact_id,l_old_sdr_hdr_rec.supplier_contact_id);
3903: l_old_sdr_hdr_rec.sales_order_currency :=NVL(l_new_sdr_hdr_rec.sales_order_currency,l_old_sdr_hdr_rec.sales_order_currency);
3904: l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3905:
3906: --//Bugfix 7822442
3907: IF ((l_new_sdr_hdr_rec.supplier_contact_id = FND_API.g_miss_num OR l_new_sdr_hdr_rec.supplier_contact_id IS NULL)
3908: AND (l_new_sdr_hdr_rec.supplier_contact_name <> FND_API.g_miss_char AND l_new_sdr_hdr_rec.supplier_contact_name IS NOT NULL)) THEN
3909: l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
3910: l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
3911: ELSE
3904: l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
3905:
3906: --//Bugfix 7822442
3907: IF ((l_new_sdr_hdr_rec.supplier_contact_id = FND_API.g_miss_num OR l_new_sdr_hdr_rec.supplier_contact_id IS NULL)
3908: AND (l_new_sdr_hdr_rec.supplier_contact_name <> FND_API.g_miss_char AND l_new_sdr_hdr_rec.supplier_contact_name IS NOT NULL)) THEN
3909: l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
3910: l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
3911: ELSE
3912: l_old_sdr_hdr_rec.supplier_contact_id :=NVL(l_new_sdr_hdr_rec.supplier_contact_id,l_old_sdr_hdr_rec.supplier_contact_id);
3986: END IF;
3987:
3988: OZF_APPROVAL_PVT.Add_SD_Access(
3989: p_api_version =>p_api_version_number
3990: ,p_init_msg_list =>FND_API.G_FALSE
3991: ,p_commit =>FND_API.G_FALSE
3992: ,p_validation_level =>p_validation_level
3993: ,p_request_header_id =>l_old_sdr_hdr_rec.request_header_id
3994: ,p_user_id =>NULL
3987:
3988: OZF_APPROVAL_PVT.Add_SD_Access(
3989: p_api_version =>p_api_version_number
3990: ,p_init_msg_list =>FND_API.G_FALSE
3991: ,p_commit =>FND_API.G_FALSE
3992: ,p_validation_level =>p_validation_level
3993: ,p_request_header_id =>l_old_sdr_hdr_rec.request_header_id
3994: ,p_user_id =>NULL
3995: ,p_resource_id =>l_new_sdr_hdr_rec.requestor_id
4000: ,x_return_status =>x_return_status
4001: ,x_msg_count =>x_msg_count
4002: ,x_msg_data =>x_msg_data);
4003:
4004: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4005: RAISE fnd_api.g_exc_unexpected_error;
4006: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4007: RAISE fnd_api.g_exc_error;
4008: END IF;
4001: ,x_msg_count =>x_msg_count
4002: ,x_msg_data =>x_msg_data);
4003:
4004: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4005: RAISE fnd_api.g_exc_unexpected_error;
4006: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4007: RAISE fnd_api.g_exc_error;
4008: END IF;
4009: l_old_sdr_hdr_rec.requestor_id :=l_new_sdr_hdr_rec.requestor_id;
4002: ,x_msg_data =>x_msg_data);
4003:
4004: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4005: RAISE fnd_api.g_exc_unexpected_error;
4006: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4007: RAISE fnd_api.g_exc_error;
4008: END IF;
4009: l_old_sdr_hdr_rec.requestor_id :=l_new_sdr_hdr_rec.requestor_id;
4010:
4003:
4004: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4005: RAISE fnd_api.g_exc_unexpected_error;
4006: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4007: RAISE fnd_api.g_exc_error;
4008: END IF;
4009: l_old_sdr_hdr_rec.requestor_id :=l_new_sdr_hdr_rec.requestor_id;
4010:
4011: END IF;
4018: END IF;
4019:
4020: OZF_APPROVAL_PVT.Add_SD_Access(
4021: p_api_version =>p_api_version_number
4022: ,p_init_msg_list =>FND_API.G_FALSE
4023: ,p_commit =>FND_API.G_FALSE
4024: ,p_validation_level =>p_validation_level
4025: ,p_request_header_id =>l_old_sdr_hdr_rec.request_header_id
4026: ,p_user_id =>NULL
4019:
4020: OZF_APPROVAL_PVT.Add_SD_Access(
4021: p_api_version =>p_api_version_number
4022: ,p_init_msg_list =>FND_API.G_FALSE
4023: ,p_commit =>FND_API.G_FALSE
4024: ,p_validation_level =>p_validation_level
4025: ,p_request_header_id =>l_old_sdr_hdr_rec.request_header_id
4026: ,p_user_id =>NULL
4027: ,p_resource_id =>l_new_sdr_hdr_rec.assignee_resource_id
4032: ,x_return_status =>x_return_status
4033: ,x_msg_count =>x_msg_count
4034: ,x_msg_data =>x_msg_data);
4035:
4036: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4037: RAISE fnd_api.g_exc_unexpected_error;
4038: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4039: RAISE fnd_api.g_exc_error;
4040: END IF;
4033: ,x_msg_count =>x_msg_count
4034: ,x_msg_data =>x_msg_data);
4035:
4036: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4037: RAISE fnd_api.g_exc_unexpected_error;
4038: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4039: RAISE fnd_api.g_exc_error;
4040: END IF;
4041: l_old_sdr_hdr_rec.assignee_resource_id :=l_new_sdr_hdr_rec.assignee_resource_id;
4034: ,x_msg_data =>x_msg_data);
4035:
4036: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4037: RAISE fnd_api.g_exc_unexpected_error;
4038: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4039: RAISE fnd_api.g_exc_error;
4040: END IF;
4041: l_old_sdr_hdr_rec.assignee_resource_id :=l_new_sdr_hdr_rec.assignee_resource_id;
4042: END IF;
4035:
4036: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4037: RAISE fnd_api.g_exc_unexpected_error;
4038: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4039: RAISE fnd_api.g_exc_error;
4040: END IF;
4041: l_old_sdr_hdr_rec.assignee_resource_id :=l_new_sdr_hdr_rec.assignee_resource_id;
4042: END IF;
4043: END IF;
4053: validate_header_items(p_SDR_hdr_rec => l_old_sdr_hdr_rec
4054: ,p_mode =>'UPDATE'
4055: ,x_return_status => x_return_status);
4056:
4057: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4058: RAISE fnd_api.g_exc_unexpected_error;
4059: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4060: RAISE fnd_api.g_exc_error;
4061: END IF;
4054: ,p_mode =>'UPDATE'
4055: ,x_return_status => x_return_status);
4056:
4057: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4058: RAISE fnd_api.g_exc_unexpected_error;
4059: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4060: RAISE fnd_api.g_exc_error;
4061: END IF;
4062:
4055: ,x_return_status => x_return_status);
4056:
4057: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4058: RAISE fnd_api.g_exc_unexpected_error;
4059: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4060: RAISE fnd_api.g_exc_error;
4061: END IF;
4062:
4063: --//Proc Lines & Customer details Validation
4056:
4057: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4058: RAISE fnd_api.g_exc_unexpected_error;
4059: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4060: RAISE fnd_api.g_exc_error;
4061: END IF;
4062:
4063: --//Proc Lines & Customer details Validation
4064: IF G_DEBUG THEN
4094: ,p_SDR_hdr_rec => l_old_sdr_hdr_rec
4095: ,p_mode => 'UPDATE'
4096: ,x_return_status => x_return_status);
4097:
4098: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4099: RAISE fnd_api.g_exc_unexpected_error;
4100: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4101: RAISE fnd_api.g_exc_error;
4102: END IF;
4095: ,p_mode => 'UPDATE'
4096: ,x_return_status => x_return_status);
4097:
4098: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4099: RAISE fnd_api.g_exc_unexpected_error;
4100: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4101: RAISE fnd_api.g_exc_error;
4102: END IF;
4103: l_line_rec_flag :='Y';
4096: ,x_return_status => x_return_status);
4097:
4098: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4099: RAISE fnd_api.g_exc_unexpected_error;
4100: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4101: RAISE fnd_api.g_exc_error;
4102: END IF;
4103: l_line_rec_flag :='Y';
4104: END IF;
4097:
4098: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4099: RAISE fnd_api.g_exc_unexpected_error;
4100: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4101: RAISE fnd_api.g_exc_error;
4102: END IF;
4103: l_line_rec_flag :='Y';
4104: END IF;
4105: END IF;
4116: validate_customer_items(p_SDR_cust_tbl => l_new_sdr_cust_tbl
4117: ,p_mode => 'UPDATE'
4118: ,x_return_status => x_return_status);
4119:
4120: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4121: RAISE fnd_api.g_exc_unexpected_error;
4122: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4123: RAISE fnd_api.g_exc_error;
4124: END IF;
4117: ,p_mode => 'UPDATE'
4118: ,x_return_status => x_return_status);
4119:
4120: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4121: RAISE fnd_api.g_exc_unexpected_error;
4122: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4123: RAISE fnd_api.g_exc_error;
4124: END IF;
4125: l_cust_rec_flag :='Y';
4118: ,x_return_status => x_return_status);
4119:
4120: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4121: RAISE fnd_api.g_exc_unexpected_error;
4122: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4123: RAISE fnd_api.g_exc_error;
4124: END IF;
4125: l_cust_rec_flag :='Y';
4126: END IF;
4119:
4120: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4121: RAISE fnd_api.g_exc_unexpected_error;
4122: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4123: RAISE fnd_api.g_exc_error;
4124: END IF;
4125: l_cust_rec_flag :='Y';
4126: END IF;
4127: END IF;
4135: update_header_record(
4136: p_SDR_hdr_rec =>l_old_sdr_hdr_rec
4137: ,x_return_status =>x_return_status);
4138:
4139: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4140: RAISE fnd_api.g_exc_unexpected_error;
4141: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4142: RAISE fnd_api.g_exc_error;
4143: END IF;
4136: p_SDR_hdr_rec =>l_old_sdr_hdr_rec
4137: ,x_return_status =>x_return_status);
4138:
4139: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4140: RAISE fnd_api.g_exc_unexpected_error;
4141: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4142: RAISE fnd_api.g_exc_error;
4143: END IF;
4144:
4137: ,x_return_status =>x_return_status);
4138:
4139: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4140: RAISE fnd_api.g_exc_unexpected_error;
4141: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4142: RAISE fnd_api.g_exc_error;
4143: END IF;
4144:
4145: IF G_DEBUG THEN
4138:
4139: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4140: RAISE fnd_api.g_exc_unexpected_error;
4141: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4142: RAISE fnd_api.g_exc_error;
4143: END IF;
4144:
4145: IF G_DEBUG THEN
4146: OZF_UTILITY_PVT.debug_message('populate_translation_record');
4152: ,p_org_id =>l_old_sdr_hdr_rec.org_id
4153: ,p_mode =>'UPDATE'
4154: ,x_return_status => x_return_status);
4155:
4156: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4157: RAISE fnd_api.g_exc_unexpected_error;
4158: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4159: RAISE fnd_api.g_exc_error;
4160: END IF;
4153: ,p_mode =>'UPDATE'
4154: ,x_return_status => x_return_status);
4155:
4156: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4157: RAISE fnd_api.g_exc_unexpected_error;
4158: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4159: RAISE fnd_api.g_exc_error;
4160: END IF;
4161:
4154: ,x_return_status => x_return_status);
4155:
4156: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4157: RAISE fnd_api.g_exc_unexpected_error;
4158: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4159: RAISE fnd_api.g_exc_error;
4160: END IF;
4161:
4162: --//Populating SD Access Table
4155:
4156: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4157: RAISE fnd_api.g_exc_unexpected_error;
4158: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4159: RAISE fnd_api.g_exc_error;
4160: END IF;
4161:
4162: --//Populating SD Access Table
4163: IF l_old_sdr_hdr_rec.accrual_type ='SUPPLIER'
4165: AND l_old_status_code <>l_new_status_code THEN
4166:
4167: OZF_APPROVAL_PVT.Add_SD_Access(
4168: p_api_version =>p_api_version_number
4169: ,p_init_msg_list =>FND_API.G_FALSE
4170: ,p_commit =>FND_API.G_FALSE
4171: ,p_validation_level =>p_validation_level
4172: ,p_request_header_id =>l_old_sdr_hdr_rec.request_header_id
4173: ,p_user_id =>NULL
4166:
4167: OZF_APPROVAL_PVT.Add_SD_Access(
4168: p_api_version =>p_api_version_number
4169: ,p_init_msg_list =>FND_API.G_FALSE
4170: ,p_commit =>FND_API.G_FALSE
4171: ,p_validation_level =>p_validation_level
4172: ,p_request_header_id =>l_old_sdr_hdr_rec.request_header_id
4173: ,p_user_id =>NULL
4174: ,p_resource_id =>l_old_sdr_hdr_rec.assignee_resource_id
4179: ,x_return_status =>x_return_status
4180: ,x_msg_count =>x_msg_count
4181: ,x_msg_data =>x_msg_data);
4182:
4183: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4184: RAISE fnd_api.g_exc_unexpected_error;
4185: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4186: RAISE fnd_api.g_exc_error;
4187: END IF;
4180: ,x_msg_count =>x_msg_count
4181: ,x_msg_data =>x_msg_data);
4182:
4183: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4184: RAISE fnd_api.g_exc_unexpected_error;
4185: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4186: RAISE fnd_api.g_exc_error;
4187: END IF;
4188: END IF;
4181: ,x_msg_data =>x_msg_data);
4182:
4183: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4184: RAISE fnd_api.g_exc_unexpected_error;
4185: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4186: RAISE fnd_api.g_exc_error;
4187: END IF;
4188: END IF;
4189:
4182:
4183: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4184: RAISE fnd_api.g_exc_unexpected_error;
4185: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4186: RAISE fnd_api.g_exc_error;
4187: END IF;
4188: END IF;
4189:
4190: --//Update Lines Record
4197: p_request_header_id => l_new_sdr_hdr_rec.request_header_id
4198: ,p_SDR_lines_tbl => l_new_sdr_lines_tbl
4199: ,x_return_status => x_return_status);
4200:
4201: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4202: RAISE fnd_api.g_exc_unexpected_error;
4203: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4204: RAISE fnd_api.g_exc_error;
4205: END IF;
4198: ,p_SDR_lines_tbl => l_new_sdr_lines_tbl
4199: ,x_return_status => x_return_status);
4200:
4201: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4202: RAISE fnd_api.g_exc_unexpected_error;
4203: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4204: RAISE fnd_api.g_exc_error;
4205: END IF;
4206: END IF;
4199: ,x_return_status => x_return_status);
4200:
4201: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4202: RAISE fnd_api.g_exc_unexpected_error;
4203: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4204: RAISE fnd_api.g_exc_error;
4205: END IF;
4206: END IF;
4207:
4200:
4201: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4202: RAISE fnd_api.g_exc_unexpected_error;
4203: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4204: RAISE fnd_api.g_exc_error;
4205: END IF;
4206: END IF;
4207:
4208: --//Update Customer Record
4214: p_request_header_id => l_new_sdr_hdr_rec.request_header_id
4215: ,p_SDR_cust_tbl => l_new_sdr_cust_tbl
4216: ,x_return_status => x_return_status);
4217:
4218: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4219: RAISE fnd_api.g_exc_unexpected_error;
4220: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4221: RAISE fnd_api.g_exc_error;
4222: END IF;
4215: ,p_SDR_cust_tbl => l_new_sdr_cust_tbl
4216: ,x_return_status => x_return_status);
4217:
4218: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4219: RAISE fnd_api.g_exc_unexpected_error;
4220: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4221: RAISE fnd_api.g_exc_error;
4222: END IF;
4223: END IF;
4216: ,x_return_status => x_return_status);
4217:
4218: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4219: RAISE fnd_api.g_exc_unexpected_error;
4220: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4221: RAISE fnd_api.g_exc_error;
4222: END IF;
4223: END IF;
4224:
4217:
4218: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4219: RAISE fnd_api.g_exc_unexpected_error;
4220: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4221: RAISE fnd_api.g_exc_error;
4222: END IF;
4223: END IF;
4224:
4225: --//Invoking Offer API Process
4244: END IF;
4245:
4246: OZF_OFFER_PVT.process_sd_modifiers(
4247: p_sdr_header_id => p_SDR_hdr_rec.request_header_id
4248: ,p_init_msg_list => FND_API.G_FALSE
4249: ,p_api_version => l_api_version_number
4250: ,p_commit => FND_API.G_FALSE
4251: ,x_return_status => x_return_status
4252: ,x_msg_count => x_msg_count
4246: OZF_OFFER_PVT.process_sd_modifiers(
4247: p_sdr_header_id => p_SDR_hdr_rec.request_header_id
4248: ,p_init_msg_list => FND_API.G_FALSE
4249: ,p_api_version => l_api_version_number
4250: ,p_commit => FND_API.G_FALSE
4251: ,x_return_status => x_return_status
4252: ,x_msg_count => x_msg_count
4253: ,x_msg_data => x_msg_data
4254: ,x_qp_list_header_id => l_qp_list_header_id
4266: offer_type = 'ACCRUAL'
4267: WHERE request_header_id = p_SDR_hdr_rec.request_header_id;
4268: END IF;
4269:
4270: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4271: RAISE fnd_api.g_exc_unexpected_error;
4272: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4273: RAISE fnd_api.g_exc_error;
4274: END IF;
4267: WHERE request_header_id = p_SDR_hdr_rec.request_header_id;
4268: END IF;
4269:
4270: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4271: RAISE fnd_api.g_exc_unexpected_error;
4272: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4273: RAISE fnd_api.g_exc_error;
4274: END IF;
4275: END IF;
4268: END IF;
4269:
4270: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4271: RAISE fnd_api.g_exc_unexpected_error;
4272: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4273: RAISE fnd_api.g_exc_error;
4274: END IF;
4275: END IF;
4276:
4269:
4270: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4271: RAISE fnd_api.g_exc_unexpected_error;
4272: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4273: RAISE fnd_api.g_exc_error;
4274: END IF;
4275: END IF;
4276:
4277:
4278: --// Commit the process
4279: IF G_DEBUG THEN
4280: OZF_UTILITY_PVT.debug_message('Public API: ' || l_api_name || ' End');
4281: END IF;
4282: IF FND_API.to_Boolean( p_commit )
4283: THEN
4284: COMMIT WORK;
4285: END IF;
4286:
4332:
4333: END IF;
4334:
4335: FND_MSG_PUB.Count_And_Get (
4336: p_encoded => FND_API.G_FALSE,
4337: p_count => x_msg_count,
4338: p_data => x_msg_data
4339: );
4340: --==============================================================================
4338: p_data => x_msg_data
4339: );
4340: --==============================================================================
4341: EXCEPTION
4342: WHEN FND_API.G_EXC_ERROR THEN
4343: ROLLBACK TO UPDATE_SDR_PUB;
4344: x_return_status := FND_API.G_RET_STS_ERROR;
4345: -- Standard call to get message count and if count=1, get the message
4346: FND_MSG_PUB.Count_And_Get (
4340: --==============================================================================
4341: EXCEPTION
4342: WHEN FND_API.G_EXC_ERROR THEN
4343: ROLLBACK TO UPDATE_SDR_PUB;
4344: x_return_status := FND_API.G_RET_STS_ERROR;
4345: -- Standard call to get message count and if count=1, get the message
4346: FND_MSG_PUB.Count_And_Get (
4347: p_encoded => FND_API.G_FALSE,
4348: p_count => x_msg_count,
4343: ROLLBACK TO UPDATE_SDR_PUB;
4344: x_return_status := FND_API.G_RET_STS_ERROR;
4345: -- Standard call to get message count and if count=1, get the message
4346: FND_MSG_PUB.Count_And_Get (
4347: p_encoded => FND_API.G_FALSE,
4348: p_count => x_msg_count,
4349: p_data => x_msg_data
4350: );
4351: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4347: p_encoded => FND_API.G_FALSE,
4348: p_count => x_msg_count,
4349: p_data => x_msg_data
4350: );
4351: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4352: ROLLBACK TO UPDATE_SDR_PUB;
4353: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4354: -- Standard call to get message count and if count=1, get the message
4355: FND_MSG_PUB.Count_And_Get (
4349: p_data => x_msg_data
4350: );
4351: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4352: ROLLBACK TO UPDATE_SDR_PUB;
4353: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4354: -- Standard call to get message count and if count=1, get the message
4355: FND_MSG_PUB.Count_And_Get (
4356: p_encoded => FND_API.G_FALSE,
4357: p_count => x_msg_count,
4352: ROLLBACK TO UPDATE_SDR_PUB;
4353: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4354: -- Standard call to get message count and if count=1, get the message
4355: FND_MSG_PUB.Count_And_Get (
4356: p_encoded => FND_API.G_FALSE,
4357: p_count => x_msg_count,
4358: p_data => x_msg_data
4359: );
4360: WHEN OTHERS THEN
4358: p_data => x_msg_data
4359: );
4360: WHEN OTHERS THEN
4361: ROLLBACK TO UPDATE_SDR_PUB;
4362: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4363: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4364: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4365: END IF;
4366: -- Standard call to get message count and if count=1, get the message
4364: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4365: END IF;
4366: -- Standard call to get message count and if count=1, get the message
4367: FND_MSG_PUB.Count_And_Get (
4368: p_encoded => FND_API.G_FALSE,
4369: p_count => x_msg_count,
4370: p_data => x_msg_data
4371: );
4372: END update_sd_request;
4379: -- Public API for Copying SDR
4380: ---------------------------------------------------------------------
4381: PROCEDURE copy_sd_request(
4382: p_api_version_number IN NUMBER,
4383: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4384: p_commit IN VARCHAR2 := FND_API.G_FALSE,
4385: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
4386: x_return_status OUT NOCOPY VARCHAR2,
4387: x_msg_count OUT NOCOPY NUMBER,
4380: ---------------------------------------------------------------------
4381: PROCEDURE copy_sd_request(
4382: p_api_version_number IN NUMBER,
4383: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4384: p_commit IN VARCHAR2 := FND_API.G_FALSE,
4385: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
4386: x_return_status OUT NOCOPY VARCHAR2,
4387: x_msg_count OUT NOCOPY NUMBER,
4388: x_msg_data OUT NOCOPY VARCHAR2,
4381: PROCEDURE copy_sd_request(
4382: p_api_version_number IN NUMBER,
4383: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4384: p_commit IN VARCHAR2 := FND_API.G_FALSE,
4385: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
4386: x_return_status OUT NOCOPY VARCHAR2,
4387: x_msg_count OUT NOCOPY NUMBER,
4388: x_msg_data OUT NOCOPY VARCHAR2,
4389: p_source_request_id IN VARCHAR2,
4588: BEGIN
4589: -- Standard Start of API savepoint
4590: SAVEPOINT COPY_SDR_PUB;
4591: -- Standard call to check for call compatibility.
4592: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4593: p_api_version_number,
4594: l_api_name,
4595: G_PKG_NAME)
4596: THEN
4593: p_api_version_number,
4594: l_api_name,
4595: G_PKG_NAME)
4596: THEN
4597: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4598: END IF;
4599:
4600: -- Initialize message list if p_init_msg_list is set to TRUE.
4601: IF FND_API.to_Boolean( p_init_msg_list )
4597: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4598: END IF;
4599:
4600: -- Initialize message list if p_init_msg_list is set to TRUE.
4601: IF FND_API.to_Boolean( p_init_msg_list )
4602: THEN
4603: FND_MSG_PUB.initialize;
4604: END IF;
4605: -- Debug Message
4606: IF G_DEBUG THEN
4607: OZF_UTILITY_PVT.debug_message('Public API: ' || l_api_name || ' pub start');
4608: END IF;
4609: -- Initialize API return status to SUCCESS
4610: x_return_status := FND_API.G_RET_STS_SUCCESS;
4611: --==============================================================================
4612: --//Bug 7190421 - User Check and population
4613:
4614: l_user_id := FND_GLOBAL.user_id;
4625: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4626: FND_MESSAGE.set_name('OZF', 'OZF_SD_USER_IS_NOT_RESOURCE');
4627: FND_MSG_PUB.add;
4628: END IF;
4629: x_return_status := fnd_api.g_ret_sts_error;
4630: RETURN;
4631: END IF;
4632:
4633: IF G_DEBUG THEN
4646: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SOURCE_REQ_ID');
4647: --//Invalid source request id. Please re-enter
4648: FND_MSG_PUB.add;
4649: END IF;
4650: x_return_status := fnd_api.g_ret_sts_error;
4651: RETURN;
4652: END IF;
4653: ELSE
4654: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4655: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_SOURCE_REQ_ID');
4656: --//Invalid source request id. Please re-enter
4657: FND_MSG_PUB.add;
4658: END IF;
4659: x_return_status := fnd_api.g_ret_sts_error;
4660: RETURN;
4661: END IF;
4662:
4663: --//Check If New Request Number already exists
4671: FND_MESSAGE.set_name('OZF', 'OZF_SD_DUP_SOURCE_REQ_NO');
4672: --//New request number entered is already exists.
4673: FND_MSG_PUB.add;
4674: END IF;
4675: x_return_status := fnd_api.g_ret_sts_error;
4676: RETURN;
4677: END IF;
4678: END IF;
4679: --//Accrual type Check
4682: p_lookup_table_name =>'OZF_LOOKUPS'
4683: ,p_lookup_type =>'OZF_SDR_ACCRUAL_TYPE'
4684: ,p_lookup_code => p_accrual_type);
4685:
4686: IF l_lookup_stat = FND_API.g_false THEN
4687: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4688: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ACCRUAL_TYPE');
4689: FND_MSG_PUB.add;
4690: END IF;
4687: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4688: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ACCRUAL_TYPE');
4689: FND_MSG_PUB.add;
4690: END IF;
4691: x_return_status := fnd_api.g_ret_sts_error;
4692: RETURN;
4693: END IF;
4694: ELSE
4695: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4696: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_ACCRUAL_TYPE');
4697: --//Accrual type is Mandatory
4698: FND_MSG_PUB.add;
4699: END IF;
4700: x_return_status := fnd_api.g_ret_sts_error;
4701: RETURN;
4702: END IF;
4703:
4704: --//Cust Account ID Validation
4708: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4709: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_CUST_ACCOUNT_ID');
4710: FND_MSG_PUB.add;
4711: END IF;
4712: x_return_status := fnd_api.g_ret_sts_error;
4713: RETURN;
4714: ELSE
4715: OPEN c_cust_account_id(p_cust_account_id);
4716: FETCH c_cust_account_id INTO l_cust_account_id;
4720: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4721: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_ACCOUNT_ID');
4722: FND_MSG_PUB.add;
4723: END IF;
4724: x_return_status := fnd_api.g_ret_sts_error;
4725: RETURN;
4726: END IF;
4727: END IF;
4728: END IF;
4770: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4771: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQUEST_SOURCE');
4772: FND_MSG_PUB.add;
4773: END IF;
4774: x_return_status := fnd_api.g_ret_sts_error;
4775: RETURN;
4776: END IF;
4777:
4778: --//Get the source Information
4837: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_END_DATE');
4838: --//End date should be greater than start date
4839: FND_MSG_PUB.add;
4840: END IF;
4841: x_return_status := fnd_api.g_ret_sts_error;
4842: RETURN;
4843: END IF;
4844:
4845: IF G_DEBUG THEN
4854: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_COPY_OPTION');
4855: --//Internal to Supplier Copy option is invalid!
4856: FND_MSG_PUB.add;
4857: END IF;
4858: x_return_status := fnd_api.g_ret_sts_error;
4859: RETURN;
4860:
4861: ELSIF ((l_sdr_source_rec.accrual_type ='SUPPLIER') AND (p_accrual_type ='INTERNAL')) OR p_accrual_type ='INTERNAL' THEN
4862: l_sdr_source_rec.accrual_type := p_accrual_type;
4892: ,p_request_source => p_request_source
4893: ,x_request_header_id => x_request_header_id
4894: ,x_return_status => x_return_status);
4895:
4896: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4897: RAISE fnd_api.g_exc_unexpected_error;
4898: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4899: RAISE fnd_api.g_exc_error;
4900: END IF;
4893: ,x_request_header_id => x_request_header_id
4894: ,x_return_status => x_return_status);
4895:
4896: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4897: RAISE fnd_api.g_exc_unexpected_error;
4898: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4899: RAISE fnd_api.g_exc_error;
4900: END IF;
4901:
4894: ,x_return_status => x_return_status);
4895:
4896: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4897: RAISE fnd_api.g_exc_unexpected_error;
4898: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4899: RAISE fnd_api.g_exc_error;
4900: END IF;
4901:
4902: --//Populate Translation table
4895:
4896: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4897: RAISE fnd_api.g_exc_unexpected_error;
4898: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4899: RAISE fnd_api.g_exc_error;
4900: END IF;
4901:
4902: --//Populate Translation table
4903: OPEN c_tl_description(p_source_request_id);
4917: IF G_DEBUG THEN
4918: OZF_UTILITY_PVT.debug_message('x_request_header_id '||x_request_header_id);
4919: END IF;
4920:
4921: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4922: RAISE fnd_api.g_exc_unexpected_error;
4923: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4924: RAISE fnd_api.g_exc_error;
4925: END IF;
4918: OZF_UTILITY_PVT.debug_message('x_request_header_id '||x_request_header_id);
4919: END IF;
4920:
4921: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4922: RAISE fnd_api.g_exc_unexpected_error;
4923: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4924: RAISE fnd_api.g_exc_error;
4925: END IF;
4926:
4919: END IF;
4920:
4921: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4922: RAISE fnd_api.g_exc_unexpected_error;
4923: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4924: RAISE fnd_api.g_exc_error;
4925: END IF;
4926:
4927: --//Populate SD Access table
4920:
4921: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4922: RAISE fnd_api.g_exc_unexpected_error;
4923: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4924: RAISE fnd_api.g_exc_error;
4925: END IF;
4926:
4927: --//Populate SD Access table
4928: --//For Owner
4928: --//For Owner
4929:
4930: OZF_APPROVAL_PVT.Add_SD_Access(
4931: p_api_version =>p_api_version_number
4932: ,p_init_msg_list =>FND_API.G_FALSE
4933: ,p_commit =>FND_API.G_FALSE
4934: ,p_validation_level =>p_validation_level
4935: ,p_request_header_id =>x_request_header_id
4936: ,p_user_id =>NULL
4929:
4930: OZF_APPROVAL_PVT.Add_SD_Access(
4931: p_api_version =>p_api_version_number
4932: ,p_init_msg_list =>FND_API.G_FALSE
4933: ,p_commit =>FND_API.G_FALSE
4934: ,p_validation_level =>p_validation_level
4935: ,p_request_header_id =>x_request_header_id
4936: ,p_user_id =>NULL
4937: ,p_resource_id =>l_sdr_source_rec.requestor_id
4942: ,x_return_status =>x_return_status
4943: ,x_msg_count =>x_msg_count
4944: ,x_msg_data =>x_msg_data);
4945:
4946: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4947: RAISE fnd_api.g_exc_unexpected_error;
4948: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4949: RAISE fnd_api.g_exc_error;
4950: END IF;
4943: ,x_msg_count =>x_msg_count
4944: ,x_msg_data =>x_msg_data);
4945:
4946: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4947: RAISE fnd_api.g_exc_unexpected_error;
4948: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4949: RAISE fnd_api.g_exc_error;
4950: END IF;
4951:
4944: ,x_msg_data =>x_msg_data);
4945:
4946: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4947: RAISE fnd_api.g_exc_unexpected_error;
4948: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4949: RAISE fnd_api.g_exc_error;
4950: END IF;
4951:
4952:
4945:
4946: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4947: RAISE fnd_api.g_exc_unexpected_error;
4948: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4949: RAISE fnd_api.g_exc_error;
4950: END IF;
4951:
4952:
4953: IF G_DEBUG THEN
5023: p_request_header_id => x_request_header_id
5024: ,p_SDR_lines_tbl => l_sdr_source_lines_tbl
5025: ,x_return_status => x_return_status);
5026:
5027: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5028: RAISE fnd_api.g_exc_unexpected_error;
5029: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5030: RAISE fnd_api.g_exc_error;
5031: END IF;
5024: ,p_SDR_lines_tbl => l_sdr_source_lines_tbl
5025: ,x_return_status => x_return_status);
5026:
5027: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5028: RAISE fnd_api.g_exc_unexpected_error;
5029: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5030: RAISE fnd_api.g_exc_error;
5031: END IF;
5032: END IF;
5025: ,x_return_status => x_return_status);
5026:
5027: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5028: RAISE fnd_api.g_exc_unexpected_error;
5029: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5030: RAISE fnd_api.g_exc_error;
5031: END IF;
5032: END IF;
5033:
5026:
5027: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5028: RAISE fnd_api.g_exc_unexpected_error;
5029: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5030: RAISE fnd_api.g_exc_error;
5031: END IF;
5032: END IF;
5033:
5034: --//Copy Customer
5071: ,p_SDR_cust_tbl => l_sdr_source_cust_tbl
5072: ,x_return_status => x_return_status);
5073:
5074:
5075: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5076: RAISE fnd_api.g_exc_unexpected_error;
5077: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5078: RAISE fnd_api.g_exc_error;
5079: END IF;
5072: ,x_return_status => x_return_status);
5073:
5074:
5075: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5076: RAISE fnd_api.g_exc_unexpected_error;
5077: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5078: RAISE fnd_api.g_exc_error;
5079: END IF;
5080:
5073:
5074:
5075: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5076: RAISE fnd_api.g_exc_unexpected_error;
5077: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5078: RAISE fnd_api.g_exc_error;
5079: END IF;
5080:
5081: END IF;
5074:
5075: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5076: RAISE fnd_api.g_exc_unexpected_error;
5077: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5078: RAISE fnd_api.g_exc_error;
5079: END IF;
5080:
5081: END IF;
5082:
5115: p_request_header_id => x_request_header_id
5116: ,p_SDR_cust_tbl => l_sdr_source_cust_tbl
5117: ,x_return_status => x_return_status);
5118:
5119: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5120: RAISE fnd_api.g_exc_unexpected_error;
5121: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5122: RAISE fnd_api.g_exc_error;
5123: END IF;
5116: ,p_SDR_cust_tbl => l_sdr_source_cust_tbl
5117: ,x_return_status => x_return_status);
5118:
5119: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5120: RAISE fnd_api.g_exc_unexpected_error;
5121: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5122: RAISE fnd_api.g_exc_error;
5123: END IF;
5124:
5117: ,x_return_status => x_return_status);
5118:
5119: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5120: RAISE fnd_api.g_exc_unexpected_error;
5121: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5122: RAISE fnd_api.g_exc_error;
5123: END IF;
5124:
5125: END IF;
5118:
5119: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5120: RAISE fnd_api.g_exc_unexpected_error;
5121: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5122: RAISE fnd_api.g_exc_error;
5123: END IF;
5124:
5125: END IF;
5126:
5129: OZF_UTILITY_PVT.debug_message('New request_header_id :'||x_request_header_id );
5130: OZF_UTILITY_PVT.debug_message('Return Status :'|| x_return_status );
5131: OZF_UTILITY_PVT.debug_message('Public API: ' || l_api_name || ' End..');
5132: END IF;
5133: IF FND_API.to_Boolean( p_commit )
5134: THEN
5135: COMMIT WORK;
5136: END IF;
5137:
5135: COMMIT WORK;
5136: END IF;
5137:
5138: FND_MSG_PUB.Count_And_Get (
5139: p_encoded => FND_API.G_FALSE,
5140: p_count => x_msg_count,
5141: p_data => x_msg_data
5142: );
5143:
5142: );
5143:
5144: --==============================================================================
5145: EXCEPTION
5146: WHEN FND_API.G_EXC_ERROR THEN
5147: ROLLBACK TO COPY_SDR_PUB;
5148: x_return_status := FND_API.G_RET_STS_ERROR;
5149: -- Standard call to get message count and if count=1, get the message
5150: FND_MSG_PUB.Count_And_Get (
5144: --==============================================================================
5145: EXCEPTION
5146: WHEN FND_API.G_EXC_ERROR THEN
5147: ROLLBACK TO COPY_SDR_PUB;
5148: x_return_status := FND_API.G_RET_STS_ERROR;
5149: -- Standard call to get message count and if count=1, get the message
5150: FND_MSG_PUB.Count_And_Get (
5151: p_encoded => FND_API.G_FALSE,
5152: p_count => x_msg_count,
5147: ROLLBACK TO COPY_SDR_PUB;
5148: x_return_status := FND_API.G_RET_STS_ERROR;
5149: -- Standard call to get message count and if count=1, get the message
5150: FND_MSG_PUB.Count_And_Get (
5151: p_encoded => FND_API.G_FALSE,
5152: p_count => x_msg_count,
5153: p_data => x_msg_data
5154: );
5155: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5151: p_encoded => FND_API.G_FALSE,
5152: p_count => x_msg_count,
5153: p_data => x_msg_data
5154: );
5155: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5156: ROLLBACK TO COPY_SDR_PUB;
5157: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5158: -- Standard call to get message count and if count=1, get the message
5159: FND_MSG_PUB.Count_And_Get (
5153: p_data => x_msg_data
5154: );
5155: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5156: ROLLBACK TO COPY_SDR_PUB;
5157: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5158: -- Standard call to get message count and if count=1, get the message
5159: FND_MSG_PUB.Count_And_Get (
5160: p_encoded => FND_API.G_FALSE,
5161: p_count => x_msg_count,
5156: ROLLBACK TO COPY_SDR_PUB;
5157: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5158: -- Standard call to get message count and if count=1, get the message
5159: FND_MSG_PUB.Count_And_Get (
5160: p_encoded => FND_API.G_FALSE,
5161: p_count => x_msg_count,
5162: p_data => x_msg_data
5163: );
5164: WHEN OTHERS THEN
5162: p_data => x_msg_data
5163: );
5164: WHEN OTHERS THEN
5165: ROLLBACK TO COPY_SDR_PUB;
5166: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5167: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5168: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
5169: END IF;
5170: -- Standard call to get message count and if count=1, get the message
5168: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
5169: END IF;
5170: -- Standard call to get message count and if count=1, get the message
5171: FND_MSG_PUB.Count_And_Get (
5172: p_encoded => FND_API.G_FALSE,
5173: p_count => x_msg_count,
5174: p_data => x_msg_data
5175: );
5176: END copy_sd_request;