Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

INVENTORY QTY + VALUE + FIRST COST 1

Status
Not open for further replies.

Johnny48

Technical User
Aug 19, 2010
18
CA
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 ?
 
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.
 
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 ?
 
*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 ?
 
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 ??
 
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?
 
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 ?
 
It should be possible, all the history is there - unless it was cleared.
 
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!
 
I think this is ctree or something though. Pervasive would be .btr files right? Or can they be .dat files?

Thanks!
 
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top