Reading and comparing records in a table

[Sheen]

Hi All,


I'm a newbee to JDE and trying to build this report: Any help with Event Rules or logic or approach will be appreciated.
I have a new report requirement:
There is a table with 7 records with a unique key
Job Code : J and E.

there are 3 records with J type and 4 records of E type in this table F5509002.
J - is Job type and it has 3 mandotory training courses for that Job and
E - is for Employee the available courses are 4.

requirement is to compare both these J and E types and look for matching training courses
and display the course that is missing in a given date range(table has Job Code, effective start date and End date)

My question is how do I compare these records within the same table and get the desired result.

The report is to be run by Employee or by Company.

Sample data:
SDB JBCD(Job Code) KY(Training Code) EFT EFTE
E 100 ABE 1/1/2014 12/31/2014
E 100 ABSLS 1/1/2013 12/31/2013
E 100 ACMCL1 1/1/2014 12/31/2014
E 100 ABSLS 1/1/2013
J 100 ABE
J 100 ABSLS
J 100 AC

Based on this example the report should print
J 100 AC
as this is mandatory for Job but not available for the Employee.
Please help.

 

[peterbruce]

Sheen,

Select the J rows and on each J row attempt to fetch the corresponding E row with additional parameters (eg date range). If the fetch fails, report the J row.

 

[Kishor@jde]

Hi Sheen .Try below code ,Hope it will work .
Listing of ER for Report: Report : compate training (R5509002)




=======================================================================
SECTION: view on compate table [COLUMNAR SECTION] (S1)
=======================================================================
OBJECT: SECTION
EVENT: Do Section
-----------------------------------------------------------------------
OPT: Using Defaults
0001 VA sec_VarDocVoucherInvoiceE = "100"
0002 F5509002.Select
BC Line Type (F5509002)(LNTY) != TK Line Type
BC Description (F5509002)(DESC) = TK Description
BC Document (Voucher, Invoice, etc.) (F5509002)(DOC) = TK Document (Voucher, Invoice, etc.)
0003 F5509002.Fetch Next
VA sec_VarDocVoucherInvoiceE <- TK Document (Order No, Invoice, etc.)
0004 If SV File_IO_Status is equal to CO SUCCESS
0005 Suppress Section Write
0006 Else
0007 If BC Line Type (F5509002)(LNTY) is equal to "E"
0008 Suppress Section Write
0009 End If
0010 End If


Thursday December 18, 2014 20:28

 

[Kishor@jde]

Listing of ER for Report: Report : compate training (R5509002)




=======================================================================
SECTION: view on compate table [COLUMNAR SECTION] (S1)
=======================================================================
OBJECT: SECTION
EVENT: Do Section
-----------------------------------------------------------------------
OPT: Using Defaults
0001 VA sec_VarDocVoucherInvoiceE = "100"
0002 F5509002.Select
BC Line Type (F5509002)(LNTY) != TK Line Type
BC Description (F5509002)(DESC) = TK Description
BC Document (Voucher, Invoice, etc.) (F5509002)(DOC) = TK Document (Voucher, Invoice, etc.)
0003 F5509002.Fetch Next
VA sec_VarDocVoucherInvoiceE <- TK Document (Order No, Invoice, etc.)
0004 If SV File_IO_Status is equal to CO SUCCESS
0005 Suppress Section Write
0006 Else
0007 If BC Line Type (F5509002)(LNTY) is equal to "E"
0008 Suppress Section Write
0009 End If
0010 End If


Thursday December 18, 2014 20:28


Hope it will help!!!!