1 PACKAGE PATC AUTHID CURRENT_USER AS
2 /* $Header: PAXTTXCS.pls 120.6 2011/04/18 12:09:00 speddi ship $ */
3
4 -- ======================================================================
5 /*
6 The processing flow of the Transaction Controls package follows the description
7 in the "Using Transaction Controls" essay in the Release 10 OPA Reference
8 Manual, especially the flow diagramed in Figure 9-4.
9
10 If the transaction passes all of the basic validation checks ( project status
11 is not closed, task is chargeable, transaction date between project/task start
12 and end dates), then the transaction is checked against any existing
13 transaction controls.
14
15 The transaction is first validated against any task-level transaction controls.
16 Task-level transaction controls always override any project-level transaction
17 controls. If an applicable transaction control is found at the task level, and
18 the item passes this control, then the item is valid. Likewise, if it fails,
19 then the item is invalid.
20
24 then the item is invalid and is rejected
21 If no applicable transaction control is found at the task level, then one of
22 the following occurs:
23 - if the limit to transaction controls flag for the task is set to 'Yes',
25 - if the flag is set to 'No', then the item is validated against any
26 applicable project-level transaction controls.
27
28 There may be several transaction controls that are applicable for a given item.
29 However, transactions are evaluated ONLY against the applicable transaction
30 control with the highest level of precedence. The order of precedence is as
31 follows:
32
33 1. Person - Expenditure Category - Expenditure Type - [Non-Labor Resource]
34 2. Person - Expenditure Category*
35 3. Expenditure Category - Expenditure Type - [Non-Labor Resource]
36 4. Expenditure Category
37
38 *If a transaction control specifies that a person CAN charge, but does not
39 specify an expenditure category, then the procedure looks for any applicable
40 transaction controls (the most granular control matching the transaction's
41 expenditure category) that do not specify a particular person and then takes
42 the intersection of these two transaction controls. This is illustrated below
43 in Example #2 below.
44
45
46
47 Example 1
48 ---------
49
50 Transaction controls defined:
51
52 Employee Expend Cat Expend Type NLR Chargeable?
53 ----------- ----------- ----------- ----------- -----------
54 -- Expense -- -- Yes
55 -- Assets -- -- Yes
56 Robinson Expense Meals -- Yes
57 Robinson -- -- -- No
58 -- Labor Clerical -- Yes
59 -- Expense Meals -- No
60
61 If the transaction being entered is a 'Meals' item for Robinson, then the
62 following transaction controls are all applicable:
63
64 Employee Expend Cat Expend Type NLR Chargeable?
65 ----------- ----------- ----------- ----------- -----------
66 Robinson Expense Meals -- Yes
67 -- Expense -- -- Yes
68 Robinson -- -- -- No
69 -- Expense Meals -- No
70
71 But given the precedence above, only one control is used to validate the item:
72
73 Employee Expend Cat Expend Type NLR Chargeable?
74 ------------- ------------- ------------- ------------- -------------
75 Robinson Expense Meals -- Yes
76
77 Outcome: The transaction is valid.
78
79
80 If the transaction being entered is an 'Air Travel' item for Robinson, then the
81 following transaction controls are both applicable:
82
83 Employee Expend Cat Expend Type NLR Chargeable?
84 ----------- ----------- ----------- ----------- -----------
85 Robinson -- -- -- No
86 -- Expense -- -- Yes
87
88 But given the precedence above, only one control is used to validate the item:
89
90 Employee Expend Cat Expend Type NLR Chargeable?
91 ----------- ----------- ----------- ----------- -----------
92 Robinson -- -- -- No
93
94 Outcome: The item is invalid.
95
96 NOTE: An intersection of the two applicable transaction controls is not used in
97 this case because Robinson is not chargeable.
98
99
100 Example 2
101 ---------
102
103 Transaction Controls defined:
104
105 Employee Expend Cat Expend Type NLR Chargeable?
106 ----------- ----------- ----------- ----------- -----------
107 Robinson -- -- -- Yes
108 -- Expense -- -- No
109 -- Expense Meals -- Yes
110
111 Again, if the transaction being entered is a 'Meals' item for Robinson, then
112 all of the transaction controls are applicable. Transaction controls involving
113 a person take precedence over others, so the transaction is validated against
114 the following control:
115
116 Employee Expend Cat Expend Type NLR Chargeable?
117 ----------- ----------- ----------- ----------- -----------
118 Robinson -- -- -- Yes
119
120 But, since the person is chargeable, but an expenditure category is not
121 specified in this transaction control, then the procedure will check for any
122 existing transaction controls that match the expenditure category for 'Meals'
123 and take the INTERSECTION of the two transaction controls. The following are
124 applicable:
125
126 Employee Expend Cat Expend Type NLR Chargeable?
127 ----------- ----------- ----------- ----------- -----------
128 -- Expense -- -- No
129 -- Expense Meals -- Yes
130
131 Since the most granular transaction control is the only control used to
132 validate the item, the following control is used in the intersection with
133 the 'person' control:
134
135 Employee Expend Cat Expend Type NLR Chargeable?
136 ----------- ----------- ----------- ----------- -----------
137 -- Expense Meals -- Yes
138
139 Outcome: The item is valid.
140
141
142 If the item being entered is an 'Air Travel' item for Robinson, then the
143 following transaction controls are all applicable:
144
145 Employee Expend Cat Expend Type NLR Chargeable?
146 ----------- ----------- ----------- ----------- -----------
150 Again an INTERSECTION is necessary. In this case, however, the item is invalid.
147 Robinson -- -- -- Yes
148 -- Expense -- -- No
149
151
152 */
153
154 /* Declare Global variable to hold the Assignment_id IN OUT param from
155 * patcx procedure. Instead of adding a new IN OUT variable for PATC,
156 * PA_TRANSACTIONS_PUB.VALIDATE_TRANSACTION,AP FORM, PO FORM , EXP FORM
157 * a global variable is declared in PATC and is accessed from all the
158 * above the places to copy the overide value of the assignment id
159 **/
160
161 G_OVERIDE_ASSIGNMENT_ID pa_expenditure_items_all.assignment_id%type := NULL;
162 /* Added for bug 2648550 */
163 /* When the assignment_id is overwritten work_type_id,TP_AMT_TYPE_CODE,assignment_name
164 * and WORK_TYPE_NAME too needs to be modified.So instead of using IN OUT parameters
165 * global variables are declared to hold these parameters and can be referenced from
166 * anywhere
167 **/
168 G_OVERIDE_WORK_TYPE_ID pa_tasks.work_type_id%type :=NULL;
169 G_OVERIDE_TP_AMT_TYPE_CODE pa_expenditure_items_all.TP_AMT_TYPE_CODE%type :=NULL;
170 G_OVERIDE_ASSIGNMENT_NAME pa_project_assignments.assignment_name%type :=NULL;
171 G_OVERIDE_WORK_TYPE_NAME pa_work_types_tl.name%type :=NULL;
172 /* End of bug 2648550 */
173
174 PROCEDURE set_billable_flag ( txn_cntrl_bill_flag IN VARCHAR2
175 , task_bill_flag IN VARCHAR2);
176
177 PROCEDURE get_status ( X_project_id IN NUMBER
178 , X_task_id IN NUMBER
179 , X_ei_date IN DATE
180 , X_expenditure_type IN VARCHAR2
181 , X_non_labor_resource IN VARCHAR2
182 , X_person_id IN NUMBER
183 , X_quantity IN NUMBER DEFAULT NULL
184 , X_denom_currency_code IN VARCHAR2 DEFAULT NULL
185 , X_acct_currency_code IN VARCHAR2 DEFAULT NULL
186 , X_denom_raw_cost IN NUMBER DEFAULT NULL
187 , X_acct_raw_cost IN NUMBER DEFAULT NULL
188 , X_acct_rate_type IN VARCHAR2 DEFAULT NULL
189 , X_acct_rate_date IN DATE DEFAULT NULL
190 , X_acct_exchange_rate IN NUMBER DEFAULT NULL
191 , X_transfer_ei IN NUMBER DEFAULT NULL
192 , X_incurred_by_org_id IN NUMBER DEFAULT NULL
193 , X_nl_resource_org_id IN NUMBER DEFAULT NULL
194 , X_transaction_source IN VARCHAR2 DEFAULT NULL
195 , X_calling_module IN VARCHAR2 DEFAULT NULL
196 , X_vendor_id IN NUMBER DEFAULT NULL
197 , X_entered_by_user_id IN NUMBER DEFAULT NULL
198 , X_attribute_category IN VARCHAR2 DEFAULT NULL
199 , X_attribute1 IN VARCHAR2 DEFAULT NULL
200 , X_attribute2 IN VARCHAR2 DEFAULT NULL
201 , X_attribute3 IN VARCHAR2 DEFAULT NULL
202 , X_attribute4 IN VARCHAR2 DEFAULT NULL
203 , X_attribute5 IN VARCHAR2 DEFAULT NULL
204 , X_attribute6 IN VARCHAR2 DEFAULT NULL
205 , X_attribute7 IN VARCHAR2 DEFAULT NULL
206 , X_attribute8 IN VARCHAR2 DEFAULT NULL
207 , X_attribute9 IN VARCHAR2 DEFAULT NULL
208 , X_attribute10 IN VARCHAR2 DEFAULT NULL
209 , X_attribute11 IN VARCHAR2 DEFAULT NULL
210 , X_attribute12 IN VARCHAR2 DEFAULT NULL
211 , X_attribute13 IN VARCHAR2 DEFAULT NULL
212 , X_attribute14 IN VARCHAR2 DEFAULT NULL
213 , X_attribute15 IN VARCHAR2 DEFAULT NULL
214 , X_msg_application IN OUT NOCOPY VARCHAR2
215 , X_msg_type OUT NOCOPY VARCHAR2
216 , X_msg_token1 OUT NOCOPY VARCHAR2
217 , X_msg_token2 OUT NOCOPY VARCHAR2
218 , X_msg_token3 OUT NOCOPY VARCHAR2
219 , X_msg_count OUT NOCOPY NUMBER
220 , X_status OUT NOCOPY VARCHAR2
221 , X_billable_flag OUT NOCOPY VARCHAR2
222 , p_projfunc_currency_code IN VARCHAR2 default null
223 , p_projfunc_cost_rate_type IN VARCHAR2 default null
224 , p_projfunc_cost_rate_date IN DATE default null
225 , p_projfunc_cost_exchg_rate IN NUMBER default null
226 , p_assignment_id IN NUMBER default null
227 , p_work_type_id IN NUMBER default null
228 , p_sys_link_function IN VARCHAR2 default null
229 , P_Po_Header_Id IN NUMBER default null
230 , P_Po_Line_Id IN NUMBER default null
231 , P_Person_Type IN VARCHAR2 default null
232 , P_Po_Price_Type IN VARCHAR2 default null
233 , P_Document_Type IN VARCHAR2 default null
234 , P_Document_Line_Type IN VARCHAR2 default null
235 , P_Document_Dist_Type IN VARCHAR2 default null
236 -- 10253400: Payroll integration enhancement for 12.2.. start
237 , p_job_id IN NUMBER default null
238 , p_location_id IN NUMBER default null
239 , p_pay_element_type_id IN NUMBER default null
240 , P_payroll_accrual_flag IN VARCHAR2 default null ---- Added for payroll billable_flag changes for 12.2 bug#12344689
241 -- 10253400: Payroll integration enhancement for 12.2.. end
242 , P_pa_ref_num1 IN NUMBER default null
243 , P_pa_ref_num2 IN NUMBER default null
244 , P_pa_ref_num3 IN NUMBER default null
245 , P_pa_ref_num4 IN NUMBER default null
246 , P_pa_ref_num5 IN NUMBER default null
247 , P_pa_ref_num6 IN NUMBER default null
248 , P_pa_ref_num7 IN NUMBER default null
249 , P_pa_ref_num8 IN NUMBER default null
250 , P_pa_ref_num9 IN NUMBER default null
251 , P_pa_ref_num10 IN NUMBER default null
252 , P_pa_ref_var1 IN VARCHAR2 default null
253 , P_pa_ref_var2 IN VARCHAR2 default null
254 , P_pa_ref_var3 IN VARCHAR2 default null
255 , P_pa_ref_var4 IN VARCHAR2 default null
256 , P_pa_ref_var5 IN VARCHAR2 default null
257 , P_pa_ref_var6 IN VARCHAR2 default null
258 , P_pa_ref_var7 IN VARCHAR2 default null
259 , P_pa_ref_var8 IN VARCHAR2 default null
260 , P_pa_ref_var9 IN VARCHAR2 default null
261 , P_pa_ref_var10 IN VARCHAR2 default null);
262
263
264 G_PREV_EI_DATE DATE;
265 G_PREV_EXP_TYPE VARCHAR2(30);
266 G_PREV_EXP_TYPE_ACTIVE NUMBER(1);
267 G_PREV_LEVEL VARCHAR2(1);
268 G_PREV_PROJ_ID NUMBER(15);
269 G_PREV_TASK_ID NUMBER(15);
270
271 /* Added the following for bug 2831477 */
272 G_EXP_TYPE VARCHAR2(30);
273 G_EXP_TYPE_SYS_LINK VARCHAR2(30);
274 G_EXP_TYPE_START_DATE DATE;
275 G_EXP_TYPE_END_DATE DATE;
276
277 /* Added Procedure check_termination for Bug#4604614 (BaseBug#4118885) */
278 procedure check_termination (p_person_id in per_all_people_f.person_id%type,
279 p_ei_date in pa_expenditure_items_all.expenditure_item_date%type,
280 x_actual_termination_date out nocopy per_periods_of_service.actual_termination_date%type);
281
282 /* Bug 6156072: Base Bug 6045051: procedure check_termination_for_cwk added */
283 procedure check_termination_for_cwk (p_person_id in per_all_people_f.person_id%type,
284 p_ei_date in pa_expenditure_items_all.expenditure_item_date%type,
285 x_actual_termination_date out nocopy per_periods_of_placement.actual_termination_date%type);
286
287 END PATC;
[Home] [Help]