[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MAX(nvl(final_process_date,to_date('4712/12/31', 'YYYY/MM/DD')))
FROM per_periods_of_service
WHERE person_id = X_person_id
AND nvl(final_process_date,to_date('4712/12/31', 'YYYY/MM/DD')) > = trunc(X_date);
SELECT
tc.task_id
, tc.person_id
, tc.expenditure_category
, tc.expenditure_type
, tc.non_labor_resource
, tc.chargeable_flag
, tc.billable_indicator
, tc.SCHEDULED_EXP_ONLY
, tc.employees_only_flag -- PA.M/CWK changes
, tc.workplan_res_only_flag -- PA.M/Task Assignment changes
FROM
pa_transaction_controls tc
WHERE
tc.project_id = X_project_id
AND ( tc.task_id is null
OR
tc.task_id = l_task_id -- Replaced X_task_id with l_task_id for bug 16220146
)
/******** Bug fix :2345895 Start donot modify refer to bug for details
AND ( tc.person_id IS NULL these lines are commented out for bug fix 2345895
or or c_person_id = -9999 ) --Added for bug# 2188422
Commented for bug# 2188422
OR
(X_person_id is NOT NULL and --Added for # 1652082
tc.person_id = X_person_id )
OR -- Added or clause for # 1652082
(X_vendor_id is NOT NULL and
X_person_id is NULL)
)
***END of bug fix 2345895 ********/
/*AND ((p_sys_link_function = 'VI'Commented for bug 5735180*/ /*Bug# 2955795: Replaced c_sys_link_func by p_sys_link_function*/
/* AND (tc.expenditure_category is NOT NULL
OR tc.expenditure_type is NOT NULL
OR tc.task_id is NOT NULL
)
AND tc.person_id is null -- Added for bug 2942492
AND tc.non_labor_resource is null) Commented for Bug 5735180*/ /* Added for bug 2942492, added ( in start and )
for bug 2939224 */
/* OR Commented for bug5735180 */ /* Added for bug#2939224 */
/* Added VI following condition for bug 5735180*/
AND ( --Added for Bug 5735180
(p_sys_link_function in ('USG', 'PJ','VI') AND ((tc.person_id is NULL) OR ((X_person_id is NOT NULL) AND
(X_person_id = tc.person_id)))) -- Modified for bug 4585740
OR
( nvl(p_sys_link_function,'-99') NOT IN ('VI', 'USG', 'PJ') /* Bug 5721949 */
/*Bug# 2955795: Replaced c_sys_link_func by p_sys_link_function*/
/* Bug 2939224-Added not in USG and PG and VI */
AND (tc.person_id is NULL OR
/**tc.person_id = tc.person_id Bug 2467454 **/
tc.person_id = x_person_id
)
)
)
/** Bug fix :2345895 End **/
AND ( tc.expenditure_category IS NULL
OR tc.expenditure_category =
( SELECT expenditure_category
FROM pa_expenditure_types
WHERE expenditure_type = X_expenditure_type ) )
AND ( tc.expenditure_type IS NULL
OR tc.expenditure_type = X_expenditure_type )
AND ( tc.non_labor_resource IS NULL
OR tc.non_labor_resource = X_non_labor_resource )
AND X_ei_date BETWEEN tc.start_date_active
AND nvl( tc.end_date_active, X_ei_date )
GROUP BY
tc.task_id
, decode(p_sys_link_function,'VI',0, 'USG', 0, 'PJ', 0, tc.person_id) -- for VI group by task,exp_cat,exp_tpe /*2955795*/ /* Added USG and PJ here as no grouping on person_id is required for these sys links */
/*** , tc.person_id commented for bug fix :2345895 **/-- for <> VI group by task,person,exp_cat,exp_type
, tc.expenditure_category
, tc.expenditure_type
, tc.non_labor_resource
, tc.start_date_active
, tc.end_date_active
, tc.chargeable_flag
, tc.billable_indicator
, tc.SCHEDULED_EXP_ONLY
, tc.employees_only_flag -- PA.M/CWK changes
, tc.workplan_res_only_flag -- PA.M/Task Assignment changes
, tc.person_id
ORDER BY tc.task_id;
SELECT
p.project_status_code
, nvl( p.start_date, X_ei_date ) p_start_date
, nvl( p.completion_date, X_ei_date ) p_end_date
, nvl( p.limit_to_txn_controls_flag, 'N' ) p_limit_flag
, nvl( p.template_flag, 'N') p_template_flag
, nvl( t.chargeable_flag, 'N' ) t_chargeable_flag
, nvl( t.billable_flag, 'N' ) t_billable_flag
, nvl( t.start_date, X_ei_date ) t_start_date
, nvl( t.completion_date, X_ei_date ) t_end_date
, nvl( t.limit_to_txn_controls_flag, 'N' ) t_limit_flag
, t.retirement_cost_flag t_ret_cost_flag -- PA.L Retirement Cost Processing
, pt.project_type_class_code p_proj_typ_class_code -- PA.L Retirement Cost Processing
, nvl(p.assign_precedes_task, 'N') p_assign_precedes_task -- Bug 3017533
, nvl(p.bill_labor_accrual, 'N') bill_labor_accrual /* Start changes for bug#12597910 */
FROM
pa_tasks t
, pa_projects_all p
, pa_project_types_all pt -- PA.L Retirement Cost Processing
WHERE
t.task_id = l_task_id -- Replaced X_task_id with l_task_id for bug 16220146
AND p.project_id = t.project_id
AND p.project_id = X_project_id
AND p.project_type = pt.project_type -- PA.L Retirement Cost Processing
AND p.org_id = pt.org_id ; -- For the Bug 5368274.Reverted the
SELECT count(1)
INTO dummy
FROM dual
WHERE NOT EXISTS
( SELECT 1
FROM pa_expend_typ_sys_links etsl
, pa_expenditure_types et
WHERE etsl.expenditure_type = et.expenditure_type
AND etsl.expenditure_type = X_expenditure_type
AND etsl.system_linkage_function = 'VI'
AND et.cost_rate_flag = 'Y' );
SELECT count(1)
INTO dummy
FROM pa_expenditure_types et
WHERE et.expenditure_type = X_expenditure_type
AND X_ei_date BETWEEN et.start_date_active AND
nvl( et.end_date_active, X_ei_date ) ;
SELECT count(1)
INTO dummy
FROM pa_organizations_v
WHERE organization_id = X_nl_resource_org_id
AND X_ei_date between date_from and nvl(date_to,X_ei_date);
SELECT count(1)
INTO dummy
FROM dual
WHERE EXISTS
( SELECT 1
FROM pa_transaction_controls
WHERE project_id = X_project_id
AND task_id = l_task_id ) ; -- Replaced X_task_id with l_task_id for bug 16220146
SELECT count(*)
INTO dummy2
FROM po_vendors_ap_v
WHERE vendor_id = x_vendor_id
AND vendor_type_lookup_code = 'EMPLOYEE'
AND employee_id is NULL ;
SELECT count(*)
INTO dummy
FROM po_vendors_ap_v
WHERE vendor_id = x_vendor_id
AND employee_id is NULL ;
select COUNT(*)
into DUMMY3
from PER_PEOPLE_F PPL,
PER_ASSIGNMENTS_F ASGN ,
PER_PERSON_TYPES PTYPE ,
PO_VENDORS_AP_V POAPV
where
POAPV.VENDOR_ID = X_VENDOR_ID
and POAPV.VENDOR_ID = ASGN.VENDOR_ID
and PPL.PERSON_ID = ASGN.PERSON_ID
and poapv.EMPLOYEE_ID is null
and PTYPE.PERSON_TYPE_ID = PPL.PERSON_TYPE_ID
and PTYPE.SYSTEM_PERSON_TYPE in ('OTHER','CWK');
SELECT vend.employee_id
FROM po_vendors vend
WHERE vend.vendor_id = p_vendor_id
AND p_ei_date BETWEEN nvl(vend.start_date_active,p_ei_date) AND
nvl( vend.end_date_active, trunc(sysdate) ) ;
SELECT
system_linkage_function
,start_date_active
,end_date_active
INTO
G_EXP_TYPE_SYS_LINK
,G_EXP_TYPE_START_DATE
,G_EXP_TYPE_END_DATE
FROM pa_expend_typ_sys_links
WHERE system_linkage_function = X_system_linkage
AND expenditure_type = X_etype ;
SELECT count(1) INTO l_tc_count
From pa_transaction_controls
WHERE project_id = p_project_id
AND task_id = p_task_id;
SELECT count(1) INTO l_tc_count
From pa_transaction_controls
WHERE project_id = p_project_id
AND task_id is NULL; --Added for the bug 12915555
SELECT 1 INTO TC_EXISTS
From pa_transaction_controls
WHERE project_id = p_project_id
AND task_id = p_task_id
AND (expenditure_category IS NOT NULL OR expenditure_type IS NOT NULL)
AND person_id IS NULL
AND non_labor_resource IS NULL
AND ROWNUM =1;
SELECT 1 INTO TC_EXISTS
FROM pa_transaction_controls
WHERE project_id = p_project_id
AND task_id is NULL
AND (expenditure_category IS NOT NULL OR expenditure_type IS NOT NULL)
AND person_id IS NULL
AND non_labor_resource IS NULL
AND ROWNUM =1;
SELECT 1 INTO TC_EXISTS
FROM pa_transaction_controls
WHERE project_id = p_project_id
AND task_id = p_task_id
-- Commented for bug 4585740
-- AND (expenditure_category IS NOT NULL OR expenditure_type IS NOT NULL)
AND (person_id IS NULL OR x_person_id is NOT NULL) -- Modified for bug 4585740
AND ROWNUM =1;
SELECT 1 INTO TC_EXISTS
FROM pa_transaction_controls
WHERE project_id = p_project_id
AND task_id is NULL
-- Commented for bug 4585740
-- AND (expenditure_category IS NOT NULL OR expenditure_type IS NOT NULL)
AND (person_id IS NULL OR x_person_id is NOT NULL) -- Modified for bug 4585740
AND ROWNUM =1;
SELECT count(1)
INTO dummy
FROM dual
WHERE EXISTS
( SELECT 1
FROM pa_expend_typ_sys_links etsl
, pa_expenditure_types et
WHERE etsl.expenditure_type = et.expenditure_type
AND etsl.expenditure_type = X_expenditure_type
AND etsl.system_linkage_function = 'VI'
);
select BILL_LABOR_ACCRUAL into X_billable_flag from PA_PROJECTS WHERE project_id = X_project_id;
select count(*) into l_task_count
from pa_tasks
where project_id = X_project_id
and task_id = X_task_id;
select proj_element_id into l_task_id
from pa_alternate_tasks
where alt_task_id = X_task_id;
/* Modified the following select statement for bug 3620355
select to_number(SUBSTR(USERENV('CLIENT_INFO'),1,10)) into l_prvdr_org_id from dual; */
select to_number(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' '
,NULL ,SUBSTRB(USERENV('CLIENT_INFO'),1,10))) into l_prvdr_org_id from dual;
SELECT org_id
INTO l_prvdr_org_id
FROM ap_invoices_all
WHERE invoice_id = pa_acc_gen_wf_pkg.g_invoice_id;
select p.CURRENT_EMPLOYEE_FLAG , p.CURRENT_NPW_FLAG
into l_CURRENT_EMPLOYEE_FLAG , l_CURRENT_NPW_FLAG
from per_all_people_f p
where p.person_id = x_person_id
and trunc( x_ei_date) between trunc(p.effective_start_date) and trunc( p.effective_end_date) /* Added trunc() for bug 16451280 */
and ((p.current_employee_flag = 'Y') OR (p.current_npw_flag = 'Y')); -- added for bug 7395534
select p.CURRENT_EMPLOYEE_FLAG , p.CURRENT_NPW_FLAG
into l_CURRENT_EMPLOYEE_FLAG , l_CURRENT_NPW_FLAG
from per_all_people_f p
where p.person_id = x_person_id
and x_ei_date between p.effective_start_date and l_final_process_date
and nvl(p.effective_end_date,l_final_process_date) >= l_final_process_date; --added for 11933246 */
/*12737060 select distinct p.CURRENT_EMPLOYEE_FLAG , p.CURRENT_NPW_FLAG
into l_CURRENT_EMPLOYEE_FLAG , l_CURRENT_NPW_FLAG
from per_all_people_f p, per_periods_of_service pos
where p.person_id = x_person_id
and p.person_id = pos.person_id
and p.effective_start_date = pos.date_start
and x_ei_date between pos.date_start and
/*12737060 nvl(nvl(pos.final_process_date, pos.actual_termination_date),to_date('4712/12/31','YYYY/MM/DD'));
select distinct p.CURRENT_EMPLOYEE_FLAG ,
p.CURRENT_NPW_FLAG
into l_CURRENT_EMPLOYEE_FLAG , l_CURRENT_NPW_FLAG
from per_all_people_f p, per_periods_of_service pos
where p.person_id = x_person_id
and p.person_id = pos.person_id
and p.effective_start_date = pos.date_start
and x_ei_date between pos.date_start and
nvl(pos.final_process_date,to_date('31-12-4712','DD-MM-YYYY'));
select distinct p.CURRENT_EMPLOYEE_FLAG ,
p.CURRENT_NPW_FLAG
into l_CURRENT_EMPLOYEE_FLAG , l_CURRENT_NPW_FLAG
from per_all_people_f p, per_periods_of_placement pop
where p.person_id = x_person_id
and p.person_id = pop.person_id
and p.effective_start_date = pop.date_start
and x_ei_date between pop.date_start and
nvl(pop.final_process_date,to_date('31-12-4712','DD-MM-YYYY'));
select distinct p.CURRENT_EMPLOYEE_FLAG , p.CURRENT_NPW_FLAG
into l_CURRENT_EMPLOYEE_FLAG , l_CURRENT_NPW_FLAG
from per_all_people_f p, per_periods_of_service pos
where p.person_id = x_person_id
and p.person_id = pos.person_id
and p.effective_start_date = pos.date_start
and x_ei_date between pos.date_start and
nvl(nvl(pos.final_process_date,
pos.actual_termination_date),to_date('4712/12/31','YYYY/MM/DD'));
select distinct p.CURRENT_EMPLOYEE_FLAG ,
p.CURRENT_NPW_FLAG
into l_CURRENT_EMPLOYEE_FLAG , l_CURRENT_NPW_FLAG
from per_all_people_f p, per_periods_of_placement pop
where p.person_id = x_person_id
and p.person_id = pop.person_id
and p.effective_start_date = pop.date_start
and x_ei_date between pop.date_start and
nvl(nvl(pop.final_process_date,
pop.actual_termination_date),to_date('4712/12/31','YYYY/MM/DD'));
to avoid the billable_flag being NULL when inserting the BTC lines in Create and dstribute burden process */
ElsIf (p_sys_link_function in ('USG', 'PJ', 'VI', 'WIP', 'INV') and x_person_id is NULL) Then
If tc.chargeable_flag = 'N' Then
Print_Message('Stage:PATC:10.12.004.1e');
select null
from per_periods_of_service
where person_id = p_person_id
and p_ei_date between date_start and nvl(actual_termination_date, p_ei_date);
select actual_termination_date into l_actual_termination_date from (
select actual_termination_date
from per_periods_of_service
where person_id = p_person_id
and actual_termination_date < p_ei_date
order by actual_termination_date desc)
where rownum = 1;
select null
from per_periods_of_placement
where person_id = p_person_id
and p_ei_date between date_start and nvl(actual_termination_date, p_ei_date);
select actual_termination_date into l_actual_termination_date from (
select actual_termination_date
from per_periods_of_placement
where person_id = p_person_id
and actual_termination_date < p_ei_date
order by actual_termination_date desc)
where rownum = 1;