APPS.PA_RBS_MAPPING SQL Statements

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

Line: 60

               SELECT decode(p_res_class_id,1,PERSON_RC_PRECEDENCE,
                                             2,EQUIPMENT_RC_PRECEDENCE,
                                             3,MATERIAL_RC_PRECEDENCE,
                                             4,FIN_ELEM_RC_PRECEDENCE),
                        rule_id
                BULK COLLECT INTO
                        l_precedence,
                        l_sorted_rules
                FROM pa_rbs_mapping_rules
                WHERE element_version_id = p_struct_version_id
                ORDER BY
		max_level desc ,
		1 ,
		decode(level15,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level15,3,3),p_res_class_id)), --bug#3940722
		decode(level14,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level14,3,3),p_res_class_id)),
		decode(level13,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level13,3,3),p_res_class_id)),
		decode(level12,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level12,3,3),p_res_class_id)),
		decode(level11,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level11,3,3),p_res_class_id)),
		decode(level10,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level10,3,3),p_res_class_id)),
		decode(level9,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level9,3,3),p_res_class_id)),
		decode(level8,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level8,3,3),p_res_class_id)),
		decode(level7,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level7,3,3),p_res_class_id)),
		decode(level6,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level6,3,3),p_res_class_id)),
		decode(level5,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level5,3,3),p_res_class_id)),
		decode(level4,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level4,3,3),p_res_class_id)),
		decode(level3,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level3,3,3),p_res_class_id)),
		decode(level2,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level2,3,3),p_res_class_id)),
		decode(substr(level15,1,1),'I',2,'R',1,0) desc, --bug#3908041
		decode(substr(level14,1,1),'I',2,'R',1,0) desc,
		decode(substr(level13,1,1),'I',2,'R',1,0) desc,
		decode(substr(level12,1,1),'I',2,'R',1,0) desc,
		decode(substr(level11,1,1),'I',2,'R',1,0) desc,
		decode(substr(level10,1,1),'I',2,'R',1,0) desc,
		decode(substr(level9,1,1),'I',2,'R',1,0) desc,
		decode(substr(level8,1,1),'I',2,'R',1,0) desc,
		decode(substr(level7,1,1),'I',2,'R',1,0) desc,
		decode(substr(level6,1,1),'I',2,'R',1,0) desc,
		decode(substr(level5,1,1),'I',2,'R',1,0) desc,
		decode(substr(level4,1,1),'I',2,'R',1,0) desc,
		decode(substr(level3,1,1),'I',2,'R',1,0) desc,
		decode(substr(level2,1,1),'I',2,'R',1,0) desc;

Line: 262

			Select Level1 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;

Line: 264

			Select Level2 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;

Line: 266

			Select Level3 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;

Line: 268

			Select Level4 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;

Line: 270

			Select Level5 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;

Line: 272

			Select Level6 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;

Line: 274

			Select Level7 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;

Line: 276

			Select Level8 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;

Line: 278

			Select Level9 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;

Line: 280

			Select Level10 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;

Line: 282

			Select Level11 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;

Line: 284

			Select Level12 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;

Line: 286

			Select Level13 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;

Line: 288

			Select Level14 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;

Line: 290

			Select Level15 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;

Line: 308

PROCEDURE	delete_tmp_tables
		(
		p_max_level			IN NUMBER
		)
IS

BEGIN
		IF g_debug_mode = 'Y' THEN
		  PA_DEBUG.set_curr_function( p_function   => 'Delete Tmp Tables'
					     ,p_debug_mode => g_debug_mode );

Line: 318

		  pa_debug.g_err_stage:= 'Inside Delete Tmp Tables - p_max_level : '|| p_max_level;

Line: 325

				DELETE pa_rbs_map_tmp1;

Line: 327

				DELETE pa_rbs_map_tmp2;

Line: 329

				DELETE pa_rbs_map_tmp3;

Line: 331

				DELETE pa_rbs_map_tmp4;

Line: 333

				DELETE pa_rbs_map_tmp5;

Line: 335

				DELETE pa_rbs_map_tmp6;

Line: 337

				DELETE pa_rbs_map_tmp7;

Line: 339

				DELETE pa_rbs_map_tmp8;

Line: 341

				DELETE pa_rbs_map_tmp9;

Line: 343

				DELETE pa_rbs_map_tmp10;

Line: 345

				DELETE pa_rbs_map_tmp11;

Line: 347

				DELETE pa_rbs_map_tmp12;

Line: 349

				DELETE pa_rbs_map_tmp13;

Line: 358

		   pa_debug.g_err_stage:= 'Exiting Delete Tmp Tables' ;

Line: 392

			--EXECUTE IMMEDIATE 'SELECT LEVEL' || i || ' FROM pa_rbs_mapping_rules WHERE rule_id = ' || p_rule_id INTO l_token ;

Line: 441

			--EXECUTE IMMEDIATE 'SELECT LEVEL' || i || ' FROM pa_rbs_mapping_rules WHERE rule_id = ' || p_rule_id INTO l_token ;

Line: 500

			--EXECUTE IMMEDIATE 'SELECT LEVEL' || i || ' FROM pa_rbs_mapping_rules WHERE rule_id = ' || p_rule_id INTO l_token ;

Line: 577

			--EXECUTE IMMEDIATE 'SELECT LEVEL' || i || ' FROM pa_rbs_mapping_rules WHERE rule_id = ' || p_rule_id INTO l_token ;

Line: 771

			select rbs_element_id
			into l_rbs_element_id
			from pa_rbs_mapping_rules
			where rule_id = p_rule_id ;

Line: 777

			l_sql_stmt := ' SELECT ' || l_res_type_cols(i) ||
					' FROM pa_rbs_elements ' ||
					' WHERE rbs_version_id = ' || p_struct_version_id ||
					' AND user_created_flag = ' || '''Y''' ||
					' AND rbs_element_id = ' || l_rbs_element_id ;

Line: 825

		SELECT rbs_element_id
		INTO l_parent_element_id
		FROM pa_rbs_elements
		WHERE rbs_level = p_level
		CONNECT BY rbs_element_id = PRIOR parent_element_id
		START WITH rbs_element_id = g_rbs_element_id ;

Line: 853

		l_INSERT_clause		VARCHAR2 (500);

Line: 854

		l_SELECT_clause		VARCHAR2 (500);

Line: 870

		--delete pa_rbs_map_tmp3;

Line: 871

		l_INSERT_clause := 'INSERT INTO pa_rbs_map_tmp3('
				|| 'txn_accum_header_id,'
				|| 'struct_version_id,'
				|| 'element_version_id,'
				|| 'parent_element_version_id,'
				|| 'resource_type_id,'
				|| 'resource_source_id,'
				|| get_sql_clause(p_rule_id,p_level,'NONE')
				|| ')' ;

Line: 881

		l_SELECT_clause := 'SELECT '
				|| 'TMP.txn_accum_header_id,'
				|| ':p_struct_version_id,'
				|| 'RBS.rbs_element_id,'
				|| 'RBS.parent_element_id,'
				|| get_sql_clause(p_rule_id,p_level,'RES_TYP_ID') || ' ,'
				|| get_sql_clause(p_rule_id,p_level,'RES_SOURCE') || ' ,'
				|| get_sql_clause(p_rule_id,p_level,'TMP') ;

Line: 906

				' (SELECT txn_accum_header_id ' ||
				' FROM  pa_rbs_txn_accum_map ' ||
				' WHERE struct_version_id = :p_struct_version_id)' ;

