1 PACKAGE PA_UTILS4 AS
2 /* $Header: PAXGUT4S.pls 120.4 2006/01/23 14:35:48 vgade noship $*/
3
4 -- Added these global variables for CWK changes.
5 G_INCURRED_BY_PERSON_ID NUMBER := null ;
6 G_full_name VARCHAR2(240) := NULL ;
7 G_employee_number VARCHAR2(30):= NULL ;
8
9
10 /* The following global variables are declared for caching in MRC trigger PAMRCEIA.pls */
11 G_P_SOB NUMBER := null;
12 G_P_org_id_new Number := null;
13 G_P_org_id_old Number := null;
14 G_R_SOB NUMBER := null;
15 G_R_org_id_new Number := null;
16 G_R_org_id_old Number := null;
17 G_prvdr_sob NUMBER := null;
18 G_prvdr_currency Varchar2(15) := null;
19 G_recvr_sob NUMBER := null;
20 G_recvr_currency Varchar2(15) := null;
21
22
23 G_Tabreporting_set_of_books_id PA_PLSQL_DATATYPES.Idtabtyp;
24 G_Tabreporting_currency_code PA_PLSQL_DATATYPES.char50TabTyp;
25 G_Tabprvdr_recvr_flag PA_PLSQL_DATATYPES.char50TabTyp;
26
27 /* End of MRC global variable declrations */
28
29 /* The following global variables are declared for caching in MRC trigger PAMRCCDL.pls */
30 G_CDL_SOB NUMBER := null;
31 G_CDL_org_id_new Number := null;
32 G_CDL_org_id_old Number := null;
33 G_CDL_currency Varchar2(15) := null;
34
35
36 G_Tabcdlreporting_set_of_books PA_PLSQL_DATATYPES.Idtabtyp;
37 G_Tabcdlreporting_currency_cod PA_PLSQL_DATATYPES.char50TabTyp;
38 G_Tabcdlenabled_flag PA_PLSQL_DATATYPES.char50TabTyp;
39
40 /* End of MRC global variable declrations */
41
42
43 /* Declare Global variables to store the profile values */
44 G_PRM_INSTALLED_FLAG VARCHAR2(1) := null;
45 G_WORKTYPE_ENABLED VARCHAR2(1) := null;
46 G_WORKTYPE_BILLABILITY VARCHAR2(1) := null;
47
48 /* Declare Global plsql tabs and Recs to store the Ids */
49
50 TYPE WorkAssignRec IS RECORD (
51 Person_Id NUMBER,
52 project_id NUMBER,
53 task_id NUMBER,
54 ei_date NUMBER,
55 system_linkage VARCHAR2(100),
56 tp_amt_type_code VARCHAR2(100),
57 assignment_id NUMBER,
58 assignment_name VARCHAR2(100),
59 work_type_id NUMBER,
60 work_type_name VARCHAR2(100),
61 return_status VARCHAR2(100),
62 error_message_code VARCHAR2(100) );
63
64 TYPE WorkAssignRecTab IS TABLE OF WorkAssignRec
65 INDEX BY BINARY_INTEGER;
66
67 TYPE WorkTypeIdRec IS RECORD (
68 project_id NUMBER,
69 task_id NUMBER,
70 assignment_id NUMBER,
71 work_type_id NUMBER
72 );
73
74 TYPE WorkTypeRecIdTab IS TABLE OF WorkTypeIdRec
75 INDEX BY BINARY_INTEGER;
76
77
78 TYPE WorkTypeNameRec IS RECORD (
79 work_type_id NUMBER,
80 work_type_name varchar2(100)
81 );
82
83 TYPE WorkTypeNameRecTab IS TABLE OF WorkTypeNameRec
84 INDEX BY BINARY_INTEGER;
85
86 TYPE TpAmtTypeRec IS RECORD (
87 work_type_id NUMBER,
88 tp_amt_type_code varchar2(100)
89 );
90
91 TYPE TpAmtTypeRecTab IS TABLE OF TpAmtTypeRec
92 INDEX BY BINARY_INTEGER;
93
94 TYPE AssignIdRec IS RECORD (
95 Person_Id NUMBER,
96 project_id NUMBER,
97 task_id NUMBER,
98 ei_date NUMBER,
99 assignment_id NUMBER
100 );
101
102 TYPE AssignIdRecTab IS TABLE OF AssignIdRec
103 INDEX BY BINARY_INTEGER;
104
105 TYPE AssignNameRec IS RECORD (
106 assignment_name varchar2(100)
107 );
108
109 TYPE AssignNameRecTab IS TABLE OF AssignNameRec
110 INDEX BY BINARY_INTEGER;
111
112 G_WorkTypeRecIdTab WorkTypeRecIdTab;
113 G_WorkTypeNameRecTab WorkTypeNameRecTab;
114 G_TpAmtTypeRecTab TpAmtTypeRecTab;
115 G_AssignIdRecTab AssignIdRecTab;
116 G_WorkAssignRecTab WorkAssignRecTab;
117 G_AssignNameRecTab AssignNameRecTab;
118
119 PROCEDURE get_work_assignment(p_person_id IN NUMBER
120 ,p_project_id IN NUMBER
121 ,p_task_id IN NUMBER
122 ,p_ei_date IN DATE
123 ,p_system_linkage IN VARCHAR2
124 ,x_tp_amt_type_code OUT NOCOPY VARCHAR2
125 ,x_assignment_id OUT NOCOPY NUMBER
126 ,x_assignment_name IN OUT NOCOPY VARCHAR2
127 ,x_work_type_id OUT NOCOPY NUMBER
128 ,x_work_type_name IN VARCHAR2
129 ,x_return_status OUT NOCOPY VARCHAR2
130 ,x_error_message_code OUT NOCOPY VARCHAR2 );
131
132 FUNCTION get_work_type_id ( p_project_id IN NUMBER
133 ,p_task_id IN NUMBER
134 ,p_assignment_id IN NUMBER
135 ) RETURN NUMBER;
136
137 --PRAGMA RESTRICT_REFERENCES ( get_work_type_id, WNDS );
138
139 FUNCTION get_work_type_name(p_work_type_id IN NUMBER)
140 RETURN varchar2;
141
142 --PRAGMA RESTRICT_REFERENCES ( get_work_type_name, WNDS );
143
144 FUNCTION get_assignment_name(p_assignment_id IN NUMBER)
145 RETURN varchar2;
146
147 --PRAGMA RESTRICT_REFERENCES ( get_assignment_name, WNDS );
148
149 FUNCTION get_assignment_id(p_person_id IN NUMBER
150 ,p_project_id IN NUMBER
151 ,p_task_id IN NUMBER
152 ,p_ei_date IN DATE
153 ) RETURN NUMBER;
154 -- commented out prgrama restrict as it voilates pragma references of get_person_details
155 --PRAGMA RESTRICT_REFERENCES ( get_assignment_id, WNDS );
156
157 FUNCTION get_tp_amt_type_code(p_work_type_id IN NUMBER)
158 RETURN varchar2;
159
160 --PRAGMA RESTRICT_REFERENCES ( get_tp_amt_type_code, WNDS );
161
162 /** This api derives the site level profile value of
163 * Transaction Billablity derived from work type
164 **/
165 FUNCTION is_worktype_billable_enabled RETURN VARCHAR2;
166
167 --PRAGMA RESTRICT_REFERENCES (is_worktype_billable_enabled, WNDS );
168
169 /** This api derives the billability of the
170 * transaction based on the work type and profile option
171 */
172 FUNCTION get_trxn_work_billabilty(p_work_type_id IN NUMBER
173 ,p_tc_extn_bill_flag IN VARCHAR2 )
174 RETURN varchar2;
175
176 --PRAGMA RESTRICT_REFERENCES (get_trxn_work_billabilty, WNDS );
177
178 /** This api derives the site level profile value of
179 * Transaction work type enabled
180 **/
181 FUNCTION is_exp_work_type_enabled RETURN VARCHAR2;
182
183 --PRAGMA RESTRICT_REFERENCES (is_exp_work_type_enabled, WNDS );
184
185 /* BUG # 3220230 Added the function to get the billability of reversals in OIT */
186 FUNCTION GetOrig_EiBillability_SST(orig_eid IN NUMBER,billable_flag IN VARCHAR2,trans_source IN VARCHAR2) RETURN VARCHAR2;
187
188 /* Bug# 4057474 Added the function to get the bill_hold_flag of reversals in external transaction source like OTL*/
189 FUNCTION GetOrig_EiBill_hold(orig_eid IN NUMBER,bill_hold_flag IN VARCHAR2) RETURN VARCHAR2;
190
191 PROCEDURE check_txn_exists (p_project_id IN NUMBER,
192 p_task_id IN NUMBER DEFAULT NULL,
193 x_status_code OUT NOCOPY NUMBER,
194 x_err_code OUT NOCOPY VARCHAR2,
195 x_err_stage OUT NOCOPY VARCHAR2);
196
197 G_PrevAsgPerId NUMBER;
198 G_PrevAsgPrjId NUMBER;
199 G_PrevAsgEIDate DATE;
200 G_PrevAsgAsgnId NUMBER;
201
202 G_PrevWkPrjId NUMBER;
203 G_PrevWkTskId NUMBER;
204 G_PrevWkAsgnId NUMBER;
205 G_PrevWkTypeId NUMBER;
206
207 /** This api checks if a bill rate schedule is used
208 * in any organization assignment.
209 */
210 FUNCTION IsUsedInCosting(p_bill_rate_sch_id IN NUMBER )
211 RETURN BOOLEAN;
212
213 /** This API validates the given IN param is Number or Not
214 * If not Number return -9999
215 */
216
217 FUNCTION getNumericString(p_reference1 IN varchar2) RETURN NUMBER;
218
219 /** This API will return Implementaion OrgId and uses cacheing logic
220 */
221 FUNCTION get_org_id RETURN NUMBER;
222
223 /** This API returns set_of_books_id from the implementations
224 **/
225 FUNCTION get_primary_sob RETURN NUMBER;
226
227 /** This API returns the Implementation values **/
228 PROCEDURE get_imp_values(x_prim_sob OUT NOCOPY Number
229 ,x_org_id OUT NOCOPY NUmber
230 ,x_book_type_code OUT NOCOPY varchar2
231 ,x_business_group OUT NOCOPY number
232 );
233
234 G_imp_sob_id NUMBER;
235 G_imp_org_id NUMBER;
236 G_imp_book_type_code VARCHAR2(100);
237 G_imp_bus_group NUMBER;
238
239 /** This API returns the Business group Id for the given Organization Id **/
240 FUNCTION GetOrgBusinessGrpId(p_organization_id IN Number) RETURN NUMBER;
241
242 /* This is an public API, which in turn calls a private function CheckCCTxnsExists
243 * This api will be called from project and task Form before deleting
244 * any of the task or project
245 */
246 PROCEDURE Check_CC_TxnExists(p_project_id Number
247 ,p_task_id Number
248 ,x_return_status OUT NOCOPY varchar2
249 ,x_msg_data OUT NOCOPY varchar2
250 ,x_msg_count OUT NOCOPY Number );
251
252
253 /* This is an public API, which in turn calls a private functions
254 * This api will be called from budgetary controls form to check any
255 * transactions exists for project or task. If so the budgetary control form
256 * will be modified to read only mode
257 */
258 PROCEDURE CheckToEnableBdgtCtrl(p_project_id Number
259 ,p_task_id Number
260 ,p_mode Varchar2 Default 'BDGTCTRL'
261 ,x_return_status OUT NOCOPY varchar2
262 ,x_error_msg_code OUT NOCOPY varchar2
263 ,x_error_stage OUT NOCOPY varchar2
264 );
265
266 FUNCTION get_unit_of_measure ( p_expenditure_type IN VARCHAR2 ) Return VARCHAR2 ;
267
268 FUNCTION get_unit_of_measure_m ( p_unit_of_measure IN VARCHAR2 DEFAULT NULL,
269 p_expenditure_type IN VARCHAR2 DEFAULT NULL ) Return VARCHAR2 ;
270
271
272 FUNCTION get_emp_name_number( p_incurred_by_person_id IN NUMBER,
273 p_expenditure_ending_date IN DATE,
274 p_mode IN VARCHAR2 )Return VARCHAR2;
275
276 FUNCTION get_wip_resource_code(p_wip_resource_id IN NUMBER ) Return VARCHAR2;
277
278 FUNCTION get_inventory_item(p_inventory_item_id IN NUMBER) Return VARCHAR2;
279
280 FUNCTION get_invoice_payment_num(p_transaction_source IN VARCHAR2,p_inv_payment_id IN VARCHAR2) Return NUMBER;
281
282 FUNCTION get_ledger_cash_basis_flag Return VARCHAR2;
283
284 /*----------------------------------------------------------------------------*/
285 -- Start of Comments
286 -- API name : IsProjectsImplemented
287 -- Type : Public Function
288 -- Pre-reqs : None
289 -- Function : To check if Projects is implemented for a given OU
290 -- Return Value : VARCHAR2
291 -- Prameters
292 -- p_org_id IN NUMBER REQUIRED
293 -- History
294 -- 12-JUL-05 Vgade -Created
295 --
296 /*----------------------------------------------------------------------------*/
297 FUNCTION IsProjectsImplemented(p_org_id IN Number) RETURN VARCHAR2;
298
299 END PA_UTILS4;
[Home] [Help]