auto po

CREATE OR REPLACE FUNCTION adempiere.tpb_auto_allocation_fabric(p_lc_date timestamp without time zone, p_product_category numeric)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
begin
    TRUNCATE TABLE tpb_populate_auto_po ;
insert into tpb_populate_auto_po
    select
        concat(co.kst_ordertype,';',mr.m_warehouse_id,
                    ';',mr_line.c_bpartner_id,';',mpc.m_product_category_id,';',mp.upc,';',mr.m_requisition_id) as id,
        '0' as line_id,
        mp.m_product_id,
        mr.m_warehouse_id,
        mr_line.c_bpartner_id,
        mr_line.QtyEntered,
        co.kst_ordertype,
        mr.m_requisition_id,
        mr_line.m_requisitionline_id,
        mpc.m_product_category_id,
        mr.m_pricelist_id,
        mr_line.c_currency_id,
        co.kst_brand,
        co.kst_season,
        co.poreference,
        co.c_order_id as so_id,
        mp.upc
    from m_requisitionline mr_line
    left join m_requisition as mr on mr_line.m_requisition_id = mr.m_requisition_id
    left join c_order as co on mr_line.poreference=co.poreference
    left join m_product as mp on mr_line.m_product_id = mp.m_product_id
    left join m_product_category as mpc on mp.m_product_category_id=mpc.m_product_category_id
    where co.kst_lcdate= p_lc_date --'2020-02-27'::timestamp--'2020-01-16'::timestamp
    and mpc.m_product_category_id = (select m_product_category_id from m_product_category mpc where value = 'FB')
    and mr_line.isactive  = 'Y'
    and co.C_DocTypeTarget_ID <> 1000082
    and mp.kst_isila = 'N'
    group by
        co.kst_ordertype,
        mr.m_warehouse_id,
        mr_line.c_bpartner_id,
        mpc.m_product_category_id,
        mp.upc,
        mp.m_product_id,
        mr_line.QtyEntered,
        mr.m_requisition_id,
        mr_line.m_requisitionline_id,
        mr.m_pricelist_id,
        mr_line.c_currency_id,
        co.kst_brand,
        co.kst_season,
        co.poreference,
        co.c_order_id,
        mp.upc;
     raise NOTICE 'Populate Data Auto Allocate From PO is Completed';
end;
$function$
;

21,171

select * from kst_orderdetail where m_requisitionline_id =3293297

select * from tpb_auto_allocation_perpo('2020-02-27'::timestamp,1000050)

select * from tpb_populate_auto_po tpap

where id = '0125737260;61034229;1001893;1000002;2020-02-27;CreatedAt:2020-04-24'

-- /////////////////////////////// auto po combine per po buyer.
CREATE OR REPLACE FUNCTION adempiere.tpb_auto_allocation_perpo(p_lc_date timestamp without time zone, p_product_category numeric)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
begin
    TRUNCATE TABLE tpb_populate_auto_po ;
insert into tpb_populate_auto_po
select
    concat(right(so.poreference,10),';',right(mp.upc,8),';',mr_line.c_bpartner_id,';',mr.m_warehouse_id,';',so.kst_lcdate::date,';CreatedAt:',now()::date) as id,
    concat(right(so.poreference,10),';',right(mp.upc,8),';',mr_line.c_bpartner_id,';',mr.m_warehouse_id,';',so.kst_lcdate::date,';CreatedAt:',now()::date) as line_id,
    mp.m_product_id,
    mr.m_warehouse_id,
    mr_line.c_bpartner_id,
    mr_line.QtyEntered,
    so.kst_ordertype,
    mr.m_requisition_id,
    mr_line.m_requisitionline_id,
    mpc.m_product_category_id,
    mr.m_pricelist_id,
    mr_line.c_currency_id,
    so.kst_brand,
    so.kst_season,
    so.poreference,
    0 as so_id,
    mp.upc,
    mr_line.datepromised
