[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_last_update_date DATE ;
G_last_updated_by NUMBER ;
G_last_update_login NUMBER ;
SELECT 1
FROM pa_resource_list_assignments
WHERE project_id = p_project_id
AND resource_list_id = p_rl_id
AND P_resource_struct_type = 'RL'
UNION All
Select 1
From PA_RBS_PRJ_ASSIGNMENTS
Where Project_Id = p_project_id
And RBS_VERSION_ID = p_version_id
AND P_resource_struct_type = 'RBS' ;
G_last_update_login := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')), -1);
G_last_updated_by := G_created_by;
G_last_update_date := G_sysdate ;
SELECT RULE_ID
, RULE_NAME
, ALLOCATION_METHOD
, TARGET_EXP_TYPE_CLASS
, TARGET_EXP_ORG_ID
, TARGET_EXP_TYPE
, TARGET_COST_TYPE
, POOL_PERCENT
, PERIOD_TYPE
, SOURCE_AMOUNT_TYPE
, SOURCE_BALANCE_CATEGORY
, SOURCE_BALANCE_TYPE
, ALLOC_RESOURCE_LIST_ID
, AUTO_RELEASE_FLAG
, IMP_WITH_EXCEPTION
, DUP_TARGETS_FLAG
, OFFSET_EXP_TYPE_CLASS
, OFFSET_EXP_ORG_ID
, OFFSET_EXP_TYPE
, OFFSET_COST_TYPE
, OFFSET_METHOD
, OFFSET_PROJECT_ID
, OFFSET_TASK_ID
, BASIS_METHOD
, BASIS_RELATIVE_PERIOD
, BASIS_AMOUNT_TYPE
, BASIS_BALANCE_CATEGORY
, BASIS_BUDGET_TYPE_CODE
, BASIS_FIN_PLAN_TYPE_ID /* added bug 2619977 */
, BASIS_BUDGET_ENTRY_METHOD_CODE
, BASIS_BALANCE_TYPE
, BASIS_RESOURCE_LIST_ID
, SOURCE_EXTN_FLAG
, TARGET_EXTN_FLAG
, FIXED_AMOUNT
, NVL(START_DATE_ACTIVE, G_sysdate) START_DATE_ACTIVE
, NVL(END_DATE_ACTIVE, G_sysdate) END_DATE_ACTIVE
, ORG_ID
, LIMIT_TARGET_PROJECTS_CODE
/* FP.M : Allocation Impact : Bug # 3512552 */
, ALLOC_RESOURCE_STRUCT_TYPE
, BASIS_RESOURCE_STRUCT_TYPE
, ALLOC_RBS_VERSION
, BASIS_RBS_VERSION
FROM pa_alloc_rules_all
WHERE rule_id = p_rule_id ;
select '1'
from dual
where exists ( select 'Y'
from pa_alloc_run_targets
where run_id = x_run_id
and exclude_flag <> 'Y' ) ;
SELECT pa_alloc_runs_s.nextval
INTO v_run_id
FROM dual;
IF (v_mode = 'DRAFT' OR v_mode = 'DELETE') THEN /* for 2176096 */
pa_debug.G_err_stage := 'GET FISCAL YEAR QUARTER for '||p_run_period;
pa_debug.G_err_stage := 'INSERTING ALLOC RUNS';
insert_alloc_runs( x_run_id
, p_rule_id
, p_run_period
, p_expnd_item_date
, G_creation_date
, G_created_by
, G_last_update_date
, G_last_updated_by
, G_last_update_login
, v_alloc_rule_rec.pool_percent
, v_alloc_rule_rec.period_type
, v_alloc_rule_rec.source_amount_type
, v_alloc_rule_rec.source_balance_category
, v_alloc_rule_rec.source_balance_type
, v_alloc_rule_rec.alloc_resource_list_id
, v_alloc_rule_rec.auto_release_flag
, v_alloc_rule_rec.allocation_method
, v_alloc_rule_rec.imp_with_exception
, v_alloc_rule_rec.dup_targets_flag
, v_alloc_rule_rec.target_exp_type_class
, v_alloc_rule_rec.target_exp_org_id
, v_alloc_rule_rec.target_exp_type
, v_alloc_rule_rec.target_cost_type
, v_alloc_rule_rec.offset_exp_type_class
, v_alloc_rule_rec.offset_exp_org_id
, v_alloc_rule_rec.offset_exp_type
, v_alloc_rule_rec.offset_cost_type
, v_alloc_rule_rec.offset_method
, v_alloc_rule_rec.offset_project_id
, v_alloc_rule_rec.offset_task_id
, 'IP' /* In-Process as initial status */
, v_alloc_rule_rec.basis_method
, v_alloc_rule_rec.basis_relative_period
, v_alloc_rule_rec.basis_amount_type
, v_alloc_rule_rec.basis_balance_category
, v_alloc_rule_rec.basis_budget_type_code
, v_alloc_rule_rec.basis_balance_type
, v_alloc_rule_rec.basis_resource_list_id
, v_period_year /* fiscal_year */
, v_quarter
, v_period_num /* p_period_num */
, NULL /* p_target_exp_group */
, NULL /* p_offset_exp_group */
, NULL /* p_total_pool_amount */
, NULL /* p_allocated_amount */
, NULL /* p_reversal_date */
, v_request_id
, G_sysdate /*p_draft_request_date */
, NULL /* p_release_request_id */
, NULL /* p_release_request_date */
, G_denom_currency_code
, v_alloc_rule_rec.fixed_amount
, NULL /* rev_target_exp_group */
, NULL /* rev_offset_exp_group */
, v_alloc_rule_rec.ORG_ID
, v_alloc_rule_rec.limit_target_projects_code
/* FP.M : Allocation Impact : Bug # 3512552 */
, Null /* p_CINT_RATE_NAME */
, v_alloc_rule_rec.ALLOC_RESOURCE_STRUCT_TYPE
, v_alloc_rule_rec.BASIS_RESOURCE_STRUCT_TYPE
, v_alloc_rule_rec.ALLOC_RBS_VERSION
, v_alloc_rule_rec.BASIS_RBS_VERSION
);
UPDATE pa_alloc_runs
SET run_status = DECODE( v_mode, 'RELEASE', 'RS', 'DS'),
release_request_id = decode(v_mode, 'RELEASE',v_request_id, NULL),
release_request_date = decode(v_mode, 'RELEASE',sysdate, NULL)
WHERE run_id = x_run_id;
UPDATE pa_alloc_runs
SET run_status = DECODE( v_mode, 'RELEASE', 'RF', 'DF'),
release_request_id = decode(v_mode, 'RELEASE',v_request_id, NULL),
release_request_date = decode(v_mode, 'RELEASE',sysdate, NULL)
WHERE run_id = x_run_id;
pa_debug.G_err_stage := 'UPDATED RUN STATUS AS FAILURE';
UPDATE pa_alloc_runs
SET run_status = DECODE( v_mode, 'RELEASE', 'RF',
'DRAFT', 'DF',
run_status ),
release_request_id = decode(v_mode, 'RELEASE',v_request_id, NULL),
release_request_date = decode(v_mode, 'RELEASE',sysdate, NULL)
WHERE run_id = x_run_id;
SELECT max(run_id)
FROM pa_alloc_runs
WHERE rule_id = p_rule_id
AND run_id <> x_run_id;
SELECT run_status
FROM pa_alloc_runs
WHERE run_id = p_prev_run_id ;
x_mode := 'DELETE';
, p_last_updated_date IN DATE
, p_last_updated_by IN NUMBER
, p_last_update_login IN NUMBER
, p_level_code IN VARCHAR2
, p_exception_type IN VARCHAR2
, p_project_id IN NUMBER
, p_task_id IN NUMBER
, p_exception_code IN VARCHAR2 ) IS
BEGIN
pa_debug.set_err_stack('ins_alloc_exceptions');
INSERT INTO pa_alloc_exceptions (
RUN_ID
, RULE_ID
, LEVEL_CODE
, EXCEPTION_TYPE
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROJECT_ID
, TASK_ID
, EXCEPTION_CODE )
VALUES (
p_run_id
, p_rule_id
, p_level_code
, p_exception_type
, p_creation_date
, p_created_by
, p_last_updated_date
, p_last_updated_by
, p_last_update_login
, p_project_id
, p_task_id
, p_exception_code );
, p_insert_flag IN VARCHAR2 DEFAULT 'Y'
, p_project_id IN NUMBER DEFAULT NULL
, p_task_id IN NUMBER DEFAULT NULL ) IS
v_mesg_code VARCHAR2(30);
IF ( p_insert_flag = 'Y') THEN
ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login,
p_level, p_type, p_project_id, p_task_id, v_mesg_code );
SELECT 1
FROM pa_alloc_source_lines
WHERE rule_id = p_rule_id ;
SELECT 1
FROM pa_alloc_GL_lines
WHERE rule_id = p_rule_id ;
SELECT project_id, task_id, exclude_flag, billable_only_flag
FROM pa_alloc_target_lines
WHERE rule_id = p_rule_id;
SELECT 1
FROM pa_alloc_target_lines
WHERE rule_id = p_rule_id;
SELECT 1
FROM pa_tasks pt
WHERE pt.task_id = p_task_id
AND nvl(pt.chargeable_flag, 'N') = 'Y'
AND ( trunc(p_expnd_item_date) BETWEEN trunc(nvl(pt.start_date,p_expnd_item_date))
AND trunc(NVL(pt.completion_date, p_expnd_item_date)) )
AND ( pa_project_utils.check_project_action_allowed( p_project_id, 'NEW_TXNS') = 'Y')
AND (pa_project_stus_utils.is_project_closed( p_project_id )= 'N');
SELECT 1
FROM pa_expenditure_types
WHERE expenditure_type = p_exp_type
AND TRUNC(G_sysdate) BETWEEN TRUNC(start_date_active)
AND TRUNC(nvl(end_date_active, G_sysdate));
SELECT nvl(sum(NVL(line_percent,0)),0)
FROM pa_alloc_target_lines
WHERE rule_id = p_rule_id;
SELECT 1
FROM pa_tasks
WHERE task_id = p_task_id
AND billable_flag = p_billable_only_flag;
PROCEDURE insert_alloc_run_sources( p_rule_id IN NUMBER
, p_run_id IN NUMBER
, p_line_num IN NUMBER
, p_project_id IN NUMBER
, p_task_id IN NUMBER
, p_exclude_flag IN VARCHAR2
, p_creation_date IN DATE
, p_created_by IN NUMBER
, p_last_update_date IN DATE
, p_last_updated_by IN NUMBER
, p_last_update_login IN NUMBER) IS
CURSOR source_exists IS
SELECT 1
FROM pa_alloc_run_sources
WHERE run_id = p_run_id
AND project_id = p_project_id
AND task_id = p_task_id;
allow_insert_flag VARCHAR2(1);
/* allow insert if current proj-task not exists */
allow_insert_flag := 'Y';
allow_insert_flag := 'N';
IF (allow_insert_flag = 'Y') THEN
INSERT INTO PA_ALLOC_RUN_SOURCES (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, EXCLUDE_FLAG
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, TASK_ID )
VALUES (
p_run_id
, p_rule_id
, p_line_num
, p_project_id
, p_exclude_flag
, p_creation_date
, p_created_by
, p_last_update_date
, p_last_updated_by
, p_last_update_login
, p_task_id );
END insert_alloc_run_sources;
SELECT project_id, task_id
FROM pa_alloc_run_sources
WHERE run_id = p_run_id
AND exclude_flag = 'Y'
AND project_id = p_project_id
AND NVL(task_id, NVL(p_task_id, -1)) = NVL(p_task_id, -1);
SELECT project_id, task_id
FROM pa_alloc_run_targets
WHERE run_id = p_run_id
AND exclude_flag = 'Y'
AND project_id = p_project_id
AND NVL(task_id, NVL(p_task_id, -1)) = NVL(p_task_id, -1);
l_select_clause VARCHAR2(80) ;
|| ' (select pt1.task_id FROM pa_tasks pt1 '
|| ' WHERE pt1.top_task_id = :lp_task_id '
|| ' AND pa_task_utils.check_child_exists(pt1.task_id)=0 )))' ;
l_select_clause := 'Select pt.project_id, pt.task_id, pt.top_task_id ' ;
x_sql_str := l_select_clause || l_from_clause || l_where_clause ;
SELECT exclude_flag
, line_num
, project_org_id
, task_org_id
, project_type
, class_category
, class_code
, service_type
, project_id
, task_id
FROM pa_alloc_source_lines
WHERE rule_id = p_rule_id
ORDER BY exclude_flag, line_num;
SELECT task_id
FROM pa_tasks
WHERE top_task_id = p_tsk_id
AND pa_task_utils.check_child_exists(task_id) = 0;
SELECT task_id
FROM pa_tasks
WHERE project_id = p_proj_id
AND pa_task_utils.check_child_exists(task_id) = 0;
SELECT project_id, task_id
FROM pa_alloc_run_sources
WHERE rule_id = p_rule_id
AND run_id = p_run_id;
SELECT top_task_id
FROM pa_tasks
WHERE top_task_id = p_tsk_id;
/* only valid project_id so insert */
insert_alloc_run_sources( p_rule_id
, p_run_id
, ( I * -1) /* line_num */
, v_cx_project_id
, v_cx_task_id
, v_cx_exclude_flag
, G_creation_date
, G_created_by
, G_last_update_date
, G_last_updated_by
, G_last_update_login );
/* include current project/task, so insert... */
insert_alloc_run_sources( p_rule_id
, p_run_id, 0
, v_cx_project_id
, v_cx_task_id
, v_cx_exclude_flag
, G_creation_date
, G_created_by
, G_last_update_date
, G_last_updated_by
, G_last_update_login );
/* include current project/task, so insert... */
insert_alloc_run_sources( p_rule_id
, p_run_id
, (I * -1) /* line_num */
, v_cx_project_id
, leaf_task_rec.task_id
, v_cx_exclude_flag
, G_creation_date
, G_created_by
, G_last_update_date
, G_last_updated_by
, G_last_update_login );
insert_alloc_run_sources( p_rule_id
, p_run_id
, source_lines_rec.line_num
, v_src_project_id
, v_src_task_id
, source_lines_rec.exclude_flag
, G_creation_date
, G_created_by
, G_last_update_date
, G_last_updated_by
, G_last_update_login );
/* include current project/task, so insert */
insert_alloc_run_sources( p_rule_id
, p_run_id
, to_number(I*-1)
, v_cx_project_id
, each_task.task_id
, 'N'
, G_creation_date
, G_created_by
, G_last_update_date
, G_last_updated_by
, G_last_update_login);
/* include current project/task, so insert... */
insert_alloc_run_sources( p_rule_id
, p_run_id
, ( I * -1) /* line_num */
, v_cx_project_id
, v_cx_task_id
, v_cx_exclude_flag
, G_creation_date
, G_created_by
, G_last_update_date
, G_last_updated_by
, G_last_update_login );
/* include current project/task, so insert... */
insert_alloc_run_sources( p_rule_id
, p_run_id
, (I * -1) /* line_num */
, v_cx_project_id
, leaf_task_rec.task_id
, v_cx_exclude_flag
, G_creation_date
, G_created_by
, G_last_update_date
, G_last_updated_by
, G_last_update_login );
/* include current project/task, so insert */
insert_alloc_run_sources( p_rule_id
, p_run_id
, to_number(source_lines_rec.line_num)
, v_src_project_id
, v_src_task_id
, source_lines_rec.exclude_flag
, G_creation_date
, G_created_by
, G_last_update_date
, G_last_updated_by
, G_last_update_login);
/* include current project/task, so insert */
insert_alloc_run_sources( p_rule_id
, p_run_id
, source_lines_rec.line_num
, v_src_project_id
, leaf_task_rec.task_id
, source_lines_rec.exclude_flag
, G_creation_date
, G_created_by
, G_last_update_date
, G_last_updated_by
, G_last_update_login );
PROCEDURE insert_alloc_run_targets( p_rule_id IN NUMBER
, p_run_id IN NUMBER
, p_line_num IN NUMBER
, p_project_id IN NUMBER
, p_task_id IN NUMBER
, p_line_percent IN NUMBER
, p_exclude_flag IN VARCHAR2
, p_creation_date IN DATE
, p_created_by IN NUMBER
, p_last_update_date IN DATE
, p_last_updated_by IN NUMBER
, p_last_update_login IN NUMBER
, p_bas_method IN VARCHAR2
, p_dup_targets_flag IN VARCHAR2 ) IS
CURSOR target_exists IS
SELECT 1
FROM pa_alloc_run_targets
WHERE run_id = p_run_id
AND project_id = p_project_id
AND task_id = p_task_id;
allow_insert_flag VARCHAR2(1):= 'Y';
pa_debug.set_err_stack('insert_alloc_run_targets');
/* allow insert only if current proj-task does NOT exist */
OPEN target_exists;
allow_insert_flag := 'Y';
allow_insert_flag := 'N';
IF (allow_insert_flag = 'Y') THEN
/* added if condition for bug 2619977 */
/* Invoking FP API to get budget_version_id. Will populate the ID in
pa_alloc_run_targets based on budget type or FP type selected. After
this processing become same for both basis - budgets and FPs */
PA_FIN_PLAN_UTILS.GET_COST_BASE_VERSION_INFO
( p_project_id
,G_basis_fin_plan_Type_id
,G_basis_budget_type_code
,x_budget_version_id
,x_return_status
,x_msg_count
,x_msg_data
);
pa_debug.write_file('insert_alloc_run_targets: ' || 'LOG','Fetching budget version id for project ['|| to_char(p_project_id) ||
'] Fin plan type ['||to_char(G_basis_fin_plan_Type_id) ||
'] Budget type code ['||G_basis_budget_type_code ||
'] Budget version ['||to_char(x_budget_version_id) ||
'] Return status ['||x_return_status||']' );
INSERT INTO PA_ALLOC_RUN_TARGETS (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, EXCLUDE_FLAG
, TASK_ID
, LINE_PERCENT
, BUDGET_VERSION_ID /* added bug 2619977 */
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
VALUES (
p_run_id
, p_rule_id
, p_line_num
, p_project_id
, p_exclude_flag
, p_task_id
, DECODE( p_bas_method, 'S', NULL, 'P', NULL, p_line_percent )
, x_budget_version_id /* added bug 2619977 */
, p_creation_date
, p_created_by
, p_last_update_date
, p_last_updated_by
, p_last_update_login );
END insert_alloc_run_targets;
l_select_clause VARCHAR2(80) ;
|| ' (select pt1.task_id FROM pa_tasks pt1 '
|| ' WHERE pt1.top_task_id = :lp_task_id '
|| ' AND pt1.chargeable_flag = ''Y'' )))' ;
l_select_clause := 'Select pt.project_id, pt.task_id, pt.top_task_id ' ;
x_sql_str := l_select_clause || l_from_clause || l_where_clause ;
SELECT exclude_flag
, line_num
, project_org_id
, task_org_id
, project_type
, class_category
, class_code
, service_type
, project_id
, task_id
, billable_only_flag
, line_percent
FROM pa_alloc_target_lines
WHERE rule_id = p_rule_id
ORDER BY 1, 2;
select project_id, task_id
from pa_tasks pt
where pt.project_id = x_project_id
AND pt.chargeable_flag = 'Y'
AND x_ei_date between nvl(pt.start_date,x_ei_date) and nvl(pt.completion_date,x_ei_date) ;
SELECT project_id, task_id
FROM pa_alloc_run_targets
WHERE run_id = p_run_id;
SELECT entry_level_code
FROM pa_budget_entry_methods
WHERE budget_entry_method_code = p_bas_budget_entry_method_code;
SELECT entry_level_code
FROM pa_budget_entry_methods
WHERE budget_entry_method_code =
( SELECT budget_entry_method_code
FROM pa_budget_versions
WHERE project_id = p_proj_id
AND current_flag = 'Y'
AND budget_type_code = p_bas_budget_type_code
AND budget_entry_method_code = p_bas_budget_entry_method_code );
SELECT 1
FROM pa_alloc_target_lines
WHERE rule_id = p_rule_id;
SELECT distinct line_num, nvl(line_percent,0) line_percent
FROM pa_alloc_run_targets
WHERE rule_id = p_rule_id
AND run_id = p_run_id;
insert_alloc_run_targets( p_rule_id, p_run_id
, (I * -1) /* line_num */
, v_cx_project_id
, v_cx_task_id
, v_cx_percent
, v_cx_exclude_flag
, G_creation_date
, G_created_by
, G_last_update_date
, G_last_updated_by
, G_last_update_login
, x_basis_method
, p_dup_targets_flag );
insert_alloc_run_targets( p_rule_id
, p_run_id
, target_lines_rec.line_num
, v_tgt_project_id
, v_tgt_task_id
, target_lines_rec.line_percent
, target_lines_rec.exclude_flag
, G_creation_date
, G_created_by
, G_last_update_date
, G_last_updated_by
, G_last_update_login
, x_basis_method
, p_dup_targets_flag );
/* include current project/task, so insert... */
insert_alloc_run_targets( p_rule_id, p_run_id
, (I * -1) /* line_num */
, v_cx_project_id
, v_cx_task_id
, v_cx_percent
, v_cx_exclude_flag
, G_creation_date
, G_created_by
, G_last_update_date
, G_last_updated_by
, G_last_update_login
, x_basis_method
, p_dup_targets_flag );
insert_alloc_run_targets( p_rule_id, p_run_id
, (I * -1) /* line_num */
, v_cx_project_id
, chargeable_tasks.task_id
, v_cx_percent
, v_cx_exclude_flag
, G_creation_date
, G_created_by
, G_last_update_date
, G_last_updated_by
, G_last_update_login
, x_basis_method
, p_dup_targets_flag );
/* include current project/task, so insert */
insert_alloc_run_targets( p_rule_id
, p_run_id
, target_lines_rec.line_num
, v_tgt_project_id
, v_tgt_task_id
, target_lines_rec.line_percent
, target_lines_rec.exclude_flag
, G_creation_date
, G_created_by
, G_last_update_date
, G_last_updated_by
, G_last_update_login
, x_basis_method
, p_dup_targets_flag );
PROCEDURE insert_alloc_run_GL_det ( p_run_id IN NUMBER
, p_rule_id IN NUMBER
, p_line_num IN NUMBER
, p_source_ccid IN NUMBER
, p_subtract_flag IN VARCHAR2
, p_creation_date IN DATE
, p_created_by IN NUMBER
, p_last_update_date IN DATE
, p_last_updated_by IN NUMBER
, p_last_update_login IN NUMBER
, p_source_percent IN NUMBER
, p_amount IN NUMBER
, p_eligible_amount IN NUMBER ) IS
BEGIN
pa_debug.set_err_stack('insert_alloc_run_GL_det');
INSERT INTO pa_alloc_run_gl_det(
RUN_ID
, RULE_ID
, LINE_NUM
, SOURCE_CCID
, SUBTRACT_FLAG
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, SOURCE_PERCENT
, AMOUNT
, ELIGIBLE_AMOUNT )
VALUES (
p_run_id
, p_rule_id
, p_line_num
, p_source_ccid
, p_subtract_flag
, p_creation_date
, p_created_by
, p_last_update_date
, p_last_updated_by
, p_last_update_login
, p_source_percent
, p_amount
, p_eligible_amount);
END insert_alloc_run_GL_det;
SELECT source_ccid
, nvl(source_percent,100) source_percent
, subtract_flag
, line_num
FROM pa_alloc_gl_lines
WHERE rule_id = p_rule_id;
SELECT a.set_of_books_id
, b.accounted_period_type
, b.currency_code
FROM pa_implementations a
, gl_sets_of_books b
WHERE a.set_of_books_id = b.set_of_books_id;
SELECT nvl(sum( nvl(eligible_amount,0)*DECODE(subtract_flag,'Y',-1,1) ),0)
FROM pa_alloc_run_GL_det
WHERE run_id = p_run_id;
SELECT NVL(period_net_dr,0) - NVL(period_net_cr, 0) +
decode(p_amount_type, 'FYTD',NVL(begin_balance_dr, 0), 'QTD'
, NVL(quarter_to_date_dr, 0), 0) -
decode(p_amount_type, 'FYTD', NVL(begin_balance_cr, 0), 'QTD'
, NVL(quarter_to_date_cr, 0), 0)
FROM gl_balances
WHERE ledger_id = p_sob_id
AND code_combination_id = p_source_ccid /** .source_ccid */
AND currency_code = p_currency_code
AND period_name = p_run_period
AND actual_flag = 'A'
AND translated_flag IS NULL;
SELECT nvl(pool_percent,100) pool_percent
FROM pa_alloc_runs
WHERE run_id = p_run_id;
/* then insert into gl_source_det */
insert_alloc_run_GL_det( p_run_id
, p_rule_id
, gl_source_rec.line_num
, gl_source_rec.source_ccid
, gl_source_rec.subtract_flag
, G_creation_date
, G_created_by
, G_last_update_date
, G_last_updated_by
, G_last_update_login
, gl_source_rec.source_percent
, v_amount
, pa_currency.round_currency_amt(v_amount*
(gl_source_rec.source_percent/100)*
( v_pool_percent/100))
) ;
pa_debug.write_file('calculate_src_GL_amounts: ' || 'LOG', 'After insert into alloc_run_Gl' );
SELECT count(task_id)
FROM pa_alloc_run_targets
WHERE run_id = p_run_id
AND line_num = p_line_num;
PROCEDURE insert_missing_costs( p_run_id IN NUMBER
, p_type_code IN VARCHAR2
, p_project_id IN NUMBER
, p_task_id IN NUMBER
, p_amount IN NUMBER ) IS
BEGIN
pa_debug.set_err_stack('Insert missing project costs');
INSERT INTO pa_alloc_missing_costs (
RUN_ID
, TYPE_CODE
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROJECT_ID
, TASK_ID
, AMOUNT)
VALUES (
p_run_id
, p_type_code
, G_creation_date
, G_created_by
, G_last_update_date
, G_last_updated_by
, G_last_update_login
, p_project_id
, p_task_id
, p_amount);
END insert_missing_costs;
SELECT a.run_id run_id
, a.project_id project_id
, a.task_id task_id
, nvl(a.eligible_amount,0) eligible_amount
FROM pa_alloc_run_source_det a,
pa_alloc_runs ar
WHERE a.run_id = ar.run_id
AND a.rule_id = p_rule_id
AND ar.fiscal_year = NVL(p_fscl_year, ar.fiscal_year)
AND ar.quarter = NVL(p_qrtr_num, ar.quarter)
AND ar.period_num = NVL(p_prd_num, ar.period_num )
AND a.run_id < p_run_id
AND NOT EXISTS ( SELECT 1
FROM pa_alloc_run_source_det b
WHERE a.project_id = b.project_id
AND a.task_id = b.task_id
AND b.rule_id = p_rule_id
AND b.run_id = p_run_id )
AND a.run_id =( SELECT max(c.run_id)
FROM pa_alloc_run_source_det c,
pa_alloc_runs c_ar -- added this table to exclude reversed runs.
WHERE c.project_id = a.project_id
AND c.task_id = a.task_id
AND c.run_id = c_ar.run_id
AND c_ar.rule_id = p_rule_id
AND c_ar.run_status = 'RS'
AND c.run_id < p_run_id )
AND a.project_id > 0 -- This is added to ignore the missing cost from fixed amount.
AND a.project_id = nvl(p_src_proj_id, a.project_id) ;
SELECT A.Run_Id Run_Id,
A.Project_Id Project_Id,
A.Task_Id Task_Id,
NVL ( A.Eligible_Amount, 0 ) Eligible_Amount
FROM PA_ALLOC_RUN_SOURCE_DET A ,
( --
-- The purpose of this in-line view is to return
-- the PA_ALLOC_RUNS (single) record for the largest Run_Id
-- less than the input p_Run_Id
--
SELECT MAX ( AR.Run_Id ) AS Run_Id
FROM PA_ALLOC_RUNS AR
WHERE AR.Fiscal_Year = NVL ( p_Fscl_Year , AR.Fiscal_Year )
AND AR.Quarter = NVL ( p_Qrtr_Num , AR.Quarter )
AND AR.Period_Num = NVL ( p_Prd_Num , AR.Period_Num )
AND AR.Run_Id < p_Run_Id
AND AR.Rule_Id = p_Rule_Id
AND AR.Run_Status = 'RS'
AND NVL(AR.allocated_amount,0) <> 0 -- added this as part of the bug 13556282
) AR
WHERE A.Run_Id = AR.Run_Id
AND A.Rule_Id = p_Rule_Id
AND NOT EXISTS
( SELECT 1
FROM PA_ALLOC_RUN_SOURCE_DET B
WHERE A.Project_Id = B.Project_Id
AND A.Task_Id = B.Task_Id
AND B.Rule_Id = p_Rule_Id
AND B.Run_Id = p_Run_Id
)
AND A.Project_Id > 0
AND A.Project_Id = NVL ( p_Src_Proj_Id, A.Project_Id ) ;
SELECT a.run_id run_id
, a.project_id project_id
, a.task_id task_id
, nvl(a.Total_allocation,0) Total_allocation
FROM pa_alloc_txn_details a,
pa_alloc_runs ar
WHERE a.run_id = ar.run_id
AND a.rule_id = p_rule_id
AND ar.rule_id = p_rule_id /* Bug 10191044 */
AND ar.fiscal_year = NVL(p_fscl_year, ar.fiscal_year)
AND ar.quarter = NVL(p_qrtr_num, ar.quarter)
AND ar.period_num = NVL(p_prd_num, ar.period_num )
AND a.run_id < p_run_id
AND a.transaction_type = 'T'
AND NOT EXISTS ( SELECT 1
FROM pa_alloc_run_targets b
WHERE b.project_id = a.project_id
AND b.task_id = a.task_id
AND b.exclude_flag = 'N'
AND b.run_id = p_run_id )
AND a.run_id =( SELECT max(c.run_id)
FROM pa_alloc_txn_details c,
pa_alloc_runs c_ar -- added this table to exclude reversed runs.
WHERE c.project_id = a.project_id
AND c.task_id = a.task_id
AND c.transaction_type = 'T'
AND c.run_id = c_ar.run_id
AND c_ar.rule_id = p_rule_id
AND c_ar.run_status = 'RS'
AND c.run_id < p_run_id );
insert_missing_costs( p_run_id
,'S'
,src_sunk_cost_rec.project_id
,src_sunk_cost_rec.task_id
,src_sunk_cost_rec.eligible_amount);
insert_missing_costs( p_run_id
,'T'
,trg_sunk_cost_rec.project_id
,trg_sunk_cost_rec.task_id
,trg_sunk_cost_rec.Total_allocation);
SELECT nvl(sum(nvl(a.current_allocation,0)),0)
FROM pa_alloc_txn_details a,
pa_alloc_runs b
WHERE b.rule_id = p_rule_id
AND b.run_id < p_run_id
AND b.quarter = nvl(p_qrtr_num, b.quarter)
AND b.fiscal_year = nvl(p_fscl_year, b.fiscal_year)
AND b.period_num = nvl(p_prd_num, b.period_num)
AND b.run_id = a.run_id
AND a.transaction_type = p_type
AND a.project_id = p_project_id
AND a.task_id = p_task_id
AND b.reversal_date is NULL
AND b.run_status <> 'DL'; /* for bug 2176096 */
PROCEDURE insert_alloc_txn_details( x_alloc_txn_id IN OUT NOCOPY NUMBER
, p_run_id IN NUMBER
, p_rule_id IN NUMBER
, p_transaction_type IN VARCHAR2
, p_fiscal_year IN NUMBER
, p_quarter_num IN NUMBER
, p_period_num IN NUMBER
, p_run_period IN VARCHAR2
, p_line_num IN NUMBER
, p_project_id IN NUMBER
, p_task_id IN NUMBER
, p_expenditure_type IN VARCHAR2
, p_total_allocation IN NUMBER
, p_previous_allocation IN NUMBER
, p_current_allocation IN NUMBER
/* PA.L:Added for Capitalized Interest */
, p_EXPENDITURE_ID IN NUMBER DEFAULT NULL
, p_EXPENDITURE_ITEM_ID IN NUMBER DEFAULT NULL
, p_CINT_SOURCE_TASK_ID IN NUMBER DEFAULT NULL
, p_CINT_EXP_ORG_ID IN NUMBER DEFAULT NULL
, p_CINT_RATE_MULTIPLIER IN NUMBER DEFAULT NULL
, p_CINT_PRIOR_BASIS_AMT IN NUMBER DEFAULT NULL
, p_CINT_CURRENT_BASIS_AMT IN NUMBER DEFAULT NULL
, p_REJECTION_CODE IN VARCHAR2 DEFAULT NULL
, p_STATUS_CODE IN VARCHAR2 DEFAULT NULL
, p_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL
, p_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL
, p_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL
, p_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL
, p_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL
, p_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL
, p_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL
, p_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL
, p_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL
, p_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL
, p_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL
/* PA.L : end */
) IS
v_attribute_category VARCHAR2(30);
pa_debug.set_err_stack('Insert Alloc Txn Details');
pa_debug.write_file('insert_alloc_txn_details: ' || 'LOG',v_err_message);
pa_debug.write_file('insert_alloc_txn_details: ' || 'LOG',v_err_message);
Select pa_alloc_txn_details_s.nextval
Into x_alloc_txn_id
From Dual;
INSERT INTO pa_alloc_txn_details (
RUN_ID
, RULE_ID
, TRANSACTION_TYPE
, FISCAL_YEAR
, QUARTER_NUM
, PERIOD_NUM
, RUN_PERIOD
, LINE_NUM
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROJECT_ID
, TASK_ID
, EXPENDITURE_TYPE
, TOTAL_ALLOCATION
, PREVIOUS_ALLOCATION
, CURRENT_ALLOCATION
, ALLOC_TXN_ID
, ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,EXPENDITURE_ID
,EXPENDITURE_ITEM_ID
,CINT_SOURCE_TASK_ID
,CINT_EXP_ORG_ID
,CINT_RATE_MULTIPLIER
,CINT_PRIOR_BASIS_AMT
,CINT_CURRENT_BASIS_AMT
,REJECTION_CODE
,STATUS_CODE
)
VALUES (
p_run_id
, p_rule_id
, p_transaction_type
, p_fiscal_year
, p_quarter_num
, p_period_num
, p_run_period
, p_line_num
, G_creation_date
, G_created_by
, G_last_update_date
, G_last_updated_by
, G_last_update_login
, p_project_id
, p_task_id
, p_expenditure_type
, p_total_allocation
, p_previous_allocation
, p_current_allocation
, x_alloc_txn_id --, pa_alloc_txn_details_s.nextval
,decode(p_rule_id,-1,p_attribute_category,v_attribute_category)
,decode(p_rule_id,-1,p_attribute1,v_attribute1)
,decode(p_rule_id,-1,p_attribute2,v_attribute2)
,decode(p_rule_id,-1,p_attribute3,v_attribute3)
,decode(p_rule_id,-1,p_attribute4,v_attribute4)
,decode(p_rule_id,-1,p_attribute5,v_attribute5)
,decode(p_rule_id,-1,p_attribute6,v_attribute6)
,decode(p_rule_id,-1,p_attribute7,v_attribute7)
,decode(p_rule_id,-1,p_attribute8,v_attribute8)
,decode(p_rule_id,-1,p_attribute9,v_attribute9)
,decode(p_rule_id,-1,p_attribute10,v_attribute10)
,p_EXPENDITURE_ID
,p_EXPENDITURE_ITEM_ID
,p_CINT_SOURCE_TASK_ID
,p_CINT_EXP_ORG_ID
,p_CINT_RATE_MULTIPLIER
,p_CINT_PRIOR_BASIS_AMT
,p_CINT_CURRENT_BASIS_AMT
,p_REJECTION_CODE
,p_STATUS_CODE
);
END insert_alloc_txn_details;
SELECT nvl(sum( nvl(basis_percent, 0)*nvl(line_percent,100)/10000),0) basis
FROM pa_alloc_run_basis_det
WHERE run_id = p_run_id
AND line_num = p_line_num
AND project_id = p_project_id
AND task_id = p_task_id ;
SELECT line_num
, project_id
, task_id
, line_percent
, exclude_flag
FROM pa_alloc_run_targets
WHERE run_id = p_run_id
AND exclude_flag = 'N';
SELECT count(task_id)
FROM pa_alloc_run_targets
WHERE run_id = p_run_id
AND exclude_flag = 'N';
SELECT COUNT(DISTINCT line_num)
FROM pa_alloc_run_targets
WHERE run_id = p_run_id
AND exclude_flag = 'N';
select NVL(sum ( nvl(par1.allocated_amount,0)),0)
from pa_alloc_runs par1
where par1.run_id < p_run_id
and par1.rule_id = p_rule_id
and par1.fiscal_year = nvl(v_fiscal_year, par1.fiscal_year)
and par1.quarter = nvl(v_quarter_num, par1.quarter)
and par1.period_num = nvl(v_period_num, par1.period_num)
and par1.reversal_date is NULL
and par1.run_status <> 'DL'; /* for bug 2176096 */
pa_debug.G_err_stage:= 'INSERTING INTO ALLOC TXN DETAILS';
/* insert into alloc_txn_details */
--
-- Bug: 983057 Do not create txn with zero curren alloc amount
--
IF (v_curr_alloc_amount <> 0 ) THEN
/** modified to call the api by reference **/
---insert_alloc_txn_details( p_run_id
---, p_rule_id
---, 'T'
---, p_fiscal_year
--- , p_quarter_num
--- , p_period_num
--- , p_run_period
--- , run_target_rec.line_num
--- , run_target_rec.project_id
--- , run_target_rec.task_id
--- , p_expenditure_type
---, v_tot_alloc_amount
---, v_prev_alloc_amount
---, v_curr_alloc_amount );
insert_alloc_txn_details( x_alloc_txn_id => l_alloc_txn_id
, p_run_id => p_run_id
, p_rule_id => p_rule_id
, p_transaction_type => 'T'
, p_fiscal_year => p_fiscal_year
, p_quarter_num => p_quarter_num
, p_period_num => p_period_num
, p_run_period => p_run_period
, p_line_num => run_target_rec.line_num
, p_project_id => run_target_rec.project_id
, p_task_id => run_target_rec.task_id
, p_expenditure_type => p_expenditure_type
, p_total_allocation => v_tot_alloc_amount
, p_previous_allocation => v_prev_alloc_amount
, p_current_allocation => v_curr_alloc_amount
, p_EXPENDITURE_ID => NULL
, p_EXPENDITURE_ITEM_ID => NULL
, p_CINT_SOURCE_TASK_ID => NULL
, p_CINT_EXP_ORG_ID => NULL
, p_CINT_RATE_MULTIPLIER => NULL
, p_CINT_PRIOR_BASIS_AMT => NULL
, p_CINT_CURRENT_BASIS_AMT => NULL
, p_REJECTION_CODE => NULL
, p_STATUS_CODE => NULL
, p_ATTRIBUTE_CATEGORY => NULL
, p_ATTRIBUTE1 => NULL
, p_ATTRIBUTE2 => NULL
, p_ATTRIBUTE3 => NULL
, p_ATTRIBUTE4 => NULL
, p_ATTRIBUTE5 => NULL
, p_ATTRIBUTE6 => NULL
, p_ATTRIBUTE7 => NULL
, p_ATTRIBUTE8 => NULL
, p_ATTRIBUTE9 => NULL
, p_ATTRIBUTE10 => NULL
);
UPDATE pa_alloc_runs
SET total_pool_amount = nvl(p_pool_amount,0)
, allocated_amount = nvl(v_net_alloc_amount,0)+nvl(v_remnant_amount,0)
-- , Missing_source_proj_amt = v_src_sunk_cost -- commented as part of the bug 13949738
, Missing_target_proj_amt = v_tgt_sunk_cost
, Total_allocated_amount = nvl(v_sum_alloc_amts,0) + nvl(v_net_alloc_amount,0)+nvl(v_remnant_amount,0)-nvl(v_tgt_sunk_cost,0)
WHERE run_id = p_run_id;
SELECT line_num, project_id, task_id, nvl(sum(nvl(eligible_amount,0)),0) eligible_amount
FROM pa_alloc_run_source_det
WHERE run_id=p_run_id
GROUP BY line_num, project_id, task_id;
SELECT project_id, nvl(sum(nvl(eligible_amount,0)),0) eligible_amount
FROM pa_alloc_run_source_det
WHERE run_id = p_run_id
GROUP BY project_id ;
SELECT nvl(sum(nvl(eligible_amount,0)),0) pool_amount
FROM pa_alloc_run_source_det
WHERE run_id = p_run_id;
SELECT nvl(sum(nvl(current_allocation,0)),0)
from pa_alloc_txn_details pat
,pa_alloc_runs par
where pat.run_id = par.run_id
and par.fiscal_year = nvl(p_fiscal_year, par.fiscal_year)
and par.quarter = nvl(p_quarter_num,par.quarter)
and par.period_num = nvl(p_period_num , par.period_num)
and par.run_id < p_run_id
and par.rule_id = p_rule_id
and par.run_status = 'RS'
and pat.transaction_type = 'O'
and pat.project_id = v_project_id
and pat.task_id <> v_task_id ;
SELECT a.run_id run_id
, a.project_id project_id
, a.task_id task_id
, nvl(a.Total_allocation,0) Total_allocation
FROM pa_alloc_txn_details a,
pa_alloc_runs ar
WHERE a.run_id = ar.run_id
AND a.rule_id = p_rule_id
AND ar.fiscal_year = NVL(p_fscl_year, ar.fiscal_year)
AND ar.quarter = NVL(p_qrtr_num, ar.quarter)
AND ar.period_num = NVL(p_prd_num, ar.period_num )
AND a.run_id < p_run_id
AND a.transaction_type = 'O'
AND NOT EXISTS ( SELECT 1
FROM pa_alloc_txn_details b
WHERE b.project_id = a.project_id
AND b.task_id = a.task_id
AND b.transaction_type = 'O'
AND b.run_id = p_run_id )
AND a.run_id =( SELECT max(c.run_id)
FROM pa_alloc_txn_details c,
pa_alloc_runs c_ar -- added this table to exclude reversed runs.
WHERE c.project_id = a.project_id
AND c.task_id = a.task_id
AND c.transaction_type = 'O'
AND c.run_id = c_ar.run_id
AND c_ar.rule_id = p_rule_id
AND c_ar.run_status = 'RS'
AND c.run_id < p_run_id );
/* insert into alloc_txn_details */
--
-- Bug: 983057 Do not create txn with zero curren alloc amount
--
IF (v_curr_offset_amount <> 0 ) THEN
/** Calling the api by reference changed for capint */
--insert_alloc_txn_details( p_run_id
-- , p_rule_id
-- , 'O'
-- , p_fiscal_year
-- , p_quarter_num
-- , p_period_num
-- , p_run_period
-- , offset_det_rec.line_num
-- , offset_det_rec.project_id
-- , offset_det_rec.task_id
-- , p_expenditure_type
-- , v_tot_offset_amount
-- , v_prev_offset_amount
-- , v_curr_offset_amount );
insert_alloc_txn_details( x_alloc_txn_id => l_alloc_txn_id
, p_run_id => p_run_id
, p_rule_id => p_rule_id
, p_transaction_type => 'O'
, p_fiscal_year => p_fiscal_year
, p_quarter_num => p_quarter_num
, p_period_num => p_period_num
, p_run_period => p_run_period
, p_line_num => offset_det_rec.line_num
, p_project_id => offset_det_rec.project_id
, p_task_id => offset_det_rec.task_id
, p_expenditure_type => p_expenditure_type
, p_total_allocation => v_tot_offset_amount
, p_previous_allocation => v_prev_offset_amount
, p_current_allocation => v_curr_offset_amount
, p_EXPENDITURE_ID => NULL
, p_EXPENDITURE_ITEM_ID => NULL
, p_CINT_SOURCE_TASK_ID => NULL
, p_CINT_EXP_ORG_ID => NULL
, p_CINT_RATE_MULTIPLIER => NULL
, p_CINT_PRIOR_BASIS_AMT => NULL
, p_CINT_CURRENT_BASIS_AMT => NULL
, p_REJECTION_CODE => NULL
, p_STATUS_CODE => NULL
, p_ATTRIBUTE_CATEGORY => NULL
, p_ATTRIBUTE1 => NULL
, p_ATTRIBUTE2 => NULL
, p_ATTRIBUTE3 => NULL
, p_ATTRIBUTE4 => NULL
, p_ATTRIBUTE5 => NULL
, p_ATTRIBUTE6 => NULL
, p_ATTRIBUTE7 => NULL
, p_ATTRIBUTE8 => NULL
, p_ATTRIBUTE9 => NULL
, p_ATTRIBUTE10 => NULL
);
/* insert into alloc_txn_details */
--
-- Bug: 983057 Do not create txn with zero curren alloc amount
--
IF (v_curr_offset_amount <> 0 ) THEN
/* Start of capint changes Call by reference */
--insert_alloc_txn_details( p_run_id
-- , p_rule_id
-- , 'O'
-- , p_fiscal_year
-- , p_quarter_num
-- , p_period_num
-- , p_run_period
-- , 0 /* offset_proj_sum_rec.line_num */
-- , offset_proj_sum_rec.project_id
-- , v_task_id
-- , p_expenditure_type
-- , v_tot_offset_amount
-- , v_prev_offset_amount
-- , v_curr_offset_amount );
insert_alloc_txn_details( x_alloc_txn_id => l_alloc_txn_id
, p_run_id => p_run_id
, p_rule_id => p_rule_id
, p_transaction_type => 'O'
, p_fiscal_year => p_fiscal_year
, p_quarter_num => p_quarter_num
, p_period_num => p_period_num
, p_run_period => p_run_period
, p_line_num => 0 /* offset_proj_sum_rec.line_num */
, p_project_id => offset_proj_sum_rec.project_id
, p_task_id => v_task_id
, p_expenditure_type => p_expenditure_type
, p_total_allocation => v_tot_offset_amount
, p_previous_allocation => v_prev_offset_amount
, p_current_allocation => v_curr_offset_amount
, p_EXPENDITURE_ID => NULL
, p_EXPENDITURE_ITEM_ID => NULL
, p_CINT_SOURCE_TASK_ID => NULL
, p_CINT_EXP_ORG_ID => NULL
, p_CINT_RATE_MULTIPLIER => NULL
, p_CINT_PRIOR_BASIS_AMT => NULL
, p_CINT_CURRENT_BASIS_AMT => NULL
, p_REJECTION_CODE => NULL
, p_STATUS_CODE => NULL
, p_ATTRIBUTE_CATEGORY => NULL
, p_ATTRIBUTE1 => NULL
, p_ATTRIBUTE2 => NULL
, p_ATTRIBUTE3 => NULL
, p_ATTRIBUTE4 => NULL
, p_ATTRIBUTE5 => NULL
, p_ATTRIBUTE6 => NULL
, p_ATTRIBUTE7 => NULL
, p_ATTRIBUTE8 => NULL
, p_ATTRIBUTE9 => NULL
, p_ATTRIBUTE10 => NULL
);
/* insert into alloc_txn_details */
--
-- Bug: 983057 Do not create txn with zero curren alloc amount
--
IF (v_curr_offset_amount <> 0 ) THEN
/** Start Capint changes call by reference */
--insert_alloc_txn_details( p_run_id
-- , p_rule_id
-- , 'O'
-- , p_fiscal_year
-- , p_quarter_num
-- , p_period_num
-- , p_run_period
-- , 0
-- , p_offset_project_id
-- , p_offset_task_id
-- , p_expenditure_type
-- , v_tot_offset_amount
-- , v_prev_offset_amount
-- , v_curr_offset_amount );
insert_alloc_txn_details( x_alloc_txn_id => l_alloc_txn_id
, p_run_id => p_run_id
, p_rule_id => p_rule_id
, p_transaction_type => 'O'
, p_fiscal_year => p_fiscal_year
, p_quarter_num => p_quarter_num
, p_period_num => p_period_num
, p_run_period => p_run_period
, p_line_num => 0
, p_project_id => p_offset_project_id
, p_task_id => p_offset_task_id
, p_expenditure_type => p_expenditure_type
, p_total_allocation => v_tot_offset_amount
, p_previous_allocation => v_prev_offset_amount
, p_current_allocation => v_curr_offset_amount
, p_EXPENDITURE_ID => NULL
, p_EXPENDITURE_ITEM_ID => NULL
, p_CINT_SOURCE_TASK_ID => NULL
, p_CINT_EXP_ORG_ID => NULL
, p_CINT_RATE_MULTIPLIER => NULL
, p_CINT_PRIOR_BASIS_AMT => NULL
, p_CINT_CURRENT_BASIS_AMT => NULL
, p_REJECTION_CODE => NULL
, p_STATUS_CODE => NULL
, p_ATTRIBUTE_CATEGORY => NULL
, p_ATTRIBUTE1 => NULL
, p_ATTRIBUTE2 => NULL
, p_ATTRIBUTE3 => NULL
, p_ATTRIBUTE4 => NULL
, p_ATTRIBUTE5 => NULL
, p_ATTRIBUTE6 => NULL
, p_ATTRIBUTE7 => NULL
, p_ATTRIBUTE8 => NULL
, p_ATTRIBUTE9 => NULL
, p_ATTRIBUTE10 => NULL
);
--insert_alloc_txn_details( p_run_id
-- , p_rule_id
-- , 'O'
-- , p_fiscal_year
-- , p_quarter_num
-- , p_period_num
-- , p_run_period
-- , 0
-- , v_offset_extn_tabtype(I).project_id
-- , v_offset_extn_tabtype(I).task_id
-- , p_expenditure_type
-- , v_tot_offset_amount
-- , v_prev_offset_amount
-- , v_curr_offset_amount );
insert_alloc_txn_details( x_alloc_txn_id => l_alloc_txn_id
, p_run_id => p_run_id
, p_rule_id => p_rule_id
, p_transaction_type => 'O'
, p_fiscal_year => p_fiscal_year
, p_quarter_num => p_quarter_num
, p_period_num => p_period_num
, p_run_period => p_run_period
, p_line_num => 0
, p_project_id => v_offset_extn_tabtype(I).project_id
, p_task_id => v_offset_extn_tabtype(I).task_id
, p_expenditure_type => p_expenditure_type
, p_total_allocation => v_tot_offset_amount
, p_previous_allocation => v_prev_offset_amount
, p_current_allocation => v_curr_offset_amount
, p_EXPENDITURE_ID => NULL
, p_EXPENDITURE_ITEM_ID => NULL
, p_CINT_SOURCE_TASK_ID => NULL
, p_CINT_EXP_ORG_ID => NULL
, p_CINT_RATE_MULTIPLIER => NULL
, p_CINT_PRIOR_BASIS_AMT => NULL
, p_CINT_CURRENT_BASIS_AMT => NULL
, p_REJECTION_CODE => NULL
, p_STATUS_CODE => NULL
, p_ATTRIBUTE_CATEGORY => NULL
, p_ATTRIBUTE1 => NULL
, p_ATTRIBUTE2 => NULL
, p_ATTRIBUTE3 => NULL
, p_ATTRIBUTE4 => NULL
, p_ATTRIBUTE5 => NULL
, p_ATTRIBUTE6 => NULL
, p_ATTRIBUTE7 => NULL
, p_ATTRIBUTE8 => NULL
, p_ATTRIBUTE9 => NULL
, p_ATTRIBUTE10 => NULL
);
insert_missing_costs( p_run_id
,'O'
,off_sunk_cost_rec.project_id
,off_sunk_cost_rec.task_id
,off_sunk_cost_rec.Total_allocation);
update pa_alloc_runs
set Missing_offset_proj_amt = nvl(v_off_sunk_cost,0) ,
TOTAL_OFFSETTED_AMOUNT = v_sum_tot_offsets
where run_id = p_run_id ;
SELECT nvl(SUM(current_allocation),0) sum_curr_alloc
, MAX( ABS(current_allocation) ) max_curr_alloc
FROM pa_alloc_txn_details
WHERE run_id = p_run_id
AND transaction_type = 'T';
SELECT project_id, task_id
FROM pa_alloc_txn_details
WHERE run_id = p_run_id
AND transaction_type = 'T'
AND ABS(current_allocation) = p_max_alloc;
UPDATE pa_alloc_txn_details
SET current_allocation = NVL(current_allocation,0) + NVL(v_remnant,0)
, total_allocation = NVL(total_allocation, 0) + NVl(v_remnant, 0)
WHERE run_id = p_run_id
AND transaction_type='T'
AND project_id = v_project_id
AND task_id = v_task_id;
PROCEDURE insert_alloc_runs( x_run_id IN OUT NOCOPY NUMBER /* modified as IN OUT for capint */
, p_rule_id IN NUMBER
, p_run_period IN VARCHAR2
, p_expnd_item_date IN DATE
, p_creation_date IN DATE
, p_created_by IN NUMBER
, p_last_update_date IN DATE
, p_last_updated_by IN NUMBER
, p_last_update_login IN NUMBER
, p_pool_percent IN NUMBER
, p_period_type IN VARCHAR2
, p_source_amount_type IN VARCHAR2
, p_source_balance_category IN VARCHAR2
, p_source_balance_type IN VARCHAR2
, p_alloc_resource_list_id IN NUMBER
, p_auto_release_flag IN VARCHAR2
, p_allocation_method IN VARCHAR2
, p_imp_with_exception IN VARCHAR2
, p_dup_targets_flag IN VARCHAR2
, p_target_exp_type_class IN VARCHAR2
, p_target_exp_org_id IN NUMBER
, p_target_exp_type IN VARCHAR2
, p_target_cost_type IN VARCHAR2
, p_offset_exp_type_class IN VARCHAR2
, p_offset_exp_org_id IN NUMBER
, p_offset_exp_type IN VARCHAR2
, p_offset_cost_type IN VARCHAR2
, p_offset_method IN VARCHAR2
, p_offset_project_id IN NUMBER
, p_offset_task_id IN NUMBER
, p_run_status IN VARCHAR2
, p_basis_method IN VARCHAR2
, p_basis_relative_period IN NUMBER
, p_basis_amount_type IN VARCHAR2
, p_basis_balance_category IN VARCHAR2
, p_basis_budget_type_code IN VARCHAR2
, p_basis_balance_type IN VARCHAR2
, p_basis_resource_list_id IN NUMBER
, p_fiscal_year IN NUMBER
, p_quarter IN NUMBER
, p_period_num IN VARCHAR2
, p_target_exp_group IN VARCHAR2
, p_offset_exp_group IN VARCHAR2
, p_total_pool_amount IN NUMBER
, p_allocated_amount IN NUMBER
, p_reversal_date IN DATE
, p_draft_request_id IN NUMBER
, p_draft_request_date IN DATE
, p_release_request_id IN NUMBER
, p_release_request_date IN DATE
, p_denom_currency_code IN VARCHAR2
, p_fixed_amount IN NUMBER
, p_rev_target_exp_group IN VARCHAR2
, p_rev_offset_exp_group IN VARCHAR2
, p_org_id IN NUMBER
, p_limit_target_projects_code IN VARCHAR2
, p_CINT_RATE_NAME IN VARCHAR2 default NULL
/* FP.M : Allocation Impact : bug # 3512552 */
, p_ALLOC_RESOURCE_STRUCT_TYPE In Varchar2 default NULL
, p_BASIS_RESOURCE_STRUCT_TYPE In Varchar2 default NULL
, p_ALLOC_RBS_VERSION In Number default NULL
, p_BASIS_RBS_VERSION In Number default NULL
) IS
BEGIN
pa_debug.set_err_stack('insert_alloc_runs');
Select pa_alloc_runs_s.nextval
Into x_run_id
From dual;
INSERT INTO pa_alloc_runs_all (
RUN_ID
, RULE_ID
, RUN_PERIOD
, EXPND_ITEM_DATE
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, POOL_PERCENT
, PERIOD_TYPE
, SOURCE_AMOUNT_TYPE
, SOURCE_BALANCE_CATEGORY
, SOURCE_BALANCE_TYPE
, ALLOC_RESOURCE_LIST_ID
, AUTO_RELEASE_FLAG
, ALLOCATION_METHOD
, IMP_WITH_EXCEPTION
, DUP_TARGETS_FLAG
, TARGET_EXP_TYPE_CLASS
, TARGET_EXP_ORG_ID
, TARGET_EXP_TYPE
, TARGET_COST_TYPE
, OFFSET_EXP_TYPE_CLASS
, OFFSET_EXP_ORG_ID
, OFFSET_EXP_TYPE
, OFFSET_COST_TYPE
, OFFSET_METHOD
, OFFSET_PROJECT_ID
, OFFSET_TASK_ID
, RUN_STATUS
, BASIS_METHOD
, BASIS_RELATIVE_PERIOD
, BASIS_AMOUNT_TYPE
, BASIS_BALANCE_CATEGORY
, BASIS_BUDGET_TYPE_CODE
, BASIS_FIN_PLAN_TYPE_ID /* added bug 2619977 */
, BASIS_BALANCE_TYPE
, BASIS_RESOURCE_LIST_ID
, FISCAL_YEAR
, QUARTER
, PERIOD_NUM
, TARGET_EXP_GROUP
, OFFSET_EXP_GROUP
, TOTAL_POOL_AMOUNT
, ALLOCATED_AMOUNT
, REVERSAL_DATE
, DRAFT_REQUEST_ID
, DRAFT_REQUEST_DATE
, RELEASE_REQUEST_ID
, RELEASE_REQUEST_DATE
, DENOM_CURRENCY_CODE
, FIXED_AMOUNT
, REV_TARGET_EXP_GROUP
, REV_OFFSET_EXP_GROUP
, org_id
, limit_target_projects_code
, cint_rate_name
/* FP.M : Allocation Impact : Bug # 3512552 */
, ALLOC_RESOURCE_STRUCT_TYPE
, BASIS_RESOURCE_STRUCT_TYPE
, ALLOC_RBS_VERSION
, BASIS_RBS_VERSION
)
VALUES (
x_run_id
---p_run_id
, p_rule_id
, p_run_period
, p_expnd_item_date
, p_creation_date
, p_created_by
, p_last_update_date
, p_last_updated_by
, p_last_update_login
, p_pool_percent
, p_period_type
, p_source_amount_type
, p_source_balance_category
, p_source_balance_type
, p_alloc_resource_list_id
, p_auto_release_flag
, p_allocation_method
, p_imp_with_exception
, p_dup_targets_flag
, p_target_exp_type_class
, p_target_exp_org_id
, p_target_exp_type
, p_target_cost_type
, p_offset_exp_type_class
, p_offset_exp_org_id
, p_offset_exp_type
, p_offset_cost_type
, p_offset_method
, p_offset_project_id
, p_offset_task_id
, p_run_status
, p_basis_method
, p_basis_relative_period
, p_basis_amount_type
, p_basis_balance_category
, p_basis_budget_type_code
, G_basis_fin_plan_type_id /* added bug 2619977 */
, p_basis_balance_type
, p_basis_resource_list_id
, p_fiscal_year
, p_quarter
, p_period_num
, p_target_exp_group
, p_offset_exp_group
, p_total_pool_amount
, p_allocated_amount
, p_reversal_date
, p_draft_request_id
, p_draft_request_date
, p_release_request_id
, p_release_request_date
, p_denom_currency_code
, p_fixed_amount
, p_rev_target_exp_group
, p_rev_offset_exp_group
, p_org_id
, p_limit_target_projects_code
, p_CINT_RATE_NAME
/* FP.M : Allocation Impact : Bug # 3512552 */
, p_ALLOC_RESOURCE_STRUCT_TYPE
, p_BASIS_RESOURCE_STRUCT_TYPE
, p_ALLOC_RBS_VERSION
, p_BASIS_RBS_VERSION
) ;
END insert_alloc_runs;
select decode(p_run_period_type,'PA', b.pa_period_type, a.accounted_period_type),
a.period_set_name,glp.period_year,glp.quarter_num,glp.period_num,end_date
from gl_periods glp,
gl_sets_of_books a,
pa_implementations b
where a.set_of_books_id = b.set_of_books_id
and glp.period_set_name = a.period_set_name
and glp.period_type = decode(p_run_period_type,'PA', b.pa_period_type,
a.accounted_period_type)
and glp.period_name = p_run_period ;
Select * From
(
Select par.resource_list_member_id ,
par.exclude_flag ,
nvl(pbr.parent_member_id,0) parent_member_id,
par.resource_percentage
from pa_resource_list_members pbr,
/** pa_budget_resources_v pbr, ** bug 2661889 */
pa_alloc_resources par
where par.rule_id = p_rule_id
and par.member_type = p_type
and pbr.resource_list_member_id = par.resource_list_member_id
and display_flag = 'Y' /* FP.M : Allocation Impact Bug # 3512552 */
and enabled_flag = 'Y' /* FP.M : Allocation Impact Bug # 3512552 */
and nvl(pbr.migration_code , 'M') = 'M'
and p_resource_struct_type = 'RL'
Union All
/* FP.M : Allocation Impact Bug # 3512552 */
Select par.resource_list_member_id ,
par.exclude_flag ,
nvl(prbs.parent_element_id , 0) parent_member_id ,
par.resource_percentage
From pa_rbs_elements prbs,
pa_alloc_resources par
Where par.rule_id = p_rule_id
and prbs.user_created_flag = 'N' /* To show only those elements created after summarization process */
and par.member_type = p_type
and prbs.rbs_version_id = p_rbs_version_id
and prbs.rbs_element_id = par.resource_list_member_id
and p_resource_struct_type = 'RBS'
)
order by exclude_flag, parent_member_id, resource_list_member_id ;
* Select resource_list_member_id
* from pa_resource_list_members
* -- pa_budget_resources_v -- bug 2661889
* where resource_list_id = p_resource_list_id
* -- 2564418 changes start
* AND resource_list_member_id NOT IN
* (select resource_list_member_id
* from pa_alloc_resources
* where exclude_flag='Y'
* AND rule_id = p_rule_id);
Select prlm.resource_list_member_id
From pa_resource_list_members prlm
Where prlm.resource_list_id = p_resource_list_id
And display_flag = 'Y' /* FP.M : Allocation Impact Bug # 3512552 */
And enabled_flag = 'Y' /* FP.M : Allocation Impact Bug # 3512552 */
And nvl(prlm.migration_code , 'M') = 'M';
Select prbs.rbs_element_id
From pa_rbs_elements prbs
Where prbs.rbs_version_id = p_rbs_version_id
and prbs.user_created_flag = 'N' /* To show only those elements created after summarization process */
and prbs.resource_type_id <> -1 /* To remove first record of Version Info*/
and Not Exists (Select '1'
From pa_rbs_elements rbs_chd
Where rbs_chd.rbs_version_id = p_rbs_version_id
and rbs_chd.user_created_flag = 'N' /* To show only those elements created after summarization process */
And rbs_chd.parent_element_id = prbs.rbs_element_id
); /* To select only Leaf nodes in case of Actuals */
Select prbs.rbs_element_id
From pa_rbs_elements prbs
Where prbs.rbs_version_id = p_rbs_version_id
and prbs.user_created_flag = 'N' /* To show only those elements created after summarization process */
and prbs.resource_type_id <> -1 ; /* To remove first record of Version Info*/
/* In this case, data can be there for intermediate nodes also. So to insert all the records */
Cursor C_RL_RLM is
Select prlm.resource_list_member_id
from pa_resource_list_members prlm
where prlm.resource_list_id = p_resource_list_id
And display_flag = 'Y' /* FP.M : Allocation Impact Bug # 3512552 */
And enabled_flag = 'Y' /* FP.M : Allocation Impact Bug # 3512552 */
And nvl(prlm.migration_code , 'M') = 'M'
AND NOT exists
(select par.resource_list_member_id
from pa_alloc_resources par
where par.exclude_flag = 'Y'
And par.member_type = p_type /* Bug 3819804 */
AND par.rule_id = p_rule_id
and prlm.resource_list_member_id = par.resource_list_member_id
)
AND p_resource_struct_type = 'RL';
Select prbs.rbs_element_id
From pa_rbs_elements prbs
Where prbs.rbs_version_id = p_rbs_version_id
and prbs.user_created_flag = 'N' /* To show only those elements created after summarization process */
and NOT exists
(Select par.resource_list_member_id
From pa_alloc_resources par
Where par.exclude_flag = 'Y'
And par.member_type = p_type /* Bug 3819804 */
AND par.rule_id = p_rule_id
AND prbs.rbs_element_id = par.resource_list_member_id
)
and Not Exists ( Select '1'
From pa_rbs_elements chd_prbs
where chd_prbs.rbs_version_id = p_rbs_Version_id
and chd_prbs.user_created_flag = 'N' /* To show only those elements created after summarization process */
And chd_prbs.parent_element_id = prbs.rbs_element_id
) /* To fetch only leaf nodes */
and p_resource_struct_type = 'RBS';
Select prbs.rbs_element_id
From pa_rbs_elements prbs
Where prbs.rbs_version_id = p_rbs_version_id
and prbs.user_created_flag = 'N' /* To show only those elements created after summarization process */
and NOT exists
(Select par.resource_list_member_id
From pa_alloc_resources par
Where par.exclude_flag = 'Y'
And par.member_type = p_type /* Bug 3819804 */
AND par.rule_id = p_rule_id
AND prbs.rbs_element_id = par.resource_list_member_id
)
and p_resource_struct_type = 'RBS';
Select resource_list_member_id
from pa_resource_list_members
/** pa_budget_resources_v ** bug 2661889 */
where nvl(parent_member_id,0) = v_rlm_id
and resource_list_id = p_resource_list_id
and display_flag = 'Y' /* FP.M : Allocation Impact Bug # 3512552 */
and enabled_flag = 'Y' /* FP.M : Allocation Impact Bug # 3512552 */
And nvl(migration_code , 'M') = 'M'
and p_resource_struct_type = 'RL';
Select Rbs_Element_Id resource_list_member_id
From pa_rbs_elements prbs
Where Rbs_Version_Id = p_rbs_version_id
and prbs.user_created_flag = 'N' /* To show only those elements created after summarization process */
AND p_resource_struct_type = 'RBS'
AND Not Exists (
Select '1'
From pa_rbs_elements chd_prbs
Where chd_prbs.rbs_version_id = p_rbs_version_id
and chd_prbs.user_created_flag = 'N' /* To show only those elements created after summarization process */
and chd_prbs.parent_element_id = prbs.rbs_element_id
)
Start with Rbs_Element_Id = v_rlm_id
Connect By Prior rbs_element_id = parent_element_id;
Select Rbs_Element_Id resource_list_member_id
From pa_rbs_elements prbs
Where Rbs_Version_Id = p_rbs_version_id
and prbs.user_created_flag = 'N' /* To show only those elements created after summarization process */
AND p_resource_struct_type = 'RBS'
Start with Rbs_Element_Id = v_rlm_id
Connect By Prior rbs_element_id = parent_element_id;
Select Resource_List_Member_Id
From pa_alloc_resources
Where Rule_Id = P_Rule_Id
And Member_Type = P_type
And Exclude_Flag = 'Y';
Select 'Y'
from pa_alloc_resources
where rule_id = p_rule_id
and member_type = p_type
and resource_list_member_id = p_rlm_id
and exclude_flag = 'Y' ;
select 'Y'
from pa_alloc_resources
where rule_id = p_rule_id
and member_type = p_type ;
select 'Y'
from pa_resource_list_members
/** pa_budget_resources_v ** bug 2661889 */
where nvl(parent_member_id,0) = p_rlm_id
and display_flag = 'Y' /* FP.M : Allocation Impact Bug # 3512552 */
and enabled_flag = 'Y' /* FP.M : Allocation Impact Bug # 3512552 */
And nvl(migration_code , 'M') = 'M';
Select 'Y'
From pa_rbs_elements
Where rbs_version_id = p_rbs_version_id
and user_created_flag = 'N' /* To show only those elements created after summarization process */
And Nvl(parent_element_id,0) = p_rlm_id
And RowNum = 1;
Procedure insert_alloc_run_resources(p_run_id IN NUMBER,
p_rule_id IN NUMBER,
p_member_type IN VARCHAR2,
p_res_list_member_id IN NUMBER,
p_resource_percent IN NUMBER)
IS
BEGIN
insert into pa_alloc_run_resource_det (
rule_id,
run_id,
member_type,
resource_list_member_id,
resource_percent,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
values(p_rule_id,
p_run_id,
p_member_type,
p_res_list_member_id ,
p_resource_percent,
G_creation_date,
G_created_by,
G_last_update_date,
G_last_updated_by,
G_last_update_login) ;
insert into pa_alloc_run_resource_det (
rule_id,
run_id,
member_type,
resource_list_member_id,
resource_percent,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
values(p_rule_id,
p_run_id,
p_type,
resource_list_member_tab(i),
100,
G_creation_date,
G_created_by,
G_last_update_date,
G_last_updated_by,
G_last_update_login) ;
v_child_resource_excl_id.delete;
Select resource_list_member_id
From pa_resource_list_members
Where Resource_List_Id = p_resource_list_Id
And Parent_member_Id = v_parent_rlm
And display_flag = 'Y' /* FP.M : Allocation Impact Bug # 3512552 */
And enabled_flag = 'Y' /* FP.M : Allocation Impact Bug # 3512552 */
And nvl(migration_code , 'M') = 'M'
And p_resource_struct_type = 'RL';
Resource_List_Member_tab.Delete;
v_child_resource_excl_id_temp.Delete;
Select Rbs_Element_Id resource_list_member_id
From pa_rbs_elements
Where Rbs_Version_Id = p_rbs_version_id
and user_created_flag = 'N' /* To show only those elements created after summarization process */
And p_resource_struct_type = 'RBS'
Start With Parent_element_Id = V_parent_Rlm
Connect By Prior Rbs_Element_Id = Parent_Element_Id;
Resource_List_Member_tab.Delete;
v_child_resource_excl_id_temp.Delete;
insert_alloc_run_resources(p_run_id => p_run_id
,p_rule_id => p_rule_id
,p_member_type => p_type
,p_res_list_member_id => RL_RLM_REC.resource_list_member_id
,p_resource_percent => 100);
Resource_list_member_tab.delete;
insert_alloc_run_resources(
p_run_id => p_run_id
,p_rule_id => p_rule_id
,p_member_type => p_type
,p_res_list_member_id => v_chd_rlm_id
,p_resource_percent => nvl(RLM_REC.resource_percentage,100)
);
insert_alloc_run_resources(
p_run_id => p_run_id
,p_rule_id => p_rule_id
,p_member_type => p_type
,p_res_list_member_id => RLM_REC.resource_list_member_id
,p_resource_percent => RLM_REC.resource_percentage
);
insert_alloc_run_resources(p_run_id => p_run_id
,p_rule_id => p_rule_id
,p_member_type => p_type
,p_res_list_member_id => RLM_REC.resource_list_member_id
,p_resource_percent => nvl(RLM_REC.resource_percentage,100));
pa_debug.write_file('populate_RLM_table: ' || 'LOG','Inserting the other members-- '|| RLM_REC.resource_list_member_id);
insert_alloc_run_resources(p_run_id => p_run_id
,p_rule_id => p_rule_id
,p_member_type => p_type
,p_res_list_member_id => RL_RLM_REC.resource_list_member_id
,p_resource_percent => nvl(RLM_REC.resource_percentage,100));
Resource_list_member_tab.delete;
pa_debug.write_file('populate_RLM_table:'||'LOG','Inserting member-'|| resource_list_member_tab(i));
insert_alloc_run_resources(p_run_id => p_run_id
,p_rule_id => p_rule_id
,p_member_type => p_type
,p_res_list_member_id => resource_list_member_tab(i)
,p_resource_percent => 100 /* nvl(RLM_REC.resource_percentage,100) */
/*
Bug 3741132 : When only excludes are defined. Then all other resources should
be considered with 100 percentage instead of exclude's percentage
*/
);
includes. The above insert inserts all members which can be
included. Hence there is no need to process further excludes.
Hence, for every exclude except the first, we do not insert.
*/
End if; /* v_incld_exists='N' 2564418 changes end here*/
select min (start_date)
from gl_periods glp
where glp.period_set_name = p_period_set_name
and glp.period_type = p_period_type
and glp.end_date <= p_run_period_end_date
and glp.period_year = p_period_year
and glp.quarter_num = nvl(v_quarter_num, glp.quarter_num);
select start_date
from gl_periods glp
where glp.period_set_name = p_period_set_name
and glp.period_type = p_period_type
and glp.period_name = p_period ;
/* PROCEDURE : insert_alloc_basis_resource
Purpose : To insert data into pa_alloc_run_basis_det table for each resource
for each task which has some data available in summarization.
Separate inserts are written for each type of amt_type
(FYTD,qtd,itd and ptd).
Created : 16-JAN-02 Manokuma
Modified: 24-JAN-03 Tarun for bug 2757875
*/
-- ==========================================================================
PROCEDURE insert_alloc_basis_resource(
p_run_id IN NUMBER,
p_rule_id IN NUMBER,
p_resource_list_id IN NUMBER,
p_amt_type IN VARCHAR2,
p_bal_type IN VARCHAR2,
p_run_period_type IN VARCHAR2,
p_period IN VARCHAR2,
p_run_period_end_date IN DATE ,
p_amttype_start_date IN DATE ,
/* FP.M : Allocation Impact */
p_resource_struct_type in Varchar2,
p_rbs_version_id In Varchar2
)
IS
cursor c_projects is
select distinct part.project_id project_id
from pa_alloc_run_targets part,
pa_resource_list_assignments prla
where part.project_id = prla.project_id
and prla.resource_list_id = p_resource_list_id
and prla.resource_list_accumulated_flag = 'Y'
and part.run_id = p_run_id
and Nvl(p_resource_struct_type,'RL') = 'RL'
Union All
select distinct part.project_id project_id
from pa_alloc_run_targets part,
pa_rbs_prj_assignments prpa
where part.project_id = prpa.project_id
and prpa.rbs_header_id = p_resource_list_id
and prpa.rbs_version_id = p_rbs_version_id
and part.run_id = p_run_id
and Nvl(p_resource_struct_type,'RL') = 'RBS'
;
select start_date
from pa_projects
where project_id = p_proj_id;
pa_debug.G_err_stage:= 'INSIDE INSERT_ALLOC_BASIS_RESOURCE procedure';
pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
pa_debug.G_err_stage:= 'inserting for FYTD or QTD';
pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id );
INSERT INTO PA_ALLOC_RUN_BASIS_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, LINE_PERCENT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
( select --/*+ ORDERED INDEX (prad, PA_RESOURCE_ACCUM_DETAILS_N2) */ --Bug Fix: 3634912 added hint Bug 9054701 removed hint
part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,NVL(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0
)),0) AMOUNT
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login
from ----Bug Fix: 3634912 :Changed the order of the tables
pa_alloc_run_targets part,
pa_alloc_run_resource_det parr,
pa_resource_accum_details prad,
pa_txn_accum pta
where pta.txn_accum_id = prad.txn_accum_id
and prad.Resource_list_member_id = parr.resource_list_member_id
and prad.Project_id = part.project_id
and prad.task_id = part.task_id
and part.run_id = p_run_id
and parr.run_id = part.run_id
and parr.member_type = 'B'
and part.project_id = c_projects_rec.project_id
and part.exclude_flag = 'N'
and exists
/* Using gl_period_statuses instead of pa_periods for Bug 2757875 */
(select /*+ NO_UNNEST */ -- Bug Fix: 3634912 added hint
gl.period_name
from gl_period_statuses gl,
pa_implementations imp
where pta.gl_period = gl.period_name
and gl.set_of_books_id = imp.set_of_books_id
and gl.application_id = pa_period_process_pkg.application_id
and gl.adjustment_period_flag = 'N'
and gl.closing_status in ('C','F','O','P')
and gl.end_date between p_amttype_start_date
and p_run_period_end_date)
/**** (select 1
from pa_periods pp
where pta.pa_period = pp.period_name
and pp.end_date between p_amttype_start_date
and p_run_period_end_date ) **** Commented for Bug 2757875 ****/
group by part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login);
INSERT INTO PA_ALLOC_RUN_BASIS_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, LINE_PERCENT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
( select
part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,NVL(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0
)),0) AMOUNT
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login
from pa_alloc_run_resource_det parr,
pa_alloc_run_targets part,
PA_ALLOC_TXN_ACCUM_RBS_V pta
where pta.Rbs_Element_Id = parr.resource_list_member_id
and pta.Project_id = part.project_id
and pta.task_id = part.task_id
and pta.RBS_STRUCT_VER_ID = p_rbs_version_id
and part.run_id = p_run_id
and parr.run_id = part.run_id
and parr.member_type = 'B'
and part.project_id = c_projects_rec.project_id
and part.exclude_flag = 'N'
and exists
( select gl.period_name
from gl_period_statuses gl,
pa_implementations imp
where pta.gl_period = gl.period_name
and gl.set_of_books_id = imp.set_of_books_id
and gl.application_id = pa_period_process_pkg.application_id
and gl.adjustment_period_flag = 'N'
and gl.closing_status in ('C','F','O','P')
and gl.end_date between p_amttype_start_date
and p_run_period_end_date
)
group by part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login);
pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG','commiting the changes after 5000 records');
pa_debug.G_err_stage:= 'inserting for PTD';
pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
INSERT INTO PA_ALLOC_RUN_BASIS_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, LINE_PERCENT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
( select --/*+ ORDERED INDEX (prad, PA_RESOURCE_ACCUM_DETAILS_N2) */ --Bug Fix: 3634912 added hint Bug 9054701 removed hint
part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,NVL(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0)),0)AMOUNT
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login
from --Bug Fix: 3634912 : Changed the order of the tables.
pa_alloc_run_targets part,
pa_alloc_run_resource_det parr,
pa_resource_accum_details prad,
pa_txn_accum pta
where pta.txn_accum_id = prad.txn_accum_id
and prad.Resource_list_member_id = parr.resource_list_member_id
and pta.Project_id = part.project_id
and pta.task_id = part.task_id
and part.run_id = p_run_id
and parr.run_id = part.run_id
and parr.member_type = 'B'
and part.project_id = c_projects_rec.project_id
and part.exclude_flag = 'N'
and pta.pa_period = p_period
group by part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login);
INSERT INTO PA_ALLOC_RUN_BASIS_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, LINE_PERCENT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
( select part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,NVL(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0)),0)AMOUNT
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login
from pa_alloc_run_resource_det parr,
pa_alloc_run_targets part,
PA_ALLOC_TXN_ACCUM_RBS_V pta
where pta.Rbs_Element_ID = parr.resource_list_member_id
and pta.Project_id = part.project_id
and pta.task_id = part.task_id
and pta.RBS_STRUCT_VER_ID = p_rbs_version_id
and part.run_id = p_run_id
and parr.run_id = part.run_id
and parr.member_type = 'B'
and part.project_id = c_projects_rec.project_id
and part.exclude_flag = 'N'
and pta.pa_period = p_period
group by part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login);
INSERT INTO PA_ALLOC_RUN_BASIS_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, LINE_PERCENT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
( select --/*+ ORDERED INDEX (prad, PA_RESOURCE_ACCUM_DETAILS_N2) */ --Bug Fix: 3634912 added hint Bug 9054701 removed hint
part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,NVL(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0)),0)AMOUNT
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login
from --Bug Fix: 3634912 : Changed the order of tables
pa_alloc_run_targets part,
pa_alloc_run_resource_det parr,
pa_resource_accum_details prad,
pa_txn_accum pta
where pta.txn_accum_id = prad.txn_accum_id
and prad.Resource_list_member_id = parr.resource_list_member_id
and pta.Project_id = part.project_id
and pta.task_id = part.task_id
and part.run_id = p_run_id
and parr.run_id = part.run_id
and parr.member_type = 'B'
and part.project_id = c_projects_rec.project_id
and part.exclude_flag = 'N'
and pta.gl_period = p_period /*Using gl_period on pta directly for bug 2757875 */
/**** and pta.pa_period IN
(SELECT period_name
FROM pa_periods pp
WHERE pp.gl_period_name = p_period) **** Commented for bug 2757875 ****/
group by part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login);
INSERT INTO PA_ALLOC_RUN_BASIS_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, LINE_PERCENT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
( select part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,NVL(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0)),0)AMOUNT
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login
from pa_alloc_run_resource_det parr,
pa_alloc_run_targets part,
PA_ALLOC_TXN_ACCUM_RBS_V pta
where pta.Rbs_Element_ID = parr.resource_list_member_id
and pta.Project_id = part.project_id
and pta.task_id = part.task_id
and pta.RBS_STRUCT_VER_ID = p_rbs_version_id
and part.run_id = p_run_id
and parr.run_id = part.run_id
and parr.member_type = 'B'
and part.project_id = c_projects_rec.project_id
and part.exclude_flag = 'N'
and pta.gl_period = p_period
group by part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login);
pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG','commiting the changes after 5000 records');
pa_debug.G_err_stage:= 'inserting for ITD';
pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
INSERT INTO PA_ALLOC_RUN_BASIS_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, LINE_PERCENT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
( select --/*+ ORDERED INDEX (prad, PA_RESOURCE_ACCUM_DETAILS_N2) */ -- Bug Fix: 3634912 added hint Bug 9054701 removed hint
part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,NVL(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0
)),0) AMOUNT
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login
from -- Bug Fix: 3634912 : Changed the order of tables.
pa_alloc_run_targets part,
pa_alloc_run_resource_det parr,
pa_resource_accum_details prad,
pa_txn_accum pta
where pta.txn_accum_id = prad.txn_accum_id
and prad.Resource_list_member_id = parr.resource_list_member_id
and prad.Project_id = part.project_id
and prad.task_id = part.task_id
and part.run_id = p_run_id
and parr.run_id = part.run_id
and parr.member_type = 'B'
and part.project_id = c_projects_rec.project_id
and part.exclude_flag = 'N'
and exists
(select /*+ NO_UNNEST */ -- Bug Fix: 3634912 added hint
1
from pa_periods pp
where pta.pa_period = pp.period_name
and pp.end_date <= p_run_period_end_date) /* Added for bug 2757875 */
/**** and pp.end_date between v_project_start_date
and p_run_period_end_date ) **** Commented for bug 2757875 ****/
group by part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login);
INSERT INTO PA_ALLOC_RUN_BASIS_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, LINE_PERCENT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
( select part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,NVL(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0
)),0) AMOUNT
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login
from pa_alloc_run_resource_det parr,
pa_alloc_run_targets part,
PA_ALLOC_TXN_ACCUM_RBS_V pta
where pta.Rbs_Element_Id = parr.resource_list_member_id
and pta.Project_id = part.project_id
and pta.task_id = part.task_id
and pta.RBS_STRUCT_VER_ID = p_rbs_version_id
and part.run_id = p_run_id
and parr.run_id = part.run_id
and parr.member_type = 'B'
and part.project_id = c_projects_rec.project_id
and part.exclude_flag = 'N'
and exists
(
select /*+ NO_UNNEST */ -- Bug Fix: 3634912 added hint
1
from pa_periods pp
where pta.pa_period = pp.period_name
and pp.end_date <= p_run_period_end_date
)
group by part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login);
pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG','commiting the changes after 5000 records');
pa_debug.G_err_stage:= 'exiting insert_alloc_basis_resource';
pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', pa_debug.G_err_stage);
END insert_alloc_basis_resource;
/* PROCEDURE : insert_alloc_source_resource
Purpose : To insert data into pa_alloc_run_source_det table for each resource
for each task which has some data available in summarization.
Separate inserts are written for each type of amt_type
(FYTD,qtd,itd and ptd).
Created : 16-JAN-02 Manokuma
Modified: 24-JAN-03 Tarun for bug 2757875
*/
-- ==========================================================================
PROCEDURE insert_alloc_source_resource(
p_run_id IN NUMBER,
p_rule_id IN NUMBER,
p_resource_list_id IN NUMBER,
p_amt_type IN VARCHAR2,
p_bal_type IN VARCHAR2,
p_run_period_type IN VARCHAR2,
p_period IN VARCHAR2,
p_run_period_end_date IN DATE ,
p_amttype_start_date IN DATE ,
/* FP.M : Allocation Impact */
p_resource_struct_type in Varchar2,
p_rbs_version_id in Number
)
IS
cursor c_projects is
select distinct pars.project_id project_id
from pa_alloc_run_sources pars,
pa_resource_list_assignments prla
where pars.project_id = prla.project_id
and prla.resource_list_id = p_resource_list_id
and prla.resource_list_accumulated_flag = 'Y'
and pars.run_id = p_run_id
and NVL(p_resource_struct_type,'RL') = 'RL'
UNION All
select distinct pars.project_id project_id
from pa_alloc_run_sources pars,
pa_rbs_prj_assignments prpa
where pars.project_id = prpa.project_id
and prpa.rbs_header_id = p_resource_list_id
and prpa.rbs_version_id = p_rbs_version_id
and pars.run_id = p_run_id
and NVL(p_resource_struct_type,'RL') = 'RBS'
;
Select nvl(pool_percent,100)/100
from pa_alloc_rules_all
where rule_id = p_rule_id;
select start_date
from pa_projects
where project_id = p_proj_id;
pa_debug.G_err_stage:= 'INSIDE INSERT_ALLOC_source_RESOURCE procedure';
pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
pa_debug.G_err_stage:= 'inserting for FYTD or QTD';
pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id );
INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, ELIGIBLE_AMOUNT
, RESOURCE_PERCENT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
( select --/*+ ORDERED INDEX (prad, PA_RESOURCE_ACCUM_DETAILS_N2) */ --Bug Fix: 3634912 added hint Bug 9054701 removed hint
pars.run_id
,pars.rule_id
,pars.line_num
,pars.project_id
,pars.task_id
,parr.resource_list_member_id
,NVL(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) + nvl(pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+ nvl(pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+ nvl(pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+ nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+ nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+ nvl(pta.i_tot_quantity,0),
0
)),0) AMOUNT
, pa_currency.round_currency_amt(NVL(sum( decode (p_bal_type, --Bug 3590551:Introduced rounding
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0
)),0)*(parr.resource_percent/100)*v_rule_pool_percent) ELIGIBLE_AMOUNT /* bug 3227783 */
,parr.resource_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login
from --Bug Fix: 3634912 : Changed the order of the tables.
pa_alloc_run_sources pars,
pa_alloc_run_resource_det parr,
pa_resource_accum_details prad,
pa_txn_accum pta
where pta.txn_accum_id = prad.txn_accum_id
and prad.Resource_list_member_id = parr.resource_list_member_id
and prad.Project_id = pars.project_id
and prad.task_id = pars.task_id
and pars.run_id = p_run_id
and parr.run_id = pars.run_id
and parr.member_type = 'S'
and pars.project_id = c_projects_rec.project_id
and pars.exclude_flag = 'N'
and exists
/* Using gl_period_statuses instead of pa_periods for bug 2757875 */
(select /*+ NO_UNNEST */ -- Bug Fix: 3634912 added hint
gl.period_name
From gl_period_statuses gl,
pa_implementations imp
where pta.gl_period = gl.period_name
and gl.set_of_books_id = imp.set_of_books_id
and gl.application_id = pa_period_process_pkg.application_id
and gl.adjustment_period_flag = 'N'
and gl.closing_status in ('C','F','O','P')
and gl.end_date between p_amttype_start_date
and p_run_period_end_date
)
/* (select 1
from pa_periods pp
where pta.pa_period = pp.period_name
and pp.end_date between p_amttype_start_date
and p_run_period_end_date) **** 2757875 */
group by pars.run_id
,pars.rule_id
,pars.line_num
,pars.project_id
,pars.task_id
,parr.resource_list_member_id
,parr.resource_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login);
INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, ELIGIBLE_AMOUNT
, RESOURCE_PERCENT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
)
( select pars.run_id
,pars.rule_id
,pars.line_num
,pars.project_id
,pars.task_id
,parr.resource_list_member_id
,NVL(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) + nvl(pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+ nvl(pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+ nvl(pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+ nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+ nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+ nvl(pta.i_tot_quantity,0),
0
)),0) AMOUNT
,pa_currency.round_currency_amt(NVL(sum( decode (p_bal_type, --Bug 3590551:Introduced rounding
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0
)),0)*(parr.resource_percent/100) * v_rule_pool_percent) ELIGIBLE_AMOUNT
,parr.resource_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login
from pa_alloc_run_resource_det parr,
pa_alloc_run_sources pars,
PA_ALLOC_TXN_ACCUM_RBS_V pta
where pta.Rbs_Element_Id = parr.resource_list_member_id
and pta.Project_id = pars.project_id
and pta.task_id = pars.task_id
and pta.RBS_STRUCT_VER_ID = p_rbs_version_id
and pars.run_id = p_run_id
and parr.run_id = pars.run_id
and parr.member_type = 'S'
and pars.project_id = C_projects_rec.project_id
and pars.exclude_flag = 'N'
and Exists
(select gl.period_name
From gl_period_statuses gl,
pa_implementations imp
where pta.gl_period = gl.period_name
and gl.set_of_books_id = imp.set_of_books_id
and gl.application_id = pa_period_process_pkg.application_id
and gl.adjustment_period_flag = 'N'
and gl.closing_status in ('C','F','O','P')
and gl.end_date between p_amttype_start_date
and p_run_period_end_date
)
group by pars.run_id
,pars.rule_id
,pars.line_num
,pars.project_id
,pars.task_id
,parr.resource_list_member_id
,parr.resource_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login);
pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG','commiting the changes after 5000 records');
pa_debug.G_err_stage:= 'inserting for PTD';
pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
INSERT INTO PA_ALLOC_RUN_source_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, ELIGIBLE_AMOUNT
, RESOURCE_PERCENT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
( select --/*+ ORDERED INDEX (prad, PA_RESOURCE_ACCUM_DETAILS_N2) */ -- Bug Fix: 3634912 added hint Bug 9054701 removed hint
pars.run_id
,pars.rule_id
,pars.line_num
,pars.project_id
,pars.task_id
,parr.resource_list_member_id
,NVL(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) + nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0)),0)AMOUNT
,pa_currency.round_currency_amt(NVL(sum( decode (p_bal_type, --Bug 3590551:Introduced rounding
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0)),0)*(parr.resource_percent/100)*v_rule_pool_percent) ELIGIBLE_AMOUNT /* bug 3227783 */
,parr.resource_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login
from -- Bug Fix: 3634912 :Changed the order of the tables.
pa_alloc_run_sources pars,
pa_alloc_run_resource_det parr,
pa_resource_accum_details prad,
pa_txn_accum pta
where pta.txn_accum_id = prad.txn_accum_id
and prad.Resource_list_member_id = parr.resource_list_member_id
and pta.Project_id = pars.project_id
and pta.task_id = pars.task_id
and pars.run_id = p_run_id
and parr.run_id = pars.run_id
and parr.member_type = 'S'
and pars.exclude_flag = 'N'
and pars.project_id = c_projects_rec.project_id
and pta.pa_period = p_period
group by pars.run_id
,pars.rule_id
,pars.line_num
,pars.project_id
,pars.task_id
,parr.resource_list_member_id
,parr.resource_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login);
INSERT INTO PA_ALLOC_RUN_source_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, ELIGIBLE_AMOUNT
, RESOURCE_PERCENT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
( select pars.run_id
,pars.rule_id
,pars.line_num
,pars.project_id
,pars.task_id
,parr.resource_list_member_id
,NVL(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) + nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+ nvl(pta.i_tot_quantity,0),
0)),0)AMOUNT
,pa_currency.round_currency_amt(NVL(sum( decode (p_bal_type, --Bug 3590551:Introduced rounding
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0)),0)*(parr.resource_percent/100)*v_rule_pool_percent) ELIGIBLE_AMOUNT /* bug 3227783 */
,parr.resource_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login
from pa_alloc_run_resource_det parr,
pa_alloc_run_sources pars,
PA_ALLOC_TXN_ACCUM_RBS_V pta
where pta.Rbs_Element_ID = parr.resource_list_member_id
and pta.Project_id = pars.project_id
and pta.task_id = pars.task_id
and pta.RBS_STRUCT_VER_ID = p_rbs_version_id
and pars.run_id = p_run_id
and parr.run_id = pars.run_id
and parr.member_type = 'S'
and pars.exclude_flag = 'N'
and pars.project_id = c_projects_rec.project_id
and pta.pa_period = p_period
group by pars.run_id
,pars.rule_id
,pars.line_num
,pars.project_id
,pars.task_id
,parr.resource_list_member_id
,parr.resource_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login);
INSERT INTO PA_ALLOC_RUN_source_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, ELIGIBLE_AMOUNT
, RESOURCE_PERCENT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
( select --/*+ ORDERED INDEX (prad, PA_RESOURCE_ACCUM_DETAILS_N2) */ --Bug Fix: 3634912 added hint Bug 9054701 removed hint
pars.run_id
,pars.rule_id
,pars.line_num
,pars.project_id
,pars.task_id
,parr.resource_list_member_id
,NVL(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0)),0)AMOUNT
,pa_currency.round_currency_amt(NVL(sum( decode (p_bal_type, --Bug 3590551:Introduced rounding
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0)),0)*(parr.resource_percent/100)*v_rule_pool_percent) ELIGIBLE_AMOUNT /* bug 3227783 */
,parr.resource_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login
from --Bug Fix: 3634912 : Changed the order of the tables.
pa_alloc_run_sources pars,
pa_alloc_run_resource_det parr,
pa_resource_accum_details prad,
pa_txn_accum pta
where pta.txn_accum_id = prad.txn_accum_id
and prad.Resource_list_member_id = parr.resource_list_member_id
and pta.Project_id = pars.project_id
and pta.task_id = pars.task_id
and pars.run_id = p_run_id
and parr.run_id = pars.run_id
and parr.member_type = 'S'
and pars.exclude_flag = 'N'
and pars.project_id = c_projects_rec.project_id
and pta.gl_period = p_period /* Using gl_period on pta directly :bug 2757875 */
/**** and pta.pa_period IN
(SELECT period_name
FROM pa_periods pp
WHERE pp.gl_period_name = p_period) **** Commented for bug 2757875 ****/
group by pars.run_id
,pars.rule_id
,pars.line_num
,pars.project_id
,pars.task_id
,parr.resource_list_member_id
,parr.resource_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login);
INSERT INTO PA_ALLOC_RUN_source_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, ELIGIBLE_AMOUNT
, RESOURCE_PERCENT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
( select pars.run_id
,pars.rule_id
,pars.line_num
,pars.project_id
,pars.task_id
,parr.resource_list_member_id
,NVL(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0)),0)AMOUNT
,pa_currency.round_currency_amt(NVL(sum( decode (p_bal_type, --Bug 3590551:Introduced rounding
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0)),0)*(parr.resource_percent/100)*v_rule_pool_percent) ELIGIBLE_AMOUNT /* bug 3227783 */
,parr.resource_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login
from pa_alloc_run_resource_det parr,
pa_alloc_run_sources pars,
PA_ALLOC_TXN_ACCUM_RBS_V pta
where pta.Rbs_Element_Id = parr.resource_list_member_id
and pta.Project_id = pars.project_id
and pta.task_id = pars.task_id
and pta.RBS_STRUCT_VER_ID = p_rbs_version_id
and pars.run_id = p_run_id
and parr.run_id = pars.run_id
and parr.member_type = 'S'
and pars.exclude_flag = 'N'
and pars.project_id = c_projects_rec.project_id
and pta.gl_period = p_period
group by pars.run_id
,pars.rule_id
,pars.line_num
,pars.project_id
,pars.task_id
,parr.resource_list_member_id
,parr.resource_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login);
pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG','commiting the changes after 5000 records');
pa_debug.G_err_stage:= 'inserting for ITD';
pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
INSERT INTO PA_ALLOC_RUN_source_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, ELIGIBLE_AMOUNT
, RESOURCE_PERCENT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
( select --/*+ ORDERED INDEX (prad, PA_RESOURCE_ACCUM_DETAILS_N2) */ --Bug Fix: 3634912 added hint Bug 9054701 removed hint
pars.run_id
,pars.rule_id
,pars.line_num
,pars.project_id
,pars.task_id
,parr.resource_list_member_id
,NVL(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0
)),0) AMOUNT
,pa_currency.round_currency_amt(NVL(sum( decode (p_bal_type, --Bug 3590551:Introduced rounding
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0
)),0)*(parr.resource_percent/100)*v_rule_pool_percent) ELIGIBLE_AMOUNT /* bug 3227783 */
,parr.resource_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login
from --Bug Fix: 3634912 : Changed the order of the tables
pa_alloc_run_sources pars,
pa_alloc_run_resource_det parr,
pa_resource_accum_details prad,
pa_txn_accum pta
where pta.txn_accum_id = prad.txn_accum_id
and prad.Resource_list_member_id = parr.resource_list_member_id
and prad.Project_id = pars.project_id
and prad.task_id = pars.task_id
and pars.run_id = p_run_id
and parr.run_id = pars.run_id
and parr.member_type = 'S'
and pars.exclude_flag = 'N'
and pars.project_id = c_projects_rec.project_id
and exists
(select /*+ NO_UNNEST */ --Bug 3634912 : Added Hint.
1
from pa_periods pp
where pta.pa_period = pp.period_name
and pp.end_date <= p_run_period_end_date) /* Added for bug 2757875 */
/**** and pp.end_date between v_project_start_date
and p_run_period_end_date) **** Commented for bug 2757875****/
group by pars.run_id
,pars.rule_id
,pars.line_num
,pars.project_id
,pars.task_id
,parr.resource_list_member_id
,parr.resource_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login);
INSERT INTO PA_ALLOC_RUN_source_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, ELIGIBLE_AMOUNT
, RESOURCE_PERCENT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
( select pars.run_id
,pars.rule_id
,pars.line_num
,pars.project_id
,pars.task_id
,parr.resource_list_member_id
,NVL(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0
)),0) AMOUNT
,pa_currency.round_currency_amt(NVL(sum( decode (p_bal_type, --Bug 3590551:Introduced rounding
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0
)),0)*(parr.resource_percent/100)*v_rule_pool_percent) ELIGIBLE_AMOUNT /* bug 3227783 */
,parr.resource_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login
from pa_alloc_run_resource_det parr,
pa_alloc_run_sources pars,
PA_ALLOC_TXN_ACCUM_RBS_V pta
where pta.Rbs_Element_Id = parr.resource_list_member_id
and pta.Project_id = pars.project_id
and pta.task_id = pars.task_id
and pta.RBS_STRUCT_VER_ID = p_rbs_version_id
and pars.run_id = p_run_id
and parr.run_id = pars.run_id
and parr.member_type = 'S'
and pars.exclude_flag = 'N'
and pars.project_id = C_projects_rec.project_id
and exists
(select /*+ NO_UNNEST */ -- Bug3634912 : Added hint .
1
from pa_periods pp
where pta.pa_period = pp.period_name
and pp.end_date <= p_run_period_end_date
)
group by pars.run_id
,pars.rule_id
,pars.line_num
,pars.project_id
,pars.task_id
,parr.resource_list_member_id
,parr.resource_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login);
pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG','commiting the changes after 5000 records');
pa_debug.G_err_stage:= 'exiting insert_alloc_source_resource';
pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', pa_debug.G_err_stage);
END insert_alloc_source_resource;
/* PROCEDURE : insert_budget_basis_resource
Purpose : inserts records into pa_alloc_run_basis_det when budgets are used
for basis and resource lists are used to calculate basis amounts.
Created : 02-feb-01 Manoj.
Modified : 24-JAN-03 Tarun for bug 2757875
06-Apr-04 vthakkar FP.M : ALlocation Impact
*/
-- ==========================================================================
PROCEDURE insert_budget_basis_resource(p_run_id IN NUMBER,
p_rule_id IN NUMBER,
p_run_period_type IN VARCHAR2,
p_bal_type IN VARCHAR2,
p_budget_type_code IN VARCHAR2,
p_start_date IN DATE ,
p_end_date IN DATE ,
/* FP.M : Allocation Impact */
p_basis_resource_struct_Type in Varchar2
)
IS
cursor c_projects is
select distinct project_id
from pa_alloc_run_targets part
where part.run_id = p_run_id;
INSERT INTO PA_ALLOC_RUN_BASIS_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, LINE_PERCENT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
( select part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,nvl(sum(decode (p_bal_type,
'BASE_RAW_COST', nvl(pfpp.raw_cost,0),
'BASE_BURDENED_COST', nvl(pfpp.burdened_cost,0),
'BASE_QUANTITY', nvl(pfpp.quantity,0),
'BASE_LABOR_QUANTITY', nvl(pfpp.labor_quantity,0),
0)),0) AMOUNT
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login
from pa_alloc_run_resource_det parr,
pa_alloc_run_targets part,
/*** pa_base_budget_by_pa_period_v pbpp *** commented bug 2619977 */
pa_base_finplan_by_pa_period_v pfpp /* added bug 2619977 */
where Decode (
Nvl(p_basis_resource_struct_Type,'RL') ,
'RL' , pfpp.resource_list_member_id ,
'RBS' , pfpp.RBS_ELEMENT_ID
) = parr.resource_list_member_id
and pfpp.Project_id = part.project_id
and pfpp.task_id = part.task_id
and part.project_id = c_projects_rec.project_id
and part.exclude_flag = 'N'
/*** and pbpp.budget_type_code = p_budget_type_code *** commented bug 2619977 */
and pfpp.budget_version_id = part.budget_version_id /* added bug 2619977 */
and pfpp.period_start_date >= nvl(p_start_date,pfpp.period_start_date)
and pfpp.period_end_date <= p_end_date
and parr.run_id = p_run_id
and parr.run_id = part.run_id /* Bug # 3850611 */
and parr.member_type = 'B'
group by part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login);
INSERT INTO PA_ALLOC_RUN_BASIS_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, LINE_PERCENT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
( select part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,nvl(sum( decode (p_bal_type,
'BASE_RAW_COST', nvl(pfpg.raw_cost,0),
'BASE_BURDENED_COST', nvl(pfpg.burdened_cost,0),
'BASE_QUANTITY', nvl(pfpg.quantity,0),
'BASE_LABOR_QUANTITY', nvl(pfpg.labor_quantity,0),
0
)),0) amount
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login
from pa_alloc_run_resource_det parr,
pa_alloc_run_targets part,
/*** pa_base_budget_by_gl_period_v pbpg *** commented bug 2619977 */
/* pa_base_finplan_by_pa_period_v pfpg added bug 2619977 commented bug 2757875 */
pa_base_finplan_by_gl_period_v pfpg /* added bug 2757875 */
where Decode (
NVL(p_basis_resource_struct_Type,'RL') ,
'RL' , pfpg.resource_list_member_id ,
'RBS' , pfpg.RBS_ELEMENT_ID
) = parr.resource_list_member_id
and pfpg.Project_id = part.project_id
and pfpg.task_id = part.task_id
/*** and pfpg.budget_type_code = p_budget_type_code ** commented bug 2619977 */
and pfpg.budget_version_id = part.budget_version_id /* added bug 2619977 */
and pfpg.period_start_date >= nvl(p_start_date,pfpg.period_start_date)
and pfpg.period_end_date <= p_end_date
and part.project_id = c_projects_rec.project_id
and part.exclude_flag = 'N'
and parr.run_id = p_run_id
and parr.run_id = part.run_id /* Bug # 3850611 */
and parr.member_type = 'B'
group by part.run_id
,part.rule_id
,part.line_num
,part.project_id
,part.task_id
,parr.resource_list_member_id
,part.line_percent
,G_creation_date
,G_created_by
,G_last_update_date
,G_last_updated_by
,G_last_update_login);
END insert_budget_basis_resource;
select NVL(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0
)),0)
from pa_txn_accum pta,
pa_periods pp,
pa_resource_accum_details prad
where pta.txn_accum_id = prad.txn_accum_id
and prad. Resource_list_member_id = p_rlm_id
and prad. Project_id = p_project_id
and prad.task_id = p_task_id
and pta.pa_period = pp.period_name
and pp.end_date >= p_amttype_start_date
and pp.end_date <= p_run_period_end_date ;
select NVL(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0
)),0)
from pa_txn_accum pta,
pa_resource_accum_details prad
where pta.txn_accum_id = prad.txn_accum_id
and prad. Resource_list_member_id = p_rlm_id
and prad. Project_id = p_project_id
and prad.task_id = p_task_id
and decode ( p_run_period_type, 'GL', pta.gl_period, pta.pa_period) = p_period ;
select NVL(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0
)),0)
from pa_txn_accum pta,
pa_periods pp ,
pa_resource_accum_details prad
where pta.txn_accum_id = prad.txn_accum_id
and prad. Resource_list_member_id = p_rlm_id
and prad. Project_id = p_project_id
and prad.task_id = p_task_id
and pta.pa_period = pp.period_name
and pp.end_date >= v_project_start_date
and pp.end_date <= p_run_period_end_date ;
select start_date
from pa_projects
where project_id = p_project_id ;
PROCEDURE insert_alloc_run_src_det( p_rule_id IN NUMBER
, p_run_id IN NUMBER
, p_line_num IN NUMBER
, p_project_id IN NUMBER
, p_task_id IN NUMBER
, p_rlm_id IN NUMBER
, p_amount IN NUMBER
, p_resource_percent IN NUMBER
, p_eligible_amount IN NUMBER
, p_creation_date IN DATE
, p_created_by IN NUMBER
, p_last_update_date IN DATE
, p_last_updated_by IN NUMBER
, p_last_update_login IN NUMBER)
IS
BEGIN
pa_debug.set_err_stack('insert_alloc_run_source_det') ;
pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_SOURCE_DET' ;
INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, RESOURCE_PERCENT
, ELIGIBLE_AMOUNT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
VALUES (
p_run_id
, p_rule_id
, p_line_num
, p_project_id
, p_task_id
, p_rlm_id
, p_amount
, p_resource_percent
, p_eligible_amount
, p_creation_date
, p_created_by
, p_last_update_date
, p_last_updated_by
, p_last_update_login ) ;
END insert_alloc_run_src_det;
Select line_num, project_id , task_id
from pa_alloc_run_sources
where rule_id = p_rule_id
and run_id = p_run_id
and exclude_flag <> 'Y' ;
select nvl(sum(nvl(eligible_amount,0)),0)
from pa_alloc_run_source_det
where run_id = p_run_id
AND (pa_project_stus_utils.is_project_closed(project_id ))= 'N'; -- added as part of the bug 13949738
Select * from pa_alloc_run_source_det
where run_id = p_run_id
AND (pa_project_stus_utils.is_project_closed(project_id ))= 'Y';
SELECT NVL ( A.Eligible_Amount, 0 ) Eligible_Amount
FROM PA_ALLOC_RUN_SOURCE_DET A ,
( SELECT MAX ( AR.Run_Id ) AS Run_Id
FROM PA_ALLOC_RUNS AR
WHERE AR.Fiscal_Year = NVL ( p_Fscl_Year , AR.Fiscal_Year )
AND AR.Quarter = NVL ( p_Qrtr_Num , AR.Quarter )
AND AR.Period_Num = NVL ( p_Prd_Num , AR.Period_Num )
AND AR.Run_Id < p_Run_Id
AND AR.Rule_Id = p_Rule_Id
AND AR.Run_Status = 'RS'
AND NVL(AR.allocated_amount,0) <> 0
) AR
WHERE A.Run_Id = AR.Run_Id
AND A.Rule_Id = p_Rule_Id
AND A.Project_Id = p_Project_Id
AND A.Task_Id = p_Task_Id ;
Select RUN_ID ,
RULE_ID ,
LINE_NUM ,
PROJECT_ID ,
TASK_ID
From Pa_Alloc_Run_Sources
Where Rule_Id = P_Rule_Id
And Run_Id = P_Run_Id
And Nvl(Exclude_Flag,'N') = 'N';
insert_alloc_run_src_det(p_rule_id, p_run_id, 0,
0, 0, NULL, p_fixed_amount,
NULL , v_net_fixed_amount,
G_creation_date, G_created_by,
G_last_update_date,
G_last_updated_by, G_last_update_login);
select count(*)
into v_resource_count
from pa_alloc_run_resource_det
where rule_id = p_rule_id
and run_id = p_run_id
and member_type = 'S';
pa_debug.G_err_stage := 'error during selecting count from pa_alloc_run_resources_det' ;
insert_alloc_source_resource(p_run_id => p_run_id
,p_rule_id => p_rule_id
,p_resource_list_id => v_resource_list_id
,p_amt_type => p_run_amount_type
,p_bal_type => p_bal_type
,p_run_period_type => p_run_period_type
,p_period => p_run_period
,p_run_period_end_date => v_run_period_end_date
,p_amttype_start_date => v_amttype_start_date
/* FP.M : Allocation Impact */
,p_resource_struct_type => p_source_resource_struct_type
,p_rbs_version_id => p_source_rbs_version_id
);
/* insert_alloc_source_resource will do whatever used to be done by this block
For I in 1.. v_src_rlm_tab.count LOOP
v_rlm_id := v_src_rlm_tab(I).resource_list_member_id ;
insert_alloc_run_src_det(p_rule_id, p_run_id, src_det_rec.line_num,
src_det_rec.project_id,
src_det_rec.task_id, v_rlm_id, v_amount,
v_rlm_percent , v_pool_amount,
G_creation_date, G_created_by,
G_last_update_date,
G_last_updated_by, G_last_update_login) ;
Insert into pa_alloc_run_source_det( rule_id, run_id, line_num, project_id,
task_id, creation_date, created_by,
last_update_date,
last_updated_by, last_update_login,
amount, eligible_amount)
select l_get_proj.rule_id, l_get_proj.run_id, l_get_proj.line_num, l_get_proj.project_id,
l_get_proj.task_id,
/* Bug 3749469
pars.rule_id, pars.run_id, pars.line_num, pars.project_id,
pars.task_id,
*/
G_creation_date, G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login ,
sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)+
+nvl(pta.i_tot_burdened_cost,0),
0 )),
pa_currency.round_currency_amt(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)+
+nvl(pta.i_tot_burdened_cost,0),
0) * v_pool_percent ) )
from pa_alloc_txn_accum_v pta,
/* FP.M : Allocation Impact : pa_txn_accum pta */
/* Commenting out pa_periods for bug 2757875 and using gl_period_statuses instead */
-- pa_periods pp ,
gl_period_statuses gl ,
pa_implementations imp
/* pa_alloc_run_sources pars */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
where /* Bug 3749469
pars.rule_id = p_rule_id
and pars.run_id = p_run_id
and pars.exclude_flag = 'N'
and pta.project_id = pars.project_id
and pta.task_id = pars.task_id
*/
pta.project_id = l_get_proj.project_id /* Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id */
and pta.task_id = l_get_proj.task_id /* Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id */
and pta.gl_period = gl.period_name
and gl.set_of_books_id = imp.set_of_books_id
and gl.application_id = pa_period_process_pkg.application_id
and gl.adjustment_period_flag = 'N'
and gl.closing_status in ('C','F','O','P')
and gl.end_date >= v_amttype_start_date
and gl.end_date <= v_run_period_end_date
-- and pta.pa_period = pp.period_name
-- and pp.end_date >= v_amttype_start_date
-- and pp.end_date <= v_run_period_end_date
group by /*
pars.rule_id, pars.run_id, pars.line_num, pars.project_id,
pars.task_id,
*/
l_get_proj.rule_id, l_get_proj.run_id, l_get_proj.line_num, l_get_proj.project_id,
l_get_proj.task_id,
G_creation_date, G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login ;
Insert into pa_alloc_run_source_det( rule_id, run_id, line_num, project_id, task_id,
creation_date, created_by,
last_update_date,
last_updated_by, last_update_login,
amount, eligible_amount)
select /* Bug 3749469
pars.rule_id, pars.run_id, pars.line_num, pars.project_id,
pars.task_id,
*/
l_get_proj.rule_id, l_get_proj.run_id, l_get_proj.line_num, l_get_proj.project_id,
l_get_proj.task_id,
G_creation_date, G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login ,
sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)+
+nvl(pta.i_tot_burdened_cost,0),
0 )),
pa_currency.round_currency_amt( sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)+
+nvl(pta.i_tot_burdened_cost,0),
0) * v_pool_percent ) )
from pa_alloc_txn_accum_v pta /* FP.M : Allocation Impact : pa_txn_accum pta */
-- Commented out pa_periods. Used the gl_periods column in pa_txn_accum table
-- pa_periods pp
/* pa_alloc_run_sources pars */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
where /* pars.rule_id = p_rule_id
and pars.run_id = p_run_id
and pars.exclude_flag = 'N'
and pta.project_id = pars.project_id
and pta.task_id = pars.task_id
*/
pta.project_id = l_get_proj.project_id /* Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id */
and pta.task_id = l_get_proj.task_id /* Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Task_Id */
and pta.gl_period = p_run_period
-- and pta.pa_period = pp.period_name
-- and pp.gl_period_name = p_run_period
group by
/* Bug 3749469
pars.rule_id, pars.run_id, pars.line_num,pars.project_id,
pars.task_id,
*/
l_get_proj.rule_id, l_get_proj.run_id, l_get_proj.line_num, l_get_proj.project_id,
l_get_proj.task_id,
G_creation_date, G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login ;
Insert into pa_alloc_run_source_det(
rule_id, run_id, line_num, project_id, task_id,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login,
amount, eligible_amount)
select
/* Bug 3749469
pars.rule_id, pars.run_id, pars.line_num,pars.project_id,
pars.task_id,
*/
l_get_proj.rule_id, l_get_proj.run_id, l_get_proj.line_num, l_get_proj.project_id,
l_get_proj.task_id,
G_creation_date, G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login ,
sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)+
+nvl(pta.i_tot_burdened_cost,0),
0 )),
pa_currency.round_currency_amt(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)+
+nvl(pta.i_tot_burdened_cost,0),
0) * v_pool_percent ) )
from pa_alloc_txn_accum_v pta /* FP.M : Allocation Impact : pa_txn_accum pta */
/* pa_alloc_run_sources pars */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
where /* pars.rule_id = p_rule_id
and pars.run_id = p_run_id
and pars.exclude_flag = 'N'
and pta.project_id = pars.project_id
and pta.task_id = pars.task_id
*/ pta.project_id = l_get_proj.project_id /* Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id */
and pta.task_id = l_get_proj.task_id /* Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Task_Id */
and pta.pa_period = p_run_period
group by
/* Bug 3749469
pars.rule_id, pars.run_id, pars.line_num,pars.project_id,
pars.task_id,
*/
l_get_proj.rule_id, l_get_proj.run_id, l_get_proj.line_num, l_get_proj.project_id,
l_get_proj.task_id,
G_creation_date, G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login ;
Insert into pa_alloc_run_source_det(
rule_id, run_id, line_num, project_id, task_id,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login,
amount, eligible_amount)
select /* Bug 3749469
pars.rule_id, pars.run_id, pars.line_num,pars.project_id,
pars.task_id,
*/
l_get_proj.rule_id, l_get_proj.run_id, l_get_proj.line_num, l_get_proj.project_id,
l_get_proj.task_id,
G_creation_date, G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login ,
sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)+
+nvl(pta.i_tot_burdened_cost,0),
0 )),
pa_currency.round_currency_amt(sum( decode (p_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)+
+nvl(pta.i_tot_burdened_cost,0),
0) * v_pool_percent ) )
from pa_alloc_txn_accum_v pta, /* FP.M : Allocation Impact : pa_txn_accum pta */
pa_periods pp ,
pa_projects P
/* pa_alloc_run_sources pars */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
where /* pars.rule_id = p_rule_id
and pars.run_id = p_run_id
and pars.exclude_flag = 'N'
and pars.project_id = p.project_id
and pta.project_id = pars.project_id
and pta.task_id = pars.task_id
*/
pta.project_id = l_get_proj.project_id /* Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id */
and pta.task_id = l_get_proj.task_id /* Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Task_Id */
and p.project_id = l_get_proj.project_id
and pta.pa_period = pp.period_name
-- Removed check for Project start date bug 1063600
-- and pp.end_date >= p.start_date
and pp.end_date <= v_run_period_end_date
group by /* Bug 3749469
pars.rule_id, pars.run_id, pars.line_num,pars.project_id,
pars.task_id,
*/
l_get_proj.rule_id, l_get_proj.run_id, l_get_proj.line_num, l_get_proj.project_id,
l_get_proj.task_id,
G_creation_date, G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login ;
insert_missing_costs( p_run_id
,'S'
,src_missing_cost_rec.project_id
,src_missing_cost_rec.task_id
,v_missing_amount);
UPDATE pa_alloc_runs
SET Missing_source_proj_amt = v_total_missing_cost
WHERE run_id = p_run_id;
select period_name
from gl_periods glp
where glp.period_set_name = p_period_set_name
and glp.period_type = p_period_type
and glp.end_date <= p_run_period_end_date
and glp.adjustment_period_flag <> 'Y' /* Added for Bug#2409474 */
order by start_date desc ;
PROCEDURE insert_alloc_run_basis_det( p_rule_id IN NUMBER
, p_run_id IN NUMBER
, p_line_num IN NUMBER
, p_project_id IN NUMBER
, p_task_id IN NUMBER
, p_rlm_id IN NUMBER
, p_amount IN NUMBER
, p_basis_percent IN NUMBER
, p_line_percent IN NUMBER
, p_creation_date IN DATE
, p_created_by IN NUMBER
, p_last_update_date IN DATE
, p_last_updated_by IN NUMBER
, p_last_update_login IN NUMBER)
IS
BEGIN
pa_debug.set_err_stack('insert_alloc_run_basis_det') ;
pa_debug.write_file('insert_alloc_run_basis_det: ' || 'insert Basis record for task '||to_char(p_task_id)||':'||to_char(p_amount));
pa_debug.write_file('insert_alloc_run_basis_det: ' || 'LOG', pa_debug.G_Err_Stage);
pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_BASIS_DET' ;
INSERT INTO PA_ALLOC_RUN_BASIS_DET (
RUN_ID
, RULE_ID
, LINE_NUM
, PROJECT_ID
, TASK_ID
, RESOURCE_LIST_MEMBER_ID
, AMOUNT
, BASIS_PERCENT
, LINE_PERCENT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
VALUES (
p_run_id
, p_rule_id
, p_line_num
, p_project_id
, p_task_id
, p_rlm_id
, p_amount
, p_basis_percent
, p_line_percent
, p_creation_date
, p_created_by
, p_last_update_date
, p_last_updated_by
, p_last_update_login ) ;
END insert_alloc_run_basis_det ;
and insert that into pa_alloc_run_basis_det.
Else
For each project and task in pa_alloc_run_targets
find the basis amount at project and task level from
pa_base_budget_by_pa_period_v
and insert that into pa_alloc_run_basis_det.
End if.
End if;
Select line_num, project_id , task_id, line_percent
from pa_alloc_run_targets
where rule_id = p_rule_id
and run_id = p_run_id
and exclude_flag <> 'Y' ;
select nvl(sum(nvl(amount,0)),0), nvl(max(nvl(amount,0)),0), count(1) /* 1900331 */
from pa_alloc_run_basis_det
where run_id = p_run_id ;
select line_num, line_percent, nvl(sum(nvl(amount,0)),0), nvl(max(nvl(amount,0)),0), count(1) /* 1900331 */
from pa_alloc_run_basis_det
where run_id = p_run_id
group by line_num, line_percent ;
select sum((nvl(line_percent,0)))
from pa_alloc_run_basis_det
where run_id = p_run_id ;
Select RUN_ID ,
RULE_ID ,
LINE_NUM ,
PROJECT_ID ,
TASK_ID ,
EXCLUDE_FLAG ,
LINE_PERCENT ,
BUDGET_VERSION_ID
From Pa_Alloc_Run_Targets
Where rule_id = p_rule_id
And run_id = p_run_id
And Nvl(exclude_flag,'N') = 'N' ;
insert_alloc_run_basis_det(
p_rule_id, p_run_id, run_targets_rec.line_num,
run_targets_rec.project_id, run_targets_rec.task_id,
v_rlm_id, nvl(v_amount,0), NULL, run_targets_rec.line_percent,
G_creation_date, G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login) ;
this procedure will now insert into pa_alloc_run_resources_det */
populate_RLM_table( p_rule_id,
p_run_id,
'B',
v_resource_list_id,
p_basis_resource_struct_type ,
p_basis_rbs_version_id ,
p_basis_category
) ;
is made and bulk inserts are done in new procedure added insert_alloc_basis_resource
These changes are not done for budgets as in budgets the volume of data should not be that high.
*/
IF p_basis_category = 'A' THEN
/* Currenly we insert all project/tasks into pa_alloc_run_targets.
These do not get converted into final txns in case there are no basis existing
for these targets in summarization. We want to intelligently delete those data
from pa_alloc_run_targets table which do not contain any basis
amount. */
clean_up_targets_for_actuals(p_run_id => p_run_id
,p_rule_id => p_rule_id
,p_amt_type => p_basis_amt_type
,p_run_period_type => p_run_period_type
,p_period => v_rel_period_name
,p_run_period_end_date => v_rel_period_end_date
,p_amttype_start_date => v_amttype_start_date
,p_basis_method => v_basis_method
);
insert_alloc_basis_resource( p_run_id => p_run_id
,p_rule_id => p_rule_id
,p_resource_list_id => v_resource_list_id
,p_amt_type => p_basis_amt_type
,p_bal_type => p_basis_bal_type
,p_run_period_type => p_run_period_type
,p_period => v_rel_period_name
,p_run_period_end_date => v_rel_period_end_date
,p_amttype_start_date => v_amttype_start_date
/* FP.M : Allocation Impact : Bug# 3512552 */
,p_resource_struct_type => p_basis_resource_struct_type
,p_rbs_version_id => p_basis_rbs_version_id
);
insert_budget_basis_resource(p_run_id => p_run_id
,p_rule_id => p_rule_id
,p_run_period_type => p_run_period_type
,p_bal_type => p_basis_bal_type
,p_budget_type_code => p_budget_type_code
,p_start_date => v_amttype_start_date
,p_end_date => v_rel_period_end_date
,p_basis_resource_struct_Type => p_basis_resource_struct_type);
/***2211234 - insert_budget_basis_resource will take care of this.
*** FOR I in 1.. v_basis_rlm_tab.count LOOP
*** v_rlm_id := v_basis_rlm_tab (I).resource_list_member_id ;
*** insert_alloc_run_basis_det(
*** p_rule_id, p_run_id, run_targets_rec.line_num,
*** run_targets_rec.project_id, run_targets_rec.task_id,
*** v_rlm_id, nvl(v_amount,0), NULL, run_targets_rec.line_percent,
*** G_creation_date, G_created_by, G_last_update_date,
*** G_last_updated_by, G_last_update_login) ;
Insert into pa_alloc_run_basis_det (
rule_id, run_id, line_num, project_id, task_id,
line_percent, creation_date, created_by,
last_update_date, last_updated_by,
last_update_login, amount)
select /* Bug 3749469
part.rule_id, part.run_id, part.line_num, part.project_id,
part.task_id, part.line_percent,
*/
l_get_proj.rule_id, l_get_proj.run_id, l_get_proj.line_num, l_get_proj.project_id,
l_get_proj.task_id, l_get_proj.line_percent,
G_creation_date, G_created_by,
G_last_update_date, G_last_updated_by, G_last_update_login ,
sum( decode (p_basis_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0 )) AMOUNT
from pa_alloc_txn_accum_v pta, /* FP.M : Allocation Impact : pa_txn_accum pta, */
/* Commenting out pa_periods and using gl_period_statuses instead for bug 2757875 */
-- pa_periods pp ,
gl_period_statuses gl,
pa_implementations imp
/* pa_alloc_run_targets part */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
where /* Bug 3749469 part.rule_id = p_rule_id
and part.run_id = p_run_id
and part.exclude_flag = 'N'
and pta.project_id = part.project_id
and pta.task_id = part.task_id
*/ /* added outer join for bug 1900331 */
/* Removed Outer join for bug 2182563 */
pta.project_id = l_get_proj.project_id /* Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id */
and pta.task_id = l_get_proj.task_id /* Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id */
and pta.gl_period = gl.period_name
and gl.set_of_books_id = imp.set_of_books_id
and gl.application_id = pa_period_process_pkg.application_id
and gl.adjustment_period_flag = 'N'
and gl.closing_status in ('C','F','O','P')
and gl.end_date >= v_amttype_start_date
and gl.end_date <= v_rel_period_end_date
-- and nvl(pta.pa_period,pp.period_name) = pp.period_name /* bug 2121598 */
-- and pp.end_date >= v_amttype_start_date
-- and pp.end_date <= v_rel_period_end_date
group by
/* Bug 3749469
part.rule_id, part.run_id, part.line_num, part.project_id,
part.task_id, part.line_percent,
*/
l_get_proj.rule_id, l_get_proj.run_id, l_get_proj.line_num, l_get_proj.project_id,
l_get_proj.task_id, l_get_proj.line_percent,
G_creation_date, G_created_by,
G_last_update_date,
G_last_updated_by, G_last_update_login ;
Insert into pa_alloc_run_basis_det (
rule_id, run_id, line_num, project_id, task_id,
line_percent, creation_date, created_by,
last_update_date, last_updated_by,
last_update_login, amount)
select /* Bug 3749469
part.rule_id, part.run_id, part.line_num, part.project_id,
part.task_id, part.line_percent,
*/
l_get_proj.rule_id, l_get_proj.run_id, l_get_proj.line_num, l_get_proj.project_id,
l_get_proj.task_id, l_get_proj.line_percent,
G_creation_date, G_created_by,
G_last_update_date, G_last_updated_by, G_last_update_login ,
sum( decode (p_basis_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0 )) AMOUNT
from pa_alloc_txn_accum_v pta /* FP.M : Allocation Impact : pa_txn_accum pta, */
-- Commented out pa_periods. Used the gl_periods column in pa_txn_accum table
-- pa_periods pp
/* pa_alloc_run_targets part */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
where /* Bug 3749469
part.rule_id = p_rule_id
and part.run_id = p_run_id
and part.exclude_flag = 'N'
and pta.project_id = part.project_id
and pta.task_id = part.task_id
*/
/* added outer join for bug 1900331 */
/* Removed Outer join for bug 2182563 */
pta.project_id = l_get_proj.project_id /* Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id */
and pta.task_id = l_get_proj.task_id /* Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Task_Id */
and pta.gl_period = v_rel_period_name
-- and pta.pa_period = pp.period_name
-- and pp.gl_period_name = v_rel_period_name
group by /* Bug 3749469
part.rule_id, part.run_id, part.line_num, part.project_id,
part.task_id, part.line_percent,
*/
l_get_proj.rule_id, l_get_proj.run_id, l_get_proj.line_num, l_get_proj.project_id,
l_get_proj.task_id, l_get_proj.line_percent,
G_creation_date, G_created_by,
G_last_update_date,
G_last_updated_by, G_last_update_login ;
Insert into pa_alloc_run_basis_det (
rule_id, run_id, line_num, project_id, task_id,
line_percent, creation_date, created_by,
last_update_date, last_updated_by,
last_update_login, amount)
select /* Bug 3749469
part.rule_id, part.run_id, part.line_num, part.project_id,
part.task_id, part.line_percent,
*/
l_get_proj.rule_id, l_get_proj.run_id, l_get_proj.line_num, l_get_proj.project_id,
l_get_proj.task_id, l_get_proj.line_percent,
G_creation_date, G_created_by,
G_last_update_date, G_last_updated_by,G_last_update_login,
sum( decode (p_basis_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0 )) AMOUNT
from pa_alloc_txn_accum_v pta /* FP.M : Allocation Impact : pa_txn_accum pta, */
/* pa_alloc_run_targets part */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
where /* Bug 3749469 part.rule_id = p_rule_id
and part.run_id = p_run_id
and part.exclude_flag = 'N'
and pta.project_id = part.project_id
and pta.task_id = part.task_id
*/ /* added outer join for bug 1900331 */
/* Removed Outer join for bug 2182563 */
pta.project_id = l_get_proj.project_id /* Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id */
and pta.task_id = l_get_proj.task_id /* Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Task_Id */
and pta.pa_period = v_rel_period_name
group by /* Bug 3749469
part.rule_id, part.run_id, part.line_num, part.project_id,
part.task_id, part.line_percent,
*/
l_get_proj.rule_id, l_get_proj.run_id, l_get_proj.line_num, l_get_proj.project_id,
l_get_proj.task_id, l_get_proj.line_percent,
G_creation_date, G_created_by,
G_last_update_date,
G_last_updated_by, G_last_update_login ;
Insert into pa_alloc_run_basis_det (
rule_id, run_id, line_num, project_id, task_id,
line_percent, creation_date, created_by,
last_update_date, last_updated_by,
last_update_login, amount)
select /*+ORDERED*/ -- added ORDERED hint for bug 2751178
/* Bug 3749469
part.rule_id, part.run_id, part.line_num, part.project_id,
part.task_id, part.line_percent,
*/
l_get_proj.rule_id, l_get_proj.run_id, l_get_proj.line_num, l_get_proj.project_id,
l_get_proj.task_id, l_get_proj.line_percent,
G_creation_date, G_created_by,
G_last_update_date, G_last_updated_by, G_last_update_login ,
sum( decode (p_basis_bal_type,
'TOT_RAW_COST', nvl(pta.tot_raw_cost,0) +nvl( pta.i_tot_raw_cost,0),
'TOT_BILLABLE_RAW_COST', nvl(pta.tot_billable_raw_cost,0)
+nvl( pta.i_tot_billable_raw_cost,0),
'TOT_BILLABLE_BURDENED_COST', nvl(pta.tot_billable_burdened_cost,0)
+nvl( pta.i_tot_billable_burdened_cost,0),
'TOT_BURDENED_COST', nvl(pta.tot_burdened_cost,0)
+nvl(pta.i_tot_burdened_cost,0),
'TOT_LABOR_HOURS', nvl(pta.tot_labor_hours,0)
+nvl(pta.i_tot_labor_hours,0),
'TOT_QUANTITY', nvl(pta.tot_quantity,0)
+nvl(pta.i_tot_quantity,0),
0 )) AMOUNT
/* Rearranged the tables in the FROM clause and commented out pa_projects_all for bug 2751178 */
from /* pa_alloc_run_targets part, */ /* Loop thru pa_alloc_run_sources. Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id and Task_Id */
pa_alloc_txn_accum_v pta, /* FP.M : Allocation Impact : pa_txn_accum pta, */
pa_periods pp
-- pa_projects_all p ,
where /* Bug 3749469
part.rule_id = p_rule_id
and part.run_id = p_run_id
and part.exclude_flag = 'N'
-- and part.project_id = p.project_id --- Commented for bug 2751178
and pta.project_id = part.project_id
and pta.task_id = part.task_id
*/
/* added for bug 1900331 */
/* Removed Outer join for bug 2182563 */
pta.project_id = l_get_proj.project_id /* Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id */
and pta.task_id = l_get_proj.task_id /* Bug 3749469 : Performance with pa_alloc_txn_accum_v. Filter for Project_Id */
/* Removed nvl for bug 2751178 */
-- and nvl(pta.pa_period,pp.period_name) = pp.period_name /* bug 2121598 */
and pta.pa_period = pp.period_name
-- Removed check for Project start date bug 1063600
-- and pp.end_date >= p.start_date
and pp.end_date <= v_rel_period_end_date
group by /* Bug 3749469
part.rule_id, part.run_id, part.line_num, part.project_id,
part.task_id, part.line_percent,
*/
l_get_proj.rule_id, l_get_proj.run_id, l_get_proj.line_num, l_get_proj.project_id,
l_get_proj.task_id, l_get_proj.line_percent,
G_creation_date, G_created_by,
G_last_update_date,
G_last_updated_by, G_last_update_login ;
pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','Inserting in basis_det from budgets');
Insert into pa_alloc_run_basis_det(
rule_id, run_id, line_num, project_id, task_id,
line_percent, creation_date, created_by, last_update_date,
last_updated_by, last_update_login, amount)
select part.rule_id, part.run_id, part.line_num,
part.project_id, part.task_id,
part.line_percent, G_creation_date,
G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login ,
sum( decode (p_basis_bal_type,
'BASE_RAW_COST', nvl(pfpp.raw_cost,0),
'BASE_BURDENED_COST', nvl(pfpp.burdened_cost,0),
'BASE_QUANTITY', nvl(pfpp.quantity,0),
'BASE_LABOR_QUANTITY', nvl(pfpp.labor_quantity,0),
0
))
/*** from pa_base_budget_by_pa_period_v pbpp, *** commented bug 2619977 */
from pa_base_finplan_by_pa_period_v pfpp, /* added bug 2619977 */
pa_projects_all p ,
pa_alloc_run_targets part
where part.rule_id = p_rule_id
and part.run_id = p_run_id
and part.exclude_flag = 'N'
and part.project_id = p.project_id
and pfpp.project_id = part.project_id
and pfpp.task_id = part.task_id
and pfpp.budget_version_id = part.budget_version_id /* added bug 2619977 */
/*** and pbpp.budget_type_code = p_budget_type_code *** commented bug 2619977 */
-- Removed check for Project start date bug 1063600
-- and pfpp.Period_start_date >= p.start_date
and pfpp.period_end_date <= v_rel_period_end_date
group by part.rule_id, part.run_id, part.line_num,
part.project_id, part.task_id,
part.line_percent, G_creation_date, G_created_by,
G_last_update_date,
G_last_updated_by, G_last_update_login ;
Insert into pa_alloc_run_basis_det(
rule_id, run_id, line_num, project_id, task_id,
line_percent, creation_date, created_by, last_update_date,
last_updated_by, last_update_login, amount)
select part.rule_id, part.run_id, part.line_num,
part.project_id, part.task_id,
part.line_percent, G_creation_date,
G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login ,
sum( decode (p_basis_bal_type,
'BASE_RAW_COST', nvl(pfpp.raw_cost,0),
'BASE_BURDENED_COST', nvl(pfpp.burdened_cost,0),
'BASE_QUANTITY', nvl(pfpp.quantity,0),
'BASE_LABOR_QUANTITY', nvl(pfpp.labor_quantity,0),
0
))
/*** from pa_base_budget_by_pa_period_v pbpp, *** commented bug 2619977 */
/**** from pa_base_finplan_by_pa_period_v pfpp, added bug 2619977. **** Commented bug 2757875*/
from pa_base_finplan_by_gl_period_v pfpp, /* Added bug 2757875 */
pa_alloc_run_targets part
where part.rule_id = p_rule_id
and part.run_id = p_run_id
and part.exclude_flag = 'N'
and pfpp.project_id = part.project_id
and pfpp.task_id = part.task_id
and pfpp.budget_version_id = part.budget_version_id /* added bug 2619977 */
/*** and pbpp.budget_type_code = p_budget_type_code *** commented bug 2619977 */
/**** and pfpp.Period_start_date >= v_amttype_start_date *** Commented bug 2757875 */
and pfpp.period_end_date >= v_amttype_start_date /* Added bug 2757875 */
and pfpp.period_end_date <= v_rel_period_end_date
group by part.rule_id, part.run_id, part.line_num,
part.project_id, part.task_id,
part.line_percent, G_creation_date, G_created_by,
G_last_update_date,
G_last_updated_by, G_last_update_login ;
Insert into pa_alloc_run_basis_det(
rule_id, run_id, line_num, project_id, task_id,
line_percent, creation_date, created_by, last_update_date,
last_updated_by, last_update_login, amount)
select part.rule_id, part.run_id, part.line_num,
part.project_id, part.task_id,
part.line_percent, G_creation_date,
G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login ,
sum( decode (p_basis_bal_type,
'BASE_RAW_COST', nvl(pfgp.raw_cost,0),
'BASE_BURDENED_COST', nvl(pfgp.burdened_cost,0),
'BASE_QUANTITY', nvl(pfgp.quantity,0),
'BASE_LABOR_QUANTITY', nvl(pfgp.labor_quantity,0),
0
))
from pa_base_finplan_by_gl_period_v pfgp,
pa_alloc_run_targets part
where part.rule_id = p_rule_id
and part.run_id = p_run_id
and part.exclude_flag = 'N'
and pfgp.project_id = part.project_id
and pfgp.task_id = part.task_id
and pfgp.budget_version_id = part.budget_version_id
and pfgp.gl_period_name = v_rel_period_name
group by part.rule_id, part.run_id, part.line_num,
part.project_id, part.task_id,
part.line_percent, G_creation_date, G_created_by,
G_last_update_date,
G_last_updated_by, G_last_update_login ;
Insert into pa_alloc_run_basis_det(
rule_id, run_id, line_num, project_id, task_id,
line_percent, creation_date, created_by, last_update_date,
last_updated_by, last_update_login, amount)
select part.rule_id, part.run_id, part.line_num,
part.project_id, part.task_id,
part.line_percent, G_creation_date,
G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login ,
sum( decode (p_basis_bal_type,
'BASE_RAW_COST', nvl(pfpp.raw_cost,0),
'BASE_BURDENED_COST', nvl(pfpp.burdened_cost,0),
'BASE_QUANTITY', nvl(pfpp.quantity,0),
'BASE_LABOR_QUANTITY', nvl(pfpp.labor_quantity,0),
0
))
from pa_base_finplan_by_pa_period_v pfpp,
pa_alloc_run_targets part
where part.rule_id = p_rule_id
and part.run_id = p_run_id
and part.exclude_flag = 'N'
and pfpp.project_id = part.project_id
and pfpp.task_id = part.task_id
and pfpp.budget_version_id = part.budget_version_id
and pfpp.pa_period = v_rel_period_name
group by part.rule_id, part.run_id, part.line_num,
part.project_id, part.task_id,
part.line_percent, G_creation_date, G_created_by,
G_last_update_date,
G_last_updated_by, G_last_update_login ;
Insert into pa_alloc_run_basis_det(
rule_id, run_id, line_num, project_id, task_id,
line_percent, creation_date, created_by, last_update_date,
last_updated_by, last_update_login, amount)
select part.rule_id, part.run_id, part.line_num,
part.project_id, part.task_id,
part.line_percent, G_creation_date,
G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login ,
0
from pa_alloc_run_targets part
where part.rule_id = p_rule_id
and part.run_id = p_run_id
and part.exclude_flag = 'N'
and NOT EXISTS ( Select 'Exists'
from pa_alloc_run_basis_det parbd
where parbd.run_id = part.run_id
and parbd.project_id = part.project_id
and parbd.task_id = part.task_id ) ;
/* added for bug 2182563. For FP insert one row per target with zero amount */
IF p_basis_method = 'FP' then
/* First delete any records that are existing in the table 'pa_alloc_run_basis_det'
with amount = 0. so that the following insert does not insert any duplicate
records into the table if there is a record already existing with amount = 0 */
DELETE FROM PA_ALLOC_RUN_BASIS_DET
WHERE rule_id = p_rule_id
AND run_id = p_run_id
AND nvl(amount,0) = 0;
/* we need to insert rows for a run_id/line_num only if there are no records in
in basis table for that run_id and line number with amount <> 0. Because in this
case the program has function like basis_method = 'FS' for that target line. */
Insert into pa_alloc_run_basis_det (
rule_id, run_id, line_num, project_id, task_id,
line_percent, creation_date, created_by,
last_update_date, last_updated_by,
last_update_login, amount)
select part.rule_id, part.run_id, part.line_num, part.project_id,
part.task_id, part.line_percent, G_creation_date, G_created_by,
G_last_update_date, G_last_updated_by, G_last_update_login ,
0 AMOUNT
from pa_alloc_run_targets part
where part.rule_id = p_rule_id
and part.run_id = p_run_id
and part.exclude_flag = 'N'
and not exists
(select null
from pa_alloc_run_basis_det parb
where parb.run_id = part.run_id
and parb.line_num = part.line_num
and parb.amount <> 0);
pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG', 'No of Records Inserted in basis details'||
to_char(SQL%ROWCOUNT));
UPDATE pa_alloc_run_basis_det
SET basis_percent = decode(nvl(amount,0), 0, 0, amount*100/v_tot_basis_amt)
WHERE run_id = p_run_id ;
UPDATE pa_alloc_run_basis_det
SET basis_percent = 100/v_tot_basis_rec
WHERE run_id = p_run_id ;
pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','No of Records Updated : '|| to_char(SQL%ROWCOUNT));
UPDATE pa_alloc_run_basis_det
SET basis_percent = decode(nvl(amount,0), 0, 0, amount*100/v_line_basis_amt)
WHERE run_id = p_run_id
AND line_num = v_line_num ;
UPDATE pa_alloc_run_basis_det
SET basis_percent = 100/v_line_count
WHERE run_id = p_run_id
AND line_num = v_line_num ;
*** select nvl(sum( decode (p_bal_type,
*** 'BASE_RAW_COST', nvl(pbpp.raw_cost,0),
*** 'BASE_BURDENED_COST', nvl(pbpp.burdened_cost,0),
*** 'BASE_QUANTITY', nvl(pbpp.quantity,0),
*** 'BASE_LABOR_QUANTITY', nvl(pbpp.labor_quantity,0),
*** 0
*** )),0)
*** from pa_base_budget_by_pa_period_v pbpp
*** where pbpp.Resource_list_id = p_rl_id
*** and pbpp.Resource_list_member_id = p_rlm_id
*** and pbpp.Project_id = p_project_id
*** and pbpp.task_id = p_task_id
*** and pbpp.budget_type_code = p_budget_type_code
*** and pbpp.period_start_date >= nvl(p_start_date,pbpp.period_start_date)
*** and pbpp.period_end_date <= p_end_date ;
*** select nvl(sum( decode (p_bal_type,
*** 'BASE_RAW_COST', nvl(pbpg.raw_cost,0),
*** 'BASE_BURDENED_COST', nvl(pbpg.burdened_cost,0),
*** 'BASE_QUANTITY', nvl(pbpg.quantity,0),
*** 'BASE_LABOR_QUANTITY', nvl(pbpg.labor_quantity,0),
*** 0
*** )),0)
*** from pa_base_budget_by_gl_period_v pbpg
*** where pbpg.Resource_list_id = p_rl_id
*** and pbpg.Resource_list_member_id = p_rlm_id
*** and pbpg.Project_id = p_project_id
*** and pbpg.task_id = p_task_id
*** and pbpg.budget_type_code = p_budget_type_code
*** and pbpg.period_start_date >= nvl(p_start_date, pbpg.period_start_date)
*** and pbpg.period_end_date <= p_end_date ;
Purpose : Deletes records from pa_alloc_run_targets which do not contain
any Basis amount. The addition of this procedure does not
modify the existing flow. This procedure has been added to
delete unnecessary records from pa_alloc_run_targets for
performance reasons. So, removing this procedure will not impact
the existing flow of Allocations.
Created : 18-feb-02 Praveen for Bug #2222280
*/
-- ==========================================================================
PROCEDURE clean_up_targets_for_actuals(
p_run_id IN NUMBER,
p_rule_id IN NUMBER,
p_amt_type IN VARCHAR2,
p_run_period_type IN VARCHAR2,
p_period IN VARCHAR2,
p_run_period_end_date IN DATE ,
p_amttype_start_date IN DATE,
p_basis_method IN OUT NOCOPY VARCHAR2
)
IS
cursor c_target_lines is
select line_num
from pa_alloc_target_lines patl
where rule_id = p_rule_id;
SAVEPOINT delete_unwanted_targets;
DELETE FROM pa_alloc_run_targets part
WHERE part.run_id = p_run_id
AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
AND not exists
(select null
from pa_txn_accum pta,pa_periods pp
where pta.project_id = part.project_id
and pta.task_id = part.task_id
and pp.period_name = pta.pa_period
and pp.end_date between p_amttype_start_date
and p_run_period_end_date);
pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
select 'Y'
into v_do_commit
from dual
where exists
(select null
from pa_alloc_run_targets part
where part.run_id = p_run_id
and part.exclude_flag = 'N'
and rownum = 1);
If all the records are deleted then we will need these records
to do spread evenly.
*/
COMMIT;
/* switch the basis method to 'S' in case all records are deleted.
We need to do rollback also
*/
ROLLBACK TO delete_unwanted_targets;
SAVEPOINT delete_unwanted_targets;
DELETE FROM pa_alloc_run_targets part
WHERE part.run_id = p_run_id
AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
AND part.line_num = c_target_lines_rec.line_num
AND not exists
(select null
from pa_txn_accum pta,pa_periods pp
where pta.project_id = part.project_id
and pta.task_id = part.task_id
and pp.period_name = pta.pa_period
and pp.end_date between p_amttype_start_date
and p_run_period_end_date);
pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
select 'Y'
into v_do_commit
from dual
where exists
(select null
from pa_alloc_run_targets part
where part.run_id = p_run_id
and part.exclude_flag = 'N'
and line_num = c_target_lines_rec.line_num
and rownum = 1);
If all the records are deleted then we will need these records
to do spread evenly.
No switch of basis method can be done in case of FP.
*/
COMMIT;
ROLLBACK TO delete_unwanted_targets;
SAVEPOINT delete_unwanted_targets;
DELETE FROM pa_alloc_run_targets part
WHERE part.run_id = p_run_id
AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
AND not exists
(select null
from pa_txn_accum pta
where pta.project_id = part.project_id
and pta.task_id = part.task_id
and pta.pa_period = p_period
and rownum = 1
);
pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
select 'Y'
into v_do_commit
from dual
where exists
(select null
from pa_alloc_run_targets part
where part.run_id = p_run_id
and part.exclude_flag = 'N'
and rownum = 1);
If all the records are deleted then we will need these records
to do spread evenly.
*/
COMMIT;
/* switch the basis method to 'S' in case all records are deleted.
We need to do rollback also
*/
ROLLBACK TO delete_unwanted_targets;
SAVEPOINT delete_unwanted_targets;
DELETE FROM pa_alloc_run_targets part
WHERE part.run_id = p_run_id
AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
AND not exists
(select null
from pa_txn_accum pta,pa_periods pp
where pta.project_id = part.project_id
and pta.task_id = part.task_id
and pp.period_name = pta.pa_period
and pp.gl_period_name = p_period);
pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
select 'Y'
into v_do_commit
from dual
where exists
(select null
from pa_alloc_run_targets part
where part.run_id = p_run_id
and part.exclude_flag = 'N'
and rownum = 1);
If all the records are deleted then we will need these records
to do spread evenly.
*/
IF P_DEBUG_MODE = 'Y' THEN
pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Committed the deletion');
/* swith the basis method to 'S' in case all records are deleted
we need to do rollback also
*/
ROLLBACK TO delete_unwanted_targets;
SAVEPOINT delete_unwanted_targets;
DELETE FROM pa_alloc_run_targets part
WHERE part.run_id = p_run_id
AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
AND part.line_num = c_target_lines_rec.line_num
AND not exists
(select null
from pa_txn_accum pta
where pta.project_id = part.project_id
and pta.task_id = part.task_id
and pta.pa_period = p_period
and rownum = 1
);
pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
select 'Y'
into v_do_commit
from dual
where exists
(select null
from pa_alloc_run_targets part
where part.run_id = p_run_id
and part.exclude_flag = 'N'
and line_num = c_target_lines_rec.line_num
and rownum = 1);
If all the records for the line are deleted then we will need
these records to do spread evenly.
No switch of basis method can be done in case of FP.
*/
COMMIT;
ROLLBACK TO delete_unwanted_targets;
SAVEPOINT delete_unwanted_targets;
DELETE FROM pa_alloc_run_targets part
WHERE part.run_id = p_run_id
AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
AND part.line_num = c_target_lines_rec.line_num
AND not exists
(select null
from pa_txn_accum pta,pa_periods pp
where pta.project_id = part.project_id
and pta.task_id = part.task_id
and pp.period_name = pta.pa_period
and pp.gl_period_name = p_period
);
pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
select 'Y'
into v_do_commit
from dual
where exists
(select null
from pa_alloc_run_targets part
where part.run_id = p_run_id
and part.exclude_flag = 'N'
and line_num = c_target_lines_rec.line_num
and rownum = 1);
If all the records for the line are deleted then we will need
these records to do spread evenly.
No switch of basis method can be done in case of FP.
*/
COMMIT;
ROLLBACK TO delete_unwanted_targets;
doing this delete for each project id in the targets table. Hence do
not use pa_periods table in this case. */
pa_debug.G_err_stage:= 'Deleting data from PA_Alloc_Run_Targets for ITD';
SAVEPOINT delete_unwanted_targets;
DELETE FROM pa_alloc_run_targets part
WHERE part.run_id = p_run_id
AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
AND not exists
(select null
from pa_txn_accum pta
where pta.project_id = part.project_id
and pta.task_id = part.task_id
and rownum = 1
);
pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Deleted '||sql%rowcount||' records');
select 'Y'
into v_do_commit
from dual
where exists
(select null
from pa_alloc_run_targets part
where part.run_id = p_run_id
and part.exclude_flag = 'N'
and rownum = 1);
If all the records are deleted then we will need these records
to do spread evenly.
*/
IF P_DEBUG_MODE = 'Y' THEN
pa_debug.write_file('clean_up_targets_for_actuals: ' || 'LOG', 'Committed the deletion');
/* swith the basis method to 'S' in case all records are deleted
we need to do rollback also
*/
ROLLBACK TO delete_unwanted_targets;
SAVEPOINT delete_unwanted_targets;
DELETE FROM pa_alloc_run_targets part
WHERE part.run_id = p_run_id
AND part.exclude_flag = 'N' /* we want to delete only exclude flag 'N' targets */
AND part.line_num = c_target_lines_rec.line_num
AND not exists
(select null
from pa_txn_accum pta
where pta.project_id = part.project_id
and pta.task_id = part.task_id
and rownum = 1
);
select 'Y'
into v_do_commit
from dual
where exists
(select null
from pa_alloc_run_targets part
where part.run_id = p_run_id
and part.exclude_flag = 'N'
and line_num = c_target_lines_rec.line_num
and rownum = 1);
If all the records for the line are deleted then we will need
these records to do spread evenly.
No switch of basis method can be done in case of FP.
*/
COMMIT;
ROLLBACK TO delete_unwanted_targets;
G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login,
'R', 'E', NULL, NULL, 'PA_AL_CANT_ACQUIRE_LOCK');
G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login,
'R', 'E', NULL, NULL, 'PA_CINT_CANT_ACQUIRE_LOCK');
G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login,
'R', 'E', NULL, NULL, 'PA_AL_LOCK_RELEASE_FAILED');
G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login,
'R', 'E', NULL, NULL, 'PA_CINT_LOCK_RELEASE_FAILED');
Select denom_currency_code
, target_exp_type_class
, offset_exp_type_class
, target_exp_org_id
, offset_exp_org_id
, target_exp_type
, offset_exp_type
, trunc(expnd_item_date) expnd_item_date
, offset_method
from pa_alloc_runs
where run_id = p_run_id ;
select org_id
from pa_implementations ;
Select expenditure_id
from pa_expenditures
where expenditure_group in (v_tgt_exp_group,v_off_exp_group) ;
SELECT meaning
FROM pa_lookups
WHERE lookup_type='PROJECT_STATUS_ACTIONS'
AND lookup_code='CAPITALIZED_INTEREST';
select distinct rejection_code
from pa_alloc_txn_details
where run_id=runid
and status_code='R'
and rejection_code is not null;
pa_debug.G_err_stage := 'Inserting records in Interface table ' ;
G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login,
'R', 'E', NULL, NULL, 'PA_AL_RUN_NOT_EXISTS');
G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login,
'R', 'E', NULL, NULL, 'PA_AL_INVALID_TARGET_EXP_ORG');
G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login,
'R', 'E', NULL, NULL, 'PA_AL_INVALID_OFFSET_EXP_ORG');
pa_debug.write_file('Release_alloc_txns: ' || 'LOG', 'Inserting records in pa_transaction_interface_all');
insert into pa_transaction_interface_all
( transaction_source
, system_linkage
, batch_name
, organization_name
, expenditure_ending_date
, expenditure_item_date
, project_number
, task_number
, expenditure_type
, quantity
, denom_currency_code
, denom_raw_cost
, denom_burdened_cost
, transaction_status_code
, orig_transaction_reference
, unmatched_negative_txn_flag
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, created_by
, creation_date
, last_updated_by
, last_update_date
, org_id
, expenditure_comment
, billable_flag -- added for Capitalized Interest Functionality
/* Passing the following columns values to increase the performance*/
, project_id
, task_id
, organization_id
, PERSON_BUSINESS_GROUP_ID
/* end of performance changes */
)
Select
l_transaction_source -- Changed this for capital project changes
, decode(patd.transaction_type,'T', run_rec.target_exp_type_class,
run_rec.offset_exp_type_class)
, v_batch_name
, decode(p_rule_id, --Changed for capital project changes
-1,pa_utils.GetOrgName(patd.cint_exp_org_id ),
decode(patd.transaction_type,
'T',v_target_expnd_org,
v_offset_expnd_org))
, v_expnd_end_date
, run_rec.expnd_item_date
, pp.segment1
, pt.task_number
, patd.expenditure_type
, decode(patd.transaction_type, 'T',
decode(run_rec.target_exp_type_class,
'PJ', patd.current_allocation,0),
decode(run_rec.offset_exp_type_class,
'PJ', patd.current_allocation,0))
, run_rec.denom_currency_code
, decode(patd.transaction_type, 'T',
decode(run_rec.target_exp_type_class,
'PJ', patd.current_allocation,0),
decode(run_rec.offset_exp_type_class,
'PJ', patd.current_allocation,0))
/* In the decode below changed the 0 in default to null for bug 1524669 */
, decode(patd.transaction_type, 'T',
decode(run_rec.target_exp_type_class,
'BTC', patd.current_allocation,null),
decode(run_rec.offset_exp_type_class,
'BTC', patd.current_allocation,null))
, 'P'
, to_char(patd.alloc_txn_id)
, 'Y'
, patd.attribute_category
, patd.attribute1
, patd.attribute2
, patd.attribute3
, patd.attribute4
, patd.attribute5
, patd.attribute6
, patd.attribute7
, patd.attribute8
, patd.attribute9
, patd.attribute10
, G_created_by
, G_creation_date
, G_last_updated_by
, G_last_update_date
, v_org_id
, v_expnd_comment
, decode(l_transaction_source,'Capitalized Interest','Y',null)
/* Passing the following columns values to increase the performance*/
, pp.project_id
, pt.task_id
, decode(p_rule_id,-1,patd.cint_exp_org_id,
decode(patd.transaction_type,'T',run_rec.target_exp_org_id,
run_rec.offset_exp_org_id))
, decode(p_rule_id,
-1,pa_utils4.GetOrgBusinessGrpId(patd.cint_exp_org_id ),
decode(patd.transaction_type,
'T',pa_utils4.GetOrgBusinessGrpId(run_rec.target_exp_org_id)
,pa_utils4.GetOrgBusinessGrpId(run_rec.offset_exp_org_id)))
/* end of performance changes */
from pa_alloc_txn_details patd
, pa_projects_all pp
, pa_tasks pt
where patd.run_id = p_run_id
and patd.project_id = pp.project_id
and patd.task_id = pt.task_id ;
pa_debug.write_file('Release_alloc_txns: ' || 'LOG', to_char(SQL%ROWCOUNT)||' Records inserted');
Select pa_interface_id_s.nextval
into v_interface_id
from dual ;
update pa_alloc_txn_details patd
set ( status_code
, rejection_code
, expenditure_id
, expenditure_item_id) =
( select transaction_status_code
,transaction_rejection_code
, expenditure_id
, expenditure_item_id
from pa_transaction_interface_all pti
where pti.orig_transaction_reference = to_char(alloc_txn_id)
and pti.transaction_source = l_transaction_source -- Changed this for capital project changes
and pti.batch_name = v_batch_name )
where run_id = p_run_id ;
pa_debug.write_file('Release_alloc_txns: ' || 'LOG', to_char(SQL%ROWCOUNT)||' Records updated');
--- run status is updated to 'RS' even if transaction import fails. Hence adding this code to reflect the
--- transaction import result into pa_alloc_exceptions. Also added code in allocation_run to
--- update the run_status_code conditionally based on whether any errors exist or not.
begin
for r1 in c_alloc_txn_err(p_run_id) loop
pa_alloc_run.ins_alloc_exceptions( p_rule_id, p_run_id, G_creation_date,
G_created_by, G_last_update_date,
G_last_updated_by, G_last_update_login,
'T', 'E', NULL, NULL, r1.rejection_code);
select 'Y'
into v_import_failed
from dual
where EXISTS ( select 'exists'
from pa_alloc_txn_details
where run_id = p_run_id
and status_code = 'R' ) ;
delete from pa_expenditure_items
where expenditure_id = exp_rec.expenditure_id ;
delete from pa_expenditures
where expenditure_id = exp_rec.expenditure_id ;
Delete from pa_expenditure_groups
where expenditure_group in ( v_tgt_exp_group, v_off_exp_group) ;
update pa_alloc_runs
set target_exp_group = v_tgt_exp_group ,
offset_exp_group = decode(p_rule_id,
-1,null,
decode(run_rec.offset_method,'N',NULL,v_off_exp_group) )
where run_id = p_run_id ;
Select target_exp_group
, offset_exp_group
from pa_alloc_runs
where run_id = p_run_id ;
update pa_alloc_runs
set run_status = 'RV'
,reversal_date = trunc(sysdate)
,rev_target_exp_group = p_tgt_exp_group
,rev_offset_exp_group = p_off_exp_group
where run_id = p_run_id ;
PROCEDURE Delete_alloc_txns( p_rule_id IN NUMBER
,p_run_id IN NUMBER)
IS
BEGIN
-- pa_debug.G_process := 'SQL' ;
pa_debug.set_err_stack('Delete_alloc_txns') ;
pa_debug.G_err_stage := 'Delete_alloc_txns' ;
pa_debug.write_file('Delete_alloc_txns: ' || 'LOG',pa_debug.G_err_stage);
Delete from pa_alloc_txn_details
where run_id = p_run_id and rownum < 10001;
Delete from pa_alloc_run_source_det
where run_id = p_run_id and rownum < 10001;
Delete from pa_alloc_run_basis_det
where run_id = p_run_id and rownum < 10001;
Delete from pa_alloc_run_sources
where run_id = p_run_id and rownum < 10001;
Delete from pa_alloc_run_targets
where run_id = p_run_id and rownum < 10001;
Delete from pa_alloc_run_resource_det
where run_id = p_run_id and rownum < 10001;
Delete from pa_alloc_missing_costs
where run_id = p_run_id ;
Delete from pa_alloc_exceptions
where run_id = p_run_id ;
Delete from pa_alloc_runs
where run_id = p_run_id ;
END Delete_alloc_txns ;
PROCEDURE Delete_alloc_run(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_rule_id IN NUMBER
)
IS
CURSOR get_run_id
IS
select run_id from pa_alloc_runs_all
where rule_id = p_rule_id
and run_status = 'DL';
pa_debug.set_err_stack('Delete_alloc_run') ;
pa_debug.G_err_stage := 'Delete_alloc_run' || To_char(p_rule_id) ;
pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
pa_debug.G_err_stage := 'Delete Rule Id' || To_char(p_rule_id) ;
pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
pa_debug.G_err_stage := 'Delete Run Id' || To_char(l_run_id) ;
pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
pa_alloc_run.delete_cint_source_dets
( p_run_id => l_run_id
,x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count);
pa_debug.write_file('Delete_alloc_run: ' || 'LOG',pa_debug.G_err_stage);
Delete from pa_alloc_txn_details
where run_id = l_run_id and rownum < 10001;
Delete from pa_alloc_run_source_det
where run_id = l_run_id and rownum < 10001;
Delete from pa_alloc_run_basis_det
where run_id = l_run_id and rownum < 10001;
Delete from pa_alloc_run_sources
where run_id = l_run_id and rownum < 10001;
Delete from pa_alloc_run_targets
where run_id = l_run_id and rownum < 10001;
Delete from pa_alloc_run_resource_det
where run_id = l_run_id and rownum < 10001;
Delete from pa_alloc_missing_costs
where run_id = l_run_id ;
Delete from pa_alloc_exceptions
where run_id = l_run_id ;
Delete from pa_alloc_runs
where run_id = l_run_id ;
END Delete_alloc_run ;
SELECT '1'
FROM dual
WHERE EXISTS (SELECT 'Y'
FROM pa_alloc_src_projects_v ps
WHERE ps.project_id=p_project_id);
SELECT '1'
FROM dual
WHERE EXISTS (SELECT 'Y'
FROM pa_alloc_src_tasks_v pt
WHERE pt.project_id=p_project_id
and pt.task_id=p_task_id);
SELECT '1'
FROM dual
WHERE EXISTS (SELECT 'Y'
FROM pa_alloc_target_proj_v pap
WHERE pap.project_id=p_project_id);
SELECT '1'
FROM dual
WHERE EXISTS (SELECT 'Y'
FROM pa_alloc_tgt_tasks_v pt
WHERE pt.project_id=p_project_id
and pt.task_id=p_task_id);
SELECT '1'
FROM dual
WHERE EXISTS (SELECT 'Y'
FROM pa_alloc_offset_projects_v pap
WHERE pap.project_id=p_project_id);
SELECT '1'
FROM dual
WHERE EXISTS (SELECT 'Y'
FROM pa_alloc_tgt_tasks_v pt
WHERE pt.project_id=p_project_id
and pt.task_id=p_task_id);
pa_debug.G_err_stage := 'About to update the Release status to ['||x_return_status||']';
/* bug fix: 3123539 based on the return status update the run staus and avoid
* setting the concurrent process to raise error
*/
IF x_return_status = 'S' Then
UPDATE pa_alloc_runs
SET run_status = 'RS'
,release_request_id = l_rel_request_id
,release_request_date =sysdate
WHERE run_id = p_run_id;
-- Update the status to release failure
UPDATE pa_alloc_runs
SET run_status = 'RF'
,release_request_id = null
,release_request_date =sysdate
WHERE run_id = p_run_id;
UPDATE pa_alloc_runs
SET run_status = 'RF'
,release_request_id = l_rel_request_id
,release_request_date =sysdate
WHERE run_id = p_run_id;
PROCEDURE delete_cint_source_dets
( p_run_id IN pa_alloc_runs_all.run_id%TYPE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_msg_count NUMBER := 0;
SELECT alloc_txn_id
FROM pa_alloc_txn_details
WHERE run_id=p_run_id;
l_module_name := 'delete_cint_source_dets';
pa_debug.g_err_stage:= 'About to delete the source txn details';
DELETE
FROM pa_cint_source_details
WHERE alloc_txn_id = c_cint_txn_rec.alloc_txn_id
AND ROWNUM <1000;
pa_debug.g_err_stage:= 'Leaving delete_cint_source_dets';
,p_procedure_name => 'delete_cint_source_dets'
,p_error_text => x_msg_data);
END delete_cint_source_dets;