PORCPAL and PORCPL

[iwells]

Hi,

Our organization finds the Accpac GRNI (goods receipted not invoiced) report very inefficient so I've created a new report in SSRS which does the following:

-- RCP
SELECT
PORCPAH.TRANSDATE,
PORCPAL.PONUMBER PONUMBER,
PORCPAH.RCPNUMBER RCPNUMBER,
PORCPAH.RCPCURR CURRENCY,
POAAPC.SCEXTENDED SCEXTENDED,
POAAPC.FCEXTENDED FCEXTENDED
FROM
POAAPC
INNER JOIN
PORCPAL
ON POAAPC.DAYENDSEQ = PORCPAL.DAYENDSEQ AND
POAAPC.HEADSEQ = PORCPAL.RCPAHSEQ AND
POAAPC.LINESEQ = PORCPAL.RCPALSEQ AND
POAAPC.TRANSTYPE = 3
INNER JOIN
PORCPAH
ON PORCPAL.DAYENDSEQ = PORCPAH.DAYENDSEQ AND
PORCPAL.RCPAHSEQ = PORCPAH.RCPAHSEQ AND
PORCPAH.POSTDATE BETWEEN @intStartDate AND @intEndDate
INNER JOIN
PORCPL
ON PORCPAL.RCPLSEQ = PORCPL.RCPLSEQ
INNER JOIN
PORCPH1
ON PORCPL.RCPHSEQ = PORCPH1.RCPHSEQ
INNER JOIN
POPORH1
ON PORCPH1.PORHSEQ = POPORH1.PORHSEQ

This is only the receipts portion obviously and the logic continues across all documents, but I've come across and odd data error in one of our organizations. Basically what's happening is 7500 records in PORCPL and PORCPAL don't join by RCPLSEQ and as I put more investigation into this I see PORCPAL has a value of 0 for RCPLSEQ for these records (from almost 7 years ago).

First, is my logic sound and my assumption that these value should ALWAYS join be correct? Second, has anyone run in to anything like this before? Third, can a data purge cause this?

I'm running a data integrity check on one of our test servers to see what that's going to come back with, but that's going to take a day.

 

[ettienne]

I wouldn't join all those tables... but that's just me.
PORCPL = receipt detail lines, PORCPAL = receipt detail audit lines. They effectively have the same data, PORCPL is populated after a receipt is posted and PORCPAL is populated after day end processing. I would use PORCPL and it's associated tables.
Who knows why they are different... I would put it to fields added in newer versions and 0 being the initial value.

 

[iwells]

You have to use the audit tables because POAAPC only joins to PORCPAL on the day end posting info and then from there you go to PORCPL.

 

[ettienne]

If you have to use POAAPC....
Doing it that way excludes receipts that have not been day ended, but again that's just the way I would work.

 

[iwells]

I have to use POAAPC that's the purpose of the report, determine accrual amounts that haven't been invoiced yet.

 

[ettienne]

No not really, that report sucks in my opinion. You are looking for receipts not invoiced, so you are looking for RCPHSEQ not in POINVH1.

 

[iwells]

Sorry, I totally disagree as that's the entire point of POAAPC. How would you know how much was accrued, what accounts it was accrued to and etc?

 

[ettienne]

I know... but that's me. You do what works for you.

 

[iwells]

I'm open to better solutions if you can tell me a way to determine what amounts went to what accrual accounts without using the account set because that's no good for history if the accrual accounts change.

Do you have a solution that would accurately reflect the historical clearing account? We have 6 different accrual accounts and I'm not a believer of taking it from the account set as mentioned for the historical reason.

 

[iwells]

Hi,

To continue this item I'm wondering if anyone else has experienced this anywhere? I'm now scanning through our company databases and noticing many of the audit tables (PORCPAL/POINVAL/etc) have many records where RCPLSEQ/INVLSEQ/etc to join to the appropriate line table is 0. I could deal with a few records here and there, but in some cases I'm talking about 10% of the records.

SELECT
*
FROM
POINVAL
WHERE
POINVAL.INVLSEQ = 0

OR

SELECT
*
FROM
PORCPAL
WHERE
PORCPAL.RCPLSEQ = 0

Is that returning records for others and if so do you see any reason why these records should be 0?

 

[iwells]

I've come across this again recently as I'm correcting our GRNI issues across the organization and one of the things I've noticed is ALL the records returned by the PORCPAL query above have a TRANSTYPE = 13.