idempiere groovy csv 船期表

import groovy.sql.Sql
import java.sql.Timestamp
import org.compiere.util.DB


// def sql = Sql.newInstance("jdbc:postgres://localhost:5432/taya1107", "adempiere", "adempiere", "org.postgresql.Driver")
// def people = sql.dataSet("ZZTEST")
int v1=0
new File("d:\\test\\1.csv").splitEachLine(",")
{fields ->
   def s1="null"   // PL No.,
   def s2="null"   // 客戶單號,
   def s3="null"   // 料號,
   def s4="null"   // 船公司,
   def s5="null"   // 船名,
   def s6="null"   // 航次,
   def s7="null"   // S/O no.,
   def s8="null"   // 貨櫃號碼,
   def s9="null"   // 起運港,
   def s10="null"   // 結關日,
   def s11="null"   // 預定開航日,
   def s12="null"   // 實際開航日,
   def s13="null"   // 抵達港,
   def s14="null"   // 預計抵港日,
   def s15="null"   // 實際抵港日,
   def s16="null"   // 清關日

    if (fields[0] != null)  s1=fields[0]

    if (fields[1] != null)  s2=fields[1]
    if (fields[2] != null)  s3=fields[2]

    if (fields[3] != null)  s4=fields[3]

    if (fields[4] != null)  s5=fields[4]
    if (fields[5] != null)  s6=fields[5]

    if (fields[6] != null)  s7=fields[6]

    if (fields[7] != null)  s8=fields[7]
    if (fields[8] != null)  s9=fields[8]

    if (fields[9] != null)  s10=fields[9]

    if (fields[10] != null)  s11=fields[10]
    if (fields[11] != null)  s12=fields[11]
    if (fields[12] != null)  s13=fields[12]
    if (fields[13] != null)  s14=fields[13]
    if (fields[14] != null)  s15=fields[14]
    if (fields[15] != null)  s16=fields[15]
    if  (v1 > 0) {
       String sql = "INSERT INTO zztest(s1,s2,s3,s4,s5,s6,s7,s8,s9,s10,s11,s12,s13,s14,s15,s16)
) VALUES ("
+"  '" +s1
+"', " +s2
+" , " +s3
+" , " +s4
+" , " +s5
+" , " +s6
+" , " +s7
+" , " +s8
+" , " +s9
+" , " +s10
+" , " +s11
+" , " +s12
+" , " +s13
+" , " +s14
+" , " +s15
+" , " +s16
+")";

       DB.executeUpdateEx( sql, A_TrxName);
       println  sql
     }
 
 
    v1 = v1+1
//  people.add
//    s1: fields[0],
//    n1: fields[1]  )
}

iDempiere Sales Order 印表錯誤信息

C_Order_Header_v.DropShip_Location_ID=C_BPartner_Location.C_BPartner_Location_ID)
AS DropShip_Location_ID,

C_Order_Header_v.DropShip_Location_ID AS DropShip_Location_ID,

(SELECT C_BPartner.Value||'-'||C_BPartner.Name
FROM C_BPartner C_BPartner
WHERE C_Order_Header_v.DropShip_BPartner_ID=C_BPartner
.C_BPartner_ID) AS KKKKKDropShip_BPartner_ID,

C_Order_Header_v.DropShip_BPartner_ID AS DropShip_BPartner_ID,
C_Order_Header_v.Separator,
C_Order_Header_v.IsFirstNameOnly,
C_Order_Header_v.FixMonthCutoff,
C_Order_Header_v.FixMonthDay,
C_Order_Header_v.FixMonthOffset,
C_Order_Header_v.IsDueFixed,
C_Order_Header_v.FreightAmt,

(SELECT NVL(M_FreightCategory.Name,'-1')
FROM M_FreightCategory
WHERE
C_Order_Header_v.M_FreightCategory_ID=M_FreightCategory.M_FreightCategory_ID)
AS LLLLLM_FreightCategory_ID,

C_Order_Header_v.M_FreightCategory_ID AS M_FreightCategory_ID,
C_Order_Header_v.FreightCostRule,
C_Order_Header_v.GraceDays,
C_Order_Header_v.IsInTransit,
C_Order_Header_v.IsInvoiced,

(SELECT C_Order.DocumentNo
FROM C_Order C_Order
WHERE C_Order_Header_v.Link_Order_ID=C_Order.C_Order_ID)
AS MMMMMLink_Order_ID,

C_Order_Header_v.Link_Order_ID AS Link_Order_ID,
C_Order_Header_v.m_paymentterm_value,

(SELECT AD_Org.Value||'-'||AD_Org.Name FROM AD_Org AD_Org
WHERE C_Order_Header_v.M_Warehouse_AD_Org_ID=AD_Org.AD_Org_ID)
AS NNNNNM_Warehouse_AD_Org_ID,

C_Order_Header_v.M_Warehouse_AD_Org_ID AS M_Warehouse_AD_Org_ID,
C_Order_Header_v.m_warehouse_description,
C_Order_Header_v.m_warehouse_isactive,
C_Order_Header_v.m_warehouse_name,
C_Order_Header_v.m_warehouse_value,
C_Order_Header_v.NetDay,
C_Order_Header_v.NetDays,
C_Order_Header_v.IsNextBusinessDay,
C_Order_Header_v.OrderType,

(SELECT NVL(AD_OrgType.Name,'-1') FROM AD_OrgType
WHERE C_Order_Header_v.AD_OrgType_ID=AD_OrgType.AD_OrgType_ID) AS OOOOOAD_OrgType_ID,

C_Order_Header_v.AD_OrgType_ID AS AD_OrgType_ID,

