APPS.POA_SUPPERF_API_PKG SQL Statements

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

Line: 19

      SELECT MIN(transaction_date)
      INTO   v_receipt_date
      FROM   rcv_transactions
      WHERE  po_line_location_id = p_line_location_id
      AND    transaction_type = 'RECEIVE';

Line: 63

      SELECT sum(quantity_ordered * nvl(rate, 1)) /
	     DECODE(sum(quantity_ordered), 0, 1, sum(quantity_ordered))
      INTO   v_ave_rate
      FROM   po_distributions_all
      WHERE  line_location_id = p_line_location_id
      AND    nvl(distribution_type,'-99') <> 'AGREEMENT';

Line: 155

      SELECT count(*)
      INTO   v_num_receipts
      FROM   rcv_transactions
      WHERE  po_line_location_id = p_line_location_id
      AND    transaction_type = 'RECEIVE';

Line: 198

         SELECT SUM(primary_quantity)
         INTO   v_txn_qty
         FROM   rcv_transactions
         WHERE  po_line_location_id = p_line_location_id
         AND    transaction_type    = 'RECEIVE'
         AND    transaction_date - NVL(p_days_late_allowed, 0)
				    > p_expected_date;

Line: 208

         SELECT SUM(rcor.primary_quantity)
         INTO   v_correction_qty
         FROM   rcv_transactions    rcor,
	        rcv_transactions    rct
         WHERE  rcor.po_line_location_id = p_line_location_id
	 AND    rcor.transaction_type    = 'CORRECT'
	 AND    rct.transaction_id	 = rcor.parent_transaction_id
	 AND    rct.transaction_type     = 'RECEIVE'
         AND    rct.transaction_date - NVL(p_days_late_allowed, 0)
					 > p_expected_date;

Line: 258

         SELECT SUM(primary_quantity)
         INTO   v_txn_qty
         FROM   rcv_transactions
         WHERE  po_line_location_id = p_line_location_id
         AND    transaction_type    = 'RECEIVE'
         AND    transaction_date + NVL(p_days_early_allowed, 0)
				    < p_expected_date;

Line: 268

         SELECT SUM(rcor.primary_quantity)
         INTO   v_correction_qty
         FROM   rcv_transactions    rcor,
	        rcv_transactions    rct
         WHERE  rcor.po_line_location_id = p_line_location_id
	 AND    rcor.transaction_type    = 'CORRECT'
	 AND    rct.transaction_id	 = rcor.parent_transaction_id
	 AND    rct.transaction_type     = 'RECEIVE'
         AND    rct.transaction_date + NVL(p_days_early_allowed, 0)
					 < p_expected_date;

Line: 346

      SELECT primary_unit_of_measure
      INTO   v_primary_uom
      FROM   mtl_system_items 			items,
	     financials_system_params_all 	fsp
      WHERE  NVL(fsp.org_id, -999)	= NVL(p_organization_id, -999)
      AND    items.organization_id      = fsp.inventory_organization_id
      AND    inventory_item_id 		= p_item_id;

Line: 463

         SELECT nvl(quantity_delivered, 0) quantity_delivered,
                nvl(quantity_billed, 0) quantity_billed,
                nvl(quantity_cancelled, 0) quantity_cancelled,
                quantity_ordered,
                nvl(rate, 1) rate
         FROM   po_distributions_all
         WHERE  line_location_id = p_line_location_id
         AND    nvl(distribution_type,'-99')  <> 'AGREEMENT';

Line: 542

            SELECT supplier_id,
                   sum(quantity_rejected) /
		   decode(sum(nvl(quantity_received, 0)), 0, 1,
			  sum(nvl(quantity_received, 0))) defects
            FROM   poa_bis_supplier_performance_v
            WHERE  item_id = p_item
            AND    date_dimension between p_fdate and p_tdate
            GROUP BY supplier_id
            ORDER BY defects desc;

