[Home] [Help]
PACKAGE BODY: APPS.GMS_TRANSACTIONS
Source
1 PACKAGE BODY GMS_TRANSACTIONS AS
2 /* $Header: GMSTRANB.pls 115.3 2002/11/26 12:41:21 mmalhotr ship $ */
3
4
5 -- ========================================================================
6 -- PROCEDURE LoadEi
7 -- ========================================================================
8
9 PROCEDURE LoadEi( X_expenditure_item_id IN NUMBER
10 , X_expenditure_id IN NUMBER
11 , X_expenditure_item_date IN DATE
12 , X_project_id IN NUMBER
13 , X_task_id IN NUMBER
14 , X_expenditure_type IN VARCHAR2
15 , X_non_labor_resource IN VARCHAR2
16 , X_nl_resource_org_id IN NUMBER
17 , X_quantity IN NUMBER
18 , X_raw_cost IN NUMBER
19 , X_raw_cost_rate IN NUMBER
20 , X_override_to_org_id IN NUMBER
21 , X_billable_flag IN VARCHAR2
22 , X_bill_hold_flag IN VARCHAR2
23 , X_orig_transaction_ref IN VARCHAR2
24 , X_transferred_from_ei IN NUMBER
25 , X_adj_expend_item_id IN NUMBER
26 , X_attribute_category IN VARCHAR2
27 , X_attribute1 IN VARCHAR2
28 , X_attribute2 IN VARCHAR2
29 , X_attribute3 IN VARCHAR2
30 , X_attribute4 IN VARCHAR2
31 , X_attribute5 IN VARCHAR2
32 , X_attribute6 IN VARCHAR2
33 , X_attribute7 IN VARCHAR2
34 , X_attribute8 IN VARCHAR2
35 , X_attribute9 IN VARCHAR2
36 , X_attribute10 IN VARCHAR2
37 , X_ei_comment IN VARCHAR2
38 , X_transaction_source IN VARCHAR2
39 , X_source_exp_item_id IN NUMBER
40 , i IN BINARY_INTEGER
41 , X_job_id IN NUMBER default null
42 , X_org_id IN NUMBER default null
43 , X_labor_cost_multiplier_name IN VARCHAR2
44 , X_drccid IN NUMBER
45 , X_crccid IN NUMBER
46 , X_cdlsr1 IN VARCHAR2
47 , X_cdlsr2 IN VARCHAR2
48 , X_cdlsr3 IN VARCHAR2
49 , X_gldate IN DATE
50 , X_bcost IN NUMBER
51 , X_bcostrate IN NUMBER
52 , X_etypeclass IN VARCHAR2
53 , X_burden_sum_dest_run_id IN NUMBER default null
54 , X_burden_compile_set_id IN NUMBER default null
55 , X_receipt_currency_amount IN NUMBER default null
56 , X_receipt_currency_code IN VARCHAR2 default NULL
57 , X_receipt_exchange_rate IN NUMBER default NULL
58 , X_denom_currency_code IN VARCHAR2 default NULL
59 , X_denom_raw_cost IN NUMBER default NULL
60 , X_denom_burdened_cost IN NUMBER default NULL
61 , X_acct_currency_code IN VARCHAR2 default null
62 , X_acct_rate_date IN DATE default NULL
63 , X_acct_rate_type IN VARCHAR2 default NULL
64 , X_acct_exchange_rate IN NUMBER default NULL
65 , X_acct_raw_cost IN NUMBER default NULL
66 , X_acct_burdened_cost IN NUMBER default NULL
67 , X_acct_exchange_rounding_limit IN NUMBER default NULL
68 , X_project_currency_code IN VARCHAR2 default NULL
69 , X_project_rate_date IN DATE default NULL
70 , X_project_rate_type IN VARCHAR2 default NULL
71 , X_project_exchange_rate IN NUMBER default NULL
72 , X_Cross_Charge_Type IN Varchar2 default 'NO'
73 , X_Cross_Charge_Code IN VArchar2 default 'P'
74 , X_Prvdr_organization_id IN Number default NULL
75 , X_Recv_organization_id IN Number default NULL
76 , X_Recv_Operating_Unit IN Number default NULL
77 , X_Borrow_Lent_Dist_Code IN VARCHAR2 default 'X'
78 , X_Ic_Processed_Code IN VARCHAR2 default 'X'
79 , X_Denom_Tp_Currency_Code IN Varchar2 default NULL
80 , X_Denom_Transfer_Price IN Number default NULL
81 , X_Acct_Tp_Rate_Type IN Varchar2 default NULL
82 , X_Acct_Tp_Rate_Date IN DATE default NULL
83 , X_Acct_Tp_Exchange_Rate IN Number default NULL
84 , X_ACCT_TRANSFER_PRICE IN Number default NULL
85 , X_PROJACCT_TRANSFER_PRICE IN Number default NULL
86 , X_CC_MARKUP_BASE_CODE IN Varchar2 default NULL
87 , X_TP_BASE_AMOUNT IN Number default NULL
88 , X_Tp_Ind_Compiled_Set_Id IN Number default NULL
89 , X_Tp_Bill_Rate IN Number default NULL
90 , X_Tp_Bill_Markup_Percentage IN Number default NULL
91 , X_Tp_Schedule_Line_Percentage IN Number default NULL
92 , X_Tp_Rule_Percentage IN Number default NULL )
93
94 IS
95 BEGIN
96 null;
97 /*
98 -- dbms_output.put_line('In Loadei:'||to_char(i));
99 pa_cc_utils.set_curr_function('LoadEi');
100 pa_cc_utils.log_message('Start ');
101
102 EiIdTab(i) := X_expenditure_item_id;
103 EIdTab(i) := X_expenditure_id;
104 ProjIdTab(i) := X_project_id;
105 TskIdTab(i) := X_task_id;
106 EiDateTab(i) := X_expenditure_item_date;
107 ETypTab(i) := X_expenditure_type;
108 NlRscTab(i) := X_non_labor_resource;
109 NlRscOrgTab(i) := X_nl_resource_org_id;
110 BillFlagTab(i) := X_billable_flag;
111 BillHoldTab(i) := X_bill_hold_flag;
112 QtyTab(i) := X_quantity;
113 RawCostTab(i) := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(X_raw_cost, X_project_currency_code);
114 RawRateTab(i) := X_raw_cost_rate;
115 OvrOrgTab(i) := X_override_to_org_id;
116 AdjEiTab(i) := X_adj_expend_item_id;
117 TfrEiTab(i) := X_transferred_from_ei;
118 TrxRefTab(i) := X_orig_transaction_ref;
119 EiTrxSrcTab(i) := X_transaction_source;
120 AttCatTab(i) := X_attribute_category;
121 Att1Tab(i) := X_attribute1;
122 Att2Tab(i) := X_attribute2;
123 Att3Tab(i) := X_attribute3;
124 Att4Tab(i) := X_attribute4;
125 Att5Tab(i) := X_attribute5;
126 Att6Tab(i) := X_attribute6;
127 Att7Tab(i) := X_attribute7;
128 Att8Tab(i) := X_attribute8;
129 Att9Tab(i) := X_attribute9;
130 Att10Tab(i) := X_attribute10;
131 SrcEiTab(i) := X_source_exp_item_id;
132 EiCommentTab(i) := X_ei_comment;
133 JobIdTab(i) := X_job_id;
134 OrgIdTab(i) := X_org_id;
135 LCMTab(i) := X_labor_cost_multiplier_name ;
136 DrccidIdTab(i) := X_drccid ;
137 CrccidIdTab(i) := X_crccid ;
138 Cdlsr1Tab(i) := X_cdlsr1 ;
139 Cdlsr2Tab(i) := X_cdlsr2 ;
140 Cdlsr3Tab(i) := X_cdlsr3 ;
141 GldateTab(i) := X_gldate ;
142 BCostTab(i) := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(X_bcost,X_project_currency_code) ;
143 BCostRateTab(i) := X_bcostrate ;
144 EtypeClassTab(i) := X_etypeclass ;
145 BurdenDestid(i) := X_burden_sum_dest_run_id ;
146 BurdenCompSetId(i) := X_burden_compile_set_id;
147 ReceiptCurrAmt(i) := X_receipt_currency_amount;
148 ReceiptCurrCode(i) := X_receipt_currency_code;
149 ReceiptExRate(i) := X_receipt_exchange_rate;
150 DenomCurrCode(i) := X_denom_currency_code;
151 DenomRawCost(i) := X_denom_raw_cost;
152 DenomBurdenCost(i) := X_denom_burdened_cost;
153 AcctCurrCode(i) := X_acct_currency_code;
154 AcctRateDate(i) := X_acct_rate_date;
155 AcctRateType(i) := X_acct_rate_type;
156 AcctExRate(i) := X_acct_exchange_rate;
157 AcctRawCost(i) := X_acct_raw_cost;
158 AcctBurdenCost(i) := X_acct_burdened_cost;
159 AcctRoundLmt(i) := X_acct_exchange_rounding_limit;
160 ProjCurrCode(i) := X_project_currency_code;
161 ProjRateDate(i) := X_project_rate_date;
162 ProjRateType(i) := X_project_rate_type;
163 ProjExRate(i) := X_project_exchange_rate;
164
165
166 -- IC Changes
167 CrossChargeTypeTab(i) := X_Cross_Charge_Type;
168 CrossChargeCodeTab(i) := X_Cross_Charge_Code;
169 PrvdrOrganizationTab(i) := X_Prvdr_organization_id;
170 RecvOrganizationTab(i) := X_Recv_organization_id;
171 RecvOperUnitTab(i) := NVL(X_Recv_Operating_Unit,
172 PA_UTILS2.GetPrjOrgId(X_project_id,X_task_id));
173 IcProcessedCodeTab(i) := X_Ic_Processed_Code;
174 BorrowLentCodeTab(i) := X_Borrow_Lent_Dist_Code;
175 DenomTpCurrCodeTab(i) := X_Denom_Tp_Currency_Code;
176 DenomTransferPriceTab(i) := X_Denom_Transfer_Price;
177 AcctTpRateTypeTab(i) := X_Acct_Tp_Rate_Type;
178 AcctTpRateDateTab(i) := X_Acct_Tp_Rate_Date;
179 AcctTpExchangeRateTab(i) := X_Acct_Tp_Exchange_Rate;
180 AcctTransferPriceTab(i) := X_ACCT_TRANSFER_PRICE;
181 ProjacctTransferPriceTab(i) := X_PROJACCT_TRANSFER_PRICE;
182 CcMarkupBaseCodeTab(i) := X_CC_MARKUP_BASE_CODE;
183 TpBaseAmountTab(i) := X_TP_BASE_AMOUNT;
184 TpIndCompiledSetIdTab(i) := X_Tp_Ind_Compiled_Set_Id;
185 TpBillRateTab(i) := X_Tp_Bill_Rate;
186 TpBillMarkupPercentageTab(i) := X_Tp_Bill_Markup_Percentage;
187 TpSchLinePercentageTab(i) := X_Tp_Schedule_Line_Percentage;
188 TpRulePercentageTab(i) := X_Tp_Rule_Percentage;
189 -- END IC Changes
190 pa_cc_utils.log_message('End ');
191 pa_cc_utils.reset_curr_function ;
192 */
193 END LoadEi;
194
195
196 -- ========================================================================
197 -- PROCEDURE FlushEiTabs
198 -- ========================================================================
199
200 PROCEDURE FlushEiTabs
201 IS
202 BEGIN
203 null;
204 /*
205 pa_cc_utils.set_curr_function('FlushEiTabs');
206 pa_cc_utils.log_message('Start ');
207
208 EiIdTab := pa_utils.EmptyIdTab;
209 EIdTab := pa_utils.EmptyIdTab;
210 TskIdTab := pa_utils.EmptyIdTab;
211 EiDateTab := pa_utils.EmptyDateTab;
212 ETypTab := pa_utils.EmptyChar30Tab;
213 NlRscTab := pa_utils.EmptyChar20Tab;
214 NlRscOrgTab := pa_utils.EmptyIdTab;
215 BillFlagTab := pa_utils.EmptyChar1Tab;
216 BillHoldTab := pa_utils.EmptyChar1Tab;
217 QtyTab := pa_utils.EmptyAmtTab;
218 RawCostTab := pa_utils.EmptyAmtTab;
219 RawRateTab := pa_utils.EmptyAmtTab;
220 OvrOrgTab := pa_utils.EmptyIdTab;
221 AdjEiTab := pa_utils.EmptyIdTab;
222 TfrEiTab := pa_utils.EmptyIdTab;
223 TrxRefTab := pa_utils.EmptyChar30Tab;
224 EiTrxSrcTab := pa_utils.EmptyChar30Tab;
225 AttCatTab := pa_utils.EmptyChar30Tab;
226 Att1Tab := pa_utils.EmptyChar150Tab;
227 Att2Tab := pa_utils.EmptyChar150Tab;
228 Att3Tab := pa_utils.EmptyChar150Tab;
229 Att4Tab := pa_utils.EmptyChar150Tab;
230 Att5Tab := pa_utils.EmptyChar150Tab;
231 Att6Tab := pa_utils.EmptyChar150Tab;
232 Att7Tab := pa_utils.EmptyChar150Tab;
233 Att8Tab := pa_utils.EmptyChar150Tab;
234 Att9Tab := pa_utils.EmptyChar150Tab;
235 Att10Tab := pa_utils.EmptyChar150Tab;
236 SrcEiTab := pa_utils.EmptyIdTab;
240 LCMTab := pa_utils.EmptyChar20Tab ;
237 EiCommentTab := pa_utils.EmptyChar240Tab;
238 JobIdTab := pa_utils.EmptyIdTab;
239 OrgIdTab := pa_utils.EmptyIdTab;
241 DrccidIdTab := pa_utils.EmptyIdTab ;
242 CrccidIdTab := pa_utils.EmptyIdTab ;
243 Cdlsr1Tab := pa_utils.EmptyChar30Tab ;
244 Cdlsr2Tab := pa_utils.EmptyChar30Tab ;
245 Cdlsr3Tab := pa_utils.EmptyChar30Tab ;
246 GldateTab := pa_utils.EmptyDateTab ;
247 BCostTab := pa_utils.EmptyAmtTab;
248 BCostRateTab := pa_utils.EmptyAmtTab;
249 EtypeClassTab := pa_utils.EmptyChar30Tab ;
250 BurdenDestId := pa_utils.EmptyIdTab ;
251 BurdenCompSetId := pa_utils.EmptyIdTab ;
252 ReceiptCurrAmt := pa_utils.EmptyNewAmtTab;
253 ReceiptCurrCode := pa_utils.EmptyChar15Tab;
254 ReceiptExRate := pa_utils.EmptyNewAmtTab;
255 DenomCurrCode := pa_utils.EmptyChar15Tab;
256 DenomRawCost := pa_utils.EmptyNewAmtTab;
257 DenomBurdenCost := pa_utils.EmptyNewAmtTab;
258 AcctCurrCode := pa_utils.EmptyChar15Tab;
259 AcctRateDate := pa_utils.EmptyDateTab;
260 AcctRateType := pa_utils.EmptyChar30Tab ;
261 AcctExRate := pa_utils.EmptyNewAmtTab;
262 AcctRawCost := pa_utils.EmptyNewAmtTab;
263 AcctBurdenCost := pa_utils.EmptyNewAmtTab;
264 AcctRoundLmt := pa_utils.EmptyNewAmtTab;
265 ProjCurrCode := pa_utils.EmptyChar15Tab;
266 ProjRateType := pa_utils.EmptyChar30Tab ;
267 ProjRateDate := pa_utils.EmptyDateTab;
268 ProjExRate := pa_utils.EmptyNewAmtTab;
269
270 -- IC Changes
271 CrossChargeTypeTab := pa_utils.EmptyChar10Tab ;
272 CrossChargeCodeTab := pa_utils.EmptyChar1Tab ;
273 PrvdrOrganizationTab := pa_utils.EmptyIdTab ;
274 RecvOrganizationTab := pa_utils.EmptyIdTAb ;
275 RecvOperUnitTab := pa_utils.EmptyIdTab ;
276 IcProcessedCodeTab := pa_utils.EmptyChar1Tab ;
277 BorrowLentCodeTab := pa_utils.EmptyChar1Tab ;
278 DenomTpCurrCodeTab := pa_utils.EmptyChar15Tab;
279 DenomTransferPriceTab := pa_utils.EmptyNewAmtTab;
280 AcctTpRateTypeTab := pa_utils.EmptyChar30Tab;
281 AcctTpRateDateTab := pa_utils.EmptyDateTab;
282 AcctTpExchangeRateTab := pa_utils.EmptyNewAmtTab;
283 AcctTransferPriceTab := pa_utils.EmptyNewAmtTab;
284 ProjacctTransferPriceTab := pa_utils.EmptyNewAmtTab;
285 CcMarkupBaseCodeTab := pa_utils.EmptyChar1TAb;
286 TpBaseAmountTab := pa_utils.EmptyNewAmtTab;
287 TpIndCompiledSetIdTab := pa_utils.EmptyIdTab;
288 TpBillRateTab := pa_utils.EmptyNewAmtTab;
289 TpBillMarkupPercentageTab := pa_utils.EmptyAmtTab;
290 TpSchLinePercentageTab := pa_utils.EmptyAmtTab;
291 TpRulePercentageTab := pa_utils.EmptyAmtTab;
292 --END IC Changes
293 pa_cc_utils.log_message('End ');
294 pa_cc_utils.reset_curr_function ;
295 */
296
297 END FlushEiTabs;
298
299
300
301 -- ========================================================================
302 -- PROCEDURE InsItemComment
303 -- ========================================================================
304 /*
305 PROCEDURE InsItemComment ( X_ei_id IN NUMBER
306 , X_ei_comment IN VARCHAR2
307 , X_user IN NUMBER
308 , X_login IN NUMBER
309 , X_status OUT NOCOPY NUMBER )
310 IS
311 BEGIN
312
313
314 pa_cc_utils.set_curr_function('InsItemComment');
315 pa_cc_utils.log_message('Start ');
316
317 INSERT INTO pa_expenditure_comments (
318 expenditure_item_id
319 , line_number
320 , expenditure_comment
321 , last_update_date
322 , last_updated_by
323 , creation_date
324 , created_by
325 , last_update_login )
326 VALUES (
327 X_ei_id -- expenditure_item_id
328 , 10 -- line_number
329 , X_ei_comment -- expenditure_comment
330 , sysdate -- last_update_date
331 , X_user -- last_updated_by
332 , sysdate -- creation_date
333 , X_user -- created_by
334 , X_login ); -- last_update_login
335
336 X_status := 0;
337 pa_cc_utils.log_message('End ');
338 pa_cc_utils.reset_curr_function ;
339
340 EXCEPTION
341 WHEN OTHERS THEN
342 X_status := SQLCODE;
343 RAISE;
344
345 END InsItemComment;
346
347 */
348 -- ========================================================================
349 -- PROCEDURE InsItems
350 -- ========================================================================
351
352 PROCEDURE InsItems( X_user IN NUMBER
353 , X_login IN NUMBER
354 , X_module IN VARCHAR2
355 , X_calling_process IN VARCHAR2
356 , Rows IN BINARY_INTEGER
357 , X_status OUT NOCOPY NUMBER
358 , X_gl_flag IN VARCHAR2 )
359 IS
360 temp_status NUMBER DEFAULT NULL;
361
362 -- Added the following variables to update the request_id, program_id and program_app_id cols.
363 -- Selva 03/13/97
364
365 x_request_id NUMBER(15);
366 x_program_application_id NUMBER(15);
367 x_program_id NUMBER(15);
368 x_err_code NUMBER(7) DEFAULT 0 ;
369 x_err_stage VARCHAR2(255) ;
370 x_err_stack VARCHAR2(255) ;
371
372 BEGIN
373 null;
377
374 /*
375 pa_cc_utils.set_curr_function('InsItems');
376 pa_cc_utils.log_message('Start ');
378 X_request_id := FND_GLOBAL.CONC_REQUEST_ID ;
379 X_program_id := FND_GLOBAL.CONC_PROGRAM_ID ;
380 X_program_application_id := FND_GLOBAL.PROG_APPL_ID ;
381
382 -- -- dbms_output.PUT_LINE(' Gl flag : ' || X_gl_flag ) ;
383
384 FOR i IN 1..Rows LOOP
385 pa_cc_utils.log_message('Start of Loop');
386 -- -- dbms_output.PUT_LINE(' Gl date : ' || to_char( GldateTab(i), 'DD-MON-YY') ) ;
387 IF nvl(X_gl_flag,'N') <> 'Y' THEN
388
389 INSERT INTO gms_encumbrance_items_all (
390 expenditure_item_id
391 , expenditure_id
392 , expenditure_item_date
393 , task_id
394 , expenditure_type
395 , cost_distributed_flag
396 , revenue_distributed_flag
397 , billable_flag
398 , bill_hold_flag
399 , net_zero_adjustment_flag
400 , non_labor_resource
401 , organization_id
402 , quantity
403 , raw_cost
404 , raw_cost_rate
405 , override_to_organization_id
406 , orig_transaction_reference
407 , transaction_source
408 , adjusted_expenditure_item_id
409 , attribute_category
410 , attribute1
411 , attribute2
412 , attribute3
413 , attribute4
414 , attribute5
415 , attribute6
416 , attribute7
417 , attribute8
418 , attribute9
419 , attribute10
420 , source_expenditure_item_id
421 , transferred_from_exp_item_id
422 , last_update_date
423 , last_updated_by
424 , creation_date
425 , created_by
426 , last_update_login
427 , job_id
428 , org_id
429 , labor_cost_multiplier_name
430 , cost_burden_distributed_flag
431 , burden_cost
432 , burden_cost_rate
433 , request_id
434 , program_application_id
435 , program_id
436 , system_linkage_function
437 , burden_sum_dest_run_id
438 , cost_ind_compiled_set_id
439 , receipt_currency_amount
440 , receipt_currency_code
441 , receipt_exchange_rate
442 , denom_currency_code
443 , denom_raw_cost
444 , denom_burdened_cost
445 , acct_currency_code
446 , acct_rate_date
447 , acct_rate_type
448 , acct_exchange_rate
449 , acct_raw_cost
450 , acct_burdened_cost
451 , acct_exchange_rounding_limit
452 , project_currency_code
453 , project_rate_date
454 , project_rate_type
455 , project_exchange_rate
456 , CC_CROSS_CHARGE_TYPE,
457 CC_CROSS_CHARGE_CODE,
458 CC_PRVDR_ORGANIZATION_ID,
459 CC_RECVR_ORGANIZATION_ID,
460 RECVR_ORG_ID,
461 CC_BL_DISTRIBUTED_CODE,
462 CC_IC_PROCESSED_CODE,
463 DENOM_TP_CURRENCY_CODE,
464 DENOM_TRANSFER_PRICE,
465 ACCT_TP_RATE_TYPE,
466 ACCT_TP_RATE_DATE,
467 ACCT_TP_EXCHANGE_RATE,
468 ACCT_TRANSFER_PRICE,
469 PROJACCT_TRANSFER_PRICE,
470 CC_MARKUP_BASE_CODE,
471 TP_BASE_AMOUNT,
472 TP_IND_COMPILED_SET_ID,
473 TP_BILL_RATE,
474 TP_BILL_MARKUP_PERCENTAGE,
475 TP_SCHEDULE_LINE_PERCENTAGE,
476 TP_RULE_PERCENTAGE)
477 VALUES (
478 EiIdTab(i) -- expenditure_item_id
479 , EIdTab(i) -- expenditure_id
480 , EiDateTab(i) -- expenditure_item_date
481 , TskIdTab(i) -- task_id
482 , ETypTab(i) -- expenditure_type
483 , 'N' -- cost_distributed_flag
484 , 'N' -- revenue_distributed_flag
485 , BillFlagTab(i) -- billable_flag
486 , BillHoldTab(i) -- bill_hold_flag
487 , decode( AdjEiTab(i),
488 NULL, 'N', 'Y' ) -- net_zero_adjustment_flag
489 , NlRscTab(i) -- non_labor_resource
490 , NlRscOrgTab(i) -- organization_id
491 , QtyTab(i) -- quantity
492 , RawCostTab(i) -- raw_cost
493 , RawRateTab(i) -- raw_cost_rate
494 , OvrOrgTab(i) -- override_to_organization_id
495 , TrxRefTab(i) -- orig_transaction_reference
496 , EiTrxSrcTab(i) -- transaction_source
497 , AdjEiTab(i) -- adjusted_expenditure_item_id
498 , AttCatTab(i) -- attribute_category
499 , Att1Tab(i) -- attribute1
500 , Att2Tab(i) -- attribute2
501 , Att3Tab(i) -- attribute3
502 , Att4Tab(i) -- attribute4
503 , Att5Tab(i) -- attribute5
504 , Att6Tab(i) -- attribute6
505 , Att7Tab(i) -- attribute7
506 , Att8Tab(i) -- attribute8
507 , Att9Tab(i) -- attribute9
508 , Att10Tab(i) -- attribute10
509 , SrcEiTab(i) -- source_expenditure_item_id
513 , sysdate -- creation_date
510 , TfrEiTab(i) -- transferred_from_exp_item_id
511 , sysdate -- last_update_date
512 , X_user -- last_updated_by
514 , X_user -- created_by
515 , X_login -- last_update_login
516 , JobIdTab(i) -- job_id
517 , OrgIdTab(i) -- org_id
518 , LCMTab(i) -- labor_cost_multiplier_name
519 , 'N' -- cost burden distributed flag
520 , BCostTab(i) -- Burdened_cost
521 , BCostRateTab(i) -- Burdened_cost_rate
522 , x_request_id -- Request Id
523 , x_program_application_id -- Program Application Id
524 , x_program_id -- Program Id
525 , EtypeClassTab(i) -- System Linkage Function
526 , BurdenDestId(i) -- Burden Summarization Dest Run Id
527 , BurdenCompSetId(i) -- Burden compile set id
528 , ReceiptCurrAmt(i) -- Receipt Currency Amount
529 , ReceiptCurrCode(i) -- receipt Currency Code
530 , ReceiptExRate(i) -- Receipt Exchange Rate
531 , DenomCurrCode(i) -- Denomination Currency Code
532 , DenomRawCost(i) -- Denomination Raw Cost
533 , DenomBurdenCost(i) -- Denomination Burden Cost
534 , AcctCurrCode(i) -- Accounting Currency Code
535 , AcctRateDate(i) -- Accounting currency Rate Date
536 , AcctRateType(i) -- Accounting Currency Rate Type
537 , AcctExRate(i) -- Accounting Currency Exchange Rate
538 , AcctRawCost(i) -- Accounting Currency Raw Cost
539 , AcctBurdenCost(i) -- Accounting Currency Burden Cost
540 , AcctRoundLmt(i) -- Accounting Currency Conversion Rounding Limit
541 , ProjCurrCode(i) -- project Currency Code
542 , ProjRateDate(i) -- Prohect Currency rate date
543 , ProjRateType(i) -- project currency rate type
544 , ProjExRate(i) -- project currency exchange rate
545 , CrossChargeTypeTab(i) ,
546 CrossChargeCodeTab(i) ,
547 PrvdrOrganizationTab(i) ,
548 RecvOrganizationTab(i) ,
549 RecvOperUnitTab(i) ,
550 BorrowLentCodeTab(i) ,
551 IcProcessedCodeTab(i) ,
552 DenomTpCurrCodeTab(i) ,
553 DenomTransferPriceTab(i),
554 AcctTpRateTypeTab(i) ,
555 AcctTpRateDateTab(i) ,
556 AcctTpExchangeRateTab(i),
557 AcctTransferPriceTab(i) ,
558 ProjacctTransferPriceTab(i) ,
559 CcMarkupBaseCodeTab(i) ,
560 TpBaseAmountTab(i) ,
561 TpIndCompiledSetIdTab(i) ,
562 TpBillRateTab(i) ,
563 TpBillMarkupPercentageTab(i) ,
564 TpSchLinePercentageTab(i),
565 TpRulePercentageTab(i));
566 ELSE
567 INSERT INTO gms_encumbrance_items_all (
568 expenditure_item_id
569 , expenditure_id
570 , expenditure_item_date
571 , task_id
572 , expenditure_type
573 , cost_distributed_flag
574 , revenue_distributed_flag
575 , billable_flag
576 , bill_hold_flag
577 , net_zero_adjustment_flag
578 , non_labor_resource
579 , organization_id
580 , quantity
581 , raw_cost
582 , raw_cost_rate
583 , override_to_organization_id
584 , orig_transaction_reference
585 , transaction_source
586 , adjusted_expenditure_item_id
587 , attribute_category
588 , attribute1
589 , attribute2
590 , attribute3
591 , attribute4
592 , attribute5
593 , attribute6
594 , attribute7
595 , attribute8
596 , attribute9
597 , attribute10
598 , source_expenditure_item_id
599 , transferred_from_exp_item_id
600 , last_update_date
601 , last_updated_by
602 , creation_date
603 , created_by
604 , last_update_login
605 , job_id
606 , org_id
607 , labor_cost_multiplier_name
608 , cost_burden_distributed_flag
609 , burden_cost
610 , burden_cost_rate
611 , request_id
612 , program_application_id
613 , program_id
614 , system_linkage_function
615 , burden_sum_dest_run_id
616 , cost_ind_compiled_set_id
617 , receipt_currency_amount
618 , receipt_currency_code
619 , receipt_exchange_rate
620 , denom_currency_code
621 , denom_raw_cost
622 , denom_burdened_cost
623 , acct_currency_code
624 , acct_rate_date
625 , acct_rate_type
626 , acct_exchange_rate
627 , acct_raw_cost
628 , acct_burdened_cost
629 , acct_exchange_rounding_limit
630 , project_currency_code
631 , project_rate_date
632 , project_rate_type
633 , project_exchange_rate
634 , CC_CROSS_CHARGE_TYPE,
635 CC_CROSS_CHARGE_CODE,
636 CC_PRVDR_ORGANIZATION_ID,
637 CC_RECVR_ORGANIZATION_ID,
641 DENOM_TP_CURRENCY_CODE,
638 RECVR_ORG_ID,
639 CC_BL_DISTRIBUTED_CODE,
640 CC_IC_PROCESSED_CODE,
642 DENOM_TRANSFER_PRICE,
643 ACCT_TP_RATE_TYPE,
644 ACCT_TP_RATE_DATE,
645 ACCT_TP_EXCHANGE_RATE,
646 ACCT_TRANSFER_PRICE,
647 PROJACCT_TRANSFER_PRICE,
648 CC_MARKUP_BASE_CODE,
649 TP_BASE_AMOUNT,
650 TP_IND_COMPILED_SET_ID,
651 TP_BILL_RATE,
652 TP_BILL_MARKUP_PERCENTAGE,
653 TP_SCHEDULE_LINE_PERCENTAGE,
654 TP_RULE_PERCENTAGE)
655 VALUES (
656 EiIdTab(i) -- expenditure_item_id
657 , EIdTab(i) -- expenditure_id
658 , EiDateTab(i) -- expenditure_item_date
659 , TskIdTab(i) -- task_id
660 , ETypTab(i) -- expenditure_type
661 , 'Y' -- cost_distributed_flag
662 , 'N' -- revenue_distributed_flag
663 , BillFlagTab(i) -- billable_flag
664 , BillHoldTab(i) -- bill_hold_flag
665 , decode( AdjEiTab(i),
666 NULL, 'N', 'Y' ) -- net_zero_adjustment_flag
667 , NlRscTab(i) -- non_labor_resource
668 , NlRscOrgTab(i) -- organization_id
669 , QtyTab(i) -- quantity
670 , RawCostTab(i) -- raw_cost
671 , RawRateTab(i) -- raw_cost_rate
672 , OvrOrgTab(i) -- override_to_organization_id
673 , TrxRefTab(i) -- orig_transaction_reference
674 , EiTrxSrcTab(i) -- transaction_source
675 , AdjEiTab(i) -- adjusted_expenditure_item_id
676 , AttCatTab(i) -- attribute_category
677 , Att1Tab(i) -- attribute1
678 , Att2Tab(i) -- attribute2
679 , Att3Tab(i) -- attribute3
680 , Att4Tab(i) -- attribute4
681 , Att5Tab(i) -- attribute5
682 , Att6Tab(i) -- attribute6
683 , Att7Tab(i) -- attribute7
684 , Att8Tab(i) -- attribute8
685 , Att9Tab(i) -- attribute9
686 , Att10Tab(i) -- attribute10
687 , SrcEiTab(i) -- source_expenditure_item_id
688 , TfrEiTab(i) -- transferred_from_exp_item_id
689 , sysdate -- last_update_date
690 , X_user -- last_updated_by
691 , sysdate -- creation_date
692 , X_user -- created_by
693 , X_login -- last_update_login
694 , JobIdTab(i) -- job_id
695 , OrgIdTab(i) -- org_id
696 , LCMTab(i) -- labor_cost_multiplier_name
697 , 'N' -- cost burden distributed flag
698 , BCostTab(i) -- Burdened_cost
699 , BCostRateTab(i) -- Burdened_cost_rate
700 , x_request_id -- Request Id
701 , x_program_application_id -- Program Application Id
702 , x_program_id -- Program Id
703 , EtypeClassTab(i) -- System Linkage Function
704 , BurdenDestId(i) -- Burden Summarization Dest Run Id
705 , BurdenCompSetId(i) -- Burden compile set id
706 , ReceiptCurrAmt(i) -- Receipt Currency Amount
707 , ReceiptCurrCode(i) -- receipt Currency Code
708 , ReceiptExRate(i) -- Receipt Exchange Rate
709 , DenomCurrCode(i) -- Denomination Currency Code
710 , DenomRawCost(i) -- Denomination Raw Cost
711 , DenomBurdenCost(i) -- Denomination Burden Cost
712 , AcctCurrCode(i) -- Accounting Currency Code
713 , AcctRateDate(i) -- Accounting currency Rate Date
714 , AcctRateType(i) -- Accounting Currency Rate Type
715 , AcctExRate(i) -- Accounting Currency Exchange Rate
716 , AcctRawCost(i) -- Accounting Currency Raw Cost
717 , AcctBurdenCost(i) -- Accounting Currency Burden Cost
718 , AcctRoundLmt(i) -- Accounting Currency Conversion Rounding Limit
719 , ProjCurrCode(i) -- project Currency Code
720 , ProjRateDate(i) -- Project Currency rate date
721 , ProjRateType(i) -- project currency rate type
722 , ProjExRate(i) -- project currency exchange rate
723 , CrossChargeTypeTab(i) ,
724 CrossChargeCodeTab(i) ,
725 PrvdrOrganizationTab(i) ,
726 RecvOrganizationTab(i) ,
727 RecvOperUnitTab(i) ,
728 BorrowLentCodeTab(i) ,
729 IcProcessedCodeTab(i) ,
730 DenomTpCurrCodeTab(i) ,
731 DenomTransferPriceTab(i),
732 AcctTpRateTypeTab(i) ,
733 AcctTpRateDateTab(i) ,
734 AcctTpExchangeRateTab(i),
735 AcctTransferPriceTab(i) ,
736 ProjacctTransferPriceTab(i) ,
737 CcMarkupBaseCodeTab(i) ,
738 TpBaseAmountTab(i) ,
739 TpIndCompiledSetIdTab(i) ,
740 TpBillRateTab(i) ,
741 TpBillMarkupPercentageTab(i) ,
742 TpSchLinePercentageTab(i),
746
743 TpRulePercentageTab(i));
744
745 pa_cc_utils.log_message('After Insert');
747 Pa_Costing.CreateExternalCdl( X_expenditure_item_id => EiIdTab(i)
748 , X_ei_date => EiDateTab(i)
749 , X_amount => RawCostTab(i)
750 , X_dr_ccid => DrccidIdTab(i)
751 , X_cr_ccid => CrccidIdTab(i)
752 , X_transfer_status_code => 'V'
753 , X_quantity => QtyTab(i)
754 , X_billable_flag => BillFlagTab(i)
755 , X_request_id => x_request_id
756 , X_program_application_id => x_program_application_id
757 , x_program_id => x_program_id
758 , x_program_update_date => sysdate
759 , X_pa_date => NULL
760 , X_gl_date => GldateTab(i)
761 Trx_Import enhancement
762 , X_transferred_date => SYSDATE
763 , X_transfer_rejection_reason => NULL
764 , X_line_type => 'R'
765 , X_ind_compiled_set_id => BurdenCompSetId(i)
766 , X_burdened_cost => BCostTab(i)
767 , X_user => X_user
768 , X_project_id => ProjIdTab(i)
769 , X_task_id => TskidTab(i)
770 , X_cdlsr1 => Cdlsr1Tab(i)
771 , X_cdlsr2 => Cdlsr2Tab(i)
772 , X_cdlsr3 => Cdlsr3Tab(i)
773 , X_denom_currency_code => DenomCurrCode(i)
774 , X_denom_raw_cost => DenomRawCost(i)
775 , X_denom_burden_cost => DenomBurdenCost(i)
776 , X_acct_currency_code => AcctCurrCode(i)
777 , X_acct_rate_date => AcctRateDate(i)
778 , X_acct_rate_type => AcctRateType(i)
779 , X_acct_exchange_rate => AcctExRate(i)
780 , X_acct_raw_cost => AcctRawCost(i)
781 , X_acct_burdened_cost => AcctBurdenCost(i)
782 , X_project_currency_code => ProjCurrCode(i)
783 , X_project_rate_date => ProjRateDate(i)
784 , X_project_rate_type => ProjRateType(i)
785 , X_project_exchange_rate => ProjExRate(i)
786 , X_err_code => X_err_code
787 , X_err_stage => X_err_stage
788 , X_err_stack => X_err_stack );
789 pa_cc_utils.log_message('After Creation of CDL');
790
791 END IF ;
792 -- dbms_output.put_line( 'error code : ' || to_char( x_err_code)) ;
793 IF X_err_code <> 0 THEN
794 x_status := x_err_code ;
795 pa_cc_utils.reset_curr_function ;
796 RETURN ;
797 END IF ;
798 IF ( EiCommentTab(i) IS NOT NULL ) THEN
799
800 InsItemComment( X_ei_id => EiIdTab(i)
801 , X_ei_comment => EiCommentTab(i)
802 , X_user => X_user
803 , X_login => X_login
804 , X_status => temp_status );
805
806 null;
807 -- DLANKA
808 Pa_Adjustments.CheckStatus( status_indicator => temp_status );
809
810 END IF;
811
812 IF ( X_calling_process = 'TRANSFER' ) THEN
813
814 pa_adjustments.InsAuditRec( X_exp_item_id => TfrEiTab(i)
815 , X_adj_activity => 'TRANSFER ORIGINATING'
816 , X_module => X_module
817 , X_user => X_user
818 , X_login => X_login
819 , X_status => temp_status );
820 Pa_Adjustments.CheckStatus( status_indicator => temp_status );
821
822 pa_adjustments.InsAuditRec( X_exp_item_id => EiIdTab(i)
823 , X_adj_activity => 'TRANSFER DESTINATION'
824 , X_module => X_module
825 , X_user => X_user
826 , X_login => X_login
827 , X_status => temp_status );
828
829 Pa_Adjustments.CheckStatus( status_indicator => temp_status );
830
831
832 pa_adjustments.BackoutItem( X_exp_item_id => TfrEiTab(i)
833 , X_expenditure_id => NULL
834 , X_adj_activity => 'TRANSFER BACK-OUT'
835 , X_module => X_module
836 , X_user => X_user
837 , X_login => X_login
841
838 , X_status => temp_status );
839
840 Pa_Adjustments.CheckStatus( status_indicator => temp_status );
842
843 Project Summarization changes
844 Call procedure to create CDL for the backout item (if necessary)
845
846
847 Pa_Costing.CreateReverseCdl( X_exp_item_id => TfrEiTab(i),
848 X_backout_id => Pa_Adjustments.BackOutId,
849 X_user => X_user,
850 X_status => temp_status);
851
852 Pa_Adjustments.CheckStatus( status_indicator => temp_status );
853
854 pa_adjustments.ReverseRelatedItems( X_source_exp_item_id => TfrEiTab(i)
855 , X_expenditure_id => NULL
856 , X_module => X_module
857 , X_user => X_user
858 , X_login => X_login
859 , X_status => temp_status );
860
861 Pa_Adjustments.CheckStatus( status_indicator => temp_status );
862
863
864 ELSIF ( X_calling_process = 'TRX_IMPORT' ) THEN
865 pa_cc_utils.log_message('Trx Import call to InsItems');
866
867 IF ( AdjEiTab(i) IS NOT NULL ) THEN
868
869 pa_adjustments.InsAuditRec( X_exp_item_id => AdjEiTab(i)
870 , X_adj_activity => 'MANUAL BACK-OUT ORIGINATING'
871 , X_module => X_module
872 , X_user => X_user
873 , X_login => X_login
874 , X_status => temp_status );
875 pa_cc_utils.log_message('After call to InsAuditRec');
876
877 Pa_Adjustments.CheckStatus( status_indicator => temp_status );
878
879
880 pa_adjustments.InsAuditRec( X_exp_item_id => EiIdTab(i)
881 , X_adj_activity => 'MANUAL BACK-OUT'
882 , X_module => X_module
883 , X_user => X_user
884 , X_login => X_login
885 , X_status => temp_status );
886
887 Pa_Adjustments.CheckStatus( status_indicator => temp_status );
888
889 pa_adjustments.SetNetZero( X_exp_item_id => AdjEiTab(i)
890 , X_user => X_user
891 , X_login => X_login
892 , X_status => temp_status );
893 pa_cc_utils.log_message('After call to SetNetZero');
894
895 Pa_Adjustments.CheckStatus( status_indicator => temp_status );
896
897 pa_adjustments.ReverseRelatedItems( X_source_exp_item_id => AdjEiTab(i)
898 , X_expenditure_id => NULL
899 , X_module => X_module
900 , X_user => X_user
901 , X_login => X_login
902 , X_status => temp_status );
903 pa_cc_utils.log_message('After call to ReverseRelatedItems');
904
905 Pa_Adjustments.CheckStatus( status_indicator => temp_status );
906
907 END IF;
908 END IF;
909 END LOOP;
910
911 X_status := 0;
912 pa_cc_utils.log_message('End ');
913 pa_cc_utils.reset_curr_function ;
914
915 EXCEPTION
916 WHEN OTHERS THEN
917 X_status := SQLCODE;
918 RAISE;
919 */
920 END InsItems;
921
922
923
924 -- ========================================================================
925 -- PROCEDURE
926 -- Added Multi-Currency Transactions columns Shree 08/06
927 -- ========================================================================
928
929 PROCEDURE InsertExp( X_expenditure_id IN NUMBER
930 , X_expend_status IN VARCHAR2
931 , X_expend_ending IN DATE
932 , X_expend_class IN VARCHAR2
933 , X_inc_by_person IN NUMBER
934 , X_inc_by_org IN NUMBER
935 , X_expend_group IN VARCHAR2
936 , X_entered_by_id IN NUMBER
937 , X_created_by_id IN NUMBER
938 , X_attribute_category IN VARCHAR2 DEFAULT NULL
939 , X_attribute1 IN VARCHAR2 DEFAULT NULL
940 , X_attribute2 IN VARCHAR2 DEFAULT NULL
941 , X_attribute3 IN VARCHAR2 DEFAULT NULL
942 , X_attribute4 IN VARCHAR2 DEFAULT NULL
943 , X_attribute5 IN VARCHAR2 DEFAULT NULL
944 , X_attribute6 IN VARCHAR2 DEFAULT NULL
945 , X_attribute7 IN VARCHAR2 DEFAULT NULL
946 , X_attribute8 IN VARCHAR2 DEFAULT NULL
947 , X_attribute9 IN VARCHAR2 DEFAULT NULL
948 , X_attribute10 IN VARCHAR2 DEFAULT NULL
949 , X_description IN VARCHAR2 DEFAULT NULL
950 , X_control_total IN NUMBER DEFAULT NULL
951 , X_denom_currency_code IN VARCHAR2 DEFAULT NULL
952 , X_acct_currency_code IN VARCHAR2 DEFAULT NULL
953 , X_acct_rate_type IN VARCHAR2 DEFAULT NULL
954 , X_acct_rate_date IN DATE DEFAULT NULL
958 , X_orig_exp_txn_reference1 IN VARCHAR2 DEFAULT NULL
955 , X_acct_exchange_rate IN NUMBER DEFAULT NULL
956 -- Trx_import enhancement: Adding new parameters
957 -- These values will be inserted into PA_EXPENDITURES_ALL table
959 , X_orig_user_exp_txn_reference IN VARCHAR2 DEFAULT NULL
960 , X_vendor_id IN NUMBER DEFAULT NULL
961 , X_orig_exp_txn_reference2 IN VARCHAR2 DEFAULT NULL
962 , X_orig_exp_txn_reference3 IN VARCHAR2 DEFAULT NULL
963 )
964 IS
965 BEGIN
966 null;
967 /*
968 pa_cc_utils.set_curr_function('InsertExp');
969 pa_cc_utils.log_message('Start ');
970 INSERT INTO gms_encumbrances(
971 expenditure_id
972 , expenditure_status_code
973 , expenditure_ending_date
974 , expenditure_class_code
975 , incurred_by_person_id
976 , incurred_by_organization_id
977 , expenditure_group
978 , entered_by_person_id
979 , last_update_date
980 , last_updated_by
981 , creation_date
982 , created_by
983 , attribute_category
984 , attribute1
985 , attribute2
986 , attribute3
987 , attribute4
988 , attribute5
989 , attribute6
990 , attribute7
991 , attribute8
992 , attribute9
993 , attribute10
994 , description
995 , control_total_amount
996 , denom_currency_code
997 , acct_currency_code
998 , acct_rate_type
999 , acct_rate_date
1000 , acct_exchange_rate
1001 -- Trx_import enhancement
1002 , orig_exp_txn_reference1
1003 , orig_user_exp_txn_reference
1004 , vendor_id
1005 , orig_exp_txn_reference2
1006 , orig_exp_txn_reference3)
1007 VALUES (
1008 X_expenditure_id
1009 , X_expend_status
1010 , X_expend_ending
1011 , X_expend_class
1012 , X_inc_by_person
1013 , X_inc_by_org
1014 , X_expend_group
1015 , X_entered_by_id
1016 , sysdate
1017 , X_created_by_id
1018 , sysdate
1019 , X_created_by_id
1020 , X_attribute_category
1021 , X_attribute1
1022 , X_attribute2
1023 , X_attribute3
1024 , X_attribute4
1025 , X_attribute5
1026 , X_attribute6
1027 , X_attribute7
1028 , X_attribute8
1029 , X_attribute9
1030 , X_attribute10
1031 , X_description
1032 , X_control_total
1033 , X_denom_currency_code
1034 , X_acct_currency_code
1035 , X_acct_rate_type
1036 , X_acct_rate_date
1037 , X_acct_exchange_rate
1038 -- Trx_import enhancement
1039 , X_orig_exp_txn_reference1
1040 , X_orig_user_exp_txn_reference
1041 , X_vendor_id
1042 , X_orig_exp_txn_reference2
1043 , X_orig_exp_txn_reference3);
1044 pa_cc_utils.log_message('End ');
1045 pa_cc_utils.reset_curr_function ;
1046 */
1047 END InsertExp;
1048
1049
1050 PROCEDURE InsertExpGroup(
1051 X_expenditure_group IN VARCHAR2
1052 , X_exp_group_status_code IN VARCHAR2
1053 , X_ending_date IN DATE
1054 , X_system_linkage IN VARCHAR2
1055 , X_created_by IN NUMBER
1056 , X_transaction_source IN VARCHAR2 )
1057 IS
1058 BEGIN
1059 /*
1060 pa_cc_utils.set_curr_function('InsertExpGroup');
1061 pa_cc_utils.log_message('Start ');
1062
1063 INSERT INTO gms_encumbrance_groups(
1064 expenditure_group
1065 , expenditure_group_status_code
1066 , expenditure_ending_date
1067 , system_linkage_function
1068 , last_update_date
1069 , last_updated_by
1070 , creation_date
1071 , created_by
1072 , transaction_source )
1073 VALUES ( X_expenditure_group
1074 , X_exp_group_status_code
1075 , X_ending_date
1076 , X_system_linkage
1077 , sysdate
1078 , X_created_by
1079 , sysdate
1080 , X_created_by
1081 , X_transaction_source );
1082 pa_cc_utils.log_message('End ');
1083 pa_cc_utils.reset_curr_function ;
1084 */
1085 null;
1086
1087 END InsertExpGroup;
1088
1089
1090 PROCEDURE CreateRelatedItem(
1091 X_source_exp_item_id IN NUMBER
1092 , X_project_id IN NUMBER
1093 , X_task_id IN NUMBER
1094 , X_expenditure_type IN VARCHAR2
1095 , X_denom_raw_cost IN NUMBER
1096 , X_denom_raw_cost_rate IN NUMBER
1097 , X_override_to_org_id IN NUMBER
1098 , X_userid IN NUMBER
1099 , X_attribute_category IN VARCHAR2
1100 , X_attribute1 IN VARCHAR2
1101 , X_attribute2 IN VARCHAR2
1102 , X_attribute3 IN VARCHAR2
1103 , X_attribute4 IN VARCHAR2
1104 , X_attribute5 IN VARCHAR2
1105 , X_attribute6 IN VARCHAR2
1106 , X_attribute7 IN VARCHAR2
1107 , X_attribute8 IN VARCHAR2
1108 , X_attribute9 IN VARCHAR2
1112 , X_outcome OUT NOCOPY VARCHAR2 )
1109 , X_attribute10 IN VARCHAR2
1110 , X_comment IN VARCHAR2
1111 , X_status OUT NOCOPY NUMBER
1113 IS
1114
1115 X_expenditure_item_id NUMBER(15);
1116 X_expenditure_id NUMBER(15);
1117 X_expenditure_item_date DATE;
1118 X_inc_by_person_id NUMBER(15);
1119 X_inc_by_org_id NUMBER(15);
1120 X_orig_proj_id NUMBER(15);
1121 X_orig_task_id NUMBER(15);
1122 X_dest_proj_id NUMBER(15);
1123 X_dest_task_id NUMBER(15);
1124 X_billable_flag VARCHAR2(1);
1125 X_bill_hold_flag VARCHAR2(1);
1126 X_system_linkage VARCHAR2(30);
1127 X_etype_class VARCHAR2(3);
1128 X_job_id NUMBER(15);
1129 X_org_id NUMBER(15);
1130 temp_status NUMBER;
1131 temp_outcome VARCHAR2(30);
1132 dummy NUMBER DEFAULT NULL;
1133 l_dest_lcm VARCHAR2(20);
1134 l_orig_lcm VARCHAR2(20);
1135 X_denom_currency_code VARCHAR2(15);
1136 X_Acct_currency_code VARCHAR2(15);
1137 X_project_currency_code VARCHAR2(15) ;
1138 temp_msg_application VARCHAR2(30) :='PA';
1139 temp_msg_type VARCHAR2(1) :='E';
1140 temp_msg_token1 VARCHAR2(240) :='';
1141 temp_msg_token2 VARCHAR2(240) :='';
1142 temp_msg_token3 VARCHAR2(240) :='';
1143 temp_msg_count NUMBER ;
1144 X_project_rate_type VARCHAR2(30);
1145 X_project_rate_date DATE ;
1146 X_project_exchange_rate NUMBER ;
1147 BEGIN
1148 null;
1149 /*
1150 pa_cc_utils.set_curr_function('CreateRelatedItem');
1151 pa_cc_utils.log_message('Start ');
1152
1153 -- Need to select new MC columns here to pass to LoadEi and PATC
1154 SELECT
1155 gms_encumbrance_items_s.nextval
1156 , ei.expenditure_id
1157 , ei.expenditure_item_date
1158 , e.incurred_by_person_id
1159 , e.incurred_by_organization_id
1160 , ei.source_expenditure_item_id
1161 , ei.bill_hold_flag
1162 , t.project_id
1163 , t.task_id
1164 , ei.job_id
1165 , ei.org_id
1166 , ei.system_linkage_function
1167 , ei.denom_currency_code
1168 , ei.acct_currency_code
1169 , ei.project_currency_code
1170 , ei.project_rate_type
1171 , ei.project_rate_date
1172 , ei.project_exchange_rate
1173 , t.labor_cost_multiplier_name
1174
1175 INTO
1176 X_expenditure_item_id
1177 , X_expenditure_id
1178 , X_expenditure_item_date
1179 , X_inc_by_person_id
1180 , X_inc_by_org_id
1181 , dummy
1182 , X_bill_hold_flag
1183 , X_orig_proj_id
1184 , X_orig_task_id
1185 , X_job_id
1186 , X_org_id
1187 , X_etype_class
1188 , X_denom_currency_code
1189 , X_Acct_currency_code
1190 , X_project_currency_code
1191 , X_project_rate_type
1192 , X_project_rate_date
1193 , X_project_exchange_rate
1194 , l_orig_lcm
1195
1196 FROM
1197 gms_encumbrance_items ei
1198 , gms_encumbrances e
1199 , pa_tasks t
1200 WHERE
1201 e.expenditure_id = ei.expenditure_id
1202 AND ei.expenditure_item_id = X_source_exp_item_id
1203 AND ei.task_id = t.task_id;
1204 pa_cc_utils.log_message('In gms_transactions.CreateRelatedItem: After select statement');
1205
1206 IF ( X_project_id IS NULL
1207 OR X_task_id IS NULL ) THEN
1208
1209 X_dest_proj_id := X_orig_proj_id;
1210 X_dest_task_id := X_orig_task_id;
1211
1212 ELSE
1213
1214 X_dest_proj_id := X_project_id;
1215 X_dest_task_id := X_task_id;
1216
1217 END IF;
1218
1219 IF ( dummy IS NOT NULL ) THEN
1220
1221 X_outcome := 'PA_TR_RELATED_ITEM';
1222 X_status := 1;
1223 pa_cc_utils.reset_curr_function ;
1224 RETURN;
1225
1226 END IF;
1227
1228 IF ( NOT pa_exp_copy.CheckExpTypeActive( X_expenditure_type
1229 , X_expenditure_item_date ) ) THEN
1230
1231 X_outcome := 'EXP_TYPE_INACTIVE';
1232 X_status := 1;
1233 pa_cc_utils.reset_curr_function ;
1234 RETURN;
1235
1236 END IF;
1237
1238 SELECT
1239 count(*)
1240 INTO
1241 dummy
1242 FROM
1243 sys.dual
1244 WHERE EXISTS
1245 ( SELECT NULL
1246 FROM pa_expenditure_types
1247 WHERE expenditure_type = X_expenditure_type);
1248
1249 IF ( dummy = 0 ) THEN
1250
1251 X_outcome := 'INVALID_EXP_TYPE';
1252 X_status := 1;
1253 pa_cc_utils.reset_curr_function ;
1254 RETURN;
1255
1256 END IF;
1257
1258 IF ( X_etype_class NOT IN ('ST', 'OT' ) ) THEN
1259 X_outcome := 'INVALID_EXP_TYPE';
1260 X_status := 1;
1261 pa_cc_utils.reset_curr_function ;
1262 RETURN;
1263 END IF;
1264
1265
1266 dummy := NULL;
1267
1268 IF ( X_override_to_org_id IS NOT NULL ) THEN
1269
1270 SELECT
1271 count(*)
1272 INTO
1273 dummy
1277 ( SELECT NULL
1274 FROM
1275 sys.dual
1276 WHERE EXISTS
1278 FROM pa_organizations_v
1279 WHERE organization_id = X_override_to_org_id);
1280
1281 IF ( dummy = 0 ) THEN
1282
1283 X_outcome := 'INVALID_ORGANIZATION';
1284 X_status := 1;
1285 pa_cc_utils.reset_curr_function ;
1286 RETURN;
1287
1288 END IF;
1289
1290 END IF;
1291
1292 -- This section added for bug 791759
1293 IF ( x_task_id IS NULL ) THEN
1294 l_dest_lcm := l_orig_lcm ;
1295 ELSE
1296 l_dest_lcm := pa_utils2.GetLaborCostMultiplier(x_task_id);
1297 END IF;
1298
1299 --
1300
1301 -- Fix for Bug # 801194. This call to get_status was removed in 11.0
1302 -- for Bug # 519532.Since, Billable flag is an OUT NOCOPY parameter and was getting
1303 -- populated in get_status, it was seen that a null was getting passed. Hence
1304 -- added the call to get_status again.
1305 pa_cc_utils.log_message('Before Call to PATC');
1306
1307 pa_transactions_pub.validate_transaction(
1308 X_project_id => X_dest_proj_id
1309 , X_task_id => X_dest_task_id
1310 , X_ei_date => X_expenditure_item_date
1311 , X_expenditure_type => X_expenditure_type
1312 , X_non_labor_resource => NULL
1313 , X_person_id => X_inc_by_person_id
1314 , X_quantity => 0
1315 , X_denom_currency_code => X_denom_currency_code
1316 , X_acct_currency_code => X_Acct_currency_code
1317 , X_denom_raw_cost => X_denom_raw_cost
1318 , X_acct_raw_cost => NULL
1319 , X_acct_rate_type => NULL
1320 , X_acct_rate_date => NULL
1321 , X_acct_exchange_rate => NULL
1322 , X_transfer_ei => NULL
1323 , X_incurred_by_org_id => X_inc_by_org_id
1324 , X_nl_resource_org_id => NULL
1325 , X_transaction_source => NULL
1326 , X_calling_module => 'CreateRelatedItem'
1327 , X_vendor_id => NULL
1328 , X_entered_by_user_id => X_userid
1329 , X_attribute_category => X_attribute_category
1330 , X_attribute1 => X_attribute1
1331 , X_attribute2 => X_attribute2
1332 , X_attribute3 => X_attribute3
1333 , X_attribute4 => X_attribute4
1334 , X_attribute5 => X_attribute5
1335 , X_attribute6 => X_attribute6
1336 , X_attribute7 => X_attribute7
1337 , X_attribute8 => X_attribute8
1338 , X_attribute9 => X_attribute9
1339 , X_attribute10 => X_attribute10
1340 , X_attribute11 => ''
1341 , X_attribute12 => ''
1342 , X_attribute13 => ''
1343 , X_attribute14 => ''
1344 , X_attribute15 => ''
1345 , X_msg_application => temp_msg_application
1346 , X_msg_type => temp_msg_type
1347 , X_msg_token1 => temp_msg_token1
1348 , X_msg_token2 => temp_msg_token2
1349 , X_msg_token3 => temp_msg_token3
1350 , X_msg_count => temp_msg_count
1351 , X_msg_data => temp_outcome
1352 , X_billable_flag => X_billable_flag );
1353 pa_cc_utils.log_message('After Call to PATC');
1354
1355 IF (( temp_outcome IS NOT NULL) and
1356 ( temp_msg_type = 'E') ) THEN
1357 -- Since this is a batch program, we handle only errors,no warnings
1358
1359 X_outcome := temp_outcome;
1360 X_status := 1;
1361 pa_cc_utils.reset_curr_function ;
1362 RETURN;
1363
1364 END IF;
1365
1366 -- Passed denom_raw_cost will be stored in array using LoadEi procedure
1367 -- All other new currency attributes (introduced in Multi-Currency Transactions)
1368 -- will be set to NULL.
1369 -- Costing program will calculate appropriate values for accounting and
1370 -- project currency columns
1371
1372 -- Fix for Bug # 813758
1373 -- Need to pass denom acct and proj currency as well as project curr attributes
1374 -- since for the related item this should be the same as the parent
1375
1376
1377
1378 IC related change
1379 Send the Recvr_Org_Id to LOADEI using the new API defined
1380 in PA_UTILS2
1381
1382 pa_cc_utils.log_message('Before Call to LoadEi');
1383
1384 gms_transactions.LoadEi( X_expenditure_item_id => X_expenditure_item_id
1385 ,X_expenditure_id => X_expenditure_id
1386 ,X_expenditure_item_date => X_expenditure_item_date
1387 ,X_project_id => NULL
1388 ,X_task_id => X_dest_task_id
1389 ,X_expenditure_type => X_expenditure_type
1390 ,X_non_labor_resource => NULL
1391 ,X_nl_resource_org_id => NULL
1392 ,X_quantity => 0
1393 ,X_raw_cost => NULL
1394 ,X_raw_cost_rate => X_denom_raw_cost_rate
1398 ,X_orig_transaction_ref => NULL
1395 ,X_override_to_org_id => X_override_to_org_id
1396 ,X_billable_flag => X_billable_flag
1397 ,X_bill_hold_flag => X_bill_hold_flag
1399 ,X_transferred_from_ei => NULL
1400 ,X_adj_expend_item_id => NULL
1401 ,X_attribute_category => X_attribute_category
1402 ,X_attribute1 => X_attribute1
1403 ,X_attribute2 => X_attribute2
1404 ,X_attribute3 => X_attribute3
1405 ,X_attribute4 => X_attribute4
1406 ,X_attribute5 => X_attribute5
1407 ,X_attribute6 => X_attribute6
1408 ,X_attribute7 => X_attribute7
1409 ,X_attribute8 => X_attribute8
1410 ,X_attribute9 => X_attribute9
1411 ,X_attribute10 => X_attribute10
1412 ,X_ei_comment => X_comment
1413 ,X_transaction_source => NULL
1414 ,X_source_exp_item_id => X_source_exp_item_id
1415 ,i => 1
1416 ,X_job_id => X_job_id
1417 ,X_org_id => X_org_id
1418 ,X_labor_cost_multiplier_name => l_dest_lcm
1419 ,X_drccid => NULL
1420 ,X_crccid => NULL
1421 ,X_cdlsr1 => NULL
1422 ,X_cdlsr2 => NULL
1423 ,X_cdlsr3 => NULL
1424 ,X_gldate => NULL
1425 ,X_bcost => NULL
1426 ,X_bcostrate => NULL
1427 ,X_etypeclass => X_etype_class
1428 ,X_burden_sum_dest_run_id => NULL
1429 ,X_burden_compile_set_id => NULL
1430 ,X_receipt_currency_amount => NULL
1431 ,X_receipt_currency_code => NULL
1432 ,X_receipt_exchange_rate => NULL
1433 ,X_denom_currency_code => X_denom_currency_code,
1434 X_denom_raw_cost => X_denom_raw_cost
1435 ,X_denom_burdened_cost => NULL
1436 ,X_acct_currency_code => X_Acct_currency_code
1437 ,X_acct_rate_date => NULL
1438 ,X_acct_rate_type => NULL
1439 ,X_acct_exchange_rate => NULL
1440 ,X_acct_raw_cost => NULL
1441 ,X_acct_burdened_cost => NULL
1442 ,X_acct_exchange_rounding_limit => NULL
1443 ,X_project_currency_code => X_project_currency_code
1444 ,X_project_rate_date => X_project_rate_date
1445 ,X_project_rate_type => X_project_rate_type
1446 ,X_project_exchange_rate => X_project_exchange_rate
1447 ,X_Recv_Operating_Unit => PA_UTILS2.GetPrjOrgId(X_dest_proj_id,
1448 X_dest_task_id) ) ;
1449 pa_cc_utils.log_message('After call to LoadEi');
1450
1451 gms_transactions.InsItems( X_user => X_userid
1452 , X_login => 0
1453 , X_module => 'CreateRelatedItem'
1454 , X_calling_process => 'RELATED_ITEM'
1455 , Rows => 1
1456 , X_status => temp_status
1457 , X_gl_flag => 'N' );
1458 pa_cc_utils.log_message('After call to InsItems');
1459
1460 gms_transactions.FlushEiTabs;
1461
1462 X_status := 0;
1463 X_outcome := NULL;
1464 pa_cc_utils.log_message('End ');
1465 pa_cc_utils.reset_curr_function ;
1466
1467 EXCEPTION
1468 WHEN NO_DATA_FOUND THEN
1469 X_status := -1403;
1470 WHEN OTHERS THEN
1471 X_status := SQLCODE;
1472 RAISE;
1473 */
1474 END CreateRelatedItem;
1475
1476
1477 -- Modified the parameter name from raw_cost to denom_raw_cost as well as
1478 -- raw_cost_rate as denom_raw_cost_rate
1479 -- Note : In this phase (11.1), cost_rates will be defined in accounting
1480 -- currency (i.e. currency code of set of books in which costing
1481 -- will be done)
1482 PROCEDURE UpdateRelatedItem( X_expenditure_item_id IN NUMBER
1483 , X_denom_raw_cost IN NUMBER
1484 , X_denom_raw_cost_rate IN NUMBER
1485 , X_status OUT NOCOPY NUMBER )
1486 IS
1487 BEGIN
1488 null;
1489 /*
1490 pa_cc_utils.set_curr_function('UpdateRelatedItem');
1491 pa_cc_utils.log_message('Start ');
1492
1493 UPDATE gms_encumbrance_items ei
1494 SET
1495 ei.denom_raw_cost = pa_currency.round_trans_currency_amt(X_denom_raw_cost,
1496 ei.denom_currency_code)
1497 , ei.raw_cost_rate = X_denom_raw_cost_rate
1498 WHERE
1499 ei.expenditure_item_id = X_expenditure_item_id;
1500
1501 X_status := 0;
1502 pa_cc_utils.log_message('End ');
1506 WHEN NO_DATA_FOUND THEN
1503 pa_cc_utils.reset_curr_function ;
1504
1505 EXCEPTION
1507 X_status := -1403;
1508 WHEN OTHERS THEN
1509 X_status := SQLCODE;
1510 RAISE;
1511 */
1512
1513 END UpdateRelatedItem;
1514
1515 END GMS_TRANSACTIONS;