德意志血統 iDempiere 超越土產ERP 困境的最佳選擇: 11月 2013

2013年11月13日星期三

idempiere ERP "船期與貨況追蹤"

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

// MSSQL jdbc:sqlserver://" + dbHost + ":" + dbPort + ";databaseName=" + dbName + ";";
mssql =Sql.newInstance('jdbc:sqlserver://192.72.1.42:1433;databaseName=Origin','aaa','111111',
'com.microsoft.sqlserver.jdbc.SQLServerDriver' )

// PostgreSQL
postsql =Sql.newInstance('jdbc:postgresql://127.0.0.1/taya1028','adempiere','adempiere','org.postgresql.Driver' )
def ptable= postsql.dataSet("船期與貨況追蹤") //上傳

def isActive='Y'
def H_ID=1000000
def m_created = new Timestamp(System.currentTimeMillis());
def t_start = System.currentTimeMillis()
def id=0
def uu=' '
int client=Env.getContextAsInt(A_Ctx, "AD_Client_ID");
int org=Env.getContextAsInt(A_Ctx, "AD_Org_ID");
int uid=Env.getContextAsInt(A_Ctx, "#AD_User_ID");

mssql.eachRow( 'SELECT ShipmentID, InvoiceNo, 客戶單號, 料號, 船公司, 船名, 航次, 貨況追蹤.序號, 貨櫃號碼, 起運港, 結關日, 預定開航日, 實際開航日, 抵達港, 預計抵港日, 實際抵港日, 清關日 FROM 貨況追蹤' )
{
// select * from adempiere.AD_Sequence WHERE Name='船期與貨況追蹤'--1000346
postsql .query("select adempiere.nextid(1000321, 'N') as id , adempiere.generate_uuid() as uu ")
{ ResultSet rs -> if (rs.next()){ id = rs.getInt('id')
uu = rs.getString('uu') } }
postsql .query("SELECT p.packinglist_id, p.單號,p.單據日期,p.客戶_id,p.客戶英文全名,p.客戶全名,p.客戶送貨地址,p.傳真,p.電話, p.出口公司,p.起運港,p.抵達港,p.結關日,p.嘜頭_id,p.正嘜,p.側嘜, l.訂單_id,l.包裝編號,l.品號_id,l.品名,l.規格, o.訂單單號, o.客戶單號 FROM adempiere.packinglist p INNER JOIN adempiere.packinglistline l ON p.packinglist_id=l.packinglist_id INNER JOIN adempiere.訂單 o ON o.訂單_id=l.訂單_id WHERE p.單號='"+ it.InvoiceNo + "'")
{ ResultSet rs -> if (rs.next()){
id = rs.getInt('id')
uu = rs.getString('uu')
println " ${it.ShipmentID} ${it.InvoiceNo} "
ptable.add(
ad_client_id:client, ad_org_id:org, isactive: 'Y', created:m_created , createdby:uid, updated:m_created, updatedby:uid,
"船期與貨況追蹤_id":id, "船期與貨況追蹤_uu":uu,
ShipmentID:it.ShipmentID, InvoiceNo:it.InvoiceNo ,
"客戶單號":rs.getString('客戶單號'),
"料號":rs.getString('料號'),
packinglist_id:rs.getInt('packinglist_id'),
"船公司":it.船公司, "船名":it.船名, "航次":it.航次, // shippingorder:
"貨櫃號碼":it.貨櫃號碼, "起運港":it.起運港, "結關日":it.結關日,
"預定開航日":it.預定開航日, "實際開航日":it.實際開航日,
"抵達港":it.抵達港,
// "預計抵港日":it.預計抵港日,
"實際抵港日":it.實際抵港日,
"清關日":it.清關日
)
} }
}

def t_end = System.currentTimeMillis()
result="執行完成: " + (t_end - t_start)

2013年11月11日星期一

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]

}

2013年11月10日星期日

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]

訂閱：文章 (Atom)

2013年11月13日 星期三