[Home] [Help]
PACKAGE BODY: APPS.PO_CHORD_WF0
Source
1 PACKAGE BODY PO_CHORD_WF0 AS
2 /* $Header: POXWCOXB.pls 120.0 2005/06/01 19:27:09 appldev noship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6
7 PROCEDURE chord_setup(itemtype IN VARCHAR2,
8 itemkey IN VARCHAR2,
9 actid IN NUMBER,
10 funcmode IN VARCHAR2,
11 result OUT NOCOPY VARCHAR2)
12 IS
13 x_po_header_id NUMBER:=NULL;
14 x_po_release_id NUMBER:=NULL;
15 x_auth_status VARCHAR2(25):=NULL;
16 x_doc_type VARCHAR2(25):=NULL;
17 x_doc_subtype VARCHAR2(25):=NULL;
18 e_invalid_doc_type EXCEPTION;
19 BEGIN
20 IF (g_po_wf_debug = 'Y') THEN
21 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, '*** In procedure chord_setup ***');
22 END IF;
23
24 x_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
25 itemkey => itemkey,
26 aname => 'DOCUMENT_TYPE');
27
28 x_doc_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
29 itemkey => itemkey,
30 aname => 'DOCUMENT_SUBTYPE');
31
32 IF (g_po_wf_debug = 'Y') THEN
33 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, 'Document Type '|| x_doc_type);
34 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, 'Document Subtype '|| x_doc_subtype);
35 END IF;
36
37 IF x_doc_type = 'REQUISITION' THEN
38 --Change order does not apply to Req
39 raise e_invalid_doc_type;
40
41 ELSIF x_doc_type IN ('PO', 'PA') THEN
42
43 x_po_header_id :=
44 wf_engine.GetItemAttrNumber(itemtype,
45 itemkey,
46 'DOCUMENT_ID');
47 BEGIN
48
49 select authorization_status
50 into x_auth_status
51 from po_headers_all
52 where po_header_id=x_po_header_id;
53
54 END;
55
56 ELSIF x_doc_type = 'RELEASE' THEN
57
58 x_po_release_id :=
59 wf_engine.GetItemAttrNumber(itemtype,
60 itemkey,
61 'DOCUMENT_ID');
62
63 BEGIN
64
65 select authorization_status
66 into x_auth_status
67 from po_releases_all
68 where po_release_id=x_po_release_id;
69
70 END;
71
72
73 ELSE
74 raise e_invalid_doc_type;
75
76 END IF;
77
78 wf_engine.SetItemAttrText ( itemtype => itemtype,
79 itemkey => itemkey,
80 aname => 'AUTHORIZATION_STATUS',
81 avalue => x_auth_status );
82
83 IF (g_po_wf_debug = 'Y') THEN
84 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, '*** FINISH: chord_setup ***');
85 END IF;
86
87 result := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
88 return;
89
90
91 EXCEPTION
92 WHEN e_invalid_doc_type THEN
93 IF (g_po_wf_debug = 'Y') THEN
94 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, '*** exception chord_doc_type ***');
95 END IF;
96 wf_core.context('POAPPRV', 'chord_doc_type', 'e_invalid_doc_type');
97 RAISE;
98
99 WHEN OTHERS THEN
100 wf_core.context('POAPPRV', 'chord_doc_type', 'others');
101 RAISE;
102
103 END;
104
105 PROCEDURE chord_doc_type(itemtype IN VARCHAR2,
106 itemkey IN VARCHAR2,
107 actid IN NUMBER,
108 funcmode IN VARCHAR2,
109 result OUT NOCOPY VARCHAR2)
110 IS
111 x_doc_type VARCHAR2(25);
112 x_doc_subtype VARCHAR2(25);
113 e_invalid_doc_type EXCEPTION;
114 BEGIN
115 IF (g_po_wf_debug = 'Y') THEN
116 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, '*** In procedure chord_doc_type ***');
117 END IF;
118
119
120 x_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
121 itemkey => itemkey,
122 aname => 'DOCUMENT_TYPE');
123
124 x_doc_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
125 itemkey => itemkey,
126 aname => 'DOCUMENT_SUBTYPE');
127
128 IF (g_po_wf_debug = 'Y') THEN
129 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, 'doc_type '|| x_doc_type);
130 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, 'doc_subtype '|| x_doc_subtype);
131 END IF;
132
133
134 IF x_doc_type = 'REQUISITION' THEN
135 --Change order does not apply to Req
136 raise e_invalid_doc_type;
137
138 ELSIF x_doc_type IN ('PO', 'PA') THEN
139
140 IF x_doc_subtype = 'STANDARD' THEN
141 RESULT:='PO_STANDARD';
142 IF (g_po_wf_debug = 'Y') THEN
143 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, 'CO TYPE: po_standard');
144 END IF;
145
146 ELSIF x_doc_subtype = 'CONTRACT' THEN
147 RESULT:='PO_CONTRACT';
148 IF (g_po_wf_debug = 'Y') THEN
149 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, 'CO TYPE: po_contract');
150 END IF;
151
152 ELSIF x_doc_subtype = 'BLANKET' THEN
153 RESULT:='PO_BLANKET';
154 IF (g_po_wf_debug = 'Y') THEN
155 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, 'CO TYPE: po_blanket');
156 END IF;
157
158 ELSIF x_doc_subtype = 'PLANNED' THEN
159 RESULT:='PO_PLANNED';
160 IF (g_po_wf_debug = 'Y') THEN
161 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, 'CO TYPE: po_planned');
162 END IF;
163
164 END IF;
165
166 ELSIF x_doc_type = 'RELEASE' THEN
167 IF x_doc_subtype = 'BLANKET' THEN
168 RESULT:='BLANKET_RELEASE';
169 IF (g_po_wf_debug = 'Y') THEN
170 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, 'CO TYPE: blanket_release');
171 END IF;
172 ELSE
173 RESULT:='SCHEDULED_RELEASE';
174 IF (g_po_wf_debug = 'Y') THEN
175 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, 'CO TYPE: scheduled_release');
176 END IF;
177 END IF;
178 ELSE
179 raise e_invalid_doc_type;
180
181 END IF;
182
183 return;
184
185 IF (g_po_wf_debug = 'Y') THEN
186 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, '*** Finish: chord_doc_type ***');
187 END IF;
188
189
190 EXCEPTION
191 WHEN e_invalid_doc_type THEN
192 IF (g_po_wf_debug = 'Y') THEN
193 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, '*** exception chord_doc_type ***');
194 END IF;
195 wf_core.context('POAPPRV', 'chord_doc_type', 'e_invalid_doc_type');
196 RAISE;
197
198 WHEN OTHERS THEN
199 wf_core.context('POAPPRV', 'chord_doc_type', 'others');
200 RAISE;
201
202 END;
203
204 /*
205 * This procedure needs to be called with an itemkey
206 */
207 PROCEDURE Start_WF_Process ( ItemType VARCHAR2,
208 ItemKey VARCHAR2,
209 WorkflowProcess VARCHAR2,
210 ActionOriginatedFrom VARCHAR2,
211 DocumentID NUMBER,
212 DocumentNumber VARCHAR2,
213 PreparerID NUMBER,
214 DocumentTypeCode VARCHAR2,
215 DocumentSubtype VARCHAR2,
216 SubmitterAction VARCHAR2,
217 forwardToID NUMBER,
218 forwardFromID NUMBER,
219 DefaultApprovalPathID NUMBER)
220 is
221
222 BEGIN
223
224 -- DBMS_OUTPUT.enable(100000); Commenting out as problems may be caused.
225
226 IF ( ItemType is NOT NULL ) AND
227 ( ItemKey is NOT NULL) AND
228 ( DocumentID is NOT NULL ) THEN
229
230 wf_engine.CreateProcess( ItemType => ItemType,
231 ItemKey => ItemKey,
232 process => WorkflowProcess );
233 --
234 -- Initialize workflow item attributes
235 --
236
237 wf_engine.SetItemAttrText ( itemtype => itemtype,
238 itemkey => itemkey,
239 aname => 'INTERFACE_SOURCE_CODE',
240 avalue => ActionOriginatedFrom);
241 --
242
243 wf_engine.SetItemAttrText ( itemtype => itemtype,
244 itemkey => itemkey,
245 aname => 'DOCUMENT_NUMBER',
246 avalue => DocumentNumber);
247 --
248 wf_engine.SetItemAttrNumber ( itemtype => itemType,
249 itemkey => itemkey,
250 aname => 'DOCUMENT_ID',
251 avalue => DocumentID);
252 --
253 wf_engine.SetItemAttrText ( itemtype => itemtype,
254 itemkey => itemkey,
255 aname => 'DOCUMENT_TYPE',
256 avalue => DocumentTypeCode);
257 --
258 wf_engine.SetItemAttrText ( itemtype => itemtype,
259 itemkey => itemkey,
260 aname => 'DOCUMENT_SUBTYPE',
261 avalue => DocumentSubtype);
262 --
263 wf_engine.SetItemAttrNumber ( itemtype => itemType,
264 itemkey => itemkey,
265 aname => 'PREPARER_ID',
266 avalue => PreparerID);
267 --
268 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
269 itemkey => itemkey,
270 aname => 'FORWARD_TO_ID',
271 avalue => ForwardToID);
272 --
273 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
274 itemkey => itemkey,
275 aname => 'FORWARD_FROM_ID',
276 avalue => ForwardToID);
277 --
278 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
279 itemkey => itemkey,
280 aname => 'APPROVAL_PATH_ID',
281 avalue => DefaultApprovalPathID);
282
283
284 --
285 wf_engine.StartProcess( itemtype => itemtype,
286 itemkey => itemkey );
287
288 END IF;
289
290 EXCEPTION
291 WHEN OTHERS THEN
292 wf_core.context('POAPPRV', 'start_wf_process', 'others');
293 RAISE;
294
295 END Start_WF_Process;
296
297
298 FUNCTION percentage_change(old_value IN NUMBER,
299 new_value IN NUMBER) return NUMBER
300 IS
301 x_percent NUMBER;
302 BEGIN
303 IF (old_value = 0) THEN
304
305 IF new_value = 0 Then
306 x_percent := 0;
307 ELSE
308 /* This is actually infinity */
309 x_percent := 100;
310 END IF;
311
312 ELSIF old_value IS NULL THEN
313 x_percent := nvl(new_value,0) * 100;
314 ELSE
315 x_percent := (nvl(new_value,0) - old_value)/old_value * 100;
316 END IF;
317
318 return(x_percent);
319
320 END;
321
322
323 /**************************************************************************
324 * PROCEDURE: archive_on_approval_set
325 * DESCRIPTION: This procedure check for archive revision flag
326 * set in the PO Document Types form.
327 * It returns 'Y' if it is set to 'APPROVAL';
328 * Otherwise 'N'.
329 * This check is mainly used by Change Order.
330 * Change Order should only executed if archive on
331 * approval is set.
332 *************************************************************************/
333
334 PROCEDURE archive_on_approval_set(itemtype IN VARCHAR2,
335 itemkey IN VARCHAR2,
336 actid IN NUMBER,
337 FUNCMODE IN VARCHAR2,
338 RESULT OUT NOCOPY VARCHAR2)
339 IS
340 x_archive_code VARCHAR2(25):='';
341 x_org_id NUMBER:='';
342 x_doc_type VARCHAR2(25):='';
343 x_doc_subtype VARCHAR2(25):='';
344 x_result VARCHAR2(1):='N';
345 BEGIN
346
347 IF (g_po_wf_debug = 'Y') THEN
348 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
349 '*** In Procedure: archive_on_approval ***');
350 END IF;
351
352 x_org_id:= wf_engine.GetItemAttrNumber (itemtype => itemType,
353 itemkey => itemkey,
354 aname => 'ORG_ID');
355
356 x_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
357 itemkey => itemkey,
358 aname => 'DOCUMENT_TYPE');
359
360 x_doc_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
361 itemkey => itemkey,
362 aname => 'DOCUMENT_SUBTYPE');
363
364 /* BUG# 1180957: draising
365 ** Forward fix of Bug# 1180198
366 ** Desc : We need to set the org_id and use stripped tables
367 ** to get the ARCHIVE_EXTERNAL_REVISION_CODE. The previous
368 ** code to get data from the PO_DOCUMENT_TYPES_ALL table
369 ** an org_id would fail for non- multi-org environment.
370 */
371
372 IF x_org_id is NOT NULL THEN
373
374 PO_MOAC_UTILS_PVT.set_org_context(x_org_id) ; -- <R12 MOAC>
375
376 END IF;
377 IF x_doc_type IS NOT NULL AND
378 x_doc_subtype IS NOT NULL THEN
379 Begin
380
381 SELECT ARCHIVE_EXTERNAL_REVISION_CODE
382 INTO x_archive_code
383 FROM PO_DOCUMENT_TYPES podta
384 WHERE podta.DOCUMENT_TYPE_CODE = x_doc_type
385 AND podta.DOCUMENT_SUBTYPE = x_doc_subtype;
386
387 Exception
388 WHEN NO_DATA_FOUND THEN
389 null;
390 WHEN OTHERS THEN
391 null;
392 End;
393
394 END IF;
395
396 IF nvl(x_archive_code, 'UNDEFINED') = 'APPROVE' THEN
397 x_result:='Y';
398 ELSE
399 x_result:='N';
400 END IF;
401
402 IF (g_po_wf_debug = 'Y') THEN
403 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
404 '*** Finished: archive_on_approval ***');
405 END IF;
406
407 result := wf_engine.eng_completed || ':' || x_result;
408 return;
409
410 EXCEPTION
411
412 WHEN OTHERS THEN
413 IF (g_po_wf_debug = 'Y') THEN
414 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
415 '*** execption archive_on_approval_set *** '||x_result);
416 END IF;
417 wf_core.context('POAPPRV', 'po_chord_wf0.archive_on_approval_set', 'result '||x_result);
418 RAISE;
419
420 END;
421
422
423 END PO_CHORD_WF0;