//销售订单
SALESORDER_BASE_TMP:
NoConcatenate
LOAD T_SAL_ORDER.LE_ID,
[T_SAL_ORDER.LCY CODE],
T_SAL_ORDER.SYSTEM,
T_SAL_ORDER.#DataDateTime,
T_SAL_ORDER.#DataDate,
T_SAL_ORDER.FID AS FID,
T_SAL_ORDER.FBILLNO AS SALES_INCOMING.process_id,
T_SAL_ORDER.FCUSTID AS FCUSTID,
date(num#(T_SAL_ORDER.FCREATEDATE, '#.##0,0', ',', '.')) AS SALES_INCOMING.date, //美国格式
date(num#(T_SAL_ORDER.FAPPROVEDATE,'#.##0,0', ',', '.')) AS SALES_INCOMING.DateFirstOrderConfirmation,
T_SAL_ORDER.FSALERID AS FSALERID
FROM
[D:QILKVIEWQLIKVIEWSalesOrderIncomeT_SAL_ORDER.qvd]
(qvd);
Inner JOIN
LOAD T_SAL_ORDERENTRY.LE_ID,
[T_SAL_ORDERENTRY.LCY CODE],
T_SAL_ORDERENTRY.SYSTEM,
T_SAL_ORDERENTRY.#DataDateTime,
T_SAL_ORDERENTRY.#DataDate,
T_SAL_ORDERENTRY.FMATERIALID AS FMATERIALID,
T_SAL_ORDERENTRY.FID AS FID,
T_SAL_ORDERENTRY.FENTRYID AS FENTRYID,
T_SAL_ORDERENTRY.FSEQ AS SALES_INCOMING.process_pos_nr,
T_SAL_ORDERENTRY.FQTY AS SALES_INCOMING.qty,
num#(T_SAL_ORDERENTRY.FZQTAXAMOUNT, '####,##', ',', '.') AS FZQTAXAMOUNT
FROM
[D:QILKVIEWQLIKVIEWSalesOrderIncomeT_SAL_ORDERENTRY.qvd]
(qvd);
inner join
LOAD T_SAL_ORDERENTRY_F.LE_ID,
[T_SAL_ORDERENTRY_F.LCY CODE],
T_SAL_ORDERENTRY_F.SYSTEM,
T_SAL_ORDERENTRY_F.#DataDateTime,
T_SAL_ORDERENTRY_F.#DataDate,
T_SAL_ORDERENTRY_F.FID AS FID,
T_SAL_ORDERENTRY_F.FENTRYID AS FENTRYID,
T_SAL_ORDERENTRY_F.FAMOUNT AS SALES_INCOMING.Original.Amount_1,
T_SAL_ORDERENTRY_F.FAMOUNT_LC AS SALES_INCOMING.amount_1,
T_SAL_ORDERENTRY_F.FALLAMOUNT,
T_SAL_ORDERENTRY_F.FALLAMOUNT_LC
FROM
[D:QILKVIEWQLIKVIEWSalesOrderIncomeT_SAL_ORDERENTRY_F.qvd]
(qvd);
//销售订单
SALESORDER_TMP:
load
SALES_INCOMING.process_id,
FMATERIALID,
FCUSTID,
FSALERID,
FID,
sum( num#(SALES_INCOMING.Original.Amount_1, '####,##', ',', '.')) AS SALES_INCOMING.Original.Amount,
SUM(num#(SALES_INCOMING.amount_1, '####,##', ',', '.')) AS SALES_INCOMING.amount
RESIDENT SALESORDER_BASE_TMP
Group By
SALES_INCOMING.process_id,
FMATERIALID,
FCUSTID,
FSALERID,
FID;
Inner JOIN
LOAD T_BD_CUSTOMER.LE_ID,
[T_BD_CUSTOMER.LCY CODE],
T_BD_CUSTOMER.SYSTEM,
T_BD_CUSTOMER.#DataDateTime,
T_BD_CUSTOMER.#DataDate,
T_BD_CUSTOMER.FCUSTID AS FCUSTID,
T_BD_CUSTOMER.FPROVINCIAL AS FPROVINCIAL,
T_BD_CUSTOMER.FNUMBER AS SALES_INCOMING.customer.id
FROM
[D:QILKVIEWQLIKVIEWSalesOrderIncomeT_BD_CUSTOMER.qvd]
(qvd);
Inner Join
LOAD T_BAS_ASSISTANTDATAENTRY.LE_ID,
[T_BAS_ASSISTANTDATAENTRY.LCY CODE],
T_BAS_ASSISTANTDATAENTRY.SYSTEM,
T_BAS_ASSISTANTDATAENTRY.#DataDateTime,
T_BAS_ASSISTANTDATAENTRY.#DataDate,
T_BAS_ASSISTANTDATAENTRY.FENTRYID AS FPROVINCIAL,
T_BAS_ASSISTANTDATAENTRY.F_LHLRZX AS F_LHLRZX
FROM
[D:QILKVIEWQLIKVIEWSalesOrderIncomeT_BAS_ASSISTANTDATAENTRY.qvd]
(qvd);
Inner Join
LOAD PBNQ_t_Cust100019.LE_ID,
[PBNQ_t_Cust100019.LCY CODE],
PBNQ_t_Cust100019.SYSTEM,
PBNQ_t_Cust100019.#DataDateTime,
PBNQ_t_Cust100019.#DataDate,
PBNQ_t_Cust100019.FID AS F_LHLRZX,
PBNQ_t_Cust100019.FNUMBER AS SALES_INCOMING.PC
FROM
[D:QILKVIEWQLIKVIEWSalesOrderIncomePBNQ_t_Cust100019.qvd]
(qvd);
Inner JOIN
LOAD T_BD_OPERATORENTRY.LE_ID,
[T_BD_OPERATORENTRY.LCY CODE],
T_BD_OPERATORENTRY.SYSTEM,
T_BD_OPERATORENTRY.#DataDateTime,
T_BD_OPERATORENTRY.#DataDate,
T_BD_OPERATORENTRY.FENTRYID AS FSALERID,
T_BD_OPERATORENTRY.FSTAFFID AS SALES_INCOMING.sales.representative.Code
FROM
[D:QILKVIEWQLIKVIEWSalesOrderIncomeT_BD_OPERATORENTRY.qvd]
(qvd);
Inner JOIN
LOAD T_BD_STAFF_L.LE_ID,
[T_BD_STAFF_L.LCY CODE],
T_BD_STAFF_L.SYSTEM,
T_BD_STAFF_L.#DataDateTime,
T_BD_STAFF_L.#DataDate,
T_BD_STAFF_L.FSTAFFID AS SALES_INCOMING.sales.representative.Code,
T_BD_STAFF_L.FNAME AS SALES_INCOMING.sales.representative_id
FROM
[D:QILKVIEWQLIKVIEWSalesOrderIncomeT_BD_STAFF_L.qvd]
(qvd);
inner JOIN
LOAD T_BD_MATERIAL.LE_ID,
[T_BD_MATERIAL.LCY CODE],
T_BD_MATERIAL.SYSTEM,
T_BD_MATERIAL.#DataDateTime,
T_BD_MATERIAL.#DataDate,
T_BD_MATERIAL.FMATERIALID AS FMATERIALID,
T_BD_MATERIAL.FNUMBER AS SALES_INCOMING.article.id
FROM
[D:QILKVIEWQLIKVIEWSalesOrderIncomeT_BD_MATERIAL.qvd]
(qvd);
inner JOIN
LOAD T_SAL_ORDERFIN.LE_ID,
[T_SAL_ORDERFIN.LCY CODE],
T_SAL_ORDERFIN.SYSTEM,
T_SAL_ORDERFIN.#DataDateTime,
T_SAL_ORDERFIN.#DataDate,
T_SAL_ORDERFIN.FID AS FID,
T_SAL_ORDERFIN.FSETTLECURRID AS FCURRENCYID,
T_SAL_ORDERFIN.FExchangeRate AS FExchangeRate,
T_SAL_ORDERFIN.FBILLALLAMOUNT ,
T_SAL_ORDERFIN.FBILLALLAMOUNT_LC
FROM
[D:QILKVIEWQLIKVIEWSalesOrderIncomeT_SAL_ORDERFIN.qvd]
(qvd);
inner Join
LOAD T_BD_CURRENCY.LE_ID,
[T_BD_CURRENCY.LCY CODE],
T_BD_CURRENCY.SYSTEM,
T_BD_CURRENCY.#DataDateTime,
T_BD_CURRENCY.#DataDate,
T_BD_CURRENCY.FCURRENCYID AS FCURRENCYID,
T_BD_CURRENCY.FNUMBER AS SALES_INCOMING.currency,
T_BD_CURRENCY.FCODE AS SALES_INCOMING.Original.Currency.Code
FROM
[D:QILKVIEWQLIKVIEWSalesOrderIncomeT_BD_CURRENCY.qvd]
(qvd);
//销售出库单
OUTSTOCK_TMP:
NoConcatenate
LOAD T_SAL_OUTSTOCK.LE_ID,
[T_SAL_OUTSTOCK.LCY CODE],
T_SAL_OUTSTOCK.SYSTEM,
T_SAL_OUTSTOCK.#DataDateTime,
T_SAL_OUTSTOCK.#DataDate,
T_SAL_OUTSTOCK.FID AS FOUTSTOCKID,
T_SAL_OUTSTOCK.FBILLNO,
T_SAL_OUTSTOCK.FDOCUMENTSTATUS
FROM
[D:QILKVIEWQLIKVIEWSalesOrderIncomeT_SAL_OUTSTOCK.qvd]
(qvd);
Inner Join
LOAD T_SAL_OUTSTOCKENTRY.LE_ID,
[T_SAL_OUTSTOCKENTRY.LCY CODE],
T_SAL_OUTSTOCKENTRY.SYSTEM,
T_SAL_OUTSTOCKENTRY.#DataDateTime,
T_SAL_OUTSTOCKENTRY.#DataDate,
T_SAL_OUTSTOCKENTRY.FID AS FOUTSTOCKID,
T_SAL_OUTSTOCKENTRY.FENTRYID AS FSTOCKENTRYID,
T_SAL_OUTSTOCKENTRY.FMATERIALID AS FMATERIALID
FROM
[D:QILKVIEWQLIKVIEWSalesOrderIncomeT_SAL_OUTSTOCKENTRY.qvd]
(qvd);
Inner Join
LOAD T_SAL_OUTSTOCKENTRY_F.LE_ID,
[T_SAL_OUTSTOCKENTRY_F.LCY CODE],
T_SAL_OUTSTOCKENTRY_F.SYSTEM,
T_SAL_OUTSTOCKENTRY_F.#DataDateTime,
T_SAL_OUTSTOCKENTRY_F.#DataDate,
T_SAL_OUTSTOCKENTRY_F.FENTRYID AS FSTOCKENTRYID,
T_SAL_OUTSTOCKENTRY_F.FCOSTPRICE AS FCOSTPRICE,
T_SAL_OUTSTOCKENTRY_F.FCOSTAMOUNT AS FCOSTAMOUNT
FROM
[D:QILKVIEWQLIKVIEWSalesOrderIncomeT_SAL_OUTSTOCKENTRY_F.qvd]
(qvd);
Inner Join
LOAD T_SAL_OUTSTOCKENTRY_R.LE_ID,
[T_SAL_OUTSTOCKENTRY_R.LCY CODE],
T_SAL_OUTSTOCKENTRY_R.SYSTEM,
T_SAL_OUTSTOCKENTRY_R.#DataDateTime,
T_SAL_OUTSTOCKENTRY_R.#DataDate,
T_SAL_OUTSTOCKENTRY_R.FENTRYID AS FSTOCKENTRYID,
T_SAL_OUTSTOCKENTRY_R.FSOORDERNO AS SALES_INCOMING.process_id
FROM
[D:QILKVIEWQLIKVIEWSalesOrderIncomeT_SAL_OUTSTOCKENTRY_R.qvd]
(qvd);
//发票
INVOICE_TMP:
NoConcatenate
LOAD T_AR_RECEIVABLEENTRY_LK_CW.LE_ID,
[T_AR_RECEIVABLEENTRY_LK_CW.LCY CODE],
T_AR_RECEIVABLEENTRY_LK_CW.SYSTEM,
T_AR_RECEIVABLEENTRY_LK_CW.#DataDateTime,
T_AR_RECEIVABLEENTRY_LK_CW.#DataDate,
T_AR_RECEIVABLEENTRY_LK_CW.FSID AS FZGENTRYID,
T_AR_RECEIVABLEENTRY_LK_CW.FENTRYID AS FCWENTRYID
FROM
[D:QILKVIEWQLIKVIEWSalesOrderIncomeT_AR_RECEIVABLEENTRY_LK_CW.qvd]
(qvd);
Inner Join
//LOAD T_AR_RECEIVABLEENTRY_CW.LE_ID,
// [T_AR_RECEIVABLEENTRY_CW.LCY CODE],
// T_AR_RECEIVABLEENTRY_CW.SYSTEM,
// T_AR_RECEIVABLEENTRY_CW.#DataDateTime,
// T_AR_RECEIVABLEENTRY_CW.#DataDate,
// T_AR_RECEIVABLEENTRY_CW.FID AS FCWID,
// T_AR_RECEIVABLEENTRY_CW.FENTRYID AS FCWENTRYID,
// T_AR_RECEIVABLEENTRY_CW.FMATERIALID AS FMATERIALID,
// T_AR_RECEIVABLEENTRY_CW.FORDERNUMBER AS SALES_INCOMING.process_id,
// num#(T_AR_RECEIVABLEENTRY_CW.FALLAMOUNTFOR, '####,##', ',', '.') AS INVOICEFALLAMOUNT,
// //折扣金额
// num#(T_AR_RECEIVABLEENTRY_CW.FSPLITAMOUNT, '####,##', ',', '.') AS INVOICEFSPLITAMOUNT,
// num#(T_AR_RECEIVABLEENTRY_CW.FPRICEQTY, '####,##', ',', '.') AS FCWSL
//
//FROM
//[D:QILKVIEWQLIKVIEWSalesOrderIncomeT_AR_RECEIVABLEENTRY_CW.qvd]
//(qvd);
LOAD T_AR_RECEIVABLEENTRY_CW.LE_ID,
[T_AR_RECEIVABLEENTRY_CW.LCY CODE],
T_AR_RECEIVABLEENTRY_CW.SYSTEM,
T_AR_RECEIVABLEENTRY_CW.#DataDateTime,
T_AR_RECEIVABLEENTRY_CW.#DataDate,
T_AR_RECEIVABLEENTRY_CW.FID AS FCWID,
T_AR_RECEIVABLEENTRY_CW.FENTRYID AS FCWENTRYID,
T_AR_RECEIVABLEENTRY_CW.FPRICEQTY,
T_AR_RECEIVABLEENTRY_CW.FALLAMOUNTFOR,
T_AR_RECEIVABLEENTRY_CW.FORDERNUMBER AS SALES_INCOMING.process_id,
T_AR_RECEIVABLEENTRY_CW.FMATERIALID AS FMATERIALID,
T_AR_RECEIVABLEENTRY_CW.FALLAMOUNT,
T_AR_RECEIVABLEENTRY_CW.FNOTAXAMOUNTFOR AS FNOTAXAMOUNTFOR,
T_AR_RECEIVABLEENTRY_CW.FSPLITAMOUNT AS INVOICEFSPLITAMOUNT
FROM
[D:QILKVIEWQLIKVIEWSalesOrderIncomeT_AR_RECEIVABLEENTRY_CW.qvd]
(qvd);
Inner Join
LOAD T_AR_RECEIVABLE_CW.LE_ID,
[T_AR_RECEIVABLE_CW.LCY CODE],
T_AR_RECEIVABLE_CW.SYSTEM,
T_AR_RECEIVABLE_CW.#DataDateTime,
T_AR_RECEIVABLE_CW.#DataDate,
T_AR_RECEIVABLE_CW.FID AS FCWID,
T_AR_RECEIVABLE_CW.FBILLNO AS FCWFBILLNO,
T_AR_RECEIVABLE_CW.FZKJE AS FZKJE
FROM
[D:QILKVIEWQLIKVIEWSalesOrderIncomeT_AR_RECEIVABLE_CW.qvd]
(qvd);
Inner Join
LOAD t_AR_receivableFIN.LE_ID,
[t_AR_receivableFIN.LCY CODE],
t_AR_receivableFIN.SYSTEM,
t_AR_receivableFIN.#DataDateTime,
t_AR_receivableFIN.#DataDate,
t_AR_receivableFIN.FID AS FCWID,
t_AR_receivableFIN.FENTRYID,
num#(t_AR_receivableFIN.FEXCHANGERATE, '####,##', ',', '.') AS FCWEXCHANGERATE
FROM
[D:QILKVIEWQLIKVIEWSalesOrderIncome _AR_receivableFIN.qvd]
(qvd);
//发票进行单号，物料分组
INVOICE_GET_TMP:
NoConcatenate
load
SALES_INCOMING.process_id,
FMATERIALID,
sum( num#(FNOTAXAMOUNTFOR, '####,##', ',', '.')) AS INVOICENOTTAXEDAMOUNT_1,
sum( num#(INVOICEFSPLITAMOUNT, '####,##', ',', '.')) AS INVOICEFSPLITAMOUNT_1
RESIDENT INVOICE_TMP
Group By SALES_INCOMING.process_id,
FMATERIALID;
//计算出库单成本
OUTSTOCK_CALCULATION_TMP:
NoConcatenate
load
SALES_INCOMING.process_id,
FMATERIALID,
sum(num#(FCOSTAMOUNT, '####,##', ',', '.')) AS SUMCOSTAMOUNT
RESIDENT OUTSTOCK_TMP
Group By SALES_INCOMING.process_id,
FMATERIALID
;
//汇总输出发票金额 产品成本
PROFIT_GET_TMP:
NoConcatenate
load SALES_INCOMING.process_id,
FMATERIALID,
max(INVOICENOTTAXEDAMOUNT_1) AS SUMINVOICENOTTAXEDAMOUNT,
max(INVOICEFSPLITAMOUNT_1) AS SALES_INCOMING.Original_amount_reduction
RESIDENT INVOICE_GET_TMP
Group By SALES_INCOMING.process_id,
FMATERIALID;
Left Join
load SALES_INCOMING.process_id,
FMATERIALID,
MAX(SUMCOSTAMOUNT) AS SUMPRODUCTCOST
RESIDENT OUTSTOCK_CALCULATION_TMP
Group By SALES_INCOMING.process_id,
FMATERIALID;
//计算利润
PROFIT_CALCULATION_TMP:
NoConcatenate
load SALES_INCOMING.process_id,
FMATERIALID,
max(SUMINVOICENOTTAXEDAMOUNT - SUMPRODUCTCOST) AS SALES_INCOMING.profit
RESIDENT PROFIT_GET_TMP
Group by SALES_INCOMING.process_id,FMATERIALID;