CREATE PROCEDURE [dbo].[create_insert_dim_transactions_receipt]
    AS
BEGIN
    DROP TABLE IF EXISTS dim_transactions_receipt;
    create table dim_transactions_receipt
    (
        id int not null,
        transaction_id varchar(250) not null,
        bank_account_id int null,
        account_id int null,
        customer_id int null,
        ignore_from_outstanding int default 0,
        transaction_date int null,
        transaction_amount money null,
        tds_amount money null,
        other_deductions money null,
        total_amount money null,
        description text null,
        currency_code varchar(255) null,
        currency_id int,
        is_new_billing bit default 0,
        receipt_url varchar(255) null,
        receipt_no varchar(255) null,
        exchange_rate money default 1.0,
        created_on int null
    );
    create index dim_transactions_receipt_transaction_date_index
        on dim_transactions_receipt (transaction_date);

 create index dim_transactions_receipt_created_on_index
        on dim_transactions_receipt (created_on);

create index dim_transactions_receipt_customer_id_index
        on dim_transactions_receipt (customer_id);

INSERT INTO dim_transactions_receipt
select
details_id,
       transaction_id,t.account_id,fa.account_id, fa.customer_id,fa.ignore_from_outstanding,
CONVERT(varchar,transaction_date,112),
       transaction_amount,tds_amount,other_deductions, total_amount,
       description,
       t.code,
       cm.currency_id,
       is_new_billing,
       receipt_url,
       receipt_no,
       exchange_rate,
       CONVERT(varchar,created_on,112)
from mis_billing_transactions_receipt as t
left join mis_customer_accounts fa on fa.account_id = t.client_id
left join mis_currency_master as cm on cm.currency_symbol = t.code
END;
     UPDATE last_updated_at_history set last_updated_at = getdate() where sp_name = 'create_insert_dim_transactions_receipt';
go

