[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
Select Level1 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
Select Level2 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
Select Level3 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
Select Level4 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
Select Level5 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
Select Level6 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
Select Level7 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
Select Level8 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
Select Level9 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
Select Level10 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
Select Level11 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
Select Level12 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
Select Level13 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
Select Level14 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
Select Level15 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
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 );
pa_debug.g_err_stage:= 'Inside Delete Tmp Tables - p_max_level : '|| p_max_level;
DELETE pa_rbs_map_tmp1;
DELETE pa_rbs_map_tmp2;
DELETE pa_rbs_map_tmp3;
DELETE pa_rbs_map_tmp4;
DELETE pa_rbs_map_tmp5;
DELETE pa_rbs_map_tmp6;
DELETE pa_rbs_map_tmp7;
DELETE pa_rbs_map_tmp8;
DELETE pa_rbs_map_tmp9;
DELETE pa_rbs_map_tmp10;
DELETE pa_rbs_map_tmp11;
DELETE pa_rbs_map_tmp12;
DELETE pa_rbs_map_tmp13;
pa_debug.g_err_stage:= 'Exiting Delete Tmp Tables' ;
--EXECUTE IMMEDIATE 'SELECT LEVEL' || i || ' FROM pa_rbs_mapping_rules WHERE rule_id = ' || p_rule_id INTO l_token ;
--EXECUTE IMMEDIATE 'SELECT LEVEL' || i || ' FROM pa_rbs_mapping_rules WHERE rule_id = ' || p_rule_id INTO l_token ;
--EXECUTE IMMEDIATE 'SELECT LEVEL' || i || ' FROM pa_rbs_mapping_rules WHERE rule_id = ' || p_rule_id INTO l_token ;
--EXECUTE IMMEDIATE 'SELECT LEVEL' || i || ' FROM pa_rbs_mapping_rules WHERE rule_id = ' || p_rule_id INTO l_token ;
select rbs_element_id
into l_rbs_element_id
from pa_rbs_mapping_rules
where rule_id = p_rule_id ;
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 ;
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 ;
l_INSERT_clause VARCHAR2 (500);
l_SELECT_clause VARCHAR2 (500);
--delete pa_rbs_map_tmp3;
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')
|| ')' ;
l_SELECT_clause := 'SELECT /*+ index (RBS PA_RBS_ELEMENTS_TMP1_111009)*/' /* Added for bug 11843445 */
|| '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') ;
' NOT exists (SELECT 1 FROM ' ||
' pa_rbs_txn_accum_map ' ||
' WHERE struct_version_id = :p_struct_version_id ' ||
' and TMP.txn_accum_header_id = txn_accum_header_id )' ; /* Modified for bug 11843445 */
' NOT exists (SELECT 1 FROM ' ||
' pa_rbs_txn_accum_map ' ||
' WHERE struct_version_id = :p_struct_version_id ' ||
' and TMP.txn_accum_header_id = txn_accum_header_id )' ; /* Modified for bug 11843445 */
l_SQL_statement := l_INSERT_clause || ' ' ||
l_SELECT_clause || ' ' ||
l_FROM_clause || ' ' ||
l_WHERE_clause ;
pa_debug.g_err_stage:= 'Exiting mapped_header- Inserts in pa_rbs_map_tmp :'||SQL%ROWCOUNT ;
l_INSERT_clause VARCHAR2 (500);
l_SELECT_clause VARCHAR2 (500);
--EXECUTE IMMEDIATE 'DELETE pa_rbs_map_tmp' || l_tmp;
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')
|| ')' ;
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') ;
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 ' ;
l_SQL_statement := l_INSERT_clause || ' ' ||
l_SELECT_clause || ' ' ||
l_FROM_clause || ' ' ||
l_WHERE_clause || ';' ;
pa_debug.g_err_stage:= 'Exiting unmapped_header- Inserts in pa_rbs_map_tmp :'||SQL%ROWCOUNT ;
l_INSERT_clause VARCHAR2 (500);
l_SELECT_clause VARCHAR2 (500);
--EXECUTE IMMEDIATE 'DELETE pa_rbs_map_tmp' || l_temp;
l_INSERT_clause := 'INSERT INTO pa_rbs_map_tmp' || l_temp
|| ' ('
|| ' struct_version_id, '
|| 'sequence, '
|| 'resource_type_id,'
|| 'resource_source_id '
|| ')' ;
l_SELECT_clause := 'SELECT '
|| ':p_struct_version_id,'
|| ' pa_rbs_elements_s.nextval , '
|| ' -1 , '
|| ':p_struct_version_id';
l_FROM_clause := ' FROM ( SELECT distinct struct_version_id '
|| ' FROM pa_rbs_map_tmp' || l_temp
|| ' WHERE parent_element_version_id IS NULL '
|| ' ) ' ;
l_SQL_statement := l_INSERT_clause || ' ' ||
l_SELECT_clause || ' ' ||
l_FROM_clause || ' ;' ;
pa_debug.g_err_stage:= 'Exiting unmapped_header_laststep- Inserts in pa_rbs_map_tmp :'||SQL%ROWCOUNT ;
l_INSERT_clause VARCHAR2 (500);
l_SELECT_clause VARCHAR2 (500);
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 )' ;
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 ;
l_SQL_statement := l_INSERT_clause || ' ' ||
l_SELECT_clause || ' ' ||
l_FROM_clause || ' ;' ;
pa_debug.g_err_stage:= 'Exiting create_top_rbs_element- Inserts in pa_rbs_elements :'||SQL%ROWCOUNT ;
l_INSERT_clause VARCHAR2 (500);
l_SELECT_clause VARCHAR2 (500);
delete pa_rbs_elem_in_temp;
l_INSERT_clause := 'INSERT INTO pa_rbs_elem_in_temp ( '
|| 'resource_type_id,'
|| 'resource_source_id ) ' ;
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') ;
l_SQL_statement := l_INSERT_clause || ' ' ||
l_SELECT_clause || ' ' ||
l_FROM_clause || ' ' ||
l_WHERE_clause || ';' ;
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 )' ;
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 ;
l_SQL_statement := l_INSERT_clause || ' ' ||
l_SELECT_clause || ' ' ||
l_FROM_clause || ' ' ||
l_WHERE_clause || ';' ;
pa_debug.g_err_stage:= 'Exiting create_rbs_element- Inserts in pa_rbs_elements :'||SQL%ROWCOUNT ;
l_INSERT_clause VARCHAR2 (500);
l_SELECT_clause VARCHAR2 (500);
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'
|| ' )' ;
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 ';
l_SQL_statement1 := l_SELECT_clause || ' ' ||
l_FROM_clause || ' ' ||
l_WHERE_clause ;
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';
l_SQL_statement2 := l_SELECT_clause || ' ' ||
l_FROM_clause || ' ' ||
l_WHERE_clause ;
l_SQL_statement := l_INSERT_clause || ' ' ||
l_SQL_statement1 ||
' UNION ALL ' ||
l_SQL_statement2 || ';' ;
pa_debug.g_err_stage:= 'Exiting populate_txn_map- Inserts in Txn_accum_map :'||SQL%ROWCOUNT ;
--EXECUTE IMMEDIATE 'SELECT LEVEL' || i || ' FROM pa_rbs_mapping_rules WHERE rule_id = :p_rule_id' INTO l_token USING p_rule_id ;
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;
pa_debug.g_err_stage:= 'Exiting populate_custom_columns - Columns Inserted :'|| l_sql_cols;
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 /*+ no_unnest */ /* Added for bug 11843445 */
txn_accum_header_id
FROM pa_rbs_txn_accum_map
WHERE struct_version_id = p_struct_version_id ) ;
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' ;
SELECT pa_rbs_elements_s.nextval INTO l_sequence FROM dual ;
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 ) ;
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' ;
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);
SELECT resource_class_id
BULK COLLECT
INTO l_res_class_id
FROM pa_resource_classes_b;
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);
l_sql_stmt := 'SELECT level' || g_max_level ||
' FROM pa_rbs_mapping_rules ' ||
' WHERE rule_id = :rule_id' ;
delete_tmp_tables(g_max_level);
EXECUTE IMMEDIATE 'DELETE pa_rbs_map_tmp' || l_tmp;
select max(rbs_element_id)
into g_max_rbs_id2
from pa_rbs_elements
where rbs_version_id = p_rbs_struct_version_id;
DELETE pa_rbs_map_tmp1 ;
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' ) ;
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' ) ;
delete from pa_rbs_map_t1 where worker_id = p_worker_id ;
INSERT INTO PA_RBS_MAP_T1 (WORKER_ID , PROJECT_ID , TXN_ACCUM_HEADER_ID)
WITH gmap as (select * from PJI_PJP_PROJ_BATCH_MAP MAP
where MAP.WORKER_ID = p_worker_id )
SELECT DISTINCT p_worker_id , PROJECT_ID, TXN_ACCUM_HEADER_ID
FROM (
SELECT /*+ ordered USE_NL(map, fin7) index(fin7 pji_fm_aggr_fin7_n1) */
FIN7.PROJECT_ID, TXN_ACCUM_HEADER_ID
FROM gmap map,
PJI_FM_AGGR_FIN7 FIN7
WHERE FIN7.PROJECT_ID = MAP.PROJECT_ID
AND FIN7.RECVR_PERIOD_TYPE='GL'
UNION ALL
SELECT /*+ ORDERED USE_NL(map, accum) index(accum pji_fp_txn_accum_n2)*/
ACCUM.PROJECT_ID, TXN_ACCUM_HEADER_ID
FROM gmap MAP,
PJI_FP_TXN_ACCUM ACCUM
WHERE ACCUM.PROJECT_ID = MAP.PROJECT_ID
AND ACCUM.RECVR_PERIOD_TYPE='GL'
UNION ALL
SELECT /*+ ORDERED USE_NL(map, accum1) index(accum1 pji_fp_txn_accum1_n1)*/
ACCUM1.PROJECT_ID, TXN_ACCUM_HEADER_ID
FROM gmap MAP,
PJI_FP_TXN_ACCUM1 ACCUM1
WHERE ACCUM1.PROJECT_ID = MAP.PROJECT_ID
UNION ALL
SELECT /*+ORDERED INDEX (RA PA_RESOURCE_ASSIGNMENTS_N1) index ( wbs pji_pjp_wbs_header_n1 ) */
DISTINCT RA.PROJECT_ID, RA.TXN_ACCUM_HEADER_ID
FROM gmap MAP, /* changed the order */
(
SELECT WORKER_ID , EVENT_TYPE , ATTRIBUTE1
FROM PJI_PA_PROJ_EVENTS_LOG
WHERE event_type = 'RBS_ASSOC' -- Moved the filter conditions to inline view Bug: 12799162
AND worker_id = p_worker_id
UNION ALL
SELECT p_worker_id , EVENT_TYPE , ATTRIBUTE1
FROM PA_PJI_PROJ_EVENTS_LOG
WHERE event_type = 'RBS_ASSOC' -- Moved the filter conditions to inline view Bug: 12799162
)LOG,
PJI_PJP_WBS_HEADER WBS,
PA_RESOURCE_ASSIGNMENTS RA
WHERE l_rbs_assoc_flag = 'Y'
AND MAP.PROJECT_ID = WBS.PROJECT_ID
-- AND LOG.EVENT_TYPE = 'RBS_ASSOC' -- Commented for bug 12799162
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 -- Commented for bug 12799162
and map.worker_id = log.worker_id /* added */
UNION ALL
SELECT /*+ORDERED INDEX (RA PA_RESOURCE_ASSIGNMENTS_N1) */
DISTINCT RA.PROJECT_ID, RA.TXN_ACCUM_HEADER_ID
FROM gmap MAP,
(
SELECT WORKER_ID , EVENT_TYPE , ATTRIBUTE1
FROM PJI_PA_PROJ_EVENTS_LOG
WHERE event_type = 'RBS_PRG' -- Moved the filter conditions to inline view Bug: 12799162
AND worker_id = p_worker_id
UNION ALL
SELECT p_worker_id , EVENT_TYPE , ATTRIBUTE1
FROM PA_PJI_PROJ_EVENTS_LOG
WHERE event_type = 'RBS_PRG' -- Moved the filter conditions to inline view Bug: 12799162
)LOG,
PJI_XBS_DENORM DEN,
PJI_PJP_WBS_HEADER HD2,
PA_RESOURCE_ASSIGNMENTS RA
WHERE l_rbs_assoc_flag = 'Y'
AND MAP.PROJECT_ID = HD2.PROJECT_ID
-- AND LOG.EVENT_TYPE = 'RBS_PRG' -- Commented for bug 12799162
AND RA.TXN_ACCUM_HEADER_ID IS NOT NULL
AND LOG.WORKER_ID = MAP.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 );
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
project_id,
txn_accum_header_id
FROM
pa_rbs_map_t1 t1 where t1.worker_id = p_worker_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
NOT EXISTS -- Bug#5578221 Performance Fix
(SELECT NULL FROM pa_rbs_txn_accum_map
WHERE struct_version_id = ASSIGN.rbs_version_id
AND DET.txn_accum_header_id = txn_accum_header_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);
delete from pa_rbs_map_t1 where worker_id = p_worker_id ;
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
);
Pji_Pjp_Fp_Curr_Wrap.print_time( ' # rows inserted = ' || SQL%ROWCOUNT );
SELECT distinct struct_version_id
BULK COLLECT
INTO l_rbs_struct_version_id
FROM pa_rbs_map_tmp1 ;
select max(rbs_element_id)
into l_rbs_max
from pa_rbs_elements
where rbs_version_id = l_rbs_struct_version_id(i);
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);
DELETE pa_rbs_map_tmp2 ;
SELECT job_group_id
INTO l_job_group_id
FROM pa_rbs_versions_b
WHERE rbs_version_id = l_rbs_struct_version_id(i) ;
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;
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
);
select count(*) into l_out from PA_RBS_PLANS_OUT_TMP;
delete from pa_rbs_map_t1 where worker_id = p_worker_id ;
INSERT INTO PA_RBS_MAP_T1 (WORKER_ID , PROJECT_ID , TXN_ACCUM_HEADER_ID)
SELECT DISTINCT p_worker_id , PROJECT_ID, TXN_ACCUM_HEADER_ID
FROM (
SELECT /*+ ordered USE_NL(map, fin7) index(fin7 pji_fm_aggr_fin7_n1) */
FIN7.PROJECT_ID, TXN_ACCUM_HEADER_ID
FROM PJI_FM_AGGR_FIN7 FIN7
WHERE p_calling_mode in ( 'FULL', 'INCREMENTAL ' )
AND FIN7.RECVR_PERIOD_TYPE='GL'
AND FIN7.project_id = p_project_id
UNION ALL
SELECT /*+ ORDERED USE_NL(map, accum) index(accum pji_fp_txn_accum_n2)*/
ACCUM.PROJECT_ID, TXN_ACCUM_HEADER_ID
FROM PJI_FP_TXN_ACCUM ACCUM
WHERE ( p_calling_mode = 'FULL' or p_event_type = 'RBS_ASSOC' )
AND ACCUM.RECVR_PERIOD_TYPE='GL'
AND ACCUM.project_id = p_project_id
UNION ALL
SELECT /*+ ORDERED USE_NL(map, accum1) index(accum1 pji_fp_txn_accum1_n1)*/
ACCUM1.PROJECT_ID, TXN_ACCUM_HEADER_ID
FROM PJI_FP_TXN_ACCUM1 ACCUM1
WHERE ( p_calling_mode = 'FULL' or p_event_type = 'RBS_ASSOC' )
AND ACCUM1.PROJECT_ID = p_project_id
UNION ALL
SELECT /*+ORDERED INDEX (RA PA_RESOURCE_ASSIGNMENTS_N1) */
DISTINCT RA.PROJECT_ID, RA.TXN_ACCUM_HEADER_ID
FROM pa_budget_versions bv ,
PA_RESOURCE_ASSIGNMENTS RA
WHERE ( p_calling_mode = 'FULL' or p_event_type = 'RBS_ASSOC' )
AND bv.PROJECT_ID = p_project_id
AND bv.PROJECT_ID = RA.PROJECT_ID
AND bv.fin_plan_type_id <> 10
AND RA.TXN_ACCUM_HEADER_ID IS NOT NULL );
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
txn_accum_header_id
FROM
pa_rbs_map_t1 t1 where t1.worker_id = p_worker_id
) det,
pa_rbs_prj_assignments ASSIGN,
pa_rbs_versions_b rbsv,
pa_rbs_headers_b rbsh
WHERE
p_calling_mode in ( 'FULL' , 'INCREMENTAL') AND
ASSIGN.project_id = p_project_id AND
ASSIGN.rbs_version_id = p_rbs_version_id AND
-- det.project_id = ASSIGN.project_id AND
NOT EXISTS
(SELECT NULL FROM pa_rbs_txn_accum_map
WHERE struct_version_id = ASSIGN.rbs_version_id
AND DET.txn_accum_header_id = txn_accum_header_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);
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 pa_pjt_events 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
LOG.attribute19 = p_project_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
);
SELECT distinct struct_version_id
BULK COLLECT
INTO l_rbs_struct_version_id
FROM pa_rbs_map_tmp1 ;
select max(rbs_element_id)
into l_rbs_max
from pa_rbs_elements
where rbs_version_id = l_rbs_struct_version_id(i);
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);
DELETE pa_rbs_map_tmp2 ;
SELECT job_group_id
INTO l_job_group_id
FROM pa_rbs_versions_b
WHERE rbs_version_id = l_rbs_struct_version_id(i) ;
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,PA_RBS_MAPPING.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,PA_RBS_MAPPING.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;
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*/
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 */
SELECT job_group_id INTO l_job_group_id FROM pa_rbs_versions_b WHERE rbs_version_id = p_rbs_version_id ;
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 (+);
pa_debug.g_err_stage:= 'accum headers are inserted';
DELETE PA_RBS_PLANS_OUT_TMP;
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') ;
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;
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;
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;
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;
DELETE pa_rbs_map_tmp2 ;
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);
select count(*) into l_tmpcnt from PA_RBS_MAP_TMP2;
pa_debug.g_err_stage:= 'Calling Pji_Pjp_Sum_Rollup.UPDATE_RBS_DENORM' ;
Pji_Pjp_Sum_Rollup.UPDATE_RBS_DENORM;
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);
DELETE pa_rbs_map_tmp1 ;
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 );
SELECT distinct struct_version_id
BULK COLLECT
INTO l_rbs_struct_version_id
FROM pa_rbs_map_tmp1;
DELETE pa_rbs_map_tmp2 ;
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);
select count(*) into l_tmpcnt from PA_RBS_MAP_TMP2;
pa_debug.g_err_stage:= 'Calling Pji_Pjp_Sum_Rollup.UPDATE_RBS_DENORM' ;
Pji_Pjp_Sum_Rollup.UPDATE_RBS_DENORM;
select count(*) into l_out from PA_RBS_PLANS_OUT_TMP;
SELECT count(*)
INTO l_count
FROM PA_RBS_ELEMENT_MAP
WHERE resource_name = l_resource_name
AND resource_type_id = p_resource_type_id ;
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 ;
SELECT PA_RBS_ELEMENT_MAP_S.nextval
INTO x_resource_id
FROM dual ;
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) ;
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;
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);
PA_DEBUG.set_curr_function( p_function => 'insert_rule'
,p_debug_mode => g_debug_mode );
pa_debug.g_err_stage:= 'Inside insert_rule';
pa_debug.g_err_stage:= 'Inside insert_rule- rbs_version_id :'|| rbs_version_id||' depth:'||depth;
select PA_RBS_MAPPING_RULE_S.nextval
into l_rule_id
from dual;
-- 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 ;
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 ) ; ' ;
pa_debug.g_err_stage:= 'Exiting insert_rule' ;
select bom_labor_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
select bom_equipment_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
select person_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
select event_type_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
select expenditure_category_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
select expenditure_type_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
select item_category_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
select inventory_item_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
select job_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
select organization_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
select person_type_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
select non_labor_resource_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
select resource_class_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
select revenue_category_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
select role_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
select supplier_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
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;
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) ;
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
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);
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);
delete PA_RBS_MAP_TMP1;
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';
pa_debug.g_err_stage:= 'Inside create_mapping_rules- Inserts in Tmp1 :'||l_count_tmp1 ;
select element_version_id
into p_elem_version_id
from PA_RBS_MAP_TMP1
where parent_element_version_id is null;
--insertion of SELF node
insert_rule(p_rbs_version_id, 1 ,l_level, p_elem_version_id, 'N',0,0,0,0);
delete PA_RBS_MAP_TMP1;