APPS.PO_VAL_LINES SQL Statements

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

Line: 282

INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, column_name
, column_val
, message_name
, token1_name
, token1_value
)
SELECT
  x_result_set_id
, c_ENTITY_TYPE_LINE
, p_line_id_tbl(i)
, c_QUANTITY
, TO_CHAR(p_quantity_tbl(i))
, (CASE
     WHEN POLL_TOTAL.qty_bill_actuals = POLL_TOTAL.qty_executed
       THEN PO_MESSAGE_S.PO_PO_QTY_ORD_LT_QTY_BILLED_NA
     ELSE PO_MESSAGE_S.PO_PO_QTY_ORD_LT_QTY_RCVD_NA
   END
  )
, (CASE
     WHEN POLL_TOTAL.qty_bill_actuals = POLL_TOTAL.qty_executed
       THEN PO_MESSAGE_S.c_QTY_BILLED_token
     ELSE PO_MESSAGE_S.c_QTY_RCVD_token
   END
  )
, (CASE
     WHEN POLL_TOTAL.qty_bill_actuals = POLL_TOTAL.qty_executed
       THEN TO_CHAR(POLL_TOTAL.qty_bill_actuals)
     ELSE TO_CHAR(POLL_TOTAL.qty_recv_actuals)
   END
  )
FROM
  ( SELECT
      sum_qty_recv_actuals qty_recv_actuals,
      sum_qty_bill_actuals qty_bill_actuals,
      GREATEST(sum_qty_recv_actuals, sum_qty_bill_actuals) qty_executed
    FROM
    ( SELECT
        NVL(SUM(
             (CASE
                WHEN PLL.shipment_type <> c_STANDARD THEN 0
                ELSE NVL(PLL.quantity_received, 0)
              END)), 0) sum_qty_recv_actuals,
        NVL(SUM(
             (CASE
                WHEN PLL.shipment_type <> c_STANDARD THEN 0
                ELSE GREATEST(NVL(PLL.quantity_billed, 0),
                                 NVL(PLL.quantity_financed, 0))
              END)), 0) sum_qty_bill_actuals
      FROM PO_LINE_LOCATIONS_ALL PLL
      WHERE PLL.po_line_id = p_line_id_tbl(i)
        AND NVL(PLL.payment_type, c_DELIVERY) <> c_MILESTONE
    )
  ) POLL_TOTAL, PO_LINES_ALL POL
WHERE
    POL.po_line_id = p_line_id_tbl(i)
AND p_quantity_tbl(i) IS NOT NULL
-- Quantity is being reduced below the current transaction quantity:
AND p_quantity_tbl(i) < POL.quantity
AND p_quantity_tbl(i) < POLL_TOTAL.qty_executed
;

Line: 449

INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, column_name
, column_val
, message_name
--PBWC Message Change Impact: Removing a token
, token1_name
, token1_value
--, token2_name
--, token2_value
)
SELECT
  x_result_set_id
, c_ENTITY_TYPE_LINE
, p_line_id_tbl(i)
, c_QUANTITY
, TO_CHAR(p_quantity_tbl(i))
, PO_MESSAGE_S.PO_PO_QTY_ORD_LT_QTY_ENC_NA
--PBWC Message Change Impact: Removing a token
--, PO_MESSAGE_S.c_QTY_ORD_token
--, TO_CHAR(p_quantity_tbl(i))
, PO_MESSAGE_S.c_QTY_ENC_token
, TO_CHAR(DIST_TOTAL.quantity_encumbered)
FROM
  ( SELECT NVL(SUM(POD.quantity_ordered),0) quantity_encumbered
    FROM
      PO_DISTRIBUTIONS_ALL POD
    WHERE
        POD.po_line_id = p_line_id_tbl(i)
    AND POD.distribution_type IN (c_STANDARD,c_PLANNED)
    AND POD.encumbered_flag = 'Y'
  ) DIST_TOTAL
WHERE
    p_quantity_tbl(i) IS NOT NULL
AND p_quantity_tbl(i) < DIST_TOTAL.quantity_encumbered
;

Line: 613

INSERT INTO PO_SESSION_GT
( key
, index_num1  -- po_line_id
, char1       -- currency_code
, num1        -- minimum_accountable_unit
, num2        -- precision
)
SELECT
l_gt_key
, p_line_id_tbl(i)
, p_currency_code_tbl(i)
, cur.minimum_accountable_unit
, cur.precision
FROM
fnd_currencies cur
WHERE
cur.currency_code = p_currency_code_tbl(i)
;

