[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select fp.funding_sequence,
fp.funding_pattern_id
from gms_funding_patterns_all fp,
pa_tasks t
where nvl(fp.retroactive_flag, 'N') = 'N'
and NVL(fp.status, 'N') = 'A'
-- and org_id = nvl(p_org_id, org_id )
and ((fp.org_id = p_org_id) or (fp.org_id is null and p_org_id is null)) -- bug 2362489
and fp.project_id = p_project_id
and t.task_id = p_task_id
and fp.task_id = t.top_task_id
and p_exp_item_dt between fp.start_date and nvl(fp.end_date, p_exp_item_dt)
union
select funding_sequence,
funding_pattern_id
from gms_funding_patterns_all gfpa
where nvl(retroactive_flag, 'N') = 'N'
and NVL(status, 'N') = 'A'
-- and org_id = NVL(p_org_id, org_id)
and ((org_id = p_org_id) or (org_id is null and p_org_id is null)) -- bug 2362489
and project_id = p_project_id
and task_id is null
and p_exp_item_dt between start_date and nvl(end_date, p_exp_item_dt)
and not exists (select '1'
from gms_funding_patterns_all b,
pa_tasks t1
where gfpa.project_id = b.project_id
and b.status = 'A'
and t1.task_id = p_task_id
and b.task_id = t1.top_task_id)
order by 1;
select expenditure_item_date,
document_header_id,
document_distribution_id,
expenditure_type,
expenditure_organization_id,
project_id,
task_id,
gl_date,
quantity,
amount,
rowid,
burdened_cost,
denom_burdened_cost,
denom_raw_cost,
acct_raw_cost,
acct_burdened_cost,
receipt_currency_amount
from gms_distributions
where NVL(dist_status,'X') <> 'FABA'
and document_header_id = x_doc_header_id
and document_type = x_doc_type;
DELETE from gms_distribution_details A
WHERE document_type = p_document_type
and exists ( select 'X' from gms_distributions B
where A.document_header_id = b.document_distribution_id
and B.document_header_id = p_header_id
and b.document_type = p_document_type ) ;
DELETE from gms_distribution_details
WHERE document_header_id = p_header_id
AND document_type = p_document_type ;
DELETE from gms_distributions
WHERE document_header_id = p_header_id
AND document_type = p_document_type ;
INSERT INTO gms_distributions ( document_header_id,
document_distribution_id,
document_type,
gl_date,
project_id,
task_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
quantity,
unit_price,
amount,
dist_status,
creation_date
)
SELECT p_header_id,
dst.distribution_id,
p_document_type,
dst.gl_encumbered_date,
dst.project_id,
dst.task_id,
dst.expenditure_type,
dst.expenditure_organization_id,
dst.expenditure_item_date,
dst.req_line_quantity,
lne.unit_price,
-- 3362016 Grants integrations with CWK and PO services.
--dst.req_line_quantity * lne.unit_price,
decode( plt.matching_basis, 'AMOUNT', dst.req_line_amount,
dst.req_line_quantity * lne.unit_price) ,
NULL,
SYSDATE
FROM po_requisition_lines_all lne,
po_req_distributions_all dst,
po_line_types plt
-- bug 3576717
WHERE lne.requisition_header_id = p_header_id
AND lne.requisition_line_id = p_line_id
AND dst.requisition_line_id = lne.requisition_line_id
AND plt.line_type_id = lne.line_type_id
AND NVL(dst.award_id,0) = p_dist_award_id ;
INSERT INTO gms_distributions ( document_header_id,
document_distribution_id,
document_type,
gl_date,
project_id,
task_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
quantity,
unit_price,
amount,
dist_status,
creation_date
)
SELECT dst.po_header_id,
dst.po_distribution_id,
p_document_type,
dst.gl_encumbered_date,
dst.project_id,
dst.task_id,
dst.expenditure_type,
dst.expenditure_organization_id,
dst.expenditure_item_date,
dst.quantity_ordered,
lne.unit_price,
-- 3362016 Grants integrations with CWK and PO services.
-- dst.quantity_ordered * lne.unit_price,
decode( plt.matching_basis, 'AMOUNT', dst.amount_ordered,
dst.quantity_ordered * lne.unit_price),
NULL,
SYSDATE
FROM po_lines_all lne,
po_distributions_all dst,
po_line_types plt
-- bug 3576717
WHERE lne.po_header_id = p_header_id
AND lne.po_line_id = p_line_id
AND plt.line_type_id = lne.line_type_id
AND dst.po_line_id = lne.po_line_id
AND NVL(dst.award_id,0) = p_dist_award_id ;
INSERT INTO gms_distributions ( document_header_id,
document_distribution_id,
document_type,
gl_date,
project_id,
task_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
quantity,
unit_price,
amount,
dist_status,
creation_date
)
-- ==========================================
-- R12 AP Lines Uptake: Insert into gms_distributions
-- got changed from picking distribution_line_number
-- to invoice_distribution_id for document type AP.
-- ==========================================
SELECT dst.invoice_id,
dst.invoice_distribution_id,
p_document_type,
dst.accounting_date,
dst.project_id,
dst.task_id,
dst.expenditure_type,
dst.expenditure_organization_id,
dst.expenditure_item_date,
dst.pa_quantity,
1,
dst.amount,
NULL,
SYSDATE
FROM ap_invoice_distributions_all dst
WHERE dst.invoice_id = p_header_id
AND NVL(dst.award_id,0) = p_dist_award_id
union /* BUG 14216205 : Added the union for SAT */
SELECT APSAT.invoice_id,
APSAT.INVOICE_DISTRIBUTION_ID,
p_document_type,
APSAT.accounting_date,
APSAT.project_id,
APSAT.task_id,
APSAT.expenditure_type,
APSAT.expenditure_organization_id,
APSAT.expenditure_item_date,
APSAT.pa_quantity,
1,
APSAT.amount,
NULL,
SYSDATE
FROM AP_SELF_ASSESSED_TAX_DIST_ALL APSAT
where APSAT.INVOICE_ID = p_header_id
and LINE_TYPE_LOOKUP_CODE='NONREC_TAX'
AND NVL(APSAT.award_id,0) = p_dist_award_id ;
INSERT INTO gms_distributions ( document_header_id,
document_distribution_id,
document_type,
gl_date,
project_id,
task_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
quantity,
unit_price,
amount,
dist_status,
creation_date
)
SELECT hdr.encumbrance_id,
dst.encumbrance_item_id,
p_document_type,
NVL(dst.gl_date,SYSDATE),
adl.project_id,
dst.task_id,
dst.encumbrance_type,
hdr.incurred_by_organization_id,
dst.encumbrance_item_date,
0,
0,
dst.amount,
NULL,
SYSDATE
FROM gms_encumbrances_all hdr,
gms_encumbrance_items_all dst,
gms_award_distributions adl
WHERE dst.encumbrance_id = p_header_id
AND hdr.encumbrance_id = p_header_id
AND hdr.encumbrance_id = dst.encumbrance_id
AND adl.expenditure_item_id = dst.encumbrance_item_id
AND nvl(adl.reversed_flag, 'N') = 'N' --Bug 5726575
AND adl.line_num_reversed is null --Bug 5726575
and adl.adl_status = 'A'
AND NVL(adl.award_id,0) = p_dist_award_id ;
INSERT INTO gms_distributions ( document_header_id,
document_distribution_id,
document_type,
gl_date,
project_id,
task_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
quantity,
unit_price,
amount,
dist_status,
creation_date
)
SELECT hdr.expenditure_id,
dst.expenditure_item_id,
p_document_type,
-- ??????????? NVL(dst.gl_date,SYSDATE),
SYSDATE,
adl.project_id,
dst.task_id,
dst.expenditure_type,
hdr.incurred_by_organization_id,
dst.expenditure_item_date,
dst.quantity,
0,
0,
NULL,
SYSDATE
FROM pa_expenditures_all hdr,
pa_expenditure_items_all dst,
gms_award_distributions adl
WHERE dst.expenditure_id = p_header_id
AND hdr.expenditure_id = p_header_id
AND hdr.expenditure_id = dst.expenditure_id
AND adl.expenditure_item_id = dst.expenditure_item_id
and adl.adl_status = 'A'
AND NVL(adl.award_id,0) = p_dist_award_id ;
select award_id,
distribution_value
from gms_fp_distributions
where funding_pattern_id = p_funding_pattern_id
order by distribution_number ;
valid_fp_tab.delete;
select 1 into x_result_code from dual where exists (
select substr(NVL(result_code,'X'),1,1)
-- into x_result_code
from gms_bc_packets
where packet_id = X_packet_id
and substr(NVL(result_code,'X'),1,1) = 'F' );
SELECT GL_BC_PACKETS_S.nextval
INTO X_packet_id
FROM DUAL ;
select glst.period_name,
glst.period_year,
glst.period_num
into x_period_name,
x_period_year,
x_period_num
from gl_period_statuses glst
where glst.set_of_books_id = P_sob_id
and glst.application_id = 101
and glst.adjustment_period_flag = 'N'
and valid_fp_tab(1).expenditure_item_date between glst.start_date and glst.end_date;
select bv.budget_version_id
into l_budget_version_id
from gms_budget_versions bv
where bv.project_id = valid_fp_tab(tab_index).project_id
and bv.award_id = valid_fp_tab(tab_index).award_id
and bv.budget_status_code = 'B'
and bv.current_flag = 'Y';
insert into gms_bc_packets ( packet_id,
set_of_books_id,
je_source_name,
je_category_name,
actual_flag,
period_name,
period_year,
period_num,
project_id,
task_id,
award_id,
result_code,
funding_pattern_id,
funding_sequence,
fp_status,
status_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
entered_dr,
entered_cr,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
document_type,
document_header_id,
document_distribution_id,
transfered_flag,
budget_version_id,
account_type,
bc_packet_id)
values (x_packet_id,
P_sob_id,
'FAB Source Name',
'FAB Category Name',
'E',
x_period_name,
x_period_year,
x_period_num,
valid_fp_tab(tab_index).project_id,
valid_fp_tab(tab_index).task_id,
valid_fp_tab(tab_index).award_id,
NULL, --result code
null,
null,
null,
'P', --Bug Fix 2273188
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
valid_fp_tab(tab_index).award_amount,
0,
valid_fp_tab(tab_index).expenditure_type,
valid_fp_tab(tab_index).expenditure_organization_id,
trunc(valid_fp_tab(tab_index).expenditure_item_date),
'FAB',
x_doc_header_id,
x_doc_dist_id,
--tab_index,
'N',
l_budget_version_id, ---bv.budget_version_id,
'E',
gms_bc_packets_s.nextval
);
delete from gms_bc_packets
where packet_id = X_packet_id;
insert into gms_distribution_details (
document_header_id,
document_distribution_id,
document_type,
funding_pattern_id,
distribution_number,
award_id,
project_id,
task_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
gl_date,
quantity_distributed,
amount_distributed,
fc_status,
line_status,
remarks,
burdened_cost,
denom_burdened_cost,
acct_burdened_cost,
denom_raw_cost,
acct_raw_cost,
receipt_currency_amount,
creation_date)
values
(
x_doc_header_id,
x_doc_dist_id,
p_document_type,
valid_fp_tab(tab_index).funding_pattern_id,
tab_index,
valid_fp_tab(tab_index).award_id,
valid_fp_tab(tab_index).project_id,
valid_fp_tab(tab_index).task_id,
valid_fp_tab(tab_index).expenditure_type,
valid_fp_tab(tab_index).expenditure_organization_id,
valid_fp_tab(tab_index).expenditure_item_date,
valid_fp_tab(tab_index).gl_date,
valid_fp_tab(tab_index).quantity,
valid_fp_tab(tab_index).old_award_amount, /*BUG 13697251 Changed the field to old_award_amount*/
'A',
'N',
NULL,
valid_fp_tab(tab_index).burdened_cost,
valid_fp_tab(tab_index).denom_burdened_cost,
valid_fp_tab(tab_index).acct_burdened_cost,
valid_fp_tab(tab_index).denom_raw_cost,
valid_fp_tab(tab_index).acct_raw_cost,
valid_fp_tab(tab_index).receipt_currency_amount,
sysdate
);
select award_id,
distribution_value
from gms_fp_distributions
where funding_pattern_id = x_funding_pattern_id
order by distribution_number ;
select set_of_books_id
into P_sob_id
from pa_implementations;
update gms_distributions
set dist_status = 'FABA'
where rowid = recs.rowid ;
update gms_distributions
set dist_status = DECODE(l_pattern_id, -1, 'ERR01', 'ERR02' )
where rowid = recs.rowid
and document_header_id = p_doc_header_id
and document_type = p_doc_type ;
update gms_distributions
set dist_status = 'ERR03'
where rowid = recs.rowid
and document_header_id = p_doc_header_id
and document_type = p_doc_type ;
delete from gms_distributions
where creation_date <= ( TRUNC(sysdate) -1 ) ;
delete from gms_distribution_details
where creation_date <= ( TRUNC(sysdate) -1 ) ;
PROCEDURE PROC_INSERT_TRANS( P_transaction_source varchar2,
p_batch varchar2,
p_user_id NUMBER,
p_xface_id NUMBER ) IS
count_new_rec NUMBER ;
pa_cc_utils.set_curr_function('PROC_INSERT_TRANS');
UPDATE GMS_DISTRIBUTION_DETAILS
SET remarks = to_char(PA_TXN_INTERFACE_S.nextval)
WHERE document_header_id = p_xface_id
AND document_type = 'EXP'
AND distribution_number > 1 ;
-- Bug 3221039 : Modified the following insert to populate award number and not
-- to populate obsolete columns.
INSERT into GMS_TRANSACTION_INTERFACE_ALL
(
--TASK_NUMBER ,
AWARD_ID ,
AWARD_NUMBER ,
--EXPENDITURE_TYPE ,
--TRANSACTION_STATUS_CODE ,
--ORIG_TRANSACTION_REFERENCE ,
--ORG_ID ,
--SYSTEM_LINKAGE ,
--USER_TRANSACTION_SOURCE ,
TRANSACTION_TYPE ,
BURDENABLE_RAW_COST ,
FUNDING_PATTERN_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
TXN_INTERFACE_ID
--BATCH_NAME ,
--TRANSACTION_SOURCE ,
--EXPENDITURE_ENDING_DATE ,
--EXPENDITURE_ITEM_DATE,
--PROJECT_NUMBER
)
SELECT
--TXN.TASK_NUMBER ,
GTN.AWARD_ID ,
GA.AWARD_NUMBER ,
--TXN.EXPENDITURE_TYPE ,
--TXN.TRANSACTION_STATUS_CODE ,
--TXN.ORIG_TRANSACTION_REFERENCE ,
--TXN.ORG_ID ,
--TXN.SYSTEM_LINKAGE ,
--TXN.USER_TRANSACTION_SOURCE ,
NULL ,
NULL ,
GTN.funding_pattern_id ,
TXN.CREATED_BY ,
TXN.CREATION_DATE ,
TXN.LAST_UPDATED_BY ,
TXN.LAST_UPDATE_DATE ,
TO_NUMBER(GTN.REMARKS)
--TXN.BATCH_NAME ,
--TXN.TRANSACTION_SOURCE ,
--TXN.EXPENDITURE_ENDING_DATE ,
--TXN.EXPENDITURE_ITEM_DATE ,
--TXN.PROJECT_NUMBER
FROM PA_TRANSACTION_INTERFACE_ALL TXN,
GMS_DISTRIBUTION_DETAILS GTN,
GMS_AWARDS_ALL GA -- Bug 3221039
WHERE GTN.document_header_id = p_xface_id
AND GA.award_id = GTN.award_id -- Bug 3221039
AND TXN.transaction_source = P_transaction_source
AND GTN.document_type = 'EXP'
AND GTN.distribution_number > 1
AND GTN.document_distribution_id = TXN.TXN_INTERFACE_ID ;
pa_cc_utils.log_message(' GMS Transactions inserted :'||to_char(SQL%ROWCOUNT));
INSERT into PA_TRANSACTION_INTERFACE_ALL
( 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 ,
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_EXP_TXN_REFERENCE2 ,
ORIG_EXP_TXN_REFERENCE3 ,
ORIG_USER_EXP_TXN_REFERENCE ,
VENDOR_NUMBER ,
OVERRIDE_TO_ORGANIZATION_NAME ,
REVERSED_ORIG_TXN_REFERENCE ,
BILLABLE_FLAG ,
PERSON_BUSINESS_GROUP_NAME ,
TRANSACTION_SOURCE ,
BATCH_NAME ,
EXPENDITURE_ENDING_DATE ,
EMPLOYEE_NUMBER ,
ORGANIZATION_NAME ,
EXPENDITURE_ITEM_DATE ,
PROJECT_NUMBER ,
TASK_NUMBER ,
EXPENDITURE_TYPE ,
NON_LABOR_RESOURCE ,
NON_LABOR_RESOURCE_ORG_NAME ,
QUANTITY ,
RAW_COST ,
EXPENDITURE_COMMENT ,
TRANSACTION_STATUS_CODE ,
TRANSACTION_REJECTION_CODE ,
EXPENDITURE_ID ,
ORIG_TRANSACTION_REFERENCE ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
RAW_COST_RATE ,
INTERFACE_ID ,
UNMATCHED_NEGATIVE_TXN_FLAG ,
EXPENDITURE_ITEM_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 ,
SYSTEM_LINKAGE ,
TXN_INTERFACE_ID ,
USER_TRANSACTION_SOURCE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
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 ,
CDL_SYSTEM_REFERENCE4 ,
ACCRUAL_FLAG ,
PROJECT_ID ,
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 ,
INVENTORY_ITEM_ID ,
WIP_RESOURCE_ID ,
UNIT_OF_MEASURE ,
PO_NUMBER , /* CWK Changes */
PO_HEADER_ID ,
PO_LINE_NUM ,
PO_LINE_ID ,
PERSON_TYPE ,
PO_PRICE_TYPE
)
SELECT
GTN.RECEIPT_CURRENCY_AMOUNT ,
TXN.RECEIPT_CURRENCY_CODE ,
TXN.RECEIPT_EXCHANGE_RATE ,
TXN.DENOM_CURRENCY_CODE ,
GTN.DENOM_RAW_COST ,
GTN.DENOM_BURDENED_COST ,
TXN.ACCT_RATE_DATE ,
TXN.ACCT_RATE_TYPE ,
TXN.ACCT_EXCHANGE_RATE ,
GTN.ACCT_RAW_COST ,
GTN.ACCT_BURDENED_COST ,
TXN.ACCT_EXCHANGE_ROUNDING_LIMIT ,
TXN.PROJECT_CURRENCY_CODE ,
TXN.PROJECT_RATE_DATE ,
TXN.PROJECT_RATE_TYPE ,
TXN.PROJECT_EXCHANGE_RATE ,
TXN.ORIG_EXP_TXN_REFERENCE1 ,
TXN.ORIG_EXP_TXN_REFERENCE2 ,
TXN.ORIG_EXP_TXN_REFERENCE3 ,
TXN.ORIG_USER_EXP_TXN_REFERENCE ,
TXN.VENDOR_NUMBER ,
TXN.OVERRIDE_TO_ORGANIZATION_NAME ,
TXN.REVERSED_ORIG_TXN_REFERENCE ,
TXN.BILLABLE_FLAG ,
TXN.PERSON_BUSINESS_GROUP_NAME ,
TXN.TRANSACTION_SOURCE ,
TXN.BATCH_NAME ,
TXN.EXPENDITURE_ENDING_DATE ,
TXN.EMPLOYEE_NUMBER ,
TXN.ORGANIZATION_NAME ,
TXN.EXPENDITURE_ITEM_DATE ,
TXN.PROJECT_NUMBER ,
TXN.TASK_NUMBER ,
TXN.EXPENDITURE_TYPE ,
TXN.NON_LABOR_RESOURCE ,
TXN.NON_LABOR_RESOURCE_ORG_NAME ,
GTN.QUANTITY_DISTRIBUTED ,
GTN.AMOUNT_DISTRIBUTED ,
TXN.EXPENDITURE_COMMENT ,
TXN.TRANSACTION_STATUS_CODE ,
TXN.TRANSACTION_REJECTION_CODE ,
TXN.EXPENDITURE_ID ,
TXN.ORIG_TRANSACTION_REFERENCE ,
TXN.ATTRIBUTE_CATEGORY ,
TXN.ATTRIBUTE1 ,
TXN.ATTRIBUTE2 ,
TXN.ATTRIBUTE3 ,
TXN.ATTRIBUTE4 ,
TXN.ATTRIBUTE5 ,
TXN.ATTRIBUTE6 ,
TXN.ATTRIBUTE7 ,
TXN.ATTRIBUTE8 ,
TXN.ATTRIBUTE9 ,
TXN.ATTRIBUTE10 ,
TXN.RAW_COST_RATE ,
TXN.INTERFACE_ID ,
TXN.UNMATCHED_NEGATIVE_TXN_FLAG ,
TXN.EXPENDITURE_ITEM_ID ,
TXN.ORG_ID ,
TXN.DR_CODE_COMBINATION_ID ,
TXN.CR_CODE_COMBINATION_ID ,
TXN.CDL_SYSTEM_REFERENCE1 ,
TXN.CDL_SYSTEM_REFERENCE2 ,
TXN.CDL_SYSTEM_REFERENCE3 ,
TXN.GL_DATE ,
GTN.BURDENED_COST ,
TXN.BURDENED_COST_RATE ,
TXN.SYSTEM_LINKAGE ,
TO_NUMBER(GTN.REMARKS) ,
TXN.USER_TRANSACTION_SOURCE ,
TXN.CREATED_BY ,
TXN.CREATION_DATE ,
TXN.LAST_UPDATED_BY ,
TXN.LAST_UPDATE_DATE ,
TXN.PROJFUNC_CURRENCY_CODE ,
TXN.PROJFUNC_COST_RATE_TYPE ,
TXN.PROJFUNC_COST_RATE_DATE ,
TXN.PROJFUNC_COST_EXCHANGE_RATE ,
TXN.PROJECT_RAW_COST ,
TXN.PROJECT_BURDENED_COST ,
TXN.ASSIGNMENT_NAME ,
TXN.WORK_TYPE_NAME ,
TXN.CDL_SYSTEM_REFERENCE4 ,
TXN.ACCRUAL_FLAG ,
TXN.PROJECT_ID ,
TXN.TASK_ID ,
TXN.PERSON_ID ,
TXN.ORGANIZATION_ID ,
TXN.NON_LABOR_RESOURCE_ORG_ID ,
TXN.VENDOR_ID ,
TXN.OVERRIDE_TO_ORGANIZATION_ID ,
TXN.ASSIGNMENT_ID ,
TXN.WORK_TYPE_ID ,
TXN.PERSON_BUSINESS_GROUP_ID ,
TXN.INVENTORY_ITEM_ID ,
TXN.WIP_RESOURCE_ID ,
TXN.UNIT_OF_MEASURE ,
TXN.PO_NUMBER ,
TXN.PO_HEADER_ID ,
TXN.PO_LINE_NUM ,
TXN.PO_LINE_ID ,
TXN.PERSON_TYPE ,
TXN.PO_PRICE_TYPE
FROM PA_TRANSACTION_INTERFACE_ALL TXN,
GMS_DISTRIBUTION_DETAILS GTN
WHERE GTN.document_header_id = p_xface_id
AND GTN.document_type = 'EXP'
AND TXN.transaction_source = P_transaction_source
AND GTN.distribution_number > 1
AND GTN.document_distribution_id = TXN.TXN_INTERFACE_ID ;
pa_cc_utils.log_message(' PA Transactions inserted :'||to_char(SQL%ROWCOUNT));
DELETE from GMS_DISTRIBUTION_DETAILS
where document_header_id = p_xface_id ;
pa_cc_utils.log_message(' No of GMS_DISTRIBUTION_DETAILS records deleted :'||to_char(SQL%ROWCOUNT));
DELETE from GMS_DISTRIBUTIONS
where document_header_id = p_xface_id ;
pa_cc_utils.log_message(' No of GMS_DISTRIBUTIONS records deleted :'||to_char(SQL%ROWCOUNT));
END PROC_INSERT_TRANS ;
SELECT 1
INTO dummy
FROM pa_transaction_xface_control
WHERE transaction_source = trx_source
AND batch_name = batch
AND system_linkage_function = etypeclasscode
AND status = 'PENDING'
FOR UPDATE OF status NOWAIT;
pa_cc_utils.log_message('Updated interface id/status on pa_transaction_xface_control',1);
SELECT default_dist_award_id,
default_dist_award_number -- Bug 3221039
INTO p_default_dist_award_id,
p_default_dist_award_number -- Bug 3221039
FROM GMS_IMPLEMENTATIONS
WHERE AWARD_DISTRIBUTION_OPTION = 'Y' ;
select project_id
from pa_projects_all
where segment1 = l_project_number ;
select segment1
from pa_projects_all
where project_id = l_project_id ;
select task_id
from pa_tasks
where task_number = l_task_number
and project_id = l_project_id ;
select task_number
from pa_tasks
where task_id = l_task_id ;
select sponsored_flag
from pa_projects_all p,
gms_project_types gpt
where p.project_id = l_project_id
and p.project_type = gpt.project_type ;
SELECT allow_emp_org_override_flag
from pa_transaction_sources
where transaction_source = P_TRANSACTION_SOURCE ;
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
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 TXN.system_linkage
, TXN.expenditure_ending_date expenditure_ending_date
, TXN.employee_number
, decode( TXN.employee_number, NULL, TXN.organization_name,
decode(allow_emp_org_override_flag,'Y',TXN.organization_name,NULL)) organization_name
, TXN.expenditure_item_date expenditure_item_date
, TXN.project_number
, TXN.project_id
, TXN.task_id
, TXN.task_number
, TXN.expenditure_type
, TXN.non_labor_resource
, TXN.non_labor_resource_org_name
, TXN.quantity
, TXN.raw_cost
, TXN.raw_cost_rate
, TXN.orig_transaction_reference
, TXN.attribute_category
, TXN.attribute1
, TXN.attribute2
, TXN.attribute3
, TXN.attribute4
, TXN.attribute5
, TXN.attribute6
, TXN.attribute7
, TXN.attribute8
, TXN.attribute9
, TXN.attribute10
, TXN.expenditure_comment
, TXN.interface_id
, TXN.expenditure_id
, TXN.unmatched_negative_txn_flag unmatched_negative_txn_flag
, to_number( NULL ) expenditure_item_id
, to_number( NULL ) job_id
, TXN.org_id org_id
, TXN.dr_code_combination_id
, TXN.cr_code_combination_id
, TXN.cdl_system_reference1
, TXN.cdl_system_reference2
, TXN.cdl_system_reference3
, TXN.gl_date
, TXN.burdened_cost
, TXN.burdened_cost_rate
, TXN.receipt_currency_amount
, TXN.receipt_currency_code
, TXN.receipt_exchange_rate
, TXN.denom_currency_code
, TXN.denom_raw_cost
, TXN.denom_burdened_cost
, TXN.acct_rate_date
, TXN.acct_rate_type
, TXN.acct_exchange_rate
, TXN.acct_raw_cost
, TXN.acct_burdened_cost
, TXN.acct_exchange_rounding_limit
, TXN.project_currency_code
, TXN.project_rate_date
, TXN.project_rate_type
, TXN.project_exchange_rate
, TXN.orig_exp_txn_reference1
, TXN.orig_user_exp_txn_reference
, TXN.vendor_number
, TXN.orig_exp_txn_reference2
, TXN.orig_exp_txn_reference3
, TXN.override_to_organization_name
, TXN.reversed_orig_txn_reference
, TXN.billable_flag
, TXN.txn_interface_id
, TXN.person_business_group_name
-- Bug 2464841 : Added parameters for 11.5 PA-J certification.
, TXN.projfunc_currency_code
, TXN.projfunc_cost_rate_type
, TXN.projfunc_cost_rate_date
, TXN.projfunc_cost_exchange_rate
, TXN.project_raw_cost
, TXN.project_burdened_cost
, TXN.assignment_name
, TXN.work_type_name
, TXN.accrual_flag
, TXN.person_id -- PA.L Changes
, TXN.organization_id
, TXN.non_labor_resource_org_id
, TXN.vendor_id
, TXN.override_to_organization_id
, TXN.assignment_id
, TXN.work_type_id
, TXN.person_business_group_id -- PA.L Changes end.
, TXN.po_number /* cwk */
, TXN.po_header_id
, TXN.po_line_num
, TXN.po_line_id
, TXN.person_type
, TXN.po_price_type
, TXN.wip_resource_id
, TXN.inventory_item_id
, TXN.unit_of_measure
FROM pa_transaction_interface TXN,
pa_transaction_sources TS,
GMS_transaction_interface_all GMS1
WHERE TXN.transaction_source = X_transaction_source
and ts.transaction_source = TXN.transaction_source
AND TXN.batch_name = current_batch
AND TXN.transaction_status_code = 'P'
and gms1.TXN_INTERFACE_ID = TXN.TXN_INTERFACE_ID
and ( (gms1.award_number IS NULL AND NVL(gms1.award_id,0) = x_default_dist_award_id)
OR
(gms1.award_number = l_default_dist_award_number))
-- Bug 3221039 : To fetch based on Award Number and Award Id
AND decode(TXN.system_linkage,'OT','ST',txn.system_linkage) = curr_etype_class_code
FOR UPDATE OF TXN.transaction_status_code;
UPDATE pa_transaction_interface
SET transaction_rejection_code = 'LOCK_'||eachGroup.batch_name||'_FAILED'
, transaction_status_code = 'PR'
WHERE transaction_source = eachGroup.transaction_source
AND batch_name = eachGroup.batch_name
AND transaction_status_code = 'P';
v_doc_header_id.DELETE ;
v_doc_dist_id.DELETE ;
v_gl_date.DELETE ;
V_project_id.DELETE ;
V_task_id.DELETE ;
V_exp_org_id.DELETE ;
V_quantity.DELETE ;
V_unit_price.DELETE ;
V_amount.DELETE ;
V_exp_type.DELETE ;
V_dist_status.DELETE ;
V_exp_item_date.DELETE ;
V_creation_date.DELETE ;
V_burdened_cost.DELETE ;
v_denom_raw_cost.DELETE ;
v_denom_burdened_cost.DELETE ;
v_acct_raw_cost.DELETE ;
v_acct_burdened_cost.DELETE ;
v_receipt_currency_amount.DELETE ;
-- Org id is null. Update status.
X_status := X_org_status;
UPDATE pa_transaction_interface
SET transaction_rejection_code = X_status ,
interface_id = P_xface_id ,
transaction_status_code = 'PR'
WHERE CURRENT OF TrxRecs;
-- = TrxRec.raw_cost is raising a ORA exception when inserting into
-- = gms_distributions table.
-- = Error is fixed by using NVL(TrxRec.raw_cost,0)
-- = =================================================================
V_amount(count_rec) := NVL(TrxRec.raw_cost ,0);
-- Insert Records into Distribution Table.
-- PLSQL Bulk operation
-- =================================================
FORALL indx in 1..count_rec
INSERT INTO gms_distributions ( document_header_id,
document_distribution_id,
document_type,
gl_date,
project_id,
task_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
quantity,
unit_price,
amount,
burdened_cost,
denom_raw_cost,
denom_burdened_cost,
acct_raw_cost,
receipt_currency_amount,
acct_burdened_cost,
dist_status,
creation_date
)
VALUES ( P_xface_id,
v_doc_dist_id(indx),
'EXP',
nvl(v_gl_date(indx),SYSDATE),
v_project_id(indx),
v_task_id(indx),
v_exp_type(indx),
v_exp_org_id(indx),
v_exp_item_date(indx),
v_quantity(indx),
1,
v_amount(indx),
v_burdened_cost(indx),
v_denom_raw_cost(indx),
v_denom_burdened_cost(indx),
v_acct_raw_cost(indx),
v_receipt_currency_amount(indx),
v_acct_burdened_cost(indx),
v_dist_status(indx),
SYSDATE
) ;
pa_cc_utils.log_message('Insert record into gms_distributions :'||to_char(count_rec));
-- Update 1st distribution into IMPORT tables.
-- ==================================================
-- 3466152
-- import process award distributions doesn't work when batch name is not supplied.
-- removed batch name criteria . Its not needed since txn_interface_id is available.
--
-- Bug 3221039 : Modified the below code to distribute based on default Award id/
-- Award number and to populate both award id and award number .
update gms_transaction_interface_all A
set (a.award_id,a.award_number) = ( select B.award_id,GA.award_number -- Bug 3221039
from gms_distribution_details B,
gms_awards_all GA
where a.txn_interface_id = b.document_distribution_id
and B.document_header_id = P_xface_id
and B.distribution_number= 1
and B.document_type = 'EXP'
and GA.award_id = B.award_id)
where --A.transaction_source = P_transaction_source -- Bug 3221039 : obsolete column
--and A.batch_name = p_batch
( (award_number IS NULL AND nvl(award_id,0)= x_default_dist_award_id )
OR
(award_number = l_default_dist_award_number)) -- Bug 3221039
and A.txn_interface_id in ( select C.document_distribution_id
from gms_distribution_details C
where C.document_header_id = P_xface_id
and C.distribution_number= 1
and C.document_type = 'EXP' );
pa_cc_utils.log_message('Update award_id in gms_transaction_interface_all count :'||to_char(SQL%ROWCOUNT));
update PA_transaction_interface_all A
set ( quantity, raw_cost, burdened_cost, denom_raw_cost, denom_burdened_cost, acct_raw_cost, acct_burdened_cost, receipt_currency_amount ) =
( select B.quantity_distributed,
B.amount_distributed,
B.burdened_cost,
B.denom_raw_cost,
B.denom_burdened_cost,
B.acct_raw_cost,
B.acct_burdened_cost,
B.receipt_currency_amount
from gms_distribution_details B
where a.txn_interface_id = b.document_distribution_id
and B.document_header_id = P_xface_id
and B.distribution_number= 1
and B.document_type = 'EXP' )
where A.transaction_source = P_transaction_source
--and A.batch_name = p_batch
and A.txn_interface_id IN ( SELECT C.document_distribution_id
from gms_distribution_details C
WHERE C.document_header_id = P_xface_id
and C.distribution_number= 1
and C.document_type = 'EXP' );
pa_cc_utils.log_message('Update ( quantity, raw_cost ) in pa_transaction_interface_all count :'||
to_char(SQL%ROWCOUNT));
UPDATE pa_transaction_interface A
SET transaction_rejection_code = 'AWARD_DISTRIBUTION_FAILED' ,
interface_id = P_xface_id ,
transaction_status_code = 'PR'
WHERE A.transaction_source = P_transaction_source
--AND A.batch_name = p_batch
AND A.TXN_INTERFACE_ID IN ( SELECT B.document_distribution_id
FROM GMS_DISTRIBUTIONS B
WHERE B.document_header_id = P_Xface_id
and B.document_type = 'EXP'
and NVL(B.dist_status,'X') <> 'FABA' ) ;
pa_cc_utils.log_message('Update distribution recject in pa_transaction_interface_all count :'||to_char(SQL%ROWCOUNT));
-- Insert distributed records into PA_transaction_interface_all and
-- gms_transaction_interface_all. Update the count in
-- pa_transaction_xface_ctrl_all
-- ==================================================================
PROC_INSERT_TRANS( P_transaction_source,
p_batch ,
p_user_id ,
p_xface_id ) ;