[Home] [Help]
61: p_parameters => l_parameter_list);
62:
63: EXCEPTION
64: WHEN OTHERS THEN
65: RAISE Fnd_Api.g_exc_error;
66: IF G_DEBUG THEN
67: ozf_utility_pvt.debug_message('Exception in raising business event');
68: END IF;
69:
158: p_parameters => l_parameter_list);
159:
160: EXCEPTION
161: WHEN OTHERS THEN
162: RAISE Fnd_Api.g_exc_error;
163: IF G_DEBUG THEN
164: ozf_utility_pvt.debug_message('Exception in raising XML Gateway business event');
165: END IF;
166: END;
177: RETURN VARCHAR2
178: IS
179: BEGIN
180: IF p_value <= 0 THEN
181: RETURN FND_API.g_false;
182: ELSE
183: RETURN FND_API.g_true;
184: END IF;
185: END check_zero;
179: BEGIN
180: IF p_value <= 0 THEN
181: RETURN FND_API.g_false;
182: ELSE
183: RETURN FND_API.g_true;
184: END IF;
185: END check_zero;
186: -----------------------------------------------------------------------
187: -- FUNCTION
322: CLOSE c_external_transition;
323: END IF;
324:
325: IF l_owner_count = 0 AND l_pm_count = 0 AND l_owner_pm_count= 0 THEN
326: RETURN FND_API.g_false;
327: ELSE
328: RETURN FND_API.g_true;
329: END IF;
330:
324:
325: IF l_owner_count = 0 AND l_pm_count = 0 AND l_owner_pm_count= 0 THEN
326: RETURN FND_API.g_false;
327: ELSE
328: RETURN FND_API.g_true;
329: END IF;
330:
331: ELSIF p_internal_flag ='Y' THEN
332:
362: CLOSE c_internal_transition;
363: END IF;
364:
365: IF l_owner_count = 0 AND l_pm_count = 0 AND l_owner_pm_count= 0 THEN
366: RETURN FND_API.g_false;
367: ELSE
368: RETURN FND_API.g_true;
369: END IF;
370:
364:
365: IF l_owner_count = 0 AND l_pm_count = 0 AND l_owner_pm_count= 0 THEN
366: RETURN FND_API.g_false;
367: ELSE
368: RETURN FND_API.g_true;
369: END IF;
370:
371: END IF;
372:
689: WHERE order_number = p_internal_order_number
690: AND org_id = p_org_id;
691:
692: BEGIN
693: x_return_status := FND_API.G_RET_STS_SUCCESS;
694:
695: --// User ID validation
696: IF p_SDR_hdr_rec.user_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.user_id IS NOT NULL THEN
697: OPEN c_user(p_SDR_hdr_rec.user_id);
692: BEGIN
693: x_return_status := FND_API.G_RET_STS_SUCCESS;
694:
695: --// User ID validation
696: IF p_SDR_hdr_rec.user_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.user_id IS NOT NULL THEN
697: OPEN c_user(p_SDR_hdr_rec.user_id);
698: FETCH c_user INTO l_user_id;
699: CLOSE c_user;
700:
703: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_USER_ID');
704: --//User Id is invalid, Please re-enter
705: FND_MSG_PUB.add;
706: END IF;
707: x_return_status := fnd_api.g_ret_sts_error;
708: RETURN;
709: ELSE --// Check if User is a valid resource or not
710: OPEN c_resource_id(p_SDR_hdr_rec.user_id);
711: FETCH c_resource_id INTO l_resource_id;
715: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
716: FND_MESSAGE.set_name('OZF', 'OZF_SD_USER_IS_NOT_RESOURCE');
717: FND_MSG_PUB.add;
718: END IF;
719: x_return_status := fnd_api.g_ret_sts_error;
720: RETURN;
721: END IF;
722: IF g_debug THEN
723: OZF_UTILITY_PVT.debug_message('l_resource_id of the user :'||l_resource_id);
729: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_USER_ID');
730: --//User Id is Mandatory
731: FND_MSG_PUB.add;
732: END IF;
733: x_return_status := fnd_api.g_ret_sts_error;
734: RETURN;
735: END IF;
736:
737: --// Organization id Validation
734: RETURN;
735: END IF;
736:
737: --// Organization id Validation
738: IF p_SDR_hdr_rec.org_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.org_id IS NOT NULL THEN
739: OPEN c_org_id(p_SDR_hdr_rec.org_id);
740: FETCH c_org_id INTO l_org_id;
741: CLOSE c_org_id;
742:
745: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ORG_ID');
746: --//Organization id entered is invalid
747: FND_MSG_PUB.add;
748: END IF;
749: x_return_status := fnd_api.g_ret_sts_error;
750: RETURN;
751: END IF;
752: --//Set Org ID to Global Variable
753: -- G_ORG_ID := p_SDR_hdr_rec.org_id;
756: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_ORG_ID');
757: --//Organization id is Mandatory
758: FND_MSG_PUB.add;
759: END IF;
760: x_return_status := fnd_api.g_ret_sts_error;
761: RETURN;
762: END IF;
763:
764: --// Requestor ID Validation (owner)
761: RETURN;
762: END IF;
763:
764: --// Requestor ID Validation (owner)
765: IF p_SDR_hdr_rec.requestor_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.requestor_id IS NOT NULL THEN
766: IF G_DEBUG THEN
767: OZF_UTILITY_PVT.debug_message('Requestor NOT NULL');
768: END IF;
769: OPEN c_requestor_id(p_SDR_hdr_rec.requestor_id);
778: OZF_UTILITY_PVT.debug_message('Requestor id entered is invalid');
779: END IF;
780: FND_MSG_PUB.add;
781: END IF;
782: x_return_status := fnd_api.g_ret_sts_error;
783: RETURN;
784: END IF;
785: ELSE
786: p_SDR_hdr_rec.requestor_id := l_resource_id;
793: --//Supplier Validations
794: p_SDR_hdr_rec.cust_account_id := NULL;
795:
796: --//Supplier ID
797: IF p_SDR_hdr_rec.supplier_id <> FND_API.g_miss_num AND p_SDR_hdr_rec.supplier_id IS NOT NULL THEN
798: OPEN c_supp_id(p_SDR_hdr_rec.supplier_id);
799: FETCH c_supp_id INTO l_supplier_id;
800: CLOSE c_supp_id;
801:
804: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPPLIER_ID');
805: --//Supplier id entered is invalid
806: FND_MSG_PUB.add;
807: END IF;
808: x_return_status := fnd_api.g_ret_sts_error;
809: RETURN;
810: END IF;
811:
812: ELSE
814: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_SUPPLIER_ID');
815: --//Supplier id is Mandatory
816: FND_MSG_PUB.add;
817: END IF;
818: x_return_status := fnd_api.g_ret_sts_error;
819: RETURN;
820: END IF;
821:
822: --//Supplier site ID
819: RETURN;
820: END IF;
821:
822: --//Supplier site ID
823: 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
824: OPEN c_supplier_site_id(p_SDR_hdr_rec.supplier_site_id
825: ,p_SDR_hdr_rec.supplier_id
826: ,p_SDR_hdr_rec.org_id);
827:
833: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPP_SITE_ID');
834: --//Supplier site id entered is invalid
835: FND_MSG_PUB.add;
836: END IF;
837: x_return_status := fnd_api.g_ret_sts_error;
838: RETURN;
839: END IF;
840:
841: ELSE
843: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_SUPP_SITE_ID');
844: --//Supplier site id is Mandatory
845: FND_MSG_PUB.add;
846: END IF;
847: x_return_status := fnd_api.g_ret_sts_error;
848: RETURN;
849: END IF;
850:
851: --//Bugfix : 9748413
862: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_SUP_TRD_PRFL');
863: --//Supplier site id entered is invalid
864: FND_MSG_PUB.add;
865: END IF;
866: x_return_status := fnd_api.g_ret_sts_error;
867: RETURN;
868: END IF;
869:
870: --//Supplier Contact ID
868: END IF;
869:
870: --//Supplier Contact ID
871: --// Bugfix : 7822442
872: 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
873:
874: OPEN c_sup_contacts(p_SDR_hdr_rec.supplier_site_id
875: ,p_SDR_hdr_rec.supplier_contact_id);
876:
879:
880: IF l_supplier_contact_id IS NOT NULL THEN
881: p_SDR_hdr_rec.supplier_contact_name := l_sup_contact_full_name;
882: ELSE
883: IF p_SDR_hdr_rec.supplier_contact_name = FND_API.g_miss_char OR p_SDR_hdr_rec.supplier_contact_name IS NULL THEN
884: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
885: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPP_CONTACT_ID');
886: --//Supplier contact id entered is invalid
887: FND_MSG_PUB.add;
885: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPP_CONTACT_ID');
886: --//Supplier contact id entered is invalid
887: FND_MSG_PUB.add;
888: END IF;
889: x_return_status := fnd_api.g_ret_sts_error;
890: RETURN;
891: ELSE
892: p_SDR_hdr_rec.supplier_contact_id := NULL;
893: END IF;
894: END IF;
895: END IF;
896:
897: --Supplier Contact Info
898: 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
899: p_SDR_hdr_rec.supplier_contact_email_address :=l_supp_email;
900: END IF;
901: 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
902: p_SDR_hdr_rec.supplier_contact_phone_number :=l_supp_phone;
897: --Supplier Contact Info
898: 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
899: p_SDR_hdr_rec.supplier_contact_email_address :=l_supp_email;
900: END IF;
901: 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
902: p_SDR_hdr_rec.supplier_contact_phone_number :=l_supp_phone;
903: END IF;
904:
905: --supplier_response_by_date --ninarasi fix for bug 12837291
902: p_SDR_hdr_rec.supplier_contact_phone_number :=l_supp_phone;
903: END IF;
904:
905: --supplier_response_by_date --ninarasi fix for bug 12837291
906: /*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
907: p_SDR_hdr_rec.supplier_response_by_date := TRUNC(p_SDR_hdr_rec.supplier_response_by_date);
908:
909: IF p_SDR_hdr_rec.supplier_response_by_date NOT BETWEEN p_SDR_hdr_rec.request_start_date AND
910: p_SDR_hdr_rec.request_end_date THEN
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_SUPRESPB_DATE');
914: FND_MSG_PUB.add;
915: END IF;
916: x_return_status := fnd_api.g_ret_sts_error;
917: RETURN;
918: END IF;
919: END IF;*/
920:
918: END IF;
919: END IF;*/
920:
921: --supplier_response_date --ninarasi fix for bug 12837291
922: /* 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
923: p_SDR_hdr_rec.supplier_response_date := TRUNC(p_SDR_hdr_rec.supplier_response_date);
924:
925: IF p_SDR_hdr_rec.supplier_response_date NOT BETWEEN p_SDR_hdr_rec.request_start_date AND
926: p_SDR_hdr_rec.request_end_date THEN
928: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
929: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPRESP_DATE');
930: FND_MSG_PUB.add;
931: END IF;
932: x_return_status := fnd_api.g_ret_sts_error;
933: RETURN;
934: END IF;
935: END IF;*/
936:
934: END IF;
935: END IF;*/
936:
937: --supplier_submission_date --ninarasi fix for bug 12837291
938: /*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
939: p_SDR_hdr_rec.supplier_submission_date := TRUNC(p_SDR_hdr_rec.supplier_submission_date);
940:
941: IF p_SDR_hdr_rec.supplier_submission_date NOT BETWEEN p_SDR_hdr_rec.request_start_date AND
942: p_SDR_hdr_rec.request_end_date THEN
944: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
945: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SUPSUB_DATE');
946: FND_MSG_PUB.add;
947: END IF;
948: x_return_status := fnd_api.g_ret_sts_error;
949: RETURN;
950: END IF;
951: END IF;*/
952:
962: IF G_DEBUG THEN
963: OZF_UTILITY_PVT.debug_message('Assignee resource_id ID :'||p_SDR_hdr_rec.assignee_resource_id);
964: END IF;
965:
966: 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
967:
968: OPEN c_requestor_id(p_SDR_hdr_rec.assignee_resource_id);
969: FETCH c_requestor_id INTO l_assignee_resource_id;
970: CLOSE c_requestor_id;
974: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ASSIGNEE_ID');
975: --//Assignee id entered is invalid (Approver)
976: FND_MSG_PUB.add;
977: END IF;
978: x_return_status := fnd_api.g_ret_sts_error;
979: RETURN;
980: END IF;
981: ELSE
982: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
983: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_ASSIGNEE_ID');
984: --//Assignee resource id is Mandatory (Approver is mandatory)
985: FND_MSG_PUB.add;
986: END IF;
987: x_return_status := fnd_api.g_ret_sts_error;
988: RETURN;
989: END IF;
990:
991: --asignee_response_by_date --ninarasi fix for bug 12837291
988: RETURN;
989: END IF;
990:
991: --asignee_response_by_date --ninarasi fix for bug 12837291
992: /*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
993: p_SDR_hdr_rec.assignee_response_by_date := TRUNC(p_SDR_hdr_rec.assignee_response_by_date);
994:
995: IF p_SDR_hdr_rec.assignee_response_by_date NOT BETWEEN p_SDR_hdr_rec.request_start_date AND
996: p_SDR_hdr_rec.request_end_date THEN
998: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
999: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ASRESPB_DATE');
1000: FND_MSG_PUB.add;
1001: END IF;
1002: x_return_status := fnd_api.g_ret_sts_error;
1003: RETURN;
1004: END IF;
1005: END IF;*/
1006:
1004: END IF;
1005: END IF;*/
1006:
1007: --asignee_response_date --ninarasi fix for bug 12837291
1008: /*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
1009: p_SDR_hdr_rec.assignee_response_date := TRUNC(p_SDR_hdr_rec.assignee_response_date);
1010:
1011: IF p_SDR_hdr_rec.assignee_response_date NOT BETWEEN p_SDR_hdr_rec.request_start_date AND
1012: p_SDR_hdr_rec.request_end_date THEN
1014: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1015: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ASRESP_DATE');
1016: FND_MSG_PUB.add;
1017: END IF;
1018: x_return_status := fnd_api.g_ret_sts_error;
1019: RETURN;
1020: END IF;
1021: END IF;*/
1022: ELSE
1041: p_SDR_hdr_rec.assignee_response_by_date :=NULL;
1042: p_SDR_hdr_rec.assignee_response_date :=NULL;
1043:
1044:
1045: 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
1046:
1047: OPEN c_cust_account_id(p_SDR_hdr_rec.cust_account_id);
1048: FETCH c_cust_account_id INTO l_cust_account_id;
1049: CLOSE c_cust_account_id;
1053: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_ACCOUNT_ID');
1054: --//Cust Account ID entered is invalid
1055: FND_MSG_PUB.add;
1056: END IF;
1057: x_return_status := fnd_api.g_ret_sts_error;
1058: RETURN;
1059: END IF;
1060:
1061: ELSE
1063: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_CUST_ACCOUNT_ID');
1064: --//Cust Account id is Mandatory
1065: FND_MSG_PUB.add;
1066: END IF;
1067: x_return_status := fnd_api.g_ret_sts_error;
1068: RETURN;
1069: END IF;
1070: END IF;
1071:
1069: END IF;
1070: END IF;
1071:
1072: --//Bugfix : 7607795 - Start Date/End date Validations
1073: 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
1074:
1075: 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
1076: --//Generate End Date from Supplier trade profile
1077: OPEN c_authorization_period(p_SDR_hdr_rec.supplier_id
1071:
1072: --//Bugfix : 7607795 - Start Date/End date Validations
1073: 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
1074:
1075: 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
1076: --//Generate End Date from Supplier trade profile
1077: OPEN c_authorization_period(p_SDR_hdr_rec.supplier_id
1078: ,p_SDR_hdr_rec.supplier_site_id
1079: ,p_SDR_hdr_rec.org_id);
1090: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_END_DATE');
1091: --//End date should be greater than start date
1092: FND_MSG_PUB.add;
1093: END IF;
1094: x_return_status := fnd_api.g_ret_sts_error;
1095: RETURN;
1096: END IF;
1097: END IF;
1098:
1100: p_SDR_hdr_rec.request_start_date := TRUNC(p_SDR_hdr_rec.request_start_date);
1101: p_SDR_hdr_rec.request_end_date := TRUNC(p_SDR_hdr_rec.request_end_date);
1102:
1103: --Request Currency code
1104: 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
1105: OPEN c_currency (p_SDR_hdr_rec.request_currency_code);
1106: FETCH c_currency INTO l_currency_code;
1107: CLOSE c_currency;
1108:
1110: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1111: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CURRENCY_CODE');
1112: FND_MSG_PUB.add;
1113: END IF;
1114: x_return_status := fnd_api.g_ret_sts_error;
1115: RETURN;
1116: END IF;
1117: ELSE
1118: --//Get currency code from profile :JTF_PROFILE_DEFAULT_CURRENCY
1124: FND_MESSAGE.set_name('OZF', 'OZF_SD_ADD_PROFILE_CURRENCY');
1125: --//Please set default Currency in profile JTF_PROFILE_DEFAULT_CURRENCY
1126: FND_MSG_PUB.add;
1127: END IF;
1128: x_return_status := fnd_api.g_ret_sts_error;
1129: RETURN;
1130: END IF;
1131: END IF;
1132:
1130: END IF;
1131: END IF;
1132:
1133: --Request Outcome
1134: IF p_SDR_hdr_rec.request_outcome <> FND_API.g_miss_char AND p_SDR_hdr_rec.request_outcome IS NOT NULL THEN
1135: l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
1136: p_lookup_table_name =>'OZF_LOOKUPS'
1137: ,p_lookup_type =>'OZF_SD_REQUEST_OUTCOME'
1138: ,p_lookup_code => p_SDR_hdr_rec.request_outcome);
1136: p_lookup_table_name =>'OZF_LOOKUPS'
1137: ,p_lookup_type =>'OZF_SD_REQUEST_OUTCOME'
1138: ,p_lookup_code => p_SDR_hdr_rec.request_outcome);
1139:
1140: IF l_lookup_stat = FND_API.g_false THEN
1141: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1142: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQ_OUTCOME');
1143: FND_MSG_PUB.add;
1144: END IF;
1141: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1142: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQ_OUTCOME');
1143: FND_MSG_PUB.add;
1144: END IF;
1145: x_return_status := fnd_api.g_ret_sts_error;
1146: RETURN;
1147: END IF;
1148: END IF;
1149:
1147: END IF;
1148: END IF;
1149:
1150: --Internal Order Number
1151: 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
1152: OPEN c_order_no (p_SDR_hdr_rec.internal_order_number,p_SDR_hdr_rec.org_id);
1153: FETCH c_order_no INTO l_internal_order_number;
1154: CLOSE c_order_no;
1155:
1157: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1158: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ORDER_NUMBER');
1159: FND_MSG_PUB.add;
1160: END IF;
1161: x_return_status := fnd_api.g_ret_sts_error;
1162: RETURN;
1163: END IF;
1164: END IF;
1165:
1163: END IF;
1164: END IF;
1165:
1166: --Sales Order Currency
1167: 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
1168: OPEN c_currency (p_SDR_hdr_rec.sales_order_currency);
1169: FETCH c_currency INTO l_sales_order_currency;
1170: CLOSE c_currency;
1171:
1174: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SO_CURRENCY');
1175: --//Invalid Sales order Currency.
1176: FND_MSG_PUB.add;
1177: END IF;
1178: x_return_status := fnd_api.g_ret_sts_error;
1179: RETURN;
1180: END IF;
1181: END IF;
1182:
1180: END IF;
1181: END IF;
1182:
1183: --internal_submission_date --ninarasi fix for bug 12837291
1184: /*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
1185: p_SDR_hdr_rec.internal_submission_date := TRUNC(p_SDR_hdr_rec.internal_submission_date);
1186:
1187: IF p_SDR_hdr_rec.internal_submission_date NOT BETWEEN p_SDR_hdr_rec.request_start_date AND
1188: p_SDR_hdr_rec.request_end_date THEN
1190: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1191: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_INTSUB_DATE');
1192: FND_MSG_PUB.add;
1193: END IF;
1194: x_return_status := fnd_api.g_ret_sts_error;
1195: RETURN;
1196: END IF;
1197: END IF;*/
1198:
1419: WHERE request_line_id = p_request_line_id;
1420:
1421: BEGIN
1422:
1423: x_return_status := FND_API.G_RET_STS_SUCCESS;
1424:
1425: --//Get the master organization id
1426: OPEN c_master_org_id(p_SDR_hdr_rec.org_id);
1427: FETCH c_master_org_id INTO G_ITEM_ORG_ID;
1435: FOR i IN p_SDR_lines_tbl.FIRST..p_SDR_lines_tbl.LAST LOOP
1436: --//Update Mode
1437: IF p_mode ='UPDATE' THEN
1438:
1439: 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
1440: --Updating existing lines
1441: OPEN c_request_line_id(p_SDR_lines_tbl(i).request_line_id,p_SDR_hdr_rec.request_header_id);
1442: FETCH c_request_line_id INTO l_request_line_id;
1443: CLOSE c_request_line_id;
1447: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQUEST_LINE_ID');
1448: --//Request Line id is Invalid , Please Re-enter
1449: FND_MSG_PUB.add;
1450: END IF;
1451: x_return_status := fnd_api.g_ret_sts_error;
1452: RETURN;
1453: ELSIF p_SDR_lines_tbl(i).create_from <> 'ADJ' THEN
1454:
1455: --//Reset the values of new record from old record if NULL.
1559:
1560: --//Update Mode End
1561:
1562: --Product Context
1563: 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
1564:
1565: l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
1566: p_lookup_table_name =>'OZF_LOOKUPS'
1567: ,p_lookup_type =>'OZF_SD_PRODUCT_CONTEXT'
1566: p_lookup_table_name =>'OZF_LOOKUPS'
1567: ,p_lookup_type =>'OZF_SD_PRODUCT_CONTEXT'
1568: ,p_lookup_code => p_SDR_lines_tbl(i).product_context);
1569:
1570: IF l_lookup_stat = FND_API.g_false THEN
1571: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1572: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_PRODUCT_TYPE');
1573: FND_MSG_PUB.add;
1574: END IF;
1571: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1572: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_PRODUCT_TYPE');
1573: FND_MSG_PUB.add;
1574: END IF;
1575: x_return_status := fnd_api.g_ret_sts_error;
1576: RETURN;
1577: END IF;
1578: ELSE
1579: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1580: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_PRODUCT_CONTEXT');
1581: --//Product context is Mandatory
1582: FND_MSG_PUB.add;
1583: END IF;
1584: x_return_status := fnd_api.g_ret_sts_error;
1585: RETURN;
1586: END IF;
1587:
1588: --//PRODUCT
1591:
1592: p_SDR_lines_tbl(i).prod_catg_id := NULL;
1593: p_SDR_lines_tbl(i).product_cat_set_id := NULL;
1594:
1595: 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
1596:
1597: OPEN c_inventory_item_id(p_SDR_lines_tbl(i).inventory_item_id,G_ITEM_ORG_ID);
1598: FETCH c_inventory_item_id INTO l_inventory_item_id;
1599: CLOSE c_inventory_item_id;
1603: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_INVENTORY_ITEM_ID');
1604: --//Inventory item id is Invalid
1605: FND_MSG_PUB.add;
1606: END IF;
1607: x_return_status := fnd_api.g_ret_sts_error;
1608: RETURN;
1609: END IF;
1610: --//Populating Vendor Item code
1611: --// Bugfix 7372272 gmiss_num changed to gmiss_char
1608: RETURN;
1609: END IF;
1610: --//Populating Vendor Item code
1611: --// Bugfix 7372272 gmiss_num changed to gmiss_char
1612: 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
1613:
1614: 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);
1615: FETCH c_vendor_item_code INTO p_SDR_lines_tbl(i).vendor_item_code;
1616: CLOSE c_vendor_item_code;
1615: FETCH c_vendor_item_code INTO p_SDR_lines_tbl(i).vendor_item_code;
1616: CLOSE c_vendor_item_code;
1617: END IF;
1618:
1619: 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
1620:
1621: l_external_code := NULL;
1622: l_internal_code := NULL;
1623: OPEN c_ext_int_code(p_SDR_hdr_rec.supplier_id,p_SDR_hdr_rec.supplier_site_id,p_SDR_hdr_rec.org_id);
1628: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1629: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_VENDOR_ITEM_CODE');
1630: FND_MSG_PUB.add;
1631: END IF;
1632: x_return_status := fnd_api.g_ret_sts_error;
1633: RETURN;
1634: END IF;
1635: p_SDR_lines_tbl(i).inventory_item_id := l_internal_code;
1636:
1639: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_INVENTORY_ITEM_ID');
1640: --//Inventory item id is mandatory
1641: FND_MSG_PUB.add;
1642: END IF;
1643: x_return_status := fnd_api.g_ret_sts_error;
1644: RETURN;
1645: END IF;
1646:
1647: /* Bugfix : 12620229 - Commented UOM Mandatory check for PRODUCTS
1644: RETURN;
1645: END IF;
1646:
1647: /* Bugfix : 12620229 - Commented UOM Mandatory check for PRODUCTS
1648: 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
1649:
1650: OPEN c_item_uom(p_SDR_lines_tbl(i).item_uom
1651: ,p_SDR_lines_tbl(i).inventory_item_id
1652: ,G_ITEM_ORG_ID);
1658: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ITEM_UOM');
1659: --//Unit of Measurement is Invalid
1660: FND_MSG_PUB.add;
1661: END IF;
1662: x_return_status := fnd_api.g_ret_sts_error;
1663: RETURN;
1664: END IF;
1665: ELSE
1666: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1667: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_ITEM_UOM');
1668: --//Unit of Measurement is mandatory
1669: FND_MSG_PUB.add;
1670: END IF;
1671: x_return_status := fnd_api.g_ret_sts_error;
1672: RETURN;
1673: END IF;
1674: */
1675:
1673: END IF;
1674: */
1675:
1676: --//Product Cost Validation Bugfix: 7501046
1677: IF p_SDR_lines_tbl(i).product_cost = FND_API.g_miss_num OR p_SDR_lines_tbl(i).product_cost IS NULL THEN
1678: OPEN c_product_cost(p_SDR_lines_tbl(i).inventory_item_id,G_ITEM_ORG_ID);
1679: FETCH c_product_cost INTO p_SDR_lines_tbl(i).product_cost;
1680: CLOSE c_product_cost;
1681:
1684: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_PRODUCT_COST');
1685: --//A value must be entered for Product Cost
1686: FND_MSG_PUB.add;
1687: END IF;
1688: x_return_status := fnd_api.g_ret_sts_error;
1689: RETURN;
1690: END IF;
1691: END IF;
1692:
1690: END IF;
1691: END IF;
1692:
1693: --//Bugfix : 8511949 - Product Cost Currency Validation - If NULL is passed to API, get from Functional Currency, Else validate and process
1694: IF p_SDR_lines_tbl(i).product_cost_currency = FND_API.g_miss_char OR p_SDR_lines_tbl(i).product_cost_currency IS NULL THEN
1695:
1696: l_currency_var := NULL;
1697: OPEN c_func_currency(p_SDR_hdr_rec.org_id);
1698: FETCH c_func_currency INTO p_SDR_lines_tbl(i).product_cost_currency;
1706: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1707: FND_MESSAGE.set_name('OZF', 'OZF_INVALID_PROD_COST_CURR');
1708: FND_MSG_PUB.add;
1709: END IF;
1710: x_return_status := fnd_api.g_ret_sts_error;
1711: RETURN;
1712: END IF;
1713: END IF;
1714:
1722: p_SDR_lines_tbl(i).product_cost := NULL;
1723: p_SDR_lines_tbl(i).product_cost_currency := NULL;
1724:
1725: --//Category ID
1726: 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
1727:
1728: OPEN c_category_id(p_SDR_lines_tbl(i).prod_catg_id);
1729: FETCH c_category_id INTO l_category_id;
1730: CLOSE c_category_id;
1734: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_PRODUCT_CATEGORY');
1735: --//Product category is Invalid
1736: FND_MSG_PUB.add;
1737: END IF;
1738: x_return_status := fnd_api.g_ret_sts_error;
1739: RETURN;
1740: END IF;
1741: ELSE
1742: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1743: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_PRODUCT_CATEGORY');
1744: --//Product category is mandatory
1745: FND_MSG_PUB.add;
1746: END IF;
1747: x_return_status := fnd_api.g_ret_sts_error;
1748: RETURN;
1749: END IF;
1750:
1751: --//Category set id
1748: RETURN;
1749: END IF;
1750:
1751: --//Category set id
1752: 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
1753:
1754: OPEN c_category_set_id(p_SDR_lines_tbl(i).prod_catg_id
1755: ,p_SDR_lines_tbl(i).product_cat_set_id);
1756: FETCH c_category_set_id INTO l_category_set_id;
1761: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_PRODUCT_CATEGORY_SET');
1762: --//Product category set is Invalid
1763: FND_MSG_PUB.add;
1764: END IF;
1765: x_return_status := fnd_api.g_ret_sts_error;
1766: RETURN;
1767: END IF;
1768: ELSE --//get the Default category set
1769: OPEN c_get_category_set_id(p_SDR_lines_tbl(i).prod_catg_id);
1784:
1785: END IF;
1786: --//Bugfix : 12620229
1787: --//UOM Validation
1788: 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
1789:
1790: l_UOM_chk := validate_UOM(p_item_type => p_SDR_lines_tbl(i).product_context
1791: ,p_inventory_item_id => p_SDR_lines_tbl(i).inventory_item_id
1792: ,p_category_id => p_SDR_lines_tbl(i).prod_catg_id
1797: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ITEM_UOM');
1798: --//Unit of Measurement is Invalid
1799: FND_MSG_PUB.add;
1800: END IF;
1801: x_return_status := fnd_api.g_ret_sts_error;
1802: RETURN;
1803: END IF;
1804: END IF;
1805:
1805:
1806:
1807:
1808: --//Requested discount type
1809: 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
1810: l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
1811: p_lookup_table_name =>'OZF_LOOKUPS'
1812: ,p_lookup_type =>'OZF_SD_REQUEST_DISTYPE'
1813: ,p_lookup_code => p_SDR_lines_tbl(i).requested_discount_type);
1811: p_lookup_table_name =>'OZF_LOOKUPS'
1812: ,p_lookup_type =>'OZF_SD_REQUEST_DISTYPE'
1813: ,p_lookup_code => p_SDR_lines_tbl(i).requested_discount_type);
1814:
1815: IF l_lookup_stat = FND_API.g_false THEN
1816: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1817: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_DISCOUNT_TYPE');
1818: FND_MSG_PUB.add;
1819: END IF;
1816: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1817: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_DISCOUNT_TYPE');
1818: FND_MSG_PUB.add;
1819: END IF;
1820: x_return_status := fnd_api.g_ret_sts_error;
1821: RETURN;
1822: END IF;
1823: ELSE
1824: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1824: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1825: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_DISCOUNT_TYPE');
1826: FND_MSG_PUB.add;
1827: END IF;
1828: x_return_status := fnd_api.g_ret_sts_error;
1829: RETURN;
1830: END IF;
1831:
1832: --//Requested discount Value
1830: END IF;
1831:
1832: --//Requested discount Value
1833:
1834: 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
1835: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1836: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_DISC_VALUE');
1837: FND_MSG_PUB.add;
1838: END IF;
1835: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1836: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_DISC_VALUE');
1837: FND_MSG_PUB.add;
1838: END IF;
1839: x_return_status := fnd_api.g_ret_sts_error;
1840: RETURN;
1841: END IF;
1842: --//Bugfix : 12673769
1843: /*
1842: --//Bugfix : 12673769
1843: /*
1844: ELSE
1845: l_status := check_zero(p_SDR_lines_tbl(i).requested_discount_value);
1846: IF l_status = FND_API.g_false THEN
1847: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1848: FND_MESSAGE.set_name('OZF', 'OZF_SD_DISCOUNT_VALUE_IS_ZERO');
1849: --//Discount value should not be zero
1850: FND_MSG_PUB.add;
1848: FND_MESSAGE.set_name('OZF', 'OZF_SD_DISCOUNT_VALUE_IS_ZERO');
1849: --//Discount value should not be zero
1850: FND_MSG_PUB.add;
1851: END IF;
1852: x_return_status := fnd_api.g_ret_sts_error;
1853: RETURN;
1854: END IF;
1855: */
1856:
1858:
1859: --//Bugfix : 8511949 - Requested discount Currency Validation
1860: --//For Discount type other than % , if requested_discount_currency is NULL, default it to Header Request Currency, Else Validate and Process.
1861: IF p_SDR_lines_tbl(i).requested_discount_type <> '%' THEN
1862: IF p_SDR_lines_tbl(i).requested_discount_currency = FND_API.g_miss_char OR p_SDR_lines_tbl(i).requested_discount_currency IS NULL THEN
1863: p_SDR_lines_tbl(i).requested_discount_currency := p_SDR_hdr_rec.request_currency_code;
1864: ELSE
1865: l_currency_var := NULL;
1866: OPEN c_currency(p_SDR_lines_tbl(i).requested_discount_currency);
1871: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1872: FND_MESSAGE.set_name('OZF', 'OZF_INVALID_REQ_DISC_CURR');
1873: FND_MSG_PUB.add;
1874: END IF;
1875: x_return_status := fnd_api.g_ret_sts_error;
1876: RETURN;
1877: END IF;
1878: END IF;
1879: END IF;
1879: END IF;
1880:
1881: --//Populating Cost Basis
1882: --//Bugfix :8511949
1883: IF p_SDR_lines_tbl(i).cost_basis <> FND_API.g_miss_num AND p_SDR_lines_tbl(i).cost_basis IS NOT NULL THEN
1884:
1885: OPEN c_price_formula(p_SDR_lines_tbl(i).cost_basis);
1886: FETCH c_price_formula INTO l_formula_count;
1887: CLOSE c_price_formula;
1890: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1891: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_COST_BASIS');
1892: FND_MSG_PUB.add;
1893: END IF;
1894: x_return_status := fnd_api.g_ret_sts_error;
1895: RETURN;
1896: END IF;
1897: ELSE
1898: --//Default cost basis only in Line create Mode
1895: RETURN;
1896: END IF;
1897: ELSE
1898: --//Default cost basis only in Line create Mode
1899: IF p_SDR_lines_tbl(i).request_line_id = FND_API.g_miss_num OR p_SDR_lines_tbl(i).request_line_id IS NULL THEN
1900: OPEN c_cost_basis(p_SDR_hdr_rec.supplier_id
1901: ,p_SDR_hdr_rec.supplier_site_id
1902: ,p_SDR_hdr_rec.org_id);
1903: FETCH c_cost_basis INTO p_SDR_lines_tbl(i).cost_basis;
1905: END IF;
1906: END IF;
1907:
1908: --//Max Qty
1909: 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
1910: l_status := check_zero(p_SDR_lines_tbl(i).max_qty);
1911: IF l_status = FND_API.g_false THEN
1912: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1913: FND_MESSAGE.set_name('OZF', 'OZF_SD_MAX_QTY_ZERO_CHECK'); --//Check the message!
1907:
1908: --//Max Qty
1909: 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
1910: l_status := check_zero(p_SDR_lines_tbl(i).max_qty);
1911: IF l_status = FND_API.g_false THEN
1912: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1913: FND_MESSAGE.set_name('OZF', 'OZF_SD_MAX_QTY_ZERO_CHECK'); --//Check the message!
1914: --//Discount value should not be zero
1915: FND_MSG_PUB.add;
1913: FND_MESSAGE.set_name('OZF', 'OZF_SD_MAX_QTY_ZERO_CHECK'); --//Check the message!
1914: --//Discount value should not be zero
1915: FND_MSG_PUB.add;
1916: END IF;
1917: x_return_status := fnd_api.g_ret_sts_error;
1918: RETURN;
1919: END IF;
1920:
1921: --//Set limit Qty
1929:
1930: END IF;
1931:
1932: --//End Customer price
1933: 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
1934: l_status := check_zero(p_SDR_lines_tbl(i).end_customer_price);
1935: IF l_status = FND_API.g_false THEN
1936: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1937: FND_MESSAGE.set_name('OZF', 'OZF_SD_END_CUST_PRICE_VALUE_IS_ZERO');
1931:
1932: --//End Customer price
1933: 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
1934: l_status := check_zero(p_SDR_lines_tbl(i).end_customer_price);
1935: IF l_status = FND_API.g_false THEN
1936: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1937: FND_MESSAGE.set_name('OZF', 'OZF_SD_END_CUST_PRICE_VALUE_IS_ZERO');
1938: --//Discount value should not be zero
1939: FND_MSG_PUB.add;
1937: FND_MESSAGE.set_name('OZF', 'OZF_SD_END_CUST_PRICE_VALUE_IS_ZERO');
1938: --//Discount value should not be zero
1939: FND_MSG_PUB.add;
1940: END IF;
1941: x_return_status := fnd_api.g_ret_sts_error;
1942: RETURN;
1943: END IF;
1944: --//End Customer Currency. Bugfix : 8511949, Introduced the validation
1945: IF p_SDR_lines_tbl(i).end_customer_currency <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).end_customer_currency IS NOT NULL THEN
1941: x_return_status := fnd_api.g_ret_sts_error;
1942: RETURN;
1943: END IF;
1944: --//End Customer Currency. Bugfix : 8511949, Introduced the validation
1945: IF p_SDR_lines_tbl(i).end_customer_currency <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).end_customer_currency IS NOT NULL THEN
1946:
1947: l_currency_var := NULL;
1948: OPEN c_currency(p_SDR_lines_tbl(i).end_customer_currency);
1949: FETCH c_currency INTO l_currency_var;
1953: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1954: FND_MESSAGE.set_name('OZF', 'OZF_INVALID_END_CUST_CURR');
1955: FND_MSG_PUB.add;
1956: END IF;
1957: x_return_status := fnd_api.g_ret_sts_error;
1958: RETURN;
1959: END IF;
1960: ELSE
1961: p_SDR_lines_tbl(i).end_customer_currency := p_SDR_hdr_rec.request_currency_code;
1970:
1971:
1972:
1973: --Vendor Approved Flag
1974: --//Bugfix : 9791659 - Changed <> to = in condition vendor_approved_flag = FND_API.g_miss_char
1975: 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
1976: p_SDR_lines_tbl(i).vendor_approved_flag := 'Y';
1977: ELSE
1978: IF p_SDR_lines_tbl(i).vendor_approved_flag NOT IN ('Y','N')THEN
1971:
1972:
1973: --Vendor Approved Flag
1974: --//Bugfix : 9791659 - Changed <> to = in condition vendor_approved_flag = FND_API.g_miss_char
1975: 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
1976: p_SDR_lines_tbl(i).vendor_approved_flag := 'Y';
1977: ELSE
1978: IF p_SDR_lines_tbl(i).vendor_approved_flag NOT IN ('Y','N')THEN
1979: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1979: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1980: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_VENDOR_APPR_FLAG');
1981: FND_MSG_PUB.add;
1982: END IF;
1983: x_return_status := fnd_api.g_ret_sts_error;
1984: RETURN;
1985: END IF;
1986: END IF;
1987: --//Bugfix : 7607795
1985: END IF;
1986: END IF;
1987: --//Bugfix : 7607795
1988: IF p_mode ='CREATE' THEN
1989: IF p_SDR_lines_tbl(i).start_date = FND_API.g_miss_date OR p_SDR_lines_tbl(i).start_date IS NULL THEN
1990: p_SDR_lines_tbl(i).start_date :=p_SDR_hdr_rec.request_start_date;
1991:
1992: ELSIF TRUNC(p_SDR_lines_tbl(i).start_date) NOT BETWEEN
1993: TRUNC(p_SDR_hdr_rec.request_start_date) AND TRUNC(p_SDR_hdr_rec.request_end_date) THEN
1996: FND_MESSAGE.set_name('OZF', 'OZF_SD_LINE_START_DATE_CHECK');
1997: --//Start Date should fall in between Header Start date and End Date
1998: FND_MSG_PUB.add;
1999: END IF;
2000: x_return_status := fnd_api.g_ret_sts_error;
2001: RETURN;
2002: END IF;
2003:
2004: --End Date
2001: RETURN;
2002: END IF;
2003:
2004: --End Date
2005: IF p_SDR_lines_tbl(i).end_date = FND_API.g_miss_date OR p_SDR_lines_tbl(i).end_date IS NULL THEN
2006: p_SDR_lines_tbl(i).end_date :=p_SDR_hdr_rec.request_end_date;
2007:
2008: ELSIF TRUNC(p_SDR_lines_tbl(i).end_date) NOT BETWEEN
2009: TRUNC(p_SDR_hdr_rec.request_start_date) AND TRUNC(p_SDR_hdr_rec.request_end_date) THEN
2012: FND_MESSAGE.set_name('OZF', 'OZF_SD_LINE_END_DATE_CHECK');
2013: --//End Date should fall in between Header Start date and End Date
2014: FND_MSG_PUB.add;
2015: END IF;
2016: x_return_status := fnd_api.g_ret_sts_error;
2017: RETURN;
2018: END IF;
2019: END IF;
2020:
2029: FND_MESSAGE.set_name('OZF', 'OZF_SD_LINE_END_DATE_GT_CHECK');
2030: --//End Date should be greater than start date
2031: FND_MSG_PUB.add;
2032: END IF;
2033: x_return_status := fnd_api.g_ret_sts_error;
2034: RETURN;
2035: END IF;
2036:
2037: p_SDR_lines_tbl(i).start_date := TRUNC(p_SDR_lines_tbl(i).start_date);
2037: p_SDR_lines_tbl(i).start_date := TRUNC(p_SDR_lines_tbl(i).start_date);
2038: p_SDR_lines_tbl(i).end_date := TRUNC(p_SDR_lines_tbl(i).end_date);
2039:
2040: --//Price Type
2041: 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
2042: l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
2043: p_lookup_table_name =>'OZF_LOOKUPS'
2044: ,p_lookup_type =>'OZF_SD_PRICE_TYPE'
2045: ,p_lookup_code => p_SDR_lines_tbl(i).end_customer_price_type);
2043: p_lookup_table_name =>'OZF_LOOKUPS'
2044: ,p_lookup_type =>'OZF_SD_PRICE_TYPE'
2045: ,p_lookup_code => p_SDR_lines_tbl(i).end_customer_price_type);
2046:
2047: IF l_lookup_stat = FND_API.g_false THEN
2048: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2049: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_PRICE_TYPE');
2050: FND_MSG_PUB.add;
2051: END IF;
2048: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2049: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_PRICE_TYPE');
2050: FND_MSG_PUB.add;
2051: END IF;
2052: x_return_status := fnd_api.g_ret_sts_error;
2053: RETURN;
2054: END IF;
2055: END IF;
2056:
2054: END IF;
2055: END IF;
2056:
2057: --Tolerance Type
2058: 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
2059: l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
2060: p_lookup_table_name =>'OZF_LOOKUPS'
2061: ,p_lookup_type =>'OZF_SD_TOLERANCE_TYPE'
2062: ,p_lookup_code => p_SDR_lines_tbl(i).end_customer_tolerance_type);
2060: p_lookup_table_name =>'OZF_LOOKUPS'
2061: ,p_lookup_type =>'OZF_SD_TOLERANCE_TYPE'
2062: ,p_lookup_code => p_SDR_lines_tbl(i).end_customer_tolerance_type);
2063:
2064: IF l_lookup_stat = FND_API.g_false THEN
2065: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2066: FND_MESSAGE.set_name('OZF', 'OZF_SD_BAD_TOLERANCE_TYPE');
2067: FND_MSG_PUB.add;
2068: END IF;
2065: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2066: FND_MESSAGE.set_name('OZF', 'OZF_SD_BAD_TOLERANCE_TYPE');
2067: FND_MSG_PUB.add;
2068: END IF;
2069: x_return_status := fnd_api.g_ret_sts_error;
2070: RETURN;
2071: END IF;
2072: END IF;
2073:
2071: END IF;
2072: END IF;
2073:
2074: --//Rejection Code
2075: 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
2076: l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
2077: p_lookup_table_name =>'OZF_LOOKUPS'
2078: ,p_lookup_type =>'OZF_SD_REQ_LINE_REJECT_CODE'
2079: ,p_lookup_code => p_SDR_lines_tbl(i).rejection_code);
2077: p_lookup_table_name =>'OZF_LOOKUPS'
2078: ,p_lookup_type =>'OZF_SD_REQ_LINE_REJECT_CODE'
2079: ,p_lookup_code => p_SDR_lines_tbl(i).rejection_code);
2080:
2081: IF l_lookup_stat = FND_API.g_false THEN
2082: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2083: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REJECTION_CODE');
2084: FND_MSG_PUB.add;
2085: END IF;
2082: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2083: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REJECTION_CODE');
2084: FND_MSG_PUB.add;
2085: END IF;
2086: x_return_status := fnd_api.g_ret_sts_error;
2087: RETURN;
2088: END IF;
2089: END IF;
2090: l_status_code := get_system_status_code(p_SDR_hdr_rec.user_status_id);
2103: OZF_UTILITY_PVT.debug_message('Validate Product Lines Status Code INSIDE');
2104: END IF;
2105:
2106: --//Approved discount type
2107: 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
2108: l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
2109: p_lookup_table_name =>'OZF_LOOKUPS'
2110: ,p_lookup_type =>'OZF_SP_REQUEST_DISTYPE'
2111: ,p_lookup_code => p_SDR_lines_tbl(i).approved_discount_type);
2109: p_lookup_table_name =>'OZF_LOOKUPS'
2110: ,p_lookup_type =>'OZF_SP_REQUEST_DISTYPE'
2111: ,p_lookup_code => p_SDR_lines_tbl(i).approved_discount_type);
2112:
2113: IF l_lookup_stat =FND_API.g_false THEN
2114: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2115: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_APPROVED_DISC_TYPE');
2116: FND_MSG_PUB.add;
2117: END IF;
2114: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2115: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_APPROVED_DISC_TYPE');
2116: FND_MSG_PUB.add;
2117: END IF;
2118: x_return_status := fnd_api.g_ret_sts_error;
2119: RETURN;
2120: END IF;
2121: ELSE
2122: p_SDR_lines_tbl(i).approved_discount_type := p_SDR_lines_tbl(i).requested_discount_type;
2124:
2125: --//Bugfix : 12673769
2126: /*
2127: --//Approved discount Value
2128: 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
2129: l_status := check_zero(p_SDR_lines_tbl(i).approved_discount_value);
2130: IF l_status = FND_API.g_false THEN
2131: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2132: FND_MESSAGE.set_name('OZF', 'OZF_SD_APPR_DISC_VALUE_IS_ZERO');
2126: /*
2127: --//Approved discount Value
2128: 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
2129: l_status := check_zero(p_SDR_lines_tbl(i).approved_discount_value);
2130: IF l_status = FND_API.g_false THEN
2131: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2132: FND_MESSAGE.set_name('OZF', 'OZF_SD_APPR_DISC_VALUE_IS_ZERO');
2133: FND_MSG_PUB.add;
2134: END IF;
2131: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2132: FND_MESSAGE.set_name('OZF', 'OZF_SD_APPR_DISC_VALUE_IS_ZERO');
2133: FND_MSG_PUB.add;
2134: END IF;
2135: x_return_status := fnd_api.g_ret_sts_error;
2136: RETURN;
2137: END IF;
2138: ELSE
2139: p_SDR_lines_tbl(i).approved_discount_value := p_SDR_lines_tbl(i).requested_discount_value;
2138: ELSE
2139: p_SDR_lines_tbl(i).approved_discount_value := p_SDR_lines_tbl(i).requested_discount_value;
2140: END IF;
2141: */
2142: IF p_SDR_lines_tbl(i).approved_discount_value = FND_API.g_miss_num OR p_SDR_lines_tbl(i).approved_discount_value IS NULL THEN
2143: p_SDR_lines_tbl(i).approved_discount_value := p_SDR_lines_tbl(i).requested_discount_value;
2144: END IF;
2145:
2146: --//Approved Discount Currency
2143: p_SDR_lines_tbl(i).approved_discount_value := p_SDR_lines_tbl(i).requested_discount_value;
2144: END IF;
2145:
2146: --//Approved Discount Currency
2147: IF p_SDR_lines_tbl(i).approved_discount_currency <> FND_API.g_miss_char AND p_SDR_lines_tbl(i).approved_discount_currency IS NOT NULL THEN
2148: l_currency_var := NULL;
2149: OPEN c_currency(p_SDR_lines_tbl(i).approved_discount_currency);
2150: FETCH c_currency INTO l_currency_var;
2151: CLOSE c_currency;
2155: FND_MESSAGE.set_name('OZF', 'OZF_INVALID_APPR_DISC_CURR');
2156: --//A value must be entered for Product Cost
2157: FND_MSG_PUB.add;
2158: END IF;
2159: x_return_status := fnd_api.g_ret_sts_error;
2160: RETURN;
2161: END IF;
2162: ELSE
2163: p_SDR_lines_tbl(i).approved_discount_currency := p_SDR_lines_tbl(i).requested_discount_currency;
2163: p_SDR_lines_tbl(i).approved_discount_currency := p_SDR_lines_tbl(i).requested_discount_currency;
2164: END IF;
2165:
2166: --//Approved Max Qty
2167: 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
2168: l_status := check_zero(p_SDR_lines_tbl(i).approved_max_qty);
2169: IF l_status = FND_API.g_false THEN
2170: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2171: FND_MESSAGE.set_name('OZF', 'OZF_SD_APPR_MAX_QTY_VALUE_IS_ZERO');
2165:
2166: --//Approved Max Qty
2167: 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
2168: l_status := check_zero(p_SDR_lines_tbl(i).approved_max_qty);
2169: IF l_status = FND_API.g_false THEN
2170: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2171: FND_MESSAGE.set_name('OZF', 'OZF_SD_APPR_MAX_QTY_VALUE_IS_ZERO');
2172: FND_MSG_PUB.add;
2173: END IF;
2170: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2171: FND_MESSAGE.set_name('OZF', 'OZF_SD_APPR_MAX_QTY_VALUE_IS_ZERO');
2172: FND_MSG_PUB.add;
2173: END IF;
2174: x_return_status := fnd_api.g_ret_sts_error;
2175: RETURN;
2176: END IF;
2177: ELSE
2178: p_SDR_lines_tbl(i).approved_max_qty := p_SDR_lines_tbl(i).max_qty;
2183: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2184: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_APPROVED_MAX_QTY');
2185: FND_MSG_PUB.add;
2186: END IF;
2187: x_return_status := fnd_api.g_ret_sts_error;
2188: RETURN; */
2189:
2190: ELSIF l_status_code IN ('DRAFT'
2191: ,'ASSIGNED'
2330: AND jt.terr_id = p_terr_id;
2331:
2332: BEGIN
2333:
2334: x_return_status := FND_API.G_RET_STS_SUCCESS;
2335:
2336: FOR j IN p_SDR_cust_tbl.FIRST..p_SDR_cust_tbl.LAST LOOP
2337:
2338: --//Update Mode Check
2337:
2338: --//Update Mode Check
2339: IF p_mode ='UPDATE' THEN
2340:
2341: 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
2342: OPEN c_request_customer_id (p_SDR_cust_tbl(j).request_customer_id,G_REQUEST_HEADER_ID);
2343: FETCH c_request_customer_id INTO l_request_customer_id;
2344: CLOSE c_request_customer_id;
2345:
2347: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2348: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQUEST_CUSTOMER_ID');
2349: FND_MSG_PUB.add;
2350: END IF;
2351: x_return_status := fnd_api.g_ret_sts_error;
2352: RETURN;
2353: ELSE
2354: --//Set old value to New If NULL.
2355: OPEN c_old_cust_details(p_SDR_cust_tbl(j).request_customer_id);
2407: END IF;
2408: END IF;
2409:
2410: --//End Customer Flag check
2411: 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
2412:
2413: IF p_SDR_cust_tbl(j).end_customer_flag NOT IN ('N','Y') THEN
2414:
2415: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2416: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_END_CUST_FLAG');
2417: --//Invalid End Customer flag
2418: FND_MSG_PUB.add;
2419: END IF;
2420: x_return_status := fnd_api.g_ret_sts_error;
2421: RETURN;
2422: END IF;
2423: ELSE
2424: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2425: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_END_CUST_FLAG');
2426: --//End Customer Flag is Mandatory
2427: FND_MSG_PUB.add;
2428: END IF;
2429: x_return_status := fnd_api.g_ret_sts_error;
2430: RETURN;
2431: END IF;
2432:
2433: --//Customer usage code Check
2430: RETURN;
2431: END IF;
2432:
2433: --//Customer usage code Check
2434: 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
2435:
2436: l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
2437: p_lookup_table_name =>'OZF_LOOKUPS'
2438: ,p_lookup_type =>'OZF_SD_CUSTOMER_TYPE'
2437: p_lookup_table_name =>'OZF_LOOKUPS'
2438: ,p_lookup_type =>'OZF_SD_CUSTOMER_TYPE'
2439: ,p_lookup_code => p_SDR_cust_tbl(j).cust_usage_code);
2440:
2441: IF l_lookup_stat = FND_API.g_false THEN
2442: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2443: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_ADDR_TYPE');
2444: FND_MSG_PUB.add;
2445: END IF;
2442: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2443: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_ADDR_TYPE');
2444: FND_MSG_PUB.add;
2445: END IF;
2446: x_return_status := fnd_api.g_ret_sts_error;
2447: RETURN;
2448: END IF;
2449:
2450: --//if end_customer_flag = 'Y' then cust_usage_code should be CUSTOMER only
2453: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2454: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_COMB');
2455: FND_MSG_PUB.add;
2456: END IF;
2457: x_return_status := fnd_api.g_ret_sts_error;
2458: RETURN;
2459: END IF;
2460:
2461: --ER 9779889
2462: IF p_SDR_cust_tbl(j).cust_usage_code IN ('CUSTOMER','BILL_TO','SHIP_TO','BUYING_GROUP') THEN
2463: p_SDR_cust_tbl(j).cust_usage_value := NULL;
2464:
2465: --//Party ID is mandatory for All Combinations
2466: 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
2467:
2468: IF p_SDR_cust_tbl(j).cust_usage_code = 'BUYING_GROUP' THEN
2469: OPEN c_buying_group (p_SDR_cust_tbl(j).party_id);
2470: FETCH c_buying_group INTO l_bg_party_id;
2475: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_BUYING_GRP');
2476: --//Buying Group selected is invalid. Please select a valid Buying Group.
2477: FND_MSG_PUB.add;
2478: END IF;
2479: x_return_status := fnd_api.g_ret_sts_error;
2480: RETURN;
2481: END IF;
2482: ELSE
2483: OPEN c_party_id (p_SDR_cust_tbl(j).party_id);
2488: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2489: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_PARTY_ID');
2490: FND_MSG_PUB.add;
2491: END IF;
2492: x_return_status := fnd_api.g_ret_sts_error;
2493: RETURN;
2494: END IF;
2495: END IF;
2496: ELSE
2498: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_PARTY_ID');
2499: --//Party Id is mandatory
2500: FND_MSG_PUB.add;
2501: END IF;
2502: x_return_status := fnd_api.g_ret_sts_error;
2503: RETURN;
2504: END IF;
2505:
2506: IF p_SDR_cust_tbl(j).cust_usage_code ='CUSTOMER' THEN
2505:
2506: IF p_SDR_cust_tbl(j).cust_usage_code ='CUSTOMER' THEN
2507:
2508: --//Cust Account ID
2509: 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
2510: OPEN c_cust_account_id (p_SDR_cust_tbl(j).cust_account_id);
2511: FETCH c_cust_account_id INTO l_cust_account_id;
2512: CLOSE c_cust_account_id;
2513:
2515: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2516: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_ACCOUNT_ID');
2517: FND_MSG_PUB.add;
2518: END IF;
2519: x_return_status := fnd_api.g_ret_sts_error;
2520: RETURN;
2521: END IF;
2522: ELSE
2523: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2523: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2524: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_CUST_ACCOUNT_ID');
2525: FND_MSG_PUB.add;
2526: END IF;
2527: x_return_status := fnd_api.g_ret_sts_error;
2528: RETURN;
2529: END IF;
2530:
2531: --//For Bill To/Ship To Site Use Id is Mandatory
2531: --//For Bill To/Ship To Site Use Id is Mandatory
2532: ELSIF p_SDR_cust_tbl(j).cust_usage_code IN ('BILL_TO','SHIP_TO') THEN
2533:
2534: --//Cust Account ID
2535: 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
2536: OPEN c_cust_account_id (p_SDR_cust_tbl(j).cust_account_id);
2537: FETCH c_cust_account_id INTO l_cust_account_id;
2538: CLOSE c_cust_account_id;
2539:
2541: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2542: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_ACCOUNT_ID');
2543: FND_MSG_PUB.add;
2544: END IF;
2545: x_return_status := fnd_api.g_ret_sts_error;
2546: RETURN;
2547: END IF;
2548: END IF;
2549:
2547: END IF;
2548: END IF;
2549:
2550: --//Site Use Id
2551: 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
2552: OPEN c_site_use_id (p_SDR_cust_tbl(j).cust_account_id
2553: ,p_SDR_cust_tbl(j).party_id
2554: ,p_SDR_cust_tbl(j).site_use_id
2555: ,p_SDR_cust_tbl(j).cust_usage_code);
2560: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2561: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SITE_USE_ID');
2562: FND_MSG_PUB.add;
2563: END IF;
2564: x_return_status := fnd_api.g_ret_sts_error;
2565: RETURN;
2566: END IF;
2567: ELSE
2568: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2568: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2569: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_SITE_USE_ID');
2570: FND_MSG_PUB.add;
2571: END IF;
2572: x_return_status := fnd_api.g_ret_sts_error;
2573: RETURN;
2574: END IF;
2575: ELSIF p_SDR_cust_tbl(j).cust_usage_code ='BUYING_GROUP' THEN
2576: p_SDR_cust_tbl(j).cust_account_id := NULL;
2578: END IF;
2579:
2580: --ER 9779889
2581: ELSIF p_SDR_cust_tbl(j).cust_usage_code IN ('LIST','SEGMENT','TERRITORY') THEN
2582: IF p_SDR_cust_tbl(j).cust_usage_value = FND_API.g_miss_char OR p_SDR_cust_tbl(j).cust_usage_value IS NULL THEN
2583: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2584: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_CUST_USAGE_VALUE');
2585: --//
2586: FND_MSG_PUB.add;
2584: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_CUST_USAGE_VALUE');
2585: --//
2586: FND_MSG_PUB.add;
2587: END IF;
2588: x_return_status := fnd_api.g_ret_sts_error;
2589: RETURN;
2590: END IF;
2591:
2592: p_SDR_cust_tbl(j).party_id := NULL;
2604: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_LIST');
2605: --//List selected is invalid. Please select a valid List.
2606: FND_MSG_PUB.add;
2607: END IF;
2608: x_return_status := fnd_api.g_ret_sts_error;
2609: RETURN;
2610: END IF;
2611:
2612: ELSIF p_SDR_cust_tbl(j).cust_usage_code ='SEGMENT' THEN
2619: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SEGMENT');
2620: --//Segment selected is invalid. Please select a valid Segment.
2621: FND_MSG_PUB.add;
2622: END IF;
2623: x_return_status := fnd_api.g_ret_sts_error;
2624: RETURN;
2625: END IF;
2626:
2627: ELSIF p_SDR_cust_tbl(j).cust_usage_code ='TERRITORY' THEN
2634: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_TERRITORY');
2635: --//Territory selected is invalid. Please select a valid Territory.
2636: FND_MSG_PUB.add;
2637: END IF;
2638: x_return_status := fnd_api.g_ret_sts_error;
2639: RETURN;
2640: END IF;
2641: END IF;
2642: END IF;
2645: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_CUST_ADDR_TYPE');
2646: --//Invalid Customer Usage Code
2647: FND_MSG_PUB.add;
2648: END IF;
2649: x_return_status := fnd_api.g_ret_sts_error;
2650: RETURN;
2651: END IF;
2652:
2653: END LOOP;
2684: WHERE custom_setup_id = p_request_type_setup_id;
2685:
2686: BEGIN
2687:
2688: x_return_status := FND_API.G_RET_STS_SUCCESS;
2689:
2690: IF G_DEBUG THEN
2691: OZF_UTILITY_PVT.debug_message('Inside INSERT HEADER RECORD Procedure ');
2692: END IF;
2699: FETCH c_reqest_header_seq INTO l_req_hdr_seq;
2700: CLOSE c_reqest_header_seq;
2701:
2702: --Generate Request Number, If Null
2703: IF l_SDR_hdr_rec.request_number = FND_API.g_miss_char OR l_SDR_hdr_rec.request_number IS NULL THEN
2704:
2705: OPEN c_code_prefix(p_SDR_hdr_rec.request_type_setup_id);
2706: FETCH c_code_prefix INTO l_code_prefix;
2707: CLOSE c_code_prefix;
2794:
2795: x_request_header_id := l_req_hdr_seq;
2796:
2797: EXCEPTION
2798: WHEN FND_API.G_EXC_ERROR THEN
2799: x_return_status := FND_API.G_RET_STS_ERROR;
2800: WHEN OTHERS THEN
2801: x_return_status := FND_API.g_ret_sts_unexp_error;
2802: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2795: x_request_header_id := l_req_hdr_seq;
2796:
2797: EXCEPTION
2798: WHEN FND_API.G_EXC_ERROR THEN
2799: x_return_status := FND_API.G_RET_STS_ERROR;
2800: WHEN OTHERS THEN
2801: x_return_status := FND_API.g_ret_sts_unexp_error;
2802: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2803: FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
2797: EXCEPTION
2798: WHEN FND_API.G_EXC_ERROR THEN
2799: x_return_status := FND_API.G_RET_STS_ERROR;
2800: WHEN OTHERS THEN
2801: x_return_status := FND_API.g_ret_sts_unexp_error;
2802: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2803: FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
2804: FND_MSG_PUB.add;
2805: END IF;
2819: x_return_status OUT NOCOPY VARCHAR2)
2820: IS
2821:
2822: BEGIN
2823: x_return_status := FND_API.G_RET_STS_SUCCESS;
2824:
2825: IF p_mode ='CREATE' OR p_mode ='COPY' THEN
2826:
2827: INSERT INTO ozf_sd_request_headers_all_tl
2874:
2875: END IF;
2876:
2877: EXCEPTION
2878: WHEN FND_API.G_EXC_ERROR THEN
2879: x_return_status := FND_API.G_RET_STS_ERROR;
2880: WHEN OTHERS THEN
2881: x_return_status := FND_API.g_ret_sts_unexp_error;
2882: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2875: END IF;
2876:
2877: EXCEPTION
2878: WHEN FND_API.G_EXC_ERROR THEN
2879: x_return_status := FND_API.G_RET_STS_ERROR;
2880: WHEN OTHERS THEN
2881: x_return_status := FND_API.g_ret_sts_unexp_error;
2882: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2883: FND_MESSAGE.set_name('OZF', 'OZF_SD_TL_POPULATION_ERROR');
2877: EXCEPTION
2878: WHEN FND_API.G_EXC_ERROR THEN
2879: x_return_status := FND_API.G_RET_STS_ERROR;
2880: WHEN OTHERS THEN
2881: x_return_status := FND_API.g_ret_sts_unexp_error;
2882: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2883: FND_MESSAGE.set_name('OZF', 'OZF_SD_TL_POPULATION_ERROR');
2884: FND_MSG_PUB.add;
2885: END IF;
2916: FROM dual;
2917:
2918: BEGIN
2919: -- Initialize API return status to sucess
2920: x_return_status := FND_API.G_RET_STS_SUCCESS;
2921: l_req_line_seq := 0;
2922:
2923: FOR p IN p_SDR_lines_tbl.FIRST..p_SDR_lines_tbl.LAST LOOP
2924:
2921: l_req_line_seq := 0;
2922:
2923: FOR p IN p_SDR_lines_tbl.FIRST..p_SDR_lines_tbl.LAST LOOP
2924:
2925: IF p_SDR_lines_tbl(p).request_line_id <> FND_API.g_miss_num AND p_SDR_lines_tbl(p).request_line_id IS NOT NULL THEN
2926: -- l_req_line_seq := p_SDR_lines_tbl(p).request_line_id; --fix for bug 15882888
2927: OPEN c_val_reqest_lines_seq(p_SDR_lines_tbl(p).request_line_id);
2928: FETCH c_val_reqest_lines_seq into l_req_line_seq;
2929: CLOSE c_val_reqest_lines_seq;
3080: l_req_line_seq := 0;
3081: END LOOP;
3082:
3083: EXCEPTION
3084: WHEN FND_API.G_EXC_ERROR THEN
3085: x_return_status := FND_API.G_RET_STS_ERROR;
3086: WHEN OTHERS THEN
3087: x_return_status := FND_API.g_ret_sts_unexp_error;
3088: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3081: END LOOP;
3082:
3083: EXCEPTION
3084: WHEN FND_API.G_EXC_ERROR THEN
3085: x_return_status := FND_API.G_RET_STS_ERROR;
3086: WHEN OTHERS THEN
3087: x_return_status := FND_API.g_ret_sts_unexp_error;
3088: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3089: FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
3083: EXCEPTION
3084: WHEN FND_API.G_EXC_ERROR THEN
3085: x_return_status := FND_API.G_RET_STS_ERROR;
3086: WHEN OTHERS THEN
3087: x_return_status := FND_API.g_ret_sts_unexp_error;
3088: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3089: FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
3090: FND_MSG_PUB.add;
3091: END IF;
3130: AND cust_usage_value = p_cust_usage_value;
3131:
3132: BEGIN
3133: -- Initialize API return status to SUCCESS
3134: x_return_status := FND_API.G_RET_STS_SUCCESS;
3135:
3136: IF G_DEBUG THEN
3137: OZF_UTILITY_PVT.debug_message('Inside populate_customer_details');
3138: END IF;
3171: FND_MESSAGE.set_name('OZF', 'OZF_SD_REQ_DUP_CUSTOMER');
3172: END IF;
3173: FND_MSG_PUB.add;
3174: END IF;
3175: x_return_status := fnd_api.g_ret_sts_error;
3176: RETURN;
3177: END IF;
3178:
3179: 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
3175: x_return_status := fnd_api.g_ret_sts_error;
3176: RETURN;
3177: END IF;
3178:
3179: 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
3180: --//Create mode
3181: OPEN c_reqest_cust_seq;
3182: FETCH c_reqest_cust_seq INTO l_request_cust_seq;
3183: CLOSE c_reqest_cust_seq;
3257:
3258: END IF;
3259: END LOOP;
3260: EXCEPTION
3261: WHEN FND_API.G_EXC_ERROR THEN
3262: x_return_status := FND_API.G_RET_STS_ERROR;
3263: WHEN OTHERS THEN
3264: x_return_status := FND_API.g_ret_sts_unexp_error;
3265: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3258: END IF;
3259: END LOOP;
3260: EXCEPTION
3261: WHEN FND_API.G_EXC_ERROR THEN
3262: x_return_status := FND_API.G_RET_STS_ERROR;
3263: WHEN OTHERS THEN
3264: x_return_status := FND_API.g_ret_sts_unexp_error;
3265: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3266: FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
3260: EXCEPTION
3261: WHEN FND_API.G_EXC_ERROR THEN
3262: x_return_status := FND_API.G_RET_STS_ERROR;
3263: WHEN OTHERS THEN
3264: x_return_status := FND_API.g_ret_sts_unexp_error;
3265: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3266: FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
3267: FND_MSG_PUB.add;
3268: END IF;
3299: WHERE qp_list_header_id = p_qp_list_header_id;
3300: */
3301:
3302: BEGIN
3303: x_return_status := FND_API.G_RET_STS_SUCCESS;
3304:
3305: --//Update process
3306: IF G_DEBUG THEN
3307: OZF_UTILITY_PVT.debug_message('Invokes Update Row');
3389: OZF_UTILITY_PVT.debug_message('End update_header_record');
3390: END IF;
3391:
3392: EXCEPTION
3393: WHEN FND_API.G_EXC_ERROR THEN
3394: x_return_status := FND_API.G_RET_STS_ERROR;
3395: WHEN OTHERS THEN
3396: IF G_DEBUG THEN
3397: OZF_UTILITY_PVT.debug_message(SQLERRM);
3390: END IF;
3391:
3392: EXCEPTION
3393: WHEN FND_API.G_EXC_ERROR THEN
3394: x_return_status := FND_API.G_RET_STS_ERROR;
3395: WHEN OTHERS THEN
3396: IF G_DEBUG THEN
3397: OZF_UTILITY_PVT.debug_message(SQLERRM);
3398: END IF;
3395: WHEN OTHERS THEN
3396: IF G_DEBUG THEN
3397: OZF_UTILITY_PVT.debug_message(SQLERRM);
3398: END IF;
3399: x_return_status := FND_API.g_ret_sts_unexp_error;
3400: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3401: FND_MESSAGE.set_name('OZF', 'OZF_TABLE_HANDLER_ERROR');
3402: FND_MSG_PUB.add;
3403: END IF;
3412: ---------------------------------------------------------------------
3413:
3414: PROCEDURE create_sd_request(
3415: p_api_version_number IN NUMBER,
3416: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3417: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3418: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
3419: x_return_status OUT NOCOPY VARCHAR2,
3420: x_msg_count OUT NOCOPY NUMBER,
3413:
3414: PROCEDURE create_sd_request(
3415: p_api_version_number IN NUMBER,
3416: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3417: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3418: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
3419: x_return_status OUT NOCOPY VARCHAR2,
3420: x_msg_count OUT NOCOPY NUMBER,
3421: x_msg_data OUT NOCOPY VARCHAR2,
3414: PROCEDURE create_sd_request(
3415: p_api_version_number IN NUMBER,
3416: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3417: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3418: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
3419: x_return_status OUT NOCOPY VARCHAR2,
3420: x_msg_count OUT NOCOPY NUMBER,
3421: x_msg_data OUT NOCOPY VARCHAR2,
3422: p_SDR_hdr_rec IN SDR_Hdr_rec_type,
3457: BEGIN
3458: -- Standard Start of API savepoint
3459: SAVEPOINT CREATE_SDR_PUB;
3460: -- Standard call to check for call compatibility.
3461: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3462: p_api_version_number,
3463: l_api_name,
3464: G_PKG_NAME)
3465: THEN
3462: p_api_version_number,
3463: l_api_name,
3464: G_PKG_NAME)
3465: THEN
3466: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3467: END IF;
3468:
3469: -- Initialize message list if p_init_msg_list is set to TRUE.
3470: IF FND_API.to_Boolean( p_init_msg_list )
3466: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3467: END IF;
3468:
3469: -- Initialize message list if p_init_msg_list is set to TRUE.
3470: IF FND_API.to_Boolean( p_init_msg_list )
3471: THEN
3472: FND_MSG_PUB.initialize;
3473: END IF;
3474: -- Debug Message
3475: IF G_DEBUG THEN
3476: OZF_UTILITY_PVT.debug_message('Public API: ' || l_api_name || ' PUB start');
3477: END IF;
3478: -- Initialize API return status to SUCCESS
3479: x_return_status := FND_API.G_RET_STS_SUCCESS;
3480:
3481: --//API Body
3482: --========================================================================
3483: --//Validations
3482: --========================================================================
3483: --//Validations
3484:
3485: --//Accrual type Validation
3486: IF l_SDR_rec.accrual_type <> FND_API.g_miss_char AND l_SDR_rec.accrual_type IS NOT NULL THEN
3487:
3488: l_lookup_check :=OZF_UTILITY_PVT.check_lookup_exists(
3489: p_lookup_table_name =>'OZF_LOOKUPS'
3490: ,p_lookup_type =>'OZF_SDR_ACCRUAL_TYPE'
3489: p_lookup_table_name =>'OZF_LOOKUPS'
3490: ,p_lookup_type =>'OZF_SDR_ACCRUAL_TYPE'
3491: ,p_lookup_code => l_SDR_rec.accrual_type);
3492:
3493: IF l_lookup_check = FND_API.g_false THEN
3494: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3495: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ACCRUAL_TYPE');
3496: FND_MSG_PUB.add;
3497: END IF;
3494: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3495: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ACCRUAL_TYPE');
3496: FND_MSG_PUB.add;
3497: END IF;
3498: x_return_status := fnd_api.g_ret_sts_error;
3499: RETURN;
3500: END IF;
3501: ELSE
3502: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3503: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_ACCRUAL_TYPE');
3504: --//Accrual type is Mandatory
3505: FND_MSG_PUB.add;
3506: END IF;
3507: x_return_status := fnd_api.g_ret_sts_error;
3508: RETURN;
3509: END IF;
3510:
3511: --//Set default user status id
3508: RETURN;
3509: END IF;
3510:
3511: --//Set default user status id
3512: IF l_SDR_rec.user_status_id = FND_API.g_miss_num OR l_SDR_rec.user_status_id IS NULL THEN
3513: l_SDR_rec.user_status_id :=get_user_status_id('DRAFT');
3514: END IF;
3515: l_system_status_code := get_system_status_code(l_SDR_rec.user_status_id);
3516:
3519: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_USER_STATUS_ID');
3520: --//User status id entered is invalid
3521: FND_MSG_PUB.add;
3522: END IF;
3523: x_return_status := fnd_api.g_ret_sts_error;
3524: RETURN;
3525: END IF;
3526:
3527: --//Accrual type wise status check
3531: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CREATE_STATUS');
3532: --//User status id entered is invalid for Create
3533: FND_MSG_PUB.add;
3534: END IF;
3535: x_return_status := fnd_api.g_ret_sts_error;
3536: RETURN;
3537: END IF;
3538:
3539: ELSIF l_SDR_rec.accrual_type ='INTERNAL' THEN
3542: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CREATE_STATUS_I');
3543: --//User status id entered is invalid for Create
3544: FND_MSG_PUB.add;
3545: END IF;
3546: x_return_status := fnd_api.g_ret_sts_error;
3547: RETURN;
3548: END IF;
3549: END IF;
3550:
3558: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_PRODUCT_RECORDS');
3559: --//Product Line records are mandatory
3560: FND_MSG_PUB.add;
3561: END IF;
3562: x_return_status := fnd_api.g_ret_sts_error;
3563: RETURN;
3564: END IF;
3565: --//Request Number Validation
3566: IF l_SDR_rec.request_number <> FND_API.g_miss_char AND l_SDR_rec.request_number IS NOT NULL THEN
3562: x_return_status := fnd_api.g_ret_sts_error;
3563: RETURN;
3564: END IF;
3565: --//Request Number Validation
3566: IF l_SDR_rec.request_number <> FND_API.g_miss_char AND l_SDR_rec.request_number IS NOT NULL THEN
3567: OPEN c_request_number(l_SDR_rec.request_number);
3568: FETCH c_request_number INTO l_request_number;
3569: CLOSE c_request_number;
3570:
3572: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3573: FND_MESSAGE.set_name('OZF', 'OZF_SD_DUP_SOURCE_REQ_NO');
3574: FND_MSG_PUB.add;
3575: END IF;
3576: x_return_status := fnd_api.g_ret_sts_error;
3577: RETURN;
3578: END IF;
3579: END IF;
3580:
3578: END IF;
3579: END IF;
3580:
3581: --//Request type validation
3582: 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
3583: OPEN c_request_type_setup(l_SDR_rec.request_type_setup_id);
3584: FETCH c_request_type_setup INTO l_request_type_setup_id,l_request_type;
3585: CLOSE c_request_type_setup;
3586:
3589: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQUEST_TYPE_SETUP');
3590: --//Request type setup id is Mandatory
3591: FND_MSG_PUB.add;
3592: END IF;
3593: x_return_status := fnd_api.g_ret_sts_error;
3594: RETURN;
3595: ELSE
3596: IF ((l_request_type = 'BID')
3597: AND (l_system_status_code <> 'DRAFT')
3600: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3601: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_CUSTOMER_DETAILS');
3602: FND_MSG_PUB.add;
3603: END IF;
3604: x_return_status := fnd_api.g_ret_sts_error;
3605: RETURN;
3606: END IF;
3607: END IF;
3608: ELSE
3610: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_REQUEST_TYPE_SETUP');
3611: --//Request type setup id is Mandatory
3612: FND_MSG_PUB.add;
3613: END IF;
3614: x_return_status := fnd_api.g_ret_sts_error;
3615: RETURN;
3616: END IF;
3617:
3618: IF G_DEBUG THEN
3622: validate_header_items(p_SDR_hdr_rec => l_SDR_rec
3623: ,p_mode =>'CREATE'
3624: ,x_return_status => x_return_status);
3625:
3626: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3627: RAISE fnd_api.g_exc_unexpected_error;
3628: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3629: RAISE fnd_api.g_exc_error;
3630: END IF;
3623: ,p_mode =>'CREATE'
3624: ,x_return_status => x_return_status);
3625:
3626: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3627: RAISE fnd_api.g_exc_unexpected_error;
3628: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3629: RAISE fnd_api.g_exc_error;
3630: END IF;
3631:
3624: ,x_return_status => x_return_status);
3625:
3626: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3627: RAISE fnd_api.g_exc_unexpected_error;
3628: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3629: RAISE fnd_api.g_exc_error;
3630: END IF;
3631:
3632: --//Validate Product Lines
3625:
3626: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3627: RAISE fnd_api.g_exc_unexpected_error;
3628: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3629: RAISE fnd_api.g_exc_error;
3630: END IF;
3631:
3632: --//Validate Product Lines
3633: IF G_DEBUG THEN
3638: ,p_SDR_hdr_rec => l_SDR_rec
3639: ,p_mode => 'CREATE'
3640: ,x_return_status => x_return_status);
3641:
3642: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3643: RAISE fnd_api.g_exc_unexpected_error;
3644: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3645: RAISE fnd_api.g_exc_error;
3646: END IF;
3639: ,p_mode => 'CREATE'
3640: ,x_return_status => x_return_status);
3641:
3642: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3643: RAISE fnd_api.g_exc_unexpected_error;
3644: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3645: RAISE fnd_api.g_exc_error;
3646: END IF;
3647: l_line_rec_flag :='Y';
3640: ,x_return_status => x_return_status);
3641:
3642: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3643: RAISE fnd_api.g_exc_unexpected_error;
3644: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3645: RAISE fnd_api.g_exc_error;
3646: END IF;
3647: l_line_rec_flag :='Y';
3648: END IF;
3641:
3642: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3643: RAISE fnd_api.g_exc_unexpected_error;
3644: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3645: RAISE fnd_api.g_exc_error;
3646: END IF;
3647: l_line_rec_flag :='Y';
3648: END IF;
3649:
3655: validate_customer_items(p_SDR_cust_tbl => l_SDR_cust_tbl
3656: ,p_mode => 'CREATE'
3657: ,x_return_status => x_return_status);
3658:
3659: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3660: RAISE fnd_api.g_exc_unexpected_error;
3661: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3662: RAISE fnd_api.g_exc_error;
3663: END IF;
3656: ,p_mode => 'CREATE'
3657: ,x_return_status => x_return_status);
3658:
3659: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3660: RAISE fnd_api.g_exc_unexpected_error;
3661: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3662: RAISE fnd_api.g_exc_error;
3663: END IF;
3664: l_cust_rec_flag :='Y';
3657: ,x_return_status => x_return_status);
3658:
3659: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3660: RAISE fnd_api.g_exc_unexpected_error;
3661: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3662: RAISE fnd_api.g_exc_error;
3663: END IF;
3664: l_cust_rec_flag :='Y';
3665: END IF;
3658:
3659: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3660: RAISE fnd_api.g_exc_unexpected_error;
3661: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3662: RAISE fnd_api.g_exc_error;
3663: END IF;
3664: l_cust_rec_flag :='Y';
3665: END IF;
3666:
3673: ,p_request_source => 'API'
3674: ,x_request_header_id => x_request_header_id
3675: ,x_return_status => x_return_status);
3676:
3677: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3678: RAISE fnd_api.g_exc_unexpected_error;
3679: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3680: RAISE fnd_api.g_exc_error;
3681: END IF;
3674: ,x_request_header_id => x_request_header_id
3675: ,x_return_status => x_return_status);
3676:
3677: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3678: RAISE fnd_api.g_exc_unexpected_error;
3679: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3680: RAISE fnd_api.g_exc_error;
3681: END IF;
3682:
3675: ,x_return_status => x_return_status);
3676:
3677: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3678: RAISE fnd_api.g_exc_unexpected_error;
3679: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3680: RAISE fnd_api.g_exc_error;
3681: END IF;
3682:
3683: IF G_DEBUG THEN
3676:
3677: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3678: RAISE fnd_api.g_exc_unexpected_error;
3679: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3680: RAISE fnd_api.g_exc_error;
3681: END IF;
3682:
3683: IF G_DEBUG THEN
3684: OZF_UTILITY_PVT.debug_message('Populating Translation table');
3690: ,p_org_id =>l_SDR_rec.org_id
3691: ,p_mode =>'CREATE'
3692: ,x_return_status => x_return_status);
3693:
3694: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3695: RAISE fnd_api.g_exc_unexpected_error;
3696: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3697: RAISE fnd_api.g_exc_error;
3698: END IF;
3691: ,p_mode =>'CREATE'
3692: ,x_return_status => x_return_status);
3693:
3694: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3695: RAISE fnd_api.g_exc_unexpected_error;
3696: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3697: RAISE fnd_api.g_exc_error;
3698: END IF;
3699:
3692: ,x_return_status => x_return_status);
3693:
3694: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3695: RAISE fnd_api.g_exc_unexpected_error;
3696: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3697: RAISE fnd_api.g_exc_error;
3698: END IF;
3699:
3700: IF G_DEBUG THEN
3693:
3694: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3695: RAISE fnd_api.g_exc_unexpected_error;
3696: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3697: RAISE fnd_api.g_exc_error;
3698: END IF;
3699:
3700: IF G_DEBUG THEN
3701: OZF_UTILITY_PVT.debug_message('Populate SD Access table for Requestor');
3704: --Populate SD Access table
3705:
3706: OZF_APPROVAL_PVT.Add_SD_Access(
3707: p_api_version =>p_api_version_number
3708: ,p_init_msg_list =>FND_API.G_FALSE
3709: ,p_commit =>FND_API.G_FALSE
3710: ,p_validation_level =>p_validation_level
3711: ,p_request_header_id =>x_request_header_id
3712: ,p_user_id =>NULL
3705:
3706: OZF_APPROVAL_PVT.Add_SD_Access(
3707: p_api_version =>p_api_version_number
3708: ,p_init_msg_list =>FND_API.G_FALSE
3709: ,p_commit =>FND_API.G_FALSE
3710: ,p_validation_level =>p_validation_level
3711: ,p_request_header_id =>x_request_header_id
3712: ,p_user_id =>NULL
3713: ,p_resource_id =>l_SDR_rec.requestor_id
3719: ,x_msg_count =>x_msg_count
3720: ,x_msg_data =>x_msg_data);
3721:
3722:
3723: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3724: RAISE fnd_api.g_exc_unexpected_error;
3725: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3726: RAISE fnd_api.g_exc_error;
3727: END IF;
3720: ,x_msg_data =>x_msg_data);
3721:
3722:
3723: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3724: RAISE fnd_api.g_exc_unexpected_error;
3725: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3726: RAISE fnd_api.g_exc_error;
3727: END IF;
3728:
3721:
3722:
3723: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3724: RAISE fnd_api.g_exc_unexpected_error;
3725: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3726: RAISE fnd_api.g_exc_error;
3727: END IF;
3728:
3729: IF l_SDR_rec.assignee_resource_id IS NOT NULL THEN
3722:
3723: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3724: RAISE fnd_api.g_exc_unexpected_error;
3725: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3726: RAISE fnd_api.g_exc_error;
3727: END IF;
3728:
3729: IF l_SDR_rec.assignee_resource_id IS NOT NULL THEN
3730: IF G_DEBUG THEN
3733: END IF;
3734: --//Assignee Entry
3735: OZF_APPROVAL_PVT.Add_SD_Access(
3736: p_api_version =>p_api_version_number
3737: ,p_init_msg_list =>FND_API.G_FALSE
3738: ,p_commit =>FND_API.G_FALSE
3739: ,p_validation_level =>p_validation_level
3740: ,p_request_header_id =>x_request_header_id
3741: ,p_user_id =>NULL
3734: --//Assignee Entry
3735: OZF_APPROVAL_PVT.Add_SD_Access(
3736: p_api_version =>p_api_version_number
3737: ,p_init_msg_list =>FND_API.G_FALSE
3738: ,p_commit =>FND_API.G_FALSE
3739: ,p_validation_level =>p_validation_level
3740: ,p_request_header_id =>x_request_header_id
3741: ,p_user_id =>NULL
3742: ,p_resource_id =>l_SDR_rec.assignee_resource_id
3748: ,x_msg_count =>x_msg_count
3749: ,x_msg_data =>x_msg_data);
3750:
3751:
3752: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3753: RAISE fnd_api.g_exc_unexpected_error;
3754: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3755: RAISE fnd_api.g_exc_error;
3756: END IF;
3749: ,x_msg_data =>x_msg_data);
3750:
3751:
3752: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3753: RAISE fnd_api.g_exc_unexpected_error;
3754: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3755: RAISE fnd_api.g_exc_error;
3756: END IF;
3757: END IF;
3750:
3751:
3752: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3753: RAISE fnd_api.g_exc_unexpected_error;
3754: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3755: RAISE fnd_api.g_exc_error;
3756: END IF;
3757: END IF;
3758:
3751:
3752: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3753: RAISE fnd_api.g_exc_unexpected_error;
3754: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3755: RAISE fnd_api.g_exc_error;
3756: END IF;
3757: END IF;
3758:
3759:
3766: p_request_header_id => x_request_header_id
3767: ,p_SDR_lines_tbl => l_SDR_lines_tbl
3768: ,x_return_status => x_return_status);
3769:
3770: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3771: RAISE fnd_api.g_exc_unexpected_error;
3772: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3773: RAISE fnd_api.g_exc_error;
3774: END IF;
3767: ,p_SDR_lines_tbl => l_SDR_lines_tbl
3768: ,x_return_status => x_return_status);
3769:
3770: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3771: RAISE fnd_api.g_exc_unexpected_error;
3772: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3773: RAISE fnd_api.g_exc_error;
3774: END IF;
3775: END IF;
3768: ,x_return_status => x_return_status);
3769:
3770: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3771: RAISE fnd_api.g_exc_unexpected_error;
3772: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3773: RAISE fnd_api.g_exc_error;
3774: END IF;
3775: END IF;
3776:
3769:
3770: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3771: RAISE fnd_api.g_exc_unexpected_error;
3772: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3773: RAISE fnd_api.g_exc_error;
3774: END IF;
3775: END IF;
3776:
3777: IF ((l_cust_rec_flag ='Y') AND (l_request_type = 'BID' ))THEN
3784: p_request_header_id => x_request_header_id
3785: ,p_SDR_cust_tbl => l_SDR_cust_tbl
3786: ,x_return_status => x_return_status);
3787:
3788: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3789: RAISE fnd_api.g_exc_unexpected_error;
3790: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3791: RAISE fnd_api.g_exc_error;
3792: END IF;
3785: ,p_SDR_cust_tbl => l_SDR_cust_tbl
3786: ,x_return_status => x_return_status);
3787:
3788: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3789: RAISE fnd_api.g_exc_unexpected_error;
3790: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3791: RAISE fnd_api.g_exc_error;
3792: END IF;
3793: END IF;
3786: ,x_return_status => x_return_status);
3787:
3788: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3789: RAISE fnd_api.g_exc_unexpected_error;
3790: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3791: RAISE fnd_api.g_exc_error;
3792: END IF;
3793: END IF;
3794: --========================================================================
3787:
3788: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3789: RAISE fnd_api.g_exc_unexpected_error;
3790: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3791: RAISE fnd_api.g_exc_error;
3792: END IF;
3793: END IF;
3794: --========================================================================
3795: --// Commit the process
3796: IF G_DEBUG THEN
3797: OZF_UTILITY_PVT.debug_message('Request Header Id: '||x_request_header_id);
3798: OZF_UTILITY_PVT.debug_message('Public API: '|| l_api_name||' End');
3799: END IF;
3800: IF FND_API.to_Boolean( p_commit )
3801: THEN
3802: COMMIT WORK;
3803: END IF;
3804:
3802: COMMIT WORK;
3803: END IF;
3804:
3805: FND_MSG_PUB.Count_And_Get (
3806: p_encoded => FND_API.G_FALSE,
3807: p_count => x_msg_count,
3808: p_data => x_msg_data
3809: );
3810:
3808: p_data => x_msg_data
3809: );
3810:
3811: EXCEPTION
3812: WHEN FND_API.G_EXC_ERROR THEN
3813: ROLLBACK TO CREATE_SDR_PUB;
3814: x_return_status := FND_API.G_RET_STS_ERROR;
3815: -- Standard call to get message count and if count=1, get the message
3816: FND_MSG_PUB.Count_And_Get (
3810:
3811: EXCEPTION
3812: WHEN FND_API.G_EXC_ERROR THEN
3813: ROLLBACK TO CREATE_SDR_PUB;
3814: x_return_status := FND_API.G_RET_STS_ERROR;
3815: -- Standard call to get message count and if count=1, get the message
3816: FND_MSG_PUB.Count_And_Get (
3817: p_encoded => FND_API.G_FALSE,
3818: p_count => x_msg_count,
3813: ROLLBACK TO CREATE_SDR_PUB;
3814: x_return_status := FND_API.G_RET_STS_ERROR;
3815: -- Standard call to get message count and if count=1, get the message
3816: FND_MSG_PUB.Count_And_Get (
3817: p_encoded => FND_API.G_FALSE,
3818: p_count => x_msg_count,
3819: p_data => x_msg_data
3820: );
3821: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3817: p_encoded => FND_API.G_FALSE,
3818: p_count => x_msg_count,
3819: p_data => x_msg_data
3820: );
3821: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3822: ROLLBACK TO CREATE_SDR_PUB;
3823: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3824: -- Standard call to get message count and if count=1, get the message
3825: FND_MSG_PUB.Count_And_Get (
3819: p_data => x_msg_data
3820: );
3821: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3822: ROLLBACK TO CREATE_SDR_PUB;
3823: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3824: -- Standard call to get message count and if count=1, get the message
3825: FND_MSG_PUB.Count_And_Get (
3826: p_encoded => FND_API.G_FALSE,
3827: p_count => x_msg_count,
3822: ROLLBACK TO CREATE_SDR_PUB;
3823: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3824: -- Standard call to get message count and if count=1, get the message
3825: FND_MSG_PUB.Count_And_Get (
3826: p_encoded => FND_API.G_FALSE,
3827: p_count => x_msg_count,
3828: p_data => x_msg_data
3829: );
3830: WHEN OTHERS THEN
3828: p_data => x_msg_data
3829: );
3830: WHEN OTHERS THEN
3831: ROLLBACK TO CREATE_SDR_PUB;
3832: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3833: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3834: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3835: END IF;
3836: -- Standard call to get message count and if count=1, get the message
3834: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3835: END IF;
3836: -- Standard call to get message count and if count=1, get the message
3837: FND_MSG_PUB.Count_And_Get (
3838: p_encoded => FND_API.G_FALSE,
3839: p_count => x_msg_count,
3840: p_data => x_msg_data
3841: );
3842: End create_sd_request;
3848: -- Public API for updating Ship & Debit Request
3849: ---------------------------------------------------------------------
3850: PROCEDURE update_sd_request(
3851: p_api_version_number IN NUMBER,
3852: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3853: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3854: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
3855: x_return_status OUT NOCOPY VARCHAR2,
3856: x_msg_count OUT NOCOPY NUMBER,
3849: ---------------------------------------------------------------------
3850: PROCEDURE update_sd_request(
3851: p_api_version_number IN NUMBER,
3852: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3853: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3854: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
3855: x_return_status OUT NOCOPY VARCHAR2,
3856: x_msg_count OUT NOCOPY NUMBER,
3857: x_msg_data OUT NOCOPY VARCHAR2,
3850: PROCEDURE update_sd_request(
3851: p_api_version_number IN NUMBER,
3852: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3853: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3854: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
3855: x_return_status OUT NOCOPY VARCHAR2,
3856: x_msg_count OUT NOCOPY NUMBER,
3857: x_msg_data OUT NOCOPY VARCHAR2,
3858: p_SDR_hdr_rec IN SDR_Hdr_rec_type,
4009: BEGIN
4010: -- Standard Start of API savepoint
4011: SAVEPOINT UPDATE_SDR_PUB;
4012: -- Standard call to check for call compatibility.
4013: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4014: p_api_version_number,
4015: l_api_name,
4016: G_PKG_NAME)
4017: THEN
4014: p_api_version_number,
4015: l_api_name,
4016: G_PKG_NAME)
4017: THEN
4018: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4019: END IF;
4020:
4021: -- Initialize message list if p_init_msg_list is set to TRUE.
4022: IF FND_API.to_Boolean( p_init_msg_list )
4018: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4019: END IF;
4020:
4021: -- Initialize message list if p_init_msg_list is set to TRUE.
4022: IF FND_API.to_Boolean( p_init_msg_list )
4023: THEN
4024: FND_MSG_PUB.initialize;
4025: END IF;
4026: -- Debug Message
4027: IF G_DEBUG THEN
4028: OZF_UTILITY_PVT.debug_message('Public API: ' || l_api_name || ' PUB start');
4029: END IF;
4030: -- Initialize API return status to SUCCESS
4031: x_return_status := FND_API.G_RET_STS_SUCCESS;
4032: --==============================================================================
4033: 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
4034:
4035: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4029: END IF;
4030: -- Initialize API return status to SUCCESS
4031: x_return_status := FND_API.G_RET_STS_SUCCESS;
4032: --==============================================================================
4033: 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
4034:
4035: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4036: FND_MESSAGE.set_name('OZF', 'OZF_SD_REQUEST_HEADER_ID_NULL');
4037: FND_MSG_PUB.add;
4035: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4036: FND_MESSAGE.set_name('OZF', 'OZF_SD_REQUEST_HEADER_ID_NULL');
4037: FND_MSG_PUB.add;
4038: END IF;
4039: x_return_status := fnd_api.g_ret_sts_error;
4040: RETURN;
4041: ELSE
4042: OPEN c_request_header_id(l_new_sdr_hdr_rec.request_header_id);
4043: FETCH c_request_header_id INTO l_request_header_id;
4048: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQ_HEADER_ID');
4049: --//Request id is invalid. Please re-enter
4050: FND_MSG_PUB.add;
4051: END IF;
4052: x_return_status := fnd_api.g_ret_sts_error;
4053: RETURN;
4054: END IF;
4055: END IF;
4056: --//Set the request Header ID to Global var
4059: OZF_UTILITY_PVT.debug_message('G_REQUEST_HEADER_ID: ' ||G_REQUEST_HEADER_ID);
4060: END IF;
4061:
4062: --//Request Number Validation
4063: 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
4064: OPEN c_request_number(l_new_sdr_hdr_rec.request_number);
4065: FETCH c_request_number INTO l_request_number;
4066: CLOSE c_request_number;
4067:
4069: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4070: FND_MESSAGE.set_name('OZF', 'OZF_SD_DUP_SOURCE_REQ_NO');
4071: FND_MSG_PUB.add;
4072: END IF;
4073: x_return_status := fnd_api.g_ret_sts_error;
4074: RETURN;
4075: END IF;
4076: END IF;
4077:
4132: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4133: FND_MESSAGE.Set_Name('OZF', 'OZF_API_RESOURCE_LOCKED');
4134: FND_MSG_PUB.ADD;
4135: END IF;
4136: RAISE FND_API.G_EXC_ERROR;
4137: END IF;
4138:
4139: OPEN c_old_sdr_tl(l_new_sdr_hdr_rec.request_header_id);
4140: FETCH c_old_sdr_tl INTO l_old_sdr_hdr_rec.request_description;
4143: IF G_DEBUG THEN
4144: OZF_UTILITY_PVT.debug_message('Validating User');
4145: END IF;
4146: --//User Check
4147: 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
4148: OPEN c_user(l_new_sdr_hdr_rec.user_id);
4149: FETCH c_user INTO l_user_id;
4150: CLOSE c_user;
4151:
4154: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_USER_ID');
4155: --//User Id is invalid, Please re-enter
4156: FND_MSG_PUB.add;
4157: END IF;
4158: x_return_status := fnd_api.g_ret_sts_error;
4159: RETURN;
4160: ELSE --// Check if User is a valid resource or not
4161: OPEN c_resource_id(p_SDR_hdr_rec.user_id);
4162: FETCH c_resource_id INTO l_resource_id;
4166: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4167: FND_MESSAGE.set_name('OZF', 'OZF_SD_USER_IS_NOT_RESOURCE');
4168: FND_MSG_PUB.add;
4169: END IF;
4170: x_return_status := fnd_api.g_ret_sts_error;
4171: RETURN;
4172: END IF;
4173: END IF;
4174:
4177: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_USER_ID');
4178: --//User Id is Mandatory
4179: FND_MSG_PUB.add;
4180: END IF;
4181: x_return_status := fnd_api.g_ret_sts_error;
4182: RETURN;
4183: END IF;
4184:
4185: --//Admin check
4221: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_USER_PERMISSIONS');
4222: --//User has no previlage to update the record.
4223: FND_MSG_PUB.add;
4224: END IF;
4225: x_return_status := fnd_api.g_ret_sts_error;
4226: RETURN;
4227: END IF;
4228:
4229: --//Get the internal and external flags
4244: l_old_user_status_id :=NVL(l_old_sdr_hdr_rec.user_status_id,0);
4245: l_new_user_status_id :=NVL(l_new_sdr_hdr_rec.user_status_id,l_old_sdr_hdr_rec.user_status_id);
4246:
4247: --//Status Transition check
4248: 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
4249: l_old_status_code := get_system_status_code(l_old_sdr_hdr_rec.user_status_id);
4250: l_new_status_code := get_system_status_code(l_new_sdr_hdr_rec.user_status_id);
4251: ELSE
4252: l_old_status_code := get_system_status_code(l_old_sdr_hdr_rec.user_status_id);
4267: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_UPDATE_ALLOWED');
4268: --//Ship and Debit Request updation is not allowed in Closed/Cancelled Status.
4269: FND_MSG_PUB.add;
4270: END IF;
4271: x_return_status := fnd_api.g_ret_sts_error;
4272: RETURN;
4273: END IF;
4274:
4275:
4281: ,p_pm_flag =>l_approver_flag
4282: ,p_internal_flag =>l_internal_flag
4283: ,p_external_flag =>l_external_flag);
4284:
4285: IF l_is_stat_trns_allowed = FND_API.g_false THEN
4286: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4287: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_STATUS_TRANS');
4288: --//Status transition is invalid
4289: FND_MSG_PUB.add;
4287: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_STATUS_TRANS');
4288: --//Status transition is invalid
4289: FND_MSG_PUB.add;
4290: END IF;
4291: x_return_status := fnd_api.g_ret_sts_error;
4292: RETURN;
4293: END IF;
4294: l_old_sdr_hdr_rec.user_status_id := l_new_sdr_hdr_rec.user_status_id;
4295:
4348: l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
4349: l_old_sdr_hdr_rec.request_basis :=NVL(l_new_sdr_hdr_rec.request_basis,l_old_sdr_hdr_rec.request_basis);
4350:
4351: --//Bugfix 7822442
4352: 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)
4353: 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
4354: l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
4355: l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
4356: ELSE
4349: l_old_sdr_hdr_rec.request_basis :=NVL(l_new_sdr_hdr_rec.request_basis,l_old_sdr_hdr_rec.request_basis);
4350:
4351: --//Bugfix 7822442
4352: 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)
4353: 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
4354: l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
4355: l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
4356: ELSE
4357: 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);
4422: 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);
4423: l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
4424:
4425: --//Bugfix 7822442
4426: 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)
4427: 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
4428: l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
4429: l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
4430: ELSE
4423: l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
4424:
4425: --//Bugfix 7822442
4426: 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)
4427: 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
4428: l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
4429: l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
4430: ELSE
4431: 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);
4454: l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
4455: l_old_sdr_hdr_rec.request_basis :=NVL(l_new_sdr_hdr_rec.request_basis,l_old_sdr_hdr_rec.request_basis);
4456:
4457: --//Bugfix 7822442
4458: 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)
4459: 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
4460: l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
4461: l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
4462: ELSE
4455: l_old_sdr_hdr_rec.request_basis :=NVL(l_new_sdr_hdr_rec.request_basis,l_old_sdr_hdr_rec.request_basis);
4456:
4457: --//Bugfix 7822442
4458: 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)
4459: 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
4460: l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
4461: l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
4462: ELSE
4463: 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);
4487: 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);
4488: l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
4489:
4490: --//Bugfix 7822442
4491: 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)
4492: 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
4493: l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
4494: l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
4495: ELSE
4488: l_old_sdr_hdr_rec.request_description :=NVL(l_new_sdr_hdr_rec.request_description,l_old_sdr_hdr_rec.request_description);
4489:
4490: --//Bugfix 7822442
4491: 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)
4492: 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
4493: l_old_sdr_hdr_rec.supplier_contact_id :=NULL;
4494: l_old_sdr_hdr_rec.supplier_contact_name :=l_new_sdr_hdr_rec.supplier_contact_name;
4495: ELSE
4496: 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);
4570: END IF;
4571:
4572: OZF_APPROVAL_PVT.Add_SD_Access(
4573: p_api_version =>p_api_version_number
4574: ,p_init_msg_list =>FND_API.G_FALSE
4575: ,p_commit =>FND_API.G_FALSE
4576: ,p_validation_level =>p_validation_level
4577: ,p_request_header_id =>l_old_sdr_hdr_rec.request_header_id
4578: ,p_user_id =>NULL
4571:
4572: OZF_APPROVAL_PVT.Add_SD_Access(
4573: p_api_version =>p_api_version_number
4574: ,p_init_msg_list =>FND_API.G_FALSE
4575: ,p_commit =>FND_API.G_FALSE
4576: ,p_validation_level =>p_validation_level
4577: ,p_request_header_id =>l_old_sdr_hdr_rec.request_header_id
4578: ,p_user_id =>NULL
4579: ,p_resource_id =>l_new_sdr_hdr_rec.requestor_id
4584: ,x_return_status =>x_return_status
4585: ,x_msg_count =>x_msg_count
4586: ,x_msg_data =>x_msg_data);
4587:
4588: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4589: RAISE fnd_api.g_exc_unexpected_error;
4590: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4591: RAISE fnd_api.g_exc_error;
4592: END IF;
4585: ,x_msg_count =>x_msg_count
4586: ,x_msg_data =>x_msg_data);
4587:
4588: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4589: RAISE fnd_api.g_exc_unexpected_error;
4590: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4591: RAISE fnd_api.g_exc_error;
4592: END IF;
4593: l_old_sdr_hdr_rec.requestor_id :=l_new_sdr_hdr_rec.requestor_id;
4586: ,x_msg_data =>x_msg_data);
4587:
4588: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4589: RAISE fnd_api.g_exc_unexpected_error;
4590: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4591: RAISE fnd_api.g_exc_error;
4592: END IF;
4593: l_old_sdr_hdr_rec.requestor_id :=l_new_sdr_hdr_rec.requestor_id;
4594:
4587:
4588: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4589: RAISE fnd_api.g_exc_unexpected_error;
4590: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4591: RAISE fnd_api.g_exc_error;
4592: END IF;
4593: l_old_sdr_hdr_rec.requestor_id :=l_new_sdr_hdr_rec.requestor_id;
4594:
4595: END IF;
4602: END IF;
4603:
4604: OZF_APPROVAL_PVT.Add_SD_Access(
4605: p_api_version =>p_api_version_number
4606: ,p_init_msg_list =>FND_API.G_FALSE
4607: ,p_commit =>FND_API.G_FALSE
4608: ,p_validation_level =>p_validation_level
4609: ,p_request_header_id =>l_old_sdr_hdr_rec.request_header_id
4610: ,p_user_id =>NULL
4603:
4604: OZF_APPROVAL_PVT.Add_SD_Access(
4605: p_api_version =>p_api_version_number
4606: ,p_init_msg_list =>FND_API.G_FALSE
4607: ,p_commit =>FND_API.G_FALSE
4608: ,p_validation_level =>p_validation_level
4609: ,p_request_header_id =>l_old_sdr_hdr_rec.request_header_id
4610: ,p_user_id =>NULL
4611: ,p_resource_id =>l_new_sdr_hdr_rec.assignee_resource_id
4616: ,x_return_status =>x_return_status
4617: ,x_msg_count =>x_msg_count
4618: ,x_msg_data =>x_msg_data);
4619:
4620: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4621: RAISE fnd_api.g_exc_unexpected_error;
4622: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4623: RAISE fnd_api.g_exc_error;
4624: END IF;
4617: ,x_msg_count =>x_msg_count
4618: ,x_msg_data =>x_msg_data);
4619:
4620: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4621: RAISE fnd_api.g_exc_unexpected_error;
4622: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4623: RAISE fnd_api.g_exc_error;
4624: END IF;
4625: l_old_sdr_hdr_rec.assignee_resource_id :=l_new_sdr_hdr_rec.assignee_resource_id;
4618: ,x_msg_data =>x_msg_data);
4619:
4620: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4621: RAISE fnd_api.g_exc_unexpected_error;
4622: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4623: RAISE fnd_api.g_exc_error;
4624: END IF;
4625: l_old_sdr_hdr_rec.assignee_resource_id :=l_new_sdr_hdr_rec.assignee_resource_id;
4626: END IF;
4619:
4620: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4621: RAISE fnd_api.g_exc_unexpected_error;
4622: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4623: RAISE fnd_api.g_exc_error;
4624: END IF;
4625: l_old_sdr_hdr_rec.assignee_resource_id :=l_new_sdr_hdr_rec.assignee_resource_id;
4626: END IF;
4627: END IF;
4637: validate_header_items(p_SDR_hdr_rec => l_old_sdr_hdr_rec
4638: ,p_mode =>'UPDATE'
4639: ,x_return_status => x_return_status);
4640:
4641: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4642: RAISE fnd_api.g_exc_unexpected_error;
4643: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4644: RAISE fnd_api.g_exc_error;
4645: END IF;
4638: ,p_mode =>'UPDATE'
4639: ,x_return_status => x_return_status);
4640:
4641: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4642: RAISE fnd_api.g_exc_unexpected_error;
4643: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4644: RAISE fnd_api.g_exc_error;
4645: END IF;
4646:
4639: ,x_return_status => x_return_status);
4640:
4641: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4642: RAISE fnd_api.g_exc_unexpected_error;
4643: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4644: RAISE fnd_api.g_exc_error;
4645: END IF;
4646:
4647: --//Proc Lines and Customer details Validation
4640:
4641: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4642: RAISE fnd_api.g_exc_unexpected_error;
4643: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4644: RAISE fnd_api.g_exc_error;
4645: END IF;
4646:
4647: --//Proc Lines and Customer details Validation
4648: IF G_DEBUG THEN
4678: ,p_SDR_hdr_rec => l_old_sdr_hdr_rec
4679: ,p_mode => 'UPDATE'
4680: ,x_return_status => x_return_status);
4681:
4682: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4683: RAISE fnd_api.g_exc_unexpected_error;
4684: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4685: RAISE fnd_api.g_exc_error;
4686: END IF;
4679: ,p_mode => 'UPDATE'
4680: ,x_return_status => x_return_status);
4681:
4682: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4683: RAISE fnd_api.g_exc_unexpected_error;
4684: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4685: RAISE fnd_api.g_exc_error;
4686: END IF;
4687: l_line_rec_flag :='Y';
4680: ,x_return_status => x_return_status);
4681:
4682: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4683: RAISE fnd_api.g_exc_unexpected_error;
4684: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4685: RAISE fnd_api.g_exc_error;
4686: END IF;
4687: l_line_rec_flag :='Y';
4688: END IF;
4681:
4682: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4683: RAISE fnd_api.g_exc_unexpected_error;
4684: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4685: RAISE fnd_api.g_exc_error;
4686: END IF;
4687: l_line_rec_flag :='Y';
4688: END IF;
4689: END IF;
4700: validate_customer_items(p_SDR_cust_tbl => l_new_sdr_cust_tbl
4701: ,p_mode => 'UPDATE'
4702: ,x_return_status => x_return_status);
4703:
4704: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4705: RAISE fnd_api.g_exc_unexpected_error;
4706: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4707: RAISE fnd_api.g_exc_error;
4708: END IF;
4701: ,p_mode => 'UPDATE'
4702: ,x_return_status => x_return_status);
4703:
4704: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4705: RAISE fnd_api.g_exc_unexpected_error;
4706: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4707: RAISE fnd_api.g_exc_error;
4708: END IF;
4709: l_cust_rec_flag :='Y';
4702: ,x_return_status => x_return_status);
4703:
4704: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4705: RAISE fnd_api.g_exc_unexpected_error;
4706: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4707: RAISE fnd_api.g_exc_error;
4708: END IF;
4709: l_cust_rec_flag :='Y';
4710: END IF;
4703:
4704: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4705: RAISE fnd_api.g_exc_unexpected_error;
4706: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4707: RAISE fnd_api.g_exc_error;
4708: END IF;
4709: l_cust_rec_flag :='Y';
4710: END IF;
4711: END IF;
4719: update_header_record(
4720: p_SDR_hdr_rec =>l_old_sdr_hdr_rec
4721: ,x_return_status =>x_return_status);
4722:
4723: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4724: RAISE fnd_api.g_exc_unexpected_error;
4725: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4726: RAISE fnd_api.g_exc_error;
4727: END IF;
4720: p_SDR_hdr_rec =>l_old_sdr_hdr_rec
4721: ,x_return_status =>x_return_status);
4722:
4723: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4724: RAISE fnd_api.g_exc_unexpected_error;
4725: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4726: RAISE fnd_api.g_exc_error;
4727: END IF;
4728:
4721: ,x_return_status =>x_return_status);
4722:
4723: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4724: RAISE fnd_api.g_exc_unexpected_error;
4725: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4726: RAISE fnd_api.g_exc_error;
4727: END IF;
4728:
4729: IF G_DEBUG THEN
4722:
4723: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4724: RAISE fnd_api.g_exc_unexpected_error;
4725: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4726: RAISE fnd_api.g_exc_error;
4727: END IF;
4728:
4729: IF G_DEBUG THEN
4730: OZF_UTILITY_PVT.debug_message('populate_translation_record');
4736: ,p_org_id =>l_old_sdr_hdr_rec.org_id
4737: ,p_mode =>'UPDATE'
4738: ,x_return_status => x_return_status);
4739:
4740: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4741: RAISE fnd_api.g_exc_unexpected_error;
4742: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4743: RAISE fnd_api.g_exc_error;
4744: END IF;
4737: ,p_mode =>'UPDATE'
4738: ,x_return_status => x_return_status);
4739:
4740: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4741: RAISE fnd_api.g_exc_unexpected_error;
4742: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4743: RAISE fnd_api.g_exc_error;
4744: END IF;
4745:
4738: ,x_return_status => x_return_status);
4739:
4740: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4741: RAISE fnd_api.g_exc_unexpected_error;
4742: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4743: RAISE fnd_api.g_exc_error;
4744: END IF;
4745:
4746: --//Populating SD Access Table
4739:
4740: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4741: RAISE fnd_api.g_exc_unexpected_error;
4742: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4743: RAISE fnd_api.g_exc_error;
4744: END IF;
4745:
4746: --//Populating SD Access Table
4747: IF l_old_sdr_hdr_rec.accrual_type ='SUPPLIER'
4749: AND l_old_status_code <>l_new_status_code THEN
4750:
4751: OZF_APPROVAL_PVT.Add_SD_Access(
4752: p_api_version =>p_api_version_number
4753: ,p_init_msg_list =>FND_API.G_FALSE
4754: ,p_commit =>FND_API.G_FALSE
4755: ,p_validation_level =>p_validation_level
4756: ,p_request_header_id =>l_old_sdr_hdr_rec.request_header_id
4757: ,p_user_id =>NULL
4750:
4751: OZF_APPROVAL_PVT.Add_SD_Access(
4752: p_api_version =>p_api_version_number
4753: ,p_init_msg_list =>FND_API.G_FALSE
4754: ,p_commit =>FND_API.G_FALSE
4755: ,p_validation_level =>p_validation_level
4756: ,p_request_header_id =>l_old_sdr_hdr_rec.request_header_id
4757: ,p_user_id =>NULL
4758: ,p_resource_id =>l_old_sdr_hdr_rec.assignee_resource_id
4763: ,x_return_status =>x_return_status
4764: ,x_msg_count =>x_msg_count
4765: ,x_msg_data =>x_msg_data);
4766:
4767: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4768: RAISE fnd_api.g_exc_unexpected_error;
4769: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4770: RAISE fnd_api.g_exc_error;
4771: END IF;
4764: ,x_msg_count =>x_msg_count
4765: ,x_msg_data =>x_msg_data);
4766:
4767: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4768: RAISE fnd_api.g_exc_unexpected_error;
4769: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4770: RAISE fnd_api.g_exc_error;
4771: END IF;
4772: END IF;
4765: ,x_msg_data =>x_msg_data);
4766:
4767: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4768: RAISE fnd_api.g_exc_unexpected_error;
4769: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4770: RAISE fnd_api.g_exc_error;
4771: END IF;
4772: END IF;
4773:
4766:
4767: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4768: RAISE fnd_api.g_exc_unexpected_error;
4769: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4770: RAISE fnd_api.g_exc_error;
4771: END IF;
4772: END IF;
4773:
4774: --//Update Lines Record
4781: p_request_header_id => l_new_sdr_hdr_rec.request_header_id
4782: ,p_SDR_lines_tbl => l_new_sdr_lines_tbl
4783: ,x_return_status => x_return_status);
4784:
4785: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4786: RAISE fnd_api.g_exc_unexpected_error;
4787: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4788: RAISE fnd_api.g_exc_error;
4789: END IF;
4782: ,p_SDR_lines_tbl => l_new_sdr_lines_tbl
4783: ,x_return_status => x_return_status);
4784:
4785: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4786: RAISE fnd_api.g_exc_unexpected_error;
4787: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4788: RAISE fnd_api.g_exc_error;
4789: END IF;
4790: END IF;
4783: ,x_return_status => x_return_status);
4784:
4785: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4786: RAISE fnd_api.g_exc_unexpected_error;
4787: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4788: RAISE fnd_api.g_exc_error;
4789: END IF;
4790: END IF;
4791:
4784:
4785: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4786: RAISE fnd_api.g_exc_unexpected_error;
4787: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4788: RAISE fnd_api.g_exc_error;
4789: END IF;
4790: END IF;
4791:
4792: --//Update Customer Record
4798: p_request_header_id => l_new_sdr_hdr_rec.request_header_id
4799: ,p_SDR_cust_tbl => l_new_sdr_cust_tbl
4800: ,x_return_status => x_return_status);
4801:
4802: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4803: RAISE fnd_api.g_exc_unexpected_error;
4804: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4805: RAISE fnd_api.g_exc_error;
4806: END IF;
4799: ,p_SDR_cust_tbl => l_new_sdr_cust_tbl
4800: ,x_return_status => x_return_status);
4801:
4802: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4803: RAISE fnd_api.g_exc_unexpected_error;
4804: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4805: RAISE fnd_api.g_exc_error;
4806: END IF;
4807: END IF;
4800: ,x_return_status => x_return_status);
4801:
4802: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4803: RAISE fnd_api.g_exc_unexpected_error;
4804: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4805: RAISE fnd_api.g_exc_error;
4806: END IF;
4807: END IF;
4808:
4801:
4802: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4803: RAISE fnd_api.g_exc_unexpected_error;
4804: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4805: RAISE fnd_api.g_exc_error;
4806: END IF;
4807: END IF;
4808:
4809: --//Invoking Offer API Process
4828: END IF;
4829:
4830: OZF_OFFER_PVT.process_sd_modifiers(
4831: p_sdr_header_id => p_SDR_hdr_rec.request_header_id
4832: ,p_init_msg_list => FND_API.G_FALSE
4833: ,p_api_version => l_api_version_number
4834: ,p_commit => FND_API.G_FALSE
4835: ,x_return_status => x_return_status
4836: ,x_msg_count => x_msg_count
4830: OZF_OFFER_PVT.process_sd_modifiers(
4831: p_sdr_header_id => p_SDR_hdr_rec.request_header_id
4832: ,p_init_msg_list => FND_API.G_FALSE
4833: ,p_api_version => l_api_version_number
4834: ,p_commit => FND_API.G_FALSE
4835: ,x_return_status => x_return_status
4836: ,x_msg_count => x_msg_count
4837: ,x_msg_data => x_msg_data
4838: ,x_qp_list_header_id => l_qp_list_header_id
4850: offer_type = 'ACCRUAL'
4851: WHERE request_header_id = p_SDR_hdr_rec.request_header_id;
4852: END IF;
4853:
4854: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4855: RAISE fnd_api.g_exc_unexpected_error;
4856: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4857: RAISE fnd_api.g_exc_error;
4858: END IF;
4851: WHERE request_header_id = p_SDR_hdr_rec.request_header_id;
4852: END IF;
4853:
4854: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4855: RAISE fnd_api.g_exc_unexpected_error;
4856: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4857: RAISE fnd_api.g_exc_error;
4858: END IF;
4859: END IF;
4852: END IF;
4853:
4854: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4855: RAISE fnd_api.g_exc_unexpected_error;
4856: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4857: RAISE fnd_api.g_exc_error;
4858: END IF;
4859: END IF;
4860:
4853:
4854: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4855: RAISE fnd_api.g_exc_unexpected_error;
4856: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
4857: RAISE fnd_api.g_exc_error;
4858: END IF;
4859: END IF;
4860:
4861:
4862: --// Commit the process
4863: IF G_DEBUG THEN
4864: OZF_UTILITY_PVT.debug_message('Public API: ' || l_api_name || ' End');
4865: END IF;
4866: IF FND_API.to_Boolean( p_commit )
4867: THEN
4868: COMMIT WORK;
4869: END IF;
4870:
4916:
4917: END IF;
4918:
4919: FND_MSG_PUB.Count_And_Get (
4920: p_encoded => FND_API.G_FALSE,
4921: p_count => x_msg_count,
4922: p_data => x_msg_data
4923: );
4924: --==============================================================================
4922: p_data => x_msg_data
4923: );
4924: --==============================================================================
4925: EXCEPTION
4926: WHEN FND_API.G_EXC_ERROR THEN
4927: ROLLBACK TO UPDATE_SDR_PUB;
4928: x_return_status := FND_API.G_RET_STS_ERROR;
4929: -- Standard call to get message count and if count=1, get the message
4930: FND_MSG_PUB.Count_And_Get (
4924: --==============================================================================
4925: EXCEPTION
4926: WHEN FND_API.G_EXC_ERROR THEN
4927: ROLLBACK TO UPDATE_SDR_PUB;
4928: x_return_status := FND_API.G_RET_STS_ERROR;
4929: -- Standard call to get message count and if count=1, get the message
4930: FND_MSG_PUB.Count_And_Get (
4931: p_encoded => FND_API.G_FALSE,
4932: p_count => x_msg_count,
4927: ROLLBACK TO UPDATE_SDR_PUB;
4928: x_return_status := FND_API.G_RET_STS_ERROR;
4929: -- Standard call to get message count and if count=1, get the message
4930: FND_MSG_PUB.Count_And_Get (
4931: p_encoded => FND_API.G_FALSE,
4932: p_count => x_msg_count,
4933: p_data => x_msg_data
4934: );
4935: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4931: p_encoded => FND_API.G_FALSE,
4932: p_count => x_msg_count,
4933: p_data => x_msg_data
4934: );
4935: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4936: ROLLBACK TO UPDATE_SDR_PUB;
4937: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4938: -- Standard call to get message count and if count=1, get the message
4939: FND_MSG_PUB.Count_And_Get (
4933: p_data => x_msg_data
4934: );
4935: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4936: ROLLBACK TO UPDATE_SDR_PUB;
4937: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4938: -- Standard call to get message count and if count=1, get the message
4939: FND_MSG_PUB.Count_And_Get (
4940: p_encoded => FND_API.G_FALSE,
4941: p_count => x_msg_count,
4936: ROLLBACK TO UPDATE_SDR_PUB;
4937: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4938: -- Standard call to get message count and if count=1, get the message
4939: FND_MSG_PUB.Count_And_Get (
4940: p_encoded => FND_API.G_FALSE,
4941: p_count => x_msg_count,
4942: p_data => x_msg_data
4943: );
4944: WHEN OTHERS THEN
4942: p_data => x_msg_data
4943: );
4944: WHEN OTHERS THEN
4945: ROLLBACK TO UPDATE_SDR_PUB;
4946: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4947: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4948: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4949: END IF;
4950: -- Standard call to get message count and if count=1, get the message
4948: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4949: END IF;
4950: -- Standard call to get message count and if count=1, get the message
4951: FND_MSG_PUB.Count_And_Get (
4952: p_encoded => FND_API.G_FALSE,
4953: p_count => x_msg_count,
4954: p_data => x_msg_data
4955: );
4956: END update_sd_request;
4963: -- Public API for Copying SDR
4964: ---------------------------------------------------------------------
4965: PROCEDURE copy_sd_request(
4966: p_api_version_number IN NUMBER,
4967: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4968: p_commit IN VARCHAR2 := FND_API.G_FALSE,
4969: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
4970: x_return_status OUT NOCOPY VARCHAR2,
4971: x_msg_count OUT NOCOPY NUMBER,
4964: ---------------------------------------------------------------------
4965: PROCEDURE copy_sd_request(
4966: p_api_version_number IN NUMBER,
4967: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4968: p_commit IN VARCHAR2 := FND_API.G_FALSE,
4969: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
4970: x_return_status OUT NOCOPY VARCHAR2,
4971: x_msg_count OUT NOCOPY NUMBER,
4972: x_msg_data OUT NOCOPY VARCHAR2,
4965: PROCEDURE copy_sd_request(
4966: p_api_version_number IN NUMBER,
4967: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4968: p_commit IN VARCHAR2 := FND_API.G_FALSE,
4969: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
4970: x_return_status OUT NOCOPY VARCHAR2,
4971: x_msg_count OUT NOCOPY NUMBER,
4972: x_msg_data OUT NOCOPY VARCHAR2,
4973: p_source_request_id IN VARCHAR2,
5173: BEGIN
5174: -- Standard Start of API savepoint
5175: SAVEPOINT COPY_SDR_PUB;
5176: -- Standard call to check for call compatibility.
5177: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
5178: p_api_version_number,
5179: l_api_name,
5180: G_PKG_NAME)
5181: THEN
5178: p_api_version_number,
5179: l_api_name,
5180: G_PKG_NAME)
5181: THEN
5182: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5183: END IF;
5184:
5185: -- Initialize message list if p_init_msg_list is set to TRUE.
5186: IF FND_API.to_Boolean( p_init_msg_list )
5182: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5183: END IF;
5184:
5185: -- Initialize message list if p_init_msg_list is set to TRUE.
5186: IF FND_API.to_Boolean( p_init_msg_list )
5187: THEN
5188: FND_MSG_PUB.initialize;
5189: END IF;
5190: -- Debug Message
5191: IF G_DEBUG THEN
5192: OZF_UTILITY_PVT.debug_message('Public API: ' || l_api_name || ' pub start');
5193: END IF;
5194: -- Initialize API return status to SUCCESS
5195: x_return_status := FND_API.G_RET_STS_SUCCESS;
5196: --==============================================================================
5197: --//Bug 7190421 - User Check and population
5198:
5199: l_user_id := FND_GLOBAL.user_id;
5210: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
5211: FND_MESSAGE.set_name('OZF', 'OZF_SD_USER_IS_NOT_RESOURCE');
5212: FND_MSG_PUB.add;
5213: END IF;
5214: x_return_status := fnd_api.g_ret_sts_error;
5215: RETURN;
5216: END IF;
5217:
5218: IF G_DEBUG THEN
5231: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_SOURCE_REQ_ID');
5232: --//Invalid source request id. Please re-enter
5233: FND_MSG_PUB.add;
5234: END IF;
5235: x_return_status := fnd_api.g_ret_sts_error;
5236: RETURN;
5237: END IF;
5238: ELSE
5239: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
5240: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_SOURCE_REQ_ID');
5241: --//Invalid source request id. Please re-enter
5242: FND_MSG_PUB.add;
5243: END IF;
5244: x_return_status := fnd_api.g_ret_sts_error;
5245: RETURN;
5246: END IF;
5247:
5248: --//Check If New Request Number already exists
5256: FND_MESSAGE.set_name('OZF', 'OZF_SD_DUP_SOURCE_REQ_NO');
5257: --//New request number entered is already exists.
5258: FND_MSG_PUB.add;
5259: END IF;
5260: x_return_status := fnd_api.g_ret_sts_error;
5261: RETURN;
5262: END IF;
5263: END IF;
5264: --//Accrual type Check
5267: p_lookup_table_name =>'OZF_LOOKUPS'
5268: ,p_lookup_type =>'OZF_SDR_ACCRUAL_TYPE'
5269: ,p_lookup_code => p_accrual_type);
5270:
5271: IF l_lookup_stat = FND_API.g_false THEN
5272: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
5273: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ACCRUAL_TYPE');
5274: FND_MSG_PUB.add;
5275: END IF;
5272: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
5273: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_ACCRUAL_TYPE');
5274: FND_MSG_PUB.add;
5275: END IF;
5276: x_return_status := fnd_api.g_ret_sts_error;
5277: RETURN;
5278: END IF;
5279: ELSE
5280: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
5281: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_ACCRUAL_TYPE');
5282: --//Accrual type is Mandatory
5283: FND_MSG_PUB.add;
5284: END IF;
5285: x_return_status := fnd_api.g_ret_sts_error;
5286: RETURN;
5287: END IF;
5288:
5289: --//Cust Account ID Validation
5293: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
5294: FND_MESSAGE.set_name('OZF', 'OZF_SD_NO_CUST_ACCOUNT_ID');
5295: FND_MSG_PUB.add;
5296: END IF;
5297: x_return_status := fnd_api.g_ret_sts_error;
5298: RETURN;
5299: ELSE
5300: OPEN c_cust_account_id(p_cust_account_id);
5301: FETCH c_cust_account_id INTO l_cust_account_id;
5305: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
5306: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_CUST_ACCOUNT_ID');
5307: FND_MSG_PUB.add;
5308: END IF;
5309: x_return_status := fnd_api.g_ret_sts_error;
5310: RETURN;
5311: END IF;
5312: END IF;
5313: END IF;
5355: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
5356: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_REQUEST_SOURCE');
5357: FND_MSG_PUB.add;
5358: END IF;
5359: x_return_status := fnd_api.g_ret_sts_error;
5360: RETURN;
5361: END IF;
5362:
5363: --//Get the source Information
5422: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_END_DATE');
5423: --//End date should be greater than start date
5424: FND_MSG_PUB.add;
5425: END IF;
5426: x_return_status := fnd_api.g_ret_sts_error;
5427: RETURN;
5428: END IF;
5429:
5430: IF G_DEBUG THEN
5439: FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_COPY_OPTION');
5440: --//Internal to Supplier Copy option is invalid!
5441: FND_MSG_PUB.add;
5442: END IF;
5443: x_return_status := fnd_api.g_ret_sts_error;
5444: RETURN;
5445:
5446: ELSIF ((l_sdr_source_rec.accrual_type ='SUPPLIER') AND (p_accrual_type ='INTERNAL')) OR p_accrual_type ='INTERNAL' THEN
5447: l_sdr_source_rec.accrual_type := p_accrual_type;
5477: ,p_request_source => p_request_source
5478: ,x_request_header_id => x_request_header_id
5479: ,x_return_status => x_return_status);
5480:
5481: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5482: RAISE fnd_api.g_exc_unexpected_error;
5483: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5484: RAISE fnd_api.g_exc_error;
5485: END IF;
5478: ,x_request_header_id => x_request_header_id
5479: ,x_return_status => x_return_status);
5480:
5481: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5482: RAISE fnd_api.g_exc_unexpected_error;
5483: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5484: RAISE fnd_api.g_exc_error;
5485: END IF;
5486:
5479: ,x_return_status => x_return_status);
5480:
5481: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5482: RAISE fnd_api.g_exc_unexpected_error;
5483: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5484: RAISE fnd_api.g_exc_error;
5485: END IF;
5486:
5487: --//Populate Translation table
5480:
5481: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5482: RAISE fnd_api.g_exc_unexpected_error;
5483: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5484: RAISE fnd_api.g_exc_error;
5485: END IF;
5486:
5487: --//Populate Translation table
5488: OPEN c_tl_description(p_source_request_id);
5502: IF G_DEBUG THEN
5503: OZF_UTILITY_PVT.debug_message('x_request_header_id '||x_request_header_id);
5504: END IF;
5505:
5506: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5507: RAISE fnd_api.g_exc_unexpected_error;
5508: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5509: RAISE fnd_api.g_exc_error;
5510: END IF;
5503: OZF_UTILITY_PVT.debug_message('x_request_header_id '||x_request_header_id);
5504: END IF;
5505:
5506: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5507: RAISE fnd_api.g_exc_unexpected_error;
5508: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5509: RAISE fnd_api.g_exc_error;
5510: END IF;
5511:
5504: END IF;
5505:
5506: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5507: RAISE fnd_api.g_exc_unexpected_error;
5508: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5509: RAISE fnd_api.g_exc_error;
5510: END IF;
5511:
5512: --//Populate SD Access table
5505:
5506: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5507: RAISE fnd_api.g_exc_unexpected_error;
5508: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5509: RAISE fnd_api.g_exc_error;
5510: END IF;
5511:
5512: --//Populate SD Access table
5513: --//For Owner
5513: --//For Owner
5514:
5515: OZF_APPROVAL_PVT.Add_SD_Access(
5516: p_api_version =>p_api_version_number
5517: ,p_init_msg_list =>FND_API.G_FALSE
5518: ,p_commit =>FND_API.G_FALSE
5519: ,p_validation_level =>p_validation_level
5520: ,p_request_header_id =>x_request_header_id
5521: ,p_user_id =>NULL
5514:
5515: OZF_APPROVAL_PVT.Add_SD_Access(
5516: p_api_version =>p_api_version_number
5517: ,p_init_msg_list =>FND_API.G_FALSE
5518: ,p_commit =>FND_API.G_FALSE
5519: ,p_validation_level =>p_validation_level
5520: ,p_request_header_id =>x_request_header_id
5521: ,p_user_id =>NULL
5522: ,p_resource_id =>l_sdr_source_rec.requestor_id
5527: ,x_return_status =>x_return_status
5528: ,x_msg_count =>x_msg_count
5529: ,x_msg_data =>x_msg_data);
5530:
5531: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5532: RAISE fnd_api.g_exc_unexpected_error;
5533: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5534: RAISE fnd_api.g_exc_error;
5535: END IF;
5528: ,x_msg_count =>x_msg_count
5529: ,x_msg_data =>x_msg_data);
5530:
5531: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5532: RAISE fnd_api.g_exc_unexpected_error;
5533: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5534: RAISE fnd_api.g_exc_error;
5535: END IF;
5536:
5529: ,x_msg_data =>x_msg_data);
5530:
5531: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5532: RAISE fnd_api.g_exc_unexpected_error;
5533: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5534: RAISE fnd_api.g_exc_error;
5535: END IF;
5536:
5537:
5530:
5531: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5532: RAISE fnd_api.g_exc_unexpected_error;
5533: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5534: RAISE fnd_api.g_exc_error;
5535: END IF;
5536:
5537:
5538: IF G_DEBUG THEN
5608: p_request_header_id => x_request_header_id
5609: ,p_SDR_lines_tbl => l_sdr_source_lines_tbl
5610: ,x_return_status => x_return_status);
5611:
5612: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5613: RAISE fnd_api.g_exc_unexpected_error;
5614: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5615: RAISE fnd_api.g_exc_error;
5616: END IF;
5609: ,p_SDR_lines_tbl => l_sdr_source_lines_tbl
5610: ,x_return_status => x_return_status);
5611:
5612: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5613: RAISE fnd_api.g_exc_unexpected_error;
5614: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5615: RAISE fnd_api.g_exc_error;
5616: END IF;
5617: END IF;
5610: ,x_return_status => x_return_status);
5611:
5612: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5613: RAISE fnd_api.g_exc_unexpected_error;
5614: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5615: RAISE fnd_api.g_exc_error;
5616: END IF;
5617: END IF;
5618:
5611:
5612: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5613: RAISE fnd_api.g_exc_unexpected_error;
5614: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5615: RAISE fnd_api.g_exc_error;
5616: END IF;
5617: END IF;
5618:
5619: --//Copy Customer
5657: ,p_SDR_cust_tbl => l_sdr_source_cust_tbl
5658: ,x_return_status => x_return_status);
5659:
5660:
5661: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5662: RAISE fnd_api.g_exc_unexpected_error;
5663: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5664: RAISE fnd_api.g_exc_error;
5665: END IF;
5658: ,x_return_status => x_return_status);
5659:
5660:
5661: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5662: RAISE fnd_api.g_exc_unexpected_error;
5663: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5664: RAISE fnd_api.g_exc_error;
5665: END IF;
5666:
5659:
5660:
5661: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5662: RAISE fnd_api.g_exc_unexpected_error;
5663: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5664: RAISE fnd_api.g_exc_error;
5665: END IF;
5666:
5667: END IF;
5660:
5661: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5662: RAISE fnd_api.g_exc_unexpected_error;
5663: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5664: RAISE fnd_api.g_exc_error;
5665: END IF;
5666:
5667: END IF;
5668:
5702: p_request_header_id => x_request_header_id
5703: ,p_SDR_cust_tbl => l_sdr_source_cust_tbl
5704: ,x_return_status => x_return_status);
5705:
5706: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5707: RAISE fnd_api.g_exc_unexpected_error;
5708: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5709: RAISE fnd_api.g_exc_error;
5710: END IF;
5703: ,p_SDR_cust_tbl => l_sdr_source_cust_tbl
5704: ,x_return_status => x_return_status);
5705:
5706: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5707: RAISE fnd_api.g_exc_unexpected_error;
5708: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5709: RAISE fnd_api.g_exc_error;
5710: END IF;
5711:
5704: ,x_return_status => x_return_status);
5705:
5706: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5707: RAISE fnd_api.g_exc_unexpected_error;
5708: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5709: RAISE fnd_api.g_exc_error;
5710: END IF;
5711:
5712: END IF;
5705:
5706: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
5707: RAISE fnd_api.g_exc_unexpected_error;
5708: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
5709: RAISE fnd_api.g_exc_error;
5710: END IF;
5711:
5712: END IF;
5713:
5716: OZF_UTILITY_PVT.debug_message('New request_header_id :'||x_request_header_id );
5717: OZF_UTILITY_PVT.debug_message('Return Status :'|| x_return_status );
5718: OZF_UTILITY_PVT.debug_message('Public API: ' || l_api_name || ' End..');
5719: END IF;
5720: IF FND_API.to_Boolean( p_commit )
5721: THEN
5722: COMMIT WORK;
5723: END IF;
5724:
5722: COMMIT WORK;
5723: END IF;
5724:
5725: FND_MSG_PUB.Count_And_Get (
5726: p_encoded => FND_API.G_FALSE,
5727: p_count => x_msg_count,
5728: p_data => x_msg_data
5729: );
5730:
5729: );
5730:
5731: --==============================================================================
5732: EXCEPTION
5733: WHEN FND_API.G_EXC_ERROR THEN
5734: ROLLBACK TO COPY_SDR_PUB;
5735: x_return_status := FND_API.G_RET_STS_ERROR;
5736: -- Standard call to get message count and if count=1, get the message
5737: FND_MSG_PUB.Count_And_Get (
5731: --==============================================================================
5732: EXCEPTION
5733: WHEN FND_API.G_EXC_ERROR THEN
5734: ROLLBACK TO COPY_SDR_PUB;
5735: x_return_status := FND_API.G_RET_STS_ERROR;
5736: -- Standard call to get message count and if count=1, get the message
5737: FND_MSG_PUB.Count_And_Get (
5738: p_encoded => FND_API.G_FALSE,
5739: p_count => x_msg_count,
5734: ROLLBACK TO COPY_SDR_PUB;
5735: x_return_status := FND_API.G_RET_STS_ERROR;
5736: -- Standard call to get message count and if count=1, get the message
5737: FND_MSG_PUB.Count_And_Get (
5738: p_encoded => FND_API.G_FALSE,
5739: p_count => x_msg_count,
5740: p_data => x_msg_data
5741: );
5742: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5738: p_encoded => FND_API.G_FALSE,
5739: p_count => x_msg_count,
5740: p_data => x_msg_data
5741: );
5742: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5743: ROLLBACK TO COPY_SDR_PUB;
5744: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5745: -- Standard call to get message count and if count=1, get the message
5746: FND_MSG_PUB.Count_And_Get (
5740: p_data => x_msg_data
5741: );
5742: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5743: ROLLBACK TO COPY_SDR_PUB;
5744: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5745: -- Standard call to get message count and if count=1, get the message
5746: FND_MSG_PUB.Count_And_Get (
5747: p_encoded => FND_API.G_FALSE,
5748: p_count => x_msg_count,
5743: ROLLBACK TO COPY_SDR_PUB;
5744: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5745: -- Standard call to get message count and if count=1, get the message
5746: FND_MSG_PUB.Count_And_Get (
5747: p_encoded => FND_API.G_FALSE,
5748: p_count => x_msg_count,
5749: p_data => x_msg_data
5750: );
5751: WHEN OTHERS THEN
5749: p_data => x_msg_data
5750: );
5751: WHEN OTHERS THEN
5752: ROLLBACK TO COPY_SDR_PUB;
5753: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5754: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5755: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
5756: END IF;
5757: -- Standard call to get message count and if count=1, get the message
5755: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
5756: END IF;
5757: -- Standard call to get message count and if count=1, get the message
5758: FND_MSG_PUB.Count_And_Get (
5759: p_encoded => FND_API.G_FALSE,
5760: p_count => x_msg_count,
5761: p_data => x_msg_data
5762: );
5763: END copy_sd_request;