Yes, here’s the rest of the stored procedure. I do think however that it’s the snippet with a query in
‘LoketNlRest.PayrollRunResults.PayrollRunPayslipTypeComponentsByPayrollRunId(runs.id) runSpecifications;’ which causes the problem.
declare
l_output varchar2;
in_employerId guid;
batchDate varchar2;
periodNumber varchar2;
periodYear varchar2;
begin
in_employerId := cast(cloud_http.get_request_query_parameter_value('employerId') as guid);
batchDate := cast(cloud_http.get_request_query_parameter_value('batchDate') as varchar2);
create or replace table payrollRuns@inmemorystorage
as
select runs.payrollRunStatus_key
, runs.payrollRunStatus_value
, runs.approvalTime
, runs.sequenceNumber
, runSpecifications.payrollComponent_key
, runSpecifications.payrollComponent_value
, runSpecifications.payrollComponent_category_key
, runSpecifications.payrollComponent_category_value
, runSpecifications.payrollComponent_costsEmployer_value
, runSpecifications.value
, runSpecifications.valueSpecialTariff
, runSpecifications.payrollPeriod_year
, runSpecifications.payrollPeriod_periodNumber
, runSpecifications.employmentId
from LoketNlRest.PayrollAdministration.PayrollAdministrationsByEmployerId('XXX') payrollAdministration
join LoketNlRest.PayrollRun.PayrollRunsByPayrollAdministrationId(payrollAdministration.id) runs
on runs.payrollRunStatus_key = 9
and TRUNC(runs.approvalTime) = TO_DATE('11/01/2018','DD/MM/YYYY')
join LoketNlRest.PayrollRunResults.PayrollRunPayslipTypeComponentsByPayrollRunId(runs.id) runSpecifications;
on runs.payrollPeriod_year = runspecifications.payrollPeriod_year
;
create or replace table payrollPeriodData@inmemorystorage
as
select employmentId
, payrollPeriodData.distributionUnit_code
, payrollPeriodData.distributionUnit_description
, payrollPeriodData.payrollPeriod_year
, payrollPeriodData.payrollPeriod_periodNumber
from ( select distinct employmentId
from payrollRuns@inMemoryStorage) employmentIds
join LoketNlRest.PayrollPeriodData.PayrollPeriodDataByEmploymentId(employmentIds.employmentId
) payrollPeriodData
;
select pr.payrollRunStatus_key
, pr.payrollRunStatus_value
, pr.approvalTime
, pr.sequenceNumber
, pr.payrollComponent_key
, pr.payrollComponent_value
, pr.payrollComponent_category_key
, pr.payrollComponent_category_value
, pr.payrollComponent_costsEmployer_value
, pr.value
, pr.valueSpecialTariff
, pr.payrollPeriod_year
, pr.payrollPeriod_periodNumber
, pr.employmentId
, pd.distributionUnit_code
, pd.distributionUnit_description
into l_output
from payrollRuns@inMemoryStorage pr
join payrollPeriodData@inMemoryStorage pd
on pr.employmentId = pd.employmentId
and pr.payrollPeriod_periodnumber = pd.payrollPeriod_periodnumber
and pr.payrollPeriod_year = pd.payrollPeriod_year
for json auto
;
cloud_http.set_response_content_type('application/json');
cloud_http.set_response_body_text(l_output);
end;