[Home] [Help]
PACKAGE BODY: APPS.PON_OPT_PKG
Source
1 PACKAGE BODY PON_OPT_PKG as
2 /* $Header: PONOPTB.pls 120.1 2011/09/19 08:03:20 spapana ship $ */
3
4 --
5 --private helper procedure for logging
6 PROCEDURE print_log (p_message IN VARCHAR2)
7 IS
8 BEGIN
9
10 IF (g_fnd_debug = 'Y') THEN
11 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
12 FND_LOG.string(log_level => FND_LOG.level_statement,
13 module => g_module_prefix,
14 message => p_message);
15 END IF;
16 END IF;
17
18 END print_log;
19
20 PROCEDURE VERIFY_OPT_RESULT(p_scenario_id IN NUMBER
21 ,x_status OUT NOCOPY VARCHAR2)
22 IS
23 x_total_award_quantity NUMBER;
24 x_award_shipment_number NUMBER;
25 l_matrix_index NUMBER;
26 l_index NUMBER;
27 l_total_award_quantity NUMBER;
28 l_bid_number NUMBER;
29 l_line_number NUMBER;
30 l_new_total_award_quantity NUMBER;
31 l_new_shipment_number NUMBER;
32 l_new_award_price NUMBER;
33 l_per_unit_price_component NUMBER;
34 l_po_total NUMBER;
35 l_scenario_total NUMBER;
36
37 l_prob_lines t_prob_lines;
38
39 CURSOR prob_opt_line(p_scenario_id NUMBER) IS
40 select distinct
41 por.scenario_id scenario_id,
42 por.bid_number bid_number,
43 por.line_number line_number,
44 sysdate AS CREATION_DATE,
45 por.CREATED_BY,
46 sysdate AS LAST_UPDATE_DATE,
47 por.LAST_UPDATED_BY,
48 por.LAST_UPDATE_LOGIN,
49 pbip.FIXED_AMOUNT_COMPONENT AS FIXED_AMOUNT_COMPONENT
50 from pon_optimize_results por,pon_bid_shipments pbs,
51 pon_bid_item_prices pbip
52 where por.scenario_id =p_scenario_id
53 and pbs.bid_number = por.bid_number
54 and pbs.line_number = por.line_number
55 and pbs.shipment_number = por.award_shipment_number
56 and ( (pbs.quantity > por.award_quantity) or
57 (pbs.max_quantity < por.award_quantity))
58 and pbip.bid_number = pbs.bid_number
59 and pbip.line_number = pbs.line_number;
60
61 l_prob_opt_line prob_opt_line%ROWTYPE;
62
63 BEGIN
64 l_matrix_index := 0;
65 x_status := 'N'; -- set the initial status to N meaning that we
66 -- are not doing anything
67
68 print_log('scenario_id: ' || p_scenario_id);
69
70 open prob_opt_line(p_scenario_id);
71 loop
72 fetch prob_opt_line into l_prob_opt_line;
73 EXIT WHEN prob_opt_line%NOTFOUND;
74
75
76 l_matrix_index := l_matrix_index + 1;
77 l_prob_lines(l_matrix_index).bid_number := l_prob_opt_line.bid_number;
78 l_prob_lines(l_matrix_index).line_number := l_prob_opt_line.line_number;
79 l_prob_lines(l_matrix_index).scenario_id := l_prob_opt_line.scenario_id;
80 l_prob_lines(l_matrix_index).CREATION_DATE := l_prob_opt_line.CREATION_DATE;
81 l_prob_lines(l_matrix_index).CREATED_BY := l_prob_opt_line.CREATED_BY;
82 l_prob_lines(l_matrix_index).LAST_UPDATE_DATE := l_prob_opt_line.LAST_UPDATE_DATE;
83 l_prob_lines(l_matrix_index).LAST_UPDATED_BY := l_prob_opt_line.LAST_UPDATED_BY;
84 l_prob_lines(l_matrix_index).LAST_UPDATE_LOGIN := l_prob_opt_line.LAST_UPDATE_LOGIN;
85 l_prob_lines(l_matrix_index).FIXED_AMOUNT_COMPONENT := l_prob_opt_line.FIXED_AMOUNT_COMPONENT;
86
87 end loop; -- End of Fetch loop
88
89 if ( l_matrix_index = 0) then
90 x_status := 'N';
91 print_log(' No Rows to process');
92 -- No need to do anything. Just Break.
93 ELSE
94 x_status := 'Y';
95 print_log(' Rows to process');
96 END IF;
97
98 -- Looping through all problem bids/
99 FOR l_index IN 1..l_matrix_index LOOP
100
101 print_log(' Row = '||l_index);
102
103 --DBMS_OUTPUT.PUT_LINE('Row = '||l_index);
104
105 select sum(award_quantity)
106 into l_total_award_quantity
107 from pon_optimize_results
108 where bid_number = l_prob_lines(l_index).bid_number
109 and line_number = l_prob_lines(l_index).line_number
110 and scenario_id = p_scenario_id;
111
112 l_new_shipment_number := -1;
113 l_new_total_award_quantity := -1;
114
115 print_log(' bid_number = '||l_prob_lines(l_index).bid_number);
116 print_log(' line_number = '||l_prob_lines(l_index).line_number);
117 print_log('award qt '||l_total_award_quantity);
118
119 --DBMS_OUTPUT.PUT_LINE(' bid_number = '||l_prob_lines(l_index).bid_number);
120 --DBMS_OUTPUT.PUT_LINE(' line_number = '||l_prob_lines(l_index).line_number);
121 --DBMS_OUTPUT.PUT_LINE('award qt '||l_total_award_quantity);
122
123 BEGIN
124 print_log(' Trying Exact Match l_new_shipment_number');
125 select shipment_number,per_unit_price_component
126 into l_new_shipment_number,l_per_unit_price_component
127 from pon_bid_shipments
128 where quantity <= l_total_award_quantity
129 and max_quantity >= l_total_award_quantity
130 and bid_number = l_prob_lines(l_index).bid_number
131 and line_number = l_prob_lines(l_index).line_number;
132 print_log(' Exact Match found - l_new_shipment_number = '||l_new_shipment_number);
133 -- Exact Match Found
134 l_new_total_award_quantity := l_total_award_quantity;
135
136
137 EXCEPTION
138 -- No Exact Match Found
139 WHEN NO_DATA_FOUND THEN
140 BEGIN
141 select max_quantity,shipment_number,
142 per_unit_price_component
143 into l_new_total_award_quantity,l_new_shipment_number,
144 l_per_unit_price_component
145 from pon_bid_shipments pbs1
146 where pbs1.max_quantity =
147 (SELECT MAX(max_quantity)
148 FROM pon_bid_shipments pbs2
149 WHERE pbs2.max_quantity < l_total_award_quantity
150 and pbs2.bid_number = l_prob_lines(l_index).bid_number
151 and pbs2.line_number = l_prob_lines(l_index).line_number)
152 and pbs1.bid_number = l_prob_lines(l_index).bid_number
153 and pbs1.line_number = l_prob_lines(l_index).line_number;
154
155 print_log(' Not Exact Match l_new_shipment_number = '||l_new_shipment_number);
156 print_log(' Not Exact Match l_new_total_award_quantity = '||l_new_total_award_quantity);
157
158 EXCEPTION
159 WHEN no_data_found THEN
160 print_log(' No tier can be found for this qty ');
161 l_new_shipment_number := -1;
162 END;
163 END;
164
165
166 -- Calculate the new Award Price.
167 IF (l_new_shipment_number <> -1) THEN
168 l_prob_lines(l_index).award_price := l_per_unit_price_component + (l_prob_lines(l_index).fixed_amount_component/l_new_total_award_quantity);
169 l_prob_lines(l_index).award_quantity := l_new_total_award_quantity;
170 l_prob_lines(l_index).award_shipment_number := l_new_shipment_number;
171
172 print_log(' New award_price = '||l_prob_lines(l_index).award_price);
173 print_log(' New shipment_number = '||l_prob_lines(l_index).award_shipment_number);
174 END IF;
175
176 -- Delete from pon_optimize_results errenous rows.
177 delete from pon_optimize_results
178 where bid_number = l_prob_lines(l_index).bid_number
179 and line_number = l_prob_lines(l_index).line_number;
180
181 --DBMS_OUTPUT.PUT_LINE(' After Delete ');
182 -- Insert the new row with calculated quantity and shipment_number
183 IF (l_new_shipment_number <> -1) THEN
184 insert into pon_optimize_results(
185 SCENARIO_ID ,
186 BID_NUMBER,
187 LINE_NUMBER,
188 AWARD_QUANTITY,
189 AWARD_PRICE,
190 CREATION_DATE,
191 CREATED_BY,
192 LAST_UPDATE_DATE,
193 LAST_UPDATED_BY,
194 LAST_UPDATE_LOGIN,
195 AWARD_SHIPMENT_NUMBER,
196 INDICATOR_VALUE)
197 values (
198 p_scenario_id,
199 l_prob_lines(l_index).BID_NUMBER,
200 l_prob_lines(l_index).LINE_NUMBER,
201 l_prob_lines(l_index).AWARD_QUANTITY,
202 l_prob_lines(l_index).AWARD_PRICE,
203 sysdate,
204 l_prob_lines(l_index).CREATED_BY,
205 sysdate,
206 l_prob_lines(l_index).LAST_UPDATED_BY,
207 l_prob_lines(l_index).LAST_UPDATE_LOGIN,
208 nvl(l_prob_lines(l_index).AWARD_SHIPMENT_NUMBER,-1),
209 1);
210 END IF;
211
212 --DBMS_OUTPUT.PUT_LINE(' After Nsert ');
213
214 END LOOP; -- End of Bid Line loop for problem Bid Lines.
215
216
217 -- To Calculate the Scenario_total
218 if ( x_status = 'Y') THEN
219 print_log(' Calculating Scenario and po_total');
220
221 select sum(por.AWARD_QUANTITY * por.award_price) as scenario_total,
222 sum(por.award_quantity*nvl2(por.award_shipment_number,pbs.unit_price,pbip.unit_price)) as po_total
223 into l_po_total,l_scenario_total
224 from pon_optimize_results por,pon_bid_shipments pbs
225 ,pon_bid_item_prices pbip
226 where por.scenario_id = p_scenario_id
227 and pbs.bid_number(+) = por.bid_number
228 and pbs.line_number(+) = por.line_number
229 and pbs.shipment_number(+) = por.award_shipment_number
230 and pbip.bid_number = pbs.bid_number
231 and pbip.line_number = pbs.line_number;
232
233 print_log(' Scenario total '||l_scenario_total);
234 print_log(' PO total '||l_po_total);
235
236 -- Update the Scenario Table with the total.
237 update pon_optimize_scenarios
238 set TOTAL_AWARD_AMOUNT = l_scenario_total,
239 TOTAL_PO_AMOUNT = l_po_total
240 where SCENARIO_ID = p_scenario_id;
241 END IF;
242
243 print_log(' At the very end Status '||x_status);
244
245 END VERIFY_OPT_RESULT;
246
247
248 END PON_OPT_PKG;