[Home] [Help]
PACKAGE BODY: APPS.PO_DOC_STYLE_PVT
Source
1 PACKAGE BODY PO_DOC_STYLE_PVT AS
2 /* $Header: PO_DOC_STYLE_PVT.plb 120.11.12020000.3 2013/02/11 00:04:06 vegajula ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_DOC_STYLE_PVT';
5
6
7 --forward Declarations
8
9 FUNCTION is_line_type_enabled(p_style_id IN NUMBER,
10 p_line_type_id IN NUMBER) RETURN BOOLEAN;
11
12 PROCEDURE check_purchase_basis_enabled(p_style_id IN NUMBER,
13 p_purchase_basis IN VARCHAR2,
14 x_purchase_basis_enabled OUT NOCOPY BOOLEAN,
15 x_related_line_types OUT NOCOPY VARCHAR2);
16
17 FUNCTION is_rate_based_temp_labor(p_line_type_id NUMBER) RETURN BOOLEAN;
18
19 FUNCTION is_amount_based_services_line(p_line_type_id NUMBER) RETURN BOOLEAN;
20 --------------------------------------------------------------------------------
21 --Start of Comments
22 --Name: style_validate_req_lines
23 --Pre-reqs:
24 -- None.
25 --Modifies:
26 -- None.
27 --Locks:
28 -- None.
29 --Function:
30 -- This function would check whether the req lines inserted in the
31 -- PO_SESSION_GT are comapatible with all other in terms of style.
32 --Parameters:
33 --IN:
34 -- p_session_gt_key
35 -- key to identify the records inserted for a given session
36 -- p_po_header_id
37 -- Header id of the Document to which the requisition lines are added to
38 -- would be NULL incase of autocreating a NEW Document
39 -- p_style_id
40 -- Paramter to pass in the group style against which the req lines
41 -- would be validated for style compatilbity
42 --OUT:
43 -- x_style_id
44 -- returns the style compatible for a group of requisition lines
45 -- would be NULL incase of style incompatiblities
46 --
47 -- x_return_status
48 -- FND_API.g_ret_sts_success : indicates a group of requisition lines
49 -- are compatible
50 -- FND_API.g_ret_sts_error : group of requisition lines encountered
51 -- style incomaptibility
52 --End of Comments
53 -------------------------------------------------------------------------------
54
55 PROCEDURE style_validate_req_lines(p_api_version IN NUMBER DEFAULT 1.0,
56 p_init_msg_list IN VARCHAR2 default fnd_api.g_false,
57 x_return_status OUT NOCOPY VARCHAR2,
58 x_msg_count OUT NOCOPY NUMBER,
59 x_msg_data OUT NOCOPY VARCHAR2,
60 p_session_gt_key IN NUMBER,
61 p_po_header_id IN NUMBER,
62 p_po_style_id IN NUMBER DEFAULT NULL,
63 x_style_id OUT NOCOPY NUMBER) IS
64
65 d_progress NUMBER;
66 d_module VARCHAR2(60) := 'po.plsql.PO_DOC_STYLE_PVT.style_validate_req_lines';
67
68 l_api_name CONSTANT VARCHAR2(30) := 'style_validate_req_lines';
69 l_api_version CONSTANT NUMBER := 1.0;
70
71 /*MAPPING FOR PO_SESSION_GT for STYLES */
72 /*
73 * PO_SESSION_GT:
74 * key = key into table
75 * num1 = Requisition line ID
76 * num2 = Source Document ID
77 * num3 = Line Type ID
78 * char1 = Destination type
79 * char2 = Purchase Basis
80 */
81
82 --Fetch the lines from the GT table with source doc refrence
83 CURSOR REQ_LINES_SOURCE_CSR IS
84 SELECT PGT.NUM2 source_doc_id,
85 PGT.NUM3 line_type_id,
86 PGT.CHAR1 destination_type,
87 PGT.CHAR2 purchase_basis,
88 PH.style_id source_doc_style_id
89 FROM PO_SESSION_GT PGT,
90 PO_HEADERS_ALL PH
91 WHERE PGT.KEY = p_session_gt_key
92 AND PGT.NUM2 = PH.PO_HEADER_ID
93 AND PGT.NUM2 IS NOT NULL;
94
95 --Fetch the lines from the GT table without source doc reference
96 CURSOR REQ_LINES_NOSOURCE_CSR IS
97 SELECT PGT.NUM3 line_type_id,
98 PGT.CHAR1 destination_type,
99 PGT.CHAR2 purchase_basis
100 FROM PO_SESSION_GT PGT
101 WHERE PGT.KEY = p_session_gt_key
102 AND PGT.NUM2 IS NULL;
103
104 l_style_id_tbl po_tbl_number;
105 l_group_style_id PO_DOC_STYLE_HEADERS.style_id%TYPE;
106
107 l_source_doc_id PO_REQUISITION_LINES_ALL.blanket_po_header_id%TYPE;
108 l_line_type_id PO_REQUISITION_LINES_ALL.line_type_id%TYPE;
109 l_purchase_basis PO_REQUISITION_LINES_ALL.purchase_basis%TYPE;
110 l_destination_type PO_REQUISITION_LINES_ALL.destination_type_code%TYPE;
111 l_source_doc_style_id PO_DOC_STYLE_HEADERS.style_id%TYPE;
112
113
114 BEGIN
115
116 d_progress := 0;
117 IF (PO_LOG.d_proc) THEN
118 PO_LOG.proc_begin(d_module);
119 PO_LOG.proc_begin(d_module, 'p_session_gt_key', p_session_gt_key);
120 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
121 PO_LOG.proc_begin(d_module, 'p_po_style_id', p_po_style_id);
122 END IF;
123
124 d_progress := 10;
125
126 IF FND_API.to_Boolean(p_init_msg_list) THEN
127 FND_MSG_PUB.initialize;
128 END IF;
129
130 d_progress := 20;
131 IF NOT FND_API.Compatible_API_Call(l_api_version,
132 p_api_version,
133 l_api_name,
134 G_PKG_NAME) THEN
135 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
136 END IF;
137
138
139 d_progress := 25;
140
141 OPEN REQ_LINES_SOURCE_CSR;
142 LOOP
143 FETCH REQ_LINES_SOURCE_CSR
144 INTO l_source_doc_id,
145 l_line_type_id,
146 l_destination_type,
147 l_purchase_basis,
148 l_source_doc_style_id;
149 EXIT WHEN REQ_LINES_SOURCE_CSR%NOTFOUND;
150
151 IF (PO_LOG.d_stmt) THEN
152 PO_LOG.stmt(d_module, d_progress, 'style validate Req attrs bef');
153 END IF;
154
155
156 STYLE_VALIDATE_REQ_ATTRS(p_api_version => 1.0,
157 p_init_msg_list => p_init_msg_list,
158 x_return_status => x_return_status,
159 x_msg_count => x_msg_count,
160 x_msg_data => x_msg_data,
161 p_doc_style_id => l_source_doc_style_id,
162 p_document_id => null,
163 p_line_type_id => l_line_type_id,
164 p_purchase_basis => l_purchase_basis,
165 p_destination_type => l_destination_type,
166 p_source => 'AUTOCREATE');
167
168 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
169
170 X_style_id := NULL;
171 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
172 p_data => x_msg_data);
173
174 IF (PO_LOG.d_proc) THEN
175 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
176 PO_LOG.proc_end(d_module, 'x_style_id', x_style_id);
177 PO_LOG.proc_end(d_module);
178 END IF;
179
180 CLOSE REQ_LINES_SOURCE_CSR;
181 RETURN;
182 END IF;
183
184 END LOOP;
185
186 CLOSE REQ_LINES_SOURCE_CSR;
187
188 d_progress := 30;
189 IF p_po_style_id is NOT NULL THEN
190
191 d_progress := 32;
192 l_group_style_id := p_po_style_id;
193
194 ELSE
195
196 d_progress := 40;
197 --Determine how many styles exist in the requisition lines
198 --Get the source doc styles and group by style id.
199 select poh.style_id BULK COLLECT
200 into l_style_id_tbl
201 from po_session_gt pgt,
202 po_headers_all poh
203 where pgt.key = p_session_gt_key
204 and pgt.num2 = poh.po_header_id
205 group by poh.style_id;
206
207
208 IF p_po_header_id is NULL THEN
209
210 IF (PO_LOG.d_stmt) THEN
211 PO_LOG.stmt(d_module, d_progress, 'Style Validate Action NEW');
212 END IF;
213 ---ACTION NEW
214
215 -- If more than one record is retrieved it means that
216 -- more than one style exists on the document
217 -- populate the error messages and return false
218
219 IF l_style_id_tbl.count > 1 THEN
220
221 d_progress := 50;
222 FND_MESSAGE.SET_NAME('PO','PO_REQ_LINES_MIXED_STYLES');
223 RAISE FND_API.G_EXC_ERROR;
224 END IF;
225
226 IF l_style_id_tbl.count = 0 THEN
227 --There is no style and hence all are
228 --compatible and they would have a standard style
229 d_progress := 60;
230
231 x_style_id := PO_DOC_STYLE_GRP.get_standard_doc_style;
232 x_return_status := FND_API.g_ret_sts_success;
233
234 IF (PO_LOG.d_proc) THEN
235 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
236 PO_LOG.proc_end(d_module, 'x_style_id', x_style_id);
237 PO_LOG.proc_end(d_module);
238 END IF;
239 RETURN;
240 END IF;
241
242 IF l_style_id_tbl.count = 1 THEN
243 d_progress := 70;
244 l_group_style_id := l_style_id_tbl(1);
245 END IF;
246 ELSE /*ACTION ADD_TO */
247
248 d_progress := 110;
249
250 IF (PO_LOG.d_stmt) THEN
251 PO_LOG.stmt(d_module, d_progress, 'Style Validate Action ADD');
252 END IF;
253
254 l_group_style_id := get_doc_style_id(p_po_header_id);
255
256 IF l_style_id_tbl.count > 1 THEN
257 d_progress := 120;
258
259 FND_MESSAGE.SET_NAME('PO','PO_ADDTO_DOCSTYLE_MISMATCH');
260 RAISE FND_API.G_EXC_ERROR;
261 ELSIF l_style_id_tbl.count = 1 THEN
262 IF l_style_id_tbl(1)<> l_group_style_id THEN
263 FND_MESSAGE.SET_NAME('PO','PO_ADDTO_DOCSTYLE_MISMATCH');
264 RAISE FND_API.G_EXC_ERROR;
265 END IF;
266 END IF;
267
268 d_progress := 130;
269
270
271 END IF; /*IF p_po_header_id is NULL THEN*/
272 END IF; /*IF p_style_id is NOT NULL THEN*/
273
274 d_progress := 200;
275 IF (PO_LOG.d_stmt) THEN
276 PO_LOG.stmt(d_module, d_progress, 'Group style id',l_group_style_id);
277 END IF;
278
279 IF PO_DOC_STYLE_GRP.is_standard_doc_style(l_group_style_id) = 'Y' THEN
280
281 x_style_id := l_group_style_id;
282 x_return_status := FND_API.g_ret_sts_success;
283
284 IF (PO_LOG.d_proc) THEN
285 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
286 PO_LOG.proc_end(d_module, 'x_style_id', x_style_id);
287 PO_LOG.proc_end(d_module);
288 END IF;
289 RETURN;
290 ELSE
291 --Fetch req lines without source refernces from the GT table
292 OPEN REQ_LINES_NOSOURCE_CSR;
293 LOOP
294 FETCH REQ_LINES_NOSOURCE_CSR
295 INTO l_line_type_id,
296 l_destination_type,
297 l_purchase_basis;
298 EXIT WHEN REQ_LINES_NOSOURCE_CSR%NOTFOUND;
299
300 IF (PO_LOG.d_stmt) THEN
301 PO_LOG.stmt(d_module, d_progress, 'style validate Req attrs');
302 END IF;
303
304
305 STYLE_VALIDATE_REQ_ATTRS(p_api_version => 1.0,
306 p_init_msg_list => p_init_msg_list,
307 x_return_status => x_return_status,
308 x_msg_count => x_msg_count,
309 x_msg_data => x_msg_data,
310 p_doc_style_id => l_group_style_id,
311 p_document_id => null,
312 p_line_type_id => l_line_type_id,
313 p_purchase_basis => l_purchase_basis,
314 p_destination_type => l_destination_type,
315 p_source => 'AUTOCREATE');
316
317 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
318
319 X_style_id := NULL;
320 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
321 p_data => x_msg_data);
322
323 IF (PO_LOG.d_proc) THEN
324 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
325 PO_LOG.proc_end(d_module, 'x_style_id', x_style_id);
326 PO_LOG.proc_end(d_module);
327 END IF;
328
329 CLOSE REQ_LINES_NOSOURCE_CSR;
330 RETURN;
331 END IF;
332
333 END LOOP;
334
335 x_style_id := l_group_style_id;
336 x_return_status := FND_API.g_ret_sts_success;
337
338 IF (PO_LOG.d_proc) THEN
339 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
340 PO_LOG.proc_end(d_module, 'x_style_id', x_style_id);
341 PO_LOG.proc_end(d_module);
342 END IF;
343
344 CLOSE REQ_LINES_NOSOURCE_CSR;
345 RETURN;
346 END IF; /*IF PO_DOC_STYLE_GRP.is_standard_doc_style*/
347
348
349 EXCEPTION
350 WHEN FND_API.G_EXC_ERROR THEN
351 x_style_id := NULL;
352 x_return_status := FND_API.G_RET_STS_ERROR;
353 FND_MSG_PUB.ADD;
354 FND_MSG_PUB.Count_And_Get
355 (p_count => x_msg_count,
356 p_data => x_msg_data
357 );
358
359 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
361
362 IF (PO_LOG.d_exc) THEN
363 PO_LOG.exc(d_module,d_progress,SQLCODE || SQLERRM);
364 END IF;
365
366 WHEN OTHERS THEN
367
368 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
369
370 IF (PO_LOG.d_exc) THEN
371 PO_LOG.exc(d_module,d_progress,SQLCODE || SQLERRM);
372 END IF;
373
374 END style_validate_req_lines;
375
376 --------------------------------------------------------------------------------
377 --Start of Comments
378 --Name: style_validate_req_attrs
379 --Pre-reqs:
380 -- None.
381 --Modifies:
382 -- None.
383 --Locks:
384 -- None.
385 --Function:
386 -- This procedure would check whether the attributes of requisition line would be
387 -- be compatible with style
388 --Parameters:
389 --IN:
390 -- p_doc_style_id
391 -- Document Style against which attributes of a requisition line are validated
392 -- p_document_id
393 -- Document against which attributes of a requisition line are validated for style
394 -- p_line_type_id
395 -- Line type of requisition line
396 -- p_purchase_basis
397 -- Purchase basis of a requisition line
398 -- p_destination_type
399 -- Destination type of a requisition line
400 -- p_source
401 -- Calling program
402 -- Possible values
403 -- 'AUTOCREATE' called from Autocreate Forms
404 -- 'REQUISITION' called from Requisition Entry and Automatic Sourcing
405 --OUT:
406 -- x_return_status
407 -- FND_API.g_ret_sts_success : indicates the attributes of requisition line
408 -- are compatible with a style
409 -- FND_API.g_ret_sts_error : attributes of requisition line encountered
410 -- style incomaptibility
411 --End of Comments
412 -------------------------------------------------------------------------------
413
414 PROCEDURE style_validate_req_attrs(p_api_version IN NUMBER DEFAULT 1.0,
415 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
416 x_return_status OUT NOCOPY VARCHAR2,
417 x_msg_count OUT NOCOPY NUMBER,
418 x_msg_data OUT NOCOPY VARCHAR2,
419 p_doc_style_id IN NUMBER,
420 p_document_id IN NUMBER,
421 p_line_type_id IN VARCHAR2,
422 p_purchase_basis IN VARCHAR2,
423 p_destination_type IN VARCHAR2,
424 p_source IN VARCHAR2) IS
425
426 d_progress NUMBER;
427 d_module VARCHAR2(60) := 'po.plsql.PO_DOC_STYLE_PVT.style_validate_req_attrs';
428
429 l_api_name CONSTANT VARCHAR2(30) := 'style_validate_req_attrs';
430 l_api_version CONSTANT NUMBER := 1.0;
431
432
433 l_doc_style_id PO_DOC_STYLE_HEADERS.style_id%type;
434 l_purchase_basis PO_REQUISITION_LINES_ALL.purchase_basis%TYPE;
435 l_purchase_basis_enabled BOOLEAN;
436 l_line_type_allowed PO_DOC_STYLE_HEADERS.line_type_allowed%TYPE;
437 l_destination_type PO_LOOKUP_CODES.lookup_code%TYPE;
438
439 BEGIN
440
441 d_progress := 0;
442 IF (PO_LOG.d_proc) THEN
443 PO_LOG.proc_begin(d_module);
444 PO_LOG.proc_begin(d_module, 'p_doc_style_id', p_doc_style_id);
445 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
446 PO_LOG.proc_begin(d_module, 'p_line_type_id', p_line_type_id);
447 PO_LOG.proc_begin(d_module, 'p_purchase_basis', p_purchase_basis);
448 PO_LOG.proc_begin(d_module, 'p_destination_type', p_destination_type);
449 PO_LOG.proc_begin(d_module, 'p_source', p_source);
450 END IF;
451
452 d_progress := 10;
453
454 IF FND_API.to_Boolean(p_init_msg_list) THEN
455 FND_MSG_PUB.initialize;
456 END IF;
457
458 d_progress := 20;
459 IF NOT FND_API.Compatible_API_Call(l_api_version,
460 p_api_version,
461 l_api_name,
462 G_PKG_NAME) THEN
463 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
464 END IF;
465
466 IF p_doc_style_id is NULL then
467 l_doc_style_id := get_doc_style_id(p_document_id);
468 ELSE
469 l_doc_style_id := p_doc_style_id;
470 END IF;
471
472 IF (PO_LOG.d_proc) THEN
473 PO_LOG.proc_begin(d_module, 'l_doc_style_id', l_doc_style_id);
474 END IF;
475
476 IF p_purchase_basis is null THEN
477
478 d_progress := 20;
479
480 SELECT purchase_basis
481 INTO l_purchase_basis
482 FROM po_line_types_b
483 WHERE line_type_id = p_line_type_id;
484
485 ELSE
486 l_purchase_basis := p_purchase_basis;
487 END IF;
488
489 IF (PO_LOG.d_proc) THEN
490 PO_LOG.proc_begin(d_module, 'l_purchase_basis', l_purchase_basis);
491 END IF;
492 /*Validate Purchase basis*/
493
494 IF (PO_LOG.d_stmt) THEN
495 PO_LOG.stmt(d_module, d_progress, 'validate purchase basis ');
496 END IF;
497
498 CHECK_PURCHASE_BASIS_ENABLED(p_style_id => l_doc_style_id,
499 p_purchase_basis => l_purchase_basis,
500 x_purchase_basis_enabled => l_purchase_basis_enabled,
501 x_related_line_types => l_line_type_allowed);
502
503 IF NOT l_purchase_basis_enabled THEN
504
505 FND_MESSAGE.set_name('PO', 'PO_REQLINE_ATTR_INCOMPATIBLE');
506 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('PO','PO_LINE_TYPE_PURCHASE_BASIS'));
507 RAISE FND_API.G_EXC_ERROR;
508 END IF;
509
510
511 /*Validate Line Type */
512
513 IF (PO_LOG.d_stmt) THEN
514 PO_LOG.stmt(d_module, d_progress, 'validate line type ');
515 END IF;
516
517 IF (l_line_type_allowed = 'SPECIFIED') THEN
518
519 IF NOT IS_LINE_TYPE_ENABLED(l_doc_style_id,
520 p_line_type_id
521 ) THEN
522
523 d_progress := 30;
524 FND_MESSAGE.set_name('PO','PO_REQLINE_ATTR_INCOMPATIBLE');
525 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('PO','PO_LINE_TYPE'));
526 RAISE FND_API.G_EXC_ERROR;
527 END IF;
528 END IF;
529
530 -- The complex work validations need to be done only in the case
531 -- of Autocreate and Requisitions Forms
532 -- Skip these when called from iProcuremnt (p_source= 'ICX')
533 -- Bug 5070181
534
535 IF p_source IN ('AUTOCREATE', 'REQUISITION') then
536 /*Complex work validations :*/
537
538 IF is_progress_payments_enabled(l_doc_style_id) THEN
539 d_progress := 100;
540
541 IF (PO_LOG.d_stmt) THEN
542 PO_LOG.stmt(d_module, d_progress, 'complex work validations: line type');
543 END IF;
544
545 /*Complex work validation :1 */
546 IF IS_RATE_BASED_TEMP_LABOR(p_line_type_id) THEN
547 d_progress := 110;
548 IF (p_source = 'AUTOCREATE') THEN
549 d_progress := 120;
550 FND_MESSAGE.set_name('PO','PO_REQLINE_ATTR_INCOMPATIBLE');
551 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('PO','PO_LINE_TYPE'));
552 ELSIF (p_source = 'REQUISITION') THEN
553 d_progress := 130;
554 FND_MESSAGE.SET_NAME('PO','PO_DOCSTYLE_TEMPLABOR_MISMATCH');
555 END IF;
556 RAISE FND_API.G_EXC_ERROR;
557 END IF;
558
559 /*Complex work Validation :2 */
560 IF is_amount_based_services_line(p_line_type_id) THEN
561 d_progress := 135;
562 IF (p_source = 'AUTOCREATE') THEN
563 d_progress := 137;
564 FND_MESSAGE.set_name('PO','PO_REQLINE_ATTR_INCOMPATIBLE');
565 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('PO','PO_LINE_TYPE'));
566 ELSIF (p_source = 'REQUISITION') THEN
567 d_progress := 139;
568 FND_MESSAGE.SET_NAME('PO','PO_AMT_SRV_PRO_PAY_INVALID');
569 END IF;
570 RAISE FND_API.G_EXC_ERROR;
571 END IF;
572
573
574 /*Complex work validation :3 */
575 IF (PO_LOG.d_stmt) THEN
576 PO_LOG.stmt(d_module, d_progress, 'complex work validations: destination type');
577 END IF;
578
579 IF p_destination_type is not null AND
580 p_destination_type IN ('INVENTORY', 'SHOP FLOOR') THEN
581 d_progress := 150;
582
583
584 IF (p_source = 'AUTOCREATE') THEN
585 d_progress := 160;
586 FND_MESSAGE.set_name('PO','PO_REQLINE_ATTR_INCOMPATIBLE');
587 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('PO','PO_DESTINATION_TYPE'));
588
589 ELSIF (p_source = 'REQUISITION') THEN
590 d_progress := 170;
591 select displayed_field
592 into l_destination_type
593 from po_lookup_codes
594 where LOOKUP_TYPE = 'DESTINATION TYPE'
595 and LOOKUP_CODE = p_destination_type;
596
597 FND_MESSAGE.set_name('PO','PO_DOCSTYLE_DEST_TYPE_MISMATCH');
598 FND_MESSAGE.SET_TOKEN('DESTINATION_TYPE',l_destination_type);
599 END IF;
600
601 RAISE FND_API.G_EXC_ERROR;
602 END IF;
603
604 END IF; /*IF is_progress_payments_enabled(l_doc_style_id)*/
605 END IF; /*IF p_source IN ('AUTOCREATE', 'REQUISITION')*/
606
607 x_return_status := FND_API.G_RET_STS_SUCCESS;
608
609 IF (PO_LOG.d_proc) THEN
610 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
611 PO_LOG.proc_end(d_module);
612 END IF;
613 RETURN;
614
615 EXCEPTION
616 WHEN FND_API.G_EXC_ERROR THEN
617 x_return_status := FND_API.G_RET_STS_ERROR;
618 FND_MSG_PUB.ADD;
619 FND_MSG_PUB.Count_And_Get
620 (p_count => x_msg_count,
621 p_data => x_msg_data
622 );
623
624 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
625 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
626
627 IF (PO_LOG.d_exc) THEN
628 PO_LOG.exc(d_module,d_progress,SQLCODE || SQLERRM);
629 END IF;
630
631 WHEN OTHERS THEN
632 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
633
634 IF (PO_LOG.d_exc) THEN
635 PO_LOG.exc(d_module,d_progress,SQLCODE || SQLERRM);
636 END IF;
637
638 END style_validate_req_attrs;
639
640 --------------------------------------------------------------------------------
641 --Start of Comments
642 --Name: check_purchase_basis_enabled
643 --Pre-reqs:
644 -- None.
645 --Modifies:
646 -- None.
647 --Locks:
648 -- None.
649 --Function:
650 -- check if purchase basis is enabled for a given style
651 -- is of STANDARD type.
652 --Parameters:
653 --IN:
654 -- p_style_id
655 -- Indicates the Document Style
656 -- p_purchase_basis
657 -- Purchase basis of a requisition line
658 --OUT:
659 -- x_purchase_basis_enabled
660 -- Indicates that the purchasis basis is enabled for the document style
661 -- x_related_line_types
662 -- returns which line types are enabled
663 --End of Comments
664 -------------------------------------------------------------------------------
665
666 PROCEDURE check_purchase_basis_enabled(p_style_id IN NUMBER,
667 p_purchase_basis IN VARCHAR2,
668 x_purchase_basis_enabled OUT NOCOPY BOOLEAN,
669 x_related_line_types OUT NOCOPY VARCHAR2) IS
670
671 d_progress NUMBER;
672 d_module VARCHAR2(60) := 'po.plsql.PO_DOC_STYLE_PVT.check_purchase_basis_enabled';
673
674 BEGIN
675
676 d_progress := 0;
677 IF (PO_LOG.d_proc) THEN
678 PO_LOG.proc_begin(d_module);
679 PO_LOG.proc_begin(d_module, 'p_style_id', p_style_id);
680 PO_LOG.proc_begin(d_module, 'p_purchase_basis', p_purchase_basis);
681 END IF;
682
683 d_progress := '010';
684
685 SELECT pdsh.line_type_allowed
686 INTO x_related_line_types
687 FROM po_doc_style_values pdsv,
688 po_doc_style_headers pdsh
689 WHERE pdsh.style_id = p_style_id
690 AND pdsv.style_id = pdsh.style_id
691 AND pdsv.style_attribute_name = 'PURCHASE_BASES'
692 AND pdsv.style_allowed_value = p_purchase_basis
693 AND nvl(pdsv.enabled_flag,
694 'N') = 'Y';
695
696 d_progress := '020';
697
698 X_purchase_basis_enabled := TRUE;
699 IF (PO_LOG.d_proc) THEN
700 PO_LOG.proc_end(d_module, 'x_purchase_basis_enabled', x_purchase_basis_enabled);
701 PO_LOG.proc_end(d_module, 'x_related_line_types', x_related_line_types);
702 PO_LOG.proc_end(d_module);
703 END IF;
704
705 RETURN;
706 EXCEPTION
707 WHEN NO_DATA_FOUND THEN
708 x_purchase_basis_enabled := FALSE;
709 x_related_line_types := NULL;
710 IF (PO_LOG.d_proc) THEN
711 PO_LOG.proc_end(d_module, 'x_purchase_basis_enabled', x_purchase_basis_enabled);
712 PO_LOG.proc_end(d_module, 'x_related_line_types', x_related_line_types);
713 PO_LOG.proc_end(d_module);
714 END IF;
715
716 RETURN;
717 END check_purchase_basis_enabled;
718
719 --------------------------------------------------------------------------------
720 --Start of Comments
721 --Name: is_line_type_enabled
722 --Pre-reqs:
723 -- None.
724 --Modifies:
725 -- None.
726 --Locks:
727 -- None.
728 --Function:
729 -- validates the line type for a given style
730 --Parameters:
731 --IN:
732 -- p_style_id
733 -- Indicates the Document Style
734 -- p_line_type_id
735 -- Line Type of a requisition line
736 --End of Comments
737 -------------------------------------------------------------------------------
738 FUNCTION is_line_type_enabled(p_style_id IN NUMBER,
739 p_line_type_id IN NUMBER
740 ) RETURN BOOLEAN IS
741
742 d_progress NUMBER;
743 d_module VARCHAR2(60) := 'po.plsql.PO_DOC_STYLE_PVT.is_line_type_enabled';
744
745
746
747 l_count NUMBER;
748 BEGIN
749
750 d_progress := 0;
751 IF (PO_LOG.d_proc) THEN
752 PO_LOG.proc_begin(d_module);
753 PO_LOG.proc_begin(d_module, 'p_style_id', p_style_id);
754 PO_LOG.proc_begin(d_module, 'p_line_type_id', p_line_type_id);
755 END IF;
756
757 d_progress := 10;
758
759
760 SELECT count(1)
761 INTO l_count
762 FROM dual
763 WHERE exists
764 (SELECT NULL
765 FROM PO_DOC_STYLE_VALUES pdv
766 WHERE pdv.style_id = p_style_id
767 AND pdv.style_attribute_name = 'LINE_TYPES'
768 AND pdv.style_allowed_value = to_char(p_line_type_id)
769 AND nvl(pdv.enabled_flag,
770 'N') = 'Y');
771
772 d_progress := 20;
773 IF l_count > 0 THEN
774 return TRUE;
775 END IF;
776
777 d_progress := 030;
778
779 return FALSE;
780
781 EXCEPTION
782 WHEN OTHERS THEN
783 IF (PO_LOG.d_exc) THEN
784 PO_LOG.exc(d_module,d_progress,SQLCODE || SQLERRM);
785 END IF;
786 RAISE;
787 END is_line_type_enabled;
788
789 --------------------------------------------------------------------------------
790 --Start of Comments
791 --Name: is_rate_based_temp_labor
792 --Pre-reqs:
793 -- None.
794 --Modifies:
795 -- None.
796 --Locks:
797 -- None.
798 --Function:
799 -- checks whether the line type is rate temp based labour
800 --Parameters:
801 --IN:
802 -- p_line_type_id
803 -- Line Type of a requisition line
804 --End of Comments
805 -------------------------------------------------------------------------------
806 FUNCTION is_rate_based_temp_labor(p_line_type_id NUMBER) RETURN BOOLEAN is
807
808 d_progress NUMBER;
809 d_module VARCHAR2(60) := 'po.plsql.PO_DOC_STYLE_PVT.is_rate_based_temp_labor';
810
811 l_result VARCHAR2(1);
812 l_count NUMBER;
813 BEGIN
814
815 d_progress := 0;
816 IF (PO_LOG.d_proc) THEN
817 PO_LOG.proc_begin(d_module);
818 PO_LOG.proc_begin(d_module, 'p_line_type_id', p_line_type_id);
819 END IF;
820 d_progress := 10;
821
822 SELECT count(1)
823 INTO l_count
824 FROM dual
825 WHERE exists
826 (SELECT NULL
827 FROM po_line_types_b
828 WHERE purchase_basis = 'TEMP LABOR'
829 AND order_type_lookup_code = 'RATE'
830 AND line_type_id = p_line_type_id);
831
832 d_progress := 20;
833 IF l_count > 0 THEN
834 return TRUE;
835 END IF;
836
837 d_progress := 030;
838
839 return FALSE;
840 EXCEPTION
841 WHEN OTHERS THEN
842 IF (PO_LOG.d_exc) THEN
843 PO_LOG.exc(d_module,d_progress,SQLCODE || SQLERRM);
844 END IF;
845 RAISE;
846 END is_rate_based_temp_labor;
847
848
849 --------------------------------------------------------------------------------
850 --Start of Comments
851 --Name: is_amount_based_services_line
852 --Pre-reqs:
853 -- None.
854 --Modifies:
855 -- None.
856 --Locks:
857 -- None.
858 --Function:
859 -- checks whether the line type is amount based services line type
860 --Parameters:
861 --IN:
862 -- p_line_type_id
863 -- Line Type of a requisition line
864 --End of Comments
865 -------------------------------------------------------------------------------
866 FUNCTION is_amount_based_services_line(p_line_type_id NUMBER) RETURN BOOLEAN is
867
868 d_progress NUMBER;
869 d_module VARCHAR2(60) := 'po.plsql.PO_DOC_STYLE_PVT.is_amount_based_services_line';
870
871 l_result VARCHAR2(1);
872 l_count NUMBER;
873 BEGIN
874
875 d_progress := 0;
876 IF (PO_LOG.d_proc) THEN
877 PO_LOG.proc_begin(d_module);
878 PO_LOG.proc_begin(d_module, 'p_line_type_id', p_line_type_id);
879 END IF;
880 d_progress := 10;
881
882 SELECT count(1)
883 INTO l_count
884 FROM dual
885 WHERE exists
886 (SELECT NULL
887 FROM po_line_types_b
888 WHERE purchase_basis = 'SERVICES'
889 AND order_type_lookup_code = 'AMOUNT'
890 AND line_type_id = p_line_type_id);
891
892 d_progress := 20;
893 IF l_count > 0 THEN
894 return TRUE;
895 END IF;
896
897 d_progress := 030;
898
899 return FALSE;
900 EXCEPTION
901 WHEN OTHERS THEN
902 IF (PO_LOG.d_exc) THEN
903 PO_LOG.exc(d_module,d_progress,SQLCODE || SQLERRM);
904 END IF;
905 RAISE;
906 END is_amount_based_services_line;
907
908 --------------------------------------------------------------------------------
909 --Start of Comments
910 --Name: get_doc_style_id
911 --Pre-reqs:
912 -- None.
913 --Modifies:
914 -- None.
915 --Locks:
916 -- None.
917 --Function:
918 -- gets the style id associated with a document
919 --Parameters:
920 --IN:
921 -- p_style_id
922 -- Indicates the Document Style
923 --End of Comments
924 -------------------------------------------------------------------------------
925
926 FUNCTION get_doc_style_id(p_doc_id IN NUMBER) RETURN VARCHAR2 IS
927
928
929 l_style_id PO_DOC_STYLE_HEADERS.STYLE_ID%type;
930 BEGIN
931 --<Mod Project>
932 l_style_id := get_doc_style_id(p_doc_id,-1);
933 RETURN l_style_id;
934 --<Mod Project> End
935 END get_doc_style_id;
936
937
938
939
940 --------------------------------------------------------------------------------
941 --<Mod Project>
942 --Start of Comments
943 --Name: get_doc_style_id
944 --Pre-reqs:
945 -- None.
946 --Modifies:
947 -- None.
948 --Locks:
949 -- None.
950 --Function:
951 -- gets the style id associated with a document
952 --Parameters:
953 --IN:
954 -- p_style_id
955 -- Indicates the Document Style
956 -- p_draft_id
957 -- Indicates draft id
958 --End of Comments
959 -------------------------------------------------------------------------------
960
961 FUNCTION get_doc_style_id(p_doc_id IN NUMBER, p_draft_id NUMBER) RETURN VARCHAR2 IS
962
963 d_progress NUMBER;
964 d_module VARCHAR2(60) := 'po.plsql.PO_DOC_STYLE_PVT.get_doc_style_id';
965
966 l_style_id PO_DOC_STYLE_HEADERS.STYLE_ID%type;
967 BEGIN
968
969 d_progress := 0;
970 IF (PO_LOG.d_proc) THEN
971 PO_LOG.proc_begin(d_module);
972 PO_LOG.proc_begin(d_module, 'p_doc_id', p_doc_id);
973 END IF;
974
975 d_progress := 10;
976
977 SELECT style_id
978 INTO l_style_id
979 FROM PO_HEADERS_MERGE_V
980 WHERE po_header_id = p_doc_id and draft_id = p_draft_id;
981
982 RETURN l_style_id;
983
984 EXCEPTION
985 WHEN OTHERS THEN
986
987 IF (PO_LOG.d_exc) THEN
988 PO_LOG.exc(d_module,d_progress,SQLCODE || SQLERRM);
989 END IF;
990 RAISE;
991 END get_doc_style_id;
992 --<Mod Project>End
993
994
995 --------------------------------------------------------------------------------
996 --Start of Comments
997 --Name: get_style_display_name
998 --Pre-reqs:
999 -- None.
1000 --Modifies:
1001 -- None.
1002 --Locks:
1003 -- None.
1004 --Function:
1005 -- gets the style display name associated with a document
1006 --Parameters:
1007 --IN:
1008 -- p_style_id
1009 -- Indicates the Document Style
1010 --End of Comments
1011 -------------------------------------------------------------------------------
1012
1013 FUNCTION get_style_display_name(p_doc_id IN NUMBER,
1014 p_language IN VARCHAR2 DEFAULT NULL)
1015 RETURN VARCHAR2 IS
1016
1017 l_style_display_name PO_DOC_STYLE_LINES_TL.DISPLAY_NAME%type;
1018
1019 BEGIN
1020 --<MOD Project>
1021 l_style_display_name := get_style_display_name(p_doc_id,-1,p_language);
1022 return l_style_display_name;
1023 --<Mod Project>
1024
1025 END get_style_display_name;
1026
1027
1028 --------------------------------------------------------------------------------
1029 --<Mod Project>
1030 --Start of Comments
1031 --Name: get_style_display_name
1032 --Pre-reqs:
1033 -- None.
1034 --Modifies:
1035 -- None.
1036 --Locks:
1037 -- None.
1038 --Function:
1039 -- gets the style display name associated with a document
1040 --Parameters:
1041 --IN:
1042 -- p_style_id
1043 -- Indicates the Document Style
1044 -- p_draft_id
1045 -- Indicates draft id
1046 --End of Comments
1047 -------------------------------------------------------------------------------
1048
1049 FUNCTION get_style_display_name(p_doc_id IN NUMBER,p_draft_id NUMBER,
1050 p_language IN VARCHAR2 DEFAULT NULL)
1051 RETURN VARCHAR2 IS
1052
1053 d_progress NUMBER;
1054 d_module VARCHAR2(60) := 'po.plsql.PO_DOC_STYLE_PVT.get_style_display_name';
1055
1056 l_style_display_name PO_DOC_STYLE_LINES_TL.DISPLAY_NAME%type;
1057 --Bug 14115069 Need to check if type is planned po
1058 l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%type;
1059
1060 BEGIN
1061
1062 d_progress := 0;
1063 IF (PO_LOG.d_proc) THEN
1064 PO_LOG.proc_begin(d_module);
1065 PO_LOG.proc_begin(d_module, 'p_doc_id', p_doc_id);
1066 PO_LOG.proc_begin(d_module, 'p_language', p_language);
1067 END IF;
1068
1069 d_progress := 10;
1070
1071 SELECT type_lookup_code
1072 INTO l_type_lookup_code
1073 FROM PO_HEADERS_ALL
1074 WHERE po_header_id = p_doc_id;
1075
1076 --Bug 14115069 Need to check if type is planned po
1077 IF(l_type_lookup_code <> 'PLANNED') THEN
1078 select DISPLAY_NAME
1079 into l_style_display_name
1080 from PO_DOC_STYLE_LINES_TL TL,
1081 PO_HEADERS_MERGE_V ph
1082 where tl.LANGUAGE = nvl(p_language,
1083 USERENV('LANG'))
1084 and tl.style_id = ph.style_id
1085 and ph.po_header_id = p_doc_id
1086 and ph.draft_id = p_draft_id
1087 and tl.DOCUMENT_SUBTYPE = PH.TYPE_LOOKUP_CODE;
1088 ELSE
1089 SELECT TYPE_NAME
1090 INTO l_style_display_name
1091 FROM PO_DOCUMENT_TYPES_TL tl,
1092 PO_HEADERS_MERGE_V ph
1093 WHERE tl.language = NVL(p_language, USERENV('LANG'))
1094 AND ph.po_header_id = p_doc_id
1095 AND ph.draft_id = p_draft_id
1096 AND tl.document_subtype = 'PLANNED';
1097 END IF;
1098
1099 RETURN l_style_display_name;
1100
1101 EXCEPTION
1102 WHEN OTHERS THEN
1103 IF (PO_LOG.d_exc) THEN
1104 PO_LOG.exc(d_module,d_progress,SQLCODE || SQLERRM);
1105 END IF;
1106 RAISE;
1107 END get_style_display_name;
1108 --<Mod Project> End
1109
1110
1111 --------------------------------------------------------------------------------
1112 --Start of Comments
1113 --Name: is_progress_payments_enabled
1114 --Pre-reqs:
1115 -- None.
1116 --Modifies:
1117 -- None.
1118 --Locks:
1119 -- None.
1120 --Function:
1121 -- checks if progress payemnts are enabled for a given style
1122 --Parameters:
1123 -- p_style_id
1124 -- Indicates the Document Style
1125 --End of Comments
1126 -------------------------------------------------------------------------------
1127
1128 FUNCTION is_progress_payments_enabled(p_style_id NUMBER) RETURN BOOLEAN IS
1129
1130 d_progress NUMBER;
1131 d_module VARCHAR2(60) := 'po.plsql.PO_DOC_STYLE_PVT.is_progress_payments_enabled';
1132
1133 l_result VARCHAR2(1);
1134 BEGIN
1135
1136 d_progress := 0;
1137 IF (PO_LOG.d_proc) THEN
1138 PO_LOG.proc_begin(d_module);
1139 PO_LOG.proc_begin(d_module, 'p_style_id', p_style_id);
1140 END IF;
1141 d_progress := 10;
1142
1143 SELECT progress_payment_flag
1144 INTO l_result
1145 FROM po_doc_style_headers
1146 WHERE style_id = p_style_id;
1147
1148 IF l_result = 'Y' THEN
1149 RETURN TRUE;
1150 ELSE
1151 RETURN FALSE;
1152 END IF;
1153
1154 EXCEPTION
1155 WHEN OTHERS THEN
1156 IF (PO_LOG.d_exc) THEN
1157 PO_LOG.exc(d_module,d_progress,SQLCODE || SQLERRM);
1158 END IF;
1159 RAISE;
1160 END is_progress_payments_enabled;
1161
1162 --------------------------------------------------------------------------------
1163 --Start of Comments
1164 --Name: populate_gt_and_validate
1165 --Pre-reqs:
1166 -- None.
1167 --Modifies:
1168 -- None.
1169 --Locks:
1170 -- None.
1171 --Function:
1172 -- Populates the Po session gt with requistion lines to be style validated
1173 --End of Comments
1174 -------------------------------------------------------------------------------
1175 PROCEDURE populate_gt_and_validate(p_api_version IN NUMBER DEFAULT 1.0,
1176 p_init_msg_list IN VARCHAR2,
1177 x_return_status OUT NOCOPY VARCHAR2,
1178 x_msg_count OUT NOCOPY NUMBER,
1179 x_msg_data OUT NOCOPY VARCHAR2,
1180 p_req_line_id_table IN g_po_tbl_num,
1181 p_source_doc_id_table IN g_po_tbl_num,
1182 p_line_type_id_table IN g_po_tbl_num,
1183 p_destination_type_table IN g_po_tbl_char30,
1184 p_purchase_basis_table IN g_po_tbl_char30,
1185 p_po_header_id IN NUMBER,
1186 p_po_style_id IN NUMBER DEFAULT NULL,
1187 x_style_id OUT NOCOPY NUMBER) IS
1188 d_progress NUMBER;
1189 d_module VARCHAR2(60) := 'po.plsql.PO_DOC_STYLE_PVT.populate_gt_and_validate';
1190
1191 l_session_gt_key PO_SESSION_GT.KEY%TYPE;
1192 l_return_status VARCHAR2(2);
1193 l_msg_count NUMBER;
1194 l_msg_data VARCHAR2(2000);
1195
1196 BEGIN
1197
1198
1199 d_progress := 0;
1200 IF (PO_LOG.d_proc) THEN
1201 PO_LOG.proc_begin(d_module);
1202 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
1203 END IF;
1204
1205 SELECT PO_SESSION_GT_S.nextval INTO l_session_gt_key FROM dual;
1206
1207 /*
1208 * PO_SESSION_GT:
1209 * key = key into table
1210 * num1 = Requisition line ID
1211 * num2 = Source Document ID
1212 * num3 = Line Type ID
1213 * char1 = Destination type
1214 * char2 = Purchase Basis
1215 */
1216 d_progress := 10;
1217 IF (PO_LOG.d_stmt) THEN
1218 PO_LOG.stmt(d_module, d_progress, 'inserting into po_session_gt');
1219 END IF;
1220
1221 FORALL i IN p_req_line_id_table.first .. p_req_line_id_table.last
1222 insert into po_session_gt
1223 (key,
1224 num1,
1225 num2,
1226 num3,
1227 char1,
1228 char2)
1229 values
1230 (l_session_gt_key,
1231 p_req_line_id_table(i),
1232 p_source_doc_id_table(i),
1233 p_line_type_id_table(i),
1234 p_destination_type_table(i),
1235 p_purchase_basis_table(i)
1236 );
1237
1238 d_progress := 20;
1239 IF (PO_LOG.d_stmt) THEN
1240 PO_LOG.stmt(d_module, d_progress, 'style_validate_req_lines');
1241 END IF;
1242
1243 STYLE_VALIDATE_REQ_LINES(p_api_version => 1.0,
1244 p_init_msg_list => FND_API.G_TRUE,
1245 X_return_status => x_return_status,
1246 X_msg_count => x_msg_count,
1247 x_msg_data => x_msg_data,
1248 p_session_gt_key => l_session_gt_key,
1249 p_po_header_id => p_po_header_id,
1250 p_po_style_id => p_po_style_id,
1251 x_style_id => x_style_id);
1252
1253 delete po_session_gt where key = l_session_gt_key;
1254
1255 IF (PO_LOG.d_proc) THEN
1256 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1257 PO_LOG.proc_end(d_module);
1258 END IF;
1259
1260 EXCEPTION
1261 WHEN OTHERS THEN
1262 IF (PO_LOG.d_exc) THEN
1263 PO_LOG.exc(d_module,d_progress,SQLCODE || SQLERRM);
1264 END IF;
1265 RAISE;
1266 END populate_gt_and_validate;
1267
1268 END PO_DOC_STYLE_PVT;