[Home] [Help]
PACKAGE BODY: APPS.PO_MASS_CLOSE_PO_PVT
Source
1 PACKAGE BODY PO_Mass_Close_PO_PVT AS
2 /* $Header: PO_Mass_Close_PO_PVT.plb 120.12.12020000.3 2013/02/10 13:42:53 vegajula ship $*/
3
4 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
5 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
6 g_pkg_name CONSTANT VARCHAR2(100) := 'PO_Mass_Close_PO_PVT';
7 g_log_head CONSTANT VARCHAR2(1000) := 'po.plsql.' || g_pkg_name || '.';
8
9 TYPE g_close_po IS REF CURSOR;
10
11 TYPE g_close_rel IS REF CURSOR;
12
13 --------------------------------------------------------------------------------------------------
14 -- Start of Comments
15
16 -- API Name : po_close_documents.
17 -- Type : Private
18 -- Pre-reqs : None
19 -- Function : Calls the procedure po_actions.close_po to close the PO's and releases.
20
21 -- Parameters :
22
23 -- IN : p_document_type Type of the document(STANDARD,BLANKET.CONTRACT,PLANNED).
24 -- p_document_no_from Document number from.
25 -- p_document_no_to Document number to.
26 -- p_date_from Date from.
27 -- p_date_to Date to.
28 -- p_supplier_id Supplier id.
29 -- p_commit_interval Commit interval.
30
31 -- OUT : p_msg_data Actual message in encoded format.
32 -- p_msg_count Holds the number of messages in the API list.
33 -- p_return_status Return status of the API (Includes 'S','E','U').
34
35 -- End of Comments
36 --------------------------------------------------------------------------------------------------
37
38 PROCEDURE po_close_documents(p_document_type IN VARCHAR2,
39 p_document_no_from IN VARCHAR2,
40 p_document_no_to IN VARCHAR2,
41 p_date_from IN VARCHAR2,
42 p_date_to IN VARCHAR2,
43 p_supplier_id IN NUMBER,
44 p_commit_interval IN NUMBER,
45 p_msg_data OUT NOCOPY VARCHAR2,
46 p_msg_count OUT NOCOPY NUMBER,
47 p_return_status OUT NOCOPY VARCHAR2) IS
48
49 close_po g_close_po;
50 close_rel g_close_rel;
51 stmt_po VARCHAR2(6000);
52 stmt_rel VARCHAR2(4000);
53 stmt_pa VARCHAR2(4000); --Bug 10371162
54 po_num_type VARCHAR2(100);
55 l_po_num po_headers.clm_document_number%TYPE;
56 l_rel_num po_releases.release_num%TYPE;
57 l_doc_id po_headers.po_header_id%TYPE;
58 l_type_code po_headers.type_lookup_code%TYPE;
59 l_doc_subtype po_document_types.document_subtype%TYPE;
60 l_release_id po_releases.po_release_id%TYPE;
61 l_doc_type po_document_types.type_name%TYPE;
62 l_org_id NUMBER;
63 result BOOLEAN;
64 l_return_code VARCHAR2(100);
65 l_commit_count NUMBER;
66 l_progress VARCHAR2(3) := '000';
67 l_log_head CONSTANT VARCHAR2(1000) := g_log_head||'po_close_documents';
68
69 BEGIN
70
71 g_document_type := p_document_type;
72 g_document_no_from := p_document_no_from;
73 g_document_no_to := p_document_no_to;
74 g_date_from := p_date_from;
75 g_date_to := p_date_to;
76 g_supplier_id := p_supplier_id;
77
78 IF g_debug_stmt THEN
79
80 PO_DEBUG.debug_begin(l_log_head);
81 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_type',p_document_type );
82 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_no_from',p_document_no_from );
83 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_type',p_document_no_to ); -- <BUG 7193855>
84 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_from',p_date_from);
85 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_to',p_date_to);
86 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_id',p_supplier_id);
87 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit_interval',p_commit_interval);
88
89 END IF;
90
91 SAVEPOINT Close_SP;
92
93 l_progress := '001';
94
95 BEGIN
96
97 SAVEPOINT Close_REC_SP;
98
99 SELECT org_id
100 INTO l_org_id
101 FROM po_system_parameters;
102
103 SELECT hou.name
104 INTO p_org_name
105 FROM hr_all_organization_units hou,
106 hr_all_organization_units_tl hout
107 WHERE hou.organization_id = hout.organization_id
108 AND hout.LANGUAGE = UserEnv('LANG')
109 AND hou.organization_id = l_org_id;
110
111 IF (p_supplier_id IS NOT NULL) then
112
113 SELECT vendor_name
114 INTO p_supplier_name
115 FROM po_vendors
116 WHERE vendor_id = p_supplier_id;
117
118 END IF;
119
120 IF g_debug_stmt THEN
121
122 PO_DEBUG.debug_var(l_log_head,l_progress,'p_org_name',p_org_name );
123 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_name',p_supplier_name );
124
125 END IF;
126
127 Print_Output(p_org_name,
128 p_document_type,
129 p_document_no_from,
130 p_document_no_to,
131 p_date_from,
132 p_date_to,
133 p_supplier_name,
134 p_msg_data,
135 p_msg_count,
136 p_return_status);
137
138 EXCEPTION
139
140 WHEN OTHERS THEN
141 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
142
143 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
144
145 END IF;
146
147 ROLLBACK TO Close_REC_SP;
148
149 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
150
151 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
152
153 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
154
155 END IF;
156
157 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
158
159 END;
160
161 -- <BUG 7193855 Changed the dynamic sql to return correct results for all the document types>
162
163 --Bug 14004642, clean up conditions
164 IF p_document_type IN ('STANDARD','PLANNED') THEN
165 stmt_po := 'SELECT poh.segment1 PO_Number,
166 poh.po_header_id,
167 pdt.document_type_code,
168 pdt.document_subtype,
169 pdt.type_name
170 FROM po_headers poh,
171 po_document_types_vl pdt,
172 po_distributions pod,
173 po_line_locations pll
174 WHERE poh.type_lookup_code = pdt.document_subtype
175 AND poh.po_header_id = pod.po_header_id
176 AND poh.po_header_id = pll.po_header_id
177 AND pll.line_location_id = pod.line_location_id
178 AND Nvl(pll.closed_code,''OPEN'')
179 IN (''CLOSED FOR INVOICE'',
180 ''CLOSED FOR RECEIVING'',''OPEN'')
181 AND pll.po_release_id IS NULL
182 AND Nvl(pdt.document_type_code,''PO'') = ''PO''
183 AND Nvl(poh.authorization_status, ''INCOMPLETE'')
184 IN (''APPROVED'',''REQUIRES REAPPROVAL'')
185 AND Nvl(poh.closed_code,''OPEN'')
186 NOT IN (''CLOSED'',''FINALLY CLOSED'')
187 AND Nvl(poh.cancel_flag,''N'') = ''N''';
188
189 ELSIF p_document_type IN ('BLANKET','CONTRACT') THEN
190 stmt_po := 'SELECT poh.segment1 PO_Number,
191 poh.po_header_id,
192 pdt.document_type_code,
193 pdt.document_subtype,
194 pdt.type_name
195 FROM po_headers poh,
196 po_document_types_vl pdt
197 WHERE poh.type_lookup_code = pdt.document_subtype
198 AND Nvl(pdt.document_type_code,''PA'') = ''PA''
199 AND Nvl(poh.authorization_status, ''INCOMPLETE'')
200 IN (''APPROVED'',''REQUIRES REAPPROVAL'')
201 AND Nvl(poh.closed_code,''OPEN'')
202 NOT IN (''CLOSED'',''FINALLY CLOSED'')
203 AND Nvl(poh.cancel_flag,''N'') = ''N''';
204 ELSE
205 stmt_po := 'SELECT poh.segment1 PO_Number,
206 poh.po_header_id,
207 pdt.document_type_code,
208 pdt.document_subtype,
209 pdt.type_name
210 FROM po_headers poh,
211 po_document_types_vl pdt,
212 po_distributions pod,
213 po_line_locations pll
214 WHERE poh.type_lookup_code = pdt.document_subtype
215 AND poh.po_header_id = pod.po_header_id
216 AND poh.po_header_id = pll.po_header_id
217 AND pll.line_location_id = pod.line_location_id
218 AND Nvl(pll.closed_code,''OPEN'')
219 IN (''CLOSED FOR INVOICE'',
220 ''CLOSED FOR RECEIVING'',''OPEN'')
221 AND pll.po_release_id IS NULL
222 AND Nvl(pdt.document_type_code,''PO'') = ''PO''
223 AND Nvl(poh.authorization_status,''INCOMPLETE'')
224 IN (''APPROVED'',''REQUIRES REAPPROVAL'')
225 AND Nvl(poh.closed_code,''OPEN'')
226 NOT IN (''CLOSED'',''FINALLY CLOSED'')
227 AND Nvl(poh.cancel_flag,''N'') = ''N'' ';
228
229 stmt_pa := ' SELECT poh.segment1 PO_Number,
230 poh.po_header_id,
231 pdt.document_type_code,
232 pdt.document_subtype,
233 pdt.type_name FROM po_headers poh,
234 po_document_types_vl pdt
235 WHERE poh.type_lookup_code = pdt.document_subtype
236 AND Nvl(pdt.document_type_code,''PA'') = ''PA''
237 AND Nvl(poh.authorization_status, ''INCOMPLETE'')
238 IN (''APPROVED'',''REQUIRES REAPPROVAL'')
239 AND Nvl(poh.closed_code,''OPEN'')
240 NOT IN (''CLOSED'',''FINALLY CLOSED'')
241 AND Nvl(poh.cancel_flag,''N'') = ''N''';
242 --End Bug 14004642
243 END IF;
244
245 IF p_document_type IS NOT NULL AND p_document_type <> 'ALL' THEN -- <BUG 6988269>
246
247 stmt_po := stmt_po || ' AND poh.type_lookup_code = PO_Mass_Close_PO_PVT.get_document_type';
248 END IF;
249
250 IF ( po_num_type = 'NUMERIC' ) THEN
251
252 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
253
254 stmt_po := stmt_po || ' AND 1 = 1 ';
255
256 IF(stmt_pa IS NOT NULL ) Then
257 stmt_pa := stmt_pa || ' AND 1 = 1 ';
258 END IF;
259
260 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
261
262 stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) >= to_number(PO_Mass_Close_PO_PVT.get_document_no_from)';
263
264 IF(stmt_pa IS NOT NULL ) Then
265 stmt_pa := stmt_pa || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) >= to_number(PO_Mass_Close_PO_PVT.get_document_no_from)';
266 END IF;
267
268
269 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
270
271 stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) <= to_number(PO_Mass_Close_PO_PVT.g_document_no_to)';
272
273 IF(stmt_pa IS NOT NULL ) Then
274 stmt_pa := stmt_pa || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) <= to_number(PO_Mass_Close_PO_PVT.g_document_no_to)';
275 END IF;
276 ELSE
277
278 stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL )
279
280 BETWEEN to_number(PO_Mass_Close_PO_PVT.get_document_no_from) AND to_number(PO_Mass_Close_PO_PVT.get_document_no_to)';
281
282 IF(stmt_pa IS NOT NULL ) Then
283 stmt_pa := stmt_pa || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL )
284
285 BETWEEN to_number(PO_Mass_Close_PO_PVT.get_document_no_from) AND to_number(PO_Mass_Close_PO_PVT.get_document_no_to)';
286 END IF;
287 END IF;
288
289 ELSE
290
291 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
292
293 stmt_po := stmt_po || ' AND 1 = 1 ';
294
295 IF(stmt_pa IS NOT NULL ) Then
296 stmt_pa := stmt_pa || ' AND 1 = 1 ';
297 END IF;
298
299 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
300
301 stmt_po := stmt_po || ' AND POH.SEGMENT1 >= PO_Mass_Close_PO_PVT.get_document_no_from';
302 IF(stmt_pa IS NOT NULL ) Then
303 stmt_pa := stmt_pa || ' AND POH.SEGMENT1 >= PO_Mass_Close_PO_PVT.get_document_no_from';
304 END IF;
305
306 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
307
308 stmt_po := stmt_po || ' AND POH.SEGMENT1 <= PO_Mass_Close_PO_PVT.get_document_no_to';
309 IF(stmt_pa IS NOT NULL ) Then
310 stmt_pa := stmt_pa || ' AND POH.SEGMENT1 <= PO_Mass_Close_PO_PVT.get_document_no_to';
311 END IF;
312
313 ELSE
314
315 stmt_po := stmt_po || ' AND POH.SEGMENT1 BETWEEN PO_Mass_Close_PO_PVT.get_document_no_from AND PO_Mass_Close_PO_PVT.get_document_no_to';
316 IF(stmt_pa IS NOT NULL ) Then
317 stmt_pa := stmt_pa || ' AND POH.SEGMENT1 BETWEEN PO_Mass_Close_PO_PVT.get_document_no_from AND PO_Mass_Close_PO_PVT.get_document_no_to';
318 END IF;
319
320 END IF;
321
322 END IF; /* po_num_type = 'NUMERIC' */
323
324 /* Bug 6899092 Added Trunc condition in validating the date ranges */
325
326 IF p_date_from IS NULL AND p_date_to IS NULL THEN
327
328 stmt_po := stmt_po || ' AND 1 = 1 ';
329
330 IF(stmt_pa IS NOT NULL ) Then
331 stmt_pa := stmt_pa || ' AND 1 = 1 ';
332 END IF;
333
334 ELSIF p_date_from IS NOT NULL AND p_date_to IS NULL THEN
335
336 stmt_po := stmt_po || ' AND POH.creation_date >= Trunc(PO_Mass_Close_PO_PVT.get_date_from)';
337
338 IF(stmt_pa IS NOT NULL ) Then
339 stmt_pa := stmt_pa || ' AND POH.creation_date >= Trunc(PO_Mass_Close_PO_PVT.get_date_from)';
340 END IF;
341
342 ELSIF p_date_from IS NULL AND p_date_to IS NOT NULL THEN
343
344 stmt_po := stmt_po || ' AND POH.creation_date <= Trunc(PO_Mass_Close_PO_PVT.get_date_to)';
345
346 IF(stmt_pa IS NOT NULL ) Then
347 stmt_pa := stmt_pa || ' AND POH.creation_date <= Trunc(PO_Mass_Close_PO_PVT.get_date_to)';
348 END IF;
349
350 ELSE
351 stmt_po := stmt_po || ' AND POH.creation_date >= Trunc(PO_Mass_Close_PO_PVT.get_date_from)
352 AND POH.creation_date < Trunc(PO_Mass_Close_PO_PVT.get_date_to)+1';
353
354 IF(stmt_pa IS NOT NULL ) Then
355 stmt_pa := stmt_pa || ' AND POH.creation_date >= Trunc(PO_Mass_Close_PO_PVT.get_date_from)
356 AND POH.creation_date < Trunc(PO_Mass_Close_PO_PVT.get_date_to)+1';
357
358 END IF;
359
360 END IF;
361
362 IF p_supplier_id IS NOT NULL THEN
363
364 stmt_po := stmt_po || ' AND POH.vendor_id = PO_Mass_Close_PO_PVT.get_supplier_id';
365
366 IF(stmt_pa IS NOT NULL ) Then
367 stmt_pa := stmt_pa || ' AND POH.vendor_id = PO_Mass_Close_PO_PVT.get_supplier_id';
368 END IF;
369
370 END IF;
371
372 IF(stmt_pa IS NOT NULL ) Then
373 stmt_po := '( ' || stmt_po || ' UNION ' || stmt_pa || ' ) ' ;
374 END IF;
375
376 stmt_po := stmt_po || ' ORDER BY PO_Number';
377
378 IF (p_document_type IS NULL OR p_document_type IN ('STANDARD','BLANKET','PLANNED','CONTRACT','ALL')) THEN -- <BUG 6988269>
379
380 OPEN close_po for stmt_po;
381
382 LOOP
383
384 FETCH close_po INTO l_po_num,
385 l_doc_id,
386 l_type_code,
387 l_doc_subtype,
388 l_doc_type;
389
390 EXIT WHEN close_po%NOTFOUND;
391
392 BEGIN
393
394 SAVEPOINT Close_PO_SP;
395
396 l_progress := '002';
397
398 IF g_debug_stmt THEN
399
400 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_num',l_po_num );
401 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_id',l_doc_id );
402 PO_DEBUG.debug_var(l_log_head,l_progress,'l_type_code',l_type_code );
403 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_subtype',l_doc_subtype );
404
405 END IF;
406
407 l_progress := '003';
408
409 -- Call the Close_PO procedure to perform the action of close on PO's fetched
410
411 result := po_actions.close_po(p_docid => l_doc_id,
412 p_doctyp => l_type_code,
413 p_docsubtyp => l_doc_subtype,
414 p_lineid => NULL,
415 p_shipid => NULL,
416 p_action => 'CLOSE',
417 p_reason => NULL,
418 p_calling_mode => 'PO',
419 p_conc_flag => 'N',
420 p_return_code => l_return_code,
421 p_auto_close => 'N',
422 p_action_date => SYSDATE,
423 p_origin_doc_id => NULL);
424
425
426 IF g_debug_stmt THEN
427
428 PO_DEBUG.debug_var(l_log_head,l_progress,'result',result );
429 PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_code',l_return_code );
430
431 END IF;
432
433 l_progress := '004';
434
435 l_commit_count := l_commit_count + 1;
436
437 IF l_commit_count = p_commit_interval THEN
438
439 COMMIT;
440 l_commit_count := 0;
441
442 END IF;
443
444 l_progress := '005';
445
446 IF g_debug_stmt THEN
447
448 PO_DEBUG.debug_var(l_log_head,l_progress,'l_commit_count',l_commit_count );
449
450 END IF;
451
452 IF (l_return_code = 'STATE_FAILED' ) THEN
453
454 IF g_debug_stmt THEN
455 PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_code',l_return_code );
456 END IF;
457
458 ELSE
459 fnd_file.put_line(fnd_file.output, rpad(l_po_num,26) || l_doc_type);
460 END IF;
461
462 EXCEPTION
463
464 WHEN OTHERS THEN
465
466 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
467
468 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
469
470 END IF;
471
472 ROLLBACK TO Close_PO_SP;
473
474 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
475
476 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
477
478 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
479
480 END IF;
481
482 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
483
484 END;
485
486 END LOOP;
487
488 CLOSE close_po;
489
490 END IF; -- <End of p_document_type>
491
492 l_progress := '006';
493
494 --Bug 13877857, move pod, pll to subquery to suppress the duplicate
495 --releases in output.
496 --Bug 14004642, clean up conditions
497 stmt_rel := 'SELECT poh.segment1,
498 por.po_release_id,
499 por.release_num,
500 pdt.document_type_code,
501 pdt.document_subtype,
502 pdt.type_name
503 FROM po_releases por,
504 po_headers poh,
505 po_document_types_vl pdt
506 WHERE por.po_header_id = poh.po_header_id
507 AND pdt.document_type_code = ''RELEASE''
508 AND pdt.document_subtype = por.release_type
509 AND Nvl(por.authorization_status,''INCOMPLETE'')
510 IN (''APPROVED'',''REQUIRES REAPPROVAL'')
511 AND Nvl(por.closed_code,''OPEN'')
512 NOT IN (''CLOSED'',''FINALLY CLOSED'')
513 AND Nvl(por.cancel_flag,''N'') = ''N''
514 AND EXISTS
515 (SELECT 1
516 FROM po_distributions_all pod,
517 po_line_locations_all pll
518 WHERE por.po_header_id = pod.po_header_id
519 AND por.po_header_id = pll.po_header_id
520 AND pll.line_location_id = pod.line_location_id
521 AND pod.po_release_id IS NOT NULL
522 AND Nvl(pll.closed_code,''OPEN'')
523 IN (''CLOSED FOR INVOICE'',
524 ''CLOSED FOR RECEIVING'',''OPEN'')
525 ) '; --End bug 14004642
526 --end bug 13877857
527
528 IF p_document_type IS NOT NULL AND p_document_type <> 'ALL' THEN -- <BUG 6988269>
529
530 stmt_rel := stmt_rel || 'AND ((PO_Mass_Close_PO_PVT.get_document_type = ''PLANNED'' and por.release_type = ''SCHEDULED'')
531
532 OR (por.release_type = Nvl(PO_Mass_Close_PO_PVT.get_document_type,por.release_type)))';
533
534 END IF;
535
536 IF ( po_num_type = 'NUMERIC' ) THEN
537
538 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
539
540 stmt_rel := stmt_rel || ' AND 1 = 1 ';
541
542 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
543
544 stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL ) >= to_number(PO_Mass_Close_PO_PVT.get_document_no_from)';
545
546 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
547
548 stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL ) <= to_number(PO_Mass_Close_PO_PVT.get_document_no_to)';
549
550 ELSE
551
552 stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL )
553
554 BETWEEN to_number(PO_Mass_Close_PO_PVT.get_document_no_from) AND to_number(PO_Mass_Close_PO_PVT.get_document_no_to)';
555
556 END IF;
557
558 ELSE
559
560 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
561
562 stmt_rel := stmt_rel || ' AND 1 = 1 ';
563
564 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
565
566 stmt_rel := stmt_rel || ' AND POH.Clm_Document_number >= PO_Mass_Close_PO_PVT.get_document_no_from';
567
568 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
569
570 stmt_rel := stmt_rel || ' AND POH.Clm_Document_number <= PO_Mass_Close_PO_PVT.get_document_no_to';
571
572 ELSE
573
574 stmt_rel := stmt_rel || ' AND POH.Clm_Document_number BETWEEN PO_Mass_Close_PO_PVT.get_document_no_from AND PO_Mass_Close_PO_PVT.get_document_no_to'; -- <BUG 7193855>
575
576 END IF;
577
578 END IF; /* po_num_type = 'NUMERIC' */
579
580 /* Bug 6899092 Added Trunc condition in validating the date ranges */
581
582 IF p_date_from IS NULL AND p_date_to IS NULL THEN
583
584 stmt_rel := stmt_rel || ' AND 1 = 1 ';
585
586 ELSIF p_date_from IS NOT NULL AND p_date_to IS NULL THEN
587
588 stmt_rel := stmt_rel || ' AND POR.creation_date >= Trunc(PO_Mass_Close_PO_PVT.get_date_from)';
589
590 ELSIF p_date_from IS NULL AND p_date_to IS NOT NULL THEN
591
592 stmt_rel := stmt_rel || ' AND POR.creation_date <= Trunc(PO_Mass_Close_PO_PVT.get_date_to)';
593
594 ELSE
595
596 stmt_rel := stmt_rel || ' AND POR.creation_date >= Trunc(PO_Mass_Close_PO_PVT.get_date_from)
597 AND POR.creation_date < Trunc(PO_Mass_Close_PO_PVT.get_date_to)+1';
598
599 END IF;
600
601 IF p_supplier_id IS NOT NULL THEN
602
603 stmt_rel := stmt_rel || ' AND POH.vendor_id = PO_Mass_Close_PO_PVT.get_supplier_id';
604
605 END IF;
606
607 stmt_rel := stmt_rel || ' ORDER BY poh.segment1,por.release_num';
608
609
610 IF (p_document_type IS NULL OR p_document_type IN ('BLANKET','PLANNED', 'ALL')) THEN -- <BUG 6988269 Added 'ALL' condition>
611
612
613 OPEN close_rel for stmt_rel;
614 LOOP
615
616 FETCH close_rel INTO l_po_num,
617 l_release_id,
618 l_rel_num,
619 l_type_code,
620 l_doc_subtype,
621 l_doc_type;
622
623 EXIT WHEN close_rel%NOTFOUND;
624
625 BEGIN
626
627 SAVEPOINT Close_REL_SP;
628
629 l_progress := '007';
630
631 IF g_debug_stmt THEN
632
633 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_num',l_po_num );
634 PO_DEBUG.debug_var(l_log_head,l_progress,'l_release_id',l_release_id );
635 PO_DEBUG.debug_var(l_log_head,l_progress,'l_type_code',l_type_code );
636
637 END IF;
638
639 l_progress := '008';
640
641 -- Call the Close_PO procedure to perform the action of close on Releases fetched
642
643 result := po_actions.close_po(p_docid => l_release_id,
644 p_doctyp => l_type_code,
645 p_docsubtyp => l_doc_subtype,
646 p_lineid => NULL,
647 p_shipid => NULL,
648 p_action => 'CLOSE',
649 p_reason => NULL,
650 p_calling_mode => 'PO',
651 p_conc_flag => 'N',
652 p_return_code => l_return_code,
653 p_auto_close => 'N',
654 p_action_date => SYSDATE,
655 p_origin_doc_id => NULL);
656
657 IF g_debug_stmt THEN
658
659 PO_DEBUG.debug_var(l_log_head,l_progress,'result',result );
660 PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_code',l_return_code );
661
662 END IF;
663
664
665 l_progress := '009';
666
667 l_commit_count := l_commit_count + 1;
668
669 IF l_commit_count = p_commit_interval THEN
670
671 COMMIT;
672 l_commit_count := 0;
673
674 END IF;
675
676 IF g_debug_stmt THEN
677
678 PO_DEBUG.debug_var(l_log_head,l_progress,'l_commit_count',l_commit_count );
679
680 END IF;
681
682 EXCEPTION
683
684 WHEN OTHERS THEN
685
686 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
687
688 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
689
690 END IF;
691
692 ROLLBACK TO Close_REL_SP;
693
694 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
695
696 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
697
698 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
699
700 END IF;
701
702 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
703
704 END;
705
706 fnd_file.put_line(fnd_file.output, rpad(l_po_num || '-' || l_rel_num,26) || l_doc_type);
707
708
709
710 END LOOP;
711
712 CLOSE close_rel;
713
714 END IF; -- <End of p_document_type>
715
716 EXCEPTION
717
718 WHEN OTHERS THEN
719
720 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
721
722 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
723
724 END IF;
725
726 ROLLBACK TO Close_SP;
727
728 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
729
730 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
731
732 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
733
734 END IF;
735
736 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
737
738 END po_close_documents;
739
740 --------------------------------------------------------------------------------------------------
741 -- Start of Comments
742
743 -- API Name : Print_Output
744 -- Type : Private
745 -- Pre-reqs : None
746 -- Function : Prints the header and body of the output file showing the documents and
747 -- document types which are closed.
748
749 -- Parameters :
750
751 -- IN : p_org_name Operating unit name.
752 -- p_document_type Type of the document(STANDARD,BLANKET.CONTRACT,PLANNED).
753 -- p_document_no_from Document number from.
754 -- p_document_no_to Document number to.
755 -- p_date_from Date from.
756 -- p_date_to Date to.
757 -- p_supplier_name Supplier name.
758
759 -- OUT : p_msg_data Actual message in encoded format.
760 -- p_msg_count Holds the number of messages in the API list.
761 -- p_return_status Return status of the API (Includes 'S','E','U').
762
763 -- End of Comments
764 --------------------------------------------------------------------------------------------------
765
766 PROCEDURE Print_Output(p_org_name IN VARCHAR2,
767 p_document_type IN VARCHAR2,
768 p_document_no_from IN VARCHAR2,
769 p_document_no_to IN VARCHAR2,
770 p_date_from IN DATE,
771 p_date_to IN DATE,
772 p_supplier_name IN VARCHAR2,
773 p_msg_data OUT NOCOPY VARCHAR2,
774 p_msg_count OUT NOCOPY NUMBER,
775 p_return_status OUT NOCOPY VARCHAR2) IS
776
777 l_msg1 VARCHAR2(240);
778 l_msg2 VARCHAR2(240);
779 l_msg3 VARCHAR2(240);
780 l_msg4 VARCHAR2(240);
781 l_msg5 VARCHAR2(240);
782 l_msg6 VARCHAR2(240);
783 l_msg7 VARCHAR2(240);
784 l_msg8 VARCHAR2(240);
785 l_msg9 VARCHAR2(240);
786 l_msg10 VARCHAR2(240);
787 l_msg11 VARCHAR2(240);
788 l_msg12 VARCHAR2(240);
789 l_progress VARCHAR2(3);
790 l_log_head CONSTANT VARCHAR2(1000) := g_log_head||'Print_Output';
791
792 BEGIN
793
794 fnd_message.set_name('PO','PO_MUB_MSG_CLOSE_HEADER1');
795 l_msg1 := fnd_message.get;
796
797 fnd_message.set_name('PO','PO_MUB_MSG_DATE');
798 l_msg2 := fnd_message.get;
799
800 fnd_message.set_name('PO','PO_MUB_MSG_OU');
801 l_msg3 := fnd_message.get;
802
803 fnd_message.set_name('PO','PO_MUB_MSG_DOC_SUB_TYPE');
804 l_msg4 := fnd_message.get;
805
806 fnd_message.set_name('PO','PO_MUB_MSG_DOC_NUM_FROM');
807 l_msg5 := fnd_message.get;
808
809 fnd_message.set_name('PO','PO_MUB_MSG_DOC_NUM_TO');
810 l_msg6 := fnd_message.get;
811
812 fnd_message.set_name('PO','PO_MUB_MSG_DATE_FROM');
813 l_msg7 := fnd_message.get;
814
815 fnd_message.set_name('PO','PO_MUB_MSG_DATE_TO');
816 l_msg8 := fnd_message.get;
817
818 fnd_message.set_name('PO','PO_MUB_MSG_SUPPLIER');
819 l_msg9 := fnd_message.get;
820
821 fnd_message.set_name('PO','PO_MUB_MSG_CLOSE_HEADER2');
822 l_msg10 := fnd_message.get;
823
824 fnd_message.set_name('PO','PO_MUB_MSG_DOC_NUM');
825 l_msg11 := fnd_message.get;
826
827 fnd_message.set_name('PO','PO_MUB_MSG_DOC_TYPE');
828 l_msg12 := fnd_message.get;
829
830 SAVEPOINT Print;
831
832 l_progress := '001';
833
834 IF g_debug_stmt THEN
835
836 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_name',p_supplier_name );
837
838 END IF;
839
840 fnd_file.put_line(fnd_file.output, l_msg1);
841 fnd_file.put_line(fnd_file.output, ' ');
842 fnd_file.put_line(fnd_file.output, rpad(l_msg2,21) || ' : ' || sysdate);
843 fnd_file.put_line(fnd_file.output, rpad(l_msg3,21) || ' : ' || p_org_name);
844 l_progress := '002';
845 fnd_file.put_line(fnd_file.output, rpad(l_msg4,21) || ' : ' || p_document_type);
846 fnd_file.put_line(fnd_file.output, rpad(l_msg5,21) || ' : ' || p_document_no_from);
847 fnd_file.put_line(fnd_file.output, rpad(l_msg6,21) || ' : ' || p_document_no_to);
848 fnd_file.put_line(fnd_file.output, rpad(l_msg7,21) || ' : ' || p_date_from);
849 l_progress := '003';
850 fnd_file.put_line(fnd_file.output, rpad(l_msg8,21) || ' : ' || p_date_to);
851 fnd_file.put_line(fnd_file.output, rpad(l_msg9,21) || ' : ' || p_supplier_name);
852 fnd_file.put_line(fnd_file.output, ' ');
853 fnd_file.put_line(fnd_file.output, l_msg10);
854 fnd_file.put_line(fnd_file.output, ' ');
855 l_progress := '004';
856 fnd_file.put_line(fnd_file.output, rpad(l_msg11,26) || l_msg12);
857 fnd_file.put_line(fnd_file.output, rpad('-',60,'-'));
858
859
860 EXCEPTION
861
862 WHEN OTHERS THEN
863
864 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
865
866 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
867
868 END IF;
869
870 ROLLBACK TO Print;
871
872 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
873
874 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
875
876 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
877
878 END IF;
879
880 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
881
882 END Print_Output;
883
884 --------------------------------------------------------------------------------------------------
885
886 -- Functions declared to return the value of the parameters passed in this API.
887
888 --------------------------------------------------------------------------------------------------
889
890 FUNCTION get_document_type RETURN VARCHAR2
891 IS
892 BEGIN
893 RETURN g_document_type;
894 END;
895
896 FUNCTION get_document_no_from RETURN VARCHAR2
897 IS
898 BEGIN
899 RETURN g_document_no_from;
900 END;
901
902 FUNCTION get_document_no_to RETURN VARCHAR2
903 IS
904 BEGIN
905 RETURN g_document_no_to;
906 END;
907
908 FUNCTION get_date_from RETURN DATE
909 IS
910 BEGIN
911 RETURN g_date_from;
912 END;
913
914 FUNCTION get_date_to RETURN DATE
915 IS
916 BEGIN
917 RETURN g_date_to;
918 END;
919
920 FUNCTION get_supplier_id RETURN NUMBER
921 IS
922 BEGIN
923 RETURN g_supplier_id;
924 END;
925
926
927 END PO_Mass_Close_PO_PVT;