Line: 920

				' (SELECT txn_accum_header_id ' ||
				' FROM  pa_rbs_txn_accum_map ' ||
				' WHERE struct_version_id = :p_struct_version_id)' ;

Line: 926

		l_SQL_statement := l_INSERT_clause || ' ' ||
				l_SELECT_clause || ' ' ||
				l_FROM_clause || ' ' ||
				l_WHERE_clause  ;

Line: 941

		  pa_debug.g_err_stage:= 'Exiting mapped_header- Inserts in pa_rbs_map_tmp :'||SQL%ROWCOUNT ;

Line: 963

		l_INSERT_clause		VARCHAR2 (500);

Line: 964

		l_SELECT_clause		VARCHAR2 (500);

Line: 980

		--EXECUTE IMMEDIATE 'DELETE pa_rbs_map_tmp' || l_tmp;

Line: 982

		l_INSERT_clause := 'INSERT INTO pa_rbs_map_tmp' || l_tmp || ' ('
				|| 'struct_version_id, '
				|| 'parent_element_version_id,'
				|| 'resource_type_id,'
				|| 'resource_source_id,'
				|| 'sequence,'
				|| get_sql_clause(p_rule_id,p_level,'NONE')
				|| ')' ;

Line: 991

		l_SELECT_clause := 'SELECT '
				|| ':p_struct_version_id,'
				|| 'RBS.rbs_element_id,'
				|| get_sql_clause(p_rule_id,p_level,'RES_TYP_ID') || ' ,'
				|| get_sql_clause(p_rule_id,p_level,'RES_SOURCE') || ' ,'
				|| 'pa_rbs_elements_s.nextval,'
				|| get_sql_clause(p_rule_id,p_level,'TMP') ;

Line: 1000

		l_FROM_clause  := ' FROM ( SELECT DISTINCT struct_version_id, '
				|| get_sql_clause_unmap(p_rule_id,p_level ,'NONE')  --bug#3749017
				|| ' FROM pa_rbs_map_tmp' || l_tmp
				|| ' WHERE parent_element_version_id IS NULL '
				|| ' ) TMP,'
				|| 'pa_rbs_elements RBS ' ;

Line: 1030

		l_SQL_statement := l_INSERT_clause || ' ' ||
				l_SELECT_clause || ' ' ||
				l_FROM_clause || ' ' ||
				l_WHERE_clause || ';' ;

Line: 1039

		  pa_debug.g_err_stage:= 'Exiting unmapped_header- Inserts in pa_rbs_map_tmp :'||SQL%ROWCOUNT ;

Line: 1063

		l_INSERT_clause		VARCHAR2 (500);

Line: 1064

		l_SELECT_clause		VARCHAR2 (500);

Line: 1078

		--EXECUTE IMMEDIATE 'DELETE pa_rbs_map_tmp' || l_temp;

Line: 1080

		l_INSERT_clause := 'INSERT INTO pa_rbs_map_tmp' || l_temp
				|| ' ('
				|| ' struct_version_id, '
				|| 'sequence, '
				|| 'resource_type_id,'
				|| 'resource_source_id '
				|| ')' ;

Line: 1088

		l_SELECT_clause := 'SELECT '
				|| ':p_struct_version_id,'
				|| ' pa_rbs_elements_s.nextval , '
				|| ' -1 , '
				|| ':p_struct_version_id';

Line: 1095

		l_FROM_clause  := ' FROM ( SELECT distinct struct_version_id '
				|| ' FROM pa_rbs_map_tmp' || l_temp
				|| ' WHERE parent_element_version_id IS NULL '
				|| ' ) ' ;

Line: 1100

		l_SQL_statement := l_INSERT_clause || ' ' ||
				l_SELECT_clause || ' ' ||
				l_FROM_clause || ' ;'  ;

Line: 1109

		  pa_debug.g_err_stage:= 'Exiting unmapped_header_laststep- Inserts in pa_rbs_map_tmp :'||SQL%ROWCOUNT ;

Line: 1133

		l_INSERT_clause		VARCHAR2 (500);

Line: 1134

		l_SELECT_clause		VARCHAR2 (500);

Line: 1152

		l_INSERT_clause := 'INSERT INTO pa_rbs_elements ('
				|| 'rbs_version_id,'
				|| 'rbs_element_id,'
				|| 'resource_source_id,'
				|| 'rbs_level,'
				|| 'rbs_element_name_id,'
				|| 'outline_number,'
				|| 'order_number,'
				|| 'resource_type_id,'
				|| 'rule_flag,'
				|| 'element_identifier,'
				|| 'user_created_flag,'
				|| 'last_update_date,'
				|| 'LAST_UPDATED_BY,'
				|| 'CREATION_DATE,'
				|| 'CREATED_BY,'
				|| 'LAST_UPDATE_LOGIN,'
				|| 'RECORD_VERSION_NUMBER )' ;

Line: 1171

		l_SELECT_clause := 'SELECT '
				|| ':p_struct_version_id,'
				|| 'sequence ' || ' ,'
				|| ':p_struct_version_id,'
				|| 1 || ' ,'
				|| ':x_rbs_element_name_id,'
				|| 1 || ' ,'
				|| 1 || ' ,'
				|| -1 || ' ,'
				|| '''' || 'N'|| '''' || ' ,'
				|| 1 || ' ,'
				|| '''' || 'N' || '''' ||' ,'
				|| '''' || sysdate || '''' ||  ' ,'
				|| ':g_user_id,'
				|| '''' || sysdate || '''' ||  ' ,'
				|| ':g_user_id,'
				|| ':g_login_id,'
				|| 1 ;

Line: 1192

		l_SQL_statement := l_INSERT_clause || ' ' ||
				l_SELECT_clause || ' ' ||
				l_FROM_clause || ' ;' ;

Line: 1205

		  pa_debug.g_err_stage:= 'Exiting create_top_rbs_element- Inserts in pa_rbs_elements :'||SQL%ROWCOUNT ;

Line: 1229

		l_INSERT_clause		VARCHAR2 (500);

Line: 1230

		l_SELECT_clause		VARCHAR2 (500);

Line: 1253

		delete pa_rbs_elem_in_temp;

Line: 1255

		l_INSERT_clause := 'INSERT INTO pa_rbs_elem_in_temp ( '
				|| 'resource_type_id,'
				|| 'resource_source_id ) ' ;

Line: 1258

		l_SELECT_clause := 'SELECT distinct '
				|| get_sql_clause(p_rule_id,p_level,'RES_TYP_ID') || ' ,'
				|| get_sql_clause(p_rule_id,p_level,'RES_SOURCE') ;

Line: 1274

		l_SQL_statement := l_INSERT_clause || ' ' ||
				l_SELECT_clause || ' ' ||
				l_FROM_clause || ' ' ||
				l_WHERE_clause || ';' ;

Line: 1286

		l_INSERT_clause := 'INSERT INTO pa_rbs_elements ( '
				|| 'rbs_version_id,'
				|| 'rbs_element_id,'
				|| 'parent_element_id,'
				|| 'rbs_level,'
				|| get_sql_clause(p_rule_id,p_level,'NONE') || ' ,'
				|| 'rbs_element_name_id,'
				|| 'outline_number,'
				|| 'order_number,'
				|| 'resource_type_id,'
				|| 'resource_source_id,'
				|| 'rule_flag,'
				|| 'element_identifier,'
				|| 'user_created_flag,'
				|| 'last_update_date,'
				|| 'LAST_UPDATED_BY,'
				|| 'CREATION_DATE,'
				|| 'CREATED_BY,'
				|| 'LAST_UPDATE_LOGIN,'
				|| 'RECORD_VERSION_NUMBER )' ;

