1 PACKAGE BODY pmi_sales_pkg as
2 /* $Header: PMISAANB.pls 115.5 2003/02/19 04:47:42 srpuri ship $ */
3
4 /* Added New function SKARIMIS 12/28/1999 */
5
6 FUNCTION PMISA_GET_TOP_N(p_Sales_orgn IN sy_orgn_mst.orgn_code%TYPE,
7 p_cost_mthd IN cm_cmpt_dtl.cost_mthd_code%TYPE,
8 p_Prd_start_date IN DATE,
9 p_Prd_end_date IN DATE,
10 p_item_id IN ic_item_mst.item_id%TYPE DEFAULT NULL,
11 P_customer_id IN op_ordr_hdr.billcust_id%TYPE DEFAULT NULL,
12 p_top_n IN NUMBER
13 )
14 RETURN NUMBER
15 IS
16 /* cursor to Fetch Top products with respect to margin */
17
18 Cursor cur_item ( p_Sales_orgn IN sy_orgn_mst.orgn_code%TYPE,
19 p_cost_mthd IN cm_cmpt_dtl.cost_mthd_code%TYPE,
20 p_Prd_start_date IN DATE,
21 p_Prd_end_date IN DATE
22 )
23 IS
24 SELECT orderdetail.item_id,
25 SUM(DECODE(orderdetail.BASE_CURRENCY,orderdetail.BILLING_CURRENCY,
26 orderdetail.EXTENDED_PRICE,
27 Decode(orderdetail.mul_div_sign,0,orderdetail.EXTENDED_PRICE*orderdetail.EXCHANGE_RATE,
28 orderdetail.EXTENDED_PRICE/orderdetail.EXCHANGE_RATE)))-
29 SUM(gmicuom.I2UOM_CV(orderdetail.Item_Id,0,orderdetail.ORDER_UM1,
30 orderdetail.ORDER_QTY1,itemmst.Item_UM)*
31 pmi_common_pkg.PMICO_GET_COST(orderdetail.item_id,
32 orderdetail.From_whse,p_cost_mthd,orderdetail.Actual_Shipdate))-
33 SUM(pmi_sales_pkg.PMISA_GET_CHARGE(orderdetail.order_Id,orderdetail.line_Id,
34 orderdetail.extended_price,orderdetail.billing_currency,orderdetail.base_currency,
35 orderdetail.exchange_rate,orderdetail.mul_div_sign)) Margin
36 FROM
37 OP_ORDR_HDR orderhdr,
38 OP_ORDR_DTL orderdetail,
39 IC_ITEM_MST itemmst
40 WHERE orderhdr.order_id = orderdetail.order_id
41 AND orderdetail.item_id = itemmst.item_id
42 AND trunc(orderdetail.ACTUAL_SHIPDATE)
43 between p_prd_start_date and p_prd_end_date
44 AND orderhdr.ORGN_CODE=p_Sales_orgn
45 GROUP BY orderhdr.orgn_code,orderdetail.item_id
46 ORDER BY Margin desc;
47 /* Cursor to get Top N customers with respect to Margin */
48 Cursor cur_customer ( p_Sales_orgn IN sy_orgn_mst.orgn_code%TYPE,
49 p_cost_mthd IN cm_cmpt_dtl.cost_mthd_code%TYPE,
50 p_Prd_start_date IN DATE,
51 p_Prd_end_date IN DATE
52 )
53 IS
54 SELECT orderhdr.billcust_id,
55 SUM(DECODE(orderdetail.BASE_CURRENCY,orderdetail.BILLING_CURRENCY,
56 orderdetail.EXTENDED_PRICE,
57 Decode(orderdetail.mul_div_sign,0,orderdetail.EXTENDED_PRICE*orderdetail.EXCHANGE_RATE,
58 orderdetail.EXTENDED_PRICE/orderdetail.EXCHANGE_RATE)))-
59 SUM(gmicuom.I2UOM_CV(orderdetail.Item_Id,0,orderdetail.ORDER_UM1,
60 orderdetail.ORDER_QTY1,itemmst.Item_UM)*
61 pmi_common_pkg.PMICO_GET_COST(orderdetail.item_id,
62 orderdetail.From_whse,p_cost_mthd,orderdetail.Actual_Shipdate))-
63 SUM(pmi_sales_pkg.PMISA_GET_CHARGE(orderdetail.order_Id,orderdetail.line_Id,
64 orderdetail.extended_price,orderdetail.billing_currency,orderdetail.base_currency,
65 orderdetail.exchange_rate,orderdetail.mul_div_sign)) Margin
66 FROM
67 OP_ORDR_HDR orderhdr,
68 OP_ORDR_DTL orderdetail,
69 IC_ITEM_MST itemmst
70 WHERE orderhdr.order_id = orderdetail.order_id
71 AND orderdetail.item_id = itemmst.item_id
72 AND trunc(orderdetail.ACTUAL_SHIPDATE)
73 between p_prd_start_date and p_prd_end_date
74 AND orderhdr.ORGN_CODE=p_Sales_orgn
75 GROUP BY orderhdr.orgn_code,orderhdr.billcust_id
76 ORDER BY Margin desc;
77 l_count NUMBER:=1;
78 l_found NUMBER:=0;
79 l_item_id NUMBER;
80 l_customer_id NUMBER;
81 l_margin NUMBER;
82 BEGIN
83 /* Check Package varables if already top N is calculated for the given combination */
84
85 /* If Item id is passed check Item Table */
86 IF P_CUSTOMER_ID is NULL THEN
87 /* If already Top N were Calculated for the given parameters
88 then check respective Package tables for given item-Customer id */
89 IF (Pv_pmisa_tp_Sales_orgn = p_sales_orgn AND Pv_pmisa_tp_cost_mthd = p_cost_mthd AND
90 Pv_pmisa_tp_Prd_start_dt = p_Prd_start_date AND Pv_pmisa_tp_Prd_end_dt = p_Prd_end_date AND
91 Pv_pmisa_tp_top_n = p_top_n ) THEN
92 LOOP
93 IF pv_pmisa_top_n_item(l_count) = p_item_id THEN
94 return 1;
95 END IF;
96 l_count := l_count+1;
97 EXIT WHEN l_count > p_top_n;
98 END LOOP;
99 return 0;
100 ELSE
101 /* If Top N is not calculated for the given parameters , Start Calculating the TOP N and
102 Populate corresponding tables */
103 /* assign new passed values to Package Variables */
104 Pv_pmisa_tp_Sales_orgn := p_sales_orgn;
105 Pv_pmisa_tp_cost_mthd := p_cost_mthd;
106 Pv_pmisa_tp_Prd_start_dt := p_Prd_start_date;
107 Pv_pmisa_tp_Prd_end_dt := p_Prd_end_date;
108 Pv_pmisa_tp_top_n := p_top_n ;
109 /* If Top N item need to be calculated, Empty the previous constructed table */
110 pv_pmisa_top_n_item.DELETE;
111 OPEN cur_item(p_Sales_orgn,p_cost_mthd,p_Prd_start_date,p_Prd_end_date);
112 LOOP
113 FETCH cur_item INTO l_item_id,l_margin;
114 EXIT WHEN cur_item%NOTFOUND;
115 EXIT WHEN l_count > p_top_n;
116 IF l_item_id = p_item_id THEN
117 /*Flag to check if this list contains the passed item */
118 l_found := 1;
119 END IF;
120 pv_pmisa_top_n_item(l_count):= l_item_id;
121 l_count := l_count+1;
122 END LOOP;
123 CLOSE cur_item;
124 IF l_found=1 THEN
125 return 1;
126 ELSE
127 return 0;
128 END IF;
129 END IF;
130
131 ELSIF P_ITEM_ID is NULL THEN /* If customer id is passed check Customer Table */
132
133 /* If already Top N were Calculated for the given parameters
134 then check respective Package tables for given item-Customer id */
135 IF (Pv_pmisa_tp_cust_Sales_orgn = p_sales_orgn AND Pv_pmisa_tp_cust_cost_mthd = p_cost_mthd AND
136 Pv_pmisa_tp_cust_Prd_start_dt = p_Prd_start_date AND Pv_pmisa_tp_cust_Prd_end_dt = p_Prd_end_date AND
137 Pv_pmisa_tp_cust_top_n = p_top_n ) THEN
138
139 LOOP
140 IF pv_pmisa_top_n_cust(l_count) = p_customer_id THEN
141 return 1;
142 END IF;
143 l_count := l_count+1;
144 EXIT WHEN l_count > p_top_n;
145 END LOOP;
146 return 0;
147
148 ELSE
149 /* If Top N is not calculated for the given parameters , Start Calculating the TOP N and
150 Populate corresponding tables */
151 /* assign new passed values to Package Variables */
152 Pv_pmisa_tp_cust_Sales_orgn := p_sales_orgn;
153 Pv_pmisa_tp_cust_cost_mthd := p_cost_mthd;
154 Pv_pmisa_tp_cust_Prd_start_dt := p_Prd_start_date;
155 Pv_pmisa_tp_cust_Prd_end_dt := p_Prd_end_date;
156 Pv_pmisa_tp_cust_top_n := p_top_n ;
157
158 /* If Top N Customers need to be calculated, Empty the previous constructed table */
159
160 pv_pmisa_top_n_cust.DELETE;
161 OPEN cur_customer(p_Sales_orgn,p_cost_mthd,p_Prd_start_date,p_Prd_end_date);
162 LOOP
163 FETCH cur_customer INTO l_customer_id,l_margin;
164 EXIT WHEN cur_customer%NOTFOUND;
165 EXIT WHEN l_count > p_top_n;
166 IF l_customer_id = p_customer_id THEN
167 /*Flag to check if this list contains the passed Customer */
168 l_found := 1;
169 END IF;
170 pv_pmisa_top_n_cust(l_count):= l_customer_id;
171 l_count := l_count+1;
172 END LOOP;
173 CLOSE cur_customer;
174 IF l_found=1 THEN
175 return 1;
176 ELSE
177 return 0;
178 END IF;
179 END IF;
180 END IF;
181 END PMISA_GET_TOP_N;
182
183 FUNCTION PMISA_GET_TOP_N_BY_OU(p_OU_ID IN hr_operating_units.ORGANIZATION_ID%TYPE,
184 p_cost_mthd IN cm_cmpt_dtl.cost_mthd_code%TYPE,
185 p_Prd_start_date IN DATE,
186 p_Prd_end_date IN DATE,
187 p_item_id IN ic_item_mst.item_id%TYPE DEFAULT NULL,
188 P_customer_id IN hz_parties.PARTY_ID%TYPE DEFAULT NULL,
189 p_top_n IN NUMBER
190 )
191 RETURN NUMBER
192 IS
193 /* cursor to Fetch Top products with respect to margin */
194
195 Cursor cur_item ( p_OU_ID IN hr_operating_units.ORGANIZATION_ID%TYPE,
196 p_cost_mthd IN cm_cmpt_dtl.cost_mthd_code%TYPE,
197 p_Prd_start_date IN DATE,
198 p_Prd_end_date IN DATE
199 )
200 IS
201 SELECT item_id, SUM(line_margin) margin
202 FROM
203 (
204 SELECT orderdetail.item_id,
205 DECODE(orderdetail.BASE_CURRENCY,orderdetail.BILLING_CURRENCY,
206 orderdetail.EXTENDED_PRICE,
207 Decode(orderdetail.mul_div_sign,0,orderdetail.EXTENDED_PRICE*orderdetail.EXCHANGE_RATE,
208 orderdetail.EXTENDED_PRICE/orderdetail.EXCHANGE_RATE))
209 -
210 gmicuom.I2UOM_CV(orderdetail.Item_Id,0,orderdetail.ORDER_UM1,
211 orderdetail.ORDER_QTY1,itemmst.Item_UM)*
212 pmi_common_pkg.PMICO_GET_COST(orderdetail.item_id,
213 orderdetail.From_whse,p_cost_mthd,orderdetail.Actual_Shipdate)
214 -
215 pmi_sales_pkg.PMISA_GET_CHARGE(orderdetail.order_Id,orderdetail.line_Id,
216 orderdetail.extended_price,orderdetail.billing_currency,orderdetail.base_currency,
217 orderdetail.exchange_rate,orderdetail.mul_div_sign) line_Margin
218 FROM
219 OP_ORDR_HDR orderhdr,
220 OP_ORDR_DTL orderdetail,
221 IC_ITEM_MST itemmst,
222 SY_ORGN_MST org,
223 GL_PLCY_MST pol
224 WHERE orderhdr.order_id = orderdetail.order_id
225 AND orderdetail.item_id = itemmst.item_id
226 AND trunc(orderdetail.ACTUAL_SHIPDATE)
227 between p_prd_start_date and p_prd_end_date
228 AND org.orgn_code = orderhdr.orgn_code
229 AND pol.co_code = org.co_code
230 AND pol.org_id = p_OU_ID
231 UNION ALL
232 SELECT itemmst.item_id,
233 (DECODE(sob.CURRENCY_CODE,
234 orderhdr.TRANSACTIONAL_CURR_CODE,
235 orderdetail.ORDERED_QUANTITY * orderdetail.UNIT_SELLING_PRICE,
236 orderdetail.ORDERED_QUANTITY * orderdetail.UNIT_SELLING_PRICE
237 * orderhdr.CONVERSION_RATE)
238 )
239 - (gmicuom.I2UOM_CV(itemmst.Item_Id,
240 0, orderdetail.ORDER_QUANTITY_UOM,
241 orderdetail.ORDERED_QUANTITY,itemmst.Item_UM)*
242 pmi_common_pkg.PMICO_GET_COST(itemmst.item_id,
243 fromwhse.whse_code,
244 p_cost_mthd,
245 orderdetail.Actual_Shipment_date)
246 )
247 - (pmi_sales_pkg.PMIOM_GET_CHARGE(
248 orderdetail.header_Id,
249 orderdetail.line_Id,
250 orderdetail.ordered_quantity*orderdetail.unit_selling_price,
251 orderhdr.transactional_curr_code,
252 sob.currency_code,
253 orderhdr.conversion_rate,
254 orderdetail.ordered_quantity)
255 ) line_Margin
256 FROM
257 OE_ORDER_HEADERS_ALL orderhdr,
258 OE_ORDER_LINES_ALL orderdetail,
259 OE_SYSTEM_PARAMETERS_ALL masterorg,
260 MTL_SYSTEM_ITEMS msi,
261 IC_ITEM_MST itemmst,
262 HR_OPERATING_UNITS ou,
263 GL_SETS_OF_BOOKS sob,
264 IC_WHSE_MST fromwhse
265 WHERE
266 orderhdr.header_id = orderdetail.header_id
267 AND masterorg.org_id = orderhdr.org_id
268 AND msi.organization_id = masterorg.master_organization_id
269 AND msi.inventory_item_id = orderdetail.inventory_item_id
270 AND itemmst.item_no = msi.segment1
271 AND trunc(orderdetail.ACTUAL_SHIPMENT_DATE) between p_prd_start_date and p_prd_end_date
272 AND ou.organization_id = p_OU_ID
273 AND ou.organization_id = orderhdr.org_id
274 AND sob.set_of_books_id = ou.set_of_books_id
275 AND fromwhse.mtl_organization_id = orderdetail.ship_from_org_id
276 )
277 GROUP BY item_id
278 ORDER BY Margin desc;
279
280 /* Cursor to get Top N customers with respect to Margin */
281 Cursor cur_customer ( p_OU_ID IN hr_operating_units.ORGANIZATION_ID%TYPE,
282 p_cost_mthd IN cm_cmpt_dtl.cost_mthd_code%TYPE,
283 p_Prd_start_date IN DATE,
284 p_Prd_end_date IN DATE
285 )
286 IS
287 SELECT cust_id, SUM(line_margin) margin
288 FROM
289 (
290 SELECT orderhdr.billcust_id cust_id,
291 DECODE(orderdetail.BASE_CURRENCY,orderdetail.BILLING_CURRENCY,
292 orderdetail.EXTENDED_PRICE,
293 Decode(orderdetail.mul_div_sign,0,orderdetail.EXTENDED_PRICE*orderdetail.EXCHANGE_RATE,
294 orderdetail.EXTENDED_PRICE/orderdetail.EXCHANGE_RATE))
295 -
296 gmicuom.I2UOM_CV(orderdetail.Item_Id,0,orderdetail.ORDER_UM1,
297 orderdetail.ORDER_QTY1,itemmst.Item_UM)*
298 pmi_common_pkg.PMICO_GET_COST(orderdetail.item_id,
299 orderdetail.From_whse,p_cost_mthd,orderdetail.Actual_Shipdate)
300 -
301 pmi_sales_pkg.PMISA_GET_CHARGE(orderdetail.order_Id,orderdetail.line_Id,
302 orderdetail.extended_price,orderdetail.billing_currency,orderdetail.base_currency,
303 orderdetail.exchange_rate,orderdetail.mul_div_sign) line_Margin
304 FROM
305 OP_ORDR_HDR orderhdr,
306 OP_ORDR_DTL orderdetail,
307 IC_ITEM_MST itemmst,
308 SY_ORGN_MST org,
309 GL_PLCY_MST pol
310 WHERE orderhdr.order_id = orderdetail.order_id
311 AND orderdetail.item_id = itemmst.item_id
312 AND trunc(orderdetail.ACTUAL_SHIPDATE)
313 between p_prd_start_date and p_prd_end_date
314 AND org.orgn_code = orderhdr.orgn_code
315 AND pol.co_code = org.co_code
316 AND pol.org_id = p_OU_ID
317 UNION ALL
318 SELECT billingcustomer.party_id cust_id,
319 (DECODE(sob.CURRENCY_CODE,
320 orderhdr.TRANSACTIONAL_CURR_CODE,
321 orderdetail.ORDERED_QUANTITY * orderdetail.UNIT_SELLING_PRICE,
322 orderdetail.ORDERED_QUANTITY * orderdetail.UNIT_SELLING_PRICE
323 * orderhdr.CONVERSION_RATE)
324 )
325 - (gmicuom.I2UOM_CV(itemmst.Item_Id,
326 0, orderdetail.ORDER_QUANTITY_UOM,
327 orderdetail.ORDERED_QUANTITY,itemmst.Item_UM)*
328 pmi_common_pkg.PMICO_GET_COST(itemmst.item_id,
329 fromwhse.whse_code,
330 p_cost_mthd,
331 orderdetail.Actual_Shipment_date)
332 )
333 - (pmi_sales_pkg.PMIOM_GET_CHARGE(
334 orderdetail.header_Id,
335 orderdetail.line_Id,
336 orderdetail.ordered_quantity*orderdetail.unit_selling_price,
337 orderhdr.transactional_curr_code,
338 sob.currency_code,
339 orderhdr.conversion_rate,
340 orderdetail.ordered_quantity)
341 ) line_Margin
342 FROM
343 OE_ORDER_HEADERS_ALL orderhdr,
344 OE_ORDER_LINES_ALL orderdetail,
345 OE_SYSTEM_PARAMETERS_ALL masterorg,
346 MTL_SYSTEM_ITEMS msi,
347 IC_ITEM_MST itemmst,
348 HR_OPERATING_UNITS ou,
349 GL_SETS_OF_BOOKS sob,
350 IC_WHSE_MST fromwhse,
351 PMI_HZ_PARTY_V billingcustomer
352 WHERE
353 orderhdr.header_id = orderdetail.header_id
354 AND masterorg.org_id = orderhdr.org_id
355 AND msi.organization_id = masterorg.master_organization_id
356 AND msi.inventory_item_id = orderdetail.inventory_item_id
357 AND itemmst.item_no = msi.segment1
358 AND trunc(orderdetail.ACTUAL_SHIPMENT_DATE) between p_prd_start_date and p_prd_end_date
359 AND ou.organization_id = p_OU_ID
360 AND ou.organization_id = orderhdr.org_id
361 AND sob.set_of_books_id = ou.set_of_books_id
362 AND fromwhse.mtl_organization_id = orderdetail.ship_from_org_id
363 AND billingcustomer.SITE_USE_ID(+) = orderhdr.invoice_to_org_id
364 )
365 GROUP BY cust_id
366 ORDER BY Margin desc;
367 l_count NUMBER:=1;
368 l_found NUMBER:=0;
369 l_item_id NUMBER;
370 l_customer_id NUMBER;
371 l_margin NUMBER;
372 BEGIN
373 IF P_CUSTOMER_ID is NULL THEN
374 /* If Item id is passed check Item Table */
375 /* Check Package varables if already top N is calculated for the given combination */
376 IF (Pv_pmisa_tp_om_OU_ID = p_OU_ID AND Pv_pmisa_tp_om_cost_mthd = p_cost_mthd AND
377 Pv_pmisa_tp_om_Prd_start_dt = p_Prd_start_date AND Pv_pmisa_tp_om_Prd_end_dt = p_Prd_end_date AND
378 Pv_pmisa_tp_om_top_n = p_top_n ) THEN
379
380 /* If already Top N were Calculated for the given parameters
381 then check respective Package tables for given item-Customer id */
382
383 LOOP
384 IF pv_pmisa_top_n_item_by_ou(l_count) = p_item_id THEN
385 return 1;
386 END IF;
387 l_count := l_count+1;
388 EXIT WHEN l_count > p_top_n;
389 END LOOP;
390 return 0;
391 ELSE
392 /* If Top N is not calculated for the given parameters , Start Calculating the TOP N and
393 Populate corresponding tables */
394 /* assign new passed values to Package Variables */
395 Pv_pmisa_tp_om_OU_ID := p_OU_ID;
396 Pv_pmisa_tp_om_cost_mthd := p_cost_mthd;
397 Pv_pmisa_tp_om_Prd_start_dt := p_Prd_start_date;
398 Pv_pmisa_tp_om_Prd_end_dt := p_Prd_end_date;
399 Pv_pmisa_tp_om_top_n := p_top_n ;
400 /* If Top N item need to be calculated, Empty the previous constructed table */
401 pv_pmisa_top_n_item_by_ou.DELETE;
402 OPEN cur_item(p_OU_ID,p_cost_mthd,p_Prd_start_date,p_Prd_end_date);
403 LOOP
404 FETCH cur_item INTO l_item_id,l_margin;
405 EXIT WHEN cur_item%NOTFOUND;
406 EXIT WHEN l_count > p_top_n;
407 IF l_item_id = p_item_id THEN
408 /*Flag to check if this list contains the passed item */
409 l_found := 1;
410 END IF;
411 pv_pmisa_top_n_item_by_ou(l_count):= l_item_id;
412 l_count := l_count+1;
413 END LOOP;
414 CLOSE cur_item;
415 IF l_found=1 THEN
416 return 1;
417 ELSE
418 return 0;
419 END IF;
420 END IF;
421 ELSIF P_ITEM_ID is NULL THEN
422 /* Check Package varables if already top N is calculated for the given combination */
423 IF (Pv_pmisa_tp_om_cust_OU_ID = p_OU_ID AND Pv_pmisa_tp_om_cust_cost_mthd = p_cost_mthd AND
424 Pv_pmisa_tp_om_cust_Prd_st_dt = p_Prd_start_date AND Pv_pmisa_tp_om_cust_Prd_end_dt = p_Prd_end_date AND
425 Pv_pmisa_tp_om_cust_top_n = p_top_n ) THEN
426
427 /* If customer id is passed check Customer Table */
428 LOOP
429 IF pv_pmisa_top_n_cust_by_ou(l_count) = p_customer_id THEN
430 return 1;
431 END IF;
432 l_count := l_count+1;
433 EXIT WHEN l_count > p_top_n;
434 END LOOP;
435 return 0;
436
437 ELSE
438 /* If Top N is not calculated for the given parameters , Start Calculating the TOP N and
439 Populate corresponding tables */
440 /* assign new passed values to Package Variables */
441 Pv_pmisa_tp_om_cust_OU_ID := p_OU_ID;
442 Pv_pmisa_tp_om_cust_cost_mthd := p_cost_mthd;
443 Pv_pmisa_tp_om_cust_Prd_st_dt := p_Prd_start_date;
444 Pv_pmisa_tp_om_cust_Prd_end_dt := p_Prd_end_date;
445 Pv_pmisa_tp_om_cust_top_n := p_top_n ;
446
447 /* If Top N Customers need to be calculated, Empty the previous constructed table */
448
449 pv_pmisa_top_n_cust_by_ou.DELETE;
450 OPEN cur_customer(p_OU_ID,p_cost_mthd,p_Prd_start_date,p_Prd_end_date);
451 LOOP
452 FETCH cur_customer INTO l_customer_id,l_margin;
453 EXIT WHEN cur_customer%NOTFOUND;
454 EXIT WHEN l_count > p_top_n;
455 IF l_customer_id = p_customer_id THEN
456 /*Flag to check if this list contains the passed Customer */
457 l_found := 1;
458 END IF;
459 pv_pmisa_top_n_cust_by_ou(l_count):= l_customer_id;
460 l_count := l_count+1;
461 END LOOP;
462 CLOSE cur_customer;
463 IF l_found=1 THEN
464 return 1;
465 ELSE
466 return 0;
467 END IF;
468 END IF;
469 END IF;
470 END PMISA_GET_TOP_N_BY_OU;
471
472
473 FUNCTION PMISA_VALIDATE_DATE(p_Sales_Company IN sy_orgn_mst.co_code%TYPE,
474 p_From_year IN number,
475 p_To_year IN number,
476 p_From_period IN pmi_gl_calendar_v.period_name%TYPE,
477 p_To_period IN pmi_gl_calendar_v.period_name%TYPE,
478 p_actual_date IN DATE
479 )
480 RETURN NUMBER IS
481 BEGIN
482 /* Check if Start Date and End Date of given period has been calculated */
483 IF pv_pmisa_vd_from_year = p_From_year
484 AND pv_pmisa_vd_to_year = p_To_year
485 AND pv_pmisa_vd_from_period = p_From_period
486 AND pv_pmisa_vd_to_period = p_To_period
487 AND pv_pmisa_vd_Company = p_Sales_company THEN
488 /*If already Calculated then compare with package variables */
489
490 IF (trunc(p_actual_date) between pv_pmisa_vd_start_date and pv_pmisa_vd_end_date) THEN
491 return 1;
492 ELSE
493 return 0;
494 END IF;
495 ELSE
496 /*If not, Recalculate the start date and end date */
497 pv_pmisa_vd_from_year := p_From_year;
498 pv_pmisa_vd_to_year := p_To_year;
499 pv_pmisa_vd_from_period := p_From_period;
500 pv_pmisa_vd_to_period := p_To_period;
501 pv_pmisa_vd_Company := p_sales_company;
502 select start_date into pv_pmisa_vd_start_date
503 from pmi_gl_calendar_v
504 where period_year=p_from_year and period_name=p_from_period and co_code=p_sales_company;
505 select end_date into pv_pmisa_vd_end_date
506 from pmi_gl_calendar_v
507 where period_year=p_to_year and period_name=p_to_period and co_code=p_sales_company;
508 IF (trunc(p_actual_date) between pv_pmisa_vd_start_date and pv_pmisa_vd_end_date) THEN
509 return 1;
510 ELSE
511 return 0;
512 END IF;
513 END IF;
514 END PMISA_VALIDATE_DATE;
515
516 /* Function to calculate Charges for a given order line , Refer Package Spec for Details Description */
517 FUNCTION PMISA_GET_CHARGE ( p_Order_id IN op_ordr_dtl.order_id%TYPE,
518 p_Line_id IN op_ordr_dtl.line_id%TYPE,
519 p_extended_price IN op_ordr_dtl.extended_price%TYPE,
520 p_Billing_Currency IN op_ordr_dtl.billing_currency%TYPE,
521 p_Base_Currency IN op_ordr_dtl.BASE_CURRENCY%TYPE,
522 p_exchange_Rate IN op_ordr_dtl.EXCHANGE_RATE%TYPE,
523 p_mul_div_sign IN op_ordr_dtl.mul_div_sign%TYPE
524 )
525 RETURN NUMBER IS
526 /* Cursor to find changes for order and line */
527 /* SUM added in case multiple charge lines are returned for a line_id. -PDONG 01-31-02 */
528
529 CURSOR cur_line_charge(P_order_id op_ordr_dtl.order_id%TYPE,
530 P_line_id op_ordr_dtl.line_id%TYPE,
531 P_base_currency op_ordr_dtl.BASE_CURRENCY%TYPE,
532 p_billing_currency op_ordr_dtl.billing_currency%TYPE,
533 p_exchange_rate op_ordr_dtl.EXCHANGE_RATE%TYPE,
534 p_mul_div_sign op_ordr_dtl.mul_div_sign%TYPE)
535 IS
536 SELECT SUM(abs(DECODE(P_Base_Currency,p_Billing_Currency,
537 Extended_amount,
538 Decode(p_mul_div_sign,0,Extended_amount*p_Exchange_Rate,
539 Extended_amount/p_Exchange_Rate)))
540 )
541 FROM op_ordr_chg a, op_chrg_mst b
542 WHERE a.order_id = P_order_id
543 AND a.line_id = P_line_id
544 AND a.charge_id = b.charge_id
545 AND b.charge_type in (20,30);
546
547 /* Cursor to find changes for order */
548 CURSOR cur_order_charge(P_order_id op_ordr_dtl.order_id%TYPE,
549 P_base_currency op_ordr_dtl.BASE_CURRENCY%TYPE,
550 p_billing_currency op_ordr_dtl.billing_currency%TYPE,
551 p_exchange_rate op_ordr_dtl.EXCHANGE_RATE%TYPE,
552 p_mul_div_sign op_ordr_dtl.mul_div_sign%TYPE)
553 IS
554 SELECT sum(abs(DECODE(P_Base_Currency,p_Billing_Currency,
555 Extended_amount,
556 Decode(p_mul_div_sign,0,Extended_amount*p_Exchange_Rate,
557 Extended_amount/p_Exchange_Rate))))
558 FROM op_ordr_chg a, op_chrg_mst b
559 WHERE a.order_id = P_order_id
560 AND a.line_id IS NULL
561 AND a.charge_id = b.charge_id
562 AND b.charge_type in (20,30);
563
564 /*Cursor to find total value for order*/
565
566 CURSOR cur_order_value(P_order_id op_ordr_dtl.order_id%TYPE)
567 IS
568 SELECT SUM(DECODE(Base_Currency,Billing_Currency,
569 Extended_price,
570 Decode(mul_div_sign,0,Extended_price*Exchange_Rate,
571 Extended_price/Exchange_Rate)))
572 FROM op_ordr_dtl
573 WHERE order_id = P_order_id;
574
575 l_line_charge NUMBER;
576 l_order_value NUMBER;
577 l_order_charge NUMBER;
578 l_total_charge NUMBER;
579 BEGIN
580 /* Get Charges for line */
581 OPEN cur_line_charge(P_Order_id,p_Line_id,P_base_currency,p_billing_currency,p_exchange_rate,p_mul_div_sign);
582 FETCH cur_line_charge into l_line_charge;
583 IF cur_line_charge%NOTFOUND THEN
584 l_line_charge:=0;
585 END IF;
586 CLOSE cur_line_charge;
587
588 /* Get order value to calculate order discount */
589
590 OPEN cur_order_value(P_Order_id);
591 FETCH cur_order_value into l_order_value;
592 IF l_order_value = 0 THEN
593 l_order_value:=1;
594 END IF;
595 CLOSE cur_order_value;
596 /* Get Charges for Order */
597 OPEN cur_order_charge(P_Order_id,P_base_currency,p_billing_currency,p_exchange_rate,p_mul_div_sign);
598 FETCH cur_order_charge into l_order_charge;
599 IF cur_order_charge%NOTFOUND THEN
600 l_order_charge:=0;
601 END IF;
602 CLOSE cur_order_charge;
603
604 /* Calculate total Charges */
605 l_total_charge:=l_line_charge + (p_extended_price/(l_order_value)*l_order_charge);
606 return nvl(l_total_charge,0);
607 END PMISA_GET_CHARGE;
608
609
610 /* Function to calculate Charges for a given OM order line */
611
612 FUNCTION PMIOM_GET_CHARGE (p_Header_id IN oe_order_lines_all.header_id%TYPE,
613 p_Line_id IN oe_order_lines_all.line_id%TYPE,
614 p_extended_price IN oe_order_lines_all.unit_selling_price%TYPE,
615 p_Billing_Currency IN oe_order_headers_all.transactional_curr_code%TYPE,
616 p_Base_Currency IN gl_sets_of_books.currency_code%TYPE,
617 p_exchange_Rate IN oe_order_headers_all.conversion_rate%TYPE,
618 p_ordered_quantity IN oe_order_lines_all.ordered_quantity%TYPE
619 )
620 RETURN NUMBER
621 IS
622 /* cur_line_charge returns line-level changes, in transactional currency */
623
624 CURSOR cur_line_charge(P_Header_id oe_order_lines_all.header_id%TYPE,
625 P_line_id oe_price_adjustments.line_id%TYPE)
626 IS
627 /* Bug fix 2733400 SKARIMIS 01/10/2003 */
628 SELECT SUM(opa.adjusted_amount) * AVG(ool.ordered_quantity) line_level_discount
629 FROM oe_price_adjustments_v opa
630 , oe_order_lines_all ool
631 WHERE opa.line_id =p_line_id
632 and ool.line_id = p_line_id
633 and ool.header_id = p_header_id
634 and nvl(opa.applied_flag,'Y') = 'Y'
635 and nvl(opa.accrual_flag,'N') = 'N'
636 and list_line_type_code = 'DIS';
637
638 /* cur_order_charge returns order-level changes, in transactional currency */
639
640 CURSOR cur_order_charge IS
641 SELECT SUM(decode(opa.arithmetic_operator,
642 null, 0,
643 '%', opa.operand*ool.unit_list_price/100,
644 'AMT',opa.operand,
645 'NEWPRICE',ool.unit_list_price - opa.operand) * ool.ordered_quantity
646 ) order_level_discount
647 FROM oe_price_adjustments_v opa
648 , oe_order_lines_all ool
649 WHERE opa.HEADER_ID = p_header_id
650 and opa.line_id is null
651 and ool.line_id = p_line_id
652 and ool.header_id = p_header_id
653 and nvl(opa.applied_flag,'Y') = 'Y'
654 and nvl(opa.accrual_flag,'N') = 'N'
655 and list_line_type_code = 'DIS';
656
657 l_line_charge NUMBER;
658 l_order_charge NUMBER;
659 l_total_charge NUMBER;
660 BEGIN
661 /* Get Charges defined at the Line level */
662 OPEN cur_line_charge(p_Header_id,P_line_id) ;
663 FETCH cur_line_charge into l_line_charge;
664 IF cur_line_charge%NOTFOUND THEN
665 l_line_charge:=0;
666 END IF;
667 CLOSE cur_line_charge;
668
669 /* Get Charges defined at the Order level*/
670 OPEN cur_order_charge;
671 FETCH cur_order_charge into l_order_charge;
672 IF cur_order_charge%NOTFOUND THEN
673 l_order_charge:=0;
674 END IF;
675 CLOSE cur_order_charge;
676
677 /* Calculate total Charges */
678 l_total_charge:= nvl(l_line_charge,0) + nvl(l_order_charge, 0);
679
680 IF p_base_currency <> p_billing_currency THEN
681 l_total_charge := l_total_charge * p_exchange_rate;
682 END IF;
683
684 return l_total_charge;
685 END PMIOM_GET_CHARGE;
686
687
688 END pmi_sales_pkg;
[Home] [Help]