[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_WCB_FF_FUNCTIONS
Source
1 PACKAGE BODY pay_ca_wcb_ff_functions AS
2 /* $Header: pycawcfc.pkb 120.0 2005/05/29 03:54:26 appldev noship $ */
3 ----------------------------------------------------------------------
4 -- FUNCTION GET_RATE_ID_FOR_WCB_CODE
5 ----------------------------------------------------------------------
6 FUNCTION get_rate_id_for_wcb_code (p_account_number varchar2
7 ,p_code varchar2
8 ,p_jurisdiction varchar2)
9 RETURN NUMBER IS
10 --
11 l_rate_id number;
12 --
13 CURSOR get_rate_id_for_wcb_code(p_account_number varchar2
14 ,p_code varchar2
15 ,p_jurisdiction varchar2) IS
16 --
17 select wr.rate_id
18 from pay_wci_accounts_v wav
19 , pay_wci_rates wr
20 where wav.account_id = wr.account_id
21 and wav.province = p_jurisdiction
22 and wav.account_number = p_account_number
23 and wr.code = p_code;
24 --
25 BEGIN
26 --
27 open get_rate_id_for_wcb_code(p_account_number, p_code, p_jurisdiction);
28 fetch get_rate_id_for_wcb_code into l_rate_id;
29 --
30 if get_rate_id_for_wcb_code%NOTFOUND then
31 l_rate_id := 8.8;
32 end if;
33 --
34 close get_rate_id_for_wcb_code;
35 --
36 RETURN l_rate_id;
37 --
38 END get_rate_id_for_wcb_code;
39 --
40 -------------------------------------------------------------------------
41 -- FUNCTION GET_RATE_ID_FOR_JOB
42 -------------------------------------------------------------------------
43 FUNCTION get_rate_id_for_job (p_account_number varchar2
44 ,p_job varchar2
45 ,p_jurisdiction varchar2)
46 RETURN number IS
47 --
48 l_rate_id number;
49 --
50 CURSOR get_rate_id_for_job(p_account_number varchar2
51 ,p_job varchar2
52 ,p_jurisdiction varchar2) IS
53 --
54 select wr.rate_id
55 from pay_wci_accounts_v wav
56 , pay_wci_rates wr
57 , pay_wci_occupations wo
58 , per_jobs pj
59 where wav.account_id = wr.account_id
60 and wr.rate_id = wo.rate_id
61 and wo.job_id = pj.job_id
62 and pj.name = p_job
63 and wav.province = p_jurisdiction
64 and wav.account_number = p_account_number;
65 --
66 BEGIN
67 --
68 open get_rate_id_for_job(p_account_number, p_job, p_jurisdiction);
69 fetch get_rate_id_for_job into l_rate_id;
70 --
71 if get_rate_id_for_job%NOTFOUND then
72 l_rate_id := 9.9;
73 end if;
74 --
75 close get_rate_id_for_job;
76 --
77 RETURN l_rate_id;
78 --
79 END get_rate_id_for_job;
80 -------------------------------------------------------------------------
81 -- FUNCTION GET_WCB_RATE
82 -------------------------------------------------------------------------
83 FUNCTION get_wcb_rate (p_rate_id number) RETURN number IS
84 --
85 l_rate number;
86 --
87 CURSOR get_wcb_rate(p_rate_id number) IS
88 --
89 select nvl(rate,0)
90 from pay_wci_rates
91 where rate_id = p_rate_id;
92 --
93 BEGIN
94 --
95 open get_wcb_rate(p_rate_id);
96 fetch get_wcb_rate into l_rate;
97 close get_wcb_rate;
98 --
99 RETURN l_rate;
100 --
101 END get_wcb_rate;
102 --
103 END pay_ca_wcb_ff_functions;