Kingdee K/3 WISE 12.3 Order tracking SQL Report

Kingdee K3 has been missing a complete tracking report, so we developed a complete tracking report, directly generated through the query analysis tool.

650) this.width=650; "style=" Float:none; "title=" Query "src=" https://s1.51cto.com/wyfs02/M01/91/C5/ Wkiol1j4tnps3pakaaad42wggpm295.png-wh_500x0-wm_3-wmp_4-s_1267476456.png "alt=" Wkiol1j4tnps3pakaaad42wggpm295.png-wh_50 "/>

650) this.width=650; "style=" Float:none; "title=" condition "src=" https://s1.51cto.com/wyfs02/M01/91/C6/ Wkiom1j4tnpdtuiwaaax7dv3-4s009.png-wh_500x0-wm_3-wmp_4-s_3220721988.png "alt=" Wkiom1j4tnpdtuiwaaax7dv3-4s009.png-wh_50 "/>

650) this.width=650; "style=" width:663px;float:none;height:88px; "title=" Effect "src=" https://s1.51cto.com/wyfs02/M02/ 91/c6/wkiom1j4tnsb66ooaac5pceprqi877.png-wh_500x0-wm_3-wmp_4-s_940161591.png "width=" 725 "height=" "alt=" Wkiom1j4tnsb66ooaac5pceprqi877.png-wh_50 "/>

Code (WIN2008 r2+sql R2 Environment, K3 WISE 12.3):

