[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE pa_rp_job_assignments
SET status_flag = p_previous_succeed
WHERE status_flag = 'P'
AND main_request_id = p_main_request_id
AND worker_request_id = p_worker_request_id;
UPDATE pa_rp_job_assignments
SET status_flag ='P', worker_request_id = p_worker_request_id
WHERE status_flag = 'C' AND ROWNUM=1 AND main_request_id = p_main_request_id
RETURN bursting_value_1, bursting_value_2, bursting_value_3
INTO x_bursting_values(1), x_bursting_values(2), x_bursting_values(3);
SELECT COUNT(*)
INTO l_count
FROM
PA_RP_Definitions_B rp, PA_RP_Template_Lists templates, XDO_LOBS xdo
WHERE rp.rp_Id = p_rp_id
AND rp.rp_Id = templates.rp_Id
AND templates.template_code = xdo.LOB_CODE
AND xdo.application_short_name = p_app_short_name
AND xdo.LOB_TYPE = 'TEMPLATE'
AND (rp.dt_process_date is null or rp.dt_process_date < xdo.last_update_date OR templates.dt_process_flag='N')
AND ROWNUM = 1;
SELECT COUNT(*)
INTO l_count
FROM
PA_RP_Definitions_B rp, PA_RP_TYPES_b TYPES, XDO_LOBS xdo
WHERE rp.rp_Id = p_rp_id
AND rp.rp_type_Id = types.rp_type_Id
AND types.seeded_dt_code = xdo.LOB_CODE
AND xdo.application_short_name = p_app_short_name
AND xdo.LOB_TYPE = 'DATA_TEMPLATE'
AND rp.dt_process_date < xdo.last_update_date
AND ROWNUM = 1;
UPDATE PA_RP_DEFINITIONS_B
SET dt_process_date = SYSDATE
WHERE rp_id = p_rp_id;
SELECT RP_FILE_DATA
INTO x_trimmed_dt
FROM PA_RP_LOBS lobs
WHERE lobs.rp_id = p_rp_id
AND lobs.lob_type = 'DT'
FOR UPDATE;
UPDATE pa_rp_template_lists
SET DT_Process_flag = 'Y'
WHERE rp_id = p_rp_id;
SELECT trimmed_dt
INTO x_trimmed_dt
FROM PA_RP_DEFINITIONS_B
WHERE rp_id = p_rp_id
FOR UPDATE;
SELECT DECODE(COUNT(*),0,'Y','N')
INTO l_finish
FROM pa_rp_job_assignments
WHERE main_request_id = p_main_request_id
AND worker_request_id = l_request_id
AND status_flag = 'P';
INSERT INTO pa_rp_job_assignments_history
(ASSIGNMENT_ID, MAIN_REQUEST_ID, WORKER_REQUEST_ID, STATUS_FLAG, RP_ID, BURSTING_VALUE_1, BURSTING_VALUE_2, BURSTING_VALUE_3, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, LAST_UPDATE_LOGIN)
SELECT ASSIGNMENT_ID, MAIN_REQUEST_ID, WORKER_REQUEST_ID, STATUS_FLAG, RP_ID, BURSTING_VALUE_1, BURSTING_VALUE_2, BURSTING_VALUE_3, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, LAST_UPDATE_LOGIN FROM pa_rp_job_assignments
WHERE main_request_id = p_main_request_id;
DELETE FROM pa_rp_job_assignments
WHERE main_request_id = p_main_request_id;
--Insert RP_DEFINITION_First
l_rp_id := TO_NUMBER(SYSDATE,'j');
Pa_Distribution_Lists_Pkg.INSERT_ROW
(
P_LIST_ID => l_dist_list_id,
P_NAME => 'Reportin pack distribution list',
P_DESCRIPTION => l_rp_id,
P_RECORD_VERSION_NUMBER => NULL,
P_CREATED_BY => Fnd_Global.user_id,
P_CREATION_DATE => SYSDATE,
P_LAST_UPDATED_BY => Fnd_Global.user_id,
P_LAST_UPDATE_DATE => SYSDATE,
P_LAST_UPDATE_LOGIN => Fnd_Global.user_id
);
Pa_Object_Dist_Lists_Pkg.INSERT_ROW
(
P_LIST_ID => l_dist_list_id,
P_OBJECT_TYPE => 'PA_RP_LIST',
P_OBJECT_ID => l_rp_id,
P_RECORD_VERSION_NUMBER => NULL,
P_CREATED_BY => Fnd_Global.user_id,
P_CREATION_DATE => SYSDATE,
P_LAST_UPDATED_BY => Fnd_Global.user_id,
P_LAST_UPDATE_DATE => SYSDATE,
P_LAST_UPDATE_LOGIN => Fnd_Global.user_id
);
Pa_Dist_List_Items_Pkg.Update_Row
(
P_LIST_ITEM_ID => l_dist_list_items(i).list_item_id,
P_LIST_ID => l_dist_list_id,
P_RECIPIENT_TYPE => l_dist_list_items(i).recipient_type,
P_RECIPIENT_ID => l_dist_list_items(i).recipient_id,
P_ACCESS_LEVEL => NULL,
P_MENU_ID => NULL,
P_EMAIL => l_dist_list_items(i).email_exists,
P_RECORD_VERSION_NUMBER => NULL,
P_LAST_UPDATED_BY => Fnd_Global.user_id,
P_LAST_UPDATE_DATE => SYSDATE,
P_LAST_UPDATE_LOGIN => Fnd_Global.user_id
);
Pa_Dist_List_Items_Pkg.INSERT_ROW
(
P_LIST_ITEM_ID => l_dist_list_item_id,
P_LIST_ID => l_dist_list_id,
P_RECIPIENT_TYPE => l_dist_list_items(i).recipient_type,
P_RECIPIENT_ID => l_dist_list_items(i).recipient_id,
P_ACCESS_LEVEL => NULL,
P_MENU_ID => NULL,
P_EMAIL => l_dist_list_items(i).email_exists,
P_RECORD_VERSION_NUMBER => NULL,
P_LAST_UPDATED_BY => Fnd_Global.user_id,
P_LAST_UPDATE_DATE => SYSDATE,
P_LAST_UPDATE_LOGIN => Fnd_Global.user_id,
P_CREATED_BY => Fnd_Global.user_id,
P_CREATION_DATE => SYSDATE
);
INSERT INTO PA_RP_CONC_PARAMS(REQUEST_ID
, RP_ID
, PARAM_NAME
, PARAM_VALUE
,LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN)
VALUES(p_main_request_id
, p_rp_id
, p_param_names(l_i)
, p_param_values(l_i)
, SYSDATE()
, Fnd_Global.user_id
, SYSDATE()
, Fnd_Global.user_id
, Fnd_Global.login_id);
SELECT DISTINCT(p.email_address)
FROM pa_object_dist_lists o,
pa_dist_list_items i,
pa_project_parties_v p,
fnd_user u
WHERE o.object_id = p_rp_id
AND o.list_id = i.list_id
AND i.recipient_type = 'PROJECT_ROLE'
AND p.project_role_id = i.recipient_id
AND p.object_type = 'PA_PROJECTS'
AND p.object_id = p_project_id
AND u.user_name=p.user_name
AND (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE))
AND (TRUNC(SYSDATE) BETWEEN TRUNC(p.start_date_active) AND NVL(TRUNC(p.end_date_active),SYSDATE));
SELECT
MAX(DECODE(ppfo.approved_cost_plan_type_flag, 'Y', fin_plan_type_id, -99)) pApprCostBudgetPTId,
MAX(DECODE(ppfo.approved_rev_plan_type_flag, 'Y', fin_plan_type_id, -99)) pApprRevBudgetPTId,
MAX(DECODE(ppfo.primary_cost_forecast_flag, 'Y', fin_plan_type_id, -99)) pPrimCostFcstPTId,
MAX(DECODE(ppfo.primary_rev_forecast_flag, 'Y', fin_plan_type_id, -99)) pPrimRevFcstPTId
INTO
x_cstbudget_plan_type_id,
x_revbudget_plan_type_id,
x_cstforecast_plan_type_id,
x_revforecast_plan_type_id
FROM pa_proj_fp_options ppfo
WHERE 1=1
AND ppfo.project_id = p_project_id
AND ppfo.fin_plan_option_level_code = 'PLAN_TYPE'
AND 'Y' IN (ppfo.approved_cost_plan_type_flag
, ppfo.approved_cost_plan_type_flag
, ppfo.primary_cost_forecast_flag
, ppfo.primary_rev_forecast_flag);
SELECT
MAX(DECODE(pbv.current_flag, 'Y', DECODE(x_cstbudget_plan_type_id, pbv.fin_plan_type_id, DECODE(version_type,'REVENUE',-99,pbv.budget_version_id), -99), -99)) pApprCostBudgetCurrPVId,
MAX(DECODE(pbv.current_original_flag, 'Y', DECODE(x_cstbudget_plan_type_id, pbv.fin_plan_type_id, DECODE(version_type,'REVENUE',-99,pbv.budget_version_id), -99), -99)) pApprCostBudgetOrigPVId,
MAX(DECODE(pbv.current_flag, 'Y', DECODE(x_revbudget_plan_type_id , pbv.fin_plan_type_id, DECODE(version_type,'COST',-99,pbv.budget_version_id), -99), -99)) pApprRevBudgetCurrPVId,
MAX(DECODE(pbv.current_original_flag, 'Y', DECODE(x_revbudget_plan_type_id , pbv.fin_plan_type_id, DECODE(version_type,'COST',-99,pbv.budget_version_id), -99), -99)) pApprRevBudgetOrigPVId,
MAX(DECODE(pbv.current_flag, 'Y', DECODE(x_cstforecast_plan_type_id, pbv.fin_plan_type_id, DECODE(version_type,'REVENUE',-99,pbv.budget_version_id), -99), -99)) pPrimCostFcstCurrPVId,
MAX(DECODE(pbv.current_flag, 'Y', DECODE(x_revforecast_plan_type_id, pbv.fin_plan_type_id, DECODE(version_type,'COST',-99,pbv.budget_version_id), -99), -99)) pPrimRevFcstCurrPVId,
MAX(DECODE(pbv.current_flag, 'Y', DECODE(p_cstbudget2_plan_type_id, pbv.fin_plan_type_id, DECODE(version_type,'REVENUE',-99,pbv.budget_version_id), -99), -99)) pCostCurrPVId,
MAX(DECODE(pbv.current_original_flag, 'Y', DECODE(p_cstbudget2_plan_type_id, pbv.fin_plan_type_id, DECODE(version_type,'REVENUE',-99,pbv.budget_version_id), -99), -99)) pCostOrigPVId,
MAX(DECODE(pbv.current_flag, 'Y', DECODE(p_revbudget2_plan_type_id, pbv.fin_plan_type_id, DECODE(version_type,'COST',-99,pbv.budget_version_id), -99), -99)) pRevCurrPVId,
MAX(DECODE(pbv.current_original_flag, 'Y', DECODE(p_revbudget2_plan_type_id, pbv.fin_plan_type_id, DECODE(version_type,'COST',-99,pbv.budget_version_id), -99), -99)) pRevOrigPVId
INTO
x_cstbudget_version_id,
x_orig_cstbudget_version_id,
x_revbudget_version_id,
x_orig_revbudget_version_id,
x_cstforecast_version_id,
x_revforecast_version_id,
x_cstbudget2_version_id,
x_orig_cstbudget2_version_id,
x_revbudget2_version_id,
x_orig_revbudget2_version_id
FROM pa_budget_versions pbv
WHERE 1=1
AND pbv.project_id = p_project_id
AND pbv.fin_plan_type_id IN ( x_cstbudget_plan_type_id
, x_revbudget_plan_type_id
, x_cstforecast_plan_type_id
, x_revforecast_plan_type_id
, p_cstbudget2_plan_type_id
, p_revbudget2_plan_type_id)
AND 'Y' IN (pbv.current_flag, pbv.current_original_flag)
AND pbv.version_type IS NOT NULL;
SELECT project_currency_code, projfunc_currency_code
INTO l_proj_currency,l_projfunc_currency
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT info.gl_calendar_id, info.pa_calendar_id
INTO l_gl_calendar_id, l_pa_calendar_id
FROM pji_org_extr_info info, pa_projects_all proj
WHERE info.org_id = proj.org_id
AND proj.project_id = p_project_id;
SELECT start_date
INTO l_report_date
FROM pji_time_ent_period_v
WHERE TRUNC(SYSDATE) BETWEEN start_date AND end_date;
SELECT MAX(start_date)
INTO l_report_date
FROM pji_time_ent_period_v
WHERE end_date
SELECT name, ent_period_id, start_date, end_date
INTO x_period_name, x_period_id, l_start_date, l_end_date
FROM pji_time_ent_period_v
WHERE l_report_date BETWEEN start_date AND end_date;
SELECT MIN(TIM.start_date) first_open
INTO l_report_date
FROM
pji_time_cal_period_v TIM
, gl_period_statuses glps
, pa_implementations paimp
WHERE 1=1
AND TIM.calendar_id = x_calendar_id
AND paimp.set_of_books_id = glps.set_of_books_id
AND glps.application_id = l_application_id
AND glps.period_name = TIM.NAME
AND closing_status = 'O';
SELECT MAX(TIM.start_date) last_open
INTO l_report_date
FROM
pji_time_cal_period_v TIM
, gl_period_statuses glps
, pa_implementations paimp
WHERE 1=1
AND TIM.calendar_id = x_calendar_id
AND paimp.set_of_books_id = glps.set_of_books_id
AND glps.application_id = 275
AND glps.period_name = TIM.NAME
AND closing_status = 'O';
SELECT MAX(TIM.start_date) last_closed
INTO l_report_date
FROM
pji_time_cal_period_v TIM
, gl_period_statuses glps
, pa_implementations paimp
WHERE 1=1
AND TIM.calendar_id = x_calendar_id
AND paimp.set_of_books_id = glps.set_of_books_id
AND glps.application_id = l_application_id
AND glps.period_name = TIM.NAME
AND closing_status = 'C';
SELECT start_date
INTO l_report_date
FROM pji_time_cal_period_v
WHERE TRUNC(SYSDATE) BETWEEN start_date
AND end_date
AND calendar_id = x_calendar_id;
SELECT MAX(start_date)
INTO l_report_date
FROM pji_time_cal_period_v
WHERE end_date < l_report_date
AND calendar_id = x_calendar_id;
SELECT name, cal_period_id, start_date, end_date, start_date
INTO x_period_name, x_period_id, l_start_date, l_end_date, l_report_date
FROM pji_time_cal_period_v
WHERE name = p_spec_period_name
AND calendar_id = x_calendar_id;
SELECT name, cal_period_id, start_date, end_date
INTO x_period_name, x_period_id, l_start_date, l_end_date
FROM pji_time_cal_period_v
WHERE l_report_date BETWEEN start_date AND end_date
AND calendar_id = x_calendar_id;
SELECT
MAX(DECODE(ppe.object_type, 'PA_TASKS', ppe.element_number, ppa.segment1)) task_number
INTO
l_number
FROM pa_proj_elements ppe, pa_projects_all ppa
WHERE ppe.project_id = p_project_id
AND ppa.project_id = ppe.project_id
AND ppe.proj_element_id = p_proj_elem_id;