[Home] [Help]
PACKAGE BODY: APPS.INL_LANDEDCOST_PUB
Source
1 PACKAGE BODY INL_LANDEDCOST_PUB AS
2 /* $Header: INLPLCOB.pls 120.6.12010000.3 2009/01/13 17:22:02 aicosta ship $ */
3
4 -- API name : Get_LandedCost
5 -- Type : Public
6 -- Function :
7 -- Pre-reqs : None
8 -- Parameters :
9 -- IN : p_api_version IN NUMBER Required
10 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
11 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
12 -- p_ship_line_id IN NUMBER Required
13 --
14 -- OUT : x_return_status OUT NOCOPY VARCHAR2
15 -- x_msg_count OUT NOCOPY NUMBER
16 -- x_msg_data OUT NOCOPY VARCHAR2
17 -- x_organization_id OUT NOCOPY NUMBER,
18 -- x_inventory_item_id OUT NOCOPY NUMBER,
19 -- x_primary_qty OUT NOCOPY NUMBER,
20 -- x_primary_uom_code OUT NOCOPY VARCHAR2,
21 -- x_estimated_item_price OUT NOCOPY NUMBER,
22 -- x_estimated_charges OUT NOCOPY NUMBER,
23 -- x_estimated_taxes OUT NOCOPY NUMBER,
24 -- x_estimated_unit_landed_cost OUT NOCOPY NUMBER,
25 -- x_actual_item_price OUT NOCOPY NUMBER,
26 -- x_actual_charges OUT NOCOPY NUMBER,
27 -- x_actual_taxes OUT NOCOPY NUMBER,
28 -- x_actual_unit_landed_cost OUT NOCOPY NUMBER
29 --
30 -- Version : Current version 1.0
31 --
32 -- Notes :
33 PROCEDURE Get_LandedCost(
34 p_api_version IN NUMBER,
35 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
36 p_commit IN VARCHAR2 := FND_API.G_FALSE,
37 p_ship_line_id IN NUMBER,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2,
41 x_organization_id OUT NOCOPY NUMBER,
42 x_inventory_item_id OUT NOCOPY NUMBER,
43 x_primary_qty OUT NOCOPY NUMBER,
44 x_primary_uom_code OUT NOCOPY VARCHAR2,
45 x_estimated_item_price OUT NOCOPY NUMBER,
46 x_estimated_charges OUT NOCOPY NUMBER,
47 x_estimated_taxes OUT NOCOPY NUMBER,
48 x_estimated_unit_landed_cost OUT NOCOPY NUMBER,
49 x_actual_item_price OUT NOCOPY NUMBER,
50 x_actual_charges OUT NOCOPY NUMBER,
51 x_actual_taxes OUT NOCOPY NUMBER,
52 x_actual_unit_landed_cost OUT NOCOPY NUMBER
53 ) IS
54
55 l_api_name CONSTANT VARCHAR2(30) := 'Get_LandedCost';
56 l_api_version CONSTANT NUMBER := 1.0;
57
58 l_return_status VARCHAR2(1);
59 l_msg_count NUMBER;
60 l_msg_data VARCHAR2(2000);
61 l_debug_info VARCHAR2(200);
62 l_parent_ship_line_id NUMBER;
63 BEGIN
64
65 -- Standard Beginning of Procedure/Function Logging
66 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
67 p_procedure_name => l_api_name);
68 -- Standard Start of API savepoint
69 SAVEPOINT Get_LandedCost_PVT;
70
71 -- Initialize message list if p_init_msg_list is set to TRUE.
72 IF FND_API.to_Boolean( p_init_msg_list ) THEN
73 FND_MSG_PUB.initialize;
74 END IF;
75
76 -- Check for call compatibility.
77 IF NOT FND_API.Compatible_API_Call (
78 l_api_version,
79 p_api_version,
80 l_api_name,
81 g_pkg_name)
82 THEN
83 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
84 END IF;
85
86 -- Initialize API return status to success
87 x_return_status := FND_API.G_RET_STS_SUCCESS;
88
89 -- Api Body
90 BEGIN
91 -- Standard Statement Level Procedure/Function Logging
92 l_debug_info := 'Get estimated landed cost for the ship_line_id: ' || p_ship_line_id;
93 INL_LOGGING_PVT.Log_Statement (
94 p_module_name => g_module_name,
95 p_procedure_name => l_api_name,
96 p_debug_info => l_debug_info
97 );
98
99 SELECT NVL(parent_ship_line_id, ship_line_id)
100 INTO l_parent_ship_line_id
101 FROM inl_ship_lines
102 WHERE ship_line_id = p_ship_line_id;
103
104 SELECT olcv.organization_id,
105 olcv.inventory_item_id,
106 olcv.primary_qty,
107 olcv.primary_uom_code,
108 olcv.item_price,
109 olcv.charges,
110 olcv.taxes,
111 olcv.unit_landed_cost
112 INTO x_organization_id,
113 x_inventory_item_id,
114 x_primary_qty,
115 x_primary_uom_code,
116 x_estimated_item_price,
117 x_estimated_charges,
118 x_estimated_taxes,
119 x_estimated_unit_landed_cost
120 FROM inl_shipln_landed_costs_v olcv
121 WHERE olcv.ship_line_id = l_parent_ship_line_id
122 AND olcv.adjustment_num = 0;
123
124 -- Standard Statement Level Procedure/Function Logging
125 l_debug_info := 'Get actual landed cost for the ship_line_id: ' || p_ship_line_id;
126 INL_LOGGING_PVT.Log_Statement (
127 p_module_name => g_module_name,
128 p_procedure_name => l_api_name,
129 p_debug_info => l_debug_info
130 );
131
132 SELECT olcv.item_price,
133 olcv.charges,
134 olcv.taxes,
135 olcv.unit_landed_cost
136 INTO x_actual_item_price,
137 x_actual_charges,
138 x_actual_taxes,
139 x_actual_unit_landed_cost
140 FROM inl_shipln_landed_costs_v olcv
141 WHERE olcv.ship_line_id = (SELECT asl.ship_line_id
142 FROM inl_adj_ship_lines_v asl,
143 inl_ship_lines sl
144 WHERE asl.ship_header_id = sl.ship_header_id
145 AND asl.ship_line_group_id = sl.ship_line_group_id -- BUG 7702229
146 AND asl.ship_line_num = sl.ship_line_num
147 AND sl.ship_line_id = l_parent_ship_line_id)
148 AND olcv.adjustment_num = (SELECT MAX(adjustment_num)
149 FROM INL_shipln_landed_costs_v olcv2
150 WHERE olcv2.ship_line_id = olcv.ship_line_id);
151
152 END;
153 -- End of Api Body
154
155 -- Standard check of p_commit.
156 IF FND_API.To_Boolean( p_commit ) THEN
157 COMMIT WORK;
158 END IF;
159
160 -- Standard call to get message count and if count is 1, get message info.
161 FND_MSG_PUB.Count_And_Get(
162 p_encoded => FND_API.g_false,
163 p_count => x_msg_count,
164 p_data => x_msg_data);
165
166 -- Standard End of Procedure/Function Logging
167 INL_LOGGING_PVT.Log_EndProc (
168 p_module_name => g_module_name,
169 p_procedure_name => l_api_name
170 );
171 EXCEPTION
172 WHEN FND_API.G_EXC_ERROR THEN
173 -- Standard Expected Error Logging
174 INL_LOGGING_PVT.Log_ExpecError (
175 p_module_name => g_module_name,
176 p_procedure_name => l_api_name
177 );
178 ROLLBACK TO Get_LandedCost_PVT;
179 x_return_status := FND_API.G_RET_STS_ERROR;
180 FND_MSG_PUB.Count_And_Get(
181 p_encoded => FND_API.g_false,
182 p_count => x_msg_count,
183 p_data => x_msg_data
184 );
185 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
186 -- Standard Unexpected Error Logging
187 INL_LOGGING_PVT.Log_UnexpecError (
188 p_module_name => g_module_name,
189 p_procedure_name => l_api_name);
190 ROLLBACK TO Get_LandedCost_PVT;
191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
192 FND_MSG_PUB.Count_And_Get(
193 p_encoded => FND_API.g_false,
194 p_count => x_msg_count,
195 p_data => x_msg_data
196 );
197 WHEN OTHERS THEN
198 -- Standard Unexpected Error Logging
199 INL_LOGGING_PVT.Log_UnexpecError (
200 p_module_name => g_module_name,
201 p_procedure_name => l_api_name
202 );
203 ROLLBACK TO Get_LandedCost_PVT;
204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
205 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
206 THEN
207 FND_MSG_PUB.Add_Exc_Msg(
208 g_pkg_name,
209 l_api_name
210 );
211 END IF;
212 FND_MSG_PUB.Count_And_Get(
213 p_encoded => FND_API.g_false,
214 p_count => x_msg_count,
215 p_data => x_msg_data
216 );
217 END Get_LandedCost;
218
219 END INL_LANDEDCOST_PUB;