Line: 1307

		l_SELECT_clause := 'SELECT '
				|| ':p_struct_version_id,'
				|| 'TMP.sequence , '
				|| ' nvl(TMP.parent_element_version_id ,TMP1.sequence ), '
				|| ':p_level,'
				|| get_sql_clause(p_rule_id,p_level,'TMP') || ' ,'
				|| 'name.rbs_element_name_id , '
				|| 'RBS.outline_number , '   --bug#3974663
				|| 'RBS.order_number , '     --bug#3974663
				|| get_sql_clause(p_rule_id,p_level,'RES_TYP_ID') || ' ,'
				|| get_sql_clause(p_rule_id,p_level,'RES_SOURCE') || ' ,'
				|| '''' || 'N' || '''' || ' ,'
				|| 1 || ' ,'
				|| '''' || 'N' || '''' ||' ,'
				|| '''' || sysdate || '''' ||  ' ,'
				|| ':g_user_id,'
				|| '''' || sysdate || '''' ||  ' ,'
				|| ':g_user_id,'
				|| ':g_login_id,'
				|| 1 ;

Line: 1345

		l_SQL_statement := l_INSERT_clause || ' ' ||
				l_SELECT_clause || ' ' ||
				l_FROM_clause || ' ' ||
				l_WHERE_clause || ';' ;

Line: 1356

		  pa_debug.g_err_stage:= 'Exiting create_rbs_element- Inserts in pa_rbs_elements :'||SQL%ROWCOUNT ;

Line: 1382

		l_INSERT_clause		VARCHAR2 (500);

Line: 1383

		l_SELECT_clause		VARCHAR2 (500);

Line: 1395

		l_INSERT_clause := 'INSERT INTO pa_rbs_txn_accum_map ('
				|| 'txn_accum_header_id,'
				|| 'element_id,'
				|| 'struct_version_id,'
				|| 'last_update_date,'
				|| 'LAST_UPDATED_BY,'
				|| 'CREATION_DATE,'
				|| 'CREATED_BY,'
				|| 'LAST_UPDATE_LOGIN'
				|| ' )' ;

Line: 1405

		l_SELECT_clause := ' SELECT '
				|| 'TMP.txn_accum_header_id,'
				|| 'RBS.rbs_element_id,'
				|| ':p_struct_version_id,'
				|| '''' || sysdate || '''' ||  ' ,'
				|| ':g_user_id,'
				|| '''' || sysdate || '''' ||  ' ,'
				|| ':g_user_id,'
				|| ':g_login_id	';

Line: 1424

		l_SQL_statement1 := l_SELECT_clause || ' ' ||
				l_FROM_clause || ' ' ||
				l_WHERE_clause  ;

Line: 1428

		l_SELECT_clause := ' SELECT '
				|| 'TMP.txn_accum_header_id,'
				|| 'TMP.element_version_id,'
				|| ':p_struct_version_id,'
				|| '''' || sysdate || '''' ||  ' ,'
				|| ':g_user_id,'
				|| '''' || sysdate || '''' ||  ' ,'
				|| ':g_user_id,'
				|| ':g_login_id';

Line: 1441

		l_SQL_statement2 := l_SELECT_clause || ' ' ||
				l_FROM_clause || ' ' ||
				l_WHERE_clause  ;

Line: 1445

		l_SQL_statement := l_INSERT_clause || ' ' ||
				l_SQL_statement1 ||
				' UNION ALL ' ||
				l_SQL_statement2 || ';' ;

Line: 1456

		  pa_debug.g_err_stage:= 'Exiting populate_txn_map- Inserts in Txn_accum_map :'||SQL%ROWCOUNT ;

Line: 1490

			--EXECUTE IMMEDIATE 'SELECT LEVEL' || i || ' FROM pa_rbs_mapping_rules WHERE rule_id = :p_rule_id' INTO l_token USING p_rule_id ;

Line: 1506

			EXECUTE IMMEDIATE 'UPDATE pa_rbs_map_tmp2 SET ( ' || l_sql_cols || ' ) =  ( SELECT ' || l_sql_cols || ' FROM PA_RBS_ELEMENTS where rbs_element_id = :g_rbs_element_id) '  USING g_rbs_element_id;

Line: 1511

		   pa_debug.g_err_stage:= 'Exiting populate_custom_columns - Columns Inserted :'|| l_sql_cols;

Line: 1544

                SELECT distinct txn_accum_header_id
                bulk collect into l_txn_header_id
                from pa_rbs_map_tmp2
                where resource_class_id = p_res_class_id
                and txn_accum_header_id  not in (SELECT txn_accum_header_id FROM
pa_rbs_txn_accum_map
                WHERE struct_version_id = p_struct_version_id ) ;

Line: 1554

			select count(*) into l_count from pa_rbs_elements where
			rbs_level = 1 and
                        rbs_version_id = p_struct_version_id and
                        user_created_flag = 'N' ;

Line: 1562

				SELECT pa_rbs_elements_s.nextval INTO l_sequence FROM dual ;

Line: 1565

				INSERT INTO pa_rbs_elements (
						rbs_version_id,
						rbs_element_id,
						rbs_level,
						rbs_element_name_id,
						outline_number,
						order_number,
						resource_type_id,
						rule_flag,
						element_identifier,
						user_created_flag,
						last_update_date,
						LAST_UPDATED_BY,
						CREATION_DATE,
						CREATED_BY,
						LAST_UPDATE_LOGIN,
						RECORD_VERSION_NUMBER  )
						VALUES (
						p_struct_version_id   ,
						l_sequence  ,
						1 ,
						x_rbs_element_name_id ,
						1 ,
						1 ,
						-1,
						'N',
						1 ,
						'N' ,
						sysdate,
						g_user_id ,
						sysdate ,
						g_user_id ,
						g_login_id ,
						1 ) ;

Line: 1605

				select rbs_element_id into l_sequence from pa_rbs_elements where rbs_level = 1 and
				rbs_version_id = p_struct_version_id and
				user_created_flag = 'N' ;

Line: 1614

			INSERT INTO pa_rbs_txn_accum_map (
					txn_accum_header_id,
					element_id,
					struct_version_id,
					last_update_date,
					LAST_UPDATED_BY,
					CREATION_DATE,
					CREATED_BY,
					LAST_UPDATE_LOGIN
					)
			VALUES		(l_txn_header_id(i),
					l_sequence,
					p_struct_version_id,
					sysdate,
					g_user_id ,
					sysdate ,
					g_user_id,
					g_login_id);

Line: 1694

		SELECT resource_class_id
		BULK COLLECT
		INTO l_res_class_id
		FROM pa_resource_classes_b;

Line: 1709

				SELECT rule_flag, max_level, rbs_element_id
				INTO g_rule_type, g_max_level, g_rbs_element_id
				FROM pa_rbs_mapping_rules
				WHERE rule_id = l_sorted_rule_id(k);

Line: 1715

				l_sql_stmt :=	'SELECT level' || g_max_level ||
						' FROM pa_rbs_mapping_rules ' ||
						' WHERE rule_id = :rule_id'  ;

Line: 1752

				delete_tmp_tables(g_max_level);

Line: 1756

                			    EXECUTE IMMEDIATE 'DELETE pa_rbs_map_tmp' || l_tmp;

Line: 1856

  DELETE pa_rbs_map_tmp1 ;

