INVENTORY QTY + VALUE + FIRST COST 1

[Johnny48]

Sage Accpac 500 ERP (Version 5.5A) with Pervasive 9:

I need to provide a spreadsheet with our YEAR END INVENTORY QTY by item with the last received date, last received date cost(prior to year end), and ITEM value at year end....

Can this be done via a few SQL queries ?

 

[ettienne]

When was year end?

 

[Johnny48]

January 01 2010

 

[ettienne]

You will have to reconstruct year end QTY and costs from ICHIST. IF you have done it at year end then you could have used ICILOC.

 

[Johnny48]

Thanks etienne,

This seems to give me the ON HAND QTY at year end:
SELECT ICHIST.ITEMNO, Sum(ICHIST.QUANTITY) AS SumOfQUANTITY
FROM ICHIST
WHERE (((ICHIST.ITEMNO)="101614") AND ((ICHIST.TRANSDATE)<=20100101))
GROUP BY ICHIST.ITEMNO;

Now where would I get the VALUE of that item at year end ?

 

[ettienne]

ICHIST as well, sum the costs.

 

[Johnny48]

*lol* I guess I should pay more attention to the data dictionary....

Thansk again etienne...Got this now:

SELECT ICHIST.ITEMNO, Sum(ICHIST.QUANTITY) AS SumOfQUANTITY, Sum(ICHIST.HOMEEXTCST)/SumOfQUANTITY AS COST
FROM ICHIST
WHERE (((ICHIST.ITEMNO)="101614") AND ((ICHIST.TRANSDATE)<=20100115))
GROUP BY ICHIST.ITEMNO;

Now trying to get the LAST PO # & PO COST for the item like this :
SELECT POPORH1.PONUMBER, POPORL.ITEMNO, POPORL.COMPLETION, Max(POPORL.DTCOMPLETE) AS MaxOfDTCOMPLETE, POPORL.UNITCOST, POPORL.STOCKITEM
FROM POPORH1 LEFT JOIN POPORL ON POPORH1.PORHSEQ = POPORL.PORHSEQ
GROUP BY POPORH1.PONUMBER, POPORL.ITEMNO, POPORL.COMPLETION, POPORL.UNITCOST, POPORL.STOCKITEM
HAVING (((POPORL.ITEMNO)="9366W") AND ((POPORL.COMPLETION)=3) AND ((Max(POPORL.DTCOMPLETE))<20100101) AND ((POPORL.STOCKITEM)=1));


However I get multiple rows....


IS there a way just to get the P # and PRICE for a particular ITEM ?

 

[ettienne]

Get the last receipt from ICHIST.

 

[Johnny48]

Again, thanks..

Now I have this:

SELECT ICHIST.ITEMNO, ICITEM.DESC, ICHIST.TRANSTYPE, ICHIST.HOMEEXTCST/ICHIST.QUANTITY AS POCOST, Max(ICHIST.TRANSDATE) AS MaxOfTRANSDATE
FROM ICHIST LEFT JOIN ICITEM ON ICHIST.ITEMNO = ICITEM.ITEMNO
GROUP BY ICHIST.ITEMNO, ICITEM.DESC, ICHIST.TRANSTYPE, ICHIST.HOMEEXTCST/ICHIST.QUANTITY, ICHIST.HOMEEXTCST, ICHIST.QUANTITY
HAVING (((ICHIST.TRANSTYPE)=1) AND ((ICHIST.HOMEEXTCST)>0) AND ((ICHIST.QUANTITY)>0) AND ((Max(ICHIST.TRANSDATE))<20100101));

But I still get multiple rows ??

 

[ettienne]

You want to do all of that in one query?
I see a couple of problems:
ICHIST.TRANSTYPE=1 What about the other transaction types?
ICHIST.HOMEEXTCST>0 and ICHIST.QUANTITY)>0 What about transactions that reduce inventory?

 

[Johnny48]

Hi,

Not sure how to answer this.....

One query would ne nice ...but several is fine as I will run through excel...

1- SELECT ICHIST.ITEMNO, ICITEM.DESC, Sum(ICHIST.QUANTITY) AS SumOfQUANTITY, Sum(ICHIST.HOMEEXTCST)/SumOfQUANTITY AS COST
FROM ICHIST LEFT JOIN ICITEM ON ICHIST.ITEMNO = ICITEM.ITEMNO
WHERE (((ICHIST.TRANSDATE)<=20100115))
GROUP BY ICHIST.ITEMNO, ICITEM.DESC, ICITEM.STOCKITEM
HAVING (((Sum(ICHIST.QUANTITY))<>0) AND ((ICITEM.STOCKITEM)=1));

Seems to give me the QTY at time of INVENTORY, however I noticed negative values and yet we have ticked the no negative inventory option...??

Next I would need the VALUE of the above..(When we receive the PO we take into consideration ALL the landing factors and add them at time of receiving!)..

Now I would need the AVERAGE PO price for the above with same date cut off....

Is this possible ?

 

[ettienne]

It should be possible, all the history is there - unless it was cleared.

 

[stef315]

Dumb question...Where/how are you running these queries? I'm trying to get item cost layers from AccPac to import into a new system. I think I've found the right files but I'm not sure how to connect to them. I'm very familiar with MS SQL but usually just connect to SQL databases. I don't have the version in front of me right now but I do know they are .dat files.

Thank you and sorry for riding along!

 

[Jay Converse]

Pervasive Control Center, or an ODBC connection.

 

[stef315]

I think this is ctree or something though. Pervasive would be .btr files right? Or can they be .dat files?

Thanks!

 

[Jay Converse]

Accpac Pervasive uses DAT, it's not "ctree".

 

[stef315]

Okay, cool, thanks! I'm not on location right now but I will check it out. I used Pervasive years ago and used to do connections to it so I think it will come back to me. Thanks for pointing me in the right direction!