Line: 556

            SELECT supplier_id,
                   sum(nvl(quantity_received_late, 0) +
		       nvl(quantity_received_early, 0) +
                       nvl(quantity_past_due, 0)) /
		     nvl(sum(quantity_ordered), 1) exceptions
            FROM   poa_bis_supplier_performance_v
            WHERE  item_id = p_item
            AND    date_dimension between p_fdate and p_tdate
            GROUP BY supplier_id
            ORDER BY exceptions desc;

Line: 571

            SELECT supplier_id,
                   sum(quantity_purchased) volume
            FROM   poa_bis_supplier_performance_v
            WHERE  item_id = p_item
            AND    date_dimension between p_fdate and p_tdate
            GROUP BY supplier_id
            ORDER BY volume desc;

Line: 583

            SELECT supplier_id,
                   sum(quantity_ordered * purchase_price) /
		     nvl(sum(quantity_ordered), 1) price
            FROM   poa_bis_supplier_performance_v
            WHERE  item_id = p_item
            AND    date_dimension between p_fdate and p_tdate
            GROUP BY supplier_id
            ORDER BY price desc;

Line: 596

            SELECT supplier_id,
                   sum(amount) amount
            FROM   poa_bis_supplier_performance_v
            WHERE  item_id = p_item
            AND    date_dimension between p_fdate and p_tdate
            GROUP BY supplier_id
            ORDER BY amount desc;

Line: 608

            SELECT supplier_id,
                   sum(quantity_rejected) /
		   decode(sum(nvl(quantity_received, 0)), 0, 1,
			  sum(nvl(quantity_received, 0))) defects
            FROM   poa_bis_supplier_performance_v
            WHERE  item_id = p_item
            AND    date_dimension between p_fdate and p_tdate
            GROUP BY supplier_id
            ORDER BY defects asc;

Line: 622

            SELECT supplier_id,
                   sum(nvl(quantity_received_late, 0) +
                       nvl(quantity_received_early, 0) +
                       nvl(quantity_past_due, 0)) /
		     nvl(sum(quantity_ordered), 1) exceptions
            FROM   poa_bis_supplier_performance_v
            WHERE  item_id = p_item
            AND    date_dimension between p_fdate and p_tdate
            GROUP BY supplier_id
            ORDER BY exceptions asc;

Line: 637

            SELECT supplier_id,
                   sum(quantity_purchased) volume
            FROM   poa_bis_supplier_performance_v
            WHERE  item_id = p_item
            AND    date_dimension between p_fdate and p_tdate
            GROUP BY supplier_id
            ORDER BY volume asc;

Line: 649

            SELECT supplier_id,
                   sum(quantity_ordered * purchase_price) /
		     nvl(sum(quantity_ordered), 1) price
            FROM   poa_bis_supplier_performance_v
            WHERE  item_id = p_item
            AND    date_dimension between p_fdate and p_tdate
            GROUP BY supplier_id
            ORDER BY price asc;

Line: 662

            SELECT supplier_id,
                   sum(amount) amount
            FROM   poa_bis_supplier_performance_v
            WHERE  item_id = p_item
            AND    date_dimension between p_fdate and p_tdate
            GROUP BY supplier_id
            ORDER BY amount asc;

Line: 725

	 SELECT MAX(last_update_date)
	 INTO   v_max_rcv_date
	 FROM   rcv_transactions
	 WHERE  po_line_location_id = p_line_location_id;

Line: 741

	 SELECT last_update_date
	 INTO   v_shipment_date
	 FROM   po_line_locations_all
	 WHERE  line_location_id = p_line_location_id;

Line: 785

      SELECT SUM(primary_quantity)
      INTO   v_txn_qty
      FROM   rcv_transactions
      WHERE  po_line_location_id = p_line_location_id
      AND    transaction_type    = p_txn_type;

Line: 793

      SELECT SUM(rcor.primary_quantity)
      INTO   v_correction_qty
      FROM   rcv_transactions    rcor,
             rcv_transactions    rct
      WHERE  rcor.po_line_location_id = p_line_location_id
      AND    rcor.transaction_type    = 'CORRECT'
      AND    rct.transaction_id	      = rcor.parent_transaction_id
      AND    rct.transaction_type     = p_txn_type;