1 PACKAGE PA_COST_RATE_PUB AUTHID CURRENT_USER AS
2 /* $Header: PAXPCRTS.pls 120.2 2011/03/25 12:06:18 racheruv ship $*/
3
4
5 g_func_curr gl_sets_of_books.currency_Code%TYPE;
6 /*
7 * Contains information for OU level Schedule Assignment.
8 */
9 g_ou_id pa_implementations_all.org_id%TYPE;
10 g_ou_org_labor_sch_rule_id pa_org_labor_sch_rule.org_labor_sch_rule_id%TYPE;
11 g_ou_cost_rate_sch_id pa_std_bill_rate_schedules.bill_rate_sch_id%TYPE;
12 g_ou_labor_costing_rule pa_compensation_rule_sets.compensation_rule_set%TYPE;
13 g_ou_ot_project_id pa_projects_all.project_id%TYPE;
14 g_ou_ot_task_id pa_tasks.task_id%TYPE;
15 g_ou_acct_rate_date_code pa_org_labor_sch_rule.acct_rate_date_code%TYPE;
16 g_ou_acct_rate_type pa_org_labor_sch_rule.acct_rate_type%TYPE;
17 g_ou_acct_exch_rate pa_org_labor_sch_rule.acct_exchange_rate%TYPE;
18
19 /*
20 * Contains information about the last processed record by the api get_labor_rate.
21 */
22 g_rt_calling_module varchar2(50);
23 g_rt_organization_id pa_expenditures_all.incurred_by_organization_id%TYPE;
24 g_rt_cost_rate pa_bill_rates_all.rate%TYPE;
25 g_rt_start_date_active date;
26 g_rt_end_date_active date;
27 g_rt_org_labor_sch_rule_id pa_org_labor_sch_rule.org_labor_sch_rule_id%TYPE;
28 g_rt_costing_rule pa_compensation_rule_sets.compensation_rule_set%TYPE;
29 g_rt_rate_sch_id pa_std_bill_rate_schedules_all.bill_rate_sch_id%TYPE;
30 g_rt_cost_rate_curr_code pa_bill_rates_all.rate_currency_code%TYPE;
31 g_rt_acct_rate_type pa_org_labor_sch_rule.acct_rate_type%TYPE;
32 g_rt_acct_rate_date_code pa_org_labor_sch_rule.acct_rate_date_code%TYPE;
33 g_rt_acct_exch_rate pa_org_labor_sch_rule.acct_exchange_rate%TYPE;
34 g_rt_ot_project_id pa_projects_all.project_id%TYPE;
35 g_rt_ot_task_id pa_tasks.task_id%TYPE;
36 g_rt_err_stage number;
37 g_rt_err_code varchar2(50);
38 g_rt_person_id pa_bill_rates_all.person_id%TYPE;
39 g_rt_job_id pa_bill_rates_all.job_id%TYPE;
40 g_base_hours pa_org_labor_sch_rule.base_hours%TYPE;
41 g_rbc_elem_type_id pa_org_labor_sch_rule.rbc_element_type_id%TYPE;
42 g_rt_sch_type pa_std_bill_rate_schedules_all.schedule_type%TYPE;
43
44
45 -- Start of comments
46 -- API name : get_labor_rate
47 -- Type : Public
48 -- Pre-reqs : None.
49 -- Function : Returns Labor Cost Rate for an Employee.
50 -- Parameters : Person Id, Transaction Date. Organization Id and Job Id are optional.
51 -- IN : p_person_id IN NUMBER Required
52 -- Id of the person for whom the rate is to be found.
53 -- p_txn_date IN DATE Required
54 -- The Date on which the rate is required.
55 -- x_organization_id IN NUMBER Optional
56 -- Organization to which the transaction is charged to.
57 -- x_job_id IN NUMBER Optional
58 -- Job of the person.
59 -- p_org_id IN NUMBER Optional
60 -- Expenditure Org Id of the Transaction .
61 -- Version : Current version 1.0
62 -- Initial version 1.0
63 -- End of comments
64 procedure get_labor_rate ( p_person_id IN per_all_people_f.person_id%TYPE
65 ,p_txn_date IN date
66 ,p_calling_module IN varchar2 default 'STAFFED'
67 ,p_org_id IN pa_expenditures_all.org_id%TYPE default NULL /*2879644*/
68 ,x_job_id IN OUT NOCOPY pa_expenditure_items_all.job_id%TYPE
69 ,x_organization_id IN OUT NOCOPY pa_expenditures_all.incurred_by_organization_id%TYPE
70 ,x_cost_rate OUT NOCOPY pa_bill_rates_all.rate%TYPE
71 ,x_start_date_active OUT NOCOPY date
72 ,x_end_date_active OUT NOCOPY date
73 ,x_org_labor_sch_rule_id OUT NOCOPY pa_org_labor_sch_rule.org_labor_sch_rule_id%TYPE
74 ,x_costing_rule OUT NOCOPY pa_compensation_rule_sets.compensation_rule_set%TYPE
75 ,x_rate_sch_id OUT NOCOPY pa_std_bill_rate_schedules_all.bill_rate_sch_id%TYPE
76 ,x_cost_rate_curr_code OUT NOCOPY gl_sets_of_books.currency_code%TYPE
77 ,x_acct_rate_type OUT NOCOPY pa_expenditure_items_all.acct_rate_type%TYPE
78 ,x_acct_rate_date_code OUT NOCOPY pa_implementations_all.acct_rate_date_code%TYPE
79 ,x_acct_exch_rate OUT NOCOPY pa_org_labor_sch_rule.acct_exchange_rate%TYPE
80 ,x_ot_project_id OUT NOCOPY pa_projects_all.project_id%TYPE
81 ,x_ot_task_id OUT NOCOPY pa_tasks.task_id%TYPE
82 ,x_err_stage OUT NOCOPY number
83 ,x_err_code OUT NOCOPY varchar2
84 ,P_Called_From IN varchar2 DEFAULT 'O' /* Added for 3405326 */
85 );
86
87 PROCEDURE get_orgn_level_costing_info(
88 p_org_id IN pa_implementations_all.org_id%TYPE
89 ,p_organization_id IN pa_expenditures_all.incurred_by_organization_id%TYPE
90 ,p_person_id IN pa_expenditures_all.incurred_by_person_id%TYPE
91 ,p_job_id IN pa_expenditure_items_all.job_id%TYPE
92 ,p_txn_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
93 ,p_calling_module IN varchar2 default 'STAFFED'
94 ,x_org_labor_sch_rule_id IN OUT NOCOPY pa_org_labor_sch_rule.org_labor_sch_rule_id%TYPE
95 ,x_costing_rule IN OUT NOCOPY pa_compensation_rule_sets.compensation_rule_set%TYPE
96 ,x_rate_sch_id IN OUT NOCOPY pa_std_bill_rate_schedules.bill_rate_sch_id%TYPE
97 ,x_ot_project_id IN OUT NOCOPY pa_projects_all.project_id%TYPE
98 ,x_ot_task_id IN OUT NOCOPY pa_tasks.task_id%TYPE
99 ,x_base_hours IN OUT NOCOPY pa_org_labor_sch_rule.base_hours%TYPE /* 12.2 payroll intg .. bug 11811475 */
100 ,x_rbc_elem_type_id IN OUT NOCOPY pa_org_labor_sch_rule.rbc_element_type_id%TYPE /* 12.2 payroll intg .. bug 11811475 */
101 ,x_cost_rate_curr_code IN OUT NOCOPY pa_expenditure_items_all.denom_currency_code%TYPE
102 ,x_acct_rate_type IN OUT NOCOPY pa_expenditure_items_all.acct_rate_type%TYPE
103 ,x_acct_rate_date_code IN OUT NOCOPY pa_implementations_all.acct_rate_date_code%TYPE
104 ,x_acct_exch_rate IN OUT NOCOPY pa_compensation_details_all.acct_exchange_rate%TYPE
105 ,x_err_stage IN OUT NOCOPY number
106 ,x_err_code IN OUT NOCOPY varchar2
107 ,P_Called_From IN varchar2 DEFAULT 'O' /* Added for 3405326 */
108 );
109 -- Labor Cost Rate Reports
110 --Globals for the Cache values.
111 G_EMP_RATE_RULE pa_compensation_rule_sets.compensation_rule_set%type;
112 G_EMP_COST_RATE pa_bill_rates_all.rate%type;
113 G_EMP_ACCT_COST_RATE pa_bill_rates_all.rate%type;
114 G_EMP_RATE_CURR pa_expenditure_items_all.acct_currency_code%type;
115 G_EMP_RATE_START_DATE date;
116 G_EMP_RATE_END_DATE date;
117
118 --Globals for the Cache Attributes.
119 G_EMP_PERSON_ID per_all_people_f.person_id%type;
120 G_EMP_JOB_ID per_jobs.job_id%type;
121 G_EMP_ORGANIZATION_ID hr_organization_units.organization_id%type;
122
123 -- Bug 3785956
124 G_ORG_ID pa_implementations_all.org_id%TYPE;
125 G_TXN_DATE pa_expenditure_items_all.expenditure_item_date%TYPE;
126 G_CALLING_MODULE VARCHAR2(150) := 'STAFFED';
127
128
129 /*----------------------------------------------------------------------------*/
130 -- Start of Comments
131 -- API name : GetEmpCostRate
132 -- Type : Public Function
133 -- Pre-reqs : None
134 -- Function : To get the emp cost rate.
135 -- Return Value : NUMBER
136 -- Prameters
137 -- P_Person_Id IN NUMBER REQUIRED
138 -- P_Job_Id IN NUMBER OPTIONAL
139 -- P_Organization_Id IN NUMBER OPTIONAL
140 -- P_Effective_Date IN DATE OPTIONAL DEFAULT SYSDATE
141 -- P_Rate_Type IN VARCHAR2 REQUIRED
142 -- -- FUNC for Rate in Functional Currency
143 -- -- DENOM for Rate in Denom Currency
144 -- History
145 -- 03-OCT-02 Vgade -Created
146 --
147 /*----------------------------------------------------------------------------*/
148
149 Function GetEmpCostRate( P_Person_Id IN per_all_people_f.person_id%type
150 ,P_Job_Id IN pa_expenditure_items_all.job_id%type
151 ,P_Organization_Id IN pa_expenditures_all.incurred_by_organization_id%type
152 ,P_Effective_Date IN date default SYSDATE
153 ,P_Rate_Type IN varchar2
154 ,P_Called_From IN varchar2 DEFAULT 'O' /* Added for 3405326 */
155 )
156 RETURN pa_bill_rates_all.rate%type;
157 /*----------------------------------------------------------------------------*/
158 -- Start of Comments
159 -- API name : GetEmpCostRateInfo
160 -- Type : Public Function
161 -- Pre-reqs : None
162 -- Function : To get the emp cost rate attributes; COMPENSATION RULE,CURRENCY
163 -- CODE, RATE EFFECTIVE START DATE, and RATE EFFECTIVE END DATE.
164 -- Return Value : VARCHAR2
165 -- Prameters
166 -- P_Person_Id IN NUMBER REQUIRED
167 -- P_Job_Id IN NUMBER OPTIONAL
168 -- P_Organization_Id IN NUMBER OPTIONAL
169 -- P_Effective_Date IN DATE OPTIONAL DEFAULT SYSDATE
170 -- P_Rate_Attribute IN VARCHAR2 REQUIRED
171 -- Valid Values
172 -- RULE for Employee Compensation Rule
173 -- CURR for Rate Currency Code
174 -- START for Rate Effective Start Date.
175 -- END for Rate Effective End Date.
176 -- History
177 -- 03-OCT-02 Vgade -Created
178 --
179 /*----------------------------------------------------------------------------*/
180
181 Function GetEmpCostRateInfo( P_Person_Id IN per_all_people_f.person_id%type
182 ,P_Job_Id IN pa_expenditure_items_all.job_id%type
183 ,P_Organization_Id IN pa_expenditures_all.incurred_by_organization_id%type
184 ,P_Effective_Date IN date default SYSDATE
185 ,P_Rate_Attribute IN varchar2
186 ,P_Called_From IN varchar2 DEFAULT 'O' /* Added for 3405326 */
187 )
188 RETURN VARCHAR2 ;
189 /*----------------------------------------------------------------------------*/
190 PROCEDURE get_orgn_lvl_cst_info_set
191 ( p_org_id_tab IN pa_plsql_datatypes.IdTabTyp
192 ,p_organization_id_tab IN pa_plsql_datatypes.IdTabTyp
193 ,p_person_id_tab IN pa_plsql_datatypes.IdTabTyp
194 ,p_job_id_tab IN pa_plsql_datatypes.IdTabTyp
195 ,p_txn_date_tab IN pa_plsql_datatypes.Char30TabTyp
196 ,p_override_type_tab IN pa_plsql_datatypes.Char150TabTyp
197 ,p_calling_module IN varchar2 default 'STAFFED'
198 ,P_Called_From IN varchar2 DEFAULT 'O' /* Added for 3405326 */
199 ,x_org_labor_sch_rule_id_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp
200 ,x_costing_rule_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp
201 ,x_rate_sch_id_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp
202 ,x_ot_project_id_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp
203 ,x_ot_task_id_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp
204 ,x_base_hours_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp /* 12.2 payroll intg .. ttc .. bug 11811475 */
205 ,x_rbc_elem_type_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp
206 ,x_cost_rate_curr_code_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp
207 ,x_acct_rate_type_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp
208 ,x_acct_rate_date_code_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp
209 ,x_acct_exch_rate_tab IN OUT NOCOPY pa_plsql_datatypes.Char30TabTyp
210 ,x_err_stage_tab IN OUT NOCOPY pa_plsql_datatypes.NumTabTyp
211 ,x_err_code_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp
212 );
213 /*----------------------------------------------------------------------------*/
214 END PA_COST_RATE_PUB;
[Home] [Help]