iDempiere ERP BPR 重新建構系統流程

-- Function: adempiere.ly_so_gen_shipment_p(integer)

-- DROP FUNCTION adempiere.ly_so_gen_shipment_p(integer);

CREATE OR REPLACE FUNCTION adempiere.ly_so_gen_shipment_p(p_instance integer)
  RETURNS void AS
$BODY$
/*
set search_path=adempiere
"程式失敗::=函式 uuid_generate_v4() 不存在 ,SQLSTATE=42883 ,v_message=Before Read ::訂單 p_Record_ID=1000002"
"程式成功::p_Record_ID=1000002,p_客戶_id=0,p_訂單單號=,p_產品_id=0,p_客戶單號="
select max(ad_pinstance_id) from adempiere.ad_pinstance  -- 1000788
"程式成功::p_Record_ID=1000002,p_客戶_id=1000002,p_訂單單號=,p_產品_id=0,p_客戶單號="
select * from adempiere.ad_pinstance where ad_pinstance_id = 1000788
SELECT * FROM adempiere.ly_so_gen_shipment_p(1000788::integer)
DELETE   FROM adempiere.訂單產生出貨單明細
SELECT * FROM adempiere.訂單產生出貨單明細
*/
DECLARE

   p RECORD;
   q RECORD;
   r RECORD;
   s RECORD;
   v_message              VARCHAR (2000);
   ResultStr              VARCHAR (2000);
   v_Next_ID              NUMERIC(10)   := null;
   v_Next_UU              VARCHAR (36)  := null;
   v_NextLine_ID          NUMERIC(10)   := null;
   v_NextLine_UU          VARCHAR (36)  := null;
  
   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_客戶_id              NUMERIC(10)   := null;
   p_訂單單號             VARCHAR (200); --起迄
   p_產品_id              NUMERIC(10)   := null;
   p_客戶單號             VARCHAR (200); --起迄
   p_未結案               CHAR (1);
   p_instance_id          NUMERIC(10)   := 0;
   v_sk_no                VARCHAR (20);
