1 PACKAGE PA_TRX_IMPORT AS
2 /* $Header: PAXTTRXS.pls 120.9 2006/08/15 07:38:45 cmishra noship $ */
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
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';
17
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 NUMBER,
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,
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,
142 X_Work_Type_Name IN OUT NOCOPY VARCHAR2, -- PA J Changes,
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,
146 P_person_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
167 PROCEDURE
168 ValidateOrgId (X_org_id IN NUMBER, X_status OUT NOCOPY VARCHAR2);
169
170 PROCEDURE
171 import (X_transaction_source IN VARCHAR2, X_batch IN VARCHAR2,
172 X_xface_id IN NUMBER, X_userid IN NUMBER,
173 X_online_exp_comment IN VARCHAR2 DEFAULT NULL);
174
175 PROCEDURE
176 import1 (X_transaction_source IN VARCHAR2, X_batch IN VARCHAR2,
177 X_xface_id IN NUMBER, X_userid IN NUMBER,
178 X_online_exp_comment IN VARCHAR2 DEFAULT NULL);
179
180 --SST:New APIs
181 PROCEDURE init (P_transaction_source IN VARCHAR2);
182
183
184 /*#
185 * Use the Pre-Import Client Extension to load approved self-service time cards from Internet Time into the Oracle Projects Transaction
186 * Interface Table (PA_TRANSACTION_INTERFACE_ALL). Once data is loaded in the transaction interface table,
187 * the Transaction Import Process will load the data into Oracle Projects.
188 * This client extension allows you to automate the process of loading
189 * Oracle Internet Time data to the interface table as part of the import process.
190 * @param P_transaction_source Classification of the transactions loaded into Oracle Projects from an external system.
191 * @rep:paraminfo {@rep:required}
192 * @param P_batch User entered name for grouping expenditures within a transaction source.
193 * @rep:paraminfo {@rep:required}
194 * @param P_xface_id System-generated number that identifies all the transactions processed by a given concurrent request.
195 * @rep:paraminfo {@rep:required}
196 * @param P_user_id User.
197 * @rep:paraminfo {@rep:required}
198 * @rep:scope public
199 * @rep:lifecycle active
200 * @rep:displayname Pre-import.
201 * @rep:compatibility S
202 */
203
204 PROCEDURE
205 pre_import (P_transaction_source IN VARCHAR2,
206 P_batch IN VARCHAR2, P_xface_id IN NUMBER, P_user_id IN NUMBER);
207
208
209
210 /*#
211 * Use the Post-Import Client Extension to tie back the Oracle Internet Time records that have been imported into Oracle Projects from Internet Time.
212 * Projects to the source transactions in Oracle Internet Time.
213 * Oracle Projects calls the Post-Import Client Extension for Internet Time after the Transaction Import Process runs when you use the Oracle
214 * Internet Time transaction source.
215 * @param P_transaction_source Classification of the transactions loaded into Oracle Projects from an external system.
216 * @rep:paraminfo {@rep:required}
217 * @param P_batch User entered name for grouping expenditures within a transaction source.
218 * @rep:paraminfo {@rep:required}
219 * @param P_xface_id System-generated number that identifies all the transactions processed by a given concurrent request.
220 * @rep:paraminfo {@rep:required}
221 * @param P_user_id User.
222 * @rep:paraminfo {@rep:required}
223 * @rep:scope public
224 * @rep:lifecycle active
225 * @rep:displayname Post-import.
226 * @rep:compatibility S
227 */
228 PROCEDURE
229 post_import (P_transaction_source IN VARCHAR2,
230 P_batch IN VARCHAR2, P_xface_id IN NUMBER, P_user_id IN NUMBER);
231
232 PROCEDURE
233 execute_import_extensions (P_program_name IN VARCHAR2,
234 P_transaction_source IN VARCHAR2,
235 P_batch IN VARCHAR2,
236 P_user_id IN NUMBER, P_xface_id IN NUMBER);
237
238
239 --New Package Variables for counting transactions with New TXN Codes
240
241 G_PRE_IMPORT_SUCCESS_COUNT NUMBER;
242
243 G_PRE_IMPORT_REJECT_COUNT NUMBER;
244 G_IMPORT_SUCCESS_COUNT NUMBER;
245 G_IMPORT_REJECT_COUNT NUMBER;
246 G_POST_IMPORT_SUCCESS_COUNT NUMBER;
247 G_POST_IMPORT_REJECT_COUNT NUMBER;
248 G_SKIP_TC_FLAG VARCHAR2 (1) := 'N'; --made changes for bug 1299909
249
250 G_REQUEST_ID NUMBER:= fnd_global.conc_request_id;
251 G_PROGRAM_ID NUMBER:= fnd_global.conc_program_id;
252 G_PROG_APPL_ID NUMBER:= fnd_global.PROG_APPL_ID;
253 G_CONC_LOGIN_ID NUMBER:= fnd_global.CONC_LOGIN_ID;
254 G_LOGIN_ID NUMBER:= fnd_global.LOGIN_ID;
255 G_USER_ID NUMBER:= fnd_global.USER_ID;
256
257
258 --New APIs for New TXN Codes
259 PROCEDURE count_status (P_phase IN VARCHAR2,
260 P_xface_id IN NUMBER,
261 P_sucess_counter OUT NOCOPY NUMBER,
262 P_failed_counter OUT NOCOPY NUMBER);
263
264
265 PROCEDURE
266 update_status_counter (P_xface_id IN NUMBER);
267
268 ----New global variables created for CBGA--
269
270 G_Business_Group_Id NUMBER;
271 G_Prev_Business_Group_Id NUMBER;
272 G_Prev_Business_Group_Name VARCHAR2 (240);
273
274 PROCEDURE
275 Upd_PktSts_Fatal (p_request_id in number);
276
277 PROCEDURE
278 Upd_PktSts (p_packet_id in number);
279
280 --PA - L Changes:Added ValidateItemOTL and G_Exit_Main flag.
281 PROCEDURE ValidateItemOTL (X_trx_src IN VARCHAR2, X_ei_date IN DATE,
282 X_etype IN VARCHAR2, X_nlr IN VARCHAR2,
283 X_qty IN NUMBER, X_denom_raw_cost IN NUMBER,
284 X_module IN VARCHAR2, X_trx_ref IN VARCHAR2,
285 X_match_flag IN VARCHAR2, X_att_cat IN VARCHAR2,
286 X_att1 IN OUT NOCOPY VARCHAR2,
287 X_att2 IN OUT NOCOPY VARCHAR2,
288 X_att3 IN OUT NOCOPY VARCHAR2,
289 X_att4 IN OUT NOCOPY VARCHAR2,
290 X_att5 IN OUT NOCOPY VARCHAR2,
291 X_att6 IN OUT NOCOPY VARCHAR2,
292 X_att7 IN OUT NOCOPY VARCHAR2,
293 X_att8 IN OUT NOCOPY VARCHAR2,
294 X_att9 IN OUT NOCOPY VARCHAR2,
295 X_att10 IN OUT NOCOPY VARCHAR2,
296 X_system_linkage IN VARCHAR2,
297 X_status OUT NOCOPY VARCHAR2, -- X_bill_flag OUT NOCOPY VARCHAR2,
298 X_denom_currency_code IN OUT NOCOPY VARCHAR2,
299 X_acct_rate_date IN OUT NOCOPY DATE,
300 X_acct_rate_type IN OUT NOCOPY VARCHAR2,
301 X_acct_exchange_rate IN OUT NOCOPY NUMBER,
302 X_acct_raw_cost IN NUMBER default NULL,
303 X_project_currency_code IN OUT NOCOPY VARCHAR2,
304 X_Projfunc_currency_code IN OUT NOCOPY VARCHAR2,
305 X_Projfunc_cost_rate_date IN OUT NOCOPY DATE,
306 X_Projfunc_cost_rate_type IN OUT NOCOPY VARCHAR2,
307 X_Projfunc_cost_exchange_rate IN OUT NOCOPY VARCHAR2,
308 X_Assignment_Name IN OUT NOCOPY VARCHAR2,
309 X_Work_Type_Name IN OUT NOCOPY VARCHAR2,
310 P_project_id IN NUMBER,
311 P_task_id IN NUMBER,
312 P_person_id IN NUMBER,
313 P_organization_id IN NUMBER,
314 P_assignment_id IN NUMBER,
315 P_work_type_id IN NUMBER,
316 P_Emp_Org_Id IN NUMBER,
317 P_Emp_Job_Id IN NUMBER,
318 P_po_header_id IN NUMBER, /* cwk */
319 P_po_line_id IN NUMBER,
320 P_person_type IN VARCHAR2,
321 P_po_price_type IN VARCHAR2,
322 p_vendor_id In Number
323 /* Bug# 3601024 : Vendor ID is not passed to the PA_EXPENDITURE_ITEMS_ALL.VENDOR_ID in OTL timecards */
324 );
325
326 G_Exit_Main BOOLEAN:= NULL;
327 G_Batch_Size NUMBER;
328 G_IterationNum NUMBER := 0;
329
330 G_Po_Line_Amt NUMBER := 0; /*cwk */
331 G_person_type VARCHAR2 (30) := 'EMP';
332
333 PoLineTaskTab pa_utils.Char150TabTyp;
334 PoAmtTab pa_utils.AmtTabTyp;
335
336 PROCEDURE init_po_amt_chk;
337 PROCEDURE release_po_line_task_lock;
338
339 Function
340 GET_PO_PRICE_TYPE_RATE (p_project_id In Number, p_task_id In Number,
341 P_Po_Line_Id In Number, P_Price_Type In Varchar2)
342 Return Number;
343
344 /* Added this for bug# 4138033 */
345 --S.N.Bug #4138033
346 PROCEDURE Set_GVal_ProjTskEi_Date (L_Validate_Proj_Tsk_Ei_Date IN VARCHAR2);
347
348 FUNCTION Get_GVal_ProjTskEi_Date
349 RETURN
350 VARCHAR2;
351
352 --E.N.Bug #4138033
353 -- --5235363 R12.PJ: XB4: DEV: APL:EXP ITEM DATE VALIDATIONS FOR SUPPLIER COST.
354 --
355 PROCEDURE validate_exp_date (p_project_id IN NUMBER,
356 p_task_id In NUMBER,
357 p_award_id in NUMBER,
358 p_incurred_by_org_id in number,
359 p_vendor_id in NUMBER,
360 p_person_id in number,
361 p_exp_item_date in date,
362 p_exp_type in varchar2,
363 p_system_linkage in varchar2,
364 p_txn_source in varchar2,
365 p_calling_modle in varchar2,
366 x_err_msg_cd in out nocopy varchar2,
367 x_status out nocopy varchar2);
368
369 END PA_TRX_IMPORT;