[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PA_FCK_UTIL.debug_msg('Before executing bc controls select statement ..........');
SELECT
BVER.Budget_Type_Code BUDGET_TYPE_CODE,
BVER.Project_ID PROJECT_ID,
BEM.Entry_Level_Code BUDGET_ENTRY_LEVEL_CODE
INTO
l_Budget_Type_Code,
l_Project_ID,
l_Budget_Entry_Level_Code
FROM
PA_Budget_versions BVER,
PA_Budget_Entry_Methods BEM,
PA_Budgetary_Control_Options BCO
WHERE
BVER.Budget_Version_ID = p_Budget_version_Id
AND BEM.Budget_Entry_Method_Code = BVER.Budget_Entry_Method_Code
AND BCO.Budget_Type_Code = BVER.Budget_Type_Code
AND BCO.Project_ID = BVER.Project_ID
AND BCO.External_Budget_Code IS NOT NULL ;
PA_FCK_UTIL.debug_msg(' '||to_char(SQL%ROWCOUNT)||' Rows selected/update/deleted/inserted ..........');
SELECT
BL.Code_Combination_ID CCID_old,
BL.Resource_Assignment_ID Resource_Assignment_ID,
BL.Start_Date Start_Date,
BL.End_Date End_Date,
NVL(RM.Parent_Member_ID, RM.Resource_List_Member_ID) Resource_Group_ID,
RTYPE.Resource_Type_Code Resource_Type,
DECODE(RTYPE.Resource_Type_Code, NULL, 'N', 'Y') Resource_List_Flag,
RA.resource_list_member_id resource_list_member_id,
RM.Parent_Member_ID parent_resource_id,
RM.alias resource_name,
RA.Task_ID Task_ID,
nvl(PT.Top_task_id,-99) Top_task_id,
PT.task_number task_number,
RT.Person_ID Person_ID,
RT.Expenditure_Category Expenditure_Category,
RT.Expenditure_Type Expenditure_Type,
RT.Job_ID Job_ID,
RT.Organization_ID Organization_ID,
RT.Vendor_ID Supplier_ID,
BL.Period_Name Period_Name,
decode(nvl(l_balance_type,'X'),
'E', decode(NVL(BL.Burdened_Cost,0),
0,nvl(bl.raw_cost,0),
bl.burdened_cost ) ,
'B',decode(l_Budget_Amount_Code,
'R',nvl(bl.revenue,0) ,
'C', decode(NVL(BL.Burdened_Cost,0),
0,nvl(bl.raw_cost,0),
bl.burdened_cost ),
0 ),
0 ) Total_Amount,
BL.txn_currency_code
FROM
PA_Resource_Types RTYPE,
PA_Budget_Lines BL,
PA_Resources RS,
PA_Resource_List_Members RM,
PA_Resource_Assignments RA,
PA_Resource_Txn_Attributes RT,
PA_TASKS PT
WHERE
-- ra.Budget_Version_ID = P_Budget_Version_ID AND
ra.Resource_Assignment_ID = BL.Resource_Assignment_ID
AND RA.Resource_List_Member_ID = RM.Resource_List_Member_ID
AND RM.Resource_ID = RS.Resource_ID
AND RM.Resource_ID = RT.Resource_ID (+)
AND RS.Resource_Type_ID = RTYPE.Resource_Type_ID
AND ra.budget_version_id = BL.budget_version_id
AND BL.Budget_Version_ID = P_Budget_Version_ID
AND PT.task_id(+) = RA.Task_ID ;
Select PBCO.Balance_type,
PBT.Budget_Amount_Code
From PA_BUDGETARY_CONTROL_OPTIONS PBCO ,
PA_BUDGET_VERSIONS PBV ,
PA_BUDGET_TYPES PBT
WHERE PBCO.Project_Id = p_project_id
AND PBV.Budget_version_id = p_Budget_version_id
AND PBV.Budget_Type_Code = PBCO.Budget_Type_Code
AND PBV.Budget_Type_Code = PBT.Budget_Type_Code;
SELECT
PROJ.Segment1 PROJECT_NUMBER,
ORG.Name PROJECT_ORGANIZATION_NAME,
ORG.Organization_ID PROJECT_ORGANIZATION_ID,
PROJ.Project_Type PROJECT_TYPE,
PROJ.Start_Date PROJ_START_DATE,
PROJ.Completion_Date PROJ_END_DATE
INTO
l_Project_Number,
l_Project_Org_Name,
l_Project_Org_Id,
l_Project_Type,
l_Project_Start_Date,
l_Project_End_Date
FROM
HR_All_Organization_Units ORG,
PA_Projects PROJ
WHERE
PROJ.Project_ID = P_Project_ID
AND ORG.Organization_ID = PROJ.Carrying_Out_Organization_ID ;
PA_FCK_UTIL.debug_msg('after selecting proj org details ..........');
SELECT
CLASS.Class_Code PROJECT_CLASS_CODE
INTO l_Project_Class_Code
FROM
PA_Project_Classes CLASS,
Pa_Class_Categories CLCAT
WHERE
CLCAT.Autoaccounting_Flag = 'Y' AND
CLCAT.Class_Category = CLASS.Class_Category AND
CLASS.Project_ID = P_Project_ID;
PA_FCK_UTIL.debug_msg('after selecting class code ..........');
SELECT
MAX(Version_Number)
INTO
l_Max_Version_Number
FROM
PA_Budget_Versions
WHERE
Project_ID = p_Project_ID
AND Budget_Status_Code = 'B'
AND Budget_Type_Code = p_Budget_Type_Code ;
SELECT
Budget_Version_ID
INTO
l_Prev_Budget_Version_ID
FROM
PA_Budget_Versions
WHERE
Project_ID = p_Project_ID
AND Budget_Status_Code = 'B'
AND Budget_Type_Code = p_Budget_Type_Code
AND Version_Number = l_Max_Version_Number - 1; --Bug 6524116
PA_FCK_UTIL.debug_msg('after selecting max version number ..........'||to_char(l_max_version_number));
PA_FCK_UTIL.debug_msg('after selecting prev_budget_version_id ..........'||to_char(l_prev_budget_version_id));
PA_FCK_UTIL.debug_msg(' before calling .... Insert_Into_Budget_Lines ');
PA_BUDGET_ACCOUNT_PKG.Insert_Into_Budget_Lines (
P_Budget_Version_ID,
P_Project_ID,
l_Project_Start_Date,
l_Project_End_Date,
l_Return_Status,
l_Msg_Count,
l_Msg_Data );
PA_FCK_UTIL.debug_msg('after calling insert into budget lines ..........');
DELETE FROM PA_BUDGET_ACCT_LINES
WHERE Budget_Version_ID = p_Budget_Version_ID;
Select 'Y'
into l_txn_exists_against_project
from dual where exists (select 1
from pa_bc_balances
where budget_version_id = l_Prev_Budget_Version_ID
and balance_type <> 'BGT');
Select 'Y'
into l_txn_exists_against_project
from dual where exists (select 1
from pa_bc_packets
where budget_version_id = l_Prev_Budget_Version_ID
and status_code in ('P','Z','A','I'));
select code_combination_id
into l_prev_ccid
from pa_budget_lines pbl,
pa_resource_assignments pra
where pra.budget_version_id = l_Prev_Budget_Version_ID
and pra.resource_list_member_id = bl.resource_list_member_id
and pra.project_id = p_project_id
and pra.task_id = bl.task_id
and pbl.budget_version_id = pra.budget_version_id
and pbl.resource_assignment_id = pra.resource_assignment_id
and pbl.start_date = bl.start_date
and pbl.txn_currency_code = bl.txn_currency_code;
select description
into l_Error_message
from pa_lookups
where lookup_type = 'FC_RESULT_CODE'
and lookup_code = 'F169';
UPDATE PA_BUDGET_LINES
SET Code_Combination_ID = l_CCID,
CCID_Gen_Status_Code = 'Y'
WHERE Resource_Assignment_ID = bl.Resource_Assignment_ID AND
Start_Date = bl.Start_Date ;
UPDATE PA_BUDGET_LINES
SET CCID_Gen_Rej_Message = l_Msg_Data,
CCID_Gen_Status_Code = 'N'
WHERE Resource_Assignment_ID = bl.Resource_Assignment_ID AND
Start_Date = bl.Start_Date ;
UPDATE PA_BUDGET_LINES
SET CCID_Gen_Rej_Message = l_Error_message,
CCID_Gen_Status_Code = 'N'
WHERE Resource_Assignment_ID = bl.Resource_Assignment_ID AND
Start_Date = bl.Start_Date ;
SELECT 'Y'
INTO l_is_cc_budget
FROM pa_budgetary_control_options
WHERE project_id = p_Project_ID
AND Budget_Type_Code = p_Budget_Type_Code
AND EXTERNAL_BUDGET_CODE = 'CC'
AND BDGT_CNTRL_FLAG = 'Y';
INSERT INTO PA_BUDGET_ACCT_LINES (
Budget_Acct_Line_ID,
Budget_Version_ID,
GL_Period_Name,
Start_Date,
End_Date,
Code_Combination_ID,
Prev_Ver_Budget_Amount,
Prev_Ver_Available_Amount,
Curr_Ver_Budget_Amount,
Curr_Ver_Available_Amount,
accounted_amount,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY
)
SELECT
PA_BUDGET_ACCT_LINES_S.nextval,
P_Budget_Version_ID,
BL1.GL_Period_Name,
BL1.Start_Date,
BL1.End_Date,
BL1.Code_Combination_ID,
BL1.Curr_Ver_Budget_Amount,
BL1.Curr_Ver_Available_Amount,
0,
-- Prev_Ver_Budget_Amount - Prev_Ver_Available_Amount, -- Bug # 2008368
0, -- Curr_Ver_Available_Amount
/* -- Commented for bug 30399850 - BL1.Curr_Ver_Available_Amount, -- Accounted Amount */
0 - BL1.Curr_Ver_Budget_Amount, /* Accounted Amount Added for bug3039985 */
sysdate,
-1,
-1,
sysdate,
-1
FROM
PA_BUDGET_ACCT_LINES BL1
WHERE
BL1.Budget_Version_ID = l_Prev_Budget_Version_ID
AND NOT EXISTS
( SELECT 'x'
FROM PA_BUDGET_ACCT_LINES BL2
WHERE BL2.Code_Combination_ID = BL1.Code_Combination_ID
AND BL2.Budget_Version_ID = P_Budget_Version_ID
AND BL2.Start_Date = BL1.Start_Date ) ;
SELECT
top_task_Id,
DECODE(task_Id, top_task_Id, NULL, task_Id)
INTO
l_top_task_Id,
l_low_task_Id
FROM
PA_Tasks
WHERE
task_Id = p_task_Id
AND project_Id = p_project_Id ;
select sob.chart_of_accounts_id
into l_chart_of_accounts_id
from pa_implementations imp, gl_sets_of_books sob
where imp.set_of_books_id = sob.set_of_books_id;
parameter TRUE to insert_if_new argument */
l_result := fnd_Flex_workflow.generate( l_Itemtype,
l_Itemkey,
TRUE,
l_return_Ccid,
l_Concat_segs,
l_Concat_Ids,
l_Concat_Descrs,
l_Error_message,
l_code_combination);
l_update_count NUMBER ;
UPDATE
PA_BUDGET_ACCT_LINES
SET
Curr_Ver_Budget_Amount = Curr_Ver_Budget_Amount + p_Amount,
Curr_Ver_Available_Amount = Curr_Ver_Available_Amount + p_Amount,
accounted_amount = accounted_amount + p_Amount
WHERE
Budget_Version_ID = p_Budget_Version_ID
AND Start_Date = p_Start_Date
AND Code_Combination_ID = p_CCID ;
l_update_count := SQL%ROWCOUNT;
PA_FCK_UTIL.debug_msg('updated pa_budget_acct_line ......row '||to_char(l_update_count));
IF l_update_count = 0 THEN
PA_FCK_UTIL.debug_msg(' record not found ');
SELECT
Curr_Ver_Budget_Amount,
Curr_Ver_Available_Amount
INTO
l_Prev_Ver_Budget_Amount,
l_Prev_Ver_Available_Amount
FROM
PA_BUDGET_ACCT_LINES
WHERE
Budget_Version_ID = p_Prev_Budget_Version_ID
AND Start_Date = p_Start_Date
AND Code_Combination_ID = p_CCID;
INSERT INTO PA_BUDGET_ACCT_LINES (
Budget_Acct_Line_ID,
Budget_Version_ID,
GL_Period_Name,
Start_Date,
End_Date,
Code_Combination_ID,
Prev_Ver_Budget_Amount,
Prev_ver_Available_Amount,
Curr_Ver_Budget_Amount,
Curr_ver_Available_Amount,
Accounted_Amount,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY
)
VALUES (
PA_BUDGET_ACCT_LINES_S.nextval,
p_Budget_Version_ID,
p_Period_Name,
p_Start_Date,
p_End_Date,
p_CCID,
l_Prev_Ver_Budget_Amount,
l_Prev_Ver_Available_Amount,
p_Amount,
-- p_Amount - (l_Prev_Ver_Budget_Amount-l_Prev_Ver_Available_Amount),
p_Amount, -- Bug # 2008368 Curr_ver_Available_Amount
p_Amount - l_Prev_Ver_Available_Amount, -- Accounted Amount
sysdate,
-1,
-1,
sysdate,
-1
);
PROCEDURE Insert_Into_Budget_Lines (
P_Budget_Version_ID IN PA_Budget_Versions.Budget_Version_ID%TYPE,
P_Project_ID IN PA_projects_All.project_Id%TYPE,
P_Project_Start_Date IN DATE,
P_Project_End_Date IN DATE,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
-- Local Parameters
l_Return_Status VARCHAR2(50);
SELECT
RA1.Resource_Assignment_ID RESOURCE_ASSIGNMENT_ID,
nvl(RA1.Task_ID, -1) TASK_ID,
RA1.Resource_List_Member_ID RESOURCE_LIST_MEMBER_ID,
GLPRD.Period_Name PERIOD_NAME,
GLPRD.Start_Date START_DATE,
GLPRD.End_Date END_DATE
FROM
GL_Period_Statuses GLPRD,
PA_Implementations IMP,
PA_Resource_Assignments RA1
WHERE
GLPRD.Application_ID = PA_Period_Process_Pkg.Application_ID -- 101
AND GLPRD.Set_Of_Books_ID = IMP.Set_Of_Books_ID
AND GLPRD.Closing_status IN ('O','F')
AND ( l_GL_Start_Date BETWEEN GLPRD.Start_Date AND GLPRD.End_Date OR
l_GL_End_Date BETWEEN GLPRD.Start_Date AND GLPRD.End_Date OR
GLPRD.Start_Date BETWEEN l_GL_Start_Date AND l_GL_End_Date )
AND GLPRD.Adjustment_Period_Flag <> 'Y' -- Bug #1891179
AND RA1.Project_ID = P_Project_ID
AND RA1.Budget_Version_ID = p_Budget_Version_ID
MINUS
SELECT
BL2.Resource_Assignment_ID RESOURCE_ASSIGNMENT_ID,
nvl(RA2.Task_ID, -1) TASK_ID,
RA2.Resource_List_Member_ID RESOURCE_LIST_MEMBER_ID,
BL2.Period_Name PERIOD_NAME,
BL2.Start_Date START_DATE,
BL2.End_Date END_DATE
FROM
PA_Budget_Lines BL2,
PA_Resource_Assignments RA2,
GL_Period_Statuses GLPRD2,
PA_Implementations IMP2
WHERE
RA2.Resource_Assignment_ID = BL2.Resource_Assignment_ID
AND RA2.Project_ID = P_Project_ID
AND RA2.Budget_Version_ID = p_Budget_Version_ID
AND GLPRD2.Period_Name = BL2.Period_Name
AND GLPRD2.Application_ID = PA_Period_Process_Pkg.Application_ID -- 101
AND GLPRD2.Closing_status IN ('O','F')
AND GLPRD2.Adjustment_Period_Flag <> 'Y' -- Bug #1891179
AND GLPRD2.Set_Of_Books_ID = IMP2.Set_Of_Books_ID ;
PA_FCK_UTIL.debug_msg(' Entering Insert_Into_Budget_Lines ');
SELECT PBCO.boundary_code
INTO l_boundary_code
FROM PA_BUDGETARY_CONTROL_OPTIONS PBCO,
PA_BUDGET_VERSIONS PBV
WHERE PBV.Project_Id = p_project_id
AND PBV.Budget_version_id = p_Budget_version_id
AND PBCO.Project_id = PBV.Project_id
AND PBCO.Budget_Type_Code = PBV.Budget_Type_Code;
SELECT MAX(BL.End_Date)
INTO l_BL_Max_Date
FROM PA_Budget_Lines BL
WHERE BL.Budget_Version_ID = p_Budget_Version_ID;
Select set_of_books_id into l_set_of_books_id from pa_implementations;
SELECT gps.year_start_date,
ADD_MONTHS (gps.year_start_date, 12 ) - 1
INTO t_GL_Start_Date,
t_GL_End_Date
FROM gl_period_statuses gps
WHERE gps.application_id = PA_Period_Process_Pkg.Application_ID
AND gps.set_of_books_id = l_set_of_books_id
AND l_BL_Max_Date BETWEEN gps.start_date AND gps.end_date
AND gps.adjustment_period_flag = 'N';
SELECT
MIN(BL.start_Date), -- decode(p_Start_Date, NULL, MIN(BL.start_Date), p_Start_Date)
MAX(BL.End_Date) -- decode(p_End_Date, NULL, MAX(BL.End_Date), p_End_Date)
INTO
t_GL_Start_Date,
t_GL_End_Date
FROM
PA_Budget_Lines BL,
PA_Resource_Assignments RA
WHERE
RA.Budget_Version_ID = p_Budget_Version_ID
AND RA.Resource_Assignment_ID = BL.Resource_Assignment_ID ;
PA_FCK_UTIL.debug_msg(' selected max bl start and end date ');
PA_BUDGET_LINES_V_PKG.Insert_Row(
X_ROWID => l_rowid,
X_Resource_Assignment_Id => l_Resource_Assignment_ID,
X_Budget_Version_Id => P_Budget_Version_ID,
X_Project_Id => P_Project_ID,
X_Task_Id => l_Task_ID,
X_Resource_List_Member_Id => GlPrds.Resource_List_Member_Id,
X_Description => NULL,
X_Start_Date => GlPrds.Start_Date,
X_End_Date => GlPrds.End_Date,
X_Period_Name => GlPrds.Period_Name,
X_Quantity => l_quantity,
X_Unit_Of_Measure => NULL,
X_Track_As_Labor_Flag => NULL,
X_Raw_Cost => l_raw_Cost,
X_Burdened_Cost => l_Burdened_Cost,
X_Revenue => l_revenue,
X_Change_Reason_Code => NULL,
X_Last_Update_Date => SYSDATE,
X_Last_Updated_By => -1,
X_Creation_Date => SYSDATE,
X_Created_By => -1,
X_Last_Update_Login => -1,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
X_Calling_Process => 'PR',
X_Pm_Product_Code => NULL,
X_Pm_Budget_Line_Reference => NULL,
X_raw_Cost_source => 'M',
X_Burdened_Cost_source => 'M',
X_quantity_source => 'M',
X_revenue_source => 'M',
x_standard_Bill_rate => NULL,
x_Average_Bill_rate => NULL,
x_Average_Cost_rate => NULL,
x_project_Assignment_Id => -1,
x_plan_Error_Code => NULL,
x_total_plan_revenue => NULL,
x_total_plan_raw_Cost => NULL,
x_total_plan_Burdened_Cost => NULL,
x_total_plan_quantity => NULL,
x_Average_Discount_percentage => NULL,
x_Cost_rejection_Code => NULL,
x_Burden_rejection_Code => NULL,
x_revenue_rejection_Code => NULL,
x_other_rejection_Code => NULL);
PA_FCK_UTIL.debug_msg(' after insert res_id '||to_char(l_Resource_Assignment_ID)||' start date '||to_char(GlPrds.Start_Date));
PA_FCK_UTIL.debug_msg(' ERROR INSERT_INTO_BUDGET_LINES '||SQLERRM);
P_Procedure_Name => 'Insert_Into_Budget_Lines');
END Insert_Into_Budget_Lines ; /* End API Insert_Into_Budget_Lines */
SELECT
Decode(Current_NPW_Flag, 'Y', NPW_Number, Employee_Number),
Decode(Current_NPW_Flag, 'Y', 'CWK', 'EMP') Person_Type -- FP_M changes
INTO
X_Employee_Number,
X_Person_Type -- FP_M changes
FROM
PER_All_People_F
WHERE
Person_ID = P_Person_ID
AND (Current_Employee_Flag = 'Y' OR Current_NPW_Flag = 'Y'); -- FP_M changes
SELECT
JOB.Name JOB_NAME,
JOB.Job_Group_ID JOB_GROUP_ID,
JBGRP.Internal_Name JOB_GROUP_NAME
INTO
X_Job_Name,
X_Job_Group_ID,
X_Job_Group_Name
FROM
PER_Jobs JOB,
PER_Job_Groups JBGRP
WHERE
JOB.job_Id = p_Job_Id
AND JOB.job_Group_Id = JBGRP.job_Group_Id ;
SELECT
ORG.name ORG_NAME,
ORG.Type ORG_TYPE
INTO
x_organization_name,
x_organization_type
FROM
HR_All_organization_units ORG
WHERE
ORG.organization_Id = P_Organization_ID;
SELECT
SUP.Vendor_Name SUPPLIER_NAME
INTO
X_Supplier_Name
FROM
PO_Vendors SUP
WHERE
SUP.Vendor_ID = P_Supplier_ID;
SELECT
TOP_TASK.task_number TASK_NUMBER,
ORG.Organization_Id TASK_ORGANIZATION_ID,
ORG.Name TASK_ORGANIZATION_NAME,
TOP_TASK.Service_Type_Code TASK_SERVICE_TYPE
INTO
X_Top_Task_Number,
X_Task_Organization_ID,
X_Task_Organization_Name,
X_Task_Service_Type
FROM
HR_All_Organization_Units ORG,
PA_Tasks TOP_TASK
WHERE
TOP_TASK.task_Id = p_top_task_Id
AND TOP_TASK.Top_Task_ID = TOP_TASK.Task_ID
AND TOP_TASK.Project_ID = p_project_Id
AND ORG.organization_Id = TOP_TASK.carrying_out_organization_Id;
SELECT
TASK.task_number TASK_NUMBER
INTO
x_task_number
FROM
PA_Tasks TASK
WHERE
TASK.task_Id = p_low_task_Id;