(SELECT AD_Org.Value||'-'||AD_Org.Name
FROM AD_Org AD_Org
WHERE C_Order_Header_v.Parent_Org_ID=AD_Org.AD_Org_ID)
AS PPPPPParent_Org_ID,

C_Order_Header_v.Parent_Org_ID AS Parent_Org_ID,

(SELECT NVL(C_BPartner_Location.Name,'-1')
FROM C_BPartner_Location
WHERE C_Order_Header_v.C_BPartner_Location_ID=C_BPartner_Location.C_BPartner_Location_ID)
AS QQQQQC_BPartner_Location_ID,

C_Order_Header_v.C_BPartner_Location_ID AS C_BPartner_Location_ID,
C_Order_Header_v.IsPayScheduleValid,

(SELECT NVL(C_Payment.DocumentNo,'-1') ||'_'|| NVL(CAST (C_Paymen
t.DateTrx AS Text),'-1') ||'_'|| NVL(CAST (C_Payment.PayAmt AS Text),'-1') ||'_'
|| NVL(C_Payment.A_Name,'-1') FROM C_Payment

WHERE C_Order_Header_v.C_Payment_ID=C_Payment.C_Payment_ID)
AS RRRRRC_Payment_ID,

C_Order_Header_v.C_Payment_ID AS C_Payment_ID,

(SELECT C_BPartner.Value||'-'||C_BPartner.Name
FROM C_BPartner C_BPartner
WHERE C_Order_Header_v.Pay_BPartner_ID=C_BPartner.C_BPartner_ID)
AS SSSSSPay_BPartner_ID,

C_Order_Header_v.Pay_BPartner_ID AS Pay_BPartner_ID,
C_Order_Header_v.PaymentRule,

(SELECT NVL(C_PaymentTerm.Name,'-1') FROM C_PaymentTerm
WHERE C_Order_Header_v.C_PaymentTerm_ID=C_PaymentTerm.C_PaymentTerm_ID)
AS TTTTTC_PaymentTerm_ID,

C_Order_Header_v.C_PaymentTerm_ID AS C_PaymentTerm_ID,
C_Order_Header_v.PaymentTermUsage,

(SELECT M_DiscountSchema.Name
FROM M_DiscountSchema M_DiscountSchema
WHERE
C_Order_Header_v.PO_DiscountSchema_ID=M_DiscountSchema.M_DiscountSchema_ID)
AS UUUUUPO_DiscountSchema_ID,

C_Order_Header_v.PO_DiscountSchema_ID AS PO_DiscountSchema_ID,

(SELECT C_PaymentTerm.Name
FROM C_PaymentTerm C_PaymentTerm
WHERE C_Order_Header_v.PO_PaymentTerm_ID=C_PaymentTerm.C_PaymentTerm_ID)
AS VVVVVPO_PaymentTerm_ID,

C_Order_Header_v.PO_PaymentTerm_ID AS PO_PaymentTerm_ID,

(SELEC
T NVL(C_POS.Name,'-1') FROM C_POS WHERE C_Order_Header_v.C_POS_ID=C_POS.C_POS_ID
) AS WWWWWC_POS_ID,C_Order_Header_v.C_POS_ID AS C_POS_ID,C_Order_Header_v.Posted
,

C_Order_Header_v.IsPrinted,
C_Order_Header_v.Processed,
C_Order_Header_v.ProcessedOn,
C_Order_Header_v.PromotionCode,

(SELECT M_PriceList.Name FROM M_PriceList M_P
riceList WHERE C_Order_Header_v.PO_PriceList_ID=M_PriceList.M_PriceList_ID)
AS XXXXXPO_PriceList_ID,

C_Order_Header_v.PO_PriceList_ID AS PO_PriceList_ID,C_Order_
Header_v.ReceiptFooterMsg,

(SELECT C_Order.DocumentNo FROM C_Order C_Order WHERE
C_Order_Header_v.Ref_Order_ID=C_Order.C_Order_ID) AS YYYYYRef_Order_ID,C_Order_H
eader_v.Ref_Order_ID AS Ref_Order_ID,C_Order_Header_v.RegionName,

(SELECT NVL(C_Region.Name,'-1') FROM C_Region
WHERE C_Order_Header_v.C_Region_ID=C_Region.C_Region_ID)
AS ZZZZZC_Region_ID,

C_Order_Header_v.C_Region_ID AS C_Region_ID,C_Order_Header_v.ReplenishmentClass,

(SELECT C_BPartner_Location.Name
FROM C_BPartner_Location C_BPartner_Location
WHERE C_Order_Header_v.SalesRep_C_BP_Location_ID=C_BPartner_Location.C_BPartner_Location_ID)
AS AAAAAASalesRep_C_BP_Location_ID,

C_Order_Header_v.SalesRep_C_BP_Location_ID AS SalesRep_C_BP_Location_ID,
C_Order_Header_v.salesrep_ad_birthday,

(SELECT AD_Org.Value||'-'||AD_Org.Name FROM AD_Org AD_Or
g WHERE C_Order_Header_v.SalesRep_AD_Org_ID=AD_Org.AD_Org_ID)
AS BBBBBBSalesRep_AD_Org_ID,

C_Order_Header_v.SalesRep_AD_Org_ID AS SalesRep_AD_Org_ID,

(SELECT AD_O
rg.Value||'-'||AD_Org.Name FROM AD_Org AD_Org WHERE C_Order_Header_v.SalesRep_AD
_OrgTrx_ID=AD_Org.AD_Org_ID) AS CCCCCCSalesRep_AD_OrgTrx_ID,

C_Order_Header_v.SalesRep_AD_OrgTrx_ID AS SalesRep_AD_OrgTrx_ID,
C_Order_Header_v.salesrep_bp_acqusitioncost,
C_Order_Header_v.salesrep_bp_actuallifetimeval,
C_Order_Header_v.salesrep_bp_ad_language,

