Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2008
    Posts
    2

    Unanswered: change sentreceiveReport view into the procedure

    hi i am working in mnc using sql server 2000 i have to change a view named SentReceivedReport. We want to change this view to a stored procedure so that it accepts the required parameters and returns only the required results based on the parameters.

    view is
    CREATE VIEW SentReceivedReport
    AS
    Select
    aac.VoucherNumber as VoucherNumber,
    PayinDateTime SRTRDate,
    td.TransactionNumber,
    'Sent '+td.TransactionNumber as Particulars,
    cd.CFName + ' ' + cd.CLName as Remitter,
    cd.BFName + ' ' + cd.BLName as Beneficiary,
    Commission,
    PayinAmount Amount,
    Commission+PayinAmount TotalAmount,
    0 AmountPaid ,
    Sam.Name as AgentAccountName,
    Sam.ID as AgentAccountID,
    Salloc.Name as AgentName,
    Salloc.ID as AgentID,
    td.Status
    from TransactionDetails td
    join CustomerDetails cd on cd.TransactionID=td.ID
    join AgentLocationList Salloc on Salloc.ID=td.PayinAgentLocationID
    join AgentMaster Sam on Sam.ID=Salloc.AgentAccountID
    join AgentAccountDetails aac on aac.TransactionID=td.ID and sam.ID = aac.AgentAccountID
    where td.status not like 'CancelledWithoutRefund'
    and aac.CreditDateTime IS NULL

    UNION

    Select
    aac.VoucherNumber as VoucherNumber,
    PayOutDateTime SRTRDate,
    td.TransactionNumber,
    'Paid '+td.TransactionNumber as Particulars,
    cd.CFName + ' ' + cd.CLName as Remitter,
    cd.BFName + ' ' + cd.BLName as Beneficiary,
    0 Commission,
    0 Amount,
    0 TotalAmount,
    PayoutAmount AmountPaid ,
    Ram.Name as AgentAccountName,
    Ram.ID as AgentAccountID,
    Ralloc.Name as AgentName,
    Ralloc.ID as AgentID,
    td.Status
    from TransactionDetails td
    join CustomerDetails cd on cd.TransactionID = td.ID
    join AgentLocationList Ralloc on Ralloc.ID=td.ActualPayoutAgentID
    join AgentMaster Ram on Ram.ID=Ralloc.AgentAccountID
    join AgentAccountDetails aac on aac.TransactionID=td.ID and aac.AgentAccountid = ram.ID
    where td.status not like 'CancelledWithoutRefund'
    and aac.DebitDateTime IS NULL


    plz help me plzzzzzzzzzz
    Last edited by ravijoshi20; 06-15-08 at 07:49.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    what are your parameters?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2008
    Posts
    2
    my pararmeters is agent acct, agent branches, frm date, to date

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •