[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT proj.project_currency_code PROJECT_CURRENCY_CODE
,NVL(x_project_rate_type,
NVL(proj.project_rate_type,imp_recvr.default_rate_type)) PROJECT_RATE_TYPE
,NVL(proj.project_rate_date,DECODE(imp_recvr.default_rate_date_code,
'E', P_EI_date, 'P'
,pa_utils2.get_pa_date(P_EI_date,sysdate, imp_recvr.org_id))) PROJECT_RATE_DATE
,NVL(x_acct_rate_type, imp_prvdr.default_rate_type) ACCT_RATE_TYPE
,NVL(x_acct_rate_date,DECODE(imp_prvdr.default_rate_date_code,
'E', P_EI_date, 'P'
,pa_utils2.get_pa_date(P_EI_date,sysdate, imp_prvdr.org_id))) ACCT_RATE_DATE
,proj.projfunc_currency_code PROJFUNC_CURRENCY_CODE
,NVL(x_projfunc_cost_rate_type,
NVL( proj.projfunc_cost_rate_type,imp_recvr.default_rate_type)) PROJFUNC_COST_RATE_TYPE
,NVL(proj.projfunc_cost_rate_date, DECODE(imp_prvdr.default_rate_date_code,
'E', P_EI_date, 'P'
,pa_utils2.get_pa_date(P_EI_date,sysdate, imp_prvdr.org_id))) PROJFUNC_COST_RATE_DATE
FROM pa_projects_all proj
,pa_implementations_all imp_prvdr
,pa_implementations_all imp_recvr /* bug fix :2322364 */
WHERE proj.project_id = P_project_id
AND imp_prvdr.org_id = P_exp_org_id
AND proj.org_id = imp_recvr.org_id; /* bug fix :2322364 */
SELECT proj.projfunc_currency_code,
NVL(P_projfunc_cost_rate_type, NVL(NVL(task.taskfunc_cost_rate_type,
proj.projfunc_cost_rate_type), imp.default_rate_type))
INTO P_projfunc_currency_code,
P_projfunc_cost_rate_type
FROM pa_projects_all proj,
pa_tasks task,
pa_implementations_all imp
WHERE proj.project_id = task.project_id
AND task.task_id = P_task_id
AND NVL(proj.org_id, -99) = NVL(imp.org_id, -99);
SELECT proj.projfunc_currency_code,
NVL(P_projfunc_cost_rate_type, NVL(NVL(task.taskfunc_cost_rate_type,
proj.projfunc_cost_rate_type), imp.default_rate_type))
INTO P_projfunc_currency_code,
P_projfunc_cost_rate_type
FROM pa_projects_all proj,
pa_tasks task,
pa_map_wp_to_fin_tasks_v map_wp_fin,
pa_implementations_all imp
WHERE proj.project_id = map_wp_fin.project_id
AND task.task_id(+) = map_wp_fin.mapped_fin_task_id
AND map_wp_fin.proj_element_id = p_task_id
AND NVL(proj.org_id, -99) = NVL(imp.org_id, -99);
SELECT proj.projfunc_currency_code,
NVL(P_projfunc_cost_rate_type, NVL(NVL(task.taskfunc_cost_rate_type,
proj.projfunc_cost_rate_type), imp.default_rate_type))
INTO P_projfunc_currency_code,
P_projfunc_cost_rate_type
FROM pa_projects_all proj,
pa_tasks task,
pa_map_wp_to_fin_tasks_v map_wp_fin,
pa_implementations_all imp
WHERE proj.project_id = p_project_id
AND task.task_id = map_wp_fin.mapped_fin_task_id
AND map_wp_fin.proj_element_id = p_task_id
AND map_wp_fin.parent_structure_version_id = p_structure_version_id
AND NVL(proj.org_id, -99) = NVL(imp.org_id, -99);
SELECT proj.projfunc_currency_code,
NVL(P_projfunc_cost_rate_type, NVL(proj.projfunc_cost_rate_type
, imp.default_rate_type))
INTO P_projfunc_currency_code,
P_projfunc_cost_rate_type
FROM pa_projects_all proj,
pa_implementations_all imp
WHERE proj.project_id = p_project_id
AND NVL(proj.org_id, -99) = NVL(imp.org_id, -99);
SELECT proj.projfunc_currency_code,
NVL(P_projfunc_cost_rate_type, NVL(NVL(task.taskfunc_cost_rate_type,
proj.projfunc_cost_rate_type), imp.default_rate_type))
INTO P_projfunc_currency_code,
P_projfunc_cost_rate_type
FROM pa_projects_all proj,
pa_tasks task,
pa_implementations_all imp -- bug 8265941 changed to pa_implementations_all
WHERE proj.project_id = task.project_id
AND task.task_id = P_task_id
AND NVL(proj.org_id,-99) = NVL(imp.org_id,-99); -- bug 7579126
SELECT project_currency_code
INTO l_project_currency_code
FROM pa_projects_all
WHERE project_id = P_project_id ;
SELECT NVL(NVL(task.taskfunc_cost_rate_date,
proj.projfunc_cost_rate_date),
DECODE(imp.default_rate_date_code,
'E', P_EI_date, 'P',
pa_utils2.get_pa_date(P_EI_date,
sysdate, imp.org_id))) /**CBGA**/
INTO P_projfunc_cost_rate_date
FROM pa_projects_all proj,
pa_tasks task,
pa_implementations_all imp -- bug 8265941 changed to pa_implementations_all
WHERE task.task_id = P_task_id
AND proj.project_id = task.project_id
AND NVL(proj.org_id,-99) = NVL(imp.org_id,-99); -- bug 7579126
SELECT task.taskfunc_cost_rate_date
INTO P_projfunc_cost_rate_date
FROM pa_tasks task
,pa_map_wp_to_fin_tasks_v map_wp_fin
WHERE task.task_id = map_wp_fin.mapped_fin_task_id
AND map_wp_fin.proj_element_id = P_task_id
AND map_wp_fin.parent_structure_version_id = p_structure_version_id;
SELECT NVL(proj.projfunc_cost_rate_date,
DECODE(imp.default_rate_date_code,
'E', P_EI_date, 'P',
pa_utils2.get_pa_date(P_EI_date,
sysdate, imp.org_id)))
INTO P_projfunc_cost_rate_date
FROM pa_projects_all proj
,pa_implementations_all imp -- bug 8265941 changed to pa_implementations_all
WHERE proj.project_id = p_project_id
AND NVL(proj.org_id,-99) = NVL(imp.org_id,-99); -- bug 7579126
SELECT NVL(NVL(task.taskfunc_cost_rate_date,
proj.projfunc_cost_rate_date),
DECODE(imp.default_rate_date_code,
'E', P_EI_date, 'P',
NULL))
INTO P_projfunc_cost_rate_date
FROM pa_projects_all proj,
pa_tasks task,
pa_implementations_all imp -- bug 8265941 changed to pa_implementations_all
WHERE task.task_id = P_task_id
AND proj.project_id = task.project_id
AND NVL(proj.org_id,-99) = NVL(imp.org_id,-99); -- bug 7579126
SELECT NVL(NVL(task.taskfunc_cost_rate_date,
proj.projfunc_cost_rate_date),
DECODE(imp.default_rate_date_code,
'E', P_EI_date, 'P',
NULL))
INTO P_projfunc_cost_rate_date
FROM pa_projects_all proj,
pa_tasks task,
pa_map_wp_to_fin_tasks_v map_wp_fin,
pa_implementations imp
WHERE proj.project_id = map_wp_fin.project_id
AND task.task_id(+) = map_wp_fin.mapped_fin_task_id
AND map_wp_fin.proj_element_id = p_task_id;
SELECT task.taskfunc_cost_rate_date
INTO P_projfunc_cost_rate_date
FROM pa_tasks task
,pa_map_wp_to_fin_tasks_v map_wp_fin
WHERE task.task_id = map_wp_fin.mapped_fin_task_id
AND map_wp_fin.proj_element_id = p_task_id
AND map_wp_fin.parent_structure_version_id = p_structure_version_id;
SELECT NVL(proj.projfunc_cost_rate_date,
DECODE(imp.default_rate_date_code,
'E', P_EI_date, 'P', NULL))
INTO P_projfunc_cost_rate_date
FROM pa_projects_all proj
,pa_implementations_all imp -- bug 8265941 changed to pa_implementations_all
WHERE proj.project_id = p_project_id
AND NVL(proj.org_id,-99) = NVL(imp.org_id,-99); -- bug 7579126
SELECT NVL(P_acct_rate_date,DECODE(default_rate_date_code,
'E', P_EI_date, 'P',
pa_utils2.get_pa_date(P_EI_date,
sysdate, org_id))) /**CBGA**/
INTO P_acct_rate_date
FROM pa_implementations ;
SELECT NVL(P_acct_rate_date,DECODE(default_rate_date_code,
'E', P_EI_date, 'P',
NULL))
INTO P_acct_rate_date
FROM pa_implementations ;
P_Update_Count OUT NOCOPY NUMBER)
IS
/*
* Variable Declarations
*/
V_loop_index NUMBER := 1;
SELECT
ITEM.expenditure_item_id,
ITEM.expenditure_item_date,
ITEM.Task_Id,
EXP.expenditure_id,
NVL(ITEM.Denom_Currency_Code,ITEM.Acct_Currency_Code) Denom_Currency_Code,
DECODE(ITEM.System_Linkage_Function,'BTC',
ITEM.Denom_Burdened_Cost,ITEM.Denom_Raw_Cost) Denom_Raw_Cost,
ITEM.Acct_Raw_Cost,
ITEM.Acct_Currency_Code,
DECODE(ITEM.system_linkage_function, 'ER', EXP.Acct_Rate_Date, ITEM.Acct_Rate_Date) Acct_Rate_Date,
DECODE(ITEM.system_linkage_function, 'ER', EXP.Acct_Rate_Type, ITEM.Acct_Rate_Type) Acct_Rate_Type,
DECODE(ITEM.system_linkage_function, 'ER', EXP.Acct_Exchange_Rate, ITEM.Acct_Exchange_Rate) Acct_Exchange_Rate,
ITEM.Raw_Cost,
ITEM.Projfunc_Currency_Code,
ITEM.Projfunc_Cost_Rate_Date,
ITEM.Projfunc_Cost_Rate_Type,
ITEM.Projfunc_Cost_Exchange_Rate,
ITEM.Project_Raw_Cost,
ITEM.Project_Currency_Code,
ITEM.Project_Rate_Date,
ITEM.Project_Rate_Type,
ITEM.Project_Exchange_Rate,
ITEM.Source_Expenditure_Item_ID Source_Id,
ITEM.Net_Zero_Adjustment_Flag Net_zero,
ITEM.org_id,
ITEM.expenditure_type,
ITEM.system_linkage_function,
ITEM.transaction_source,
TXN.GL_Accounted_Flag, /* Bug #1824407 */
NVL(ITEM.override_to_organization_id,EXP.incurred_by_organization_id) exp_organization_id,
ITEM.Organization_Id nlr_organization_id,
EXP.incurred_by_person_id,
ITEM.Cc_Cross_Charge_Type,
ITEM.Cc_Cross_Charge_Code,
ITEM.Cc_Prvdr_Organization_Id,
ITEM.Cc_Recvr_Organization_Id,
ITEM.Recvr_Org_Id
,ITEM.PO_Line_Id --3535935 hkulkarn
/**CBGA select job_id and project_group_id using API GetProjectGroupId().
** To be uncommented after decing upon rates-model.
** ITEM.Job_Id,
** ITEM.Cost_Job_Id,
** PA_Cross_Business_Grp.GetProjectGroupId(TASK.Project_Id, 'C') job_group_id
**/
FROM PA_Expenditure_Items ITEM,
PA_Expenditures EXP,
PA_Transaction_Sources TXN /* Bug 1824407 */
/** To be uncommented after decing upon rates-model.
** CBGA Join pa_tasks with pa_expenditure_items_all.
** PA_Tasks TASK
**/
WHERE ITEM.Cost_Distributed_Flag = 'S'
AND ITEM.Cost_Dist_Rejection_Code IS NULL
AND ITEM.Request_id = P_request_id
AND ITEM.expenditure_id = EXP.expenditure_id
AND ITEM.Transaction_Source = TXN.Transaction_Source (+)/*Bug1824407*/
AND (( ITEM.Source_Expenditure_Item_Id IS NULL
AND l_related_item = 'N')
OR
( ITEM.Source_Expenditure_Item_Id IS NOT NULL
AND l_related_item = 'Y'))
/** To be uncommented after decing upon rates-model.
** CBGA Joining pa_tasks with EIs.
** AND ITEM.task_id = TASK.task_id
**/
;
P_update_count := 0;
* for later use in the update. The IC API is called outside this loop
* since array parameters are accepted by it. The final update
* takes care of both MC and IC.
*/
FOR loop_control in 1 .. 2
LOOP
IF P_DEBUG_MODE THEN
pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'In outer Loop');
* so that the Identification process doesnt update this value.
*/
IF P_DEBUG_MODE THEN
pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before setting Array variables');
* The output values are stored into array for later use in the update.
*
* Note: These assigments need not to be kept within the previous if statement
* because the variables are initialized with the existing values
* of the EIs before the MC Call. So even if MC call is not made
* ( because of the existence of both the buckets ) the old
* values will be assigned to the array elelemnts. So, final update
* will update the columns with the existing values only and that is fine.
*/
IF P_DEBUG_MODE THEN
pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before storing in Array ');
P_Source := 'Update Error';
pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before updating Expenditures. Count to update [' || to_char(l_er_exp_count) || ']');
UPDATE pa_expenditures exp
SET exp.Acct_Exchange_rate = l_exp_acct_exch_rate_tab(i)
WHERE exp.expenditure_id = l_er_expenditure_id_tab(i)
;
* Final update statement to update all the relevant columns for
* MC as well as IC. ( arrays are used for that )
*
* Note: The MC related column are updated only when the cost in the
* appropriate currency is not available and the MC/IC API hasnot returned
* any error.
* The IC related columns are updated only when the MC/IC API hasnot returned
* any error.
*
* Denom_Currency_Code shouldn't be null under normal circumstances,
* for safety purpose it is set to accounting currency code if it's value
* is null.
*/
IF V_loop_index > 0 THEN
IF P_DEBUG_MODE THEN
pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'Before Final update statement');
select count(*) into l_cc_dist_count_tab(loop_index)
from pa_cc_dist_lines_all
where expenditure_item_id = PA_CC_IDENT.ExpItemIdTab(loop_index)
and line_type = 'BL' ; /*Bug# 3184731 :Excluding line_type ='PC' here */
select ei1.cost_distributed_flag,ei1.Cc_Cross_Charge_Type,
ei1.Cc_Prvdr_Organization_Id,ei1.Cc_Recvr_Organization_Id,
ei1.Recvr_Org_Id
into l_parent_cdf_tab(loop_index),l_parent_CCharge_Type(loop_index),
l_parent_Cc_Prvdr_Org_Id(loop_index),l_parent_Cc_Recvr_Org_Id(loop_index),
l_parent_Recvr_Org_Id(loop_index) from
pa_expenditure_items_all ei1,pa_expenditure_items_all ei2
where ei1.expenditure_item_id = ei2.adjusted_expenditure_item_id and
ei2.expenditure_item_id = PA_CC_IDENT.ExpItemIdTab(loop_index);
UPDATE Pa_Expenditure_Items ITEM
SET ITEM.Denom_Currency_Code =
DECODE(ITEM.Denom_Currency_Code, NULL,
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
PA_CC_IDENT.DenomCurrCodeTab(loop_index),
ITEM.Denom_Currency_Code),
ITEM.Denom_Currency_Code),
ITEM.Cost_Dist_Rejection_Code =
PA_CC_IDENT.StatusTab(loop_index),
/** To be uncommented after decing upon rates-model.
** CBGA StatusTab contains the status information for the call pa_cc_identify_txn.
** ErrorStageTab contains the status information for the call GetMappedToJobs.
** The rejection code is set - when either of them is NOT NULL.
**
** ITEM.Cost_Dist_Rejection_Code =
** decode ( PA_CC_IDENT.StatusTab(loop_index), NULL,
** ErrorStageTab(loop_index) ,
** PA_CC_IDENT.StatusTab(loop_index)
** ),
**/
ITEM.Burden_Cost =
DECODE(ITEM.Burden_Cost,NULL,
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
DECODE(ITEM.System_Linkage_Function,'BTC',
PA_CC_IDENT.ProjFuncRawCostTab(loop_index), /* Replaced ProjRawCostTab by ProjFuncRawCostTab for bug 3285759 */
ITEM.Burden_Cost),
ITEM.Burden_Cost),
ITEM.Burden_Cost),
/***** Added for 3285759 */
ITEM.Project_Burdened_Cost =
DECODE(ITEM.Project_Burdened_Cost,NULL,
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
DECODE(ITEM.System_Linkage_Function,'BTC',
PA_CC_IDENT.ProjRawCostTab(loop_index),
ITEM.Project_Burdened_Cost),
ITEM.Project_Burdened_Cost),
ITEM.Project_Burdened_Cost),
/***** Added for 3285759 End */
ITEM.Acct_Burdened_Cost =
DECODE(ITEM.Acct_Burdened_Cost,NULL,
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
DECODE(ITEM.System_Linkage_Function,'BTC',
PA_CC_IDENT.AcctRawCostTab(loop_index),
ITEM.Acct_Burdened_Cost),
ITEM.Acct_Burdened_Cost),
ITEM.Acct_Burdened_Cost),
ITEM.Raw_Cost =
DECODE(ITEM.Raw_Cost, NULL,
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
DECODE(ITEM.System_Linkage_Function,'BTC',
0,
PA_CC_IDENT.ProjFuncRawCostTab(loop_index)),
ITEM.Raw_Cost),
ITEM.Raw_Cost),
ITEM.ProjFunc_Cost_Exchange_Rate =
DECODE(ITEM.Raw_Cost,NULL,
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
PA_CC_IDENT.ProjFuncRateTab(loop_index),
ITEM.ProjFunc_Cost_Exchange_Rate),
ITEM.ProjFunc_Cost_exchange_Rate),
ITEM.projfunc_cost_rate_Date =
DECODE(ITEM.Raw_Cost,NULL,
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
to_date(PA_CC_IDENT.ProjFuncRateDateTab(loop_index),'dd-mm-yyyy hh:mi:ss'),
ITEM.projfunc_cost_rate_Date),
ITEM.projfunc_cost_rate_Date),
ITEM.projfunc_cost_rate_Type =
DECODE(ITEM.Raw_Cost,NULL,
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
PA_CC_IDENT.ProjFuncRateTypeTab(loop_index),
ITEM.projfunc_cost_rate_Type),
ITEM.projfunc_cost_rate_Type),
ITEM.Project_Raw_Cost =
DECODE(ITEM.Project_Raw_Cost, NULL,
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
DECODE(ITEM.System_Linkage_Function,'BTC',
0,
PA_CC_IDENT.ProjRawCostTab(loop_index)),
ITEM.Project_Raw_Cost),
ITEM.Project_Raw_Cost),
ITEM.Project_Exchange_Rate =
DECODE(ITEM.Project_Raw_Cost,NULL,
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
PA_CC_IDENT.ProjRateTab(loop_index),
ITEM.Project_Exchange_Rate),
ITEM.Project_exchange_Rate),
ITEM.Project_Rate_Date =
DECODE(ITEM.Project_Raw_Cost,NULL,
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
to_date(PA_CC_IDENT.ProjRateDateTab(loop_index),'dd-mm-yyyy hh:mi:ss'),
ITEM.Project_Rate_Date),
ITEM.Project_Rate_Date),
ITEM.Project_Rate_Type =
DECODE(ITEM.Project_Raw_Cost,NULL,
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
PA_CC_IDENT.ProjRateTypeTab(loop_index),
ITEM.Project_Rate_Type),
ITEM.Project_Rate_Type),
ITEM.Acct_Raw_Cost =
DECODE(ITEM.Acct_Raw_Cost,NULL,
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
DECODE(ITEM.System_Linkage_Function,'BTC',
0,
PA_CC_IDENT.AcctRawCostTab(loop_index)),
ITEM.Acct_Raw_Cost),
ITEM.Acct_Raw_Cost),
ITEM.Acct_Exchange_Rate =
DECODE(ITEM.Acct_Raw_Cost,NULL,
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
PA_CC_IDENT.AcctRateTab(loop_index),
ITEM.Acct_Exchange_Rate),
ITEM.Acct_Exchange_Rate),
ITEM.Acct_Rate_Date =
DECODE(ITEM.Acct_Raw_Cost,NULL,
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
to_date(PA_CC_IDENT.AcctRateDateTab(loop_index),'dd-mm-yyyy hh:mi:ss'),
ITEM.Acct_Rate_Date),
ITEM.Acct_Rate_Date),
ITEM.Acct_Rate_Type =
DECODE(ITEM.Acct_Raw_Cost,NULL,
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
PA_CC_IDENT.AcctRateTypeTab(loop_index),
ITEM.Acct_Rate_Type),
ITEM.Acct_Rate_Type),
ITEM.Cc_Cross_Charge_Code =
Decode(ITEM.Cc_Cross_Charge_Code,'P', -- Added Decode wrapper to update only for P case 3173932
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
PA_CC_IDENT.CrossChargeCodeTab(loop_index),
ITEM.Cc_Cross_Charge_Code),ITEM.Cc_Cross_Charge_Code),
ITEM.Cc_Cross_Charge_Type =
DECODE(l_parent_cdf_tab(loop_index),'Y',l_parent_CCharge_Type(loop_index), /*Added Decode wrapper not to rederive if parent ei is cost distributed bug 12983087*/
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
PA_CC_IDENT.CrossChargeTypeTab(loop_index),
ITEM.Cc_Cross_Charge_Type)),
ITEM.Cc_Bl_Distributed_Code =
DECODE(l_parent_cdf_tab(loop_index),'Y','N', /*Added Decode wrapper not to rederive if parent ei is cost distributed bug 12983087*/
DECODE(PA_CC_IDENT.StatusTab(loop_index), NULL,
DECODE(PA_CC_IDENT.CrossChargeCodeTab(loop_index),'B',
'N',
DECODE(l_cc_dist_count_tab(loop_index),0,'X','N')), /* bug#2919885 */
ITEM.Cc_Bl_Distributed_Code)),
ITEM.Cc_IC_Processed_Code =
DECODE(l_parent_cdf_tab(loop_index),'Y','N', /*Added Decode wrapper not to rederive if parent ei is cost distributed bug 12983087*/
DECODE(PA_CC_IDENT.StatusTab(loop_index), NULL,
DECODE(PA_CC_IDENT.CrossChargeCodeTab(loop_index),'I',
'N',
'X'),
ITEM.Cc_IC_processed_Code)),
ITEM.Cc_Prvdr_Organization_Id =
DECODE(l_parent_cdf_tab(loop_index),'Y',l_parent_Cc_Prvdr_Org_Id(loop_index), /*Added Decode wrapper not to rederive if parent ei is cost distributed bug 12983087*/
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
PA_CC_IDENT.PrvdrOrganizationIdTab(loop_index),
ITEM.Cc_Prvdr_Organization_Id)),
ITEM.Cc_Recvr_Organization_Id =
DECODE(l_parent_cdf_tab(loop_index),'Y',l_parent_Cc_Recvr_Org_Id(loop_index), /*Added Decode wrapper not to rederive if parent ei is cost distributed bug 12983087*/
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
PA_CC_IDENT.RecvrOrganizationIdTab(loop_index),
ITEM.Cc_Recvr_Organization_Id)),
ITEM.Recvr_Org_Id =
DECODE(l_parent_cdf_tab(loop_index),'Y',l_parent_Recvr_Org_Id(loop_index), /*Added Decode wrapper not to rederive if parent ei is cost distributed bug 12983087*/
DECODE(PA_CC_IDENT.StatusTab(loop_index),NULL,
PA_CC_IDENT.RecvrOrgIdTab(loop_index),
ITEM.Recvr_Org_Id))
/** To be uncommented after decing upon rates-model.
** CBGA Updating Cost_Job_Id in EI.
** ITEM.cost_job_id =
** DECODE(ErrorStageTab(loop_index),NULL,
** CostJobIdTab(loop_index),
** ITEM.cost_job_id)
**/
WHERE ITEM.Expenditure_Item_Id = PA_CC_IDENT.ExpItemIdTab(loop_index);
P_Update_Count := SQL%ROWCOUNT;
select ei1.cost_distributed_flag,ei1.Cc_Cross_Charge_Type,
ei1.Cc_Prvdr_Organization_Id,ei1.Cc_Recvr_Organization_Id,
ei1.Recvr_Org_Id
into l_parent_cdf_tab(loop_index),l_parent_CCharge_Type(loop_index),
l_parent_Cc_Prvdr_Org_Id(loop_index),l_parent_Cc_Recvr_Org_Id(loop_index),
l_parent_Recvr_Org_Id(loop_index)
from pa_expenditure_items_all ei1,pa_expenditure_items_all ei2
where ei1.expenditure_item_id = ei2.adjusted_expenditure_item_id and
ei2.expenditure_item_id = PA_CC_IDENT.ExpItemIdTab(loop_index)
AND ei1.Request_id = P_request_id;
UPDATE Pa_Expenditure_Items ITEM
SET ITEM.Cc_Cross_Charge_Type =
DECODE(l_parent_cdf_tab(loop_index),'S',l_parent_CCharge_Type(loop_index), ITEM.Cc_Cross_Charge_Type),
ITEM.Cc_Prvdr_Organization_Id =
DECODE(l_parent_cdf_tab(loop_index),'S',l_parent_Cc_Prvdr_Org_Id(loop_index), ITEM.Cc_Prvdr_Organization_Id),
ITEM.Cc_Recvr_Organization_Id =
DECODE(l_parent_cdf_tab(loop_index),'S',l_parent_Cc_Recvr_Org_Id(loop_index), ITEM.Cc_Recvr_Organization_Id),
ITEM.Recvr_Org_Id =
DECODE(l_parent_cdf_tab(loop_index),'S',l_parent_Recvr_Org_Id(loop_index), ITEM.Recvr_Org_Id)
WHERE ITEM.Expenditure_Item_Id = PA_CC_IDENT.ExpItemIdTab(loop_index)
AND ITEM.adjusted_expenditure_item_id is NOT NULL;
pa_cc_utils.log_message('Perform_MC_and_IC_processing: ' || 'After Final Update Statement');
* No of records updated is set to the output parameter.
*/
/* Commented for Bug 2984871
P_Update_Count := SQL%ROWCOUNT; */
SELECT proj.project_currency_code,
NVL(P_project_rate_type, NVL(NVL(task.project_rate_type,
proj.project_rate_type), imp.default_rate_type))
INTO P_project_currency_code,
P_project_rate_type
FROM pa_projects_all proj,
pa_tasks task,
pa_implementations_all imp
WHERE proj.project_id = task.project_id
AND task.task_id = P_task_id
AND proj.org_id = imp.org_id;
SELECT proj.project_currency_code,
NVL(P_project_rate_type, NVL(NVL(task.project_rate_type,
proj.project_rate_type), imp.default_rate_type))
INTO P_project_currency_code,
P_project_rate_type
FROM pa_projects_all proj,
pa_tasks task,
pa_map_wp_to_fin_tasks_v map_wp_fin,
pa_implementations_all imp
WHERE proj.project_id = p_project_id
AND task.task_id = map_wp_fin.mapped_fin_task_id
AND map_wp_fin.proj_element_id = p_task_id
AND map_wp_fin.parent_structure_version_id = p_structure_version_id
AND proj.org_id = imp.org_id;
SELECT proj.project_currency_code,
NVL(NVL(P_project_rate_type, proj.project_rate_type)
, imp.default_rate_type)
INTO P_project_currency_code,
P_project_rate_type
FROM pa_projects_all proj,
pa_implementations_all imp
WHERE proj.project_id = p_project_id
AND proj.org_id = imp.org_id;
SELECT NVL(NVL(task.project_rate_date,
proj.project_rate_date),
DECODE(imp.default_rate_date_code,
'E', P_EI_date, 'P',
pa_utils2.get_pa_date(P_EI_date,
sysdate, imp.org_id))) /**CBGA**/
INTO P_project_rate_date
FROM pa_projects_all proj,
pa_tasks task,
pa_implementations_all imp
WHERE task.task_id = P_task_id
AND proj.project_id = task.project_id
AND nvl(proj.org_id, -99) = nvl(imp.org_id, -99);
SELECT task.project_rate_date
INTO P_project_rate_date
FROM pa_tasks task
,pa_map_wp_to_fin_tasks_v map_wp_fin
WHERE task.task_id = map_wp_fin.mapped_fin_task_id
AND map_wp_fin.proj_element_id = p_task_id
AND map_wp_fin.parent_structure_version_id = p_structure_version_id;
SELECT NVL(proj.project_rate_date,
DECODE(imp.default_rate_date_code,
'E', P_EI_date, 'P',
pa_utils2.get_pa_date(P_EI_date,
sysdate, imp.org_id)))
INTO P_project_rate_date
FROM pa_projects_all proj
,pa_implementations_all imp
WHERE proj.project_id = p_project_id
AND nvl(proj.org_id, -99) = nvl(imp.org_id, -99);