1 PACKAGE BODY pa_bill_schedule AS
2 /*$Header: PAXBILAB.pls 120.1 2005/08/19 17:09:08 mwasowic noship $ */
3
4 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 PROCEDURE get_computed_bill_rate (
7 p_array_size IN NUMBER,
8 p_bill_rate_sch_id IN pa_plsql_datatypes.IdTabTyp,
9 p_expenditure_item_id IN pa_plsql_datatypes.IdTabTyp,
10 p_exp_sys_linkage IN pa_plsql_datatypes.Char30TabTyp,
11 p_expenditure_type IN pa_plsql_datatypes.Char30TabTyp,
12 p_expenditure_item_date IN pa_plsql_datatypes.DateTabTyp,
13 p_fixed_date IN pa_plsql_datatypes.DateTabTyp,
14 p_quantity IN pa_plsql_datatypes.NumTabTyp,
15 p_incurred_by_person_id IN pa_plsql_datatypes.IdTabTyp,
16 p_non_labor_resource IN pa_plsql_datatypes.Char20TabTyp,
17 p_base_curr IN pa_plsql_datatypes.Char15TabTyp,
18 p_base_amt IN pa_plsql_datatypes.NumTabTyp,
19 p_exp_uom IN pa_plsql_datatypes.Char30TabTyp,
20 p_compute_flag IN OUT NOCOPY pa_plsql_datatypes.Char1TabTyp,
21 x_error_code IN OUT NOCOPY pa_plsql_datatypes.Char30TabTyp,
22 x_reject_cnt OUT NOCOPY number, --File.Sql.39 bug 4440895
23 x_computed_rate OUT NOCOPY pa_plsql_datatypes.NumTabTyp,
24 x_computed_markup OUT NOCOPY pa_plsql_datatypes.NumTabTyp,
25 x_computed_currency OUT NOCOPY pa_plsql_datatypes.Char15TabTyp,
26 x_computed_amount OUT NOCOPY pa_plsql_datatypes.NumTabTyp,
27 x_tp_job_id OUT NOCOPY pa_plsql_datatypes.IdTabtyp,
28 x_error_stage OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
29 )
30 IS
31 /*-----------------------------------------------------------------------------
32 declare all the variables.
33 ----------------------------------------------------------------------------*/
34 bill_rate pa_bill_rates_all.rate%TYPE;
35 markup pa_bill_rates_all.markup_percentage%TYPE;
36 bill_ous pa_bill_rates_all.org_id%TYPE;
37 v_job_id pa_bill_rates_all.job_id%TYPE;
38 v_curr_code pa_bill_rates_all.rate_currency_code%TYPE; -- Added this column for MCB2
39
40
41 BEGIN
42 /*-----------------------------------------------------------------------------
43 loop until all records are processed
44 ----------------------------------------------------------------------------*/
45 IF g1_debug_mode = 'Y' THEN
46 PA_MCB_INVOICE_PKG.log_message('Entering pa_bill_schedule.get_computed_bill_rate Id :');
47 END IF;
48 FOR i in 1..p_array_size LOOP
49
50 -- CBGA fix
51
52 x_tp_job_id(i) := NULL;
53
54 /* Selecting currency from bill rate table in all the selects for MCB2 */
55 IF p_compute_flag(i) = 'Y' THEN
56 BEGIN
57 IF ( p_exp_sys_linkage(i) NOT IN ( 'ST','OT') ) THEN
58
59 /*Bug# 2036624:Commented for bug# 2036624.Now when non_labor_resource is null
60 it will be trapped in exception part and the bill rate will be calculated */
61
62 /* AND ( p_non_labor_resource(i) IS NOT NULL ) ) THEN Bug# 2036624 */
63 BEGIN
64 /* Non Labor Resource Based Bill Rate/Markup */
65 SELECT b.rate,
66 b.markup_percentage,
67 b.org_id,
68 b.rate_currency_code
69 INTO bill_rate,
70 markup,
71 bill_ous,
72 v_curr_code
73 FROM pa_bill_rates_all b
74 WHERE
75 b.bill_rate_sch_id = p_bill_rate_sch_id(i)
76 AND b.expenditure_type = p_expenditure_type(i)
77 AND b.non_labor_resource = p_non_labor_resource(i)
78 AND nvl(p_fixed_date(i), p_expenditure_item_date(i)) BETWEEN
79 b.start_date_active AND nvl(b.end_date_active,p_expenditure_item_date(i));
80 EXCEPTION
81 WHEN no_data_found then
82
83 /* Expenditure Type Based Non Labor Based Bill Rate/Markup */
84 /* Bug#3019564 - The following select statement is raised a too may row exception.
85 Fix : Add the non_labor_resource is null for the following sql */
86
87 SELECT b.rate,
88 b.markup_percentage,
89 b.org_id,
90 b.rate_currency_code
91 INTO bill_rate,
92 markup,
93 bill_ous,
94 v_curr_code
95 FROM pa_bill_rates_all b
96 WHERE
97 b.bill_rate_sch_id = p_bill_rate_sch_id(i)
98 AND b.expenditure_type = p_expenditure_type(i)
99 AND b.non_labor_resource IS NULL
100 AND nvl(p_fixed_date(i), p_expenditure_item_date(i)) BETWEEN
101 b.start_date_active AND nvl(b.end_date_active,p_expenditure_item_date(i));
102 END;
103 ELSIF ( p_exp_sys_linkage(i) IN ( 'ST','OT') ) THEN
104 /* Job Based Labor Bill Rate-Markup */
105 BEGIN
106
107 /* CBGA changes : select the column job_id */
108
109
110 SELECT b.rate,
111 b.markup_percentage,
112 b.org_id,
113 b.job_id,
114 b.rate_currency_code
115 INTO bill_rate,
116 markup,
117 bill_ous,
118 v_job_id,
119 v_curr_code
120 FROM pa_bill_rates_all b,
121 per_assignments_f pa,
122 pa_std_bill_rate_schedules_all brs
123 WHERE
124 pa.primary_flag = 'Y'
125 -- AND pa.assignment_type = 'E' /* bug 2911451 */
126 AND pa.assignment_type IN ('E','C') -- Modified for CWK impact
127 AND pa.person_id = p_incurred_by_person_id(i)
128 AND p_expenditure_item_date(i) BETWEEN
129 pa.effective_start_date AND
130 pa.effective_end_date /* Removed the nvl on end_date as part of Assignment Type Validation changes for bug 2911451 */
131 AND b.bill_rate_sch_id = p_bill_rate_sch_id(i)
132 AND b.bill_rate_sch_id = brs.bill_rate_sch_id
133 AND b.job_id = PA_cross_Business_Grp.IsMappedToJob(pa.job_id, brs.job_group_id)
134 AND nvl(p_fixed_date(i),p_expenditure_item_date(i)) BETWEEN
135 b.start_date_active AND nvl(b.end_date_active,p_expenditure_item_date(i));
136
137 x_tp_job_id(I) := v_job_id;
138
139
140 EXCEPTION
141 WHEN no_data_found THEN
142 /* Employee Based Labor Bill Rate/Markup */
143 SELECT b.rate,
144 b.markup_percentage,
145 b.org_id,
146 b.rate_currency_code
147 INTO bill_rate,
148 markup,
149 bill_ous,
150 v_curr_code
151 FROM pa_bill_rates_all b
152 WHERE
153 b.bill_rate_sch_id = p_bill_rate_sch_id(i)
154 AND b.person_id = p_incurred_by_person_id(i)
155 AND nvl(p_fixed_date(i),p_expenditure_item_date(i)) BETWEEN
156 b.start_date_active AND nvl(b.end_date_active,p_expenditure_item_date(i));
157 END;
158 END IF;
159
160 /* Added for MCB2 Assigning the rate currency */
161 x_computed_currency(i) := v_curr_code;
162
163 IF markup is not null THEN
164 x_computed_currency(i) := p_base_curr(i);
165 x_computed_markup(i) := markup;
166 x_computed_rate(i) := null;
167 /*
168 select pa_currency.round_trans_curr_amt(x_computed_markup(i)*p_base_amt(i)/100,
169 x_computed_currency(i))
170 into x_computed_amount(i)
171 from dual;
172 */
173 /* x_computed_amount(i) := pa_currency.round_trans_currency_amt(x_computed_markup(i)*p_base_amt(i)/100,x_computed_currency(i));
174 commented for bug 3697180 */
175
176 /* Added the below for bug 3697180 */
177
178 x_computed_amount(i) :=
179 pa_currency.round_trans_currency_amt((100 + x_computed_markup(i))*p_base_amt(i)/100,x_computed_currency(i));
180 ELSE
181 /* Added for MCB2 : Commented the below select because , now selecting rate currency can be diffrent from PFC */
182 /* select gsb.currency_code
183 into x_computed_currency(i)
184 from pa_implementations_all imp,
185 gl_sets_of_books gsb
186 where imp.org_id = bill_ous
187 and imp.set_of_books_id = gsb.set_of_books_id; */
188 x_computed_markup(i) := null;
189 x_computed_rate(i) := bill_rate;
190 x_computed_amount(i) := pa_currency.round_trans_currency_amt(p_quantity(i)*bill_rate,x_computed_currency(i));
191 IF g1_debug_mode = 'Y' THEN
192 PA_MCB_INVOICE_PKG.log_message('get_computed_bill_rate: ' || 'Leaving pa_bill_schedule.x_computed_markup :'||x_computed_markup(i));
193 PA_MCB_INVOICE_PKG.log_message('get_computed_bill_rate: ' || 'Leaving pa_bill_schedule.x_computed_rate :'||x_computed_rate(i));
194 PA_MCB_INVOICE_PKG.log_message('get_computed_bill_rate: ' || 'Leaving pa_bill_schedule.x_computed_amount :'||x_computed_amount(i));
195 END IF;
196 END IF;
197 EXCEPTION
198 when no_data_found then
199 /*x_error_code(i) := 'NO_BILL_SCH';commented for bug 3118724*/
200 x_error_code(i) := 'NO_BILL_RATE';
201 x_reject_cnt := x_reject_cnt + 1;
202 END;
203 END IF;
204 END LOOP;
205 END get_computed_bill_rate;
206
207 END pa_bill_schedule;
[Home] [Help]