ACA_EE_VW (SQL View)	Index Back
Year-End Forms for employees This view will select year-end forms from guide table that meet the settings in Year-End Form Options page.

ACA_EE_VW

(SQL View)

Year-End Forms for employees

This view will select year-end forms from guide table that meet the settings in Year-End Form Options page.

SELECT %subrec(ACA_SS_SBR, A) FROM PS_ACA_SS_GDE A , PS_ACA_FORM_OPTS B WHERE A.ACA_FORM_VIEW_DT <= %CurrentDateIn AND A.ACA_ALE_MEMBER = B.ACA_ALE_MEMBER AND A.ACA_CALENDAR_YEAR = B.ACA_CALENDAR_YEAR AND A.ACA_FORM_TYPE IN ('1095') AND (( A.ACA_FORM_STATUS = 'O' AND A.ACA_SEQNO = ( SELECT MAX(A1.ACA_SEQNO) FROM PS_ACA_SS_GDE A1 WHERE A1.EMPLID = A.EMPLID AND A1.ACA_ALE_MEMBER = A.ACA_ALE_MEMBER AND A.ACA_FORM_VIEW_DT <= %CurrentDateIn AND A1.ACA_CALENDAR_YEAR = A.ACA_CALENDAR_YEAR AND A1.ACA_FORM_TYPE = A.ACA_FORM_TYPE AND A1.ACA_FORM_STATUS = A.ACA_FORM_STATUS AND A1.ACA_FINAL_PRINT = 'Y') OR (A.ACA_FORM_STATUS IN ('C','R') AND A.ACA_FINAL_PRINT = 'Y')))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

2 ACA_CALENDAR_YEAR Number(4,0) SMALLINT NOT NULL 1094-C/1095-C Calendar Year

3 ACA_ALE_MEM_GRP Character(3) VARCHAR2(3) NOT NULL Applicable Large Employer

4 ACA_ALE_MEMBER Character(3) VARCHAR2(3) NOT NULL Used to contain value of ALE Member name

5 ACA_FORM_TYPE Character(4) VARCHAR2(4) NOT NULL ACA Form Type. The default Value is 1095-C
1095=1095-C

6 ACA_SEQNO Number(2,0) SMALLINT NOT NULL Sequence Number

7 ACA_YE_FORM_SEQNUM Number(2,0) SMALLINT NOT NULL A sequential number to indicate the number of Form 1095-C that have been issued/generated for an employee. Initial copy will have a value of 1.

8 ACA_PROCESS_TYPE Character(1) VARCHAR2(1) NOT NULL Process type.
C=Correction
O=Original
P=Replace Transmission
Q=Replace Submission
R=Reissue

9 ACA_FORM_ISSUE_DT Date(10) DATE This field is used in ACA Self Service Forms. It stores the date when the forms were generated.

10 ACA_FORM_VIEW_DT Date(10) DATE This field is used in ACA Self Service Year-End Forms. It stores the date that determines when the forms become viewable in Self Service.

11 ATTACHSYSFILENAME Character(128) VARCHAR2(128) NOT NULL Unique Sys Filename

12 ATTACHUSERFILE Character(64) VARCHAR2(64) NOT NULL Attached File

13 ACA_YE_FILEURLID Character(30) VARCHAR2(30) NOT NULL URL ID used to store and retrieve year-end pdf forms.

14 ACA_REPORT_URL Character(254) VARCHAR2(254) NOT NULL Report URL.

15 ACA_BURST_ID Character(40) VARCHAR2(40) NOT NULL Stores the value used for bursting a PDF file into separate employee-level files.

16 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance

17 REPORT_DEFN_ID Character(12) VARCHAR2(12) NOT NULL Report Name (see PSXPRPTDEFN).

18 BURST_FIELDNAME Character(30) VARCHAR2(30) NOT NULL Burst Field Name

19 ACA_NOTIFY_FLG Character(1) VARCHAR2(1) NOT NULL Indicates whether notification of availability for viewing a year-end form has been sent to employee. Y - notification already sent to employee N - notification not yet sent to employee

20 ACA_FINAL_PRINT Character(1) VARCHAR2(1) NOT NULL Identifies whether final run or not.
Y/N Table Edit

21 ACA_FORM_STATUS Character(1) VARCHAR2(1) NOT NULL Form status.
C=Correction
D=Duplicate
O=Original
R=Reissue
V=Void

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	ACA_CALENDAR_YEAR	Number(4,0)	SMALLINT NOT NULL	1094-C/1095-C Calendar Year
3	ACA_ALE_MEM_GRP	Character(3)	VARCHAR2(3) NOT NULL	Applicable Large Employer
4	ACA_ALE_MEMBER	Character(3)	VARCHAR2(3) NOT NULL	Used to contain value of ALE Member name
5	ACA_FORM_TYPE	Character(4)	VARCHAR2(4) NOT NULL	ACA Form Type. The default Value is 1095-C 1095=1095-C
6	ACA_SEQNO	Number(2,0)	SMALLINT NOT NULL	Sequence Number
7	ACA_YE_FORM_SEQNUM	Number(2,0)	SMALLINT NOT NULL	A sequential number to indicate the number of Form 1095-C that have been issued/generated for an employee. Initial copy will have a value of 1.
8	ACA_PROCESS_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Process type. C=Correction O=Original P=Replace Transmission Q=Replace Submission R=Reissue
9	ACA_FORM_ISSUE_DT	Date(10)	DATE	This field is used in ACA Self Service Forms. It stores the date when the forms were generated.
10	ACA_FORM_VIEW_DT	Date(10)	DATE	This field is used in ACA Self Service Year-End Forms. It stores the date that determines when the forms become viewable in Self Service.
11	ATTACHSYSFILENAME	Character(128)	VARCHAR2(128) NOT NULL	Unique Sys Filename
12	ATTACHUSERFILE	Character(64)	VARCHAR2(64) NOT NULL	Attached File
13	ACA_YE_FILEURLID	Character(30)	VARCHAR2(30) NOT NULL	URL ID used to store and retrieve year-end pdf forms.
14	ACA_REPORT_URL	Character(254)	VARCHAR2(254) NOT NULL	Report URL.
15	ACA_BURST_ID	Character(40)	VARCHAR2(40) NOT NULL	Stores the value used for bursting a PDF file into separate employee-level files.
16	PROCESS_INSTANCE	Number(10,0)	DECIMAL(10) NOT NULL	Process Instance
17	REPORT_DEFN_ID	Character(12)	VARCHAR2(12) NOT NULL	Report Name (see PSXPRPTDEFN).
18	BURST_FIELDNAME	Character(30)	VARCHAR2(30) NOT NULL	Burst Field Name
19	ACA_NOTIFY_FLG	Character(1)	VARCHAR2(1) NOT NULL	Indicates whether notification of availability for viewing a year-end form has been sent to employee. Y - notification already sent to employee N - notification not yet sent to employee
20	ACA_FINAL_PRINT	Character(1)	VARCHAR2(1) NOT NULL	Identifies whether final run or not. Y/N Table Edit
21	ACA_FORM_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Form status. C=Correction D=Duplicate O=Original R=Reissue V=Void