[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 to_number(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 1';
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_id;
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_id;
SELECT pay_assignment_actions_s.nextval
INTO ln_mag_asg_act_id
FROM dual;
SELECT dim.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(RFC_ID,'-','') RFC_ID
,CURP
,ltrim(rtrim(PATERNAL_LAST_NAME)) PATERNAL_LAST_NAME
,ltrim(rtrim(MATERNAL_LAST_NAME)) MATERNAL_LAST_NAME
,ltrim(rtrim(NAMES)) NAMES
,decode(ECONOMIC_ZONE, 'A', '01',
'B', '02', 'C', '03', '0') ECONOMIC_ZONE
,decode(ANNUAL_TAX_CALC_FLAG, 'Y', '1',
'N', '2' , '0') ANNUAL_TAX_CALC_FLAG
,RATE_FISCAL_YEAR_IND
,RATE_1991_IND
,to_char(FND_NUMBER.canonical_to_number(nvl(TAX_SUBSIDY_PCT,'0')), p_format)
TAX_SUBSIDY_PCT
,decode(UNION_WORKER_FLAG, 'Y', 1, 'N', 2, 0) UNION_WORKER_FLAG
,0 ASSIMILATED_TO_SALARY_IND
,STATE_ID
,replace(OTHER_ER_RFC1,'-','') OTHER_ER_RFC1
,replace(OTHER_ER_RFC2,'-','') OTHER_ER_RFC2
,replace(OTHER_ER_RFC3,'-','') OTHER_ER_RFC3
,replace(OTHER_ER_RFC4,'-','') OTHER_ER_RFC4
,replace(OTHER_ER_RFC5,'-','') OTHER_ER_RFC5
,replace(OTHER_ER_RFC6,'-','') OTHER_ER_RFC6
,replace(OTHER_ER_RFC7,'-','') OTHER_ER_RFC7
,replace(OTHER_ER_RFC8,'-','') OTHER_ER_RFC8
,replace(OTHER_ER_RFC9,'-','') OTHER_ER_RFC9
,replace(OTHER_ER_RFC10,'-','') OTHER_ER_RFC10
,0 SEP_EARNINGS
,0 ASSIMILATED_SALARIES
,0 ER_PAYMENT_TO_EE
,RET_EARNINGS_IN_PART_PYMNT
,RET_DAILY_EARNINGS_IN_PYMNT
,RET_PERIOD_EARNINGS
,RET_EARNINGS_IN_ONE_PYMNT
,RET_EARNINGS_DAYS
,RET_EXEMPT_EARNINGS
,RET_TAXABLE_EARNINGS
,RET_CUMULATIVE_EARNINGS
,RET_NON_CUMULATIVE_EARNINGS
,ISR_WITHHELD_FOR_RET_EARNINGS
,AMENDS
,NVL(SENIORITY,0) SENIORITY
,ISR_EXEMPT_FOR_AMENDS
,ISR_SUBJECT_FOR_AMENDS
,LAST_MTH_ORD_SAL
,LAST_MTH_ORD_SAL_WITHHELD
,NON_CUMULATIVE_AMENDS
,ISR_WITHHELD_FOR_AMENDS
,ASSIMILATED_EARNINGS
,ISR_WITHHELD_FOR_ASSI_EARNINGS
,decode(STK_OPTIONS_VESTING_VALUE,0,0,1) EMPR_STOCK_OPTION_PLAN
,STK_OPTIONS_VESTING_VALUE
,STK_OPTIONS_GRANT_PRICE
,STK_OPTIONS_CUML_INCOME
,STK_OPTIONS_TAX_WITHHELD
,ISR_SUBJECT_FOR_FIXED_EARNINGS
,ISR_EXEMPT_FOR_FIXED_EARNINGS
,ISR_SUBJECT_FOR_XMAS_BONUS
,ISR_EXEMPT_FOR_XMAS_BONUS
,ISR_SUBJECT_FOR_TRAVEL_EXP
,ISR_EXEMPT_FOR_TRAVEL_EXP
,ISR_SUBJECT_FOR_OVERTIME
,ISR_EXEMPT_FOR_OVERTIME
,ISR_SUBJECT_FOR_VAC_PREMIUM
,ISR_EXEMPT_FOR_VAC_PREMIUM
,ISR_SUBJECT_FOR_DOM_PREMIUM
,ISR_EXEMPT_FOR_DOM_PREMIUM
,ISR_SUBJECT_FOR_PROFIT_SHARING
,ISR_EXEMPT_FOR_PROFIT_SHARING
,ISR_SUBJECT_FOR_HEALTHCARE_REI
,ISR_EXEMPT_FOR_HEALTHCARE_REI
,ISR_SUBJECT_FOR_SAVINGS_FUND
,ISR_EXEMPT_FOR_SAVINGS_FUND
,ISR_SUBJECT_FOR_SAVINGS_BOX
,ISR_EXEMPT_FOR_SAVINGS_BOX
,ISR_SUBJECT_FOR_PANTRY_COUPONS
,ISR_EXEMPT_FOR_PANTRY_COUPONS
,ISR_SUBJECT_FOR_FUNERAL_AID
,ISR_EXEMPT_FOR_FUNERAL_AID
,ISR_SUBJECT_FOR_WR_PD_BY_ER
,ISR_EXEMPT_FOR_WR_PD_BY_ER
,ISR_SUBJECT_FOR_PUN_INCENTIVE
,ISR_EXEMPT_FOR_PUN_INCENTIVE
,ISR_SUBJECT_FOR_LIFE_INS_PRE
,ISR_EXEMPT_FOR_LIFE_INS_PRE
,ISR_SUBJECT_FOR_MAJOR_MED_INS
,ISR_EXEMPT_FOR_MAJOR_MED_INS
,ISR_SUBJECT_FOR_REST_COUPONS
,ISR_EXEMPT_FOR_REST_COUPONS
,ISR_SUBJECT_FOR_GAS_COUPONS
,ISR_EXEMPT_FOR_GAS_COUPONS
,ISR_SUBJECT_FOR_UNI_COUPONS
,ISR_EXEMPT_FOR_UNI_COUPONS
,ISR_SUBJECT_FOR_RENTAL_AID
,ISR_EXEMPT_FOR_RENTAL_AID
,ISR_SUBJECT_FOR_EDU_AID
,ISR_EXEMPT_FOR_EDU_AID
,ISR_SUBJECT_FOR_GLASSES_AID
,ISR_EXEMPT_FOR_GLASSES_AID
,ISR_SUBJECT_FOR_TRANS_AID
,ISR_EXEMPT_FOR_TRANS_AID
,ISR_SUBJECT_FOR_UNION_PD_BY_ER
,ISR_EXEMPT_FOR_UNION_PD_BY_ER
,ISR_SUBJECT_FOR_DISAB_SUBSIDY
,ISR_EXEMPT_FOR_DISAB_SUBSIDY
,ISR_SUBJECT_FOR_CHILD_SCHOLAR
,ISR_EXEMPT_FOR_CHILD_SCHOLAR
,NVL(PREV_ER_EARNINGS,0) PREV_ER_EARNINGS
,NVL(PREV_ER_EXEMPT_EARNINGS,0) PREV_ER_EXEMPT_EARNINGS
,ISR_SUBJECT_OTHER_INCOME
,ISR_EXEMPT_OTHER_INCOME
,TOTAL_SUBJECT_EARNINGS
,TOTAL_EXEMPT_EARNINGS
,TAX_WITHHELD_IN_FISCAL_YEAR
,NVL(PREV_ER_ISR_WITHHELD,0) PREV_ER_ISR_WITHHELD
--,CURRENT_FY_ARREARS
,decode( sign (decode( ANNUAL_TAX_CALC_FLAG , 'Y',NVL(CURRENT_FY_ARREARS,0), 0))
,-1,(decode( ANNUAL_TAX_CALC_FLAG , 'Y',NVL(CURRENT_FY_ARREARS,0), 0))* -1,0)
CURRENT_FY_ARREARS
,PREV_FY_ARREARS
,CREDIT_TO_SALARY
,CREDIT_TO_SALARY_PAID
,SOCIAL_FORESIGHT_EARNINGS
,ISR_EXEMPT_FOR_SOC_FORESIGHT
,nvl(TOTAL_SUBJECT_EARNINGS,0)+nvl(TOTAL_EXEMPT_EARNINGS,0) SUM_SAL_WAGES_EARNINGS
,EMPLOYEE_STATE_TAX_WITHHELD LOCAL_TAX_AMT_EARN_SAL_WAGES
,0 AMT_SUBSIDY_EMPT_IN_FY
,0 AMT_SUBSIDY_INCOME_PAID_EMP_FY
,decode(ANNUAL_TAX_CALC_FLAG,'Y',ISR_CALCULATED,0) ISR_CALCULATED
,ISR_CREDITABLE_SUBSIDY
,ISR_NON_CREDITABLE_SUBSIDY
,ISR_ON_CUMULATIVE_EARNINGS
,ISR_ON_NON_CUMULATIVE_EARNINGS
,0 ISR_SUBSIDY_EMPT_PAID_TO_EMP
,0 ISR_SUBSIDY_INC_PAID_EMP
,trunc(FND_NUMBER.canonical_to_number(NVL(tax_subsidy_pct,'0')),0) TAX_SUBSIDY_PCT_I
,rpad(replace(FND_NUMBER.canonical_to_number(NVL(tax_subsidy_pct,'0'))-
trunc(FND_NUMBER.canonical_to_number(NVL(tax_subsidy_pct,'0')),0),'.',''),4,0)
TAX_SUBSIDY_PCT_D
,to_char(FND_NUMBER.canonical_to_number(nvl(SUBSIDY_PORTION_APPLIED,'0')),p_format)
SUBSIDY_PORTION_APPLIED
,trunc(FND_NUMBER.canonical_to_number(NVL(subsidy_portion_applied,'0')),0)
SUBSIDY_PORTION_APPLIED_I
,rpad(replace(FND_NUMBER.canonical_to_number(NVL(subsidy_portion_applied,'0'))-
trunc(FND_NUMBER.canonical_to_number(NVL(subsidy_portion_applied,'0')),0),'.',''),4,0)
SUBSIDY_PORTION_APPLIED_D
,TOT_EARNING_ASSI_CONCEPTS
,EMPLOYEE_STATE_TAX_WITHHELD
,TOT_EXEMPT_EARNINGS
,TOT_NON_CUMULATIVE_EARNINGS
,TOT_CUMULATIVE_EARNINGS
,CREDITABLE_SUBSIDY_FRACTIONIII
,CREDITABLE_SUBSIDY_FRACTIONIV
,TAX_ON_INCOME_FISCAL_YEAR
,ISR_TAX_WITHHELD
,(nvl(TOTAL_SUBJECT_EARNINGS,0) + nvl(TOTAL_EXEMPT_EARNINGS,0)) TOTAL_EARNINGS
,replace(ER_RFC_ID,'-','') ER_RFC_ID
,UPPER(ER_LEGAL_NAME) ER_LEGAL_NAME
,UPPER(ER_LEGAL_REP_NAMES) ER_LEGAL_REP_NAMES
,replace(ER_LEGAL_REP_RFC_ID,'-','') ER_LEGAL_REP_RFC_ID
,ER_LEGAL_REP_CURP
,NVL(ER_TAX_SUBSIDY_PCT,'0') ER_TAX_SUBSIDY_PCT
,FISCAL_YEAR_REPORTING
,ltrim(rtrim(PATERNAL_LAST_NAME)) ||' '
||ltrim(rtrim(MATERNAL_LAST_NAME)) ||' '
||ltrim(rtrim(NAMES)) FULL_NAME
FROM pay_mx_isr_tax_format37_v dim
,pay_assignment_actions paa
,pay_action_interlocks pai
WHERE dim.payroll_action_id = paa.payroll_action_id
AND dim.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;
dim_xml_tbl.DELETE;
SELECT fnd_global.local_chr(13) || fnd_global.local_chr(10)
INTO EOL
FROM dual;