I have been trying to write a logic to post data from two tables into daily ledger which will capture following.
1) We'll get data from previous day so we'll post data for last day for today. e.g.: on Wednesday 26 june, we'll post data of 25th june. However following exceptions are making it too complicated
1.1) if today is Monday then post data which we would have received on Friday. for example, on Monday,june 24; we'll post data of Thursday June 20 ,which was received on Friday June 21, for Friday, Saturday and Sunday. i.e. June 21, 22, 23 will be posted with same data from June 20 when procedure runs on Monday, June 24th.
1.2) if it's month end and it falls on weekend then other team send data on Friday or last working day EOD but they set the date as actual month end. For example, if it is March 2019, 30 and 31 march is weekend so posting should happen as following:
1.2.1) on Friday march 29, in morning, we'll post data for Thursday March 28.
1.2.2) On Friday march 29 EOD, we'll receive data for which they'll set date to last month date which march 31st. So when we run procedure on April 1st to close march month, we'll post data for Friday, Saturday, sunday(march 29, 30, 31). Data received on Friday is set as March 31 so this will be posted for Friday, Saturday, sunday(march 29, 30, 31).
1.3) If last month day falls on weekend and next working day is holiday. For instance, September 2nd, 2019 is holiday and 31 august and 1st sep is weekend. If proc runs on Tuesday Sep 3rd then post data entries for Aug 30, 31 and sep 1st with data which was received on Friday aug 30 EOD for which date value is set as Aug 31.
These are almost all the cases shared by business. It is too confusing but it'll be great if someone can share the logic here.
--Query to get data is:
V_SQL := 'SELECT A.ACCOUNT_TYPE, B.FIN_ELEM, A.ORG_UNIT_ID, A.GL_ACCOUNT_ID, B.CMN_COA_ID, B.PROD1, B.PROD2, B.PROD3,
SUM(CURRENT_BAL) AS CB_SUM, SUM(AVG_BAL) AS AB_SUM, B.FLAG1 FROM DAILYGL_TEST A, AL_LOOKUP B '||
'WHERE A.GL_ACCOUNT_ID = B.GL_ACCT AND A.AS_OF_DATE = '||V_DATE_PARAM||
' AND ROWNUM <=15 GROUP BY A.ACCOUNT_TYPE, B.FIN_ELEM, A.ORG_UNIT_ID, A.GL_ACCOUNT_ID,B.CMN_COA_ID, B.PROD1,
B.PROD2, B.PROD3, A.AS_OF_DATE, B.FLAG1';
--V_DATE_PARAM is date for which logic needs to written as per above conditions.
---function to insert data
IF (V_END_BAL <> 0 OR V_AVG_BAL <>0) THEN
IF V_ACCT_TYPE = 1 OR V_ACCT_TYPE = 5 OR V_ACCT_TYPE = 10 OR V_ACCT_TYPE = 90 THEN
V_FIN1 := SUBSTR(V_FIN,1,2) || '100';
DBMS_OUTPUT.PUT_LINE('POST_DAILY_LEDGER..INSIDE 2 IF IN POST_DAILY_GL');
IN_LEDGER_STAT_DAILY(V_IDENTITY_CODE,V_CONSOLIDATION_CD,V_FIN1,V_ORG_UNIT_ID,V_GL_ACCOUNT_ID,V_CMN_COA_ID,
V_PROD1, V_PROD2, V_PROD3,V_DATE,V_SWITCH * V_END_BAL);
IN_LEDGER_STAT_DAILY(V_IDENTITY_CODE,V_CONSOLIDATION_CD,V_FIN,V_ORG_UNIT_ID,V_GL_ACCOUNT_ID,V_CMN_COA_ID,
V_PROD1, V_PROD2, V_PROD3,V_DATE,V_SWITCH * V_AVG_BAL);
ELSE
IN_LEDGER_STAT_DAILY(V_IDENTITY_CODE,V_CONSOLIDATION_CD,V_FIN,V_ORG_UNIT_ID,V_GL_ACCOUNT_ID,V_CMN_COA_ID,
V_PROD1, V_PROD2, V_PROD3,V_DATE,V_SWITCH * V_END_BAL);
--This needs to be run as per above logic. so if it's sep 3rd this will run for aug 30, 31 and sep 1 as per above conditions.
--complete proc for posting is as following
PROCEDURE POST_DAILY_GL(V_RUN_DATE NUMBER DEFAULT 0) AS
V_IDENTITY_CODE CONSTANT NUMBER(6):=112233;
V_CONSOLIDATION_CD CONSTANT NUMBER(3):=100;
-- variables store result of dynamic cursor
V_SQL VARCHAR2(2500);
TYPE V_CURSOR IS REF CURSOR;
seq V_CURSOR;
V_ORG_UNIT_ID LEDGER_STAT_DLY.ORG_UNIT_ID%TYPE;
V_GL_ACCOUNT_ID LEDGER_STAT_DLY.GL_ACCOUNT_ID%TYPE;
V_CMN_COA_ID LEDGER_STAT_DLY. COMMON_COA_ID%TYPE;
V_PROD1 LEDGER_STAT_DLY.PRODUCT_1_ID%TYPE;
V_PROD2 LEDGER_STAT_DLY.PRODUCT_ID%TYPE;
V_PROD3 LEDGER_STAT_DLY.PRODUCT_3_ID%TYPE;
V_DATE DAILYGL.AS_OF_DATE%TYPE;
V_DATE_PARAM DATE;
V_END_BAL NUMBER;
V_AVG_BAL NUMBER;
V_SWITCH NUMBER(1);
V_FLAG AL_LOOKUP.FLAG1%TYPE;
V_FIN AL_LOOKUP.FIN_ELEM%TYPE;
V_FIN1 AL_LOOKUP.FIN_ELEM%TYPE;
V_ACCT_TYPE NUMBER(2);
BEGIN
IF V_RUN_DATE = 0 THEN
SELECT INSTR_AS_OF_DATE INTO V_DATE FROM AS_OF_DATE;
ELSE
V_DATE := TO_DATE(LPAD(V_RUN_DATE, 8, '0'),'mmddyyyy');
END IF;
V_DATE_PARAM := RETURN_DATE(V_DATE);
V_SQL := 'SELECT A.ACCOUNT_TYPE, B.FIN_ELEM, A.ORG_UNIT_ID, A.GL_ACCOUNT_ID, B.CMN_COA_ID, B.PROD1, B.PROD2, B.PROD3,
SUM(CURRENT_BAL) AS CB_SUM, SUM(AVG_BAL) AS AB_SUM, B.FLAG1 FROM DAILYGL_TEST A, AL_LOOKUP B '||
'WHERE A.GL_ACCOUNT_ID = B.GL_ACCT AND A.AS_OF_DATE = '||V_DATE_PARAM||
' AND ROWNUM <=15 GROUP BY A.ACCOUNT_TYPE, B.FIN_ELEM, A.ORG_UNIT_ID, A.GL_ACCOUNT_ID,B.CMN_COA_ID, B.PROD1,
B.PROD2, B.PROD3, A.AS_OF_DATE, B.FLAG1';
DBMS_OUTPUT.PUT_LINE(V_DATE_PARAM);
DBMS_OUTPUT.PUT_LINE(V_SQL);
OPEN seq FOR V_SQL using V_RUN_DATE;
LOOP
V_SWITCH := 1;
FETCH seq INTO
V_ACCT_TYPE,
V_FIN,
V_ORG_UNIT_ID,
V_GL_ACCOUNT_ID,
V_CMN_COA_ID,
V_PROD1,
V_PROD2,
V_PROD3,
V_END_BAL,
V_AVG_BAL,
V_FLAG;
EXIT WHEN seq%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('POST_DAILY_LEDGER.. OPEN SEQ..AFTER FETCH');
-- CHANGE SIGN FOR CREDITS AND ADD 2 RECORDS FOR BS
IF V_FLAG = 'Y' THEN
V_SWITCH := -1;
ELSE
V_SWITCH := 1;
END IF;
IF V_ACCT_TYPE = 5 OR V_ACCT_TYPE = 10 OR V_ACCT_TYPE = 20 OR V_ACCT_TYPE = 35 THEN
V_SWITCH := V_SWITCH * -1;
END IF;
IF (V_END_BAL <> 0 OR V_AVG_BAL <>0) THEN
IF V_ACCT_TYPE = 1 OR V_ACCT_TYPE = 5 OR V_ACCT_TYPE = 10 OR V_ACCT_TYPE = 90 THEN
V_FIN1 := SUBSTR(V_FIN,1,2) || '100';
DBMS_OUTPUT.PUT_LINE('POST_DAILY_LEDGER..INSIDE 2 IF IN POST_DAILY_GL');
IN_LEDGER_STAT_DAILY(V_IDENTITY_CODE,V_CONSOLIDATION_CD,V_FIN1,V_ORG_UNIT_ID,V_GL_ACCOUNT_ID,V_CMN_COA_ID,
V_PROD1, V_PROD2, V_PROD3,V_DATE,V_SWITCH * V_END_BAL);
IN_LEDGER_STAT_DAILY(V_IDENTITY_CODE,V_CONSOLIDATION_CD,V_FIN,V_ORG_UNIT_ID,V_GL_ACCOUNT_ID,V_CMN_COA_ID,
V_PROD1, V_PROD2, V_PROD3,V_DATE,V_SWITCH * V_AVG_BAL);
ELSE
IN_LEDGER_STAT_DAILY(V_IDENTITY_CODE,V_CONSOLIDATION_CD,V_FIN,V_ORG_UNIT_ID,V_GL_ACCOUNT_ID,V_CMN_COA_ID,
V_PROD1, V_PROD2, V_PROD3,V_DATE,V_SWITCH * V_END_BAL);
END IF;
END IF;
END LOOP;
CLOSE seq;
END POST_DAILY_GL;
