eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PO_CLM_CAR_WF_UTIL

Source


1 PACKAGE BODY PO_CLM_CAR_WF_UTIL AS
2 /* $Header: POCARWFB.pls 120.7.12020000.3 2013/04/04 06:02:04 vpeddi ship $ */
3 PROCEDURE get_car_logs(document_id	in	varchar2,
4                        display_type	in	varchar2,
5                        document	in out	NOCOPY varchar2,
6                        document_type	in out	NOCOPY varchar2) IS
7 
8   l_document VARCHAR2(32000) := '';
9   l_item_type    wf_items.item_type%TYPE;
10   l_item_key     wf_items.item_key%TYPE;
11   l_document_id NUMBER;
12   l_draft_id  NUMBER;
13   NL  VARCHAR2(1) := fnd_global.newline;
14 
15   TYPE car_num_tbl_type IS TABLE OF PO_CLM_CARS.CAR_NUMBER%TYPE;
16   TYPE err_code_tbl_type IS TABLE OF PO_CLM_CAR_LOGS.MESSAGE_CODE%TYPE;
17   TYPE err_level_tbl_type IS TABLE OF VARCHAR2(60);
18   TYPE err_text_tbl_type IS TABLE OF PO_CLM_CAR_LOGS.MESSAGE_TEXT%TYPE;
19 
20   l_car_num_tbl car_num_tbl_type;
21   l_err_code_tbl err_code_tbl_type;
22   l_err_level_tbl err_level_tbl_type;
23   l_err_text_tbl err_text_tbl_type;
24 
25   CURSOR car_logs_csr(v_document_id NUMBER, v_draft_id NUMBER) IS
26   SELECT
27   po_clm_car_util.get_car_number(PO_HEADER_ID, PO_DRAFT_ID) CAR_NUMBER,
28   MESSAGE_CODE,
29   NVL(PLC.DISPLAYED_FIELD, MESSAGE_LEVEL) MESSAGE_LEVEL_MEANING,
30   MESSAGE_TEXT
31   FROM
32   PO_CLM_CAR_LOGS LOG, po_lookup_codes PLC
33   WHERE
34   LOG.PO_HEADER_ID = v_document_id AND
35   LOG.PO_DRAFT_ID = v_draft_id AND
36   PLC.LOOKUP_TYPE(+) = 'PO_CLM_CAR_MESSAGE_LEVELS' AND
37   LOG.MESSAGE_LEVEL = PLC.LOOKUP_CODE (+);
38 
39   l_count NUMBER := 0;
40   curr_len  NUMBER := 0;
41   prior_len NUMBER := 0;
42 BEGIN
43   l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
44   l_item_key := substr(document_id, instr(document_id, ':') + 1);
45   l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => l_item_type,
46                                                      itemkey  => l_item_key,
47                                                      aname    => 'DOCUMENT_ID');
48   l_draft_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => l_item_type,
49                                                   itemkey  => l_item_key,
50                                                   aname    => 'DRAFT_ID');
51 
52   OPEN car_logs_csr(l_document_id, l_draft_id);
53   FETCH car_logs_csr BULK COLLECT INTO l_car_num_tbl
54                                       , l_err_code_tbl
55                                       , l_err_level_tbl
56                                       , l_err_text_tbl;
57   l_count := car_logs_csr%ROWCOUNT;
58   CLOSE car_logs_csr;
59 
60   IF (display_type = 'text/html') THEN
61     l_document := '<TABLE border=1 cellpadding=2 cellspacing=1 summary="' || fnd_message.get_string('PO', 'PO_CLM_CAR_LOGS_TBL_SUM') || '">' || NL;
62 
63     l_document := l_document || '<TR>';
64 
65     l_document := l_document || '<TH id="carNum_1">' ||
66                   fnd_message.get_string('PO', 'PO_CLM_CAR_NUM') || '</TH>' || NL;
67 
68     l_document := l_document || '<TH id="errCode_1">' ||
69                   fnd_message.get_string('PO', 'PO_CLM_CAR_ERR_CODE') || '</TH>' || NL;
70 
71     l_document := l_document || '<TH id="errLevel_1">' ||
72                   fnd_message.get_string('PO', 'PO_CLM_CAR_ERR_LEVEL') || '</TH>' || NL;
73 
74     l_document := l_document || '<TH id="errText_1">' ||
75                   fnd_message.get_string('PO', 'PO_CLM_CAR_ERR_TEXT') || '</TH>' || NL;
76 
77     l_document := l_document || '</TR>' || NL;
78 
79     curr_len  := lengthb(l_document);
80     prior_len := curr_len;
81 
82     FOR i IN 1..l_count LOOP
83       /* Exit the cursor if the current document length and 2 times the
84       ** length added in prior line exceeds 32000 char */
85       if (curr_len + (2 * (curr_len - prior_len))) >= 32000 then
86          exit;
87       end if;
88 
89       prior_len := curr_len;
90 
91       l_document := l_document || '<TR>' || NL;
92 
93       l_document := l_document || '<TD nowrap align=center headers="carNum_1">' ||
94 					 nvl(to_char(l_car_num_tbl(i)), ' ') || '</TD>' || NL;
95       l_document := l_document || '<TD nowrap align=center headers="errCode_1">' ||
96 					 nvl(to_char(l_err_code_tbl(i)), ' ') || '</TD>' || NL;
97       l_document := l_document || '<TD nowrap align=center headers="errLevel_1">' ||
98 					 nvl(to_char(l_err_level_tbl(i)), ' ') || '</TD>' || NL;
99       l_document := l_document || '<TD nowrap align=center headers="errText_1">' ||
100 					 nvl(to_char(l_err_text_tbl(i)), ' ') || '</TD>' || NL;
101 
102       l_document := l_document || '</TR>' || NL;
103       curr_len  := lengthb(l_document);
104     END LOOP;
105 
106     l_document := l_document || '</TABLE></P>' || NL;
107     document := l_document;
108 
109   ELSIF (display_type = 'text/plain') THEN
110 
111     FOR i IN 1..l_count LOOP
112       /* Exit the cursor if the current document length and 2 times the
113       ** length added in prior line exceeds 32000 char */
114       if (curr_len + (2 * (curr_len - prior_len))) >= 32000 then
115          exit;
116       end if;
117 
118       prior_len := curr_len;
119 
120       l_document := l_document || NL;
121 
122       l_document := l_document || fnd_message.get_string('PO', 'PO_CLM_CAR_NUM') || ':' || l_car_num_tbl(i) || NL;
123       l_document := l_document || fnd_message.get_string('PO', 'PO_CLM_CAR_ERR_CODE') || ':' || l_err_code_tbl(i) || NL;
124       l_document := l_document || fnd_message.get_string('PO', 'PO_CLM_CAR_ERR_LEVEL') || ':' || l_err_level_tbl(i) || NL;
125       l_document := l_document || fnd_message.get_string('PO', 'PO_CLM_CAR_ERR_TEXT') || ':' || l_err_text_tbl(i) || NL;
126 
127       l_document := l_document || NL;
128       curr_len  := lengthb(l_document);
129     END LOOP;
130 
131     document := l_document;
132 
133   END IF;
134 
135 END;
136 
137 PROCEDURE get_service_attr(itemtype in varchar2,
138                           itemkey in varchar2,
139                           actid in number,
140                           funcmode in varchar2,
141                           resultout out NOCOPY varchar2) IS
142   l_document_id NUMBER;
143   l_draft_id  NUMBER;
144   l_car_id  NUMBER;
145   l_reporting_method VARCHAR2(60);
146   l_car_status VARCHAR2(60);
147 
148 BEGIN
149 
150   IF (funcmode <> wf_engine.eng_run) THEN
151     resultout := wf_engine.eng_null;
152     RETURN;
153   END IF;
154 
155   l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
156                                                      itemkey  => itemkey,
157                                                      aname    => 'DOCUMENT_ID');
158   l_draft_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
159                                                   itemkey  => itemkey,
160                                                   aname    => 'DRAFT_ID');
161 
162   IF l_draft_id IS NULL THEN
163     l_draft_id := -1;
164   END IF;
165 
166   SELECT CAR_ID, REPORTING_METHOD, CAR_STATUS
167   INTO l_car_id , l_reporting_method, l_car_status
168   FROM PO_CLM_CARS
169   WHERE PO_HEADER_ID = l_document_id
170   AND PO_DRAFT_ID = l_draft_id;
171 
172   PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
173                                    itemkey => itemkey,
174                                    aname   => 'CAR_ID',
175                                    avalue  => l_car_id);
176   PO_WF_UTIL_PKG.SetItemAttrText(itemtype=>itemtype,
177                                    itemkey => itemkey,
178                                    aname   => 'INVOKER_KEY',
179                                    avalue  => itemkey);
180 
181   IF (l_reporting_method = 'NONE' OR
182          (l_reporting_method = 'SINGLE_CAR' AND l_car_status = 'NOT_REPORTED')) THEN
183 	resultout := 'COMPLETE' || ':' ||  'FAILURE';
184   ELSE
185 	resultout := 'COMPLETE' || ':' ||  'SUCCESS';
186   END IF;
187 EXCEPTION
188   WHEN OTHERS THEN
189     resultout := 'COMPLETE' || ':' ||  'FAILURE';
190 END;
191 
192 PROCEDURE get_fpds_notif_attr(itemtype in varchar2,
193                               itemkey in varchar2,
194                               actid in number,
195                               funcmode in varchar2,
196                               resultout out NOCOPY varchar2) IS
197   l_document_id NUMBER;
198   l_draft_id  NUMBER;
199   l_document_subtype  VARCHAR2(25);
200   l_doc_type_disp VARCHAR2(25);
201   l_performer_user_name VARCHAR2(25);
202   l_buyer_user_name VARCHAR2(25);
203   l_approver_user_name VARCHAR2(25);
204   l_notif_type VARCHAR2(25) := null;
205   l_update_fpds_info VARCHAR2(200);
206   l_fnd_org_id NUMBER;
207   l_mo_org_id NUMBER;
208 BEGIN
209 
210   IF (funcmode <> wf_engine.eng_run) THEN
211     resultout := wf_engine.eng_null;
212     RETURN;
213   END IF;
214 
215   l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
216                                                      itemkey  => itemkey,
217                                                      aname    => 'DOCUMENT_ID');
218   l_draft_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
219                                                   itemkey  => itemkey,
220                                                   aname    => 'DRAFT_ID');
221   l_document_subtype := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
222                                                         itemkey  => itemkey,
223                                                         aname    => 'DOCUMENT_SUBTYPE');
224   l_buyer_user_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
225                                                         itemkey  => itemkey,
226                                                         aname    => 'BUYER_USER_NAME');
227   l_approver_user_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
228                                                         itemkey  => itemkey,
229                                                         aname    => 'APPROVER_USER_NAME');
230 
231   l_update_fpds_info := 'JSP:/OA_HTML/OA.jsp?OAFunc=PO_UPDATE_FPDSNG_INFO&poHeaderId=' || To_Char(l_document_id)
232      || '&DraftId=' || To_Char(l_draft_id)  || '&DOCUMENT_MODE=update&addBreadCrumb=Y';
233 
234   IF l_draft_id IS NOT NULL AND l_draft_id <> -1 THEN
235     l_doc_type_disp := 'Modification';
236   ELSIF l_document_subtype = 'STANDARD' THEN
237     l_doc_type_disp := 'Award';
238   ELSE
239     l_doc_type_disp := 'IDV';
240   END IF;
241 
242   -- Bug 11890961 Changes
243   l_fnd_org_id := fnd_global.org_id;
244   l_mo_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
245 
246   IF (l_fnd_org_id <> l_mo_org_id) then
247     fnd_global.initialize('ORG_ID',l_mo_org_id);
248   END IF;
249 
250   l_notif_type := fnd_profile.value('PO_CLM_FPDS_APPROV_NOTIF_TYPE');
251 
252   IF (l_fnd_org_id <> l_mo_org_id) then
253     fnd_global.initialize('ORG_ID',l_fnd_org_id);
254   END IF;
255 
256   IF l_notif_type IS NULL OR l_notif_type = 'BUYER' THEN
257     l_performer_user_name := l_buyer_user_name;
258   ELSE
259     l_performer_user_name := l_approver_user_name;
260   END IF;
261 
262   PO_WF_UTIL_PKG.SetItemAttrText(itemtype=>itemtype,
263                                   itemkey => itemkey,
264                                   aname   => 'DOCUMENT_TYPE_DISP',
265                                   avalue  => l_doc_type_disp);
266   PO_WF_UTIL_PKG.SetItemAttrText(itemtype=>itemtype,
267                                   itemkey => itemkey,
268                                   aname   => 'PERFORMER_USER_NAME',
269                                   avalue  => l_performer_user_name);
270   PO_WF_UTIL_PKG.SetItemAttrText(itemtype=>itemtype,
271                                   itemkey => itemkey,
272                                   aname   => 'CAR_LOGS',
273                                   avalue  => 'PLSQL:PO_CLM_CAR_WF_UTIL.GET_CAR_LOGS/' || itemtype || ':' || itemkey);
274 
275   PO_WF_UTIL_PKG.SetItemAttrText(itemtype=>itemtype,
276                                    itemkey => itemkey,
277                                    aname   => 'UPDATE_FPDS_INFO',
278                                    avalue  => l_update_fpds_info);
279 
280   resultout := 'COMPLETE';
281 
282 END;
283 
284 PROCEDURE get_service_result(itemtype in varchar2,
285                               itemkey in varchar2,
286                               actid in number,
287                               funcmode in varchar2,
288                               resultout out NOCOPY varchar2) IS
289   l_evt wf_event_t;
290   l_ret VARCHAR(25);
291 BEGIN
292 
293   if (funcmode <> wf_engine.eng_run) then
294     resultout := wf_engine.eng_null;
295     return;
296   end if;
297 
298   l_evt := wf_engine.GetItemAttrEvent(itemtype, itemkey, 'RECEIVER_DATA');
299   l_ret := l_evt.GetEventData;
300 
301   IF l_ret = 'S' THEN
302     resultout := 'COMPLETE' || ':' ||  'SUCCESS';
303   ELSE
304     resultout := 'COMPLETE' || ':' ||  'FAILURE';
305   END IF;
306 
307 END;
308 
309 PROCEDURE is_fpds_rep_needed(itemtype in varchar2,
310 					 		 itemkey in varchar2,
311 							 actid in number,
312 					 		 funcmode in varchar2,
313 							 resultout out NOCOPY varchar2) IS
314 
315   l_fpds_reporting VARCHAR2(1);
316   l_document_id NUMBER;
317   l_draft_id NUMBER;
318   l_reporting_method VARCHAR2(60);
319   l_approved_without_reporting VARCHAR2(1);
320   l_org_id NUMBER; --CLM Controls Project Changes
321 
322 BEGIN
323 
324   IF (funcmode <> wf_engine.eng_run) THEN
325     resultout := wf_engine.eng_null;
326     RETURN;
327   END IF;
328 
329   --CLM Controls Project changes
330   l_org_id := po_wf_util_pkg.GetItemAttrNumber( itemtype =>
331 					   itemtype,
332 					   itemkey  =>
333 					   itemkey,
334 					   aname  =>
335 					   'ORG_ID');
336   SELECT PO_CORE_S.retrieveOptionValue(p_org_id => l_org_id,
337                                   p_option_column => PO_CORE_S.g_FPDS_REPORTING_COL)
338   into l_fpds_reporting
339   FROM dual;
340 
341   IF l_fpds_reporting is null OR l_fpds_reporting = 'N' THEN
342     resultout := wf_engine.eng_completed || ':' ||  'N';
343     RETURN;
344   END IF;
345 
346   l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype =>
347 					   itemtype,
348 					   itemkey  =>
349 					   itemkey,
350 					   aname
351 					   =>
352 					   'DOCUMENT_ID');
353   l_draft_id := po_wf_util_pkg.GetItemAttrNumber( itemtype =>
354 					itemtype,
355 					itemkey  =>
356 					itemkey,
357 					aname
358 					=>
359 					'DRAFT_ID');
360 
361   IF l_draft_id IS NULL THEN
362     l_draft_id := -1;
363   END IF;
364 
365   BEGIN
366     SELECT REPORTING_METHOD, APPROVED_WITHOUT_REPORTING
367     INTO l_reporting_method, l_approved_without_reporting
368     FROM PO_CLM_CARS
369     WHERE PO_HEADER_ID = l_document_id
370     AND PO_DRAFT_ID = l_draft_id;
371   EXCEPTION
372   WHEN no_data_found THEN
373     resultout := wf_engine.eng_completed || ':' || 'Y';
374 	RETURN;
375   END;
376 
377   IF (l_reporting_method <> 'EXEMPT' AND nvl(l_approved_without_reporting, '2') <> '1') THEN
378       resultout := wf_engine.eng_completed || ':' || 'Y';
379   ELSE
380       resultout := wf_engine.eng_completed || ':' || 'N';
381   END IF;
382 
383 END;
384 
385 PROCEDURE IS_CAR_AUTHENTICATED( itemtype in varchar2,
386 				itemkey in varchar2,
387 				actid in number,
388 				funcmode in varchar2,
389 				resultout out NOCOPY varchar2) IS
390   l_document_id NUMBER;
391   l_draft_id NUMBER;
392   l_car_authenticated VARCHAR2(5);
393 
394 BEGIN
395 
396   IF (funcmode <> wf_engine.eng_run) THEN
397     resultout := wf_engine.eng_null;
398     RETURN;
399   END IF;
400 
401   l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype =>
402 					   itemtype,
403 					   itemkey  =>
404 					   itemkey,
405 					   aname
406 					   =>
407 					   'DOCUMENT_ID');
408   l_draft_id := po_wf_util_pkg.GetItemAttrNumber( itemtype =>
409 					itemtype,
410 					itemkey  =>
411 					itemkey,
412 					aname
413 					=>
414 					'DRAFT_ID');
415 
416   IF l_draft_id IS NULL THEN
417     l_draft_id := -1;
418   END IF;
419 
420   BEGIN
421     SELECT 'Y'
422     INTO l_car_authenticated
423     FROM PO_CLM_CARS
424     WHERE PO_HEADER_ID = l_document_id
425     AND PO_DRAFT_ID = l_draft_id
426     AND CAR_STATUS in ('AUTHENTICATED', 'APPROVED')
427     AND REPORTING_METHOD = 'SINGLE_CAR';
428   EXCEPTION
429   WHEN no_data_found THEN
430     resultout := wf_engine.eng_completed || ':' || 'N';
431 	RETURN;
432   END;
433 
434   resultout := wf_engine.eng_completed || ':' ||  'Y';
435 
436 END;
437 
438 END;
439