[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT org_information7 template
,org_information8 chunk_size
FROM hr_organization_information_v
WHERE organization_id = p_business_group_id
AND org_information_context= 'PER_IN_PRINTED_PAYSLIP';
UPDATE fnd_concurrent_requests
SET output_file_type = 'XML'
WHERE request_id = p_request_id;
SELECT substr(p.product_version,1,2) INTO l_product_release
FROM fnd_application a, fnd_application_tl t, fnd_product_installations p
WHERE a.application_id = p.application_id
AND a.application_id = t.application_id
AND t.language = Userenv ('LANG')
AND Substr (a.application_short_name, 1, 5) = 'PAY';
SELECT pad.address_line1
, pad.address_line2
, pad.address_line3
, pad.add_information13
, pad.add_information14
, hr_general.decode_lookup('IN_STATES',pad.add_information15)
, hr_general.decode_lookup('PER_US_COUNTRY_CODE',pad.country)
, pad.postal_code
FROM per_addresses pad
WHERE pad.address_id = p_address_id;
SELECT l_location_address1 || DECODE(l_location_address1,NULL,NULL,',' || fnd_global.local_chr(10)) ||
l_location_address2 || DECODE(l_location_address2,NULL,NULL,',' || fnd_global.local_chr(10)) ||
l_location_address3 || DECODE(l_location_address3,NULL,NULL,',' || fnd_global.local_chr(10)) ||
l_location_address4 || DECODE(l_location_address4,NULL,NULL,',' || fnd_global.local_chr(10)) ||
l_location_city || DECODE(l_location_city ,NULL,NULL,',' || fnd_global.local_chr(10)) ||
l_location_state || DECODE(l_location_state ,NULL,NULL,',' || fnd_global.local_chr(10)) ||
l_location_zipcode || DECODE(l_location_zipcode ,NULL,NULL,',' || fnd_global.local_chr(10)) ||
l_location_country
INTO l_details
FROM DUAL;
SELECT pa.address_id
FROM per_addresses pa
WHERE pa.person_id = p_person_id
AND pa.address_type = DECODE(pa.address_type,'IN_P','IN_P','IN_C')
AND TO_DATE(p_date) BETWEEN pa.date_from AND nvl(pa.date_to,to_date('31-12-4712','DD-MM-YYYY'))
ORDER BY address_type DESC;
SELECT pai.action_information1
FROM pay_action_information pai
WHERE action_information_category = 'IN_EMPLOYEE_DETAILS'
AND action_context_id = p_assign_action_id;
SELECT DECODE(element_classification
,'Earnings', element_reporting_name
,'Paid Monetary Perquisite', SUBSTR(element_reporting_name, 0,
LENGTH(element_reporting_name) - 8)) ename
,current_amount amt
FROM pay_apac_payslip_elements_v
WHERE action_context_id = p_action_context_id
AND (element_classification = p_classification_name
OR element_classification = 'Paid Monetary Perquisite');
SELECT element_reporting_name ename
,current_amount amt
FROM pay_apac_payslip_elements_v
WHERE action_context_id = p_action_context_id
AND element_classification = p_classification_name;
SELECT sum(current_amount) amt
FROM pay_apac_payslip_elements_v
WHERE action_context_id = p_action_context_id
AND element_classification = p_classification_name;
SELECT narrative bname
,ytd_amount ytd
FROM pay_apac_payslip_balances_v
WHERE action_context_id = p_action_context_id;
SELECT narrative
,amount
FROM pay_emea_usr_ele_action_info_v
WHERE action_context_id = p_action_context_id;
SELECT accrual_plan_name plan_name
,uom
,balance
FROM pay_apac_payslip_accruals_v
WHERE action_context_id = p_action_context_id;
SELECT absence_type absence_name
,start_date
,end_date
,absence_value
FROM pay_apac_payslip_absences_v
WHERE action_context_id = p_action_context_id;
SELECT pai.action_information6 value
FROM pay_assignment_actions paa, pay_action_information pai
WHERE paa.assignment_action_id = p_action_context_id
AND paa.payroll_action_id = pai.action_context_id
AND pai.action_information_category = 'EMPLOYEE OTHER INFORMATION'
AND pai.action_information2 = 'MESG'
AND pai.jurisdiction_code IS NOT NULL
UNION
SELECT pai.action_information6 value
FROM pay_action_information pai,pay_assignment_actions paa
WHERE paa.assignment_action_id = p_action_context_id
AND pai.action_information_category = 'EMPLOYEE OTHER INFORMATION'
AND pai.action_information2 = 'MESG'
AND paa.payroll_action_id = pai.action_context_id
AND paa.assignment_id = nvl(pai.assignment_id,paa.assignment_id)
AND EXISTS
(SELECT ppa1.pay_advice_message
FROM pay_assignment_actions paa1,pay_action_interlocks intl,pay_payroll_actions ppa1
WHERE intl.locking_action_id = paa.assignment_action_id
AND intl.locked_action_id = paa1.assignment_action_id
AND paa1.payroll_action_id = ppa1.payroll_action_id
AND ppa1.pay_advice_message IS NOT NULL
AND ppa1.action_type IN('R','Q'));
SELECT narrative
,value
FROM pay_apac_bals_action_info_v
WHERE action_context_id = p_action_context_id;
SELECT org_payment_method_name payment_method
,segment1 bank_name
,segment3 account_number
,value
FROM pay_emp_net_dist_action_info_v pendv
WHERE pendv.action_context_id = p_action_context_id;
l_stmt := ' SELECT piaav.assignment_action_id action_context_id
,peaiv.action_information10 employee_number
,peaiv.action_information14 assignment_number
,peaiv.action_information18 gre_name
,pay_us_employee_payslip_web.format_to_date(peaiv.action_information13) dob
,pay_us_employee_payslip_web.format_to_date(peaiv.action_information11) joining_date
,peaiv.action_information8 ptn
,peaiv.action_information24 pf_number
,peaiv.action_information6 esi_number
,peaiv.action_information1 emp_name
,pay_in_soe.get_emp_email(peaiv.action_context_id) email_addr
,pay_in_soe.get_emp_address(paf.person_id,:end_date) emp_addr
,peaiv.action_information23 pay_month
,peaiv.action_information30 er_location
,peaiv.action_information17 job
,peaiv.action_information19 position
,peaiv.action_information7 grade
,peaiv.action_information25 pan
,peaiv.action_information27 superannuation
,peaiv.action_information1 full_name
,peaiv.action_information30 location_name
,peaiv.action_information15 organization_name
FROM pay_in_arch_actions_v piaav
,pay_action_information peaiv
,pay_assignment_actions paa
,per_all_assignments_f paf
WHERE piaav.business_group_id = :bg_id
AND piaav.business_group_id = paf.business_group_id
AND piaav.effective_date BETWEEN :start_date
AND :end_date
AND ( :payroll_id = -1 OR :payroll_id = piaav.payroll_id)
AND ( :cons_set_id = -1 OR :cons_set_id =
piaav.consolidation_set_id)
AND DECODE (:assg_set_id,-1,''Y'',
DECODE (hr_assignment_set.ASSIGNMENT_IN_SET(:assg_set_id,paf.assignment_id),''Y'',''Y'',''N'')) = ''Y''
AND ( :person_id = -1 OR paf.person_id = :person_id)
AND paf.assignment_id = paa.assignment_id
AND peaiv.action_context_id = piaav.assignment_action_id
AND peaiv.action_context_type = ''AAP''
AND peaiv.action_information_category = ''EMPLOYEE DETAILS''
AND paa.assignment_action_id = peaiv.action_context_id
AND (TO_CHAR(paf.effective_start_date,''Month-YYYY'')=to_char(:end_date,''Month-YYYY'')
OR TO_CHAR(paf.effective_end_date,''Month-YYYY'')=to_char(:end_date,''Month-YYYY'')
OR :end_date between paf.effective_start_date and paf.effective_end_date)';
l_stmt := ' SELECT piaav.assignment_action_id action_context_id
,peaiv.action_information10 employee_number
,peaiv.action_information14 assignment_number
,peaiv.action_information18 gre_name
,pay_us_employee_payslip_web.format_to_date(peaiv.action_information13) dob
,pay_us_employee_payslip_web.format_to_date(peaiv.action_information11) joining_date
,peaiv.action_information8 ptn
,peaiv.action_information24 pf_number
,peaiv.action_information6 esi_number
,peaiv.action_information1 emp_name
,pay_in_soe.get_emp_email(peaiv.action_context_id) email_addr
,pay_in_soe.get_emp_address(paf.person_id,:end_date) emp_addr
,peaiv.action_information23 pay_month
,peaiv.action_information30 er_location
,peaiv.action_information17 job
,peaiv.action_information19 position
,peaiv.action_information7 grade
,peaiv.action_information25 pan
,peaiv.action_information27 superannuation
,peaiv.action_information1 full_name
,peaiv.action_information30 location_name
,peaiv.action_information15 organization_name
FROM pay_in_arch_actions_v piaav
,pay_action_information peaiv
,pay_assignment_actions paa
,per_all_assignments_f paf
WHERE piaav.business_group_id = :bg_id
AND piaav.business_group_id = paf.business_group_id
AND piaav.effective_date BETWEEN :start_date
AND :end_date
AND ( :payroll_id = -1 OR :payroll_id = piaav.payroll_id)
AND ( :cons_set_id = -1 OR :cons_set_id =
piaav.consolidation_set_id)
AND DECODE (:assg_set_id,-1,''Y'',
DECODE (hr_assignment_set.ASSIGNMENT_IN_SET(:assg_set_id,paf.assignment_id),''Y'',''Y'',''N'')) = ''Y''
AND ( :person_id = -1 OR paf.person_id = :person_id)
AND paf.assignment_id = paa.assignment_id
AND peaiv.action_context_id = piaav.assignment_action_id
AND EXISTS (SELECT 1
FROM pay_action_information pai
WHERE pai.action_information_category = ''IN_EMPLOYEE_DETAILS''
AND pai.action_context_id = peaiv.action_context_id
AND pai.action_information1 IS NOT NULL)
AND peaiv.action_context_type = ''AAP''
AND peaiv.action_information_category = ''EMPLOYEE DETAILS''
AND paa.assignment_action_id = peaiv.action_context_id
AND (TO_CHAR(paf.effective_start_date,''Month-YYYY'')=to_char(:end_date,''Month-YYYY'')
OR TO_CHAR(paf.effective_end_date,''Month-YYYY'')=to_char(:end_date,''Month-YYYY'')
OR :end_date between paf.effective_start_date and paf.effective_end_date)';