jeudi 18 août 2016

Insert duplicate record with max + 1 entry_sr_no

I have a SP which returns me the Maximum entry no for respective document no.

Now what I want is, I want to insert the same record in the inward_doc_tracking_trl table with max + 1 entry_sr_no

Let say,

Max_Entry_Srno of doc_no = IW/16/5694 is 3, then while inserting the same in trl table the Max_Entry_Srno should be 4.

Here is my SP

 ALTER procedure set_auto_action  
       as   
        begin         
             select hdr.mkey, hdr.doc_no, MAX(trl.entry_sr_no) as Max_Entry_Srno  
              from inward_doc_tracking_hdr hdr  
               JOIN inward_doc_tracking_trl trl  
             ON(hdr.mkey = trl.ref_mkey)  
              where hdr.status_flag     not in (5,13)   
             and trl.U_Datetime < DATEADD(DAY, -1, GETDATE())       
             GROUP BY hdr.mkey, hdr.doc_no  
       end

Also see the screenshot for the result returned by the SP

Screenshot

The table script of inward_doc_tracking_trl is also below:-

CREATE TABLE [dbo].[Inward_Doc_Tracking_Trl](
[Mkey] [numeric](18, 0) NOT NULL,
[Entry_Sr_No] [numeric](4, 0) NOT NULL,
[N_UserMkey] [numeric](10, 0) NULL,
[N_Department] [numeric](10, 0) NULL,
[CStatus_Flag] [numeric](8, 0) NOT NULL,
[Remarks] [varchar](500) NULL,
[CUser_ID] [numeric](10, 0) NOT NULL,
[U_Datetime] [datetime] NOT NULL,
[NStatus_Flag] [numeric](10, 0) NOT NULL, 
[Delete_Flag] [char](1) NULL,
[CDept_Id] [numeric](10, 0) NOT NULL,
[Ref_Mkey] [numeric](18, 0) NULL,
[No_Of_Days] [int] NULL,
[Approved_Amount] [float] NULL,
[Chq_No] [varchar](50) NULL,
[Chq_dated] [datetime] NULL,
[Chq_Bank] [varchar](40) NULL,
[Chq_Amount] [float] NULL,
[Vendor_MKey] [int] NULL,
[Vendor_Comp_Mkey] [int] NULL,
[Project_Mkey] [numeric](10, 0) NULL,
[Program_mkey] [numeric](10, 0) NULL,
[Payment_MKey] [int] NULL,
[Due_Date] [datetime] NULL,
[Updated_Remarks] [varchar](500) NULL,
[Updated_Bill_no] [varchar](27) NULL,
[Updated_Bill_Date] [datetime] NULL,
[Updated_Bill_Amt] [float] NULL,
[Party_Name] [varchar](80) NULL,
[Acc_mkey] [numeric](10, 0) NULL,
[TotalDeductions] [float] NULL,
[Broker_Mkey] [numeric](10, 0) NULL,
[Customer_Mkey] [numeric](10, 0) NULL,
[Payable_Amt] [float] NULL,
[Balance_Amt] [float] NULL,
[Receipt_No] [varchar](50) NULL,
[Po_No] [varchar](50) NULL,
[Bill_No] [varchar](50) NULL,
[Disp_through] [varchar](50) NULL,
[Disp_Through_Name] [varchar](100) NULL,
[Site_Id] [int] NULL,
   CONSTRAINT [PK_Inward_Doc_Tracking_Trl_1] PRIMARY KEY CLUSTERED 
  (
   [Mkey] ASC,
   [Entry_Sr_No] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =       OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

NOTE: The reason behind adding the duplicate row with max+ 1 sr_no is to let user know the document is received by that user

Aucun commentaire:

Enregistrer un commentaire