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