[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_AUDIT_RECORDS
(
P_DATE_START DATE,
P_DATE_END DATE ,
P_PAYROLL_ID NUMBER,
P_EMP_ORG_ID NUMBER,
P_PERSON_ID NUMBER);
Procedure : INSERT_AUDIT_RECORDS
Type : Private
Purpose :
Generates the interface_run_id which is the unique identifier for
each payroll batch .
Makes an entry in PA_PAY_AUDIT table.
*/
PROCEDURE INSERT_AUDIT_RECORDS
(P_DATE_START DATE,
P_DATE_END DATE ,
P_PAYROLL_ID NUMBER,
P_EMP_ORG_ID NUMBER,
P_PERSON_ID NUMBER
)
IS
L_RUN_SEQUENCE NUMBER;
/* Generate interface run id and insert audit record for EBS Payroll */
FOR X IN
( SELECT DISTINCT
PPIT.PAYROLL_ACTION_ID ,
PPIT.PAYROLL_ID ,
PPIT.TIME_PERIOD_START_DATE,
PPIT.TIME_PERIOD_END_DATE ,
PPIT.TIME_PERIOD_ID ,
PPIT.SOURCE_START_DATE, /* bug 12690224*/
PPIT.SOURCE_END_DATE
FROM
PA_PAY_INTERFACE_TEMP PPIT
ORDER BY
PAYROLL_ACTION_ID
)
LOOP
BEGIN
SELECT
NVL(MAX(RUN_SEQUENCE),0) +1 ,
MAX(INTERFACE_RUN_ID)
INTO
L_RUN_SEQUENCE ,
L_MAX_RUN_ID
FROM
PA_PAY_AUDIT_ALL
WHERE
BATCH_ID = X.PAYROLL_ACTION_ID AND
PAYROLL_ID = X.PAYROLL_ID AND
TIME_PERIOD_ID = X.TIME_PERIOD_ID ;
INSERT
INTO
PA_PAY_AUDIT
(
ORG_ID ,
BATCH_ID ,
PAYROLL_ID ,
INT_EXT_INDICATOR ,
PAY_PERIOD_START_DATE,
PAY_PERIOD_END_DATE ,
SOURCE_START_DATE , /* bug 12690224*/
SOURCE_END_DATE,
INTERFACE_RUN_ID ,
TIME_PERIOD_ID ,
PAYROLL_STATUS_FLAG ,
RUN_SEQUENCE ,
PREVIOUS_RUN_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
REQUEST_ID
)
VALUES
(
G_ORG_ID ,
X.PAYROLL_ACTION_ID ,
X.PAYROLL_ID ,
'INT' ,
X.TIME_PERIOD_START_DATE ,
X.TIME_PERIOD_END_DATE ,
X.SOURCE_START_DATE , /* bug 12690224*/
X.SOURCE_END_DATE,
PA_PAY_INTERFACE_RUN_ID_S.NEXTVAL,
X.TIME_PERIOD_ID ,
'Y' ,
L_RUN_SEQUENCE ,
L_MAX_RUN_ID ,
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID ,
G_REQUEST_ID
);
/* Generate interface run id and insert audit record for Third party Payroll */
FOR X IN
( SELECT DISTINCT
PPEI.PAYROLL_BATCH_ID ,
PPEI.PAYROLL_ID ,
PPEI.TIME_PERIOD_START_DATE,
PPEI.TIME_PERIOD_END_DATE ,
PPEI.TIME_PERIOD_ID ,
PPEI.RETRO_PERIOD_START_DATE ,
PPEI.RETRO_PERIOD_END_DATE,
PPEP.PRIORITY
FROM
PA_PAY_EXT_INTERAFACE_V PPEI,
PA_PAY_EXTERNAL_PAYROLL PPEP
WHERE PPEP.PAYROLL_ID = PPEI.PAYROLL_ID AND
Trunc(PPEI.TIME_PERIOD_END_DATE) BETWEEN P_DATE_START AND P_DATE_END
AND PPEI.PAYROLL_ID = Nvl(P_PAYROLL_ID,PPEI.PAYROLL_ID)
AND PPEI.PERSON_ID = Nvl(P_PERSON_ID,PPEI.PERSON_ID)
AND PPEI.EMP_ORGANIZATION_ID = Nvl(P_EMP_ORG_ID,PPEI.EMP_ORGANIZATION_ID)
AND TRANSFER_STATUS_FLAG = 'P'
ORDER BY
PPEI.PAYROLL_BATCH_ID,
PPEP.PRIORITY
)
LOOP
BEGIN
SELECT
NVL(MAX(RUN_SEQUENCE),0) +1 ,
MAX(INTERFACE_RUN_ID)
INTO
L_RUN_SEQUENCE ,
L_MAX_RUN_ID
FROM
PA_PAY_AUDIT_ALL
WHERE
BATCH_ID = X.PAYROLL_BATCH_ID AND
PAYROLL_ID = X.PAYROLL_ID AND
TIME_PERIOD_ID = X.TIME_PERIOD_ID ;
INSERT
INTO
PA_PAY_AUDIT
(
ORG_ID ,
BATCH_ID ,
PAYROLL_ID ,
INT_EXT_INDICATOR ,
PAY_PERIOD_START_DATE,
PAY_PERIOD_END_DATE ,
INTERFACE_RUN_ID ,
TIME_PERIOD_ID ,
PAYROLL_STATUS_FLAG ,
RUN_SEQUENCE ,
PREVIOUS_RUN_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
REQUEST_ID
)
VALUES
(
G_ORG_ID ,
X.PAYROLL_BATCH_ID ,
X.PAYROLL_ID ,
'EXT' ,
X.TIME_PERIOD_START_DATE ,
X.TIME_PERIOD_END_DATE ,
PA_PAY_INTERFACE_RUN_ID_S.NEXTVAL,
X.TIME_PERIOD_ID ,
'Y' ,
L_RUN_SEQUENCE ,
L_MAX_RUN_ID ,
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID ,
G_REQUEST_ID
);
END INSERT_AUDIT_RECORDS;
option selected for CP
*/
PROCEDURE INTERFACE_PAY
( errbuf OUT NOCOPY VARCHAR2
,retcode OUT NOCOPY VARCHAR2
,P_FROM_DATE IN VARCHAR2
, P_TO_DATE IN VARCHAR2
,P_STREAMLINE IN VARCHAR2
, P_PAYROLL_ID IN NUMBER
, P_EMP_ORG_ID IN NUMBER
, P_PERSON_ID IN NUMBER
) IS
l_date_from DATE;
SELECT DISTINCT PAA.INTERFACE_RUN_ID,
PAA.PAYROLL_ID,
PAA.TIME_PERIOD_ID,
PAA.PAY_PERIOD_END_DATE,
PIT.ELEMENT_TYPE_ID,
PEM.PAYROLL_PROJ_SEGMENT,
PEM.PAYROLL_TASK_SEGMENT,
PEM.PAYROLL_EXP_ORG_SEGMENT
FROM PA_PAY_AUDIT PAA,
(select distinct
PAYROLL_ACTION_ID ,
PAYROLL_ID,
TIME_PERIOD_ID,
TIME_PERIOD_END_DATE,
ELEMENT_TYPE_ID
from
PA_PAY_INTERFACE_TEMP ) PIT,
PA_PAY_ELEMENT_MAPPING PEM
WHERE
PAA.BATCH_ID = PIT.PAYROLL_ACTION_ID
AND PAA.PAYROLL_ID = PIT.PAYROLL_ID
AND PAA.TIME_PERIOD_ID = PIT.TIME_PERIOD_ID
AND PAA.org_id = PEM.ORG_ID
AND PIT.TIME_PERIOD_END_DATE between pem.start_date_active and Nvl(pem.end_date_active,PIT.TIME_PERIOD_END_DATE +1)
and PIT.ELEMENT_TYPE_ID = pem.pay_element_type_id;
SELECT sysdate INTO G_RUN_DATE FROM dual;
INSERT_AUDIT_RECORDS (l_date_from,l_date_to,P_PAYROLL_ID,P_EMP_ORG_ID,P_PERSON_ID);
FOR X IN (SELECT * FROM PA_PAY_AUDIT WHERE request_id = G_REQUEST_ID ORDER BY INTERFACE_RUN_ID)
LOOP
write_log( Log, '=========== Processing starts for =========== ' ) ;
DELETE FROM pa_pay_dist_lines
WHERE interface_run_id = G_INTERFACE_RUN_ID AND
person_id IN (SELECT DISTINCT PPS.PERSON_ID
FROM PA_PAY_REJECTIONS_ALL PPR , PA_PAY_SOURCE_AMOUNTS PPS
WHERE PPR.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND PPR.PAY_SOURCE_ID = PPS.PAY_SOURCE_ID
) ;
SELECT DISTINCT PERSON_ID,ASSIGNMENT_ID
FROM PA_PAY_SOURCE_AMOUNTS
WHERE INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND PERSON_ID NOT IN ( SELECT DISTINCT PPS.PERSON_ID
FROM PA_PAY_REJECTIONS_ALL PPR , PA_PAY_SOURCE_AMOUNTS PPS
WHERE PPR.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND PPR.PAY_SOURCE_ID = PPS.PAY_SOURCE_ID
)
)
LOOP
/* Added for bug 13681581 */
BEGIN
SAVEPOINT process_emp_pay;
INSERT
INTO PA_PAY_INTERFACE_TEMP
(
COST_TYPE,
EFFECTIVE_DATE,
BUSINESS_GROUP_ID,
CONSOLIDATION_SET_NAME,
PAYROLL_NAME,
ORGANIZATION_NAME,
LOCATION_CODE,
FULL_NAME,
FIRST_NAME,
LAST_NAME,
MIDDLE_NAMES,
EMPLOYEE_NUMBER,
NATIONAL_IDENTIFIER,
ASSIGNMENT_NUMBER,
ELEMENT_NAME,
REPORTING_NAME,
CLASSIFICATION_NAME,
INPUT_VALUE_NAME,
UOM,
OUTPUT_CURRENCY_CODE,
CREDIT_AMOUNT,
DEBIT_AMOUNT,
CONCATENATED_SEGMENTS,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
SEGMENT6,
SEGMENT7,
SEGMENT8,
SEGMENT9,
SEGMENT10,
SEGMENT11,
SEGMENT12,
SEGMENT13,
SEGMENT14,
SEGMENT15,
SEGMENT16,
SEGMENT17,
SEGMENT18,
SEGMENT19,
SEGMENT20,
SEGMENT21,
SEGMENT22,
SEGMENT23,
SEGMENT24,
SEGMENT25,
SEGMENT26,
SEGMENT27,
SEGMENT28,
SEGMENT29,
SEGMENT30,
PAYROLL_ACTION_ID,
ASSIGNMENT_ACTION_ID,
CONSOLIDATION_SET_ID,
PAYROLL_ID,
TAX_UNIT_ID,
ORGANIZATION_ID,
LOCATION_ID,
PERSON_ID,
ASSIGNMENT_ID,
CLASSIFICATION_ID,
ELEMENT_TYPE_ID,
INPUT_VALUE_ID,
RUN_RESULT_ID,
GRE_NAME,
COST_ID,
TRANSFERED_TO_PRJ,
TIME_PERIOD_ID,
TIME_PERIOD_START_DATE,
TIME_PERIOD_END_DATE,
SOURCE_CREATOR_TYPE,
SOURCE_START_DATE,
SOURCE_END_DATE
)
SELECT COST_TYPE,
EFFECTIVE_DATE,
PCPV.BUSINESS_GROUP_ID,
CONSOLIDATION_SET_NAME,
PAYROLL_NAME,
ORGANIZATION_NAME,
LOCATION_CODE,
FULL_NAME,
FIRST_NAME,
LAST_NAME,
MIDDLE_NAMES,
PCPV.EMPLOYEE_NUMBER,
NATIONAL_IDENTIFIER,
PCPV.ASSIGNMENT_NUMBER,
ELEMENT_NAME,
REPORTING_NAME,
CLASSIFICATION_NAME,
INPUT_VALUE_NAME,
UOM,
OUTPUT_CURRENCY_CODE,
CREDIT_AMOUNT,
DEBIT_AMOUNT,
CONCATENATED_SEGMENTS,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
SEGMENT6,
SEGMENT7,
SEGMENT8,
SEGMENT9,
SEGMENT10,
SEGMENT11,
SEGMENT12,
SEGMENT13,
SEGMENT14,
SEGMENT15,
SEGMENT16,
SEGMENT17,
SEGMENT18,
SEGMENT19,
SEGMENT20,
SEGMENT21,
SEGMENT22,
SEGMENT23,
SEGMENT24,
SEGMENT25,
SEGMENT26,
SEGMENT27,
SEGMENT28,
SEGMENT29,
SEGMENT30,
PAYROLL_ACTION_ID,
ASSIGNMENT_ACTION_ID,
CONSOLIDATION_SET_ID,
PCPV.PAYROLL_ID,
TAX_UNIT_ID,
PCPV.ORGANIZATION_ID,
PCPV.LOCATION_ID,
PCPV.PERSON_ID,
PCPV.ASSIGNMENT_ID,
CLASSIFICATION_ID,
ELEMENT_TYPE_ID,
INPUT_VALUE_ID,
RUN_RESULT_ID,
GRE_NAME,
COST_ID,
TRANSFERED_TO_PRJ,
TIME_PERIOD_ID,
TIME_PERIOD_START_DATE,
TIME_PERIOD_END_DATE,
SOURCE_CREATOR_TYPE,
(CASE WHEN SOURCE_CREATOR_TYPE IN ('R','RR','EE','PR','NR') THEN SOURCE_START_DATE
ELSE NULL
END), /* bug 12690224*/
(CASE WHEN SOURCE_CREATOR_TYPE IN ('R','RR','EE','PR','NR') THEN SOURCE_END_DATE
ELSE NULL
END)
FROM
PAY_COSTING_PROJECTS_V PCPV ,
PER_ALL_ASSIGNMENTS_F PAAF ,
hr_organization_information HOI
WHERE
PCPV.PERSON_ID = PAAF.PERSON_ID AND
PCPV.ASSIGNMENT_NUMBER = PAAF.ASSIGNMENT_NUMBER AND
HOI.organization_id = PAAF.organization_id AND
HOI.ORG_INFORMATION1 = To_Char(FND_GLOBAL.ORG_ID) AND
PCPV.TIME_PERIOD_END_DATE BETWEEN (P_FROM_DATE ) AND ( P_TO_DATE )
AND PCPV.PAYROLL_ID = Nvl(P_PAYROLL_ID,PCPV.PAYROLL_ID)
AND PCPV.PERSON_ID = Nvl(P_PERSON_ID,PCPV.PERSON_ID)
AND PCPV.ORGANIZATION_ID = Nvl(P_EMP_ORG_ID,PCPV.ORGANIZATION_ID)
AND BALANCE_OR_COST = 'C'
AND Nvl(TRANSFERED_TO_PRJ,'N' ) = 'N';
l_insert_fixed VARCHAR2(2000);
l_insert_random VARCHAR2(2000);
l_insert_fixed :=
'INSERT
INTO PA_PAY_SOURCE_AMOUNTS
( PAY_SOURCE_ID,
INTERFACE_RUN_ID,
PERSON_ID,
ASSIGNMENT_ID,
EMP_ORGANIZATION_ID,
PAY_ELEMENT_TYPE_ID,
PAY_ELEMENT_MAP_ID,
COST_TYPE_CODE,
DISTRIBUTION_BASIS_CODE,
TIMECARD_ELEMENT,
TIMECARD_EXP_TYPE,
ALLOW_MISC_FLAG,
EXPENDITURE_TYPE,
PROJ_COST_REQD,
PAYROLL_EXP_ORG_SEGMENT,
PAYROLL_PROJ_SEGMENT,
PAYROLL_TASK_SEGMENT,
PAY_CURRENCY_CODE,
ORG_ID,
REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY' ;
' SELECT
PA_PAY_SOURCE_ID_S.nextval ,INNER.* from
(Select
PAA.INTERFACE_RUN_ID,
PIT.PERSON_ID,
PIT.ASSIGNMENT_ID,
PIT.ORGANIZATION_ID ,
PIT.ELEMENT_TYPE_ID,
PEM.PAY_ELEMENT_MAP_ID,
PEM.COST_TYPE_CODE,
PEM.DISTRIBUTION_BASIS_CODE,
PEM.TIMECARD_ELEMENT,
PEM.TIMECARD_EXP_TYPE,
PEM.ALLOW_MISC_FLAG,
PEM.EXPENDITURE_TYPE,
PEM.PROJ_COST_REQD,
PEM.PAYROLL_EXP_ORG_SEGMENT,
PEM.PAYROLL_PROJ_SEGMENT,
PEM.PAYROLL_TASK_SEGMENT,
PIT.OUTPUT_CURRENCY_CODE,
458 ORG,
FND_GLOBAL.CONC_REQUEST_ID,
trunc(sysdate)L_UPDATE_DATE,
fnd_global.user_id L_UPDATE_BY,
trunc(sysdate) C_UPDATE_DATE,
fnd_global.user_id C_UPDATE_BY,';
l_insert_random := ' , PAY_SOURCE_PROJECT_ID';
l_insert_random := l_insert_random || ' , PAY_SOURCE_TASK_ID';
l_insert_random := l_insert_random || ' , PAY_SOURCE_ORGANIZATION_ID';
l_insert_random := l_insert_random || ' , PAY_AMOUNT )';
l_sql_final := l_insert_fixed ||l_insert_random || l_sql_sel_fixed ||l_sql_sel_random || l_sql_from_where ||l_sql_goup_Fixed || l_sql_goup_random|| ') INNER';
INSERT
INTO
PA_PAY_SOURCE_AMOUNTS
(
PAY_SOURCE_ID ,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
EMP_ORGANIZATION_ID ,
PAY_ELEMENT_TYPE_CODE ,
PAY_ELEMENT_MAP_ID ,
COST_TYPE_CODE ,
DISTRIBUTION_BASIS_CODE,
TIMECARD_ELEMENT ,
TIMECARD_EXP_TYPE ,
ALLOW_MISC_FLAG ,
EXPENDITURE_TYPE ,
PROJ_COST_REQD ,
PAY_SOURCE_ORGANIZATION_ID ,
PAY_SOURCE_PROJECT_ID ,
PAY_SOURCE_TASK_ID ,
PAY_CURRENCY_CODE ,
ORG_ID ,
REQUEST_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
PAY_AMOUNT
)
SELECT
PA_PAY_SOURCE_ID_S.nextval ,
INNER.*
FROM
(
SELECT
PAA.INTERFACE_RUN_ID ,
PPEI.PERSON_ID ,
PPEI.ASSIGNMENT_ID ,
PPEI.EMP_ORGANIZATION_ID ,
PPEI.PAY_ELEMENT_TYPE_CODE ,
PEM.PAY_ELEMENT_MAP_ID ,
PEM.COST_TYPE_CODE ,
PEM.DISTRIBUTION_BASIS_CODE ,
PEM.TIMECARD_ELEMENT ,
PEM.TIMECARD_EXP_TYPE ,
PEM.ALLOW_MISC_FLAG ,
PEM.EXPENDITURE_TYPE ,
PEM.PROJ_COST_REQD ,
PPEI.PAY_SOURCE_ORGANIZATION_ID ,
PPEI.PAY_SOURCE_PROJECT_ID ,
PPEI.PAY_SOURCE_TASK_ID ,
PPEI.PAY_CURRENCY_CODE ,
G_ORG_ID ,
FND_GLOBAL.CONC_REQUEST_ID ,
TRUNC(sysdate)L_UPDATE_DATE ,
fnd_global.user_id L_UPDATE_BY,
TRUNC(sysdate) C_UPDATE_DATE ,
fnd_global.user_id C_UPDATE_BY,
SUM(PAY_AMOUNT) PAY_AMOUNT
FROM
PA_PAY_AUDIT PAA ,
PA_PAY_EXT_INTERAFACE_V PPEI,
PA_PAY_ELEMENT_MAPPING PEM
WHERE
PAA.REQUEST_ID = G_REQUEST_ID AND
PAA.BATCH_ID = PPEI.PAYROLL_BATCH_ID AND
PAA.PAYROLL_ID = PPEI.PAYROLL_ID AND
PAA.TIME_PERIOD_ID = PPEI.TIME_PERIOD_ID AND
PPEI.PAY_ELEMENT_TYPE_CODE = PEM.PAY_ELEMENT_CODE AND
/*PPEI.ELEMENT_TYPE_ID = :C_ELEMENT_TYPE_ID AND */
--For third party , every thign shd run at one shot
(
PPEI.TIME_PERIOD_END_DATE BETWEEN PEM.START_DATE_ACTIVE
AND NVL(PEM.END_DATE_ACTIVE,PPEI.TIME_PERIOD_END_DATE +
1 )
)
GROUP BY
PAA.INTERFACE_RUN_ID ,
PPEI.PERSON_ID ,
PPEI.ASSIGNMENT_ID ,
PPEI.EMP_ORGANIZATION_ID ,
PPEI.PAY_ELEMENT_TYPE_CODE ,
PEM.PAY_ELEMENT_MAP_ID ,
PEM.COST_TYPE_CODE ,
PEM.DISTRIBUTION_BASIS_CODE,
PEM.TIMECARD_ELEMENT ,
PEM.TIMECARD_EXP_TYPE ,
PEM.ALLOW_MISC_FLAG ,
PEM.EXPENDITURE_TYPE ,
PEM.PROJ_COST_REQD ,
PPEI.PAY_SOURCE_ORGANIZATION_ID ,
PPEI.PAY_SOURCE_PROJECT_ID ,
PPEI.PAY_SOURCE_TASK_ID ,
PPEI.PAY_CURRENCY_CODE
)
INNER;
INSERT
INTO
PA_PAY_REJECTIONS_ALL
(
PAY_REJECTION_ID ,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
REJECTION_CODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
)
SELECT
PA_PAY_REJECTION_ID_S.NEXTVAL,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
Pay_source_id ,
PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
'RC 10' ,
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID
FROM
PA_PAY_SOURCE_AMOUNTS
WHERE
PROJ_COST_REQD = 'YES' AND
/* PAYROLL_PROJ_SEGMENT IS NULL AND Bug 12678472 */
PAY_SOURCE_PROJECT_ID IS NULL AND
REQUEST_ID = G_REQUEST_ID;
INSERT
INTO
PA_PAY_REJECTIONS_ALL
(
PAY_REJECTION_ID ,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
REJECTION_CODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
)
SELECT
PA_PAY_REJECTION_ID_S.NEXTVAL,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
'RC 15' ,
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID
FROM
PA_PAY_SOURCE_AMOUNTS
WHERE
DISTRIBUTION_BASIS_CODE = 'NONE' AND
(
PAY_SOURCE_PROJECT_ID IS NULL OR
PAY_SOURCE_TASK_ID IS NULL
)
AND
REQUEST_ID = G_REQUEST_ID;
INSERT
INTO
PA_PAY_REJECTIONS_ALL
(
PAY_REJECTION_ID ,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID ,
REJECTION_CODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
)
SELECT
PA_PAY_REJECTION_ID_S.NEXTVAL,
PPS.INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
Pay_source_id ,
PAY_ELEMENT_TYPE_ID ,
'RC 24' ,
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID
FROM
PA_PAY_SOURCE_AMOUNTS PPS, PA_PAY_AUDIT PPA
WHERE
PPS.INTERFACE_RUN_ID = PPA.INTERFACE_RUN_ID AND
PPA.SOURCE_START_DATE IS NULL AND
PPS.PAY_AMOUNT < 0 AND
NVL(PPS.ALLOW_MISC_FLAG,'NO') ='NO' AND /*Added for bug#13595751*/
PPS.REQUEST_ID = G_REQUEST_ID;
INSERT
INTO
PA_PAY_REJECTIONS_ALL
(
PAY_REJECTION_ID ,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
REJECTION_CODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
)
SELECT
PA_PAY_REJECTION_ID_S.NEXTVAL,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
Pay_source_id ,
PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
'RC 70' ,
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID
FROM
PA_PAY_SOURCE_AMOUNTS PPSA
WHERE
PAY_SOURCE_PROJECT_ID IS NOT NULL AND
PAY_SOURCE_TASK_ID IS NOT NULL AND
NOT EXISTS
(
SELECT
1
FROM
PA_PROJECTS PPA ,
PA_TASKS PTA
WHERE
PPA.PROJECT_ID = PPSA.PAY_SOURCE_PROJECT_ID AND
PTA.TASK_ID = PPSA.PAY_SOURCE_TASK_ID AND
PTA.PROJECT_ID = PPA.PROJECT_ID AND /*12824209 */
PA_TASK_UTILS.CHECK_CHILD_EXISTS(PTA.TASK_ID) = 0
)
AND
REQUEST_ID = G_REQUEST_ID;
(SELECT
INTERFACE_RUN_ID ,
TRUNC(Nvl(SOURCE_START_DATE,PAY_PERIOD_START_DATE))START_DATE ,
TRUNC(Nvl(SOURCE_END_DATE,PAY_PERIOD_END_DATE)) END_DATE
FROM
PA_PAY_AUDIT
WHERE
REQUEST_ID = G_REQUEST_ID
)
LOOP
-- Bug 12783959
-- RC 16
-- Reject the Payroll if there exists a TC with Payroll amount and that is not yet costed.
write_log(Log,'Checking uncosted payroll amounts in ' || X.START_DATE || '-' || X.END_DATE);
INSERT
INTO
PA_PAY_REJECTIONS_ALL
(
PAY_REJECTION_ID ,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID ,
REJECTION_CODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
)
SELECT
PA_PAY_REJECTION_ID_S.NEXTVAL,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID ,
'RC 16' ,
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID
FROM
PA_PAY_SOURCE_AMOUNTS
WHERE
PERSON_ID IN
(
SELECT
EXP.INCURRED_BY_PERSON_ID
FROM
PA_EXPENDITURE_ITEMS ITEM,
PA_EXPENDITURES EXP
WHERE
EXP.EXPENDITURE_ID = ITEM.EXPENDITURE_ID AND
ITEM.SYSTEM_LINKAGE_FUNCTION IN ('OT', 'ST') AND
EXP.INCURRED_BY_PERSON_ID IN
(
SELECT DISTINCT
PERSON_ID
FROM
PA_PAY_SOURCE_AMOUNTS
WHERE
INTERFACE_RUN_ID = X.INTERFACE_RUN_ID
)
AND
(
Trunc(ITEM.EXPENDITURE_ITEM_DATE) BETWEEN X.START_DATE AND X.END_DATE
)
AND
(
ITEM.INTERFACE_RUN_ID IS NOT NULL AND
Nvl(ITEM.COST_DISTRIBUTED_FLAG,'N') = 'N'
-- Nvl(ITEM.NET_ZERO_ADJUSTMENT_FLAG,'N') = 'N'
)
)
AND INTERFACE_RUN_ID = X.INTERFACE_RUN_ID ;
INSERT
INTO
PA_PAY_REJECTIONS_ALL
(
PAY_REJECTION_ID ,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
REJECTION_CODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
)
SELECT
PA_PAY_REJECTION_ID_S.NEXTVAL,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
Pay_source_id ,
PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
'RC 20' , /*Bug 13551455*/
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID
FROM
PA_PAY_SOURCE_AMOUNTS
WHERE
PERSON_ID IN
(
SELECT
EXP.INCURRED_BY_PERSON_ID
FROM
PA_EXPENDITURE_ITEMS ITEM,
PA_EXPENDITURES EXP
WHERE
EXP.EXPENDITURE_ID = ITEM.EXPENDITURE_ID AND
EXP.EXPENDITURE_STATUS_CODE = 'APPROVED' AND
ITEM.SYSTEM_LINKAGE_FUNCTION IN ('OT', 'ST') AND
NVL(ITEM.NET_ZERO_ADJUSTMENT_FLAG,'N') <> 'Y' AND
ITEM.QUANTITY < 0 AND
EXP.INCURRED_BY_PERSON_ID IN
(
SELECT DISTINCT
PERSON_ID
FROM
PA_PAY_SOURCE_AMOUNTS
WHERE
INTERFACE_RUN_ID = X.INTERFACE_RUN_ID
)
AND
(
EXP.EXPENDITURE_ENDING_DATE BETWEEN X.START_DATE AND
X.END_DATE
)
)
AND interface_run_id = X.INTERFACE_RUN_ID;
INSERT
INTO
PA_PAY_REJECTIONS_ALL
(
PAY_REJECTION_ID ,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
REJECTION_CODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
)
SELECT
PA_PAY_REJECTION_ID_S.NEXTVAL,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
'RC 45' ,
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID
FROM
PA_PAY_SOURCE_AMOUNTS
WHERE
PERSON_ID IN
(
SELECT
EXP.INCURRED_BY_PERSON_ID
FROM
PA_EXPENDITURE_ITEMS ITEM,
PA_EXPENDITURES EXP
WHERE
EXP.EXPENDITURE_ID = ITEM.EXPENDITURE_ID AND
EXP.EXPENDITURE_STATUS_CODE = 'APPROVED' AND
ITEM.SYSTEM_LINKAGE_FUNCTION IN ('OT', 'ST') AND
NVL(ITEM.NET_ZERO_ADJUSTMENT_FLAG,'N') <> 'Y' AND
EXP.INCURRED_BY_PERSON_ID IN
(
SELECT DISTINCT
PERSON_ID
FROM
PA_PAY_SOURCE_AMOUNTS
WHERE
INTERFACE_RUN_ID = X.INTERFACE_RUN_ID
)
AND
(
EXP.EXPENDITURE_ENDING_DATE BETWEEN X.START_DATE AND
X.END_DATE
)
AND
get_cost_method(ITEM.expenditure_item_id,
ITEM.expenditure_item_date)IS NULL
) ;
INSERT
INTO
PA_PAY_REJECTIONS_ALL
(
PAY_REJECTION_ID ,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
REJECTION_CODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
)
SELECT
PA_PAY_REJECTION_ID_S.NEXTVAL,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
'RC 50' ,
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID
FROM
PA_PAY_SOURCE_AMOUNTS
WHERE
PERSON_ID IN
(
SELECT
EXP.INCURRED_BY_PERSON_ID
FROM
PA_EXPENDITURE_ITEMS ITEM,
PA_EXPENDITURES EXP
WHERE
EXP.EXPENDITURE_ID = ITEM.EXPENDITURE_ID AND
EXP.EXPENDITURE_STATUS_CODE = 'APPROVED' AND
ITEM.SYSTEM_LINKAGE_FUNCTION IN ('OT', 'ST') AND
NVL(ITEM.NET_ZERO_ADJUSTMENT_FLAG,'N') <> 'Y' AND
EXP.INCURRED_BY_PERSON_ID IN
(
SELECT DISTINCT
PERSON_ID
FROM
PA_PAY_SOURCE_AMOUNTS
WHERE
INTERFACE_RUN_ID = X.INTERFACE_RUN_ID
)
AND
(
EXP.EXPENDITURE_ENDING_DATE BETWEEN X.START_DATE AND
X.END_DATE
)
AND
(
COSTING_METHOD IS NOT NULL AND
COSTING_METHOD <> get_cost_method(
ITEM.expenditure_item_id,ITEM.expenditure_item_date)
)
) ;
INSERT
INTO
PA_PAY_REJECTIONS_ALL
(
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID,
REJECTION_CODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
)
SELECT
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
Pay_source_id ,
PAY_ELEMENT_TYPE_ID ,
'RC50' ,
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID
FROM
PA_PAY_SOURCE_AMOUNTS PPSA,
PA_PAY_AUDIT PPA
WHERE
PPSA.INTERFACE_RUN_ID = PPA.INTERFACE_RUN_ID AND
PAY_SOURCE_ORGANIZATION_ID IS NOT NULL AND
PPSA.DISTRIBUTION_BASIS_CODE in ('ST_AMT',
'OT_AMT',
'ST_OT_AMT',
'NONE')
NOT EXISTS
(SELECT
'Y'
FROM
pa_organizations_expend_v paev
WHERE
organization_id = PPSA.PAY_SOURCE_ORGANIZATION_ID AND
AND PPA.PAY_PERIOD_END_DATE between paev.date_from and paev.date_to )
REQUEST_ID = p_request_id; */
UPDATE PA_PAY_SOURCE_AMOUNTS PPS
SET PAY_REJECTION_ID =
( SELECT MAX(PAY_REJECTION_ID)
FROM PA_PAY_REJECTIONS_ALL PPR
WHERE PPR.PAY_SOURCE_ID = PPS.PAY_SOURCE_ID
)
WHERE REQUEST_ID = G_REQUEST_ID
AND PAY_SOURCE_ID IN
(SELECT PAY_SOURCE_ID
FROM PA_PAY_REJECTIONS_ALL
WHERE REQUEST_ID = G_REQUEST_ID
) ;
INSERT
INTO PA_PAY_DIST_LINES
( PAY_DIST_ID,
PAY_SOURCE_ID,
INTERFACE_RUN_ID,
PERSON_ID,
ASSIGNMENT_ID,
ORGANIZATION_ID,
PROJECT_ID,
TASK_ID,
EXPENDITURE_TYPE,
SYSTEM_LINKAGE_FUNCTION,
EXPENDITURE_ITEM_DATE,
PAY_ELEMENT_TYPE_ID,
PAY_ELEMENT_TYPE_CODE,
QUANTITY,
DENOM_CURRENCY_CODE,
DENOM_RAW_COST,
DENOM_BURDEN_COST,
ORG_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY
)
SELECT
pa_pay_dist_id_s.NEXTVAL ,
PAY_SOURCE_ID,
INTERFACE_RUN_ID,
PERSON_ID,
ASSIGNMENT_ID,
EMP_ORGANIZATION_ID,
PAY_SOURCE_PROJECT_ID,
PAY_SOURCE_TASK_ID,
EXPENDITURE_TYPE,
DECODE(COST_TYPE_CODE,'RAW','PJ','BURDEN','BTC',NULL) ,
G_PAY_PERIOD_END,
PAY_ELEMENT_TYPE_ID,
PAY_ELEMENT_TYPE_CODE,
0,--Quantity ,
PAY_CURRENCY_CODE,
DECODE(COST_TYPE_CODE,'RAW',PAY_AMOUNT,0),
DECODE(COST_TYPE_CODE,'BURDEN',PAY_AMOUNT,0),
ORG_ID,
trunc(sysdate),
fnd_global.user_id,
trunc(sysdate),
fnd_global.user_id
from PA_PAY_SOURCE_AMOUNTS
WHERE PAY_REJECTION_ID IS NULL
AND DISTRIBUTION_BASIS_CODE = 'NONE'
AND PAY_SOURCE_PROJECT_ID >0
AND PAY_SOURCE_TASK_ID > 0
AND INTERFACE_RUN_ID = G_INTERFACE_RUN_ID ;
UPDATE PA_PAY_SOURCE_AMOUNTS
SET PRIORITY =
( Decode(PAY_CURRENCY_CODE,G_FUNC_CURR_CODE,10000,0)+
(Decode(PAY_SOURCE_PROJECT_ID,NULL , 0,1)*1000) +
(Decode(PAY_SOURCE_TASK_ID,NULL , 0,1)*100) +
(Decode(PAY_SOURCE_ORGANIZATION_ID,NULL , 0,1)* 10) +
(Decode(TIMECARD_EXP_TYPE,NULL , 0,1)*1))
WHERE
Upper(TIMECARD_ELEMENT) = 'YES'
AND REQUEST_ID = G_REQUEST_ID;
UPDATE PA_PAY_SOURCE_AMOUNTS
SET PRIORITY =
(
(Decode(PAY_SOURCE_PROJECT_ID,NULL , 0,1)*10000) +
(Decode(PAY_SOURCE_TASK_ID,NULL , 0,1)*1000) +
(Decode(PAY_SOURCE_ORGANIZATION_ID,NULL , 0,1)* 100) +
(Decode(TIMECARD_EXP_TYPE,NULL , 0,1)*10) +
Decode(PAY_CURRENCY_CODE,G_FUNC_CURR_CODE,1,0)
)
WHERE
distribution_basis_code IN ('STHOURS','OTHOURS','TOTAL_HOURS') AND /*
Added for bug#12975007 */
Upper(TIMECARD_ELEMENT) = 'YES'
AND REQUEST_ID = G_REQUEST_ID;
UPDATE PA_PAY_SOURCE_AMOUNTS
SET PRIORITY =
(
(Decode(PAY_SOURCE_PROJECT_ID,NULL , 0,1)*20000) +
(Decode(PAY_SOURCE_TASK_ID,NULL , 0,1)*2000) +
(Decode(PAY_SOURCE_ORGANIZATION_ID,NULL , 0,1)* 200)
)
WHERE
distribution_basis_code IN ('STHOURS','OTHOURS','TOTAL_HOURS') AND
Upper(Nvl(TIMECARD_ELEMENT,'NO')) = 'NO'
AND REQUEST_ID = G_REQUEST_ID;
UPDATE PA_PAY_SOURCE_AMOUNTS
SET PRIORITY =
(
(Decode(PAY_SOURCE_PROJECT_ID,NULL , 0,1)*30000) +
(Decode(PAY_SOURCE_TASK_ID,NULL , 0,1)*3000) +
(Decode(PAY_SOURCE_ORGANIZATION_ID,NULL , 0,1)* 300)
)
WHERE
distribution_basis_code IN ('ST_AMT','OT_AMT','ST_OT_AMT')
AND REQUEST_ID = G_REQUEST_ID;
DELETE
FROM PA_PAY_EXP_TEMP ;
INSERT
INTO PA_PAY_EXP_TEMP
(
INCURRED_BY_PERSON_ID,
EXPENDITURE_ITEM_ID ,
EXPENDITURE_ITEM_DATE,
EXPENDITURE_TYPE ,
ORGANIZATION_ID ,
OVERRIDE_TO_ORGANIZATION_ID ,
ORG_ID ,
PROJECT_ID ,
TASK_ID ,
SYSTEM_LINKAGE_FUNCTION ,
COST_DISTRIBUTED_FLAG,
ACCT_RAW_COST,
QUANTITY ,
INTERFACE_RUN_ID,
PAYROLL_ACCRUAL_FLAG,
TRANSFERRED_FROM_EXP_ITEM_ID)
(SELECT EI.INCURRED_BY_PERSON_ID,
EII.EXPENDITURE_ITEM_ID ,
EII.EXPENDITURE_ITEM_DATE,
EII.EXPENDITURE_TYPE ,
EI.INCURRED_BY_ORGANIZATION_ID ,
EII.OVERRIDE_TO_ORGANIZATION_ID ,
EII.ORG_ID ,
EII.PROJECT_ID ,
EII.TASK_ID ,
EII.SYSTEM_LINKAGE_FUNCTION ,
EII.COST_DISTRIBUTED_FLAG,
EII.ACCT_RAW_COST,
EII.QUANTITY,
EII.INTERFACE_RUN_ID,
EII.PAYROLL_ACCRUAL_FLAG,
EII.TRANSFERRED_FROM_EXP_ITEM_ID
FROM PA_EXPENDITURE_ITEMS EII,
PA_EXPENDITURES EI
WHERE EI.INCURRED_BY_PERSON_ID IN (SELECT DISTINCT PERSON_ID FROM PA_PAY_SOURCE_AMOUNTS
WHERE
INTERFACE_RUN_ID = G_INTERFACE_RUN_ID)
AND EI.EXPENDITURE_ID = EII.EXPENDITURE_ID
AND EII.expenditure_item_date BETWEEN P_START_DATE AND P_END_DATE
AND net_zero_adjustment_flag <> 'Y'
AND (( costing_method = 'ACTUAL' AND
payroll_accrual_flag = 'N' AND
COST_DISTRIBUTED_FLAG = 'Y' AND INTERFACE_RUN_ID IS NOT NULL
AND system_linkage_function IN ('ST','OT','PJ','BTC')
)
OR
( costing_method = 'ACTUAL' AND
payroll_accrual_flag = 'Y' AND
--COST_DISTRIBUTED_FLAG = 'Y' AND /*Accrual line Marked for re-calc also should get picked up*/
INTERFACE_RUN_ID IS NULL
AND system_linkage_function IN ('ST','OT')
)
OR
(COST_DISTRIBUTED_FLAG = 'N' AND
get_cost_method(eii.expenditure_item_id,eii.expenditure_item_date) = 'ACTUAL' AND
Nvl(COSTING_METHOD,'ACTUAL') <> 'STANDARD'AND
system_linkage_function IN ('ST','OT'))
)
) ;
SELECT temp.expenditure_item_id,
ei1.acct_currency_code,
ei1.acct_raw_cost
FROM PA_PAY_EXP_TEMP temp ,
pa_expenditure_items_all ei1,
pa_expenditure_items_all ei2,
pa_expenditure_items_all ei3
WHERE
temp.expenditure_item_id= ei3.expenditure_item_id
AND ei3.transaction_source = 'ORACLE TIME AND LABOR'
AND ei2.orig_transaction_reference = ei3.orig_transaction_reference
AND ei2.system_linkage_function IN ('ST','OT')
AND ei2.transaction_source = 'ORACLE TIME AND LABOR'
AND ei2.COSTING_METHOD = 'ACTUAL'
AND ei2.net_zero_adjustment_flag = 'Y'
AND ei2.adjusted_expenditure_item_id = ei1.expenditure_item_id
AND ei1.transaction_source = 'ORACLE TIME AND LABOR'
AND ei1.COSTING_METHOD = 'ACTUAL'
AND ei1.system_linkage_function IN ('ST','OT')
AND ei1.net_zero_adjustment_flag = 'Y'
)
LOOP
UPDATE PA_PAY_EXP_TEMP t
SET ORIG_CURR_CODE = x.acct_currency_code
, ORIG_AMOUNT = x.acct_raw_cost
WHERE t.expenditure_item_id = x.expenditure_item_id;
UPDATE PA_PAY_EXP_TEMP T
SET
(
ORIG_CURR_CODE ,
ORIG_AMOUNT
)
=
(SELECT EI3.ACCT_CURRENCY_CODE,
EI3.ACCT_RAW_COST
FROM PA_EXPENDITURE_ITEMS EI3
WHERE EXPENDITURE_ITEM_ID =
(SELECT MAX(EI5.EXPENDITURE_ITEM_ID)
FROM PA_EXPENDITURE_ITEMS EI4 ,
PA_EXPENDITURE_ITEMS EI5 ,
PA_PAY_AUDIT AUD
WHERE EI4.EXPENDITURE_ITEM_ID = T.EXPENDITURE_ITEM_ID
AND EI4.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
AND EI5.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
AND EI5.INTERFACE_RUN_ID IS NOT NULL
AND EI5.INTERFACE_RUN_ID = AUD.INTERFACE_RUN_ID
AND AUD.BATCH_ID <> (SELECT BATCH_ID FROM PA_PAY_AUDIT WHERE INTERFACE_RUN_ID = G_INTERFACE_RUN_ID)
AND SUBSTR(EI5.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI5.ORIG_TRANSACTION_REFERENCE,':') - 1) = SUBSTR(EI4.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI4.ORIG_TRANSACTION_REFERENCE,':') - 1)
AND EI5.COST_DISTRIBUTED_FLAG = 'Y'
AND EI5.ADJUSTED_EXPENDITURE_ITEM_ID IS NULL
)
)
WHERE EXPENDITURE_ITEM_ID IN
(SELECT T.EXPENDITURE_ITEM_ID
FROM PA_EXPENDITURE_ITEMS EI1 ,
PA_PAY_EXP_TEMP T
WHERE T.EXPENDITURE_ITEM_ID = EI1.EXPENDITURE_ITEM_ID
AND EI1.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
AND EXISTS
(SELECT 1
FROM PA_EXPENDITURE_ITEMS EI2
,PA_PAY_AUDIT AUD2
WHERE EI2.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
AND EI2.INTERFACE_RUN_ID IS NOT NULL
AND EI2.INTERFACE_RUN_ID = AUD2.INTERFACE_RUN_ID
AND AUD2.BATCH_ID <> (SELECT BATCH_ID FROM PA_PAY_AUDIT WHERE INTERFACE_RUN_ID = G_INTERFACE_RUN_ID)
AND SUBSTR(EI2.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI2.ORIG_TRANSACTION_REFERENCE,':') - 1) = SUBSTR(EI1.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI1.ORIG_TRANSACTION_REFERENCE,':') - 1)
AND COST_DISTRIBUTED_FLAG = 'Y'
AND ADJUSTED_EXPENDITURE_ITEM_ID IS NULL )
) ;
SELECT DISTINCT
PRIORITY ,
PAY_CURRENCY_CODE
FROM
PA_PAY_SOURCE_AMOUNTS
WHERE
DISTRIBUTION_BASIS_CODE IN ('STHOURS', 'OTHOURS', 'TOTAL_HOURS')
AND
TIMECARD_ELEMENT = 'YES' AND
/* PERSON_ID = P_PERSON_ID AND */
INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
ORDER BY
PRIORITY DESC
)
LOOP
INSERT
INTO
PA_PAY_DIST_LINES
(
PAY_DIST_ID ,
PAY_SOURCE_ID ,
INTERFACE_RUN_ID ,
PERSON_ID ,
ORGANIZATION_ID ,
PROJECT_ID ,
TASK_ID ,
EXPENDITURE_TYPE ,
SYSTEM_LINKAGE_FUNCTION ,
EXPENDITURE_ITEM_DATE ,
PAY_ELEMENT_TYPE_ID ,
PAY_ELEMENT_TYPE_CODE ,
DENOM_CURRENCY_CODE ,
QUANTITY ,
DENOM_RAW_COST ,
SOURCE_EXPENDITURE_ITEM_ID,
ORG_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
REQUEST_ID
)
SELECT
PA_PAY_DIST_ID_S.NEXTVAL,
PSA.PAY_SOURCE_ID ,
G_INTERFACE_RUN_ID ,
PSA.PERSON_ID ,
NVL(EII.OVERRIDE_TO_ORGANIZATION_ID , EII.ORGANIZATION_ID),
EII.PROJECT_ID ,
EII.TASK_ID ,
EII.EXPENDITURE_TYPE ,
EII.SYSTEM_LINKAGE_FUNCTION,
EII.EXPENDITURE_ITEM_DATE ,
PSA.PAY_ELEMENT_TYPE_ID ,
PSA.PAY_ELEMENT_TYPE_CODE ,
PSA.PAY_CURRENCY_CODE ,
EII.QUANTITY ,
RATIO_TO_REPORT(EII.QUANTITY) over (PARTITION BY PAY_SOURCE_ID) * PSA.PAY_AMOUNT AS AMOUNT ,
EII.EXPENDITURE_ITEM_ID ,
EII.ORG_ID ,
TRUNC(sysdate) ,
fnd_global.user_id ,
TRUNC(sysdate) ,
fnd_global.user_id ,
G_LOGIN_ID ,
G_REQUEST_ID
FROM
PA_PAY_EXP_TEMP EII , -- change to PA_PAY_EXP_TEMP danger
PA_PAY_SOURCE_AMOUNTS PSA
WHERE
PSA.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
PSA.PRIORITY = Z.PRIORITY AND
PSA.PAY_CURRENCY_CODE = Z.PAY_CURRENCY_CODE AND
--PSA.PERSON_ID = p_person_id AND
PSA.PERSON_ID = EII.INCURRED_BY_PERSON_ID AND
((PSA.PAY_AMOUNT >0 AND G_RETRO ='N') OR (G_RETRO ='Y')) AND /*Added
for bug#13595751*/
-- PSA.PAY_SOURCE_ID = p_pay_source_id AND
EII.expenditure_type = NVL(PSA.TIMECARD_EXP_TYPE, EII.expenditure_type) AND
/*pick the same pay source record */
PSA.PERSON_ID = EII.INCURRED_BY_PERSON_ID AND
EII.PROJECT_ID = NVL( PSA.PAY_SOURCE_PROJECT_ID,EII.PROJECT_ID )AND
EII.TASK_ID = NVL(PSA.PAY_SOURCE_TASK_ID ,EII.TASK_ID) AND
NVL(EII.OVERRIDE_TO_ORGANIZATION_ID , EII.ORGANIZATION_ID) =
COALESCE(PSA.PAY_SOURCE_ORGANIZATION_ID,
EII.OVERRIDE_TO_ORGANIZATION_ID , EII.ORGANIZATION_ID) AND
EII.QUANTITY <> 0 AND
EII.SYSTEM_LINKAGE_FUNCTION IN
(
SELECT
DECODE(psa.DISTRIBUTION_BASIS_CODE,'STHOURS' ,'ST',
'OTHOURS','OT','TOTAL_HOURS','ST')
FROM
dual
UNION
SELECT
DECODE(psa.DISTRIBUTION_BASIS_CODE,'STHOURS' ,'ST',
'OTHOURS','OT','TOTAL_HOURS','OT')
FROM
dual
)
AND NVL(EII.PROCESSED_FLAG,'N') <> 'Y' -- not processed in current run
AND EII.INTERFACE_RUN_ID IS NULL; -- not an EI which has actual cost
INSERT
INTO
PA_PAY_REJECTIONS_ALL
(
PAY_REJECTION_ID ,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID,
PAY_ELEMENT_TYPE_CODE,
REJECTION_CODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
)
SELECT
PA_PAY_REJECTION_ID_S.nextval,
G_INTERFACE_RUN_ID ,
PS.PERSON_ID ,
PS.ASSIGNMENT_ID ,
PS.PAY_SOURCE_ID ,
PS.PAY_ELEMENT_TYPE_ID ,
PS.PAY_ELEMENT_TYPE_CODE ,
'RC 25' ,
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID
FROM
PA_PAY_SOURCE_AMOUNTS PS
Where
INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
PRIORITY = Z.PRIORITY AND
PAY_CURRENCY_CODE = Z.PAY_CURRENCY_CODE AND
Nvl(PS.ALLOW_MISC_FLAG,'NO') = 'NO' AND
NOT EXISTS
(
SELECT 1 FROM PA_PAY_DIST_LINES PPD
WHERE
PPD.PAY_SOURCE_ID = PS.PAY_SOURCE_ID
)
;
INSERT
INTO
PA_PAY_DIST_LINES
(
PAY_DIST_ID ,
PAY_SOURCE_ID ,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
ORGANIZATION_ID ,
PROJECT_ID ,
TASK_ID ,
EXPENDITURE_TYPE ,
SYSTEM_LINKAGE_FUNCTION,
EXPENDITURE_ITEM_DATE ,
PAY_ELEMENT_TYPE_ID ,
PAY_ELEMENT_TYPE_CODE ,
QUANTITY ,
DENOM_CURRENCY_CODE ,
DENOM_RAW_COST ,
DENOM_BURDEN_COST ,
ORG_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
REQUEST_ID
)
SELECT
PA_PAY_DIST_ID_S.NEXTVAL ,
HOURS_TOTAL.PAY_SOURCE_ID ,
G_INTERFACE_RUN_ID ,
HOURS_TOTAL.PERSON_ID ,
HOURS_TOTAL.ASSIGNMENT_ID ,
HOURS_TOTAL.ORGANIZATION_ID ,
HOURS_TOTAL.PROJECT_ID ,
HOURS_TOTAL.TASK_ID ,
HOURS_TOTAL.EXPENDITURE_TYPE ,
'PJ' ,
G_PAY_PERIOD_END ,
HOURS_TOTAL.PAY_ELEMENT_TYPE_ID ,
HOURS_TOTAL.PAY_ELEMENT_TYPE_CODE ,
0 ,
HOURS_TOTAL.PAY_CURRENCY_CODE ,
HOURS_TOTAL.PAY_AMOUNT * RATIO_TO_REPORT(HOURS_TOTAL.HOURS) OVER (PARTITION BY HOURS_TOTAL.PAY_SOURCE_ID),
0 ,
G_ORG_ID ,
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID ,
G_LOGIN_ID ,
G_REQUEST_ID
FROM
(
SELECT
PAY_SOURCE_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
NVL(ORGANIZATION_ID ,OVERRIDE_TO_ORGANIZATION_ID) ORGANIZATION_ID ,
PROJECT_ID ,
TASK_ID ,
PPSA.EXPENDITURE_TYPE,
PAY_ELEMENT_TYPE_ID,
PAY_ELEMENT_TYPE_CODE ,
PAY_CURRENCY_CODE ,
PAY_AMOUNT,
SUM(QUANTITY) AS HOURS
FROM
PA_PAY_EXP_TEMP EI,
PA_PAY_SOURCE_AMOUNTS PPSA
WHERE
PPSA.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
PPSA.PRIORITY = Z.PRIORITY AND
PPSA.PAY_CURRENCY_CODE = Z.PAY_CURRENCY_CODE AND
PPSA.DISTRIBUTION_BASIS_CODE IN ('STHOURS', 'OTHOURS','TOTAL_HOURS') AND
PPSA.TIMECARD_ELEMENT = 'YES' AND
PPSA.PAY_SOURCE_ID NOT IN
( SELECT
PAY_SOURCE_ID
FROM
PA_PAY_DIST_LINES PPD
WHERE
PPD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
)AND
NVL(PPSA.ALLOW_MISC_FLAG,'NO') = 'YES' AND
PPSA.PERSON_ID = EI.INCURRED_BY_PERSON_ID AND
COALESCE(EI.ORGANIZATION_ID ,EI.OVERRIDE_TO_ORGANIZATION_ID,-99) = COALESCE(PPSA.PAY_SOURCE_ORGANIZATION_ID, EI.ORGANIZATION_ID ,EI.OVERRIDE_TO_ORGANIZATION_ID,-99) AND
EI.PROJECT_ID = NVL(PPSA.PAY_SOURCE_PROJECT_ID,PROJECT_ID) AND
EI.TASK_ID = NVL(PPSA.PAY_SOURCE_TASK_ID,TASK_ID)
/*AND COST_DISTRIBUTED_FLAG <> 'Y'
-- No unprocessed timecard found .. shd go on already
-- processed in prior runs*/
AND
EI.SYSTEM_LINKAGE_FUNCTION IN
(
SELECT
DECODE(PPSA.DISTRIBUTION_BASIS_CODE,'STHOURS' ,'ST', 'OTHOURS','OT','ALLHOURS','ST')
FROM
DUAL
UNION
SELECT
DECODE(PPSA.DISTRIBUTION_BASIS_CODE,'STHOURS' ,'ST', 'OTHOURS','OT','ALLHOURS','OT')
FROM
DUAL
)
AND
EI.EXPENDITURE_TYPE NOT IN
(
SELECT
EXPENDITURE_TYPE
FROM
PA_PAY_EXCLUDE_EXP_TYPES
WHERE
PAY_ELEMENT_MAP_ID = PPSA.PAY_ELEMENT_MAP_ID
)
GROUP BY
PAY_SOURCE_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
NVL(ORGANIZATION_ID ,OVERRIDE_TO_ORGANIZATION_ID) ,
PROJECT_ID ,
TASK_ID ,
PPSA.EXPENDITURE_TYPE,
PAY_ELEMENT_TYPE_ID,
PAY_ELEMENT_TYPE_CODE ,
PAY_CURRENCY_CODE ,
PAY_AMOUNT
)
HOURS_TOTAL ;
INSERT
INTO PA_PAY_REJECTIONS_ALL
(
PAY_REJECTION_ID ,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID ,
PAY_ELEMENT_TYPE_CODE,
REJECTION_CODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
)
SELECT PA_PAY_REJECTION_ID_S.NEXTVAL,
G_INTERFACE_RUN_ID ,
PS.PERSON_ID ,
PS.ASSIGNMENT_ID ,
PS.PAY_SOURCE_ID ,
PS.PAY_ELEMENT_TYPE_ID ,
PS.PAY_ELEMENT_TYPE_CODE ,
'RC 30' ,
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID
FROM PA_PAY_SOURCE_AMOUNTS PS
WHERE INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND PRIORITY = Z.PRIORITY
AND PAY_CURRENCY_CODE = Z.PAY_CURRENCY_CODE
AND NVL(PS.ALLOW_MISC_FLAG,'NO') = 'YES'
AND PS.PAY_SOURCE_ID NOT IN
( SELECT PAY_SOURCE_ID
FROM PA_PAY_DIST_LINES PPD
WHERE PPD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
) ;
UPDATE PA_PAY_EXP_TEMP
SET PROCESSED_FLAG = 'Y'
WHERE EXPENDITURE_ITEM_ID IN
(SELECT DIST.SOURCE_EXPENDITURE_ITEM_ID
FROM PA_PAY_DIST_LINES DIST,
PA_PAY_SOURCE_AMOUNTS SOURCE
WHERE SOURCE.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND SOURCE.PRIORITY = Z.PRIORITY
AND SOURCE.PAY_CURRENCY_CODE = Z.PAY_CURRENCY_CODE
AND DIST.PAY_SOURCE_ID = SOURCE.PAY_SOURCE_ID
) ;
FOR X IN ( SELECT * FROM pa_pay_source_amounts WHERE pay_source_id =p_pay_source_id )
Loop
INSERT
INTO PA_PAY_DIST_LINES
( PAY_DIST_ID,
PAY_SOURCE_ID,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
ORGANIZATION_ID ,
PROJECT_ID ,
TASK_ID ,
EXPENDITURE_TYPE ,
SYSTEM_LINKAGE_FUNCTION,
EXPENDITURE_ITEM_DATE ,
PAY_ELEMENT_TYPE_ID ,
PAY_ELEMENT_TYPE_CODE ,
QUANTITY ,
denom_CURRENCY_CODE,
denom_RAW_COST ,
denom_BURDEN_COST ,
ORG_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN,
REQUEST_ID
)
SELECT pa_pay_dist_id_s.NEXTVAL ,
X.PAY_SOURCE_ID,
X.INTERFACE_RUN_ID ,
X.PERSON_ID ,
X.ASSIGNMENT_ID ,
Hours_total.organization_id ,
Hours_total.project_id ,
Hours_total.task_id ,
X.expenditure_type ,
'PJ' ,
G_PAY_PERIOD_END ,
X.PAY_ELEMENT_TYPE_ID ,
X.PAY_ELEMENT_TYPE_CODE ,
0 ,
X.PAY_CURRENCY_CODE,
X.PAY_AMOUNT * Ratio_To_Report(Hours_total.hours) over (),
0 ,
G_ORG_ID ,
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID ,
G_LOGIN_ID,
G_REQUEST_ID
FROM ( SELECT NVL(organization_id ,OVERRIDE_TO_ORGANIZATION_ID) organization_id ,
project_id ,
task_id ,
SUM(quantity) AS hours
FROM PA_PAY_EXP_TEMP
WHERE COALESCE(organization_id ,OVERRIDE_TO_ORGANIZATION_ID,-99) = COALESCE(X.PAY_SOURCE_ORGANIZATION_ID,organization_id ,OVERRIDE_TO_ORGANIZATION_ID,-99)
AND project_id = Nvl(X.PAY_SOURCE_PROJECT_ID,project_id)
AND task_id = Nvl(X.PAY_SOURCE_TASK_ID,task_id)
--AND COST_DISTRIBUTED_FLAG <> 'Y'
-- No unprocessed timecard found .. shd go on already processed in prior runs
AND SYSTEM_LINKAGE_FUNCTION IN
(SELECT DECODE(X.distribution_basis_code,'STHOURS' ,'ST','OTHOURS','OT','ALLHOURS','ST')
FROM dual
UNION
SELECT DECODE(X.distribution_basis_code,'STHOURS' ,'ST','OTHOURS','OT','ALLHOURS','OT')
FROM dual
)
AND expenditure_type NOT IN (SELECT expenditure_type FROM PA_PAY_EXCLUDE_EXP_TYPES
WHERE pay_element_map_id = X.pay_element_map_id )
GROUP BY NVL(organization_id ,OVERRIDE_TO_ORGANIZATION_ID) ,
project_id ,
task_id
)
Hours_total ;
INSERT INTO
PA_PAY_DIST_LINES
( PAY_DIST_ID,
PAY_SOURCE_ID,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
ORGANIZATION_ID ,
PROJECT_ID ,
TASK_ID ,
EXPENDITURE_TYPE ,
SYSTEM_LINKAGE_FUNCTION,
EXPENDITURE_ITEM_DATE ,
PAY_ELEMENT_TYPE_ID ,
PAY_ELEMENT_TYPE_CODE,
QUANTITY ,
DENOM_CURRENCY_CODE,
DENOM_RAW_COST ,
DENOM_BURDEN_COST ,
ORG_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN,
REQUEST_ID
)
SELECT
PA_PAY_DIST_ID_S.NEXTVAL
, SOURCE.PAY_SOURCE_ID
, SOURCE.INTERFACE_RUN_ID
, SOURCE.PERSON_ID
, SOURCE.ASSIGNMENT_ID
, Hours_total.organization_id
, Hours_total.project_id
, Hours_total.task_id
, SOURCE.EXPENDITURE_TYPE
, DECODE(SOURCE.COST_TYPE_CODE
,'RAW','PJ'
,'BURDEN','BTC')
, G_PAY_PERIOD_END
, SOURCE.PAY_ELEMENT_TYPE_ID
, SOURCE.PAY_ELEMENT_TYPE_CODE
, 0
, SOURCE.PAY_CURRENCY_CODE
, DECODE(SOURCE.COST_TYPE_CODE
,'RAW',SOURCE.PAY_AMOUNT * Ratio_To_Report(Hours_total.hours) over (PARTITION BY SOURCE.PAY_SOURCE_ID))
, DECODE(SOURCE.COST_TYPE_CODE
,'BURDEN',SOURCE.PAY_AMOUNT * Ratio_To_Report(Hours_total.hours) over (PARTITION BY SOURCE.PAY_SOURCE_ID))
, G_ORG_ID
, G_RUN_DATE
, G_USER_ID
, G_RUN_DATE
, G_USER_ID
, G_LOGIN_ID
, G_REQUEST_ID
FROM ( SELECT Pay_source_id
, NVL(organization_id ,OVERRIDE_TO_ORGANIZATION_ID) organization_id
, project_id
, task_id
, SUM(NVL(quantity,0)) AS hours
FROM PA_PAY_EXP_TEMP TEMP
, PA_PAY_SOURCE_AMOUNTS S2
WHERE S2.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND DISTRIBUTION_BASIS_CODE IN ('STHOURS'
, 'OTHOURS'
, 'TOTAL_HOURS')
AND NVL(TIMECARD_ELEMENT,'NO') = 'NO'
AND S2.PERSON_ID = TEMP.INCURRED_BY_PERSON_ID
AND COALESCE(organization_id ,OVERRIDE_TO_ORGANIZATION_ID,-99) = COALESCE(S2.PAY_SOURCE_ORGANIZATION_ID,organization_id ,OVERRIDE_TO_ORGANIZATION_ID,-99)
AND project_id = NVL(S2.PAY_SOURCE_PROJECT_ID,project_id)
AND task_id = NVL(S2.PAY_SOURCE_TASK_ID,task_id)
AND
(
(
S2.distribution_basis_code <> 'TOTAL_HOURS'
AND TEMP.SYSTEM_LINKAGE_FUNCTION = DECODE(S2.distribution_basis_code
,'STHOURS' ,'ST'
,'OTHOURS','OT' )
)
OR
(
S2.distribution_basis_code = 'TOTAL_HOURS'
AND TEMP.SYSTEM_LINKAGE_FUNCTION IN ('ST'
, 'OT')
)
)
AND TEMP.expenditure_type NOT IN (SELECT expenditure_type
FROM PA_PAY_EXCLUDE_EXP_TYPES
WHERE pay_element_map_id = S2.pay_element_map_id
)
GROUP BY Pay_source_id
, NVL(organization_id ,OVERRIDE_TO_ORGANIZATION_ID)
, project_id
, task_id
)
Hours_total
, PA_PAY_SOURCE_AMOUNTS SOURCE
WHERE hours_total.pay_source_id = source.pay_source_id;
INSERT
INTO PA_PAY_REJECTIONS_ALL
(
PAY_REJECTION_ID
, INTERFACE_RUN_ID
, PERSON_ID
, ASSIGNMENT_ID
, PAY_SOURCE_ID
, PAY_ELEMENT_TYPE_ID
, PAY_ELEMENT_TYPE_CODE
, REJECTION_CODE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
)
SELECT PA_PAY_REJECTION_ID_S.NEXTVAL
, G_INTERFACE_RUN_ID
, PS.PERSON_ID
, PS.ASSIGNMENT_ID
, PS.PAY_SOURCE_ID
, PS.PAY_ELEMENT_TYPE_ID
, PS.PAY_ELEMENT_TYPE_CODE
, 'RC 30'
, G_RUN_DATE
, G_USER_ID
, G_RUN_DATE
, G_USER_ID
FROM PA_PAY_SOURCE_AMOUNTS PS
WHERE INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND DISTRIBUTION_BASIS_CODE IN ('STHOURS'
, 'OTHOURS'
, 'TOTAL_HOURS')
AND NVL(TIMECARD_ELEMENT,'NO') = 'NO'
AND PS.PAY_SOURCE_ID NOT IN ( SELECT PAY_SOURCE_ID
FROM PA_PAY_DIST_LINES PPD
WHERE PPD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
) ;
INSERT
INTO PA_PAY_DIST_LINES
(
PAY_DIST_ID
, PAY_SOURCE_ID
, INTERFACE_RUN_ID
, PERSON_ID
, ASSIGNMENT_ID
, ORGANIZATION_ID
, PROJECT_ID
, TASK_ID
, EXPENDITURE_TYPE
, SYSTEM_LINKAGE_FUNCTION
, EXPENDITURE_ITEM_DATE
, PAY_ELEMENT_TYPE_ID
, PAY_ELEMENT_TYPE_CODE
, QUANTITY
, DENOM_CURRENCY_CODE
, DENOM_RAW_COST
, DENOM_BURDEN_COST
, ORG_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
)
SELECT pa_pay_dist_id_s.NEXTVAL
, SOURCE.PAY_SOURCE_ID
, G_INTERFACE_RUN_ID
, SOURCE.PERSON_ID
, SOURCE.ASSIGNMENT_ID
, SUMM_AMOUNTS.organization_id
, SUMM_AMOUNTS.project_id
, SUMM_AMOUNTS.task_id
, SOURCE.EXPENDITURE_TYPE
, DECODE(SOURCE.COST_TYPE_CODE
,'RAW','PJ'
,'BURDEN','BTC')
, G_PAY_PERIOD_END
, SOURCE.PAY_ELEMENT_TYPE_ID
, SOURCE.PAY_ELEMENT_TYPE_CODE
, 0
, SOURCE.PAY_CURRENCY_CODE
, DECODE(SOURCE.COST_TYPE_CODE
,'RAW',SOURCE.PAY_AMOUNT * Ratio_To_Report(SUMM_AMOUNTS.amount) over (PARTITION BY SOURCE.PAY_SOURCE_ID))
, DECODE(SOURCE.COST_TYPE_CODE
,'BURDEN',SOURCE.PAY_AMOUNT * Ratio_To_Report(SUMM_AMOUNTS.amount) over (PARTITION BY SOURCE.PAY_SOURCE_ID))
, G_ORG_ID
, G_RUN_DATE
, G_USER_ID
, G_RUN_DATE
, G_USER_ID
FROM (
SELECT pay_source_id
, organization_id
, project_id
, task_id
, SUM(amount) amount
FROM ( SELECT S1.pay_source_id
, NVL(organization_id ,OVERRIDE_TO_ORGANIZATION_ID) organization_id
, project_id
, task_id
, SUM(ACCT_RAW_COST) AS amount
FROM PA_PAY_DIST_LINES DIST
,PA_PAY_SOURCE_AMOUNTS S1
WHERE S1.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND DISTRIBUTION_BASIS_CODE IN ('ST_AMT'
,'OT_AMT'
, 'ST_OT_AMT')
AND DIST.PERSON_ID = S1.PERSON_ID
AND DIST.INTERFACE_RUN_ID = S1.INTERFACE_RUN_ID
AND COALESCE(DIST.organization_id ,DIST.OVERRIDE_TO_ORGANIZATION_ID,-99) = COALESCE(S1.PAY_SOURCE_ORGANIZATION_ID,DIST.organization_id ,DIST.OVERRIDE_TO_ORGANIZATION_ID,-99)
AND project_id = NVL(S1.PAY_SOURCE_PROJECT_ID,project_id)
AND task_id = NVL(S1.PAY_SOURCE_TASK_ID,task_id)
AND
(
(
S1.distribution_basis_code <> 'ST_OT_AMT'
AND DIST.SYSTEM_LINKAGE_FUNCTION = DECODE(S1.distribution_basis_code
,'ST_AMT' ,'ST'
,'OT_AMT','OT' )
)
OR
(
S1.distribution_basis_code = 'ST_OT_AMT'
AND DIST.SYSTEM_LINKAGE_FUNCTION IN ('ST'
,'OT')
)
)
AND DIST.expenditure_type NOT IN (SELECT expenditure_type
FROM PA_PAY_EXCLUDE_EXP_TYPES
WHERE pay_element_map_id = S1.pay_element_map_id
)
GROUP BY S1.pay_source_id
, NVL(organization_id ,OVERRIDE_TO_ORGANIZATION_ID)
, project_id
, task_id
UNION
SELECT S2.pay_source_id
, NVL(organization_id ,OVERRIDE_TO_ORGANIZATION_ID) organization_id
, project_id
, task_id
, SUM(acct_raw_cost) AS amount
FROM PA_PAY_EXP_TEMP TEMP
, PA_PAY_SOURCE_AMOUNTS S2
WHERE S2.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND DISTRIBUTION_BASIS_CODE IN ('ST_AMT'
,'OT_AMT'
, 'ST_OT_AMT')
AND S2.PERSON_ID = TEMP.INCURRED_BY_PERSON_ID
AND COALESCE(organization_id ,OVERRIDE_TO_ORGANIZATION_ID,-99) = COALESCE(S2.PAY_SOURCE_ORGANIZATION_ID,organization_id ,OVERRIDE_TO_ORGANIZATION_ID,-99)
AND project_id = NVL(S2.PAY_SOURCE_PROJECT_ID,project_id)
AND task_id = NVL(S2.PAY_SOURCE_TASK_ID,task_id)
AND COST_DISTRIBUTED_FLAG = 'Y'
AND NVL(PAYROLL_ACCRUAL_FLAG,'N') <> 'Y' -- Bug 12813753
AND
(
(
S2.distribution_basis_code <> 'ST_OT_AMT'
AND TEMP.SYSTEM_LINKAGE_FUNCTION = DECODE(S2.distribution_basis_code
,'ST_AMT' ,'ST'
,'OT_AMT','OT' )
)
OR
(
S2.distribution_basis_code = 'ST_OT_AMT'
AND TEMP.SYSTEM_LINKAGE_FUNCTION IN ('ST'
,'OT')
)
)
AND TEMP.expenditure_type NOT IN (SELECT expenditure_type
FROM PA_PAY_EXCLUDE_EXP_TYPES
WHERE pay_element_map_id = S2.pay_element_map_id
)
GROUP BY S2.pay_source_id
, NVL(organization_id ,OVERRIDE_TO_ORGANIZATION_ID)
, project_id
, task_id
)
GROUP BY
pay_source_id
, organization_id
, project_id
, task_id ) SUMM_AMOUNTS , pa_Pay_source_amounts SOURCE WHERE SUMM_AMOUNTS.PAY_SOURCE_ID = SOURCE.PAY_SOURCE_ID ;
INSERT
INTO PA_PAY_REJECTIONS_ALL
(
PAY_REJECTION_ID
, INTERFACE_RUN_ID
, PERSON_ID
, ASSIGNMENT_ID
, PAY_SOURCE_ID
, PAY_ELEMENT_TYPE_ID
, PAY_ELEMENT_TYPE_CODE
, REJECTION_CODE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
)
SELECT PA_PAY_REJECTION_ID_S.NEXTVAL
, G_INTERFACE_RUN_ID
, PS.PERSON_ID
, PS.ASSIGNMENT_ID
, PS.PAY_SOURCE_ID
, PS.PAY_ELEMENT_TYPE_ID
, PS.PAY_ELEMENT_TYPE_CODE
, 'RC 35'
, G_RUN_DATE
, G_USER_ID
, G_RUN_DATE
, G_USER_ID
FROM PA_PAY_SOURCE_AMOUNTS PS
WHERE INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND DISTRIBUTION_BASIS_CODE IN ('ST_AMT'
,'OT_AMT'
, 'ST_OT_AMT')
AND PS.PAY_SOURCE_ID NOT IN ( SELECT PAY_SOURCE_ID
FROM PA_PAY_DIST_LINES PPD
WHERE PPD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
) ;
INSERT
INTO
PA_PAY_DIST_LINES
(
PAY_DIST_ID ,
PAY_SOURCE_ID ,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
ORGANIZATION_ID ,
PROJECT_ID ,
TASK_ID ,
EXPENDITURE_TYPE ,
SYSTEM_LINKAGE_FUNCTION,
EXPENDITURE_ITEM_DATE ,
PAY_ELEMENT_TYPE_ID ,
PAY_ELEMENT_TYPE_CODE ,
QUANTITY ,
DENOM_CURRENCY_CODE ,
DENOM_RAW_COST ,
DENOM_BURDEN_COST ,
ORG_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
)
SELECT
PA_PAY_DIST_ID_S.NEXTVAL ,
SOURCE.PAY_SOURCE_ID ,
G_INTERFACE_RUN_ID ,
SOURCE.PERSON_ID ,
SOURCE.ASSIGNMENT_ID ,
SUMM_AMOUNTS.ORGANIZATION_ID ,
SUMM_AMOUNTS.PROJECT_ID ,
SUMM_AMOUNTS.TASK_ID ,
SOURCE.EXPENDITURE_TYPE ,
DECODE(SOURCE.COST_TYPE_CODE,'RAW','PJ','BURDEN','BTC') ,
G_PAY_PERIOD_END ,
SOURCE.PAY_ELEMENT_TYPE_ID ,
SOURCE.PAY_ELEMENT_TYPE_CODE ,
0 ,
SOURCE.PAY_CURRENCY_CODE ,
DECODE(SOURCE.COST_TYPE_CODE,'RAW',SOURCE.PAY_AMOUNT * RATIO_TO_REPORT(
SUMM_AMOUNTS.AMOUNT) OVER (PARTITION BY SOURCE.PAY_SOURCE_ID)),
DECODE(SOURCE.COST_TYPE_CODE,'BURDEN',SOURCE.PAY_AMOUNT * RATIO_TO_REPORT
(SUMM_AMOUNTS.AMOUNT) OVER (PARTITION BY SOURCE.PAY_SOURCE_ID)),
G_ORG_ID ,
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID
FROM
(
SELECT
PAY_SOURCE_ID ,
ORGANIZATION_ID ,
PROJECT_ID ,
TASK_ID ,
SUM(AMOUNT) AMOUNT
FROM
(
SELECT
S1.PAY_SOURCE_ID,
NVL(ORGANIZATION_ID ,OVERRIDE_TO_ORGANIZATION_ID) ORGANIZATION_ID ,
PROJECT_ID ,
TASK_ID ,
SUM(ACCT_RAW_COST) AS AMOUNT
FROM
PA_PAY_DIST_LINES DIST ,
PA_PAY_SOURCE_AMOUNTS S1
WHERE
S1.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
DISTRIBUTION_BASIS_CODE = 'TOTAL_RAW' AND
DIST.PERSON_ID = S1.PERSON_ID AND
DIST.INTERFACE_RUN_ID = S1.INTERFACE_RUN_ID AND
COALESCE(DIST.ORGANIZATION_ID ,
DIST.OVERRIDE_TO_ORGANIZATION_ID,-99) = COALESCE( S1.PAY_SOURCE_ORGANIZATION_ID, DIST.ORGANIZATION_ID , DIST.OVERRIDE_TO_ORGANIZATION_ID,-99) AND
PROJECT_ID = NVL(S1.PAY_SOURCE_PROJECT_ID, PROJECT_ID) AND
TASK_ID = NVL(S1.PAY_SOURCE_TASK_ID,TASK_ID) AND
DIST.SYSTEM_LINKAGE_FUNCTION IN ('ST','OT','PJ') AND
DIST.EXPENDITURE_TYPE NOT IN
(
SELECT
EXPENDITURE_TYPE
FROM
PA_PAY_EXCLUDE_EXP_TYPES
WHERE
PAY_ELEMENT_MAP_ID = S1.PAY_ELEMENT_MAP_ID
)
GROUP BY
S1.PAY_SOURCE_ID,
NVL(ORGANIZATION_ID ,OVERRIDE_TO_ORGANIZATION_ID)
,
PROJECT_ID ,
TASK_ID
UNION
SELECT
S2.PAY_SOURCE_ID,
NVL(ORGANIZATION_ID ,OVERRIDE_TO_ORGANIZATION_ID) ORGANIZATION_ID ,
PROJECT_ID ,
TASK_ID ,
SUM(ACCT_RAW_COST) AS AMOUNT
FROM
PA_PAY_EXP_TEMP TEMP ,
PA_PAY_SOURCE_AMOUNTS S2
WHERE
S2.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
DISTRIBUTION_BASIS_CODE = 'TOTAL_RAW' AND
S2.PERSON_ID = TEMP.INCURRED_BY_PERSON_ID AND
COALESCE(ORGANIZATION_ID ,
OVERRIDE_TO_ORGANIZATION_ID,-99) = COALESCE(S2.PAY_SOURCE_ORGANIZATION_ID,ORGANIZATION_ID ,OVERRIDE_TO_ORGANIZATION_ID,-99) AND
PROJECT_ID = NVL(S2.PAY_SOURCE_PROJECT_ID,PROJECT_ID) AND
TASK_ID = NVL(S2.PAY_SOURCE_TASK_ID,TASK_ID) AND
COST_DISTRIBUTED_FLAG = 'Y' AND
NVL(PAYROLL_ACCRUAL_FLAG,'N') <> 'Y' AND
TEMP.SYSTEM_LINKAGE_FUNCTION IN ('ST','OT','PJ') AND
TEMP.EXPENDITURE_TYPE NOT IN
(
SELECT
EXPENDITURE_TYPE
FROM
PA_PAY_EXCLUDE_EXP_TYPES
WHERE
PAY_ELEMENT_MAP_ID = S2.PAY_ELEMENT_MAP_ID
)
GROUP BY
S2.PAY_SOURCE_ID,
NVL(ORGANIZATION_ID ,OVERRIDE_TO_ORGANIZATION_ID),
PROJECT_ID ,
TASK_ID
)
GROUP BY
PAY_SOURCE_ID ,
ORGANIZATION_ID ,
PROJECT_ID ,
TASK_ID
)
SUMM_AMOUNTS ,
PA_PAY_SOURCE_AMOUNTS SOURCE
WHERE
SUMM_AMOUNTS.PAY_SOURCE_ID = SOURCE.PAY_SOURCE_ID ;
INSERT
INTO PA_PAY_REJECTIONS_ALL
(
PAY_REJECTION_ID
, INTERFACE_RUN_ID
, PERSON_ID
, ASSIGNMENT_ID
, PAY_SOURCE_ID
, PAY_ELEMENT_TYPE_ID
, PAY_ELEMENT_TYPE_CODE
, REJECTION_CODE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
)
SELECT PA_PAY_REJECTION_ID_S.NEXTVAL
, G_INTERFACE_RUN_ID
, PS.PERSON_ID
, PS.ASSIGNMENT_ID
, PS.PAY_SOURCE_ID
, PS.PAY_ELEMENT_TYPE_ID
, PS.PAY_ELEMENT_TYPE_CODE
, 'RC 35'
, G_RUN_DATE
, G_USER_ID
, G_RUN_DATE
, G_USER_ID
FROM PA_PAY_SOURCE_AMOUNTS PS
WHERE INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND DISTRIBUTION_BASIS_CODE = 'TOTAL_RAW'
AND PS.PAY_SOURCE_ID NOT IN ( SELECT PAY_SOURCE_ID
FROM PA_PAY_DIST_LINES PPD
WHERE PPD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
) ;
select --ei.org_id ORG_ID
--,TO_CHAR(ei.expenditure_item_date,'YYYY/MM/DD') expenditure_item_date
--,nvl(ei.override_to_organization_id
-- ,exp.incurred_by_organization_id) orgj_id
-- ,
exp.incurred_by_person_id incurred_by_person_id
-- ,ei.job_id JOB_ID
,detail.compensation_rule_set
,nvl(detail.cost_rate_currency_code,pa_currency.get_currency_code)
,to_char(detail.rate_schedule_id)
,detail.override_type
,detail.acct_rate_type
,detail.acct_rate_date_code
,detail.acct_exchange_rate
,detail.base_hours
from pa_compensation_details detail
-- ,pa_expenditure_items ei
,pa_expenditures exp
where trunc(p_exp_item_date) between trunc(detail.start_date_active)
and trunc(nvl(detail.end_date_active,p_exp_item_date))
and detail.person_id = exp.incurred_by_person_id
and exp.expenditure_id = x_exp_id
--and ei.source_expenditure_item_id is null
-- and ei.expenditure_item_id = p_exp_item_id
-- and ei.po_line_id IS NULL
;
select
p_job_id,
p_org_id ORG_ID
,TO_CHAR(x_exp_item_date,'YYYY/MM/DD') expenditure_item_date
,p_organization_id orgj_id
from dual;
select rule.costing_method, rule.rate_source_code
into x_costing_method, x_rate_source_code
from pa_compensation_rule_sets rule
where rule.compensation_rule_set = l_costing_rule_tab(1);
select ei.org_id ORG_ID
,TO_CHAR(ei.expenditure_item_date,'YYYY/MM/DD') expenditure_item_date
,nvl(ei.override_to_organization_id
,exp.incurred_by_organization_id) orgj_id
,exp.incurred_by_person_id incurred_by_person_id
,ei.job_id JOB_ID
,detail.compensation_rule_set
,nvl(detail.cost_rate_currency_code,pa_currency.get_currency_code)
,to_char(detail.rate_schedule_id)
,detail.override_type
,detail.acct_rate_type
,detail.acct_rate_date_code
,detail.acct_exchange_rate
,detail.base_hours
from pa_compensation_details detail
,pa_expenditure_items ei
,pa_expenditures exp
where trunc(p_exp_item_date) between trunc(detail.start_date_active)
and trunc(nvl(detail.end_date_active,p_exp_item_date))
and detail.person_id = exp.incurred_by_person_id
and exp.expenditure_id = ei.expenditure_id
--and ei.source_expenditure_item_id is null
and ei.expenditure_item_id = p_exp_item_id
and ei.po_line_id IS NULL;
select rule.costing_method, rule.rate_source_code
into x_costing_method, x_rate_source_code
from pa_compensation_rule_sets rule
where rule.compensation_rule_set = l_costing_rule_tab(1);
UPDATE
pa_pay_dist_lines
SET
ACCT_RAW_COST = DENOM_RAW_COST ,
ACCT_CURRENCY_CODE = DENOM_CURRENCY_CODE
WHERE
DENOM_CURRENCY_CODE = G_FUNC_CURR_CODE AND
INTERFACE_RUN_ID = G_INTERFACE_RUN_ID;
write_log(Log,'updated ACCT amounts for records ' || SQL%rowcount);
SELECT
pay_dist_id ,
pay_source_id ,
pay_element_type_id ,
pay_element_type_code ,
person_id ,
TRUNC(expenditure_item_date)expenditure_item_date,
denom_currency_code ,
denom_raw_cost
FROM
pa_pay_dist_lines
WHERE
interface_run_id = G_INTERFACE_RUN_ID AND
/* person_id = G_PERSON_ID AND */
DENOM_CURRENCY_CODE <> G_FUNC_CURR_CODE
)
LOOP
write_log(Log,'Getting conversion attributes');
UPDATE
pa_pay_dist_lines
SET
ACCT_RAW_COST = l_acct_pay_amount ,
ACCT_CURRENCY_CODE = G_FUNC_CURR_CODE ,
ACCT_EXCHANGE_RATE = l_acct_exch_rate ,
ACCT_RATE_DATE = l_conversion_date,
ACCT_RATE_TYPE = l_acct_rate_type
WHERE
pay_dist_id = X.PAY_DIST_ID;
INSERT
INTO
PA_PAY_REJECTIONS_ALL
(
PAY_REJECTION_ID ,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID,
PAY_ELEMENT_TYPE_CODE,
REJECTION_CODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
)
VALUES
(
PA_PAY_REJECTION_ID_S.nextval,
G_INTERFACE_RUN_ID ,
G_PERSON_ID ,
G_ASSIGNMENT_ID ,
P_PAY_SOURCE_ID ,
P_PAY_ELEMENT_TYPE_ID ,
P_PAY_ELEMENT_TYPE_CODE ,
P_REJECTION_CODE ,
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID
);
INSERT
INTO
PA_PAY_REJECTIONS_ALL
(
PAY_REJECTION_ID ,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID,
PAY_ELEMENT_TYPE_CODE,
REJECTION_CODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
)
select PA_PAY_REJECTION_ID_S.nextval,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
P_PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID ,
PAY_ELEMENT_TYPE_CODE ,
P_REJECTION_CODE ,
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID
from pa_pay_source_amounts where pay_source_id = P_PAY_SOURCE_ID;
SELECT
MIN(PAY_REJECTION_ID)
INTO
L_PARENT_REJECTION_ID
FROM
PA_PAY_REJECTIONS_ALL
WHERE
INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
PERSON_ID = G_PERSON_ID ;
SELECT PPS.PERSON_ID , Min(PPR.PAY_REJECTION_ID) PARENT_REJECTION_ID
FROM PA_PAY_REJECTIONS_ALL PPR , PA_PAY_SOURCE_AMOUNTS PPS
WHERE PPR.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND PPR.INTERFACE_RUN_ID = PPS.INTERFACE_RUN_ID
AND PPR.PAY_SOURCE_ID = PPS.PAY_SOURCE_ID
GROUP BY PPS.PERSON_ID
)
LOOP
INSERT
INTO
PA_PAY_REJECTIONS_ALL
(
PAY_REJECTION_ID ,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID,
PAY_ELEMENT_TYPE_CODE,
PARENT_REJECTION_ID,
REJECTION_CODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
)
SELECT
PA_PAY_REJECTION_ID_S.NEXTVAL,
G_INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID,
PAY_ELEMENT_TYPE_CODE,
X.PARENT_REJECTION_ID ,
'RC 85' ,
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID
FROM
PA_PAY_SOURCE_AMOUNTS
WHERE
PERSON_ID = X.PERSON_ID AND
INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
PAY_source_id NOT IN
(
SELECT DISTINCT
PAY_source_id
FROM
PA_PAY_REJECTIONS_ALL
WHERE
PERSON_ID = X.PERSON_ID AND
INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
) ;
SELECT INT_EXT_INDICATOR
INTO
l_int_ext_flag
FROM pa_pay_audit_all
WHERE INTERFACE_RUN_ID = G_INTERFACE_RUN_ID ;
INSERT
INTO
PA_PAY_REJECTIONS_ALL
(PAY_REJECTION_ID ,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_ELEMENT_TYPE_ID,
REJECTION_CODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
)
SELECT
PA_PAY_REJECTION_ID_S.NEXTVAL, rej.*
from
(select DISTINCT
G_INTERFACE_RUN_ID INTERFACE_RUN_ID,
G_PERSON_ID PERSON_ID,
G_ASSIGNMENT_ID ASSIGNMENT_ID,
ELEMENT_TYPE_ID ,
'RC 05' REJECTION_CODE,
G_RUN_DATE LAST_UPDATE_DATE ,
G_USER_ID LAST_UPDATED_BY ,
G_RUN_DATE CREATION_DATE ,
G_USER_ID CREATED_BY
FROM
PA_PAY_INTERFACE_TEMP TEMP, PA_PAY_AUDIT AUD
WHERE
AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
-- TEMP.TIME_PERIOD_ID = AUD.TIME_PERIOD_ID AND
TRUNC(NVL(TEMP.SOURCE_START_DATE,TEMP.TIME_PERIOD_START_DATE)) = TRUNC(NVL(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE))
AND TRUNC(NVL(TEMP.SOURCE_END_DATE,TEMP.TIME_PERIOD_END_DATE)) = TRUNC(NVL(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE)) /* bug 12690224*/
AND TEMP.PERSON_ID = G_PERSON_ID AND
ELEMENT_TYPE_ID NOT IN
(
SELECT
PAY_ELEMENT_TYPE_ID
FROM
PA_PAY_SOURCE_AMOUNTS PSA
WHERE
PSA.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
PSA.PERSON_ID = G_PERSON_ID /*AND
PAY_SOURCE_ID IN
(
SELECT
PAY_SOURCE_ID
FROM
PA_PAY_REJECTIONS_ALL
WHERE
PERSON_ID = G_PERSON_ID AND
INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
)*/
))rej ;
INSERT
INTO
PA_PAY_REJECTIONS_ALL
(PAY_REJECTION_ID ,
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAY_ELEMENT_TYPE_CODE,
REJECTION_CODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
)
SELECT
PA_PAY_REJECTION_ID_S.NEXTVAL, rej.*
from
(select DISTINCT
G_INTERFACE_RUN_ID INTERFACE_RUN_ID,
G_PERSON_ID PERSON_ID,
G_ASSIGNMENT_ID ASSIGNMENT_ID,
PAY_ELEMENT_TYPE_CODE ,
'RC 05' REJECTION_CODE,
G_RUN_DATE LAST_UPDATE_DATE ,
G_USER_ID LAST_UPDATED_BY ,
G_RUN_DATE CREATION_DATE ,
G_USER_ID CREATED_BY
FROM
PA_PAY_EXT_INTERAFACE_V EXT, PA_PAY_AUDIT AUD
WHERE
AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
EXT.TIME_PERIOD_ID = AUD.TIME_PERIOD_ID AND
EXT.PERSON_ID = G_PERSON_ID AND
PAY_ELEMENT_TYPE_CODE NOT IN
(
SELECT
PAY_ELEMENT_TYPE_CODE
FROM
PA_PAY_SOURCE_AMOUNTS PSA
WHERE
PSA.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
PSA.PERSON_ID = G_PERSON_ID
))rej ;
SELECT 'Y' INTO l_burden_exists FROM dual
WHERE EXISTS (SELECT 1
FROM pa_pay_dist_lines
WHERE SYSTEM_LINKAGE_FUNCTION = 'BTC'
AND INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND PERSON_ID = G_PERSON_ID ) ;
pa_transactions.InsertExpGroup (
l_exp_group,
'RELEASED',
G_PAY_PERIOD_END,
'BTC',
0,
NULL,
NULL,
G_ORG_ID);
(SELECT DISTINCT organization_id ,
denom_currency_code
FROM pa_pay_dist_lines
WHERE SYSTEM_LINKAGE_FUNCTION = 'BTC'
AND INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND PERSON_ID = G_PERSON_ID
)
LOOP
select pa_expenditures_s.nextval
into l_expenditure_id from dual;
write_log(LOG,'Calling InsertExp' );
pa_transactions.InsertExp(
x_expenditure_id =>l_expenditure_id,
x_expend_status =>'APPROVED',
x_expend_ending => pa_utils.NewGetWeekEnding((G_PAY_PERIOD_END)), --Bug 2236707,3551106
x_expend_class => 'BT',
x_inc_by_person => G_PERSON_ID ,
x_inc_by_org => X.organization_id,
x_expend_group => l_exp_group,
x_entered_by_id =>X.organization_id,
x_created_by_id =>0,
x_attribute_category => null,
x_attribute1 => null,
x_attribute2 => null,
x_attribute3 => null,
x_attribute4 => null,
x_attribute5 => null,
x_attribute6 => null,
x_attribute7 => null,
x_attribute8 => null,
x_attribute9 => null,
x_attribute10=> null,
x_description=> null,
x_control_total=> null,
x_denom_currency_code =>X.denom_currency_code,
x_acct_currency_code => G_FUNC_CURR_CODE,
x_acct_rate_type => null,
x_acct_rate_date => null,
x_acct_exchange_rate=> null
,X_person_type => null
,P_Org_Id => G_ORG_ID
,X_vendor_id => null
);
write_log(LOG,'After InsertExp' );
-- For each organization and denom currency above , insert all items
FOR Y IN
(
SELECT
DIST. ORGANIZATION_ID ,
DIST.PROJECT_ID ,
DIST.TASK_ID ,
DIST.EXPENDITURE_TYPE ,
DIST.EXPENDITURE_ITEM_DATE ,
PA.PROJECT_CURRENCY_CODE ,
PA.PROJFUNC_CURRENCY_CODE ,
DIST.DENOM_CURRENCY_CODE ,
DIST.ACCT_CURRENCY_CODE ,
SUM(DIST.DENOM_BURDEN_COST) DENOM_BURDEN_COST
FROM
PA_PAY_DIST_LINES DIST ,
PA_PROJECTS_ALL PA
WHERE
DIST.PROJECT_ID = PA.PROJECT_ID AND
SYSTEM_LINKAGE_FUNCTION = 'BTC' AND
INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
PERSON_ID = G_PERSON_ID AND
ORGANIZATION_ID = X.ORGANIZATION_ID AND
DENOM_CURRENCY_CODE = X.DENOM_CURRENCY_CODE
GROUP BY
DIST. ORGANIZATION_ID ,
DIST.PROJECT_ID ,
DIST.TASK_ID ,
DIST.EXPENDITURE_TYPE ,
DIST.EXPENDITURE_ITEM_DATE ,
PA.PROJECT_CURRENCY_CODE ,
PA.PROJFUNC_CURRENCY_CODE ,
DIST.DENOM_CURRENCY_CODE ,
DIST.ACCT_CURRENCY_CODE
ORDER BY
PROJECT_ID ,
TASK_ID
)
LOOP
i := i+1;
select pa_expenditure_items_s.nextval
into l_exp_item_id from dual;
FOR X IN (SELECT DISTINCT
PAY_SOURCE_ID,PAY_ELEMENT_TYPE_ID,PAY_ELEMENT_TYPE_CODE
FROM
PA_PAY_DIST_LINES
WHERE
INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
PERSON_ID = G_PERSON_ID AND
PROJECT_ID = Y.PROJECT_ID AND
TASK_ID = Y.TASK_ID AND
SYSTEM_LINKAGE_FUNCTION = 'BTC' AND
EXPENDITURE_TYPE = Y.EXPENDITURE_TYPE)
LOOP
reject_pay_element ( X.PAY_SOURCE_ID,X.PAY_ELEMENT_TYPE_ID,X.PAY_ELEMENT_TYPE_CODE,'RC 75');
FOR X IN (SELECT DISTINCT
PAY_SOURCE_ID,PAY_ELEMENT_TYPE_ID,PAY_ELEMENT_TYPE_CODE
FROM
PA_PAY_DIST_LINES
WHERE
INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
PERSON_ID = G_PERSON_ID AND
PROJECT_ID = Y.PROJECT_ID AND
TASK_ID = Y.TASK_ID AND
SYSTEM_LINKAGE_FUNCTION = 'BTC' AND
EXPENDITURE_TYPE = Y.EXPENDITURE_TYPE)
LOOP
reject_pay_element ( X.PAY_SOURCE_ID,X.PAY_ELEMENT_TYPE_ID,X.PAY_ELEMENT_TYPE_CODE,'RC 65');
INSERT
INTO
PA_EXPENDITURE_ITEMS_ALL
(
EXPENDITURE_ITEM_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
EXPENDITURE_ID ,
TASK_ID ,
EXPENDITURE_ITEM_DATE ,
EXPENDITURE_TYPE ,
COST_DISTRIBUTED_FLAG ,
REVENUE_DISTRIBUTED_FLAG ,
BILLABLE_FLAG ,
BILL_HOLD_FLAG ,
QUANTITY ,
NET_ZERO_ADJUSTMENT_FLAG ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
PROJECT_ID ,
ORG_ID ,
SYSTEM_LINKAGE_FUNCTION ,
DENOM_CURRENCY_CODE ,
/*DENOM_RAW_COST ,*/
DENOM_BURDENED_COST,
ACCT_CURRENCY_CODE ,
PROJECT_CURRENCY_CODE ,
ASSIGNMENT_ID ,
PROJFUNC_CURRENCY_CODE ,
HISTORICAL_FLAG ,
COSTING_METHOD ,
PAYROLL_ACCRUAL_FLAG ,
INTERFACE_RUN_ID ,
COST_BURDEN_DISTRIBUTED_FLAG,
CC_CROSS_CHARGE_TYPE ,
CC_BL_DISTRIBUTED_CODE ,
CC_IC_PROCESSED_CODE ,
CC_PRVDR_COST_RECLASS_CODE
)
VALUES
(
l_exp_item_id ,
G_RUN_DATE , -- LAST_UPDATE_DATE
G_USER_ID , -- LAST_UPDATED_BY
G_RUN_DATE , -- CREATION_DATE
G_USER_ID , -- CREATED_BY
l_expenditure_id ,
Y.TASK_ID ,
Y.EXPENDITURE_ITEM_DATE ,
Y.EXPENDITURE_TYPE ,
'N' , --COST_DISTRIBUTED_FLAG
'N' ,--REVENUE_DISTRIBUTED_FLAG
l_billable_flag ,--BILLABLE_FLAG
'N' ,--BILL_HOLD_FLAG
0 ,--QUANTITY
'N' , --NET_ZERO_ADJUSTMENT_FLAG
G_LOGIN_ID ,--LAST_UPDATE_LOGIN
G_REQUEST_ID ,--REQUEST_ID
275 ,
G_PROGRAM_ID ,
G_RUN_DATE ,--PROGRAM_UPDATE_DATE
Y.PROJECT_ID ,
G_ORG_ID ,
'BTC' ,
Y.denom_CURRENCY_CODE , --DENOM_CURRENCY_CODE
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1( Y.denom_burden_cost, Y.denom_CURRENCY_CODE) ,--DENOM_BURDENED_COST
G_FUNC_CURR_CODE ,
Y.PROJECT_CURRENCY_CODE ,
G_ASSIGNMENT_ID ,
Y.PROJFUNC_CURRENCY_CODE ,
'N' ,--HISTORICAL_FLAG
'ACTUAL' ,
'N' ,
G_INTERFACE_RUN_ID ,
'X' ,
'NO' ,
'X' ,
'X' ,
'N'
) ;
UPDATE PA_PAY_DIST_LINES
SET TARGET_EXPENDITURE_ITEM_ID = l_exp_item_id
WHERE ORGANIZATION_ID = Y. ORGANIZATION_ID
AND PROJECT_ID = Y.PROJECT_ID
AND TASK_ID = Y.TASK_ID
AND EXPENDITURE_TYPE = Y.EXPENDITURE_TYPE
AND EXPENDITURE_ITEM_DATE = Y.EXPENDITURE_ITEM_DATE
AND DENOM_CURRENCY_CODE = Y.DENOM_CURRENCY_CODE
AND ACCT_CURRENCY_CODE = Y.ACCT_CURRENCY_CODE ;
/* UPDATE pa_expenditure_items_all
SET interface_run_id = G_INTERFACE_RUN_ID
,COSTING_METHOD = 'ACTUAL'
,PAYROLL_ACCRUAL_FLAG = 'N'
WHERE expenditure_id = l_expenditure_id ; */
SELECT 'Y' INTO l_misc_exists FROM dual
WHERE EXISTS (SELECT 1
FROM pa_pay_dist_lines
WHERE SYSTEM_LINKAGE_FUNCTION = 'PJ'
AND INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND PERSON_ID = G_PERSON_ID ) ;
pa_transactions.InsertExpGroup (
l_exp_group,
'RELEASED',
G_PAY_PERIOD_END,
'PJ',
0,
NULL,
NULL,
G_ORG_ID);
(SELECT DISTINCT organization_id ,
denom_currency_code
FROM pa_pay_dist_lines
WHERE SYSTEM_LINKAGE_FUNCTION = 'PJ'
AND INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND PERSON_ID = G_PERSON_ID
)
LOOP
select pa_expenditures_s.nextval
into l_expenditure_id from dual;
write_log(LOG,'Calling InsertExp for ' || X.organization_id || ' - ' || x.denom_currency_code );
pa_transactions.InsertExp(
x_expenditure_id =>l_expenditure_id,
x_expend_status =>'APPROVED',
x_expend_ending => pa_utils.NewGetWeekEnding((G_PAY_PERIOD_END)), --Bug 2236707,3551106
x_expend_class => 'PU',
x_inc_by_person => G_PERSON_ID ,
x_inc_by_org => X.organization_id,
x_expend_group => l_exp_group,
x_entered_by_id =>X.organization_id,
x_created_by_id =>0,
x_attribute_category => null,
x_attribute1 => null,
x_attribute2 => null,
x_attribute3 => null,
x_attribute4 => null,
x_attribute5 => null,
x_attribute6 => null,
x_attribute7 => null,
x_attribute8 => null,
x_attribute9 => null,
x_attribute10=> null,
x_description=> null,
x_control_total=> null,
x_denom_currency_code =>X.denom_currency_code,
x_acct_currency_code => G_FUNC_CURR_CODE,
x_acct_rate_type => null,
x_acct_rate_date => null,
x_acct_exchange_rate=> null
,X_person_type => null
,P_Org_Id => G_ORG_ID
,X_vendor_id => null
);
write_log(LOG,'After InsertExp' );
-- For each organization and denom currency above , insert all items
FOR Y IN
(
SELECT
DIST.ORGANIZATION_ID ,
DIST.PROJECT_ID ,
DIST.TASK_ID ,
DIST.EXPENDITURE_TYPE ,
DIST.EXPENDITURE_ITEM_DATE ,
PA.PROJECT_CURRENCY_CODE ,
PA.PROJFUNC_CURRENCY_CODE ,
DIST.DENOM_CURRENCY_CODE ,
DIST.acct_currency_code,
SUM(DIST.DENOM_RAW_COST) DENOM_RAW_COST
FROM
PA_PAY_DIST_LINES DIST,
PA_PROJECTS_ALL PA
WHERE
DIST.PROJECT_ID = PA.PROJECT_ID AND
SYSTEM_LINKAGE_FUNCTION = 'PJ' AND
INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
PERSON_ID = G_PERSON_ID AND
ORGANIZATION_ID = X.ORGANIZATION_ID AND
DENOM_CURRENCY_CODE = X.DENOM_CURRENCY_CODE
GROUP BY
DIST.ORGANIZATION_ID ,
DIST.PROJECT_ID ,
DIST.TASK_ID ,
DIST.EXPENDITURE_TYPE ,
DIST.EXPENDITURE_ITEM_DATE ,
PA.PROJECT_CURRENCY_CODE ,
PA.PROJFUNC_CURRENCY_CODE ,
DIST.DENOM_CURRENCY_CODE ,
DIST.acct_currency_code
ORDER BY
PROJECT_ID ,
TASK_ID
)
LOOP
i := i+1;
select pa_expenditure_items_s.nextval
into l_exp_item_id from dual;
FOR X IN (SELECT DISTINCT
PAY_SOURCE_ID,PAY_ELEMENT_TYPE_ID,PAY_ELEMENT_TYPE_CODE
FROM
PA_PAY_DIST_LINES
WHERE
INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
PERSON_ID = G_PERSON_ID AND
PROJECT_ID = Y.PROJECT_ID AND
TASK_ID = Y.TASK_ID AND
SYSTEM_LINKAGE_FUNCTION = 'PJ' AND
EXPENDITURE_TYPE = Y.EXPENDITURE_TYPE)
LOOP
reject_pay_element ( X.PAY_SOURCE_ID,X.PAY_ELEMENT_TYPE_ID,X.PAY_ELEMENT_TYPE_CODE,'RC 75');
FOR X IN (SELECT DISTINCT
PAY_SOURCE_ID,PAY_ELEMENT_TYPE_ID,PAY_ELEMENT_TYPE_CODE
FROM
PA_PAY_DIST_LINES
WHERE
INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
PERSON_ID = G_PERSON_ID AND
PROJECT_ID = Y.PROJECT_ID AND
TASK_ID = Y.TASK_ID AND
SYSTEM_LINKAGE_FUNCTION = 'PJ' AND
EXPENDITURE_TYPE = Y.EXPENDITURE_TYPE)
LOOP
reject_pay_element ( X.PAY_SOURCE_ID,X.PAY_ELEMENT_TYPE_ID,X.PAY_ELEMENT_TYPE_CODE,'RC 65');
INSERT
INTO
PA_EXPENDITURE_ITEMS_ALL
(
EXPENDITURE_ITEM_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
EXPENDITURE_ID ,
TASK_ID ,
EXPENDITURE_ITEM_DATE ,
EXPENDITURE_TYPE ,
COST_DISTRIBUTED_FLAG ,
REVENUE_DISTRIBUTED_FLAG ,
BILLABLE_FLAG ,
BILL_HOLD_FLAG ,
QUANTITY ,
NET_ZERO_ADJUSTMENT_FLAG ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
PROJECT_ID ,
ORG_ID ,
SYSTEM_LINKAGE_FUNCTION ,
DENOM_CURRENCY_CODE ,
DENOM_RAW_COST ,
/*DENOM_BURDENED_COST*/
ACCT_CURRENCY_CODE ,
PROJECT_CURRENCY_CODE ,
ASSIGNMENT_ID ,
PROJFUNC_CURRENCY_CODE ,
HISTORICAL_FLAG ,
COSTING_METHOD ,
PAYROLL_ACCRUAL_FLAG ,
INTERFACE_RUN_ID ,
COST_BURDEN_DISTRIBUTED_FLAG,
CC_CROSS_CHARGE_TYPE ,
CC_BL_DISTRIBUTED_CODE ,
CC_IC_PROCESSED_CODE ,
CC_PRVDR_COST_RECLASS_CODE
)
VALUES
(
l_exp_item_id ,
G_RUN_DATE , -- LAST_UPDATE_DATE
G_USER_ID , -- LAST_UPDATED_BY
G_RUN_DATE , -- CREATION_DATE
G_USER_ID , -- CREATED_BY
l_expenditure_id ,
Y.TASK_ID ,
Y.EXPENDITURE_ITEM_DATE ,
Y.EXPENDITURE_TYPE ,
'N' , --COST_DISTRIBUTED_FLAG
'N' ,--REVENUE_DISTRIBUTED_FLAG
l_billable_flag ,--BILLABLE_FLAG
'N' ,--BILL_HOLD_FLAG
0 ,--QUANTITY
'N' , --NET_ZERO_ADJUSTMENT_FLAG
G_LOGIN_ID ,--LAST_UPDATE_LOGIN
G_REQUEST_ID ,--REQUEST_ID
275 ,
G_PROGRAM_ID ,
G_RUN_DATE ,--PROGRAM_UPDATE_DATE
Y.PROJECT_ID ,
G_ORG_ID ,
'PJ' ,
Y.denom_CURRENCY_CODE , --DENOM_CURRENCY_CODE
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1( Y.denom_raw_cost, Y.denom_CURRENCY_CODE) ,--DENOM_RAW_COST
G_FUNC_CURR_CODE ,
Y.PROJECT_CURRENCY_CODE ,
G_ASSIGNMENT_ID ,
Y.PROJFUNC_CURRENCY_CODE ,
'N' ,--HISTORICAL_FLAG
'ACTUAL' ,
'N' ,
G_INTERFACE_RUN_ID ,
'X' ,
'NO' ,
'X' ,
'X' ,
'N'
);
UPDATE PA_PAY_DIST_LINES
SET TARGET_EXPENDITURE_ITEM_ID = l_exp_item_id
WHERE ORGANIZATION_ID = Y. ORGANIZATION_ID
AND PROJECT_ID = Y.PROJECT_ID
AND TASK_ID = Y.TASK_ID
AND EXPENDITURE_TYPE = Y.EXPENDITURE_TYPE
AND EXPENDITURE_ITEM_DATE = Y.EXPENDITURE_ITEM_DATE
--AND PROJECT_CURRENCY_CODE = Y.PROJECT_CURRENCY_CODE
--AND PROJFUNC_CURRENCY_CODE = Y.PROJFUNC_CURRENCY_CODE
AND DENOM_CURRENCY_CODE = Y.DENOM_CURRENCY_CODE
AND ACCT_CURRENCY_CODE = Y.ACCT_CURRENCY_CODE ;
UPDATE pa_expenditure_items_all
SET interface_run_id = G_INTERFACE_RUN_ID
,COSTING_METHOD = 'ACTUAL'
,PAYROLL_ACCRUAL_FLAG = 'N'
WHERE expenditure_id = l_expenditure_id ;
FOR X IN ( SELECT DISTINCT
SOURCE_EXPENDITURE_ITEM_ID ,
EI.EXPENDITURE_ITEM_DATE,
DENOM_CURRENCY_CODE,
ORIG_CURR_CODE ,
Sum(ORIG_AMOUNT) ORIG_AMOUNT ,
SUM(DENOM_RAW_COST) DENOM_RAW_COST
FROM
PA_PAY_DIST_LINES DIST , PA_PAY_EXP_TEMP EI
WHERE
DIST.SOURCE_EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID AND
DIST.SOURCE_EXPENDITURE_ITEM_ID IS NOT NULL AND
DIST.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
DIST.PERSON_ID = G_PERSON_ID AND
EI.COST_DISTRIBUTED_FLAG = 'N' AND
EI.PAYROLL_ACCRUAL_FLAG is NULL
GROUP BY
DIST.SOURCE_EXPENDITURE_ITEM_ID ,
EI.EXPENDITURE_ITEM_DATE,
DIST.DENOM_CURRENCY_CODE,
EI.ORIG_CURR_CODE
)
LOOP
l_denom_orig_amount := NULL;
UPDATE
PA_EXPENDITURE_ITEMS_ALL EI
SET DENOM_CURRENCY_CODE = X.DENOM_CURRENCY_CODE
,DENOM_RAW_COST = PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(l_denom_raw_cost, X.DENOM_CURRENCY_CODE)
,REQUEST_ID = G_REQUEST_ID
,INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
,COSTING_METHOD = 'ACTUAL'
,PAYROLL_ACCRUAL_FLAG = 'N'
WHERE
EXPENDITURE_ITEM_ID = X.SOURCE_EXPENDITURE_ITEM_ID;
UPDATE PA_PAY_DIST_LINES
SET TARGET_EXPENDITURE_ITEM_ID = SOURCE_EXPENDITURE_ITEM_ID
WHERE SOURCE_EXPENDITURE_ITEM_ID = X.SOURCE_EXPENDITURE_ITEM_ID;
write_log(LOG,'Case of uncosted EIs .. updated expendture item '|| X.SOURCE_EXPENDITURE_ITEM_ID);
FOR X IN (SELECT DISTINCT
DIST.SOURCE_EXPENDITURE_ITEM_ID,DIST.DENOM_CURRENCY_CODE,
TEMP.COST_DISTRIBUTED_FLAG,TEMP.PAYROLL_ACCRUAL_FLAG , TEMP.ORIG_CURR_CODE,
TEMP.ORIG_AMOUNT,TEMP.EXPENDITURE_ITEM_DATE
FROM
PA_PAY_DIST_LINES DIST , PA_PAY_EXP_TEMP TEMP
WHERE
DIST.SOURCE_EXPENDITURE_ITEM_ID IS NOT NULL AND
DIST.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
DIST.PERSON_ID = G_PERSON_ID AND
DIST.SOURCE_EXPENDITURE_ITEM_ID = TEMP.EXPENDITURE_ITEM_ID AND
TEMP. PAYROLL_ACCRUAL_FLAG = 'Y')
LOOP
-- Accrual EI , but was marked for re-calc.
-- Mark them back as costed and copy amounts from CDL
IF ((X.COST_DISTRIBUTED_FLAG = 'N') AND (X.PAYROLL_ACCRUAL_FLAG = 'Y')) THEN
UPDATE PA_EXPENDITURE_ITEMS EI
SET
EI.COST_DISTRIBUTED_FLAG='Y',
(EI.RAW_COST , EI.RAW_COST_RATE, EI.BURDEN_COST, EI.BURDEN_COST_RATE,
EI.ACCT_RAW_COST, EI.ACCT_BURDENED_COST, EI.DENOM_RAW_COST,
EI.DENOM_BURDENED_COST, EI.PROJECT_RAW_COST, EI.PROJECT_BURDENED_COST) =
(
SELECT
CDL.AMOUNT,
(CDL.DENOM_RAW_COST/DECODE(CDL.QUANTITY, NULL, 1,0, 1,
CDL.QUANTITY)) ,
CDL.BURDENED_COST ,
(CDL.DENOM_BURDENED_COST/DECODE(CDL.QUANTITY, NULL,1,0,1,
CDL.QUANTITY)) ,
CDL.ACCT_RAW_COST ,
CDL.ACCT_BURDENED_COST ,
CDL.DENOM_RAW_COST ,
CDL.DENOM_BURDENED_COST ,
CDL.PROJECT_RAW_COST ,
CDL.PROJECT_BURDENED_COST
FROM
PA_COST_DISTRIBUTION_LINES CDL
WHERE
CDL.EXPENDITURE_ITEM_ID =EI.EXPENDITURE_ITEM_ID AND
CDL.LINE_TYPE ='R' AND
NVL(CDL.REVERSED_FLAG,'N')='N' AND
CDL.LINE_NUM_REVERSED IS NULL
)
WHERE
EXPENDITURE_ITEM_ID =X.SOURCE_EXPENDITURE_ITEM_ID AND
EI.COST_DISTRIBUTED_FLAG='N' AND
EI.ADJUSTMENT_TYPE IS NOT NULL AND
EI.COSTING_METHOD IS NOT NULL AND
EXISTS
(
SELECT
1
FROM
PA_COST_DISTRIBUTION_LINES CDL2
WHERE
CDL2.EXPENDITURE_ITEM_ID =EI.EXPENDITURE_ITEM_ID
) ;
SELECT ei.expenditure_item_id INTO l_reversal_id
FROM pa_expenditure_items ei
WHERE ei.adjusted_expenditure_item_id= X.SOURCE_EXPENDITURE_ITEM_ID
AND ei.net_zero_adjustment_flag = 'Y'
AND ei.cost_distributed_flag='N';
INSERT
INTO
PA_EXPENDITURE_ITEMS_ALL
(
EXPENDITURE_ITEM_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
EXPENDITURE_ID ,
TASK_ID ,
EXPENDITURE_ITEM_DATE ,
EXPENDITURE_TYPE ,
COST_DISTRIBUTED_FLAG ,
REVENUE_DISTRIBUTED_FLAG ,
BILLABLE_FLAG ,
BILL_HOLD_FLAG ,
QUANTITY ,
/*NON_LABOR_RESOURCE ,
ORGANIZATION_ID , */
OVERRIDE_TO_ORGANIZATION_ID ,
/*RAW_COST ,
RAW_COST_RATE ,
BURDEN_COST ,
BURDEN_COST_RATE ,
COST_DIST_REJECTION_CODE ,
LABOR_COST_MULTIPLIER_NAME ,
RAW_REVENUE ,
BILL_RATE ,
ACCRUED_REVENUE ,
ACCRUAL_RATE ,
ADJUSTED_REVENUE ,
ADJUSTED_RATE ,
BILL_AMOUNT ,
FORECAST_REVENUE ,
BILL_RATE_MULTIPLIER ,
REV_DIST_REJECTION_CODE ,
EVENT_NUM ,
EVENT_TASK_ID ,
BILL_JOB_ID ,
BILL_JOB_BILLING_TITLE ,
BILL_EMPLOYEE_BILLING_TITLE ,
ADJUSTED_EXPENDITURE_ITEM_ID , */
NET_ZERO_ADJUSTMENT_FLAG ,
TRANSFERRED_FROM_EXP_ITEM_ID ,
CONVERTED_FLAG ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
/* COST_IND_COMPILED_SET_ID ,
REV_IND_COMPILED_SET_ID ,
INV_IND_COMPILED_SET_ID ,
COST_BURDEN_DISTRIBUTED_FLAG ,
IND_COST_DIST_REJECTION_CODE ,*/
ORIG_TRANSACTION_REFERENCE ,
TRANSACTION_SOURCE ,
PROJECT_ID ,
SOURCE_EXPENDITURE_ITEM_ID ,
JOB_ID ,
ORG_ID ,
SYSTEM_LINKAGE_FUNCTION ,
BURDEN_SUM_DEST_RUN_ID ,
/* RECEIPT_CURRENCY_AMOUNT ,
RECEIPT_CURRENCY_CODE ,
RECEIPT_EXCHANGE_RATE ,*/
DENOM_CURRENCY_CODE ,
DENOM_RAW_COST ,
/*DENOM_BURDENED_COST ,*/
ACCT_CURRENCY_CODE ,
/* ACCT_RATE_DATE ,
ACCT_RATE_TYPE ,
ACCT_EXCHANGE_RATE ,
ACCT_RAW_COST ,
ACCT_BURDENED_COST ,
ACCT_EXCHANGE_ROUNDING_LIMIT ,*/
PROJECT_CURRENCY_CODE ,
/*PROJECT_RATE_DATE ,
PROJECT_RATE_TYPE ,
PROJECT_EXCHANGE_RATE ,
DENORM_ID ,
CC_CROSS_CHARGE_CODE , */
CC_PRVDR_ORGANIZATION_ID ,
CC_RECVR_ORGANIZATION_ID ,
/* CC_REJECTION_CODE ,
DENOM_TP_CURRENCY_CODE ,
DENOM_TRANSFER_PRICE ,
ACCT_TP_RATE_TYPE ,
ACCT_TP_RATE_DATE ,
ACCT_TP_EXCHANGE_RATE ,
ACCT_TRANSFER_PRICE ,
PROJACCT_TRANSFER_PRICE ,
CC_MARKUP_BASE_CODE ,
TP_BASE_AMOUNT ,
CC_CROSS_CHARGE_TYPE ,*/
RECVR_ORG_ID ,
/*CC_BL_DISTRIBUTED_CODE ,
CC_IC_PROCESSED_CODE ,
TP_IND_COMPILED_SET_ID ,
TP_BILL_RATE ,
TP_BILL_MARKUP_PERCENTAGE ,
TP_SCHEDULE_LINE_PERCENTAGE ,
TP_RULE_PERCENTAGE ,
CC_PRVDR_COST_RECLASS_CODE ,
CRL_ASSET_CREATION_STATUS_CODE,
CRL_ASSET_CREATION_REJ_CODE ,
COST_JOB_ID ,
TP_JOB_ID ,
PROV_PROJ_BILL_JOB_ID ,
COST_DIST_WARNING_CODE ,
PROJECT_TP_RATE_DATE ,
PROJECT_TP_RATE_TYPE ,
PROJECT_TP_EXCHANGE_RATE ,
PROJFUNC_TP_RATE_DATE ,
PROJFUNC_TP_RATE_TYPE ,
PROJFUNC_TP_EXCHANGE_RATE ,
PROJFUNC_TRANSFER_PRICE ,
BILL_TRANS_FORECAST_CURR_CODE ,
BILL_TRANS_FORECAST_REVENUE ,
PROJFUNC_REV_RATE_DATE ,
PROJFUNC_REV_EXCHANGE_RATE ,
PROJFUNC_COST_RATE_TYPE ,
PROJFUNC_COST_RATE_DATE ,
PROJFUNC_COST_EXCHANGE_RATE ,
PROJECT_RAW_COST ,
PROJECT_BURDENED_COST ,*/
ASSIGNMENT_ID ,
WORK_TYPE_ID ,
/* PROJFUNC_RAW_REVENUE ,
PROJECT_BILL_AMOUNT ,*/
PROJFUNC_CURRENCY_CODE ,
/* PROJECT_RAW_REVENUE ,
PROJECT_TRANSFER_PRICE ,
TP_AMT_TYPE_CODE ,
BILL_TRANS_CURRENCY_CODE ,
BILL_TRANS_RAW_REVENUE ,
BILL_TRANS_BILL_AMOUNT ,
BILL_TRANS_ADJUSTED_REVENUE ,
REVPROC_CURRENCY_CODE ,
REVPROC_RATE_TYPE ,
REVPROC_RATE_DATE ,
REVPROC_EXCHANGE_RATE ,
INVPROC_CURRENCY_CODE ,
INVPROC_RATE_TYPE ,
INVPROC_RATE_DATE ,
DISCOUNT_PERCENTAGE ,
LABOR_MULTIPLIER ,
AMOUNT_CALCULATION_CODE ,
BILL_MARKUP_PERCENTAGE ,
RATE_SOURCE_ID ,
INVPROC_EXCHANGE_RATE ,
INV_GEN_REJECTION_CODE ,
PROJFUNC_BILL_AMOUNT ,
PROJECT_REV_RATE_TYPE ,
PROJECT_REV_RATE_DATE ,
PROJECT_REV_EXCHANGE_RATE ,
PROJFUNC_REV_RATE_TYPE ,
PROJFUNC_INV_RATE_TYPE ,
PROJFUNC_INV_RATE_DATE ,
PROJFUNC_INV_EXCHANGE_RATE ,
PROJECT_INV_RATE_TYPE ,
PROJECT_INV_RATE_DATE ,
PROJECT_INV_EXCHANGE_RATE ,
PROJFUNC_FCST_RATE_TYPE ,
PROJFUNC_FCST_RATE_DATE ,
PROJFUNC_FCST_EXCHANGE_RATE ,
PRVDR_ACCRUAL_DATE ,
RECVR_ACCRUAL_DATE ,
RATE_DISC_REASON_CODE ,
POSTED_DENOM_BURDENED_COST ,
POSTED_PROJECT_BURDENED_COST ,
POSTED_PROJFUNC_BURDENED_COST ,
POSTED_ACCT_BURDENED_COST ,
ADJUSTMENT_TYPE ,
CAPITAL_EVENT_ID ,
PO_LINE_ID ,
PO_PRICE_TYPE ,
WIP_RESOURCE_ID ,
INVENTORY_ITEM_ID ,
UNIT_OF_MEASURE ,
SRC_SYSTEM_LINKAGE_FUNCTION ,
DOCUMENT_HEADER_ID ,
DOCUMENT_DISTRIBUTION_ID ,
DOCUMENT_LINE_NUMBER ,
DOCUMENT_PAYMENT_ID ,
VENDOR_ID ,
DOCUMENT_TYPE ,
DOCUMENT_DISTRIBUTION_TYPE ,*/
HISTORICAL_FLAG ,
LOCATION_ID ,
PAY_ELEMENT_TYPE_ID ,
COSTING_METHOD ,
/* RATE_SOURCE_CODE */
PAYROLL_ACCRUAL_FLAG,
INTERFACE_RUN_ID
)
SELECT
l_transfer_id ,
G_RUN_DATE , -- LAST_UPDATE_DATE
G_USER_ID , -- LAST_UPDATED_BY
G_RUN_DATE , -- CREATION_DATE
G_USER_ID , -- CREATED_BY
EXPENDITURE_ID ,
TASK_ID ,
EXPENDITURE_ITEM_DATE ,
EXPENDITURE_TYPE ,
'N' , --COST_DISTRIBUTED_FLAG
'N' ,--REVENUE_DISTRIBUTED_FLAG
BILLABLE_FLAG ,
BILL_HOLD_FLAG ,
QUANTITY ,
/*NON_LABOR_RESOURCE ,
ORGANIZATION_ID , */
OVERRIDE_TO_ORGANIZATION_ID ,
/* RAW_COST ,
RAW_COST_RATE ,
BURDEN_COST ,
BURDEN_COST_RATE ,
COST_DIST_REJECTION_CODE ,
LABOR_COST_MULTIPLIER_NAME ,
RAW_REVENUE ,
BILL_RATE ,
ACCRUED_REVENUE ,
ACCRUAL_RATE ,
ADJUSTED_REVENUE ,
ADJUSTED_RATE ,
BILL_AMOUNT ,
FORECAST_REVENUE ,
BILL_RATE_MULTIPLIER ,
REV_DIST_REJECTION_CODE ,
EVENT_NUM ,
EVENT_TASK_ID ,
BILL_JOB_ID ,
BILL_JOB_BILLING_TITLE ,
BILL_EMPLOYEE_BILLING_TITLE ,
ADJUSTED_EXPENDITURE_ITEM_ID , */
'N' , --NET_ZERO_ADJUSTMENT_FLAG
EXPENDITURE_ITEM_ID , --TRANSFERRED_FROM_EXP_ITEM_ID
CONVERTED_FLAG ,
G_LOGIN_ID ,--LAST_UPDATE_LOGIN
G_REQUEST_ID ,--REQUEST_ID
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
G_RUN_DATE ,--PROGRAM_UPDATE_DATE
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
/* COST_IND_COMPILED_SET_ID ,
REV_IND_COMPILED_SET_ID ,
INV_IND_COMPILED_SET_ID ,
COST_BURDEN_DISTRIBUTED_FLAG ,
IND_COST_DIST_REJECTION_CODE ,*/
ORIG_TRANSACTION_REFERENCE ,
TRANSACTION_SOURCE ,
PROJECT_ID ,
SOURCE_EXPENDITURE_ITEM_ID ,
JOB_ID ,
ORG_ID ,
SYSTEM_LINKAGE_FUNCTION ,
NULL ,--BURDEN_SUM_DEST_RUN_ID
/*RECEIPT_CURRENCY_AMOUNT ,
RECEIPT_CURRENCY_CODE ,
RECEIPT_EXCHANGE_RATE , */
dist.denom_CURRENCY_CODE , --DENOM_CURRENCY_CODE
--dist.denom_raw_cost + l_denom_orig_amount , --DENOM_RAW_COST
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(dist.denom_raw_cost + l_denom_orig_amount,dist.denom_CURRENCY_CODE ) ,
/* DENOM_BURDENED_COST ,*/
ACCT_CURRENCY_CODE ,
/* ACCT_RATE_DATE ,
ACCT_RATE_TYPE ,
ACCT_EXCHANGE_RATE ,
ACCT_RAW_COST ,
ACCT_BURDENED_COST ,
ACCT_EXCHANGE_ROUNDING_LIMIT ,*/
PROJECT_CURRENCY_CODE ,
/* PROJECT_RATE_DATE ,
PROJECT_RATE_TYPE ,
PROJECT_EXCHANGE_RATE ,
DENORM_ID ,
CC_CROSS_CHARGE_CODE , */
CC_PRVDR_ORGANIZATION_ID ,
CC_RECVR_ORGANIZATION_ID ,
/* CC_REJECTION_CODE ,
DENOM_TP_CURRENCY_CODE ,
DENOM_TRANSFER_PRICE ,
ACCT_TP_RATE_TYPE ,
ACCT_TP_RATE_DATE ,
ACCT_TP_EXCHANGE_RATE ,
ACCT_TRANSFER_PRICE ,
PROJACCT_TRANSFER_PRICE ,
CC_MARKUP_BASE_CODE ,
TP_BASE_AMOUNT ,
CC_CROSS_CHARGE_TYPE ,*/
RECVR_ORG_ID ,
/*CC_BL_DISTRIBUTED_CODE ,
CC_IC_PROCESSED_CODE ,
TP_IND_COMPILED_SET_ID ,
TP_BILL_RATE ,
TP_BILL_MARKUP_PERCENTAGE ,
TP_SCHEDULE_LINE_PERCENTAGE ,
TP_RULE_PERCENTAGE ,
CC_PRVDR_COST_RECLASS_CODE ,
CRL_ASSET_CREATION_STATUS_CODE,
CRL_ASSET_CREATION_REJ_CODE ,
COST_JOB_ID ,
TP_JOB_ID ,
PROV_PROJ_BILL_JOB_ID ,
COST_DIST_WARNING_CODE ,
PROJECT_TP_RATE_DATE ,
PROJECT_TP_RATE_TYPE ,
PROJECT_TP_EXCHANGE_RATE ,
PROJFUNC_TP_RATE_DATE ,
PROJFUNC_TP_RATE_TYPE ,
PROJFUNC_TP_EXCHANGE_RATE ,
PROJFUNC_TRANSFER_PRICE ,
BILL_TRANS_FORECAST_CURR_CODE ,
BILL_TRANS_FORECAST_REVENUE ,
PROJFUNC_REV_RATE_DATE ,
PROJFUNC_REV_EXCHANGE_RATE ,
PROJFUNC_COST_RATE_TYPE ,
PROJFUNC_COST_RATE_DATE ,
PROJFUNC_COST_EXCHANGE_RATE ,
PROJECT_RAW_COST ,
PROJECT_BURDENED_COST ,*/
ASSIGNMENT_ID ,
WORK_TYPE_ID ,
/*PROJFUNC_RAW_REVENUE ,
PROJECT_BILL_AMOUNT ,*/
PROJFUNC_CURRENCY_CODE ,
/* PROJECT_RAW_REVENUE ,
PROJECT_TRANSFER_PRICE ,
TP_AMT_TYPE_CODE ,
BILL_TRANS_CURRENCY_CODE ,
BILL_TRANS_RAW_REVENUE ,
BILL_TRANS_BILL_AMOUNT ,
BILL_TRANS_ADJUSTED_REVENUE ,
REVPROC_CURRENCY_CODE ,
REVPROC_RATE_TYPE ,
REVPROC_RATE_DATE ,
REVPROC_EXCHANGE_RATE ,
INVPROC_CURRENCY_CODE ,
INVPROC_RATE_TYPE ,
INVPROC_RATE_DATE ,
DISCOUNT_PERCENTAGE ,
LABOR_MULTIPLIER ,
AMOUNT_CALCULATION_CODE ,
BILL_MARKUP_PERCENTAGE ,
RATE_SOURCE_ID ,
INVPROC_EXCHANGE_RATE ,
INV_GEN_REJECTION_CODE ,
PROJFUNC_BILL_AMOUNT ,
PROJECT_REV_RATE_TYPE ,
PROJECT_REV_RATE_DATE ,
PROJECT_REV_EXCHANGE_RATE ,
PROJFUNC_REV_RATE_TYPE ,
PROJFUNC_INV_RATE_TYPE ,
PROJFUNC_INV_RATE_DATE ,
PROJFUNC_INV_EXCHANGE_RATE ,
PROJECT_INV_RATE_TYPE ,
PROJECT_INV_RATE_DATE ,
PROJECT_INV_EXCHANGE_RATE ,
PROJFUNC_FCST_RATE_TYPE ,
PROJFUNC_FCST_RATE_DATE ,
PROJFUNC_FCST_EXCHANGE_RATE ,
PRVDR_ACCRUAL_DATE ,
RECVR_ACCRUAL_DATE ,
RATE_DISC_REASON_CODE ,
POSTED_DENOM_BURDENED_COST ,
POSTED_PROJECT_BURDENED_COST ,
POSTED_PROJFUNC_BURDENED_COST ,
POSTED_ACCT_BURDENED_COST ,
ADJUSTMENT_TYPE ,
CAPITAL_EVENT_ID ,
PO_LINE_ID ,
PO_PRICE_TYPE ,
WIP_RESOURCE_ID ,
INVENTORY_ITEM_ID ,
UNIT_OF_MEASURE ,
SRC_SYSTEM_LINKAGE_FUNCTION ,
DOCUMENT_HEADER_ID ,
DOCUMENT_DISTRIBUTION_ID ,
DOCUMENT_LINE_NUMBER ,
DOCUMENT_PAYMENT_ID ,
VENDOR_ID ,
DOCUMENT_TYPE ,
DOCUMENT_DISTRIBUTION_TYPE ,*/
'N' ,--HISTORICAL_FLAG
LOCATION_ID ,
PAY_ELEMENT_TYPE_ID ,
'ACTUAL' ,
/* RATE_SOURCE_CODE , */
'N',
G_INTERFACE_RUN_ID
FROM
PA_EXPENDITURE_ITEMS_ALL ei,
( SELECT source_expenditure_item_id seid ,denom_CURRENCY_CODE,Sum(denom_raw_cost) denom_raw_cost, Sum(denom_raw_cost) acct_raw_cost FROM
pa_pay_dist_lines
WHERE
source_expenditure_item_id IS NOT NULL
AND interface_run_id = G_INTERFACE_RUN_ID
AND person_id = G_PERSON_ID
AND source_expenditure_item_id = X.SOURCE_EXPENDITURE_ITEM_ID
GROUP BY source_expenditure_item_id ,denom_CURRENCY_CODE
) dist
WHERE dist.seid = ei.expenditure_item_id ;
write_log(Log, 'New item inserted ' || l_transfer_id);
UPDATE PA_PAY_DIST_LINES
SET TARGET_EXPENDITURE_ITEM_ID = l_transfer_id
WHERE SOURCE_EXPENDITURE_ITEM_ID = X.SOURCE_EXPENDITURE_ITEM_ID;
SELECT
ec.expenditure_comment
INTO
l_item_comment
FROM
pa_expenditure_comments ec
WHERE
ec.expenditure_item_id = X.SOURCE_EXPENDITURE_ITEM_ID;
write_log(LOG, 'Audit record Inserted');
UPDATE pa_expenditure_items
SET interface_run_id = G_INTERFACE_RUN_ID
,COSTING_METHOD = 'ACTUAL'
,PAYROLL_ACCRUAL_FLAG = 'N'
WHERE expenditure_id = l_transfer_id ;
UPDATE PA_EXPENDITURE_ITEMS_ALL EI2
SET EI2.DENOM_RAW_COST = 0 ,
EI2.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
WHERE EI2.EXPENDITURE_ITEM_ID IN
( SELECT EI.EXPENDITURE_ITEM_ID
FROM PA_EXPENDITURE_ITEMS_ALL EI ,
PA_EXPENDITURES_ALL E,
PA_PAY_AUDIT AUD
WHERE E.INCURRED_BY_PERSON_ID = G_PERSON_ID
AND AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND EI.EXPENDITURE_ID = E.EXPENDITURE_ID
AND Trunc(EXPENDITURE_ITEM_DATE) BETWEEN Trunc(Nvl(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE)) AND Trunc( Nvl(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE))
AND EI.DENOM_RAW_COST IS NULL
AND get_cost_method(EI.expenditure_item_id,EI.expenditure_item_date) = 'ACTUAL'
AND NVL(EI.NET_ZERO_ADJUSTMENT_FLAG,'N') = 'Y'
) ;
/* Update all netzero OTL EI's between pay start and dates with zero payroll cost */
UPDATE PA_EXPENDITURE_ITEMS Ei2 SET
(EI2.DENOM_RAW_COST,Ei2.INTERFACE_RUN_ID,Ei2.
COSTING_METHOD,Ei2.PAYROLL_ACCRUAL_FLAG,Ei2.REQUEST_ID) = (SELECT
NVL((-1*EI.DENOM_RAW_COST),0),
G_INTERFACE_RUN_ID,'ACTUAL',NVL(EI.PAYROLL_ACCRUAL_FLAG,'N'),G_REQUEST_ID FROM
PA_EXPENDITURE_ITEMS Ei,
PA_EXPENDITURES E,
PA_PAY_AUDIT AUD
WHERE E.INCURRED_BY_PERSON_ID = G_PERSON_ID
AND AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND EI2.EXPENDITURE_ID = E.EXPENDITURE_ID
AND EI2.EXPENDITURE_ITEM_DATE BETWEEN Trunc(Nvl(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE))
AND Trunc( NVL(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE))
AND EI.EXPENDITURE_ITEM_ID = nvl(EI2.ADJUSTED_EXPENDITURE_ITEM_ID,EI2.EXPENDITURE_ITEM_ID)
AND PA_PAY_INTERFACE.GET_COST_METHOD1(EI2.EXPENDITURE_ID,EI2.JOB_ID,EI2.ORG_ID,
NVL(EI2.OVERRIDE_to_ORGANIZATION_ID
,E.INCURRED_BY_ORGANIZATION_ID),EI2.EXPENDITURE_ITEM_ID,EI2.EXPENDITURE_ITEM_DATE) = 'ACTUAL'
AND EXISTS (
SELECT 1 FROM PA_EXPENDITURE_ITEMS EI3
WHERE EI3.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND SUBSTR(EI3.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI3.ORIG_TRANSACTION_REFERENCE,':')-1) =
SUBSTR(EI.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI.ORIG_TRANSACTION_REFERENCE,':')-1)
))
WHERE NVL(EI2.NET_ZERO_ADJUSTMENT_FLAG,'N') = 'Y'
AND EI2.SYSTEM_LINKAGE_FUNCTION IN ('ST','OT')
AND EI2.TRANSACTIOn_SOURCE ='ORACLE TIME AND LABOR'
AND EI2.DENOM_RAW_COST IS NULL;
FOR X IN (SELECT EI2.EXPENDITURE_ITEM_ID,EI2.TRANSACTION_SOURCE,EI2.ADJUSTED_EXPENDITURE_ITEM_ID
FROM PA_EXPENDITURE_ITEMS EI2 ,
PA_EXPENDITURES E,
PA_PAY_AUDIT AUD
WHERE E.INCURRED_BY_PERSON_ID = G_PERSON_ID
AND AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND EI2.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND EI2.EXPENDITURE_ID = E.EXPENDITURE_ID
AND EI2.EXPENDITURE_ITEM_DATE BETWEEN Trunc(Nvl(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE)) AND Trunc( Nvl(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE))
AND pa_pay_interface.get_cost_method(EI2.EXPENDITURE_ITEM_ID,EI2.EXPENDITURE_ITEM_DATE) = 'ACTUAL'
AND NVL(EI2.NET_ZERO_ADJUSTMENT_FLAG,'N') = 'N'
AND EI2.SYSTEM_LINKAGE_FUNCTION IN ('ST','OT')
AND NVL(EI2.TRANSACTION_SOURCE,' ') <>'ORACLE TIME AND LABOR'
)
LOOP
--{
UPDATE PA_EXPENDITURE_ITEMS EI2
SET (EI2.DENOM_RAW_COST,EI2.INTERFACE_RUN_ID,
EI2.COSTING_METHOD,
EI2.PAYROLL_ACCRUAL_FLAG,
EI2.REQUEST_ID) = (SELECT
/*(NVL ( ( SELECT (-1*EI1.DENOM_RAW_COST) FROM PA_EXPENDITURE_ITEMS EI1 WHERE
EI1.EXPENDITURE_ITEM_ID=EI2.ADJUSTED_EXPENDITURE_ITEM_ID),0 ) ),G_INTERFACE_RUN_ID,'ACTUAL',
(NVL ( ( SELECT EI1.PAYROLL_ACCRUAL_FLAG FROM
PA_EXPENDITURE_ITEMS EI1 WHERE
EI1.EXPENDITURE_ITEM_ID=EI2.ADJUSTED_EXPENDITURE_ITEM_ID),'N' )),
--'N',
G_REQUEST_ID FROM DUAL)*/
NVL((-1*EI1.DENOM_RAW_COST),0),G_INTERFACE_RUN_ID,'ACTUAL',NVL(EI1.PAYROLL_ACCRUAL_FLAG,'N'),G_REQUEST_ID
FROM PA_EXPENDITURE_ITEMS EI1
WHERE
EI1.EXPENDITURE_ITEM_ID=NVL(EI2.ADJUSTED_EXPENDITURE_ITEM_ID,EI2.EXPENDITURE_ITEM_ID))
WHERE EI2.EXPENDITURE_ITEM_ID IN
(SELECT EXPENDITURE_ITEM_ID
FROM PA_EXPENDITURE_ITEMS Ei3
,PA_EXPENDITURES E,
PA_PAY_AUDIT AUD
WHERE EI3.EXPENDITURE_ITEM_ID IN
(SELECT EXPENDITURE_ITEM_ID FROM PA_EXPENDITURE_ITEMS START WITH EXPENDITURE_ITEM_ID= X.EXPENDITURE_ITEM_ID
CONNECT BY PRIOR TRANSFERRED_FROM_EXP_ITEM_ID=EXPENDITURE_ITEM_ID
UNION
SELECT EXPENDITURE_ITEM_ID FROM PA_EXPENDITURE_ITEMS START WITH EXPENDITURE_ITEM_ID= X.EXPENDITURE_ITEM_ID
CONNECT BY PRIOR TRANSFERRED_FROM_EXP_ITEM_ID=ADJUSTED_EXPENDITURE_ITEM_ID
)
AND E.INCURRED_BY_PERSON_ID = G_PERSON_ID
AND AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
AND EI3.EXPENDITURE_ID = E.EXPENDITURE_ID
AND EI3.EXPENDITURE_ITEM_DATE BETWEEN
Trunc(Nvl(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE))
AND Trunc( NVL(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE))
AND
PA_PAY_INTERFACE.GET_COST_METHOD1(EI3.EXPENDITURE_ID,EI3.JOB_ID,EI3.ORG_ID,
NVL(EI3.OVERRIDE_to_ORGANIZATION_ID
,E.INCURRED_BY_ORGANIZATION_ID),EI3.EXPENDITURE_ITEM_ID,EI3.EXPENDITURE_ITEM_DATE)
= 'ACTUAL'
AND Ei3.INTERFACE_RUN_ID IS NULL
AND NVL(EI3.NET_ZERO_ADJUSTMENT_FLAG,'N') = 'Y'
AND EI3.SYSTEM_LINKAGE_FUNCTION IN ('ST','OT')
AND NVL(EI3.TRANSACTION_SOURCE,' ') <>'ORACLE TIME AND LABOR'
AND EI3.DENOM_RAW_COST IS NULL
);
write_log (DEBUG, 'No of adjustment ei updated' || SQL%rowcount);
DELETE FROM pa_pay_dist_lines
WHERE interface_run_id = G_INTERFACE_RUN_ID AND
person_id = G_PERSON_ID ;
SELECT REJECTION_CODE
INTO
l_rejection_code
FROM pa_pay_rejection_priority
WHERE PRIORITY = (
SELECT
MIN(pprp.priority)
FROM
PA_PAY_REJECTIONS_ALL ppr ,
pa_pay_rejection_priority pprp
WHERE
ppr.interface_run_id = p_interface_run_id AND
ppr.person_id = p_person_id AND
( (p_int_ext_indicator = 'INT' AND (ppr.pay_element_type_id = p_pay_element_type_id )
)OR
( p_int_ext_indicator = 'EXT' AND (ppr.pay_element_type_code = p_pay_element_type_code)
)
) AND
ppr.rejection_code = pprp.rejection_code
GROUP BY
ppr.pay_element_type_id);
SELECT
PPR.REJECTION_CODE,
MAX(PPRP.PRIORITY) PRTY
FROM
PA_PAY_REJECTIONS_ALL PPR ,
PA_PAY_REJECTION_PRIORITY PPRP
WHERE
PPR.INTERFACE_RUN_ID = P_INTERFACE_RUN_ID AND
PPR.PERSON_ID = P_PERSON_ID AND
( (p_int_ext_indicator = 'INT' AND (ppr.pay_element_type_id = p_pay_element_type_id )
)OR
(p_int_ext_indicator = 'EXT' AND (ppr.pay_element_type_code = p_pay_element_type_code)
)
) AND
PPR.REJECTION_CODE = PPRP.REJECTION_CODE
GROUP BY
PPR.REJECTION_CODE
ORDER BY
PRTY ASC
)
LOOP
L_COUNTER := L_COUNTER +1;
SELECT INT_EXT_INDICATOR
INTO
l_int_ext_flag
FROM pa_pay_audit_all
WHERE INTERFACE_RUN_ID = G_INTERFACE_RUN_ID ;
SELECT
PAYROLL_ACTION_ID ,
ASSIGNMENT_ACTION_ID ,
SRC.PAY_ELEMENT_TYPE_ID ,
'Y' STATUS
FROM
PA_PAY_AUDIT AUD ,
PA_PAY_INTERFACE_TEMP TEMP ,
PA_PAY_SOURCE_AMOUNTS SRC
WHERE
AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
AUD.INTERFACE_RUN_ID = SRC.INTERFACE_RUN_ID AND
TEMP.PERSON_ID = SRC.PERSON_ID AND
SRC.PERSON_ID = G_PERSON_ID AND
TEMP.ASSIGNMENT_ID = SRC.ASSIGNMENT_ID AND
TRUNC(NVL(TEMP.SOURCE_START_DATE,TEMP.TIME_PERIOD_START_DATE)) = TRUNC(NVL(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE)) AND
TRUNC(NVL(TEMP.SOURCE_END_DATE,TEMP.TIME_PERIOD_END_DATE)) = TRUNC(NVL(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE))/* bug 12690224*/
UNION
SELECT
PAYROLL_ACTION_ID ,
ASSIGNMENT_ACTION_ID ,
TEMP.ELEMENT_TYPE_ID PAY_ELEMENT_TYPE_ID ,
'Z' STATUS
FROM
PA_PAY_AUDIT AUD ,
PA_PAY_INTERFACE_TEMP TEMP
WHERE
AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
TRUNC(NVL(TEMP.SOURCE_START_DATE,TEMP.TIME_PERIOD_START_DATE)) = TRUNC(NVL(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE)) AND
TRUNC(NVL(TEMP.SOURCE_END_DATE,TEMP.TIME_PERIOD_END_DATE)) = TRUNC(NVL(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE)) AND /* bug 12690224*/
TEMP.PERSON_ID = G_PERSON_ID AND
TEMP.ASSIGNMENT_ID = G_ASSIGNMENT_ID AND
NOT EXISTS
(
SELECT
'Y'
FROM
PA_PAY_SOURCE_AMOUNTS PPSA
WHERE
PPSA.PERSON_ID = G_PERSON_ID AND
PPSA.ASSIGNMENT_ID = G_ASSIGNMENT_ID AND
PPSA.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
PPSA.PAY_ELEMENT_TYPE_ID = TEMP.ELEMENT_TYPE_ID
))
LOOP
pay_core_utils.update_prj_flag (X.pay_element_type_id , X.assignment_action_id, X.STATUS, l_status ) ;
Update PA_PAY_EXT_INTERFACE_DETAILS
set TRANSFER_STATUS_FLAG = 'A'
where INTERFACE_LINE_ID in
(
SELECT
EXT.INTERFACE_LINE_ID
FROM
PA_PAY_AUDIT AUD ,
PA_PAY_EXT_INTERAFACE_V EXT ,
PA_PAY_SOURCE_AMOUNTS SRC
WHERE
AUD.INT_EXT_INDICATOR = 'EXT' AND
AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
AUD.INTERFACE_RUN_ID = SRC.INTERFACE_RUN_ID AND
SRC.PERSON_ID = G_PERSON_ID AND
SRC.ASSIGNMENT_ID = G_ASSIGNMENT_ID AND
EXT.PERSON_ID = SRC.PERSON_ID AND
EXT.ASSIGNMENT_ID = SRC.ASSIGNMENT_ID AND
Trunc(EXT.TIME_PERIOD_START_DATE) = Trunc(AUD.PAY_PERIOD_START_DATE )AND
Trunc(EXT.TIME_PERIOD_END_DATE) = Trunc(AUD.PAY_PERIOD_END_DATE) AND
EXT.PAY_ELEMENT_TYPE_CODE = SRC.PAY_ELEMENT_TYPE_CODE
);
UPDATE PA_PAY_EXT_INTERFACE_DETAILS EXTD
SET TRANSFER_STATUS_FLAG = 'X'
WHERE
INTERFACE_LINE_ID IN
(
SELECT
EXT.INTERFACE_LINE_ID
FROM
PA_PAY_EXT_INTERAFACE_V EXT,
PA_PAY_AUDIT AUD
WHERE
AUD.INT_EXT_INDICATOR = 'EXT' AND
AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
Trunc(EXT.TIME_PERIOD_START_DATE) = Trunc(AUD.PAY_PERIOD_START_DATE) AND
Trunc(EXT.TIME_PERIOD_END_DATE) = Trunc(AUD.PAY_PERIOD_END_DATE) AND
NOT EXISTS
(
SELECT
PAY_ELEMENT_TYPE_CODE
FROM
PA_PAY_SOURCE_AMOUNTS SRC
WHERE
SRC.INTERFACE_RUN_ID = AUD.INTERFACE_RUN_ID AND
SRC.PERSON_ID = G_PERSON_ID AND
SRC.ASSIGNMENT_ID = G_ASSIGNMENT_ID AND
SRC.PAY_ELEMENT_TYPE_CODE = EXT.PAY_ELEMENT_TYPE_CODE
));
write_log (LOG,'Insert Pay Proj record ' );
INSERT
INTO
PA_PAY_PROJ_STATUS_ALL
(
INTERFACE_RUN_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
PAYROLL_STATUS_FLAG ,
PROJECT_ROLLBACK_STATUS_IND ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
ASSIGNMENT_ACTION_ID
)
SELECT DISTINCT
G_INTERFACE_RUN_ID,
G_PERSON_ID ,
G_ASSIGNMENT_ID ,
NULL ,
NULL ,
G_RUN_DATE ,
G_USER_ID ,
G_RUN_DATE ,
G_USER_ID ,
G_LOGIN_ID,
G_REQUEST_ID ,
ASSIGNMENT_ACTION_ID
FROM
PA_PAY_INTERFACE_TEMP TEMP ,
PA_PAY_AUDIT AUD
WHERE
AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
TEMP.PAYROLL_ACTION_ID = AUD.BATCH_ID AND
TEMP.PERSON_ID = G_PERSON_ID AND
TEMP.ASSIGNMENT_ID = G_ASSIGNMENT_ID AND
TEMP.TIME_PERIOD_ID = AUD.TIME_PERIOD_ID ;
p_program_update_date pa_cost_distribution_lines.program_update_date%TYPE;
SELECT ITEMS.adjusted_expenditure_item_id,
ITEMS.transferred_from_exp_item_id,
TRN.gl_accounted_flag,
TRN.transaction_source
,ITEMS.expenditure_item_date
,ITEMS.org_id
,NVL(ITEMS.recvr_org_id,ITEMS.ORG_ID)
,ITEMS.system_linkage_function
,ITEMS.expenditure_id
INTO p_parent_adjusted_id,
p_parent_transferred_id,
p_gl_accounted_flag,
p_transaction_source
,l_ei_date
,l_org_id
,l_recvr_org_id
,l_sys_link_function
,l_exp_id
FROM pa_expenditure_items_All ITEMS,
pa_transaction_sources TRN
WHERE ITEMS.transaction_source = TRN.transaction_source (+)
AND ITEMS.expenditure_item_id = X_exp_item_id;
SELECT imp1.set_of_books_id, imp2.set_of_books_id
INTO l_sob_id, l_recvr_sob_id
FROM pa_implementations_all imp1, pa_implementations_all imp2
WHERE imp1.org_id = l_org_id
AND imp2.org_id = l_recvr_org_id;
SELECT max(cdl.line_num)
INTO actual_cdl_line_num
FROM pa_cost_distribution_lines cdl
WHERE cdl.expenditure_item_id = X_exp_item_id and cdl.line_type = 'R';
SELECT amount
, dr_code_combination_id
, cr_code_combination_id
, transfer_status_code
, quantity
, billable_flag
, request_id
, program_application_id
, program_id
, program_update_date
, pa_date
, gl_date
, transferred_date
, transfer_rejection_reason
, line_type
, ind_compiled_set_id
, nvl(burdened_cost,0) + nvl(projfunc_burdened_change,0)
, line_num_reversed
, reversed_flag
, system_reference1
, system_reference2
, system_reference3
, denom_currency_code
, denom_raw_cost
, NVL(denom_burdened_cost,0) + nvl(denom_burdened_change,0)
, acct_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, acct_raw_cost
, NVL(acct_burdened_cost,0) + nvl(acct_burdened_change,0)
, project_currency_code
, project_rate_date
, project_rate_type
, project_exchange_rate
, project_id
, task_id,
recvr_gl_date
, Projfunc_currency_code
, Projfunc_cost_rate_date
, Projfunc_cost_rate_type
, Projfunc_cost_exchange_rate
, Project_raw_cost
, NVL(Project_burdened_cost,0) + nvl(project_burdened_change,0)
, Work_type_id
, system_reference4
, system_reference5
, decode(si_assets_addition_flag, 'R','T', 'O', 'T', 'Y', 'T', 'N', 'T',si_assets_addition_flag )
, rate_source_code
, costing_method
INTO p_amount,
p_dr_ccid,
p_cr_ccid,
p_transfer_status_code,
p_quantity,
p_billable_flag,
p_request_id,
p_program_application_id,
p_program_id,
p_program_update_date,
p_pa_date,
p_gl_date,
p_transferred_date,
p_transfer_rejection_reason,
p_line_type,
p_ind_complied_set_id,
p_burdened_cost,
p_line_num_reversed,
p_reversed_flag,
p_cdlsr1,
p_cdlsr2,
p_cdlsr3,
p_denom_currency_code,
p_denom_raw_cost,
p_denom_burdened_cost,
p_acct_currency_code,
p_acct_rate_date,
p_acct_rate_type,
p_acct_exchange_rate,
p_acct_raw_cost,
p_acct_burdened_cost,
p_project_currency_code,
p_project_rate_date,
p_project_rate_type,
p_project_exchange_rate,
p_project_id,
p_task_id
, p_recvr_gl_date
, p_Projfunc_currency_code
, p_Projfunc_cost_rate_date
, p_Projfunc_cost_rate_type
, p_Projfunc_cost_exchange_rate
, p_Project_raw_cost
, p_Project_burdened_cost
, p_Work_type_id
, p_cdlsr4
, p_cdlsr5
, l_si_assets_addition_flag
, l_rate_source_code
, l_costing_method
FROM pa_cost_distribution_lines_All -- 12i MOAC changes
WHERE expenditure_item_id = X_exp_item_id
AND line_num = actual_cdl_line_num; -- bug2378505
, x_program_update_date => p_program_update_date
, X_pa_date => l_pa_date /* bug 2361495 p_pa_date */
, X_recvr_pa_date => l_recvr_pa_date /** bug 2361495 p_recvr_pa_date CBGA **/
, X_gl_date => l_gl_date /* bug 2661921 p_gl_date */
, X_transferred_date => NULL /* bug 2361495 p_transferred_date */
, X_transfer_rejection_reason => NULL /* bug 2361495 p_transfer_rejection_reason */
, X_line_type => p_line_type
, X_ind_compiled_set_id => p_ind_complied_set_id
, X_burdened_cost => -p_burdened_cost
, X_line_num_reversed => p_line_num_reversed
, X_reverse_flag => p_reversed_flag
, X_user => X_user
, X_err_code => p_err_code
, X_err_stage => p_err_stage
, X_err_stack => p_err_stack
, X_project_id => p_project_id
, X_task_id => p_task_id
, X_cdlsr1 => p_cdlsr1
, X_cdlsr2 => p_cdlsr2
, X_cdlsr3 => p_cdlsr3
, X_denom_currency_code => p_denom_currency_code
, X_denom_raw_cost => -p_denom_raw_cost
, X_denom_burden_cost => -p_denom_burdened_cost
, X_acct_currency_code => p_acct_currency_code
, X_acct_rate_date => p_acct_rate_date
, X_acct_rate_type => p_acct_rate_type
, X_acct_exchange_rate => p_acct_exchange_rate
, X_acct_raw_cost => -p_acct_raw_cost
, X_acct_burdened_cost => -p_acct_burdened_cost
, X_project_currency_code => p_project_currency_code
, X_project_rate_date => p_project_rate_date
, X_project_rate_type => p_project_rate_type
, X_project_exchange_rate => p_project_exchange_rate
, P_PaPeriodName => P_Pa_Period_Name
, P_RecvrPaPeriodName => P_Recvr_Pa_Period_Name
, P_GlPeriodName => P_Gl_Period_Name
, P_RecvrGlDate => l_recvr_gl_date /* bug 2661921 P_Recvr_Gl_Date */
, P_RecvrGlPeriodName => P_Recvr_Gl_Period_Name
, P_Projfunc_currency_code => P_Projfunc_currency_code
, P_Projfunc_cost_rate_date => P_Projfunc_cost_rate_date
, P_Projfunc_cost_rate_type => P_Projfunc_cost_rate_type
, P_Projfunc_cost_exchange_rate => P_Projfunc_cost_exchange_rate
, P_Project_Raw_Cost => -P_Project_Raw_Cost --Bug 3315099
, P_Project_Burdened_Cost => -P_Project_Burdened_Cost --Bug 3315099
, P_Work_Type_Id => P_Work_Type_Id
, p_cdlsr4 => p_cdlsr4
, p_si_assets_addition_flag => l_si_assets_addition_flag
, p_cdlsr5 => p_cdlsr5
, P_Parent_Line_Num => actual_cdl_line_num
/* 12.2 payroll intg .. start */
, p_rate_source_code => l_rate_source_code
, p_costing_method => l_costing_method);
UPDATE pa_expenditure_items
SET cost_distributed_flag = 'Y'
WHERE expenditure_item_id = X_backout_id;
SELECT DEFAULT_RATE_TYPE,
DEFAULT_RATE_DATE_CODE
INTO
X_ACCT_RATE_TYPE,
X_ACCT_RATE_DATE_CODE
FROM pa_implementations ;
SELECT DISTINCT
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID,
PAY_ELEMENT_TYPE_CODE
FROM
PA_PAY_DIST_LINES
WHERE
INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
PERSON_ID = G_PERSON_ID AND
SOURCE_EXPENDITURE_ITEM_ID = P_SOURCE_EXPENDITURE_ITEM_ID
)
LOOP
reject_pay_element (X.PAY_SOURCE_ID, X.PAY_ELEMENT_TYPE_ID,X.PAY_ELEMENT_TYPE_CODE , 'RC 80' ) ;
SELECT DISTINCT
PAY_SOURCE_ID ,
PAY_ELEMENT_TYPE_ID,
PAY_ELEMENT_TYPE_CODE
FROM
PA_PAY_DIST_LINES
WHERE
INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
PERSON_ID = G_PERSON_ID AND
SOURCE_EXPENDITURE_ITEM_ID = P_SOURCE_EXPENDITURE_ITEM_ID
)
LOOP
reject_pay_element (X.PAY_SOURCE_ID, X.PAY_ELEMENT_TYPE_ID,X.PAY_ELEMENT_TYPE_CODE , 'RC 80' ) ;