[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_updated_by pa_expenditure_items.last_updated_by%TYPE := FND_GLOBAL.USER_ID;
g_last_update_login pa_expenditure_items.last_update_login%TYPE := FND_GLOBAL.LOGIN_ID;
* 2. Inserts Credit burden lines for reversing CDLs.
* -- FChecked burden amount is,
* ---- (cdl.burdened_amount - cdl.amount) for burden_amt_display_method = 'S'.
* ---- burden amount derived for burden_amt_display_method = 'D'.
* 3. Calls FC API.
* 4. Deletes CDLs that were created in this run and failed Funds Check .
*/
PROCEDURE costing_fc_proc ( p_calling_module IN VARCHAR2
,p_request_id IN NUMBER
,x_return_status OUT NOCOPY NUMBER
,x_error_code OUT NOCOPY VARCHAR2
,x_error_stage OUT NOCOPY NUMBER
)
IS
/*
* Processing related variables.
*/
l_calling_module VARCHAR2(20) ;
SELECT cdl.expenditure_item_id
,cdl.line_num
,cdl.line_type
,cdl.line_num_reversed
-- ,cdl.acct_raw_cost
,DECODE(ei.system_linkage_function ,'BTC' ,cdl.acct_burdened_cost
,DECODE(cdl.line_type, 'R', cdl.acct_raw_cost, cdl.acct_burdened_cost ))
-- ,cdl.denom_raw_cost
,DECODE(ei.system_linkage_function ,'BTC' ,cdl.denom_burdened_cost
,DECODE(cdl.line_type, 'R', cdl.denom_raw_cost, cdl.denom_burdened_cost ))
,cdl.acct_burdened_cost
,cdl.denom_burdened_cost
,cdl.project_id
,cdl.pa_date
,cdl.gl_date
,cdl.burden_sum_rejection_code
,cdl.burden_sum_source_run_id
,cdl.ind_compiled_set_id
,cdl.dr_code_combination_id
,glp.period_name
,ei.expenditure_item_date
,ei.expenditure_type
,ei.task_id
,NVL(ei.override_to_organization_id, exp.incurred_by_organization_id)
,NVL(ei.org_id, -99)
,ei.system_linkage_function
,NVL(pt.burden_amt_display_method, 'S')
,NVL(pt.burden_cost_flag, 'N')
,bv.budget_version_id
,DECODE(ei.system_linkage_function, 'BTC', -7777, DECODE(cdl.line_type, 'I', NULL, DECODE(cdl.line_num_reversed, NULL, -1, NULL)))
-- ,cdl.system_reference3 po_line_id -- R12 change
,ei.po_line_id po_line_id -- R12 change
,'EXP' pkt_reference1
,cdl.expenditure_item_id pkt_reference2
,cdl.line_num pkt_reference3
FROM pa_expenditure_items_all ei
,pa_cost_distribution_lines_all cdl
,pa_project_types_all pt
,pa_projects_all p
,pa_expenditures exp
,pa_budget_versions bv
,pa_budgetary_control_options pbct
,gl_period_statuses glp
WHERE ei.cost_distributed_flag = 'S'
AND ei.request_id = g_request_id
AND ei.cost_dist_rejection_code IS NULL
AND (ei.system_linkage_function IN ('VI') OR
(ei.system_linkage_function = 'BTC' AND ei.adjustment_type = 'BURDEN_RESUMMARIZE'))
AND ei.expenditure_id = exp.expenditure_id
AND ei.expenditure_item_id > l_ei_to_process_from
/*
* With I lines, this check is no longer valid.
* transfer_status_code check is not needed.
* AND cdl.transfer_status_code = DECODE(cdl.line_type, 'R', 'P', 'G')
*/
AND cdl.request_id = g_request_id
AND cdl.line_type in ('R', 'I')
AND cdl.expenditure_item_id = ei.expenditure_item_id
AND NVL(cdl.reversed_flag, 'N') <> 'Y'
AND cdl.project_id = p.project_id
AND p.project_type = pt.project_type
--R12 AND NVL(pt.org_Id, -99) = NVL(p.org_Id, -99)
AND pt.org_Id = p.org_Id
AND glp.application_id = 101
AND glp.set_of_books_id = g_sob_id
/* AND TRUNC(glp.END_DATE) = TRUNC(cdl.gl_date) Commented for 2843753, 2961161*/
/* Added for 2843753,2961161 */
AND TRUNC(cdl.gl_date) between TRUNC(glp.START_DATE) and TRUNC(glp.END_DATE)
AND pbct.project_id = bv.project_id
AND pbct.BDGT_CNTRL_FLAG = 'Y'
AND pbct.BUDGET_TYPE_CODE = bv.budget_type_code
AND (pbct.EXTERNAL_BUDGET_CODE = 'GL'
OR
pbct.EXTERNAL_BUDGET_CODE is NULL)
AND bv.project_id = cdl.project_id
AND bv.current_flag = 'Y'
AND bv.budget_status_code = 'B'
--FP M changes
And adjustment_period_flag = 'N'
ORDER BY cdl.expenditure_item_id
,cdl.line_num
;
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':ORACLE error occurred while selecting pa_implementations';
SELECT set_of_books_id
INTO g_sob_id
FROM pa_implementations;
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After selecting from pa_implementations. Sob_id is [' || TO_CHAR(g_sob_id) || ']' ;
* Select Expenditure_item_ids to process.
*
* We should get rid of this sql - because the columns selected here
* can be received from the pro*C process as arrays.
*/
l_stage := 200;
PA_DEBUG.g_err_stage := TO_CHAR(l_stage) || ':Fresh line Missing. Selecting Fresh line.';
SELECT cdl.expenditure_item_id
,cdl.line_num
,cdl.line_type
,cdl.line_num_reversed
--,cdl.acct_raw_cost
,DECODE(cdl.line_type, 'R', cdl.acct_raw_cost, cdl.acct_burdened_cost)
--,cdl.denom_raw_cost
,DECODE(cdl.line_type, 'R', cdl.denom_raw_cost, cdl.denom_burdened_cost)
,cdl.acct_burdened_cost
,cdl.denom_burdened_cost
,cdl.project_id
,cdl.pa_date
,cdl.gl_date
,cdl.burden_sum_rejection_code
,cdl.burden_sum_source_run_id
,cdl.ind_compiled_set_id
,cdl.dr_code_combination_id
,glp.period_name
,l_expenditure_item_date_tab(l_this_fetch)
,l_expenditure_type_tab(l_this_fetch)
,l_task_id_tab(l_this_fetch)
,l_exp_organization_id_tab(l_this_fetch)
,l_org_id_tab(l_this_fetch)
,NVL(pt.burden_amt_display_method, 'S')
,NVL(pt.burden_cost_flag, 'N')
,bv.budget_version_id
,DECODE(l_system_linkage_function_tab(l_this_fetch), 'BTC', -7777, DECODE(cdl.line_type, 'I', NULL, DECODE(cdl.line_num_reversed, NULL, -1, NULL)))
-- ,cdl.system_reference3 po_line_id -- R12 change
,(select ei.po_line_id
from pa_expenditure_items_all ei
where ei.expenditure_item_id = cdl.expenditure_item_id
) po_line_id -- R12 change
,'EXP'
,cdl.expenditure_item_id
,cdl.line_num
INTO l_expenditure_item_id_tab(l_this_fetch+1)
,l_line_num_tab(l_this_fetch+1)
,l_line_type_tab(l_this_fetch+1)
,l_line_num_reversed_tab(l_this_fetch+1)
,l_acct_raw_cost_tab(l_this_fetch+1)
,l_denom_raw_cost_tab(l_this_fetch+1)
,l_acct_burdened_cost_tab(l_this_fetch+1)
,l_denom_burdened_cost_tab(l_this_fetch+1)
,l_project_id_tab(l_this_fetch+1)
,l_pa_date_tab(l_this_fetch+1)
,l_gl_date_tab(l_this_fetch+1)
,l_burden_sum_rej_code_tab(l_this_fetch+1)
,l_burden_sum_source_run_id_tab(l_this_fetch+1)
,l_ind_compiled_set_id_tab(l_this_fetch+1)
,l_dr_code_combination_id_tab(l_this_fetch+1)
,l_gl_period_name_tab(l_this_fetch+1)
,l_expenditure_item_date_tab(l_this_fetch+1)
,l_expenditure_type_tab(l_this_fetch+1)
,l_task_id_tab(l_this_fetch+1)
,l_exp_organization_id_tab(l_this_fetch+1)
,l_org_id_tab(l_this_fetch+1)
,l_burden_amt_disp_method_tab(l_this_fetch+1)
,l_burden_cost_flag_tab(l_this_fetch+1)
,l_budget_version_id_tab(l_this_fetch+1)
,l_parent_bc_packet_id_tab(l_this_fetch+1)
,l_document_line_id_tab(l_this_fetch+1)
,l_pkt_reference1_tab(l_this_fetch+1)
,l_pkt_reference2_tab(l_this_fetch+1)
,l_pkt_reference3_tab(l_this_fetch+1)
FROM pa_cost_distribution_lines_all cdl
,pa_project_types_all pt
,pa_projects_all p
,pa_budget_versions bv
,pa_budgetary_control_options pbct
,gl_period_statuses glp
WHERE
/*
* With I lines, this check is no longer valid.
* transfer_status_code check is not needed.
* cdl.transfer_status_code = decode(cdl.line_type, 'R', 'P', 'G')
*/
cdl.line_num_reversed IS NULL -- ensures fresh line.
AND cdl.reversed_flag IS NULL -- ensures fresh line.
AND cdl.request_id = g_request_id
AND cdl.line_type in ('R', 'I')
AND cdl.expenditure_item_id = l_expenditure_item_id_tab(l_this_fetch)
AND p.project_id = cdl.project_id
AND p.project_type = pt.project_type
-- R12 AND NVL(pt.org_Id, -99) = NVL(p.org_Id, -99)
AND pt.org_Id = p.org_Id
AND glp.application_id = 101
AND glp.set_of_books_id = g_sob_id
/* AND TRUNC(glp.END_DATE) = TRUNC(cdl.gl_date) Commented for 2843753, 2961161*/
/* Added for 2843753,2961161 */
AND TRUNC(cdl.gl_date) between TRUNC(glp.START_DATE) and TRUNC(glp.END_DATE)
AND pbct.project_id = bv.project_id
AND pbct.BDGT_CNTRL_FLAG = 'Y'
AND pbct.BUDGET_TYPE_CODE = bv.budget_type_code
AND (pbct.EXTERNAL_BUDGET_CODE = 'GL'
OR
pbct.EXTERNAL_BUDGET_CODE is NULL)
AND bv.project_id = cdl.project_id
AND bv.current_flag = 'Y'
AND bv.budget_status_code = 'B'
--FP M changes
And adjustment_period_flag = 'N'
;
SELECT gl_bc_packets_s.NEXTVAL
INTO g_packet_id
FROM dual;
* Call Autonomous Procedure to insert the pl/sql tables into pa_bc_packets.
*/
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Before Calling populate_pa_bc_packets.';
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After Inserting Records into PA_BC_PACKETS.';
l_expenditure_item_id_tab.DELETE;
l_line_num_tab.DELETE;
l_line_type_tab.DELETE;
l_line_num_reversed_tab.DELETE;
l_acct_raw_cost_tab.DELETE;
l_denom_raw_cost_tab.DELETE;
l_acct_burdened_cost_tab.DELETE;
l_denom_burdened_cost_tab.DELETE;
l_project_id_tab.DELETE;
l_pa_date_tab.DELETE;
l_gl_date_tab.DELETE;
l_burden_sum_rej_code_tab.DELETE;
l_burden_sum_source_run_id_tab.DELETE;
l_ind_compiled_set_id_tab.DELETE;
l_dr_code_combination_id_tab.DELETE;
l_gl_period_name_tab.DELETE;
l_expenditure_item_date_tab.DELETE;
l_expenditure_type_tab.DELETE;
l_task_id_tab.DELETE;
l_exp_organization_id_tab.DELETE;
l_org_id_tab.DELETE;
l_burden_amt_disp_method_tab.DELETE;
l_burden_cost_flag_tab.DELETE;
l_budget_version_id_tab.DELETE;
l_pkt_reference1_Tab.DELETE;
l_pkt_reference2_Tab.DELETE;
l_pkt_reference3_Tab.DELETE;
END LOOP; -- End of loop to insert total number records.
SELECT btc_cdl.expenditure_item_id expenditure_item_id
,btc_cdl.project_id project_id
,btc_cdl.line_num line_num
,btc_cdl.cdl_rowid cdl_rowid
,btc_cdl.task_id task_id
,btc_cdl.top_task_id top_task_id
,btc_cdl.person_id person_id
,btc_cdl.organization_id organization_id
,btc_cdl.job_id job_id
,btc_cdl.expenditure_type expenditure_type
,btc_cdl.expenditure_category expenditure_category
,btc_cdl.system_linkage_function system_linkage_function
,btc_cdl.gl_start_date gl_start_date
,btc_cdl.encum_type_id encum_type_id
,btc_cdl.vendor_id vendor_id
,resmap.system_reference4 budget_version_id
,resmap.resource_list_id resource_list_id
,resmap.resource_list_member_id resource_list_member_id
,btc_cdl.entry_level_code entry_level_code
,btc_cdl.po_line_id po_line_id
,btc_cdl.system_reference2 po_header_id
,decode(btc_cdl.burden_amt_disp_method,'D','BURDEN','RAW') pkt_line_type
--FP M changes
,btc_cdl.dr_code_combination_id dr_ccid
FROM pa_res_map_btc_v btc_cdl
,pa_mappable_txns_tmp resmap
WHERE btc_cdl.request_id = g_request_id
AND resmap.system_reference3 = btc_cdl.request_id
AND resmap.system_reference2 = btc_cdl.line_num
AND resmap.system_reference1 = btc_cdl.expenditure_item_id
ORDER BY btc_cdl.resource_list_id
,btc_cdl.project_id
,btc_cdl.budget_version_id;
SELECT distinct btc_rl.resource_list_id
FROM pa_res_map_btc_v btc_rl
WHERE btc_rl.request_id = g_request_id;
SELECT btc_cdl.expenditure_item_id expenditure_item_id
,btc_cdl.project_id project_id
,btc_cdl.line_num line_num
,btc_cdl.cdl_rowid cdl_rowid
,btc_cdl.task_id task_id
,btc_cdl.top_task_id top_task_id
,btc_cdl.person_id person_id
,btc_cdl.organization_id organization_id
,btc_cdl.job_id job_id
,btc_cdl.expenditure_type expenditure_type
,btc_cdl.expenditure_category expenditure_category
,btc_cdl.system_linkage_function system_linkage_function
,btc_cdl.gl_start_date gl_start_date
,btc_cdl.encum_type_id encum_type_id
,btc_cdl.vendor_id vendor_id
/* added for Cwk changes */
,btc_cdl.budget_version_id budget_version_id
,btc_cdl.resource_list_id resource_list_id
,btc_cdl.entry_level_code entry_level_code
,g_request_id request_id
FROM pa_res_map_btc_v btc_cdl
WHERE btc_cdl.request_id = g_request_id
ORDER BY NVL(btc_cdl.resource_list_id,0)
,btc_cdl.project_id
,btc_cdl.budget_version_id
;
l_resmap_exp_item_id.delete;
l_resmap_project_id.delete;
l_resmap_line_num.delete;
l_resmap_cdl_rowid.delete;
l_resmap_task_id.delete;
l_resmap_top_task_id.delete;
l_resmap_person_id.delete;
l_resmap_organization_id.delete;
l_resmap_job_id.delete;
l_resmap_exp_type.delete;
l_resmap_exp_category.delete;
l_resmap_sys_link_func.delete;
l_resmap_gl_start_date.delete;
l_resmap_encum_type_id.delete;
l_resmap_vendor_id.delete;
l_resmap_budget_version_id.delete;
l_resmap_resource_list_id.delete;
l_resmap_entry_level_code.delete;
Insert into PA_MAPPABLE_TXNS_TMP
(txn_id,
person_id,
job_id,
organization_id,
vendor_id,
expenditure_type,
event_type,
non_labor_resource,
expenditure_category,
revenue_category,
non_labor_resource_org_id,
event_type_classification,
system_linkage_function,
project_role_id,
resource_list_id,
system_reference1,
system_reference2,
system_reference3,
system_reference4,
system_reference5
)
SELECT
pa_mappable_txns_tmp_s.NEXTVAL
,l_resmap_person_id(i)
,l_resmap_job_id(i)
,l_resmap_organization_id(i)
,l_resmap_vendor_id(i)
,l_resmap_exp_type(i)
,NULL
,NULL
,l_resmap_exp_category(i)
,NULL
,NULL
,NULL
,l_resmap_sys_link_func(i)
,NULL
,l_resmap_resource_list_id(i)
,l_resmap_exp_item_id(i)
,l_resmap_line_num(i)
,l_resmap_request_id(i)
,l_resmap_budget_version_id(i)
,NULL
FROM DUAL ;
l_debug_stage := l_stage||':'||'Num of Rows Inserted into ResTmpTable['||sql%Rowcount||']';
l_expenditure_item_id_tab.delete;
l_budget_ccid_tab.delete;
l_cost_dist_rejection_code_tab.delete;
l_line_num_tab.delete;
l_cdl_rowid_tab.delete;
l_encum_type_id_tab.delete;
l_budget_line_id_tab.delete;
l_budget_ver_id_tab.delete;
* The following tables will be used for BULK update later.
*/
l_expenditure_item_id_tab(l_counter) := c1_rec.expenditure_item_id;
* Update the FC related columns in the CDL.
* If Mapping was successful.
*
* Should modify this update rowid based for performance.
*/
l_debug_stage := TO_CHAR(l_stage) || ':ORACLE error occurred while Bulk updating BC columns in CDL.' ;
UPDATE pa_cost_distribution_lines_all cdl
SET cdl.budget_ccid = l_budget_ccid_tab(l_counter)
--r12
,cdl.budget_version_id = l_budget_ver_id_tab(l_counter)
,cdl.budget_line_id = l_budget_line_id_tab(l_counter)
,cdl.encumbrance_amount = cdl.acct_burdened_cost
,cdl.liquidate_encum_flag = 'Y'
,cdl.ENCUMBRANCE_TYPE_ID = l_encum_type_id_tab(l_counter)
WHERE cdl.rowid = l_cdl_rowid_tab(l_counter)
AND l_budget_ccid_tab(l_counter) IS NOT NULL
;
l_debug_stage := TO_CHAR(l_stage) || ':No.of CDLs updated with FC columns['||TO_CHAR(SQL%ROWCOUNT)||']';
* Update ei.cost_dist_rejection_code if Mapping
* Failed.
*/
l_stage := 700;
UPDATE pa_expenditure_items ei
SET ei.cost_dist_rejection_code = l_cost_dist_rejection_code_tab(l_counter)
WHERE ei.expenditure_item_id = l_expenditure_item_id_tab(l_counter)
AND l_budget_ccid_tab(l_counter) IS NULL
;
l_debug_stage := TO_CHAR(l_stage)||':No.of CDLs updated with rej_code['||TO_CHAR(SQL%ROWCOUNT)||']';
* Delete the CDLs which failed resource-mapping.
*/
l_stage := 800;
DELETE FROM pa_cost_distribution_lines cdl
WHERE cdl.rowid = l_cdl_rowid_tab(l_counter)
AND l_budget_ccid_tab(l_counter) IS NULL
;
l_debug_stage := TO_CHAR(l_stage)||':No.of CDLs deleted for Mapping failure['||TO_CHAR(SQL%ROWCOUNT)||']';
* This procedure deletes the CDLs that failed Funds-Check.
*/
PROCEDURE process_rejected_exp_items ( x_return_status OUT NOCOPY NUMBER
,x_error_code OUT NOCOPY VARCHAR2
,x_error_stage OUT NOCOPY VARCHAR2
)
IS
l_rejected_eiid_tab PA_PLSQL_DATATYPES.IdTabTyp;
l_records_deleted NUMBER := 0; /* Added for bug#3094341 */
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':ORACLE error occurred while selecting rejected EIs.';
SELECT ei.expenditure_item_id
BULK COLLECT
INTO l_rejected_eiid_tab
FROM pa_expenditure_items ei
WHERE ei.cost_dist_rejection_code IS NOT NULL
AND ei.cost_distributed_flag = 'S'
AND ei.request_id = g_request_id
AND (ei.system_linkage_function IN ('VI')
OR (ei.system_linkage_function in ('ST','OT') AND ei.po_line_id IS NOT NULL)
OR (ei.system_linkage_function = 'BTC' AND ei.adjustment_type = 'BURDEN_RESUMMARIZE')
);
TO_CHAR(g_request_id) || '] are being deleted';
* Delete CDLs that were rejected during FC.
* These records will be marked with a NOT NULL value for ei.cost_dist_rejection_code.
*
* I think its enough to check for eiid and request_id to identify CDLs
* that were created during this run. But if its needed to check against line_num
* also, then line_num also has to be selected above from pa_bc_packets.
*/
l_stage := 300;
DELETE
FROM pa_cost_distribution_lines cdl
WHERE cdl.request_id = g_request_id
AND cdl.expenditure_item_id = l_rejected_eiid_tab(i)
AND NVL(cdl.reversed_flag, 'N') <> 'Y'
AND cdl.transfer_status_code <> 'V'
RETURNING cdl.expenditure_item_id, cdl.line_num, cdl.line_type, cdl.parent_line_num
,nvl(cdl.denom_burdened_cost,0), nvl(cdl.acct_burdened_cost,0), nvl(cdl.burdened_cost,0), nvl(cdl.project_burdened_cost,0)
BULK COLLECT INTO l_del_cdl_eiid_tab, l_del_cdl_line_num_tab, l_del_cdl_line_type_tab, l_del_cdl_parent_tab
,l_del_cdl_dbc_tab, l_del_cdl_abc_tab, l_del_cdl_pfbc_tab, l_del_cdl_pbc_tab
;
/* Added for Bug fix to get the no of rec's deleted. Bug 3094341 */
l_records_deleted := SQL%ROWCOUNT;
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':No. of CDLs deleted [' || TO_CHAR(SQL%ROWCOUNT) || ']';
/* Check added to check if no of rec's deleted > 0 then go to the updates.
Added for Bug 3094341
*/
IF l_records_deleted > 0 THEN -----------------------------------------{
IF (l_debug_mode = 'Y')
THEN
/*
* Modified the Looping to go by l_del_cdl_eiid_tab instead of l_expenditure_item_id_tab.
*/
FOR i IN l_del_cdl_eiid_tab.FIRST .. l_del_cdl_eiid_tab.LAST
LOOP
pa_debug.g_err_stage := 'deleted eiid [' || l_del_cdl_eiid_tab(i) ||
'] line_num [' || l_del_cdl_line_num_tab(i) ||
'] line_type [' || l_del_cdl_line_type_tab(i) ||
'] parent line [' || l_del_cdl_parent_tab(i) ||
'] dbc [' || l_del_cdl_dbc_tab(i) ||
'] abc [' || l_del_cdl_abc_tab(i) ||
'] pfbc [' || l_del_cdl_pfbc_tab(i) ||
'] pbc [' || l_del_cdl_pbc_tab(i) ||
']';
* Update the reversed_flag of the original CDLs, the reversing and new
* CDLs of whom where deleted above because of failed FC.
* Because, since the reversing and new are deleted, the original's
* reversing flag should be brought back to NULL.
* The request_id of the original is updated with the current request_id
* when setting reversed_flag to 'Y'. So, we can make use of that.
*/
l_stage := 400;
UPDATE pa_cost_distribution_lines cdl
SET cdl.reversed_flag = NULL
WHERE NVL(cdl.reversed_flag, 'N') = 'Y'
AND cdl.request_id = g_request_id
AND cdl.expenditure_item_id = l_rejected_eiid_tab(i)
;
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Reversed Flag updated for [' || TO_CHAR(SQL%ROWCOUNT) || '] CDLs';
| For failed transactions, if the CDL being deleted is of line type I, |
| then the corresponding burden change amount has to be deducted from |
| the parent raw line. |
+=======================================================================*/
l_stage := 500;
UPDATE pa_cost_distribution_lines cdl
SET cdl.denom_burdened_change = cdl.denom_burdened_change - l_del_cdl_dbc_tab(i)
,cdl.acct_burdened_change = cdl.acct_burdened_change - l_del_cdl_abc_tab(i)
,cdl.projfunc_burdened_change = cdl.projfunc_burdened_change - l_del_cdl_pfbc_tab(i)
,cdl.project_burdened_change = cdl.project_burdened_change - l_del_cdl_pbc_tab(i)
WHERE cdl.expenditure_item_id = l_del_cdl_eiid_tab(i)
and cdl.line_num = l_del_cdl_parent_tab(i)
and l_del_cdl_line_type_tab(i) = 'I'
;
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Burden Change Bucket updated for [' || TO_CHAR(SQL%ROWCOUNT) || '] CDLs';
END IF; -------------------------------------------------------------} /* l_records_deleted? */
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Before Inserting Raw lines into pa_bc_packets.' ;
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':ORACLE error occurred while Bulk inserting into pa_bc_packets.' ;
* Insert Raw lines.
*
* For New lines, the FC process will insert the debit burden lines.
* To distinguish a new line from a reversal line, the parent_bc_packet_id
* is inserted as -1 for a new line.
* For a reversal line, the value will be NULL.
*
* The transfer_status_code Join is used to make use of PA_COST_DISTRIBUTION_LINES_N2
* index.
*
* burden_cost_flag is populated 'N' for Raw line.
*/
l_stage := 200;
INSERT
INTO pa_bc_packets( packet_id
,project_id
,task_id
,budget_version_id
,expenditure_type
,expenditure_item_date
,period_name
,pa_date
,gl_date
,set_of_books_id
,je_category_name
,je_source_name
,status_code
,document_type
,funds_process_mode
,burden_cost_flag
,expenditure_organization_id
,document_header_id
,document_distribution_id
,document_line_id
,txn_ccid
,accounted_dr
,entered_dr
,bc_packet_id
,parent_bc_packet_id
,org_id
,balance_posted_flag
,program_id
,program_application_id
,program_update_date
,last_update_date
,last_updated_by
,created_by
,creation_date
,last_update_login
,request_id
,reference1
,reference2
,reference3
)
SELECT g_packet_id -- packet_id
,l_project_id_tab(i) -- project_id
,l_task_id_tab(i) -- task_id
,l_budget_version_id_tab(i) -- budget_version_id
,l_expenditure_type_tab(i) -- expenditure_type
,l_expenditure_item_date_tab(i) -- expenditure_item_date
,l_gl_period_name_tab(i) -- period_name
,l_pa_date_tab(i) -- pa_date
,l_gl_date_tab(i) -- gl_date
,g_sob_id -- set_of_book_id
,'Project Accounting' -- je_category_name
,'Expenditures' -- je_source_name
,'P' -- status_code
,'EXP' -- document_type
,'T' -- funds_process_mode
,'N' -- burden_cost_flag
,l_exp_organization_id_tab(i) -- expenditure_organization_id
,l_expenditure_item_id_tab(i) -- document_header_id
,l_line_num_tab(i) -- document_distribution_id
,l_document_line_id_tab(i) -- document_line_id
,l_dr_code_combination_id_tab(i) -- txn_ccid
,l_acct_raw_cost_tab(i) -- accounted_dr
,l_acct_raw_cost_tab(i) -- entered_dr
,pa_bc_packets_s.NEXTVAL -- bc_packet_id
,l_parent_bc_packet_id_tab(i) -- parent_bc_packet_id
,l_org_id_tab(i) -- org_id
,'N' -- balance_posted_flag
,g_program_id -- program_id
,g_program_application_id -- program_application_id
,SYSDATE -- program_update_date
,SYSDATE -- last_update_date
,g_last_updated_by -- last_updated_by
,g_created_by -- created_by
,SYSDATE -- creation_date
,g_last_update_login -- last_update_login
,g_request_id
,l_pkt_reference1_Tab(i)
,l_pkt_reference2_Tab(i)
,l_pkt_reference3_Tab(i)
FROM DUAL
;
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After Inserting Raw lines into pa_bc_packets.' ;
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Inserted [' || TO_CHAR(l_records_affected) ||
'] Raw lines into pa_bc_packets.';
* Insert Burden lines - if the project is burdened.
* For burden_amt_display_method = 'S', Burdened amount is stored in the raw
* cdl itself.
* Entered_dr = Burdened_amount - raw_cost
*
* Since, both the reversed and the reversing lines have the same request_id
* (though the reversed line could have been created in a previous run),
* to identify CDLs that were created in this run, we select those records
* with cdl.reversed_flag <> 'Y'.
*
* Burden lines are inserted in this level - only if this is a reversing line.
* For NEW lines, the FC process creates the Burden lines.
* For this, we go by the cdl.line_num_reversed.
*
* Identifying the raw line in bc_packets corresponding to the burden line
* that we are inserting.
*
* cdl.eiid = bcpk.eiid
* bcpk.parent_bc_packet_id is null
* because, if parent_bc_packet_id is NOT NULL, it means its a fresh raw line.
* for fresh line, we wouldnt' be inserting burden lines in the first place.
* if its just another burden line, the parent_bc_packet_id will have the
* bc_packet_id of the raw line.
*
* burden_cost_flag is populated 'O' for Burden lines.
*/
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Inserting Burden lines into pa_bc_packets (display_method = S).' ;
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':ORACLE error occurred while inserting into pa_bc_packets' ;
INSERT
INTO pa_bc_packets( packet_id
,project_id
,task_id
,budget_version_id
,expenditure_type
,expenditure_item_date
,period_name
,pa_date
,gl_date
,set_of_books_id
,je_category_name
,je_source_name
,status_code
,document_type
,funds_process_mode
,burden_cost_flag
,expenditure_organization_id
,document_header_id
,document_distribution_id
,document_line_id
,txn_ccid
,accounted_dr
,entered_dr
,bc_packet_id
,parent_bc_packet_id
,org_id
,balance_posted_flag
,program_id
,program_application_id
,program_update_date
,last_update_date
,last_updated_by
,created_by
,creation_date
,last_update_login
,request_id
,reference1
,reference2
,reference3
)
SELECT g_packet_id -- packet_id
,l_project_id_tab(i) -- project_id
,l_task_id_tab(i) -- task_id
,l_budget_version_id_tab(i) -- budget_version_id
,l_expenditure_type_tab(i) -- expenditure_type
,l_expenditure_item_date_tab(i) -- expenditure_item_date
,l_gl_period_name_tab(i) -- period_name
,l_pa_date_tab(i) -- pa_date
,l_gl_date_tab(i) -- gl_date
,g_sob_id -- set_of_books_id
,'Project Accounting' -- je_category_name
,'Expendiures' -- je_source_name
,'P' -- status_code
,'EXP' -- document_type
,'T' -- funds_process_mode
,'O' -- funds_process_mode
,l_exp_organization_id_tab(i) -- expenditure_organization_id
,l_expenditure_item_id_tab(i) -- document_header_id
,l_line_num_tab(i) -- document_distribution_id
,l_document_line_id_tab(i) -- document_line_id
,l_dr_code_combination_id_tab(i) -- txn_ccid
,(l_acct_burdened_cost_tab(i) - l_acct_raw_cost_tab(i)) -- accounted_dr
,(l_acct_burdened_cost_tab(i) - l_acct_raw_cost_tab(i)) -- entered_dr
,pa_bc_packets_s.NEXTVAL -- pa_bc_packet_id
--,DECODE(l_line_num_reversed_tab(i), NULL, NULL, bcpk.bc_packet_id) -- parent_bc_packet_id
,DECODE(l_line_type_tab(i), 'I', NULL, DECODE(l_line_num_reversed_tab(i), NULL, NULL, bcpk.bc_packet_id)) -- parent_bc_packet_id
,l_org_id_tab(i) -- org_id
,'N' -- balance_posted_flag
,g_program_id -- program_id
,g_program_application_id -- program_application_id
,SYSDATE -- program_update_date
,SYSDATE -- last_update_date
,g_last_updated_by -- last_updated_by
,g_created_by -- created_by
-- ,100 -- created_by
,SYSDATE -- creation_date
,g_last_update_login -- last_update_login
,g_request_id
,l_pkt_reference1_Tab(i)
,l_pkt_reference2_Tab(i)
,l_pkt_reference3_Tab(i)
FROM pa_bc_packets bcpk -- to get the raw line in bc_packets
WHERE l_line_num_reversed_tab(i) IS NOT NULL
AND l_burden_amt_disp_method_tab(i) = 'S'
AND l_burden_cost_flag_tab(i) = 'Y'
AND bcpk.document_header_id = l_expenditure_item_id_tab(i)
AND bcpk.parent_bc_packet_id IS NULL
AND bcpk.packet_id = g_packet_id
;
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After Inserting Burden lines into pa_bc_packets (display_method = S).' ;
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Inserted [' || TO_CHAR(l_records_affected) ||
'] Burden lines into pa_bc_packets';
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Inserting Burden lines into pa_bc_packets (disp_method = D).' ;
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':ORACLE error occurred while inserting Burden lines into bc_pk dis_meth = D' ;
INSERT
INTO pa_bc_packets( packet_id
,project_id
,task_id
,budget_version_id
,expenditure_type
,expenditure_item_date
,period_name
,pa_date
,gl_date
,set_of_books_id
,je_category_name
,je_source_name
,status_code
,document_type
,funds_process_mode
,burden_cost_flag
,expenditure_organization_id
,document_header_id
,document_distribution_id
,document_line_id
,txn_ccid
,accounted_dr
,entered_dr
,bc_packet_id
,parent_bc_packet_id
,org_id
,balance_posted_flag
,program_id
,program_application_id
,program_update_date
,last_update_date
,last_updated_by
,created_by
,creation_date
,last_update_login
,request_id
,reference1
,reference2
,reference3
)
SELECT g_packet_id -- packet_id
,l_project_id_tab(i) -- project_id
,l_task_id_tab(i) -- task_id
,l_budget_version_id_tab(i) -- budget_version_id
,icc.expenditure_type -- expenditure_type
,l_expenditure_item_date_tab(i) -- expenditure_item_date
,l_gl_period_name_tab(i) -- period_name
,l_pa_date_tab(i) -- pa_date
,l_gl_date_tab(i) -- gl_date
,g_sob_id -- set_of_book_id
,'Project Accounting' -- je_category_name
,'Expenditures' -- je_source_name
,'P' -- status_code
,'EXP' -- document_type
,'T' -- funds_process_mode
,'O' -- funds_process_mode
,l_exp_organization_id_tab(i) -- expenditure_organization_id
,l_expenditure_item_id_tab(i) -- document_header_id
,l_line_num_tab(i) -- document_distribution_id
,l_document_line_id_tab(i) -- document_line_id
,l_dr_code_combination_id_tab(i) -- txn_ccid
,ROUND(l_acct_raw_cost_tab(i) * cm.compiled_multiplier,2) -- accounted_dr
,ROUND(l_acct_raw_cost_tab(i) * cm.compiled_multiplier,2) -- entered_dr
,pa_bc_packets_s.NEXTVAL -- bc_packet_id
--,DECODE(l_line_num_reversed_tab(i), NULL, NULL, bcpk.bc_packet_id) -- parent_bc_packet_id
,DECODE(l_line_type_tab(i), 'I', NULL, DECODE(l_line_num_reversed_tab(i), NULL, NULL, bcpk.bc_packet_id)) -- parent_bc_packet_id
,l_org_id_tab(i) -- org_id
,'N' -- balance_posted_flag
,g_program_id -- program_id
,g_program_application_id -- program_application_id
,SYSDATE -- program_update_date
,SYSDATE -- last_update_date
,g_last_updated_by -- last_updated_by
,g_created_by -- created_by
,SYSDATE -- creation_date
,g_last_update_login -- last_update_login
,g_request_id
,l_pkt_reference1_tab(i)
,l_pkt_reference2_tab(i)
,l_pkt_reference3_tab(i)
FROM PA_IND_COST_CODES ICC,
PA_COMPILED_MULTIPLIERS CM,
PA_IND_COMPILED_SETS ICS,
PA_COST_BASE_EXP_TYPES CBET,
PA_COST_BASES CB,
PA_IND_RATE_SCH_REVISIONS IRSR,
PA_IND_RATE_SCHEDULES_ALL_BG IRS
,PA_BC_PACKETS bcpk
WHERE ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
AND irs.ind_rate_sch_id = irsr.ind_rate_sch_id
AND irsr.cost_plus_structure = cbet.cost_plus_structure
AND cbet.cost_base = cm.cost_base
AND cb.cost_base = cbet.cost_base
AND cb.cost_base_type = cbet.cost_base_type
AND cbet.cost_base_type = 'INDIRECT COST'
AND cbet.expenditure_type = l_expenditure_type_tab(i)
AND ics.organization_id = l_exp_organization_id_tab(i)
AND ics.cost_base = cbet.cost_base
AND ics.ind_compiled_set_id = l_ind_compiled_set_id_tab(i)
AND icc.ind_cost_code = cm.ind_cost_code
AND cm.ind_compiled_set_id = l_ind_compiled_set_id_tab(i)
AND l_burden_sum_rej_code_tab(i) IS NULL
AND l_burden_sum_source_run_id_tab(i) = -9999
AND l_burden_amt_disp_method_tab(i) = 'D'
AND l_burden_cost_flag_tab(i) = 'Y'
AND l_line_num_reversed_tab(i) IS NOT NULL -- reversing line
AND bcpk.document_header_id = l_expenditure_item_id_tab(i)
AND bcpk.parent_bc_packet_id IS NULL
AND bcpk.packet_id = g_packet_id
;
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Inserted [' || TO_CHAR(l_records_affected) || '] Burden lines into pa_bc_packets';
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After Inserting Burden lines into pa_bc_packets (disp_method = D).' ;
l_deleted_eiids_tab PA_PLSQL_DATATYPES.IdTabTyp;
l_cdls_deleted NUMBER := 0;
SELECT cdl.expenditure_item_id expenditure_item_id
,cdl.budget_ccid budget_ccid
,cdl.line_num line_num
FROM pa_cost_distribution_lines cdl
,pa_expenditure_items ei
WHERE (ei.system_linkage_function IN ('VI')
--FP M changes
OR (ei.system_linkage_function in ('ST','OT') AND ei.po_line_id IS NOT NULL))
AND ei.expenditure_item_id = cdl.expenditure_item_id
AND ei.cost_burden_distributed_flag = 'S'
AND ei.cost_distributed_flag = 'Y'
AND ei.ind_cost_dist_rejection_code IS NULL
AND cdl.line_type ='R'
AND cdl.line_num_reversed IS NULL
AND cdl.reversed_flag IS NULL
AND pa_funds_control_utils.get_bdgt_link(NVL( cdl.project_id, -99)
,'STD'
) = 'Y'
;
SELECT cdl.dr_code_combination_id
FROM pa_cost_distribution_lines cdl
WHERE cdl.expenditure_item_id = p_cur_eiid
AND cdl.request_id = p_cur_request_id
AND cdl.line_type = 'D'
;
* finally delete 'D' and 'C' lines which have ei.dist_rejection_code
* as not null.
*/
pa_debug.init_err_stack('pa_bc_costing.validate_debit_lines');
* Update cost_dist_rejection_code.
*/
FORALL i IN 1 .. l_expenditure_item_id_tab.LAST
UPDATE pa_expenditure_items ei
SET ei.ind_cost_dist_rejection_code = l_cost_dist_rejection_code_tab(i)
WHERE ei.expenditure_item_id = l_expenditure_item_id_tab(i)
AND l_cost_dist_rejection_code_tab(i) IS NOT NULL
AND ei.ind_cost_dist_rejection_code IS NULL
;
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':No. of EIs updated with cost_dist_rejection_code [' ||
TO_CHAR(SQL%ROWCOUNT) || ']' ;
* Delete errored CDLs.
*/
FORALL i IN 1 .. l_expenditure_item_id_tab.LAST
DELETE
FROM pa_cost_distribution_lines cdl
WHERE cdl.line_type IN ('C', 'D')
AND cdl.request_id = g_request_id
AND cdl.expenditure_item_id = l_expenditure_item_id_tab(i)
AND l_cost_dist_rejection_code_tab(i) IS NOT NULL
;
l_cdls_deleted := SQL%ROWCOUNT;
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':No. of CDLs deleted should be an even number.';
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':No. of CDLs deleted [' || TO_CHAR(l_cdls_deleted) || ']' ;
SELECT cdl.expenditure_item_id
,cdl.line_num
,cdl.line_type
,cdl.line_num_reversed
,decode(cdl.line_type, 'I', ei.acct_raw_cost, cdl.acct_raw_cost)
,decode(cdl.line_type, 'I', ei.denom_raw_cost, cdl.denom_raw_cost)
,cdl.acct_burdened_cost
,cdl.denom_burdened_cost
,cdl.project_id
,cdl.pa_date
,cdl.gl_date
,cdl.burden_sum_rejection_code
,cdl.burden_sum_source_run_id
,cdl.ind_compiled_set_id
,cdl.dr_code_combination_id
,TO_NUMBER(cdl.system_reference2) po_header_id
,glp.period_name
,ei.expenditure_item_date
,ei.expenditure_type
,cdl.task_id
,ei.po_line_id
,NVL(ei.override_to_organization_id, exp.incurred_by_organization_id)
,NVL(ei.org_id, -99)
,NVL(pt.burden_amt_display_method, 'S')
,NVL(pt.burden_cost_flag, 'N')
,bv.budget_version_id
,'EXP' reference1
,cdl.expenditure_item_id reference2
,cdl.line_num reference3
FROM pa_expenditure_items_all ei
,pa_cost_distribution_lines_all cdl
,pa_project_types_all pt
,pa_projects_all p
,pa_expenditures exp
,pa_budget_versions bv
,pa_budgetary_control_options pbct
,gl_period_statuses glp
,po_distributions_all pod /* 6989758 */
WHERE ei.cost_distributed_flag = 'S'
AND ei.request_id = g_request_id
AND ei.cost_dist_rejection_code IS NULL
AND ei.denom_raw_cost IS NOT NULL
AND ei.system_linkage_function IN ('ST')
AND ei.expenditure_id = exp.expenditure_id
AND ei.expenditure_item_id > l_ei_to_process_from
AND cdl.request_id = g_request_id
AND cdl.line_type in ('R', 'I')
AND cdl.expenditure_item_id = ei.expenditure_item_id
AND NVL(cdl.reversed_flag, 'N') <> 'Y'
AND cdl.project_id = p.project_id
AND p.project_type = pt.project_type
-- R12 AND NVL(pt.org_Id, -99) = NVL(p.org_Id, -99)
AND pt.org_Id = p.org_Id
AND glp.application_id = 101
AND glp.set_of_books_id = g_sob_id
AND TRUNC(cdl.gl_date) BETWEEN TRUNC(glp.START_DATE) AND TRUNC(glp.END_DATE)
AND pbct.project_id = bv.project_id
AND pbct.BDGT_CNTRL_FLAG = 'Y'
AND pbct.BUDGET_TYPE_CODE = bv.budget_type_code
AND (pbct.EXTERNAL_BUDGET_CODE = 'GL'
OR
pbct.EXTERNAL_BUDGET_CODE IS NULL)
AND bv.project_id = cdl.project_id
AND bv.current_flag = 'Y'
AND bv.budget_status_code = 'B'
AND ei.po_line_id IS NOT NULL
--FP M changes
And adjustment_period_flag = 'N'
AND to_char(pod.po_header_id) = cdl.system_reference2 /* 6989758 */
AND ei.po_line_id = pod.po_line_id /* 6989758 */
AND cdl.project_id = pod.project_id /* 6989758 */
AND cdl.task_id = pod.task_id /* 6989758 */
ORDER BY cdl.expenditure_item_id
,cdl.line_num
;
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':ORACLE error occurred while selecting pa_implementations';
SELECT set_of_books_id
INTO g_sob_id
FROM pa_implementations;
* Select Expenditure_item_ids to process.
*
* We should get rid of this sql - because the columns selected here
* can be received from the pro*C process as arrays.
*/
l_stage := 200;
PA_DEBUG.g_err_stage := TO_CHAR(l_stage) || ': Fresh line Missing. Selecting Fresh line.';
SELECT cdl.expenditure_item_id
,cdl.line_num
,cdl.line_type
,cdl.line_num_reversed
,DECODE(cdl.line_type, 'R', cdl.acct_raw_cost, cdl.acct_burdened_cost)
,DECODE(cdl.line_type, 'R', cdl.denom_raw_cost, cdl.denom_burdened_cost)
,cdl.acct_burdened_cost
,cdl.denom_burdened_cost
,cdl.project_id
,cdl.pa_date
,cdl.gl_date
,cdl.burden_sum_rejection_code
,cdl.burden_sum_source_run_id
,cdl.ind_compiled_set_id
,cdl.dr_code_combination_id
,TO_NUMBER(cdl.system_reference2)
,glp.period_name
,l_expenditure_item_date_tab(l_this_fetch)
,l_expenditure_type_tab(l_this_fetch)
,l_task_id_tab(l_this_fetch)
,l_document_line_id_tab(l_this_fetch)
,l_exp_organization_id_tab(l_this_fetch)
,l_org_id_tab(l_this_fetch)
,NVL(pt.burden_amt_display_method, 'S')
,NVL(pt.burden_cost_flag, 'N')
,bv.budget_version_id
,'EXP'
,cdl.expenditure_item_id
,cdl.line_num
INTO l_expenditure_item_id_tab(l_this_fetch+1)
,l_line_num_tab(l_this_fetch+1)
,l_line_type_tab(l_this_fetch+1)
,l_line_num_reversed_tab(l_this_fetch+1)
,l_acct_raw_cost_tab(l_this_fetch+1)
,l_denom_raw_cost_tab(l_this_fetch+1)
,l_acct_burdened_cost_tab(l_this_fetch+1)
,l_denom_burdened_cost_tab(l_this_fetch+1)
,l_project_id_tab(l_this_fetch+1)
,l_pa_date_tab(l_this_fetch+1)
,l_gl_date_tab(l_this_fetch+1)
,l_burden_sum_rej_code_tab(l_this_fetch+1)
,l_burden_sum_source_run_id_tab(l_this_fetch+1)
,l_ind_compiled_set_id_tab(l_this_fetch+1)
,l_dr_code_combination_id_tab(l_this_fetch+1)
,l_document_header_id_tab(l_this_fetch+1)
,l_gl_period_name_tab(l_this_fetch+1)
,l_expenditure_item_date_tab(l_this_fetch+1)
,l_expenditure_type_tab(l_this_fetch+1)
,l_task_id_tab(l_this_fetch+1)
,l_document_line_id_tab(l_this_fetch+1)
,l_exp_organization_id_tab(l_this_fetch+1)
,l_org_id_tab(l_this_fetch+1)
,l_burden_amt_disp_method_tab(l_this_fetch+1)
,l_burden_cost_flag_tab(l_this_fetch+1)
,l_budget_version_id_tab(l_this_fetch+1)
,l_pkt_reference1_Tab(l_this_fetch+1)
,l_pkt_reference2_Tab(l_this_fetch+1)
,l_pkt_reference3_Tab(l_this_fetch+1)
FROM pa_cost_distribution_lines_all cdl
,pa_project_types_all pt
,pa_projects_all p
,pa_budget_versions bv
,pa_budgetary_control_options pbct
,gl_period_statuses glp
WHERE cdl.line_num_reversed IS NULL
AND cdl.reversed_flag IS NULL
AND cdl.request_id = g_request_id
AND cdl.line_type in ('R', 'I')
AND cdl.expenditure_item_id = l_expenditure_item_id_tab(l_this_fetch)
AND p.project_id = cdl.project_id
AND p.project_type = pt.project_type
-- R12 AND NVL(pt.org_Id, -99) = NVL(p.org_Id, -99)
AND pt.org_Id = p.org_Id
AND glp.application_id = 101
AND glp.set_of_books_id = g_sob_id
AND TRUNC(cdl.gl_date) between TRUNC(glp.START_DATE) and TRUNC(glp.END_DATE)
AND pbct.project_id = bv.project_id
AND pbct.BDGT_CNTRL_FLAG = 'Y'
AND pbct.BUDGET_TYPE_CODE = bv.budget_type_code
AND (pbct.EXTERNAL_BUDGET_CODE = 'GL'
OR
pbct.EXTERNAL_BUDGET_CODE is NULL)
AND bv.project_id = cdl.project_id
AND bv.current_flag = 'Y'
AND bv.budget_status_code = 'B'
--FP M changes
And adjustment_period_flag = 'N' ;
SELECT gl_bc_packets_s.NEXTVAL
INTO g_packet_id
FROM dual;
| Call Autonomous Procedure to insert the pl/sql tables into pa_bc_packets.|
+==========================================================================*/
pa_debug.g_err_stage := TO_CHAR(l_stage) || ': Before Calling populate_pa_bc_packets.';
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After Inserting Records into PA_BC_PACKETS.';
UPDATE pa_expenditure_items ei
SET ei.cost_dist_rejection_code = l_rejn_code_tab(i)
WHERE ei.expenditure_item_id = l_expenditure_item_id_tab(i)
AND l_rejn_code_tab(i) IS NOT NULL;
* This count does not represent the number of EIs updated because,
* this table is for CDLs and can have more than one record for the same EI.
*/
l_records_affected := SQL%ROWCOUNT ;
pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Updated [' ||
TO_CHAR(l_records_affected) ||
'] records - this count is not right.';
l_expenditure_item_id_tab.DELETE;
l_line_num_tab.DELETE;
l_line_type_tab.DELETE;
l_line_num_reversed_tab.DELETE;
l_acct_raw_cost_tab.DELETE;
l_denom_raw_cost_tab.DELETE;
l_acct_burdened_cost_tab.DELETE;
l_denom_burdened_cost_tab.DELETE;
l_project_id_tab.DELETE;
l_pa_date_tab.DELETE;
l_gl_date_tab.DELETE;
l_burden_sum_rej_code_tab.DELETE;
l_burden_sum_source_run_id_tab.DELETE;
l_ind_compiled_set_id_tab.DELETE;
l_dr_code_combination_id_tab.DELETE;
l_gl_period_name_tab.DELETE;
l_expenditure_item_date_tab.DELETE;
l_expenditure_type_tab.DELETE;
l_task_id_tab.DELETE;
l_exp_organization_id_tab.DELETE;
l_org_id_tab.DELETE;
l_burden_amt_disp_method_tab.DELETE;
l_burden_cost_flag_tab.DELETE;
l_budget_version_id_tab.DELETE;
l_pkt_reference1_Tab.DELETE;
l_pkt_reference2_Tab.DELETE;
l_pkt_reference3_Tab.DELETE;
END LOOP; -- End of loop to insert total number records.
| Select summary information from either pa_bc_packets or |
| pa_bc_commitments_all. Populate the summary information into |
| host plsql tables. |
| If the summary information of the current txn already exist in the |
| plsql table, proceed with further processing. Otherwise get the |
| summary record from db and populate the plsql table. |
| o j holds the number of summary records in the plsql table. |
+====================================================================*/
l_found := FALSE;
SELECT pabcc.Comm_Tot_Raw_Amt
,pabcc.Comm_Tot_Bd_Amt
,pabcc.Comm_Raw_Amt_Relieved
,pabcc.Comm_Bd_Amt_Relieved
,pabcc.compiled_multiplier
,pabcc.parent_bc_packet_id
,pabcc.expenditure_type
,'PA_BC_COMMITMENTS'
BULK COLLECT
INTO l_temp_Tot_Raw_Amt_tab
,l_temp_Tot_Bd_Amt_tab
,l_temp_Raw_Amt_Relieved_tab
,l_temp_Bd_Amt_Relieved_tab
,l_temp_compiled_multiplier_tab
,l_temp_parent_bc_packet_id_tab
,l_temp_expenditure_type_tab
,l_temp_comm_source_tab
FROM pa_bc_commitments pabcc
WHERE pabcc.document_header_id = l_document_header_id_tab(i)
AND pabcc.document_line_id = l_document_line_id_tab(i)
AND pabcc.project_id = l_project_id_tab(i)
AND pabcc.task_id = l_task_id_tab(i)
AND ( (pabcc.parent_bc_packet_id IS NOT NULL AND pabcc.Comm_Tot_Bd_Amt <> 0)
OR pabcc.parent_bc_packet_id IS NULL)
AND pabcc.summary_record_flag = 'Y';
SELECT pabc.Comm_Tot_Raw_Amt
,pabc.Comm_Tot_Bd_Amt
,pabc.Comm_Raw_Amt_Relieved
,pabc.Comm_Bd_Amt_Relieved
,pabc.compiled_multiplier
,pabc.parent_bc_packet_id
,pabc.expenditure_type
,'PA_BC_PACKETS'
BULK COLLECT
INTO l_temp_Tot_Raw_Amt_tab
,l_temp_Tot_Bd_Amt_tab
,l_temp_Raw_Amt_Relieved_tab
,l_temp_Bd_Amt_Relieved_tab
,l_temp_compiled_multiplier_tab
,l_temp_parent_bc_packet_id_tab
,l_temp_expenditure_type_tab
,l_temp_comm_source_tab
FROM pa_bc_packets pabc
WHERE pabc.document_header_id = l_document_header_id_tab(i)
AND pabc.document_line_id = l_document_line_id_tab(i)
AND pabc.project_id = l_project_id_tab(i)
AND pabc.task_id = l_task_id_tab(i)
AND ( (pabc.parent_bc_packet_id IS NOT NULL AND pabc.Comm_Tot_Bd_Amt <> 0)
OR pabc.parent_bc_packet_id IS NULL)
AND pabc.funds_process_mode = 'T'
AND pabc.summary_record_flag = 'Y'
AND pabc.status_code IN ('A', 'C');
| Insert the new summary record at the end of the main |
| summary plsql table |
+======================================================*/
j := j + 1;
| inserted into pa_bc_packets. |
+=========================================================*/
pa_debug.g_err_stage := 'Inserting Raw PO relieving record.';
pa_debug.g_err_stage := 'ORACLE error selecting sequence';
SELECT pa_bc_packets_s.NEXTVAL
INTO l_ins_bc_packet_id_tab(ins_rec)
FROM DUAL;
| is not getting inserted for some rejection. |
+===============================================================*/
l_i_raw_po_rec := ins_rec;
| be inserted by the distribution process. |
+===============================================================*/
IF ( l_burden_cost_flag_tab(i) = 'Y' AND
l_burden_amt_disp_method_tab(i) = 'S' AND
l_line_type_tab(i) <> 'I'
)
THEN
ins_rec := ins_rec + 1;
pa_debug.g_err_stage := 'Inserting Burd PO rec. Same line burd. ins_rec is [' || to_char(ins_rec) || ']';
SELECT pa_bc_packets_s.NEXTVAL
INTO l_ins_bc_packet_id_tab(ins_rec)
FROM DUAL;
| to updated on the column Comm_Raw_Amt_Relieved. |
+===========================================================*/
l_cur_new_bd_amt_relieved := l_summ_bd_amt_relieved_tab(i_summary) +
(l_acct_raw_cost_tab(i) * l_summ_compiled_multiplier_tab(i_summary));
| lines getting inserted. This is because, the burden expenditure |
| types and multipliers are needed to insert funds-check lines. |
+=====================================================================*/
IF ( l_burden_cost_flag_tab(i) = 'Y' AND
l_burden_amt_disp_method_tab(i) = 'D' AND
l_line_type_tab(i) <> 'I'
)
THEN
pa_debug.g_err_stage := 'Separate line burdening - hitting burdening datamodel';
SELECT icc.expenditure_type
,cm.compiled_multiplier
BULK COLLECT INTO
l_txn_burden_exp_type_tab
,l_txn_burden_comp_mult_tab
FROM PA_IND_COST_CODES ICC
,PA_COMPILED_MULTIPLIERS CM
,PA_IND_COMPILED_SETS ICS
,PA_COST_BASE_EXP_TYPES CBET
,PA_COST_BASES CB
,PA_IND_RATE_SCH_REVISIONS IRSR
,PA_IND_RATE_SCHEDULES_ALL_BG IRS
WHERE ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
AND irs.ind_rate_sch_id = irsr.ind_rate_sch_id
AND irsr.cost_plus_structure = cbet.cost_plus_structure
AND cbet.cost_base = cm.cost_base
AND cb.cost_base = cbet.cost_base
AND cb.cost_base_type = cbet.cost_base_type
AND cbet.cost_base_type = 'INDIRECT COST'
AND cbet.expenditure_type = l_expenditure_type_tab(i)
AND ics.organization_id = l_exp_organization_id_tab(i)
AND ics.cost_base = cbet.cost_base
AND ics.ind_compiled_set_id = l_ind_compiled_set_id_tab(i)
AND icc.ind_cost_code = cm.ind_cost_code
AND cm.ind_compiled_set_id = l_ind_compiled_set_id_tab(i)
AND l_burden_sum_rej_code_tab(i) IS NULL
AND l_burden_sum_source_run_id_tab(i) = -9999
AND l_burden_amt_disp_method_tab(i) = 'D'
AND l_burden_cost_flag_tab(i) = 'Y'
;
/*stop the raw relieving line getting inserted*/
l_ins_rejn_code_tab(l_i_raw_po_rec) := 'PA_TXN_COMM_BCC_NO_MATCH';
| Inserting Funds PO relieving Burden Record. |
+=============================================*/
FOR summ_line IN l_summ_project_id_tab.FIRST .. l_summ_project_id_tab.LAST
LOOP
IF ( l_summ_project_id_tab(summ_line) = l_project_id_tab(i) AND
l_summ_task_id_tab(summ_line) = l_task_id_tab(i) AND
l_summ_document_header_id_tab(summ_line) = l_document_header_id_tab(i) AND
l_summ_document_line_id_tab(summ_line) = l_document_line_id_tab(i) AND
l_summ_parent_bc_packet_id_tab(summ_line) IS NOT NULL AND
l_summ_tot_bd_amt_tab(summ_line) <> 0
)
THEN
ins_rec := ins_rec + 1;
pa_debug.g_err_stage := 'Inserting sep line PO relieving. ins_rec is [' || to_char(ins_rec) || ']';
SELECT pa_bc_packets_s.NEXTVAL
INTO l_ins_bc_packet_id_tab(ins_rec)
FROM DUAL;
pa_debug.g_err_stage := 'Inserting raw EXP record ins_rec is [' || to_char(ins_rec) || ']';
SELECT pa_bc_packets_s.NEXTVAL
INTO l_ins_bc_packet_id_tab(ins_rec)
FROM DUAL;
pa_debug.g_err_stage := 'inserting burden same line exp record ins_rec is [' || to_char(ins_rec) || ']';
SELECT pa_bc_packets_s.NEXTVAL
INTO l_ins_bc_packet_id_tab(ins_rec)
FROM DUAL;
pa_debug.write_file('inserting burden sep line exp record ins_rec is [' || to_char(ins_rec) || ']');
SELECT pa_bc_packets_s.NEXTVAL
INTO l_ins_bc_packet_id_tab(ins_rec)
FROM DUAL;
| Delete all plsql tables that are used per loop. |
+==================================================*/
l_temp_Tot_Raw_Amt_tab.DELETE;
l_temp_Tot_Bd_Amt_tab.DELETE;
l_temp_Raw_Amt_Relieved_tab.DELETE;
l_temp_Bd_Amt_Relieved_tab.DELETE;
l_temp_compiled_multiplier_tab.DELETE;
l_temp_parent_bc_packet_id_tab.DELETE;
l_temp_expenditure_type_tab.DELETE;
l_temp_comm_source_tab.DELETE;
l_txn_burden_exp_type_tab.DELETE;
l_txn_burden_comp_mult_tab.DELETE;
pa_debug.g_err_stage := 'Before inserting record [' || to_char(ins_rec)
|| '] l_ins_packet_id_tab [' || to_char(l_ins_packet_id_tab(ins_rec))
|| '] l_ins_project_id_tab [' || to_char(l_ins_project_id_tab(ins_rec))
|| '] l_ins_task_id_tab [' || to_char(l_ins_task_id_tab(ins_rec))
|| '] l_ins_budget_version_id_tab [' || to_char(l_ins_budget_version_id_tab(ins_rec))
|| '] l_ins_expenditure_type_tab [' || l_ins_expenditure_type_tab(ins_rec)
|| '] l_ins_ei_date_tab [' || to_char(l_ins_ei_date_tab(ins_rec))
|| '] l_ins_period_name_tab [' || l_ins_period_name_tab(ins_rec)
|| '] l_ins_pa_date_tab [' || to_char(l_ins_pa_date_tab(ins_rec))
|| '] l_ins_gl_date_tab [' || to_char(l_ins_gl_date_tab(ins_rec))
|| '] l_ins_set_of_books_id_tab [' || to_char(l_ins_set_of_books_id_tab(ins_rec))
|| '] l_ins_je_category_name_tab [' || l_ins_je_category_name_tab(ins_rec)
|| '] l_ins_je_source_name_tab [' || l_ins_je_source_name_tab(ins_rec)
|| '] l_ins_status_code_tab [' || l_ins_status_code_tab(ins_rec)
|| '] l_ins_document_type_tab [' || l_ins_document_type_tab(ins_rec)
|| ']';
pa_debug.g_err_stage := 'Before inserting record [' || to_char(ins_rec)
|| '] l_ins_funds_process_mode_tab [' || l_ins_funds_process_mode_tab(ins_rec)
|| '] l_ins_burden_cost_flag_tab [' || l_ins_burden_cost_flag_tab(ins_rec)
|| '] l_ins_expenditure_orgn_id_tab [' || to_char(l_ins_expenditure_orgn_id_tab(ins_rec))
|| '] l_ins_document_header_id_tab [' || to_char(l_ins_document_header_id_tab(ins_rec))
|| '] l_ins_document_line_id_tab [' || to_char(l_ins_document_line_id_tab(ins_rec))
|| '] l_ins_document_dist_id_tab [' || to_char(l_ins_document_dist_id_tab(ins_rec))
|| '] l_ins_txn_ccid_tab [' || to_char(l_ins_txn_ccid_tab(ins_rec))
|| '] l_ins_burden_cost_flag_tab [' || l_ins_burden_cost_flag_tab(ins_rec)
|| '] l_ins_balance_posted_flag_tab [' || l_ins_balance_posted_flag_tab(ins_rec)
|| ']';
| Insert into pa_bc_packets. |
+==================================*/
/* ?????????? This IF is added because the update gives numeric/value error
* when there are no records to insert. Ideally this should not happen. This
* has to be figured out. ??????????????????
*/
IF ( l_ins_packet_id_tab.COUNT > 0)
THEN
pa_debug.g_err_stage := 'Before inserting into pa_bc_pacets';
pa_debug.g_err_stage := 'ORACLE error while Inserting PA_BC_PACKETS.' ;
INSERT
INTO pa_bc_packets( packet_id
,project_id
,task_id
,budget_version_id
,expenditure_type
,expenditure_item_date
,period_name
,pa_date
,gl_date
,set_of_books_id
,je_category_name
,je_source_name
,status_code
,document_type
,funds_process_mode
,burden_cost_flag
,expenditure_organization_id
,document_header_id
,document_line_id
,document_distribution_id
,txn_ccid
,accounted_dr
,entered_dr
,bc_packet_id
,parent_bc_packet_id
,org_id
,balance_posted_flag
,exp_item_id
,program_id
,program_application_id
,program_update_date
,last_update_date
,last_updated_by
,created_by
,creation_date
,last_update_login
,request_id
,reference1
,reference2
,reference3
,actual_flag -- Bug 5494476
)
SELECT l_ins_packet_id_tab(ins_rec)
,l_ins_project_id_tab(ins_rec)
,l_ins_task_id_tab(ins_rec)
,l_ins_budget_version_id_tab(ins_rec)
,l_ins_expenditure_type_tab(ins_rec)
,l_ins_ei_date_tab(ins_rec)
,l_ins_period_name_tab(ins_rec)
,l_ins_pa_date_tab(ins_rec)
,l_ins_gl_date_tab(ins_rec)
,l_ins_set_of_books_id_tab(ins_rec)
,l_ins_je_category_name_tab(ins_rec)
,l_ins_je_source_name_tab(ins_rec)
,l_ins_status_code_tab(ins_rec)
,l_ins_document_type_tab(ins_rec)
,l_ins_funds_process_mode_tab(ins_rec)
,l_ins_burden_cost_flag_tab(ins_rec)
,l_ins_expenditure_orgn_id_tab(ins_rec)
,l_ins_document_header_id_tab(ins_rec)
,l_ins_document_line_id_tab(ins_rec)
,l_ins_document_dist_id_tab(ins_rec)
,l_ins_txn_ccid_tab(ins_rec)
,l_ins_accounted_dr_tab(ins_rec)
,l_ins_entered_dr_tab(ins_rec)
,l_ins_bc_packet_id_tab(ins_rec)
,l_ins_parent_bc_packet_id_tab(ins_rec)
,l_ins_org_id_tab(ins_rec)
,l_ins_balance_posted_flag_tab(ins_rec)
,l_ins_exp_item_id_tab(ins_rec)
,g_program_id -- program_id
,g_program_application_id -- program_application_id
,SYSDATE -- program_update_date
,SYSDATE -- last_update_date
,-99 -- last_updated_by
,-99 -- created_by
,SYSDATE -- creation_date
,g_last_update_login -- last_update_login
,g_request_id
,'EXP'
,l_ins_exp_item_id_tab(ins_rec)
,l_ins_document_dist_id_tab(ins_rec)
-- Bug 5494476 : Actual flag should be 'A' for expenditures and 'E' for PO commitment relieving records.
,decode (l_ins_document_type_tab(ins_rec),'EXP','A','E')
FROM DUAL
WHERE l_ins_rejn_code_tab(ins_rec) IS NULL
;
pa_debug.g_err_stage := 'Inserted [' || TO_CHAR(l_records_affected) ||
'] Records into pa_bc_packets.';
l_ins_packet_id_tab.DELETE;
l_ins_project_id_tab.DELETE;
l_ins_task_id_tab.DELETE;
l_ins_budget_version_id_tab.DELETE;
l_ins_expenditure_type_tab.DELETE;
l_ins_ei_date_tab.DELETE;
l_ins_period_name_tab.DELETE;
l_ins_pa_date_tab.DELETE;
l_ins_gl_date_tab.DELETE;
l_ins_set_of_books_id_tab.DELETE;
l_ins_je_category_name_tab.DELETE;
l_ins_je_source_name_tab.DELETE;
l_ins_status_code_tab.DELETE;
l_ins_funds_process_mode_tab.DELETE;
l_ins_burden_cost_flag_tab.DELETE;
l_ins_expenditure_orgn_id_tab.DELETE;
l_ins_document_dist_id_tab.DELETE;
l_ins_txn_ccid_tab.DELETE;
l_ins_bc_packet_id_tab.DELETE;
l_ins_org_id_tab.DELETE;
l_ins_balance_posted_flag_tab.DELETE;
l_ins_document_type_tab.DELETE;
l_ins_parent_bc_packet_id_tab.DELETE;
l_ins_document_header_id_tab.DELETE;
l_ins_document_line_id_tab.DELETE;
l_ins_entered_dr_tab.DELETE;
l_ins_accounted_dr_tab.DELETE;
l_summ_project_id_tab.DELETE;
l_summ_task_id_tab.DELETE;
l_summ_document_header_id_tab.DELETE;
l_summ_document_line_id_tab.DELETE;
l_summ_tot_raw_amt_tab.DELETE;
l_summ_tot_bd_amt_tab.DELETE;
l_summ_raw_amt_relieved_tab.DELETE;
l_summ_bd_amt_relieved_tab.DELETE;
l_summ_compiled_multiplier_tab.DELETE;
l_summ_parent_bc_packet_id_tab.DELETE;
l_summ_expenditure_type_tab.DELETE;
l_summ_source_tab.DELETE;