from
m_requisitionline mr_line
left join m_requisition as mr on mr.m_requisition_id = mr_line.m_requisition_id
left join c_order as so on mr_line.poreference = so.poreference
left join m_product as mp on mr_line.m_product_id = mp.m_product_id
left join m_product_category as mpc on mp.m_product_category_id = mpc.m_product_category_id
where so.C_DocTypeTarget_ID <> 1000082 and mr_line.isactive  = 'Y' and mp.kst_isila ='N'
and so.kst_lcdate = p_lc_date and mpc.m_product_category_id = p_product_category;
raise NOTICE 'Populate Data Auto Allocate From PO is Completed';
end;
$function$
;

select * from m_pricelist mp where m_pricelist_id =1000005


select * from tpb_alloc_wh_style_tyord('2020-02-27'::timestamp, 1000050)

select * from tpb_populate_auto_po

--Per WH/Style/Type Order per UPC (8 digit)
--////////////////////////////////// auto po combine per style/WH/type order
CREATE OR REPLACE FUNCTION adempiere.tpb_alloc_wh_style_tyord(p_lc_date timestamp without time zone, p_product_category numeric)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
begin
    TRUNCATE TABLE tpb_populate_auto_po ;
insert into tpb_populate_auto_po
select
    concat(mr.m_warehouse_id,';',string_agg(DISTINCT "left"(mr_line.kst_joborder::text, strpos(mr_line.kst_joborder::text, '::'::text) - 1), ':'::text),';',right(mp.upc,8),';',so.kst_ordertype,';',mr_line.c_bpartner_id,';',so.kst_lcdate::date,';CreatedAt:',now()::date) as id,
    mr_line.poreference as line_id,
    mp.m_product_id,
    mr.m_warehouse_id,
    mr_line.c_bpartner_id,
    mr_line.QtyEntered,
    so.kst_ordertype,
    mr.m_requisition_id,
    mr_line.m_requisitionline_id,
    mpc.m_product_category_id,
    mr.m_pricelist_id,
    mr_line.c_currency_id,
    so.kst_brand,
    so.kst_season,
    so.poreference,
    0 as so_id,
    mp.upc,
    mr_line.datepromised
from m_requisitionline as mr_line
left join m_requisition as mr on mr_line.m_requisition_id = mr.m_requisition_id
left join c_order as so on mr_line.poreference = so.poreference
left join m_product as mp on mr_line.m_product_id = mp.m_product_id
left join m_product_category as mpc on mp.m_product_category_id = mpc.m_product_category_id
where so.C_DocTypeTarget_ID <> 1000082 and mr_line.isactive  = 'Y' and mp.kst_isila = 'N'
and so.kst_lcdate = p_lc_date and mpc.m_product_category_id = p_product_category
group by mp.m_product_id,
    mr.m_requisition_id,
    mr_line.m_requisitionline_id,
    so.c_order_id,
    mpc.m_product_category_id;
raise NOTICE 'Populate Data Auto Allocate From PO is Completed';
end;
$function$
;

select * from rma_populate_fabric
-- adempiere.rma_populate_fabric source

