[Home] [Help]
PACKAGE BODY: APPS.JTF_REQUEST_HISTORY_PVT
Source
1 PACKAGE BODY JTF_Request_History_PVT as
2 /* $Header: jtfgrqhb.pls 115.4 2003/09/05 19:44:10 sxkrishn ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- JTF_Request_History_PVT
7 -- Purpose
8 --
9 -- History Created by SXKRISHN AND ABUDDHAV
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'JTF_Request_History_PVT';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'jtfgrqhb.pls';
19
20 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
21 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
22
23 -- Hint: Primary key needs to be returned.
24 PROCEDURE Create_Request_History(
25 p_api_version_number IN NUMBER,
26 p_init_msg_list IN VARCHAR2,
27 p_commit IN VARCHAR2,
28 p_validation_level IN NUMBER,
29
30 x_return_status OUT NOCOPY VARCHAR2,
31 x_msg_count OUT NOCOPY NUMBER,
32 x_msg_data OUT NOCOPY VARCHAR2,
33
34 p_request_history_rec IN request_history_rec_type,
35 x_request_history_id OUT NOCOPY NUMBER
36 )
37
38 IS
39
40 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Request_History';
41 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
42 l_return_status_full VARCHAR2(1);
43 l_object_version_number NUMBER := 1;
44 l_org_id NUMBER ;
45 l_REQUEST_HISTORY_ID NUMBER;
46 l_dummy NUMBER;
47
48 CURSOR c_id IS
49 SELECT JTF_FM_REQUESTHISTID_S.NEXTVAL
50 FROM dual;
51
52 CURSOR c_id_exists (l_id IN NUMBER) IS
53 SELECT 1
54 FROM JTF_FM_REQUEST_HISTORY
55 WHERE HIST_REQ_ID = l_id;
56
57 BEGIN
58 -- Standard Start of API savepoint
59 SAVEPOINT CREATE_Request_History_PVT;
60
61 -- Standard call to check for call compatibility.
62 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
63 p_api_version_number,
64 l_api_name,
65 G_PKG_NAME)
66 THEN
67 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
68 END IF;
69
70 -- Initialize message list if p_init_msg_list is set to TRUE.
71 IF FND_API.to_Boolean( p_init_msg_list )
72 THEN
73 FND_MSG_PUB.initialize;
74 END IF;
75
76 -- Debug Message
77 --JTF_FM_REQUEST_GRP.PRINT_MESSAGE('Private API: ' || l_api_name || 'start');
78
79
80 -- Initialize API return status to SUCCESS
81 x_return_status := FND_API.G_RET_STS_SUCCESS;
82
83 -- Local variable initialization
84
85 IF p_request_history_rec.HIST_REQ_ID IS NULL OR p_request_history_rec.HIST_REQ_ID = FND_API.g_miss_num THEN
86 LOOP
87 l_dummy := NULL;
88 OPEN c_id;
89 FETCH c_id INTO l_REQUEST_HISTORY_ID;
90 CLOSE c_id;
91
92 OPEN c_id_exists(l_REQUEST_HISTORY_ID);
93 FETCH c_id_exists INTO l_dummy;
94 CLOSE c_id_exists;
95 EXIT WHEN l_dummy IS NULL;
96
97 END LOOP;
98
99
100 END IF;
101
102 -- =========================================================================
103 -- Validate Environment
104 -- =========================================================================
105
106 IF FND_GLOBAL.User_Id IS NULL
107 THEN
108 --AMF_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
109 RAISE FND_API.G_EXC_ERROR;
110 END IF;
111
112
113
114 -- Debug Message
115 --JTF_FM_REQUEST_GRP.PRINT_MESSAGE( 'Private API: Calling create table handler');
116
117 -- Invoke table handler(AMF_REQUEST_HISTORY_PKG.Insert_Row)
118
119 --DBMS_OUTPUT.PUT_LINE('*** svatsa *** :Before AMF_REQUEST_HISTORY_PKG.Insert_Row');
120 --DBMS_OUTPUT.PUT_LINE('*** svatsa *** :Before px_request_history_id = '||TO_CHAR(l_request_history_id));
121
122 INSERT INTO JTF_FM_REQUEST_HISTORY_ALL(
123 OUTCOME_CODE,
124 SOURCE_CODE_ID,
125 SOURCE_CODE,
126 OBJECT_TYPE,
127 OBJECT_ID,
128 order_id,
129 resubmit_count,
130 outcome_desc ,
131 request,
132 submit_dt_tm,
133 server_id,
134 template_id,
135 app_info,
136 group_id,
137 hist_req_id,
138 user_id,
139 priority,
140 processed_dt_tm,
141 message_id,
142 last_update_date,
143 last_updated_by,
144 creation_date,
145 created_by,
146 last_update_login,
147 org_id,
148 f_deletedflag,
149 object_version_number,
150 security_group_id)
151 VALUES(
152 p_request_history_rec.outcome_code,
153 p_request_history_rec.source_code_id,
154 p_request_history_rec.source_code,
155 p_request_history_rec.object_type,
156 p_request_history_rec.object_id,
157 p_request_history_rec.order_id,
158 p_request_history_rec.resubmit_count,
159 p_request_history_rec.outcome_desc,
160 p_request_history_rec.request,
161 p_request_history_rec.submit_dt_tm,
162 p_request_history_rec.server_id,
163 p_request_history_rec.template_id,
164 p_request_history_rec.app_info,
165 p_request_history_rec.group_id,
166 l_request_history_id,
167 p_request_history_rec.user_id,
168 p_request_history_rec.priority,
169 p_request_history_rec.processed_dt_tm,
170 p_request_history_rec.message_id,
171 SYSDATE,
172 G_USER_ID,
173 SYSDATE,
174 G_USER_ID,
175 G_LOGIN_ID,
176 p_request_history_rec.org_id,
177 null,
178 l_object_version_number,
179 p_request_history_rec.security_group_id
180 );
181
182 x_request_history_id := l_request_history_id ;
183
184 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
185 RAISE FND_API.G_EXC_ERROR;
186 END IF;
187 --
188 -- End of API body
189 --
190
191 -- Standard check for p_commit
192 IF FND_API.to_Boolean( p_commit )
193 THEN
194 COMMIT WORK;
195 END IF;
196
197
198 -- Debug Message
199 --JTF_FM_REQUEST_GRP.PRINT_MESSAGE('Private API: ' || l_api_name || 'end');
200
201 -- Standard call to get message count and if count is 1, get message info.
202 FND_MSG_PUB.Count_And_Get
203 (p_count => x_msg_count,
204 p_data => x_msg_data
205 );
206 EXCEPTION
207
208
209 WHEN FND_API.G_EXC_ERROR THEN
210 ROLLBACK TO CREATE_Request_History_PVT;
211 x_return_status := FND_API.G_RET_STS_ERROR;
212 -- Standard call to get message count and if count=1, get the message
213 FND_MSG_PUB.Count_And_Get (
214 p_encoded => FND_API.G_FALSE,
215 p_count => x_msg_count,
216 p_data => x_msg_data
217 );
218
219 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
220 ROLLBACK TO CREATE_Request_History_PVT;
221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
222 -- Standard call to get message count and if count=1, get the message
223 FND_MSG_PUB.Count_And_Get (
224 p_encoded => FND_API.G_FALSE,
225 p_count => x_msg_count,
226 p_data => x_msg_data
227 );
228
229 WHEN OTHERS THEN
230 ROLLBACK TO CREATE_Request_History_PVT;
231 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
232 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
233 THEN
234 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
235 END IF;
236 -- Standard call to get message count and if count=1, get the message
237 FND_MSG_PUB.Count_And_Get (
238 p_encoded => FND_API.G_FALSE,
239 p_count => x_msg_count,
240 p_data => x_msg_data
241 );
242 End Create_Request_History;
243 END JTF_Request_History_PVT;