Piolot_RepotForm_PeiHao/SQLQuery1.sql

Select
            MONTH(A.FDATE) as '<EFBFBD>·<EFBFBD>', --<EFBFBD>·<EFBFBD>
            (SELECT FNAME FROM T_BD_DEPARTMENT_L WHERE FDEPTID = A.FSaleDeptId) as '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
            (Select FNAME From V_BD_SALESMAN_L Where fid = A.FSalerId) as '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ա', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ա
            (SELECT FNAME FROM T_BD_CUSTOMER_L WHERE FCUSTID = A.FCustId) as '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>λ', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>λ
            A.FDATE as 'ǩ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>', --ǩ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
            A.F_contractnumber as '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
            C.FNAME as '<EFBFBD><EFBFBD>Ʒ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>', --<EFBFBD><EFBFBD>Ʒ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
            C.FSPECIFICATION as '<EFBFBD>ͺŹ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>׼', --<EFBFBD>ͺ<EFBFBD>\<EFBFBD><EFBFBD><EFBFBD><EFBFBD>\<EFBFBD><EFBFBD>׼
            A.F_contractnumber+C.FSPECIFICATION as '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͺŹ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>׼',
            '' as '<EFBFBD>ͺŷ<EFBFBD><EFBFBD><EFBFBD>',
            '' as '<EFBFBD>ͺŴ<EFBFBD><EFBFBD><EFBFBD>',
            '' as 'ҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>',
            CASE WHEN A.F_VRYF_COMBO_LSN = 'DDXS' THEN '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
            WHEN A.F_VRYF_COMBO_LSN = 'SXTXS' THEN '<EFBFBD><EFBFBD>ϵͳ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
            WHEN A.F_VRYF_COMBO_LSN = 'XTZHT' THEN 'ϵͳ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͬ' ELSE ''
            END as '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>',
            '' as 'OEM<EFBFBD>ͺ<EFBFBD>',--ԭȡ<EFBFBD><EFBFBD><EFBFBD>ֶ<EFBFBD>
            '' as '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>',--ԭȡ<EFBFBD><EFBFBD><EFBFBD>ֶ<EFBFBD>
            B.FQty as '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>̨', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>/̨
            (Select FTaxPrice From T_SAL_ORDERENTRY_F Where FENTRYID = B.FENTRYID) as '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ԫ̨', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ԫ/̨
            (Select FAllAmount_LC From T_SAL_ORDERENTRY_F Where FENTRYID = B.FENTRYID) as '<EFBFBD><EFBFBD>ͬ<EFBFBD>ܼ<EFBFBD>', --<EFBFBD><EFBFBD>ͬ<EFBFBD>ܼ<EFBFBD>
            (Select FStockOutQty From T_SAL_ORDERENTRY_R Where FENTRYID = B.FENTRYID) as '<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>', --<EFBFBD>ѽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
            (Select FRemainOutQty From T_SAL_ORDERENTRY_R Where FENTRYID = B.FENTRYID) as 'δ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>', --δ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
            CASE WHEN (D.FOPENSTATUS)= 'A' Then '<EFBFBD><EFBFBD>' When (D.FOPENSTATUS)= 'B' Then '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' When (D.FOPENSTATUS)= 'C' Then '<EFBFBD><EFBFBD>ȫ' Else ''
            END as '<EFBFBD><EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>', --<EFBFBD><EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD> A.F_VRYF_Assistant_k79
            (Select FNAME From T_BAS_PREBDONE_L Where FID = A.F_projectname) as '<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>', --<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
            CASE WHEN --<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>1
            (Select
            aq.F_Projectfield 
            From 
            T_BAS_PREBDTHREE aq
            JOIN
            T_BAS_PREBDTHREE_L aw
            on aq.FID = aw.FID
            Where aq.FID = A.F_BON) = 1 
            THEN '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>¥<EFBFBD><EFBFBD>'
            WHEN --<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>2
            (Select
            aq.F_Projectfield 
            From 
            T_BAS_PREBDTHREE aq
            JOIN
            T_BAS_PREBDTHREE_L aw
            on aq.FID = aw.FID
            Where aq.FID = A.F_BON) = 2
            THEN 'ҽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
            WHEN --<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>3
            (Select
            aq.F_Projectfield 
            From 
            T_BAS_PREBDTHREE aq
            JOIN
            T_BAS_PREBDTHREE_L aw
            on aq.FID = aw.FID
            Where aq.FID = A.F_BON) = 3
            THEN 'ѧУ'
            WHEN --<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>4
            (Select
            aq.F_Projectfield 
            From 
            T_BAS_PREBDTHREE aq
            JOIN
            T_BAS_PREBDTHREE_L aw
            on aq.FID = aw.FID
            Where aq.FID = A.F_BON) = 4
            THEN '<EFBFBD><EFBFBD><EFBFBD>鿵<EFBFBD><EFBFBD>'
            WHEN --<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>5
            (Select
            aq.F_Projectfield 
            From 
            T_BAS_PREBDTHREE aq
            JOIN
            T_BAS_PREBDTHREE_L aw
            on aq.FID = aw.FID
            Where aq.FID = A.F_BON) = 5
            THEN '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
            WHEN --<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>6
            (Select
            aq.F_Projectfield 
            From 
            T_BAS_PREBDTHREE aq
            JOIN
            T_BAS_PREBDTHREE_L aw
            on aq.FID = aw.FID
            Where aq.FID = A.F_BON) = 6
            THEN '<EFBFBD><EFBFBD>Դ'
            WHEN --<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>7
            (Select
            aq.F_Projectfield 
            From 
            T_BAS_PREBDTHREE aq
            JOIN
            T_BAS_PREBDTHREE_L aw
            on aq.FID = aw.FID
            Where aq.FID = A.F_BON) = 7
            THEN '<EFBFBD><EFBFBD>ҵ'
            WHEN --<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>8
            (Select
            aq.F_Projectfield 
            From 
            T_BAS_PREBDTHREE aq
            JOIN
            T_BAS_PREBDTHREE_L aw
            on aq.FID = aw.FID
            Where aq.FID = A.F_BON) = 8
            THEN '<EFBFBD><EFBFBD>ͨ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
            ELSE ''
            END as '<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>', --<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
            CASE WHEN --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
            A.F_PurchasingPro = 'LH' 
            THEN '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
            WHEN --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
            A.F_PurchasingPro = 'BL' 
            THEN '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ز<EFBFBD>'
            WHEN --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
            A.F_PurchasingPro = 'HR' 
            THEN '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
            WHEN --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
            A.F_PurchasingPro = 'N' 
            THEN '<EFBFBD><EFBFBD>'
            WHEN --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
            A.F_PurchasingPro = 'HEF' 
            THEN '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD><EFBFBD><EFBFBD>'
            WHEN --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
            A.F_PurchasingPro = 'LD' 
            THEN '<EFBFBD>̵<EFBFBD>'
            ELSE ''
            END as '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> LH=<EFBFBD><EFBFBD><EFBFBD><EFBFBD>,BL=<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ز<EFBFBD>,HR=<EFBFBD><EFBFBD><EFBFBD><EFBFBD>,N=<EFBFBD><EFBFBD>,HEF=<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD><EFBFBD><EFBFBD>,LD=<EFBFBD>̵<EFBFBD>
            CASE WHEN A.FBUSINESSTYPE = 'VMI' THEN 'VMIҵ<EFBFBD><EFBFBD>' WHEN A.FBUSINESSTYPE = 'DRPTRANS' THEN '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
            WHEN A.FBUSINESSTYPE = 'STRAIGHT' THEN 'ֱ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' WHEN A.FBUSINESSTYPE = 'CONSIGNMENT' THEN '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
            WHEN A.FBUSINESSTYPE = 'NORMAL' THEN '<EFBFBD><EFBFBD>ͨ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' WHEN A.FBUSINESSTYPE = 'DRPSALE' THEN '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
            WHEN A.FBUSINESSTYPE = 'RETURNSO' THEN '<EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' ELSE ''
            END as 'ҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>-<2D><><EFBFBD><EFBFBD>Ʒ<EFBFBD><C6B7><EFBFBD><EFBFBD>',
            CASE WHEN
	        (Select F_Authorization From T_BD_CUSTOMER where FCUSTID = A.FCustId) = 'Y' THEN '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
	        WHEN (Select F_Authorization From T_BD_CUSTOMER where FCUSTID = A.FCustId) = 'N' THEN 'ֱ<EFBFBD><EFBFBD>'
	        ELSE '' 	        END as 'ֱ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>',
            CASE WHEN B.F_SPECIALOFFER = 0 THEN '<EFBFBD><EFBFBD>' WHEN B.F_SPECIALOFFER = 1 THEN '<EFBFBD><EFBFBD>' ELSE '' END as '<EFBFBD>Ƿ<EFBFBD><EFBFBD>ؼ<EFBFBD>',
            '' as 'ҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>',
            (Select wa.FDATAVALUE From
            T_BAS_ASSISTANTDATAENTRY aw
            JOIN 
            T_BAS_ASSISTANTDATAENTRY_L wa
            on aw.FENTRYID = wa.FENTRYID
            Where wa.FENTRYID = (Select F_CreditClassification From T_BD_CUSTOMER where FCUSTID = A.FCustId)
            AND FDocumentStatus = 'C' AND FFORBIDSTATUS = 'A') as '<EFBFBD>ͻ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>', --<EFBFBD>ͻ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
            --(Select top 1 V1.<EFBFBD><EFBFBD><EFBFBD><EFBFBD> From
	           -- ERPTOHR.HYHRV3.dbo.v_erp_empinfo V1
	           -- JOIN
	           -- (Select FName From V_BD_SALESMAN_L
		          --  Where 
		          --  fid in (Select A1.FSALEID From
		          --  (Select a2.* From 
		          --  T_PerformanceSharing a1
		          --  JOIN
		          --  T_PerformanceSharingEntry a2
		          --  on a1.FID = a2.FID
		          --  Where a1.FDocumentStatus = 'C') A1
		          --  JOIN
		          --  T_PerformanceSharing_LK A2
		          --  on A2.FID = A1.FID
		          --  Where A2.FSTableName = 'T_SAL_ORDER1'
		          --  AND A2.FSId = B.FID AND A1.FSALEID = A.FSalerId
		          --  )) V2
	           -- on V1.<2E><><EFBFBD><EFBFBD> = V2.FNAME GROUP BY V1.<2E><><EFBFBD><EFBFBD>)
            --as 'SBU' --SBU
            --Into #TempTable1 
			ISNULL(F.<EFBFBD><EFBFBD><EFBFBD><EFBFBD>,'') as 'SBU',
            '' as '<EFBFBD>ͻ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֶ<EFBFBD>
            '' as '<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֶ<EFBFBD>
            '' as '<EFBFBD>Ƿ<EFBFBD>ֱǩ<EFBFBD><EFBFBD>ϵͳ', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֶ<EFBFBD>
            '' as '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʡ<EFBFBD><EFBFBD>', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֶ<EFBFBD>
            '' as '<EFBFBD><EFBFBD><EFBFBD>ƻ<EFBFBD>ϵͳ', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֶ<EFBFBD>
            '' as 'ҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>̯', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֶ<EFBFBD>
            B.FNOTE as '<EFBFBD><EFBFBD>ע', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֶ<EFBFBD>
            '' as '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֶ<EFBFBD>
            From
            T_SAL_ORDER A
            JOIN
            T_SAL_ORDERENTRY B
            on B.FID = A.FID
            Left JOIN
            (Select aq.FMATERIALID,qa.FNAME,aq.F_XHDL,aq.F_116,qa.FSpecification 
            From 
            T_BD_MATERIAL aq
            Left JOIN
            T_BD_MATERIAL_L qa
            on aq.FMATERIALID = qa.FMATERIALID) C
            on B.FMaterialId = C.FMATERIALID
            LEFT JOIN 
            (Select A2.FOPENSTATUS,A1.FID,A1.FORDERNUMBER From T_AR_RECEIVABLEENTRY A1 JOIN T_AR_RECEIVABLE A2 on A1.FID = A2.FID
            Where A2.FSETACCOUNTTYPE = 3 Group by A2.FOPENSTATUS,A1.FID,A1.FORDERNUMBER) D on A.FBILLNO = D.FORDERNUMBER
			LEFT JOIN 
			V_BD_SALESMAN_L E on A.FSALERID = E.fid
			LEFT JOIN 
			ERPTOHR.HYHRV3.dbo.v_erp_empinfo F on E.FNAME = F.<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
            --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
            Where A.FDocumentStatus = 'C'
            AND A.FCloseStatus = 'A'
            AND A.FCancelStatus = 'A'
            AND A.FSALEORGID = _CurrentOrgUnitId_
AND ('#FPaperContract#' = '' or A.F_contractnumber like '%#FPaperContract#%')
AND ('#FStartDate#' = '' or A.FDATE >= '#FStartDate#') 
AND ('#FEndDate#' = '' or A.FDATE <= '#FEndDate#') 
Order by A.FDATE desc