[Home] [Help]
PACKAGE BODY: APPS.ASO_SHIPPING_INT
Source
1 PACKAGE BODY ASO_SHIPPING_INT as
2 /* $Header: asoishpb.pls 120.3 2006/02/07 11:53:58 skulkarn ship $ */
3 -- Start of Comments
4 -- Package name : ASO_SHIPPING_INT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_SHIPPING_INT';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asoishpb.pls';
12
13 --wli_start
14 FUNCTION Get_Total_Freight_Charges(p_qte_header_id NUMBER)
15 RETURN NUMBER
16 IS
17 l_index NUMBER := 0;
18 l_total_freight_charge NUMBER := 0;
19
20 CURSOR header_freight_cursor(l_quote_header_id NUMBER) IS
21 SELECT APA.operand ,
22 APA.arithmetic_operator
23 FROM aso_price_adjustments APA
24 WHERE APA.modifier_line_type_code = 'FREIGHT_CHARGE'
25 AND APA.quote_header_id = l_quote_header_id
26 AND APA.quote_line_id IS NULL
27 AND APA.APPLIED_FLAG = 'Y';
28
29 CURSOR line_freight_cursor(l_quote_header_id NUMBER) IS
30 SELECT APA.operand ,
31 APA.arithmetic_operator,
32 AQLA.quantity ,
33 AQLA.line_list_price
34 FROM aso_price_adjustments APA,
35 aso_quote_lines_all AQLA
36 WHERE APA.modifier_line_type_code = 'FREIGHT_CHARGE'
37 AND APA.quote_header_id = l_quote_header_id
38 -- AND APA.quote_header_id = AQLA.quote_header_id
39 AND APA.quote_line_id = AQLA.quote_line_id
40 AND APA.APPLIED_FLAG = 'Y';
41 BEGIN
42
43 FOR header_freight IN header_freight_cursor(p_qte_header_id) LOOP
44 IF header_freight.arithmetic_operator = 'LUMPSUM' THEN
45 l_total_freight_charge := l_total_freight_charge + header_freight.operand;
46 END IF;
47 END LOOP;
48
49 FOR line_freight IN line_freight_cursor(p_qte_header_id) LOOP
50 IF line_freight.arithmetic_operator = '%' THEN
51 l_total_freight_charge := l_total_freight_charge + line_freight.line_list_price * line_freight.operand * line_freight.quantity/100.0;
52 END IF;
53
54 IF line_freight.arithmetic_operator = 'AMT' THEN
55 l_total_freight_charge := l_total_freight_charge + line_freight.operand * line_freight.quantity;
56 END IF;
57
58 IF line_freight.arithmetic_operator = 'LUMPSUM' THEN
59 l_total_freight_charge := l_total_freight_charge + line_freight.operand;
60 END IF;
61 END LOOP;
62
63 RETURN l_total_freight_charge;
64 END Get_Total_Freight_Charges;
65
66
67 FUNCTION Get_line_Freight_charges(
68 p_qte_header_id NUMBER := FND_API.G_MISS_NUM
69 ,p_qte_line_id NUMBER := FND_API.G_MISS_NUM )
70 RETURN number
71 is
72
73 l_operand number;
74 l_arithmetic_operator varchar2(20);
75 l_quantity number;
76 l_line_list_price number;
77 l_adjusted_amount NUMBER;
78
79 cursor c_line_charge(l_qte_header_id number, l_qte_line_id number ) is
80 select p.operand, p.arithmetic_operator, l.quantity, l.line_list_price,p.ADJUSTED_AMOUNT
81 from aso_price_adjustments p, aso_quote_lines_all l
82 where p.modifier_line_type_code = 'FREIGHT_CHARGE'
83 and p.quote_line_id = l.quote_line_id
84 and p.quote_header_id = l_qte_header_id
85 and p.quote_line_id = l_qte_line_id
86 AND p.APPLIED_FLAG = 'Y';
87
88
89 l_index number:=0;
90 l_charge_amount number :=0;
91 begin
92
93 open c_line_charge(p_qte_header_id, p_qte_line_id);
94 loop
95 fetch c_line_charge
96 into l_operand, l_arithmetic_operator, l_quantity, l_line_list_price, l_adjusted_amount;
97 exit when c_line_charge%notfound;
98 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
99 aso_debug_pub.add('ASO_SHIPPING_INT:c_line_charge - l_quantity:'||l_quantity,1,'Y');
100 aso_debug_pub.add('ASO_SHIPPING_INT:c_line_charge - l_line_list_price:'||l_line_list_price,1,'Y');
101 aso_debug_pub.add('ASO_SHIPPING_INT:c_line_charge - l_adjusted_amount:'||l_adjusted_amount,1,'Y');
102 aso_debug_pub.add('ASO_SHIPPING_INT:c_line_charge - l_arithmetic_operator:'||l_arithmetic_operator,1,'Y');
103 aso_debug_pub.add('ASO_SHIPPING_INT:c_line_charge - l_operand:'||l_operand,1,'Y');
104 END IF;
105
106 if (l_arithmetic_operator = '%') then
107 l_charge_amount := l_charge_amount + (l_adjusted_amount*l_quantity) ;
108 end if;
109
110 if (l_arithmetic_operator = 'AMT' ) then
111 l_charge_amount := l_charge_amount + l_operand*l_quantity;
112 end if;
113
114 if (l_arithmetic_operator = 'LUMPSUM' ) then
115 l_charge_amount := l_charge_amount + l_operand;
116 end if;
117
118 end loop;
119 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
120 aso_debug_pub.add('ASO_SHIPPING_INT:c_line_charge - l_charge_amount:'||NVL(l_charge_amount,0),1,'Y');
121 END IF;
122 return nvl(l_charge_amount,0);
123 end get_line_freight_charges;
124 --wli_end
125
126
127
128
129
130 PROCEDURE Calculate_Freight_Charges(
131 P_Api_Version_Number IN NUMBER,
132 P_Charge_Control_Rec IN Charge_Control_Rec_Type
133 := G_Miss_Charge_Control_Rec,
134 P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type
135 := ASO_QUOTE_PUB.G_Miss_Qte_Header_Rec,
136 P_Qte_Line_Rec IN ASO_QUOTE_PUB.Qte_Line_Rec_Type
137 := ASO_QUOTE_PUB.G_Miss_Qte_Line_Rec,
138 P_Shipment_Tbl IN ASO_QUOTE_PUB.Shipment_Tbl_Type
139 := ASO_QUOTE_PUB.G_MISS_SHIPMENT_TBL,
140 x_Freight_Charge_Tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Freight_Charge_Tbl_Type,
141 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
142 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
143 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
144 IS
145 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_Freight_Charges';
146 l_freight_charge_rec ASO_QUOTE_PUB.Freight_Charge_Rec_Type;
147
148 BEGIN
149 x_return_status := FND_API.G_RET_STS_SUCCESS;
150
151 FOR i IN 1..P_Shipment_Tbl.count LOOP
152 l_freight_charge_rec.operation_code := 'CREATE';
153 l_freight_charge_rec.QUOTE_LINE_ID := P_Qte_Line_Rec.quote_line_id;
154 l_freight_charge_rec.QUOTE_SHIPMENT_ID := P_Shipment_Tbl(i).shipment_id;
155 l_freight_charge_rec.SHIPMENT_INDEX := i;
156 l_freight_charge_rec.FREIGHT_CHARGE_TYPE_ID := -1;
157 l_freight_charge_rec.CHARGE_AMOUNT := 0;
158 x_Freight_Charge_Tbl(x_Freight_Charge_Tbl.count+1) := l_freight_charge_rec;
159 END LOOP;
160
161 -- Standard call to get message count and if count is 1, get message info.
162 FND_MSG_PUB.Count_And_Get
163 ( p_count => x_msg_count,
164 p_data => x_msg_data
165 );
166
167 EXCEPTION
168 WHEN FND_API.G_EXC_ERROR THEN
169 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
170 P_API_NAME => L_API_NAME
171 ,P_PKG_NAME => G_PKG_NAME
172 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
173 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
174 ,X_MSG_COUNT => X_MSG_COUNT
175 ,X_MSG_DATA => X_MSG_DATA
176 ,X_RETURN_STATUS => X_RETURN_STATUS);
177
178 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
179 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
180 P_API_NAME => L_API_NAME
181 ,P_PKG_NAME => G_PKG_NAME
182 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
183 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
184 ,X_MSG_COUNT => X_MSG_COUNT
185 ,X_MSG_DATA => X_MSG_DATA
186 ,X_RETURN_STATUS => X_RETURN_STATUS);
187
188 WHEN OTHERS THEN
189 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
190 P_API_NAME => L_API_NAME
191 ,P_PKG_NAME => G_PKG_NAME
192 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
193 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
194 ,X_MSG_COUNT => X_MSG_COUNT
195 ,X_MSG_DATA => X_MSG_DATA
196 ,X_RETURN_STATUS => X_RETURN_STATUS);
197
198 END Calculate_Freight_Charges;
199
200 FUNCTION Get_Header_Freight_Charges(p_qte_header_id NUMBER)
201 RETURN NUMBER
202 IS
203 l_index NUMBER := 0;
204 l_total_freight_charge NUMBER := 0;
205
206 CURSOR header_freight_cursor(l_quote_header_id NUMBER) IS
207 SELECT APA.operand ,
208 APA.arithmetic_operator
209 FROM aso_price_adjustments APA
210 WHERE APA.modifier_line_type_code = 'FREIGHT_CHARGE'
211 AND APA.quote_header_id = l_quote_header_id
212 AND APA.quote_line_id IS NULL
213 AND APA.APPLIED_FLAG = 'Y';
214
215
216 BEGIN
217
218 FOR header_freight IN header_freight_cursor(p_qte_header_id) LOOP
219 IF header_freight.arithmetic_operator = 'LUMPSUM' THEN
220 l_total_freight_charge := l_total_freight_charge + header_freight.operand;
221 END IF;
222 END LOOP;
223
224
225
226 RETURN nvl(l_total_freight_charge,0);
227 END Get_Header_Freight_Charges;
228
229
230 End ASO_SHIPPING_INT;