Line: 1862

   SELECT 'Y'
      INTO l_rbs_assoc_flag
   FROM dual
   WHERE
        EXISTS (
               SELECT   event_type
               FROM  pji_pa_proj_events_log
               WHERE event_type  = 'RBS_ASSOC' )
        OR EXISTS (
               SELECT   event_type
               FROM  pa_pji_proj_events_log
               WHERE event_type  = 'RBS_ASSOC' ) ;

Line: 1888

   SELECT 'Y'
      INTO l_rbs_prg_flag
   FROM dual
   WHERE
        EXISTS (
               SELECT   event_type
               FROM  pji_pa_proj_events_log
               WHERE event_type  = 'RBS_PRG' )
        OR EXISTS (
               SELECT   event_type
               FROM  pa_pji_proj_events_log
               WHERE event_type  = 'RBS_PRG' ) ;

Line: 1915

	INSERT INTO pa_rbs_map_tmp1
		(txn_accum_header_id,
		struct_version_id)
	SELECT  /*+ ORDERED */ DISTINCT det.txn_accum_header_id,	-- Bug#5578221 Performance Fix
		ASSIGN.rbs_version_id
	FROM   (SELECT DISTINCT
                  project_id,
                  txn_accum_header_id
                FROM
                  (
                    --SELECT /*+ ORDERED index(fin7 pji_fm_aggr_fin7_n1) */
                    SELECT /*+ ORDERED USE_NL(map, fin7) index(fin7 pji_fm_aggr_fin7_n1) */  --AECOM added ordered and use_nl bug 6739719
			      fin7.project_id,
			      txn_accum_header_id
                    FROM
			      pji_pjp_proj_batch_map map,		-- Bug#5223360 -  Peformance Fix
			      pji_fm_aggr_fin7 fin7
		    WHERE fin7.project_id = map.project_id
		     		AND map.worker_id = p_worker_id
				AND fin7.recvr_period_type='GL'
                    UNION ALL
                    --SELECT /*+ ORDERED index(accum pji_fp_txn_accum_n2)*/
                    SELECT /*+ ORDERED USE_NL(map, accum) index(accum pji_fp_txn_accum_n2)*/  --AECOM added ordered and use_nl  bug 6739719
			      accum.project_id,
			      txn_accum_header_id
                    FROM
			    pji_pjp_proj_batch_map map,		-- Bug#5223360 -  Peformance Fix
			    pji_fp_txn_accum accum
		     WHERE accum.project_id = map.project_id
			     AND map.worker_id = p_worker_id
                             AND accum.recvr_period_type='GL'
		    UNION ALL
                    --SELECT /*+ ORDERED index(accum1 pji_fp_txn_accum1_n1)*/
                    SELECT /*+ ORDERED USE_NL(map, accum1) index(accum1 pji_fp_txn_accum1_n1)*/   --AECOM added ordered and use_nl bug 6739719
			      accum1.project_id,
			      txn_accum_header_id
                    FROM
			    pji_pjp_proj_batch_map map,		-- Bug#5223360 -  Peformance Fix
			    pji_fp_txn_accum1 accum1
		     WHERE accum1.project_id = map.project_id
				 AND map.worker_id = p_worker_id
                    UNION ALL
                    SELECT /*+ORDERED*/DISTINCT
			      ra.project_id,
			      ra.txn_accum_header_id
                    FROM
		    	pji_pjp_proj_batch_map map,			-- Bug#5223360 -  Peformance Fix
                     	pji_pjp_wbs_header     wbs,
                      	pa_resource_assignments ra ,
				( SELECT   worker_id
				       , event_type
				       , attribute1
				FROM pji_pa_proj_events_log
				UNION ALL
				SELECT   p_worker_id
				       , event_type
				       , attribute1
				FROM pa_pji_proj_events_log
                      		)LOG
                    WHERE
                 l_rbs_assoc_flag = 'Y'  and   --AECOM Change  bug 6739719
			     map.worker_id = p_worker_id and
			     map.project_id = wbs.project_id and
			      LOG.event_type              = 'RBS_ASSOC'                   AND
			      TO_NUMBER(LOG.attribute1)   = wbs.project_id                AND
			      wbs.plan_version_id         = ra.budget_version_id          AND
			      wbs.project_id              = ra.project_id                 AND
			      wbs.wp_flag                 = 'N'                           AND
			      ra.txn_accum_header_id IS NOT NULL                          AND
			      LOG.worker_id               = p_worker_id
                    UNION ALL
                    SELECT /*+ ORDERED index(den PJI_XBS_DENORM_N1)*/ -- Bug#5578221 -  Peformance Fix
				DISTINCT
			      ra.project_id,
			      ra.txn_accum_header_id
                    FROM
			      pji_pjp_proj_batch_map map,
			      pji_pjp_wbs_header      hd2,  -- sub
			      pa_resource_assignments ra,
				       ( SELECT   worker_id
					       , event_type
					       , attribute1
					FROM pji_pa_proj_events_log
					UNION ALL
					SELECT   p_worker_id
					       , event_type
					       , attribute1
					FROM pa_pji_proj_events_log
				      )LOG,
		   	pji_xbs_denorm          den
                    WHERE
                  l_rbs_prg_flag = 'Y' and     --AECOM Change  bug 6739719
			      map.worker_id = p_worker_id and
			      map.project_id = hd2.project_id and
			      LOG.event_type              = 'RBS_PRG'                     AND
			      ra.txn_accum_header_id      IS NOT NULL                     AND
			      LOG.worker_id               = p_worker_id                   AND
			      den.struct_version_id       IS NULL                         AND
			      TO_NUMBER(LOG.attribute1)   = den.sup_project_id            AND
			      hd2.wbs_version_id          = den.sub_id                    AND
			      den.struct_type             = 'PRG'                         AND
			      hd2.wp_flag                 = 'N'                           AND
			      hd2.plan_version_id         = ra.budget_version_id          AND
			      hd2.project_id              = ra.project_id		            )
		  ) det,
		pa_rbs_prj_assignments ASSIGN,
		pa_rbs_versions_b rbsv,
		pa_rbs_headers_b rbsh
	WHERE
		det.project_id = ASSIGN.project_id AND
		det.txn_accum_header_id NOT IN
		(SELECT txn_accum_header_id FROM pa_rbs_txn_accum_map
		WHERE struct_version_id = ASSIGN.rbs_version_id ) AND
		ASSIGN.reporting_usage_flag = 'Y' AND
		ASSIGN.rbs_version_id = rbsv.rbs_version_id AND
		rbsv.current_reporting_flag = 'Y' AND
		rbsv.rbs_header_id = rbsh.rbs_header_id AND
		SYSDATE BETWEEN rbsh.effective_from_date AND
		NVL(rbsh.effective_to_date,SYSDATE);

Line: 2038

	INSERT INTO pa_rbs_map_tmp1
	  ( txn_accum_header_id
	  , struct_version_id)
        SELECT DISTINCT
               ra.txn_accum_header_id
	     , rbv2.rbs_version_id
        FROM  pji_pa_proj_events_log    LOG
            , pa_rbs_prj_assignments    asg
            , pa_rbs_versions_b         rbv1
            , pa_rbs_versions_b         rbv2
            , pa_budget_versions        bv
            , pa_resource_assignments   ra
        WHERE
              LOG.event_type              = 'RBS_PUSH'                    AND
              LOG.worker_id               = p_worker_id                   AND
              asg.rbs_version_id          = TO_NUMBER(LOG.attribute2)     AND
              asg.rbs_version_id          = rbv1.rbs_version_id           AND
              rbv1.rbs_header_id          = rbv2.rbs_header_id            AND
              rbv2.current_reporting_flag = 'Y'                           AND
              asg.project_id              = bv.project_id                 AND
              bv.budget_version_id        = ra.budget_version_id          AND
              NVL(bv.wp_version_flag, 'N')= 'N'                           AND
              bv.budget_status_code       = 'B'                           AND
              ra.txn_accum_header_id IS NOT NULL                          AND
              ra.txn_accum_header_id NOT IN
                (
		  SELECT txn_accum_header_id
                  FROM pa_rbs_txn_accum_map
                  WHERE struct_version_id = rbv2.rbs_version_id
		);