Line: 635

INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, column_name
, column_val
, message_name
--PBWC Message Change Impact: Adding a token
, token1_name
, token1_value
)
SELECT
  x_result_set_id
, c_ENTITY_TYPE_LINE
, p_line_id_tbl(i)
, c_AMOUNT
, TO_CHAR(p_amount_tbl(i))
, (CASE
     WHEN POLL_TOTAL.amt_bill_actuals = POLL_TOTAL.amt_executed
       THEN PO_MESSAGE_S.PO_PO_AMT_ORD_LT_AMT_BILLED_NA
     ELSE PO_MESSAGE_S.PO_PO_AMT_ORD_LT_AMT_RCVD_NA
   END
  )
--PBWC Message Change Impact: Adding a token
, (CASE
     WHEN POLL_TOTAL.amt_bill_actuals = POLL_TOTAL.amt_executed
     THEN PO_MESSAGE_S.c_AMT_BILLED_TOKEN
     ELSE PO_MESSAGE_S.c_AMT_RCVD_TOKEN
   END
  )
, (CASE
     WHEN POLL_TOTAL.amt_bill_actuals = POLL_TOTAL.amt_executed
     THEN TO_CHAR(POLL_TOTAL.amt_bill_actuals)
     ELSE TO_CHAR(POLL_TOTAL.amt_recv_actuals)
   END
  )
--End PBWC Message Change Impact: Adding a token
FROM
  ( SELECT
      sum_amt_recv_actuals amt_recv_actuals,
      sum_amt_bill_actuals amt_bill_actuals,
      GREATEST(sum_amt_recv_actuals, sum_amt_bill_actuals) amt_executed
    FROM
    ( SELECT
      NVL(SUM(
          (CASE
             WHEN PLL.shipment_type <> c_STANDARD
               THEN 0
             WHEN PLL.payment_type = c_RATE
               THEN
	         CASE
	           WHEN gtt.num1 IS NOT NULL THEN
	             -- Round to minimum accountable unit.
	             ROUND(
	                   NVL(PLL.quantity_received*PLL.price_override,0) / gtt.num1
	                  ) * gtt.num1
	           ELSE
	             -- Round to currency precision.
	             ROUND(  NVL(PLL.quantity_received*PLL.price_override,0)
	                   , gtt.num2)
	           END
             ELSE NVL(PLL.amount_received, 0)
           END)), 0) sum_amt_recv_actuals,
      NVL(SUM(
          (CASE
             WHEN PLL.shipment_type <> c_STANDARD
               THEN 0
             WHEN PLL.payment_type = c_RATE
	       THEN
	         CASE
	           WHEN gtt.num1 IS NOT NULL THEN
	             -- Round to minimum accountable unit.
	             ROUND(
	                   NVL(PLL.quantity_billed*PLL.price_override,0) / gtt.num1
	             ) * gtt.num1
	           ELSE
	             -- Round to currency precision.
	             ROUND(  NVL(PLL.quantity_billed*PLL.price_override,0)
	                    , gtt.num2)
	           END
             ELSE GREATEST(NVL(PLL.amount_billed, 0),
                             NVL(PLL.amount_financed, 0))
           END)), 0) sum_amt_bill_actuals
      FROM PO_LINE_LOCATIONS_ALL PLL
         , PO_SESSION_GT GTT
      WHERE PLL.po_line_id    = p_line_id_tbl(i)
      AND   GTT.key           = l_gt_key
      AND   GTT.index_num1(+) = PLL.po_line_id
    )
  ) POLL_TOTAL
  , PO_LINES_ALL POL
WHERE
    POL.po_line_id = p_line_id_tbl(i)
AND p_amount_tbl(i) IS NOT NULL
-- Amount is being reduced below the current transaction amount:
AND p_amount_tbl(i) < POL.amount
AND p_amount_tbl(i) < POLL_TOTAL.amt_executed
;

Line: 804

INSERT INTO PO_SESSION_GT
( key
, num1
, num2
)
VALUES
( l_data_key
, p_line_id_tbl(i)
, p_amount_tbl(i)
)
;

Line: 816

SELECT
  SES.num1
, SES.num2
BULK COLLECT INTO
  l_line_id_tbl
, l_amount_tbl
FROM
  PO_SESSION_GT SES