(SELECT AD_Org.Value||'-'||AD_Org.Name FROM AD_Org AD_Org WHERE
C_Order_Header_v.SalesRep_BP_AD_Org_ID=AD_Org.AD_Org_ID)
AS DDDDDDSalesRep_BP_AD_Org_ID,

C_Order_Header_v.SalesRep_BP_AD_Org_ID AS SalesRep_BP_AD_Org_ID,

(SELECT AD_Org.Value||'-'||AD_Org.Name FROM AD_Org AD_Org
WHERE C_Order_Header_v.SalesRep_BP_AD_OrgBP_ID=AD_Org.AD_Org_ID)
AS EEEEEESalesRep_BP_AD_OrgBP_ID,

C_Order_Header_v.SalesRep_BP_AD_OrgBP_ID AS SalesRep_BP_AD_OrgBP_ID,

(SELECT C_BP_Group.Name
FROM C_BP_Group C_BP_Group
WHERE C_Order_Header_v.SalesRep_BP_C_BP_Group_ID=C_BP_Group.C_BP_Group_ID)
AS FFFFFFSalesRep_BP_C_BP_Group_ID,

C_Order_Header_v.SalesRep_BP_C_BP_Group_ID AS SalesRep_BP_C_BP_Group_ID,

(SELECT C_Dunning.Name FROM C_Dunning C_Dunning
WHERE C_Order_Header_v.SalesRep_BP_C_Dunning_ID=C_Dunning.C_Dunning_ID)
AS GGGGGGSalesRep_BP_C_Dunning_ID,
C_Order_Header_v.SalesRep_BP_C_Dunning_ID AS SalesRep_BP_C_Dunning_ID,

(SELECT C_Greeting.Name
FROM C_Greeting C_Greeting WHERE C_Order_Header_v.SalesRep_BP_C_Greeting_ID=C_Greeting.C_Greeting_ID)
AS HHHHHHSalesRep_BP_C_Greeting_ID,

C_Order_Header_v.SalesRep_BP_C_Greeting_ID AS SalesRep_BP_C_Greeting_ID,

(SELECT C_InvoiceSchedule.Name FROM C_InvoiceSchedule
C_InvoiceSchedule WHERE C_Order_Header_v.SalesRep_BP_C_InvoiceSched_ID=C_Invoice
Schedule.C_InvoiceSchedule_ID)
AS IIIIIISalesRep_BP_C_InvoiceSched_ID,

C_Order_Header_v.SalesRep_BP_C_InvoiceSched_ID AS SalesRep_BP_C_InvoiceSched_ID,

(SELECT C_
PaymentTerm.Name FROM C_PaymentTerm C_PaymentTerm WHERE C_Order_Header_v.SalesRe
p_BP_C_PaymentTerm_ID=C_PaymentTerm.C_PaymentTerm_ID)
AS JJJJJJSalesRep_BP_C_PaymentTerm_ID,

C_Order_Header_v.SalesRep_BP_C_PaymentTerm_ID AS SalesRep_BP_C_Payme
ntTerm_ID,

(SELECT C_TaxGroup.Name FROM C_TaxGroup C_TaxGroup WHERE C_Order_Heade
r_v.SalesRep_BP_C_TaxGroup_ID=C_TaxGroup.C_TaxGroup_ID)
AS KKKKKKSalesRep_BP_C_TaxGroup_ID,

C_Order_Header_v.SalesRep_BP_C_TaxGroup_ID AS SalesRep_BP_C_TaxGroup_ID,
C_Order_Header_v.salesrep_bp_created,

(SELECT AD_User.Name FROM AD_User AD_User WHERE C_Order_Header_v.SalesRep_BP_CreatedBy=AD_User.AD_User_ID)
AS LLLLLLSalesRep_BP_CreatedBy,

C_Order_Header_v.SalesRep_BP_CreatedBy AS SalesRep_BP_CreatedBy,
C_Order_Header_v.salesrep_bp_deliveryrule,
C_Order_Header_v.salesrep_bp_deliveryviarule,
C_Order_Header_v.salesrep_bp_description,
C_Order_Header_v.salesrep_bp_dunninggrace,
C_Order_Header_v.salesrep_bp_duns,
C_Order_Header_v.salesrep_bp_firstsale,
C_Order_Header_v.salesrep_bp_flatdiscount,
C_Order_Header_v.salesrep_bp_freightcostrule,
C_Order_Header_v.salesrep_bp_invoicerule,
C_Order_Header_v.salesrep_bp_isactive,
C_Order_Header_v.salesrep_bp_iscustomer,
C_Order_Header_v.salesrep_bp_isdiscountprinted,
C_Order_Header_v.salesrep_bp_isemployee,
C_Order_Header_v.salesrep_bp_ismanufacturer,
C_Order_Header_v.salesrep_bp_isonetime,
C_Order_Header_v.salesrep_bp_ispotaxexempt,
C_Order_Header_v.salesrep_bp_isprospect,
C_Order_Header_v.salesrep_bp_issalesrep,
C_Order_Header_v.salesrep_bp_issummary,
C_Order_Header_v.salesrep_bp_istaxexempt,
C_Order_Header_v.salesrep_bp_isvendor,

(SELECT M_Discount
Schema.Name FROM M_DiscountSchema M_DiscountSchema
WHERE C_Order_Header_v.SalesRep_BP_M_DiscountSchm_ID=M_DiscountSchema.M_DiscountSchema_ID)
AS MMMMMMSalesRep_BP_M_DiscountSchm_ID,

C_Order_Header_v.SalesRep_BP_M_DiscountSchm_ID AS SalesRep_BP_M_DiscountSchm_ID,

