APPS.PON_OPT_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 40

select distinct
por.scenario_id scenario_id,
por.bid_number bid_number,
por.line_number line_number,
sysdate AS CREATION_DATE,
por.CREATED_BY,
sysdate AS LAST_UPDATE_DATE,
por.LAST_UPDATED_BY,
por.LAST_UPDATE_LOGIN,
pbip.FIXED_AMOUNT_COMPONENT AS FIXED_AMOUNT_COMPONENT
  from pon.pon_optimize_results por,pon_bid_shipments pbs,
       pon_bid_item_prices pbip
  where por.scenario_id =p_scenario_id
  and pbs.bid_number = por.bid_number
  and pbs.line_number = por.line_number
  and pbs.shipment_number = por.award_shipment_number
  and ( (pbs.quantity > por.award_quantity) or
        (pbs.max_quantity < por.award_quantity))
  and pbip.bid_number = pbs.bid_number
  and pbip.line_number = pbs.line_number;

Line: 82

        l_prob_lines(l_matrix_index).LAST_UPDATE_DATE := l_prob_opt_line.LAST_UPDATE_DATE;

Line: 83

        l_prob_lines(l_matrix_index).LAST_UPDATED_BY := l_prob_opt_line.LAST_UPDATED_BY;

Line: 84

        l_prob_lines(l_matrix_index).LAST_UPDATE_LOGIN := l_prob_opt_line.LAST_UPDATE_LOGIN;

Line: 105

              select sum(award_quantity)
              into l_total_award_quantity
              from pon.pon_optimize_results
              where bid_number = l_prob_lines(l_index).bid_number
              and line_number = l_prob_lines(l_index).line_number
              and scenario_id = p_scenario_id;

Line: 125

		 select shipment_number,per_unit_price_component
                     into l_new_shipment_number,l_per_unit_price_component
                     from pon_bid_shipments
                     where quantity <= l_total_award_quantity
                     and max_quantity >= l_total_award_quantity
                     and bid_number = l_prob_lines(l_index).bid_number
                     and line_number = l_prob_lines(l_index).line_number;

Line: 141

                 select max_quantity,shipment_number,
                      per_unit_price_component
                 into l_new_total_award_quantity,l_new_shipment_number,
                      l_per_unit_price_component
                 from pon_bid_shipments pbs1
  	         where pbs1.max_quantity =
		     (SELECT MAX(max_quantity)
		      FROM pon_bid_shipments pbs2
		      WHERE pbs2.max_quantity < l_total_award_quantity
		      and pbs2.bid_number = l_prob_lines(l_index).bid_number
		      and pbs2.line_number = l_prob_lines(l_index).line_number)
		 and pbs1.bid_number = l_prob_lines(l_index).bid_number
                 and pbs1.line_number = l_prob_lines(l_index).line_number;

Line: 177

              delete from pon_optimize_results
              where bid_number = l_prob_lines(l_index).bid_number
              and line_number = l_prob_lines(l_index).line_number;

Line: 184

                insert into pon_optimize_results(
                           SCENARIO_ID ,
                           BID_NUMBER,
                           LINE_NUMBER,
                           AWARD_QUANTITY,
                           AWARD_PRICE,
                           CREATION_DATE,
                           CREATED_BY,
                           LAST_UPDATE_DATE,
                           LAST_UPDATED_BY,
                           LAST_UPDATE_LOGIN,
                           AWARD_SHIPMENT_NUMBER,
                           INDICATOR_VALUE)
                 values (
                           p_scenario_id,
                           l_prob_lines(l_index).BID_NUMBER,
                           l_prob_lines(l_index).LINE_NUMBER,
                           l_prob_lines(l_index).AWARD_QUANTITY,
                           l_prob_lines(l_index).AWARD_PRICE,
                           sysdate,
                           l_prob_lines(l_index).CREATED_BY,
                           sysdate,
                           l_prob_lines(l_index).LAST_UPDATED_BY,
                           l_prob_lines(l_index).LAST_UPDATE_LOGIN,
                           nvl(l_prob_lines(l_index).AWARD_SHIPMENT_NUMBER,-1),
                           1);

Line: 221

         select sum(por.AWARD_QUANTITY * por.award_price) as scenario_total,
                sum(por.award_quantity*nvl2(por.award_shipment_number,pbs.unit_price,pbip.unit_price)) as po_total
         into l_po_total,l_scenario_total
         from pon.pon_optimize_results por,pon_bid_shipments pbs
              ,pon_bid_item_prices pbip
         where por.scenario_id = p_scenario_id
         and pbs.bid_number(+) = por.bid_number
         and pbs.line_number(+) = por.line_number
         and pbs.shipment_number(+) = por.award_shipment_number
         and pbip.bid_number = pbs.bid_number
         and pbip.line_number = pbs.line_number;

Line: 237

         update pon_optimize_scenarios
         set TOTAL_AWARD_AMOUNT = l_scenario_total,
             TOTAL_PO_AMOUNT = l_po_total
         where SCENARIO_ID = p_scenario_id;