Purging PO Detail R4311P

[rommel]

Hi List,
Has any one had any experience with running the PO Detail Purge Program
R4311P?

There are some aspects of the program that I do not understand .Our
intention is to purge record from the F4311 table to another Table from
where the records can be retrieved if required.

The JDE documentation on this program does not seem adequate to me.The
processing options ask for a name of the table to which the data has to be
saved to. There is also a mention of the need to create a Purge environment
in case data has to be saved.The question is :Is it absolutely essestial to
create another environment to simply store this data?
If a purge environment were to be created and the data were to be puged to
another table ,could this table be viewed using UTB?I posed this question to
JDE response Line and their response was "No".If the data cannot be viewed
using UTB,why is there a need to create a purge environment and another
table?

Would it be easier to write a custom program to delete old data off the
F4311 table and simply put it into another custom table Within the same
environment?
I've checked KG on this issue and all I can find is a document called
"Breaking News" that says the purge program purges exactly the opposite of
what you want it to purge and that there is a SAR to fix this.

Has any one run this program before?Any thoughts, suggestions would be
greatly appreciated!Thanks!

Regards,
Rommel

 

[DSauve]

Rommel,

I, too went through the exercise of trying to figure out the JDE Purchase Order Purge programs, and was disappointed in what I found. The way JDE has written this set of purges (R4301P - Purge Headers, R4311P - Purge Detail, R43121P - Purge PO Receipts, R43199P - Purge PO Ledger, R43960 - Purge PO Text Lines) as five separate programs does not lend itself well at all to purging ALL associated records for a given PO. In other words, you may run one of the purges with data selection based on, say, F4311 fields, and purge some of those records. But then if you run the F4301 purge, you may not purge the header record for the PO's for which the F4311 records were just purged. So you could end up with a PO that has the F4301 record still intact, but its F4311 or F43121 records are purged. This is not very clean, and could make trying to find archived vs. current information confusing.

Instead, I've opted to write an Oracle PL/SQL procedure that purges PO's in whole (i.e. the header and ALL associated records) that meet the following criteria:
1. All PO Lines (F4311) must be at a next status of 999
2. The maximum last update date (UPMJ) on all PO Lines (F4311) must be at least 400 days ago. (We chose 400 days as our retention period, you of course can choose whatever make sense in your organization).

The purged records are stored off into associated new F55* tables in the same environment (in the business data source, e.g. CRPDTA), so we didn't have to create any new purge environment. The tables I created and use are: F554301, F554311, F5543121, F5543199.

I've run this in our CRP environment, and all looks good. I'll attach the source .sql file to this post. Of course, this source code is provided AS-IS, with no warranties or guarantees. You should test this code out yourself on a test environment before running in production.

Note: This .sql code is for Oracle database usage. Also, the JDE2DATE function (which is called by the purge .sql code) I've included below is for Oracle. You can find other database date conversion routines by searching the the JDEList archives.


JDE2DATE.SQL:
REM -------------------------------------------------------------------
REM Name: JDE2Date.sql
REM Purpose: Stored Function for Converting JDE Dates
REM to Standard Dates.
REM Usage: From within SQL script or command. Example;
REM Select ABAN8, ABALPH, JDE2Date(ABUPMJ) from F0101;
REM Parameters: Pass in a JDE Numberic Date value - Number(6,0)
REM Returns: An Oracle Date value.
REM Special: Script MUST be run as a Oracle user with the DBA role.
REM

CREATE OR REPLACE FUNCTION JDE2Date (jdedate IN NUMBER)
RETURN DATE
IS
v_dateout DATE;
v_jdedate NUMBER(6,0) :=0;
v_year NUMBER(4,0) :=0;
v_day NUMBER(3,0) :=0;

BEGIN
v_dateout := TO_DATE(19000101,'YYYY MM DD');
if (jdedate IS NOT NULL) AND (jdedate != 0) then
v_jdedate :=jdedate;
v_year := (v_jdedate / 1000) + 1900;
v_day := MOD(v_jdedate,1000) - 1;
v_dateout := TRUNC(TO_DATE(v_year,'YYYY'),'YEAR') + v_day;
end if;

RETURN (v_dateout);

END;




Don Sauve
Wagstaff, Inc.
OW XE, SP15.1, HP-UX 11.0, Oracle 8.1.6

 

[david_mallory]

Don,

Thanks for posting this info. I think your " F55*, same environment, use SQL
" approach makes sense. Our experience with JDE purge programs and mass
update programs was short and unpleasant.

Dave Mallory Denver Water [email protected]

 

[DSauve]

Re: RE: Purging PO Detail R4311P

One other thing I might note, to JDE's defense, is that their WO Purge Program does work well. It is a single UBE that you run (R4801P), and it's data selection is based on WO (F4801) values. Then, for each WO selected, it optionally archives and then purges the WO Header record (F4801), WO Parts List records (F3111), WO Routing records (F3112), and WO Time Transactions records (F31122). These records go into F4801S, F3111S, F3112S, and F31122S tables, respectively, in the same environment. I have used this UBE with good results.

While on the subject of purging, the SO Purge is in the same ugly boat as PO Purge, and I ended up writing SQL code for that one as well.

Don Sauve
Wagstaff, Inc.
OW XE, SP15.1, HP-UX 11.0, Oracle 8.1.6

 

[rommel]

Don,

Thank you for posting this information.I sincerely appreciate the effort and
the time spent in sharing this.We are on an SQL Database so I think we'll
have to follow a similar methodology for the Purge.
Thanks again,
Rommel