APPS.OE_OE_TOTALS_SUMMARY SQL Statements

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

Line: 45

 SELECT   -1 * decode(opa.arithmetic_operator,
                null, 0,
                '%', opa.operand*ool.unit_list_price/100,
                'AMT',opa.operand,
                'NEWPRICE',ool.unit_list_price - opa.operand)
      INTO adj_line_total_order_modifier
      FROM      oe_price_adjustments opa
                        , oe_order_lines_all ool
      WHERE     opa.HEADER_ID = x_header_id
                        and opa.line_id is null
                        and ool.line_id = x_line_id
                        and ool.header_id = x_header_id
                        and opa.list_line_id = x_list_line_id
                        and nvl(opa.applied_flag,'N') = 'Y'
                        and nvl(opa.accrual_flag,'N') = 'N'
                        and list_line_type_code in ('DIS','SUR','PBH');

Line: 84

 SELECT   -1 * decode(opa.arithmetic_operator,
                null, 0,
                '%', opa.operand*ool.unit_list_price/100,
                'AMT',opa.operand,
                'NEWPRICE',ool.unit_list_price - opa.operand) * NVL(ool.ordered_quantity,0)
      INTO ext_line_tot_order_modifier
      FROM      oe_price_adjustments opa
                        , oe_order_lines_all ool
      WHERE     opa.HEADER_ID = x_header_id
                        and opa.line_id is null
                        and ool.line_id = x_line_id
                        and ool.header_id = x_header_id
                        and opa.list_line_id = x_list_line_id
                        and nvl(opa.applied_flag,'N') = 'Y'
                        and nvl(opa.accrual_flag,'N') = 'N'
                        and list_line_type_code in ('DIS','SUR','PBH');

Line: 252

   SELECT NVL(Ordered_Quantity,0)*
		NVL(unit_selling_price,0) Line_details_total,Line_Number,
		Line_Category_Code
   FROM   oe_order_lines_all
   WHERE  header_id=p_header_id
   AND (line_number=p_line_number
   AND NVL(cancelled_flag,'N') ='N'
   OR (top_model_line_id is not null
   AND top_model_line_id=p_line_id
   AND NVL(cancelled_flag,'N') ='N')
   OR (service_reference_line_id is not null
   AND service_reference_line_id=p_line_id
   AND NVL(cancelled_flag,'N') ='N'));

Line: 272

   SELECT NVL(ordered_quantity,0)-NVL(cancelled_quantity,0)*
		NVL(unit_selling_price,0) shipment_total,Line_Number
   FROM   oe_order_lines_all
   WHERE  header_id=header_id
   AND    Line_Number=Line_Number
   AND    Shipment_Number=shipment_number
   AND    cancelled_flag='N'; */

Line: 316

   SELECT NVL(Ordered_Quantity,0)*
		NVL(unit_selling_price,0) Line_details_total,Line_Number,
		Line_Category_Code
   FROM   oe_order_lines_all
   WHERE  header_id=p_header_id
   AND line_number=p_line_number
   AND service_number=p_service_number
   AND NVL(cancelled_flag,'N') ='N'
   AND item_type_code = 'SERVICE';

Line: 371

SELECT SUM(nvl(ROUND(decode(oel.line_category_code,'RETURN',-oel.tax_value,oel.tax_value), OE_ORDER_UTIL.G_Precision),0)),
       SUM(ROUND(nvl(oel.Ordered_Quantity,0) * (oel.unit_selling_price) * (decode(oel.line_category_code,'RETURN',-1,1)),OE_ORDER_UTIL.G_Precision))
INTO   G_TAX_VALUE, G_TOTAL_EXTENDED_PRICE
FROM   oe_order_lines_all oel
WHERE  oel.header_id=p_header_id
AND charge_periodicity_code is NULL  -- added for recurring charges
AND    NVL(oel.cancelled_flag,'N') ='N';

Line: 407

  SELECT SUM(nvl(Ordered_Quantity,0)
	   *(unit_selling_price))
  INTO  l_config_total
  FROM oe_order_lines_all
  WHERE line_id=p_line_id
  AND NVL(cancelled_flag,'N') ='N';

Line: 437

   SELECT NVL(Ordered_quantity,0) Qty
   FROM   oe_order_lines_all
   WHERE  header_id=p_header_id
   AND NVL(cancelled_flag,'N') ='N'
   AND line_number=p_line_number
   AND item_type_code in ('STANDARD','MODEL','KIT')
--   And option_number is null
   and line_id = nvl(top_model_line_id,line_id);

