1 PACKAGE PA_TRX_IMPORT AUTHID CURRENT_USER AS
2 /* $Header: PAXTTRXS.pls 120.15.12020000.4 2013/04/05 07:03:22 rboyalap ship $ */
3 /*#
4 * Oracle Projects provides a single open interface, called Transaction Import.
5 * These procedures are called before and after the Transaction Import interface process.
6 * @rep:scope public
7 * @rep:product PA
8 * @rep:lifecycle active
9 * @rep:displayname Post-import{Transaction import}.
10 * @rep:compatibility S
11 * @rep:category BUSINESS_ENTITY PA_PROJECT
12 * @rep:category BUSINESS_ENTITY PA_PROJ_COST
13 * @rep:doccd 120pjapi.pdf See the Oracle Projects API's, Client Extensions, and Open Interfaces Reference
17
14 */
15 -- BUG: 4600792 PQE: R12 CHANGE AWARD END WHEN ENCUMBRANCE EXISTS, IMPORT ENC REVERSALS FOR CLOSE
16 G_validate_proj_tsk_Ei_Date VARCHAR2 (1) := 'Y';
18 G_cash_based_accounting VARCHAR2 (1); /* REL12 */
19 --Identifier which is set to 'Y' if cash basis accounting is setup.
20 -- REL12 AP Lines Uptake-- Cash based accounting support--
21
22 G_finalPaymentID NUMBER;
23
24 --
25 -- Bug : 4962731
26 -- : R12.PJ:XB1:QA:BC:INCORRECT AMOUNTS INTERFACED TO PROJECTS IN CASH BASED ACC
27 -- Functionality :
28 -- Discount is applicable when discount method is EXPENSE
29 -- Discount is applicable for tax distributions when discount method is TAX
30 -- Discount is not applicable when discount method is 'SYSTEM'
31 -- Discount is also based on the discount profile start date
32 -- ap payment record includes the discount amount and we do not need to interface
33 -- discount record because we are interfacing the payments.
34 -- But we need to relieve corresponding inv dist amount paid to relieve the ap commitment amount.
35 -- ap amount to relieve := payment amunt + discount amount (when applicable).
36 -- ====================================================================================================
37 G_Discount_Method VARCHAR2(10);
38 G_Profile_Discount_Start_date DATE;
39
40 PROCEDURE GetTrxSrcInfo (X_trx_src IN VARCHAR2);
41
42 PROCEDURE GetEtypeInfo (X_etype IN VARCHAR2,
43 X_date IN DATE);
44
45 PROCEDURE GetNlrInfo (X_nlr IN VARCHAR2,
46 X_nlro_id IN NUMBER);
47
48 PROCEDURE CheckDupItem (X_trx_source IN VARCHAR2,
49 X_trx_ref IN VARCHAR2,
50 X_status OUT NOCOPY VARCHAR2);
51
52 PROCEDURE CheckDupAdjItem (X_adj_item_id IN NUMBER,
53 X_status OUT NOCOPY VARCHAR2);
54
55 --SST Changes modified procedure name from GetProjBcostFlag to
56 -- GetProjTypeInfo, also added project type class parameter.
57
58 PROCEDURE GetProjTypeInfo (X_project_id IN NUMBER,
59 X_proj_bcost_flag OUT NOCOPY VARCHAR2,
60 X_proj_type_class OUT NOCOPY VARCHAR2,
61 X_burden_amt_display_method OUT NOCOPY VARCHAR2,
62 X_Total_Burden_Flag OUT NOCOPY VARCHAR2);
63
64 FUNCTION CheckCCID (ccid NUMBER) RETURN NUMBER;
65 --pragma RESTRICT_REFERENCES (CheckCCID, WNDS, WNPS);
66
67 /* Added the function below for bug number 1254143. Bug 1426802 changed NUMBER TO VARCHAR2(30) */
68 FUNCTION GetOrgName (employee_number VARCHAR2,
69 expenditure_item_date DATE,
70 business_group_name IN VARCHAR2,
71 person_type IN varchar2 default NULL)
72 RETURN VARCHAR2;
73 --pragma RESTRICT_REFERENCES (GetOrgName, WNDS, WNPS);
74
75 PROCEDURE Validate_VI (X_vendor_number IN VARCHAR2 DEFAULT NULL,
76 X_employee_number IN VARCHAR2 DEFAULT NULL,
77 X_result OUT NOCOPY NUMBER,
78 X_status OUT NOCOPY VARCHAR2);
79
80
81 PROCEDURE ValidateItem (X_trx_src IN VARCHAR2,
82 X_enum IN VARCHAR2,
83 X_oname IN VARCHAR2,
84 X_end_date IN DATE,
85 X_ei_date IN DATE,
86 X_etype IN VARCHAR2,
87 X_pnum IN VARCHAR2,
88 X_tnum IN VARCHAR2,
89 X_nlr IN VARCHAR2,
90 X_nlro_name IN VARCHAR2,
91 X_qty IN NUMBER,
92 X_denom_raw_cost IN OUT NOCOPY NUMBER, /*10357604 */
93 X_module IN VARCHAR2,
94 X_trx_ref IN VARCHAR2,
95 X_match_flag IN VARCHAR2,
96 X_entered_by IN NUMBER,
97 X_att_cat IN VARCHAR2,
98 X_att1 IN OUT NOCOPY VARCHAR2,
99 X_att2 IN OUT NOCOPY VARCHAR2,
100 X_att3 IN OUT NOCOPY VARCHAR2,
101 X_att4 IN OUT NOCOPY VARCHAR2,
102 X_att5 IN OUT NOCOPY VARCHAR2,
103 X_att6 IN OUT NOCOPY VARCHAR2,
104 X_att7 IN OUT NOCOPY VARCHAR2,
105 X_att8 IN OUT NOCOPY VARCHAR2,
106 X_att9 IN OUT NOCOPY VARCHAR2,
107 X_att10 IN OUT NOCOPY VARCHAR2,
108 X_drccid IN NUMBER,
109 X_crccid IN NUMBER,
110 X_gl_date IN OUT NOCOPY DATE -- bug 3357936 change param to in out,
111 , X_denom_burdened_cost IN OUT NOCOPY NUMBER,
112 X_system_linkage IN VARCHAR2,
113 X_status OUT NOCOPY VARCHAR2,
114 X_bill_flag OUT NOCOPY VARCHAR2,
115 X_receipt_currency_amount IN NUMBER default null,
116 X_receipt_currency_code IN VARCHAR2 default NULL,
117 X_receipt_exchange_rate IN OUT NOCOPY NUMBER,
118 X_denom_currency_code IN OUT NOCOPY VARCHAR2,
119 X_acct_rate_date IN OUT NOCOPY DATE,
120 X_acct_rate_type IN OUT NOCOPY VARCHAR2,
121 X_acct_exchange_rate IN OUT NOCOPY NUMBER,
122 X_acct_raw_cost IN NUMBER default NULL,
123 X_acct_burdened_cost IN OUT NOCOPY NUMBER,
124 X_acct_exchange_rounding_limit IN NUMBER default NULL,
125 X_project_currency_code IN OUT NOCOPY VARCHAR2,
126 X_project_rate_date IN OUT NOCOPY DATE,
127 X_project_rate_type IN OUT NOCOPY VARCHAR2,
128 X_project_exchange_rate IN OUT NOCOPY NUMBER,
129 X_project_raw_cost IN OUT NOCOPY NUMBER,
130 X_project_burdened_cost IN OUT NOCOPY NUMBER
131 /* Trx_Import Enhancement */
132 , X_override_to_oname IN VARCHAR2 default NULL,
133 X_vendor_number IN VARCHAR2 default NULL, X_org_id IN NUMBER,
134 X_Business_Group_Name IN VARCHAR2 default NULL, -- PA - I Changes,
135 X_Projfunc_currency_code IN OUT NOCOPY VARCHAR2,
136 X_Projfunc_cost_rate_date IN OUT NOCOPY DATE,
137 X_Projfunc_cost_rate_type IN OUT NOCOPY VARCHAR2,
138 X_Projfunc_cost_exchange_rate IN OUT NOCOPY NUMBER,
142 X_Work_Type_Name IN OUT NOCOPY VARCHAR2, -- PA J Changes,
139 X_actual_project_raw_cost IN OUT NOCOPY NUMBER,
140 X_actual_project_burdened_cost IN OUT NOCOPY NUMBER,
141 X_Assignment_Name IN OUT NOCOPY VARCHAR2,
146 P_person_id IN NUMBER DEFAULT NULL,
143 X_Accrual_Flag IN VARCHAR2, -- PA - L Changes,
144 P_project_id IN NUMBER DEFAULT NULL,
145 P_task_id IN NUMBER DEFAULT NULL,
147 P_organization_id IN NUMBER DEFAULT NULL,
148 P_NLR_Org_Id IN NUMBER DEFAULT NULL,
149 P_Vendor_Id IN NUMBER DEFAULT NULL,
150 P_Override_Organization_Id IN NUMBER DEFAULT NULL,
151 P_Person_business_Group_Id IN NUMBER DEFAULT NULL,
152 P_assignment_id IN NUMBER DEFAULT NULL,
153 P_work_type_id IN NUMBER DEFAULT NULL,
154 P_Emp_Org_Id IN NUMBER DEFAULT NULL,
155 P_Emp_Job_Id IN NUMBER DEFAULT NULL
156 /* Added parameter X_txn_interface_id for bug 2563364 */
157 , X_txn_interface_id IN NUMBER default NULL, P_po_number IN VARCHAR2 default NULL /* cwk */
158 , P_po_header_id IN OUT NOCOPY NUMBER,
159 P_po_line_num IN NUMBER default NULL,
160 P_po_line_id IN OUT NOCOPY NUMBER,
161 P_person_type IN VARCHAR2 default NULL,
162 P_po_price_type IN VARCHAR2 default NULL
163 /* REL12-AP Lines uptake */
164 , p_adj_exp_item_id IN NUMBER default NULL,
165 p_fc_document_type IN varchar2 default NULL
166 -- , p_agreement_id IN OUT NOCOPY NUMBER --FSIO Changes
167 -- , p_agreement_number IN OUT NOCOPY varchar2
168 -- 10253400: Payroll integration enhancement for 12.2.. start
169 ,p_job_id pa_transaction_interface_all.job_id%type default null
170 ,p_job_name pa_transaction_interface_all.job_name%type default null
171 ,p_location_id pa_transaction_interface_all.location_id%type default null
172 ,p_location_code pa_transaction_interface_all.location_code%type default null
173 ,p_pay_element_type_id pa_transaction_interface_all.pay_element_type_id%type default null
174 -- 10253400: Payroll integration enhancement for 12.2.. end
175 -- Start: Added for bug 16220146
176 ,P_CBS_ELEMENT_ID PA_TRANSACTION_INTERFACE_ALL.CBS_ELEMENT_ID%TYPE DEFAULT NULL
177 ,P_CBS_ELEMENT_NAME PA_TRANSACTION_INTERFACE_ALL.CBS_ELEMENT_NAME%TYPE DEFAULT NULL
178 -- End: Added for bug 16220146
179 );
180
181 PROCEDURE
182 ValidateOrgId (X_org_id IN NUMBER, X_status OUT NOCOPY VARCHAR2);
183
184 PROCEDURE
185 import (X_transaction_source IN VARCHAR2, X_batch IN VARCHAR2,
186 X_xface_id IN NUMBER, X_userid IN NUMBER,
187 X_online_exp_comment IN VARCHAR2 DEFAULT NULL);
188
189 PROCEDURE
190 import1 (X_transaction_source IN VARCHAR2, X_batch IN VARCHAR2,
191 X_xface_id IN NUMBER, X_userid IN NUMBER,
192 X_online_exp_comment IN VARCHAR2 DEFAULT NULL);
193
194 --SST:New APIs
195 PROCEDURE init (P_transaction_source IN VARCHAR2);
196
197
198 /*#
199 * Use the Pre-Import Client Extension to load approved self-service time cards from Internet Time into the Oracle Projects Transaction
200 * Interface Table (PA_TRANSACTION_INTERFACE_ALL). Once data is loaded in the transaction interface table,
201 * the Transaction Import Process will load the data into Oracle Projects.
202 * This client extension allows you to automate the process of loading
203 * Oracle Internet Time data to the interface table as part of the import process.
204 * @param P_transaction_source Classification of the transactions loaded into Oracle Projects from an external system.
205 * @rep:paraminfo {@rep:required}
206 * @param P_batch User entered name for grouping expenditures within a transaction source.
207 * @rep:paraminfo {@rep:required}
208 * @param P_xface_id System-generated number that identifies all the transactions processed by a given concurrent request.
209 * @rep:paraminfo {@rep:required}
210 * @param P_user_id User.
211 * @rep:paraminfo {@rep:required}
212 * @rep:scope public
213 * @rep:lifecycle active
214 * @rep:displayname Pre-import.
215 * @rep:compatibility S
216 */
217
218 PROCEDURE
219 pre_import (P_transaction_source IN VARCHAR2,
220 P_batch IN VARCHAR2, P_xface_id IN NUMBER, P_user_id IN NUMBER);
221
222
223
224 /*#
225 * Use the Post-Import Client Extension to tie back the Oracle Internet Time records that have been imported into Oracle Projects from Internet Time.
226 * Projects to the source transactions in Oracle Internet Time.
227 * Oracle Projects calls the Post-Import Client Extension for Internet Time after the Transaction Import Process runs when you use the Oracle
228 * Internet Time transaction source.
229 * @param P_transaction_source Classification of the transactions loaded into Oracle Projects from an external system.
230 * @rep:paraminfo {@rep:required}
231 * @param P_batch User entered name for grouping expenditures within a transaction source.
232 * @rep:paraminfo {@rep:required}
233 * @param P_xface_id System-generated number that identifies all the transactions processed by a given concurrent request.
234 * @rep:paraminfo {@rep:required}
235 * @param P_user_id User.
236 * @rep:paraminfo {@rep:required}
237 * @rep:scope public
238 * @rep:lifecycle active
239 * @rep:displayname Post-import.
240 * @rep:compatibility S
241 */
242 PROCEDURE
243 post_import (P_transaction_source IN VARCHAR2,
244 P_batch IN VARCHAR2, P_xface_id IN NUMBER, P_user_id IN NUMBER);
245
246 PROCEDURE
247 execute_import_extensions (P_program_name IN VARCHAR2,
248 P_transaction_source IN VARCHAR2,
249 P_batch IN VARCHAR2,
250 P_user_id IN NUMBER, P_xface_id IN NUMBER);
251
252
253 --New Package Variables for counting transactions with New TXN Codes
254
255 G_PRE_IMPORT_SUCCESS_COUNT NUMBER;
256
257 G_PRE_IMPORT_REJECT_COUNT NUMBER;
258 G_IMPORT_SUCCESS_COUNT NUMBER;
259 G_IMPORT_REJECT_COUNT NUMBER;
260 G_POST_IMPORT_SUCCESS_COUNT NUMBER;
261 G_POST_IMPORT_REJECT_COUNT NUMBER;
262 G_SKIP_TC_FLAG VARCHAR2 (1) := 'N'; --made changes for bug 1299909
263
264 G_REQUEST_ID NUMBER:= fnd_global.conc_request_id;
265 G_PROGRAM_ID NUMBER:= fnd_global.conc_program_id;
266 G_PROG_APPL_ID NUMBER:= fnd_global.PROG_APPL_ID;
267 G_CONC_LOGIN_ID NUMBER:= fnd_global.CONC_LOGIN_ID;
268 G_LOGIN_ID NUMBER:= fnd_global.LOGIN_ID;
269 G_USER_ID NUMBER:= fnd_global.USER_ID;
270
271
272 --New APIs for New TXN Codes
273 PROCEDURE count_status (P_phase IN VARCHAR2,
274 P_xface_id IN NUMBER,
275 P_sucess_counter OUT NOCOPY NUMBER,
276 P_failed_counter OUT NOCOPY NUMBER);
277
278
279 PROCEDURE
280 update_status_counter (P_xface_id IN NUMBER);
281
282 ----New global variables created for CBGA--
283
284 G_Business_Group_Id NUMBER;
285 G_Prev_Business_Group_Id NUMBER;
289 Upd_PktSts_Fatal (p_request_id in number);
286 G_Prev_Business_Group_Name VARCHAR2 (240);
287
288 PROCEDURE
290
291 PROCEDURE
292 Upd_PktSts (p_packet_id in number);
293
294 --PA - L Changes:Added ValidateItemOTL and G_Exit_Main flag.
295 PROCEDURE ValidateItemOTL (X_trx_src IN VARCHAR2, X_ei_date IN DATE,
296 X_etype IN VARCHAR2, X_nlr IN VARCHAR2,
297 X_qty IN NUMBER, X_denom_raw_cost IN NUMBER,
298 X_module IN VARCHAR2, X_trx_ref IN VARCHAR2,
299 X_match_flag IN VARCHAR2, X_att_cat IN VARCHAR2,
300 X_att1 IN OUT NOCOPY VARCHAR2,
301 X_att2 IN OUT NOCOPY VARCHAR2,
302 X_att3 IN OUT NOCOPY VARCHAR2,
303 X_att4 IN OUT NOCOPY VARCHAR2,
304 X_att5 IN OUT NOCOPY VARCHAR2,
305 X_att6 IN OUT NOCOPY VARCHAR2,
306 X_att7 IN OUT NOCOPY VARCHAR2,
307 X_att8 IN OUT NOCOPY VARCHAR2,
308 X_att9 IN OUT NOCOPY VARCHAR2,
309 X_att10 IN OUT NOCOPY VARCHAR2,
310 X_system_linkage IN VARCHAR2,
311 X_status OUT NOCOPY VARCHAR2, -- X_bill_flag OUT NOCOPY VARCHAR2,
312 X_denom_currency_code IN OUT NOCOPY VARCHAR2,
313 X_acct_rate_date IN OUT NOCOPY DATE,
314 X_acct_rate_type IN OUT NOCOPY VARCHAR2,
315 X_acct_exchange_rate IN OUT NOCOPY NUMBER,
316 X_acct_raw_cost IN NUMBER default NULL,
317 X_project_currency_code IN OUT NOCOPY VARCHAR2,
318 X_Projfunc_currency_code IN OUT NOCOPY VARCHAR2,
319 X_Projfunc_cost_rate_date IN OUT NOCOPY DATE,
320 X_Projfunc_cost_rate_type IN OUT NOCOPY VARCHAR2,
324 P_project_id IN NUMBER,
321 X_Projfunc_cost_exchange_rate IN OUT NOCOPY VARCHAR2,
322 X_Assignment_Name IN OUT NOCOPY VARCHAR2,
323 X_Work_Type_Name IN OUT NOCOPY VARCHAR2,
325 P_task_id IN NUMBER,
326 P_person_id IN NUMBER,
327 P_organization_id IN NUMBER,
328 P_assignment_id IN NUMBER,
329 P_work_type_id IN NUMBER,
330 P_Emp_Org_Id IN NUMBER,
331 P_Emp_Job_Id IN NUMBER,
332 P_po_header_id IN NUMBER, /* cwk */
333 P_po_line_id IN NUMBER,
334 P_person_type IN VARCHAR2,
335 P_po_price_type IN VARCHAR2,
336 p_vendor_id In Number ,
337 p_location_id IN NUMBER
338 ,p_cbs_element_id IN NUMBER DEFAULT NULL -- Added for bug 16592018
339 /* Bug# 3601024 : Vendor ID is not passed to the PA_EXPENDITURE_ITEMS_ALL.VENDOR_ID in OTL timecards */
340 );
341
342 G_Exit_Main BOOLEAN:= NULL;
343 G_Batch_Size NUMBER;
344 G_IterationNum NUMBER := 0;
345
346 G_Po_Line_Amt NUMBER := 0; /*cwk */
347 G_person_type VARCHAR2 (30) := 'EMP';
348
349 /*Bug 14748295*/
350 G_unmatched_negative_txn_flag pa_transaction_interface_all.unmatched_negative_txn_flag%TYPE default 'N'; -- bug 14173646
351
352 PoLineTaskTab pa_utils.Char150TabTyp;
353 PoAmtTab pa_utils.AmtTabTyp;
354
355 PROCEDURE init_po_amt_chk;
356 PROCEDURE release_po_line_task_lock;
357
358 Function
359 GET_PO_PRICE_TYPE_RATE (p_project_id In Number, p_task_id In Number,
360 P_Po_Line_Id In Number, P_Price_Type In Varchar2)
361 Return Number;
362
363 /* Added this for bug# 4138033 */
364 --S.N.Bug #4138033
365 PROCEDURE Set_GVal_ProjTskEi_Date (L_Validate_Proj_Tsk_Ei_Date IN VARCHAR2);
366
367 FUNCTION Get_GVal_ProjTskEi_Date
368 RETURN
369 VARCHAR2;
370
371 --E.N.Bug #4138033
372 -- --5235363 R12.PJ: XB4: DEV: APL:EXP ITEM DATE VALIDATIONS FOR SUPPLIER COST.
373 --
374 PROCEDURE validate_exp_date (p_project_id IN NUMBER,
375 p_task_id In NUMBER,
376 p_award_id in NUMBER,
377 p_incurred_by_org_id in number,
378 p_vendor_id in NUMBER,
379 p_person_id in number,
380 p_exp_item_date in date,
381 p_exp_type in varchar2,
382 p_system_linkage in varchar2,
383 p_txn_source in varchar2,
384 p_calling_modle in varchar2,
385 x_err_msg_cd in out nocopy varchar2,
386 x_status out nocopy varchar2);
387
388
389 END PA_TRX_IMPORT;
[Home] [Help]