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
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