
CREATE  VIEW `erp_cash_flow`  AS  select `inv`.`id` AS `id`,`inv`.`invoice_no` AS `invoice_no`,`inv`.`invoice_date` AS `invoice_date`,`pay`.`payment_terms` AS `payment_terms`,`pay`.`no_of_days` AS `no_of_days`,`cust`.`customer_name` AS `customer_name`,(`inv`.`invoice_date` + interval `pay`.`no_of_days` day) AS `receive_date`,(to_days(curdate()) - to_days((`inv`.`invoice_date` + interval `pay`.`no_of_days` day))) AS `due_days`,'SI' AS `type`,`cur`.`currency_code` AS `currency_code`,`inv`.`grand_total` AS `grand_total`,`inv`.`exchange_rate` AS `exchange_rate` from (((`erp_invoice` `inv` left join `erp_customer` `cust` on((`inv`.`customer_id` = `cust`.`id`))) left join `erp_mas_payment_term` `pay` on((`inv`.`payment_terms` = `pay`.`id`))) left join `erp_mas_currency` `cur` on((`inv`.`currency` = `cur`.`id`))) union select `inv`.`id` AS `id`,`inv`.`invoice_no` AS `invoice_no`,`inv`.`invoice_date` AS `invoice_date`,`pay`.`payment_terms` AS `payment_terms`,`pay`.`no_of_days` AS `no_of_days`,`vendor`.`vendor_name` AS `vendor_name`,(`inv`.`invoice_date` + interval `pay`.`no_of_days` day) AS `receive_date`,(to_days(curdate()) - to_days((`inv`.`invoice_date` + interval `pay`.`no_of_days` day))) AS `due_days`,'PI' AS `type`,`cur`.`currency_code` AS `currency_code`,`inv`.`grand_total` AS `grand_total`,`inv`.`exchange_rate` AS `exchange_rate` from (((`erp_purchasinginvoice` `inv` left join `erp_vendor` `vendor` on((`inv`.`vendor_id` = `vendor`.`id`))) left join `erp_mas_payment_term` `pay` on((`inv`.`payment_terms` = `pay`.`id`))) left join `erp_mas_currency` `cur` on((`inv`.`currency` = `cur`.`id`))) ;


CREATE  VIEW `erp_finance_ageing_0_to_30`  AS  select `VI`.`ledger_id` AS `ledger_id`,`VI`.`subledger_id` AS `subledger_id`,ifnull(`S`.`description`,'No Subledger') AS `subledger`,`V`.`id` AS `voucher_id`,`V`.`voucher_date` AS `voucher_date`,`V`.`due_date` AS `due_date`,`V`.`reference_no` AS `reference_no`,`VI`.`narration` AS `narration`,`VI`.`id` AS `voucher_item_id`,concat(`VT`.`code`,'/',`V`.`voucher_no`) AS `voucher_no`,round((`VI`.`amount` * `VI`.`exchange_rate`),4) AS `total_amount`,ifnull(abs(round(sum(`AI`.`amount`),4)),0) AS `allocated`,(round((`VI`.`amount` * `VI`.`exchange_rate`),4) - ifnull(round(sum(`AI`.`amount`),4),0)) AS `amount` from ((((`erp_finance_voucher_items` `VI` left join `erp_finance_voucher_head` `V` on((`V`.`id` = `VI`.`header_id`))) left join `erp_finance_allocation_items` `AI` on((`AI`.`voucher_item_id` = `VI`.`id`))) left join `erp_finance_subledger` `S` on((`S`.`id` = `VI`.`subledger_id`))) left join `erp_finance_voucher_type` `VT` on((`VT`.`id` = `V`.`voucher_type`))) where ((`V`.`voucher_date` between '2021-03-31' and '2021-04-30') and (`V`.`is_posted` = 'Y') and (`VI`.`ledger_id` = '11')) group by `VI`.`id`,`VI`.`subledger_id` ;



CREATE  VIEW `erp_finance_ageing_31_to_60`  AS  select `VI`.`ledger_id` AS `ledger_id`,`VI`.`subledger_id` AS `subledger_id`,ifnull(`S`.`description`,'No Subledger') AS `subledger`,`V`.`id` AS `voucher_id`,`V`.`voucher_date` AS `voucher_date`,`V`.`due_date` AS `due_date`,`V`.`reference_no` AS `reference_no`,`VI`.`narration` AS `narration`,`VI`.`id` AS `voucher_item_id`,concat(`VT`.`code`,'/',`V`.`voucher_no`) AS `voucher_no`,round((`VI`.`amount` * `VI`.`exchange_rate`),4) AS `total_amount`,ifnull(abs(round(sum(`AI`.`amount`),4)),0) AS `allocated`,(round((`VI`.`amount` * `VI`.`exchange_rate`),4) - ifnull(round(sum(`AI`.`amount`),4),0)) AS `amount` from ((((`erp_finance_voucher_items` `VI` left join `erp_finance_voucher_head` `V` on((`V`.`id` = `VI`.`header_id`))) left join `erp_finance_allocation_items` `AI` on((`AI`.`voucher_item_id` = `VI`.`id`))) left join `erp_finance_subledger` `S` on((`S`.`id` = `VI`.`subledger_id`))) left join `erp_finance_voucher_type` `VT` on((`VT`.`id` = `V`.`voucher_type`))) where ((`V`.`voucher_date` between '2021-03-01' and '2021-03-30') and (`V`.`is_posted` = 'Y') and (`VI`.`ledger_id` = '11')) group by `VI`.`id`,`VI`.`subledger_id` ;


CREATE  VIEW `erp_finance_ageing_61_to_90`  AS  select `VI`.`ledger_id` AS `ledger_id`,`VI`.`subledger_id` AS `subledger_id`,ifnull(`S`.`description`,'No Subledger') AS `subledger`,`V`.`id` AS `voucher_id`,`V`.`voucher_date` AS `voucher_date`,`V`.`due_date` AS `due_date`,`V`.`reference_no` AS `reference_no`,`VI`.`narration` AS `narration`,`VI`.`id` AS `voucher_item_id`,concat(`VT`.`code`,'/',`V`.`voucher_no`) AS `voucher_no`,round((`VI`.`amount` * `VI`.`exchange_rate`),4) AS `total_amount`,ifnull(abs(round(sum(`AI`.`amount`),4)),0) AS `allocated`,(round((`VI`.`amount` * `VI`.`exchange_rate`),4) - ifnull(round(sum(`AI`.`amount`),4),0)) AS `amount` from ((((`erp_finance_voucher_items` `VI` left join `erp_finance_voucher_head` `V` on((`V`.`id` = `VI`.`header_id`))) left join `erp_finance_allocation_items` `AI` on((`AI`.`voucher_item_id` = `VI`.`id`))) left join `erp_finance_subledger` `S` on((`S`.`id` = `VI`.`subledger_id`))) left join `erp_finance_voucher_type` `VT` on((`VT`.`id` = `V`.`voucher_type`))) where ((`V`.`voucher_date` between '2021-01-30' and '2021-02-28') and (`V`.`is_posted` = 'Y') and (`VI`.`ledger_id` = '11')) group by `VI`.`id`,`VI`.`subledger_id` ;



