Understand PurchTableAllVersions with VendPurchOrderJour

(0) Share

Report

Posted on by junior AX

1,552

Hi,

i want to build an entity where i can see all versions of confirmed POs.

So i built a data entity with VendPurchOrderJour as the root source, outer joined with View PurchTableAllversions(relation is VendPurchOrderJour.PurchTableVersion= PurchTableAllversions.PurchTableVersionRecId) and this is outer joined with View PurchLineAllversions(Relation on PurchTableVersionRecId field on both data sources)

My questions are:

1. what should be the entity key for this? and why? as there is no primary key on VendPurchOrderJour

2. should the relation between PurchTableAllversions and PurchLineAllversions be inner join instead of outer join? or it wouldn't make a difference?

3. in view PurchTableAllversions there is archived and not archived queries. where the range for each one is based on isArchived and proforma fields, how is this archived field filled? what makes a PO archived true? and what is this proforma field? i thought whenever a new version of PO confirmation is done the previous ones become archived but i noticed a PO with 2 versions only and these two versions are archived. So i got confused

I have the same question (0)

All responses (20)

Answers (2)

Sort by

Gunjan Bhattachayya 35,429 on at

Like (0)

Report
Copy link

Link copied!

Hi junior AX,

I am assuming you will use this entity for reading data. Could you please elaborate on what data you need? Based on that it will be easier to understand if you will need the PurchTableAllVersions and PurchLineAllVersions views/

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report
Copy link

Link copied!

Hi Gunjan,

yes i need it for export. I want to get all confirmed versions of POs with all it's lines values

Was this reply helpful? Yes No
Verified answer

Sergei Minozhenko 23,097 on at

Like (1)

Report
Copy link

Link copied!

Hi Junior Ax,

1. Entity key should identify each record in entity, in most cases it's a combination of fields from the unique index from different data sources joined by inner\outer join with 1-n relation. In your one confirmation should generate one version, so it's 1-1 relation between VendPurchOrderJour and PurchTableAllversions source, but there is 1-n relation between PurchTableAllversions and PurchLineAllversions.

Since PurchTableAllversions and PurchLineAllversions are views, it's more complicated to find correct unique fields, but i would say it should

PurchTableAllversions.PurchTableVersionRecId

PurchLineAllVersions.InventTransId

2. Outer join relation will select all records from parent data source even if there are no records in a child data source, inner join will select only records from a parent data source that have related in a child data source. So what to choose depends on your requirement, but i believe in case of PurchTableHistory and PurchLineHistory you can use inner join as it should not be possible to confirm PO without lines.

www.diffen.com/.../Inner_Join_vs_Outer_Join

3. Previous version is archived when a new version of the order is created (for example change requested after WF approval) or confirmed.

Was this reply helpful? Yes No
Verified answer

Gunjan Bhattachayya 35,429 on at

Like (1)

Report
Copy link

Link copied!

Hi junior AX,

1. Regarding the key, you might want to take a look at the data. I would go with a combination of the following fields -

a. VendPurchOrderJour\RecId

b. VendPurchOrderJour\PurchTableVersion

c. PurchLineAllVersions\InventTransId

I am choosing these fields because there is a 1:1 relation between VendPurchOrderJour and PurchTableAllVersions. There is a 1:n relation between PurchTableAllVersions and PurchLineAllVersions. For every record of VendPurchOrderJour there would be one record of PurchTableAllVersions and for each record of PurchTableAllVersions, there would be multiple records of PurchLineAllVersions.

2. I would go for inner join between all three. VendPurchOrderJour linked to PurchTableAllVersions, PurchLineAllVersions linked to PurchTableAllVersions. There could be records in these views even without confirmations. I don't think you are trying to fetch those. You need those linked to a purchase order confirmation.

3. The field Proforma is filled while generating a Proforma confirmation -

IsArchieved is populated when creating a new version of the PO. I don't think you need to worry about these fields as it should be taken care of by the view "PurchTableArchivedVersions" view.

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report
Copy link

Link copied!

Hi Sergie and Gunjan,

Thanks alot both.

1. The entity key i used before asking the question is PurchId, PurchOrderDocNum and LineNumber. And till now i didn't get any errors. Can you suggest why would this entity key be wrong? i thought the combination of these is definitely unique, as each purchId can have different PurchOrderDocNum and it can have different line numbers for the same purchId and PurchOrderDocNum. So is that wrong?

2. So both of you agree that i should use InventTransId and (PurchTableVersionRecId OR PurchTableVersion).

Sergie, are you saying that if the relation is 1:1 i should ignore putting an entity key for it? and look for tables with 1:n?

and Gunjan, why did u suggest to put RecId of VendPurchOrderJo, are you saying if relation is 1:1 i need to put the RecId always?

3. as for inner and outer joins. So based on you explanation and my understanding for joins and please correct me if I'm wrong

