APPS.POR_CHANGE_REQUEST_PKG SQL Statements

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

Line: 19

    SELECT gls.currency_code
    INTO functional_cur_code
    FROM
      financials_system_parameters fsp,
      gl_sets_of_books gls
    WHERE
      fsp.set_of_books_id = gls.set_of_books_id;

Line: 58

    SELECT req_fsp.set_of_books_id
    INTO l_req_ou_sob_id
    FROM financials_system_params_all req_fsp
    WHERE nvl(req_fsp.org_id, - 99) = nvl(p_req_ou, - 99);

Line: 66

      SELECT default_rate_type
      INTO  l_rate_type
      FROM  po_system_parameters_all psp
      WHERE nvl(psp.org_id, - 99) = nvl(p_po_ou, - 99);

Line: 365

    SELECT
      prl.purchase_basis,
      prl.matching_basis,
      trunc(prl.need_by_date) - trunc(pll.need_by_date),
      (prl.quantity - nvl(prl.quantity_cancelled, 0)) - pll.quantity,
      prl.org_id,
      poh.org_id,
      prl.currency_code,
      prl.unit_price,
      poh.currency_code,
      poh.rate, poh.rate_type, poh.rate_date, pll.price_override,
      pol.unit_price,
      prl.amount,
      pll.amount,
      pol.amount,
      trunc(prl.need_by_date) - trunc(pol.start_date),
      trunc(prl.assignment_end_date) - trunc(pol.expiration_date)
    INTO
      purchase_basis,
      matching_basis,
      date_diff,
      quantity_diff,
      req_ou,
      po_ou,
      req_cur_code,
      req_price,
      po_cur_code,
      po_rate,
      po_rate_type,
      po_rate_date,
      line_location_price,
      po_line_price,
      req_amount,
      line_location_amount,
      po_line_amount,
      start_date_diff,
      end_date_diff
    FROM
      po_requisition_lines prl,
      po_req_distributions prd,
      po_line_locations_all pll,
      po_headers_all poh,
      po_lines_all pol,
      po_distributions_all pod
    WHERE
      prl.requisition_line_id = reqlineid AND
      prd.requisition_line_id = prl.requisition_line_id AND
      prl.line_location_id = pll.line_location_id AND
      pll.po_header_id = poh.po_header_id AND
      pol.po_header_id = poh.po_header_id AND
      pod.po_line_id = pol.po_line_id AND
      pod.req_distribution_id = prd.distribution_id AND ROWNUM = 1;

Line: 516

    SELECT nvl(SUM(get_changed_line_total(requisition_line_id)), 0)
    INTO req_total
    FROM
      po_requisition_lines
    WHERE
     requisition_header_id = reqheaderid AND
     nvl(cancel_flag, 'N') = 'N' AND
     nvl(modified_by_agent_flag, 'N') = 'N' AND
     requisition_line_id NOT IN
     (SELECT DISTINCT document_line_id
     FROM po_change_requests
     WHERE document_header_id = reqheaderid
     AND request_level = 'LINE'
     AND action_type = 'CANCELLATION');

Line: 549

    SELECT nvl(SUM(get_chn_line_nonrec_tax_total(requisition_line_id)), 0)
    INTO tax_total
    FROM
      po_requisition_lines
    WHERE
      requisition_header_id =  reqheaderid
      AND requisition_line_id NOT IN
      (SELECT DISTINCT document_line_id
      FROM po_change_requests
      WHERE document_header_id = reqheaderid
      AND request_level = 'LINE'
      AND action_type = 'CANCELLATION');

Line: 584

      SELECT new_price
      INTO unit_price
      FROM po_change_requests
      WHERE
        document_line_id = reqlineid AND
        document_type = 'REQ' AND
        action_type = 'MODIFICATION' AND
        request_status = 'SYSTEMSAVE' AND
        new_price IS NOT NULL;

Line: 600

    SELECT unit_price
    INTO unit_price
    FROM po_requisition_lines_all
    WHERE
      requisition_line_id = reqlineid;