CREATE  VIEW `erp_finance_ageing_91_to_120`  AS  select `VI`.`ledger_id` AS `ledger_id`,`VI`.`subledger_id` AS `subledger_id`,ifnull(`S`.`description`,'No Subledger') AS `subledger`,`V`.`id` AS `voucher_id`,`V`.`voucher_date` AS `voucher_date`,`V`.`due_date` AS `due_date`,`V`.`reference_no` AS `reference_no`,`VI`.`narration` AS `narration`,`VI`.`id` AS `voucher_item_id`,concat(`VT`.`code`,'/',`V`.`voucher_no`) AS `voucher_no`,round((`VI`.`amount` * `VI`.`exchange_rate`),4) AS `total_amount`,ifnull(abs(round(sum(`AI`.`amount`),4)),0) AS `allocated`,(round((`VI`.`amount` * `VI`.`exchange_rate`),4) - ifnull(round(sum(`AI`.`amount`),4),0)) AS `amount` from ((((`erp_finance_voucher_items` `VI` left join `erp_finance_voucher_head` `V` on((`V`.`id` = `VI`.`header_id`))) left join `erp_finance_allocation_items` `AI` on((`AI`.`voucher_item_id` = `VI`.`id`))) left join `erp_finance_subledger` `S` on((`S`.`id` = `VI`.`subledger_id`))) left join `erp_finance_voucher_type` `VT` on((`VT`.`id` = `V`.`voucher_type`))) where ((`V`.`voucher_date` between '2020-12-31' and '2021-01-29') and (`V`.`is_posted` = 'Y') and (`VI`.`ledger_id` = '11')) group by `VI`.`id`,`VI`.`subledger_id` ;



CREATE  VIEW `erp_finance_ageing_greaterthan_120`  AS  select `VI`.`ledger_id` AS `ledger_id`,`VI`.`subledger_id` AS `subledger_id`,ifnull(`S`.`description`,'No Subledger') AS `subledger`,`V`.`id` AS `voucher_id`,`V`.`voucher_date` AS `voucher_date`,`V`.`due_date` AS `due_date`,`V`.`reference_no` AS `reference_no`,`VI`.`narration` AS `narration`,`VI`.`id` AS `voucher_item_id`,concat(`VT`.`code`,'/',`V`.`voucher_no`) AS `voucher_no`,round((`VI`.`amount` * `VI`.`exchange_rate`),4) AS `total_amount`,ifnull(abs(round(sum(`AI`.`amount`),4)),0) AS `allocated`,(round((`VI`.`amount` * `VI`.`exchange_rate`),4) - ifnull(round(sum(`AI`.`amount`),4),0)) AS `amount` from ((((`erp_finance_voucher_items` `VI` left join `erp_finance_voucher_head` `V` on((`V`.`id` = `VI`.`header_id`))) left join `erp_finance_allocation_items` `AI` on((`AI`.`voucher_item_id` = `VI`.`id`))) left join `erp_finance_subledger` `S` on((`S`.`id` = `VI`.`subledger_id`))) left join `erp_finance_voucher_type` `VT` on((`VT`.`id` = `V`.`voucher_type`))) where ((`V`.`voucher_date` between '2010-01-01' and '2020-12-30') and (`V`.`is_posted` = 'Y') and (`VI`.`ledger_id` = '11')) group by `VI`.`id`,`VI`.`subledger_id` ;



CREATE  VIEW `erp_finance_allocated_credit`  AS  select `V`.`id` AS `voucher_id`,`VI`.`id` AS `voucher_item_id`,`V`.`voucher_no` AS `voucher_no`,abs((`VI`.`amount` * `VI`.`exchange_rate`)) AS `amount`,ifnull(abs(sum(`AI`.`amount`)),0) AS `allocated`,(abs((`VI`.`amount` * `VI`.`exchange_rate`)) - ifnull(abs(sum(`AI`.`amount`)),0)) AS `balance` from ((`erp_finance_voucher_items` `VI` left join `erp_finance_voucher_head` `V` on((`V`.`id` = `VI`.`header_id`))) left join `erp_finance_allocation_items` `AI` on((`AI`.`voucher_item_id` = `VI`.`id`))) where ((`V`.`is_posted` = 'Y') and (`VI`.`amount` < 0) and (`VI`.`ledger_id` = 11) and (`VI`.`subledger_id` = 449)) group by `VI`.`id` ;



CREATE  VIEW `erp_finance_allocated_debit`  AS  select `V`.`id` AS `voucher_id`,`VI`.`id` AS `voucher_item_id`,`V`.`voucher_no` AS `voucher_no`,(`VI`.`amount` * `VI`.`exchange_rate`) AS `amount`,ifnull(abs(sum(`AI`.`amount`)),0) AS `allocated`,((`VI`.`amount` * `VI`.`exchange_rate`) - ifnull(abs(sum(`AI`.`amount`)),0)) AS `balance` from ((`erp_finance_voucher_items` `VI` left join `erp_finance_voucher_head` `V` on((`V`.`id` = `VI`.`header_id`))) left join `erp_finance_allocation_items` `AI` on((`AI`.`voucher_item_id` = `VI`.`id`))) where ((`V`.`is_posted` = 'Y') and (`VI`.`amount` > 0) and (`VI`.`ledger_id` = 11) and (`VI`.`subledger_id` = 449)) group by `VI`.`id` ;


