1 PACKAGE BODY PO_LINES_SV1 AS
2 /* $Header: POXPILSB.pls 120.0.12000000.1 2007/07/27 07:25:38 grohit noship $ */
3
4 /*================================================================
5
6 FUNCTION NAME: val_line_num_uniqueness()
7
8 ==================================================================*/
9 FUNCTION val_line_num_uniqueness(x_line_num IN NUMBER,
10 x_rowid IN VARCHAR2,
11 x_po_header_id IN NUMBER) RETURN BOOLEAN
12 IS
13
14 x_progress varchar2(3) := null;
15 x_temp binary_integer := 0;
16
17 BEGIN
18 x_progress := '010';
19
20 /* check to see if there are any non_unique line_num exists
21 in po_lines */
22
23 SELECT count(*)
24 INTO x_temp
25 FROM po_lines
26 WHERE po_header_id = x_po_header_id
27 AND line_num = x_line_num
28 AND (rowid <> x_rowid OR x_rowid is null);
29
30 IF x_temp = 0 THEN
31 /* there are no duplicated line_num exists in po_lines */
32 RETURN TRUE;
33 ELSE
34 RETURN FALSE;
35 END IF;
36
37 EXCEPTION
38 WHEN others THEN
39 po_message_s.sql_error
40 ('val_line_num_uniqueness', x_progress, sqlcode);
41 raise;
42 END val_line_num_uniqueness;
43
44 /*================================================================
45
46 FUNCTION NAME: val_line_id_uniqueness()
47
48 ==================================================================*/
49 FUNCTION val_line_id_uniqueness(x_po_line_id IN NUMBER,
50 x_rowid IN VARCHAR2,
51 x_po_header_id IN NUMBER) RETURN BOOLEAN
52 IS
53
54 x_progress varchar2(3) := null;
55 x_temp binary_integer := 0;
56
57 BEGIN
58 x_progress := '010';
59
60 /* check to see if there are any non_unique line_id exists
61 in po_lines */
62 SELECT COUNT(*)
63 INTO x_temp
64 FROM po_lines
65 WHERE po_header_id = x_po_header_id
66 AND po_line_id = x_po_line_id
67 AND (rowid <> x_rowid OR x_rowid is null);
68
69 IF x_temp = 0 THEN /* no duplicated line_id found */
70 RETURN TRUE;
71 ELSE
72 RETURN FALSE;
73 END IF;
74
75 EXCEPTION
76 WHEN others THEN
77 po_message_s.sql_error('val_line_id_uniqueness', x_progress,sqlcode);
78 raise;
79 END val_line_id_uniqueness;
80
81 /*================================================================
82
83 FUNCTION NAME: derive_po_line_id()
84
85 ==================================================================*/
86
87 FUNCTION derive_po_line_id(X_po_header_id IN NUMBER,
88 X_line_num IN NUMBER)
89 return NUMBER IS
90
91 X_progress varchar2(3) := NULL;
92 X_po_line_id_v number := NULL;
93
94 BEGIN
95
96 X_progress := '010';
97
98 /* derive the po_line_id from po_lines based on the po_header_id and
99 line_num from the input parameter */
100
101 SELECT po_line_id
102 INTO X_po_line_id_v
103 FROM po_lines
104 WHERE po_header_id = X_po_header_id
105 AND line_num = X_line_num;
106
107 RETURN X_po_line_id_v;
108
109 EXCEPTION
110 When no_data_found then
111 RETURN NULL;
112 When others then
113 po_message_s.sql_error('derive_po_line_id',X_progress, sqlcode);
114 raise;
115 END derive_po_line_id;
116
117 END PO_LINES_SV1;
[Home] [Help]