Program Title, Program Description, Program Agenda and Program Section ID and Program Section Issue

[Abhishek_Kr]

SELECT *
FROM
	( SELECT DISTINCT pc.seq_num,
		p.program_id ,
		cpnt.cpnt_title                                                                                                               AS program_title ,
		DECODE (p.DURATION_TYPE,'DAYS', pkg_sql.add_days_rpt(pc.ASSGN_DTE ,p.duration),pkg_sql.add_days_rpt(pc.ASSGN_DTE,( p.duration * 7) )) AS totalDuration ,
		s.PERSON_ID_EXTERNAL                                                                                                          AS STUD_ID ,
		s.fname ,
		s.lname ,
		s.mi ,
		pc.ASSGN_DTE ,
		pc.RTYP_ID ,
		p.END_DTE AS DUE_DTE ,
		NULL      AS COMPL_DTE ,
		NULL      AS CMPL_STAT_ID,
		NULL      AS CMPL_STAT_DESC ,
		p.duration ,
		p.PROGRAM_TYPE,
		pt.label_id AS program_type_label,
		p.DURATION_TYPE,
		p.START_DTE,
		sp.STUD_PROGRAM_SYS_GUID
	FROM pa_stud_program sp,
		pa_program p,
		pa_student s,
		pa_stud_cpnt pc,
		ps_program_type pt,
		pa_cpnt cpnt
	WHERE p.PROGRAM_SYS_GUID = sp.PROGRAM_SYS_GUID
	AND cpnt.CPNT_TYP_ID     = p.CPNT_TYP_ID
	AND cpnt.CPNT_ID         = p.CPNT_ID
	AND cpnt.REV_DTE         = p.REV_DTE
	AND pc.STUD_ID           = sp.STUD_ID
	AND sp.stud_id           = s.STUD_ID
	AND pc.CPNT_ID           = sp.CPNT_ID
	AND pc.CPNT_TYP_ID       = sp.CPNT_TYP_ID
	AND pc.REV_DTE           = sp.REV_DTE
	AND pc.seq_num           = sp.seq_num
	AND pc.compl_dte        IS NULL
	AND pt.PROGRAM_TYPE_ID   = p.PROGRAM_TYPE
	/** and p.program_id in [ProgramSearch]
	and [security:pa_student s]
	and sp.stud_id in [UserSearch]
	and s.notactive = [UserStatus]
	and p.program_type in [ProgramTypeSearch]
	and ([ProgramStatus] = 'N' OR [ProgramStatus] = 'B')*/
	UNION
	SELECT DISTINCT pc.seq_num,
		p.program_id ,
		cpnt.cpnt_title                                                                                                               AS program_title ,
		DECODE (p.DURATION_TYPE,'DAYS', pkg_sql.add_days_rpt(pc.ASSGN_DTE ,p.duration),pkg_sql.add_days_rpt(pc.ASSGN_DTE,( p.duration * 7)) ) AS totalDuration ,
		s.PERSON_ID_EXTERNAL ,
		s.fname ,
		s.lname ,
		s.mi ,
		pc.ASSGN_DTE ,
		pc.RTYP_ID ,
		p.END_DTE AS DUE_DTE ,
		pc.compl_dte ,
		pc.CMPL_STAT_ID,
		d.CMPL_STAT_DESC ,
		p.duration ,
		p.PROGRAM_TYPE,
		pt.label_id AS program_type_label,
		p.DURATION_TYPE,
		p.START_DTE,
		sp.STUD_PROGRAM_SYS_GUID
	FROM pa_stud_program sp,
		pa_program p,
		pa_cmpl_stat d,
		pa_student s,
		pa_stud_cpnt pc,
		ps_program_type pt,
		pa_cpnt cpnt
	WHERE pc.CMPL_STAT_ID   = d.CMPL_STAT_ID
	AND cpnt.CPNT_TYP_ID    = p.CPNT_TYP_ID
	AND cpnt.CPNT_ID        = p.CPNT_ID
	AND cpnt.REV_DTE        = p.REV_DTE
	AND sp.PROGRAM_SYS_GUID = p.PROGRAM_SYS_GUID
	AND pc.STUD_ID          = sp.STUD_ID
	AND sp.STUD_ID          = s.STUD_ID
	AND pc.CPNT_ID          = sp.CPNT_ID
	AND pc.CPNT_TYP_ID      = sp.CPNT_TYP_ID
	AND pc.REV_DTE          = sp.REV_DTE
	AND pc.seq_num          = sp.seq_num
	AND pc.compl_dte       IS NOT NULL
	AND pt.PROGRAM_TYPE_ID  = p.PROGRAM_TYPE
		/** and p.program_id in [ProgramSearch]
		and [security:pa_student s]
		and sp.stud_id in [UserSearch]
		and s.notactive = [UserStatus]
		and p.program_type in [ProgramTypeSearch]
		and to_timestamp(to_char(pc.compl_dte, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') >= [CompletedDateFrom]
		and not to_timestamp(to_char(pc.compl_dte, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') > [CompletedDateTo]
		and ([ProgramStatus] = 'Y' OR [ProgramStatus] = 'B')*/
	) a
ORDER BY a.program_id,
	a.stud_id<br>

Currently I have a business requirement to add in existing program status report-

PFA report SQL codes and screenshot of the required program report columns.

1.Program Title and Description

2.Program Agenda

3.Program Section id and title

Please advise.

Thanks

Abhishek