idempiere ERP '船期與貨況追蹤'

// 建立 '資料表' 來接受上傳 String tableName = '船期與貨況追蹤' String tableDefinition = """CREATE TEXT TABLE $tableName ( packinglist_id NUMERIC(10,0), "船公司" VARCHAR(40), "船名" VARCHAR(40), "航次" VARCHAR(20), shippingorder VARCHAR(20), "貨櫃號碼" VARCHAR(20), "起運港" VARCHAR(40), "結關日" DATE, "預定開航日" DATE, "實際開航日" DATE, "抵達港" VARCHAR(40), "預定抵港日" date, "實際抵港日" date, "清關日" date, ad_client_id NUMERIC(10,0) NOT NULL, ad_org_id NUMERIC(10,0) NOT NULL, isactive CHAR(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, "船期與貨況追蹤_id" NUMERIC(10,0), "船期與貨況追蹤_uu" VARCHAR(36)  //atomic_number INTEGER PRIMARY KEY, );"""   //create a new file database and a table corresponding to the csv file Sql sql = Sql.newInstance("jdbc:hsqldb:file:${testdbDir.absolutePath}/testdb", 'sa', ''     ,'org.hsqldb.jdbcDriver') sql.execute(tableDefinition)   //set the source to the csv file sql.execute("SET TABLE elements SOURCE '${TEST_FILE_NAME};all_quoted=true'".toString())   //querying the database that's wrapping our CSV file def elementsOver200Mass = sql.rows("SELECT * FROM $tableName WHERE atomic_mass > ?", [200]) def elementsBetween10And20 = sql.rows(     "SELECT * FROM $tableName WHERE atomic_mass <= ? AND atomic_mass >= ?", [20, 10])   //simple db aggregates def count = 0 sql.eachRow("SELECT count(1) FROM $tableName WHERE atomic_mass <= ?", [20]){row->     count = row[0] } def avg = 0 sql.eachRow("SELECT avg(atomic_mass) FROM $tableName".toString()){row->     avg = row[0] }

idempiere groovy cvs

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

def dbUrl = 'jdbc:postgresql://localhost:5432/taya1110'
def dbUser = 'adempiere'
def dbPassword = 'adempiere'
def driverClass = 'org.postgresql.Driver'
def sql = Sql.newInstance(dbUrl, dbUser, dbPassword, driverClass)
try {     sql.execute("drop table \"船期與貨況追蹤上傳\"")   } catch (Exception e) {}
// PL No.,客戶單號,料號,船公司,船名,航次,S/O no.,貨櫃號碼,起運港,結關日,預定開航日,實際開航日,抵達港,預計抵港日,實際抵港日,清關日
sql.execute('''create table \"船期與貨況追蹤上傳\"(
PLNo             varchar(20),
\"客戶單號\"   varchar(20),
\"料號\"           varchar(20),
\"船公司\"       varchar(20),
\"船名\"           varchar(20),
\"航次\"           varchar(20),
SONo             varchar(20),
\"貨櫃號碼\"   varchar(20),
\"起運港\"       varchar(20),
\"結關日\"           varchar(20),
\"預定開航日\"   varchar(20),
\"實際開航日\"   varchar(20),
\"抵達港\"           varchar(20),
\"預計抵港日\"   varchar(20),
\"實際抵港日\"   varchar(20),
\"清關日\"           varchar(20)
 )''')
def ptable= sql.dataSet("船期與貨況追蹤上傳")
int v1=0
new File("C:\\Users\\albert\\Downloads\\船期與貨況追蹤1021110.csv").splitEachLine(",")
{fields ->
if (v1 > 0){
    ptable.add(
PLNo:fields[0], "客戶單號":fields[1], "料號":fields[2], "船公司":fields[3], "船名":fields[4],,"航次":fields[5],
SONo:fields[6],"貨櫃號碼":fields[7],"起運港":fields[8],
"結關日": fields[9],
"預定開航日":fields[10],
"實際開航日":fields[11],
"抵達港":fields[12],
"預計抵港日":fields[13],
"實際抵港日":fields[14],
"清關日":fields[15]
 )
}
v1=v1+1
}
return "完成"

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 .