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