PABillingOpenTrx_withPAPNUM

• Tweet
• 

          
  
    
CREATE VIEW [dbo].[PABillingOpenTrx_withPAPNUM]    
AS    
SELECT     dbo.PA05100.PAPROJNUMBER,     
--dbo.PAProjects.[Project Name],    
 dbo.PA_FUNC_PA_Biller_Trx_Type(['PA Billing Header OPEN'].PABILLTRXT) AS [PA Biller Trx Type],     
                      RTRIM(['PA Billing Header OPEN'].PADocnumber20) AS [PA Document No.], ['PA Billing Header OPEN'].PADOCDT AS [PA Document Date],     
                      ['PA Billing Header OPEN'].PAPD AS [PA Post Date], RTRIM(['PA Billing Header OPEN'].BACHNUMB) AS [Batch Number],     
                      RTRIM(['PA Billing Header OPEN'].BCHSOURC) AS [Batch Source], RTRIM(['PA Billing Header OPEN'].TRXSORCE) AS [TRX Source],     
                      RTRIM(['PA Billing Header OPEN'].PAuserid) AS [PA User ID], ['PA Billing Header OPEN'].LSTEDTDT AS [Last Edit Date], RTRIM(['PA Billing Header OPEN'].LSTUSRED)     
                      AS [Last User to Edit], RTRIM(['PA Billing Header OPEN'].PAREFNO) AS [PA Reference Document No.], RTRIM(['PA Billing Header OPEN'].CUSTNMBR)     
                      AS [Customer Number], RTRIM(['PA Billing Header OPEN'].CSTPONBR) AS [Customer PO Number], RTRIM(['PA Billing Header OPEN'].PRBTADCD)     
                      AS [Primary Billto Address Code], RTRIM(['PA Billing Header OPEN'].PYMTRMID) AS [Payment Terms ID], RTRIM(['PA Billing Header OPEN'].PABILLFORMAT)     
                      AS [PA Bill Format], RTRIM(['PA Billing Header OPEN'].PACOMM) AS [PA Comment], RTRIM(['PA Billing Header OPEN'].CURNCYID) AS [Currency ID],     
                      RTRIM(['PA Billing Header OPEN'].PAUD1) AS [PA User Defined 1], RTRIM(['PA Billing Header OPEN'].PAUD2) AS [PA User Defined 2],     
                      ['PA Billing Header OPEN'].CASHAMNT AS [Cash Amount], RTRIM(['PA Billing Header OPEN'].CBKIDCSH) AS [Checkbook ID Cash],     
                      ['PA Billing Header OPEN'].CASHDATE AS [Cash Date], RTRIM(['PA Billing Header OPEN'].DCNUMCSH) AS [Document Number Cash],     
                      ['PA Billing Header OPEN'].CHEKAMNT AS [Check Amount], RTRIM(['PA Billing Header OPEN'].CBKIDCHK) AS [Checkbook ID Check],     
                      RTRIM(['PA Billing Header OPEN'].CHEKNMBR) AS [Check Number], ['PA Billing Header OPEN'].CHEKDATE AS [Check Date],     
                      RTRIM(['PA Billing Header OPEN'].DCNUMCHK) AS [Document Number Check], ['PA Billing Header OPEN'].CRCRDAMT AS [Credit Card Amount],     
                      RTRIM(['PA Billing Header OPEN'].CRCRDNAM) AS [Credit Card Name], RTRIM(['PA Billing Header OPEN'].RCTNCCRD) AS [Receipt Number Credit Card],     
                      ['PA Billing Header OPEN'].CRCARDDT AS [Credit Card Date], RTRIM(['PA Billing Header OPEN'].DCNUMCRD) AS [Document Number Credit Card],     
                      ['PA Billing Header OPEN'].DSCPCTAM / 100.00 AS [Discount Percent], ['PA Billing Header OPEN'].DISCAMNT AS [Discount Amount],     
                      ['PA Billing Header OPEN'].DISAVAMT AS [Discount Available Amount], ['PA Billing Header OPEN'].DISCDATE AS [Discount Date],     
                      ['PA Billing Header OPEN'].DISTKNAM AS [Discount Taken Amount], ['PA Billing Header OPEN'].DISCRTND AS [Discount Returned],     
                      ['PA Billing Header OPEN'].PABillingAmount AS [PA Billing Amount], ['PA Billing Header OPEN'].TRDISAMT AS [Trade Discount Amount],     
                      ['PA Billing Header OPEN'].PA_Freight_Amount AS [PA Freight Amount], ['PA Billing Header OPEN'].PA_Misc_Amount AS [PA Misc Amount],     
                      ['PA Billing Header OPEN'].FRTTXAMT AS [Freight Tax Amount], ['PA Billing Header OPEN'].MSCTXAMT AS [Misc Tax Amount],     
                      ['PA Billing Header OPEN'].PATOTTAX AS [PA Total Tax], ['PA Billing Header OPEN'].PABACKBILL AS [PA Backout Bill Amount],     
                      ['PA Billing Header OPEN'].BKTFRTAM AS [Backout Freight Amount], ['PA Billing Header OPEN'].BKTMSCAM AS [Backout Misc Amount],     
                      ['PA Billing Header OPEN'].DOCAMNT AS [Document Amount], ['PA Billing Header OPEN'].DUEDATE AS [Due Date],     
                      ['PA Billing Header OPEN'].APPLDAMT AS [Applied Amount], ['PA Billing Header OPEN'].PALastDateApplied AS [PA Last Date Applied],     
                      RTRIM(['PA Billing Header OPEN'].SLPRSNID) AS [Salesperson ID], RTRIM(['PA Billing Header OPEN'].SALSTERR) AS [Sales Territory],     
                      RTRIM(['PA Billing Header OPEN'].TAXSCHID) AS [Tax Schedule ID], ['PA Billing Header OPEN'].ORCOMAMT AS [Commission Amount],     
                      ['PA Billing Header OPEN'].CUTOFDAT AS [Cutoff Date], RTRIM(['PA Billing Header OPEN'].SHIPMTHD) AS [Shipping Method],     
                      RTRIM(['PA Billing Header OPEN'].MSCSCHID) AS [Misc Schedule ID], dbo.PA_FUNC_Misc_Taxable(['PA Billing Header OPEN'].MISCTXBL) AS [Misc Taxable],     
                      RTRIM(['PA Billing Header OPEN'].FRTSCHID) AS [Freight Schedule ID], dbo.PA_FUNC_Freight_Taxable(['PA Billing Header OPEN'].FRGTTXBL) AS [Freight Taxable],     
                      ['PA Billing Header OPEN'].PARetentionFeeAmount AS [PA Retention Fee Amount], ['PA Billing Header OPEN'].PARetainer_Fee_Amount AS [PA Retainer Fee Amount],     
                      ['PA Billing Header OPEN'].PAtotbillings AS [PA Total Billings], RTRIM(['PA Billing Header OPEN'].PAlastprtby) AS [PA Last Printed By],     
                      ['PA Billing Header OPEN'].PAlastprtdate AS [PA Last Printed Date], ['PA Billing Header OPEN'].ORCASAMT AS [Originating Cash Amount],     
                      ['PA Billing Header OPEN'].ORCHKAMT AS [Originating Check Amount], ['PA Billing Header OPEN'].ORCCDAMT AS [Originating Credit Card Amount],     
                      ['PA Billing Header OPEN'].ORDDLRAT AS [Originating Discount Amount], ['PA Billing Header OPEN'].ORDAVAMT AS [Originating Discount Available Amount],     
                      ['PA Billing Header OPEN'].ORTDISAM AS [Originating Trade Discount Amount], ['PA Billing Header OPEN'].ORFRTTAX AS [Originating Freight Tax Amount],     
                      ['PA Billing Header OPEN'].ORMSCTAX AS [Originating Misc Tax Amount], ['PA Billing Header OPEN'].ORBKTFRT AS [Originating Backout Freight Amount],     
                      ['PA Billing Header OPEN'].ORBKTMSC AS [Originating Backout Misc Amount], ['PA Billing Header OPEN'].ORDOCAMT AS [Originating Document Amount],     
                      ['PA Billing Header OPEN'].ORAPPAMT AS [Originating Applied Amount], ['PA Billing Header OPEN'].ORCOMAMT AS [Originating Commission Amount],     
                      ['PA Billing Header OPEN'].PAOrigBillAmount AS [PA Originating Billing Amount], ['PA Billing Header OPEN'].PAOrigFreightAmt AS [PA Originating Freight Amount],     
                      ['PA Billing Header OPEN'].PAOrigMiscAmt AS [PA Originating Misc Amount], ['PA Billing Header OPEN'].PAOrigTotalTaxAmt AS [PA Originating Total Tax],     
                      ['PA Billing Header OPEN'].PAOrigBackoutBillAmt AS [PA Originating Backout Bill Amt],     
                      ['PA Billing Header OPEN'].PAOrigRetentionFeeAmt AS [PA Originating Retention Fee Amt],     
                      ['PA Billing Header OPEN'].PAOrigRetainerFeeAmt AS [PA Originating Retainer Fee Amt],     
                      ['PA Billing Header OPEN'].PAOrigTotalBilings AS [PA Originating Total Billings],     
                      ['PA Billing Header OPEN'].PAOrigDiscTakenAmt AS [PA Originating Discount Taken Amt],     
                      ['PA Billing Header OPEN'].PAOrigDiscRetAmt AS [PA Originating Discount Returned Amt], RTRIM(['PA Billing Header OPEN'].RATETPID) AS [Rate Type ID],     
                      RTRIM(['PA Billing Header OPEN'].EXGTBLID) AS [Exchange Table ID], ['PA Billing Header OPEN'].XCHGRATE AS [Exchange Rate],     
                      ['PA Billing Header OPEN'].EXCHDATE AS [Exchange Date], RTRIM(['RM Customer MSTR'].CUSTNAME) AS [Customer Name],     
    'dgpp://DynamicsGPDrillBack/?DatabaseInstance=&ServerName=BTASGP&CompanyID=BTAS1&ProductID=0' + dbo.dgppCustomerID(1,     
                      ['PA Billing Header OPEN'].CUSTNMBR) AS [Customer Number For Drillback],     
                      'dgpp://DynamicsGPDrillBack/?DatabaseInstance=&ServerName=BTASGP&CompanyID=BTAS1&ProductID=0' + dbo.dgppSalespersonID(1,     
                      ['PA Billing Header OPEN'].SLPRSNID, 2) AS [Salesperson ID For Drillback]    
FROM         dbo.PA23100 AS ['PA Billing Header OPEN'] WITH (NOLOCK) INNER JOIN    
                      dbo.PA05100 ON ['PA Billing Header OPEN'].PADocnumber20 = dbo.PA05100.PADocnumber20    
                       --INNER JOIN dbo.PAProjects ON dbo.PA05100.PAPROJNUMBER = dbo.PAProjects.[Project Number]    
                        INNER JOIN    
                      dbo.RM00101 AS ['RM Customer MSTR'] WITH (NOLOCK) ON ['PA Billing Header OPEN'].CUSTNMBR = ['RM Customer MSTR'].CUSTNMBR        

Tags:
• 2008
• GP
• SSRS