2014年10月6日 星期一

adempiere.tg_sk_rmjh_i

-- Function: adempiere.tg_sk_rmjh_i(numeric)

-- DROP FUNCTION adempiere.tg_sk_rmjh_i(numeric);

CREATE OR REPLACE FUNCTION adempiere.tg_sk_rmjh_i(p_instance numeric)
  RETURNS SETOF adempiere.tg_sk_rmjh_t AS
$BODY$ 
/* DEBUG AND CREATE TABLE
CURRENT_LANG =  en_US , zh_CN , zh_TW
set search_path=adempiere
"程式完成:: p_instance=1000545 ,p_Record_ID=0 ,p_DocDate=2013-01-01 - 2013-06-30 ,p_SK_NO=TG-G016 - TG-G016"
select p.Value,Name,pi.* from ad_pinstance pi inner join ad_process p on p.ad_process_id=pi.ad_process_id order by ad_pinstance_id desc
*/
DECLARE

   p RECORD;
   q RECORD;
   r RECORD;
   v_message              VARCHAR (2000);
   ResultStr              VARCHAR (2000);
  
   p_Record_ID            NUMERIC(10)   := null;
   p_AD_User_ID           NUMERIC(10)   := null;
   p_AD_Process_ID        NUMERIC(10)   := null;
   p_AD_Client_ID         NUMERIC(10)   := null;
   p_AD_Org_ID            NUMERIC(10)   := null;
   p_DocDate              DATE;
   p_DocDateTo            DATE;
   p_DocumentNo           VARCHAR (200); --起迄
   p_DocumentNoTo         VARCHAR (200);
   p_DateOrdered          DATE;          --起迄
   p_DateOrderedTo        DATE;
   p_M_Product_ID         NUMERIC(10)   := 0; --起迄
   p_M_ProductTo_ID       NUMERIC(10)   := 0;
   p_C_BPartner_ID        NUMERIC(10)   := 0; --起迄
   p_C_BPartnerTo_ID      NUMERIC(10)   := 0;
   v_SK_NO                VARCHAR (20)  := null; --起迄
   p_SK_NO                VARCHAR (20)  := null; --起迄
   p_SK_NOTo              VARCHAR (20)  := null; --起迄
   p_M_Product_Value      VARCHAR (20)  := null; --起迄
   p_M_ProductEnd_Value   VARCHAR (20)  := null;
   p_C_BPartner_Value     VARCHAR (20)  := null; --起迄
   p_C_BPartnerEnd_Value  VARCHAR (20)  := null;
   p_instance_id           NUMERIC(10)  := 0;
  
