[Home] [Help]
PACKAGE BODY: APPS.IBE_ORDER_UTIL_PVT
Source
1 PACKAGE BODY IBE_ORDER_UTIL_PVT AS
5 l_true VARCHAR2(1) := FND_API.G_TRUE;
2 /* $Header: IBEVOUTB.pls 120.6 2006/06/15 09:14:39 akhgupta noship $ */
3
4
6
7 PROCEDURE Get_Ord_Recurring_totals
8 (
9 p_header_id IN NUMBER,
10 x_charge_periodicity_code OUT NOCOPY JTF_VARCHAR2_TABLE_300,
11 x_charge_periodicity_desc OUT NOCOPY JTF_VARCHAR2_TABLE_300,
12 x_charge_periodicity_meaning OUT NOCOPY JTF_VARCHAR2_TABLE_300,
13 x_rec_subtotal OUT NOCOPY JTF_NUMBER_TABLE,
14 x_rec_tax OUT NOCOPY JTF_NUMBER_TABLE,
15 x_rec_charges OUT NOCOPY JTF_NUMBER_TABLE,
16 x_rec_total OUT NOCOPY JTF_NUMBER_TABLE,
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_msg_count OUT NOCOPY NUMBER,
19 x_msg_data OUT NOCOPY VARCHAR2
20 )
21
22 IS
23
24 l_rec_charge_tbl OE_OE_TOTALS_SUMMARY.Rec_Charges_Tbl_Type;
25
26 BEGIN
27 x_charge_periodicity_code := JTF_VARCHAR2_TABLE_300();
28 x_charge_periodicity_desc := JTF_VARCHAR2_TABLE_300();
29 x_charge_periodicity_meaning := JTF_VARCHAR2_TABLE_300();
30 x_rec_subtotal := JTF_NUMBER_TABLE();
31 x_rec_tax := JTF_NUMBER_TABLE();
32 x_rec_charges := JTF_NUMBER_TABLE();
33 x_rec_total := JTF_NUMBER_TABLE();
34
35 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
36 IBE_Util.Debug('In Get_Ord_Recurring_totals : After Initializations. Calling OE_Totals_GRP.GET_RECURRING_TOTALS');
37 END IF;
38
39 OE_Totals_GRP.GET_RECURRING_TOTALS
40 (
41 p_header_id => p_header_id,
42 x_rec_charges_tbl => l_rec_charge_tbl
43 );
44
45 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
46 IBE_Util.Debug('In Get_Ord_Recurring_totals : Returned from OE_Totals_GRP.GET_RECURRING_TOTALS');
47 END IF;
48
49 IF(l_rec_charge_tbl is not null) THEN
50
51 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
52 IBE_Util.Debug('In Get_Ord_Recurring_totals : Count :: '||l_rec_charge_tbl.count);
53 END IF;
54
55 FOR n IN l_rec_charge_tbl.first .. l_rec_charge_tbl.last LOOP
56 x_charge_periodicity_code.EXTEND();
57 x_charge_periodicity_code(n) := l_rec_charge_tbl(n).charge_periodicity_code;
58 x_charge_periodicity_desc.EXTEND();
59 x_charge_periodicity_desc(n) := l_rec_charge_tbl(n).charge_periodicity_desc;
60 x_charge_periodicity_meaning.EXTEND();
61 x_charge_periodicity_meaning(n) := l_rec_charge_tbl(n).charge_periodicity_meaning;
62 x_rec_subtotal.EXTEND();
63 x_rec_subtotal(n) := l_rec_charge_tbl(n).rec_subtotal;
64 x_rec_tax.EXTEND();
65 x_rec_tax(n) := l_rec_charge_tbl(n).rec_tax;
66 x_rec_charges.EXTEND();
67 x_rec_charges(n) := l_rec_charge_tbl(n).rec_charges;
68 x_rec_total.EXTEND();
69 x_rec_total(n) := l_rec_charge_tbl(n).rec_total;
70 END LOOP;
71 END IF;
72
73 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
74 IBE_Util.Debug('In Get_Ord_Recurring_totals : End');
75 END IF;
76
77 END Get_Ord_Recurring_totals;
78
79
80
81 PROCEDURE Get_Adjustments(
82 x_adjustment_name OUT NOCOPY JTF_VARCHAR2_TABLE_300 ,
83 x_adjustment_description OUT NOCOPY JTF_VARCHAR2_TABLE_2000 ,
84 x_list_line_no OUT NOCOPY JTF_VARCHAR2_TABLE_300 ,
85 x_adjustment_type_code OUT NOCOPY JTF_VARCHAR2_TABLE_300 ,
86 x_arithmetic_operator OUT NOCOPY JTF_VARCHAR2_TABLE_300 ,
87 x_return_status OUT NOCOPY VARCHAR2,
88 p_header_id IN NUMBER,
89 p_line_id IN NUMBER,
90 x_price_adjustment_id OUT NOCOPY jtf_number_table ,
91 x_operand OUT NOCOPY jtf_number_table ,
92 x_unit_discount_amount OUT NOCOPY jtf_number_table,
93 x_count_lines OUT NOCOPY number
94 )IS
95
96 l_adj_detail_tbl OE_Header_Adj_Util.line_adjustments_tab_type ;
97
98
99 BEGIN
100 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
101 IBE_Util.Debug('In Get_Adjustments : Start');
102 END IF;
103 x_price_adjustment_id := JTF_NUMBER_TABLE();
104 x_adjustment_name := JTF_VARCHAR2_TABLE_300();
105 x_adjustment_description := JTF_VARCHAR2_TABLE_2000();
106 x_list_line_no := JTF_VARCHAR2_TABLE_300();
107 x_adjustment_type_code := JTF_VARCHAR2_TABLE_300();
108 x_operand := JTF_NUMBER_TABLE();
109 x_arithmetic_operator := JTF_VARCHAR2_TABLE_300();
110 x_unit_discount_amount := JTF_NUMBER_TABLE();
111
112 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
113 IBE_Util.Debug('In Get_Adjustments : After Initializations. Calling oe_lineinfo_grp.Get_Adjustments');
114 END IF;
115
116 OE_LineInfo_Grp.Get_Adjustments(
117 p_header_id => p_header_id,
118 p_line_id => p_line_id,
119 x_adj_detail => l_adj_detail_tbl,
120 x_return_status => x_return_status
121 );
122
123 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
124 IBE_Util.Debug('In Get_Adjustments : After Call to oe_lineinfo_grp.Get_Adjustments');
125 END IF;
126
127 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
131 for n in l_adj_detail_tbl.first .. l_adj_detail_tbl.last LOOP
128 IBE_Util.Debug('In Get_Adjustments : Indexes of l_adj_detail_tbl Fist : '||l_adj_detail_tbl.first || ' Last : ' || l_adj_detail_tbl.last);
129 END IF;
130 x_count_lines:=l_adj_detail_tbl.count();
132 x_price_adjustment_id.EXTEND();
133 x_price_adjustment_id (n) := l_adj_detail_tbl(n).price_adjustment_id ;
134 x_adjustment_name.EXTEND();
135 x_adjustment_name (n) := l_adj_detail_tbl(n).adjustment_name ;
136 x_adjustment_description.EXTEND();
137 x_adjustment_description (n) := l_adj_detail_tbl(n).adjustment_description ;
138 x_list_line_no.EXTEND();
139 x_list_line_no(n) := l_adj_detail_tbl(n).list_line_no;
140 x_adjustment_type_code.EXTEND();
141 x_adjustment_type_code(n) := l_adj_detail_tbl(n).adjustment_type_code;
142 x_operand.EXTEND();
143 x_operand(n) := l_adj_detail_tbl(n).operand;
144 x_arithmetic_operator.EXTEND();
145 x_arithmetic_operator(n) := l_adj_detail_tbl(n).arithmetic_operator;
146 x_unit_discount_amount.EXTEND();
147 x_unit_discount_amount(n) := l_adj_detail_tbl(n).unit_discount_amount;
148 END LOOP;
149
150 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
151 IBE_Util.Debug('In Get_Adjustments : End');
152 END IF;
153 END Get_Adjustments;
154
155
156
157 -- Procedure to get whether the top models are reconfigured or not fiven a header id
158
159 -- Procedure to get whether the top models are reconfigured or not fiven a header id
160 PROCEDURE Get_MACD_Action_Mode
161 ( p_header_id IN NUMBER,
162 x_top_model_line_id OUT NOCOPY JTF_NUMBER_TABLE,
163 x_line_is_reconfig OUT NOCOPY JTF_VARCHAR2_TABLE_100,
164 x_return_status OUT NOCOPY VARCHAR2
165 )IS
166 -- Define the variables
167 l_top_model_line_id NUMBER;
168 l_line_id number;
169 l_config_mode NUMBER;
170 l_return_status VARCHAR2(1);
171 l_index Number := 1;
172
173
174 -- Find all the top model line ids of type container.
175
176 CURSOR c_top_model(c_header_id NUMBER ) IS
177 SELECT oel.line_id, oel.top_model_line_id
178 FROM oe_order_lines_all oel
179 WHERE oel.top_model_line_id = oel.line_id
180 AND oel.link_to_line_id is null
181 AND oel.header_id = c_header_id;
182
183 BEGIN
184
185 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
186 IBE_Util.Debug('In Get_MACD_Action_Mode : Start');
187 END IF;
188
189 x_top_model_line_id := JTF_NUMBER_TABLE();
190 x_line_is_reconfig := JTF_VARCHAR2_TABLE_100();
191
192 OPEN c_top_model(P_header_id);
193 LOOP
194 FETCH c_top_model INTO l_line_id ,l_top_model_line_id;
195
196 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
197 IBE_Util.Debug('In Get_MACD_Action_Mode : l_top_model_line_id:' ||l_top_model_line_id);
198 END IF;
199
200
201 EXIT WHEN c_top_model%NOTFOUND;
202
203 /*Call the OE procedure to check that the line is reconfigured or not
204 If the value of l_config_mode in (2,3 4) , the model is reconfigred. */
205
206 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
207 IBE_Util.Debug('In Get_MACD_Action_Mode : After Initializations. Calling OE_CONFIG_TSO_GRP.Get_MACD_Action_Mode');
208 END IF;
209
210
211
212
213 OE_CONFIG_TSO_GRP.Get_MACD_Action_Mode
214 ( p_line_id => l_line_id,
215 p_top_model_line_id => l_top_model_line_id ,
216 x_config_mode => l_config_mode,
217 x_return_status => l_return_status
218 );
219
220 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
221 IBE_Util.Debug('In Get_MACD_Action_Mode : After Calling OE_CONFIG_TSO_GRP.Get_MACD_Action_Mode');
222 END IF;
223
224
225
226 -- if the l_config_mode = 2,3, 4 then the line is a reconfigured one
227
228 if (l_config_mode IS NOT NULL) AND (l_config_mode IN (2,3, 4) ) then
229
230 x_top_model_line_id.extend();
231 x_top_model_line_id(l_index) := l_top_model_line_id;
232 x_line_is_reconfig.extend();
233 x_line_is_reconfig(l_index) := 'Y' ;
234
235
236 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
237 IBE_Util.Debug('In Get_MACD_Action_Mode : x_top_model_line_id(l_index) :' || x_top_model_line_id(l_index) );
238 IBE_Util.Debug('In Get_MACD_Action_Mode : x_line_is_reconfig(l_index) :' || x_line_is_reconfig(l_index) );
239 END IF;
240
241 l_index := l_index +1;
242
243 end if;
244
245
246
247
248 END LOOP;
249 CLOSE c_top_model;
250
251 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
252 IBE_Util.Debug('In Get_Adjustments : End');
253 END IF;
254
255 END Get_MACD_Action_Mode;
256
257
258
259 -- Function to retrieve phone and email given a party id
260
261 FUNCTION GET_PHONE_EMAIL
262 ( p_party_id IN NUMBER
263 ) RETURN VARCHAR2
264 IS
265
266 l_api_name CONSTANT VARCHAR2(30):= 'Get_Phone_Email';
267
268 CURSOR c_contact_details(c_party_id number) IS
269 select phone_country_code,phone_area_code,phone_number,phone_extension,
270 contact_point_type, primary_flag,phone_line_type,
271 email_address
272 from hz_contact_points
273 where contact_point_type in ('PHONE','EMAIL') and
274 NVL(status, 'A') = 'A'
275 and owner_table_name = 'HZ_PARTIES'
276 and owner_table_id = c_party_id
277 and primary_flag='Y';
278
279 lc_contact_details c_contact_details%rowtype;
280 l_cntct_phone_country_code VARCHAR2(20) := '';
281 l_cntct_phone_area_code VARCHAR2(20) := '';
282 l_cntct_phone_number VARCHAR2(20) := '';
283 l_cntct_phone_extn VARCHAR2(20) := '';
284 L_CNTCT_PHONE VARCHAR2(100) := '';
285 l_cntct_email VARCHAR2(1000) := '';
286 BEGIN
287
288 -- FETCH EMAIL and Phone
289 if p_party_id is null then
290 l_cntct_phone := '';
291 l_cntct_email := '';
292 else
293 open c_contact_details(p_party_id);
294 loop
295 fetch c_contact_details into lc_contact_details;
296 Exit When c_contact_details%notfound;
297 IF lc_contact_details.CONTACT_POINT_TYPE = 'EMAIL' THEN
298 l_cntct_email := lc_contact_details.EMAIL_ADDRESS;
299 ELSE
300 IF (lc_contact_details.CONTACT_POINT_TYPE = 'PHONE') THEN
301 l_cntct_phone := IBE_UTIL.format_phone(lc_contact_details.PHONE_COUNTRY_CODE,lc_contact_details.PHONE_AREA_CODE,
302 lc_contact_details.PHONE_NUMBER,lc_contact_details.PHONE_EXTENSION );
303 END IF;
304 END IF;
305 end loop;
306 close c_contact_details;
307 end if;
308
309 -- Concatenate phone and email with a delimiter parse it in jsp
310 RETURN (l_cntct_phone || '[({})]' || l_cntct_email );
311
312 EXCEPTION
313 WHEN NO_DATA_FOUND THEN
314 RETURN '';
315 WHEN OTHERS THEN
316 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
317 END Get_Phone_Email;
318
319
320 /* The following procudure verifies whether the last_updated_date passed is older than the
321 last_updated_date present in the DB */
322
323
324 PROCEDURE validate_last_update_date
325 (p_header_id IN NUMBER,
326 p_last_update_date IN DATE,
327 x_is_diff_last_update OUT NOCOPY VARCHAR2)
328
329 IS
330 CURSOR c_getLastUpdatedDate(p_header_id NUMBER) IS
331 SELECT last_update_date FROM oe_order_headers_all WHERE header_id = p_header_id;
332
333
334 l_getLastUpdatedDate c_getLastUpdatedDate%rowtype;
335 l_is_diff_last_update VARCHAR2(1) := 'F';
336
337
338 BEGIN
339 IBE_Util.Debug('In validate_last_update_date : start' );
340
341 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
342 IBE_Util.Debug('In validate_last_update_date : After Initializations');
343 END IF;
344
345 --
346 OPEN c_getLastUpdatedDate(p_header_id);
347 FETCH c_getLastUpdatedDate INTO l_getLastUpdatedDate;
348 IF (c_getLastUpdatedDate%NOTFOUND) THEN
349 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
350 IBE_Util.Debug('No order with this header id, it is cancelled');
351 END IF;
352 l_is_diff_last_update := 'T';
353 ELSIF (l_getLastUpdatedDate.last_update_date > p_last_update_date) THEN
354 l_is_diff_last_update := 'T';
355 ELSE
356 l_is_diff_last_update := 'F';
357 END IF;
358 CLOSE c_getLastUpdatedDate;
359
360 x_is_diff_last_update := l_is_diff_last_update;
361 IBE_Util.Debug('In validate_last_update_date : End' || x_is_diff_last_update );
362
363 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
364 IBE_Util.Debug('In validate_last_update_date : End');
365 END IF;
366
367 END validate_last_update_date;
368
369
370 END IBE_ORDER_UTIL_PVT;