CREATE  VIEW `erp_finance_balance_sheet`  AS  select `L`.`id` AS `ledger_id`,`L`.`account_no` AS `ledger_no`,`L`.`description` AS `ledger_name`,`L`.`level` AS `level`,`L`.`parent_account` AS `parent`,ifnull(`S`.`description`,'N/A') AS `subledger`,ifnull(`S`.`id`,0) AS `subledger_id`,round(sum((`VI`.`amount` * `VI`.`exchange_rate`)),4) AS `balance` from (((`erp_finance_legder` `L` left join `erp_finance_voucher_items` `VI` on((`VI`.`ledger_id` = `L`.`id`))) left join `erp_finance_voucher_head` `V` on((`VI`.`header_id` = `V`.`id`))) left join `erp_finance_subledger` `S` on((`S`.`id` = `VI`.`subledger_id`))) where ((`V`.`is_posted` = 'Y') and (`V`.`voucher_date` >= '2010-01-01') and (`V`.`voucher_date` <= '2021-03-13') and (`L`.`type_of_account` = 'B')) group by `L`.`id`,`S`.`id` ;



CREATE  VIEW `erp_finance_gl_summary`  AS  select `SL`.`id` AS `subledger_id`,`SL`.`account_no` AS `subledger_no`,ifnull(`SL`.`description`,'No Subledger') AS `subledger_name`,round(sum((case when (`VI`.`amount` > 0) then (`VI`.`amount` * `VI`.`exchange_rate`) else 0 end)),4) AS `debit`,round(sum((case when (`VI`.`amount` < 0) then abs((`VI`.`amount` * `VI`.`exchange_rate`)) else 0 end)),4) AS `credit`,round(sum((`VI`.`amount` * `VI`.`exchange_rate`)),4) AS `balance`,`L`.`id` AS `ledger_id` from (((`erp_finance_voucher_items` `VI` left join `erp_finance_voucher_head` `V` on((`V`.`id` = `VI`.`header_id`))) left join `erp_finance_legder` `L` on((`L`.`id` = `VI`.`ledger_id`))) left join `erp_finance_subledger` `SL` on((`SL`.`id` = `VI`.`subledger_id`))) where ((`VI`.`ledger_id` = '11') and (`V`.`is_posted` = 'Y') and (`V`.`voucher_date` >= '2021-03-30') and (`V`.`voucher_date` <= '2021-03-30')) group by `VI`.`ledger_id`,`VI`.`subledger_id` ;



CREATE  VIEW `erp_finance_profit_and_loss`  AS  select `L`.`id` AS `ledger_id`,`L`.`account_no` AS `ledger_no`,`L`.`description` AS `ledger_name`,`L`.`level` AS `level`,`L`.`parent_account` AS `parent`,ifnull(`S`.`description`,'N/A') AS `subledger`,`S`.`id` AS `subledger_id`,round(sum((case when (`VI`.`amount` > 0) then (`VI`.`amount` * `VI`.`exchange_rate`) else 0 end)),4) AS `debit`,round(sum((case when (`VI`.`amount` < 0) then (`VI`.`amount` * `VI`.`exchange_rate`) else 0 end)),4) AS `credit`,round(sum((`VI`.`amount` * `VI`.`exchange_rate`)),4) AS `balance` from (((`erp_finance_legder` `L` left join `erp_finance_voucher_items` `VI` on((`VI`.`ledger_id` = `L`.`id`))) left join `erp_finance_voucher_head` `V` on((`VI`.`header_id` = `V`.`id`))) left join `erp_finance_subledger` `S` on((`S`.`id` = `VI`.`subledger_id`))) where ((`V`.`is_posted` = 'Y') and (`V`.`voucher_date` between '2021-01-01' and '2021-04-10') and (`L`.`type_of_account` = 'P')) group by `L`.`id`,`S`.`id` ;



CREATE  VIEW `erp_finance_statement`  AS  select `V`.`id` AS `voucher_id`,`V`.`voucher_date` AS `voucher_date`,`V`.`due_date` AS `due_date`,`V`.`voucher_type` AS `type`,`V`.`header_table_id` AS `head_id`,`V`.`reference_no` AS `reference_no`,`VI`.`narration` AS `narration`,`VI`.`id` AS `voucher_item_id`,`V`.`voucher_no` AS `voucher_no`,round(abs((`VI`.`amount` * `VI`.`exchange_rate`)),4) AS `amount`,round(ifnull(abs(sum(`AI`.`amount`)),0),4) AS `allocated`,(round(abs((`VI`.`amount` * `VI`.`exchange_rate`)),4) - round(ifnull(abs(sum(`AI`.`amount`)),0),4)) AS `balance` from ((`erp_finance_voucher_items` `VI` left join `erp_finance_voucher_head` `V` on((`V`.`id` = `VI`.`header_id`))) left join `erp_finance_allocation_items` `AI` on((`AI`.`voucher_item_id` = `VI`.`id`))) where ((`V`.`is_posted` = 'Y') and (`VI`.`amount` > 0) and (`VI`.`ledger_id` = 11) and (`VI`.`subledger_id` = 236)) group by `VI`.`id` ;



CREATE  VIEW `erp_finance_statement_new`  AS  (select `S`.`id` AS `subledger_id`,`S`.`description` AS `subledger_name`,`S`.`industry` AS `industry_name`,`V`.`id` AS `voucher_id`,`V`.`voucher_date` AS `voucher_date`,`V`.`due_date` AS `due_date`,`V`.`reference_no` AS `reference_no`,`VI`.`narration` AS `narration`,`VI`.`id` AS `voucher_item_id`,`V`.`voucher_no` AS `voucher_no`,round(abs((`VI`.`amount` * `VI`.`exchange_rate`)),4) AS `amount`,round(ifnull(abs(sum(`AI`.`amount`)),0),4) AS `allocated`,(round(abs((`VI`.`amount` * `VI`.`exchange_rate`)),4) - round(ifnull(abs(sum(`AI`.`amount`)),0),4)) AS `balance` from (((`erp_finance_voucher_items` `VI` left join `erp_finance_voucher_head` `V` on((`V`.`id` = `VI`.`header_id`))) left join `erp_finance_subledger` `S` on((`S`.`id` = `VI`.`subledger_id`))) left join `erp_finance_allocation_items` `AI` on((`AI`.`voucher_item_id` = `VI`.`id`))) where ((`S`.`subledger_type_id` = 1) and (`V`.`is_posted` = 'Y') and (`V`.`voucher_date` <= '2021-04-03') and (`VI`.`amount` < 0) and (`VI`.`ledger_id` = 11)) group by `VI`.`id`) union (select `S`.`id` AS `subledger_id`,`S`.`description` AS `subledger_name`,`S`.`industry` AS `industry_name`,`V`.`id` AS `voucher_id`,`V`.`voucher_date` AS `voucher_date`,`V`.`due_date` AS `due_date`,`V`.`reference_no` AS `reference_no`,`VI`.`narration` AS `narration`,`VI`.`id` AS `voucher_item_id`,`V`.`voucher_no` AS `voucher_no`,-(round(abs((`VI`.`amount` * `VI`.`exchange_rate`)),4)) AS `amount`,-(round(ifnull(abs(sum(`AI`.`amount`)),0),4)) AS `allocated`,-((round(abs((`VI`.`amount` * `VI`.`exchange_rate`)),4) - round(ifnull(abs(sum(`AI`.`amount`)),0),4))) AS `balance` from (((`erp_finance_voucher_items` `VI` left join `erp_finance_voucher_head` `V` on((`V`.`id` = `VI`.`header_id`))) left join `erp_finance_subledger` `S` on((`S`.`id` = `VI`.`subledger_id`))) left join `erp_finance_allocation_items` `AI` on((`AI`.`voucher_item_id` = `VI`.`id`))) where ((`S`.`subledger_type_id` = 1) and (`V`.`is_posted` = 'Y') and (`V`.`voucher_date` >= '2021-04-01') and (`V`.`voucher_date` <= '2021-04-03') and (`VI`.`amount` > 0) and (`VI`.`ledger_id` = 11)) group by `VI`.`id`) ;


