[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
introduced in the previous update should
have been 801 and not 800
08-AUG-2005 saurgupt 115.5 Modified the proc get_dynamic_tax_unit.
Raised the error if tax_unit_id is not
present for the element being processed.
10-APR-2002 vpandya 115.4 Added get_multi_tax_unit_pay_flag procedure
to get 'Payroll Archiver Level' of the
business group for prepayment.
GRE - Separate Cheque by GRE
TAXGRP - Consolidated Cheque for all GREs.
04-SEP-2002 vpandya 115.3 Added get_dynamic_tax_unit procedure for
Multi GRE functionality.
14-Apr-2000 SSattini 115.1 Changed pay_ca_emp_all_fedtax_info to
pay_ca_emp_all_fedtax_info_v.
07-May-1999 Lwthomps Modified to use the allfed info view.
16-APr-1999 mmukherj 110.0 Created.
*/
--
--
PROCEDURE get_default_jurisdiction(p_asg_act_id number,
p_ee_id number,
p_jurisdiction in out nocopy varchar2)
IS
l_geocode varchar2(15);
Select employment_province, geocode
from pay_ca_emp_all_fedtax_info_v cft,
pay_assignment_actions paa
where cft.assignment_id = paa.assignment_id
and paa.assignment_action_id = p_asg_act_id;
select substr(run_type_name,1,instr(run_type_name,' ')-1)
from pay_run_types_f
where run_type_id = cp_run_type_id;
select segment1 T4_RL1_GRE
,segment11 T4A_RL1_GRE
,segment12 T4A_RL2_GRE
from hr_soft_coding_keyflex hsck
,per_all_assignments_f paf
,pay_assignment_actions paa
,pay_payroll_actions ppa
where paa.assignment_action_id = cp_asg_act_id
and ppa.payroll_action_id = paa.payroll_action_id
and paf.assignment_id = paa.assignment_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
select hoi.org_information5
from hr_organization_information hoi
where hoi.organization_id = cp_tax_unit_id
and hoi.org_information_context = 'Canada Employer Identification';
select decode(segment1, NULL, 0, 1 ) +
decode(segment11, NULL, 0, 1 ) +
decode(segment12, NULL, 0, 1 ) tot_no_of_tu
,nvl(segment1, nvl(segment11,segment12) ) tax_unit_id
from hr_soft_coding_keyflex hsck
,per_all_assignments_f paf
,pay_assignment_actions paa
,pay_payroll_actions ppa
where paa.assignment_action_id = cp_asg_act_id
and ppa.payroll_action_id = paa.payroll_action_id
and paf.assignment_id = paa.assignment_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
select org_information1
into l_reporting_level
from hr_organization_information
where org_information_context = 'Payroll Archiver Level'
and organization_id = p_bus_grp;
SELECT assignment_number
FROM per_assignments_f paf, pay_assignment_actions paa
WHERE paa.assignment_action_id = p_asg_action_id
and paa.assignment_id = paf.assignment_id; */
SELECT
SYSDATE,
NVL(overriding_dd_date,effective_date)
FROM
pay_payroll_actions
WHERE
payroll_action_id = p_payroll_action_id;
SELECT
decode(l_dd_type, 'NOVA_SCOT',to_char(l_direct_deposit_date,'YYDDD'),
'TD', to_char(l_direct_deposit_date,'DDMMYY'),
'CPA','0'||to_char(l_direct_deposit_date,'YYDDD'),
'CIBC',to_char(l_direct_deposit_date,'YYMMDD'))
INTO
l_dd_date
FROM
DUAL;
SELECT
decode(l_dd_type, 'NOVA_SCOT',to_char(l_payment_date,'YYDDD'),
'TD', to_char(l_payment_date,'DDMMYY'),
'CPA','0'||to_char(l_payment_date,'YYDDD'),
'CIBC',to_char(l_payment_date,'YYMMDD'))
INTO
l_payment_date1
FROM
DUAL;
SELECT
SYSDATE,
NVL(overriding_dd_date,effective_date)
FROM
pay_payroll_actions
WHERE
payroll_action_id = p_payroll_action_id;
select ft.nls_territory
from pay_action_information pai,fnd_territories ft
where pai.action_context_id = cp_assignment_action_id
and pai.action_information_category='ADDRESS DETAILS'
and ft.territory_code=pai.action_information13;
select DISTINCT ft.nls_territory
from pay_action_information pai,fnd_territories ft
where pai.action_context_id =
(SELECT payroll_action_id
FROM pay_assignment_actions
WHERE assignment_action_id = cp_assignment_action_id)
and pai.action_information_category='ADDRESS DETAILS'
and ft.territory_code=pai.action_information13;
SELECT net_pay
FROM PAY_AC_EMP_SUM_ACTION_INFO_V
WHERE action_context_id = cp_assignment_action_id
AND action_information_category = 'AC SUMMARY CURRENT';
SELECT net_pay
FROM PAY_AC_EMP_SUM_ACTION_INFO_V
WHERE action_context_id = cp_assignment_action_id
AND ACTION_INFORMATION_CATEGORY = 'AC SUMMARY YTD';
SELECT nvl(ACTION_INFORMATION28,0)
FROM PAY_ACTION_INFORMATION
WHERE action_context_id = cp_assignment_action_id
AND action_information_category = 'EMPLOYEE DETAILS';
SELECT check_deposit_number,
-- org_payment_method_id added for bug 13024522
org_payment_method_id,
segment5,
segment2,
segment3,
value,segment4,segment7 from
pay_emp_net_dist_action_info_v
WHERE action_context_id=cp_assignment_action_id;
SELECT pai.action_information16, ppt.CATEGORY, pai.action_information5,
pai.action_information6, pai.action_information7,
pai.action_information8, pai.action_information9,
pai.action_information10, paa.serial_number
FROM pay_action_information pai,
pay_org_payment_methods_f popmf,
pay_payment_types ppt,
pay_assignment_actions paa
WHERE pai.action_context_id = arch_assact_id
AND pai.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
AND paa.assignment_action_id = chk_assact_id
AND popmf.org_payment_method_id = pai.action_information1
AND popmf.payment_type_id = ppt.payment_type_id
AND (paa.pre_payment_id = pai.action_information15 or ppt.CATEGORY = 'CH')
AND pai.effective_date BETWEEN popmf.effective_start_date AND popmf.effective_end_date;
/* SELECT locked_action_id
FROM pay_action_interlocks
WHERE locking_action_id = arch_assact_id; */
SELECT fnd_number.canonical_to_number (substr (serial_number,3))
FROM pay_assignment_actions
WHERE assignment_action_id = arch_assact_id;
SELECT pai.action_information5
, decode (pai.action_information6, 'C'
, 'Checking Account', 'Savings Account')
, pai.action_information7
, pai.action_information8
, pai.action_information9
, pai.action_information10
, fnd_number.canonical_to_number (substr (paa2.serial_number,3))
, pai.action_information16
, pai.action_information2
, pai.action_information11
, ppt.category
, pay_assignment_actions_pkg.get_payment_status (pail.locked_action_id, pai.action_information15) status
FROM pay_action_information pai
, pay_org_payment_methods_f popmf
, pay_payment_types ppt
, pay_action_interlocks pail
, pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_assignment_actions paa2
WHERE pai.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
AND pai.action_context_id = arch_assact_id
AND paa2.assignment_action_id = pai.action_context_id
AND pai.action_context_id = pail.locking_action_id
AND paa.assignment_action_id = pail.locked_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('U', 'P')
AND pai.action_information1 = popmf.org_payment_method_id
AND popmf.payment_type_id = ppt.payment_type_id
AND ppt.category = 'MT'
AND pai.effective_date
BETWEEN popmf.effective_start_date
AND popmf.effective_end_date;
SELECT paa.serial_number, pain.action_information16 ,
pain.action_information9 ,
DECODE (pain.action_information6,
'C', 'Checking Account',
'Savings Account'
),
pain.action_information7
FROM pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_interlocks pai1,
pay_action_information pain
WHERE pai.locking_action_id = cp_assignment_action_id
AND pai.locked_action_id = pai1.locked_action_id
AND pai.locking_action_id <> pai1.locking_action_id
AND pai1.locking_action_id = paa.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type = 'H'
AND pain.action_information15 = paa.pre_payment_id
AND pain.action_context_id = pai.locking_action_id
AND pain.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION' ;
SELECT paa.serial_number
, pai.action_information3 amount
, ltrim (initcap (rtrim (ppf.title)) || ' ' || rtrim (ppf.first_name) || ' ' || rtrim (ppf.last_name)) employee_name
, pea.segment4 er_transit_code /*13849708*/
, pea.segment3 er_account_number /*13849708*/
FROM pay_assignment_actions paa
, pay_action_information pai
, per_all_assignments_f paf
, per_all_people_f ppf
, pay_external_accounts pea
, pay_org_payment_methods_f popm
WHERE paa.assignment_action_id = cp_chk_assactid
AND pai.action_context_id = cp_assactid
AND paa.pre_payment_id = pai.action_information2
AND paf.assignment_id = pai.assignment_id
AND ppf.person_id = paf.person_id
AND pai.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND pai.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND popm.org_payment_method_id = pai.action_information5
AND pai.effective_date BETWEEN popm.effective_start_date AND popm.effective_end_date
AND pea.external_account_id = popm.external_account_id;
SELECT
decode(l_dd_type, 'NOVA_SCOT',to_char(l_direct_deposit_date,'YYDDD'),
'TD', to_char(l_direct_deposit_date,'DDMMYY'),
'CPA','0'||to_char(l_direct_deposit_date,'YYDDD'),
'CIBC',to_char(l_direct_deposit_date,'YYMMDD'))
INTO
l_dd_date
FROM
DUAL;
SELECT
decode(l_dd_type, 'NOVA_SCOT',to_char(l_payment_date,'YYDDD'),
'TD', to_char(l_payment_date,'DDMMYY'),
'CPA','0'||to_char(l_payment_date,'YYDDD'),
'CIBC',to_char(l_payment_date,'YYMMDD'))
INTO
l_payment_date1
FROM
DUAL;
select initcap(lower(
l_word_amount||' '||
decode(trunc(p_amount),
1,l_unit_singular,
l_unit_plural)||' And '||
lpad(to_char(trunc((p_amount-trunc(p_amount))*l_unit_ratio)),
ceil(l_log),'0')||' '||
decode(trunc((p_amount-trunc(p_amount))*l_unit_ratio),
1,l_sub_unit_singular,
l_sub_unit_plural)
))
into l_currency_word
from dual;
SELECT rule_mode
FROM pay_legislation_rules
WHERE legislation_code = 'CA'
and rule_type = 'S';
SELECT target.SEGMENT4
FROM /* route for SCL keyflex - assignment level */
hr_soft_coding_keyflex target,
per_all_assignments_f ASSIGN
WHERE p_date_earned BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
AND ASSIGN.assignment_id = p_assignment_id
AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
AND target.enabled_flag = 'Y'
AND target.id_flex_num = p_id_flex_num;
select put.user_table_name
into c_ws_tab_name
from hr_organization_information hoi
,pay_user_tables put
where hoi.organization_id = p_bg_id
and hoi.org_information_context ='Work Schedule'
and hoi.org_information1 = put.user_table_id ;
SELECT DECODE(COUNT(session_id), 0, 'N', 'Y')
INTO v_fnd_sess_row
FROM fnd_sessions
WHERE session_id = userenv('sessionid');
select 'Y'
into l_exists
from pay_user_tables PUT,
pay_user_columns PUC
where PUC.USER_COLUMN_NAME = p_ws_name
and NVL(PUC.business_group_id, p_bg_id) = p_bg_id
and NVL(PUC.legislation_code,'CA') = 'CA'
and PUC.user_table_id = PUT.user_table_id
and PUT.user_table_name = c_ws_tab_name;
select PUC.USER_COLUMN_NAME
into v_ws_name
from pay_user_tables PUT,
pay_user_columns PUC
where PUC.USER_COLUMN_ID = p_ws_name
and NVL(PUC.business_group_id, p_bg_id) = p_bg_id
and NVL(PUC.legislation_code,'CA') = 'CA'
and PUC.user_table_id = PUT.user_table_id
and PUT.user_table_name = c_ws_tab_name;
SELECT decode(v_day_no,1,'SUN',2,'MON',3,'TUE',
4,'WED',5,'THU',6,'FRI',7,'SAT')
INTO v_curr_day
FROM DUAL;
SELECT
legislative_parameters,
business_group_id,
org_payment_method_id
FROM
pay_payroll_actions
WHERE
payroll_action_id = pactid;
SELECT hl.description
FROM hr_lookups hl
WHERE hl.lookup_type = 'PAY_CUSTOM_XML_CODE'
AND hl.lookup_code = 'CA_DEPOSIT_ADVICE_XML'
AND hl.enabled_flag = 'Y';
SELECT hl.description
FROM hr_lookups hl
WHERE hl.lookup_type = 'PAY_CUSTOM_XML_CODE'
AND hl.lookup_code = 'CA_ARCHIVE_CHEQUE'
AND hl.enabled_flag = 'Y';
SELECT hl.description
FROM hr_lookups hl
WHERE hl.lookup_type = 'PAY_CUSTOM_XML_CODE'
AND hl.lookup_code = 'CA_THIRD_PARTY_CHEQUE'
AND hl.enabled_flag = 'Y';