, PO_LINES_ALL LINE
, PO_HEADERS_ALL HEADER
WHERE
    SES.key = l_data_key
AND LINE.po_line_id = SES.num1
AND HEADER.po_header_id = LINE.po_header_id
AND HEADER.type_lookup_code = c_STANDARD
AND LINE.order_type_lookup_code = c_RATE
AND SES.num2 < LINE.amount
;

Line: 996

  INSERT INTO PO_VALIDATION_RESULTS_GT
  ( result_set_id
  , result_type
  , entity_type
  , entity_id
  , column_name
  , column_val
  , message_name
  )
  SELECT
    x_result_set_id
  , PO_VALIDATIONS.c_result_type_WARNING
  , c_ENTITY_TYPE_LINE
  , p_line_id_tbl(i)
  , c_ITEM_ID
  , TO_CHAR(p_item_id_tbl(i))
  , PO_MESSAGE_S.PO_VMI_ASL_EXIST
  FROM
    PO_APPROVED_SUPPLIER_LIS_VAL_V PASL
  , PO_ASL_ATTRIBUTES PAA
  , PO_ASL_STATUS_RULES_V PASR
  WHERE
  -- item is not null
      p_item_id_tbl(i) IS NOT NULL
  -- Document is standard PO
  AND p_type_lookup_code_tbl(i) = c_STANDARD

  --VMI is enabled
  AND paa.enable_vmi_flag = 'Y'
  AND pasl.item_id = p_item_id_tbl(i)
  AND pasl.vendor_id = p_vendor_id_tbl(i)
  AND nvl(pasl.vendor_site_id,-1) = nvl(p_vendor_site_id_tbl(i),-1)
  AND pasl.using_organization_id IN (p_org_id_tbl(i), -1)
  AND pasl.asl_id = paa.asl_id
  AND pasr.business_rule = c_2_SOURCING
  AND pasr.allow_action_flag = 'Y'
  AND pasr.status_id = pasl.asl_status_id
  AND paa.using_organization_id =
            (SELECT max(paa2.using_organization_id)
             FROM   po_asl_attributes paa2
             WHERE  paa2.asl_id = pasl.asl_id
             AND    paa2.using_organization_id IN (-1, p_org_id_tbl(i)));

Line: 1082

, p_column_val_selector => NULL
, p_message_name => PO_MESSAGE_S.PO_SVC_ASSIGNMENT_DATES
, x_results => x_results
, x_result_type => x_result_type
);

Line: 1312

INSERT INTO PO_SESSION_GT
( key
, num1
, num2
, char1
)
VALUES
( l_data_key
, p_line_id_tbl(i)
, p_unit_price_tbl(i)
, p_price_break_lookup_code_tbl(i)
)
;

Line: 1327

SELECT
  SES.num1
, SES.char1
BULK COLLECT INTO
  l_line_id_tbl
, l_price_break_lookup_code_tbl
FROM
  PO_SESSION_GT SES
, PO_LINES_ALL SAVED_LINE
WHERE
    SES.key = l_data_key
AND SAVED_LINE.po_line_id = SES.num1
AND SAVED_LINE.order_type_lookup_code IN (c_QUANTITY, c_RATE)
AND (   SES.num2 <> SAVED_LINE.unit_price
    OR  (SES.num2 IS NULL AND SAVED_LINE.unit_price IS NOT NULL)
    OR  (SES.num2 IS NOT NULL AND SAVED_LINE.unit_price IS NULL)
    )
;

Line: 1403

, p_column_val_selector => PO_VALIDATION_HELPER.c_END_DATE
, p_message_name => PO_MESSAGE_S.POX_EXPIRATION_DATES
, x_results => x_results
, x_result_type => x_result_type
);

Line: 1435

, p_column_val_selector => PO_VALIDATION_HELPER.c_START_DATE
, p_message_name => PO_MESSAGE_S.POX_EXPIRATION_DATES
, x_results => x_results
, x_result_type => x_result_type
);

Line: 1552

INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, column_name
, column_val
, message_name
)
SELECT
  x_result_set_id
, c_ENTITY_TYPE_LINE
, p_line_id_tbl(i)
, c_QUANTITY
, TO_CHAR(p_quantity_tbl(i))
, (CASE
     WHEN POLL_TOTAL.max_qty_bill = POLL_TOTAL.max_qty_executed
       THEN 'CWPOTODOMESSAGE'
     ELSE 'CWPOTODOMESSAGE'
   END
  )
