[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT opi_edw_job_rsrc_inc_s.NEXTVAL INTO l_seq_id FROM dual;
INSERT
INTO opi_edw_OPI_job_rsrc_inc(primary_key1,primary_key2,primary_key3,primary_key4,primary_key5,primary_key6,seq_id,view_id)
SELECT
DISTINCT
wor.organization_id,
wor.wip_entity_id,
wor.repetitive_schedule_id,
wor.operation_seq_num,
wor.resource_id,
'OPI',
l_seq_id,
1
FROM
WIP_OPERATION_RESOURCES wor,
WIP_OPERATIONS wo,
/*WIP_MOVE_TRANSACTIONS wmt,WIP_MOVE_TRANSACTIONS wmt2, */
WIP_DISCRETE_JOBS wdj,
WIP_REPETITIVE_SCHEDULES wrs,
WIP_ENTITIES we,
BOM_DEPARTMENTS bd,
HR_ORGANIZATION_INFORMATION hoi,
GL_SETS_OF_BOOKS gsob
WHERE
wor.organization_id = wo.organization_id
and wor.wip_entity_id = wo.wip_entity_id
and wor.operation_seq_num = wo.operation_seq_num
and nvl(wor.repetitive_schedule_id,-99) = nvl(wo.repetitive_schedule_id,-99)
and wo.organization_id = bd.organization_id
and wo.department_id = bd.department_id
and wo.organization_id = we.organization_id
and wo.wip_entity_id = we.wip_entity_id
and hoi.organization_id = wor.organization_id
and to_char(gsob.set_of_books_id) = hoi.ORG_INFORMATION1
and hoi.ORG_INFORMATION_CONTEXT = 'Accounting Information'
and wdj.wip_entity_id (+) = wor.wip_entity_id
and wdj.organization_id (+) = wor.organization_id
and wrs.repetitive_schedule_id (+)= nvl(wor.repetitive_schedule_id,-99)
and wrs.organization_id (+) = wor.organization_id
and (wrs.status_type in (4,5,7,12) or wdj.status_type in (4,5,7,12))
and greatest(
nvl(wor.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(wrs.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(wdj.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss'))
)
BETWEEN g_push_from_date and g_push_to_date
UNION
select
distinct
primary_key1,
primary_key2,
primary_key3,
primary_key4,
primary_key5,
primary_key6,
l_seq_id, /* NOTE : THIS IS THE NEW SEQ_ID */
view_id
from
opi_edw_opi_job_rsrc_mr_tmp
where
view_id = 1 ;
INSERT
INTO opi_edw_opi_job_rsrc_inc(primary_key1,primary_key2,primary_key3,primary_key4,primary_key5,primary_key6,seq_id,view_id)
SELECT
DISTINCT
wt.organization_id,
wt.wip_entity_id,
to_number(NULL),
wt.operation_seq_num,
wt.resource_id,
'OPI',
l_seq_id,
2
FROM
WIP_ENTITIES we,
WIP_TRANSACTIONS wt,
WIP_TRANSACTION_ACCOUNTS wta,
BOM_DEPARTMENTS bd,
HR_ORGANIZATION_INFORMATION hoi,
GL_SETS_OF_BOOKS gsob,
BOM_OPERATIONAL_ROUTINGS bor,
BOM_OPERATION_SEQUENCES bos,
WIP_FLOW_SCHEDULES wfs
WHERE
wt.transaction_type in (1,3)
and wfs.status = 2
and wt.wip_entity_id = wfs.wip_entity_id
and wt.organization_id = wfs.organization_id
and wt.organization_id = wta.organization_id
and wt.wip_entity_id = wta.wip_entity_id
and wt.transaction_id = wta.transaction_id
and wta.accounting_line_type = 7
and wt.wip_entity_id = we.wip_entity_id
and wt.organization_id = we.organization_id
and wt.organization_id = bd.organization_id
and wt.department_id = bd.department_id
and hoi.organization_id = wt.organization_id
and hoi.ORG_INFORMATION_CONTEXT = 'Accounting Information'
and to_char(gsob.set_of_books_id) = hoi.ORG_INFORMATION1
and wfs.organization_id = bor.organization_id
and nvl(wfs.alternate_routing_designator,-99) = nvl(bor.alternate_routing_designator,-99)
and wfs.primary_item_id = bor.assembly_item_id
and bor.routing_sequence_id = bos.routing_sequence_id
and wt.operation_seq_num = bos.operation_seq_num
and bos.operation_type = 1
and greatest(
nvl(wt.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(wfs.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss'))
)
BETWEEN g_push_from_date and g_push_to_date
UNION
select
primary_key1,
primary_key2,
primary_key3,
primary_key4,
primary_key5,
primary_key6,
l_seq_id, /* NOTE : THIS IS THE NEW SEQ_ID */
view_id
from
opi_edw_opi_job_rsrc_mr_tmp
where
view_id = 2 ;
delete
opi_edw_opi_job_rsrc_mr_tmp
where
view_id = p_view_id ;
Insert Into opi_edw_job_rsrc_fstg
(
JOB_RSRC_PK,
ACT_RSRC_COUNT,
PLN_RSRC_COUNT,
ACT_RSRC_QTY,
ACT_RSRC_VAL_B,
ACT_RSRC_VAL_G,
PLN_RSRC_QTY,
PLN_RSRC_VAL_B,
PLN_RSRC_VAL_G,
ACT_RSRC_USAGE,
PLN_RSRC_USAGE,
ACT_RSRC_USAGE_VAL_B,
ACT_RSRC_USAGE_VAL_G,
PLN_RSRC_USAGE_VAL_B,
PLN_RSRC_USAGE_VAL_G,
EXTD_RSRC_COST,
STND_RSRC_USAGE,
JOB_NO,
OPERATION_SEQ_NO,
DEPARTMENT,
ACT_STRT_DATE,
ACT_CMPL_DATE,
PLN_STRT_DATE,
PLN_CMPL_DATE,
SOB_CURRENCY_FK,
QTY_UOM_FK,
INSTANCE_FK,
LOCATOR_FK,
ACTIVITY_FK,
TRX_DATE_FK,
OPRN_FK,
RSRC_FK,
ITEM_FK,
USAGE_UOM_FK,
USER_ATTRIBUTE1,
USER_ATTRIBUTE10,
USER_ATTRIBUTE11,
USER_ATTRIBUTE12,
USER_ATTRIBUTE13,
USER_ATTRIBUTE14,
USER_ATTRIBUTE15,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
USER_ATTRIBUTE6,
USER_ATTRIBUTE7,
USER_ATTRIBUTE8,
USER_ATTRIBUTE9,
USER_FK1,
USER_FK2,
USER_FK3,
USER_FK4,
USER_FK5,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
OPERATION_CODE,
COLLECTION_STATUS
)
SELECT /*+ ALL_ROWS */
JOB_RSRC_PK,
ACT_RSRC_COUNT,
PLN_RSRC_COUNT,
ACT_RSRC_QTY,
ACT_RSRC_VAL_B,
round((nvl(ACT_RSRC_VAL_B,0) * GLOBAL_CURRENCY_RATE )/l_mau)*l_mau ACT_RSRC_VAL_G,
PLN_RSRC_QTY,
PLN_RSRC_VAL_B,
round((nvl(PLN_RSRC_VAL_B,0) * GLOBAL_CURRENCY_RATE )/l_mau)*l_mau PLN_RSRC_VAL_G,
ACT_RSRC_USAGE,
PLN_RSRC_USAGE,
ACT_RSRC_USAGE_VAL_B,
round((nvl(ACT_RSRC_USAGE_VAL_B,0) * GLOBAL_CURRENCY_RATE )/l_mau)*l_mau ACT_RSRC_USAGE_VAL_G,
PLN_RSRC_USAGE_VAL_B,
round((nvl(PLN_RSRC_USAGE_VAL_B,0) * GLOBAL_CURRENCY_RATE )/l_mau)*l_mau PLN_RSRC_USAGE_VAL_G,
EXTD_RSRC_COST,
STND_RSRC_USAGE,
JOB_NO,
OPERATION_SEQ_NO,
DEPARTMENT,
ACT_STRT_DATE,
ACT_CMPL_DATE,
PLN_STRT_DATE,
PLN_CMPL_DATE,
NVL(SOB_CURRENCY_FK,'NA_EDW'),
NVL(QTY_UOM_FK,'NA_EDW'),
NVL(INSTANCE_FK,'NA_EDW'),
NVL(LOCATOR_FK,'NA_EDW'),
NVL(ACTIVITY_FK,'NA_EDW'),
NVL(TRX_DATE_FK,'NA_EDW'),
NVL(OPRN_FK,'NA_EDW'),
NVL(RSRC_FK,'NA_EDW'),
NVL(ITEM_FK,'NA_EDW'),
NVL(USAGE_UOM_FK,'NA_EDW'),
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
'NA_EDW' ,
'NA_EDW' ,
'NA_EDW' ,
'NA_EDW' ,
'NA_EDW' ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL, -- OPERATION_CODE
DECODE(GLOBAL_CURRENCY_RATE,
NULL, 'RATE NOT AVAILABLE',
-1, 'RATE NOT AVAILABLE',
-2, 'INVALID CURRENCY',
'LOCAL READY') /* COLLECTION_STATUS */
FROM opi_edw_opi_job_rsrc_fcv
WHERE view_id = p_view_id
AND seq_id = p_seq_id;
l_rows_inserted Number:=0;
edw_log.put_line('Inserting into local staging table for view type 1');
edw_log.put_line('Inserted ' || Nvl(l_row_count_view1,0) ||
' rows into local staging table for view type 1');
DELETE_STG ;
edw_log.put_line('Inserting into local staging table for view type 2');
edw_log.put_line('Inserted ' || Nvl(l_row_count_view2,0) ||
' rows into local staging table for view type 2');
edw_log.put_line('For all view types, inserted ' || Nvl(g_row_count,0)
|| ' rows into local staging table.');
DELETE_STG ;
edw_log.put_line('Inserted '||nvl(g_row_count,0)||
' rows into the staging table');
rollback; -- Rollback insert into local staging
edw_log.put_line('Inserting into local staging have failed');
PROCEDURE DELETE_STG IS
BEGIN
/*
We do not check if the LOCAL instance is the same as the REMOTE instance
before DELETing.
This is because we need to do ( Push_To_Local -> Delete_Stg)
for each view type instead of (Push_To_Local 1 -> Push_To_Local 2 -> Delete_Stg)
*/
delete
opi_edw_job_rsrc_fstg
where
collection_status in ('RATE NOT AVAILABLE','INVALID CURRENCY') ;
END DELETE_STG ;
select
sob_currency_fk from_currency,
nvl(substrb(TRX_DATE_FK,1,10),CREATION_DATE) c_date,
collection_status collection_status,
job_rsrc_pk job_rsrc_pk
from
opi_edw_job_rsrc_fstg
where
job_rsrc_pk like '%OPI'
and collection_status in ('RATE NOT AVAILABLE','INVALID CURRENCY')
/*
order by
from_currency, c_date
*/
group by
sob_currency_fk,
nvl(substrb(TRX_DATE_FK,1,10),CREATION_DATE),
collection_status,
job_rsrc_pk
;
-- Insert Records with seq_id = NULL
INSERT INTO opi_edw_opi_job_rsrc_mr_tmp
(primary_key1,primary_key2,primary_key3,primary_key4,primary_key5,primary_key6,view_id)
VALUES
(l_primary_key1,l_primary_key2,l_primary_key3,l_primary_key4,l_primary_key5,'OPI',l_view_id);