CREATE OR REPLACE VIEW adempiere.rma_populate_fabric
AS SELECT (((((((po.m_warehouse_id || ';'::text) || po.upc::text) || ';'::text) || po.kst_ordertype::text) || ';'::text) ||
        CASE
            WHEN (( SELECT so.c_country_id
               FROM c_order so
              WHERE so.c_order_id = po.so_id
             LIMIT 1)) = ANY (ARRAY[153::numeric, 216::numeric]) THEN 'JPCN'::text
            ELSE 'NonJPCN'::text
        END) || ';'::text) || po.c_bpartner_id AS id,
    (((((((((po.m_warehouse_id || ';'::text) || po.upc::text) || ';'::text) || po.kst_ordertype::text) || ';'::text) ||
        CASE
            WHEN (( SELECT so.c_country_id
               FROM c_order so
              WHERE so.c_order_id = po.so_id
             LIMIT 1)) = ANY (ARRAY[153::numeric, 216::numeric]) THEN 'JPCN'::text
            ELSE 'NonJPCN'::text
        END) || ';'::text) || po.c_bpartner_id) || ';'::text) || po.m_product_id::text AS lineid,
    po.m_product_id,
    po.upc,
    po.m_warehouse_id,
    po.c_bpartner_id,
    po.kst_ordertype,
    po.m_product_category,
    po.m_pricelist_id,
    po.c_currency_id,
    po.kst_brand,
    max(po.kst_season::text) AS kst_season,
        CASE
            WHEN (( SELECT so.c_country_id
               FROM c_order so
              WHERE so.c_order_id = po.so_id
             LIMIT 1)) = ANY (ARRAY[153::numeric, 216::numeric]) THEN 'JPCN'::text
            ELSE 'NonJPCN'::text
        END AS c_country_id,
    sum(po.qtyneed) AS qtyneed,
    po.m_requisitionline_id
   FROM tpb_populate_auto_po po
  GROUP BY po.m_product_id, po.m_warehouse_id, po.c_bpartner_id, po.kst_ordertype, po.m_product_category, po.m_pricelist_id, po.c_currency_id, po.kst_brand, (
        CASE
            WHEN (( SELECT so.c_country_id
               FROM c_order so
              WHERE so.c_order_id = po.so_id
             LIMIT 1)) = ANY (ARRAY[153::numeric, 216::numeric]) THEN 'JPCN'::text
            ELSE 'NonJPCN'::text
        END), po.upc, ((((((((po.m_warehouse_id || ';'::text) || po.upc::text) || ';'::text) || po.kst_ordertype::text) || ';'::text) ||
        CASE
            WHEN (( SELECT so.c_country_id
               FROM c_order so
              WHERE so.c_order_id = po.so_id
             LIMIT 1)) = ANY (ARRAY[153::numeric, 216::numeric]) THEN 'JPCN'::text
            ELSE 'NonJPCN'::text
        END) || ';'::text) || po.c_bpartner_id), ((((((((((po.m_warehouse_id || ';'::text) || po.upc::text) || ';'::text) || po.kst_ordertype::text) || ';'::text) ||
        CASE
            WHEN (( SELECT so.c_country_id
               FROM c_order so
              WHERE so.c_order_id = po.so_id
             LIMIT 1)) = ANY (ARRAY[153::numeric, 216::numeric]) THEN 'JPCN'::text
            ELSE 'NonJPCN'::text
        END) || ';'::text) || po.c_bpartner_id) || ';'::text) || po.m_product_id::text), po.m_requisitionline_id
  ORDER BY ((((((((po.m_warehouse_id || ';'::text) || po.upc::text) || ';'::text) || po.kst_ordertype::text) || ';'::text) ||
        CASE
            WHEN (( SELECT so.c_country_id
               FROM c_order so
              WHERE so.c_order_id = po.so_id
             LIMIT 1)) = ANY (ARRAY[153::numeric, 216::numeric]) THEN 'JPCN'::text
            ELSE 'NonJPCN'::text
        END) || ';'::text) || po.c_bpartner_id);



select * from m_movement mm where m_movement_id =2033109

update m_movement set created = '2020-01-07'
where m_movement_id =2033109


select * from tpb_mapping_po tmp

select name,m_product_category_id from m_product_category mpc where lower("name")in (
'heat transfer',
'zipper'
 )


select tpb_mapping_po_id,* from tpb_mapping_po tmp
 -- adempiere.tpb_populate_auto_po definition

-- adempiere.tpb_populate_auto_po definition

-- Drop table

-- DROP TABLE adempiere.tpb_populate_auto_po CASCADE;