BEGIN
   IF p_instance is null THEN
      p_instance_id:=0;
   ELSE
      p_instance_id:=p_instance;
   END IF;
   v_message :='程式開始..';

   IF p_instance_id > 0 THEN
      BEGIN
         ResultStr := 'p_instanceNotFound';
         UPDATE adempiere.ad_pinstance
            SET updated      = NOW(),
                isprocessing = 'Y',
                result       = 0,
                errormsg     = ResultStr
          WHERE ad_pinstance_id = p_instance_id;
      EXCEPTION
           WHEN OTHERS THEN NULL;
      END;
   END IF;

   FOR p IN (
       SELECT i.ad_pinstance_id, i.ad_process_id,
              i.record_id,       i.ad_user_id,
              i.ad_client_id,    i.ad_org_id,
              pp.seqno,          pp.parametername,
              pp.p_string,       pp.p_string_to,
              pp.p_number,       pp.p_number_to,
              pp.p_date,         pp.p_date_to,
              pp.info,           pp.info_to
         FROM adempiere.ad_pinstance i
   INNER JOIN adempiere.ad_pinstance_para pp ON pp.ad_pinstance_id=i.ad_pinstance_id
        WHERE i.ad_pinstance_id=p_instance_id
     ORDER BY pp.seqno
   ) LOOP
      v_message := '讀取傳遞參數表';
      p_Record_ID    :=p.record_id;
      p_AD_Process_ID:=p.ad_process_id;
      p_AD_User_ID   :=p.ad_user_id;
      p_AD_Client_ID :=p.ad_client_id;  
      p_AD_Org_ID    :=p.ad_org_id;
      IF p.parametername = 'DocDate' THEN
         p_DocDate   :=p.p_date;
         p_DocDateTo :=p.p_date_to;
      ELSE IF p.parametername = 'SK_NO' THEN
         p_SK_NO   :=p.p_string;
         p_SK_NOTo :=p.p_string_to;
      END IF;
      END IF;
   END LOOP;

  
   v_message := 'p_instance=1000000';
   IF COALESCE(p_instance,1000000) = 1000000 THEN --OR p_Record_ID = 0 THEN

     /* FOR r IN (
          SELECT x.vcount , x.sk_rmjh_id
            FROM (
                  SELECT COUNT(*) AS vcount , h.sk_rmjh_id
                    FROM adempiere.sk_rmjh h
              INNER JOIN adempiere.sk_fgjl l ON h.sk_rmjh_id=l.sk_rmjh_id
                GROUP BY h.sk_rmjh_id
                  ) x
           ORDER BY vcount DESC LIMIT 1
      ) LOOP
         v_message := '如果沒資料抓一筆來測試';
         p_Record_ID := r.sk_rmjh_id;
      END LOOP; */
     -- p_DocDate   := '2013.01.01'::DATE;
     -- p_DocDateTo := date_trunc('month',p_DocDate)+'1month'::interval-'1day'::interval;
     -- p_SK_NO     := 'TG-G016';
   END IF;
  
   IF p_DocDateTo IS NULL AND p_DocDate IS NOT NULL THEN
      p_DocDateTo := p_DocDate;
   END IF;
   IF p_SK_NOTo IS NULL AND p_SK_NO IS NOT NULL THEN
      p_SK_NOTo := p_SK_NO;
   END IF;
   -- 1) TABLE
   DELETE FROM adempiere.tg_sk_rmjh_t WHERE ad_pinstance_id = p_instance;
   -- 2)  INSERT INOT TABLE SELECT


  v_SK_NO := null;
  v_message :='xxxx,'||' p_instance='||p_instance||' ,p_Record_ID='||p_Record_ID
   ||' ,p_DocDate='||p_DocDate||' - '||p_DocDateTo||' ,p_SK_NO='||p_SK_NO||' - '||p_SK_NOTo;
  FOR r IN (
    SELECT
      h.sk_rmjh_id        ,--numeric(10,0) NOT NULL,
      h.ad_client_id      ,--numeric(10,0) NOT NULL,
      h.ad_org_id         ,--numeric(10,0) NOT NULL,
      h.isactive          ,--character(1) NOT NULL DEFAULT 'Y'::bpchar,
      h.created           ,--timestamp without time zone NOT NULL DEFAULT now(),
      h.createdby         ,--numeric(10,0) NOT NULL,
      h.updated           ,--timestamp without time zone NOT NULL DEFAULT now(),
      h.updatedby         ,--numeric(10,0) NOT NULL,
      h.sk_no             ,--character varying(40),
      h.docdate           ,--date,
      h.docdateto         ,--date,
      h.sk_rmjh_uu        ,--character varying(36) DEFAULT NULL::character varying,
      h.sk_name           ,--character varying(80),
     --sk_nowqty          ,--numeric(14,2)
    coalesce((select l.sk_nowqty from adempiere.sk_fgjl l
               where l.sk_no=h.sk_no
                 and date_trunc('day',l.DocDate)  < date_trunc('day', p_DocDate) order by l.DocDate desc, sk_fgjl_id desc limit 1),
              coalesce(h.sk_nowqty),0) as sk_nowqty
    FROM adempiere.sk_rmjh h
    WHERE 1=1
       AND   (p_DocDate is null or(
              date_trunc('day',h.DocDate)   <= date_trunc('day',p_DocDate) AND
              date_trunc('day',h.DocDateTo) >= date_trunc('day',p_DocDateTo)))
       AND   (TRIM(p_SK_NO) is null OR TRIM(h.sk_no) BETWEEN TRIM(p_SK_NO) AND TRIM(p_SK_NOTo))
    ORDER BY h.sk_no
    --p_DocDate=2013-01-01 - 2013-06-30 ,p_SK_NO=TG-G016 - TG-G016"
   --AND   (date_trunc('day',h.DocDate)   <= date_trunc('day',timestamp '2013-01-01 00:00:00') AND
   --       date_trunc('day',h.DocDateTo) >= date_trunc('day',timestamp '2013-01-22 00:00:00'))
   --  p_DocDate=2013-01-01 - 2013-10-22 ,p_SK_NO=TG-G016 - TG-G016

  )LOOP

   v_message :='程式完成::'||' p_instance='||p_instance||' ,p_Record_ID='||p_Record_ID
   ||' ,p_DocDate='||p_DocDate||' - '||p_DocDateTo||' ,p_SK_NO='||p_SK_NO||' - '||p_SK_NOTo;
    --select * from tg_sk_rmjh_t
    IF (v_SK_NO IS NULL OR v_SK_NO <> r.sk_no ) THEN
    -- INSERT INTO
    INSERT INTO adempiere.tg_sk_rmjh_t(
    ad_pinstance_id   ,sk_rmjh_id        ,ad_client_id      ,ad_org_id        ,--numeric(10,0) NOT NULL,
    isactive          ,created           ,createdby         ,updated          ,--timestamp without time zone NOT NULL DEFAULT now(),
    updatedby         ,
    sk_no             ,docdate           ,docdateto         ,sk_rmjh_uu       ,--character varying(36) DEFAULT NULL::character varying,
    sk_name           ,sk_nowqty         ,description      
    ) VALUES (
    p_instance_id     ,r.sk_rmjh_id      ,r.ad_client_id    ,r.ad_org_id      ,--numeric(10,0) NOT NULL,
    r.isactive        ,r.created         ,r.createdby       ,r.updated        ,--timestamp without time zone NOT NULL DEFAULT now(),
    r.updatedby       ,
    r.sk_no           ,p_docdate         ,p_docdateto       ,r.sk_rmjh_uu     ,--character varying(36) DEFAULT NULL::character varying,
    r.sk_name         ,r.sk_nowqty       ,'docdate='||r.docdate||' ,docdateto='||r.docdateto --description
    );
    END IF;
    v_SK_NO := r.sk_no;
  END LOOP;


 --  v_message :='程式完成::'||' p_instance='||p_instance||' ,p_Record_ID='||p_Record_ID
 --  ||' ,p_DocDate='||p_DocDate||' - '||p_DocDateTo||' ,p_SK_NO='||p_SK_NO||' - '||p_SK_NOTo;
   IF p_instance_id > 0 THEN
     BEGIN
        UPDATE adempiere.ad_pinstance
           SET updated         = NOW(),
               isprocessing    = 'N',
               result          = 1,
               errormsg        = v_message
         WHERE ad_pinstance_id = p_instance_id;
     EXCEPTION
          WHEN OTHERS THEN NULL;
     END;
   END IF;

  -- 3) RETURN
  RETURN QUERY SELECT *
             FROM adempiere.tg_sk_rmjh_t
            WHERE ad_pinstance_id = p_instance;


   --raise notice 'Yo this is good! --> % %', SQLERRM, SQLSTATE;
EXCEPTION WHEN OTHERS THEN
   v_message :='程式失敗::'||SQLERRM||' ,SQLSTATE='||SQLSTATE||' ,v_message='||v_message;
   IF p_instance_id > 0 THEN
     BEGIN
        ResultStr := v_message;
        UPDATE adempiere.ad_pinstance
         SET updated      = NOW(),
             isprocessing = 'N',
             result       = 0,
             errormsg     = ResultStr
         WHERE ad_pinstance_id = p_instance_id;
     EXCEPTION
          WHEN OTHERS THEN NULL;
     END;
   END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION adempiere.tg_sk_rmjh_i(numeric)
  OWNER TO adempiere;
張貼者： 於 沒有留言:

adempiere.sk_gen_rmjl

-- Function: adempiere.sk_gen_rmjl(numeric)

-- DROP FUNCTION adempiere.sk_gen_rmjl(numeric);

CREATE OR REPLACE FUNCTION adempiere.sk_gen_rmjl(pinstance numeric)
  RETURNS void AS
