I did not bother to name this...

From Insensitive Mockingbird, 4 Weeks ago, written in Plain Text, viewed 21 times.
URL http://codebin.org/view/5f269f42 Embed
Download Paste or View Raw
  1. USE [fxDB6]
  2. GO
  3.  
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8.  
  9. If Object_ID('dbo.mo_sp_Transactions_List','P') Is Null
  10.         Exec('Create Procedure dbo.mo_sp_Transactions_List As Select 1')
  11. Go
  12.  
  13. /*
  14.         :Description:
  15.                 Lists money order transactions
  16.         :Database Target:
  17.                 fxDB6
  18.         :Revision  History:
  19.                 2017-09-29 DB SCR 3506911 Inital version
  20.                 2017-10-06 DB SCR 3506911 Corrected @iSource value when checking for existing entries
  21.                 2017-12-01 DB SCR 3602111 Remove items matched on salestblTransactions_ToPost and not on salestblTransactions but exist on salestblTransactions
  22.                 2017-12-06 DB SCR 3605111 Allow filtering by Status 'Unknown'
  23.                 2018-01-24 LL SCR 3588511 Add return fCommRA column. (agent per check fee)
  24.                 2018-02-08 LL SCR 3588511 Update to show absolute value if fCommRA is less than zero, and show zero if fCommRA is positive.
  25.                 2018-05-04 TH SCR 3810811 Filter by Range of Serial and StatusID From, Set @sToSerialNo to the same as SerialNo for Backward Compability,
  26.                                                                         Serial No is range, it is no longer unique.  When @sToSerialNo = @sSerialNo, it is unique
  27.                 2018-06-12 TH SCR 3810811 Fix Or Statement
  28.                 2018-06-15 TH SCR 3810811 Fix Detail Logic
  29.                 2018-12-03 JB SCR 4195211 Implement Country/State Filters + output
  30.                 2019-01-04 TH SCR 4305511 Change to Left Join to return transactions with no location ids
  31.                 2019-01-23 JB SCR 4195211 Output clear date
  32.         :Example Query:
  33.                 Select t.fTransID, Count(t.fTransID)
  34.                 From dbo.salestblTransactions_ToPost t with (nolock)
  35.                 Inner Join dbo.salestblTransactions_LineItems_ToPost p with (nolock) on t.fID = p.fID and t.fTransID = p.fTransID
  36.                 Where t.fDelete = 0 and p.fServiceID = 311 and p.fProductID = 20711 and p.fProductItemID = 2000
  37.                 Group By t.fTransID
  38.  
  39.                 Exec mo_sp_Transactions_List  @lAppID = 1, @lAppObjectID = 1, @lUserNameID = 1, @lTransID = 40125551, @iSource = 0, @bExport = 1
  40.  
  41.                 Exec mo_sp_Transactions_List  @lAppID = 1, @lAppObjectID = 1, @lUserNameID = 1, @lTransID = 40192951, @iSource = 0, @bExport = 0
  42.                 Exec mo_sp_Transactions_List  @lAppID = 1, @lAppObjectID = 1, @lUserNameID = 1, @lTransID = 40192951, @iSource = 1, @bExport = 0
  43.                 Exec mo_sp_Transactions_List  @lAppID = 1, @lAppObjectID = 1, @lUserNameID = 1, @lTransID = 40192951, @iSource = 2, @bExport = 0
  44.                 Exec mo_sp_Transactions_List  @lAppID = 1, @lAppObjectID = 1, @lUserNameID = 1, @lTransID = 40192951, @iSource = 2, @bExport = 1
  45.                 Exec mo_sp_Transactions_List  @lAppID = 1, @lAppObjectID = 1, @lUserNameID = 1, @lTransID = 40192951, @iSource = 2, @bExport = 1, @bIncludeHeaderInfo = 1
  46.                 Exec mo_sp_Transactions_List  @lAppID = 1, @lAppObjectID = 1, @lUserNameID = 1, @iSource = 1, @bExport = 1, @mFromAmt = 50, @mToAmt = 53, @bIncludeFees = 1, @lRecAGentId = 11311
  47.                 select * from salestbltransactions_lineitems where famount = 58
  48.  
  49.                 Exec mo_sp_Transactions_List  @lAppID = 1, @lAppObjectID = 1, @lUserNameID = 1, @lTransID = 40139651, @iSource = 0, @bExport = 1
  50.                
  51.                 Exec dbo.mo_sp_Transactions_List @lAppID = 16, @lAppObjectID = 12, @lUserNameID = 1122212, @iSource = 0, @bExport = 0, @iDateType = 1, @dStartDate = '12/5/2016', @dEndDate = '12/6/2017'
  52.                 select top 100 * from salestblTransactions with (nolock)
  53.                 select top 100 * from salestblTransactions_ToPost with (nolock)
  54.                 select top 100 fCommRA,  fCommRACur,  * from salestbltransactions_lineitems with (nolock) where fCommRA is not null and fCommRA > 0--= 58
  55.                 Exec dbo.mo_sp_Transactions_List  @lAppID = 16, @lAppObjectID = 12, @lUserNameID = 35719711, @iSource = 0, @bExport = 0, @iDateType = 2, @dStartDate = '1/23/2017', @dEndDate = '1/24/2018'
  56.  
  57.                 select top 100 abs(fCommRA), fCommRA,  fCommRACur,  * from salestbltransactions_lineitems with (nolock) where fCommRA is not null and fCommRA <> 0--= 58
  58.                         order by 2 asc
  59.                 Exec dbo.mo_sp_Transactions_List @lAppID = 16, @lAppObjectID = 12, @lUserNameID = 35719711, @lfID = 0, @lTransID = 40150451
  60.  
  61.                 Exec dbo.mo_sp_Transactions_List @lAppID = 16, @lAppObjectID = 12, @lUserNameID = 17278822, @iSource = 0, @bExport = 0, @iDateType = 1,
  62.         @dStartDate = '4/25/2018', @dEndDate = '4/26/2018' ,@sSerialNo = '8000005840' ,@sToSerialNo = '',@lFromStatusID = 10
  63.  
  64. select * from systblChange_Log
  65.  
  66. */
  67.  
  68. Alter Procedure dbo.mo_sp_Transactions_List
  69. (
  70.         @lAppID Int,
  71.         @lAppObjectID Int,
  72.         @lUserNameID Int,
  73.         @iSource SmallInt = 0, -- 0 Both, 1 = ToApprove, 2 = Approved
  74.         @bExport Bit = 0,
  75.         @bIncludeHeaderInfo Bit = 0,
  76.         @lServiceID Int = 311, -- Retail
  77.         @lProductID Int = 20711, -- Money Orders
  78.         @lProductItemID Int = 2000, -- Money Order GFPS
  79.         @lfID Int = 0,
  80.         @lTransID Int = 0,
  81.         @lLineID Int = 0,
  82.         @sInvoiceNo Varchar(20) = '', -- Invoice at header level
  83.         @sSerialNo Varchar(20) = '', -- Trans No at detail level
  84.         @lDepartmentID Int = 0,
  85.         @lRecAgentID Int = 0,
  86.         @lRecAgentLocID Int = 0,
  87.         @sCurrency Char(3) = '',
  88.         @lPaymentMethodID Int = 0,
  89.         @lStatusID Int = Null,
  90.         @iActionedByType SmallInt = 0,
  91.         @lActionedByID Int = 0,
  92.         @iDateType SmallInt = 0,
  93.         @dStartDate Date = Null,
  94.         @dEndDate Date = Null,
  95.         @mFromAmt Money = 0,
  96.         @mToAmt Money = 0,
  97.         @bIncludeFees Bit = 0,
  98.         @lFromStatusID Int = Null,
  99.         @sToSerialNo Varchar(20) = Null,
  100.         @sCountry Varchar(50) = Null,
  101.         @sState Varchar(50) = Null
  102. )
  103.  
  104. As
  105.  
  106. Set NoCount On;
  107.  
  108. Set @sSerialNo = Isnull(@sSerialNo, '')
  109. Set @sToSerialNo = Isnull(@sToSerialNo, @sSerialNo)
  110. Set @sCountry = Isnull(@sCountry, '')
  111. Set @sState = Isnull(@sState, '')
  112.  
  113. Create Table #info (fID Int,
  114.                                         fTransID Int, fLineID Int, fInvoiceNo Varchar(20), fSerialNo Varchar(20), fTransDate date,
  115.                                         fDepartmentID Int, Department Varchar(100), fAgentIDFrom Int, AgentName Varchar(100), fAgentLocIDFrom Int, AgentLoc Varchar(100),
  116.                                         fTotalAmount Money, fTotalFees Money, fTotalCommission Money, fTotalVendorCommission Money,
  117.                                         fAmount Money, fFee Money, fCommission Money, fVendorCommission Money,
  118.                                         fCurrency Char(3),
  119.                                         fJEID Int, fJEPosted Bit,
  120.                                         fPaymentMethod Int,
  121.                                         fEnteredBy Int, fEnteredTime DateTime, EnteredBy Varchar(100),
  122.                                         fCreatedBy Int, fCreatedTime DateTime, fCreatedLocalTime DateTime, CreatedBy Varchar(100),
  123.                                         fModifiedBy Int, fModifiedTime DateTime, ModifiedBy Varchar(100),
  124.                                         fVoid Bit, fVoidedBy Int, fVoidedTime DateTime, fVoidedLocalTime DateTime, VoidedBy Varchar(100),
  125.                                         fConfirmedBy Int, fConfirmedTime DateTime, fConfirmedLocalTime DateTime, ConfirmedBy Varchar(100),
  126.                                         fSentBy Int, fSentTime DateTime, SentBy Varchar(100),
  127.                                         fStatusID Int, Status Varchar(255), fReissuedID Int, MatchedSource SmallInt, fCommRA Money, fAgentNo Varchar(50), fKey Varchar(500),
  128.                                         fCountry Varchar(50), fState Varchar(50), fClearedDate date)
  129.  
  130. If @lfID > 0 and @iSource = 1
  131.         Set @iSource = 2
  132.        
  133. If @dEndDate Is Not Null       
  134.         Set @dEndDate = DateAdd(DD,1,@dEndDate)
  135.  
  136. -- Detail
  137. If IsNull(@lTransID,0) > 0 Or IsNull(@sInvoiceNo,'') <> '' Or (IsNull(@sSerialNo,'') <> '' And IsNull(@sSerialNo,'') = @sToSerialNo)
  138. Begin
  139.         If Exists(select 1 From  dbo.salestblTransactions t with (nolock)
  140.                 Inner Join dbo.salestblTransactions_LineItems p with (nolock) on t.fTransID = p.fTransID
  141.                 Where t.fDelete = 0 and p.fServiceID = @lServiceID and p.fProductID = @lProductID and p.fProductItemID = @lProductItemID
  142.                 And @lTransID In (0, t.fTransiD)
  143.                 And @lLineID In (0, p.fLineID)
  144.                 And @sInvoiceNo In (0, t.fInvoiceNo)
  145.                 And @sSerialNo In ('', p.fProduct_TransNo))
  146.  
  147.                         Insert Into #info (fID, fTransID, fLineID, fInvoiceNo, fSerialNo, fTransDate,
  148.                                         fDepartmentID, fAgentIDFrom, fAgentLocIDFrom,
  149.                                         fTotalAmount, fTotalFees, fTotalCommission, fTotalVendorCommission,
  150.                                         fAmount, fFee, fCommission, fVendorCommission,
  151.                                         fCurrency,
  152.                                         fJEID, fJEPosted,
  153.                                         fPaymentMethod,
  154.                                         fEnteredBy, fEnteredTime,
  155.                                         fCreatedBy, fCreatedTime, fCreatedLocalTime,
  156.                                         fModifiedBy, fModifiedTime,
  157.                                         fVoid, fVoidedBy, fVoidedTime, fVoidedLocalTime,
  158.                                         fConfirmedBy, fConfirmedTime, fConfirmedLocalTime,
  159.                                         fSentBy, fSentTime,
  160.                                         fStatusID, fReissuedID, MatchedSource,
  161.                                         fCommRA)
  162.                         Select 0, t.fTransID, p.fLineID, t.fInvoiceNo, p.fProduct_TransNo, t.fTransDate,
  163.                                         t.fDepartmentID, t.fAgentIDFrom, t.fAgentLocIDFrom,
  164.                                         t.fTransTotal, t.fLineFees, t.fCommission, t.fVendorCommission,
  165.                                         p.fAmount, p.fItemFee, p.fCommission, p.fVendorCommission,
  166.                                         t.fTransCurrency,
  167.                                         t.fJEID, t.fJEPosted,
  168.                                         t.fPaymentMethod,
  169.                                         t.fEnteredBy, t.fEnteredTime,
  170.                                         t.fCreatedBy, t.fCreatedTime, t.fCreatedLocalTime,
  171.                                         t.fModifiedBy, t.fModifiedTime,
  172.                                         p.fVoid, p.fVoidedBy, p.fVoidedTime, p.fVoidedLocalTime,
  173.                                         t.fConfirmedBy, t.fConfirmedTime, t.fConfirmedLocalTime,
  174.                                         p.fSentBy, p.fSentTime,
  175.                                         p.fStatusID, p.fTransID_Reissued, 1,
  176.                                         Case When p.fCommRA < 0 Then Abs(p.fCommRA) Else 0 End
  177.                         From dbo.salestblTransactions t with (nolock)
  178.                         Inner Join dbo.salestblTransactions_LineItems p with (nolock) on t.fTransID = p.fTransID
  179.                         Where t.fDelete = 0 and p.fServiceID = @lServiceID and p.fProductID = @lProductID and p.fProductItemID = @lProductItemID
  180.                         And @lTransID In (0, t.fTransiD)
  181.                         And @lLineID In (0, p.fLineID)
  182.                         And @sInvoiceNo In (0, t.fInvoiceNo)
  183.                         And @sSerialNo In ('', p.fProduct_TransNo)
  184.         Else   
  185.                         Insert Into #info (fID, fTransID, fLineID, fInvoiceNo, fSerialNo, fTransDate,
  186.                                         fDepartmentID, fAgentIDFrom, fAgentLocIDFrom,
  187.                                         fTotalAmount, fTotalFees, fTotalCommission, fTotalVendorCommission,
  188.                                         fAmount, fFee, fCommission, fVendorCommission,
  189.                                         fCurrency,
  190.                                         fJEID, fJEPosted,
  191.                                         fPaymentMethod,
  192.                                         fEnteredBy, fEnteredTime,
  193.                                         fCreatedBy, fCreatedTime, fCreatedLocalTime,
  194.                                         fModifiedBy, fModifiedTime,
  195.                                         fVoid, fVoidedBy, fVoidedTime, fVoidedLocalTime,
  196.                                         fConfirmedBy, fConfirmedTime, fConfirmedLocalTime,
  197.                                         fSentBy, fSentTime,
  198.                                         fStatusID, fReissuedID, MatchedSource,
  199.                                         fCommRA)               
  200.                         Select t.fID, t.fTransID, p.fLineID, t.fInvoiceNo, p.fProduct_TransNo, t.fTransDate,
  201.                                         t.fDepartmentID, t.fAgentIDFrom, t.fAgentLocIDFrom,
  202.                                         t.fTransTotal, t.fLineFees, t.fCommission, t.fVendorCommission,
  203.                                         p.fAmount, p.fItemFee, p.fCommission, p.fVendorCommission,
  204.                                         t.fTransCurrency,
  205.                                         t.fJEID, t.fJEPosted,
  206.                                         t.fPaymentMethod,
  207.                                         t.fEnteredBy, t.fEnteredTime,
  208.                                         t.fCreatedBy, t.fCreatedTime, t.fCreatedLocalTime,
  209.                                         t.fModifiedBy, t.fModifiedTime,
  210.                                         p.fVoid, p.fVoidedBy, p.fVoidedTime, p.fVoidedLocalTime,
  211.                                         t.fConfirmedBy, t.fConfirmedTime, t.fConfirmedLocalTime,
  212.                                         p.fSentBy, p.fSentTime,
  213.                                         p.fStatusID, p.fTransID_Reissued, 2,
  214.                                         Case When p.fCommRA < 0 Then Abs(p.fCommRA) Else 0 End
  215.                         From dbo.salestblTransactions_ToPost t with (nolock)
  216.                         Inner Join dbo.salestblTransactions_LineItems_ToPost p with (nolock) on t.fID = p.fID and t.fTransID = p.fTransID
  217.                         Where t.fDelete = 0 and p.fServiceID = @lServiceID and p.fProductID = @lProductID and p.fProductItemID = @lProductItemID
  218.                         And (@iSource = 2 Or (@iSource = 0 and t.fTransID Not In (Select fTransID From #info)))
  219.                         And @lTransID In (0, t.fTransiD)
  220.                         And @lLineID In (0, p.fLineID)
  221.                         And @sInvoiceNo In (0, t.fInvoiceNo)
  222.                         And @sSerialNo In ('', p.fProduct_TransNo)
  223.  
  224.         Goto updateLookups
  225. End
  226.  
  227. If @iSource In (0, 1)
  228. Begin
  229.         Insert Into #info (fID, fTransID, fLineID, fInvoiceNo, fSerialNo, fTransDate,
  230.                         fDepartmentID, fAgentIDFrom, fAgentLocIDFrom,
  231.                         fTotalAmount, fTotalFees, fTotalCommission, fTotalVendorCommission,
  232.                         fAmount, fFee, fCommission, fVendorCommission,
  233.                         fCurrency, fJEID, fJEPosted,
  234.                         fPaymentMethod,
  235.                         fEnteredBy, fEnteredTime,
  236.                         fCreatedBy, fCreatedTime, fCreatedLocalTime,
  237.                         fModifiedBy, fModifiedTime,
  238.                         fVoid, fVoidedBy, fVoidedTime, fVoidedLocalTime,
  239.                         fConfirmedBy, fConfirmedTime, fConfirmedLocalTime,
  240.                         fSentBy, fSentTime,
  241.                         fStatusID, fReissuedID, MatchedSource,
  242.                         fCommRA)
  243.         Select 0, t.fTransID, p.fLineID, t.fInvoiceNo, p.fProduct_TransNo, t.fTransDate,
  244.                         t.fDepartmentID, t.fAgentIDFrom, t.fAgentLocIDFrom,
  245.                         t.fTransTotal, t.fLineFees, t.fCommission, t.fVendorCommission,
  246.                         p.fAmount, p.fItemFee, p.fCommission, p.fVendorCommission,
  247.                         t.fTransCurrency, t.fJEID, t.fJEPosted,
  248.                         t.fPaymentMethod,
  249.                         t.fEnteredBy, t.fEnteredTime,
  250.                         t.fCreatedBy, t.fCreatedTime, t.fCreatedLocalTime,
  251.                         t.fModifiedBy, t.fModifiedTime,
  252.                         p.fVoid, p.fVoidedBy, p.fVoidedTime, p.fVoidedLocalTime,
  253.                         t.fConfirmedBy, t.fConfirmedTime, t.fConfirmedLocalTime,
  254.                         p.fSentBy, p.fSentTime,
  255.                         p.fStatusID, p.fTransID_Reissued, 1,
  256.                         Case When p.fCommRA < 0 Then Abs(p.fCommRA) Else 0 End
  257.         From dbo.salestblTransactions t with (nolock)
  258.         Inner Join dbo.salestblTransactions_LineItems p with (nolock) on t.fTransID = p.fTransID
  259.         Left Join dbo.lttblPayAgentsLocs l with (nolock) on t.fAgentLocIDFrom = l.fNameIDLoc and t.fAgentIDFrom = l.fNameIDAgent
  260.         Where t.fDelete = 0
  261.         And p.fServiceID = @lServiceID and p.fProductID = @lProductID and p.fProductItemID = @lProductItemID
  262.         And @sCountry In ('', l.fCountry)
  263.         And @sState In ('', l.fState)
  264.        
  265. End
  266.  
  267. If @iSource In (0, 2)
  268. Begin
  269.         Insert Into #info (fID, fTransID, fLineID, fInvoiceNo, fSerialNo, fTransDate,
  270.                         fDepartmentID, fAgentIDFrom, fAgentLocIDFrom,
  271.                         fTotalAmount, fTotalFees, fTotalCommission, fTotalVendorCommission,
  272.                         fAmount, fFee, fCommission, fVendorCommission,
  273.                         fCurrency, fJEID, fJEPosted,
  274.                         fPaymentMethod,
  275.                         fEnteredBy, fEnteredTime,
  276.                         fCreatedBy, fCreatedTime, fCreatedLocalTime,
  277.                         fModifiedBy, fModifiedTime,
  278.                         fVoid, fVoidedBy, fVoidedTime, fVoidedLocalTime,
  279.                         fConfirmedBy, fConfirmedTime, fConfirmedLocalTime,
  280.                         fSentBy, fSentTime,
  281.                         fStatusID, fReissuedID, MatchedSource,
  282.                         fCommRA)
  283.         Select @lStatusID, t.fTransID, p.fLineID, t.fInvoiceNo, p.fProduct_TransNo, t.fTransDate,
  284.                         t.fDepartmentID, t.fAgentIDFrom, t.fAgentLocIDFrom,
  285.                         t.fTransTotal, t.fLineFees, t.fCommission, t.fVendorCommission,
  286.                         p.fAmount, p.fItemFee, p.fCommission, p.fVendorCommission,
  287.                         t.fTransCurrency, t.fJEID, t.fJEPosted,
  288.                         t.fPaymentMethod,
  289.                         t.fEnteredBy, t.fEnteredTime,
  290.                         t.fCreatedBy, t.fCreatedTime, t.fCreatedLocalTime,
  291.                         t.fModifiedBy, t.fModifiedTime,
  292.                         p.fVoid, p.fVoidedBy, p.fVoidedTime, p.fVoidedLocalTime,
  293.                         t.fConfirmedBy, t.fConfirmedTime, t.fConfirmedLocalTime,
  294.                         p.fSentBy, p.fSentTime,
  295.                         p.fStatusID, p.fTransID_Reissued, 2,
  296.                         Case When p.fCommRA < 0 Then Abs(p.fCommRA) Else 0 End
  297.         From dbo.salestblTransactions_ToPost t with (nolock)
  298.         Inner Join dbo.salestblTransactions_LineItems_ToPost p with (nolock) on t.fID = p.fID and t.fTransID = p.fTransID
  299.         Left Join dbo.lttblPayAgentsLocs l with (nolock) on t.fAgentLocIDFrom = l.fNameIDLoc and t.fAgentIDFrom = l.fNameIDAgent
  300.         Where (@iSource = 2 Or (@iSource = 0 and t.fTransID Not In (Select fTransID From #info))) And t.fDelete = 0
  301.         And p.fServiceID = @lServiceID and p.fProductID = @lProductID and p.fProductItemID = @lProductItemID
  302.         And @sCountry In ('', l.fCountry)
  303.         And @sState In ('', l.fState)
  304.  
  305.  
  306.         -- Remove any records where the transaction matched on salestblTransactions_LineItems_ToPost but exists in salestblTransactions_LineItems as well
  307.         -- This is because statuses may differ and the wrong value may. This applies to list type 'All' only
  308.         If @iSource = 0
  309.                 Delete i
  310.                 From #info i Inner Join salestblTransactions_LineItems s with (nolock) on i.fSerialNo = s.fProduct_TransNo
  311.                 Where MatchedSource = 2
  312. End
  313.  
  314. Delete From #info Where Not Exists (Select * From #info Where @lDepartmentID In (0, fDepartmentID))
  315. Delete From #info Where Not Exists (Select * From #info Where @lRecAgentID In (0, fAgentIDFrom))
  316. Delete From #info Where Not Exists (Select * From #info Where @lRecAgentLocID In (0, fAgentLocIDFrom))
  317. Delete From #info Where Not Exists (Select * From #info Where @sCurrency In ('', fCurrency))
  318. Delete From #info Where Not Exists (Select * From #info Where @lPaymentMethodID In (0, fPaymentMethod))
  319. Delete From #info Where Not Exists (Select * From #info Where @lStatusID Is Null Or fStatusID = @lStatusID)
  320.  
  321. Delete From #info Where Not Exists (Select * From #info Where @lActionedByID = 0 Or (@lActionedByID > 0 And Case @iActionedByType
  322.                 --When 1 Then fEnteredBy
  323.                 When 2 Then fVoidedBy
  324.                 When 3 Then fConfirmedBy
  325.                 When 4 Then fSentBy
  326.         Else fEnteredBy
  327.         End = @lActionedByID))
  328. Delete From #info Where Not Exists (Select * From #info Where @dStartDate Is Null Or Case @iDateType
  329.                 --When 1 Then fEnteredTime
  330.                 When 2 Then fVoidedTime
  331.                 When 3 Then fConfirmedTime
  332.                 When 4 Then fSentTime
  333.         Else
  334.                 fEnteredTime
  335.         End >= @dStartDate)
  336. Delete From #info Where Not Exists (Select * From #info Where @dEndDate Is Null Or Case @iDateType
  337.                 --When 1 Then fEnteredTime
  338.                 When 2 Then fVoidedTime
  339.                 When 3 Then fConfirmedTime
  340.                 When 4 Then fSentTime
  341.         Else
  342.                 fEnteredTime
  343.         End < @dEndDate)
  344. Delete From #info Where Not Exists (Select * From #info Where Not @mFromAmt > 0 Or
  345.         (@mFromAmt > 0 and (@bIncludeFees = 0 and fAmount >= @mFromAmt) Or (@bIncludeFees = 1 and fAmount + IsNull(fFee, 0) >= @mFromAmt)))
  346. Delete From #info Where Not Exists (Select * From #info Where Not @mToAmt > 0 Or
  347.         (@mToAmt > 0 and (@bIncludeFees = 0 and fAmount <= @mToAmt) Or (@bIncludeFees = 1 and fAmount + IsNull(fFee,0) <= @mToAmt)))
  348. Delete From #info Where Not Exists (Select * From #info Where (@sSerialNo = '' And @sToSerialNo = '') Or (@sToSerialNo = '' And fSerialNo >= @sSerialNo)
  349.         Or (@sSerialNo = '' And fSerialNo <= @sToSerialNo) Or (fSerialNo Between @sSerialNo And @sToSerialNo))
  350.        
  351. Delete From #info Where ('8888888691' = '' And '8888888694' = '') Or ('8888888694' = '' And fSerialNo >= '8888888691')
  352.         Or ('8888888691' = '' And fSerialNo <= '8888888694') Or (fSerialNo Between '8888888691' And '8888888694'))
  353.  
  354.         Select * From #info Where ('8888888691' = '' And '8888888694' = '') Or ('8888888694' = '' And fSerialNo >= '8888888691')
  355.         Or ('8888888691' = '' And fSerialNo <= '8888888694') Or (fSerialNo Between '8888888691' And '8888888694')
  356.  
  357. updateLookups:
  358.  
  359. If @lFromStatusID Is Not Null
  360. Begin
  361.         Declare @sStatusFrom Varchar(10), @sStatusTo Varchar(10)
  362.         Set @sStatusFrom = Convert(Varchar(10), @lFromStatusID)
  363.         Set @sStatusTo = Convert(Varchar(10), @lStatusID)
  364.  
  365.         Update #info Set fKey = 'fTransID=' + Convert(Varchar(20), fTransID) + ';fLineID=' + Convert(Varchar(10), fLineID) + ';'
  366.  
  367.         Delete From #info
  368.                 Where Not Exists (Select 1 From systblChange_Log l With (Nolock)
  369.                                                         Where l.fKey = #info.fKey
  370.                                                         And l.fOLDvalue = @sStatusFrom
  371.                                                         And (@lStatusID Is Null Or l.fNewValue = @sStatusTo)
  372.                                                         And l.fFieldName = 'fStatusID')
  373. End
  374.  
  375. Update t
  376. Set AgentName = n.fName, fAgentNo = n.fNameNo  
  377. From #info t Inner Join dbo.contblNames n with (nolock) on t.fAgentIDFrom = n.fNameID
  378.  
  379. Update t
  380. Set AgentLoc = l.fName, fCountry = l.fCountry, fState = l.fState
  381. From #info t Inner Join dbo.lttblPayAgentsLocs l with (nolock) on t.fAgentLocIDFrom = l.fNameIDLoc Where t.fAgentLocIDFrom > 0
  382.  
  383. Update t
  384. Set Status = c.fName
  385. From #info t Inner Join dbo.systblConst2 c with (nolock) on t.fStatusID = c.fKey2 Where c.fKey1 = 92000
  386.  
  387. Update t
  388. Set EnteredBy = n.fName
  389. From #info t Inner Join dbo.contblNames n with (nolock) on t.fEnteredBy = n.fNameID
  390.  
  391. Update t
  392. Set CreatedBy = n.fName
  393. From #info t Inner Join dbo.contblNames n with (nolock) on t.fCreatedBy = n.fNameID
  394.  
  395. Update t
  396. Set ConfirmedBy = n.fName
  397. From #info t Inner Join dbo.contblNames n with (nolock) on t.fConfirmedBy = n.fNameID
  398.  
  399. Update t
  400. Set VoidedBy = n.fName
  401. From #info t Inner Join dbo.contblNames n with (nolock) on t.fVoidedBy = n.fNameID
  402.  
  403. Update t
  404. Set SentBy = n.fName
  405. From #info t Inner Join dbo.contblNames n with (nolock) on t.fSentBy = n.fNameID
  406.  
  407. Update t
  408. Set fClearedDate = d.fClearedDate
  409. From #info t Left Join dbo.motblClearingFile_Import_Detail d with (nolock) on t.fSerialNo = d.fSerialNo
  410.  
  411. If @bExport = 1
  412. Begin
  413.         Update t
  414.         Set Department = d.fName
  415.         From #info t Inner Join dbo.actblDepartment d with (nolock) on t.fDepartmentID = d.fDepartmentID
  416. End
  417.  
  418. If @bIncludeHeaderInfo = 0
  419.         Select fID, fTransID, fLineID, fSerialNo, fTransDate,
  420.                         Department, AgentName, AgentLoc,
  421.                         fAmount, fFee, fCommission, fVendorCommission,
  422.                         fCurrency,
  423.                         fPaymentMethod,
  424.                         fEnteredTime, EnteredBy,
  425.                         fCreatedTime, fCreatedLocalTime, CreatedBy,
  426.                         fVoid, fVoidedTime, fVoidedLocalTime, VoidedBy,
  427.                         fConfirmedTime, fConfirmedLocalTime, ConfirmedBy,
  428.                         fSentTime, SentBy,
  429.                         fStatusID, Status, fReissuedID, fCommRA,
  430.                         fAgentNo, fAgentIDFrom As fAccountID,
  431.                         fCountry, fState, fClearedDate
  432.         From #info
  433.         Order by fTransID, fLineID
  434. Else
  435.         Select * From #info Order By fTransID, fLineID
  436.  
  437. Go
  438. Grant Execute On dbo.mo_sp_Transactions_List To [fxClient_Role]
  439. Go

Reply to "I did not bother to name this..."

Here you can reply to the paste above