APPS.GL_FLATTEN_LEDGER_SETS SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 42

    DELETE from GL_LEDGER_SET_ASSIGNMENTS
    WHERE status_code = 'I'
    AND   ledger_set_id IN
	  (SELECT ledger_id
	   FROM	  GL_LEDGERS
	   WHERE  object_type_code = 'S'
 	   AND 	  chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);

Line: 70

    UPDATE GL_LEDGER_SET_ASSIGNMENTS
    SET	   status_code = NULL
    WHERE  status_code = 'D'
    AND	   ledger_set_id IN
	   (SELECT ledger_id
	    FROM   GL_LEDGERS
            WHERE  object_type_code = 'S'
            AND    chart_of_accounts_id =
			GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);

Line: 106

    DELETE from GL_ACCESS_SET_NORM_ASSIGN
    WHERE status_code = 'I'
    AND   access_set_id IN
	  (SELECT implicit_access_set_id
	   FROM	  GL_LEDGERS
	   WHERE  object_type_code = 'S'
 	   AND 	  chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);

Line: 136

         v2        => 'Insert access information into ' ||
		      'GL_ACCESS_SET_NORM_ASSIGN ' ||
		      'for new ledger sets...');

Line: 141

    INSERT INTO GL_ACCESS_SET_NORM_ASSIGN
    (access_set_id, ledger_id, all_segment_value_flag,
     segment_value_type_code, access_privilege_code, status_code,
     record_id, link_id, last_update_date, last_updated_by,
     last_update_login, creation_date, created_by, request_id,
     segment_value, start_date, end_date)
    (SELECT distinct
 	    gll.implicit_access_set_id, gllsna.ledger_set_id, 'Y',
	    'S', 'B', 'I', -1,
	    NULL, SYSDATE, GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
	    GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
	    GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
	    GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID,
	    NULL, gllsna.start_date, gllsna.end_date
     FROM   GL_LEDGER_SET_NORM_ASSIGN gllsna,
	    GL_LEDGERS gll
     WHERE  gllsna.status_code = 'I'
     AND    gllsna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
     AND    gll.ledger_id = gllsna.ledger_set_id
     AND    gll.chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
     AND    gll.automatically_created_flag = 'N'
     AND    NOT EXISTS
	    (SELECT 1
	     FROM 	GL_ACCESS_SET_NORM_ASSIGN glasna
	     WHERE	glasna.access_set_id =
				gll.implicit_access_set_id
  	     AND	glasna.ledger_id = gllsna.ledger_set_id
	     AND	glasna.access_privilege_code = 'B'
 	     AND	glasna.all_segment_value_flag = 'Y'
	     AND	glasna.segment_value_type_code = 'S'
 	     AND	glasna.segment_value is NULL
   	     AND	NVL(glasna.status_code, 'X') <> 'D'));

Line: 198

    UPDATE GL_ACCESS_SET_NORM_ASSIGN glasna
    SET	glasna.record_id = GL_ACCESS_SET_NORM_ASSIGN_S.nextval
    WHERE glasna.status_code = 'I'
    AND   glasna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
    AND	  glasna.record_id = -1
    AND   glasna.access_set_id IN
	  (SELECT gll.implicit_access_set_id
	   FROM   GL_LEDGERS gll
	   WHERE  gll.chart_of_accounts_id =
			GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
	   AND    gll.automatically_created_flag = 'N'
	   AND	  gll.object_type_code = 'S');

Line: 243

	 	      'in GL_LEDGER_SET_ASSIGNMENTS for delete...');

Line: 252

		      'deleted links ' ||
		      'for delete in GL_LEDGER_SET_ASSIGNMENTS...');

Line: 256

    UPDATE GL_LEDGER_SET_ASSIGNMENTS gllsa1
    SET	   gllsa1.status_code = 'D'
    WHERE  NVL(gllsa1.status_code, 'X') <> 'D'
    AND	   (gllsa1.ledger_set_id, gllsa1.ledger_id) IN
	   (SELECT distinct gllsa2.ledger_set_id, gllsna.ledger_id
  	    FROM   GL_LEDGER_SET_NORM_ASSIGN gllsna,
		   GL_LEDGERS gll,
		   GL_LEDGER_SET_ASSIGNMENTS gllsa2
	    WHERE  gllsna.status_code = 'D'
	    AND	   gllsna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
	    AND	   gll.ledger_id = gllsna.ledger_set_id
   	    AND	   gll.chart_of_accounts_id =
			GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
 	    AND	   gll.automatically_created_flag = 'N'
	    AND	   gll.object_type_code = 'S'
 	    AND	   gllsa2.ledger_id = gllsna.ledger_set_id)
    AND    gllsa1.ledger_set_id <> gllsa1.ledger_id;

Line: 290

         v2        => 'Second, mark descendants of deleted ledger sets ' ||
		      'for delete in GL_LEDGER_SET_ASSIGNMENTS...');

Line: 294

    UPDATE GL_LEDGER_SET_ASSIGNMENTS gllsa1
    SET	   gllsa1.status_code = 'D'
    WHERE  NVL(gllsa1.status_code, 'X') <> 'D'
    AND	   (gllsa1.ledger_set_id, gllsa1.ledger_id) IN
	   (SELECT distinct gllsa2.ledger_set_id, gllsa3.ledger_id
	    FROM   GL_LEDGER_SET_NORM_ASSIGN gllsna,
		   GL_LEDGERS gll,
		   GL_LEDGER_SET_ASSIGNMENTS gllsa2,
		   GL_LEDGER_SET_ASSIGNMENTS gllsa3
  	    WHERE  gllsna.status_code = 'D'
	    AND	   gllsna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
	    AND	   gll.ledger_id = gllsna.ledger_set_id
	    AND    gll.chart_of_accounts_id =
			GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
	    AND	   gll.automatically_created_flag = 'N'
	    AND	   gll.object_type_code = 'S'
    	    AND	   gllsa2.ledger_id = gllsna.ledger_set_id
 	    AND    gllsa3.ledger_set_id = gllsna.ledger_id)
    AND	   gllsa1.ledger_set_id <> gllsa1.ledger_id;

Line: 333

         v2        => 'Third, reconnect all deleted mappings in ' ||
		      'GL_LEDGER_SET_ASSIGNMENTS that are included via ' ||
		      'other effective paths...');

Line: 340

      UPDATE GL_LEDGER_SET_ASSIGNMENTS gllsa1
      SET    gllsa1.status_code = NULL
      WHERE  gllsa1.status_code = 'D'
      AND    gllsa1.ledger_set_id IN
	     (SELECT gll.ledger_id
 	      FROM   GL_LEDGERS gll
	      WHERE  gll.chart_of_accounts_id =
			GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
	      AND    gll.object_type_code = 'S'
	      AND    gll.automatically_created_flag = 'N')
      AND    (	  EXISTS
	     		(SELECT 1
	      		 FROM   GL_LEDGER_SET_ASSIGNMENTS gllsa2,
		     		GL_LEDGER_SET_ASSIGNMENTS gllsa3
	      		 WHERE  gllsa2.status_code is NULL
	      		 AND    gllsa2.ledger_id = gllsa1.ledger_id
	      		 AND    gllsa3.status_code is NULL
	      		 AND    gllsa3.ledger_set_id = gllsa1.ledger_set_id
	      		 AND    gllsa3.ledger_id = gllsa2.ledger_set_id)
	      OR  EXISTS
			(SELECT 1
			 FROM 	GL_LEDGER_SET_NORM_ASSIGN gllsna
			 WHERE	gllsna.ledger_set_id = gllsa1.ledger_set_id
			 AND	gllsna.ledger_id = gllsa1.ledger_id
			 AND	gllsna.status_code is NULL));

Line: 396

         v2        => 'Inserting self mapping record for new ledger sets ' ||
		      'into GL_LEDGER_SET_ASSIGNMENTS');

Line: 400

    INSERT INTO GL_LEDGER_SET_ASSIGNMENTS
    (ledger_set_id, ledger_id, status_code, last_update_date,
     last_updated_by, last_update_login, creation_date,
     created_by, start_date, end_date)
    (SELECT distinct gll.ledger_id, gll.ledger_id, 'I', SYSDATE,
		     GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
		     GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
		     SYSDATE, GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
		     NULL, NULL
     FROM   GL_LEDGERS gll
     WHERE  gll.object_type_code = 'S'
     AND    gll.chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
     AND    gll.automatically_created_flag = 'N'
     AND    NOT EXISTS
		(SELECT 1
		 FROM 	GL_LEDGER_SET_ASSIGNMENTS gllsa
		 WHERE	gllsa.ledger_set_id = gll.ledger_id
		 AND	gllsa.ledger_id = gll.ledger_id
		 AND	NVL(gllsa.status_code, 'X') <> 'D'));

Line: 436

         v2        => 'Inserting new ledgers to the respective ' ||
		      'ledger sets into GL_LEDGER_SET_ASSIGNMENTS...');

Line: 440

    INSERT INTO GL_LEDGER_SET_ASSIGNMENTS
    (ledger_set_id, ledger_id, status_code, last_update_date,
     last_updated_by, last_update_login, creation_date,
     created_by, start_date, end_date)
    (SELECT distinct gllsa.ledger_set_id, gllsna.ledger_id, 'I', SYSDATE,
		     GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
		     GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID,
		     SYSDATE, GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
		     NULL, NULL
     FROM   GL_LEDGER_SET_NORM_ASSIGN gllsna,
	    GL_LEDGERS gll,
	    GL_LEDGER_SET_ASSIGNMENTS gllsa
     WHERE  gllsna.status_code = 'I'
     AND    gllsna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
     AND    gll.ledger_id = gllsna.ledger_id
     AND    gll.chart_of_accounts_id = GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
     AND    gll.object_type_code = 'L'
     AND    gllsa.ledger_id = gllsna.ledger_set_id
     AND    NVL(gllsa.status_code, 'X') <> 'D'
     AND    NOT EXISTS
		(SELECT 1
		 FROM 	GL_LEDGER_SET_ASSIGNMENTS gllsa2
		 WHERE	gllsa2.ledger_set_id = gllsa.ledger_set_id
	  	 AND	gllsa2.ledger_id = gllsna.ledger_id
 		 AND	NVL(gllsa2.status_code, 'X') <> 'D'));

Line: 487

         v2        => 'Inserting all descendants of new ledger sets into ' ||
		      'GL_LEDGER_SET_ASSIGNMENTS...');

Line: 493

      INSERT INTO GL_LEDGER_SET_ASSIGNMENTS
      (ledger_set_id, ledger_id, status_code, last_update_date,
       last_updated_by, last_update_login, creation_date,
       created_by, start_date, end_date)
      (SELECT distinct gllsa1.ledger_set_id, gllsa2.ledger_id,
		       'I', SYSDATE, GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
		       GL_FLATTEN_SETUP_DATA.GLSTFL_LOGIN_ID, SYSDATE,
		       GL_FLATTEN_SETUP_DATA.GLSTFL_USER_ID,
		       NULL, NULL
	FROM  GL_LEDGER_SET_NORM_ASSIGN gllsna,
	      GL_LEDGERS gll,
	      GL_LEDGER_SET_ASSIGNMENTS gllsa1,
	      GL_LEDGER_SET_ASSIGNMENTS gllsa2
   	WHERE gllsna.status_code = 'I'
	AND   gllsna.request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
	AND   gll.ledger_id = gllsna.ledger_set_id
	AND   gll.chart_of_accounts_id =
		GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
	AND   gll.object_type_code = 'S'
 	AND   gll.automatically_created_flag = 'N'
	AND   gllsa1.ledger_id = gllsna.ledger_set_id
	AND   NVL(gllsa1.status_code, 'X') <> 'D'
 	AND   gllsa2.ledger_set_id = gllsna.ledger_id
	AND   NVL(gllsa2.status_code, 'X') <> 'D'
	AND   NOT EXISTS
		(SELECT 1
		 FROM 	GL_LEDGER_SET_ASSIGNMENTS gllsa3
		 WHERE	gllsa3.ledger_set_id = gllsa1.ledger_set_id
		 AND	gllsa3.ledger_id = gllsa2.ledger_id
		 AND	NVL(gllsa3.status_code, 'X') <> 'D'));

Line: 538

      SELECT 1
      INTO loop_exists
      FROM DUAL
      WHERE EXISTS
	   (SELECT 	1
            FROM 	GL_LEDGER_SET_ASSIGNMENTS gllsa1,
			GL_LEDGERS gll,
			GL_LEDGER_SET_ASSIGNMENTS gllsa2
	    WHERE	gllsa1.status_code = 'I'
	    AND		gll.ledger_id = gllsa1.ledger_set_id
	    AND		gll.chart_of_accounts_id =
				GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID
	    AND		gll.object_type_code = 'S'
	    AND		gll.automatically_created_flag = 'N'
	    AND		gllsa1.ledger_set_id <> gllsa1.ledger_id
	    AND 	NVL(gllsa2.status_code, 'X') <> 'D'
	    AND		gllsa2.ledger_set_id = gllsa1.ledger_id
 	    AND		gllsa2.ledger_id = gllsa1.ledger_set_id);

Line: 639

    DELETE from GL_LEDGER_SET_NORM_ASSIGN
    WHERE status_code = 'D'
    AND	  request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
    AND   ledger_set_id IN
	 	(SELECT ledger_id
		 FROM 	GL_LEDGERS
		 WHERE	object_type_code = 'S'
		 AND	chart_of_accounts_id =
				GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);

Line: 671

    UPDATE GL_LEDGER_SET_NORM_ASSIGN
    SET   status_code = NULL, request_id = NULL
    WHERE status_code = 'I'
    AND	  request_id = GL_FLATTEN_SETUP_DATA.GLSTFL_REQ_ID
    AND   ledger_set_id IN
	 	(SELECT ledger_id
		 FROM 	GL_LEDGERS
		 WHERE	object_type_code = 'S'
		 AND	chart_of_accounts_id =
				GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);

Line: 703

    DELETE from GL_LEDGER_SET_ASSIGNMENTS
    WHERE status_code = 'D'
    AND   ledger_set_id IN
	 	(SELECT ledger_id
		 FROM 	GL_LEDGERS
		 WHERE	object_type_code = 'S'
		 AND	chart_of_accounts_id =
				GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);

Line: 734

    UPDATE GL_LEDGER_SET_ASSIGNMENTS
    SET   status_code = NULL
    WHERE status_code = 'I'
    AND   ledger_set_id IN
	 	(SELECT ledger_id
		 FROM 	GL_LEDGERS
		 WHERE	object_type_code = 'S'
		 AND	chart_of_accounts_id =
				GL_FLATTEN_SETUP_DATA.GLSTFL_COA_ID);