[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
INTO x_return
FROM pa_projects proj
WHERE proj.project_type = p_project_type
AND proj.segment1 = p_proj_num;
SELECT min(segment1), max(segment1)
INTO p_proj_num_from_out,
p_proj_num_to_out
FROM pa_projects;
SELECT MIN(segment1), MAX(segment1)
INTO p_proj_num_from_out,
p_proj_num_to_out
FROM pa_projects
WHERE project_type = NVL(p_proj_type, project_type)
AND NVL(cbs_enable_flag,'N') <> 'Y'; --bug#16461684
SELECT MIN(segment1)
INTO p_proj_num_from_temp
FROM pa_projects
WHERE project_type = p_proj_type
AND segment1 BETWEEN p_proj_num_from AND p_proj_num_to_out
AND NVL(cbs_enable_flag,'N') <> 'Y'; --bug#16461684
SELECT MAX(segment1)
INTO p_proj_num_to_out
FROM pa_projects
WHERE project_type = p_proj_type
AND segment1 BETWEEN p_proj_num_from_out AND p_proj_num_to
AND NVL(cbs_enable_flag,'N') <> 'Y'; --bug#16461684
SELECT MIN(segment1)
INTO p_proj_num_from_out
FROM pa_projects
WHERE project_type = p_proj_type
AND NVL(cbs_enable_flag,'N') <> 'Y'; --bug#16461684
SELECT MAX(segment1)
INTO p_proj_num_to_out
FROM pa_projects
WHERE project_type = p_proj_type
AND NVL(cbs_enable_flag,'N') <> 'Y'; --bug#16461684
SELECT DISTINCT accum_period
INTO l_prev_accum_period
FROM pa_project_accum_headers
WHERE project_id = x_project_id
AND Task_id = 0
AND Resource_List_member_id = 0 ;
SELECT end_date
INTO l_prev_accum_end_date
FROM pa_periods
WHERE period_name = l_prev_accum_period ;
SELECT DISTINCT gl_end_date
INTO l_prev_accum_end_date
FROM pa_periods_v
WHERE gl_period_name = l_prev_accum_period ;
SELECT 'N'
INTO x_ret_flag
FROM dual
WHERE EXISTS (SELECT 'x'
FROM pa_periods
WHERE period_name =
l_prev_accum_period) ;
SELECT 'N'
INTO x_ret_flag
FROM dual
WHERE EXISTS (SELECT 'x'
FROM pa_periods_v
WHERE gl_period_name =
l_prev_accum_period) ;
x_delete_temp_table IN VARCHAR2,
x_project_type IN VARCHAR2 -- for bug 2543021
)
IS
/* Added Debug Profile Option variable initialization for bug#2674619 */
P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
x_InsertStart DATE := NULL; /* Start time for Insert onto */
x_InsertEnd DATE := NULL; /* End time for Insert onto */
SELECT DISTINCT pfa.project_id, pfa.segment1
FROM pa_projects_for_accum pfa
WHERE pfa.request_id = x_request_id
AND pfa.segment1
BETWEEN l_project_num_from AND l_project_num_to
AND pfa.action_flag IN ('CS', 'RV', 'RL', 'CM', 'BD', 'PR', 'TX', 'TM')
AND PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(pfa.project_id) = 'N' --bug#16461684
AND NOT EXISTS (SELECT pf.project_id
FROM pa_projects_for_accum pf
WHERE pf.request_id = pfa.request_id
AND pf.segment1 = pfa.segment1 -- Bug3653978. replaced pfa.project_id with pfa.segment1
AND pf.action_flag IN
('PT', 'CL', 'BK', 'TR'));
AND EXISTS (SELECT pa.project_id -- for bug 2543021
FROM pa_projects pa
WHERE pa.project_id = pfa.project_id
AND pa.project_type = NVL(x_project_type,project_type));
SELECT DISTINCT pfa.project_id
FROM pa_projects_for_accum pfa
WHERE pfa.request_id = x_request_id
AND pfa.segment1
BETWEEN l_project_num_from AND l_project_num_to
AND pfa.action_flag = 'CM'
AND PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(pfa.project_id) = 'N' --bug#16461684
AND NOT EXISTS (SELECT pf.project_id
FROM pa_projects_for_accum pf
WHERE pf.request_id = pfa.request_id
AND pf.segment1 = pfa.segment1 -- Bug3783746. replaced pf.project_id with pfa.segment1
AND pf.action_flag IN
('PT', 'CL', 'BK', 'TR'));
SELECT pfa.action_flag
FROM pa_projects_for_accum pfa
WHERE pfa.segment1 = l_segment1
AND pfa.request_id = x_request_id ;
SELECT pfa.project_id, pfa.segment1, pfa.action_flag
FROM pa_projects_for_accum pfa
WHERE pfa.request_id = x_request_id
AND pfa.action_flag IN ('PT', 'CL', 'BK', 'TR')
ORDER BY pfa.segment1, pfa.action_flag ;
SELECT DISTINCT use_code Budget_Type_Code
FROM pa_resource_list_uses_v
WHERE project_id = l_project_id
AND budget_type_yn = 'Y'
AND use_code = NVL(Tmp_Bud_Type_Code,use_code);
SELECT DISTINCT resource_list_id
FROM pa_resource_list_assignments
WHERE project_id = l_project_id
AND NVL(resource_list_accumulated_flag,'N') = 'N';
SELECT DISTINCT proj.project_id, proj.segment1
FROM pa_projects_for_accum_v proj
WHERE proj.segment1 BETWEEN l_project_num_from AND l_project_num_to
AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
AND NOT EXISTS (SELECT 1
FROM pa_projects_for_accum proj_invalid
WHERE proj_invalid.project_id = proj.project_id
AND proj_invalid.action_flag IN ('PT','CL','BK','TR')
AND proj_invalid.request_id+0 = x_request_id);*/
SELECT
proj.project_id ,
proj.segment1 ,proj.project_type
FROM pa_projects proj
WHERE proj.segment1 BETWEEN pa_accum_utils.Get_project_info('F')
AND pa_accum_utils.Get_project_info('T')
AND pa_accum_utils.Get_context_info = 'REGULAR'
AND NVL(proj.template_flag,'N') <> 'Y'
AND NVL(proj.cbs_enable_flag,'N') <> 'Y' --bug#16461684
AND proj.segment1 BETWEEN l_project_num_from AND l_project_num_to
AND proj.project_type = NVL(x_project_type, project_type)
AND NOT EXISTS (SELECT 1
FROM pa_projects_for_accum proj_invalid
WHERE proj_invalid.project_id = proj.project_id
AND proj_invalid.action_flag IN ('PT','CL','BK','TR')
AND proj_invalid.request_id = x_request_id)
UNION
SELECT proj.project_id , proj.segment1 ,proj.project_type
FROM pa_projects proj
WHERE proj.segment1 BETWEEN l_project_num_from AND l_project_num_to
AND EXISTS ( select 1 from pa_alloc_txn_details alloc
where alloc.project_id = proj.project_id
AND alloc.run_id = pa_accum_utils.Get_grouping_id)
AND pa_accum_utils.Get_context_info = 'AUTO_ALLOCATION'
AND proj.project_type = NVL(x_project_type, project_type)
AND NVL(proj.cbs_enable_flag,'N') <> 'Y' --bug#16461684
AND NOT EXISTS (SELECT 1
FROM pa_projects_for_accum proj_invalid
WHERE proj_invalid.project_id = proj.project_id
AND proj_invalid.action_flag IN ('PT','CL','BK','TR')
AND proj_invalid.request_id = x_request_id); /*Code redefined for bug#3672175 ends here*/
Pa_Debug.debug('proj_accum: ' || 'Nothing selected for accumulation');
errbuf := 'Nothing selected for accumulation ';
errbuf := 'Please Select Accumulation mode as (I)ncremental';
DELETE FROM pa_projects_for_accum pfa
WHERE pfa.request_id = x_request_id
AND pfa.segment1
BETWEEN l_project_num_from AND l_project_num_to ;
INSERT INTO pa_projects_for_accum
(project_id, request_id, action_flag, segment1, exception_flag)
SELECT proj.project_id, x_request_id request_id,
'CS' action_flag, proj.segment1, 'N'
FROM pa_projects_for_accum_v proj
WHERE proj.segment1
BETWEEN l_project_num_from AND l_project_num_to
AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
AND x_actual_cost_flag = 'Y'
AND PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(proj.project_id) = 'N' --bug#16461684
AND EXISTS (SELECT 'x'
FROM pa_cost_distribution_lines_all cdl
WHERE cdl.project_id = proj.project_id
AND (cdl.line_type = 'R' OR
( cdl.line_type = 'I' and Pa_Proj_Accum_Main.G_PA_Enhanced_Burdening = 'Y')
) /*4278940 */
AND cdl.resource_accumulated_flag='N')
UNION ALL
SELECT proj.project_id, x_request_id request_id,
'RV' action_flag, proj.segment1, 'N'
FROM pa_projects_for_accum_v proj
WHERE proj.segment1
BETWEEN l_project_num_from AND l_project_num_to
AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
AND x_revenue_flag = 'Y'
AND PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(proj.project_id) = 'N' --bug#16461684
AND EXISTS (SELECT 'x'
FROM pa_draft_revenues dr
WHERE dr.project_id = proj.project_id
AND NVL(dr.resource_accumulated_flag,'S') =
DECODE(x_mode,'I', 'S', 'F',
NVL(dr.resource_accumulated_flag,'S'),'S')
AND dr.released_date IS NOT NULL)
UNION ALL
SELECT proj.project_id, x_request_id request_id,
'RL' action_flag, proj.segment1, 'N'
FROM pa_projects_for_accum_v proj
WHERE proj.segment1
BETWEEN l_project_num_from AND l_project_num_to
AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
AND PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(proj.project_id) = 'N' --bug#16461684
AND EXISTS (SELECT 'x'
FROM pa_resource_list_assignments rla
WHERE rla.project_id = proj.project_id
AND NVL(rla.resource_list_accumulated_flag,
'N') = 'N')
/* UNION ALL
SELECT proj.project_id, x_request_id request_id,
'CM' action_flag, proj.segment1, 'N'
FROM pa_projects_for_accum_v proj
WHERE proj.segment1 BETWEEN l_project_num_from AND l_project_num_to
AND x_commitments_flag = 'Y'
AND PA_CHECK_COMMITMENTS.commitments_changed(proj.project_id) = 'Y' */
UNION ALL
SELECT proj.project_id, x_request_id request_id,
'BD' action_flag, proj.segment1, 'N'
FROM pa_projects_for_accum_v proj
WHERE proj.segment1
BETWEEN l_project_num_from AND l_project_num_to
AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
AND x_budgets_flag = 'Y'
AND PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(proj.project_id) = 'N' --bug#16461684
AND EXISTS (SELECT 'x'
FROM pa_budget_versions bud
WHERE bud.project_id = proj.project_id
AND bud.current_flag = 'Y'
AND bud.resource_accumulated_flag = 'N')
UNION ALL
SELECT proj.project_id, x_request_id request_id,
'TR' action_flag, proj.segment1, 'Y'
FROM pa_projects_for_accum_v proj
WHERE proj.segment1
BETWEEN l_project_num_from AND l_project_num_to
AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
AND PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(proj.project_id) = 'N' --bug#16461684
AND EXISTS (SELECT 'x'
FROM pa_project_accum_headers pah
WHERE pah.project_id = proj.project_id
AND NVL(pah.tasks_restructured_flag, 'N') = 'Y')
UNION ALL
SELECT proj.project_id, x_request_id request_id,
'PR' action_flag, proj.segment1, 'N'
FROM pa_projects_for_accum_v proj
WHERE proj.segment1
BETWEEN l_project_num_from AND l_project_num_to
AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
AND PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(proj.project_id) = 'N' --bug#16461684
AND Pa_Proj_Accum_Main.check_period_flags
(proj.project_id, x_impl_option, l_current_end_date, 'PR',
proj.closed_date, l_current_start_date) = 'Y'
UNION ALL
SELECT proj.project_id, x_request_id request_id,
'BK' action_flag, proj.segment1, 'Y'
FROM pa_projects_for_accum_v proj
WHERE proj.segment1
BETWEEN l_project_num_from AND l_project_num_to
AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
AND PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(proj.project_id) = 'N' --bug#16461684
AND Pa_Proj_Accum_Main.check_period_flags
(proj.project_id, x_impl_option, l_current_end_date, 'BK',
proj.closed_date, l_current_start_date) = 'Y'
UNION ALL
SELECT proj.project_id, x_request_id request_id,
'CL' action_flag, proj.segment1, 'N'
FROM pa_projects_for_accum_v proj
WHERE proj.segment1
BETWEEN l_project_num_from AND l_project_num_to
AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
AND PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(proj.project_id) = 'N' --bug#16461684
AND Pa_Proj_Accum_Main.check_period_flags
(proj.project_id, x_impl_option, l_current_end_date, 'CL',
proj.closed_date, l_current_start_date) = 'Y'
UNION ALL
SELECT proj.project_id, x_request_id request_id,
'PT' action_flag, proj.segment1, 'Y'
FROM pa_projects_for_accum_v proj
WHERE proj.segment1
BETWEEN l_project_num_from AND l_project_num_to
AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
AND PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(proj.project_id) = 'N' --bug#16461684
AND Pa_Proj_Accum_Main.check_period_flags
(proj.project_id, x_impl_option, l_current_end_date, 'PT',
proj.closed_date, l_current_start_date) = 'Y'
UNION ALL
SELECT proj.project_id, x_request_id request_id, 'TX' action_flag,
proj.segment1, 'N'
FROM pa_projects_for_accum_v proj
WHERE proj.segment1 BETWEEN l_project_num_from AND l_project_num_to
AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
AND PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(proj.project_id) = 'N' --bug#16461684
AND (x_actual_cost_flag = 'Y' OR x_revenue_flag = 'Y')
AND EXISTS (SELECT 'x'
FROM pa_txn_accum txn
WHERE txn.actual_cost_rollup_flag = 'Y'
AND txn.project_id = proj.project_id
AND x_actual_cost_flag = 'Y'
UNION ALL
SELECT 'x'
FROM pa_txn_accum txn_r
WHERE txn_r.revenue_rollup_flag = 'Y'
AND txn_r.project_id = proj.project_id
AND x_revenue_flag = 'Y')
UNION ALL
SELECT proj.project_id, x_request_id request_id, 'TM' action_flag,
proj.segment1, 'N'
FROM pa_projects_for_accum_v proj
WHERE proj.segment1 BETWEEN l_project_num_from AND l_project_num_to
AND proj.project_type = NVL(x_project_type, project_type) --for bug 2543021
AND PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(proj.project_id) = 'N' --bug#16461684
AND x_commitments_flag = 'Y'
AND EXISTS (SELECT 'x'
FROM pa_txn_accum txn_m
WHERE txn_m.cmt_rollup_flag = 'Y'
AND txn_m.project_id = proj.project_id
AND x_commitments_flag = 'Y')
;
INSERT INTO pa_projects_for_accum
(project_id, request_id, action_flag, segment1, exception_flag)
SELECT recs.project_id, x_request_id, 'CM', recs.segment1, 'N'
FROM dual
WHERE Pa_Check_Commitments.commitments_changed(recs.project_id) = 'Y';
Pa_Debug.debug('proj_accum: ' || 'Cannot update summarization exception for project'||
filter_proj.segment1,Pa_Debug.DEBUG_LEVEL_EXCEPTION);
UPDATE pa_project_accum_headers
SET sum_exception_code = l_sum_exception_code
WHERE project_id = filter_proj.project_id
AND task_id = 0
AND resource_list_member_id = 0;
DELETE FROM pa_projects_for_accum
WHERE request_id = x_request_id ;
--Bug 5635857. Select the pending commitments for eligible projects and insert them
--into the temoporary table pa_commitment_txns_tmp so that they can be used later
-- in the code
--Note that if the below condition is changed then the code for updating the commitments
--should also be changed in the FOR loop
/* Modified for Bug#6408874 - START */
IF x_commitments_flag = 'Y' THEN
populate_cmt_tmp_table( p_project_num_from => l_project_num_from,
p_project_num_to => l_project_num_to,
p_system_linkage_function => x_system_linkage_function,
p_refresh_flag => 'N', -- for Bug# 7175975
p_project_type => NULL); -- for Bug# 7175975
x_actions_in.DELETE ; -- Initializing the PL/SQL table
UPDATE pa_project_accum_headers
SET sum_exception_code=NULL
WHERE project_accum_id=x_proj_level_accum_id;
Pa_Delete_Accum_Recs.delete_project_commitments
(l_project_id,
x_err_stack,
x_err_stage,
x_err_code) ;
UPDATE Pa_Budget_Versions
SET Resource_Accumulated_Flag ='N'
WHERE Project_id = l_project_id
AND (Current_Flag = 'Y' OR current_original_flag = 'Y');
Pa_Delete_Accum_Recs.delete_project_budgets
(l_project_id,
tmp_bud_type_code,
x_err_stack,
x_err_stage,
x_err_code) ;
Pa_Accum_Utils.update_proj_accum_header
(x_proj_level_accum_id,
x_current_period,
x_err_stack,
x_err_stage,
x_err_code);
Pa_Delete_Accum_Recs.delete_project_accum_headers
(l_project_id,
x_err_stack,
x_err_stage,
x_err_code) ;
INSERT INTO pa_accum_txn_time
(Project_id,
Project_num,
Request_id,
Attribute1,
Attribute2,
Attribute3,
Attribute4,
Attribute5,
Attribute6,
Attribute7,
Attribute8,
Attribute9,
Attribute10,
Attribute11,
Attribute12,
Attribute13,
Attribute14,
Attribute15,
Attribute16,
Attribute17,
Attribute18,
Attribute19,
Attribute20
)
VALUES
(l_project_id,
l_segment1,
x_request_id,
x_CdlStart,
x_CdlEnd,
x_RdlStart,
x_RdlEnd,
x_CmtCrStart,
x_CmtCrEnd,
x_CmtAccStart,
x_CmtAccEnd,
x_InitStart,
x_InitEnd,
x_BudStart,
x_BudEnd,
x_MapTxnStart,
x_MapTxnEnd,
x_PrcCmtStart,
x_PrcCmtEnd,
x_PrcTxnStart,
x_PrcTxnEnd,
x_PrjSumStart,
x_PrjSumEnd
);
IF x_delete_temp_table = 'Y' THEN
DELETE FROM pa_projects_for_accum WHERE request_id = x_request_id
AND exception_flag = 'N';
DELETE FROM pa_projects_for_accum WHERE request_id = x_request_id;
DELETE FROM pa_projects_for_accum
WHERE request_id = x_request_id ;
x_DelAccStart DATE := NULL; /* Start time - Delete ProjAccHeaders */
x_DelAccEnd DATE := NULL; /* End time - Delete ProjAccHeaders */
SELECT
ppr.project_id,
ppr.segment1,
ppr.closed_date
FROM
pa_projects ppr
WHERE
NVL(ppr.template_flag,'N') <> 'Y'
AND NVL(ppr.cbs_enable_flag,'N') <> 'Y' --bug#16461684
AND (
(
( ppr.segment1 BETWEEN l_project_num_from AND l_project_num_to) AND l_client_extn_mode = 'N' )
OR
(l_client_extn_mode = 'Y' and exists (Select 1 from PA_PROJ_REFRESH_TMP tmp where tmp.project_id=ppr.project_id))
) --14090312
AND ppr.project_type = NVL(x_project_type, project_type) -- for bug 2543021
AND Pa_Utils2.IsProjectInPurgeStatus(ppr.project_status_code) = 'N';
SELECT DISTINCT use_code Budget_Type_Code
FROM
pa_resource_list_uses_v
WHERE project_id = x_project_id
AND budget_type_yn = 'Y';
delete from PA_PROJ_REFRESH_TMP; --14090312
insert into PA_PROJ_REFRESH_TMP (project_id) values (tmp_table(i));
Pa_Res_Accums.delete_res_maps_on_prj_id
(projrec.project_id,
NULL, -- x_resource_list_id
x_err_stage,
x_err_code);
Pa_Res_Accums.delete_resource_accum_details
(NULL, -- x_resource_list_assignment_id
NULL, -- x_resource_list_id
projrec.project_id,
x_err_stage,
x_err_code);
Pa_Delete_Accum_Recs.delete_project_actuals
(projrec.project_id,
x_err_stack,
x_err_stage,
x_err_code);
Pa_Delete_Accum_Recs.delete_project_commitments
(projrec.project_id,
x_err_stack,
x_err_stage,
x_err_code);
Pa_Txn_Accums.update_resource_flag
(projrec.project_id,
projrec.project_id,
x_start_pa_date,
x_end_pa_date,
x_err_stage,
x_err_code);
Pa_Accum_Utils.update_proj_accum_header
(x_proj_level_accum_id,
x_current_period,
x_err_stack,
x_err_stage,
x_err_code);
Pa_Accum_Utils.update_tasks_restructured_flag (x_proj_level_accum_id,
'N',
x_err_stack,
x_err_stage,
x_err_code);
UPDATE Pa_Budget_Versions
SET Resource_Accumulated_Flag ='N'
WHERE Project_id = projrec.project_id AND
(Current_Flag = 'Y' OR current_original_flag = 'Y');
Pa_Delete_Accum_Recs.delete_project_budgets
(projrec.project_id,
NULL,
x_err_stack,
x_err_stage,
x_err_code);
x_DelAccStart := SYSDATE; /* Start time for Delete Project_accum_headers */
Pa_Delete_Accum_Recs.delete_project_accum_headers
(projrec.project_id ,
x_err_stack,
x_err_stage,
x_err_code);
x_DelAccEnd := SYSDATE; /* End time for Delete Project_accum_headers */
INSERT INTO pa_accum_txn_time
(Project_id,
Project_num,
Request_id,
Attribute1,
Attribute2,
Attribute3,
Attribute4,
Attribute5,
Attribute6,
Attribute7,
Attribute8,
Attribute9,
Attribute10,
Attribute11,
Attribute12,
Attribute13,
Attribute14,
Attribute15,
Attribute16,
Attribute17,
Attribute18,
Attribute19,
Attribute20
)
VALUES
(projrec.project_id,
projrec.segment1,
x_request_id,
x_CdlStart,
x_CdlEnd,
x_RdlStart,
x_RdlEnd,
x_CmtCrStart,
x_CmtCrEnd,
x_CmtAccStart,
x_CmtAccEnd,
x_MapTxnStart,
x_MapTxnEnd,
x_PrcTxnStart,
x_PrcTxnEnd,
x_PrcCmtStart,
x_PrcCmtEnd,
x_BudStart,
x_BudEnd,
x_DelAccStart,
x_DelAccEnd,
x_PrjSumStart,
x_PrjSumEnd
);
x_DelAccStart:= NULL; /* Start time for Delete ProjAccHead */
x_DelAccEnd := NULL; /* End time for Delete ProjAccHead */
SELECT
ppr.project_id,
ppr.segment1,
ppr.closed_date
FROM
pa_projects ppr
WHERE
NVL(ppr.template_flag,'N') <> 'Y'
AND ppr.segment1 BETWEEN l_project_num_from AND l_project_num_to
AND Pa_Utils2.IsProjectInPurgeStatus(ppr.project_status_code)= 'N';
SELECT 'Y'
INTO x_tasks_restructured_flag
FROM sys.dual
WHERE EXISTS (SELECT 'x'
FROM pa_project_accum_headers pah
WHERE pah.project_id = projrec.project_id
AND NVL(pah.tasks_restructured_flag,'N') = 'Y');
Pa_Debug.debug('ref_rl_accum: ' || 'Cannot update summarization exception for project'||projrec.segment1,Pa_Debug.DEBUG_LEVEL_EXCEPTION);
UPDATE pa_project_accum_headers
SET sum_exception_code = l_sum_exception_code
WHERE project_id = projrec.project_id
AND task_id = 0
AND resource_list_member_id = 0;
Pa_Res_Accums.delete_res_maps_on_prj_id
(projrec.project_id,
x_resource_list_id,
x_err_stage,
x_err_code);
Pa_Res_Accums.delete_resource_accum_details
(NULL,
x_resource_list_id,
projrec.project_id,
x_err_stage,
x_err_code);
Pa_Delete_Accum_Recs.delete_res_list_actuals
(projrec.project_id,
x_resource_list_id,
x_err_stack,
x_err_stage,
x_err_code);
Pa_Delete_Accum_Recs.delete_res_list_commitments
(projrec.project_id,
x_resource_list_id,
x_err_stack,
x_err_stage,
x_err_code);
SELECT
ppr.project_id,
ppr.segment1,
ppr.closed_date
FROM
pa_projects ppr
WHERE
NVL(ppr.template_flag,'N') <> 'Y'
AND ppr.segment1 BETWEEN l_project_num_from AND l_project_num_to
AND Pa_Utils2.IsProjectInPurgeStatus(ppr.project_status_code)= 'N';
SELECT start_date INTO x_start_pa_date
FROM pa_periods
WHERE period_name = x_start_pa_period;
SELECT end_date INTO x_end_pa_date
FROM pa_periods
WHERE period_name = x_end_pa_period;
Pa_Res_Accums.delete_res_maps_on_prj_id
(projrec.project_id,
NULL,
x_err_stage,
x_err_code);
Pa_Res_Accums.delete_resource_accum_details
(NULL,
NULL,
projrec.project_id,
x_err_stage,
x_err_code);
Pa_Delete_Accum_Recs.delete_project_actuals
(projrec.project_id,
x_err_stack,
x_err_stage,
x_err_code);
Pa_Delete_Accum_Recs.delete_project_commitments
(projrec.project_id,
x_err_stack,
x_err_stage,
x_err_code);
Pa_Txn_Accums.update_act_txn_accum
( projrec.project_id,
projrec.project_id,
x_err_stage,
x_err_code);
Pa_Accum_Utils.update_proj_accum_header
(x_proj_level_accum_id,
x_current_period,
x_err_stack,
x_err_stage,
x_err_code);
SELECT
pah.project_id
INTO
dummy_project_id
FROM
pa_project_accum_headers pah
WHERE
pah.project_id = x_project_id
AND pah.task_id = 0
AND pah.resource_list_id = 0
AND pah.resource_list_member_id = 0
AND pah.resource_id = 0
FOR UPDATE OF project_id NOWAIT;
x_last_updated_by NUMBER(15);
x_last_update_date NUMBER(15);
x_last_update_login NUMBER(15);
DELETE FROM pa_commitment_txns_tmp;
x_last_updated_by := NVL(FND_GLOBAL.USER_ID,-1);
x_last_update_date := NVL(FND_GLOBAL.USER_ID,-1);
x_last_update_login := NVL(FND_GLOBAL.LOGIN_ID,-1);
l_sql:='INSERT INTO pa_commitment_txns_tmp ' ||
'( PROJECT_ID, '||
'TASK_ID, '||
'TRANSACTION_SOURCE, '||
'LINE_TYPE, '||
'CMT_NUMBER, '||
'CMT_DISTRIBUTION_ID, '||
'CMT_HEADER_ID, '||
'DESCRIPTION, '||
'EXPENDITURE_ITEM_DATE, '||
'PA_PERIOD, '||
'GL_PERIOD, '||
'CMT_LINE_NUMBER, '||
'CMT_CREATION_DATE, '||
'CMT_APPROVED_DATE, '||
'CMT_REQUESTOR_NAME, '||
'CMT_BUYER_NAME, '||
'CMT_APPROVED_FLAG, '||
'CMT_PROMISED_DATE, '||
'CMT_NEED_BY_DATE, '||
'ORGANIZATION_ID, '||
'VENDOR_ID, '||
'VENDOR_NAME, '||
'EXPENDITURE_TYPE, '||
'EXPENDITURE_CATEGORY, '||
'REVENUE_CATEGORY, '||
'SYSTEM_LINKAGE_FUNCTION, '||
'UNIT_OF_MEASURE, '||
'UNIT_PRICE, '||
'CMT_IND_COMPILED_SET_ID, '||
'TOT_CMT_QUANTITY, '||
'QUANTITY_ORDERED, '||
'AMOUNT_ORDERED, '||
'ORIGINAL_QUANTITY_ORDERED, '||
'ORIGINAL_AMOUNT_ORDERED, '||
'QUANTITY_CANCELLED, '||
'AMOUNT_CANCELLED, '||
'QUANTITY_DELIVERED, '||
'QUANTITY_INVOICED, '||
'AMOUNT_INVOICED, '||
'QUANTITY_OUTSTANDING_DELIVERY, '||
'AMOUNT_OUTSTANDING_DELIVERY, '||
'QUANTITY_OUTSTANDING_INVOICE, '||
'AMOUNT_OUTSTANDING_INVOICE, '||
'QUANTITY_OVERBILLED, '||
'AMOUNT_OVERBILLED, '||
'ORIGINAL_TXN_REFERENCE1, '||
'ORIGINAL_TXN_REFERENCE2, '||
'ORIGINAL_TXN_REFERENCE3, '||
'LAST_UPDATE_DATE, '||
'LAST_UPDATED_BY, '||
'CREATION_DATE, '||
'CREATED_BY, '||
'LAST_UPDATE_LOGIN, '||
'REQUEST_ID, '||
'PROGRAM_APPLICATION_ID, '||
'PROGRAM_ID, '||
'PROGRAM_UPDATE_DATE, '||
'acct_raw_cost, '||
'acct_burdened_cost, '||
'denom_currency_code, '||
'denom_raw_cost, '||
'denom_burdened_cost, '||
'acct_currency_code, '||
'acct_rate_date, '||
'acct_rate_type, '||
'acct_exchange_rate, '||
'receipt_currency_code, '||
'receipt_currency_amount, '||
'receipt_exchange_rate, '||
'INVENTORY_ITEM_ID, '||
'UOM_CODE, '||
'wip_resource_id, '||
'resource_class '||
') '||
'SELECT '||
'pctv.project_id, '||
'pctv.task_id, '||
'pctv.transaction_source, '||
'decode(pctv.line_type,''P'',''P'',''R'',''R'',''I'',''I'',''O''), '||
'pctv.cmt_number, '||
'pctv.cmt_distribution_id, '||
'pctv.cmt_header_id, '||
'pctv.description, '||
'pctv.expenditure_item_date, '||
'pctv.pa_period, '||
'pctv.gl_period, '||
'pctv.cmt_line_number, '||
'pctv.cmt_creation_date, '||
'pctv.cmt_approved_date, '||
'pctv.cmt_requestor_name, '||
'pctv.cmt_buyer_name, '||
'pctv.cmt_approved_flag, '||
'pctv.cmt_promised_date, '||
'pctv.cmt_need_by_date, '||
'pctv.organization_id, '||
'pctv.vendor_id, '||
'pctv.vendor_name, '||
'pctv.expenditure_type, '||
'pctv.expenditure_category, '||
'pctv.revenue_category, '||
'pctv.system_linkage_function, '||
'pctv.unit_of_measure, '||
'pctv.unit_price, '||
'pctv.cmt_ind_compiled_set_id, '||
'pctv.tot_cmt_quantity, '||
'pctv.quantity_ordered, '||
'pctv.amount_ordered, '||
'pctv.original_quantity_ordered, '||
'pctv.original_amount_ordered, '||
'pctv.quantity_cancelled, '||
'pctv.amount_cancelled, '||
'pctv.quantity_delivered, '||
'pctv.quantity_invoiced, '||
'pctv.amount_invoiced, '||
'pctv.quantity_outstanding_delivery, '||
'pctv.amount_outstanding_delivery, '||
'pctv.quantity_outstanding_invoice, '||
'pctv.amount_outstanding_invoice, '||
'pctv.quantity_overbilled, '||
'pctv.amount_overbilled, '||
'pctv.original_txn_reference1, '||
'pctv.original_txn_reference2, '||
'pctv.original_txn_reference3, '||
'SYSDATE, '||
x_last_updated_by||', '||
'SYSDATE, '||
x_created_by||', '||
x_last_update_login||', '||
x_request_id||', '||
x_program_application_id||', '||
x_program_id||', '||
'NULL, '||
'pctv.acct_raw_cost, '||
'pctv.acct_burdened_cost, '||
'pctv.denom_currency_code, '||
'pctv.denom_raw_cost, '||
'pctv.denom_burdened_cost, '||
'pctv.acct_currency_code, '||
'pctv.acct_rate_date, '||
'pctv.acct_rate_type, '||
'pctv.acct_exchange_rate, '||
'pctv.receipt_currency_code, '||
'pctv.receipt_currency_amount, '||
'pctv.receipt_exchange_rate, '||
'pctv.INVENTORY_ITEM_ID, '||
'pctv.UOM_CODE, '||
'pctv.wip_resource_id, '||
'pctv.resource_class '||
'FROM '||
'pa_commitment_txns_v pctv '||
'WHERE ' ;
x_last_updated_by NUMBER(15);
x_last_update_date NUMBER(15);
x_last_update_login NUMBER(15);
DELETE FROM pa_commitment_txns_tmp;
DELETE FROM pa_pjm_po_commitments_tmp; /* Bug 6408874 */
DELETE FROM pa_pjm_req_commitments_tmp; /* Bug 6408874 */
DELETE FROM pa_proj_summ_tmp; -- BUG 10107257 Addition of DELETE
x_last_updated_by := NVL(FND_GLOBAL.USER_ID,-1);
x_last_update_date := NVL(FND_GLOBAL.USER_ID,-1);
x_last_update_login := NVL(FND_GLOBAL.LOGIN_ID,-1);
INSERT INTO pa_proj_summ_tmp (project_id) -- BUG 10107257 Using global temp table instead of Bulk collect
(SELECT DISTINCT pfa.project_id project_id
-- bulk collect into TmpProjectTab
FROM pa_projects_for_accum pfa
WHERE pfa.request_id = x_request_id
AND (
((pfa.segment1 BETWEEN p_project_num_from AND p_project_num_to) and l_client_extn_mode='N') OR
(l_client_extn_mode = 'Y' and exists (Select 1 from PA_PROJ_REFRESH_TMP tmp where tmp.project_id=pfa.project_id))
) --14090312
AND pfa.action_flag = 'CM'
AND NOT EXISTS (SELECT pf.project_id
FROM pa_projects_for_accum pf
WHERE pf.request_id = pfa.request_id
AND pf.segment1 = pfa.segment1
AND pf.action_flag IN ('PT', 'CL', 'BK', 'TR')));
INSERT INTO pa_proj_summ_tmp (project_id) -- BUG 10107257 Using global temp table instead of Bulk collect
(SELECT ppr.project_id
-- BULK COLLECT INTO TmpProjectTab
FROM pa_projects ppr
WHERE NVL(ppr.template_flag,'N') <> 'Y'
AND (
((ppr.segment1 BETWEEN p_project_num_from AND p_project_num_to) and l_client_extn_mode='N') OR
(l_client_extn_mode = 'Y' and exists (Select 1 from PA_PROJ_REFRESH_TMP tmp where tmp.project_id=ppr.project_id))
) --14090312
AND ppr.project_type = NVL(p_project_type, project_type)
AND Pa_Utils2.IsProjectInPurgeStatus(ppr.project_status_code) = 'N');
insert into pa_pjm_req_commitments_tmp
( project_id
,task_id
,req_number
,req_distribution_id
,requisition_header_id
,item_description
,expenditure_item_date
,req_line
,creation_date
,requestor_name
,approved_flag
,need_by_date
,expenditure_organization_id
,vendor_id
,vendor_name
,expenditure_type
,expenditure_category
,revenue_category
,unit_of_measure
,unit_price
,amount
,quantity
,denom_currency_code
,denom_amount
,acct_currency_code
,acct_rate_date
,acct_rate_type
,acct_exchange_rate
,inventory_item_id
,inventory_item_name
,uom_code
,wip_resource_id
,wip_resource_name
)
select /*+ leading(prj) */
pmprd.project_id
,pmprd.task_id
,pmprd.req_number
,pmprd.req_distribution_id
,pmprd.requisition_header_id
,pmprd.item_description
,pmprd.expenditure_item_date
,pmprd.req_line
,pmprd.creation_date
,pmprd.requestor_name
,pmprd.approved_flag
,pmprd.need_by_date
,pmprd.expenditure_organization_id
,pmprd.vendor_id
,pmprd.vendor_name
,pmprd.expenditure_type
,pmprd.expenditure_category
,pmprd.revenue_category
,pmprd.unit_of_measure
,pmprd.unit_price
,pmprd.amount
,pmprd.quantity
,pmprd.denom_currency_code
,pmprd.denom_amount
,pmprd.acct_currency_code
,pmprd.acct_rate_date
,pmprd.acct_rate_type
,pmprd.acct_exchange_rate
,pmprd.inventory_item_id
,pmprd.inventory_item_name
,pmprd.uom_code
,pmprd.wip_resource_id
,pmprd.wip_resource_name
from pjm_req_commitments_v pmprd ,pa_proj_summ_tmp prj -- BUG 10107257 Addition of global temp table
where pmprd.project_id = prj.project_id; -- BUG 10107257 Using global temp table instead of Bulk collect
insert into pa_pjm_po_commitments_tmp
(project_id
,task_id
,po_number
,po_distribution_id
,po_header_id
,item_description
,expenditure_item_date
,po_line
,creation_date
,approved_date
,requestor_name
,buyer_name
,approved_flag
,promised_date --- added for bug 10072148
,need_by_date --- added for bug 10072148
,expenditure_organization_id
,vendor_id
,vendor_name
,expenditure_type
,expenditure_category
,revenue_category
,unit_of_measure
,unit_price
,amount_outstanding_delivery
,quantity_outstanding_delivery
,quantity_ordered
,amount_ordered
,original_quantity_ordered
,original_amount_ordered
,quantity_cancelled
,amount_cancelled
,quantity_delivered
,quantity_invoiced
,amount_invoiced
,quantity_outstanding_invoice
,amount_outstanding_invoice
,quantity_overbilled
,amount_overbilled
,denom_currency_code
,denom_amt_outstanding_delivery
,acct_currency_code
,acct_rate_date
,acct_rate_type
,acct_exchange_rate
,inventory_item_id
,inventory_item_name
,uom_code
,wip_resource_id
,wip_resource_name
)
select /*+ leading(prj) */
pmppd.project_id,
pmppd.task_id,
pmppd.po_number,
pmppd.po_distribution_id,
pmppd.po_header_id,
pmppd.item_description,
pmppd.expenditure_item_date,
pmppd.po_line,
pmppd.creation_date,
pmppd.approved_date,
pmppd.requestor_name,
pmppd.buyer_name,
pmppd.approved_flag,
pmppd.promised_date,
pmppd.need_by_date,
pmppd.expenditure_organization_id,
pmppd.vendor_id,
pmppd.vendor_name,
pmppd.expenditure_type,
pmppd.expenditure_category,
pmppd.revenue_category,
pmppd.unit_of_measure,
pmppd.unit_price,
pmppd.amount_outstanding_delivery,
pmppd.quantity_outstanding_delivery,
pmppd.quantity_ordered,
pmppd.amount_ordered,
pmppd.original_quantity_ordered,
pmppd.original_amount_ordered,
pmppd.quantity_cancelled,
pmppd.amount_cancelled,
pmppd.quantity_delivered,
pmppd.quantity_invoiced,
pmppd.amount_invoiced,
pmppd.quantity_outstanding_invoice,
pmppd.amount_outstanding_invoice,
pmppd.quantity_overbilled,
pmppd.amount_overbilled,
pmppd.denom_currency_code,
pmppd.denom_amt_outstanding_delivery,
pmppd.acct_currency_code,
pmppd.acct_rate_date,
pmppd.acct_rate_type,
pmppd.acct_exchange_rate,
pmppd.inventory_item_id,
pmppd.inventory_item_name,
pmppd.uom_code,
pmppd.wip_resource_id,
pmppd.wip_resource_name
from pjm_po_commitments_v pmppd, pa_proj_summ_tmp prj -- BUG 10107257 Addition of global temp table
where pmppd.project_id = prj.project_id; --BUG 10107257 Using global temp table instead of Bulk collect
INSERT INTO pa_commitment_txns_tmp
(PROJECT_ID,
TASK_ID,
TRANSACTION_SOURCE,
LINE_TYPE,
CMT_NUMBER,
CMT_DISTRIBUTION_ID,
CMT_HEADER_ID,
DESCRIPTION,
EXPENDITURE_ITEM_DATE,
PA_PERIOD,
GL_PERIOD,
CMT_LINE_NUMBER,
CMT_CREATION_DATE,
CMT_APPROVED_DATE,
CMT_REQUESTOR_NAME,
CMT_BUYER_NAME,
CMT_APPROVED_FLAG,
CMT_PROMISED_DATE,
CMT_NEED_BY_DATE,
ORGANIZATION_ID,
VENDOR_ID,
VENDOR_NAME,
EXPENDITURE_TYPE,
EXPENDITURE_CATEGORY,
REVENUE_CATEGORY,
SYSTEM_LINKAGE_FUNCTION,
UNIT_OF_MEASURE,
UNIT_PRICE,
CMT_IND_COMPILED_SET_ID,
TOT_CMT_QUANTITY,
QUANTITY_ORDERED,
AMOUNT_ORDERED,
ORIGINAL_QUANTITY_ORDERED,
ORIGINAL_AMOUNT_ORDERED,
QUANTITY_CANCELLED,
AMOUNT_CANCELLED,
QUANTITY_DELIVERED,
QUANTITY_INVOICED,
AMOUNT_INVOICED,
QUANTITY_OUTSTANDING_DELIVERY,
AMOUNT_OUTSTANDING_DELIVERY,
QUANTITY_OUTSTANDING_INVOICE,
AMOUNT_OUTSTANDING_INVOICE,
QUANTITY_OVERBILLED,
AMOUNT_OVERBILLED,
ORIGINAL_TXN_REFERENCE1,
ORIGINAL_TXN_REFERENCE2,
ORIGINAL_TXN_REFERENCE3,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
acct_raw_cost,
acct_burdened_cost,
denom_currency_code,
denom_raw_cost,
denom_burdened_cost,
acct_currency_code,
acct_rate_date,
acct_rate_type,
acct_exchange_rate,
receipt_currency_code,
receipt_currency_amount,
receipt_exchange_rate,
inventory_item_id,
uom_code,
wip_resource_id,
resource_class
)
SELECT /*+ leading(prj) */
pctv.project_id,
pctv.task_id,
pctv.transaction_source,
pctv.line_type,
pctv.cmt_number,
pctv.cmt_distribution_id,
pctv.cmt_header_id,
pctv.description,
pctv.expenditure_item_date,
pctv.pa_period,
pctv.gl_period,
pctv.cmt_line_number,
pctv.cmt_creation_date,
pctv.cmt_approved_date,
pctv.cmt_requestor_name,
pctv.cmt_buyer_name,
pctv.cmt_approved_flag,
pctv.cmt_promised_date,
pctv.cmt_need_by_date,
pctv.organization_id,
pctv.vendor_id,
pctv.vendor_name,
pctv.expenditure_type,
pctv.expenditure_category,
pctv.revenue_category,
pctv.system_linkage_function,
pctv.unit_of_measure,
pctv.unit_price,
pctv.cmt_ind_compiled_set_id,
pctv.tot_cmt_quantity,
pctv.quantity_ordered,
pctv.amount_ordered,
pctv.original_quantity_ordered,
pctv.original_amount_ordered,
pctv.quantity_cancelled,
pctv.amount_cancelled,
pctv.quantity_delivered,
pctv.quantity_invoiced,
pctv.amount_invoiced,
pctv.quantity_outstanding_delivery,
pctv.amount_outstanding_delivery,
pctv.quantity_outstanding_invoice,
pctv.amount_outstanding_invoice,
pctv.quantity_overbilled,
pctv.amount_overbilled,
pctv.original_txn_reference1,
pctv.original_txn_reference2,
pctv.original_txn_reference3,
SYSDATE,
x_last_updated_by,
SYSDATE,
x_created_by,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
NULL,
pctv.acct_raw_cost,
pctv.acct_burdened_cost,
pctv.denom_currency_code,
pctv.denom_raw_cost,
pctv.denom_burdened_cost,
pctv.acct_currency_code,
pctv.acct_rate_date,
pctv.acct_rate_type,
pctv.acct_exchange_rate,
pctv.receipt_currency_code,
pctv.receipt_currency_amount,
pctv.receipt_exchange_rate,
pctv.inventory_item_id,
pctv.uom_code,
pctv.wip_resource_id,
pctv.resource_class
FROM pa_commitments_v1 pctv
--, pa_proj_summ_tmp prj -- BUG 10107257 Addition of global temp table
--changed to pa_commitments_v1 for bug 14221415
WHERE
NVL(pctv.system_linkage_function,'X') = NVL(p_system_linkage_function,NVL(pctv.system_linkage_function,'X'));