[Home] [Help]
PACKAGE BODY: APPS.AMW_CM_EVENT_LISTNER_PKG
Source
1 package body amw_cm_event_listner_pkg as
2 /*$Header: amwcmlsb.pls 120.2 2006/08/25 10:58:43 yreddy noship $*/
3
4 FUNCTION listen_cm_approval
5 ( p_subscription_guid in raw,
6 p_event in out NOCOPY wf_event_t
7 ) return VARCHAR2
8 IS
9
10 l_key varchar2(240) := p_event.GetEventKey();
11 p_ChangeId NUMBER;
12 p_NewApprovalStatusCode NUMBER;
13 p_WorkflowRouteStatus varchar2(240);
14 ename varchar2(80);
15 pk1 varchar2(100);
16 pk2 varchar2(100);
17 pk3 varchar2(100);
18 process_id number;
19 organization_id number;
20 revnum number;
21
22 begin
23
24 p_ChangeId := p_event.GetValueForParameter('ChangeId');
25 p_NewApprovalStatusCode := p_event.GetValueForParameter('NewApprovalStatusCode');
26 p_WorkflowRouteStatus := p_event.GetValueForParameter('WorkflowRouteStatus');
27
28
29 select entity_name,pk1_value,pk2_value,pk3_value
30 into ename, pk1, pk2, pk3
31 from eng_change_subjects
32 where change_id = p_ChangeId
33 and change_line_id is null
34 and subject_level=1;
35
36 if ename = 'AMW_REVISION_ETTY' then
37 process_id := pk1;
38 revnum := pk2;
39
40 if p_WorkflowRouteStatus in ('TIME_OUT', 'ABORTED') then
41 AMW_PROC_APPROVAL_PKG.reject(process_id);
42 else
43 if p_NewApprovalStatusCode = 3 then
44 AMW_PROC_APPROVAL_PKG.sub_for_approval(p_process_id => process_id, p_webadi_call => null);
45 elsif p_NewApprovalStatusCode = 5 then
46 AMW_PROC_APPROVAL_PKG.approve(process_id);
47 elsif p_NewApprovalStatusCode in (4,7,8) then
48 AMW_PROC_APPROVAL_PKG.reject(process_id);
49 end if;
50 end if;
51
52
53 elsif ename = 'AMW_ORG_REV_ETTY' then
54 organization_id := pk1;
55 process_id := pk2;
56 revnum := pk3;
57
58 if p_WorkflowRouteStatus in ('TIME_OUT', 'ABORTED') then
59 AMW_PROC_ORG_APPROVAL_PKG.reject(process_id, organization_id);
60 else
61 if p_NewApprovalStatusCode = 3 then
62 AMW_PROC_ORG_APPROVAL_PKG.sub_for_approval(process_id, organization_id);
63 elsif p_NewApprovalStatusCode = 5 then
64 AMW_PROC_ORG_APPROVAL_PKG.approve(process_id, organization_id);
65 elsif p_NewApprovalStatusCode in (4,7,8) then
66 AMW_PROC_ORG_APPROVAL_PKG.reject(process_id, organization_id);
67 end if;
68 end if;
69
70 end if;
71
72 commit;
73
74 return 'SUCCESS';
75
76 EXCEPTION
77 WHEN OTHERS THEN
78 WF_CORE.CONTEXT('amw_cm_event_listner_pkg', 'listen_cm_approval', p_event.getEventName(), p_subscription_guid);
79 WF_EVENT.setErrorInfo(p_event, 'ERROR');
80 RETURN 'ERROR';
81
82 end listen_cm_approval;
83
84
85 ---10.11.2005 npanandi: changed the signature of
86 ---listen_cm_approval to the below procedure
87 ---bug 4473863 fix
88 procedure UPDATE_APPROVAL_STATUS(
89 p_change_id in number
90 ,p_base_change_mgmt_type_code in varchar2
91 ,p_new_approval_status_code in varchar2
92 ,p_workflow_status_code in varchar2
93 ,x_return_status out nocopy varchar2
94 ,x_msg_count out nocopy number
95 ,x_msg_data out nocopy varchar2
96 )
97 is
98 dummy1 varchar2(100);
99
100 ----l_key varchar2(240) := p_event.GetEventKey();
101 p_ChangeId NUMBER;
102 p_NewApprovalStatusCode NUMBER;
103 p_WorkflowRouteStatus varchar2(240);
104 l_ename varchar2(80);
105 l_pk1 varchar2(100);
106 l_pk2 varchar2(100);
107 l_pk3 varchar2(100);
108 l_process_id number;
109 l_organization_id number;
110 l_revnum number;
111 l_audit_project_id number;
112 l_sign_off_status varchar2(30);
113
114 begin
115 x_return_status := fnd_api.g_ret_sts_success;
116
117 select entity_name,pk1_value,pk2_value,pk3_value
118 into l_ename, l_pk1, l_pk2, l_pk3
119 from eng_change_subjects
120 where change_id = p_change_id
121 and change_line_id is null
122 and subject_level=1;
123
124 if l_ename = 'AMW_REVISION_ETTY' then
125 l_process_id := l_pk1;
126 l_revnum := l_pk2;
127
128 if p_workflow_status_code in ('TIME_OUT', 'ABORTED') then
129 AMW_PROC_APPROVAL_PKG.reject(l_process_id);
130 else
131 if p_new_approval_status_code = 3 then
132 AMW_PROC_APPROVAL_PKG.sub_for_approval(p_process_id => l_process_id, p_webadi_call => null);
133 elsif p_new_approval_status_code = 5 then
134 AMW_PROC_APPROVAL_PKG.approve(l_process_id);
135 elsif p_new_approval_status_code in (4,7,8) then
136 AMW_PROC_APPROVAL_PKG.reject(l_process_id);
137 end if;
138 end if;
139
140 elsif l_ename = 'AMW_ORG_REV_ETTY' then
141 l_organization_id := l_pk1;
142 l_process_id := l_pk2;
143 l_revnum := l_pk3;
144
145 if p_workflow_status_code in ('TIME_OUT', 'ABORTED') then
146 AMW_PROC_ORG_APPROVAL_PKG.reject(l_process_id, l_organization_id);
147 else
148 if p_new_approval_status_code = 3 then
149 AMW_PROC_ORG_APPROVAL_PKG.sub_for_approval(l_process_id, l_organization_id);
150 elsif p_new_approval_status_code = 5 then
151 AMW_PROC_ORG_APPROVAL_PKG.approve(l_process_id, l_organization_id);
152 elsif p_new_approval_status_code in (4,7,8) then
153 AMW_PROC_ORG_APPROVAL_PKG.reject(l_process_id, l_organization_id);
154 end if;
155 end if;
156
157 elsif l_ename = 'PROJECT' then
158 l_audit_project_id := l_pk1;
159
160 if p_workflow_status_code in ('TIME_OUT', 'ABORTED') then
161 l_sign_off_status := 'NOT_COMPLETED';
162 else
163 if p_new_approval_status_code = 3 then
164 l_sign_off_status := 'PENDING_APPROVAL';
165 elsif p_new_approval_status_code = 5 then
166 l_sign_off_status := 'APPROVED';
167 elsif p_new_approval_status_code in (4,7,8) then
168 l_sign_off_status := 'REJECTED';
169 end if;
170 end if;
171
172 /* update the Engagement status */
173 IF l_sign_off_status = 'APPROVED' THEN
174 UPDATE AMW_AUDIT_PROJECTS
175 SET audit_project_status = 'SIGN',
176 sign_off_status = 'APPROVED'
177 WHERE AUDIT_PROJECT_ID = l_audit_project_id
178 AND AUDIT_PROJECT_STATUS = 'ACTI';
179 ELSE /* Update the signOffStatus. */
180 UPDATE AMW_AUDIT_PROJECTS
181 SET sign_off_status = l_sign_off_status
182 WHERE AUDIT_PROJECT_ID = l_audit_project_id;
183 END IF;
184
185 end if;
186
187 commit;
188
189 EXCEPTION
190 WHEN OTHERS THEN
191 x_return_status := fnd_api.g_ret_sts_unexp_error;
192 -- Standard call to get message count and if count=1, get the message
193 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false,
194 p_count => x_msg_count,
195 p_data => x_msg_data);
196 end UPDATE_APPROVAL_STATUS;
197
198
199 end amw_cm_event_listner_pkg;