Home » SQL & PL/SQL » SQL & PL/SQL » Please help in converting a script to query for getting Net Entitlement (merged)
Please help in converting a script to query for getting Net Entitlement (merged) [message #359834] Tue, 18 November 2008 05:45 Go to next message
Akash83
Messages: 9
Registered: November 2008
Location: India
Junior Member
Hi,

I need your help.

I have written a pl/sql script for getting the Net_Entitlement of different accrual plans, an employee is having.

IF plan_REC.accrual_plan='US Vacation Plan' THEN
per_accrual_calc_functions.Get_Net_Accrual
(
p_assignment_id => assignment_rec.assignment_id,
p_plan_id => plan_REC.accrual_plan_id,
p_payroll_id => assignment_rec.payroll_id,
p_business_group_id => assignment_rec.business_group_id,
p_assignment_action_id => p_assignment_action_id,
p_calculation_date => sysdate,
p_accrual_start_date => p_accrual_start_date,
p_accrual_latest_balance => p_accrual_latest_balance,
P_Calling_Point => P_Calling_Point,
P_Start_Date => l_BEFORE_start_date,
P_End_Date => l_BEFORE_end_date,
P_Accrual_End_date => l_BEFORE_accrual_end_date,
P_Accrual => l_BEFORE_accrual,
P_Net_Entitlement => Net_Entitlement_Vacation
);
like this i am checking for 5 accrual plans within loop.

But now i want to change everything to a query. I think we can do this using DECODE. Can you people suggest me how to get do it.

[Updated on: Tue, 18 November 2008 05:51]

Report message to a moderator

Re: Need help in converting a script to query [message #359835 is a reply to message #359834] Tue, 18 November 2008 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Insufficient data to compute.

Regards
Michel
Re: Need help in converting a script to query [message #359842 is a reply to message #359835] Tue, 18 November 2008 05:56 Go to previous messageGo to next message
Akash83
Messages: 9
Registered: November 2008
Location: India
Junior Member
Actually instead od IF condition I want to use DECODE but I have doubt with the per_accrual_calc_functions.get_net_accrual() functionality.

Can I give it as

DECODE( pap.accrual_plan_name,'US Vacation Plan' ,
(per_accrual_calc_functions.Get_Net_Accrual
(
paaf.assignment_id ,
pap.accrual_plan_id,
paaf.payroll_id,
paaf.business_group_id,
-1,
sysdate,
NULL,
NULL,
'FRM'
)
), NULL) in select statement.

thanks in advance.

If I give in this way will this function return the 'Net_Entitlement' of the particular accrual plan for the particular employee.
Can a function be called within DECODE [message #359859 is a reply to message #359834] Tue, 18 November 2008 06:18 Go to previous messageGo to next message
Akash83
Messages: 9
Registered: November 2008
Location: India
Junior Member
Hi,

Can we call a function within DECODE.

My case is, I have given the below DECODE in SELECT statement.

DECODE( pap.accrual_plan_name,'US Vacation Plan' ,
(per_accrual_calc_functions.Get_Net_Accrual
(
paaf.assignment_id ,
pap.accrual_plan_id,
paaf.payroll_id,
paaf.business_group_id,
-1,
sysdate,
NULL,
NULL,
'FRM'
)
), NULL)

But it is throwing error.

Please help in this.

Re: Can a function be called within DECODE [message #359860 is a reply to message #359859] Tue, 18 November 2008 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No need to create a new topic for the same question.

Regards
Michel
Re: Need help in converting a script to query [message #359863 is a reply to message #359842] Tue, 18 November 2008 06:25 Go to previous messageGo to next message
Akash83
Messages: 9
Registered: November 2008
Location: India
Junior Member
Hi Michel,

Is my data clear now

Please reply
Re: Need help in converting a script to query [message #359865 is a reply to message #359863] Tue, 18 November 2008 06:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's so far from clear, that it couldn't see clear on a cloudless day, with binoculars, from the top of a tall tower.

In answer to the only coherrent question you've asked: Yes, you can use functions from within Decode statements.
Re: Can a function be called within DECODE [message #359867 is a reply to message #359859] Tue, 18 November 2008 06:40 Go to previous messageGo to next message
mail2das.ashok
Messages: 10
Registered: May 2008
Junior Member
Yes, u can call a function in decode statement
Re: Can a function be called within DECODE [message #359870 is a reply to message #359867] Tue, 18 November 2008 06:48 Go to previous messageGo to next message
Akash83
Messages: 9
Registered: November 2008
Location: India
Junior Member
Thanks...
I have changed and it is working fine...

I think i didnt explained the issue clearly.As it is a more than 1000 line script i just explained the part. I am new to this forum and hope i will improve
Re: Can a function be called within DECODE [message #359880 is a reply to message #359870] Tue, 18 November 2008 07:38 Go to previous message
mail2das.ashok
Messages: 10
Registered: May 2008
Junior Member
definitely
Previous Topic: Procedure
Next Topic: positional notation and rownum
Goto Forum:
  


Current Time: Sun Dec 04 21:00:54 CST 2016

Total time taken to generate the page: 0.25449 seconds