Select T1.fname1 Customer, t1.fname2 salesman, t1.f_102 number, T1.fbillno order number, t1.fdate order date, T1.fqty order quantity, T5.fqty production quantity, T10.fnumber BOM material code, t10.fname BOM material name, T6.fnumber feed single material code, T6.fqty quantity of feeding order, T7.fqty picking list quantity, T7.soutfprice picking price, t7.soutfamount picking amount,
T2.fdate application date, T2. Seefbillno application number, T2.fnumber application material code, t2.fqty application quantity, t3.fdate purchase date, T3.sefbillno purchase order number, T3.fqty purchase order quantity, T3.fprice purchase price, T3.famount Purchase amount, t4.fdate warehousing date, t4.fbillno warehousing number, t4.fqty warehousing quantity, T8.cpfdate finished goods warehousing date, T8.fqty product storage, T8.cpfprice warehousing unit Price, T8.cpfamount Storage Amount,
T9.fqty number of out-of-stock, T9.fqtyinvoice invoicing from
(Select T5.f_102,t3.fname fname1,t4.fname fname2,t1.fbillno,sum (t2.fqty) Fqty,t1.fdate,t1.finterid from Seorder t1
Inner join Seorderentry T2 on T1.finterid=t2.finterid
INNER join t_organization T3 on T1.fcustid=t3.fitemid
Inner join t_emp T4 on T1.fempid=t4.fitemid
INNER join T_icitem T5 on T2.fitemid=t5.fitemid where t1.fcancellation=0
GROUP by T5.f_102,t3.fname,t4.fname,t1.fbillno,t1.fdate,t1.finterid) T1
--Production task order
Left JOIN (select T2.f_102,t1.forderinterid,sum (t1.fqty) Fqty from ICMO t1
Inner join T_icitem T2 on T1.fitemid=t2.fitemid
GROUP by T2.f_102,t1.forderinterid) T5 on t1.f_102=t5.f_102 and T1.finterid=t5.forderinterid
--Product Storage list
Left JOIN (select T3.f_102,t2.fentryselfa0240,sum (fqty) fqty,t1.fdate cpfdate,t2.fprice cpfprice,t2.famount Cpfamount From Icstockbill T1
Inner join Icstockbillentry T2 on T1.finterid=t2.finterid
INNER join T_icitem T3 on T2.fitemid=t3.fitemid where t1.ftrantype=2
GROUP by T3.f_102,t2.fentryselfa0240,t2.fprice,t2.famount,t1.fdate) T8 on t1.f_102=t8.f_102 and t1.fbillno= t8.fentryselfa0240
--Sales out of the library
Left JOIN (select T3.f_102,t2.forderbillno,sum (fqty) fqty,sum (fqtyinvoice) fqtyinvoice from Icstockbill t1
Inner join Icstockbillentry T2 on T1.finterid=t2.finterid
INNER join T_icitem T3 on T2.fitemid=t3.fitemid where t1.ftrantype=21
Group by T3.f_102,t2.forderbillno) T9 on t1.f_102=t9.f_102 and T1.fbillno=t9.forderbillno
--bom Single
Left join (SELECT distinct t3.f_102,t4.fnumber,t4.fname from Icbom t1
Inner join Icbomchild T2 on T1.finterid=t2.finterid
INNER join T_icitem T3 on T3.fitemid=t1.fitemid
Inner join t_icitem T4 on T2.fitemid=t4.fitemid) T10 on t10.f_102=t1.f_102
--feeding List
Left JOIN (select T3.f_102,t1.forderbillno,t4.fnumber,t4.fname,sum (t2.fqtymust) Fqty from Ppbom t1
Inner join Ppbomentry T2 on T1.finterid=t2.finterid
INNER join T_icitem T3 on T1.fitemid=t3.fitemid
Inner join t_icitem T4 on T2.fitemid=t4.fitemid
Group by T3.f_102,t1.forderbillno,t4.fnumber,t4.fname) T6 on t1.f_102=t6.f_102 and T1.fbillno=t6.forderbillno and T6.fnumber=t10.fnumber
--Picking list
Left JOIN (select Fentryselfb0453,fentryselfb0454,t3.fnumber,sum (fqty) Fqty,t2.fprice Soutfprice,t2.famount Soutfamount from Icstockbill t1
Inner join Icstockbillentry T2 on T1.finterid=t2.finterid
INNER join T_icitem T3 on T2.fitemid=t3.fitemid where t1.ftrantype=24
GROUP by Fentryselfb0453,fentryselfb0454,t3.fnumber,t2.fprice,t2.famount) T7 on t1.f_102=t7.fentryselfb0453 and t1.fbillno=t7.fentryselfb0454 and T6.fnumber=t7.fnumber
--Purchase requisition
Left JOIN (select T1.fheadselfp0136,t2.fentryselfp0137,t3.fnumber,t3.fname,sum (t2.fqty) fqty,t1.fdate fdate, T1.fbillno Seefbillno from Porequest t1
Inner join Porequestentry T2 on T1.finterid=t2.finterid
INNER join T_icitem T3 on T2.fitemid=t3.fitemid
Group by T1.fheadselfp0136,t2.fentryselfp0137,t3.fnumber,t3.fname,t1.fdate,t1.fbillno) T2 on t1.fbillno= t2.fheadselfp0136 and t1.f_102=t2.fentryselfp0137 and T10.fnumber=t2.fnumber
--Purchase order
Left JOIN (select T2.fentryselfp0270,t2.fentryselfp0269,t3.fnumber,t3.fname,sum (t2.fqty) Fqty,t2.fprice Fprice, T2.famount famount,t2.fdate Fdate,t1.fbillno sefbillno from Poorder t1
Inner join Poorderentry T2 on T1.finterid=t2.finterid
INNER join T_icitem T3 on T2.fitemid=t3.fitemid
Group by T2.fentryselfp0270,t2.fentryselfp0269,t3.fnumber,t3.fname,t2.fprice,t2.famount,t2.fdate,t1.fbillno) T3 on t1.fbillno=t3.fentryselfp0270 and t1.f_102=t3.fentryselfp0269 and T10.fnumber=t3.fnumber
--Outsourcing of warehousing orders
Left join (select T2.fentryselfa0158,t2.fentryselfa0159,t1.fdate,t3.fnumber,t3.fname,t1.fdate Fdate1,t1.fbillno Fbillno,sum (T2.fqty) Fqty from Icstockbill t1
Inner join Icstockbillentry T2 on T1.finterid=t2.finterid
INNER join T_icitem T3 on T3.fitemid=t2.fitemid
where t1.ftrantype=1
Group by T2.fentryselfa0158,t2.fentryselfa0159,t1.fdate,t3.fnumber,t3.fname,t1.fdate,t1.fbillno) T4 on t1.fbillno= t4.fentryselfa0159 and t1.f_102=t4.fentryselfa0158 and T10.fnumber=t4.fnumber
where t1.f_102 like '%*kuanghao*% ' and t1.fbillno like '%*fbillnocommon*% '
ORDER BY T1.fdate

This article is from the "Small Business Small Network Management" blog, please be sure to keep this source http://t4513.blog.51cto.com/773180/1917723

Kingdee K/3 WISE 12.3 Order tracking SQL Report