[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
pa_commitment_txns_s.NEXTVAL
INTO cmt_line_id
FROM
SYS.DUAL;
PROCEDURE update_resource_flag
(x_start_project_id IN NUMBER,
x_end_project_id IN NUMBER,
x_start_pa_date IN DATE,
x_end_pa_date IN DATE,
x_err_stage IN OUT NOCOPY VARCHAR2,
x_err_code IN OUT NOCOPY NUMBER)
IS
tot_recs_processed NUMBER;
pa_debug.debug('update_resource_flag: ' || x_err_stage);
UPDATE pa_cost_distribution_lines_all SET
resource_accumulated_flag = 'N'
WHERE project_id = x_start_project_id AND
(line_type = 'R' OR line_type = 'I') AND
resource_accumulated_flag <> 'N' AND
TRUNC(pa_date) BETWEEN x_start_pa_date AND x_end_pa_date AND
ROWNUM <= pa_proj_accum_main.x_commit_size;
pa_debug.debug('update_resource_flag: ' || 'Number of Records Commited cumulatively = '|| TO_CHAR(tot_recs_processed));
UPDATE pa_draft_revenues SET
resource_accumulated_flag = 'S'
WHERE project_id = x_start_project_id AND
released_date IS NOT NULL AND
resource_accumulated_flag <> 'S' AND
TRUNC(pa_date) BETWEEN x_start_pa_date AND x_end_pa_date AND
ROWNUM <= pa_proj_accum_main.x_commit_size;
pa_debug.debug('update_resource_flag: ' || 'Number of Records Commited cumulatively = '|| TO_CHAR(tot_recs_processed));
END update_resource_flag;
delete_act_txn_accum_details
( x_start_project_id,
x_end_project_id,
x_start_pa_date,
x_end_pa_date,
x_system_linkage_function,
x_err_stage,
x_err_code);
delete_rev_txn_accum_details
( x_start_project_id,
x_end_project_id,
x_start_pa_date,
x_end_pa_date,
x_err_stage,
x_err_code);
delete_cmt_txn_accum_details
( x_start_project_id,
x_end_project_id,
x_start_pa_date,
x_end_pa_date,
x_system_linkage_function,
x_err_stage,
x_err_code);
delete_cmt_txns
( x_start_project_id,
x_end_project_id,
x_start_pa_date,
x_end_pa_date,
x_system_linkage_function,
x_err_stage,
x_err_code);
delete_act_txn_accum_details
( x_start_project_id,
x_end_project_id,
x_start_pa_date,
x_end_pa_date,
x_system_linkage_function,
x_err_stage,
x_err_code);
delete_rev_txn_accum_details
( x_start_project_id,
x_end_project_id,
x_start_pa_date,
x_end_pa_date,
x_err_stage,
x_err_code);
delete_cmt_txn_accum_details
( x_start_project_id,
x_end_project_id,
x_start_pa_date,
x_end_pa_date,
x_system_linkage_function,
x_err_stage,
x_err_code);
delete_cmt_txns
( x_start_project_id,
x_end_project_id,
x_start_pa_date,
x_end_pa_date,
x_system_linkage_function,
x_err_stage,
x_err_code);
UPDATE
pa_txn_accum pta
SET
pta.tot_raw_cost = NULL,
pta.tot_burdened_cost = NULL,
pta.tot_quantity = NULL,
pta.tot_labor_hours = NULL,
pta.tot_billable_raw_cost = NULL,
pta.tot_billable_burdened_cost = NULL,
pta.tot_billable_quantity = NULL,
pta.tot_billable_labor_hours = NULL,
pta.i_tot_raw_cost = NULL,
pta.i_tot_burdened_cost = NULL,
pta.i_tot_quantity = NULL,
pta.i_tot_labor_hours = NULL,
pta.i_tot_billable_raw_cost = NULL,
pta.i_tot_billable_burdened_cost = NULL,
pta.i_tot_billable_quantity = NULL,
pta.i_tot_billable_labor_hours = NULL,
pta.unit_of_measure = NULL,
pta.actual_cost_rollup_flag = 'N',
pta.last_updated_by = x_last_updated_by,
pta.last_update_date = SYSDATE,
pta.request_id = x_request_id,
pta.program_application_id = x_program_application_id,
pta.program_id = x_program_id,
pta.program_update_date = SYSDATE
WHERE
pta.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id - Commented for bug 3736097
AND pta.system_linkage_function =
NVL(x_system_linkage_function,pta.system_linkage_function)
AND EXISTS
( SELECT 'Yes'
FROM pa_txn_accum_details ptad
WHERE pta.txn_accum_id = ptad.txn_accum_id
AND ptad.line_type = 'C'
)
AND EXISTS
( SELECT 'Yes'
FROM pa_periods
WHERE period_name = pta.pa_period
AND start_date >= x_start_pa_date
AND end_date <= x_end_pa_date
);
pa_debug.debug('refresh_act_txn_accum: ' || 'Records Updated = '||TO_CHAR(SQL%ROWCOUNT));
UPDATE
pa_txn_accum pta
SET
pta.tot_revenue = NULL,
pta.i_tot_revenue = NULL,
pta.revenue_rollup_flag = 'N',
pta.last_updated_by = x_last_updated_by,
pta.last_update_date = SYSDATE,
pta.request_id = x_request_id,
pta.program_application_id = x_program_application_id,
pta.program_id = x_program_id,
pta.program_update_date = SYSDATE
WHERE
pta.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id -- Commented for bug 3736097
AND EXISTS
( SELECT 'Yes'
FROM pa_txn_accum_details ptad
WHERE pta.txn_accum_id = ptad.txn_accum_id
AND ptad.line_type IN ('R','E')
)
AND EXISTS
( SELECT 'Yes'
FROM pa_periods
WHERE period_name = pta.pa_period
AND start_date >= x_start_pa_date
AND end_date <= x_end_pa_date
);
pa_debug.debug('refresh_rev_txn_accum: ' || 'Records Updated = '||TO_CHAR(SQL%ROWCOUNT));
UPDATE /*+ leading(PTA) */ /* 10144700 */
pa_txn_accum pta
SET
pta.tot_cmt_raw_cost = NULL,
pta.tot_cmt_burdened_cost = NULL,
pta.cmt_rollup_flag = 'N',
pta.last_updated_by = x_last_updated_by,
pta.last_update_date = SYSDATE,
pta.request_id = x_request_id,
pta.program_application_id = x_program_application_id,
pta.program_id = x_program_id,
pta.program_update_date = SYSDATE
WHERE
pta.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id Commented for bug 3736097
-- System_linkage_function can be Null for commitments
AND NVL(pta.system_linkage_function,'X') =
NVL(NVL(x_system_linkage_function,pta.system_linkage_function),'X')
AND EXISTS
( SELECT /*+ NO_UNNEST push_subq */ 'Yes' /* 10144700 */
FROM pa_txn_accum_details ptad
WHERE pta.txn_accum_id = ptad.txn_accum_id
AND ptad.line_type = 'M'
)
AND EXISTS
( SELECT 'Yes'
FROM pa_periods
WHERE period_name = pta.pa_period
AND start_date >= x_start_pa_date
AND end_date <= x_end_pa_date
);
pa_debug.debug('refresh_cmt_txn_accum: ' || 'Records Updated = '||TO_CHAR(SQL%ROWCOUNT));
PROCEDURE update_act_txn_accum
( x_start_project_id IN NUMBER,
x_end_project_id IN NUMBER,
x_err_stage IN OUT NOCOPY VARCHAR2,
x_err_code IN OUT NOCOPY NUMBER)
IS
P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
pa_debug.debug('update_act_txn_accum: ' || x_err_stage);
UPDATE
pa_txn_accum pta
SET
pta.i_tot_raw_cost = DECODE(raw_cost_flag,'Y',
(NVL(i_tot_raw_cost, 0) + NVL(tot_raw_cost,0)), NULL),
pta.i_tot_burdened_cost = DECODE(burdened_cost_flag,'Y',
(NVL(i_tot_burdened_cost, 0) + NVL(tot_burdened_cost,0)),
NULL),
pta.i_tot_quantity = DECODE(quantity_flag,'Y',
(NVL(i_tot_quantity, 0) + NVL(tot_quantity,0)), NULL),
pta.i_tot_labor_hours = DECODE(labor_hours_flag,'Y',
(NVL(i_tot_labor_hours,0) +
DECODE(pta.system_linkage_function,
'OT', NVL(tot_quantity,0),
'ST', NVL(tot_quantity,0), 0)),NULL),
pta.i_tot_billable_raw_cost = DECODE(billable_raw_cost_flag,'Y',
(NVL(i_tot_billable_raw_cost, 0) +
NVL(tot_billable_raw_cost,0)),NULL),
pta.i_tot_billable_burdened_cost = DECODE(billable_burdened_cost_flag,'Y',
(NVL(i_tot_billable_burdened_cost, 0) +
NVL(tot_billable_burdened_cost,0)),NULL),
pta.i_tot_billable_quantity = DECODE(billable_quantity_flag,'Y',
(NVL(i_tot_billable_quantity, 0) +
NVL(tot_billable_quantity,0)),NULL),
pta.i_tot_billable_labor_hours = DECODE(billable_labor_hours_flag,'Y',
(NVL(i_tot_billable_labor_hours,0) +
DECODE(pta.system_linkage_function,
'OT', NVL(tot_billable_quantity,0),
'ST', NVL(tot_billable_quantity,0), 0)),NULL),
pta.i_tot_revenue = DECODE(revenue_flag,'Y',
(NVL(i_tot_revenue, 0) + NVL(tot_revenue,0)),NULL),
pta.tot_raw_cost = NULL,
pta.tot_burdened_cost = NULL,
pta.tot_quantity = NULL,
pta.tot_labor_hours = NULL,
pta.tot_billable_raw_cost = NULL,
pta.tot_billable_burdened_cost = NULL,
pta.tot_billable_quantity = NULL,
pta.tot_billable_labor_hours = NULL,
pta.tot_revenue = NULL,
pta.actual_cost_rollup_flag = 'Y',
pta.revenue_rollup_flag = 'Y',
pta.last_update_date = SYSDATE,
pta.last_updated_by = x_last_updated_by,
pta.request_Id = x_request_id,
pta.program_application_id = x_program_application_id,
pta.program_id = x_program_id,
pta.program_update_Date = SYSDATE
WHERE
pta.project_id BETWEEN x_start_project_id AND x_end_project_id
AND pta.request_id <> x_request_id
AND EXISTS
( SELECT 'Yes'
FROM pa_txn_accum_details ptad
WHERE pta.txn_accum_id = ptad.txn_accum_id
AND ptad.line_type IN ('C','R','E')
);
pa_debug.debug('update_act_txn_accum: ' || 'Records Updated = '||TO_CHAR(SQL%ROWCOUNT));
END update_act_txn_accum;
PROCEDURE delete_act_txn_accum_details
( x_start_project_id IN NUMBER,
x_end_project_id IN NUMBER,
x_start_pa_date IN DATE,
x_end_pa_date IN DATE,
x_system_linkage_function IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
x_err_stage IN OUT NOCOPY VARCHAR2,
x_err_code IN OUT NOCOPY NUMBER)
IS
P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
x_err_stage := 'Deleteing Actual transaction accum details';
pa_debug.debug('delete_act_txn_accum_details: ' || x_err_stage);
DELETE pa_txn_accum_details ptad
WHERE txn_accum_id IN
(SELECT txn_accum_id FROM pa_txn_accum pta
WHERE
pta.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id -- Commented for bug 3736097
AND pta.system_linkage_function =
NVL(x_system_linkage_function,pta.system_linkage_function)
AND EXISTS
( SELECT 'Yes'
FROM pa_periods
WHERE period_name = pta.pa_period
AND start_date >= x_start_pa_date
AND end_date <= x_end_pa_date
)
)
AND ptad.line_type = 'C'
AND ROWNUM <= pa_proj_accum_main.x_commit_size;
pa_debug.debug('delete_act_txn_accum_details: ' || 'Number of Records Commited cumulatively = '|| TO_CHAR(tot_recs_processed));
pa_debug.debug('delete_act_txn_accum_details: ' || 'Records Deleted = '||TO_CHAR(tot_recs_processed));
END delete_act_txn_accum_details;
PROCEDURE delete_rev_txn_accum_details
( x_start_project_id IN NUMBER,
x_end_project_id IN NUMBER,
x_start_pa_date IN DATE,
x_end_pa_date IN DATE,
x_err_stage IN OUT NOCOPY VARCHAR2,
x_err_code IN OUT NOCOPY NUMBER)
IS
P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
x_err_stage := 'Deleteing Revenue transaction accum details';
pa_debug.debug('delete_rev_txn_accum_details: ' || x_err_stage);
DELETE pa_txn_accum_details ptad
WHERE txn_accum_id IN
(SELECT txn_accum_id FROM pa_txn_accum pta
WHERE
pta.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id Commented for bug 3736097
AND EXISTS
( SELECT 'Yes'
FROM pa_periods
WHERE period_name = pta.pa_period
AND start_date >= x_start_pa_date
AND end_date <= x_end_pa_date
)
)
AND ptad.line_type IN ('R','E')
AND ROWNUM <= pa_proj_accum_main.x_commit_size;
pa_debug.debug('delete_rev_txn_accum_details: ' || 'Number of Records Commited cumulatively = '|| TO_CHAR(tot_recs_processed));
pa_debug.debug('delete_rev_txn_accum_details: ' || 'Records Deleted = '||TO_CHAR(tot_recs_processed));
END delete_rev_txn_accum_details;
PROCEDURE delete_cmt_txn_accum_details
( x_start_project_id IN NUMBER,
x_end_project_id IN NUMBER,
x_start_pa_date IN DATE,
x_end_pa_date IN DATE,
x_system_linkage_function IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
x_err_stage IN OUT NOCOPY VARCHAR2,
x_err_code IN OUT NOCOPY NUMBER)
IS
P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
x_err_stage := 'Deleteing commitments transaction accum details';
pa_debug.debug('delete_cmt_txn_accum_details: ' || x_err_stage);
DELETE pa_txn_accum_details ptad
WHERE txn_accum_id IN
(SELECT txn_accum_id FROM pa_txn_accum pta
WHERE
pta.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id Commented out for bug 3736097
AND NVL(pta.system_linkage_function,'X') =
NVL(NVL(x_system_linkage_function,pta.system_linkage_function),'X')
AND EXISTS
( SELECT 'Yes'
FROM pa_periods
WHERE period_name = pta.pa_period
AND start_date >= x_start_pa_date
AND end_date <= x_end_pa_date
)
)
AND ptad.line_type = 'M'
AND ROWNUM <= pa_proj_accum_main.x_commit_size;
pa_debug.debug('delete_cmt_txn_accum_details: ' || 'Number of Records Commited cumulatively = '|| TO_CHAR(tot_recs_processed));
pa_debug.debug('delete_cmt_txn_accum_details: ' || 'Records Deleted = '||TO_CHAR(tot_recs_processed));
END delete_cmt_txn_accum_details;
INSERT INTO pa_txn_accum_details
(txn_accum_id,
line_type,
expenditure_item_id,
line_num,
event_num,
cmt_line_id,
project_id,
task_id,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id)
VALUES
(x_txn_accum_id,
x_line_type,
x_expenditure_item_id,
x_line_num,
x_event_num,
x_cmt_line_id,
x_project_id,
x_task_id,
SYSDATE,
x_created_by,
x_last_updated_by,
SYSDATE,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id);
SELECT /*+ index(pta PA_TXN_ACCUM_N2)*/ txn_accum_id -- Added hint for bug 4504019
INTO x_txn_accum_id
FROM pa_txn_accum pta
WHERE x_project_id = pta.project_id
AND x_task_Id = pta.task_id
AND x_pa_period = pta.pa_period
AND x_gl_period = pta.gl_period
AND x_week_ending_date = pta.week_ending_date
AND x_month_ending_date = pta.month_ending_date
AND x_expenditure_type = pta.expenditure_type
AND x_organization_id = pta.organization_id
AND x_person_id = pta.person_id
AND NVL(x_job_id,-1) = NVL(pta.job_id,-1)
AND NVL(x_vendor_id,-1) = NVL(pta.vendor_id,-1)
AND NVL(x_non_labor_resource,'X') = NVL(pta.non_labor_resource,'X')
AND NVL(x_non_labor_resource_org_id,-1)
= NVL(pta.non_labor_resource_org_id,-1)
AND NVL(x_expenditure_category,'X')= NVL(pta.expenditure_category,'X')
AND NVL(x_revenue_category,'X') = NVL(pta.revenue_category,'X')
AND NVL(x_system_linkage_function,'X')
= NVL(pta.system_linkage_function,'X')
AND DECODE(x_line_type,'C',(NVL(x_cost_ind_compiled_set_id,-1)),-1)
= NVL(pta.cost_ind_compiled_set_id,-1)
AND DECODE(x_line_type,'R',(NVL(x_rev_ind_compiled_set_id,-1)),-1)
= NVL(pta.rev_ind_compiled_set_id,-1)
AND DECODE(x_line_type,'R',(NVL(x_inv_ind_compiled_set_id,-1)),-1)
= NVL(pta.inv_ind_compiled_set_id,-1)
AND DECODE(x_line_type,'M',(NVL(x_cmt_ind_compiled_set_id,-1)),-1)
= NVL(pta.cmt_ind_compiled_set_id,-1);
SELECT txn_accum_id
INTO x_txn_accum_id
FROM pa_txn_accum pta
WHERE x_project_id = pta.project_id
AND x_task_Id = pta.task_id
AND x_pa_period = pta.pa_period
AND x_gl_period = pta.gl_period
AND x_week_ending_date = pta.week_ending_date
AND x_month_ending_date = pta.month_ending_date
AND x_expenditure_type = pta.expenditure_type
AND x_organization_id = pta.organization_id
AND pta.person_id IS NULL
AND NVL(x_job_id,-1) = NVL(pta.job_id,-1)
AND NVL(x_vendor_id,-1) = NVL(pta.vendor_id,-1)
AND NVL(x_non_labor_resource,'X') = NVL(pta.non_labor_resource,'X')
AND NVL(x_non_labor_resource_org_id,-1)
= NVL(pta.non_labor_resource_org_id,-1)
AND NVL(x_expenditure_category,'X')= NVL(pta.expenditure_category,'X')
AND NVL(x_revenue_category,'X') = NVL(pta.revenue_category,'X')
AND NVL(x_system_linkage_function,'X')
= NVL(pta.system_linkage_function,'X')
AND DECODE(x_line_type,'C',(NVL(x_cost_ind_compiled_set_id,-1)),-1)
= NVL(pta.cost_ind_compiled_set_id,-1)
AND DECODE(x_line_type,'R',(NVL(x_rev_ind_compiled_set_id,-1)),-1)
= NVL(pta.rev_ind_compiled_set_id,-1)
AND DECODE(x_line_type,'R',(NVL(x_inv_ind_compiled_set_id,-1)),-1)
= NVL(pta.inv_ind_compiled_set_id,-1)
AND DECODE(x_line_type,'M',(NVL(x_cmt_ind_compiled_set_id,-1)),-1)
= NVL(pta.cmt_ind_compiled_set_id,-1);
SELECT txn_accum_id
INTO x_txn_accum_id
FROM pa_txn_accum pta
WHERE x_project_id = pta.project_id
AND x_task_Id = pta.task_id
AND x_pa_period = pta.pa_period
AND x_gl_period = pta.gl_period
AND x_week_ending_date = pta.week_ending_date
AND x_month_ending_date = pta.month_ending_date
AND x_event_type = pta.event_type
AND x_event_type_classification = pta.event_type_classification
AND x_organization_id = pta.organization_id
AND x_revenue_category = pta.revenue_category;
SELECT pa_txn_accum_s.NEXTVAL
INTO x_txn_accum_Id
FROM SYS.DUAL;
INSERT INTO PA_TXN_ACCUM (
txn_accum_id,
project_id,
task_Id,
pa_period,
gl_period,
week_ending_date,
month_ending_date,
person_id,
job_id,
vendor_id,
expenditure_type,
organization_id,
non_labor_resource,
non_labor_resource_org_id,
expenditure_category,
revenue_category,
event_type,
event_type_classification,
system_linkage_function,
cost_ind_compiled_set_id,
rev_ind_compiled_set_id,
inv_ind_compiled_set_id,
cmt_ind_compiled_set_id,
actual_cost_rollup_flag,
revenue_rollup_flag,
cmt_rollup_flag,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id
)
VALUES (
x_txn_accum_id,
x_project_id,
x_task_Id,
x_pa_period,
x_gl_period,
x_week_ending_date,
x_month_ending_date,
x_person_id,
x_job_id,
x_vendor_id,
x_expenditure_type,
x_organization_id,
x_non_labor_resource,
x_non_labor_resource_org_id,
x_expenditure_category,
x_revenue_category,
x_event_type,
x_event_type_classification,
x_system_linkage_function,
x_cost_ind_compiled_set_id,
x_rev_ind_compiled_set_id,
x_inv_ind_compiled_set_id,
x_cmt_ind_compiled_set_id,
'N',
'N',
'N',
SYSDATE,
x_created_by,
x_last_updated_by,
SYSDATE,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id
);
PROCEDURE delete_cmt_txns
( x_start_project_id IN NUMBER,
x_end_project_id IN NUMBER,
x_start_pa_date IN DATE,
x_end_pa_date IN DATE,
x_system_linkage_function IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
x_err_stage IN OUT NOCOPY VARCHAR2,
x_err_code IN OUT NOCOPY NUMBER)
IS
P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
pa_debug.debug('delete_cmt_txns: ' || x_err_stage);
DELETE
pa_commitment_txns pct
WHERE
pct.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id -- Commented out for bug 3736097
-- System_linkage_function can be Null for commitments
AND NVL(pct.system_linkage_function,'X') =
NVL(NVL(x_system_linkage_function,pct.system_linkage_function),'X')
AND EXISTS
( SELECT 'Yes'
FROM pa_periods
WHERE period_name = pct.pa_period
AND start_date >= x_start_pa_date
AND end_date <= x_end_pa_date
)
AND ROWNUM <= pa_proj_accum_main.x_commit_size;
pa_debug.debug('delete_cmt_txns: ' || 'Number of Records Commited cumulatively = '|| TO_CHAR(tot_recs_processed));
pa_debug.debug('delete_cmt_txns: ' || 'Records Deleted = '||TO_CHAR(tot_recs_processed));
END delete_cmt_txns;
select
per.PERIOD_NAME,
per.GL_PERIOD_NAME
into
l_cur_pa_period,
l_cur_gl_period
from
PA_PROJECTS_ALL prj,
PA_PERIODS_ALL per
where
prj.PROJECT_ID = x_start_project_id and
nvl(per.ORG_ID, -1) = nvl(prj.ORG_ID, -1) and
per.CURRENT_PA_PERIOD_FLAG = 'Y';
INSERT INTO pa_commitment_txns
( CMT_LINE_ID,
PROJECT_ID,
TASK_ID,
TRANSACTION_SOURCE,
LINE_TYPE,
CMT_NUMBER,
CMT_DISTRIBUTION_ID,
CMT_HEADER_ID,
DESCRIPTION,
EXPENDITURE_ITEM_DATE,
PA_PERIOD,
GL_PERIOD,
CMT_LINE_NUMBER,
CMT_CREATION_DATE,
CMT_APPROVED_DATE,
CMT_REQUESTOR_NAME,
CMT_BUYER_NAME,
CMT_APPROVED_FLAG,
CMT_PROMISED_DATE,
CMT_NEED_BY_DATE,
ORGANIZATION_ID,
VENDOR_ID,
VENDOR_NAME,
EXPENDITURE_TYPE,
EXPENDITURE_CATEGORY,
REVENUE_CATEGORY,
SYSTEM_LINKAGE_FUNCTION,
UNIT_OF_MEASURE,
UNIT_PRICE,
CMT_IND_COMPILED_SET_ID,
TOT_CMT_RAW_COST,
TOT_CMT_BURDENED_COST,
TOT_CMT_QUANTITY,
QUANTITY_ORDERED,
AMOUNT_ORDERED,
ORIGINAL_QUANTITY_ORDERED,
ORIGINAL_AMOUNT_ORDERED,
QUANTITY_CANCELLED,
AMOUNT_CANCELLED,
QUANTITY_DELIVERED,
AMOUNT_DELIVERED,
QUANTITY_INVOICED,
AMOUNT_INVOICED,
QUANTITY_OUTSTANDING_DELIVERY,
AMOUNT_OUTSTANDING_DELIVERY,
QUANTITY_OUTSTANDING_INVOICE,
AMOUNT_OUTSTANDING_INVOICE,
QUANTITY_OVERBILLED,
AMOUNT_OVERBILLED,
ORIGINAL_TXN_REFERENCE1,
ORIGINAL_TXN_REFERENCE2,
ORIGINAL_TXN_REFERENCE3,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
BURDEN_SUM_SOURCE_RUN_ID,
BURDEN_SUM_DEST_RUN_ID,
BURDEN_SUM_REJECTION_CODE,
acct_raw_cost,
acct_burdened_cost,
denom_currency_code,
denom_raw_cost,
denom_burdened_cost,
acct_currency_code,
acct_rate_date,
acct_rate_type,
acct_exchange_rate,
receipt_currency_code,
receipt_currency_amount,
receipt_exchange_rate,
project_currency_code,
project_rate_date,
project_rate_type,
project_exchange_rate,
generation_error_flag,
cmt_rejection_code,
/* added in FP.M */
INVENTORY_ITEM_ID,
UOM_CODE,
BOM_LABOR_RESOURCE_ID,
BOM_EQUIPMENT_RESOURCE_ID,
RESOURCE_CLASS ,
CBS_ELEMENT_ID --16461684
)
SELECT
pa_txn_accums.cmt_line_id,
pctv.project_id,
pctv.task_id,
pctv.transaction_source,
decode(pctv.line_type,'P','P','R','R','I','I','O'),/*Bug 4050269*/
pctv.cmt_number,
pctv.cmt_distribution_id,
pctv.cmt_header_id,
pctv.description,
pctv.expenditure_item_date,
/* For commitment change request
pctv.pa_period,
pctv.gl_period, and added below variables*/
l_cur_pa_period, /* Added for commitment change request*/
l_cur_gl_period, /* Added for commitment change request*/
pctv.cmt_line_number,
pctv.cmt_creation_date,
pctv.cmt_approved_date,
pctv.cmt_requestor_name,
pctv.cmt_buyer_name,
pctv.cmt_approved_flag,
pctv.cmt_promised_date,
pctv.cmt_need_by_date,
pctv.organization_id,
pctv.vendor_id,
pctv.vendor_name,
pctv.expenditure_type,
pctv.expenditure_category,
pctv.revenue_category,
pctv.system_linkage_function,
pctv.unit_of_measure,
pctv.unit_price,
pctv.cmt_ind_compiled_set_id,
TO_NUMBER(NULL),
TO_NUMBER(NULL),
pctv.tot_cmt_quantity,
pctv.quantity_ordered,
pctv.amount_ordered,
pctv.original_quantity_ordered,
pctv.original_amount_ordered,
pctv.quantity_cancelled,
pctv.amount_cancelled,
pctv.quantity_delivered,
TO_NUMBER(NULL),
pctv.quantity_invoiced,
pctv.amount_invoiced,
pctv.quantity_outstanding_delivery,
pctv.amount_outstanding_delivery,
pctv.quantity_outstanding_invoice,
pctv.amount_outstanding_invoice,
pctv.quantity_overbilled,
pctv.amount_overbilled,
pctv.original_txn_reference1,
pctv.original_txn_reference2,
pctv.original_txn_reference3,
SYSDATE,
x_last_updated_by,
SYSDATE,
x_created_by,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
NULL,
-9999,
NULL,
NULL,
-- Bug 8848682
-- pctv.acct_raw_cost,
-- pctv.acct_burdened_cost,
PA_CURRENCY.round_trans_currency_amt(pctv.acct_raw_cost, pctv.acct_currency_code) acct_raw_cost,
PA_CURRENCY.round_trans_currency_amt(pctv.acct_burdened_cost, pctv.acct_currency_code) acct_burdened_cost,
-- End bug 8848682
pctv.denom_currency_code,
-- Bug 8848682
-- pctv.denom_raw_cost,
-- pctv.denom_burdened_cost,
PA_CURRENCY.round_trans_currency_amt(pctv.denom_raw_cost, pctv.denom_currency_code) denom_raw_cost,
PA_CURRENCY.round_trans_currency_amt(pctv.denom_burdened_cost, pctv.denom_currency_code) denom_burdened_cost,
-- End bug 8848682
pctv.acct_currency_code,
pctv.acct_rate_date,
pctv.acct_rate_type,
pctv.acct_exchange_rate,
pctv.receipt_currency_code,
pctv.receipt_currency_amount,
pctv.receipt_exchange_rate,
NULL,
TO_DATE(NULL),
NULL,
TO_NUMBER(NULL),
'N',
NULL,
/* added in FP.M */
pctv.INVENTORY_ITEM_ID,
pctv.UOM_CODE,
pctv.wip_resource_id,
pctv.wip_resource_id,
pctv.resource_class,
pctv.cbs_element_id --16461684
FROM
pa_commitment_txns_v pctv
WHERE
pctv.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id commented for bug 3736097
AND NVL(pctv.system_linkage_function,'X') =
NVL(NVL(x_system_linkage_function,pctv.system_linkage_function),'X')
/* 14457478 */
AND pctv.PROJECT_ID IS NOT NULL
AND pctv.TASK_ID IS NOT NULL
AND pctv.TRANSACTION_SOURCE IS NOT NULL
AND pctv.LINE_TYPE IS NOT NULL
AND pctv.EXPENDITURE_ITEM_DATE IS NOT NULL
AND pctv.EXPENDITURE_TYPE IS NOT NULL
AND pctv.EXPENDITURE_CATEGORY IS NOT NULL
AND pctv.REVENUE_CATEGORY IS NOT NULL
AND pctv.SYSTEM_LINKAGE_FUNCTION IS NOT NULL ;
INSERT INTO pa_commitment_txns
( CMT_LINE_ID,
PROJECT_ID,
TASK_ID,
TRANSACTION_SOURCE,
LINE_TYPE,
CMT_NUMBER,
CMT_DISTRIBUTION_ID,
CMT_HEADER_ID,
DESCRIPTION,
EXPENDITURE_ITEM_DATE,
PA_PERIOD,
GL_PERIOD,
CMT_LINE_NUMBER,
CMT_CREATION_DATE,
CMT_APPROVED_DATE,
CMT_REQUESTOR_NAME,
CMT_BUYER_NAME,
CMT_APPROVED_FLAG,
CMT_PROMISED_DATE,
CMT_NEED_BY_DATE,
ORGANIZATION_ID,
VENDOR_ID,
VENDOR_NAME,
EXPENDITURE_TYPE,
EXPENDITURE_CATEGORY,
REVENUE_CATEGORY,
SYSTEM_LINKAGE_FUNCTION,
UNIT_OF_MEASURE,
UNIT_PRICE,
CMT_IND_COMPILED_SET_ID,
TOT_CMT_RAW_COST,
TOT_CMT_BURDENED_COST,
TOT_CMT_QUANTITY,
QUANTITY_ORDERED,
AMOUNT_ORDERED,
ORIGINAL_QUANTITY_ORDERED,
ORIGINAL_AMOUNT_ORDERED,
QUANTITY_CANCELLED,
AMOUNT_CANCELLED,
QUANTITY_DELIVERED,
AMOUNT_DELIVERED,
QUANTITY_INVOICED,
AMOUNT_INVOICED,
QUANTITY_OUTSTANDING_DELIVERY,
AMOUNT_OUTSTANDING_DELIVERY,
QUANTITY_OUTSTANDING_INVOICE,
AMOUNT_OUTSTANDING_INVOICE,
QUANTITY_OVERBILLED,
AMOUNT_OVERBILLED,
ORIGINAL_TXN_REFERENCE1,
ORIGINAL_TXN_REFERENCE2,
ORIGINAL_TXN_REFERENCE3,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
BURDEN_SUM_SOURCE_RUN_ID,
BURDEN_SUM_DEST_RUN_ID,
BURDEN_SUM_REJECTION_CODE,
acct_raw_cost,
acct_burdened_cost,
denom_currency_code,
denom_raw_cost,
denom_burdened_cost,
acct_currency_code,
acct_rate_date,
acct_rate_type,
acct_exchange_rate,
receipt_currency_code,
receipt_currency_amount,
receipt_exchange_rate,
project_currency_code,
project_rate_date,
project_rate_type,
project_exchange_rate,
generation_error_flag,
cmt_rejection_code,
/* added in FP.M */
INVENTORY_ITEM_ID,
UOM_CODE,
BOM_LABOR_RESOURCE_ID,
BOM_EQUIPMENT_RESOURCE_ID,
RESOURCE_CLASS
)
SELECT
pa_txn_accums.cmt_line_id,
pctv.project_id,
pctv.task_id,
pctv.transaction_source,
pctv.line_type,/*Bug 4050269*/
pctv.cmt_number,
pctv.cmt_distribution_id,
pctv.cmt_header_id,
pctv.description,
pctv.expenditure_item_date,
/* For commitment change request
pctv.pa_period,
pctv.gl_period, and added below variables*/
l_cur_pa_period, /* Added for commitment change request*/
l_cur_gl_period, /* Added for commitment change request*/
pctv.cmt_line_number,
pctv.cmt_creation_date,
pctv.cmt_approved_date,
pctv.cmt_requestor_name,
pctv.cmt_buyer_name,
pctv.cmt_approved_flag,
pctv.cmt_promised_date,
pctv.cmt_need_by_date,
pctv.organization_id,
pctv.vendor_id,
pctv.vendor_name,
pctv.expenditure_type,
pctv.expenditure_category,
pctv.revenue_category,
pctv.system_linkage_function,
pctv.unit_of_measure,
pctv.unit_price,
pctv.cmt_ind_compiled_set_id,
TO_NUMBER(NULL),
TO_NUMBER(NULL),
pctv.tot_cmt_quantity,
pctv.quantity_ordered,
pctv.amount_ordered,
pctv.original_quantity_ordered,
pctv.original_amount_ordered,
pctv.quantity_cancelled,
pctv.amount_cancelled,
pctv.quantity_delivered,
TO_NUMBER(NULL),
pctv.quantity_invoiced,
pctv.amount_invoiced,
pctv.quantity_outstanding_delivery,
pctv.amount_outstanding_delivery,
pctv.quantity_outstanding_invoice,
pctv.amount_outstanding_invoice,
pctv.quantity_overbilled,
pctv.amount_overbilled,
pctv.original_txn_reference1,
pctv.original_txn_reference2,
pctv.original_txn_reference3,
SYSDATE,
x_last_updated_by,
SYSDATE,
x_created_by,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
NULL,
-9999,
NULL,
NULL,
-- Bug 8848682
-- pctv.acct_raw_cost,
-- pctv.acct_burdened_cost,
PA_CURRENCY.round_trans_currency_amt(pctv.acct_raw_cost, pctv.acct_currency_code) acct_raw_cost,
PA_CURRENCY.round_trans_currency_amt(pctv.acct_burdened_cost, pctv.acct_currency_code) acct_burdened_cost,
-- End bug 8848682
pctv.denom_currency_code,
-- Bug 8848682
-- pctv.denom_raw_cost,
-- pctv.denom_burdened_cost,
PA_CURRENCY.round_trans_currency_amt(pctv.denom_raw_cost, pctv.denom_currency_code) denom_raw_cost,
PA_CURRENCY.round_trans_currency_amt(pctv.denom_burdened_cost, pctv.denom_currency_code) denom_burdened_cost,
-- End bug 8848682
pctv.acct_currency_code,
pctv.acct_rate_date,
pctv.acct_rate_type,
pctv.acct_exchange_rate,
pctv.receipt_currency_code,
pctv.receipt_currency_amount,
pctv.receipt_exchange_rate,
NULL,
TO_DATE(NULL),
NULL,
TO_NUMBER(NULL),
'N',
NULL,
/* added in FP.M */
pctv.INVENTORY_ITEM_ID,
pctv.UOM_CODE,
pctv.wip_resource_id,
pctv.wip_resource_id,
pctv.resource_class
FROM
pa_commitment_txns_tmp pctv
WHERE
pctv.project_id = x_start_project_id
/* 14457478 */
AND pctv.PROJECT_ID IS NOT NULL
AND pctv.TASK_ID IS NOT NULL
AND pctv.TRANSACTION_SOURCE IS NOT NULL
AND pctv.LINE_TYPE IS NOT NULL
AND pctv.EXPENDITURE_ITEM_DATE IS NOT NULL
AND pctv.EXPENDITURE_TYPE IS NOT NULL
AND pctv.EXPENDITURE_CATEGORY IS NOT NULL
AND pctv.REVENUE_CATEGORY IS NOT NULL
AND pctv.SYSTEM_LINKAGE_FUNCTION IS NOT NULL ;
for c in (select PROJECT_ID,
PA_PERIOD_NAME,
GL_PERIOD_NAME
from PJI_FM_EXTR_DREVN -- overload of drev table for cmt
where BATCH_ID = l_helper_batch_id) loop
if (l_x = 1) then
l_project_id_1 := c.PROJECT_ID;
insert into PA_COMMITMENT_TXNS
(
CMT_LINE_ID,
PROJECT_ID,
TASK_ID,
TRANSACTION_SOURCE,
LINE_TYPE,
CMT_NUMBER,
CMT_DISTRIBUTION_ID,
CMT_HEADER_ID,
DESCRIPTION,
EXPENDITURE_ITEM_DATE,
PA_PERIOD,
GL_PERIOD,
CMT_LINE_NUMBER,
CMT_CREATION_DATE,
CMT_APPROVED_DATE,
CMT_REQUESTOR_NAME,
CMT_BUYER_NAME,
CMT_APPROVED_FLAG,
CMT_PROMISED_DATE,
CMT_NEED_BY_DATE,
ORGANIZATION_ID,
VENDOR_ID,
VENDOR_NAME,
EXPENDITURE_TYPE,
EXPENDITURE_CATEGORY,
REVENUE_CATEGORY,
SYSTEM_LINKAGE_FUNCTION,
UNIT_OF_MEASURE,
UNIT_PRICE,
CMT_IND_COMPILED_SET_ID,
TOT_CMT_RAW_COST,
TOT_CMT_BURDENED_COST,
TOT_CMT_QUANTITY,
QUANTITY_ORDERED,
AMOUNT_ORDERED,
ORIGINAL_QUANTITY_ORDERED,
ORIGINAL_AMOUNT_ORDERED,
QUANTITY_CANCELLED,
AMOUNT_CANCELLED,
QUANTITY_DELIVERED,
AMOUNT_DELIVERED,
QUANTITY_INVOICED,
AMOUNT_INVOICED,
QUANTITY_OUTSTANDING_DELIVERY,
AMOUNT_OUTSTANDING_DELIVERY,
QUANTITY_OUTSTANDING_INVOICE,
AMOUNT_OUTSTANDING_INVOICE,
QUANTITY_OVERBILLED,
AMOUNT_OVERBILLED,
ORIGINAL_TXN_REFERENCE1,
ORIGINAL_TXN_REFERENCE2,
ORIGINAL_TXN_REFERENCE3,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
BURDEN_SUM_SOURCE_RUN_ID,
BURDEN_SUM_DEST_RUN_ID,
BURDEN_SUM_REJECTION_CODE,
ACCT_RAW_COST,
ACCT_BURDENED_COST,
DENOM_CURRENCY_CODE,
DENOM_RAW_COST,
DENOM_BURDENED_COST,
ACCT_CURRENCY_CODE,
ACCT_RATE_DATE,
ACCT_RATE_TYPE,
ACCT_EXCHANGE_RATE,
RECEIPT_CURRENCY_CODE,
RECEIPT_CURRENCY_AMOUNT,
RECEIPT_EXCHANGE_RATE,
PROJECT_CURRENCY_CODE,
PROJECT_RATE_DATE,
PROJECT_RATE_TYPE,
PROJECT_EXCHANGE_RATE,
GENERATION_ERROR_FLAG,
CMT_REJECTION_CODE,
INVENTORY_ITEM_ID,
UOM_CODE,
BOM_LABOR_RESOURCE_ID,
BOM_EQUIPMENT_RESOURCE_ID,
RESOURCE_CLASS,
CBS_ELEMENT_ID --16461684
)
select /*+ push_pred(pctv) */
PA_COMMITMENT_TXNS_S.NEXTVAL CMT_LINE_ID,
pctv.PROJECT_ID,
pctv.TASK_ID,
pctv.TRANSACTION_SOURCE,
decode(pctv.LINE_TYPE,
'P', 'P',
'R', 'R',
'I', 'I',
'O') LINE_TYPE,
pctv.CMT_NUMBER,
pctv.CMT_DISTRIBUTION_ID,
pctv.CMT_HEADER_ID,
pctv.DESCRIPTION,
pctv.EXPENDITURE_ITEM_DATE,
decode(pctv.PROJECT_ID,
l_project_id_1, l_pa_period_1,
l_project_id_2, l_pa_period_2,
l_project_id_3, l_pa_period_3,
l_project_id_4, l_pa_period_4,
l_project_id_5, l_pa_period_5,
l_project_id_6, l_pa_period_6,
l_project_id_7, l_pa_period_7,
l_project_id_8, l_pa_period_8,
l_project_id_9, l_pa_period_9,
l_project_id_10, l_pa_period_10,
l_project_id_11, l_pa_period_11,
l_project_id_12, l_pa_period_12,
l_project_id_13, l_pa_period_13,
l_project_id_14, l_pa_period_14,
l_project_id_15, l_pa_period_15,
l_project_id_16, l_pa_period_16,
l_project_id_17, l_pa_period_17,
l_project_id_18, l_pa_period_18,
l_project_id_19, l_pa_period_19,
l_project_id_20, l_pa_period_20) PA_PERIOD,
decode(pctv.PROJECT_ID,
l_project_id_1, l_gl_period_1,
l_project_id_2, l_gl_period_2,
l_project_id_3, l_gl_period_3,
l_project_id_4, l_gl_period_4,
l_project_id_5, l_gl_period_5,
l_project_id_6, l_gl_period_6,
l_project_id_7, l_gl_period_7,
l_project_id_8, l_gl_period_8,
l_project_id_9, l_gl_period_9,
l_project_id_10, l_gl_period_10,
l_project_id_11, l_gl_period_11,
l_project_id_12, l_gl_period_12,
l_project_id_13, l_gl_period_13,
l_project_id_14, l_gl_period_14,
l_project_id_15, l_gl_period_15,
l_project_id_16, l_gl_period_16,
l_project_id_17, l_gl_period_17,
l_project_id_18, l_gl_period_18,
l_project_id_19, l_gl_period_19,
l_project_id_20, l_gl_period_20) GL_PERIOD,
pctv.CMT_LINE_NUMBER,
pctv.CMT_CREATION_DATE,
pctv.CMT_APPROVED_DATE,
pctv.CMT_REQUESTOR_NAME,
pctv.CMT_BUYER_NAME,
pctv.CMT_APPROVED_FLAG,
pctv.CMT_PROMISED_DATE,
pctv.CMT_NEED_BY_DATE,
pctv.ORGANIZATION_ID,
pctv.VENDOR_ID,
pctv.VENDOR_NAME,
pctv.EXPENDITURE_TYPE,
pctv.EXPENDITURE_CATEGORY,
pctv.REVENUE_CATEGORY,
pctv.SYSTEM_LINKAGE_FUNCTION,
pctv.UNIT_OF_MEASURE,
pctv.UNIT_PRICE,
pctv.CMT_IND_COMPILED_SET_ID,
to_number(null) TOT_CMT_RAW_COST,
to_number(null) TOT_CMT_BURDENED_COST,
pctv.TOT_CMT_QUANTITY,
pctv.QUANTITY_ORDERED,
pctv.AMOUNT_ORDERED,
pctv.ORIGINAL_QUANTITY_ORDERED,
pctv.ORIGINAL_AMOUNT_ORDERED,
pctv.QUANTITY_CANCELLED,
pctv.AMOUNT_CANCELLED,
pctv.QUANTITY_DELIVERED,
to_number(null) AMOUNT_DELIVERED,
pctv.QUANTITY_INVOICED,
pctv.AMOUNT_INVOICED,
pctv.QUANTITY_OUTSTANDING_DELIVERY,
pctv.AMOUNT_OUTSTANDING_DELIVERY,
pctv.QUANTITY_OUTSTANDING_INVOICE,
pctv.AMOUNT_OUTSTANDING_INVOICE,
pctv.QUANTITY_OVERBILLED,
pctv.AMOUNT_OVERBILLED,
pctv.ORIGINAL_TXN_REFERENCE1,
pctv.ORIGINAL_TXN_REFERENCE2,
pctv.ORIGINAL_TXN_REFERENCE3,
sysdate LAST_UPDATE_DATE,
x_last_updated_by LAST_UPDATED_BY,
sysdate CREATION_DATE,
x_created_by CREATED_BY,
x_last_update_login LAST_UPDATE_LOGIN,
x_request_id REQUEST_ID,
x_program_application_id PROGRAM_APPLICATION_ID,
x_program_id PROGRAM_ID,
null PROGRAM_UPDATE_DATE,
-9999 BURDEN_SUM_SOURCE_RUN_ID,
null BURDEN_SUM_DEST_RUN_ID,
null BURDEN_SUM_REJECTION_CODE,
-- Bug 8848682
-- pctv.acct_raw_cost,
-- pctv.acct_burdened_cost,
PA_CURRENCY.round_trans_currency_amt(pctv.acct_raw_cost, pctv.acct_currency_code) acct_raw_cost,
PA_CURRENCY.round_trans_currency_amt(pctv.acct_burdened_cost, pctv.acct_currency_code) acct_burdened_cost,
-- End bug 8848682
pctv.denom_currency_code,
-- Bug 8848682
-- pctv.denom_raw_cost,
-- pctv.denom_burdened_cost,
PA_CURRENCY.round_trans_currency_amt(pctv.denom_raw_cost, pctv.denom_currency_code) denom_raw_cost,
PA_CURRENCY.round_trans_currency_amt(pctv.denom_burdened_cost, pctv.denom_currency_code) denom_burdened_cost,
-- End bug 8848682
pctv.ACCT_CURRENCY_CODE,
pctv.ACCT_RATE_DATE,
pctv.ACCT_RATE_TYPE,
pctv.ACCT_EXCHANGE_RATE,
pctv.RECEIPT_CURRENCY_CODE,
pctv.RECEIPT_CURRENCY_AMOUNT,
pctv.RECEIPT_EXCHANGE_RATE,
null PROJECT_CURRENCY_CODE,
to_date(null) PROJECT_RATE_DATE,
null PROJECT_RATE_TYPE,
to_number(null) PROJECT_EXCHANGE_RATE,
'N' GENERATION_ERROR_FLAG,
null CMT_REJECTION_CODE,
pctv.INVENTORY_ITEM_ID,
pctv.UOM_CODE,
pctv.WIP_RESOURCE_ID BOM_LABOR_RESOURCE_ID,
pctv.WIP_RESOURCE_ID BOM_EQUIPMENT_RESOURCE_ID,
pctv.RESOURCE_CLASS,
pctv.CBS_ELEMENT_ID --16461684
from
PA_COMMITMENT_TXNS_V pctv
where
pctv.PROJECT_ID = l_project_id_1
/* 14457478 */
AND pctv.PROJECT_ID IS NOT NULL
AND pctv.TASK_ID IS NOT NULL
AND pctv.TRANSACTION_SOURCE IS NOT NULL
AND pctv.LINE_TYPE IS NOT NULL
AND pctv.EXPENDITURE_ITEM_DATE IS NOT NULL
AND pctv.EXPENDITURE_TYPE IS NOT NULL
AND pctv.EXPENDITURE_CATEGORY IS NOT NULL
AND pctv.REVENUE_CATEGORY IS NOT NULL
AND pctv.SYSTEM_LINKAGE_FUNCTION IS NOT NULL ;
insert into PA_COMMITMENT_TXNS
(
CMT_LINE_ID,
PROJECT_ID,
TASK_ID,
TRANSACTION_SOURCE,
LINE_TYPE,
CMT_NUMBER,
CMT_DISTRIBUTION_ID,
CMT_HEADER_ID,
DESCRIPTION,
EXPENDITURE_ITEM_DATE,
PA_PERIOD,
GL_PERIOD,
CMT_LINE_NUMBER,
CMT_CREATION_DATE,
CMT_APPROVED_DATE,
CMT_REQUESTOR_NAME,
CMT_BUYER_NAME,
CMT_APPROVED_FLAG,
CMT_PROMISED_DATE,
CMT_NEED_BY_DATE,
ORGANIZATION_ID,
VENDOR_ID,
VENDOR_NAME,
EXPENDITURE_TYPE,
EXPENDITURE_CATEGORY,
REVENUE_CATEGORY,
SYSTEM_LINKAGE_FUNCTION,
UNIT_OF_MEASURE,
UNIT_PRICE,
CMT_IND_COMPILED_SET_ID,
TOT_CMT_RAW_COST,
TOT_CMT_BURDENED_COST,
TOT_CMT_QUANTITY,
QUANTITY_ORDERED,
AMOUNT_ORDERED,
ORIGINAL_QUANTITY_ORDERED,
ORIGINAL_AMOUNT_ORDERED,
QUANTITY_CANCELLED,
AMOUNT_CANCELLED,
QUANTITY_DELIVERED,
AMOUNT_DELIVERED,
QUANTITY_INVOICED,
AMOUNT_INVOICED,
QUANTITY_OUTSTANDING_DELIVERY,
AMOUNT_OUTSTANDING_DELIVERY,
QUANTITY_OUTSTANDING_INVOICE,
AMOUNT_OUTSTANDING_INVOICE,
QUANTITY_OVERBILLED,
AMOUNT_OVERBILLED,
ORIGINAL_TXN_REFERENCE1,
ORIGINAL_TXN_REFERENCE2,
ORIGINAL_TXN_REFERENCE3,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
BURDEN_SUM_SOURCE_RUN_ID,
BURDEN_SUM_DEST_RUN_ID,
BURDEN_SUM_REJECTION_CODE,
ACCT_RAW_COST,
ACCT_BURDENED_COST,
DENOM_CURRENCY_CODE,
DENOM_RAW_COST,
DENOM_BURDENED_COST,
ACCT_CURRENCY_CODE,
ACCT_RATE_DATE,
ACCT_RATE_TYPE,
ACCT_EXCHANGE_RATE,
RECEIPT_CURRENCY_CODE,
RECEIPT_CURRENCY_AMOUNT,
RECEIPT_EXCHANGE_RATE,
PROJECT_CURRENCY_CODE,
PROJECT_RATE_DATE,
PROJECT_RATE_TYPE,
PROJECT_EXCHANGE_RATE,
GENERATION_ERROR_FLAG,
CMT_REJECTION_CODE,
INVENTORY_ITEM_ID,
UOM_CODE,
BOM_LABOR_RESOURCE_ID,
BOM_EQUIPMENT_RESOURCE_ID,
RESOURCE_CLASS
)
select
PA_COMMITMENT_TXNS_S.NEXTVAL CMT_LINE_ID,
pctv.PROJECT_ID,
pctv.TASK_ID,
pctv.TRANSACTION_SOURCE,
pctv.LINE_TYPE,
pctv.CMT_NUMBER,
pctv.CMT_DISTRIBUTION_ID,
pctv.CMT_HEADER_ID,
pctv.DESCRIPTION,
pctv.EXPENDITURE_ITEM_DATE,
decode(pctv.PROJECT_ID,
l_project_id_1, l_pa_period_1,
l_project_id_2, l_pa_period_2,
l_project_id_3, l_pa_period_3,
l_project_id_4, l_pa_period_4,
l_project_id_5, l_pa_period_5,
l_project_id_6, l_pa_period_6,
l_project_id_7, l_pa_period_7,
l_project_id_8, l_pa_period_8,
l_project_id_9, l_pa_period_9,
l_project_id_10, l_pa_period_10,
l_project_id_11, l_pa_period_11,
l_project_id_12, l_pa_period_12,
l_project_id_13, l_pa_period_13,
l_project_id_14, l_pa_period_14,
l_project_id_15, l_pa_period_15,
l_project_id_16, l_pa_period_16,
l_project_id_17, l_pa_period_17,
l_project_id_18, l_pa_period_18,
l_project_id_19, l_pa_period_19,
l_project_id_20, l_pa_period_20) PA_PERIOD,
decode(pctv.PROJECT_ID,
l_project_id_1, l_gl_period_1,
l_project_id_2, l_gl_period_2,
l_project_id_3, l_gl_period_3,
l_project_id_4, l_gl_period_4,
l_project_id_5, l_gl_period_5,
l_project_id_6, l_gl_period_6,
l_project_id_7, l_gl_period_7,
l_project_id_8, l_gl_period_8,
l_project_id_9, l_gl_period_9,
l_project_id_10, l_gl_period_10,
l_project_id_11, l_gl_period_11,
l_project_id_12, l_gl_period_12,
l_project_id_13, l_gl_period_13,
l_project_id_14, l_gl_period_14,
l_project_id_15, l_gl_period_15,
l_project_id_16, l_gl_period_16,
l_project_id_17, l_gl_period_17,
l_project_id_18, l_gl_period_18,
l_project_id_19, l_gl_period_19,
l_project_id_20, l_gl_period_20) GL_PERIOD,
pctv.CMT_LINE_NUMBER,
pctv.CMT_CREATION_DATE,
pctv.CMT_APPROVED_DATE,
pctv.CMT_REQUESTOR_NAME,
pctv.CMT_BUYER_NAME,
pctv.CMT_APPROVED_FLAG,
pctv.CMT_PROMISED_DATE,
pctv.CMT_NEED_BY_DATE,
pctv.ORGANIZATION_ID,
pctv.VENDOR_ID,
pctv.VENDOR_NAME,
pctv.EXPENDITURE_TYPE,
pctv.EXPENDITURE_CATEGORY,
pctv.REVENUE_CATEGORY,
pctv.SYSTEM_LINKAGE_FUNCTION,
pctv.UNIT_OF_MEASURE,
pctv.UNIT_PRICE,
pctv.CMT_IND_COMPILED_SET_ID,
to_number(null) TOT_CMT_RAW_COST,
to_number(null) TOT_CMT_BURDENED_COST,
pctv.TOT_CMT_QUANTITY,
pctv.QUANTITY_ORDERED,
pctv.AMOUNT_ORDERED,
pctv.ORIGINAL_QUANTITY_ORDERED,
pctv.ORIGINAL_AMOUNT_ORDERED,
pctv.QUANTITY_CANCELLED,
pctv.AMOUNT_CANCELLED,
pctv.QUANTITY_DELIVERED,
to_number(null) AMOUNT_DELIVERED,
pctv.QUANTITY_INVOICED,
pctv.AMOUNT_INVOICED,
pctv.QUANTITY_OUTSTANDING_DELIVERY,
pctv.AMOUNT_OUTSTANDING_DELIVERY,
pctv.QUANTITY_OUTSTANDING_INVOICE,
pctv.AMOUNT_OUTSTANDING_INVOICE,
pctv.QUANTITY_OVERBILLED,
pctv.AMOUNT_OVERBILLED,
pctv.ORIGINAL_TXN_REFERENCE1,
pctv.ORIGINAL_TXN_REFERENCE2,
pctv.ORIGINAL_TXN_REFERENCE3,
sysdate LAST_UPDATE_DATE,
x_last_updated_by LAST_UPDATED_BY,
sysdate CREATION_DATE,
x_created_by CREATED_BY,
x_last_update_login LAST_UPDATE_LOGIN,
x_request_id REQUEST_ID,
x_program_application_id PROGRAM_APPLICATION_ID,
x_program_id PROGRAM_ID,
null PROGRAM_UPDATE_DATE,
-9999 BURDEN_SUM_SOURCE_RUN_ID,
null BURDEN_SUM_DEST_RUN_ID,
null BURDEN_SUM_REJECTION_CODE,
-- Bug 8848682
-- pctv.acct_raw_cost,
-- pctv.acct_burdened_cost,
PA_CURRENCY.round_trans_currency_amt(pctv.acct_raw_cost, pctv.acct_currency_code) acct_raw_cost,
PA_CURRENCY.round_trans_currency_amt(pctv.acct_burdened_cost, pctv.acct_currency_code) acct_burdened_cost,
-- End bug 8848682
pctv.denom_currency_code,
-- Bug 8848682
-- pctv.denom_raw_cost,
-- pctv.denom_burdened_cost,
PA_CURRENCY.round_trans_currency_amt(pctv.denom_raw_cost, pctv.denom_currency_code) denom_raw_cost,
PA_CURRENCY.round_trans_currency_amt(pctv.denom_burdened_cost, pctv.denom_currency_code) denom_burdened_cost,
-- End bug 8848682
pctv.ACCT_CURRENCY_CODE,
pctv.ACCT_RATE_DATE,
pctv.ACCT_RATE_TYPE,
pctv.ACCT_EXCHANGE_RATE,
pctv.RECEIPT_CURRENCY_CODE,
pctv.RECEIPT_CURRENCY_AMOUNT,
pctv.RECEIPT_EXCHANGE_RATE,
null PROJECT_CURRENCY_CODE,
to_date(null) PROJECT_RATE_DATE,
null PROJECT_RATE_TYPE,
to_number(null) PROJECT_EXCHANGE_RATE,
'N' GENERATION_ERROR_FLAG,
null CMT_REJECTION_CODE,
pctv.INVENTORY_ITEM_ID,
pctv.UOM_CODE,
pctv.WIP_RESOURCE_ID BOM_LABOR_RESOURCE_ID,
pctv.WIP_RESOURCE_ID BOM_EQUIPMENT_RESOURCE_ID,
pctv.RESOURCE_CLASS
from
pa_commitment_txns_tmp pctv
WHERE
pctv.PROJECT_ID = l_project_id_1
/* 14457478 */
AND pctv.PROJECT_ID IS NOT NULL
AND pctv.TASK_ID IS NOT NULL
AND pctv.TRANSACTION_SOURCE IS NOT NULL
AND pctv.LINE_TYPE IS NOT NULL
AND pctv.EXPENDITURE_ITEM_DATE IS NOT NULL
AND pctv.EXPENDITURE_TYPE IS NOT NULL
AND pctv.EXPENDITURE_CATEGORY IS NOT NULL
AND pctv.REVENUE_CATEGORY IS NOT NULL
AND pctv.SYSTEM_LINKAGE_FUNCTION IS NOT NULL ;
pa_debug.debug('create_cmt_txns: ' || 'Records Inserted = '||TO_CHAR(SQL%ROWCOUNT));
UPDATE pa_txn_accum pta
SET pta.i_tot_raw_cost = DECODE(raw_cost_flag,'Y',
(NVL(i_tot_raw_cost, 0) + x_tot_raw_cost_tb2(i)),
NULL),
pta.i_tot_burdened_cost = DECODE(burdened_cost_flag,'Y',
(NVL(i_tot_burdened_cost, 0) +
x_tot_burdened_cost_tb2(i)),NULL),
pta.i_tot_quantity = DECODE(quantity_flag,'Y',
(NVL(i_tot_quantity, 0) +
x_tot_quantity_tb2(i)),NULL),
pta.i_tot_labor_hours = DECODE(labor_hours_flag,'Y',
(NVL(i_tot_labor_hours,0) +
DECODE(pta.system_linkage_function,
'OT',
x_tot_quantity_tb2(i),
'ST',
x_tot_quantity_tb2(i),
0)),NULL),
pta.i_tot_billable_raw_cost = DECODE(billable_raw_cost_flag,'Y',
(NVL(i_tot_billable_raw_cost, 0) +
x_tot_billable_raw_cost_tb2(i)),NULL),
pta.i_tot_billable_burdened_cost = DECODE(billable_burdened_cost_flag,'Y',
(NVL(i_tot_billable_burdened_cost, 0) +
x_tot_billable_brdn_cost_tb2(i)),NULL),
pta.i_tot_billable_quantity = DECODE(billable_quantity_flag,'Y',
(NVL(i_tot_billable_quantity, 0) +
x_tot_billable_quantity_tb2(i)),NULL),
pta.i_tot_billable_labor_hours = DECODE(billable_labor_hours_flag,'Y',
(NVL(i_tot_billable_labor_hours,0) +
DECODE(pta.system_linkage_function,
'OT',
x_tot_billable_quantity_tb2(i),
'ST',
x_tot_billable_quantity_tb2(i),
0)),NULL),
pta.unit_of_measure = x_unit_of_measure_tb2(i),
pta.actual_cost_rollup_flag = 'Y',
pta.last_update_date = SYSDATE,
pta.last_updated_by = x_last_updated_by,
pta.request_Id = x_request_id,
pta.program_application_id = x_program_application_id,
pta.program_id = x_program_id,
pta.program_update_Date = SYSDATE
WHERE
pta.txn_accum_id = x_txn_accum_id_tb2(i);
UPDATE pa_txn_accum pta
SET pta.i_tot_revenue = DECODE(revenue_flag,'Y',
(NVL(i_tot_revenue, 0) +
x_tot_revenue),NULL),
pta.unit_of_measure = x_unit_of_measure,
pta.revenue_rollup_flag = 'Y',
pta.last_update_date = SYSDATE,
pta.last_updated_by = x_last_updated_by,
pta.request_Id = x_request_id,
pta.program_application_id = x_program_application_id,
pta.program_id = x_program_id,
pta.program_update_Date = SYSDATE
WHERE
pta.txn_accum_id = x_txn_accum_id;
x_tot_burdened_cost in the following update */
UPDATE pa_txn_accum pta
SET pta.tot_cmt_raw_cost = DECODE(cmt_raw_cost_flag,'Y',
(NVL(tot_cmt_raw_cost, 0) +
NVL(x_tot_cmt_raw_cost,0)),NULL),
pta.tot_cmt_burdened_cost = DECODE(cmt_burdened_cost_flag,'Y',
(NVL(tot_cmt_burdened_cost, 0) +
NVL(x_tot_cmt_burdened_cost,0)),NULL),
pta.cmt_rollup_flag = 'Y',
pta.last_update_date = SYSDATE,
pta.last_updated_by = x_last_updated_by,
pta.request_Id = x_request_id,
pta.program_application_id= x_program_application_id,
pta.program_id = x_program_id,
pta.program_update_Date = SYSDATE
WHERE
pta.txn_accum_id = x_txn_accum_id;
-- The cursor selcdl selects all CDLs which
-- satisfy the pa_period given as the parameters
-- the argument x_mode represents the mode for accumulation i.e.
-- 'I' for incremental and 'F' for FULL
/* Bug# 1770772 - Breaking the cursor into two and call/open it conditionally based on x_mode parameter
to eliminate the decode on resource_accumulated_Flag and hence use index usage
*/
/* Bug# 10371758 - quantity from pa_cost_distribution_lines_all is selected as
NVL(cdl.quantity,0) for 'I' lines also.This is modified as
DECODE( DECODE(et.unit_of_measure, NULL, ei.unit_of_measure, et.unit_of_measure),'HOURS',
DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0) quantity.
And also billable quantity is modified as
DECODE(cdl.billable_flag,'Y',DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0) billable_quantity,
*/
CURSOR selcdls1 IS
SELECT
cdl.ROWID cdlrowid,
cdl.expenditure_item_id expenditure_item_id,
cdl.line_num line_num,
pe.incurred_by_person_id person_id,
ei.job_id job_id,
NVL(ei.override_to_organization_id,
pe.incurred_by_organization_id) organization_id,
decode(ei.system_linkage_function,'VI',cdl.system_reference1,NULL) vendor_id, -- Modified for bug#5878137
et.expenditure_type expenditure_type,
ei.non_labor_resource non_labor_resource,
et.expenditure_category expenditure_category,
et.revenue_category_code revenue_category,
ei.organization_id non_labor_resource_org_id,
ei.system_linkage_function system_linkage_function,
cdl.project_id project_id,
cdl.task_id task_id,
cdl.RECVR_PA_PERIOD_NAME pa_period,
cdl.RECVR_GL_PERIOD_NAME gl_period,
pe.expenditure_ending_date week_ending_date,
LAST_DAY(ei.expenditure_item_date) month_ending_date,
NVL(cdl.amount,0) raw_cost,
--DECODE( DECODE(et.unit_of_measure, NULL, ei.unit_of_measure, et.unit_of_measure),'HOURS', DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0) quantity,
DECODE( DECODE(ei.system_linkage_function, 'ST', 'Y', 'OT', 'Y', 'N'),'Y', DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0) quantity, --Bug 16022826
NVL(cdl.burdened_cost,0) burdened_cost,
DECODE(cdl.billable_flag,'Y',NVL(cdl.amount,0),0) billable_raw_cost,
--DECODE(cdl.billable_flag,'Y',DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0) billable_quantity,
DECODE(cdl.billable_flag,'Y',DECODE( DECODE(ei.system_linkage_function, 'ST', 'Y', 'OT', 'Y', 'N'),'Y', DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0),0) billable_quantity, --Bug 16022826
DECODE(cdl.billable_flag,'Y',NVL(cdl.burdened_cost,0),0) billable_burdened_cost,
decode(et.unit_of_measure,NULL, ei.unit_of_measure, et.unit_of_measure) unit_of_measure,
cdl.ind_compiled_set_id cost_ind_compiled_set_id
FROM
pa_expenditures_all pe,
pa_expenditure_types et,
pa_expenditure_items_all ei,
pa_cost_distribution_lines_all cdl
WHERE
cdl.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id
AND cdl.line_type = x_cdl_line_type
AND cdl.resource_accumulated_flag = 'N'
AND cdl.expenditure_item_id = ei.expenditure_item_id
AND NVL(cdl.org_id,-99) = NVL(ei.org_id,-99)
AND ei.expenditure_type = et.expenditure_type
AND ei.task_id = cdl.task_id
AND pe.expenditure_id = ei.expenditure_id
AND NVL(pe.org_id,-99) = NVL(ei.org_id,-99)
AND ei.system_linkage_function||'' =
NVL(x_system_linkage_function,ei.system_linkage_function)
AND TRUNC(cdl.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date;
/* Bug# 10371758 - quantity from pa_cost_distribution_lines_all is selected as
NVL(cdl.quantity,0) for 'I' lines also.This is modified as
DECODE( DECODE(et.unit_of_measure, NULL, ei.unit_of_measure, et.unit_of_measure),'HOURS',
DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0) quantity.
And also billable quantity is modified as
DECODE(cdl.billable_flag,'Y',DECODE(line_type, 'I', 0,NVL(cdl.quantity,0)),0) billable_quantity,
*/
CURSOR selcdls2 IS
SELECT
cdl.ROWID cdlrowid,
cdl.expenditure_item_id expenditure_item_id,
cdl.line_num line_num,
pe.incurred_by_person_id person_id,
ei.job_id job_id,
NVL(ei.override_to_organization_id,
pe.incurred_by_organization_id) organization_id,
decode(ei.system_linkage_function,'VI',cdl.system_reference1,NULL) vendor_id, -- Modified for bug#5878137
et.expenditure_type expenditure_type,
ei.non_labor_resource non_labor_resource,
et.expenditure_category expenditure_category,
et.revenue_category_code revenue_category,
ei.organization_id non_labor_resource_org_id,
ei.system_linkage_function system_linkage_function,
cdl.project_id project_id,
cdl.task_id task_id,
cdl.RECVR_PA_PERIOD_NAME pa_period,
cdl.RECVR_GL_PERIOD_NAME gl_period,
pe.expenditure_ending_date week_ending_date,
LAST_DAY(ei.expenditure_item_date) month_ending_date,
NVL(cdl.amount,0) raw_cost,
--DECODE( DECODE(et.unit_of_measure, NULL, ei.unit_of_measure,et.unit_of_measure),'HOURS', DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0) quantity,
DECODE( DECODE(ei.system_linkage_function, 'ST', 'Y', 'OT', 'Y', 'N'),'Y', DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0) quantity, --Bug 16022826
NVL(cdl.burdened_cost,0) burdened_cost,
DECODE(cdl.billable_flag,'Y',NVL(cdl.amount,0),0) billable_raw_cost,
--DECODE(cdl.billable_flag,'Y',DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0) billable_quantity,
DECODE(cdl.billable_flag,'Y',DECODE( DECODE(ei.system_linkage_function, 'ST', 'Y', 'OT', 'Y', 'N'),'Y', DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0),0) billable_quantity, --Bug 16022826
DECODE(cdl.billable_flag,'Y',NVL(cdl.burdened_cost,0),0) billable_burdened_cost,
decode(et.unit_of_measure ,NULL, ei.unit_of_measure, et.unit_of_measure) unit_of_measure,
cdl.ind_compiled_set_id cost_ind_compiled_set_id
FROM
pa_expenditures_all pe,
pa_expenditure_types et,
pa_expenditure_items_all ei,
pa_cost_distribution_lines_all cdl
WHERE
-- cdl.project_id BETWEEN x_start_project_id AND x_end_project_id -- Modified for bug 3736097
cdl.project_id = x_start_project_id
AND cdl.line_type = x_cdl_line_type
/* Commented for bug# 1770772 while splitting the cursor in two
AND cdl.resource_accumulated_flag =
decode(x_mode,'I','N',
'F',cdl.resource_accumulated_flag,'N')
*/
AND cdl.expenditure_item_id = ei.expenditure_item_id
AND NVL(cdl.org_id,-99) = NVL(ei.org_id,-99)
AND ei.expenditure_type = et.expenditure_type
AND ei.task_id = cdl.task_id
AND pe.expenditure_id = ei.expenditure_id
AND NVL(pe.org_id,-99) = NVL(ei.org_id,-99)
AND ei.system_linkage_function||'' =
NVL(x_system_linkage_function,ei.system_linkage_function)
AND TRUNC(cdl.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date;
SELECT
p.period_name pa_period1,
g.period_name gl_period1
FROM
gl_date_period_map p,
gl_date_period_map g,
pa_expenditures_all pe,
pa_expenditure_types et,
pa_expenditure_items_all ei,
pa_cost_distribution_lines_all cdl,
pa_implementations pi,
gl_sets_of_books sob
WHERE
-- cdl.project_id BETWEEN x_start_project_id AND x_end_project_id -- Modified for bug 3736097
cdl.project_id = x_start_project_id
AND cdl.ROWID = CHARTOROWID(crowid)
AND cdl.line_type = x_cdl_line_type
AND cdl.expenditure_item_id = ei.expenditure_item_id
AND NVL(cdl.org_id,-99) = NVL(ei.org_id,-99)
AND ei.expenditure_type = et.expenditure_type
AND ei.task_id = cdl.task_id
AND pe.expenditure_id = ei.expenditure_id
AND NVL(pe.org_id,-99) = NVL(ei.org_id,-99)
AND sob.set_of_books_id = pi.set_of_books_id
AND p.period_set_name = sob.period_set_name
AND g.period_set_name = sob.period_set_name
AND p.period_type = pi.pa_period_type
AND g.period_type = sob.accounted_period_type
/* Bug #3493462: Added trunc to recvr_pa_date */
AND p.accounting_date = TRUNC(cdl.recvr_pa_date)
AND g.accounting_date = NVL(TRUNC(cdl.recvr_gl_date), TRUNC(cdl.recvr_pa_date))
AND ei.system_linkage_function||'' =
NVL(x_system_linkage_function,ei.system_linkage_function)
AND TRUNC(cdl.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date;
UPDATE
pa_cost_distribution_lines_all
SET
resource_accumulated_flag = 'Y'
WHERE
ROWID = cdlrec.cdlrowid;
UPDATE
pa_cost_distribution_lines_all
SET
resource_accumulated_flag = 'Y'
WHERE
ROWID = cdlrowid_tb1(i);
SELECT
dr.ROWID drrowid,
rdl.expenditure_item_id expenditure_item_id,
rdl.line_num line_num,
pe.incurred_by_person_id person_id,
ei.job_id job_id,
NVL(ei.override_to_organization_id,
pe.incurred_by_organization_id) organization_id,
et.expenditure_type expenditure_type,
ei.non_labor_resource non_labor_resource,
et.expenditure_category expenditure_category,
et.revenue_category_code revenue_category,
ei.organization_id non_labor_resource_org_id,
ei.system_linkage_function system_linkage_function,
dr.project_id project_id,
ei.task_id task_id,
dr.PA_PERIOD_NAME pa_period,
dr.GL_PERIOD_NAME gl_period,
pe.expenditure_ending_date week_ending_date,
LAST_DAY(ei.expenditure_item_date) month_ending_date,
rdl.rev_ind_compiled_set_id rev_ind_compiled_set_id,
rdl.inv_ind_compiled_set_id inv_ind_compiled_set_id,
NVL(rdl.amount,0) amount,
decode(et.unit_of_measure,NULL,et.unit_of_measure,ei.unit_of_measure) unit_of_measure
FROM
pa_expenditures_all pe,
pa_expenditure_types et,
pa_expenditure_items_all ei,
pa_cust_rev_dist_lines rdl,
pa_draft_revenues dr
WHERE
dr.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id Commented for Bug # 3736097
AND NVL(dr.resource_accumulated_flag,'S') =
DECODE(x_mode,'I','S',
'F',NVL(dr.resource_accumulated_flag,'S'),'S')
AND dr.released_date IS NOT NULL
AND rdl.function_code NOT IN ('LRL','LRB','URL','URB')
AND dr.project_id = rdl.project_id
AND dr.draft_revenue_num = rdl.draft_revenue_num
AND rdl.expenditure_item_id = ei.expenditure_item_id
AND ei.expenditure_type = et.expenditure_type
AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date
AND NVL(pe.org_id,-99) = NVL(ei.org_id,-99)
AND pe.expenditure_id = ei.expenditure_id;
SELECT
p.period_name pa_period1,
g.period_name gl_period1
FROM
gl_date_period_map p,
gl_date_period_map g,
pa_expenditures_all pe,
pa_expenditure_types et,
pa_expenditure_items_all ei,
pa_cust_rev_dist_lines rdl,
pa_draft_revenues dr,
pa_implementations pi,
gl_sets_of_books sob
WHERE
dr.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id Commented for Bug # 3736097
AND dr.ROWID = CHARTOROWID(rrowid)
AND NVL(dr.resource_accumulated_flag,'S') =
DECODE(x_mode,'I','S',
'F',NVL(dr.resource_accumulated_flag,'S'),'S')
AND dr.released_date IS NOT NULL
AND rdl.function_code NOT IN ('LRL','LRB','URL','URB')
AND dr.project_id = rdl.project_id
AND dr.draft_revenue_num = rdl.draft_revenue_num
AND rdl.expenditure_item_id = ei.expenditure_item_id
AND ei.expenditure_type = et.expenditure_type
AND sob.set_of_books_id = pi.set_of_books_id
AND p.period_set_name = sob.period_set_name
AND g.period_set_name = sob.period_set_name
AND p.period_type = pi.pa_period_type
AND g.period_type = sob.accounted_period_type
AND p.accounting_date = dr.pa_date
AND g.accounting_date = NVL(dr.gl_date, dr.pa_date)
AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date
AND NVL(pe.org_id,-99) = NVL(ei.org_id,-99)
AND pe.expenditure_id = ei.expenditure_id;
UPDATE
pa_draft_revenues
SET
resource_accumulated_flag = 'S'
WHERE
ROWID = rdlrec.drrowid;
SELECT
dr.ROWID drrowid,
erdl.event_num event_num,
erdl.line_num line_num,
ev.organization_id organization_id,
ev.event_type,
evt.revenue_category_code revenue_category,
erdl.project_id,
NVL(erdl.task_id,0) task_id,
dr.PA_PERIOD_NAME pa_period,
dr.GL_PERIOD_NAME gl_period,
evt.event_type_classification,
pa_utils.GetWeekEnding(ev.completion_date) week_ending_date,
LAST_DAY(ev.completion_date) month_ending_date,
NVL(erdl.amount,0) amount
FROM
pa_events ev,
pa_event_types evt,
pa_cust_event_rev_dist_lines erdl,
pa_draft_revenues dr
WHERE
dr.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id commented for Bug # 3736097
AND NVL(dr.resource_accumulated_flag,'S') =
DECODE(x_mode,'I','S',
'F',NVL(dr.resource_accumulated_flag,'S'),'S')
AND dr.released_date IS NOT NULL
AND dr.project_id = erdl.project_id
AND dr.draft_revenue_num = erdl.draft_revenue_num
AND erdl.project_id = ev.project_id
AND NVL(erdl.task_id,0) = NVL(ev.task_id,0)
AND ev.event_num = erdl.event_num
AND ev.event_type = evt.event_type
AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date;
SELECT
p.period_name pa_period1,
g.period_name gl_period1
FROM
gl_date_period_map p,
gl_date_period_map g,
pa_events ev,
pa_event_types evt,
pa_cust_event_rev_dist_lines erdl,
pa_draft_revenues dr,
pa_implementations pi,
gl_sets_of_books sob
WHERE
dr.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id commented for Bug # 3736097
AND dr.ROWID = CHARTOROWID(rrowid)
AND NVL(dr.resource_accumulated_flag,'S') =
DECODE(x_mode,'I','S',
'F',NVL(dr.resource_accumulated_flag,'S'),'S')
AND dr.released_date IS NOT NULL
AND dr.project_id = erdl.project_id
AND dr.draft_revenue_num = erdl.draft_revenue_num
AND erdl.project_id = ev.project_id
AND NVL(erdl.task_id,0) = NVL(ev.task_id,0)
AND ev.event_num = erdl.event_num
AND ev.event_type = evt.event_type
AND sob.set_of_books_id = pi.set_of_books_id
AND p.period_set_name = sob.period_set_name
AND g.period_set_name = sob.period_set_name
AND p.period_type = pi.pa_period_type
AND g.period_type = sob.accounted_period_type
AND p.accounting_date = dr.pa_date
AND g.accounting_date = NVL(dr.gl_date, dr.pa_date)
AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date;
UPDATE
pa_draft_revenues
SET
resource_accumulated_flag = 'S'
WHERE
ROWID = eventrec.drrowid;
SELECT
'R' line_type,
dr.ROWID drrowid,
rdl.expenditure_item_id expenditure_item_id,
rdl.line_num line_num,
pe.incurred_by_person_id person_id,
ei.job_id job_id,
NVL(ei.override_to_organization_id,
pe.incurred_by_organization_id) organization_id,
et.expenditure_type expenditure_type,
ei.non_labor_resource non_labor_resource,
et.expenditure_category expenditure_category,
et.revenue_category_code revenue_category,
ei.organization_id non_labor_resource_org_id,
ei.system_linkage_function system_linkage_function,
dr.project_id project_id,
ei.task_id task_id,
dr.PA_PERIOD_NAME pa_period,
dr.GL_PERIOD_NAME gl_period,
pe.expenditure_ending_date week_ending_date,
LAST_DAY(ei.expenditure_item_date) month_ending_date,
rdl.rev_ind_compiled_set_id rev_ind_compiled_set_id,
rdl.inv_ind_compiled_set_id inv_ind_compiled_set_id,
NVL(rdl.amount,0) amount,
TO_NUMBER(NULL) event_num,
NULL event_type,
NULL event_type_classification,
et.unit_of_measure unit_of_measure
FROM
pa_expenditures_all pe,
pa_expenditure_types et,
pa_expenditure_items_all ei,
pa_cust_rev_dist_lines rdl,
pa_draft_revenues dr
WHERE
dr.project_id = x_start_project_id ---- x_start_project_id and x_end_project_id
AND NVL(dr.resource_accumulated_flag,'S') =
DECODE(x_mode,'I','S',
'F',NVL(dr.resource_accumulated_flag,'S'),'S')
AND dr.released_date IS NOT NULL
AND rdl.function_code NOT IN ('LRL','LRB','URL','URB')
AND dr.project_id = rdl.project_id
AND dr.draft_revenue_num = rdl.draft_revenue_num
AND rdl.expenditure_item_id = ei.expenditure_item_id
AND ei.expenditure_type = et.expenditure_type
AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date
AND NVL(pe.org_id,-99) = NVL(ei.org_id,-99)
AND pe.expenditure_id = ei.expenditure_id
UNION ALL
SELECT
'E' line_type,
dr.ROWID drrowid,
TO_NUMBER(NULL) expenditure_item_id,
erdl.line_num line_num,
TO_NUMBER(NULL) person_id,
TO_NUMBER(NULL) job_id,
ev.organization_id organization_id,
NULL expenditure_type,
NULL non_labor_resource,
NULL expenditure_category,
evt.revenue_category_code revenue_category,
TO_NUMBER(NULL) non_labor_resource_org_id,
NULL system_linkage_function,
erdl.project_id project_id,
NVL(erdl.task_id,0) task_id,
dr.PA_PERIOD_NAME pa_period,
dr.GL_PERIOD_NAME gl_period,
pa_utils.GetWeekEnding(ev.completion_date) week_ending_date,
LAST_DAY(ev.completion_date) month_ending_date,
TO_NUMBER(NULL) rev_ind_compiled_set_id,
TO_NUMBER(NULL) inv_ind_compiled_set_id,
NVL(erdl.amount,0) amount,
erdl.event_num event_num,
ev.event_type event_type,
evt.event_type_classification event_type_classification,
NULL unit_of_measure
FROM
pa_events ev,
pa_event_types evt,
pa_cust_event_rev_dist_lines erdl,
pa_draft_revenues dr
WHERE
dr.project_id = x_start_project_id ---- x_start_project_id and x_end_project_id
AND NVL(dr.resource_accumulated_flag,'S') =
DECODE(x_mode,'I','S',
'F',NVL(dr.resource_accumulated_flag,'S'),'S')
AND dr.released_date IS NOT NULL
AND dr.project_id = erdl.project_id
AND dr.draft_revenue_num = erdl.draft_revenue_num
AND erdl.project_id = ev.project_id
AND NVL(erdl.task_id,0) = NVL(ev.task_id,0)
AND ev.event_num = erdl.event_num
AND ev.event_type = evt.event_type
AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date
ORDER BY 2;
SELECT
p.period_name pa_period1,
g.period_name gl_period1
FROM
gl_date_period_map p,
gl_date_period_map g,
pa_draft_revenues dr,
pa_implementations pi,
gl_sets_of_books sob
WHERE
dr.project_id = x_start_project_id
AND dr.ROWID = CHARTOROWID(rrowid)
AND NVL(dr.resource_accumulated_flag,'S') = DECODE(x_mode,'I','S','F',NVL(dr.resource_accumulated_flag,'S'),'S')
AND dr.released_date IS NOT NULL
AND sob.set_of_books_id = pi.set_of_books_id
AND p.period_set_name = sob.period_set_name
AND g.period_set_name = sob.period_set_name
AND p.period_type = pi.pa_period_type
AND g.period_type = sob.accounted_period_type
AND p.accounting_date = dr.pa_date
AND g.accounting_date = NVL(dr.gl_date, dr.pa_date)
AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date;
UPDATE
pa_draft_revenues
SET
resource_accumulated_flag = 'Y',
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id
WHERE
ROWID = curr_rowid;
UPDATE
pa_draft_revenues
SET
resource_accumulated_flag = 'Y',
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id
WHERE
ROWID = curr_rowid;
-- The cursor selcmts selects all PA_COMMITMENT_TXNS which
-- satisfy the given parameters
CURSOR selcmts IS
SELECT
pct.cmt_line_id,
pct.project_id,
pct.task_id,
pa_utils.GetWeekEnding(pct.expenditure_item_date) week_ending_date,
LAST_DAY(pct.expenditure_item_date) month_ending_date,
pct.pa_period,
pct.gl_period,
pct.organization_id,
pct.vendor_id,
pct.expenditure_type,
pct.expenditure_category,
pct.revenue_category,
pct.system_linkage_function,
pct.cmt_ind_compiled_set_id,
pct.expenditure_item_date,
pct.denom_currency_code,
pct.denom_raw_cost,
pct.denom_burdened_cost,
pct.acct_currency_code,
pct.acct_rate_date,
pct.acct_rate_type,
pct.acct_exchange_rate,
pct.acct_raw_cost,
pct.acct_burdened_cost,
pct.receipt_currency_code,
pct.receipt_currency_amount,
pct.receipt_exchange_rate
FROM
pa_commitment_txns pct
-- Bug#2634995 - removed the reference to pa_implentations as it is not used in the SQL
-- ,pa_implementations pi
WHERE
pct.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id commented for bug 3736097
AND pct.system_linkage_function||'' =
NVL(x_system_linkage_function,pct.system_linkage_function);
SELECT project_currency_code,projfunc_currency_code
FROM pa_projects p
WHERE p.project_id = l_project_id;
UPDATE pa_commitment_txns
SET tot_cmt_raw_cost = l_tot_cmt_raw_cost
, tot_cmt_burdened_cost = l_tot_cmt_burdened_cost
, project_currency_code = l_project_curr_code
, project_rate_date = l_project_rate_date
, project_rate_type = l_project_rate_type
, project_exchange_rate = l_project_exch_rate
, proj_raw_cost = l_PROJECT_RAW_COST /* added for FP.M proj_raw_cost stores raw cost in project currency */
, proj_burdened_cost = l_PROJECT_BURDENED_COST /* added for FP.M proj_burdened_cost stores burdened cost in project currency */
WHERE cmt_line_id = cmtrec.cmt_line_id;
UPDATE pa_commitment_txns
SET generation_error_flag = 'Y'
, cmt_rejection_code = l_cmt_rejection_code
WHERE cmt_line_id = cmtrec.cmt_line_id;
END IF; -- UPDATE COMMITMENT ROW
UPDATE pa_project_accum_headers
SET sum_exception_code = l_sum_exception_code
WHERE project_id = x_start_project_id
AND task_id = 0
AND resource_list_id = 0
AND resource_list_member_id = 0;