1 PACKAGE BODY PO_LINE_LOCATIONS_PKG_S3 as
2 /* $Header: POXP4PSB.pls 115.4 2004/03/09 23:28:47 dreddy ship $ */
3
4 /*========================================================================
5 ** PROCEDURE NAME : check_unique
6 ** DESCRIPTION : We need either the po_line_id OR po_release_id
7 ** argument passed in. Since both are the same data type
8 ** cannot use function overloading unless we to_number()
9 ** one of them. For now please pass in a NULL or 0 if
10 ** either of them is NOT relevant.
11 ** ======================================================================*/
12
13
14 PROCEDURE check_unique (X_rowid VARCHAR2,
15 X_shipment_num VARCHAR2,
16 X_po_line_id NUMBER,
17 X_po_release_id NUMBER,
18 X_shipment_type VARCHAR2) IS
19
20 X_progress VARCHAR2(3) := NULL;
21 dummy NUMBER;
22
23 BEGIN
24
25 X_progress := '010';
26
27 if ((X_shipment_type = 'SCHEDULED') OR
28 (X_shipment_type = 'BLANKET')) then
29
30 /* This is checking uniques for Release Shipments of
31 ** both the SCHEDULED and BLANKET types */
32
33 SELECT 1
34 INTO dummy
35 FROM DUAL
36 WHERE not exists (SELECT 1
37 FROM po_line_locations
38 WHERE po_release_id = X_po_release_id
39 AND shipment_type = X_shipment_type
40 AND shipment_num = X_shipment_num
41 AND ((X_rowid is null) or
42 (X_rowid <> rowid)));
43
44 elsif ((X_shipment_type = 'STANDARD') OR
45 (X_shipment_type = 'PLANNED')) then
46
47
48 /* This is checking the uniques for Purchase Order Shipments
49 ** of STANDARD AND PLANNED POS */
50
51 SELECT 1
52 INTO dummy
53 FROM DUAL
54 WHERE not exists (SELECT 1
55 FROM po_line_locations
56 WHERE po_line_id = X_po_line_id
57 AND shipment_type in ('STANDARD','PLANNED')
58 AND shipment_num = X_shipment_num
59 AND ((X_rowid IS NULL)
60 OR (X_rowid <> rowid)));
61
62 elsif (X_shipment_type IN ('RFQ', 'QUOTATION')) then
63
64
65 /* This is checking the uniques for RFQ or Quotation Shipments
66 */
67
68 SELECT 1
69 INTO dummy
70 FROM DUAL
71 WHERE not exists (SELECT 1
72 FROM po_line_locations
73 WHERE po_line_id = X_po_line_id
74 AND shipment_type = X_shipment_type
75 AND shipment_num = X_shipment_num
76 AND ((X_rowid IS NULL)
77 OR (X_rowid <> rowid)));
78
79 elsif (X_shipment_type = 'PRICE BREAK') then
80
81 /* This is checking uniques for PRICE BREAKS */
82
83 SELECT 1
84 INTO dummy
85 FROM DUAL
86 WHERE not exists (SELECT 1
87 FROM po_line_locations
88 WHERE po_line_id = X_po_line_id
89 AND shipment_type = 'PRICE BREAK'
90 AND shipment_num = X_shipment_num
91 AND ((X_rowid <> rowid)
92 OR (X_rowid IS NULL)));
93 end if;
94
95
96 exception
97 when no_data_found then
98 po_message_s.app_error('PO_PO_ENTER_UNIQUE_SHIP_NUM');
99 when others then
100 po_message_s.sql_error('check_uniue',X_progress,sqlcode);
101
102 end check_unique;
103
104 /*===========================================================================
105
106 FUNCTION NAME: check unique
107 Bug 3494974
108 ===========================================================================*/
109 FUNCTION check_unique ( X_shipment_num VARCHAR2,
110 X_po_line_id NUMBER,
111 X_shipment_type VARCHAR2)
112 RETURN BOOLEAN IS
113
114 X_progress VARCHAR2(3) := NULL;
115 dummy NUMBER;
116
117 BEGIN
118
119 X_progress := '010';
120
121 SELECT 1
122 INTO dummy
123 FROM DUAL
124 WHERE not exists (SELECT 1
125 FROM po_line_locations
126 WHERE po_line_id = X_po_line_id
127 AND shipment_type = X_shipment_type
128 AND shipment_num = X_shipment_num);
129
130
131 Return TRUE;
132
133 exception
134 when no_data_found then
135 Return FALSE;
136 when others then
137 Return FALSE;
138
139 end check_unique;
140
141 /*===========================================================================
142
143 FUNCTION NAME: get_max_shipment_num
144
145 ===========================================================================*/
146
147 FUNCTION get_max_shipment_num
148 (X_po_line_id NUMBER,
149 X_po_release_id NUMBER,
150 X_shipment_type VARCHAR2) return number is
151
152 x_max_shipment_num NUMBER;
153 X_Progress varchar2(3) := '';
154
155 BEGIN
156 X_Progress := '010';
157
158 if ((X_shipment_type = 'STANDARD') OR
159 (X_shipment_type = 'PLANNED')) then
160 SELECT nvl(max(shipment_num),0)
161 INTO X_max_shipment_num
162 FROM po_line_locations
163 WHERE po_line_id = X_po_line_id
164 AND shipment_type IN ('STANDARD','PLANNED');
165
166 elsif (X_shipment_type IN ('RFQ', 'QUOTATION')) then
167 SELECT nvl(max(shipment_num),0)
168 INTO X_max_shipment_num
169 FROM po_line_locations
170 WHERE po_line_id = X_po_line_id
171 AND shipment_type = X_shipment_type;
172
173 elsif (X_shipment_type = 'PRICE BREAK') then
174 SELECT nvl(max(shipment_num),0)
175 INTO X_max_shipment_num
176 FROM po_line_locations
177 WHERE po_line_id = X_po_line_id
178 AND shipment_type = 'PRICE BREAK';
179
180 elsif ((X_shipment_type = 'SCHEDULED') OR
181 (X_shipment_type = 'BLANKET')) then
182
183 SELECT nvl(max(shipment_num),0)
184 INTO X_max_shipment_num
185 FROM po_line_locations
186 WHERE po_release_id = X_po_release_id ;
187
188 end if;
189
190 return(x_max_shipment_num);
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 return(0);
195 RAISE;
196
197 END get_max_shipment_num;
198
199 /*========================================================
200 ** PROCEDURE NAME : select_summary()
201 **=======================================================*/
202
203
204 function select_summary(X_po_release_id IN number)
205 return number is
206
207 X_rel_total number;
208 X_progress varchar2(3) := '';
209
210 begin
211 X_Progress := '010';
212
213 /* Bug# 1499773: kagarwal
214 ** Modified the following SQL to incorporate quantity_cancelled since
215 ** Total Released in Enter Releases Form is showing wrong value
216 ** when it has cancelled shipments.
217 */
218
219 select nvl(sum((nvl(quantity,0)-nvl(quantity_cancelled,0))*nvl(price_override,0)),0)
220 into X_rel_total
221 from po_line_locations
222 where po_release_id = X_po_release_id;
223
224 return(X_rel_total);
225
226 exception
227 when no_data_found then
228 null;
229 when others then
230 -- po_message_s.sql_error('select_summary',X_Progress,sqlcode);
231 raise;
232 end select_summary;
233
234 END PO_LINE_LOCATIONS_PKG_S3;
[Home] [Help]