[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select descriptive_flex_context_name into l_context_name
from fnd_descr_flex_contexts_vl
where descriptive_flexfield_name = 'OTC Information Types'
and descriptive_flex_context_code = p_context_code
and application_id = 809;
select 'Y' into l_dummy
from fnd_descr_flex_contexts
where application_id = 809
and descriptive_flexfield_name = 'OTC Information Types'
and enabled_flag = 'Y'
and descriptive_flex_context_code like p_context_prefix||'%GLOBAL%';
select d.default_context_field_name
from fnd_descriptive_flexs d
,fnd_application a
,fnd_product_installations z
where d.application_id = a.application_id
and z.application_id = a.application_id
and a.application_short_name = 'PA'
and z.status = 'I'
and d.descriptive_flexfield_name = 'PA_EXPENDITURE_ITEMS_DESC_FLEX';
select atc.component_name,
fdfcu.application_column_name
from fnd_descr_flex_column_usages fdfcu,
hxc_alias_type_components atc,
hxc_alias_types aty,
hxc_alias_definitions ad,
hxc_alias_values av
where av.alias_value_id = p_value_id
and av.alias_definition_id = ad.alias_definition_id
and ad.alias_type_id = aty.alias_type_id
and aty.alias_type_id = atc.alias_type_id
and atc.component_name in ('EXPENDITURE_TYPE','SYSTEM_LINKAGE_FUNCTION')
and atc.component_name = fdfcu.end_user_column_name
and fdfcu.application_id = 809
and fdfcu.descriptive_flexfield_name = 'OTC Aliases'
and fdfcu.descriptive_flex_context_code = aty.reference_object
and aty.alias_type = 'OTL_ALT_DDF';
select *
from hxc_alias_values av
where av.alias_value_id = p_value_id;
select papf.full_name
,paa.assignment_number
,to_char(papf.original_date_of_hire,'YYYY/MM/DD')
,paa.assignment_id
from per_all_people_f papf
,per_all_assignments_f paa
where paa.person_id = papf.person_id
and p_d between paa.effective_start_date and paa.effective_end_date
and p_d between papf.effective_start_date and papf.effective_end_date
and paa.primary_flag = 'Y'
and paa.assignment_type = 'E'
and papf.person_id = p_person_id;
select assignment_number
,assignment_id
,effective_start_date
,min(abs(effective_start_date-p_d))
from per_all_assignments_f
where person_id = p_person_id
and primary_flag = 'Y'
and assignment_type = 'E'
group by assignment_number, assignment_id, effective_start_date;
select full_name,to_char(original_date_of_hire,'YYYY/MM/DD')
from per_all_people_f
where person_id = p_person_id
and effective_start_date <= p_d
and effective_end_date >= p_d;
select rp.period_type
,rp.duration_in_days
,p.number_per_fiscal_year
,substr(fnd_date.date_to_canonical(rp.start_date),1,50) start_date
from hxc_recurring_periods rp
,per_time_period_types p
where p.period_type (+) = rp.period_type
and rp.recurring_period_id = p_recurring_period_id;
SELECT min(paa.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F paa,
per_assignment_status_types typ
WHERE paa.PERSON_ID = p_person_id
AND paa.ASSIGNMENT_TYPE = 'E'
AND paa.PRIMARY_FLAG = 'Y'
AND paa.ASSIGNMENT_STATUS_TYPE_ID = typ.ASSIGNMENT_STATUS_TYPE_ID
AND typ.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN','ACTIVE_CWK');
SELECT max(paa.EFFECTIVE_END_DATE)
FROM PER_ALL_ASSIGNMENTS_F paa,
per_assignment_status_types typ
WHERE paa.PERSON_ID = p_person_id
AND paa.ASSIGNMENT_TYPE = 'E'
AND paa.PRIMARY_FLAG = 'Y'
AND paa.ASSIGNMENT_STATUS_TYPE_ID = typ.ASSIGNMENT_STATUS_TYPE_ID
AND typ.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN','ACTIVE_CWK');
select start_time,stop_time
from hxc_time_building_blocks
where time_building_block_id = p_id
and date_to = hr_general.end_of_time;
select hxc_transactions_s.nextval from dual;
select hxc_transaction_details_s.nextval from dual;
insert into hxc_transactions
(transaction_id
,transaction_date
,type
,transaction_process_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,status
) values
(l_transaction_id
,p_effective_date
,p_transaction_type
,p_transaction_process_id
,null
,sysdate
,null
,sysdate
,null
,p_overall_status
);
insert into hxc_transaction_details
(transaction_detail_id
,time_building_block_id
,transaction_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,time_building_block_ovn
,status
,exception_description
) values
(l_transaction_detail_id
,p_transaction_tab(l_tx_ind).tbb_id
,l_transaction_id
,null
,sysdate
,null
,sysdate
,null
,p_transaction_tab(l_tx_ind).tbb_ovn
,p_transaction_tab(l_tx_ind).status
,p_transaction_tab(l_tx_ind).exception_desc
);
select havt.alias_value_name Display_Value,
hav.attribute1 element_id,
hav.alias_value_id alias_value_id
from hxc_alias_values hav,
hxc_alias_values_tl havt,
hxc_alias_definitions had
where
--hav.attribute_category='PAYROLL_ELEMENTS'
hav.enabled_flag='Y'
and had.alias_definition_id = hav.alias_definition_id
and had.alias_definition_id = p_alias_definition_id
and havt.language = USERENV('LANG')
and havt.alias_value_id = hav.alias_value_id
and hav.date_from <= p_end_date
and nvl(hav.date_to,hr_general.end_of_time) >=p_start_date
and exists (
select 'x'
from PAY_ELEMENT_TYPES_F ELEMENT,
PAY_ELEMENT_CLASSIFICATIONS CLASSIFICATION,
BEN_BENEFIT_CLASSIFICATIONS BENEFIT,
PAY_ELEMENT_LINKS_F LINK,
PER_ALL_ASSIGNMENTS_F ASGT,
PER_PERIODS_OF_SERVICE SERVICE_PERIOD
WHERE
asgt.person_id = p_person_id and
to_number(hav.attribute1) = ELEMENT.element_type_id
AND ELEMENT.EFFECTIVE_START_DATE <= p_end_date
AND ELEMENT.EFFECTIVE_END_DATE >= p_start_date
AND ASGT.BUSINESS_GROUP_ID = LINK.BUSINESS_GROUP_ID
AND ELEMENT.ELEMENT_TYPE_ID = LINK.ELEMENT_TYPE_ID
AND ELEMENT.BENEFIT_CLASSIFICATION_ID = BENEFIT.BENEFIT_CLASSIFICATION_ID (+)
AND ELEMENT.CLASSIFICATION_ID = CLASSIFICATION.CLASSIFICATION_ID
AND SERVICE_PERIOD.PERIOD_OF_SERVICE_ID = ASGT.PERIOD_OF_SERVICE_ID
AND ASGT.EFFECTIVE_START_DATE <= p_end_date
AND ASGT.EFFECTIVE_END_DATE >= p_start_date
AND LINK.EFFECTIVE_START_DATE <= p_end_date
AND LINK.EFFECTIVE_END_DATE >= p_start_date
AND ELEMENT.INDIRECT_ONLY_FLAG = 'N'
AND UPPER (ELEMENT.ELEMENT_NAME) <> 'VERTEX'
AND not exists
(select 1
from HR_ORGANIZATION_INFORMATION HOI,
PAY_LEGISLATION_RULES PLR
WHERE plr.rule_type in
('ADVANCE','ADVANCE_INDICATOR','ADV_DEDUCTION',
'PAY_ADVANCE_INDICATOR','ADV_CLEARUP','DEFER_PAY')
AND plr.rule_mode = to_char(element.element_type_id)
AND plr.legislation_code = hoi.org_information9
AND HOI.ORGANIZATION_ID = ASGT.ORGANIZATION_ID
)
AND ELEMENT.CLOSED_FOR_ENTRY_FLAG = 'N'
AND ELEMENT.ADJUSTMENT_ONLY_FLAG = 'N'
AND ((LINK.PAYROLL_ID IS NOT NULL AND LINK.PAYROLL_ID = ASGT.PAYROLL_ID)
OR (LINK.LINK_TO_ALL_PAYROLLS_FLAG = 'Y' AND ASGT.PAYROLL_ID IS NOT NULL)
OR (LINK.PAYROLL_ID IS NULL AND LINK.LINK_TO_ALL_PAYROLLS_FLAG = 'N'))
AND (LINK.ORGANIZATION_ID = ASGT.ORGANIZATION_ID OR LINK.ORGANIZATION_ID IS NULL)
AND (LINK.POSITION_ID = ASGT.POSITION_ID OR LINK.POSITION_ID IS NULL)
AND (LINK.JOB_ID = ASGT.JOB_ID OR LINK.JOB_ID IS NULL)
AND (LINK.GRADE_ID = ASGT.GRADE_ID OR LINK.GRADE_ID IS NULL)
AND (LINK.LOCATION_ID = ASGT.LOCATION_ID OR LINK.LOCATION_ID IS NULL)
AND (LINK.PAY_BASIS_ID = ASGT.PAY_BASIS_ID OR LINK.PAY_BASIS_ID IS NULL)
AND (LINK.EMPLOYMENT_CATEGORY = ASGT.EMPLOYMENT_CATEGORY OR
LINK.EMPLOYMENT_CATEGORY IS NULL)
AND (LINK.PEOPLE_GROUP_ID IS NULL
OR EXISTS (
SELECT 1 FROM PAY_ASSIGNMENT_LINK_USAGES_F USAGE
WHERE USAGE.ASSIGNMENT_ID = ASGT.ASSIGNMENT_ID
AND USAGE.ELEMENT_LINK_ID = LINK.ELEMENT_LINK_ID
AND (USAGE.EFFECTIVE_START_DATE <= p_end_date
AND USAGE.EFFECTIVE_END_DATE >= p_start_date)))
AND (ELEMENT.PROCESSING_TYPE = 'R' OR ASGT.PAYROLL_ID IS NOT NULL)
AND (SERVICE_PERIOD.ACTUAL_TERMINATION_DATE IS NULL
OR (SERVICE_PERIOD.ACTUAL_TERMINATION_DATE IS NOT NULL
AND p_start_date <= DECODE(ELEMENT.POST_TERMINATION_RULE,
'L', NVL(SERVICE_PERIOD.LAST_STANDARD_PROCESS_DATE,hr_general.end_of_time),
'F', NVL(SERVICE_PERIOD.FINAL_PROCESS_DATE,
hr_general.end_of_time),
SERVICE_PERIOD.ACTUAL_TERMINATION_DATE))))
ORDER BY Display_Value;
l_hours_type_list.DELETE;
l_hours_type_list_ins_alg.DELETE;
select max(object_version_number)
from hxc_time_building_blocks
where time_building_block_id = p_tbb_id;
select transaction_id
from hxc_transaction_details
where time_building_block_id = p_tbb_id
and object_version_number = p_tbb_ovn;
INSERT INTO hxc_errors (
error_id
, transaction_detail_id
, time_building_block_id
, time_building_block_ovn
, time_attribute_id
, time_attribute_ovn
, message_name
, message_level
, message_field
, message_tokens
, application_short_name
, object_version_number )
VALUES (
hxc_errors_s.nextval
, l_tx_id
, p_messages(l_msg_ind).time_building_block_id
, l_tbb_ovn
, p_messages(l_msg_ind).time_attribute_id
, p_messages(l_msg_ind).time_attribute_ovn
, p_messages(l_msg_ind).message_name
, p_messages(l_msg_ind).message_level
, p_messages(l_msg_ind).message_field
, p_messages(l_msg_ind).message_tokens
, p_messages(l_msg_ind).application_short_name
, 1 );
SELECT descriptive_flex_context_code
FROM fnd_descr_flex_contexts_vl
WHERE descriptive_flex_context_name = p_name
AND descriptive_flexfield_name = 'OTC Information Types'
AND application_id = 809
AND substrB(DESCRIPTIVE_FLEX_CONTEXT_CODE,0,instr(DESCRIPTIVE_FLEX_CONTEXT_CODE,'-')-2)
=substrB(DESCRIPTIVE_FLEX_CONTEXT_name,0,instr(DESCRIPTIVE_FLEX_CONTEXT_name,'-')-2)||'C'
AND SUBSTRB(DESCRIPTION,0, LENGTH(P_MESSAGE))=P_MESSAGE;
SELECT descriptive_flex_context_name
FROM fnd_descr_flex_contexts_vl
WHERE descriptive_flex_context_code = p_code
AND descriptive_flexfield_name = 'OTC Information Types'
AND application_id = 809
AND SUBSTRB (
descriptive_flex_context_code,
0,
INSTR (descriptive_flex_context_code, '-') - 2
) = SUBSTRB (
descriptive_flex_context_name,
0,
INSTR (descriptive_flex_context_name, '-') - 2
)
|| 'C'
AND SUBSTRB (description, 0, LENGTH (p_message)) = p_message;
SELECT
havt.alias_value_name Display_Value,
hav.attribute1 element_id,
hav.alias_value_id alias_value_id
FROM
hxc_alias_values hav,
hxc_alias_values_tl havt,
hxc_alias_definitions had,
PAY_ELEMENT_TYPES_F ELEMENT
WHERE
hav.attribute1 = ELEMENT.element_type_id and
hav.enabled_flag='Y' and
had.alias_definition_id = hav.alias_definition_id and
havt.language = USERENV('LANG') and
havt.alias_value_id =hav.alias_value_id and
had.alias_definition_id = p_alias_definition_id AND
ELEMENT.EFFECTIVE_START_DATE <= sysdate AND
ELEMENT.EFFECTIVE_END_DATE >= sysdate;