[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE gms_encumbrance_items_all ei
SET
ei.net_zero_adjustment_flag = 'Y'
, ei.last_update_date = sysdate
, ei.last_updated_by = X_user
, ei.last_update_login = X_login
WHERE
ei.encumbrance_item_id = X_enc_item_id;
select gms_encumbrance_items_s.nextval
into X_backout_id
from SYS.dual ;
INSERT INTO gms_encumbrance_items_all(
encumbrance_item_id
, task_id
, project_id --Bug 5726575
, encumbrance_type
, system_linkage_function
, encumbrance_item_date
, encumbrance_id
, override_to_organization_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, amount
, enc_distributed_flag
, adjusted_encumbrance_item_id
, net_zero_adjustment_flag
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, transferred_from_enc_item_id
, transaction_source
, orig_transaction_reference
, source_encumbrance_item_id
, job_id
, org_id
, denom_currency_code
, denom_raw_amount
, acct_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, acct_raw_cost
, acct_exchange_rounding_limit
, project_currency_code
, project_rate_date
, project_rate_type
, project_exchange_rate
, denom_tp_currency_code
, denom_transfer_price
, encumbrance_comment ) /* Added for Bug:5879427 */
SELECT
X_backout_id -- encumbrance_item_id
, ei.task_id -- task_id
, ei.project_id -- project_id Bug 5726575
, ei.encumbrance_type -- encumbrance_type
, ei.system_linkage_function -- system_linkage_function
, ei.encumbrance_item_date -- encumbrance_item_date
, nvl( X_encumbrance_id,
ei.encumbrance_id ) -- encumbrance_id
, ei.override_to_organization_id -- override enc organization
, sysdate -- last_update_date
, X_user -- last_updated_by
, sysdate -- creation_date
, X_user -- created_by
, X_login -- last_update_login
, (0 - ei.amount) -- quantity
, 'N' -- enc_distributed_flag
, ei.encumbrance_item_id -- adjusted_encumbrance_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_enc_item_id -- tfr from enc item id
, ei.transaction_source -- transaction_source
, decode(ei.transaction_source,'PTE TIME',NULL,
decode(ei.transaction_source,'PTE EXPENSE',NULL,
ei.orig_transaction_reference)) -- orig_transaction_reference
, ei.source_encumbrance_item_id -- source_encumbrance_item_id
, ei.job_id -- job_id
, ei.org_id -- org_id
, ei.denom_currency_code -- denom_currency_code
, (0 - ei.denom_raw_amount) -- denom_raw_amount
, 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
, 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.denom_tp_currency_code -- denom_tp_currency_code
, (0 - ei.denom_transfer_price) -- denom_transfer_price
, ei.encumbrance_comment -- encumbrance_comment
FROM
gms_encumbrance_items_all ei
WHERE
ei.encumbrance_item_id = X_enc_item_id ;
SELECT gms_encumbrance_items_s.nextval encumbrance_item_id,
i.last_update_date,
i.last_updated_by,
i.creation_date,
i.created_by,
X_new_enc_id encumbrance_id ,
i.task_id,
decode( copy_mode, 'O',
--next_day((to_date(X_date)-7), --For bug 3066504
next_day((trunc(X_date)-7), --For bug 3066504
to_char(i.encumbrance_item_date, 'DAY')),
X_date ) encumbrance_item_date,
i.encumbrance_type,
i.enc_distributed_flag,
i.override_to_organization_id,
i.adjusted_encumbrance_item_id,
i.net_zero_adjustment_flag,
i.transferred_from_enc_item_id,
i.last_update_login,
i.request_id,
i.attribute_category,
i.attribute1,
i.attribute2,
i.attribute3,
i.attribute4,
i.attribute5,
i.attribute6,
i.attribute7,
i.attribute8,
i.attribute9,
i.attribute10,
i.orig_transaction_reference,
i.transaction_source,
t.project_id,
i.source_encumbrance_item_id,
i.job_id,
i.org_id,
i.system_linkage_function,
i.denom_currency_code,
i.denom_raw_amount,
i.acct_currency_code,
i.acct_rate_date,
i.acct_rate_type,
i.acct_exchange_rate,
i.acct_raw_cost,
i.acct_exchange_rounding_limit,
i.project_currency_code,
i.project_rate_date,
i.project_rate_type,
i.project_exchange_rate,
i.denom_tp_currency_code,
i.denom_transfer_price,
decode( copy_mode, 'S', NULL, i.amount ) amount,
NULL , -- Fix for Bugno : 1348099
X_person_id person_id,
i.incurred_by_person_id,
i.ind_compiled_set_id,
i.pa_date,
i.gl_date,
i.line_num,
i.burden_sum_dest_run_id,
i.burden_sum_source_run_id,
t.billable_flag
FROM
pa_tasks t
,gms_encumbrance_items i
WHERE
(X_enc_class_code = 'ER'
OR i.system_linkage_function = 'ST' )
AND i.task_id = t.task_id
AND i.encumbrance_id = X_orig_enc_id
AND i.adjusted_encumbrance_item_id IS NULL
AND nvl(i.net_zero_adjustment_flag, 'N' ) <> 'Y'
AND i.source_encumbrance_item_id IS NULL;
select award_id into P_Award_Id
from gms_award_distributions adl ,gms_encumbrance_items ei,gms_encumbrances es
where adl.expenditure_item_id = ei.encumbrance_item_id
and ei.encumbrance_id = es.encumbrance_id
and es.encumbrance_id = X_orig_enc_id
and adl.document_type = 'ENC'
and nvl(adl.reversed_flag, 'N') = 'N' --Bug 5726575
and adl.line_num_reversed IS null --Bug 5726575
and adl.adl_status = 'A';
gms_encumbrance_items_pkg.insert_row(
x_dummy,
EI.encumbrance_item_id,
EI.last_update_date,
EI.last_updated_by,
EI.creation_date,
EI.created_by,
EI.encumbrance_id ,
EI.task_id,
EI.encumbrance_item_date,
EI.encumbrance_type,
-- fix for bug : 2469854
-- EI.enc_distributed_flag,
'N' ,
EI.amount,
EI.override_to_organization_id,
EI.adjusted_encumbrance_item_id,
EI.net_zero_adjustment_flag,
EI.transferred_from_enc_item_id,
EI.last_update_login,
EI.request_id,
EI.attribute_category,
EI.attribute1,
EI.attribute2,
EI.attribute3,
EI.attribute4,
EI.attribute5,
EI.attribute6,
EI.attribute7,
EI.attribute8,
EI.attribute9,
EI.attribute10,
EI.orig_transaction_reference,
EI.transaction_source,
EI.project_id, --NULL, Bug 5726575
EI.source_encumbrance_item_id,
EI.job_id,
EI.system_linkage_function,
EI.denom_currency_code,
EI.denom_raw_amount,
EI.acct_exchange_rounding_limit,
EI.acct_currency_code,
EI.acct_rate_date,
EI.acct_rate_type,
EI.acct_exchange_rate,
EI.acct_raw_cost,
EI.project_currency_code,
EI.project_rate_date,
EI.project_rate_type,
EI.project_exchange_rate,
NULL ,
EI.org_id ,
EI.denom_tp_currency_code,
EI.denom_transfer_price,
EI.person_id,
EI.incurred_by_person_id,
EI.ind_compiled_set_id,
EI.pa_date,
EI.gl_date,
EI.line_num,
EI.burden_sum_dest_run_id,
EI.burden_sum_source_run_id );
SELECT encumbrance_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
encumbrance_status_code,
encumbrance_ending_date,
encumbrance_class_code,
-- incurred_by_person_id,
-- nvl( new_inc_by_person, incurred_by_person_id ) person_id,
incurred_by_person_id person_id,
incurred_by_organization_id,
encumbrance_group,
-- control_total_amount,
decode( copy_mode, 'S', NULL,
decode( copy_items, 'Y', control_total_amount, NULL ))
control_total_amount,
entered_by_person_id,
description,
initial_submission_date,
last_update_login,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
denom_currency_code,
acct_currency_code,
acct_rate_type,
acct_rate_date,
acct_exchange_rate,
orig_enc_txn_reference1,
orig_enc_txn_reference2,
orig_enc_txn_reference3,
orig_user_enc_txn_reference,
vendor_id,
org_id
FROM
gms_encumbrances
WHERE
encumbrance_group = orig_enc_group
AND encumbrance_id = nvl( orig_enc_id, encumbrance_id );
select gms_encumbrances_s.nextval
into new_enc_id
from dual;
gms_encumbrances_pkg.Insert_row (x_rowid => x_dummy ,
x_encumbrance_id => new_enc_id,
x_last_update_date => sysdate ,
x_last_updated_by => X_user ,
x_creation_date => sysdate ,
x_created_by => X_user ,
x_encumbrance_status_code =>'SUBMITTED',
x_encumbrance_ending_date => enc_ending_date ,
x_encumbrance_class_code => 'PT' ,
x_incurred_by_person_id => ENC.person_id ,
x_incurred_by_organization_id => ENC.incurred_by_organization_id ,
x_encumbrance_group => new_enc_group ,
x_control_total_amount => ENC.control_total_amount ,
x_entered_by_person_id => X_user ,
x_last_update_login => ENC.last_update_login,
x_attribute_category => ENC.attribute_category,
x_attribute1 => ENC.attribute1,
x_attribute2 => ENC.attribute2,
x_attribute3 => ENC.attribute3,
x_attribute4 => ENC.attribute4,
x_attribute5 => ENC.attribute5,
x_attribute6 => ENC.attribute6,
x_attribute7 => ENC.attribute7,
x_attribute8 => ENC.attribute8,
x_attribute9 => ENC.attribute9,
x_attribute10 => ENC.attribute10,
x_description => ENC.description ,
x_denom_currency_code => ENC.denom_currency_code,
x_acct_currency_code => ENC.acct_currency_code,
x_acct_rate_type => ENC.acct_rate_type,
x_acct_rate_date => ENC.acct_rate_date,
x_acct_exchange_rate => ENC.acct_exchange_rate,
x_orig_enc_txn_reference1 => ENC.orig_enc_txn_reference1,
x_orig_enc_txn_reference2 => ENC.orig_enc_txn_reference2,
x_orig_enc_txn_reference3 => ENC.orig_enc_txn_reference3,
x_orig_user_enc_txn_reference => ENC.orig_user_enc_txn_reference,
x_vendor_id => ENC.vendor_id ,
x_org_id => ENC.org_id );
InsertEnc BOOLEAN := TRUE ;
InsertBatch BOOLEAN := FALSE ;
SELECT
e.encumbrance_id orig_enc_id
, gms_encumbrances_s.nextval new_enc_id
, e.encumbrance_ending_date
, e.description
, e.incurred_by_person_id person_id
, e.incurred_by_organization_id inc_by_org_id
, e.encumbrance_class_code
, e.control_total_amount
, e.attribute_category
, e.attribute1
, e.attribute2
, e.attribute3
, e.attribute4
, e.attribute5
, e.attribute6
, e.attribute7
, e.attribute8
, e.attribute9
, e.attribute10
, e.denom_currency_code
, e.acct_currency_code
, e.acct_rate_type
, e.acct_rate_date
, e.acct_exchange_rate
, e.org_id
FROM
gms_encumbrances e
WHERE
e.encumbrance_group = X_orig_enc_group ;
select
ei.encumbrance_item_id
, ei.net_zero_adjustment_flag
, ei.source_encumbrance_item_id
, ei.transferred_from_enc_item_id
from
gms_encumbrance_items_all ei
where
encumbrance_id = encend_id ;
select
encumbrance_group
, encumbrance_ending_date
, system_linkage_function
, control_count
, control_total_amount
, request_id
, program_id
, program_application_id
, transaction_source
, org_id -- fix for bug : 2376730
from
gms_encumbrance_groups
where
encumbrance_group = X_orig_enc_group ;
SELECT 1
INTO Dummy
FROM gms_encumbrance_groups
WHERE encumbrance_group = X_new_enc_group;
InsertEnc := TRUE ;
InsertEnc := FALSE ;
If ( InsertEnc ) and (no_of_items > 0) then
IF X_encgrp_status = 'WORKING' THEN
enc_status := 'SUBMITTED';
gms_encumbrances_pkg.Insert_row(x_rowid => x_dummy ,
X_encumbrance_id => Enc.new_enc_id,
X_last_update_date => sysdate ,
X_last_updated_by => fnd_global.user_id ,
X_creation_date => sysdate ,
X_created_by => X_user_id ,
X_encumbrance_status_code => enc_status,
X_encumbrance_ending_date => Enc.encumbrance_ending_date ,
X_encumbrance_class_code => Enc.encumbrance_class_code ,
X_incurred_by_person_id => Enc.person_id ,
X_incurred_by_organization_id => Enc.inc_by_org_id ,
X_encumbrance_group => X_new_enc_group ,
X_control_total_amount => Enc.control_total_amount,
X_entered_by_person_id => X_user_id ,
X_description => Enc.description ,
X_attribute_category => Enc.attribute_category ,
X_attribute1 => Enc.attribute1 ,
X_attribute2 => Enc.attribute2 ,
X_attribute3 => Enc.attribute3 ,
X_attribute4 => Enc.attribute4 ,
X_attribute5 => Enc.attribute5 ,
X_attribute6 => Enc.attribute6 ,
X_attribute7 => Enc.attribute7 ,
X_attribute8 => Enc.attribute8 ,
X_attribute9 => Enc.attribute9 ,
X_attribute10 => Enc.attribute10 ,
X_denom_currency_code => Enc.denom_currency_code ,
X_acct_currency_code => Enc.acct_currency_code ,
X_acct_rate_type => Enc.acct_rate_type ,
X_acct_rate_date => Enc.acct_rate_date ,
X_acct_exchange_rate => Enc.acct_exchange_rate,
X_org_id => Enc.org_id );
InsertBatch := TRUE ;
if ((InsertBatch ) AND (X_module <> 'GMSTRENE')) then
OPEN ReverseGroup ;
gms_encumbrance_groups_pkg.insert_row (x_rowid => x_dummy,
x_encumbrance_group => X_new_enc_group,
x_last_update_date => sysdate,
x_last_updated_by => fnd_global.user_id,
x_creation_date => sysdate,
x_created_by => X_user_id,
x_encumbrance_group_status => X_encgrp_status,
x_encumbrance_ending_date => EncGroup.encumbrance_ending_date,
x_system_linkage_function => EncGroup.system_linkage_function,
-- x_control_count => null,
-- x_control_total_amount => null,
-- x_description => null,
-- x_last_update_login => null,
x_transaction_source => EncGroup.transaction_source ,
x_org_id => encgroup.org_id );
select o.organization_id,
o.name
from pa_employees p,
per_assignments_f a,
hr_organization_units o
where a.person_id = p.person_id
and a.effective_start_date <= p_week_end_date
and nvl(a.effective_end_date,p_week_end_date) >= p_week_end_date - 6
and a.primary_flag = 'Y'
and a.organization_id = o.organization_id
and p.person_id = p_incurred_by_person_id;
l_org_id_tab.delete;
l_org_name_tab.delete;