create or replace
PROCEDURE TXN_MANAGE_FEE_AND_GL_Entry(INPUT_XML IN CLOB,P_ERR_CODE OUT NUMBER,P_MSG OUT VARCHAR2)--,P_OUTPUT OUT SYS_REFCURSOR) --
AS
V_Transaction_Id NUMBER(20);
V_Is_Service_Request CHAR(1);
V_Dummy CHAR(1);
V_Template_Has_Fee CHAR(1);
V_No_GL_Template CHAR(1);
BEGIN
P_ERR_CODE :=0;
P_MSG :='Success';
SELECT xt.transactionId,xt.isServiceRequest
,xt.templateHasFee,xt.noGLTemplate
INTO V_Transaction_Id,V_Is_Service_Request
,V_Template_Has_Fee,V_No_GL_Template
FROM XMLTABLE('/getPrincipalAndFeeDetail'
PASSING XMLTYPE(INPUT_XML)
COLUMNS
isServiceRequest CHAR(1) PATH 'isServiceRequest',
transactionId VARCHAR2(20) PATH 'transactionId',
templateHasFee CHAR(1) PATH 'templateHasFee',
noGLTemplate CHAR(1) PATH 'noGLTemplate'
) xt;
IF V_Template_Has_Fee = 'Y' THEN -- Fund code detail data
BEGIN
INSERT INTO Transaction_Fee_Dtl(Transaction_Id,
Fund_Code,
Fund_Code_Type,
Fund_Description,
Fund_Code_Value,
Is_Fee_Breakup,
Is_Fee_To_Customer,
Is_Fee_External
)
SELECT V_Transaction_Id,
FUND_CODE,
FUND_CODE_TYPE,
FUND_DESCRIPTION,
CASE WHEN FUND_CODE_TYPE = 'V' AND FUND_CODE_VALUE <0.01 THEN 0.01
ELSE FUND_CODE_VALUE END,
--DECODE(FUND_CODE_TYPE,'V',IIF(FUND_CODE_VALUE <0.01,0.01,FUND_CODE_VALUE),FUND_CODE_VALUE) AS FUND_CODE_VALUE ,
IS_FEE_BREAKUP,
IS_FEE_TO_CUSTOMER,
IS_FEE_EXTERNAL
FROM TABLE(BFEE.FN_EVAL_FUND_CODE(INPUT_XML));
EXCEPTION
WHEN OTHERS THEN
P_ERR_CODE := SQLCODE;
P_MSG := SQLERRM;
ROLLBACK;
GOTO OUTPUT_S;
END;
BEGIN -- Update Total Fees
MERGE INTO Transaction_Master TM
USING(
SELECT SUM(TFD.Fund_Code_Value) AS Total_Fees,TFD.Transaction_Id
FROM Transaction_Fee_Dtl TFD
WHERE TFD.Transaction_Id = V_Transaction_Id
--AND TFD.IS_FEE_BREAKUP = 'N'
AND NVL(TFD.IS_FEE_TO_CUSTOMER,'N') = 'Y'
GROUP BY TFD.Transaction_Id
) TFEE
ON (TM.Transaction_Id = TFEE.Transaction_Id)
WHEN MATCHED THEN
UPDATE SET TM.Total_Fee = ROUND(TFEE.Total_Fees,2);
EXCEPTION
WHEN OTHERS THEN
P_ERR_CODE := SQLCODE;
P_MSG := SQLERRM;
ROLLBACK;
GOTO OUTPUT_S;
END;
END IF;
IF V_No_GL_Template = 'N' THEN -- GL Header
BEGIN
INSERT INTO Transaction_GL_Entry_H(
Transaction_Id,
FT_GL_Leg_Seq_No,
FT_GL_Leg_Header,
Is_Inst_Or_Man_Schedule,
Schedule_Id,
Is_Last_24Hr,
Schedule_To_run,
Settlement_Group_Id,
Settlement_Group_Name,
Created_By,
Created_On
)
SELECT V_Transaction_Id,
GL_Leg_Seq_No,
GL_Leg_Header,
Is_Inst_Or_Man_Schedule,
Schedule_Id,
Is_Last_24Hr,
Schedule_To_run,
Settlement_Group_Id,
BFEE.FN_GET_SETTLEMENT_GRP(Settlement_Group_Id),
1,
SYSDATE
FROM TABLE(BFEE.FN_GET_GL_HAEDER(INPUT_XML));
EXCEPTION
WHEN OTHERS THEN
P_ERR_CODE := SQLCODE;
P_MSG := SQLERRM;
ROLLBACK;
GOTO OUTPUT_S;
END;
BEGIN -- GL Line Items
INSERT INTO Transaction_GL_Entry_LI(
Transaction_GL_Entry_H_Id,
GL_Description,
GL_Account_Number,
Dr_Cr_Ind,
GL_Amount,
--Adjustment_Amount,
Is_Adjustment_GL,
IS_MAIN_ACCOUNT,
REMARKS
)
SELECT TGLH.Transaction_GL_Entry_H_Id,
EGLI.GL_Description,
EGLI.GL_Account_Number,
EGLI.Dr_Cr_Ind,
ROUND(EGLI.GL_Amount,2), --CASE WHEN EGLI.Is_Adjustment_GL = 'Y' THEN ROUND(EGLI.GL_Amount,2) - ADJC.Adj_Amt ELSE ROUND(EGLI.GL_Amount,2) END,
--CASE WHEN EGLI.Is_Adjustment_GL = 'Y' THEN ADJC.Adj_Amt ELSE NULL END
EGLI.Is_Adjustment_GL,
EGLI.IS_MAIN_ACCOUNT,
EGLI.REMARKS
FROM TABLE(BFEE.FN_EVAL_GL_ENTRY(INPUT_XML)) EGLI
INNER JOIN Transaction_GL_Entry_H TGLH ON EGLI.GL_Leg_Seq_No = TGLH.FT_GL_Leg_Seq_No
AND TGLH.Transaction_Id = V_Transaction_Id
--INNER JOIN (
/*
LEFT OUTER JOIN (
SELECT SUM(ROUND(EGLIC.GL_Amount,2)) - SUM(ROUND(EGLID.GL_Amount,2)) AS Adj_Amt
,EGLID.GL_Leg_Seq_No AS GL_Leg_Seq_No
FROM TABLE(BFEE.FN_EVAL_GL_ENTRY(INPUT_XML)) EGLIC
INNER JOIN TABLE(BFEE.FN_EVAL_GL_ENTRY(INPUT_XML)) EGLID ON EGLIC.GL_Leg_Seq_No = EGLID.GL_Leg_Seq_No
GROUP BY EGLID.GL_Leg_Seq_No
) ADJC ON EGLI.GL_Leg_Seq_No = ADJC.GL_Leg_Seq_No
*/
;
EXCEPTION
WHEN OTHERS THEN
P_ERR_CODE := SQLCODE;
P_MSG := SQLERRM;
ROLLBACK;
GOTO OUTPUT_S;
END;
BEGIN -- Update Adjustment
MERGE INTO Transaction_GL_Entry_LI TGLI
USING(
SELECT SUM(DECODE(TGLI_C.Dr_Cr_Ind,'C',TGLI_C.GL_Amount,0))
- SUM(DECODE(TGLI_C.Dr_Cr_Ind,'D',TGLI_C.GL_Amount,0)) AS Adj_Amt,TGLH.Transaction_GL_Entry_H_Id
FROM Transaction_GL_Entry_LI TGLI_C
--INNER JOIN Transaction_GL_Entry_LI TGLI_D ON TGLI_C.Transaction_GL_Entry_H_Id = TGLI_D.Transaction_GL_Entry_H_Id
INNER JOIN Transaction_GL_Entry_H TGLH ON TGLI_C.Transaction_GL_Entry_H_Id = TGLH.Transaction_GL_Entry_H_Id
--AND TGLH.Transaction_Id = V_Transaction_Id
WHERE TGLH.Transaction_Id = V_Transaction_Id
--AND TGLI_C.Dr_Cr_Ind = 'C'
--AND TGLI_D.Dr_Cr_Ind = 'D'
GROUP BY TGLH.Transaction_GL_Entry_H_Id
) TFEE
ON (TGLI.Transaction_GL_Entry_H_Id = TFEE.Transaction_GL_Entry_H_Id)
WHEN MATCHED THEN
UPDATE SET TGLI.GL_Amount = DECODE(TGLI.Is_Adjustment_GL,'Y',
TGLI.GL_Amount - TFEE.Adj_Amt*DECODE(TGLI.Dr_Cr_Ind,'C',1,-1)
,TGLI.GL_Amount),
TGLI.Adjustment_Amount = DECODE(TGLI.Is_Adjustment_GL,'Y',TFEE.Adj_Amt*DECODE(TGLI.Dr_Cr_Ind,'C',1,-1),NULL)
;
EXCEPTION
WHEN OTHERS THEN
P_ERR_CODE := SQLCODE;
P_MSG := SQLERRM;
ROLLBACK;
GOTO OUTPUT_S;
END;
/* -- Move to TXN_GET_FeeDetailES
BEGIN -- Update Customer Account
MERGE INTO Transaction_GL_Entry_LI TGLL
USING(
SELECT CASE WHEN TGLLC.Dr_Cr_Ind = 'D' THEN TM.From_Entity_Value
WHEN TGLLC.Dr_Cr_Ind = 'C' THEN TM.To_Entity_Value
END AS Cust_Ac,
TGLLC.Transaction_GL_Entry_LI_Id
FROM Transaction_GL_Entry_H TGLH
INNER JOIN Transaction_GL_Entry_LI TGLLC ON TGLH.Transaction_GL_Entry_H_Id = TGLLC.Transaction_GL_Entry_H_Id
INNER JOIN Transaction_Master TM ON TM.Transaction_Id = TGLH.Transaction_Id
WHERE TM.Transaction_Id = V_Transaction_Id
AND TGLLC.GL_Account_Number = 'CA'
) TGL
ON (TGLL.Transaction_GL_Entry_LI_Id = TGL.Transaction_GL_Entry_LI_Id)
WHEN MATCHED THEN
UPDATE SET TGLL.GL_Account_Number = TGL.Cust_Ac;
EXCEPTION
WHEN OTHERS THEN
P_ERR_CODE := SQLCODE;
P_MSG := SQLERRM;
ROLLBACK;
GOTO OUTPUT_S;
END;
*/
END IF;
COMMIT;
<<OUTPUT_S>>
SELECT 1 INTO V_DUMMY FROM DUAL;
/*
OPEN P_OUTPUT FOR
SELECT P_ERR_CODE,P_MSG
FROM DUAL;
*/
END;