[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_pqh_gms_interface
(
p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
p_period_name IN varchar2,
p_project_id IN pqh_gl_interface.project_id%TYPE,
p_task_id IN pqh_gl_interface.task_id%TYPE,
p_award_id IN pqh_gl_interface.award_id%TYPE,
p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
p_organization_id IN pqh_gl_interface.organization_id%TYPE,
p_amount IN pqh_gl_interface.amount_dr%TYPE,
p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
);
PROCEDURE update_pqh_gms_interface
(
p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
p_period_name IN varchar2,
p_project_id IN pqh_gl_interface.project_id%TYPE,
p_task_id IN pqh_gl_interface.task_id%TYPE,
p_award_id IN pqh_gl_interface.award_id%TYPE,
p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
p_organization_id IN pqh_gl_interface.organization_id%TYPE,
p_amount IN pqh_gl_interface.amount_dr%TYPE,
p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
) ;
g_period_amt_tab.delete(cnt);
Select start_time_period_id,end_time_period_id
From pqh_budget_periods
Where budget_period_id = p_budget_period_id;
Select start_date,end_date
From per_time_periods
Where time_period_id = p_time_period_id;
SELECT bg.currency_code
FROM per_business_groups bg,
pqh_budgets bgt,
pqh_budget_versions bvr
WHERE bgt.budget_id = bvr.budget_id
AND bvr.budget_version_id = p_budget_version_id
AND bgt.business_group_id = bg.business_group_id ;
SELECT bgt.currency_code
FROM pqh_budgets bgt,
pqh_budget_versions bvr
WHERE bgt.budget_id = bvr.budget_id
AND bvr.budget_version_id = g_budget_version_id;
UPDATE pqh_process_log
SET message_type_cd = 'ERROR',
message_text = p_message_text,
txn_table_route_id = g_table_route_id_bvr
WHERE process_log_id = pqh_process_batch_log.g_master_process_log_id;
SELECT *
FROM pqh_budget_versions
WHERE budget_version_id = p_budget_version_id;
SELECT *
FROM pqh_budgets
WHERE budget_id = ( SELECT budget_id
FROM pqh_budget_versions
WHERE budget_version_id = p_budget_version_id ) ;
SELECT *
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_of_books_id;
SELECT *
FROM per_shared_types
WHERE shared_type_id = p_shared_type_id;
SELECT *
FROM gl_budget_versions
WHERE budget_name =p_gl_budget_name AND
status in ('O','C');
SELECT *
FROM gl_je_sources
WHERE je_source_name = 'Public Sector Budget';
SELECT *
FROM gl_je_categories
WHERE je_category_name = 'Public Sector Budget';
SELECT table_route_id
FROM pqh_table_route
WHERE table_alias = p_table_alias;
SELECT COUNT(*)
FROM pqh_budget_gl_flex_maps
WHERE budget_id = p_budget_id;
SELECT COUNT(*)
FROM pqh_budget_gl_flex_maps
WHERE budget_id = p_budget_id
AND payroll_cost_segment IS NULL;
select encumbrance_type_id
from gl_encumbrance_types
where encumbrance_type_id = 1000;
END IF; -- insert error message if l_error_flag is Y
Select bst.budget_set_id,decode(p_budget_unit_seq,1,bst.budget_unit1_value,
2,bst.budget_unit2_value,
bst.budget_unit3_value)
From pqh_budget_sets bst
Where bst.budget_period_id = p_budget_period_id;
Select distinct bst.budget_set_id,
decode(p_budget_unit_seq,1,bst.budget_unit1_value,
2,bst.budget_unit2_value,
bst.budget_unit3_value)
from pqh_budget_sets bst,pqh_budget_elements bel
Where bst.budget_period_id = p_budget_period_id
and bst.budget_set_id = bel.budget_set_id
and bel.element_type_id = p_element_type_id ;
Procedure update_money_dist_table(p_budget_ratio_table IN t_ratio_table,
p_element_type_id IN number,
p_commitment IN NUMBER,
p_distribution_table IN OUT NOCOPY t_distribution_table) IS
--
cnt number(10);
l_proc varchar2(72) := g_package||'update_money_dist_table';
End update_money_dist_table;
Procedure update_distribution_table(p_budget_ratio_table IN t_ratio_table,
p_commitment IN NUMBER,
p_distribution_table IN OUT NOCOPY t_distribution_table) IS
--
cnt number(10);
l_proc varchar2(72) := g_package||'update_distribution_table';
End update_distribution_table;
Select BGT.BUDGETED_ENTITY_CD , BGT.BUSINESS_GROUP_ID
From PQH_BUDGETS BGT,
PQH_BUDGET_VERSIONS BVR
Where BGT.BUDGET_ID = BVR.BUDGET_ID
And BGT.POSITION_CONTROL_FLAG ='Y'
And l_effective_dt BETWEEN BGT.BUDGET_START_DATE AND BGT.BUDGET_END_DATE
And BVR.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
update_distribution_table(p_budget_ratio_table => l_budget_ratio_table,
p_commitment => l_commitment,
p_distribution_table => l_distribution_table);
Select BGT.BUDGETED_ENTITY_CD, BGT.BUSINESS_GROUP_ID
From PQH_BUDGETS BGT,
PQH_BUDGET_VERSIONS BVR
Where BGT.BUDGET_ID = BVR.BUDGET_ID
And BGT.POSITION_CONTROL_FLAG ='Y'
And l_effective_dt BETWEEN BGT.BUDGET_START_DATE AND BGT.BUDGET_END_DATE
And BVR.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
Select distinct bel.element_type_id
from pqh_budget_sets bst,pqh_budget_elements bel
Where bst.budget_period_id = p_budget_period_id
and bst.budget_set_id = bel.budget_set_id;
update_money_dist_table (p_budget_ratio_table => l_budget_ratio_table,
p_commitment => l_commt_value, /* l_commitment,*/
p_element_type_id => l_element_type_id,
p_distribution_table => l_distribution_table);
SELECT bst.budget_set_id,
bel.budget_element_id,bel.element_type_id,bel.distribution_percentage,
bfs.budget_fund_src_id,bfs.cost_allocation_keyflex_id,
bfs.project_id,bfs.task_id,bfs.award_id,
bfs.expenditure_type,bfs.organization_id,
bfs.distribution_percentage
FROM pqh_budget_fund_srcs bfs, pqh_budget_elements bel,
pqh_budget_sets bst
WHERE bst.budget_period_id = p_budget_period_id
AND bst.budget_set_id = bel.budget_set_id
AND bel.budget_element_id = bfs.budget_element_id;
Select bpr.budget_period_id ,
bfs.cost_allocation_keyflex_id,
bfs.project_id,
bfs.task_id,
bfs.award_id,
bfs.expenditure_type,
bfs.organization_id
FROM pqh_budget_fund_srcs bfs, pqh_budget_elements bel,
pqh_budget_sets bst, pqh_budget_periods bpr
WHERE bpr.budget_detail_id = p_budget_detail_id
AND bpr.budget_period_id = bst.budget_period_id
AND bst.budget_set_id = bel.budget_set_id
AND bel.budget_element_id = bfs.budget_element_id
GROUP BY bpr.budget_period_id ,bfs.cost_allocation_keyflex_id,bfs.project_id,
bfs.task_id,bfs.award_id,bfs.expenditure_type, bfs.organization_id;
Select bpr.budget_period_id
From pqh_budget_periods bpr
Where bpr.budget_detail_id = p_budget_detail_id;
g_period_amt_tab.DELETE;
g_distribution_table.DELETE;
SELECT start_date
FROM per_time_periods
WHERE time_period_id = ( SELECT start_time_period_id
FROM pqh_budget_periods
WHERE budget_period_id = p_budget_period_id );
SELECT *
FROM gl_period_statuses
WHERE application_id = g_application_id
AND set_of_books_id = g_set_of_books_id
AND closing_status = 'O'
AND p_start_date BETWEEN start_date AND end_date;
PROCEDURE update_period_commitment_tab
(
p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
p_post_to_period_name IN gl_period_statuses.period_name%TYPE
)
IS
--
-- The foll procedure reads the global table g_period_amt_tab and
-- fetches the period_name and code_combination_id corresponding to the
-- period_id and cost_allocation_keyflex_id.If it does not find a period_name
-- or a code_combination_id then it will populate the global variable
-- g_detail_error to Y and we will not populate the pqh_gl_interface
-- table for the current budget_detail_id.
--
l_gl_period_statuses_rec gl_period_statuses%ROWTYPE;
l_proc varchar2(72) := 'update_period_commitment_tab';
SELECT gl.start_date into l_accounting_date
FROM gl_period_statuses gl, pqh_budgets bdgt
WHERE gl.application_id = g_application_id
AND gl.closing_status = 'O'
AND gl.set_of_books_id = g_set_of_books_id
AND gl.period_name = p_post_to_period_name
AND bdgt.budget_id = g_budget_id
AND gl.start_date <= bdgt.budget_end_date
AND gl.end_date >= bdgt.budget_start_date ;
pqh_process_batch_log.insert_log
(
p_message_type_cd => 'ERROR',
p_message_text => l_message_text
);
pqh_process_batch_log.insert_log
(
p_message_type_cd => 'ERROR',
p_message_text => l_message_text
);
END update_period_commitment_tab;
3 3 US 100 (update) 3 3 US 200 ( update )
4 4 US 100 (unchanged) 4 4 US 100 ( unchanged )
4 7 UK 100 ( new )
7 9 US 100 ( new )
***************************************************************************************************************/
--
PROCEDURE build_old_bdgt_dtls_tab
(
p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
p_posting_type_cd IN varchar2
) IS
--
-- local variables
--
l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
SELECT *
FROM pqh_gl_interface
WHERE budget_version_id = g_budget_version_id
AND budget_detail_id = p_budget_detail_id
AND posting_type_cd = p_posting_type_cd
AND NVL(adjustment_flag,'N') = 'N'
AND status IS NOT NULL
AND posting_date IS NOT NULL
AND (NVL(amount_dr,0) > 0 OR NVL(amount_cr,0) > 0 ) ;
SELECT *
FROM pqh_gl_interface
WHERE budget_version_id = g_budget_version_id
AND budget_detail_id = p_budget_detail_id
AND posting_type_cd = p_posting_type_cd
AND period_name = p_period_name
AND code_combination_id = p_code_combination_id
AND currency_code = p_currency_code
AND NVL(adjustment_flag,'N') = 'N'
AND status IS NOT NULL
AND posting_date IS NOT NULL
AND cost_allocation_keyflex_id is not null
FOR UPDATE of amount_dr;
SELECT *
FROM pqh_gl_interface
WHERE budget_version_id = g_budget_version_id
AND budget_detail_id = p_budget_detail_id
AND posting_type_cd = p_posting_type_cd
AND period_name = p_period_name
AND project_id = p_project_id
AND task_id = p_task_id
AND award_id = p_award_id
AND expenditure_type = p_expenditure_type
AND organization_id = p_organization_id
AND currency_code = p_currency_code
AND NVL(adjustment_flag,'N') = 'N'
AND status IS NOT NULL
AND posting_date IS NOT NULL
AND cost_allocation_keyflex_id is null
FOR UPDATE of amount_dr;
UPDATE pqh_gl_interface
SET amount_dr = 0
WHERE CURRENT OF csr_pqh_gl_interface;
UPDATE pqh_gl_interface
SET amount_dr = 0
WHERE CURRENT OF csr_pqh_gms_interface;
INSERT INTO pqh_gl_interface
(
gl_interface_id,
budget_version_id,
budget_detail_id,
period_name,
accounting_date,
code_combination_id,
cost_allocation_keyflex_id,
project_id,
task_id,
award_id,
expenditure_type,
organization_id,
amount_dr,
amount_cr,
currency_code,
status,
adjustment_flag,
posting_type_cd,
posting_date
)
VALUES
(
pqh_gl_interface_s.nextval,
g_budget_version_id,
p_budget_detail_id,
g_old_bdgt_dtls_tab(i).period_name,
g_old_bdgt_dtls_tab(i).accounting_date,
g_old_bdgt_dtls_tab(i).code_combination_id,
g_old_bdgt_dtls_tab(i).cost_allocation_keyflex_id,
g_old_bdgt_dtls_tab(i).project_id,
g_old_bdgt_dtls_tab(i).task_id,
g_old_bdgt_dtls_tab(i).award_id,
g_old_bdgt_dtls_tab(i).expenditure_type,
g_old_bdgt_dtls_tab(i).organization_id,
0,
NVL(l_pqh_gl_interface_rec.amount_dr,0),
g_old_bdgt_dtls_tab(i).currency_code,
null,
'Y',
'COMMITMENT',
null
);
SELECT *
FROM pqh_gl_interface
WHERE budget_version_id = g_budget_version_id
AND period_name = p_period_name
AND currency_code = p_currency_code
AND code_combination_id = p_code_combination_id
AND posting_type_cd = p_posting_type_cd
AND NVL(adjustment_flag,'N') = 'Y'
AND status IS NULL
AND posting_date IS NULL;
UPDATE pqh_gl_interface
SET amount_dr = NVL(amount_dr,0) -
NVL(l_pqh_gl_interface_rec.amount_dr,0) +
NVL(l_pqh_gl_interface_rec.amount_cr,0)
WHERE budget_version_id = l_pqh_gl_interface_rec.budget_version_id
AND budget_detail_id = l_pqh_gl_interface_rec.budget_detail_id
AND period_name = l_pqh_gl_interface_rec.period_name
AND currency_code = l_pqh_gl_interface_rec.currency_code
AND code_combination_id = l_pqh_gl_interface_rec.code_combination_id
AND posting_type_cd = p_posting_type_cd
AND NVL(adjustment_flag,'N') = 'N'
AND status IS NOT NULL;
UPDATE pqh_budget_details
SET commitment_gl_status = 'ERROR'
WHERE budget_version_id = g_budget_version_id
AND budget_detail_id IN
( SELECT distinct budget_detail_id
FROM pqh_gl_interface
WHERE budget_version_id = g_budget_version_id
AND period_name = p_period_name
AND currency_code = p_currency_code
AND code_combination_id = p_code_combination_id
AND posting_type_cd = p_posting_type_cd
AND status IS NULL
AND posting_date IS NULL
);
DELETE FROM pqh_gl_interface
WHERE budget_version_id = g_budget_version_id
AND period_name = p_period_name
AND currency_code = p_currency_code
AND code_combination_id = p_code_combination_id
AND posting_type_cd = p_posting_type_cd
AND status IS NULL
AND posting_date IS NULL;
SELECT period_name, accounting_date,
code_combination_id, cost_allocation_keyflex_id, currency_code,
SUM(NVL(amount_dr,0)) amount_dr,
SUM(NVL(amount_cr,0)) amount_cr
FROM pqh_gl_interface
WHERE budget_version_id IN (g_budget_version_id, NVL(g_last_posted_ver,0) )
AND status IS NULL
AND posting_date IS NULL
AND posting_type_cd = 'COMMITMENT'
AND cost_allocation_keyflex_id is NOT NULL
GROUP BY period_name, accounting_date,code_combination_id,
cost_allocation_keyflex_id,currency_code;
SELECT gl_bc_packets_s.nextval
FROM dual;
SELECT *
FROM gl_period_statuses
WHERE application_id = g_application_id
AND set_of_books_id = g_set_of_books_id
AND period_name = p_period_name;
SELECT description
FROM gl_lookups
WHERE lookup_type = 'FUNDS_CHECK_RESULT_CODE'
AND lookup_code = p_lookup_code
AND NVL(enabled_flag,'N') = 'Y';
SELECT result_code
FROM gl_bc_packets
WHERE packet_id = p_packet_id;
SELECT description
FROM gl_lookups
WHERE lookup_type = 'FUNDS_CHECK_STATUS_CODE'
AND lookup_code = p_lookup_code
AND NVL(enabled_flag,'N') = 'Y';
hr_utility.set_location('Inserting into GL_BC_PACKETS',10);
pqh_process_batch_log.insert_log
(
p_message_type_cd => 'ERROR',
p_message_text => l_packet_status_code||' : '||l_fc_message
);
hr_utility.set_location('Inserted Error and calling reverse txn ',140);
hr_utility.set_location('Inserting into GL_INTERFACE',200);
INSERT INTO gl_interface
(status,
set_of_books_id,
user_je_source_name,
user_je_category_name,
currency_code,
date_created,
created_by,
actual_flag,
accounting_date,
period_name,
code_combination_id,
chart_of_accounts_id,
entered_dr,
entered_cr,
encumbrance_type_id,
reference1,
reference2)
VALUES
('NEW',
g_set_of_books_id,
g_user_je_source_name,
g_user_je_category_name,
l_currency_code,
sysdate,
8302,
'E',
l_accounting_date,
l_period_name,
l_code_combination_id,
g_chart_of_accounts_id,
NVL(l_amount_dr,0),
NVL(l_amount_cr,0),
1000, -- encumbrance_type_id
g_budget_version_id,
l_cost_allocation_keyflex_id);
PROCEDURE update_commitment_gl_status
IS
--
-- This procedure will update the gl_status of pqh_budget_versions,
-- pqh_budget_details and update the pqh_gl_interface table
-- We always update the TRANSFERED_TO_GL_FLAG = Y to indicate this is the
-- latest budget_version that is posted to GL
-- gl_status = POST or ERROR
--
-- local variables
--
l_budget_details_rec pqh_budget_details%ROWTYPE;
SELECT *
FROM pqh_budget_details
WHERE budget_version_id = g_budget_version_id
AND NVL(commitment_gl_status,'X') <> 'ERROR'
FOR UPDATE OF commitment_gl_status;
SELECT COUNT(*)
FROM pqh_budget_details
WHERE budget_version_id = g_budget_version_id
AND NVL(commitment_gl_status,'ERROR') = 'ERROR';
l_proc varchar2(72) := g_package||'update_commitment_gl_status';
UPDATE pqh_budget_details
SET commitment_gl_status = 'POST'
WHERE CURRENT OF csr_budget_details;
UPDATE pqh_budget_versions
SET commitment_gl_status = 'POST'
WHERE budget_version_id = g_budget_version_id;
UPDATE pqh_budget_versions
SET commitment_gl_status = 'ERROR'
WHERE budget_version_id = g_budget_version_id;
UPDATE pqh_gl_interface
SET posting_date = sysdate,
status = 'POST'
WHERE budget_version_id = g_budget_version_id
AND posting_type_cd = 'COMMITMENT'
AND posting_date IS NULL
AND status IS NULL;
UPDATE pqh_gl_interface
SET posting_date = sysdate,
status = 'POST'
WHERE budget_version_id = NVL(g_last_posted_ver,0)
AND posting_type_cd = 'COMMITMENT'
AND posting_date IS NULL
AND status IS NULL;
END update_commitment_gl_status;
SELECT *
FROM pqh_budget_details
WHERE budget_detail_id = p_budget_detail_id ;
PROCEDURE insert_pqh_gl_interface
(
p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
p_period_name IN pqh_gl_interface.period_name%TYPE,
p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
p_amount IN pqh_gl_interface.amount_dr%TYPE,
p_currency_code IN pqh_gl_interface.currency_code%TYPE,
p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
) IS
--
-- This procedure will insert record into pqh_gl_interface
-- If the same UOM is repeated more then once then we would update the unposted txn.
--
CURSOR csr_pqh_gl_interface IS
SELECT COUNT(*)
FROM pqh_gl_interface
WHERE budget_version_id = g_budget_version_id
AND budget_detail_id = p_budget_detail_id
AND period_name = p_period_name
AND code_combination_id = p_code_combination_id
AND currency_code = p_currency_code
AND posting_type_cd = p_posting_type_cd
AND NVL(adjustment_flag,'N') = 'N'
AND status IS NULL
AND posting_date IS NULL
AND cost_allocation_keyflex_id is not null;
l_proc varchar2(72) := g_package||'insert_pqh_gl_interface';
hr_utility.set_location('l_count in Insert pqh_gl_interface : '||l_count,10);
UPDATE pqh_gl_interface
SET AMOUNT_DR = NVL(AMOUNT_DR,0) + NVL(p_amount,0)
WHERE budget_version_id = g_budget_version_id
AND budget_detail_id = p_budget_detail_id
AND period_name = p_period_name
AND code_combination_id = p_code_combination_id
AND currency_code = p_currency_code
AND posting_type_cd = p_posting_type_cd
AND NVL(adjustment_flag,'N') = 'N'
AND status IS NULL
AND posting_date IS NULL;
INSERT INTO pqh_gl_interface
(
gl_interface_id,
budget_version_id,
budget_detail_id,
period_name,
accounting_date,
code_combination_id,
cost_allocation_keyflex_id,
amount_dr,
amount_cr,
currency_code,
status,
adjustment_flag,
posting_date,
posting_type_cd
)
VALUES
(
pqh_gl_interface_s.nextval,
g_budget_version_id,
p_budget_detail_id,
p_period_name,
p_accounting_date,
p_code_combination_id,
p_cost_allocation_keyflex_id,
NVL(p_amount,0),
0,
p_currency_code,
null,
null,
null,
p_posting_type_cd
);
END insert_pqh_gl_interface;
PROCEDURE update_pqh_gl_interface
(
p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
p_period_name IN pqh_gl_interface.period_name%TYPE,
p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
p_amount IN pqh_gl_interface.amount_dr%TYPE,
p_currency_code IN pqh_gl_interface.currency_code%TYPE,
p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
) IS
--
-- This procedure will update pqh_gl_interface and create a adjustment record
--
--
-- local variables
--
l_proc varchar2(72) := g_package||'update_pqh_gl_interface';
SELECT *
FROM pqh_gl_interface
WHERE budget_version_id = g_budget_version_id
AND budget_detail_id = p_budget_detail_id
AND period_name = p_period_name
AND code_combination_id = p_code_combination_id
AND currency_code = p_currency_code
AND posting_type_cd = p_posting_type_cd
AND NVL(adjustment_flag,'N') = 'N'
AND status IS NOT NULL
AND posting_date IS NOT NULL
AND cost_allocation_keyflex_id is not null
FOR UPDATE of amount_dr;
UPDATE pqh_gl_interface
SET amount_dr = NVL(p_amount,0)
WHERE CURRENT OF csr_pqh_gl_interface;
INSERT INTO pqh_gl_interface
(
gl_interface_id,
budget_version_id,
budget_detail_id,
period_name,
accounting_date,
code_combination_id,
cost_allocation_keyflex_id,
amount_dr,
amount_cr,
currency_code,
status,
adjustment_flag,
posting_date,
posting_type_cd
)
VALUES
(
pqh_gl_interface_s.nextval,
g_budget_version_id,
p_budget_detail_id,
p_period_name,
p_accounting_date,
p_code_combination_id,
p_cost_allocation_keyflex_id,
NVL(l_amount_dr,0),
NVL(l_amount_cr,0),
p_currency_code,
null,
'Y',
null,
p_posting_type_cd
);
END IF; -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
END update_pqh_gl_interface;
SELECT COUNT(*)
FROM pqh_gl_interface
WHERE budget_version_id = p_budget_version_id
AND budget_detail_id = p_budget_detail_id
AND period_name = p_period_name
AND code_combination_id = p_code_combination_id
AND currency_code = p_currency_code
AND posting_type_cd = p_posting_type_cd
AND NVL(adjustment_flag,'N') = 'N'
AND status IS NOT NULL
AND posting_date IS NOT NULL
AND cost_allocation_keyflex_id is not null;
update_pqh_gl_interface
(
p_budget_detail_id => p_budget_detail_id,
p_period_name => g_period_amt_tab(i).period_name,
p_accounting_date => g_period_amt_tab(i).accounting_date,
p_code_combination_id => g_period_amt_tab(i).code_combination_id,
p_cost_allocation_keyflex_id=> g_period_amt_tab(i).cost_allocation_keyflex_id,
p_amount => g_period_amt_tab(i).commitment1,
p_posting_type_cd => p_posting_type_cd,
p_currency_code => g_currency_code1
);
insert_pqh_gl_interface
(
p_budget_detail_id => p_budget_detail_id,
p_period_name => g_period_amt_tab(i).period_name,
p_accounting_date => g_period_amt_tab(i).accounting_date,
p_code_combination_id => g_period_amt_tab(i).code_combination_id,
p_cost_allocation_keyflex_id => g_period_amt_tab(i).cost_allocation_keyflex_id,
p_amount => g_period_amt_tab(i).commitment1,
p_posting_type_cd => p_posting_type_cd,
p_currency_code => g_currency_code1
);
update_pqh_gl_interface
(
p_budget_detail_id => p_budget_detail_id,
p_period_name => g_period_amt_tab(i).period_name,
p_accounting_date => g_period_amt_tab(i).accounting_date,
p_code_combination_id => g_period_amt_tab(i).code_combination_id,
p_cost_allocation_keyflex_id=> g_period_amt_tab(i).cost_allocation_keyflex_id,
p_amount => g_period_amt_tab(i).commitment2,
p_posting_type_cd => p_posting_type_cd,
p_currency_code => g_currency_code2
);
insert_pqh_gl_interface
(
p_budget_detail_id => p_budget_detail_id,
p_period_name => g_period_amt_tab(i).period_name,
p_accounting_date => g_period_amt_tab(i).accounting_date,
p_code_combination_id => g_period_amt_tab(i).code_combination_id,
p_cost_allocation_keyflex_id => g_period_amt_tab(i).cost_allocation_keyflex_id,
p_amount => g_period_amt_tab(i).commitment2,
p_posting_type_cd => p_posting_type_cd,
p_currency_code => g_currency_code2
);
update_pqh_gl_interface
(
p_budget_detail_id => p_budget_detail_id,
p_period_name => g_period_amt_tab(i).period_name,
p_accounting_date => g_period_amt_tab(i).accounting_date,
p_code_combination_id => g_period_amt_tab(i).code_combination_id,
p_cost_allocation_keyflex_id=> g_period_amt_tab(i).cost_allocation_keyflex_id,
p_amount => g_period_amt_tab(i).commitment3,
p_posting_type_cd => p_posting_type_cd,
p_currency_code => g_currency_code3
);
insert_pqh_gl_interface
(
p_budget_detail_id => p_budget_detail_id,
p_period_name => g_period_amt_tab(i).period_name,
p_accounting_date => g_period_amt_tab(i).accounting_date,
p_code_combination_id => g_period_amt_tab(i).code_combination_id,
p_cost_allocation_keyflex_id => g_period_amt_tab(i).cost_allocation_keyflex_id,
p_amount => g_period_amt_tab(i).commitment3,
p_posting_type_cd => p_posting_type_cd,
p_currency_code => g_currency_code3
);
UPDATE pqh_budget_details
SET commitment_gl_status = ''
WHERE budget_detail_id = p_budget_detail_id;
UPDATE pqh_budget_details
SET commitment_gl_status = 'ERROR'
WHERE budget_detail_id = p_budget_detail_id;
SELECT *
FROM pqh_budget_details
WHERE budget_version_id = p_budget_version_id
AND NVL(commitment_gl_status,'X') <> 'POST';
Select 1
From PQH_BUDGETS BGT,
PQH_BUDGET_VERSIONS BVR
Where BGT.BUDGET_ID = BVR.BUDGET_ID
And BGT.POSITION_CONTROL_FLAG ='Y'
And l_effective_dt BETWEEN BGT.BUDGET_START_DATE AND BGT.BUDGET_END_DATE
And BVR.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
update_period_commitment_tab
(
p_budget_detail_id => l_budget_details_rec.budget_detail_id,
p_post_to_period_name => p_post_to_period_name
);
update_commitment_gl_status;
PROCEDURE insert_pqh_gms_interface
(
p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
p_period_name IN varchar2,
p_project_id IN pqh_gl_interface.project_id%TYPE,
p_task_id IN pqh_gl_interface.task_id%TYPE,
p_award_id IN pqh_gl_interface.award_id%TYPE,
p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
p_organization_id IN pqh_gl_interface.organization_id%TYPE,
p_amount IN pqh_gl_interface.amount_dr%TYPE,
p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
) IS
/*
This procedure will insert record into pqh_gl_interface
If the same UOM is repeated more then once then we would update the unposted txn.
*/
--
-- local variables
--
l_proc varchar2(72) := g_package||'.insert_pqh_gms_interface';
Select COUNT(*)
From pqh_gl_interface
Where budget_version_id = g_budget_version_id
AND budget_detail_id = p_budget_detail_id
AND p_period_name = p_period_name
AND posting_type_cd = p_posting_type_cd
AND project_id = p_project_id
AND task_id = p_task_id
AND award_id = p_award_id
AND expenditure_type = p_expenditure_type
AND organization_id = p_organization_id
AND NVL(adjustment_flag,'N') = 'N'
AND status IS NULL
AND posting_date IS NULL
AND cost_allocation_keyflex_id is null;
hr_utility.set_location('l_count in Insert pqh_gl_interface : '||l_count,10);
UPDATE pqh_gl_interface
SET AMOUNT_DR = NVL(AMOUNT_DR,0) + NVL(p_amount,0)
WHERE budget_version_id = g_budget_version_id
AND budget_detail_id = p_budget_detail_id
AND p_period_name = p_period_name
AND posting_type_cd = p_posting_type_cd
AND project_id = p_project_id
AND task_id = p_task_id
AND award_id = p_award_id
AND expenditure_type = p_expenditure_type
AND organization_id = p_organization_id
AND NVL(adjustment_flag,'N') = 'N'
AND status IS NULL
AND posting_date IS NULL;
INSERT INTO pqh_gl_interface
(
gl_interface_id,
budget_version_id,
budget_detail_id,
period_name,
project_id,
task_id,
award_id,
expenditure_type,
organization_id,
amount_dr,
amount_cr,
currency_code,
status,
adjustment_flag,
posting_date,
posting_type_cd
)
VALUES
(
pqh_gl_interface_s.nextval,
g_budget_version_id,
p_budget_detail_id,
p_period_name,
p_project_id,
p_task_id,
p_award_id,
p_expenditure_type,
p_organization_id,
NVL(p_amount,0),
0,
g_bgt_currency_code,
null,
null,
null,
p_posting_type_cd
);
END insert_pqh_gms_interface;
PROCEDURE update_pqh_gms_interface
(
p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
p_period_name IN varchar2,
p_project_id IN pqh_gl_interface.project_id%TYPE,
p_task_id IN pqh_gl_interface.task_id%TYPE,
p_award_id IN pqh_gl_interface.award_id%TYPE,
p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
p_organization_id IN pqh_gl_interface.organization_id%TYPE,
p_amount IN pqh_gl_interface.amount_dr%TYPE,
p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
) IS
/*
This procedure will update pqh_gl_interface and create a adjustment record
*/
--
-- local variables
--
l_proc varchar2(72) := g_package||'.update_pqh_gms_interface';
SELECT *
FROM pqh_gl_interface
WHERE budget_version_id = g_budget_version_id
AND budget_detail_id = p_budget_detail_id
AND period_name = p_period_name
AND posting_type_cd = p_posting_type_cd
AND project_id = p_project_id
AND task_id = p_task_id
AND award_id = p_award_id
AND expenditure_type = p_expenditure_type
AND organization_id = p_organization_id
AND NVL(adjustment_flag,'N') = 'N'
AND status IS NOT NULL
AND posting_date IS NOT NULL
AND cost_allocation_keyflex_id is null
FOR UPDATE of amount_dr;
UPDATE pqh_gl_interface
SET amount_dr = NVL(p_amount,0)
WHERE CURRENT OF csr_pqh_gms_interface;
INSERT INTO pqh_gl_interface
(
gl_interface_id,
budget_version_id,
budget_detail_id,
period_name,
project_id,
task_id,
award_id,
expenditure_type,
organization_id,
amount_dr,
amount_cr,
currency_code,
status,
adjustment_flag,
posting_date,
posting_type_cd
)
VALUES
(
pqh_gl_interface_s.nextval,
g_budget_version_id,
p_budget_detail_id,
p_period_name,
p_project_id,
p_task_id,
p_award_id,
p_expenditure_type,
p_organization_id,
NVL(l_amount_dr,0),
NVL(l_amount_cr,0),
g_bgt_currency_code,
null,
'Y',
null,
p_posting_type_cd
);
END IF; -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
END update_pqh_gms_interface;
This procedure will update or insert GMS records into pqh_gl_interface if there was no error for
the current budget detail record i.e g_detail_error = N
if g_detail_error = Y then update the pqh_budget_details record with gl_status = ERROR
Also it will Deduct a similar amount from Budget Commitments.
If a Bduget Commitment for that Detail/Period is not available error is thrown and program is
aborted
*/
--
-- local variables
--
l_proc varchar2(72) := g_package||'.populate_pqh_gms_interface';
Select *
From pqh_gl_interface
Where budget_version_id = p_budget_version_id
AND budget_detail_id = p_budget_detail_id
AND period_name = p_period_name
AND posting_type_cd = p_posting_type_cd
AND project_id = p_project_id
AND task_id = p_task_id
AND award_id = p_award_id
AND expenditure_type = p_expenditure_type
AND organization_id = p_organization_id
AND NVL(adjustment_flag,'N') = 'N'
AND cost_allocation_keyflex_id is null
AND nvl(status,'X')='POST'
AND posting_date IS NOT NULL
AND cost_allocation_keyflex_id is null
FOR UPDATE of amount_dr;
Select
hr_general.decode_shared_type(budget_unit1_id) UOM1,
hr_general.decode_shared_type(budget_unit2_id) UOM2,
hr_general.decode_shared_type(budget_unit3_id) UOM3
From
pqh_budgets
Where budget_id=g_budget_id;
update_pqh_gms_interface
(
p_budget_detail_id => p_budget_detail_id,
p_period_name => g_period_amt_tab(i).period_name,
p_project_id => g_period_amt_tab(i).project_id,
p_task_id => g_period_amt_tab(i).task_id,
p_award_id => g_period_amt_tab(i).award_id,
p_expenditure_type => g_period_amt_tab(i).expenditure_type,
p_organization_id => g_period_amt_tab(i).organization_id,
p_amount => l_amount,
p_posting_type_cd => p_posting_type_cd
);
insert_pqh_gms_interface
(
p_budget_detail_id => p_budget_detail_id,
p_period_name => g_period_amt_tab(i).period_name,
p_project_id => g_period_amt_tab(i).project_id,
p_task_id => g_period_amt_tab(i).task_id,
p_award_id => g_period_amt_tab(i).award_id,
p_expenditure_type => g_period_amt_tab(i).expenditure_type,
p_organization_id => g_period_amt_tab(i).organization_id,
p_amount => l_amount,
p_posting_type_cd => p_posting_type_cd
);
UPDATE pqh_gl_interface
SET amount_dr = amount_dr - l_amount
WHERE CURRENT OF csr_pqh_gms_interface;
INSERT INTO pqh_gl_interface
(
gl_interface_id,
budget_version_id,
budget_detail_id,
period_name,
project_id,
task_id,
award_id,
expenditure_type,
organization_id,
amount_dr,
amount_cr,
currency_code,
status,
adjustment_flag,
posting_date,
posting_type_cd
)
VALUES
(
pqh_gl_interface_s.nextval,
g_budget_version_id,
p_budget_detail_id,
g_period_amt_tab(i).period_name,
g_period_amt_tab(i).project_id,
g_period_amt_tab(i).task_id,
g_period_amt_tab(i).award_id,
g_period_amt_tab(i).expenditure_type,
g_period_amt_tab(i).organization_id,
l_amount_dr,
l_amount_cr,
g_bgt_currency_code,
null,
'Y',
null,
'BUDGET'
);
END IF; -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
END IF; -- Insert only GMS records
UPDATE pqh_budget_details
SET commitment_gl_status = ''
WHERE budget_detail_id = p_budget_detail_id;
UPDATE pqh_budget_details
SET commitment_gl_status = 'ERROR'
WHERE budget_detail_id = p_budget_detail_id;
This procedure Inserts in gl_bc_packets , commits so that the data is available
for the autonomous funds checker and runs funds checker returns as argument funds
checker return code and success flag
*/
--
-- local variables
--
l_proc varchar2(72) := g_package||'.ins_gl_bc_run_fund_check';
INSERT INTO gl_bc_packets
(packet_id,
ledger_id,
je_source_name,
je_category_name,
code_combination_id,
actual_flag,
period_name,
period_year,
period_num,
quarter_num,
currency_code,
status_code,
last_update_date,
last_updated_by,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
encumbrance_type_id,
reference1,
reference2 )
VALUES
(p_packet_id,
g_set_of_books_id,
g_user_je_source_name,
g_user_je_category_name,
p_code_combination_id,
'E',
p_period_name,
p_period_year,
p_period_num,
p_quarter_num,
p_currency_code,
'P',
sysdate,
8302,
p_entered_dr,
p_entered_cr,
p_accounted_dr,
p_accounted_cr,
1000, -- encumbrance_type_id
g_budget_version_id,
p_cost_allocation_keyflex_id );
Select
'PQH'||to_char(pqh_gms_batch_name_s.nextval) INTO p_gms_batch_name
From dual;
Select pa_txn_interface_s.nextval
INTO l_txn_interface_id
From dual;
INSERT INTO PA_TRANSACTION_INTERFACE_ALL
(
TXN_INTERFACE_ID
,TRANSACTION_SOURCE
,BATCH_NAME
,EXPENDITURE_ENDING_DATE
,ORGANIZATION_NAME
,EXPENDITURE_ITEM_DATE
,PROJECT_NUMBER
,TASK_NUMBER
,EXPENDITURE_TYPE
,QUANTITY
,TRANSACTION_STATUS_CODE
,ORIG_TRANSACTION_REFERENCE
,ORG_ID
,DENOM_CURRENCY_CODE
,DENOM_RAW_COST
)
VALUES
(
l_txn_interface_id
,g_gms_import_tab(cnt).TRANSACTION_SOURCE
,p_gms_batch_name
,g_gms_import_tab(cnt).EXPENDITURE_ENDING_DATE
,g_gms_import_tab(cnt).ORGANIZATION_NAME
,g_gms_import_tab(cnt).EXPENDITURE_ITEM_DATE
,g_gms_import_tab(cnt).PROJECT_NUMBER
,g_gms_import_tab(cnt).TASK_NUMBER
,g_gms_import_tab(cnt).EXPENDITURE_TYPE
,g_gms_import_tab(cnt).QUANTITY
,'P'
,g_gms_import_tab(cnt).ORIG_TRANSACTION_REFERENCE
,g_gms_import_tab(cnt).ORG_ID
,g_gms_import_tab(cnt).DENOM_CURRENCY_CODE
,g_gms_import_tab(cnt).amount
);
In case of failure the status in pqh_gl_interface is updated to error
*/
--
-- Cursor to get records rejected by import process
--
CURSOR gms_tie_back_reject_cur IS
SELECT
nvl(transaction_rejection_code,'P') rejection_code,
orig_transaction_reference,
transaction_status_code
FROM pa_transaction_interface_all
WHERE transaction_source = 'GMSEPQHC '
AND batch_name = p_gms_batch_name
AND transaction_status_code in ('R', 'PI', 'PR', 'PO');
SELECT
count(*) into l_cnt
FROM pa_transaction_interface_all
WHERE transaction_source = 'GMSEPQHC '
And batch_name = p_gms_batch_name
And transaction_status_code in ('P', 'I');
UPDATE pqh_gl_interface
SET status='ERROR',posting_date=sysdate
WHERE period_name =g_gms_import_tab(l_int_id).period_name And
project_id =g_gms_import_tab(l_int_id).project_id And
task_id =g_gms_import_tab(l_int_id).task_id And
award_id =g_gms_import_tab(l_int_id).award_id And
expenditure_type =g_gms_import_tab(l_int_id).expenditure_type And
organization_id =g_gms_import_tab(l_int_id).organization_id;
UPDATE pqh_budget_details
SET gl_status = 'ERROR'
Where budget_detail_id in (select budget_detail_id from pqh_gl_interface where
budget_version_id=g_budget_version_id
And cost_allocation_keyflex_id is null
And status='ERROR'
);
DELETE pa_transaction_interface_all
WHERE batch_name = p_gms_batch_name
And transaction_source = 'GMSEPQHC ';
hr_utility.set_location('Deleted pa_transaction_interface_all:',20);
DELETE gms_transaction_interface_all
WHERE batch_name = p_gms_batch_name
And transaction_source = 'GMSEPQHC ';
hr_utility.set_location('Deleted gms_transaction_interface_all:',30);
Select business_group_id
From pqh_budgets
Where budget_id=g_budget_id;
Select transaction_source
From pa_transaction_sources
Where transaction_source = 'GMSEPQHC ';
Select period_name,project_id,award_id,task_id,
expenditure_type,organization_id,
currency_code,
SUM(NVL(amount_dr,0)) amount_dr,
SUM(NVL(amount_cr,0)) amount_cr
From pqh_gl_interface
Where budget_version_id = g_budget_version_id
AND posting_type_cd = 'COMMITMENT'
AND status IS NULL
AND posting_date IS NULL
AND cost_allocation_keyflex_id IS NULL
group by
period_name,project_id,award_id,task_id,
expenditure_type,organization_id,currency_code;
Select name
From hr_organization_units
Where organization_id = p_organization_id
AND business_group_id = l_bg_id;
Select segment1,org_id
From pa_projects_all
Where project_id = p_project_id;
Select task_number
From pa_tasks
Where task_id = p_task_id;
select pqh_gms_orig_txn_reference_s.nextval
into ref_cnt
from dual;
pqh_process_batch_log.insert_log
(
p_message_type_cd => 'ERROR',
p_message_text => fnd_message.get
);