Untitled
unknown
plain_text
a year ago
7.7 kB
4
Indexable
USE [HK_PISYS_POC]
GO
/****** Object: StoredProcedure [dbo].[GetUnallocatedAcctBalBasedInvTypeNmbr] Script Date: 07/24/2024 5:12:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER Procedure [dbo].[GetUnallocatedAcctBalBasedInvTypeNmbr]
@Contract_nmbr integer,
@as_of_dt datetime,
@Acct_Flag Char(1),
@tot_unit float output,
@tot_Amt float output,
@IsBeforeAsOfDtALLTrns char(1) = NULL,
@Plan_nmbr INT = NULL,
@clnt_rltnshp_nmbr INT = NULL,
@inv_type_nmbr integer
as
Declare @Unit_cnt float,
@Unit_cnt2 float,
@gross_amt float,
@gross_amt2 float,
@rltnshp_type_nmbr smallint,
@money_type_nmbr smallint,
@product_type_nmbr int
/************************************************************************
drop proc GetUnallocatedAcctBalBasedInvTypeNmbr
This stored procedure get Total Unit and Total amount in all the Forfeiture or
Reserve and Credit Balance Accounts depend on @Acct_flag.
@Acct_flag Account
---------- -------
C Credit Balance
F Forfeiture
R Reserve
inputs: @Contract_nmbr - Contract number
@as_of_dt - retrieve data before that date
@Acct_flag -
outputs: @Tot_Unit - Total No. of Unit
@Tot_Amt - Total Amount
Created by Eric Lee, 17/08/2000 (field definition : IN009, IN010, IN011)
Change Log
Date Developer Description
------------------------------------------------------------------
17/08/2000 EL Initial
29/12/2000 EL Changed for multipal UNALLOCATED ACNT HOLDER
and Credit bal acnt holder
15/01/2001 EL Changed for account nature (cntrb or dstrb)
06/03/2001 EL Changed for Forfeiture include activity 25
20/04/2001 EL add a parameter @IsBeforeAsOfDtALLTrns
N - get the efctv date = @as_of_dt txn.
Y - Get the efctv date <= @as_of_dt txn. (Default)
03/12/2001 EL 634 Specify which Paycenter OR Plan
22/07/2002 EF 1820 Handle TFR IN/OUT activity, press policy no.
29/08/2003 PP 2317 Add CD for CREDIT BAL ACNT HOLDER
29/09/2005 EF 4244 Not to check client rltnshp type so that a/c bal is still correct if user input incorrect activity
03/11/2005 ESMOND 4065 LM - show current product's related investment fund only
23/02/2009 WC 6961 Fix the rounding problem by reordering based on the primary key
13/07/2016 CT 14748 Add FD1 for distribution
*************************************************************************/
SELECT --@clnt_rltnshp_nmbr = NULL,
@Unit_cnt = NULL,
@Unit_cnt2 = NULL,
@gross_amt = NULL,
@gross_amt2 = NULL
IF NOT @clnt_rltnshp_nmbr > 0
BEGIN
SELECT @clnt_rltnshp_nmbr = NULL
END
IF @Acct_flag = 'C'
BEGIN
SELECT @rltnshp_type_nmbr = 17 -- CREDIT BAL ACNT HOLDER
,@money_type_nmbr = NULL -- ALL Money type
END
ELSE
BEGIN
IF @Acct_flag = 'R'
BEGIN
SELECT @rltnshp_type_nmbr = 16 -- UNALLOCATED ACNT HOLDER
,@money_type_nmbr = 115 -- Reserve
END
ELSE
BEGIN
SELECT @rltnshp_type_nmbr = 16 -- UNALLOCATED ACNT HOLDER
,@money_type_nmbr = 114 -- Forfeiture
END
END
SELECT @product_type_nmbr = c.product_type_nmbr
FROM contract_vw c (NOLOCK)
WHERE c.contract_nmbr = @contract_nmbr
Create table #client_rltnshp(clnt_rltnshp_nmbr int, trns_hst_seq_nmbr smallint, activity_type_nmbr smallint, sub_trns_nmbr smallint)
insert into #client_rltnshp
SELECT DISTINCT cr.clnt_rltnshp_nmbr, th.trns_hst_seq_nmbr, th.activity_type_nmbr, th.sub_trns_nmbr
FROM trns_history_vw th (NOLOCK)
INNER JOIN client_rltnshp_vw cr (NOLOCK)
ON (th.clnt_rltnshp_nmbr = cr.clnt_rltnshp_nmbr)
WHERE cr.contract_nmbr = @contract_nmbr
AND ( (cr.clnt_rltnshp_nmbr = @clnt_rltnshp_nmbr AND @clnt_rltnshp_nmbr IS NOT NULL)
OR (@clnt_rltnshp_nmbr IS NULL))
AND cr.rltnshp_type_nmbr = @rltnshp_type_nmbr
AND (th.money_type_nmbr = @money_type_nmbr OR @Money_type_nmbr is NULL)
AND th.reversal_flg = '0'
AND ( (th.trns_hst_efctv_dt <= @as_of_dt and (@IsBeforeAsOfDtALLTrns IS NULL or @IsBeforeAsOfDtALLTrns = 'Y'))
OR (th.trns_hst_efctv_dt = @as_of_dt and @IsBeforeAsOfDtALLTrns = 'N')
)
AND ( ( cr.clnt_rltnshp_nmbr in (SELECT cad.clnt_rltnshp_nmbr
FROM cntr_alloc_dtl cad (NOLOCK)
WHERE plan_nmbr = @Plan_nmbr)
AND @rltnshp_type_nmbr = 16)
OR
( cr.clnt_rltnshp_nmbr IN (SELECT cr1.clnt_rltnshp_nmbr
FROM client_rltnshp_vw cr1 (NOLOCK)
WHERE cr1.rltnshp_type_nmbr in (17)
AND cr1.contract_nmbr = @contract_nmbr
AND cr1.prmy_client_nmbr = (SELECT TOP 1 cr2.prmy_client_nmbr
FROM cntr_alloc_dtl cad (NOLOCK)
INNER JOIN client_rltnshp_vw cr2 (NOLOCK)
ON ( cad.paycenter_nmbr = cr2.clnt_rltnshp_nmbr
OR cad.clnt_rltnshp_nmbr = cr2.clnt_rltnshp_nmbr)
WHERE cad.plan_nmbr = @Plan_nmbr
AND cr2.contract_nmbr = @contract_nmbr
ORDER BY cr2.rltnshp_type_nmbr, cr2.clnt_rltnshp_nmbr desc))
AND @rltnshp_type_nmbr <> 16)
OR @Plan_nmbr IS NULL
)
CREATE INDEX client_rltnshp_ind ON #client_rltnshp (clnt_rltnshp_nmbr, trns_hst_seq_nmbr)
SELECT thi.*
INTO #trns_history_inv_tmp
FROM trns_history_inv_vw thi (NOLOCK)
INNER JOIN #client_rltnshp cr
ON ( thi.clnt_rltnshp_nmbr = cr.clnt_rltnshp_nmbr
AND thi.trns_hst_seq_nmbr = cr.trns_hst_seq_nmbr)
ORDER BY thi.clnt_rltnshp_nmbr, thi.trns_hst_seq_nmbr, thi.inv_type_nmbr
CREATE INDEX #trns_history_inv_tmp_ind ON #trns_history_inv_tmp (clnt_rltnshp_nmbr, trns_hst_seq_nmbr, inv_type_nmbr)
/* Get all contribution transaction */
SELECT @Unit_cnt = sum(thi.Unit_cnt),
@gross_amt = sum(thi.gross_amt)
FROM #client_rltnshp cr
INNER JOIN #trns_history_inv_tmp thi
ON ( cr.clnt_rltnshp_nmbr = thi.clnt_rltnshp_nmbr
AND cr.trns_hst_seq_nmbr = thi.trns_hst_seq_nmbr)
WHERE cr.activity_type_nmbr in (25, 111, 30, 148, 259)
AND thi.inv_type_nmbr IN ( SELECT ptio.Inv_type_nmbr
FROM prdct_type_inv_opt ptio (NOLOCK)
INNER JOIN inv_type it (NOLOCK)
ON (ptio.inv_type_nmbr = it.inv_type_nmbr)
WHERE ptio.product_type_nmbr = @product_type_nmbr
AND ptio.inv_type_nmbr = @inv_type_nmbr)
-- 25 = Plcy lvl - contrib, 5 = TO CB, -- 111 = Credit Bal Contribution
-- 30 FORFEIT CONTRIBUTION
if @Unit_cnt is null
SELECT @Unit_Cnt = 0
if @gross_amt is null
SELECT @gross_amt = 0
/* Get all distribution transaction */
SELECT @Unit_cnt2 = sum(thi.Unit_cnt),
@gross_amt2 = sum(thi.gross_amt)
FROM #client_rltnshp cr
INNER JOIN #trns_history_inv_tmp thi
ON ( cr.clnt_rltnshp_nmbr = thi.clnt_rltnshp_nmbr
AND cr.trns_hst_seq_nmbr = thi.trns_hst_seq_nmbr)
WHERE cr.activity_type_nmbr in (92, 94, 163, 260, 95, 93, 68, 149, 258, 98)
AND thi.inv_type_nmbr IN ( SELECT ptio.Inv_type_nmbr
FROM prdct_type_inv_opt ptio (NOLOCK)
INNER JOIN inv_type it (NOLOCK)
ON (ptio.inv_type_nmbr = it.inv_type_nmbr)
WHERE ptio.product_type_nmbr = @product_type_nmbr
AND ptio.inv_type_nmbr = @inv_type_nmbr)
-- 94 = Plcy_Partial Dis/surr, 5 = FRM CB, 163 = Credit Bal DISTRIBUTION
-- 92 PLCY-CMPLT DIS/SURR, 94 PLCY-PARTIAL DIS/SURR, 260 UNALLOCA ACNT DSTRB,
-- 95 PRTL DSTRB/ADJUSTMENT, 93 PLCY-PRD DSTBN, PLCY ALLOCATION DISTRIBUTION
if @Unit_cnt2 is null
SELECT @Unit_Cnt2 = 0
if @gross_amt2 is null
SELECT @gross_amt2 = 0
Select @tot_unit = @unit_cnt - @unit_cnt2,
@tot_amt = @gross_amt - @gross_amt2
drop table #client_rltnshp
drop table #trns_history_inv_tmp
Editor is loading...
Leave a Comment