1 PACKAGE BODY PO_ITEMS_SV1 AS
2 /* $Header: POXPISIB.pls 120.0.12000000.1 2007/01/16 23:04:37 appldev ship $ */
3
4 /*================================================================
5
6 FUNCTION NAME: val_item_id()
7
8 ==================================================================*/
9 FUNCTION val_item_id(x_item_id IN NUMBER,
10 x_organization_id IN NUMBER,
11 x_outside_operation_flag IN VARCHAR2) 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 x_item_id exists in mtl_system_items table */
21 SELECT count(*)
22 INTO x_temp
23 FROM mtl_system_items
24 WHERE inventory_item_id = x_item_id
25 AND organization_id = x_organization_id
26 AND enabled_flag = 'Y'
27 AND purchasing_item_flag = 'Y'
28 AND purchasing_enabled_flag = 'Y'
29 AND outside_operation_flag = x_outside_operation_flag
30 AND TRUNC(nvl(start_date_active, sysdate)) <= TRUNC(sysdate)
31 AND TRUNC(nvl(end_date_active, sysdate)) >= TRUNC(sysdate);
32
33 IF x_temp = 0 THEN
34 RETURN FALSE; /* validation fails */
35 ELSE
36 RETURN TRUE; /* validation succeeds */
37 END IF;
38
39 EXCEPTION
40 WHEN others THEN
41 po_message_s.sql_error
42 ('val_inventory_item_id', x_progress, sqlcode);
43 raise;
44 END val_item_id;
45
46 /*================================================================
47
48 FUNCTION NAME: derive_item_id()
49
50 ==================================================================*/
51 FUNCTION derive_item_id(X_item_number IN VARCHAR2,
52 X_vendor_product_num IN VARCHAR2,
53 X_vendor_id IN NUMBER,
54 X_organization_id IN VARCHAR2,
55 X_error_code IN OUT NOCOPY VARCHAR2)
56 return NUMBER IS
57
58 X_progress varchar2(3) := NULL;
59 X_inventory_item_id_v number := NULL;
60 x_temp binary_integer;
61
62 BEGIN
63
64 X_progress := '010';
65 IF (X_item_number is not null) THEN
66 /* check to see if there are any inventory_item_id exists */
67
68 SELECT inventory_item_id
69 INTO X_inventory_item_id_v
70 FROM mtl_system_items_kfv
71 WHERE concatenated_segments = X_item_number
72 AND organization_id = X_organization_id;
73
74 ELSIF (X_vendor_product_num is not null) THEN
75 /* item_number is null */
76 X_progress := '020';
77 BEGIN
78 SELECT distinct b.item_id
79 INTO X_inventory_item_id_v
80 FROM po_headers a, po_lines b
81 WHERE a.po_header_id = b.po_header_id
82 AND b.vendor_product_num = X_vendor_product_num
83 AND a.vendor_id = X_vendor_id
84 AND b.item_id is not NULL;
85 EXCEPTION
86 WHEN no_data_found THEN
87 RETURN NULL;
88 WHEN too_many_rows THEN
89 X_error_code := 'PO_PDOI_MULT_BUYER_PART';
90 RETURN NULL;
91 END;
92 END IF;
93
94 RETURN X_inventory_item_id_v;
95
96 EXCEPTION
97 WHEN no_data_found THEN
98 RETURN NULL;
99 When others then
100 po_message_s.sql_error('derive_item_id',X_progress, sqlcode);
101 raise;
102
103 END derive_item_id;
104
105 END PO_ITEMS_SV1;
[Home] [Help]