[Home] [Help]
PACKAGE BODY: APPS.CSF_WF_EVENT_SUBSCR_PKG
Source
1 PACKAGE BODY CSF_WF_EVENT_SUBSCR_PKG as
2 /* $Header: csfwfevb.pls 120.2 2005/08/30 14:06:58 rhungund noship $ */
3 -- Start of Comments
4 -- Package name : CSF_WF_EVENT_SUBSCR_PKG
5 -- Purpose : Preventive Maintenace WF Business Event Subscription API.
6 -- History : Initial version for release 11.5.9
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSF_WF_EVENT_SUBSCR_PKG';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csfwfevb.pls';
12 g_retcode number := 0;
13
14
15 FUNCTION CSF_VERIFY_PM_SR(p_subscription_guid in raw,
16 p_event in out nocopy WF_EVENT_T) RETURN varchar2 is
17
18 -- Generic Event Parameters and Cursors
19 l_event_name VARCHAR2(240) := p_event.getEventName( );
20
21 l_resp_appl_id NUMBER := NULL;
22 l_login_id NUMBER := NULL;
23 l_user_id NUMBER := NULL;
24
25 l_return_status VARCHAR2(1);
26 l_msg_count NUMBER;
27 l_msg_data VARCHAR2(2000);
28
29 l_request_number VARCHAR2(64);
30
31
32 BEGIN
33
34 l_return_status := FND_API.G_RET_STS_SUCCESS;
35
36 IF (l_event_name = 'oracle.apps.cs.sr.ServiceRequest.statuschanged') THEN
37
38 /* The event payload only passes REQUEST_NUMBER. Hence getting that parameter ... */
39 l_request_number := p_event.getvalueforparameter('REQUEST_NUMBER');
40
41 IF (VALIDATE_SR_FOR_DEBRIEF(l_request_number) <> 'S') THEN
42 raise FND_API.G_EXC_ERROR;
43 END IF;
44
45 END IF;
46
47 return 'SUCCESS';
48
49 EXCEPTION
50 WHEN FND_API.G_EXC_ERROR THEN
51 FND_MESSAGE.SET_NAME('CSF','CSF_PRIOR_PM_SRS_EXIST');
52 APP_EXCEPTION.RAISE_EXCEPTION;
53 return 'ERROR';
54
55 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
56 FND_MESSAGE.SET_NAME('CSF','CSF_PRIOR_PM_SRS_EXIST');
57 APP_EXCEPTION.RAISE_EXCEPTION;
58 return 'ERROR';
59
60 WHEN OTHERS THEN
61 FND_MESSAGE.SET_NAME('CSF','CSF_PRIOR_PM_SRS_EXIST');
62 APP_EXCEPTION.RAISE_EXCEPTION;
63 return 'ERROR';
64
65 END CSF_VERIFY_PM_SR;
66
67
68
69 FUNCTION VALIDATE_SR_FOR_DEBRIEF(
70 p_service_request_number IN VARCHAR2
71 ) RETURN VARCHAR2 IS
72
73 -- UMP and SR Event Parameters and Cursors
74 l_forecast_sequence NUMBER;
75 l_mr_header_id NUMBER;
76
77 l_customer_product_id NUMBER;
78 l_request_number VARCHAR2(64);
79 l_request_id NUMBER;
80
81
82 CURSOR c_get_ue_details_csr IS
83 SELECT csi.incident_id, csi.customer_product_id,
84 aueb.forecast_sequence, aueb.mr_header_id
85 FROM
86 cs_incidents_all_b csi,
87 ahl_unit_effectivities_vl aueb,
88 cs_incident_links cil
89 WHERE
90 cil.object_id = aueb.unit_effectivity_id
91 and cil.object_type = 'AHL_UMP_EFF'
92 and cil.link_type_id = 6
93 and csi.incident_id = cil.subject_id
94 and csi.incident_number = l_request_number;
95
96
97 CURSOR c_check_for_pm_sr_csr IS
98 SELECT csi.incident_id,aueb.unit_effectivity_id from
99 cs_incidents_all_b csi,
100 ahl_unit_effectivities_vl aueb,
101 cs_incident_links cil
102 WHERE
103 (aueb.status_code is NULL
104 or aueb.status_code = 'INIT-DUE')
105 and cil.object_id = aueb.unit_effectivity_id
106 and cil.object_type = 'AHL_UMP_EFF'
107 and cil.link_type_id = 6
108 and csi.incident_id = cil.subject_id
109 and nvl(csi.status_flag, 'O') <> 'C' and
110 nvl(aueb.forecast_sequence, -1) < l_forecast_sequence and
111 aueb.csi_item_instance_id = l_customer_product_id and
112 nvl(aueb.mr_header_id, 0) = l_mr_header_id and
113 csi.incident_id <> l_request_id;
114
115 c_check_for_pm_sr_rec c_check_for_pm_sr_csr%ROWTYPE;
116
117
118 BEGIN
119
120 l_request_number := p_service_request_number;
121 OPEN c_get_ue_details_csr;
122 FETCH c_get_ue_details_csr INTO l_request_id, l_customer_product_id, l_forecast_sequence, l_mr_header_id;
123
124
125 /* For PM Srs the following 2 fields will not be null. Just to be on the safer side ... */
126 IF (l_forecast_sequence is null) THEN
127 l_forecast_sequence := 0;
128 END IF;
129
130 IF(l_mr_header_id is null) THEN
131 l_mr_header_id := 0;
132 END IF;
133
134
135 IF(c_get_ue_details_csr%FOUND)THEN
136 OPEN c_check_for_pm_sr_csr;
137 FETCH c_check_for_pm_sr_csr INTO c_check_for_pm_sr_rec;
138
139 IF (c_check_for_pm_sr_csr%FOUND) THEN
140 return 'U';
141 END IF;
142
143 CLOSE c_check_for_pm_sr_csr;
144 END IF;
145
146 CLOSE c_get_ue_details_csr;
147 return 'S';
148
149 END VALIDATE_SR_FOR_DEBRIEF;
150
151
152 -- Enter further code below as specified in the Package spec.
153 END CSF_WF_EVENT_SUBSCR_PKG;
154