[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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
INSERT INTO gms_encumbrance_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
, 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
, 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_rate_date
, project_rate_type
, 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)
VALUES (
EiIdTab(i) -- expenditure_item_id
, EIdTab(i) -- expenditure_id
, EiDateTab(i) -- expenditure_item_date
, TskIdTab(i) -- task_id
, ETypTab(i) -- expenditure_type
, 'N' -- cost_distributed_flag
, '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
, 'N' -- cost burden distributed flag
, 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));
INSERT INTO gms_encumbrance_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
, 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
, 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_rate_date
, project_rate_type
, 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)
VALUES (
EiIdTab(i) -- expenditure_item_id
, EIdTab(i) -- expenditure_id
, EiDateTab(i) -- expenditure_item_date
, TskIdTab(i) -- task_id
, ETypTab(i) -- expenditure_type
, 'Y' -- cost_distributed_flag
, '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
, 'N' -- cost burden distributed flag
, 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) -- Project 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));
pa_cc_utils.log_message('After Insert');
, x_program_update_date => sysdate
, X_pa_date => NULL
, 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 );
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 DEFAULT NULL
, X_attribute1 IN VARCHAR2 DEFAULT NULL
, X_attribute2 IN VARCHAR2 DEFAULT NULL
, X_attribute3 IN VARCHAR2 DEFAULT NULL
, X_attribute4 IN VARCHAR2 DEFAULT NULL
, X_attribute5 IN VARCHAR2 DEFAULT NULL
, X_attribute6 IN VARCHAR2 DEFAULT NULL
, X_attribute7 IN VARCHAR2 DEFAULT NULL
, X_attribute8 IN VARCHAR2 DEFAULT NULL
, X_attribute9 IN VARCHAR2 DEFAULT NULL
, X_attribute10 IN VARCHAR2 DEFAULT NULL
, X_description IN VARCHAR2 DEFAULT NULL
, X_control_total IN NUMBER DEFAULT NULL
, X_denom_currency_code IN VARCHAR2 DEFAULT NULL
, X_acct_currency_code IN VARCHAR2 DEFAULT NULL
, X_acct_rate_type IN VARCHAR2 DEFAULT NULL
, X_acct_rate_date IN DATE DEFAULT NULL
, X_acct_exchange_rate IN NUMBER DEFAULT NULL
-- Trx_import enhancement: Adding new parameters
-- These values will be inserted into PA_EXPENDITURES_ALL table
, X_orig_exp_txn_reference1 IN VARCHAR2 DEFAULT NULL
, X_orig_user_exp_txn_reference IN VARCHAR2 DEFAULT NULL
, X_vendor_id IN NUMBER DEFAULT NULL
, X_orig_exp_txn_reference2 IN VARCHAR2 DEFAULT NULL
, X_orig_exp_txn_reference3 IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
null;
pa_cc_utils.set_curr_function('InsertExp');
INSERT INTO gms_encumbrances(
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)
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);
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 )
IS
BEGIN
/*
pa_cc_utils.set_curr_function('InsertExpGroup');
INSERT INTO gms_encumbrance_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 )
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 );
END InsertExpGroup;
SELECT
gms_encumbrance_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
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
FROM
gms_encumbrance_items ei
, gms_encumbrances 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 gms_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);
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 )
IS
BEGIN
null;
pa_cc_utils.set_curr_function('UpdateRelatedItem');
UPDATE gms_encumbrance_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
WHERE
ei.expenditure_item_id = X_expenditure_item_id;
END UpdateRelatedItem;