1 PACKAGE BODY PO_SOURCING_SV as
2 /* $Header: POXSCS1B.pls 115.7 2004/06/22 02:10:05 mbhargav ship $ */
3
4
5 /*============================= PO_SOURCING_SV ============================*/
6
7 /*===========================================================================
8
9 PROCEDURE NAME: val_order_pad_line()
10
11 ===========================================================================*/
12 PROCEDURE val_order_pad_line
13 (
14 p_item_id IN NUMBER,
15 p_category_id IN NUMBER,
16 p_vendor_id IN NUMBER,
17 p_vendor_site_id IN NUMBER,
18 p_vendor_contact_id IN NUMBER,
19 p_currency_code IN VARCHAR2,
20 p_ap_terms_id IN NUMBER,
21 p_fob_lookup_code IN VARCHAR2,
22 p_ship_via_code IN VARCHAR2,
23 p_freight_terms_code IN VARCHAR2,
24 p_line_type_id IN NUMBER,
25 p_unit_of_measure IN VARCHAR2,
26 p_dest_org_id IN NUMBER,
27 p_document_type IN VARCHAR2,
28 p_structure_id IN NUMBER,
29 p_source_type IN VARCHAR2,
30 p_display_message IN VARCHAR2,
31 p_cross_ref_type IN VARCHAR2,
32 p_instance_org_id IN NUMBER,
33 p_primary_inv_cost IN NUMBER,
34 p_purchasing_org_id IN NUMBER, --< Shared Proc FPJ >
35 X_multiple_flag IN OUT NOCOPY VARCHAR2,
36 X_messages_exist IN OUT NOCOPY BOOLEAN,
37 X_message IN OUT NOCOPY VARCHAR2,
38 X_category_val IN OUT NOCOPY BOOLEAN,
39 X_vendor_val IN OUT NOCOPY BOOLEAN,
40 X_vendor_site_val IN OUT NOCOPY BOOLEAN,
41 X_vendor_contact_val IN OUT NOCOPY BOOLEAN,
42 X_currency_val IN OUT NOCOPY BOOLEAN,
43 X_ap_terms_val IN OUT NOCOPY BOOLEAN,
44 X_fob_lookup_val IN OUT NOCOPY BOOLEAN,
45 X_ship_via_val IN OUT NOCOPY BOOLEAN,
46 X_freight_terms_val IN OUT NOCOPY BOOLEAN,
47 X_line_type_val IN OUT NOCOPY BOOLEAN,
48 X_unit_of_meas_val IN OUT NOCOPY BOOLEAN,
49 X_list_price IN OUT NOCOPY NUMBER,
50 X_planned_item_flag IN OUT NOCOPY VARCHAR2,
51 X_primary_uom IN OUT NOCOPY VARCHAR2,
52 X_convert_inv_cost IN OUT NOCOPY NUMBER,
53 X_change_price IN OUT NOCOPY BOOLEAN
54 )
55 IS
56
57 l_progress VARCHAR2(3) := NULL;
58 l_item_org_val BOOLEAN := TRUE;
59
60 -- <SERVICES FPJ START>
61 l_order_type_lookup_code po_lines_all.order_type_lookup_code%TYPE := null;
62 l_purchase_basis po_lines_all.purchase_basis%TYPE := null;
63 l_category_id NUMBER := null;
64 l_unit_meas_lookup_code po_lines_all.unit_meas_lookup_code%TYPE := null;
65 l_unit_price NUMBER := null;
66 l_outside_operation_flag po_line_types.outside_operation_flag%TYPE := null;
67 l_receiving_flag po_line_types.receiving_flag%TYPE := null;
68 l_receive_close_tolerance NUMBER := null;
69 -- <SERVICES FPJ END>
70
71 BEGIN
72
73 /* Verify those data elements that should always present on a source
74 ** document line. If any of these required elements are NULL, then
75 ** set the valid value to FALSE.
76 */
77
78 if ((p_category_id is null) and
79 (p_structure_id is null)) then
80
81 X_category_val := FALSE;
82 else
83 l_progress := '010';
84 X_category_val := po_items_sv.val_category(p_category_id,
85 p_structure_id);
86 end if;
87
88 -- <SERVICES FPJ START>
89 -- Need to retrieve the value basis to be used for validation
90 -- of UOM. If the line is a new Services line, UOM has to be null
91 IF (p_line_type_id is not null) THEN
92 PO_LINE_TYPES_SV.get_line_type_def(p_line_type_id,
93 l_order_type_lookup_code,
94 l_purchase_basis,
95 l_category_id,
96 l_unit_meas_lookup_code,
97 l_unit_price,
98 l_outside_operation_flag,
99 l_receiving_flag,
100 l_receive_close_tolerance);
101 END IF;
102 -- <SERVICES FPJ END>
103
104 IF (l_order_type_lookup_code <> 'FIXED PRICE' AND
105 l_order_type_lookup_code is not null) THEN -- <SERVICES FPJ>
106 if (p_unit_of_measure is null) then
107 X_unit_of_meas_val := FALSE;
108 else
109 l_progress := '020';
110 X_unit_of_meas_val := po_uom_s.val_unit_of_measure(p_unit_of_measure);
111 end if;
112 -- <SERVICES FPJ START>
113 ELSE
114 X_unit_of_meas_val := TRUE;
115 END IF;
116 -- <SERVICES FPJ END>
117
118 if (p_currency_code is null) then
119 X_currency_val := FALSE;
120 else
121 l_progress := '030';
122 X_currency_val := po_currency_sv.val_currency(p_currency_code);
123 end if;
124
125 if (p_line_type_id is null) then
126 X_line_type_val := FALSE;
127 else
128 l_progress := '040';
129 X_line_type_val:= po_line_types_sv.val_line_type(p_line_type_id);
130 end if;
131
132 /* If the source type is vendor, then validate the vendor-related
133 ** information. If this is an internally-sourced line, then we
134 ** will validate the source org etc. when we validate the Order
135 ** Pad record.
136 */
137
138 if (p_source_type = 'VENDOR') then
139
140 l_progress := '050';
141 X_vendor_val := po_vendors_sv.val_vendor(p_vendor_id);
142
143 l_progress := '060';
144 --< Shared Proc FPJ Start >
145 X_vendor_site_val := po_vendor_sites_sv.val_vendor_site_id
146 (p_document_type => p_document_type,
147 p_vendor_site_id => p_vendor_site_id,
148 p_org_id => p_purchasing_org_id);
149 --< Shared Proc FPJ End >
150
151 /* If a supplier contact has been specified, check to see if it
152 ** is still active.
153 */
154
155 if (p_vendor_contact_id is not null
156 and x_vendor_site_val) then --<Bug 3692519>
157
158 l_progress := '065';
159 X_vendor_contact_val :=
160 po_vendor_contacts_sv.val_vendor_contact(
161 p_vendor_contact_id => p_vendor_contact_id,
162 p_vendor_site_id => p_vendor_site_id); --<Bug 3692519>
163 end if;
164 end if;
165
166 /* If the item and destination org are not null, get relevant item information.
167 */
168
169 if (p_item_id is not null) then
170 if (p_dest_org_id is not null) then
171
172 l_progress := '070';
173 l_item_org_val := po_sourcing2_sv.get_item_detail(p_item_id,
174 p_dest_org_id,
175 X_planned_item_flag,
176 X_list_price,
177 X_primary_uom);
178 end if;
179 end if;
180
181
182 /* Now for inventory-sourced lines, if the order pad line's UOM differs
183 ** from the primary UOM, then we need to convert the inventory cost.
184 */
185
186
187
188 if ((p_item_id is null) or
189 (X_primary_uom = p_unit_of_measure) or
190 (p_primary_inv_cost is null) or
191 (p_vendor_id is null) or
192 (p_primary_inv_cost = 0) or
193 (X_unit_of_meas_val = FALSE) and
194 (p_source_type <> 'INVENTORY')) then
195
196 X_change_price := FALSE;
197
198 /* Bug#2632638 Added the below condition in the Else part as for Inventory-
199 ** Sourced lines, if the order pad line's UOM differs from the primary UOM,
200 ** then only we need to convert the inventory cost and not for the other
201 ** cases. */
202
203 elsif (p_source_type = 'INVENTORY') then
204
205 l_progress := '075';
206
207 SELECT primary_unit_of_measure
208 INTO X_primary_uom
209 FROM mtl_system_items
210 WHERE inventory_item_id = p_item_id
211 AND organization_id = p_dest_org_id;
212
213 if (po_uom_sv2.convert_inv_cost(p_item_id,
214 p_unit_of_measure,
215 X_primary_uom,
216 p_primary_inv_cost,
217 X_convert_inv_cost) = TRUE) then
218
219 X_change_price := TRUE;
220
221 end if;
222 end if;
223
224
225 /* If disposition messages should be shown for the item/item master
226 ** organization -- and we are working with a predefined item --
227 ** get the disposition message for this combination. If multiple
228 ** messages exist, display an warning message indicating the user
229 ** should look at the item cross references form.
230 */
231
232 if ((p_item_id is not null) and
233 (p_instance_org_id is not null) and
234 (p_display_message = 'Y') and
235 (l_item_org_val = TRUE)) then
236
237 X_messages_exist := po_sourcing_sv4.get_disposition_message(p_item_id,
238 p_instance_org_id,
239 p_cross_ref_type,
240 X_message,
241 X_multiple_flag);
242
243 end if;
244
245 /* If working within the Catalog from a purchase order, verify the terms
246 ** and conditions.
247 */
248
249 if (p_document_type in ('PO', 'BLANKET')) then
250
251 if (p_ap_terms_id is null) then
252 X_ap_terms_val := FALSE;
253 else
254 l_progress := '120';
255 X_ap_terms_val := po_terms_sv.val_payment_terms(p_ap_terms_id);
256 end if;
257
258 if (p_fob_lookup_code is null) then
259 X_fob_lookup_val := FALSE;
260 else
261 l_progress := '130';
262 X_fob_lookup_val := po_terms_sv.val_fob_code(p_fob_lookup_code);
263 end if;
264
265 if (p_freight_terms_code is null) then
266 X_freight_terms_val := FALSE;
267 else
268 l_progress := '140';
269 X_freight_terms_val := po_terms_sv.val_freight_code(p_freight_terms_code);
270 end if;
271
272 if (p_ship_via_code is null) then
273 X_ship_via_val := FALSE;
274 else
275 l_progress := '150';
276 X_ship_via_val := po_terms_sv.val_ship_via(p_ship_via_code,
277 p_instance_org_id);
278 end if;
279
280 end if;
281
282 EXCEPTION
283
284 when others then
285 po_message_s.sql_error('val_order_pad_line', l_progress, sqlcode);
286 raise;
287
288 END val_order_pad_line;
289
290 /*===========================================================================
291
292 FUNCTION NAME: vendor_sourcing_status()
293
294 ===========================================================================*/
295 FUNCTION vendor_sourcing_status(X_item_id IN NUMBER,
296 X_vendor_id IN NUMBER,
297 X_vendor_site_id IN NUMBER,
298 X_organization_id IN NUMBER,
299 X_autosource_rule_id IN NUMBER,
300 X_assignment_set_id IN NUMBER)
301 RETURN varchar2 IS
302
303 approval_status varchar2(20) := NULL;
304 temp varchar2(20) := NULL;
305
306 BEGIN
307
308 /* Note: this function does not have std. error handling as required to be
309 ** called from within a view.
310 */
311
312 /* One-time items do not have autosource rules associated with them,
313 ** so approval status is not applicable here.
314 */
315
316 if (X_item_id is null or X_vendor_id is null) then
317
318 approval_status := 'NOT APPLICABLE';
319
320 elsif (x_assignment_set_id IS NULL) then
321
322 /* If the query finds a row (only 1 AutoSource rule can be effective
323 ** at any point in time so this will return only 1 row if a match is
324 ** found), set the status to APPROVED. If no data found, see EXCEPTION.
325 **
326 ** Note: need 2 different selects because the AutoSource case is more
327 ** precise -- need to check for the given rule id while the check in
328 ** all other cases just needs to know if *any* rule is valid for the
329 ** constraints.
330 */
331
332 if (X_autosource_rule_id is null) then
333 select 'exists' into
334 temp
335 from po_autosource_rules par,
336 po_autosource_vendors pav
337 where par.item_id = X_item_id
338 and pav.vendor_id = X_vendor_id
339 and par.autosource_rule_id = pav.autosource_rule_id
340 and trunc(sysdate) between
341 nvl(par.start_date, sysdate - 1) and
342 nvl(par.end_date, sysdate + 1);
343
344 else
345 select 'exists' into
346 temp
347 from po_autosource_rules par,
348 po_autosource_vendors pav
349 where par.item_id = X_item_id
350 and pav.vendor_id = X_vendor_id
351 and par.autosource_rule_id = pav.autosource_rule_id
352 and par.autosource_rule_id = X_autosource_rule_id
353 and trunc(sysdate) between
354 nvl(par.start_date, sysdate - 1) and
355 nvl(par.end_date, sysdate + 1);
356
357 end if;
358
359 if (temp = 'exists') then
360
361 approval_status := 'APPROVED';
362
363 end if;
364
365 else
366
367 if (X_organization_id is null) then
368
369 -- Quotations do not have deliver-to orgs. Check that
370 -- the vendor, site on the quotation is approved for that
371 -- item in some org.
372
373 select 'exists' into
374 temp
375 from mrp_sources_v sv
376 where sv.inventory_item_id = X_item_id
377 and sv.vendor_id = X_vendor_id
378 and nvl(sv.vendor_site_id, -1) = nvl(X_vendor_site_id, -1)
379 and sv.assignment_set_id = X_assignment_set_id
380 and trunc(sysdate) between
381 nvl(sv.effective_date, sysdate - 1) and
382 nvl(sv.disable_date, sysdate + 1);
383
384 elsif (X_autosource_rule_id is null) then
385 select 'exists' into
386 temp
387 from mrp_sources_v sv
388 where sv.inventory_item_id = X_item_id
389 and sv.vendor_id = X_vendor_id
390 and nvl(sv.vendor_site_id, -1) = nvl(X_vendor_site_id, -1)
391 and sv.assignment_set_id = X_assignment_set_id
392 and sv.organization_id = X_organization_id
393 and trunc(sysdate) between
394 nvl(sv.effective_date, sysdate - 1) and
395 nvl(sv.disable_date, sysdate + 1);
396
397 else
398
399 select 'exists' into
400 temp
401 from mrp_sources_v sv
402 where sv.inventory_item_id = X_item_id
403 and sv.sourcing_rule_id = X_autosource_rule_id
404 and sv.vendor_id = X_vendor_id
405 and nvl(sv.vendor_site_id, -1) = nvl(X_vendor_site_id, -1)
406 and sv.assignment_set_id = X_assignment_set_id
407 and sv.organization_id = X_organization_id
408 and trunc(sysdate) between
409 nvl(sv.effective_date, sysdate - 1) and
410 nvl(sv.disable_date, sysdate + 1);
411
412 end if;
413
414 if (temp = 'exists') then
415
416 approval_status := 'APPROVED';
417
418 end if;
419
420 end if;
421
422 return (approval_status);
423
424 EXCEPTION
425 when no_data_found then
426
427 return('NOT APPROVED');
428
429 when others then
430
431 return (NULL);
432
433 END;
434
435
436
437 END PO_SOURCING_SV;
[Home] [Help]