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