Line: 578

  SELECT
    SUM(ROUND(nvl(ool.tax_value,0), OE_ORDER_UTIL.G_Precision))
  , SUM(ROUND(nvl(ool.Ordered_Quantity,0)
	   *(ool.unit_selling_price), OE_ORDER_UTIL.G_Precision))
  INTO
    l_tax_total
  , l_order_total
  FROM  oe_order_lines_all ool
  WHERE ool.header_id      = p_header_id
  AND   ( (ool.open_flag  = 'Y' AND p_all_lines is null)
        OR nvl(p_all_lines, 'N') = 'Y' )
  AND   ool.line_category_code <> 'RETURN'
  AND   ool.charge_periodicity_code is null -- Added for Recurring Charges
  AND   NOT EXISTS
       (SELECT 'Non Invoiceable Item Line'
        FROM   mtl_system_items mti
        WHERE  mti.inventory_item_id = ool.inventory_item_id
        AND    mti.organization_id   = nvl(ool.ship_from_org_id,
                         oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
        AND   (mti.invoiceable_item_flag = 'N'
           OR  mti.invoice_enabled_flag  = 'N'));

Line: 602

    SELECT SUM(ROUND(nvl(op.commitment_applied_amount,0), OE_ORDER_UTIL.G_Precision))
    INTO   l_commitment_total
    FROM   oe_payments op
    WHERE  op.header_id = p_header_id
    AND    NOT EXISTS
          (SELECT 'Non Invoiceable Item Line'
           FROM   mtl_system_items mti, oe_order_lines_all ool
           WHERE  ool.line_id           = op.line_id
           AND    mti.inventory_item_id = ool.inventory_item_id
           AND    mti.organization_id   = nvl(ool.ship_from_org_id,
                          oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
           AND   (mti.invoiceable_item_flag = 'N'
              OR  mti.invoice_enabled_flag  = 'N'));

Line: 617

  SELECT SUM(ROUND(nvl(ool.Ordered_Quantity,0) *(ool.unit_selling_price), OE_ORDER_UTIL.G_Precision))
  INTO   l_commitment_total
  FROM   oe_order_lines_all ool
  WHERE  ool.header_id      = p_header_id
  AND    ool.commitment_id is not null
  AND   ( (ool.open_flag  = 'Y' AND p_all_lines is null)
        OR nvl(p_all_lines, 'N') = 'Y' )
  AND   ool.charge_periodicity_code is null -- Added for Recurring Charges
  AND    ool.line_category_code <> 'RETURN'
  AND   NOT EXISTS
       (SELECT 'Non Invoiceable Item Line'
        FROM   mtl_system_items mti
        WHERE  mti.inventory_item_id = ool.inventory_item_id
        AND    mti.organization_id   = nvl(ool.ship_from_org_id,
                         oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
        AND   (mti.invoiceable_item_flag = 'N'
           OR  mti.invoice_enabled_flag  = 'N'));

Line: 638

     SELECT SUM(
                ROUND(
                      DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',-P.OPERAND,P.OPERAND), OE_ORDER_UTIL.G_Precision
                )
               )
     INTO l_chgs_wo_line_id
     FROM OE_PRICE_ADJUSTMENTS P
     WHERE P.HEADER_ID = p_header_id
     AND   P.LINE_ID IS NULL
     AND   P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
     AND   P.APPLIED_FLAG = 'Y'
     --Bug 6072691
     --AND   NVL(P.INVOICED_FLAG, 'N') = 'N';

Line: 664

     SELECT SUM(
                ROUND(
                      DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',
                        DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
                               -P.OPERAND,
                               (-L.ORDERED_QUANTITY*P.ADJUSTED_AMOUNT)),
                        DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
                               P.OPERAND,
                               (L.ORDERED_QUANTITY*P.ADJUSTED_AMOUNT))
                             )
                     ,OE_ORDER_UTIL.G_Precision
                     )
              )
     INTO l_chgs_w_line_id
     FROM OE_PRICE_ADJUSTMENTS P,
          OE_ORDER_LINES_ALL L
     WHERE P.HEADER_ID = p_header_id
     AND   P.LINE_ID = L.LINE_ID
     AND   P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
     AND   P.APPLIED_FLAG = 'Y'
     AND   L.charge_periodicity_code is null -- Added for Recurring Charges
     AND   L.header_id      = p_header_id
     AND   ( (L.open_flag  = 'Y' AND p_all_lines is null)
           OR nvl(p_all_lines, 'N') = 'Y' )
     AND   L.line_category_code <> 'RETURN'
     AND   NOT EXISTS
          (SELECT 'Non Invoiceable Item Line'
           FROM   MTL_SYSTEM_ITEMS MTI
           WHERE  MTI.INVENTORY_ITEM_ID = L.INVENTORY_ITEM_ID
           AND    MTI.ORGANIZATION_ID   = NVL(L.SHIP_FROM_ORG_ID,
                         oe_sys_parameters.value('MASTER_ORGANIZATION_ID', L.org_id))
           AND   (MTI.INVOICEABLE_ITEM_FLAG = 'N'
              OR  MTI.INVOICE_ENABLED_FLAG  = 'N'))
     --Bug 6072691
     --AND   NVL(P.INVOICED_FLAG, 'N') = 'N';