$BODY$
-- ALTER TABLE adempiere.SK_RMJH
-- ALTER COLUMN sk_name TYPE varchar(80)
-- set search_path=adempiere
-- SELECT * FROM adempiere.SK_RMJH
-- SELECT * FROM adempiere.SK_RMJL
-- select * from adempiere.SK_RMJH where sk_no='TG-G016'   1052702    608.00
-- select * from adempiere.SK_RMJL where SK_RMJH_ID=1052702 order by SK_RMJL_id
-- select adempiere.sk_gen_rmjl(1000000);
-- select * from adempiere.ad_pinstance where ad_pinstance_id=1000000
DECLARE
   text_var1              text;
   text_var2              text;
   text_var3              text;
   ResultStr              VARCHAR (200);
   roleaccesslevelwin     VARCHAR (200);
   sqlins                 VARCHAR (4000);
   sqlupd                 VARCHAR (4000);
   sqlwindowaccess        VARCHAR (4000);
   p                      RECORD;
   q                      RECORD;
   r                      RECORD;
  
   inv                    RECORD;
   o_nextid               NUMERIC(10)   := 0;
   o_nextheadid           NUMERIC(10)   := 0;
   o_nextlineid           NUMERIC(10)   := 0;
   p_Record_ID            NUMERIC(10)   := 0;
   p_AD_User_ID           NUMERIC(10)   := 0;
   p_AD_Process_ID        NUMERIC(10)   := 0;
   p_AD_Client_ID         NUMERIC(10)   := 0;
   p_AD_Org_ID            NUMERIC(10)   := 0;
   p_DocDate              DATE;
   p_DocDateTo            DATE;
   p_DocumentNo           VARCHAR (200); --起迄
   p_DocumentNoTo         VARCHAR (200);
   p_DateOrdered          DATE;          --起迄
   p_DateOrderedTo        DATE;
   p_M_Product_ID         NUMERIC(10)   := 0; --起迄
   p_M_ProductTo_ID       NUMERIC(10)   := 0;
   p_C_BPartner_ID        NUMERIC(10)   := 0;--起迄
   p_C_BPartnerTo_ID      NUMERIC(10)   := 0;
  
   p_M_Product_Value      VARCHAR (200); --起迄
   p_M_ProductEnd_Value   VARCHAR (200);
   p_C_BPartner_Value     VARCHAR (200);--起迄
   p_C_BPartnerEnd_Value  VARCHAR (200);
   v_message              VARCHAR(400)  := '';
   v_NextNo               NUMERIC(10)   := 0;
   p_IsAllActive          VARCHAR(1)    := 'Y'; --IsAllActive
   p_AD_Tab_ID            NUMERIC(10)   := 0;
   pinstance_id           NUMERIC(10)   := 0;
   P_NOWQTY               NUMERIC(14,4) := 0;
   v_count1               NUMERIC(10)   := 0;
   v_count2               NUMERIC(10)   := 0;
BEGIN
   IF pinstance is null THEN
      pinstance_id:=0;
   ELSE
      pinstance_id:=pinstance;
   END IF;
   v_message :='程式開始..';

   IF pinstance_id > 0 THEN
      BEGIN
         ResultStr := 'PInstanceNotFound';
         UPDATE adempiere.ad_pinstance
            SET updated      = NOW(),
                isprocessing = 'Y',
                result       = 0,
                errormsg     = ResultStr
          WHERE ad_pinstance_id = pinstance_id;
      EXCEPTION
           WHEN OTHERS THEN NULL;
      END;
   END IF;

      FOR p IN (
       SELECT  i.ad_pinstance_id, i.ad_process_id,
               i.record_id,       i.ad_user_id,
               i.ad_client_id,    i.ad_org_id,
               pp.seqno,          pp.parametername,
               pp.p_string,       pp.p_string_to,
               pp.p_number,       pp.p_number_to,
               pp.p_date,         pp.p_date_to,
               pp.info,           pp.info_to
               FROM adempiere.ad_pinstance i
         INNER JOIN adempiere.ad_pinstance_para pp ON pp.ad_pinstance_id=i.ad_pinstance_id
              WHERE i.ad_pinstance_id=pinstance_id
           ORDER BY pp.seqno
    ) LOOP
      v_message := '讀取傳遞參數表';
      p_Record_ID    :=p.record_id;
      p_AD_Process_ID:=p.ad_process_id;
      p_AD_User_ID   :=p.ad_user_id;
      p_AD_Client_ID :=p.ad_client_id;  
      p_AD_Org_ID    :=p.ad_org_id;
      IF p.parametername = 'DocDate' THEN
         p_DocDate   :=p_date;
         p_DocDateTo :=p_date_to;
      END IF;
   END LOOP;

      -- SELECT date_trunc('month', current_date),date_trunc('month', current_date)+'1month'::interval-'1day'::interval
      p_DocDate      := coalesce(p_DocDate, date_trunc('month', current_date));
      p_DocDateTo    := coalesce(p_DocDate, date_trunc('month', current_date)+'1month'::interval-'1day'::interval);

      p_DocDate   := p_DocDate    -'100month'::interval; --假設兩個月前
      p_DocDateTo := p_DocDateTo  -'2month'::interval;

      p_AD_User_ID   :=coalesce(p_AD_User_ID,0);
      p_AD_Client_ID :=coalesce(p_AD_Client_ID,1000000);
      p_AD_Org_ID    :=coalesce(p_AD_Org_ID,0);

   --第一層抓出需要的料號
   FOR p IN (
       SELECT sk.sk_no, sk.sk_name, 
     coalesce((select sum(a.sh_qty)
                from adempiere.z_sphd a
                where sh_skno = sk.sk_no
                  and sh_sfg = '1'                                           -- 成品產出
                  and (a.sh_date > '20051231' and a.sh_date < p_DocDate)),0) -- 當期之前有進出

   + coalesce((select sum(sd_qty)
                 from adempiere.z_sslpdt
                where sd_skno = sk.sk_no
                  and((sd_class = '0' and sd_slip_fg = '0') OR           -- 進貨入庫
                      (sd_class = '5' and sd_slip_fg = 'A'))
                  and (sd_date > '20051231' and sd_date < p_DocDate)),0) -- 當期之前有進出
 
   + coalesce((select sum(b.sd_qty)
                 from adempiere.z_sslip  a
           inner join adempiere.z_sslpdt b on b.sd_class= a.sp_class and b.sd_slip_fg = a.sp_slip_fg and b.sd_no = a.sp_no
         --inner join adempiere.z_sstock s on s.sk_no = b.sd_skno and s.sk_ikind <> '2' --非成品
                where b.sd_skno = sk.sk_no
                  and a.sp_class = '1' and a.sp_slip_fg = '3'                       -- 銷售退回
                  and (a.sp_date > '20051231' and a.sp_date < p_DocDate)),0),       -- 當期之前有進出

   + coalesce((select sum(b.sd_qty)
                 from adempiere.z_sslip  a
           inner join adempiere.z_sslpdt b on b.sd_class= a.sp_class and b.sd_slip_fg = a.sp_slip_fg and b.sd_no = a.sp_no
         --inner join adempiere.z_sstock s on s.sk_no = b.sd_skno and s.sk_ikind <> '2' --非成品
                where b.sd_skno = sk.sk_no
                  and a.sp_class = '6' and a.sp_slip_fg = 'R'                       -- 借入
                  and (a.sp_date > '20051231' and a.sp_date < p_DocDate)),0),       -- 當期之前有進出
                 
   -- 借貨
   - coalesce((select sum(b.sd_qty)
                 from adempiere.z_sslip  a
           inner join adempiere.z_sslpdt b on b.sd_class= a.sp_class and b.sd_slip_fg = a.sp_slip_fg and b.sd_no = a.sp_no
        -- inner join adempiere.z_sstock s on s.sk_no = b.sd_skno and s.sk_ikind <> '2' --非成品
                where b.sd_skno = sk.sk_no
                  and a.sp_class = '2' and a.sp_slip_fg = '4'                       -- 借貨
                  and (a.sp_date > '20051231' and a.sp_date < p_DocDate)),0),       -- 當期之前有進出

   - coalesce((select sum(sd_qty) 
                 from adempiere.z_spdt
        -- inner join adempiere.z_sphd b on b.sd_spno = a.sh_spno and b.sd_sfg=a.sh_sfg
                where sd_skno = sk.sk_no
                  and sd_sfg = '3'                                       -- 生產領用
                  and (sd_date > '20051231' and sd_date < p_DocDate)),0) -- 當期之前有進出

   - coalesce((select sum(b.sd_qty)
                 from adempiere.z_sslip  a
           inner join adempiere.z_sslpdt b on b.sd_class= a.sp_class and b.sd_slip_fg = a.sp_slip_fg and b.sd_no = a.sp_no
       --  inner join adempiere.z_sstock s on s.sk_no = b.sd_skno and s.sk_ikind <> '2' --非成品
                where b.sd_skno = sk.sk_no
                  and a.sp_class = '1' and a.sp_slip_fg = '2'                                           -- 成品銷售
                  and (a.sp_date > '20051231' and a.sp_date < p_DocDate)),0) AS NOWQTY   -- 當期之前有進出

              FROM adempiere.z_sstock sk
              inner join adempiere.z_bonding_product bp on bp.sk_no = sk.sk_no
             WHERE 1=1  -- and trim(sk_no)='1F-OFS905'
            -- AND sk_ikind in('4', '5', '6') --  4 物料,5 原料,6 其他
            -- AND sk_ikind <> '2'            --  非成品
               AND bp.ptype = '5'  -- bp.ptype 2 是成品 5 是原料
          ORDER BY sk.sk_ikind,sk.sk_no
      ) LOOP
      v_message := '讀取傳遞參數表';
      P_NOWQTY  := p.NOWQTY;
