Join POP30310 with POP30390 by POP Receipt Line 1

[barbola]

I can't seem to get this query to work. I want to show all the Receiving line items, quantity and cost for specific GL accounts.

My results are duplicating the data in POP30390 when there is more than one line item on the Receiving transaction.

I can't find a link between the tables to do this. If I try using the dollar amount it doesn't work because if the same line item is used twice on a receiving, it could be only one line in the distributions.

SELECT POP30300.VENDORID, POP30390.ACTINDX, POP30310.POPRCTNM, POP30310.ITEMNMBR, POP30310.UOFM, 
POP30310.EXTDCOST/POP30310.UNITCOST AS UNITS, POP30310.UNITCOST, POP30310.EXTDCOST, IV00101.ITMCLSCD,
POP30300.GLPOSTDT, GL00100.ACTNUMBR_2, GL00100.ACTNUMBR_3, GL00100.ACTNUMBR_4

FROM POP30300

JOIN POP30310 ON POP30300.POPRCTNM = POP30310.POPRCTNM
JOIN IV00101 ON POP30310.ITEMNMBR = IV00101.ITEMNMBR
JOIN POP30390 ON POP30390.CURNCYID = POP30300.CURNCYID AND 
     POP30390.POPRCTNM = POP30300.POPRCTNM AND 
     POP30390.POPRCTNM = POP30310.POPRCTNM AND 
     POP30390.VENDORID = POP30300.VENDORID AND 
     POP30390.XCHGRATE = POP30300.XCHGRATE 
JOIN GL00100 ON POP30390.ACTINDX = GL00100.ACTINDX 

WHERE
     (POP30300.GLPOSTDT Between '5/1/6' And '5/31/6') AND 
     (GL00100.ACTNUMBR_2 Like '6100%') AND 
     (ACTNUMBR_3+ACTNUMBR_4 In ('120000','190000','220000','227000','210000'))

ORDER BY POP30310.POPRCTNM

I want these two lines as part of the results:
POPRCTNM ITEM UNITS EXTDCOST ACCOUNT
RCT000999 1000 20 $500 610001
RCT000999 2000 50 $800 610002

But I am getting this:
POPRCTNM ITEM UNITS EXTDCOST ACCOUNT
RCT000999 1000 20 $500 610001
RCT000999 2000 50 $800 610001
RCT000999 1000 20 $500 610002
RCT000999 2000 50 $800 610002

Aside from running two separate queries and manually matching them up I don't know how else to proceed.

thanks,
Barb

 

[BackStageJim]

missing DISTINCT?

This is why Artemis slew Orion ERP

 

[barbola]

No, tried it, that's not it.

It is giving me the results with 4 records because the two tables aren't can't be linked by line item, so it's showing each receipt line with each account.

Is there maybe another table I should use?

 

[RobertC22]

As you noted, the POP30390 is the final distributions for the PO Receipt. If you had mulitple lines and each line used the same GL account the amount is accumulated.

Here is a script that uses the INVINDX value on the PO Receipt Line to link to the 'original' Account. This may not be the final account as listed in the PO Receipt Distribution when Site ID segment override occurs.

[tt]
SELECT POP30300.VENDORID, POP30310.POPRCTNM, POP30310.ITEMNMBR, POP30310.UOFM,
POP30310.EXTDCOST/POP30310.UNITCOST AS UNITS, POP30310.UNITCOST, POP30310.EXTDCOST, IV00101.ITMCLSCD,
POP30300.GLPOSTDT, GL00100.ACTNUMBR_2, GL00100.ACTNUMBR_3, GL00100.ACTNUMBR_4

FROM POP30300

JOIN POP30310 ON POP30300.POPRCTNM = POP30310.POPRCTNM
LEFT JOIN IV00101 ON POP30310.ITEMNMBR = IV00101.ITEMNMBR /* left join to allow for Non Inventory Items */
JOIN GL00100 ON POP30310.INVINDX = GL00100.ACTINDX


WHERE
     (POP30300.GLPOSTDT Between '5/1/6' And '5/31/6') AND
     (GL00100.ACTNUMBR_2 Like '6100%') AND
     (ACTNUMBR_3+ACTNUMBR_4 In ('120000','190000','220000','227000','210000'))

