1 PACKAGE BODY poa_dbi_savings_pkg AS
2 /* $Header: poadbipodsvgb.pls 115.11 2004/02/12 19:17:52 mangupta noship $ */
3
4 g_po_distribution_id NUMBER := -99999;
5 g_lowest_price NUMBER := -99999;
6 g_blanket_id NUMBER := -99999;
7
8 function get_lowest_possible_price (p_creation_date DATE,
9 p_org_id IN NUMBER,
10 p_need_by_date IN DATE,
11 p_quantity IN NUMBER,
12 p_unit_meas_lookup_code IN VARCHAR2,
13 p_currency_code IN VARCHAR2,
14 p_item_id IN NUMBER,
15 p_item_revision in varchar2,
16 p_category_id in number,
17 p_ship_to_location_id IN NUMBER,
18 p_func_cur_code IN VARCHAR2,
19 p_rate_date IN DATE,
20 p_ship_to_ou_id IN NUMBER,
21 p_ship_to_organization_id IN NUMBER,
22 p_po_distribution_id IN NUMBER,
23 p_type IN VARCHAR2)
24 RETURN NUMBER
25 IS
26
27 TYPE T_FLEXREF IS REF CURSOR;
28
29 rlines_cur T_FLEXREF;
30 rline_q NUMBER := 0;
31 rline_qa NUMBER := 0;
32 rline_a NUMBER := 0;
33
34 cursor pblines_cur(p_item_id number
35 ,p_unit_meas_lookup_code varchar2
36 ,p_creation_date date
37 ,p_currency_code varchar2)
38 is
39 select bl.po_header_id,b.vendor_id,bl.po_line_id,bl.price_break_lookup_code
40 ,bl.unit_price,bl.min_release_amount bl_min,bl.expiration_date
41 ,b.min_release_amount b_min,b.amount_limit , b.global_agreement_flag
42 ,0 line_qty,0 line_all_qty, gl.currency_code bl_func_cur_code, b.rate bl_rate
43 from po_lines_all bl
44 ,po_headers_all b
45 ,financials_system_params_all fsp
46 ,gl_sets_of_books gl
47 where bl.item_id = p_item_id
48 and bl.price_break_lookup_code is not null
49 and bl.unit_meas_lookup_code = p_unit_meas_lookup_code
50 and b.org_id = fsp.org_id
51 and fsp.set_of_books_id = gl.set_of_books_id
52 and ((b.org_id = p_ship_to_ou_id and nvl(b.global_agreement_flag, 'N') = 'N')
53 or (b.global_agreement_flag = 'Y' and exists
54 (select 'enabled'
55 from po_ga_org_assignments pgoa
56 where pgoa.po_header_id = b.po_header_id
57 and pgoa.enabled_flag = 'Y'
58 and ((pgoa.purchasing_org_id in
59 (select tfh.start_org_id
60 from mtl_procuring_txn_flow_hdrs_v tfh,
61 financials_system_params_all fsp1,
62 financials_system_params_all fsp2
63 where p_creation_date between nvl(tfh.start_date, p_creation_date) and nvl(tfh.end_date, p_creation_date)
64 and fsp1.org_id = tfh.start_org_id
65 and fsp1.purch_encumbrance_flag = 'N'
66 and fsp2.org_id = tfh.end_org_id
67 and fsp2.purch_encumbrance_flag = 'N'
68 and tfh.end_org_id = p_ship_to_ou_id
69 and ((tfh.qualifier_code is null) or (tfh.qualifier_code = 1 and tfh.qualifier_value_id = p_category_id))
70 and ((tfh.organization_id is null) or (tfh.organization_id = p_ship_to_organization_id))
71 )
72 )
73 or (nvl(pgoa.purchasing_org_id, p_ship_to_ou_id) = p_ship_to_ou_id))
74 )))
75 and nvl(bl.cancel_flag, 'N') = 'N'
76 and Trunc(p_creation_date) <= nvl(bl.expiration_date, p_creation_date)
77 and p_creation_date >= bl.creation_date
78 and bl.po_header_id = b.po_header_id
79 and b.type_lookup_code = 'BLANKET'
80 and b.approved_flag in ('Y','R')
81 and nvl(b.cancel_flag, 'N') = 'N'
82 and Trunc(p_creation_date)
83 between nvl(b.start_date, Trunc(p_creation_date)) and nvl(b.end_date, p_creation_date);
84
85 type pot_blkt_lines_tbl is TABLE Of pblines_cur%ROWTYPE;
86
87 cursor pricebreaks_cur(p_po_line_id number
88 ,p_ship_to_location_id number
89 ,p_unit_price number
90 ,p_price_break_lookup_code varchar2
91 ,p_b_min number
92 ,p_bl_min number
93 ,p_amount_limit number
94 ,p_blanket_amt number
95 ,p_quantity number
96 ,p_line_qty number
97 ,p_line_all_qty number
98 ,p_creation_date date
99 ,p_need_by_date date)
100 is
101 select min(shipto_price) keep (dense_rank first order by nvl2(shipto_price, nvl(quantity, 0), null) desc nulls last, trunc(creation_date) desc) over () shipto_price,
102 min(generic_price) keep (dense_rank first order by nvl2(generic_price, nvl(quantity, 0), null) desc nulls last, trunc(creation_date) desc) over () generic_price
103 from
104 (select
105 (case when pb.ship_to_location_id = p_ship_to_location_id and (pb.quantity is null or
106 (p_price_break_lookup_code = 'NON CUMULATIVE' and p_quantity >= pb.quantity)
107 or (p_price_break_lookup_code = 'CUMULATIVE' and p_quantity + p_line_qty >= pb.quantity ))
108 then pb.price_override else null end) shipto_price,
109 (case when pb.line_location_id is not null and pb.ship_to_location_id is null and (pb.quantity is null or
110 (p_price_break_lookup_code = 'NON CUMULATIVE' and p_quantity >= pb.quantity)
111 or (p_price_break_lookup_code = 'CUMULATIVE' and p_quantity + p_line_all_qty >= pb.quantity))
112 then pb.price_override else null end) generic_price,
113 creation_date,
114 pb.quantity
115 from po_line_locations_all pb
116 where pb.po_line_id = p_po_line_id
117 and pb.shipment_type = 'PRICE BREAK'
118 and p_quantity * nvl(pb.price_override,p_unit_price) >= nvl(p_bl_min,0)
119 and p_quantity * nvl(pb.price_override,p_unit_price) >= nvl(p_b_min,0)
120 and trunc(nvl(p_need_by_date, p_creation_date)) between
121 trunc(nvl(pb.start_date, pb.creation_date)) and
122 nvl(pb.end_date, nvl(p_need_by_date, p_creation_date))
123 and (p_amount_limit is null or p_quantity * nvl(pb.price_override,p_unit_price) + p_blanket_amt <= p_amount_limit) );
124
125 l_pbline pblines_cur%ROWTYPE;
126 l_qty number;
127 l_shipto_price number;
128 l_generic_price number;
129 l_unit_price number;
130 l_shipto_min number;
131 l_generic_min number;
132 l_unit_price_min number;
133 l_shipto_po_header_id_min number;
134 l_generic_po_header_id_min number;
135 l_unit_po_header_id_min number;
136 l_ret number := null;
137 l_index number;
138 l_debug_line number;
139 l_ga_conversion_rate number;
140 begin
141 /*
142
143 For blanket lines with price breaks, We store line-level price in the same row as price_override. For blanket lines
144 without price breaks, it stands in a seperate row by itself. Therefore we can't use a where clause to exclude the price
145 breaks in the former case, b/c that might remove a good line-level price in the 3nd step. Note that a blanket line
146 may not have any price break. And a blanket line may not have any release shipment, either. So both are outer joins.
147
148 $$$$$$$$$$$$$$$$$$$$$$$$ Ordering criteria:
149 >with price break
150 order | Null ship-to | Matching ship-to | Mis-matching ship-to
151 _____________________________________________________________________________________
152 Good price break quantity | 2 | 1 | 3
153 Bad price break quantity | 3 | 3 | 3
154 _____________________________________________________________________________________
155 >without price break (line level price)
156 order: 3
157
158 $$$$$$$$$$$$$$$$$$$$$$$$ line_qty and line_all_qty:
159 for price breaks w/o ship_to_location specified, any shipment toward that blanket line counts as a price break ladder
160 for price breaks w/ ship_to_location specified, only shipments with the exact ship_to_location counts.
161 In PO Forms, ship_to_org is also considered simliarly, but we don't consider ship_to_org in our best price calculation.
162
163 $$$$$$$$$$$$$$$$$$$$$$$$ rll.approved_flag:
164 used in line_qty for price break ladder -- only approved release shipments count. Not used in blanket_amt because you
165 cannot exceed the blanekt total amount even if the obstacle is an unapproved shipment. This is in accordance with
166 Forms logic.
167
168 $$$$$$$$$$$$$$$$$$$$$$$$ Assumptions:
169 For a particular blanket line, the line level price is no smaller than any line on the price break. This is important when
170 blanket_amt (in amount term, not quantity) is considered.
171 If a price break throws the blanket off the roof, its corresponding line price surely will. Thus it's safe to put them in
172 the final where clause.
173 For line_min and blanket_min, however, we could theoretically have the scenario when a price break is too small, but its
174 line price is big enough to get over the MINs. Although that will disallow us to use the line price, the penalty is small
175 enough (quantity is small) and it's a corner case anyway.
176
177 */
178
179 if (g_po_distribution_id <> p_po_distribution_id) then
180
181 g_po_distribution_id := p_po_distribution_id;
182
183 g_hit_count := g_hit_count + 1;
184
185 open pblines_cur(p_item_id,p_unit_meas_lookup_code,p_creation_date,p_currency_code);
186 loop
187 fetch pblines_cur into l_pbline;
188 exit when pblines_cur%NOTFOUND;
189
190 l_ga_conversion_rate := nvl(l_pbline.bl_rate, 1) * poa_ga_util_pkg.get_ga_conversion_rate(l_pbline.bl_func_cur_code, p_func_cur_code, p_rate_date); -- convert to blanket functional currency and then to standard PO functional currency
191
192 if(l_pbline.global_agreement_flag = 'Y') then
193 select
194 sum(case when sll.approved_flag='Y' and sll.ship_to_location_id=p_ship_to_location_id then nvl(sd.quantity_ordered,0)-nvl(sd.quantity_cancelled,0) else 0 end) line_qty
195 ,sum(case when sll.approved_flag='Y' then nvl(sd.quantity_ordered,0)-nvl(sd.quantity_cancelled,0) else 0 end) line_all_qty
196 ,sum(sum(nvl(sll.price_override,0)*(nvl(sd.quantity_ordered,0)-nvl(sd.quantity_cancelled,0)))) over () blanket_amt
197 into rline_q, rline_qa, rline_a
198 from po_line_locations_all sll
199 ,po_distributions_all sd
200 ,po_lines_all pol
201 where pol.po_line_id = sll.po_line_id (+)
202 and pol.from_header_id = l_pbline.po_header_id
203 and pol.from_line_id = l_pbline.po_line_id
204 and sll.shipment_type (+) = 'STANDARD'
205 and nvl(sd.distribution_type,'-99') <> 'AGREEMENT'
206 and sll.line_location_id = sd.line_location_id(+)
207 and sd.creation_date(+) < p_creation_date;
208
209 else
210 select
211 sum(case when rll.approved_flag='Y' and rll.ship_to_location_id=p_ship_to_location_id then
212 nvl(rd.quantity_ordered,0)-nvl(rd.quantity_cancelled,0) else 0 end) line_qty
213 ,sum(case when rll.approved_flag='Y' then
214 nvl(rd.quantity_ordered,0)-nvl(rd.quantity_cancelled,0) else 0 end) line_all_qty
215 ,sum(sum(nvl(rll.price_override,0)*(nvl(rd.quantity_ordered,0)-nvl(rd.quantity_cancelled,0)))) over () blanket_amt
216 into rline_q, rline_qa, rline_a
217 from po_line_locations_all rll
218 ,po_distributions_all rd
219 where rll.po_header_id = l_pbline.po_header_id
220 and rll.po_line_id = l_pbline.po_line_id
221 and rll.shipment_type = 'BLANKET'
222 and nvl(rd.distribution_type,'-99') <> 'AGREEMENT'
223 and rll.line_location_id = rd.line_location_id(+)
224 and rd.creation_date(+) < p_creation_date;
225
226 end if;
227
228 open pricebreaks_cur(l_pbline.po_line_id
229 ,p_ship_to_location_id
230 ,l_pbline.unit_price
231 ,l_pbline.price_break_lookup_code
232 ,nvl(l_pbline.b_min, 0)
233 ,nvl(l_pbline.bl_min, 0)
234 ,l_pbline.amount_limit
235 ,nvl(rline_a, 0)
236 ,p_quantity
237 ,nvl(rline_q, 0)
238 ,nvl(rline_qa, 0)
239 ,p_creation_date
240 ,p_need_by_date);
241 fetch pricebreaks_cur into l_shipto_price, l_generic_price;
242 close pricebreaks_cur;
243 if(l_shipto_price is not null and (l_shipto_min is null or l_shipto_min > l_shipto_price * l_ga_conversion_rate)) then
244 l_shipto_min := l_shipto_price * l_ga_conversion_rate;
245 l_shipto_po_header_id_min := l_pbline.po_header_id;
246 end if;
247 if(l_generic_price is not null and (l_generic_min is null or l_generic_min > l_generic_price * l_ga_conversion_rate)) then
248 l_generic_min := l_generic_price * l_ga_conversion_rate;
249 l_generic_po_header_id_min := l_pbline.po_header_id;
250 end if;
251
252 l_unit_price := l_pbline.unit_price;
253 if(l_unit_price is not null and (l_unit_price_min is null or l_unit_price_min > l_unit_price * l_ga_conversion_rate)) then
254 if(p_quantity * l_unit_price >= nvl(l_pbline.bl_min,0)
255 and p_quantity * l_unit_price >= nvl(l_pbline.b_min,0)
256 and (l_pbline.amount_limit is null
257 or p_quantity * l_unit_price + nvl(rline_a, 0) <= l_pbline.amount_limit)) then
258 l_unit_price_min := l_unit_price * l_ga_conversion_rate;
259 l_unit_po_header_id_min := l_pbline.po_header_id;
260 end if;
261 end if;
262 end loop;
263
264 g_lowest_price := coalesce(l_shipto_min,l_generic_min,l_unit_price_min);
265 g_blanket_id := coalesce(l_shipto_po_header_id_min, l_generic_po_header_id_min, l_unit_po_header_id_min);
266
267 end if;
268
269 return (case p_type
270 when 'PRICE' then g_lowest_price
271 when 'BLANKET' then g_blanket_id
272 end);
273
274 END get_lowest_possible_price ;
275
276 END poa_dbi_savings_pkg;
[Home] [Help]