jueves, 26 de enero de 2012

Listas de precio por producto


/*

Este script consiste en registrar todas las listas de precio a cada producto

- Lista de Precios Compra Moneda Nacional
- Lista de Precios Compra Moneda Extranjera
- Lista de Precios Venta Moneda Nacional
- Lista de Precios Venta Moneda Extranjera

*/

/*** Lista de Precios Compra Moneda Nacional ***/

INSERT INTO M_ProductPrice
(
    m_pricelist_version_id,
    m_product_id,
    ad_client_id,
    ad_org_id,
    isactive,
    created,
    createdby,
    updated,
    updatedby,
    pricelist,
    pricestd,
    pricelimit
)
select
    1000000 AS m_pricelist_version_id,
    m_product_id,
    1000000 as ad_client_id,
    0 as ad_org_id,
    'Y' as isactive,
    GETDATE() as created,
    1000000 as ctreatedby,
    GETDATE() updated,
    1000000 as updateby,
    0.00 as pricelist,
    0.00 as priceestd,
    0.00 pricelimit
from M_Product
where ad_client_id=1000000
and m_product_id not in
(
    select m_product_id from M_ProductPrice
    where m_pricelist_version_id=1000000
);

/*** Lista de Precios Compra Moneda Extranjera ***/

INSERT INTO M_ProductPrice
(
    m_pricelist_version_id,
    m_product_id,
    ad_client_id,
    ad_org_id,
    isactive,
    created,
    createdby,
    updated,
    updatedby,
    pricelist,
    pricestd,
    pricelimit
)
select
    1000001 AS m_pricelist_version_id,
    m_product_id,
    1000000 as ad_client_id,
    0 as ad_org_id,
    'Y' as isactive,
    GETDATE() as created,
    1000000 as ctreatedby,
    GETDATE() updated,
    1000000 as updateby,
    0.00 as pricelist,
    0.00 as priceestd,
    0.00 pricelimit
from M_Product
where ad_client_id=1000000
and m_product_id not in
(
    select m_product_id from M_ProductPrice
    where m_pricelist_version_id=1000001
);

/*** Lista de Precios Venta Moneda Nacional ***/

INSERT INTO M_ProductPrice
(
    m_pricelist_version_id,
    m_product_id,
    ad_client_id,
    ad_org_id,
    isactive,
    created,
    createdby,
    updated,
    updatedby,
    pricelist,
    pricestd,
    pricelimit
)
select
    1000002 AS m_pricelist_version_id,
    m_product_id,
    1000000 as ad_client_id,
    0 as ad_org_id,
    'Y' as isactive,
    GETDATE() as created,
    1000000 as ctreatedby,
    GETDATE() updated,
    1000000 as updateby,
    0.00 as pricelist,
    0.00 as priceestd,
    0.00 pricelimit
from M_Product
where ad_client_id=1000000
and m_product_id not in
(
    select m_product_id from M_ProductPrice
    where m_pricelist_version_id=1000002
);

/*** Lista de Precios Venta Moneda Extranjera ***/

INSERT INTO M_ProductPrice
(
    m_pricelist_version_id,
    m_product_id,
    ad_client_id,
    ad_org_id,
    isactive,
    created,
    createdby,
    updated,
    updatedby,
    pricelist,
    pricestd,
    pricelimit
)
select
    1000003 AS m_pricelist_version_id,
    m_product_id,
    1000000 as ad_client_id,
    0 as ad_org_id,
    'Y' as isactive,
    GETDATE() as created,
    1000000 as ctreatedby,
    GETDATE() updated,
    1000000 as updateby,
    0.00 as pricelist,
    0.00 as priceestd,
    0.00 pricelimit
from M_Product
where ad_client_id=1000000
and m_product_id not in
(
    select m_product_id from M_ProductPrice
    where m_pricelist_version_id=1000003
);

-- Registrar costos en cada uno de los productos para que se pueda aplicar la contabilización de la recepción de material.

update m_cost set currentcostprice=1.00
where ad_client_id=1000000
and currentcostprice = 0;

No hay comentarios:

Publicar un comentario en la entrada