ORDER BY POP30310.POPRCTNM
[/tt]

------
Robert

 

[barbola]

Thanks, but unfortunately this gives me no results. The default account number is changed to use a site code in segment 3, which is the main purpose of the query.

 

[BobSchleicher]

Add a WHERE POP30310.TRXSORCE like POINV%

POINV=invoice
RECVG=receiving

This should show just the Invoice line and not the Invoice with the Receipt.

BobSchleicher

 

[barbola]

Thanks Bob but that also gave me no results, using both POINV and RECVG.

I sort of was able to run two separate queries and summarize the results, and then compare the totals. The first time they matched (no missing data).

 

[RobertC22]

Try this and let me know if it works for you

declare @Seg integer
select @Seg = ACSGFLOC from IV40100


SELECT 
	GL00105.ACTNUMST OriginalAcctNumber, 
	GL00105_2.ACTNUMST SubsitutedLocationAccountNumber,
	POP30300.VENDORID, POP30310.POPRCTNM, POP30310.ITEMNMBR, POP30310.UOFM,
	case when POP30310.UNITCOST <> 0 then POP30310.EXTDCOST/POP30310.UNITCOST else 0 end AS UNITS, 
	POP30310.UNITCOST, POP30310.EXTDCOST, IV00101.ITMCLSCD,
	POP30300.GLPOSTDT, GLTrlx.ACTNUMBR_2, GLTrlx.ACTNUMBR_3, GLTrlx.ACTNUMBR_4

FROM POP30300

JOIN POP30310 ON POP30300.POPRCTNM = POP30310.POPRCTNM
JOIN IV40700 ON POP30310.LOCNCODE = IV40700.LOCNCODE
LEFT JOIN IV00101 ON POP30310.ITEMNMBR = IV00101.ITEMNMBR /* left join to allow for Non Inventory Items */
JOIN GL00100 ON POP30310.INVINDX = GL00100.ACTINDX
Left JOIN GL00100 GLTrlx on 
GLTrlx.ACTNUMBR_1 =  case when @Seg = 1 then IV40700.Location_Segment else GL00100.ACTNUMBR_1 end
and GLTrlx.ACTNUMBR_2 =  case when @Seg = 2 then IV40700.Location_Segment else GL00100.ACTNUMBR_2 end
and GLTrlx.ACTNUMBR_3 =  case when @Seg = 3 then IV40700.Location_Segment else GL00100.ACTNUMBR_3 end
and GLTrlx.ACTNUMBR_4 =  case when @Seg = 4 then IV40700.Location_Segment else GL00100.ACTNUMBR_4 end
join GL00105 on GL00100.ACTINDX = GL00105.ACTINDX
join GL00105 GL00105_2 on GLTrlx.ACTINDX = GL00105_2.ACTINDX

WHERE
     (POP30300.GLPOSTDT Between '2006-05-01' And '2006-05-31') AND
     (GLTrlx.ACTNUMBR_2 Like '6100%') AND
     (GLTrlx.ACTNUMBR_3 + GLTrlx.ACTNUMBR_4 In ('120000','190000','220000','227000','210000'))

ORDER BY POP30310.POPRCTNM

 

[barbola]

Oh okay our location segment is Segment1 but it is for the Company, and the same for every account. The query gives me no results and now I see IV40700 and is not related to our inventory sites.

We have alot of Sites, one for each division, but when I look at the Site entry screen it says AccountSegment1.
We use Segment3 as our "division" and we have a Site for each division as well. Where do I enter that Segment3 is the location code?

If I can change that then maybe I can get the query to work.

What is GLTrlx? What is GL00105_2?

thanks.

 

[RobertC22]

Inventory Site Segement
The Segment used to determine sites is in the Inventory Setup window.
(Tools --> Setup --> inventory --> Inventory Control)
If the field 'Segment ID for Sites' is locked, use sql to to update the ACSGFLOC field in IV40100.
Be aware that this may affect your SOP gl accounts.

Once this has been done, when you view your Inventory Sites, the 'Account Segment ID:' should show the name of your third segment.
Use the lookup to set appropriate values.


What is GLTrlx and GL00105_2?

As the GL00100 and GL00105 tables are listed more than once in the FROM clause, each needs to have a unique alias. In this instance the alias to uniquely identify the second occurrence of these tables are GLTrlx and GL00105_2.


