[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_expenditure_item_id.delete;
g_adjusted_expenditure_item_id.delete;
g_system_linkage_function.delete;
g_PERIOD_ACCRUAL_FLAG.delete;
g_cdl_rowid.delete;
g_gl_date_new_tab.delete;
g_gl_period_new_tab.delete;
g_line_num.delete;
g_cdl_line_type.delete;
g_liquidate_encum_flag.delete;
g_buren_Sum_Dest_Run_Id.delete;
g_document_header_Id.delete;
g_document_distribution_Id.delete;
g_expenditure_type.delete;
g_cdl_acct_event_id.delete;
Is Select EV.Event_Id From xla_entity_events_v EV
where EV.EVENT_DATE between p_start_date and p_end_Date
and EV.process_status_code <> 'P'
and EV.security_id_int_1 = g_org_id
and EV.Application_Id = 275
and EV.EVENT_TYPE_CODE IN ( SELECT EVENT_TYPE_CODE
from XLA_EVENT_TYPES_VL
Where
( ( ENTITY_CODE = 'EXPENDITURES'
AND P_TRAN_TYPE = 'EXPENDITURES'
AND EVENT_CLASS_CODE NOT IN ('BORROWED_AND_LENT',
'PRVDR_RECVR_RECLASS')
)
OR
(
ENTITY_CODE = 'EXPENDITURES'
AND P_TRAN_TYPE = 'CROSSCHARGE'
AND EVENT_CLASS_CODE IN ('BORROWED_AND_LENT',
'PRVDR_RECVR_RECLASS')
)
OR
(
ENTITY_CODE = 'REVENUE'
AND P_TRAN_TYPE = 'REVENUE'
)
OR
(
P_TRAN_TYPE Is Null
AND ENTITY_CODE IN ('EXPENDITURES', 'REVENUE')
)
)
AND Application_ID = 275
);
Select set_of_books_id
into g_sob_id
from pa_implementations_all where org_id = g_org_id;
Select START_DATE, End_Date into p_first_date , p_last_date
From gl_period_statuses
where period_name = P_GL_PERIOD
and set_of_books_id = g_sob_id
and application_id = g_app_id ;
g_event_tab.delete;
Update PA_COST_DISTRIBUTION_LINES_ALL
SET TRANSFER_STATUS_CODE = 'X' ,
Request_Id = g_request_id
Where Acct_Event_ID = g_event_tab(i);
Log_Message ( SQL%ROWCOUNT || ' Updated CDLs with X');
Update pa_cc_dist_lines
SET TRANSFER_STATUS_CODE = 'X' ,
Request_Id = g_request_id
Where Acct_Event_ID = g_event_tab(i);
Log_Message ( SQL%ROWCOUNT || ' Updated CCDLs with X');
Update PA_DRAFT_REVENUES_ALL
SET TRANSFER_STATUS_CODE = 'X' ,
Request_Id = g_request_id
Where Event_ID = g_event_tab(i);
Log_Message ( SQL%ROWCOUNT || ' Updated RDLs with X');
Update XLA_EVENTS
Set EVENT_DATE = g_new_period_date ,
TRANSACTION_DATE = g_new_period_date ,
LAST_UPDATE_DATE = Sysdate ,
LAST_UPDATED_BY = fnd_global.user_id ,
LAST_UPDATE_LOGIN = fnd_global.user_id ,
request_id = g_request_id
Where event_id = g_event_tab(i);
Log_Message ( SQL%ROWCOUNT || ' Event(s) updated.');
Update XLA_AE_HEADERS
SET ACCOUNTING_DATE = g_new_period_date ,
PERIOD_NAME = g_new_period_name ,
LAST_UPDATE_DATE = Sysdate ,
LAST_UPDATED_BY = fnd_global.user_id ,
LAST_UPDATE_LOGIN = fnd_global.user_id ,
request_id = g_request_id
Where event_id = g_event_tab(i);
Log_Message ( SQL%ROWCOUNT || ' Header(s) updated.');
SELECT
ei.expenditure_item_id,
cdl.billable_flag,
cdl.line_type,
cdl.line_num,
ei.transaction_source,
tr.gl_accounted_flag,
ei.denom_currency_code,
ei.acct_currency_code,
ei.acct_rate_date,
ei.acct_rate_type,
ei.acct_exchange_rate,
ei.project_currency_code,
ei.project_rate_date,
ei.project_rate_type,
ei.project_exchange_rate,
tr.system_linkage_function,
ei.projfunc_currency_code,
ei.projfunc_cost_rate_date,
ei.projfunc_cost_rate_type,
ei.projfunc_cost_exchange_rate,
ei.work_type_id
FROM pa_expenditure_items_all ei,
pa_cost_distribution_lines_all cdl,
pa_transaction_sources tr
WHERE tr.transaction_source(+) = ei.transaction_source
AND ei.expenditure_item_id = cdl.expenditure_item_id
AND CDL.Transfer_Status_Code = 'Y'
AND CDL.request_id = g_request_id;
Select
ei.expenditure_item_id ,
ei.adjusted_expenditure_item_id ,
ei.system_linkage_function ,
exp_grp.PERIOD_ACCRUAL_FLAG ,
rowidtochar(CDL.rowid) ROW_ID ,
cdl.line_num ,
g_new_period_date GL_DATE,
g_new_period_name GL_PERIOD_NAME,
cdl.recvr_gl_date ,
IMP.set_of_books_id recvr_sob_id,
nvl(EI.recvr_org_id,CDL.org_id) recvr_org_id,
-- R12 Funds Management uptake
cdl.line_type ,
cdl.liquidate_encum_flag ,
ei.Burden_Sum_Dest_Run_Id ,
ei.document_header_id ,
ei.document_distribution_id ,
ei.expenditure_type ,
cdl.Acct_Event_ID
From PA_Cost_Distribution_lines_ALL CDL,
PA_Expenditure_items_all EI,
PA_IMPLEMENTATIONS_ALL IMP ,
PA_EXPENDITURES_ALL EXP ,
PA_EXPENDITURE_GROUPS_ALL EXP_GRP
Where CDL.Transfer_Status_Code = 'X'
AND CDL.expenditure_item_id = EI.expenditure_item_id
AND nvl(EI.recvr_org_id,CDL.org_id) = IMP.ORG_ID
AND EXP.EXPENDITURE_ID = EI.EXPENDITURE_ID
AND EXP_GRP.EXPENDITURE_GROUP = EXP.EXPENDITURE_GROUP
AND EXP_GRP.ORG_ID = EXP.ORG_ID
AND CDL.REQUEST_ID = g_request_id
AND CDL.ORG_ID = g_org_id
AND EI.ORG_ID = g_org_id ;
UPDATE PA_Cost_Distribution_lines_ALL CDL
SET CDL.request_id = g_request_id,
CDL.transfer_status_code = DECODE(g_gl_date_new_tab(i),
NULL,'R', DECODE(g_recvr_gl_date_new_tab(i)
,NULL,'R',
DECODE ( DECODE ( CDL.LINE_TYPE ,'R', CDL.PJI_SUMMARIZED_FLAG, 'N'),
'N', 'A',
DECODE (CDL.gl_date,g_gl_date_new_tab(i), 'A','Y')
)
)
)
,CDL.gl_date = DECODE ( DECODE ( CDL.LINE_TYPE ,'R', CDL.PJI_SUMMARIZED_FLAG, 'N'),
'N', nvl(g_gl_date_new_tab(i),CDL.gl_date) ,
CDL.GL_DATE)
,CDL.gl_period_name = DECODE ( DECODE ( CDL.LINE_TYPE ,'R', CDL.PJI_SUMMARIZED_FLAG, 'N'),
'N', nvl(g_gl_period_new_tab(i),CDL.gl_period_name),
CDL.gl_period_name)
,CDL.recvr_gl_date = DECODE ( DECODE ( CDL.LINE_TYPE ,'R', CDL.PJI_SUMMARIZED_FLAG, 'N'),
'N', nvl(g_recvr_gl_date_new_tab(i),CDL.recvr_gl_date),
CDL.recvr_gl_date)
,CDL.recvr_gl_period_name = DECODE ( DECODE ( CDL.LINE_TYPE ,'R', CDL.PJI_SUMMARIZED_FLAG, 'N'),
'N', nvl(g_recvr_gl_period_new_tab(i),CDL.recvr_gl_period_name),
CDL.recvr_gl_period_name)
WHERE CDL.Expenditure_Item_Id = g_expenditure_item_id(i)
AND CDL.Line_nUm = g_line_num(i)
AND CDL.Transfer_Status_Code = 'X';
log_message ( 'Total CDLs updated ' || SQL%ROWCOUNT);
UPDATE PA_Cost_Distribution_lines_ALL CDL
SET CDL.request_id = g_request_id
,CDL.transfer_status_code = 'A'
,CDL.gl_date = nvl(g_gl_date_new_tab(i),CDL.gl_date)
,CDL.gl_period_name = nvl(g_gl_period_new_tab(i),CDL.gl_period_name)
,CDL.recvr_gl_date = nvl(g_recvr_gl_date_new_tab(i),CDL.recvr_gl_date)
,CDL.recvr_gl_period_name = nvl(g_recvr_gl_period_new_tab(i),CDL.recvr_gl_period_name)
WHERE CDL.Transfer_Status_Code = 'Y'
AND CDL.reversed_flag is NULL
AND CDL.Expenditure_Item_Id = g_expenditure_item_id(i)
AND CDL.Line_nUm = g_line_num(i);
log_message ( SQL%ROWCOUNT || ' rows updated from TSC = Y to A for PJI Summarized Lines');
Update PA_Draft_Revenues_All
set gl_date = g_new_period_date ,
gl_period_name = g_new_period_name ,
transfer_status_code = 'A'
Where request_id = g_request_id
and transfer_status_code = 'X';
log_message ( SQL%ROWCOUNT || ' RDLs updated for GL-Date Rederivation.');
Update pa_cc_dist_lines
set gl_date = g_new_period_date ,
gl_period_name = g_new_period_name ,
transfer_status_code = 'A'
Where request_id = g_request_id
and transfer_status_code = 'X';
log_message ( SQL%ROWCOUNT || ' CCDLs updated for GL-Date Rederivation.');
SELECT
ei.expenditure_item_id,
ei.adjusted_expenditure_item_id,
cdl.gl_date,
cdl.pji_summarized_flag,
cdl.billable_flag,
cdl.line_type,
cdl.line_num,
ei.denom_currency_code,
ei.acct_currency_code,
ei.acct_rate_date,
ei.acct_rate_type,
ei.acct_exchange_rate,
ei.project_currency_code,
ei.project_rate_date,
ei.project_rate_type,
ei.project_exchange_rate,
ei.projfunc_currency_code,
ei.projfunc_cost_rate_date,
ei.projfunc_cost_rate_type,
ei.projfunc_cost_exchange_rate,
ei.work_type_id ,
cdl.acct_event_id ,
cdl.transfer_status_Code ,
cdl.org_id
INTO l_exp_item_id,
l_adj_exp_item_id,
l_gl_date,
l_pji_summarized_flag,
l_billable_flag,
l_line_type,
l_line_num,
l_denom_currency_code,
l_acct_currency_code,
l_acct_rate_date,
l_acct_rate_type,
l_acct_exchange_rate,
l_project_currency_code,
l_project_rate_date,
l_project_rate_type,
l_project_exchange_rate,
l_projfunc_currency_code,
l_projfunc_cost_rate_date,
l_projfunc_cost_rate_type,
l_projfunc_cost_exchange_rate,
l_work_type_id,
l_acct_event_id,
l_transfer_status_code,
l_org_id
FROM PA_COST_DISTRIBUTION_LINES_ALL CDL,
PA_EXPENDITURE_ITEMS_ALL EI
WHERE CDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND EI.ADJUSTED_EXPENDITURE_ITEM_ID = g_expenditure_item_id(g_currec);
SELECT GPS.start_date
INTO l_prvdr_accr_date
FROM gl_period_statuses GPS
WHERE GPS.application_id = 101
AND GPS.set_of_books_id = g_sob_id
AND GPS.adjustment_period_flag = 'N'
AND GPS.start_date = (SELECT min(GPS1.start_date)
FROM gl_period_statuses GPS1
WHERE GPS1.application_id = 101
AND GPS1.set_of_books_id = g_sob_id
AND GPS1.adjustment_period_flag = 'N'
AND GPS1.start_date > g_gl_date_new_tab(g_currec)
);
UPDATE PA_Cost_Distribution_lines_ALL CDL
SET CDL.gl_date = l_prvdr_accr_date
WHERE CDL.EXPENDITURE_ITEM_ID = l_exp_item_id;
log_message ('Reversing EI is not PJI Summarised. Updated with new GL-Date');
UPDATE PA_Cost_Distribution_lines_ALL CDL
SET CDL.GL_DATE = l_prvdr_accr_date,
CDL.GL_PERIOD_NAME = l_accr_period_name
WHERE CDL.EXPENDITURE_ITEM_ID = l_exp_item_id
AND CDL.LINE_NUM_REVERSED IS NULL
AND CDL.TRANSFER_STATUS_CODE in ('P','R','G','A');
log_message ('CDLs of Reversing EI updated.');
Update XLA_EVENTS
Set EVENT_DATE = l_prvdr_accr_date ,
TRANSACTION_DATE = l_prvdr_accr_date,
Request_ID = g_request_id ,
LAST_UPDATE_DATE = Sysdate ,
LAST_UPDATED_BY = fnd_global.user_id ,
LAST_UPDATE_LOGIN = fnd_global.user_id
Where event_id = l_acct_event_id;
log_message ('Event updated for reversing EI.');
Update XLA_AE_HEADERS
SET ACCOUNTING_DATE = l_prvdr_accr_date ,
PERIOD_NAME = l_accr_period_name ,
Request_ID = g_request_id,
LAST_UPDATE_DATE = Sysdate ,
LAST_UPDATED_BY = fnd_global.user_id ,
LAST_UPDATE_LOGIN = fnd_global.user_id
Where event_id = l_acct_event_id;
log_message ('Header updated for reversing EI.');
UPDATE pa_bc_commitments bc_cm
SET bc_cm.request_id = g_request_id,
bc_cm.transferred_date = SYSDATE,
bc_cm.liquidate_gl_date = g_gl_date_new_tab(i)
WHERE ( bc_cm.document_header_id,bc_cm.document_distribution_id,expenditure_type)
IN ( SELECT exp.document_header_id,exp.document_distribution_id,exp.expenditure_type
FROM PA_Cost_Distribution_lines cdl_raw,
pa_expenditure_items_all exp
WHERE cdl_raw.burden_sum_source_run_id = g_buren_Sum_Dest_Run_Id(i)
AND exp.expenditure_item_id = cdl_raw.expenditure_item_id
AND cdl_raw.line_num = 1
AND g_cdl_line_type(i) ='R'
UNION ALL
SELECT g_document_header_id(i),g_document_distribution_id(i),g_expenditure_type(i)
FROM dual
WHERE g_cdl_line_type(i) ='D' )
AND bc_cm.transfer_status_code = 'A'
AND bc_cm.bc_event_id = g_cdl_acct_event_id(i)
AND bc_cm.document_type = 'AP'
AND bc_cm.burden_cost_flag = 'R'
AND ((bc_cm.parent_bc_packet_id IS NOT NULL AND g_cdl_line_type(i) ='R') OR g_cdl_line_type(i) ='D')
AND NVL(g_liquidate_encum_flag(i),'N') = 'Y'
AND ((g_system_linkage_function(i) = 'BTC' AND g_cdl_line_type(i) = 'R' ) OR
(g_system_linkage_function(i) IN ('VI','ST','OT') AND g_cdl_line_type(i) = 'D'));
log_message ( 'Total AP commitment records updated ' || SQL%ROWCOUNT);
UPDATE pa_bc_commitments bc_cm
SET bc_cm.request_id = g_request_id,
bc_cm.transferred_date = SYSDATE,
bc_cm.liquidate_gl_date = g_gl_date_new_tab(i)
WHERE (bc_cm.exp_item_id,bc_cm.expenditure_type)
IN ( SELECT cdl_raw.expenditure_item_id,ei_raw.expenditure_type
FROM PA_Cost_Distribution_lines cdl_raw,
Pa_Expenditure_Items ei_raw
WHERE cdl_raw.burden_sum_source_run_id = g_buren_Sum_Dest_Run_Id(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 g_cdl_line_type(i) ='R'
UNION ALL
SELECT g_expenditure_item_id(i),g_expenditure_type(i)
FROM dual
WHERE g_system_linkage_function(i) IN ('ST','OT','VI')
AND g_cdl_line_type(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 g_cdl_line_type(i) ='R') OR g_cdl_line_type(i) ='D')
AND NVL(g_liquidate_encum_flag(i),'N') = 'Y'
AND ((g_system_linkage_function(i) = 'BTC' AND g_cdl_line_type(i) = 'R' ) OR
(g_system_linkage_function(i) IN ('VI','ST','OT') AND g_cdl_line_type(i) = 'D'));
log_message ( 'Total AP commitment records updated ' || SQL%ROWCOUNT);