CREATE  VIEW `erp_finance_subledger_summary`  AS  select `L`.`main_account_type` AS `main_account_type`,`L`.`description` AS `description`,round(sum((`VI`.`amount` * `VI`.`exchange_rate`)),4) AS `balance` from (((`erp_finance_voucher_items` `VI` left join `erp_finance_voucher_head` `VH` on((`VH`.`id` = `VI`.`header_id`))) left join `erp_finance_legder` `L` on((`L`.`id` = `VI`.`ledger_id`))) left join `erp_finance_subledger` `SL` on((`SL`.`id` = `VI`.`subledger_id`))) where ((`VH`.`is_posted` = 'Y') and (`VH`.`voucher_date` <= '2021-04-30') and (`VI`.`subledger_id` = 274)) group by `L`.`id` order by `L`.`main_account_type` ;


CREATE  VIEW `erp_finance_trialbalance`  AS  select `L`.`id` AS `ledger_id`,`L`.`account_no` AS `ledger_no`,`L`.`description` AS `ledger_name`,`L`.`level` AS `level`,`L`.`parent_account` AS `parent`,ifnull(`S`.`description`,'N/A') AS `subledger`,ifnull(`S`.`id`,0) AS `subledger_id`,round(sum((case when (`VI`.`amount` > 0) then (`VI`.`amount` * `VI`.`exchange_rate`) else 0 end)),4) AS `debit`,round(sum((case when (`VI`.`amount` < 0) then (`VI`.`amount` * `VI`.`exchange_rate`) else 0 end)),4) AS `credit`,round(sum((`VI`.`amount` * `VI`.`exchange_rate`)),4) AS `balance` from (((`erp_finance_legder` `L` left join `erp_finance_voucher_items` `VI` on((`VI`.`ledger_id` = `L`.`id`))) left join `erp_finance_voucher_head` `V` on((`VI`.`header_id` = `V`.`id`))) left join `erp_finance_subledger` `S` on((`S`.`id` = `VI`.`subledger_id`))) where ((`V`.`is_posted` = 'Y') and (`V`.`voucher_date` between '2021-01-01' and '2021-03-13') and (`L`.`type_of_account` in ('B','P'))) group by `L`.`id`,`S`.`id` ;


CREATE  VIEW `erp_finance_voucher_head_list`  AS  select `vh`.`id` AS `id`,`vt`.`have_mappings` AS `have_mappings`,concat(`vt`.`code`,'/',`vh`.`voucher_no`) AS `voucher_no`,`vt`.`description` AS `voucher_type`,`vt`.`code` AS `code`,`vt`.`id` AS `voucher_type_id`,`vh`.`voucher_date` AS `voucher_date`,`vh`.`reference_no` AS `reference_no`,`vh`.`header_table_id` AS `header_table_id`,`vh`.`total_voucher_amount` AS `total_voucher_amount`,`vh`.`cheque_no` AS `cheque_no`,`vh`.`cheque_date` AS `cheque_date`,`vh`.`is_posted` AS `is_posted`,`vh`.`is_allocated` AS `is_allocated`,group_concat(distinct `su`.`description` separator ',') AS `subledger`,group_concat(distinct `br`.`product_class` separator ',') AS `brands`,sum((case when (`vi`.`amount` > 0) then round((`vi`.`amount` * `vi`.`exchange_rate`),2) else 0 end)) AS `value`,abs(sum(round((`vi`.`amount` * `vi`.`exchange_rate`),4))) AS `balance`,`vh`.`general_narration` AS `narration`,group_concat(distinct `le`.`description` separator ',') AS `ledger` from (((((`erp_finance_voucher_items` `vi` left join `erp_finance_voucher_head` `vh` on((`vh`.`id` = `vi`.`header_id`))) left join `erp_finance_subledger` `su` on((`su`.`id` = `vi`.`subledger_id`))) left join `erp_product_class` `br` on((`br`.`id` = `vi`.`brand_id`))) left join `erp_finance_legder` `le` on((`le`.`id` = `vi`.`ledger_id`))) left join `erp_finance_voucher_type` `vt` on((`vt`.`id` = `vh`.`voucher_type`))) group by `vi`.`header_id` ;



CREATE  VIEW `fixed_asset_depreciation`  AS  select `h`.`id` AS `id`,`h`.`purchase_date` AS `purchase_date`,`h`.`depreciation_start_date` AS `depreciation_start_date`,`m`.`fixed_asset_name` AS `fixed_asset_name`,`h`.`part_no` AS `part_no`,`h`.`description` AS `description`,`h`.`serial_no` AS `serial_no`,`h`.`active` AS `active`,`h`.`monthly_depreciation` AS `monthly_depreciation`,`h`.`total_value` AS `total_value`,`d`.`depreciated_date` AS `depreciated_date`,`h`.`current_book_value` AS `current_book_value`,`d`.`depreciation_amount` AS `cbv`,`m`.`depreciation_period` AS `depreciation_period`,`h`.`sold_amount` AS `sold_amount` from ((`erp_fixed_asset_register` `h` left join `erp_fixed_asset_master` `m` on((`h`.`master_id` = `m`.`id`))) left join `erp_fixed_asset_depreciation` `d` on((`h`.`id` = `d`.`fixed_asset_register`))) where ((`h`.`purchase_date` between '2020-05-01' and '2020-10-09') and (`h`.`master_id` = 2)) ;



