1 PACKAGE BODY po_co_tolerances_pvt AS
2 /* $Header: PO_CO_TOLERANCES_PVT.plb 120.3.12010000.3 2008/10/23 08:40:27 rojain ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_CO_TOLERANCES_PVT';
5
6 -- Read the profile option that enables/disables the debug log
7 -- Logging global constants
8 d_package_base CONSTANT VARCHAR2(100) := po_log.get_package_base(g_pkg_name);
9
10 c_log_head CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
11
12 -- Debugging
13 g_debug_stmt CONSTANT BOOLEAN := po_debug.is_debug_stmt_on;
14 g_debug_unexp CONSTANT BOOLEAN := po_debug.is_debug_unexp_on;
15
16
17 --<R12 REQUESTER DRIVEN PROCUREMENT START>
18
19 ------------------------------------------------------------------------------
20 --Start of Comments
21 --Name: GET_TOLERANCES
22 --Pre-reqs:
23 -- None
24 --Modifies:
25 -- None
26 --Locks:
27 -- None
28 --Function:
29 -- 1. This procedure will retrieve the tolerances of a
30 -- given change order type and operating unit.
31 --Parameters:
32 --IN:
33 -- p_api_version
34 -- Used to determine compatibility of API and calling program
35 -- p_init_msg_list
36 -- True/False parameter to initialize message list
37 -- p_organization_id
38 -- Operating Unit Id
39 -- p_change_order_type
40 -- Change Order Type for which the tolerances should be retrieved.
41 --OUT:
42 -- x_tolerances_tbl
43 -- Table containing the tolerances and their values
44 -- x_return_status
45 -- The standard OUT parameter giving return status of the API call.
46 -- FND_API.G_RET_STS_ERROR - for expected error
47 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
48 -- FND_API.G_RET_STS_SUCCESS - for success
49 -- x_msg_count
50 -- The count of number of messages added to the message list in this call
51 -- x_msg_data
52 -- If the count is 1 then x_msg_data contains the message returned
53 --End of Comment
54 -------------------------------------------------------------------------------
55 PROCEDURE get_tolerances(p_api_version IN NUMBER,
56 p_init_msg_list IN VARCHAR2,
57 p_organization_id IN NUMBER,
58 p_change_order_type IN VARCHAR2,
59 x_tolerances_tbl IN OUT NOCOPY po_co_tolerances_grp.tolerances_tbl_type,
60 x_return_status OUT NOCOPY VARCHAR2,
61 x_msg_count OUT NOCOPY NUMBER,
62 x_msg_data OUT NOCOPY VARCHAR2) IS
63
64 l_api_name CONSTANT VARCHAR(30) := 'GET_TOLERANCES';
65 l_api_version CONSTANT NUMBER := 1.0;
66 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
67 l_progress VARCHAR2(3) := '000';
68
69
70 BEGIN
71
72 IF g_debug_stmt THEN
73 po_debug.debug_begin(l_log_head);
74 po_debug.debug_var(l_log_head, l_progress, 'p_api_version', p_api_version);
75 po_debug.debug_var(l_log_head, l_progress, 'p_init_msg_list', p_init_msg_list);
76 po_debug.debug_var(l_log_head, l_progress, 'p_organization_id', p_organization_id);
77 po_debug.debug_var(l_log_head, l_progress, 'p_change_order_type', p_change_order_type);
78 END IF;
79
80
81 -- Version check
82 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version,
83 l_api_name, g_pkg_name)
84 THEN
85 RAISE fnd_api.g_exc_unexpected_error;
86 END IF;
87
88 -- Initialize message list if p_init_msg_list is set to TRUE.
89 IF fnd_api.to_boolean(p_init_msg_list ) THEN
90 fnd_msg_pub.initialize;
91 END IF;
92
93
94 -- Initialize API return status to success
95 x_return_status := fnd_api.g_ret_sts_success;
96
97 IF(p_change_order_type = po_co_tolerances_grp.g_supp_chg_app OR
98 p_change_order_type = po_co_tolerances_grp.g_rco_req_app OR
99 p_change_order_type = po_co_tolerances_grp.g_rco_int_req_app OR
100 p_change_order_type = po_co_tolerances_grp.g_rco_buy_app OR
101 p_change_order_type = po_co_tolerances_grp.g_chg_agreements OR
102 p_change_order_type = po_co_tolerances_grp.g_chg_releases OR
103 p_change_order_type = po_co_tolerances_grp.g_chg_orders)
104 THEN
105 l_progress := '001';
106
107
108 IF g_debug_stmt THEN
109 po_debug.debug_var(l_log_head, l_progress,'Inside IF for type=', p_change_order_type);
110 END IF;
111
112
113 -- SQL What: Retrieving the tolerances
114 -- SQL Why: Need these values in performing
115 -- the tolerance check
116 SELECT cotl.tolerance_name,
117 cotl.maximum_increment,
118 cotl.maximum_decrement,
119 nvl(cotl.routing_flag, 'N')
120 BULK COLLECT INTO x_tolerances_tbl
121 FROM po_change_order_tolerances_all cotl
122 WHERE cotl.org_id = p_organization_id
123 AND cotl.change_order_type = p_change_order_type
124 ORDER BY cotl.sequence_number;
125
126 l_progress := '002';
127
128
129 IF g_debug_stmt THEN
130 po_debug.debug_var(l_log_head, l_progress,'Inside IF for type=', p_change_order_type);
131 END IF;
132
133 ELSE
134 x_return_status := fnd_api.g_ret_sts_error;
135
136 fnd_message.set_name ('PO', 'PO_TOL_INVALID_CHANGE_TYPE');
137 fnd_msg_pub.add;
138
139 -- Get message count and if 1, return message data.
140 fnd_msg_pub.count_and_get
141 (p_count => x_msg_count ,
142 p_data => x_msg_data
143 );
144
145 END IF;
146
147 IF g_debug_stmt THEN po_debug.debug_end(l_log_head); END IF;
148
149
150 EXCEPTION
151 WHEN no_data_found THEN
152 -- returns null when no data exists
153 x_return_status := fnd_api.g_ret_sts_success;
154
155 WHEN OTHERS THEN
156 x_return_status := fnd_api.g_ret_sts_unexp_error;
157 fnd_message.set_name ('PO', 'PO_UNEXPECTED_ERROR');
158 fnd_msg_pub.add;
159
160 -- Get message count and if 1, return message data.
161 fnd_msg_pub.count_and_get
162 (p_count => x_msg_count,
163 p_data => x_msg_data
164 );
165 END get_tolerances;
166
167 --<R12 REQUESTER DRIVEN PROCUREMENT END>
168
169 END;