[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_tab_last_update_date pa_plsql_datatypes.datetabtyp;
g_tab_last_updated_by pa_plsql_datatypes.NumTabTyp;
g_tab_budget_version_id.delete;
g_tab_budget_line_id.delete;
g_tab_budget_ccid.delete;
g_tab_project_id.delete;
g_tab_task_id.delete;
g_tab_exp_type.delete;
g_tab_exp_org_id.delete;
g_tab_exp_item_date.delete;
g_tab_set_of_books_id.delete;
g_tab_je_source_name.delete;
g_tab_je_category_name.delete;
g_tab_doc_type.delete;
g_tab_doc_header_id.delete;
g_tab_doc_line_id.delete;
g_tab_doc_distribution_id.delete;
g_tab_inv_distribution_id.delete;
g_tab_actual_flag.delete;
g_tab_result_code.delete;
g_tab_status_code.delete;
g_tab_entered_dr.delete;
g_tab_entered_cr.delete;
g_tab_accounted_dr.delete;
g_tab_accounted_cr.delete;
g_tab_balance_posted_flag.delete;
g_tab_funds_process_mode.delete;
g_tab_burden_cost_flag.delete;
g_tab_org_id.delete;
g_tab_pkt_reference1.delete;
g_tab_pkt_reference2.delete;
g_tab_pkt_reference3.delete;
g_tab_event_id.delete;
g_tab_vendor_id.delete;
g_tab_burden_method_code.delete;
g_tab_main_or_backing_code.delete;
g_tab_source_event_id.delete;
g_tab_trxn_ccid.delete;
g_tab_p_bc_packet_id.delete;
g_tab_fck_reqd_flag.delete;
g_tab_ap_quantity_variance.delete;
g_tab_ap_amount_variance.delete;
g_tab_ap_base_qty_variance.delete;
g_tab_ap_base_amount_variance.delete;
g_tab_ap_po_distribution_id.delete;
g_tab_gl_date.delete;
g_tab_period_name.delete;
g_tab_entered_amount.delete;
g_tab_accted_amount.delete;
g_tab_event_type_code.delete;
g_tab_po_release_id.delete;
g_tab_distribution_type.delete;
g_tab_enc_type_id.delete;
g_line_type_lookup_code.delete;
g_ap_line_type_lkup.delete;
g_tab_orig_sequence_num.delete; -- Bug 5403775
g_tab_applied_to_dist_id_2.delete;
g_tab_rate.delete; -- Bug 5665232
g_tab_bc_packet_id.delete; -- Bug 5406690
g_tab_parent_reversal_id.delete; -- Bug 5406690
g_tab_reference1.delete;
g_tab_reference2.delete;
g_tab_reference3.delete;
g_tab_reference4.delete;
g_tab_reference5.delete;
g_tab_rowid.delete;
g_tab_period_num.delete;
g_tab_period_year.delete;
g_tab_last_update_date.delete;
g_tab_last_updated_by.delete;
PROCEDURE update_cwk_pkt_lines(p_calling_module IN varchar2,
p_packet_id IN NUMBER);
select sum(decode(com.parent_bc_packet_id, NULL, 0
,decode(p_bd_disp_method,'D', decode(com.expenditure_type,p_expenditure_type
,(nvl(com.accounted_dr,0) - nvl(com.accounted_cr,0)),0)
,(nvl(com.accounted_dr,0) - nvl(com.accounted_cr,0)))
)) ComBdAmt
,sum(decode(com.parent_bc_packet_id, NULL,(nvl(com.accounted_dr,0) - nvl(com.accounted_cr,0)),0)
) ComRawAmt
Into l_combdamt
,l_comrawamt
from pa_bc_commitments_all com
where com.project_id = p_project_id
and com.task_id = p_task_id
and com.budget_version_id = p_budget_version_id
and com.document_line_id = p_document_line_id
and com.document_type = p_document_type ;
select sum(decode(pkt.parent_bc_packet_id, NULL, 0
,decode(p_bd_disp_method,'D', decode(pkt.expenditure_type,p_expenditure_type
,(nvl(pkt.accounted_dr,0) - nvl(pkt.accounted_cr,0)),0)
,(nvl(pkt.accounted_dr,0) - nvl(pkt.accounted_cr,0)))
)) pktBdAmt
,sum(decode(pkt.parent_bc_packet_id, NULL,(nvl(pkt.accounted_dr,0) - nvl(pkt.accounted_cr,0)),0)
) pktRawAmt
Into l_pktbdamt
,l_pktrawamt
from pa_bc_packets pkt
Where pkt.project_id = p_project_id
and pkt.task_id = p_task_id
and pkt.budget_version_id = p_budget_version_id
and pkt.document_line_id = p_document_line_id
and pkt.document_type = p_document_type
and pkt.status_code in ('A','C')
and nvl(pkt.balance_posted_flag,'N') <> 'Y'
and nvl(pkt.funds_process_mode,'N') = 'T'
and substr(nvl(pkt.result_code,'P'),1,1) = 'P';
-- Select the Task level schedule override if not found
-- then select the Project level override
SELECT irs.ind_rate_sch_id,
t.cost_ind_sch_fixed_date
INTO l_sch_id,l_sch_date
FROM pa_tasks t,
pa_ind_rate_schedules irs
WHERE t.task_id = p_task_id
AND t.task_id = irs.task_id
AND irs.cost_ovr_sch_flag = 'Y';
SELECT irs.ind_rate_sch_id,
p.cost_ind_sch_fixed_date
INTO l_sch_id,l_sch_date
FROM pa_tasks t,
pa_projects_all p,
pa_ind_rate_schedules irs
WHERE t.task_id = p_task_id
AND t.project_id = p.project_id
AND t.project_id = irs.project_id
AND irs.cost_ovr_sch_flag = 'Y'
AND irs.task_id is null;
SELECT t.cost_ind_rate_sch_id,
t.cost_ind_sch_fixed_date
INTO l_sch_id ,l_sch_date
FROM pa_tasks t,
pa_ind_rate_schedules irs
WHERE t.task_id = p_task_id
AND t.cost_ind_rate_sch_id = irs.ind_rate_sch_id;
/* This api will update the summary level flag, compiled multiplier etc
* attributes required for contingent worker related transactions
*/
PROCEDURE upd_cwk_attributes(p_calling_module varchar2
,p_packet_id number
,p_mode varchar2
,p_reference varchar2
,x_return_status OUT NOCOPY varchar2 )
IS
--PRAGMA AUTONOMOUS_TRANSACTION;
l_rows_updated Number := 0;
select 'Y'
from dual
where exists (select null
from pa_bc_packets pkt
where pkt.packet_id = p_packet_id
and pkt.document_type = 'PO'
);
--cursor to select distinct cwk records in current pkt
cursor cur_cwkRecs IS
select pkt.project_id
,pkt.task_id
,pkt.budget_version_id
,pkt.document_line_id
,NVL(pt.burden_amt_display_method,'N') burden_amt_display_method
,decode(pt.burden_amt_display_method,'D'
,decode(pkt.parent_bc_packet_id,NULL,NULL,pkt.expenditure_type)
,NULL) expenditure_type
,decode(pt.burden_amt_display_method,'D'
,decode(pkt.parent_bc_packet_id,NULL,'RAW','BURDEN')
,'RAW') line_type
from pa_bc_packets pkt
,pa_projects_all pp
,pa_project_types_all pt
where pkt.packet_id = p_packet_id
and pkt.document_line_id is NOT NULL -- with R12 this check is not sufficient to find if the PO is an CWK PO
and pa_funds_control_utils2.is_CWK_PO(pkt.document_header_id,pkt.document_line_id
,pkt.document_distribution_id,pkt.org_id) = 'Y' -- R12 Funds management uptake
and pkt.document_type in ('PO') --,'EXP')
and pkt.status_code in ('P','A','C','I')
and substr(NVL(pkt.result_code,'P'),1,1) = 'P'
and pt.project_type = pp.project_type
and pt.org_id = pp.org_id --R12 Ledger change : Removed NVL clause
and pp.project_id = pkt.project_id
Group By
pkt.project_id
,pkt.task_id
,pkt.budget_version_id
,pkt.document_line_id
,NVL(pt.burden_amt_display_method,'N')
,decode(pt.burden_amt_display_method,'D'
,decode(pkt.parent_bc_packet_id,NULL,NULL,pkt.expenditure_type)
,NULL)
,decode(pt.burden_amt_display_method,'D'
,decode(pkt.parent_bc_packet_id,NULL,'RAW','BURDEN')
,'RAW');
select 'Y'
from dual
Where exists
(select null
from pa_bc_commitments_all comm
where comm.project_id = l_project_id
and comm.task_id = l_task_id
and comm.budget_version_id = l_budget_version_id
and comm.document_line_id = l_document_line_id
and comm.summary_record_flag = 'Y'
and comm.document_type = 'PO'
and ((l_bd_disp_method = 'D'
and comm.expenditure_type = l_expenditure_type
and comm.parent_bc_packet_id is NOT NULL)
OR ( l_bd_disp_method = 'D'
and comm.parent_bc_packet_id is NULL
and l_line_type = 'RAW' )
OR
( l_bd_disp_method <> 'D')
)
);
select 'Y'
from dual
where exists
(select null
from pa_bc_packets pkts1
where pkts1.document_line_id is NOT NULL
and pkts1.status_code in ('A','P','C','I')
and substr(NVL(pkts1.result_code,'P'),1,1) = 'P'
and nvl(pkts1.funds_process_mode,'T') <> 'B'
and nvl(pkts1.balance_posted_flag,'N') <> 'Y'
and pkts1.project_id = l_project_id
and pkts1.task_id = l_task_id
and pkts1.budget_version_id = l_budget_version_id
and pkts1.document_line_id = l_document_line_id
and pkts1.document_type = 'PO'
and pkts1.summary_record_flag = 'Y'
and ((l_bd_disp_method = 'D'
and pkts1.expenditure_type = l_expenditure_type
and pkts1.parent_bc_packet_id is NOT NULL)
OR ( l_bd_disp_method = 'D'
and pkts1.parent_bc_packet_id is NULL
and l_line_type = 'RAW' )
OR
(l_bd_disp_method <> 'D')
)
);
SELECT sum((nvl(accounted_dr,0) - nvl(accounted_cr,0)) *
decode(pkt.document_type,'PO',decode(pkt.parent_bc_packet_id,NULL,1,0),0)) comm_raw_amt
,sum((nvl(accounted_dr,0) - nvl(accounted_cr,0)) *
decode(pkt.document_type,'PO'
,decode(pkt.parent_bc_packet_id, NULL ,0
,decode(lv_bd_disp_method, 'D'
,decode(pkt.expenditure_type,lv_expenditure_type,1,0),1)),0)) comm_bd_amt
,sum((nvl(accounted_dr,0) - nvl(accounted_cr,0)) *
decode(pkt.document_type,'PO',decode(pkt.parent_bc_packet_id,NULL,1,0),0)) relevd_comm_raw_amt
,sum((nvl(accounted_dr,0) - nvl(accounted_cr,0)) *
decode(pkt.document_type,'PO'
,decode(pkt.parent_bc_packet_id,NULL ,0
,decode(lv_bd_disp_method, 'D'
,decode(pkt.expenditure_type,lv_expenditure_type,1,0),1)),0)) relevd_comm_bd_amt
FROM pa_bc_packets pkt
WHERE pkt.project_id = lv_project_id
AND pkt.budget_version_id = lv_budget_version_id
AND pkt.task_id = lv_task_id
AND pkt.document_line_id = lv_document_line_id
AND substr(nvl(pkt.result_code,'P'),1,1) = 'P'
AND pkt.status_code in ('A','C','B')
AND pkt.document_type in ('PO') --,'EXP')
AND nvl(pkt.balance_posted_flag,'N') = 'N'
AND nvl(pkt.funds_process_mode,'N') = 'T'
AND pkt.packet_id = p_packet_id ;
* then update the record with amt columns, If no record exists in bc_commiemtns or
* the record exists in bc_packets which is not yet swept then update the amts only
* If the summary record is creating first time then update all the relevent columns
*/
pa_funds_control_pkg.log_message(p_msg_token1 =>'Inside upd_cwk_attributes API params:packetId['||p_packet_id||
']mode['||p_mode||']callingModule['||p_calling_module||']Reference['||p_reference||']');
--loop through each project, task, document line and expenditure type and update the cwk attributes
FOR cwk IN cur_cwkRecs LOOP
OPEN cur_commsummrec(l_project_id => cwk.project_id
,l_task_id => cwk.task_id
,l_budget_version_id => cwk.budget_version_id
,l_document_line_id => cwk.document_line_id
,l_expenditure_type => cwk.expenditure_type
,l_bd_disp_method => cwk.burden_amt_display_method
,l_line_type => cwk.line_type );
l_rows_updated := 0;
l_rows_updated := 0;
UPDATE pa_bc_commitments_all com
SET com.comm_tot_raw_amt = nvl(com.comm_tot_raw_amt,0) +
decode(p_calling_module,'GL',decode(cwk.line_type,'RAW',nvl(l_comm_raw_amt,0),0),0)
,com.comm_tot_bd_amt = nvl(com.comm_tot_bd_amt,0) +
decode(p_calling_module,'GL'
,decode(cwk.burden_amt_display_method,'D'
,decode(cwk.line_type,'BURDEN',nvl(l_comm_bd_amt,0),0)
,nvl(l_comm_bd_amt,0)),0)
,com.comm_raw_amt_relieved = nvl(com.comm_raw_amt_relieved,0) -
decode(p_calling_module,'DISTCWKST'
,decode(cwk.line_type,'RAW',nvl(l_relvd_comm_raw_amt,0),0),0)
,com.comm_bd_amt_relieved = nvl(com.comm_bd_amt_relieved,0) -
decode(p_calling_module,'DISTCWKST'
,decode(cwk.burden_amt_display_method,'D'
,decode(cwk.line_type,'BURDEN',nvl(l_relvd_comm_bd_amt,0),0)
,nvl(l_relvd_comm_bd_amt,0)),0)
WHERE com.summary_record_flag = 'Y'
AND com.document_type = 'PO'
AND com.document_line_id is not null
AND com.project_id = cwk.project_id
AND com.task_id = cwk.task_id
AND com.budget_version_id = cwk.budget_version_id
AND com.document_line_id = cwk.document_line_id
AND (( -- burden lines should be stamped with summary record info if display method is different
com.parent_bc_packet_id is NOT NULL
and com.expenditure_type = cwk.expenditure_type
and cwk.line_type = 'BURDEN'
and cwk.burden_amt_display_method = 'D'
)
OR
( -- Sep line burden raw(only one line) should be stamped with summary record info
com.parent_bc_packet_id is NULL
and cwk.burden_amt_display_method = 'D'
and cwk.line_type = 'RAW'
and com.summary_record_flag = 'Y'
)
OR
( -- same line burden raw line should be stamped with summary record info if display method is same
com.parent_bc_packet_id is NULL
and cwk.burden_amt_display_method <> 'D'
)
);
l_rows_updated := sql%rowcount;
l_rows_updated := 0;
UPDATE pa_bc_packets pkt
SET pkt.comm_tot_raw_amt = nvl(pkt.comm_tot_raw_amt,0) +
decode(p_calling_module,'GL',decode(cwk.line_type,'RAW',nvl(l_comm_raw_amt,0),0),0)
,pkt.comm_tot_bd_amt = nvl(pkt.comm_tot_bd_amt,0) +
decode(p_calling_module,'GL'
,decode(cwk.burden_amt_display_method,'D'
,decode(cwk.line_type,'BURDEN',nvl(l_comm_bd_amt,0),0)
,nvl(l_comm_bd_amt,0)),0)
,pkt.comm_raw_amt_relieved = nvl(pkt.comm_raw_amt_relieved,0) -
decode(p_calling_module,'DISTCWKST'
,decode(cwk.line_type,'RAW',nvl(l_relvd_comm_raw_amt,0),0),0)
,pkt.comm_bd_amt_relieved = nvl(pkt.comm_bd_amt_relieved,0) -
decode(p_calling_module,'DISTCWKST'
,decode(cwk.burden_amt_display_method,'D'
,decode(cwk.line_type,'BURDEN',nvl(l_relvd_comm_bd_amt,0),0)
,nvl(l_relvd_comm_bd_amt,0)),0)
WHERE pkt.document_line_id is NOT NULL
AND pkt.document_type = 'PO'
AND NVL(pkt.summary_record_flag,'N') = 'Y'
AND substr(NVL(pkt.result_code,'P'),1,1) = 'P'
AND nvl(pkt.balance_posted_flag,'N' ) <> 'Y'
AND nvl(pkt.funds_process_mode,'N') = 'T'
AND pkt.status_code in ('A','B','C')
AND pkt.project_id = cwk.project_id
AND pkt.task_id = cwk.task_id
AND pkt.budget_version_id = cwk.budget_version_id
AND pkt.document_line_id = cwk.document_line_id
AND (( -- burden lines should be stamped with summary record info if display method is different
pkt.parent_bc_packet_id is NOT NULL
and pkt.expenditure_type = cwk.expenditure_type
and cwk.line_type = 'BURDEN'
and cwk.burden_amt_display_method = 'D'
)
OR
( -- Sep line burden raw(only one line) should be stamped with summary record info
pkt.parent_bc_packet_id is NULL
and cwk.burden_amt_display_method = 'D'
and cwk.line_type = 'RAW'
and pkt.summary_record_flag = 'Y'
)
OR
( -- same line burden raw line should be stamped with summary record info if display method is same
pkt.parent_bc_packet_id is NULL
and cwk.burden_amt_display_method <> 'D'
)
);
l_rows_updated := sql%rowcount;
l_rows_updated := 0;
UPDATE pa_bc_packets pkt
SET pkt.summary_record_flag = decode (pkt.summary_record_flag,NULL,'Y',pkt.summary_record_flag)
WHERE pkt.packet_id = p_packet_id
AND pkt.document_type = 'PO'
AND nvl(pkt.funds_process_mode,'N') = 'T'
AND nvl(pkt.balance_posted_flag,'N') <> 'Y'
AND pkt.summary_record_flag is NULL
AND pkt.project_id = cwk.project_id
AND pkt.task_id = cwk.task_id
AND pkt.budget_version_id = cwk.budget_version_id
AND pkt.document_line_id = cwk.document_line_id
AND decode(pkt.parent_bc_packet_id,NULL,'RAW','BURDEN') = cwk.line_type
AND (( -- sep line burden cost codes should be stamped with summary record info if display method is different
pkt.parent_bc_packet_id is NOT NULL
and pkt.expenditure_type = cwk.expenditure_type
and cwk.burden_amt_display_method = 'D'
and pkt.bc_packet_id = (select min(pkt1.bc_packet_id)
from pa_bc_packets pkt1
where pkt1.packet_id = pkt.packet_id
and pkt1.project_id = pkt.project_id
and pkt1.task_id = pkt.task_id
and pkt1.budget_version_id = pkt.budget_version_id
and pkt1.document_line_id = pkt.document_line_id
and pkt1.expenditure_type = pkt.expenditure_type
and pkt1.document_type = pkt.document_type
and pkt1.parent_bc_packet_id is NOT NULL
)
)
OR
( -- sep line burden lines only one raw line should be stamped with summary record info
pkt.parent_bc_packet_id is NULL
and cwk.burden_amt_display_method = 'D'
and pkt.bc_packet_id = (select min(pkt1.bc_packet_id)
from pa_bc_packets pkt1
where pkt1.packet_id = pkt.packet_id
and pkt1.project_id = pkt.project_id
and pkt1.task_id = pkt.task_id
and pkt1.budget_version_id = pkt.budget_version_id
and pkt1.document_line_id = pkt.document_line_id
--and pkt1.expenditure_type = pkt.expenditure_type
and pkt1.document_type = pkt.document_type
and pkt1.parent_bc_packet_id is NULL
)
)
OR
( -- raw line should be stamped with summary record info if display method is same
pkt.parent_bc_packet_id is NULL
and cwk.burden_amt_display_method <> 'D'
and pkt.bc_packet_id = (select min(pkt1.bc_packet_id)
from pa_bc_packets pkt1
where pkt1.packet_id = pkt.packet_id
and pkt1.project_id = pkt.project_id
and pkt1.task_id = pkt.task_id
and pkt1.budget_version_id = pkt.budget_version_id
and pkt1.document_line_id = pkt.document_line_id
and pkt1.document_type = pkt.document_type
and pkt1.parent_bc_packet_id is NULL
)
)
);
l_rows_updated := sql%rowcount;
l_rows_updated := 0;
UPDATE pa_bc_commitments_all cmt
SET cmt.compiled_multiplier = decode (cmt.document_line_id,NULL,cmt.compiled_multiplier,
l_cwk_multiplier)
WHERE cmt.summary_record_flag = 'Y'
AND cmt.document_line_id is NOT NULL
AND cmt.document_type = 'PO'
AND cmt.project_id = cwk.project_id
ANd cmt.task_id = cwk.task_id
AND cmt.budget_version_id = cwk.budget_version_id
AND cmt.document_line_id = cwk.document_line_id
AND (( -- burden lines should be stamped with summary record info if display method is different
cmt.parent_bc_packet_id is NOT NULL
and cmt.expenditure_type = cwk.expenditure_type
and cwk.burden_amt_display_method = 'D'
)
OR
( -- raw line should be stamped with summary record info if display method is same
cmt.parent_bc_packet_id is NULL
and cwk.burden_amt_display_method <> 'D'
)
);
l_rows_updated := sql%rowcount;
UPDATE pa_bc_packets cmt
SET cmt.compiled_multiplier = decode (cmt.document_line_id,NULL,cmt.compiled_multiplier
,l_cwk_multiplier)
WHERE cmt.summary_record_flag = 'Y'
AND cmt.document_line_id is NOT NULL
AND cmt.document_type = 'PO'
AND cmt.project_id = cwk.project_id
ANd cmt.task_id = cwk.task_id
AND cmt.budget_version_id = cwk.budget_version_id
AND cmt.document_line_id = cwk.document_line_id
AND NVL(cmt.balance_posted_flag,'N') <> 'Y'
AND nvl(cmt.funds_process_mode,'N') = 'T'
AND cmt.status_code IN ('A','C')
AND substr(nvl(cmt.result_code,'P'),1,1) = 'P'
AND (( -- burden lines should be stamped with summary record info if display method is different
cmt.parent_bc_packet_id is NOT NULL
and cmt.expenditure_type = cwk.expenditure_type
and cwk.burden_amt_display_method = 'D'
)
OR
( -- raw line should be stamped with summary record info if display method is same
cmt.parent_bc_packet_id is NULL
and cwk.burden_amt_display_method <> 'D'
)
);
l_rows_updated := sql%rowcount;
pa_funds_control_pkg.log_message(p_msg_token1 =>l_stage||'- Num of Rows cwk attribute Updated['||l_rows_updated||']');
SELECT gl_bc_packets_s.nextval
FROM dual;
l_update_login NUMBER := FND_GLOBAL.login_id;
If l_update_login is null then
l_update_login := -1;
pa_funds_control_pkg.log_message(p_msg_token1 => 'Inserting records in pa_bc_packets');
INSERT INTO pa_bc_packets
( ---- who columns------
request_id,
program_id,
program_application_id,
program_update_date,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
------ main columns-----------
packet_id,
bc_packet_id,
budget_version_id,
project_id,
task_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
set_of_books_id,
je_source_name,
je_category_name,
document_type,
document_header_id,
document_distribution_id,
actual_flag,
period_name,
period_year,
period_num,
result_code,
status_code,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
gl_row_number,
balance_posted_flag,
funds_process_mode,
txn_ccid,
encumbrance_type_id,
burden_cost_flag,
org_id,
parent_resource_id,
resource_list_member_id,
proj_encumbrance_type_id,
budget_ccid,
document_line_id,
reference1,
reference2,
reference3,
-- R12 Funds Management Uptake: Newly added columns
bc_event_id,
budget_line_id,
session_id,
serial_id,
vendor_id,
main_or_backing_code,
burden_method_code,
source_event_id,
document_distribution_type,
document_header_id_2
)
SELECT
l_request_id,
l_program_id,
l_program_application_id,
sysdate,
sysdate,
l_update_login,
l_update_login,
sysdate,
l_update_login,
l_packet_id,
pa_bc_packets_s.nextval,
pbc.budget_version_id,
pbc.project_id,
pbc.task_id,
pbc.expenditure_type,
pbc.expenditure_organization_id,
trunc(pbc.expenditure_item_date),
pbc.set_of_books_id,
pbc.je_source_name,
pbc.je_category_name,
pbc.document_type,
pbc.document_header_id,
pbc.document_distribution_id,
pbc.actual_flag,
pbc.period_name,
pbc.period_year,
pbc.period_num,
pbc.result_code,
'P', -- status_code,
NVL(pbc.entered_cr,0),
NVL(pbc.entered_dr,0),
NVL(pbc.accounted_cr,0),
NVL(pbc.accounted_dr,0),
pbc.gl_row_number,
pbc.balance_posted_flag,
pbc.funds_process_mode ,
pbc.txn_ccid,
pbc.encumbrance_type_id,
pbc.burden_cost_flag,
pbc.org_id,
pbc.parent_resource_id,
pbc.resource_list_member_id,
pbc.proj_encumbrance_type_id,
pbc.budget_ccid,
pbc.document_line_id,
pbc.reference1,
pbc.reference2,
pbc.reference3,
-- R12 Funds Management Uptake: Newly added columns
pbc.bc_event_id,
pbc.budget_line_id,
pbc.session_id,
pbc.serial_id,
pbc.vendor_id,
pbc.main_or_backing_code,
pbc.burden_method_code,
pbc.source_event_id,
pbc.document_distribution_type,
pbc.document_header_id_2
FROM
pa_bc_packets pbc
WHERE pbc.packet_id = x_packet_id;
pa_funds_control_pkg.log_message(p_msg_token1 => 'Inserting records in pa_bc_packets');
INSERT INTO pa_bc_packets
( ---- who columns------
request_id,
program_id,
program_application_id,
program_update_date,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
------ main columns-----------
packet_id,
bc_packet_id,
budget_version_id,
project_id,
task_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
set_of_books_id,
je_source_name,
je_category_name,
document_type,
document_header_id,
document_distribution_id,
actual_flag,
period_name,
period_year,
period_num,
result_code,
status_code,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
gl_row_number, --gl_row_bc_packet_row_id
balance_posted_flag,
funds_process_mode,
txn_ccid,
encumbrance_type_id,
burden_cost_flag,
org_id,
parent_resource_id,
resource_list_member_id,
proj_encumbrance_type_id,
budget_ccid,
document_line_id,
reference1,
reference2,
reference3,
-- R12 Funds Management Uptake: Newly added columns
bc_event_id,
budget_line_id,
session_id,
serial_id,
vendor_id,
main_or_backing_code,
burden_method_code,
source_event_id,
document_distribution_type,
document_header_id_2
)
SELECT
l_request_id,
l_program_id,
l_program_application_id,
sysdate,
sysdate,
l_update_login,
l_update_login,
sysdate,
l_update_login,
l_packet_id,
pa_bc_packets_s.nextval,
pbc.budget_version_id,
pbc.project_id,
pbc.task_id,
pbc.expenditure_type,
pbc.expenditure_organization_id,
trunc(pbc.expenditure_item_date),
pbc.set_of_books_id,
pbc.je_source_name,
pbc.je_category_name,
pbc.document_type,
pbc.document_header_id,
pbc.document_distribution_id,
pbc.actual_flag,
pbc.period_name,
pbc.period_year,
pbc.period_num,
pbc.result_code,
'P', -- status_code,
NVL(pbc.entered_cr,0),
NVL(pbc.entered_dr,0),
NVL(pbc.accounted_cr,0),
NVL(pbc.accounted_dr,0),
pbc.gl_row_number,
pbc.balance_posted_flag,
pbc.funds_process_mode ,
pbc.txn_ccid,
pbc.encumbrance_type_id,
pbc.burden_cost_flag,
pbc.org_id,
pbc.parent_resource_id,
pbc.resource_list_member_id,
pbc.proj_encumbrance_type_id,
pbc.budget_ccid,
pbc.document_line_id,
pbc.reference1,
pbc.reference2,
pbc.reference3,
-- R12 Funds Management Uptake: Newly added columns
pbc.bc_event_id,
pbc.budget_line_id,
pbc.session_id,
pbc.serial_id,
pbc.vendor_id,
pbc.main_or_backing_code,
pbc.burden_method_code,
pbc.source_event_id,
pbc.document_distribution_type,
pbc.document_header_id_2
FROM
pa_bc_packets pbc
WHERE pbc.packet_id = x_packet_id
AND document_type in ('CC_C_CO','CC_P_CO')
AND document_header_id = p_reference2;
SELECT 'PO'
,po_header_id
,po_distribution_id
FROM po_distributions_all pod
WHERE pod.req_distribution_id = p_req_distribution_id ;
SELECT parent_bc_packet_id
FROM ( SELECT comm.bc_commitment_id,
comm.parent_bc_packet_id
FROM pa_bc_commitments comm
WHERE comm.document_distribution_id = p_req_distribution_id
AND comm.document_header_id = p_req_header_id
AND comm.document_type = 'REQ'
UNION ALL
SELECT null bc_commitment_id,
pbc.parent_bc_packet_id
FROM pa_bc_packets pbc
WHERE pbc.document_distribution_id = p_req_distribution_id
AND pbc.document_header_id = p_req_header_id
AND pbc.document_type = 'REQ'
AND pbc.balance_posted_flag = 'N'
AND pbc.status_code in ('A','C')
AND substr(nvl(result_code,'P'),1,1) = 'P');
SELECT po.req_header_reference_num
,po.req_line_reference_num
,po.destination_type_code
FROM po_distributions_all po
WHERE po.po_distribution_id = p_po_distribution_id;
SELECT DISTINCT cc_acct_line_id
FROM igc_cc_det_pf
WHERE cc_det_pf_line_id = p_det_pf_line_id;
SELECT parent_bc_packet_id
FROM ( SELECT comm.parent_bc_packet_id
FROM pa_bc_commitments comm
WHERE comm.document_distribution_id = p_distribution_id
AND comm.document_header_id = p_header_id
AND comm.document_type = p_document_type
AND NVL(comm.document_header_id_2 ,-99) = NVL(p_po_release_id,-99)
UNION ALL
SELECT pbc.parent_bc_packet_id
FROM pa_bc_packets pbc
WHERE pbc.document_distribution_id = p_distribution_id
AND pbc.document_header_id = p_header_id
AND pbc.document_type = p_document_type
AND pbc.balance_posted_flag = 'N'
AND pbc.status_code in ('A','C')
AND substr(nvl(result_code,'P'),1,1) = 'P'
AND NVL(pbc.document_header_id_2 ,-99) = NVL(p_po_release_id,-99));
pa_funds_control_pkg.log_message(p_msg_token1 => ' PO is Inventory based so update status code to S');
select pa_bc_packets_s.nextval
into g_tab_bc_packet_id(p_new_rec_index)
from dual;
SELECT po.po_line_id po_line_id,
po.po_header_id ,
(SELECT encumbrance_type_id
FROM gl_encumbrance_types
WHERE encumbrance_type_KEY = 'Obligation') po_encumbrance_type_id ,
po.rate -- Bug 5665232
FROM po_distributions_all po
WHERE po.po_distribution_id = p_po_distribution_id;
SELECT type_lookup_code
FROM po_headers_all po
WHERE po.po_header_id = p_po_header_id
AND p_po_release_id IS NULL
UNION
SELECT release_type
FROM po_releases_all po
WHERE po.po_release_id = p_po_release_id
AND p_po_release_id IS NOT NULL;
SELECT DECODE(SIGN(p_variance),-1,0, p_variance) entered_dr,
DECODE(SIGN(p_variance),-1,ABS(p_variance),0) entered_cr,
DECODE(SIGN(p_base_variance),-1,0, p_base_variance) accounted_dr,
DECODE(SIGN(p_base_variance),-1,ABS(p_base_variance),0) accounted_cr
FROM dual;
SELECT pa_funds_control_utils2.is_CWK_PO
(po.po_header_id,
po.po_line_id,
po.po_distribution_id,
po.org_id) cwk_po_flag,
NVL(po.accrue_on_receipt_flag,'N') accrue_on_receipt_flag
FROM po_distributions_all po
WHERE po.po_distribution_id = p_po_distribution_id;
SELECT DISTINCT pbc.set_of_books_id
FROM pa_bc_packets pbc
WHERE pbc.packet_id = p_packet_id
AND pbc.status_code = 'I'
AND substr(nvl(pbc.result_code,'P'),1,1) not in ('R','F');
SELECT DISTINCT
pbc.project_id,
pbc.task_id,
pbc.period_name,
pbc.gl_date,
pbc.set_of_books_id,
pbc.budget_version_id,
pm.entry_level_code,
DECODE(pm.entry_level_code,'P',0,pt.top_task_id) top_task_id,
pbc.resource_list_member_id
FROM pa_bc_packets pbc,
pa_tasks pt,
pa_budget_versions bv,
pa_budget_entry_methods pm
WHERE pbc.packet_id = p_packet_id
AND pbc.budget_version_id = bv.budget_version_id
AND bv.budget_entry_method_code = pm.budget_entry_method_code
AND pbc.status_code = 'I'
--AND substr(nvl(pbc.result_code,'P'),1,1) not in ('R','F')
AND pt.task_id = pbc.task_id
and nvl(ext_bdgt_flag,'N') = 'Y';
SELECT gl.start_date
FROM gl_period_statuses gl
WHERE gl.application_id = 101
AND gl.set_of_books_id = p_sob_id
AND gl.period_name = p_period_name;
UPDATE pa_bc_packets
set budget_ccid = l_budget_ccid,
budget_line_id = l_budget_line_id,
status_code = DECODE(status_code,'F',status_code,'R',status_code,'T',status_code,DECODE(p_bc_mode,'C','F','R')),
result_code = DECODE(substr(result_code,1,1),'F',result_code,'F132'),
PROJECT_RESULT_CODE = DECODE(substr(PROJECT_RESULT_CODE,1,1),'F',PROJECT_RESULT_CODE,'F132'),
TASK_RESULT_CODE = DECODE(substr(TASK_RESULT_CODE,1,1),'F',TASK_RESULT_CODE,'F132'),
RES_GRP_RESULT_CODE = DECODE(substr(RES_GRP_RESULT_CODE,1,1),'F',RES_GRP_RESULT_CODE,'F132'),
RES_RESULT_CODE = DECODE(substr(RES_RESULT_CODE,1,1),'F',RES_RESULT_CODE,'F132'),
TOP_TASK_RESULT_CODE = DECODE(substr(TOP_TASK_RESULT_CODE,1,1),'F',TOP_TASK_RESULT_CODE,'F132'),
PROJECT_ACCT_RESULT_CODE = DECODE(substr(PROJECT_ACCT_RESULT_CODE,1,1),'F',PROJECT_ACCT_RESULT_CODE,'F132')
WHERE packet_id = p_packet_id
AND status_code ='I'
AND project_id = c_pkt.project_id
AND task_id = c_pkt.task_id
AND resource_list_member_id = c_pkt.resource_list_member_id
AND NVL(period_name,'X') = NVL(c_pkt.period_name,'X')
AND gl_date = c_pkt.gl_date ;
pa_funds_control_pkg.log_message(p_msg_token1 => 'DERIVE_PKT_RLMI_BUDGET_CCID - # of records updated to F132='||SQL%ROWCOUNT);
UPDATE pa_bc_packets
set budget_ccid = l_budget_ccid,
budget_line_id = l_budget_line_id
WHERE packet_id = p_packet_id
AND status_code ='I'
AND project_id = c_pkt.project_id
AND task_id = c_pkt.task_id
AND resource_list_member_id = c_pkt.resource_list_member_id
AND NVL(period_name,'X') = NVL(c_pkt.period_name,'X')
AND gl_date = c_pkt.gl_date ;
pa_funds_control_pkg.log_message(p_msg_token1 => 'DERIVE_PKT_RLMI_BUDGET_CCID - # of records updated with budget ccid info='||SQL%ROWCOUNT);
SELECT 1
FROM pa_bc_packets
WHERE packet_id = p_packet_id
AND (status_code in ('F','T','R')
OR SUBSTR (result_code,1,1) = 'F');
UPDATE pa_bc_packets a
SET a.status_code = DECODE(p_bc_mode,'C','F','R'),
a.result_code = DECODE( SUBSTR (result_code,1,1),'F',result_code,'F170'),
res_result_code = DECODE(substr(res_result_code,1,1),'F',res_result_code,'F170'),
res_grp_result_code = DECODE(substr(res_grp_result_code,1,1),'F',res_grp_result_code,'F170'),
task_result_code = DECODE(substr(task_result_code,1,1),'F',task_result_code,'F170'),
top_task_result_code = DECODE(substr(top_task_result_code,1,1),'F',top_task_result_code,'F170'),
project_result_code = DECODE(substr(project_result_code,1,1),'F',project_result_code,'F170'),
project_acct_result_code = DECODE(substr(project_acct_result_code,1,1),'F',project_acct_result_code,'F170')
WHERE a.status_code = 'I'
AND a.status_code <> 'F'
AND a.packet_id = p_packet_id;
UPDATE pa_bc_packets a
SET a.status_code = DECODE(p_bc_mode,'C','F','R'),
a.result_code = 'F168' ,
res_result_code = DECODE(substr(res_result_code,1,1),'F',res_result_code,'F168'),
res_grp_result_code = DECODE(substr(res_grp_result_code,1,1),'F',res_grp_result_code,'F168'),
task_result_code = DECODE(substr(task_result_code,1,1),'F',task_result_code,'F168'),
top_task_result_code = DECODE(substr(top_task_result_code,1,1),'F',top_task_result_code,'F168'),
project_result_code = DECODE(substr(project_result_code,1,1),'F',project_result_code,'F168'),
project_acct_result_code = DECODE(substr(project_acct_result_code,1,1),'F',project_acct_result_code,'F168')
WHERE a.status_code = 'I'
AND a.ext_bdgt_flag = 'Y'
AND a.packet_id = p_packet_id
AND a.bc_event_id is null;
UPDATE pa_bc_packets
SET status_code = 'T',
result_code = 'F142',
res_result_code = DECODE(substr(res_result_code,1,1),'F',res_result_code,'F142'),
res_grp_result_code = DECODE(substr(res_grp_result_code,1,1),'F',res_grp_result_code,'F142'),
task_result_code = DECODE(substr(task_result_code,1,1),'F',task_result_code,'F142'),
top_task_result_code = DECODE(substr(top_task_result_code,1,1),'F',top_task_result_code,'F142'),
project_result_code = DECODE(substr(project_result_code,1,1),'F',project_result_code,'F142'),
project_acct_result_code = DECODE(substr(project_acct_result_code,1,1),'F',project_acct_result_code,'F142')
WHERE packet_id <> p_packet_id
AND status_code = 'I';
SELECT NULL budget_version_id,
NULL budget_line_id,
NULL budget_ccid,
pobc.pa_project_id project_id,
pobc.pa_task_id task_id,
pobc.pa_exp_type expenditure_type,
pobc.pa_exp_org_id expenditure_organization_id,
pobc.pa_exp_item_date expenditure_item_date,
pobc.ledger_id set_of_books_id,
-- The below hardcoded values for Je_source_name and Je_category_name columns
-- are based on SLA seed data for PO and REQ. These values are later
-- updated by pa_funds_check API to synch up with gl_bc_packets if different
'Purchasing' je_source_name,
DECODE(pobc.distribution_type,
'REQUISITION','Requisitions',
'BLANKET' ,'Release',
'SCHEDULED' ,'Release',
'Purchases') je_category_name,
DECODE(pobc.distribution_type,'REQUISITION','REQ','PO') document_type,
pobc.header_id document_header_id,
-- Populated for PA Purchasing extracts.And with this change document_line_id
-- is no longer a unique way of identifying if the BC packet is associated with CWK
pobc.line_id document_line_id,
pobc.distribution_id document_distribution_id,
'E' actual_flag,
NULL result_code,
'I' status_code,
pobc.event_type_code event_type_code,
pobc.entered_amt entered_amount,
pobc.accounted_amt accounted_amount,
'N' balance_posted_flag,
'T' funds_process_mode,
'N' burden_cost_flag,
NULL org_id,
DECODE(pobc.distribution_type,'REQUISITION','REQ','PO') reference1,
pobc.header_id reference2,
pobc.distribution_id reference3,
NULL bc_event_id,
NULL vendor_id,
pa_funds_control_pkg.check_bdn_on_sep_item
(pobc.pa_project_id) burden_method_code,
pobc.main_or_backing_code main_or_backing_code,
pobc.ae_event_id source_event_id,
pobc.CODE_COMBINATION_ID txn_ccid,
NULL parent_bc_packet_id,
pa_funds_control_utils.get_fnd_reqd_flag
(pobc.pa_project_id,'STD') fck_reqd_flag,
pobc.gl_date gl_date,
pogt.period_name period_name,
-- Below code is added to handle Requisition adjusment scenario :
-- If REQ adjusted line has adjustment type as 'OLD' then amount previously reserved should be relieved
-- else if REQ adjusted line has adjustment type as 'NEW' then new amount should be reserved
-- i.e. IF distribution_type = 'REQUISITION_ADJUSTED_OLD' then CR
-- IF distribution_type = 'REQUISITION_ADJUSTED_NEW' then DR
DECODE(event_type_code, 'REQ_ADJUSTED',
DECODE(pobc.main_or_backing_code,'M',
DECODE(pobc.adjustment_status
,'OLD',pobc.distribution_type||'_ADJUSTED_OLD'
,'NEW',pobc.distribution_type||'_ADJUSTED_NEW'
,pobc.distribution_type),
pobc.distribution_type),
pobc.distribution_type) distribution_type,
DECODE(pobc.distribution_type,'SCHEDULED',pobc.po_release_id
,'BLANKET',pobc.po_release_id
,NULL) release_id,
DECODE(pobc.distribution_type,'REQUISITION'
,1000
,decode(pobc.main_or_backing_code, 'B_REQ', 1000, 1001)) encumbrance_type_id,
-- Bug 5403775 : Added below columns to derive pkt reference columns for backing docs such that
-- they will point to the main doc
POBC.origin_sequence_num,
pobc.applied_to_dist_id_2,
pa_bc_packets_s.nextval bc_packet_id -- Bug 5406690
FROM po_bc_distributions pobc ,
po_encumbrance_gt pogt,
psa_bc_xla_events_gt xlaevt
WHERE pobc.ae_event_id = xlaevt.event_id
AND pobc.pa_project_id IS NOT NULL
AND pogt.distribution_id = pobc.distribution_id
AND pogt.distribution_type = pobc.distribution_type
AND nvl(pogt.reference15,-999999)= nvl(pobc.reference15,-999999) --Bug 12984297
AND EXISTS ( SELECT 1
FROM po_requisition_lines_all porl
WHERE nvl(porl.DESTINATION_TYPE_CODE,'EXPENSE') = 'EXPENSE'
AND porl.requisition_line_id = pobc.line_id
AND pobc.distribution_type = 'REQUISITION'
UNION ALL
SELECT 1
FROM po_distributions_all pord
WHERE nvl(pord.DESTINATION_TYPE_CODE,'EXPENSE') = 'EXPENSE'
AND pord.po_distribution_id = pobc.distribution_id
AND pobc.distribution_type <> 'REQUISITION' )
/** Bug fix : 2347699 added this check to make sure that project is not cross charged **/
/** BugFix : If grants and projects enables in the same ou then errors with no data found in cursors*/
AND EXISTS (SELECT 1
FROM pa_projects_all pp,
pa_implementations_all imp
WHERE pp.project_id = pobc.pa_project_id
AND pp.org_id = imp.org_id
AND imp.set_of_books_id = pobc.ledger_id )
AND EXISTS ( SELECT 'Project Bdgt Ctrl enabled'
FROM pa_budget_types bdgttype
,pa_budgetary_control_options pbct
WHERE pbct.project_id = pobc.pa_project_id
AND pbct.BDGT_CNTRL_FLAG = 'Y'
AND (pbct.EXTERNAL_BUDGET_CODE = 'GL' OR
pbct.EXTERNAL_BUDGET_CODE is NULL
)
AND pbct.BUDGET_TYPE_CODE = bdgttype.budget_type_code
AND bdgttype.budget_amount_code = 'C'
);
SELECT apd.invoice_distribution_id,
DECODE(apd.line_type_lookup_code -- Bug 5490378
,'NONREC_TAX',DECODE(apd.prepay_distribution_id,NULL,apd.line_type_lookup_code,'PREPAY')
,apd.line_type_lookup_code)
FROM ap_invoice_distributions_all apd ,
psa_bc_xla_events_gt xlaevt,
ap_invoices_all apinv
WHERE apd.bc_event_id = xlaevt.event_id
AND apd.project_id IS NOT NULL
AND NVL(apd.pa_addition_flag, 'X' ) <> 'T'
AND apinv.invoice_id = apd.invoice_id
AND apinv.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND apd.line_type_lookup_code <> 'REC_TAX'
-- R12 : Prepayments mathed to PO will not be fundschecked
AND ((apinv.invoice_type_lookup_code = 'PREPAYMENT'
AND apd.po_distribution_id IS NULL )
OR apinv.invoice_type_lookup_code <> 'PREPAYMENT')
--R12 : Application of Prepayment matched to PO will not be fundschecked
--Bug 5490378 : NONREC_TAX associated with prepay line should be filtered out
AND NOT EXISTS
( SELECT 1
FROM dual
WHERE apd.line_type_lookup_code IN ('PREPAY','NONREC_TAX')
AND apd.prepay_distribution_id IS NOT NULL
AND apd.po_distribution_id IS NOT NULL)
-- Bug 5562245 : As part of PSA bug 5563122 fix ,code logic has been modified such that
-- Variances on AP matched to PO with accrue on receipt will not be fundschecked.
-- Bug 5494476 : AP ITEM Distribution matched to CWK PO will be fundschecked only for the
-- the amout/quantity variance amount.If no variance stamped on the ITEM distribution then
-- filter out those distributions.
-- Bug 5533290 : AP TAX Distribution matched to CWK PO will be fundschecked only for the
-- the variance amount.Even tough AP TAX is eligible for interface to projects,the commitment amount
-- will remain with PO and during interface will be relieved from PO bucket
AND NOT EXISTS ( SELECT 1
FROM po_distributions_all pod
WHERE pod.po_distribution_id = apd.po_distribution_id
AND apd.po_distribution_id IS NOT NULL
AND ((NVL(pod.accrue_on_receipt_flag,'N') = 'Y' -- Bug 5348212
AND apd.line_type_lookup_code IN ('ITEM','ACCRUAL','NONREC_TAX'))
OR
(pa_funds_control_utils2.is_CWK_PO -- Bug 5494476
(pod.po_header_id,pod.po_line_id,pod.po_distribution_id,pod.org_id) = 'Y'
AND apd.line_type_lookup_code IN ('ITEM','NONREC_TAX') -- Bug 5533290
AND NVL(apd.amount_variance,0)= 0
AND NVL(apd.base_amount_variance,0)=0
AND NVL(apd.quantity_variance,0)=0
AND NVL(apd.base_quantity_variance,0)=0
)
)
)
/** Bug fix : 2347699 added this check to make sure that project is not cross charged **/
/** BugFix : If grants and projects enables in the same ou then errors with no data found in cursors*/
AND EXISTS (SELECT 1
FROM pa_projects_all pp,
pa_implementations_all imp
WHERE pp.project_id = apd.project_id
AND pp.org_id = imp.org_id
AND imp.set_of_books_id = apd.set_of_books_id )
AND EXISTS ( SELECT 'Project Bdgt Ctrl enabled'
FROM pa_budget_types bdgttype
,pa_budgetary_control_options pbct
WHERE pbct.project_id = apd.project_id
AND pbct.BDGT_CNTRL_FLAG = 'Y'
AND (pbct.EXTERNAL_BUDGET_CODE = 'GL' OR
pbct.EXTERNAL_BUDGET_CODE is NULL
)
AND pbct.BUDGET_TYPE_CODE = bdgttype.budget_type_code
AND bdgttype.budget_amount_code = 'C'
)
/*Bug 13602288 :Added the below union for the self assessed tax changes,If there exists pre-payments we need to handle for SAT also*/
UNION
SELECT apd.invoice_distribution_id,
DECODE(apd.line_type_lookup_code
,'NONREC_TAX',DECODE(apd.prepay_distribution_id,NULL,decode(apd.line_type_lookup_code,'NONREC_TAX','SELF_ASSESSED_TAX',apd.line_type_lookup_code),'PREPAY')
,decode(apd.line_type_lookup_code,'NONREC_TAX','SELF_ASSESSED_TAX',apd.line_type_lookup_code))
FROM ap_self_assessed_tax_dist_all apd ,
psa_bc_xla_events_gt xlaevt,
ap_invoices_all apinv
WHERE apd.bc_event_id = xlaevt.event_id
AND apd.project_id IS NOT NULL
AND NVL(apd.pa_addition_flag, 'X' ) <> 'T'
AND apinv.invoice_id = apd.invoice_id
AND apinv.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND apd.line_type_lookup_code <> 'REC_TAX'
AND ((apinv.invoice_type_lookup_code = 'PREPAYMENT'
AND apd.po_distribution_id IS NULL )
OR apinv.invoice_type_lookup_code <> 'PREPAYMENT')
AND NOT EXISTS
( SELECT 1
FROM dual
WHERE apd.line_type_lookup_code IN ('PREPAY','NONREC_TAX')
AND apd.prepay_distribution_id IS NOT NULL
AND apd.po_distribution_id IS NOT NULL)
AND NOT EXISTS ( SELECT 1
FROM po_distributions_all pod
WHERE pod.po_distribution_id = apd.po_distribution_id
AND apd.po_distribution_id IS NOT NULL
AND ((NVL(pod.accrue_on_receipt_flag,'N') = 'Y'
AND apd.line_type_lookup_code IN ('ITEM','ACCRUAL','NONREC_TAX'))
OR
(pa_funds_control_utils2.is_CWK_PO
(pod.po_header_id,pod.po_line_id,pod.po_distribution_id,pod.org_id) = 'Y'
AND apd.line_type_lookup_code IN ('ITEM','NONREC_TAX')
AND NVL(apd.amount_variance,0)= 0
AND NVL(apd.base_amount_variance,0)=0
AND NVL(apd.quantity_variance,0)=0
AND NVL(apd.base_quantity_variance,0)=0
)
)
)
AND EXISTS (SELECT 1
FROM pa_projects_all pp,
pa_implementations_all imp
WHERE pp.project_id = apd.project_id
AND pp.org_id = imp.org_id
AND imp.set_of_books_id = apd.set_of_books_id )
AND EXISTS ( SELECT 'Project Bdgt Ctrl enabled'
FROM pa_budget_types bdgttype
,pa_budgetary_control_options pbct
WHERE pbct.project_id = apd.project_id
AND pbct.BDGT_CNTRL_FLAG = 'Y'
AND (pbct.EXTERNAL_BUDGET_CODE = 'GL' OR
pbct.EXTERNAL_BUDGET_CODE is NULL
)
AND pbct.BUDGET_TYPE_CODE = bdgttype.budget_type_code
AND bdgttype.budget_amount_code = 'C'
);
SELECT /*+ leading(PBXEG, APEXT.AID ) USE_NL(APEXT.AID) cardinality(PBXEG,1) */ NULL budget_version_id, --Added hint for bug 14752984
NULL budget_line_id,
NULL budget_ccid,
apext.aid_project_id project_id,
apext.aid_task_id task_id,
apext.aid_expenditure_type expenditure_type,
apext.aid_expenditure_org_id expenditure_organization_id,
-- The below hardcoded values for Je_source_name and Je_category_name columns
-- are based on SLA seed data for PO and REQ. These values are later
-- updated by pa_funds_check API to synch up with gl_bc_packets if different
'Payables' je_source_name,
'Purchase Invoices' je_category_name,
'AP' document_type,
apext.bus_flow_inv_id document_header_id,
apext.aid_invoice_line_number document_line_id,
apext.aid_invoice_dist_id document_distribution_id,
-- For standard invoice this is always same as document_distribution_id
apext.aid_invoice_dist_id invoice_distribution_id,
'E' actual_flag,
NULL result_code,
'I' status_code,
ENCUMBRANCE_AMOUNT entered_amount, --Bug 5203226/5498978
ENCUMBRANCE_BASE_AMOUNT accounted_amount, --Bug 5203226/5498978
'N' balance_posted_flag,
'T' funds_process_mode,
'N' burden_cost_flag,
--Below decode ensures that PO is relieved only when attached to ITEM/ACCRUAL line
DECODE(apext.AID_LINE_TYPE_LOOKUP_CODE
,'ITEM',DECODE(apext.po_distribution_id,NULL,'AP','PO')
,'ACCRUAL',DECODE(apext.po_distribution_id,NULL,'AP','PO')
,'NONREC_TAX',DECODE(apext.SELF_ASSESSED_TAX_FLAG,'Y',NULL,DECODE(apext.po_distribution_id,NULL,'AP','PO')) -- Bug 5523570
,NULL) reference1,
DECODE(apext.AID_LINE_TYPE_LOOKUP_CODE
,'ITEM', DECODE(apext.po_distribution_id
,NULL,apext.bus_flow_inv_id
,apext.bus_flow_po_doc_id)
,'ACCRUAL',DECODE(apext.po_distribution_id
,NULL,apext.bus_flow_inv_id
,apext.bus_flow_po_doc_id)
,'NONREC_TAX',DECODE(apext.SELF_ASSESSED_TAX_FLAG,'Y',NULL,DECODE(apext.po_distribution_id -- Bug 5523570
,NULL,apext.bus_flow_inv_id
,apext.bus_flow_po_doc_id))
, NULL) reference2,
DECODE(apext.AID_LINE_TYPE_LOOKUP_CODE
,'ITEM', DECODE(apext.po_distribution_id
,NULL,apext.aid_invoice_dist_id
,apext.po_distribution_id)
,'ACCRUAL',DECODE(apext.po_distribution_id
,NULL,apext.aid_invoice_dist_id
,apext.po_distribution_id)
,'NONREC_TAX',DECODE(apext.SELF_ASSESSED_TAX_FLAG,'Y',NULL,DECODE(apext.po_distribution_id -- Bug 5523570
,NULL,apext.aid_invoice_dist_id
,apext.po_distribution_id))
, NULL) reference3,
NULL bc_event_id,
pa_funds_control_pkg.check_bdn_on_sep_item
(apext.aid_project_id) burden_method_code,
NULL main_or_backing_code,
apext.event_id source_event_id,
apext.aid_dist_ccid txn_ccid,
NULL parent_bc_packet_id,
pa_funds_control_utils.get_fnd_reqd_flag
(apext.aid_project_id ,'STD') fck_reqd_flag,
apext.aid_quantity_variance ap_quantity_variance,
apext.aid_amount_variance ap_amount_variance,
apext.aid_base_quantity_variance ap_base_quantity_variance,
apext.aid_base_amount_variance ap_base_amount_variance,
/* Bug 5406564 : Below decode ensures that PO is relieved only when attached to ITEM/ACCRUAL/NONREC_TAX line
and not for variance records. Added SAT_FLAG for Bug 13622390 */
DECODE(apext.AID_LINE_TYPE_LOOKUP_CODE
,'ITEM',apext.po_distribution_id
,'ACCRUAL',apext.po_distribution_id
,'NONREC_TAX',DECODE(apext.SELF_ASSESSED_TAX_FLAG,'Y', NULL,apext.po_distribution_id)
, NULL) ap_po_distribution_id,
apext.aid_accounting_date gl_date,
-- Bug 5238282 : Prepayment application will be treated as standard invoice line for check funds
-- as there will be no data in ap_prepay_app_dists table.This table is populated during invoice
-- validation.
DECODE(p_bc_mode,'C'
,DECODE(apext.AID_LINE_TYPE_LOOKUP_CODE
,'PREPAY','STANDARD'
,apext.AID_LINE_TYPE_LOOKUP_CODE)
,DECODE(apext.SELF_ASSESSED_TAX_FLAG, 'Y','SELF_ASSESSED_TAX', apext.AID_LINE_TYPE_LOOKUP_CODE)) distribution_type,
DECODE(apext.AID_LINE_TYPE_LOOKUP_CODE
,'ITEM',DECODE(apext.bus_flow_po_entity_code
,'RELEASE',apext.bus_flow_po_doc_id,NULL)
,'ACCRUAL',DECODE(apext.bus_flow_po_entity_code
,'RELEASE',apext.bus_flow_po_doc_id,NULL)
,'NONREC_TAX',DECODE(apext.SELF_ASSESSED_TAX_FLAG,'Y',NULL,DECODE(apext.bus_flow_po_entity_code -- Bug 5523570
,'RELEASE',apext.bus_flow_po_doc_id,NULL))
, NULL) release_id,
-- R12 Funds Management Uptake: Currently this column is not available in AP extract
-- hence added below logic to fetch encumbrance_type_id for invoices
(SELECT encumbrance_type_id
FROM gl_encumbrance_types
WHERE encumbrance_type_KEY = 'Invoices') encumbrance_type_id,
--pa_utils2.get_gl_period_name(apd.accounting_date,apd.org_id)
apext.aid_period_name period_name,
apext.AID_PARENT_REVERSAL_ID parent_reversal_id -- Bug 5406690
/* FROM ap_extract_invoice_dtls_bc_v apext -- Bug 5500126 replaced the view with the below inline view for bug 14752984 */
FROM ( SELECT AID.bc_event_id event_id
,AID.accounting_date aid_accounting_date
,AID.award_id aid_award_id
,AID.dist_code_combination_id aid_dist_ccid
,AID.expenditure_type aid_expenditure_type
,AID.period_name aid_period_name
,AID.line_type_lookup_code aid_line_type_lookup_code
,AID.invoice_line_number aid_invoice_line_number
,AID.invoice_distribution_id aid_invoice_dist_id
,AID.parent_reversal_id aid_parent_reversal_id
,AID.po_distribution_id po_distribution_id
,AID.invoice_id bus_flow_inv_id
,Decode (IGC.CC_LINE, null, 'PO_DISTRIBUTIONS_ALL', IGC.CC_DIST_TYPE) Bus_Flow_PO_Dist_Type
,Decode (IGC.CC_LINE, null, Decode (pod.po_release_id, NULL, 'PURCHASE_ORDER', 'RELEASE') , IGC.CC_ENTITY_CODE) Bus_Flow_PO_Entity_Code
,Decode (IGC.CC_LINE, null, Decode (pod.po_release_id, NULL, pod.po_header_id,pod.po_release_id)
,IGC.cc_doc_id) Bus_Flow_PO_Doc_Id
,AID.project_id aid_project_id
,AID.task_id aid_task_id
,AID.Expenditure_Organization_ID aid_expenditure_org_id
,AID.quantity_variance aid_quantity_variance
,nvl(AID.base_quantity_variance, AID.quantity_variance) aid_base_quantity_variance
,AID.amount_variance aid_amount_variance
,nvl(AID.base_amount_variance, AID.amount_variance) aid_base_amount_variance
,NVL(AID.base_amount,AID.amount)
- NVL(AID.base_amount_variance,nvl(AID.amount_variance,0))
- NVL(AID.base_quantity_variance,NVL(AID.quantity_variance,0)) encumbrance_base_amount
,NVL(AID.amount,0) - NVL(AID.amount_variance,0)
- NVL(AID.quantity_variance,0) encumbrance_amount
,AID.CHARGE_APPLICABLE_TO_DIST_ID charge_applicable_to_dist_id
,'N' self_assessed_tax_flag
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID,
PO_DISTRIBUTIONS_ALL POD,
FINANCIALS_SYSTEM_PARAMS_ALL FSP,
IGC_CC_SLA_BFLOW_PRIOR_V IGC,
ap_system_parameters_all ASP
WHERE nvl(AID.posted_flag,'N') <> 'Y'
AND AID.po_distribution_id = POD.po_distribution_id(+)
AND AID.po_distribution_id = IGC.po_distribution_id(+)
AND AID.ORG_ID = FSP.ORG_ID
AND aid.set_of_books_id = asp.set_of_books_id
AND aid.org_id = asp.org_id
UNION
SELECT AID.bc_event_id event_id
,AID.accounting_date aid_accounting_date
,AID.award_id aid_award_id
,AID.dist_code_combination_id aid_dist_ccid
,AID.expenditure_type aid_expenditure_type
,AID.period_name aid_period_name
,AID.line_type_lookup_code aid_line_type_lookup_code
,AID.invoice_line_number aid_invoice_line_number
,AID.invoice_distribution_id aid_invoice_dist_id
,AID.parent_reversal_id aid_parent_reversal_id
,AID.po_distribution_id po_distribution_id
,AID.invoice_id bus_flow_inv_id
,Decode (IGC.CC_LINE, null, 'PO_DISTRIBUTIONS_ALL', IGC.CC_DIST_TYPE) Bus_Flow_PO_Dist_Type
,Decode (IGC.CC_LINE, null, Decode (pod.po_release_id, NULL, 'PURCHASE_ORDER', 'RELEASE') , IGC.CC_ENTITY_CODE) Bus_Flow_PO_Entity_Code
,Decode (IGC.CC_LINE, null, Decode (pod.po_release_id, NULL, pod.po_header_id,pod.po_release_id)
,IGC.cc_doc_id) Bus_Flow_PO_Doc_Id
,AID.project_id aid_project_id
,AID.task_id aid_task_id
,AID.Expenditure_Organization_ID aid_expenditure_org_id
,AID.quantity_variance aid_quantity_variance
,nvl(AID.base_quantity_variance, AID.quantity_variance) aid_base_quantity_variance
,AID.amount_variance aid_amount_variance
,nvl(AID.base_amount_variance, AID.amount_variance) aid_base_amount_variance
,NVL(AID.base_amount,AID.amount)
- NVL(AID.base_amount_variance,nvl(AID.amount_variance,0))
- NVL(AID.base_quantity_variance,NVL(AID.quantity_variance,0)) encumbrance_base_amount
,NVL(AID.amount,0) - NVL(AID.amount_variance,0)
- NVL(AID.quantity_variance,0) encumbrance_amount
,AID.CHARGE_APPLICABLE_TO_DIST_ID charge_applicable_to_dist_id
,AID.self_assessed_flag self_assessed_tax_flag
FROM AP_SELF_ASSESSED_TAX_DIST_ALL AID,
PO_DISTRIBUTIONS_ALL POD,
FINANCIALS_SYSTEM_PARAMS_ALL FSP,
IGC_CC_SLA_BFLOW_PRIOR_V IGC,
ap_system_parameters_all ASP
WHERE nvl(AID.posted_flag,'N') <> 'Y'
AND AID.po_distribution_id = POD.po_distribution_id(+)
AND AID.po_distribution_id = IGC.po_distribution_id(+)
AND AID.ORG_ID = FSP.ORG_ID
AND aid.set_of_books_id = asp.set_of_books_id
AND aid.org_id = asp.org_id
) apext ,
PSA_BC_XLA_EVENTS_GT PBXEG -- Added for bug 14752984
WHERE apext.aid_invoice_dist_id IN (select Column_Value from Table(g_ap_inv_dist_id))
/*AND apext.event_id in ( SELECT event_id FROM psa_bc_xla_events_gt) removed the existing inline view and added below join*/
AND apext.event_id = PBXEG.EVENT_ID -- Added for bug 14752984
AND (p_bc_mode ='C'
OR (apext.aid_line_type_lookup_code <> 'PREPAY' AND p_bc_mode <>'C')) -- Bug 5238282
AND NOT EXISTS ( --Bug 5490378 : Filter out Tax associated with prepay lines for reserve action
SELECT 1
FROM ap_invoice_distributions_all apd1
WHERE apd1.invoice_distribution_id = apext.charge_applicable_to_dist_id
AND apext.aid_line_type_lookup_code = 'NONREC_TAX'
AND apext.charge_applicable_to_dist_id IS NOT NULL
AND p_bc_mode <> 'C'
AND apd1.line_type_lookup_code = 'PREPAY')
AND NOT EXISTS ( /*Bug 13602288 :Self Assessed Tax Changes*/
SELECT 1
FROM ap_self_assessed_tax_dist_all apd2
WHERE apd2.invoice_distribution_id = apext.charge_applicable_to_dist_id
AND apext.aid_line_type_lookup_code = 'NONREC_TAX'
AND apext.charge_applicable_to_dist_id IS NOT NULL
AND p_bc_mode <> 'C'
AND apd2.line_type_lookup_code = 'PREPAY')
AND p_stdinvoice_exists = 'Y'
UNION ALL
SELECT /*+ leading(PSA_BC_XLA_EVENTS_GT) cardinality(PSA_BC_XLA_EVENTS_GT,1) */ NULL budget_version_id, -- Added for bug 14752984
NULL budget_line_id,
NULL budget_ccid,
AID.project_id project_id,
AID.task_id task_id,
AID.expenditure_type expenditure_type,
AID.expenditure_organization_id expenditure_organization_id,
-- The below hardcoded values for Je_source_name and Je_category_name columns
-- are based on SLA seed data for PO and REQ. These values are later
-- updated by pa_funds_check API to synch up with gl_bc_packets if different
'Payables' je_source_name,
'Purchase Invoices' je_category_name,
'AP' document_type,
AID.invoice_id document_header_id,
AID.INVOICE_LINE_NUMBER document_line_id,
APAD.Prepay_App_Distribution_ID document_distribution_id,
AID.invoice_distribution_id invoice_distribution_id,
'E' actual_flag,
NULL result_code,
'I' status_code,
APAD.AMOUNT entered_amount,
nvl(APAD.Base_amount, APAD.amount) accounted_amount,
'N' balance_posted_flag,
'T' funds_process_mode,
'N' burden_cost_flag,
-- For prepayment application reference columns will refer prepayment dist which
-- needs to be reversed.There wont be any fundscheck for prepayments matched to PO
'AP' reference1,
APPH.PREPAY_INVOICE_ID reference2,
AID.PREPAY_DISTRIBUTION_ID reference3,
NULL bc_event_id,
pa_funds_control_pkg.check_bdn_on_sep_item
(AID.project_id) burden_method_code,
NULL main_or_backing_code,
APPH.bc_event_id source_event_id,
AID.Dist_code_combination_id txn_ccid,
NULL parent_bc_packet_id,
pa_funds_control_utils.get_fnd_reqd_flag(AID.project_id
,'STD') fck_reqd_flag,
NULL ap_quantity_variance,
NULL ap_amount_variance,
NULL ap_base_quantity_variance,
NULL ap_base_amount_variance,
AID.po_distribution_id ap_po_distribution_id,
AID.ACCOUNTING_DATE gl_date,
APAD.prepay_dist_lookup_code distribution_type,
AIL.po_release_id release_id,
-- R12 Funds Management Uptake: Currently this column
-- is not available in AP extract
-- hence added below logic to fetch encumbrance_type_id for invoices
(SELECT encumbrance_type_id
FROM gl_encumbrance_types
WHERE encumbrance_type_KEY = 'Invoices') encumbrance_type_id,
AID.PERIOD_NAME period_name,
AID.parent_reversal_id parent_reversal_id -- Bug 5406690
FROM AP_PREPAY_HISTORY_ALL APPH,
PSA_BC_XLA_EVENTS_GT PBXEG, --Added for bug 14752984
AP_PREPAY_APP_DISTS APAD,
AP_INVOICE_LINES_ALL AIL,
AP_INVOICE_DISTRIBUTIONS_ALL AID
WHERE AID.bc_event_id = APPH.bc_Event_id
AND APPH.prepay_history_id = APAD.prepay_history_id
AND AID.invoice_line_number = AIL.line_number
AND AID.invoice_id = AIL.invoice_id
AND AID.line_type_lookup_code IN ( 'PREPAY' ,'NONREC_TAX' ) --Bug 5490378
/* and APPH.bc_Event_id IN ( SELECT event_id FROM psa_bc_xla_events_gt)removed the inline view for bug 14752984 and added below join */
and AID.invoice_distribution_id IN (select Column_Value from Table(g_ap_inv_dist_id))
AND APPH.bc_Event_id = PBXEG.event_id --Added for bug 14752984
AND p_prepay_exists = 'Y'
and aid.invoice_distribution_id = apad.prepay_app_distribution_id
/*Bug 13602288 :Self Assessed Tax Changes - We need to have SAT data to apply funds check*/
UNION ALL
SELECT NULL budget_version_id,
NULL budget_line_id,
NULL budget_ccid,
AID.project_id project_id,
AID.task_id task_id,
AID.expenditure_type expenditure_type,
AID.expenditure_organization_id expenditure_organization_id,
'Payables' je_source_name,
'Purchase Invoices' je_category_name,
'AP' document_type,
AID.invoice_id document_header_id,
AID.INVOICE_LINE_NUMBER document_line_id,
APAD.Prepay_App_Distribution_ID document_distribution_id,
AID.invoice_distribution_id invoice_distribution_id,
'E' actual_flag,
NULL result_code,
'I' status_code,
APAD.AMOUNT entered_amount,
nvl(APAD.Base_amount, APAD.amount) accounted_amount,
'N' balance_posted_flag,
'T' funds_process_mode,
'N' burden_cost_flag,
'AP' reference1,
APPH.PREPAY_INVOICE_ID reference2,
AID.PREPAY_DISTRIBUTION_ID reference3,
NULL bc_event_id,
pa_funds_control_pkg.check_bdn_on_sep_item
(AID.project_id) burden_method_code,
NULL main_or_backing_code,
APPH.bc_event_id source_event_id,
AID.Dist_code_combination_id txn_ccid,
NULL parent_bc_packet_id,
pa_funds_control_utils.get_fnd_reqd_flag(AID.project_id
,'STD') fck_reqd_flag,
NULL ap_quantity_variance,
NULL ap_amount_variance,
NULL ap_base_quantity_variance,
NULL ap_base_amount_variance,
AID.po_distribution_id ap_po_distribution_id,
AID.ACCOUNTING_DATE gl_date,
APAD.prepay_dist_lookup_code distribution_type,
AIL.po_release_id release_id,
(SELECT encumbrance_type_id
FROM gl_encumbrance_types
WHERE encumbrance_type_KEY = 'Invoices') encumbrance_type_id,
AID.PERIOD_NAME period_name,
AID.parent_reversal_id parent_reversal_id
FROM AP_PREPAY_HISTORY_ALL APPH,
AP_PREPAY_APP_DISTS APAD,
AP_INVOICE_LINES_ALL AIL,
AP_SELF_ASSESSED_TAX_DIST_ALL AID
WHERE AID.bc_event_id = APPH.bc_Event_id
AND APPH.prepay_history_id = APAD.prepay_history_id
AND AID.invoice_line_number = AIL.line_number
AND AID.invoice_id = AIL.invoice_id
AND AID.line_type_lookup_code IN ( 'PREPAY' ,'NONREC_TAX' )
and APPH.bc_Event_id IN ( SELECT event_id FROM psa_bc_xla_events_gt)
and AID.invoice_distribution_id IN (select Column_Value from Table(g_ap_inv_dist_id))
AND p_prepay_exists = 'Y'
and aid.invoice_distribution_id = apad.prepay_app_distribution_id;
SELECT apd.set_of_books_id,
apd.expenditure_item_date,
apd.org_id,
apinv.vendor_id
FROM ap_invoice_distributions_all apd,
ap_invoices_all apinv
WHERE apd.invoice_distribution_id = p_inv_dist_id
AND apinv.invoice_id = apd.invoice_id
UNION /*Bug 13602288 :Self Assessed Tax Changes*/
SELECT apd.set_of_books_id,
apd.expenditure_item_date,
apd.org_id,
apinv.vendor_id
FROM ap_self_assessed_tax_dist_all apd,
ap_invoices_all apinv
WHERE apd.invoice_distribution_id = p_inv_dist_id
AND apinv.invoice_id = apd.invoice_id;
SELECT pov.vendor_id,
porl.org_id
FROM po_vendors pov,
po_requisition_lines_all porl
WHERE pov.vendor_name (+) = porl.suggested_vendor_name
AND porl.REQUISITION_LINE_ID =p_req_line_id;
SELECT poh.vendor_id,
poh.org_id
FROM po_headers_all poh
WHERE poh.po_header_id = p_header_id;
SELECT count(*)
FROM pa_bc_packets
WHERE packet_id = p_packet_id
AND (NVL(status_code,'I') NOT IN ('F','T','R')
AND SUBSTR (NVL(result_code,'P'),1,1) <> 'F');
SELECT DECODE(ORIG.distribution_type ,'SCHEDULED','REL'
,'BLANKET','REL'
,'PO'),
DECODE(ORIG.distribution_type ,'SCHEDULED',ORIG.po_release_id
,'BLANKET',ORIG.po_release_id
,ORIG.header_id)
FROM PO_BC_DISTRIBUTIONS ORIG
WHERE ORIG.sequence_number= p_req_origin_seq_num
AND ORIG.ae_event_id = p_req_event_id;
select pa_bc_packets_s.nextval
into g_tab_bc_packet_id(i)
from dual;
SELECT gl_bc_packets_s.nextval
INTO l_packet_id
FROM dual;
pa_funds_control_pkg.log_message(p_msg_token1 => 'Updated I status records created in last run to T status '||SQL%ROWCOUNT);
pa_funds_control_pkg.log_message(p_msg_token1 => 'Calling update_cwk_pkt_lines ');
-- To update CWK related columns of pa_bc_packets PO records
update_cwk_pkt_lines (p_calling_module => 'GL',
p_packet_id => l_packet_id);
pa_funds_control_pkg.log_message(p_msg_token1 => 'In partial mode returning F status as all records have failed insert validation');
pa_funds_control_pkg.log_message(p_msg_token1 => 'In partial mode returning F status as all records have failed insert validation');
PA_FUNDS_CONTROL_PKG.result_status_code_update
( p_status_code => 'T',
p_result_code => 'F09',
p_res_result_code => 'F09',
p_res_grp_result_code => 'F09',
p_task_result_code => 'F09',
p_top_task_result_code => 'F09',
p_project_result_code => 'F09',
p_proj_acct_result_code => 'F09',
p_packet_id => l_packet_id
);
l_update_login NUMBER := FND_GLOBAL.login_id;
If l_update_login is null then
l_update_login := -1;
INSERT INTO PA_BC_PACKETS (
request_id,
program_id,
program_application_id,
program_update_date,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
------ main columns-----------
packet_id,
bc_packet_id,
budget_version_id,
project_id,
task_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
set_of_books_id,
je_source_name,
je_category_name,
document_type,
document_header_id,
document_distribution_id,
actual_flag,
result_code,
status_code,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
balance_posted_flag,
funds_process_mode,
txn_ccid,
burden_cost_flag,
org_id,
parent_bc_packet_id
,document_line_id
,reference1
,reference2
,reference3
-- R12 Funds management Uptake : Newly added columns
,bc_event_id
,vendor_id
,main_or_backing_code
,burden_method_code
,budget_line_id
,source_event_id
,ext_bdgt_flag
,gl_date
,period_name
,document_distribution_type
,DOCUMENT_HEADER_ID_2
,encumbrance_type_id
,proj_encumbrance_type_id
)
SELECT
l_request_id,
l_program_id,
l_program_application_id,
sysdate,
sysdate,
l_update_login,
l_update_login,
sysdate,
l_update_login,
p_packet_id,
g_tab_bc_packet_id(i), -- Bug 5406690
g_tab_budget_version_id(i),
g_tab_project_id(i),
g_tab_task_id(i),
g_tab_exp_type(i),
g_tab_exp_org_id(i),
g_tab_exp_item_date(i),
g_tab_set_of_books_id(i),
g_tab_je_source_name(i),
g_tab_je_category_name(i),
g_tab_doc_type(i),
g_tab_doc_header_id(i),
g_tab_doc_distribution_id(i),
g_tab_actual_flag(i),
g_tab_result_code(i),
g_tab_status_code(i),
NVL(pa_currency.round_trans_currency_amt(g_tab_entered_dr(i),g_acct_currency_code),0),
NVL(pa_currency.round_trans_currency_amt(g_tab_entered_cr(i),g_acct_currency_code),0),
NVL(pa_currency.round_trans_currency_amt(g_tab_accounted_dr(i),g_acct_currency_code),0),
NVL(pa_currency.round_trans_currency_amt(g_tab_accounted_cr(i),g_acct_currency_code),0),
g_tab_balance_posted_flag(i),
g_tab_funds_process_mode(i),
g_tab_trxn_ccid(i),
g_tab_burden_cost_flag(i),
g_tab_org_id(i),
g_tab_p_bc_packet_id(i),
g_tab_doc_line_id(i),
g_tab_pkt_reference1(i),
g_tab_pkt_reference2(i),
g_tab_pkt_reference3(i),
g_tab_event_id(i),
g_tab_vendor_id(i),
g_tab_main_or_backing_code(i),
g_tab_burden_method_code(i),
g_tab_budget_line_id(i),
g_tab_source_event_id(i),
PA_FUNDS_CONTROL_UTILS.get_bdgt_link(g_tab_project_id(i),'STD' ),
g_tab_gl_date(i),
g_tab_period_name(i),
g_tab_distribution_type(i),
DECODE(g_tab_doc_type(i),'PO',g_tab_po_release_id(i),NULL),
g_tab_enc_type_id(i),
PA_FUNDS_CONTROL_UTILS.get_encum_type_id(g_tab_project_id(i),'STD')
FROM
dual
WHERE g_tab_fck_reqd_flag(i) in ('R','Y')
-- fck_reqd_flag R - year end rollover
AND ( nvl(g_tab_status_code(i),'P') <> 'V'
and nvl(g_tab_result_code(i),'P') <> 'P113'
);
Update pa_bc_packets
set status_code = DECODE(status_code,'F',status_code,'R',status_code,'T',status_code,DECODE(p_bc_mode,'C','F','R')),
res_result_code = DECODE(substr(res_result_code,1,1),'F',res_result_code,result_code),
res_grp_result_code = DECODE(substr(res_grp_result_code,1,1),'F',res_grp_result_code,result_code),
task_result_code = DECODE(substr(task_result_code,1,1),'F',task_result_code,result_code),
top_task_result_code = DECODE(substr(top_task_result_code,1,1),'F',top_task_result_code,result_code),
project_result_code = DECODE(substr(project_result_code,1,1),'F',project_result_code,result_code),
project_acct_result_code = DECODE(substr(project_acct_result_code,1,1),'F',project_acct_result_code,result_code)
Where packet_id = p_packet_id
AND SUBSTR(result_code,1,1) = 'F'
ANd status_code in ( 'I' ,'R'); -- Bug 12597774 Added status_code R
pa_funds_control_pkg.log_message(p_msg_token1 => 'NUmber of records updated to Failed status ='||SQL%ROWCOUNT);
Update pa_bc_packets set gl_row_number = g_tab_rowid(i)
where bc_packet_id = g_tab_bc_packet_id (i); END IF;
PROCEDURE update_cwk_pkt_lines(p_calling_module IN varchar2 ,
p_packet_id IN NUMBER) IS
l_counter NUMBER := 0;
select distinct pbc.document_header_id,
pbc.document_line_id,
org_id
from pa_bc_packets pbc
where packet_id = p_packet_id
and document_type = 'PO'
and (nvl(accounted_dr,0) - nvl(accounted_cr,0)) < 0
and not exists ( select 1
from pa_bc_packets
where packet_id = p_packet_id
and document_type <> 'PO') ;
update pa_bc_packets
set entered_cr = p_entered_cr_tab(i),
accounted_cr = p_accounted_cr_tab(i),
entered_dr = 0,
accounted_dr = 0
where packet_id = p_packet_id
and bc_packet_id = p_bc_packet_id_tab(i) ;
pa_funds_control_pkg.log_message(p_msg_token1 => 'update_cwk_pkt_lines : Start ');
** BC packet record is updated with the amount ( whichever is less credit amount or
** summary balance ) for a project, task and a po line.
** ***************************************************************************************/
g_cwk_po_unreserve := 'N' ;
g_project_id_tab.delete ;
g_task_id_tab.delete ;
l_raw_amount_tab.delete ;
l_amt_balance_tab.delete ;
g_doc_line_id_tab.delete ;
g_bdamt_balance_tab.delete ;
g_burden_type_tab.delete ;
select distinct pbc.project_id,
pbc.task_id,
(nvl(pbc.comm_tot_raw_amt,0) - nvl(pbc.comm_raw_amt_relieved,0)) amount,
(nvl(pbc.comm_tot_bd_amt,0) - nvl(pbc.comm_bd_amt_relieved,0) ) bd_amount,
pbc.document_line_id,
decode(NVL(ppt.burden_cost_flag, 'N'),
'Y',
decode(NVL(ppt.burden_amt_display_method,'S'), 'S','SAME','D','DIFFERENT'),'NONE')
bulk collect into g_project_id_tab,
g_task_id_tab,
l_amt_balance_tab,
g_bdamt_balance_tab,
g_doc_line_id_tab,
g_burden_type_tab
from pa_bc_packets pbc ,
pa_projects_all pp,
pa_project_types ppt
where pbc.document_type = 'PO'
and pbc.summary_record_flag = 'Y'
and pbc.document_line_id = c_rate_po.document_line_id
and pbc.document_header_id = c_rate_po.document_header_id
and pbc.status_code in ('A', 'C')
and pbc.parent_bc_packet_id is NULL
and pbc.project_id = pp.project_id
and pp.project_type = ppt.project_type
and (pbc.project_id, task_id) in ( select distinct project_id, task_id
from pa_bc_packets
where packet_id = p_packet_id
and document_header_id = c_rate_po.document_header_id
and document_line_id = c_rate_po.document_line_id )
and pbc.packet_id < p_packet_id ;
select distinct pbc.project_id,
pbc.task_id,
(nvl(pbc.comm_tot_raw_amt,0) - nvl(pbc.comm_raw_amt_relieved,0)) amount,
(nvl(pbc.comm_tot_bd_amt,0) - nvl(pbc.comm_bd_amt_relieved,0) ) bd_amount,
pbc.document_line_id,
decode(NVL(ppt.burden_cost_flag, 'N'),
'Y',
decode(NVL(ppt.burden_amt_display_method,'S'), 'S','SAME','D','DIFFERENT'),'NONE')
bulk collect into g_project_id_tab,
g_task_id_tab,
l_amt_balance_tab,
g_bdamt_balance_tab,
g_doc_line_id_tab,
g_burden_type_tab
from pa_bc_commitments pbc,
pa_projects_all pp,
pa_project_types ppt
where pbc.document_type = 'PO'
and pbc.summary_record_flag = 'Y'
--and pbc.burden_cost_flag = 'N'
and pbc.parent_bc_packet_id is NULL
and pbc.document_line_id = c_rate_po.document_line_id
and pbc.document_header_id = c_rate_po.document_header_id
and pbc.project_id = pp.project_id
and pp.project_type = ppt.project_type
and (pbc.project_id, pbc.task_id) in ( select distinct project_id, task_id
from pa_bc_packets
where packet_id = p_packet_id
and document_header_id = c_rate_po.document_header_id
and document_line_id = c_rate_po.document_line_id )
and pbc.packet_id < p_packet_id ;
l_bc_packet_id_tab.delete ;
l_entered_dr_tab.delete ;
l_entered_cr_tab.delete ;
l_accounted_dr_tab.delete ;
l_accounted_cr_tab.delete ;
select bc_packet_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr
bulk collect into l_bc_packet_id_tab,
l_entered_dr_tab,
l_entered_cr_tab,
l_accounted_dr_tab,
l_accounted_cr_tab
from pa_bc_packets
where packet_id = p_packet_id
and document_line_id = g_doc_line_id_tab(line_indx)
and document_header_id = c_rate_po.document_header_id
and project_id = g_project_id_tab(line_indx)
and task_id = g_task_id_tab(line_indx) ;
** 3703180 : Update the bc packet with the correct credit amount.
*/
rate_po_unreserve ( p_packet_id,
l_bc_packet_id_tab,
l_entered_cr_tab,
l_accounted_cr_tab ) ;
pa_funds_control_pkg.log_message(p_msg_token1 => 'update_cwk_pkt_lines : End ');
END update_cwk_pkt_lines;
l_update_login NUMBER := NVL(FND_GLOBAL.login_id,-1);
/* This check is not required as this is done even before inserting the record into pa_bc_packets
* during distribute process. Having this check is redudant
If p_calling_module = 'DISTCWKST' then
pa_funds_control_pkg.log_message(p_msg_token1 => 'Calling checkCWKbdExp Api to check burden cost codes');
/* This Query insert records into pa_bc_packets
* for the projects which is of burden on same
* expenditure item
*/
INSERT INTO pa_bc_packets
( ---- who columns------
request_id,
program_id,
program_application_id,
program_update_date,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
------ main columns-----------
packet_id,
bc_packet_id,
budget_version_id,
project_id,
task_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
set_of_books_id,
je_source_name,
je_category_name,
document_type,
document_header_id,
document_distribution_id,
actual_flag,
period_name,
period_year,
period_num,
result_code,
status_code,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
gl_row_number, --gl_row_bc_packet_row_id
balance_posted_flag,
funds_process_mode,
txn_ccid,
parent_bc_packet_id,
encumbrance_type_id,
burden_cost_flag,
org_id,
gl_date,
pa_date,
document_line_id,
compiled_multiplier,
reference1,
reference2,
reference3,
exp_item_id
)
SELECT
l_request_id,
l_program_id,
l_program_application_id,
sysdate,
sysdate,
l_userid,
l_userid,
sysdate,
l_update_login,
------ main columns-----------
pbc.packet_id,
pa_bc_packets_s.nextval,
pbc.budget_version_id,
pbc.project_id,
pbc.task_id,
pbc.expenditure_type,
pbc.expenditure_organization_id,
pbc.expenditure_item_date,
pbc.set_of_books_id,
pbc.je_source_name,
pbc.je_category_name,
pbc.document_type,
pbc.document_header_id,
pbc.document_distribution_id,
pbc.actual_flag,
--decode(pbc.document_type,'AP',(
/** pagl period enhancement changes instead of passing pa date
pass transaction date to derive the period name
pa_funds_control_pkg1.get_period_name(pa_utils2.get_pa_date
(pbc.expenditure_item_date,NULL,pbc.org_id),pbc.set_of_books_id),
-- ),pbc.period_name),
**/
/** Bug fix:2905892 As per discussions with Barbara , Dinakar, Prithi
* for Transaction import process the period name is to derived
* based on the orginal raw line for the burden transactions
* so reverting back to changes made earlier
*pa_funds_control_pkg1.get_period_name
* (pbc.expenditure_item_date,pbc.set_of_books_id),**/
pbc.period_name,
/** End of bug fix: 2905892 ***/
pbc.period_year,
pbc.period_num,
pbc.result_code,
pbc.status_code,
pa_currency.round_trans_currency_amt(
DECODE ( NVL ( pbc.entered_dr, 0 ), 0, 0,
(( NVL (pbc.entered_dr ,0) *
NVL (pa_funds_control_utils.get_fc_compiled_multiplier
( pbc.expenditure_organization_id,
pbc.task_id,
pbc.expenditure_item_date,
'C',
pbc.expenditure_type
), 0)))),g_acct_currency_code),
pa_currency.round_trans_currency_amt(
DECODE ( NVL ( pbc.entered_cr, 0 ), 0, 0,
(( NVL (pbc.entered_cr ,0) *
NVL (pa_funds_control_utils.get_fc_compiled_multiplier
( pbc.expenditure_organization_id,
pbc.task_id,
pbc.expenditure_item_date,
'C',
pbc.expenditure_type
), 0)))),g_acct_currency_code),
pa_currency.round_trans_currency_amt(
DECODE ( NVL ( pbc.accounted_dr, 0 ), 0, 0,
(( NVL (pbc.accounted_dr ,0) *
NVL (pa_funds_control_utils.get_fc_compiled_multiplier
( pbc.expenditure_organization_id,
pbc.task_id,
pbc.expenditure_item_date,
'C',
pbc.expenditure_type
), 0)))),g_acct_currency_code),
pa_currency.round_trans_currency_amt(
DECODE ( NVL ( pbc.accounted_cr, 0 ), 0, 0,
(( NVL (pbc.accounted_cr ,0) *
NVL (pa_funds_control_utils.get_fc_compiled_multiplier
( pbc.expenditure_organization_id,
pbc.task_id,
pbc.expenditure_item_date,
'C',
pbc.expenditure_type
), 0)))),g_acct_currency_code),
NULL, --gl_row_bc_packet_row_id
pbc.balance_posted_flag,
pbc.funds_process_mode,
pbc.txn_ccid,
pbc.bc_packet_id,
pbc.encumbrance_type_id,
'O',
pbc.org_id,
pbc.gl_date,
pbc.pa_date,
pbc.document_line_id,
pa_funds_control_utils.get_fc_compiled_multiplier
( pbc.expenditure_organization_id,
pbc.task_id,
pbc.expenditure_item_date,
'C',
pbc.expenditure_type
)
,pbc.reference1
,pbc.reference2
,pbc.reference3
,pbc.exp_item_id
FROM pa_bc_packets pbc
WHERE pbc.packet_id = p_packet_id
AND pbc.parent_bc_packet_id = -1
AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F'
AND pa_funds_control_pkg.check_bdn_on_sep_item
(pbc.project_id ) = 'S'
AND NVL (pa_funds_control_utils.get_fc_compiled_multiplier
( pbc.expenditure_organization_id,
pbc.task_id,
pbc.expenditure_item_date,
'C',
pbc.expenditure_type
), 0) <> 0;
'Num of records inserted ='||sql%rowcount);
/* This Query insert records into pa_bc_packets
* for the projects which is of burden on different
* expenditure item
*/
INSERT INTO pa_bc_packets
( ---- who columns------
request_id,
program_id,
program_application_id,
program_update_date,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
------ main columns-----------
packet_id,
bc_packet_id,
budget_version_id,
project_id,
task_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
set_of_books_id,
je_source_name,
je_category_name,
document_type,
document_header_id,
document_distribution_id,
actual_flag,
period_name,
period_year,
period_num,
result_code,
status_code,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
gl_row_number, --gl_row_bc_packet_row_id
balance_posted_flag,
funds_process_mode,
txn_ccid,
parent_bc_packet_id,
encumbrance_type_id,
burden_cost_flag,
org_id,
gl_date,
pa_date,
document_line_id,
compiled_multiplier,
reference1,
reference2,
reference3,
exp_item_id
)
SELECT
l_request_id,
l_program_id,
l_program_application_id,
sysdate,
sysdate,
l_userid,
l_userid,
sysdate,
l_update_login,
------ main columns-----------
pbc.packet_id,
pa_bc_packets_s.nextval,
pbc.budget_version_id,
pbc.project_id,
pbc.task_id,
et.expenditure_type,
pbc.expenditure_organization_id,
pbc.expenditure_item_date,
pbc.set_of_books_id,
pbc.je_source_name,
pbc.je_category_name,
pbc.document_type,
pbc.document_header_id,
pbc.document_distribution_id,
pbc.actual_flag,
/** pagl period enhancement changes instead of passing pa date
pass transaction date to derive the period name
pa_funds_control_pkg1.get_period_name(pa_utils2.get_pa_date
(pbc.expenditure_item_date,NULL,pbc.org_id),pbc.set_of_books_id),
**/
/** Bug fix:2905892 As per discussions with Barbara , Dinakar, Prithi
* for Transaction import process the period name is to derived
* based on the orginal raw line for the burden transactions
* so reverting back to changes made earlier
* pa_funds_control_pkg1.get_period_name
* (pbc.expenditure_item_date,pbc.set_of_books_id), --pbc.period_name, **/
pbc.period_name,
/** End of bug fix: 2905892 **/
pbc.period_year,
pbc.period_num,
pbc.result_code,
pbc.status_code,
pa_currency.round_trans_currency_amt(
decode(nvl(pbc.entered_dr,0),0,0,(nvl(pbc.entered_dr,0)*
cm.compiled_multiplier)),g_acct_currency_code),
pa_currency.round_trans_currency_amt(
decode(nvl(pbc.entered_cr,0),0,0,(nvl(pbc.entered_cr,0)*
cm.compiled_multiplier)),g_acct_currency_code),
pa_currency.round_trans_currency_amt(
decode(nvl(pbc.accounted_dr,0),0,0,(nvl(pbc.accounted_dr,0)*
cm.compiled_multiplier)),g_acct_currency_code),
pa_currency.round_trans_currency_amt(
decode(nvl(pbc.accounted_cr,0),0,0,(nvl(pbc.accounted_cr,0)*
cm.compiled_multiplier)),g_acct_currency_code),
NULL, --gl_row_bc_packet_row_id
pbc.balance_posted_flag,
pbc.funds_process_mode,
pbc.txn_ccid,
pbc.bc_packet_id,
pbc.encumbrance_type_id,
'O',
pbc.org_id,
pbc.gl_date,
pbc.pa_date,
pbc.document_line_id,
cm.compiled_multiplier,
pbc.reference1,
pbc.reference2,
pbc.reference3,
pbc.exp_item_id
FROM
pa_ind_rate_sch_revisions irsr,
pa_cost_bases cb,
pa_expenditure_types et,
pa_ind_cost_codes icc,
pa_cost_base_exp_types cbet,
pa_ind_rate_schedules_all_bg irs,
pa_ind_compiled_sets ics,
pa_compiled_multipliers cm,
pa_bc_packets pbc
WHERE irsr.cost_plus_structure = cbet.cost_plus_structure
AND cb.cost_base = cbet.cost_base
AND cb.cost_base_type = cbet.cost_base_type
AND et.expenditure_type = icc.expenditure_type
AND icc.ind_cost_code = cm.ind_cost_code
AND cbet.cost_base = cm.cost_base
AND cbet.cost_base_type = 'INDIRECT COST'
AND cbet.expenditure_type = pbc.expenditure_type
AND irs.ind_rate_sch_id = irsr.ind_rate_sch_id
AND ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
AND ics.organization_id = pbc.expenditure_organization_id
AND ics.ind_compiled_set_id =
pa_funds_control_utils.get_fc_compiled_set_id
(pbc.task_id,
pbc.expenditure_item_date,
pbc.expenditure_organization_id,
'C'
, 'COMPILE_SET_ID'
,pbc.expenditure_type) -- Added for burdening changes
AND ics.cost_base = cb.cost_base -- Added for burdening changes
AND cm.ind_compiled_set_id = ics.ind_compiled_set_id
AND cm.compiled_multiplier <> 0
AND pbc.packet_id = p_packet_id
AND pbc.parent_bc_packet_id = -1
AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F'
AND pa_funds_control_pkg.check_bdn_on_sep_item
(pbc.project_id ) = 'D';
'Num of records inserted ='||sql%rowcount);
PROCEDURE update_cwk_po_burden(p_packet_id NUMBER ) is
PRAGMA AUTONOMOUS_TRANSACTION;
pa_funds_control_pkg.log_message(p_msg_token1 => 'update_cwk_po_burden : Start ');
l_bc_packet_id_tab.delete ;
l_entered_dr_tab.delete ;
l_entered_cr_tab.delete ;
l_accounted_dr_tab.delete ;
l_accounted_cr_tab.delete ;
select bc_packet_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr
bulk collect into l_bc_packet_id_tab,
l_entered_dr_tab,
l_entered_cr_tab,
l_accounted_dr_tab,
l_accounted_cr_tab
from pa_bc_packets
where packet_id = p_packet_id
and document_line_id = g_doc_line_id_tab(line_indx)
and project_id = g_project_id_tab(line_indx)
and task_id = g_task_id_tab(line_indx)
and parent_bc_packet_id is not NULL
order by abs(nvl(accounted_dr,0) - nvl(accounted_cr,0)) desc;
-- Update the calculated burden cost to pa bc packets.
--
FORALL i IN l_bc_packet_id_tab.first .. l_bc_packet_id_tab.last
update pa_bc_packets
set entered_cr = l_entered_cr_tab(i),
accounted_cr = l_accounted_cr_tab(i),
entered_dr = 0,
accounted_dr = 0
where packet_id = p_packet_id
and bc_packet_id = l_bc_packet_id_tab(i) ;
l_exp_type_tab.delete ;
select distinct pbc.expenditure_type
bulk collect into l_exp_type_tab
from pa_bc_packets pbc
where packet_id = p_packet_id
and document_type = 'PO'
and parent_bc_packet_id is not NULL
and project_id = g_project_id_tab(line_indx)
and task_id = g_task_id_tab(line_indx)
and document_line_id = g_doc_line_id_tab(line_indx) ;
g_bdamt_balance_tab.delete ;
select (nvl(comm_tot_bd_amt,0) - nvl(comm_bd_amt_relieved,0) ) bd_amount
bulk collect into g_bdamt_balance_tab
from pa_bc_packets
where document_type = 'PO'
and summary_record_flag = 'Y'
and document_line_id = g_doc_line_id_tab(line_indx)
and status_code in ('A', 'C')
--and burden_cost_flag = 'O'
and parent_bc_packet_id is not NULL
and project_id = g_project_id_tab(line_indx)
and task_id = g_task_id_tab(line_indx)
and expenditure_type = l_exp_type_tab(indx)
and packet_id < p_packet_id ;
select (nvl(comm_tot_bd_amt,0) - nvl(comm_bd_amt_relieved,0) ) bd_amount
bulk collect into g_bdamt_balance_tab
from pa_bc_commitments
where document_type = 'PO'
and summary_record_flag = 'Y'
-- and burden_cost_flag = 'O'
and parent_bc_packet_id is not NULL
and document_line_id = g_doc_line_id_tab(line_indx)
and project_id = g_project_id_tab(line_indx)
and task_id = g_task_id_tab(line_indx)
and expenditure_type = l_exp_type_tab(indx)
and packet_id < p_packet_id ;
l_bc_packet_id_tab.delete ;
l_entered_dr_tab.delete ;
l_entered_cr_tab.delete ;
l_accounted_dr_tab.delete ;
l_accounted_cr_tab.delete ;
select bc_packet_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr
bulk collect into l_bc_packet_id_tab,
l_entered_dr_tab,
l_entered_cr_tab,
l_accounted_dr_tab,
l_accounted_cr_tab
from pa_bc_packets
where packet_id = p_packet_id
and document_line_id = g_doc_line_id_tab(line_indx)
and project_id = g_project_id_tab(line_indx)
and task_id = g_task_id_tab(line_indx)
and parent_bc_packet_id is not NULL
and expenditure_type = l_exp_type_tab(indx)
order by abs(nvl(entered_dr,0) - nvl(entered_cr,0)) desc;
-- Update the burden cost to pa bc packets.
--
FORALL i IN l_bc_packet_id_tab.first .. l_bc_packet_id_tab.last
update pa_bc_packets
set entered_cr = l_entered_cr_tab(i),
accounted_cr = l_accounted_cr_tab(i),
entered_dr = 0,
accounted_dr = 0
where packet_id = p_packet_id
and bc_packet_id = l_bc_packet_id_tab(i) ;
pa_funds_control_pkg.log_message(p_msg_token1 => 'update_cwk_po_burden : End ');
END update_cwk_po_burden ;
l_update_login NUMBER := NVL(FND_GLOBAL.login_id,-1);
SELECT parent_reversal_id
FROM ap_invoice_distributions_all ap
WHERE ap.invoice_distribution_id = l_doc_distribution_id
AND parent_reversal_id IS NOT NULL; */
l_tab_multiplier.DELETE ;
l_tab_icc_exp_type.DELETE ;
select document_header_id ,
document_distribution_id ,
(nvl(entered_dr,0) - NVL(entered_cr,0)) amount
into l_doc_header_id,
l_doc_distribution_id,
l_amount
from pa_bc_packets
where packet_id = p_packet_id
and bc_packet_id = p_bc_packet_id ;
select NVL(max(pbc.packet_id) ,0)
into l_max_packet_id
from pa_bc_commitments pbc
where pbc.document_header_id = l_doc_header_id
and pbc.document_distribution_id = l_doc_distribution_id
and pbc.document_type = p_doc_type
and pbc.packet_id <> p_packet_id ;
select max(packet_id)
into l_max_packet_id_b
from pa_bc_packets gbc1
where packet_id <> p_packet_id
and packet_id > NVL(l_max_packet_id,0)
and document_type = p_doc_type
and document_header_id = l_doc_header_id
and document_distribution_id = l_doc_distribution_id
and status_code in ( 'A','C') ;
select compiled_multiplier ,
expenditure_type
bulk collect into l_tab_multiplier,
l_tab_icc_exp_type
from pa_bc_commitments
where packet_id = l_max_packet_id
and document_header_id = l_doc_header_id
and document_distribution_id = l_doc_distribution_id
and document_type = p_doc_type
-- and parent_bc_packet_id is not NULL ;
and parent_bc_packet_id IN ( SELECT bc1.bc_packet_id -- SQL to fetch single raw record
FROM pa_bc_commitments bc1
WHERE bc1.packet_id = l_max_packet_id
AND bc1.document_header_id = l_doc_header_id
AND bc1.document_distribution_id = l_doc_distribution_id
AND bc1.document_type = p_doc_type
AND bc1.parent_bc_packet_id IS NULL
AND ROWNUM = 1 );
select compiled_multiplier ,
expenditure_type
bulk collect into l_tab_multiplier,
l_tab_icc_exp_type
from pa_bc_packets
where packet_id = l_max_packet_id_b
and document_header_id = l_doc_header_id
and document_distribution_id = l_doc_distribution_id
and document_type = p_doc_type
-- and parent_bc_packet_id is not NULL ;
and parent_bc_packet_id IN ( SELECT bc1.bc_packet_id -- SQL to fetch single raw record
FROM pa_bc_packets bc1
WHERE bc1.packet_id = l_max_packet_id_b
AND bc1.document_header_id = l_doc_header_id
AND bc1.document_distribution_id = l_doc_distribution_id
AND bc1.document_type = p_doc_type
AND bc1.parent_bc_packet_id IS NULL
AND ROWNUM = 1 );
SELECT NVL (pa_funds_control_utils.get_fc_compiled_multiplier
( pbc.expenditure_organization_id,
pbc.task_id,
pbc.expenditure_item_date,
'C',
pbc.expenditure_type
), 0),
expenditure_type
BULK COLLECT into l_tab_multiplier ,
l_tab_icc_exp_type
FROM pa_bc_packets pbc
WHERE pbc.packet_id = p_packet_id
AND pbc.bc_packet_id = p_bc_packet_id
AND pbc.document_type = p_doc_type
AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F'
AND NVL (pa_funds_control_utils.get_fc_compiled_multiplier
( pbc.expenditure_organization_id,
pbc.task_id,
pbc.expenditure_item_date,
'C',
pbc.expenditure_type
), 0) <> 0;
select et.expenditure_type,
cm.compiled_multiplier
bulk collect into l_tab_icc_exp_type,
l_tab_multiplier
FROM
pa_ind_rate_sch_revisions irsr,
pa_cost_bases cb,
pa_expenditure_types et,
pa_ind_cost_codes icc,
pa_cost_base_exp_types cbet,
pa_ind_rate_schedules_all_bg irs,
pa_ind_compiled_sets ics,
pa_compiled_multipliers cm,
pa_bc_packets pbc
WHERE irsr.cost_plus_structure = cbet.cost_plus_structure
AND cb.cost_base = cbet.cost_base
AND cb.cost_base_type = cbet.cost_base_type
AND et.expenditure_type = icc.expenditure_type
AND icc.ind_cost_code = cm.ind_cost_code
AND cbet.cost_base = cm.cost_base
AND cbet.cost_base_type = 'INDIRECT COST'
AND cbet.expenditure_type = pbc.expenditure_type
AND irs.ind_rate_sch_id = irsr.ind_rate_sch_id
AND ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
AND ics.organization_id = pbc.expenditure_organization_id
AND ics.ind_compiled_set_id =
pa_funds_control_utils.get_fc_compiled_set_id
(pbc.task_id,
pbc.expenditure_item_date,
pbc.expenditure_organization_id,
'C'
, 'COMPILE_SET_ID'
,pbc.expenditure_type) -- Added for burdening changes
AND ics.cost_base = cb.cost_base -- Added for burdening changes
AND cm.ind_compiled_set_id = ics.ind_compiled_set_id
AND cm.compiled_multiplier <> 0
AND pbc.packet_id = p_packet_id
AND pbc.bc_packet_id = p_bc_packet_id
AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F';
INSERT INTO pa_bc_packets
( ---- who columns------
request_id,
program_id,
program_application_id,
program_update_date,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
------ main columns-----------
packet_id,
bc_packet_id,
budget_version_id,
project_id,
task_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
set_of_books_id,
je_source_name,
je_category_name,
document_type,
document_header_id,
document_distribution_id,
actual_flag,
period_name,
period_year,
period_num,
result_code,
status_code,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
gl_row_number, --gl_row_bc_packet_row_id
balance_posted_flag,
funds_process_mode,
txn_ccid,
parent_bc_packet_id,
encumbrance_type_id,
burden_cost_flag,
org_id,
gl_date,
pa_date,
document_line_id,
compiled_multiplier,
reference1,
reference2,
reference3,
exp_item_id,
bc_event_id,
budget_line_id,
vendor_id,
main_or_backing_code,
burden_method_code,
source_event_id,
ext_bdgt_flag,
document_distribution_type,
document_header_id_2,
proj_encumbrance_type_id
)
SELECT
l_request_id,
l_program_id,
l_program_application_id,
sysdate,
sysdate,
l_userid,
l_userid,
sysdate,
l_update_login,
------ main columns-----------
pbc.packet_id,
pa_bc_packets_s.nextval,
pbc.budget_version_id,
pbc.project_id,
pbc.task_id,
pbc.expenditure_type,
pbc.expenditure_organization_id,
pbc.expenditure_item_date,
pbc.set_of_books_id,
pbc.je_source_name,
pbc.je_category_name,
pbc.document_type,
pbc.document_header_id,
pbc.document_distribution_id,
pbc.actual_flag,
/** pagl period enhancement changes instead of passing pa date
pass transaction date to derive the period name
--decode(pbc.document_type,'AP',(
pa_funds_control_pkg1.get_period_name(pa_utils2.get_pa_date
(pbc.expenditure_item_date,NULL,pbc.org_id),pbc.set_of_books_id),
-- ),pbc.period_name),
**/
/** Bug fix:2905892 As per discussions with Barbara , Dinakar, Prithi
* the period name should be derived
* based on the orginal raw line for the burden transactions
* so reverting back to changes made earlier
*pa_funds_control_pkg1.get_period_name
* (pbc.expenditure_item_date,pbc.set_of_books_id),**/
pbc.period_name,
/** End of bug fix: 2905892 ***/
pbc.period_year,
pbc.period_num,
pbc.result_code,
pbc.status_code,
pa_currency.round_trans_currency_amt(
DECODE ( NVL ( pbc.entered_dr, 0 ), 0, 0,
(( NVL (pbc.entered_dr ,0) *
l_tab_multiplier(indx)))),g_acct_currency_code),
pa_currency.round_trans_currency_amt(
DECODE ( NVL ( pbc.entered_cr, 0 ), 0, 0,
(( NVL (pbc.entered_cr ,0) *
l_tab_multiplier(indx) ))),g_acct_currency_code),
pa_currency.round_trans_currency_amt(
DECODE ( NVL ( pbc.accounted_dr, 0 ), 0, 0,
(( NVL (pbc.accounted_dr ,0) *
l_tab_multiplier(indx)))),g_acct_currency_code),
pa_currency.round_trans_currency_amt(
DECODE ( NVL ( pbc.accounted_cr, 0 ), 0, 0,
(( NVL (pbc.accounted_cr ,0) *
l_tab_multiplier(indx)))),g_acct_currency_code),
NULL, --gl_row_bc_packet_row_id
pbc.balance_posted_flag,
pbc.funds_process_mode,
pbc.txn_ccid,
pbc.bc_packet_id,
pbc.encumbrance_type_id,
'O',
pbc.org_id,
pbc.gl_date,
pbc.pa_date,
pbc.document_line_id,
l_tab_multiplier(indx)
,pbc.reference1
,pbc.reference2
,pbc.reference3
,pbc.exp_item_id
,pbc.bc_event_id
,pbc.budget_line_id
,pbc.vendor_id
,pbc.main_or_backing_code
,pbc.burden_method_code
,pbc.source_event_id
,pbc.ext_bdgt_flag
,pbc.document_distribution_type
,pbc.document_header_id_2
,pbc.proj_encumbrance_type_id
FROM pa_bc_packets pbc
WHERE pbc.packet_id = p_packet_id
AND pbc.bc_packet_id = p_bc_packet_id
AND pbc.document_type = p_doc_type
AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F' ;
PA_FUNDS_CONTROL_PKG.result_status_code_update
(p_packet_id => p_packet_id,
p_bc_packet_id => p_bc_packet_id,
p_result_code => 'F114',
p_res_result_code => 'F114',
p_res_grp_result_code => 'F114',
p_task_result_code => 'F114',
p_top_task_result_code => 'F114',
p_project_result_code => 'F114',
p_proj_acct_result_code => 'F114');
INSERT INTO pa_bc_packets
( ---- who columns------
request_id,
program_id,
program_application_id,
program_update_date,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
------ main columns-----------
packet_id,
bc_packet_id,
budget_version_id,
project_id,
task_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
set_of_books_id,
je_source_name,
je_category_name,
document_type,
document_header_id,
document_distribution_id,
actual_flag,
period_name,
period_year,
period_num,
result_code,
status_code,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
gl_row_number, --gl_row_bc_packet_row_id
balance_posted_flag,
funds_process_mode,
txn_ccid,
parent_bc_packet_id,
encumbrance_type_id,
burden_cost_flag,
org_id,
gl_date,
pa_date,
document_line_id,
compiled_multiplier,
reference1,
reference2,
reference3,
exp_item_id,
bc_event_id,
budget_line_id,
vendor_id,
main_or_backing_code,
burden_method_code,
source_event_id,
ext_bdgt_flag,
document_distribution_type,
document_header_id_2,
proj_encumbrance_type_id)
SELECT
l_request_id,
l_program_id,
l_program_application_id,
sysdate,
sysdate,
l_userid,
l_userid,
sysdate,
l_update_login,
------ main columns-----------
pbc.packet_id,
pa_bc_packets_s.nextval,
pbc.budget_version_id,
pbc.project_id,
pbc.task_id,
pbc.expenditure_type, --- p_exp_type
pbc.expenditure_organization_id,
pbc.expenditure_item_date,
pbc.set_of_books_id,
pbc.je_source_name,
pbc.je_category_name,
pbc.document_type,
pbc.document_header_id,
pbc.document_distribution_id,
pbc.actual_flag,
/** pagl period enhancement changes instead of passing pa date
pass transaction date to derive the period name
pa_funds_control_pkg1.get_period_name(pa_utils2.get_pa_date
(pbc.expenditure_item_date,NULL,pbc.org_id),pbc.set_of_books_id),
--P_period_name,
**/
/** Bug fix:2905892 As per discussions with Barbara , Dinakar, Prithi
* the period name should be derived
* based on the orginal raw line for the burden transactions
* so reverting back to changes made earlier
*pa_funds_control_pkg1.get_period_name
* (pbc.expenditure_item_date,pbc.set_of_books_id),**/
pbc.period_name,
/** End of bug fix: 2905892 ***/
pbc.period_year,
pbc.period_num,
pbc.result_code,
pbc.status_code,
/* Incorrect Burden amts Bug fix:
pa_currency.round_trans_currency_amt
(p_entered_cr,g_acct_currency_code),
-- amount from pa_bc_commitments (flip the amts)
pa_currency.round_trans_currency_amt
(p_entered_dr,g_acct_currency_code), -- amount from pa_bc_commitments
pa_currency.round_trans_currency_amt
(p_accounted_cr,g_acct_currency_code),-- amount from pa_bc_commitments
pa_currency.round_trans_currency_amt
(p_accounted_dr,g_acct_currency_code), -- amount from pa_bc_commitments
End Of bug fix: */
pa_currency.round_trans_currency_amt
(p_entered_dr,g_acct_currency_code),
pa_currency.round_trans_currency_amt
(p_entered_cr,g_acct_currency_code),
pa_currency.round_trans_currency_amt
(p_accounted_dr,g_acct_currency_code),
pa_currency.round_trans_currency_amt
(p_accounted_cr,g_acct_currency_code),
NULL, --gl_row_bc_packet_row_id to be updated later
pbc.balance_posted_flag,
pbc.funds_process_mode,
pbc.txn_ccid,
pbc.bc_packet_id,
pbc.encumbrance_type_id,
'O',
pbc.org_id,
pbc.gl_date,
pbc.pa_date,
pbc.document_line_id,
p_compiled_multiplier,
pbc.reference1,
pbc.reference2,
pbc.reference3,
pbc.exp_item_id,
pbc.bc_event_id,
pbc.budget_line_id,
pbc.vendor_id,
pbc.main_or_backing_code,
pbc.burden_method_code,
pbc.source_event_id,
pbc.ext_bdgt_flag,
pbc.document_distribution_type,
pbc.document_header_id_2,
pbc.proj_encumbrance_type_id
FROM pa_bc_packets pbc
WHERE pbc.packet_id = p_packet_id
AND pbc.bc_packet_id = p_bc_packet_id
AND pbc.document_type = p_doc_type
AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F';
PA_FUNDS_CONTROL_PKG.result_status_code_update
(p_packet_id => p_packet_id,
p_bc_packet_id => p_bc_packet_id,
p_result_code => 'F114',
p_res_result_code => 'F114',
p_res_grp_result_code => 'F114',
p_task_result_code => 'F114',
p_top_task_result_code => 'F114',
p_project_result_code => 'F114',
p_proj_acct_result_code => 'F114');
INSERT INTO pa_bc_packets
( ---- who columns------
request_id,
program_id,
program_application_id,
program_update_date,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
------ main columns-----------
packet_id,
bc_packet_id,
budget_version_id,
project_id,
task_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
set_of_books_id,
je_source_name,
je_category_name,
document_type,
document_header_id,
document_distribution_id,
actual_flag,
period_name,
period_year,
period_num,
result_code,
status_code,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
gl_row_number, --gl_row_bc_packet_row_id
balance_posted_flag,
funds_process_mode,
txn_ccid,
parent_bc_packet_id,
encumbrance_type_id,
burden_cost_flag,
org_id,
gl_date,
pa_date,
document_line_id,
compiled_multiplier
,reference1
,reference2
,reference3
,exp_item_id
,bc_event_id
,budget_line_id
,vendor_id
,main_or_backing_code
,burden_method_code
,source_event_id
,ext_bdgt_flag
,document_distribution_type
,document_header_id_2
,proj_encumbrance_type_id
)
SELECT
l_request_id,
l_program_id,
l_program_application_id,
sysdate,
sysdate,
l_userid,
l_userid,
sysdate,
l_update_login,
------ main columns-----------
pbc.packet_id,
pa_bc_packets_s.nextval,
pbc.budget_version_id,
pbc.project_id,
pbc.task_id,
l_tab_icc_exp_type(indx),
--et.expenditure_type,
pbc.expenditure_organization_id,
pbc.expenditure_item_date,
pbc.set_of_books_id,
pbc.je_source_name,
pbc.je_category_name,
pbc.document_type,
pbc.document_header_id,
pbc.document_distribution_id,
pbc.actual_flag,
/** added the pagl enhancement changes pass ei date instead of pa date
* to get glperiod name
pa_funds_control_pkg1.get_period_name(pa_utils2.get_pa_date
(pbc.expenditure_item_date,NULL,pbc.org_id),pbc.set_of_books_id),
--pbc.period_name,
*/
/** Bug fix:2905892 As per discussions with Barbara , Dinakar, Prithi
* the period name should be derived
* based on the orginal raw line for the burden transactions
* so reverting back to changes made earlier
*pa_funds_control_pkg1.get_period_name
* (pbc.expenditure_item_date,pbc.set_of_books_id),**/
pbc.period_name,
/** End of bug fix: 2905892 ***/
pbc.period_year,
pbc.period_num,
pbc.result_code,
pbc.status_code,
pa_currency.round_trans_currency_amt(
decode(nvl(pbc.entered_dr,0),0,0,(nvl(pbc.entered_dr,0)*
l_tab_multiplier(indx))),g_acct_currency_code),
pa_currency.round_trans_currency_amt(
decode(nvl(pbc.entered_cr,0),0,0,(nvl(pbc.entered_cr,0)*
l_tab_multiplier(indx))),g_acct_currency_code),
pa_currency.round_trans_currency_amt(
decode(nvl(pbc.accounted_dr,0),0,0,(nvl(pbc.accounted_dr,0)*
l_tab_multiplier(indx))),g_acct_currency_code),
pa_currency.round_trans_currency_amt(
decode(nvl(pbc.accounted_cr,0),0,0,(nvl(pbc.accounted_cr,0)*
l_tab_multiplier(indx))),g_acct_currency_code),
NULL, --gl_row_bc_packet_row_id
pbc.balance_posted_flag,
pbc.funds_process_mode,
pbc.txn_ccid,
pbc.bc_packet_id,
pbc.encumbrance_type_id,
'O',
pbc.org_id,
pbc.gl_date,
pbc.pa_date,
pbc.document_line_id,
--cm.compiled_multiplier
l_tab_multiplier(indx)
,pbc.reference1
,pbc.reference2
,pbc.reference3
,pbc.exp_item_id
,pbc.bc_event_id
,pbc.budget_line_id
,pbc.vendor_id
,pbc.main_or_backing_code
,pbc.burden_method_code
,pbc.source_event_id
,ext_bdgt_flag
,pbc.document_distribution_type
,pbc.document_header_id_2
,pbc.proj_encumbrance_type_id
FROM pa_bc_packets pbc
WHERE pbc.packet_id = p_packet_id
AND pbc.bc_packet_id = p_bc_packet_id
AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F';
PA_FUNDS_CONTROL_PKG.result_status_code_update
(p_packet_id => p_packet_id,
p_bc_packet_id => p_bc_packet_id,
p_result_code => 'F114',
p_res_result_code => 'F114',
p_res_grp_result_code => 'F114',
p_task_result_code => 'F114',
p_top_task_result_code => 'F114',
p_project_result_code => 'F114',
p_proj_acct_result_code => 'F114');
INSERT INTO pa_bc_packets
( ---- who columns------
request_id,
program_id,
program_application_id,
program_update_date,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
------ main columns-----------
packet_id,
bc_packet_id,
budget_version_id,
project_id,
task_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
set_of_books_id,
je_source_name,
je_category_name,
document_type,
document_header_id,
document_distribution_id,
actual_flag,
period_name,
period_year,
period_num,
result_code,
status_code,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
gl_row_number, --gl_row_bc_packet_row_id
balance_posted_flag,
funds_process_mode,
txn_ccid,
parent_bc_packet_id,
encumbrance_type_id,
burden_cost_flag,
org_id,
gl_date,
pa_date,
document_line_id,
compiled_multiplier
,reference1
,reference2
,reference3
,bc_event_id
,budget_line_id
,vendor_id
,main_or_backing_code
,burden_method_code
,source_event_id
,ext_bdgt_flag
,document_distribution_type
,document_header_id_2
,proj_encumbrance_type_id
)
SELECT
l_request_id,
l_program_id,
l_program_application_id,
sysdate,
sysdate,
l_userid,
l_userid,
sysdate,
l_update_login,
------ main columns-----------
pbc.packet_id,
pa_bc_packets_s.nextval,
pbc.budget_version_id,
pbc.project_id,
pbc.task_id,
/* Bug fix:3026988
--when REQ becomes PO, the Exp type for reversing REQ is getting
--exp type from raw line instead of icc.cost_codes exp type from original
--burden line
--pbc.expenditure_type,
-- to be confirmed with sandeep to consider the old exp type
*/
p_exp_type,
/* end of bug fix:3026988 */
pbc.expenditure_organization_id,
pbc.expenditure_item_date,
pbc.set_of_books_id,
pbc.je_source_name,
pbc.je_category_name,
pbc.document_type,
pbc.document_header_id,
pbc.document_distribution_id,
pbc.actual_flag,
/** pagl period enhancement changes instead of passing pa date
pass transaction date to derive the period name
pa_funds_control_pkg1.get_period_name(pa_utils2.get_pa_date
(pbc.expenditure_item_date,NULL,pbc.org_id),pbc.set_of_books_id),
**/
/** Bug fix:2905892 As per discussions with Barbara , Dinakar, Prithi
* the period name should be derived
* based on the orginal raw line for the burden transactions
* so reverting back to changes made earlier
*pa_funds_control_pkg1.get_period_name
* (pbc.expenditure_item_date,pbc.set_of_books_id),**/
pbc.period_name,
/** End of bug fix: 2905892 ***/
pbc.period_year,
pbc.period_num,
pbc.result_code,
pbc.status_code,
/* Incorrect Burden amts Bug fix:
pa_currency.round_trans_currency_amt
(p_entered_cr,g_acct_currency_code),
-- amount from pa_bc_commitments (flip amts)
pa_currency.round_trans_currency_amt
(p_entered_dr,g_acct_currency_code), -- amount from pa_bc_commitments
pa_currency.round_trans_currency_amt
(p_accounted_cr,g_acct_currency_code), -- amount from pa_bc_commitments
pa_currency.round_trans_currency_amt
(p_accounted_dr,g_acct_currency_code), -- amount from pa_bc_commitments
End of Bug fix: */
pa_currency.round_trans_currency_amt
(p_entered_dr,g_acct_currency_code),
pa_currency.round_trans_currency_amt
(p_entered_cr,g_acct_currency_code),
pa_currency.round_trans_currency_amt
(p_accounted_dr,g_acct_currency_code),
pa_currency.round_trans_currency_amt
(p_accounted_cr,g_acct_currency_code),
NULL, --gl_row_bc_packet_row_id to be updated later
pbc.balance_posted_flag,
pbc.funds_process_mode,
pbc.txn_ccid,
pbc.bc_packet_id,
pbc.encumbrance_type_id,
'O',
pbc.org_id,
pbc.gl_date,
pbc.pa_date,
pbc.document_line_id,
p_compiled_multiplier,
pbc.reference1,
pbc.reference2,
pbc.reference3,
pbc.bc_event_id,
pbc.budget_line_id,
pbc.vendor_id,
pbc.main_or_backing_code,
pbc.burden_method_code,
pbc.source_event_id,
ext_bdgt_flag,
pbc.document_distribution_type,
pbc.document_header_id_2,
pbc.proj_encumbrance_type_id
FROM pa_bc_packets pbc
WHERE pbc.packet_id = p_packet_id
AND pbc.bc_packet_id = p_bc_packet_id
AND pbc.document_type = p_doc_type
AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F';
PA_FUNDS_CONTROL_PKG.result_status_code_update
(p_packet_id => p_packet_id,
p_bc_packet_id => p_bc_packet_id,
p_result_code => 'F114',
p_res_result_code => 'F114',
p_res_grp_result_code => 'F114',
p_task_result_code => 'F114',
p_top_task_result_code => 'F114',
p_project_result_code => 'F114',
p_proj_acct_result_code => 'F114');
l_update_login NUMBER := NVL(FND_GLOBAL.login_id,-1);
SELECT pktburd.period_name,
/* Commenting as part of 13911609
nvl(pkttrx.entered_dr,0) * decode(nvl(pkttrx.entered_dr,0),0,0,
get_ratio(pkttrx.document_header_id,
pkttrx.document_distribution_id,
pkttrx.document_type,
'BCCMT',
'E')) entered_dr,
nvl(pkttrx.entered_cr,0) * decode(nvl(pkttrx.entered_cr,0),0,0,
get_ratio(pkttrx.document_header_id,
pkttrx.document_distribution_id,
pkttrx.document_type,
'BCCMT',
'E')) entered_cr,
nvl(pkttrx.accounted_dr,0) * decode(nvl(pkttrx.accounted_dr,0),0,0,
get_ratio(pkttrx.document_header_id,
pkttrx.document_distribution_id,
pkttrx.document_type,
'BCCMT',
'A')) accounted_dr,
nvl(pkttrx.accounted_cr,0) * decode(nvl(pkttrx.accounted_cr,0),0,0,
get_ratio(pkttrx.document_header_id,
pkttrx.document_distribution_id,
pkttrx.document_type,
'BCCMT',
'A')) accounted_cr, */
/* Start : Bug 13911609*/
(ABS(nvl(pktburd.entered_dr,0)-nvl(pktburd.entered_cr,0))/decode(ABS(nvl(pktraw.entered_dr,0) - nvl(pktraw.entered_cr,0)),0,1,ABS(nvl(pktraw.entered_dr,0) - nvl(pktraw.entered_cr,0))))
* nvl(pkttrx.entered_dr,0) entered_dr,
(ABS(nvl(pktburd.entered_dr,0)-nvl(pktburd.entered_cr,0))/decode(ABS(nvl(pktraw.entered_dr,0) - nvl(pktraw.entered_cr,0)),0,1,ABS(nvl(pktraw.entered_dr,0) - nvl(pktraw.entered_cr,0))))
* nvl(pkttrx.entered_cr,0) entered_cr,
(ABS(nvl(pktburd.accounted_dr,0)-nvl(pktburd.accounted_cr,0))/decode(ABS(nvl(pktraw.accounted_dr,0) - nvl(pktraw.accounted_cr,0)),0,1,ABS(nvl(pktraw.accounted_dr,0) - nvl(pktraw.accounted_cr,0))))
* nvl(pkttrx.accounted_dr,0) accounted_dr,
(ABS(nvl(pktburd.accounted_dr,0)-nvl(pktburd.accounted_cr,0))/decode(ABS(nvl(pktraw.accounted_dr,0) - nvl(pktraw.accounted_cr,0)),0,1,ABS(nvl(pktraw.accounted_dr,0) - nvl(pktraw.accounted_cr,0))))
* nvl(pkttrx.accounted_cr,0) accounted_cr,
/* End : Bug 13911609*/
pktburd.expenditure_type,
pktburd.compiled_multiplier
FROM pa_bc_commitments_all pktburd
,pa_bc_commitments_all pktraw
,pa_bc_packets pkttrx
WHERE pktburd.document_distribution_id = l_req_id
AND pktburd.document_header_id = l_po_header_id
AND pktburd.document_type = l_comm_doc_type
AND pktburd.parent_bc_packet_id is NOT NULL
AND (pktburd.packet_id ,pktburd.parent_bc_packet_id ) in
( SELECT max(comm.packet_id),max(comm.bc_packet_id)
FROM pa_bc_commitments comm
WHERE comm.document_distribution_id = pktburd.document_distribution_id
ANd comm.document_header_id = pktburd.document_header_id
AND comm.document_type = pktburd.document_type
AND comm.parent_bc_packet_id is NULL
)
AND pktburd.packet_id = pktraw.packet_id
AND pktraw.parent_bc_packet_id is null
AND pktraw.document_distribution_id = pktburd.document_distribution_id
AND pktraw.document_header_id = pktburd.document_header_id
AND pktraw.document_type = pktburd.document_type
AND pktburd.parent_bc_packet_id = pktraw.bc_packet_id
AND pkttrx.packet_id = p_packet_id
AND pkttrx.bc_packet_id = l_bc_packet_id;
SELECT pktburd.period_name,
/* Commenting as part of 13911609
nvl(pkttrx.entered_dr,0) * decode(nvl(pkttrx.entered_dr,0),0,0,
get_ratio(pkttrx.document_header_id,
pkttrx.document_distribution_id,
pkttrx.document_type,
'BCPKT',
'E')) entered_dr,
nvl(pkttrx.entered_cr,0) * decode(nvl(pkttrx.entered_cr,0),0,0,
get_ratio(pkttrx.document_header_id,
pkttrx.document_distribution_id,
pkttrx.document_type,
'BCPKT',
'E')) entered_cr,
nvl(pkttrx.accounted_dr,0) * decode(nvl(pkttrx.accounted_dr,0),0,0,
get_ratio(pkttrx.document_header_id,
pkttrx.document_distribution_id,
pkttrx.document_type,
'BCPKT',
'A')) accounted_dr,
nvl(pkttrx.accounted_cr,0) * decode(nvl(pkttrx.accounted_cr,0),0,0,
get_ratio(pkttrx.document_header_id,
pkttrx.document_distribution_id,
pkttrx.document_type,
'BCPKT',
'A')) accounted_cr, */
/* Start : Bug 13911609*/
(ABS(nvl(pktburd.entered_dr,0)-nvl(pktburd.entered_cr,0))/decode(ABS(nvl(pktraw.entered_dr,0) - nvl(pktraw.entered_cr,0)),0,1,ABS(nvl(pktraw.entered_dr,0) - nvl(pktraw.entered_cr,0))))
* nvl(pkttrx.entered_dr,0) entered_dr,
(ABS(nvl(pktburd.entered_dr,0)-nvl(pktburd.entered_cr,0))/decode(ABS(nvl(pktraw.entered_dr,0) - nvl(pktraw.entered_cr,0)),0,1,ABS(nvl(pktraw.entered_dr,0) - nvl(pktraw.entered_cr,0))))
* nvl(pkttrx.entered_cr,0) entered_cr,
(ABS(nvl(pktburd.accounted_dr,0)-nvl(pktburd.accounted_cr,0))/decode(ABS(nvl(pktraw.accounted_dr,0) - nvl(pktraw.accounted_cr,0)),0,1,ABS(nvl(pktraw.accounted_dr,0) - nvl(pktraw.accounted_cr,0))))
* nvl(pkttrx.accounted_dr,0) accounted_dr,
(ABS(nvl(pktburd.accounted_dr,0)-nvl(pktburd.accounted_cr,0))/decode(ABS(nvl(pktraw.accounted_dr,0) - nvl(pktraw.accounted_cr,0)),0,1,ABS(nvl(pktraw.accounted_dr,0) - nvl(pktraw.accounted_cr,0))))
* nvl(pkttrx.accounted_cr,0) accounted_cr,
/* End : Bug 13911609*/
pktburd.expenditure_type,
pktburd.compiled_multiplier
FROM pa_bc_packets pktburd
,pa_bc_packets pktraw
,pa_bc_packets pkttrx
WHERE pktburd.document_distribution_id = l_req_id
AND pktburd.document_header_id = l_po_header_id
AND pktburd.document_type = l_pkt_doc_type
AND pktburd.parent_bc_packet_id is NOT NULL
AND pktburd.balance_posted_flag in ('N')
AND pktburd.status_code in ('A','C')
AND substr(nvl(pktburd.result_code,'P'),1,1) = 'P'
AND (pktburd.packet_id,pktburd.parent_bc_packet_id) in
( SELECT MAX(pbc.packet_id),max(bc_packet_id)
FROM pa_bc_packets pbc
WHERE pbc.document_distribution_id = pktburd.document_distribution_id
AND pbc.document_header_id = pktburd.document_header_id
AND pbc.document_type = pktburd.document_type
AND pbc.parent_bc_packet_id is NULL
AND pbc.balance_posted_flag in ('N')
AND pbc.status_code in ('A','C')
AND substr(nvl(pbc.result_code,'P'),1,1) = 'P'
)
AND pktburd.packet_id = pktraw.packet_id
AND pktraw.parent_bc_packet_id is null
AND pktraw.document_distribution_id = pktburd.document_distribution_id
AND pktraw.document_header_id = pktburd.document_header_id
AND pktraw.document_type = pktburd.document_type
and pktburd.parent_bc_packet_id = pktraw.bc_packet_id
AND pkttrx.packet_id = p_packet_id
AND pkttrx.bc_packet_id = l_bc_packet_id;
SELECT decode(NVL(ppt.burden_cost_flag, 'N'),'Y',
decode(NVL(burden_amt_display_method,'S'), 'S','SAME','D','DIFFERENT'),'NONE'),
pbc.bc_packet_id ,
pbc.document_type,
pbc.parent_bc_packet_id,
pbc.document_header_id,
pbc.document_distribution_id ,
pbc.task_id,
pbc.expenditure_item_date,
pbc.expenditure_type
FROM pa_project_types ppt,
pa_projects_all pp,
pa_tasks ptk,
pa_bc_packets pbc
WHERE
ppt.project_type = pp.project_type
AND pp.project_id = pbc.project_id
AND ptk.project_id = pbc.project_id
AND ptk.task_id = pbc.task_id
AND pbc.packet_id = p_packet_id
AND ((pbc.parent_bc_packet_id is null
and p_calling_module IN ('GL','CBC','EXPENDITURE'))
OR (pbc.parent_bc_packet_id = -1 and p_calling_module
in ('DISTBTC','GL','TRXIMPORT','DISTERADJ','DISTVIADJ','INTERFACER'
,'INTERFACVI','TRXNIMPORT','DISTCWKST'))
)
AND pbc.status_code IN ('P', 'I');
--- insert into pa_bc_packets for the burden cost as a separate bc packet record
--- after the funds check these records may be inserted into gl_bc_packets
-----------------------------------------------------------------------------
If l_parent_bc_packet_id = -1 then
l_related_link := 'Y';
-- or invoice based on purchase orders then update the transaction
-- with error status transaction failed to populate burden cost
If l_status_flag = 'Y' and l_related_link = 'Y' then
PA_FUNDS_CONTROL_PKG.result_status_code_update
(p_packet_id => p_packet_id,
p_bc_packet_id => l_bc_packet_id,
p_result_code => 'F114',
p_res_result_code => 'F114',
p_res_grp_result_code => 'F114',
p_task_result_code => 'F114',
p_top_task_result_code => 'F114',
p_project_result_code => 'F114',
p_proj_acct_result_code => 'F114');
-- update the bc packets set the parent bc packet id to null
-- after derving the burden components for the raw lines
UPDATE pa_bc_packets
SET parent_bc_packet_id = null
WHERE packet_id = p_packet_id
AND parent_bc_packet_id = -1;
--update the cwk summary record flag info for contingent worker transactions only
--for the first occurance of the record ie. project, task and po line
IF p_calling_module = 'GL' Then
/* 3703180 */
IF g_cwk_po_unreserve = 'Y' THEN
update_cwk_po_burden(p_packet_id) ;
--error msg : 'F140 = Funds check failed because of insert burden cost'
PA_FUNDS_CONTROL_PKG.result_status_code_update
( p_status_code=> 'T',
p_result_code => 'F140',
p_res_result_code => 'F140',
p_res_grp_result_code => 'F140',
p_task_result_code => 'F140',
p_top_task_result_code => 'F140',
p_project_result_code => 'F140',
p_proj_acct_result_code => 'F140',
p_packet_id => p_packet_id
);
SELECT start_date,
completion_date
INTO l_project_start_date,
l_project_end_date
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT MIN ( start_date )
INTO l_project_start_date
FROM pa_bc_balances
WHERE budget_version_id = p_budget_version_id
AND project_id = p_project_id
AND balance_type = 'BGT';
SELECT MAX ( end_date )
INTO l_bal_end_date
FROM pa_bc_balances
WHERE budget_version_id = p_budget_version_id
AND project_id = p_project_id
AND balance_type = 'BGT';
SELECT gps.year_start_date
INTO l_year_start_date
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.set_of_books_id = p_set_of_books_id
AND p_expenditure_item_date BETWEEN gps.start_date AND gps.end_date
AND gps.adjustment_period_flag = 'N';
SELECT TRUNC ( gps.start_date ),
TRUNC ( gps.end_date )
INTO l_gl_period_start_date,
l_gl_period_end_date
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.set_of_books_id = p_set_of_books_id
AND p_expenditure_item_date BETWEEN gps.start_date AND gps.end_date
AND gps.adjustment_period_flag = 'N';
SELECT TRUNC ( start_date ),
TRUNC ( end_date )
INTO l_pa_period_start_date,
l_pa_period_end_date
FROM pa_periods gpa
WHERE p_expenditure_item_date BETWEEN gpa.start_date AND gpa.end_date;
SELECT TRUNC ( MAX ( start_date ))
INTO l_dr_period_start_date
FROM pa_bc_balances
WHERE project_id = p_project_id
AND budget_version_id = p_budget_version_id
AND balance_type = 'BGT'
AND start_date <= p_expenditure_item_date;
SELECT TRUNC ( MIN ( end_date ))
INTO l_dr_period_end_date
FROM pa_bc_balances
WHERE project_id = p_project_id
AND budget_version_id = p_budget_version_id
AND balance_type = 'BGT'
AND end_date >= p_expenditure_item_date;
SELECT TRUNC ( start_date )
INTO l_gs_start_date
FROM pa_periods gpa
WHERE l_project_start_date BETWEEN gpa.start_date AND gpa.end_date;
SELECT TRUNC ( MIN ( start_date ))
INTO l_gs_start_date
FROM pa_bc_balances
WHERE project_id = p_project_id
AND budget_version_id = p_budget_version_id
AND balance_type = 'BGT';
SELECT TRUNC ( gps.start_date )
INTO l_gs_start_date
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.set_of_books_id = p_set_of_books_id
AND l_project_start_date BETWEEN gps.start_date AND gps.end_date
AND gps.adjustment_period_flag = 'N';
SELECT TRUNC ( MIN ( start_date ))
INTO l_gs_start_date
FROM pa_bc_balances
WHERE project_id = p_project_id
AND budget_version_id = p_budget_version_id
AND balance_type = 'BGT';
SELECT TRUNC ( MIN ( start_date ))
INTO l_gs_start_date
FROM pa_bc_balances
WHERE project_id = p_project_id
AND budget_version_id = p_budget_version_id
AND balance_type = 'BGT';
SELECT TRUNC ( end_date )
INTO l_gs_end_date
FROM pa_periods gpa
WHERE l_project_end_date BETWEEN gpa.start_date AND gpa.end_date;
SELECT TRUNC ( MAX ( end_date ))
INTO l_gs_end_date
FROM pa_bc_balances
WHERE project_id = p_project_id
AND budget_version_id = p_budget_version_id
AND balance_type = 'BGT';
SELECT TRUNC ( gps.end_date )
INTO l_gs_end_date
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.set_of_books_id = p_set_of_books_id
AND l_project_end_date BETWEEN gps.start_date AND gps.end_date
AND gps.adjustment_period_flag = 'N';
SELECT TRUNC ( MAX ( end_date ))
INTO l_gs_end_date
FROM pa_bc_balances
WHERE project_id = p_project_id
AND budget_version_id = p_budget_version_id
AND balance_type = 'BGT';
SELECT p.end_date
INTO l_gs_end_date
FROM pa_periods p
WHERE l_year_end_date BETWEEN p.start_date AND p.end_date;
SELECT p.start_date
INTO l_gs_start_date
FROM pa_periods p
WHERE l_year_start_date BETWEEN p.start_date AND p.end_date;
SELECT p.end_date
INTO l_gs_end_date
FROM pa_periods p
WHERE l_year_end_date BETWEEN p.start_date AND p.end_date;
SELECT event_id, b.result_code
FROM PSA_BC_XLA_EVENTS_GT b;
UPDATE pa_bc_packets a
SET status_code = DECODE(p_bc_mode,'C','F','R'),
result_code = DECODE(substr(result_code,1,1),'F',result_code,'F172')
WHERE status_code in ('P','I','A','S')
AND source_event_id IN
(SELECT event_id
FROM PSA_BC_XLA_EVENTS_GT
WHERE upper(result_code) in ('XLA_ERROR','FATAL','XLA_UNPROCESSED','XLA_NO_JOURNAL'));
UPDATE pa_bc_packets a
SET status_code = DECODE(p_bc_mode,'C','F','R'),
result_code = DECODE(substr(result_code,1,1),'F',result_code,'F172')
WHERE status_code in ('P','I','A','S')
AND bc_event_id IN
(SELECT event_id
FROM PSA_BC_XLA_EVENTS_GT
WHERE upper(result_code) in ('XLA_ERROR','FATAL','XLA_UNPROCESSED','XLA_NO_JOURNAL'));
PROCEDURE INTERFACE_TBC_BTC_COMT_UPDATE (p_calling_module IN VARCHAR2,
P_request_id IN NUMBER ,
x_result_code OUT NOCOPY VARCHAR2) IS
-- Variables to store eligible CDL data
l_Exp_Item_Id_tbl PA_PLSQL_DATATYPES.IDTabTyp;
SELECT CDL.Expenditure_Item_Id,
CDL.Gl_Date,
ITEM.Burden_Sum_Dest_Run_Id,
ITEM.document_header_id,
ITEM.document_distribution_id ,
CDL.line_type,
ITEM.system_linkage_function,
ITEM.expenditure_type,
CDL.acct_event_id,
-- Below columns added for bug 5263721
CDL.billable_flag,
CDL.project_id,
CDL.task_id,
CDL.pa_period_name,
ITEM.denom_currency_code,
ITEM.acct_currency_code,
ITEM.project_currency_code,
ITEM.projfunc_currency_code,
CDL.system_reference1,
EXP.person_type,
ITEM.po_line_id,
ITEM.attribute1,
ITEM.attribute2,
ITEM.attribute3,
ITEM.attribute4,
ITEM.attribute5,
ITEM.attribute6,
ITEM.attribute7,
ITEM.attribute8,
ITEM.attribute9,
ITEM.attribute10,
ITEM.attribute_category,
ITEM.expenditure_item_date,
CDL.acct_rate_date,
CDL.acct_rate_type,
CDL.acct_exchange_rate,
CDL.project_rate_date,
CDL.project_rate_type,
CDL.project_exchange_rate,
CDL.projfunc_cost_rate_date,
CDL.projfunc_cost_rate_type,
CDL.projfunc_cost_exchange_rate,
ITEM.job_id,
ITEM.non_labor_resource,
ITEM.organization_id non_labor_resource_orgn_id,
ITEM.wip_resource_id,
EXP.incurred_by_person_id,
ITEM.inventory_item_id,
-- Columns added for Bug 5680236
cdl.budget_line_id,
cdl.budget_version_id,
ITEM.document_line_number,
ITEM.transaction_source,
cdl.rowid
FROM PA_Cost_Distribution_Lines CDL,
pa_expenditure_items_all ITEM,
pa_expenditures_all EXP
WHERE CDL.transfer_status_code in ('A', 'G')
AND CDL.request_id = p_request_id
AND item.expenditure_item_id = cdl.expenditure_item_id
AND ITEM.expenditure_id = EXP.expenditure_id
AND ( (p_calling_module = 'BTC' AND item.system_linkage_function = 'BTC' AND CDL.line_type = 'R' ) OR
(p_calling_module = 'TBC' AND CDL.line_type = 'D' AND item.system_linkage_function IN ('VI','ST','OT')) OR
(p_calling_module = 'Cost' AND ((item.system_linkage_function = 'BTC' AND CDL.line_type = 'R') OR
(CDL.line_type = 'D' AND item.system_linkage_function IN ('VI','ST','OT'))))
)
AND NVL(CDL.liquidate_encum_flag,'N') = 'Y'
ORDER BY CDL.Expenditure_Item_Id,CDL.line_num;
SELECT pbc.bc_commitment_id,
pbc.project_id,
pbc.task_id,
pbc.resource_list_member_id,
DECODE(pm.entry_level_code,'P',0,pt.top_task_id) top_task_id,
bv.budget_version_id,
pbc.budget_line_id,
pm.entry_level_code,
(SELECT gl.start_date
FROM gl_period_statuses gl
WHERE gl.application_id = 101
AND gl.set_of_books_id = pbc.set_of_books_id
AND gl.period_name = pbc.period_name) gl_start_date,
pbc.exp_item_id,
pbc.transfer_status_code
FROM pa_bc_commitments_all pbc,
pa_tasks pt,
pa_budget_versions bv,
pa_budget_entry_methods pm
WHERE pbc.document_header_id = p_doc_header_id
AND pbc.document_distribution_id = DECODE(substr(p_transaction_source,1,10),'PO RECEIPT',p_doc_line_number,p_doc_distribution_id)
AND pbc.expenditure_type = p_expenditure_type
AND pbc.expenditure_item_date = p_expenditure_item_date
--AND pbc.transfer_status_code in ('P','R','X')
AND pbc.document_type in ('AP','PO')
AND pbc.burden_cost_flag = 'R'
AND ((pbc.parent_bc_packet_id is not null AND p_cdl_line_type ='R') OR p_cdl_line_type ='D')
AND pt.task_id = pbc.task_id
AND bv.budget_version_id = pbc.budget_version_id
AND bv.budget_entry_method_code = pm.budget_entry_method_code;
SELECT DECODE(pm.entry_level_code,'P',0,pt.top_task_id) top_task_id,
bv.budget_version_id,
pm.entry_level_code
FROM pa_tasks pt,
pa_budget_versions bv,
Pa_Budget_Types bt,
pa_budgetary_control_options pbct,
pa_budget_entry_methods pm
WHERE pt.task_id = p_task_id
AND bv.project_id = pt.project_id
AND bv.BUDGET_STATUS_CODE = 'B'
AND bt.budget_type_Code = bv.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.budget_entry_method_code = pm.budget_entry_method_code;
PA_DEBUG.init_err_stack ('PA_FUNDS_CONTROL_PKG1.INTERFACE_TBC_BTC_COMT_UPDATE');
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE : In start ');
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE: p_calling_module = '||p_calling_module);
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE : P_request_id = '||P_request_id);
PA_Sweeper.Update_Act_Enc_Balance (
X_Return_Status => l_Return_Status,
X_Error_Message_Code => l_Msg_Data
);
pa_funds_control_pkg.log_message(p_msg_token1 => 'Error occured while running sweeper process PA_Sweeper.Update_Act_Enc_Balance');
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE : Opening cursor CDL_CUR ');
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE : NUmber of records fethced '||l_count_of_records);
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE : Data interfaced prior R12');
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE : # of records fetched from CDL'||l_bc_commitment_id_tbl.count);
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE : budget line id on bc comt'||l_budget_line_id);
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE : Before calling PA_FUNDS_CONTROL_UTILS.Get_Budget_CCID' );
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE : After calling PA_FUNDS_CONTROL_UTILS.Get_Budget_CCID' );
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE : derived budget line id on bc comt'||l_budget_line_id);
UPDATE pa_bc_commitments
SET budget_line_id = l_budget_line_id
WHERE bc_commitment_id = l_bc_commitment_id_tbl(i)
AND budget_line_id IS NULL;
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE : # of bc comt records updated'||SQL%ROWCOUNT);
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE : Before calling PA_FUNDS_CONTROL_UTILS.Get_Budget_CCID' );
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE : After calling PA_FUNDS_CONTROL_UTILS.Get_Budget_CCID' );
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE : derived budget line id on cdl'||l_budget_line_id);
UPDATE pa_cost_distribution_lines_all cdl
SET cdl.budget_version_id = NVL(l_cdl_budget_version_id,l_bc_budget_version_id_tbl(1)),
cdl.budget_line_id = l_budget_line_id
WHERE cdl.rowid = l_cdl_rowid_tbl(cdl_rec);
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE : # of cdl records updated'||SQL%ROWCOUNT);
UPDATE pa_bc_commitments
SET exp_item_id = l_Exp_Item_Id_tbl(cdl_rec)
WHERE bc_commitment_id = l_bc_commitment_id_tbl(i)
AND exp_item_id IS NULL
AND transfer_status_code in ('P','R','X');
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE : # of bc comt records updated'||SQL%ROWCOUNT);
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE : updating eligible AP commitments for liq encumbrance ');
UPDATE pa_bc_commitments bc_cm
SET bc_cm.bc_event_id = l_cdl_acct_event_id_tbl(i),
bc_cm.transfer_status_code = 'A',
bc_cm.request_id = p_request_id,
bc_cm.transferred_date = SYSDATE,
bc_cm.liquidate_gl_date = l_cdl_gl_Date_tbl(i),
bc_cm.exp_item_id = DECODE (l_cdl_line_type_tbl(i),'R',l_Exp_Item_Id_tbl(i),bc_cm.exp_item_id) -- Bug 5076612
WHERE ( bc_cm.document_header_id,bc_cm.document_distribution_id,expenditure_type)
IN ( SELECT exp.document_header_id,exp.document_distribution_id,l_expenditure_type_tbl(i)
FROM PA_Cost_Distribution_lines cdl_raw,
pa_expenditure_items_all exp ,
pa_expenditures_all expend
WHERE cdl_raw.burden_sum_source_run_id = l_Burd_Sum_Dest_Run_Id_tbl(i)
AND exp.expenditure_item_id = cdl_raw.expenditure_item_id
AND cdl_raw.line_num = 1
AND l_cdl_line_type_tbl(i) ='R'
-- Below join conditions added for bug 5263721
AND exp.expenditure_id = expend.expenditure_id
AND nvl(l_billable_flag_tbl(i), -1) = nvl(cdl_raw.billable_flag, -1)
AND nvl(l_project_id_tbl(i), -1) = nvl(cdl_raw.project_id, -1)
AND nvl(l_task_id_tbl(i), -1) = nvl(cdl_raw.task_id, -1)
AND nvl(l_pa_period_name_tbl(i), -1) = nvl(cdl_raw.pa_period_name, -1)
AND nvl(l_denom_currency_code_tbl(i), -1) = nvl(exp.denom_currency_code, -1)
AND nvl(l_acct_currency_code_tbl(i), -1) = nvl(exp.acct_currency_code, -1)
AND nvl(l_project_currency_code_tbl(i), -1) = nvl(exp.project_currency_code, -1)
AND nvl(l_projfunc_currency_code_tbl(i), -1) = nvl(exp.projfunc_currency_code, -1)
/* AND nvl(l_system_reference1_tbl(i), -1) = nvl(cdl_raw.system_reference1, -1) bug 5453131*/
AND nvl(l_person_type_tbl(i), -1) = nvl(expend.person_type, -1)
AND nvl(l_po_line_id_tbl(i), -1) = nvl(exp.po_line_id, -1)
AND nvl(PA_CLIENT_EXTN_BURDEN_SUMMARY.CLIENT_GROUPING(
null,
null,
l_attribute1_tbl(i),
l_attribute2_tbl(i),
l_attribute3_tbl(i),
l_attribute4_tbl(i),
l_attribute5_tbl(i),
l_attribute6_tbl(i),
l_attribute7_tbl(i),
l_attribute8_tbl(i),
l_attribute9_tbl(i),
l_attribute10_tbl(i),
l_attribute_category_tbl(i),
l_expenditure_item_date_tbl(i),
l_acct_rate_date_tbl(i),
l_acct_rate_type_tbl(i),
l_acct_exchange_rate_tbl(i),
l_project_rate_date_tbl(i),
l_project_rate_type_tbl(i),
l_project_exchange_rate_tbl(i),
l_projfunc_cost_rate_date_tbl(i),
l_projfunc_cost_rate_type_tbl(i),
l_pfc_ex_rate_tbl(i)),-1) =
nvl(PA_CLIENT_EXTN_BURDEN_SUMMARY.CLIENT_GROUPING(
null,
null,
exp.attribute1,
exp.attribute2,
exp.attribute3,
exp.attribute4,
exp.attribute5,
exp.attribute6,
exp.attribute7,
exp.attribute8,
exp.attribute9,
exp.attribute10,
exp.attribute_category,
exp.expenditure_item_date,
exp.acct_rate_date,
exp.acct_rate_type,
exp.acct_exchange_rate,
exp.project_rate_date,
exp.project_rate_type,
exp.project_exchange_rate,
exp.projfunc_cost_rate_date,
exp.projfunc_cost_rate_type,
exp.projfunc_cost_exchange_rate),-1)
AND nvl(PA_CLIENT_EXTN_BURDEN_RESOURCE.CLIENT_GROUPING(
l_job_id_tbl(i),
l_non_labor_resource_tbl(i),
l_nl_res_orgn_id_tbl(i),
l_wip_resource_id_tbl(i),
l_incurred_by_person_id_tbl(i),
l_inventory_item_id_tbl(i)), -1) =
nvl(PA_CLIENT_EXTN_BURDEN_RESOURCE.CLIENT_GROUPING(
exp.job_id,
exp.non_labor_resource,
exp.organization_id,
exp.wip_resource_id,
expend.incurred_by_person_id,
exp.inventory_item_id),-1)
UNION ALL
SELECT l_doc_header_id_tbl(i),l_doc_distribution_id_tbl(i),l_expenditure_type_tbl(i)
FROM dual
WHERE l_cdl_line_type_tbl(i) ='D' )
AND bc_cm.transfer_status_code in ('P','R','X')
AND bc_cm.document_type = 'AP'
AND bc_cm.burden_cost_flag = 'R'
AND ((bc_cm.parent_bc_packet_id IS NOT NULL AND l_cdl_line_type_tbl(i) ='R') OR l_cdl_line_type_tbl(i) ='D');
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE :Number of AP commitments updated '||SQL%ROWCOUNT);
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE : updating eligible AP commitments for liq encumbrance ');
UPDATE pa_bc_commitments bc_cm
SET bc_cm.bc_event_id = l_cdl_acct_event_id_tbl(i),
bc_cm.transfer_status_code = 'A',
bc_cm.request_id = p_request_id,
bc_cm.transferred_date = SYSDATE,
bc_cm.liquidate_gl_date = l_cdl_gl_Date_tbl(i),
bc_cm.exp_item_id = DECODE (l_cdl_line_type_tbl(i),'R',l_Exp_Item_Id_tbl(i),bc_cm.exp_item_id)-- Bug 5076612
WHERE (bc_cm.exp_item_id,bc_cm.expenditure_type)
IN ( SELECT peia.expenditure_item_id ,l_expenditure_type_tbl(i) -- Bug 5663343 : Handled transfer/split cases
FROM pa_expenditure_items_all peia
WHERE peia.TRANSFERRED_FROM_EXP_ITEM_ID IS NULL
START WITH peia.expenditure_item_id in (
SELECT cdl_raw.expenditure_item_id
FROM PA_Cost_Distribution_lines cdl_raw,
Pa_Expenditure_Items ei_raw,
pa_expenditures_all expend
WHERE cdl_raw.burden_sum_source_run_id = l_Burd_Sum_Dest_Run_Id_tbl(i)
AND cdl_raw.line_num = 1
AND cdl_raw.expenditure_item_id = ei_raw.expenditure_item_id
AND ei_raw.system_linkage_function in ('ST','OT','VI')
AND l_cdl_line_type_tbl(i) ='R'
-- Below join conditions added for bug 5263721
AND ei_raw.expenditure_id = expend.expenditure_id
AND nvl(l_billable_flag_tbl(i), -1) = nvl(cdl_raw.billable_flag, -1)
AND nvl(l_project_id_tbl(i), -1) = nvl(cdl_raw.project_id, -1)
AND nvl(l_task_id_tbl(i), -1) = nvl(cdl_raw.task_id, -1)
AND nvl(l_pa_period_name_tbl(i), -1) = nvl(cdl_raw.pa_period_name, -1)
AND nvl(l_denom_currency_code_tbl(i), -1) = nvl(ei_raw.denom_currency_code, -1)
AND nvl(l_acct_currency_code_tbl(i), -1) = nvl(ei_raw.acct_currency_code, -1)
AND nvl(l_project_currency_code_tbl(i), -1) = nvl(ei_raw.project_currency_code, -1)
AND nvl(l_projfunc_currency_code_tbl(i), -1) = nvl(ei_raw.projfunc_currency_code, -1)
/* AND nvl(l_system_reference1_tbl(i), -1) = nvl(cdl_raw.system_reference1, -1) bug 5453131*/
AND nvl(l_person_type_tbl(i), -1) = nvl(expend.person_type, -1)
AND nvl(l_po_line_id_tbl(i), -1) = nvl(ei_raw.po_line_id, -1)
AND nvl(PA_CLIENT_EXTN_BURDEN_SUMMARY.CLIENT_GROUPING(
null,
null,
l_attribute1_tbl(i),
l_attribute2_tbl(i),
l_attribute3_tbl(i),
l_attribute4_tbl(i),
l_attribute5_tbl(i),
l_attribute6_tbl(i),
l_attribute7_tbl(i),
l_attribute8_tbl(i),
l_attribute9_tbl(i),
l_attribute10_tbl(i),
l_attribute_category_tbl(i),
l_expenditure_item_date_tbl(i),
l_acct_rate_date_tbl(i),
l_acct_rate_type_tbl(i),
l_acct_exchange_rate_tbl(i),
l_project_rate_date_tbl(i),
l_project_rate_type_tbl(i),
l_project_exchange_rate_tbl(i),
l_projfunc_cost_rate_date_tbl(i),
l_projfunc_cost_rate_type_tbl(i),
l_pfc_ex_rate_tbl(i)),-1) =
nvl(PA_CLIENT_EXTN_BURDEN_SUMMARY.CLIENT_GROUPING(
null,
null,
ei_raw.attribute1,
ei_raw.attribute2,
ei_raw.attribute3,
ei_raw.attribute4,
ei_raw.attribute5,
ei_raw.attribute6,
ei_raw.attribute7,
ei_raw.attribute8,
ei_raw.attribute9,
ei_raw.attribute10,
ei_raw.attribute_category,
ei_raw.expenditure_item_date,
ei_raw.acct_rate_date,
ei_raw.acct_rate_type,
ei_raw.acct_exchange_rate,
ei_raw.project_rate_date,
ei_raw.project_rate_type,
ei_raw.project_exchange_rate,
ei_raw.projfunc_cost_rate_date,
ei_raw.projfunc_cost_rate_type,
ei_raw.projfunc_cost_exchange_rate),-1)
AND nvl(PA_CLIENT_EXTN_BURDEN_RESOURCE.CLIENT_GROUPING(
l_job_id_tbl(i),
l_non_labor_resource_tbl(i),
l_nl_res_orgn_id_tbl(i),
l_wip_resource_id_tbl(i),
l_incurred_by_person_id_tbl(i),
l_inventory_item_id_tbl(i)), -1) =
nvl(PA_CLIENT_EXTN_BURDEN_RESOURCE.CLIENT_GROUPING(
ei_raw.job_id,
ei_raw.non_labor_resource,
ei_raw.organization_id,
ei_raw.wip_resource_id,
expend.incurred_by_person_id,
ei_raw.inventory_item_id),-1)
)
CONNECT BY PRIOR peia.transferred_from_exp_item_id = peia.expenditure_item_id
UNION ALL
select l_Exp_Item_Id_tbl(i),l_expenditure_type_tbl(i)
from dual
where l_system_linkage_function_tbl(i) IN ('ST','OT','VI')
AND l_cdl_line_type_tbl(i) ='D')
and bc_cm.transfer_status_code in ('P','R','X')
and bc_cm.document_type = 'PO'
and bc_cm.burden_cost_flag = 'R'
and ((bc_cm.parent_bc_packet_id is not null AND l_cdl_line_type_tbl(i) ='R') OR l_cdl_line_type_tbl(i) ='D');
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE :Number of PO commitments updated '||SQL%ROWCOUNT);
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE : End');
pa_funds_control_pkg.log_message(p_msg_token1 => 'INTERFACE_TBC_BTC_COMT_UPDATE :Exception '||SQLERRM);
END INTERFACE_TBC_BTC_COMT_UPDATE;
SELECT sum(decode(parent_bc_packet_id,null,0,
decode(p_dr_cr,'A',abs(nvl(accounted_dr,0)-nvl(accounted_cr,0)),
'E',abs(nvl(entered_dr,0)-nvl(entered_cr,0)))))/
sum(decode(parent_bc_packet_id,null,
decode(p_dr_cr,'A',abs(nvl(accounted_dr,0)-nvl(accounted_cr,0)),
'E',abs(nvl(entered_dr,0)-nvl(entered_cr,0)))
,0))
INTO l_ratio
FROM pa_bc_packets pbc1
WHERE pbc1.packet_id = ( SELECT max(pbc.packet_id)
FROM pa_bc_packets pbc
WHERE pbc.document_distribution_id = l_document_distribution_id
AND pbc.document_header_id = l_document_header_id
AND pbc.document_type = l_document_type
AND pbc.parent_bc_packet_id is NULL
AND pbc.balance_posted_flag in ('N')
AND pbc.status_code in ('A','C')
AND substr(nvl(pbc.result_code,'P'),1,1) = 'P')
AND pbc1.document_distribution_id = l_document_distribution_id
AND pbc1.document_header_id = l_document_header_id
AND pbc1.document_type = l_document_type
AND pbc1.balance_posted_flag in ('N')
AND pbc1.status_code in ('A','C')
AND substr(nvl(pbc1.result_code,'P'),1,1) = 'P';
SELECT sum(decode(parent_bc_packet_id,null,0,
decode(p_dr_cr,'A',abs(nvl(accounted_dr,0)-nvl(accounted_cr,0)),
'E',abs(nvl(entered_dr,0)-nvl(entered_cr,0)))))/
sum(decode(parent_bc_packet_id,null,
decode(p_dr_cr,'A',abs(nvl(accounted_dr,0)-nvl(accounted_cr,0)),
'E',abs(nvl(entered_dr,0)-nvl(entered_cr,0)))
,0))
INTO l_ratio
FROM pa_bc_commitments comm1
WHERE comm1.packet_id = ( SELECT max(comm.packet_id)
FROM pa_bc_commitments comm
WHERE comm.document_distribution_id = l_document_distribution_id
AND comm.document_header_id = l_document_header_id
AND comm.document_type = l_document_type
AND comm.parent_bc_packet_id is NULL)
AND comm1.document_distribution_id = l_document_distribution_id
AND comm1.document_header_id = l_document_header_id
AND comm1.document_type = l_document_type;
l_update_login NUMBER := FND_GLOBAL.login_id;
SELECT decode('Confirmed','CC_C_PAY',
'Provisional','CC_P_PAY'
) document_type,
gl.last_update_date,
gl.last_updated_by,
gl.ledger_id set_of_books_id,
gl.je_source_name,
gl.je_category_name,
gl.reference1,
gl.reference2,
gl.reference3,
gl.reference4,
gl.reference5,
gl.actual_flag,
gl.period_name,
gl.period_year,
gl.period_num,
NVL(gl.entered_dr,0),
NVL(gl.entered_cr,0),
NVL(gl.accounted_dr,0),
NVL(gl.accounted_cr,0),
gl.ROWID, --gl_row_bc_packet_row_id
gl.code_combination_id,
NULL , --reference1
NULL , --reference2
NULL --reference3
FROM gl_bc_packets gl
WHERE gl.packet_id = p_packet_id
AND gl.je_source_name = 'Contract Commitment'
and gl.je_category_name in ('Confirmed','Provisional')
and EXISTS
( SELECT 'Project Related'
FROM pa_tasks pkt,
pa_projects_all pp,
igc_cc_acct_lines igc,
igc_cc_det_pf igcpf,
igc_cc_headers_all igchead,
pa_implementations_all imp
WHERE igc.cc_header_id = gl.reference1
AND igchead.cc_header_id = igc.cc_header_id
AND igcpf.cc_det_pf_line_id = gl.reference4
AND igc.cc_acct_line_id = igcpf.cc_acct_line_id
AND igc.project_id IS NOT NULL
AND igc.project_id = pp.project_id
AND igc.task_id = pkt.task_id
AND pkt.project_id = pp.project_id
AND nvl(pp.org_id, -99) = nvl(imp.org_id, -99)
AND imp.set_of_books_id = gl.ledger_Id
AND EXISTS
( select 'Project Bdgt Ctrl enabled'
from pa_budget_types bdgttype
,pa_budgetary_control_options pbct
where pbct.project_id = pp.project_id
and pbct.BDGT_CNTRL_FLAG = 'Y'
and (pbct.EXTERNAL_BUDGET_CODE = 'GL'
OR
pbct.EXTERNAL_BUDGET_CODE is NULL
)
and pbct.BUDGET_TYPE_CODE = bdgttype.budget_type_code
and bdgttype.budget_amount_code = 'C'
)
);*/
SELECT decode(igchead.CC_STATE,'PR','CC_P_PAY','CC_C_PAY') document_type,
igc.last_update_date,
igc.last_updated_by,
igc.set_of_books_id,
'Contract Commitment' JeSourceName,
decode(igchead.CC_STATE,'PR','Provisional','Confirmed') Category,
to_char(igc.cc_header_id),
NULL,
NULL,
to_char(igc.cc_det_pf_line_id),
NULL,
igc.CC_TRANSACTION_DATE gl_date,
igc.actual_flag,
glp.period_name,
glp.period_year,
glp.period_num,
NVL(igc.cc_func_dr_amt,0) entered_dr,
NVL(igc.cc_func_cr_amt,0) entered_cr,
NVL(igc.cc_func_dr_amt,0) accounted_dr,
NVL(igc.cc_func_cr_amt,0) accounted_cr,
igc.ROWID,
igc.code_combination_id,
igc.event_id,
NULL ,
NULL ,
NULL
FROM igc_cc_interface igc,
igc_cc_headers_all igchead,
psa_bc_xla_events_gt pbgt,
gl_period_statuses glp
WHERE pbgt.event_id = igc.event_id
and igc.cc_header_id = igchead.cc_header_id
and igc.budget_dest_flag = 'S'
and glp.application_id = 101
and glp.closing_status = 'O'
and glp.adjustment_period_flag = 'N'
and igc.cc_transaction_date between glp.start_date and glp.end_date
and glp.set_of_books_id = igc.set_of_books_id
and exists (select 1 from igc_cc_acct_lines igcc where igcc.cc_acct_line_id = igc.cc_acct_line_id
and project_id >0);
g_tab_last_update_date,
g_tab_last_updated_by,
g_tab_set_of_books_id,
g_tab_je_source_name,
g_tab_je_category_name,
g_tab_reference1,
g_tab_reference2,
g_tab_reference3,
g_tab_reference4,
g_tab_reference5,
g_tab_gl_date,
g_tab_actual_flag,
g_tab_period_name,
g_tab_period_year,
g_tab_period_num,
g_tab_entered_dr,
g_tab_entered_cr,
g_tab_accounted_dr,
g_tab_accounted_cr,
g_tab_rowid,
g_tab_trxn_ccid,
g_tab_event_id,
g_tab_pkt_reference1,
g_tab_pkt_reference2,
g_tab_pkt_reference3;
SELECT p_reference1 document_header_id,
igc.cc_acct_line_id document_distribution_id,
NULL budget_version_id,
igc.project_id,
igc.task_id,
igc.expenditure_type,
igc.expenditure_org_id,
trunc(igc.expenditure_item_date),
igchead.org_id,
'N' balance_posted_flag,
'T' funds_process_mode,
'N' burden_cost_flag,
NULL result_code,
'I' status_code,
pa_funds_control_utils.get_fnd_reqd_flag(pp.project_id ,'STD') fck_reqd_flag,
null parent_bc_packet_id,
NULL main_or_backing_code,
pa_funds_control_pkg.check_bdn_on_sep_item(igc.project_id) burden_method_code,
NULL budget_line_id,
NULL source_event_id,
NULL distribution_type,
NULL po_release_Id,
PA_FUNDS_CONTROL_UTILS.get_encum_type_id(igc.project_id,'STD') enc_type_id,
igchead.vendor_id,
pa_bc_packets_s.nextval
FROM pa_tasks pkt,
pa_projects_all pp,
igc_cc_acct_lines igc,
igc_cc_det_pf igcpf,
igc_cc_headers_all igchead
WHERE igc.cc_header_id = p_reference1
AND igchead.cc_header_id = igc.cc_header_id
AND igcpf.cc_det_pf_line_id = p_reference4
AND igc.cc_acct_line_id = igcpf.cc_acct_line_id
AND igc.project_id IS NOT NULL
AND igc.project_id = pp.project_id
AND igc.task_id = pkt.task_id
AND pkt.project_id = pp.project_id
AND EXISTS ( select 'Project Bdgt Ctrl enabled'
from pa_budget_types bdgttype
,pa_budgetary_control_options pbct
where pbct.project_id = pp.project_id
and pbct.BDGT_CNTRL_FLAG = 'Y'
and (pbct.EXTERNAL_BUDGET_CODE = 'GL'
OR
pbct.EXTERNAL_BUDGET_CODE is NULL
)
and pbct.BUDGET_TYPE_CODE = bdgttype.budget_type_code
and bdgttype.budget_amount_code = 'C'
);
SELECT
igci.last_update_date,
igci.last_updated_by,
igci.last_updated_by,
NULL budget_version_id,
igc.project_id,
igc.task_id,
igc.expenditure_type,
igc.expenditure_org_id,
trunc(igc.expenditure_item_date),
igci.set_of_books_id,
'Contract Commitment' JeSourceName, -- igci.je_source_name,
decode(igchead.CC_STATE,'PR','Provisional','Confirmed') Category, -- igci.je_category_name,
decode(decode(igchead.CC_STATE,'PR','Provisional','Confirmed'),'Confirmed','CC_C_CO',
'Provisional','CC_P_CO')
document_type,
igci.cc_header_id,
igci.cc_acct_line_id,
igci.actual_flag,
igci.cc_acct_line_id line_id,
NULL event_id,
igchead.vendor_id,
NULL main_or_backing_code,
pa_funds_control_pkg.check_bdn_on_sep_item(igc.project_id) burden_method_code,
NULL budget_line_id,
igci.event_id source_event_id,
igc.CC_ACCT_ENCMBRNC_DATE gl_date,
NULL distribution_type,
NULL po_release_Id,
PA_FUNDS_CONTROL_UTILS.get_encum_type_id(igc.project_id,'STD') enc_type_id,
gl.period_name, --igci.period_name,
NULL period_year, --igci.period_year,
NULL period_num, --igci.period_num,
NULL result_code, -- result_code
'P' status_code, -- status_code,
NVL(igci.cc_func_dr_amt,0),
NVL(igci.cc_func_cr_amt,0),
NVL(igci.cc_func_dr_amt,0),
NVL(igci.cc_func_cr_amt,0),
igci.ROWID, --gl_row_bc_packet_row_id
'N' balance_posted_flag,
'T' funds_process_mode, -- T - transaction B- base line
igci.code_combination_id,
'N' burden_cost_flag, -- original transaction (raw)
igchead.org_id,
pa_funds_control_utils.get_fnd_reqd_flag
(pp.project_id ,'STD') fck_reqd_flag,
null parent_bc_packet_id,
decode(igci.je_category_name,'Confirmed','CC_C_CO',
'Provisional','CC_P_CO')
pkt_reference1,
igci.cc_header_id pkt_reference2,
igci.cc_acct_line_id pkt_reference3,
pa_bc_packets_s.nextval
FROM
pa_tasks pkt,
pa_projects_all pp,
igc_cc_interface igci,
igc_cc_acct_lines igc,
igc_cc_headers_all igchead,
gl_period_statuses gl,
pa_implementations_all imp
WHERE igc.cc_header_id = p_reference2
AND p_reference1 = 'CC'
AND igchead.cc_header_id = igc.cc_header_id
AND igc.project_id IS NOT NULL
AND igc.project_id = pp.project_id
AND igc.cc_header_id = igci.cc_header_id
AND igc.cc_acct_line_id = igci.cc_acct_line_id
AND pkt.task_id = igc.task_id
AND pkt.project_id = pp.project_id
AND gl.application_id = 101
ANd gl.set_of_books_id = igci.set_of_books_id
AND gl.ADJUSTMENT_PERIOD_FLAG <> 'Y'
AND trunc(igci.cc_transaction_date)
between gl.start_date and gl.end_date
AND nvl(pp.org_id, -99) = nvl(imp.org_id, -99)
AND imp.set_of_books_id = (SELECT imp1.set_of_books_id
FROM pa_implementations_all imp1 where org_id = pp.org_id)
AND EXISTS ( select 'Project Bdgt Ctrl enabled'
from pa_budget_types bdgttype
,pa_budgetary_control_options pbct
where pbct.project_id = pp.project_id
and pbct.BDGT_CNTRL_FLAG = 'Y'
and pbct.EXTERNAL_BUDGET_CODE = 'CC'
and pbct.BUDGET_TYPE_CODE = bdgttype.budget_type_code
and bdgttype.budget_amount_code = 'C'
);
g_tab_last_update_date,
g_tab_last_updated_by,
g_tab_last_updated_by,
g_tab_budget_version_id,
g_tab_project_id,
g_tab_task_id,
g_tab_exp_type,
g_tab_exp_org_id,
g_tab_exp_item_date,
g_tab_set_of_books_id,
g_tab_je_source_name,
g_tab_je_category_name,
g_tab_doc_type,
g_tab_doc_header_id,
g_tab_doc_distribution_id,
g_tab_actual_flag,
g_tab_doc_line_id,
g_tab_event_id,
g_tab_vendor_id,
g_tab_main_or_backing_code,
g_tab_burden_method_code,
g_tab_budget_line_id,
g_tab_source_event_id,
g_tab_gl_date,
g_tab_distribution_type,
g_tab_po_release_id,
g_tab_enc_type_id,
g_tab_period_name,
g_tab_period_year, --igci.period_year,
g_tab_period_num, --igci.period_num,
g_tab_result_code, -- result_code
g_tab_status_code, -- status_code,
g_tab_entered_dr,
g_tab_entered_cr,
g_tab_accounted_dr,
g_tab_accounted_cr,
g_tab_rowid,
g_tab_balance_posted_flag,
g_tab_funds_process_mode, -- T - transaction B- base line
g_tab_trxn_ccid,
g_tab_burden_cost_flag, -- original transaction (raw)
g_tab_org_id,
g_tab_fck_reqd_flag,
g_tab_p_bc_packet_id,
g_tab_pkt_reference1,
g_tab_pkt_reference2,
g_tab_pkt_reference3,
g_tab_bc_packet_id;