CREATE  VIEW `view_all_stock_items`  AS  select `s`.`id` AS `id`,`s`.`part_number` AS `part_number`,`s`.`description` AS `description`,`b`.`product_class` AS `brand`,`b`.`id` AS `brand_id`,`s`.`generic_id` AS `generic_id`,`u`.`symbol` AS `unit`,`s`.`isActive` AS `isActive`,`s`.`inventory_item_type` AS `type`,`s`.`alias_name` AS `alias_name`,`s`.`rate` AS `rate`,(case when (`s`.`inventory_item_type` = 2) then 0 else ifnull(sum(`sl`.`quantity`),0) end) AS `quantity`,(case when (`s`.`inventory_item_type` = 2) then 0 else ifnull(sum(`a`.`quantity`),0) end) AS `alloc_qty`,`t`.`percentage` AS `tax_code`,`g`.`generic_name` AS `generic_name`,`s`.`have_serial_no` AS `have_serial_no`,`s`.`oem_number` AS `oem_number`,`sl`.`stock_updated` AS `stock_updated` from ((((((`erp_store` `s` left join `erp_allocated_materials` `a` on((`s`.`id` = `a`.`product_id`))) left join `erp_product_class` `b` on((`s`.`brand_class_id` = `b`.`id`))) left join `erp_unit` `u` on((`s`.`unit_id` = `u`.`id`))) left join `erp_mas_inv_tax` `t` on((`t`.`id` = `s`.`tax_code`))) left join `erp_inventory_generic_name` `g` on((`g`.`id` = `s`.`generic_id`))) left join `erp_stock_ledger` `sl` on(((`s`.`id` = `sl`.`product_id`) and (`sl`.`stock_updated` = 'Y')))) group by `s`.`id` order by `s`.`isActive`,`s`.`part_number` ;


CREATE  VIEW `view_all_stock_items_old`  AS  select `s`.`id` AS `id`,`s`.`part_number` AS `part_number`,`s`.`description` AS `description`,`b`.`product_class` AS `brand`,`b`.`id` AS `brand_id`,`s`.`generic_id` AS `generic_id`,`u`.`symbol` AS `unit`,`s`.`isActive` AS `isActive`,`s`.`inventory_item_type` AS `type`,`s`.`alias_name` AS `alias_name`,`s`.`rate` AS `rate`,(case when (`s`.`inventory_item_type` = 2) then 0 else ifnull(sum(`sl`.`quantity`),0) end) AS `quantity`,(case when (`s`.`inventory_item_type` = 2) then 0 else ifnull(sum(`a`.`quantity`),0) end) AS `alloc_qty`,`t`.`percentage` AS `tax_code`,`g`.`generic_name` AS `generic_name`,`s`.`machine_type` AS `machine_type`,`s`.`have_serial_no` AS `have_serial_no`,`s`.`oem_number` AS `oem_number`,`sl`.`stock_updated` AS `stock_updated` from ((((((`erp_store` `s` left join `erp_allocated_materials` `a` on((`s`.`id` = `a`.`product_id`))) left join `erp_product_class` `b` on((`s`.`brand_class_id` = `b`.`id`))) left join `erp_unit` `u` on((`s`.`unit_id` = `u`.`id`))) left join `erp_mas_inv_tax` `t` on((`t`.`id` = `s`.`tax_code`))) left join `erp_inventory_generic_name` `g` on((`g`.`id` = `s`.`generic_id`))) left join `erp_stock_ledger` `sl` on((`s`.`id` = `sl`.`product_id`))) group by `s`.`id` order by `s`.`isActive`,`s`.`part_number` ;


CREATE  VIEW `view_checkin_list`  AS  select `cn`.`id` AS `transaction_id`,`cn`.`file_id` AS `file_id`,`cn`.`credited_date` AS `transaction_date`,`cn`.`customer_id` AS `customer_id`,`customer`.`customer_name` AS `customer_name`,'Credit Note' AS `checkin_frm`,'CN' AS `status` from (`erp_creditnote` `cn` join `erp_customer` `customer` on((`customer`.`id` = `cn`.`customer_id`))) where (`cn`.`is_updated_store` = 'N') union all select `pgr`.`id` AS `transaction_id`,`pgr`.`file_id` AS `file_id`,`pgr`.`goods_receipt_date` AS `transaction_date`,`pgr`.`customer_id` AS `customer_id`,`customer`.`customer_name` AS `customer_name`,'Production Goods Receipt' AS `checkin_frm`,'PGR' AS `status` from (`erp_production_goods_receipt` `pgr` left join `erp_customer` `customer` on((`customer`.`id` = `pgr`.`customer_id`))) where (`pgr`.`is_updated_store` = 'N') union all select `igr`.`id` AS `transaction_id`,`igr`.`file_id` AS `file_id`,`igr`.`goods_receipt_date` AS `transaction_date`,`igr`.`customer_id` AS `customer_id`,`customer`.`customer_name` AS `customer_name`,'Inventory Goods Receipt' AS `checkin_frm`,'IGR' AS `status` from (`erp_inventory_goods_receipt` `igr` left join `erp_customer` `customer` on((`customer`.`id` = `igr`.`customer_id`))) where (`igr`.`stock_updated` = 'N') union all select `dt`.`id` AS `transaction_id`,'N/A' AS `file_id`,`dt`.`transfer_date` AS `transaction_date`,0 AS `customer_id`,'N/A' AS `customer_name`,'Direct Transfer' AS `checkin_frm`,'DTCK' AS `status` from `erp_inventory_direct_transfer` `dt` where (`dt`.`status` = 'P') union all select `idt`.`id` AS `transaction_id`,'N/A' AS `file_id`,`idt`.`transfer_date` AS `transaction_date`,0 AS `customer_id`,'N/A' AS `customer_name`,'Indirect Transfer' AS `checkin_frm`,'IDTCK' AS `status` from `erp_inventory_indirect_transfer` `idt` where (`idt`.`is_complete` = 'P') ;



CREATE  VIEW `view_customer_sale_report`  AS  select `C`.`id` AS `customer_id`,`C`.`customer_name` AS `customer_name` from (`erp_customer` `C` left join `erp_invoice` `I` on((`C`.`id` = `I`.`customer_id`))) where ((`I`.`invoice_date` not between '2019-10-01' and '2020-12-31') and (`C`.`isActive` = 'Y')) group by `C`.`id` ;



