[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_ap_bc_packets(p_packet_id IN NUMBER,
p_sys_ref2 IN NUMBER,
p_sys_ref4 IN VARCHAR2,
p_sys_ref5 IN NUMBER,
p_acct_raw_cost IN NUMBER,
p_acct_bur_cost IN NUMBER,
p_fc_document_type IN VARCHAR2,
p_txn_source IN VARCHAR2,
p_adj_exp_item_id IN NUMBER,
p_txn_interface_id IN NUMBER);
PROCEDURE insert_ap_bc_pkt_autonomous
(p_packet_id IN NUMBER,
p_sys_ref2 IN NUMBER,
p_sys_ref4 IN VARCHAR2,
p_sys_ref5 IN NUMBER,
p_acct_raw_cost IN NUMBER,
p_acct_bur_cost IN NUMBER,
p_fc_document_type IN VARCHAR2,
p_txn_source IN VARCHAR2,
p_adj_exp_item_id IN NUMBER,
p_txn_interface_id IN NUMBER,
p_comm_fc_req IN VARCHAR2,
p_act_fc_req IN VARCHAR2,
p_adj_act_fc_req IN VARCHAR2);
PROCEDURE insert_cash_ap_bc_packets(p_packet_id IN NUMBER,
p_sys_ref2 IN NUMBER,
p_sys_ref5 IN NUMBER,
p_acct_raw_cost IN NUMBER,
p_fc_document_type IN VARCHAR2,
p_txn_source IN VARCHAR2,
p_adj_exp_item_id IN NUMBER,
p_txn_interface_id IN NUMBER,
p_cash_pay_to_relieve IN NUMBER DEFAULT 0);
PROCEDURE insert_cash_ap_bc_pkt_auto
(p_packet_id IN NUMBER,
p_sys_ref2 IN NUMBER,
p_sys_ref5 IN NUMBER,
p_acct_raw_cost IN NUMBER,
p_fc_document_type IN VARCHAR2,
p_txn_source IN VARCHAR2,
p_adj_exp_item_id IN NUMBER,
p_txn_interface_id IN NUMBER,
p_cash_pay_to_relieve IN NUMBER DEFAULT 0,
p_comm_fc_req IN VARCHAR2,
p_act_fc_req IN VARCHAR2,
p_adj_act_fc_req IN VARCHAR2);
PROCEDURE insert_po_bc_packets(p_packet_id IN NUMBER,
p_sys_ref2 IN NUMBER,
p_sys_ref4 IN NUMBER,
p_sys_ref3 IN NUMBER,
p_acct_raw_cost IN NUMBER,
p_cmt_raw_cost IN NUMBER,
p_fc_document_type IN VARCHAR2,
p_txn_source IN VARCHAR2,
p_adj_exp_item_id IN NUMBER,
p_txn_interface_id IN NUMBER);
of the PROCEDURE insert_po_bc_packets_auto to be of VARCHAR2 datatype. */
PROCEDURE insert_po_bc_packets_auto
(p_packet_id IN NUMBER,
p_sys_ref2 IN NUMBER,
p_sys_ref4 IN NUMBER,
p_sys_ref3 IN NUMBER,
p_acct_raw_cost IN NUMBER,
p_cmt_raw_cost IN NUMBER,
p_fc_document_type IN VARCHAR2,
p_txn_source IN VARCHAR2,
p_adj_exp_item_id IN NUMBER,
p_txn_interface_id IN NUMBER,
p_comm_fc_req IN VARCHAR2,
p_act_fc_req IN VARCHAR2,
p_adj_act_fc_req IN VARCHAR2);
g_xface_project_id_tbl.delete;
g_xface_task_id_tbl.delete;
g_xface_exp_type_tbl.delete;
g_xface_ei_date_tbl.delete;
g_xface_exp_org_id_tbl.delete;
g_xface_bud_ver_id_tbl.delete;
g_xface_Entered_Cr_tbl.delete;
g_xface_Entered_Dr_tbl.delete;
g_xface_acct_Cr_tbl.delete;
g_xface_acct_Dr_tbl.delete;
g_xface_Txn_Ccid_tbl.delete;
g_xface_org_id_tbl.delete;
g_xface_Txn_interface_tbl.delete;
g_sob_Id_tbl.delete;
g_Period_Year_tbl.delete;
g_project_id_tbl.delete;
g_task_id_tbl.delete;
g_exp_type_tbl.delete;
g_ei_date_tbl.delete;
g_exp_org_id_tbl.delete;
g_bud_ver_id_tbl.delete;
g_budget_line_id_tbl.delete; -- R12 Funds Management uptake
g_Document_Type_tbl.delete;
g_Doc_Header_Id_tbl.delete;
g_Doc_dist_Id_tbl.delete;
g_Entered_Cr_tbl.delete;
g_Entered_Dr_tbl.delete;
g_acct_Cr_tbl.delete;
g_acct_Dr_tbl.delete;
g_Actual_Flag_tbl.delete;
g_Txn_Ccid_tbl.delete;
g_Je_Catg_Name_tbl.delete;
g_Je_sorce_Name_tbl.delete;
g_org_id_tbl.delete;
g_Pa_Date_tbl.delete;
g_bc_packet_id_tbl.delete;
g_packet_id_tbl.delete;
g_bc_parent_pkt_id_tbl.delete;
g_enc_type_id_tbl.delete;
g_doc_hdr_id_2_tbl.delete;
g_doc_dist_type_tbl.delete;
g_bc_comt_id_tbl.delete;
SELECT ts.transaction_source
, ts.system_linkage_function
, DECODE( ts.system_linkage_function,
'ST', 'PT',
'ER', 'PE',
'VI','VI','PU' )/* Added for bug 2041741*/
, ts.costed_flag
, ts.start_date_active
, ts.end_date_active
, predefined_flag
, allow_adjustments_flag
, gl_accounted_flag
, nvl(posted_flag, 'N') -- get posted_flag
, allow_duplicate_reference_flag
, skip_tc_validation_flag
, allow_emp_org_override_flag
, allow_burden_flag
, pre_processing_extension -- SST change
, post_processing_extension -- SST change
, nvl(batch_size,0)
, nvl(process_funds_check,'N')
INTO
G_trx_source
, G_trx_link
, G_eclass
, G_trx_costed
, G_trx_start
, G_trx_end
, G_trx_predef_flag
, G_allow_adj_flag
, G_gl_accted_flag
, G_gl_posted_flag
, G_allow_dup_flag
, G_skip_tc_flag
, G_emp_org_oride
, G_burdened_flag
, G_pre_processing_extn
, G_post_processing_extn
, G_Batch_Size
, G_Process_Funds_Flag
FROM
pa_transaction_sources ts
WHERE
ts.transaction_source = X_trx_src;
pa_debug.G_err_stage := 'Selecting Values';
SELECT vendor_ID INTO G_vendor_id
FROM po_vendors
WHERE segment1 = p_vendor_number;
select vendor_id
into g_vendor_id
from po_vendors
where employee_id = p_person_id
and vendor_type_lookup_code = 'EMPLOYEE' ;
pa_debug.G_err_Stage := 'Selecting Values';
SELECT
/* et.system_linkage_function */ /* Commented for Bug#2726242 */
et.start_date_active
, et.end_date_active
, NVL(ecr.cost_rate, 1)
, et.cost_rate_flag
INTO
/* G_etype_link */ /* Commented for Bug#2726242 */
G_etype_start
, G_etype_end
, G_etype_cr
, G_etype_cost_rate_flag
FROM
pa_expenditure_cost_rates ecr
, pa_expenditure_types et
WHERE
et.expenditure_type = ecr.expenditure_type (+)
AND X_date BETWEEN ecr.start_date_active (+)
AND nvl(ecr.end_date_active (+), X_date)
AND et.expenditure_type = X_etype;
pa_debug.G_err_Stage := 'Selecting Values';
SELECT
ets.system_linkage_function
,st.labor_non_labor_flag
,ets.start_date_active
,ets.end_date_active
INTO
G_etype_link
, G_etype_labor_flag
, G_etec_start
, G_etec_end
FROM pa_system_linkages st,
pa_expend_typ_sys_links ets
WHERE st.function = ets.system_linkage_function
AND ets.system_linkage_function = X_system_linkage
AND ets.expenditure_type = X_etype ;
pa_debug.G_err_Stage := 'select from pa_non_labor_resources';
pa_debug.G_err_Stage := 'Selecting Values';
SELECT
nlr.expenditure_type
, nlr.start_date_active
, nlr.end_date_active
INTO
G_nlr_etype
, G_nlr_start
, G_nlr_end
FROM
pa_non_labor_resources nlr
WHERE
nlr.non_labor_resource = X_nlr;
pa_debug.G_err_stage := 'select from pa_non_labor_resource_orgs';
SELECT
nlro.start_date_active
, nlro.end_date_active
INTO
G_nlro_start
, G_nlro_end
FROM
pa_non_labor_resource_orgs nlro
WHERE
nlro.organization_id = X_nlro_id
AND nlro.non_labor_resource = X_nlr;
SELECT
1
INTO
dummy
FROM
sys.dual
WHERE EXISTS
( SELECT 1
FROM pa_expenditure_items ei
WHERE ei.orig_transaction_reference = X_trx_ref
AND ei.transaction_source = X_trx_source );
pa_debug.G_err_stage := 'Inside GetProjTypeInfo, selecting values';
SELECT burden_cost_flag, project_type_class_code, burden_amt_display_method,
total_burden_flag
INTO X_proj_bcost_flag, X_proj_type_class, X_burden_amt_display_method,
X_Total_Burden_Flag
FROM pa_projects_all proj,
pa_project_types_all ptype
WHERE proj.project_type = ptype.project_type
-- MOAC Changes
AND proj.org_id = ptype.org_id
AND project_id = X_Project_id ;
select code_combination_id
into X_ccid
from gl_code_combinations
where code_combination_id = ccid
and enabled_flag='Y'
and chart_of_accounts_id in (select sob.chart_of_accounts_id
from pa_implementations imp
,gl_sets_of_books sob
where imp.set_of_books_id=sob.set_of_books_id) ; /*10138679*/
select nvl(p.start_date, p_exp_item_date) proj_start_date,
nvl(p.completion_date, p_exp_item_date) proj_end_date,
nvl(t.start_date, p_exp_item_date) task_start_date,
nvl(t.completion_date, p_exp_item_date) task_completion_date,
nvl(et.start_date_active, p_exp_item_date) et_start_date,
nvl(et.end_date_active, p_exp_item_date) et_end_date,
nvl(sl.start_date_active, p_exp_item_date) sl_start_date,
nvl(sl.end_date_active, p_exp_item_date) sl_end_date
from pa_projects_all p,
pa_tasks t,
pa_expenditure_types et,
pa_expend_typ_sys_links sl
where p.project_id = p_project_id
and t.project_id = p_project_id
and t.task_id = p_task_id
and et.expenditure_type = p_exp_type
and sl.system_linkage_function = p_system_linkage
and sl.expenditure_type = p_exp_type ;
Select Allowable_Schedule_Id,
nvl(Preaward_Date,START_DATE_ACTIVE) preaward_date,
End_Date_Active end_date,
Close_Date close_date,
Status
from GMS_AWARDS
where award_id = P_award_id;
SELECT vend.employee_id
FROM po_vendors vend
WHERE vend.vendor_id = p_vendor_id
/* AND p_ei_date BETWEEN nvl(vend.start_date_active,p_ei_date) AND
nvl( vend.end_date_active, trunc(sysdate) ) ; */
l_cwk_amt_updated VARCHAR2(1) := 'N';
Select 'X'
from Dual
Where exists (select 'Y' from pa_bc_packets
Where packet_id = c_packet_id
and substr(nvl(result_code, 'P'),1,1) = 'F');
/***** CWK CHANGES - This Procedure will update PL/SQL Tables to reflect *****/
/***** Processed Amount for the given Po_Line_ID. That will be used to *****/
/***** do the Po Funds check and validate the transaction record. *****/
PROCEDURE po_processed_amt_chk(P_Po_Line_Id IN NUMBER
,P_Task_Id IN NUMBER
,P_Calc_Amt IN NUMBER
,X_Processed_Amt OUT NOCOPY NUMBER
,X_status OUT NOCOPY VARCHAR2
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
pa_debug.G_err_stage := 'PO_PROCESSED_AMT_CHK : Insertion of Records : ' ||SQLERRM;
SELECT NVL(allow_negative_accrual, 'N')
INTO l_all_neg_acc
FROM pa_implementations_all
WHERE org_id = X_org_id;
select paa.job_id
into G_job_id
from PER_ALL_ASSIGNMENTS_F paa,
psp_summary_lines psl
where paa.assignment_id = psl.assignment_id
and psl.effective_date between paa.effective_start_date and paa.effective_end_date
and psl.SUMMARY_LINE_ID = X_trx_ref;
select paa.job_id
into G_job_id
from PER_ALL_ASSIGNMENTS_F paa,
psp_summary_lines psl
where paa.assignment_id = psl.assignment_id
and psl.effective_date between paa.effective_start_date and paa.effective_end_date
and psl.SUMMARY_LINE_ID = X_trx_ref;
update pa_transaction_interface_all
Set project_number = ( select segment1
from pa_projects_all
where project_id = P_project_id ),
task_number = ( select task_number from pa_tasks
where task_id = P_task_id)
where txn_interface_id = X_txn_interface_id ;
SELECT 'Y'
INTO l_dummy1
FROM pa_agreements
WHERE agreement_id = l_agreement_id
AND agreement_num = l_agreement_number;
SELECT agreement_num
INTO p_agreement_number
FROM pa_agreements
WHERE agreement_id = p_agreement_id;
SELECT agreement_id
INTO p_agreement_id
FROM pa_agreements
WHERE agreement_num = p_agreement_number;
SELECT RBS_ELEMENT_ID
INTO G_cbs_element_id
FROM PA_PROJECTS_ALL PAP, PA_RBS_ELEMENTS PRE
WHERE PAP.PROJECT_ID = G_project_id
AND PRE.RBS_VERSION_ID = PAP.CBS_VERSION_ID
AND PRE.COST_CODE = P_cbs_element_name;
select set_of_books_id
into l_SobId
from pa_implementations_all
where nvl(org_id,-99) = nvl(X_org_id,-99);
SELECT
expenditure_item_id
INTO
dummy
FROM
pa_expenditure_items
WHERE
expenditure_item_id = G_adj_item_id
FOR UPDATE NOWAIT;
select set_of_books_id
into G_SobId
from pa_implementations_all
where org_id = X_org_id;
select set_of_books_id
into G_RecvrSobId
from pa_implementations_all
where org_id = nvl(G_RecvrOrgId,-99);
select sum(denom_raw_cost)
into l_costed
from pa_expenditure_items ei, pa_expenditures exp
where ei.cost_distributed_flag = 'Y'
and ei.project_id = G_project_id
and ei.task_id = G_task_id
and ei.po_line_id = P_Po_Line_Id
and ei.system_linkage_function in ('ST','OT')
and ei.expenditure_id = exp.expenditure_id
and exp.person_type = 'CWK';
select sum(quantity)*l_po_rate
into l_uncosted
from pa_expenditure_items ei, pa_expenditures exp
where ei.cost_distributed_flag = 'N'
and ei.project_id = G_project_id
and ei.task_id = G_task_id
and ei.po_line_id = P_Po_Line_Id
and ei.system_linkage_function in ('ST','OT')
and ei.expenditure_id = exp.expenditure_id
and exp.person_type = 'CWK';
Select Sum ( Quantity * PA_TRX_IMPORT.GET_PO_PRICE_TYPE_RATE (Project_id , Task_Id , P_Po_Line_Id , po_price_type ) )
into l_uncosted
From
(
select ei.project_id , ei.task_id , ei.po_price_type , sum(quantity) Quantity
from pa_expenditure_items ei, pa_expenditures exp
where ei.cost_distributed_flag = 'N'
and ei.project_id = G_project_id
and ei.task_id = G_task_id
and ei.po_line_id = P_Po_Line_Id
and ei.system_linkage_function in ('ST','OT')
and ei.expenditure_id = exp.expenditure_id
and exp.person_type = 'CWK'
Group By ei.project_id , ei.task_id , ei.po_price_type
);
Select Sum (
Decode ( cost_distributed_flag,
'N', Quantity * PA_TRX_IMPORT.GET_PO_PRICE_TYPE_RATE (G_project_id , G_task_id , P_Po_Line_Id , po_price_type ), 0
)
) ,
Sum (
Decode ( cost_distributed_flag,
'Y', denom_raw_cost, 0
)
)
Into l_uncosted, l_costed
From
(
select ei.po_price_type , ei.cost_distributed_flag,
Sum(Decode(ei.cost_distributed_flag, 'N', ei.quantity,0)) Quantity,
Sum(Decode(ei.cost_distributed_flag, 'Y', ei.denom_raw_cost,0)) denom_raw_cost
from pa_expenditure_items ei, pa_expenditures exp
where ei.project_id = G_project_id
and ei.task_id = G_task_id
and ei.po_line_id = P_Po_Line_Id
and ei.system_linkage_function in ('ST','OT')
and ei.expenditure_id = exp.expenditure_id
and exp.person_type = 'CWK'
Group By ei.po_price_type , ei.cost_distributed_flag
);
l_cwk_amt_updated := 'Y' ;
IF l_cwk_amt_updated = 'Y' THEN
undo_processed_amt_chk(P_Po_Line_Id
,P_Task_Id
,l_Calc_Amt
);
IF l_cwk_amt_updated = 'Y' THEN
undo_processed_amt_chk(P_Po_Line_Id
,P_Task_Id
,l_Calc_Amt );
pa_debug.G_err_stage := 'Call to internal funds check packet insert success';
pa_debug.G_err_stage := 'After select to check for funds check failed records';
v_src_txnifIDTab.DELETE ;
v_src_eidateTab.DELETE ;
v_dst_ifIDTab.DELETE ;
v_dst_TxnifIDTab.DELETE ;
v_dst_eidateTab.DELETE ;
v_dst_txnstatcdTab.DELETE ;
v_doc_headerIDTab.DELETE ;
v_week_ending_dtTab.DELETE ;
select a.txn_interface_id,
a.expenditure_item_date,
b.interface_id,
b.txn_interface_id,
b.expenditure_item_date,
b.transaction_status_code
BULK Collect into
v_src_txnifIDTab,
v_src_eidateTab,
v_dst_ifIDTab,
v_dst_TxnifIDTab,
v_dst_eidateTab,
v_dst_txnstatcdTab
from pa_transaction_interface_all a,
pa_transaction_interface_all b
where a.interface_id = X_xface_id
and a.transaction_source = X_transaction_source
and a.batch_name = X_batch
and a.transaction_source = b.transaction_source
and b.interface_id <> X_xface_id
and a.cdl_system_reference1 = b.cdl_system_reference1
and a.cdl_system_reference2 = b.cdl_system_reference2
and a.cdl_system_reference3 = b.cdl_system_reference3
and NVL(a.cdl_system_reference4,'0') = NVL(b.cdl_system_reference4, '0')
and NVL(a.cdl_system_reference5,0) = NVL(b.cdl_system_reference5,0)
and b.transaction_status_code <> 'A'
and NVL(a.expenditure_item_id, -1) <> 0
and a.adjusted_expenditure_item_id is NULL ;
Update pa_transaction_interface_all
set expenditure_item_date = v_dst_eidateTab(indx)
where txn_interface_id = v_src_TxnIfIDTab(indx) ;
Update pa_transaction_interface_all
set expenditure_item_date = v_dst_eidateTab(indx)
where adjusted_txn_interface_id = v_src_TxnIfIDTab(indx)
and net_zero_adjustment_flag = 'Y' ;
select pa_utils.getweekending(max(a.expenditure_item_date)),
a.cdl_system_reference2
BULK Collect into
v_week_ending_dtTab,
v_doc_headerIDTab
from pa_transaction_interface_all a,
pa_transaction_interface_all b
where a.interface_id = X_xface_id
and a.transaction_source = X_transaction_source
and a.batch_name = X_batch
and a.transaction_source = b.transaction_source
and b.interface_id <> X_xface_id
and a.cdl_system_reference1 = b.cdl_system_reference1
and a.cdl_system_reference2 = b.cdl_system_reference2
and a.cdl_system_reference3 = b.cdl_system_reference3
and NVL(a.cdl_system_reference4,'0') = NVL(b.cdl_system_reference4, '0')
and nvl(a.cdl_system_reference5,0) = nvl(b.cdl_system_reference5 ,0)
and b.transaction_status_code <> 'A'
group by a.cdl_system_reference2 ;
log_message('log_message: Update the weekending date... '|| v_doc_headerIDTab.count );
UPDATE pa_transaction_interface_all
set expenditure_ending_date = v_week_ending_dtTab(indx)
where interface_id = X_xface_id
and transaction_source = X_transaction_source
and batch_name = X_batch
and cdl_system_reference2 =v_doc_headerIDTab(indx) ;
delete from pa_transaction_interface_all
where txn_interface_id = v_dst_TxnIfIdTab(indx) ;
SELECT payment_status_flag
FROM ap_payment_schedules_all pmt
WHERE pmt.invoice_id = l_invoice_id
AND pmt.payment_status_flag = 'Y' ;
SELECT
xc.transaction_source
, xc.batch_name
, xc.system_linkage_function
, xc.batch_name ||xc.system_linkage_function|| to_char(X_xface_id) exp_group_name
, xc.transaction_count
, xc.processed_count
FROM
pa_transaction_xface_control xc
WHERE
xc.transaction_source = X_transaction_source
AND xc.batch_name = nvl(X_batch, xc.batch_name)
AND xc.status = 'PENDING'
--PA-K Changes: Added intermediate_flag
ORDER BY intermediate_flag desc;
values have been selected.
*/
/** Modified the following cursors to support ID columns for Non-Oracle Txns
*** The current code is based on four cursors - two cursors for pre-defined sources
*** (one for ST, OT and the other for other than ST and OT) - two other cursors
*** for non-oracle sources of which one for ST, OT and the other for rest Sys Links
*** Now these four are collapsed into two cursors - irrespective of a transaction
*** source whether pre-defined on user-defined - once cursor for ST and OT and the
*** other for system linkages other than ST and OT
*** Cursors are removed from the code and not commented for the sake of clarity.
*** Hence there will be only two cursors, defined, opened, fetched from and closed
*** with these changes
**/
/* TrxRecs1 is for ST, OT */
CURSOR TrxRecs1 ( X_transaction_source VARCHAR2
, current_batch VARCHAR2
, curr_etype_class_code VARCHAR2 )
IS
SELECT
system_linkage
, trunc(expenditure_ending_date) expenditure_ending_date
, employee_number
/* Moving the logic for getting organization name based on the G_emp_oride_flag to TrxRec cursor
for fix 2686544 */
, organization_name
/* Bug fix 2655157 starts */
/* , decode (G_emp_org_oride,
'Y',
decode (organization_name,
null,
GetOrgName(employee_number, expenditure_item_date, person_business_group_name),
organization_name),
decode (employee_number,
null,
organization_name,
GetOrgName(employee_number, expenditure_item_date, person_business_group_name))
) organization_name */
/* decode( employee_number, NULL, organization_name,
decode(G_emp_org_oride,'Y',organization_name,NULL))
organization_name */
/* Bug fix 2655157 ends */
, trunc(expenditure_item_date) expenditure_item_date
, project_number
, task_number
, expenditure_type
, non_labor_resource
, non_labor_resource_org_name
, quantity
, raw_cost
, raw_cost_rate
, orig_transaction_reference
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, expenditure_comment
, interface_id
, expenditure_id
, nvl(unmatched_negative_txn_flag, 'N') unmatched_negative_txn_flag
, to_number( NULL ) expenditure_item_id
, org_id org_id
, dr_code_combination_id
, cr_code_combination_id
, cdl_system_reference1
, cdl_system_reference2
, cdl_system_reference3
, cdl_system_reference4
, cdl_system_reference5
, trunc(gl_date) gl_date --7535550
, burdened_cost
, burdened_cost_rate
, receipt_currency_amount
, receipt_currency_code
, receipt_exchange_rate
, denom_currency_code
, denom_raw_cost
, denom_burdened_cost
, trunc(acct_rate_date) acct_rate_date --7535550
, acct_rate_type
, acct_exchange_rate
-- , pa_currency.round_currency_amt1(acct_raw_cost) acct_raw_cost -- Bug 7522080
, acct_raw_cost
, acct_burdened_cost
, acct_exchange_rounding_limit
, project_currency_code
, trunc(project_rate_date) project_rate_date --7535550
, project_rate_type
, project_exchange_rate
, orig_exp_txn_reference1
, orig_user_exp_txn_reference
, vendor_number
, orig_exp_txn_reference2
, orig_exp_txn_reference3
, override_to_organization_name
, reversed_orig_txn_reference
, billable_flag
, txn_interface_id
, person_business_group_name
, projfunc_currency_code
, trunc(projfunc_cost_rate_date) projfunc_cost_rate_date --7535550
, projfunc_cost_rate_type
, projfunc_cost_exchange_rate
, project_raw_cost
, project_burdened_cost
, assignment_name
, work_type_name
, nvl(accrual_flag,'N') accrual_flag
, project_id
, task_id
, person_id
, organization_id
, non_labor_resource_org_id
, vendor_id
, override_to_organization_id
, assignment_id
, work_type_id
, person_business_group_id
, po_number /* cwk */
, po_header_id
, po_line_num
, po_line_id
, person_type
, po_price_type
, wip_resource_id
, inventory_item_id
, unit_of_measure
, adjusted_expenditure_item_id
, NVL(fc_document_type, 'NOT')
, NULL document_type
, document_distribution_type -- R12 AP lines uptake : Prepayment changes
, si_assets_addition_flag
, NULL -- adjusted_txn_interface_id
, NULL -- net_zero_adjustment_flag
, NULL -- sc_xfer_code
, 0 -- final_payment_id
-- , agreement_id --FSIO Changes
-- , agreement_number
/* 10253400: Payroll integration enhancement for 12.2..start */
, job_id
, job_name
, location_id
, location_code
, pay_element_type_id
/* 10253400: Payroll integration enhancement for 12.2..end */
/* Start: Added for bug 16220146 */
, cbs_element_name
, cbs_element_id
/* End: Added for bug 16220146 */
FROM pa_transaction_interface
WHERE transaction_source = X_transaction_source
AND batch_name = current_batch
AND transaction_status_code = 'P'
AND system_linkage in ('ST', 'OT')
ORDER BY
expenditure_ending_date DESC
, decode(nvl(person_id,0), 0, employee_number, person_id)
, decode(nvl(organization_id,0), 0, organization_name, organization_id)
, orig_exp_txn_reference1
, orig_user_exp_txn_reference
, decode(nvl(vendor_id,0), 0, vendor_number, vendor_id)
, orig_exp_txn_reference2
, orig_exp_txn_reference3
, denom_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, expenditure_item_date
, decode(nvl(project_id,0), 0, project_number, project_id)
, decode(nvl(task_id,0), 0, task_number, task_id)
, accrual_flag ;
SELECT
system_linkage
, trunc(expenditure_ending_date) expenditure_ending_date
, employee_number
/* Moving the logic for getting organization name based on the G_emp_oride_flag to TrxRec cursor
for fix 2686544 */
, organization_name
/* Bug fix 2655157 starts */
/* , decode (G_emp_org_oride,
'Y',
decode (organization_name,
null,
GetOrgName(employee_number, expenditure_item_date, person_business_group_name),
organization_name),
decode (employee_number,
null,
organization_name,
GetOrgName(employee_number, expenditure_item_date, person_business_group_name))
) organization_name */
/* decode( employee_number, NULL, organization_name,
decode(G_emp_org_oride,'Y',organization_name,NULL))
organization_name */
/* Bug fix 2655157 ends */
, trunc(expenditure_item_date) expenditure_item_date
, project_number
, task_number
, expenditure_type
, non_labor_resource
, non_labor_resource_org_name
, quantity
, raw_cost
, raw_cost_rate
, orig_transaction_reference
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, expenditure_comment
, interface_id
, expenditure_id
, nvl(unmatched_negative_txn_flag, 'N') unmatched_negative_txn_flag
-- REL12 AP Lines uptake.
-- to_number(NULL) was removed.
, expenditure_item_id
, org_id org_id
, dr_code_combination_id
, cr_code_combination_id
, cdl_system_reference1
, cdl_system_reference2
, cdl_system_reference3
, cdl_system_reference4
, cdl_system_reference5
, trunc(gl_date) gl_date --7535550
, burdened_cost
, burdened_cost_rate
, receipt_currency_amount
, receipt_currency_code
, receipt_exchange_rate
, denom_currency_code
, denom_raw_cost
, denom_burdened_cost
, trunc(acct_rate_date) acct_rate_date --7535550
, acct_rate_type
, acct_exchange_rate
-- , pa_currency.round_currency_amt1(acct_raw_cost) acct_raw_cost -- Bug 7522080
, acct_raw_cost
, acct_burdened_cost
, acct_exchange_rounding_limit
, project_currency_code
, trunc(project_rate_date) project_rate_date --7535550
, project_rate_type
, project_exchange_rate
, orig_exp_txn_reference1
, orig_user_exp_txn_reference
, vendor_number
, orig_exp_txn_reference2
, orig_exp_txn_reference3
, override_to_organization_name
, reversed_orig_txn_reference
, billable_flag
, txn_interface_id
, person_business_group_name
, projfunc_currency_code
, trunc(projfunc_cost_rate_date) projfunc_cost_rate_date --7535550
, projfunc_cost_rate_type
, projfunc_cost_exchange_rate
, project_raw_cost
, project_burdened_cost
, assignment_name
, work_type_name
, nvl(accrual_flag,'N') accrual_flag
, project_id
, task_id
, person_id
, organization_id
, non_labor_resource_org_id
, vendor_id
, override_to_organization_id
, assignment_id
, work_type_id
, person_business_group_id
, po_number /* cwk */
, po_header_id
, po_line_num
, po_line_id
, person_type
, po_price_type
, wip_resource_id
, inventory_item_id
, unit_of_measure
, adjusted_expenditure_item_id
, NVL(fc_document_type, 'NOT' )
, document_type
, document_distribution_type
, si_assets_addition_flag
, adjusted_txn_interface_id
, net_zero_adjustment_flag
, sc_xfer_code -- supplier cost transfer status code.
, 0 -- final_payment_id
-- , agreement_id --FSIO Changes
-- , agreement_number
/* 10253400: Payroll integration enhancement for 12.2..start */
, null job_id
, null job_name
, null location_id
, null location_code
, null pay_element_type_id
/* 10253400: Payroll integration enhancement for 12.2..end */
/* Start: Added for bug 16220146 */
, cbs_element_name
, cbs_element_id
/* End: Added for bug 16220146 */
FROM pa_transaction_interface
WHERE transaction_source = X_transaction_source
AND batch_name = current_batch
AND transaction_status_code = 'P'
AND system_linkage = curr_etype_class_code
ORDER BY
expenditure_ending_date DESC
, decode(nvl(person_id,0), 0, employee_number, person_id)
-- 5389130 added order by criteria.
, decode(nvl(override_to_organization_id ,organization_id), 0, organization_name,
organization_id, organization_id, override_to_organization_id)
, orig_exp_txn_reference1
, orig_user_exp_txn_reference
, decode(nvl(vendor_id,0), 0, vendor_number, vendor_id)
, orig_exp_txn_reference2
, orig_exp_txn_reference3
, denom_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, expenditure_item_date
-- 5389130 added order by criteria.
, decode(adjusted_expenditure_item_id, null, 1, 0, 1, 0)
-- , decode(curr_etype_class_code, 'VI', cdl_system_reference2, '0' ) Commented as part of the Bug 12776739
-- , decode(curr_etype_class_code, 'VI', cdl_system_reference3, '0' ) Commented as part of the Bug 12776739
, decode(curr_etype_class_code, 'VI', to_number(trim(cdl_system_reference2)), '0' ) -- Added as part of the Bug 12776739
, decode(curr_etype_class_code, 'VI', to_number(trim(cdl_system_reference3)), '0' ) -- Added as part of the Bug 12776739
, decode(curr_etype_class_code, 'VI',to_number(trim(cdl_system_reference5)),'0') -- Added for bug#13558683
, TXN_INTERFACE_ID -- Added for bug 13977560
, decode(curr_etype_class_code, 'VI',trim(cdl_system_reference4), '0') -- Added for bug#13558683 removed to_number from
--, decode(curr_etype_class_code, 'VI', cdl_system_reference5, '0' )
--commented for bug#13558683
-- , decode(curr_etype_class_code, 'VI', cdl_system_reference4, '0' )
--Commented for bug#13558683
, decode(fc_document_type ,'ALL', 1, 'CMT', 2, 'ACT', 3 , 4 )
, decode(nvl(project_id,0), 0, project_number, project_id)
, decode(nvl(task_id,0), 0, task_number, task_id)
, accrual_flag
, NVL(adjusted_txn_interface_id,0) ;
SELECT business_group_id
INTO l_business_group_id
FROM pa_implementations;
SELECT decode(a.assignment_type,'E','EMP','C','CWK')
INTO p_person_type
FROM per_assignment_status_types s
,per_all_assignments_f a
WHERE a.job_id IS NOT NULL
AND a.primary_flag = 'Y'
AND p_date BETWEEN trunc(a.effective_start_date) AND trunc(a.effective_end_date)
AND a.person_id = p_person_id
AND s.per_system_status in ('ACTIVE_ASSIGN','ACTIVE_CWK','TERM_ASSIGN')
AND s.assignment_status_type_id = a.assignment_status_type_id
AND ((l_Cross_BG_Profile = 'N' AND l_business_group_id = a.business_group_id+0)
OR l_Cross_BG_Profile = 'Y');
SELECT
1
INTO
dummy
FROM
pa_transaction_xface_control
WHERE
transaction_source = trx_source
AND batch_name = batch
AND system_linkage_function = etypeclasscode
AND status = 'PENDING'
FOR UPDATE OF status NOWAIT;
UPDATE pa_transaction_xface_control
SET
interface_id = X_xface_id
, status = 'IN_PROGRESS'
WHERE
transaction_source = trx_source
AND batch_name = batch
AND system_linkage_function = etypeclasscode
AND status = 'PENDING';
log_message('log_message: ' || 'Updated interface id/status on pa_transaction_xface_control');
/*PA-K Changes: Replaced the existing update stmt */
/* Bug#3451108. Added an 'AND' condition in the if clause to check if the
P_BatchProcCount is not equal to Zero */
If (((P_BatchProcCount + P_ProcCount) < P_TxnCount) And (P_BatchProcCount <> 0)) Then
IF PG_DEBUG = 'Y' THEN
log_message('log_message: ' || 'Set ctrl record status to PENDING');
UPDATE pa_transaction_xface_control
SET status = 'PENDING',
intermediate_flag = 'Y',
processed_count = processed_count + P_BatchProcCount
WHERE transaction_source = P_TrxSource
AND batch_name = P_BatchName
AND interface_id = P_XfaceId
AND system_linkage_function = p_system_linkage_function;
log_message('log_message: ' || 'Updated ' || SQL%ROWCOUNT || ' records');
UPDATE pa_transaction_xface_control
SET status = 'PROCESSED',
processed_count = processed_count + P_BatchProcCount
WHERE transaction_source = P_TrxSource
AND batch_name = P_BatchName
AND interface_id = P_XfaceId
AND system_linkage_function = p_system_linkage_function;
log_message('log_message: ' || 'Updated ' || SQL%ROWCOUNT || ' records');
SELECT pa_expenditures_s.nextval
INTO G_expenditure_id
FROM sys.dual;
pa_debug.G_err_Stage := 'Calling pa_transactions.InsertExpGroupNew';
pa_transactions.InsertExpGroupNew(
group_name
, 'RELEASED'
, ending_date
, current_system_linkage
, X_userid
, X_transaction_source
, l_Accrual_Flag
-- MOAC Changes
, g_moac_org_id );
UPDATE pa_expenditures
SET expenditure_group = group_name
WHERE expenditure_id = G_expenditure_id;
pa_debug.G_err_Stage := 'Calling pa_transactions.InsertExp';
pa_transactions.InsertExp(
X_expenditure_id => G_expenditure_id,
X_expend_status => 'APPROVED',
X_expend_ending => ending_date,
X_expend_class => G_eclass,
X_inc_by_person => G_person_id,
X_inc_by_org => G_org_id,
X_expend_group => group_name,
-- X_entered_by_id => X_userid,
X_entered_by_id => FND_GLOBAL.employee_id, -- Bug 2396121
X_created_by_id => X_userid,
X_denom_currency_code => p_denom_currency_code,
X_acct_currency_code => p_acct_currency_code,
X_acct_rate_type => l_acct_rate_type,
X_acct_rate_date => l_acct_rate_date,
X_acct_exchange_rate => p_acct_exchange_rate,
X_orig_exp_txn_reference1 => G_orig_exp_txn_reference1,
X_orig_user_exp_txn_reference => G_orig_user_exp_txn_reference,
X_vendor_id => G_vendor_id,
X_orig_exp_txn_reference2 => G_orig_exp_txn_reference2,
X_orig_exp_txn_reference3 => G_orig_exp_txn_reference3,
X_person_type => l_person_type, /* Added for bug 13554435 */
-- MOAC Changes
p_org_id => g_moac_org_id); /*cwk */
pa_transactions.InsertExp(
X_expenditure_id => G_expenditure_id,
X_expend_status => 'APPROVED',
X_expend_ending => ending_date,
X_expend_class => G_eclass,
X_inc_by_person => G_person_id,
X_inc_by_org => G_org_id,
X_expend_group => group_name,
-- X_entered_by_id => X_userid,
X_entered_by_id => FND_GLOBAL.employee_id, -- Bug 2396121
X_created_by_id => X_userid,
X_orig_exp_txn_reference1 => G_orig_exp_txn_reference1,
X_orig_user_exp_txn_reference => G_orig_user_exp_txn_reference,
X_vendor_id => G_vendor_id,
X_acct_currency_code => p_acct_currency_code, -- Bug 6412762: Base Bug 6354066
X_orig_exp_txn_reference2 => G_orig_exp_txn_reference2,
X_orig_exp_txn_reference3 => G_orig_exp_txn_reference3,
X_person_type => l_person_type, /* Added for bug 13554435 */
-- MOAC Changes
p_org_id => g_moac_org_id); /* cwk */
select decode(G_gl_accted_flag,'N','N',decode(G_gl_posted_flag,'Y','Y','P')) into l_gl_accted_flag from dual;
UPDATE pa_transaction_interface
-- SST Change: If there's post import extension, then
-- set status code to 'I' so the post import extension
-- will pick it up, otherwise set to 'A' meaning it has
-- completed the transfer process. Post-extension will
-- eventually set the status code to 'A' as well if the
-- the transaction went through post extension
-- successfully.
SET
transaction_status_code = decode(G_post_processing_extn,NULL,'A',
'I')
WHERE
expenditure_id = G_expenditure_id
-- Bug 3613784 : Performance fix
AND batch_name = P_batch_name
AND transaction_rejection_code is NULL; -- Bug 3613784 : Performance fix
pa_debug.G_err_stage := 'Updated ' || SQL%ROWCOUNT || ' txn interface for accepted expenditures';
UPDATE pa_transaction_interface
SET
transaction_status_code = decode(expenditure_item_id, 0, 'P', 'R') , /* Bug 8709614 */
expenditure_item_id = decode(expenditure_item_id, 0, 0, NULL) /* Bug 8709614 */
-- SST Change: For rejected expenditure, we need
-- to reset interface table's reversed_orig_txn_reference
-- field. This field is updated after calling validateitem
-- for each transaction. While processing an entire
-- expenditure, some transaction may pass validateitem API
-- and get their reversed_orig_txn_reference field modified;
pa_debug.G_err_stage := 'Updated ' || SQL%ROWCOUNT || ' txn interface for rejected expenditures';
l_SysLinkTab.Delete;
l_EmpNumTab.Delete;
l_EiEndDateTab.Delete;
l_OrganizationNameTab.Delete;
l_EiDateTab.Delete;
l_PrjNumTab.Delete;
l_TaskNumTab.Delete;
l_ETypeTab.Delete;
l_NlrTab.Delete;
l_NlrOrgNameTab.Delete;
l_QtyTab.Delete;
l_rawCostTab.Delete;
l_rawCostRateTab.Delete;
l_OrigTxnRefTab.Delete;
l_AttCatTab.Delete;
l_Att1Tab.Delete;
l_Att2Tab.Delete;
l_Att3Tab.Delete;
l_Att4Tab.Delete;
l_Att5Tab.Delete;
l_Att6Tab.Delete;
l_Att7Tab.Delete;
l_Att8Tab.Delete;
l_Att9Tab.Delete;
l_Att10Tab.Delete;
l_ExpCommentTab.Delete;
l_InterfaceIdTab.Delete;
l_ExpIdTab.Delete;
l_UnMatchNegFlagTab.Delete;
l_ExpItemIdTab.Delete;
l_OrgIdTab.Delete;
l_DRCCIDTab.Delete;
l_CRCCIDTab.Delete;
l_SysRef1Tab.Delete;
l_SysRef2Tab.Delete;
l_SysRef3Tab.Delete;
l_Sysref4Tab.Delete;
l_GlDateTab.Delete;
l_burdenedCostTab.Delete;
l_BdCostRateTab.Delete;
l_RcptCurrAmtTab.Delete;
l_RcptCurrCodeTab.Delete;
l_RcptExchRateTab.Delete;
l_DenomCurrCodeTab.Delete;
l_DenomRawCostTab.Delete;
l_DenomBdCostTab.Delete;
l_AcctRateDateTab.Delete;
l_AcctRateTypeTab.Delete;
l_AcctExchRateTab.Delete;
l_AcctRawCostTab.Delete;
l_AcctBdCostTab.Delete;
l_AcctExchRndLimitTab.Delete;
l_ProjCurrCodeTab.Delete;
l_prjRateDateTab.Delete;
l_PrjRateTypeTab.Delete;
l_PrjExchRateTab.Delete;
l_OrigExpTxnRef1Tab.Delete;
l_OrigUsrExpTxnRefTab.Delete;
l_VendorNumTab.Delete;
l_OrigExpTxnRef2Tab.Delete;
l_OrigExpTxnRef3Tab.Delete;
l_OverrideOrgNameTab.Delete;
l_RevOrigTxnRefTab.Delete;
l_billableFlagTab.Delete;
l_TxnIfIdTab.Delete;
l_PerBusGrpNameTab.Delete;
l_ProjFuncCurrCodeTab.Delete;
l_PrjFuncRateDateTab.Delete;
l_PrjFuncRateTypeTab.Delete;
l_PrjFuncExchRateTab.Delete;
l_ProjRawCostTab.Delete;
l_ProjBdCostTab.Delete;
l_AsgnNameTab.Delete;
l_WorkTypeNameTab.Delete;
l_AccrualFlagTab.Delete;
l_PrjIdTab.Delete;
l_TaskIdTab.Delete;
l_PersonIdTab.Delete;
l_OrganizationIdTab.Delete;
l_NLROrgIDTab.Delete;
l_VendorIdTab.Delete;
l_OverrideOrgIdTab.Delete;
l_AsgnIdTab.Delete;
l_WorkTypeIdTab.Delete;
l_PersonBusGrpIdTab.Delete;
l_EmpOrgIdTab.Delete;
l_EmpJobIdTab.Delete;
l_po_numberTab.Delete; /* cwk */
l_po_header_idTab.Delete;
l_po_line_numTab.Delete;
l_po_line_idTab.Delete;
l_person_typeTab.Delete;
l_po_price_typeTab.Delete;
l_wip_resource_idTab.Delete;
l_inventory_item_idTab.Delete;
l_unit_of_measureTab.Delete;
l_SysRef5Tab.Delete ;
l_AdjExpItemIdTab.Delete ;
l_FcDocTypeTab.Delete ;
l_DocumentTypeTab.DELETE ;
l_DocumentDistTypeTab.DELETE ;
l_siaAddFlagTab.DELETE ;
l_adjTxnInterfaceIDTab.delete ;
l_NZAdjFlagTab.delete ;
l_AdjTxnEiIDTab.delete ;
l_scXferCdTab.delete ;
l_finalPaymentIdTab.delete ;
l_txn_rejected_sr5tab.DELETE ;
l_txn_rejected_sr4tab.DELETE ;
l_job_idtab.delete;
l_job_nametab.delete;
l_location_idtab.delete;
l_location_codetab.delete;
l_pay_element_type_idtab.delete;
l_cbs_element_idtab.delete;
l_cbs_element_nametab.delete;
select reversed_prepay_app_dist_id
from ap_prepay_app_dists
where prepay_app_dist_id = NVL(l_SysRef4Tab(p_indx),0) ;
select reversal_inv_pmt_id
from AP_Invoice_Payments
where Invoice_Payment_Id = NVL(l_SysRef4Tab(p_indx),0)
and nvl(reversal_flag,'N') = 'Y' ;
select parent_reversal_id
into l_parent_dist_id
from ap_invoice_distributions_all apd
where invoice_distribution_id = NVL(l_SysRef5Tab(p_indx),0)
and nvl(reversal_flag,'N') = 'Y';
select parent_reversal_id
into l_parent_dist_id
from AP_SELF_ASSESSED_TAX_DIST_ALL apd
where invoice_distribution_id = NVL(l_SysRef5Tab(p_indx),0)
and nvl(reversal_flag,'N') = 'Y';
select parent_transaction_id
from rcv_transactions
where transaction_id = NVL(l_SysRef4Tab(p_indx),0) ;
select transaction_rejection_code
from pa_transaction_interface_all
where txn_interface_id = l_rejected_txn_id
and transaction_status_code = 'R' ;
SELECT sum(xc.transaction_count)
INTO l_Batch_Size
FROM pa_transaction_xface_control xc
WHERE xc.transaction_source = TrxBatch.transaction_source
AND xc.status = 'PENDING';
SELECT NVL(glb.sla_ledger_cash_basis_flag,'N')
INTO G_cash_based_accounting
FROM gl_sets_of_books glb, pa_implementations_all pai
WHERE glb.set_of_books_id = pai.set_of_books_id
AND pai.org_id = g_moac_org_id ;
SELECT max(Invoice_Payment_Id)
into l_finalPaymentIdTab(j)
from ap_payment_hist_dists Paydist
where NVL(paydist.pay_dist_lookup_code, 'CASH') = 'CASH'
and Paydist.invoice_distribution_id = NVL(l_SysRef5Tab(j) ,0) ;
log_message('log_message: ' || 'Before selecting expend grouping attributes for transaction sources');
select PPL.person_id
into L_PERSONIDTAB(J)
from PER_PEOPLE_F PPL,
PER_ASSIGNMENTS_f ASGN ,
PER_PERSON_TYPES PTYPE
where
ASGN.VENDOR_ID = l_VendorIdTab(j)
and PPL.PERSON_ID = ASGN.PERSON_ID
and PTYPE.PERSON_TYPE_ID = PPL.PERSON_TYPE_ID
and PTYPE.SYSTEM_PERSON_TYPE in ('OTHER','CWK');
SELECT
to_char(trunc(l_EiEndDateTab(j)), 'J')||':'||
nvl(decode(nvl(l_PersonIdTab(j),0), 0, l_EmpNumTab(j), l_PersonIdTab(j)),
'-DUMMY EMP-')||':'||
--removing the logic for getting organization name for getting l_expend1
--as it has already been done above for 2686544. So we can just use l_OrganizationNameTab(j)
--nvl(decode(G_emp_org_oride,'Y',
-- decode(nvl(l_OrganizationIdTab(j),0), 0, l_OrganizationNameTab(j),l_OrganizationIdTab(j)),
-- decode(nvl(l_PersonIdTab(j),0),0,
-- decode(l_EmpNumTab(j), null,l_OrganizationNameTab(j),
-- GetOrgName(l_EmpNumTab(j),l_EiDateTab(j), l_PerBusGrpNameTab(j)))
-- ,l_EmpOrgIdTab(j))),
nvl(decode(nvl(l_OrganizationIdTab(j),0), 0, l_OrganizationNameTab(j), nvl(l_EmpOrgIdTab(j), l_OrganizationIdTab(j)))
,'-DUMMY ORG-')||':'||
nvl(l_OrigExpTxnRef1Tab(j), '-DUMMY EXP_TXN_REF1-') || ':' ||
nvl(l_OrigUsrExpTxnRefTab(j), '-DUMMY USER_EXP_TXN_REF-') || ':' ||
nvl(decode(nvl(l_VendorIdTab(j),0),0,l_VendorNumTab(j),l_VendorIdTab(j)),
'-DUMMY VENDOR_NUMBER-') || ':' ||
nvl(l_OrigExpTxnRef2Tab(j), '-DUMMY EXP_TXN_REF2-') || ':' ||
nvl(l_OrigExpTxnRef3Tab(j), '-DUMMY EXP_TXN_REF3-') || ':' ||
nvl(l_AccrualFlagTab(j),'-DUMMY ACC_FLAG-')|| ':' ||
nvl(l_person_typeTab(j),'EMP') /* cwk */
, decode(l_SysLinkTab(j),'OT','ST',l_SysLinkTab(j)) || ':' ||
decode(l_SysLinkTab(j),'ER', nvl(l_DenomCurrCodeTab(j),'-DUMMY CODE-'),
'VI', nvl(l_DenomCurrCodeTab(j),'-DUMMY CODE-'),
'-DUMMY CODE-')||':'||
decode(l_SysLinkTab(j),'ER', nvl(to_char(l_AcctRateDateTab(j),'MMDDYYYY'),'-DUMMY DATE-'),
'VI', nvl(to_char(l_AcctRateDateTab(j),'MMDDYYYY'),'-DUMMY DATE-'),
'-DUMMY DATE-')||':'||
decode(l_SysLinkTab(j),'ER', nvl(l_AcctRateTypeTab(j),'-DUMMY TYPE-'),
'VI', nvl(l_AcctRateTypeTab(j),'-DUMMY TYPE-'),
'-DUMMY TYPE-')||':'||
decode(l_SysLinkTab(j),'ER', nvl(to_char(l_AcctExchRateTab(j)),'-DUMMY RATE-'),
'VI', nvl(to_char(l_AcctExchRateTab(j)),'-DUMMY RATE-'),
'-DUMMY RATE-')
Into l_Expend1, l_Expend2
From Dual;
-- Org id is null. Update status.
X_status := X_org_status;
--select
---- Added for payroll billable_flag changes for 12.2 start bug#12344689
g_costing_method := NULL;
Performance Team Suggestion: To combine the below update (by txn_interface_id) with the
update in newexpend (by expenditure_id).
Results: For 20,000 records - gain of 30 sec.
Implementing this combined update has been phased out until there is a dire need to do it.
Placing this comment here for reference.
*/
UPDATE pa_transaction_interface
SET
transaction_rejection_code = X_status
, interface_id = X_xface_id
, expenditure_id = G_expenditure_id
, transaction_status_code = 'R'
WHERE txn_interface_id = l_TxnIfIDTab(j);
log_message('log_message: ' || 'Updated to reject count ' || SQL%ROWCOUNT);
UPDATE pa_transaction_interface
SET
transaction_rejection_code = NULL
, interface_id = X_xface_id
, expenditure_id = G_expenditure_id
, expenditure_item_id = nvl(X_ei_id, 0) /* Bug 8709614 */
-- SST Changes: If TrxRec.reversed_orig_txn_reference
-- is NULL, this means we've called verifyorigitem API
-- to get the reversed item's orig_txn_reference and
-- store it in G_reversed_orig_txn_reference
, reversed_orig_txn_reference =
nvl(l_RevOrigTxnRefTab(j),G_reversed_orig_txn_reference)
WHERE txn_interface_id = l_TxnIfIDTab(j);
log_message('log_message: ' || 'Updated to accepted count ' || SQL%ROWCOUNT);
select decode(reversal_flag, 'Y', reversed_pay_hist_dist_id,l_SysRef1Tab(j))
into l_SysRef5Tab(j)
from ap_payment_hist_dists
where payment_hist_dist_id = l_SysRef1Tab(j);
Select WIP_RESOURCE_ID
INTO G_WIP_RESOURCE_ID
FROM PA_TRANSACTION_INTERFACE_ALL
WHERE ORIG_TRANSACTION_REFERENCE = l_OrigTxnRefTab(j)
and SYSTEM_LINKAGE <> 'BTC'
and rownum = 1;
Select resource_id
into G_WIP_RESOURCE_ID
from wip_transactions
where transaction_id = l_OrigTxnRefTab(j);
raise_application_error(-20001,'Error during Balance Update');
/* PA.M PJM Additional Attribute changes: Calling unit of measure insertion api for all INV/WIP/ST sources from manufacturing */
IF X_transaction_source in ('Inventory', 'Inventory Misc', 'PJM_CSTBP_INV_NO_ACCOUNTS',
'PJM_CSTBP_INV_ACCOUNTS', 'Work In Process', 'PJM_CSTBP_ST_NO_ACCOUNTS',
'PJM_CSTBP_WIP_NO_ACCOUNTS', 'PJM_CSTBP_ST_ACCOUNTS',
'PJM_CSTBP_WIP_ACCOUNTS', 'PJM_NON_CSTBP_ST_ACCOUNTS') THEN
IF PG_DEBUG = 'Y' THEN
log_message('log_message: ' || 'Calling pa_uom.get_uom for transaction source '|| X_transaction_source);
Report will directly select the counts from the interface table */
EXCEPTION WHEN others THEN
IF PG_DEBUG = 'Y' THEN
log_message('log_message: ' || 'Error in pre_import procedure',1);
Report will directly select the counts from the interface table */
IF PG_DEBUG = 'Y' THEN
pa_debug.G_err_stage := 'After import1';
l_stmt := 'BEGIN HXC_INTEGRATION_LAYER_V1_GRP.Update_Transaction_Status ( ' ||
' P_Process => :process, '||
' P_Status => :status, '||
' P_Exception_Description => :Exception_Desc );'||
Report will directly select the counts from the interface table */
EXCEPTION WHEN others THEN
IF PG_DEBUG = 'Y' THEN
log_message('log_message: ' || 'Error in post import procedure',1);
/* PA-K Changes: Commenting the call to update_status_counter
Report will directly select the counts from the interface table */
END import;
SELECT COUNT(DECODE(transaction_status_code, v_sucess, 1, NULL)),
COUNT(DECODE(transaction_status_code, v_failed, 1, NULL))
INTO P_sucess_counter, P_failed_counter
FROM pa_transaction_interface
WHERE interface_id = P_xface_id
AND transaction_status_code IN (v_sucess, v_failed);
PROCEDURE update_status_counter(P_xface_id IN NUMBER) IS
BEGIN
UPDATE pa_transaction_xface_ctrl_all
SET pre_import_reject_count = G_PRE_IMPORT_REJECT_COUNT,
pre_import_success_count = G_PRE_IMPORT_SUCCESS_COUNT,
import_reject_count = G_IMPORT_REJECT_COUNT,
import_success_count = G_IMPORT_SUCCESS_COUNT,
post_import_reject_count = G_POST_IMPORT_REJECT_COUNT,
post_import_success_count = G_POST_IMPORT_SUCCESS_COUNT
WHERE interface_id = P_xface_id;
cursor select_bc_packets(p_calling_mode in varchar2) is
select a.packet_id,
a.document_header_id,
a.document_distribution_id,
a.budget_ccid,
b.cdl_system_reference4,
a.project_id,
a.budget_line_id ,
a.budget_version_id
from pa_bc_packets a,
pa_transaction_interface b
where a.txn_interface_id = b.txn_interface_id
and a.document_header_id = b.cdl_system_reference2
and a.document_distribution_id = decode(p_calling_mode, 'AP VARIANCE',b.cdl_system_reference5
, 'AP ERV',b.cdl_system_reference5
, b.cdl_system_reference3)
and a.document_type = decode(p_calling_mode, 'AP VARIANCE', 'AP', 'AP ERV', 'AP' , 'PO RECEIPT', 'PO', 'PO RECEIPT NRTAX', 'PO')
and a.parent_bc_packet_id is null
and b.transaction_status_code = 'A'
and a.request_id = g_request_id
and to_char(a.request_id) = b.orig_transaction_reference
and a.status_code = 'P'
and b.transaction_source = p_calling_mode;
cursor select_disc_packets(p_calling_mode in varchar2) is
select a.packet_id,
b.cdl_system_reference2,
b.cdl_system_reference5,
a.budget_ccid,
b.cdl_system_reference4,
a.project_id,
a.budget_line_id ,
a.budget_version_id
from pa_bc_packets a,
pa_transaction_interface b,-- moac changes
pa_expenditure_items c
where a.txn_interface_id = b.txn_interface_id
and a.document_header_id = b.expenditure_item_id
and a.document_distribution_id = 1
and a.document_type = decode(p_calling_mode, 'AP DISCOUNTS', 'EXP')
and a.parent_bc_packet_id is null
and b.transaction_status_code = 'A'
and a.request_id = g_request_id
and a.request_id = b.orig_transaction_reference
and a.status_code = 'P'
and b.expenditure_item_id = c.expenditure_item_id
and b.transaction_source = p_calling_mode;
select a.packet_id ,
a.document_header_id ,
a.document_distribution_id ,
a.budget_ccid,
b.cdl_system_reference4,
a.project_id,
a.budget_line_id ,
a.budget_version_id
from pa_bc_packets a,
pa_transaction_interface b -- moac changes
where a.txn_interface_id = b.txn_interface_id
and a.document_header_id = b.cdl_system_reference2
and a.document_distribution_id = b.cdl_system_reference5
and a.document_type = 'AP'
and a.parent_bc_packet_id is null
and b.transaction_status_code = 'A'
and a.request_id = g_request_id
and b.transaction_source = p_calling_mode
and a.status_code = 'P'
union
select a.packet_id ,
to_number(b.cdl_system_reference2) ,
to_number(b.cdl_system_reference5) ,
a.budget_ccid,
b.cdl_system_reference4,
a.project_id,
a.budget_line_id ,
a.budget_version_id
from pa_bc_packets a,
pa_transaction_interface b, -- moac changes
ap_invoice_distributions c,
po_distributions d
where a.txn_interface_id = b.txn_interface_id
and a.document_header_id = d.po_header_id
and a.document_distribution_id = d.po_distribution_id
and c.invoice_id = b.cdl_system_reference2
and c.invoice_distribution_id = b.cdl_system_reference5
and c.po_distribution_id = d.po_distribution_id
and a.document_type = 'PO'
and a.parent_bc_packet_id is null
and b.transaction_status_code = 'A'
and a.request_id = g_request_id
and b.transaction_source IN ('AP INVOICE','AP NRTAX') -- Bug 5550268
and a.status_code = 'P'
union /*Self Assessed Tax Changes */
select a.packet_id ,
to_number(b.cdl_system_reference2) ,
to_number(b.cdl_system_reference5) ,
a.budget_ccid,
b.cdl_system_reference4,
a.project_id,
a.budget_line_id ,
a.budget_version_id
from pa_bc_packets a,
pa_transaction_interface b,
ap_self_assessed_tax_dist c,
po_distributions d
where a.txn_interface_id = b.txn_interface_id
and a.document_header_id = d.po_header_id
and a.document_distribution_id = d.po_distribution_id
and c.invoice_id = b.cdl_system_reference2
and c.invoice_distribution_id = b.cdl_system_reference5
and c.po_distribution_id = d.po_distribution_id
and a.document_type = 'PO'
and a.parent_bc_packet_id is null
and b.transaction_status_code = 'A'
and a.request_id = g_request_id
and b.transaction_source IN ('AP INVOICE','AP NRTAX')
and a.status_code = 'P';
SELECT txn.expenditure_item_id ,
pkt.bc_packet_id
FROM pa_transaction_interface txn,-- moac changes
pa_bc_packets pkt
WHERE pkt.txn_interface_id = txn.txn_interface_id
and pkt.request_id = g_request_id
and txn.transaction_status_code = 'A'
and pkt.status_code = 'P'
and pkt.document_type ='EXP'
and txn.adjusted_expenditure_item_id IS NOT NULL
and pkt.document_header_id <> txn.expenditure_item_id;
l_records_updated number;
l_records_updated := 0;
update pa_bc_packets a
set a.status_code = 'A'
where a.packet_id = p_packet_id
and a.request_id = g_request_id
and a.status_code = 'P';
l_records_updated := l_records_updated + SQL%ROWCOUNT;
pa_debug.G_err_stage := 'Updated PacketId [' || p_packet_id || ']-' || l_records_updated || ' records to A/C';
UPDATE pa_bc_packets
SET document_header_id = l_exp_item_id,
reference1 = l_exp_item_id
WHERE bc_packet_id = l_bc_pkt_id;
pa_debug.G_err_stage := 'Before opening the cursor select_bc_packets for ' || p_calling_mode;
open select_bc_packets(p_calling_mode) ;
fetch select_bc_packets
into l_packet_id,
l_sys_ref2,
l_doc_dist_id,
l_budget_ccid,
l_sys_ref4,
l_project_id,
l_budget_line_id ,
l_budget_version_id;
if select_bc_packets%notfound then
IF PG_DEBUG = 'Y' THEN
pa_debug.G_err_stage := 'No packets found, exit';
l_ExpItemId_temp_Tab.DELETE ;
select ei.expenditure_item_id
bulk collect into l_ExpItemId_temp_Tab
from pa_expenditure_items_all ei
where ei.document_header_id = l_sys_ref2
and ei.document_distribution_id = DECODE(p_calling_mode,'AP VARIANCE',l_doc_dist_id,'AP ERV',l_doc_dist_id,l_sys_ref4); -- Bug 5560524
update pa_cost_distribution_lines_all cdl
set cdl.Budget_CCID = l_budget_ccid ,
cdl.encumbrance_type_id = pa_funds_control_utils.get_encum_type_id(cdl.project_id,'STD'),
cdl.budget_line_id = DECODE(cdl.budget_line_id,NULL,l_budget_line_id,cdl.budget_line_id),
cdl.budget_version_id = DECODE(cdl.budget_version_id,NULL,l_budget_version_id,cdl.budget_version_id)
Where cdl.budget_ccid is null
and cdl.expenditure_item_id = l_ExpItemId_temp_Tab(j) ;
pa_debug.G_err_stage := 'Updated Ref2-Ref3 [' || l_sys_ref2 || ',' || l_doc_dist_id || ',' || l_sys_ref4 || ']-'
|| SQL%ROWCOUNT || ' Budget CCID';
IF select_bc_packets%isopen THEN
close select_bc_packets;
l_ExpItemId_temp_Tab.DELETE ;
select ei.expenditure_item_id
bulk collect into l_ExpItemId_temp_Tab
from pa_expenditure_items_all ei
where ei.document_header_id = l_sys_ref2
and ei.document_distribution_id = l_doc_dist_id; /*REL12 : AP Lines Uptake changes*/
update pa_cost_distribution_lines_all cdl
set cdl.Budget_CCID = l_budget_ccid ,
-- R12 Funds Management Uptake : Modified and added below columns logic
cdl.encumbrance_type_id = pa_funds_control_utils.get_encum_type_id(cdl.project_id,'STD'),
cdl.budget_line_id = DECODE(cdl.budget_line_id,NULL,l_budget_line_id,cdl.budget_line_id),
cdl.budget_version_id = DECODE(cdl.budget_version_id,NULL,l_budget_version_id,cdl.budget_version_id)
Where cdl.budget_ccid is null
and cdl.expenditure_item_id = l_ExpItemId_temp_Tab(j) ; /*REL12 : AP Lines Uptake changes*/
pa_debug.G_err_stage := 'Updated Ref2-Ref3 [' || l_sys_ref2 || ',' || l_doc_dist_id || ',' || l_sys_ref4 || ']-'
|| SQL%ROWCOUNT || ' Budget CCID';
pa_debug.G_err_stage := 'Before opening the cursor select_disc_packets for ' || p_calling_mode;
open select_disc_packets(p_calling_mode) ;
fetch select_disc_packets
into l_packet_id,
l_sys_ref2,
l_doc_dist_id,
l_budget_ccid,
l_sys_ref4,
l_project_id,
l_budget_line_id ,
l_budget_version_id;
if select_disc_packets%notfound then
IF PG_DEBUG = 'Y' THEN
pa_debug.G_err_stage := 'No discount packets found, exit';
l_ExpItemId_temp_Tab.DELETE ;
select ei.expenditure_item_id
bulk collect into l_ExpItemId_temp_Tab
from pa_expenditure_items_all ei
where ei.document_header_id = l_sys_ref2
and ei.document_distribution_id = l_doc_dist_id
and ei.document_payment_id = to_number(l_sys_ref4);
update pa_cost_distribution_lines_all cdl
set cdl.Budget_CCID = l_budget_ccid ,
cdl.encumbrance_type_id = pa_funds_control_utils.get_encum_type_id(cdl.project_id,'STD'),
-- R12 Funds Management Uptake
cdl.budget_line_id = DECODE(cdl.budget_line_id,NULL,l_budget_line_id,cdl.budget_line_id),
cdl.budget_version_id = DECODE(cdl.budget_version_id,NULL,l_budget_version_id,cdl.budget_version_id)
Where cdl.budget_ccid is null
and cdl.expenditure_item_id = l_ExpItemId_temp_Tab(j) ;
pa_debug.G_err_stage := 'Updated Ref2-Ref3 [' || l_sys_ref2 || ',' || l_sys_ref3 || ',' || l_sys_ref4 || ']-'
|| SQL%ROWCOUNT || ' Budget CCID';
close select_disc_packets;
pa_debug.G_err_stage := 'Update rejected interface records in pa_bc_packets to T';
update pa_bc_packets
set status_code = 'T',
result_code = 'F140'
where request_id = g_request_id
and status_code = 'P'
and txn_interface_id in
(select txn_interface_id /*REL12 : AP Lines Uptake changes*/
from pa_transaction_interface_all
where transaction_source = p_calling_mode
and transaction_status_code = 'R'
and orig_transaction_reference = to_char(g_request_id)
);
pa_debug.G_err_stage := 'In stage 1 Updated count to T = '|| SQL%ROWCOUNT;
update pa_bc_packets
set status_code = 'T',
result_code = 'F140'
where request_id = g_request_id
and status_code = 'P'
and (document_header_id, document_distribution_id) in
(select po_header_id, po_distribution_id
from po_distributions_all
where po_distribution_id in
(select po_distribution_id
from ap_invoice_distributions_all inv, pa_transaction_interface_all pti
where inv.invoice_id = pti.cdl_system_reference2
and inv.invoice_distribution_id = pti.cdl_system_reference5 /*REL12 : AP Lines Uptake changes*/
and pti.transaction_source = p_calling_mode
and pti.transaction_status_code = 'R'
and pti.orig_transaction_reference = to_char(g_request_id))
);
pa_debug.G_err_stage := 'In stage 2 Updated count to T = '|| SQL%ROWCOUNT;
log_message('log_message: Before update to T');
update pa_bc_packets
set status_code = 'T',
result_code = 'F140'
where status_code = 'P'
and request_id = G_REQUEST_ID;
log_message('log_message: ' || 'Updated ' || SQL%ROWCOUNT || ' packet statuses to T');
SELECT dist.po_distribution_id,
dist.project_id,
nvl(dist.base_quantity_variance,0), --PA.M QV and AV
NVL(dist.base_amount_variance,0),
inv.invoice_type_lookup_code,
dist.line_type_lookup_code
FROM ap_invoice_distributions dist,
ap_invoices inv
WHERE dist.invoice_id = p_invoice_id
AND dist.invoice_distribution_id = p_invoice_distribution_id
AND inv.invoice_id = dist.invoice_id
union /*Self Assessed Tax Changes*/
SELECT dist.po_distribution_id,
dist.project_id,
nvl(dist.base_quantity_variance,0), --PA.M QV and AV
NVL(dist.base_amount_variance,0),
inv.invoice_type_lookup_code,
dist.line_type_lookup_code
FROM ap_self_assessed_tax_dist dist,
ap_invoices inv
WHERE dist.invoice_id = p_invoice_id
AND dist.invoice_distribution_id = p_invoice_distribution_id
AND inv.invoice_id = dist.invoice_id
;
Select Period_Name,PERIOD_YEAR
Into l_Period_Name,l_period_year
From Gl_Period_Statuses
Where Application_Id = 101
And Trunc(P_GL_Date) Between Trunc(Start_Date) And Trunc(End_Date)
And Set_Of_Books_Id = G_SobId
And adjustment_period_flag = 'N'; -- added for bug 3083625
Select Gl_Bc_Packets_s.NextVal
Into l_Packet_Id
From dual;
/* Since we have divided the funds check packet insert code into 3 sections
the bc packet id will be generated in each of the individual sections
rather than in the beginning
*/
--Bug 2905892
--Get the GL Period Name from txn's gl_date. This will be inserted into pa_bc_packets
--for both the reversing raw and burden lines and also the EXP lines.
--Funds Check API will derive the GL_Date based on the Period_Name, so what
--we insert for gl_date is irrelevant here.
IF PG_DEBUG = 'Y' THEN
pa_debug.G_err_stage := 'Calling G_FC_Period_Name';
pa_debug.G_err_stage := 'Selecting PO Dist Id for AP Invoice';
/*Call to AP_PA_API_PKG.Get_Inv_Amount_Var is deleted and introduced logic to fetch
amt variance from ap_invoice_distributions*/
IF PG_DEBUG = 'Y' THEN
log_message('log_message: ' || 'Po Dist = ' || l_po_dist_id);
pa_debug.G_err_stage := 'PO Dist Found, Selecting Receipt Accrual Flag';
Select nvl(accrue_on_receipt_flag,'N'),
po_header_id
Into l_acc_flag,
l_po_hdr_id
from Po_distributions
where po_distribution_id = l_po_dist_id;
pa_debug.G_err_stage := 'Start funds check packet insert for AP - non accrue on receipt diff enc type';
pa_debug.G_err_stage := 'Start funds check packet insert for PO Receipts';
pa_debug.G_err_stage := 'Start funds check packet insert for AP Variance records';
pa_debug.G_err_stage := 'Selecting disc amount for AP Invoice';
log_message('log_message: Before calling insert_ap_bc_packets api');
insert_ap_bc_packets( p_packet_id => p_packet_id,
p_sys_ref2 => p_sys_ref2,
p_sys_ref4 => p_sys_ref4,
p_sys_ref5 => p_sys_ref5,
p_acct_raw_cost => p_acct_raw_cost,
p_acct_bur_cost => 0,
p_fc_document_type => p_fc_document_type ,
p_txn_source => p_txn_source ,
p_adj_exp_item_id => p_adj_exp_item_id ,
p_txn_interface_id => p_txn_interface_id);
log_message('log_message: After calling insert_ap_bc_packets api');
log_message('log_message: Before calling insert_ap_bc_packets api');
insert_ap_bc_packets( p_packet_id => p_packet_id,
p_sys_ref2 => p_sys_ref2,
p_sys_ref4 => NULL,
p_sys_ref5 => p_sys_ref5,
p_acct_raw_cost => p_acct_raw_cost,
p_acct_bur_cost => 0,
p_fc_document_type => p_fc_document_type ,
p_txn_source => p_txn_source ,
p_adj_exp_item_id => p_adj_exp_item_id ,
p_txn_interface_id => p_txn_interface_id);
select acct_exchange_rate
from pa_transaction_interface_all
where txn_interface_id = p_txn_interface_id;
/* Bug 5731450 : The following two select statements are modified to also fetch the PO exchange rate. */
select sum(nvl(accounted_dr,0)-nvl(accounted_cr,0)),SUM(POD.rate)/SUM(1)
into l_pkt_summ,l_pkt_rate
from pa_bc_packets bcc,
po_distributions pod --Added for Bug#3693974
where pod.po_header_id = p_sys_ref2
and pod.po_distribution_id = p_sys_ref3
and ((
bcc.document_type = 'PO'
and pod.po_distribution_id = bcc.document_distribution_id
and pod.po_header_id = bcc.document_header_id
)
or
(
bcc.document_type = 'CC_C_PAY'
-- 4456442, 4221848
-- Bug : PQE:R12: PAAPIMP_SI: ORA-01722 IN PO_FUNDS_CHECK AND ORA-01400
and pod.req_header_reference_num = to_char(bcc.document_header_id)
and pod.req_line_reference_num = to_char(bcc.document_distribution_id)
)
)
and (
(bcc.Status_Code in('A','C'))
or
(bcc.Status_Code = 'P' and bcc.request_id = G_Request_Id)
)
and bcc.Parent_Bc_Packet_Id is NULL;
select sum(nvl(accounted_dr,0)-nvl(accounted_cr,0)),SUM(POD.rate)/SUM(1)
into l_com_summ,l_com_rate
from pa_bc_commitments bcc,
po_distributions pod --Added for Bug#3693974
where pod.po_header_id = p_sys_ref2
and pod.po_distribution_id = p_sys_ref3
and ((
bcc.document_type = 'PO'
and pod.po_distribution_id = bcc.document_distribution_id
and pod.po_header_id = bcc.document_header_id
)
or
(
bcc.document_type = 'CC_C_PAY'
-- 4456442, 4221848
-- Bug : PQE:R12: PAAPIMP_SI: ORA-01722 IN PO_FUNDS_CHECK AND ORA-01400
and pod.req_header_reference_num = to_char(bcc.document_header_id)
and pod.req_line_reference_num = to_char(bcc.document_distribution_id)
)
)
and bcc.Parent_Bc_Packet_Id is NULL;
pa_debug.G_err_stage := 'Before Calling insert_po_bc_packets';
insert_po_bc_packets (p_packet_id => p_packet_id ,
p_sys_ref2 => p_sys_ref2 ,
p_sys_ref4 => p_sys_ref4, -- Bug 5530897
p_sys_ref3 => p_sys_ref3 ,
p_acct_raw_cost => p_acct_raw_cost,
p_cmt_raw_cost => l_rev_acct_raw_cost,
p_fc_document_type => p_fc_document_type,
p_txn_source => p_txn_source ,
p_adj_exp_item_id => p_adj_exp_item_id,
p_txn_interface_id => p_txn_interface_id);
pa_debug.G_err_stage := 'After Calling insert_po_bc_packets';
pa_debug.G_err_stage := 'Start funds check packet insert for AP records that accrue on receipt';
/* Deleted code for IPV and ERV as these will be interfaced as seperate EXP's
in REL12 */
/* Replaced ap_funds_check calls for QV and AV with one call to ap_funds_check
.In insert_ap_bc_packets procedure logic is introduced to relieve all the varaince/invoice
amounts associated with invoice distribution */
IF NVL(p_base_qty_var_amt,0) <> 0 THEN
IF PG_DEBUG = 'Y' THEN
pa_debug.G_err_stage := 'Call ap_funds_check to reverse out the ap p_base_qty_var_amt = '||p_base_qty_var_amt;
update pa_bc_packets
set status_code = 'T',
result_code = 'F140'
where status_code = 'P'
and request_id = P_REQUEST_ID;
log_message('log_message: ' || 'Updated ' || SQL%ROWCOUNT || ' packet statuses to T');
update pa_bc_packets
set status_code = 'T',
result_code = 'F140'
where status_code = 'P'
--Bug 3592289 changed p_packet_id to packet_id
--and p_packet_id = p_packet_id;
log_message('log_message: ' || 'Updated ' || SQL%ROWCOUNT || ' packet statuses to T');
SELECT
expenditure_item_id
INTO
dummy
FROM
pa_expenditure_items
WHERE
expenditure_item_id = G_adj_item_id
FOR UPDATE NOWAIT;
PoLineTaskTab.DELETE;
PoAmtTab.DELETE;
select sum(nvl(accounted_dr,0)-nvl(accounted_cr,0))
from pa_bc_packets bcc
where bcc.document_type = 'AP'
and bcc.document_header_id = p_sys_ref2
and bcc.document_distribution_id = p_sys_ref5
and ( (bcc.Status_Code in('A','C'))
or
(bcc.Status_Code = 'P' and bcc.request_id = G_Request_Id))
and bcc.Parent_Bc_Packet_Id is NULL;
select sum(nvl(accounted_dr,0)-nvl(accounted_cr,0))
from pa_bc_commitments bcc
where bcc.document_type = 'AP'
and bcc.document_header_id = p_sys_ref2
and bcc.document_distribution_id = p_sys_ref5
and bcc.Parent_Bc_Packet_Id is NULL;
SELECT NVL(b.invoice_dist_base_amount , b.invoice_dist_amount) amount
from ap_payment_hist_dists b,
ap_invoice_distributions_all apd
where b.invoice_payment_id = p_sys_ref4
and b.invoice_distribution_id = p_sys_ref5
and b.pay_dist_lookup_code = 'DISCOUNT'
and apd.invoice_distribution_id = b.invoice_distribution_id
and NVL(apd.historical_flag,'N') <> 'Y'
and apd.expenditure_item_date >=G_Profile_Discount_Start_date
and apd.line_type_lookup_code = decode ( G_discount_Method,
'TAX', decode (apd.line_type_lookup_code,
'TIPV', 'TIPV',
'TERV','TERV',
'TRV', 'TRV',
'NONREC_TAX') ,
'SYSTEM', 'NOT APPLICABLE',
apd.line_type_lookup_code ) ;
SELECT NVL(paydist.invoice_dist_base_amount,paydist.invoice_dist_amount)
FROM ap_payment_hist_dists Paydist
WHERE paydist.pay_dist_lookup_code = 'CASH'
AND Paydist.invoice_distribution_id = p_sys_ref5
AND PayDIST.invoice_payment_id = p_sys_ref4
AND EXISTS
(SELECT 1
FROM ap_invoice_payments Pay,
ap_invoices_all inv
WHERE pay.invoice_payment_id = p_sys_ref4
AND pay.invoice_id = inv.invoice_id
AND NVL(pay.exchange_rate,0) <> NVL(inv.exchange_rate,0));
pa_debug.G_err_stage := 'Before calling insert_cash_ap_bc_packets';
insert_cash_ap_bc_packets( p_packet_id => p_packet_id,
p_sys_ref2 => p_sys_ref2,
p_sys_ref5 => p_sys_ref5,
p_acct_raw_cost => p_acct_raw_cost,
p_fc_document_type => p_fc_document_type ,
p_txn_source => p_txn_source ,
p_adj_exp_item_id => p_adj_exp_item_id,
p_txn_interface_id => p_txn_interface_id,
p_cash_pay_to_relieve => l_raw_amount );
log_message('log_message: ' || 'After calling insert_cash_ap_bc_packets');
PROCEDURE insert_ap_bc_packets(p_packet_id IN NUMBER,
p_sys_ref2 IN NUMBER,
p_sys_ref4 IN VARCHAR2,
p_sys_ref5 IN NUMBER,
p_acct_raw_cost IN NUMBER,
p_acct_bur_cost IN NUMBER,
p_fc_document_type IN VARCHAR2,
p_txn_source IN VARCHAR2,
p_adj_exp_item_id IN NUMBER,
p_txn_interface_id IN NUMBER) IS
-- Cursor to fetch data from pa_transaction_interface_table.This data is later used
-- in autonomous fundscheck insert_ap_bc_pkts_autonomous procedure.
CURSOR c_pa_txn_interface_data (p_adj_act_fc_req VARCHAR2,
p_act_fc_req VARCHAR2) IS
Select txn.Project_Id
,txn.Task_Id
,txn.Expenditure_Type
,txn.Expenditure_Item_Date
,nvl(txn.OVERRIDE_TO_ORGANIZATION_ID,txn.Org_Id)
,bv.Budget_Version_Id
-- R12 AP lines uptake : Prepayment changes
-- For Prepayment application/pre-payment matched to PO records consider p_acct_raw_cost as amount
,DECODE(txn_interface_id,p_txn_interface_id, p_acct_raw_cost,txn.acct_raw_cost)
,0
,DECODE(txn_interface_id,p_txn_interface_id, p_acct_raw_cost,txn.acct_raw_cost)
,0
,txn.cr_code_combination_id
,txn.Org_id
,txn.txn_interface_id --REL12
,txn.vendor_id /*bug 13601768*/
From Pa_Budget_Versions bv,
Pa_Budget_Types bt,
pa_budgetary_control_options pbct,
pa_transaction_interface_all txn
Where bv.budget_type_code = bt.budget_type_code
and bt.budget_amount_code = 'C'
and bv.current_flag = 'Y'
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 = txn.project_id
AND txn.txn_interface_id in ( SELECT txn1.txn_interface_id
FROM pa_transaction_interface_all txn1
WHERE txn1.TRANSACTION_SOURCE = p_txn_source -- Condition for using index
-- new index usage needs to be verified for below columns
AND txn1.cdl_system_reference2 = p_sys_ref2
AND txn1.cdl_system_reference5 = p_sys_ref5
AND txn1.fc_document_type in ('ACT','ALL')
AND txn1.adjusted_expenditure_item_id IS NOT NULL
AND p_adj_act_fc_req = 'Y'
UNION ALL -- R12 AP lines uptake : Prepayment changes : Added to pick current transaction for prepayment scenarios
SELECT p_txn_interface_id
FROM DUAL
WHERE p_act_fc_req = 'Y');
log_message('log_message: ' || 'In insert_bc_packets - Start');
log_message('log_message: ' || 'In insert_bc_packets - Value of p_packet_id '||p_packet_id);
log_message('log_message: ' || 'In insert_bc_packets - Value of p_sys_ref2 '||p_sys_ref2 );
log_message('log_message: ' || 'In insert_bc_packets - Value of p_sys_ref4 '||p_sys_ref4 );
log_message('log_message: ' || 'In insert_bc_packets - Value of p_sys_ref5 '||p_sys_ref5 );
log_message('log_message: ' || 'In insert_bc_packets - Value of p_acct_raw_cost '||p_acct_raw_cost);
log_message('log_message: ' || 'In insert_bc_packets - Value of p_acct_bur_cost '||p_acct_bur_cost);
log_message('log_message: ' || 'In insert_bc_packets - Value of p_fc_document_type '||p_fc_document_type );
log_message('log_message: ' || 'In insert_bc_packets - Value of p_txn_source '||p_txn_source);
log_message('log_message: ' || 'In insert_bc_packets - Value of p_adj_exp_item_id '||p_adj_exp_item_id);
log_message('log_message: ' || 'In insert_bc_packets - Value of p_txn_interface_id '||p_txn_interface_id);
log_message('log_message: ' || 'In insert_bc_packets - Value of l_comm_fc_req '||l_comm_fc_req);
log_message('log_message: ' || 'In insert_bc_packets - Value of l_adj_act_fc_req '||l_adj_act_fc_req );
log_message('log_message: ' || 'In insert_bc_packets - Value of l_act_fc_req '||l_act_fc_req );
log_message('log_message: ' || 'In insert_bc_packets - # of recs fetched from interface table c_pa_txn_interface_data = '||g_xface_Txn_interface_tbl.count);
log_message('log_message: ' || 'In insert_bc_packets - calling insert_ap_bc_pkts_autonomous');
insert_ap_bc_pkt_autonomous
(p_packet_id => p_packet_id,
p_sys_ref2 => p_sys_ref2,
p_sys_ref4 => p_sys_ref4,
p_sys_ref5 => p_sys_ref5,
p_acct_raw_cost => p_acct_raw_cost,
p_acct_bur_cost => p_acct_bur_cost,
p_fc_document_type => p_fc_document_type,
p_txn_source => p_txn_source,
p_adj_exp_item_id => p_adj_exp_item_id ,
p_txn_interface_id => p_txn_interface_id,
p_comm_fc_req => l_comm_fc_req,
p_act_fc_req => l_act_fc_req,
p_adj_act_fc_req => l_adj_act_fc_req );
log_message('log_message: ' || 'After calling insert_ap_bc_pkt_autonomous api');
log_message('log_message: ' || 'In insert_ap_bc_packets - End');
log_message('log_message:I n insert_bc_packets exception' || sqlerrm||' Returning from insert_bc_packets');
END insert_ap_bc_packets;
PROCEDURE INSERT_AP_BC_PKT_AUTONOMOUS
(p_packet_id IN NUMBER,
p_sys_ref2 IN NUMBER,
p_sys_ref4 IN VARCHAR2,
p_sys_ref5 IN NUMBER,
p_acct_raw_cost IN NUMBER,
p_acct_bur_cost IN NUMBER,
p_fc_document_type IN VARCHAR2,
p_txn_source IN VARCHAR2,
p_adj_exp_item_id IN NUMBER,
p_txn_interface_id IN NUMBER,
p_comm_fc_req IN VARCHAR2,
p_act_fc_req IN VARCHAR2,
p_adj_act_fc_req IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT Pa_Bc_Packets_s.NextVal
FROM dual;
SELECT po_distribution_id
FROM ap_invoice_distributions
WHERE invoice_id = p_sys_ref2
AND invoice_distribution_id = p_sys_ref5
union /*Self Assessed Tax Changes*/
SELECT po_distribution_id
FROM ap_self_assessed_tax_dist
WHERE invoice_id = p_sys_ref2
AND invoice_distribution_id = p_sys_ref5;
SELECT po_line_id
FROM po_distributions_all
WHERE po_distribution_id = p_po_dist_id;
SELECT a.vendor_id
FROM ap_invoices_all A, ap_invoice_distributions_all aid
WHERE A.invoice_id = aid.invoice_id
AND A.invoice_id = p_sys_ref2
AND aid.invoice_distribution_id = p_sys_ref5
UNION /* Added for bug 13859614 */
SELECT a.vendor_id
FROM ap_invoices_all A, ap_self_assessed_tax_dist_all aid
WHERE A.invoice_id = aid.invoice_id
AND A.invoice_id = p_sys_ref2
AND aid.invoice_distribution_id = p_sys_ref5;
Select bcc.Set_Of_Books_Id
,bcc.Period_Year
,bcc.Project_Id
,bcc.Task_Id
,bcc.Expenditure_Type
,bcc.Expenditure_Item_Date
,bcc.Expenditure_Organization_Id
,bcc.Budget_Version_Id
,bcc.budget_line_id -- R12 Funds Management uptake
,bcc.Document_Type
,bcc.Document_Header_Id
,bcc.Document_Distribution_Id
,bcc.Entered_Cr
,bcc.Entered_Dr
,bcc.Accounted_Cr
,bcc.Accounted_Dr
,bcc.Actual_Flag
,bcc.Txn_Ccid
,bcc.Je_Category_Name
,bcc.Je_Source_Name
,bcc.Org_Id
,bcc.Pa_Date
,bcc.bc_packet_id
,bcc.packet_id
,bcc.parent_bc_packet_id
,bcc.encumbrance_type_id
,bcc.document_header_id_2
,bcc.document_distribution_type
From Pa_Bc_Commitments_all bcc
Where bcc.Document_Header_Id = p_sys_ref2
and bcc.Document_Distribution_Id =p_sys_ref5
--PA-J Receipt accrual changes
and bcc.document_type = 'AP'
and bcc.exp_item_id IS NULL -- Bug 5014138 : to pick just the encumbrance record
and bcc.actual_flag = 'E' -- Bug 5014138 : to pick just the encumbrance record
-- Bug 5550268 : For variance we should be picking just the variance records
/* Bug 5560524 : Modified the following condition such that the burden commitment relieving record is also
fetched for 'AP VARIANCE' transaction source. */
and ((nvl(bcc.accounted_dr,0)-nvl(bcc.accounted_cr,0) = p_acct_raw_cost
and p_txn_source = 'AP VARIANCE' and bcc.parent_bc_packet_id is null)
OR
p_txn_source <> 'AP VARIANCE'
OR
(bcc.parent_bc_packet_id is NOT null AND p_txn_source = 'AP VARIANCE')
);
Select bcc.Set_Of_Books_Id
,bcc.Period_Year
,bcc.Project_Id
,bcc.Task_Id
,bcc.Expenditure_Type
,bcc.Expenditure_Item_Date
,bcc.Expenditure_Organization_Id
,bcc.Budget_Version_Id
,bcc.budget_line_id -- R12 Funds Management uptake
,bcc.Document_Type
,bcc.Document_Header_Id
,bcc.Document_Distribution_Id
,bcc.Entered_Cr
,bcc.Entered_Dr
,bcc.Accounted_Cr
,bcc.Accounted_Dr
,bcc.Actual_Flag
,bcc.Txn_Ccid
,bcc.Je_Category_Name
,bcc.Je_Source_Name
,bcc.Org_Id
,bcc.Pa_Date
,bcc.bc_packet_id
,bcc.packet_id
,bcc.parent_bc_packet_id
,bcc.encumbrance_type_id
,bcc.document_header_id_2
,bcc.document_distribution_type
From Pa_Bc_Packets bcc
where bcc.Document_Header_Id = p_sys_ref2
and bcc.Document_Distribution_Id =p_sys_ref5
and bcc.Status_Code in ('A','C')
and bcc.document_type = 'AP'
and bcc.exp_item_id IS NULL -- Bug 5014138 : to pick just the encumbrance record
and bcc.actual_flag = 'E' -- Bug 5014138 : to pick just the encumbrance record
-- Bug 5550268 : For variance we should be picking just the variance records
/* Bug 5560524 : Modified the following condition such that the burden commitment relieving record is also
fetched for 'AP VARIANCE' transaction source. */
and ((nvl(bcc.accounted_dr,0)-nvl(bcc.accounted_cr,0) = p_acct_raw_cost
and p_txn_source = 'AP VARIANCE' and bcc.parent_bc_packet_id is null)
OR
p_txn_source <> 'AP VARIANCE'
OR
(bcc.parent_bc_packet_id is NOT null AND p_txn_source = 'AP VARIANCE')
);
SELECT bc_packet_id
FROM pa_bc_packets
WHERE packet_id = p_packet_id
AND Document_Header_Id = p_sys_ref2
AND Document_Distribution_Id =p_sys_ref5
AND parent_bc_packet_id IS NULL
AND status_code ='P';
log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - Start');
log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - After fetching CWK related data l_po_dist_id'||l_po_dist_id);
log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - After fetching CWK related data l_po_line_id'||l_po_line_id);
log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - Fetchign data from bc commitments');
log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - Fetchign data from bc packets');
log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - Number of commitment records fetched = '||g_bc_packet_id_tbl.count);
Insert Into Pa_Bc_Packets
(Packet_Id
,Bc_Packet_Id
,Parent_Bc_Packet_Id
,Set_Of_Books_Id
,Gl_Date
,Period_Name
,Period_Year
,Pa_Date
,Project_Id
,Task_Id
,Expenditure_Type
,Expenditure_Item_Date
,Expenditure_Organization_Id
,Budget_Version_Id
,budget_line_id -- R12 Funds management uptake
,Document_Type
,Document_Header_Id
,Document_Distribution_Id
,Entered_Dr
,Entered_Cr
,Accounted_Dr
,Accounted_Cr
,Request_Id
,Program_Id
,Program_Application_Id
,Program_Update_Date
,Actual_Flag
,Last_Update_Date
,Last_Updated_By
,Created_By
,Creation_Date
,Last_Update_Login
,Txn_Ccid
,Burden_Cost_Flag
,Status_Code
,Je_Category_Name
,Je_Source_Name
,Org_id
,Exp_Item_Id
,Document_Line_Id
,reference1
,reference2
,reference3
,txn_interface_id --REL12
,encumbrance_type_id
,document_header_id_2
,document_distribution_type
)
Select p_packet_id
,g_bc_packet_id_tbl(i) -- Later updated with newly generated Id's
,g_bc_parent_pkt_id_tbl(i) -- Later updated with newly generated Id's
,g_sob_Id_tbl(i)
,G_FC_Gl_Date
,G_FC_Period_Name
,g_Period_Year_tbl(i)
,g_Pa_Date_tbl(i)
,g_project_id_tbl(i)
,g_task_id_tbl(i)
,g_exp_type_tbl(i)
,g_ei_date_tbl(i)
,g_exp_org_id_tbl(i)
,g_bud_ver_id_tbl(i)
,g_budget_line_id_tbl(i)
,g_Document_Type_tbl(i)
,g_Doc_Header_Id_tbl(i)
,g_Doc_dist_Id_tbl(i)
,g_Entered_Cr_tbl(i) -- reversing dr
,g_Entered_Dr_tbl(i) -- reversing cr
,g_acct_Cr_tbl(i)
,g_acct_Dr_tbl(i)
,g_Request_Id
,G_Program_Id
,G_PROG_APPL_ID
,sysdate
,g_Actual_Flag_tbl(i)
,sysdate
,G_CONC_LOGIN_ID
,G_CONC_LOGIN_ID
,sysdate
,G_CONC_LOGIN_ID
,g_Txn_Ccid_tbl(i)
,'R'
,'P'
,g_Je_Catg_Name_tbl(i)
,g_Je_sorce_Name_tbl(i)
,g_org_id_tbl(i)
,X_ei_id
,l_po_line_id
,'EXP'
,X_ei_id
,1
,p_txn_interface_id --REL12
,g_enc_type_id_tbl(i)
,g_doc_hdr_id_2_tbl(i)
,g_doc_dist_type_tbl(i)
From dual;
log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - Inserted '||SQL%ROWCOUNT||' AP reversing raw and burden line(s) into bc packets');
log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous -Logic to update correct bc pkt Id/parent BC pkt Id');
log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous -Old Bc Packet Id = ' || c_cur.bc_packet_id||'New Bc Packet Id = ' || l_Raw_Bc_Packet_Id ||'Packet Id = ' || p_packet_id);
-- Update raw records with new bc_packet_id
UPDATE Pa_Bc_Packets
SET bc_packet_id = l_Raw_Bc_Packet_Id
WHERE packet_id = p_packet_id
AND bc_packet_id = c_cur.bc_packet_id;
log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - NUmber of raw packets updated with new bc_packet_id '||SQL%ROWCOUNT);
UPDATE Pa_Bc_Packets
SET bc_packet_id = Pa_Bc_Packets_s.NextVal
,parent_bc_packet_id = l_Raw_Bc_Packet_Id
WHERE packet_id = p_packet_id
AND parent_bc_packet_id = c_cur.bc_packet_id;
log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - NUmber of burden packets updated with new parent_bc_packet_id '||SQL%ROWCOUNT);
log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - Before inserting ACTUAL raw line from bc packets');
Insert Into Pa_Bc_Packets
(Packet_Id
,Bc_Packet_Id
,Parent_Bc_Packet_Id
,Set_Of_Books_Id
,gl_date
,Period_Name
,Period_Year
,Pa_Date
,Project_Id
,Task_Id
,Expenditure_Type
,Expenditure_Item_Date
,Expenditure_Organization_Id
,Budget_Version_Id
,budget_line_id -- R12 Funds management uptake
,Document_Type
,Document_Header_Id
,Document_Distribution_Id
,Entered_Dr
,Entered_Cr
,Accounted_Dr
,Accounted_Cr
,Request_Id
,Program_Id
,Program_Application_Id
,Program_Update_Date
,Actual_Flag
,Last_Update_Date
,Last_Updated_By
,Created_By
,Creation_Date
,Last_Update_Login
,Txn_Ccid
,Burden_Cost_Flag
,Status_Code
,Je_Category_Name
,Je_Source_Name
,Org_id
,Document_Line_Id
,reference1
,reference2
,reference3
,txn_interface_id --REL12
,vendor_id
)
Select p_Packet_Id
,Pa_Bc_Packets_s.NextVal
,-1
,g_sob_Id_tbl(i)
,G_FC_Gl_Date
,G_FC_Period_Name
,g_Period_Year_tbl(i)
,g_Pa_Date_tbl(i)
,g_project_id_tbl(i)
,g_task_id_tbl(i)
,g_exp_type_tbl(i)
,g_ei_date_tbl(i)
,g_exp_org_id_tbl(i)
,g_bud_ver_id_tbl(i)
,g_budget_line_id_tbl(i) -- R12 Funds management uptake
,'EXP'
,X_ei_id
,1
,DECODE(p_txn_source,'AP DISCOUNTS',p_acct_raw_cost,g_Entered_Dr_tbl(i))
,DECODE(p_txn_source,'AP DISCOUNTS',0,g_Entered_Cr_tbl(i))
,DECODE(p_txn_source,'AP DISCOUNTS',p_acct_raw_cost,g_acct_Dr_tbl(i))
,DECODE(p_txn_source,'AP DISCOUNTS',0,g_acct_Cr_tbl(i))
,g_Request_Id
,g_program_id
,g_prog_appl_id
,sysdate
,'A'
,sysdate
,g_conc_login_id
,g_conc_login_id
,sysdate
,g_conc_login_id
,g_Txn_Ccid_tbl(i)
,'N'
,'P'
,'Expenditures'
,'Project Accounting'
,g_org_id_tbl(i)
,DECODE(p_txn_source,'AP DISCOUNTS',NULL,l_po_line_id)
,DECODE(p_txn_source,'AP DISCOUNTS',NULL,'EXP')
,DECODE(p_txn_source,'AP DISCOUNTS',NULL,x_Ei_Id)
,DECODE(p_txn_source,'AP DISCOUNTS',NULL,1)
,p_txn_interface_id --REL12
,l_vendor_id
From Pa_Budget_Versions bv,
Pa_Budget_Types bt,
pa_budgetary_control_options pbct
Where bt.budget_type_Code = bv.budget_type_Code
and bt.budget_amount_code = 'C'
and bv.project_id = g_project_id_tbl(i)
and bv.current_flag = 'Y'
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 g_bc_parent_pkt_id_tbl(i) IS NULL; -- Need to fire only for raw records
/* Bug 5984525 : The following code is modified so that ACTUAL lines are inserted correctly
into pa bc packets while interfacing discounts to Projects. */
IF p_txn_source = 'AP DISCOUNTS' AND SQL%ROWCOUNT = 1 THEN
IF PG_DEBUG = 'Y' THEN
log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - Inserted 1 ACTUAL raw line(s) into bc packets');
log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - Before inserting adjusted ACTUAL raw line from transaction import table');
Insert Into Pa_Bc_Packets
(Packet_Id
,Bc_Packet_Id
,Parent_Bc_Packet_Id
,Set_Of_Books_Id
,Gl_Date
,Period_Name
,Period_Year
,Pa_Date
,Project_Id
,Task_Id
,Expenditure_Type
,Expenditure_Item_Date
,Expenditure_Organization_Id
,Budget_Version_Id
,Document_Type
,Document_Header_Id
,Document_Distribution_Id
,Entered_Dr
,Entered_Cr
,Accounted_Dr
,Accounted_Cr
,Request_Id
,Program_Id
,Program_Application_Id
,Program_Update_Date
,Actual_Flag
,Last_Update_Date
,Last_Updated_By
,Created_By
,Creation_Date
,Last_Update_Login
,Txn_Ccid
,Burden_Cost_Flag
,Status_Code
,Je_Category_Name
,Je_Source_Name
,Org_id
,Document_Line_Id
,reference1
,reference2
,reference3
,txn_interface_id --REL12
,vendor_id
)
Select p_Packet_Id
,Pa_Bc_Packets_s.NextVal
,-1
,g_sobid
,G_FC_Gl_Date
,G_FC_Period_Name
,G_FC_Period_Year
,G_PaDate
,g_xface_project_id_tbl(i)
,g_xface_task_id_tbl(i)
,g_xface_exp_type_tbl(i)
,g_xface_ei_date_tbl(i)
,g_xface_exp_org_id_tbl(i)
,g_xface_bud_ver_id_tbl(i)
,'EXP'
,X_ei_id
,1
-- R12 AP lines uptake : Prepayment changes
-- For Prepayment application/pre-payment matched to PO records consider p_acct_raw_cost as amount
,g_xface_Entered_Dr_tbl(i)
,g_xface_acct_Cr_tbl(i)
,g_xface_acct_Dr_tbl(i)
,g_xface_acct_Cr_tbl(i)
,g_Request_Id
,g_program_id
,g_prog_appl_id
,sysdate
,'A' --Actual_Flag
,sysdate
,g_conc_login_id
,g_conc_login_id
,sysdate
,g_conc_login_id
,g_xface_Txn_Ccid_tbl(i)
,'N'
,'P'
,'Expenditures'
,'Project Accounting'
,g_xface_org_id_tbl(i)
,l_po_line_id
,'EXP'
,X_Ei_Id
,1
,g_xface_Txn_interface_tbl(i) --REL12
,g_xface_vendor_id_tbl(i)
From dual;
log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - Inserted '||SQL%ROWCOUNT||' ACTUAL raw line(s) into bc packets');
log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - End');
log_message('log_message: In insert_ap_bc_pkt_autonomous - ' || sqlerrm||' Returning from insert_ap_bc_pkt_autonomous');
END insert_ap_bc_pkt_autonomous;
PROCEDURE insert_cash_ap_bc_packets(p_packet_id IN NUMBER,
p_sys_ref2 IN NUMBER,
p_sys_ref5 IN NUMBER,
p_acct_raw_cost IN NUMBER,
p_fc_document_type IN VARCHAR2,
p_txn_source IN VARCHAR2,
p_adj_exp_item_id IN NUMBER,
p_txn_interface_id IN NUMBER,
p_cash_pay_to_relieve IN NUMBER DEFAULT 0) IS
-- Cursor to fetch data from pa_transaction_interface_table.This data is later used
-- in autonomous fundscheck insert_ap_bc_pkts_autonomous procedure.
CURSOR c_pa_txn_interface_data (p_adj_act_fc_req VARCHAR2,
p_act_fc_req VARCHAR2) IS
Select txn.Project_Id
,txn.Task_Id
,txn.Expenditure_Type
,txn.Expenditure_Item_Date
,nvl(txn.OVERRIDE_TO_ORGANIZATION_ID,txn.Org_Id)
,bv.Budget_Version_Id
-- R12 AP lines uptake : Prepayment changes
-- For Prepayment application/pre-payment matched to PO records consider p_acct_raw_cost as amount
,DECODE(txn_interface_id,p_txn_interface_id, p_acct_raw_cost,txn.acct_raw_cost)
,0
,DECODE(txn_interface_id,p_txn_interface_id, p_acct_raw_cost,txn.acct_raw_cost)
,0
,txn.cr_code_combination_id
,txn.Org_id
,txn.txn_interface_id --REL12
,txn.vendor_id
From Pa_Budget_Versions bv,
Pa_Budget_Types bt,
pa_budgetary_control_options pbct,
pa_transaction_interface_all txn
Where bv.budget_type_code = bt.budget_type_code
and bt.budget_amount_code = 'C'
and bv.current_flag = 'Y'
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 = txn.project_id
AND txn.txn_interface_id in ( SELECT txn1.txn_interface_id
FROM pa_transaction_interface_all txn1
WHERE txn1.TRANSACTION_SOURCE = p_txn_source -- Condition for using index
-- new index usage needs to be verified for below columns
AND txn1.cdl_system_reference2 = p_sys_ref2
AND txn1.cdl_system_reference5 = p_sys_ref5
AND txn1.fc_document_type in ('ACT','ALL')
AND txn1.adjusted_expenditure_item_id IS NOT NULL
AND p_adj_act_fc_req = 'Y'
UNION ALL -- R12 AP lines uptake : Prepayment changes : Added to pick current transaction for prepayment scenarios
SELECT p_txn_interface_id
FROM DUAL
WHERE p_act_fc_req = 'Y');
log_message('log_message: ' || 'In insert_cash_ap_bc_packets - Start');
log_message('log_message: ' || 'In insert_cash_ap_bc_packets - Value of p_packet_id '||p_packet_id);
log_message('log_message: ' || 'In insert_cash_ap_bc_packets - Value of p_sys_ref2 '||p_sys_ref2 );
log_message('log_message: ' || 'In insert_cash_ap_bc_packets - Value of p_sys_ref5 '||p_sys_ref5 );
log_message('log_message: ' || 'In insert_cash_ap_bc_packets - Value of p_acct_raw_cost '||p_acct_raw_cost);
log_message('log_message: ' || 'In insert_cash_ap_bc_packets - Value of p_fc_document_type '||p_fc_document_type );
log_message('log_message: ' || 'In insert_cash_ap_bc_packets - Value of p_txn_source '||p_txn_source);
log_message('log_message: ' || 'In insert_cash_ap_bc_packets - Value of p_adj_exp_item_id '||p_adj_exp_item_id);
log_message('log_message: ' || 'In insert_cash_ap_bc_packets - Value of p_txn_interface_id '||p_txn_interface_id);
log_message('log_message: ' || 'In insert_cash_ap_bc_packets - Value of l_comm_fc_req '||l_comm_fc_req);
log_message('log_message: ' || 'In insert_cash_ap_bc_packets - Value of l_adj_act_fc_req '||l_adj_act_fc_req );
log_message('log_message: ' || 'In insert_cash_ap_bc_packets - Value of l_act_fc_req '||l_act_fc_req );
log_message('log_message: ' || 'In insert_cash_ap_bc_packets - fetched data from interface table c_pa_txn_interface_data ');
log_message('log_message: ' || 'In insert_cash_ap_bc_packets - calling insert_cash_ap_bc_pkt_auto');
insert_cash_ap_bc_pkt_auto
(p_packet_id => p_packet_id,
p_sys_ref2 => p_sys_ref2,
p_sys_ref5 => p_sys_ref5,
p_acct_raw_cost => p_acct_raw_cost,
p_fc_document_type => p_fc_document_type,
p_txn_source => p_txn_source,
p_adj_exp_item_id => p_adj_exp_item_id ,
p_txn_interface_id => p_txn_interface_id,
p_cash_pay_to_relieve => p_cash_pay_to_relieve,
p_comm_fc_req => l_comm_fc_req,
p_act_fc_req => l_act_fc_req,
p_adj_act_fc_req => l_adj_act_fc_req );
log_message('log_message: ' || 'After Calling insert_cash_ap_bc_pkt_auto api');
log_message('log_message: ' || 'In insert_cash_ap_bc_packets - End');
log_message('log_message:I n insert_cash_ap_bc_packets exception' || sqlerrm||' Returning from insert_cash_ap_bc_packets');
END insert_cash_ap_bc_packets;
PROCEDURE insert_cash_ap_bc_pkt_auto
(p_packet_id IN NUMBER,
p_sys_ref2 IN NUMBER,
p_sys_ref5 IN NUMBER,
p_acct_raw_cost IN NUMBER,
p_fc_document_type IN VARCHAR2,
p_txn_source IN VARCHAR2,
p_adj_exp_item_id IN NUMBER,
p_txn_interface_id IN NUMBER,
p_cash_pay_to_relieve IN NUMBER DEFAULT 0,
p_comm_fc_req IN VARCHAR2,
p_act_fc_req IN VARCHAR2,
p_adj_act_fc_req IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT Pa_Bc_Packets_s.NextVal
FROM dual;
SELECT po_distribution_id
FROM ap_invoice_distributions
WHERE invoice_id = p_sys_ref2
AND invoice_distribution_id = p_sys_ref5
union /*Self Assessed Tax Changes*/
SELECT po_distribution_id
FROM ap_invoice_distributions
WHERE invoice_id = p_sys_ref2
AND invoice_distribution_id = p_sys_ref5 ;
SELECT po_line_id
FROM po_distributions_all
WHERE po_distribution_id = p_po_dist_id;
SELECT a.vendor_id
FROM ap_invoices_all A, ap_invoice_distributions_all aid
WHERE A.invoice_id = aid.invoice_id
AND A.invoice_id = p_sys_ref2
AND aid.invoice_distribution_id = p_sys_ref5
UNION /* Added for bug 13859614 */
SELECT a.vendor_id
FROM ap_invoices_all A, ap_self_assessed_tax_dist_all aid
WHERE A.invoice_id = aid.invoice_id
AND A.invoice_id = p_sys_ref2
AND aid.invoice_distribution_id = p_sys_ref5;
Select bcc.Set_Of_Books_Id
,bcc.Period_Year
,bcc.Project_Id
,bcc.Task_Id
,bcc.Expenditure_Type
,bcc.Expenditure_Item_Date
,bcc.Expenditure_Organization_Id
,bcc.Budget_Version_Id
,bcc.budget_line_id -- R12 Funds management uptake
,bcc.Document_Type
,bcc.Document_Header_Id
,bcc.Document_Distribution_Id
,bcc.Entered_Cr
,bcc.Entered_Dr
,bcc.Accounted_Cr
,bcc.Accounted_Dr
,bcc.Actual_Flag
,bcc.Txn_Ccid
,bcc.Je_Category_Name
,bcc.Je_Source_Name
,bcc.Org_Id
,bcc.Pa_Date
,bcc.bc_packet_id
,bcc.packet_id
From Pa_Bc_Commitments_all bcc
Where bcc.Document_Header_Id = p_sys_ref2
and bcc.Document_Distribution_Id = p_sys_ref5
and bcc.Parent_Bc_Packet_Id is NULL
--PA-J Receipt accrual changes
and bcc.document_type = 'AP'
and bcc.exp_item_id IS NULL -- Bug 5014138 : to pick just the encumbrance record
and bcc.actual_flag = 'E' -- Bug 5014138 : to pick just the encumbrance record
-- R12 Ap lines Uptake : For cash based accounting which can have multiple packets associated with a distribution.
and rownum = 1 ;
Select bcc.Set_Of_Books_Id
,bcc.Period_Year
,bcc.Project_Id
,bcc.Task_Id
,bcc.Expenditure_Type
,bcc.Expenditure_Item_Date
,bcc.Expenditure_Organization_Id
,bcc.Budget_Version_Id
,bcc.budget_line_id -- R12 Funds management uptake
,bcc.Document_Type
,bcc.Document_Header_Id
,bcc.Document_Distribution_Id
,bcc.Entered_Cr
,bcc.Entered_Dr
,bcc.Accounted_Cr
,bcc.Accounted_Dr
,bcc.Actual_Flag
,bcc.Txn_Ccid
,bcc.Je_Category_Name
,bcc.Je_Source_Name
,bcc.Org_Id
,bcc.Pa_Date
,bcc.bc_packet_id
,bcc.packet_id
From Pa_Bc_Packets bcc
where bcc.Document_Header_Id = p_sys_ref2
and bcc.Document_Distribution_Id = p_sys_ref5
and bcc.Status_Code in ('A','C')
and bcc.Parent_Bc_Packet_Id is NULL
and bcc.document_type = 'AP'
and bcc.exp_item_id IS NULL -- Bug 5014138 : to pick just the encumbrance record
and bcc.actual_flag = 'E' -- Bug 5014138 : to pick just the encumbrance record
-- R12 Ap lines Uptake : Added for cash based accounting which can have multiple packets associated witha distribution.
and rownum = 1 ;
log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Start');
log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - After fetching CWK related data l_po_dist_id'||l_po_dist_id);
log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - After fetching CWK related data l_po_line_id'||l_po_line_id);
log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Fetchign data from bc commitments');
log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Fetchign data from bc packets');
log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Generating new Bc packet Id for reversing RAW act and commitment line');
log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Bc Packet Id = ' || l_Raw_Bc_Packet_Id ||'Packet Id = ' || p_packet_id);
log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Before inserting AP reversing raw and burden line from bc commitments');
Insert Into Pa_Bc_Packets
(Packet_Id
,Bc_Packet_Id
,Parent_Bc_Packet_Id
,Set_Of_Books_Id
,Gl_Date
,Period_Name
,Period_Year
,Pa_Date
,Project_Id
,Task_Id
,Expenditure_Type
,Expenditure_Item_Date
,Expenditure_Organization_Id
,Budget_Version_Id
,budget_line_id -- R12 funds management uptake
,Document_Type
,Document_Header_Id
,Document_Distribution_Id
,Entered_Dr
,Entered_Cr
,Accounted_Dr
,Accounted_Cr
,Request_Id
,Program_Id
,Program_Application_Id
,Program_Update_Date
,Actual_Flag
,Last_Update_Date
,Last_Updated_By
,Created_By
,Creation_Date
,Last_Update_Login
,Txn_Ccid
,Burden_Cost_Flag
,Status_Code
,Je_Category_Name
,Je_Source_Name
,Org_id
,Exp_Item_Id
,Document_Line_Id
,reference1
,reference2
,reference3
,txn_interface_id --REL12
,encumbrance_type_id
,document_header_id_2
,document_distribution_type
)
Select p_packet_id
,decode(bcc.Parent_Bc_Packet_Id,NULL,l_Raw_Bc_Packet_id,Pa_Bc_Packets_s.NextVal)
,decode(bcc.Parent_Bc_Packet_Id,NULL,NULL,l_Raw_Bc_Packet_id)
,bcc.Set_Of_Books_Id
,G_FC_Gl_Date
,G_FC_Period_Name
,bcc.Period_Year
,bcc.Pa_Date -- pa_date on raw is used for burden line
,bcc.Project_Id
,bcc.Task_Id
,bcc.Expenditure_Type
,bcc.Expenditure_Item_Date
,bcc.Expenditure_Organization_Id
,bcc.Budget_Version_Id
,bcc.budget_line_id -- R12 funds management uptake
,bcc.Document_Type
,bcc.Document_Header_Id
,bcc.Document_Distribution_Id
,DECODE(bcc.Parent_Bc_Packet_Id,NULL,p_cash_pay_to_relieve
,pa_currency.round_trans_currency_amt1((p_cash_pay_to_relieve * bcc.compiled_multiplier),G_accounting_currency_code))
,0
,DECODE(bcc.Parent_Bc_Packet_Id,NULL,p_cash_pay_to_relieve
,pa_currency.round_trans_currency_amt1((p_cash_pay_to_relieve * bcc.compiled_multiplier),G_accounting_currency_code))
,0
,g_Request_Id
,G_Program_Id
,G_PROG_APPL_ID
,sysdate
,bcc.Actual_Flag
,sysdate
,G_CONC_LOGIN_ID
,G_CONC_LOGIN_ID
,sysdate
,G_CONC_LOGIN_ID
,bcc.Txn_Ccid
,'R'
,'P'
,bcc.Je_Category_Name
,bcc.Je_Source_Name
,bcc.Org_Id
,X_ei_id
,l_po_line_id
,'EXP'
,X_ei_id
,1
,p_txn_interface_id --REL12
,bcc.encumbrance_type_id
,bcc.document_header_id_2
,bcc.document_distribution_type
From Pa_Bc_Commitments_all bcc
Where bcc.Document_Header_Id = p_sys_ref2 -- Added for using index
and bcc.Document_Distribution_Id = p_sys_ref5
and bcc.packet_id = g_packet_id_tbl(i)
and bcc.document_type = 'AP' ;
log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Inserted '||SQL%ROWCOUNT||' AP reversing raw and burden line(s) into bc packets');
log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Before inserting the AP raw and burden lines from Bc Packets');
Insert Into Pa_Bc_Packets
(Packet_Id
,Bc_Packet_Id
,Parent_Bc_Packet_Id
,Set_Of_Books_Id
,Gl_Date
,Period_Name
,Period_Year
,Pa_Date
,Project_Id
,Task_Id
,Expenditure_Type
,Expenditure_Item_Date
,Expenditure_Organization_Id
,Budget_Version_Id
,budget_line_id -- R12 funds management uptake
,Document_Type
,Document_Header_Id
,Document_Distribution_Id
,Entered_Dr
,Entered_Cr
,Accounted_Dr
,Accounted_Cr
,Request_Id
,Program_Id
,Program_Application_Id
,Program_Update_Date
,Actual_Flag
,Last_Update_Date
,Last_Updated_By
,Created_By
,Creation_Date
,Last_Update_Login
,Txn_Ccid
,Burden_Cost_Flag
,Status_Code
,Je_Category_Name
,Je_Source_Name
,Org_id
,Exp_Item_Id
,Document_Line_Id
,reference1
,reference2
,reference3
,txn_interface_id --REL12
,encumbrance_type_id
,document_header_id_2
,document_distribution_type
)
Select p_packet_id
,decode(bcc.Parent_Bc_Packet_Id,NULL,l_Raw_Bc_Packet_id,Pa_Bc_Packets_s.NextVal)
,decode(bcc.Parent_Bc_Packet_Id,NULL,NULL,l_Raw_Bc_Packet_id)
,bcc.Set_Of_Books_Id
,G_FC_Gl_Date
,G_FC_Period_Name
,bcc.Period_Year
,bcc.pa_date
,bcc.Project_Id
,bcc.Task_Id
,bcc.Expenditure_Type
,bcc.Expenditure_Item_Date
,bcc.Expenditure_Organization_Id
,bcc.Budget_Version_Id
,bcc.budget_line_id -- R12 funds management uptake
,bcc.Document_Type
,bcc.Document_Header_Id
,bcc.Document_Distribution_Id
,DECODE(bcc.Parent_Bc_Packet_Id,NULL,p_cash_pay_to_relieve
,pa_currency.round_trans_currency_amt1((p_cash_pay_to_relieve * bcc.compiled_multiplier),G_accounting_currency_code))
,0
,DECODE(bcc.Parent_Bc_Packet_Id,NULL,p_cash_pay_to_relieve
,pa_currency.round_trans_currency_amt1((p_cash_pay_to_relieve * bcc.compiled_multiplier),G_accounting_currency_code))
,0
,g_Request_Id
,G_Program_Id
,G_PROG_APPL_ID
,sysdate
,bcc.Actual_Flag
,sysdate
,G_CONC_LOGIN_ID
,G_CONC_LOGIN_ID
,sysdate
,G_CONC_LOGIN_ID
,bcc.Txn_Ccid
,'R'
,'P'
,bcc.Je_Category_Name
,bcc.Je_Source_Name
,bcc.Org_id
,X_ei_id
,l_po_line_id
,'EXP'
,X_ei_id
,1
,p_txn_interface_id --REL12
,bcc.encumbrance_type_id
,bcc.document_header_id_2
,bcc.document_distribution_type
From Pa_Bc_Packets bcc
Where bcc.packet_id = g_packet_id_tbl(i)
and bcc.document_type = 'AP'
-- Bug : 4962731
-- : R12.PJ:XB1:QA:BC:INCORRECT AMOUNTS INTERFACED TO PROJECTS IN CASH BASED ACC
and bcc.Document_Header_Id = p_sys_ref2 -- Added for using index
and bcc.Document_Distribution_Id = p_sys_ref5
and bcc.Status_Code in ('A','C');
log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Before inserting the AP raw and burden lines from Bc Packets');
log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Before inserting ACTUAL raw line from bc packets');
Insert Into Pa_Bc_Packets
(Packet_Id
,Bc_Packet_Id
,Parent_Bc_Packet_Id
,Set_Of_Books_Id
,gl_date
,Period_Name
,Period_Year
,Pa_Date
,Project_Id
,Task_Id
,Expenditure_Type
,Expenditure_Item_Date
,Expenditure_Organization_Id
,Budget_Version_Id
,budget_line_id -- R12 funds management uptake
,Document_Type
,Document_Header_Id
,Document_Distribution_Id
,Entered_Dr
,Entered_Cr
,Accounted_Dr
,Accounted_Cr
,Request_Id
,Program_Id
,Program_Application_Id
,Program_Update_Date
,Actual_Flag
,Last_Update_Date
,Last_Updated_By
,Created_By
,Creation_Date
,Last_Update_Login
,Txn_Ccid
,Burden_Cost_Flag
,Status_Code
,Je_Category_Name
,Je_Source_Name
,Org_id
,Document_Line_Id
,reference1
,reference2
,reference3
,txn_interface_id --REL12
,vendor_id
)
Select p_Packet_Id
,Pa_Bc_Packets_s.NextVal
,-1
,g_sob_Id_tbl(i)
,G_FC_Gl_Date
,G_FC_Period_Name
,g_Period_Year_tbl(i)
,g_Pa_Date_tbl(i)
,g_project_id_tbl(i)
,g_task_id_tbl(i)
,g_exp_type_tbl(i)
,g_ei_date_tbl(i)
,g_exp_org_id_tbl(i)
,g_bud_ver_id_tbl(i)
,g_budget_line_id_tbl(i) -- R12 funds management uptake
,'EXP'
,X_ei_id
,1
,p_acct_raw_cost
,0
,p_acct_raw_cost
,0
,g_Request_Id
,g_program_id
,g_prog_appl_id
,sysdate
,'A'
,sysdate
,g_conc_login_id
,g_conc_login_id
,sysdate
,g_conc_login_id
,g_Txn_Ccid_tbl(i)
,'N'
,'P'
,'Expenditures'
,'Project Accounting'
,g_org_id_tbl(i)
,l_po_line_id
,'EXP'
,x_Ei_Id
,1
,p_txn_interface_id --REL12
,l_vendor_id
From Pa_Budget_Versions bv,
Pa_Budget_Types bt,
pa_budgetary_control_options pbct
Where bt.budget_type_Code = bv.budget_type_Code
and bt.budget_amount_code = 'C'
and bv.project_id = g_project_id_tbl(i)
and bv.current_flag = 'Y'
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
);
log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Inserted '||SQL%ROWCOUNT||' ACTUAL raw line(s) into bc packets');
log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Before inserting adjusted ACTUAL raw line from transaction import table');
Insert Into Pa_Bc_Packets
(Packet_Id
,Bc_Packet_Id
,Parent_Bc_Packet_Id
,Set_Of_Books_Id
,Gl_Date
,Period_Name
,Period_Year
,Pa_Date
,Project_Id
,Task_Id
,Expenditure_Type
,Expenditure_Item_Date
,Expenditure_Organization_Id
,Budget_Version_Id
,Document_Type
,Document_Header_Id
,Document_Distribution_Id
,Entered_Dr
,Entered_Cr
,Accounted_Dr
,Accounted_Cr
,Request_Id
,Program_Id
,Program_Application_Id
,Program_Update_Date
,Actual_Flag
,Last_Update_Date
,Last_Updated_By
,Created_By
,Creation_Date
,Last_Update_Login
,Txn_Ccid
,Burden_Cost_Flag
,Status_Code
,Je_Category_Name
,Je_Source_Name
,Org_id
,Document_Line_Id
,reference1
,reference2
,reference3
,txn_interface_id --REL12
,vendor_id
)
Select p_Packet_Id
,Pa_Bc_Packets_s.NextVal
,-1
,g_sobid
,G_FC_Gl_Date
,G_FC_Period_Name
,G_FC_Period_Year
,G_PaDate
,g_xface_project_id_tbl(i)
,g_xface_task_id_tbl(i)
,g_xface_exp_type_tbl(i)
,g_xface_ei_date_tbl(i)
,g_xface_exp_org_id_tbl(i)
,g_xface_bud_ver_id_tbl(i)
,'EXP'
,X_ei_id
,1
-- R12 AP lines uptake : Prepayment changes
-- For Prepayment application/pre-payment matched to PO records consider p_acct_raw_cost as amount
,g_xface_Entered_Dr_tbl(i)
,g_xface_acct_Cr_tbl(i)
,g_xface_acct_Dr_tbl(i)
,g_xface_acct_Cr_tbl(i)
,g_Request_Id
,g_program_id
,g_prog_appl_id
,sysdate
,'A' --Actual_Flag
,sysdate
,g_conc_login_id
,g_conc_login_id
,sysdate
,g_conc_login_id
,g_xface_Txn_Ccid_tbl(i)
,'N'
,'P'
,'Expenditures'
,'Project Accounting'
,g_xface_org_id_tbl(i)
,l_po_line_id
,'EXP'
,X_Ei_Id
,1
,g_xface_Txn_interface_tbl(i) --REL12
,g_xface_vendor_id_tbl(i)
From dual;
log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Before inserting adjusted ACTUAL raw line from transaction import table');
log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - End');
log_message('log_message: In insert_cash_ap_bc_pkt_auto exception' || sqlerrm||' Returning from insert_cash_ap_bc_pkt_auto');
END insert_cash_ap_bc_pkt_auto;
PROCEDURE insert_po_bc_packets
(p_packet_id IN NUMBER,
p_sys_ref2 IN NUMBER,
p_sys_ref4 IN NUMBER,
p_sys_ref3 IN NUMBER,
p_acct_raw_cost IN NUMBER,
p_cmt_raw_cost IN NUMBER,
p_fc_document_type IN VARCHAR2,
p_txn_source IN VARCHAR2,
p_adj_exp_item_id IN NUMBER,
p_txn_interface_id IN NUMBER) IS
-- Cursor to fetch data from pa_transaction_interface_table.This data is later used
-- in autonomous fundscheck insert_ap_bc_pkts_autonomous procedure.
CURSOR c_pa_txn_interface_data IS
Select txn.Project_Id
,txn.Task_Id
,txn.Expenditure_Type
,txn.Expenditure_Item_Date
,nvl(txn.OVERRIDE_TO_ORGANIZATION_ID,txn.Org_Id)
,bv.Budget_Version_Id
-- R12 AP lines uptake : Prepayment changes
-- For Prepayment application/pre-payment matched to PO records consider p_acct_raw_cost as amount
,txn.acct_raw_cost
,0
,txn.acct_raw_cost
,0
,txn.cr_code_combination_id
,txn.Org_id
,txn.txn_interface_id --REL12
,txn.vendor_id
From Pa_Budget_Versions bv,
Pa_Budget_Types bt,
pa_budgetary_control_options pbct,
pa_transaction_interface_all txn
Where bv.budget_type_code = bt.budget_type_code
and bt.budget_amount_code = 'C'
and bv.current_flag = 'Y'
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 = txn.project_id
and txn.TRANSACTION_SOURCE = p_txn_source -- Condition for using index
-- new index usage needs to be verified for below columns
AND txn.cdl_system_reference2 = p_sys_ref2
AND txn.cdl_system_reference3 = p_sys_ref3 --Bug 5550268
--Bug 5550268 : Will be NULL when fired from ap_po_fundscheck_check
AND (txn.cdl_system_reference4 = p_sys_ref4 OR p_sys_ref4 IS NULL)
AND txn.fc_document_type in ('ACT','ALL')
AND txn.adjusted_expenditure_item_id IS NOT NULL;
log_message('log_message: ' || 'In insert_po_bc_packets - Start');
log_message('log_message: ' || 'In insert_po_bc_packets - Value of p_packet_id '||p_packet_id);
log_message('log_message: ' || 'In insert_po_bc_packets - Value of p_sys_ref2 '||p_sys_ref2 );
log_message('log_message: ' || 'In insert_po_bc_packets - Value of p_sys_ref4 '||p_sys_ref4 );
log_message('log_message: ' || 'In insert_po_bc_packets - Value of p_sys_ref3 '||p_sys_ref3 );
log_message('log_message: ' || 'In insert_po_bc_packets - Value of p_acct_raw_cost '||p_acct_raw_cost);
log_message('log_message: ' || 'In insert_po_bc_packets - Value of p_cmt_raw_cost '||p_cmt_raw_cost); -- Bug 5731450
log_message('log_message: ' || 'In insert_po_bc_packets - Value of p_fc_document_type '||p_fc_document_type );
log_message('log_message: ' || 'In insert_po_bc_packets - Value of p_txn_source '||p_txn_source);
log_message('log_message: ' || 'In insert_po_bc_packets - Value of p_adj_exp_item_id '||p_adj_exp_item_id);
log_message('log_message: ' || 'In insert_po_bc_packets - Value of p_txn_interface_id '||p_txn_interface_id);
log_message('log_message: ' || 'In insert_po_bc_packets - Value of l_comm_fc_req '||l_comm_fc_req);
log_message('log_message: ' || 'In insert_po_bc_packets - Value of l_adj_act_fc_req '||l_adj_act_fc_req );
log_message('log_message: ' || 'In insert_po_bc_packets - Value of l_act_fc_req '||l_act_fc_req );
log_message('log_message: ' || 'In insert_po_bc_packets - fetched data from interface table c_pa_txn_interface_data ');
log_message('log_message: ' || 'In insert_po_bc_packets - calling insert_po_bc_packets_auto');
insert_po_bc_packets_auto
(p_packet_id => p_packet_id,
p_sys_ref2 => p_sys_ref2,
p_sys_ref4 => p_sys_ref4,
p_sys_ref3 => p_sys_ref3,
p_acct_raw_cost => p_acct_raw_cost,
p_cmt_raw_cost => p_cmt_raw_cost,
p_fc_document_type => p_fc_document_type,
p_txn_source => p_txn_source,
p_adj_exp_item_id => p_adj_exp_item_id ,
p_txn_interface_id => p_txn_interface_id,
p_comm_fc_req => l_comm_fc_req,
p_act_fc_req => l_act_fc_req,
p_adj_act_fc_req => l_adj_act_fc_req );
log_message('log_message: ' || 'In insert_po_bc_packets - End');
log_message('log_message:I n insert_po_bc_packets exception' || sqlerrm||' Returning from insert_po_bc_packets');
END insert_po_bc_packets;
PROCEDURE insert_po_bc_packets_auto
(p_packet_id IN NUMBER,
p_sys_ref2 IN NUMBER,
p_sys_ref4 IN NUMBER,
p_sys_ref3 IN NUMBER,
p_acct_raw_cost IN NUMBER,
p_cmt_raw_cost IN NUMBER,
p_fc_document_type IN VARCHAR2,
p_txn_source IN VARCHAR2,
p_adj_exp_item_id IN NUMBER,
p_txn_interface_id IN NUMBER,
p_comm_fc_req IN VARCHAR2,
p_act_fc_req IN VARCHAR2,
p_adj_act_fc_req IN VARCHAR2) IS
CURSOR c_gen_raw_bc_pkt IS
SELECT Pa_Bc_Packets_s.NextVal
FROM dual;
SELECT po_line_id
FROM po_distributions_all
WHERE po_distribution_id = p_po_dist_id;
select distinct expenditure_item_date
from pa_transaction_interface_all
where txn_interface_id = p_txn_interface_id;
Select bcc.Set_Of_Books_Id
,bcc.Period_Year
,bcc.Project_Id
,bcc.Task_Id
,bcc.Expenditure_Type
,bcc.Expenditure_Item_Date
,bcc.Expenditure_Organization_Id
,bcc.Budget_Version_Id
,bcc.budget_line_id -- R12 funds management uptake
,bcc.Document_Type
,bcc.Document_Header_Id
,bcc.Document_Distribution_Id
,bcc.Entered_Cr
,bcc.Entered_Dr
,bcc.Accounted_Cr
,bcc.Accounted_Dr
,bcc.Actual_Flag
,bcc.Txn_Ccid
,bcc.Je_Category_Name
,bcc.Je_Source_Name
,bcc.Org_Id
,bcc.Pa_Date
,bcc.bc_packet_id
,bcc.packet_id
,bcc.bc_commitment_id
,bcc.vendor_id
From Pa_Bc_Commitments_all bcc,
po_distributions pod
-- R12 Funds management Uptake : For fixing performance issues removing the code for CC which is obsolete for R12
-- Modifying code to use Index on document_header_id and document_distribution_id of bc commitments
where bcc.document_header_id = pod.po_header_id
and bcc.document_distribution_id = pod.po_distribution_id
and bcc.project_id = pod.project_id -- Bug# 4479105
and bcc.task_id = pod.task_id --Bug# 4479105
and bcc.expenditure_type = pod.expenditure_type -- Bug7620577
and bcc.document_type = 'PO'
and pod.po_header_id = p_sys_ref2
and pod.po_distribution_id = p_sys_ref3
and bcc.exp_item_id IS NULL -- Bug 5014138 : to pick just the encumbrance record
and bcc.actual_flag = 'E' -- Bug 5014138 : to pick just the encumbrance record
and bcc.Parent_Bc_Packet_Id is NULL
Order By bcc.packet_id;
Select bcc.Set_Of_Books_Id
,bcc.Period_Year
,bcc.Project_Id
,bcc.Task_Id
,bcc.Expenditure_Type
,bcc.Expenditure_Item_Date
,bcc.Expenditure_Organization_Id
,bcc.Budget_Version_Id
,bcc.budget_line_id -- R12 funds management uptake
,bcc.Document_Type
,bcc.Document_Header_Id
,bcc.Document_Distribution_Id
,bcc.Entered_Cr
,bcc.Entered_Dr
,bcc.Accounted_Cr
,bcc.Accounted_Dr
,bcc.Actual_Flag
,bcc.Txn_Ccid
,bcc.Je_Category_Name
,bcc.Je_Source_Name
,bcc.Org_Id
,bcc.Pa_Date
,bcc.bc_packet_id
,bcc.packet_id
,bcc.vendor_id
-- R12 Funds management Uptake : For fixing performance issues removing the code for CC which is obsolete for R12
-- Modifying code to use Index on document_header_id and document_distribution_id of bc commitments
From Pa_Bc_packets bcc,
po_distributions pod
where bcc.document_header_id = pod.po_header_id
and bcc.document_distribution_id = pod.po_distribution_id
and bcc.project_id = pod.project_id -- Bug# 4479105
and bcc.task_id = pod.task_id --Bug# 4479105
and bcc.expenditure_type = pod.expenditure_type -- Bug7620577
and bcc.document_type = 'PO'
and pod.po_header_id = p_sys_ref2
and pod.po_distribution_id = p_sys_ref3
and bcc.exp_item_id IS NULL -- Bug 5014138 : to pick just the encumbrance record
and bcc.actual_flag = 'E' -- Bug 5014138 : to pick just the encumbrance record
and bcc.Status_Code in('A','C')
and bcc.Parent_Bc_Packet_Id is NULL
Order By bcc.packet_id;
log_message('log_message: ' || 'In insert_po_bc_packets_auto - Start');
log_message('log_message: ' || 'In insert_po_bc_packets_auto - After fetching CWK related data l_po_line_id'||l_po_line_id);
log_message('log_message: ' || 'In insert_po_bc_packets_auto - Fetchign data from bc commitments');
log_message('log_message: ' || 'In insert_po_bc_packets_auto - Fetchign data from bc packets');
log_message('log_message: ' || 'In insert_po_bc_packets_auto - Generating new Bc packet Id for reversing RAW act and commitment line');
log_message('log_message: ' || 'In insert_po_bc_packets_auto - Bc Packet Id = ' || l_Raw_Bc_Packet_Id ||'Packet Id = ' || p_packet_id);
log_message('log_message: ' || 'In insert_po_bc_packets_auto - Before inserting PO reversing raw and burden line from bc commitments');
Insert Into Pa_Bc_Packets
(Packet_Id
,Bc_Packet_Id
,Parent_Bc_Packet_Id
,Set_Of_Books_Id
,Gl_Date
,Period_Name
,Period_Year
,Pa_Date
,Project_Id
,Task_Id
,Expenditure_Type
,Expenditure_Item_Date
,Expenditure_Organization_Id
,Budget_Version_Id
,Budget_line_id -- R12 funds management uptake
,Document_Type
,Document_Header_Id
,Document_Distribution_Id
,Entered_Dr
,Entered_Cr
,Accounted_Dr
,Accounted_Cr
,Request_Id
,Program_Id
,Program_Application_Id
,Program_Update_Date
,Actual_Flag
,Last_Update_Date
,Last_Updated_By
,Created_By
,Creation_Date
,Last_Update_Login
,Txn_Ccid
,Burden_Cost_Flag
,Status_Code
,Je_Category_Name
,Je_Source_Name
,Org_id
,Exp_Item_Id
,Document_Line_Id
,reference1
,reference2
,reference3
,txn_interface_id --REL12
,encumbrance_type_id
,document_header_id_2
,document_distribution_type
)
Select p_packet_id
,decode(bcc.Parent_Bc_Packet_Id,NULL,l_Raw_Bc_Packet_id,Pa_Bc_Packets_s.NextVal)
,decode(bcc.Parent_Bc_Packet_Id,NULL,NULL,l_Raw_Bc_Packet_id)
,bcc.Set_Of_Books_Id
,G_FC_Gl_Date
,G_FC_Period_Name
,bcc.Period_Year
-- ,bcc.Pa_Date -- pa_date on raw is used for burden line /* changed to g_padate for 16451280 */
, G_PADATE
,bcc.Project_Id
,bcc.Task_Id
,bcc.Expenditure_Type
,l_Expenditure_Item_Date
,bcc.Expenditure_Organization_Id
,bcc.Budget_Version_Id
,bcc.Budget_line_id -- R12 funds management uptake
,bcc.Document_Type
,bcc.Document_Header_Id
,bcc.Document_Distribution_Id
,DECODE(bcc.parent_bc_packet_id,NULL,
(-1 * p_cmt_raw_cost), --rshaik
(0 - (pa_currency.round_trans_currency_amt1((((nvl(bcc.Entered_dr,0)-nvl(bcc.Entered_Cr,0))/
decode((nvl(g_Entered_Dr_tbl(i),0)-nvl(g_Entered_Cr_tbl(i),0)),
0,1,(nvl(g_Entered_Dr_tbl(i),0)-nvl(g_Entered_Cr_tbl(i),0))))
*p_cmt_raw_cost), G_accounting_currency_code))))
,0
,DECODE(bcc.parent_bc_packet_id,NULL,
(-1 * p_cmt_raw_cost)
,(0 - (pa_currency.round_trans_currency_amt1((((nvl(bcc.Accounted_dr,0)-nvl(bcc.Accounted_cr,0))/
decode((nvl(g_acct_Dr_tbl(i),0)-nvl(g_acct_Cr_tbl(i),0))
,0,1,(nvl(g_acct_Dr_tbl(i),0)-nvl(g_acct_Cr_tbl(i),0))))
*p_cmt_raw_cost), G_accounting_currency_code))))
,0
,g_Request_Id
,G_Program_Id
,G_PROG_APPL_ID
,sysdate
,bcc.Actual_Flag
,sysdate
,G_CONC_LOGIN_ID
,G_CONC_LOGIN_ID
,sysdate
,G_CONC_LOGIN_ID
,bcc.Txn_Ccid
,'R'
,'P'
,decode(p_txn_source, 'AP NRTAX' , g_Je_Catg_Name_tbl(i)||' Tax', g_Je_Catg_Name_tbl(i))
,bcc.Je_Source_Name
,bcc.Org_Id
,X_ei_id
,l_po_line_id
,'EXP'
,X_ei_id
,1
,p_txn_interface_id --REL12
,bcc.encumbrance_type_id
,bcc.document_header_id_2
,bcc.document_distribution_type
From Pa_Bc_Commitments_all bcc
Where bcc.bc_commitment_id in (SELECT bcc1.bc_commitment_id
FROM PA_BC_COMMITMENTS_ALL bcc1
WHERE bcc1.document_header_id = p_sys_ref2
AND bcc1.document_distribution_id = p_sys_ref3
AND bcc1.parent_bc_packet_id = g_bc_packet_id_tbl(i)
UNIOn ALL
SELECT g_bc_comt_id_tbl(i)
FROM DUAL);
log_message('log_message: ' || 'In insert_po_bc_packets_auto - Inserted '||SQL%ROWCOUNT||' PO reversing raw and burden line(s) into bc packets');
log_message('log_message: ' || 'In insert_po_bc_packets_auto - Before inserting the PO raw and burden lines from Bc Packets');
Insert Into Pa_Bc_Packets
(Packet_Id
,Bc_Packet_Id
,Parent_Bc_Packet_Id
,Set_Of_Books_Id
,Gl_Date
,Period_Name
,Period_Year
,Pa_Date
,Project_Id
,Task_Id
,Expenditure_Type
,Expenditure_Item_Date
,Expenditure_Organization_Id
,Budget_Version_Id
,Budget_line_id -- R12 funds management uptake
,Document_Type
,Document_Header_Id
,Document_Distribution_Id
,Entered_Dr
,Entered_Cr
,Accounted_Dr
,Accounted_Cr
,Request_Id
,Program_Id
,Program_Application_Id
,Program_Update_Date
,Actual_Flag
,Last_Update_Date
,Last_Updated_By
,Created_By
,Creation_Date
,Last_Update_Login
,Txn_Ccid
,Burden_Cost_Flag
,Status_Code
,Je_Category_Name
,Je_Source_Name
,Org_id
,Exp_Item_Id
,Document_Line_Id
,reference1
,reference2
,reference3
,txn_interface_id --REL12
,encumbrance_type_id
,document_header_id_2
,document_distribution_type
)
Select p_packet_id
,decode(bcc.Parent_Bc_Packet_Id,NULL,l_Raw_Bc_Packet_id,Pa_Bc_Packets_s.NextVal)
,decode(bcc.Parent_Bc_Packet_Id,NULL,NULL,l_Raw_Bc_Packet_id)
,bcc.Set_Of_Books_Id
,G_FC_Gl_Date
,G_FC_Period_Name
,bcc.Period_Year
--,bcc.pa_date /* changed to g_padate for 16451280 */
,G_PADATE
,bcc.Project_Id
,bcc.Task_Id
,bcc.Expenditure_Type
--,bcc.Expenditure_Item_Date /* changed to l_expenditure_item_date for 16451280 */
,l_expenditure_item_date
,bcc.Expenditure_Organization_Id
,bcc.Budget_Version_Id
,bcc.Budget_line_id -- R12 funds management uptake
,bcc.Document_Type
,bcc.Document_Header_Id
,bcc.Document_Distribution_Id
,DECODE(bcc.parent_bc_packet_id,NULL,
-1 * p_cmt_raw_cost,
(0 - (pa_currency.round_trans_currency_amt1((((nvl(bcc.Entered_dr,0)-nvl(bcc.Entered_Cr,0))/
decode((nvl(g_Entered_Dr_tbl(i),0)-nvl(g_Entered_Cr_tbl(i),0)),
0,1,(nvl(g_Entered_Dr_tbl(i),0)-nvl(g_Entered_Cr_tbl(i) ,0))))
*p_cmt_raw_cost), G_accounting_currency_code))))
,0
,DECODE(bcc.parent_bc_packet_id,NULL,
-1 * p_cmt_raw_cost
,(0 - (pa_currency.round_trans_currency_amt1((((nvl(bcc.Accounted_dr,0)-nvl(bcc.Accounted_cr,0))/
decode((nvl(g_acct_Dr_tbl(i),0)-nvl(g_acct_Cr_tbl(i),0))
,0,1,(nvl(g_acct_Dr_tbl(i),0)-nvl(g_acct_Cr_tbl(i),0))))
*p_cmt_raw_cost), G_accounting_currency_code))))
,0
,g_Request_Id
,G_Program_Id
,G_PROG_APPL_ID
,sysdate
,bcc.Actual_Flag
,sysdate
,G_CONC_LOGIN_ID
,G_CONC_LOGIN_ID
,sysdate
,G_CONC_LOGIN_ID
,bcc.Txn_Ccid
,'R'
,'P'
,bcc.Je_Category_Name
,bcc.Je_Source_Name
,bcc.Org_id
,X_ei_id
,l_po_line_id
,'EXP'
,X_ei_id
,1
,p_txn_interface_id --REL12
,bcc.encumbrance_type_id
,bcc.document_header_id_2
,bcc.document_distribution_type
From Pa_Bc_Packets bcc
Where bcc.packet_id = g_packet_id_tbl(i)
and bcc.bc_packet_id in (SELECT bcc1.bc_packet_id
FROM PA_BC_PACKETS bcc1
WHERE bcc1.packet_id = g_packet_id_tbl(i)
and bcc1.parent_bc_packet_id = g_bc_packet_id_tbl(i)
and bcc1.Status_Code in ('A','C')
UNIOn ALL
SELECT g_bc_packet_id_tbl(i)
FROM DUAL);
log_message('log_message: ' || 'In insert_po_bc_packets_auto - Inserted '||SQL%ROWCOUNT||' PO reversing raw and burden line(s) into bc packets');
log_message('log_message: ' || 'In insert_po_bc_packets_auto - Before inserting ACTUAL raw line from bc packets');
Insert Into Pa_Bc_Packets
(Packet_Id
,Bc_Packet_Id
,Parent_Bc_Packet_Id
,Set_Of_Books_Id
,gl_date
,Period_Name
,Period_Year
,Pa_Date
,Project_Id
,Task_Id
,Expenditure_Type
,Expenditure_Item_Date
,Expenditure_Organization_Id
,Budget_Version_Id
,Budget_line_id -- R12 funds management uptake
,Document_Type
,Document_Header_Id
,Document_Distribution_Id
,Entered_Dr
,Entered_Cr
,Accounted_Dr
,Accounted_Cr
,Request_Id
,Program_Id
,Program_Application_Id
,Program_Update_Date
,Actual_Flag
,Last_Update_Date
,Last_Updated_By
,Created_By
,Creation_Date
,Last_Update_Login
,Txn_Ccid
,Burden_Cost_Flag
,Status_Code
,Je_Category_Name
,Je_Source_Name
,Org_id
,Document_Line_Id
,reference1
,reference2
,reference3
,txn_interface_id --REL12
,vendor_id
)
Select p_Packet_Id
,Pa_Bc_Packets_s.NextVal
,-1
,g_sob_Id_tbl(i)
,G_FC_Gl_Date
,G_FC_Period_Name
,g_Period_Year_tbl(i)
-- ,g_Pa_Date_tbl(i) /* changed to g_padate for 16451280 */
,g_padate
,g_project_id_tbl(i)
,g_task_id_tbl(i)
,g_exp_type_tbl(i)
--,g_ei_date_tbl(i) /* changed to l_expenditure_item_date for 16451280 */
,l_expenditure_item_date
,g_exp_org_id_tbl(i)
,g_bud_ver_id_tbl(i)
,g_budget_line_id_tbl(i) -- R12 funds management uptake
,'EXP'
,X_ei_id
,1
,nvl(p_acct_raw_cost,0)
,0
,nvl(p_acct_raw_cost,0)
,0
,g_Request_Id
,g_program_id
,g_prog_appl_id
,sysdate
,'A'
,sysdate
,g_conc_login_id
,g_conc_login_id
,sysdate
,g_conc_login_id
,g_Txn_Ccid_tbl(i)
,'N'
,'P'
,'Expenditures'
,'Project Accounting'
,g_org_id_tbl(i)
,l_po_line_id
,'EXP'
,x_Ei_Id
,1
,p_txn_interface_id --REL12
,g_vendor_id_tbl(i)
From Pa_Budget_Versions bv,
Pa_Budget_Types bt,
pa_budgetary_control_options pbct
Where bt.budget_type_Code = bv.budget_type_Code
and bt.budget_amount_code = 'C'
and bv.project_id = g_project_id_tbl(i)
and bv.current_flag = 'Y'
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
);
log_message('log_message: ' || 'In insert_po_bc_packets_auto - Inserted '||SQL%ROWCOUNT||' ACTUAL raw line(s) into bc packets');
log_message('log_message: ' || 'In insert_po_bc_packets_auto - Before inserting adjusted ACTUAL raw line from transaction import table');
Insert Into Pa_Bc_Packets
(Packet_Id
,Bc_Packet_Id
,Parent_Bc_Packet_Id
,Set_Of_Books_Id
,Gl_Date
,Period_Name
,Period_Year
,Pa_Date
,Project_Id
,Task_Id
,Expenditure_Type
,Expenditure_Item_Date
,Expenditure_Organization_Id
,Budget_Version_Id
,Document_Type
,Document_Header_Id
,Document_Distribution_Id
,Entered_Dr
,Entered_Cr
,Accounted_Dr
,Accounted_Cr
,Request_Id
,Program_Id
,Program_Application_Id
,Program_Update_Date
,Actual_Flag
,Last_Update_Date
,Last_Updated_By
,Created_By
,Creation_Date
,Last_Update_Login
,Txn_Ccid
,Burden_Cost_Flag
,Status_Code
,Je_Category_Name
,Je_Source_Name
,Org_id
,Document_Line_Id
,reference1
,reference2
,reference3
,txn_interface_id --REL12
,vendor_id
)
Select p_Packet_Id
,Pa_Bc_Packets_s.NextVal
,-1
,g_sobid
,G_FC_Gl_Date
,G_FC_Period_Name
,G_FC_Period_Year
,G_PaDate
,g_xface_project_id_tbl(i)
,g_xface_task_id_tbl(i)
,g_xface_exp_type_tbl(i)
-- ,g_xface_ei_date_tbl(i) /* changed to l_expenditure_item_date for 16451280 */
, l_expenditure_item_date
,g_xface_exp_org_id_tbl(i)
,g_xface_bud_ver_id_tbl(i)
,'EXP'
,X_ei_id
,1
,g_xface_Entered_Dr_tbl(i)
,g_xface_acct_Cr_tbl(i)
,g_xface_acct_Dr_tbl(i)
,g_xface_acct_Cr_tbl(i)
,g_Request_Id
,g_program_id
,g_prog_appl_id
,sysdate
,'A' --Actual_Flag
,sysdate
,g_conc_login_id
,g_conc_login_id
,sysdate
,g_conc_login_id
,g_xface_Txn_Ccid_tbl(i)
,'N'
,'P'
,'Expenditures'
,'Project Accounting'
,g_xface_org_id_tbl(i)
,l_po_line_id
,'EXP'
,X_Ei_Id
,1
,g_xface_Txn_interface_tbl(i)
,g_xface_vendor_id_tbl(i)
From dual;
log_message('log_message: ' || 'In insert_po_bc_packets_auto - Inserted '||SQL%ROWCOUNT||' ACTUAL raw line(s) into bc packets');
log_message('log_message: ' || 'In insert_po_bc_packets_auto - End');
log_message('log_message: insert_po_bc_packets_auto ' || sqlerrm||' Returning from insert_po_bc_packets_auto');
END insert_po_bc_packets_auto;