(SELECT M_PriceList.Name FROM M_PriceList M_PriceList WHERE
 C_Order_Header_v.SalesRep_BP_M_PriceList_ID=M_PriceList.M_PriceList_ID)
AS NNNNNNSalesRep_BP_M_PriceList_ID,

C_Order_Header_v.SalesRep_BP_M_PriceList_ID AS SalesRep_BP_M_PriceList_ID,
C_Order_Header_v.salesrep_bp_naics,
C_Order_Header_v.salesrep_bp_name2,
C_Order_Header_v.salesrep_bp_numberemployees,
C_Order_Header_v.salesrep_bp_paymentrule,
C_Order_Header_v.salesrep_bp_paymentrulepo,

(SELECT M_DiscountSchema.Name
FROM M_DiscountSchema M_DiscountSchema
WHERE C_Order_Header_v.SalesR
ep_BP_PO_DiscountSchm_ID=M_DiscountSchema.M_DiscountSchema_ID)
AS OOOOOOSalesRep_BP_PO_DiscountSchm_ID,

C_Order_Header_v.SalesRep_BP_PO_DiscountSchm_ID AS SalesRep_BP_PO_DiscountSchm_ID,

(SELECT C_PaymentTerm.Name FROM C_PaymentTerm C_PaymentTerm
WHERE C_Order_Header_v.SalesRep_BP_PO_PaymentTerm_ID=C_PaymentTerm.C_Paymen
tTerm_ID) AS PPPPPPSalesRep_BP_PO_PaymentTerm_ID,

C_Order_Header_v.SalesRep_BP_PO_PaymentTerm_ID AS SalesRep_BP_PO_PaymentTerm_ID,

(SELECT M_PriceList.Name
FROM M_PriceList M_PriceList
WHERE C_Order_Header_v.SalesRep_BP_PO_PriceList_ID=M_PriceList.M_PriceList_ID)
AS QQQQQQSalesRep_BP_PO_PriceList_ID,

C_Order_Header_v.SalesRep_BP_PO_PriceList_ID AS SalesRep_BP_PO_PriceList_ID,
C_Order_Header_v.salesrep_bp_poreference,
C_Order_Header_v.salesrep_bp_potentiallifetime,
C_Order_Header_v.salesrep_bp_rating,
C_Order_Header_v.salesrep_bp_referenceno,

(SELECT AD_User.Name
 FROM AD_User AD_User
WHERE C_Order_Header_v.SalesRep_BP_SalesRep_ID=AD_User.AD_User_ID)
AS RRRRRRSalesRep_BP_SalesRep_ID,

C_Order_Header_v.SalesRep_BP_SalesRep_ID AS SalesRep_BP_SalesRep_ID,

C_Order_Header_v.salesrep_bp_salesvolume,
C_Order_Header_v.salesrep_bp_sendemail,

C_Order_Header_v.salesrep_bp_shareofcustomer,
C_Order_Header_v.salesrep_bp_shelflifeminpct,
C_Order_Header_v.salesrep_bp_so_creditlimit,
C_Order_Header_v.salesrep_bp_so_creditused,
C_Order_Header_v.salesrep_bp_so_description,
C_Order_Header_v.salesrep_bp_socreditstatus,

C_Order_Header_v.salesrep_bp_taxid,
C_Order_Header_v.salesrep_bp_totalopenbalance,
C_Order_Header_v.salesrep_bp_updated,

(SELECT AD_User.Name FROM AD_User AD_User WHERE C_Order_Header_v.Sa
lesRep_BP_UpdatedBy=AD_User.AD_User_ID)
AS SSSSSSSalesRep_BP_UpdatedBy,

C_Order_Header_v.SalesRep_BP_UpdatedBy AS SalesRep_BP_UpdatedBy,
C_Order_Header_v.salesrep_bp_url,
C_Order_Header_v.salesrep_bp_value,

(SELECT C_BPartner.Value||'-'||C_BPartner.Name
FROM C_BPartner C_BPartner WHERE C_Order_Header_v.SalesRep_C_BPartner_
ID=C_BPartner.C_BPartner_ID)
AS TTTTTTSalesRep_C_BPartner_ID,

C_Order_Header_v.SalesRep_C_BPartner_ID AS SalesRep_C_BPartner_ID,
(SELECT C_Greeting.Name FROM C_Greeting C_Greeting
WHERE C_Order_Header_v.SalesRep_C_Greeting_ID=C_Greeting.C_Greeting_ID)
AS UUUUUUSalesRep_C_Greeting_ID,

C_Order_Header_v.SalesRep_C_Greeting_ID AS SalesRep_C_Greeting_ID,

C_Order_Header_v.salesrep_comments,
C_Order_Header_v.salesrep_created,

(SELECT AD_User.Name FROM AD_User AD_User WHERE C_Order_Header_
v.SalesRep_CreatedBy=AD_User.AD_User_ID) AS VVVVVVSalesRep_CreatedBy,

C_Order_Header_v.SalesRep_CreatedBy AS SalesRep_CreatedBy,
C_Order_Header_v.salesrep_description,
C_Order_Header_v.salesrep_email,
C_Order_Header_v.salesrep_fax,
C_Order_Header_v.salesrep_isactive,
C_Order_Header_v.salesrep_lastcontact,
C_Order_Header_v.salesrep_lastresult,
C_Order_Header_v.salesrep_phone,
C_Order_Header_v.salesrep_phone2,

(SELECT AD_User.Name FROM AD_User AD_User
WHERE C_Order_Header_v.SalesRep_Supervisor_ID=AD_User.AD_User_ID)
AS WWWWWWSalesRep_Supervisor_ID,

C_Order_Header_v.SalesRep_Supervisor_ID AS SalesRep_Supervisor_ID,
C_Order_Header_v.salesrep_title,
C_Order_Header_v.salesrep_updated,

