xx_gen

-- Function: adempiere.tg_sk_bom_fg_gen(numeric)

-- DROP FUNCTION adempiere.tg_sk_bom_fg_gen(numeric);

CREATE OR REPLACE FUNCTION adempiere.tg_sk_bom_fg_gen(pinstance numeric)
  RETURNS void AS
$BODY$
-- ALTER TABLE adempiere.SK_FGJH
-- ALTER COLUMN sk_name TYPE varchar(80)
--"程式失敗::INSERT 的目標資料行比運算式更多 ,SQLSTATE=42601 ,v_message=有用到的日期"
-- set search_path=adempiere
-- DELETE FROM adempiere.sk_gen_fgbom_log
-- DELETE FROM adempiere.SK_BOM_FG;
-- select * FROM adempiere.SK_BOM_FG;
-- select * from adempiere.sk_gen_fgbom_log
-- select adempiere.sk_gen_fgbom(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_fg
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);
   
      sqlins := 'INSERT INTO adempiere.SK_BOM_FG('
             || 'SK_BOM_FG_ID, SK_BOM_FG_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_FG'; --1000200
         SELECT adempiere.nextid(1000200, 'N') INTO o_nextid; -- SK_BOM_FG
         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.tg_sk_bom_fg_gen(numeric)
  OWNER TO adempiere;