Line: 2069

    Pji_Pjp_Fp_Curr_Wrap.print_time( ' # rows inserted = ' || SQL%ROWCOUNT );

Line: 2074

	SELECT distinct struct_version_id
	BULK COLLECT
	INTO l_rbs_struct_version_id
	FROM pa_rbs_map_tmp1 ;

Line: 2088

	SELECT distinct head.revenue_category
	BULK COLLECT INTO l_revenue_category
	FROM pa_rbs_map_tmp1 tmp, pji_fp_txn_accum_header head
	WHERE tmp.struct_version_id = l_rbs_struct_version_id(i)
	AND tmp.txn_accum_header_id = head.txn_accum_header_id
	AND head.revenue_category IS NOT NULL
	AND head.revenue_category NOT IN
	(select resource_name from pa_rbs_element_map where
	resource_type_id = 14);

Line: 2103

		DELETE pa_rbs_map_tmp2 ;

Line: 2108

  		  SELECT job_group_id
              INTO l_job_group_id
              FROM pa_rbs_versions_b
              WHERE rbs_version_id = l_rbs_struct_version_id(i) ;

Line: 2118

		INSERT INTO pa_rbs_map_tmp2
			(txn_accum_header_id,
			struct_version_id,    ---not needed to insert but doing
			supplier_id,            --- supplier
			role_id,		--- role
			revenue_category_id,    --- revenue category
			resource_class_id,      --- resource class
			non_labor_resource_id, --- project non-labor resource
			person_type_id,         --- person type
			organization_id,        --- organization
			job_id,                 --- job
			inventory_item_id,      --- inventory item
			item_category_id,       --- item category
			expenditure_type_id,    --- expenditure type
			expenditure_category_id,--- expenditure category
			event_type_id,          --- event type
			person_id,              --- named person
			bom_equipment_id,       --- BOM equipment
			bom_labor_id            --- BOM labor
			)
		SELECT  DISTINCT
			head.txn_accum_header_id,
			l_rbs_struct_version_id(i),
			decode(head.vendor_id,-1,null,head.vendor_id),              --- supplier
			decode(head.PROJECT_ROLE_ID,-1,null, head.project_role_id),	 --- role
			decode(head.revenue_category,'PJI$NULL',null,get_res_type_numeric_id(head.revenue_category,14)),    --- revenue category
			decode(head.resource_class_id,-1,null,head.resource_class_id),           --- resource class
			decode(head.non_labor_resource_id,-1,null,head.non_labor_resource_id),   --- non labor resource
			decode(head.person_type,'PJI$NULL',null,get_res_type_numeric_id(head.person_type,11)),         --- person type
			decode(head.expenditure_organization_id,-1,null,head.expenditure_organization_id),        --- organization
			decode(head.job_id,-1,null,l_job_group_id,null,head.job_id,PA_Cross_Business_Grp.IsMappedToJob(head.job_id, l_job_group_id)),                 --- job, bug#4027727
			decode(head.inventory_item_id,-1,null,head.inventory_item_id),      --- inventory item
			decode(head.item_category_id,-1,null,head.item_category_id),       --- item category
			decode(head.expenditure_type_id,-1,null,head.expenditure_type_id),    --- expenditure type
			decode(head.expenditure_category_id,-1,null,head.expenditure_category_id), --- expenditure category
			decode(head.event_type_id,-1,null,head.event_type_id),          --- event type
			decode(head.person_id,-1,null,head.person_id),            --- named person
			decode(head.bom_equipment_resource_id,-1,null,head.bom_equipment_resource_id),       --- BOM equipment
			decode(head.bom_labor_resource_id,-1,null,head.bom_labor_resource_id)            --- BOM labor
		FROM 	pa_rbs_map_tmp1 tmp,
			pji_fp_txn_accum_header head
		WHERE tmp.struct_version_id = l_rbs_struct_version_id(i) AND
			tmp.txn_accum_header_id = head.txn_accum_header_id;

Line: 2171

                insert into pji_pa_proj_events_log (
                  EVENT_OBJECT,
                  EVENT_TYPE,
                  WORKER_ID
                )
                values (
                  l_rbs_struct_version_id(i),
                  'PJI_RBS_CHANGE',
                  l_worker_id
                );

Line: 2212

	   select count(*) into l_out from PA_RBS_PLANS_OUT_TMP;

Line: 2294

		SELECT distinct revenue_category_code
		BULK COLLECT INTO l_revenue_category
		FROM PA_RBS_PLANS_IN_TMP
		WHERE revenue_category_code NOT IN
		(select resource_name from pa_rbs_element_map where resource_type_id = 14)
                AND revenue_category_code IS NOT NULL; /*Added for bug 3575147*/

Line: 2306

		SELECT distinct person_type_code
		BULK COLLECT INTO l_person_type_code
		FROM PA_RBS_PLANS_IN_TMP
		WHERE person_type_code NOT IN
		(select resource_name from pa_rbs_element_map where resource_type_id = 11)
                AND person_type_code IS NOT NULL; /* Added for bug 3575147 */

Line: 2320

		SELECT job_group_id INTO l_job_group_id FROM pa_rbs_versions_b WHERE rbs_version_id = p_rbs_version_id ;