--    記錄下有期初 或是 本期有異動的料號 當作單頭
--    SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_RMJH'; --1000195
      SELECT adempiere.nextid(1000195, 'N') INTO o_nextid; -- SK_RMJH
  
      sqlins := 'INSERT INTO SK_RMJH(sk_rmjh_id, ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby,'
             || 'sk_no, sk_name, sk_nowqty, docdate, docdateto, sk_rmjh_uu)VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)';
      
      EXECUTE sqlins USING  o_nextid, p_AD_Client_ID, p_AD_Org_ID, 'Y', now(), p_AD_User_ID,  now(), p_AD_User_ID,
                            p.sk_no, p.sk_name, P_NOWQTY, p_DocDate, p_DocDateTo, adempiere.generate_uuid();
      --SET search_path=adempiere
      --SELECT adempiere.generate_uuid()::char(36)

   -- select  date_trunc('day', now())
     
-- 第二層  取出該料號有異動的日期
   FOR q IN (
       select distinct 
             z.docdate,
             date_part('year',  z.docdate) as year,
             date_part('month', z.docdate) as month,
             date_part('day',   z.docdate) as date
       from ( 
              select distinct(date_trunc('day', sh_date )) as docdate
                from adempiere.z_sphd
                where 1=1
                  and sh_skno = p.sk_no
                  and sh_sfg = '1'                                           -- 成品產出
                  and sh_date between p_DocDate and p_DocDateTo
         union
               select distinct(date_trunc('day',sd_date)) as docdate
                 from adempiere.z_sslpdt
                where 1=1
                  and sd_skno = p.sk_no
                  and((sd_class = '0' and sd_slip_fg = '0') OR                -- 進貨入庫
                      (sd_class = '5' and sd_slip_fg = 'A'))
                  and sd_date between p_DocDate and p_DocDateTo
         union
               select distinct(date_trunc('day', sd_date ))  as docdate
                 from adempiere.z_spdt
                where 1=1
                  and sd_skno = p.sk_no
                  and sd_sfg = '3'                                            -- 生產領用
                  and sd_date between p_DocDate and p_DocDateTo

         union
               select distinct(date_trunc('day', a.sp_date ))  as docdate
                 from adempiere.z_sslip  a
           inner join adempiere.z_sslpdt b on b.sd_class= a.sp_class and b.sd_slip_fg = a.sp_slip_fg and b.sd_no = a.sp_no
        -- inner join adempiere.z_sstock s on s.sk_no = b.sd_skno and s.sk_ikind = '2' --成品
                where 1=1
                  and b.sd_skno = p.sk_no
                  and ((a.sp_class = '1' and (a.sp_slip_fg = '2' or a.sp_slip_fg = '3'))or     -- 成品銷售 and  銷售退回
                       (a.sp_class = '2' and (a.sp_slip_fg = '4'                      ))or     -- 借貨
                       (a.sp_class = '6' and (a.sp_slip_fg = 'R'                      )))      -- 借入
                  and a.sp_date between p_DocDate and p_DocDateTo
               
      ) z
    --   inner join adempiere.z_sstock sk on bp.sk_no = sk.sk_no and sk.sk_ikind <> '2'
      order by z.docdate
   ) LOOP
      v_message := '有用到的日期';

      sqlins := 'INSERT INTO SK_RMJL(sk_rmjh_id, sk_rmjl_id,'                                --1..2
             ||  'ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby,' --3..9
             ||  'sk_no, docdate, sp_no, in_qty, out_qty, sk_nowqty, out_date, bonding_no, exp_qty, loc_qty, sk_rmjl_uu'   --10..20
             ||  ')VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20)';
