[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
);
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
) ;
SELECT *
FROM pqh_budget_details
WHERE budget_version_id = p_budget_version_id
AND NVL(gl_status,'X') <> 'POST';
update_period_amt_tab
(
p_budget_detail_id => l_budget_details_rec.budget_detail_id
);
update gl_status of pqh_budget_versions and pqh_budget_details
update posting_date and status of pqh_gl_interface
update the global g_status with the program status
*/
IF NOT p_validate THEN
update_gl_status;
SELECT *
FROM pqh_budget_versions
WHERE budget_version_id = p_budget_version_id;
SELECT *
FROM pqh_budgets
WHERE budget_id = c_budget_id;
( SELECT budget_id
FROM pqh_budget_versions
WHERE budget_version_id = p_budget_version_id ) ;
SELECT bg.ORG_INFORMATION10
FROM HR_ORGANIZATION_INFORMATION bg
WHERE bg.organization_id = p_business_group_id
AND bg.ORG_INFORMATION_CONTEXT = 'Business Group Information';
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 COUNT(*)
FROM pqh_budget_gl_flex_maps
WHERE budget_id = p_budget_id;
SELECT table_alias,table_route_id
FROM pqh_table_route
WHERE table_alias IN ('BVR','BDT','BPR','BFS','GLF');
SELECT COUNT(*)
FROM pqh_budget_gl_flex_maps
WHERE budget_id = p_budget_id
AND payroll_cost_segment IS NULL;
UPDATE pqh_process_log
SET message_type_cd = 'ERROR',
message_text = l_message_text,
txn_table_route_id = g_table_route_id_bvr
-- batch_status = 'ERROR',
-- batch_end_date = sysdate
WHERE process_log_id = pqh_process_batch_log.g_master_process_log_id;
END IF; -- insert error message if l_error_flag is Y
SELECT bpr.budget_period_id ,
bfs.cost_allocation_keyflex_id,
bfs.project_id,
bfs.award_id,
bfs.task_id,
bfs.expenditure_type,
bfs.organization_id,
SUM(pqh_gl_posting.get_amt1(bfs.budget_fund_src_id)) Amount1,
SUM(pqh_gl_posting.get_amt2(bfs.budget_fund_src_id)) Amount2,
SUM(pqh_gl_posting.get_amt3(bfs.budget_fund_src_id)) Amount3
FROM pqh_budget_fund_srcs bfs, pqh_budget_elements bel,
pqh_budget_sets bst, pqh_budget_periods bpr
WHERE 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
AND bpr.budget_detail_id = p_budget_detail_id
GROUP BY bpr.budget_period_id ,bfs.cost_allocation_keyflex_id,
bfs.project_id, bfs.award_id,bfs.task_id,
bfs.expenditure_type,bfs.organization_id
ORDER BY bpr.budget_period_id , bfs.cost_allocation_keyflex_id,
bfs.project_id, bfs.award_id,bfs.task_id,
bfs.expenditure_type,bfs.organization_id;
SELECT bpr.budget_period_id ,
bfs.cost_allocation_keyflex_id,
bfs.project_id,
bfs.award_id,
bfs.task_id,
bfs.expenditure_type,
bfs.organization_id,
SUM((NVL(bfs.dflt_dist_percentage,0)*0.01)*(NVL(bel.dflt_dist_percentage,0)*0.01)*NVL(bst.budget_unit1_value,0)) Amount1,
SUM((NVL(bfs.dflt_dist_percentage,0)*0.01)*(NVL(bel.dflt_dist_percentage,0)*0.01)*NVL(bst.budget_unit2_value,0)) Amount2,
SUM((NVL(bfs.dflt_dist_percentage,0)*0.01)*(NVL(bel.dflt_dist_percentage,0)*0.01)*NVL(bst.budget_unit3_value,0)) Amount3
FROM pqh_dflt_fund_srcs bfs,
pqh_dflt_budget_elements bel,
pqh_budget_sets bst,
pqh_budget_periods bpr
WHERE bpr.budget_period_id = bst.budget_period_id
AND bst.dflt_budget_set_id = bel.dflt_budget_set_id
AND bel.dflt_budget_element_id = bfs.dflt_budget_element_id
AND bpr.budget_detail_id = p_budget_detail_id
GROUP BY bpr.budget_period_id ,bfs.cost_allocation_keyflex_id,
bfs.project_id, bfs.award_id,bfs.task_id,
bfs.expenditure_type,bfs.organization_id
ORDER BY bpr.budget_period_id , bfs.cost_allocation_keyflex_id,
bfs.project_id, bfs.award_id,bfs.task_id,
bfs.expenditure_type,bfs.organization_id;
g_period_amt_tab.DELETE;
PROCEDURE update_period_amt_tab
(
p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE
)
IS
/*
This procedure will read the above populated global table g_period_amt_tab and
1.Get 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.
2.Get LD Encumbrance/Liquidation amount for each Budget Period and make adjustments to
all PTAEO's invlved in that Budget period.
If g_detail_error is Y then we will not populate the pqh_gl_interface table for the current
budget_detail_id
*/
--
-- local variables
--
l_proc varchar2(72) := g_package||'.update_period_amt_tab';
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_amt_tab;
This procedure will update or insert 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
*/
--
-- local variables
--
l_proc varchar2(72) := g_package||'.populate_pqh_gl_interface';
SELECT COUNT(*)
FROM pqh_gl_interface
WHERE budget_version_id = g_budget_version_id
AND budget_detail_id = p_budget_detail_id
AND posting_type_cd = 'BUDGET'
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;
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).amount1,
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).amount1,
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).amount2,
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).amount2,
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).amount3,
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).amount3,
p_currency_code => g_currency_code3
);
END IF; -- Insert only GL records
UPDATE pqh_budget_details
SET gl_status = ''
WHERE budget_detail_id = p_budget_detail_id;
UPDATE pqh_budget_details
SET gl_status = 'ERROR'
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
) 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_gl_interface';
SELECT COUNT(*)
FROM pqh_gl_interface
WHERE budget_version_id = g_budget_version_id
AND budget_detail_id = p_budget_detail_id
AND posting_type_cd = 'BUDGET'
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 NULL
AND posting_date IS NULL;
hr_utility.set_location('l_count in Insert pqh_gl_interface : '||l_count,10);
UPDATE pqh_gl_interface
-- ns since the record is new, the current amount is actual amount
-- no need to add to previous amount
-- SET AMOUNT_DR = NVL(AMOUNT_DR,0) + NVL(p_amount,0)
SET AMOUNT_DR = NVL(p_amount,0)
WHERE budget_version_id = g_budget_version_id
AND budget_detail_id = p_budget_detail_id
AND posting_type_cd = 'BUDGET'
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 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,
'BUDGET'
);
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
) 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 posting_type_cd = 'BUDGET'
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
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,
'BUDGET'
);
END IF; -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
END update_pqh_gl_interface;
This procedure will pick records from pqh_gl_interface table and insert them into
gl tables depending on the g_budgetary_control_flag
If we insert into gl_bc_packets do funds checking for each packet
*/
--
-- local variables
--
l_proc varchar2(72) := g_package||'.populate_gl_tables';
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 = 'BUDGET'
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);
Insert in gl_bc_packets and Call the GL funds checker
The GL funds checker program has COMMIT inside the program so we cannot rollback
The GL funds checker is only called when the validate flag is false i.e no validation
-- do funds checking for each packet
-- Mode = R (reserved) if amount is dr
-- Mode = U (unreserved) if amount is cr
-- Mode = C (Checking) if program is run in validate mode i.e g_validate = TRUE
-- Mode C is never called as there as explicit commits in GL funds checker program , so
-- we call the GL funds checker program only when p_validate is FALSE in R or U mode
------------------------------------------------------------------------------------------------
*/
-- Insert in gl_bc_packets and run funds checker
hr_utility.set_location('Calling ins_gl_bc_run_fund_check with fund checker Mode : '||l_fc_mode,100);
2. update all the budget_detail records which have this Period Name + CCID + currency code to ERROR ( gl_status)
3. Reverse unposted adjustment txns in pqh_gl_interface
4. Delete all unposted non-adjustment txns from pqh_gl_interface
-----------------------------------------------------------------------------------------------------
*/
IF NOT ( l_fc_success ) OR
( NVL(l_fc_return,'T') in ('T', 'F','R') ) THEN
-- fund checker failed
hr_utility.set_location('Fund Checker Failed ',120);
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,
budget_version_id,
accounting_date,
period_name,
code_combination_id,
chart_of_accounts_id,
entered_dr,
entered_cr,
reference1,
reference2)
VALUES
('NEW',
g_set_of_books_id,
g_user_je_source_name,
g_user_je_category_name,
l_currency_code,
sysdate,
8302,
'B',
g_gl_budget_version_id,
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),
g_budget_version_id,
l_cost_allocation_keyflex_id);
PROCEDURE update_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_proc varchar2(72) := g_package||'.update_gl_status';
SELECT *
FROM pqh_budget_details
WHERE budget_version_id = g_budget_version_id
AND NVL(gl_status,'X') <> 'ERROR'
FOR UPDATE OF gl_status;
SELECT COUNT(*)
FROM pqh_budget_details
WHERE budget_version_id = g_budget_version_id
AND NVL(gl_status,'ERROR') = 'ERROR';
UPDATE pqh_budget_details
SET gl_status = 'POST'
WHERE CURRENT OF csr_budget_details;
UPDATE pqh_budget_versions
SET gl_status = 'POST',
transfered_to_gl_flag = 'Y'
WHERE budget_version_id = g_budget_version_id;
UPDATE pqh_budget_versions
SET gl_status = 'ERROR',
transfered_to_gl_flag = 'Y'
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 = 'BUDGET'
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 = 'BUDGET'
AND posting_date IS NULL
AND status IS NULL;
END update_gl_status;
SELECT *
FROM pay_cost_allocation_keyflex
WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
SELECT *
FROM pqh_budget_gl_flex_maps
WHERE budget_id = g_budget_id ;
hr_utility.set_location('Built dynamic select ',26);
sql_stmt := 'SELECT * FROM gl_code_combinations WHERE chart_of_accounts_id = :g_chart_of_accounts_id and '||l_where_str ;
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;
SELECT (NVL(srcs.distribution_percentage,0) * .01) *
(NVL(elem.distribution_percentage,0) * .01) *
NVL(sets.budget_unit1_value,0)
FROM pqh_budget_fund_srcs srcs,
pqh_budget_elements elem,
pqh_budget_sets sets
WHERE srcs.budget_fund_src_id = p_budget_fund_src_id
AND elem.budget_element_id = srcs.budget_element_id
AND sets.budget_set_id = elem.budget_set_id;
SELECT (NVL(srcs.distribution_percentage,0) * .01) *
(NVL(elem.distribution_percentage,0) * .01) *
NVL(sets.budget_unit2_value,0)
FROM pqh_budget_fund_srcs srcs,
pqh_budget_elements elem,
pqh_budget_sets sets
WHERE srcs.budget_fund_src_id = p_budget_fund_src_id
AND elem.budget_element_id = srcs.budget_element_id
AND sets.budget_set_id = elem.budget_set_id;
SELECT (NVL(srcs.distribution_percentage,0) * .01) *
(NVL(elem.distribution_percentage,0) * .01) *
NVL(sets.budget_unit3_value,0)
FROM pqh_budget_fund_srcs srcs,
pqh_budget_elements elem,
pqh_budget_sets sets
WHERE srcs.budget_fund_src_id = p_budget_fund_src_id
AND elem.budget_element_id = srcs.budget_element_id
AND sets.budget_set_id = elem.budget_set_id;
SELECT *
FROM pqh_budget_details
WHERE budget_detail_id = p_budget_detail_id ;
SELECT *
FROM pqh_budget_periods
WHERE budget_period_id = p_budget_period_id ;
SELECT *
FROM per_time_periods
WHERE time_period_id = p_time_period_id ;
SELECT *
FROM pay_cost_allocation_keyflex
WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
This will update the g_status global with ERROR or SUCCESS
*/
--
-- local variables
--
l_proc varchar2(72) := g_package||'end_log';
SELECT COUNT(*)
FROM pqh_process_log
WHERE message_type_cd = p_message_type_cd
START WITH process_log_id = pqh_process_batch_log.g_master_process_log_id
CONNECT BY PRIOR process_log_id = master_process_log_id;
SELECT *
FROM pqh_process_log
WHERE process_log_id = pqh_process_batch_log.g_master_process_log_id;
update the 'start' record for this batch with message_type_cd = 'COMPLETE' and
update the batch_end_date with current date time
*/
OPEN csr_batch_rec;
UPDATE pqh_process_log
SET message_type_cd = 'COMPLETE',
message_text = fnd_message.get_string('PQH','PQH_PROCESS_COMPLETED'),
txn_table_route_id = g_table_route_id_bvr,
batch_status = l_status,
batch_end_date = sysdate
WHERE process_log_id = pqh_process_batch_log.g_master_process_log_id;
UPDATE pqh_process_log
SET batch_status = l_status,
batch_end_date = sysdate,
txn_table_route_id = g_table_route_id_bvr
WHERE process_log_id = pqh_process_batch_log.g_master_process_log_id;
UPDATE pqh_process_log
SET message_type_cd = 'ERROR',
message_text = p_message_text,
txn_table_route_id = g_table_route_id_bvr
-- batch_status = 'ERROR',
-- batch_end_date = sysdate
WHERE process_log_id = pqh_process_batch_log.g_master_process_log_id;
SELECT *
FROM pqh_budgets
WHERE budget_id = p_budget_id;
SELECT *
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_of_books_id;
SELECT *
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = p_id_flex_num
AND enabled_flag = 'Y'
AND display_flag = 'Y'
ORDER BY application_column_name;
1. update all the budget_detail records which have this Period Name + CCID + currency code to ERROR ( gl_status)
2. Reverse unposted adjustment txns in pqh_gl_interface
3. Delete all unposted non-adjustment txns from pqh_gl_interface
Note : If a budget detail record has 4 periods and there was a error in 4th period , we have no control on the 1st three
as they have already been Approved by funs checker program and would have already been posted to GL.
*/
--
-- local variables
--
l_proc varchar2(72) := g_package||'reverse_budget_details';
SELECT *
FROM pqh_gl_interface
WHERE budget_version_id = g_budget_version_id
AND posting_type_cd = 'BUDGET'
AND period_name = p_period_name
AND currency_code = p_currency_code
AND code_combination_id = p_code_combination_id
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 posting_type_cd = 'BUDGET'
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 NVL(adjustment_flag,'N') = 'N'
AND status IS NOT NULL;
UPDATE pqh_budget_details
SET gl_status = 'ERROR'
WHERE budget_detail_id IN
(
SELECT distinct budget_detail_id
FROM pqh_gl_interface
WHERE budget_version_id = g_budget_version_id
AND posting_type_cd = 'BUDGET'
AND period_name = p_period_name
AND currency_code = p_currency_code
AND code_combination_id = p_code_combination_id
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 = 'BUDGET'
AND status IS NULL
AND posting_date IS NULL;
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 )
*/
--
-- local variables
--
l_proc varchar2(72) := g_package||'build_old_bdgt_dtls_tab';
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 = 'BUDGET'
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 ) ;
g_old_bdgt_dtls_tab which are not in g_period_amt_tab and update the reverse flag for those records to 'Y' so that
we can reverse those records
*/
--
-- local variables
--
l_proc varchar2(72) := g_package||'compare_old_bdgt_dtls_tab';
where reverse_flag is Y and update the posted record amount to 0
*/
--
-- local variables
--
l_proc varchar2(72) := g_package||'reverse_old_bdgt_dtls_tab';
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 = 'BUDGET'
AND NVL(adjustment_flag,'N') = 'N'
AND status IS NOT NULL
AND posting_date 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 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 posting_type_cd = 'BUDGET'
AND NVL(adjustment_flag,'N') = 'N'
AND status IS NOT NULL
AND posting_date IS NOT NULL
FOR UPDATE of amount_dr;
UPDATE pqh_gl_interface
SET amount_dr = 0
WHERE CURRENT OF csr_pqh_gl_interface;
hr_utility.set_location('Updated pqh_gl_interface ',15);
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,
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,
0,
NVL(l_pqh_gl_interface_rec.amount_dr,0),
g_old_bdgt_dtls_tab(i).currency_code,
null,
'Y',
null,
'BUDGET'
);
-- update the pqh_gl_interface table
UPDATE pqh_gl_interface
SET amount_dr = 0
WHERE CURRENT OF csr_pqh_gms_interface;
hr_utility.set_location('Updated pqh_gl_interface ',15);
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,
status,
adjustment_flag,
posting_date,
currency_code,
posting_type_cd
)
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).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),
null,
'Y',
null,
g_old_bdgt_dtls_tab(i).currency_code,
'BUDGET'
);
SELECT bg.ORG_INFORMATION10
FROM HR_ORGANIZATION_INFORMATION bg,
pqh_budgets bgt,
pqh_budget_versions bvr
WHERE bgt.budget_id = bvr.budget_id
AND bvr.budget_version_id = g_budget_version_id
AND bgt.business_group_id = bg.organization_id
AND bg.ORG_INFORMATION_CONTEXT = 'Business Group Information';
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;
SELECT cost_allocation_keyflex_id
FROM PAY_ALL_PAYROLLS_F pay,
FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV( 'sessionid')
AND PAY.EFFECTIVE_START_DATE <= ss.effective_date
AND PAY.EFFECTIVE_END_DATE >= ss.effective_date
AND pay.payroll_id =
(SELECT pos.pay_freq_payroll_id
FROM pqh_budget_details bdt,
hr_all_positions_f pos,
FND_SESSIONS SS
WHERE bdt.budget_detail_id = p_budget_detail_id
AND bdt.position_id = pos.position_id
AND SS.SESSION_ID = USERENV( 'sessionid')
AND POS.EFFECTIVE_START_DATE <= ss.effective_date
AND POS.EFFECTIVE_END_DATE >= ss.effective_date
);
SELECT *
FROM pay_cost_allocation_keyflex
WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
g_seg_val_tab.DELETE;
SELECT cost_allocation_keyflex_id
FROM pqh_budget_details bdt, pqh_budget_periods bpr,
pqh_budget_sets bst, pqh_budget_elements bel,
pay_element_links pel
WHERE bdt.budget_detail_id = bpr.budget_detail_id
AND bpr.budget_period_id = bst.budget_period_id
AND bst.budget_set_id = bel.budget_set_id
AND bdt.position_id = pel.position_id
AND bel.element_type_id = pel.element_type_id
AND bdt.budget_detail_id = p_budget_detail_id
AND bpr.budget_period_id = p_budget_period_id ;
SELECT *
FROM pay_cost_allocation_keyflex
WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
SELECT cost_allocation_keyflex_id
FROM pqh_budget_details bdt,
hr_all_organization_units org
WHERE bdt.budget_detail_id = p_budget_detail_id
AND bdt.organization_id = org.organization_id;
SELECT *
FROM pay_cost_allocation_keyflex
WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
We will also update the following :
Budget Version table :
For last Posted Version :
TRANSFERED_TO_GL_FLAG for the last posted version to 'N '
gl_status = 'UNPOST'
For the current budget_version :
After posting the current version we will update the TRANSFERED_TO_GL_FLAG to Y for the current version.
gl_status = POST or ERROR
Budget Detail table :
For last Posted Version :
gl_status = NULL ( for all records )
For the current budget_version :
handled by the current posting logic, will be set to POST or ERROR
*/
--
-- local variables
--
l_proc varchar2(72) := g_package||' .reverse_prev_posted_version';
SELECT budget_version_id
FROM pqh_budget_versions
WHERE budget_id = g_budget_id
AND NVL(transfered_to_gl_flag,'N') = 'Y';
SELECT *
FROM pqh_gl_interface
WHERE budget_version_id = p_budget_version_id
AND NVL(adjustment_flag,'N') = 'N'
AND posting_type_cd = 'BUDGET'
AND status IS NOT NULL
AND posting_date IS NOT NULL
AND (NVL(amount_dr,0) > 0 OR NVL(amount_cr,0) > 0 )
FOR UPDATE of amount_dr;
UPDATE pqh_gl_interface
SET amount_dr = 0
WHERE CURRENT OF csr_unpost_version;
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_date,
posting_type_cd
)
VALUES
(
pqh_gl_interface_s.nextval,
g_last_posted_ver,
l_pqh_gl_interface_rec.budget_detail_id,
l_pqh_gl_interface_rec.period_name,
l_pqh_gl_interface_rec.accounting_date,
l_pqh_gl_interface_rec.code_combination_id,
l_pqh_gl_interface_rec.cost_allocation_keyflex_id,
l_pqh_gl_interface_rec.project_id,
l_pqh_gl_interface_rec.task_id,
l_pqh_gl_interface_rec.award_id,
l_pqh_gl_interface_rec.expenditure_type,
l_pqh_gl_interface_rec.organization_id,
0,
NVL(l_pqh_gl_interface_rec.amount_dr,0),
l_pqh_gl_interface_rec.currency_code,
null,
'Y',
null,
'BUDGET'
);
UPDATE pqh_budget_versions
SET transfered_to_gl_flag = 'N' ,
gl_status = 'UNPOST'
WHERE budget_version_id = g_last_posted_ver;
UPDATE pqh_budget_details
SET gl_status = ''
WHERE budget_version_id = g_last_posted_ver;
SELECT *
FROM pqh_gl_interface
WHERE budget_version_id = p_budget_version_id
AND NVL(adjustment_flag,'N') = 'N'
AND posting_type_cd = 'COMMITMENT'
AND status IS NOT NULL
AND posting_date IS NOT NULL
AND (NVL(amount_dr,0) > 0 OR NVL(amount_cr,0) > 0 )
FOR UPDATE of amount_dr;
UPDATE pqh_gl_interface
SET amount_dr = 0
WHERE CURRENT OF csr_unpost_version;
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_date,
posting_type_cd
)
VALUES
(
pqh_gl_interface_s.nextval,
g_last_posted_ver,
l_pqh_gl_interface_rec.budget_detail_id,
l_pqh_gl_interface_rec.period_name,
l_pqh_gl_interface_rec.accounting_date,
l_pqh_gl_interface_rec.code_combination_id,
l_pqh_gl_interface_rec.cost_allocation_keyflex_id,
l_pqh_gl_interface_rec.project_id,
l_pqh_gl_interface_rec.task_id,
l_pqh_gl_interface_rec.award_id,
l_pqh_gl_interface_rec.expenditure_type,
l_pqh_gl_interface_rec.organization_id,
0,
NVL(l_pqh_gl_interface_rec.amount_dr,0),
l_pqh_gl_interface_rec.currency_code,
null,
'Y',
null,
'BUDGET'
);
UPDATE pqh_budget_versions
SET commitment_gl_status = 'UNPOST'
WHERE budget_version_id = g_last_posted_ver;
UPDATE pqh_budget_details
SET commitment_gl_status = 'UNPOST'
WHERE budget_version_id = g_last_posted_ver;
SELECT bfs.budget_fund_src_id
FROM pqh_budget_details bdt, pqh_budget_periods bpr, pqh_budget_sets bst, pqh_budget_elements bel, pqh_budget_fund_srcs bfs
WHERE bdt.budget_version_id = p_budget_version_id
and bdt.budget_detail_id = bpr.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;
Select position_id
From
pqh_budget_details
where budget_detail_id = p_budget_detail_id;
Select organization_id
From
pqh_budget_details
where budget_detail_id = p_budget_detail_id;
Select grade_id
From
pqh_budget_details
where budget_detail_id = p_budget_detail_id;
Select job_id
From
pqh_budget_details
where budget_detail_id = p_budget_detail_id;
Select
assignment_id,
effective_start_date,
effective_end_date
From per_all_assignments_f
Where position_id=l_entity_id and
(
(effective_start_date <=start_date AND effective_end_date >start_date)
OR
(effective_start_date <=end_date AND effective_end_date >end_date)
);
Select
assignment_id,
effective_start_date,
effective_end_date
From per_all_assignments_f
Where organization_id=l_entity_id and
(
(effective_start_date <=start_date AND effective_end_date >start_date)
OR
(effective_start_date <=end_date AND effective_end_date >end_date)
);
Select
assignment_id,
effective_start_date,
effective_end_date
From per_all_assignments_f
Where grade_id=l_entity_id and
(
(effective_start_date <=start_date AND effective_end_date >start_date)
OR
(effective_start_date <=end_date AND effective_end_date >end_date)
);
Select
assignment_id,
effective_start_date,
effective_end_date
From per_all_assignments_f
Where job_id=l_entity_id and
(
(effective_start_date <=start_date AND effective_end_date >start_date)
OR
(effective_start_date <=end_date AND effective_end_date >end_date)
);
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
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;
Select budget_version_id
From pqh_budget_versions
Where budget_id = g_budget_id
AND NVL(transfered_to_gl_flag,'N') = 'Y';
DELETE from pqh_gms_excess
WHERE budget_period_id in (Select budget_period_id
From
pqh_budget_periods bpr
,pqh_budget_details bdt
Where bpr.budget_detail_id = bdt.budget_detail_id AND
bdt.budget_version_id=g_budget_version_id);
Select amount
From pqh_gms_excess
Where
budget_period_id =g_period_amt_tab(inx).period_id
AND project_id =g_period_amt_tab(inx).project_id
AND task_id =g_period_amt_tab(inx).task_id
AND award_id =g_period_amt_tab(inx).award_id
AND expenditure_type =g_period_amt_tab(inx).expenditure_type
AND organization_id =g_period_amt_tab(inx).organization_id;
DELETE FROM PQH_GMS_EXCESS
WHERE budget_period_id =g_period_amt_tab(p_inx).period_id
AND project_id =g_period_amt_tab(p_inx).project_id
AND task_id =g_period_amt_tab(p_inx).task_id
AND award_id =g_period_amt_tab(p_inx).award_id
AND expenditure_type =g_period_amt_tab(p_inx).expenditure_type
AND organization_id =g_period_amt_tab(p_inx).organization_id;
UPDATE PQH_GMS_EXCESS
SET amount = l_ptaeo_excess
WHERE budget_period_id =g_period_amt_tab(p_inx).period_id
AND project_id =g_period_amt_tab(p_inx).project_id
AND task_id =g_period_amt_tab(p_inx).task_id
AND award_id =g_period_amt_tab(p_inx).award_id
AND expenditure_type =g_period_amt_tab(p_inx).expenditure_type
AND organization_id =g_period_amt_tab(p_inx).organization_id;
INSERT into pqh_gms_excess
( GMS_EXCESS_ID
,BUDGET_PERIOD_ID
,PROJECT_ID
,TASK_ID
,AWARD_ID
,EXPENDITURE_TYPE
,ORGANIZATION_ID
,AMOUNT
)
VALUES
(
pqh_gms_excess_s.nextval
,g_period_amt_tab(p_inx).period_id
,g_period_amt_tab(p_inx).project_id
,g_period_amt_tab(p_inx).task_id
,g_period_amt_tab(p_inx).award_id
,g_period_amt_tab(p_inx).expenditure_type
,g_period_amt_tab(p_inx).organization_id
,-l_ptaeo_amt
);
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
*/
--
-- local variables
--
l_proc varchar2(72) := g_package||'.populate_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 period_name = p_period_name
AND posting_type_cd = 'BUDGET'
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;
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
);
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
);
END IF; -- Insert only GMS records
UPDATE pqh_budget_details
SET gl_status = ''
WHERE budget_detail_id = p_budget_detail_id;
UPDATE pqh_budget_details
SET gl_status = 'ERROR'
WHERE budget_detail_id = p_budget_detail_id;
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
) 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 = 'BUDGET'
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;
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 = 'BUDGET'
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,
'BUDGET'
);
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
) 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 = 'BUDGET'
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
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,
'BUDGET'
);
END IF; -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
END update_pqh_gms_interface;
SELECT substr(l.description, 1, 240 ) meaning
FROM pa_lookups l
WHERE l.lookup_type in ('TRANSACTION REJECTION REASON','FC_RESULT_CODE',
'COST DIST REJECTION CODE','INVOICE_CURRENCY',
'TRANSACTION USER REJ REASON')
AND l.lookup_code = p_rejection_code
UNION all
SELECT message_text
FROM fnd_new_messages fnd
WHERE language_code = userenv('lang')
AND fnd.message_name = p_rejection_code
AND application_id = 275; -- PA
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 = 'GMSEPQHBC'
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 = 'GMSEPQHBC'
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 = 'GMSEPQHBC';
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 = 'GMSEPQHBC';
hr_utility.set_location('Deleted gms_transaction_interface_all:',30);
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
);
Select business_group_id
From pqh_budgets
Where budget_id=g_budget_id;
Select transaction_source
From pa_transaction_sources
Where transaction_source = 'GMSEPQHBC';
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 = 'BUDGET'
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
);
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';
select s.audsid, s.serial#
into l_session_id, l_serial_id
from v$session s, v$process p
where s.paddr = p.addr
and s.audsid = USERENV('SESSIONID');
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,
budget_version_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
reference1,
reference2,
-- Added these three columns for Bug 6769905 fix, only for R12
SESSION_ID,
SERIAL_ID,
APPLICATION_ID
)
VALUES
(p_packet_id,
g_set_of_books_id,
g_user_je_source_name,
g_user_je_category_name,
p_code_combination_id,
'B',
p_period_name,
p_period_year,
p_period_num,
p_quarter_num,
p_currency_code,
'P',
sysdate,
8302,
g_gl_budget_version_id,
p_entered_dr,
p_entered_cr,
p_accounted_dr,
p_accounted_cr,
g_budget_version_id,
p_cost_allocation_keyflex_id,
-- Added these three columns for Bug 6769905 fix, only for R12
l_session_id,
l_serial_id,
l_application_id);