Line: 2334

              INSERT INTO PJI_FP_TXN_ACCUM_HEADER(
		txn_accum_header_id,
		vendor_id,
		project_role_id,
		revenue_category,
		resource_class_id,
		non_labor_resource_id,
		expenditure_organization_id,
		expenditure_org_id,
		work_type_id,
		exp_evt_type_id,
		event_type,
		event_type_classification,
		expenditure_type,
		expenditure_category,
		system_linkage_function,
		job_id,
		inventory_item_id,
		item_category_id,
		expenditure_type_id,
		expenditure_category_id,
		event_type_id,
		person_id,
		bom_equipment_resource_id,
		bom_labor_resource_id,
		person_type,
                named_role,
		last_update_date,
		LAST_UPDATED_BY,
		CREATION_DATE,
		CREATED_BY,
		LAST_UPDATE_LOGIN )
              SELECT
		PJI_FP_TXN_ACCUM_HEADER_S.nextval ,
		decode(TAB1.supplier_id,null,-1,TAB1.supplier_id),
		decode(TAB1.role_id,null,-1,TAB1.role_id),
		decode(TAB1.revenue_category_code,null,'PJI$NULL',TAB1.revenue_category_code),
		decode(TAB1.resource_class_id,null,-1,TAB1.resource_class_id) ,
		decode(TAB1.non_labor_resource_id,null,-1,TAB1.non_labor_resource_id),
		decode(TAB1.organization_id,null,-1,TAB1.organization_id),
		-1,
		-1,
		-1,
		nvl(evt.event_type, 'PJI$NULL'),
		nvl(evt.event_type_classification, 'PJI$NULL'),
		nvl(et.expenditure_type,'PJI$NULL'),
		nvl(ec.expenditure_category,'PJI$NULL'),
		'PJI$NULL',
		decode(TAB1.job_id,null,-1,TAB1.job_id),
		decode(TAB1.inventory_item_id,null,-1,TAB1.inventory_item_id),
		decode(TAB1.item_category_id,null,-1,TAB1.item_category_id),
		decode(TAB1.expenditure_type_id,null,-1,TAB1.expenditure_type_id),
		decode(TAB1.expenditure_category_id,null,-1,TAB1.expenditure_category_id),
		decode(TAB1.event_type_id,null,-1,TAB1.event_type_id),
		decode(TAB1.person_id,null,-1,TAB1.person_id),
		decode(TAB1.bom_equipment_id,null,-1,TAB1.bom_equipment_id),
		decode(TAB1.bom_labor_id,null,-1,TAB1.bom_labor_id),
		decode(TAB1.person_type_code,null,'PJI$NULL',TAB1.person_type_code),
                'PJI$NULL',
		SYSDATE,
		g_user_id,
		SYSDATE,
		g_user_id,
		g_login_id
              FROM
		(SELECT
			distinct
			supplier_id,
			role_id,
			revenue_category_code,
			resource_class_id,
			non_labor_resource_id,
			organization_id,
			job_id,
			inventory_item_id,
			item_category_id,
			expenditure_type_id,
			expenditure_category_id,
			event_type_id,
			person_id,
			bom_equipment_id,
			bom_labor_id,
			person_type_code
		FROM PA_RBS_PLANS_IN_TMP
		WHERE rowid NOT IN
			(SELECT /*+ ordered */ tmp.rowid
			FROM PA_RBS_PLANS_IN_TMP tmp, PJI_FP_TXN_ACCUM_HEADER head
			WHERE
			head.vendor_id       	= nvl(tmp.supplier_id,-1) AND
			head.project_role_id        	= nvl(tmp.role_id,-1) AND
			head.revenue_category    	= nvl(tmp.revenue_category_code,'PJI$NULL') AND
			head.resource_class_id      	= nvl(tmp.resource_class_id,-1) AND
			head.non_labor_resource_id  	= nvl(tmp.non_labor_resource_id,-1) AND
			head.expenditure_organization_id= nvl(tmp.organization_id,-1) AND
			head.job_id                 	= nvl(tmp.job_id,-1) AND
			head.inventory_item_id	= nvl(tmp.inventory_item_id,-1) AND
			head.item_category_id       	= nvl(tmp.item_category_id,-1) AND
			head.expenditure_type_id    	= nvl(tmp.expenditure_type_id,-1) AND
			head.expenditure_category_id	= nvl(tmp.expenditure_category_id,-1) AND
			head.event_type_id          	= nvl(tmp.event_type_id,-1) AND
			head.person_id            	= nvl(tmp.person_id,-1) AND
			head.bom_equipment_resource_id = nvl(tmp.bom_equipment_id,-1) AND
			head.bom_labor_resource_id   = nvl(tmp.bom_labor_id,-1) AND
			head.person_type    		= nvl(tmp.person_type_code,'PJI$NULL')
			) )TAB1,
              pa_event_types evt,
              pa_expenditure_types et,
              pa_expenditure_categories ec
            WHERE
              tab1.event_type_id           = evt.event_type_id          (+) and
              tab1.expenditure_type_id     = et.expenditure_type_id     (+) and
              tab1.expenditure_category_id = ec.expenditure_category_id (+);

Line: 2457

  pa_debug.g_err_stage:= 'accum headers are inserted';

Line: 2461

	DELETE PA_RBS_PLANS_OUT_TMP;

Line: 2462

	INSERT INTO PA_RBS_PLANS_OUT_TMP
		(txn_accum_header_id,
		rbs_version_id,
		source_id,
		supplier_id,
		role_id,
		revenue_category_code,
		resource_class_id,
		non_labor_resource_id,
		organization_id,
		job_id,
		inventory_item_id,
		item_category_id,
		expenditure_type_id,
		expenditure_category_id,
		event_type_id,
		person_id,
		bom_equipment_id,
		bom_labor_id,
		person_type_code)
	SELECT /*+ ordered */
		head.txn_accum_header_id,
		p_rbs_version_id,
		tmp.source_id,
		tmp.supplier_id,
		tmp.role_id,
		tmp.revenue_category_code,
		tmp.resource_class_id,
		tmp.non_labor_resource_id,
		tmp.organization_id,
		tmp.job_id,
		tmp.inventory_item_id,
		tmp.item_category_id,
		tmp.expenditure_type_id,
		tmp.expenditure_category_id,
		tmp.event_type_id,
		tmp.person_id,
		tmp.bom_equipment_id,
		tmp.bom_labor_id,
		tmp.person_type_code
	FROM PA_RBS_PLANS_IN_TMP tmp, PJI_FP_TXN_ACCUM_HEADER head
	WHERE
		head.vendor_id		       	= nvl(tmp.supplier_id,-1) AND
		head.project_role_id        	= nvl(tmp.role_id,-1) AND
		head.revenue_category    	= nvl(tmp.revenue_category_code,'PJI$NULL') AND
		head.resource_class_id      	= nvl(tmp.resource_class_id,-1) AND
		head.non_labor_resource_id  	= nvl(tmp.non_labor_resource_id,-1) AND
		head.expenditure_organization_id= nvl(tmp.organization_id,-1) AND
		head.job_id                 	= nvl(tmp.job_id,-1) AND
		head.inventory_item_id		= nvl(tmp.inventory_item_id,-1) AND
		head.item_category_id       	= nvl(tmp.item_category_id,-1) AND
		head.expenditure_type_id    	= nvl(tmp.expenditure_type_id,-1) AND
		head.expenditure_category_id	= nvl(tmp.expenditure_category_id,-1) AND
		head.event_type_id          	= nvl(tmp.event_type_id,-1) AND
		head.person_id             	= nvl(tmp.person_id,-1) AND
		head.bom_equipment_resource_id  = nvl(tmp.bom_equipment_id,-1) AND
		head.bom_labor_resource_id	= nvl(tmp.bom_labor_id,-1) AND
		head.person_type	    	= nvl(tmp.person_type_code,'PJI$NULL') ;

Line: 2522

	 UPDATE pa_rbs_plans_out_tmp dest
		  SET revenue_category_id =
	( SELECT
	     src.resource_id
	  FROM
	     pa_rbs_element_map src
	 WHERE
	 src.resource_name    = dest.revenue_category_code AND
	 src.resource_type_id = 14 )
	 WHERE
	   revenue_category_code is not null;

Line: 2534

	 UPDATE pa_rbs_plans_out_tmp dest
	    SET person_type_id =
	    ( SELECT
		 src.resource_id
	      FROM
		pa_rbs_element_map src
	      WHERE
		src.resource_name    = dest.person_type_code AND
		src.resource_type_id = 11 )
	 WHERE
	   dest.person_type_code is not null;

Line: 2546

	UPDATE pa_rbs_plans_out_tmp dest
	   SET job_id =
		( decode(job_id, null, null, l_job_group_id, null, job_id,
			    PA_Cross_Business_Grp.IsMappedToJob(job_id, l_job_group_id)))
	WHERE
	  dest.job_id is not null;

Line: 2558

	UPDATE pa_rbs_plans_out_tmp dest
	SET job_id= PA_Cross_Business_Grp.IsMappedToJob(job_id,l_job_group_id)
	WHERE dest.job_id is not null;

