1 PACKAGE BODY PO_DOCUMENT_ACTIONS_SV AS
2 /* $Header: POXDORAB.pls 120.4.12020000.5 2013/05/14 08:04:56 pamandav ship $*/
3
4 -- <HTMLAC BEGIN>
5 G_PKG_NAME CONSTANT varchar2(30) := 'po_document_actions_sv';
6
7 -- Start of comments
8 -- API name : po_request_action_bulk
9 -- Type : Public
10 -- Pre-reqs : None.
11 -- Function : Calls po_request_action for each line that needs
12 -- to have an action requsted.
13 -- Action is hard coded to requisition return!
14 -- Parameters :
15 -- IN : p_api_version IN NUMBER Required
16 -- p_reason IN varchar2 Required
17 -- The reason needed to return this document if any
18 -- p_employee_id IN NUMBER Required
19 -- The employee_id to whom we will send a notification.
20 -- p_grouping_method IN varchar2 Required
21 -- The req grouping selected from the UI
22 -- p_req_header_id_tbl IN PO_TBL_NUMBER Required
23 -- The table containing the req_header_id column.
24 -- OUT : x_result OUT NUMBER
25 -- x_error_message OUT VARCHAR2
26 -- x_online_report_id_tbl OUT PO_TBL_NUMBER
27 -- The online report ids that have been generated.
28 -- x_req_header_id_succ_tbl OUT PO_TBL_NUMBER
29 -- Contains all the header_ids of the successful reqs.
30 -- Version : Current version 1.0
31 -- Previous version 1.0
32 -- Initial version 1.0
33 -- End of comments
34 PROCEDURE po_request_action_bulk (
35 p_api_version IN NUMBER,
36 x_result OUT NOCOPY NUMBER,
37 x_error_message OUT NOCOPY VARCHAR2,
38 p_reason IN VARCHAR2 := NULL,
39 p_employee_id IN NUMBER,
40 p_req_header_id_tbl IN PO_TBL_NUMBER,
41 x_online_report_id_tbl OUT NOCOPY PO_TBL_NUMBER,
42 x_req_header_id_succ_tbl OUT NOCOPY PO_TBL_NUMBER
43 ) IS
44 -- Standard api variables
45 l_api_name CONSTANT VARCHAR2(30) := 'po_request_action_bulk';
46 l_api_version CONSTANT NUMBER := 1.0;
47 -- Count variables
48 l_num_lines NUMBER; -- num lines passed in
49 l_out_online_count NUMBER; -- count of the online report
50 l_out_header_count NUMBER; -- count of the succ header
51 -- Bulk collect
52 l_id_key NUMBER;
53 -- First their table types
54 TYPE l_doc_sub_type_list_type IS TABLE OF
55 po_requisition_headers_all.type_lookup_code%TYPE;
56 TYPE l_approval_id_list_type IS TABLE OF
57 po_document_types_all.default_approval_path_id%TYPE;
58 TYPE l_org_id_list_type IS TABLE OF
59 po_requisition_headers_all.org_id%TYPE;
60
61 TYPE l_rowid_char_tbl_type IS TABLE OF VARCHAR2(18);
62 -- The local tables
63 l_doc_sub_type_tbl l_doc_sub_type_list_type;
64 l_approval_id_tbl l_approval_id_list_type;
65 l_org_id_tbl l_org_id_list_type;
66 l_rowid_char_tbl l_rowid_char_tbl_type;
67
68 -- <Doc Manager Rewrite 11.5.11 Start>
69 -- replace po_request_action call with po_document_action_pvt.do_return call
70 l_ret_sts VARCHAR2(1);
71 l_online_report_id NUMBER;
72 l_return_code VARCHAR2(25);
73 l_error_message VARCHAR2(2000);
74 -- <Doc Manager Rewrite 11.5.11 End>
75
76 -- original org context
77 l_original_org_context NUMBER := null;
78 BEGIN
79 -- Standard Start of API savepoint
80 SAVEPOINT po_request_action_bulk_sp;
81 -- Standard Call to check for call compatibility
82 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
83 p_api_version ,
84 l_api_name ,
85 G_PKG_NAME )
86 THEN
87 x_error_message := 'API version check raised exception';
88 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
89 END IF;
90
91 -- API body
92 -- First lets get the bulk collects for org_id, l_doc_sub_type,
93 -- l_approval_id
94 l_id_key := PO_CORE_S.get_session_gt_nextval();
95 l_num_lines := p_req_header_id_tbl.COUNT;
96
97
98 -- What we are trying to do is to insert these rows into the po_session_gt
99 -- table and then do a bulk collect.
100 -- For 8i compatibility, need to do 2 bulk collects.
101 -- Need to have one to insert and then another one to update.
102 FORALL i in 1..l_num_lines
103 INSERT INTO PO_SESSION_GT(key, num1)
104 VALUES (l_id_key, p_req_header_id_tbl(i))
105 RETURNING ROWIDTOCHAR(rowid)
106 BULK COLLECT INTO l_rowid_char_tbl
107 ;
108
109 FORALL i in 1..l_num_lines
110 UPDATE PO_SESSION_GT SES
111 SET
112 ( char1, -- document sub type
113 num2, -- approval id
114 num3 -- org_id
115 )
116 =
117 ( SELECT prh.type_lookup_code,
118 podc.default_approval_path_id,
119 prh.org_id
120 FROM po_requisition_headers_all prh,
121 po_document_types_all podc
122 WHERE prh.requisition_header_id = p_req_header_id_tbl(i)
123 AND podc.document_type_code = 'REQUISITION'
124 AND podc.document_subtype = prh.type_lookup_code
125 AND podc.org_id = prh.org_id -- <R12 MOAC>
126 )
127 WHERE SES.rowid = CHARTOROWID(l_rowid_char_tbl(i))
128 RETURNING
129 char1,
130 num2,
131 num3
132 BULK COLLECT INTO
133 l_doc_sub_type_tbl,
134 l_approval_id_tbl,
135 l_org_id_tbl
136 ;
137
138 -- Initialise the count for the online report list and the header list
139 -- to zero.
140 l_out_online_count := 0;
141 l_out_header_count := 0;
142
143 -- Initialise the two tables
144 x_online_report_id_tbl := PO_TBL_NUMBER();
145 x_req_header_id_succ_tbl := PO_TBL_NUMBER();
146
147 --<<PAR Project - START>>
148 --Update the header status to returned for PAR document,if no par line exists on any mod.
149 UPDATE po_drafts
150 SET status = 'RETURNED'
151 WHERE draft_id IN (SELECT par_draft_id
152 FROM po_requisition_headers_all
153 -- Bug 16778189- Use column_value instaed of requisition_header_id
154 WHERE requisition_header_id IN (SELECT column_value FROM TABLE(p_req_header_id_tbl)))
155 AND NOT EXISTS (SELECT par_line_id
156 FROM po_requisition_lines_all prl
157 WHERE requisition_header_id IN (SELECT column_value FROM TABLE(p_req_header_id_tbl))
158 -- Use Exists clause instead of Not in
159 AND EXISTS (SELECT po_line_id FROM po_lines_draft_all plda WHERE prl.par_line_id = plda.po_line_id
160 AND mod_draft_id IS NOT NULL)) ;
161
162 IF (SQL%ROWCOUNT > 0) THEN
163 PO_LOG.stmt(l_api_name, 10, 'No of records updated in po_drafts ', SQL%ROWCOUNT);
164 END IF;
165
166
167 --Update the line status to returned for PAR document,if the par line doesnt exists on any mod.
168 UPDATE po_lines_draft_all
169 SET DRAFT_LINE_STATUS ='RETURNED'
170 WHERE po_line_id IN (SELECT par_line_id
171 FROM po_requisition_lines_all
172 WHERE requisition_header_id IN (SELECT column_value FROM TABLE(p_req_header_id_tbl)))
173 --Bug 16778189 - Include draftId condition
174 AND draft_id IN (SELECT par_draft_id
175 FROM po_requisition_headers_all
176 WHERE requisition_header_id IN (SELECT column_value FROM TABLE(p_req_header_id_tbl)))
177 AND mod_draft_id IS NULL;
178
179 IF (SQL%ROWCOUNT > 0) THEN
180 PO_LOG.stmt(l_api_name, 20, 'No of records updated in po_lines_draft_all ', SQL%ROWCOUNT);
181 END IF;
182
183 --In case of error, the txn will be rolled back.
184 --<<PAR Project - END>>
185
186 -- <Doc Manager Rewrite 11.5.11 Start>
187 -- Use PO_DOCUMENT_ACTION_PVT.do_return instead of po_request_action call
188
189 FOR i IN 1..l_num_lines
190 LOOP
191
192 PO_DOCUMENT_ACTION_PVT.do_return(
193 p_document_id => p_req_header_id_tbl(i)
194 , p_document_type => 'REQUISITION'
195 , p_document_subtype => l_doc_sub_type_tbl(i)
196 , p_note => p_reason
197 , p_approval_path_id => l_approval_id_tbl(i)
198 , x_return_status => l_ret_sts
199 , x_return_code => l_return_code
200 , x_exception_msg => l_error_message
201 , x_online_report_id => l_online_report_id
202 );
203
204 -- Information we need is: l_return_code,
205 -- l_error_message and l_online_report_id
206
207 -- Check for unexpected errors. An unexpected error happens if for
208 -- some reason l_return_code is not getting set.
209 -- Doc Manager Rewrite: also check for STATE_FAILED return code
210 IF(l_ret_sts <> 'S') THEN
211 x_error_message := l_error_message;
212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
213 ELSIF (l_return_code = 'STATE_FAILED') THEN
214 x_error_message := 'Document state check failed.';
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 ELSE
217 x_result := 1;
218 END IF;
219
220 -- <Doc Manager Rewrite 11.5.11 End>
221
222 -- Now let us look for the expected errors
223 IF (l_return_code IN ('F', 'P', 'T')) THEN
224 l_out_online_count := l_out_online_count + 1;
225 x_online_report_id_tbl.extend(1);
226 x_online_report_id_tbl(l_out_online_count) :=
227 l_online_report_id;
228 ELSE
229 l_out_header_count := l_out_header_count + 1;
230 x_req_header_id_succ_tbl.extend(1);
231 x_req_header_id_succ_tbl(l_out_header_count) :=
232 p_req_header_id_tbl(i);
233 -- Set the org context
234 IF(l_org_id_tbl(i) IS NOT NULL) THEN
235 l_original_org_context := PO_MOAC_UTILS_PVT.get_current_org_id; -- <R12 MOAC>
236 --IF(l_original_org_context <> l_org_id_tbl(i)) THEN -- <R12 MOAC> commented as this is already handles in PO_MOAC_UTILS_PVT.set_org_context
237 PO_MOAC_UTILS_PVT.set_org_context(l_org_id_tbl(i)) ; -- <R12 MOAC> Added
238 --END IF; -- <R12 MOAC>
239 END IF;
240
241 -- Send email notification of the success of the return
242 PO_AUTOCREATE_DOC.send_return_notif(p_req_header_id_tbl(i),
243 p_employee_id,
244 p_reason);
245 commit;
246 -- Set back the org context
247 --Commented out as code below. This is now handled
248 --in PO_MOAC_UTILS.set_org_context
249 PO_MOAC_UTILS_PVT.set_org_context(l_original_org_context) ; -- <R12 MOAC>
250 END IF;
251 END LOOP;
252
253 -- End of API body
254 EXCEPTION
255 WHEN FND_API.G_EXC_ERROR THEN
256 ROLLBACK TO po_request_action_bulk_sp;
257 IF(x_error_message is null) THEN
258 x_error_message := 'In Expected error of po_request_action_bulk';
259 END IF;
260 x_result := -1;
261 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
262 ROLLBACK TO po_request_action_bulk_sp;
263 IF(x_error_message is null) THEN
264 x_error_message := 'In Unexpected error';
265 END IF;
266 x_result := -1;
267 WHEN OTHERS THEN
268 ROLLBACK TO po_request_action_bulk_sp;
269 IF(x_error_message is null) THEN
270 x_error_message := 'In other error: ' || fnd_message.get;
271 END IF;
272 x_result := -1;
273 END po_request_action_bulk;
274 -- <HTMLAC END>
275
276 /*
277 ** The return values from this function and their definition
278 ** are the following:
279 **
280 ** E_SUCCESS constant number := 0; -- e_code is success
281 ** E_TIMEOUT constant number := 1; -- e_code is timeout
282 ** E_NOMGR constant number := 2; -- e_code is no manager
283 ** E_OTHER constant number := 3; -- e_code is other
284 */
285
286 -- <Doc Manager Rewrite R12>: Only actions that are still supported
287 -- through this package are:
288 -- 1. CANCEL - only from within PO
289 -- 2. IGC YEAR END RESERVE/UNRESERVE - only from IGC
290 -- 3. VERIFY_AUTHORITY_CHECK, UNRESERVE_DOCUMENT - only from ICX
291 -- Of the above, only CANCEL still goes through a Pro*C concurrent manager
292 -- This method should no longer be extended.
293 -- Please see replacements: PO_DOCUMENT_ACTIONS_PVT, PO_DOCUMENT_FUNDS_PVT/GRP
294
295 FUNCTION PO_REQUEST_ACTION (
296 Action IN VARCHAR2,
297 Document_Type IN VARCHAR2,
298 Document_Subtype IN VARCHAR2,
299 Document_Id IN NUMBER,
300 Line_Id IN NUMBER,
301 Shipment_Id IN NUMBER,
302 Distribution_Id IN NUMBER,
303 Employee_id IN NUMBER,
304 New_Document_Status IN VARCHAR2,
305 Offline_Code IN VARCHAR2,
306 Note IN VARCHAR2,
307 Approval_Path_Id IN NUMBER,
308 Forward_To_Id IN NUMBER,
309 Action_Date IN DATE,
310 Override_Funds IN VARCHAR2,
311 Info_Request OUT NOCOPY VARCHAR2,
312 Document_Status OUT NOCOPY VARCHAR2,
313 Online_Report_Id OUT NOCOPY NUMBER,
314 Return_Code OUT NOCOPY VARCHAR2,
315 Error_Msg OUT NOCOPY VARCHAR2,
316 --<CANCEL API FPI START>
317 p_extra_arg1 IN VARCHAR2,
318 p_extra_arg2 IN VARCHAR2,
319 p_extra_arg3 IN VARCHAR2,
320 --<CANCEL API FPI END>
321 p_extra_arg4 IN VARCHAR2 -- <ENCUMBRANCE FPJ>
322 )
323 RETURN NUMBER
324 IS
325
326 rc_sync NUMBER := 0;
327 rc NUMBER := 0;
328 outcome varchar2(200) := NULL;
329 message varchar2(200) := NULL;
330
331 -- <APPROVAL TIMEOUT VALUE FPI START>
332 -- Enh. Request: 2535262
333 -- Details : The timeout value to be read from the profile value
334 -- instead of defining a fixed value in this function.
335 -- Any new profile value may be set at site level.
336 -- By default, the site level profile value is 300 sec.
337 -- This is a change from earlier value of 180 sec.
338 timeout NUMBER := NVL(FND_PROFILE.value('PO_APPROVAL_TIMEOUT_VALUE'),180);
339 --
340 -- Commented for Enh.Request 2535262
341 -- timeout number := 180; /* Timeout to wait for the manager
342 -- to return is 180 Seconds */
343 -- <APPROVAL TIMEOUT VALUE FPI END>
344
345 r_val1 varchar2(200) := NULL;
346 r_val2 varchar2(200) := NULL;
347 r_val3 varchar2(200) := NULL;
348 r_val4 varchar2(200) := NULL;
349 r_val5 varchar2(200) := NULL;
350 r_val6 varchar2(200) := NULL;
351 r_val7 varchar2(200) := NULL;
352 r_val8 varchar2(200) := NULL;
353 r_val9 varchar2(200) := NULL;
354 r_val10 varchar2(200) := NULL;
355 r_val11 varchar2(200) := NULL;
356 r_val12 varchar2(200) := NULL;
357 r_val13 varchar2(200) := NULL;
358 r_val14 varchar2(200) := NULL;
359 r_val15 varchar2(200) := NULL;
360 r_val16 varchar2(200) := NULL;
361 r_val17 varchar2(200) := NULL;
362 r_val18 varchar2(200) := NULL;
363 r_val19 varchar2(200) := NULL;
364 r_val20 varchar2(200) := NULL;
365 parm_location NUMBER := NULL;
366 X_info_request varchar2(25) := NULL;
367 X_document_status varchar2(240) := NULL;
368 X_online_report_id NUMBER := NULL;
369 X_return_code varchar2(25) := NULL;
370 number_of_args NUMBER := 0;
371 X_progress varchar2(4) := '000';
372
373 --<ENCUMBRANCE FPJ>
374 l_return_status VARCHAR2(1);
375 l_enc_return_code VARCHAR2(10);
376
377 -- <Doc Manager Rewrite R12 Start>
378 l_exc_msg VARCHAR2(2000);
379 l_error_msg VARCHAR2(2000);
380 -- <Doc Manager Rewrite R12 End>
381
382 l_function_return_value NUMBER := 0;
383 l_return_exc EXCEPTION;
384
385 BEGIN
386
387 /*
388 ** Check if any of the last four in arguments are populated. If so
389 ** tell the server that there are extra arguments being passed in and
390 ** to parse those args
391 */
392 number_of_args := 17;
393
394 --<CANCEL API FPI START>
395 -- Increment the number of args for each extra arg passed in
396 IF p_extra_arg1 IS NOT NULL THEN
397 number_of_args := number_of_args + 1;
398 END IF;
399 IF p_extra_arg2 IS NOT NULL THEN
400 number_of_args := number_of_args + 1;
401 END IF;
402 IF p_extra_arg3 IS NOT NULL THEN
403 number_of_args := number_of_args + 1;
404 END IF;
405 --<CANCEL API FPI END>
406
407 -- <ENCUMBRANCE FPJ START>
408 IF p_extra_arg4 IS NOT NULL THEN
409 number_of_args := number_of_args + 1;
410 END IF;
411 -- <ENCUMBRANCE FPJ END>
412
413 X_progress := '100';
414
415
416 -- <ENCUMBRANCE FPJ START>
417 IF action IN ('IGC YEAR END RESERVE', 'IGC YEAR END UNRESERVE', 'UNRESERVE_DOCUMENT') THEN
418 -- these actions no longer go through the document manager
419 -- call the encumbrance code directly instead
420
421 IF action = 'IGC YEAR END RESERVE' THEN
422
423 PO_DOCUMENT_FUNDS_PVT.do_cbc_yearend_reserve(
424 x_return_status => l_return_status
425 , p_doc_type => document_type
426 , p_doc_subtype => document_subtype
427 , p_doc_level => 'HEADER'
428 , p_doc_level_id => document_id
429 , p_override_funds => PO_DOCUMENT_FUNDS_PVT.g_parameter_USE_PROFILE
430 , p_employee_id => employee_id
431 , x_po_return_code => l_enc_return_code
432 , x_online_report_id => online_report_id
433 );
434
435 -- <Doc Manager Rewrite R12>: IDTools analysis revealed that
436 -- UNRESERVE_DOCUMENT action may still be called in ICX code. So, added
437 -- call to do_unreserve.
438
439 ELSIF (action = 'UNRESERVE_DOCUMENT') THEN
440
441 -- for now, we assume that we do not commit.
442 -- previously, there was a commit in the doc manager.
443 -- if the commit is still necessary, then we can wrap this in
444 -- an autonomous transaction
445
446 PO_DOCUMENT_FUNDS_PVT.do_unreserve(
447 x_return_status => l_return_status
448 , p_doc_type => document_type
449 , p_doc_subtype => document_subtype
450 , p_doc_level => 'HEADER'
451 , p_doc_level_id => document_id
452 , p_use_enc_gt_flag => PO_DOCUMENT_FUNDS_PVT.g_parameter_NO
453 , p_validate_document => PO_DOCUMENT_FUNDS_PVT.g_parameter_NO
454 , p_override_funds => PO_DOCUMENT_FUNDS_PVT.g_parameter_NO
455 , p_use_gl_date => PO_DOCUMENT_FUNDS_PVT.g_parameter_NO
456 , p_override_date => SYSDATE
457 , p_employee_id => employee_id
458 , x_po_return_code => l_enc_return_code
459 , x_online_report_id => online_report_id
460 );
461
462 ELSE
463 -- Year End Unreserve
464 PO_DOCUMENT_FUNDS_PVT.do_cbc_yearend_unreserve(
465 x_return_status => l_return_status
466 , p_doc_type => document_type
467 , p_doc_subtype => document_subtype
468 , p_doc_level => 'HEADER'
469 , p_doc_level_id => document_id
470 , p_override_funds => PO_DOCUMENT_FUNDS_PVT.g_parameter_USE_PROFILE
471 , p_use_gl_date => PO_DOCUMENT_FUNDS_PVT.g_parameter_NO
472 , p_override_date => action_date
473 , p_employee_id => employee_id
474 , x_po_return_code => l_enc_return_code
475 , x_online_report_id => online_report_id
476 );
477
478 END IF;
479
480 IF (l_enc_return_code = PO_DOCUMENT_FUNDS_PVT.g_return_SUCCESS) THEN
481 return_code := 'S';
482 ELSIF (l_enc_return_code = PO_DOCUMENT_FUNDS_PVT.g_return_WARNING) THEN
483 return_code := 'A';
484 ELSIF (l_enc_return_code = PO_DOCUMENT_FUNDS_PVT.g_return_PARTIAL) THEN
485 return_code := 'P';
486 ELSIF (l_enc_return_code = PO_DOCUMENT_FUNDS_PVT.g_return_FAILURE) THEN
487 return_code := 'F';
488 ELSE
489 --fatal/SQL exception
490 return_code := 'T';
491 END IF;
492
493 --these OUT parameters are only used by the doc manager
494 Info_Request := NULL;
495 Document_Status := NULL;
496
497
498 IF l_return_status IN (FND_API.g_ret_sts_SUCCESS,
499 FND_API.g_ret_sts_ERROR) THEN
500 IF action IN ('IGC YEAR END RESERVE', 'IGC YEAR END UNRESERVE') THEN
501
502 -- CBC expects the encumbrance code to commit, unless
503 -- there was a SQL/fatal error
504 COMMIT;
505 END IF;
506 ELSE
507
508 IF (action = 'UNRESERVE_DOCUMENT') THEN
509 -- ICX expects error
510 l_function_return_value := 3;
511 END IF;
512
513 END IF;
514
515 -- <Doc Manager Rewrite R12>: VERIFY_AUTHORITY_CHECK is still called by ICX
516
517 ELSIF (action = 'VERIFY_AUTHORITY_CHECK') THEN
518
519 PO_DOCUMENT_ACTION_PVT.verify_authority(
520 p_document_id => document_id
521 , p_document_type => document_type
522 , p_document_subtype => document_subtype
523 , p_employee_id => employee_id
524 , x_return_status => l_return_status
525 , x_return_code => return_code
526 , x_exception_msg => l_exc_msg
527 , x_auth_failed_msg => l_error_msg
528 );
529
530 IF (l_return_status <> 'S') THEN
531 return_code := NULL;
532 l_function_return_value := 3;
533 error_msg := l_error_msg;
534 END IF;
535
536
537 ELSIF (action = 'CANCEL') THEN
538
539 -- Because fnd_transaction.synchronous restricts the number of parameters
540 -- to be less than 20, and for final close and cancel action we need
541 -- to pass in the extra argument (p_extra_arg4), for Use GL Date, we need
542 -- to remove one of the existing parameters. Since OVERRIDE_FUNDS is
543 -- currently always passed as NULL for these actions, and the backend
544 -- reads the profile value, we removed that argument. Additionally,
545 -- this was the only non-used argument that is NOT one of the doc
546 -- mngr's "standard parameters" (changing those requires greater impact)
547
548 number_of_args := number_of_args - 1;
549
550 rc_sync := FND_TRANSACTION.synchronous (
551 timeout ,
552 outcome ,
553 message ,
554 'PO', 'POXCON' ,
555 Action ,
556 Document_Type ,
557 Document_Subtype ,
558 Document_Id ,
559 Line_Id ,
560 Shipment_Id ,
561 Distribution_Id ,
562 Employee_Id ,
563 New_Document_Status ,
564 'ONLINE_MANAGER' || ':' || 'Y',
565 'OFFLINE_CODE' || ':' || Offline_Code,
566 'NOTE' || ':' || Note,
567 'APPROVAL_PATH_ID' || ':' || to_char(Approval_Path_Id),
568 'FORWARD_TO_ID' || ':' || to_char(Forward_To_Id),
569 'ACTION_DATE' || ':' || to_char(Action_Date, 'DD-MM-YYYY'),
570 /* Bug 7261397: Action_Date is now passed in the format of DD-MM-YYYY to avoid invalid
571 month issues for Cancel/finally close actions */
572 p_extra_arg1,
573 p_extra_arg2,
574 p_extra_arg3,
575 p_extra_arg4,
576 number_of_args);
577
578 /*
579 ** E_SUCCESS constant number := 0; -- e_code is success
580 ** E_TIMEOUT constant number := 1; -- e_code is timeout
581 ** E_NOMGR constant number := 2; -- e_code is no manager
582 ** E_OTHER constant number := 3; -- e_code is other
583 */
584 /* dbms_output.put_line ('Return From Sync = ' || to_char(rc_sync)); */
585
586 /*
587 ** If the call to the Document Action Manager process was successful
588 ** then get the return values and concatenate them together and then
589 ** parse out the particular arguments that you are looking for.
590 */
591 X_progress := '200';
592
593 IF (rc_sync = 0) THEN
594
595 rc := fnd_transaction.get_values (
596 r_val1, r_val2, r_val3, r_val4, r_val5,
597 r_val6, r_val7, r_val8, r_val9, r_val10,
598 r_val11, r_val12, r_val13, r_val14, r_val15,
599 r_val16, r_val17, r_val18, r_val19, r_val20);
600
601 /* dbms_output.put_line ('Return From Get Vals = ' || to_char(rc)); */
602
603 parm_location := INSTR(r_val1, 'ERROR-FAILURE');
604
605 IF (parm_location > 0) THEN
606
607 -- Bug 642346, lpo, 03/25/98
608 -- Added carriage returns to error_msg and use substr() to
609 -- make sure that we don't exceed the string size.
610
611 error_msg := substr(r_val2 || '
612 ' ||
613 r_val3 || '
614 ' ||
615 r_val4 || '
616 ' ||
617 r_val5 || '
618 ' ||
619 r_val6 || '
620 ' ||
621 r_val7 || '
622 ' ||
623 r_val8 || '
624 ' ||
625 r_val9 || '
626 ' ||
627 r_val10 || '
628 ' ||
629 r_val11 || '
630 ' ||
631 r_val12 || '
632 ' ||
633 r_val13 || '
634 ' ||
635 r_val14 || '
636 ' ||
637 r_val15 || '
638 ' ||
639 r_val16 || '
640 ' ||
641 r_val17 || '
642 ' ||
643 r_val18 || '
644 ' ||
645 r_val19 || '
646 ' ||
647 r_val20, 1, 2000);
648
649 -- End of fix. Bug 642346, lpo, 3/25/98
650
651 Info_Request := NULL;
652 Document_Status := NULL;
653 Online_Report_Id := NULL;
654 Return_Code := NULL;
655
656 l_function_return_value := 3;
657 RAISE l_return_exc;
658
659 END IF;
660
661 /*
662 ** r_val1 will contain a return value of either 'INFO_REQUEST_FIELD'
663 ** or 'STATUS_FIELD'. Look for these strings in the return codes and
664 ** act on these accordingly.
665 ** Check to see if the info_request_parameter has been passed
666 ** back. If so then you know that this should be the only
667 ** return parameter. You can look at poxdmaction() in src/xit/poxdm.lpc
668 ** to see how the parameters are passed back to this process using
669 ** afptpput().
670 */
671 X_progress := '300';
672
673 parm_location := INSTR(r_val1, 'INFO_REQUEST_FIELD=');
674
675 IF (parm_location > 0) THEN
676
677 /*
678 ** If the parm_location is greater than 0 then the field was found
679 ** so go ahead and populate the info_request field
680 */
681 parm_location := INSTR(r_val1, '=');
682
683 X_info_request := SUBSTR(r_val1, parm_location + 1);
684
685 END IF;
686
687 /*
688 ** Now check for the status field. If the status field is populated
689 ** in the parameter then look for the other parameters of
690 ** ONLINE_REPORT_ID and RETURN_CODE
691 */
692 parm_location := INSTR(r_val1, 'STATUS_FIELD=');
693
694 IF (parm_location > 0) THEN
695
696 parm_location := INSTR(r_val1, '=');
697
698 X_document_status := SUBSTR(r_val1, parm_location + 1);
699
700 /*
701 ** Now check for the online report id
702 */
703 parm_location := INSTR(r_val2, 'ONLINE_REPORT_ID=');
704
705 IF (parm_location > 0) THEN
706
707 parm_location := INSTR(r_val2, '=');
708
709 x_online_report_id := TO_NUMBER(SUBSTR(r_val2, parm_location + 1));
710
711 END IF;
712
713 /*
714 ** Now check for the return code
715 */
716 parm_location := INSTR(r_val3, 'RETURN_CODE=');
717
718 IF (parm_location > 0) THEN
719
720 parm_location := INSTR(r_val3, '=');
721
722 X_return_code := SUBSTR(r_val3, parm_location + 1);
723
724 END IF;
725
726 END IF;
727
728 /*
729 ** Now print out all the values you've found
730
731 dbms_output.put_line ('--------------------------------------------');
732
733 dbms_output.put_line ('Info Request = ' || X_info_request);
734 dbms_output.put_line ('Status = ' || X_document_status);
735 dbms_output.put_line ('Report Id = ' || to_char(x_online_report_id));
736 dbms_output.put_line ('Return Code = ' || X_return_code);
737
738 dbms_output.put_line ('--------------------------------------------');
739 */
740
741 ELSE --rc_sync <> 0
742
743 /* dbms_output.put_line ('ERROR : Process exited with status: ' ||
744 to_char(rc_sync));
745 */
746 /*
747 ** Process timed out
748 */
749 IF (rc_sync = 1) THEN
750
751 Error_Msg := fnd_message.get_string('FND', 'TM-TIMEOUT');
752
753 ELSIF (rc_sync = 2) THEN
754
755 Error_Msg := fnd_message.get_string('PO', 'PO_APP_NO_MANAGER');
756
757 END IF;
758
759 /*
760 dbms_output.put_line (r_val1);
761 dbms_output.put_line (r_val2);
762 dbms_output.put_line (r_val3);
763 dbms_output.put_line (r_val4);
764 dbms_output.put_line (r_val5);
765 dbms_output.put_line (r_val6);
766 dbms_output.put_line (r_val7);
767 dbms_output.put_line (r_val8);
768 dbms_output.put_line (r_val9);
769 dbms_output.put_line (r_val10);
770 dbms_output.put_line (r_val11);
771 dbms_output.put_line (r_val12);
772 dbms_output.put_line (r_val13);
773 dbms_output.put_line (r_val14);
774 dbms_output.put_line (r_val15);
775 dbms_output.put_line (r_val16);
776 dbms_output.put_line (r_val17);
777 dbms_output.put_line (r_val18);
778 dbms_output.put_line (r_val19);
779 dbms_output.put_line (r_val20);
780 */
781
782 END IF; -- if rc_sync = 0
783
784 -- X_progress := '400';
785
786 /*
787 ** Set the return values and return control to the caller
788 */
789 Info_Request := X_info_request;
790 Document_Status := X_document_status;
791 Online_Report_Id := X_online_report_id;
792 Return_Code := X_return_code;
793
794 l_function_return_value := rc_sync;
795
796 END IF; -- IGC Year-End actions vs. other doc manager actions
797
798 -- temp
799 if(error_msg is null) then
800 error_msg := r_val1 || ' ' || r_val2 || ' ' || r_val3;
801 end if;
802
803 RETURN(l_function_return_value);
804
805 EXCEPTION
806
807 WHEN l_return_exc THEN
808 RETURN(l_function_return_value);
809
810 WHEN OTHERS THEN
811 po_message_s.sql_error('PO_DOCUMENT_ACTIONS_SV.PO_REQUEST_ACTION',
812 X_progress, sqlcode);
813 RAISE;
814
815
816 END PO_REQUEST_ACTION;
817
818
819
820 FUNCTION PO_HOLD_DOCUMENT (
821 Po_Header_Id IN NUMBER ,
822 Po_Release_Id IN NUMBER ,
823 Error_Msg OUT NOCOPY VARCHAR2) RETURN NUMBER
824 IS
825 rc NUMBER := 0;
826
827 X_return_code varchar2(25) := NULL;
828 X_Document_Type VARCHAR2(25) := NULL;
829 X_Document_Id NUMBER := 0;
830 X_Document_Subtype VARCHAR2(25) := NULL;
831 X_progress varchar2(4) := '000';
832
833 -- <Doc Manager Rewrite 11.5.11>
834 l_ret_sts VARCHAR2(1);
835 l_exc_msg VARCHAR2(2000);
836
837 BEGIN
838
839 /*
840 ** This function is used by Oracle Quality to set the document status of
841 ** a po or a release to Unapproved and On Hold. This function will call
842 ** the document approval manager to perform this action. Therefore the
843 ** the Document Manager
844 */
845
846 /*
847 ** Assumption: If po_release_id is populated with a non 0 and non null
848 ** value then the document must be a release. Otherwise it is a standard
849 ** po. This function does not currently support internal reqs
850 */
851
852 IF (NVL(Po_Release_Id,0) <> 0) THEN
853
854 X_Document_Type := 'RELEASE';
855 X_Document_Subtype := 'RELEASE';
856 X_Document_Id := Po_Release_Id;
857
858 ELSE
859
860 /*
861 ** Assume cannot be an agreement since you can't
862 ** receive against an agreement header (must use a release)
863 */
864
865 X_Document_Id := Po_Header_Id;
866 X_Document_Type := 'PO';
867 X_Document_Subtype := 'STANDARD';
868
869 END IF;
870
871 /*
872 ** Call the document manager with the HOLD_DOCUMENT action code
873 */
874
875 -- <Doc Manager Rewrite 11.5.11 Start>
876 -- Use po_document_action_pvt.do_hold instead po_request_action
877
878 PO_DOCUMENT_ACTION_PVT.do_hold(
879 p_document_id => X_Document_Id
880 , p_document_type => X_Document_Type
881 , p_document_subtype => X_Document_Subtype
882 , p_reason => NULL
883 , x_return_status => l_ret_sts
884 , x_return_code => X_return_code
885 , x_exception_msg => l_exc_msg
886 );
887
888 IF ((l_ret_sts <> 'S') OR (X_return_code = 'STATE_FAILED'))
889 THEN
890 rc := 3;
891 Error_Msg := substr(l_exc_msg, 1, 240);
892 ELSE
893 rc := 0;
894 Error_Msg := NULL;
895 END IF;
896
897 -- <Doc Manager Rewrite 11.5.11 End>
898
899 RETURN (rc);
900
901 EXCEPTION
902 WHEN OTHERS THEN
903 po_message_s.sql_error('PO_DOCUMENT_ACTIONS_SV.HOLD_DOCUMENT',
904 X_progress, sqlcode);
905 RAISE;
906
907 END PO_HOLD_DOCUMENT;
908
909 END PO_DOCUMENT_ACTIONS_SV;
910
[Home] [Help]