Contents

require 'skr/db/migration_helpers'

class CreateSkrSkuLocDetailsView < ActiveRecord::Migration
    def up

        execute <<-EOS
        create view #{skr_prefix}sku_loc_details as
          select
            sl.id as sku_loc_id,
            s.code as sku_code,
            s.description as sku_description,
            s.default_uom_code,
            uom.id as default_uom_id,
            coalesce( uom.size, 1) as default_uom_size,
            coalesce(uom.price, 0.0) as default_price,
            v.code as vendor_code, v.name as vendor_name,
            sv.part_code as vendor_part_code,
            sv.cost as purchase_cost
          from #{skr_prefix}sku_locs sl
            join #{skr_prefix}skus s on s.id=sl.sku_id
            left join #{skr_prefix}uoms uom on uom.sku_id = s.id and uom.code = s.default_uom_code
            join #{skr_prefix}vendors v on s.default_vendor_id = v.id
            join #{skr_prefix}sku_vendors sv on sv.vendor_id = v.id and sv.sku_id=s.id
        EOS
    end

    def down
        execute "drop view #{skr_prefix}sku_loc_details"
    end

end

Version data entries

8 entries across 8 versions & 1 rubygems

Version	Path
stockor-0.5.0	db/migrate/20140330232808_create_skr_sku_loc_details_view.rb
stockor-0.4.0	db/migrate/20140330232808_create_skr_sku_loc_details_view.rb
stockor-0.3.0	db/migrate/20140330232808_create_skr_sku_loc_details_view.rb
stockor-0.2	db/migrate/20140330232808_create_skr_sku_loc_details_view.rb
stockor-0.1.9	db/migrate/20140330232808_create_skr_sku_loc_details_view.rb
stockor-0.1.8	db/migrate/20140330232808_create_skr_sku_loc_details_view.rb
stockor-0.1.7	db/migrate/20140330232808_create_skr_sku_loc_details_view.rb
stockor-0.1.5	db/migrate/20140330232808_create_skr_sku_loc_details_view.rb