iDempiere 客製化教學
出貨單項次必須在
原單據 項下驗退的
+
沒有處理過(退貨) 才出來
((@原單據_ID@=0 AND 出貨單項次.訂單_ID=@訂單_ID@) OR 出貨單項次.出貨單_ID=@原單據_ID@)
AND EXISTS (SELECT 1 FROM 品管狀態 b WHERE b.名稱='驗退' AND 出貨單項次.品管狀態_ID=b.品管狀態_ID)
AND NOT EXISTS (SELECT 1 FROM 出貨單項次 a WHERE a.原項次_ID=出貨單項次.出貨單項次_ID AND 出貨單項次.出貨單項次_ID<>@原項次_ID@ )
2014年10月30日 星期四
iDempiere 客製化教學 完工入庫單項次必須在 原單據 項下驗退的 + 沒有處理過(退貨) 才出來
iDempiere 客製化教學
完工入庫單項次必須在
原單據 項下驗退的
+
沒有處理過(退貨)
才出來
完工入庫單項次.完工入庫單_ID=@原單據_ID@
AND EXISTS(SELECT 1 FROM 品管狀態 b WHERE b.名稱='驗退' AND 完工入庫單項次.品管狀態_ID=b.品管狀態_ID)
AND NOT EXISTS (SELECT 1 FROM 完工入庫單項次 a WHERE a.原項次_ID=完工入庫單項次.完工入庫單項次_ID AND 完工入庫單項次.完工入庫單項次_ID<>@原項次_ID@ )
完工入庫單項次必須在
原單據 項下驗退的
+
沒有處理過(退貨)
才出來
完工入庫單項次.完工入庫單_ID=@原單據_ID@
AND EXISTS(SELECT 1 FROM 品管狀態 b WHERE b.名稱='驗退' AND 完工入庫單項次.品管狀態_ID=b.品管狀態_ID)
AND NOT EXISTS (SELECT 1 FROM 完工入庫單項次 a WHERE a.原項次_ID=完工入庫單項次.完工入庫單項次_ID AND 完工入庫單項次.完工入庫單項次_ID<>@原項次_ID@ )
iDempiere Hard code VS Soft Code
iDempiere Hard code VS Soft Code
這樣才不至於在 Upgrade 時要調整 [廠商類別_ID] Hard code(直接指定內碼)
廠商.廠商類別_ID 必須是 [一般廠商]
廠商.廠商類別_ID=(SELECT 廠商類別_ID FROM 廠商類別 WHERE 名稱='一般廠商')
這樣才不至於在 Upgrade 時要調整 [廠商類別_ID] Hard code(直接指定內碼)
廠商.廠商類別_ID 必須是 [一般廠商]
廠商.廠商類別_ID=(SELECT 廠商類別_ID FROM 廠商類別 WHERE 名稱='一般廠商')
2014年10月21日 星期二
委外入貨品檢單：自動抓出［待檢］中的［委外入貨單］
以上範例顧問於 30分鐘完成
技術轉移時間約 60分鐘完成
（已經受訓 3-6個周的非資訊人員,例如 會計人員,生管人員,物管人員,業務人員,採購人員）
我們轉移實作技術, 用實際需求範例給［客戶］參考, 客戶在顧問指導下完成自己的需求
依據 Open Source GPL 規範, ［客戶］自己完成後, 如未有償或無償轉移就不需公告
....
［委外入貨品檢單］自動抓出［待檢］中的［委外入貨單］
插入 [委外入貨品檢單單據]
插入 [委外入貨品檢單項次]
SELECT c.* FROM 委外入貨單 c
WHERE EXISTS(SELECT 1 FROM 委外入貨單項次 a
INNER JOIN 品管狀態 b ON a.品管狀態_ID=b.品管狀態_ID
WHERE b.名稱 ='待驗'
AND a.委外入貨單_ID=c.委外入貨單_ID)
AND COALESCE(c.已作廢,'N')='N'
ORDER BY c.入貨單號
--select ad_sequence_id from adempiere.ad_sequence where name = '委外入貨品檢單單據'--1000094
SELECT adempiere.nextid(1000094::Integer, 'N'::Varchar) INTO v_Head_ID; -- 1000094
SELECT adempiere.generate_uuid() INTO v_Head_UU;
INSERT INTO adempiere.委外入貨品檢單單據(
委外入貨品檢單_ID, 委外入貨品檢單單據_ID, 委外入貨品檢單單據_UU,
委外入貨單_ID, 入貨單號, 入貨日期, 訂單_ID, 訂單製程_ID, 製程_ID, 產品_ID, 廠商_ID,
AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy
) VALUES (
NEW.委外入貨品檢單_ID, v_Head_ID, v_Head_UU,
r.委外入貨單_ID, r.入貨單號, r.入貨日期, r.訂單_ID, r.訂單製程_ID, r.製程_ID, r.產品_ID, r.廠商_ID,
NEW.AD_Client_ID, NEW.AD_Org_ID, 'Y', NOW(), NEW.UpdatedBy, NOW(), NEW.UpdatedBy
);
SELECT * FROM 委外入貨單項次 a
WHERE a.委外入貨單_ID=r.委外入貨單_ID
--select ad_sequence_id from adempiere.ad_sequence where name = '委外入貨品檢單項次'--1000095
SELECT adempiere.nextid(1000095::Integer, 'N'::Varchar) INTO v_Next_ID; -- 1000095
SELECT adempiere.generate_uuid() INTO v_Next_UU;
INSERT INTO adempiere.委外入貨品檢單項次(
委外入貨品檢單_ID, 委外入貨品檢單單據_ID, 委外入貨品檢單項次_ID,委外入貨品檢單項次_UU,
委外入貨單_ID, 委外入貨單項次_ID,訂單_ID, 訂單製程_ID, 製程_ID, 產品_ID, 廠商_ID,
重量kg, 單重g, 數量pcs, 桶型_ID, 桶號, 批號, 品管狀態_ID,
--作廢原因_ID, 已作廢,
AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy
) VALUES (
NEW.委外入貨品檢單_ID, v_Head_ID, v_Next_ID, v_Next_UU,
p.委外入貨單_ID, p.委外入貨單項次_ID, p.訂單_ID, p.訂單製程_ID, r.製程_ID, r.產品_ID, r.廠商_ID,
p.重量kg, p.單重g, p.數量pcs, p.桶型_ID, p.桶號, p.批號, p.品管狀態_ID,
--p.作廢原因_ID, p.已作廢,
NEW.AD_Client_ID, NEW.AD_Org_ID, 'Y', NOW(), NEW.UpdatedBy, NOW(), NEW.UpdatedBy);
2014年10月18日 星期六
企業規則引擎設定 Dynamic Validation Rule 動態驗證規則實例
企業規則引擎設定 Dynamic Validation Rule 動態驗證規則實例
Before Save Validation 存檔之前驗證 : 該有敲的資料是否建立
相關性強制條件
例如 : 如果是退貨項目必須宣告退貨原因, 否則出警告不准存檔!!
自主性強制條件
例如 : 委外加工單必須宣告來自哪一張客戶的訂單
例如 : 委外加工單項次必須建立加工的 (數量/桶數) Dice/ Wafer(晶粒/晶圓)數量
Before Save Validation 存檔之前驗證 : 該有敲的資料是否建立
相關性強制條件
例如 : 如果是退貨項目必須宣告退貨原因, 否則出警告不准存檔!!
自主性強制條件
例如 : 委外加工單必須宣告來自哪一張客戶的訂單
例如 : 委外加工單項次必須建立加工的 (數量/桶數) Dice/ Wafer(晶粒/晶圓)數量
委外入貨單項次在原單據下
委外入貨單項次在原單據下
委外入貨單項次在原單據下未退貨過的 但是不可包括本筆
委外入貨單項次.委外入貨單_ID=@原單據_ID@
AND
EXISTS(SELECT 1 FROM 品管狀態 b WHERE b.名稱='驗退' AND 委外入貨單項次.品管狀態_ID=b.品管狀態_ID) AND NOT EXISTS (SELECT 1 FROM 委外入貨單項次 a WHERE a.原項次_ID=委外入貨單項次.委外入貨單項次_ID AND 委外入貨單項次.委外入貨單項次_ID<>@原項次_ID@ )
委外入貨單項次.訂單_ID=@訂單_ID@ AND (@原單據_ID@=0 OR 委外入貨單項次.委外入貨單_ID=@原單據_ID@)
AND EXISTS(SELECT 1 FROM 品管狀態 b WHERE b.名稱='驗退' AND 委外入貨單項次.品管狀態_ID=b.品管狀態_ID) AND NOT EXISTS (SELECT 1 FROM 委外入貨單項次 a WHERE a.原項次_ID=委外入貨單項次.委外入貨單項次_ID AND 委外入貨單項次.委外入貨單項次_ID<>@原項次_ID@ )
委外入貨單項次在原單據下未退貨過的 但是不可包括本筆
委外入貨單項次.委外入貨單_ID=@原單據_ID@
AND
EXISTS(SELECT 1 FROM 品管狀態 b WHERE b.名稱='驗退' AND 委外入貨單項次.品管狀態_ID=b.品管狀態_ID) AND NOT EXISTS (SELECT 1 FROM 委外入貨單項次 a WHERE a.原項次_ID=委外入貨單項次.委外入貨單項次_ID AND 委外入貨單項次.委外入貨單項次_ID<>@原項次_ID@ )
委外入貨單項次.訂單_ID=@訂單_ID@ AND (@原單據_ID@=0 OR 委外入貨單項次.委外入貨單_ID=@原單據_ID@)
AND EXISTS(SELECT 1 FROM 品管狀態 b WHERE b.名稱='驗退' AND 委外入貨單項次.品管狀態_ID=b.品管狀態_ID) AND NOT EXISTS (SELECT 1 FROM 委外入貨單項次 a WHERE a.原項次_ID=委外入貨單項次.委外入貨單項次_ID AND 委外入貨單項次.委外入貨單項次_ID<>@原項次_ID@ )
2014年10月13日 星期一
iDempiere Callout ERP 規則引擎實作 groovy:來料加工單項次取回重量
import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
String sql = "SELECT 重量kg, 單重g, 桶型_id, 桶號, 批號, 備註 FROM 來料加工單項次 WHERE 來料加工單項次_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
BigDecimal ug1 = rs.getBigDecimal("單重g");
BigDecimal kg1 = rs.getBigDecimal("重量kg");
int tu1 = rs.getInt("桶型_id");
String tn1 = rs.getString ("桶號");
String lo1 = rs.getString ("批號");
String re1 = rs.getString ("備註");
kg1= kg1.negate();
A_Tab.setValue("單重g", ug1 );
A_Tab.setValue("重量kg", kg1 );
A_Tab.setValue("桶型_id", new Integer(tu1) );
A_Tab.setValue("桶號", tn1 );
A_Tab.setValue("批號", lo1 );
A_Tab.setValue("備註", re1 );
}
}
result=""
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
String sql = "SELECT 重量kg, 單重g, 桶型_id, 桶號, 批號, 備註 FROM 來料加工單項次 WHERE 來料加工單項次_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
BigDecimal ug1 = rs.getBigDecimal("單重g");
BigDecimal kg1 = rs.getBigDecimal("重量kg");
int tu1 = rs.getInt("桶型_id");
String tn1 = rs.getString ("桶號");
String lo1 = rs.getString ("批號");
String re1 = rs.getString ("備註");
kg1= kg1.negate();
A_Tab.setValue("單重g", ug1 );
A_Tab.setValue("重量kg", kg1 );
A_Tab.setValue("桶型_id", new Integer(tu1) );
A_Tab.setValue("桶號", tn1 );
A_Tab.setValue("批號", lo1 );
A_Tab.setValue("備註", re1 );
}
}
result=""
iDempiere Callout ERP 規則引擎實作 groovy:重量kg單重g=數量pcs
import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
import java.math.RoundingMode
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
BigDecimal Tw = new BigDecimal (0);
BigDecimal Uw = new BigDecimal (0);
BigDecimal Qty = new BigDecimal (0);
String fname = A_Field.getColumnName();
if (fname.equals("重量kg")) {
if (A_Value == null) Tw = null;
else Tw = (BigDecimal) A_Value;
if (A_Tab.getValue("單重g") == null) Uw = null;
else Uw = (BigDecimal)A_Tab.getValue("單重g");
}else if (fname.equals("單重g")) {
if (A_Value == null) Uw = null;
else Uw = (BigDecimal) A_Value;
if (A_Tab.getValue("重量kg") == null) Tw = null;
else Tw = (BigDecimal)A_Tab.getValue("重量Kg");
}
BigDecimal Qty1 = new BigDecimal (1);
if (Tw==null) Tw = new BigDecimal (0);
if (Uw==null) Uw = new BigDecimal (0);
if (Uw.equals(new BigDecimal (0)))
Qty = new BigDecimal (0);
else Qty = Tw.multiply(1000).divide(Uw,0,RoundingMode.DOWN ); //小數點全捨 FLOOR DOWN CEILING
if (Qty.compareTo(new BigDecimal (0)) < 0) Qty1= Qty1.negate();
A_Tab.setValue("桶數", Qty1);
A_Tab.setValue("數量pcs", Qty );
}
result=""
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
import java.math.RoundingMode
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
BigDecimal Tw = new BigDecimal (0);
BigDecimal Uw = new BigDecimal (0);
BigDecimal Qty = new BigDecimal (0);
String fname = A_Field.getColumnName();
if (fname.equals("重量kg")) {
if (A_Value == null) Tw = null;
else Tw = (BigDecimal) A_Value;
if (A_Tab.getValue("單重g") == null) Uw = null;
else Uw = (BigDecimal)A_Tab.getValue("單重g");
}else if (fname.equals("單重g")) {
if (A_Value == null) Uw = null;
else Uw = (BigDecimal) A_Value;
if (A_Tab.getValue("重量kg") == null) Tw = null;
else Tw = (BigDecimal)A_Tab.getValue("重量Kg");
}
BigDecimal Qty1 = new BigDecimal (1);
if (Tw==null) Tw = new BigDecimal (0);
if (Uw==null) Uw = new BigDecimal (0);
if (Uw.equals(new BigDecimal (0)))
Qty = new BigDecimal (0);
else Qty = Tw.multiply(1000).divide(Uw,0,RoundingMode.DOWN ); //小數點全捨 FLOOR DOWN CEILING
if (Qty.compareTo(new BigDecimal (0)) < 0) Qty1= Qty1.negate();
A_Tab.setValue("桶數", Qty1);
A_Tab.setValue("數量pcs", Qty );
}
result=""
iDempiere Callout ERP 規則引擎實作 groovy:採購單ID帶出所有採購單資料
import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
import java.sql.Timestamp
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
// Integer ID= (Integer)A_Tab.getValue("SK_BOM_ID");
String sql = "SELECT * FROM 採購單 WHERE 採購單_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String shno1 = rs.getString("採購單號"); A_Tab.setValue("採購單號", shno1);
String name1 = rs.getString("廠商名稱"); A_Tab.setValue("廠商名稱", name1);
Integer orid1 = new Integer(rs.getInt("訂單_id")); A_Tab.setValue("訂單_id", orid1);
String stat1 = rs.getString("單據狀態"); A_Tab.setValue("單據狀態", stat1);
Boolean uncl1 = (Boolean)("Y".equals( rs.getString("未結案"))); A_Tab.setValue("未結案", uncl1);
Boolean clos1 = (Boolean)("Y".equals( rs.getString("已結案"))); A_Tab.setValue("已結案", clos1);
Integer cuid1 = new Integer( rs.getInt("廠商_id")); A_Tab.setValue("廠商_id", cuid1);
Integer pdid1 = new Integer( rs.getInt("採購品_id")); A_Tab.setValue("採購品_id", pdid1);
Timestamp ordt1 = rs.getTimestamp("採購日期"); A_Tab.setValue("採購日期", ordt1);
//說明
}
}
result=""
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
import java.sql.Timestamp
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
// Integer ID= (Integer)A_Tab.getValue("SK_BOM_ID");
String sql = "SELECT * FROM 採購單 WHERE 採購單_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String shno1 = rs.getString("採購單號"); A_Tab.setValue("採購單號", shno1);
String name1 = rs.getString("廠商名稱"); A_Tab.setValue("廠商名稱", name1);
Integer orid1 = new Integer(rs.getInt("訂單_id")); A_Tab.setValue("訂單_id", orid1);
String stat1 = rs.getString("單據狀態"); A_Tab.setValue("單據狀態", stat1);
Boolean uncl1 = (Boolean)("Y".equals( rs.getString("未結案"))); A_Tab.setValue("未結案", uncl1);
Boolean clos1 = (Boolean)("Y".equals( rs.getString("已結案"))); A_Tab.setValue("已結案", clos1);
Integer cuid1 = new Integer( rs.getInt("廠商_id")); A_Tab.setValue("廠商_id", cuid1);
Integer pdid1 = new Integer( rs.getInt("採購品_id")); A_Tab.setValue("採購品_id", pdid1);
Timestamp ordt1 = rs.getTimestamp("採購日期"); A_Tab.setValue("採購日期", ordt1);
//說明
}
}
result=""
iDempiere Callout ERP 規則引擎實作 groovy:廠商ID帶出名稱
import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
// Integer ID= (Integer)A_Tab.getValue("SK_BOM_ID");
String sql = "SELECT 名稱 FROM 廠商 WHERE 廠商_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String name1 = rs.getString("名稱")
A_Tab.setValue("廠商名稱", name1);
}
}
result=""
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
// Integer ID= (Integer)A_Tab.getValue("SK_BOM_ID");
String sql = "SELECT 名稱 FROM 廠商 WHERE 廠商_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String name1 = rs.getString("名稱")
A_Tab.setValue("廠商名稱", name1);
}
}
result=""
iDempiere Callout ERP 規則引擎實作 groovy:訂單ID帶出所有訂單資料
import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
import java.sql.Timestamp
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
// Integer ID= (Integer)A_Tab.getValue("SK_BOM_ID");
String sql = "SELECT * FROM 訂單 WHERE 訂單_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String shno1 = rs.getString("訂單單號"); A_Tab.setValue("訂單單號", shno1);
String name1 = rs.getString("客戶名稱"); A_Tab.setValue("客戶名稱", name1);
String cusn1 = rs.getString("客戶單號"); A_Tab.setValue("客戶單號", cusn1);
String stat1 = rs.getString("單據狀態"); A_Tab.setValue("單據狀態", stat1);
Boolean uncl1 = (Boolean)("Y".equals( rs.getString("未結案"))); A_Tab.setValue("未結案", uncl1);
Boolean clos1 = (Boolean)("Y".equals( rs.getString("已結案"))); A_Tab.setValue("已結案", clos1);
Integer cuid1 = new Integer( rs.getInt("客戶_id")); A_Tab.setValue("客戶_id", cuid1);
Integer pdid1 = new Integer( rs.getInt("產品_id")); A_Tab.setValue("產品_id", pdid1);
Timestamp ordt1 = rs.getTimestamp("訂單日期"); A_Tab.setValue("訂單日期", ordt1);
//說明
}
}
result=""
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
import java.sql.Timestamp
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
// Integer ID= (Integer)A_Tab.getValue("SK_BOM_ID");
String sql = "SELECT * FROM 訂單 WHERE 訂單_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String shno1 = rs.getString("訂單單號"); A_Tab.setValue("訂單單號", shno1);
String name1 = rs.getString("客戶名稱"); A_Tab.setValue("客戶名稱", name1);
String cusn1 = rs.getString("客戶單號"); A_Tab.setValue("客戶單號", cusn1);
String stat1 = rs.getString("單據狀態"); A_Tab.setValue("單據狀態", stat1);
Boolean uncl1 = (Boolean)("Y".equals( rs.getString("未結案"))); A_Tab.setValue("未結案", uncl1);
Boolean clos1 = (Boolean)("Y".equals( rs.getString("已結案"))); A_Tab.setValue("已結案", clos1);
Integer cuid1 = new Integer( rs.getInt("客戶_id")); A_Tab.setValue("客戶_id", cuid1);
Integer pdid1 = new Integer( rs.getInt("產品_id")); A_Tab.setValue("產品_id", pdid1);
Timestamp ordt1 = rs.getTimestamp("訂單日期"); A_Tab.setValue("訂單日期", ordt1);
//說明
}
}
result=""
iDempiere Callout ERP 規則引擎實作 groovy:已結案時將未結案清空不打勾
import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
String status= (String)A_Value;
A_Tab.setValue("備註", status);
if (status.equals("true")){
A_Tab.setValue("未結案", "N");
int id = DB.getSQLValue (null, "SELECT 單據狀態_ID FROM 單據狀態 WHERE 名稱='已結案'");
A_Tab.setValue("備註", "單據狀態_ID: "+id );
A_Tab.setValue("單據狀態_ID", new Integer(id) );
// A_Tab.setValue("Processed", "Y");
}else{
A_Tab.setValue("未結案", "Y");
int id = DB.getSQLValue (null, "SELECT 單據狀態_ID FROM 單據狀態 WHERE 名稱='未結案'");
A_Tab.setValue("單據狀態_ID", new Integer(id) );
}
}
result=""
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
String status= (String)A_Value;
A_Tab.setValue("備註", status);
if (status.equals("true")){
A_Tab.setValue("未結案", "N");
int id = DB.getSQLValue (null, "SELECT 單據狀態_ID FROM 單據狀態 WHERE 名稱='已結案'");
A_Tab.setValue("備註", "單據狀態_ID: "+id );
A_Tab.setValue("單據狀態_ID", new Integer(id) );
// A_Tab.setValue("Processed", "Y");
}else{
A_Tab.setValue("未結案", "Y");
int id = DB.getSQLValue (null, "SELECT 單據狀態_ID FROM 單據狀態 WHERE 名稱='未結案'");
A_Tab.setValue("單據狀態_ID", new Integer(id) );
}
}
result=""
iDempiere Callout ERP 規則引擎實作 groovy:讀取採購單稅別表取得稅率
import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.math.RoundingMode
import java.sql.PreparedStatement
import java.sql.ResultSet
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
BigDecimal Rate = (BigDecimal)A_Tab.getValue("稅率");
if (Rate != null) Rate = Rate.divide(new BigDecimal(100));
BigDecimal Qty= (BigDecimal)A_Tab.getValue("採購數量");
BigDecimal Price= (BigDecimal)A_Tab.getValue("單價");
BigDecimal Amt= (BigDecimal)A_Tab.getValue("採購金額");
BigDecimal Amt1= (BigDecimal)A_Tab.getValue("含稅金額");
BigDecimal Tax= (BigDecimal)A_Tab.getValue("稅額");
BigDecimal Prepay= (BigDecimal)A_Tab.getValue("採購訂金");
BigDecimal Total= (BigDecimal)A_Tab.getValue("合計");
String sql = "SELECT 稅率, 編號 FROM 稅別 WHERE 稅別_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String type = rs.getString("編號"); // 02 -> 應稅 (內含)
Rate = rs.getBigDecimal("稅率");
A_Tab.setValue("稅率", Rate);
if (Rate != null) Rate = Rate.divide(new BigDecimal(100));
if (type.equals("02"))
{
Amt1= Qty.multiply(Price);
A_Tab.setValue("含稅金額", Amt1);
Amt = Amt1.divide(Rate.add(new BigDecimal(1) ) , 0, RoundingMode.CEILING );
A_Tab.setValue("採購金額", Amt );
Tax = Amt1.subtract(Amt);
A_Tab.setValue("稅額", Tax);
Total = Amt1.subtract(Prepay);
A_Tab.setValue("合計", Total);
} else {
Amt= Qty.multiply(Price).divide(new BigDecimal(1) , 0, RoundingMode.CEILING );
A_Tab.setValue("採購金額", Amt);
Tax = Amt.multiply(Rate);
A_Tab.setValue("稅額", Tax);
Total = Amt.add(Tax).subtract(Prepay);
A_Tab.setValue("合計", Total);
}
}
}
result=""
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.math.RoundingMode
import java.sql.PreparedStatement
import java.sql.ResultSet
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
BigDecimal Rate = (BigDecimal)A_Tab.getValue("稅率");
if (Rate != null) Rate = Rate.divide(new BigDecimal(100));
BigDecimal Qty= (BigDecimal)A_Tab.getValue("採購數量");
BigDecimal Price= (BigDecimal)A_Tab.getValue("單價");
BigDecimal Amt= (BigDecimal)A_Tab.getValue("採購金額");
BigDecimal Amt1= (BigDecimal)A_Tab.getValue("含稅金額");
BigDecimal Tax= (BigDecimal)A_Tab.getValue("稅額");
BigDecimal Prepay= (BigDecimal)A_Tab.getValue("採購訂金");
BigDecimal Total= (BigDecimal)A_Tab.getValue("合計");
String sql = "SELECT 稅率, 編號 FROM 稅別 WHERE 稅別_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String type = rs.getString("編號"); // 02 -> 應稅 (內含)
Rate = rs.getBigDecimal("稅率");
A_Tab.setValue("稅率", Rate);
if (Rate != null) Rate = Rate.divide(new BigDecimal(100));
if (type.equals("02"))
{
Amt1= Qty.multiply(Price);
A_Tab.setValue("含稅金額", Amt1);
Amt = Amt1.divide(Rate.add(new BigDecimal(1) ) , 0, RoundingMode.CEILING );
A_Tab.setValue("採購金額", Amt );
Tax = Amt1.subtract(Amt);
A_Tab.setValue("稅額", Tax);
Total = Amt1.subtract(Prepay);
A_Tab.setValue("合計", Total);
} else {
Amt= Qty.multiply(Price).divide(new BigDecimal(1) , 0, RoundingMode.CEILING );
A_Tab.setValue("採購金額", Amt);
Tax = Amt.multiply(Rate);
A_Tab.setValue("稅額", Tax);
Total = Amt.add(Tax).subtract(Prepay);
A_Tab.setValue("合計", Total);
}
}
}
result=""
iDempiere Callout ERP 規則引擎實作 groovy:採購品ID帶出品號品名規格
import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
// Integer ID= (Integer)A_Tab.getValue("SK_BOM_ID");
String sql = "SELECT 採購品號, 採購品名, 採購規格 FROM 採購品 WHERE 採購品_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String code1= rs.getString("採購品號");
String name1= rs.getString("採購品名");
String spec1= rs.getString("採購規格");
if (code1== null) code1="";
if (name1== null) name1="";
if (spec1== null) spec1="";
A_Tab.setValue("採購品號", code1 );
A_Tab.setValue("採購品名", name1 );
A_Tab.setValue("採購規格", spec1 );
}
Integer VID=(Integer)A_Tab.getValue("廠商_ID");
if (VID != null && ID != null)
{
sql = "SELECT 單價 FROM 採購單 WHERE 廠商_ID=? AND 採購品_ID=? ORDER BY 採購日期 DESC ";
pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, VID.intValue());
pstmt.setInt(2, ID.intValue());
rs = pstmt.executeQuery();
if(rs.next()) {
BigDecimal price = rs.getBigDecimal("單價");
if (price == null) price = new BigDecimal(0);
A_Tab.setValue("單價", price);
}
}
}
result=""
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
// Integer ID= (Integer)A_Tab.getValue("SK_BOM_ID");
String sql = "SELECT 採購品號, 採購品名, 採購規格 FROM 採購品 WHERE 採購品_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String code1= rs.getString("採購品號");
String name1= rs.getString("採購品名");
String spec1= rs.getString("採購規格");
if (code1== null) code1="";
if (name1== null) name1="";
if (spec1== null) spec1="";
A_Tab.setValue("採購品號", code1 );
A_Tab.setValue("採購品名", name1 );
A_Tab.setValue("採購規格", spec1 );
}
Integer VID=(Integer)A_Tab.getValue("廠商_ID");
if (VID != null && ID != null)
{
sql = "SELECT 單價 FROM 採購單 WHERE 廠商_ID=? AND 採購品_ID=? ORDER BY 採購日期 DESC ";
pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, VID.intValue());
pstmt.setInt(2, ID.intValue());
rs = pstmt.executeQuery();
if(rs.next()) {
BigDecimal price = rs.getBigDecimal("單價");
if (price == null) price = new BigDecimal(0);
A_Tab.setValue("單價", price);
}
}
}
result=""
iDempiere Callout ERP 規則引擎實作 groovy:計算採購單金額
import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
BigDecimal Qty = new BigDecimal (0);
BigDecimal Price = new BigDecimal (0);
BigDecimal Prepay = new BigDecimal (0);
BigDecimal Amt = new BigDecimal (0);
BigDecimal Amt1 = new BigDecimal (0);
String fname = A_Field.getColumnName();
// Integer ID= (Integer)A_Value;
BigDecimal Rate = (BigDecimal)A_Tab.getValue("稅率");
if (Rate != null) Rate = Rate.divide(new BigDecimal(100));
if (fname.equals("採購數量")) {
Qty = (BigDecimal) A_Value;
Price= (BigDecimal)A_Tab.getValue("單價");
Prepay= (BigDecimal)A_Tab.getValue("採購訂金");
}else if (fname.equals("單價")) {
Price = (BigDecimal) A_Value;
Qty = (BigDecimal)A_Tab.getValue("採購數量");
Prepay= (BigDecimal)A_Tab.getValue("採購訂金");
}else if (fname.equals("採購訂金")) {
Prepay = (BigDecimal) A_Value;
Qty = (BigDecimal)A_Tab.getValue("採購數量");
Price = (BigDecimal)A_Tab.getValue("單價");
}
Integer Tax_ID = (Integer)A_Tab.getValue("稅別_ID");
String type = DB.getSQLValueString(null,"SELECT 編號 FROM 稅別 WHERE 稅別_ID=?", Tax_ID );
if (type.equals("02")) {
Amt1= Qty.multiply(Price);
Amt = Amt1.divide(Rate.add(new BigDecimal(1) ) , 0, RoundingMode.CEILING );
Tax = Amt1.subtract(Amt);
Total = Amt.add(Tax).subtract(Prepay);
}else{
Amt = Qty.multiply(Price);
Tax = Amt.multiply(Rate);
Total = Amt.add(Tax).subtract(Prepay);
}
Amt = Qty.multiply(Price);
Tax = Amt.multiply(Rate);
Total = Amt.add(Tax).subtract(Prepay);
A_Tab.setValue("採購金額", Amt );
A_Tab.setValue("稅額", Tax);
A_Tab.setValue("合計", Total);
}
result=""
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
BigDecimal Qty = new BigDecimal (0);
BigDecimal Price = new BigDecimal (0);
BigDecimal Prepay = new BigDecimal (0);
BigDecimal Amt = new BigDecimal (0);
BigDecimal Amt1 = new BigDecimal (0);
String fname = A_Field.getColumnName();
// Integer ID= (Integer)A_Value;
BigDecimal Rate = (BigDecimal)A_Tab.getValue("稅率");
if (Rate != null) Rate = Rate.divide(new BigDecimal(100));
if (fname.equals("採購數量")) {
Qty = (BigDecimal) A_Value;
Price= (BigDecimal)A_Tab.getValue("單價");
Prepay= (BigDecimal)A_Tab.getValue("採購訂金");
}else if (fname.equals("單價")) {
Price = (BigDecimal) A_Value;
Qty = (BigDecimal)A_Tab.getValue("採購數量");
Prepay= (BigDecimal)A_Tab.getValue("採購訂金");
}else if (fname.equals("採購訂金")) {
Prepay = (BigDecimal) A_Value;
Qty = (BigDecimal)A_Tab.getValue("採購數量");
Price = (BigDecimal)A_Tab.getValue("單價");
}
Integer Tax_ID = (Integer)A_Tab.getValue("稅別_ID");
String type = DB.getSQLValueString(null,"SELECT 編號 FROM 稅別 WHERE 稅別_ID=?", Tax_ID );
if (type.equals("02")) {
Amt1= Qty.multiply(Price);
Amt = Amt1.divide(Rate.add(new BigDecimal(1) ) , 0, RoundingMode.CEILING );
Tax = Amt1.subtract(Amt);
Total = Amt.add(Tax).subtract(Prepay);
}else{
Amt = Qty.multiply(Price);
Tax = Amt.multiply(Rate);
Total = Amt.add(Tax).subtract(Prepay);
}
Amt = Qty.multiply(Price);
Tax = Amt.multiply(Rate);
Total = Amt.add(Tax).subtract(Prepay);
A_Tab.setValue("採購金額", Amt );
A_Tab.setValue("稅額", Tax);
A_Tab.setValue("合計", Total);
}
result=""
iDempiere Callout ERP 規則引擎實作 groovy:讀稅別表取得稅率
import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.math.RoundingMode
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
BigDecimal Rate = (BigDecimal)A_Tab.getValue("稅率");
if (Rate != null) Rate = Rate.divide(new BigDecimal(100));
BigDecimal Qty= (BigDecimal)A_Tab.getValue("訂單數量");
BigDecimal Price= (BigDecimal)A_Tab.getValue("單價");
BigDecimal Amt= (BigDecimal)A_Tab.getValue("訂單金額");
BigDecimal Amt1= new BigDecimal(0);
BigDecimal Tax= (BigDecimal)A_Tab.getValue("稅額");
BigDecimal Prepay= (BigDecimal)A_Tab.getValue("銷貨訂金");
BigDecimal Total= (BigDecimal)A_Tab.getValue("合計");
// Integer Tax_ID = (Integer)A_Tab.getValue("稅別_ID");
String sql = "SELECT 稅率, 編號 FROM 稅別 WHERE 稅別_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String type = rs.getString("編號"); // 02 -> 應稅 (內含)
Rate = rs.getBigDecimal("稅率");
A_Tab.setValue("稅率", Rate);
if (Rate != null) Rate = Rate.divide(new BigDecimal(100));
if (type.equals("02"))
{
Amt1= Qty.multiply(Price);//含稅金額
Amt = Amt1.divide(Rate.add(new BigDecimal(1) ) , 0, RoundingMode.CEILING );
Tax = Amt1.subtract(Amt);
Total = Amt1.subtract(Prepay);
} else {
Amt= Qty.multiply(Price).divide(new BigDecimal(1) , 0, RoundingMode.CEILING );
Tax = Amt.multiply(Rate);
Total = Amt.add(Tax).subtract(Prepay);
}
A_Tab.setValue("訂單金額", Amt);
A_Tab.setValue("稅額", Tax);
A_Tab.setValue("合計", Total);
}
}
result=""
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.math.RoundingMode
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
BigDecimal Rate = (BigDecimal)A_Tab.getValue("稅率");
if (Rate != null) Rate = Rate.divide(new BigDecimal(100));
BigDecimal Qty= (BigDecimal)A_Tab.getValue("訂單數量");
BigDecimal Price= (BigDecimal)A_Tab.getValue("單價");
BigDecimal Amt= (BigDecimal)A_Tab.getValue("訂單金額");
BigDecimal Amt1= new BigDecimal(0);
BigDecimal Tax= (BigDecimal)A_Tab.getValue("稅額");
BigDecimal Prepay= (BigDecimal)A_Tab.getValue("銷貨訂金");
BigDecimal Total= (BigDecimal)A_Tab.getValue("合計");
// Integer Tax_ID = (Integer)A_Tab.getValue("稅別_ID");
String sql = "SELECT 稅率, 編號 FROM 稅別 WHERE 稅別_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String type = rs.getString("編號"); // 02 -> 應稅 (內含)
Rate = rs.getBigDecimal("稅率");
A_Tab.setValue("稅率", Rate);
if (Rate != null) Rate = Rate.divide(new BigDecimal(100));
if (type.equals("02"))
{
Amt1= Qty.multiply(Price);//含稅金額
Amt = Amt1.divide(Rate.add(new BigDecimal(1) ) , 0, RoundingMode.CEILING );
Tax = Amt1.subtract(Amt);
Total = Amt1.subtract(Prepay);
} else {
Amt= Qty.multiply(Price).divide(new BigDecimal(1) , 0, RoundingMode.CEILING );
Tax = Amt.multiply(Rate);
Total = Amt.add(Tax).subtract(Prepay);
}
A_Tab.setValue("訂單金額", Amt);
A_Tab.setValue("稅額", Tax);
A_Tab.setValue("合計", Total);
}
}
result=""
iDempiere Callout ERP 規則引擎實作 groovy:計算訂單金額
import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
BigDecimal Qty = new BigDecimal (0);
BigDecimal Price = new BigDecimal (0);
BigDecimal Prepay = new BigDecimal (0);
BigDecimal Amt = new BigDecimal (0);
BigDecimal Amt1 = new BigDecimal (0);
String fname = A_Field.getColumnName();
// Integer ID= (Integer)A_Value;
BigDecimal Rate = (BigDecimal)A_Tab.getValue("稅率");
if (Rate != null) Rate = Rate.divide(new BigDecimal(100));
if (fname.equals("訂單數量")) {
Qty = (BigDecimal) A_Value;
Price= (BigDecimal)A_Tab.getValue("單價");
Prepay= (BigDecimal)A_Tab.getValue("銷貨訂金");
}else if (fname.equals("單價")) {
Price = (BigDecimal) A_Value;
Qty = (BigDecimal)A_Tab.getValue("訂單數量");
Prepay= (BigDecimal)A_Tab.getValue("銷貨訂金");
}else if (fname.equals("銷貨訂金")) {
Prepay = (BigDecimal) A_Value;
Qty = (BigDecimal)A_Tab.getValue("訂單數量");
Price = (BigDecimal)A_Tab.getValue("單價");
}
Integer Tax_ID = (Integer)A_Tab.getValue("稅別_ID");
String type = DB.getSQLValueString(null,"SELECT 編號 FROM 稅別 WHERE 稅別_ID=?", Tax_ID );
if (type.equals("02")) {
Amt1= Qty.multiply(Price);
Amt = Amt1.divide(Rate.add(new BigDecimal(1) ) , 0, RoundingMode.CEILING );
Tax = Amt1.subtract(Amt);
Total = Amt.add(Tax).subtract(Prepay);
}else{
Amt = Qty.multiply(Price);
Tax = Amt.multiply(Rate);
Total = Amt.add(Tax).subtract(Prepay);
}
if (Qty == null || Price==null || Rate == null)
{A_Tab.setValue("訂單金額", null );
A_Tab.setValue("稅額", null );
A_Tab.setValue("合計", null );
}else{
//Amt = Qty.multiply(Price);
//Tax = Amt.multiply(Rate);
//Total = Amt.add(Tax).subtract(Prepay);
A_Tab.setValue("訂單金額", Amt );
A_Tab.setValue("稅額", Tax);
A_Tab.setValue("合計", Total);
}
}
result=""
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
BigDecimal Qty = new BigDecimal (0);
BigDecimal Price = new BigDecimal (0);
BigDecimal Prepay = new BigDecimal (0);
BigDecimal Amt = new BigDecimal (0);
BigDecimal Amt1 = new BigDecimal (0);
String fname = A_Field.getColumnName();
// Integer ID= (Integer)A_Value;
BigDecimal Rate = (BigDecimal)A_Tab.getValue("稅率");
if (Rate != null) Rate = Rate.divide(new BigDecimal(100));
if (fname.equals("訂單數量")) {
Qty = (BigDecimal) A_Value;
Price= (BigDecimal)A_Tab.getValue("單價");
Prepay= (BigDecimal)A_Tab.getValue("銷貨訂金");
}else if (fname.equals("單價")) {
Price = (BigDecimal) A_Value;
Qty = (BigDecimal)A_Tab.getValue("訂單數量");
Prepay= (BigDecimal)A_Tab.getValue("銷貨訂金");
}else if (fname.equals("銷貨訂金")) {
Prepay = (BigDecimal) A_Value;
Qty = (BigDecimal)A_Tab.getValue("訂單數量");
Price = (BigDecimal)A_Tab.getValue("單價");
}
Integer Tax_ID = (Integer)A_Tab.getValue("稅別_ID");
String type = DB.getSQLValueString(null,"SELECT 編號 FROM 稅別 WHERE 稅別_ID=?", Tax_ID );
if (type.equals("02")) {
Amt1= Qty.multiply(Price);
Amt = Amt1.divide(Rate.add(new BigDecimal(1) ) , 0, RoundingMode.CEILING );
Tax = Amt1.subtract(Amt);
Total = Amt.add(Tax).subtract(Prepay);
}else{
Amt = Qty.multiply(Price);
Tax = Amt.multiply(Rate);
Total = Amt.add(Tax).subtract(Prepay);
}
if (Qty == null || Price==null || Rate == null)
{A_Tab.setValue("訂單金額", null );
A_Tab.setValue("稅額", null );
A_Tab.setValue("合計", null );
}else{
//Amt = Qty.multiply(Price);
//Tax = Amt.multiply(Rate);
//Total = Amt.add(Tax).subtract(Prepay);
A_Tab.setValue("訂單金額", Amt );
A_Tab.setValue("稅額", Tax);
A_Tab.setValue("合計", Total);
}
}
result=""
iDempiere Callout ERP 規則引擎實作 groovy:廠商ID帶出編號簡稱
import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
// Integer ID= (Integer)A_Tab.getValue("SK_BOM_ID");
String sql = "SELECT 編號,簡稱 FROM 廠商 WHERE 廠商_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String code1 = rs.getString("編號");
String name1 = rs.getString("簡稱")
A_Tab.setValue("廠商名稱", code1+"_"+name1);
}
}
result=""
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
// Integer ID= (Integer)A_Tab.getValue("SK_BOM_ID");
String sql = "SELECT 編號,簡稱 FROM 廠商 WHERE 廠商_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String code1 = rs.getString("編號");
String name1 = rs.getString("簡稱")
A_Tab.setValue("廠商名稱", code1+"_"+name1);
}
}
result=""
iDempiere Callout ERP 規則引擎實作 groovy:廠商簡稱不得重覆
import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null && A_OldValue!=A_Value){
String sname = (String)A_Tab.getValue("簡稱");
sql = "SELECT COUNT(*) FROM 廠商 WHERE 簡稱=?";
BigDecimal sQty = DB.getSQLValueBD (null, sql, [sname] );
if ( sQty.compareTo(new BigDecimal(0) ) > 0 )
A_Tab.fireDataStatusEEvent("簡稱已存在","簡稱:"+ sname +"已存在", false);
// A_Tab.setValue("Description", "目前總數:"+tQty ); // +" , "+BOMQty+" , "+OldBOMQty+" , "+SK_BOM_ID+" , "+SK_BOMLine_ID);
}
result=""
import org.compiere.util.DB
import org.compiere.util.Msg
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null && A_OldValue!=A_Value){
String sname = (String)A_Tab.getValue("簡稱");
sql = "SELECT COUNT(*) FROM 廠商 WHERE 簡稱=?";
BigDecimal sQty = DB.getSQLValueBD (null, sql, [sname] );
if ( sQty.compareTo(new BigDecimal(0) ) > 0 )
A_Tab.fireDataStatusEEvent("簡稱已存在","簡稱:"+ sname +"已存在", false);
// A_Tab.setValue("Description", "目前總數:"+tQty ); // +" , "+BOMQty+" , "+OldBOMQty+" , "+SK_BOM_ID+" , "+SK_BOMLine_ID);
}
result=""
iDempiere Callout ERP 規則引擎實作 groovy:客戶簡稱不得重覆
import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null && A_OldValue!=A_Value){
String sname = (String)A_Tab.getValue("簡稱");
sql = "SELECT COUNT(*) FROM 客戶 WHERE 簡稱=?";
BigDecimal sQty = DB.getSQLValueBD (null, sql, [sname] );
if ( sQty.compareTo(new BigDecimal(0) ) > 0 )
A_Tab.fireDataStatusEEvent("簡稱已存在","簡稱:"+ sname +"已存在", false);
// A_Tab.setValue("Description", "目前總數:"+tQty ); // +" , "+BOMQty+" , "+OldBOMQty+" , "+SK_BOM_ID+" , "+SK_BOMLine_ID);
}
result=""
import org.compiere.util.DB
import org.compiere.util.Msg
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null && A_OldValue!=A_Value){
String sname = (String)A_Tab.getValue("簡稱");
sql = "SELECT COUNT(*) FROM 客戶 WHERE 簡稱=?";
BigDecimal sQty = DB.getSQLValueBD (null, sql, [sname] );
if ( sQty.compareTo(new BigDecimal(0) ) > 0 )
A_Tab.fireDataStatusEEvent("簡稱已存在","簡稱:"+ sname +"已存在", false);
// A_Tab.setValue("Description", "目前總數:"+tQty ); // +" , "+BOMQty+" , "+OldBOMQty+" , "+SK_BOM_ID+" , "+SK_BOMLine_ID);
}
result=""
iDempiere Callout ERP 規則引擎實作 groovy:客戶ID帶出編號簡稱
import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
// Integer ID= (Integer)A_Tab.getValue("SK_BOM_ID");
String sql = "SELECT 編號,簡稱 FROM 客戶 WHERE 客戶_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String code1 = rs.getString("編號");
String name1 = rs.getString("簡稱")
A_Tab.setValue("客戶名稱", code1+"_"+name1);
}
}
result=""
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
// Integer ID= (Integer)A_Tab.getValue("SK_BOM_ID");
String sql = "SELECT 編號,簡稱 FROM 客戶 WHERE 客戶_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String code1 = rs.getString("編號");
String name1 = rs.getString("簡稱")
A_Tab.setValue("客戶名稱", code1+"_"+name1);
}
}
result=""
iDempiere Callout ERP 規則引擎實作 groovy:產品ID帶品號品名規格
import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
// Integer ID= (Integer)A_Tab.getValue("SK_BOM_ID");
//
String sql = "SELECT a.產品品號,a.產品品名,a.產品規格, c.單位_id, a.加工單價, a.應收單價 FROM 產品 a INNER JOIN 計價單位 b ON a.計價單位_id=b.計價單位_id INNER JOIN 單位 c ON b.名稱=c.名稱 WHERE a.產品_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String code1 = rs.getString("產品品號");
String name1 = rs.getString("產品品名");
String spec1 = rs.getString("產品規格");
Integer uom1 = new Integer( rs.getInt("單位_id"));
BigDecimal price = rs.getBigDecimal ("加工單價");
BigDecimal price1 = rs.getBigDecimal ("應收單價");
if (code1 !=null) A_Tab.setValue("產品品號", code1 );
if (name1 !=null) A_Tab.setValue("產品品名", name1 );
if (spec1 !=null) A_Tab.setValue("產品規格", spec1 );
if (uom1 !=null) A_Tab.setValue("單位_id", uom1 );
if (price !=null) A_Tab.setValue("加工單價", price ); // 加工單價
if (price1 !=null) A_Tab.setValue("單價", price1 ); // 應收單價
A_Tab.setValue("備註", "單價:"+price1+" ,加工單價:"+price );
}
}
result=""
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null) // A_OldValue!=A_Value)
{
Integer ID= (Integer)A_Value;
// Integer ID= (Integer)A_Tab.getValue("SK_BOM_ID");
//
String sql = "SELECT a.產品品號,a.產品品名,a.產品規格, c.單位_id, a.加工單價, a.應收單價 FROM 產品 a INNER JOIN 計價單位 b ON a.計價單位_id=b.計價單位_id INNER JOIN 單位 c ON b.名稱=c.名稱 WHERE a.產品_ID=?";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, ID.intValue());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String code1 = rs.getString("產品品號");
String name1 = rs.getString("產品品名");
String spec1 = rs.getString("產品規格");
Integer uom1 = new Integer( rs.getInt("單位_id"));
BigDecimal price = rs.getBigDecimal ("加工單價");
BigDecimal price1 = rs.getBigDecimal ("應收單價");
if (code1 !=null) A_Tab.setValue("產品品號", code1 );
if (name1 !=null) A_Tab.setValue("產品品名", name1 );
if (spec1 !=null) A_Tab.setValue("產品規格", spec1 );
if (uom1 !=null) A_Tab.setValue("單位_id", uom1 );
if (price !=null) A_Tab.setValue("加工單價", price ); // 加工單價
if (price1 !=null) A_Tab.setValue("單價", price1 ); // 應收單價
A_Tab.setValue("備註", "單價:"+price1+" ,加工單價:"+price );
}
}
result=""
2014年10月12日 星期日
這是一個自製的時代, 一個站在巨人的肩膀上的時代 (Base on Open Source ERP iDempiere)
這是一個自製的時代, 一個站在巨人的肩膀上的時代 (Base on Open Source ERP iDempiere)
我們鼓勵大家用 Open Source 當基礎發展出自己要的系統
( 依據 GPL 規範不管無償或有償, 有再度 [散播] 修改後軟體才公告免費讓第三者免費取得)
( [修改後軟體] : 意指動到 java source code, 有重新編譯過 idempere)
( iDempiere 是 MDA 增加: 輸入畫面, 查詢報表, 驗證規則, 並不需動到 java source code,)
EPICOR 是一個反社會潮流的宣告 :(節錄給大家欣賞)
她得偏激基礎立論就是
1.自製一定是沒有顧問
2.自製沒有Open Source 可以當基礎來發展
像他這種沒有 MDA 架構的 ERP 是技術落後的商業軟件
因為開放原碼沒有向下相容的必要性商業行為
因為開放原碼 Upgrade 是不需要付版權費用
因此沒有必要為版權費用自己去卡自己的路
In today's complex business environment, it can be very challenging for in house IT professionals to keep up with the pace of change necessary to sustain a competitive advantage
.
Whether the homegrown IT system you have seems "fine" for now,or your company has developed strong loyalty to a custom-built solution, these options cannot offer you the level of protection and security of an outside enterprise resource planning (ERP) solution.
ERP system installation is often thought of as the most risky, expensive, and disruptive investment a distributor can make in their business. However, you must also consider the inherent risks of a homegrown solution. When you argue against a potential system
switch with costs, pain, and efficiency concerns, you should take a look at them as they exist in your current system.
Compared to a robust ERP system built by a well-established software provider, homegrown tends to be fragile and lacking in - depth support.
And while a custom-built system may seem to offer control and flexibility, it may actually lock you into a suboptimal solution. Some basic questions to consider include:
Is software development really your core competency?
Can you spare the programming resources ?
Do you have the years and the capital to develop, test, and fine-tune your application?
What happens if you lose the precious few resources that can support and maintain your older technology?
What is your visibility into emerging technologies and industry best practices that other distributors are using?
Is your system truly integrating information and processes from all functional divisions of your organization, or do you have disparate data sources “running” your business?
Cost-effective ERP solutions meet distribution organizations’ demands by leveraging existing applications and technologies, while taking the burden off your internal development
resources.
For all of these reasons, it is in your best interest to partner with a leading ERP provider, in order to advance your operations to the next level of efficiency.
To help answer the above questions and explain the realities of an ERP system, we have compiled the top nine reasons to weigh the advantages of an outside ERP over your homegrown system
.
我們鼓勵大家用 Open Source 當基礎發展出自己要的系統
( 依據 GPL 規範不管無償或有償, 有再度 [散播] 修改後軟體才公告免費讓第三者免費取得)
( [修改後軟體] : 意指動到 java source code, 有重新編譯過 idempere)
( iDempiere 是 MDA 增加: 輸入畫面, 查詢報表, 驗證規則, 並不需動到 java source code,)
EPICOR 是一個反社會潮流的宣告 :(節錄給大家欣賞)
她得偏激基礎立論就是
1.自製一定是沒有顧問
2.自製沒有Open Source 可以當基礎來發展
像他這種沒有 MDA 架構的 ERP 是技術落後的商業軟件
因為開放原碼沒有向下相容的必要性商業行為
因為開放原碼 Upgrade 是不需要付版權費用
因此沒有必要為版權費用自己去卡自己的路
In today's complex business environment, it can be very challenging for in house IT professionals to keep up with the pace of change necessary to sustain a competitive advantage
.
Whether the homegrown IT system you have seems "fine" for now,or your company has developed strong loyalty to a custom-built solution, these options cannot offer you the level of protection and security of an outside enterprise resource planning (ERP) solution.
ERP system installation is often thought of as the most risky, expensive, and disruptive investment a distributor can make in their business. However, you must also consider the inherent risks of a homegrown solution. When you argue against a potential system
switch with costs, pain, and efficiency concerns, you should take a look at them as they exist in your current system.
Compared to a robust ERP system built by a well-established software provider, homegrown tends to be fragile and lacking in - depth support.
And while a custom-built system may seem to offer control and flexibility, it may actually lock you into a suboptimal solution. Some basic questions to consider include:
Is software development really your core competency?
Can you spare the programming resources ?
Do you have the years and the capital to develop, test, and fine-tune your application?
What happens if you lose the precious few resources that can support and maintain your older technology?
What is your visibility into emerging technologies and industry best practices that other distributors are using?
Is your system truly integrating information and processes from all functional divisions of your organization, or do you have disparate data sources “running” your business?
Cost-effective ERP solutions meet distribution organizations’ demands by leveraging existing applications and technologies, while taking the burden off your internal development
resources.
For all of these reasons, it is in your best interest to partner with a leading ERP provider, in order to advance your operations to the next level of efficiency.
To help answer the above questions and explain the realities of an ERP system, we have compiled the top nine reasons to weigh the advantages of an outside ERP over your homegrown system
.
2014年10月11日 星期六
管理軟體 [ 自造 ] 活動 Management Software [ homegrown ]
管理軟體 [ 自造 ] 活動 Management Software [ homegrown]
以下是說你可以買得到的軟體的話千萬別自己來
但是他沒告訴你買來調整比自己寫還複雜還困難
就像華碩買 一億軟體 兩億顧問 在花三億來調整改寫成自己要的
因為沒人敢承擔自己開發規格直接外包的後果
到最後只是繞一圈要承包商來發包改成自己要的
自己要的系統先透過買來的大系統驗證系統的差異性在重差異中開始改系統
http://erp.ittoolbox.com/groups/vendor-selection/erp-select/can-a-home-grown-system-be-the-right-choice-482229
Glenn Cronin replied Jun 2, 2004
以下是說你可以買得到的軟體的話千萬別自己來
但是他沒告訴你買來調整比自己寫還複雜還困難
就像華碩買 一億軟體 兩億顧問 在花三億來調整改寫成自己要的
因為沒人敢承擔自己開發規格直接外包的後果
到最後只是繞一圈要承包商來發包改成自己要的
自己要的系統先透過買來的大系統驗證系統的差異性在重差異中開始改系統
http://erp.ittoolbox.com/groups/vendor-selection/erp-select/can-a-home-grown-system-be-the-right-choice-482229
Glenn Cronin replied Jun 2, 2004
2014年10月8日 星期三
iDempiere :: m_substitute 這一個替代用料沒有優先順序
iDempiere ::
原使設計 m_substitute 這一個替代用料表沒有優先順序
原使設計 這個 m_substitute 沒有 m_substitute_id 主鍵
原使設計 也沒在 bomline 裡面有是用替代類別
CREATE TABLE adempiere.m_substitute
(
m_product_id numeric(10,0) NOT NULL,
substitute_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,
name character varying(60),
description character varying(255),
m_substitute_uu character varying(36) DEFAULT NULL::character varying,
CONSTRAINT m_substitute_pkey PRIMARY KEY (m_product_id, substitute_id),
原使設計 m_substitute 這一個替代用料表沒有優先順序
原使設計 這個 m_substitute 沒有 m_substitute_id 主鍵
原使設計 也沒在 bomline 裡面有是用替代類別
CREATE TABLE adempiere.m_substitute
(
m_product_id numeric(10,0) NOT NULL,
substitute_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,
name character varying(60),
description character varying(255),
m_substitute_uu character varying(36) DEFAULT NULL::character varying,
CONSTRAINT m_substitute_pkey PRIMARY KEY (m_product_id, substitute_id),
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;
-- 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;
-- 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;
訂閱：
文章 (Atom)