[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
CONSOLIDATION_SET Y N Used in select to set up assignment actions
for unpaid Pre Payment Actions
PAYROLL_ID N N select by payroll
START_DATE N N only include pre payments from this date
EFFECTIVE_DATE N Y end of period?
PAYMENT_TYPE_ID Y Y BACS payment type
ORG_PAYMENT_METHOD_ID N N us field to output for just one debit account
OVERRIDE_DD_DATE N Y BACS processin date
EXPIRATION_DATE N L when will the tape expire (for bacs header)
SUBMISSION_NUMBER N L Volume Serial Number for Volume/File headers
MEDIA N L if Media=TEL then it indicates no Headers
MULTI_DAY N L is this a multi day run
BUREAU N L is this a multi file run for a bureau
The Parameter passed to the PLSQL procedure on its 1st call is the
payroll_action_id. The rest of the parameters update the approprate
columns on the payroll actions table - the legislative parameters
are all stored with there token identifyer(e.g. SUBMISSION_NUMBER=TAPE1
MEDIA=TAPE..) in the legislative parameter column.
Change List
-----------
Date Name Vers Bug No Description
---- ---- ---- ------ -----------
30-JUN-95 ASNELL 40.0 Created.
30-JUN-95 NBRISTOW 40.1 Modified to use PL/SQL tables
to pass parameter and
and context rule data.
20-AUG-95 TINEKUKU Created routines to get and
validate the process date, i.e. check for weekends and Bank Holidays.
30-JUL-96 JALLOUN 40.2 Added error handling.
26-NOV-97 APARKES 110.1 Bug 572503 removed to_date
functions in cursor Payment_rule
in validate_process_date function
Bug 572935 hr_general.decode_lookup
used to decode return from get_banks
cursor. Same as UK arcs (R10) v40.14
12-DEC-97 APARKES 110.2 599470 Removed use of to_date in the cursor in
function check_hols when restricting by
ROW_LOW_RANGE_OR_NAME as this col may
contain non-date strings and otherwise
cause ORA-01858.
17-FEB-1998 APARKES 110.3 Converted implicit cursors in
functions validate_process_date
and get_process_date into
explicit cursors.
621006 Catered for periods in
get_process_date.csr_get_period_info
18-MAR-1998 APARKES 110.4 640915 Changed ADD_MONTHS behaviour in
get_process_date() for entry days
at end of short month.
Prevented validate_process_date()
overridding to Next Banking Day
behaviour if no BACS personal payment
method exists.
27-APR-1998 APARKES 110.5 648085 Made check for bank holidays
case insensitive in function
check_hols
03-DEC-1998 FDUCHENE 110.6 749168 Changed cursors (see .pkh)
16-FEB-1999 APARKES 110.7 809367 Made get_process_date treat
monthly periods the same as
shorter ones
07-OCT-1999 PDAVIES 110.8 Replaced all occurrences of
DBMS_Output.Put_Line with
hr_utility.trace.
16-FEB-2000 SMROBINS 115.1 1071880 Handle date parameters in
canonical format
24-NOV-2000 AMILLS 115.2 1381231 Added territory_code to get_
banks cursor as unique key
pay_payment_types_uk changed.
05-MAR-2002 KTHAMPAN 115.6 2231983 Change select statement in function check_hols from
select inst.user_column_instance_id into hols_id
to select 1 into hols_id
06-MAR-2002 KTHAMPAN 115.7 Add dbdrv command
06-MAR-2002 GBUTLER 115.8 Changed sql_str declaration in validate_process_date
for UTF8 project.
11-JUN-2002 KTHAMPAN 115.9 2366717 Removing knowledge of hardcoded id_flex_num.
09-SEP-2004 KTHAMPAN 115.10 Fix GSCC error
*/
--
--
-- Package body:
--
--
--
--
--
total_body_count NUMBER;
SELECT ppp.org_payment_method_id,
ppp.personal_payment_method_id,
ppp.value,
pa.assignment_number
FROM pay_assignment_actions paa,
pay_pre_payments ppp,
per_assignments pa
WHERE paa.payroll_action_id = p_payroll_action_id
AND ppp.pre_payment_id = paa.pre_payment_id
AND paa.assignment_id = pa.assignment_id
ORDER BY ppp.org_payment_method_id, pa.assignment_number;
select_count VARCHAR2(11);
SELECT DISTINCT formula_id
INTO p_formula_id
FROM ff_formulas_f
WHERE formula_name = p_formula_name;
SELECT to_char(effective_date,'YYYY/MM/DD HH24:MI:SS')
INTO p_session_date
from fnd_sessions
where session_id = userenv('sessionid');
select nvl(substr(LEGISLATIVE_PARAMETERS,
decode(instr(LEGISLATIVE_PARAMETERS,'EXPIRATION_DATE='),
'0', null,
instr(LEGISLATIVE_PARAMETERS,'EXPIRATION_DATE='))+16,11),
to_char(add_months(sysdate,2),'YYYY/MM/DD HH24:MI:SS') ) Expiration_date
into p_expiration_date
from pay_payroll_actions
where PAYROLL_ACTION_ID = p_payroll_action_id;
select
nvl(substr(LEGISLATIVE_PARAMETERS,
decode(instr(LEGISLATIVE_PARAMETERS,'SUBMISSION_NUMBER='),
'0', null,
instr(LEGISLATIVE_PARAMETERS,'SUBMISSION_NUMBER='))+18,6),
'NOLABL') Submission_number
into p_submission_number
from pay_payroll_actions
where PAYROLL_ACTION_ID = p_payroll_action_id;
select
to_char(OVERRIDING_DD_DATE ,'YYYY/MM/DD HH24:MI:SS') effdate
into p_process_date
from pay_payroll_actions
where PAYROLL_ACTION_ID = p_payroll_action_id;
SELECT ppp.org_payment_method_id
INTO p_org_payment_method_id
FROM pay_assignment_actions paa, pay_pre_payments ppp
WHERE paa.payroll_action_id = p_payroll_action_id
AND ppp.pre_payment_id = paa.pre_payment_id
AND ROWNUM = 1
ORDER BY ppp.org_payment_method_id;
pay_mag_tape.internal_prm_names(6) := 'TRANSFER_SELECT_COUNT';
select 1 into hols_id
from pay_user_column_instances_f inst,
pay_user_rows_f row1,
pay_user_columns col1,
pay_user_tables tab1
where tab1.user_table_name = 'BANK_HOLIDAYS'
and tab1.business_group_id is null
and tab1.legislation_code = 'GB'
and row1.user_table_id = tab1.user_table_id
and col1.user_table_id = tab1.user_table_id
and col1.user_column_name = sql_str
and inst.user_column_id = col1.user_column_id
and inst.user_row_id = row1.user_row_id
and upper(row1.ROW_LOW_RANGE_OR_NAME) = date_in_char;
select default_dd_date
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
where paa.assignment_action_id =
p_assignment_action_id
and ppa.payroll_action_id =
paa.payroll_action_id
and ptp.time_period_id = ppa.time_period_id;
select ppa.effective_date,
ptp.end_date,
decode(tpr.basic_period_type,'CM',tpr.periods_per_period,0)
from per_time_period_rules tpr,
per_time_period_types tpt,
per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = p_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ptp.time_period_id = ppa.time_period_id
and ptp.period_type = tpt.period_type
and tpt.number_per_fiscal_year = tpr.number_per_fiscal_year;
select paa.assignment_id, ppa.effective_date
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = p_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id;
select pea.segment8
from pay_org_payment_methods_f pop,
pay_personal_payment_methods_f ppp,
pay_assignment_actions paa,
pay_external_accounts pea,
pay_payment_types ppt
where paa.assignment_action_id =
p_assignment_action_id
and ppp.assignment_id =
paa.assignment_id
and pea.external_account_id =
ppp.external_account_id
and pop.org_payment_method_id =
ppp.org_payment_method_id
and ppt.payment_type_id =
pop.payment_type_id +0
and ppt.payment_type_name = 'BACS Tape'
and ppt.territory_code = 'GB';
SELECT target.SEGMENT9
FROM hr_soft_coding_keyflex target,
fnd_id_flex_structures FNDID,
per_assignments_f ASSIGN,
pay_payrolls_f PAYROLL
WHERE param1 BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
AND ASSIGN.assignment_id = param2
AND target.id_flex_num = FNDID.id_flex_num
AND FNDID.id_flex_structure_code = 'GB_STATUTORY_INFO.'
AND target.enabled_flag = 'Y'
AND PAYROLL.payroll_id = ASSIGN.payroll_id
AND param1 BETWEEN PAYROLL.effective_start_date
AND PAYROLL.effective_end_date
AND target.soft_coding_keyflex_id =
PAYROLL.soft_coding_keyflex_id;