[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
xc.transaction_source
, xc.batch_name
, xc.system_linkage_function
--, xc.batch_name ||xc.system_linkage_function|| to_char(P_xface_id) exp_group_name name --Bug 3035863 : commented as its not used anywhere
FROM
pa_transaction_xface_control xc
WHERE
xc.transaction_source = P_transaction_source
AND xc.batch_name = nvl(P_batch, xc.batch_name)
AND xc.status = 'PENDING';
SELECT allow_emp_org_override_flag ,
purgeable_flag , -- Added following columns for Bug 3035863
allow_duplicate_reference_flag,
gl_accounted_flag ,
allow_reversal_flag ,
costed_flag ,
allow_burden_flag
from pa_transaction_sources
where transaction_source = P_TRANSACTION_SOURCE ;
SELECT
to_char(trunc(expenditure_ending_date), 'J')||':'||
nvl(employee_number, '-DUMMY EMP-')||':'||
nvl(organization_name, '-DUMMY ORG-')||':'||
nvl(orig_exp_txn_reference1, '-DUMMY EXP_TXN_REF1-') || ':' ||
nvl(orig_user_exp_txn_reference, '-DUMMY USER_EXP_TXN_REF-') || ':' ||
nvl(vendor_number, '-DUMMY VENDOR_NUMBER-') || ':' ||
nvl(orig_exp_txn_reference2, '-DUMMY EXP_TXN_REF2-') || ':' ||
nvl(orig_exp_txn_reference3, '-DUMMY EXP_TXN_REF3-') expend
, decode(system_linkage,'OT','ST',system_linkage) || ':' ||
decode(system_linkage,'ER', nvl(denom_currency_code,'-DUMMY CODE-'),
'VI', nvl(denom_currency_code,'-DUMMY CODE-'),
'-DUMMY CODE-')||':'||
decode(system_linkage,'ER', nvl(to_char(acct_rate_date,'MMDDYYYY'),'-DUMMY DATE-'),
'VI', nvl(to_char(acct_rate_date,'MMDDYYYY'),'-DUMMY DATE-'),
'-DUMMY DATE-')||':'||
decode(system_linkage,'ER', nvl(acct_rate_type,'-DUMMY TYPE-'),
'VI', nvl(acct_rate_type,'-DUMMY TYPE-'),
'-DUMMY TYPE-')||':'||
decode(system_linkage,'ER', nvl(to_char(acct_exchange_rate),'-DUMMY RATE-'),
'VI', nvl(to_char(acct_exchange_rate),'-DUMMY RATE-'),
'-DUMMY RATE-') expend2
, system_linkage
, trunc(expenditure_ending_date) expenditure_ending_date
, employee_number
, organization_name
, trunc(expenditure_item_date) expenditure_item_date
, project_number
, task_number
, expenditure_type
, non_labor_resource
, non_labor_resource_org_name
, quantity
, raw_cost
, raw_cost_rate
, orig_transaction_reference
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, expenditure_comment
, interface_id
, expenditure_id
, nvl(unmatched_negative_txn_flag, 'N') unmatched_negative_txn_flag
, to_number( NULL ) expenditure_item_id
, to_number( NULL ) job_id
, org_id org_id
, dr_code_combination_id
, cr_code_combination_id
, cdl_system_reference1
, cdl_system_reference2
, cdl_system_reference3
, gl_date
, burdened_cost
, burdened_cost_rate
, receipt_currency_amount
, receipt_currency_code
, receipt_exchange_rate
, denom_currency_code
, denom_raw_cost
, denom_burdened_cost
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, pa_currency.round_currency_amt(acct_raw_cost) acct_raw_cost
, acct_burdened_cost
, acct_exchange_rounding_limit
, project_currency_code
, project_rate_date
, project_rate_type
, project_exchange_rate
, orig_exp_txn_reference1
, orig_user_exp_txn_reference
, vendor_number
, orig_exp_txn_reference2
, orig_exp_txn_reference3
, override_to_organization_name
, reversed_orig_txn_reference
, billable_flag
, txn_interface_id
, person_business_group_name
-- Bug 2464841 : Added parameters for 11.5 PA-J certification.
, projfunc_currency_code
, projfunc_cost_rate_type
, projfunc_cost_rate_date
, projfunc_cost_exchange_rate
, project_raw_cost
, project_burdened_cost
, assignment_name
, work_type_name
, accrual_flag
, project_id -- PA.L Changes
, task_id
, person_id
, organization_id
, non_labor_resource_org_id
, vendor_id
, override_to_organization_id
, assignment_id
, work_type_id
, person_business_group_id -- PA.L Changes end.
, po_number /* cwk */
, po_header_id
, po_line_num
, po_line_id
, person_type
, po_price_type
, wip_resource_id
, inventory_item_id
, unit_of_measure
FROM pa_transaction_interface_all
WHERE transaction_source = X_transaction_source
AND batch_name = current_batch
AND transaction_status_code = 'P'
AND decode(system_linkage,'OT','ST',system_linkage) =
curr_etype_class_code
ORDER BY
decode(system_linkage,'OT','ST',system_linkage)
, expenditure_ending_date DESC
, employee_number
, organization_name
, orig_exp_txn_reference1
, orig_user_exp_txn_reference
, vendor_number
, orig_exp_txn_reference2
, orig_exp_txn_reference3
, denom_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, expenditure_item_date
, project_number
, task_number
FOR UPDATE OF transaction_status_code;
SELECT original_encumbrance_item_id ,
DECODE(original_encumbrance_item_id,NULL,NULL,'Y') net_zero_adjustment_flag
FROM gms_transaction_interface_all
WHERE txn_interface_id = p_txn_interface_id ;
SELECT 1
FROM
pa_transaction_xface_control
WHERE
transaction_source = trx_source
AND batch_name = NVL(batch,batch_name) -- Bug 3035863 : Introduced NVL as batch can be NULL
AND system_linkage_function = NVL(etypeclasscode,system_linkage_function) -- Bug 3035863 : Introduced NVL as etypeclasscode can be NULL
AND status = 'PENDING'
FOR UPDATE OF status NOWAIT;
from control table pa_transaction_xface_control and updates them to 'IN_PROGRESS'
during processing .At the end of process updates them to 'PROCESSED' if successful
else in case of failure updates them back to 'PENDING' status.
But in Grants the records are marked and left in 'IN_PROGRESS' status. We don't
mark the records to 'PROCESSED' as the Projects code deletes 'PROCESSED' records
when purgeable_flag is set to 'Yes'.And to prevent user from updating/deleting
the Encumbrance transaction source details through 'Transaction Sources' form
i.e. PAXTRTXS.fmb we need record in control table ,hence Grants code leaves records
in control table with 'IN_PROGRESS' status.
Scenario Fixed : When transaction which is rejected is marked for re-processing
then grants code was failing with unique constraint violation on above control table.
Code issue : After processing records are left in 'In_PROGRESS' status and when the
transaction is marked for re-processing projects code checks for PENDING status
record in control table and as it fails to find one it creates a new record with
PENDING status. Grants code tries to mark even this new record to In_PROGRESS and
fails with UNIQUE constraint violation, as both records are similar.
Solution: Delete the 'IN_PROGRESS' record created during the previous unsuccessful
run before updating the current run record to same status. */
pa_cc_utils.log_message('GMS_LD_PKG.LOCKCNTRLREC : Deleting interface control record in IN_PROGRESS status which is created during last run' ,1);
DELETE pa_transaction_xface_control
WHERE transaction_source = trx_source
AND batch_name = NVL(batch,batch_name) -- Bug 3035863
AND system_linkage_function = NVL(etypeclasscode,system_linkage_function) -- Bug 3035863
AND status = 'IN_PROGRESS' ;
pa_cc_utils.log_message('GMS_LD_PKG.LOCKCNTRLREC : Number of records deleted from pa_transaction_xface_control : '||SQl%ROWCOUNT);
UPDATE pa_transaction_xface_control
SET
interface_id = P_xface_id
, status = 'IN_PROGRESS'
WHERE
transaction_source = trx_source
AND batch_name = NVL(batch,batch_name) -- Bug 3035863
AND system_linkage_function = NVL(etypeclasscode,system_linkage_function) -- Bug 3035863
AND status = 'PENDING';
pa_cc_utils.log_message('Updated interface id/status on pa_transaction_xface_control',1);
SELECT ga.award_id
FROM gms_transaction_interface_all gtxn,
gms_awards_all ga
WHERE ((gtxn.award_number IS NULL AND ga.award_id = NVL(gtxn.award_id,0) ) OR
(ga.award_number = gtxn.award_number) )
AND gtxn.txn_interface_id = Trxrec.txn_interface_id ;
select distinct encumbrance_id
from gms_encumbrances
where encumbrance_group = x_encumbrance_grp ;
select gms_encumbrance_groups_s.nextval
into l_encumbrance_grp
from dual;
gms_encumbrance_groups_pkg.insert_row (x_rowid => l_rowid,
x_encumbrance_group => l_encumbrance_grp,
x_last_update_date => sysdate,
x_last_updated_by => to_number(fnd_profile.value('USER_ID')),
x_creation_date => sysdate,
x_created_by => to_number(fnd_profile.value('USER_ID')),
x_encumbrance_group_status => 'RELEASED',
x_encumbrance_ending_date => TrxRec.expenditure_ending_date,
x_system_linkage_function => TrxRec.system_linkage,
x_control_count => NULL,
x_control_total_amount => NULL,
x_description => NULL,
x_last_update_login => to_number(fnd_profile.value('LOGIN_ID')),
x_transaction_source => P_TRANSACTION_SOURCE ,
x_org_id => l_org_id,
x_request_id => l_req_id /*bug 5689213*/
); -- bug : 2376730
select gms_encumbrance_groups_s.nextval
into l_encumbrance_grp
from dual;
gms_encumbrance_groups_pkg.insert_row (x_rowid => l_rowid,
x_encumbrance_group => l_encumbrance_grp,
x_last_update_date => sysdate,
x_last_updated_by => to_number(fnd_profile.value('USER_ID')),
x_creation_date => sysdate,
x_created_by => to_number(fnd_profile.value('USER_ID')),
x_encumbrance_group_status => 'RELEASED',
x_encumbrance_ending_date => TrxRec.expenditure_ending_date,
x_system_linkage_function => TrxRec.system_linkage,
x_control_count => NULL,
x_control_total_amount => NULL,
x_description => NULL,
x_last_update_login => to_number(fnd_profile.value('LOGIN_ID')),
x_transaction_source => P_TRANSACTION_SOURCE ,
x_org_id => l_org_id,
x_request_id => l_req_id /*bug 5689213*/
); -- bug : 2376730
SELECT net_zero_adjustment_flag,amount
FROM gms_encumbrance_items_all gei
WHERE gei.transaction_source = P_transaction_source
AND gei.encumbrance_item_id = l_orig_enc_item_id ;
select organization_id
into l_temp_org_id
from pa_organizations_expend_v
--hr_all_organization_units
WHERE organization_id = l_temp_org_id
and active_flag = 'Y'
and trunc(TrxRec.expenditure_ending_date) between date_from
and nvl(date_to,trunc(TrxRec.expenditure_ending_date));
select project_id
into l_project_id
from pa_projects_all
where segment1 = TrxRec.project_number;
select task_id
into l_task_id
from pa_tasks
where task_number = TrxRec.task_number
and project_id = l_project_id;
UPDATE pa_transaction_interface_all
SET transaction_rejection_code = X_status,
interface_id = P_xface_id,
transaction_status_code = 'R'
WHERE transaction_status_code ='P'
AND (transaction_source,batch_name,decode(system_linkage,'OT','ST',system_linkage)) IN
(SELECT xc.transaction_source,xc.batch_name,xc.system_linkage_function
FROM pa_transaction_xface_control xc
WHERE xc.transaction_source = P_transaction_source
AND xc.batch_name = nvl(P_batch, xc.batch_name)
AND xc.status = 'PENDING');
UPDATE pa_transaction_interface_all
SET transaction_rejection_code = 'INVALID_PROJECT'
, interface_id = P_xface_id
, expenditure_id = l_enc_id
, transaction_status_code = 'R'
WHERE current of TrxRecs ;
UPDATE pa_transaction_interface_all
SET transaction_rejection_code = 'INVALID_TASK'
, interface_id = P_xface_id
, expenditure_id = l_enc_id
, transaction_status_code = 'R'
WHERE current of TrxRecs ;
UPDATE pa_transaction_interface_all
SET transaction_rejection_code = 'GMS_UNEXPECTED_ERROR'
, interface_id = P_xface_id
, expenditure_id = l_enc_id
, transaction_status_code = 'R'
WHERE current of TrxRecs ;
-- Bug 3221039 : Commented the following update statement as gms_transaction_
-- interface_all.transaction_status_code column is obsolete
/*UPDATE gms_transaction_interface_all
SET transaction_status_code = 'R'
WHERE orig_transaction_reference = TrxRec.orig_transaction_reference;*/
UPDATE pa_transaction_interface_all
SET transaction_rejection_code = X_status
, interface_id = P_xface_id
, expenditure_id = l_enc_id
, transaction_status_code = 'R'
WHERE CURRENT OF TrxRecs;
-- Bug 3221039 : Commented the following update statement as gms_transaction_
-- interface_all.transaction_status_code column is obsolete
/* UPDATE gms_transaction_interface_all
SET transaction_status_code = 'R'
WHERE orig_transaction_reference = TrxRec.orig_transaction_reference; */
UPDATE pa_transaction_interface_all
SET transaction_rejection_code = DECODE(x_status_code ,NULL,'GMS_UNEXPECTED_ERROR',x_status_code) -- Bug 3465939
, interface_id = P_xface_id
, expenditure_id = l_enc_id
, transaction_status_code = 'R'
WHERE current of TrxRecs ;
-- Bug 3221039 : Commented the following update statement as gms_transaction_
-- interface_all.transaction_status_code column is obsolete
/* UPDATE gms_transaction_interface_all
SET transaction_status_code = 'R'
WHERE orig_transaction_reference = TrxRec.orig_transaction_reference; */
UPDATE pa_transaction_interface_all
SET transaction_rejection_code = 'GMS_UNEXPECTED_ERROR'
, interface_id = P_xface_id
, expenditure_id = l_enc_id
, transaction_status_code = 'R'
WHERE batch_name = P_batch ;
-- Bug 3221039 : Commented the following update statement as gms_transaction_
-- interface_all.transaction_status_code column is obsolete
/*UPDATE gms_transaction_interface_all
SET transaction_status_code = 'R'
WHERE batch_name = P_batch ; */
select gms_encumbrances_s.nextval
into l_enc_id
from dual;
gms_encumbrances_pkg.insert_row (x_rowid => l_rowid,
x_encumbrance_id => l_enc_id,
x_last_update_date => sysdate,
x_last_updated_by => to_number(fnd_profile.value('USER_ID')),
x_creation_date => sysdate,
x_created_by => to_number(fnd_profile.value('USER_ID')),
x_encumbrance_status_code => 'APPROVED',
x_encumbrance_ending_date => TrxRec.expenditure_ending_date,
x_encumbrance_class_code => TrxRec.system_linkage, /* changed to TrxRec.system_linkage from 'ST' for bug 5035700 --'ST',*/
x_incurred_by_person_id => l_person_id,
x_incurred_by_organization_id => l_organization_id,
x_encumbrance_group => l_encumbrance_grp,
x_control_total_amount => NULL,
x_entered_by_person_id => NULL,
x_description => NULL,
x_initial_submission_date => sysdate,
x_last_update_login => to_number(fnd_profile.value('LOGIN_ID')),
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_denom_currency_code => 'USD', -- Currency code hard coded
-- The following fix is for Bug: 1331903
-- x_acct_currency_code => 'USD', -- Currency code hard coded
x_acct_currency_code => x_acct_currency_code,
x_acct_rate_type => NULL,
x_acct_rate_date => NULL,
x_acct_exchange_rate => NULL,
x_orig_enc_txn_reference1 => NULL,
x_orig_enc_txn_reference2 => NULL,
x_orig_enc_txn_reference3 => NULL,
x_orig_user_enc_txn_reference => NULL,
x_vendor_id => NULL,
x_org_id => TrxRec.org_id ); -- fix for bug : 2376730
select count(*)
into x_dummy
from gms_encumbrance_items_all gei
where orig_transaction_reference = trxRec.orig_transaction_reference
and transaction_source = P_TRANSACTION_SOURCE ;
UPDATE pa_transaction_interface_all
SET transaction_rejection_code = 'DUPLICATE_ITEM'
, interface_id = P_xface_id
, expenditure_id = l_enc_id
, transaction_status_code = 'R'
WHERE CURRENT OF TrxRecs;
-- Bug 3221039 : Commented the following update statement as gms_transaction_
-- interface_all.transaction_status_code column is obsolete
/*UPDATE gms_transaction_interface_all
SET transaction_status_code = 'R'
WHERE orig_transaction_reference = TrxRec.orig_transaction_reference
AND transaction_source = P_TRANSACTION_SOURCE;*/
select gms_encumbrance_items_s.nextval
into l_enc_item_id
from dual;
/* Modified the following insert call by passing attributes instead of NULL for bug 3646187*/
gms_encumbrance_items_pkg.insert_row (x_rowid => l_rowid,
x_encumbrance_item_id => l_enc_item_id,
x_last_update_date => sysdate,
x_last_updated_by => to_number(fnd_profile.value('USER_ID')),
x_creation_date => sysdate,
x_created_by => to_number(fnd_profile.value('USER_ID')),
x_encumbrance_id => l_enc_id,
x_task_id => l_task_id,
x_encumbrance_item_date => TrxRec.expenditure_item_date,
x_encumbrance_type => TrxRec.expenditure_type,
x_enc_distributed_flag => 'N', -- default
x_amount => TrxRec.raw_cost,
x_override_to_organization_id => l_override_organization_id,
x_adjusted_encumbrance_item_id => l_orig_enc_item_id, -- Bug 3465939
x_net_zero_adjustment_flag => l_net_zero_adj_flag, -- Bug 3465939
x_transferred_from_enc_item_id => NULL,
x_last_update_login => to_number(fnd_profile.value('LOGIN_ID')),
x_request_id => NULL,
x_attribute_category => TrxRec.attribute_category,
x_attribute1 => TrxRec.attribute1,
x_attribute2 => TrxRec.attribute2,
x_attribute3 => TrxRec.attribute3,
x_attribute4 => TrxRec.attribute4,
x_attribute5 => TrxRec.attribute5,
x_attribute6 => TrxRec.attribute6,
x_attribute7 => TrxRec.attribute7,
x_attribute8 => TrxRec.attribute8,
x_attribute9 => TrxRec.attribute9,
x_attribute10 => TrxRec.attribute10,
x_orig_transaction_reference => TrxRec.orig_transaction_reference,
x_transaction_source => P_TRANSACTION_SOURCE,
x_project_id => l_project_id,
x_source_encumbrance_item_id => NULL,
x_job_id => NULL,
x_system_linkage_function => TrxRec.system_linkage,
x_denom_currency_code => TrxRec.denom_currency_code,
x_denom_raw_amount => TrxRec.acct_raw_cost,
x_acct_exchange_rounding_limit => TrxRec.acct_exchange_rounding_limit,
-- The following fix is for Bug:1331903
-- x_acct_currency_code => NULL,
x_acct_currency_code => x_acct_currency_code,
x_acct_rate_date => TrxRec.acct_rate_date,
x_acct_rate_type => TrxRec.acct_rate_type,
x_acct_exchange_rate => TrxRec.acct_exchange_rate,
x_acct_raw_cost => TrxRec.acct_raw_cost,
x_project_currency_code => TrxRec.project_currency_code,
x_project_rate_date => TrxRec.project_rate_date,
x_project_rate_type => TrxRec.project_rate_type,
x_project_exchange_rate => TrxRec.project_exchange_rate,
x_denom_tp_currency_code => NULL,
x_denom_transfer_price => NULL,
x_encumbrance_comment => TrxRec.expenditure_comment, --Bug#3755610
x_person_id => NULL,
x_incurred_by_person_id => l_person_id,
x_ind_compiled_set_id => NULL,
x_pa_date => NULL,
x_gl_date => NULL ,
x_line_num => 1,
x_burden_sum_dest_run_id => NULL,
x_burden_sum_source_run_id => NULL,
x_org_id => TrxRec.org_id ); -- fix for bug : 2376730
UPDATE pa_transaction_interface_all
SET transaction_rejection_code = NULL,
interface_id = P_xface_id,
expenditure_id = l_enc_id,
transaction_status_code = 'A',
expenditure_item_id = l_enc_item_id
WHERE CURRENT OF TrxRecs;
-- Bug 3221039 : Commented the following update statement as gms_transaction_
-- interface_all.transaction_status_code column is obsolete
/*UPDATE gms_transaction_interface_all
SET transaction_status_code = 'A'
WHERE orig_transaction_reference = TrxRec.orig_transaction_reference
AND transaction_source = P_TRANSACTION_SOURCE;*/
UPDATE gms_encumbrance_items_all
SET net_zero_adjustment_flag = 'Y'
WHERE encumbrance_item_id = l_orig_enc_item_id;
UPDATE pa_transaction_interface_all
SET transaction_rejection_code = 'GMS_CREATE_ADL_FAILED'
, interface_id = P_xface_id
, expenditure_id = l_enc_id
, transaction_status_code = 'R'
WHERE CURRENT OF TrxRecs;
-- Bug 3221039 : Commented the following update statement as gms_transaction_
-- interface_all.transaction_status_code column is obsolete
/* UPDATE gms_transaction_interface_all
SET transaction_status_code = 'R'
WHERE orig_transaction_reference = TrxRec.orig_transaction_reference
AND transaction_source = P_TRANSACTION_SOURCE; */
UPDATE pa_transaction_interface_all
SET transaction_rejection_code = 'GMS_UNEXPECTED_ERROR'
, interface_id = P_xface_id
, expenditure_id = l_enc_id
, transaction_status_code = 'R'
WHERE CURRENT OF TrxRecs;
-- Bug 3221039 : Commented the following update statement as gms_transaction_
-- interface_all.transaction_status_code column is obsolete
/* UPDATE gms_transaction_interface_all
SET transaction_status_code = 'R'
WHERE orig_transaction_reference = TrxRec.orig_transaction_reference; */
DELETE gms_transaction_interface_all
WHERE txn_interface_id IN (SELECT txn_interface_id
FROM pa_transaction_interface_all
WHERE interface_id = P_XFACE_ID
AND transaction_status_code ='A' );
pa_cc_utils.log_message('GMS_LD_PKG.PRE_PROCESS : Number of success records deleted from Grants interface table :'||SQL%ROWCOUNT);
SELECT max(start_date), min(completion_date)
FROM ( select start_date, completion_date
from pa_tasks tsk1
where task_id = l_task_id1
union all
select start_date, completion_date
from pa_projects_all
where project_id = l_project_id1
union all
select start_date_active start_date, end_date_active completion_date
from gms_awards_all
where award_id = l_award_id1
);
SELECT 1
FROM gms_encumbrance_items_all
WHERE encumbrance_item_id = l_orig_enc_item_id1;