[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MIN(sp.start_date)
INTO x_start_date
FROM gl_period_statuses sp, pa_implementations imp
WHERE
sp.period_name = NVL(x_from_period_name,sp.period_name)
AND sp.set_of_books_id = imp.set_of_books_id
AND sp.application_id = 101
AND sp.adjustment_period_flag = 'N';
SELECT MAX(ep.end_date)
INTO x_end_date
FROM gl_period_statuses ep, pa_implementations imp
WHERE
ep.period_name = NVL(x_to_period_name,ep.period_name)
AND ep.set_of_books_id = imp.set_of_books_id
AND ep.application_id = 101
AND ep.adjustment_period_flag = 'N';
SELECT MIN(sp.start_date)
INTO x_start_date
FROM gl_period_statuses sp, pa_implementations imp
WHERE
sp.period_name =x_from_period_name
AND sp.set_of_books_id = imp.set_of_books_id
AND sp.application_id = pa_period_process_pkg.application_id
AND sp.adjustment_period_flag = 'N';
SELECT MIN(sp.start_date)
INTO x_start_date
FROM gl_period_statuses sp, pa_implementations imp
WHERE sp.set_of_books_id = imp.set_of_books_id
AND sp.application_id = pa_period_process_pkg.application_id
AND sp.adjustment_period_flag = 'N';
SELECT MAX(ep.end_date)
INTO x_end_date
FROM gl_period_statuses ep, pa_implementations imp
WHERE
ep.period_name = x_to_period_name
AND ep.set_of_books_id = imp.set_of_books_id
AND ep.application_id = pa_period_process_pkg.application_id
AND ep.adjustment_period_flag = 'N';
SELECT MAX(ep.end_date)
INTO x_end_date
FROM gl_period_statuses ep, pa_implementations imp
WHERE ep.set_of_books_id = imp.set_of_books_id
AND ep.application_id = pa_period_process_pkg.application_id
AND ep.adjustment_period_flag = 'N';
SELECT MIN(sp.start_date)
INTO x_start_date
FROM pa_periods sp
WHERE sp.period_name = x_from_period_name;
SELECT MIN(sp.start_date)
INTO x_start_date
FROM pa_periods sp;
SELECT MAX(ep.end_date)
INTO x_end_date
FROM pa_periods ep
WHERE ep.period_name = x_to_period_name;
SELECT MAX(ep.end_date)
INTO x_end_date
FROM pa_periods ep;
SELECT MIN(sp.start_date)
INTO x_start_date
FROM pa_periods sp
WHERE
sp.period_name = NVL(x_from_period_name,sp.period_name);
SELECT MAX(ep.end_date)
INTO x_end_date
FROM pa_periods ep
WHERE
ep.period_name = NVL(x_to_period_name,ep.period_name);
SELECT
tot_revenue,
tot_raw_cost,
tot_burdened_cost,
tot_quantity,
tot_labor_hours,
tot_billable_raw_cost,
tot_billable_burdened_cost,
tot_billable_quantity,
tot_billable_labor_hours,
tot_cmt_raw_cost,
tot_cmt_burdened_cost,
unit_of_measure
FROM
pa_txn_accum pta /*, commented for bug 4390421
pa_periods_all pp,
pa_implementations imp */
WHERE
x_period_type = 'P'
AND pta.project_id = x_project_id
AND (
(x_task_id IS NULL) --- project level numbers
OR
(pta.task_id IN
(SELECT
task_id
FROM
pa_tasks
CONNECT BY PRIOR task_id = parent_task_id
START WITH task_id = x_task_id
)
)
)
AND EXISTS
( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
WHERE
PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
)
AND pta.pa_period = x_from_period_name ;
SELECT
tot_revenue,
tot_raw_cost,
tot_burdened_cost,
tot_quantity,
tot_labor_hours,
tot_billable_raw_cost,
tot_billable_burdened_cost,
tot_billable_quantity,
tot_billable_labor_hours,
tot_cmt_raw_cost,
tot_cmt_burdened_cost,
unit_of_measure
FROM
/* commented for bug 4390421
pa_implementations imp,
gl_period_statuses glp,
pa_periods_all pp, Commented for bug 2922974 Added for bug 1631100 performance tuning */
pa_txn_accum pta
WHERE
x_period_type = 'G'
AND pta.project_id = x_project_id
AND (
(x_task_id IS NULL) --- project level numbers
OR
(pta.task_id IN
(SELECT
task_id
FROM
pa_tasks
CONNECT BY PRIOR task_id = parent_task_id
START WITH task_id = x_task_id
)
)
)
AND EXISTS
( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
WHERE
PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
)
AND pta.gl_period = x_from_period_name ; -- Added for bug 4390421
SELECT
PTA.TOT_REVENUE,
PTA.TOT_RAW_COST,
PTA.TOT_BURDENED_COST,
PTA.TOT_QUANTITY,
PTA.TOT_LABOR_HOURS,
PTA.TOT_BILLABLE_RAW_COST,
PTA.TOT_BILLABLE_BURDENED_COST,
PTA.TOT_BILLABLE_QUANTITY,
PTA.TOT_BILLABLE_LABOR_HOURS,
PTA.TOT_CMT_RAW_COST,
PTA.TOT_CMT_BURDENED_COST,
PTA.UNIT_OF_MEASURE
FROM
PA_TXN_ACCUM PTA /*, Commented for bug 4390421
pa_periods_all pp,
pa_implementations imp */
WHERE PTA.PROJECT_ID = X_PROJECT_ID
AND (
(x_task_id IS NULL) --- project level numbers
OR
(PTA.TASK_ID IN
(SELECT
task_id
FROM
pa_tasks
CONNECT BY PRIOR task_id = parent_task_id
START WITH task_id = x_task_id
)
)
)
AND EXISTS
( SELECT 'Yes'
FROM PA_RESOURCE_ACCUM_DETAILS PRAD
WHERE PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
AND PRAD.RESOURCE_LIST_MEMBER_ID IN
-- Modified for bug 4390421
( -- Fetch both 2nd level and group level resource list member
SELECT PRLM.RESOURCE_LIST_MEMBER_ID
FROM PA_RESOURCE_LIST_MEMBERS PRLM
WHERE (prlm.resource_list_member_id = X_RESOURCE_LIST_MEMBER_ID
or
PRLM.PARENT_MEMBER_ID = X_RESOURCE_LIST_MEMBER_ID )
/* Commented for bug 4390421
(
SELECT -- 2nd level resource list members
PRLM.RESOURCE_LIST_MEMBER_ID
FROM
PA_RESOURCE_LIST_MEMBERS PRLM
WHERE
PRLM.PARENT_MEMBER_ID = X_RESOURCE_LIST_MEMBER_ID
UNION
SELECT -- Group level Resource list member
X_RESOURCE_LIST_MEMBER_ID
FROM
SYS.DUAL */
)
)
AND EXISTS
( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
WHERE
PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
)
AND x_period_type = 'P'
AND pta.pa_period = x_from_period_name; -- Added for bug 4390421
SELECT
PTA.TOT_REVENUE,
PTA.TOT_RAW_COST,
PTA.TOT_BURDENED_COST,
PTA.TOT_QUANTITY,
PTA.TOT_LABOR_HOURS,
PTA.TOT_BILLABLE_RAW_COST,
PTA.TOT_BILLABLE_BURDENED_COST,
PTA.TOT_BILLABLE_QUANTITY,
PTA.TOT_BILLABLE_LABOR_HOURS,
PTA.TOT_CMT_RAW_COST,
PTA.TOT_CMT_BURDENED_COST,
PTA.UNIT_OF_MEASURE
FROM
/* Commented for bug 4390421
pa_implementations imp,
gl_period_statuses glp, */
PA_TXN_ACCUM PTA
/* PA_PERIODS_ALL PP commented for bug 2922974 */
WHERE PTA.PROJECT_ID = X_PROJECT_ID
AND (
(x_task_id IS NULL) --- project level numbers
OR
(PTA.TASK_ID IN
(SELECT
task_id
FROM
pa_tasks
CONNECT BY PRIOR task_id = parent_task_id
START WITH task_id = x_task_id
)
)
)
AND EXISTS (SELECT 'Yes'
FROM PA_RESOURCE_ACCUM_DETAILS PRAD
WHERE PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
AND PRAD.RESOURCE_LIST_MEMBER_ID IN
-- Modified for bug 4390421
( -- Fetch both 2nd level and group level resource list member
SELECT PRLM.RESOURCE_LIST_MEMBER_ID
FROM PA_RESOURCE_LIST_MEMBERS PRLM
WHERE (prlm.resource_list_member_id = X_RESOURCE_LIST_MEMBER_ID
or
PRLM.PARENT_MEMBER_ID = X_RESOURCE_LIST_MEMBER_ID )
/*
(
SELECT -- 2nd level resource list members
PRLM.RESOURCE_LIST_MEMBER_ID
FROM
PA_RESOURCE_LIST_MEMBERS PRLM
WHERE
PRLM.PARENT_MEMBER_ID = X_RESOURCE_LIST_MEMBER_ID
UNION
SELECT -- Group level Resource list member
X_RESOURCE_LIST_MEMBER_ID
FROM
SYS.DUAL */
)
)
AND EXISTS
( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
WHERE
PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
)
/* AND pp.gl_period_name = glp.period_name commented for bug 2922974 Added for bug 1631100 performance tuning
AND pp.period_name = pta.pa_period commented for bug 2922974 Added for bug 1631100 performance tuning
AND nvl(pp.org_id, -1) = nvl(imp.org_id, -1) commented for bug 2922974 Added for bug 1631100 performance tuning */
AND x_period_type = 'G'
AND pta.gl_period = x_from_period_name;
SELECT
bpv.base_raw_cost,
bpv.base_burdened_cost,
bpv.base_revenue,
bpv.base_quantity,
bpv.base_labor_quantity,
bpv.unit_of_measure,
bpv.orig_raw_cost,
bpv.orig_burdened_cost,
bpv.orig_revenue,
bpv.orig_quantity,
bpv.orig_labor_quantity
FROM
pa_budget_by_pa_period_v bpv,
pa_periods pp
WHERE
bpv.project_id = x_project_id
AND (
(x_task_id IS NULL) --- project level numbers
OR
(bpv.task_id IN
(SELECT
t.task_id
FROM
pa_tasks t
CONNECT BY PRIOR t.task_id = t.parent_task_id
START WITH t.task_id = x_task_id
)
)
)
AND x_period_type = 'P'
AND pp.period_name = bpv.pa_period
AND pp.start_date BETWEEN
NVL(x_start_date,pp.start_date) AND NVL(x_end_date,pp.end_date)
AND bpv.budget_type_code = NVL(x_budget_type_code,bpv.budget_type_code)
UNION ALL
SELECT
bpv.base_raw_cost,
bpv.base_burdened_cost,
bpv.base_revenue,
bpv.base_quantity,
bpv.base_labor_quantity,
bpv.unit_of_measure,
bpv.orig_raw_cost,
bpv.orig_burdened_cost,
bpv.orig_revenue,
bpv.orig_quantity,
bpv.orig_labor_quantity
FROM
pa_budget_by_pa_period_v bpv,
gl_period_statuses glp,
pa_implementations imp
WHERE
bpv.project_id = x_project_id
AND (
(x_task_id IS NULL) --- project level numbers
OR
(bpv.task_id IN
(SELECT
t.task_id
FROM
pa_tasks t
CONNECT BY PRIOR t.task_id = t.parent_task_id
START WITH t.task_id = x_task_id
)
)
)
AND x_period_type = 'G'
AND glp.period_name = bpv.gl_period_name
AND glp.set_of_books_id = imp.set_of_books_id
AND glp.application_id = pa_period_process_pkg.application_id
AND glp.adjustment_period_flag = 'N'
AND glp.start_date BETWEEN
NVL(x_start_date,glp.start_date) AND NVL(x_end_date,glp.end_date)
AND bpv.budget_type_code = NVL(x_budget_type_code,bpv.budget_type_code) ;
SELECT
bpv.base_raw_cost,
bpv.base_burdened_cost,
bpv.base_revenue,
bpv.base_quantity,
bpv.base_labor_quantity,
bpv.unit_of_measure,
bpv.orig_raw_cost,
bpv.orig_burdened_cost,
bpv.orig_revenue,
bpv.orig_quantity,
bpv.orig_labor_quantity
FROM
pa_budget_by_pa_period_v bpv,
pa_periods pp
WHERE
bpv.project_id = x_project_id
AND (
(x_task_id IS NULL) --- project level numbers
OR
(bpv.task_id IN
(SELECT
t.task_id
FROM
pa_tasks t
CONNECT BY PRIOR t.task_id = t.parent_task_id
START WITH t.task_id = x_task_id)
)
)
AND bpv.resource_list_member_id IN
(
SELECT -- 2nd level resource list members
rlm.resource_list_member_id
FROM
pa_resource_list_members rlm
WHERE
rlm.parent_member_id = x_resource_list_member_id
UNION
SELECT -- Group level Resource list member
x_resource_list_member_id
FROM
SYS.DUAL
)
AND x_period_type = 'P'
AND pp.period_name = bpv.pa_period
AND pp.start_date BETWEEN
NVL(x_start_date,pp.start_date) AND NVL(x_end_date,pp.end_date)
AND bpv.budget_type_code = NVL(x_budget_type_code,bpv.budget_type_code)
UNION ALL
SELECT
bpv.base_raw_cost,
bpv.base_burdened_cost,
bpv.base_revenue,
bpv.base_quantity,
bpv.base_labor_quantity,
bpv.unit_of_measure,
bpv.orig_raw_cost,
bpv.orig_burdened_cost,
bpv.orig_revenue,
bpv.orig_quantity,
bpv.orig_labor_quantity
FROM
pa_budget_by_pa_period_v bpv,
gl_period_statuses glp,
pa_implementations imp
WHERE
bpv.project_id = x_project_id
AND (
(x_task_id IS NULL) --- project level numbers
OR
(bpv.task_id IN
(SELECT
t.task_id
FROM
pa_tasks t
CONNECT BY PRIOR t.task_id = t.parent_task_id
START WITH t.task_id = x_task_id)
)
)
AND bpv.resource_list_member_id IN
(
SELECT -- 2nd level resource list members
rlm.resource_list_member_id
FROM
pa_resource_list_members rlm
WHERE
rlm.parent_member_id = x_resource_list_member_id
UNION
SELECT -- Group level Resource list member
x_resource_list_member_id
FROM
SYS.DUAL
)
AND x_period_type = 'G'
AND glp.period_name = bpv.gl_period_name
AND glp.set_of_books_id = imp.set_of_books_id
AND glp.application_id = pa_period_process_pkg.application_id
AND glp.adjustment_period_flag = 'N'
AND glp.start_date BETWEEN
NVL(x_start_date,glp.start_date) AND NVL(x_end_date,glp.end_date)
AND bpv.budget_type_code = NVL(x_budget_type_code,bpv.budget_type_code) ;