(SELECT AD_User.Name FROM AD_User AD_User
WHERE C_Order_Header_v.SalesRep_UpdatedBy=AD_User.AD_User_ID)
AS XXXXXXSalesRep_UpdatedBy,

C_Order_Header_v.SalesRep_UpdatedBy AS SalesRep_UpdatedBy,
C_Order_Header_v.salesrep_value,

C_Order_Header_v.IsSelected,
C_Order_Header_v.IsSelfService,
C_Order_Header_v.SendEMail,

(SELECT M_Warehouse.Name FROM M_Warehouse M_Warehouse WHER
E C_Order_Header_v.M_WarehouseSource_ID=M_Warehouse.M_Warehouse_ID) AS YYYYYYM_W
arehouseSource_ID,C_Order_Header_v.M_WarehouseSource_ID AS M_WarehouseSource_ID,
(SELECT AD_User.Name FROM AD_User AD_User WHERE C_Order_Header_v.Supervisor_ID=A
D_User.AD_User_ID) AS ZZZZZZSupervisor_ID,

C_Order_Header_v.Supervisor_ID AS Supervisor_ID,
C_Order_Header_v.CurSymbol,

(SELECT C_DocType.Name FROM C_DocType C_DocType
WHERE C_Order_Header_v.C_DocTypeTarget_ID=C_DocType.C_DocType_ID)
AS AAAAAAAC_DocTypeTarget_ID,

C_Order_Header_v.C_DocTypeTarget_ID AS C_DocTypeTarget_ID,
C_Order_Header_v.IsTransferred,

(SELECT AD_Org.Value||'-'||AD_Org.Name
FROM AD_OrgAD_Org
WHERE C_Order_Header_v.AD_OrgTrx_ID=AD_Org.AD_Org_ID)
AS BBBBBBBAD_OrgTrx_ID,

C_Order_Header_v.AD_OrgTrx_ID AS AD_OrgTrx_ID,

(SELECT C_ElementValue.Value||'-'||C_ElementValue.Name
FROM C_ElementValue C_ElementValue
WHERE C_Order_Header_v.User1_ID=C_ElementValue.C_ElementValue_ID)
AS CCCCCCCUser1_ID,

C_Order_Header_v.User1_ID AS User1_ID,

(SELECT C_ElementValue.Value||'-'||C_ElementValue.Name FR
OM C_ElementValue C_ElementValue
WHERE C_Order_Header_v.User2_ID=C_ElementValue.
C_ElementValue_ID) AS DDDDDDDUser2_ID,

C_Order_Header_v.User2_ID AS User2_ID,

(SELECT NVL(AD_User.Name,'-1')
FROM AD_User
WHERE C_Order_Header_v.AD_User_ID=AD_User.AD_User_ID)
AS EEEEEEEAD_User_ID,

C_Order_Header_v.AD_User_ID AS AD_User_ID,
C_Order_Header_v.IsValid,
C_Order_Header_v.Volume,
C_Order_Header_v.Weight,
C_Order_Header_v.Postal,
C_Order_Header_v.C_Order_ID
FROM C_Order_Header_v

LEFT OUTER JOIN
C_Location B ON (C_Order_Header_v.C_Location_ID=B.C_Location_ID)
LEFT OUTER JOIN
 AD_Ref_List G ON (C_Order_Header_v.DeliveryRule=G.Value AND G.AD_Reference_ID=1
51) LEFT OUTER JOIN AD_Ref_List H ON (C_Order_Header_v.DeliveryViaRule=H.Value A
ND H.AD_Reference_ID=152)

LEFT OUTER JOIN AD_Ref_List I ON
(C_Order_Header_v.DocStatus=I.ValueAND I.AD_Reference_ID=131)

LEFT OUTER JOIN C_Location K ON
(C_Order_Header_v.Bill_C_Location_ID=K.C_Location_ID)

LEFT OUTER JOIN AD_Ref_List O ON
 (C_Order_Header_v.InvoiceRule=O.Value AND O.AD_Reference_ID=150)

LEFT OUTER JOIN C_Location P ON
(C_Order_Header_v.Org_Location_ID=P.C_Location_ID)

LEFT OUTER JOIN AD_Ref_List R ON
(C_Order_Header_v.PriorityRule=R.Value AND R.AD_Reference_ID=154)

INNER JOIN C_Location W ON
(C_Order_Header_v.Warehouse_Location_ID=W.C_Location_ID)

LEFT OUTER JOIN AD_Ref_List GGGGG ON
(C_Order_Header_v.DocAction=GGGGG.Value AND GGGGG.AD_Reference_ID=0)

WHERE (C_Order_Header_v.C_Order_ID=1013939)
AND C_Order_Header_v.AD_Client_ID IN(0,11)

AND C_Order_Header_v.AD_Org_ID IN
(50007,0,50004,50005,50006,50000,50001,50002,11,12)

AND  (
B.C_Location_ID IS NULL
OR
B.C_Location_ID NOT IN
( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 162
AND AD_User_ID <> 100
AND IsActive = 'Y' )
)


AND  (
G.AD_Ref_List_ID IS NULL
OR
G.AD_Ref_List_ID NOT IN
( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 104
AND AD_User_ID <> 100
AND IsActive = 'Y' )
)

AND  (
H.AD_Ref_List_ID IS NULL
OR H.AD_Ref_List_ID NOT IN
( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 104
AND AD_User_ID <> 100
AND IsActive = 'Y' )
)

AND (
I.AD_Ref_List_ID IS NULL
OR I.AD_Ref_List_ID NOT IN
( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 104
AND AD_User_ID <> 100 AND IsActive = 'Y')
)

