The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure to update master and child record in series, ijn response to submit a process
like Pre-payments, BACS, Payslip generation, Retro
*/
PROCEDURE UPDATE_MASTER_CHILD(
P_CONCURRENT_REQUEST_ID IN NUMBER
,P_PAYROLL_GROUP_ID IN NUMBER
,P_PROCESS_NAME IN VARCHAR2
,P_REQUEST_PARAMETERS IN VARCHAR2
,P_REQUEST_SUBMISSION_DATE IN DATE
,P_CHILD_REQ PAY_GB_MULTI_PAYROLL_RUN.CHILD_REQ)
IS
BEGIN
PAY_GB_MULTI_DML_AND_VAL.ADD_MASTER_CON_DETAILS(
P_CONCURRENT_REQUEST_ID=>P_CONCURRENT_REQUEST_ID
,P_PAYROLL_GROUP_ID=>P_PAYROLL_GROUP_ID
,P_PROCESS_NAME=>P_PROCESS_NAME
,P_REQUEST_PARAMETERS=>P_REQUEST_PARAMETERS
,P_REQUEST_SUBMISSION_DATE=>SYSDATE
);
END UPDATE_MASTER_CHILD;
SELECT PTP.TIME_PERIOD_ID INTO
V_TIME_PERIOD_ID
FROM PER_TIME_PERIODS PTP
WHERE PTP.PERIOD_NAME=P_TIME_PERIOD_NAME
AND PTP.PAYROLL_ID=P_PAYROLL_ID;
Call to update the master and child record
*/
PAY_GB_MULTI_PAYROLL_RUN.UPDATE_MASTER_CHILD(
P_CONCURRENT_REQUEST_ID=>PAR_REQ_ID
,P_PAYROLL_GROUP_ID =>P_PAYROLL_GROUP_ID
,P_PROCESS_NAME =>'Payroll Run UK Multiple'
,P_REQUEST_PARAMETERS =>PAR_REQ_PARAMS
,P_REQUEST_SUBMISSION_DATE =>SYSDATE
,P_CHILD_REQ =>T_CHILD_REQ);
hr_utility.trace('after inserting into master and child records:');
SELECT DISTINCT POPMF.ORG_PAYMENT_METHOD_NAME
FROM
PAY_ALL_PAYROLLS_F PAPF,
PAY_ORG_PAYMENT_METHODS_F POPMF
WHERE PAPF.DEFAULT_PAYMENT_METHOD_ID=POPMF.ORG_PAYMENT_METHOD_ID
AND PAPF.BUSINESS_GROUP_ID=POPMF.BUSINESS_GROUP_ID
AND PAPF.PAYROLL_ID=P_PAYROLL_ID;
SELECT CONSOLIDATION_SET_ID
FROM PAY_ALL_PAYROLLS_F
WHERE PAYROLL_ID=P_PAYROLL_ID;
PAY_GB_MULTI_PAYROLL_RUN.UPDATE_MASTER_CHILD(
P_CONCURRENT_REQUEST_ID=>PAR_REQ_ID
,P_PAYROLL_GROUP_ID =>P_PAYROLL_GROUP_ID
,P_PROCESS_NAME =>'Pre-payments'
,P_REQUEST_PARAMETERS =>PAR_REQ_PARAMS
,P_REQUEST_SUBMISSION_DATE =>SYSDATE
,P_CHILD_REQ =>T_CHILD_REQ);
hr_utility.trace('Child records also updated successfully');
SELECT CONSOLIDATION_SET_ID
FROM PAY_ALL_PAYROLLS_F
WHERE PAYROLL_ID=P_PAYROLL_ID;
V_AUDIT_SAMPLE_HID:='TRANSFER_SELECT_COUNT='||lpad(nvl(P_AUDIT_SAM_FREQ,'0'),4,'0');
PAY_GB_MULTI_PAYROLL_RUN.UPDATE_MASTER_CHILD(
P_CONCURRENT_REQUEST_ID=>PAR_REQ_ID
,P_PAYROLL_GROUP_ID =>P_PAYROLL_GROUP_ID
,P_PROCESS_NAME =>'Magnetic transfer'
,P_REQUEST_PARAMETERS =>PAR_REQ_PARAMS
,P_REQUEST_SUBMISSION_DATE =>SYSDATE
,P_CHILD_REQ =>T_CHILD_REQ);
SELECT COUNT(*)
FROM
PAY_PRE_PAYMENTS PPP
,PAY_ASSIGNMENT_ACTIONS PAA
,PER_ALL_ASSIGNMENTS_F PAAF
,PAY_ORG_PAYMENT_METHODS_F POPMF
,PER_TIME_PERIODS PTP
WHERE PPP.ASSIGNMENT_ACTION_ID=PAA.ASSIGNMENT_ACTION_ID
AND PAA.ASSIGNMENT_ID=PAAF.ASSIGNMENT_ID
AND PAAF.PAYROLL_ID=PTP.PAYROLL_ID
AND PAAF.PAYROLL_ID=P_PAYROLL_ID
AND PTP.START_DATE>=FND_DATE.CANONICAL_TO_DATE(P_START_DATE)
AND PTP.END_DATE<=FND_DATE.CANONICAL_TO_DATE(P_END_DATE)
AND POPMF.ORG_PAYMENT_METHOD_NAME='Cheque'
AND PPP.ORG_PAYMENT_METHOD_ID=POPMF.ORG_PAYMENT_METHOD_ID;
SELECT CONSOLIDATION_SET_ID
FROM PAY_ALL_PAYROLLS_F
WHERE PAYROLL_ID=P_PAYROLL_ID;
PAY_GB_MULTI_PAYROLL_RUN.UPDATE_MASTER_CHILD(
P_CONCURRENT_REQUEST_ID=>PAR_REQ_ID
,P_PAYROLL_GROUP_ID =>P_PAYROLL_GROUP_ID
,P_PROCESS_NAME =>'Cheque writer'
,P_REQUEST_PARAMETERS =>PAR_REQ_PARAMS
,P_REQUEST_SUBMISSION_DATE =>SYSDATE
,P_CHILD_REQ =>T_CHILD_REQ);
SELECT PCS.CONSOLIDATION_SET_ID FROM
PAY_ALL_PAYROLLS_F PAPF,
PAY_CONSOLIDATION_SETS PCS
WHERE PAPF.CONSOLIDATION_SET_ID=PCS.CONSOLIDATION_SET_ID
AND PAPF.PAYROLL_ID=P_PAYROLL_ID;
PAY_GB_MULTI_PAYROLL_RUN.UPDATE_MASTER_CHILD(
P_CONCURRENT_REQUEST_ID=>PAR_REQ_ID
,P_PAYROLL_GROUP_ID =>P_PAYROLL_GROUP_ID
,P_PROCESS_NAME =>'Magnetic report'
,P_REQUEST_PARAMETERS =>PAR_REQ_PARAMS
,P_REQUEST_SUBMISSION_DATE =>SYSDATE
,P_CHILD_REQ =>T_CHILD_REQ);
PAY_GB_MULTI_PAYROLL_RUN.UPDATE_MASTER_CHILD(
P_CONCURRENT_REQUEST_ID=>PAR_REQ_ID
,P_PAYROLL_GROUP_ID =>P_PAYROLL_GROUP_ID
,P_PROCESS_NAME =>'Retropay by Element'
,P_REQUEST_PARAMETERS =>PAR_REQ_PARAMS
,P_REQUEST_SUBMISSION_DATE =>SYSDATE
,P_CHILD_REQ =>T_CHILD_REQ);
SELECT CONSOLIDATION_SET_ID
FROM PAY_ALL_PAYROLLS_F
WHERE PAYROLL_ID=P_PAYROLL_ID;
SELECT PCMCRD.CHILD_CONCURRENT_REQUEST_ID CON_ID
FROM
PAY_GB_MP_CHILD_REQUEST_DET PCMCRD
WHERE PCMCRD.PARENT_CONCURRENT_REQUEST_ID=P_PARENT_REQ_ID;
SELECT PPA.PAYROLL_ACTION_ID
FROM PAY_PAYROLL_ACTIONS PPA WHERE PPA.REQUEST_ID=PROCESS_NAME;
PAY_GB_MULTI_DML_AND_VAL.UPDATE_MASTER_ROLLBACK_DETAILS
(
P_MASTER_REQUEST_ID=>P_PARENT_REQ_ID
,P_PAYROLL_GROUP_ID=>P_PAYROLL_GROUP_ID
,P_ROLLBACK_REQUEST_ID=>PAR_REQ_ID
);
PAY_GB_MULTI_DML_AND_VAL.UPDATE_CHILD_ROLLBACK_DETAILS
(
P_MASTER_REQUEST_ID=>P_PARENT_REQ_ID
,P_PAYROLL_GROUP_ID=>P_PAYROLL_GROUP_ID
,P_ROLLBACK_REQUEST_ID=>T_ROLLBACK_REQ(P)
,P_CHILD_REQUEST_ID=>T_CHILD_REQ(P)
);
PAY_GB_MULTI_DML_AND_VAL.UPDATE_ALREADY_ROLLEDBACK(P_REQUEST_ID=>T_CHILD_REQ_AL(P));
SELECT PCMCRD.CHILD_CONCURRENT_REQUEST_ID CON_ID
FROM
PAY_GB_MP_CHILD_REQUEST_DET PCMCRD
WHERE PCMCRD.PARENT_CONCURRENT_REQUEST_ID=P_PARENT_REQ_ID;
SELECT PPA.PAYROLL_ACTION_ID
FROM PAY_PAYROLL_ACTIONS PPA WHERE PPA.REQUEST_ID=PROCESS_NAME;
hr_utility.trace('bEFORE MASTER UPDATE');
PAY_GB_MULTI_DML_AND_VAL.UPDATE_MASTER_ROLLBACK_DETAILS
(
P_MASTER_REQUEST_ID=>P_PROCESS_NAME
,P_PAYROLL_GROUP_ID=>P_PAYROLL_GROUP_ID
,P_ROLLBACK_REQUEST_ID=>PAR_REQ_ID
);
hr_utility.trace('BEFORE CHILD UPDATE');
PAY_GB_MULTI_DML_AND_VAL.UPDATE_CHILD_ROLLBACK_DETAILS
(
P_MASTER_REQUEST_ID=>P_PROCESS_NAME
,P_PAYROLL_GROUP_ID=>P_PAYROLL_GROUP_ID
,P_ROLLBACK_REQUEST_ID=>T_CHILD_REQ_NO_AL(P)
,P_CHILD_REQUEST_ID=>T_CHILD_REQ(P)
);
UPDATE FOR ALREADY ROLLEDBACK REQUESTS
*/
FOR P IN 0..V_AL_COUNT
LOOP
PAY_GB_MULTI_DML_AND_VAL.UPDATE_ALREADY_ROLLEDBACK(P_REQUEST_ID=>T_CHILD_REQ_AL(P));