1.Find Application Id of related ‘Cancellation - Top Ups’ or ‘Not Taken Up - Top Ups’ application:
Select S_OPTY.NAME where S_OPTY.PAR_OPTY_ID = [Original Application Id] and S_OPTY.X_SALES_METHOD_ID identifies S_SALES_METHOD.NAME = either ‘Cancellation - Top Ups’ or ‘Not Taken Up - Top Ups’.
2.Select from PR_MONEY_BFN:
Select record from PR_MONEY_BFN where:
PR_MONEY_BFN.APPLICATION_NO = S_OPTY.NAME identified above (ie for the actual ‘Cancellation - Top Ups’ or ‘Not Taken Up - Top Ups’ application); and
PR_MONEY_BFN.BUS_FUNC_CLASS = ‘NTUT’ or ‘CANT’; and
PR_MONEY_BFN.BUS_FUNC_STATUS = 80.
Your requirement is not clear. You have two fields called NAME; one on S_OPTY and one on S_SALES_METHOD. in requirement 2 You have said you want the one from S_OPTY (which you already have)
Select
M.*,
O.*
FROM
S_OPTY O
JOIN
PR_MONEY_BFN M on M.APLLICATION_NO = O.NAME
WHERE
O.PAR_OPTY_ID = @ApplicationID
if you want the one from S_SALES_METHOD
Select
M.*,
O.*
FROM
S_OPTY O
JOIN
S_SALES_METHOD S on S.X_SALES_METHOD_ID = O.X_SALES_METHOD_ID
PR_MONEY_BFN M on M.APLLICATION_NO = O.NAME
WHERE
O.PAR_OPTY_ID = @ApplicationID
I am making the assumption that the join field is called X_SALES_METHOD_ID in both tables