[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert /*+ append * into PJI_FM_RMAP_FIN
(
WORKER_ID
, MAP_ROWID
, PROJECT_ID
, ACTIVITY_MIN_GL_DATE
, ACTIVITY_MIN_PA_DATE
, REVENUE
)
select /*+ ordered
full(fin9) use_hash(fin9) parallel(fin9)
full(map) use_hash(map) swap_join_inputs(map)
*
p_worker_id
, map.rowid
, map.project_id
-- temptemp should consider GL and PA seperately
, to_date(min(fin9.TIME_ID), 'J')
, to_date(min(fin9.TIME_ID), 'J')
-- , to_date(min(fin9.RECVR_GL_TIME_ID),'J')
-- , to_date(min(fin9.RECVR_PA_TIME_ID),'J')
, sum(abs(fin9.POU_REVENUE))
from pji_pji_proj_batch_map map
, PJI_FM_AGGR_FIN9 fin9
where 1 = 1
and map.worker_id = p_worker_id
and fin9.project_id = map.project_id
-- temptemp should consider GL and PA seperately
and nvl(fin9.TIME_ID, 0) > 0
-- and nvl(fin9.RECVR_GL_TIME_ID,0) > 0
-- and nvl(fin9.RECVR_PA_TIME_ID,0) > 0
group by
map.rowid
, map.project_id
;Commented for bug 13011859 */
procedure UPDATE_ACTIVITY_DATES_FIN (p_worker_id in number) is
l_process varchar2(30);
if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_BKLG.UPDATE_ACTIVITY_DATES_FIN(p_worker_id);')) then
UPDATE /*+ index(map, PJI_PJI_PROJ_BATCH_MAP_N1) *
pji_pji_proj_batch_map map
SET ( map.ACTIVITY_MIN_GL_DATE
, map.ACTIVITY_MIN_PA_DATE) =
(select scope.ACTIVITY_MIN_GL_DATE
, scope.ACTIVITY_MIN_PA_DATE
from PJI_FM_RMAP_FIN scope
where scope.worker_id = p_worker_id
and scope.MAP_ROWID = map.rowid
)
WHERE 1 = 1
AND map.WORKER_ID = p_worker_id
AND map.PROJECT_ID in (select scope2.PROJECT_ID
from PJI_FM_RMAP_FIN scope2
where scope2.worker_id = p_worker_id
and ((l_extraction_type <> 'INCREMENTAL')
or
scope2.REVENUE <>0
)
)
;Commented for bug 13011859 */
PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_BKLG.UPDATE_ACTIVITY_DATES_FIN(p_worker_id);');
end UPDATE_ACTIVITY_DATES_FIN;
/* insert /*+ append * into PJI_FM_RMAP_ACT
(
WORKER_ID
, MAP_ROWID
, PROJECT_ID
, FUNDING_MIN_DATE
, FUNDING
)
select /* ordered
full(act5) use_hash(act5) parallel(act5)
full(map) use_hash(map) parallel(map)
*
p_worker_id
, map.rowid
, map.project_id
-- temptemp should consider GL and PA seperately
, to_date(min(act5.TIME_ID), 'J')
-- , to_date(min(LEAST(act5.GL_TIME_ID,act5.PA_TIME_ID)),'J')
, sum(abs(act5.POU_FUNDING))
from PJI_FM_AGGR_ACT5 act5
, pji_pji_proj_batch_map map
where 1 = 1
and act5.worker_id = p_worker_id
and act5.project_id = map.project_id
and map.worker_id = p_worker_id
group by
map.rowid
, map.project_id
;Commented for bug 13011859 */
procedure UPDATE_ACTIVITY_DATES_ACT (p_worker_id in number) is
l_process varchar2(30);
if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_BKLG.UPDATE_ACTIVITY_DATES_ACT(p_worker_id);')) then
UPDATE /*+ index(map, PJI_PJI_PROJ_BATCH_MAP_N1) *
pji_pji_proj_batch_map map
SET ( map.FUNDING_MIN_DATE ) =
(select scope.FUNDING_MIN_DATE
from PJI_FM_RMAP_ACT scope
where scope.worker_id = p_worker_id
and scope.MAP_ROWID = map.rowid
)
WHERE 1 = 1
AND map.WORKER_ID = p_worker_id
AND map.PROJECT_ID in (select scope2.PROJECT_ID
from PJI_FM_RMAP_ACT scope2
where scope2.worker_id = p_worker_id
and ((l_extraction_type <> 'INCREMENTAL')
or
scope2.FUNDING <>0
)
)
;Commented for bug 13011859 */
PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_BKLG.UPDATE_ACTIVITY_DATES_ACT(p_worker_id);');
end UPDATE_ACTIVITY_DATES_ACT;
UPDATE PJI_PJI_PROJ_BATCH_MAP upd
set upd.BACKLOG_EXTRACTION_STATUS=null
where upd.project_id in
(
select /*+ ORDERED
full(fin2)
*
distinct
map.project_id project_id
From
PJI_PJI_PROJ_BATCH_MAP map
, PJI_FM_AGGR_ACT3 act3
Where 1=1
and map.worker_id = p_worker_id
and map.project_id = act3.project_id
and (
(abs(nvl(act3.initial_funding_amount, 0))
+abs(nvl(act3.additional_funding_amount, 0))
+abs(nvl(act3.cancelled_funding_amount, 0))
+abs(nvl(act3.funding_adjustment_amount, 0))
) > 0
OR
abs(act3.revenue) > 0
OR
nvl(map.OLD_CLOSED_DATE,sysdate) <> nvl(map.NEW_CLOSED_DATE,sysdate)
OR
l_extraction_type <> 'INCREMENTAL'
)
)
;Commented for bug 13011859 */
Procedure Insert_Backlog_Updates_pvt
( p_worker_id IN number
, p_project_id IN number
, p_time_id IN number
, p_curr_record_type_id IN number
, p_currency_code IN varchar2
, p_gl_calendar_id IN number
, p_pa_calendar_id IN number
, p_drmt_bklg IN number
, p_strt_bklg IN number
, p_lost_bklg IN number
, p_actv_bklg IN number
, p_risky_rev IN number
, p_project_org_id IN number
, p_project_organization_id IN number
, p_calendar_type IN varchar2
, p_ex_drmt_bklg IN number
, p_ex_strt_bklg IN number
, p_ex_lost_bklg IN number
, p_ex_actv_bklg IN number
, p_ex_risky_rev IN number
) is
Begin
null;
/* INSERT INTO PJI_FM_AGGR_ACT3(
WORKER_ID
, PROJECT_ID
, PROJECT_ORG_ID
, PROJECT_ORGANIZATION_ID
, TIME_ID
, PERIOD_TYPE_ID
, CALENDAR_TYPE
, GL_CALENDAR_ID
, PA_CALENDAR_ID
, CURR_RECORD_TYPE_ID
, CURRENCY_CODE
, REVENUE
, FUNDING
, INITIAL_FUNDING_AMOUNT
, INITIAL_FUNDING_COUNT
, ADDITIONAL_FUNDING_AMOUNT
, ADDITIONAL_FUNDING_COUNT
, CANCELLED_FUNDING_AMOUNT
, CANCELLED_FUNDING_COUNT
, FUNDING_ADJUSTMENT_AMOUNT
, FUNDING_ADJUSTMENT_COUNT
, REVENUE_WRITEOFF
, AR_INVOICE_AMOUNT
, AR_INVOICE_COUNT
, AR_CASH_APPLIED_AMOUNT
, AR_CASH_APPLIED_COUNT
, AR_INVOICE_WRITEOFF_AMOUNT
, AR_INVOICE_WRITEOFF_COUNT
, AR_CREDIT_MEMO_AMOUNT
, AR_CREDIT_MEMO_COUNT
, UNBILLED_RECEIVABLES
, UNEARNED_REVENUE
, AR_UNAPPR_INVOICE_AMOUNT
, AR_UNAPPR_INVOICE_COUNT
, AR_APPR_INVOICE_AMOUNT
, AR_APPR_INVOICE_COUNT
, AR_AMOUNT_DUE
, AR_COUNT_DUE
, AR_AMOUNT_OVERDUE
, AR_COUNT_OVERDUE
, DORMANT_BACKLOG_INACTIV
, DORMANT_BACKLOG_START
, LOST_BACKLOG
, ACTIVE_BACKLOG
, REVENUE_AT_RISK
)
SELECT
1 WORKER_ID
, p_project_id PROJECT_ID
, p_project_org_id PROJECT_ORG_ID
, p_project_organization_id PROJECT_ORGANIZATION_ID
, p_time_id TIME_ID
, 1 PERIOD_TYPE_ID
, p_calendar_type CALENDAR_TYPE
, p_gl_calendar_id GL_CALENDAR_ID
, p_pa_calendar_id PA_CALENDAR_ID
, p_curr_record_type_id CURR_RECORD_TYPE_ID
, p_currency_code CURRENCY_CODE
, to_number(null) REVENUE
, to_number(null) FUNDING
, to_number(null) INITIAL_FUNDING_AMOUNT
, to_number(null) INITIAL_FUNDING_COUNT
, to_number(null) ADDITIONAL_FUNDING_AMOUNT
, to_number(null) ADDITIONAL_FUNDING_COUNT
, to_number(null) CANCELLED_FUNDING_AMOUNT
, to_number(null) CANCELLED_FUNDING_COUNT
, to_number(null) FUNDING_ADJUSTMENT_AMOUNT
, to_number(null) FUNDING_ADJUSTMENT_COUNT
, to_number(null) REVENUE_WRITEOFF
, to_number(null) AR_INVOICE_AMOUNT
, to_number(null) AR_INVOICE_COUNT
, to_number(null) AR_CASH_APPLIED_AMOUNT
, to_number(null) AR_CASH_APPLIED_COUNT
, to_number(null) AR_INVOICE_WRITEOFF_AMOUNT
, to_number(null) AR_INVOICE_WRITEOFF_COUNT
, to_number(null) AR_CREDIT_MEMO_AMOUNT
, to_number(null) AR_CREDIT_MEMO_COUNT
, to_number(null) UNBILLED_RECEIVABLES
, to_number(null) UNEARNED_REVENUE
, to_number(null) AR_UNAPPR_INVOICE_AMOUNT
, to_number(null) AR_UNAPPR_INVOICE_COUNT
, to_number(null) AR_APPR_INVOICE_AMOUNT
, to_number(null) AR_APPR_INVOICE_COUNT
, to_number(null) AR_AMOUNT_DUE
, to_number(null) AR_COUNT_DUE
, to_number(null) AR_AMOUNT_OVERDUE
, to_number(null) AR_COUNT_OVERDUE
, nvl(-p_ex_drmt_bklg,0) + p_drmt_bklg DORMANT_BACKLOG_INACTIV
, nvl(-p_ex_strt_bklg,0) + p_strt_bklg DORMANT_BACKLOG_START
, nvl(-p_ex_lost_bklg,0) + p_lost_bklg LOST_BACKLOG
, nvl(-p_ex_actv_bklg,0) + p_actv_bklg ACTIVE_BACKLOG
, nvl(-p_ex_risky_rev,0) + p_risky_rev REVENUE_AT_RISK
FROM dual
;Commented for bug 13011859 */
End Insert_Backlog_Updates_pvt;
Insert_Backlog_Updates_pvt
( p_worker_id => p_worker_id
, p_project_id => p_project_id
, p_time_id => p_curr_date
, p_curr_record_type_id => p_curr_record_type_id
, p_currency_code => p_currency_code
, p_gl_calendar_id => p_gl_calendar_id
, p_pa_calendar_id => p_pa_calendar_id
, p_drmt_bklg => p_drmt_bklg
, p_strt_bklg => p_strt_bklg
, p_lost_bklg => p_lost_bklg
, p_actv_bklg => p_actv_bklg
, p_risky_rev => p_risky_rev
, p_project_org_id => p_project_org_id
, p_project_organization_id => p_project_organization_id
, p_calendar_type => p_calendar_type
, p_ex_drmt_bklg => p_ex_drmt_bklg
, p_ex_strt_bklg => p_ex_strt_bklg
, p_ex_lost_bklg => p_ex_lost_bklg
, p_ex_actv_bklg => p_ex_actv_bklg
, p_ex_risky_rev => p_ex_risky_rev
);
SELECT
WORKER_ID
, PROJECT_ID
, PROJECT_ORG_ID
, PROJECT_ORGANIZATION_ID
, TIME_ID
, PERIOD_TYPE_ID
, CALENDAR_TYPE
, CURR_RECORD_TYPE_ID
, CURRENCY_CODE
, nvl(REVENUE, 0) REVENUE
, nvl(FUNDING, 0) FUNDING
, nvl(DORMANT_BACKLOG_INACTIV, 0) DORMANT_BACKLOG_INACTIV
, nvl(DORMANT_BACKLOG_START, 0) DORMANT_BACKLOG_START
, nvl(LOST_BACKLOG, 0) LOST_BACKLOG
, nvl(ACTIVE_BACKLOG, 0) ACTIVE_BACKLOG
, nvl(REVENUE_AT_RISK, 0) REVENUE_AT_RISK
FROM (
Select
-l_worker_id worker_id
, fct.project_id project_id
, fct.PROJECT_ORG_ID PROJECT_ORG_ID
, fct.PROJECT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID
, fct.time_id time_id
, 1 period_type_id
, fct.CALENDAR_TYPE CALENDAR_TYPE
, bitand(fct.curr_record_type_id, 247) curr_record_type_id
, fct.currency_code currency_code
, sum(fct.revenue) REVENUE
, sum(nvl(fct.initial_funding_amount, 0) +
nvl(fct.additional_funding_amount, 0) +
nvl(fct.cancelled_funding_amount, 0) +
nvl(fct.funding_adjustment_amount, 0)) FUNDING
, sum(DORMANT_BACKLOG_INACTIV) DORMANT_BACKLOG_INACTIV
, sum(DORMANT_BACKLOG_START) DORMANT_BACKLOG_START
, sum(LOST_BACKLOG) LOST_BACKLOG
, sum(ACTIVE_BACKLOG) ACTIVE_BACKLOG
, sum(REVENUE_AT_RISK) REVENUE_AT_RISK
FROM pji_ac_proj_f fct
WHERE 1 = 1
And fct.project_id = l_project_id
And fct.calendar_type = l_daily_calendar_type
And fct.time_id >= l_min_julian_date - l_dbklg_days
And fct.curr_record_type_id not in (8, 256)
And fct.period_type_id = 1
Group By
fct.project_id
, fct.PROJECT_ORG_ID
, fct.PROJECT_ORGANIZATION_ID
, fct.time_id
, fct.CALENDAR_TYPE
, bitand(fct.curr_record_type_id, 247)
, fct.currency_code
union all
Select
-l_worker_id worker_id
, fct.project_id project_id
, to_number(l_project_org_id) project_org_id
, to_number(l_project_organization_id) project_organization_id
, -1 time_id
, 1 period_type_id
, to_char(l_daily_calendar_type) calendar_type
, bitand(fct.curr_record_type_id, 247) curr_record_type_id
, fct.currency_code currency_code
, sum(fct.revenue) REVENUE
, sum(nvl(fct.initial_funding_amount, 0) +
nvl(fct.additional_funding_amount, 0) +
nvl(fct.cancelled_funding_amount, 0) +
nvl(fct.funding_adjustment_amount, 0)) FUNDING
, sum(fct.DORMANT_BACKLOG_INACTIV) DORMANT_BACKLOG_INACTIV
, sum(fct.DORMANT_BACKLOG_START) DORMANT_BACKLOG_START
, sum(fct.LOST_BACKLOG) LOST_BACKLOG
, sum(fct.ACTIVE_BACKLOG) ACTIVE_BACKLOG
, sum(fct.REVENUE_AT_RISK) REVENUE_AT_RISK
From PJI_PMV_ITD_DIM_TMP time
, pji_ac_proj_f fct
Where fct.time_id = time.id
And fct.project_id = l_project_id
And (fct.calendar_type = l_aggr_calendar_type or fct.calendar_type = l_daily_calendar_type)
And fct.curr_record_type_id not in (8, 256)
Group By fct.project_id
, bitand(fct.curr_record_type_id, 247)
, fct.currency_code
union all
Select
-l_worker_id as worker_id
, to_number(l_project_id) as project_id
, to_number(l_project_org_id) as project_org_id
, to_number(l_project_organization_id) as project_organization_id
, to_number(to_char(l_max_date-1,'J')) as time_id
, 1 as period_type_id
, to_char(l_daily_calendar_type) as calendar_type
, 1 as curr_record_type_id
, l_g1_currency_code as currency_code
, to_number(null) as REVENUE
, to_number(null) as FUNDING
, to_number(null) as DORMANT_BACKLOG_INACTIV
, to_number(null) as DORMANT_BACKLOG_START
, to_number(null) as LOST_BACKLOG
, to_number(null) as ACTIVE_BACKLOG
, to_number(null) as REVENUE_AT_RISK
From Dual
union all
Select
-l_worker_id as worker_id
, to_number(l_project_id) as project_id
, to_number(l_project_org_id) as project_org_id
, to_number(l_project_organization_id) as project_organization_id
, to_number(to_char(l_max_date-1,'J')) as time_id
, 1 as period_type_id
, to_char(l_daily_calendar_type) as calendar_type
, 2 as curr_record_type_id
, l_g2_currency_code as currency_code
, to_number(null) as REVENUE
, to_number(null) as FUNDING
, to_number(null) as DORMANT_BACKLOG_INACTIV
, to_number(null) as DORMANT_BACKLOG_START
, to_number(null) as LOST_BACKLOG
, to_number(null) as ACTIVE_BACKLOG
, to_number(null) as REVENUE_AT_RISK
From Dual
Where l_g2_currency_flag = 'Y'
union all
Select
-l_worker_id as worker_id
, to_number(l_project_id) as project_id
, to_number(l_project_org_id) as project_org_id
, to_number(l_project_organization_id) as project_organization_id
, to_number(to_char(l_max_date-1,'J')) as time_id
, 1 as period_type_id
, to_char(l_daily_calendar_type) as calendar_type
, 4 as curr_record_type_id
, pf_currency_code as currency_code
, to_number(null) as REVENUE
, to_number(null) as FUNDING
, to_number(null) as DORMANT_BACKLOG_INACTIV
, to_number(null) as DORMANT_BACKLOG_START
, to_number(null) as LOST_BACKLOG
, to_number(null) as ACTIVE_BACKLOG
, to_number(null) as REVENUE_AT_RISK
From PJI_ORG_EXTR_INFO
Where nvl(ORG_ID, -1) = nvl(l_project_org_id, -1)
)
ORDER BY
PROJECT_ID
, CURR_RECORD_TYPE_ID
, CURRENCY_CODE
, TIME_ID
;
select
TXN_CURR_FLAG,
GLOBAL_CURR2_FLAG
into
l_txn_currency_flag,
l_g2_currency_flag
from
PJI_SYSTEM_SETTINGS;
/* DELETE FROM PJI_PMV_ITD_DIM_TMP;
/* delete
from PJI_FM_AGGR_ACT3
where worker_id = 1
and project_id in
(select project_id
from pji_pji_proj_batch_map
where worker_id = 1
and BACKLOG_EXTRACTION_STATUS = 'P'
and PARALLEL_BACKLOG_WORKER_ID = p_worker_id
)
;
Update pji_pji_proj_batch_map
Set BACKLOG_EXTRACTION_STATUS = null
, PARALLEL_BACKLOG_WORKER_ID = null
Where WORKER_ID = 1
And PARALLEL_BACKLOG_WORKER_ID = p_worker_id
And BACKLOG_EXTRACTION_STATUS = 'P'
;Commented for bug 13011859 */
Update pji_pji_proj_batch_map
Set BACKLOG_EXTRACTION_STATUS='P'
, PARALLEL_BACKLOG_WORKER_ID = p_worker_id
Where WORKER_ID = 1
And BACKLOG_EXTRACTION_STATUS is null
And rownum = 1
And not exists
( select 1
from pji_pji_proj_batch_map
where worker_id = 1
and parallel_backlog_worker_id = p_worker_id
and backlog_extraction_status = 'P'
)
And ( ACTIVITY_MIN_GL_DATE is not null
or ACTIVITY_MIN_PA_DATE is not null
or FUNDING_MIN_DATE is not null
or nvl(OLD_CLOSED_DATE, l_max_date)
<> nvl(NEW_CLOSED_DATE, l_max_date)
)
;Commented for bug 13011859 */
select count(*)
into l_count_for_upd
from pji_pji_proj_batch_map
where WORKER_ID = p_worker_id
and BACKLOG_EXTRACTION_STATUS is null
and ( ACTIVITY_MIN_GL_DATE is not null
or ACTIVITY_MIN_PA_DATE is not null
or FUNDING_MIN_DATE is not null
or nvl(OLD_CLOSED_DATE, l_max_date)
<> nvl(NEW_CLOSED_DATE, l_max_date)
)
;
ELSE -- go ahead with the processing for the record updated
Select project_id
Into l_curr_project_id
From pji_pji_proj_batch_map
Where BACKLOG_EXTRACTION_STATUS='P'
And PARALLEL_BACKLOG_WORKER_ID = p_worker_id
;
SELECT /*+ ORDERED *
map.project_id
, least(
(nvl(map.ACTIVITY_MIN_GL_DATE, l_max_date))
, (nvl(map.FUNDING_MIN_DATE, l_max_date))
, (nvl(map.OLD_CLOSED_DATE, l_max_date))
, (nvl(map.NEW_CLOSED_DATE, l_max_date))
, (nvl(last_extr_date , l_max_date))
) as min_nocal_date
, least(
(nvl(map.ACTIVITY_MIN_PA_DATE, l_max_date))
, (nvl(map.FUNDING_MIN_DATE, l_max_date))
, (nvl(map.OLD_CLOSED_DATE, l_max_date))
, (nvl(map.NEW_CLOSED_DATE, l_max_date))
, (nvl(last_extr_date , l_max_date))
) as min_cal_date
, nvl(map.NEW_CLOSED_DATE, l_max_date) as new_closed_date
, map.PROJECT_ORG_ID
, map.PROJECT_ORGANIZATION_ID
, org_info.PA_CALENDAR_ID
, org_info.GL_CALENDAR_ID
, org_info.PA_CALENDAR_ID
INTO l_project_id
, l_min_nocal_date
, l_min_cal_date
, l_close_date
, l_project_org_id
, l_project_organization_id
, l_calendar_id
, l_gl_calendar_id
, l_pa_calendar_id
FROM pji_pji_proj_batch_map map
, PJI_ORG_EXTR_INFO org_info
WHERE map.WORKER_ID = 1
AND map.PROJECT_ORG_ID = org_info.org_id
AND map.project_id = l_curr_project_id
;
Update pji_pji_proj_batch_map
Set BACKLOG_EXTRACTION_STATUS='X'
Where project_id = l_curr_project_id
;
select count(*)
into l_count_for_upd
from pji_pji_proj_batch_map
where WORKER_ID = p_worker_id
and NVL(BACKLOG_EXTRACTION_STATUS,'P') = 'P'
;
update PJI_SYSTEM_PRC_STATUS
set STEP_STATUS = 'C'
where PROCESS_NAME = PJI_RM_SUM_MAIN.g_process || to_char(x) and
STEP_NAME = 'PJI_FM_SUM_BKLG.PROCESS_DRMT_BKLG(p_worker_id);';