Uploading the ASCII File for Approved Transcripts

Transcript programs must exist prior to upload. The ASCII file you upload must be tab delimited with a particular field structure and sequence (see below). Records can be reviewed and edited prior to creating transcript records.

 

The upload automatically generates a screen report showing a list of ASCII records, the name of the customer from the database, and error codes (if they exist). The error codes explain why something cannot be loaded, and include the following:

·            Customer number does not exist

·            Customer name does not match the name in the ASCII file (based on the first 2 characters)

·            Invalid validation code

·            Grade below minimum

 

Repeat transcripts are only allowed in the following very specific situations:  

·            When a value is entered the Sub Program text box, which makes the transcript record unique.

·            When the user checks the Allow Repeat Transcripts checkbox.

 

The following is the logic in determining whether or not a transcript record is unique:

·            If a transcript is being created for a Transcript Program, the user needs to enter a value in the Sub Program text box.

·            If a customer transcript record is being created for a course or exam, the CE Credit Date is not be part of the uniqueness test for the transcript. For example, if a customer takes Accounting 101 and gets credit on January 1 and then retakes the course on September 1, the customer is not entitled to duplicate CE credits for taking the same course or exam. However, if the user checks the Allow Repeat Transcripts checkbox, the customer can retake the course or exam and get a transcript record to record the new grade, but the CE credits amount will be 0 and the transcript status will be REPEAT.  

Currently, Personify does not support users being able to retake a course to get repeat credits. However,you can create a new transcript program for the course as a workaround.   

·            If a customer transcript record is being created for a customer who has purchased a non-renewable or non-standing order product, the CE_CREDIT_DATE is not be part of the uniqueness test.  A customer should not get duplicate CE credits for purchasing the same product on different dates.

·            If a customer transcript record is created and credit is earned for a meeting product by attending the meeting, the customer gets a transcript record with CE credits.  

·            For customer transcript records being created because of an order, the system is checking that the order line status is active and the transcript is being created for the order line ship-to customer, not the bill-to customer.

Please note that when ANY of the above actions occur, they call the UploadTranscripts stored procedure.

To upload the ASCII file for approved transcript programs:

1.    From the Approved Transcript Program Definition screen, click ASCII Upload for Approved Transcripts from the Transcript Uploads task category.
Alternatively, you can enter "TRN005A" into the command line.
The ASCII Upload for Approved Programs screen displays.

2.    Search and select an approved program.
The ASCII Upload for Approved Transcripts screen displays, as shown below.

3.    If necessary, enter the Sub program. When a value is entered in this text box, the transcript record is unique.

4.    Select the Credit Date from the drop-down.

5.    Select the CE Credit Type from the drop-down.

6.    Enter the number of Credits.

7.    Click Browse and search for a file to upload.

8.    The Batch field defaults to the batch number of the uploaded transcript records. This is system-generated, but it can be changed.

9.    If necessary, check the Allow Repeat Transcript Creation checkbox at the bottom left of the screen.

10.  Click Upload.

11.  Review the Upload Details and add or delete records as necessary.

12.  Click Validate.

13.  Click Save.

Screen Element

Description

Program Information

Sub program

Text box. Enter a value in this field in order to make the transcript record unique.

Credit Date

Drop-down. The date the credit will be given for the uploaded transcript records.

CE Credit Type

Drop-down. The type of credit that will be given to the uploaded transcript records.

Credits

Text box. The number of credits given to the uploaded transcript records.

Batch

Text box. The batch number of the uploaded transcript records. This is system-generated, but it can be changed.

Upload File Specification

Browse

Button. When clicked, search for the ASCII file to upload.

Upload

Button. When clicked, the ASCII file is uploaded.

Validate

Button. When clicked, the ASCII file is validated. The upload validates the first five letters of the last name (when provided) with the last name of the customer in the database as a check for valid customer numbers being passed.

Create Transcripts

Button. When clicked, a transcript is created based on the ASCII file information. The transcript is only created for records without errors.

Upload Details

Add

Button. When clicked, a record is added to the upload.

Delete

Button. When clicked, the highlighted record is deleted from the upload.

Print

Button. When clicked, the ASCII file prints.

Allow Repeat Transcript Creation

Checkbox. When checked, indicates repeat transcript entries are permitted.

Approved Transcript Program ASCII File Upload Field Structure and Sequence

Seq #

Field Name

Required?

Comments

1

Master Customer ID

Y

Valid Customer ID

2

Sub Customer ID

Y

Valid Sub Customer ID

3

Last Name

N

First two letters will be validated against the customer’s last name in the database.

4

Transcript Program ID

N

This must be a valid Transcript Program ID:

·       Trn_Program.Org_ID = Org ID of Logged-On User

·       Trn_Program.Org_Unit_ID = Org Unit ID of Logged-On User