Line: 628

    SELECT MIN(pcr.change_request_group_id)
    INTO grp_id
    FROM
      po_requisition_lines_all prl,
      po_change_requests pcr
    WHERE
      pcr.document_header_id = prl.requisition_header_id
      AND prl.requisition_line_id = reqlineid
      AND pcr.request_status = 'SYSTEMSAVE';

Line: 642

      SELECT new_quantity
      INTO line_qty
      FROM
        po_change_requests
      WHERE
        document_line_id = reqlineid AND
        change_request_group_id = grp_id AND
        document_type = 'REQ' AND
        new_quantity IS NOT NULL;

Line: 687

    SELECT MIN(pcr.change_request_group_id)
    INTO grp_id
    FROM
      po_requisition_lines_all prl,
      po_change_requests pcr
    WHERE
      pcr.document_header_id = prl.requisition_header_id
      AND prl.requisition_line_id = reqlineid
      AND pcr.request_status = 'SYSTEMSAVE';

Line: 701

    SELECT prl.matching_basis
    INTO matching_basis
    FROM
      po_requisition_lines_all prl
    WHERE
      prl.requisition_line_id = reqlineid;

Line: 762

    SELECT MIN(pcr.change_request_group_id)
    INTO grp_id
    FROM
      po_requisition_lines_all prl,
      po_change_requests pcr
    WHERE
      pcr.document_header_id = prl.requisition_header_id
      AND prl.requisition_line_id = reqlineid
      AND pcr.request_status = 'SYSTEMSAVE';

Line: 773

    SELECT prl.matching_basis
    INTO matching_basis
    FROM
      po_requisition_lines_all prl
    WHERE
      prl.requisition_line_id = reqlineid;

Line: 830

    SELECT SUM(decode(prl.matching_basis, 'AMOUNT', prd.req_line_amount, prd.req_line_quantity * prl.unit_price))
      INTO old_line_total
    FROM
      po_requisition_lines_all prl,
      po_req_distributions prd
    WHERE
      prl.requisition_line_id = reqlineid AND
      prl.requisition_line_id =  prd.requisition_line_id;

Line: 866

    SELECT SUM(decode(prl.matching_basis, 'AMOUNT', prd.req_line_amount, prd.req_line_quantity * prl.unit_price))
      INTO old_line_total
    FROM
      po_requisition_lines_all prl,
      po_req_distributions_all prd
    WHERE
      prl.requisition_line_id = reqlineid AND
      prl.requisition_line_id =  prd.requisition_line_id;

Line: 908

  SELECT DISTINCT(request_status)
  FROM
    po_change_requests pcr
  WHERE
    pcr.document_type = 'REQ' AND
    pcr.document_line_id = documentlineid AND
    pcr.action_type IN ('MODIFICATION', 'CANCELLATION') AND
    pcr.change_request_group_id = groupid;

Line: 972

  SELECT DISTINCT(request_status)
  FROM
    po_change_requests pcr
  WHERE
    pcr.document_type = 'REQ' AND
    pcr.document_line_id = documentlineid AND
    pcr.action_type IN ('MODIFICATION', 'CANCELLATION') AND
    pcr.change_request_group_id = groupid;

Line: 1011

    SELECT COUNT(distribution_id)
    INTO no_of_values
    FROM po_req_distributions_all
    WHERE requisition_line_id = req_line_id;

Line: 1031

    SELECT MIN(pcr.change_request_group_id)
    INTO grp_id
    FROM
      po_requisition_lines_all prl,
      po_change_requests pcr
    WHERE
      pcr.document_header_id = prl.requisition_header_id
      AND prl.requisition_line_id = reqlineid
      AND pcr.request_status = 'SYSTEMSAVE';

Line: 1041

    SELECT nvl(po_rcotolerance_pvt.get_new_line_quantity(prl.requisition_header_id, prl.requisition_line_id, grp_id), 0)
      INTO changed_quantity
      FROM po_requisition_lines_all prl
      WHERE prl.requisition_line_id = reqlineid ;

