[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM opi_dbi_muv_conv_rates
WHERE (nvl (conversion_rate, -999) < 0
OR nvl (sec_conversion_rate, 999) < 0)
AND rownum < 2;
SELECT DISTINCT
report_order,
curr_code,
rate_type,
transaction_date,
func_currency_code
FROM (
SELECT DISTINCT
g_global_currency_code curr_code,
g_global_rate_type rate_type,
1 report_order, -- ordering global currency first
mp.organization_code,
decode (conv.conversion_rate,
C_EURO_MISSING_AT_START, g_euro_start_date,
conv.transaction_date) transaction_date,
conv.f_currency_code func_currency_code
FROM opi_dbi_muv_conv_rates conv,
mtl_parameters mp,
(SELECT
DISTINCT organization_id,
trunc (transaction_date) transaction_date
FROM OPI_DBI_JOBS_TXN_STG) to_conv
WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
AND mp.organization_id = to_conv.organization_id
AND conv.transaction_date (+) = to_conv.transaction_date
AND conv.organization_id (+) = to_conv.organization_id
UNION ALL
SELECT DISTINCT
g_secondary_currency_code curr_code,
g_secondary_rate_type rate_type,
decode (p_pri_sec_curr_same,
1, 1,
2) report_order, --ordering secondary currency next
mp.organization_code,
decode (conv.sec_conversion_rate,
C_EURO_MISSING_AT_START, g_euro_start_date,
conv.transaction_date) transaction_date,
conv.f_currency_code func_currency_code
FROM opi_dbi_muv_conv_rates conv,
mtl_parameters mp,
(SELECT
DISTINCT organization_id,
trunc (transaction_date) transaction_date
FROM OPI_DBI_JOBS_TXN_STG) to_conv
WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
AND mp.organization_id = to_conv.organization_id
AND conv.transaction_date (+) = to_conv.transaction_date
AND conv.organization_id (+) = to_conv.organization_id)
ORDER BY
report_order ASC,
transaction_date,
func_currency_code;
INSERT /*+ append parallel(rates) */
INTO opi_dbi_muv_conv_rates rates (
organization_id,
f_currency_code,
transaction_date,
conversion_rate,
sec_conversion_rate,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID
)
SELECT /*+ parallel (to_conv) parallel (curr_codes) */
to_conv.organization_id,
curr_codes.currency_code,
to_conv.transaction_date,
decode (curr_codes.currency_code,
g_global_currency_code, 1,
fii_currency.get_global_rate_primary (
curr_codes.currency_code,
to_conv.transaction_date) ),
decode (g_secondary_currency_code,
NULL, NULL,
curr_codes.currency_code, 1,
decode (l_pri_sec_curr_same,
1, C_PRI_SEC_CURR_SAME_MARKER,
fii_currency.get_global_rate_secondary (
curr_codes.currency_code,
to_conv.transaction_date))),
s_sysdate,
s_sysdate,
s_user_id,
s_user_id,
s_login_id,
s_program_id,
s_program_login_id,
s_program_application_id,
s_request_id
FROM
(SELECT
DISTINCT organization_id, trunc (transaction_date) transaction_date
FROM OPI_DBI_JOBS_TXN_STG
) to_conv,
(SELECT /*+ leading (hoi) full (hoi) use_hash (gsob)
parallel (hoi) parallel (gsob)*/
DISTINCT hoi.organization_id, gsob.currency_code
FROM hr_organization_information hoi,
gl_sets_of_books gsob
WHERE hoi.org_information_context = 'Accounting Information'
AND hoi.org_information1 = to_char(gsob.set_of_books_id))
curr_codes
WHERE curr_codes.organization_id = to_conv.organization_id;
UPDATE /*+ parallel (opi_dbi_muv_conv_rates) */
opi_dbi_muv_conv_rates
SET sec_conversion_rate = conversion_rate;
/* Insert MMT data into staging */
l_stmt_num := 20;
INSERT /*+ APPEND parallel(stg) */ INTO OPI_DBI_JOBS_TXN_MMT_STG stg
(transaction_id
, organization_id
, inventory_item_id
, transaction_date
, primary_quantity
, transaction_source_id
, transaction_source_type_id
, transaction_action_id
, reason_id
, costed_flag
, process_enabled_flag
, creation_date
, last_update_date
, created_by
, last_updated_by
, last_update_login
, PROGRAM_ID
, PROGRAM_LOGIN_ID
, PROGRAM_APPLICATION_ID
, REQUEST_ID
)
/* For Discrete Orgs, collect between transaction id range */
SELECT /*+ ordered use_hash(mtp) swap_join_inputs(mtp) parallel(mmt) full(LOG) full(mmt) parallel(mtp) parallel(log)*/
MMT.transaction_id
, MMT.organization_id
, MMT.inventory_item_id
, MMT.transaction_date
, MMT.primary_quantity
, MMT.transaction_source_id
, MMT.transaction_source_type_id
, MMT.transaction_action_id
, MMT.reason_id
, MMT.costed_flag
, 'N'
, s_sysdate
, s_sysdate
, s_user_id
, s_user_id
, s_login_id
, s_program_id
, s_program_login_id
, s_program_application_id
, s_request_id
FROM OPI_DBI_RUN_LOG_CURR LOG
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_PARAMETERS mtp
WHERE 1 = 1
AND MMT.transaction_action_id IN (1, 27, 31, 32, 30) -- Issue, Receipt, Completion, Return,Scrap
AND MMT.transaction_source_type_id = 5 -- Jobs abd Schedules
AND MMT.ORGANiZATION_ID = mtp.organization_id
AND mtp.process_enabled_flag = 'N'
AND mmt.organization_id = LOG.organization_id
AND LOG.organization_id IS NOT NULL
AND LOG.etl_id = 1
AND LOG.SOURCE = 1
AND mmt.transaction_id >= LOG.start_txn_id
AND mmt.transaction_id <= LOG.next_start_txn_id
UNION ALL
/* For process orgs, collect from global start date */
SELECT /*+ ordered use_hash(mtp) swap_join_inputs(mtp) parallel(mmt) full(LOG) full(mmt) parallel(mtp) parallel(log)*/
MMT.transaction_id
, MMT.organization_id
, MMT.inventory_item_id
, MMT.transaction_date
, MMT.primary_quantity
, MMT.transaction_source_id
, MMT.transaction_source_type_id
, MMT.transaction_action_id
, MMT.reason_id
, MMT.costed_flag
, 'Y'
, s_sysdate
, s_sysdate
, s_user_id
, s_user_id
, s_login_id
, s_program_id
, s_program_login_id
, s_program_application_id
, s_request_id
FROM OPI_DBI_RUN_LOG_CURR LOG
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_PARAMETERS mtp
WHERE 1 = 1
AND MMT.transaction_action_id IN (1, 27, 31, 32, 30) -- Issue, Receipt, Completion, Return,Scrap
AND MMT.transaction_source_type_id = 5 -- Jobs abd Schedules
AND MMT.ORGANiZATION_ID = mtp.organization_id
AND mtp.process_enabled_flag = 'Y'
--AND mmt.organization_id = LOG.organization_id
AND LOG.organization_id IS NULL
AND LOG.etl_id = 1
AND LOG.SOURCE = 2
AND MMT.transaction_date >= LOG.from_bound_date;
BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of MMT Staging Table: '|| l_row_count || ' rows inserted');
/* Insert ODM data into Staging */
/* mta and mmta are joined to give the transaction value and mmt gives the transaction value */
l_stmt_num := 20;
INSERT /*+ APPEND PARALLEL(STG) */
INTO OPI_DBI_JOBS_TXN_STG STG
(
job_id,
job_type,
organization_id,
assembly_item_id,
component_item_id,
uom_code,
line_type,
transaction_date,
primary_quantity,
primary_quantity_draft,
transaction_value_b,
transaction_value_draft_b,
scrap_reason,
planned_item,
etl_type_id,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID
)
select
mmt1.JOB_ID,
DECODE(WE.ENTITY_TYPE,1,1,2,2,4,3,3,1,8,5,5,5,5),
mta1.ORGANIZATION_ID,
WE.PRIMARY_ITEM_ID,
mta1.INVENTORY_ITEM_ID,
mmt1.PRIMARY_UOM_CODE,
decode(mmt1.etl_type_id, 1, -1, 1),
mmt1.transaction_date,
mmt1.TXN_QTY * -1,
0,
mta1.BASE_TRANSACTION_VALUE,
0,
nvl(mmt1.reason_id,-1),
MMT1.PLANNED_ITEM,
MMT1.ETL_TYPE_ID,
1,
s_sysdate,
s_sysdate,
s_user_id,
s_user_id,
s_login_id,
s_program_id,
s_program_login_id,
s_program_application_id,
s_request_id
from
(
select
mta.ORGANIZATION_ID,
mta.INVENTORY_ITEM_ID,
mta.transaction_source_id,
mmta.REPETITIVE_SCHEDULE_ID,
trunc(mta.transaction_date) transaction_date,
mta.transaction_id,
decode(mmta.REPETITIVE_SCHEDULE_ID, null,
SUM(mta.BASE_TRANSACTION_VALUE),
SUM(mta.BASE_TRANSACTION_VALUE) * decode(sum(mmta.tot_primary_quantity), 0,
null, sum(mmta.primary_quantity) / sum(mmta.tot_primary_quantity))) BASE_TRANSACTION_VALUE
from
(select
mtain.ORGANIZATION_ID,
mtain.INVENTORY_ITEM_ID,
mtain.transaction_source_id,
mtain.transaction_id,
mtain.transaction_date,
SUM(mtain.BASE_TRANSACTION_VALUE) BASE_TRANSACTION_VALUE
from
mtl_transaction_accounts mtain,
OPI_DBI_RUN_LOG_CURR log
where
mtain.accounting_line_type = 7 /* WIP valuation */ and
mtain.transaction_source_type_id = 5 /* Job or schedule */ and
log.source = 1 and
log.etl_id = 1 and
mtain.organization_id = log.organization_id and
mtain.transaction_id >= log.Start_txn_id and
mtain.transaction_id < log.Next_start_txn_id
group by
mtain.ORGANIZATION_ID,
mtain.INVENTORY_ITEM_ID,
mtain.transaction_source_id,
mtain.transaction_id,
mtain.transaction_date
)mta, /* For repetitive schedules: An mtl txn can span across multiple repetitive schedules */
(
select
mmtain.organization_id,
mmtain.repetitive_schedule_id,
mmtain.transaction_id,
mmtain.transaction_date,
sum(primary_quantity) primary_quantity,
sum(sum(primary_quantity)) over
(partition by mmtain.organization_id, mmtain.transaction_id) tot_primary_quantity
from
mtl_material_txn_allocations mmtain,
OPI_DBI_RUN_LOG_CURR log
where
log.source = 1 and
log.etl_id = 1 and
mmtain.organization_id = log.organization_id and
mmtain.transaction_id >= log.Start_txn_id and
mmtain.transaction_id < log.Next_start_txn_id
group by
mmtain.organization_id,
mmtain.repetitive_schedule_id,
mmtain.transaction_id,
mmtain.transaction_date
)mmta
where
mta.organization_id = mmta.organization_id (+) and
mta.transaction_id = mmta.transaction_id (+)
group by
mta.INVENTORY_ITEM_ID,
mta.ORGANIZATION_ID,
mta.transaction_source_id,
mmta.REPETITIVE_SCHEDULE_ID,
mta.transaction_id,
mta.transaction_date
)mta1,
(
select
mmt.transaction_id,
mmt.ORGANIZATION_ID,
mmt.INVENTORY_ITEM_ID,
mmt.transaction_source_id,
decode(sum(mmta.primary_quantity), null, mmt.transaction_source_id,mmta.repetitive_schedule_id) JOB_ID,
decode(sum(mmta.primary_quantity), null, 1, 2) JOB_TYPE, -- Here 1 is for Discrete and Flow.
msi.PRIMARY_UOM_CODE,
decode(sum(mmta.primary_quantity), null, sum(mmt.primary_quantity),sum(mmta.primary_quantity)) TXN_QTY,
trunc(mmt.transaction_date) transaction_date,
mmt.reason_id,
decode (msi.mrp_planning_code,
NON_PLANNED_ITEM, 'N',
'Y') PLANNED_ITEM,
decode(mmt.transaction_action_id,1,1,
27,1,
31,2,
32,2,
30,3) ETL_TYPE_ID
from
OPI_DBI_JOBS_TXN_MMT_STG mmt,
mtl_material_txn_allocations mmta,
mtl_system_items_b msi,
OPI_DBI_RUN_LOG_CURR log
where
mmt.organization_id = msi.organization_id and
mmt.inventory_item_id = msi.inventory_item_id and
mmt.transaction_action_id in (1, 27,31,32,30) and -- Issue, Receipt, Completion, Return,Scrap
mmt.transaction_source_type_id = 5 and -- Jobs abd Schedules
mmt.transaction_id = mmta.transaction_id (+) and
mmt.organization_id = log.organization_id and
mmt.transaction_id >= log.Start_txn_id and
mmt.transaction_id < log.Next_start_txn_id and
log.etl_id = 1 and
log.source = 1
group by
mmt.ORGANIZATION_ID,
mmt.INVENTORY_ITEM_ID,
mmt.transaction_source_id,
mmta.repetitive_schedule_id,
msi.PRIMARY_UOM_CODE,
mmt.transaction_date,
mmt.transaction_id,
mmt.reason_id,
mmt.transaction_action_id,
msi.mrp_planning_code
)mmt1,
WIP_ENTITIES we,
WIP_DISCRETE_JOBS wdj
where
mta1.transaction_id = mmt1.transaction_id and
mta1.organization_id = mmt1.organization_id and
mta1.inventory_item_id = mmt1.inventory_item_id and
mta1.transaction_source_id = mmt1.transaction_source_id and
mta1.transaction_date = mmt1.transaction_date and
(we.ENTITY_TYPE in (1,3,4,5,8) OR (we.ENTITY_TYPE = 2 and mta1.REPETITIVE_SCHEDULE_ID = mmt1.JOB_ID)) and
(mmt1.TXN_QTY <> 0 or mta1.BASE_TRANSACTION_VALUE <> 0) and
mta1.ORGANIZATION_ID = we.ORGANIZATION_ID and
mta1.transaction_source_id = WE.WIP_ENTITY_ID and
we.PRIMARY_ITEM_ID IS NOT NULL and
we.WIP_ENTITY_ID = wdj.WIP_ENTITY_ID (+) and
nvl (wdj.JOB_TYPE, 1) =1;
BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of ODM Txn Staging Table: '|| l_row_count || ' rows inserted');
/* Insert ODM data into Staging*/
/* mta and mmta are joined to give the transaction value and mmt gives the transaction value */
l_stmt_num := 20;
INSERT
INTO OPI_DBI_JOBS_TXN_STG
(
job_id,
job_type,
organization_id,
assembly_item_id,
component_item_id,
uom_code,
line_type,
transaction_date,
primary_quantity,
primary_quantity_draft,
transaction_value_b,
transaction_value_draft_b,
scrap_reason,
planned_item,
etl_type_id,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID
)
select
mmt1.JOB_ID,
DECODE(WE.ENTITY_TYPE,1,1,2,2,4,3,3,1,8,5,5,5,5),
mta1.ORGANIZATION_ID,
WE.PRIMARY_ITEM_ID,
mta1.INVENTORY_ITEM_ID,
mmt1.PRIMARY_UOM_CODE,
decode(mmt1.etl_type_id, 1, -1, 1),
mmt1.transaction_date,
mmt1.TXN_QTY * -1,
0,
mta1.BASE_TRANSACTION_VALUE,
0,
nvl(mmt1.reason_id,-1),
MMT1.PLANNED_ITEM,
MMT1.ETL_TYPE_ID,
1,
s_sysdate,
s_sysdate,
s_user_id,
s_user_id,
s_login_id,
s_program_id,
s_program_login_id,
s_program_application_id,
s_request_id
from
(
select
mta.ORGANIZATION_ID,
mta.INVENTORY_ITEM_ID,
mta.transaction_source_id,
mmta.REPETITIVE_SCHEDULE_ID,
trunc(mta.transaction_date) transaction_date,
mta.transaction_id transaction_id,
decode(mmta.REPETITIVE_SCHEDULE_ID, null,
SUM(mta.BASE_TRANSACTION_VALUE),
SUM(mta.BASE_TRANSACTION_VALUE) * decode(sum(mmta.tot_primary_quantity), 0,
null, sum(mmta.primary_quantity) / sum(mmta.tot_primary_quantity))) BASE_TRANSACTION_VALUE
from
(select
mtain.ORGANIZATION_ID,
mtain.INVENTORY_ITEM_ID,
mtain.transaction_source_id,
mtain.transaction_id,
mtain.transaction_date,
SUM(mtain.BASE_TRANSACTION_VALUE) BASE_TRANSACTION_VALUE
from
mtl_transaction_accounts mtain,
OPI_DBI_RUN_LOG_CURR log
where
mtain.accounting_line_type = 7 /* WIP valuation */ and
mtain.transaction_source_type_id = 5 /* Job or schedule */ and
log.source = 1 and
log.etl_id = 1 and
mtain.organization_id = log.organization_id and
mtain.transaction_id >= log.Start_txn_id and
mtain.transaction_id < log.Next_start_txn_id
group by
mtain.ORGANIZATION_ID,
mtain.INVENTORY_ITEM_ID,
mtain.transaction_source_id,
mtain.transaction_id,
mtain.transaction_date
)mta,
(
select
mmtain.organization_id,
mmtain.REPETITIVE_SCHEDULE_ID,
mmtain.transaction_id,
mmtain.transaction_date,
sum(primary_quantity) primary_quantity,
sum(sum(primary_quantity)) over
(partition by mmtain.organization_id, mmtain.transaction_id) tot_primary_quantity
from
mtl_material_txn_allocations mmtain,
OPI_DBI_RUN_LOG_CURR log
where
log.source = 1 and
log.etl_id = 1 and
mmtain.organization_id = log.organization_id and
mmtain.transaction_id >= log.Start_txn_id and
mmtain.transaction_id < log.Next_start_txn_id
group by
mmtain.organization_id,
mmtain.repetitive_schedule_id,
mmtain.transaction_id,
mmtain.transaction_date
)mmta
where
mta.organization_id = mmta.organization_id (+) and
mta.transaction_id = mmta.transaction_id (+)
group by
mta.INVENTORY_ITEM_ID,
mta.ORGANIZATION_ID,
mta.transaction_source_id,
mmta.REPETITIVE_SCHEDULE_ID,
mta.transaction_id,
mta.transaction_date
)mta1,
(
select
mmt.transaction_id,
mmt.ORGANIZATION_ID,
mmt.INVENTORY_ITEM_ID,
mmt.transaction_source_id,
decode(sum(mmta.primary_quantity), null, mmt.transaction_source_id,mmta.repetitive_schedule_id) JOB_ID,
decode(sum(mmta.primary_quantity), null, 1, 2) JOB_TYPE, -- Here 1 is for Discrete and Flow.
msi.PRIMARY_UOM_CODE,
decode(sum(mmta.primary_quantity), null, sum(mmt.primary_quantity),sum(mmta.primary_quantity)) TXN_QTY,
trunc(mmt.transaction_date) transaction_date,
mmt.reason_id,
decode (msi.mrp_planning_code,
NON_PLANNED_ITEM, 'N',
'Y') PLANNED_ITEM,
decode(mmt.transaction_action_id,1,1,
27,1,
31,2,
32,2,
30,3) ETL_TYPE_ID
from
MTL_MATERIAL_TRANSACTIONS mmt,
mtl_material_txn_allocations mmta,
mtl_system_items_b msi,
OPI_DBI_RUN_LOG_CURR log
where
mmt.organization_id = msi.organization_id and
mmt.inventory_item_id = msi.inventory_item_id and
mmt.transaction_action_id in (1, 27,31,32,30) and -- Issue, Receipt, Completion, Return,Scrap
mmt.transaction_source_type_id = 5 and -- Jobs abd Schedules
mmt.transaction_id = mmta.transaction_id (+) and
mmt.organization_id = log.organization_id and
mmt.transaction_id >= log.Start_txn_id and
mmt.transaction_id < log.Next_start_txn_id and
log.etl_id = 1 and
log.source = 1
group by
mmt.ORGANIZATION_ID,
mmt.INVENTORY_ITEM_ID,
mmt.transaction_source_id,
mmta.repetitive_schedule_id,
msi.PRIMARY_UOM_CODE,
mmt.transaction_date,
mmt.transaction_id,
mmt.reason_id,
mmt.transaction_action_id,
msi.mrp_planning_code
)mmt1,
WIP_ENTITIES we,
WIP_DISCRETE_JOBS wdj
where
mta1.transaction_id = mmt1.transaction_id and
mta1.organization_id = mmt1.organization_id and
mta1.inventory_item_id = mmt1.inventory_item_id and
mta1.transaction_source_id = mmt1.transaction_source_id and
mta1.transaction_date = mmt1.transaction_date and
(we.ENTITY_TYPE in (1,3,4,5,8) OR (we.ENTITY_TYPE = 2 and mta1.REPETITIVE_SCHEDULE_ID = mmt1.JOB_ID)) and
(mmt1.TXN_QTY <> 0 or mta1.BASE_TRANSACTION_VALUE <> 0) and
mta1.ORGANIZATION_ID = we.ORGANIZATION_ID and
mta1.transaction_source_id = WE.WIP_ENTITY_ID and
we.PRIMARY_ITEM_ID IS NOT NULL and
we.WIP_ENTITY_ID = wdj.WIP_ENTITY_ID (+) and
nvl (wdj.JOB_TYPE, 1) =1;
BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of ODM Txn Staging Table: '|| l_row_count || ' rows inserted');
select
from_bound_date,
to_bound_date
from
OPI_DBI_RUN_LOG_CURR
where
ETL_ID = 1 and
source = 2;
INSERT
INTO OPI_DBI_JOBS_TXN_STG
(
job_id,
job_type,
organization_id,
assembly_item_id,
component_item_id,
uom_code,
line_type,
transaction_date,
primary_quantity,
primary_quantity_draft,
transaction_value_b,
transaction_value_draft_b,
scrap_reason,
planned_item,
etl_type_id,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID
)
SELECT
MTL_DTL.batch_id,
4,
MTL_DTL.organization_id,
MTL_DTL.inventory_item_id,
GTV.inventory_item_id,
msi.PRIMARY_UOM_CODE,
GTV.line_type,
GTV.transaction_date,
-sum(decode(GTV.accounted_flag,'F',
MMT_STG.primary_quantity*decode(GTV.line_type,1,
decode(MTL_DTL.inventory_item_id,GTV.inventory_item_id,1,0),
2,
MTL_DTL.cost_alloc,
-1,
MTL_DTL.cost_alloc),0)) primary_quantity,
-sum(decode(GTV.accounted_flag,'D',
MMT_STG.primary_quantity*decode(GTV.line_type,1,
decode(MTL_DTL.inventory_item_id,GTV.inventory_item_id,1,0),
2,
MTL_DTL.cost_alloc,
-1,
MTL_DTL.cost_alloc),0)) primary_quantity_draft,
-sum(decode(GTV.accounted_flag,'F',
GTV.txn_base_value*decode(GTV.line_type,1,
decode(MTL_DTL.inventory_item_id,GTV.inventory_item_id,1,0),
2,
MTL_DTL.cost_alloc,
-1,
MTL_DTL.cost_alloc),0)) transaction_value_b,
-sum(decode(GTV.accounted_flag,'D',
GTV.txn_base_value*decode(GTV.line_type,1,
decode(MTL_DTL.inventory_item_id,GTV.inventory_item_id,1,0),
2,
MTL_DTL.cost_alloc,
-1,
MTL_DTL.cost_alloc),0)) transaction_value_draft_b,
-1,
decode (msi.mrp_planning_code,
NON_PLANNED_ITEM, 'N',
'Y') PLANNED_ITEM,
decode(GTV.line_type,-1,1,
2,1,
1,decode(gtv.inventory_item_id,mtl_dtl.inventory_item_id,2,-1))
ETL_TYPE_ID,
2,
s_sysdate,
s_sysdate,
s_user_id,
s_user_id,
s_login_id,
s_program_id,
s_program_login_id,
s_program_application_id,
s_request_id
FROM
(
select
gtv.transaction_id,
gtv.organization_id,
gtv.doc_id,
gtv.inventory_item_id,
gtv.line_type,
gtv.transaction_date,
nvl(gtv.accounted_flag,'F') accounted_flag,
sum(gtv.txn_base_value) txn_base_value
from
gmf_transaction_valuation gtv,
OPI_DBI_ORG_LE_TEMP tmp
where
gtv.journal_line_type in ('INV') and
--gtv.txn_source = 'PRODUCTION' and
gtv.event_class_code = 'BATCH_MATERIAL' and
gtv.transaction_date>= s_global_start_date and
( gtv.accounted_flag = 'D' OR -- All draft rows
( nvl(gtv.accounted_flag,'N') = 'N' and
gtv.final_posting_date between l_from_date and l_to_date
)
) and
gtv.ledger_id = tmp.ledger_id and
gtv.legal_entity_id = tmp.legal_entity_id and
gtv.valuation_cost_type_id = tmp.valuation_cost_type_id and
gtv.organization_id = tmp. organization_id
group by
gtv.transaction_id,
gtv.organization_id,
gtv.doc_id,
gtv.inventory_item_id,
gtv.line_type,
gtv.transaction_date,
gtv.accounted_flag
) GTV,
GME_MATERIAL_DETAILS MTL_DTL,
OPI_DBI_JOBS_TXN_MMT_STG MMT_STG,
mtl_system_items_b msi
where
GTV.organization_id = MTL_DTL.organization_id and
GTV.doc_id = MTL_DTL.batch_id and
MTL_DTL.line_type = 1 and --Product
GTV.transaction_id = MMT_STG.transaction_id and
MMT_STG.process_enabled_flag = 'Y' and
msi.organization_id = GTV.organization_id and
msi.inventory_item_id = GTV.inventory_item_id
group by
MTL_DTL.batch_id,
MTL_DTL.organization_id,
MTL_DTL.inventory_item_id,
GTV.inventory_item_id,
msi.PRIMARY_UOM_CODE,
GTV.line_type,
GTV.transaction_date,
msi.mrp_planning_code;
BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of OPM Txn Staging Table: '|| l_row_count || ' rows inserted');
select
from_bound_date,
to_bound_date
from
OPI_DBI_RUN_LOG_CURR
where
ETL_ID = 1 and
source = 2;
INSERT
INTO OPI_DBI_JOBS_TXN_STG
(
job_id,
job_type,
organization_id,
assembly_item_id,
component_item_id,
uom_code,
line_type,
transaction_date,
primary_quantity,
primary_quantity_draft,
transaction_value_b,
transaction_value_draft_b,
scrap_reason,
planned_item,
etl_type_id,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID
)
SELECT
MTL_DTL.batch_id,
4,
MTL_DTL.organization_id,
MTL_DTL.inventory_item_id,
GTV.inventory_item_id,
msi.PRIMARY_UOM_CODE,
GTV.line_type,
GTV.transaction_date,
-sum(decode(GTV.accounted_flag,'F',
MMT_STG.primary_quantity*decode(GTV.line_type,1,
decode(MTL_DTL.inventory_item_id,GTV.inventory_item_id,1,0),
2,
MTL_DTL.cost_alloc,
-1,
MTL_DTL.cost_alloc),0)) primary_quantity,
-sum(decode(GTV.accounted_flag,'D',
MMT_STG.primary_quantity*decode(GTV.line_type,1,
decode(MTL_DTL.inventory_item_id,GTV.inventory_item_id,1,0),
2,
MTL_DTL.cost_alloc,
-1,
MTL_DTL.cost_alloc),0)) primary_quantity_draft,
-sum(decode(GTV.accounted_flag,'F',
GTV.txn_base_value*decode(GTV.line_type,1,
decode(MTL_DTL.inventory_item_id,GTV.inventory_item_id,1,0),
2,
MTL_DTL.cost_alloc,
-1,
MTL_DTL.cost_alloc),0)) transaction_value_b,
-sum(decode(GTV.accounted_flag,'D',
GTV.txn_base_value*decode(GTV.line_type,1,
decode(MTL_DTL.inventory_item_id,GTV.inventory_item_id,1,0),
2,
MTL_DTL.cost_alloc,
-1,
MTL_DTL.cost_alloc),0)) transaction_value_draft_b,
-1,
decode (msi.mrp_planning_code,
NON_PLANNED_ITEM, 'N',
'Y') PLANNED_ITEM,
decode(GTV.line_type,-1,1,
2,1,
1,decode(gtv.inventory_item_id,mtl_dtl.inventory_item_id,2,-1))
ETL_TYPE_ID,
2,
s_sysdate,
s_sysdate,
s_user_id,
s_user_id,
s_login_id,
s_program_id,
s_program_login_id,
s_program_application_id,
s_request_id
FROM
(
select
gtv.transaction_id,
gtv.organization_id,
gtv.doc_id,
gtv.inventory_item_id,
gtv.line_type,
gtv.transaction_date,
nvl(gtv.accounted_flag,'F') accounted_flag,
sum(gtv.txn_base_value) txn_base_value
from
gmf_transaction_valuation gtv,
OPI_DBI_ORG_LE_TEMP tmp
where
gtv.journal_line_type in ('INV') and
--gtv.txn_source = 'PRODUCTION' and
gtv.event_class_code = 'BATCH_MATERIAL' and
gtv.transaction_date>= s_global_start_date and
( gtv.accounted_flag = 'D' OR -- All draft rows
( nvl(gtv.accounted_flag,'N') = 'N' and
gtv.final_posting_date between l_from_date and l_to_date
)
) and
gtv.ledger_id = tmp.ledger_id and
gtv.legal_entity_id = tmp.legal_entity_id and
gtv.valuation_cost_type_id = tmp.valuation_cost_type_id and
gtv.organization_id = tmp. organization_id
group by
gtv.transaction_id,
gtv.organization_id,
gtv.doc_id,
gtv.inventory_item_id,
gtv.line_type,
gtv.transaction_date,
gtv.accounted_flag
) GTV,
GME_MATERIAL_DETAILS MTL_DTL,
MTL_MATERIAL_TRANSACTIONS MMT_STG,
mtl_system_items_b msi
where
GTV.organization_id = MTL_DTL.organization_id and
GTV.doc_id = MTL_DTL.batch_id and
MTL_DTL.line_type = 1 and -- Products
GTV.transaction_id = MMT_STG.transaction_id and
--MMT_STG.process_enabled_flag = 'Y' and
msi.organization_id = GTV.organization_id and
msi.inventory_item_id = GTV.inventory_item_id
group by
MTL_DTL.batch_id,
MTL_DTL.organization_id,
MTL_DTL.inventory_item_id,
GTV.inventory_item_id,
msi.PRIMARY_UOM_CODE,
GTV.line_type,
GTV.transaction_date,
msi.mrp_planning_code;
BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of OPM Txn Staging Table: '|| l_row_count || ' rows inserted');
/* Inserting Pre R12 OPM MU Actuals to Jobs Transaction Staging */
l_stmt_num := 10;
INSERT
INTO OPI_DBI_JOBS_TXN_STG
(
job_id,
job_type,
organization_id,
assembly_item_id,
component_item_id,
uom_code,
line_type,
transaction_date,
primary_quantity,
primary_quantity_draft,
transaction_value_b,
transaction_value_draft_b,
scrap_reason,
planned_item,
etl_type_id,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID
)
select
scaled.batch_id,
scaled.job_type,
scaled.Organization_Id,
scaled.coproduct_id,
scaled.item_id,
scaled.item_um,
scaled.line_type,
itp.trans_date,
sum(itp.trans_qty * coprod.cost_alloc),
0,
-sum(led.amount_base * coprod.cost_alloc),
0,
null,
null,
1,
3,
s_sysdate,
s_sysdate,
s_user_id,
s_user_id,
s_login_id,
s_program_id,
s_program_login_id,
s_program_application_id,
s_request_id
from
OPI_DBI_OPM_SCALED_MTL scaled,
gme_material_details coprod,
gl_subr_led led,
(
SELECT
jobs.Organization_id,
jobs.Job_Id,
jobs.Job_Type,
jobs.Assembly_Item_id,
itp.trans_qty,
itp.doc_type,
itp.doc_id,
itp.line_id,
itp.trans_date,
itp.line_type
FROM
OPI_DBI_JOBS_F jobs,
IC_TRAN_PND itp
WHERE
jobs.source = 3 AND
itp.completed_ind = 1 AND
itp.doc_type = 'PROD' AND
itp.doc_id = jobs.job_id
GROUP BY
jobs.Organization_id,
jobs.Job_Id,
jobs.Job_Type,
jobs.Assembly_Item_id,
doc_type,
doc_id,
line_id,
trans_date,
trans_qty,
itp.line_type
)itp
where
coprod.line_type in (1) and
scaled.line_type in (-1, 2) and
coprod.organization_id = scaled.organization_id and
coprod.batch_id = scaled.batch_id and
coprod.inventory_item_id = scaled.coproduct_id and
itp.organization_id = scaled.organization_id and
itp.job_id = scaled.batch_id and
itp.assembly_item_id = scaled.coproduct_id and
led.doc_id = itp.job_id and
led.line_id = itp.line_id and
led.doc_type = 'PROD' and
led.acct_ttl_type = 1500 and
led.sub_event_type in (50010,50040)
group by
scaled.Organization_Id,
scaled.batch_id,
scaled.job_type,
scaled.coproduct_id,
scaled.item_id,
scaled.item_um,
scaled.line_type,
itp.trans_date;
/* Inserting Pre R12 OPM WIP Completions to Jobs Transaction Staging */
l_stmt_num := 20;
INSERT
INTO OPI_DBI_JOBS_TXN_STG
(
job_id,
job_type,
organization_id,
assembly_item_id,
component_item_id,
uom_code,
line_type,
transaction_date,
primary_quantity,
primary_quantity_draft,
transaction_value_b,
transaction_value_draft_b,
scrap_reason,
planned_item,
etl_type_id,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID
)
SELECT
itp.doc_id job_id,
4,
mtl_dtl.organization_id,
mtl_dtl.inventory_item_id,
mtl_dtl.inventory_item_id,
mtl_dtl.dtl_um,
mtl_dtl.line_type,
led.gl_trans_date,
-sum (itp.trans_qty),
0,
-sum (led.amount_base),
0,
null,
decode (msi.mrp_planning_code,NON_PLANNED_ITEM,
'N',
'Y') PLANNED_ITEM,
2,
3,
s_sysdate,
s_sysdate,
s_user_id,
s_user_id,
s_login_id,
s_program_id,
s_program_login_id,
s_program_application_id,
s_request_id
FROM
(SELECT
doc_type,
doc_id,
line_id,
TRUNC(trans_date) trans_date,
orgn_code,
item_id,
SUM(trans_qty) trans_qty
FROM
ic_tran_pnd
WHERE
doc_type = 'PROD' AND
line_type IN (1,2) AND
completed_ind = 1 AND
gl_posted_ind = 1 AND
trans_date >= s_global_start_date
GROUP BY
doc_type,
doc_id,
line_id,
TRUNC(trans_date),
orgn_code,
item_id
)itp,
(SELECT
sub.doc_type,
sub.doc_id,
sub.line_id,
TRUNC(sub.gl_trans_date) gl_trans_date,
SUM(sub.amount_base * sub.debit_credit_sign) amount_base
FROM
gl_subr_led sub
WHERE
sub.gl_trans_date >= s_global_start_date AND
sub.acct_ttl_type = 1500 AND
sub.doc_type = 'PROD'
GROUP BY
sub.doc_type,
sub.doc_id,
sub.line_id,
TRUNC(sub.gl_trans_date)
) led,
GME_MATERIAL_DETAILS mtl_dtl,
mtl_system_items_b msi
WHERE
itp.doc_type = led.doc_type AND
itp.doc_id = led.doc_id AND
itp.line_id = led.line_id AND
itp.trans_date = led.gl_trans_date AND
mtl_dtl.batch_id = itp.doc_id AND
mtl_dtl.material_detail_id = itp.line_id AND
msi.inventory_item_id = mtl_dtl.inventory_item_id AND
msi.organization_id = mtl_dtl.organization_id
GROUP BY
mtl_dtl.organization_id,
itp.doc_id,
mtl_dtl.inventory_item_id,
mtl_dtl.dtl_um,
mtl_dtl.line_type,
led.gl_trans_date,
msi.mrp_planning_code;
BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction Pre R12 OPM to Jobs Txn Staging: '|| l_row_count || ' rows inserted');
INSERT
INTO OPI_DBI_JOB_MTL_DETAILS_F
(
organization_id,
job_id,
job_type,
assembly_item_id,
component_item_id,
uom_code,
line_type,
transaction_date,
standard_value_b,
actual_value_b,
actual_value_draft_b,
standard_quantity,
actual_quantity,
actual_quantity_draft,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
actual_value_g,
actual_value_draft_g,
actual_value_sg,
actual_value_draft_sg,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID
)
select
jobs_txn.organization_id,
job_id,
job_type,
assembly_item_id,
component_item_id,
uom_code,
line_type,
trunc(jobs_txn.transaction_date),
0,
sum(transaction_value_b+transaction_value_draft_b),
sum(transaction_value_draft_b),
0, /* This fact will no more hold Stabdard Value and Standard Qty and hence 0 */
sum(primary_quantity+primary_quantity_draft),
sum(primary_quantity_draft),
source,
s_sysdate,
s_sysdate,
s_user_id,
s_user_id,
s_login_id,
sum((transaction_value_b+transaction_value_draft_b)*crates.conversion_rate),
sum(transaction_value_draft_b*crates.conversion_rate),
sum((transaction_value_b+transaction_value_draft_b)*crates.sec_conversion_rate),
sum(transaction_value_draft_b*crates.sec_conversion_rate),
s_program_id,
s_program_login_id,
s_program_application_id,
s_request_id
from
OPI_DBI_JOBS_TXN_STG jobs_txn,
opi_dbi_muv_conv_rates crates
where
etl_type_id = 1 and
crates.organization_id = jobs_txn.organization_id and
trunc(jobs_txn.transaction_date) = crates.transaction_date
group by
jobs_txn.organization_id,
job_id,
job_type,
assembly_item_id,
component_item_id,
uom_code,
line_type,
trunc(jobs_txn.transaction_date),
source;
BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of MTL USAGE ACTUALS Table: '|| l_row_count || ' rows inserted');
select
jobs_txn.organization_id organization_id,
job_id job_id,
job_type job_type,
assembly_item_id assembly_item_id,
component_item_id component_item_id,
uom_code uom_code,
line_type line_type,
trunc(jobs_txn.transaction_date) transaction_date,
0 standard_value_b,
sum(transaction_value_b+transaction_value_draft_b) actual_value_b,
sum(transaction_value_draft_b) actual_value_draft_b,
0 standard_quantity,
sum(primary_quantity+primary_quantity_draft) actual_quantity,
sum(primary_quantity_draft) actual_quantity_draft,
source source,
s_sysdate creation_date,
s_sysdate last_update_date,
s_user_id created_by,
s_user_id last_updated_by,
s_login_id last_update_login,
sum((transaction_value_b+transaction_value_draft_b)*crates.conversion_rate) actual_value_g,
sum(transaction_value_draft_b*crates.conversion_rate) actual_value_draft_g,
sum((transaction_value_b+transaction_value_draft_b)*crates.sec_conversion_rate) actual_value_sg,
sum(transaction_value_draft_b*crates.sec_conversion_rate) actual_value_draft_sg,
s_program_id PROGRAM_ID,
s_program_login_id PROGRAM_LOGIN_ID,
s_program_application_id PROGRAM_APPLICATION_ID,
s_request_id REQUEST_ID
from
OPI_DBI_JOBS_TXN_STG jobs_txn,
opi_dbi_muv_conv_rates crates
where
etl_type_id = 1 and
crates.organization_id = jobs_txn.organization_id and
trunc(jobs_txn.transaction_date) = crates.transaction_date
group by
jobs_txn.organization_id,
job_id,
job_type,
assembly_item_id,
component_item_id,
uom_code,
line_type,
trunc(jobs_txn.transaction_date),
source
)stg
ON
(
fact.organization_id = stg.organization_id and
fact.job_id = stg.job_id and
fact.job_type = stg.job_type and
fact.assembly_item_id = stg.assembly_item_id and
fact.component_item_id = stg.component_item_id and
fact.line_type = stg.line_type and
fact.transaction_date = stg.transaction_date and
fact.uom_code = stg.uom_code and
fact.source = stg.source
)
WHEN MATCHED THEN
UPDATE SET
fact.actual_quantity = fact.actual_quantity + stg.actual_quantity - fact.actual_quantity_draft,
fact.actual_quantity_draft = stg.actual_quantity_draft,
fact.actual_value_b = fact.actual_value_b + stg.actual_value_b - fact.actual_value_draft_b,
fact.actual_value_draft_b = stg.actual_value_draft_b,
fact.actual_value_g = fact.actual_value_g + stg.actual_value_g - fact.actual_value_draft_g,
fact.actual_value_draft_g = stg.actual_value_draft_g,
fact.actual_value_sg = fact.actual_value_sg + stg.actual_value_b - fact.actual_value_draft_sg,
fact.actual_value_draft_sg = stg.actual_value_draft_sg,
fact.creation_date = stg.creation_date,
fact.last_update_date = stg.last_update_date,
fact.created_by = stg.created_by,
fact.last_updated_by = stg.last_updated_by,
fact.last_update_login = stg.last_update_login,
fact.PROGRAM_ID = stg.PROGRAM_ID,
fact.PROGRAM_LOGIN_ID = stg.PROGRAM_LOGIN_ID,
fact.PROGRAM_APPLICATION_ID = stg.PROGRAM_APPLICATION_ID,
fact.REQUEST_ID = stg.REQUEST_ID
WHEN NOT MATCHED THEN
INSERT(
organization_id,
job_id,
job_type,
assembly_item_id,
component_item_id,
uom_code,
line_type,
transaction_date,
standard_value_b,
actual_value_b,
actual_value_draft_b,
standard_quantity,
actual_quantity,
actual_quantity_draft,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
actual_value_g,
actual_value_draft_g,
actual_value_sg,
actual_value_draft_sg,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID
)
VALUES
(
stg.organization_id,
stg.job_id,
stg.job_type,
stg.assembly_item_id,
stg.component_item_id,
stg.uom_code,
stg.line_type,
stg.transaction_date,
stg.standard_value_b,
stg.actual_value_b,
stg.actual_value_draft_b,
stg.standard_quantity,
stg.actual_quantity,
stg.actual_quantity_draft,
stg.source,
stg.creation_date,
stg.last_update_date,
stg.created_by,
stg.last_updated_by,
stg.last_update_login,
stg.actual_value_g,
stg.actual_value_draft_g,
stg.actual_value_sg,
stg.actual_value_draft_sg,
stg.PROGRAM_ID,
stg.PROGRAM_LOGIN_ID,
stg.PROGRAM_APPLICATION_ID,
stg.REQUEST_ID
);
BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of MTL USAGE ACTUALS Table: '|| l_row_count || ' rows inserted/updated');
/* ODM insert into temp table */
l_stmt_num := 20;
INSERT
INTO OPI_DBI_JOB_MTL_STD_QTY_TMP
(ORGANIZATION_ID,
INVENTORY_ITEM_ID,
JOB_ID,
JOB_TYPE,
Standard_Quantity
)
SELECT
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
JOB_ID,
JOB_TYPE,
Standard_Quantity
FROM
(
SELECT /* Standard Quantities for Discrete */
WRO.ORGANIZATION_ID,
WRO.INVENTORY_ITEM_ID,
WRO.WIP_ENTITY_ID JOB_ID,
decode(WE.ENTITY_TYPE,5,5,8,5,1) JOB_TYPE,
SUM(WRO.REQUIRED_QUANTITY) Standard_Quantity
FROM
WIP_ENTITIES WE,
WIP_REQUIREMENT_OPERATIONS WRO
WHERE
WRO.ORGANIZATION_ID = WE.ORGANIZATION_ID AND
WRO.WIP_ENTITY_ID = WE.WIP_ENTITY_ID AND
WE.ENTITY_TYPE in (1,3,5,8) AND
WE.WIP_ENTITY_ID in (SELECT JOB_ID FROM OPI_DBI_JOBS_F WHERE Std_Req_Flag=1 AND JOB_TYPE in (1,5))
GROUP BY
WRO.ORGANIZATION_ID,
WRO.INVENTORY_ITEM_ID,
WRO.WIP_ENTITY_ID,
WE.ENTITY_TYPE
UNION ALL
SELECT /* Standard Quantities for Repetitive */
WRO.ORGANIZATION_ID,
WRO.INVENTORY_ITEM_ID,
WRO.REPETITIVE_SCHEDULE_ID JOB_ID,
2 JOB_TYPE,
SUM(WRO.REQUIRED_QUANTITY) Standard_Quantity
FROM
WIP_ENTITIES WE,
WIP_REQUIREMENT_OPERATIONS WRO
WHERE
WRO.ORGANIZATION_ID = WE.ORGANIZATION_ID AND
WRO.WIP_ENTITY_ID = WE.WIP_ENTITY_ID AND
WE.ENTITY_TYPE = 2 AND
WRO.REPETITIVE_SCHEDULE_ID in (SELECT JOB_ID FROM OPI_DBI_JOBS_F WHERE Std_Req_Flag=1 AND JOB_TYPE=2)
GROUP BY
WRO.ORGANIZATION_ID,
WRO.INVENTORY_ITEM_ID,
WRO.REPETITIVE_SCHEDULE_ID
UNION ALL
SELECT /* Standard Quantities for Flow
Standard Qty for each component in BOM is multiplied with the planned
qty from wfs for the assembly to get the standard qty for each component.
*/
wfs.organization_id,
bom_join.component_item_id inventory_item_id,
wfs.wip_entity_id JOB_ID,
3 JOB_TYPE,
SUM(bom_join.Standard_Quantity) * wfs.PLANNED_QUANTITY Standard_Quantity
FROM
( select /*+ index(bb) */
bb.organization_id organization_id,
bb.assembly_item_id assembly_item_id,
bic.component_item_id component_item_id,
bic.effectivity_date effectivity_date,
bb.alternate_bom_designator alternate_bom_designator,
bic.disable_date disable_date,
nvl(lead(bic.effectivity_date) OVER
(partition by bb.organization_id,
bb.assembly_item_id,
bb.alternate_bom_designator,
bic.component_item_id,
bic.operation_seq_num
order by effectivity_date), sysdate) last_rev,
bic.component_quantity Standard_Quantity
from
bom_bill_of_materials bb,
bom_inventory_components bic
where
bb.COMMON_BILL_SEQUENCE_ID = bic.bill_sequence_id and
bic.implementation_date is not null
) bom_join,
wip_flow_schedules wfs
WHERE
EFFECTIVITY_DATE <= wfs.scheduled_completion_date and
last_rev > decode (sign(wfs.scheduled_completion_date - nvl(wfs.date_closed,wfs.scheduled_completion_date)),1,wfs.date_closed,wfs.scheduled_completion_date) and
decode (sign(wfs.scheduled_completion_date - nvl(wfs.date_closed,wfs.scheduled_completion_date)),1,wfs.date_closed,wfs.scheduled_completion_date) < nvl(bom_join.disable_date, sysdate) and
wfs.organization_id = bom_join.organization_id and
wfs.PRIMARY_ITEM_ID = bom_join.assembly_item_id and
nvl(wfs.alternate_bom_designator,1) = nvl(bom_join.alternate_bom_designator,1) and
WFS.WIP_ENTITY_ID in (SELECT JOB_ID FROM OPI_DBI_JOBS_F WHERE Std_Req_Flag=1 AND
JOB_TYPE=3)
GROUP BY
wfs.organization_id,
bom_join.component_item_id,
wfs.wip_entity_id,
wfs.PLANNED_QUANTITY);
/* ODM Standards insert into fact table */
l_stmt_num := 30;
INSERT
INTO OPI_DBI_JOB_MTL_DTL_STD_F
(
organization_id,
job_id,
job_type,
assembly_item_id,
component_item_id,
line_type,
standard_quantity,
standard_value_b,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID
)
select
actuals.organization_id,
actuals.job_id,
actuals.job_type,
actuals.assembly_item_id,
actuals.component_item_id,
actuals.line_type,
tmp.standard_quantity,
Decode(actuals.actual_quantity, 0,
tmp.standard_quantity*OPI_DBI_JOBS_PKG.GET_ODM_ITEM_COST
(actuals.organization_id,
actuals.component_item_id),
tmp.standard_quantity*(actual_value_b/actual_quantity)),
actuals.source,
s_sysdate,
s_sysdate,
s_user_id,
s_user_id,
s_login_id,
s_program_id,
s_program_login_id,
s_program_application_id,
s_request_id
from
OPI_DBI_JOB_MTL_STD_QTY_TMP tmp,
(select
job_id,
job_type,
organization_id,
assembly_item_id,
component_item_id,
line_type,
source,
sum(actual_quantity) actual_quantity,
sum(actual_value_b) actual_value_b
from
OPI_DBI_JOB_MTL_DETAILS_F
where
source = 1
group by
organization_id,
job_id,
assembly_item_id,
component_item_id,
line_type,
job_type,
source
)actuals
where
tmp.organization_id = actuals.organization_id and
tmp.job_id = actuals.job_id and
tmp.inventory_item_id = actuals.component_item_id and
tmp.job_type = actuals.job_type;
BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of ODM MTL USAGE Standards Table: '|| l_row_count || ' rows inserted');
/* ODM insert into temp table */
l_stmt_num := 20;
INSERT
INTO OPI_DBI_JOB_MTL_STD_QTY_TMP
(ORGANIZATION_ID,
INVENTORY_ITEM_ID,
JOB_ID,
JOB_TYPE,
Standard_Quantity
)
SELECT
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
JOB_ID,
JOB_TYPE,
Standard_Quantity
FROM
(
SELECT /* Standard Quantities for Discrete */
WRO.ORGANIZATION_ID,
WRO.INVENTORY_ITEM_ID,
WRO.WIP_ENTITY_ID JOB_ID,
decode(WE.ENTITY_TYPE,5,5,8,5,1) JOB_TYPE,
SUM(WRO.REQUIRED_QUANTITY) Standard_Quantity
FROM
WIP_ENTITIES WE,
WIP_REQUIREMENT_OPERATIONS WRO
WHERE
WRO.ORGANIZATION_ID = WE.ORGANIZATION_ID AND
WRO.WIP_ENTITY_ID = WE.WIP_ENTITY_ID AND
WE.ENTITY_TYPE in (1,3,5,8) AND
WE.WIP_ENTITY_ID in (SELECT JOB_ID FROM OPI_DBI_JOBS_F WHERE Std_Req_Flag=1 AND JOB_TYPE in (1,5))
GROUP BY
WRO.ORGANIZATION_ID,
WRO.INVENTORY_ITEM_ID,
WRO.WIP_ENTITY_ID,
WE.ENTITY_TYPE
UNION ALL
SELECT /* Standard Quantities for Repetitive */
WRO.ORGANIZATION_ID,
WRO.INVENTORY_ITEM_ID,
WRO.REPETITIVE_SCHEDULE_ID JOB_ID,
2 JOB_TYPE,
SUM(WRO.REQUIRED_QUANTITY) Standard_Quantity
FROM
WIP_ENTITIES WE,
WIP_REQUIREMENT_OPERATIONS WRO
WHERE
WRO.ORGANIZATION_ID = WE.ORGANIZATION_ID AND
WRO.WIP_ENTITY_ID = WE.WIP_ENTITY_ID AND
WE.ENTITY_TYPE = 2 AND
WRO.REPETITIVE_SCHEDULE_ID in (SELECT JOB_ID FROM OPI_DBI_JOBS_F WHERE Std_Req_Flag=1 AND JOB_TYPE=2)
GROUP BY
WRO.ORGANIZATION_ID,
WRO.INVENTORY_ITEM_ID,
WRO.REPETITIVE_SCHEDULE_ID
UNION ALL
SELECT /* Standard Quantities for Flow */
wfs.organization_id,
t.component_item_id inventory_item_id,
wfs.wip_entity_id JOB_ID,
3 JOB_TYPE,
SUM(t.Standard_Quantity) * wfs.PLANNED_QUANTITY Standard_Quantity
FROM
( select /*+ index(bb) */
bb.organization_id organization_id,
bb.assembly_item_id assembly_item_id,
bic.component_item_id component_item_id,
bic.effectivity_date effectivity_date,
bb.alternate_bom_designator alternate_bom_designator,
bic.disable_date disable_date,
nvl(lead(bic.effectivity_date) OVER
(partition by bb.organization_id,
bb.assembly_item_id,
bb.alternate_bom_designator,
bic.component_item_id,
bic.operation_seq_num
order by effectivity_date), sysdate) last_rev,
bic.component_quantity Standard_Quantity
from
bom_bill_of_materials bb,
bom_inventory_components bic
where
bb.COMMON_BILL_SEQUENCE_ID = bic.bill_sequence_id and
bic.implementation_date is not null
) t,
wip_flow_schedules wfs
WHERE
EFFECTIVITY_DATE <= wfs.scheduled_completion_date and
last_rev > decode (sign(wfs.scheduled_completion_date - nvl(wfs.date_closed,wfs.scheduled_completion_date)),1,wfs.date_closed,wfs.scheduled_completion_date) and
decode (sign(wfs.scheduled_completion_date - nvl(wfs.date_closed,wfs.scheduled_completion_date)),1,wfs.date_closed,wfs.scheduled_completion_date) < nvl(t.disable_date, sysdate) and
wfs.organization_id = t.organization_id and
wfs.PRIMARY_ITEM_ID = t.assembly_item_id and
nvl(wfs.alternate_bom_designator,1) = nvl(t.alternate_bom_designator,1) and
WFS.WIP_ENTITY_ID in (SELECT JOB_ID FROM OPI_DBI_JOBS_F WHERE Std_Req_Flag=1 AND
JOB_TYPE=3)
GROUP BY
wfs.organization_id,
t.component_item_id,
wfs.wip_entity_id,
wfs.PLANNED_QUANTITY);
select
actuals.organization_id organization_id,
actuals.job_id job_id,
actuals.job_type job_type,
actuals.assembly_item_id assembly_item_id,
actuals.component_item_id component_item_id,
actuals.line_type line_type,
tmp.standard_quantity standard_quantity,
Decode(actuals.actual_quantity, 0,
tmp.standard_quantity*OPI_DBI_JOBS_PKG.GET_ODM_ITEM_COST
(actuals.organization_id,
actuals.component_item_id),
tmp.standard_quantity*(actual_value_b/actual_quantity))
standard_value_b,
actuals.source source,
s_sysdate creation_date,
s_sysdate last_update_date,
s_user_id created_by,
s_user_id last_updated_by,
s_login_id last_update_login,
s_program_id PROGRAM_ID,
s_program_login_id PROGRAM_LOGIN_ID,
s_program_application_id PROGRAM_APPLICATION_ID,
s_request_id REQUEST_ID
from
OPI_DBI_JOB_MTL_STD_QTY_TMP tmp,
(select
job_id,
job_type,
organization_id,
assembly_item_id,
component_item_id,
line_type,
source,
sum(actual_quantity) actual_quantity,
sum(actual_value_b) actual_value_b
from
OPI_DBI_JOB_MTL_DETAILS_F
where
source = 1
group by
organization_id,
job_id,
assembly_item_id,
component_item_id,
line_type,
job_type,
source
)actuals
where
tmp.organization_id = actuals.organization_id and
tmp.job_id = actuals.job_id and
tmp.inventory_item_id = actuals.component_item_id and
tmp.job_type = actuals.job_type
)stg
ON
(
fact.organization_id = stg.organization_id and
fact.job_id = stg.job_id and
fact.job_type = stg.job_type and
fact.assembly_item_id = stg.assembly_item_id and
fact.component_item_id = stg.component_item_id and
fact.line_type = stg.line_type and
fact.source = stg.source
)
WHEN MATCHED THEN
UPDATE SET
fact.standard_quantity = stg.standard_quantity,
fact.standard_value_b = stg.standard_value_b,
fact.creation_date = stg.creation_date,
fact.last_update_date = stg.last_update_date,
fact.created_by = stg.created_by,
fact.last_updated_by = stg.last_updated_by,
fact.last_update_login = stg.last_update_login,
fact.PROGRAM_ID = stg.PROGRAM_ID,
fact.PROGRAM_LOGIN_ID = stg.PROGRAM_LOGIN_ID,
fact.PROGRAM_APPLICATION_ID = stg.PROGRAM_APPLICATION_ID,
fact.REQUEST_ID = stg.REQUEST_ID
WHEN NOT MATCHED THEN
INSERT
(
organization_id,
job_id,
job_type,
assembly_item_id,
component_item_id,
line_type,
standard_quantity,
standard_value_b,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID
)
VALUES
(
stg.organization_id,
stg.job_id,
stg.job_type,
stg.assembly_item_id,
stg.component_item_id,
stg.line_type,
stg.standard_quantity,
stg.standard_value_b,
stg.source,
stg.creation_date,
stg.last_update_date,
stg.created_by,
stg.last_updated_by,
stg.last_update_login,
stg.PROGRAM_ID,
stg.PROGRAM_LOGIN_ID,
stg.PROGRAM_APPLICATION_ID,
stg.REQUEST_ID
);
BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of ODM MTL USAGE Standards Table: '|| l_row_count || ' rows inserted');
/* OPM Standards insert into fact table */
/* Actuals fact which is at transaction date level is summarised and joined with the
scaled mtl table, standard value is calculated as actual-value* std_qty/actual_qty */
l_stmt_num := 40;
INSERT
INTO OPI_DBI_JOB_MTL_DTL_STD_F
(
organization_id,
job_id,
job_type,
assembly_item_id,
component_item_id,
line_type,
standard_quantity,
standard_value_b,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID
)
select
tmp.organization_id,
tmp.batch_id,
tmp.job_type,
tmp.coproduct_id,
tmp.item_id,
actuals.line_type,
sum(tmp.scaled_plan_qty*decode(actuals.line_type,2,-1,1)),
sum(decode(actuals.actual_qty,0,
OPI_DBI_JOBS_PKG.GET_OPM_ITEM_COST(tmp.organization_id,
tmp.item_id,
tmp.completion_date),
actuals.actual_value_b*tmp.scaled_plan_qty/actuals.actual_qty)
*decode(actuals.line_type,2,-1,1)),
actuals.source,
s_sysdate,
s_sysdate,
s_user_id,
s_user_id,
s_login_id,
s_program_id,
s_program_login_id,
s_program_application_id,
s_request_id
from
OPI_DBI_OPM_SCALED_MTL tmp,
(
select
job_id,
job_type,
organization_id,
assembly_item_id,
component_item_id,
line_type,
source,
sum(actual_quantity) actual_qty,
sum(actual_value_b) actual_value_b
from
OPI_DBI_JOB_MTL_DETAILS_F
where
source in(2,3)
group by
job_id,
job_type,
organization_id,
assembly_item_id,
component_item_id,
line_type,
source
)actuals
where
tmp.organization_id = actuals.organization_id and
tmp.batch_id = actuals.job_id and
tmp.job_type = actuals.job_type and
tmp.coproduct_id = actuals.assembly_item_id and
tmp.item_id = actuals.component_item_id and
tmp.line_type = actuals.line_type
group by
tmp.organization_id,
tmp.batch_id,
tmp.job_type,
tmp.coproduct_id,
tmp.item_id,
actuals.line_type,
actuals.source;
BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of OPM MTL USAGE Standards Table: '|| l_row_count || ' rows inserted');
/* OPM Standards insert into fact table */
l_stmt_num := 40;
select
tmp.organization_id organization_id,
tmp.batch_id job_id,
tmp.job_type job_type,
tmp.coproduct_id assembly_item_id,
tmp.item_id component_item_id,
actuals.line_type line_type,
sum(tmp.scaled_plan_qty*decode(actuals.line_type,2,-1,1)) standard_quantity,
sum(decode(actuals.actual_qty,0,
OPI_DBI_JOBS_PKG.GET_OPM_ITEM_COST(tmp.organization_id,
tmp.item_id,
tmp.completion_date),
actuals.actual_value_b*tmp.scaled_plan_qty/actuals.actual_qty)
*decode(actuals.line_type,2,-1,1)) standard_value_b,
actuals.source source,
s_sysdate creation_date,
s_sysdate last_update_date,
s_user_id created_by,
s_user_id last_updated_by,
s_login_id last_update_login,
s_program_id PROGRAM_ID,
s_program_login_id PROGRAM_LOGIN_ID,
s_program_application_id PROGRAM_APPLICATION_ID,
s_request_id REQUEST_ID
from
OPI_DBI_OPM_SCALED_MTL tmp,
(
select
job_id,
job_type,
organization_id,
assembly_item_id,
component_item_id,
line_type,
source,
sum(actual_quantity) actual_qty,
sum(actual_value_b) actual_value_b
from
OPI_DBI_JOB_MTL_DETAILS_F
where
source in(2,3)
group by
job_id,
job_type,
organization_id,
assembly_item_id,
component_item_id,
line_type,
source
)actuals
where
tmp.organization_id = actuals.organization_id and
tmp.batch_id = actuals.job_id and
tmp.job_type = actuals.job_type and
tmp.coproduct_id = actuals.assembly_item_id and
tmp.item_id = actuals.component_item_id and
tmp.line_type = actuals.line_type
group by
tmp.organization_id,
tmp.batch_id,
tmp.job_type,
tmp.coproduct_id,
tmp.item_id,
actuals.line_type,
actuals.source
)stg
ON
( fact.organization_id = stg.organization_id and
fact.job_id = stg.job_id and
fact.job_type = stg.job_type and
fact.assembly_item_id = stg.assembly_item_id and
fact.component_item_id = stg.component_item_id and
fact.line_type = stg.line_type and
fact.source = stg.source
)
WHEN MATCHED THEN
UPDATE SET
fact.standard_quantity = stg.standard_quantity,
fact.standard_value_b = stg.standard_value_b,
fact.last_update_date = stg.last_update_date,
fact.last_updated_by = stg.last_updated_by,
fact.last_update_login = stg.last_update_login
WHEN NOT MATCHED THEN
INSERT
(
organization_id,
job_id,
job_type,
assembly_item_id,
component_item_id,
line_type,
standard_quantity,
standard_value_b,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID
)
VALUES
(
stg.organization_id,
stg.job_id,
stg.job_type,
stg.assembly_item_id,
stg.component_item_id,
stg.line_type,
stg.standard_quantity,
stg.standard_value_b,
stg.source,
stg.creation_date,
stg.last_update_date,
stg.created_by,
stg.last_updated_by,
stg.last_update_login,
stg.PROGRAM_ID,
stg.PROGRAM_LOGIN_ID,
stg.PROGRAM_APPLICATION_ID,
stg.REQUEST_ID
);
BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of OPM MTL USAGE Standards Table: '|| l_row_count || ' rows inserted');
select
jobs.job_id job_id,
jobs.organization_id organization_id,
jobs.assembly_item_id,
mtl_dtl.cost_alloc cost_alloc
from
opi_dbi_jobs_f jobs,
gme_material_details mtl_dtl
where
jobs.source in (2,3) and
jobs.job_type = 4 and
jobs.status = 'Closed' and
jobs.line_type = 1 and
jobs.organization_id = mtl_dtl.organization_id and
jobs.job_id = mtl_dtl.batch_id and
jobs.assembly_item_id = mtl_dtl.inventory_item_id and
mtl_dtl.line_type = 1;
select
job.Organization_Id Organization_Id,
job.job_type Job_Type,
dtl.batch_id,
job.completion_date completion_date,
job.assembly_item_id coproduct_id,
dtl.material_detail_id material_detail_id,
job.start_quantity coproduct_plan_qty,
job.actual_qty_completed coproduct_actual_qty,
NULL scaled_plan_qty,
dtl.actual_qty,
dtl.dtl_um,
dtl.scale_type,
dtl.contribute_yield_ind,
dtl.scale_multiple,
dtl.scale_rounding_variance,
dtl.rounding_direction,
dtl.line_no,
dtl.line_type,
dtl.inventory_item_id ,
dtl.plan_qty,
cost_alloc_in
from
OPI_DBI_JOBS_F job,
gme_material_details dtl
where
job.job_id = dtl.batch_id
and job.status = 'Closed'
and dtl.line_type in (-1,2)
and job.job_id = job_id_in
and job.organization_id = org_id_in
and job.assembly_item_id = item_id_in
and job.line_type = 1
order by
dtl.batch_id,
job.assembly_item_id,
dtl.line_type;
INSERT INTO OPI_DBI_OPM_SCALED_MTL
(
ORGANIZATION_ID ,
JOB_TYPE,
BATCH_ID,
COPRODUCT_ID,
MATERIAL_DETAIL_ID,
COPRODUCT_PLAN_QTY,
COPRODUCT_ACTUAL_QTY,
SCALED_PLAN_QTY ,
ACTUAL_QTY,
ITEM_UM,
SCALE_TYPE,
CONTRIBUTE_YIELD_IND,
SCALE_MULTIPLE,
SCALE_ROUNDING_VARIANCE,
ROUNDING_DIRECTION,
LINE_NO,
LINE_TYPE,
item_id,
PLAN_QTY,
COMPLETION_DATE)
values
(
get_rec2.ORGANIZATION_ID,
get_rec2.JOB_TYPE,
get_rec2.BATCH_ID,
get_rec2.COPRODUCT_ID,
get_rec2.MATERIAL_DETAIL_ID,
get_rec2.COPRODUCT_PLAN_QTY,
get_rec2.COPRODUCT_ACTUAL_QTY,
l_scale_qty*l_cost_alloc,
get_rec2.ACTUAL_QTY,
get_rec2.DTL_UM,
get_rec2.SCALE_TYPE,
get_rec2.CONTRIBUTE_YIELD_IND,
get_rec2.SCALE_MULTIPLE,
get_rec2.SCALE_ROUNDING_VARIANCE,
get_rec2.ROUNDING_DIRECTION,
get_rec2.LINE_NO,
get_rec2.LINE_TYPE,
get_rec2.inventory_item_id,
get_rec2.PLAN_QTY,
get_rec2.COMPLETION_DATE);
select count(*) into l_row_count from OPI_DBI_OPM_SCALED_MTL;
BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of OPM Scaled Extraction: '|| l_row_count || ' rows inserted');
select
jobs.job_id job_id,
jobs.organization_id organization_id,
jobs.assembly_item_id,
mtl_dtl.cost_alloc cost_alloc
from
opi_dbi_jobs_stg jobs,
gme_material_details mtl_dtl
where
jobs.source in (2,3) and
jobs.job_type = 4 and
jobs.status = 'Closed' and
jobs.line_type = 1 and
jobs.organization_id = mtl_dtl.organization_id and
jobs.job_id = mtl_dtl.batch_id and
jobs.assembly_item_id = mtl_dtl.inventory_item_id and
mtl_dtl.line_type = 1;
select
job.Organization_Id Organization_Id,
job.job_type Job_Type,
dtl.batch_id,
job.completion_date completion_date,
job.assembly_item_id coproduct_id,
dtl.material_detail_id material_detail_id,
job.start_quantity coproduct_plan_qty,
job.actual_qty_completed coproduct_actual_qty,
NULL scaled_plan_qty,
dtl.actual_qty,
dtl.dtl_um,
dtl.scale_type,
dtl.contribute_yield_ind,
dtl.scale_multiple,
dtl.scale_rounding_variance,
dtl.rounding_direction,
dtl.line_no,
dtl.line_type,
dtl.inventory_item_id ,
dtl.plan_qty,
cost_alloc_in
from
OPI_DBI_JOBS_F job,
gme_material_details dtl
where
job.job_id = dtl.batch_id
and job.status = 'Closed'
and dtl.line_type in (-1,2)
and job.job_id = job_id_in
and job.organization_id = org_id_in
and job.assembly_item_id = item_id_in
and job.line_type = 1
order by
dtl.batch_id,
job.assembly_item_id,
dtl.line_type;
INSERT INTO OPI_DBI_OPM_SCALED_MTL
(
ORGANIZATION_ID ,
JOB_TYPE,
BATCH_ID,
COPRODUCT_ID,
MATERIAL_DETAIL_ID,
COPRODUCT_PLAN_QTY,
COPRODUCT_ACTUAL_QTY,
SCALED_PLAN_QTY ,
ACTUAL_QTY,
ITEM_UM,
SCALE_TYPE,
CONTRIBUTE_YIELD_IND,
SCALE_MULTIPLE,
SCALE_ROUNDING_VARIANCE,
ROUNDING_DIRECTION,
LINE_NO,
LINE_TYPE,
item_id,
PLAN_QTY,
COMPLETION_DATE)
values
(
get_rec2.ORGANIZATION_ID,
get_rec2.JOB_TYPE,
get_rec2.BATCH_ID,
get_rec2.COPRODUCT_ID,
get_rec2.MATERIAL_DETAIL_ID,
get_rec2.COPRODUCT_PLAN_QTY,
get_rec2.COPRODUCT_ACTUAL_QTY,
l_scale_qty*l_cost_alloc,
get_rec2.ACTUAL_QTY,
get_rec2.DTL_UM,
get_rec2.SCALE_TYPE,
get_rec2.CONTRIBUTE_YIELD_IND,
get_rec2.SCALE_MULTIPLE,
get_rec2.SCALE_ROUNDING_VARIANCE,
get_rec2.ROUNDING_DIRECTION,
get_rec2.LINE_NO,
get_rec2.LINE_TYPE,
get_rec2.inventory_item_id,
get_rec2.PLAN_QTY,
get_rec2.COMPLETION_DATE);
select count(*) into l_row_count from OPI_DBI_OPM_SCALED_MTL;
BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of OPM Scaled Extraction: '|| l_row_count || ' rows inserted');
INSERT
INTO OPI_DBI_WIP_COMP_F
(
organization_id,
inventory_item_id,
transaction_date,
completion_quantity,
completion_value_b,
uom_code,
conversion_rate,
planned_item,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
sec_conversion_rate,
job_id,
job_type,
line_type,
completion_quantity_draft,
completion_value_draft_b,
completion_value_g,
completion_value_draft_g,
completion_value_sg,
completion_value_draft_sg,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID
)
select
jobs_txn.organization_id,
assembly_item_id,
trunc(jobs_txn.transaction_date),
-sum(primary_quantity+primary_quantity_draft),
-sum(transaction_value_b+transaction_value_draft_b),
uom_code,
crates.conversion_rate,
planned_item,
source,
s_sysdate,
s_sysdate,
s_user_id,
s_user_id,
s_login_id,
crates.sec_conversion_rate,
job_id,
job_type,
line_type,
-sum(primary_quantity_draft),
-sum(transaction_value_draft_b),
-sum((transaction_value_b+transaction_value_draft_b)*crates.conversion_rate),
-sum(transaction_value_draft_b*crates.conversion_rate),
-sum((transaction_value_b+transaction_value_draft_b)*crates.sec_conversion_rate),
-sum(transaction_value_draft_b*crates.sec_conversion_rate),
s_program_id,
s_program_login_id,
s_program_application_id,
s_request_id
from
OPI_DBI_JOBS_TXN_STG jobs_txn,
opi_dbi_muv_conv_rates crates
where
jobs_txn.etl_type_id = 2 and
jobs_txn.organization_id = crates.organization_id and
trunc(jobs_txn.transaction_date) = crates.transaction_date
group by
jobs_txn.organization_id,
jobs_txn.job_id,
jobs_txn.job_type,
jobs_txn.assembly_item_id,
jobs_txn.component_item_id,
jobs_txn.uom_code,
jobs_txn.line_type,
trunc(jobs_txn.transaction_date),
jobs_txn.source,
crates.conversion_rate,
crates.sec_conversion_rate,
planned_item;
BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of WIP Completions Fact Table: '|| l_row_count || ' rows inserted');
select
jobs_txn.organization_id organization_id,
assembly_item_id inventory_item_id,
trunc(jobs_txn.transaction_date) transaction_date,
-sum(primary_quantity+primary_quantity_draft) completion_quantity,
-sum(transaction_value_b+transaction_value_draft_b) completion_value_b,
uom_code uom_code,
crates.conversion_rate conversion_rate,
planned_item planned_item,
source source,
s_sysdate creation_date,
s_sysdate last_update_date,
s_user_id created_by,
s_user_id last_updated_by,
s_login_id last_update_login,
sec_conversion_rate sec_conversion_rate,
job_id job_id,
job_type job_type,
line_type line_type,
-sum(primary_quantity_draft) completion_quantity_draft,
-sum(transaction_value_draft_b) completion_value_draft_b,
-sum((transaction_value_b+transaction_value_draft_b)*crates.conversion_rate) completion_value_g,
-sum(transaction_value_draft_b*crates.conversion_rate) completion_value_draft_g,
-sum((transaction_value_b+transaction_value_draft_b)*crates.sec_conversion_rate) completion_value_sg,
-sum(transaction_value_draft_b*crates.sec_conversion_rate) completion_value_draft_sg,
s_program_id PROGRAM_ID,
s_program_login_id PROGRAM_LOGIN_ID,
s_program_application_id PROGRAM_APPLICATION_ID,
s_request_id REQUEST_ID
from
OPI_DBI_JOBS_TXN_STG jobs_txn,
opi_dbi_muv_conv_rates crates
where
jobs_txn.etl_type_id = 2 and
jobs_txn.organization_id = crates.organization_id and
trunc(jobs_txn.transaction_date) = crates.transaction_date
group by
jobs_txn.organization_id,
jobs_txn.job_id,
jobs_txn.job_type,
jobs_txn.assembly_item_id,
jobs_txn.component_item_id,
jobs_txn.uom_code,
jobs_txn.line_type,
trunc(jobs_txn.transaction_date),
jobs_txn.source,
crates.conversion_rate,
crates.sec_conversion_rate,
planned_item
)stg
ON
(
fact.organization_id = stg.organization_id and
fact.job_id = stg.job_id and
fact.job_type = stg.job_type and
fact.inventory_item_id = stg.inventory_item_id and
fact.transaction_date = stg.transaction_date and
fact.line_type = stg.line_type and
fact.uom_code = stg.uom_code and
fact.source = stg.source
)
WHEN MATCHED THEN
UPDATE SET
fact.completion_quantity = fact.completion_quantity + stg.completion_quantity - fact.completion_quantity_draft,
fact.completion_quantity_draft = stg.completion_quantity_draft,
fact.completion_value_b = fact.completion_value_b + stg.completion_value_b - fact.completion_value_draft_b,
fact.completion_value_draft_b = stg.completion_value_draft_b,
fact.completion_value_g = fact.completion_value_g + stg.completion_value_g - fact.completion_value_draft_g,
fact.completion_value_draft_g = stg.completion_value_draft_g,
fact.completion_value_sg = fact.completion_value_sg + stg.completion_value_sg - fact.completion_value_draft_sg,
fact.completion_value_draft_sg = stg.completion_value_draft_sg,
fact.last_update_date = stg.last_update_date,
fact.last_updated_by = stg.last_updated_by,
fact.last_update_login = stg.last_update_login
WHEN NOT MATCHED THEN
INSERT
(organization_id,
inventory_item_id,
transaction_date,
completion_quantity,
completion_value_b,
uom_code,
conversion_rate,
planned_item,
source,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
sec_conversion_rate,
job_id,
job_type,
line_type,
completion_quantity_draft,
completion_value_draft_b,
completion_value_g,
completion_value_draft_g,
completion_value_sg,
completion_value_draft_sg,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID
)
VALUES
(
stg.organization_id,
stg.inventory_item_id,
stg.transaction_date,
stg.completion_quantity,
stg.completion_value_b,
stg.uom_code,
stg.conversion_rate,
stg.planned_item,
stg.source,
stg.creation_date,
stg.last_update_date,
stg.created_by,
stg.last_updated_by,
stg.last_update_login,
stg.sec_conversion_rate,
stg.job_id,
stg.job_type,
stg.line_type,
stg.completion_quantity_draft,
stg.completion_value_draft_b,
stg.completion_value_g,
stg.completion_value_draft_g,
stg.completion_value_sg,
stg.completion_value_draft_sg,
stg.PROGRAM_ID,
stg.PROGRAM_LOGIN_ID,
stg.PROGRAM_APPLICATION_ID,
stg.REQUEST_ID
);
BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of WIP Completions Fact Table: '|| l_row_count || ' rows inserted');
INSERT
INTO OPI_DBI_WIP_SCRAP_F
(
organization_id,
inventory_item_id,
transaction_date,
scrap_quantity,
scrap_value_b,
uom_code,
conversion_rate,
source,
planned_item,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
sec_conversion_rate,
job_id,
job_type,
scrap_reason_id,
scrap_value_g,
scrap_value_sg,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID
)
select
jobs_txn.organization_id organization_id,
assembly_item_id inventory_item_id,
jobs_txn.transaction_date transaction_date,
-sum(primary_quantity) scrap_quantity,
-sum(transaction_value_b) scrap_value_b,
uom_code uom_code,
crates.conversion_rate conversion_rate,
source source,
planned_item planned_item,
s_sysdate creation_date,
s_sysdate last_update_date,
s_user_id created_by,
s_user_id last_updated_by,
s_login_id last_update_login,
crates.sec_conversion_rate sec_conversion_rate,
job_id job_id,
job_type job_type,
scrap_reason scrap_reason_id,
-sum(transaction_value_b*crates.conversion_rate) scrap_value_g,
-sum(transaction_value_b*crates.sec_conversion_rate) scrap_value_sg,
s_program_id PROGRAM_ID,
s_program_login_id PROGRAM_LOGIN_ID,
s_program_application_id PROGRAM_APPLICATION_ID,
s_request_id REQUEST_ID
FROM
OPI_DBI_JOBS_TXN_STG jobs_txn,
opi_dbi_muv_conv_rates crates
WHERE
etl_type_id = 3 and
jobs_txn.organization_id = crates.organization_id and
trunc(jobs_txn.transaction_date) = crates.transaction_date
GROUP BY
jobs_txn.organization_id,
assembly_item_id,
jobs_txn.transaction_date,
uom_code,
crates.conversion_rate,
source,
planned_item,
crates.sec_conversion_rate,
job_id,
job_type,
scrap_reason;
BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of Scrap Fact Table: '|| l_row_count || ' rows inserted');
select
jobs_txn.organization_id organization_id,
assembly_item_id inventory_item_id,
jobs_txn.transaction_date transaction_date,
-sum(primary_quantity) scrap_quantity,
-sum(transaction_value_b) scrap_value_b,
uom_code uom_code,
crates.conversion_rate conversion_rate,
source source,
planned_item planned_item,
s_sysdate creation_date,
s_sysdate last_update_date,
s_user_id created_by,
s_user_id last_updated_by,
s_login_id last_update_login,
crates.sec_conversion_rate sec_conversion_rate,
job_id job_id,
job_type job_type,
scrap_reason scrap_reason_id,
-sum(transaction_value_b*crates.conversion_rate) scrap_value_g,
-sum(transaction_value_b*crates.sec_conversion_rate) scrap_value_sg,
s_program_id PROGRAM_ID,
s_program_login_id PROGRAM_LOGIN_ID,
s_program_application_id PROGRAM_APPLICATION_ID,
s_request_id REQUEST_ID
FROM
OPI_DBI_JOBS_TXN_STG jobs_txn,
opi_dbi_muv_conv_rates crates
WHERE
etl_type_id = 3 and
jobs_txn.organization_id = crates.organization_id and
trunc(jobs_txn.transaction_date) = crates.transaction_date
GROUP BY
jobs_txn.organization_id,
assembly_item_id,
jobs_txn.transaction_date,
uom_code,
crates.conversion_rate,
source,
planned_item,
crates.sec_conversion_rate,
job_id,
job_type,
scrap_reason
)stg
ON
( fact.organization_id = stg.organization_id and
fact.job_id = stg.job_id and
fact.job_type = stg.job_type and
fact.inventory_item_id = stg.inventory_item_id and
fact.transaction_date = stg.transaction_date and
fact.uom_code = stg.uom_code and
fact.scrap_reason_id = stg.scrap_reason_id and
fact.source = stg.source
)
WHEN MATCHED THEN
UPDATE SET
fact.scrap_quantity = stg.scrap_quantity,
fact.scrap_value_b = stg.scrap_value_b,
fact.scrap_value_g = stg.scrap_value_g,
fact.scrap_value_sg = stg.scrap_value_sg,
fact.last_update_date = stg.last_update_date,
fact.last_updated_by = stg.last_updated_by,
fact.last_update_login = stg.last_update_login
WHEN NOT MATCHED THEN
INSERT
(organization_id,
inventory_item_id,
transaction_date,
scrap_quantity,
scrap_value_b,
uom_code,
conversion_rate,
source,
planned_item,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
sec_conversion_rate,
job_id,
job_type,
scrap_reason_id,
scrap_value_g,
scrap_value_sg,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID
)
VALUES
(
stg.organization_id,
stg.inventory_item_id,
stg.transaction_date,
stg.scrap_quantity,
stg.scrap_value_b,
stg.uom_code,
stg.conversion_rate,
stg.source,
stg.planned_item,
stg.creation_date,
stg.last_update_date,
stg.created_by,
stg.last_updated_by,
stg.last_update_login,
stg.sec_conversion_rate,
stg.job_id,
stg.job_type,
stg.scrap_reason_id,
stg.scrap_value_g,
stg.scrap_value_sg,
stg.PROGRAM_ID,
stg.PROGRAM_LOGIN_ID,
stg.PROGRAM_APPLICATION_ID,
stg.REQUEST_ID
);
BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of Scrap Fact Table: '|| l_row_count || ' rows inserted');
after the first uncosted transaction and use this list of jobs and update
Include_Jobs in Jobs Table with 2 and leave the rest with 1 */
l_stmt_num := 20;
UPDATE /*+ parallel(f) */ OPI_DBI_JOBS_F f
SET
Include_Job = 2
WHERE
JOB_ID IN (
SELECT /*+ ordered use_nl(mmt,mmta) index(log) parallel(mmt) parallel(mmta) */
distinct decode(mmta.primary_quantity, null, mmt.transaction_source_id, mmta.repetitive_schedule_id) JOB_ID
FROM
MTL_MATERIAL_TRANSACTIONS MMT,
mtl_material_txn_allocations mmta,
OPI_DBI_RUN_LOG_CURR log
WHERE
MMT.TRANSACTION_ID >= log.Next_start_txn_id
and mmt.transaction_action_id in (1, 27)
and mmt.transaction_source_type_id = 5
and mmt.transaction_id = mmta.transaction_id (+)
and mmt.organization_id = log.organization_id
and log.source = 1
and log.etl_id = 1
)
AND SOURCE <> 2; /* Do not update OPM Jobs here */
UPDATE /*+ parallel(f) */ OPI_DBI_JOBS_F f
SET Std_Req_Flag = 0
WHERE Std_Req_Flag = 1 AND SOURCE=1;