i have a stored procedure in SQL Server like this:
DECLARE @EFEKTIF_DATE VARCHAR(15);
SET @EFEKTIF_DATE = (SELECT REPLACE(CONVERT(NVARCHAR,CAST(GETDATE() AS DATE),106),' ','-') EFEKTIF_DATE)
DECLARE @SQL_P1 VARCHAR(MAX) =
'SELECT papf.employee_number ,
papf.original_date_of_hire ,
paaf.effective_start_date ,
paaf.person_id ,
ppt.user_person_type ,
ppt.person_type_id ,
paaf.assignment_id ,
paaf.organization_id ,
papf.last_name ,
papf.sex code_jk ,
papf.marital_status ,
''' @EFEKTIF_DATE ''' efektif_date ,
CASE
WHEN Upper(Nvl(amdtl.user_status , sttl.user_status)) LIKE ''ACTIVE%'' and ppt.user_person_type<>''Ex-employee'' THEN ''ACTIVE''
ELSE ''RESIGN''
END user_status ,
Decode(paaf.assignment_type , ''E'' , (select meaning from hr_lookups where lookup_type = ''EMP_CAT'' and lookup_code = paaf.employment_category),
''C'', (select meaning from hr_lookups where lookup_type = ''CWK_ASG_CATEGORY'' and lookup_code = paaf.employment_category)) assignment_category ,
(select meaning from hr_lookups where lookup_type = ''EMPLOYEE_CATG''
and lookup_code = paaf.employee_category) employee_category ,
papf.date_of_birth tgl_lahir ,
papf.town_of_birth tempat_lahir ,
(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = ''BI_OM_PANGKAT''
AND pgd.segment2 = lookup_code) pangkat ,
(SELECT NAME
FROM per_jobs
WHERE job_id = paaf.job_id) jabatan ,
id_hridcommutil_pk.get_lookup_meaning(''BI_PA_PERSONAL_BRANDING'' ,
(SELECT MAX(pac.segment1)
FROM per_analysis_criteria PAC ,
per_person_analyses PPA
WHERE paaf.person_id = ppa.person_id( )
AND pac.analysis_criteria_id( ) = ppa.analysis_criteria_id
AND
(SELECT id_flex_num
FROM fnd_id_flex_structures_vl
WHERE id_flex_structure_code = ''BI_PA_PERSONAL_BRANDING'') = ppa.id_flex_num
AND paaf.effective_end_date BETWEEN ppa.date_from AND nvl(ppa.date_to , to_date(''31-DEC-4712'',''DD-MON-YYYY'')))) personal_branding ,
nvl(to_date(pac_v.segment4 , ''RRRR/MM/DD HH24:MI:SS''), papf.original_date_of_hire) tanggal_masuk_bi ,
NULLIF (floor(months_between( LEAST( case when ppt.person_type_id=1130 and pac_v.segment1 is null then term.end_date 1 when to_date(pac_v.segment1,''RRRR/MM/DD HH24:MI:SS'')<nvl(to_date(pac_v.segment4,''RRRR/MM/DD HH24:MI:SS''),papf.original_date_of_hire) then term.end_date 1 else to_date(nvl(pac_v.segment1,''4712/12/31 00:00:00''),''RRRR/MM/DD HH24:MI:SS'') end , ''' @EFEKTIF_DATE ''' ) 1, nvl(to_date(pac_v.segment4 , ''RRRR/MM/DD HH24:MI:SS''), papf.original_date_of_hire)) / 12)
|| '' Tahun ''
|| round(mod(months_between( LEAST( case when ppt.person_type_id=1130 and pac_v.segment1 is null then term.end_date 1 when to_date(pac_v.segment1,''RRRR/MM/DD HH24:MI:SS'')<nvl(to_date(pac_v.segment4,''RRRR/MM/DD HH24:MI:SS''),papf.original_date_of_hire) then term.end_date 1 else to_date(nvl(pac_v.segment1,''4712/12/31 00:00:00''),''RRRR/MM/DD HH24:MI:SS'') end , ''' @EFEKTIF_DATE ''' ) 1 , nvl(to_date(pac_v.segment4 , ''RRRR/MM/DD HH24:MI:SS''), papf.original_date_of_hire)) , 12))
|| '' Bulan'' , '' Tahun Bulan'') masa_dinas_bi ,
to_date(pac_v.segment7 , ''RRRR/MM/DD HH24:MI:SS'') tanggal_satker ,
NULLIF (floor(months_between( LEAST( case when ppt.person_type_id=1130 and pac_v.segment1 is null then term.end_date 1 when to_date(pac_v.segment1,''RRRR/MM/DD HH24:MI:SS'')<nvl(to_date(pac_v.segment4,''RRRR/MM/DD HH24:MI:SS''),papf.original_date_of_hire) then term.end_date 1 else to_date(nvl(pac_v.segment1,''4712/12/31 00:00:00''),''RRRR/MM/DD HH24:MI:SS'') end , ''' @EFEKTIF_DATE ''' ) 1 , to_date(pac_v.segment7 , ''RRRR/MM/DD HH24:MI:SS'')) / 12)
|| '' Tahun ''
|| round(mod(months_between( LEAST( case when ppt.person_type_id=1130 and pac_v.segment1 is null then term.end_date 1 when to_date(pac_v.segment1,''RRRR/MM/DD HH24:MI:SS'')<nvl(to_date(pac_v.segment4,''RRRR/MM/DD HH24:MI:SS''),papf.original_date_of_hire) then term.end_date 1 else to_date(nvl(pac_v.segment1,''4712/12/31 00:00:00''),''RRRR/MM/DD HH24:MI:SS'') end , ''' @EFEKTIF_DATE ''' ) 1 , to_date(pac_v.segment7 , ''RRRR/MM/DD HH24:MI:SS'')) , 12))
|| '' Bulan'' , '' Tahun Bulan'') masa_dinas_satker ,
to_date(pac_v.segment8 , ''RRRR/MM/DD HH24:MI:SS'') tanggal_jabatan ,
NULLIF (floor(months_between( LEAST( case when ppt.person_type_id=1130 and pac_v.segment1 is null then term.end_date 1 when to_date(pac_v.segment1,''RRRR/MM/DD HH24:MI:SS'')<nvl(to_date(pac_v.segment4,''RRRR/MM/DD HH24:MI:SS''),papf.original_date_of_hire) then term.end_date 1 else to_date(nvl(pac_v.segment1,''4712/12/31 00:00:00''),''RRRR/MM/DD HH24:MI:SS'') end , ''' @EFEKTIF_DATE ''' ) 1 , to_date(pac_v.segment8 , ''RRRR/MM/DD HH24:MI:SS'')) / 12)
|| '' Tahun ''
|| round(mod(months_between( LEAST( case when ppt.person_type_id=1130 and pac_v.segment1 is null then term.end_date 1 when to_date(pac_v.segment1,''RRRR/MM/DD HH24:MI:SS'')<nvl(to_date(pac_v.segment4,''RRRR/MM/DD HH24:MI:SS''),papf.original_date_of_hire) then term.end_date 1 else to_date(nvl(pac_v.segment1,''4712/12/31 00:00:00''),''RRRR/MM/DD HH24:MI:SS'') end , ''' @EFEKTIF_DATE ''' ) 1 , to_date(pac_v.segment8 , ''RRRR/MM/DD HH24:MI:SS'')) , 12))
|| '' Bulan'' , '' Tahun Bulan'') masa_dinas_jabatan ,
to_date(pac_v.segment6 , ''RRRR/MM/DD HH24:MI:SS'') tanggal_pangkat ,
NULLIF (floor(months_between( LEAST( case when ppt.person_type_id=1130 and pac_v.segment1 is null then term.end_date 1 when to_date(pac_v.segment1,''RRRR/MM/DD HH24:MI:SS'')<nvl(to_date(pac_v.segment4,''RRRR/MM/DD HH24:MI:SS''),papf.original_date_of_hire) then term.end_date 1 else to_date(nvl(pac_v.segment1,''4712/12/31 00:00:00''),''RRRR/MM/DD HH24:MI:SS'') end , ''' @EFEKTIF_DATE ''' ) 1 , to_date(pac_v.segment6 , ''RRRR/MM/DD HH24:MI:SS'')) / 12)
|| '' Tahun ''
|| round(mod(months_between( LEAST( case when ppt.person_type_id=1130 and pac_v.segment1 is null then term.end_date 1 when to_date(pac_v.segment1,''RRRR/MM/DD HH24:MI:SS'')<nvl(to_date(pac_v.segment4,''RRRR/MM/DD HH24:MI:SS''),papf.original_date_of_hire) then term.end_date 1 else to_date(nvl(pac_v.segment1,''4712/12/31 00:00:00''),''RRRR/MM/DD HH24:MI:SS'') end , ''' @EFEKTIF_DATE ''' ) 1 , to_date(pac_v.segment6 , ''RRRR/MM/DD HH24:MI:SS'')) , 12))
|| '' Bulan'' , '' Tahun Bulan'') masa_dinas_pangkat ,
nvl(floor(months_between(to_date(pac_v.segment1, ''RRRR/MM/DD HH24:MI:SS'') 1 , LEAST( case when ppt.person_type_id = 1130 and pac_v.segment1 is null then term.end_date 1 else to_date(pac_v.segment1, ''RRRR/MM/DD HH24:MI:SS'') end , ''' @EFEKTIF_DATE ''' ) ) / 12) , 0)
|| '' Tahun ''
|| nvl(round(mod(months_between(to_date(pac_v.segment1, ''RRRR/MM/DD HH24:MI:SS'') 1 , LEAST( case when ppt.person_type_id = 1130 and pac_v.segment1 is null then term.end_date 1 else to_date(pac_v.segment1, ''RRRR/MM/DD HH24:MI:SS'') end , ''' @EFEKTIF_DATE ''' ) ) , 12)) , 0)
|| '' Bulan'' sisa_masa_dinas ,
to_date(pac_v.segment3 , ''RRRR/MM/DD HH24:MI:SS'') tanggal_mpp ,'
DECLARE @SQL_P2 VARCHAR(MAX)=
'
(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_code = papf.attribute4
AND lookup_type = ''BI_PA_JALUR_REKRUTMEN'') jalur_rekrutmen ,
haou.attribute2 rubrik ,
id_hridcommutil_pk.get_organization_name(haou.attribute8) satker ,
id_hridcommutil_pk.get_organization_name(haou.attribute9) grup ,
id_hridcommutil_pk.get_organization_name(haou.attribute10) divisi ,
id_hridcommutil_pk.get_organization_name(haou.attribute11) tim ,
id_hridcommutil_pk.get_organization_name(haou.attribute12) unit ,
(SELECT location_code
FROM hr_locations
WHERE location_id = paaf.location_id) lokasi ,
CASE
WHEN papf.sex = ''F'' THEN ''Perempuan''
WHEN papf.sex = ''M'' THEN ''Laki-laki''
ELSE '' ''
END jenis_kelamin ,
(SELECT hl.meaning
FROM hr_lookups hl
WHERE papf.per_information2 = hl.lookup_code
AND hl.enabled_flag = ''Y''
AND hl.lookup_type = ''HR_ID_RELIGION''
AND rownum = 1) agama ,
hl2.meaning status_pernikahan ,
(SELECT pac.segment3
FROM per_analysis_criteria pac ,
per_person_analyses ppa
WHERE paaf.person_id = ppa.person_id( )
AND pac.analysis_criteria_id( ) = ppa.analysis_criteria_id
AND(SELECT id_flex_num
FROM fnd_id_flex_structures_vl
WHERE id_flex_structure_code = ''ID_EE_NPWP_TAX_DETAILS'') = ppa.id_flex_num
AND paaf.effective_end_date BETWEEN ppa.date_from AND nvl(ppa.date_to , to_date(''31-DEC-4712'' , ''DD-MON-YYYY''))) tax_material_status ,
(SELECT hl3.meaning
FROM hr_lookups hl3
WHERE papf.per_information3 = hl3.lookup_code
AND hl3.enabled_flag = ''Y''
AND hl3.lookup_type = ''HR_ID_ETHNIC_ORIGIN''
AND rownum = 1) suku_bangsa ,
(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = ''BI_OM_KOTA''
AND attribute6 = ''Y''
AND papf.attribute3 = lookup_code
AND rownum = 1) homebase ,
(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = ''BI_OM_PROVINSI''
AND attribute1 = ''ID''
AND papf.per_information10 = lookup_code) putra_daerah ,
papf.email_address email_kantor ,
case when ppt.person_type_id = 1130 and pac_v.segment1 is null then term.end_date 1
when to_date(pac_v.segment1 , ''RRRR/MM/DD HH24:MI:SS'') < nvl(to_date(pac_v.segment4 , ''RRRR/MM/DD HH24:MI:SS''), papf.original_date_of_hire) then term.end_date 1
else to_date(pac_v.segment1 , ''RRRR/MM/DD HH24:MI:SS'') end tanggal_pensiun ,
case when ppt.person_type_id = 1130 then term.end_date 1 else null end tanggal_berhenti ,
pqt.NAME jenjangpendidikan ,
round(nk_rata.performance_rating , 2) nk_rata_rata ,
nk_.nk5 nk_4_thn ,
nk_.nk4 nk_3_thn ,
nk_.nk3 nk_2_thn ,
nk_.nk2 nk_1_thn ,
nk_.nk1 nk_terakhir ,
nk_data.NK NK_ALL,
CASE
WHEN sanksi.cek = ''Y'' THEN ''YES''
ELSE ''NO''
END sanksi ,
CASE
WHEN(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_code = paaf.ass_attribute2
AND lookup_type = ''BI_PA_JENIS_PERUBAHAN'') = ''Promosi Penghargaan'' THEN ''YES''
ELSE ''NO''
END promosi ,
CASE
WHEN(SELECT pac.segment2
FROM per_analysis_criteria pac ,
per_person_analyses ppa
WHERE paaf.person_id = ppa.person_id( )
AND pac.analysis_criteria_id( ) = ppa.analysis_criteria_id
AND(SELECT id_flex_num
FROM fnd_id_flex_structures_vl
WHERE id_flex_structure_code = ''BI_PA_IKATAN_DINAS'') = ppa.id_flex_num
AND paaf.effective_end_date BETWEEN ppa.date_from AND nvl(ppa.date_to , to_date(''31-DEC-4712'' , ''DD-MON-YYYY''))
AND rownum = 1) IS NOT NULL THEN ''YES''
ELSE ''NO''
END status_ikatan_dinas ,
CASE
WHEN(SELECT ''Y''
FROM per_person_analyses pac ,
fnd_id_flex_structures_vl f
WHERE pac.id_flex_num = f.id_flex_num
AND pac.person_id = papf.person_id
AND f.id_flex_structure_code = ''BI_PA_MPP''
AND ''' @EFEKTIF_DATE ''' BETWEEN pac.date_from AND nvl(pac.date_to , ''31-DEC-4712'')
AND rownum = 1) IS NOT NULL THEN ''YES''
ELSE ''NO''
END mengajukan_mpp ,
(SELECT count (distinct CONTACT_PERSON_ID)
FROM PER_CONTACT_RELATIONSHIPS pcr, HR_LOOKUPS hl
WHERE pcr.person_id = paaf.person_id
AND hl.LOOKUP_TYPE = ''CONTACT''
AND hl.lookup_code LIKE ''BI_A%''
AND hl.lookup_code NOT IN (''BI_A'', ''BI_AM'')
AND hl.lookup_code = pcr.contact_type ) jumlah_anak ,
(SELECT COUNT (*)
FROM PER_CONTACT_RELATIONSHIPS pcr, HR_LOOKUPS hl
WHERE pcr.person_id = paaf.person_id
AND hl.LOOKUP_TYPE = ''CONTACT''
AND hl.lookup_code LIKE ''BI_A%''
AND hl.lookup_code NOT IN (''BI_A'', ''BI_AM'')
AND hl.lookup_code = pcr.contact_type
AND pcr.dependent_flag = ''Y''
AND ''' @EFEKTIF_DATE ''' BETWEEN TRUNC (date_start)
AND NVL (TRUNC (date_end), ''31-DEC-4712'') ) jumlah_anak_ditanggung
FROM per_people_f papf ,
per_person_types ppt ,
(SELECT *
FROM (SELECT paaf.* ,
final_process_date ,
CASE
WHEN pps.final_process_date = paaf.effective_end_date
AND NVL((SELECT DISTINCT ''Y'' FROM PER_PERIODS_OF_SERVICE PPOS WHERE PERSON_ID = paaf.person_id AND DATE_START > paaf.effective_end_date), ''N'') = ''N'' THEN to_date(''31-DEC-4712'' , ''DD-MON-YYYY'')
ELSE paaf.effective_end_date
END effective_end_date1
FROM per_assignments_f paaf ,
per_periods_of_service pps
WHERE pps.period_of_service_id = paaf.period_of_service_id
AND paaf.person_id = pps.person_id) a
WHERE ''' @EFEKTIF_DATE ''' BETWEEN a.effective_start_date AND a.effective_end_date1) paaf ,
per_grades pg ,
per_grade_definitions pgd ,
hr_organization_units haou ,
hr_lookups hl2 ,
per_assignment_status_types st ,
per_assignment_status_types_tl sttl ,
per_ass_status_type_amends amd ,
per_ass_status_type_amends_tl amdtl ,
(SELECT *
FROM (SELECT ppa.person_id ,
ppa.creation_date ppa_creation_date ,
pac.*
FROM per_person_analyses ppa ,
per_analysis_criteria pac , -- fixing
(SELECT ppa1.person_id, ppa1.date_from, max(ppa1.creation_date) creation_date
FROM per_person_analyses ppa1
INNER JOIN fnd_id_flex_structures_vl f on ppa1.id_flex_num = f.id_flex_num AND f.id_flex_structure_code = ''BI_PA_TANGGAL_KEPEGAWAIAN''
LEFT JOIN (select person_id, date_from from per_person_analyses p INNER JOIN fnd_id_flex_structures_vl g on p.id_flex_num = g.id_flex_num AND g.id_flex_structure_code = ''BI_PA_TANGGAL_KEPEGAWAIAN'') ppa2
on ppa2.person_id = ppa1.person_id and ppa2.date_from > ppa1.date_from
where ppa1.date_from <= ''' @EFEKTIF_DATE ''' and ppa2.person_id is null
GROUP BY ppa1.person_id, ppa1.date_from ) ppa_v
WHERE 1 = 1
AND pac.analysis_criteria_id = ppa.analysis_criteria_id
AND pac.id_flex_num = ppa.id_flex_num
AND pac.enabled_flag = ''Y''
AND (SELECT id_flex_num
FROM fnd_id_flex_structures_vl
WHERE id_flex_structure_code = ''BI_PA_TANGGAL_KEPEGAWAIAN'') = ppa.id_flex_num
AND ppa.person_id = ppa_v.person_id
AND ppa.date_from = ppa_v.date_from
AND ppa.creation_date = ppa_v.creation_date)) pac_v ,'
DECLARE @SQL_P3 VARCHAR(MAX) =
'
(SELECT max(attribute1) attribute1 ,
person_id
FROM (SELECT to_number(qut.attribute1) attribute1 ,
decode(qua.person_id , NULL , esa.person_id , qua.person_id) person_id
FROM per_qualifications qua ,
per_establishment_attendances esa ,
per_establishments est ,
hr.per_qualification_types qut
WHERE qua.attendance_id = esa.attendance_id( )
AND esa.establishment_id = est.establishment_id( )
AND qut.qualification_type_id = qua.qualification_type_id)
GROUP BY person_id) pend ,
hr.per_qualification_types pqt ,
(SELECT ''Y'' cek ,
person_id
FROM (SELECT ppa.person_id ,
ppa.creation_date ppa_creation_date ,
pac.*
FROM per_person_analyses ppa ,
per_analysis_criteria pac , -- fixing
(SELECT ppa1.person_id, ppa1.date_from, max(ppa1.creation_date) creation_date
FROM per_person_analyses ppa1
INNER JOIN fnd_id_flex_structures_vl f on ppa1.id_flex_num = f.id_flex_num AND f.id_flex_structure_code = ''BI_PA_SANKSI''
LEFT JOIN (select person_id, date_from from per_person_analyses p INNER JOIN fnd_id_flex_structures_vl g on p.id_flex_num = g.id_flex_num AND g.id_flex_structure_code = ''BI_PA_SANKSI'') ppa2
on ppa2.person_id = ppa1.person_id and ppa2.date_from > ppa1.date_from
where ''' @EFEKTIF_DATE ''' BETWEEN ppa1.date_from AND nvl(ppa1.date_to , ''31-DEC-4712'') and ppa2.person_id is null
GROUP BY ppa1.person_id, ppa1.date_from ) ppa_v
WHERE 1 = 1
AND pac.analysis_criteria_id = ppa.analysis_criteria_id
AND pac.id_flex_num = ppa.id_flex_num
AND pac.enabled_flag = ''Y''
AND(SELECT id_flex_num
FROM fnd_id_flex_structures_vl
WHERE id_flex_structure_code = ''BI_PA_SANKSI'') = ppa.id_flex_num
AND ppa.person_id = ppa_v.person_id
AND ppa.date_from = ppa_v.date_from
AND ppa.creation_date = ppa_v.creation_date)) sanksi ,
(SELECT *
FROM (SELECT person_id ,
performance_rating ,
row_num
FROM (SELECT rownum row_num , yr
FROM (SELECT to_char(dt , ''yyyy'') yr
FROM (SELECT to_date(''31-DEC-''
||
(SELECT max(thn)
FROM apps.xxcust_nk_v) , ''DD-MON-YYYY'') - level dt
FROM dual connect BY level <= to_date(''31-DEC-''
|| substr(to_char(trunc(sysdate) , ''MON-RRRR'') , 5 , 4) , ''DD-MON-YYYY'') - to_date(''01-JAN-''
||(substr(to_char(trunc(sysdate) , ''MON-RRRR'') , 5 , 4) - 4) , ''DD-MON-YYYY''))
GROUP BY to_char(dt , ''yyyy'')
ORDER BY to_char(dt , ''yyyy'') DESC)) tahun ,
apps.xxcust_nk_v nk
WHERE tahun.yr = nk.thn( )) PIVOT(max(performance_rating) FOR row_num IN(1 nk1 ,
2 nk2 ,
3 nk3 ,
4 nk4 ,
5 nk5))) nk_ ,
(SELECT person_id, LISTAGG(thn || '':'' || performance_rating, '';'') within group (order by thn desc) NK
FROM apps.xxcust_nk_v
GROUP by person_id) nk_data,
(SELECT person_id ,avg(performance_rating) performance_rating
FROM (SELECT rownum row_num,yr
FROM (SELECT to_char(dt , ''yyyy'') yr
FROM (SELECT to_date(''31-DEC-''
||
(SELECT max(thn)
FROM apps.xxcust_nk_v) , ''DD-MON-YYYY'') - level dt
FROM dual connect BY level <= to_date(''31-DEC-''
|| substr( to_char(trunc(sysdate) , ''MON-RRRR'') , 5 , 4) , ''DD-MON-YYYY'') - to_date(''01-JAN-''
||(substr(to_char(trunc(sysdate) , ''MON-RRRR'') , 5 , 4) - 4) , ''DD-MON-YYYY''))
GROUP BY to_char(dt , ''yyyy'')
ORDER BY to_char(dt , ''yyyy'') DESC)) tahun ,
apps.xxcust_nk_v nk
WHERE tahun.yr = nk.thn( )
GROUP BY person_id) nk_rata ,
(select person_id, max(effective_end_date) end_date from per_people_f
where current_emp_or_apl_flag=''Y''
group by person_id) term
WHERE 100=100
AND papf.person_id = paaf.person_id
AND paaf.grade_id = pg.grade_id( )
AND pg.grade_definition_id = pgd.grade_definition_id( )
AND term.person_id = papf.person_id
AND ''' @EFEKTIF_DATE ''' BETWEEN papf.effective_start_date AND nvl(papf.effective_end_date , to_date(''31-DEC-4712'' , ''DD-MON-YYYY''))
AND paaf.organization_id = haou.organization_id
AND papf.marital_status = hl2.lookup_code( )
AND hl2.enabled_flag( ) = ''Y''
AND hl2.lookup_type( ) = ''MAR_STATUS''
AND paaf.primary_flag = ''Y''
AND ppt.person_type_id = papf.person_type_id
AND paaf.assignment_status_type_id = st.assignment_status_type_id
AND paaf.assignment_status_type_id = amd.assignment_status_type_id( )
AND paaf.business_group_id 0 = amd.business_group_id( ) 0
AND st.assignment_status_type_id = sttl.assignment_status_type_id
AND sttl.language = userenv(''LANG'')
AND amd.ass_status_type_amend_id = amdtl.ass_status_type_amend_id( )
AND decode(amdtl.ass_status_type_amend_id , NULL , ''1'' , amdtl.language) = decode(amdtl.ass_status_type_amend_id , NULL , ''1'' , userenv(''LANG''))
AND papf.person_id = pac_v.person_id( )
AND papf.person_id = pend.person_id( )
AND papf.person_id = sanksi.person_id( )
AND pqt.attribute1( ) = pend.attribute1
AND nk_.person_id( ) = papf.person_id
AND nk_rata.person_id( ) = papf.person_id
AND nk_data.person_id( ) = papf.person_id
AND papf.employee_number IS NOT NULL
AND nvl(floor (months_between( ''' @EFEKTIF_DATE ''' , to_date(pac_v.segment4 , ''RRRR/MM/DD HH24:MI:SS'')) / 12) , 0) = nvl(NULL , nvl(floor(months_between( ''' @EFEKTIF_DATE ''' , to_date(pac_v.segment4 , ''RRRR/MM/DD HH24:MI:SS'')) / 12) , 0))
AND nvl(floor (months_between( ''' @EFEKTIF_DATE ''' , to_date(pac_v.segment8 , ''RRRR/MM/DD HH24:MI:SS'')) / 12) , 0) = nvl(NULL , nvl(floor(months_between( ''' @EFEKTIF_DATE ''' , to_date(pac_v.segment8 , ''RRRR/MM/DD HH24:MI:SS'')) / 12) , 0))
AND nvl(floor (months_between( ''' @EFEKTIF_DATE ''' , to_date(pac_v.segment7 , ''RRRR/MM/DD HH24:MI:SS'')) / 12) , 0) = nvl(NULL , nvl(floor(months_between( ''' @EFEKTIF_DATE ''' , to_date(pac_v.segment7 , ''RRRR/MM/DD HH24:MI:SS'')) / 12) , 0))
AND nvl(floor (months_between( ''' @EFEKTIF_DATE ''' , to_date(pac_v.segment6 , ''RRRR/MM/DD HH24:MI:SS'')) / 12) , 0) = nvl(NULL , nvl(floor(months_between( ''' @EFEKTIF_DATE ''' , to_date(pac_v.segment6 , ''RRRR/MM/DD HH24:MI:SS'')) / 12) , 0))
AND nvl(pqt.NAME , 1) = nvl(NULL , nvl(pqt.NAME , 1))
ORDER BY papf.employee_number ,
papf.last_name'
this stored procedure supposed to returning data from linked server to oracle. when i execute the variable using EXEC(@SQL_P1 @SQL_P2 @SQL_P3) AT ERP and running the Stored Procedure,it doesn't returning data in rows. capture
but when i use SELECT @SQL_P1 @SQL_P2 @SQL_P3 then running the procedure,it returning the query that can be run in SQL ORACLE DEVELOPER.
so what is wrong with it?
CodePudding user response：
it's solved,i change some parameters with sysdate in the query. but at some point it only receiving date as a parameters. idk,as long as it works :)
cheers.. thank you