-- for VendPurchOrderJo and PurchTableAllVersions: i don't think it will matter if put inner join or outer join because VendPurchOrderJo is the root (parent) so if I put inner join our outer join with PurchTableAllVersions, i'm still going to get records that exist in both VendPurchOrderJo and PurchTableAllVersions(as i can't have a record in VendPurchOrderJo that doesn't exist in PurchTableAllVersions, right?. However if PurchTableAllVersions is the parent, then i should definitely use inner as there could be records in PurchTableAllVersions without confirmation. Am i right? but i think it makes sense to use inner join.

-- For PurchTableAllVersions and PurchLineAllVersions: I can create a purchOrder without lines but as sergie said i won't be able to confirm it without lines. So using inner or outer won't make a difference here. Am i right? But i'll prefer to use inner join.

4. So you are saying changing the status of the PO to anything other than confirmed will make it archived, right?

5. from a business point of view what does Proforma mean?

Was this reply helpful? Yes No
Suggested answer

Gunjan Bhattachayya 35,429 on at

Like (1)

Report
Copy link

Link copied!

Hi junior AX,

1. If you are using PurchId, PurchDocNum and LineNumber, it will work in case there are only single confirmations. In case of multiple confirmations the line number will repeat. How will you identify which version the lines belong to? You have to check for the unique key for the PurchLIneAllVersions view.

2. PurchTableVersionRecId is unique for all versions and is the key through which PurchTableAllVersions and PurchLineAllVersions are linked. A combination of PurchTableVersionRecId and InventTransId will be unique for each record on the entity. I am not suggesting that you put the Recid always in case of 1:1 relations. The reason I include VendPurchOrderJour\RecId is because the combination of VendPurchOrderJour\RecId and PurchTableVersionRecId is unique for the header records.

3. This will be clear to you if you change a field (qty on the PO lines for an example) and don't confirm the order. There won't be a confirmation journal as you haven't confirmed the order as yet. But you can check if you can see records in PurchTableAllVersions and PuchLineAllVersions. If you can see these records, then you need an inner join since you need the records which are linked to a PO confirmation.

4. All the old confirmations are marked as Archived. Let's you had a confirmation PO00001-1 and PO00001-2, when you generate PO00001-2, the old version will be marked as archived. You can find references on this field to check where it is being modified.

5. Proforma is when you don't want to do a posting, but are looking to provide a view of how the order would look. Proforma confirmation has no implications on the system, but generate a report showing how the confirmation would look.

Was this reply helpful? Yes No
Sergei Minozhenko 23,097 on at

Like (1)

Report
Copy link

Link copied!

Hi Junior Ax,

1. It's not wrong, you just asked how to identify the key and the most common way is to use unique index fields from all data sources joined by inner\outer join. Or course if there are multiple unique indexes per table or you sure if the combination is unique you can use it as well

2. If the relation is 1:1 it doesn't matter if you take unique fields from both tables or form one as you always have 1 record as a result of joining

3. It's correct, but you need to test it if all your processes are working fine and you get correct data through entity.

4. No, i'm saying that in some cases records in version table can be generated without confirmation process, like requesting change after PO is approved. If the order is confirmed and you are doing the next steps in processing, new versions should be not generated

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report
Copy link

Link copied!

Hi Gunjan,

1. PurchOrderDocNum should specify which version the line belongs to

Example:

1st confirmation:

Po1 Po1Doc1 Line1

Po1 Po1Doc1 Line2

2nd confirmation:

Po1 Po1Doc2 Line1

Po1 Po1Doc2 Line2

Doesn't that solve the issue you mentioned?

2. So if i don't put the RecId, am i going to get any errors?

3. I think got what you are saying, but what i mean is there is no way a record will be created in vendPurchOrderJo without confirmation and there can't be a record in vendPurchOrderJo without having a record in PurchTableAllVersions. So here using inner join/outer will get me the same correct result since vendPurchOrderJo is the parent. I will always get only records linked to a PO confirmation. Correct?

4. yeah different versions will make the older version archived and i think the latest version will be archived as well (not sure) if for example I approved the PO so the last confirmed version will be archived.

5. Thank you

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report
Copy link

Link copied!

Hi Sergie,

1. So there is nothing better than something as long as i get the unique value.

2. ok so you are saying, for example if only had VendPurchOrderJo and PurchTableAllVersions only in the entity then i should at least put one record from one of them. But if i had three tables: VendPurchOrderJo ,PurchTableAllVersions and PurchLineAllversions i should put one field from any of the tables that have 1:1 relation and a field from the table where 1:n relation occurred is a must in the entity key

3. Thank you

4. new versions should be not generated, but the last version of confirmed PO should be updated to archived true , if i approved the PO..right?

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,429 on at

Like (0)

Report
Copy link

Link copied!

Hi junior AX,

1. I think this might work as well. Since you can check with the data, you can go ahead with this entity key.

2. No, you won't get any errors if you don't mention the RecId. The entity will still work without it.

3. Yes. the inner/outer join won't matter as such in this case. You can use either one. I suggested inner join as the records will always be present and if you look at the confirmation form, they are linked using inner join as well.

4. You can give this a try using a few scenarios and check the versions that get archived. In any case, I don't think that will have an impact on the entity since you are getting all the values anyway.

Was this reply helpful? Yes No