[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
14-JUN-2012 pbalu 14196612 When duplicate Rank 1 exists delete one row.
14-JUN-2012 pbalu 14237673 When duplicate Rank 1 exists retain one row.
18-JUL-2012 pbalu 14331104 Added assignment_action_id when fetching random string for BACS
20-JUL-2012 ssarap 14331104 Used child asg action id for PTD and YTD balances for NI Employee
and NI Employer balances.
01-Aug-2012 ssarap 115.3 Added functions for FPS Aggregated cases.
02-Aug-2012 rajganga 115.4 Removed CheckFile Equivalence.
02-Aug-2012 rajganga 115.4 Added logic to process all childs of payroll run.
24-Aug-2012 ssarap 115.5 BACS code should be generted based on profile setting.
Can be configured at Tax refernce level.
29-Aug-2012 ssarap 115.6 Used the correct NI Reporting flag.
25-Sep-2012 ssarap 115.7 Corrected the NI Able values to Period Level.
28-Sep-2012 ssarap 115.8 Moved the BACS changes related to random digit generation from procedure
fetch_random_digit_bacs to FPS_BACS_PREPROCESS. This would avoid
generation of hash code and insertion into PAY_GB_BACS_FPS table.
28-Sep-2012 ssarap 115.9 Implemented the code review comments from Prem.
05-Oct-2012 ssarap 115.10 In FPS_BACS_PREPROCESS procedure used used the config table directly
29-Oct-2012 pbalu 115.13 14797457 When BACS payment exists in a prepayment, BACS should not error
30-Oct-2012 pbalu 115.14 14807372 Master insert should be committed before data insert starts
31-Oct-2012 krreddy 115.15 14827248 Fixed PAYE Aggregation issues on NI Records.
2-Nov -2012 ssarap 115.15 14827248 Added new procedure for NI Only aggregation.
5-Nov -2012 ssarap 115.16 14827248 Added new procedure for NI Only aggregation.
21-Nov-2012 rajganga 115.17 14827248 Added for reversal.
22-Nov-2012 rajganga 115.18 14827248 Added out parameter to fetch_HASH_FPS_PER.
26-Dec-2012 pbalu 115.20 15903040 Sequenced the Hash code and Random digit generation. Also the fetch
of hash/random digit will start only after insert completed.
21-Feb-2013 pbalu 115.21 16268879 Fine Tuned the Master Insert, as it creates problem with certain BACS run.
21-Feb-2013 pbalu 115.22 16268879 check_patch errors
21-Feb-2013 pbalu 115.23 16268879 check_patch errors - Some more
04-Mar-2013 pbalu 115.24 16386622 NI adjustment logic modified to check for ER run level values.
11-Mar-2013 rajganga 115.25 16402171 Added fnd_canonical date conversion.
15-Mar-2013 ssarap 115.26 16496657 Changed the data type of number(15) to number(15,2).
19-Mar-2013 pbalu 115.27 16510056 Random digit fetching for BACS modified
26-Mar-2013 ssarap 115.28 16306737 Added a third party prepayment check for pay_pre_payments tables
in function FPS_BACS_PREPROCESS.
29-Mar-2013 ssarap 115.30 16555308 Added the assignment id OUT
param to fetch_HASH_FPS_PER. Fetch the assignment id of largest bacs.
=============================================================================*/
--
--
-- Declare variables to hold NI Defined balance id's
g_nia_able_id pay_defined_balances.defined_balance_id%TYPE;
SELECT defined_balance_id
FROM pay_defined_balances db,
pay_balance_types b,
pay_balance_dimensions d
WHERE b.balance_name = p_balance_name
AND d.dimension_name = p_dimension_name
AND db.balance_type_id = b.balance_type_id
AND db.balance_dimension_id = d.balance_dimension_id;
SELECT defined_balance_id
FROM pay_defined_balances db,
pay_balance_types b,
pay_balance_dimensions d
WHERE b.balance_name = p_balance_name
AND d.dimension_name = p_dimension_name
AND db.balance_type_id = b.balance_type_id
AND db.balance_dimension_id = d.balance_dimension_id;
SELECT ASSIGNMENT_ACTION_ID
FROM pay_assignment_actions
WHERE SOURCE_ACTION_ID = c_lst_act_id;
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
WHERE pbt.balance_name = c_balance_name
AND pbd.database_item_suffix = c_dim_name
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.legislation_code = 'GB';
SELECT hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id)
FROM dual;
select ACTION_TYPE from pay_assignment_actions paa, pay_payroll_actions ppa
where paa.assignment_action_id = l_last_asg_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ACTION_TYPE in ('V');
SELECT ASSIGNMENT_ACTION_ID
FROM pay_assignment_actions
WHERE SOURCE_ACTION_ID = c_lst_act_id;
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
WHERE pbt.balance_name = c_balance_name
AND pbd.database_item_suffix = c_dim_name
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.legislation_code = 'GB';
SELECT hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id)
FROM dual;
select ACTION_TYPE from pay_assignment_actions paa, pay_payroll_actions ppa
where paa.assignment_action_id = l_last_asg_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ACTION_TYPE in ('V');
SELECT ASSIGNMENT_ACTION_ID
FROM pay_assignment_actions
WHERE SOURCE_ACTION_ID = c_lst_act_id;
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
WHERE pbt.balance_name = c_balance_name
AND pbd.database_item_suffix = c_dim_name
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.legislation_code = 'GB';
SELECT hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id)
FROM dual;
select ACTION_TYPE from pay_assignment_actions paa, pay_payroll_actions ppa
where paa.assignment_action_id = l_last_asg_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ACTION_TYPE in ('V');
select balance_type_id into l_bal_type_id from pay_balance_types where balance_name = f_balance_name
and legislation_code = 'GB';
select sum(target.result_value)
into
l_value
from
pay_assignment_actions BAL_ASSACT
,pay_payroll_actions BACT
,per_time_periods BPTP
,pay_assignment_actions ASSACT
,pay_payroll_actions PACT
,per_time_periods PPTP
,pay_run_results RR
,pay_run_result_values TARGET
,pay_balance_feeds_f FEED
where BAL_ASSACT.assignment_action_id = f_asg_act_id
and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
and FEED.balance_type_id = l_bal_type_id + decode(TARGET.input_value_id, 0, 0, 0)
and FEED.input_value_id = TARGET.input_value_id
and TARGET.run_result_id = RR.run_result_id
and RR.assignment_action_id = ASSACT.assignment_action_id
and ASSACT.payroll_action_id = PACT.payroll_action_id
and PACT.effective_date between
FEED.effective_start_date and FEED.effective_end_date
and RR.status in ('P','PA')
and BPTP.time_period_id = BACT.time_period_id
and PPTP.time_period_id = PACT.time_period_id
and PPTP.regular_payment_date >= /* fin year start */
( to_date('06-04-' || to_char( to_number(
to_char( BPTP.regular_payment_date,'YYYY'))
+ decode(sign( BPTP.regular_payment_date - to_date('06-04-'
|| to_char(BPTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
-1,-1,0)),'DD-MM-YYYY'))
and PPTP.regular_payment_date >= /* Termination check for RTI NI Reporting assignment. */
( nvl(
(select max(fnd_date.canonical_to_date(px.AEI_INFORMATION2)) from per_all_assignments_f paa1, per_all_assignments_f paa2,
per_assignment_extra_info PX
where paa1.assignment_id = BAL_ASSACT.assignment_id
and paa1.person_id = paa2.person_id
and PX.assignment_id = paa2.assignment_id
and PX.AEI_INFORMATION1 = 'Y'
and PX.AEI_INFORMATION_CATEGORY = 'GB_RTI_AGGREGATION'
and paa2.ASSIGNMENT_STATUS_TYPE_ID = (select ASSIGNMENT_STATUS_TYPE_ID from per_assignment_status_types
where USER_STATUS = 'Terminate Assignment')
and fnd_date.canonical_to_date(PX.AEI_INFORMATION2) between ( to_date('06-04-' || to_char( to_number(
to_char( BPTP.regular_payment_date,'YYYY'))
+ decode(sign( BPTP.regular_payment_date - to_date('06-04-'
|| to_char(BPTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
-1,-1,0)),'DD-MM-YYYY')) and BPTP.regular_payment_date ), PPTP.regular_payment_date)
)
and PACT.effective_date >=
/* find the latest td payroll transfer date - compare each of the */
/* assignment rows with its predecessor looking for the payroll */
/* that had a different tax district at that date */
( select /*+ ordered use_nl(ASS PASS NROLL FLEX PROLL PFLEX)
INDEX(NROLL PAY_PAYROLLS_F_PK)
INDEX(PROLL PAY_PAYROLLS_F_PK) */
nvl(max(ASS.effective_start_date),
to_date('01/01/0001','DD/MM/YYYY'))
from per_assignments_f ASS
,per_assignments_f PASS /* previous assignment */
,pay_payrolls_f NROLL
,hr_soft_coding_keyflex FLEX
,pay_payrolls_f PROLL
,hr_soft_coding_keyflex PFLEX
where ASS.assignment_id = BAL_ASSACT.assignment_id
and NROLL.payroll_id = ASS.payroll_id
and ASS.effective_start_date between
NROLL.effective_start_date and NROLL.effective_end_date
and NROLL.soft_coding_keyflex_id = FLEX.soft_coding_keyflex_id
and ASS.assignment_id = PASS.assignment_id
and PASS.effective_end_date = (ASS.effective_start_date - 1)
and ASS.effective_start_date <= BACT.effective_date
and PROLL.payroll_id = PASS.payroll_id
and ASS.effective_start_date between
PROLL.effective_start_date and PROLL.effective_end_date
and PROLL.soft_coding_keyflex_id = PFLEX.soft_coding_keyflex_id
and ASS.payroll_id <> PASS.payroll_id
and FLEX.segment1 <> PFLEX.segment1
)
and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and ASSACT.assignment_id = BAL_ASSACT.assignment_id;
SELECT ASSIGNMENT_ACTION_ID
FROM pay_assignment_actions
WHERE SOURCE_ACTION_ID = c_lst_act_id;
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
WHERE pbt.balance_name = c_balance_name
AND pbd.database_item_suffix = c_dim_name
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.legislation_code = 'GB';
SELECT hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id)
FROM dual;
select ACTION_TYPE from pay_assignment_actions paa, pay_payroll_actions ppa
where paa.assignment_action_id = l_last_asg_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ACTION_TYPE in ('V');
SELECT ASSIGNMENT_ACTION_ID
FROM pay_assignment_actions
WHERE SOURCE_ACTION_ID = c_lst_act_id;
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
WHERE pbt.balance_name = c_balance_name
AND pbd.database_item_suffix = c_dim_name
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.legislation_code = 'GB';
SELECT hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id)
FROM dual;
SELECT count( distinct PAA.PRE_PAYMENT_PAYROLL_ACTION_ID) into l_dummy FROM
(
SELECT DISTINCT payroll_action_id PRE_PAYMENT_PAYROLL_ACTION_ID
FROM pay_payroll_actions
WHERE payroll_action_id IN
(
SELECT DISTINCT payroll_action_id
FROM pay_assignment_actions
WHERE assignment_action_id IN
(
SELECT LOCKED_ACTION_ID
FROM pay_action_interlocks
WHERE locking_action_id IN
(
SELECT ASSIGNMENT_ACTION_ID
FROM pay_assignment_actions
WHERE payroll_action_id = L_PAYROLL_ACTION_ID
)
)
)
) PAA, PAY_GB_BACS_FPS BACS_FPS
WHERE PAA.PRE_PAYMENT_PAYROLL_ACTION_ID is not null
and PAA.PRE_PAYMENT_PAYROLL_ACTION_ID = BACS_FPS.PRE_PAYMENT_PAYROLL_ACTION_ID
and BACS_FPS.PRE_PAYMENT_ID is null
AND nvl(BACS_FPS.RANDOM_STRING,'no data') <> 'COMP';
HR_UTILITY.TRACE('All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
fnd_file.put_line(FND_FILE.LOG,'All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
fnd_file.put_line(FND_FILE.LOG,'Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
SELECT RANDOM_STRING
INTO L_RANDOM
FROM PAY_GB_BACS_FPS bacs,
pay_assignment_actions paa
WHERE paa.payroll_action_id = L_PAYROLL_ACTION_ID -- CONTEXT (BACS payroll action id)
and paa.ASSIGNMENT_action_id = L_ASSIGNMENT_ACTION_ID -- CONTEXT (BACS ASSIGNMENT action id)
AND paa.pre_payment_id = bacs.pre_payment_id
AND BACS.ORG_SORT_CODE = P_ORG_SORT_CODE --PARAMETER
AND BACS.RECEIPIENT_SORT_CODE = P_RECEIPIENT_SORT_CODE --PARAMETER
AND BACS.AMOUNT = P_AMOUNT ; --PARAMETER
FUNCTION Insert_master(
P_PAYROLL_ACTION_ID IN NUMBER,
P_PROGRAM IN VARCHAR2,
l_insert_status OUT NOCOPY NUMBER)
RETURN NUMBER
IS
L_DUMMY NUMBER := 0;
hr_utility.trace(' Inserting Master Record in PAY_GB_BACS_FPS ');
fnd_file.put_line(FND_FILE.LOG,' Master Insert start at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
SELECT DISTINCT PRE_PAYMENT_PAYROLL_ACTION_ID
INTO L_PAY_GB_BACS_FPS
FROM PAY_GB_BACS_FPS
WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID;
hr_utility.trace(' Second check - Data present in PAY_GB_BACS_FPS - No insert happened');
fnd_file.put_line(FND_FILE.LOG,' Second check - Data present in PAY_GB_BACS_FPS - No insert happened at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
INSERT
INTO PAY_GB_BACS_FPS
(
PRE_PAYMENT_PAYROLL_ACTION_ID,
CREATION_DATE,
TRIGGERED_BY
)
VALUES
(
P_PAYROLL_ACTION_ID,
SYSDATE,
P_PROGRAM
);
COMMIT; -- 14807372 This commit is needed so that other threads know that insert
l_insert_status := 5; --This means insert of master record happened.
hr_utility.trace(' Master Record insert completed ');
fnd_file.put_line(FND_FILE.LOG,' Master Insert completed at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
END INSERT_MASTER;
SELECT PRE_PAYMENT_PAYROLL_ACTION_ID INTO L_DUMMY
FROM PAY_GB_BACS_FPS
WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID
AND PRE_PAYMENT_ID is null
AND RANDOM_STRING = 'COMP';
HR_UTILITY.TRACE(L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
fnd_file.put_line(FND_FILE.LOG,L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
fnd_file.put_line(FND_FILE.LOG,'Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
SELECT HASH,
AMOUNT
INTO L_HASH,
L_AMOUNT
FROM PAY_GB_BACS_FPS
WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = p_PAYROLL_ACTION_ID
AND ASSIGNMENT_ID = p_ASSIGNMENT_ID ;
SELECT PRE_PAYMENT_PAYROLL_ACTION_ID INTO L_DUMMY
FROM PAY_GB_BACS_FPS
WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID
AND PRE_PAYMENT_ID is null
AND RANDOM_STRING = 'COMP';
HR_UTILITY.TRACE(L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
fnd_file.put_line(FND_FILE.LOG,L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
fnd_file.put_line(FND_FILE.LOG,'Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
SELECT HASH,AMOUNT,ASSIGNMENT_ID
INTO L_HASH,L_AMOUNT,L_ASG_ID
FROM PAY_GB_BACS_FPS
WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = p_PAYROLL_ACTION_ID
AND PERSON_ID = p_PERSON_ID ;
l_insert_bacs NUMBER;
l_insert_status NUMBER;
SELECT paaf.business_group_id,
flex.segment1
FROM pay_assignment_actions paa,
per_all_assignments_f paaf,
hr_soft_coding_keyflex flex,
pay_payrolls_f ppf,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = L_PAYROLL_ACTION_ID
AND paa.payroll_action_id = ppa.payroll_action_id
AND paaf.assignment_id = paa.assignment_id
AND paaf.payroll_id = ppf.payroll_id
AND ppf.soft_coding_keyflex_id = flex.SOFT_CODING_KEYFLEX_ID
AND c_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND c_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
SELECT DISTINCT payroll_action_id
FROM pay_payroll_actions
WHERE payroll_action_id IN
( SELECT DISTINCT payroll_action_id
FROM pay_assignment_actions
WHERE assignment_action_id IN
(SELECT LOCKED_ACTION_ID
FROM pay_action_interlocks
WHERE locking_action_id IN
(SELECT ASSIGNMENT_ACTION_ID
FROM pay_assignment_actions
WHERE payroll_action_id = B_PAYROLL_ACTION_ID
)
)
)
ORDER BY payroll_action_id Nulls Last;
SELECT DISTINCT payroll_action_id
FROM pay_payroll_actions
WHERE payroll_action_id = P_PAYROLL_ACTION_ID
ORDER BY payroll_action_id Nulls Last;
l_insert_bacs := NULL;
SELECT effective_date
INTO l_effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = L_PAYROLL_ACTION_ID;
SELECT 1
INTO l_insert_bacs
FROM pqp_configuration_values
WHERE business_group_id = l_business_group_id
AND pcv_information_category = 'PAY_GB_RTI_FPS_BACS'
AND pcv_information1 = l_tax_ref
AND l_effective_date >= fnd_date.canonical_to_date(PCV_INFORMATION2);
IF l_insert_bacs IS NOT NULL OR l_uptake_value = 'ALL' THEN
BEGIN
fnd_file.put_line(FND_FILE.LOG,' Check for PAYROLL_ACTION_ID '||L_PAYROLL_ACTION_ID);
SELECT DISTINCT PRE_PAYMENT_PAYROLL_ACTION_ID
INTO L_PAY_GB_BACS_FPS
FROM PAY_GB_BACS_FPS
WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID;
hr_utility.trace(' Data present in PAY_GB_BACS_FPS - No insert happened');
fnd_file.put_line(FND_FILE.LOG,' Data present in PAY_GB_BACS_FPS - No insert happened');
l_insert_status := 0;
fnd_file.put_line(FND_FILE.LOG,' Data not present in PAY_GB_BACS_FPS- Calling Master insert');
l_return := Insert_master(L_PAYROLL_ACTION_ID,P_PROGRAM,l_insert_status);
fnd_file.put_line(FND_FILE.LOG,' Master insert completed - Moving to other inserts');
IF l_insert_status = 5 THEN --15903040
--l_insert_status = 5 means Insert_master inserted the master row in this thread/session
--so below insert should be executed.
fnd_file.put_line(FND_FILE.LOG,' Main insert started at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
INSERT
INTO PAY_GB_BACS_FPS
(
PRE_PAYMENT_PAYROLL_ACTION_ID,
PRE_PAYMENT_ID,
PERSON_ID,
ASSIGNMENT_ID,
AGGREGATION_FLAG,
ORG_SORT_CODE,
RECEIPIENT_SORT_CODE,
AMOUNT,
RANDOM_STRING,
HASH,
CREATION_DATE,
TRIGGERED_BY
)
SELECT bacs.PAYROLL_ACTION_ID,
bacs.PRE_PAYMENT_ID,
bacs.PERSON_ID,
bacs.ASSIGNMENT_ID,
bacs.Aggregation,
bacs.Org_sort_code,
bacs.Receipient_sort_code,
bacs.Amount,
bacs.RandomStr,
PAY_GB_FPS_NI_AND_OTHERS.FPS_HASH(bacs.RandomStr,bacs.Org_sort_code, bacs.Receipient_sort_code,bacs.Amount) Hash,
SYSDATE,
P_PROGRAM
FROM
(SELECT lpad(Orgkey.segment3,6,'0') Org_sort_code,
lpad(deskey.segment3,6,'0') Receipient_sort_code,
PAA.PAYROLL_ACTION_ID ,
ppp.PRE_PAYMENT_ID,
PAPF.PERSON_ID,
PAA.ASSIGNMENT_ID,
lpad(ppp.value*100,11,'0') Amount,
PAPF.PER_information10 Aggregation,
DECODE(PAPF.PER_information10,'Y',RANK() OVER (PARTITION BY PAA.PAYROLL_ACTION_ID,PAPF.PERSON_ID order by ppp.value DESC),RANK() OVER (PARTITION BY PAA.PAYROLL_ACTION_ID,PAA.ASSIGNMENT_ID order by ppp.value DESC)) highest,
PAY_GB_FPS_NI_AND_OTHERS.BACS_RANDOM_DIGIT RandomStr
FROM pay_pre_payments ppp,
pay_payment_types ppt,
pay_org_payment_methods_f popf,
pay_personal_payment_methods_f pppf,
pay_external_accounts Orgkey,
pay_external_accounts deskey,
PAY_ASSIGNMENT_ACTIONS PAA,
PER_ALL_PEOPLE_F PAPF,
per_all_assignments_f paaf,
pay_payroll_actions ppa
WHERE popf.ORG_PAYMENT_METHOD_ID = ppp.ORG_PAYMENT_METHOD_ID
AND pppf.personal_payment_method_ID = ppp.personal_payment_method_ID
AND popf.PAYMENT_TYPE_ID = ppt.PAYMENT_TYPE_ID
AND ppt.PAYMENT_TYPE_NAME = 'BACS Tape'
AND popf.defined_balance_id is not null --Bug#16306737
AND orgkey.external_account_id = popf.external_account_id
AND deskey.external_account_id = pppf.external_account_id
AND ppa.effective_date BETWEEN popf.effective_start_date AND popf.effective_end_date
AND ppa.effective_date BETWEEN pppf.effective_start_date AND pppf.effective_end_date
AND ppp.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAA.PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
AND PAAF.PERSON_ID = PAPF.PERSON_ID
AND ppa.effective_date BETWEEN PAAF.effective_start_date AND PAAF.effective_end_date
AND ppa.effective_date BETWEEN PAPF.effective_start_date AND PAPF.effective_end_date
) BACS
WHERE BACS.highest = 1
AND 1 = -- This check is to prevent inserts from multiple programs at the same time
(SELECT COUNT(*)
FROM PAY_GB_BACS_FPS
WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID
);
hr_utility.trace(' All insert completed for '||L_PAYROLL_ACTION_ID);
fnd_file.put_line(FND_FILE.LOG,' Main insert completed at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
--This update statement will be fired once.
--This will indicate all inserts for the payroll action id is completed.
update PAY_GB_BACS_FPS
set RANDOM_STRING = 'COMP'
where PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID
and RANDOM_STRING is null
and PRE_PAYMENT_ID is null;
END IF;-- check for l_insert_status = 5
DELETE
FROM PAY_GB_BACS_FPS
WHERE rowid NOT IN
(SELECT MIN(rowid)
FROM PAY_GB_BACS_FPS
WHERE NVL(AGGREGATION_FLAG,'N')= 'N'
GROUP BY PRE_PAYMENT_PAYROLL_ACTION_ID,
PERSON_ID,
ASSIGNMENT_ID,
AMOUNT
HAVING COUNT(*) > 1
)
AND (PRE_PAYMENT_PAYROLL_ACTION_ID,PERSON_ID,ASSIGNMENT_ID,AMOUNT) IN
(SELECT PRE_PAYMENT_PAYROLL_ACTION_ID,
PERSON_ID,
ASSIGNMENT_ID,
AMOUNT
FROM PAY_GB_BACS_FPS
WHERE NVL(AGGREGATION_FLAG,'N')= 'N'
GROUP BY PRE_PAYMENT_PAYROLL_ACTION_ID,
PERSON_ID,
ASSIGNMENT_ID,
AMOUNT
HAVING COUNT(*) > 1
);
hr_utility.trace(' Deleted Non aggregated duplicate rows for '||L_PAYROLL_ACTION_ID);
DELETE
FROM PAY_GB_BACS_FPS
WHERE rowid NOT IN
(SELECT MIN(rowid)
FROM PAY_GB_BACS_FPS
WHERE NVL(AGGREGATION_FLAG,'N') = 'Y'
-- AND PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID -- To Delete all Payroll actions' duplicates
GROUP BY PRE_PAYMENT_PAYROLL_ACTION_ID,
PERSON_ID,
AMOUNT
HAVING COUNT(*) > 1
)
AND (PRE_PAYMENT_PAYROLL_ACTION_ID, PERSON_ID, AMOUNT) IN
(SELECT PRE_PAYMENT_PAYROLL_ACTION_ID,
PERSON_ID,
AMOUNT
FROM PAY_GB_BACS_FPS
WHERE NVL(AGGREGATION_FLAG,'N') = 'Y'
-- AND PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID -- To Delete all Payroll actions' duplicates
GROUP BY PRE_PAYMENT_PAYROLL_ACTION_ID,
PERSON_ID,
AMOUNT
HAVING COUNT(*) > 1
);
hr_utility.trace(' Deleted aggregated duplicate rows for '||L_PAYROLL_ACTION_ID);
DELETE
FROM PAY_GB_BACS_FPS
WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID;
END IF; -- end for if l_insert_bacs = true OR l_uptake_value = 'ALL'
SELECT object_version_number,
assignment_extra_info_id
FROM per_assignment_extra_info
WHERE assignment_id = p_assig_id
AND information_type = p_type;
SELECT paa.assignment_id,
paa.assignment_action_id,
pai.action_information6, --EXPAT
pai.action_information7, --EXPAT
pai.action_information8, --EXPAT
pai.action_information9, --EXPAT
pai.action_information10, --EXPAT
pai.action_information12, --PENSIONER
pai.action_information4, --STARTER
pai.action_information5, --STARTER
pai.action_information20 -- Number of Periods Covered
FROM pay_assignment_actions paa,
pay_action_information pai
WHERE paa.payroll_action_id = p_pactid
AND paa.assignment_action_id = pai.action_context_id
AND pai.action_information_category = 'GB_RTI_FPS_ASG_DET1'
AND pai.action_context_type = 'AAP';
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_PAY_RTI',
p_aei_information1 => archive_rec.action_information20);
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information19 => 'N');
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information9 => 'N');
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information8 => 'N');
DELETE
FROM pay_gb_fps_details
WHERE FPS_ASG_ACT_ID = archive_rec.ASSIGNMENT_ACTION_ID
AND ASSIGNMENT_ID =archive_rec.ASSIGNMENT_ID;