CREATE TABLE adempiere.tpb_populate_auto_po (
    id varchar(255) NULL,
    lineid varchar(255) NULL,
    m_product_id varchar(75) NULL,
    m_warehouse_id numeric NULL,
    c_bpartner_id numeric NULL,
    qtyneed numeric NULL,
    kst_ordertype varchar(50) NULL,
    m_requisition_id numeric NULL,
    m_requisitionline_id numeric NULL,
    m_product_category numeric NULL,
    m_pricelist_id numeric NULL,
    c_currency_id numeric NULL,
    kst_brand varchar(10) NULL,
    kst_season varchar(10) NULL,
    poreference varchar(50) NULL,
    so_id numeric NULL,
    upc varchar(100) null,
    mrd_date timestamp null
);

select datepromised,* from c_orderline co limit 1

-- adempiere.tpb_mapping_po definition

-- Drop table

-- DROP TABLE adempiere.tpb_mapping_po;

CREATE TABLE adempiere.tpb_mapping_po (
    tpb_mapping_po_id numeric(10) NOT NULL,
    tpb_mapping_po_uu varchar(36) NULL DEFAULT NULL::character varying,
    ad_client_id numeric(10) NULL DEFAULT NULL::numeric,
    ad_org_id numeric(10) NULL DEFAULT NULL::numeric,
    created timestamp NULL DEFAULT statement_timestamp(),
    createdby numeric(10) NULL DEFAULT NULL::numeric,
    isactive bpchar(1) NULL DEFAULT 'Y'::bpchar,
    processed bpchar(1) NULL DEFAULT NULL::bpchar,
    processing bpchar(1) NULL DEFAULT NULL::bpchar,
    m_product_category_id numeric(10) NULL DEFAULT NULL::numeric,
    m_warehouse_id numeric(10) NULL DEFAULT NULL::numeric,
    c_doctypetarget_id numeric(10) NULL DEFAULT NULL::numeric,
    tpb_allocation_type varchar(255) NULL DEFAULT NULL::character varying,
    kst_ordertype varchar(50) NULL DEFAULT NULL::character varying,
    updated timestamp NULL DEFAULT statement_timestamp(),
    updatedby numeric(10) NULL DEFAULT NULL::numeric,
    tpb_allocation_type_grup varchar(255) NULL DEFAULT NULL::character varying,
    tpb_mapping_view_name varchar(40) NULL DEFAULT NULL::character varying,
    CONSTRAINT tpb_mapping_po_isactive_check CHECK ((isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]))),
    CONSTRAINT tpb_mapping_po_key PRIMARY KEY (tpb_mapping_po_id),
    CONSTRAINT tpb_mapping_po_processed_check CHECK ((processed = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]))),
    CONSTRAINT tpb_mapping_po_uu_idx UNIQUE (tpb_mapping_po_uu)
);


-- adempiere.tpb_mapping_po foreign keys

ALTER TABLE adempiere.tpb_mapping_po ADD CONSTRAINT cdoctypetarget_tpbmappingpo FOREIGN KEY (c_doctypetarget_id) REFERENCES c_doctype(c_doctype_id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE adempiere.tpb_mapping_po ADD CONSTRAINT mapping_po_product_category_id FOREIGN KEY (m_product_category_id) REFERENCES m_product_category(m_product_category_id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE adempiere.tpb_mapping_po ADD CONSTRAINT mwarehouse_tpbmappingpo FOREIGN KEY (m_warehouse_id) REFERENCES m_warehouse(m_warehouse_id) DEFERRABLE INITIALLY DEFERRED;

INSERT INTO TPB_MAPPING_PO(tpb_mapping_po_id,ad_client_id,ad_org_id,createdby,isactive,processed,processing,m_product_category_id,m_warehouse_id,c_doctypetarget_id,tpb_allocation_type,kst_ordertype,updatedby,tpb_allocation_type_grup) VALUES(1000000,1000000,1000000,1002292,'Y','N','N',1000003,1000001,1000112,'alloc_5','MTF',1002292,'FABRIC');

select * from tpb_populate_auto_po

select m_pricelist_id,* from tpb_populate_auto_po tpap where m_product_id = '1469339'

select * from m_pricelist mp where m_pricelist_id =1000003

select * from m_requisitionline mr where m_requisitionline_id =3302412