-- 第三層明細分 in/out
-- in detail
   v_message := 'p.sk_no='||p.sk_no||' ,q.docdate='||q.docdate;

   -- 產出 
   FOR r IN (  
          select
          a.sh_spno                as doc_no,     --存(出)倉單證號碼
          a.sh_qty                 as in_qty,     --存倉數量  / 出口數量
          null::numeric(10,2)      as out_qty,    --出倉數量
          null::numeric(10,2)      as exp_qty,    --外銷數量
          null::numeric(10,2)      as loc_qty,    --內銷數量
          null::date               as out_date,   --驗放日期,
          null::varchar(20)        as bonding_no  --報單號碼,
     from adempiere.z_sphd a
    where 1=1
      and a.sh_skno =p.sk_no    -- 必須同第一層的料號
      and a.sh_date =q.docdate  -- 必須同第二層的日期
      and a.sh_sfg = '1'        -- 組合單 產出  就是成品入庫
 order by a.sh_spno
   ) LOOP
      v_message := '產出';
      P_NOWQTY  := P_NOWQTY + coalesce(r.in_qty,0);
   -- SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_RMJL'; --1000204
      SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_RMJL
      EXECUTE sqlins USING o_nextid, o_nextlineid,
                        p_AD_Client_ID, p_AD_Org_ID, 'Y', now(), p_AD_User_ID, now(), p_AD_User_ID,
                        p.sk_no,      --料號 (不同要跳頁)
                        q.docdate,
                        r.doc_no,     --存(出)倉單證號碼
                        r.in_qty,     --存倉數量
                        r.out_qty,    --出倉數量
                        P_NOWQTY,     --帳面庫存數量 sk_nowqty
                        r.out_date,   --驗放日期
                        r.bonding_no, --報單號碼
                        r.exp_qty,    --出口數量 exp_qty
                        r.loc_qty,    --內銷數量 loc_qty
                        adempiere.generate_uuid(); --sk_rmjl_uu
   END LOOP; --產出

  
   --進貨入庫
   FOR r IN (  
          select
          sd_no                  as doc_no,     --存(出)倉單證號碼
          sd_qty                 as in_qty,     --存倉數量  / 出口數量
          null::numeric(10,2)    as out_qty,    --出倉數量
          null::numeric(10,2)    as exp_qty,    --外銷數量
          null::numeric(10,2)    as loc_qty,    --內銷數量
          null::date             as out_date,   --驗放日期,
          null::varchar(20)      as bonding_no  --報單號碼,
     from adempiere.z_sslpdt
    where 1=1
      and sd_skno               = p.sk_no    -- 必須同第一層的料號
      and sd_date = q.docdate  -- 必須同第二層的日期
      and((sd_class = '0' and sd_slip_fg = '0') OR -- 進貨入庫
          (sd_class = '5' and sd_slip_fg = 'A'))
 order by sd_no
   ) LOOP
      v_message := '進貨入庫';
      P_NOWQTY  := P_NOWQTY + coalesce(r.in_qty,0);
   -- SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_RMJL'; --1000204
      SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_RMJL
      EXECUTE sqlins USING o_nextid, o_nextlineid,
                        p_AD_Client_ID, p_AD_Org_ID, 'Y', now(), p_AD_User_ID, now(), p_AD_User_ID,
                        p.sk_no,      --料號 (不同要跳頁)
                        q.docdate,
                        r.doc_no,     --存(出)倉單證號碼
                        r.in_qty,     --存倉數量
                        r.out_qty,    --出倉數量
                        P_NOWQTY,     --帳面庫存數量 sk_nowqty
                        r.out_date,   --驗放日期
                        r.bonding_no, --報單號碼
                        r.exp_qty,    --出口數量 exp_qty
                        r.loc_qty,    --內銷數量 loc_qty
                        adempiere.generate_uuid(); --sk_rmjl_uu
   END LOOP; --進貨入庫

   --銷貨退回
   FOR r IN (select      a.sp_no   as doc_no,
                         b.sd_qty  as in_qty,
             null::numeric(14,2)   as out_qty,
             null::numeric(14,2)   as exp_qty,     --出口數量
             null::numeric(14,2)   as loc_qty,     --內銷數量
             null::date            as out_date,
             null::varchar(20)     as bonding_no
        from adempiere.z_sslip a
  inner join adempiere.z_sslpdt b on b.sd_class= a.sp_class and b.sd_slip_fg = a.sp_slip_fg and b.sd_no = a.sp_no
  inner join adempiere.z_sstock s on s.sk_no=b.sd_skno
       where 1=1
         and b.sd_skno            = p.sk_no       -- 必須同第一層的料號
         and a.sp_date= q.docdate     -- 必須同第二層的日期
         and a.sp_class   = '1' and a.sp_slip_fg = '3'  -- 成品出貨             銷貨退回
     --  and s.sk_ikind   = '2' --成品
    order by a.sp_no
   ) LOOP
      v_message := '銷貨退回';
      P_NOWQTY  := P_NOWQTY + coalesce(r.in_qty,0);
    --SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_RMJL'; --1000204
      SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_RMJL
     EXECUTE sqlins USING o_nextid, o_nextlineid,
                           p_AD_Client_ID, p_AD_Org_ID, 'Y', now(),p_AD_User_ID, now(),p_AD_User_ID,
                           p.sk_no,      --料號
                           q.docdate,
                           r.doc_no,     --存(出)倉單證號碼
                           r.in_qty,     --存倉數量
                           r.out_qty,    --出倉數量
                           P_NOWQTY,     --帳面庫存數量 sk_nowqty
                           r.out_date,   --驗放日期
                           r.bonding_no, --報單號碼
                           r.exp_qty,    --出口數量 exp_qty
                           r.loc_qty,    --內銷數量 loc_qty
                           adempiere.generate_uuid(); --sk_rmjl_uu
   END LOOP; --銷貨退回
  
   --借入
   FOR r IN (select      a.sp_no   as doc_no,
                         b.sd_qty  as in_qty,
             null::numeric(14,2)   as out_qty,
             null::numeric(14,2)   as exp_qty,     --出口數量
             null::numeric(14,2)   as loc_qty,     --內銷數量
             null::date            as out_date,
             null::varchar(20)     as bonding_no
        from adempiere.z_sslip a
  inner join adempiere.z_sslpdt b on b.sd_class= a.sp_class and b.sd_slip_fg = a.sp_slip_fg and b.sd_no = a.sp_no
  inner join adempiere.z_sstock s on s.sk_no=b.sd_skno
       where 1=1
         and b.sd_skno            = p.sk_no       -- 必須同第一層的料號
         and a.sp_date= q.docdate     -- 必須同第二層的日期
         and a.sp_class   = '6' and a.sp_slip_fg = 'R'  -- 借入
     --  and s.sk_ikind   = '2' --成品
    order by a.sp_no
   ) LOOP
      v_message := '借入';
      P_NOWQTY  := P_NOWQTY + coalesce(r.in_qty,0);
    --SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_RMJL'; --1000204
      SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_RMJL
     EXECUTE sqlins USING o_nextid, o_nextlineid,
                           p_AD_Client_ID, p_AD_Org_ID, 'Y', now(),p_AD_User_ID, now(),p_AD_User_ID,
                           p.sk_no,      --料號
                           q.docdate,
                           r.doc_no,     --存(出)倉單證號碼
                           r.in_qty,     --存倉數量
                           r.out_qty,    --出倉數量
                           P_NOWQTY,     --帳面庫存數量 sk_nowqty
                           r.out_date,   --驗放日期
                           r.bonding_no, --報單號碼
                           r.exp_qty,    --出口數量 exp_qty
                           r.loc_qty,    --內銷數量 loc_qty
                           adempiere.generate_uuid(); --sk_rmjl_uu
   END LOOP; --借入
  
   --借貨
   FOR r IN (select      a.sp_no   as doc_no,
             null::numeric(14,2)   as in_qty,
                         b.sd_qty  as out_qty,
                         b.sd_qty  as exp_qty,     --出口數量
             null::numeric(14,2)   as loc_qty,     --內銷數量
             null::date            as out_date,
             null::varchar(20)     as bonding_no
        from adempiere.z_sslip a
  inner join adempiere.z_sslpdt b on b.sd_class= a.sp_class and b.sd_slip_fg = a.sp_slip_fg and b.sd_no = a.sp_no
  inner join adempiere.z_sstock s on s.sk_no=b.sd_skno
       where 1=1
         and b.sd_skno    = p.sk_no       -- 必須同第一層的料號
         and a.sp_date    = q.docdate     -- 必須同第二層的日期
         and a.sp_class   = '2' and a.sp_slip_fg = '4'  -- 借貨
      -- and s.sk_ikind   = '2' --成品
    order by a.sp_no
   ) LOOP
      v_message := '借貨';
      P_NOWQTY  := P_NOWQTY - coalesce(r.out_qty,0);
    --SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_RMJL'; --1000204
      SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_RMJL
     EXECUTE sqlins USING o_nextid, o_nextlineid,
                           p_AD_Client_ID, p_AD_Org_ID, 'Y', now(),p_AD_User_ID, now(),p_AD_User_ID,
                           p.sk_no,      --料號
                           q.docdate,
                           r.doc_no,     --存(出)倉單證號碼
                           r.in_qty,     --存倉數量
                           r.out_qty,    --出倉數量
                           P_NOWQTY,     --帳面庫存數量 sk_nowqty
                           r.out_date,   --驗放日期
                           r.bonding_no, --報單號碼
                           r.exp_qty,    --出口數量 exp_qty
                           r.loc_qty,    --內銷數量 loc_qty
                           adempiere.generate_uuid(); --sk_rmjl_uu
   END LOOP; --銷貨退回
  
   --銷貨
   FOR r IN (select      a.sp_no   as doc_no,
                         b.sd_qty  as out_qty,
             null::numeric(14,2)   as in_qty,
                         b.sd_qty  as exp_qty,     --出口數量
             null::numeric(14,2)   as loc_qty,     --內銷數量
             null::date            as out_date,
             null::varchar(20)     as bonding_no
        from adempiere.z_sslip a
  inner join adempiere.z_sslpdt b on b.sd_class= a.sp_class and b.sd_slip_fg = a.sp_slip_fg and b.sd_no = a.sp_no
  inner join adempiere.z_sstock s on s.sk_no=b.sd_skno
       where 1=1
         and b.sd_skno            = p.sk_no       -- 必須同第一層的料號
         and a.sp_date= q.docdate     -- 必須同第二層的日期
         and a.sp_class   = '1' and a.sp_slip_fg = '2'  -- 成品出貨             銷貨
     --  and s.sk_ikind like '2' --成品
    order by a.sp_no
   ) LOOP
      v_message := '銷貨';
      P_NOWQTY  := P_NOWQTY - coalesce(r.out_qty,0);
    --SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_RMJL'; --1000204
      SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_RMJL
     EXECUTE sqlins USING o_nextid, o_nextlineid,
                           p_AD_Client_ID, p_AD_Org_ID, 'Y', now(),p_AD_User_ID, now(),p_AD_User_ID,
                           p.sk_no,      --料號
                           q.docdate,
                           r.doc_no,     --存(出)倉單證號碼
                           r.in_qty,     --存倉數量
                           r.out_qty,    --出倉數量
                           P_NOWQTY,     --帳面庫存數量 sk_nowqty
                           r.out_date,   --驗放日期
                           r.bonding_no, --報單號碼
                           r.exp_qty,    --出口數量 exp_qty
                           r.loc_qty,    --內銷數量 loc_qty
                           adempiere.generate_uuid(); --sk_rmjl_uu
   END LOOP; --銷貨


   --生產領用
   FOR r IN (select      sd_spno   as doc_no,
             null::numeric(14,4)   as in_qty,
                         sd_qty    as out_qty,
             null::numeric(14,4)   as exp_qty,    --外銷數量
             null::numeric(14,4)   as loc_qty,    --內銷數量
             null::date            as out_date,
             null::varchar(20)     as bonding_no
        from adempiere.z_spdt
       where 1=1
         and sd_skno            = p.sk_no       -- 必須同第一層的料號
         and sd_date= q.docdate     -- 必須同第二層的日期
         and sd_sfg = '3'                             -- 生產領用
    order by sd_spno
   
   ) LOOP
      v_message := '生產領用';
      P_NOWQTY  := P_NOWQTY - coalesce(r.out_qty,0);
    --SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_RMJL'; --1000204
      SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_RMJL
     EXECUTE sqlins USING o_nextid, o_nextlineid,
                           p_AD_Client_ID, p_AD_Org_ID, 'Y', now(),p_AD_User_ID, now(),p_AD_User_ID,
                           p.sk_no,      --料號
                           q.docdate,
                           r.doc_no,     --存(出)倉單證號碼
                           r.in_qty,     --存倉數量
                           r.out_qty,    --出倉數量
                           P_NOWQTY,     --帳面庫存數量 sk_nowqty
                           r.out_date,   --驗放日期
                           r.bonding_no, --報單號碼
                           0,            --出口數量 exp_qty
                           0,            --內銷數量 loc_qty
                           adempiere.generate_uuid(); --sk_rmjl_uu
   END LOOP; --生產領用

   END LOOP; --date loop
   END LOOP; --head loop