Line: 774

    SELECT  SUM(nvl(ool.Ordered_Quantity,0)
 	   *(ool.unit_selling_price))
    INTO   l_order_subtotal
    FROM  oe_order_lines_all ool
    WHERE ool.header_id      = p_header_id
    AND   ool.open_flag      = 'Y'
    AND   ool.charge_periodicity_code is null -- Added for Recurring Charges
    AND   ool.line_category_code <> 'RETURN'
    AND   NOT EXISTS
         (SELECT 'Non Invoiceable Item Line'
          FROM   mtl_system_items mti
          WHERE  mti.inventory_item_id = ool.inventory_item_id
          AND    mti.organization_id   = nvl(ool.ship_from_org_id,
                     oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
          AND   (mti.invoiceable_item_flag = 'N'
                 OR  mti.invoice_enabled_flag  = 'N'));

Line: 828

  SELECT SUM(DECODE(line_category_code, 'RETURN', 0, nvl(Ordered_Quantity,0)*(unit_list_price))),
         SUM(DECODE(line_category_code, 'RETURN', nvl(Ordered_Quantity,0)*(unit_list_price),0))
  INTO orders_total, returns_total
  FROM oe_order_lines_all
  WHERE header_id=p_header_id
  AND   charge_periodicity_code is null -- Added for Recurring CHarges
  AND NVL(cancelled_flag,'N') ='N';

Line: 960

SELECT SUM(nvl(ROUND(decode(oel.line_category_code,'RETURN',-oel.tax_value,oel.tax_value), OE_ORDER_UTIL.G_Precision),0)),
       SUM(ROUND(nvl(oel.Ordered_Quantity,0) * (oel.unit_selling_price) * (decode(oel.line_category_code,'RETURN',-1,1)),OE_ORDER_UTIL.G_Precision))
INTO   G_REC_TAX_VALUE, G_REC_TOTAL_EXTENDED_PRICE
FROM   oe_order_lines_all oel
WHERE  oel.header_id=p_header_id
AND nvl(charge_periodicity_code,'ONE') = p_charge_periodicity_code
AND    NVL(oel.cancelled_flag,'N') ='N';

Line: 1141

        select distinct charge_periodicity_code
        from oe_order_lines_all
        where header_id = g_header_id
        and charge_periodicity_code is not null
        order by charge_periodicity_code;

Line: 1222

        select distinct nvl(charge_periodicity_code,'ONE')
        from oe_order_lines_all
        where header_id = g_header_id
      --  and charge_periodicity_code is not null
        order by nvl(charge_periodicity_code,'ONE');

Line: 1308

        select distinct nvl(charge_periodicity_code,'ONE')
        from oe_order_lines_all
        where header_id = g_header_id
 --       and charge_periodicity_code is not null
        and line_number  = p_line_number
        order by nvl(charge_periodicity_code,'ONE') desc;

Line: 1316

   SELECT NVL(Ordered_Quantity,0)*
                NVL(unit_selling_price,0) Line_details_total,tax_value,line_category_code
   FROM   oe_order_lines_all
   WHERE  header_id=p_header_id
   AND    nvl(charge_periodicity_code,'ONE')=p_code  -- added abghosh
   AND (line_number=p_line_number
   AND NVL(cancelled_flag,'N') ='N'
   OR (top_model_line_id is not null
   AND top_model_line_id=p_line_id
--   AND charge_periodicity_code=p_code    -- commented abghosh
   AND NVL(cancelled_flag,'N') ='N')
   OR (service_reference_line_id is not null
   AND service_reference_line_id=p_line_id
   AND NVL(cancelled_flag,'N') ='N'));

Line: 1389

     SELECT SUM(ROUND(
                DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',
                        DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
                               DECODE(L.ORDERED_QUANTITY,0,0,-P.OPERAND),
                               (-L.ORDERED_QUANTITY* nvl(P.ADJUSTED_AMOUNT,0))),
                        DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
                               DECODE(L.ORDERED_QUANTITY,0,0,P.OPERAND),
                               (L.ORDERED_QUANTITY* nvl(P.ADJUSTED_AMOUNT,0)))
                       )
                  ,OE_ORDER_UTIL.G_Precision)
                 )
      INTO x_charges
      FROM OE_PRICE_ADJUSTMENTS P,
           OE_ORDER_LINES_ALL L
      WHERE P.HEADER_ID = p_header_id
      AND   P.LINE_ID = L.LINE_ID
      AND   nvl(L.CHARGE_PERIODICITY_CODE,'ONE') = l_code
      AND   P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
      AND   P.APPLIED_FLAG = 'Y'
      AND (l.line_number=p_line_number
      AND NVL(l.cancelled_flag,'N') ='N'
      OR (l.top_model_line_id is not null
      AND l.top_model_line_id=p_line_id
      AND NVL(l.cancelled_flag,'N') ='N')
      OR (l.service_reference_line_id is not null
      AND l.service_reference_line_id=p_line_id
      AND NVL(l.cancelled_flag,'N') ='N'));

Line: 1462

SELECT DISTINCT charge_periodicity_code
FROM oe_order_lines_all
WHERE header_id = p_header_id
AND charge_periodicity_code is not null
ORDER BY charge_periodicity_code;

Line: 1483

	    SELECT header_id INTO l_header_id
	    FROM oe_price_adjustments
	    WHERE price_adjustment_id = p_price_adjustment_id;

Line: 1507

   G_RECURRING_AMOUNTS_TBL.DELETE;