CREATE  VIEW `view_customer_sale_report_old`  AS  select `c`.`id` AS `customer_id`,`c`.`customer_name` AS `customer_name` from (`erp_customer` `c` left join `erp_invoice` `i` on((`c`.`id` = `i`.`customer_id`))) where ((`i`.`invoice_date` not between '2019-01-01' and '2019-12-31') and (`c`.`isActive` = 'Y')) group by `c`.`id` ;



CREATE  VIEW `view_invoice_creditnote_details`  AS  select `INV`.`id` AS `transaction_id`,`U`.`id` AS `lead_owner_id`,concat(`U`.`first_name`,' ',`U`.`last_name`) AS `lead_owner_name`,`INV`.`invoice_date` AS `transaction_date`,(`INV`.`grand_total` * `INV`.`exchange_rate`) AS `invoice_amount`,0 AS `credit_note_amount` from ((`cumuluse_erp_diatech_main`.`erp_user` `U` join `erp_mas_user_group` `UG` on((`UG`.`id` = `U`.`user_group_id`))) left join `erp_invoice` `INV` on((`INV`.`lead_owner_id` = `U`.`id`))) where ((`UG`.`is_salesman` = 'Y') and (`U`.`isActive` = 'Y')) union all select `CN`.`id` AS `transaction_id`,`U`.`id` AS `lead_owner_id`,concat(`U`.`first_name`,' ',`U`.`last_name`) AS `lead_owner_name`,`CN`.`credited_date` AS `transaction_date`,0 AS `invoice_amount`,(`CN`.`credited_amount` * `CN`.`exchange_rate`) AS `credit_note_amount` from (((`cumuluse_erp_diatech_main`.`erp_user` `U` join `erp_mas_user_group` `UG` on((`UG`.`id` = `U`.`user_group_id`))) left join `erp_invoice` `INV` on((`INV`.`lead_owner_id` = `U`.`id`))) left join `erp_creditnote` `CN` on((`CN`.`invoice_id` = `INV`.`id`))) where ((`UG`.`is_salesman` = 'Y') and (`U`.`isActive` = 'Y')) ;


CREATE  VIEW `view_leave_details`  AS  select `L`.`id` AS `id`,`E`.`id` AS `employee_id`,`E`.`first_name` AS `first_name`,`E`.`last_name` AS `last_name`,`E`.`employee_code` AS `employee_code`,`L`.`apply_date` AS `apply_date`,`L`.`leave_start_date` AS `leave_start_date`,`L`.`leave_end_date` AS `leave_end_date`,`L`.`leave_type` AS `leave_type`,`L`.`no_days` AS `no_days`,`L`.`leave_status` AS `leave_status`,`L`.`medical_certificate` AS `medical_certificate`,`L`.`leave_approval_document` AS `leave_approval_document`,'Leave' AS `type` from (`erp_hr_employee_leave_detail` `L` join `erp_hr_employee` `E` on((`L`.`employee_id` = `E`.`id`))) union select `M`.`id` AS `id`,`T`.`id` AS `employee_id`,`T`.`first_name` AS `first_name`,`T`.`last_name` AS `last_name`,`T`.`employee_code` AS `employee_code`,`M`.`leave_start_date` AS `apply_date`,`M`.`leave_start_date` AS `leave_start_date`,`M`.`leave_end_date` AS `leave_end_date`,`M`.`leave_type` AS `leave_type`,`M`.`no_days` AS `no_days`,`M`.`leave_status` AS `leave_status`,`M`.`medical_certificate` AS `medical_certificate`,`M`.`leave_approval_document` AS `leave_approval_document`,'Leave Migration' AS `type` from (`erp_hr_employee_leave_migrated` `M` join `erp_hr_employee` `T` on((`M`.`employee_id` = `T`.`id`))) ;



CREATE  VIEW `view_packaging_list`  AS  select `dn`.`id` AS `transaction_id`,(case when (`dn`.`file_id` > 0) then `dn`.`file_id` else 'N/A' end) AS `file_id`,`dn`.`delivery_date` AS `transaction_date`,`dn`.`customer_id` AS `customer_id`,(case when (`dn`.`customer_id` > 0) then `customer`.`customer_name` else 'N/A' end) AS `customer_name`,'Delivery Note' AS `packing_frm`,'D' AS `status` from (`erp_delivery_note` `dn` join `erp_customer` `customer` on((`customer`.`id` = `dn`.`customer_id`))) where ((`dn`.`ready_to_invoice` = 'N') and (`dn`.`status` = 'PN')) union all select `gi`.`id` AS `transaction_id`,'N/A' AS `file_id`,`gi`.`goods_issue_date` AS `transaction_date`,`gi`.`customer_id` AS `customer_id`,(case when (`gi`.`customer_id` > 0) then `customer`.`customer_name` else 'N/A' end) AS `customer_name`,'Inventory Goods Issue' AS `packing_frm`,'GI' AS `status` from (`erp_inventory_goods_issue` `gi` left join `erp_customer` `customer` on((`customer`.`id` = `gi`.`customer_id`))) where (`gi`.`stock_updated` = 'N') union all select `pdn`.`id` AS `transaction_id`,`pdn`.`file_id` AS `file_id`,`pdn`.`debited_date` AS `transaction_date`,`pdn`.`vendor_id` AS `customer_id`,(case when (`pdn`.`vendor_id` > 0) then `vendor`.`vendor_name` else 'N/A' end) AS `customer_name`,'Debitnote' AS `packing_frm`,'PDN' AS `status` from (`erp_purchasing_debit_note` `pdn` left join `erp_vendor` `vendor` on((`vendor`.`id` = `pdn`.`vendor_id`))) where (`pdn`.`is_goods_returned` = 'N') union all select `prgi`.`id` AS `transaction_id`,(case when (`prgi`.`file_id` > 0) then `prgi`.`file_id` else 'N/A' end) AS `file_id`,`prgi`.`goods_issue_date` AS `transaction_date`,`prgi`.`customer_id` AS `customer_id`,(case when (`prgi`.`customer_id` > 0) then `customer`.`customer_name` else 'N/A' end) AS `customer_name`,'Production Goods Issue' AS `packing_frm`,'PRGI' AS `status` from (`erp_production_goods_issue` `prgi` left join `erp_customer` `customer` on((`customer`.`id` = `prgi`.`customer_id`))) where (`prgi`.`is_goods_issued` = 'N') union all select `dt`.`id` AS `transaction_id`,'N/A' AS `file_id`,`dt`.`transfer_date` AS `transaction_date`,0 AS `customer_id`,'N/A' AS `customer_name`,'Direct Transfer' AS `packing_frm`,'DTOUT' AS `status` from `erp_inventory_direct_transfer` `dt` where (`dt`.`status` = 'T') union all select `idt`.`id` AS `transaction_id`,'N/A' AS `file_id`,`idt`.`transfer_date` AS `transaction_date`,0 AS `customer_id`,'N/A' AS `customer_name`,'Indirect Transfer' AS `packing_frm`,'IDTOUT' AS `status` from `erp_inventory_indirect_transfer` `idt` where (`idt`.`is_complete` = 'N') ;