--科學工業園區    鎧暘科技股份有限公司  保稅成品帳
-- 101年        存(出)倉情形            帳面    成品出口情形（含視同出口及內銷）           
-- 月    日    存(出)倉    存倉    出倉    庫存數量    驗放日期    報單號碼    出口數量    內銷數量
--        單證號碼    數量    數量                   
--鎧暘科技股份有限公司  保稅成品帳
--園區事業編號:CS0916   (固定)                                                         
--帳卡編號    :年月日

   v_message :='程式完成::';
   IF pinstance_id > 0 THEN
     BEGIN
        UPDATE adempiere.ad_pinstance
         SET updated      = NOW(),
             isprocessing = 'N',
             result       = 1,
             errormsg     = v_message
         WHERE ad_pinstance_id = pinstance_id;
     EXCEPTION
          WHEN OTHERS THEN NULL;
     END;
   END IF;


   --raise notice 'Yo this is good! --> % %', SQLERRM, SQLSTATE;
EXCEPTION WHEN OTHERS THEN
   v_message :='程式失敗::'||SQLERRM||' ,SQLSTATE='||SQLSTATE||' ,v_message='||v_message;
   IF pinstance_id > 0 THEN
     BEGIN
        ResultStr := v_message;
        UPDATE adempiere.ad_pinstance
         SET updated      = NOW(),
             isprocessing = 'N',
             result       = 0,
             errormsg     = ResultStr
         WHERE ad_pinstance_id = pinstance_id;
     EXCEPTION
          WHEN OTHERS THEN NULL;
     END;
   END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION adempiere.sk_gen_rmjl(numeric)
  OWNER TO adempiere;
張貼者： 於 沒有留言:

adempiere.sk_gen_rmbom

-- Function: adempiere.sk_gen_rmbom(numeric)

-- DROP FUNCTION adempiere.sk_gen_rmbom(numeric);

CREATE OR REPLACE FUNCTION adempiere.sk_gen_rmbom(pinstance numeric)
  RETURNS void AS