BEGIN
   IF p_instance is null THEN
      p_instance_id:=1000000;
   ELSE
      p_instance_id:=p_instance;
   END IF;
  
   v_message :='程式開始..p_instance_id='||p_instance_id;

   BEGIN
         ResultStr := 'p_instanceNotFound'||v_message;
         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;

   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
   LEFT JOIN adempiere.ad_pinstance_para pp ON i.ad_pinstance_id=pp.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;
   --   END IF;
   END LOOP;

    v_message :='程式開始..p_Record_ID='||p_Record_ID;

   IF COALESCE(p_instance_id,1000000) = 1000000 THEN --OR p_Record_ID = 0 THEN
   --  p_DocDate   := '2013.01.01'::DATE;
   --  p_DocDateTo := date_trunc('month',p_DocDate)+'1month'::interval-'1day'::interval;
      FOR r IN (
          SELECT MAX("訂單產生出貨單_ID") Record_ID FROM adempiere."訂單產生出貨單"
      ) LOOP
           v_message   := '如果沒資料抓一筆來測試';
           p_Record_ID := r.Record_ID;
      END LOOP;
   END IF;--IF COALESCE(p_instance_id,1000000)


   p_客戶_id   :=0;
   p_訂單單號  :='';
   p_產品_id   :=0;
   p_客戶單號  :='';
   p_未結案    :='Y';
   v_message   := 'Before Read::訂單產生出貨單';


   FOR r IN (
          SELECT "客戶_id","訂單單號","產品_id","客戶單號","訂單產生出貨單_id","未結案"
            FROM adempiere."訂單產生出貨單"
           WHERE "訂單產生出貨單_id"=p_Record_ID
   )LOOP
           p_客戶_id   := r."客戶_id";
           p_訂單單號  := r."訂單單號";
           p_產品_id   := r."產品_id";
           p_客戶單號  := r."客戶單號";
           p_未結案    := r."未結案";
   END LOOP;

   v_message   := 'Before DELETE::訂單產生出貨單明細 p_Record_ID='||p_Record_ID;
   DELETE FROM adempiere."訂單產生出貨單明細" WHERE "訂單產生出貨單_id" = p_Record_ID;
   DELETE FROM adempiere."出貨單底稿"         WHERE "訂單產生出貨單_id" = p_Record_ID;
   DELETE FROM adempiere."製程完工底稿"       WHERE "訂單產生出貨單_id" = p_Record_ID;
  
   v_message   := 'Before Read ::訂單 p_Record_ID='||p_Record_ID;

   FOR r IN (
       SELECT t."客戶_id",t."訂單單號",t."產品_id",t."客戶單號", t."訂單_id",
              c."編號"||'_'||c."簡稱" AS "客戶名稱"
       FROM adempiere."訂單" t
 INNER JOIN adempiere."客戶" c ON t."客戶_id"=c."客戶_id"
      WHERE 1=1  --AND t."客戶_id"=1000002
        AND (COALESCE(p_客戶_id,  0  )=0   OR COALESCE(p_客戶_id,   0) = COALESCE(t."客戶_id" ,  0))
        AND (COALESCE(p_訂單單號, '' )=''  OR COALESCE(p_訂單單號, '') = COALESCE(t."訂單單號", ''))
        AND (COALESCE(p_產品_id,  0  )=0   OR COALESCE(p_產品_id,   0) = COALESCE(t."產品_id" ,  0))
        AND (COALESCE(p_客戶單號, '' )=''  OR COALESCE(p_客戶單號,'' ) = COALESCE(t."客戶單號",'' ))
        AND (COALESCE(p_未結案,   'N')='N' OR COALESCE(p_未結案,  'N') = COALESCE(t."未結案"  ,'N'))
  )LOOP
       --select ad_sequence_id from adempiere.ad_sequence where name = '訂單產生出貨單明細'
       SELECT adempiere.nextid(1000048::Integer, 'N'::Varchar) INTO v_Next_ID;
       SELECT adempiere.generate_uuid() INTO v_Next_UU;

       INSERT INTO adempiere."訂單產生出貨單明細"(
       "訂單產生出貨單_id", "訂單產生出貨單明細_id", "訂單產生出貨單明細_uu",
       "客戶_id",  "客戶名稱",  "訂單單號", "訂單_id", "產品_id", "客戶單號",
       ad_pinstance_id,
       ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby
       ) VALUES (
       p_Record_ID,   v_Next_ID,     v_Next_UU,
       r."客戶_id",   r."客戶名稱",  r."訂單單號",  r."訂單_id", r."產品_id",  r."客戶單號", 
       p_instance_id,
       p_AD_Client_ID, p_AD_Client_ID,'Y', NOW(), p_AD_User_ID, NOW(), p_AD_User_ID
       ); 
       --select ad_sequence_id from adempiere.ad_sequence where name = '出貨單底稿' --1000056
       SELECT adempiere.nextid(1000056::Integer, 'N'::Varchar) INTO v_NextLine_ID;
       SELECT adempiere.generate_uuid() INTO v_NextLine_UU;
       INSERT INTO adempiere."出貨單底稿"(
       "訂單產生出貨單_id", "訂單產生出貨單明細_id", "出貨單底稿_id", "出貨單底稿_uu",
       "訂單_id", "客戶_id", "產品_id", "應收帳款月份", "出貨單號","出貨日期","重量kg","單重g","數量pcs","桶型_id","桶號","批號","備註",
       "進貨退回_id", "桶數",
       ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby
       ) VALUES (
       p_Record_ID, v_Next_ID, v_NextLine_ID, v_NextLine_UU,
       r."訂單_id",
       r."客戶_id",
       r."產品_id",
       '',    --"應收帳款月份",
       '',    --"出貨單號",
       now(), --"出貨日期",
       0 , --"重量kg",
       0 , --"單重g",
       0 , --"數量pcs",
       0 , --"桶型_id",
       '', --"桶號",
       '', --"批號",
       '', --"備註",
       0 , --"進貨退回_id",
       0 , --"桶數",
       p_AD_Client_ID,p_AD_Client_ID,'Y', NOW(), p_AD_User_ID, NOW(), p_AD_User_ID
       );

   FOR s IN (
     SELECT a."訂單_id", a."產品_id", a."製程_id", a."單價",   a."備註",    a."訂單製程_id",
            a."客戶名稱",a."訂單單號",a."順序",    a."廠商_id",a."應付單價",a."應收單價",    a."派工數量",
            b."名稱" AS "製程名稱"
       FROM adempiere."訂單製程" a
 INNER JOIN adempiere."製程" b ON a."製程_id"=b."製程_id"
        WHERE a."訂單_id"=r."訂單_id"
        ORDER BY a."順序"
  )LOOP
       --select ad_sequence_id from adempiere.ad_sequence where name = '製程完工底稿'--1000058
       SELECT adempiere.nextid(1000058::Integer, 'N'::Varchar) INTO v_NextLine_ID;
       SELECT adempiere.generate_uuid() INTO v_NextLine_UU;
 ---訂單產生出貨單_ID=1000002
       INSERT INTO adempiere."製程完工底稿"(
       "訂單產生出貨單_id", "訂單產生出貨單明細_id", "製程完工底稿_id", "製程完工底稿_uu",
       "訂單_id",  "廠商_id", "產品_id", "製程_id", "製程名稱",    "應付帳款月份",
       "出貨單號", "出貨日期","重量kg",  "單重g",   "數量pcs",
       "桶型_id",  "桶號",    "批號",    "備註",    "進貨退回_id", "桶數",
       ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby
       ) VALUES (
       p_Record_ID, v_Next_ID, v_NextLine_ID, v_NextLine_UU,
       r."訂單_id",
       s."廠商_id",
       r."產品_id",
       s."製程_id" ,
       s."製程名稱",
       '',   --"應付帳款月份",
       '',   --"出貨單號",
       now(),--"出貨日期",
       0,    --"重量kg", 
       0,    --"單重g",  
       0,    --"數量pcs",
       0,    --"桶型_id", 
       '',   --"桶號",   
       '',   --"批號",   
       '',   --"備註",
       0,    --"進貨退回_id",
       0,    --"桶數",
       p_AD_Client_ID, p_AD_Client_ID, 'Y', NOW(), p_AD_User_ID, NOW(), p_AD_User_ID
       ); 
  END LOOP;

  END LOOP;

        v_message :='程式成功::p_Record_ID='||COALESCE(p_Record_ID,0)
        ||',p_客戶_id=' ||COALESCE(p_客戶_id,  0)
        ||',p_訂單單號='||COALESCE(p_訂單單號,'')
        ||',p_產品_id=' ||COALESCE(p_產品_id,  0)
        ||',p_客戶單號='||COALESCE(p_客戶單號,'')
        ||',p_未結案='  ||COALESCE(p_未結案,'N');
       
        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
        v_message :='程式失敗::=p_Record_ID='||p_Record_ID||',SQLERRM='||SQLERRM ||' ,SQLSTATE='||SQLSTATE||' ,v_message='||v_message;
        ResultStr := v_message;
        UPDATE adempiere.ad_pinstance
           SET updated         = NOW(),
               isprocessing    = 'N',
               result          = 0,
               errormsg        = ResultStr
         WHERE ad_pinstance_id = p_instance_id;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION adempiere.ly_so_gen_shipment_p(integer)
  OWNER TO postgres;