[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MAX(pai.action_context_id)
FROM pay_action_information pai
WHERE pai.action_information_category ='IN_EOY_ORG'
AND pai.action_context_type = 'PA'
AND pai.Action_information1 = p_gre_id
AND pai.action_information3 = p_assessment_year;
SELECT hr_loc.address_line_1
, hr_loc.address_line_2
, hr_loc.address_line_3
, hr_loc.loc_information14
, hr_loc.loc_information15
, hr_general.decode_lookup('IN_STATES',hr_loc.loc_information16)
, hr_general.decode_lookup('PER_US_COUNTRY_CODE',hr_loc.country)
, hr_loc.postal_code
, hr_loc.loc_information16
FROM hr_locations hr_loc
WHERE location_id = p_location_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,',')||
l_location_country || DECODE(l_location_country ,NULL,NULL,',')||
l_location_zipcode
INTO l_details
FROM DUAL;
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_country
INTO l_details
FROM DUAL;
SELECT 'ER_LEGAL' er_legal
, UPPER(action_information8) er_legal_value
, 'ER_ADDRESS' er_address
, get_location_details ( TO_NUMBER(action_information7)
, 'Y') er_address_value
, 'ER_NAME' er_org
, UPPER(action_information6) er_org_value
, 'TAN' er_tan
, UPPER(action_information4) er_tan_value
, 'GIR' er_gir
, UPPER(action_information2) er_gir_value
, 'TDS_CIRCLE' er_tds
, UPPER(action_information9) er_tds_value
, 'REP_NAME' rep_name
, UPPER(Action_information11) rep_name_value
, 'REP_TITLE_NAME' rep_title_name
, Upper(Action_information12) || Upper(Action_information11) rep_title_value
, 'REP_FATHER_NAME' rep_father_name
, UPPER(Action_information14) rep_father_value
, 'REP_POSITION' rep_designation
, Action_information13 rep_designation_value
, 'PLACE' gre_place
, pay_in_eoy_reports.get_location_details ( TO_NUMBER(action_information7)
, NULL
, 'CITY') gre_place_value
FROM pay_action_information
WHERE action_context_id = p_action_context_id
AND action_information_category = 'IN_EOY_ORG'
AND action_information1 = p_gre_id
AND ROWNUM =1;
((SELECT fnd_date.canonical_to_date(hoi_challan.org_information2) Payment_date
,hoi_bank.org_information4 Bank
,hoi_challan.org_information3 Voucher_Num
,hoi_challan.org_information11 DD_Cheque_Num
FROM hr_organization_information hoi_bank
,hr_organization_information hoi_challan
WHERE hoi_bank.organization_id = p_gre_id
AND hoi_challan.organization_id = hoi_bank.organization_id
AND hoi_challan.org_information_context ='PER_IN_IT_CHALLAN_INFO'
AND hoi_bank.org_information_context = 'PER_IN_CHALLAN_BANK'
AND hoi_bank.org_information_id = hoi_challan.org_information5
AND hoi_challan.org_information12 = 'N'
AND hoi_challan.org_information1 = to_char(to_number(substr(g_assessment_year, 1,4))-1)||'-'||to_char(to_number(substr(g_assessment_year, 6,4))-1)
)
UNION ALL
(SELECT fnd_date.canonical_to_date(hoi_challan.org_information2) Payment_date
,hoi_challan.org_information5 Bank
,hoi_challan.org_information3 Voucher_Num
,hoi_challan.org_information11 DD_Cheque_Num
FROM hr_organization_information hoi_challan
WHERE hoi_challan.organization_id = p_gre_id
AND hoi_challan.org_information_context ='PER_IN_IT_CHALLAN_INFO'
AND hoi_challan.org_information12 = 'Y'
AND hoi_challan.org_information5 is null
AND hoi_challan.org_information6 is null
AND hoi_challan.org_information1 = to_char(to_number(substr(g_assessment_year, 1,4))-1)||'-'||to_char(to_number(substr(g_assessment_year, 6,4))-1)
)) ORDER BY Payment_Date;
((SELECT fnd_date.canonical_to_date(hoi_challan.org_information2) Payment_date
,hr_general.decode_lookup('IN_BANK',hoi_bank.org_information1)||','||hr_general.decode_lookup('IN_BANK_BRANCH',hoi_bank.org_information2) Bank
,hoi_challan.org_information3 Voucher_Num
FROM hr_organization_information hoi_bank
,hr_organization_information hoi_challan
WHERE hoi_bank.organization_id = p_gre_id
AND hoi_challan.organization_id = hoi_bank.organization_id
AND hoi_challan.org_information_context ='PER_IN_IT_CHALLAN_INFO'
AND hoi_bank.org_information_context = 'PER_IN_CHALLAN_BANK'
AND hoi_bank.org_information_id = hoi_challan.org_information5
AND hoi_challan.org_information12 = 'N'
AND hoi_challan.org_information1 = to_char(to_number(substr(g_assessment_year, 1,4))-1)||'-'||to_char(to_number(substr(g_assessment_year, 6,4))-1)
)
UNION ALL
(SELECT fnd_date.canonical_to_date(hoi_challan.org_information2) Payment_date
,hoi_challan.org_information5 Bank
,hoi_challan.org_information3 Voucher_Num
FROM hr_organization_information hoi_challan
WHERE hoi_challan.organization_id = p_gre_id
AND hoi_challan.org_information_context ='PER_IN_IT_CHALLAN_INFO'
AND hoi_challan.org_information12 = 'Y'
AND hoi_challan.org_information5 is null
AND hoi_challan.org_information6 is null
AND hoi_challan.org_information1 = to_char(to_number(substr(g_assessment_year, 1,4))-1)||'-'||to_char(to_number(substr(g_assessment_year, 6,4))-1)
)) ORDER BY Payment_Date;
SELECT org_information2 quarter,DECODE(org_information6,'O',' Regular','C',' Correction') Nature,
org_information4 receipt
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND org_information1 = g_assessment_year
AND organization_id = p_gre_id
ORDER BY quarter;
g_Bank_Details_tbl.DELETE;
g_Bank_Details_tbl_16aa.DELETE;
SELECT 'EID' empno_tag
, pai.action_information1 empno_value
, 'EE_DETAILS' emp_details
, UPPER(pai.action_information6
|| pai.action_information5
|| DECODE(pai.action_information9,NULL,'',fnd_global.local_chr(10))
|| pai.action_information9) emp_details_value
, 'E_F_NAME' emp_full_name
, UPPER(pai.action_information5) emp_full_value
, 'E_TITLE' emp_title
, UPPER(pai.action_information6) emp_title_value
, 'PAN' emp_pan
, DECODE(pai.action_information4,'Y','APPLIED FOR','N','',pai.action_information4) emp_pan_value
, 'E_DESG' emp_designation
, UPPER(pai.action_information9) emp_designation_value
, 'E_FAT_NAME' emp_father_name
, UPPER(pai.action_information7) emp_father_value
, 'DOB' emp_dob
, TO_CHAR(fnd_date.canonical_to_date(pai.action_information10),'DD-MM-YYYY') emp_dob_value
, 'GENDER' emp_gender
, UPPER(pai.action_information11) emp_gender_value
, 'E_INTEREST' emp_interest
, DECODE(pai.action_information12,'N','No','Y','Yes') emp_interest_value
, 'ASG_START' emp_asg_start
, TO_DATE(pai.action_information17,'DD-MM-RRRR') emp_asg_start_value
, 'ASG_END' emp_asg_end
, TO_DATE(pai.action_information18,'DD-MM-RRRR') emp_asg_end_value
, 'E_ADDRESS' emp_address
, get_address_details( pai.action_information14
, 'Y','NULL'
) emp_address_value
, 'EMP_POSTAL_CODE' emp_zipcode
, get_address_details( pai.action_information14
, 'N', 'POSTAL_CODE'
) emp_zipcode_value
, action_information20 emp_date_earned
, assignment_id emp_asg_id
, action_information15 emp_resident_status
, 'EMP_PHONE' emp_phone
, action_information16 emp_phone_value
FROM pay_action_information pai
WHERE pai.action_information_category = 'IN_EOY_PERSON'
AND pai.action_context_id = p_action_context_id
AND pai.source_id = p_source_id;
SELECT paf.payroll_id
FROM per_all_assignments_f paf
WHERE paf.assignment_id =p_assignment_id
AND p_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
SELECT DECODE(pai.action_information1,'F16 Salary Under Section 17', 1,
'F16 Value of Perquisites',2,
'F16 Profit in lieu of Salary', 3,
'F16 Gross Salary',4,
'F16 Gross Salary less Allowances',6,
'F16 Entertainment Allowance', 7,
'F16 Employment Tax',8,
'F16 Deductions under Sec 16',9,
'F16 Income Chargeable Under head Salaries',10,
'F16 Other Income',11,
'F16 Gross Total Income',12,
'F16 Total Income',13,
'F16 Tax on Total Income',14,
'F16 Surcharge',15,
'F16 Education Cess',16,
'F16 Relief under Sec 89',18,
'F16 Total Tax payable',19,
'Income Tax Deduction',20,
'F16 Balance Tax',21,
'ER Paid Tax on Non Monetary Perquisite',22,
0) sort_index,
action_information2 balance_value
FROM pay_action_information pai
WHERE action_information_category = 'IN_EOY_ASG_SAL'
AND action_context_id = p_action_context_id
AND source_id = p_source_id;
SELECT 1
FROM pay_action_information pai
WHERE action_information_category = 'IN_EOY_ASG_SAL'
AND action_context_id = p_action_context_id
AND source_id = p_source_id
AND action_information1 IN('TDS on Previous Employment',
'CESS on Previous Employment',
'SC on Previous Employment');
SELECT action_information2 balance_value
FROM pay_action_information pai
WHERE action_information_category = 'IN_EOY_ASG_SAL'
AND action_context_id = p_action_context_id
AND source_id = p_source_id
AND action_information1 = p_action_information1;
SELECT action_information1 balance_name,
action_information2 balance_value
FROM pay_action_information pai
WHERE action_information_category = 'IN_EOY_ASG_SAL'
AND action_context_id = p_action_context_id
AND source_id = p_source_id
AND action_information1 IN('Long Term Capital Gains',
'Short Term Capital Gains',
'Business and Profession Gains',
'Other Sources of Income',
'Loss From House Property')
AND action_information2 IS NOT NULL;
SELECT action_information1 Allowance_name,
action_information2 Amt,
action_information3 Std_Amt,
action_information4 Taxable_Amt,
action_information5 Std_Taxable_Amt
FROM pay_action_information
WHERE action_information_category = 'IN_EOY_ALLOW'
AND action_context_id = p_action_context_id
AND action_information1 <> 'Taxable Allowances'
AND source_id =p_source_id;
SELECT DECODE(action_information1, 'Life Insurance Premium','Life Insurance Premium',
'Deferred Annuity','Deferred Annuity',
'Senior Citizens Savings Scheme','Senior Citizens Savings Scheme',
'Five Year Post Office Time Deposit Account','Five Year Post Office Time Deposit Account',
'NABARD Bank Deposits','NABARD Bank Deposits',
'Public Provident Fund','Public Provident Fund',
'Interest on NSC','Interest on National Savings Certificate reinvested',
'House Loan Repayment', 'Principal Loan (Housing Loan) Repayment',
'Mutual Fund or UTI','Notified units of Mutual Funds/UTI',
'National Housing Bank', 'National Housing Bank Scheme',
'ULIP','Unit Linked Insurance Plan (UTI,LIC etc)',
'Notified Annuity Plan','Notified Annuity Plan',
'Notified Pension Fund','Notified Pension Fund',
'Public Sector Scheme','Public Sector Company Scheme',
'Infrastructure Bonds','Investment in Infrastructure Bonds',
'Tuition fee','Tuition Fees per children (max 2 children allowed)',
'Superannuation Fund', 'Employee Contribution to an approved superannuation fund',
'F16 Employee PF Contribution','Employee Contribution to Provident Fund',
'NSC','NSC',
'Deposits in Govt. Security','Deposits in Govt. Security',
'Notified Deposit Scheme','Notified Deposit Scheme',
'Approved Shares or Debentures','Approved Shares or Debentures',
'Approved Mutual Fund','Approved Mutual Fund',
'Fixed Deposits','Fixed Deposits',
'X')Description_Value
, action_information2 Qualifying_Value
, nvl(action_information3,action_information2) Gross_Value
FROM pay_action_information pai
WHERE action_information_category = 'IN_EOY_VIA'
AND action_context_id = p_action_context_id
AND NVL(action_information3,action_information2) > 0
AND source_id =p_source_id
ORDER BY Description_Value;
SELECT DECODE(action_information1, 'F16 Deductions Sec 80D','80D',
'F16 Deductions Sec 80DD','80DD',
'F16 Deductions Sec 80DDB','80DDB',
'F16 Deductions Sec 80E','80E',
'F16 Deductions Sec 80G','80G',
'F16 Deductions Sec 80GG','80GG',
'F16 Deductions Sec 80GGA','80GGA',
'F16 Deductions Sec 80U','80U',
'Pension Fund 80CCC','80CCC',
'Govt Pension Scheme 80CCD','80CCD',
'F16 Total Chapter VI A Deductions','TOTAL_V1A',
'X')Description_Value
, action_information2 Qualifying_Value
, nvl(action_information3,action_information2) Gross_Value
FROM pay_action_information pai
WHERE action_information_category = 'IN_EOY_VIA'
AND action_context_id = p_action_context_id
AND NVL(action_information3,action_information2) > 0
AND source_id =p_source_id
ORDER BY Description_Value;
SELECT MAX(action_information4) run_assact
FROM pay_action_information pai
WHERE pai.action_information_category ='IN_EOY_ASG_SAL'
AND pai.action_information1='Income Tax This Pay'
AND pai.action_context_id = p_action_context_id
AND pai.source_id = p_source_id
GROUP BY TRUNC(TO_DATE(Action_information3,'DD-MM_RRRR'),'MM')
ORDER BY TRUNC(TO_DATE(Action_information3,'DD-MM_RRRR'),'MM');
SELECT action_information2 tds_value
,DECODE(TO_CHAR(TO_DATE(Action_information3,'DD-MM-RRRR'),'MM'),
'04',1,
'05',2,
'06',3,
'07',4,
'08',5,
'09',6,
'10',7,
'11',8,
'12',9,
'01',10,
'02',11,
'03',12)sort_index
FROM pay_action_information
WHERE action_context_id = p_action_context_id
AND source_id =p_source_id
AND action_information_category = 'IN_EOY_ASG_SAL'
AND action_information1= p_information
AND action_information4 = p_max_run_action_id;
SELECT input.name name
, value.screen_entry_value value
, entries.element_entry_id
FROM per_assignments_f assign
,pay_element_entries_f entries
,pay_element_types_f type
,pay_input_values_f input
,pay_element_entry_values_f value
,pay_element_links_f links
WHERE assign.assignment_id =
(SELECT assignment_id
FROM pay_assignment_actions
WHERE assignment_action_id = p_action_context_id)
AND links.element_type_id = type.element_type_id
AND links.element_type_id = entries.element_type_id
AND links.element_link_id = entries.element_link_id
AND type.element_name = 'Income Tax Challan Information'
AND type.element_type_id = entries.element_type_id
AND entries.assignment_id = assign.assignment_id
AND type.element_type_id = input.element_type_id
AND value.element_entry_id = entries.element_entry_id
AND value.input_value_id = input.input_value_id
AND input.name in ('Amount Deposited'
, 'Education Cess Deducted'
, 'Income Tax Deducted'
, 'Surcharge Deducted'
, 'Challan or Voucher Number')
AND type.legislation_code ='IN'
AND entries.effective_start_date BETWEEN assign.effective_start_date AND assign.effective_end_date
AND entries.effective_start_date BETWEEN g_tax_start_date AND g_tax_end_date
AND entries.effective_start_date BETWEEN type.effective_start_date AND type.effective_end_date
AND entries.effective_start_date BETWEEN input.effective_start_date AND input.effective_end_date
AND entries.effective_start_date BETWEEN links.effective_start_date AND links.effective_end_date
AND value.effective_start_date BETWEEN g_tax_start_date AND g_tax_end_date
ORDER BY entries.element_entry_id
, input.name;
SELECT input.name name
, value.screen_entry_value value
, entries.element_entry_id
FROM per_assignments_f assign
,pay_element_entries_f entries
,pay_element_types_f type
,pay_input_values_f input
,pay_element_entry_values_f value
,pay_element_links_f links
WHERE assign.assignment_id =
(SELECT assignment_id
FROM pay_assignment_actions
WHERE assignment_action_id = p_action_context_id)
AND links.element_type_id = type.element_type_id
AND links.element_type_id = entries.element_type_id
AND links.element_link_id = entries.element_link_id
AND type.element_name = 'Income Tax Challan Information'
AND type.element_type_id = entries.element_type_id
AND entries.assignment_id = assign.assignment_id
AND type.element_type_id = input.element_type_id
AND value.element_entry_id = entries.element_entry_id
AND value.input_value_id = input.input_value_id
AND input.name in ('Amount Deposited'
, 'Challan or Voucher Number')
AND type.legislation_code ='IN'
AND entries.effective_start_date BETWEEN assign.effective_start_date AND assign.effective_end_date
AND entries.effective_start_date BETWEEN g_tax_start_date AND g_tax_end_date
AND entries.effective_start_date BETWEEN type.effective_start_date AND type.effective_end_date
AND entries.effective_start_date BETWEEN input.effective_start_date AND input.effective_end_date
AND entries.effective_start_date BETWEEN links.effective_start_date AND links.effective_end_date
AND value.effective_start_date BETWEEN g_tax_start_date AND g_tax_end_date
ORDER BY entries.element_entry_id
, input.name;
select lower(fnd_global.local_chr(r+64)) INTO l_seq
FROM
( SELECT LEVEL r
FROM dual
CONNECT BY LEVEL <= 26 )
WHERE r+64 = 72 + l_count;
t_table_1.DELETE;
g_emp_challan_details_tbl.DELETE;
g_emp_challan_details_tbl_16aa.DELETE;
SELECT DECODE(action_information1, 'Company Accommodation', 1
, 'Motor Car Perquisite',2
, 'Domestic Servant',3
, 'Gas / Water / Electricity', 4
, 'Loan at Concessional Rate',5
, 'Travel / Tour / Accommodation',7
, 'Leave Travel Concession',7
, 'Lunch Perquisite',8
, 'Free Education', 9
, 'Gift Voucher', 10
, 'Credit Cards', 11
, 'Club Expenditure', 12
, 'Company Movable Assets',13
, 'Transfer of Company Assets',14
, 'Employer Paid Tax',15
, 'Shares',16
, 20) sort_index
, SUM(NVL(action_information2,0)) value1
, SUM(NVL(action_information3,0)) value2
FROM pay_action_information
WHERE action_information_category = 'IN_EOY_PERQ'
AND action_context_id = p_action_context_id
AND source_id =p_source_id
GROUP BY DECODE(action_information1, 'Company Accommodation', 1
, 'Motor Car Perquisite',2
, 'Domestic Servant',3
, 'Gas / Water / Electricity', 4
, 'Loan at Concessional Rate',5
, 'Travel / Tour / Accommodation',7
, 'Leave Travel Concession',7
, 'Lunch Perquisite',8
, 'Free Education', 9
, 'Gift Voucher', 10
, 'Credit Cards', 11
, 'Club Expenditure', 12
, 'Company Movable Assets',13
, 'Transfer of Company Assets',14
, 'Employer Paid Tax',15
, 'Shares',16
, 20) ;
SELECT NVL(action_information2,0) value1
FROM pay_action_information
WHERE action_information_category = 'IN_EOY_PERQ'
AND action_context_id = p_action_context_id
AND source_id =p_source_id
AND action_information1 = p_action_information1;
SELECT hou.organization_id orgid
FROM hr_all_organization_units hou
, hr_organization_information hoi
WHERE hou.organization_id = hoi.organization_id
AND hoi.org_information_context = 'CLASS'
AND hoi.org_information1 = 'HR_LEGAL'
AND hoi.org_information2 = 'Y'
AND hou.organization_id = NVL(p_gre_id,hou.organization_id)
AND hou.business_group_id = p_business_group_id
AND EXISTS (SELECT 1
FROM pay_action_information pai,
pay_payroll_actions ppa
WHERE pai.action_information_category = 'IN_EOY_ORG'
AND pai.action_information1 = hou.organization_id
AND pai.action_information3 = p_assessment_year
AND pai.action_context_type ='PA'
AND pai.action_context_id = ppa.payroll_action_id
AND ppa.report_qualifier ='IN'
AND ppa.report_type ='IN_EOY_ARCHIVE'
AND ppa.report_category ='ARCHIVE'
AND ROWNUM < 2)
ORDER BY hou.name;
SELECT MAX(pai.action_context_id) action_context_id
, pai.action_information17 start_date
, pai.action_information1 employee_number
FROM pay_action_information pai
,per_assignments_f asg
WHERE pai.action_information_category = 'IN_EOY_PERSON'
AND asg.assignment_id = pai.assignment_id
AND asg.business_group_id = p_business_group_id
AND pai.action_information3 = p_gre_id
AND pai.action_information2 = p_assessment_year
AND pai.action_information1 LIKE NVL(p_employee_number,'%')
GROUP BY pai.action_information1,pai.action_information17
ORDER BY LENGTH(pai.action_information1), pai.action_information1;
SELECT pai.source_id Payroll_run_action_id
,pai.action_information18 end_date
FROM pay_action_information pai
WHERE pai.action_information_category ='IN_EOY_PERSON'
AND pai.action_information17 = p_start_date
AND pai.action_information1 = p_employee_number
AND pai.action_information2 = p_assessment_year
AND pai.action_information3 = p_gre_id
AND pai.action_context_id = p_action_context_id
AND EXISTS (SELECT 1
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE pai.source_id = paa.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.business_group_id = p_business_group_id );
SELECT global_value
FROM ff_globals_f
WHERE global_name =p_global_name
AND legislation_code='IN'
AND g_tax_end_date BETWEEN effective_start_date and effective_end_date;