根據 iDempiere GPL 版權 adempiere.tg_rmtransaction

根據 iDempiere GPL 版權
改過的程式如果散播出去(買賣或無償)都需公告,
由顧問教導客製化寫作, 顧問並非散播程式僅教學示範,
由顧問教導後由客戶自行客製化的公司, 如屬自用無須公告客製化程式碼.

:::下面案例要求交付程式碼
因此顧問在此對大家公告程式碼內容
有些資料是給程式判斷, 然資料屬於公司機密
因此無法公告資料內容, 在此只能公告程式碼
這是目前台灣保稅系統成本與原料帳資料表架構



-- Table: adempiere.tg_rmtransaction

-- DROP TABLE adempiere.tg_rmtransaction;

CREATE TABLE adempiere.tg_rmtransaction
(
  tg_rmtransaction_id  numeric(10,0) NOT NULL,
  tg_rmtransaction_uu character varying(36) DEFAULT NULL::character varying,

  ad_client_id               numeric(10,0) NOT NULL,
  ad_org_id                  numeric(10,0) NOT NULL,
  isactive                      character(1) NOT NULL DEFAULT 'Y'::bpchar,
  created                      timestamp without time zone NOT NULL,
  createdby                  numeric(10,0) NOT NULL,
  updated                     timestamp without time zone NOT NULL,
  updatedby                 numeric(10,0) NOT NULL,
  m_transaction_id      numeric(10,0) DEFAULT NULL::numeric,
  m_product_id            numeric(10,0) NOT NULL,
  m_locator_id             numeric(10,0) NOT NULL,

  declarationno            character varying(30) DEFAULT NULL::character varying,
  declarationqty           numeric,
  documentnumber     character varying(30) DEFAULT NULL::character varying,
  domesticsaleqty       numeric,

  movementdate          timestamp without time zone,
  movementqty            numeric NOT NULL,
  releasedate               timestamp without time zone,
  otherqty                     numeric,
  internaluseqty            numeric,
  importqty                    numeric,
  bondingimportqty       numeric,
  productionqty             numeric DEFAULT (0)::numeric,

  sk_no character varying(40),　　--料號
  docdate date,
  sp_no character varying(40),　　 --存(出)倉單證號碼
  in_qty numeric(10,2),　　　　　--存倉數量
  out_qty numeric(10,2),　　　　　--出倉數量
  sk_nowqty numeric(10,2),　　　--帳面庫存數量 sk_nowqty
  out_date date,　　　　　　　　--驗放日期
  bonding_no character varying(20),--報單號碼
  exp_qty numeric(10,2),　　　　--出口數量 exp_qty
  loc_qty numeric(10,2),　　　　 --內銷數量 loc_qty

bonding_no	declarationno
exp_qty	declarationqty
in_qty/out_qty	movementqty	以欄位documentnumber單據編號做判斷, 編號開頭TGDO的 就放out_qty(負號拿掉) 編號開頭是數字的就放in_qty
docdate	movementdate
out_date	releasedate
loc_qty	domesticsaleqty
sp_no	documentnumber

  CONSTRAINT tg_rmtransaction_key PRIMARY KEY (tg_rmtransaction_id),
  CONSTRAINT adclient_tgrmtransaction FOREIGN KEY (ad_client_id)
      REFERENCES adempiere.ad_client (ad_client_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT adorg_tgrmtransaction FOREIGN KEY (ad_org_id)
      REFERENCES adempiere.ad_org (ad_org_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT createdby_tgrmtransaction FOREIGN KEY (createdby)
      REFERENCES adempiere.ad_user (ad_user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT mlocator_tgrmtransaction FOREIGN KEY (m_locator_id)
      REFERENCES adempiere.m_locator (m_locator_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT mproduct_tgrmtransaction FOREIGN KEY (m_product_id)
      REFERENCES adempiere.m_product (m_product_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT mtransaction_tgrmtransaction FOREIGN KEY (m_transaction_id)
      REFERENCES adempiere.m_transaction (m_transaction_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT updatedby_tgrmtransaction FOREIGN KEY (updatedby)
      REFERENCES adempiere.ad_user (ad_user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT tg_rmtransaction_uu_idx UNIQUE (tg_rmtransaction_uu),
  CONSTRAINT tg_rmtransaction_isactive_check CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]))
)
WITH (
  OIDS=FALSE
);
ALTER TABLE adempiere.tg_rmtransaction
  OWNER TO adempiere;

根據 iDempiere GPL 版權 adempiere.sk_fgjl

根據 iDempiere GPL 版權
改過的程式如果散播出去(買賣或無償)都需公告
由顧問教導客製化寫作, 顧問並非散播程式僅教學示範
由顧問教導後由客戶自行客製化的公司, 如屬自用無須公告客製化程式碼.

:::下面案例要求交付程式碼
因此顧問在此對大家公告程式碼內容
有些資料是給程式判斷, 然資料屬於公司機密
因此無法公告資料內容, 在此只能公告程式碼
這是目前台灣保稅系統成本與原料帳資料表架構

-- Table: adempiere.sk_fgjl

-- DROP TABLE adempiere.sk_fgjl;

CREATE TABLE adempiere.sk_fgjl
(
  sk_fgjl_id numeric(10,0) NOT NULL,
  ad_client_id numeric(10,0) NOT NULL,
  ad_org_id numeric(10,0) NOT NULL,
  isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,
  created timestamp without time zone NOT NULL DEFAULT now(),
  createdby numeric(10,0) NOT NULL,
  updated timestamp without time zone NOT NULL DEFAULT now(),
  updatedby numeric(10,0) NOT NULL,
  sk_no character varying(40),　　--料號
  docdate date,
  sp_no character varying(40),　　 --存(出)倉單證號碼
  in_qty numeric(10,2),　　　　　--存倉數量
  out_qty numeric(10,2),　　　　　--出倉數量
  sk_nowqty numeric(10,2),　　　--帳面庫存數量 sk_nowqty
  out_date date,　　　　　　　　--驗放日期
  bonding_no character varying(20),--報單號碼
  exp_qty numeric(10,2),　　　　--出口數量 exp_qty
  loc_qty numeric(10,2),　　　　 --內銷數量 loc_qty
  sk_fgjl_uu character varying(36) DEFAULT NULL::character varying,
  sk_fgjh_id numeric(10,0) NOT NULL,
  description character varying(255) DEFAULT NULL::character varying

)
WITH (
  OIDS=FALSE
);
ALTER TABLE adempiere.sk_fgjl
  OWNER TO adempiere;

根據 iDempiere GPL 版權 adempiere.sk_fgjh

根據 iDempiere GPL 版權
改過的程式如果散播出去(買賣或無償)都需公告
由顧問教導客製化寫作, 顧問並非散播程式僅教學示範
由顧問教導後由客戶自行客製化的公司, 如屬自用無須公告客製化程式碼.

:::下面案例要求交付程式碼
因此顧問在此對大家公告程式碼內容
有些資料是給程式判斷, 然資料屬於公司機密
因此無法公告資料內容, 在此只能公告程式碼
這是目前台灣保稅系統成本與原料帳資料表架構

-- Table: adempiere.sk_fgjh

-- DROP TABLE adempiere.sk_fgjh;

CREATE TABLE adempiere.sk_fgjh
(
  sk_fgjh_id numeric(10,0) NOT NULL,
  ad_client_id numeric(10,0) NOT NULL,
  ad_org_id numeric(10,0) NOT NULL,
  isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,
  created timestamp without time zone NOT NULL DEFAULT now(),
  createdby numeric(10,0) NOT NULL,
  updated timestamp without time zone NOT NULL DEFAULT now(),
  updatedby numeric(10,0) NOT NULL,
  sk_no character varying(40),
  docdate date,
  docdateto date,
  sk_fgjh_uu character varying(36) DEFAULT NULL::character varying,
  sk_name character varying(80),
  sk_nowqty numeric(14,2),
  linecount numeric(10,0),
  CONSTRAINT sk_fgjh_pk PRIMARY KEY (sk_fgjh_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE adempiere.sk_fgjh
  OWNER TO adempiere;

-- Index: adempiere.sk_fgjh_sk_no

-- DROP INDEX adempiere.sk_fgjh_sk_no;

CREATE INDEX sk_fgjh_sk_no
  ON adempiere.sk_fgjh
  USING btree
  (sk_no COLLATE pg_catalog."default");

-- Index: adempiere.sk_fgjh_uu_idx

-- DROP INDEX adempiere.sk_fgjh_uu_idx;

CREATE UNIQUE INDEX sk_fgjh_uu_idx
  ON adempiere.sk_fgjh
  USING btree
  (sk_fgjh_uu COLLATE pg_catalog."default");

根據 iDempiere GPL 版權 adempiere.tg_sk_fgjh_i1

根據 iDempiere GPL 版權
改過的程式如果散播出去(買賣或無償)都需公告
由顧問教導客製化寫作, 顧問並非散播程式僅教學示範
由顧問教導後由客戶自行客製化的公司, 如屬自用無須公告客製化程式碼.

:::下面案例要求交付程式碼
因此顧問在此對大家公告程式碼內容
有些資料是給程式判斷, 然資料屬於公司機密
因此無法公告資料內容, 在此只能公告程式碼
這是目前台灣保稅系統成本與原料帳資料表架構

-- Function: adempiere.tg_sk_fgjh_i1(numeric)

-- DROP FUNCTION adempiere.tg_sk_fgjh_i1(numeric);

CREATE OR REPLACE FUNCTION adempiere.tg_sk_fgjh_i1(p_instance numeric)
  RETURNS SETOF adempiere.tg_sk_fgjh_t1 AS
$BODY$ 
/* 原設計是單頭就是一個月份..現在改成直接抓單身..只要確認最後一次產生的單頭且區間要 >= 列印起訖
CURRENT_LANG =  en_US , zh_CN , zh_TW
set search_path=adempiere
select * from adempiere.tg_sk_fgjh_i(1000437);
-- "程式完成:: p_instance=1000000 ,p_Record_ID=0 ,p_DocDate=2013-01-01 - 2013-01-31 ,p_SK_NO=1D-NGF899 - 1D-NGF899"
select * from adempiere.ad_pinstance where ad_pinstance_id=1000000
select * from adempiere.ad_pinstance_para where ad_pinstance_id = 1000000
DROP table adempiere.tg_sk_fgjh_t1 CASCADE
SELECT * FROM adempiere.tg_sk_fgjh_i1(1000000)
*/
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;
   p_SK_NO                VARCHAR (20); --起迄
   p_SK_NOTo              VARCHAR (20); --起迄
   p_M_Product_Value      VARCHAR (20); --起迄
   p_M_ProductEnd_Value   VARCHAR (20);
   p_C_BPartner_Value     VARCHAR (20); --起迄
   p_C_BPartnerEnd_Value  VARCHAR (20);
   p_instance_id          NUMERIC(10)   := 0;
   v_sk_no                VARCHAR (20);
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;

   IF COALESCE(p_instance,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 * from adempiere.sk_fgjh
     
          SELECT x.vcount , x.sk_no
            FROM (
                  SELECT COUNT(*) AS vcount , h.sk_no
                    FROM adempiere.sk_fgjh h
              INNER JOIN adempiere.sk_fgjl l ON h.sk_fgjh_id=l.sk_fgjh_id
                   WHERE l.docdate BETWEEN p_DocDate AND p_DocDateTo
                GROUP BY h.sk_no
                  ) x
           ORDER BY vcount DESC LIMIT 1
      ) LOOP
      --   v_message := '如果沒資料抓一筆來測試';
      --   p_Record_ID := r.sk_fgjh_id;
           p_SK_NO   := r.sk_no;
           p_SK_NOTo := r.sk_no;
      END LOOP;
     
     -- p_DocDate   := '2013.01.01'::DATE;
     -- p_DocDateTo := date_trunc('month',p_DocDate)+'1month'::interval-'1day'::interval;
     -- p_SK_NO   := '2Z-F8050-B070-1';
     -- p_SK_NOTo := '2Z-F8050-B070-1';
    
   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
   -- alter table adempiere.tg_sk_fgjh_t1 add docdateto date
  
   DELETE FROM adempiere.tg_sk_fgjh_t1 WHERE ad_pinstance_id = p_instance;

   v_sk_no := '';
   -- 2)  INSERT INOT TABLE SELECT
   FOR r IN (
       SELECT
       h.sk_fgjh_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_fgjh_uu        ,--character varying(36) DEFAULT NULL::character varying,
       h.sk_name           ,--character varying(80),
    -- h.sk_nowqty          --numeric(14,2)
    coalesce((select l.sk_nowqty from adempiere.sk_fgjl l where l.docdate <=  p_DocDate  --'2013-01-01'
                  and l.sk_fgjh_id=h.sk_fgjh_id order by l.docdate desc limit 1),coalesce(h.sk_no,0)) as sk_nowqty
       FROM adempiere.sk_fgjh h
      WHERE 1=1 
        AND h.docdate   <= p_DocDate     --'2013-01-01' --
        AND h.docdateto >= p_DocDateTo   --'2013-01-31' --
        AND h.sk_no BETWEEN p_SK_NO AND p_SK_NOTo  -- p_SK_NO := '2Z-F8050-B070-1';
     -- AND h.sk_no='1D-NGF899'
      ORDER BY h.sk_no, h.sk_fgjh_id desc
      --p_DocDate=2013-01-01 - 2013-01-31 ,p_SK_NO=1D-NGF899 - 1D-NGF899"
  )LOOP
    --   一個料號只取一次
    IF v_sk_no <> r.sk_no THEN
       -- INSERT INTO
       INSERT INTO adempiere.tg_sk_fgjh_t1(
       ad_pinstance_id   ,--numeric(10,0) NOT NULL,
       sk_fgjh_id        ,--numeric(10,0) NOT NULL,
       ad_client_id      ,--numeric(10,0) NOT NULL,
       ad_org_id         ,--numeric(10,0) NOT NULL,
       isactive          ,--character(1) NOT NULL DEFAULT 'Y'::bpchar,
       created           ,--timestamp without time zone NOT NULL DEFAULT now(),
       createdby         ,--numeric(10,0) NOT NULL,
       updated           ,--timestamp without time zone NOT NULL DEFAULT now(),
       updatedby         ,--numeric(10,0) NOT NULL,
       sk_no             ,--character varying(40),
       docdate           ,--date,
       docdateto         ,--date,
       sk_fgjh_uu        ,--character varying(36) DEFAULT NULL::character varying,
       sk_name           ,--character varying(80),
       sk_nowqty          --numeric(14,2)
       ) VALUES (
       p_instance_id       ,--numeric(10,0) NOT NULL,
       r.sk_fgjh_id        ,--numeric(10,0) NOT NULL,
       r.ad_client_id      ,--numeric(10,0) NOT NULL,
       r.ad_org_id         ,--numeric(10,0) NOT NULL,
       r.isactive          ,--character(1) NOT NULL DEFAULT 'Y'::bpchar,
       r.created           ,--timestamp without time zone NOT NULL DEFAULT now(),
       r.createdby         ,--numeric(10,0) NOT NULL,
       r.updated           ,--timestamp without time zone NOT NULL DEFAULT now(),
       r.updatedby         ,--numeric(10,0) NOT NULL,
       r.sk_no             ,--character varying(40),
       p_DocDate           ,--date,
       p_DocDateTo         ,--date,
       r.sk_fgjh_uu        ,--character varying(36) DEFAULT NULL::character varying,
       r.sk_name           ,--character varying(80),
       r.sk_nowqty          --numeric(14,2)
       );
       v_sk_no:=r.sk_no;
    END IF;
  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_fgjh_t1
            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_fgjh_i1(numeric)
  OWNER TO adempiere;

根據 iDempiere GPL 版權 adempiere.sk_gen_alljl

根據 iDempiere GPL 版權
改過的程式如果散播出去(買賣或無償)都需公告
由顧問教導客製化寫作, 顧問並非散播程式僅教學示範
由顧問教導後由客戶自行客製化的公司, 如屬自用無須公告客製化程式碼.

:::下面案例要求交付程式碼
因此顧問在此對大家公告程式碼內容
有些資料是給程式判斷, 然資料屬於公司機密
因此無法公告資料內容, 在此只能公告程式碼
這是目前台灣保稅系統成本與原料帳資料表架構
-- Function: adempiere.sk_gen_alljl(numeric)

-- DROP FUNCTION adempiere.sk_gen_alljl(numeric);

CREATE OR REPLACE FUNCTION adempiere.sk_gen_alljl(pinstance numeric)
  RETURNS void AS
$BODY$
-- ALTER TABLE adempiere.SK_FGJH
-- ALTER COLUMN sk_name TYPE varchar(80)
-- "程式失敗::資料行參考 "sk_no" 模稜兩可 ,SQLSTATE=42702 ,v_message=讀取傳遞參數表"
-- set search_path=adempiere
-- SELECT * FROM adempiere.SK_RMJH
-- DELETE FROM adempiere.SK_FGJH
-- DELETE FROM adempiere.SK_FGJL
-- DELETE FROM adempiere.SK_RMJH
-- DELETE FROM adempiere.SK_RMJL
-- select * from adempiere.SK_FGJH where sk_no='TG-G016'   1052702    608.00
-- select * from adempiere.SK_FGJL where SK_FGJH_ID=1052702 order by SK_FGJL_id
-- select adempiere.sk_gen_alljl(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);
   sql_fg                 VARCHAR (4000);
   sql_rm                 VARCHAR (4000);
   sql_fgl                VARCHAR (4000);
   sql_rml                VARCHAR (4000);
   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);

   sql_fg :=    'INSERT INTO SK_FGJH(sk_fgjh_id, ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby,'
             || 'sk_no, sk_name, sk_nowqty, docdate, docdateto, sk_fgjh_uu)VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)';

   sql_rm :=    '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)';

   sql_fgl    := 'INSERT INTO SK_FGJL(sk_fgjh_id, sk_fgjl_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_fgjl_uu'   --10..20
             ||  ')VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20)';

   sql_rml    := '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)';
           
   --第一層抓出需要的料號
   FOR p IN (
       SELECT sk.sk_no, sk.sk_name, bp.ptype,   --bp.ptype="2" 成品  bp.ptype="5" 原料
     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 = '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 bp.ptype = '5' -- 原料  bp.ptype = "2" 成品
          ORDER BY bp.ptype, sk.sk_ikind,sk.sk_no
      ) LOOP
      v_message := '讀取傳遞參數表';
      P_NOWQTY  := p.NOWQTY;
--    記錄下有期初 或是 本期有異動的料號 當作單頭
--    SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_FGJH'; --1000195
--    SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_RMJH'; --1000203
    
      IF      p.ptype='2' THEN
                          sqlins:=sql_fg;
                          SELECT adempiere.nextid(1000195, 'N') INTO o_nextid; -- SK_FGJH
     
      ELSE
                          sqlins:=sql_rm;
                          SELECT adempiere.nextid(1000203, 'N') INTO o_nextid; -- SK_FGJH
      END IF;  --原料
     
      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();
-- 第二層  取出該料號有異動的日期
   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  銷售退回 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 order by z.docdate
   ) LOOP
      v_message := '有用到的日期';


-- 第三層明細分 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_FGJL'; --1000196
   -- SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_RMJL'; --1000204

      sqlins:=NULL;
      IF p.ptype='2' THEN
                          sqlins:=sql_fgl;  --成品
                          SELECT adempiere.nextid(1000196, 'N') INTO o_nextlineid; -- SK_FGJL
      ELSE
                          sqlins:=sql_rml;
                          SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_FGJL
      END IF;  --原料
    
      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_fgjl_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_seqfld
   ) LOOP
      v_message := '進貨入庫';
      P_NOWQTY  := P_NOWQTY + coalesce(r.in_qty,0);

      sqlins:=NULL;
      IF p.ptype='2' THEN
                          sqlins:=sql_fgl;
                          SELECT adempiere.nextid(1000196, 'N') INTO o_nextlineid; -- SK_FGJL
      ELSE                sqlins:=sql_rml;
                          SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_FGJL
      END IF;  --原料
    
      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_fgjl_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 b.sd_seqfld  -- a.sp_no,
   ) LOOP
      v_message := '銷貨退回';
      P_NOWQTY  := P_NOWQTY + coalesce(r.in_qty,0);

      sqlins:=NULL;
      IF p.ptype='2' THEN
                          sqlins:=sql_fgl;
                          SELECT adempiere.nextid(1000196, 'N') INTO o_nextlineid; -- SK_FGJL
      ELSE
                          sqlins:=sql_rml;
                          SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_FGJL
      END IF;  --原料
    
     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_fgjl_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   = '2' and a.sp_slip_fg = '4'  -- 成品出貨             銷貨退回
         and s.sk_ikind   = '2' --成品
    order by b.sd_seqfld  --a.sp_no,
   ) LOOP
      v_message := '銷貨退回';
      P_NOWQTY  := P_NOWQTY + coalesce(r.in_qty,0);

      sqlins:=NULL;
      IF p.ptype='2' THEN
                          sqlins:=sql_fgl;
                          SELECT adempiere.nextid(1000196, 'N') INTO o_nextlineid; -- SK_FGJL
      ELSE
                          sqlins:=sql_rml;
                          SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_FGJL
      END IF;  --原料
    
     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_fgjl_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   = '2' --成品
    order by b.sd_seqfld
   ) LOOP
      v_message := '銷貨';
      P_NOWQTY  := P_NOWQTY - coalesce(r.out_qty,0);
    --SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_FGJL'; --1000196
      sqlins:=NULL;
      IF p.ptype='2' THEN
                          sqlins:=sql_fgl;
                          SELECT adempiere.nextid(1000196, 'N') INTO o_nextlineid; -- SK_FGJL
      ELSE
                          sqlins:=sql_rml;
                          SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_FGJL
      END IF;  --原料
    
      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_fgjl_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_FGJL'; --1000196
      sqlins:=NULL;
      IF p.ptype='2' THEN
                          sqlins:=sql_fgl;
                          SELECT adempiere.nextid(1000196, 'N') INTO o_nextlineid; -- SK_FGJL
      ELSE
                          sqlins:=sql_rml;
                          SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_FGJL
      END IF;  --原料
    
      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_fgjl_uu
   END LOOP; --生產領用

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

--科學工業園區    XX科技股份有限公司  保稅成品帳
-- 101年        存(出)倉情形            帳面    成品出口情形（含視同出口及內銷）          
-- 月    日    存(出)倉    存倉    出倉    庫存數量    驗放日期    報單號碼    出口數量    內銷數量
--        單證號碼    數量    數量                  
--XX科技股份有限公司  保稅成品帳
--園區事業編號: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_alljl(numeric)
  OWNER TO adempiere;