[Home] [Help]
PACKAGE BODY: APPS.OKE_HOLD_UTILS
Source
1 PACKAGE BODY OKE_HOLD_UTILS AS
2 /* $Header: OKEHLDUB.pls 120.2 2005/06/30 14:08:05 ausmani noship $ */
3 --
4 -- Name : Status_Change
5 -- Pre-reqs : None
6 -- Function : This procedure performs utility functions during
7 -- a change request status change.
8 --
9 --
10 -- Parameters :
11 -- IN : None
12 -- OUT : None
13 --
14 -- Returns : None
15 --
16
17 PROCEDURE Status_Change
18 ( P_Hold_ID IN NUMBER
19 , P_K_Header_ID IN NUMBER
20 , P_K_Line_ID IN NUMBER
21 , P_DTS_ID IN NUMBER
22 , P_Hold_Type_Code IN VARCHAR2
23 , P_Hold_Reason_Code IN VARCHAR2
24 , P_Remove_Reason_Code IN VARCHAR2
25 , P_Old_Status_Code IN VARCHAR2
26 , P_New_Status_Code IN VARCHAR2
27 , P_Updated_By IN NUMBER
28 , P_Update_Date IN DATE
29 , P_Login_ID IN NUMBER
30 ) IS
31
32 CURSOR sts IS
33 SELECT wf_item_type
34 , wf_process
35 , hold_status_name
36 FROM oke_hold_statuses_vl
37 WHERE hold_status_code = P_New_Status_Code;
38
39 CURSOR old_sts IS
40 SELECT hold_status_name
41 FROM oke_hold_statuses_vl
42 WHERE hold_status_code = P_Old_Status_Code;
43
44 CURSOR hold_reason IS
45 SELECT meaning
46 FROM fnd_lookup_values_vl
47 WHERE lookup_type = 'APPLY_HOLD_REASON'
48 AND lookup_code = P_Hold_reason_Code;
49
50 CURSOR remove_reason IS
51 SELECT meaning
52 FROM fnd_lookup_values_vl
53 WHERE lookup_type = 'REMOVE_HOLD_REASON'
54 AND lookup_code = P_Remove_reason_Code;
55
56 CURSOR hold_type IS
57 SELECT meaning
58 FROM fnd_lookup_values_vl
59 WHERE lookup_type = 'HOLD_TYPE'
60 AND lookup_code = P_Hold_type_Code;
61
62 CURSOR o IS
63 SELECT user_name
64 FROM fnd_user
65 WHERE user_id = P_Updated_By;
66
67 CURSOR kh IS
68 SELECT H.k_number_disp k_number
69 , T.k_type_name k_type
70 , hdr.org_id org_id
71 FROM oke_k_headers H
72 , oke_k_types_vl T
73 , okc_k_headers_all_B HDR
74 WHERE H.k_header_id = P_K_Header_ID
75 AND T.k_type_code = H.k_type_code
76 AND H.k_header_id = HDR.ID;
77
78 CURSOR kl IS
79 SELECT L.line_number
80 FROM okc_k_lines_b L
81 WHERE L.id = P_K_Line_ID;
82
83 CURSOR kd IS
84 SELECT D.deliverable_num
85 FROM oke_k_deliverables_b D
86 , okc_k_lines_b L
87 WHERE D.deliverable_id = P_dts_ID
88 AND L.id = D.K_Line_ID;
89
90 -- CURSOR kadmin IS
91 -- SELECT R.name
92 -- FROM oke_k_all_access_v A
93 -- , wf_roles R
94 -- WHERE A.k_header_id = P_K_Header_ID
95 -- AND A.role_id = 701 /* Contract Administrator */
96 -- AND sysdate BETWEEN A.START_DATE_ACTIVE AND NVL(A.END_DATE_ACTIVE , sysdate + 1)
97 -- AND R.ORIG_SYSTEM = 'PER'
98 -- AND R.ORIG_SYSTEM_ID = A.PERSON_ID
99 -- ORDER BY DECODE( assignment_level , 'SITE' , 0 , 'OKE_PROGRAMS' , 1 , 2 ) DESC;
100
101 l_wf_item_type VARCHAR2(8) := NULL;
102 l_wf_process VARCHAR2(30) := NULL;
103 l_wf_item_key VARCHAR2(240) := NULL;
104 l_wf_user_key VARCHAR2(240) := NULL;
105 l_wf_threshold NUMBER;
106 l_org_id NUMBER;
107 l_user_name VARCHAR2(30);
108 l_admin_name VARCHAR2(30);
109 l_contract_num VARCHAR2(150);
110 l_line_num VARCHAR2(150);
111 l_dts_num VARCHAR2(150);
112 l_k_type VARCHAR2(150);
113 l_new_status VARCHAR2(150);
114 l_old_status VARCHAR2(150);
115 l_hold_reason VARCHAR2(150);
116 l_remove_reason VARCHAR2(150);
117 l_hold_type VARCHAR2(150);
118
119 BEGIN
120
121 OPEN sts;
122 FETCH sts INTO l_wf_item_type , l_wf_process , l_new_status;
123 CLOSE sts;
124
125 IF ( l_wf_item_type IS NOT NULL and l_wf_process IS NOT NULL) THEN
126
127 OPEN old_sts;
128 FETCH old_sts INTO l_old_status;
129 CLOSE old_sts;
130
131 OPEN hold_reason;
132 FETCH hold_reason INTO l_hold_reason;
133 CLOSE hold_reason;
134
135 OPEN remove_reason;
136 FETCH remove_reason INTO l_remove_reason;
137 CLOSE remove_reason;
138
139 OPEN hold_type;
140 FETCH hold_type INTO l_hold_type;
141 CLOSE hold_type;
142
143 OPEN o;
144 FETCH o INTO l_user_name;
145 CLOSE o;
146
147 OPEN kh;
148 FETCH kh INTO l_contract_num , l_k_type,l_org_id;
149 CLOSE kh;
150
151 IF ( P_K_Line_ID IS NOT NULL ) THEN
152 OPEN kl;
153 FETCH kl INTO l_line_num;
154 CLOSE kl;
155 END IF;
156
157 IF ( P_DTS_ID IS NOT NULL ) THEN
158 OPEN kd;
159 FETCH kd INTO l_dts_num;
160 CLOSE kd;
161 END IF;
162
163 -- OPEN kadmin;
164 -- FETCH kadmin INTO l_admin_name;
165 -- CLOSE kadmin;
166 l_admin_name := OKE_UTILS.Retrieve_WF_Role_Name(P_K_Header_ID,701);
167
168 l_wf_item_key := P_Hold_ID || '-' ||
169 P_New_Status_Code || '-' ||
170 TO_CHAR(sysdate,'YYYYMMDDHH24MISS');
171
172 l_wf_user_key := l_Contract_Num || '-' ||
173 P_Hold_ID || '-' ||
174 P_New_Status_Code || '-' ||
175 TO_CHAR(sysdate,'YYYYMMDDHH24MISS');
176
177 WF_ENGINE.CreateProcess( itemtype => l_wf_item_type
178 , itemkey => l_wf_item_key
179 , process => l_wf_process );
180
181 WF_ENGINE.SetItemOwner ( itemtype => l_wf_item_type
182 , itemkey => l_wf_item_key
183 , owner => l_user_name );
184
185 WF_ENGINE.SetItemUserKey( itemtype => l_wf_item_type
186 , itemkey => l_wf_item_key
187 , userkey => l_wf_user_key );
188
189 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
190 , itemkey => l_wf_item_key
191 , aname => 'DOC_TYPE'
192 , avalue => l_k_type );
193
194 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
195 , itemkey => l_wf_item_key
196 , aname => 'DOC_NUMBER'
197 , avalue => l_Contract_Num );
198
199 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
200 , itemkey => l_wf_item_key
201 , aname => 'LINE_NUMBER'
202 , avalue => l_Line_Num );
203
204 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
205 , itemkey => l_wf_item_key
206 , aname => 'DTS_NUMBER'
207 , avalue => l_DTS_Num );
208
209 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
210 , itemkey => l_wf_item_key
211 , aname => 'HOLD_TYPE'
212 , avalue => l_Hold_Type );
213
214 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
215 , itemkey => l_wf_item_key
216 , aname => 'HOLD_REASON'
217 , avalue => l_Hold_Reason );
218
219 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
220 , itemkey => l_wf_item_key
221 , aname => 'REMOVE_REASON'
222 , avalue => l_Remove_Reason );
223
224 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
225 , itemkey => l_wf_item_key
226 , aname => 'OLD_STATUS'
227 , avalue => l_old_status );
228
229 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
230 , itemkey => l_wf_item_key
231 , aname => 'NEW_STATUS'
232 , avalue => l_new_status );
233
234 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
235 , itemkey => l_wf_item_key
236 , aname => 'NEW_STATUS_CODE'
237 , avalue => P_New_Status_Code );
238
239 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
240 , itemkey => l_wf_item_key
241 , aname => 'OLD_STATUS_CODE'
242 , avalue => P_Old_Status_Code );
243
244 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
245 , itemkey => l_wf_item_key
246 , aname => 'K_HEADER_ID'
247 , avalue => P_K_Header_ID );
248
249 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
250 , itemkey => l_wf_item_key
251 , aname => 'K_LINE_ID'
252 , avalue => P_K_Line_ID );
253
254 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
255 , itemkey => l_wf_item_key
256 , aname => 'DTS_ID'
257 , avalue => P_DTS_ID );
258
259 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
260 , itemkey => l_wf_item_key
261 , aname => 'HOLD_ID'
262 , avalue => P_Hold_ID );
263
264 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
265 , itemkey => l_wf_item_key
266 , aname => 'LAST_UPDATED_BY'
267 , avalue => P_Updated_by );
268
269 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
270 , itemkey => l_wf_item_key
271 , aname => 'REQUESTOR'
272 , avalue => l_user_name );
273
274 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
275 , itemkey => l_wf_item_key
276 , aname => 'ADMINISTRATOR'
277 , avalue => l_admin_name );
278
279 WF_ENGINE.SetItemAttrNumber( itemtype => l_wf_item_type
280 , itemkey => l_wf_item_key
281 , aname => 'ORG_ID'
282 , avalue => l_org_id );
283
284 WF_ENGINE.StartProcess( itemtype => l_wf_item_type
285 , itemkey => l_wf_item_key );
286
287 END IF;
288
289 END Status_Change;
290
291
292 END OKE_HOLD_UTILS;