[Home] [Help]
133: l_return_csr NUMBER;
134:
135: BEGIN
136:
137: x_return_status := FND_API.G_RET_STS_SUCCESS;
138: /*
139: update JTY_WEBADI_OTH_TERR_INTF
140: set parent_terr_id = null
141: where user_sequence = p_user_sequence
219: and user_sequence = p_user_sequence
220: and interface_type = p_intf_type;
221:
222: ELSE
223: x_return_status := fnd_api.g_ret_sts_error;
224: fnd_message.clear;
225: fnd_message.set_name('JTF', 'JTY_OTH_TERR_CIR_REF');
226: x_msg_data := fnd_message.GET();
227:
233: AND interface_type = p_intf_type;
234: END IF;
235: EXCEPTION WHEN OTHERS THEN
236: IF SQLCODE = -01436 THEN
237: x_return_status := fnd_api.g_ret_sts_error;
238: fnd_message.clear;
239: fnd_message.set_name('JTF', 'JTY_OTH_TERR_CIR_REF');
240: x_msg_data := fnd_message.GET();
241:
248: END IF;
249: END;
250:
251: else
252: x_return_status := FND_API.G_RET_STS_ERROR;
253: fnd_message.clear;
254: FND_MESSAGE.set_name ('JTF', 'JTY_OTH_TERR_NON_UNIQUE_TERR');
255: FND_MESSAGE.set_token ('POSITION', l_parent);
256: X_Msg_Data := fnd_message.get();
290: where org_id = l_hierarchy_tbl(c).org_id
291: and name = l_parent
292: and parent_territory_id = l_anc_id;
293: else
294: x_return_status := FND_API.G_RET_STS_ERROR;
295: fnd_message.clear;
296: FND_MESSAGE.set_name ('JTF', 'JTY_OTH_TERR_NON_UNIQUE_TERR');
297: FND_MESSAGE.set_token ('POSITION', l_parent);
298: X_Msg_Data := fnd_message.get();
320: end if;
321:
322: exception
323: when others then
324: x_return_status := FND_API.G_RET_STS_ERROR;
325: end get_hierarchy;
326:
327: -- This procedure populates the parent terr id when new territory heirarchy is being created.
328:
346: l_return_csr NUMBER;
347:
348: BEGIN
349:
350: x_return_status := fnd_api.g_ret_sts_success;
351:
352: l_parent_id := 1;
353: l_anc_id := 1;
354: l_terr := p_hierarchy;
390: UPDATE jtf_terr_all
391: SET parent_territory_id = l_parent_id
392: WHERE terr_id = p_terr_id;
393: ELSE
394: x_return_status := fnd_api.g_ret_sts_error;
395: fnd_message.clear;
396: fnd_message.set_name('JTF', 'JTY_OTH_TERR_CIR_REF');
397: x_msg_data := fnd_message.GET();
398:
404: AND interface_type = p_intf_type;
405: END IF;
406: EXCEPTION WHEN OTHERS THEN
407: IF SQLCODE = -01436 THEN
408: x_return_status := fnd_api.g_ret_sts_error;
409: fnd_message.clear;
410: fnd_message.set_name('JTF', 'JTY_OTH_TERR_CIR_REF');
411: x_msg_data := fnd_message.GET();
412:
419: END IF;
420: END;
421:
422: ELSE
423: x_return_status := fnd_api.g_ret_sts_error;
424: fnd_message.clear;
425: fnd_message.set_name('JTF', 'JTY_OTH_TERR_NON_UNIQUE_TERR');
426: fnd_message.set_token('POSITION', l_parent);
427: x_msg_data := fnd_message.GET();
465: AND terr_name = l_parent
466: AND parent_terr_id = l_anc_id;
467:
468: ELSE
469: x_return_status := fnd_api.g_ret_sts_error;
470: fnd_message.clear;
471: fnd_message.set_name('JTF', 'JTY_OTH_TERR_NON_UNIQUE_TERR');
472: fnd_message.set_token('POSITION', l_parent);
473: x_msg_data := fnd_message.GET();
494: -- loop through instr
495:
496: EXCEPTION
497: WHEN others THEN
498: x_return_status := fnd_api.g_ret_sts_error;
499: END populate_parent_id;
500:
501: PROCEDURE VALIDATE_TERRITORY_RECORDS
502: ( P_USER_SEQUENCE IN NUMBER,
1202: qual_rec Qual_Rec_Type;
1203: BEGIN
1204: debugmsg('VALIDATE_TERRITORY_RECORDS: P_USER_SEQUENCE : ' || P_USER_SEQUENCE);
1205: debugmsg('VALIDATE_TERRITORY_RECORDS: P_INTF_TYPE : ' || P_INTF_TYPE);
1206: x_return_status := FND_API.G_RET_STS_ERROR;
1207: fnd_message.clear;
1208: fnd_message.set_name ('JTF', 'JTF_TTY_INVALID_TERR_NAME');
1209: X_Msg_Data := fnd_message.get();
1210: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate JTF_TTY_INVALID_TERR_NAME : ' );
1216: and jwot.INTERFACE_TYPE = p_intf_type
1217: and jwot.status is null;
1218:
1219: -- Fix for bug 8289489. Rank can't be null. This fix can be removed, if webadi raises error for not null columns.
1220: x_return_status := FND_API.G_RET_STS_ERROR;
1221: fnd_message.clear;
1222: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_INVALID_RANK');
1223: X_Msg_Data := fnd_message.get();
1224:
1231: and jwot.INTERFACE_TYPE = p_intf_type
1232: and jwot.status is null;
1233:
1234: -- Fix for bug 8289489. ORG ID can't be null. This fix can be removed, if webadi raises error for not null columns.
1235: x_return_status := FND_API.G_RET_STS_ERROR;
1236: fnd_message.clear;
1237: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_INVALID_ORGID');
1238: X_Msg_Data := fnd_message.get();
1239:
1248:
1249: l_header := 'RSC';
1250: l_action_flag := 'D';
1251:
1252: x_return_status := FND_API.G_RET_STS_ERROR;
1253: fnd_message.clear;
1254: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_INVALID_ID');
1255: X_Msg_Data := fnd_message.get();
1256: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate JTY_OTH_TERR_INVALID_ID : ' );
1308: and jwot.status is null;
1309:
1310: l_action_flag := 'U';
1311:
1312: x_return_status := FND_API.G_RET_STS_ERROR;
1313: fnd_message.clear;
1314: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_INVALID_TEMPLATE');
1315: X_Msg_Data := fnd_message.get();
1316: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate JTY_OTH_TERR_INVALID_TEMPLATE : ' );
1329: and jwot.status is null
1330: AND jwot.TERR_ID is not null;
1331:
1332:
1333: x_return_status := FND_API.G_RET_STS_ERROR;
1334: fnd_message.clear;
1335: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_NO_TEMPLATE');
1336: X_Msg_Data := fnd_message.get();
1337: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate JTY_OTH_TERR_NO_TEMPLATE : ' );
1345: and jwot.status is null;
1346:
1347: -- Commented it as a part of fix for bug #5479649
1348: /*
1349: x_return_status := FND_API.G_RET_STS_ERROR;
1350: fnd_message.clear;
1351: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_INVALID_ACCESS');
1352: X_Msg_Data := fnd_message.get();
1353: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate JTY_OTH_TERR_INVALID_ACCESS : ' );
1399:
1400: -- Fix for bug #5479649 START
1401: -- An error message should be displayed when all the access types
1402: -- are NONE for all the transaction type of a resource.
1403: x_return_status := FND_API.G_RET_STS_ERROR;
1404: fnd_message.clear;
1405: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_INVALID_ACCESS');
1406: X_Msg_Data := fnd_message.get();
1407: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate JTY_OTH_TERR_INVALID_ACCESS : ' );
1432: -- Fix for bug #5479649 END.
1433:
1434: -- Fix for bug # 6372728 START
1435: -- Should display a error message when user tries to add a duplicate reosurce.
1436: x_return_status := FND_API.G_RET_STS_ERROR;
1437: fnd_message.clear;
1438: fnd_message.set_name ('JTF', 'JTF_TTY_DUPLICATE_RESOURCE');
1439: X_Msg_Data := fnd_message.get();
1440: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate JTF_TTY_DUPLICATE_RESOURCE : ' );
1487: or jwr2.res_end_date between jwr1.res_start_date and jwr1.res_end_date )
1488: );
1489: -- Fix for bug # 9124144 END
1490:
1491: x_return_status := FND_API.G_RET_STS_ERROR;
1492: fnd_message.clear;
1493: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_RSC_START_DATE');
1494: X_Msg_Data := fnd_message.get();
1495: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate JTY_OTH_TERR_RSC_START_DATE : ' );
1509: and jwot.interface_type = p_intf_type
1510: and jwot.header = l_header
1511: and jwot.status is null;
1512:
1513: x_return_status := FND_API.G_RET_STS_ERROR;
1514: fnd_message.clear;
1515: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_RSC_END_DATE');
1516: X_Msg_Data := fnd_message.get();
1517: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate JTY_OTH_TERR_RSC_END_DATE : ' );
1533: and jwot.status is null;
1534:
1535:
1536: l_header := 'QUAL';
1537: x_return_status := FND_API.G_RET_STS_ERROR;
1538: fnd_message.clear;
1539: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_INVALID_QUAL');
1540: X_Msg_Data := fnd_message.get();
1541: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate JTY_OTH_TERR_INVALID_QUAL : ' );
1556: end if;
1557:
1558: l_header := 'TERR';
1559:
1560: x_return_status := FND_API.G_RET_STS_ERROR;
1561: fnd_message.clear;
1562: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_START_DATE');
1563: X_Msg_Data := fnd_message.get();
1564: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate JTY_OTH_TERR_START_DATE : ' );
1582: where jwot.parent_terr_id = jta.terr_id
1583: and NOT(jwot.TERR_START_DATE between jta.START_DATE_ACTIVE and jta.END_DATE_ACTIVE))
1584: );
1585:
1586: x_return_status := FND_API.G_RET_STS_ERROR;
1587: fnd_message.clear;
1588: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_END_DATE');
1589: X_Msg_Data := fnd_message.get();
1590: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate JTY_OTH_TERR_END_DATE : ' );
1612: -- Fix for bug #7707288 START
1613:
1614: -- Should display a error message when user tries to create a territory
1615: -- with same name.
1616: x_return_status := FND_API.G_RET_STS_ERROR;
1617: fnd_message.clear;
1618: fnd_message.set_name ('JTF', 'JTY_TTY_DUPLICATE_TERR');
1619: X_Msg_Data := fnd_message.get();
1620: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate JTY_TTY_DUPLICATE_TERR : ' );
1657: -- Fix for bug #8208298 START
1658:
1659: -- Should display a error message when user tries to create a territory
1660: -- with empty Territory DFF.
1661: x_return_status := FND_API.G_RET_STS_ERROR;
1662: fnd_message.clear;
1663: fnd_message.set_name ('JTF', 'JTY_TERR_OTH_MTERR_DFF_NA');
1664: X_Msg_Data := fnd_message.get();
1665: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate JTY_TERR_OTH_MTERR_DFF_NA : ' );
1675:
1676: -- Fix for bug # 8208298 END
1677:
1678:
1679: x_return_status := FND_API.G_RET_STS_ERROR;
1680: fnd_message.clear;
1681: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_NO_ACCESS');
1682: X_Msg_Data := fnd_message.get();
1683: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate JTY_OTH_TERR_NO_ACCESS : ' );
1704:
1705: -- Fix for BUG12758833 by nbombili
1706: --Here Account classification and Oppurtunity classification qualifiers validation has been done.
1707:
1708: x_return_status := FND_API.G_RET_STS_ERROR;
1709: fnd_message.clear;
1710: update JTY_WEBADI_OTH_TERR_INTF jwot
1711: set status = x_return_status,
1712: error_msg = 'Primary Interest and Secondary Interest values are mandatory. Please enter values for Primary and Secondary Interest'
1717: and jwot.status is null;
1718:
1719: --end of the fix by nbombili
1720:
1721: x_return_status := FND_API.G_RET_STS_SUCCESS;
1722: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate x_return_status : ' || x_return_status);
1723: select count(*) into l_count
1724: from JTY_WEBADI_OTH_TERR_INTF jwot
1725: where status is not null
1734: where status is null
1735: and jwot.USER_SEQUENCE = p_USER_SEQUENCE
1736: and jwot.INTERFACE_TYPE = p_intf_type;
1737:
1738: x_return_status := FND_API.G_RET_STS_ERROR;
1739: else
1740: x_return_status := FND_API.G_RET_STS_SUCCESS;
1741: end if;
1742: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate end x_return_status : ' || x_return_status);
1736: and jwot.INTERFACE_TYPE = p_intf_type;
1737:
1738: x_return_status := FND_API.G_RET_STS_ERROR;
1739: else
1740: x_return_status := FND_API.G_RET_STS_SUCCESS;
1741: end if;
1742: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate end x_return_status : ' || x_return_status);
1743: EXCEPTION
1744: WHEN OTHERS THEN
1742: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate end x_return_status : ' || x_return_status);
1743: EXCEPTION
1744: WHEN OTHERS THEN
1745: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate EXCEPTION others : ' || SQLERRM(SQLCODE()));
1746: X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1747: END VALIDATE_TERRITORY_RECORDS;
1748:
1749: PROCEDURE SET_CREATE_RECORDS(
1750: p_user_sequence IN NUMBER,
4145:
4146: BEGIN
4147:
4148: -- process territory deletion
4149: x_return_status := FND_API.G_RET_STS_SUCCESS;
4150: l_header := 'TERR';
4151: open get_del_terr_csr(p_user_sequence, p_action_flag, p_intf_type, l_header);
4152: fetch get_del_terr_csr bulk collect into l_del_terr_rec.terr_id, l_del_terr_rec.lay_seq_num;
4153: close get_del_terr_csr;
4195: and interface_type = p_intf_type;
4196:
4197: EXCEPTION
4198: when others then
4199: x_return_status := FND_API.G_RET_STS_ERROR;
4200: fnd_message.clear;
4201: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_DELETE_TERR');
4202: X_Msg_Data := fnd_message.get();
4203:
4211: END;
4212: end if;
4213:
4214: -- process qualifier deletion
4215: x_return_status := FND_API.G_RET_STS_SUCCESS;
4216: l_header := 'QUAL';
4217: open get_del_qual_val_csr(p_user_sequence, p_action_flag, p_intf_type, l_header);
4218: fetch get_del_qual_val_csr bulk collect into
4219: l_del_qual_val_rec.qual_value_id, l_del_qual_val_rec.lay_seq_num;
4244: and interface_type = p_intf_type;
4245:
4246: EXCEPTION
4247: when others then
4248: x_return_status := FND_API.G_RET_STS_ERROR;
4249: fnd_message.clear;
4250: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_DELETE_QUAL');
4251: X_Msg_Data := fnd_message.get();
4252:
4261: and interface_type = p_intf_type;
4262: END;
4263: end if;
4264:
4265: x_return_status := FND_API.G_RET_STS_SUCCESS;
4266: l_header := 'RSC';
4267: open get_del_rsc_csr(p_user_sequence, p_action_flag, p_intf_type, l_header);
4268: fetch get_del_rsc_csr bulk collect into
4269: l_del_rsc_rec.terr_rsc_id, l_del_rsc_rec.lay_seq_num;
4291: and interface_type = p_intf_type;
4292:
4293: EXCEPTION
4294: when others then
4295: x_return_status := FND_API.G_RET_STS_ERROR;
4296: fnd_message.clear;
4297: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_DELETE_RSC');
4298: X_Msg_Data := fnd_message.get();
4299:
4318: ) IS
4319: l_OVERLAP_ALLOWED_FLAG varchar2(1) := 'Y';
4320: l_terr_qual_rec terr_qual_rec_type := p_terr_qual_rec;
4321: BEGIN
4322: --x_return_status := FND_API.G_RET_STS_SUCCESS;
4323: if (l_Terr_Qual_Rec.TERR_ID.count > 0) then
4324: --dbms_output.put_line('U: get_qual_csr: create MA, rowcount: ' || l_Terr_Qual_Rec.TERR_ID.count);
4325: forall i in l_Terr_Qual_Rec.TERR_ID.first..l_Terr_Qual_Rec.TERR_ID.last
4326: INSERT INTO JTF_TERR_QUAL_ALL(
4352: end if; --get_qual_csr
4353:
4354: EXCEPTION
4355: WHEN OTHERS THEN
4356: x_return_status := FND_API.G_RET_STS_ERROR;
4357: fnd_message.clear;
4358: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_CREATE_QUAL');
4359: X_Msg_Data := fnd_message.get();
4360:
4370: l_terr_values_rec terr_values_rec_type := p_terr_values_rec;
4371:
4372: BEGIN
4373:
4374: x_return_status := fnd_api.g_ret_sts_success;
4375:
4376: IF(l_terr_values_rec.terr_qual_id.COUNT > 0) THEN
4377: FOR i IN l_terr_values_rec.terr_qual_id.FIRST .. l_terr_values_rec.terr_qual_id.LAST
4378: LOOP
4403: IF l_dummy = 'X' THEN
4404: fnd_message.clear;
4405: fnd_message.set_name('JTF', 'JTY_DUP_TRANS_ATTR_VAL');
4406: x_msg_data := fnd_message.GET();
4407: x_return_status := fnd_api.g_ret_sts_error;
4408: END IF;
4409: END IF;
4410:
4411: EXCEPTION --
4409: END IF;
4410:
4411: EXCEPTION --
4412: WHEN others THEN
4413: x_return_status := fnd_api.g_ret_sts_error;
4414: fnd_message.set_name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
4415: fnd_message.set_token('PROC_NAME', 'Check_duplicate_Value');
4416: fnd_message.set_token('ERROR', sqlerrm);
4417: x_msg_data := fnd_message.GET();
4427: l_terr_values_rec terr_values_rec_type := p_terr_values_rec;
4428:
4429: BEGIN
4430:
4431: x_return_status := fnd_api.g_ret_sts_success;
4432: --test('p_terr_values_rec.terr_qual_id.COUNT', p_terr_values_rec.terr_qual_id.COUNT);
4433: IF(l_terr_values_rec.terr_qual_id.COUNT > 0) THEN
4434: FOR i IN l_terr_values_rec.terr_qual_id.FIRST .. l_terr_values_rec.terr_qual_id.LAST
4435: LOOP
4463: IF l_dummy = 'X' THEN
4464: fnd_message.clear;
4465: fnd_message.set_name('JTF', 'JTY_DUP_TRANS_ATTR_VAL');
4466: x_msg_data := fnd_message.GET();
4467: x_return_status := fnd_api.g_ret_sts_error;
4468: END IF;
4469: END IF;
4470:
4471: EXCEPTION --
4469: END IF;
4470:
4471: EXCEPTION --
4472: WHEN others THEN
4473: x_return_status := fnd_api.g_ret_sts_error;
4474: fnd_message.set_name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
4475: fnd_message.set_token('PROC_NAME', 'Check_duplicate_Value');
4476: fnd_message.set_token('ERROR', sqlerrm);
4477: x_msg_data := fnd_message.GET();
4487: l_Terr_Values_Rec Terr_values_rec_type;
4488: l_terr_qual_rec Terr_Qual_Rec_Type := p_terr_qual_rec;
4489:
4490: BEGIN
4491: --x_return_status := FND_API.G_RET_STS_SUCCESS;
4492: IF (l_Terr_Qual_Rec.TERR_QUAL_ID.count > 0) THEN
4493: for i in l_Terr_Qual_Rec.TERR_QUAL_ID.first..l_Terr_Qual_Rec.TERR_QUAL_ID.last loop
4494:
4495: l_Terr_Values_Rec.LAST_UPDATE_DATE(i) := l_Terr_Qual_Rec.LAST_UPDATE_DATE(i);
4614: end loop;
4615: */
4616:
4617: --Check for duplicate values.
4618: x_return_status := FND_API.G_RET_STS_SUCCESS;
4619:
4620: Check_duplicate_Value(
4621: p_Terr_Values_Rec => l_Terr_Values_Rec ,
4622: x_return_status => x_return_status,
4622: x_return_status => x_return_status,
4623: x_msg_data => x_msg_data );
4624:
4625:
4626: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
4627: forall i in l_Terr_Values_Rec.TERR_QUAL_ID.first..l_Terr_Values_Rec.TERR_QUAL_ID.last
4628: INSERT INTO JTF_TERR_VALUES_ALL(
4629: TERR_VALUE_ID,
4630: LAST_UPDATED_BY,
4681: delete from jtf_terr_qual_all where terr_id = l_terr_qual_rec.TERR_ID(i) and terr_qual_id not in (select terr_qual_id from jtf_terr_values_all);
4682:
4683: --dbms_output.put_line(' C: get_qual_csr:TV, actual row processed: '||SQL%ROWCOUNT);
4684:
4685: x_return_status := FND_API.G_RET_STS_SUCCESS;
4686: END IF;
4687: END IF; -- TERR_QUAL_ID count
4688: EXCEPTION
4689: WHEN OTHERS THEN
4686: END IF;
4687: END IF; -- TERR_QUAL_ID count
4688: EXCEPTION
4689: WHEN OTHERS THEN
4690: x_return_status := FND_API.G_RET_STS_ERROR;
4691: fnd_message.clear;
4692: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_CREATE_QUAL_VAL');
4693: X_Msg_Data := fnd_message.get();
4694:
4802: l_intf_type varchar2(1) := 'U';
4803:
4804: BEGIN
4805:
4806: x_return_status := FND_API.G_RET_STS_SUCCESS;
4807:
4808: open get_terr_all_csr( p_user_sequence, l_header, p_action_flag, l_intf_type);
4809: fetch get_terr_all_csr bulk collect into l_terr_all_rec.TERR_ID,
4810: l_terr_all_rec.LAST_UPDATE_DATE, l_terr_all_rec.LAST_UPDATED_BY,
4956: and user_sequence = p_user_sequence;
4957:
4958: end if;
4959:
4960: x_return_status := FND_API.G_RET_STS_SUCCESS;
4961: if (p_action_flag = 'U' and l_terr_all_rec.TERR_ID.count > 0) then
4962:
4963: forall i in l_terr_all_rec.TERR_ID.first..l_terr_all_rec.TERR_ID.last
4964: Update JTF_TERR_ALL
5005:
5006: end if;
5007: EXCEPTION
5008: WHEN OTHERS THEN
5009: X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
5010:
5011: fnd_message.clear;
5012: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_CREATE_TERR');
5013: X_Msg_Data := fnd_message.get();
9915: l_header varchar2(15) := 'QUAL';
9916: l_action_flag varchar2(1);
9917:
9918: BEGIN
9919: x_return_status := FND_API.G_RET_STS_SUCCESS;
9920: x_msg_data := NULL;
9921: debugmsg('UPDATE_TERR_DEF: UPDATE_TERR_QUAL : Inside' );
9922: --l_action_flag := p_action_flag;
9923: l_action_flag := 'C';
9944: p_Terr_Qual_Rec => l_Terr_Qual_Rec,
9945: x_return_status => x_return_status,
9946: x_msg_data => x_msg_data);
9947: debugmsg('UPDATE_TERR_DEF: INSERT_TERR_QUAL : After completion x_return_status : ' || x_return_status);
9948: if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
9949: debugmsg('UPDATE_TERR_DEF: INSERT_TERR_VALUES : Before ' );
9950: INSERT_TERR_VALUES(
9951: p_Terr_Qual_Rec => l_terr_qual_rec,
9952: p_terr_values_out_rec => l_terr_values_out_rec,
10038: and user_sequence = p_user_sequence
10039: and header = l_header
10040: and interface_type = l_intf_type;
10041:
10042: if x_return_status = FND_API.G_RET_STS_ERROR then
10043: forall i in l_Terr_Qual_Rec.TERR_QUAL_ID.first..l_Terr_Qual_Rec.TERR_QUAL_ID.last
10044: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
10045: SET STATUS = x_return_status,
10046: ERROR_MSG = x_msg_data
10082:
10083: end if; -- terr_id count
10084: l_action_flag := 'U';
10085: debugmsg('UPDATE_TERR_DEF: UPDATE_TERR_QUAL : Inside and Action Update' );
10086: x_return_status := FND_API.G_RET_STS_SUCCESS;
10087: open get_qual_csr(P_USER_SEQUENCE, l_action_flag, l_intf_type, l_header);
10088: fetch get_qual_csr bulk collect into
10089: l_Terr_Qual_Rec.TERR_QUAL_ID, l_Terr_Qual_Rec.terr_id,
10090: l_Terr_Qual_Rec.qual_value_id, l_Terr_Qual_Rec.qual_value1,
10103: p_Terr_Qual_Rec => l_Terr_Qual_Rec,
10104: x_return_status => x_return_status,
10105: x_msg_data => x_msg_data);
10106:
10107: if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
10108: INSERT_TERR_VALUES(
10109: p_Terr_Qual_Rec => l_terr_qual_rec,
10110: p_terr_values_out_rec => l_terr_values_out_rec,
10111: x_return_status => x_return_status,
10194: and user_sequence = p_user_sequence
10195: and header = l_header
10196: and interface_type = l_intf_type;
10197:
10198: if x_return_status = FND_API.G_RET_STS_ERROR then
10199: forall i in l_Terr_Qual_Rec.TERR_QUAL_ID.first..l_Terr_Qual_Rec.TERR_QUAL_ID.last
10200: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
10201: SET STATUS = x_return_status,
10202: ERROR_MSG = x_msg_data
10235: and user_sequence = p_user_sequence;
10236: end if;
10237: end if; -- terr_id count
10238:
10239: x_return_status := FND_API.G_RET_STS_SUCCESS;
10240: debugmsg('UPDATE_TERR_DEF: UPDATE_TERR_QUAL : get_c_terr_value_csr before' );
10241: debugmsg('UPDATE_TERR_DEF: UPDATE_TERR_QUAL : get_c_terr_value_csr l_action_flag ' || l_action_flag);
10242: open get_c_terr_value_csr(P_USER_SEQUENCE, l_intf_type, l_header);
10243: fetch get_c_terr_value_csr bulk collect into
10259: p_terr_values_out_rec => l_terr_values_out_rec,
10260: x_return_status => x_return_status,
10261: x_msg_data => x_msg_data);
10262: debugmsg('UPDATE_TERR_DEF: UPDATE_TERR_QUAL : get_c_terr_value_csr after : INSERT_TERR_VALUES : x_return_status ' || x_return_status );
10263: if x_return_status = FND_API.G_RET_STS_ERROR then
10264: forall i in l_Terr_Qual_Rec.TERR_QUAL_ID.first..l_Terr_Qual_Rec.TERR_QUAL_ID.last
10265: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
10266: SET STATUS = x_return_status,
10267: ERROR_MSG = x_msg_data
10318: if (l_Terr_Qual_Rec.TERR_QUAL_ID.count > 0) then
10319: BEGIN
10320: --dbms_output.put_line('U: get_u_terr_value_csr: update TV, rowcount: ' || l_Terr_Qual_Rec.TERR_QUAL_ID.count);
10321:
10322: x_return_status := FND_API.G_RET_STS_SUCCESS;
10323: for i in l_Terr_Qual_Rec.TERR_QUAL_ID.first..l_Terr_Qual_Rec.TERR_QUAL_ID.last loop
10324:
10325: l_Terr_Values_Rec.LAST_UPDATE_DATE(i) := l_Terr_Qual_Rec.LAST_UPDATE_DATE(i);
10326: l_Terr_Values_Rec.LAST_UPDATED_BY(i) := l_Terr_Qual_Rec.LAST_UPDATED_BY(i);
10410: end case;
10411: end loop;
10412:
10413: -- Check for duplicate value.
10414: x_return_status := FND_API.G_RET_STS_SUCCESS;
10415: Check_duplicate_Value_update(
10416: p_Terr_Values_Rec => l_Terr_Values_Rec ,
10417: x_return_status => x_return_status,
10418: x_msg_data => x_msg_data );
10416: p_Terr_Values_Rec => l_Terr_Values_Rec ,
10417: x_return_status => x_return_status,
10418: x_msg_data => x_msg_data );
10419:
10420: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
10421: forall i in l_Terr_Values_Rec.TERR_VALUE_ID.first..l_Terr_Values_Rec.TERR_VALUE_ID.last
10422: Update JTF_TERR_VALUES_ALL
10423: SET
10424: LAST_UPDATED_BY = l_Terr_Values_Rec.LAST_UPDATED_BY(i),
10449:
10450: EXCEPTION
10451: WHEN OTHERS THEN
10452: debugmsg('UPDATE_TERR_DEF: UPDATE_TERR_QUAL : Inside exception others');
10453: x_return_status := FND_API.G_RET_STS_ERROR;
10454: fnd_message.clear;
10455: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_UPDATE_QUAL_VAL');
10456: X_Msg_Data := fnd_message.get();
10457:
10504:
10505: if (l_Terr_Qual_Rec.TERR_QUAL_ID.count > 0) then
10506: BEGIN
10507: --dbms_output.put_line('U: get_d_terr_value_csr: delete TV, rowcount: ' || l_Terr_Qual_Rec.TERR_QUAL_ID.count);
10508: x_return_status := FND_API.G_RET_STS_SUCCESS;
10509: forall i in l_Terr_Qual_Rec.qual_value_id.first..l_Terr_Qual_Rec.qual_value_id.last
10510: DELETE FROM JTF_TERR_VALUES_ALL
10511: where TERR_VALUE_ID = l_Terr_Qual_Rec.qual_value_id(i);
10512: --dbms_output.put_line(' U: get_d_terr_value_csr:actual row processed: '||SQL%ROWCOUNT);
10558: and user_sequence = p_user_sequence;
10559:
10560: EXCEPTION
10561: WHEN OTHERS THEN
10562: x_return_status := FND_API.G_RET_STS_ERROR;
10563: fnd_message.clear;
10564: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_DELETE_QUAL_VAL');
10565: X_Msg_Data := fnd_message.get();
10566:
10983:
10984: BEGIN
10985:
10986: --l_action_flag := p_action_flag;
10987: x_return_status := FND_API.G_RET_STS_SUCCESS;
10988: l_action_flag := 'C';
10989:
10990: -- process resource records
10991: open get_rsc_csr(p_user_sequence, l_intf_type, l_header, l_action_flag);
11121: END LOOP;
11122: -- End of addition
11123:
11124: BEGIN
11125: x_return_status := FND_API.G_RET_STS_SUCCESS;
11126: open get_c_rsc_access_csr(P_USER_SEQUENCE, l_header, l_intf_type);
11127: fetch get_c_rsc_access_csr bulk collect into l_rsc_access_rec.TERR_RSC_ACCESS_ID,
11128: l_rsc_access_rec.LAST_UPDATE_DATE, l_rsc_access_rec.LAST_UPDATED_BY,
11129: l_rsc_access_rec.CREATION_DATE, l_rsc_access_rec.CREATED_BY,
11177: end if; -- create resource access type
11178:
11179: exception
11180: when others then
11181: x_return_status := FND_API.G_RET_STS_ERROR;
11182: fnd_message.clear;
11183: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_CREATE_ACCESS');
11184: X_Msg_Data := fnd_message.get();
11185:
11200: and jwot.status is null;
11201: END;
11202:
11203: BEGIN
11204: x_return_status := FND_API.G_RET_STS_SUCCESS;
11205: open get_u_rsc_access_csr(P_USER_SEQUENCE, l_header, l_intf_type);
11206: fetch get_u_rsc_access_csr bulk collect into l_rsc_access_rec.TERR_RSC_ACCESS_ID,
11207: l_rsc_access_rec.LAST_UPDATE_DATE, l_rsc_access_rec.LAST_UPDATED_BY,
11208: l_rsc_access_rec.CREATION_DATE, l_rsc_access_rec.CREATED_BY,
11242: end if;
11243:
11244: exception
11245: when others then
11246: x_return_status := FND_API.G_RET_STS_ERROR;
11247: fnd_message.clear;
11248: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_UPDATE_ACCESS');
11249: X_Msg_Data := fnd_message.get();
11250:
11266: END;
11267:
11268: exception
11269: when others then
11270: x_return_status := FND_API.G_RET_STS_ERROR;
11271: fnd_message.clear;
11272: fnd_message.set_name ('JTF', 'JTY_OTH_TERR_UPDATE_RSC');
11273: X_Msg_Data := fnd_message.get();
11274:
11316: l_lay_seq_num number_tbl_type;
11317:
11318: BEGIN
11319: debugmsg('UPDATE_TERR_DEF: P_USER_SEQUENCE : ' || P_USER_SEQUENCE);
11320: x_return_status := FND_API.G_RET_STS_SUCCESS;
11321: --l_action_flag := p_action_flag;
11322: --fnd_file.put_line( FND_FILE.OUTPUT, 'Program starting....., sequence: '||p_USER_SEQUENCE ||', user_id: '||p_user_id);
11323: --dbms_output.put_line('Program starting....., sequence: '||p_USER_SEQUENCE ||', user_id: '||p_user_id);
11324:
11350:
11351: --dbms_output.put_line('get hierarchy completed, status: ' ||x_return_status);
11352: debugmsg('UPDATE_TERR_DEF: get hierarchy completed, status: ' ||x_return_status );
11353:
11354: if x_return_status = FND_API.G_RET_STS_SUCCESS then
11355: --dbms_output.put_line('set create record ');
11356: -- set all create records id to null
11357: debugmsg('UPDATE_TERR_DEF: SET_CREATE_RECORDS : Before ' );
11358: SET_CREATE_RECORDS(
11376: X_MSG_DATA => x_msg_data);
11377: --dbms_output.put_line('validating record completed, status: '||x_return_status);
11378: debugmsg('UPDATE_TERR_DEF: validating record completed, status: '||x_return_status );
11379:
11380: if x_return_status = FND_API.G_RET_STS_SUCCESS then
11381: -- process qualifier records
11382: --dbms_output.put_line(' Call delete records procedure...');
11383: l_action_flag := 'D';
11384: delete_records(
11402: x_msg_data => x_msg_data);
11403:
11404: debugmsg('UPDATE_TERR_DEF: UPDATE_TERR (C) completed, status: '||x_return_status );
11405:
11406: if x_return_status = FND_API.G_RET_STS_SUCCESS then
11407: -- process terr update
11408: --dbms_output.put_line(' process terr update... ');
11409:
11410: /*bug-9244529
11419: x_msg_data => x_msg_data);
11420:
11421: debugmsg('UPDATE_TERR_DEF: UPDATE_TERR (U) completed, status: '||x_return_status );
11422:
11423: if x_return_status = FND_API.G_RET_STS_SUCCESS then
11424: -- process resources
11425: --dbms_output.put_line(' process resource records');
11426: debugmsg('UPDATE_TERR_DEF: UPDATE_TERR_RSC : Before' );
11427: UPDATE_TERR_RSC(P_USER_SEQUENCE => p_user_sequence,
11430: x_msg_data => x_msg_data);
11431:
11432: debugmsg('UPDATE_TERR_DEF: UPDATE_TERR_RSC completed, status: '||x_return_status );
11433:
11434: if x_return_status = FND_API.G_RET_STS_SUCCESS then
11435: -- process qualifier
11436: --dbms_output.put_line(' process qualifier records');
11437: debugmsg('UPDATE_TERR_DEF: UPDATE_TERR_QUAL : Before' );
11438: UPDATE_TERR_QUAL(P_USER_SEQUENCE => p_user_sequence,
11536: and interface_type = l_intf_type
11537: and user_sequence = p_user_sequence;
11538: end if; -- validation
11539: debugmsg('UPDATE_TERR_DEF: End : ' );
11540: x_errbuf := FND_API.G_RET_STS_SUCCESS;
11541: x_retcode := 0;
11542: --commit;
11543:
11544: exception