[Home] [Help]
PACKAGE BODY: APPS.PA_TRANSACTIONS
Source
1 PACKAGE BODY PA_TRANSACTIONS AS
2 /* $Header: PAXTRANB.pls 120.24.12020000.6 2013/04/03 10:48:50 utdas ship $ */
3
4 P_DEBUG_MODE BOOLEAN := pa_cc_utils.g_debug_mode;
5
6
7 -- ========================================================================
8 -- PROCEDURE LoadEi
9 -- ========================================================================
10
11
12 PROCEDURE LoadEi( X_expenditure_item_id IN NUMBER
13 , X_expenditure_id IN NUMBER
14 , X_expenditure_item_date IN DATE
15 , X_project_id IN NUMBER
16 , X_task_id IN NUMBER
17 , X_expenditure_type IN VARCHAR2
18 , X_non_labor_resource IN VARCHAR2
19 , X_nl_resource_org_id IN NUMBER
20 , X_quantity IN NUMBER
21 , X_raw_cost IN NUMBER
22 , X_raw_cost_rate IN NUMBER
23 , X_override_to_org_id IN NUMBER
24 , X_billable_flag IN VARCHAR2
25 , X_bill_hold_flag IN VARCHAR2
26 , X_orig_transaction_ref IN VARCHAR2
27 , X_transferred_from_ei IN NUMBER
28 , X_adj_expend_item_id IN NUMBER
29 , X_attribute_category IN VARCHAR2
30 , X_attribute1 IN VARCHAR2
31 , X_attribute2 IN VARCHAR2
32 , X_attribute3 IN VARCHAR2
33 , X_attribute4 IN VARCHAR2
34 , X_attribute5 IN VARCHAR2
35 , X_attribute6 IN VARCHAR2
36 , X_attribute7 IN VARCHAR2
37 , X_attribute8 IN VARCHAR2
38 , X_attribute9 IN VARCHAR2
39 , X_attribute10 IN VARCHAR2
40 , X_ei_comment IN VARCHAR2
41 , X_transaction_source IN VARCHAR2
42 , X_source_exp_item_id IN NUMBER
43 , i IN BINARY_INTEGER
44 , X_job_id IN NUMBER
45 , X_org_id IN NUMBER
46 , X_labor_cost_multiplier_name IN VARCHAR2
47 , X_drccid IN NUMBER
48 , X_crccid IN NUMBER
49 , X_cdlsr1 IN VARCHAR2
50 , X_cdlsr2 IN VARCHAR2
51 , X_cdlsr3 IN VARCHAR2
52 , X_gldate IN DATE
53 , X_bcost IN NUMBER
54 , X_bcostrate IN NUMBER
55 , X_etypeclass IN VARCHAR2
56 , X_burden_sum_dest_run_id IN NUMBER
57 , X_burden_compile_set_id IN NUMBER
58 , X_receipt_currency_amount IN NUMBER
59 , X_receipt_currency_code IN VARCHAR2
60 , X_receipt_exchange_rate IN NUMBER
61 , X_denom_currency_code IN VARCHAR2
62 , X_denom_raw_cost IN NUMBER
63 , X_denom_burdened_cost IN NUMBER
64 , X_acct_currency_code IN VARCHAR2
65 , X_acct_rate_date IN DATE
66 , X_acct_rate_type IN VARCHAR2
67 , X_acct_exchange_rate IN NUMBER
68 , X_acct_raw_cost IN NUMBER
69 , X_acct_burdened_cost IN NUMBER
70 , X_acct_exchange_rounding_limit IN NUMBER
71 , X_project_currency_code IN VARCHAR2
72 , X_project_rate_date IN DATE
73 , X_project_rate_type IN VARCHAR2
74 , X_project_exchange_rate IN NUMBER
75 , X_Cross_Charge_Type IN Varchar2
76 , X_Cross_Charge_Code IN VArchar2
77 , X_Prvdr_organization_id IN Number
78 , X_Recv_organization_id IN Number
79 , X_Recv_Operating_Unit IN Number
80 , X_Borrow_Lent_Dist_Code IN VARCHAR2
81 , X_Ic_Processed_Code IN VARCHAR2
82 , X_Denom_Tp_Currency_Code IN Varchar2
83 , X_Denom_Transfer_Price IN Number
84 , X_Acct_Tp_Rate_Type IN Varchar2
85 , X_Acct_Tp_Rate_Date IN DATE
86 , X_Acct_Tp_Exchange_Rate IN Number
87 , X_ACCT_TRANSFER_PRICE IN Number
88 , X_PROJACCT_TRANSFER_PRICE IN Number
89 , X_CC_MARKUP_BASE_CODE IN Varchar2
90 , X_TP_BASE_AMOUNT IN Number
91 , X_Tp_Ind_Compiled_Set_Id IN Number
92 , X_Tp_Bill_Rate IN Number
93 , X_Tp_Bill_Markup_Percentage IN Number
94 , X_Tp_Schedule_Line_Percentage IN Number
95 , X_Tp_Rule_Percentage IN Number
96 , X_Cost_Job_Id IN NUMBER
97 , X_Prov_Proj_Bill_Job_Id IN NUMBER
98 , X_TP_Job_Id IN NUMBER
99 , P_PaDate IN DATE
100 , P_PaPeriodName IN Varchar2
101 , P_RecvrPaDate IN DATE
102 , P_RecvrPaPeriodName IN Varchar2
103 , P_GlPeriodName IN Varchar2
104 , P_RecvrGlDate IN DATE
105 , P_RecvrGlPeriodName IN Varchar2
106 , p_assignment_id IN NUMBER
107 , p_work_type_id IN NUMBER
108 , p_projfunc_currency_code IN varchar2
109 , p_projfunc_cost_rate_date IN date
110 , p_projfunc_cost_rate_type IN varchar2
111 , p_projfunc_cost_exchange_rate IN number
112 , p_project_raw_cost IN number
113 , p_project_burdened_cost IN number
114 , p_tp_amt_type_code IN varchar2
115 , p_cdlsr4 IN varchar2
116 , p_accrual_Date IN DATE
117 , p_recvr_accrual_date IN DATE
118 , p_Wip_Resource_Id IN number
119 , p_Inventory_Item_Id IN number
120 , p_Unit_Of_Measure IN varchar2
121 , p_Po_Line_Id IN number
122 , p_Po_Price_Type IN varchar2
123 , p_adjustment_type IN varchar2
124 , p_src_system_linkage_function IN varchar2 /* 4057874 */
125 /* REL12-AP Lines uptake */
126 , p_document_header_id IN number default NULL
127 , p_document_distribution_id IN number default NULL
128 , p_document_line_number IN number default NULL
129 , p_document_payment_id IN number default NULL
130 , p_vendor_id IN number default NULL
131 , p_document_type in varchar2 default NULL
132 , p_document_distribution_type in varchar2 default NULL
133 , p_si_assets_addition_flag in varchar2 default NULL
134 , p_sc_xfer_code IN varchar2 default NULL
135 , p_cdlsr5 IN number default NULL
136 -- , p_agreement_id IN NUMBER DEFAULT NULL --FSIO Changes
137 /* 12.2 changes for payroll intg .. bug 10253400.. start */
138 , p_location_id IN NUMBER DEFAULT NULL
139 , p_pay_element_type_id IN NUMBER DEFAULT NULL
140 , p_rate_source_code IN VARCHAR2 DEFAULT NULL
141 , p_costing_method IN VARCHAR2 DEFAULT NULL
142 /* 12.2 changes for payroll intg .. bug 10253400 .. end*/
143 , p_cbs_element_id IN NUMBER DEFAULT NULL -- Added for CBS Enhancement Bug 16220146
144
145 ) IS
146 BEGIN
147 -- dbms_output.put_line('In Loadei:'||to_char(i));
148 pa_cc_utils.set_curr_function('LoadEi');
149 IF P_DEBUG_MODE THEN
150 pa_cc_utils.log_message('LoadEi: ' || 'Start ');
151 END IF;
152
153 EiIdTab(i) := X_expenditure_item_id;
154 EIdTab(i) := X_expenditure_id;
155 ProjIdTab(i) := X_project_id;
156 TskIdTab(i) := X_task_id;
157 EiDateTab(i) := X_expenditure_item_date;
158 ETypTab(i) := X_expenditure_type;
159 NlRscTab(i) := X_non_labor_resource;
160 NlRscOrgTab(i) := X_nl_resource_org_id;
161 BillFlagTab(i) := X_billable_flag;
162 BillHoldTab(i) := X_bill_hold_flag;
163 QtyTab(i) := X_quantity;
164 RawCostTab(i) := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(X_raw_cost, X_project_currency_code);
165 RawRateTab(i) := X_raw_cost_rate;
166 OvrOrgTab(i) := X_override_to_org_id;
167 AdjEiTab(i) := X_adj_expend_item_id;
168 TfrEiTab(i) := X_transferred_from_ei;
169 TrxRefTab(i) := X_orig_transaction_ref;
170 EiTrxSrcTab(i) := X_transaction_source;
171 AttCatTab(i) := X_attribute_category;
172 Att1Tab(i) := X_attribute1;
173 Att2Tab(i) := X_attribute2;
174 Att3Tab(i) := X_attribute3;
175 Att4Tab(i) := X_attribute4;
176 Att5Tab(i) := X_attribute5;
177 Att6Tab(i) := X_attribute6;
178 Att7Tab(i) := X_attribute7;
179 Att8Tab(i) := X_attribute8;
180 Att9Tab(i) := X_attribute9;
181 Att10Tab(i) := X_attribute10;
182 SrcEiTab(i) := X_source_exp_item_id;
183 EiCommentTab(i) := X_ei_comment;
184 JobIdTab(i) := X_job_id;
185 OrgIdTab(i) := X_org_id;
186 LCMTab(i) := X_labor_cost_multiplier_name ;
187 DrccidIdTab(i) := X_drccid ;
188 CrccidIdTab(i) := X_crccid ;
189 Cdlsr1Tab(i) := X_cdlsr1 ;
190 Cdlsr2Tab(i) := X_cdlsr2 ;
191 Cdlsr3Tab(i) := X_cdlsr3 ;
192 GldateTab(i) := X_gldate ;
193 BCostTab(i) := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(X_bcost,X_project_currency_code) ;
194 BCostRateTab(i) := X_bcostrate ;
195 EtypeClassTab(i) := X_etypeclass ;
196 BurdenDestid(i) := X_burden_sum_dest_run_id ;
197 BurdenCompSetId(i) := X_burden_compile_set_id;
198 ReceiptCurrAmt(i) := X_receipt_currency_amount;
199 ReceiptCurrCode(i) := X_receipt_currency_code;
200 ReceiptExRate(i) := X_receipt_exchange_rate;
201 DenomCurrCode(i) := X_denom_currency_code;
202 DenomRawCost(i) := X_denom_raw_cost;
203 DenomBurdenCost(i) := X_denom_burdened_cost;
204 AcctCurrCode(i) := X_acct_currency_code;
205 AcctRateDate(i) := X_acct_rate_date;
206 AcctRateType(i) := X_acct_rate_type;
207 AcctExRate(i) := X_acct_exchange_rate;
208 AcctRawCost(i) := X_acct_raw_cost;
209 AcctBurdenCost(i) := X_acct_burdened_cost;
210 AcctRoundLmt(i) := X_acct_exchange_rounding_limit;
211 ProjCurrCode(i) := X_project_currency_code;
212 ProjRateDate(i) := X_project_rate_date;
213 ProjRateType(i) := X_project_rate_type;
214 ProjExRate(i) := X_project_exchange_rate;
215
216
217 -- IC Changes
218 CrossChargeTypeTab(i) := X_Cross_Charge_Type;
219 CrossChargeCodeTab(i) := X_Cross_Charge_Code;
220 PrvdrOrganizationTab(i) := X_Prvdr_organization_id;
221 RecvOrganizationTab(i) := X_Recv_organization_id;
222 RecvOperUnitTab(i) := NVL(X_Recv_Operating_Unit,
223 PA_UTILS2.GetPrjOrgId(X_project_id,X_task_id));
224 IcProcessedCodeTab(i) := X_Ic_Processed_Code;
225 BorrowLentCodeTab(i) := X_Borrow_Lent_Dist_Code;
226 DenomTpCurrCodeTab(i) := X_Denom_Tp_Currency_Code;
227 DenomTransferPriceTab(i) := X_Denom_Transfer_Price;
228 AcctTpRateTypeTab(i) := X_Acct_Tp_Rate_Type;
229 AcctTpRateDateTab(i) := X_Acct_Tp_Rate_Date;
230 AcctTpExchangeRateTab(i) := X_Acct_Tp_Exchange_Rate;
231 AcctTransferPriceTab(i) := X_ACCT_TRANSFER_PRICE;
232 ProjacctTransferPriceTab(i) := X_PROJACCT_TRANSFER_PRICE;
233 CcMarkupBaseCodeTab(i) := X_CC_MARKUP_BASE_CODE;
234 TpBaseAmountTab(i) := X_TP_BASE_AMOUNT;
235 TpIndCompiledSetIdTab(i) := X_Tp_Ind_Compiled_Set_Id;
236 TpBillRateTab(i) := X_Tp_Bill_Rate;
237 TpBillMarkupPercentageTab(i) := X_Tp_Bill_Markup_Percentage;
238 TpSchLinePercentageTab(i) := X_Tp_Schedule_Line_Percentage;
239 TpRulePercentageTab(i) := X_Tp_Rule_Percentage;
240 -- END IC Changes
241
242 -- New Jobs Model
243 CostJobIdTab(i) := X_Cost_Job_Id;
244 ProvProjBillJobIdTab(i) := X_Prov_Proj_Bill_Job_Id;
245 TPJobIdTab(i) := X_TP_Job_Id;
246 -- End New Jobs Model
247
248 -- Begin EPP changes
249 PaDateTab(i) := P_PaDate;
250 PaPeriodNameTab(i) := P_PaPeriodName;
251 RecvrPaDateTab(i) := P_RecvrPaDate;
252 RecvrPaPeriodNameTab(i) := P_RecvrPaPeriodName;
253 GlPeriodNameTab(i) := P_GlPeriodName;
254 RecvrGlDateTab(i) := P_RecvrGlDate;
255 RecvrGlPeriodNameTab(i) := P_RecvrGlPeriodName;
256 -- End EPP changes
257
258 -- start of project currency and EI attributes
259 AssgnIDTab(i) := p_assignment_id ;
260 WorkTypeTab(i) := p_work_type_id ;
261 ProjFuncCurrencyTab(i) := p_projfunc_currency_code;
262 ProjFuncCostRateDateTab(i) := p_projfunc_cost_rate_date;
263 ProjFunccostRateTypeTab(i) := p_projfunc_cost_rate_type ;
264 ProjfuncCostExgRateTab(i) := p_projfunc_cost_exchange_rate;
265 ProjRawCostTab(i) := p_project_raw_cost;
266 ProjBurdendCostTab(i) := p_project_burdened_cost;
267 TpAmtTypeCode(i) := nvl(p_tp_amt_type_code,pa_utils4.get_tp_amt_type_code(p_work_type_id));
268 -- End of project currency and EI attributes
269
270 -- AP Discounts
271 Cdlsr4Tab(i) := p_cdlsr4;
272
273 /* REL12-AP Lines uptake */
274 DocumentHeaderIDTab(i) := p_document_header_id ;
275 DocumentDistributionIdTab(i) := p_document_distribution_id ;
276 DocumentLineNumberTab(i) := p_document_line_number ;
277 DocumentPaymentIdTab(i) := p_document_payment_id ;
278 VendorIdTab(i) := p_vendor_id ;
279 DocumentTypeTab(i) := p_document_type ;
280 DocumentDistributionTypeTab(i):= p_document_distribution_type ;
281 SiAssetsAddFlagTab(i) := p_si_assets_addition_flag ;
282 ScXferCodeTab(i) := p_sc_xfer_code ;
283 Cdlsr5Tab(i) := p_cdlsr5;
284
285 -- Begin PA-J Period End Accrual Changes
286 AccrualDateTab(i) := p_accrual_date;
287 RecvrAccrualDateTab(i) := p_recvr_accrual_date;
288 -- End PA-J Period End Accrual Changes
289
290 --begin PA.M contingent worker and PJM attribute chnages
291 Wip_Resource_IdTab(i) := p_Wip_Resource_Id;
292 Inventory_Item_IdTab(i) := p_Inventory_Item_Id;
293 Unit_Of_MeasureTab(i) := p_Unit_Of_Measure;
294 Po_Line_IdTab(i) := p_Po_Line_Id;
295 Po_Price_TypeTab(i) := p_Po_Price_Type;
296 Adjustment_TypeTab(i) := p_adjustment_type;
297 --end PA.M contingent worker and PJM attribute chnages
298
299 SrcEtypeClassTab(i) := p_src_system_linkage_function; /* 4057874 */
300
301 -- Agreement_idTab(i) := p_agreement_id; --FSIO Changes
302
303 /* 10253400: Payroll integration enhancement for 12.2. . start */
304
305 Location_IdTab(i) := p_location_id;
306 Pay_Element_IdTab(i) := p_pay_element_type_id;
307 Rate_Source_CodeTab(i) := p_rate_source_code;
308 Costing_Method_Tab(i) := p_costing_method;
309
310 /* 10253400: Payroll integration enhancement for 12.2.. end */
311
312 /* Start changes for Bug#12606242 */
313
314 Payroll_Accrual_Flag(i) := null;
315 Rbc_Element_Type_Id(i) := null;
316 Interface_Run_Id(i) := null;
317
318 cbs_element_idtab(i) := p_cbs_element_id; -- Added for CBS Enhancement Bug 16220146
319
320
321 IF (X_transaction_source = 'ORACLE TIME AND LABOR') Then
322
323 IF (X_adj_expend_item_id is not null) THEN
324
325 select Payroll_Accrual_Flag,Rbc_Element_Type_Id,Interface_Run_Id,
326 Rate_Source_Code,Costing_Method into
327 Payroll_Accrual_Flag(i),
328 Rbc_Element_Type_Id(i),
329 Interface_Run_Id(i),
330 Rate_Source_CodeTab(i),
331 Costing_Method_Tab(i) from pa_expenditure_items
332 where expenditure_item_id = X_adj_expend_item_id;
333
334
335 END IF;
336
337 END IF;
338
339 /* End changes for Bug#12606242 */
340
341 IF P_DEBUG_MODE THEN
342 pa_cc_utils.log_message('LoadEi: ' || 'End ');
343 END IF;
344 pa_cc_utils.reset_curr_function ;
345 END LoadEi;
346
347
348 -- ========================================================================
349 -- PROCEDURE FlushEiTabs
350 -- ========================================================================
351
352 PROCEDURE FlushEiTabs
353 IS
354 BEGIN
355 pa_cc_utils.set_curr_function('FlushEiTabs');
356 pa_cc_utils.log_message('Start ');
357
358 EiIdTab := pa_utils.EmptyIdTab;
359 EIdTab := pa_utils.EmptyIdTab;
360 TskIdTab := pa_utils.EmptyIdTab;
361 EiDateTab := pa_utils.EmptyDateTab;
362 ETypTab := pa_utils.EmptyChar30Tab;
363 NlRscTab := pa_utils.EmptyChar20Tab;
364 NlRscOrgTab := pa_utils.EmptyIdTab;
365 BillFlagTab := pa_utils.EmptyChar1Tab;
366 BillHoldTab := pa_utils.EmptyChar1Tab;
367 QtyTab := pa_utils.EmptyAmtTab;
368 RawCostTab := pa_utils.EmptyAmtTab;
369 RawRateTab := pa_utils.EmptyAmtTab;
370 OvrOrgTab := pa_utils.EmptyIdTab;
371 AdjEiTab := pa_utils.EmptyIdTab;
372 TfrEiTab := pa_utils.EmptyIdTab;
373 TrxRefTab := pa_utils.EmptyChar50Tab; -- Changed for Bug 12371923 from EmptyChar30Tab to EmptyChar50Tab
374 EiTrxSrcTab := pa_utils.EmptyChar30Tab;
375 AttCatTab := pa_utils.EmptyChar30Tab;
376 Att1Tab := pa_utils.EmptyChar150Tab;
377 Att2Tab := pa_utils.EmptyChar150Tab;
378 Att3Tab := pa_utils.EmptyChar150Tab;
379 Att4Tab := pa_utils.EmptyChar150Tab;
380 Att5Tab := pa_utils.EmptyChar150Tab;
381 Att6Tab := pa_utils.EmptyChar150Tab;
382 Att7Tab := pa_utils.EmptyChar150Tab;
383 Att8Tab := pa_utils.EmptyChar150Tab;
384 Att9Tab := pa_utils.EmptyChar150Tab;
385 Att10Tab := pa_utils.EmptyChar150Tab;
386 SrcEiTab := pa_utils.EmptyIdTab;
387 EiCommentTab := pa_utils.EmptyChar240Tab;
388 JobIdTab := pa_utils.EmptyIdTab;
389 OrgIdTab := pa_utils.EmptyIdTab;
390 LCMTab := pa_utils.EmptyChar20Tab ;
391 DrccidIdTab := pa_utils.EmptyIdTab ;
392 CrccidIdTab := pa_utils.EmptyIdTab ;
393 Cdlsr1Tab := pa_utils.EmptyChar30Tab ;
394 Cdlsr2Tab := pa_utils.EmptyChar30Tab ;
395 Cdlsr3Tab := pa_utils.EmptyChar30Tab ;
396 GldateTab := pa_utils.EmptyDateTab ;
397 BCostTab := pa_utils.EmptyAmtTab;
398 BCostRateTab := pa_utils.EmptyAmtTab;
399 EtypeClassTab := pa_utils.EmptyChar30Tab ;
400 BurdenDestId := pa_utils.EmptyIdTab ;
401 BurdenCompSetId := pa_utils.EmptyIdTab ;
402 ReceiptCurrAmt := pa_utils.EmptyNewAmtTab;
403 ReceiptCurrCode := pa_utils.EmptyChar15Tab;
404 ReceiptExRate := pa_utils.EmptyNewAmtTab;
405 DenomCurrCode := pa_utils.EmptyChar15Tab;
406 DenomRawCost := pa_utils.EmptyNewAmtTab;
407 DenomBurdenCost := pa_utils.EmptyNewAmtTab;
408 AcctCurrCode := pa_utils.EmptyChar15Tab;
409 AcctRateDate := pa_utils.EmptyDateTab;
410 AcctRateType := pa_utils.EmptyChar30Tab ;
411 AcctExRate := pa_utils.EmptyNewAmtTab;
412 AcctRawCost := pa_utils.EmptyNewAmtTab;
413 AcctBurdenCost := pa_utils.EmptyNewAmtTab;
414 AcctRoundLmt := pa_utils.EmptyNewAmtTab;
415 ProjCurrCode := pa_utils.EmptyChar15Tab;
416 ProjRateType := pa_utils.EmptyChar30Tab ;
417 ProjRateDate := pa_utils.EmptyDateTab;
418 ProjExRate := pa_utils.EmptyNewAmtTab;
419
420 -- IC Changes
421 CrossChargeTypeTab := pa_utils.EmptyChar10Tab ;
422 CrossChargeCodeTab := pa_utils.EmptyChar1Tab ;
423 PrvdrOrganizationTab := pa_utils.EmptyIdTab ;
424 RecvOrganizationTab := pa_utils.EmptyIdTAb ;
425 RecvOperUnitTab := pa_utils.EmptyIdTab ;
426 IcProcessedCodeTab := pa_utils.EmptyChar1Tab ;
427 BorrowLentCodeTab := pa_utils.EmptyChar1Tab ;
428 DenomTpCurrCodeTab := pa_utils.EmptyChar15Tab;
429 DenomTransferPriceTab := pa_utils.EmptyNewAmtTab;
430 AcctTpRateTypeTab := pa_utils.EmptyChar30Tab;
431 AcctTpRateDateTab := pa_utils.EmptyDateTab;
432 AcctTpExchangeRateTab := pa_utils.EmptyNewAmtTab;
433 AcctTransferPriceTab := pa_utils.EmptyNewAmtTab;
434 ProjacctTransferPriceTab := pa_utils.EmptyNewAmtTab;
435 CcMarkupBaseCodeTab := pa_utils.EmptyChar1TAb;
436 TpBaseAmountTab := pa_utils.EmptyNewAmtTab;
437 TpIndCompiledSetIdTab := pa_utils.EmptyIdTab;
438 TpBillRateTab := pa_utils.EmptyNewAmtTab;
439 TpBillMarkupPercentageTab := pa_utils.EmptyAmtTab;
440 TpSchLinePercentageTab := pa_utils.EmptyAmtTab;
441 TpRulePercentageTab := pa_utils.EmptyAmtTab;
442 --END IC Changes
443
444 -- New Jobs Model changes for 11i
445 CostJobIdTab := pa_utils.EmptyIdTab;
446 TPJobIdTab := pa_utils.EmptyIdTab;
447 ProvProjBillJobIdTab := pa_utils.EmptyIdTab;
448 -- End New Jobs Model changes for 11i
449
450 -- Begin EPP changes
451 PaDateTab := pa_utils.EmptyDateTab ;
452 PaPeriodNameTab := pa_utils.EmptyChar15Tab;
453 RecvrPaDateTab := pa_utils.EmptyDateTab ;
454 RecvrPaPeriodNameTab := pa_utils.EmptyChar15Tab;
455 GlPeriodNameTab := pa_utils.EmptyChar15Tab;
456 RecvrGlDateTab := pa_utils.EmptyDateTab ;
457 RecvrGlPeriodNameTab := pa_utils.EmptyChar15Tab;
458 -- End EPP changes
459
460 -- start of project currency and EI attributes
461 AssgnIDTab := pa_utils.EmptyIdTab;
462 WorkTypeTab := pa_utils.EmptyIdTab;
463 ProjFuncCurrencyTab := pa_utils.EmptyChar15Tab;
464 ProjFuncCostRateDateTab := pa_utils.EmptyDateTab ;
465 ProjFunccostRateTypeTab := pa_utils.EmptyChar30Tab;
466 ProjfuncCostExgRateTab := pa_utils.EmptyNewAmtTab;
467 ProjRawCostTab := pa_utils.EmptyNewAmtTab;
468 ProjBurdendCostTab := pa_utils.EmptyNewAmtTab;
469 TpAmtTypeCode := pa_utils.EmptyChar30Tab;
470 -- End of project currency and EI attributes
471
472 -- AP Discounts
473 Cdlsr4Tab := pa_utils.EmptyChar30Tab ;
474
475 -- Begin PA-J Period End Accrual Changes
476 AccrualDateTab := pa_utils.EmptyDateTab;
477 RecvrAccrualDateTab := pa_utils.EmptyDateTab;
478 -- End PA-J Period End Accrual Changes
479
480 --begin PA.M contingent worker and PJM attribute chnages
481 Wip_Resource_IdTab := pa_utils.EmptyIdTab;
482 Inventory_Item_IdTab := pa_utils.EmptyIdTab;
483 Unit_Of_MeasureTab := pa_utils.EmptyChar30Tab ;
484 Po_Line_IdTab := pa_utils.EmptyIdTab;
485 Po_Price_TypeTab := pa_utils.EmptyChar30Tab ;
486 Adjustment_TypeTab := pa_utils.EmptyChar150Tab ;
487 --end PA.M contingent worker and PJM attribute chnages
488
489 SrcEtypeClassTab := pa_utils.EmptyChar30Tab ; /* 4057874 */
490
491 /* REL12-AP Lines uptake Start*/
492 DocumentHeaderIDTab := pa_utils.EmptyIdTab;
493 DocumentDistributionIdTab := pa_utils.EmptyIdTab;
494 DocumentLineNumberTab := pa_utils.EmptyIdTab;
495 DocumentPaymentIDTab := pa_utils.EmptyIdTab;
496 VendorIdTab := pa_utils.EmptyNewAmtTab; -- Bug 12591580 Changed from EmptyIdTab to EmptyNewAmtTab
497 DocumentTypeTab := pa_utils.EmptyChar30Tab ;
498 DocumentDistributionTypeTab:= pa_utils.EmptyChar30Tab ;
499 SiAssetsAddFlagTab := pa_utils.EmptyChar1Tab ;
500 ScxferCodeTab := pa_utils.EmptyChar1Tab ;
501 Cdlsr5Tab := pa_utils.EmptyIdTab ;
502
503 /* REL12-AP Lines uptake END*/
504 -- Agreement_idTab := pa_utils.EmptyIdTab ; --FSIO Changes
505
506
507 /* 10253400: Payroll integration enhancement for 12.2.. start*/
508 Location_IdTab := pa_utils.EmptyIdTab;
509 Pay_Element_IdTab := pa_utils.EmptyIdTab;
510 Rate_Source_CodeTab := pa_utils.EmptyChar30Tab;
511 Costing_Method_Tab := pa_utils.EmptyChar150Tab ;
512 /* 10253400: Payroll integration enhancement for 12.2.. end */
513
514 /* Start changes for Bug#12606242 */
515
516 Payroll_Accrual_Flag := pa_utils.EmptyChar1Tab;
517 Rbc_Element_Type_Id := pa_utils.EmptyIdTab;
518 Interface_Run_Id := pa_utils.EmptyIdTab;
519
520 /* End changes for Bug#12606242 */
521
522 cbs_element_idtab := PA_PLSQL_DATATYPES.EmptyNum15Tab; -- Added for CBS Enhancement Bug 16220146
523
524 pa_cc_utils.log_message('End ');
525 pa_cc_utils.reset_curr_function ;
526
527 END FlushEiTabs;
528
529
530
531 -- ========================================================================
532 -- PROCEDURE InsItemComment
533 -- ========================================================================
534
535 PROCEDURE InsItemComment ( X_ei_id IN NUMBER
536 , X_ei_comment IN VARCHAR2
537 , X_user IN NUMBER
538 , X_login IN NUMBER
539 , X_status OUT NOCOPY NUMBER )
540 IS
541 BEGIN
542 pa_cc_utils.set_curr_function('InsItemComment');
543 IF P_DEBUG_MODE THEN
544 pa_cc_utils.log_message('InsItemComment: ' || 'Start ');
545 END IF;
546
547 INSERT INTO pa_expenditure_comments (
548 expenditure_item_id
549 , line_number
550 , expenditure_comment
551 , last_update_date
552 , last_updated_by
553 , creation_date
554 , created_by
555 , last_update_login )
556 VALUES (
557 X_ei_id -- expenditure_item_id
558 , 10 -- line_number
559 , X_ei_comment -- expenditure_comment
560 , sysdate -- last_update_date
561 , X_user -- last_updated_by
562 , sysdate -- creation_date
563 , X_user -- created_by
564 , X_login ); -- last_update_login
565
566 X_status := 0;
567 IF P_DEBUG_MODE THEN
568 pa_cc_utils.log_message('InsItemComment: ' || 'End ');
569 END IF;
570 pa_cc_utils.reset_curr_function ;
571
572 EXCEPTION
573 WHEN OTHERS THEN
574 X_status := SQLCODE;
575 RAISE;
576
577 END InsItemComment;
578
579
580 -- ========================================================================
581 -- PROCEDURE InsItems
582 -- ========================================================================
583
584 PROCEDURE InsItems( X_user IN NUMBER
585 , X_login IN NUMBER
586 , X_module IN VARCHAR2
587 , X_calling_process IN VARCHAR2
588 , Rows IN BINARY_INTEGER
589 , X_status OUT NOCOPY NUMBER
590 , X_gl_flag IN VARCHAR2 )
591 IS
592 temp_status NUMBER DEFAULT NULL;
593
594 -- Added the following variables to update the request_id, program_id and program_app_id cols.
595 -- Selva 03/13/97
596
597 x_request_id NUMBER(15);
598 x_program_application_id NUMBER(15);
599 x_program_id NUMBER(15);
600 x_err_code NUMBER(7) DEFAULT 0 ;
601 x_err_stage VARCHAR2(255) ;
602 x_err_stack VARCHAR2(255) ;
603 l_pa_date DATE;
604 l_recvr_pa_date DATE; /**CBGA**/
605
606 l_x_gl_flag VARCHAR2(2);
607 -- Oct 2001 Enhanced Period Processing
608 -- Start EPP Changes
609 l_PaPeriodName pa_cost_distribution_lines_all.pa_period_name%TYPE;
610 l_RecvrPaPeriodName pa_cost_distribution_lines_all.recvr_pa_period_name%TYPE;
611 l_GlPeriodName pa_cost_distribution_lines_all.gl_period_name%TYPE;
612 l_RecvrGlDate pa_cost_distribution_lines_all.recvr_gl_date%TYPE;
613 l_RecvrGlPeriodName pa_cost_distribution_lines_all.recvr_gl_period_name%TYPE;
614 l_SobId pa_implementations_all.set_of_books_id%TYPE;
615 l_RecvrSobId pa_implementations_all.set_of_books_id%TYPE;
616
617 x_return_status NUMBER;
618 x_error_code VARCHAR2(100);
619 x_error_stage NUMBER;
620 -- End EPP Changes
621
622 BackoutItemID pa_utils.IdTabTyp; -- Bug 5501593
623 item_comment VARCHAR2(240); -- Bug 5501593
624
625 BEGIN
626 pa_cc_utils.set_curr_function('InsItems');
627 IF P_DEBUG_MODE THEN
628 pa_cc_utils.log_message('InsItems: ' || 'Start ');
629 END IF;
630
631 /* Bug Fix: 3258043 If the transaction import process calls insItems with gl_accounted_flag = 'Y'
632 * then donot override the GL period information
633 * Initialize the global variable gl_accounted_flag */
634 PA_TRANSACTIONS.GL_ACCOUNTED_FLAG := NVL(X_gl_flag,'N') ;
635
636 X_request_id := FND_GLOBAL.CONC_REQUEST_ID ;
637 X_program_id := FND_GLOBAL.CONC_PROGRAM_ID ;
638 X_program_application_id := FND_GLOBAL.PROG_APPL_ID ;
639
640 --PA-K Changes: Modified EI insert into bulk insert
641 IF P_DEBUG_MODE THEN
642 pa_cc_utils.log_message('InsItems: ' || 'Start of bulk insert for EI insertion');
643 END IF;
644 FORALL i IN 1..Rows ---{
645 INSERT INTO pa_expenditure_items_all (
646 expenditure_item_id
647 , expenditure_id
648 , expenditure_item_date
649 , task_id
650 , expenditure_type
651 , cost_distributed_flag
652 , revenue_distributed_flag
653 , billable_flag
654 , bill_hold_flag
655 , net_zero_adjustment_flag
656 , non_labor_resource
657 , organization_id
658 , quantity
659 , raw_cost -- project functional raw cost
660 , raw_cost_rate
661 , override_to_organization_id
662 , orig_transaction_reference
663 , transaction_source
664 , adjusted_expenditure_item_id
665 , attribute_category
666 , attribute1
667 , attribute2
668 , attribute3
669 , attribute4
670 , attribute5
671 , attribute6
672 , attribute7
673 , attribute8
674 , attribute9
675 , attribute10
676 , source_expenditure_item_id
677 , transferred_from_exp_item_id
678 , last_update_date
679 , last_updated_by
680 , creation_date
681 , created_by
682 , last_update_login
683 , job_id
684 , org_id
685 , labor_cost_multiplier_name
686 , cost_burden_distributed_flag
687 , burden_cost -- project functional burden cost
688 , burden_cost_rate
689 , request_id
690 , program_application_id
691 , program_id
692 , system_linkage_function
693 , burden_sum_dest_run_id
694 , cost_ind_compiled_set_id
695 , receipt_currency_amount
696 , receipt_currency_code
697 , receipt_exchange_rate
698 , denom_currency_code
699 , denom_raw_cost
700 , denom_burdened_cost
701 , acct_currency_code
702 , acct_rate_date
703 , acct_rate_type
704 , acct_exchange_rate
705 , acct_raw_cost
706 , acct_burdened_cost
707 , acct_exchange_rounding_limit
708 , project_currency_code -- project currency code
709 , project_rate_date -- project rate date
710 , project_rate_type -- project rate type
711 , project_exchange_rate -- project exchange rate
712 , CC_CROSS_CHARGE_TYPE
713 , CC_CROSS_CHARGE_CODE
714 , CC_PRVDR_ORGANIZATION_ID
715 , CC_RECVR_ORGANIZATION_ID
716 , RECVR_ORG_ID
717 , CC_BL_DISTRIBUTED_CODE
718 , CC_IC_PROCESSED_CODE
719 , DENOM_TP_CURRENCY_CODE
720 , DENOM_TRANSFER_PRICE
721 , ACCT_TP_RATE_TYPE
722 , ACCT_TP_RATE_DATE
723 , ACCT_TP_EXCHANGE_RATE
724 , ACCT_TRANSFER_PRICE
725 , PROJACCT_TRANSFER_PRICE
726 , CC_MARKUP_BASE_CODE
727 , TP_BASE_AMOUNT
728 , TP_IND_COMPILED_SET_ID
729 , TP_BILL_RATE
730 , TP_BILL_MARKUP_PERCENTAGE
731 , TP_SCHEDULE_LINE_PERCENTAGE
732 , TP_RULE_PERCENTAGE
733 , COST_JOB_ID
734 , TP_JOB_ID
735 , PROV_PROJ_BILL_JOB_ID
736 , ASSIGNMENT_ID -- assignment id
737 , WORK_TYPE_ID -- work type
738 , PROJFUNC_CURRENCY_CODE -- project functional currency
739 , PROJFUNC_COST_RATE_TYPE -- project functional rate
740 , PROJFUNC_COST_RATE_DATE -- project functional rate date
741 , PROJFUNC_COST_EXCHANGE_RATE -- project functional exchange rate
742 , PROJECT_RAW_COST
743 , PROJECT_BURDENED_COST
744 , PROJECT_ID
745 , TP_AMT_TYPE_CODE
746 , prvdr_accrual_date
747 , recvr_accrual_date
748 , Wip_Resource_Id
749 , Inventory_Item_Id
750 , Unit_Of_Measure
751 , Po_Line_Id
752 , Po_Price_Type
753 , Adjustment_Type
754 , Src_System_Linkage_Function -- 4057874
755 /* REL12-AP Lines uptake Start*/
756 , Document_header_id
757 , Document_distribution_ID
758 , Document_Line_number
759 , Document_Payment_ID
760 , Document_type
761 , Document_distribution_type
762 , Vendor_id
763 , historical_flag
764 /* REL12-AP Lines uptake END*/
765 -- , agreement_id --FSIO Chnages
766 /* 10253400: Payroll integration enhancement for 12.2..start */
767 , location_id
768 , pay_element_type_id
769 , rate_source_code
770 , costing_method
771 /* 10253400: Payroll integration enhancement for 12.2..end */
772 ,Payroll_Accrual_Flag, /* Start changes for Bug#12606242 */
773 Rbc_Element_Type_Id,
774 Interface_Run_Id /* End changes for Bug#12606242 */
775 , cbs_element_id -- Added for CBS Enhancement Bug 16220146
776 )
777 VALUES (
778 EiIdTab(i) -- expenditure_item_id
779 , EIdTab(i) -- expenditure_id
780 , EiDateTab(i) -- expenditure_item_date
781 , TskIdTab(i) -- task_id
782 , ETypTab(i) -- expenditure_type
783 , decode(nvl(X_gl_flag,'N'), 'Y', 'Y','P','Y', 'N') -- cost_distributed_flag, changed for cost blue print project.
784 , 'N' -- revenue_distributed_flag
785 , BillFlagTab(i) -- billable_flag
786 , BillHoldTab(i) -- bill_hold_flag
787 , decode( AdjEiTab(i),
788 NULL, 'N', 'Y' ) -- net_zero_adjustment_flag
789 , NlRscTab(i) -- non_labor_resource
790 , NlRscOrgTab(i) -- organization_id
791 , QtyTab(i) -- quantity
792 , RawCostTab(i) -- raw_cost
793 , RawRateTab(i) -- raw_cost_rate
794 , OvrOrgTab(i) -- override_to_organization_id
795 , TrxRefTab(i) -- orig_transaction_reference
796 , EiTrxSrcTab(i) -- transaction_source
797 , AdjEiTab(i) -- adjusted_expenditure_item_id
798 , AttCatTab(i) -- attribute_category
799 , Att1Tab(i) -- attribute1
800 , Att2Tab(i) -- attribute2
801 , Att3Tab(i) -- attribute3
802 , Att4Tab(i) -- attribute4
803 , Att5Tab(i) -- attribute5
804 , Att6Tab(i) -- attribute6
805 , Att7Tab(i) -- attribute7
806 , Att8Tab(i) -- attribute8
807 , Att9Tab(i) -- attribute9
808 , Att10Tab(i) -- attribute10
809 , SrcEiTab(i) -- source_expenditure_item_id
810 , TfrEiTab(i) -- transferred_from_exp_item_id
811 , sysdate -- last_update_date
812 , X_user -- last_updated_by
813 , sysdate -- creation_date
814 , X_user -- created_by
815 , X_login -- last_update_login
816 , JobIdTab(i) -- job_id
817 , OrgIdTab(i) -- org_id
818 , LCMTab(i) -- labor_cost_multiplier_name
819 --, decode(EtypeClassTab(i),'VI',decode(BurdenCompSetId(i),NULL,'X','N'),'N')
820 --PA-K Changes
821 --For all system linkages, base the cost burden dist flag on the compile set id
822 , decode(BurdenCompSetId(i),NULL,'X','N')
823 -- cost burden distributed flag modified for bug #1978887
824 , BCostTab(i) -- Burdened_cost
825 , BCostRateTab(i) -- Burdened_cost_rate
826 , x_request_id -- Request Id
827 , x_program_application_id -- Program Application Id
828 , x_program_id -- Program Id
829 , EtypeClassTab(i) -- System Linkage Function
830 , BurdenDestId(i) -- Burden Summarization Dest Run Id
831 , BurdenCompSetId(i) -- Burden compile set id
832 , ReceiptCurrAmt(i) -- Receipt Currency Amount
833 , ReceiptCurrCode(i) -- receipt Currency Code
834 , ReceiptExRate(i) -- Receipt Exchange Rate
835 , DenomCurrCode(i) -- Denomination Currency Code
836 , DenomRawCost(i) -- Denomination Raw Cost
837 , DenomBurdenCost(i) -- Denomination Burden Cost
838 , AcctCurrCode(i) -- Accounting Currency Code
839 , AcctRateDate(i) -- Accounting currency Rate Date
840 , AcctRateType(i) -- Accounting Currency Rate Type
841 , AcctExRate(i) -- Accounting Currency Exchange Rate
842 , AcctRawCost(i) -- Accounting Currency Raw Cost
843 , AcctBurdenCost(i) -- Accounting Currency Burden Cost
844 , AcctRoundLmt(i) -- Accounting Currency Conversion Rounding Limit
845 , ProjCurrCode(i) -- project Currency Code
846 , ProjRateDate(i) -- Prohect Currency rate date
847 , ProjRateType(i) -- project currency rate type
848 , ProjExRate(i) -- project currency exchange rate
849 , CrossChargeTypeTab(i)
850 , CrossChargeCodeTab(i)
851 , PrvdrOrganizationTab(i)
852 , RecvOrganizationTab(i)
853 , RecvOperUnitTab(i)
854 , BorrowLentCodeTab(i)
855 , IcProcessedCodeTab(i)
856 , DenomTpCurrCodeTab(i)
857 , DenomTransferPriceTab(i)
858 , AcctTpRateTypeTab(i)
859 , AcctTpRateDateTab(i)
860 , AcctTpExchangeRateTab(i)
861 , AcctTransferPriceTab(i)
862 , ProjacctTransferPriceTab(i)
863 , CcMarkupBaseCodeTab(i)
864 , TpBaseAmountTab(i)
865 , TpIndCompiledSetIdTab(i)
866 , TpBillRateTab(i)
867 , TpBillMarkupPercentageTab(i)
868 , TpSchLinePercentageTab(i)
869 , TpRulePercentageTab(i)
870 , CostJobIdTab(i)
871 , ProvProjBillJobIdTab(i)
872 , TPJobIdTab(i)
873 , AssgnIDTab(i) -- assignment id
874 , WorkTypeTab(i) -- work type id
875 , ProjFuncCurrencyTab(i) -- project functional currency
876 , ProjFunccostRateTypeTab(i) -- project functional rate type
877 , ProjFuncCostRateDateTab(i) -- project funcational rate date
878 , ProjfuncCostExgRateTab(i) -- project functional exchange rate
879 , ProjRawCostTab(i) -- project raw cost
880 , ProjBurdendCostTab(i) -- project burened cost
881 , ProjIdTab(i)
882 , TpAmtTypeCode(i)
883 , AccrualDateTab(i)
884 , RecvrAccrualDateTab(i)
885 , Wip_Resource_IdTab(i)
886 , Inventory_Item_IdTab(i)
887 , Unit_Of_MeasureTab(i)
888 , Po_Line_IdTab(i)
889 , Po_Price_TypeTab(i)
890 , Adjustment_TypeTab(i)
891 , SrcEtypeClassTab(i) -- 4057874
892 /* REL12-AP Lines uptake Start*/
893 , DocumentHeaderidTab(i)
894 , DocumentDistributionIDTab(i)
895 , DocumentLinenumberTab(i)
896 -- Bug: 5443263
897 -- R12.PJ:XB7:QA:APL:UPG:ADJUSTMENT REVERSAL NOT GETTING ACCOUNTED IN CASH
898 -- Cash basis invoice distribution interfaces to projects not payments for historical invoices.
899 -- (DocumentPaymentIDTab(i) value -1 indicates historical invoices.
900 --
901 , DECODE(DocumentPaymentIDTab(i), -1, NULL, DocumentPaymentIDTab(i) )
902 , DocumentTypeTab(i)
903 , DocumentDistributionTypeTab(i)
904 , VendoridTab(i)
905 -- Bug: 5443263
906 -- R12.PJ:XB7:QA:APL:UPG:ADJUSTMENT REVERSAL NOT GETTING ACCOUNTED IN CASH
907 -- Cash basis invoice distribution interfaces to projects not payments for historical invoices.
908 -- (DocumentPaymentIDTab(i) value -1 indicates historical invoices.
909 --
910 , DECODE(DocumentPaymentIDTab(i), -1, 'Y', 'N' )
911 /* REL12-AP Lines uptake END*/
912 -- , Agreement_idTab(i) --FSIO Changes
913 /* 10253400: Payroll integration enhancement for 12.2..start */
914 , Location_IdTab(i)
915 , Pay_Element_IdTab(i)
916 , Rate_Source_CodeTab(i)
917 , Costing_Method_Tab(i)
918 /* 10253400: Payroll integration enhancement for 12.2.. end */
919 ,Payroll_Accrual_Flag(i), /* Start changes for Bug#12606242 */
920 Rbc_Element_Type_Id(i),
921 Interface_Run_Id(i) /* end changes for Bug#12606242 */
922 , cbs_element_idtab(i) -- Added for CBS Enhancement Bug 16220146
923 ); ---}
924
925 IF P_DEBUG_MODE THEN
926 pa_cc_utils.log_message('InsItems: ' || 'End of bulk insert for EI insertion');
927 END IF;
928
929 FOR i IN 1..Rows LOOP ---{
930 IF P_DEBUG_MODE THEN
931 pa_cc_utils.log_message('InsItems: ' || 'Start of Loop for CDL insertion');
932 END IF;
933
934 /* #1978887: In case of Supplier Invoices which are interfaced to Projects,
935 Cost_Burden_Distributed_Flag should be set to 'N' only if the corresponding project
936 type allows burdeneing. For this, we check the cost_ind_compiled_set_id. If some value
937 exists for this field, it means the Burdening is there. Hence a check has been added for
938 inserting into Cost_Burden_Distributed_Flag instead of inserting it with hard-coded 'N' */
939
940 --PA-K Changes: Commented the following as bulk insert for EIs is being done now
941 /*IF nvl(X_gl_flag,'N') <> 'Y' THEN
942 **
943 **INSERT INTO pa_expenditure_items_all (
944 ** )
945 ** VALUES (
946 ** );
947 **ELSE
948 ** INSERT INTO pa_expenditure_items_all (
949 ** )
950 ** VALUES (
951 ** );
952 */
953
954 --PA-K For GL Accoutned Txns, call CDL creation API row by row
955 IF nvl(X_gl_flag,'N') in ('Y', 'P') THEN ---{
956
957 IF P_DEBUG_MODE THEN
958 pa_cc_utils.log_message('InsItems: ' || 'After Insert');
959 END IF;
960
961 -- Added the following function call to calculate pa_date to Resolve Bug 1103257 base bug 967390
962
963 --Commenting below such that if pa_date and recvr_pa_date is null then call the API
964 --to get period information.
965
966 /* EPP Changes
967 l_pa_date := pa_integration.get_raw_cdl_pa_date(EiDateTab(i),GldateTab(i),OrgIdTab(i)); --CBGA
968 l_recvr_pa_date := pa_integration.get_raw_cdl_recvr_pa_date(EiDateTab(i),GldateTab(i),RecvOperUnitTab(i)); --CBGA
969 */
970
971 IF (PaDateTab(i) is NULL or RecvrPaDateTab(i) is null) THEN
972
973 select set_of_books_id
974 into l_SobId
975 from pa_implementations_all
976 where org_id = nvl(OrgIdTab(i),-99);
977
978 select set_of_books_id
979 into l_RecvrSobId
980 from pa_implementations_all
981 where org_id = nvl(RecvOperUnitTab(i),-99);
982
983 pa_integration.get_period_information(
984 p_expenditure_item_date => EiDateTab(i)
985 ,p_prvdr_gl_date => GldateTab(i)
986 ,x_recvr_gl_date => l_RecvrGlDate
987 ,p_line_type => 'R'
988 ,p_prvdr_org_id => OrgIdTab(i)
989 ,p_recvr_org_id => RecvOperUnitTab(i)
990 ,p_prvdr_sob_id => l_SobId
991 ,p_recvr_sob_id => l_RecvrSobId
992 ,x_prvdr_pa_date => l_Pa_Date
993 ,x_prvdr_pa_period_name => l_PaPeriodName
994 ,x_prvdr_gl_period_name => l_GlPeriodName
995 ,x_recvr_pa_date => l_Recvr_Pa_Date
996 ,x_recvr_pa_period_name => l_RecvrPaPeriodName
997 ,x_recvr_gl_period_name => l_RecvrGlPeriodName
998 ,x_return_status => x_return_status
999 ,x_error_code => x_error_code
1000 ,x_error_stage => x_error_stage);
1001
1002 ELSE
1003
1004 l_Pa_Date := PaDateTab(i);
1005 l_recvr_pa_date := RecvrPaDateTab(i);
1006 l_PaPeriodName := PaPeriodNameTab(i);
1007 l_RecvrPaPeriodName := RecvrPaPeriodNameTab(i);
1008 l_GlPeriodName := GlPeriodNameTab(i);
1009 l_RecvrGlDate := RecvrGlDateTab(i);
1010 l_RecvrGlPeriodName := RecvrGlPeriodNameTab(i);
1011
1012 END IF;
1013
1014 --added this code to accomodate for cost blue-print project
1015 IF(X_gl_flag ='P') THEN
1016 l_x_gl_flag := 'P';
1017 ELSE
1018
1019 -- REL12 AP Lines uptake
1020 -- supplier invoice adjusted expenditure items should have transfer status code 'P'
1021 --
1022 IF scXferCodeTab(i) is not NULL AND
1023 EiTrxSrcTab(i) in ( 'AP EXPENSE', 'AP INVOICE', 'AP NRTAX' , 'AP ERV',
1024 'INTERCOMPANY_AP_INVOICES', 'INTERPROJECT_AP_INVOICES',
1025 'AP VARIANCE', 'AP DISCOUNTS', 'PO RECEIPT', 'PO RECEIPT NRTAX',
1026 'PO RECEIPT PRICE ADJ', 'PO RECEIPT NRTAX PRICE ADJ' ) THEN
1027 l_x_gl_flag := ScXferCodeTab(i);
1028 ELSE
1029 l_x_gl_flag := 'V';
1030 END IF ;
1031
1032 END IF;
1033
1034 Pa_Costing.CreateExternalCdl( X_expenditure_item_id => EiIdTab(i)
1035 , X_ei_date => EiDateTab(i)
1036 , X_amount => RawCostTab(i)
1037 , X_dr_ccid => DrccidIdTab(i)
1038 , X_cr_ccid => CrccidIdTab(i)
1039 , X_transfer_status_code => l_x_gl_flag
1040 , X_quantity => QtyTab(i)
1041 , X_billable_flag => BillFlagTab(i)
1042 , X_request_id => x_request_id
1043 , X_program_application_id => x_program_application_id
1044 , x_program_id => x_program_id
1045 , x_program_update_date => sysdate
1046 , X_pa_date => l_pa_date
1047 , X_recvr_pa_date => l_recvr_pa_date /*CBGA*/
1048 , X_gl_date => GldateTab(i)
1049 /*Trx_Import enhancement*/
1050 , X_transferred_date => SYSDATE
1051 , X_transfer_rejection_reason => NULL
1052 , X_line_type => 'R'
1053 , X_ind_compiled_set_id => BurdenCompSetId(i)
1054 , X_burdened_cost => BCostTab(i)
1055 , X_user => X_user
1056 , X_project_id => ProjIdTab(i)
1057 , X_task_id => TskidTab(i)
1058 , X_cdlsr1 => Cdlsr1Tab(i)
1059 , X_cdlsr2 => Cdlsr2Tab(i)
1060 , X_cdlsr3 => Cdlsr3Tab(i)
1061 , X_denom_currency_code => DenomCurrCode(i)
1062 , X_denom_raw_cost => DenomRawCost(i)
1063 , X_denom_burden_cost => DenomBurdenCost(i)
1064 , X_acct_currency_code => AcctCurrCode(i)
1065 , X_acct_rate_date => AcctRateDate(i)
1066 , X_acct_rate_type => AcctRateType(i)
1067 , X_acct_exchange_rate => AcctExRate(i)
1068 , X_acct_raw_cost => AcctRawCost(i)
1069 , X_acct_burdened_cost => AcctBurdenCost(i)
1070 , X_project_currency_code => ProjCurrCode(i)
1071 , X_project_rate_date => ProjRateDate(i)
1072 , X_project_rate_type => ProjRateType(i)
1073 , X_project_exchange_rate => ProjExRate(i)
1074 , X_err_code => X_err_code
1075 , X_err_stage => X_err_stage
1076 , X_err_stack => X_err_stack
1077 , P_PaPeriodName => l_PaPeriodName
1078 , P_RecvrPaPeriodName => l_RecvrPaPeriodName
1079 , P_GlPeriodName => l_GlPeriodName
1080 , P_RecvrGlDate => l_RecvrGlDate
1081 , P_RecvrGlPeriodName => l_RecvrGlPeriodName
1082 /** Added for project currency and EI attributes **/
1083 , p_projfunc_currency_code => ProjFunccurrencyTab(i)
1084 , p_projfunc_cost_rate_date => ProjfuncCostRateDateTab(i)
1085 , p_projfunc_cost_rate_type => ProjfuncCostRateTypeTab(i)
1086 , p_projfunc_cost_exchange_rate => ProjFuncCostExgRateTab(i)
1087 , p_project_raw_cost => ProjRawCostTab(i)
1088 , p_project_burdened_cost => ProjBurdendCostTab(i)
1089 --, p_assignment_id => AssignIdTab(i)
1090 , p_work_type_id => WorktypeTab(i)
1091 -- AP Discounts
1092 , p_cdlsr4 => Cdlsr4Tab(i)
1093 , p_si_assets_addition_flag => SiAssetsAddFlagTab(i)
1094 , p_cdlsr5 => Cdlsr5Tab(i)
1095 -- , p_agreement_id => Agreement_idTab(i) --FSIO Changes
1096 , p_cbs_element_id => cbs_element_idtab(i) -- Added for CBS Enhancement Bug 16220146
1097 );
1098
1099 IF P_DEBUG_MODE THEN
1100 pa_cc_utils.log_message('InsItems: ' || 'After Creation of CDL');
1101 END IF;
1102
1103 END IF ; ---}
1104
1105 -- dbms_output.put_line( 'error code : ' || to_char( x_err_code)) ;
1106 IF X_err_code <> 0 THEN
1107 x_status := x_err_code ;
1108 pa_cc_utils.reset_curr_function ;
1109 RETURN ;
1110 END IF ;
1111
1112 IF ( EiCommentTab(i) IS NOT NULL ) THEN
1113 InsItemComment( X_ei_id => EiIdTab(i)
1114 , X_ei_comment => EiCommentTab(i)
1115 , X_user => X_user
1116 , X_login => X_login
1117 , X_status => temp_status );
1118
1119 Pa_Adjustments.CheckStatus( status_indicator => temp_status );
1120
1121 END IF;
1122
1123 IF ( X_calling_process = 'TRX_IMPORT' ) THEN
1124 IF P_DEBUG_MODE THEN
1125 pa_cc_utils.log_message('Trx Import call to InsItems');
1126 END IF;
1127
1128 IF ( AdjEiTab(i) IS NOT NULL ) THEN ---{
1129
1130 pa_adjustments.InsAuditRec( X_exp_item_id => AdjEiTab(i)
1131 , X_adj_activity => 'MANUAL BACK-OUT ORIGINATING'
1132 , X_module => X_module
1133 , X_user => X_user
1134 , X_login => X_login
1135 , X_status => temp_status );
1136 IF P_DEBUG_MODE THEN
1137 pa_cc_utils.log_message('InsItems: ' || 'After call to InsAuditRec');
1138 END IF;
1139
1140 Pa_Adjustments.CheckStatus( status_indicator => temp_status );
1141
1142 pa_adjustments.InsAuditRec( X_exp_item_id => EiIdTab(i)
1143 , X_adj_activity => 'MANUAL BACK-OUT'
1144 , X_module => X_module
1145 , X_user => X_user
1146 , X_login => X_login
1147 , X_status => temp_status
1148 /* R12 Changes Start */
1149 , X_who_req_id => pa_adjustments.G_REQUEST_ID
1150 , X_who_prog_id => pa_adjustments.G_PROGRAM_ID
1151 , X_who_prog_app_id => pa_adjustments.G_PROG_APPL_ID
1152 , X_who_prog_upd_date => sysdate);
1153 /* R12 Changes End */
1154
1155 Pa_Adjustments.CheckStatus( status_indicator => temp_status );
1156
1157 pa_adjustments.SetNetZero( X_exp_item_id => AdjEiTab(i)
1158 , X_user => X_user
1159 , X_login => X_login
1160 , X_status => temp_status );
1161 IF P_DEBUG_MODE THEN
1162 pa_cc_utils.log_message('InsItems: ' || 'After call to SetNetZero');
1163 END IF;
1164
1165 -- R12 AP Lines uptake
1166 -- SLA need to have parent line_num populated with the latest cdl line
1167 -- num of adjusted expenditure item to resolve performance issue.
1168 --
1169 IF nvl(X_gl_flag,'N') in ('Y', 'P') THEN ---{
1170 update pa_cost_distribution_lines cdl1
1171 set cdl1.parent_line_num = ( select cdl2.line_num
1172 from pa_cost_distribution_lines cdl2
1173 where cdl2.expenditure_item_id = AdjEiTab(i)
1174 and cdl2.line_type = 'R'
1175 and cdl2.reversed_flag is NULL
1176 and cdl2.line_num_reversed is NULL )
1177 where cdl1.expenditure_item_id = EiIdTab(i)
1178 and cdl1.line_type = 'R'
1179 and cdl1.reversed_flag is NULL
1180 and cdl1.line_num_reversed is NULL ;
1181 END IF ; --}
1182
1183 Pa_Adjustments.CheckStatus( status_indicator => temp_status );
1184
1185 pa_adjustments.ReverseRelatedItems( X_source_exp_item_id => AdjEiTab(i)
1186 , X_expenditure_id => NULL
1187 , X_module => X_module
1188 , X_user => X_user
1189 , X_login => X_login
1190 , X_status => temp_status );
1191 IF P_DEBUG_MODE THEN
1192 pa_cc_utils.log_message('InsItems: ' || 'After call to ReverseRelatedItems');
1193 END IF;
1194
1195 Pa_Adjustments.CheckStatus( status_indicator => temp_status );
1196
1197 END IF; ---}
1198 END IF; ---}
1199
1200 END LOOP; ---}
1201
1202 /* Bug 5501593 - Start */
1203 IF X_calling_process = 'TRANSFER' THEN
1204 FOR i IN 1..Rows LOOP
1205 /* Audit trail for the original expenditure item */
1206 pa_adjustments.InsAuditRec( X_exp_item_id => TfrEiTab(i)
1207 , X_adj_activity => 'TRANSFER ORIGINATING'
1208 , X_module => X_module
1209 , X_user => X_user
1210 , X_login => X_login
1211 , X_status => temp_status
1212 , X_who_req_id => pa_adjustments.G_REQUEST_ID
1213 , X_who_prog_id => pa_adjustments.G_PROGRAM_ID
1214 , X_who_prog_app_id => pa_adjustments.G_PROG_APPL_ID
1215 , X_who_prog_upd_date => sysdate);
1216 Pa_Adjustments.CheckStatus( status_indicator => temp_status );
1217
1218 /* Audit trail for new expenditure item */
1219 pa_adjustments.InsAuditRec( X_exp_item_id => EiIdTab(i)
1220 , X_adj_activity => 'TRANSFER DESTINATION'
1221 , X_module => X_module
1222 , X_user => X_user
1223 , X_login => X_login
1224 , X_status => temp_status
1225 , X_who_req_id => pa_adjustments.G_REQUEST_ID
1226 , X_who_prog_id => pa_adjustments.G_PROGRAM_ID
1227 , X_who_prog_app_id => pa_adjustments.G_PROG_APPL_ID
1228 , X_who_prog_upd_date => sysdate);
1229 Pa_Adjustments.CheckStatus( status_indicator => temp_status );
1230
1231 /* Get the expenditure Item IDs for all reversinf EIs */
1232 BackoutItemID(i) := pa_utils.GetNextEiId;
1233 END LOOP;
1234
1235 /* Insert the Reversing expenditure Items */
1236
1237 FORALL i IN 1..Rows
1238 INSERT INTO pa_expenditure_items_all(
1239 expenditure_item_id
1240 , task_id
1241 , expenditure_type
1242 , system_linkage_function
1243 , expenditure_item_date
1244 , expenditure_id
1245 , override_to_organization_id
1246 , last_update_date
1247 , last_updated_by
1248 , creation_date
1249 , created_by
1250 , last_update_login
1251 , quantity
1252 , revenue_distributed_flag
1253 , bill_hold_flag
1254 , billable_flag
1255 , bill_rate_multiplier
1256 , cost_distributed_flag
1257 , raw_cost
1258 , raw_cost_rate
1259 , burden_cost
1260 , burden_cost_rate
1261 , cost_ind_compiled_set_id
1262 , non_labor_resource
1263 , organization_id
1264 , adjusted_expenditure_item_id
1265 , net_zero_adjustment_flag
1266 , attribute_category
1267 , attribute1
1268 , attribute2
1269 , attribute3
1270 , attribute4
1271 , attribute5
1272 , attribute6
1273 , attribute7
1274 , attribute8
1275 , attribute9
1276 , attribute10
1277 , transferred_from_exp_item_id
1278 , transaction_source
1279 , orig_transaction_reference
1280 , source_expenditure_item_id
1281 , job_id
1282 , org_id
1283 , labor_cost_multiplier_name
1284 , receipt_currency_amount
1285 , receipt_currency_code
1286 , receipt_exchange_rate
1287 , denom_currency_code
1288 , denom_raw_cost
1289 , denom_burdened_cost
1290 , acct_currency_code
1291 , acct_rate_date
1292 , acct_rate_type
1293 , acct_exchange_rate
1294 , acct_raw_cost
1295 , acct_burdened_cost
1296 , acct_exchange_rounding_limit
1297 , project_currency_code
1298 , project_rate_date
1299 , project_rate_type
1300 , project_exchange_rate
1301 , cc_cross_charge_code
1302 , cc_prvdr_organization_id
1303 , cc_recvr_organization_id
1304 , cc_rejection_code
1305 , denom_tp_currency_code
1306 , denom_transfer_price
1307 , acct_tp_rate_type
1308 , acct_tp_rate_date
1309 , acct_tp_exchange_rate
1310 , acct_transfer_price
1311 , projacct_transfer_price
1312 , cc_markup_base_code
1313 , tp_base_amount
1314 , cc_cross_charge_type
1315 , recvr_org_id
1316 , cc_bl_distributed_code
1317 , cc_ic_processed_code
1318 , tp_ind_compiled_set_id
1319 , tp_bill_rate
1320 , tp_bill_markup_percentage
1321 , tp_schedule_line_percentage
1322 , tp_rule_percentage
1323 , cost_job_id
1324 , tp_job_id
1325 , prov_proj_bill_job_id
1326 , assignment_id
1327 , work_type_id
1328 , projfunc_currency_code
1329 , projfunc_cost_rate_date
1330 , projfunc_cost_rate_type
1331 , projfunc_cost_exchange_rate
1332 , project_raw_cost
1333 , project_burdened_cost
1334 , project_id
1335 , project_tp_rate_date
1336 , project_tp_rate_type
1337 , project_tp_exchange_rate
1338 , project_transfer_price
1339 , tp_amt_type_code
1340 , cost_burden_distributed_flag
1341 , capital_event_id
1342 , wip_resource_id
1343 , inventory_item_id
1344 , unit_of_measure
1345 , document_header_id
1346 , document_distribution_id
1347 , document_line_number
1348 , document_payment_id
1349 , vendor_id
1350 , document_type
1351 , document_distribution_type
1352 , po_line_id --added po_line_id for Bug 16451280
1353 -- , agreement_id ) --FSIO Changes
1354 /* 10253400: Payroll integration enhancement for 12.2..start */
1355 , location_id
1356 , pay_element_type_id
1357 , rate_source_code
1358 , costing_method /* 10253400: Payroll integration enhancement for 12.2.. end */
1359 ,Payroll_Accrual_Flag /* start changes for 12581268 */
1360 ,Rbc_Element_Type_Id
1361 ,Interface_Run_Id /* end changes for 12581268 */
1362 ,cbs_element_id) -- Added for CBS Enhancement Bug 16220146
1363
1364 SELECT
1365 BackoutItemID(i) -- expenditure_item_id
1366 , ei.task_id -- task_id
1367 , ei.expenditure_type -- expenditure_type
1368 , ei.system_linkage_function -- system_linkage_function
1369 , ei.expenditure_item_date -- expenditure_item_date
1370 , ei.expenditure_id -- expenditure_id
1371 , ei.override_to_organization_id -- override exp organization
1372 , sysdate -- last_update_date
1373 , X_user -- last_updated_by
1374 , sysdate -- creation_date
1375 , X_user -- created_by
1376 , X_login -- last_update_login
1377 , (0 - ei.quantity) -- quantity
1378 , 'N' -- revenue_distributed_flag
1379 , ei.bill_hold_flag -- bill_hold_flag
1380 , ei.billable_flag -- billable_flag
1381 , ei.bill_rate_multiplier -- bill_rate_multiplier
1382 , 'N' -- cost_distributed_flag
1383 , (0 - ei.raw_cost) -- raw_cost
1384 , ei.raw_cost_rate -- raw_cost_rate
1385 , (0 - ei.burden_cost) -- raw_cost
1386 , ei.burden_cost_rate -- raw_cost_rate
1387 , ei.cost_ind_compiled_set_id -- cost_ind_compiled_set_id
1388 , ei.non_labor_resource -- non_labor_resource
1389 , ei.organization_id -- organization_id
1390 , ei.expenditure_item_id -- adjusted_expenditure_item_id
1391 , 'Y' -- net_zero_adjustment_flag
1392 , ei.attribute_category -- attribute_category
1393 , ei.attribute1 -- attribute1
1394 , ei.attribute2 -- attribute2
1395 , ei.attribute3 -- attribute3
1396 , ei.attribute4 -- attribute4
1397 , ei.attribute5 -- attribute5
1398 , ei.attribute6 -- attribute6
1399 , ei.attribute7 -- attribute7
1400 , ei.attribute8 -- attribute8
1401 , ei.attribute9 -- attribute9
1402 , ei.attribute10 -- attribute10
1403 , ei.transferred_from_exp_item_id -- tfr from exp item id
1404 , ei.transaction_source -- transaction_source
1405 , decode(ei.transaction_source,'PTE TIME',NULL,
1406 decode(ei.transaction_source,'PTE EXPENSE',NULL,
1407 decode(ei.transaction_source,'ORACLE TIME AND LABOR',NULL,
1408 decode(ei.transaction_source,'Oracle Self Service Time',NULL,
1409 ei.orig_transaction_reference)))) -- orig_transaction_reference
1410 , ei.source_expenditure_item_id -- source_expenditure_item_id
1411 , ei.job_id -- job_id
1412 , ei.org_id -- org_id
1413 , ei.labor_cost_multiplier_name -- labor_cost_multiplier_name
1414 , (0 - ei.receipt_currency_amount) -- receipt_currency_amount
1415 , ei.receipt_currency_code -- receipt_currency_code
1416 , ei.receipt_exchange_rate -- receipt_exchange_rate
1417 , ei.denom_currency_code -- denom_currency_code
1418 , (0 - ei.denom_raw_cost) -- denom_raw_cost
1419 , (0 - ei.denom_burdened_cost) -- denom_burdened_cost
1420 , ei.acct_currency_code -- acct_currency_code
1421 , ei.acct_rate_date -- acct_rate_date
1422 , ei.acct_rate_type -- acct_rate_type
1423 , ei.acct_exchange_rate -- acct_exchange_rate
1424 , (0 - ei.acct_raw_cost) -- acct_raw_cost
1425 , (0 - ei.acct_burdened_cost) -- acct_burdened_cost
1426 , ei.acct_exchange_rounding_limit -- acct_exchange_rounding_limit
1427 , ei.project_currency_code -- project_currency_code
1428 , ei.project_rate_date -- project_rate_date
1429 , ei.project_rate_type -- project_rate_type
1430 , ei.project_exchange_rate -- project_exchange_rate
1431 , ei.cc_cross_charge_code -- cc_cross_charge_code
1432 , ei.cc_prvdr_organization_id -- cc_prvdr_organization_id
1433 , ei.cc_recvr_organization_id -- cc_recvr_organization_id
1434 , ei.cc_rejection_code -- cc_rejection_code
1435 , ei.denom_tp_currency_code -- denom_tp_currency_code
1436 , (0 - ei.denom_transfer_price) -- denom_transfer_price
1437 , ei.acct_tp_rate_type -- acct_tp_rate_type
1438 , ei.acct_tp_rate_date -- acct_tp_rate_date
1439 , ei.acct_tp_exchange_rate -- acct_tp_exchange_rate
1440 , (0 - ei.acct_transfer_price) -- acct_transfer_price
1441 , (0 - ei.projacct_transfer_price) -- projacct_transfer_price
1442 , ei.cc_markup_base_code -- cc_markup_base_code
1443 , (0 - ei.tp_base_amount) -- tp_base_amount
1444 , ei.cc_cross_charge_type -- cc_cross_charge_type
1445 , ei.recvr_org_id -- recvr_org_id
1446 , decode(ei.cc_bl_distributed_code,'X','X','N') -- cc_bl_distributed_code /*Added for bug#13390012*/
1447 , decode(ei.cc_ic_processed_code,'X','X','N') -- cc_ic_processed_code modified for bug 14079203
1448 , ei.tp_ind_compiled_set_id -- tp_ind_compiled_set_id
1449 , ei.tp_bill_rate -- tp_bill_rate
1450 , ei.tp_bill_markup_percentage -- tp_bill_markup_percentage
1451 , ei.tp_schedule_line_percentage -- tp_schedule_line_percentage
1452 , ei.tp_rule_percentage -- tp_rule_percentage
1453 , ei.cost_job_id -- cost_job_id
1454 , ei.tp_job_id -- tp_job_id
1455 , ei.prov_proj_bill_job_id -- prov_proj_bill_job_id
1456 , ei.assignment_id
1457 , ei.work_type_id
1458 , ei.projfunc_currency_code
1459 , ei.projfunc_cost_rate_date
1460 , ei.projfunc_cost_rate_type
1461 , ei.projfunc_cost_exchange_rate
1462 , (0 - ei.project_raw_cost) -- project raw cost
1463 , (0 - ei.project_burdened_cost) -- project burended cost
1464 , ei.project_id
1465 , ei.project_tp_rate_date
1466 , ei.project_tp_rate_type
1467 , ei.project_tp_exchange_rate
1468 , (0 - ei.project_transfer_price)
1469 , ei.tp_amt_type_code
1470 , decode(ei.cost_ind_compiled_set_id,null,'X','N')
1471 , capital_event_id
1472 , wip_resource_id
1473 , inventory_item_id
1474 , unit_of_measure
1475 , ei.document_header_id
1476 , ei.document_distribution_id
1477 , ei.document_line_number
1478 , ei.document_payment_id
1479 , ei.vendor_id ei_vendor_id
1480 , ei.document_type
1481 , ei.document_distribution_type
1482 , ei.po_line_id -- Bug 16451280
1483 -- , ei.agreement_id --FSIO Changes
1484 /* 10253400: Payroll integration enhancement for 12.2..stat */
1485 , ei.location_id
1486 , ei.pay_element_type_id
1487 , ei.rate_source_code
1488 , ei.costing_method /* 10253400: Payroll integration enhancement for 12.2..end */
1489 , ei.Payroll_Accrual_Flag /* start changes for 12581268 */
1490 , ei.Rbc_Element_Type_Id
1491 , ei.Interface_Run_Id /* end changes for 12581268 */
1492 , ei.cbs_element_id -- Added for CBS Enhancement Bug 16220146
1493
1494 FROM
1495 pa_expenditure_items_all ei
1496 WHERE
1497 ei.expenditure_item_id = TfrEiTab(i);
1498
1499 FOR i IN 1..Rows LOOP
1500 /* Create comment for the reversing EI if they exist for the original EI */
1501 BEGIN
1502 SELECT ec.expenditure_comment
1503 INTO item_comment
1504 FROM pa_expenditure_comments ec
1505 WHERE ec.expenditure_item_id = TfrEiTab(i);
1506 EXCEPTION
1507 WHEN NO_DATA_FOUND THEN
1508 NULL;
1509 END;
1510 IF ( item_comment IS NOT NULL ) THEN
1511 pa_transactions.InsItemComment( X_ei_id => BackoutItemID(i)
1512 , X_ei_comment => item_comment
1513 , X_user => X_user
1514 , X_login => X_login
1515 , X_status => temp_status );
1516 pa_adjustments.CheckStatus( status_indicator => temp_status );
1517 END IF;
1518
1519 /* Set Net Zero Flag on the Original Expenditure Item */
1520 pa_adjustments.SetNetZero( TfrEiTab(i)
1521 , X_user
1522 , X_login
1523 , temp_status );
1524 pa_adjustments.CheckStatus( temp_status );
1525
1526 /* Create an Audit trail */
1527 pa_adjustments.InsAuditRec( BackoutItemID(i)
1528 , 'TRANSFER BACK-OUT'
1529 , X_module
1530 , X_user
1531 , X_login
1532 , temp_status
1533 , pa_adjustments.G_REQUEST_ID
1534 , pa_adjustments.G_PROGRAM_ID
1535 , pa_adjustments.G_PROG_APPL_ID
1536 , sysdate );
1537 pa_adjustments.CheckStatus( temp_status );
1538
1539 /* Create a reversing CDL */
1540 Pa_Costing.CreateReverseCdl( X_exp_item_id => TfrEiTab(i),
1541 X_backout_id => BackoutItemID(i),
1542 X_user => X_user,
1543 X_status => temp_status);
1544 Pa_Adjustments.CheckStatus( status_indicator => temp_status );
1545
1546 /* Reverse Related Items */
1547 pa_adjustments.ReverseRelatedItems( X_source_exp_item_id => TfrEiTab(i)
1548 , X_expenditure_id => NULL
1549 , X_module => X_module
1550 , X_user => X_user
1551 , X_login => X_login
1552 , X_status => temp_status );
1553 Pa_Adjustments.CheckStatus( status_indicator => temp_status );
1554 END LOOP;
1555 END IF;
1556 /* Bug 5501593 - End */
1557
1558 X_status := 0;
1559 IF P_DEBUG_MODE THEN
1560 pa_cc_utils.log_message('InsItems: ' || 'End ');
1561 END IF;
1562 pa_cc_utils.reset_curr_function ;
1563
1564 EXCEPTION
1565 WHEN OTHERS THEN
1566 X_status := SQLCODE;
1567 RAISE;
1568
1569 END InsItems;
1570
1571 -- ========================================================================
1572 -- PROCEDURE
1573 -- Added Multi-Currency Transactions columns Shree 08/06
1574 -- ========================================================================
1575
1576 PROCEDURE InsertExp( X_expenditure_id IN NUMBER
1577 , X_expend_status IN VARCHAR2
1578 , X_expend_ending IN DATE
1579 , X_expend_class IN VARCHAR2
1580 , X_inc_by_person IN NUMBER
1581 , X_inc_by_org IN NUMBER
1582 , X_expend_group IN VARCHAR2
1583 , X_entered_by_id IN NUMBER
1584 , X_created_by_id IN NUMBER
1585 , X_attribute_category IN VARCHAR2
1586 , X_attribute1 IN VARCHAR2
1587 , X_attribute2 IN VARCHAR2
1588 , X_attribute3 IN VARCHAR2
1589 , X_attribute4 IN VARCHAR2
1590 , X_attribute5 IN VARCHAR2
1591 , X_attribute6 IN VARCHAR2
1592 , X_attribute7 IN VARCHAR2
1593 , X_attribute8 IN VARCHAR2
1594 , X_attribute9 IN VARCHAR2
1595 , X_attribute10 IN VARCHAR2
1596 , X_description IN VARCHAR2
1597 , X_control_total IN NUMBER
1598 , X_denom_currency_code IN VARCHAR2
1599 , X_acct_currency_code IN VARCHAR2
1600 , X_acct_rate_type IN VARCHAR2
1601 , X_acct_rate_date IN DATE
1602 , X_acct_exchange_rate IN NUMBER
1603 -- Trx_import enhancement: Adding new parameters
1604 -- These values will be inserted into PA_EXPENDITURES_ALL table
1605 , X_orig_exp_txn_reference1 IN VARCHAR2
1606 , X_orig_user_exp_txn_reference IN VARCHAR2
1607 , X_vendor_id IN NUMBER
1608 , X_orig_exp_txn_reference2 IN VARCHAR2
1609 , X_orig_exp_txn_reference3 IN VARCHAR2
1610 , X_person_type IN VARCHAR2
1611 , P_Org_ID IN NUMBER -- 12i MOAC changes
1612 )
1613 IS
1614 BEGIN
1615 pa_cc_utils.set_curr_function('InsertExp');
1616 IF P_DEBUG_MODE THEN
1617 pa_cc_utils.log_message('InsertExp: ' || 'Start ');
1618 END IF;
1619 INSERT INTO pa_expenditures(
1620 expenditure_id
1621 , expenditure_status_code
1622 , expenditure_ending_date
1623 , expenditure_class_code
1624 , incurred_by_person_id
1625 , incurred_by_organization_id
1626 , expenditure_group
1627 , entered_by_person_id
1628 , last_update_date
1629 , last_updated_by
1630 , creation_date
1631 , created_by
1632 , attribute_category
1633 , attribute1
1634 , attribute2
1635 , attribute3
1636 , attribute4
1637 , attribute5
1638 , attribute6
1639 , attribute7
1640 , attribute8
1641 , attribute9
1642 , attribute10
1643 , description
1644 , control_total_amount
1645 , denom_currency_code
1646 , acct_currency_code
1647 , acct_rate_type
1648 , acct_rate_date
1649 , acct_exchange_rate
1650 -- Trx_import enhancement
1651 , orig_exp_txn_reference1
1652 , orig_user_exp_txn_reference
1653 , vendor_id
1654 , orig_exp_txn_reference2
1655 , orig_exp_txn_reference3
1656 , person_type
1657 , org_id) -- 12i MOAC changes
1658 VALUES (
1659 X_expenditure_id
1660 , X_expend_status
1661 , X_expend_ending
1662 , X_expend_class
1663 , X_inc_by_person
1664 , X_inc_by_org
1665 , X_expend_group
1666 , X_entered_by_id
1667 , sysdate
1668 , X_created_by_id
1669 , sysdate
1670 , X_created_by_id
1671 , X_attribute_category
1672 , X_attribute1
1673 , X_attribute2
1674 , X_attribute3
1675 , X_attribute4
1676 , X_attribute5
1677 , X_attribute6
1678 , X_attribute7
1679 , X_attribute8
1680 , X_attribute9
1681 , X_attribute10
1682 , X_description
1683 , X_control_total
1684 , X_denom_currency_code
1685 , X_acct_currency_code
1686 , X_acct_rate_type
1687 , X_acct_rate_date
1688 , X_acct_exchange_rate
1689 -- Trx_import enhancement
1690 , X_orig_exp_txn_reference1
1691 , X_orig_user_exp_txn_reference
1692 , X_vendor_id
1693 , X_orig_exp_txn_reference2
1694 , X_orig_exp_txn_reference3
1695 , X_person_type
1696 , P_Org_Id); -- 12i MOAC changes
1697 IF P_DEBUG_MODE THEN
1698 pa_cc_utils.log_message('InsertExp: ' || 'End ');
1699 END IF;
1700 pa_cc_utils.reset_curr_function ;
1701
1702 END InsertExp;
1703
1704
1705 PROCEDURE InsertExpGroup(
1706 X_expenditure_group IN VARCHAR2
1707 , X_exp_group_status_code IN VARCHAR2
1708 , X_ending_date IN DATE
1709 , X_system_linkage IN VARCHAR2
1710 , X_created_by IN NUMBER
1711 , X_transaction_source IN VARCHAR2
1712 , P_accrual_flag IN VARCHAR2
1713 , P_Org_Id IN NUMBER) -- 12i MOAC changes
1714 IS
1715 BEGIN
1716 pa_cc_utils.set_curr_function('InsertExpGroup');
1717 IF P_DEBUG_MODE THEN
1718 pa_cc_utils.log_message('InsertExp: ' || 'Start ');
1719 END IF;
1720
1721 INSERT INTO pa_expenditure_groups(
1722 expenditure_group
1723 , expenditure_group_status_code
1724 , expenditure_ending_date
1725 , system_linkage_function
1726 , last_update_date
1727 , last_updated_by
1728 , creation_date
1729 , created_by
1730 , transaction_source
1731 , period_accrual_flag
1732 , Org_Id) -- 12i MOAC changes
1733 VALUES ( X_expenditure_group
1734 , X_exp_group_status_code
1735 , X_ending_date
1736 , X_system_linkage
1737 , sysdate
1738 , X_created_by
1739 , sysdate
1740 , X_created_by
1741 , X_transaction_source
1742 , P_accrual_flag
1743 , P_Org_Id); -- 12i MOAC changes
1744 IF P_DEBUG_MODE THEN
1745 pa_cc_utils.log_message('InsertExp: ' || 'End ');
1746 END IF;
1747 pa_cc_utils.reset_curr_function ;
1748
1749 END InsertExpGroup;
1750
1751 --PA-K Changes
1752 PROCEDURE InsertExpGroupNew(
1753 X_expenditure_group IN VARCHAR2
1754 , X_exp_group_status_code IN VARCHAR2
1755 , X_ending_date IN DATE
1756 , X_system_linkage IN VARCHAR2
1757 , X_created_by IN NUMBER
1758 , X_transaction_source IN VARCHAR2
1759 , P_accrual_flag IN VARCHAR2
1760 , P_Org_Id IN NUMBER ) -- 12i MOAC changes
1761 IS
1762
1763 l_Dummy NUMBER;
1764 l_Ending_Date DATE;
1765
1766 BEGIN
1767 pa_cc_utils.set_curr_function('InsertExpGroupNew');
1768 IF P_DEBUG_MODE THEN
1769 pa_cc_utils.log_message('InsertExp: ' || 'Start ');
1770 END IF;
1771
1772 Begin
1773
1774 IF P_DEBUG_MODE THEN
1775 pa_cc_utils.log_message('InsertExp: ' || 'Selecting if the group already exists');
1776 END IF;
1777
1778 select 1, expenditure_ending_date
1779 into l_Dummy, l_Ending_Date
1780 from pa_expenditure_groups
1781 where expenditure_group = X_expenditure_group;
1782
1783 Exception
1784 when no_data_found then
1785 IF P_DEBUG_MODE THEN
1786 pa_cc_utils.log_message('InsertExp: ' || 'no data found when selecting if group already exists');
1787 END IF;
1788 l_dummy := 0 ;
1789
1790 End;
1791
1792 If (l_dummy = 0) Then
1793 IF P_DEBUG_MODE THEN
1794 pa_cc_utils.log_message('InsertExp: ' || 'Exp Group does not exist, insert');
1795 END IF;
1796
1797 INSERT INTO pa_expenditure_groups(
1798 expenditure_group
1799 , expenditure_group_status_code
1800 , expenditure_ending_date
1801 , system_linkage_function
1802 , last_update_date
1803 , last_updated_by
1804 , creation_date
1805 , created_by
1806 , transaction_source
1807 , period_accrual_flag
1808 , org_id) -- 12i MOAC changes
1809 VALUES ( X_expenditure_group
1810 , X_exp_group_status_code
1811 , X_ending_date
1812 , X_system_linkage
1813 , sysdate
1814 , X_created_by
1815 , sysdate
1816 , X_created_by
1817 , X_transaction_source
1818 , P_accrual_flag
1819 , P_Org_Id); -- 12i MOAC changes
1820
1821 Else
1822
1823 IF P_DEBUG_MODE THEN
1824 pa_cc_utils.log_message('InsertExp: ' || 'Exp Group does exist, update if needed');
1825 END IF;
1826
1827 If trunc(X_ending_date) > trunc(l_Ending_Date) Then
1828
1829 IF P_DEBUG_MODE THEN
1830 pa_cc_utils.log_message('InsertExp: ' || 'Existing Exp Groups ending date is lesser, update');
1831 END IF;
1832
1833 update pa_expenditure_groups
1834 set expenditure_ending_date = X_ending_date
1835 where expenditure_group = X_expenditure_group;
1836
1837 IF P_DEBUG_MODE THEN
1838 pa_cc_utils.log_message('InsertExp: ' || 'Updated Count = '||SQL%ROWCOUNT);
1839 END IF;
1840
1841 End If;
1842
1843 End If;
1844
1845 IF P_DEBUG_MODE THEN
1846 pa_cc_utils.log_message('InsertExp: ' || 'End ');
1847 END IF;
1848 pa_cc_utils.reset_curr_function ;
1849
1850 END InsertExpGroupNew;
1851
1852
1853 PROCEDURE CreateRelatedItem(
1854 X_source_exp_item_id IN NUMBER
1855 , X_project_id IN NUMBER
1856 , X_task_id IN NUMBER
1857 , X_Award_id IN NUMBER
1858 , X_expenditure_type IN VARCHAR2
1859 , X_denom_raw_cost IN NUMBER
1860 , X_denom_raw_cost_rate IN NUMBER
1861 , X_override_to_org_id IN NUMBER
1862 , X_userid IN NUMBER
1863 , X_attribute_category IN VARCHAR2
1864 , X_attribute1 IN VARCHAR2
1865 , X_attribute2 IN VARCHAR2
1866 , X_attribute3 IN VARCHAR2
1867 , X_attribute4 IN VARCHAR2
1868 , X_attribute5 IN VARCHAR2
1869 , X_attribute6 IN VARCHAR2
1870 , X_attribute7 IN VARCHAR2
1871 , X_attribute8 IN VARCHAR2
1872 , X_attribute9 IN VARCHAR2
1873 , X_attribute10 IN VARCHAR2
1874 , X_comment IN VARCHAR2
1875 , X_status OUT NOCOPY NUMBER
1876 , X_outcome OUT NOCOPY VARCHAR2
1877 , X_work_type_name IN VARCHAR2 /*bug2482593*/
1878 )
1879 IS
1880
1881 X_expenditure_item_id NUMBER(15);
1882 X_expenditure_id NUMBER(15);
1883 X_expenditure_item_date DATE;
1884 X_inc_by_person_id NUMBER(15);
1885 X_inc_by_org_id NUMBER(15);
1886 X_orig_proj_id NUMBER(15);
1887 X_orig_task_id NUMBER(15);
1888 X_dest_proj_id NUMBER(15);
1889 X_dest_task_id NUMBER(15);
1890 X_billable_flag VARCHAR2(1);
1891 X_bill_hold_flag VARCHAR2(1);
1892 X_system_linkage VARCHAR2(30);
1893 X_etype_class VARCHAR2(3);
1894 X_job_id NUMBER(15);
1895 X_org_id NUMBER(15);
1896 temp_status NUMBER;
1897 temp_outcome VARCHAR2(30);
1898 dummy NUMBER DEFAULT NULL;
1899 l_dest_lcm VARCHAR2(20);
1900 l_orig_lcm VARCHAR2(20);
1901 X_denom_currency_code VARCHAR2(15);
1902 X_Acct_currency_code VARCHAR2(15);
1903 X_project_currency_code VARCHAR2(15) ;
1904 temp_msg_application VARCHAR2(30) :='PA';
1905 temp_msg_type VARCHAR2(1) :='E';
1906 temp_msg_token1 VARCHAR2(240) :='';
1907 temp_msg_token2 VARCHAR2(240) :='';
1908 temp_msg_token3 VARCHAR2(240) :='';
1909 temp_msg_count NUMBER ;
1910 X_project_rate_type VARCHAR2(30);
1911 X_project_rate_date DATE ;
1912 X_project_exchange_rate NUMBER ;
1913 l_PROJFUNC_CURRENCY_CODE VARCHAR2(30);
1914 l_PROJFUNC_COST_RATE_TYPE VARCHAR2(30);
1915 l_PROJFUNC_COST_RATE_DATE DATE ;
1916 l_PROJFUNC_COST_EXCHANGE_RATE NUMBER ;
1917 l_PROJECT_RAW_COST NUMBER ;
1918 l_PROJECT_BURDENED_COST NUMBER ;
1919 l_ASSIGNMENT_ID NUMBER ;
1920 l_WORK_TYPE_ID NUMBER ;
1921 l_PROJECT_TP_CURRENCY_CODE VARCHAR2(30);
1922 l_PROJECT_TP_COST_RATE_DATE DATE ;
1923 l_PROJECT_TP_COST_RATE_TYPE VARCHAR2(30);
1924 l_PROJECT_TP_COST_EXG_RATE NUMBER ;
1925 l_PROJECT_TRANSFER_PRICE NUMBER ;
1926 l_gms_enabled VARCHAR(1); /*added for bug 5769510*/
1927 adl_rec gms_award_distributions%ROWTYPE; /*added for bug 5769510*/
1928 source_award_id NUMBER ; /*added for bug 5769510*/
1929 l_cbs_element_id NUMBER ; -- Added for CBS Enhancement Bug 16220146
1930
1931
1932 BEGIN
1933 pa_cc_utils.set_curr_function('CreateRelatedItem');
1934 IF P_DEBUG_MODE THEN
1935 pa_cc_utils.log_message('CreateRelatedItem: ' || 'Start ');
1936 END IF;
1937
1938 l_gms_enabled := gms_pa_costing_pkg.grants_implemented;
1939
1940 /* changes for bug2482593 starts */
1941 if(X_work_type_name IS NOT NULL)then
1942 Begin
1943 select work_type_id
1944 into l_work_type_id
1945 from pa_work_types_tl
1946 where name=X_work_type_name AND
1947 language = userenv('LANG');
1948 exception
1949 when no_data_found then
1950 X_status := 10;
1951 IF P_DEBUG_MODE THEN
1952 pa_cc_utils.log_message('CreateRelatedItem: ' || 'Invalid Work Type Name');
1953 END IF;
1954 pa_cc_utils.reset_curr_function ;
1955 X_outcome := 'INVALID_WORK_TYPE';
1956 return;
1957 end;
1958
1959 else
1960 IF P_DEBUG_MODE THEN
1961 pa_cc_utils.log_message('CreateRelatedItem: ' || 'Work Type ID defaults from source EI');
1962 END IF;
1963 select work_type_id
1964 into l_work_type_id
1965 from pa_expenditure_items
1966 where expenditure_item_id = X_source_exp_item_id;
1967 end if;
1968 /* changes for bug2482593 ends */
1969
1970 -- Need to select new MC columns here to pass to LoadEi and PATC
1971 SELECT
1972 pa_expenditure_items_s.nextval
1973 , ei.expenditure_id
1974 , ei.expenditure_item_date
1975 , e.incurred_by_person_id
1976 , e.incurred_by_organization_id
1977 , ei.source_expenditure_item_id
1978 , ei.bill_hold_flag
1979 , t.project_id
1980 , t.task_id
1981 , ei.job_id
1982 , ei.org_id
1983 , ei.system_linkage_function
1984 , ei.denom_currency_code
1985 , ei.acct_currency_code
1986 , ei.project_currency_code
1987 , ei.project_rate_type
1988 , ei.project_rate_date
1989 , ei.project_exchange_rate
1990 , t.labor_cost_multiplier_name
1991 , ei.PROJFUNC_CURRENCY_CODE
1992 , ei.PROJFUNC_COST_RATE_TYPE
1993 , ei.PROJFUNC_COST_RATE_DATE
1994 , ei.PROJFUNC_COST_EXCHANGE_RATE
1995 , NULL -- bug 4719803 ei.PROJECT_RAW_COST
1996 , NULL -- bug 4719803 ei.PROJECT_BURDENED_COST
1997 , ei.ASSIGNMENT_ID
1998 /* , ei.WORK_TYPE_ID bug2482593 */
1999 , ei.cbs_element_id -- Added for CBS Enhancement Bug 16220146
2000 INTO
2001 X_expenditure_item_id
2002 , X_expenditure_id
2003 , X_expenditure_item_date
2004 , X_inc_by_person_id
2005 , X_inc_by_org_id
2006 , dummy
2007 , X_bill_hold_flag
2008 , X_orig_proj_id
2009 , X_orig_task_id
2010 , X_job_id
2011 , X_org_id
2012 , X_etype_class
2013 , X_denom_currency_code
2014 , X_Acct_currency_code
2015 , X_project_currency_code
2016 , X_project_rate_type
2017 , X_project_rate_date
2018 , X_project_exchange_rate
2019 , l_orig_lcm
2020 , l_PROJFUNC_CURRENCY_CODE
2021 , l_PROJFUNC_COST_RATE_TYPE
2022 , l_PROJFUNC_COST_RATE_DATE
2023 , l_PROJFUNC_COST_EXCHANGE_RATE
2024 , l_PROJECT_RAW_COST
2025 , l_PROJECT_BURDENED_COST
2026 , l_ASSIGNMENT_ID
2027 /* , l_WORK_TYPE_ID bug2482593*/
2028 , l_cbs_element_id -- Added for CBS Enhancement Bug 16220146
2029 FROM
2030 pa_expenditure_items ei
2031 , pa_expenditures e
2032 , pa_tasks t
2033 WHERE
2034 e.expenditure_id = ei.expenditure_id
2035 AND ei.expenditure_item_id = X_source_exp_item_id
2036 AND ei.task_id = t.task_id;
2037 IF P_DEBUG_MODE THEN
2038 pa_cc_utils.log_message('In pa_transactions.CreateRelatedItem: After select statement');
2039 END IF;
2040
2041 IF ( X_project_id IS NULL
2042 OR X_task_id IS NULL ) THEN
2043
2044 X_dest_proj_id := X_orig_proj_id;
2045 X_dest_task_id := X_orig_task_id;
2046
2047 ELSE
2048
2049 X_dest_proj_id := X_project_id;
2050 X_dest_task_id := X_task_id;
2051
2052 END IF;
2053
2054 IF ( dummy IS NOT NULL ) THEN
2055
2056 X_outcome := 'PA_TR_RELATED_ITEM';
2057 X_status := 1;
2058 pa_cc_utils.reset_curr_function ;
2059 RETURN;
2060
2061 END IF;
2062 /*
2063 IF ( NOT pa_exp_copy.CheckExpTypeActive( X_expenditure_type
2064 , X_expenditure_item_date ) ) THEN
2065
2066 X_outcome := 'EXP_TYPE_INACTIVE';
2067 X_status := 1;
2068 pa_cc_utils.reset_curr_function ;
2069 RETURN;
2070
2071 END IF;
2072 */
2073 SELECT
2074 count(*)
2075 INTO
2076 dummy
2077 FROM
2078 sys.dual
2079 WHERE EXISTS
2080 ( SELECT NULL
2081 FROM pa_expenditure_types
2082 WHERE expenditure_type = X_expenditure_type);
2083
2084 IF ( dummy = 0 ) THEN
2085
2086 X_outcome := 'INVALID_EXP_TYPE';
2087 X_status := 1;
2088 pa_cc_utils.reset_curr_function ;
2089 RETURN;
2090
2091 END IF;
2092
2093 IF ( X_etype_class NOT IN ('ST', 'OT' ) ) THEN
2094 X_outcome := 'INVALID_EXP_TYPE';
2095 X_status := 1;
2096 pa_cc_utils.reset_curr_function ;
2097 RETURN;
2098 END IF;
2099
2100
2101 dummy := NULL;
2102
2103 IF ( X_override_to_org_id IS NOT NULL ) THEN
2104
2105 SELECT
2106 count(*)
2107 INTO
2108 dummy
2109 FROM
2110 sys.dual
2111 WHERE EXISTS
2112 ( SELECT NULL
2113 FROM pa_organizations_v
2114 WHERE organization_id = X_override_to_org_id);
2115
2116 IF ( dummy = 0 ) THEN
2117
2118 X_outcome := 'INVALID_ORGANIZATION';
2119 X_status := 1;
2120 pa_cc_utils.reset_curr_function ;
2121 RETURN;
2122
2123 END IF;
2124
2125 END IF;
2126
2127 -- This section added for bug 791759
2128 IF ( x_task_id IS NULL ) THEN
2129 l_dest_lcm := l_orig_lcm ;
2130 ELSE
2131 l_dest_lcm := pa_utils2.GetLaborCostMultiplier(x_task_id);
2132 END IF;
2133
2134 --
2135
2136 -- Fix for Bug # 801194. This call to get_status was removed in 11.0
2137 -- for Bug # 519532.Since, Billable flag is an OUT parameter and was getting
2138 -- populated in get_status, it was seen that a null was getting passed. Hence
2139 -- added the call to get_status again.
2140 IF P_DEBUG_MODE THEN
2141 pa_cc_utils.log_message('CreateRelatedItem: ' || 'Before Call to PATC');
2142 END IF;
2143
2144 pa_transactions_pub.validate_transaction(
2145 X_project_id => X_dest_proj_id
2146 , X_task_id => X_dest_task_id
2147 , X_ei_date => X_expenditure_item_date
2148 , X_expenditure_type => X_expenditure_type
2149 , X_non_labor_resource => NULL
2150 , X_person_id => X_inc_by_person_id
2151 , X_quantity => 0
2152 , X_denom_currency_code => X_denom_currency_code
2153 , X_acct_currency_code => X_Acct_currency_code
2154 , X_denom_raw_cost => X_denom_raw_cost
2155 , X_acct_raw_cost => NULL
2156 , X_acct_rate_type => NULL
2157 , X_acct_rate_date => NULL
2158 , X_acct_exchange_rate => NULL
2159 , X_transfer_ei => NULL
2160 , X_incurred_by_org_id => X_inc_by_org_id
2161 , X_nl_resource_org_id => NULL
2162 , X_transaction_source => NULL
2163 , X_calling_module => 'CreateRelatedItem'
2164 , X_vendor_id => NULL
2165 , X_entered_by_user_id => X_userid
2166 , X_attribute_category => X_attribute_category
2167 , X_attribute1 => X_attribute1
2168 , X_attribute2 => X_attribute2
2169 , X_attribute3 => X_attribute3
2170 , X_attribute4 => X_attribute4
2171 , X_attribute5 => X_attribute5
2172 , X_attribute6 => X_attribute6
2173 , X_attribute7 => X_attribute7
2174 , X_attribute8 => X_attribute8
2175 , X_attribute9 => X_attribute9
2176 , X_attribute10 => X_attribute10
2177 , X_attribute11 => ''
2178 , X_attribute12 => ''
2179 , X_attribute13 => ''
2180 , X_attribute14 => ''
2181 , X_attribute15 => ''
2182 , X_msg_application => temp_msg_application
2183 , X_msg_type => temp_msg_type
2184 , X_msg_token1 => temp_msg_token1
2185 , X_msg_token2 => temp_msg_token2
2186 , X_msg_token3 => temp_msg_token3
2187 , X_msg_count => temp_msg_count
2188 , X_msg_data => temp_outcome
2189 , X_billable_flag => X_billable_flag
2190 , p_projfunc_currency_code => l_projfunc_currency_code
2191 , p_projfunc_cost_rate_type => l_projfunc_cost_rate_type
2192 , p_projfunc_cost_rate_date => l_projfunc_cost_rate_date
2193 , p_projfunc_cost_exchg_rate => l_projfunc_cost_exchange_rate
2194 , p_assignment_id => l_assignment_id
2195 , p_work_type_id => l_work_type_id
2196 , p_sys_link_function => X_etype_class);
2197 /* Added p_sys_link_function => X_etype_class for bug3557261 */
2198
2199 IF P_DEBUG_MODE THEN
2200 pa_cc_utils.log_message('CreateRelatedItem: ' || 'After Call to PATC');
2201 END IF;
2202
2203 /* Start of Bug 2648550 */
2204 l_assignment_id := PATC.G_OVERIDE_ASSIGNMENT_ID;
2205 l_work_type_id := PATC.G_OVERIDE_WORK_TYPE_ID;
2206 /* End of Bug 2648550 */
2207 IF (( temp_outcome IS NOT NULL) and
2208 ( temp_msg_type = 'E') ) THEN
2209 -- Since this is a batch program, we handle only errors,no warnings
2210
2211 X_outcome := temp_outcome;
2212 X_status := 1;
2213 pa_cc_utils.reset_curr_function ;
2214 RETURN;
2215
2216 END IF;
2217
2218 /* Start of Bug 5984498 */
2219
2220 IF (l_gms_enabled = 'Y' ) and (x_award_id is NOT NULL) THEN
2221 gms_transactions_pub.validate_transaction( X_dest_proj_id
2222 , X_dest_task_id
2223 , x_award_id
2224 , X_expenditure_type
2225 , X_expenditure_item_date
2226 , 'CLIENT_EXTN'
2227 , temp_outcome ) ;
2228
2229 IF P_DEBUG_MODE THEN
2230 pa_cc_utils.log_message('CreateRelatedItem: ' || 'After Call to gms_transactions_pub.validate_transaction');
2231 END IF;
2232
2233 IF ( temp_outcome IS NOT NULL) THEN
2234
2235 X_outcome := temp_outcome;
2236 pa_cc_utils.reset_curr_function ;
2237 RETURN;
2238
2239 END IF;
2240 END IF;
2241
2242 /* End of Bug 5984498 */
2243
2244
2245 -- Passed denom_raw_cost will be stored in array using LoadEi procedure
2246 -- All other new currency attributes (introduced in Multi-Currency Transactions)
2247 -- will be set to NULL.
2248 -- Costing program will calculate appropriate values for accounting and
2249 -- project currency columns
2250
2251 -- Fix for Bug # 813758
2252 -- Need to pass denom acct and proj currency as well as project curr attributes
2253 -- since for the related item this should be the same as the parent
2254
2255
2256 /*
2257 * IC related change
2258 * Send the Recvr_Org_Id to LOADEI using the new API defined
2259 * in PA_UTILS2
2260 */
2261 IF P_DEBUG_MODE THEN
2262 pa_cc_utils.log_message('CreateRelatedItem: ' || 'Before Call to LoadEi');
2263 END IF;
2264
2265 pa_transactions.LoadEi( X_expenditure_item_id => X_expenditure_item_id
2266 ,X_expenditure_id => X_expenditure_id
2267 ,X_expenditure_item_date => X_expenditure_item_date
2268 ,X_project_id => x_dest_proj_id --Bug fix : 2201207 NULL
2269 ,X_task_id => X_dest_task_id
2270 ,X_expenditure_type => X_expenditure_type
2271 ,X_non_labor_resource => NULL
2272 ,X_nl_resource_org_id => NULL
2273 ,X_quantity => 0
2274 ,X_raw_cost => NULL
2275 ,X_raw_cost_rate => X_denom_raw_cost_rate
2276 ,X_override_to_org_id => X_override_to_org_id
2277 ,X_billable_flag => X_billable_flag
2278 ,X_bill_hold_flag => X_bill_hold_flag
2279 ,X_orig_transaction_ref => NULL
2280 ,X_transferred_from_ei => NULL
2281 ,X_adj_expend_item_id => NULL
2282 ,X_attribute_category => X_attribute_category
2283 ,X_attribute1 => X_attribute1
2284 ,X_attribute2 => X_attribute2
2285 ,X_attribute3 => X_attribute3
2286 ,X_attribute4 => X_attribute4
2287 ,X_attribute5 => X_attribute5
2288 ,X_attribute6 => X_attribute6
2289 ,X_attribute7 => X_attribute7
2290 ,X_attribute8 => X_attribute8
2291 ,X_attribute9 => X_attribute9
2292 ,X_attribute10 => X_attribute10
2293 ,X_ei_comment => X_comment
2294 ,X_transaction_source => NULL
2295 ,X_source_exp_item_id => X_source_exp_item_id
2296 ,i => 1
2297 ,X_job_id => X_job_id
2298 ,X_org_id => X_org_id
2299 ,X_labor_cost_multiplier_name => l_dest_lcm
2300 ,X_drccid => NULL
2301 ,X_crccid => NULL
2302 ,X_cdlsr1 => NULL
2303 ,X_cdlsr2 => NULL
2304 ,X_cdlsr3 => NULL
2305 ,X_gldate => NULL
2306 ,X_bcost => NULL
2307 ,X_bcostrate => NULL
2308 ,X_etypeclass => X_etype_class
2309 ,X_burden_sum_dest_run_id => NULL
2310 ,X_burden_compile_set_id => NULL
2311 ,X_receipt_currency_amount => NULL
2312 ,X_receipt_currency_code => NULL
2313 ,X_receipt_exchange_rate => NULL
2314 ,X_denom_currency_code => X_denom_currency_code,
2315 X_denom_raw_cost => X_denom_raw_cost
2316 ,X_denom_burdened_cost => NULL
2317 ,X_acct_currency_code => X_Acct_currency_code
2318 ,X_acct_rate_date => NULL
2319 ,X_acct_rate_type => NULL
2320 ,X_acct_exchange_rate => NULL
2321 ,X_acct_raw_cost => NULL
2322 ,X_acct_burdened_cost => NULL
2323 ,X_acct_exchange_rounding_limit => NULL
2324 ,X_project_currency_code => X_project_currency_code
2325 ,X_project_rate_date => X_project_rate_date
2326 ,X_project_rate_type => X_project_rate_type
2327 ,X_project_exchange_rate => X_project_exchange_rate
2328 ,X_Recv_Operating_Unit => PA_UTILS2.GetPrjOrgId(X_dest_proj_id,
2329 X_dest_task_id)
2330 , p_assignment_id => l_ASSIGNMENT_ID
2331 , p_work_type_id => l_WORK_TYPE_ID
2332 , p_projfunc_currency_code => l_PROJFUNC_CURRENCY_CODE
2333 , p_projfunc_cost_rate_date => l_PROJFUNC_COST_RATE_DATE
2334 , p_projfunc_cost_rate_type => l_PROJFUNC_COST_RATE_TYPE
2335 , p_projfunc_cost_exchange_rate => l_PROJFUNC_COST_EXCHANGE_RATE
2336 , p_project_raw_cost => l_PROJECT_RAW_COST
2337 , p_project_burdened_cost => l_PROJECT_BURDENED_COST
2338 , p_cbs_element_id => l_cbs_element_id -- Added for CBS Enhancement Bug 16220146
2339 );
2340 IF P_DEBUG_MODE THEN
2341 pa_cc_utils.log_message('CreateRelatedItem: ' || 'After call to LoadEi');
2342 END IF;
2343
2344 pa_transactions.InsItems( X_user => X_userid
2345 , X_login => 0
2346 , X_module => 'CreateRelatedItem'
2347 , X_calling_process => 'RELATED_ITEM'
2348 , Rows => 1
2349 , X_status => temp_status
2350 , X_gl_flag => 'N' );
2351 IF P_DEBUG_MODE THEN
2352 pa_cc_utils.log_message('CreateRelatedItem: ' || 'After call to InsItems');
2353 END IF;
2354
2355 /*start of bug 5769510*/
2356 IF (l_gms_enabled = 'Y') THEN
2357
2358 if (x_award_id is NULL) then
2359
2360 select DISTINCT award_id
2361 into source_award_id
2362 from gms_award_distributions adl
2363 where adl.expenditure_item_id = X_source_exp_item_id
2364 and adl.document_type = 'EXP'
2365 and adl_status = 'A' ;
2366 Else
2367 source_award_id := x_award_id ;
2368 END IF;
2369
2370
2371 adl_rec.expenditure_item_id := X_expenditure_item_id ;
2372 adl_rec.cost_distributed_flag := 'N';
2373 adl_rec.project_id := x_dest_proj_id;
2374 adl_rec.task_id := X_dest_task_id ;
2375 adl_rec.cdl_line_num := NULL; -- Bug 1906331
2376 adl_rec.adl_line_num := 1;
2377 adl_rec.distribution_value := 100;
2378 adl_rec.line_type :='R';
2379 adl_rec.adl_status := 'A';
2380 adl_rec.document_type := 'EXP';
2381 adl_rec.billed_flag := 'N';
2382 adl_rec.bill_hold_flag := NULL ;
2383 adl_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id;
2384 adl_rec.award_id := source_award_id ;
2385 adl_rec.raw_cost := NULL;
2386 adl_rec.last_update_date := sysdate ;
2387 adl_rec.creation_date := sysdate;
2388 adl_rec.last_updated_by := X_userid ;
2389 adl_rec.created_by := X_userid ;
2390 adl_rec.last_update_login := 0 ;
2391
2392 gms_awards_dist_pkg.create_adls(adl_rec);
2393
2394 END IF;
2395
2396 pa_transactions.FlushEiTabs;
2397
2398 X_status := 0;
2399 X_outcome := NULL;
2400 IF P_DEBUG_MODE THEN
2401 pa_cc_utils.log_message('CreateRelatedItem: ' || 'End ');
2402 END IF;
2403 pa_cc_utils.reset_curr_function ;
2404
2405 EXCEPTION
2406 WHEN NO_DATA_FOUND THEN
2407 X_status := -1403;
2408 WHEN OTHERS THEN
2409 X_status := SQLCODE;
2410 RAISE;
2411
2412 END CreateRelatedItem;
2413
2414
2415 -- Modified the parameter name from raw_cost to denom_raw_cost as well as
2416 -- raw_cost_rate as denom_raw_cost_rate
2417 -- Note : In this phase (11.1), cost_rates will be defined in accounting
2418 -- currency (i.e. currency code of set of books in which costing
2419 -- will be done)
2420 PROCEDURE UpdateRelatedItem( X_expenditure_item_id IN NUMBER
2421 , X_denom_raw_cost IN NUMBER
2422 , X_denom_raw_cost_rate IN NUMBER
2423 , X_status OUT NOCOPY NUMBER
2424 , X_work_type_name IN VARCHAR2 /*bug2482593*/
2425 )
2426 IS
2427
2428 l_work_type_id NUMBER; /* bug2482593 */
2429
2430 BEGIN
2431 pa_cc_utils.set_curr_function('UpdateRelatedItem');
2432 IF P_DEBUG_MODE THEN
2433 pa_cc_utils.log_message('UpdateRelatedItem: ' || 'Start ');
2434 END IF;
2435
2436 /* changes for bug 2482593 start */
2437 if(X_work_type_name IS NOT NULL) then
2438 Begin
2439 select work_type_id
2440 into l_work_type_id
2441 from pa_work_types_tl
2442 where name=X_work_type_name AND
2443 language = userenv('LANG');
2444 exception
2445 when no_data_found then
2446 X_status := 10;
2447 IF P_DEBUG_MODE THEN
2448 pa_cc_utils.log_message('UpdateRelatedItem: ' || 'Invalid Work Type Name');
2449 END IF;
2450 pa_cc_utils.reset_curr_function ;
2451 return;
2452 end;
2453
2454 /* else
2455 {
2456 pa_cc_utils.log_message("Work Type ID defaults from source EI");
2457 select work_type_id
2458 into l_work_type_id
2459 from pa_expenditure_items
2460 where expenditure_item_id = X_Expenditure_item_id;
2461 }
2462 */
2463 end if;
2464 /* changes for bug 2482593 end */
2465
2466 UPDATE pa_expenditure_items ei
2467 SET ei.denom_raw_cost = pa_currency.round_trans_currency_amt(X_denom_raw_cost,
2468 ei.denom_currency_code)
2469 ,ei.RAW_COST = NULL /* Added for bug#5067217 */
2470 ,ei.ACCT_RAW_COST = NULL /* Added for bug#5067217*/
2471 ,ei.PROJECT_RAW_COST = NULL /* Added for bug#5067217 */
2472 ,ei.raw_cost_rate = X_denom_raw_cost_rate
2473 ,ei.work_type_id = l_work_type_id /* bug2482593 */
2474 WHERE ei.expenditure_item_id = X_expenditure_item_id
2475 AND (ei.adjusted_expenditure_item_id is NULL or ei.denom_raw_cost is NULL); /*bug 5617096*/
2476
2477 /*
2478 * Commented for Bug 5617096
2479 UPDATE pa_expenditure_items ei
2480 SET
2481 ei.denom_raw_cost = pa_currency.round_trans_currency_amt(X_denom_raw_cost,
2482 ei.denom_currency_code)
2483 , ei.raw_cost_rate = X_denom_raw_cost_rate
2484 , ei.work_type_id = l_work_type_id ** bug2482593 **
2485 WHERE
2486 ei.expenditure_item_id = X_expenditure_item_id;
2487 */
2488 X_status := 0;
2489 IF P_DEBUG_MODE THEN
2490 pa_cc_utils.log_message('UpdateRelatedItem: ' || 'End ');
2491 END IF;
2492 pa_cc_utils.reset_curr_function ;
2493
2494 EXCEPTION
2495 WHEN NO_DATA_FOUND THEN
2496 X_status := -1403;
2497 WHEN OTHERS THEN
2498 X_status := SQLCODE;
2499 RAISE;
2500
2501 END UpdateRelatedItem;
2502
2503 /* Added as a fix for bug# 1358018 */
2504
2505 PROCEDURE UpdateSystemLinkFunc (
2506 X_expend_item_id IN NUMBER
2507 , X_sys_link_func IN VARCHAR2
2508 )
2509 IS
2510 BEGIN
2511
2512 UPDATE pa_expenditure_items ei
2513 SET ei.system_linkage_function = X_sys_link_func
2514 WHERE ei.source_expenditure_item_id = X_expend_item_id;
2515
2516
2517 EXCEPTION
2518 WHEN OTHERS THEN
2519 RAISE;
2520 END UpdateSystemLinkFunc;
2521
2522
2523 END PA_TRANSACTIONS;