AND  (
K.C_Location_ID IS NULL
OR K.C_Location_ID NOT IN
( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 162
AND AD_User_ID <> 100
AND IsActive = 'Y' )
)


AND  (
O.AD_Ref_List_ID IS NULL
OR O.AD_Ref_List_ID NOT IN
( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 104
AND AD_User_ID <> 100
AND IsActive = 'Y' )
)


AND  (
P.C_Location_ID IS NULL
OR P.C_Location_ID NOT IN
( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 162
AND AD_User_ID <> 100
AND IsActive = 'Y' )
)

AND
(
R.AD_Ref_List_ID IS NULL OR R.AD_Ref_List_ID NOT IN
( SELECT Record_ID FROM AD_Private_Access
WHERE AD_Table_ID = 104
AND AD_User_ID <> 100 AND IsActive = 'Y' )
)


AND
 (
W.C_Location_ID IS NULL OR
W.C_Location_ID NOT IN
( SELECT Record_ID
FROM AD_Private_Access
WHERE AD_Table_ID = 162 AND AD_User_ID <> 100 AND IsActive = 'Y' )
)

AND  (
GGGGG.AD_Ref_List_ID IS NULL OR
 GGGGG.AD_Ref_List_ID NOT IN
( SELECT Record_ID FROM AD_Private_Access
WHERE AD_Table_ID = 104
AND AD_User_ID <> 100
AND IsActive = 'Y' )
)
 [32]
23:16:40.485===========> zkoss.handleError:1359: >>org.adempiere.exceptions.Adem
piereException: org.postgresql.util.PSQLException: 錯誤: 欄位 c_order_header_v.c
_bpartner_ad_org_id 不存在
  Position: 23070
[SQL: 0, 42703]
>>org.postgresql.util.PSQLException: 錯誤: 欄位 c_order_header_v.c_bpartner_ad_o
rg_id 不存在
>>  Position: 23070
>>      at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryEx
ecutorImpl.java:2157)
>>      at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutor
Impl.java:1886)
>>      at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.ja
va:255)
>>      at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stat
ement.java:555)
>>      at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(Abstract
Jdbc2Statement.java:417)
>>      at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc
2Statement.java:302)
>>      at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewPr
oxyPreparedStatement.java:116)
>>      at sun.reflect.GeneratedMethodAccessor41.invoke(Unknown Source)
>>      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
>>      at java.lang.reflect.Method.invoke(Unknown Source)
>>      at org.compiere.db.StatementProxy.invoke(StatementProxy.java:125)
>>      at $Proxy0.executeQuery(Unknown Source)
>>      at org.compiere.print.DataEngine.loadPrintData(DataEngine.java:797)
>>      at org.compiere.print.DataEngine.getPrintData(DataEngine.java:200)
>>      at org.compiere.print.ReportEngine.setPrintData(ReportEngine.java:251)
>>      at org.compiere.print.ReportEngine.setPrintFormat(ReportEngine.java:197)

>>      at org.adempiere.webui.window.ZkReportViewer.cmd_report(ZkReportViewer.j
ava:994)
>>      at org.adempiere.webui.window.ZkReportViewer.actionPerformed(ZkReportVie
wer.java:706)
>>      at org.adempiere.webui.window.ZkReportViewer.onEvent(ZkReportViewer.java
:689)
>>      at org.zkoss.zk.ui.AbstractComponent.onEvent(AbstractComponent.java:2742
)
>>      at org.zkoss.zk.ui.AbstractComponent.service(AbstractComponent.java:2713
)
>>      at org.zkoss.zk.ui.AbstractComponent.service(AbstractComponent.java:2654
)
>>      at org.zkoss.zk.ui.impl.EventProcessor.process(EventProcessor.java:136)
>>      at org.zkoss.zk.ui.impl.UiEngineImpl.processEvent(UiEngineImpl.java:1717
)
>>      at org.zkoss.zk.ui.impl.UiEngineImpl.process(UiEngineImpl.java:1502)
>>      at org.zkoss.zk.ui.impl.UiEngineImpl.execUpdate(UiEngineImpl.java:1212)
>>      at org.zkoss.zk.au.http.DHtmlUpdateServlet.process(DHtmlUpdateServlet.ja
va:600)
>>      at org.zkoss.zk.au.http.DHtmlUpdateServlet.doGet(DHtmlUpdateServlet.java
:482)
>>      at org.zkoss.zk.au.http.DHtmlUpdateServlet.doPost(DHtmlUpdateServlet.jav
a:491)
>>      at javax.servlet.http.HttpServlet.service(HttpServlet.java:755)
>>      at javax.servlet.http.HttpServlet.service(HttpServlet.java:848)
>>      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:304)
>>      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:210)
>>      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV
alve.java:224)
>>      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV
alve.java:185)
>>      at org.apache.catalina.authenticator.AuthenticatorBase.invoke(Authentica
torBase.java:472)
>>      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j
ava:151)
>>      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j
ava:100)
>>      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal
ve.java:118)
>>      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.jav
a:405)
>>      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java
:269)
>>      at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(
AbstractProtocol.java:515)
>>      at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoin
t.java:302)
>>      at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source
)
>>      at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
>>      at java.lang.Thread.run(Unknown Source)
 [32]

iDempiere Groovy CSV

import groovy.sql.Sql
import java.sql.Timestamp
import org.compiere.util.DB


// def sql = Sql.newInstance("jdbc:postgres://localhost:5432/taya1107", "adempiere", "adempiere", "org.postgresql.Driver")
// def people = sql.dataSet("ZZTEST")
int v1=0
new File("d:\\test\\1.csv").splitEachLine(",")
{fields ->
 
   def s1="NULL"
   def n1="NULL"
   def n2="NULL"

    if (fields[0] != null)  s1=fields[0]
    if (fields[1] != null)  n1=fields[1]
    if (fields[2] != null)  n2=fields[2]
 
    if  (v1 > 0) {
       String sql = "INSERT INTO ZZTEST(S1,N1,N2) VALUES ('"+s1+"'," + n1+ ","+n2+")";
       DB.executeUpdateEx( sql, A_TrxName);
       println  sql
     }
 
 
    v1 = v1+1
//  people.add
//    s1: fields[0],
//    n1: fields[1]  )
}

