[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-1995 ASNELL 40.0 Created.
30-JUN-1995 NBRISTOW 40.1 Modified to use PL/SQL tables
to pass parameter and
and context rule data.
20-AUG-1995 TINEKUKU Created routines to get and
validate the process date,
i.e.check for weekends and Bank
Holidays.
30-JUL-1996 ALLOUN 40.2 Added error handling.
01-DEC-1996 TTAGAWA Package name is changed for Japanese
MAGTAPE process and recreated.
08-JAN-1999 YNEGORO 110.01 787405
03-JUN-1999 YNEGORO 115.02 Flex Date change
19-JUL-1999 TNANJYO 115.03 Add a semicolon to the exit statement.
Comment Out dbms_output.
*/
--
--
-- 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 fnd_date.date_to_canonical(effective_date)
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),'DD-MON-YYYY') ) Expiration_date
fnd_date.date_to_canonical(add_months(sysdate,2)) ) 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
-- substr(to_char(OVERRIDING_DD_DATE ,'DD-MON-YYYY'),1,11) effdate
substr(fnd_date.date_to_canonical(OVERRIDING_DD_DATE),1,11) 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 inst.user_column_instance_id into hols_id
from pay_user_columns col1,
pay_user_tables tab1,
pay_user_rows_f row1,
pay_user_column_instances_f inst
where tab1.user_table_name = 'BANK_HOLIDAYS'
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 to_date(row1.ROW_LOW_RANGE_OR_NAME, 'DD-Mon-YYYY') = date_in;
select default_dd_date into 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 into eff_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
and ppt.payment_type_name = 'BACS Tape';
SELECT target.SEGMENT9
FROM hr_soft_coding_keyflex target,
per_assignments_f ASSIGN,
pay_payrolls_f PAYROLL
-- WHERE to_date (param1, 'DD-MON-YYYY')
WHERE fnd_date.canonical_to_date(param1)
BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
AND ASSIGN.assignment_id = param2
AND target.id_flex_num = 50106
AND target.enabled_flag = 'Y'
AND PAYROLL.payroll_id = ASSIGN.payroll_id
AND fnd_date.canonical_to_date(param1)
BETWEEN PAYROLL.effective_start_date
AND PAYROLL.effective_end_date
AND target.soft_coding_keyflex_id =
PAYROLL.soft_coding_keyflex_id;
select ppa.effective_date into eff_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 assignment_id into assignmt_id
from pay_assignment_actions
where assignment_action_id = p_assignment_action_id;