Approval procedure to go request to required FirmPartner

Can you explain your use-case in a little bit more detail? The FirmPartner itself is no Person that would be able to decide a request.

In addition, what version are you using?

we are in 8.1 currently.
so we have a requirement where team will raise a request for third party account creation from IT shop.

so request will go to business contact, user will select any of the firm from IT shop.
Then request will go all the UID_Person's under that UID_FirmPartner.

I wrote the below query let me know if there is any changes?

select distinct
ISNULL( pe.NES_UID_EscalationPerson, pe.UID_Person) as uid_person , dbo.QER_FGIPWORulerOrigin(hho.XObjectkey) as UID_PWORulerOrigin
from PersonWantsOrg PWO
join person po on pwo.uid_personordered = po.uid_Person
join FirmPartner fp on fp.UID_FirmPartner = po.UID_FirmPartner
join person pe on pe.uid_Person = po.uid_Person
join helperHeadperson hho on hho.uid_person = pe.uid_Person
and hho.XOrigin > 0
where pwo.uid_personwantsorg = @uid_personwantsorg

Where do you store the FirmPartner selected? The query you have posted will not work 'cause you just joined the same person from alias pe to the one from alias po which is the PersonOrdered from the request.

yeah so my question is how can I get whose is the Business contacts(approvers)from FirmPartner and person table.
For groups, products, profiles...etc we can get it from AccProduct using some columns.
Now how can i write this query to get who is the approver from firmpartner table?

yeah so my question is how can I get whose is the Business contacts(approvers)from FirmPartner and person table.
For groups, products, profiles...etc we can get it from AccProduct using some columns.
Now how can i write this query to get who is the approver from firmpartner table?

OOTB the only relation between person a FirmPartner is the FK Person.UID_FirmPartner. The definition of specific approvers for firm partners like you wanna implement is nothing that comes ootb and the storage of that information needs to be customized by you as well.