台中精機 ERP 為何需要 [全球會計引擎]

台中精機 ERP 為何需要  [全球會計引擎]
What is functionality of the iDempiere Global Accounting Engine ?
iDempiere 北歐東歐中歐南歐北美南美整合服務
Skype: Adempiere/Compiere
全球技術轉移顧問
Albert

The iDempiere Global Accounting Engine provides you with features to satisfy the following basic business needs. You can:

1. Comply with accounting regulations of any country. Some accounting entries are legal in one country but not in another.(完全滿足任何國家規定以符合政府要求, 例如: 關說與關心的不同)
2. The Global Accounting Engine lets you choose the right accounting schemes for your country.(正確選擇正確的會計架構)
3. Define accounting rules per set of books.(不同 SOB帳可以用不同會計規則)
4. Post different accounting rules to different sets of books.(可用不同規則過入不同 SOB帳冊)
5. Generate different accounting entries from the same business transaction into different sets of books.(一個交易產生不同會計分錄給不同 SOB帳冊)
6. Perform synchronous multiple set-of-book accounting. The multiple set-of-book accounting is synchronous because it ensures that a Business transaction is either posted in all related set of books or is not posted in any set of books.(及時同步過入每一個不同 SOB帳冊)
7. Comply with legal and fiscal audit trail requirements.(完全滿足財務稽核軌跡的需求)
8. Store all accounting entries in the same structure and format for all applications. An accounting entry always consists of a header and two or more accounting entry lines. The Global Accounting Engine uses this information to provide accurate reconciliation reports in applications such as Payables and Receivables.
9. Reconcile your subledger accounting system with General Ledger. The Global Accounting Engine provides additional reports such as daily journal reports. Reports are used when a company needs to match the amounts in the subledgers such as Payables, Receivables, and General Ledger.
10. Define journals rather than using hard-coded categories. These journals are numbered sequentially to comply with legal requirements.(不須人工去區分類別可用自動編碼方式來解決政府的規定)
11. Assign sequential numbers to the defined journals across applications.(分配順序編碼遍及整個系統日記帳)
12. Each accounting entry has a unique identifier as well as other header information. This accounting number is the primary key for your accounting lines. You can use the same numbering in applications such as Payables and Receivables.
13. Audit and control period and fiscal year closing procedures.(可稽核可控管每一格財務報表結帳程序)
14. The Global Accounting Engine provides a new mechanism for closing periods that ensures a complete and gapless numbering is generated as required in some countries.
15. Create journal entries and detailed balances for control accounts.(為控管會計科目建立日記帳分錄跟每一筆明細餘額)
16. Control accounts are General Ledger accounts that you can access only from subledger applications such as Payables and Receivables. The balances are calculated for each account with the control account status and third party period and identification. A third party can be a supplier in Payables or a customer in Receivables. The Global Accounting Engine uses these structures to provide legal balance reports that are the same across all applications.
17. Secure subledger accounting entries. You can either secure accounts individually or secure the procedure to create accounting entries. To secure subledger accounting entries, use control accounts to define accounts. A control account is only accessible from the subledgers. Securing your entries ensures a valid audit trail is on paper. The secured posting makes sure that you are only given access to a range of steps involved in the posting cycle.
18. Print reports that correspond to country-specific accounting practices.
19. Print legal subledger accounting reports .

xxx_i

-- Function: adempiere.tg_sk_bom_fg_i(numeric)

-- DROP FUNCTION adempiere.tg_sk_bom_fg_i(numeric);

CREATE OR REPLACE FUNCTION adempiere.tg_sk_bom_fg_i(p_instance numeric)
  RETURNS SETOF adempiere.tg_sk_bom_fg_t AS