CREATE  VIEW `view_payment_details`  AS  select `l`.`employee_id` AS `employee_id`,`e`.`first_name` AS `first_name`,`e`.`last_name` AS `last_name`,`e`.`employee_code` AS `employee_code`,`l`.`id` AS `id`,`l`.`approved_date` AS `approved_date`,`l`.`loan_amount` AS `amount`,`l`.`loan_status` AS `STATUS`,'Loan' AS `TYPE` from (`erp_hr_employee_loan_detail` `l` join `erp_hr_employee` `e` on((`l`.`employee_id` = `e`.`id`))) where ((`l`.`loan_posted` = 'N') and ((`l`.`loan_status` = 1) or (`l`.`loan_status` >= 3))) union select `s`.`employee_id` AS `employee_id`,`t`.`first_name` AS `first_name`,`t`.`last_name` AS `last_name`,`t`.`employee_code` AS `employee_code`,`s`.`id` AS `id`,`s`.`approved_date` AS `approved_date`,`s`.`amount` AS `amount`,`s`.`advance_payment_status` AS `STATUS`,'Salary Advance' AS `TYPE` from (`erp_hr_employee_salary_advance` `s` join `erp_hr_employee` `t` on((`s`.`employee_id` = `t`.`id`))) where ((`s`.`posted` = 'N') and ((`s`.`advance_payment_status` = 1) or (`s`.`advance_payment_status` >= 3))) union select `b`.`employee_id` AS `employee_id`,`u`.`first_name` AS `first_name`,`u`.`last_name` AS `last_name`,`u`.`employee_code` AS `employee_code`,`b`.`id` AS `id`,`b`.`approve_date` AS `approved_date`,`b`.`encashment_amount` AS `amount`,`b`.`status` AS `STATUS`,'Leave Encashment' AS `TYPE` from (`erp_hr_employee_leave_encashment` `b` join `erp_hr_employee` `u` on((`b`.`employee_id` = `u`.`id`))) where ((`b`.`finance_posted` = 'N') and ((`b`.`status` = 2) or (`b`.`status` = 4))) union select `c`.`employee_id` AS `employee_id`,`v`.`first_name` AS `first_name`,`v`.`last_name` AS `last_name`,`v`.`employee_code` AS `employee_code`,`c`.`id` AS `id`,`c`.`approve_date` AS `approved_date`,`c`.`total_amount` AS `amount`,`c`.`status` AS `STATUS`,'End of Service' AS `TYPE` from (`erp_hr_employee_eos` `c` join `erp_hr_employee` `v` on((`c`.`employee_id` = `v`.`id`))) where ((`c`.`finance_posted` = 'N') and ((`c`.`status` = 2) or (`c`.`status` = 4))) union select `d`.`employee_id` AS `employee_id`,`w`.`first_name` AS `first_name`,`w`.`last_name` AS `last_name`,`w`.`employee_code` AS `employee_code`,`d`.`id` AS `id`,`d`.`date_approved` AS `approved_date`,`d`.`settlement_amount` AS `amount`,`d`.`status` AS `STATUS`,'Leave Settlement' AS `TYPE` from (`erp_hr_employee_leavesettlement` `d` join `erp_hr_employee` `w` on((`d`.`employee_id` = `w`.`id`))) where ((`d`.`posted` = 'N') and ((`d`.`status` = 2) or (`d`.`status` = 4))) union select `f`.`employee_id` AS `employee_id`,`x`.`first_name` AS `first_name`,`x`.`last_name` AS `last_name`,`x`.`employee_code` AS `employee_code`,`f`.`id` AS `id`,`f`.`HR_approval_date` AS `approved_date`,`f`.`total` AS `amount`,`f`.`status` AS `STATUS`,'Expense' AS `TYPE` from (`erp_hr_employee_expense` `f` join `erp_hr_employee` `x` on((`f`.`employee_id` = `x`.`id`))) where ((`f`.`finance_posted` = 'N') and ((`f`.`status` = 4) or (`f`.`status` = 5))) union select `g`.`employee_id` AS `employee_id`,`y`.`first_name` AS `first_name`,`y`.`last_name` AS `last_name`,`y`.`employee_code` AS `employee_code`,`g`.`id` AS `id`,`g`.`approve_date` AS `approved_date`,`g`.`ticket_amt` AS `amount`,`g`.`status` AS `STATUS`,'Ticket Encashment' AS `TYPE` from (`erp_hr_employee_ticket_encashment` `g` join `erp_hr_employee` `y` on((`g`.`employee_id` = `y`.`id`))) where ((`g`.`finance_posted` = 'N') and ((`g`.`status` = 2) or (`g`.`status` = 4))) ;



CREATE  VIEW `view_product_location`  AS  select `sl`.`product_id` AS `product_id`,`sl`.`warehouse_id` AS `warehouse_id`,`sl`.`rack_id` AS `rack_id`,`sl`.`bin_id` AS `bin_id`,`w`.`warehouse_name` AS `warehouse_name`,`r`.`name` AS `rack`,`b`.`name` AS `bin`,sum(`sl`.`quantity`) AS `quantity` from ((((`erp_stock_ledger` `sl` left join `erp_store` `s` on((`sl`.`product_id` = `s`.`id`))) left join `erp_mas_inv_warehouse` `w` on((`sl`.`warehouse_id` = `w`.`id`))) left join `erp_mas_inv_bin` `b` on((`b`.`id` = `sl`.`bin_id`))) left join `erp_mas_inv_rack` `r` on((`r`.`id` = `sl`.`rack_id`))) group by `sl`.`bin_id`,`sl`.`rack_id`,`sl`.`warehouse_id`,`sl`.`product_id` ;



