[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT
mp.organization_code
, decode( least( r.conversion_rate1, r.conversion_rate2 )
, l_euro_missing_at_start, l_euro_start_date
, r.transaction_date) transaction_date
, r.base_currency_code
, nvl(r.conversion_rate1, -999) primary_rate
, nvl(r.conversion_rate2, -999) secondary_rate
FROM
isc_fs_inv_usg_conv_rates r
, mtl_parameters mp
, ( SELECT /*+ index_ffs(isc_fs_inv_usg_value_stg) */ DISTINCT
organization_id
, transaction_date
FROM isc_fs_inv_usg_value_stg
) s
WHERE ( nvl(r.conversion_rate1, -999) < 0 OR
nvl(r.conversion_rate2, -999) < 0 )
AND mp.organization_id = s.organization_id
AND r.transaction_date (+) = s.transaction_date
AND r.organization_id (+) = s.organization_id;
INSERT /*+ append */
INTO ISC_FS_INV_USG_CONV_RATES
( organization_id
, transaction_date
, base_currency_code
, conversion_rate1
, conversion_rate2
, creation_date
, last_update_date
, created_by
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
SELECT
s.organization_id
, s.transaction_date
, c.currency_code
, fii_currency.get_global_rate_primary
( c.currency_code
, s.transaction_date ) conversion_rate1
, decode( l_global_currency_code2
, null, 0 -- only attempt conversion if secondary currency defined
, fii_currency.get_global_rate_secondary
( c.currency_code
, s.transaction_date )
) conversion_rate2
, g_sysdate
, g_sysdate
, g_user_id
, g_user_id
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
FROM
( SELECT /*+ index_ffs(isc_fs_inv_usg_value_stg)
parallel_index(isc_fs_inv_usg_value_stg) */ DISTINCT
organization_id
, transaction_date
FROM
ISC_FS_INV_USG_VALUE_STG
) s
, ( SELECT 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)
) c
WHERE c.organization_id = s.organization_id;
BIS_COLLECTION_UTILITIES.LOG( l_row_count || ' rows inserted into currency conversion
table', 2 );
/* Insert into our log table the upper transaction boundaries for every organization to
be extracted. We will access OPI_DBI_CONC_PROG_RUN_LOG to obtain these upper boundaries. The
incremental load will use these boundaries as starting points. */
l_stmt_num := 20;
INSERT INTO ISC_FS_INV_USG_LOG
(
ORGANIZATION_ID
,FROM_TRANSACTION_ID
,TO_TRANSACTION_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_ID
,PROGRAM_LOGIN_ID
,PROGRAM_APPLICATION_ID
,REQUEST_ID
)
SELECT
from_to_txn.BOUND_LEVEL_ENTITY_ID organization_id
,min(from_to_txn.FROM_BOUND_ID) FROM_TRANSACTION_ID
,case
when nvl(max(from_to_txn_incr.TO_BOUND_ID),-99) > max(from_to_txn.TO_BOUND_ID) then max(from_to_txn_incr.TO_BOUND_ID)
else max(from_to_txn.TO_BOUND_ID)
end TO_TRANSACTION_ID /* The to_transaction_id must come from the INCR row if there is one */
/* the nvl is in case the inventory page incremental has not been run yet */
,g_user_id
,g_sysdate
,g_sysdate
,g_user_id
,g_login_id
,g_program_id
,g_program_login_id
,g_program_application_id
,g_request_id
FROM
OPI_DBI_CONC_PROG_RUN_LOG from_to_txn
,OPI_DBI_CONC_PROG_RUN_LOG from_to_txn_incr
/* Change in Boundaries Log table in OPI caused a change in how our code handles MMT boundaries */
WHERE
from_to_txn.etl_type = 'INVENTORY'
AND from_to_txn.BOUND_TYPE = 'ID'
AND from_to_txn.DRIVING_TABLE_CODE = 'MMT'
AND from_to_txn.LOAD_TYPE = 'INIT'
AND from_to_txn.BOUND_LEVEL_ENTITY_CODE = 'ORGANIZATION'
AND from_to_txn_incr.etl_type (+) = 'INVENTORY'
AND from_to_txn_incr.BOUND_TYPE (+) = 'ID'
AND from_to_txn_incr.DRIVING_TABLE_CODE (+) = 'MMT'
AND from_to_txn_incr.LOAD_TYPE (+) = 'INCR'
AND from_to_txn_incr.BOUND_LEVEL_ENTITY_CODE (+) = 'ORGANIZATION'
AND from_to_txn.BOUND_LEVEL_ENTITY_ID = from_to_txn_incr.BOUND_LEVEL_ENTITY_ID (+)
GROUP BY
from_to_txn.BOUND_LEVEL_ENTITY_ID
UNION /* This union is for Organizations that were created after initial load of inventory page */
SELECT
from_to_txn.BOUND_LEVEL_ENTITY_ID organization_id
,-1 FROM_TRANSACTION_ID
/* Organizations that were created after initial load of inventory page,
hence after GSD, do not have INIT rows */
,from_to_txn.TO_BOUND_ID TO_TRANSACTION_ID
,g_user_id
,g_sysdate
,g_sysdate
,g_user_id
,g_login_id
,g_program_id
,g_program_login_id
,g_program_application_id
,g_request_id
FROM
OPI_DBI_CONC_PROG_RUN_LOG from_to_txn
/* Change in Boundaries Log table in OPI caused a change in how our code handles MMT boundaries */
WHERE
from_to_txn.etl_type = 'INVENTORY'
AND from_to_txn.BOUND_TYPE = 'ID'
AND from_to_txn.DRIVING_TABLE_CODE = 'MMT'
AND from_to_txn.LOAD_TYPE = 'INCR'
AND from_to_txn.BOUND_LEVEL_ENTITY_CODE = 'ORGANIZATION'
AND not exists (SELECT 'X' FROM OPI_DBI_CONC_PROG_RUN_LOG olog
WHERE
olog.etl_type = 'INVENTORY'
AND olog.BOUND_TYPE = 'ID'
AND olog.DRIVING_TABLE_CODE = 'MMT'
AND olog.LOAD_TYPE = 'INIT'
AND from_to_txn.BOUND_LEVEL_ENTITY_ID = olog.BOUND_LEVEL_ENTITY_ID);
/* Insert field service material issue transaction values into staging table */
INSERT /*+ append parallel(ISC_FS_INV_USG_VALUE_STG) */ INTO ISC_FS_INV_USG_VALUE_STG
(
ORGANIZATION_ID
,SUBINVENTORY_CODE
,TRANSACTION_DATE
,INVENTORY_ITEM_ID
,ONHAND_VALUE_B
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_ID
,PROGRAM_LOGIN_ID
,PROGRAM_APPLICATION_ID
,REQUEST_ID
)
SELECT /*+ ordered use_hash(mmt,mta,bound) swap_join_inputs(bound)
parallel(mmt) parallel(mta) pq_distribute(mta,hash,hash) pq_distribute(bound,none,broadcast) */
mta.ORGANIZATION_ID
,mmt.SUBINVENTORY_CODE
,trunc(mta.TRANSACTION_DATE) TRANSACTION_DATE
,mta.INVENTORY_ITEM_ID
,SUM(mta.BASE_TRANSACTION_VALUE)*(-1) ONHAND_VALUE_B /* Issue transactions are
expressed in negative numbers */
,g_user_id
,g_sysdate
,g_sysdate
,g_user_id
,g_login_id
,g_program_id
,g_program_login_id
,g_program_application_id
,g_request_id
FROM
(SELECT
csi1.organization_id
,csi1.SECONDARY_INVENTORY_NAME
,cutt.TRANSACTION_TYPE_ID
,mtt.TRANSACTION_ACTION_ID
,mtt.TRANSACTION_SOURCE_TYPE_ID
FROM
CSP_PLANNING_PARAMETERS cpp
,CSP_USG_TRANSACTION_TYPES cutt
,CSP_SEC_INVENTORIES csi1
,MTL_TRANSACTION_TYPES mtt
WHERE
cpp.FORECAST_RULE_ID = cutt.FORECAST_RULE_ID
and csi1.organization_id = cpp.organization_id
and csi1.SECONDARY_INVENTORY_NAME = cpp.SECONDARY_INVENTORY
and csi1.CONDITION_TYPE = 'G' /* Usable Subinventory */
and mtt.TRANSACTION_TYPE_ID = cutt.TRANSACTION_TYPE_ID
and mtt.TRANSACTION_ACTION_ID = 1 /* Issue */
UNION
SELECT /* For subinventories without forecast rule defined: Use this default
transaction type */
csi2.organization_id
,csi2.SECONDARY_INVENTORY_NAME
,93 transaction_type_id /* Field Service Usage transaction type */
,1 TRANSACTION_ACTION_ID
,13 TRANSACTION_SOURCE_TYPE_ID
FROM
CSP_SEC_INVENTORIES csi2
WHERE
csi2.CONDITION_TYPE = 'G' /* Usable Subinventory */
and not exists
(select 'x' from CSP_PLANNING_PARAMETERS cpp
where cpp.organization_id = csi2.organization_id
and cpp.SECONDARY_INVENTORY = csi2.secondary_inventory_name
and cpp.forecast_rule_id is not null) /* Do not include subinventories that
have forecast rules defined */
) sec
,mtl_material_transactions mmt
,ISC_FS_INV_USG_LOG bound /* Obtain the transaction boundaries from our log table */
,mtl_transaction_accounts mta
WHERE
mmt.organization_id = sec.organization_id
and mmt.SUBINVENTORY_CODE = sec.SECONDARY_INVENTORY_NAME
and mmt.TRANSACTION_ACTION_ID = sec.TRANSACTION_ACTION_ID
and mmt.TRANSACTION_TYPE_ID = sec.TRANSACTION_TYPE_ID
and mmt.TRANSACTION_SOURCE_TYPE_ID = sec.TRANSACTION_SOURCE_TYPE_ID
and mmt.organization_id = bound.organization_id
/* Note that the boundary conditions have changed from > to >= and from <= to < due to changes in OPI's log table logic */
and mmt.transaction_id >= bound.from_transaction_id
and mmt.transaction_id < bound.to_transaction_id
and mta.accounting_line_type = 1 /* Inventory Valuation */
and mta.transaction_id = mmt.transaction_id
GROUP BY
mta.ORGANIZATION_ID
,mmt.SUBINVENTORY_CODE
,mta.INVENTORY_ITEM_ID
,trunc(mta.TRANSACTION_DATE);
BIS_COLLECTION_UTILITIES.LOG( l_row_count || ' rows inserted into staging table', 1 );
/* Insert field service inventory usage value data into the DBI Field Service Inventory
Usage Value Base Summary table based on Staging table and Current Conversion table */
l_stmt_num := 80;
INSERT /* append parallel(f) */ INTO ISC_FS_INV_USG_VALUE_F f
(
ORGANIZATION_ID
,SUBINVENTORY_CODE
,TRANSACTION_DATE
,INVENTORY_ITEM_ID
,ONHAND_VALUE_B
,PRIM_CONVERSION_RATE
,SEC_CONVERSION_RATE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_ID
,PROGRAM_LOGIN_ID
,PROGRAM_APPLICATION_ID
,REQUEST_ID
)
SELECT /* parallel(s) parallel(c) */
s.ORGANIZATION_ID
,s.SUBINVENTORY_CODE
,s.TRANSACTION_DATE
,s.INVENTORY_ITEM_ID
,s.ONHAND_VALUE_B
,c.conversion_rate1
,c.conversion_rate2
,g_user_id
,g_sysdate
,g_sysdate
,g_user_id
,g_login_id
,g_program_id
,g_program_login_id
,g_program_application_id
,g_request_id
FROM
ISC_FS_INV_USG_VALUE_STG s
,ISC_FS_INV_USG_CONV_RATES c
WHERE
c.organization_id = s.organization_id
AND c.transaction_date = s.transaction_date;
BIS_COLLECTION_UTILITIES.LOG( l_row_count || ' rows inserted into base summary', 1 );
/* Update or insert into our log table the new upper transaction boundaries for every
organization to be extracted. We will access OPI_DBI_INV_VALUE_LOG to obtain these upper
boundaries. The next incremental load will use these boundaries as starting points.
(Note: We will be using the from transaction id column to store the upper transaction
boundary momentarily during the collection since the to transaction id column already
stores the lower transaction id. At the end of the collection we will swith the from and
to boundaries.) */
l_stmt_num := 5;
/* This select statement has changed due to OPI changes in its Boundaries log table */
SELECT
from_to_txn.BOUND_LEVEL_ENTITY_ID organization_id
,from_to_txn.TO_BOUND_ID FROM_TRANSACTION_ID
/* This will become the To transaction at the end of the collection. We are temporarily storing the To boundary
in the from transaction column */
FROM
OPI_DBI_CONC_PROG_RUN_LOG from_to_txn
WHERE
from_to_txn.etl_type = 'INVENTORY'
AND from_to_txn.BOUND_TYPE = 'ID'
AND from_to_txn.DRIVING_TABLE_CODE = 'MMT'
AND from_to_txn.LOAD_TYPE = 'INCR'
AND from_to_txn.BOUND_LEVEL_ENTITY_CODE = 'ORGANIZATION'
) opilog
ON (bivlog.organization_id = opilog.organization_id)
WHEN matched THEN
UPDATE SET
bivlog.FROM_TRANSACTION_ID = opilog.FROM_TRANSACTION_ID
,bivlog.LAST_UPDATE_DATE = g_sysdate
,bivlog.LAST_UPDATED_BY = g_user_id
,bivlog.LAST_UPDATE_LOGIN = g_login_id
,bivlog.PROGRAM_ID = g_program_id
,bivlog.PROGRAM_LOGIN_ID = g_program_login_id
,bivlog.PROGRAM_APPLICATION_ID = g_program_application_id
,bivlog.REQUEST_ID = g_request_id
WHEN NOT matched THEN
INSERT /* If new organization has been created after field service collection */
(
ORGANIZATION_ID
,FROM_TRANSACTION_ID
,TO_TRANSACTION_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_ID
,PROGRAM_LOGIN_ID
,PROGRAM_APPLICATION_ID
,REQUEST_ID
)
VALUES
(
opilog.ORGANIZATION_ID
,opilog.FROM_TRANSACTION_ID /* This temporarily contains the To transaction boundary */
,-1 /* It will become the from transaction id at the end of the collection */
/* We use -1 because the OPI Incremental could have run more than once since
the last time the Field Service Incremental was run */
,g_user_id
,g_sysdate
,g_sysdate
,g_user_id
,g_login_id
,g_program_id
,g_program_login_id
,g_program_application_id
,g_request_id
);
BIS_COLLECTION_UTILITIES.LOG( 'Log Table updated', 1);
/* Insert field service material issue transaction values into staging table */
l_stmt_num := 30;
INSERT /*+ append parallel(ISC_FS_INV_USG_VALUE_STG) */ INTO ISC_FS_INV_USG_VALUE_STG
(
ORGANIZATION_ID
,SUBINVENTORY_CODE
,TRANSACTION_DATE
,INVENTORY_ITEM_ID
,ONHAND_VALUE_B
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_ID
,PROGRAM_LOGIN_ID
,PROGRAM_APPLICATION_ID
,REQUEST_ID
)
SELECT
mta.ORGANIZATION_ID
,mmt.SUBINVENTORY_CODE
,trunc(mta.TRANSACTION_DATE) TRANSACTION_DATE
,mta.INVENTORY_ITEM_ID
,SUM(mta.BASE_TRANSACTION_VALUE)*(-1) ONHAND_VALUE_B /* Issue transactions are expressed
in negative numbers */
,g_user_id
,g_sysdate
,g_sysdate
,g_user_id
,g_login_id
,g_program_id
,g_program_login_id
,g_program_application_id
,g_request_id
FROM
mtl_transaction_accounts mta,
(SELECT
csi1.organization_id
,csi1.SECONDARY_INVENTORY_NAME
,cutt.TRANSACTION_TYPE_ID
,mtt.TRANSACTION_ACTION_ID
,mtt.TRANSACTION_SOURCE_TYPE_ID
FROM
CSP_PLANNING_PARAMETERS cpp,
CSP_USG_TRANSACTION_TYPES cutt,
CSP_SEC_INVENTORIES csi1,
MTL_TRANSACTION_TYPES mtt
WHERE
cpp.FORECAST_RULE_ID = cutt.FORECAST_RULE_ID
AND csi1.organization_id = cpp.organization_id
AND csi1.SECONDARY_INVENTORY_NAME = cpp.SECONDARY_INVENTORY
AND csi1.CONDITION_TYPE = 'G' /* Usable Subinventory */
AND mtt.TRANSACTION_TYPE_ID = cutt.TRANSACTION_TYPE_ID
AND mtt.TRANSACTION_ACTION_ID = 1 /* Issue */
UNION
SELECT /* For subinventories without forecast rule defined: Use this default transaction
type */
csi2.organization_id
,csi2.SECONDARY_INVENTORY_NAME
,93 transaction_type_id /* Field Service Usage transaction type */
,1 TRANSACTION_ACTION_ID
,13 TRANSACTION_SOURCE_TYPE_ID
FROM CSP_SEC_INVENTORIES csi2
WHERE
csi2.CONDITION_TYPE = 'G' /* Usable Subinventory */
and not exists
(select 'x' from CSP_PLANNING_PARAMETERS cpp
where cpp.organization_id = csi2.organization_id
and cpp.SECONDARY_INVENTORY = csi2.secondary_inventory_name
and cpp.forecast_rule_id is not null) /* Do not include subinventories that
have forecast rules defined */
) sec,
mtl_material_transactions mmt
,ISC_FS_INV_USG_LOG bound /* Obtain the transaction boundaries from our log table */
WHERE
mmt.organization_id = sec.organization_id
AND mmt.SUBINVENTORY_CODE = sec.SECONDARY_INVENTORY_NAME
AND mmt.TRANSACTION_ACTION_ID = sec.TRANSACTION_ACTION_ID
AND mmt.TRANSACTION_TYPE_ID = sec.TRANSACTION_TYPE_ID
AND mmt.TRANSACTION_SOURCE_TYPE_ID = sec.TRANSACTION_SOURCE_TYPE_ID
AND mta.accounting_line_type = 1
AND mta.transaction_id = mmt.transaction_id
AND mta.organization_id = bound.organization_id
/* Note that the boundary conditions have changed from > to >= and from <= to < due to changes in OPI's log table logic */
AND mta.transaction_id >= bound.to_transaction_id /* The from and to boundaries are
switched in the log table at this point */
AND mta.transaction_id < bound.from_transaction_id
GROUP BY
mta.ORGANIZATION_ID
,mmt.SUBINVENTORY_CODE
,mta.INVENTORY_ITEM_ID
,trunc(mta.TRANSACTION_DATE);
BIS_COLLECTION_UTILITIES.LOG( l_row_count || ' rows inserted into staging table', 1 );
SELECT /* parallel(s) parallel(c) */
s.ORGANIZATION_ID
,s.SUBINVENTORY_CODE
,s.TRANSACTION_DATE
,s.INVENTORY_ITEM_ID
,s.ONHAND_VALUE_B
,c.conversion_rate1
,c.conversion_rate2
FROM
ISC_FS_INV_USG_VALUE_STG s
,ISC_FS_INV_USG_CONV_RATES c
WHERE
c.organization_id = s.organization_id
AND c.transaction_date = s.transaction_date
) t
ON
(t.organization_id = f.organization_id
AND t.subinventory_code = f.subinventory_code
AND t.inventory_item_id = f.inventory_item_id
AND t.transaction_date = f.transaction_date
)
WHEN MATCHED THEN
UPDATE SET
f.ONHAND_VALUE_B = f.ONHAND_VALUE_B + t.ONHAND_VALUE_B
,f.LAST_UPDATE_DATE = g_sysdate
,f.LAST_UPDATED_BY = g_user_id
,f.LAST_UPDATE_LOGIN = g_login_id
,f.PROGRAM_ID = g_program_id
,f.PROGRAM_LOGIN_ID = g_program_login_id
,f.PROGRAM_APPLICATION_ID = g_program_application_id
,f.REQUEST_ID = g_request_id
WHEN NOT MATCHED THEN
INSERT
(
ORGANIZATION_ID
,SUBINVENTORY_CODE
,TRANSACTION_DATE
,INVENTORY_ITEM_ID
,ONHAND_VALUE_B
,PRIM_CONVERSION_RATE
,SEC_CONVERSION_RATE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_ID
,PROGRAM_LOGIN_ID
,PROGRAM_APPLICATION_ID
,REQUEST_ID
)
VALUES
(
t.ORGANIZATION_ID
,t.SUBINVENTORY_CODE
,t.TRANSACTION_DATE
,t.INVENTORY_ITEM_ID
,t.ONHAND_VALUE_B
,t.conversion_rate1
,t.conversion_rate2
,g_user_id
,g_sysdate
,g_sysdate
,g_user_id
,g_login_id
,g_program_id
,g_program_login_id
,g_program_application_id
,g_request_id
);
BIS_COLLECTION_UTILITIES.LOG( l_row_count || ' rows inserted into base summary', 1 );
UPDATE ISC_FS_INV_USG_LOG
SET from_transaction_id = to_transaction_id,to_transaction_id = from_transaction_id;
BIS_COLLECTION_UTILITIES.LOG( 'Log table updated', 1 );