[Home] [Help]
9: --
10: g_application_id NUMBER(15) := 101;
11: --
12: g_budget_id pqh_budgets.budget_id%TYPE;
13: g_set_of_books_id gl_interface.set_of_books_id%TYPE;
14: g_budgetary_control_flag gl_sets_of_books.enable_budgetary_control_flag%TYPE;
15: g_budget_name pqh_budgets.budget_name%TYPE;
16: g_budgeted_entity_cd pqh_budgets.budgeted_entity_cd%TYPE;
17: g_transfer_to_grants_flag pqh_budgets.transfer_to_grants_flag%TYPE;
16: g_budgeted_entity_cd pqh_budgets.budgeted_entity_cd%TYPE;
17: g_transfer_to_grants_flag pqh_budgets.transfer_to_grants_flag%TYPE;
18: g_bgt_currency_code pqh_budgets.currency_code%TYPE;
19: --
20: g_user_je_source_name gl_interface.user_je_source_name%TYPE;
21: g_user_je_category_name gl_interface.user_je_category_name%TYPE;
22: --
23: g_budget_version_id gl_interface.budget_version_id%TYPE;
24: g_gl_budget_version_id gl_interface.budget_version_id%TYPE;
17: g_transfer_to_grants_flag pqh_budgets.transfer_to_grants_flag%TYPE;
18: g_bgt_currency_code pqh_budgets.currency_code%TYPE;
19: --
20: g_user_je_source_name gl_interface.user_je_source_name%TYPE;
21: g_user_je_category_name gl_interface.user_je_category_name%TYPE;
22: --
23: g_budget_version_id gl_interface.budget_version_id%TYPE;
24: g_gl_budget_version_id gl_interface.budget_version_id%TYPE;
25: g_version_number pqh_budget_versions.version_number%TYPE;
19: --
20: g_user_je_source_name gl_interface.user_je_source_name%TYPE;
21: g_user_je_category_name gl_interface.user_je_category_name%TYPE;
22: --
23: g_budget_version_id gl_interface.budget_version_id%TYPE;
24: g_gl_budget_version_id gl_interface.budget_version_id%TYPE;
25: g_version_number pqh_budget_versions.version_number%TYPE;
26: g_last_posted_ver gl_interface.budget_version_id%TYPE;
27: --
20: g_user_je_source_name gl_interface.user_je_source_name%TYPE;
21: g_user_je_category_name gl_interface.user_je_category_name%TYPE;
22: --
23: g_budget_version_id gl_interface.budget_version_id%TYPE;
24: g_gl_budget_version_id gl_interface.budget_version_id%TYPE;
25: g_version_number pqh_budget_versions.version_number%TYPE;
26: g_last_posted_ver gl_interface.budget_version_id%TYPE;
27: --
28: g_chart_of_accounts_id gl_interface.chart_of_accounts_id%TYPE;
22: --
23: g_budget_version_id gl_interface.budget_version_id%TYPE;
24: g_gl_budget_version_id gl_interface.budget_version_id%TYPE;
25: g_version_number pqh_budget_versions.version_number%TYPE;
26: g_last_posted_ver gl_interface.budget_version_id%TYPE;
27: --
28: g_chart_of_accounts_id gl_interface.chart_of_accounts_id%TYPE;
29: g_default_currency_code gl_interface.currency_code%TYPE;
30: g_currency_code1 gl_interface.currency_code%TYPE;
24: g_gl_budget_version_id gl_interface.budget_version_id%TYPE;
25: g_version_number pqh_budget_versions.version_number%TYPE;
26: g_last_posted_ver gl_interface.budget_version_id%TYPE;
27: --
28: g_chart_of_accounts_id gl_interface.chart_of_accounts_id%TYPE;
29: g_default_currency_code gl_interface.currency_code%TYPE;
30: g_currency_code1 gl_interface.currency_code%TYPE;
31: g_currency_code2 gl_interface.currency_code%TYPE;
32: g_currency_code3 gl_interface.currency_code%TYPE;
25: g_version_number pqh_budget_versions.version_number%TYPE;
26: g_last_posted_ver gl_interface.budget_version_id%TYPE;
27: --
28: g_chart_of_accounts_id gl_interface.chart_of_accounts_id%TYPE;
29: g_default_currency_code gl_interface.currency_code%TYPE;
30: g_currency_code1 gl_interface.currency_code%TYPE;
31: g_currency_code2 gl_interface.currency_code%TYPE;
32: g_currency_code3 gl_interface.currency_code%TYPE;
33: g_budget_uom1 pqh_budgets.budget_unit1_id%TYPE;
26: g_last_posted_ver gl_interface.budget_version_id%TYPE;
27: --
28: g_chart_of_accounts_id gl_interface.chart_of_accounts_id%TYPE;
29: g_default_currency_code gl_interface.currency_code%TYPE;
30: g_currency_code1 gl_interface.currency_code%TYPE;
31: g_currency_code2 gl_interface.currency_code%TYPE;
32: g_currency_code3 gl_interface.currency_code%TYPE;
33: g_budget_uom1 pqh_budgets.budget_unit1_id%TYPE;
34: g_budget_uom2 pqh_budgets.budget_unit2_id%TYPE;
27: --
28: g_chart_of_accounts_id gl_interface.chart_of_accounts_id%TYPE;
29: g_default_currency_code gl_interface.currency_code%TYPE;
30: g_currency_code1 gl_interface.currency_code%TYPE;
31: g_currency_code2 gl_interface.currency_code%TYPE;
32: g_currency_code3 gl_interface.currency_code%TYPE;
33: g_budget_uom1 pqh_budgets.budget_unit1_id%TYPE;
34: g_budget_uom2 pqh_budgets.budget_unit2_id%TYPE;
35: g_budget_uom3 pqh_budgets.budget_unit3_id%TYPE;
28: g_chart_of_accounts_id gl_interface.chart_of_accounts_id%TYPE;
29: g_default_currency_code gl_interface.currency_code%TYPE;
30: g_currency_code1 gl_interface.currency_code%TYPE;
31: g_currency_code2 gl_interface.currency_code%TYPE;
32: g_currency_code3 gl_interface.currency_code%TYPE;
33: g_budget_uom1 pqh_budgets.budget_unit1_id%TYPE;
34: g_budget_uom2 pqh_budgets.budget_unit2_id%TYPE;
35: g_budget_uom3 pqh_budgets.budget_unit3_id%TYPE;
36: --
56: PROCEDURE populate_pqh_gms_interface
57: (
58: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
59: p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
60: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
61: );
62:
63: PROCEDURE insert_pqh_gms_interface
64: (
61: );
62:
63: PROCEDURE insert_pqh_gms_interface
64: (
65: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
66: p_period_name IN varchar2,
67: p_project_id IN pqh_gl_interface.project_id%TYPE,
68: p_task_id IN pqh_gl_interface.task_id%TYPE,
69: p_award_id IN pqh_gl_interface.award_id%TYPE,
63: PROCEDURE insert_pqh_gms_interface
64: (
65: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
66: p_period_name IN varchar2,
67: p_project_id IN pqh_gl_interface.project_id%TYPE,
68: p_task_id IN pqh_gl_interface.task_id%TYPE,
69: p_award_id IN pqh_gl_interface.award_id%TYPE,
70: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
71: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
64: (
65: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
66: p_period_name IN varchar2,
67: p_project_id IN pqh_gl_interface.project_id%TYPE,
68: p_task_id IN pqh_gl_interface.task_id%TYPE,
69: p_award_id IN pqh_gl_interface.award_id%TYPE,
70: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
71: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
72: p_amount IN pqh_gl_interface.amount_dr%TYPE,
65: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
66: p_period_name IN varchar2,
67: p_project_id IN pqh_gl_interface.project_id%TYPE,
68: p_task_id IN pqh_gl_interface.task_id%TYPE,
69: p_award_id IN pqh_gl_interface.award_id%TYPE,
70: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
71: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
72: p_amount IN pqh_gl_interface.amount_dr%TYPE,
73: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
66: p_period_name IN varchar2,
67: p_project_id IN pqh_gl_interface.project_id%TYPE,
68: p_task_id IN pqh_gl_interface.task_id%TYPE,
69: p_award_id IN pqh_gl_interface.award_id%TYPE,
70: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
71: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
72: p_amount IN pqh_gl_interface.amount_dr%TYPE,
73: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
74: );
67: p_project_id IN pqh_gl_interface.project_id%TYPE,
68: p_task_id IN pqh_gl_interface.task_id%TYPE,
69: p_award_id IN pqh_gl_interface.award_id%TYPE,
70: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
71: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
72: p_amount IN pqh_gl_interface.amount_dr%TYPE,
73: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
74: );
75:
68: p_task_id IN pqh_gl_interface.task_id%TYPE,
69: p_award_id IN pqh_gl_interface.award_id%TYPE,
70: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
71: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
72: p_amount IN pqh_gl_interface.amount_dr%TYPE,
73: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
74: );
75:
76: PROCEDURE update_pqh_gms_interface
69: p_award_id IN pqh_gl_interface.award_id%TYPE,
70: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
71: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
72: p_amount IN pqh_gl_interface.amount_dr%TYPE,
73: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
74: );
75:
76: PROCEDURE update_pqh_gms_interface
77: (
74: );
75:
76: PROCEDURE update_pqh_gms_interface
77: (
78: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
79: p_period_name IN varchar2,
80: p_project_id IN pqh_gl_interface.project_id%TYPE,
81: p_task_id IN pqh_gl_interface.task_id%TYPE,
82: p_award_id IN pqh_gl_interface.award_id%TYPE,
76: PROCEDURE update_pqh_gms_interface
77: (
78: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
79: p_period_name IN varchar2,
80: p_project_id IN pqh_gl_interface.project_id%TYPE,
81: p_task_id IN pqh_gl_interface.task_id%TYPE,
82: p_award_id IN pqh_gl_interface.award_id%TYPE,
83: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
84: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
77: (
78: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
79: p_period_name IN varchar2,
80: p_project_id IN pqh_gl_interface.project_id%TYPE,
81: p_task_id IN pqh_gl_interface.task_id%TYPE,
82: p_award_id IN pqh_gl_interface.award_id%TYPE,
83: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
84: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
85: p_amount IN pqh_gl_interface.amount_dr%TYPE,
78: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
79: p_period_name IN varchar2,
80: p_project_id IN pqh_gl_interface.project_id%TYPE,
81: p_task_id IN pqh_gl_interface.task_id%TYPE,
82: p_award_id IN pqh_gl_interface.award_id%TYPE,
83: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
84: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
85: p_amount IN pqh_gl_interface.amount_dr%TYPE,
86: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
79: p_period_name IN varchar2,
80: p_project_id IN pqh_gl_interface.project_id%TYPE,
81: p_task_id IN pqh_gl_interface.task_id%TYPE,
82: p_award_id IN pqh_gl_interface.award_id%TYPE,
83: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
84: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
85: p_amount IN pqh_gl_interface.amount_dr%TYPE,
86: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
87: ) ;
80: p_project_id IN pqh_gl_interface.project_id%TYPE,
81: p_task_id IN pqh_gl_interface.task_id%TYPE,
82: p_award_id IN pqh_gl_interface.award_id%TYPE,
83: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
84: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
85: p_amount IN pqh_gl_interface.amount_dr%TYPE,
86: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
87: ) ;
88:
81: p_task_id IN pqh_gl_interface.task_id%TYPE,
82: p_award_id IN pqh_gl_interface.award_id%TYPE,
83: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
84: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
85: p_amount IN pqh_gl_interface.amount_dr%TYPE,
86: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
87: ) ;
88:
89: -- Procedure added to run funds checker in autonomous transaction
82: p_award_id IN pqh_gl_interface.award_id%TYPE,
83: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
84: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
85: p_amount IN pqh_gl_interface.amount_dr%TYPE,
86: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
87: ) ;
88:
89: -- Procedure added to run funds checker in autonomous transaction
90: PROCEDURE ins_gl_bc_run_fund_check
88:
89: -- Procedure added to run funds checker in autonomous transaction
90: PROCEDURE ins_gl_bc_run_fund_check
91: ( p_packet_id IN gl_bc_packets.packet_id%TYPE
92: ,p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE
93: ,p_period_name IN pqh_gl_interface.period_name%TYPE
94: ,p_period_year IN gl_period_statuses.period_year%TYPE
95: ,p_period_num IN gl_period_statuses.period_num%TYPE
96: ,p_quarter_num IN gl_period_statuses.quarter_num%TYPE
89: -- Procedure added to run funds checker in autonomous transaction
90: PROCEDURE ins_gl_bc_run_fund_check
91: ( p_packet_id IN gl_bc_packets.packet_id%TYPE
92: ,p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE
93: ,p_period_name IN pqh_gl_interface.period_name%TYPE
94: ,p_period_year IN gl_period_statuses.period_year%TYPE
95: ,p_period_num IN gl_period_statuses.period_num%TYPE
96: ,p_quarter_num IN gl_period_statuses.quarter_num%TYPE
97: ,p_currency_code IN pqh_gl_interface.currency_code%TYPE
93: ,p_period_name IN pqh_gl_interface.period_name%TYPE
94: ,p_period_year IN gl_period_statuses.period_year%TYPE
95: ,p_period_num IN gl_period_statuses.period_num%TYPE
96: ,p_quarter_num IN gl_period_statuses.quarter_num%TYPE
97: ,p_currency_code IN pqh_gl_interface.currency_code%TYPE
98: ,p_entered_dr IN pqh_gl_interface.amount_dr%TYPE
99: ,p_entered_cr IN pqh_gl_interface.amount_cr%TYPE
100: ,p_accounted_dr IN pqh_gl_interface.amount_dr%TYPE
101: ,p_accounted_cr IN pqh_gl_interface.amount_cr%TYPE
94: ,p_period_year IN gl_period_statuses.period_year%TYPE
95: ,p_period_num IN gl_period_statuses.period_num%TYPE
96: ,p_quarter_num IN gl_period_statuses.quarter_num%TYPE
97: ,p_currency_code IN pqh_gl_interface.currency_code%TYPE
98: ,p_entered_dr IN pqh_gl_interface.amount_dr%TYPE
99: ,p_entered_cr IN pqh_gl_interface.amount_cr%TYPE
100: ,p_accounted_dr IN pqh_gl_interface.amount_dr%TYPE
101: ,p_accounted_cr IN pqh_gl_interface.amount_cr%TYPE
102: ,p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE
95: ,p_period_num IN gl_period_statuses.period_num%TYPE
96: ,p_quarter_num IN gl_period_statuses.quarter_num%TYPE
97: ,p_currency_code IN pqh_gl_interface.currency_code%TYPE
98: ,p_entered_dr IN pqh_gl_interface.amount_dr%TYPE
99: ,p_entered_cr IN pqh_gl_interface.amount_cr%TYPE
100: ,p_accounted_dr IN pqh_gl_interface.amount_dr%TYPE
101: ,p_accounted_cr IN pqh_gl_interface.amount_cr%TYPE
102: ,p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE
103: ,p_fc_mode IN varchar2
96: ,p_quarter_num IN gl_period_statuses.quarter_num%TYPE
97: ,p_currency_code IN pqh_gl_interface.currency_code%TYPE
98: ,p_entered_dr IN pqh_gl_interface.amount_dr%TYPE
99: ,p_entered_cr IN pqh_gl_interface.amount_cr%TYPE
100: ,p_accounted_dr IN pqh_gl_interface.amount_dr%TYPE
101: ,p_accounted_cr IN pqh_gl_interface.amount_cr%TYPE
102: ,p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE
103: ,p_fc_mode IN varchar2
104: ,p_fc_success OUT NOCOPY boolean
97: ,p_currency_code IN pqh_gl_interface.currency_code%TYPE
98: ,p_entered_dr IN pqh_gl_interface.amount_dr%TYPE
99: ,p_entered_cr IN pqh_gl_interface.amount_cr%TYPE
100: ,p_accounted_dr IN pqh_gl_interface.amount_dr%TYPE
101: ,p_accounted_cr IN pqh_gl_interface.amount_cr%TYPE
102: ,p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE
103: ,p_fc_mode IN varchar2
104: ,p_fc_success OUT NOCOPY boolean
105: ,p_fc_return OUT NOCOPY varchar2
98: ,p_entered_dr IN pqh_gl_interface.amount_dr%TYPE
99: ,p_entered_cr IN pqh_gl_interface.amount_cr%TYPE
100: ,p_accounted_dr IN pqh_gl_interface.amount_dr%TYPE
101: ,p_accounted_cr IN pqh_gl_interface.amount_cr%TYPE
102: ,p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE
103: ,p_fc_mode IN varchar2
104: ,p_fc_success OUT NOCOPY boolean
105: ,p_fc_return OUT NOCOPY varchar2
106: );
191: --
192: --
193: PROCEDURE get_default_currency
194: (p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
195: p_default_currency_code OUT NOCOPY gl_interface.currency_code%TYPE) IS
196: --
197: -- local variables
198: --
199: l_bg_curr_code varchar2(150) := '';
322: l_gl_encumbrance_type_id gl_encumbrance_types.encumbrance_type_id%TYPE;
323: --
324: l_transfer_to_gl_flag pqh_budgets.transfer_to_gl_flag%TYPE;
325: l_psb_budget_flag pqh_budgets.psb_budget_flag%TYPE;
326: l_default_currency_code gl_interface.currency_code%TYPE;
327: --
328: l_message_text pqh_process_log.message_text%TYPE;
329: l_message_text_out fnd_new_messages.message_text%TYPE;
330: l_error_flag varchar2(10) := 'N';
2005: -- The foll procedure reads the global table g_period_amt_tab and
2006: -- fetches the period_name and code_combination_id corresponding to the
2007: -- period_id and cost_allocation_keyflex_id.If it does not find a period_name
2008: -- or a code_combination_id then it will populate the global variable
2009: -- g_detail_error to Y and we will not populate the pqh_gl_interface
2010: -- table for the current budget_detail_id.
2011: --
2012: l_gl_period_statuses_rec gl_period_statuses%ROWTYPE;
2013: l_period_name gl_period_statuses.period_name%TYPE;
2191: END update_period_commitment_tab;
2192: --
2193: --
2194: /************************************************************************************************************
2195: This procedure will check if the current budget_detail_id was already posted (exists in pqh_gl_interface)
2196: If Yes, it would build a pl/sql table with all records which have this current budget_detail_id.
2197: This is done as the user might have changed the records with current budget_detail_id which were previously
2198: posted and not present in new records. For those records we need to unpost i.e reverse the transactions.
2199:
2218: ) IS
2219: --
2220: -- local variables
2221: --
2222: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
2223: i BINARY_INTEGER :=1;
2224: --
2225: --
2226: CURSOR csr_old_bdgt_dtls_rec IS
2224: --
2225: --
2226: CURSOR csr_old_bdgt_dtls_rec IS
2227: SELECT *
2228: FROM pqh_gl_interface
2229: WHERE budget_version_id = g_budget_version_id
2230: AND budget_detail_id = p_budget_detail_id
2231: AND posting_type_cd = p_posting_type_cd
2232: AND NVL(adjustment_flag,'N') = 'N'
2249: OPEN csr_old_bdgt_dtls_rec;
2250: --
2251: LOOP
2252: --
2253: FETCH csr_old_bdgt_dtls_rec INTO l_pqh_gl_interface_rec;
2254: EXIT WHEN csr_old_bdgt_dtls_rec%NOTFOUND;
2255: --
2256: g_old_bdgt_dtls_tab(i).budget_version_id := g_budget_version_id;
2257: g_old_bdgt_dtls_tab(i).budget_detail_id := p_budget_detail_id;
2254: EXIT WHEN csr_old_bdgt_dtls_rec%NOTFOUND;
2255: --
2256: g_old_bdgt_dtls_tab(i).budget_version_id := g_budget_version_id;
2257: g_old_bdgt_dtls_tab(i).budget_detail_id := p_budget_detail_id;
2258: g_old_bdgt_dtls_tab(i).period_name := l_pqh_gl_interface_rec.period_name;
2259: g_old_bdgt_dtls_tab(i).accounting_date := l_pqh_gl_interface_rec.accounting_date;
2260: g_old_bdgt_dtls_tab(i).cost_allocation_keyflex_id := l_pqh_gl_interface_rec.cost_allocation_keyflex_id;
2261: g_old_bdgt_dtls_tab(i).code_combination_id := l_pqh_gl_interface_rec.code_combination_id;
2262: g_old_bdgt_dtls_tab(i).project_id := l_pqh_gl_interface_rec.project_id;
2255: --
2256: g_old_bdgt_dtls_tab(i).budget_version_id := g_budget_version_id;
2257: g_old_bdgt_dtls_tab(i).budget_detail_id := p_budget_detail_id;
2258: g_old_bdgt_dtls_tab(i).period_name := l_pqh_gl_interface_rec.period_name;
2259: g_old_bdgt_dtls_tab(i).accounting_date := l_pqh_gl_interface_rec.accounting_date;
2260: g_old_bdgt_dtls_tab(i).cost_allocation_keyflex_id := l_pqh_gl_interface_rec.cost_allocation_keyflex_id;
2261: g_old_bdgt_dtls_tab(i).code_combination_id := l_pqh_gl_interface_rec.code_combination_id;
2262: g_old_bdgt_dtls_tab(i).project_id := l_pqh_gl_interface_rec.project_id;
2263: g_old_bdgt_dtls_tab(i).task_id := l_pqh_gl_interface_rec.task_id;
2256: g_old_bdgt_dtls_tab(i).budget_version_id := g_budget_version_id;
2257: g_old_bdgt_dtls_tab(i).budget_detail_id := p_budget_detail_id;
2258: g_old_bdgt_dtls_tab(i).period_name := l_pqh_gl_interface_rec.period_name;
2259: g_old_bdgt_dtls_tab(i).accounting_date := l_pqh_gl_interface_rec.accounting_date;
2260: g_old_bdgt_dtls_tab(i).cost_allocation_keyflex_id := l_pqh_gl_interface_rec.cost_allocation_keyflex_id;
2261: g_old_bdgt_dtls_tab(i).code_combination_id := l_pqh_gl_interface_rec.code_combination_id;
2262: g_old_bdgt_dtls_tab(i).project_id := l_pqh_gl_interface_rec.project_id;
2263: g_old_bdgt_dtls_tab(i).task_id := l_pqh_gl_interface_rec.task_id;
2264: g_old_bdgt_dtls_tab(i).award_id := l_pqh_gl_interface_rec.award_id ;
2257: g_old_bdgt_dtls_tab(i).budget_detail_id := p_budget_detail_id;
2258: g_old_bdgt_dtls_tab(i).period_name := l_pqh_gl_interface_rec.period_name;
2259: g_old_bdgt_dtls_tab(i).accounting_date := l_pqh_gl_interface_rec.accounting_date;
2260: g_old_bdgt_dtls_tab(i).cost_allocation_keyflex_id := l_pqh_gl_interface_rec.cost_allocation_keyflex_id;
2261: g_old_bdgt_dtls_tab(i).code_combination_id := l_pqh_gl_interface_rec.code_combination_id;
2262: g_old_bdgt_dtls_tab(i).project_id := l_pqh_gl_interface_rec.project_id;
2263: g_old_bdgt_dtls_tab(i).task_id := l_pqh_gl_interface_rec.task_id;
2264: g_old_bdgt_dtls_tab(i).award_id := l_pqh_gl_interface_rec.award_id ;
2265: g_old_bdgt_dtls_tab(i).expenditure_type := l_pqh_gl_interface_rec.expenditure_type;
2258: g_old_bdgt_dtls_tab(i).period_name := l_pqh_gl_interface_rec.period_name;
2259: g_old_bdgt_dtls_tab(i).accounting_date := l_pqh_gl_interface_rec.accounting_date;
2260: g_old_bdgt_dtls_tab(i).cost_allocation_keyflex_id := l_pqh_gl_interface_rec.cost_allocation_keyflex_id;
2261: g_old_bdgt_dtls_tab(i).code_combination_id := l_pqh_gl_interface_rec.code_combination_id;
2262: g_old_bdgt_dtls_tab(i).project_id := l_pqh_gl_interface_rec.project_id;
2263: g_old_bdgt_dtls_tab(i).task_id := l_pqh_gl_interface_rec.task_id;
2264: g_old_bdgt_dtls_tab(i).award_id := l_pqh_gl_interface_rec.award_id ;
2265: g_old_bdgt_dtls_tab(i).expenditure_type := l_pqh_gl_interface_rec.expenditure_type;
2266: g_old_bdgt_dtls_tab(i).organization_id := l_pqh_gl_interface_rec.organization_id;
2259: g_old_bdgt_dtls_tab(i).accounting_date := l_pqh_gl_interface_rec.accounting_date;
2260: g_old_bdgt_dtls_tab(i).cost_allocation_keyflex_id := l_pqh_gl_interface_rec.cost_allocation_keyflex_id;
2261: g_old_bdgt_dtls_tab(i).code_combination_id := l_pqh_gl_interface_rec.code_combination_id;
2262: g_old_bdgt_dtls_tab(i).project_id := l_pqh_gl_interface_rec.project_id;
2263: g_old_bdgt_dtls_tab(i).task_id := l_pqh_gl_interface_rec.task_id;
2264: g_old_bdgt_dtls_tab(i).award_id := l_pqh_gl_interface_rec.award_id ;
2265: g_old_bdgt_dtls_tab(i).expenditure_type := l_pqh_gl_interface_rec.expenditure_type;
2266: g_old_bdgt_dtls_tab(i).organization_id := l_pqh_gl_interface_rec.organization_id;
2267: g_old_bdgt_dtls_tab(i).currency_code := l_pqh_gl_interface_rec.currency_code;
2260: g_old_bdgt_dtls_tab(i).cost_allocation_keyflex_id := l_pqh_gl_interface_rec.cost_allocation_keyflex_id;
2261: g_old_bdgt_dtls_tab(i).code_combination_id := l_pqh_gl_interface_rec.code_combination_id;
2262: g_old_bdgt_dtls_tab(i).project_id := l_pqh_gl_interface_rec.project_id;
2263: g_old_bdgt_dtls_tab(i).task_id := l_pqh_gl_interface_rec.task_id;
2264: g_old_bdgt_dtls_tab(i).award_id := l_pqh_gl_interface_rec.award_id ;
2265: g_old_bdgt_dtls_tab(i).expenditure_type := l_pqh_gl_interface_rec.expenditure_type;
2266: g_old_bdgt_dtls_tab(i).organization_id := l_pqh_gl_interface_rec.organization_id;
2267: g_old_bdgt_dtls_tab(i).currency_code := l_pqh_gl_interface_rec.currency_code;
2268: g_old_bdgt_dtls_tab(i).amount_dr := l_pqh_gl_interface_rec.amount_dr;
2261: g_old_bdgt_dtls_tab(i).code_combination_id := l_pqh_gl_interface_rec.code_combination_id;
2262: g_old_bdgt_dtls_tab(i).project_id := l_pqh_gl_interface_rec.project_id;
2263: g_old_bdgt_dtls_tab(i).task_id := l_pqh_gl_interface_rec.task_id;
2264: g_old_bdgt_dtls_tab(i).award_id := l_pqh_gl_interface_rec.award_id ;
2265: g_old_bdgt_dtls_tab(i).expenditure_type := l_pqh_gl_interface_rec.expenditure_type;
2266: g_old_bdgt_dtls_tab(i).organization_id := l_pqh_gl_interface_rec.organization_id;
2267: g_old_bdgt_dtls_tab(i).currency_code := l_pqh_gl_interface_rec.currency_code;
2268: g_old_bdgt_dtls_tab(i).amount_dr := l_pqh_gl_interface_rec.amount_dr;
2269: g_old_bdgt_dtls_tab(i).amount_cr := l_pqh_gl_interface_rec.amount_cr;
2262: g_old_bdgt_dtls_tab(i).project_id := l_pqh_gl_interface_rec.project_id;
2263: g_old_bdgt_dtls_tab(i).task_id := l_pqh_gl_interface_rec.task_id;
2264: g_old_bdgt_dtls_tab(i).award_id := l_pqh_gl_interface_rec.award_id ;
2265: g_old_bdgt_dtls_tab(i).expenditure_type := l_pqh_gl_interface_rec.expenditure_type;
2266: g_old_bdgt_dtls_tab(i).organization_id := l_pqh_gl_interface_rec.organization_id;
2267: g_old_bdgt_dtls_tab(i).currency_code := l_pqh_gl_interface_rec.currency_code;
2268: g_old_bdgt_dtls_tab(i).amount_dr := l_pqh_gl_interface_rec.amount_dr;
2269: g_old_bdgt_dtls_tab(i).amount_cr := l_pqh_gl_interface_rec.amount_cr;
2270: g_old_bdgt_dtls_tab(i).reverse_flag := 'Y';
2263: g_old_bdgt_dtls_tab(i).task_id := l_pqh_gl_interface_rec.task_id;
2264: g_old_bdgt_dtls_tab(i).award_id := l_pqh_gl_interface_rec.award_id ;
2265: g_old_bdgt_dtls_tab(i).expenditure_type := l_pqh_gl_interface_rec.expenditure_type;
2266: g_old_bdgt_dtls_tab(i).organization_id := l_pqh_gl_interface_rec.organization_id;
2267: g_old_bdgt_dtls_tab(i).currency_code := l_pqh_gl_interface_rec.currency_code;
2268: g_old_bdgt_dtls_tab(i).amount_dr := l_pqh_gl_interface_rec.amount_dr;
2269: g_old_bdgt_dtls_tab(i).amount_cr := l_pqh_gl_interface_rec.amount_cr;
2270: g_old_bdgt_dtls_tab(i).reverse_flag := 'Y';
2271: --
2264: g_old_bdgt_dtls_tab(i).award_id := l_pqh_gl_interface_rec.award_id ;
2265: g_old_bdgt_dtls_tab(i).expenditure_type := l_pqh_gl_interface_rec.expenditure_type;
2266: g_old_bdgt_dtls_tab(i).organization_id := l_pqh_gl_interface_rec.organization_id;
2267: g_old_bdgt_dtls_tab(i).currency_code := l_pqh_gl_interface_rec.currency_code;
2268: g_old_bdgt_dtls_tab(i).amount_dr := l_pqh_gl_interface_rec.amount_dr;
2269: g_old_bdgt_dtls_tab(i).amount_cr := l_pqh_gl_interface_rec.amount_cr;
2270: g_old_bdgt_dtls_tab(i).reverse_flag := 'Y';
2271: --
2272: i := i + 1;
2265: g_old_bdgt_dtls_tab(i).expenditure_type := l_pqh_gl_interface_rec.expenditure_type;
2266: g_old_bdgt_dtls_tab(i).organization_id := l_pqh_gl_interface_rec.organization_id;
2267: g_old_bdgt_dtls_tab(i).currency_code := l_pqh_gl_interface_rec.currency_code;
2268: g_old_bdgt_dtls_tab(i).amount_dr := l_pqh_gl_interface_rec.amount_dr;
2269: g_old_bdgt_dtls_tab(i).amount_cr := l_pqh_gl_interface_rec.amount_cr;
2270: g_old_bdgt_dtls_tab(i).reverse_flag := 'Y';
2271: --
2272: i := i + 1;
2273: --
2394: p_posting_type_cd IN varchar2) IS
2395: --
2396: -- local variables
2397: --
2398: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
2399: --
2400: CURSOR csr_pqh_gl_interface(p_period_name IN varchar2,
2401: p_code_combination_id IN number,
2402: p_currency_code IN varchar2) IS
2396: -- local variables
2397: --
2398: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
2399: --
2400: CURSOR csr_pqh_gl_interface(p_period_name IN varchar2,
2401: p_code_combination_id IN number,
2402: p_currency_code IN varchar2) IS
2403: SELECT *
2404: FROM pqh_gl_interface
2400: CURSOR csr_pqh_gl_interface(p_period_name IN varchar2,
2401: p_code_combination_id IN number,
2402: p_currency_code IN varchar2) IS
2403: SELECT *
2404: FROM pqh_gl_interface
2405: WHERE budget_version_id = g_budget_version_id
2406: AND budget_detail_id = p_budget_detail_id
2407: AND posting_type_cd = p_posting_type_cd
2408: AND period_name = p_period_name
2422: p_expenditure_type IN varchar2,
2423: p_organization_id IN number,
2424: p_currency_code IN varchar2) IS
2425: SELECT *
2426: FROM pqh_gl_interface
2427: WHERE budget_version_id = g_budget_version_id
2428: AND budget_detail_id = p_budget_detail_id
2429: AND posting_type_cd = p_posting_type_cd
2430: AND period_name = p_period_name
2466: -- update the record and reverse the txn
2467: --
2468: IF g_old_bdgt_dtls_tab(i).cost_allocation_keyflex_id is NOT NULL
2469: THEN
2470: OPEN csr_pqh_gl_interface(p_period_name => g_old_bdgt_dtls_tab(i).period_name,
2471: p_code_combination_id => g_old_bdgt_dtls_tab(i).code_combination_id,
2472: p_currency_code => g_old_bdgt_dtls_tab(i).currency_code) ;
2473: FETCH csr_pqh_gl_interface INTO l_pqh_gl_interface_rec;
2474:
2469: THEN
2470: OPEN csr_pqh_gl_interface(p_period_name => g_old_bdgt_dtls_tab(i).period_name,
2471: p_code_combination_id => g_old_bdgt_dtls_tab(i).code_combination_id,
2472: p_currency_code => g_old_bdgt_dtls_tab(i).currency_code) ;
2473: FETCH csr_pqh_gl_interface INTO l_pqh_gl_interface_rec;
2474:
2475: hr_utility.set_location('Fetched record ',10);
2476:
2477: --
2476:
2477: --
2478: -- Reverse the old record.
2479: --
2480: UPDATE pqh_gl_interface
2481: SET amount_dr = 0
2482: WHERE CURRENT OF csr_pqh_gl_interface;
2483:
2484: CLOSE csr_pqh_gl_interface;
2478: -- Reverse the old record.
2479: --
2480: UPDATE pqh_gl_interface
2481: SET amount_dr = 0
2482: WHERE CURRENT OF csr_pqh_gl_interface;
2483:
2484: CLOSE csr_pqh_gl_interface;
2485: ELSE
2486: OPEN csr_pqh_gms_interface(p_period_name => g_old_bdgt_dtls_tab(i).period_name,
2480: UPDATE pqh_gl_interface
2481: SET amount_dr = 0
2482: WHERE CURRENT OF csr_pqh_gl_interface;
2483:
2484: CLOSE csr_pqh_gl_interface;
2485: ELSE
2486: OPEN csr_pqh_gms_interface(p_period_name => g_old_bdgt_dtls_tab(i).period_name,
2487: p_project_id => g_old_bdgt_dtls_tab(i).project_id,
2488: p_task_id => g_old_bdgt_dtls_tab(i).task_id,
2489: p_award_id => g_old_bdgt_dtls_tab(i).award_id,
2490: p_expenditure_type => g_old_bdgt_dtls_tab(i).expenditure_type,
2491: p_organization_id => g_old_bdgt_dtls_tab(i).organization_id,
2492: p_currency_code => g_old_bdgt_dtls_tab(i).currency_code) ;
2493: FETCH csr_pqh_gms_interface INTO l_pqh_gl_interface_rec;
2494:
2495: hr_utility.set_location('Fetched record ',10);
2496:
2497: --
2496:
2497: --
2498: -- Reverse the old record.
2499: --
2500: UPDATE pqh_gl_interface
2501: SET amount_dr = 0
2502: WHERE CURRENT OF csr_pqh_gms_interface;
2503:
2504: CLOSE csr_pqh_gms_interface;
2505: END IF;
2506: --
2507: -- create a reverse transaction for this amount_dr
2508: --
2509: INSERT INTO pqh_gl_interface
2510: (
2511: gl_interface_id,
2512: budget_version_id,
2513: budget_detail_id,
2507: -- create a reverse transaction for this amount_dr
2508: --
2509: INSERT INTO pqh_gl_interface
2510: (
2511: gl_interface_id,
2512: budget_version_id,
2513: budget_detail_id,
2514: period_name,
2515: accounting_date,
2529: posting_date
2530: )
2531: VALUES
2532: (
2533: pqh_gl_interface_s.nextval,
2534: g_budget_version_id,
2535: p_budget_detail_id,
2536: g_old_bdgt_dtls_tab(i).period_name,
2537: g_old_bdgt_dtls_tab(i).accounting_date,
2542: g_old_bdgt_dtls_tab(i).award_id,
2543: g_old_bdgt_dtls_tab(i).expenditure_type,
2544: g_old_bdgt_dtls_tab(i).organization_id,
2545: 0,
2546: NVL(l_pqh_gl_interface_rec.amount_dr,0),
2547: g_old_bdgt_dtls_tab(i).currency_code,
2548: null,
2549: 'Y',
2550: 'COMMITMENT',
2574: ---------------------------------------------------------------------------------------
2575: --
2576: PROCEDURE reverse_budget_details
2577: (
2578: p_period_name IN pqh_gl_interface.period_name%TYPE,
2579: p_currency_code IN pqh_gl_interface.currency_code%TYPE,
2580: p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
2581: p_posting_type_cd IN varchar2
2582: ) IS
2575: --
2576: PROCEDURE reverse_budget_details
2577: (
2578: p_period_name IN pqh_gl_interface.period_name%TYPE,
2579: p_currency_code IN pqh_gl_interface.currency_code%TYPE,
2580: p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
2581: p_posting_type_cd IN varchar2
2582: ) IS
2583: --
2576: PROCEDURE reverse_budget_details
2577: (
2578: p_period_name IN pqh_gl_interface.period_name%TYPE,
2579: p_currency_code IN pqh_gl_interface.currency_code%TYPE,
2580: p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
2581: p_posting_type_cd IN varchar2
2582: ) IS
2583: --
2584: -- This procedure will be called if the GL fund checker failed.
2583: --
2584: -- This procedure will be called if the GL fund checker failed.
2585: -- This procedure will does the following : 1. update all the budget_detail
2586: -- records which have this Period Name + CCID + currency code to ERROR ( gl_status)
2587: -- 2. Reverse unposted adjustment txns in pqh_gl_interface
2588: -- 3. Delete all unposted non-adjustment txns from pqh_gl_interface
2589: -- Note : If a budget detail record has 4 periods and there was a error in 4th period ,
2590: -- we have no control on the 1st three as they have already been Approved by funds
2591: -- checker program and would have already been posted to GL.
2584: -- This procedure will be called if the GL fund checker failed.
2585: -- This procedure will does the following : 1. update all the budget_detail
2586: -- records which have this Period Name + CCID + currency code to ERROR ( gl_status)
2587: -- 2. Reverse unposted adjustment txns in pqh_gl_interface
2588: -- 3. Delete all unposted non-adjustment txns from pqh_gl_interface
2589: -- Note : If a budget detail record has 4 periods and there was a error in 4th period ,
2590: -- we have no control on the 1st three as they have already been Approved by funds
2591: -- checker program and would have already been posted to GL.
2592: --
2591: -- checker program and would have already been posted to GL.
2592: --
2593: -- local variables
2594: --
2595: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
2596: --
2597: CURSOR csr_adj IS
2598: SELECT *
2599: FROM pqh_gl_interface
2595: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
2596: --
2597: CURSOR csr_adj IS
2598: SELECT *
2599: FROM pqh_gl_interface
2600: WHERE budget_version_id = g_budget_version_id
2601: AND period_name = p_period_name
2602: AND currency_code = p_currency_code
2603: AND code_combination_id = p_code_combination_id
2615: -- reverse the adjustment transactions
2616: --
2617: OPEN csr_adj;
2618: LOOP
2619: FETCH csr_adj INTO l_pqh_gl_interface_rec;
2620: EXIT WHEN csr_adj%NOTFOUND;
2621: --
2622: -- update the amount_dr for the original record
2623: --
2620: EXIT WHEN csr_adj%NOTFOUND;
2621: --
2622: -- update the amount_dr for the original record
2623: --
2624: UPDATE pqh_gl_interface
2625: SET amount_dr = NVL(amount_dr,0) -
2626: NVL(l_pqh_gl_interface_rec.amount_dr,0) +
2627: NVL(l_pqh_gl_interface_rec.amount_cr,0)
2628: WHERE budget_version_id = l_pqh_gl_interface_rec.budget_version_id
2622: -- update the amount_dr for the original record
2623: --
2624: UPDATE pqh_gl_interface
2625: SET amount_dr = NVL(amount_dr,0) -
2626: NVL(l_pqh_gl_interface_rec.amount_dr,0) +
2627: NVL(l_pqh_gl_interface_rec.amount_cr,0)
2628: WHERE budget_version_id = l_pqh_gl_interface_rec.budget_version_id
2629: AND budget_detail_id = l_pqh_gl_interface_rec.budget_detail_id
2630: AND period_name = l_pqh_gl_interface_rec.period_name
2623: --
2624: UPDATE pqh_gl_interface
2625: SET amount_dr = NVL(amount_dr,0) -
2626: NVL(l_pqh_gl_interface_rec.amount_dr,0) +
2627: NVL(l_pqh_gl_interface_rec.amount_cr,0)
2628: WHERE budget_version_id = l_pqh_gl_interface_rec.budget_version_id
2629: AND budget_detail_id = l_pqh_gl_interface_rec.budget_detail_id
2630: AND period_name = l_pqh_gl_interface_rec.period_name
2631: AND currency_code = l_pqh_gl_interface_rec.currency_code
2624: UPDATE pqh_gl_interface
2625: SET amount_dr = NVL(amount_dr,0) -
2626: NVL(l_pqh_gl_interface_rec.amount_dr,0) +
2627: NVL(l_pqh_gl_interface_rec.amount_cr,0)
2628: WHERE budget_version_id = l_pqh_gl_interface_rec.budget_version_id
2629: AND budget_detail_id = l_pqh_gl_interface_rec.budget_detail_id
2630: AND period_name = l_pqh_gl_interface_rec.period_name
2631: AND currency_code = l_pqh_gl_interface_rec.currency_code
2632: AND code_combination_id = l_pqh_gl_interface_rec.code_combination_id
2625: SET amount_dr = NVL(amount_dr,0) -
2626: NVL(l_pqh_gl_interface_rec.amount_dr,0) +
2627: NVL(l_pqh_gl_interface_rec.amount_cr,0)
2628: WHERE budget_version_id = l_pqh_gl_interface_rec.budget_version_id
2629: AND budget_detail_id = l_pqh_gl_interface_rec.budget_detail_id
2630: AND period_name = l_pqh_gl_interface_rec.period_name
2631: AND currency_code = l_pqh_gl_interface_rec.currency_code
2632: AND code_combination_id = l_pqh_gl_interface_rec.code_combination_id
2633: AND posting_type_cd = p_posting_type_cd
2626: NVL(l_pqh_gl_interface_rec.amount_dr,0) +
2627: NVL(l_pqh_gl_interface_rec.amount_cr,0)
2628: WHERE budget_version_id = l_pqh_gl_interface_rec.budget_version_id
2629: AND budget_detail_id = l_pqh_gl_interface_rec.budget_detail_id
2630: AND period_name = l_pqh_gl_interface_rec.period_name
2631: AND currency_code = l_pqh_gl_interface_rec.currency_code
2632: AND code_combination_id = l_pqh_gl_interface_rec.code_combination_id
2633: AND posting_type_cd = p_posting_type_cd
2634: AND NVL(adjustment_flag,'N') = 'N'
2627: NVL(l_pqh_gl_interface_rec.amount_cr,0)
2628: WHERE budget_version_id = l_pqh_gl_interface_rec.budget_version_id
2629: AND budget_detail_id = l_pqh_gl_interface_rec.budget_detail_id
2630: AND period_name = l_pqh_gl_interface_rec.period_name
2631: AND currency_code = l_pqh_gl_interface_rec.currency_code
2632: AND code_combination_id = l_pqh_gl_interface_rec.code_combination_id
2633: AND posting_type_cd = p_posting_type_cd
2634: AND NVL(adjustment_flag,'N') = 'N'
2635: AND status IS NOT NULL;
2628: WHERE budget_version_id = l_pqh_gl_interface_rec.budget_version_id
2629: AND budget_detail_id = l_pqh_gl_interface_rec.budget_detail_id
2630: AND period_name = l_pqh_gl_interface_rec.period_name
2631: AND currency_code = l_pqh_gl_interface_rec.currency_code
2632: AND code_combination_id = l_pqh_gl_interface_rec.code_combination_id
2633: AND posting_type_cd = p_posting_type_cd
2634: AND NVL(adjustment_flag,'N') = 'N'
2635: AND status IS NOT NULL;
2636:
2643: SET commitment_gl_status = 'ERROR'
2644: WHERE budget_version_id = g_budget_version_id
2645: AND budget_detail_id IN
2646: ( SELECT distinct budget_detail_id
2647: FROM pqh_gl_interface
2648: WHERE budget_version_id = g_budget_version_id
2649: AND period_name = p_period_name
2650: AND currency_code = p_currency_code
2651: AND code_combination_id = p_code_combination_id
2653: AND status IS NULL
2654: AND posting_date IS NULL
2655: );
2656: --
2657: -- delete the unposted transactions from pqh_gl_interface
2658: --
2659: DELETE FROM pqh_gl_interface
2660: WHERE budget_version_id = g_budget_version_id
2661: AND period_name = p_period_name
2655: );
2656: --
2657: -- delete the unposted transactions from pqh_gl_interface
2658: --
2659: DELETE FROM pqh_gl_interface
2660: WHERE budget_version_id = g_budget_version_id
2661: AND period_name = p_period_name
2662: AND currency_code = p_currency_code
2663: AND code_combination_id = p_code_combination_id
2678: END reverse_budget_details;
2679: --
2680: -----------------------------------------------------------------------------------------
2681: --
2682: -- This procedure will pick records from pqh_gl_interface table and insert them into
2683: -- gl tables depending on the g_budgetary_control_flag If we insert into gl_bc_packets
2684: -- do funds checking for each packet
2685: --
2686: PROCEDURE populate_gl_tables
2687: IS
2688: --
2689: -- local variables
2690: --
2691: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
2692: l_period_name pqh_gl_interface.period_name%TYPE;
2693: l_accounting_date pqh_gl_interface.accounting_date%TYPE;
2694: l_code_combination_id pqh_gl_interface.code_combination_id%TYPE;
2695: l_cost_allocation_keyflex_id pqh_gl_interface.cost_allocation_keyflex_id%TYPE;
2688: --
2689: -- local variables
2690: --
2691: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
2692: l_period_name pqh_gl_interface.period_name%TYPE;
2693: l_accounting_date pqh_gl_interface.accounting_date%TYPE;
2694: l_code_combination_id pqh_gl_interface.code_combination_id%TYPE;
2695: l_cost_allocation_keyflex_id pqh_gl_interface.cost_allocation_keyflex_id%TYPE;
2696: l_currency_code pqh_gl_interface.currency_code%TYPE;
2689: -- local variables
2690: --
2691: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
2692: l_period_name pqh_gl_interface.period_name%TYPE;
2693: l_accounting_date pqh_gl_interface.accounting_date%TYPE;
2694: l_code_combination_id pqh_gl_interface.code_combination_id%TYPE;
2695: l_cost_allocation_keyflex_id pqh_gl_interface.cost_allocation_keyflex_id%TYPE;
2696: l_currency_code pqh_gl_interface.currency_code%TYPE;
2697: l_amount_dr pqh_gl_interface.amount_dr%TYPE;
2690: --
2691: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
2692: l_period_name pqh_gl_interface.period_name%TYPE;
2693: l_accounting_date pqh_gl_interface.accounting_date%TYPE;
2694: l_code_combination_id pqh_gl_interface.code_combination_id%TYPE;
2695: l_cost_allocation_keyflex_id pqh_gl_interface.cost_allocation_keyflex_id%TYPE;
2696: l_currency_code pqh_gl_interface.currency_code%TYPE;
2697: l_amount_dr pqh_gl_interface.amount_dr%TYPE;
2698: l_amount_cr pqh_gl_interface.amount_cr%TYPE;
2691: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
2692: l_period_name pqh_gl_interface.period_name%TYPE;
2693: l_accounting_date pqh_gl_interface.accounting_date%TYPE;
2694: l_code_combination_id pqh_gl_interface.code_combination_id%TYPE;
2695: l_cost_allocation_keyflex_id pqh_gl_interface.cost_allocation_keyflex_id%TYPE;
2696: l_currency_code pqh_gl_interface.currency_code%TYPE;
2697: l_amount_dr pqh_gl_interface.amount_dr%TYPE;
2698: l_amount_cr pqh_gl_interface.amount_cr%TYPE;
2699: l_packet_id gl_bc_packets.packet_id%TYPE;
2692: l_period_name pqh_gl_interface.period_name%TYPE;
2693: l_accounting_date pqh_gl_interface.accounting_date%TYPE;
2694: l_code_combination_id pqh_gl_interface.code_combination_id%TYPE;
2695: l_cost_allocation_keyflex_id pqh_gl_interface.cost_allocation_keyflex_id%TYPE;
2696: l_currency_code pqh_gl_interface.currency_code%TYPE;
2697: l_amount_dr pqh_gl_interface.amount_dr%TYPE;
2698: l_amount_cr pqh_gl_interface.amount_cr%TYPE;
2699: l_packet_id gl_bc_packets.packet_id%TYPE;
2700: l_gl_period_statuses_rec gl_period_statuses%ROWTYPE;
2693: l_accounting_date pqh_gl_interface.accounting_date%TYPE;
2694: l_code_combination_id pqh_gl_interface.code_combination_id%TYPE;
2695: l_cost_allocation_keyflex_id pqh_gl_interface.cost_allocation_keyflex_id%TYPE;
2696: l_currency_code pqh_gl_interface.currency_code%TYPE;
2697: l_amount_dr pqh_gl_interface.amount_dr%TYPE;
2698: l_amount_cr pqh_gl_interface.amount_cr%TYPE;
2699: l_packet_id gl_bc_packets.packet_id%TYPE;
2700: l_gl_period_statuses_rec gl_period_statuses%ROWTYPE;
2701: l_fc_success boolean;
2694: l_code_combination_id pqh_gl_interface.code_combination_id%TYPE;
2695: l_cost_allocation_keyflex_id pqh_gl_interface.cost_allocation_keyflex_id%TYPE;
2696: l_currency_code pqh_gl_interface.currency_code%TYPE;
2697: l_amount_dr pqh_gl_interface.amount_dr%TYPE;
2698: l_amount_cr pqh_gl_interface.amount_cr%TYPE;
2699: l_packet_id gl_bc_packets.packet_id%TYPE;
2700: l_gl_period_statuses_rec gl_period_statuses%ROWTYPE;
2701: l_fc_success boolean;
2702: l_fc_return varchar2(100);
2705: l_log_context varchar2(255);
2706: l_packet_result_code varchar2(255);
2707: l_packet_status_code varchar2(255);
2708: --
2709: CURSOR csr_pqh_gl_interface IS
2710: SELECT period_name, accounting_date,
2711: code_combination_id, cost_allocation_keyflex_id, currency_code,
2712: SUM(NVL(amount_dr,0)) amount_dr,
2713: SUM(NVL(amount_cr,0)) amount_cr
2710: SELECT period_name, accounting_date,
2711: code_combination_id, cost_allocation_keyflex_id, currency_code,
2712: SUM(NVL(amount_dr,0)) amount_dr,
2713: SUM(NVL(amount_cr,0)) amount_cr
2714: FROM pqh_gl_interface
2715: WHERE budget_version_id IN (g_budget_version_id, NVL(g_last_posted_ver,0) )
2716: AND status IS NULL
2717: AND posting_date IS NULL
2718: AND posting_type_cd = 'COMMITMENT'
2761: -- insert into gl_bc_packets and do funds checking for each packet
2762: --
2763: hr_utility.set_location('Inserting into GL_BC_PACKETS',10);
2764: --
2765: OPEN csr_pqh_gl_interface;
2766: LOOP
2767: --
2768: FETCH csr_pqh_gl_interface INTO l_period_name, l_accounting_date,
2769: l_code_combination_id, l_cost_allocation_keyflex_id, l_currency_code,
2764: --
2765: OPEN csr_pqh_gl_interface;
2766: LOOP
2767: --
2768: FETCH csr_pqh_gl_interface INTO l_period_name, l_accounting_date,
2769: l_code_combination_id, l_cost_allocation_keyflex_id, l_currency_code,
2770: l_amount_dr, l_amount_cr;
2771: EXIT WHEN csr_pqh_gl_interface%NOTFOUND;
2772: --
2767: --
2768: FETCH csr_pqh_gl_interface INTO l_period_name, l_accounting_date,
2769: l_code_combination_id, l_cost_allocation_keyflex_id, l_currency_code,
2770: l_amount_dr, l_amount_cr;
2771: EXIT WHEN csr_pqh_gl_interface%NOTFOUND;
2772: --
2773: -- Get Packet ID
2774: --
2775: OPEN csr_packet_id;
2849: -- If the fund checker program failed i.e l_fc_success = FALSE or
2850: -- l_fc_return in ('T', 'F','R') then we would do the following :
2851: -- 1. Put the error message in pqh_process_log ( context : Period Name + CCID + currency code )
2852: -- 2.update gl_status of budget_detail records which have this Period Name+CCID+currency code to ERROR
2853: -- 3. Reverse unposted adjustment txns in pqh_gl_interface
2854: -- 4. Delete all unposted non-adjustment txns from pqh_gl_interface
2855: --
2856: IF NOT ( l_fc_success ) OR ( NVL(l_fc_return,'T') in ('T', 'F','R') ) THEN
2857: --
2850: -- l_fc_return in ('T', 'F','R') then we would do the following :
2851: -- 1. Put the error message in pqh_process_log ( context : Period Name + CCID + currency code )
2852: -- 2.update gl_status of budget_detail records which have this Period Name+CCID+currency code to ERROR
2853: -- 3. Reverse unposted adjustment txns in pqh_gl_interface
2854: -- 4. Delete all unposted non-adjustment txns from pqh_gl_interface
2855: --
2856: IF NOT ( l_fc_success ) OR ( NVL(l_fc_return,'T') in ('T', 'F','R') ) THEN
2857: --
2858: -- fund checker failed
2917: END IF; -- Fund checker Error
2918: --
2919: END LOOP;
2920: --
2921: CLOSE csr_pqh_gl_interface;
2922: --
2923: ELSE
2924: --
2925: -- insert into gl_interface
2921: CLOSE csr_pqh_gl_interface;
2922: --
2923: ELSE
2924: --
2925: -- insert into gl_interface
2926: --
2927: hr_utility.set_location('Inserting into GL_INTERFACE',200);
2928: --
2929: OPEN csr_pqh_gl_interface;
2923: ELSE
2924: --
2925: -- insert into gl_interface
2926: --
2927: hr_utility.set_location('Inserting into GL_INTERFACE',200);
2928: --
2929: OPEN csr_pqh_gl_interface;
2930: LOOP
2931: --
2925: -- insert into gl_interface
2926: --
2927: hr_utility.set_location('Inserting into GL_INTERFACE',200);
2928: --
2929: OPEN csr_pqh_gl_interface;
2930: LOOP
2931: --
2932: FETCH csr_pqh_gl_interface INTO l_period_name, l_accounting_date,
2933: l_code_combination_id, l_cost_allocation_keyflex_id, l_currency_code,
2928: --
2929: OPEN csr_pqh_gl_interface;
2930: LOOP
2931: --
2932: FETCH csr_pqh_gl_interface INTO l_period_name, l_accounting_date,
2933: l_code_combination_id, l_cost_allocation_keyflex_id, l_currency_code,
2934: l_amount_dr, l_amount_cr;
2935: EXIT WHEN csr_pqh_gl_interface%NOTFOUND;
2936: --
2931: --
2932: FETCH csr_pqh_gl_interface INTO l_period_name, l_accounting_date,
2933: l_code_combination_id, l_cost_allocation_keyflex_id, l_currency_code,
2934: l_amount_dr, l_amount_cr;
2935: EXIT WHEN csr_pqh_gl_interface%NOTFOUND;
2936: --
2937: INSERT INTO gl_interface
2938: (status,
2939: set_of_books_id,
2933: l_code_combination_id, l_cost_allocation_keyflex_id, l_currency_code,
2934: l_amount_dr, l_amount_cr;
2935: EXIT WHEN csr_pqh_gl_interface%NOTFOUND;
2936: --
2937: INSERT INTO gl_interface
2938: (status,
2939: set_of_books_id,
2940: user_je_source_name,
2941: user_je_category_name,
2972: l_cost_allocation_keyflex_id);
2973: --
2974: END LOOP;
2975: --
2976: CLOSE csr_pqh_gl_interface;
2977: --
2978: END IF;
2979: --
2980: hr_utility.set_location('Leaving:'||l_proc, 1000);
2991: PROCEDURE update_commitment_gl_status
2992: IS
2993: --
2994: -- This procedure will update the gl_status of pqh_budget_versions,
2995: -- pqh_budget_details and update the pqh_gl_interface table
2996: -- We always update the TRANSFERED_TO_GL_FLAG = Y to indicate this is the
2997: -- latest budget_version that is posted to GL
2998: -- gl_status = POST or ERROR
2999: --
3068: END IF;
3069: --
3070: hr_utility.set_location('Budget Details Error Count : '||l_count, 100);
3071: --
3072: -- update the pqh_gl_interface table
3073: --
3074: UPDATE pqh_gl_interface
3075: SET posting_date = sysdate,
3076: status = 'POST'
3070: hr_utility.set_location('Budget Details Error Count : '||l_count, 100);
3071: --
3072: -- update the pqh_gl_interface table
3073: --
3074: UPDATE pqh_gl_interface
3075: SET posting_date = sysdate,
3076: status = 'POST'
3077: WHERE budget_version_id = g_budget_version_id
3078: AND posting_type_cd = 'COMMITMENT'
3078: AND posting_type_cd = 'COMMITMENT'
3079: AND posting_date IS NULL
3080: AND status IS NULL;
3081: --
3082: -- update the pqh_gl_interface table for last posted version
3083: --
3084: UPDATE pqh_gl_interface
3085: SET posting_date = sysdate,
3086: status = 'POST'
3080: AND status IS NULL;
3081: --
3082: -- update the pqh_gl_interface table for last posted version
3083: --
3084: UPDATE pqh_gl_interface
3085: SET posting_date = sysdate,
3086: status = 'POST'
3087: WHERE budget_version_id = NVL(g_last_posted_ver,0)
3088: AND posting_type_cd = 'COMMITMENT'
3148: raise g_error_exception;
3149: END set_bdt_log_context;
3150: --
3151: ------------------------------------------------------------------------------------
3152: PROCEDURE insert_pqh_gl_interface
3153: (
3154: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
3155: p_period_name IN pqh_gl_interface.period_name%TYPE,
3156: p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
3150: --
3151: ------------------------------------------------------------------------------------
3152: PROCEDURE insert_pqh_gl_interface
3153: (
3154: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
3155: p_period_name IN pqh_gl_interface.period_name%TYPE,
3156: p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
3157: p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
3158: p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
3151: ------------------------------------------------------------------------------------
3152: PROCEDURE insert_pqh_gl_interface
3153: (
3154: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
3155: p_period_name IN pqh_gl_interface.period_name%TYPE,
3156: p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
3157: p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
3158: p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
3159: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3152: PROCEDURE insert_pqh_gl_interface
3153: (
3154: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
3155: p_period_name IN pqh_gl_interface.period_name%TYPE,
3156: p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
3157: p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
3158: p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
3159: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3160: p_currency_code IN pqh_gl_interface.currency_code%TYPE,
3153: (
3154: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
3155: p_period_name IN pqh_gl_interface.period_name%TYPE,
3156: p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
3157: p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
3158: p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
3159: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3160: p_currency_code IN pqh_gl_interface.currency_code%TYPE,
3161: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3154: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
3155: p_period_name IN pqh_gl_interface.period_name%TYPE,
3156: p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
3157: p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
3158: p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
3159: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3160: p_currency_code IN pqh_gl_interface.currency_code%TYPE,
3161: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3162: ) IS
3155: p_period_name IN pqh_gl_interface.period_name%TYPE,
3156: p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
3157: p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
3158: p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
3159: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3160: p_currency_code IN pqh_gl_interface.currency_code%TYPE,
3161: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3162: ) IS
3163: --
3156: p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
3157: p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
3158: p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
3159: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3160: p_currency_code IN pqh_gl_interface.currency_code%TYPE,
3161: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3162: ) IS
3163: --
3164: -- This procedure will insert record into pqh_gl_interface
3157: p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
3158: p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
3159: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3160: p_currency_code IN pqh_gl_interface.currency_code%TYPE,
3161: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3162: ) IS
3163: --
3164: -- This procedure will insert record into pqh_gl_interface
3165: -- If the same UOM is repeated more then once then we would update the unposted txn.
3160: p_currency_code IN pqh_gl_interface.currency_code%TYPE,
3161: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3162: ) IS
3163: --
3164: -- This procedure will insert record into pqh_gl_interface
3165: -- If the same UOM is repeated more then once then we would update the unposted txn.
3166: --
3167: CURSOR csr_pqh_gl_interface IS
3168: SELECT COUNT(*)
3163: --
3164: -- This procedure will insert record into pqh_gl_interface
3165: -- If the same UOM is repeated more then once then we would update the unposted txn.
3166: --
3167: CURSOR csr_pqh_gl_interface IS
3168: SELECT COUNT(*)
3169: FROM pqh_gl_interface
3170: WHERE budget_version_id = g_budget_version_id
3171: AND budget_detail_id = p_budget_detail_id
3165: -- If the same UOM is repeated more then once then we would update the unposted txn.
3166: --
3167: CURSOR csr_pqh_gl_interface IS
3168: SELECT COUNT(*)
3169: FROM pqh_gl_interface
3170: WHERE budget_version_id = g_budget_version_id
3171: AND budget_detail_id = p_budget_detail_id
3172: AND period_name = p_period_name
3173: AND code_combination_id = p_code_combination_id
3179: AND cost_allocation_keyflex_id is not null;
3180: --
3181: -- local variables
3182: --
3183: l_proc varchar2(72) := g_package||'insert_pqh_gl_interface';
3184: l_count number(9) := 0 ;
3185: --
3186: BEGIN
3187:
3188: hr_utility.set_location('Entering: '||l_proc, 5);
3189: --
3190: -- check if its a repeat of that same UOM
3191: --
3192: OPEN csr_pqh_gl_interface;
3193: FETCH csr_pqh_gl_interface INTO l_count;
3194: CLOSE csr_pqh_gl_interface;
3195: --
3196: hr_utility.set_location('l_count in Insert pqh_gl_interface : '||l_count,10);
3189: --
3190: -- check if its a repeat of that same UOM
3191: --
3192: OPEN csr_pqh_gl_interface;
3193: FETCH csr_pqh_gl_interface INTO l_count;
3194: CLOSE csr_pqh_gl_interface;
3195: --
3196: hr_utility.set_location('l_count in Insert pqh_gl_interface : '||l_count,10);
3197: --
3190: -- check if its a repeat of that same UOM
3191: --
3192: OPEN csr_pqh_gl_interface;
3193: FETCH csr_pqh_gl_interface INTO l_count;
3194: CLOSE csr_pqh_gl_interface;
3195: --
3196: hr_utility.set_location('l_count in Insert pqh_gl_interface : '||l_count,10);
3197: --
3198: IF l_count <> 0 THEN
3192: OPEN csr_pqh_gl_interface;
3193: FETCH csr_pqh_gl_interface INTO l_count;
3194: CLOSE csr_pqh_gl_interface;
3195: --
3196: hr_utility.set_location('l_count in Insert pqh_gl_interface : '||l_count,10);
3197: --
3198: IF l_count <> 0 THEN
3199: --
3200: -- this is a repeat of UOM , so update the first one adding the new amount
3198: IF l_count <> 0 THEN
3199: --
3200: -- this is a repeat of UOM , so update the first one adding the new amount
3201: --
3202: UPDATE pqh_gl_interface
3203: SET AMOUNT_DR = NVL(AMOUNT_DR,0) + NVL(p_amount,0)
3204: WHERE budget_version_id = g_budget_version_id
3205: AND budget_detail_id = p_budget_detail_id
3206: AND period_name = p_period_name
3215: --
3216: hr_utility.set_location('Currency code: '||p_currency_code, 5);
3217: -- insert this record
3218: --
3219: INSERT INTO pqh_gl_interface
3220: (
3221: gl_interface_id,
3222: budget_version_id,
3223: budget_detail_id,
3217: -- insert this record
3218: --
3219: INSERT INTO pqh_gl_interface
3220: (
3221: gl_interface_id,
3222: budget_version_id,
3223: budget_detail_id,
3224: period_name,
3225: accounting_date,
3234: posting_type_cd
3235: )
3236: VALUES
3237: (
3238: pqh_gl_interface_s.nextval,
3239: g_budget_version_id,
3240: p_budget_detail_id,
3241: p_period_name,
3242: p_accounting_date,
3260: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3261: hr_utility.set_message_token('ROUTINE', l_proc);
3262: hr_utility.set_message_token('REASON', SQLERRM);
3263: hr_utility.raise_error;
3264: END insert_pqh_gl_interface;
3265: --
3266: -- ----------------------------------------------------------------------------
3267: PROCEDURE update_pqh_gl_interface
3268: (
3263: hr_utility.raise_error;
3264: END insert_pqh_gl_interface;
3265: --
3266: -- ----------------------------------------------------------------------------
3267: PROCEDURE update_pqh_gl_interface
3268: (
3269: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
3270: p_period_name IN pqh_gl_interface.period_name%TYPE,
3271: p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
3265: --
3266: -- ----------------------------------------------------------------------------
3267: PROCEDURE update_pqh_gl_interface
3268: (
3269: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
3270: p_period_name IN pqh_gl_interface.period_name%TYPE,
3271: p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
3272: p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
3273: p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
3266: -- ----------------------------------------------------------------------------
3267: PROCEDURE update_pqh_gl_interface
3268: (
3269: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
3270: p_period_name IN pqh_gl_interface.period_name%TYPE,
3271: p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
3272: p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
3273: p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
3274: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3267: PROCEDURE update_pqh_gl_interface
3268: (
3269: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
3270: p_period_name IN pqh_gl_interface.period_name%TYPE,
3271: p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
3272: p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
3273: p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
3274: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3275: p_currency_code IN pqh_gl_interface.currency_code%TYPE,
3268: (
3269: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
3270: p_period_name IN pqh_gl_interface.period_name%TYPE,
3271: p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
3272: p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
3273: p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
3274: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3275: p_currency_code IN pqh_gl_interface.currency_code%TYPE,
3276: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3269: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
3270: p_period_name IN pqh_gl_interface.period_name%TYPE,
3271: p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
3272: p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
3273: p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
3274: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3275: p_currency_code IN pqh_gl_interface.currency_code%TYPE,
3276: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3277: ) IS
3270: p_period_name IN pqh_gl_interface.period_name%TYPE,
3271: p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
3272: p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
3273: p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
3274: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3275: p_currency_code IN pqh_gl_interface.currency_code%TYPE,
3276: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3277: ) IS
3278: --
3271: p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
3272: p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
3273: p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
3274: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3275: p_currency_code IN pqh_gl_interface.currency_code%TYPE,
3276: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3277: ) IS
3278: --
3279: -- This procedure will update pqh_gl_interface and create a adjustment record
3272: p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
3273: p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
3274: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3275: p_currency_code IN pqh_gl_interface.currency_code%TYPE,
3276: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3277: ) IS
3278: --
3279: -- This procedure will update pqh_gl_interface and create a adjustment record
3280: --
3275: p_currency_code IN pqh_gl_interface.currency_code%TYPE,
3276: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3277: ) IS
3278: --
3279: -- This procedure will update pqh_gl_interface and create a adjustment record
3280: --
3281: --
3282: -- local variables
3283: --
3280: --
3281: --
3282: -- local variables
3283: --
3284: l_proc varchar2(72) := g_package||'update_pqh_gl_interface';
3285: --
3286: l_amount_diff pqh_gl_interface.amount_dr%TYPE :=0;
3287: l_amount_dr pqh_gl_interface.amount_dr%TYPE :=0;
3288: l_amount_cr pqh_gl_interface.amount_cr%TYPE :=0;
3282: -- local variables
3283: --
3284: l_proc varchar2(72) := g_package||'update_pqh_gl_interface';
3285: --
3286: l_amount_diff pqh_gl_interface.amount_dr%TYPE :=0;
3287: l_amount_dr pqh_gl_interface.amount_dr%TYPE :=0;
3288: l_amount_cr pqh_gl_interface.amount_cr%TYPE :=0;
3289: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
3290: --
3283: --
3284: l_proc varchar2(72) := g_package||'update_pqh_gl_interface';
3285: --
3286: l_amount_diff pqh_gl_interface.amount_dr%TYPE :=0;
3287: l_amount_dr pqh_gl_interface.amount_dr%TYPE :=0;
3288: l_amount_cr pqh_gl_interface.amount_cr%TYPE :=0;
3289: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
3290: --
3291: CURSOR csr_pqh_gl_interface IS
3284: l_proc varchar2(72) := g_package||'update_pqh_gl_interface';
3285: --
3286: l_amount_diff pqh_gl_interface.amount_dr%TYPE :=0;
3287: l_amount_dr pqh_gl_interface.amount_dr%TYPE :=0;
3288: l_amount_cr pqh_gl_interface.amount_cr%TYPE :=0;
3289: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
3290: --
3291: CURSOR csr_pqh_gl_interface IS
3292: SELECT *
3285: --
3286: l_amount_diff pqh_gl_interface.amount_dr%TYPE :=0;
3287: l_amount_dr pqh_gl_interface.amount_dr%TYPE :=0;
3288: l_amount_cr pqh_gl_interface.amount_cr%TYPE :=0;
3289: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
3290: --
3291: CURSOR csr_pqh_gl_interface IS
3292: SELECT *
3293: FROM pqh_gl_interface
3287: l_amount_dr pqh_gl_interface.amount_dr%TYPE :=0;
3288: l_amount_cr pqh_gl_interface.amount_cr%TYPE :=0;
3289: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
3290: --
3291: CURSOR csr_pqh_gl_interface IS
3292: SELECT *
3293: FROM pqh_gl_interface
3294: WHERE budget_version_id = g_budget_version_id
3295: AND budget_detail_id = p_budget_detail_id
3289: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
3290: --
3291: CURSOR csr_pqh_gl_interface IS
3292: SELECT *
3293: FROM pqh_gl_interface
3294: WHERE budget_version_id = g_budget_version_id
3295: AND budget_detail_id = p_budget_detail_id
3296: AND period_name = p_period_name
3297: AND code_combination_id = p_code_combination_id
3306: BEGIN
3307: --
3308: hr_utility.set_location('Entering: '||l_proc, 5);
3309: --
3310: OPEN csr_pqh_gl_interface;
3311: FETCH csr_pqh_gl_interface INTO l_pqh_gl_interface_rec;
3312: --
3313: l_amount_diff := NVL(p_amount,0) - NVL(l_pqh_gl_interface_rec.amount_dr,0);
3314: --
3307: --
3308: hr_utility.set_location('Entering: '||l_proc, 5);
3309: --
3310: OPEN csr_pqh_gl_interface;
3311: FETCH csr_pqh_gl_interface INTO l_pqh_gl_interface_rec;
3312: --
3313: l_amount_diff := NVL(p_amount,0) - NVL(l_pqh_gl_interface_rec.amount_dr,0);
3314: --
3315: IF l_amount_diff > 0 THEN
3309: --
3310: OPEN csr_pqh_gl_interface;
3311: FETCH csr_pqh_gl_interface INTO l_pqh_gl_interface_rec;
3312: --
3313: l_amount_diff := NVL(p_amount,0) - NVL(l_pqh_gl_interface_rec.amount_dr,0);
3314: --
3315: IF l_amount_diff > 0 THEN
3316: --
3317: -- debit as new is more then old
3325: l_amount_cr := (-1)*l_amount_diff;
3326: --
3327: END IF;
3328: --
3329: -- update the pqh_gl_interface table
3330: --
3331: UPDATE pqh_gl_interface
3332: SET amount_dr = NVL(p_amount,0)
3333: WHERE CURRENT OF csr_pqh_gl_interface;
3327: END IF;
3328: --
3329: -- update the pqh_gl_interface table
3330: --
3331: UPDATE pqh_gl_interface
3332: SET amount_dr = NVL(p_amount,0)
3333: WHERE CURRENT OF csr_pqh_gl_interface;
3334: --
3335: CLOSE csr_pqh_gl_interface;
3329: -- update the pqh_gl_interface table
3330: --
3331: UPDATE pqh_gl_interface
3332: SET amount_dr = NVL(p_amount,0)
3333: WHERE CURRENT OF csr_pqh_gl_interface;
3334: --
3335: CLOSE csr_pqh_gl_interface;
3336: --
3337: -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
3331: UPDATE pqh_gl_interface
3332: SET amount_dr = NVL(p_amount,0)
3333: WHERE CURRENT OF csr_pqh_gl_interface;
3334: --
3335: CLOSE csr_pqh_gl_interface;
3336: --
3337: -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
3338: --
3339: IF NVL(l_amount_diff,0) <> 0 THEN
3337: -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
3338: --
3339: IF NVL(l_amount_diff,0) <> 0 THEN
3340: --
3341: INSERT INTO pqh_gl_interface
3342: (
3343: gl_interface_id,
3344: budget_version_id,
3345: budget_detail_id,
3339: IF NVL(l_amount_diff,0) <> 0 THEN
3340: --
3341: INSERT INTO pqh_gl_interface
3342: (
3343: gl_interface_id,
3344: budget_version_id,
3345: budget_detail_id,
3346: period_name,
3347: accounting_date,
3356: posting_type_cd
3357: )
3358: VALUES
3359: (
3360: pqh_gl_interface_s.nextval,
3361: g_budget_version_id,
3362: p_budget_detail_id,
3363: p_period_name,
3364: p_accounting_date,
3382: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3383: hr_utility.set_message_token('ROUTINE', l_proc);
3384: hr_utility.set_message_token('REASON', SQLERRM);
3385: hr_utility.raise_error;
3386: END update_pqh_gl_interface;
3387:
3388: -- ----------------------------------------------------------------------------
3389: --
3390: PROCEDURE populate_pqh_gl_interface
3386: END update_pqh_gl_interface;
3387:
3388: -- ----------------------------------------------------------------------------
3389: --
3390: PROCEDURE populate_pqh_gl_interface
3391: (
3392: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
3393: p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
3394: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3390: PROCEDURE populate_pqh_gl_interface
3391: (
3392: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
3393: p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
3394: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3395: )
3396: IS
3397: --
3398: -- This procedure will update or insert into pqh_gl_interface if there was
3394: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3395: )
3396: IS
3397: --
3398: -- This procedure will update or insert into pqh_gl_interface if there was
3399: -- no error -- for the current budget detail record i.e g_detail_error = N.
3400: -- If g_detail_error = Y
3401: -- then update the pqh_budget_details record with gl_status = ERROR.
3402: --
3399: -- no error -- for the current budget detail record i.e g_detail_error = N.
3400: -- If g_detail_error = Y
3401: -- then update the pqh_budget_details record with gl_status = ERROR.
3402: --
3403: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
3404: l_uom1_count number;
3405: l_uom2_count number;
3406: l_uom3_count number;
3407: --
3408: CURSOR csr_pqh_interface (p_period_name IN varchar2,
3409: p_code_combination_id IN number,
3410: p_currency_code IN varchar2) IS
3411: SELECT COUNT(*)
3412: FROM pqh_gl_interface
3413: WHERE budget_version_id = p_budget_version_id
3414: AND budget_detail_id = p_budget_detail_id
3415: AND period_name = p_period_name
3416: AND code_combination_id = p_code_combination_id
3422: AND cost_allocation_keyflex_id is not null;
3423: --
3424: -- local variables
3425: --
3426: l_proc varchar2(72) := g_package||'populate_pqh_gl_interface';
3427: --
3428: BEGIN
3429: --
3430: hr_utility.set_location('Entering: '||l_proc, 5);
3430: hr_utility.set_location('Entering: '||l_proc, 5);
3431: --
3432: If g_detail_error = 'N' THEN
3433: --
3434: -- loop thru the array and get populate the pqh_gl_interface table
3435: --
3436: FOR i IN NVL(g_period_amt_tab.FIRST,0)..NVL(g_period_amt_tab.LAST,-1)
3437: LOOP
3438: --
3451:
3452: IF l_uom1_count <> 0 THEN
3453: --
3454: hr_utility.set_location('CURRENCY '||g_currency_code1,7);
3455: -- update pqh_gl_interface and create a adjustment txn
3456: --
3457: update_pqh_gl_interface
3458: (
3459: p_budget_detail_id => p_budget_detail_id,
3453: --
3454: hr_utility.set_location('CURRENCY '||g_currency_code1,7);
3455: -- update pqh_gl_interface and create a adjustment txn
3456: --
3457: update_pqh_gl_interface
3458: (
3459: p_budget_detail_id => p_budget_detail_id,
3460: p_period_name => g_period_amt_tab(i).period_name,
3461: p_accounting_date => g_period_amt_tab(i).accounting_date,
3467: );
3468: ELSE
3469: --
3470: hr_utility.set_location('CURRENCY '||g_currency_code1,7);
3471: -- insert into pqh_gl_interface
3472: --
3473: insert_pqh_gl_interface
3474: (
3475: p_budget_detail_id => p_budget_detail_id,
3469: --
3470: hr_utility.set_location('CURRENCY '||g_currency_code1,7);
3471: -- insert into pqh_gl_interface
3472: --
3473: insert_pqh_gl_interface
3474: (
3475: p_budget_detail_id => p_budget_detail_id,
3476: p_period_name => g_period_amt_tab(i).period_name,
3477: p_accounting_date => g_period_amt_tab(i).accounting_date,
3497: CLOSE csr_pqh_interface;
3498:
3499: IF l_uom2_count <> 0 THEN
3500: --
3501: -- update pqh_gl_interface and create a adjustment txn
3502: --
3503: update_pqh_gl_interface
3504: (
3505: p_budget_detail_id => p_budget_detail_id,
3499: IF l_uom2_count <> 0 THEN
3500: --
3501: -- update pqh_gl_interface and create a adjustment txn
3502: --
3503: update_pqh_gl_interface
3504: (
3505: p_budget_detail_id => p_budget_detail_id,
3506: p_period_name => g_period_amt_tab(i).period_name,
3507: p_accounting_date => g_period_amt_tab(i).accounting_date,
3512: p_currency_code => g_currency_code2
3513: );
3514: ELSE
3515: --
3516: -- insert into pqh_gl_interface
3517: --
3518: insert_pqh_gl_interface
3519: (
3520: p_budget_detail_id => p_budget_detail_id,
3514: ELSE
3515: --
3516: -- insert into pqh_gl_interface
3517: --
3518: insert_pqh_gl_interface
3519: (
3520: p_budget_detail_id => p_budget_detail_id,
3521: p_period_name => g_period_amt_tab(i).period_name,
3522: p_accounting_date => g_period_amt_tab(i).accounting_date,
3544: CLOSE csr_pqh_interface;
3545:
3546: IF l_uom3_count <> 0 THEN
3547: --
3548: -- update pqh_gl_interface and create a adjustment txn
3549: --
3550: update_pqh_gl_interface
3551: (
3552: p_budget_detail_id => p_budget_detail_id,
3546: IF l_uom3_count <> 0 THEN
3547: --
3548: -- update pqh_gl_interface and create a adjustment txn
3549: --
3550: update_pqh_gl_interface
3551: (
3552: p_budget_detail_id => p_budget_detail_id,
3553: p_period_name => g_period_amt_tab(i).period_name,
3554: p_accounting_date => g_period_amt_tab(i).accounting_date,
3559: p_currency_code => g_currency_code3
3560: );
3561: ELSE
3562: --
3563: -- insert into pqh_gl_interface
3564: --
3565: insert_pqh_gl_interface
3566: (
3567: p_budget_detail_id => p_budget_detail_id,
3561: ELSE
3562: --
3563: -- insert into pqh_gl_interface
3564: --
3565: insert_pqh_gl_interface
3566: (
3567: p_budget_detail_id => p_budget_detail_id,
3568: p_period_name => g_period_amt_tab(i).period_name,
3569: p_accounting_date => g_period_amt_tab(i).accounting_date,
3604: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3605: hr_utility.set_message_token('ROUTINE', l_proc);
3606: hr_utility.set_message_token('REASON', SQLERRM);
3607: hr_utility.raise_error;
3608: END populate_pqh_gl_interface;
3609:
3610:
3611:
3612: -- ----------------------------------------------------------------------------
3612: -- ----------------------------------------------------------------------------
3613: ----------------------------------------------------------------------------------------------
3614: -- This is the MAIN procedure which is called to post budget commitment
3615: -- This would pick-up all the budget_detail under the budget_version_id
3616: -- and try to post them to gl interface tables
3617: -- If the program is run in validate mode i.e g_validate is TRUE then we
3618: -- would just check for errors and log the errors
3619: --
3620: -- Additional parameter is added p_effecitve_date for the bug 2288274
3757: consolidate_commitment;
3758: END IF;
3759:
3760: --
3761: -- populate pqh_gl_interface table if there was no error and
3762: -- validate is false
3763: --
3764: IF NOT g_validate THEN
3765: --
3770: p_budget_detail_id => l_budget_details_rec.budget_detail_id,
3771: p_posting_type_cd => 'COMMITMENT'
3772: );
3773: --
3774: -- build the new bdgt_dtls tab and populate_pqh_gl_interface
3775: --
3776: populate_pqh_gl_interface
3777: (
3778: p_budget_version_id => l_budget_details_rec.budget_version_id,
3772: );
3773: --
3774: -- build the new bdgt_dtls tab and populate_pqh_gl_interface
3775: --
3776: populate_pqh_gl_interface
3777: (
3778: p_budget_version_id => l_budget_details_rec.budget_version_id,
3779: p_budget_detail_id => l_budget_details_rec.budget_detail_id,
3780: p_posting_type_cd => 'COMMITMENT'
3819: END IF;
3820: --
3821: **/
3822: --
3823: -- insert into gl_interface or gl_bc_packets table if not in validate mode
3824: --
3825: IF NOT g_validate THEN
3826: --
3827: populate_gl_tables;
3831: --
3832: END IF;
3833: --
3834: -- update gl_status of pqh_budget_versions and pqh_budget_details
3835: -- update posting_date and status of pqh_gl_interface
3836: -- update the global g_status with the program status
3837: --
3838: IF NOT g_validate THEN
3839: --
3872: -------------------------------------------------------------------------------------------------------
3873: -------------------------------------------------------------------------------------------------------
3874: PROCEDURE insert_pqh_gms_interface
3875: (
3876: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
3877: p_period_name IN varchar2,
3878: p_project_id IN pqh_gl_interface.project_id%TYPE,
3879: p_task_id IN pqh_gl_interface.task_id%TYPE,
3880: p_award_id IN pqh_gl_interface.award_id%TYPE,
3874: PROCEDURE insert_pqh_gms_interface
3875: (
3876: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
3877: p_period_name IN varchar2,
3878: p_project_id IN pqh_gl_interface.project_id%TYPE,
3879: p_task_id IN pqh_gl_interface.task_id%TYPE,
3880: p_award_id IN pqh_gl_interface.award_id%TYPE,
3881: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
3882: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
3875: (
3876: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
3877: p_period_name IN varchar2,
3878: p_project_id IN pqh_gl_interface.project_id%TYPE,
3879: p_task_id IN pqh_gl_interface.task_id%TYPE,
3880: p_award_id IN pqh_gl_interface.award_id%TYPE,
3881: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
3882: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
3883: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3876: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
3877: p_period_name IN varchar2,
3878: p_project_id IN pqh_gl_interface.project_id%TYPE,
3879: p_task_id IN pqh_gl_interface.task_id%TYPE,
3880: p_award_id IN pqh_gl_interface.award_id%TYPE,
3881: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
3882: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
3883: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3884: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3877: p_period_name IN varchar2,
3878: p_project_id IN pqh_gl_interface.project_id%TYPE,
3879: p_task_id IN pqh_gl_interface.task_id%TYPE,
3880: p_award_id IN pqh_gl_interface.award_id%TYPE,
3881: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
3882: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
3883: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3884: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3885: ) IS
3878: p_project_id IN pqh_gl_interface.project_id%TYPE,
3879: p_task_id IN pqh_gl_interface.task_id%TYPE,
3880: p_award_id IN pqh_gl_interface.award_id%TYPE,
3881: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
3882: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
3883: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3884: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3885: ) IS
3886: /*
3879: p_task_id IN pqh_gl_interface.task_id%TYPE,
3880: p_award_id IN pqh_gl_interface.award_id%TYPE,
3881: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
3882: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
3883: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3884: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3885: ) IS
3886: /*
3887: This procedure will insert record into pqh_gl_interface
3880: p_award_id IN pqh_gl_interface.award_id%TYPE,
3881: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
3882: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
3883: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3884: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3885: ) IS
3886: /*
3887: This procedure will insert record into pqh_gl_interface
3888: If the same UOM is repeated more then once then we would update the unposted txn.
3883: p_amount IN pqh_gl_interface.amount_dr%TYPE,
3884: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3885: ) IS
3886: /*
3887: This procedure will insert record into pqh_gl_interface
3888: If the same UOM is repeated more then once then we would update the unposted txn.
3889: */
3890: --
3891: -- local variables
3894: l_count number(9) := 0 ;
3895:
3896: Cursor csr_pqh_gms_interface IS
3897: Select COUNT(*)
3898: From pqh_gl_interface
3899: Where budget_version_id = g_budget_version_id
3900: AND budget_detail_id = p_budget_detail_id
3901: AND p_period_name = p_period_name
3902: AND posting_type_cd = p_posting_type_cd
3918: OPEN csr_pqh_gms_interface;
3919: FETCH csr_pqh_gms_interface INTO l_count;
3920: CLOSE csr_pqh_gms_interface;
3921:
3922: hr_utility.set_location('l_count in Insert pqh_gl_interface : '||l_count,10);
3923:
3924: IF l_count <> 0 THEN
3925:
3926: -- this is a repeat of UOM , so update the first one adding the new amount
3923:
3924: IF l_count <> 0 THEN
3925:
3926: -- this is a repeat of UOM , so update the first one adding the new amount
3927: UPDATE pqh_gl_interface
3928: SET AMOUNT_DR = NVL(AMOUNT_DR,0) + NVL(p_amount,0)
3929: WHERE budget_version_id = g_budget_version_id
3930: AND budget_detail_id = p_budget_detail_id
3931: AND p_period_name = p_period_name
3941:
3942: ELSE
3943:
3944: -- insert this record
3945: INSERT INTO pqh_gl_interface
3946: (
3947: gl_interface_id,
3948: budget_version_id,
3949: budget_detail_id,
3943:
3944: -- insert this record
3945: INSERT INTO pqh_gl_interface
3946: (
3947: gl_interface_id,
3948: budget_version_id,
3949: budget_detail_id,
3950: period_name,
3951: project_id,
3962: posting_type_cd
3963: )
3964: VALUES
3965: (
3966: pqh_gl_interface_s.nextval,
3967: g_budget_version_id,
3968: p_budget_detail_id,
3969: p_period_name,
3970: p_project_id,
3996: ---------------------------------------------------------------------------------------------
3997: ---------------------------------------------------------------------------------------------
3998: PROCEDURE update_pqh_gms_interface
3999: (
4000: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
4001: p_period_name IN varchar2,
4002: p_project_id IN pqh_gl_interface.project_id%TYPE,
4003: p_task_id IN pqh_gl_interface.task_id%TYPE,
4004: p_award_id IN pqh_gl_interface.award_id%TYPE,
3998: PROCEDURE update_pqh_gms_interface
3999: (
4000: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
4001: p_period_name IN varchar2,
4002: p_project_id IN pqh_gl_interface.project_id%TYPE,
4003: p_task_id IN pqh_gl_interface.task_id%TYPE,
4004: p_award_id IN pqh_gl_interface.award_id%TYPE,
4005: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
4006: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
3999: (
4000: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
4001: p_period_name IN varchar2,
4002: p_project_id IN pqh_gl_interface.project_id%TYPE,
4003: p_task_id IN pqh_gl_interface.task_id%TYPE,
4004: p_award_id IN pqh_gl_interface.award_id%TYPE,
4005: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
4006: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
4007: p_amount IN pqh_gl_interface.amount_dr%TYPE,
4000: p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
4001: p_period_name IN varchar2,
4002: p_project_id IN pqh_gl_interface.project_id%TYPE,
4003: p_task_id IN pqh_gl_interface.task_id%TYPE,
4004: p_award_id IN pqh_gl_interface.award_id%TYPE,
4005: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
4006: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
4007: p_amount IN pqh_gl_interface.amount_dr%TYPE,
4008: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
4001: p_period_name IN varchar2,
4002: p_project_id IN pqh_gl_interface.project_id%TYPE,
4003: p_task_id IN pqh_gl_interface.task_id%TYPE,
4004: p_award_id IN pqh_gl_interface.award_id%TYPE,
4005: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
4006: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
4007: p_amount IN pqh_gl_interface.amount_dr%TYPE,
4008: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
4009: ) IS
4002: p_project_id IN pqh_gl_interface.project_id%TYPE,
4003: p_task_id IN pqh_gl_interface.task_id%TYPE,
4004: p_award_id IN pqh_gl_interface.award_id%TYPE,
4005: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
4006: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
4007: p_amount IN pqh_gl_interface.amount_dr%TYPE,
4008: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
4009: ) IS
4010: /*
4003: p_task_id IN pqh_gl_interface.task_id%TYPE,
4004: p_award_id IN pqh_gl_interface.award_id%TYPE,
4005: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
4006: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
4007: p_amount IN pqh_gl_interface.amount_dr%TYPE,
4008: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
4009: ) IS
4010: /*
4011: This procedure will update pqh_gl_interface and create a adjustment record
4004: p_award_id IN pqh_gl_interface.award_id%TYPE,
4005: p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
4006: p_organization_id IN pqh_gl_interface.organization_id%TYPE,
4007: p_amount IN pqh_gl_interface.amount_dr%TYPE,
4008: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
4009: ) IS
4010: /*
4011: This procedure will update pqh_gl_interface and create a adjustment record
4012: */
4007: p_amount IN pqh_gl_interface.amount_dr%TYPE,
4008: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
4009: ) IS
4010: /*
4011: This procedure will update pqh_gl_interface and create a adjustment record
4012: */
4013: --
4014: -- local variables
4015: --
4013: --
4014: -- local variables
4015: --
4016: l_proc varchar2(72) := g_package||'.update_pqh_gms_interface';
4017: l_amount_diff pqh_gl_interface.amount_dr%TYPE :=0;
4018: l_amount_dr pqh_gl_interface.amount_dr%TYPE :=0;
4019: l_amount_cr pqh_gl_interface.amount_cr%TYPE :=0;
4020: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
4021:
4014: -- local variables
4015: --
4016: l_proc varchar2(72) := g_package||'.update_pqh_gms_interface';
4017: l_amount_diff pqh_gl_interface.amount_dr%TYPE :=0;
4018: l_amount_dr pqh_gl_interface.amount_dr%TYPE :=0;
4019: l_amount_cr pqh_gl_interface.amount_cr%TYPE :=0;
4020: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
4021:
4022:
4015: --
4016: l_proc varchar2(72) := g_package||'.update_pqh_gms_interface';
4017: l_amount_diff pqh_gl_interface.amount_dr%TYPE :=0;
4018: l_amount_dr pqh_gl_interface.amount_dr%TYPE :=0;
4019: l_amount_cr pqh_gl_interface.amount_cr%TYPE :=0;
4020: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
4021:
4022:
4023: CURSOR csr_pqh_gms_interface IS
4016: l_proc varchar2(72) := g_package||'.update_pqh_gms_interface';
4017: l_amount_diff pqh_gl_interface.amount_dr%TYPE :=0;
4018: l_amount_dr pqh_gl_interface.amount_dr%TYPE :=0;
4019: l_amount_cr pqh_gl_interface.amount_cr%TYPE :=0;
4020: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
4021:
4022:
4023: CURSOR csr_pqh_gms_interface IS
4024: SELECT *
4021:
4022:
4023: CURSOR csr_pqh_gms_interface IS
4024: SELECT *
4025: FROM pqh_gl_interface
4026: WHERE budget_version_id = g_budget_version_id
4027: AND budget_detail_id = p_budget_detail_id
4028: AND period_name = p_period_name
4029: AND posting_type_cd = p_posting_type_cd
4043:
4044: hr_utility.set_location('Entering: '||l_proc, 5);
4045:
4046: OPEN csr_pqh_gms_interface;
4047: FETCH csr_pqh_gms_interface INTO l_pqh_gl_interface_rec;
4048:
4049: l_amount_diff := NVL(p_amount,0) - NVL(l_pqh_gl_interface_rec.amount_dr,0);
4050:
4051: IF l_amount_diff > 0 THEN
4045:
4046: OPEN csr_pqh_gms_interface;
4047: FETCH csr_pqh_gms_interface INTO l_pqh_gl_interface_rec;
4048:
4049: l_amount_diff := NVL(p_amount,0) - NVL(l_pqh_gl_interface_rec.amount_dr,0);
4050:
4051: IF l_amount_diff > 0 THEN
4052: -- debit as new is more then old
4053: l_amount_dr := l_amount_diff;
4054: ELSE
4055: -- credit as new is less then old
4056: l_amount_cr := (-1)*l_amount_diff;
4057: END IF;
4058: -- update the pqh_gl_interface table
4059: UPDATE pqh_gl_interface
4060: SET amount_dr = NVL(p_amount,0)
4061: WHERE CURRENT OF csr_pqh_gms_interface;
4062:
4055: -- credit as new is less then old
4056: l_amount_cr := (-1)*l_amount_diff;
4057: END IF;
4058: -- update the pqh_gl_interface table
4059: UPDATE pqh_gl_interface
4060: SET amount_dr = NVL(p_amount,0)
4061: WHERE CURRENT OF csr_pqh_gms_interface;
4062:
4063: CLOSE csr_pqh_gms_interface;
4064:
4065: -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
4066: IF NVL(l_amount_diff,0) <> 0 THEN
4067:
4068: INSERT INTO pqh_gl_interface
4069: (
4070: gl_interface_id,
4071: budget_version_id,
4072: budget_detail_id,
4066: IF NVL(l_amount_diff,0) <> 0 THEN
4067:
4068: INSERT INTO pqh_gl_interface
4069: (
4070: gl_interface_id,
4071: budget_version_id,
4072: budget_detail_id,
4073: period_name,
4074: project_id,
4085: posting_type_cd
4086: )
4087: VALUES
4088: (
4089: pqh_gl_interface_s.nextval,
4090: g_budget_version_id,
4091: p_budget_detail_id,
4092: p_period_name,
4093: p_project_id,
4123: PROCEDURE populate_pqh_gms_interface
4124: (
4125: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
4126: p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
4127: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
4128: )
4129: IS
4130: /*
4131: This procedure will update or insert GMS records into pqh_gl_interface if there was no error for
4127: p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
4128: )
4129: IS
4130: /*
4131: This procedure will update or insert GMS records into pqh_gl_interface if there was no error for
4132: the current budget detail record i.e g_detail_error = N
4133: if g_detail_error = Y then update the pqh_budget_details record with gl_status = ERROR
4134:
4135: Also it will Deduct a similar amount from Budget Commitments.
4139: --
4140: -- local variables
4141: --
4142: l_proc varchar2(72) := g_package||'.populate_pqh_gms_interface';
4143: l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
4144: l_uom_count number;
4145: l_amount number;
4146: l_amount_dr number;
4147: l_amount_cr number;
4157: p_expenditure_type IN varchar2,
4158: p_organization_id IN NUMBER,
4159: p_posting_type_cd IN VARCHAR2) IS
4160: Select *
4161: From pqh_gl_interface
4162: Where budget_version_id = p_budget_version_id
4163: AND budget_detail_id = p_budget_detail_id
4164: AND period_name = p_period_name
4165: AND posting_type_cd = p_posting_type_cd
4194: OPEN csr_budget_units;
4195: FETCH csr_budget_units into l_uom1, l_uom2,l_uom3;
4196: CLOSE csr_budget_units;
4197:
4198: -- loop thru the array and get populate the pqh_gl_interface table
4199:
4200: FOR i IN 1..g_period_amt_tab.COUNT
4201: LOOP
4202:
4216: p_award_id => g_period_amt_tab(i).award_id,
4217: p_expenditure_type => g_period_amt_tab(i).expenditure_type,
4218: p_organization_id => g_period_amt_tab(i).organization_id ,
4219: p_posting_type_cd =>'COMMITMENT');
4220: FETCH csr_pqh_gms_interface INTO l_pqh_gl_interface_rec;
4221: IF csr_pqh_gms_interface%FOUND THEN
4222: l_uom_count :=1;
4223: ELSE l_uom_count :=0;
4224: END IF;
4230: p_award_id => g_period_amt_tab(i).award_id,
4231: p_expenditure_type => g_period_amt_tab(i).expenditure_type,
4232: p_organization_id => g_period_amt_tab(i).organization_id ,
4233: p_posting_type_cd => 'BUDGET');
4234: FETCH csr_pqh_gms_interface INTO l_pqh_gl_interface_rec;
4235: IF csr_pqh_gms_interface%FOUND THEN
4236: CLOSE csr_pqh_gms_interface;
4237: hr_utility.set_message(8302, 'PQH_BUDGET_VERSION_NOT_POSTED');
4238: hr_utility.raise_error;
4239: END IF;
4240: --
4241: --We cannot Xfer a commitment greater than Budget amount posted for that period/Detail
4242: --
4243: IF(nvl(l_amount,-1) > 0 and l_amount < l_pqh_gl_interface_rec.amount_dr)
4244: THEN
4245: l_amount := l_pqh_gl_interface_rec.amount_dr;
4246: END IF;
4247:
4241: --We cannot Xfer a commitment greater than Budget amount posted for that period/Detail
4242: --
4243: IF(nvl(l_amount,-1) > 0 and l_amount < l_pqh_gl_interface_rec.amount_dr)
4244: THEN
4245: l_amount := l_pqh_gl_interface_rec.amount_dr;
4246: END IF;
4247:
4248: CLOSE csr_pqh_gms_interface;
4249:
4248: CLOSE csr_pqh_gms_interface;
4249:
4250:
4251: IF l_uom_count <> 0 THEN
4252: -- update pqh_gl_interface and create a adjustment txn
4253: update_pqh_gms_interface
4254: (
4255: p_budget_detail_id => p_budget_detail_id,
4256: p_period_name => g_period_amt_tab(i).period_name,
4262: p_amount => l_amount,
4263: p_posting_type_cd => p_posting_type_cd
4264: );
4265: ELSE
4266: -- insert into pqh_gl_interface
4267: insert_pqh_gms_interface
4268: (
4269: p_budget_detail_id => p_budget_detail_id,
4270: p_period_name => g_period_amt_tab(i).period_name,
4282: -- Deduct Commitment Amount posted, from Budget Commitment for that Detail/Period and create
4283: -- adjustment transaction for BUDGET
4284: --
4285: IF NVL(l_amount,0) <>0 THEN
4286: UPDATE pqh_gl_interface
4287: SET amount_dr = amount_dr - l_amount
4288: WHERE CURRENT OF csr_pqh_gms_interface;
4289: l_amount_dr :=0;
4290: l_amount_cr :=0;
4293: ELSE l_amount_cr := -1 * l_amount;
4294: END IF;
4295:
4296:
4297: INSERT INTO pqh_gl_interface
4298: (
4299: gl_interface_id,
4300: budget_version_id,
4301: budget_detail_id,
4295:
4296:
4297: INSERT INTO pqh_gl_interface
4298: (
4299: gl_interface_id,
4300: budget_version_id,
4301: budget_detail_id,
4302: period_name,
4303: project_id,
4314: posting_type_cd
4315: )
4316: VALUES
4317: (
4318: pqh_gl_interface_s.nextval,
4319: g_budget_version_id,
4320: p_budget_detail_id,
4321: g_period_amt_tab(i).period_name,
4322: g_period_amt_tab(i).project_id,
4367: /**************************************************************/
4368:
4369: PROCEDURE ins_gl_bc_run_fund_check
4370: ( p_packet_id IN gl_bc_packets.packet_id%TYPE
4371: ,p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE
4372: ,p_period_name IN pqh_gl_interface.period_name%TYPE
4373: ,p_period_year IN gl_period_statuses.period_year%TYPE
4374: ,p_period_num IN gl_period_statuses.period_num%TYPE
4375: ,p_quarter_num IN gl_period_statuses.quarter_num%TYPE
4368:
4369: PROCEDURE ins_gl_bc_run_fund_check
4370: ( p_packet_id IN gl_bc_packets.packet_id%TYPE
4371: ,p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE
4372: ,p_period_name IN pqh_gl_interface.period_name%TYPE
4373: ,p_period_year IN gl_period_statuses.period_year%TYPE
4374: ,p_period_num IN gl_period_statuses.period_num%TYPE
4375: ,p_quarter_num IN gl_period_statuses.quarter_num%TYPE
4376: ,p_currency_code IN pqh_gl_interface.currency_code%TYPE
4372: ,p_period_name IN pqh_gl_interface.period_name%TYPE
4373: ,p_period_year IN gl_period_statuses.period_year%TYPE
4374: ,p_period_num IN gl_period_statuses.period_num%TYPE
4375: ,p_quarter_num IN gl_period_statuses.quarter_num%TYPE
4376: ,p_currency_code IN pqh_gl_interface.currency_code%TYPE
4377: ,p_entered_dr IN pqh_gl_interface.amount_dr%TYPE
4378: ,p_entered_cr IN pqh_gl_interface.amount_cr%TYPE
4379: ,p_accounted_dr IN pqh_gl_interface.amount_dr%TYPE
4380: ,p_accounted_cr IN pqh_gl_interface.amount_cr%TYPE
4373: ,p_period_year IN gl_period_statuses.period_year%TYPE
4374: ,p_period_num IN gl_period_statuses.period_num%TYPE
4375: ,p_quarter_num IN gl_period_statuses.quarter_num%TYPE
4376: ,p_currency_code IN pqh_gl_interface.currency_code%TYPE
4377: ,p_entered_dr IN pqh_gl_interface.amount_dr%TYPE
4378: ,p_entered_cr IN pqh_gl_interface.amount_cr%TYPE
4379: ,p_accounted_dr IN pqh_gl_interface.amount_dr%TYPE
4380: ,p_accounted_cr IN pqh_gl_interface.amount_cr%TYPE
4381: ,p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE
4374: ,p_period_num IN gl_period_statuses.period_num%TYPE
4375: ,p_quarter_num IN gl_period_statuses.quarter_num%TYPE
4376: ,p_currency_code IN pqh_gl_interface.currency_code%TYPE
4377: ,p_entered_dr IN pqh_gl_interface.amount_dr%TYPE
4378: ,p_entered_cr IN pqh_gl_interface.amount_cr%TYPE
4379: ,p_accounted_dr IN pqh_gl_interface.amount_dr%TYPE
4380: ,p_accounted_cr IN pqh_gl_interface.amount_cr%TYPE
4381: ,p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE
4382: ,p_fc_mode IN varchar2
4375: ,p_quarter_num IN gl_period_statuses.quarter_num%TYPE
4376: ,p_currency_code IN pqh_gl_interface.currency_code%TYPE
4377: ,p_entered_dr IN pqh_gl_interface.amount_dr%TYPE
4378: ,p_entered_cr IN pqh_gl_interface.amount_cr%TYPE
4379: ,p_accounted_dr IN pqh_gl_interface.amount_dr%TYPE
4380: ,p_accounted_cr IN pqh_gl_interface.amount_cr%TYPE
4381: ,p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE
4382: ,p_fc_mode IN varchar2
4383: ,p_fc_success OUT NOCOPY boolean
4376: ,p_currency_code IN pqh_gl_interface.currency_code%TYPE
4377: ,p_entered_dr IN pqh_gl_interface.amount_dr%TYPE
4378: ,p_entered_cr IN pqh_gl_interface.amount_cr%TYPE
4379: ,p_accounted_dr IN pqh_gl_interface.amount_dr%TYPE
4380: ,p_accounted_cr IN pqh_gl_interface.amount_cr%TYPE
4381: ,p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE
4382: ,p_fc_mode IN varchar2
4383: ,p_fc_success OUT NOCOPY boolean
4384: ,p_fc_return OUT NOCOPY varchar2
4377: ,p_entered_dr IN pqh_gl_interface.amount_dr%TYPE
4378: ,p_entered_cr IN pqh_gl_interface.amount_cr%TYPE
4379: ,p_accounted_dr IN pqh_gl_interface.amount_dr%TYPE
4380: ,p_accounted_cr IN pqh_gl_interface.amount_cr%TYPE
4381: ,p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE
4382: ,p_fc_mode IN varchar2
4383: ,p_fc_success OUT NOCOPY boolean
4384: ,p_fc_return OUT NOCOPY varchar2
4385: )
4637: p_gms_batch_name IN VARCHAR2
4638: )
4639: IS
4640: /*
4641: This procedure ties back all the transactions posted into Oracle Grants Mgmt with records in pqh_gl_interface
4642: In case of failure the status in pqh_gl_interface is updated to error
4643: */
4644: --
4645: -- Cursor to get records rejected by import process
4638: )
4639: IS
4640: /*
4641: This procedure ties back all the transactions posted into Oracle Grants Mgmt with records in pqh_gl_interface
4642: In case of failure the status in pqh_gl_interface is updated to error
4643: */
4644: --
4645: -- Cursor to get records rejected by import process
4646: --
4676: And batch_name = p_gms_batch_name
4677: And transaction_status_code in ('P', 'I');
4678:
4679: --
4680: -- IF import for all records failed then update status in pqh_gl_interface to error
4681: --
4682: IF l_cnt > 0
4683: THEN
4684:
4705: p_message_text => pqh_gl_posting.get_gms_rejection_msg(reject_rec.rejection_code));
4706:
4707: begin
4708:
4709: UPDATE pqh_gl_interface
4710: SET status='ERROR',posting_date=sysdate
4711: WHERE period_name =g_gms_import_tab(l_int_id).period_name And
4712: project_id =g_gms_import_tab(l_int_id).project_id And
4713: task_id =g_gms_import_tab(l_int_id).task_id And
4736: hr_utility.set_location('Set Budget Detail status to Error', 25);
4737: begin
4738: UPDATE pqh_budget_details
4739: SET gl_status = 'ERROR'
4740: Where budget_detail_id in (select budget_detail_id from pqh_gl_interface where
4741: budget_version_id=g_budget_version_id
4742: And cost_allocation_keyflex_id is null
4743: And status='ERROR'
4744: );
4806: /**************************************************************/
4807:
4808: PROCEDURE populate_gms_tables IS
4809: /*
4810: This procedure transfers records from pqh_gl_interface to pa_transaction_interface_all,
4811: kicks off the TRANSACTION IMPORT program in GMS
4812: */
4813:
4814: ---------------Local Variables---------------------------------------------
4826: l_gms_transaction_source varchar2(30);
4827: l_amount NUMBER;
4828: tran_setup_exception EXCEPTION;
4829: tran_source_exception EXCEPTION;
4830: l_pqh_interface_rec pqh_gl_interface%ROWTYPE;
4831: l_log_context pqh_process_log.log_context%TYPE;
4832: l_proc varchar2(72) := g_package||'.populate_gms_interface';
4833: l_log_message varchar2(8000);
4834: cnt BINARY_INTEGER := 1;
4850: expenditure_type,organization_id,
4851: currency_code,
4852: SUM(NVL(amount_dr,0)) amount_dr,
4853: SUM(NVL(amount_cr,0)) amount_cr
4854: From pqh_gl_interface
4855: Where budget_version_id = g_budget_version_id
4856: AND posting_type_cd = 'COMMITMENT'
4857: AND status IS NULL
4858: AND posting_date IS NULL