[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
in Costing processes in delete_event.
120.12 A.Logue 16-Oct-2006 Handle un-specified payroll ids
in Costing processes.
120.11 A.Logue 13-Oct-2006 Subtle change to
ESITMATE_COST_REVERSAL event type
code.
Improved rule names to include
segment names.
120.10 A.Logue 05-Oct-2006 Use Chart of Account name in
create_custom_adrs.
120.9 A.Logue 04-Oct-2006 Added create_custom_adrs.
Bug 5531729.
120.8 A.Logue 04-Sep-2006 Added delete_event to support rollback
of a Transfer to SLA (if events not
completed).
Also simplified Postprocessing.
Bug 5510388.
120.7 A.Logue 15-Aug-2006 Correct entity_type_code passed to
XLA_EVENTS_PUB_PKG.create_event, and
initialise event_status in pay_xla_events.
120.6 A.Logue 15-Jun-2006 Fix GSCC error.
120.5 A.Logue 25-May-2006 Mark payroll action as complete if
all actions are complete in Postprocessing.
120.4 A.Logue 25-Nov-2005 Added stub preaccounting, postaccounting
and extract procedures for unused SLA hooks.
120.3 A.Logue 22-Nov-2005 Postprocessing procedure.
120.1 A.Logue 16-Nov-2005 Added various procedures and functions.
115.0 A.Logue 07-Oct-2005 Created.
--
DESCRIPTION
Payroll support for SLA (Sub Ledger Accounting)
SLA is new in R12. This file exists to avoid breaking dual maintenance on
TGL files between 11.5 and R12.
*/
--
-- Caches for get_conversion_type
--
g_business_group_id pay_payroll_actions.business_group_id%TYPE := null;
SELECT DISTINCT accounting_date
FROM pay_payment_costs
WHERE assignment_action_id = c_action_id;
SELECT pa.payroll_action_id,
pa1.assignment_id,
pa1.assignment_action_id,
ppa1.payroll_action_id,
ppa1.action_type,
ppa1.effective_date
INTO t_payroll_action_id,
r_assignment_id,
c_assignment_action_id,
c_payroll_action_id,
c_action_type,
c_effective_date
FROM pay_assignment_actions pa, -- TGL assignment action
pay_action_interlocks pi, -- interlock to cost
pay_assignment_actions pa1, -- cost assignment action
pay_payroll_actions ppa1 -- cost payroll action
WHERE pa.assignment_action_id = i_assignment_action_id
AND pi.locking_action_id = pa.assignment_action_id
AND pa1.assignment_action_id = pi.locked_action_id
AND ppa1.payroll_action_id = pa1.payroll_action_id
AND ppa1.action_type IN ('C', 'S', 'EC', 'CP');
SELECT pa2.assignment_action_id,
ppa2.payroll_action_id,
ppa2.action_type,
pap.gl_set_of_books_id,
pay_sla_pkg.get_accounting_date
(ppa2.action_type, c_effective_date,
ppa2.effective_date, ppa2.date_earned)
INTO r_assignment_action_id,
r_payroll_action_id,
r_action_type,
l_ledger_id,
l_accounting_date
FROM pay_assignment_actions pa, -- TGL assignment action
pay_action_interlocks pi2, -- interlock to run
pay_assignment_actions pa2, -- run assignment action
pay_payroll_actions ppa2,-- run payroll action
pay_all_payrolls_f pap
WHERE pa.assignment_action_id = i_assignment_action_id
AND pi2.locking_action_id = pa.assignment_action_id
AND pa2.assignment_action_id = pi2.locked_action_id
AND ppa2.payroll_action_id = pa2.payroll_action_id
AND ppa2.action_type NOT IN ('C', 'S', 'EC')
AND pap.payroll_id = ppa2.payroll_id
AND ppa2.effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
insert into pay_xla_events (assignment_action_id, event_id,
accounting_date, event_status)
values (i_assignment_action_id, l_event_id, l_accounting_date, 'U');
insert into pay_xla_events (assignment_action_id, event_id,
accounting_date, event_status)
values (i_assignment_action_id, l_event_id, l_accounting_date, 'U');
SELECT pay_sla_pkg.get_ecost_accounting_date
(pera.payroll_id, c_effective_date),
pap.gl_set_of_books_id
INTO l_rev_accounting_date,
l_ledger_id
FROM per_all_assignments_f pera,
pay_all_payrolls_f pap
WHERE pera.assignment_id = r_assignment_id
AND c_effective_date BETWEEN pera.effective_start_date
AND pera.effective_end_date
AND pap.payroll_id = pera.payroll_id
AND c_effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
insert into pay_xla_events (assignment_action_id, event_id,
accounting_date, event_status)
values (i_assignment_action_id, l_event_id, c_effective_date, 'U');
insert into pay_xla_events (assignment_action_id, event_id,
accounting_date, event_status)
values (i_assignment_action_id, l_event_id, l_rev_accounting_date, 'U');
SELECT pap.gl_set_of_books_id
INTO l_ledger_id
FROM per_all_assignments_f pera,
pay_all_payrolls_f pap
WHERE pera.assignment_id = r_assignment_id
AND c_effective_date BETWEEN pera.effective_start_date
AND pera.effective_end_date
AND pap.payroll_id = pera.payroll_id
AND c_effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
insert into pay_xla_events (assignment_action_id, event_id,
accounting_date, event_status)
values (i_assignment_action_id, l_event_id, account_date.accounting_date, 'U');
SELECT XPAE.event_id event_id,
XPAE.event_type_code event_type_code,
XPAE.SOURCE_ID_INT_1 event_aa_id,
XPAE.ledger_id ledger_id
FROM XLA_POST_ACCTG_EVENTS_V XPAE,
GL_SETS_OF_BOOKS GSOB,
PAY_XLA_EVENTS PAYE
WHERE XPAE.ledger_id = GSOB.set_of_books_id
AND PAYE.event_id = XPAE.event_id
AND PAYE.event_status <> 'C';
UPDATE pay_xla_events
SET event_status = 'C'
WHERE event_id = xlarec.event_id;
SELECT conversion_type
FROM gl_daily_conversion_types gdct
WHERE gdct.user_conversion_type = p_user_currency_type;
select parameter_value
into g_revb_acc_date_mode
from pay_action_parameters
where parameter_name = 'TGL_REVB_ACC_DATE';
select parameter_value
into g_tgl_date_used
from pay_action_parameters
where parameter_name = 'TGL_DATE_USED';
select parameter_value
into g_tgl_date_used
from pay_action_parameters
where parameter_name = 'TGL_DATE_USED';
SELECT /*+ ORDERED */
decode(g_tgl_date_used, 'E', ptp.end_date,
'EVE', ptp.end_date,
ptp.pay_advice_date + pay.pay_date_offset)
INTO l_accounting_date
FROM pay_all_payrolls_f pay,
per_time_periods ptp
WHERE pay.payroll_id = ecost_payroll_id
AND cost_effective_date BETWEEN pay.effective_start_date
AND pay.effective_end_date
AND ptp.payroll_id = pay.payroll_id
AND cost_effective_date BETWEEN ptp.start_date
AND ptp.end_date;
PROCEDURE delete_event
(i_assignment_action_id NUMBER)
IS
CURSOR xla_events_cur IS
SELECT PAYE.event_id event_id,
PAYE.accounting_date
FROM PAY_XLA_EVENTS PAYE
WHERE PAYE.assignment_action_id = i_assignment_action_id;
hr_utility.set_location('pay_sla_pkg.delete_event',10);
SELECT count(*)
INTO l_completed_events
FROM pay_xla_events
WHERE assignment_action_id = i_assignment_action_id
AND event_status = 'C';
hr_utility.trace('delete_event failed more than 1 C event');
SELECT pap.gl_set_of_books_id
INTO l_ledger_id
FROM pay_assignment_actions pa, -- TGL assignment action
pay_action_interlocks pi, -- interlock to cost
pay_assignment_actions pa1, -- cost assignment action
pay_payroll_actions ppa1,-- cost payroll action
pay_all_payrolls_f pap
WHERE pa.assignment_action_id = i_assignment_action_id
AND pi.locking_action_id = pa.assignment_action_id
AND pa1.assignment_action_id = pi.locked_action_id
AND ppa1.payroll_action_id = pa1.payroll_action_id
AND ppa1.action_type IN ('C', 'S', 'EC', 'CP')
AND ppa1.payroll_id = pap.payroll_id (+)
AND ppa1.effective_date BETWEEN pap.effective_start_date (+)
AND pap.effective_end_date (+);
SELECT pap.gl_set_of_books_id
INTO l_ledger_id
FROM pay_assignment_actions pa, -- TGL assignment action
pay_action_interlocks pi, -- interlock to run
pay_assignment_actions pa1, -- run assignment action
pay_payroll_actions ppa1,-- run payroll action
pay_all_payrolls_f pap,
per_all_assignments_f paf
WHERE pa.assignment_action_id = i_assignment_action_id
AND pi.locking_action_id = pa.assignment_action_id
AND pa1.assignment_action_id = pi.locked_action_id
AND ppa1.payroll_action_id = pa1.payroll_action_id
AND ppa1.action_type IN ('R', 'Q', 'V','B','CP')
AND paf.assignment_id = pa.assignment_id
AND ppa1.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.payroll_id = pap.payroll_id
AND ppa1.effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
hr_utility.trace('Before XLA_EVENTS_PUB_PKG.delete_event');
XLA_EVENTS_PUB_PKG.delete_event (
p_event_source_info => l_event_source_info,
p_event_id => xlarec.event_id,
p_valuation_method => null,
p_security_context => l_security_context
);
hr_utility.trace('After XLA_EVENTS_PUB_PKG.delete_event');
DELETE from pay_xla_events
WHERE assignment_action_id = i_assignment_action_id;
END delete_event;
SELECT fm.payroll_id, fm.gl_set_of_books_id, gl.chart_of_accounts_id,
fm.gl_account_segment, fm.payroll_cost_segment,
fs.id_flex_structure_name coa_name,
fseg.segment_name coa_seg_name
FROM pay_payroll_gl_flex_maps fm,
gl_sets_of_books gl,
fnd_id_flex_structures_vl fs,
fnd_id_flex_segments_vl fseg
WHERE fm.gl_set_of_books_id = gl.set_of_books_id
AND fs.application_id = 101
AND fs.id_flex_code = 'GL#'
AND fs.id_flex_num = gl.chart_of_accounts_id
AND fseg.application_id = 101
AND fseg.id_flex_code = 'GL#'
AND fseg.id_flex_num = gl.chart_of_accounts_id
AND fseg.application_column_name = fm.gl_account_segment
ORDER BY gl.chart_of_accounts_id, fm.gl_account_segment;
select payroll_name
into l_payroll_name
from pay_all_payrolls_f
where payroll_id = segmap.payroll_id
and rownum < 2;
insert into xla_rules_t (
application_id,
amb_context_code,
segment_rule_type_code,
segment_rule_code,
accounting_coa_id,
flexfield_assign_mode_code,
flexfield_segment_code,
enabled_flag,
name,
description,
error_value)
select
801,
'DEFAULT',
'S',
l_segment_rule_code,
l_coa_id,
'S',
l_acc_segment,
'Y',
'Rule for '||segmap.coa_seg_name|| ' in '||segmap.coa_name,
'Rule for '||segmap.coa_seg_name|| ' in '||segmap.coa_name,
0
from dual
where not exists
(select 1
from xla_rules_t
where application_id = 801
and segment_rule_code = l_segment_rule_code);
select count(*)
into l_rule_det_seq
from xla_rule_details_t
where application_id = 801
and segment_rule_code = l_segment_rule_code;
insert into xla_rule_details_t (
application_id,
amb_context_code,
segment_rule_type_code,
segment_rule_code,
segment_rule_detail_id,
user_sequence,
value_type_code,
value_source_application_id,
value_source_type_code,
value_source_code,
error_value
)
select
801,
'DEFAULT',
'S',
l_segment_rule_code,
xla_seg_rule_details_s.nextval,
l_rule_det_seq + 1,
'S',
801,
'S',
l_pay_segment,
0
from dual
where not exists
(select 1
from xla_rule_details_t xrd,
xla_conditions_t xc
where xrd.application_id = 801
and xrd.segment_rule_code = l_segment_rule_code
and xrd.value_source_code = l_pay_segment
and xc.segment_rule_detail_id = xrd.segment_rule_detail_id
and xc.value_constant = l_payroll_name);
insert into xla_conditions_t (
condition_id,
application_id,
amb_context_code,
segment_rule_detail_id,
user_sequence,
value_type_code,
source_application_id,
source_type_code,
source_code,
line_operator_code,
value_constant,
error_value)
select
xla_conditions_s.nextval,
801,
'DEFAULT',
xla_seg_rule_details_s.currval,
1,
'C',
801,
'S',
'PAYROLL_NAME',
'E',
l_payroll_name,
0
from dual
where not exists
(select 1
from xla_rule_details_t xrd,
xla_conditions_t xc
where xrd.application_id = 801
and xrd.segment_rule_code = l_segment_rule_code
and xrd.value_source_code = l_pay_segment
and xc.segment_rule_detail_id = xrd.segment_rule_detail_id
and xc.value_constant = l_payroll_name);