[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT effective_date,
business_group_id,
pay_mx_utility.get_legi_param_val( 'LEGAL_EMPLOYER'
,legislative_parameters)
FROM pay_payroll_actions
WHERE payroll_action_id = cp_payroll_action_id;
SELECT DISTINCT paa_arch.serial_number
FROM pay_assignment_actions paa_arch
,pay_payroll_actions ppa_arch
WHERE ppa_arch.business_group_id = '|| gn_business_group_id ||'
AND ppa_arch.report_type = ''MX_YREND_ARCHIVE''
AND ppa_arch.report_qualifier = ''MX''
AND ppa_arch.report_category = ''ARCHIVE''
AND pay_mx_utility.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',
ppa_arch.legislative_parameters) = '||gn_legal_er_id||'
AND TRUNC(ppa_arch.effective_date,''Y'') =
fnd_date.canonical_to_date('''||
fnd_date.date_to_canonical(gd_effective_date)||''')
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.action_status = ''C''
AND :p_payroll_action_id = '||p_payroll_action_id||'
ORDER BY serial_number';
SELECT paa_arch.assignment_action_id
,paa_arch.assignment_id
,paa_arch.serial_number person_id
,ppa_arch.payroll_action_id
FROM pay_assignment_actions paa_arch,
pay_payroll_actions ppa_arch
WHERE ppa_arch.business_group_id = cp_business_group_id
AND ppa_arch.report_type = 'MX_YREND_ARCHIVE'
AND ppa_arch.report_qualifier = 'MX'
AND ppa_arch.report_category = 'ARCHIVE'
AND pay_mx_utility.get_parameter('TRANSFER_LEGAL_EMPLOYER',
ppa_arch.legislative_parameters) = cp_legal_er_id
AND TRUNC(ppa_arch.effective_date,'Y') = TRUNC(cp_effective_date,'Y')
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.action_status = 'C'
AND paa_arch.serial_number BETWEEN cp_start_person_id
AND cp_end_person_id
ORDER BY paa_arch.serial_number,
paa_arch.assignment_action_id desc;
SELECT paa_arch.assignment_action_id
,paa_arch.assignment_id
,paa_arch.serial_number person_id
,ppa_arch.payroll_action_id
FROM pay_assignment_actions paa_arch,
pay_payroll_actions ppa_arch,
pay_population_ranges ppr
WHERE ppa_arch.business_group_id = cp_business_group_id
AND ppa_arch.report_type = 'MX_YREND_ARCHIVE'
AND ppa_arch.report_qualifier = 'MX'
AND ppa_arch.report_category = 'ARCHIVE'
AND pay_mx_utility.get_parameter('TRANSFER_LEGAL_EMPLOYER',
ppa_arch.legislative_parameters) = cp_legal_er_id
AND TRUNC(ppa_arch.effective_date,'Y') = TRUNC(cp_effective_date,'Y')
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.action_status = 'C'
AND paa_arch.serial_number = ppr.person_id
AND ppr.chunk_number = cp_chunk
AND ppr.payroll_action_id = cp_payroll_action_id
ORDER BY paa_arch.serial_number,
paa_arch.assignment_action_id desc;
SELECT pay_assignment_actions_s.nextval
INTO ln_mag_asg_act_id
FROM dual;
SELECT format2d.person_id PERSON_ID
,to_char(fnd_date.canonical_to_date(start_month),'mm') START_MONTH
,to_char(fnd_date.canonical_to_date(end_month),'mm') END_MONTH
,replace(ER_RFC_ID,'-','') ER_RFC_ID
,CURP
,ltrim(rtrim(PATERNAL_LAST_NAME)) PATERNAL_LAST_NAME
,ltrim(rtrim(MATERNAL_LAST_NAME)) MATERNAL_LAST_NAME
,ltrim(rtrim(NAMES)) NAMES
,ltrim(rtrim(PATERNAL_LAST_NAME))||' '||
ltrim(rtrim(MATERNAL_LAST_NAME))||' '||
ltrim(rtrim(NAMES)) NAME
,0 WAGE_LEVEL
,(fnd_date.canonical_to_date(end_month) -
fnd_date.canonical_to_date(start_month)) + 1 TOTAL_DAYS_WORKED
,(ISR_SUBJECT_FOR_FIXED_EARNINGS +
ISR_EXEMPT_FOR_FIXED_EARNINGS) SAL_WAGES
,nvl(ISR_SUBJECT_FOR_OVERTIME,0) +
nvl(ISR_EXEMPT_FOR_OVERTIME,0) OVERTIME
,nvl(ISR_SUBJECT_FOR_PROFIT_SHARING,0) +
nvl(ISR_EXEMPT_FOR_PROFIT_SHARING,0) PROFIT_SHARING
,nvl(ISR_SUBJECT_FOR_XMAS_BONUS,0) +
nvl(ISR_EXEMPT_FOR_XMAS_BONUS,0) CHRISTMAS_BONUS
,nvl(ISR_SUBJECT_FOR_VAC_PREMIUM,0) +
nvl(ISR_EXEMPT_FOR_VAC_PREMIUM,0) VACATION_PREMIUM
,nvl(ISR_SUBJECT_FOR_SAVINGS_FUND,0) +
nvl(ISR_EXEMPT_FOR_SAVINGS_FUND,0) SAVING_FUND
,AID_FOR_PANTRY_AND_FOOD
,nvl(ISR_SUBJECT_FOR_TRANS_AID,0) +
nvl(ISR_EXEMPT_FOR_TRANS_AID,0) TRANSPORTATION_AID
,0 OTHER_EARNINGS
,nvl(TOTAL_SUBJECT_EARNINGS,0)+
nvl(TOTAL_EXEMPT_EARNINGS,0) TOTAL_EARNINGS
FROM pay_mx_isr_tax_format37_v format2d
,pay_assignment_actions paa
,pay_action_interlocks pai
WHERE format2d.payroll_action_id = paa.payroll_action_id
AND format2d.person_id = to_number(paa.serial_number)
AND paa.assignment_action_id = pai.locked_action_id
AND pai.locking_action_id = cp_assignment_action_id
ORDER BY effective_date DESC;
select legislation_info2
from PAY_MX_LEGISLATION_INFO_F
where LEGISLATION_INFO_TYPE = 'MX Minimum Wage Information'
and effective_start_date = cp_effective_date
and legislation_info1 = 'GMW';
SELECT paa.payroll_action_id
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = cp_assignment_action_id;
format2d_xml_tbl.DELETE;
SELECT fnd_global.local_chr(13) || fnd_global.local_chr(10)
INTO EOL
FROM dual;
SELECT COUNT(*)
INTO ln_count
FROM pay_us_rpt_totals
WHERE tax_unit_id = ln_payroll_action_id
AND session_id = ln_session_id;
INSERT INTO pay_us_rpt_totals ( session_id
,business_group_id
,tax_unit_id
,organization_name )
VALUES ( ln_session_id
,gn_business_group_id
,ln_payroll_action_id
,prev_format2d.er_rfc_id );
UPDATE pay_us_rpt_totals
SET value1 = NVL(value1,0) + 1 -- No. of Employees
,value6 = NVL(value6,0) + prev_format2d.SAL_WAGES
,value11 = NVL(value11,0) + prev_format2d.OVERTIME
,value16 = NVL(value16,0) + prev_format2d.PROFIT_SHARING
,value21 = NVL(value21,0) + prev_format2d.CHRISTMAS_BONUS
,value26 = NVL(value26,0) + prev_format2d.VACATION_PREMIUM
,attribute1 = NVL(attribute1,'0') + prev_format2d.SAVING_FUND
,attribute6 = NVL(attribute6,'0') +
prev_format2d.AID_FOR_PANTRY_AND_FOOD
,attribute11 = NVL(attribute11,'0') +
prev_format2d.TRANSPORTATION_AID
,attribute16 = NVL(attribute16,'0') +
prev_format2d.OTHER_EARNINGS
,attribute21 = NVL(attribute21,'0') +
prev_format2d.TOTAL_EARNINGS
WHERE tax_unit_id = ln_payroll_action_id
AND session_id = ln_session_id;
UPDATE pay_us_rpt_totals
SET value2 = NVL(value2,0) + 1 -- No. of Employees
,value7 = NVL(value7,0) + prev_format2d.SAL_WAGES
,value12 = NVL(value12,0) + prev_format2d.OVERTIME
,value17 = NVL(value17,0) + prev_format2d.PROFIT_SHARING
,value22 = NVL(value22,0) + prev_format2d.CHRISTMAS_BONUS
,value27 = NVL(value27,0) + prev_format2d.VACATION_PREMIUM
,attribute2 = NVL(attribute2,'0') + prev_format2d.SAVING_FUND
,attribute7 = NVL(attribute7,'0') +
prev_format2d.AID_FOR_PANTRY_AND_FOOD
,attribute12 = NVL(attribute12,'0') +
prev_format2d.TRANSPORTATION_AID
,attribute17 = NVL(attribute17,'0') +
prev_format2d.OTHER_EARNINGS
,attribute22 = NVL(attribute22,'0') +
prev_format2d.TOTAL_EARNINGS
WHERE tax_unit_id = ln_payroll_action_id
AND session_id = ln_session_id;
UPDATE pay_us_rpt_totals
SET value3 = NVL(value3,0) + 1 -- No. of Employees
,value8 = NVL(value8,0) + prev_format2d.SAL_WAGES
,value13 = NVL(value13,0) + prev_format2d.OVERTIME
,value18 = NVL(value18,0) + prev_format2d.PROFIT_SHARING
,value23 = NVL(value23,0) + prev_format2d.CHRISTMAS_BONUS
,value28 = NVL(value28,0) + prev_format2d.VACATION_PREMIUM
,attribute3 = NVL(attribute3,'0') + prev_format2d.SAVING_FUND
,attribute8 = NVL(attribute8,'0') +
prev_format2d.AID_FOR_PANTRY_AND_FOOD
,attribute13 = NVL(attribute13,'0') +
prev_format2d.TRANSPORTATION_AID
,attribute18 = NVL(attribute18,'0') +
prev_format2d.OTHER_EARNINGS
,attribute23 = NVL(attribute23,'0') +
prev_format2d.TOTAL_EARNINGS
WHERE tax_unit_id = ln_payroll_action_id
AND session_id = ln_session_id;
UPDATE pay_us_rpt_totals
SET value4 = NVL(value4,0) + 1 -- No. of Employees
,value9 = NVL(value9,0) + prev_format2d.SAL_WAGES
,value14 = NVL(value14,0) + prev_format2d.OVERTIME
,value19 = NVL(value19,0) + prev_format2d.PROFIT_SHARING
,value24 = NVL(value24,0) + prev_format2d.CHRISTMAS_BONUS
,value29 = NVL(value29,0) + prev_format2d.VACATION_PREMIUM
,attribute4 = NVL(attribute4,'0') + prev_format2d.SAVING_FUND
,attribute9 = NVL(attribute9,'0') +
prev_format2d.AID_FOR_PANTRY_AND_FOOD
,attribute14 = NVL(attribute14,'0') +
prev_format2d.TRANSPORTATION_AID
,attribute19 = NVL(attribute19,'0') +
prev_format2d.OTHER_EARNINGS
,attribute24 = NVL(attribute24,'0') +
prev_format2d.TOTAL_EARNINGS
WHERE tax_unit_id = ln_payroll_action_id
AND session_id = ln_session_id;
UPDATE pay_us_rpt_totals
SET value5 = NVL(value5,0) + 1 -- No. of Employees
,value10 = NVL(value10,0) + prev_format2d.SAL_WAGES
,value15 = NVL(value15,0) + prev_format2d.OVERTIME
,value20 = NVL(value20,0) + prev_format2d.PROFIT_SHARING
,value25 = NVL(value25,0) + prev_format2d.CHRISTMAS_BONUS
,value30 = NVL(value30,0) + prev_format2d.VACATION_PREMIUM
,attribute5 = NVL(attribute5,'0') + prev_format2d.SAVING_FUND
,attribute10 = NVL(attribute10,'0') +
prev_format2d.AID_FOR_PANTRY_AND_FOOD
,attribute15 = NVL(attribute15,'0') +
prev_format2d.TRANSPORTATION_AID
,attribute20 = NVL(attribute20,'0') +
prev_format2d.OTHER_EARNINGS
,attribute25 = NVL(attribute25,'0') +
prev_format2d.TOTAL_EARNINGS
WHERE tax_unit_id = ln_payroll_action_id
AND session_id = ln_session_id;
select organization_name RFC_ID
,NVL(SUM(value1),0) NO_OF_EMPLOYEES_280042
,NVL(SUM(value2),0) NO_OF_EMPLOYEES_280043
,NVL(SUM(value3),0) NO_OF_EMPLOYEES_280044
,NVL(SUM(value4),0) NO_OF_EMPLOYEES_280045
,NVL(SUM(value5),0) NO_OF_EMPLOYEES_280046
,NVL(SUM(value6),0) SAL_WAGES_280047
,NVL(SUM(value7),0) SAL_WAGES_280057
,NVL(SUM(value8),0) SAL_WAGES_280067
,NVL(SUM(value9),0) SAL_WAGES_280077
,NVL(SUM(value10),0) SAL_WAGES_280087
,NVL(SUM(value11),0) OVERTIME_280048
,NVL(SUM(value12),0) OVERTIME_280058
,NVL(SUM(value13),0) OVERTIME_280068
,NVL(SUM(value14),0) OVERTIME_280078
,NVL(SUM(value15),0) OVERTIME_280088
,NVL(SUM(value16),0) PROFIT_SHARING_280049
,NVL(SUM(value17),0) PROFIT_SHARING_280059
,NVL(SUM(value18),0) PROFIT_SHARING_280069
,NVL(SUM(value19),0) PROFIT_SHARING_280079
,NVL(SUM(value20),0) PROFIT_SHARING_280089
,NVL(SUM(value21),0) CHRISTMAS_BONUS_280050
,NVL(SUM(value22),0) CHRISTMAS_BONUS_280060
,NVL(SUM(value23),0) CHRISTMAS_BONUS_280070
,NVL(SUM(value24),0) CHRISTMAS_BONUS_280080
,NVL(SUM(value25),0) CHRISTMAS_BONUS_280090
,NVL(SUM(value26),0) VACATION_PREMIUM_280051
,NVL(SUM(value27),0) VACATION_PREMIUM_280061
,NVL(SUM(value28),0) VACATION_PREMIUM_280071
,NVL(SUM(value29),0) VACATION_PREMIUM_280081
,NVL(SUM(value30),0) VACATION_PREMIUM_280091
,NVL(SUM(attribute1),0) SAVING_FUND_280052
,NVL(SUM(attribute2),0) SAVING_FUND_280062
,NVL(SUM(attribute3),0) SAVING_FUND_280072
,NVL(SUM(attribute4),0) SAVING_FUND_280082
,NVL(SUM(attribute5),0) SAVING_FUND_280092
,NVL(SUM(attribute6),0) AID_FOR_PANTRY_AND_FOOD_280053
,NVL(SUM(attribute7),0) AID_FOR_PANTRY_AND_FOOD_280063
,NVL(SUM(attribute8),0) AID_FOR_PANTRY_AND_FOOD_280073
,NVL(SUM(attribute9),0) AID_FOR_PANTRY_AND_FOOD_280083
,NVL(SUM(attribute10),0) AID_FOR_PANTRY_AND_FOOD_280093
,NVL(SUM(attribute11),0) TRANSPORTATION_AID_280054
,NVL(SUM(attribute12),0) TRANSPORTATION_AID_280064
,NVL(SUM(attribute13),0) TRANSPORTATION_AID_280074
,NVL(SUM(attribute14),0) TRANSPORTATION_AID_280084
,NVL(SUM(attribute15),0) TRANSPORTATION_AID_280094
,NVL(SUM(attribute16),0) OTHER_EARNINGS_280055
,NVL(SUM(attribute17),0) OTHER_EARNINGS_280065
,NVL(SUM(attribute18),0) OTHER_EARNINGS_280075
,NVL(SUM(attribute19),0) OTHER_EARNINGS_280085
,NVL(SUM(attribute20),0) OTHER_EARNINGS_280095
,NVL(SUM(attribute21),0) TOTAL_EARNINGS_280056
,NVL(SUM(attribute22),0) TOTAL_EARNINGS_280066
,NVL(SUM(attribute23),0) TOTAL_EARNINGS_280076
,NVL(SUM(attribute24),0) TOTAL_EARNINGS_280086
,NVL(SUM(attribute25),0) TOTAL_EARNINGS_280096
FROM pay_us_rpt_totals
WHERE tax_unit_id = cp_payroll_action_id
GROUP by organization_name;
format2d_xml_tbl.DELETE;
DELETE FROM pay_us_rpt_totals
WHERE tax_unit_id = ln_payroll_action_id;