postgres_view.txt

CREATE OR REPLACE VIEW 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) 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,
    coalesce(CASE WHEN 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' THEN  6 END , 0),
    price,
    equipment,
    lane_count,
    lane_estimate,
    TRANSACTION_ID,
    -- convert 0 to NULL [TRI-5491]
    CASE WHEN SUBMISSION_ID=0 THEN  NULL  ELSE SUBMISSION_ID END
FROM
    rateindex.transcycle;