1 PACKAGE BODY pay_jp_get_month_balance_pkg as
2 /* $Header: pyjpgmbl.pkb 115.1 99/10/08 06:54:33 porting ship $ */
3 ----------------------------------------------------------------------------------
4 -- --
5 -- GET_MONTH_BALANCE --
6 -- sum run results for a given balance for a calendar month
7 -- parameters: business_group_id Formula context
8 -- assignment_id Formula context
9 -- balance name parameter to select balance feedS
10 -- months_prior how many months to go back
11 -- --
12 ----------------------------------------------------------------------------------
13 FUNCTION get_month_balance(
14 p_business_group_id NUMBER,
15 p_assignment_id NUMBER,
16 p_balance_name VARCHAR2,
17 p_months_prior NUMBER)
18 RETURN NUMBER IS
19 l_effective_date date;
20 l_balance_type_id number;
21 l_end_month_date date;
22 l_start_month_date date;
23 l_balance number;
24 BEGIN
25 select balance_type_id
26 into l_balance_type_id
27 from pay_balance_types
28 where balance_name = p_balance_name
29 and ((business_group_id is null and legislation_code = 'JP')
30 or business_group_id = p_business_group_id);
31
32 select effective_date,
33 add_months(last_day(effective_date),(-1 * abs(p_months_prior))),
34 add_months(last_day(effective_date), (-1 + (-1 * abs(p_months_prior)))) + 1
35 into l_effective_date,
36 l_end_month_date,
37 l_start_month_date
38 from fnd_sessions
39 where session_id = userenv('sessionid');
40
41 BEGIN
42 SELECT NVL(SUM(TARGET.result_value * FEED.scale),0)
43 INTO l_balance
44 FROM
45 pay_run_result_values TARGET,
46 pay_balance_feeds_f FEED,
47 pay_run_results RR,
48 pay_payroll_actions PACT,
49 pay_assignment_actions ASSACT
50 WHERE ASSACT.assignment_id = p_assignment_id
51 AND ASSACT.payroll_action_id = PACT.payroll_action_id
52 AND PACT.effective_date
53 between l_start_month_date and l_end_month_date
54 AND RR.assignment_action_id = ASSACT.assignment_action_id
55 AND RR.status IN ('P','PA')
56 AND FEED.balance_type_id = l_balance_type_id
57 AND PACT.effective_date
58 between FEED.effective_start_date and FEED.effective_end_date
59 AND TARGET.run_result_id = RR.run_result_id
60 AND TARGET.input_value_id = FEED.input_value_id
61 AND NVL(TARGET.result_value,'0') <> '0';
62 END;
63
64 RETURN l_balance;
65 end get_month_balance;
66 ----------------------------------------------------------------------------------
67 -- --
68 -- GET_MONTH_ADJUSTMENTS
69 -- sum run results for a given balance for a calendar month where the results
70 -- belong to either adjustment or reversal actions.
71 --
72 -- parameters: business_group_id Formula context
73 -- assignment_id Formula context
74 -- balance name parameter to select balance feedS
75 -- months_prior how many months to go back
76 -- --
77 ----------------------------------------------------------------------------------
78 FUNCTION get_month_adjustments(
79 p_business_group_id NUMBER,
80 p_assignment_id NUMBER,
81 p_balance_name VARCHAR2,
82 p_months_prior NUMBER)
83 RETURN NUMBER IS
84 l_effective_date DATE;
85 l_balance_type_id NUMBER;
86 l_end_month_date DATE;
87 l_start_month_date DATE;
88 l_balance NUMBER;
89 BEGIN
90 select balance_type_id
91 into l_balance_type_id
92 from pay_balance_types
93 where balance_name = P_BALANCE_NAME
94 and ((business_group_id is null and legislation_code = 'JP')
95 or business_group_id = p_business_group_id);
96
97 select effective_date,
98 add_months(last_day(effective_date),(-1 * abs(p_months_prior))),
99 add_months(last_day(effective_date), (-1 + (-1 * abs(p_months_prior)))) + 1
100 into l_effective_date,
101 l_end_month_date,
102 l_start_month_date
103 from fnd_sessions
104 where session_id = userenv('sessionid');
105
106 BEGIN
107 SELECT NVL(SUM(TARGET.result_value * FEED.scale),0)
108 INTO l_balance
109 FROM pay_run_result_values TARGET,
110 pay_balance_feeds_f FEED,
111 pay_run_results RR,
112 pay_payroll_actions PACT,
113 pay_assignment_actions ASSACT
114 WHERE ASSACT.assignment_id = p_assignment_id
115 AND ASSACT.payroll_action_id = PACT.payroll_action_id
116 AND PACT.action_type IN ('B','V')
117 AND PACT.effective_date
118 between l_start_month_date and l_end_month_date
119 AND RR.assignment_action_id = ASSACT.assignment_action_id
120 AND RR.status IN ('P','PA')
121 AND FEED.balance_type_id = l_balance_type_id
122 AND PACT.effective_date
123 between FEED.effective_start_date and FEED.effective_end_date
124 AND TARGET.run_result_id = RR.run_result_id
125 AND TARGET.input_value_id = FEED.input_value_id
126 AND NVL(TARGET.result_value,'0') <> '0';
127 END;
128
129 RETURN l_balance;
130 END get_month_adjustments;
131 END pay_jp_get_month_balance_pkg;
[Home] [Help]