[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 = to_char(p_gre_id) /* Bug 11698592 */
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 TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) run_asg_action_id
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_assignments_f paf
WHERE paf.assignment_id = paa.assignment_id
AND paf.assignment_id = p_assignment_id
AND paa.tax_unit_id = p_tax_unit_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN('R','Q','I','B')
AND ppa.payroll_id = paf.payroll_id
AND ppa.action_status ='C'
AND ppa.effective_date between p_qtr_start and p_qtr_end
AND paa.source_action_id IS NULL
AND (1 = DECODE(ppa.action_type,'I',1,0)
OR EXISTS (SELECT ''
FROM pay_action_interlocks intk,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
WHERE intk.locked_action_id = paa.assignment_Action_id
AND intk.locking_action_id = paa1.assignment_action_id
AND paa1.payroll_action_id =ppa1.payroll_action_id
AND paa1.assignment_id = p_assignment_id
AND ppa1.action_type in('P','U')
AND ppa.action_type in('R','Q','B')
AND ppa1.action_status ='C'
AND ppa1.effective_date BETWEEN p_qtr_start and p_qtr_end
AND ROWNUM =1 ));
SELECT pdb.defined_balance_id balance_id
,pbt.balance_name balance_name
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pbt.balance_name IN('Education Cess This Pay'
,'Income Tax This Pay'
,'Sec and HE Cess This Pay'
,'TDS on Direct Payments'
)
AND pbd.dimension_name='_ASG_LE_YTD'
AND pbt.legislation_code = 'IN'
AND pbd.legislation_code = 'IN'
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id;
g_bal_name_tab.DELETE;
g_balance_value_tab.DELETE;
g_result_table.DELETE;
g_context_table.DELETE;
SELECT 'ER_LEGAL' er_legal
, UPPER(action_information8) er_legal_value
, 'ER_TYPE' er_type
, action_information20 er_type_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_information15) || UPPER(Action_information14) rep_father_value
, 'REP_POSITION' rep_designation
, CASE WHEN p_designation ='POS' THEN Action_information13
ELSE Action_information21 END rep_designation_value
,'ITAX_ADDRESS' itax_address
,get_location_details ( TO_NUMBER(action_information19),'N','EMPLOYER_ADDRESS1') || DECODE(get_location_details ( TO_NUMBER(action_information19),'N','EMPLOYER_ADDRESS1'),NULL,NULL,',' || fnd_global.local_chr(10)) ||
get_location_details ( TO_NUMBER(action_information19),'N','EMPLOYER_ADDRESS2') || DECODE(get_location_details ( TO_NUMBER(action_information19),'N','EMPLOYER_ADDRESS2'),NULL,NULL,',' || fnd_global.local_chr(10)) ||
get_location_details ( TO_NUMBER(action_information19),'N','EMPLOYER_ADDRESS3') || DECODE(get_location_details ( TO_NUMBER(action_information19),'N','EMPLOYER_ADDRESS3'),NULL,NULL,',' || fnd_global.local_chr(10)) ||
get_location_details ( TO_NUMBER(action_information19),'N','EMPLOYER_ADDRESS4') itax_address_value
, 'ITAX_CITY' itax_city
, get_location_details ( TO_NUMBER(action_information19),'N','CITY') itax_city_value
,'ITAX_PIN_CODE' itax_pin_code
, get_location_details ( TO_NUMBER(action_information19),'N','POSTAL_CODE') itax_pin_code_value
, 'PLACE' gre_place
, pay_in_eoy_reports.get_location_details ( TO_NUMBER(action_information7)
, NULL
, 'CITY') gre_place_value
,'DIG_SIGN' dig_sign_name
,Action_information22 dig_sign_value
FROM pay_action_information
WHERE action_context_id = p_action_context_id
AND action_information_category = 'IN_EOY_ORG'
AND action_information1 = to_char(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 org_information2 quarter,DECODE(org_information6,'O',' Regular','C',' Correction') Nature,
org_information4 receipt,
organization_id orgID,
to_number(org_information3) ActionID
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
AND org_information5 = 'A'
ORDER BY quarter,Nature desc;
g_Bank_Details_tbl.DELETE;
g_qtr_action_id_details.DELETE;
SELECT 'EID' empno_tag
, pai.action_information1 empno_value
, 'EE_DETAILS' emp_details
,CASE WHEN p_designation ='POS' THEN
UPPER(pai.action_information6
|| pai.action_information5
|| DECODE(pai.action_information9,NULL,'',fnd_global.local_chr(10))
|| pai.action_information9)
ELSE
UPPER(pai.action_information6
|| pai.action_information5
|| DECODE(pai.action_information22,NULL,'',fnd_global.local_chr(10))
|| pai.action_information22)
END 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
, CASE WHEN p_designation ='POS' THEN
UPPER(pai.action_information9)
ELSE UPPER(pai.action_information22)
END 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
, 'EMP_EMAIL' emp_email
, action_information21 emp_email_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',
'Stamp Duty for House Property','Stamp Duty for House Property',
'Registration Fees for House Property','Registration Fees for House Property',
'GSLI','GSLI',
'General Provident Fund','General Provident Fund',
'General Insurance Scheme','General Insurance Scheme',
'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',
'F16 Deductions Sec 80CCF','80CCF',
'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 nvl(sum(action_information9),0) Tax_Deposited
FROM pay_action_information pai
WHERE action_information_category ='IN_24Q_DEDUCTEE'
AND action_context_type = 'AAP'
AND action_information3 =p_org_id
AND EXISTS (SELECT 1
FROM pay_assignment_actions paa
WHERE paa.payroll_action_id = p_action_id
AND paa.assignment_action_id = pai.action_context_id
AND paa.assignment_id = p_assignment_id);
SELECT sum(action_information16 - action_information17) Tax_Deposited
FROM pay_action_information pai
WHERE action_information_category ='IN_24QC_DEDUCTEE'
AND action_context_type = 'AAP'
AND action_information3 =p_org_id
AND EXISTS (SELECT 1
FROM pay_assignment_actions paa
WHERE paa.payroll_action_id = p_action_id
AND paa.assignment_action_id = pai.action_context_id
AND paa.assignment_id = p_assignment_id);
SELECT pai.assignment_id
FROM pay_action_information pai
WHERE pai.action_information_category ='IN_EOY_PERSON'
AND action_context_id = p_action_context_id
AND source_id = p_source_id;
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;
SELECT nvl(pps.actual_termination_date,paa.effective_end_Date)
INTO l_asg_end_date
FROM per_Assignments_f paa,-- Modified this for 4774108 to remove NMV
per_periods_of_Service pps
WHERE paa.assignment_id = l_assignment_id
AND paa.period_of_service_id =pps.period_of_service_id
AND paa.effective_end_date = ( SELECT MAX (b.effective_end_date)
FROM per_all_assignments_f b
WHERE paa.assignment_id=b.assignment_id );
So the code related to Form16AA is deleted*/
g_emp_challan_details_tbl.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
'IMAGE_FILE1' image_form16_name
,Action_information23 image_form16_value
FROM pay_action_information
WHERE action_context_id = p_action_context_id
AND action_information_category = 'IN_EOY_ORG'
AND action_information1 = to_char(p_gre_id)
AND ROWNUM =1;
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 = to_char(p_gre_id) /*Bug 13564801 */
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 = to_char(p_gre_id) /*Bug 13564801 */
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;
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 = to_char(p_gre_id) /*Bug 13564801 */
AND pai.action_information2 = p_assessment_year
AND pai.action_information1 LIKE NVL(p_employee_number,'%')
AND pai.action_information21 is not null
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 = to_char(p_gre_id) /*Bug 13564801 */
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;