$BODY$
-- ALTER TABLE adempiere.SK_FGJH
-- ALTER COLUMN sk_name TYPE varchar(80)
-- set search_path=adempiere
-- DELETE FROM adempiere.sk_gen_fgbom_log
-- DELETE FROM adempiere.SK_BOM_RM;
-- select * FROM adempiere.SK_BOM_RM;
-- select * from adempiere.sk_gen_rmbom_log
-- select adempiere.sk_gen_rmbom(1000000);
-- select * from adempiere.ad_pinstance where ad_pinstance_id=1000000
/*
select a.sd_skno, a.bomname, a.bomvalue, a.version, a.exp_qty, b.linename, b.linevalue, b.lineqty, a.exp_qty *b.lineqty --, b.bom_qty
from
(select  sd_skno, bomname, bomvalue, version,  sum(exp_qty) as exp_qty
from adempiere.sk_bom_rm
group by  sd_skno, bomname, bomvalue, version) a
--order by  sd_skno, bomname, bomvalue, version
inner join
(select  sd_skno, bomname, bomvalue, version,linename, linevalue, lineqty  --, sum(exp_qty*lineqty) as bom_qty
from adempiere.sk_bom_fg
group by  sd_skno, bomname, bomvalue, version,linename, linevalue, lineqty) b on a.sd_skno=b.sd_skno and a.bomname=b.bomname and a.bomvalue=b.bomvalue and a.version=b.version
order by  a.sd_skno, a.bomname, a.bomvalue, a.version, b.linename, b.linevalue, b.lineqty
*/
DECLARE
   text_var1              text;
   text_var2              text;
   text_var3              text;
   ResultStr              VARCHAR (200);
   roleaccesslevelwin     VARCHAR (200);
   sqlins                 VARCHAR (4000);
   sqlupd                 VARCHAR (4000);
   sqlwindowaccess        VARCHAR (4000);
   p                      RECORD;
   q                      RECORD;
   r                      RECORD;
  
   inv                    RECORD;
   o_nextid               NUMERIC(10)   := 0;
   o_nextheadid           NUMERIC(10)   := 0;
   o_nextlineid           NUMERIC(10)   := 0;
   p_Record_ID            NUMERIC(10)   := 0;
   p_AD_User_ID           NUMERIC(10)   := 0;
   p_AD_Process_ID        NUMERIC(10)   := 0;
   p_AD_Client_ID         NUMERIC(10)   := 0;
   p_AD_Org_ID            NUMERIC(10)   := 0;
   p_DocDate              DATE;
   p_DocDateTo            DATE;
   p_DocumentNo           VARCHAR (200); --起迄
   p_DocumentNoTo         VARCHAR (200);
   p_DateOrdered          DATE;          --起迄
   p_DateOrderedTo        DATE;
   p_M_Product_ID         NUMERIC(10)   := 0; --起迄
   p_M_ProductTo_ID       NUMERIC(10)   := 0;
   p_C_BPartner_ID        NUMERIC(10)   := 0;--起迄
   p_C_BPartnerTo_ID      NUMERIC(10)   := 0;
  
   p_M_Product_Value      VARCHAR (200); --起迄
   p_M_ProductEnd_Value   VARCHAR (200);
   p_C_BPartner_Value     VARCHAR (200);--起迄
   p_C_BPartnerEnd_Value  VARCHAR (200);
   v_message              VARCHAR(400)  := '';
   v_NextNo               NUMERIC(10)   := 0;
   p_IsAllActive          VARCHAR(1)    := 'Y'; --IsAllActive
   p_AD_Tab_ID            NUMERIC(10)   := 0;
   pinstance_id           NUMERIC(10)   := 0;
   P_NOWQTY               NUMERIC(14,4) := 0;
   v_count1               NUMERIC(10)   := 0;
   v_count2               NUMERIC(10)   := 0;
   v_BOMAlternative_ID    NUMERIC(10)   := 0;
   v_BOMAlternative_SW    NUMERIC(10)   := 0;