FROM
  ( SELECT
       max_qty_recv,
       max_qty_bill,
       GREATEST(max_qty_recv, max_qty_bill) max_qty_executed
    FROM
    ( SELECT
        NVL(MAX(quantity_received), 0) max_qty_recv,
        NVL(MAX(quantity_billed), 0) max_qty_bill
      FROM PO_LINE_LOCATIONS_ALL PLL
      WHERE PLL.po_line_id = p_line_id_tbl(i)
        AND PLL.payment_type = c_MILESTONE
        AND PLL.value_basis = c_QUANTITY
        AND PLL.shipment_type = c_STANDARD
    )
  ) POLL_TOTAL, PO_LINES_ALL POL
WHERE
    POL.po_line_id = p_line_id_tbl(i)
AND p_quantity_tbl(i) IS NOT NULL
AND p_quantity_tbl(i) < POL.quantity
AND p_quantity_tbl(i) < POLL_TOTAL.max_qty_executed
;

Line: 1646

INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, column_name
, column_val
, message_name
)
SELECT
  x_result_set_id
, c_ENTITY_TYPE_LINE
, p_line_id_tbl(i)
, c_UNIT_PRICE
, TO_CHAR(p_price_tbl(i))
, 'CWPOTODOMESSAGE'
FROM
    ( SELECT
       NVL(SUM(CASE
                WHEN PLL.quantity_received > 0
                  THEN NVL(PLL.price_override, 0)
                WHEN PLL.quantity_billed > 0
                  THEN NVL(PLL.price_override, 0)
                ELSE 0
               END),0) sum_price_executed
      FROM PO_LINE_LOCATIONS_ALL PLL
      WHERE PLL.po_line_id = p_line_id_tbl(i)
        AND PLL.payment_type = c_MILESTONE
        AND PLL.value_basis = c_QUANTITY
        AND PLL.shipment_type = c_STANDARD
    ) POLL_TOTAL, PO_LINES_ALL POL
WHERE
    POL.po_line_id = p_line_id_tbl(i)
AND p_price_tbl(i) IS NOT NULL
AND p_price_tbl(i) < POL.unit_price
AND p_price_tbl(i) < POLL_TOTAL.sum_price_executed
;

Line: 1887

  INSERT INTO PO_VALIDATION_RESULTS_GT
  ( result_set_id
  , entity_type
  , entity_id
  , column_name
  , message_name
  )
  SELECT
    x_result_set_id
  , c_ENTITY_TYPE_LINE
  , l_id_tbl(i)
  , c_ITEM_ID
  , p_message_name
  FROM
    PO_LINE_TYPES_B PLT
  WHERE
      PLT.line_type_id = l_line_type_id_tbl(i)
  AND PLT.outside_operation_flag = 'Y'
  ;

Line: 2168

    select inventory_organization_id
    into l_def_inv_org_id
    from financials_system_parameters;

Line: 2234

    select inventory_organization_id
    into l_def_inv_org_id
    from financials_system_parameters;

Line: 2312

    SELECT type_lookup_code
    INTO l_src_doc_type_lookup_code
    FROM po_headers_all
    WHERE po_header_id = l_from_header_id;

Line: 2414

INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, column_name
, column_val
, message_name
, token1_name
, token1_value
, token2_name
, token2_value
)
SELECT
  x_result_set_id
, c_ENTITY_TYPE_LINE
, p_line_id_tbl(i)
, c_MAX_RETAINAGE_AMOUNT
, TO_CHAR(p_max_retain_amt_tbl(i))
, PO_MESSAGE_S.PO_MAX_RET_AMT_GE_RETAINED
, PO_MESSAGE_S.c_MAX_RET_AMT_token
, TO_CHAR(p_max_retain_amt_tbl(i))
, PO_MESSAGE_S.c_AMT_RETAINED_token
, TO_CHAR(LOCATIONS_TOTAL.amount_retained)
FROM
  ( SELECT NVL(SUM(POLL.retainage_withheld_amount),0) amount_retained
    FROM
      PO_LINE_LOCATIONS_ALL POLL
    WHERE
        POLL.po_line_id = p_line_id_tbl(i)
  ) LOCATIONS_TOTAL
WHERE
 p_max_retain_amt_tbl(i) < LOCATIONS_TOTAL.amount_retained
;