[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: INSERT INTO GL_LEDGER_SEGMENT_VALUES
196: (LEDGER_ID, SEGMENT_TYPE_CODE, SEGMENT_VALUE, STATUS_CODE,
205: GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
206: GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
207: GLLNSV.START_DATE, GLLNSV.END_DATE
208: FROM GL_LEDGERS GLL,
209: GL_LEDGER_NORM_SEG_VALS GLLNSV
210: WHERE GLL.CHART_OF_ACCOUNTS_ID =
211: GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
212: AND GLLNSV.LEDGER_ID = GLL.LEDGER_ID
213: AND GLLNSV.STATUS_CODE = 'I'
224: V2 =>'GL_LEDGER_SEGMENT_VALUES');
225: l_number_of_rows := 0;
226:
227: -- The following statement inserts a record into GL_LEDGER_SEGMENT_VALUES
228: -- table for every new record in GL_LEDGER_NORM_SEG_VALS with
229: -- status_code 'I' and segment_value_type_code of 'C'.
230:
231: If (GL_FLATTEN_SETUP_DATA.GLSTFL_DEBUG) THEN
232: GL_MESSAGE.Write_Log(MSG_NAME =>'SHRD0180',
239: ||' GL_LEDGER_SEGMENT_VALUES'
240: ||' FOR EVERY RECORD WITH STATUS CODE I '
241: ||' AND SEGMENT_VALUE_TYPE_CODE OF C'
242: ||' IN THE TABLE'
243: ||' GL_LEDGER_NORM_SEG_VALS ');
244: END IF;
245:
246: -- Obtain a shared Lock on balancing and management value set ids
247:
342: GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
343: GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
344: GLLNSV.START_DATE, GLLNSV.END_DATE
345: FROM GL_LEDGERS GLL,
346: GL_LEDGER_NORM_SEG_VALS GLLNSV,
347: GL_SEG_VAL_HIERARCHIES GLSVH
348: WHERE GLL.CHART_OF_ACCOUNTS_ID =
349: GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
350: AND GLLNSV.LEDGER_ID = GLL.LEDGER_ID
390: V2 =>'Inserting ALC ledger record(S) into'
391: ||' GL_LEDGER_SEGMENT_VALUES'
392: ||' for every source ledger '
393: ||' in the '
394: ||' GL_LEDGER_NORM_SEG_VALS table');
395: END IF;
396:
397: INSERT INTO GL_LEDGER_SEGMENT_VALUES
398: (LEDGER_ID, SEGMENT_TYPE_CODE, SEGMENT_VALUE, STATUS_CODE,
653: GLLSV.SEGMENT_VALUE) IN
654: (SELECT GLLNSV.LEDGER_ID, GLLNSV.RECORD_ID,
655: GLSVH.CHILD_FLEX_VALUE
656: FROM GL_SEG_VAL_HIERARCHIES GLSVH,
657: GL_LEDGER_NORM_SEG_VALS GLLNSV,
658: GL_LEDGERS GLL
659: WHERE GLSVH.FLEX_VALUE_SET_ID =
660: GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
661: AND GLSVH.STATUS_CODE = 'D'
714: GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
715: GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
716: GLLNSV.START_DATE, GLLNSV.END_DATE
717: FROM GL_SEG_VAL_HIERARCHIES GLSVH,
718: GL_LEDGER_NORM_SEG_VALS GLLNSV,
719: GL_LEDGERS GLL
720: WHERE GLSVH.FLEX_VALUE_SET_ID =
721: GL_FLATTEN_SETUP_DATA.GLSTFL_VS_ID
722: AND GLSVH.STATUS_CODE = 'I'
815:
816: GL_MESSAGE.FUNC_ENT(FUNC_NAME =>
817: 'GL_FLATTEN_LEDGER_SEG_VALS.Clean_Up_By_Coa');
818:
819: UPDATE GL_LEDGER_NORM_SEG_VALS
820: SET STATUS_CODE = NULL, request_id = NULL
821: WHERE STATUS_CODE IN ( 'I','U')
822: AND request_id =
823: GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID;
832: TOKEN_NUM =>2,
833: T1 =>'NUM',
834: V1 =>TO_CHAR(L_NUMBER_OF_ROWS),
835: T2 => 'TABLE',
836: V2 => 'GL_LEDGER_NORM_SEG_VALS');
837:
838: l_number_of_rows := 0;
839:
840: UPDATE GL_LEDGER_SEGMENT_VALUES
858: -- To improve performance for bug fix # 5075776
859: l_status := 'D';
860:
861: DELETE
862: FROM GL_LEDGER_NORM_SEG_VALS
863: WHERE STATUS_CODE = l_status
864: AND LEDGER_ID IN
865: (SELECT LEDGER_ID
866: FROM GL_LEDGERS
872: TOKEN_NUM => 2,
873: T1 => 'NUM',
874: V1 => TO_CHAR(L_NUMBER_OF_ROWS),
875: T2 =>'TABLE',
876: V2 => 'GL_LEDGER_NORM_SEG_VALS');
877: l_number_of_rows := 0;
878:
879: DELETE
880: FROM GL_LEDGER_SEGMENT_VALUES