[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select Payroll_Accrual_Flag,Rbc_Element_Type_Id,Interface_Run_Id,
Rate_Source_Code,Costing_Method into
Payroll_Accrual_Flag(i),
Rbc_Element_Type_Id(i),
Interface_Run_Id(i),
Rate_Source_CodeTab(i),
Costing_Method_Tab(i) from pa_expenditure_items
where expenditure_item_id = X_adj_expend_item_id;
INSERT INTO pa_expenditure_comments (
expenditure_item_id
, line_number
, expenditure_comment
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login )
VALUES (
X_ei_id -- expenditure_item_id
, 10 -- line_number
, X_ei_comment -- expenditure_comment
, sysdate -- last_update_date
, X_user -- last_updated_by
, sysdate -- creation_date
, X_user -- created_by
, X_login ); -- last_update_login
pa_cc_utils.log_message('InsItems: ' || 'Start of bulk insert for EI insertion');
INSERT INTO pa_expenditure_items_all (
expenditure_item_id
, expenditure_id
, expenditure_item_date
, task_id
, expenditure_type
, cost_distributed_flag
, revenue_distributed_flag
, billable_flag
, bill_hold_flag
, net_zero_adjustment_flag
, non_labor_resource
, organization_id
, quantity
, raw_cost -- project functional raw cost
, raw_cost_rate
, override_to_organization_id
, orig_transaction_reference
, transaction_source
, adjusted_expenditure_item_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, source_expenditure_item_id
, transferred_from_exp_item_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, job_id
, org_id
, labor_cost_multiplier_name
, cost_burden_distributed_flag
, burden_cost -- project functional burden cost
, burden_cost_rate
, request_id
, program_application_id
, program_id
, system_linkage_function
, burden_sum_dest_run_id
, cost_ind_compiled_set_id
, receipt_currency_amount
, receipt_currency_code
, receipt_exchange_rate
, denom_currency_code
, denom_raw_cost
, denom_burdened_cost
, acct_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, acct_raw_cost
, acct_burdened_cost
, acct_exchange_rounding_limit
, project_currency_code -- project currency code
, project_rate_date -- project rate date
, project_rate_type -- project rate type
, project_exchange_rate -- project exchange rate
, CC_CROSS_CHARGE_TYPE
, CC_CROSS_CHARGE_CODE
, CC_PRVDR_ORGANIZATION_ID
, CC_RECVR_ORGANIZATION_ID
, RECVR_ORG_ID
, CC_BL_DISTRIBUTED_CODE
, CC_IC_PROCESSED_CODE
, DENOM_TP_CURRENCY_CODE
, DENOM_TRANSFER_PRICE
, ACCT_TP_RATE_TYPE
, ACCT_TP_RATE_DATE
, ACCT_TP_EXCHANGE_RATE
, ACCT_TRANSFER_PRICE
, PROJACCT_TRANSFER_PRICE
, CC_MARKUP_BASE_CODE
, TP_BASE_AMOUNT
, TP_IND_COMPILED_SET_ID
, TP_BILL_RATE
, TP_BILL_MARKUP_PERCENTAGE
, TP_SCHEDULE_LINE_PERCENTAGE
, TP_RULE_PERCENTAGE
, COST_JOB_ID
, TP_JOB_ID
, PROV_PROJ_BILL_JOB_ID
, ASSIGNMENT_ID -- assignment id
, WORK_TYPE_ID -- work type
, PROJFUNC_CURRENCY_CODE -- project functional currency
, PROJFUNC_COST_RATE_TYPE -- project functional rate
, PROJFUNC_COST_RATE_DATE -- project functional rate date
, PROJFUNC_COST_EXCHANGE_RATE -- project functional exchange rate
, PROJECT_RAW_COST
, PROJECT_BURDENED_COST
, PROJECT_ID
, TP_AMT_TYPE_CODE
, prvdr_accrual_date
, recvr_accrual_date
, Wip_Resource_Id
, Inventory_Item_Id
, Unit_Of_Measure
, Po_Line_Id
, Po_Price_Type
, Adjustment_Type
, Src_System_Linkage_Function -- 4057874
/* REL12-AP Lines uptake Start*/
, Document_header_id
, Document_distribution_ID
, Document_Line_number
, Document_Payment_ID
, Document_type
, Document_distribution_type
, Vendor_id
, historical_flag
/* REL12-AP Lines uptake END*/
-- , agreement_id --FSIO Chnages
/* 10253400: Payroll integration enhancement for 12.2..start */
, location_id
, pay_element_type_id
, rate_source_code
, costing_method
/* 10253400: Payroll integration enhancement for 12.2..end */
,Payroll_Accrual_Flag, /* Start changes for Bug#12606242 */
Rbc_Element_Type_Id,
Interface_Run_Id /* End changes for Bug#12606242 */
, cbs_element_id -- Added for CBS Enhancement Bug 16220146
)
VALUES (
EiIdTab(i) -- expenditure_item_id
, EIdTab(i) -- expenditure_id
, EiDateTab(i) -- expenditure_item_date
, TskIdTab(i) -- task_id
, ETypTab(i) -- expenditure_type
, decode(nvl(X_gl_flag,'N'), 'Y', 'Y','P','Y', 'N') -- cost_distributed_flag, changed for cost blue print project.
, 'N' -- revenue_distributed_flag
, BillFlagTab(i) -- billable_flag
, BillHoldTab(i) -- bill_hold_flag
, decode( AdjEiTab(i),
NULL, 'N', 'Y' ) -- net_zero_adjustment_flag
, NlRscTab(i) -- non_labor_resource
, NlRscOrgTab(i) -- organization_id
, QtyTab(i) -- quantity
, RawCostTab(i) -- raw_cost
, RawRateTab(i) -- raw_cost_rate
, OvrOrgTab(i) -- override_to_organization_id
, TrxRefTab(i) -- orig_transaction_reference
, EiTrxSrcTab(i) -- transaction_source
, AdjEiTab(i) -- adjusted_expenditure_item_id
, AttCatTab(i) -- attribute_category
, Att1Tab(i) -- attribute1
, Att2Tab(i) -- attribute2
, Att3Tab(i) -- attribute3
, Att4Tab(i) -- attribute4
, Att5Tab(i) -- attribute5
, Att6Tab(i) -- attribute6
, Att7Tab(i) -- attribute7
, Att8Tab(i) -- attribute8
, Att9Tab(i) -- attribute9
, Att10Tab(i) -- attribute10
, SrcEiTab(i) -- source_expenditure_item_id
, TfrEiTab(i) -- transferred_from_exp_item_id
, sysdate -- last_update_date
, X_user -- last_updated_by
, sysdate -- creation_date
, X_user -- created_by
, X_login -- last_update_login
, JobIdTab(i) -- job_id
, OrgIdTab(i) -- org_id
, LCMTab(i) -- labor_cost_multiplier_name
--, decode(EtypeClassTab(i),'VI',decode(BurdenCompSetId(i),NULL,'X','N'),'N')
--PA-K Changes
--For all system linkages, base the cost burden dist flag on the compile set id
, decode(BurdenCompSetId(i),NULL,'X','N')
-- cost burden distributed flag modified for bug #1978887
, BCostTab(i) -- Burdened_cost
, BCostRateTab(i) -- Burdened_cost_rate
, x_request_id -- Request Id
, x_program_application_id -- Program Application Id
, x_program_id -- Program Id
, EtypeClassTab(i) -- System Linkage Function
, BurdenDestId(i) -- Burden Summarization Dest Run Id
, BurdenCompSetId(i) -- Burden compile set id
, ReceiptCurrAmt(i) -- Receipt Currency Amount
, ReceiptCurrCode(i) -- receipt Currency Code
, ReceiptExRate(i) -- Receipt Exchange Rate
, DenomCurrCode(i) -- Denomination Currency Code
, DenomRawCost(i) -- Denomination Raw Cost
, DenomBurdenCost(i) -- Denomination Burden Cost
, AcctCurrCode(i) -- Accounting Currency Code
, AcctRateDate(i) -- Accounting currency Rate Date
, AcctRateType(i) -- Accounting Currency Rate Type
, AcctExRate(i) -- Accounting Currency Exchange Rate
, AcctRawCost(i) -- Accounting Currency Raw Cost
, AcctBurdenCost(i) -- Accounting Currency Burden Cost
, AcctRoundLmt(i) -- Accounting Currency Conversion Rounding Limit
, ProjCurrCode(i) -- project Currency Code
, ProjRateDate(i) -- Prohect Currency rate date
, ProjRateType(i) -- project currency rate type
, ProjExRate(i) -- project currency exchange rate
, CrossChargeTypeTab(i)
, CrossChargeCodeTab(i)
, PrvdrOrganizationTab(i)
, RecvOrganizationTab(i)
, RecvOperUnitTab(i)
, BorrowLentCodeTab(i)
, IcProcessedCodeTab(i)
, DenomTpCurrCodeTab(i)
, DenomTransferPriceTab(i)
, AcctTpRateTypeTab(i)
, AcctTpRateDateTab(i)
, AcctTpExchangeRateTab(i)
, AcctTransferPriceTab(i)
, ProjacctTransferPriceTab(i)
, CcMarkupBaseCodeTab(i)
, TpBaseAmountTab(i)
, TpIndCompiledSetIdTab(i)
, TpBillRateTab(i)
, TpBillMarkupPercentageTab(i)
, TpSchLinePercentageTab(i)
, TpRulePercentageTab(i)
, CostJobIdTab(i)
, ProvProjBillJobIdTab(i)
, TPJobIdTab(i)
, AssgnIDTab(i) -- assignment id
, WorkTypeTab(i) -- work type id
, ProjFuncCurrencyTab(i) -- project functional currency
, ProjFunccostRateTypeTab(i) -- project functional rate type
, ProjFuncCostRateDateTab(i) -- project funcational rate date
, ProjfuncCostExgRateTab(i) -- project functional exchange rate
, ProjRawCostTab(i) -- project raw cost
, ProjBurdendCostTab(i) -- project burened cost
, ProjIdTab(i)
, TpAmtTypeCode(i)
, AccrualDateTab(i)
, RecvrAccrualDateTab(i)
, Wip_Resource_IdTab(i)
, Inventory_Item_IdTab(i)
, Unit_Of_MeasureTab(i)
, Po_Line_IdTab(i)
, Po_Price_TypeTab(i)
, Adjustment_TypeTab(i)
, SrcEtypeClassTab(i) -- 4057874
/* REL12-AP Lines uptake Start*/
, DocumentHeaderidTab(i)
, DocumentDistributionIDTab(i)
, DocumentLinenumberTab(i)
-- Bug: 5443263
-- R12.PJ:XB7:QA:APL:UPG:ADJUSTMENT REVERSAL NOT GETTING ACCOUNTED IN CASH
-- Cash basis invoice distribution interfaces to projects not payments for historical invoices.
-- (DocumentPaymentIDTab(i) value -1 indicates historical invoices.
--
, DECODE(DocumentPaymentIDTab(i), -1, NULL, DocumentPaymentIDTab(i) )
, DocumentTypeTab(i)
, DocumentDistributionTypeTab(i)
, VendoridTab(i)
-- Bug: 5443263
-- R12.PJ:XB7:QA:APL:UPG:ADJUSTMENT REVERSAL NOT GETTING ACCOUNTED IN CASH
-- Cash basis invoice distribution interfaces to projects not payments for historical invoices.
-- (DocumentPaymentIDTab(i) value -1 indicates historical invoices.
--
, DECODE(DocumentPaymentIDTab(i), -1, 'Y', 'N' )
/* REL12-AP Lines uptake END*/
-- , Agreement_idTab(i) --FSIO Changes
/* 10253400: Payroll integration enhancement for 12.2..start */
, Location_IdTab(i)
, Pay_Element_IdTab(i)
, Rate_Source_CodeTab(i)
, Costing_Method_Tab(i)
/* 10253400: Payroll integration enhancement for 12.2.. end */
,Payroll_Accrual_Flag(i), /* Start changes for Bug#12606242 */
Rbc_Element_Type_Id(i),
Interface_Run_Id(i) /* end changes for Bug#12606242 */
, cbs_element_idtab(i) -- Added for CBS Enhancement Bug 16220146
); ---}
pa_cc_utils.log_message('InsItems: ' || 'End of bulk insert for EI insertion');
pa_cc_utils.log_message('InsItems: ' || 'Start of Loop for CDL insertion');
inserting into Cost_Burden_Distributed_Flag instead of inserting it with hard-coded 'N' */
--PA-K Changes: Commented the following as bulk insert for EIs is being done now
/*IF nvl(X_gl_flag,'N') <> 'Y' THEN
**
**INSERT INTO pa_expenditure_items_all (
** )
** VALUES (
** );
** INSERT INTO pa_expenditure_items_all (
** )
** VALUES (
** );
pa_cc_utils.log_message('InsItems: ' || 'After Insert');
select set_of_books_id
into l_SobId
from pa_implementations_all
where org_id = nvl(OrgIdTab(i),-99);
select set_of_books_id
into l_RecvrSobId
from pa_implementations_all
where org_id = nvl(RecvOperUnitTab(i),-99);
, x_program_update_date => sysdate
, X_pa_date => l_pa_date
, X_recvr_pa_date => l_recvr_pa_date /*CBGA*/
, X_gl_date => GldateTab(i)
/*Trx_Import enhancement*/
, X_transferred_date => SYSDATE
, X_transfer_rejection_reason => NULL
, X_line_type => 'R'
, X_ind_compiled_set_id => BurdenCompSetId(i)
, X_burdened_cost => BCostTab(i)
, X_user => X_user
, X_project_id => ProjIdTab(i)
, X_task_id => TskidTab(i)
, X_cdlsr1 => Cdlsr1Tab(i)
, X_cdlsr2 => Cdlsr2Tab(i)
, X_cdlsr3 => Cdlsr3Tab(i)
, X_denom_currency_code => DenomCurrCode(i)
, X_denom_raw_cost => DenomRawCost(i)
, X_denom_burden_cost => DenomBurdenCost(i)
, X_acct_currency_code => AcctCurrCode(i)
, X_acct_rate_date => AcctRateDate(i)
, X_acct_rate_type => AcctRateType(i)
, X_acct_exchange_rate => AcctExRate(i)
, X_acct_raw_cost => AcctRawCost(i)
, X_acct_burdened_cost => AcctBurdenCost(i)
, X_project_currency_code => ProjCurrCode(i)
, X_project_rate_date => ProjRateDate(i)
, X_project_rate_type => ProjRateType(i)
, X_project_exchange_rate => ProjExRate(i)
, X_err_code => X_err_code
, X_err_stage => X_err_stage
, X_err_stack => X_err_stack
, P_PaPeriodName => l_PaPeriodName
, P_RecvrPaPeriodName => l_RecvrPaPeriodName
, P_GlPeriodName => l_GlPeriodName
, P_RecvrGlDate => l_RecvrGlDate
, P_RecvrGlPeriodName => l_RecvrGlPeriodName
/** Added for project currency and EI attributes **/
, p_projfunc_currency_code => ProjFunccurrencyTab(i)
, p_projfunc_cost_rate_date => ProjfuncCostRateDateTab(i)
, p_projfunc_cost_rate_type => ProjfuncCostRateTypeTab(i)
, p_projfunc_cost_exchange_rate => ProjFuncCostExgRateTab(i)
, p_project_raw_cost => ProjRawCostTab(i)
, p_project_burdened_cost => ProjBurdendCostTab(i)
--, p_assignment_id => AssignIdTab(i)
, p_work_type_id => WorktypeTab(i)
-- AP Discounts
, p_cdlsr4 => Cdlsr4Tab(i)
, p_si_assets_addition_flag => SiAssetsAddFlagTab(i)
, p_cdlsr5 => Cdlsr5Tab(i)
-- , p_agreement_id => Agreement_idTab(i) --FSIO Changes
, p_cbs_element_id => cbs_element_idtab(i) -- Added for CBS Enhancement Bug 16220146
);
update pa_cost_distribution_lines cdl1
set cdl1.parent_line_num = ( select cdl2.line_num
from pa_cost_distribution_lines cdl2
where cdl2.expenditure_item_id = AdjEiTab(i)
and cdl2.line_type = 'R'
and cdl2.reversed_flag is NULL
and cdl2.line_num_reversed is NULL )
where cdl1.expenditure_item_id = EiIdTab(i)
and cdl1.line_type = 'R'
and cdl1.reversed_flag is NULL
and cdl1.line_num_reversed is NULL ;
/* Insert the Reversing expenditure Items */
FORALL i IN 1..Rows
INSERT INTO pa_expenditure_items_all(
expenditure_item_id
, task_id
, expenditure_type
, system_linkage_function
, expenditure_item_date
, expenditure_id
, override_to_organization_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, quantity
, revenue_distributed_flag
, bill_hold_flag
, billable_flag
, bill_rate_multiplier
, cost_distributed_flag
, raw_cost
, raw_cost_rate
, burden_cost
, burden_cost_rate
, cost_ind_compiled_set_id
, non_labor_resource
, organization_id
, adjusted_expenditure_item_id
, net_zero_adjustment_flag
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, transferred_from_exp_item_id
, transaction_source
, orig_transaction_reference
, source_expenditure_item_id
, job_id
, org_id
, labor_cost_multiplier_name
, receipt_currency_amount
, receipt_currency_code
, receipt_exchange_rate
, denom_currency_code
, denom_raw_cost
, denom_burdened_cost
, acct_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, acct_raw_cost
, acct_burdened_cost
, acct_exchange_rounding_limit
, project_currency_code
, project_rate_date
, project_rate_type
, project_exchange_rate
, cc_cross_charge_code
, cc_prvdr_organization_id
, cc_recvr_organization_id
, cc_rejection_code
, denom_tp_currency_code
, denom_transfer_price
, acct_tp_rate_type
, acct_tp_rate_date
, acct_tp_exchange_rate
, acct_transfer_price
, projacct_transfer_price
, cc_markup_base_code
, tp_base_amount
, cc_cross_charge_type
, recvr_org_id
, cc_bl_distributed_code
, cc_ic_processed_code
, tp_ind_compiled_set_id
, tp_bill_rate
, tp_bill_markup_percentage
, tp_schedule_line_percentage
, tp_rule_percentage
, cost_job_id
, tp_job_id
, prov_proj_bill_job_id
, assignment_id
, work_type_id
, projfunc_currency_code
, projfunc_cost_rate_date
, projfunc_cost_rate_type
, projfunc_cost_exchange_rate
, project_raw_cost
, project_burdened_cost
, project_id
, project_tp_rate_date
, project_tp_rate_type
, project_tp_exchange_rate
, project_transfer_price
, tp_amt_type_code
, cost_burden_distributed_flag
, capital_event_id
, wip_resource_id
, inventory_item_id
, unit_of_measure
, document_header_id
, document_distribution_id
, document_line_number
, document_payment_id
, vendor_id
, document_type
, document_distribution_type
, po_line_id --added po_line_id for Bug 16451280
-- , agreement_id ) --FSIO Changes
/* 10253400: Payroll integration enhancement for 12.2..start */
, location_id
, pay_element_type_id
, rate_source_code
, costing_method /* 10253400: Payroll integration enhancement for 12.2.. end */
,Payroll_Accrual_Flag /* start changes for 12581268 */
,Rbc_Element_Type_Id
,Interface_Run_Id /* end changes for 12581268 */
,cbs_element_id) -- Added for CBS Enhancement Bug 16220146
SELECT
BackoutItemID(i) -- expenditure_item_id
, ei.task_id -- task_id
, ei.expenditure_type -- expenditure_type
, ei.system_linkage_function -- system_linkage_function
, ei.expenditure_item_date -- expenditure_item_date
, ei.expenditure_id -- expenditure_id
, ei.override_to_organization_id -- override exp organization
, sysdate -- last_update_date
, X_user -- last_updated_by
, sysdate -- creation_date
, X_user -- created_by
, X_login -- last_update_login
, (0 - ei.quantity) -- quantity
, 'N' -- revenue_distributed_flag
, ei.bill_hold_flag -- bill_hold_flag
, ei.billable_flag -- billable_flag
, ei.bill_rate_multiplier -- bill_rate_multiplier
, 'N' -- cost_distributed_flag
, (0 - ei.raw_cost) -- raw_cost
, ei.raw_cost_rate -- raw_cost_rate
, (0 - ei.burden_cost) -- raw_cost
, ei.burden_cost_rate -- raw_cost_rate
, ei.cost_ind_compiled_set_id -- cost_ind_compiled_set_id
, ei.non_labor_resource -- non_labor_resource
, ei.organization_id -- organization_id
, ei.expenditure_item_id -- adjusted_expenditure_item_id
, 'Y' -- net_zero_adjustment_flag
, ei.attribute_category -- attribute_category
, ei.attribute1 -- attribute1
, ei.attribute2 -- attribute2
, ei.attribute3 -- attribute3
, ei.attribute4 -- attribute4
, ei.attribute5 -- attribute5
, ei.attribute6 -- attribute6
, ei.attribute7 -- attribute7
, ei.attribute8 -- attribute8
, ei.attribute9 -- attribute9
, ei.attribute10 -- attribute10
, ei.transferred_from_exp_item_id -- tfr from exp item id
, ei.transaction_source -- transaction_source
, decode(ei.transaction_source,'PTE TIME',NULL,
decode(ei.transaction_source,'PTE EXPENSE',NULL,
decode(ei.transaction_source,'ORACLE TIME AND LABOR',NULL,
decode(ei.transaction_source,'Oracle Self Service Time',NULL,
ei.orig_transaction_reference)))) -- orig_transaction_reference
, ei.source_expenditure_item_id -- source_expenditure_item_id
, ei.job_id -- job_id
, ei.org_id -- org_id
, ei.labor_cost_multiplier_name -- labor_cost_multiplier_name
, (0 - ei.receipt_currency_amount) -- receipt_currency_amount
, ei.receipt_currency_code -- receipt_currency_code
, ei.receipt_exchange_rate -- receipt_exchange_rate
, ei.denom_currency_code -- denom_currency_code
, (0 - ei.denom_raw_cost) -- denom_raw_cost
, (0 - ei.denom_burdened_cost) -- denom_burdened_cost
, ei.acct_currency_code -- acct_currency_code
, ei.acct_rate_date -- acct_rate_date
, ei.acct_rate_type -- acct_rate_type
, ei.acct_exchange_rate -- acct_exchange_rate
, (0 - ei.acct_raw_cost) -- acct_raw_cost
, (0 - ei.acct_burdened_cost) -- acct_burdened_cost
, ei.acct_exchange_rounding_limit -- acct_exchange_rounding_limit
, ei.project_currency_code -- project_currency_code
, ei.project_rate_date -- project_rate_date
, ei.project_rate_type -- project_rate_type
, ei.project_exchange_rate -- project_exchange_rate
, ei.cc_cross_charge_code -- cc_cross_charge_code
, ei.cc_prvdr_organization_id -- cc_prvdr_organization_id
, ei.cc_recvr_organization_id -- cc_recvr_organization_id
, ei.cc_rejection_code -- cc_rejection_code
, ei.denom_tp_currency_code -- denom_tp_currency_code
, (0 - ei.denom_transfer_price) -- denom_transfer_price
, ei.acct_tp_rate_type -- acct_tp_rate_type
, ei.acct_tp_rate_date -- acct_tp_rate_date
, ei.acct_tp_exchange_rate -- acct_tp_exchange_rate
, (0 - ei.acct_transfer_price) -- acct_transfer_price
, (0 - ei.projacct_transfer_price) -- projacct_transfer_price
, ei.cc_markup_base_code -- cc_markup_base_code
, (0 - ei.tp_base_amount) -- tp_base_amount
, ei.cc_cross_charge_type -- cc_cross_charge_type
, ei.recvr_org_id -- recvr_org_id
, decode(ei.cc_bl_distributed_code,'X','X','N') -- cc_bl_distributed_code /*Added for bug#13390012*/
, decode(ei.cc_ic_processed_code,'X','X','N') -- cc_ic_processed_code modified for bug 14079203
, ei.tp_ind_compiled_set_id -- tp_ind_compiled_set_id
, ei.tp_bill_rate -- tp_bill_rate
, ei.tp_bill_markup_percentage -- tp_bill_markup_percentage
, ei.tp_schedule_line_percentage -- tp_schedule_line_percentage
, ei.tp_rule_percentage -- tp_rule_percentage
, ei.cost_job_id -- cost_job_id
, ei.tp_job_id -- tp_job_id
, ei.prov_proj_bill_job_id -- prov_proj_bill_job_id
, ei.assignment_id
, ei.work_type_id
, ei.projfunc_currency_code
, ei.projfunc_cost_rate_date
, ei.projfunc_cost_rate_type
, ei.projfunc_cost_exchange_rate
, (0 - ei.project_raw_cost) -- project raw cost
, (0 - ei.project_burdened_cost) -- project burended cost
, ei.project_id
, ei.project_tp_rate_date
, ei.project_tp_rate_type
, ei.project_tp_exchange_rate
, (0 - ei.project_transfer_price)
, ei.tp_amt_type_code
, decode(ei.cost_ind_compiled_set_id,null,'X','N')
, capital_event_id
, wip_resource_id
, inventory_item_id
, unit_of_measure
, ei.document_header_id
, ei.document_distribution_id
, ei.document_line_number
, ei.document_payment_id
, ei.vendor_id ei_vendor_id
, ei.document_type
, ei.document_distribution_type
, ei.po_line_id -- Bug 16451280
-- , ei.agreement_id --FSIO Changes
/* 10253400: Payroll integration enhancement for 12.2..stat */
, ei.location_id
, ei.pay_element_type_id
, ei.rate_source_code
, ei.costing_method /* 10253400: Payroll integration enhancement for 12.2..end */
, ei.Payroll_Accrual_Flag /* start changes for 12581268 */
, ei.Rbc_Element_Type_Id
, ei.Interface_Run_Id /* end changes for 12581268 */
, ei.cbs_element_id -- Added for CBS Enhancement Bug 16220146
FROM
pa_expenditure_items_all ei
WHERE
ei.expenditure_item_id = TfrEiTab(i);
SELECT ec.expenditure_comment
INTO item_comment
FROM pa_expenditure_comments ec
WHERE ec.expenditure_item_id = TfrEiTab(i);
PROCEDURE InsertExp( X_expenditure_id IN NUMBER
, X_expend_status IN VARCHAR2
, X_expend_ending IN DATE
, X_expend_class IN VARCHAR2
, X_inc_by_person IN NUMBER
, X_inc_by_org IN NUMBER
, X_expend_group IN VARCHAR2
, X_entered_by_id IN NUMBER
, X_created_by_id IN NUMBER
, X_attribute_category IN VARCHAR2
, X_attribute1 IN VARCHAR2
, X_attribute2 IN VARCHAR2
, X_attribute3 IN VARCHAR2
, X_attribute4 IN VARCHAR2
, X_attribute5 IN VARCHAR2
, X_attribute6 IN VARCHAR2
, X_attribute7 IN VARCHAR2
, X_attribute8 IN VARCHAR2
, X_attribute9 IN VARCHAR2
, X_attribute10 IN VARCHAR2
, X_description IN VARCHAR2
, X_control_total IN NUMBER
, X_denom_currency_code IN VARCHAR2
, X_acct_currency_code IN VARCHAR2
, X_acct_rate_type IN VARCHAR2
, X_acct_rate_date IN DATE
, X_acct_exchange_rate IN NUMBER
-- Trx_import enhancement: Adding new parameters
-- These values will be inserted into PA_EXPENDITURES_ALL table
, X_orig_exp_txn_reference1 IN VARCHAR2
, X_orig_user_exp_txn_reference IN VARCHAR2
, X_vendor_id IN NUMBER
, X_orig_exp_txn_reference2 IN VARCHAR2
, X_orig_exp_txn_reference3 IN VARCHAR2
, X_person_type IN VARCHAR2
, P_Org_ID IN NUMBER -- 12i MOAC changes
)
IS
BEGIN
pa_cc_utils.set_curr_function('InsertExp');
pa_cc_utils.log_message('InsertExp: ' || 'Start ');
INSERT INTO pa_expenditures(
expenditure_id
, expenditure_status_code
, expenditure_ending_date
, expenditure_class_code
, incurred_by_person_id
, incurred_by_organization_id
, expenditure_group
, entered_by_person_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, description
, control_total_amount
, denom_currency_code
, acct_currency_code
, acct_rate_type
, acct_rate_date
, acct_exchange_rate
-- Trx_import enhancement
, orig_exp_txn_reference1
, orig_user_exp_txn_reference
, vendor_id
, orig_exp_txn_reference2
, orig_exp_txn_reference3
, person_type
, org_id) -- 12i MOAC changes
VALUES (
X_expenditure_id
, X_expend_status
, X_expend_ending
, X_expend_class
, X_inc_by_person
, X_inc_by_org
, X_expend_group
, X_entered_by_id
, sysdate
, X_created_by_id
, sysdate
, X_created_by_id
, X_attribute_category
, X_attribute1
, X_attribute2
, X_attribute3
, X_attribute4
, X_attribute5
, X_attribute6
, X_attribute7
, X_attribute8
, X_attribute9
, X_attribute10
, X_description
, X_control_total
, X_denom_currency_code
, X_acct_currency_code
, X_acct_rate_type
, X_acct_rate_date
, X_acct_exchange_rate
-- Trx_import enhancement
, X_orig_exp_txn_reference1
, X_orig_user_exp_txn_reference
, X_vendor_id
, X_orig_exp_txn_reference2
, X_orig_exp_txn_reference3
, X_person_type
, P_Org_Id); -- 12i MOAC changes
pa_cc_utils.log_message('InsertExp: ' || 'End ');
END InsertExp;
PROCEDURE InsertExpGroup(
X_expenditure_group IN VARCHAR2
, X_exp_group_status_code IN VARCHAR2
, X_ending_date IN DATE
, X_system_linkage IN VARCHAR2
, X_created_by IN NUMBER
, X_transaction_source IN VARCHAR2
, P_accrual_flag IN VARCHAR2
, P_Org_Id IN NUMBER) -- 12i MOAC changes
IS
BEGIN
pa_cc_utils.set_curr_function('InsertExpGroup');
pa_cc_utils.log_message('InsertExp: ' || 'Start ');
INSERT INTO pa_expenditure_groups(
expenditure_group
, expenditure_group_status_code
, expenditure_ending_date
, system_linkage_function
, last_update_date
, last_updated_by
, creation_date
, created_by
, transaction_source
, period_accrual_flag
, Org_Id) -- 12i MOAC changes
VALUES ( X_expenditure_group
, X_exp_group_status_code
, X_ending_date
, X_system_linkage
, sysdate
, X_created_by
, sysdate
, X_created_by
, X_transaction_source
, P_accrual_flag
, P_Org_Id); -- 12i MOAC changes
pa_cc_utils.log_message('InsertExp: ' || 'End ');
END InsertExpGroup;
PROCEDURE InsertExpGroupNew(
X_expenditure_group IN VARCHAR2
, X_exp_group_status_code IN VARCHAR2
, X_ending_date IN DATE
, X_system_linkage IN VARCHAR2
, X_created_by IN NUMBER
, X_transaction_source IN VARCHAR2
, P_accrual_flag IN VARCHAR2
, P_Org_Id IN NUMBER ) -- 12i MOAC changes
IS
l_Dummy NUMBER;
pa_cc_utils.set_curr_function('InsertExpGroupNew');
pa_cc_utils.log_message('InsertExp: ' || 'Start ');
pa_cc_utils.log_message('InsertExp: ' || 'Selecting if the group already exists');
select 1, expenditure_ending_date
into l_Dummy, l_Ending_Date
from pa_expenditure_groups
where expenditure_group = X_expenditure_group;
pa_cc_utils.log_message('InsertExp: ' || 'no data found when selecting if group already exists');
pa_cc_utils.log_message('InsertExp: ' || 'Exp Group does not exist, insert');
INSERT INTO pa_expenditure_groups(
expenditure_group
, expenditure_group_status_code
, expenditure_ending_date
, system_linkage_function
, last_update_date
, last_updated_by
, creation_date
, created_by
, transaction_source
, period_accrual_flag
, org_id) -- 12i MOAC changes
VALUES ( X_expenditure_group
, X_exp_group_status_code
, X_ending_date
, X_system_linkage
, sysdate
, X_created_by
, sysdate
, X_created_by
, X_transaction_source
, P_accrual_flag
, P_Org_Id); -- 12i MOAC changes
pa_cc_utils.log_message('InsertExp: ' || 'Exp Group does exist, update if needed');
pa_cc_utils.log_message('InsertExp: ' || 'Existing Exp Groups ending date is lesser, update');
update pa_expenditure_groups
set expenditure_ending_date = X_ending_date
where expenditure_group = X_expenditure_group;
pa_cc_utils.log_message('InsertExp: ' || 'Updated Count = '||SQL%ROWCOUNT);
pa_cc_utils.log_message('InsertExp: ' || 'End ');
END InsertExpGroupNew;
select work_type_id
into l_work_type_id
from pa_work_types_tl
where name=X_work_type_name AND
language = userenv('LANG');
select work_type_id
into l_work_type_id
from pa_expenditure_items
where expenditure_item_id = X_source_exp_item_id;
SELECT
pa_expenditure_items_s.nextval
, ei.expenditure_id
, ei.expenditure_item_date
, e.incurred_by_person_id
, e.incurred_by_organization_id
, ei.source_expenditure_item_id
, ei.bill_hold_flag
, t.project_id
, t.task_id
, ei.job_id
, ei.org_id
, ei.system_linkage_function
, ei.denom_currency_code
, ei.acct_currency_code
, ei.project_currency_code
, ei.project_rate_type
, ei.project_rate_date
, ei.project_exchange_rate
, t.labor_cost_multiplier_name
, ei.PROJFUNC_CURRENCY_CODE
, ei.PROJFUNC_COST_RATE_TYPE
, ei.PROJFUNC_COST_RATE_DATE
, ei.PROJFUNC_COST_EXCHANGE_RATE
, NULL -- bug 4719803 ei.PROJECT_RAW_COST
, NULL -- bug 4719803 ei.PROJECT_BURDENED_COST
, ei.ASSIGNMENT_ID
/* , ei.WORK_TYPE_ID bug2482593 */
, ei.cbs_element_id -- Added for CBS Enhancement Bug 16220146
INTO
X_expenditure_item_id
, X_expenditure_id
, X_expenditure_item_date
, X_inc_by_person_id
, X_inc_by_org_id
, dummy
, X_bill_hold_flag
, X_orig_proj_id
, X_orig_task_id
, X_job_id
, X_org_id
, X_etype_class
, X_denom_currency_code
, X_Acct_currency_code
, X_project_currency_code
, X_project_rate_type
, X_project_rate_date
, X_project_exchange_rate
, l_orig_lcm
, l_PROJFUNC_CURRENCY_CODE
, l_PROJFUNC_COST_RATE_TYPE
, l_PROJFUNC_COST_RATE_DATE
, l_PROJFUNC_COST_EXCHANGE_RATE
, l_PROJECT_RAW_COST
, l_PROJECT_BURDENED_COST
, l_ASSIGNMENT_ID
/* , l_WORK_TYPE_ID bug2482593*/
, l_cbs_element_id -- Added for CBS Enhancement Bug 16220146
FROM
pa_expenditure_items ei
, pa_expenditures e
, pa_tasks t
WHERE
e.expenditure_id = ei.expenditure_id
AND ei.expenditure_item_id = X_source_exp_item_id
AND ei.task_id = t.task_id;
pa_cc_utils.log_message('In pa_transactions.CreateRelatedItem: After select statement');
SELECT
count(*)
INTO
dummy
FROM
sys.dual
WHERE EXISTS
( SELECT NULL
FROM pa_expenditure_types
WHERE expenditure_type = X_expenditure_type);
SELECT
count(*)
INTO
dummy
FROM
sys.dual
WHERE EXISTS
( SELECT NULL
FROM pa_organizations_v
WHERE organization_id = X_override_to_org_id);
select DISTINCT award_id
into source_award_id
from gms_award_distributions adl
where adl.expenditure_item_id = X_source_exp_item_id
and adl.document_type = 'EXP'
and adl_status = 'A' ;
adl_rec.last_update_date := sysdate ;
adl_rec.last_updated_by := X_userid ;
adl_rec.last_update_login := 0 ;
PROCEDURE UpdateRelatedItem( X_expenditure_item_id IN NUMBER
, X_denom_raw_cost IN NUMBER
, X_denom_raw_cost_rate IN NUMBER
, X_status OUT NOCOPY NUMBER
, X_work_type_name IN VARCHAR2 /*bug2482593*/
)
IS
l_work_type_id NUMBER; /* bug2482593 */
pa_cc_utils.set_curr_function('UpdateRelatedItem');
pa_cc_utils.log_message('UpdateRelatedItem: ' || 'Start ');
select work_type_id
into l_work_type_id
from pa_work_types_tl
where name=X_work_type_name AND
language = userenv('LANG');
pa_cc_utils.log_message('UpdateRelatedItem: ' || 'Invalid Work Type Name');
select work_type_id
into l_work_type_id
from pa_expenditure_items
where expenditure_item_id = X_Expenditure_item_id;
UPDATE pa_expenditure_items ei
SET ei.denom_raw_cost = pa_currency.round_trans_currency_amt(X_denom_raw_cost,
ei.denom_currency_code)
,ei.RAW_COST = NULL /* Added for bug#5067217 */
,ei.ACCT_RAW_COST = NULL /* Added for bug#5067217*/
,ei.PROJECT_RAW_COST = NULL /* Added for bug#5067217 */
,ei.raw_cost_rate = X_denom_raw_cost_rate
,ei.work_type_id = l_work_type_id /* bug2482593 */
WHERE ei.expenditure_item_id = X_expenditure_item_id
AND (ei.adjusted_expenditure_item_id is NULL or ei.denom_raw_cost is NULL); /*bug 5617096*/
UPDATE pa_expenditure_items ei
SET
ei.denom_raw_cost = pa_currency.round_trans_currency_amt(X_denom_raw_cost,
ei.denom_currency_code)
, ei.raw_cost_rate = X_denom_raw_cost_rate
, ei.work_type_id = l_work_type_id ** bug2482593 **
WHERE
ei.expenditure_item_id = X_expenditure_item_id;
pa_cc_utils.log_message('UpdateRelatedItem: ' || 'End ');
END UpdateRelatedItem;
PROCEDURE UpdateSystemLinkFunc (
X_expend_item_id IN NUMBER
, X_sys_link_func IN VARCHAR2
)
IS
BEGIN
UPDATE pa_expenditure_items ei
SET ei.system_linkage_function = X_sys_link_func
WHERE ei.source_expenditure_item_id = X_expend_item_id;
END UpdateSystemLinkFunc;