·       Trn_Program.Program_ID = Uploaded Program ID

·       Trn_Program.Valid_Until_Date IS NULL or >= CE Credit Date

5

Exam Code

N

This must be a valid Exam Code:

·       Trn_Program.Org_ID = Org ID of Logged-On User

·       Trn_Program.Org_Unit_ID = Org Unit ID of Logged-On User

·       Trn_Program.Program_ID = Uploaded Program ID

·       Trn_Program.Exam_Code = Uploaded Exam Code

·       Trn_Program.Related_Product_Type_Code = 'EXAM'

 

Or

·       Trn_Program_Exam.Org_ID = Org ID of Logged-On User

·       Trn_Program_Exam.Org_Unit_ID = Org Unit ID of Logged-On User

·       Trn_Program_Exam.Program_ID = Uploaded Program ID

·       Trn_Program_Exam.Exam_Code = Uploaded Exam Code

·       Trn_Program.Related_Product_Type_Code = 'EXAM'

 

Or

·       Product_Education.Org_ID = Org ID of Logged-On User

·       Product_Education Org_Unit_ID = Org Unit ID of Logged-On User

·       Product_Education.Program_ID = Uploaded Program ID

·       Product_Education.Exam_Code = Uploaded Exam Code

·       Trn_Program.Program_ID = Uploaded Program ID

·       Trn_Program.Related_Product_Type_Code = 'PRODUCT'

·       Trn_Program.Program_ID = Product_Education.Program_ID

 

Or

·       Trn_Program.Org_ID = Org ID of Logged-On User

·       Trn_Program.Org_Unit_ID = Org Unit ID of Logged-On User

·       Trn_Program.Program_ID = Uploaded Program ID

·       Trn_Program.Exam_Code = Uploaded Exam Code

·       Trn_Program.Related_Product_Type_Code = 'NONE

6

Exam Form ID

N

 

7

Test Version

N

 

8

Course Code

N

This must be a valid Exam Code:

·       Trn_Program.Org_ID = Org ID of Logged-On User

·       Trn_Program.Org_Unit_ID = Org Unit ID of Logged-On User

·       Trn_Program.Program_ID = Uploaded Program ID

·       Trn_Program.Course_Code = Uploaded Course Code

·       Trn_Program.Related_Product_Type_Code = 'COURSE'

 

Or

·       Trn_Program.Org_ID = Org ID of Logged-On User

·       Trn_Program.Org_Unit_ID = Org Unit ID of Logged-On User

·       Trn_Program.Program_ID = Uploaded Program ID

·       Trn_Program.Course_Code = Uploaded Course Code

·       Trn_Program.Related_Product_Type_Code = 'NONE

9

Product ID

Sometimes

Required if Trn_Program.Related_Product_Type_Code = 'PRODUCT' or Trn_Program.Product_ID = Uploaded product ID.

10

Sub program

N

 

11

Grade Code

Sometimes

Required if Trn_program.Passing_Grade_Code is not null or Trn_Program_Exam.Passing_Grade_Code is not null.

12

Pass Fail Code

N

If the file has the PASS_Fail code provided, then on validate, system will check if it not null.

 

If yes, then it will compare the value provided in the file with the active codes of Type 'PASS_FAIL' of subsystem 'TRN'.

·       If it matches, then system will just retain the value provided in the file and not evaluate again as per Grade code.

·       If it is an invalid value, then as per existing logic system will evaluate the pass fail code as per grade provided.

 

If it is null in the file, then system will populate as per existing logic on the basis of grade code provided in the file.

13

CE Credit Type Code

N

If not provided, the system will get it from the parameter entered by the user. If not provided by the user, the system will evaluate each customer to make a determination of the CE Credit Type that should be awarded.

14

CE Credits

N

As of 7.6.0, this is no longer evaluated.

15

Attendance Flag

N

If not provided, the system will set it to “Y”.

16

Order No

Sometimes

Required if the Product ID is not null. If provided, the system evaluates whether the customer in the ASCII upload record is the ship-to customer on the order line where:

·       Order_Detail.ORDER_NO = [ASCII Upload]ORDER_NO

·       Order_Detail.ORDER_LINE_NO = [ASCII Upload]ORDER_LINE_NO.

17

Order Line No

Sometimes

Required if the Product ID is not null. If provided, the system evaluates whether the customer in the ASCII upload record is the ship-to customer on the order line where:

·       Order_Detail.Ship_Master_Customer_ID = Uploaded Master Customer ID

·       Order_Detail.Ship_Sub_Customer_ID = Uploaded Sub Customer ID

·       Order_Detail.Order_No = Uploaded Order No

·       Order_Detail.Order_Line_No = Uploaded Order Line No

·       Order_Detail.Product_ID = Uploaded Order Line No

18

Comments

N

Comments