BEGIN
   IF pinstance is null THEN
      pinstance_id:=0;
   ELSE
      pinstance_id:=pinstance;
   END IF;
   v_message :='程式開始..';

   IF pinstance_id > 0 THEN
      BEGIN
         ResultStr := 'PInstanceNotFound';
         UPDATE adempiere.ad_pinstance
            SET updated      = NOW(),
                isprocessing = 'Y',
                result       = 0,
                errormsg     = ResultStr
          WHERE ad_pinstance_id = pinstance_id;
      EXCEPTION
           WHEN OTHERS THEN NULL;
      END;
   END IF;

      FOR p IN (
       SELECT  i.ad_pinstance_id, i.ad_process_id,
               i.record_id,       i.ad_user_id,
               i.ad_client_id,    i.ad_org_id,
               pp.seqno,          pp.parametername,
               pp.p_string,       pp.p_string_to,
               pp.p_number,       pp.p_number_to,
               pp.p_date,         pp.p_date_to,
               pp.info,           pp.info_to
               FROM adempiere.ad_pinstance i
         INNER JOIN adempiere.ad_pinstance_para pp ON pp.ad_pinstance_id=i.ad_pinstance_id
              WHERE i.ad_pinstance_id=pinstance_id
           ORDER BY pp.seqno
    ) LOOP
      v_message := '讀取傳遞參數表';
      p_Record_ID    :=p.record_id;
      p_AD_Process_ID:=p.ad_process_id;
      p_AD_User_ID   :=p.ad_user_id;
      p_AD_Client_ID :=p.ad_client_id;  
      p_AD_Org_ID    :=p.ad_org_id;
      IF p.parametername = 'DocDate' THEN
         p_DocDate   :=p_date;
         p_DocDateTo :=p_date_to;
      END IF;
   END LOOP;
      p_DocDate = coalesce(p_DocDate, current_date-'2month'::interval ); --假設兩個月前); --now()
      -- SELECT date_trunc('month', current_date),date_trunc('month', current_date)+'1month'::interval-'1day'::interval
      p_DocDate      := date_trunc('month', p_DocDate);
      p_DocDateTo    := date_trunc('month', p_DocDate)+'1month'::interval-'1day'::interval;

        p_DocDate   := p_DocDate    -'8month'::interval; --假設 8 個月前
     -- p_DocDateTo := p_DocDateTo  -'2month'::interval;

      p_AD_User_ID   :=coalesce(p_AD_User_ID,0);
      p_AD_Client_ID :=coalesce(p_AD_Client_ID,1000000);
      p_AD_Org_ID    :=coalesce(p_AD_Org_ID,0);

  --  SELECT * FROM adempiere.SK_BOM_RM;
      sqlins := 'INSERT INTO adempiere.SK_BOM_RM('
             || 'SK_BOM_RM_ID, SK_BOM_RM_UU, SP_Date, Doc_No, SD_SKno, Exp_Qty,'
             || 'BOMName, BOMValue, BOMQty, DocumentNo, Version, SK_BOM_ID, LineName, LineValue, LineQty, Effectivedate, Expireddate, SK_BomAlternative_ID, AlternativeName,'
             || 'AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy'
             || ')VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26)';
 --銷貨
   FOR p IN (select trim(b.sd_skno) as sk_no,
                         a.sp_no    as doc_no,
                         a.sp_date  as sp_date,
             null::numeric(14,4)    as in_qty,
                         b.sd_qty   as out_qty,     --出口數量
                         b.sd_qty   as exp_qty,     --出口數量
             null::date             as out_date,
             null::varchar(20)      as bonding_no
        from adempiere.z_sslip a
  inner join adempiere.z_sslpdt b on b.sd_class= a.sp_class and b.sd_slip_fg = a.sp_slip_fg and b.sd_no = a.sp_no
  inner join adempiere.z_sstock s on trim(s.sk_no)=trim(b.sd_skno)
       where 1=1
     --  and trim(b.sd_skno)            = p.sk_no       -- 必須同第一層的料號
     --  and date_trunc('day',a.sp_date)= q.docdate     -- 必須同第二層的日期
         and a.sp_class   = '1' and a.sp_slip_fg = '2'  -- 成品出貨  銷貨
         and s.sk_ikind   = '2' --成品
         and date_trunc('day', a.sp_date) between date_trunc('day',p_DocDate) and date_trunc('day',p_DocDateTo) -- 當期有進出      
    order by b.sd_skno, a.sp_date, a.sp_no
   ) LOOP
      v_message := '銷貨';

   --SELECT * FROM adempiere.SK_BOM b
   FOR q IN (
             SELECT s.Name  AS BOMName,  s.Value   AS BOMValue,  b.BOMQty  AS BOMQty, b.DocumentNo, b.Version, b.SK_BOM_ID,
                    sl.Name AS LineName, sl.Value  AS LineValue, bl.BOMQty AS LineQty,  
                    b.EffectiveDate,   b.ExpiredDate, bl.SK_BOMAlternative_ID, nl.Name AS AlternativeName
               FROM adempiere.SK_BOM b
         INNER JOIN adempiere.SK_BOMLine bl ON b.SK_BOM_ID   =bl.SK_BOM_ID
         INNER JOIN adempiere.SK_Stock   s  ON s.SK_Stock_ID =b.SK_Stock_ID
         INNER JOIN adempiere.SK_Stock   sl ON sl.SK_Stock_ID=bl.SK_Stock_ID
    LEFT OUTER JOIN adempiere.SK_BOMAlternative nl ON bl.SK_BOMAlternative_ID=nl.SK_BOMAlternative_ID
              WHERE s.Value = p.sk_no                                         -- BOM 產出成品
                AND (b.effectivedate IS NULL OR date_trunc('day',p.sp_date ) >= date_trunc('day', b.effectivedate) )
                AND (b.expireddate   IS NULL OR date_trunc('day',p.sp_date ) <= date_trunc('day', b.expireddate)   )
         ORDER BY  COALESCE(bl.SK_BOMAlternative_ID,0)
   ) LOOP
      v_message    := '有用到的日期';
      v_count2     := v_count2 + 1;
     
      -- IF   有替代性質代碼  IF   上一個替代性質 跟 目前這筆不相同 = 這是第一筆
      --                      ELSE                             相同 = 這是第 N 筆
      --
      IF COALESCE(q.SK_BOMAlternative_ID,0) > 0 THEN
         IF v_BOMAlternative_ID <> q.SK_BOMAlternative_ID THEN  -- 第1筆
         -- v_BOMAlternative_ID := q.SK_BOMAlternative_ID;
            v_BOMAlternative_SW := 1;                           -- 這是第 1 筆
         ELSE   
            v_BOMAlternative_SW := v_BOMAlternative_SW + 1 ;    -- 這是第 N 筆
         END IF;
      END IF;
            
      IF COALESCE(q.SK_BOMAlternative_ID,0) = 0 OR v_BOMAlternative_SW = 1 THEN  --
      -- SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_BOM_RM'; --1000200
         SELECT adempiere.nextid(1000200, 'N') INTO o_nextid; -- SK_BOM_RM
         EXECUTE sqlins USING  o_nextid, adempiere.generate_uuid(),
                        p.sp_date, p.doc_no, p.sk_no, p.exp_qty,  
                        q.BOMName,  q.BOMValue,  q.BOMQty, q.DocumentNo, q.Version, q.SK_BOM_ID,
                        q.LineName, q.LineValue, q.LineQty,
                        q.Effectivedate, q.Expireddate, q.SK_BomAlternative_ID,q.AlternativeName,
                        p_AD_Client_ID, p_AD_Org_ID, 'Y', now(), p_AD_User_ID, now(), p_AD_User_ID;
      END IF;
      v_BOMAlternative_ID := q.SK_BOMAlternative_ID;
   END LOOP; --BOM
   END LOOP; --銷貨






   v_message :='程式完成::';
   IF pinstance_id > 0 THEN
     BEGIN
        UPDATE adempiere.ad_pinstance
         SET updated      = NOW(),
             isprocessing = 'N',
             result       = 1,
             errormsg     = v_message
         WHERE ad_pinstance_id = pinstance_id;
     EXCEPTION
          WHEN OTHERS THEN NULL;
     END;
   END IF;

   --raise notice 'Yo this is good! --> % %', SQLERRM, SQLSTATE;
EXCEPTION WHEN OTHERS THEN
   v_message :='程式失敗::'||SQLERRM||' ,SQLSTATE='||SQLSTATE||' ,v_message='||v_message;
   IF pinstance_id > 0 THEN
     BEGIN
        ResultStr := v_message;
        UPDATE adempiere.ad_pinstance
         SET updated      = NOW(),
             isprocessing = 'N',
             result       = 0,
             errormsg     = ResultStr
         WHERE ad_pinstance_id = pinstance_id;
     EXCEPTION
          WHEN OTHERS THEN NULL;
     END;
   END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION adempiere.sk_gen_rmbom(numeric)
  OWNER TO adempiere;
張貼者： 於 沒有留言:

2014年10月4日 星期六

雲端 iDempiere 要改進的地方

雲端 iDempiere 要改進的地方
One Install 要去滿足多個客戶 (Client)  要求是不方便的地方
因此企業規則不可以放在 (System) 要降階到 (Client) 或是 (Org)
目前企業規則的做法
 1. 資料庫端的 Trigger 要加 (Client) 或是 (Org) 判斷
 2. iDempiere 物件(Ex.MOrder.java) 要加 (Client) 或是 (Org) 判斷
 3. iDempiere CallOut  畫面即時運算與驗證警告
 4. iDempiere ValRule 畫面關聯驗證
 5. iDempiere RuleEngine CallOut , Before(After) Action, Before(After) Insert/Update/Delete

張貼者： 於 沒有留言:
訂閱： 文章 (Atom)