How does it work?
Firstly we get the Inventory Segment from the Inventory Control setup.

The INVINDX field from PO receipt line POP30310 (alias POP30310) looks up the original Account index in GL0010 (alias GL00100).
Also the LOCNCODE (location code) on the POP30310 table links to the Inventory site (IV40700) table so that we have the 'Site Segment' value ready.

From the first GL00100 table, link to the second GL00100 (alias GLTrlx) matching on each segemtn, except for the Site ID Segment, where we grab the Site Id value from IV40700 instead. (Hence Trlx short for translated)

In both cases the GL00100 table is linked to the GL00105 to display allow quick display of Full Account number as contained in the ACTNUMST field.



Though I have a question - if you have not already setup the Sites, how is it that the GL accounts in the PO distribution are different to the GL account specified on the PO Receipting Lines? Are the GL distributions accounts being modified before posting the PO receipt?

------
Robert

 

[barbola]

Yes, the GL accounts get modified to code to the correct accounting division (Segment3).

If the PO is for Supplies, and the Site is SITE99, then the user needs to change the division on the GL account...

wait a minute....you mean if I change the site segment to "3" would that make these allocations automatically go to the correct division???? If so, this is HUGE!

thanks.

 

[RobertC22]

barbola,

Yes, Site Segment substitution can occurr automatically once you have setup the values in the Inventory Sites.
When entering the PO / receipt It is likely to show the default account, but will be substituted when it posts to the GL. Suggest you trial it out in a 'test company' to check it works as you hope.

I could not find anything in the Purchase Order Processing Manual on site segment substitution, but the following MBS KB article confirms it.

https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;852215

Alternatively refer to the Inventory Control manual (PDF file) and search for "Site Segment"


------
Robert

 

[barbola]

WOW that is awesome. My Accounts Payable people are gonna love this!!! We will still have some overrides, and I can see having a gazillion sites set up, but Yes I will definitely play with this in our Test company!

I still can't get the SQL statement to work though. I guess because historically the site segment isn't linked that way. It's okay though I will use the two queries I set up earlier.

Thanks for all your help everyone.

 

[jazgeek]

Barb, try it this way

SELECT TOP 100 PERCENT dbo.POP30300.VENDORID, dbo.POP30310.RCPTLNNM, dbo.POP30390.ACTINDX, dbo.POP30310.POPRCTNM,
dbo.POP30310.ITEMNMBR, dbo.POP30310.UOFM, dbo.POP30310.EXTDCOST / dbo.POP30310.UNITCOST AS UNITS, dbo.POP30310.UNITCOST,
dbo.POP30310.EXTDCOST, dbo.IV00101.ITMCLSCD, dbo.POP30300.GLPOSTDT, dbo.GL00100.ACTNUMBR_2, dbo.GL00100.ACTNUMBR_3,
dbo.GL00100.ACTNUMBR_4
FROM dbo.GL00100 INNER JOIN
dbo.POP30390 ON dbo.GL00100.ACTINDX = dbo.POP30390.ACTINDX INNER JOIN
dbo.POP30300 INNER JOIN
dbo.POP30310 ON dbo.POP30300.POPRCTNM = dbo.POP30310.POPRCTNM INNER JOIN
dbo.IV00101 ON dbo.POP30310.ITEMNMBR = dbo.IV00101.ITEMNMBR ON dbo.POP30390.POPRCTNM = dbo.POP30310.POPRCTNM AND
dbo.POP30390.SEQNUMBR = dbo.POP30310.RCPTLNNM
WHERE
(POP30300.GLPOSTDT Between '5/1/6' And '5/31/6') AND
(GL00100.ACTNUMBR_2 Like '6100%') AND
(ACTNUMBR_3+ACTNUMBR_4 In ('120000','190000','220000','227000','210000'))

ORDER BY POP30310.POPRCTNM

-----------
and they wonder why they call it Great Pains!

jaz

 

[barbola]

While both tables use line sequence numbers, they are not related fields so that join doesn't work.

But hey, guess what? I got Robert's query to work in the test company after changing the site segment. It gave me zero records the first time because there was not data for the date range. I will load the Test company with the live data and test it again.

Thanks!