Line: 2565

	DELETE pa_rbs_map_tmp2 ;

Line: 2566

	INSERT INTO pa_rbs_map_tmp2
		(txn_accum_header_id,
		struct_version_id,
		supplier_id,
		role_id,
		revenue_category_id,
		resource_class_id,
		non_labor_resource_id,
		organization_id,
		job_id,
		inventory_item_id,
		item_category_id,
		expenditure_type_id,
		expenditure_category_id,
		event_type_id,
		person_id,
		bom_equipment_id,
		bom_labor_id,
		person_type_id)
	SELECT
                distinct /* bug#3656352 */
		txn_accum_header_id,
		p_rbs_version_id,
		supplier_id,
		role_id,
		-- get_res_type_numeric_id(revenue_category_code,14),
		revenue_category_id,   --bug#4098679
		resource_class_id,
		non_labor_resource_id,
		organization_id,
		-- decode(job_id, null, null, l_job_group_id, null, job_id, PA_Cross_Business_Grp.IsMappedToJob(job_id, l_job_group_id)),  --bug#4027727
		job_id,    --bug#4098679
		inventory_item_id,
		item_category_id,
		expenditure_type_id,
		expenditure_category_id,
		event_type_id,
		person_id,
		bom_equipment_id,
		bom_labor_id,
		-- get_res_type_numeric_id(person_type_code,11)
		person_type_id   --bug#4098679
	   FROM PA_RBS_PLANS_OUT_TMP a
	   WHERE NOT EXISTS
		(SELECT 1
		 FROM pa_rbs_txn_accum_map b
		 WHERE b.struct_version_id = p_rbs_version_id and
		       b.txn_accum_header_id=a.txn_accum_header_id);

Line: 2623

select count(*) into l_tmpcnt from PA_RBS_MAP_TMP2;

Line: 2671

		   pa_debug.g_err_stage:= 'Calling Pji_Pjp_Sum_Rollup.UPDATE_RBS_DENORM' ;

Line: 2676

		Pji_Pjp_Sum_Rollup.UPDATE_RBS_DENORM;

Line: 2693

	UPDATE PA_RBS_PLANS_OUT_TMP tmp
	SET tmp.rbs_element_id =
			(select map.element_id
			from pa_rbs_txn_accum_map map
			where map.txn_accum_header_id = tmp.txn_accum_header_id
			and map.struct_version_id = p_rbs_version_id);

Line: 2708

	DELETE pa_rbs_map_tmp1 ;

Line: 2710

	INSERT INTO pa_rbs_map_tmp1
		(txn_accum_header_id,
		struct_version_id)
	SELECT distinct tmp.txn_accum_header_id, prj_assign.rbs_version_id
	FROM PA_RBS_PLANS_IN_TMP tmp,
		PA_RESOURCE_ASSIGNMENTS res_assign,
		PA_RBS_PRJ_ASSIGNMENTS prj_assign
	WHERE tmp.source_id = res_assign.resource_assignment_id AND
		res_assign.project_id = prj_assign.project_id AND
		tmp.txn_accum_header_id not in
			(SELECT txn_accum_header_id
			FROM pa_rbs_txn_accum_map
			WHERE struct_version_id = prj_assign.rbs_version_id );

Line: 2724

	SELECT distinct struct_version_id
	BULK COLLECT
	INTO l_rbs_struct_version_id
	FROM pa_rbs_map_tmp1;

Line: 2733

		DELETE pa_rbs_map_tmp2 ;

Line: 2734

		INSERT INTO pa_rbs_map_tmp2
			(txn_accum_header_id,
			struct_version_id,      ---not needed to insert but doing
			supplier_id,              --- supplier
			role_id,        --- role
			revenue_category_id,    --- revenue category
			resource_class_id,      --- resource class
			non_labor_resource_id, --- project non-labor resource
			person_type_id,         --- person type
			organization_id,        --- organization
			job_id,                 --- job
			inventory_item_id,      --- inventory item
			item_category_id,       --- item category
			expenditure_type_id,    --- expenditure type
			expenditure_category_id,--- expenditure category
			event_type_id,          --- event type
			person_id,              --- named person
			bom_equipment_id,       --- BOM equipment
			bom_labor_id           --- BOM labor
			)
		SELECT
			tmp.txn_accum_header_id,
			l_rbs_struct_version_id(i),
			tmp.supplier_id,              --- supplier
			tmp.role_id,                --- role
			get_res_type_numeric_id(tmp.revenue_category_code,14),    --- revenue category
			tmp.resource_class_id,           --- resource class
			tmp.non_labor_resource_id,   --- non labor resource
			get_res_type_numeric_id(tmp.person_type_code,11),     --- person_type
			tmp.organization_id,        --- organization
			tmp.job_id,                 --- job
			tmp.inventory_item_id,      --- inventory item
			tmp.item_category_id,       --- item category
			tmp.expenditure_type_id,    --- expenditure type
			tmp.expenditure_category_id, --- expenditure category
			tmp.event_type_id,          --- event type
			tmp.person_id,            --- named person
			tmp.bom_equipment_id,       --- BOM equipment
			tmp.bom_labor_id           --- BOM labor
		FROM pa_rbs_map_tmp1 tmp1, PA_RBS_PLANS_IN_TMP tmp
		WHERE tmp1.struct_version_id = l_rbs_struct_version_id(i) AND
		      tmp1.txn_accum_header_id = tmp.txn_accum_header_id AND
		      tmp.rowid = (select max(rowid) from PA_RBS_PLANS_IN_TMP where txn_accum_header_id = tmp.txn_accum_header_id);

Line: 2784

			select count(*) into l_tmpcnt from PA_RBS_MAP_TMP2;

Line: 2831

		   pa_debug.g_err_stage:= 'Calling Pji_Pjp_Sum_Rollup.UPDATE_RBS_DENORM' ;

Line: 2836

		Pji_Pjp_Sum_Rollup.UPDATE_RBS_DENORM;

Line: 2862

   select count(*) into l_out from PA_RBS_PLANS_OUT_TMP;

Line: 2911

		SELECT count(*)
		INTO l_count
		FROM PA_RBS_ELEMENT_MAP
		WHERE resource_name = l_resource_name
		AND resource_type_id = p_resource_type_id ;

Line: 2918

		SELECT resource_id
		INTO x_resource_id
		FROM PA_RBS_ELEMENT_MAP
		WHERE resource_name = l_resource_name
		AND resource_type_id = p_resource_type_id ;

Line: 2924

		SELECT PA_RBS_ELEMENT_MAP_S.nextval
		INTO x_resource_id
		FROM dual ;

Line: 2928

		INSERT INTO PA_RBS_ELEMENT_MAP
		(
		RESOURCE_TYPE_ID,
		RESOURCE_NAME,
		RESOURCE_ID,
		CREATION_DATE,
		CREATED_BY,
		LAST_UPDATE_LOGIN,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE)
		VALUES
		(
		p_resource_type_id,
		l_resource_name,
		x_resource_id,
		sysdate,
		g_user_id ,
		g_login_id ,
		g_user_id ,
		sysdate) ;

Line: 2983

		SELECT resource_id
		INTO l_resource_id
		FROM PA_RBS_ELEMENT_MAP
		WHERE resource_name = p_resource_name
		AND resource_type_id = p_resource_type_id;

Line: 2998