$BODY$
/*
原設計是單頭就是一個月份..現在改成直接抓單身..只要確認最後一次產生的單頭且區間要 >= 列印起訖
CURRENT_LANG =  en_US , zh_CN , zh_TW
set search_path=adempiere
select * from adempiere.tg_sk_bom_fg_t
-- "程式完成:: p_instance=1000000 ,p_Record_ID=0 ,p_DocDate=2013-01-01 - 2013-01-31 ,p_SK_NO=TG-G016 - TG-G016"
select * from adempiere.ad_pinstance where ad_pinstance_id=1000000
select * from adempiere.ad_pinstance_para where ad_pinstance_id = 1000000
DROP table adempiere.tg_sk_bom_fg_t CASCADE
SELECT * FROM adempiere.sk_fgbom_i(1000000)
*/
DECLARE

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

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

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

   IF COALESCE(p_instance,1000000) = 1000000 THEN --OR p_Record_ID = 0 THEN

      p_DocDate   := '2013.01.01'::DATE;
      p_DocDateTo := date_trunc('month',p_DocDate)+'1month'::interval-'1day'::interval;
      FOR r IN (

--select * from adempiere.sk_fgjh
     
          SELECT x.vcount , x.sd_skno
            FROM (
                  SELECT COUNT(*) AS vcount ,sd_skno
                    FROM adempiere.sk_bom_fg
                GROUP BY sd_skno
                  ) x
           ORDER BY vcount DESC LIMIT 1
      ) LOOP
      --   v_message := '如果沒資料抓一筆來測試';
      --   p_Record_ID := r.sk_fgjh_id;
           p_SK_NO   := r.sd_skno;
           p_SK_NOTo := r.sd_skno;
      END LOOP;
     
     -- p_DocDate   := '2013.01.01'::DATE;
     -- p_DocDateTo := date_trunc('month',p_DocDate)+'1month'::interval-'1day'::interval;
     -- p_SK_NO   := '2Z-F8050-B070-1';
     -- p_SK_NOTo := '2Z-F8050-B070-1';
   
   END IF;
 
   IF p_DocDateTo IS NULL AND p_DocDate IS NOT NULL THEN
      p_DocDateTo := p_DocDate;
   END IF;
   IF p_SK_NOTo IS NULL AND p_SK_NO IS NOT NULL THEN
      p_SK_NOTo := p_SK_NO;
   END IF;
 
   -- 1) TABLE
   -- alter table adempiere.tg_sk_bom_fg_t add docdateto date
 
   DELETE FROM adempiere.tg_sk_bom_fg_t WHERE ad_pinstance_id = p_instance;

   v_sk_no := '';
   -- 2)  INSERT INOT TABLE SELECT
   FOR r IN (
       SELECT
   --  ad_pinstance_id numeric(10,0) NOT NULL,
       sk_bom_fg_id,  -- numeric(10,0) NOT NULL,
       sk_bom_fg_uu,  -- character varying(36),
       sp_date,       -- date,
       doc_no,        -- character varying(40),
       sd_skno,       -- character varying(40),
       exp_qty,       -- numeric(14,4),
       bomname,       -- character varying(40),
       bomvalue,      -- character varying(40),
       documentno,    -- character varying(40),
       sk_bom_id,     -- numeric(10,0),
       bomqty,        -- numeric(14,4),
       linename,      -- character varying(40),
       linevalue,     -- character varying(40),
       lineqty,       -- numeric(14,4),
       effectivedate, -- date,
       expireddate,   -- date,
       sk_bomalternative_id,  -- numeric(10,0),
       alternativename,       -- character varying(40),
       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
       FROM adempiere.sk_bom_fg
      WHERE 1=1
     -- AND sp_date   <= p_DocDate     --'2013-01-01' --
     -- AND sp_date   >= p_DocDateTo   --'2013-01-31' --
        AND sd_skno BETWEEN p_SK_NO AND p_SK_NOTo  -- p_SK_NO := '2Z-F8050-B070-1';
     -- AND sd_skno='1D-NGF899'
      ORDER BY sd_skno, sp_date desc
      --p_DocDate=2013-01-01 - 2013-01-31 ,p_SK_NO=1D-NGF899 - 1D-NGF899"
  )LOOP
       INSERT INTO adempiere.tg_sk_bom_fg_t(
       ad_pinstance_id, -- numeric(10,0) NOT NULL,
       sk_bom_fg_id,    -- numeric(10,0) NOT NULL,
       sk_bom_fg_uu,    -- character varying(36),
       sp_date,         -- date,
       doc_no,          -- character varying(40),
       sd_skno,         -- character varying(40),
       exp_qty,         -- numeric(14,4),
       bomname,         -- character varying(40),
       bomvalue,        -- character varying(40),
       documentno,      -- character varying(40),
       sk_bom_id,       -- numeric(10,0),
       bomqty,          -- numeric(14,4),
       linename,        -- character varying(40),
       linevalue,       -- character varying(40),
       lineqty,         -- numeric(14,4),
       effectivedate,   -- date,
       expireddate,     -- date,
       sk_bomalternative_id,  -- numeric(10,0),
       alternativename,       -- character varying(40),
       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
       ) VALUES (
       p_instance_id,   -- numeric(10,0) NOT NULL,
       r.sk_bom_fg_id,    -- numeric(10,0) NOT NULL,
       r.sk_bom_fg_uu,    -- character varying(36),
       r.sp_date,         -- date,
       r.doc_no,          -- character varying(40),
       r.sd_skno,         -- character varying(40),
       r.exp_qty,         -- numeric(14,4),
       r.bomname,         -- character varying(40),
       r.bomvalue,        -- character varying(40),
       r.documentno,      -- character varying(40),
       r.sk_bom_id,       -- numeric(10,0),
       r.bomqty,          -- numeric(14,4),
       r.linename,        -- character varying(40),
       r.linevalue,       -- character varying(40),
       r.lineqty,         -- numeric(14,4),
       r.effectivedate,   -- date,
       r.expireddate,     -- date,
       r.sk_bomalternative_id,  -- numeric(10,0),
       r.alternativename,       -- character varying(40),
       r.ad_client_id,    -- numeric(10,0) NOT NULL,
       r.ad_org_id,       -- numeric(10,0) NOT NULL,
       r.isactive,        -- character(1) NOT NULL DEFAULT 'Y'::bpchar,
       r.created,         -- timestamp without time zone NOT NULL DEFAULT now(),
       r.createdby,       -- numeric(10,0) NOT NULL,
       r.updated,         -- timestamp without time zone NOT NULL DEFAULT now(),
       r.updatedby        -- numeric(10,0) NOT NULL
       );
  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_bom_fg_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_bom_fg_i(numeric)
  OWNER TO postgres;

xx_gen

-- Function: adempiere.tg_sk_bom_fg_gen(numeric)

-- DROP FUNCTION adempiere.tg_sk_bom_fg_gen(numeric);

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

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

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

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

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

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






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

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

groovy_14

"import groovy.sql.Sql
import java.sql.Timestamp
import org.compiere.util.DB
import java.sql.PreparedStatement
import java.sql.ResultSet

String sql = "select jray_xx_price_default(1000000)";
PreparedStatement pstmt = DB.prepareStatement(sql,null);
ResultSet rs = pstmt.executeQuery();
rs.close();
pstmt.close();

result="""