1 PACKAGE BODY PO_LINE_LOCATIONS_SV1 AS
2 /* $Header: POXPILLB.pls 120.0 2005/06/02 00:30:35 appldev noship $ */
3
4
5 /*================================================================
6
7 FUNCTION NAME: val_shipment_num()
8
9 ==================================================================*/
10 FUNCTION val_shipment_num(x_shipment_num IN NUMBER,
11 x_shipment_type IN VARCHAR2,
12 x_po_header_id IN NUMBER,
13 x_po_line_id IN NUMBER,
14 x_rowid IN VARCHAR2) RETURN BOOLEAN
15 IS
16
17 x_progress varchar2(3) := null;
18 x_temp binary_integer := 0;
19 BEGIN
20 x_progress := '010';
21
22 /* check to see if there are any non_unique shipment_num exists
23 in po_shipments_all_v table */
24 --Bug4040677 Start
25 --Removed "AND shipment_type = 'PRICE BREAK'" clause. This clause was overriding
26 --the clause "shipment_type = x_shipment_type" and the code was breaking for QUOTATIONS.
27 SELECT count(*)
28 INTO x_temp
29 FROM po_line_locations
30 WHERE shipment_num = x_shipment_num
31 AND shipment_type = x_shipment_type
32 AND po_header_id = x_po_header_id
33 AND po_line_id = x_po_line_id
34 AND (rowid <> x_rowid OR x_rowid is null);
35 --Bug4040677 End
36
37 IF x_temp = 0 THEN
38 RETURN TRUE; /* shipment_num is unique */
39 ELSE
40 RETURN FALSE; /* shipment_num is not unique */
41 END IF;
42
43 EXCEPTION
44 WHEN others THEN
45 po_message_s.sql_error
46 ('val_shipment_num', x_progress, sqlcode);
47 raise;
48 END val_shipment_num;
49
50 /*================================================================
51
52 FUNCTION NAME: val_shipment_type()
53
54 ==================================================================*/
55 FUNCTION val_shipment_type(x_shipment_type IN VARCHAR2,
56 x_lookup_code IN VARCHAR2) RETURN BOOLEAN
57 IS
58
59 x_progress varchar2(3) := null;
60
61 BEGIN
62 x_progress := '010';
63 -- we only support shipment_type of 'QUOTATION' and 'BLANKET'
64
65 IF x_lookup_code = 'QUOTATION' THEN
66 IF (x_shipment_type = 'QUOTATION') THEN
67 RETURN TRUE;
68 ELSE
69 RETURN FALSE;
70 END IF;
71 ELSIF x_lookup_code = 'BLANKET' THEN
72 IF (x_shipment_type = 'PRICE BREAK') THEN
73 RETURN TRUE;
74 ELSE
75 RETURN FALSE;
76 END IF;
77 -- VRANKAIY
78 ELSIF x_lookup_code = 'STANDARD' THEN
79 RETURN TRUE;
80 END IF;
81 EXCEPTION
82 WHEN others THEN
83 po_message_s.sql_error('val_shipment_type', x_progress,sqlcode);
84 raise;
85 END val_shipment_type;
86
87 /*================================================================
88
89 FUNCTION NAME: derive_line_location_id()
90
91 ==================================================================*/
92 FUNCTION derive_line_location_id(X_po_header_id IN NUMBER,
93 X_po_line_id IN NUMBER,
94 X_shipment_num IN NUMBER)
95 return NUMBER IS
96
97 X_progress varchar2(3) := NULL;
98 X_line_location_id_v number := NULL;
99
100 BEGIN
101
102 X_progress := '010';
103
104 /* derive the line_location_id from po_line_locations based on
105 po_header_id, po_line_id and shipment_num which was provided
106 from the input parameter and shipment type of PRICE BREAK */
107 --Bug4040677 Start
108 --This SQL only considers BPAs. Added shipment_type clauses for SPO and QUOTATIONS.
109 --Its OK because a line will only have one type of shipments listed.
110
111 SELECT line_location_id
112 INTO X_line_location_id_v
113 FROM po_line_locations
114 WHERE po_header_id = X_po_header_id
115 AND po_line_id = X_po_line_id
116 AND shipment_num = X_shipment_num
117 AND shipment_type in ('PRICE BREAK','QUOTATION', 'STANDARD');
118 --Bug4040677 End
119
120 RETURN X_line_location_id_v;
121
122 EXCEPTION
123 When no_data_found then
124 RETURN NULL;
125 When others then
126 po_message_s.sql_error('derive_line_location_id',X_progress, sqlcode);
127 raise;
128 END derive_line_location_id;
129
130 /*===============================================================
131
132 FUNCTION NAME : derive_location_id()
133
134 ===============================================================*/
135
136 FUNCTION derive_location_id(X_location_code IN VARCHAR2,
137 X_location_usage IN VARCHAR2)
138 return NUMBER IS
139
140 X_progress varchar2(3) := NULL;
141 X_location_id_v number := NULL;
142
143 BEGIN
144
145 X_progress := '010';
146
147 /* get the location_id from po_locations_val_v view based on the
148 location_code and location_usages which are provided from
149 input parameter */
150
151 SELECT location_id
152 INTO X_location_id_v
153 FROM po_locations_val_v
154 WHERE location_code = X_location_code
155 AND DECODE(X_location_usage,
156 'SHIP_TO', NVL(ship_to_site_flag,'N'),
157 'BILL_TO', NVL(bill_to_site_flag,'N'),
158 'RECEIVING',NVL(receiving_site_flag,'N'),
159 'OFFICE',NVL(OFFICE_SITE_FLAG,'N')) = 'Y';
160
161 RETURN X_location_id_v;
162
163 EXCEPTION
164 WHEN NO_DATA_FOUND then
165 RETURN NULL;
166 WHEN OTHERS THEN
167 po_message_s.sql_error('derive_location_id',X_progress, sqlcode);
168 RAISE;
169
170 END derive_location_id;
171 /*==================================================================
172
173 FUNCTION NAME: val_location_id()
174
175 ==================================================================*/
176 FUNCTION val_location_id(X_location_id IN NUMBER,
177 X_location_type IN VARCHAR2)
178 RETURN BOOLEAN
179 IS
180
181 x_progress varchar2(3) := null;
182 x_temp binary_integer := 0;
183
184 BEGIN
185 x_progress := '010';
186
187 /*** make sure location_id is a valid and active location based
188 on the location type. ***/
189
190 SELECT count(*)
191 INTO x_temp
192 FROM po_locations_val_v
193 WHERE location_id = X_location_id
194 AND DECODE(X_location_type,
195 'SHIP_TO', NVL(ship_to_site_flag, 'N'),
196 'BILL_TO', NVL(bill_to_site_flag, 'N'),
197 'RECEIVING', NVL(receiving_site_flag, 'N'),
198 'OFFICE', NVL(office_site_flag, 'N') )
199 = 'Y';
200
201 IF x_temp = 0 THEN
202 RETURN FALSE; /* validation fails */
203 ELSE
204 RETURN TRUE; /* validation succeeds */
205 END IF;
206
207 EXCEPTION
208 WHEN others THEN
209 po_message_s.sql_error('val_location_id', x_progress,sqlcode);
210 raise;
211 END val_location_id;
212
213
214 END PO_LINE_LOCATIONS_SV1;
[Home] [Help]