PROCEDURE	insert_rule
		(
		rbs_version_id	number,
		depth		number,
		level		PA_PLSQL_DATATYPES.Char30TabTyp,
		element_id	number,
		rule_flag varchar2,
		per_rc_pre	number,
		equip_rc_pre	number,
		mat_rc_pre	number,
		fin_rc_pre	number
		)
IS
		l_SQL_statement	varchar2(5000);

Line: 3021

		  PA_DEBUG.set_curr_function( p_function   => 'insert_rule'
					     ,p_debug_mode => g_debug_mode );

Line: 3023

		  pa_debug.g_err_stage:= 'Inside insert_rule';

Line: 3024

		  pa_debug.g_err_stage:= 'Inside insert_rule- rbs_version_id :'|| rbs_version_id||' depth:'||depth;

Line: 3052

		select PA_RBS_MAPPING_RULE_S.nextval
		into l_rule_id
		from dual;

Line: 3056

		-- commented select below as its not required because rule_flag in pa_rbs_elements is not null column
		-- select decode(rule_flag,null,'null',''''||rule_flag||'''') into l_rule_flag from dual ;

Line: 3060

		l_SQL_statement := 'INSERT INTO PA_RBS_MAPPING_RULES' ||
				' (ELEMENT_VERSION_ID, ' ||
				' RULE_ID, ' ||
				' RBS_ELEMENT_ID, ' ||
				' RULE_FLAG, ' ||
				col_sql_clause  || ' , ' ||
				' PERSON_RC_PRECEDENCE, ' ||
				' EQUIPMENT_RC_PRECEDENCE, ' ||
				' MATERIAL_RC_PRECEDENCE, ' ||
				' FIN_ELEM_RC_PRECEDENCE, ' ||
				' MAX_LEVEL, ' ||
				' LAST_UPDATE_DATE, ' ||
				' LAST_UPDATED_BY, ' ||
				' CREATION_DATE, ' ||
				' CREATED_BY, ' ||
				' LAST_UPDATE_LOGIN ) ' ||
				' VALUES ' ||
				 '( :rbs_version_id,' ||
				':l_rule_id,' ||
				':element_id,'  ||
				':l_rule_flag,'  ||
				val_sql_clause ||' ,' ||
				':per_rc_pre,' ||
				':equip_rc_pre,' ||
				':mat_rc_pre,' ||
				':fin_rc_pre,' ||
				':depth,'  ||
				'''' || sysdate || '''' || ' ,' ||
				':g_user_id,' ||
				'''' || sysdate || '''' || ' ,' ||
				':g_user_id,' ||
				':g_login_id ) ; ' ;

Line: 3103

		   pa_debug.g_err_stage:= 'Exiting insert_rule' ;

Line: 3133

			select bom_labor_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;

Line: 3140

			select bom_equipment_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;

Line: 3147

			select person_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;

Line: 3154

			select event_type_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;

Line: 3161

			select expenditure_category_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;

Line: 3168

			select expenditure_type_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;

Line: 3175

			select item_category_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;

Line: 3182

			select inventory_item_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;

Line: 3189

			select job_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;

Line: 3196

			select organization_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;

Line: 3203

			select person_type_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;

Line: 3210

			select non_labor_resource_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;

Line: 3217

			select resource_class_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;

Line: 3224

			select revenue_category_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;

Line: 3231

			select role_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;

Line: 3238

			select supplier_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;

Line: 3245

			select	USER_DEFINED_CUSTOM1_ID,
				USER_DEFINED_CUSTOM2_ID,
				USER_DEFINED_CUSTOM3_ID,
				USER_DEFINED_CUSTOM4_ID,
				USER_DEFINED_CUSTOM5_ID
			into	l_value1,
				l_value2,
				l_value3,
				l_value4,
				l_value5
			from pa_rbs_elements where rbs_element_id = p_elem_version_id;

Line: 3314

		select element_version_id
			, resource_type_id
			, rule_flag
		bulk collect into elem_version_id
			, l_res_type_id
			, rule_flag
		from PA_RBS_MAP_TMP1
		where nvl(parent_element_version_id,-1) = nvl(element_id,-1) ;

Line: 3334

			select get_res_token(res_type_code,elem_version_id(i))
			into level(depth+1)
			from pa_res_types_b
			where res_type_id = l_res_type_id(i); --bug#3917401 replaced pa_res_types_vl by pa_res_types_b

Line: 3343

			insert_rule(rbs_version_id,depth+1,level, elem_version_id(i), rule_flag(i),per_rc_pre,equip_rc_pre,mat_rc_pre,fin_rc_pre);

Line: 3389

select count(1) into l_count from dual
where exists
(select 1
from pa_rbs_mapping_rules
where element_version_id = p_rbs_version_id);

Line: 3397

		delete PA_RBS_MAP_TMP1;

Line: 3398

		insert into PA_RBS_MAP_TMP1
		(
		STRUCT_VERSION_ID,
		ELEMENT_VERSION_ID,
		PARENT_ELEMENT_VERSION_ID,
		RESOURCE_CLASS_ID,
		BOM_LABOR_ID,
		BOM_EQUIPMENT_ID,
		PERSON_ID,
		EVENT_TYPE_ID,
		EXPENDITURE_CATEGORY_ID,
		EXPENDITURE_TYPE_ID,
		ITEM_CATEGORY_ID,
		INVENTORY_ITEM_ID,
		JOB_ID,
		ORGANIZATION_ID,
		PERSON_TYPE_ID,
		NON_LABOR_RESOURCE_ID,
		ROLE_ID,
		SUPPLIER_ID,
	--	added for custom nodes
		USER_DEFINED_CUSTOM1_ID,
		USER_DEFINED_CUSTOM2_ID,
		USER_DEFINED_CUSTOM3_ID,
		USER_DEFINED_CUSTOM4_ID,
		USER_DEFINED_CUSTOM5_ID,
		RULE_FLAG,
		RESOURCE_TYPE_ID
		)
		SELECT
		RBS_VERSION_ID,
		RBS_ELEMENT_ID,
		PARENT_ELEMENT_ID,
		RESOURCE_CLASS_ID,
		BOM_LABOR_ID,
		BOM_EQUIPMENT_ID,
		PERSON_ID,
		EVENT_TYPE_ID,
		EXPENDITURE_CATEGORY_ID,
		EXPENDITURE_TYPE_ID,
		ITEM_CATEGORY_ID,
		INVENTORY_ITEM_ID,
		JOB_ID,
		ORGANIZATION_ID,
		PERSON_TYPE_ID,
		NON_LABOR_RESOURCE_ID,
		ROLE_ID,
		SUPPLIER_ID,
	--	added for custom nodes
		USER_DEFINED_CUSTOM1_ID,
		USER_DEFINED_CUSTOM2_ID,
		USER_DEFINED_CUSTOM3_ID,
		USER_DEFINED_CUSTOM4_ID,
		USER_DEFINED_CUSTOM5_ID,
		RULE_FLAG,
		RESOURCE_TYPE_ID
		from PA_RBS_ELEMENTS
		where rbs_version_id = p_rbs_version_id and
		user_created_flag = 'Y';

Line: 3463

			  pa_debug.g_err_stage:= 'Inside create_mapping_rules- Inserts in Tmp1 :'||l_count_tmp1 ;

Line: 3475

			select element_version_id
			into p_elem_version_id
			from PA_RBS_MAP_TMP1
			where parent_element_version_id is null;

Line: 3480

			--insertion of SELF node
			insert_rule(p_rbs_version_id, 1 ,l_level, p_elem_version_id, 'N',0,0,0,0);

Line: 3485

			delete PA_RBS_MAP_TMP1;