CREATE  VIEW `view_product_location_old`  AS  select `sl`.`product_id` AS `product_id`,`sl`.`warehouse_id` AS `warehouse_id`,`sl`.`rack_id` AS `rack_id`,`sl`.`bin_id` AS `bin_id`,`w`.`warehouse_name` AS `warehouse_name`,`r`.`name` AS `rack`,`b`.`name` AS `bin`,sum(`sl`.`quantity`) AS `quantity` from ((((`erp_stock_ledger` `sl` left join `erp_store` `s` on((`sl`.`product_id` = `s`.`id`))) left join `erp_mas_inv_warehouse` `w` on((`sl`.`warehouse_id` = `w`.`id`))) left join `erp_mas_inv_bin` `b` on((`b`.`id` = `sl`.`bin_id`))) left join `erp_mas_inv_rack` `r` on((`r`.`id` = `sl`.`rack_id`))) group by `sl`.`bin_id`,`sl`.`rack_id`,`sl`.`warehouse_id`,`sl`.`product_id` ;


CREATE  VIEW `view_stock_ledger`  AS  select `erp_stock_ledger`.`id` AS `id`,`erp_stock_ledger`.`file_id` AS `file_id`,`erp_stock_ledger`.`header_id` AS `header_id`,`erp_stock_ledger`.`header_item_id` AS `header_item_id`,`erp_stock_ledger`.`customer_vendor_id` AS `customer_vendor_id`,`erp_stock_ledger`.`customer_vendor_name` AS `customer_vendor_name`,`erp_stock_ledger`.`product_id` AS `product_id`,`erp_stock_ledger`.`product_name` AS `product_name`,`erp_stock_ledger`.`product_description` AS `product_description`,`erp_stock_ledger`.`brand_id` AS `brand_id`,`erp_stock_ledger`.`brand_name` AS `brand_name`,`erp_stock_ledger`.`warehouse_id` AS `warehouse_id`,`erp_stock_ledger`.`warehouse_name` AS `warehouse_name`,`erp_stock_ledger`.`rack_id` AS `rack_id`,`erp_stock_ledger`.`rack_name` AS `rack_name`,`erp_stock_ledger`.`bin_id` AS `bin_id`,`erp_stock_ledger`.`bin_name` AS `bin_name`,`erp_stock_ledger`.`unit_id` AS `unit_id`,`erp_stock_ledger`.`unit_name` AS `unit_name`,`erp_stock_ledger`.`quantity` AS `quantity`,`erp_stock_ledger`.`available_status` AS `available_status`,`erp_stock_ledger`.`batch_no` AS `batch_no`,`erp_stock_ledger`.`serial_no` AS `serial_no`,`erp_stock_ledger`.`manufacturing_date` AS `manufacturing_date`,`erp_stock_ledger`.`expiry_date` AS `expiry_date`,`erp_stock_ledger`.`cost_price` AS `cost_price`,`erp_stock_ledger`.`sales_price` AS `sales_price`,`erp_stock_ledger`.`transaction_date` AS `transaction_date`,`erp_stock_ledger`.`transaction_from` AS `transaction_from`,`erp_stock_ledger`.`transaction_type` AS `transaction_type`,`erp_stock_ledger`.`master_stock_ledger_id` AS `master_stock_ledger_id`,`erp_stock_ledger`.`created_by` AS `created_by`,`erp_stock_ledger`.`created_date` AS `created_date` from `erp_stock_ledger` order by `erp_stock_ledger`.`transaction_from` ;



CREATE  VIEW `view_stock_ledger_customer_vendor`  AS  select `c`.`id` AS `id`,`c`.`customer_name` AS `name`,'Customer' AS `type` from ((`erp_stock_ledger` `l` left join `erp_delivery_note` `d` on((`d`.`id` = `l`.`header_id`))) left join `erp_customer` `c` on((`c`.`id` = `d`.`customer_id`))) where (`l`.`transaction_type` in ('DELIVERY_NOTE','CREDIT_NOTE')) group by `c`.`id` union select `v`.`id` AS `id`,`v`.`vendor_name` AS `name`,'Vendor' AS `type` from ((`erp_stock_ledger` `l` left join `erp_purchasinginvoice` `p` on((`l`.`header_id` = `p`.`id`))) left join `erp_vendor` `v` on((`v`.`id` = `p`.`vendor_id`))) where (`l`.`transaction_type` in ('PURCHASE','DEBIT_NOTE')) group by `v`.`id` ;



CREATE  VIEW `view_stock_ledger_summary`  AS  select `erp_stock_ledger`.`id` AS `id`,`erp_stock_ledger`.`header_id` AS `header_id`,`erp_stock_ledger`.`header_item_id` AS `header_item_id`,`erp_stock_ledger`.`transaction_date` AS `transaction_date`,`erp_stock_ledger`.`transaction_type` AS `transaction_type`,`erp_stock_ledger`.`transaction_from` AS `transaction_from`,`erp_stock_ledger`.`customer_vendor_name` AS `customer_vendor_name`,`erp_stock_ledger`.`product_id` AS `product_id`,`erp_stock_ledger`.`quantity` AS `quantity`,`erp_stock_ledger`.`unit_id` AS `unit_id`,`erp_stock_ledger`.`warehouse_id` AS `warehouse_id`,`erp_stock_ledger`.`rack_id` AS `rack_id`,`erp_stock_ledger`.`bin_id` AS `bin_id`,`erp_stock_ledger`.`created_by` AS `created_by`,`erp_stock_ledger`.`created_date` AS `created_date`,`erp_stock_ledger`.`cost_price` AS `cost_price`,sum(`erp_stock_ledger`.`quantity`) AS `summary_qty` from `erp_stock_ledger` group by `erp_stock_ledger`.`transaction_type`,`erp_stock_ledger`.`header_id`,`erp_stock_ledger`.`product_id` ;



CREATE  VIEW `view_stock_state_as_of`  AS  select `S`.`id` AS `product_id`,`S`.`part_number` AS `part_number`,`S`.`description` AS `description`,`S`.`alias_name` AS `alias_name`,`B`.`product_class` AS `brand`,`G`.`generic_name` AS `generic_name`,`S`.`brand_class_id` AS `brand_id`,`S`.`generic_id` AS `generic_id`,sum(`L`.`quantity`) AS `quantity`,substring_index(group_concat(cast(`L`.`cost_price` as char charset utf8) order by `L`.`transaction_date` DESC separator ','),',',1) AS `rate` from (((`erp_stock_ledger` `L` left join `erp_store` `S` on((`S`.`id` = `L`.`product_id`))) left join `erp_product_class` `B` on((`S`.`brand_class_id` = `B`.`id`))) left join `erp_inventory_generic_name` `G` on((`S`.`generic_id` = `G`.`id`))) where (`L`.`transaction_date` <= '2021-04-10') group by `S`.`id` order by `S`.`part_number` ;
