[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_updated_by NUMBER;
g_last_update_login NUMBER;
SELECT 'Y'
INTO l_exists
FROM dual
WHERE EXISTS
(SELECT NULL
FROM PA_ALLOC_TXN_DETAILS
WHERE run_id = p_run_id);
SELECT bv.budget_version_id
,bv.budget_entry_method_code
,bv.fin_plan_type_id
,decode(bv.fin_plan_type_id, null,'BUDGET TYPE','PLAN TYPE') threshold_Type
FROM pa_budget_versions bv
WHERE bv.project_id = p_project_id
AND bv.current_flag = 'Y'
AND ( (bv.fin_plan_type_id is not NULL
and bv.version_type IN ('COST','ALL')
and bv.fin_plan_type_id = p_fin_plan_type_id
and bv.budget_type_code is null )
OR
(bv.fin_plan_type_id is NULL
and bv.budget_type_code = p_budget_type_code
and NOT EXISTS (select 'Y'
from pa_budget_versions bv1
where bv1.project_id = bv.project_id
and bv1.fin_plan_type_id = p_fin_plan_type_id)
)
);
SELECT distinct ENTRY_LEVEL_CODE
-- ,CATEGORIZATION_CODE
FROM pa_budget_entry_methods
WHERE BUDGET_ENTRY_METHOD_CODE = p_bdgt_entry_method ;
SELECT nvl(decode(fin_plan_option_level_code
,'PLAN_VERSION',decode(fin_plan_preference_code
,'COST_ONLY'
,decode(COST_FIN_PLAN_LEVEL_CODE,null,ALL_FIN_PLAN_LEVEL_CODE
,COST_FIN_PLAN_LEVEL_CODE)
,'COST_AND_REV_SEP'
,decode(COST_FIN_PLAN_LEVEL_CODE,null,ALL_FIN_PLAN_LEVEL_CODE
,COST_FIN_PLAN_LEVEL_CODE))
,'PROJECT',decode(fin_plan_preference_code
,'COST_ONLY'
,decode(COST_FIN_PLAN_LEVEL_CODE,null,ALL_FIN_PLAN_LEVEL_CODE
,COST_FIN_PLAN_LEVEL_CODE)
,'COST_AND_REV_SEP'
,decode(COST_FIN_PLAN_LEVEL_CODE,null,ALL_FIN_PLAN_LEVEL_CODE
,COST_FIN_PLAN_LEVEL_CODE))
,'PLAN_TYPE',decode(fin_plan_preference_code
,'COST_ONLY'
,decode(COST_FIN_PLAN_LEVEL_CODE,null,ALL_FIN_PLAN_LEVEL_CODE
,COST_FIN_PLAN_LEVEL_CODE)
,'COST_AND_REV_SEP'
,decode(COST_FIN_PLAN_LEVEL_CODE,null,ALL_FIN_PLAN_LEVEL_CODE
,COST_FIN_PLAN_LEVEL_CODE))
),'~') entry_level_code
FROM pa_proj_fp_options
WHERE project_id = p_project_id
AND fin_plan_version_id = p_plan_version_id
AND fin_plan_type_id = p_fin_plan_type_id
AND fin_plan_preference_code in ('COST_ONLY','COST_AND_REV_SEP')
ORDER BY entry_level_code;
SELECT 'CLOSED'
INTO lv_value
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM pa_project_asset_line_details ppald
WHERE ppald.reversed_flag = 'N'
AND ppald.line_num = p_line_num
AND ppald.expenditure_item_id = p_expenditure_item_id);
SELECT picc.expenditure_type
INTO lv_exp_type
FROM pa_ind_cost_codes picc
WHERE picc.ind_cost_code = p_rate_name;
SELECT 1
INTO ln_count
FROM dual
WHERE EXISTS(
SELECT null
FROM pa_cint_exp_type_excl pcete
WHERE pcete.expenditure_type = p_exp_type
AND pcete.ind_cost_code = p_rate_name
);
SELECT gps.start_date
,gps.end_date
,gps.period_year
,gps.quarter_num
,gps.period_num
INTO x_start_date
,x_end_date
,x_fiscal_year
,x_quarter_num
,x_period_num
FROM gl_period_statuses gps
,pa_implementations pi
WHERE gps.period_name = p_gl_period
AND gps.application_id = g_gl_app_id
AND gps.set_of_books_id = pi.set_of_books_id;
SELECT gps.period_year
,gps.period_num
INTO x_fiscal_year
,x_period_num
FROM gl_period_statuses gps
,pa_implementations pi
WHERE gps.application_id = g_gl_app_id
AND gps.set_of_books_id = pi.set_of_books_id
AND gps.adjustment_period_flag = 'N'
AND gps.start_date =
(SELECT MIN(gps.start_date)
FROM gl_period_statuses gps
,pa_implementations pi
WHERE gps.application_id = g_gl_app_id
AND gps.set_of_books_id = pi.set_of_books_id
AND gps.adjustment_period_flag = 'N'
AND gps.start_date >
(SELECT gps.end_date
FROM gl_period_statuses gps
,pa_implementations pi
WHERE gps.application_id = g_gl_app_id
AND gps.set_of_books_id = pi.set_of_books_id
AND gps.period_year = p_fiscal_year
AND gps.period_num = p_period_num));
SELECT COUNT(*)
INTO ln_count
FROM gl_period_statuses gps
,pa_implementations pi
WHERE gps.period_year = p_fiscal_year
AND gps.application_id = g_gl_app_id
AND gps.set_of_books_id = pi.set_of_books_id
AND adjustment_period_flag = 'N';
SELECT 'Y'
INTO x_bypass
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM pa_alloc_txn_details patd
,pa_alloc_runs par
WHERE patd.project_id = p_project_id
AND patd.run_id = par.run_id
AND par.run_status <> 'RV'
AND par.cint_rate_name = p_rate_name
AND par.period_num = p_period_num
AND par.quarter = p_quarter_num
AND par.fiscal_year = p_fiscal_year
AND par.rule_id = p_rule_id)
/* This condition is added to check the CRL migrated trxns
* Since we are only migrating the rate name defaulted at the BG
* but the transactions includes the rate name defaulted at BG and
* overriding rate names.
* EX: projects p1,,,p5 are associated with rate1 are at BG and p7 - rate2 (overide)
* Before migration capint run for FEB-02 and we migrated p1 to p7
* again when user runs capint for FEB-02 after migration
* ideally p7 should not be picked up for processing. In order to avoid this
* the following condition is added : check run irresepective of rate name
* for the given project and period and the rate name doesnot exists in the
* pa_ind_cost_codes table
*/
OR EXISTS (SELECT 'X'
FROM pa_alloc_txn_details patd
,pa_alloc_runs par
WHERE patd.run_id = par.run_id
AND par.run_status <> 'RV'
AND patd.project_id = p_project_id
AND par.period_num = p_period_num
AND par.quarter = p_quarter_num
AND par.fiscal_year = p_fiscal_year
AND par.rule_id = p_rule_id
AND NOT EXISTS ( -- check for override rates which are not migrated
select null
from pa_ind_cost_codes icc
where icc.ind_cost_code = par.cint_rate_name
and icc.ind_cost_code_usage = 'CAPITALIZED_INTEREST'
)
);
SELECT ind_rate_sch_revision_id
INTO x_sched_version_id
FROM pa_ind_rate_sch_revisions pirsr
WHERE TRUNC(p_test_date) BETWEEN
TRUNC(pirsr.start_date_active) AND TRUNC(NVL(pirsr.end_date_active, p_test_date))
AND pirsr.compiled_flag = 'Y'
AND pirsr.ind_rate_sch_id = p_int_sch_id;
SELECT 'Y'
INTO l_rate_falg
FROM DUAL
WHERE EXISTS (
SELECT null
FROM pa_cint_rate_multipliers rate
WHERE rate.ind_rate_sch_id = p_sch_id
AND rate.ind_rate_sch_revision_id = p_sch_rev_id
AND rate.rate_name = p_rate_name
);
SELECT task_id
FROM pa_tasks
WHERE project_id = p_project_id
AND task_capital_flag(task_id) = 'N';
SELECT pt.task_id,nvl(pt.billable_flag,'N')
INTO ln_task_id,lv_cap_flag
FROM pa_tasks pt
WHERE TRUNC(p_period_end_date) <= TRUNC(NVL(pt.cint_stop_date,p_period_end_date))
AND NVL(pt.cint_eligible_flag,'Y') = 'Y'
AND TRUNC(p_exp_item_date) BETWEEN
TRUNC(NVL(pt.start_date,p_exp_item_date)) AND
TRUNC(NVL(pt.completion_date,p_exp_item_date))
--AND pt.chargeable_flag = 'Y'
AND pt.task_id = p_task_id
AND pt.project_id = p_project_id;
SELECT multiplier
FROM pa_cint_rate_multipliers
WHERE rate_name = p_rate_name
AND organization_id = ln_organization_id
AND ind_rate_sch_revision_id = p_sched_version_id;
SELECT pa_alloc_runs_s.nextval
INTO x_run_id
FROM DUAL;
pa_alloc_run.insert_alloc_runs
(x_run_id => x_run_id
,p_rule_id => p_rule_id
,p_run_period => p_gl_period
,p_expnd_item_date => p_exp_item_date
,p_creation_date => SYSDATE
,p_created_by => g_created_by
,p_last_update_date => SYSDATE
,p_last_updated_by => g_last_updated_by
,p_last_update_login => g_last_update_login
,p_pool_percent => NULL
,p_period_type => NULL
,p_source_amount_type => NULL
,p_source_balance_category => NULL
,p_source_balance_type => NULL
,p_alloc_resource_list_id => NULL
,p_auto_release_flag => p_autorelease
,p_allocation_method => NULL
,p_imp_with_exception => NULL
,p_dup_targets_flag => NULL
,p_target_exp_type_class => 'PJ'
,p_target_exp_org_id => NULL
,p_target_exp_type => p_exp_type
,p_target_cost_type => NULL
,p_offset_exp_type_class => NULL
,p_offset_exp_org_id => NULL
,p_offset_exp_type => NULL
,p_offset_cost_type => NULL
,p_offset_method => NULL
,p_offset_project_id => NULL
,p_offset_task_id => NULL
,p_run_status => 'DS'
,p_basis_method => NULL
,p_basis_relative_period => NULL
,p_basis_amount_type => NULL
,p_basis_balance_category => NULL
,p_basis_budget_type_code => NULL
,p_basis_balance_type => NULL
,p_basis_resource_list_id => NULL
,p_fiscal_year => p_fiscal_year
,p_quarter => p_quarter_num
,p_period_num => p_period_num
,p_target_exp_group => p_rate_name
,p_offset_exp_group => NULL
,p_total_pool_amount => NULL
,p_allocated_amount => 0
,p_reversal_date => NULL
,p_draft_request_id => g_request_id
,p_draft_request_date => SYSDATE
,p_release_request_id => NULL
,p_release_request_date => NULL
,p_denom_currency_code => p_currency_code
,p_fixed_amount => NULL
,p_rev_target_exp_group => NULL
,p_rev_offset_exp_group => NULL
,p_org_id => p_org_id
,p_limit_target_projects_code => 'O'
,p_cint_rate_name => p_rate_name);
,p_last_updated_date => SYSDATE
,p_last_updated_by => g_last_updated_by
,p_last_update_login => g_last_update_login
,p_level_code => 'T'
,p_exception_type => p_exception_type
,p_project_id => p_project_id
,p_task_id => p_task_id
,p_exception_code => p_exception_code);
DELETE FROM pa_alloc_runs_all
WHERE run_id = p_run_id;
* created then delete the run created in the exception portion
*/
IF g_debug_mode = 'Y' THEN
pa_debug.write_file('LOG','Inside remove_run mode = EXCEPTION');
SELECT 'Y'
INTO l_exists
FROM dual
WHERE EXISTS
(SELECT NULL
FROM PA_ALLOC_TXN_DETAILS det
WHERE det.run_id = p_run_id)
OR
EXISTS (SELECT null
FROM pa_alloc_exceptions exc
where exc.run_id = p_run_id ) ;
UPDATE pa_alloc_runs_all run
SET run.run_status = 'DF'
WHERE run.run_id = p_run_id
AND EXISTS (SELECT null
FROM pa_alloc_exceptions exc
WHERE exc.run_id = run.run_id ) ;
Delete from pa_alloc_runs_all
where run_id = p_run_id;
SELECT picc.ind_cost_code rate_name
,picc.expenditure_type exp_type
,pcri.exp_org_source exp_org_source
,pcri.threshold_amt_type threshold_amt_type
,pcri.budget_type_code budget_type
,pcri.proj_amt_threshold proj_amt_threshold
,pcri.task_amt_threshold task_amt_threshold
,pcri.proj_duration_threshold proj_duration_threshold
,pcri.task_duration_threshold task_duration_threshold
,pcri.curr_period_convention curr_period_convention
,pcri.interest_calculation_method interest_calc_method
,pcri.period_rate_code period_rate_code
,pcri.fin_plan_type_id fin_plan_type_id
FROM pa_cint_rate_info pcri
,pa_ind_cost_codes picc
WHERE pcri.ind_cost_code = picc.ind_cost_code
AND picc.ind_cost_code_usage = 'CAPITALIZED_INTEREST'
/* Start Bug fix :3028240
AND /** As discussed with murali the end date of the run period be between the
* start date active and end date active, we need not take the partial effective
* of the rate names
--((trunc(g_period_start_date) BETWEEN trunc(picc.start_date_active)
--AND trunc(nvl(picc.end_date_active,g_period_end_date)))
--OR
**
(trunc(g_period_end_date) BETWEEN trunc(picc.start_date_active)
AND trunc(nvl(picc.end_date_active,g_period_end_date)))
--)
/* End Bug fix :3028240
AND EXISTS
(SELECT 'X'
FROM pa_projects pp
,pa_ind_rate_schedules_all_bg pirs
,pa_ind_rate_sch_revisions pirsv
/* Bug fix:3208751 ,pa_cint_rate_multipliers pccm
,pa_ind_cost_multipliers pccm
WHERE pp.cint_rate_sch_id = pirs.ind_rate_sch_id
AND pirs.ind_rate_sch_usage = 'CAPITALIZED_INTEREST'
AND pirs.ind_rate_sch_id = pirsv.ind_rate_sch_id
AND pccm.ind_rate_sch_revision_id = pirsv.ind_rate_sch_revision_id
/** Added this condtion for bug fix :2984441 *
AND TRUNC(g_period_end_date) BETWEEN
TRUNC(pirsv.start_date_active) AND TRUNC(NVL(pirsv.end_date_active,g_period_end_date))
--AND NVL(pirsv.compiled_flag,'N') = 'Y'
/** End of bug fix:2984441 **/
/* Bug fix: 3208751 AND pccm.rate_name = picc.ind_cost_code
AND pccm.ind_cost_code = picc.ind_cost_code
AND pp.segment1 BETWEEN
NVL(p_from_project_num,pp.segment1) AND NVL(p_to_project_num,pp.segment1)
)
ORDER BY picc.ind_cost_code; */
SELECT distinct picc.ind_cost_code rate_name -- added distinct for bug 8876299
,picc.expenditure_type exp_type
,pcri.exp_org_source exp_org_source
,pcri.threshold_amt_type threshold_amt_type
,pcri.budget_type_code budget_type
,pcri.proj_amt_threshold proj_amt_threshold
,pcri.task_amt_threshold task_amt_threshold
,pcri.proj_duration_threshold proj_duration_threshold
,pcri.task_duration_threshold task_duration_threshold
,pcri.curr_period_convention curr_period_convention
,pcri.interest_calculation_method interest_calc_method
,pcri.period_rate_code period_rate_code
,pcri.fin_plan_type_id fin_plan_type_id
,pirs.ind_rate_sch_id interest_sch_id
FROM pa_cint_rate_info pcri
,pa_ind_cost_codes picc
,pa_ind_rate_schedules_all_bg pirs
,pa_ind_rate_sch_revisions pirsv
,pa_ind_cost_multipliers pccm
WHERE pcri.ind_cost_code = picc.ind_cost_code
AND picc.ind_cost_code_usage = 'CAPITALIZED_INTEREST'
AND (trunc(g_period_end_date) BETWEEN trunc(picc.start_date_active)
AND trunc(nvl(picc.end_date_active,g_period_end_date)))
AND pirs.ind_rate_sch_id = pirsv.ind_rate_sch_id
/* Added the condition for bug 8334911 */
AND (TRUNC(g_period_end_date) BETWEEN
TRUNC(pirsv.start_date_active) AND TRUNC(NVL(pirsv.end_date_active,g_period_end_date)))
AND pccm.ind_rate_sch_revision_id = pirsv.ind_rate_sch_revision_id
AND pccm.ind_cost_code = picc.ind_cost_code
-- AND pccm.ORGANIZATION_ID = pcri.org_id /* commented for bug 8625855 */
AND EXISTS
(SELECT 'X'
FROM pa_projects pp
WHERE pp.cint_rate_sch_id = pirs.ind_rate_sch_id
AND pp.template_flag = 'N'
AND pp.segment1 BETWEEN
NVL(p_from_project_num,pp.segment1) AND NVL(p_to_project_num,pp.segment1))
ORDER BY picc.ind_cost_code;
SELECT pp.project_id project_id
,pp.segment1 project_num
,pp.carrying_out_organization_id owning_org_id
,pp.cint_rate_sch_id interest_sch_id
,pp.start_date start_date
,ppt.capital_cost_type_code cip_cost_type
,ppt.burden_amt_display_method burden_method
,ppt.total_burden_flag tot_burden_flag
FROM pa_project_types ppt
,pa_projects pp
WHERE TRUNC(g_period_end_date) <= TRUNC(NVL(pp.cint_stop_date,g_period_end_date))
AND pp.cint_rate_sch_id IS NOT NULL
AND NVL(pp.cint_eligible_flag,'Y') = 'Y'
AND TRUNC(g_exp_item_date) BETWEEN
TRUNC(NVL(pp.start_date,g_exp_item_date)) AND TRUNC(NVL(pp.completion_date,g_exp_item_date))
AND pa_project_utils.Check_prj_stus_action_allowed
(pp.project_status_code
,'CAPITALIZED_INTEREST') = 'Y'
AND pa_project_utils.Check_prj_stus_action_allowed
(pp.project_status_code
,'NEW_TXNS') = 'Y'
AND pp.project_status_code <> 'CLOSED'
AND ppt.project_type_class_code = 'CAPITAL'
AND ppt.project_type = pp.project_type
AND pp.template_flag = 'N'
AND pp.segment1 BETWEEN
NVL(p_from_project_num,pp.segment1) AND NVL(p_to_project_num,pp.segment1)
ORDER BY pp.segment1;
SELECT pctd.task_id task_id
,pctd.task_number task_num
,pctd.task_owning_org_id task_owning_org_id
,pctd.task_start_date task_start_date
,pctd.task_completion_date task_end_date
,pctd.target_exp_organization_id exp_org_id
,pctd.rate_multiplier rate_mult
,pctd.cint_grouping_method grouping_method
,pctd.cint_cdl_status cdl_status
,SUM(DECODE(SIGN(ld_period_start_date - pctd.gl_date)
,1, pctd.amount
, 0)) prior_period_amt
,SUM(DECODE(SIGN(ld_period_start_date - pctd.gl_date)
,1, 0
, pctd.amount)) curr_period_amt
,'Y' process_task_flag
--Bug fix:3051022 Added these columns to initialize collection tables for each element
,NULL alloc_txn_id
,NULL target_task_id
,NULL cap_int_amt
,NULL attribute_category
,NULL attribute1
,NULL attribute2
,NULL attribute3
,NULL attribute4
,NULL attribute5
,NULL attribute6
,NULL attribute7
,NULL attribute8
,NULL attribute9
,NULL attribute10
FROM pa_cint_txn_details_v pctd
WHERE /* Commented out this condition for performance issues
--(
-- (NVL(lv_threshold_amt_type,'TOTAL_CIP') = 'TOTAL_CIP')
-- OR
-- (NVL(lv_threshold_amt_type,'TOTAL_CIP') <> 'TOTAL_CIP'
-- AND pctd.cint_cdl_status = 'OPEN')
--)
--AND
**/
pctd.gl_date <= TRUNC(g_period_end_date)
AND TRUNC(g_period_end_date) <= TRUNC(NVL(pctd.task_cint_stop_date, g_period_end_date))
AND TRUNC(g_exp_item_date) BETWEEN
TRUNC(NVL(pctd.task_start_date, g_exp_item_date)) AND
TRUNC(NVL(pctd.task_completion_date, g_exp_item_date))
AND pctd.project_id = g_project_id
AND pctd.cint_rate_name = g_rate_name
AND pctd.period_name = g_gl_period
GROUP BY pctd.task_id
,pctd.task_number
,pctd.task_owning_org_id
,pctd.task_start_date
,pctd.task_completion_date
,pctd.target_exp_organization_id
,pctd.rate_multiplier
,pctd.cint_grouping_method
,pctd.cint_cdl_status
,'Y'
ORDER BY pctd.task_id
,pctd.target_exp_organization_id
,pctd.rate_multiplier
,pctd.cint_grouping_method;
g_last_update_login := NVL(TO_NUMBER(fnd_profile.value('LOGIN_ID')), -1);
g_last_updated_by := g_created_by;
lt_alloc_txn_id.DELETE;
lt_task_id.DELETE;
lt_task_num.DELETE;
lt_task_owning_org_id.DELETE;
lt_task_start_date.DELETE;
lt_task_end_date.DELETE;
lt_exp_org_id.DELETE;
lt_rate_mult.DELETE;
lt_grouping_method.DELETE;
lt_cdl_status.DELETE;
lt_prior_period_amt.DELETE;
lt_curr_period_amt.DELETE;
lt_target_task_id.DELETE;
lt_cap_int_amt.DELETE;
lt_attribute_category.DELETE;
lt_attribute1.DELETE;
lt_attribute2.DELETE;
lt_attribute3.DELETE;
lt_attribute4.DELETE;
lt_attribute5.DELETE;
lt_attribute6.DELETE;
lt_attribute7.DELETE;
lt_attribute8.DELETE;
lt_attribute9.DELETE;
lt_attribute10.DELETE;
lt_process_task_flag.DELETE;
-- Update the accumulators for task-level threshold testing
ln_task_tot_amt := ln_task_tot_amt
+ lt_prior_period_amt(i)
+ lt_curr_period_amt(i);
SELECT pa_alloc_txn_details_s.nextval
INTO lt_alloc_txn_id(i)
FROM DUAL;
-- Update accumulators
ln_rate_trans_amt := ln_rate_trans_amt + lt_cap_int_amt(i);
pa_debug.write_file('LOG','Insert pa_alloc_txn_details');
INSERT INTO pa_alloc_txn_details
(alloc_txn_id
,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
,current_allocation
,status_code
,cint_source_task_id
,cint_exp_org_id
,cint_rate_multiplier
,cint_current_basis_amt
,cint_prior_basis_amt
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,ind_rate_sch_revision_id)
SELECT
lt_alloc_txn_id(k)
,ln_run_id
,g_cap_int_rule_id
,'T'
,ln_fiscal_year
,ln_quarter_num
,ln_period_num
,g_gl_period
,-1
,SYSDATE
,g_created_by
,SYSDATE
,g_last_updated_by
,g_last_update_login
,g_project_id
,lt_target_task_id(k)
,r_rate.exp_type
,lt_cap_int_amt(k)
,'P'
,lt_task_id(k)
,lt_exp_org_id(k)
,lt_rate_mult(k)
/* Bug fix:3038119 */
,NVL(lt_curr_period_amt(k),0)* NVL(ln_curr_period_mult,0)
,lt_prior_period_amt(k)
,lt_attribute_category(k)
,lt_attribute1(k)
,lt_attribute2(k)
,lt_attribute3(k)
,lt_attribute4(k)
,lt_attribute5(k)
,lt_attribute6(k)
,lt_attribute7(k)
,lt_attribute8(k)
,lt_attribute9(k)
,lt_attribute10(k)
,ln_sched_version_id
FROM DUAL
WHERE lt_process_task_flag(k) = 'Y';
'Insert Interest Transactions for Project '
||r_project.project_num||']x-errMsg['||x_error_msg_code
||']' ;
INSERT INTO pa_cint_source_details
(alloc_txn_id
,run_period_end_date
,project_id
,expenditure_item_id
,line_num
,prior_amount
,current_amount
,fiscal_year
,period_num
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
lt_alloc_txn_id(k)
,g_period_end_date
,pctd.project_id
,pctd.expenditure_item_id
,pctd.line_num
,DECODE(SIGN(ld_period_start_date - pctd.gl_date)
,1, pctd.amount
, 0)
,DECODE(SIGN(ld_period_start_date - pctd.gl_date)
,1, 0
, pctd.amount)
,ln_fiscal_year
,ln_period_num
,SYSDATE
,g_created_by
,SYSDATE
,g_last_updated_by
,g_last_update_login
FROM pa_cint_txn_details_v pctd
WHERE pctd.target_exp_organization_id = lt_exp_org_id(k)
AND NVL(pctd.rate_multiplier, lt_rate_mult(k)) =
lt_rate_mult(k)
AND NVL(pctd.cint_grouping_method,'@#$') =
NVL(lt_grouping_method(k),'@#$')
AND pctd.task_id = lt_task_id(k)
AND pctd.cint_cdl_status = 'OPEN'
AND pctd.gl_date <= TRUNC(g_period_end_date)
AND TRUNC(g_period_end_date) <=
TRUNC(NVL(pctd.task_cint_stop_date, g_period_end_date))
AND TRUNC(g_exp_item_date) BETWEEN
TRUNC(NVL(pctd.task_start_date, g_exp_item_date)) AND
TRUNC(NVL(pctd.task_completion_date, g_exp_item_date))
AND pctd.project_id = g_project_id
AND pctd.cint_rate_name = g_rate_name;
'Insert Source Details for Project'||r_project.project_num||
']x-errMsg['||x_error_msg_code||']';
-- Update the total transaction amount for the run
UPDATE pa_alloc_runs_all run
SET run.allocated_amount = -- Bug fix:2959030 ln_rate_trans_amt
(select sum(nvl(txn.current_allocation,0))
from pa_alloc_txn_details txn
where txn.run_id = run.run_id
)
,run.run_status = lv_rate_status
WHERE run.run_id = ln_run_id;
c_max_del_rows NUMBER := 10000; -- maximum rows to delete per statement
ln_rows_deleted NUMBER;
ln_tot_rows_deleted NUMBER;
ln_tot_rows_deleted := 0;
g_last_update_login := NVL(TO_NUMBER(fnd_profile.value('LOGIN_ID')), -1);
g_last_updated_by := g_created_by;
-- Delete rows in specified increments until no rows remain to be deleted
LOOP
BEGIN
DELETE FROM pa_cint_source_details pcsd
WHERE pcsd.run_period_end_date <= ld_period_end_date
AND EXISTS
(SELECT pp.project_id
FROM pa_projects pp
WHERE pp.segment1 BETWEEN
NVL(p_from_project_num, pp.segment1) AND
NVL(p_to_project_num, pp.segment1)
AND pp.project_id = pcsd.project_id)
AND rownum <= c_max_del_rows;
ln_rows_deleted := SQL%ROWCOUNT;
||' Rows deleted ['||TO_CHAR(ln_rows_deleted)||']');
ln_rows_deleted := 0;
pa_debug.g_err_stage := 'Delete_Source_Detail x-errMsg['||x_error_msg_code||']';
-- Determine if all rows have been deleted
IF ln_rows_deleted = 0 THEN
EXIT;
ln_tot_rows_deleted := ln_tot_rows_deleted + ln_rows_deleted;
||' Total Rows deleted ['||TO_CHAR(ln_tot_rows_deleted)||']');