[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_UPDATE_LOCATION_PUB
Source
1 PACKAGE BODY jtf_rs_update_location_pub AS
2 /* $Header: jtfrsugb.pls 120.0 2005/05/11 08:22:45 appldev ship $ */
3 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_UPDATE_LOCATION_PUB';
4
5 PROCEDURE update_resource
6 (P_API_VERSION IN NUMBER,
7 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
8 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
9 P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
10 P_LOCATION IN MDSYS.SDO_GEOMETRY ,
11 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_RESOURCE_EXTNS.OBJECT_VERSION_NUMBER%TYPE,
12 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
13 X_MSG_COUNT OUT NOCOPY NUMBER,
14 X_MSG_DATA OUT NOCOPY VARCHAR2
15 )
16 IS
17 l_api_version CONSTANT NUMBER := 1.0;
18 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE';
19 l_date Date;
20 l_user_id Number;
21 l_login_id Number;
22
23 cursor res_cur
24 is
25 select resource_id,
26 object_version_number
27 from jtf_rs_resource_extns
28 where resource_id = p_resource_id;
29
30 res_rec res_cur%rowtype;
31
32 BEGIN
33 SAVEPOINT update_sp;
34 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
35 THEN
36 RAISE FND_API.G_EXC_ERROR;
37 END IF;
38
39 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
40 IF FND_API.To_boolean(P_INIT_MSG_LIST)
41 THEN
42 FND_MSG_PUB.Initialize;
43 END IF;
44
45 l_date := sysdate;
46 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
47 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
48
49
50 if(p_resource_id is null)
51 then
52 fnd_message.set_name('JTF', 'JTF_RS_RESOURCE_NULL');
53 fnd_msg_pub.add;
54 x_return_status := fnd_api.g_ret_sts_error;
55 RAISE fnd_api.g_exc_error;
56
57 end if;
58
59 open res_cur;
60 fetch res_cur into res_rec;
61 if(res_cur%notfound)
62 then
63 fnd_message.set_name('JTF', 'JTF_RS_RESOURCE_NULL');
64 fnd_msg_pub.add;
65 x_return_status := fnd_api.g_ret_sts_error;
66 close res_cur;
67 RAISE fnd_api.g_exc_error;
68 else
69
70 BEGIN
71 jtf_rs_resource_extns_pkg.lock_row(
72 x_resource_id => p_resource_id,
73 x_object_version_number => p_object_version_num
74 );
75
76 EXCEPTION
77 WHEN OTHERS THEN
78 x_return_status := fnd_api.g_ret_sts_unexp_error;
79 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
80 fnd_msg_pub.add;
81 RAISE fnd_api.g_exc_unexpected_error;
82 END;
83 update jtf_rs_resource_extns
84 set location = p_location,
85 object_version_number = object_version_number + 1
86 where resource_id = p_resource_id;
87
88 end if; -- end of res_cur check
89 close res_cur;
90 p_object_version_num := p_object_version_num + 1;
91
92 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
93
94 EXCEPTION
95 WHEN fnd_api.g_exc_unexpected_error
96 THEN
97 ROLLBACK TO update_sp;
98 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
99 WHEN fnd_api.g_exc_error
100 THEN
101 ROLLBACK TO update_sp;
102 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
103
104 WHEN OTHERS
105 THEN
106 ROLLBACK TO update_sp;
107 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
108 fnd_message.set_token('P_SQLCODE',SQLCODE);
109 fnd_message.set_token('P_SQLERRM',SQLERRM);
110 fnd_message.set_token('P_API_NAME',l_api_name);
111 FND_MSG_PUB.add;
112 x_return_status := fnd_api.g_ret_sts_unexp_error;
113 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
114 END;
115
116
117 END jtf_rs_update_location_pub;