oracle_view.txt

CREATE OR REPLACE FORCE VIEW CSBTFSPRD.BILLED_RATE_TRANSACTIONS
(
    ACCOUNT_ID,
    COMPANY_ID,
    AUTHENTICATING_ORDER_ID,
    AUTHENTICATING_PRODUCT_ID,
    AUTHENTICATING_PRICE_CLASS,
    AUTHORIZING_ORDER_ID,
    AUTHORIZING_PRODUCT_ID,
    AUTHORIZING_PRICE_CLASS,
    START_DATE,
    END_DATE,
    TYPE_CODE,
    PRICE,
    EQUIPMENT,
    LANE_COUNT,
    LANE_ESTIMATE,
    TRANSACTION_ID,
    SUBMISSION_ID
)
BEQUEATH DEFINER
AS
    SELECT account_id,
           company_id,
           order_id,
           product_id,
           price_class,
           authorizing_order_id,
           authorizing_subscription_type,
           authorizing_price_class,
           start_date,
           end_date,
           NVL (
               DECODE (impact_category,
                       'BULK_RATE_REQUEST', 1, -- To handle Portal's category for the old RIP
                       'CONTRACT_BULK_RATE', 1,
                       'SPOT_BULK_RATE', 2,
                       'CONTRACT_HISTORY', 3,
                       'SPOT_HISTORY', 4,
                       'RATE_SUBMISSION', 5,
                       'SPOT_BACKHAUL', 6),
               0),
           price,
           equipment,
           lane_count,
           lane_estimate,
           TRANSACTION_ID,
           -- convert 0 to NULL [TRI-5491]
           DECODE (SUBMISSION_ID, 0, NULL, SUBMISSION_ID)
      FROM rateindex.transcycle;