[Home] [Help]
29:
30: -- The flow of this routine is as follows
31: -- First clean records with status_code 'I' and update records with
32: -- status_code ='D' to NULL in the GL_LEDGER_SEGMENT_VALUES table
33: -- Detect any changes in GL_LEDGER_NORM_SEG_VALS table, then mainatain
34: -- GL_LEDGER_SEGMENT_VALUES based on these changes.
35: -- Calls routine Error_Check to makesure that the data is fine.
36:
37:
115:
116: UPDATE GL_LEDGER_SEGMENT_VALUES GLLSV
117: SET (GLLSV.START_DATE, GLLSV.END_DATE) =
118: (SELECT GLLNSV.START_DATE, GLLNSV.END_DATE
119: FROM GL_LEDGER_NORM_SEG_VALS GLLNSV
120: WHERE GLLNSV.RECORD_ID = GLLSV.PARENT_RECORD_ID)
121: WHERE GLLSV.PARENT_RECORD_ID IN
122: (SELECT GLLNSV2.RECORD_ID
123: FROM GL_LEDGERS GLL,
120: WHERE GLLNSV.RECORD_ID = GLLSV.PARENT_RECORD_ID)
121: WHERE GLLSV.PARENT_RECORD_ID IN
122: (SELECT GLLNSV2.RECORD_ID
123: FROM GL_LEDGERS GLL,
124: GL_LEDGER_NORM_SEG_VALS GLLNSV2
125: WHERE GLL.CHART_OF_ACCOUNTS_ID =
126: GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
127: AND GLLNSV2.STATUS_CODE = 'U'
128: AND GLLNSV2.REQUEST_ID =
155: SET STATUS_CODE = 'D'
156: WHERE PARENT_RECORD_ID IN
157: (SELECT RECORD_ID
158: FROM GL_LEDGERS GLL,
159: GL_LEDGER_NORM_SEG_VALS GLLNSV
160: WHERE GLL.CHART_OF_ACCOUNTS_ID =
161: GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
162: AND GLLNSV.STATUS_CODE = 'D'
163: AND GLLNSV.LEDGER_ID = GLL.LEDGER_ID);
173: -- Inserting new Ledger-Segment value assignments into the table
174: -- GL_LEDGER_SEGMENT_VALUES.
175:
176: -- The following statement inserts a record into GL_LEDGER_SEGMENT_VALUES
177: -- table for every new record in GL_LEDGER_NORM_SEG_VALS with
178: -- status_code 'I' and segment_value_type_code of 'S'.
179:
180: IF (GL_FLATTEN_SETUP_DATA.GLSTFL_DEBUG) THEN
181: GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0180',
188: ||' GL_LEDGER_SEGMENT_VALUES'
189: ||' for every record with status code I '
190: ||' and segment_value_type_code of S'
191: ||' in the table'
192: ||' GL_LEDGER_NORM_SEG_VALS ');
193: END IF;
194:
195: -- Bug 8265487 - Added GLL.IMPLICIT_ACCESS_SET_ID IS NOT NULL
196: INSERT INTO GL_LEDGER_SEGMENT_VALUES
206: GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
207: GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
208: GLLNSV.START_DATE, GLLNSV.END_DATE
209: FROM GL_LEDGERS GLL,
210: GL_LEDGER_NORM_SEG_VALS GLLNSV
211: WHERE GLL.CHART_OF_ACCOUNTS_ID =
212: GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
213: AND GLLNSV.LEDGER_ID = GLL.LEDGER_ID
214: AND GLLNSV.STATUS_CODE = 'I'
227: V2 =>'GL_LEDGER_SEGMENT_VALUES');
228: l_number_of_rows := 0;
229:
230: -- The following statement inserts a record into GL_LEDGER_SEGMENT_VALUES
231: -- table for every new record in GL_LEDGER_NORM_SEG_VALS with
232: -- status_code 'I' and segment_value_type_code of 'C'.
233:
234: If (GL_FLATTEN_SETUP_DATA.GLSTFL_DEBUG) THEN
235: GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0180',
242: ||' GL_LEDGER_SEGMENT_VALUES'
243: ||' FOR EVERY RECORD WITH STATUS CODE I '
244: ||' AND SEGMENT_VALUE_TYPE_CODE OF C'
245: ||' IN THE TABLE'
246: ||' GL_LEDGER_NORM_SEG_VALS ');
247: END IF;
248:
249: -- Obtain a shared Lock on balancing and management value set ids
250:
345: GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
346: GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
347: GLLNSV.START_DATE, GLLNSV.END_DATE
348: FROM GL_LEDGERS GLL,
349: GL_LEDGER_NORM_SEG_VALS GLLNSV,
350: GL_SEG_VAL_HIERARCHIES GLSVH
351: WHERE GLL.CHART_OF_ACCOUNTS_ID =
352: GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
353: AND GLLNSV.LEDGER_ID = GLL.LEDGER_ID
393: V2 =>'Inserting ALC ledger record(S) into'
394: ||' GL_LEDGER_SEGMENT_VALUES'
395: ||' for every source ledger '
396: ||' in the '
397: ||' GL_LEDGER_NORM_SEG_VALS table');
398: END IF;
399:
400: INSERT INTO GL_LEDGER_SEGMENT_VALUES
401: (LEDGER_ID, SEGMENT_TYPE_CODE, SEGMENT_VALUE, STATUS_CODE,
656: GLLSV.SEGMENT_VALUE) IN
657: (SELECT GLLNSV.LEDGER_ID, GLLNSV.RECORD_ID,
658: GLSVH.CHILD_FLEX_VALUE
659: FROM GL_SEG_VAL_HIERARCHIES GLSVH,
660: GL_LEDGER_NORM_SEG_VALS GLLNSV,
661: GL_LEDGERS GLL
662: WHERE GLSVH.FLEX_VALUE_SET_ID =
663: GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
664: AND GLSVH.STATUS_CODE = 'D'
717: GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
718: GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
719: GLLNSV.START_DATE, GLLNSV.END_DATE
720: FROM GL_SEG_VAL_HIERARCHIES GLSVH,
721: GL_LEDGER_NORM_SEG_VALS GLLNSV,
722: GL_LEDGERS GLL
723: WHERE GLSVH.FLEX_VALUE_SET_ID =
724: GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
725: AND GLSVH.STATUS_CODE = 'I'
819: GL_MESSAGE.FUNC_ENT(FUNC_NAME =>
820: 'GL_FLATTEN_LEDGER_SEG_VALS.Clean_Up_By_Coa');
821:
822: -- Bug 8265487 -- Should update STATUS_CODE only for completed ledgers
823: UPDATE GL_LEDGER_NORM_SEG_VALS
824: SET STATUS_CODE = NULL, request_id = NULL
825: WHERE STATUS_CODE IN ( 'I','U')
826: AND request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
827: AND LEDGER_ID IN (SELECT LEDGER_ID FROM GL_LEDGERS
840: TOKEN_NUM =>2,
841: T1 =>'NUM',
842: V1 =>TO_CHAR(L_NUMBER_OF_ROWS),
843: T2 => 'TABLE',
844: V2 => 'GL_LEDGER_NORM_SEG_VALS');
845:
846: l_number_of_rows := 0;
847:
848: UPDATE GL_LEDGER_SEGMENT_VALUES
866: -- To improve performance for bug fix # 5075776
867: l_status := 'D';
868:
869: DELETE
870: FROM GL_LEDGER_NORM_SEG_VALS
871: WHERE STATUS_CODE = l_status
872: AND LEDGER_ID IN
873: (SELECT LEDGER_ID
874: FROM GL_LEDGERS
880: TOKEN_NUM => 2,
881: T1 => 'NUM',
882: V1 => TO_CHAR(L_NUMBER_OF_ROWS),
883: T2 =>'TABLE',
884: V2 => 'GL_LEDGER_NORM_SEG_VALS');
885: l_number_of_rows := 0;
886:
887: DELETE
888: FROM GL_LEDGER_SEGMENT_VALUES