[Home] [Help]
PACKAGE BODY: APPS.CUG_PREVENT_SR_UPDATE_PKG
Source
1 Package Body CUG_PREVENT_SR_UPDATE_PKG AS
2 /* $Header: CUGPREUB.pls 115.3.1159.2 2003/06/30 06:47:54 vmuruges ship $ */
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- dejoseph 12-05-02 Replaced reference to install_site_use_id with
13 -- install_site_id. ER# 2695480.
14 --
15 -- vmuruges 30-06-03 Bug# 2802879 Ignored the condition when no workflow
16 -- is associated with the Service Request.
17
18 -- Enter procedure, function bodies as shown below
19
20 PROCEDURE Update_ServiceRequest_Prevent
21 ( p_api_version IN NUMBER,
22 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
23 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
24 p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2,
28 p_request_id IN NUMBER,
29 p_object_version_number IN NUMBER,
30 p_resp_appl_id IN NUMBER DEFAULT NULL,
31 p_resp_id IN NUMBER DEFAULT NULL,
32 p_last_updated_by IN NUMBER,
33 p_last_update_login IN NUMBER DEFAULT NULL,
34 p_last_update_date IN DATE,
35 p_service_request_rec IN CS_ServiceRequest_PVT.service_request_rec_type,
36 p_update_desc_flex IN VARCHAR2 DEFAULT fnd_api.g_false,
37 p_notes IN CS_ServiceRequest_PVT.notes_table,
38 p_contacts IN CS_ServiceRequest_PVT.contacts_table,
39 p_audit_comments IN VARCHAR2 DEFAULT NULL,
40 p_called_by_workflow IN VARCHAR2 DEFAULT fnd_api.g_false,
41 p_workflow_process_id IN NUMBER DEFAULT NULL,
42 x_workflow_process_id OUT NOCOPY NUMBER,
43 x_interaction_id OUT NOCOPY NUMBER
44 )
45 IS
46 l_return_status VARCHAR2(1);
47
48 l_request_id NUMBER;
49 l_type_id NUMBER;
50 l_status_id NUMBER;
51 l_severity_id NUMBER;
52 l_urgency_id NUMBER;
53 l_owner_id NUMBER;
54 l_owner_group_id NUMBER;
55 l_install_site_id NUMBER;
56
57 -- Begin of changes by ANEEMUCH date 20-May-2002
58 l_incident_location_id NUMBER;
59 l_incident_address VARCHAR2(960);
60 l_incident_city VARCHAR2(60);
61 l_incident_state VARCHAR2(60);
62 l_incident_county VARCHAR2(60);
63 l_incident_province VARCHAR2(60);
64 l_incident_postal_code VARCHAR2(60);
65 l_incident_country VARCHAR2(60);
66 -- End of changes by ANEEMUCH date 20-May-2002
67
68 l_request_number VARCHAR2(64);
69 l_workflow_process_id NUMBER;
70 l_request_number_key VARCHAR2(100);
71 l_end_date DATE;
72
73 l_api_name_full CONSTANT VARCHAR2(61) := 'CS_SERVICEREQUEST_VUHK'||'.'||'Update_ServiceRequest_Pre';
74
75 CURSOR l_CurrentServiceRequest_csr IS
76 SELECT * FROM CS_INCIDENTS_ALL_B WHERE
77 incident_id = l_request_id;
78 l_CurrentServiceRequest_rec l_CurrentServiceRequest_csr%ROWTYPE;
79
80
81 CURSOR l_IncidentDetails_csr IS
82 SELECT * FROM CUG_INCIDNT_ATTR_VALS_VL WHERE incident_id = l_request_id;
83 l_IncidentDetails_rec l_IncidentDetails_csr%ROWTYPE;
84
85
86 CURSOR l_WFDetails_csr IS
87 SELECT end_date from WF_ITEMS where
88 item_type = 'SERVEREQ' AND
89 root_activity = 'CUG_GENERIC_WORKFLOW' AND
90 item_key = l_request_number_key;
91 l_WFDetails_rec l_WFDetails_csr%ROWTYPE;
92
93 CURSOR l_WFActivityDetails_csr IS
94 SELECT activity_end_date from
95 wf_item_activity_statuses_v where
96 item_key = l_request_number_key AND
97 activity_name = 'CLOSE_REQUEST';
98 l_WFActivityDetails_rec l_WFActivityDetails_csr%ROWTYPE;
99
100 BEGIN
101
102
103 l_request_id := p_request_id;
104 l_type_id := p_service_request_rec.type_id;
105 l_status_id := p_service_request_rec.status_id;
106 l_severity_id := p_service_request_rec.severity_id;
107 l_urgency_id := p_service_request_rec.urgency_id;
108 l_owner_id := p_service_request_rec.owner_id;
109 l_owner_group_id := p_service_request_rec.owner_group_id;
110 l_install_site_id := p_service_request_rec.install_site_id;
111
112 -- Begin changes by ANEEMUCH date 20-May-2002
113 -- added columns to check chgs to incident address
114 l_incident_location_id := p_service_request_rec.incident_location_id;
115 l_incident_address := p_service_request_rec.incident_address;
116 l_incident_city := p_service_request_rec.incident_city;
117 l_incident_state := p_service_request_rec.incident_state;
118 l_incident_county := p_service_request_rec.incident_county;
119 l_incident_province := p_service_request_rec.incident_province;
120 l_incident_postal_code := p_service_request_rec.incident_postal_code;
121 l_incident_country := p_service_request_rec.incident_country;
122 -- End of changes by ANEEMUCH date 20-May-2002
123
124
125 OPEN l_CurrentServiceRequest_csr;
126 FETCH l_CurrentServiceRequest_csr INTO l_CurrentServiceRequest_rec;
127
128
129 IF (l_CurrentServiceRequest_csr%NOTFOUND) THEN
130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
131 END IF;
132 CLOSE l_CurrentServiceRequest_csr;
133
134 l_request_number := l_CurrentServiceRequest_rec.incident_number;
135 l_workflow_process_id := l_CurrentServiceRequest_rec.workflow_process_id;
136 l_request_number_key := l_request_number||'-'||l_workflow_process_id;
137
138 OPEN l_WFDetails_csr;
139 FETCH l_WFDetails_csr into l_WFDetails_rec;
140 IF (l_WFDetails_csr%NOTFOUND) THEN
141 -- start for bug 2802879 ignoring the situation when no workflow is associated
142 x_return_status := FND_API.G_RET_STS_SUCCESS;
143 return;
144 --end for bug 2802879
145 ELSE
146 l_end_date := l_WFDetails_rec.end_date;
147 IF(l_end_date is null and l_status_id = 2) THEN
148 OPEN l_WFActivityDetails_csr;
149 FETCH l_WFActivityDetails_csr into l_WFActivityDetails_rec;
150 IF (l_WFActivityDetails_csr%NOTFOUND) THEN
151 null;
152 ELSE
153 IF( l_WFActivityDetails_rec.activity_end_date is null) THEN
154 x_return_status := FND_API.G_RET_STS_SUCCESS;
155 return;
156 END IF;
157 END IF;
158 END IF;
159 END IF;
160 CLOSE l_WFDetails_csr;
161
162 l_end_date := l_WFDetails_rec.end_date;
163
164 IF (l_type_id <> l_CurrentServiceRequest_rec.incident_type_id OR
165 l_status_id <> l_CurrentServiceRequest_rec.incident_status_id OR
166 l_severity_id <> l_CurrentServiceRequest_rec.incident_severity_id OR
167 l_urgency_id <> l_CurrentServiceRequest_rec.incident_urgency_id OR
168 l_owner_id <> l_CurrentServiceRequest_rec.incident_owner_id
169 )
170 THEN
171 FND_MESSAGE.Set_Name('CUG', 'CUG_SR_UPDATE_NOT_ALLOWED');
172 -- FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
173 FND_MSG_PUB.Add;
174 RAISE FND_API.G_EXC_ERROR;
175
176 -- Begin of changes by ANEEMUCH date 20-May-2002
177 -- removed check from install_site_use_id as incident address is part of service request
178 /*
179 ELSIF (l_install_site_use_id <> l_CurrentServiceRequest_rec.install_site_use_id) THEN
180 OPEN l_IncidentDetails_csr;
181 FETCH l_IncidentDetails_csr INTO l_IncidentDetails_rec;
182
183 IF (l_IncidentDetails_csr%NOTFOUND) THEN
184 x_return_status := FND_API.G_RET_STS_SUCCESS;
185 ELSE
186 x_return_status := FND_API.G_RET_STS_ERROR;
187 FND_MESSAGE.Set_Name('CUG', 'CUG_SR_UPDATE_NOT_ALLOWED');
188 -- FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
189 FND_MSG_PUB.Add;
190 RAISE FND_API.G_EXC_ERROR;
191 END IF;
192 CLOSE l_IncidentDetails_csr;
193 */
194
195 ELSIF ( l_incident_location_id <> l_CurrentServiceRequest_rec.incident_location_id or
196 l_incident_address <> l_CurrentServiceRequest_rec.incident_address or
197 l_incident_city <> l_CurrentServiceRequest_rec.incident_city or
198 l_incident_state <> l_CurrentServiceRequest_rec.incident_state or
199 l_incident_county <> l_CurrentServiceRequest_rec.incident_county or
200 l_incident_province <> l_CurrentServiceRequest_rec.incident_province or
201 l_incident_postal_code <> l_CurrentServiceRequest_rec.incident_postal_code or
202 l_incident_country <> l_CurrentServiceRequest_rec.incident_country) THEN
203
204 x_return_status := FND_API.G_RET_STS_ERROR;
205 FND_MESSAGE.Set_Name('CUG', 'CUG_INC_ADDR_UPD_NOT_ALLOWED');
206 FND_MSG_PUB.Add;
207 RAISE FND_API.G_EXC_ERROR;
208
209 -- End of changes by ANEEMUCH date 20-May-2002
210
211 END IF;
212
213 EXCEPTION
214 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
215 x_return_status := FND_API.G_RET_STS_ERROR;
216 FND_MSG_PUB.Count_And_Get
217 ( p_count => x_msg_count,
218 p_data => x_msg_data
219 );
220 WHEN FND_API.G_EXC_ERROR THEN
221 x_return_status := FND_API.G_RET_STS_ERROR;
222 FND_MSG_PUB.Count_And_Get
223 ( p_count => x_msg_count,
224 p_data => x_msg_data
225 );
226
227 END Update_ServiceRequest_Prevent;
228
229
230 -- Enter further code below as specified in the Package spec.
231 END; -- Package Body CUG_PREVENT_SR_UPDATE_PKG