[Home] [Help]
149: CLOSE c_validate_party;
150: x_terr_grp_id := -999;
151: x_terr_grp_acct_id := -999;
152: x_named_acct_id := -999;
153: x_return_status := fnd_api.g_ret_sts_error;
154: fnd_message.clear;
155: fnd_message.set_name ('JTF', 'JTF_TTY_INVALID_PARTY');
156: return;
157: END IF;
166: CLOSE c_validate_tg;
167: x_terr_grp_id := -999;
168: x_terr_grp_acct_id := -999;
169: x_named_acct_id := -999;
170: x_return_status := fnd_api.g_ret_sts_error;
171: fnd_message.clear;
172: fnd_message.set_name ('JTF', 'JTF_TTY_INVALID_FROM_TG');
173: return;
174: END IF;
182: p_start_date := l_tg_start_date;
183: END IF;
184: ELSE
185: IF (p_start_date < l_tg_start_date) THEN
186: x_return_status := fnd_api.g_ret_sts_error;
187: fnd_message.clear;
188: fnd_message.set_name ('JTF', 'JTF_TERR_STARTDATE_NOT_VALID');
189: return;
190: END IF;
197: p_end_date := ADD_MONTHS(NVL(P_START_DATE,TRUNC(SYSDATE)), 12);
198: END IF;
199: ELSE
200: IF (p_end_date < l_tg_start_date OR p_end_date > l_tg_end_date) THEN
201: x_return_status := fnd_api.g_ret_sts_error;
202: fnd_message.clear;
203: fnd_message.set_name ('JTF', 'JTF_TERR_ENDDATE_NOT_VALID');
204: return;
205: END IF;
216: CLOSE c_get_all_ids;
217: x_terr_grp_id := -999;
218: x_terr_grp_acct_id := -999;
219: x_named_acct_id := -999;
220: x_return_status := fnd_api.g_ret_sts_error;
221: fnd_message.clear;
222: fnd_message.set_name ('JTF', 'JTF_TTY_NO_TGA_MATCH');
223: return;
224: END IF;
249:
250: x_terr_grp_id := l_tg_id;
251: x_terr_grp_acct_id := l_tga_id;
252: x_named_acct_id := l_na_id;
253: x_return_status := fnd_api.g_ret_sts_success;
254:
255: EXCEPTION
256: WHEN OTHERS THEN
257: l_error_msg := substr(sqlerrm,1,200);
333: l_user_id,
334: sysdate,
335: l_login_id);
336: */
337: x_return_status := fnd_api.g_ret_sts_success;
338:
339: JTF_TTY_GEN_TERR_PVT.delete_TGA(
340: p_terr_grp_acct_id =>p_terr_grp_acct_id,
341: p_terr_group_id =>p_terr_grp_id,
344: );
345:
346: EXCEPTION
347: WHEN OTHERS THEN
348: x_return_status := fnd_api.g_ret_sts_unexp_error;
349: l_error_msg := substr(sqlerrm,1,200);
350: fnd_message.clear;
351: fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
352: fnd_message.set_token('ERRMSG', l_error_msg );
668: CLOSE c_validate_party;
669: x_terr_grp_id := -999;
670: x_terr_grp_acct_id := -999;
671: x_named_acct_id := -999;
672: x_return_status := fnd_api.g_ret_sts_error;
673: fnd_message.clear;
674: fnd_message.set_name ('JTF', 'JTF_TTY_INVALID_PARTY');
675: return;
676: END IF;
687: CLOSE c_validate_tg;
688: x_terr_grp_id := -999;
689: x_terr_grp_acct_id := -999;
690: x_named_acct_id := -999;
691: x_return_status := fnd_api.g_ret_sts_error;
692: fnd_message.clear;
693: fnd_message.set_name ('JTF', 'JTF_TTY_INVALID_TO_TG');
694: return;
695: END IF;
698: IF (( l_match_rule_code = '3' ) AND ( l_duns_number is null )) THEN
699: x_terr_grp_id := -999;
700: x_terr_grp_acct_id := -999;
701: x_named_acct_id := -999;
702: x_return_status := fnd_api.g_ret_sts_error;
703: fnd_message.clear;
704: fnd_message.set_name ('JTF', 'JTF_TTY_NO_DUNS_NUMBER');
705: return;
706: END IF;
713: p_start_date := l_tg_start_date;
714: END IF;
715: ELSE
716: IF (p_start_date < l_tg_start_date) THEN
717: x_return_status := fnd_api.g_ret_sts_error;
718: fnd_message.clear;
719: fnd_message.set_name ('JTF', 'JTF_TERR_STARTDATE_NOT_VALID');
720: return;
721: END IF;
728: p_end_date := ADD_MONTHS(NVL(P_START_DATE,TRUNC(SYSDATE)), 12);
729: END IF;
730: ELSE
731: IF (p_end_date < l_tg_start_date OR p_end_date > l_tg_end_date) THEN
732: x_return_status := fnd_api.g_ret_sts_error;
733: fnd_message.clear;
734: fnd_message.set_name ('JTF', 'JTF_TERR_ENDDATE_NOT_VALID');
735: return;
736: END IF;
748: CLOSE c_check_tga_exists;
749: x_terr_grp_id := -999;
750: x_terr_grp_acct_id := -999;
751: x_named_acct_id := -999;
752: x_return_status := fnd_api.g_ret_sts_error;
753: fnd_message.clear;
754: fnd_message.set_name ('JTF', 'JTF_TTY_TGA_EXISTS');
755: return;
756: END IF;
798:
799: IF l_match_rule_code = '1' THEN
800: JTF_TTY_MAINTAIN_NA_PVT.create_acct_mappings
801: (P_API_VERSION_NUMBER => 1.0,
802: P_INIT_MSG_LIST => fnd_api.g_false,
803: P_COMMIT => fnd_api.g_false,
804: P_VALIDATION_LEVEL => fnd_api.g_valid_level_full,
805: P_ACCT_ID => l_na_id,
806: P_PARTY_ID => l_party_id,
799: IF l_match_rule_code = '1' THEN
800: JTF_TTY_MAINTAIN_NA_PVT.create_acct_mappings
801: (P_API_VERSION_NUMBER => 1.0,
802: P_INIT_MSG_LIST => fnd_api.g_false,
803: P_COMMIT => fnd_api.g_false,
804: P_VALIDATION_LEVEL => fnd_api.g_valid_level_full,
805: P_ACCT_ID => l_na_id,
806: P_PARTY_ID => l_party_id,
807: P_PARTY_SITE_ID => p_party_site_id,
800: JTF_TTY_MAINTAIN_NA_PVT.create_acct_mappings
801: (P_API_VERSION_NUMBER => 1.0,
802: P_INIT_MSG_LIST => fnd_api.g_false,
803: P_COMMIT => fnd_api.g_false,
804: P_VALIDATION_LEVEL => fnd_api.g_valid_level_full,
805: P_ACCT_ID => l_na_id,
806: P_PARTY_ID => l_party_id,
807: P_PARTY_SITE_ID => p_party_site_id,
808: P_USER_ID => l_user_id,
906: */
907: x_terr_grp_id := l_tg_id;
908: x_terr_grp_acct_id := l_tga_id;
909: x_named_acct_id := l_na_id;
910: x_return_status := fnd_api.g_ret_sts_success;
911:
912: EXCEPTION
913: WHEN OTHERS THEN
914: l_error_msg := substr(sqlerrm,1,200);
1000:
1001: --Procedure to validate resource, called by populate sales team
1002: PROCEDURE VALIDATE_RESOURCE (
1003: P_API_VERSION_NUMBER IN NUMBER,
1004: P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1005: P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
1006: P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1007: X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1008: X_MSG_COUNT OUT NOCOPY NUMBER,
1001: --Procedure to validate resource, called by populate sales team
1002: PROCEDURE VALIDATE_RESOURCE (
1003: P_API_VERSION_NUMBER IN NUMBER,
1004: P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1005: P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
1006: P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1007: X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1008: X_MSG_COUNT OUT NOCOPY NUMBER,
1009: X_MSG_DATA OUT NOCOPY VARCHAR2,
1002: PROCEDURE VALIDATE_RESOURCE (
1003: P_API_VERSION_NUMBER IN NUMBER,
1004: P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1005: P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
1006: P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1007: X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1008: X_MSG_COUNT OUT NOCOPY NUMBER,
1009: X_MSG_DATA OUT NOCOPY VARCHAR2,
1010: P_RESOURCE_NAME IN VARCHAR2,
1052:
1053: BEGIN
1054:
1055: x_status := 'S';
1056: x_return_status := FND_API.G_RET_STS_SUCCESS;
1057: l_user_id := fnd_global.user_id;
1058:
1059: IF (P_RESOURCE_NAME is not null AND P_GROUP_NAME is not null AND P_ROLE_NAME is not null) THEN
1060:
1069: counter := counter +1;
1070: x_group_id := group_rec.group_id; -- group_id assigned
1071: IF counter=2 THEN
1072: x_status := 'E';
1073: x_return_status := FND_API.G_RET_STS_ERROR ;
1074: fnd_message.clear;
1075: fnd_message.set_name ('JTF', 'JTF_TTY_NON_UNIQUE_GROUP_NAME');
1076: x_error_code := 1;
1077: RETURN;
1087: counter := counter +1;
1088: x_role_code := role_rec.role_code; -- role_code assigned
1089: IF counter=2 THEN
1090: x_status := 'E';
1091: x_return_status := FND_API.G_RET_STS_ERROR ;
1092: fnd_message.clear;
1093: fnd_message.set_name ('JTF', 'JTF_TTY_NON_UNIQUE_ROLE_NAME');
1094: x_error_code := 1;
1095: RETURN;
1101:
1102: EXCEPTION
1103: WHEN NO_DATA_FOUND THEN
1104: x_status := 'E';
1105: x_return_status := FND_API.G_RET_STS_ERROR ;
1106: fnd_message.clear;
1107: fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
1108: x_error_code := 1;
1109: RETURN;
1190:
1191: EXCEPTION
1192: WHEN NO_DATA_FOUND THEN
1193: x_status := 'E';
1194: x_return_status := FND_API.G_RET_STS_ERROR ;
1195: fnd_message.clear;
1196: fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
1197: x_error_code := 1;
1198: RETURN;
1197: x_error_code := 1;
1198: RETURN;
1199: WHEN TOO_MANY_ROWS THEN
1200: x_status := 'E';
1201: x_return_status := FND_API.G_RET_STS_ERROR ;
1202: fnd_message.clear;
1203: fnd_message.set_name ('JTF', 'JTF_TTY_NON_UNIQUE_SALES_DATA');
1204: x_error_code := 1;
1205: RETURN;
1204: x_error_code := 1;
1205: RETURN;
1206: WHEN OTHERS THEN
1207: x_status := 'E';
1208: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1209: x_error_code := 4;
1210: l_error_msg := substr(sqlerrm,1,200);
1211: fnd_message.clear;
1212: fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
1226: EXCEPTION
1227: WHEN NO_DATA_FOUND THEN
1228: x_status := 'E';
1229: x_error_code := 2;
1230: x_return_status := FND_API.G_RET_STS_ERROR;
1231: fnd_message.clear;
1232: fnd_message.set_name ('JTF', 'JTF_TTY_ROLE_NOT_IN_TG');
1233: RETURN;
1234: END;
1235:
1236: ELSE
1237: x_status := 'E';
1238: x_error_code := 3;
1239: x_return_status := FND_API.G_RET_STS_ERROR;
1240: fnd_message.clear;
1241: fnd_message.set_name ('JTF', 'JTF_TTY_SALES_MANDATORY');
1242: RETURN;
1243: END IF;
1245: EXCEPTION
1246: WHEN OTHERS THEN
1247: x_status := 'E';
1248: x_error_code := 4;
1249: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1250: l_error_msg := substr(sqlerrm,1,200);
1251: fnd_message.clear;
1252: fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
1253: fnd_message.set_token('ERRMSG', l_error_msg );
1259: sales person.*/
1260:
1261: PROCEDURE CHECK_VALID_RESOURCE_ADD (
1262: p_Api_Version_Number IN NUMBER,
1263: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1264: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1265: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1266: X_Return_Status OUT NOCOPY VARCHAR2,
1267: X_Msg_Count OUT NOCOPY NUMBER,
1260:
1261: PROCEDURE CHECK_VALID_RESOURCE_ADD (
1262: p_Api_Version_Number IN NUMBER,
1263: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1264: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1265: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1266: X_Return_Status OUT NOCOPY VARCHAR2,
1267: X_Msg_Count OUT NOCOPY NUMBER,
1268: X_Msg_Data OUT NOCOPY VARCHAR2,
1261: PROCEDURE CHECK_VALID_RESOURCE_ADD (
1262: p_Api_Version_Number IN NUMBER,
1263: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1264: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1265: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1266: X_Return_Status OUT NOCOPY VARCHAR2,
1267: X_Msg_Count OUT NOCOPY NUMBER,
1268: X_Msg_Data OUT NOCOPY VARCHAR2,
1269: P_RESOURCE_id in number,
1281: l_error_msg VARCHAR2(250);
1282:
1283: BEGIN
1284: -- x_status := 'S';
1285: x_return_status := FND_API.G_RET_STS_SUCCESS;
1286: /* check salesperson for the current TG */
1287: BEGIN
1288: /* find if the resource belongs to the saleshierarchy of the territory group owner(s) since when an
1289: admin is doing upload, he/she is doing as the owner of the TG */
1312: EXCEPTION
1313: WHEN NO_DATA_FOUND THEN
1314: -- x_status := 'E';
1315: x_error_code := 1;
1316: x_return_status := FND_API.G_RET_STS_ERROR;
1317: fnd_message.clear;
1318: fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
1319: RETURN;
1320:
1320:
1321: WHEN OTHERS THEN
1322: -- x_status := 'E';
1323: x_error_code := 4;
1324: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1325: put_jty_log('Error in CHECK_VALID_RESOURCE_ADD: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
1326: l_error_msg := substr(sqlerrm,1,200);
1327: fnd_message.clear;
1328: fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
1337: sales person.*/
1338:
1339: PROCEDURE GET_RESOURCE_OWNERS (
1340: p_Api_Version_Number IN NUMBER,
1341: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1342: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1343: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1344: X_Return_Status OUT NOCOPY VARCHAR2,
1345: X_Msg_Count OUT NOCOPY NUMBER,
1338:
1339: PROCEDURE GET_RESOURCE_OWNERS (
1340: p_Api_Version_Number IN NUMBER,
1341: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1342: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1343: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1344: X_Return_Status OUT NOCOPY VARCHAR2,
1345: X_Msg_Count OUT NOCOPY NUMBER,
1346: X_Msg_Data OUT NOCOPY VARCHAR2,
1339: PROCEDURE GET_RESOURCE_OWNERS (
1340: p_Api_Version_Number IN NUMBER,
1341: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1342: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1343: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1344: X_Return_Status OUT NOCOPY VARCHAR2,
1345: X_Msg_Count OUT NOCOPY NUMBER,
1346: X_Msg_Data OUT NOCOPY VARCHAR2,
1347: P_TERR_GP_ID in number,
1404:
1405: BEGIN
1406: -- x_status := 'S';
1407: x_rscs_owners_tbl := SALESREP_RSC_OWNERS_TBL_TYPE();
1408: x_return_status := FND_API.G_RET_STS_SUCCESS;
1409: /* check salesperson for the current TG */
1410: j := 0;
1411: FOR i in P_RESOURCES_TBL.first.. P_RESOURCES_TBL.last LOOP
1412: if (i = 1) then
1517: EXCEPTION
1518: WHEN NO_DATA_FOUND THEN
1519: -- x_status := 'E';
1520: x_error_code := 1;
1521: x_return_status := FND_API.G_RET_STS_ERROR;
1522: fnd_message.clear;
1523: fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
1524: RETURN;
1525:
1525:
1526: WHEN OTHERS THEN
1527: -- x_status := 'E';
1528: x_error_code := 4;
1529: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1530: l_error_msg := substr(sqlerrm,1,200);
1531: fnd_message.clear;
1532: fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
1533: fnd_message.set_token('ERRMSG', l_error_msg );
1540: /* If the account is not assigned to anyone in the tg owners hierarchy
1541: assign to the owner(s) */
1542: PROCEDURE ASSIGN_UA_ACCT_TO_TGOWNERS(
1543: p_Api_Version_Number IN NUMBER,
1544: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1545: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1546: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1547: X_Return_Status OUT NOCOPY VARCHAR2,
1548: X_Msg_Count OUT NOCOPY NUMBER,
1541: assign to the owner(s) */
1542: PROCEDURE ASSIGN_UA_ACCT_TO_TGOWNERS(
1543: p_Api_Version_Number IN NUMBER,
1544: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1545: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1546: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1547: X_Return_Status OUT NOCOPY VARCHAR2,
1548: X_Msg_Count OUT NOCOPY NUMBER,
1549: X_Msg_Data OUT NOCOPY VARCHAR2,
1542: PROCEDURE ASSIGN_UA_ACCT_TO_TGOWNERS(
1543: p_Api_Version_Number IN NUMBER,
1544: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1545: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1546: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1547: X_Return_Status OUT NOCOPY VARCHAR2,
1548: X_Msg_Count OUT NOCOPY NUMBER,
1549: X_Msg_Data OUT NOCOPY VARCHAR2,
1550: P_TERR_GP_ACCT_ID IN NUMBER,
1564: l_error_msg VARCHAR2(250);
1565:
1566: BEGIN
1567: -- dbms_output.put_line('Sandeep - In ASSIGN_UA_ACCT_TO_TGOWNERS ' || P_TERR_GP_ACCT_ID );
1568: x_return_status := FND_API.G_RET_STS_SUCCESS;
1569: FOR owners_c IN getTerrgpOwners(p_terr_gp_id) LOOP
1570: p_owner_user_rsc_id := owners_c.resource_id;
1571: p_owner_group_id := owners_c.rsc_group_id;
1572: p_owner_role_code :=owners_c.rsc_role_code;
1620: END;
1621: END LOOP;
1622: EXCEPTION
1623: WHEN OTHERS THEN
1624: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1625: -- dbms_output.put_line('Sandeep - Error 2 In ASSIGN_UA_ACCT_TO_TGOWNERS ' || P_TERR_GP_ACCT_ID );
1626: l_error_msg := substr(sqlerrm,1,200);
1627: fnd_message.clear;
1628: fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
1645: -- lp_territory_group_id
1646:
1647: PROCEDURE POPULATE_SALES_TEAM(
1648: p_api_version_number IN NUMBER,
1649: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1650: p_SQL_Trace IN VARCHAR2,
1651: p_Debug_Flag IN VARCHAR2,
1652: x_return_status OUT NOCOPY VARCHAR2,
1653: x_msg_count OUT NOCOPY NUMBER,
1793:
1794:
1795: l_user := fnd_global.USER_ID;
1796: l_login_id := fnd_global.LOGIN_ID;
1797: x_return_status := fnd_api.g_ret_sts_success;
1798:
1799:
1800:
1801: -- tag all incoming accounts as non-subsidiary record
2205:
2206: exception
2207:
2208: when others then
2209: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2210: l_error_msg := substr(sqlerrm,1,200);
2211: fnd_message.clear;
2212: fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
2213: fnd_message.set_token('ERRMSG', l_error_msg );
2222: */
2223:
2224: PROCEDURE VALIDATE_SALES_TEAM(
2225: p_Api_Version_Number IN NUMBER,
2226: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2227: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2228: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2229: X_Return_Status OUT NOCOPY VARCHAR2,
2230: X_Msg_Count OUT NOCOPY NUMBER,
2223:
2224: PROCEDURE VALIDATE_SALES_TEAM(
2225: p_Api_Version_Number IN NUMBER,
2226: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2227: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2228: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2229: X_Return_Status OUT NOCOPY VARCHAR2,
2230: X_Msg_Count OUT NOCOPY NUMBER,
2231: X_Msg_Data OUT NOCOPY VARCHAR2,
2224: PROCEDURE VALIDATE_SALES_TEAM(
2225: p_Api_Version_Number IN NUMBER,
2226: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2227: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2228: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2229: X_Return_Status OUT NOCOPY VARCHAR2,
2230: X_Msg_Count OUT NOCOPY NUMBER,
2231: X_Msg_Data OUT NOCOPY VARCHAR2,
2232: P_TERR_GP_ID IN NUMBER,
2250: l_rsc_start_date date;
2251: l_rsc_end_date date;
2252: BEGIN
2253: -- dbms_output.put_line('Sandeep - Begin update sales team: after validate resource');
2254: x_return_status := FND_API.G_RET_STS_SUCCESS;
2255: x_added_rscs_tbl := SALESREP_RSC_TBL_TYPE();
2256: if l_excel_rscs_tbl.FIRST is not null THEN
2257: FOR i in l_excel_rscs_tbl.first.. l_excel_rscs_tbl.last LOOP
2258: if l_excel_rscs_tbl(i).resource_name is not null
2300: IF l_excel_rscs_tbl(i).resource_start_date is null THEN
2301: x_added_rscs_tbl(j).resource_start_date := P_START_DATE;
2302: ELSE
2303: IF (l_excel_rscs_tbl(i).resource_start_date < P_START_DATE) THEN
2304: x_return_status := fnd_api.g_ret_sts_error;
2305: fnd_message.clear;
2306: fnd_message.set_name ('JTF', 'JTY_RSC_STARTDATE_NOT_VALID');
2307: FND_MESSAGE.Set_Token ('RES_NAME', i ||', '||l_excel_rscs_tbl(i).resource_name||',');
2308: return;
2316: ELSE
2317: IF (l_excel_rscs_tbl(i).resource_end_date BETWEEN p_start_date and P_end_date) THEN
2318: x_added_rscs_tbl(j).resource_end_date := l_excel_rscs_tbl(i).resource_end_date;
2319: ELSE
2320: x_return_status := fnd_api.g_ret_sts_error;
2321: fnd_message.clear;
2322: fnd_message.set_name ('JTF', 'JTY_RSC_ENDDATE_NOT_VALID');
2323: FND_MESSAGE.Set_Token ('RES_NAME', i ||', '||l_excel_rscs_tbl(i).resource_name||',');
2324: return;
2329:
2330: -- dbms_output.put_line('Sandeep - after inserting to x_added_rscs_tbl');
2331: elsif x_status = 'I' then NULL;
2332: else
2333: x_return_status := fnd_api.g_ret_sts_error;
2334: -- dbms_output.put_line('Sandeep - error in update sales team: after validate resource' );
2335: if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', i); end if;
2336: return;
2337: end if;
2340: END IF;
2341: -- -- dbms_output.put_line('Sandeep - ');
2342: EXCEPTION
2343: WHEN OTHERS THEN
2344: x_return_status := fnd_api.g_ret_sts_unexp_error;
2345: -- dbms_output.put_line('Sandeep - ' || sqlerrm);
2346: l_error_msg := substr(sqlerrm,1,200);
2347: fnd_message.clear;
2348: fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
2358: * needed.
2359: */
2360: PROCEDURE UPDATE_SALES_TEAM (
2361: p_Api_Version_Number IN NUMBER,
2362: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2363: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2364: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2365: X_Return_Status OUT NOCOPY VARCHAR2,
2366: X_Msg_Count OUT NOCOPY NUMBER,
2359: */
2360: PROCEDURE UPDATE_SALES_TEAM (
2361: p_Api_Version_Number IN NUMBER,
2362: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2363: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2364: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2365: X_Return_Status OUT NOCOPY VARCHAR2,
2366: X_Msg_Count OUT NOCOPY NUMBER,
2367: X_Msg_Data OUT NOCOPY VARCHAR2,
2360: PROCEDURE UPDATE_SALES_TEAM (
2361: p_Api_Version_Number IN NUMBER,
2362: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2363: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2364: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2365: X_Return_Status OUT NOCOPY VARCHAR2,
2366: X_Msg_Count OUT NOCOPY NUMBER,
2367: X_Msg_Data OUT NOCOPY VARCHAR2,
2368: p_added_rscs_tbl IN SALESREP_RSC_TBL_TYPE,
2440: l_affected_parties_tbl.extend;
2441: l_affected_parties_tbl(1).terr_group_account_id := P_TERR_GP_ACCT_ID;
2442: l_affected_parties_tbl(1).attribute1 := 'N';
2443: /* get all the owners for this TG */
2444: X_Return_Status := FND_API.G_RET_STS_SUCCESS;
2445: for owners in get_owners_c(P_TERR_GP_ID) LOOP
2446: j := j+1;
2447: l_owners_tbl.extend();
2448: l_owners_tbl(j).resource_id := owners.resource_id;
2471: , x_status => x_status );
2472: end if;
2473: -- dbms_output.put_line('Sandeep - In update sales team: after gt resource owners ' || X_Return_Status);
2474: IF ( l_added_rscs_tbl.FIRST is not null) THEN
2475: IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
2476: if l_added_rscs_tbl.FIRST is not null
2477: then
2478: -- dbms_output.put_line('Sandeep - In update sales team: data in added rscs table ');
2479: for j in l_added_rscs_tbl.FIRST..l_added_rscs_tbl.LAST
2496: end loop;
2497: end if;
2498: end loop;
2499: else
2500: x_return_status := FND_API.G_RET_STS_ERROR;
2501: fnd_message.clear;
2502: fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
2503: FND_MESSAGE.Set_Token ('POSITION', l_added_rscs_tbl(j).attribute2);
2504: RETURN;
2565: l_add_rscs_tbl(n).mgr_role_code := '-999';
2566: l_add_rscs_tbl(n).resource_type := 'RS_EMPLOYEE';
2567: end;
2568: else
2569: x_return_status := FND_API.G_RET_STS_ERROR;
2570: fnd_message.clear;
2571: fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
2572: FND_MESSAGE.Set_Token ('POSITION', l_added_rscs_tbl(j).attribute2);
2573: RETURN;
2574: end if;
2575: end loop;
2576: end if;
2577: ELSE
2578: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2579: -- dbms_output.put_line('Sandeep - ERror 2 In populate sales team');
2580: l_error_msg := substr(sqlerrm,1,200);
2581: fnd_message.clear;
2582: fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
2584: put_jty_log('Error in UPDATE_SALES_TEAM: ' || SQLERRM, 'ORACLE.APPS.ORACLE.APPS.ORACLE.APPS.JTF.JTF_TTY_MAINTAIN_NA_PVT', FND_LOG.LEVEL_UNEXPECTED);
2585: return;
2586: END IF;
2587: END IF;
2588: IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
2589: for i in l_owners_tbl.FIRST..l_owners_tbl.LAST loop
2590: if (l_owners_tbl(i).delete_flag = 'N') THEN
2591: -- dbms_output.put_line ('In owner tbl');
2592: l_add_rscs_tbl.extend;
2636: l_removed_rscs_tbl(l_delete_count).mgr_role_code := '-999';
2637: l_removed_rscs_tbl(l_delete_count).resource_type := 'RS_EMPLOYEE';
2638: Exception
2639: when others then
2640: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2641: -- dbms_output.put_line('Sandeep - Error In populate sales team: removed sales persons processing');
2642: l_error_msg := substr(sqlerrm,1,200);
2643: fnd_message.clear;
2644: fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
2680: when no_data_found then
2681: fnd_message.clear;
2682: fnd_message.set_name ('JTF', 'JTF_TTY_NA_NOT_ASSIGED');
2683: -- dbms_output.put_line('Sandeep - ERror 1 In populate sales team');
2684: x_return_status := FND_API.G_RET_STS_ERROR;
2685: return;
2686:
2687: when others then
2688: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2684: x_return_status := FND_API.G_RET_STS_ERROR;
2685: return;
2686:
2687: when others then
2688: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2689: -- dbms_output.put_line('Sandeep - ERror 2 In populate sales team');
2690: l_error_msg := substr(sqlerrm,1,200);
2691: fnd_message.clear;
2692: fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
2701: * APIs called: Populate Sales Team
2702: */
2703: PROCEDURE ADD_SALES_TEAM (
2704: p_Api_Version_Number IN NUMBER,
2705: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2706: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2707: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2708: X_Return_Status OUT NOCOPY VARCHAR2,
2709: X_Msg_Count OUT NOCOPY NUMBER,
2702: */
2703: PROCEDURE ADD_SALES_TEAM (
2704: p_Api_Version_Number IN NUMBER,
2705: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2706: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2707: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2708: X_Return_Status OUT NOCOPY VARCHAR2,
2709: X_Msg_Count OUT NOCOPY NUMBER,
2710: X_Msg_Data OUT NOCOPY VARCHAR2,
2703: PROCEDURE ADD_SALES_TEAM (
2704: p_Api_Version_Number IN NUMBER,
2705: p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2706: p_Commit IN VARCHAR2 := FND_API.G_FALSE,
2707: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2708: X_Return_Status OUT NOCOPY VARCHAR2,
2709: X_Msg_Count OUT NOCOPY NUMBER,
2710: X_Msg_Data OUT NOCOPY VARCHAR2,
2711: p_added_rscs_tbl IN SALESREP_RSC_TBL_TYPE,
2797: x_rscs_owners_tbl => l_rscs_owners_tbl
2798: , x_error_code => x_error_code
2799: , x_status => x_status );
2800: -- dbms_output.put_line('Sandeep - Start of Add sales team status ' || X_Return_Status);
2801: IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
2802: if l_added_rscs_tbl.FIRST is not null
2803: then
2804: -- dbms_output.put_line('Sandeep - looping added resources table ');
2805: for j in l_added_rscs_tbl.FIRST..l_added_rscs_tbl.LAST
2824: end loop;
2825: end if;
2826: end loop; /* end of looping resource's owners table */
2827: else
2828: x_return_status := FND_API.G_RET_STS_ERROR;
2829: -- dbms_output.put_line('Sandeep - No Owners found');
2830: fnd_message.clear;
2831: fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
2832: FND_MESSAGE.Set_Token ('POSITION', l_added_rscs_tbl(j).attribute2);
2856: l_add_rscs_tbl(n).mgr_group_id := -999;
2857: l_add_rscs_tbl(n).mgr_role_code := '-999';
2858: l_add_rscs_tbl(n).resource_type := 'RS_EMPLOYEE';
2859: else
2860: x_return_status := FND_API.G_RET_STS_ERROR;
2861: fnd_message.clear;
2862: fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
2863: FND_MESSAGE.Set_Token ('POSITION', l_added_rscs_tbl(j).attribute2);
2864: RETURN;
2865: end if;
2866: end loop; /* end of looping l_added_rscs_tbl */
2867: end if; /* end of if l_added_rscs_tbl.first is not null */
2868: ELSE /* if GET_RESOURCE_OWNERS returned error */
2869: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2870: -- dbms_output.put_line('Sandeep - ERror 2 In populate sales team');
2871: l_error_msg := substr(sqlerrm,1,200);
2872: fnd_message.clear;
2873: fnd_message.set_name ('JTF', 'JTF_TTY_ERROR');
2874: fnd_message.set_token('ERRMSG', l_error_msg );
2875: return;
2876: END IF;
2877: -- dbms_output.put_line('Sandeep - 222');
2878: IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
2879: for i in l_owners_tbl.FIRST..l_owners_tbl.LAST loop
2880: if (l_owners_tbl(i).delete_flag = 'N') THEN
2881: l_add_rscs_tbl.extend;
2882: n := n + 1;
2926: when no_data_found then
2927: fnd_message.clear;
2928: fnd_message.set_name ('JTF', 'JTF_TTY_NA_NOT_ASSIGED');
2929: -- dbms_output.put_line('Sandeep - ERror 1 In populate sales team');
2930: x_return_status := FND_API.G_RET_STS_ERROR;
2931: return;
2932:
2933: when others then
2934: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2930: x_return_status := FND_API.G_RET_STS_ERROR;
2931: return;
2932:
2933: when others then
2934: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2935: -- dbms_output.put_line('Sandeep ' || SQLERRM);
2936: -- dbms_output.put_line('Sandeep - ERror 2 In populate sales team');
2937: l_error_msg := substr(sqlerrm,1,200);
2938: fnd_message.clear;
3295: x_new_named_acct_id NUMBER;
3296: x_to_tg_id NUMBER;
3297: x_to_tg_acct_id NUMBER;
3298: p_Api_Version_Number NUMBER := 1.0;
3299: p_Init_Msg_List VARCHAR2(1) := FND_API.G_FALSE;
3300: p_Commit VARCHAR2(1) := FND_API.G_FALSE;
3301: p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
3302: X_Return_Status VARCHAR2(1);
3303: X_Msg_Count NUMBER;
3296: x_to_tg_id NUMBER;
3297: x_to_tg_acct_id NUMBER;
3298: p_Api_Version_Number NUMBER := 1.0;
3299: p_Init_Msg_List VARCHAR2(1) := FND_API.G_FALSE;
3300: p_Commit VARCHAR2(1) := FND_API.G_FALSE;
3301: p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
3302: X_Return_Status VARCHAR2(1);
3303: X_Msg_Count NUMBER;
3304: l_party_site_id NUMBER;
3297: x_to_tg_acct_id NUMBER;
3298: p_Api_Version_Number NUMBER := 1.0;
3299: p_Init_Msg_List VARCHAR2(1) := FND_API.G_FALSE;
3300: p_Commit VARCHAR2(1) := FND_API.G_FALSE;
3301: p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
3302: X_Return_Status VARCHAR2(1);
3303: X_Msg_Count NUMBER;
3304: l_party_site_id NUMBER;
3305: X_Msg_Data VARCHAR2(2000);
3444: /*BUG8209345*/
3445:
3446: GET_TERR_GRP_ACCT_DETAILS(
3447: p_Api_Version_Number => 1.0 ,
3448: p_Init_Msg_List => FND_API.G_FALSE ,
3449: p_Commit => FND_API.G_FALSE ,
3450: p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
3451: X_Return_Status => X_Return_Status ,
3452: X_Msg_Count => X_Msg_Count ,
3445:
3446: GET_TERR_GRP_ACCT_DETAILS(
3447: p_Api_Version_Number => 1.0 ,
3448: p_Init_Msg_List => FND_API.G_FALSE ,
3449: p_Commit => FND_API.G_FALSE ,
3450: p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
3451: X_Return_Status => X_Return_Status ,
3452: X_Msg_Count => X_Msg_Count ,
3453: X_Msg_Data => X_Msg_Data,
3446: GET_TERR_GRP_ACCT_DETAILS(
3447: p_Api_Version_Number => 1.0 ,
3448: p_Init_Msg_List => FND_API.G_FALSE ,
3449: p_Commit => FND_API.G_FALSE ,
3450: p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
3451: X_Return_Status => X_Return_Status ,
3452: X_Msg_Count => X_Msg_Count ,
3453: X_Msg_Data => X_Msg_Data,
3454: P_PARTY_NUMBER => P_PARTY_NUMBER,
3474: X_TERR_GRP_ID => X_FROM_TG_ID,
3475: X_TERR_GRP_ACCT_ID => X_FROM_TG_ACCT_ID,
3476: X_NAMED_ACCT_ID => X_NAMED_ACCT_ID);
3477:
3478: IF (X_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
3479: RETURN; /* unable to get terr gp acct details due to wrong party number
3480: --* wrong tg name or party not a na in the tg */
3481: END IF;
3482:
3944: L_END_DATE,
3945: l_excel_rscs_tbl ,
3946: l_added_rscs_tbl);
3947: -- dbms_output.put_line('Sandeep - After doing validate Sales Team' || X_Return_Status);
3948: IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
3949: UPDATE_SALES_TEAM(P_Api_Version_Number ,
3950: p_Init_Msg_List ,
3951: p_Commit ,
3952: p_validation_level ,
4001: x_terr_grp_id => x_to_tg_id,
4002: x_named_acct_id => x_named_acct_id);
4003:
4004: -- dbms_output.put_line('Sandeep - Add to Org/Add to Org with update sales Team -- After Add Org to TG: Status ' || X_Return_Status);
4005: IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN /* add of org is successful */
4006: if (p_salesperson_flag = 'N') THEN
4007: /* Add Org to TG, assign the account to territory group owners*/
4008:
4009: ASSIGN_ACCT_TO_TG_OWNERS(
4035: L_END_DATE,
4036: l_excel_rscs_tbl ,
4037: l_added_rscs_tbl);
4038: -- dbms_output.put_line('Sandeep - Add Org to TG with update Sales Team After doing validate Sales Team: Status ' || X_Return_Status);
4039: IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
4040: BEGIN
4041: -- dbms_output.put_line('Sandeep - Add Org to TG with update Sales Team Before ADD_SALES_TEAM');
4042: ADD_SALES_TEAM(P_Api_Version_Number ,
4043: p_Init_Msg_List ,
4074: X_Msg_Data => X_Msg_Data) ;
4075:
4076:
4077:
4078: IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
4079: /* i.e. delete was successful, so move to add org to To Tg */
4080: ADD_ORG_TO_TG(P_Api_Version_Number => P_Api_Version_Number,
4081: p_Init_Msg_List => p_Init_Msg_List,
4082: p_Commit => p_Commit,
4107: x_terr_grp_acct_id => x_to_tg_acct_id,
4108: x_terr_grp_id => x_to_tg_id,
4109: x_named_acct_id => x_new_named_acct_id);
4110:
4111: IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN /* add of org is successful */
4112: if (p_salesperson_flag = 'N') THEN
4113: /* Transfer with no update sales team,
4114: * assign the account to territory group owners*/
4115: ASSIGN_ACCT_TO_TG_OWNERS(
4135: L_START_DATE,
4136: L_END_DATE,
4137: l_excel_rscs_tbl ,
4138: l_added_rscs_tbl);
4139: IF (X_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
4140: ADD_SALES_TEAM(P_Api_Version_Number ,
4141: p_Init_Msg_List ,
4142: p_Commit ,
4143: p_validation_level ,