Line: 1054

  * This function returns the updated line total for a given requisition   *
  * line id and change request group id.                                   *
  **************************************************************************/
  FUNCTION get_hist_changed_line_total(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
  RETURN NUMBER IS
  matching_basis po_requisition_lines.matching_basis%TYPE := '';

Line: 1062

    SELECT matching_basis
    INTO matching_basis
    FROM po_requisition_lines_all
    WHERE requisition_line_id = reqlineid;

Line: 1080

  * This function returns the updated line price for history page          *
  * for a given requisition line id and change request group id            *
  * if there is no price change, then it returns null                      *
  **************************************************************************/
  FUNCTION get_hist_changed_line_price(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
  RETURN NUMBER IS
  new_price  NUMBER;

Line: 1089

    SELECT new_price
    INTO new_price
    FROM po_change_requests
    WHERE
      change_request_group_id = chgreqgrpid AND
      document_line_id = reqlineid AND
      document_type = 'REQ' AND
      request_level = 'LINE' AND
      new_price IS NOT NULL;

Line: 1117

    SELECT matching_basis
    INTO matching_basis
    FROM po_requisition_lines_all
    WHERE requisition_line_id = reqlineid;

Line: 1143

    SELECT DISTINCT(old_price)
    INTO line_price
    FROM
      po_change_requests
    WHERE
      document_line_id = reqlineid AND
      change_request_group_id = chgreqgrpid AND
      document_type = 'REQ' AND
      old_price IS NOT NULL;

Line: 1173

    SELECT DISTINCT(old_currency_unit_price)
    INTO cur_line_price
    FROM
      po_change_requests
    WHERE
      document_line_id = reqlineid AND
      change_request_group_id = chgreqgrpid AND
      document_type = 'REQ' AND
      old_currency_unit_price IS NOT NULL;

Line: 1188

    SELECT rate INTO l_req_rate
    FROM po_requisition_lines_all
    WHERE requisition_line_id = reqlineid;

Line: 1214

    SELECT DISTINCT(new_currency_unit_price)
    INTO cur_line_price
    FROM
      po_change_requests
    WHERE
      document_line_id = reqlineid AND
      change_request_group_id = chgreqgrpid AND
      document_type = 'REQ' AND
      new_currency_unit_price IS NOT NULL;

Line: 1235

    SELECT rate INTO l_req_rate
    FROM po_requisition_lines_all
    WHERE requisition_line_id = reqlineid;

Line: 1259

    SELECT old_quantity
    INTO line_qty
    FROM
      po_change_requests
    WHERE
      document_line_id = reqlineid AND
      change_request_group_id = chgreqgrpid AND
      document_type = 'REQ' AND
      action_type = 'DERIVED' AND
      old_quantity IS NOT NULL;

Line: 1274

    SELECT quantity
    INTO line_qty
    FROM po_requisition_lines_all
    WHERE requisition_line_id = reqlineid;

Line: 1288

  * This function returns the newly updated line quantity                  *
  * for a specific change request group                                    *
  **************************************************************************/
  FUNCTION get_hist_changed_line_qty(reqlineid IN NUMBER, chgreqgrpid IN NUMBER)
  RETURN NUMBER IS
  line_qty  NUMBER;

Line: 1296

    SELECT new_quantity
    INTO line_qty
    FROM
      po_change_requests
    WHERE
      document_line_id = reqlineid AND
      change_request_group_id = chgreqgrpid AND
      document_type = 'REQ' AND
     ( action_type = 'DERIVED' OR REQUEST_LEVEL = 'LINE') AND
      new_quantity IS NOT NULL;

Line: 1325

    SELECT new_price
    INTO new_price
    FROM
      po_change_requests
    WHERE
      document_line_id = reqlineid AND
      change_request_group_id = chgreqgrpid AND
      document_type = 'REQ' AND
      action_type = 'DERIVED' AND
      new_price IS NOT NULL;

Line: 1353

    SELECT new_currency_unit_price
    INTO new_cur_price
    FROM
      po_change_requests
    WHERE
      document_line_id = reqlineid AND
      change_request_group_id = chgreqgrpid AND
      document_type = 'REQ' AND
      action_type = 'DERIVED' AND
      new_currency_unit_price IS NOT NULL;

Line: 1382

    SELECT nvl(new_currency_unit_price, new_price)
    INTO new_price
    FROM
      po_change_requests
    WHERE
      document_line_id = reqlineid AND
      change_request_group_id = chgreqgrpid AND
      document_type = 'REQ' AND
      action_type = 'DERIVED' AND
      new_price IS NOT NULL;

Line: 1409

    SELECT MAX(change_request_group_id)
    INTO chggroupid
    FROM po_change_requests
    WHERE document_header_id = reqheaderid AND
    document_type = 'REQ';

Line: 1427

    SELECT MAX(change_request_group_id)
    INTO chggroupid
    FROM po_change_requests
    WHERE document_header_id = reqheaderid AND
    document_type = 'REQ';

Line: 1445

    SELECT new_amount
    INTO amount
    FROM po_change_requests
    WHERE
      document_line_id = reqlineid AND
      change_request_group_id = chgreqgrpid AND
      document_type = 'REQ' AND
      action_type = 'DERIVED' AND
      new_amount IS NOT NULL;

Line: 1472

    SELECT old_amount
    INTO amount
    FROM po_change_requests
    WHERE
      document_line_id = reqlineid AND
      change_request_group_id = chgreqgrpid AND
      document_type = 'REQ' AND
      action_type = 'DERIVED' AND
      old_amount IS NOT NULL;

Line: 1486

    SELECT amount
    INTO amount
    FROM po_requisition_lines_all
    WHERE requisition_line_id = reqlineid;

Line: 1508

    SELECT matching_basis
    INTO matching_basis
    FROM po_requisition_lines_all
    WHERE requisition_line_id = reqlineid;

Line: 1531

    SELECT old_currency_amount
    INTO amount
    FROM po_change_requests
    WHERE
      document_line_id = reqlineid AND
      change_request_group_id = chgreqgrpid AND
      document_type = 'REQ' AND
      action_type = 'DERIVED' AND
      old_currency_amount IS NOT NULL;

Line: 1545

    SELECT nvl(currency_amount, amount)
    INTO amount
    FROM po_requisition_lines_all
    WHERE requisition_line_id = reqlineid;

Line: 1572

      SELECT new_currency_unit_price
      INTO cur_unit_price
      FROM po_change_requests
      WHERE
        document_line_id = reqlineid AND
        document_type = 'REQ' AND
        action_type = 'MODIFICATION' AND
        request_status = 'SYSTEMSAVE' AND
        new_currency_unit_price IS NOT NULL;

Line: 1588

    SELECT currency_unit_price
    INTO cur_unit_price
    FROM po_requisition_lines_all
    WHERE
      requisition_line_id = reqlineid;

Line: 1611

    SELECT matching_basis
    INTO matching_basis
    FROM po_requisition_lines_all
    WHERE requisition_line_id = reqlineid;

Line: 1634

    SELECT new_currency_amount
    INTO amount
    FROM po_change_requests
    WHERE
      document_line_id = reqlineid AND
      change_request_group_id = chgreqgrpid AND
      document_type = 'REQ' AND
      action_type = 'DERIVED' AND
      new_currency_amount IS NOT NULL;

Line: 1660

    SELECT old_currency_amount
    INTO amount
    FROM po_change_requests
    WHERE
      document_distribution_id = reqdistid AND
      change_request_group_id = chgreqgrpid AND
      document_type = 'REQ';

Line: 1680

    SELECT new_currency_amount
    INTO amount
    FROM po_change_requests
    WHERE
      document_distribution_id = reqdistid AND
      change_request_group_id = chgreqgrpid AND
      document_type = 'REQ';

Line: 1710

    SELECT SUM(prd.req_line_quantity * prl.unit_price)
     INTO old_line_total
  FROM
    po_requisition_lines_all prl,
    po_req_distributions prd
  WHERE
    prl.requisition_line_id = reqlineid AND
    prl.requisition_line_id =  prd.requisition_line_id;

Line: 1747

    SELECT SUM(prd.req_line_quantity * prl.unit_price)
    INTO old_line_total
  FROM
    po_requisition_lines_all prl,
    po_req_distributions_all prd
  WHERE
    prl.requisition_line_id = reqlineid AND
    prl.requisition_line_id =  prd.requisition_line_id;