1 PACKAGE PA_COST_RATE_PUB AS
2 /* $Header: PAXPCRTS.pls 120.0 2005/05/30 19:30:17 appldev noship $*/
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_rt_sch_type pa_std_bill_rate_schedules_all.schedule_type%TYPE;
41
42
43 -- Start of comments
44 -- API name : get_labor_rate
45 -- Type : Public
46 -- Pre-reqs : None.
47 -- Function : Returns Labor Cost Rate for an Employee.
48 -- Parameters : Person Id, Transaction Date. Organization Id and Job Id are optional.
49 -- IN : p_person_id IN NUMBER Required
50 -- Id of the person for whom the rate is to be found.
51 -- p_txn_date IN DATE Required
52 -- The Date on which the rate is required.
53 -- x_organization_id IN NUMBER Optional
54 -- Organization to which the transaction is charged to.
55 -- x_job_id IN NUMBER Optional
56 -- Job of the person.
57 -- p_org_id IN NUMBER Optional
58 -- Expenditure Org Id of the Transaction .
59 -- Version : Current version 1.0
60 -- Initial version 1.0
61 -- End of comments
62 procedure get_labor_rate ( p_person_id IN per_all_people_f.person_id%TYPE
63 ,p_txn_date IN date
64 ,p_calling_module IN varchar2 default 'STAFFED'
65 ,p_org_id IN pa_expenditures_all.org_id%TYPE default NULL /*2879644*/
66 ,x_job_id IN OUT NOCOPY pa_expenditure_items_all.job_id%TYPE
67 ,x_organization_id IN OUT NOCOPY pa_expenditures_all.incurred_by_organization_id%TYPE
68 ,x_cost_rate OUT NOCOPY pa_bill_rates_all.rate%TYPE
69 ,x_start_date_active OUT NOCOPY date
70 ,x_end_date_active OUT NOCOPY date
71 ,x_org_labor_sch_rule_id OUT NOCOPY pa_org_labor_sch_rule.org_labor_sch_rule_id%TYPE
72 ,x_costing_rule OUT NOCOPY pa_compensation_rule_sets.compensation_rule_set%TYPE
73 ,x_rate_sch_id OUT NOCOPY pa_std_bill_rate_schedules_all.bill_rate_sch_id%TYPE
74 ,x_cost_rate_curr_code OUT NOCOPY gl_sets_of_books.currency_code%TYPE
75 ,x_acct_rate_type OUT NOCOPY pa_expenditure_items_all.acct_rate_type%TYPE
76 ,x_acct_rate_date_code OUT NOCOPY pa_implementations_all.acct_rate_date_code%TYPE
77 ,x_acct_exch_rate OUT NOCOPY pa_org_labor_sch_rule.acct_exchange_rate%TYPE
78 ,x_ot_project_id OUT NOCOPY pa_projects_all.project_id%TYPE
79 ,x_ot_task_id OUT NOCOPY pa_tasks.task_id%TYPE
80 ,x_err_stage OUT NOCOPY number
81 ,x_err_code OUT NOCOPY varchar2
82 ,P_Called_From IN varchar2 DEFAULT 'O' /* Added for 3405326 */
83 );
84
85 PROCEDURE get_orgn_level_costing_info(
86 p_org_id IN pa_implementations_all.org_id%TYPE
87 ,p_organization_id IN pa_expenditures_all.incurred_by_organization_id%TYPE
88 ,p_person_id IN pa_expenditures_all.incurred_by_person_id%TYPE
89 ,p_job_id IN pa_expenditure_items_all.job_id%TYPE
90 ,p_txn_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
91 ,p_calling_module IN varchar2 default 'STAFFED'
92 ,x_org_labor_sch_rule_id IN OUT NOCOPY pa_org_labor_sch_rule.org_labor_sch_rule_id%TYPE
93 ,x_costing_rule IN OUT NOCOPY pa_compensation_rule_sets.compensation_rule_set%TYPE
94 ,x_rate_sch_id IN OUT NOCOPY pa_std_bill_rate_schedules.bill_rate_sch_id%TYPE
95 ,x_ot_project_id IN OUT NOCOPY pa_projects_all.project_id%TYPE
96 ,x_ot_task_id IN OUT NOCOPY pa_tasks.task_id%TYPE
97 ,x_cost_rate_curr_code IN OUT NOCOPY pa_expenditure_items_all.denom_currency_code%TYPE
98 ,x_acct_rate_type IN OUT NOCOPY pa_expenditure_items_all.acct_rate_type%TYPE
99 ,x_acct_rate_date_code IN OUT NOCOPY pa_implementations_all.acct_rate_date_code%TYPE
100 ,x_acct_exch_rate IN OUT NOCOPY pa_compensation_details_all.acct_exchange_rate%TYPE
101 ,x_err_stage IN OUT NOCOPY number
102 ,x_err_code IN OUT NOCOPY varchar2
103 ,P_Called_From IN varchar2 DEFAULT 'O' /* Added for 3405326 */
104 );
105 -- Labor Cost Rate Reports
106 --Globals for the Cache values.
107 G_EMP_RATE_RULE pa_compensation_rule_sets.compensation_rule_set%type;
108 G_EMP_COST_RATE pa_bill_rates_all.rate%type;
109 G_EMP_ACCT_COST_RATE pa_bill_rates_all.rate%type;
110 G_EMP_RATE_CURR pa_expenditure_items_all.acct_currency_code%type;
111 G_EMP_RATE_START_DATE date;
112 G_EMP_RATE_END_DATE date;
113
114 --Globals for the Cache Attributes.
115 G_EMP_PERSON_ID per_all_people_f.person_id%type;
116 G_EMP_JOB_ID per_jobs.job_id%type;
117 G_EMP_ORGANIZATION_ID hr_organization_units.organization_id%type;
118
119 -- Bug 3785956
120 G_ORG_ID pa_implementations_all.org_id%TYPE;
121 G_TXN_DATE pa_expenditure_items_all.expenditure_item_date%TYPE;
122 G_CALLING_MODULE VARCHAR2(150) := 'STAFFED';
123
124
125 /*----------------------------------------------------------------------------*/
126 -- Start of Comments
127 -- API name : GetEmpCostRate
128 -- Type : Public Function
129 -- Pre-reqs : None
130 -- Function : To get the emp cost rate.
131 -- Return Value : NUMBER
132 -- Prameters
133 -- P_Person_Id IN NUMBER REQUIRED
134 -- P_Job_Id IN NUMBER OPTIONAL
135 -- P_Organization_Id IN NUMBER OPTIONAL
136 -- P_Effective_Date IN DATE OPTIONAL DEFAULT SYSDATE
137 -- P_Rate_Type IN VARCHAR2 REQUIRED
138 -- -- FUNC for Rate in Functional Currency
139 -- -- DENOM for Rate in Denom Currency
140 -- History
141 -- 03-OCT-02 Vgade -Created
142 --
143 /*----------------------------------------------------------------------------*/
144
145 Function GetEmpCostRate( P_Person_Id IN per_all_people_f.person_id%type
146 ,P_Job_Id IN pa_expenditure_items_all.job_id%type
147 ,P_Organization_Id IN pa_expenditures_all.incurred_by_organization_id%type
148 ,P_Effective_Date IN date default SYSDATE
149 ,P_Rate_Type IN varchar2
150 ,P_Called_From IN varchar2 DEFAULT 'O' /* Added for 3405326 */
151 )
152 RETURN pa_bill_rates_all.rate%type;
153 /*----------------------------------------------------------------------------*/
154 -- Start of Comments
155 -- API name : GetEmpCostRateInfo
156 -- Type : Public Function
157 -- Pre-reqs : None
158 -- Function : To get the emp cost rate attributes; COMPENSATION RULE,CURRENCY
159 -- CODE, RATE EFFECTIVE START DATE, and RATE EFFECTIVE END DATE.
160 -- Return Value : VARCHAR2
161 -- Prameters
162 -- P_Person_Id IN NUMBER REQUIRED
163 -- P_Job_Id IN NUMBER OPTIONAL
164 -- P_Organization_Id IN NUMBER OPTIONAL
165 -- P_Effective_Date IN DATE OPTIONAL DEFAULT SYSDATE
166 -- P_Rate_Attribute IN VARCHAR2 REQUIRED
167 -- Valid Values
168 -- RULE for Employee Compensation Rule
169 -- CURR for Rate Currency Code
170 -- START for Rate Effective Start Date.
171 -- END for Rate Effective End Date.
172 -- History
173 -- 03-OCT-02 Vgade -Created
174 --
175 /*----------------------------------------------------------------------------*/
176
177 Function GetEmpCostRateInfo( P_Person_Id IN per_all_people_f.person_id%type
178 ,P_Job_Id IN pa_expenditure_items_all.job_id%type
179 ,P_Organization_Id IN pa_expenditures_all.incurred_by_organization_id%type
180 ,P_Effective_Date IN date default SYSDATE
181 ,P_Rate_Attribute IN varchar2
182 ,P_Called_From IN varchar2 DEFAULT 'O' /* Added for 3405326 */
183 )
184 RETURN VARCHAR2 ;
185 /*----------------------------------------------------------------------------*/
186 PROCEDURE get_orgn_lvl_cst_info_set
187 ( p_org_id_tab IN pa_plsql_datatypes.IdTabTyp
188 ,p_organization_id_tab IN pa_plsql_datatypes.IdTabTyp
189 ,p_person_id_tab IN pa_plsql_datatypes.IdTabTyp
190 ,p_job_id_tab IN pa_plsql_datatypes.IdTabTyp
191 ,p_txn_date_tab IN pa_plsql_datatypes.Char30TabTyp
192 ,p_override_type_tab IN pa_plsql_datatypes.Char150TabTyp
193 ,p_calling_module IN varchar2 default 'STAFFED'
194 ,P_Called_From IN varchar2 DEFAULT 'O' /* Added for 3405326 */
195 ,x_org_labor_sch_rule_id_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp
196 ,x_costing_rule_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp
197 ,x_rate_sch_id_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp
198 ,x_ot_project_id_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp
199 ,x_ot_task_id_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp
200 ,x_cost_rate_curr_code_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp
201 ,x_acct_rate_type_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp
202 ,x_acct_rate_date_code_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp
203 ,x_acct_exch_rate_tab IN OUT NOCOPY pa_plsql_datatypes.Char30TabTyp
204 ,x_err_stage_tab IN OUT NOCOPY pa_plsql_datatypes.NumTabTyp
205 ,x_err_code_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp
206 );
207 /*----------------------------------------------------------------------------*/
208 END PA_COST_RATE_PUB;