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.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
103: --DBMS_OUTPUT.PUT_LINE('Row = '||l_index);
104:
105: select sum(award_quantity)
106: into l_total_award_quantity
107: from pon.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:
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:
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 ');
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,
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.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