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