Hello,
The below code is working in SQLdev, and will read in to BI Publisher. However, when I go to view data I am getting a 'SQL or ORA exceptions caused due to invalid sql database package state or some database exceptions' error message. My huynch is it has something to do with the 'extract' statement, but my research has yielded nothing thus far. Please feel free to chime in!
SELECT VEAS.PERSON_CODE,
VEAS.NAME,
veas.termination_date,
'11'||SUBSTR(DDS.DISTRIBUTION_CODE, 31, 9) ORG,
extract(MONTH from AAL.START_DATE) || '-' || extract(YEAR from AAL.START_DATE) MonthYear,
Sum(aald.amount)
FROM P2K_HR_VASSIGNMENTS VEAS
INNER JOIN P2K_CM_DISTRIBUTIONS DDS ON VEAS.EASD_ID = DDS.EASD_ID
inner join P2K_AT_LEAVE_LINES AAL on AAL.EEM_ID = VEAS.EEM_ID
INNER JOIN P2K_AT_LEAVE_LINE_DETAILS AALD ON AAL.ID = AALD.AAL_ID
INNER JOIN P2K_AT_LEAVE_POLICY_TYPES ALPT ON ALPT.ID = AAL.ALPT_ID
INNER JOIN P2K_AT_ATTENDANCE_COMPONENTS AAC ON AAC.ID = AALD.AAC_ID
WHERE VEAS.PRIME_ASSIGNMENT = '1'
AND VEAS.UNIT_CODE NOT IN ('OPS', 'ELECTED')
and ALPT.LEAVE_TYPE_CODE = 'SICK POOL JOINT'
AND VEAS.DES_ID IN (SELECT DES.ID FROM P2K_CM_EMPLOYMENT_STATUSES DES WHERE DES.STATUS_TYPE IN ('L', 'A'))
AND AAL.START_DATE BETWEEN VEAS.EFFECTIVE AND VEAS.EXPIRY
AND AAL.START_DATE BETWEEN TO_DATE(:FY_START,'MM/DD/YYYY') AND TO_DATE(:FY_END,'MM/DD/YYYY')
AND AAL.ACCRUAL_STATUS ='02'
AND AAC.AC_CODE = 'A300'
AND VEAS.PERSON_CODE = '1001972'
GROUP BY VEAS.PERSON_CODE,
VEAS.NAME,
veas.termination_date,
'11'||SUBSTR(DDS.DISTRIBUTION_CODE, 31, 9),
extract(MONTH from AAL.START_DATE) || '-' || extract(YEAR from AAL.START_DATE)
ORDER BY 2, 3, 5
