[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
BIS_COLLECTION_UTILITIES.PUT_LINE ('Inserting data into the fact table ...');
WHEN BOUNDS_UPDATE_ERROR THEN
rollback;
(BOUNDS_UPDATE_ERROR_MESG,
l_proc_name, l_stmt_id));
Insert /*+ append parallel (opi_dbi_mmt_cca_stg) */
into OPI_DBI_MMT_CCA_STG
(TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
CYCLE_COUNT_ID,
SUBINVENTORY_CODE,
TRANSFER_SUBINVENTORY,
TRANSACTION_DATE,
TRANSACTION_TYPE_ID,
PRIMARY_QUANTITY)
Select TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
CYCLE_COUNT_ID,
SUBINVENTORY_CODE,
TRANSFER_SUBINVENTORY,
TRANSACTION_DATE,
TRANSACTION_TYPE_ID,
PRIMARY_QUANTITY
From MTL_MATERIAL_TRANSACTIONS
Where Transaction_date >= s_global_start_date
and Transaction_source_type_id = 9 ;
interim cost updates may cause the values recorded in MCCE to
be different. Thus we need to get the cost associated to the item
using the value in MTA so that the reported adjustments match
the MTA reported value.
However, due to issues with layered costing in LIFO/FIFO orgs, the
system inventory value is computed using the cost at the time when
the entry was made. Thus the reported system inventory value
matches the one reported in Oracle Forms.
We are only interested in entries completed after the global start
date (i.e. entry_status_code = 5 and approval_date > global start date).
But it is not possible mmt_mta join to return records where cycle count
entries are not completed.
The only MTA rows we are interested in are those that affect the
inventory account (accounting line type = 1).
MTA does not store rows for expense items, or items in expense
subinventories. Thus the join between MMT and MTA will have to
be an outer join. Rows with no matches will be assigned an
item cost of 0.
MCCE does not store quantities in the primary uom_code. If the
transaction uom is not the same as the primary uom, all
quantities will have to be converted to the primary uom.
There is a need to check for adjustment quantity = 0 serial item counts
can have count level matches, but serial number level adjustments,
To handle this, make four enhancements:
1. Declare exact matches if the sum of the MMT primary quantity is 0.
2. Pick the MCCE item_unit_cost if the quantity is 0 (check for expense
subs before that).
3. Set the tolerance to 0 if there is an adjustment in another
subinventory that the one counted. This will automatically
make any adjustments against it misses.
4. The system_inventory_qty for the other subinventory is the negative
of the adjustment quantity, so that the sum of the adjustment
and system quantity is always 0.
We will only be scanning MMT rows between the transaction ranges
in the log table.
Also, since the fact table is at a cycle count entry level,
it is impossible for the sum (mmt.primary_quantity) to be 0. Thus
the item_cost formula of sum (value)/sum (quantity) is always
valid and we need not worry about sum (quantity) being 0 or NULL.
Since the join conditions between MCCE - MMT - MTA is one to many
to many, join MMT/MTA on transaction_id first.
Also, to handle serial item adjustments where the adjustment
automatically issues an item from a sub to move it to the next,
use the subinventory_code from MMT, not MCCE.
There cannot be an entry in MMT and MTA for which cycle count has not been approved.
ie entry_status_code = 5.
-------- R12 Changes
1> Subinventory Transfer type of transaction (MMT.transaction_type_id = 5)
is seen in Cycle Count. This case occurs when a LPN is received in
say subinv. BULK, and cycle count is done in say subinv. CASE for a specific LPN.
This type of transaction has 2 rows in MMT, one for issue transaction
and other for receipt transaction. But the MTA has no rows for receipt
transaction while it has 2 entries differing only in MTA.primary_quantity.
The subinventory for issue transaction where mta.primary_quantity is -ve and
transfer_subinventory for issue transaction where mta.primary_quantity is
positive.
This case will not be encountered for Expense item as LPN cannot be made
for expense item.
Such transactions can only be done through Warehouse mobile.
Currently for such transactions MMT.cycle_count_id is not populated.
Bug # 4392340 has been logged for the same.
2> Changes to log table and columns.
3> mmt staging table is introduced to collect all mmt transactions
for discrete and process orgs at one go. Here as we are joining
with bounds table which has bounds for discrete orgs by
transaction_id we do not need to put any filters to get only
discrete organizations. For process there is one single record in
bounds table which has bound_level_entity_id as NULL and hence mmt
records for process orgs will not be selected in the extract
below.
----------------------
Do not commit data. Let the wrapper coordinate committing of data.
Paramters:
IN:
p_global_start_date - DBI global start date
History:
Date Author Action
01/14/04 Dinkar Gupta Defined procedure.
02/10/04 Dinkar Gupta Added group by transaction_id
in the mmt/mta join.
Used MMT subinventory_code
to handle serial item issues.
02/18/04 Dinkar Gupta Added condition to handle
cases where MMT quantity is 0 for
serial items.
Also added condition to report miss
for adjustments against any other
sub than the one being counted.
03/10/04 Dinkar Gupta Used item_cost from MCCE for system
inventory value for LIFO/FIFO org
issue.
06/22/05 Vishal Gupta Refer to R12 Changes in the above
header.
*/
PROCEDURE extract_discrete_adj_init (p_global_start_date IN DATE)
IS
l_proc_name CONSTANT VARCHAR2 (40) := 'extract_adjustments_init';
INSERT /*+ append parallel (opi_dbi_inv_cca_stg) */
INTO opi_dbi_inv_cca_stg (
organization_id,
inventory_item_id,
cycle_count_header_id,
abc_class_id,
subinventory_code,
cycle_count_entry_id,
source,
approval_date,
uom_code,
system_inventory_qty,
positive_adjustment_qty,
negative_adjustment_qty,
item_unit_cost,
item_adj_unit_cost,
hit_miss_pos,
hit_miss_neg,
exact_match)
SELECT /*+ parallel(mcce) parallel(mcch) parallel(mcci)
parallel(mccc) parallel(msi) parallel(subs)
parallel(mmt_mta)
use_hash(mcce mcch mcci mccc msi subs) */
mcce.organization_id,
mcce.inventory_item_id,
mcce.cycle_count_header_id,
to_char (mccc.abc_class_id),
mmt_mta.subinventory_code,
to_char (mcce.cycle_count_entry_id),
C_OPI_SOURCE,
trunc (mcce.approval_date),
msi.primary_uom_code,
decode (mcce.count_uom_current,
msi.primary_uom_code,
decode (mmt_mta.subinventory_code,
mcce.subinventory, mcce.system_quantity_current,
-1 * sum (mmt_mta.primary_quantity)),
decode (mmt_mta.subinventory_code,
mcce.subinventory,
inv_convert.inv_um_convert
(mcce.inventory_item_id, C_MMT_PRECISION,
mcce.system_quantity_current,
mcce.count_uom_current,
msi.primary_uom_code, NULL, NULL),
inv_convert.inv_um_convert
(mcce.inventory_item_id, C_MMT_PRECISION,
-1 * sum (mmt_mta.primary_quantity),
mcce.count_uom_current,
msi.primary_uom_code, NULL, NULL))),
CASE WHEN sum (mmt_mta.primary_quantity) > 0 THEN
sum (mmt_mta.primary_quantity)
ELSE 0
END,
CASE WHEN sum (mmt_mta.primary_quantity) < 0 THEN
-1 * sum (mmt_mta.primary_quantity)
ELSE 0
END,
decode (subs.asset_inventory,
C_EXPENSE_SUBINVENTORY, 0,
mcce.item_unit_cost),
decode (sum (mmt_mta.primary_quantity),
0, decode (subs.asset_inventory,
C_EXPENSE_SUBINVENTORY, 0,
mcce.item_unit_cost),
nvl (sum (mmt_mta.base_transaction_value)/
sum (mmt_mta.primary_quantity), 0)),
decode (mmt_mta.subinventory_code,
mcce.subinventory, nvl (mccc.hit_miss_tolerance_positive,
mcch.hit_miss_tolerance_positive),
0),
decode (mmt_mta.subinventory_code,
mcce.subinventory, nvl (mccc.hit_miss_tolerance_negative,
mcch.hit_miss_tolerance_negative),
0),
decode (sum (mmt_mta.primary_quantity),
0, C_EXACT_MATCH,
C_NO_MATCH)
FROM mtl_cycle_count_entries mcce,
mtl_cycle_count_headers mcch,
mtl_cycle_count_items mcci,
mtl_cycle_count_classes mccc,
mtl_system_items_b msi,
mtl_secondary_inventories subs,
(SELECT /*+ no_merge
parallel(mmt) parallel(mta) parallel(log)
use_hash(mmt mta log)*/
mmt.organization_id,
mmt.inventory_item_id,
mmt.transaction_date,
mmt.cycle_count_id,
decode (mmt.transaction_type_id,
5, decode(sign(nvl(mta.primary_quantity,0)),
1, 0,
mmt.primary_quantity),
mmt.primary_quantity) primary_quantity,
decode (mmt.transaction_type_id,
5, decode(sign(nvl(mta.primary_quantity,0)),
1, mmt.transfer_subinventory,
mmt.subinventory_code),
mmt.subinventory_code) subinventory_code,
sum (mta.base_transaction_value) base_transaction_value
FROM opi_dbi_mmt_cca_stg mmt,
mtl_transaction_accounts mta,
opi_dbi_conc_prog_run_log log
WHERE log.etl_type = C_ETL_TYPE
AND log.driving_table_code = C_LOG_MMT_DRV_TBL
AND log.load_type = C_LOAD_INIT
AND mmt.organization_id = log.bound_level_entity_id
AND mmt.transaction_id >= log.from_bound_id
AND mmt.transaction_id < log.to_bound_id
AND mmt.transaction_date >= p_global_start_date
-- AND mmt.transaction_type_id = C_MMT_CYCLE_COUNT_ADJ
AND mmt.transaction_id = mta.transaction_id (+)
AND nvl (mta.accounting_line_type, C_INVENTORY_ACCOUNT) =
C_INVENTORY_ACCOUNT -- 1
GROUP BY
mmt.organization_id,
mmt.inventory_item_id,
mmt.transaction_date,
mmt.cycle_count_id,
decode (mmt.transaction_type_id,
5, decode(sign(nvl(mta.primary_quantity,0)),
1, 0,
mmt.primary_quantity),
mmt.primary_quantity),
decode (mmt.transaction_type_id,
5, decode(sign(nvl(mta.primary_quantity,0)),
1, mmt.transfer_subinventory,
mmt.subinventory_code),
mmt.subinventory_code),
mmt.transaction_id) mmt_mta
WHERE mmt_mta.organization_id = msi.organization_id
AND mmt_mta.inventory_item_id = msi.inventory_item_id
AND mmt_mta.cycle_count_id = mcce.cycle_count_entry_id
AND mcce.entry_status_code = C_COMPLETED_CCA_ENTRY
AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
AND mcce.cycle_count_header_id = mcci.cycle_count_header_id
AND mcce.inventory_item_id = mcci.inventory_item_id
AND mcce.organization_id = mccc.organization_id
AND mcce.cycle_count_header_id = mccc.cycle_count_header_id
AND mcci.abc_class_id = mccc.abc_class_id
AND mmt_mta.organization_id = subs.organization_id
AND mmt_mta.subinventory_code = subs.secondary_inventory_name
GROUP BY
mcce.organization_id,
mcce.inventory_item_id,
mcce.cycle_count_header_id,
mccc.abc_class_id,
mmt_mta.subinventory_code,
mcce.cycle_count_entry_id,
trunc (mcce.approval_date),
msi.primary_uom_code,
mcce.count_uom_current,
mcce.system_quantity_current,
subs.asset_inventory,
mcce.item_unit_cost,
mcce.subinventory,
mccc.hit_miss_tolerance_positive,
mcch.hit_miss_tolerance_positive,
mcce.subinventory,
mccc.hit_miss_tolerance_negative,
mcch.hit_miss_tolerance_negative;
cost updates may cause the values recorded in MCCE to be different. Thus
we need to get the cost associated to the item using the value in GTV so
that the reported adjustments match the GTV reported value.
However, due to issues with layered costing in LIFO/FIFO orgs, the
system inventory value is computed using the cost at the time when
the entry was made. Thus the reported system inventory value
matches the one reported in Oracle Forms.
MCCE does not store quantities in the primary uom_code. If the
transaction uom is not the same as the primary uom, all
quantities will have to be converted to the primary uom.
Also, since the fact table is at a cycle count entry level,
it is impossible for the sum (mmt.primary_quantity) to be 0. Thus
the item_cost formula of sum (value)/sum (quantity) is always
valid and we need not worry about sum (quantity) being 0 or NULL.
Since the join conditions between MCCE - MMT - GTV is one to many
to many, join GTV/MMT on transaction_id first.
IMPORTANT NOTE R12:
-------------------
draft and permanent quantity/value is not handled separately as in
other ETLs as its assumed that either all the MMT-GTV records are
in draft or permanent status for one cycle count entries.
Now, as we relook at all the draft records in every ETL its
possible that same draft records are collected again. But as our
fact is at cycle_count_entry level its assumed that either all or
none of MMT-GTV for a cycle count entry are collected. Hence in
Merge of cycle count values are only replaced and not added.
Do not commit data. Let the wrapper coordinate committing of data.
Paramters:
IN:
p_global_start_date - DBI global start date
History:
Date Author Action
06/22/05 Vishal Gupta New Procedure. To collect data for
Post R12 Process cycle counting,
*/
PROCEDURE extract_process_adj_init (p_global_start_date IN DATE)
IS
l_proc_name CONSTANT VARCHAR2 (40) := 'extract_adjustments_init';
SELECT from_bound_date, to_bound_date
INTO l_from_bound_date, l_to_bound_date
FROM OPI_DBI_CONC_PROG_RUN_LOG
WHERE driving_table_code = C_LOG_GTV_DRV_TBL
AND etl_type = C_ETL_TYPE
AND load_type = C_LOAD_INIT;
INSERT /*+ append parallel (opi_dbi_inv_cca_stg) */
INTO opi_dbi_inv_cca_stg (
organization_id,
inventory_item_id,
cycle_count_header_id,
abc_class_id,
subinventory_code,
cycle_count_entry_id,
source,
approval_date,
uom_code,
system_inventory_qty,
positive_adjustment_qty,
negative_adjustment_qty,
item_unit_cost,
item_adj_unit_cost,
hit_miss_pos,
hit_miss_neg,
exact_match )
SELECT mcce.organization_id,
mcce.inventory_item_id,
mcce.cycle_count_header_id,
to_char (mccc.abc_class_id),
mmt_gtv.subinventory_code,
to_char (mcce.cycle_count_entry_id),
C_OPM_SOURCE,
trunc (mcce.approval_date),
msi.primary_uom_code,
decode (mcce.count_uom_current,
msi.primary_uom_code,
mcce.system_quantity_current,
inv_convert.inv_um_convert ( mcce.inventory_item_id,
C_MMT_PRECISION,
mcce.system_quantity_current,
mcce.count_uom_current,
msi.primary_uom_code,
NULL,
NULL)) system_inventory_qty,
CASE WHEN sum (mmt_gtv.primary_quantity) > 0 THEN
sum (mmt_gtv.primary_quantity)
ELSE 0
END positive_adjustment_qty,
CASE WHEN sum (mmt_gtv.primary_quantity) < 0 THEN
-1 * sum (mmt_gtv.primary_quantity)
ELSE 0
END negative_adjustment_qty ,
decode (subs.asset_inventory,
C_EXPENSE_SUBINVENTORY, 0,
mcce.item_unit_cost) item_unit_cost,
decode (sum (mmt_gtv.primary_quantity),
0, decode (subs.asset_inventory,
C_EXPENSE_SUBINVENTORY, 0,
mcce.item_unit_cost),
nvl (sum (mmt_gtv.transaction_base_value)/
sum (mmt_gtv.primary_quantity), 0)) item_adj_unit_cost,
decode (mmt_gtv.subinventory_code,
mcce.subinventory, nvl (mccc.hit_miss_tolerance_positive,
mcch.hit_miss_tolerance_positive),
0),
decode (mmt_gtv.subinventory_code,
mcce.subinventory, nvl (mccc.hit_miss_tolerance_negative,
mcch.hit_miss_tolerance_negative),
0),
decode (sum (mmt_gtv.primary_quantity),
0, C_EXACT_MATCH,
C_NO_MATCH) exact_match
FROM mtl_cycle_count_entries mcce,
mtl_cycle_count_headers mcch,
mtl_cycle_count_items mcci,
mtl_cycle_count_classes mccc,
mtl_system_items_b msi,
mtl_secondary_inventories subs,
-- below select is grouped at transaction_id, quantity level so that
-- quantity is summed correctly and then it can be joined with
-- mcce and other tables outside.
(SELECT mmt.organization_id,
mmt.transaction_id,
mmt.inventory_item_id,
mmt.cycle_count_id,
DECODE (mmt.transaction_type_id ,
5 , DECODE(GTV.subinventory_code,
MMT.subinventory_code, MMT.primary_quantity,
mmt.transfer_subinventory, -1* MMT.primary_quantity),mmt.primary_quantity) primary_quantity,
DECODE (mmt.transaction_type_id ,
5 ,GTV.subinventory_code,
mmt.subinventory_code) subinventory_code,
SUM (gtv.txn_base_value) transaction_base_value
FROM mtl_material_transactions mmt ,
(SELECT gt.*
FROM gmf_transaction_valuation gt,
opi_dbi_org_le_temp olt
WHERE olt.organization_id = gt.organization_id
AND olt.ledger_id = gt.ledger_id
AND olt.legal_entity_id = gt.legal_entity_id
AND olt.valuation_cost_type_id = gt.valuation_cost_type_id) gtv
WHERE gtv.event_class_code in ('MISC_TXN' , 'SUBINV_XFER')
AND gtv.event_type_code in ('CYCLE_COUNT_ADJ' , 'CYCLE_COUNT_XFER')
AND gtv.journal_line_type = 'INV'
AND gtv.transaction_source = 'INVENTORY'
AND gtv.transaction_date >= l_from_bound_date
-- for final posted records consider within the bounds
-- for draft posted records consider all the txns
AND DECODE( accounted_flag,
NULL, gtv.final_posting_date,
'D',s_global_start_date) < l_to_bound_date
and gtv.transaction_id = mmt.transaction_id
GROUP BY
mmt.organization_id,
mmt.inventory_item_id,
mmt.cycle_count_id,
DECODE (mmt.transaction_type_id ,
5 , DECODE(GTV.subinventory_code,
MMT.subinventory_code, MMT.primary_quantity,
mmt.transfer_subinventory, -1* MMT.primary_quantity),mmt.primary_quantity),
DECODE (mmt.transaction_type_id ,
5 ,GTV.subinventory_code,
mmt.subinventory_code) ,
mmt.transaction_id) mmt_gtv
WHERE mmt_gtv.organization_id = msi.organization_id
AND mmt_gtv.inventory_item_id = msi.inventory_item_id
AND mmt_gtv.cycle_count_id = mcce.cycle_count_entry_id
-- mmt records are created only after cycle count is approved
-- hence its not possible that entry_status_code is not approved.
-- it is ensured that all costed txns are collected
AND mcce.entry_status_code = C_COMPLETED_CCA_ENTRY -- 5
AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
AND mcce.cycle_count_header_id = mcci.cycle_count_header_id
AND mcce.inventory_item_id = mcci.inventory_item_id
AND mcce.organization_id = mccc.organization_id
AND mcce.cycle_count_header_id = mccc.cycle_count_header_id
AND mcci.abc_class_id = mccc.abc_class_id
AND mmt_gtv.organization_id = subs.organization_id
AND mmt_gtv.subinventory_code = subs.secondary_inventory_name
GROUP BY
mcce.organization_id,
mcce.inventory_item_id,
mcce.cycle_count_header_id,
mccc.abc_class_id,
mmt_gtv.subinventory_code,
mcce.cycle_count_entry_id,
trunc (mcce.approval_date),
msi.primary_uom_code,
mcce.count_uom_current,
mcce.system_quantity_current,
subs.asset_inventory,
mcce.item_unit_cost,
mcce.subinventory,
mccc.hit_miss_tolerance_positive,
mcch.hit_miss_tolerance_positive,
mcce.subinventory,
mccc.hit_miss_tolerance_negative,
mcch.hit_miss_tolerance_negative;
However, since we are dealing with last_update_dates, we need to
be sure that the dates in the log tables have timestamps so that
we do not collect partially collected days again.
The one special case is that we need to identify expense
subinventories and set the item cost to 0 for all entries
bearing those subinventory codes.
------------ R12 Changes
Since Process exact matches will be extrracted in the same insert.
Date bounds speific to organization based on first uncosted transaction
cannot be applied. Hence we will use date bounds that are being used for
the process adjustments. ie Collect all the exact matches for which
MCCE.approval_date (MCCE.last_update_date) is between global
start date and the initial program start date.
Discrete Adjustments are collected only upto first uncosted transaction
date. Hence exact matches and discrete cycle cout adjustments will be
out of sync if there are uncosted transactions. Please refer bug 4395280.
------------
Do not commit data. Let the wrapper coordinate committing of data.
Parameters:
None.
History:
Date Author Action
01/15/04 Dinkar Gupta Defined procedure.
02/05/04 Dinkar Gupta Added logic to join to the from and to
transaction dates in the log table.
06/22/05 Vishal Gupta Made changes on bounds.
*/
PROCEDURE extract_exact_matches_init
IS
l_proc_name CONSTANT VARCHAR2 (40) := 'extract_exact_matches_init';
SELECT from_bound_date, to_bound_date
INTO l_from_bound_date, l_to_bound_date
FROM OPI_DBI_CONC_PROG_RUN_LOG
WHERE driving_table_code = C_LOG_GTV_DRV_TBL
AND etl_type = C_ETL_TYPE
AND load_type = C_LOAD_INIT;
INSERT /*+ append parallel (opi_dbi_inv_cca_stg) */
INTO opi_dbi_inv_cca_stg (
organization_id,
inventory_item_id,
cycle_count_header_id,
abc_class_id,
subinventory_code,
cycle_count_entry_id,
source,
approval_date,
uom_code,
system_inventory_qty,
positive_adjustment_qty,
negative_adjustment_qty,
item_unit_cost,
item_adj_unit_cost,
hit_miss_pos,
hit_miss_neg,
exact_match)
SELECT /*+ parallel(mcce) parallel(mcci) parallel(mccc)
parallel(msi) parallel(subs) parallel(log)
use_hash(mcci mccc msi subs) */
mcce.organization_id,
mcce.inventory_item_id,
mcce.cycle_count_header_id,
to_char (mccc.abc_class_id),
mcce.subinventory,
to_char (mcce.cycle_count_entry_id),
decode(mp.process_enabled_flag,
'Y',C_OPM_SOURCE,
C_OPI_SOURCE ) source,
trunc (mcce.approval_date),
msi.primary_uom_code,
decode (mcce.count_uom_current,
msi.primary_uom_code, mcce.system_quantity_current,
inv_convert.inv_um_convert
(mcce.inventory_item_id, C_MMT_PRECISION,
mcce.system_quantity_current, mcce.count_uom_current,
msi.primary_uom_code, NULL, NULL)),
0,
0,
decode (subs.asset_inventory,
C_EXPENSE_SUBINVENTORY, 0,
mcce.item_unit_cost),
0,
NULL,
NULL,
C_EXACT_MATCH
FROM mtl_cycle_count_entries mcce,
mtl_cycle_count_items mcci,
mtl_cycle_count_classes mccc,
mtl_system_items_b msi,
mtl_secondary_inventories subs,
mtl_parameters mp
-- exact matches are approved at the time entry is completed
-- hence it is not possible that status of a cycle count entry
-- changes once the load is collected for that date range.
WHERE mcce.last_update_date >= l_from_bound_date
AND mcce.last_update_date < l_to_bound_date
AND mcce.entry_status_code = C_COMPLETED_CCA_ENTRY
AND mcce.organization_id = mp.organization_id
AND mcce.adjustment_date IS NULL
AND mcce.adjustment_quantity = 0
AND mcce.cycle_count_header_id = mcci.cycle_count_header_id
AND mcce.inventory_item_id = mcci.inventory_item_id
AND mcce.organization_id = mccc.organization_id
AND mcce.cycle_count_header_id = mccc.cycle_count_header_id
AND mcci.abc_class_id = mccc.abc_class_id
AND mcce.organization_id = msi.organization_id
AND mcce.inventory_item_id = msi.inventory_item_id
AND mcce.organization_id = subs.organization_id
AND mcce.subinventory = subs.secondary_inventory_name;
do not call the secondary currency API. Instead update the secondary
rates from the primary.
If the secondary currency has not been set up, set the conversion rate
to null.
If any primary conversion rates are missing, throw an exception.
If any secondary currency rates are missing (after the secondary
currency has been set up) throw an exception.
Need to commit data here due to insert+append.
History:
Date Author Action
01/15/04 Dinkar Gupta Defined procedure.
05/11/04 Dinkar Gupta Specializing this procedure for initial
load.
08/17/04 Dinkar Gupta Added secondary currency support
*/
PROCEDURE compute_cca_conv_rates_init (p_global_curr_code IN VARCHAR2,
p_global_rate_type IN VARCHAR2)
IS
l_proc_name CONSTANT VARCHAR2 (60) := 'compute_cca_conv_rates_init';
SELECT 1
FROM opi_dbi_inv_cca_conv
WHERE ( nvl (conversion_rate, -999) < 0
OR nvl (sec_conversion_rate, 999) < 0)
AND rownum < 2;
SELECT /*+ parallel (compare) */
DISTINCT
report_order,
curr_code,
rate_type,
approval_date,
func_currency_code
FROM (
SELECT /*+ parallel (conv) parallel (mp) parallel (to_conv) */
DISTINCT
s_global_curr_code curr_code,
s_global_rate_type rate_type,
1 report_order, -- ordering global currency first
mp.organization_code,
decode (conv.conversion_rate,
C_EURO_MISSING_AT_START, C_EURO_START_DATE,
conv.approval_date) approval_date,
conv.func_currency_code
FROM opi_dbi_inv_cca_conv conv,
mtl_parameters mp,
(SELECT /*+ parallel (opi_dbi_inv_cca_stg) */
DISTINCT organization_id, approval_date
FROM opi_dbi_inv_cca_stg) to_conv
WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
AND mp.organization_id = to_conv.organization_id
AND conv.approval_date (+) = to_conv.approval_date
AND conv.organization_id (+) = to_conv.organization_id
UNION ALL
SELECT /*+ parallel (conv) parallel (mp) parallel (to_conv) */
DISTINCT
s_secondary_curr_code curr_code,
s_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, C_EURO_START_DATE,
conv.approval_date) approval_date,
conv.func_currency_code
FROM opi_dbi_inv_cca_conv conv,
mtl_parameters mp,
(SELECT /*+ parallel (opi_dbi_inv_cca_stg) */
DISTINCT organization_id, approval_date
FROM opi_dbi_inv_cca_stg) to_conv
WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
AND mp.organization_id = to_conv.organization_id
AND conv.approval_date (+) = to_conv.approval_date
AND conv.organization_id (+) = to_conv.organization_id)
compare
ORDER BY
report_order ASC,
approval_date,
func_currency_code;
INSERT /*+ append parallel (opi_dbi_inv_cca_conv) */
INTO opi_dbi_inv_cca_conv (
organization_id,
func_currency_code,
approval_date,
conversion_rate,
sec_conversion_rate)
SELECT /*+ parallel (to_conv) parallel (curr_codes) */
to_conv.organization_id,
curr_codes.currency_code,
to_conv.approval_date,
decode (curr_codes.currency_code,
s_global_curr_code, 1,
fii_currency.get_global_rate_primary (
curr_codes.currency_code,
to_conv.approval_date) ),
decode (s_secondary_curr_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.approval_date)))
FROM
(SELECT /*+ parallel (opi_dbi_inv_cca_stg) */
DISTINCT organization_id, approval_date
FROM opi_dbi_inv_cca_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;
commit; -- due to insert+append
UPDATE /*+ parallel (opi_dbi_inv_cca_conv) */ opi_dbi_inv_cca_conv
SET sec_conversion_rate = conversion_rate;
INSERT /*+ append parallel(opi_dbi_inv_cca_f) */
INTO opi_dbi_inv_cca_f (
organization_id,
inventory_item_id,
cycle_count_header_id,
abc_class_id,
subinventory_code,
cycle_count_entry_id,
source,
approval_date,
uom_code,
system_inventory_qty,
system_inventory_val_b,
system_inventory_val_g,
system_inventory_val_sg,
positive_adjustment_qty,
positive_adjustment_val_b,
positive_adjustment_val_g,
positive_adjustment_val_sg,
negative_adjustment_qty,
negative_adjustment_val_b,
negative_adjustment_val_g,
negative_adjustment_val_sg,
conversion_rate,
sec_conversion_rate,
item_unit_cost,
hit_or_miss,
exact_match,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT /*+ parallel(stg) parallel (conv) use_hash (stg conv)*/
stg.organization_id,
stg.inventory_item_id,
stg.cycle_count_header_id,
stg.abc_class_id,
stg.subinventory_code,
stg.cycle_count_entry_id,
stg.source,
stg.approval_date,
stg.uom_code,
stg.system_inventory_qty,
stg.system_inventory_qty * stg.item_unit_cost,
stg.system_inventory_qty * stg.item_unit_cost *
conv.conversion_rate,
stg.system_inventory_qty * stg.item_unit_cost *
conv.sec_conversion_rate,
stg.positive_adjustment_qty,
stg.positive_adjustment_qty * stg.item_adj_unit_cost,
stg.positive_adjustment_qty * stg.item_adj_unit_cost *
conv.conversion_rate,
stg.positive_adjustment_qty * stg.item_adj_unit_cost *
conv.sec_conversion_rate,
stg.negative_adjustment_qty,
stg.negative_adjustment_qty * stg.item_adj_unit_cost,
stg.negative_adjustment_qty * stg.item_adj_unit_cost *
conv.conversion_rate,
stg.negative_adjustment_qty * stg.item_adj_unit_cost *
conv.sec_conversion_rate,
conv.conversion_rate,
conv.sec_conversion_rate,
stg.item_unit_cost,
CASE
WHEN stg.exact_match = C_EXACT_MATCH THEN
C_HIT
WHEN stg.hit_miss_pos IS NULL AND stg.hit_miss_neg IS NULL THEN
C_HIT
WHEN stg.hit_miss_pos IS NULL AND
stg.positive_adjustment_qty > 0 THEN
C_HIT
WHEN stg.hit_miss_neg IS NULL AND
stg.negative_adjustment_qty > 0 THEN
C_HIT
WHEN stg.system_inventory_qty = 0 THEN
CASE
WHEN stg.positive_adjustment_qty = 0 AND
stg.negative_adjustment_qty = 0 THEN
C_HIT
ELSE
C_MISS
END
WHEN stg.positive_adjustment_qty > 0 AND
(stg.positive_adjustment_qty/
abs (stg.system_inventory_qty)) <=
(stg.hit_miss_pos/100) THEN
C_HIT
WHEN stg.negative_adjustment_qty > 0 AND
(stg.negative_adjustment_qty/
abs (stg.system_inventory_qty)) <=
(stg.hit_miss_neg/100) THEN
C_HIT
ELSE
C_MISS
END,
stg.exact_match,
sysdate,
s_user_id,
s_login_id,
sysdate,
s_user_id,
s_request_id,
s_program_application_id,
s_program_id,
sysdate
FROM opi_dbi_inv_cca_stg stg,
opi_dbi_inv_cca_conv conv
WHERE stg.organization_id = conv.organization_id
AND stg.approval_date = conv.approval_date;
BIS_COLLECTION_UTILITIES.PUT_LINE ('Inserting data into the fact table ...');
WHEN BOUNDS_UPDATE_ERROR THEN
rollback;
(BOUNDS_UPDATE_ERROR_MESG,
l_proc_name, l_stmt_id));
interim cost updates may cause the values recorded in MCCE to
be different. Thus we need to get the cost associated to the item
using the value in MTA so that the reported adjustments match
the MTA reported value.
However, due to issues with layered costing in LIFO/FIFO orgs, the
system inventory value is computed using the cost at the time when
the entry was made. Thus the reported system inventory value
matches the one reported in Oracle Forms.
We are only interested in entries completed after the global start
date (i.e. entry_status_code = 5 and approval_date > global start date).
The only MTA rows we are interested in are those that affect the
inventory account (accounting line type = 1).
MTA does not store rows for expense items, or items in expense
subinventories. Thus the join between MMT and MTA will have to
be an outer join. Rows with no matches will be assigned an
item cost of 0.
MCCE does not store quantities in the primary uom_code. If the
transaction uom is not the same as the primary uom, all
quantities will have to be converted to the primary uom.
There is a need to check for adjustment quantity = 0 serial item counts
can have count level matches, but serial number level adjustments,
To handle this, make four enhancements:
1. Declare exact matches if the sum of the MMT primary quantity is 0.
2. Pick the MCCE item_unit_cost if the quantity is 0 (check for expense
subs before that).
3. Set the tolerance to 0 if there is an adjustment in another
subinventory that the one counted. This will automatically
make any adjustments against it misses.
4. The system_inventory_qty for the other subinventory is the negative
of the adjustment quantity, so that the sum of the adjustment
and system quantity is always 0.
We will only be scanning MMT rows between the transaction ranges
in the log table.
Also, since the fact table is at a cycle count entry level,
it is impossible for the sum (mmt.primary_quantity) to be 0. Thus
the item_cost formula of sum (value)/sum (quantity) is always
valid and we need not worry about sum (quantity) being 0 or NULL.
Since the join conditions between MCCE - MMT - MTA is one to many
to many, join MMT/MTA on transaction_id first.
Also, to handle serial item adjustments where the adjustment
automatically issues an item from a sub to move it to the next,
use the subinventory_code from MMT, not MCCE.
-------- R12 Changes
1> Subinventory Transfer type of transaction (MMT.transaction_type_id = 5)
is seen in Cycle Count. This case occurs when a LPN is received in
say BULK, and cycle count is done in say CASE for a specific LPN.
This type of transaction has 2 rows in MMT, one for issue transaction
and other for receipt transaction. But the MTA has no rows for receipt
transaction while it has 2 entries differing only in MTA.primary_quantity.
Can be done through Warehouse mobile.
Currently for such transactions MMT.cycle_count_id is not populated.
Bug # 4392340 has been logged for the same.
2> Changes to log table and columns.
----------------------
Do not commit data. Let the wrapper coordinate committing of data.
Paramters:
IN:
p_global_start_date - DBI global start date
History:
Date Author Action
01/19/04 Dinkar Gupta Defined procedure.
02/10/04 Dinkar Gupta Added group by transaction_id
in the mmt/mta join.
Used MMT subinventory_code
to handle serial item issues.
02/18/04 Dinkar Gupta Added condition to handle
cases where MMT quantity is 0 for
serial items.
Also added condition to report miss
for adjustments against any other
sub than the one being counted.
03/10/04 Dinkar Gupta Used item_cost from MCCE for system
inventory value for LIFO/FIFO org
issue.
06/22/05 Vishal Gupta Refer to R12 Changes in the above
header.
*/
PROCEDURE extract_discrete_adj_incr (p_global_start_date IN DATE)
IS
l_proc_name CONSTANT VARCHAR2 (40) := 'extract_adjustments_init';
INSERT
INTO opi_dbi_inv_cca_stg (
organization_id,
inventory_item_id,
cycle_count_header_id,
abc_class_id,
subinventory_code,
cycle_count_entry_id,
source,
approval_date,
uom_code,
system_inventory_qty,
positive_adjustment_qty,
negative_adjustment_qty,
item_unit_cost,
item_adj_unit_cost,
hit_miss_pos,
hit_miss_neg,
exact_match)
SELECT
mcce.organization_id,
mcce.inventory_item_id,
mcce.cycle_count_header_id,
to_char (mccc.abc_class_id),
mmt_mta.subinventory_code,
to_char (mcce.cycle_count_entry_id),
C_OPI_SOURCE,
trunc (mcce.approval_date),
msi.primary_uom_code,
decode (mcce.count_uom_current,
msi.primary_uom_code,
decode (mmt_mta.subinventory_code,
mcce.subinventory, mcce.system_quantity_current,
-1 * sum (mmt_mta.primary_quantity)),
decode (mmt_mta.subinventory_code,
mcce.subinventory,
inv_convert.inv_um_convert
(mcce.inventory_item_id, C_MMT_PRECISION,
mcce.system_quantity_current,
mcce.count_uom_current,
msi.primary_uom_code, NULL, NULL),
inv_convert.inv_um_convert
(mcce.inventory_item_id, C_MMT_PRECISION,
-1 * sum (mmt_mta.primary_quantity),
mcce.count_uom_current,
msi.primary_uom_code, NULL, NULL))),
CASE WHEN sum (mmt_mta.primary_quantity) > 0 THEN
sum (mmt_mta.primary_quantity)
ELSE 0
END,
CASE WHEN sum (mmt_mta.primary_quantity) < 0 THEN
-1 * sum (mmt_mta.primary_quantity)
ELSE 0
END,
decode (subs.asset_inventory,
C_EXPENSE_SUBINVENTORY, 0,
mcce.item_unit_cost),
decode (sum (mmt_mta.primary_quantity),
0, decode (subs.asset_inventory,
C_EXPENSE_SUBINVENTORY, 0,
mcce.item_unit_cost),
nvl (sum (mmt_mta.base_transaction_value)/
sum (mmt_mta.primary_quantity), 0)),
decode (mmt_mta.subinventory_code,
mcce.subinventory, nvl (mccc.hit_miss_tolerance_positive,
mcch.hit_miss_tolerance_positive),
0),
decode (mmt_mta.subinventory_code,
mcce.subinventory, nvl (mccc.hit_miss_tolerance_negative,
mcch.hit_miss_tolerance_negative),
0),
decode (sum (mmt_mta.primary_quantity),
0, C_EXACT_MATCH,
C_NO_MATCH)
FROM mtl_cycle_count_entries mcce,
mtl_cycle_count_headers mcch,
mtl_cycle_count_items mcci,
mtl_cycle_count_classes mccc,
mtl_system_items_b msi,
mtl_secondary_inventories subs,
(SELECT
mmt.organization_id,
mmt.inventory_item_id,
mmt.transaction_date,
mmt.cycle_count_id,
decode (mmt.transaction_type_id,
5, decode(sign(nvl(mta.primary_quantity,0)),
1, 0,
mmt.primary_quantity),
mmt.primary_quantity) primary_quantity,
decode (mmt.transaction_type_id,
5, decode(sign(nvl(mta.primary_quantity,0)),
1, mmt.transfer_subinventory,
mmt.subinventory_code),
mmt.subinventory_code) subinventory_code,
sum (mta.base_transaction_value) base_transaction_value
FROM mtl_material_transactions mmt,
mtl_transaction_accounts mta,
opi_dbi_conc_prog_run_log log
WHERE log.etl_type = C_ETL_TYPE
AND log.driving_table_code = C_LOG_MMT_DRV_TBL
AND log.load_type = C_LOAD_INCR
AND mmt.organization_id = log.bound_level_entity_id
AND mmt.transaction_id >= log.from_bound_id
AND mmt.transaction_id < log.to_bound_id
AND mmt.transaction_date > p_global_start_date
AND mmt.transaction_source_type_id = 9
AND mmt.transaction_type_id in ( C_MMT_CYCLE_COUNT_ADJ, 5)
--AND mmt.transaction_type_id = C_MMT_CYCLE_COUNT_ADJ
AND mmt.transaction_id = mta.transaction_id (+)
AND nvl (mta.accounting_line_type, C_INVENTORY_ACCOUNT) =
C_INVENTORY_ACCOUNT
GROUP BY
mmt.organization_id,
mmt.inventory_item_id,
mmt.transaction_date,
mmt.cycle_count_id,
decode (mmt.transaction_type_id,
5, decode(sign(nvl(mta.primary_quantity,0)),
1, 0,
mmt.primary_quantity),
mmt.primary_quantity),
decode (mmt.transaction_type_id,
5, decode(sign(nvl(mta.primary_quantity,0)),
1, mmt.transfer_subinventory,
mmt.subinventory_code),
mmt.subinventory_code),
mmt.transaction_id) mmt_mta
WHERE mmt_mta.organization_id = msi.organization_id
AND mmt_mta.inventory_item_id = msi.inventory_item_id
AND mmt_mta.cycle_count_id = mcce.cycle_count_entry_id
AND mcce.entry_status_code = C_COMPLETED_CCA_ENTRY
AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
AND mcce.cycle_count_header_id = mcci.cycle_count_header_id
AND mcce.inventory_item_id = mcci.inventory_item_id
AND mcce.organization_id = mccc.organization_id
AND mcce.cycle_count_header_id = mccc.cycle_count_header_id
AND mcci.abc_class_id = mccc.abc_class_id
AND mmt_mta.organization_id = subs.organization_id
AND mmt_mta.subinventory_code = subs.secondary_inventory_name
GROUP BY
mcce.organization_id,
mcce.inventory_item_id,
mcce.cycle_count_header_id,
mccc.abc_class_id,
mmt_mta.subinventory_code,
mcce.cycle_count_entry_id,
trunc (mcce.approval_date),
msi.primary_uom_code,
mcce.count_uom_current,
mcce.system_quantity_current,
subs.asset_inventory,
mcce.item_unit_cost,
mcce.subinventory,
mccc.hit_miss_tolerance_positive,
mcch.hit_miss_tolerance_positive,
mcce.subinventory,
mccc.hit_miss_tolerance_negative,
mcch.hit_miss_tolerance_negative;
Adjustments can get costed well after approval and interim cost updates may
cause the values recorded in MCCE to be different. Thus we need to get the
cost associated to the item using the value in GTV so that the reported
adjustments match the GTV reported value.
However, due to issues with layered costing in LIFO/FIFO orgs, the
system inventory value is computed using the cost at the time when
the entry was made. Thus the reported system inventory value
matches the one reported in Oracle Forms.
MCCE does not store quantities in the primary uom_code. If the
transaction uom is not the same as the primary uom, all
quantities will have to be converted to the primary uom.
Also, since the fact table is at a cycle count entry level,
it is impossible for the sum (mmt.primary_quantity) to be 0. Thus
the item_cost formula of sum (value)/sum (quantity) is always
valid and we need not worry about sum (quantity) being 0 or NULL.
Since the join conditions between MCCE - MMT - GTV is one to many
to many, join GTV/MMT on transaction_id first.
Do not commit data. Let the wrapper coordinate committing of data.
Paramters:
IN:
p_global_start_date - DBI global start date
History:
Date Author Action
06/22/05 Vishal Gupta New Procedure. To collect data for
Post R12 Process cycle counting,
*/
PROCEDURE extract_process_adj_incr (p_global_start_date IN DATE)
IS
l_proc_name CONSTANT VARCHAR2 (40) := 'extract_adjustments_init';
SELECT from_bound_date, to_bound_date
INTO l_from_bound_date, l_to_bound_date
FROM OPI_DBI_CONC_PROG_RUN_LOG
WHERE driving_table_code = C_LOG_GTV_DRV_TBL
AND etl_type = C_ETL_TYPE
AND load_type = C_LOAD_INCR;
INSERT
INTO opi_dbi_inv_cca_stg (
organization_id,
inventory_item_id,
cycle_count_header_id,
abc_class_id,
subinventory_code,
cycle_count_entry_id,
source,
approval_date,
uom_code,
system_inventory_qty,
positive_adjustment_qty,
negative_adjustment_qty,
item_unit_cost,
item_adj_unit_cost,
hit_miss_pos,
hit_miss_neg,
exact_match )
SELECT mcce.organization_id,
mcce.inventory_item_id,
mcce.cycle_count_header_id,
to_char (mccc.abc_class_id),
mmt_gtv.subinventory_code,
to_char (mcce.cycle_count_entry_id),
C_OPM_SOURCE,
trunc (mcce.approval_date),
msi.primary_uom_code,
decode (mcce.count_uom_current,
msi.primary_uom_code,
mcce.system_quantity_current,
inv_convert.inv_um_convert ( mcce.inventory_item_id,
C_MMT_PRECISION,
mcce.system_quantity_current,
mcce.count_uom_current,
msi.primary_uom_code,
NULL,
NULL)) system_inventory_qty,
CASE WHEN sum (mmt_gtv.primary_quantity) > 0 THEN
sum (mmt_gtv.primary_quantity)
ELSE 0
END positive_adjustment_qty,
CASE WHEN sum (mmt_gtv.primary_quantity) < 0 THEN
-1 * sum (mmt_gtv.primary_quantity)
ELSE 0
END negative_adjustment_qty ,
decode (subs.asset_inventory,
C_EXPENSE_SUBINVENTORY, 0,
mcce.item_unit_cost) item_unit_cost,
decode (sum (mmt_gtv.primary_quantity),
0, decode (subs.asset_inventory,
C_EXPENSE_SUBINVENTORY, 0,
mcce.item_unit_cost),
nvl (sum (mmt_gtv.transaction_base_value)/
sum (mmt_gtv.primary_quantity), 0)) item_adj_unit_cost,
decode (mmt_gtv.subinventory_code,
mcce.subinventory, nvl (mccc.hit_miss_tolerance_positive,
mcch.hit_miss_tolerance_positive),
0),
decode (mmt_gtv.subinventory_code,
mcce.subinventory, nvl (mccc.hit_miss_tolerance_negative,
mcch.hit_miss_tolerance_negative),
0),
decode (sum (mmt_gtv.primary_quantity),
0, C_EXACT_MATCH,
C_NO_MATCH) exact_match
FROM mtl_cycle_count_entries mcce,
mtl_cycle_count_headers mcch,
mtl_cycle_count_items mcci,
mtl_cycle_count_classes mccc,
mtl_system_items_b msi,
mtl_secondary_inventories subs,
-- as MMT to GTV is one to many relation grouping is done
-- by transaction_id and primary_quantity level.
(SELECT mmt.organization_id,
mmt.transaction_id,
mmt.inventory_item_id,
mmt.cycle_count_id,
DECODE (mmt.transaction_type_id ,
5 , DECODE(GTV.subinventory_code,
MMT.subinventory_code, MMT.primary_quantity,
mmt.transfer_subinventory, -1* MMT.primary_quantity),mmt.primary_quantity) primary_quantity,
DECODE (mmt.transaction_type_id ,
5 ,GTV.subinventory_code,
mmt.subinventory_code) subinventory_code,
SUM (gtv.txn_base_value) transaction_base_value
FROM mtl_material_transactions mmt ,
(SELECT gt.*
FROM gmf_transaction_valuation gt,
opi_dbi_org_le_temp olt
WHERE olt.organization_id = gt.organization_id
AND olt.ledger_id = gt.ledger_id
AND olt.legal_entity_id = gt.legal_entity_id
AND olt.valuation_cost_type_id = gt.valuation_cost_type_id) gtv
WHERE gtv.event_class_code in ( 'MISC_TXN','SUBINV_XFER')
AND gtv.event_type_code in ('CYCLE_COUNT_ADJ','CYCLE_COUNT_XFER')
AND gtv.journal_line_type = 'INV'
AND gtv.transaction_source = 'INVENTORY'
AND gtv.transaction_date >= l_from_bound_date
-- all draft rows are considered in every incremental run
AND DECODE( accounted_flag,
NULL, gtv.final_posting_date,
'D',s_global_start_date) < l_to_bound_date
and gtv.transaction_id = mmt.transaction_id
GROUP BY
mmt.organization_id,
mmt.inventory_item_id,
mmt.cycle_count_id,
DECODE (mmt.transaction_type_id ,
5 , DECODE(GTV.subinventory_code,
MMT.subinventory_code, MMT.primary_quantity,
mmt.transfer_subinventory, -1* MMT.primary_quantity),mmt.primary_quantity),
DECODE (mmt.transaction_type_id ,
5 ,GTV.subinventory_code,
mmt.subinventory_code) ,
mmt.transaction_id) mmt_gtv
WHERE mmt_gtv.organization_id = msi.organization_id
AND mmt_gtv.inventory_item_id = msi.inventory_item_id
AND mmt_gtv.cycle_count_id = mcce.cycle_count_entry_id
AND mcce.entry_status_code = C_COMPLETED_CCA_ENTRY -- 5
AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
AND mcce.cycle_count_header_id = mcci.cycle_count_header_id
AND mcce.inventory_item_id = mcci.inventory_item_id
AND mcce.organization_id = mccc.organization_id
AND mcce.cycle_count_header_id = mccc.cycle_count_header_id
AND mcci.abc_class_id = mccc.abc_class_id
AND mmt_gtv.organization_id = subs.organization_id
AND mmt_gtv.subinventory_code = subs.secondary_inventory_name
GROUP BY
mcce.organization_id,
mcce.inventory_item_id,
mcce.cycle_count_header_id,
mccc.abc_class_id,
mmt_gtv.subinventory_code,
mcce.cycle_count_entry_id,
trunc (mcce.approval_date),
msi.primary_uom_code,
mcce.count_uom_current,
mcce.system_quantity_current,
subs.asset_inventory,
mcce.item_unit_cost,
mcce.subinventory,
mccc.hit_miss_tolerance_positive,
mcch.hit_miss_tolerance_positive,
mcce.subinventory,
mccc.hit_miss_tolerance_negative,
mcch.hit_miss_tolerance_negative;
However, since we are dealing with last_update_dates, we need to
be sure that the dates in the log tables have timestamps so that
we do not collect partially collected days again.
The one special case is that we need to identify expense
subinventories and set the item cost to 0 for all entries
bearing those subinventory codes.
------------ R12 Changes
Since Process exact matches will be extracted in the same insert.
Date bounds speific to organization based on first uncosted transaction
cannot be applied. Hence we will use date bounds as that for GTV.
ie Collect all the exact matches between global start date and the initial
program run date.
------------
Do not commit data. Let the wrapper coordinate committing of data.
Parameters:
None.
History:
Date Author Action
01/19/04 Dinkar Gupta Defined procedure.
02/05/04 Dinkar Gupta Added logic to join to the from and to
transaction dates in the log table.
06/22/05 Vishal Gupta Refer to R12 Changes in the above header.
*/
PROCEDURE extract_exact_matches_incr
IS
l_proc_name CONSTANT VARCHAR2 (40) := 'extract_exact_matches_incr';
SELECT from_bound_date, to_bound_date
INTO l_from_bound_date, l_to_bound_date
FROM OPI_DBI_CONC_PROG_RUN_LOG
WHERE driving_table_code = C_LOG_GTV_DRV_TBL
AND etl_type = C_ETL_TYPE
AND load_type = C_LOAD_INCR;
INSERT
INTO opi_dbi_inv_cca_stg (
organization_id,
inventory_item_id,
cycle_count_header_id,
abc_class_id,
subinventory_code,
cycle_count_entry_id,
source,
approval_date,
uom_code,
system_inventory_qty,
positive_adjustment_qty,
negative_adjustment_qty,
item_unit_cost,
item_adj_unit_cost,
hit_miss_pos,
hit_miss_neg,
exact_match)
SELECT
mcce.organization_id,
mcce.inventory_item_id,
mcce.cycle_count_header_id,
to_char (mccc.abc_class_id),
mcce.subinventory,
to_char (mcce.cycle_count_entry_id),
decode(mp.process_enabled_flag,
'Y',C_OPM_SOURCE,
C_OPI_SOURCE ) source,
trunc (mcce.approval_date),
msi.primary_uom_code,
decode (mcce.count_uom_current,
msi.primary_uom_code, mcce.system_quantity_current,
inv_convert.inv_um_convert
(mcce.inventory_item_id, C_MMT_PRECISION,
mcce.system_quantity_current, mcce.count_uom_current,
msi.primary_uom_code, NULL, NULL)),
0,
0,
decode (subs.asset_inventory,
C_EXPENSE_SUBINVENTORY, 0,
mcce.item_unit_cost),
0,
NULL,
NULL,
C_EXACT_MATCH
FROM mtl_cycle_count_entries mcce,
mtl_cycle_count_items mcci,
mtl_cycle_count_classes mccc,
mtl_system_items_b msi,
mtl_secondary_inventories subs,
mtl_parameters mp
WHERE mcce.last_update_date >= l_from_bound_date
AND mcce.last_update_date < l_to_bound_date
AND mcce.organization_id = mp.organization_id
AND mcce.entry_status_code = C_COMPLETED_CCA_ENTRY -- 5
AND mcce.adjustment_date IS NULL
AND mcce.adjustment_quantity = 0
AND mcce.cycle_count_header_id = mcci.cycle_count_header_id
AND mcce.inventory_item_id = mcci.inventory_item_id
AND mcce.organization_id = mccc.organization_id
AND mcce.cycle_count_header_id = mccc.cycle_count_header_id
AND mcci.abc_class_id = mccc.abc_class_id
AND mcce.organization_id = msi.organization_id
AND mcce.inventory_item_id = msi.inventory_item_id
AND mcce.organization_id = subs.organization_id
AND mcce.subinventory = subs.secondary_inventory_name;
do not call the secondary currency API. Instead update the secondary
rates from the primary.
If the secondary currency has not been set up, set the conversion rate
to null.
If any primary conversion rates are missing, throw an exception.
If any secondary currency rates are missing (after the secondary
currency has been set up) throw an exception.
Need to commit data here due to insert+append.
History:
Date Author Action
01/15/04 Dinkar Gupta Defined procedure.
05/11/04 Dinkar Gupta Specializing this procedure for incr.
load.
08/16/04 Dinkar Gupta Added secondary currency support.
*/
PROCEDURE compute_cca_conv_rates_incr (p_global_curr_code IN VARCHAR2,
p_global_rate_type IN VARCHAR2)
IS
l_proc_name CONSTANT VARCHAR2 (60) := 'compute_cca_conv_rates_incr';
SELECT 1
FROM opi_dbi_inv_cca_conv
WHERE ( nvl (conversion_rate, -999) < 0
OR nvl (sec_conversion_rate, 999) < 0)
AND rownum < 2;
SELECT DISTINCT
report_order,
curr_code,
rate_type,
approval_date,
func_currency_code
FROM (
SELECT DISTINCT
s_global_curr_code curr_code,
s_global_rate_type rate_type,
1 report_order, -- ordering global currency first
mp.organization_code,
decode (conv.conversion_rate,
C_EURO_MISSING_AT_START, C_EURO_START_DATE,
conv.approval_date) approval_date,
conv.func_currency_code
FROM opi_dbi_inv_cca_conv conv,
mtl_parameters mp,
(SELECT
DISTINCT organization_id, approval_date
FROM opi_dbi_inv_cca_stg) to_conv
WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
AND mp.organization_id = to_conv.organization_id
AND conv.approval_date (+) = to_conv.approval_date
AND conv.organization_id (+) = to_conv.organization_id
UNION ALL
SELECT DISTINCT
s_secondary_curr_code curr_code,
s_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, C_EURO_START_DATE,
conv.approval_date) approval_date,
conv.func_currency_code
FROM opi_dbi_inv_cca_conv conv,
mtl_parameters mp,
(SELECT
DISTINCT organization_id, approval_date
FROM opi_dbi_inv_cca_stg) to_conv
WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
AND mp.organization_id = to_conv.organization_id
AND conv.approval_date (+) = to_conv.approval_date
AND conv.organization_id (+) = to_conv.organization_id)
ORDER BY
report_order ASC,
approval_date,
func_currency_code;
INSERT /*+ append */
INTO opi_dbi_inv_cca_conv (
organization_id,
func_currency_code,
approval_date,
conversion_rate,
sec_conversion_rate)
SELECT
to_conv.organization_id,
curr_codes.currency_code,
to_conv.approval_date,
decode (curr_codes.currency_code,
s_global_curr_code, 1,
fii_currency.get_global_rate_primary (
curr_codes.currency_code,
to_conv.approval_date) ),
decode (s_secondary_curr_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.approval_date)))
FROM
(SELECT
DISTINCT organization_id, approval_date
FROM opi_dbi_inv_cca_stg) to_conv,
(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))
curr_codes
WHERE curr_codes.organization_id = to_conv.organization_id;
commit; -- due to insert+append
UPDATE /*+ parallel (opi_dbi_inv_cca_conv) */ opi_dbi_inv_cca_conv
SET sec_conversion_rate = conversion_rate;
merge. In case the merge condition needs to insert, there is no
complication. Insert as in the initial load.
In case the merge condition needs to update, the type of entry matters.
For exact matches, the entry can be simply updated.
For adjustments, the entry needs to be appended because of the case
where serial adjustments can create multiple MMT transactions. In that
case, it is possible for one of the transactions to be uncosted. In
next run, these will get picked up and need to be appended to the
previous values. The hit/miss calculations will have to be done
based on the new appended values too.
Do not commit anything in this step. Let the wrapper handle that.
History:
Date Author Action
01/19/04 Dinkar Gupta Defined procedure.
02/09/04 Dinkar Gupta Used absolute value of
system inventory quantity
to compute hit/miss.
03/10/04 Dinkar Gupta Used item_cost from MCCE for system
inventory value and cost from MTA
for adjustment value to account for
for LIFO/FIFO org layered costing.
08/17/04 Dinkar Gupta Added secondary currency support
*/
PROCEDURE populate_fact_incr
IS
l_proc_name CONSTANT VARCHAR2(40) := 'populate_fact_incr';
(SELECT
stg.organization_id,
stg.inventory_item_id,
stg.cycle_count_header_id,
stg.abc_class_id,
stg.subinventory_code,
stg.cycle_count_entry_id,
stg.source,
stg.approval_date,
stg.uom_code,
stg.system_inventory_qty,
(stg.system_inventory_qty * stg.item_unit_cost)
system_inventory_val_b,
(stg.system_inventory_qty * stg.item_unit_cost *
conv.conversion_rate) system_inventory_val_g,
(stg.system_inventory_qty * stg.item_unit_cost *
conv.sec_conversion_rate) system_inventory_val_sg,
stg.positive_adjustment_qty,
(stg.positive_adjustment_qty * stg.item_adj_unit_cost)
positive_adjustment_val_b,
(stg.positive_adjustment_qty * stg.item_adj_unit_cost *
conv.conversion_rate) positive_adjustment_val_g,
(stg.positive_adjustment_qty * stg.item_adj_unit_cost *
conv.sec_conversion_rate) positive_adjustment_val_sg,
stg.negative_adjustment_qty,
(stg.negative_adjustment_qty * stg.item_adj_unit_cost)
negative_adjustment_val_b,
(stg.negative_adjustment_qty * stg.item_adj_unit_cost *
conv.conversion_rate) negative_adjustment_val_g,
(stg.negative_adjustment_qty * stg.item_adj_unit_cost *
conv.sec_conversion_rate) negative_adjustment_val_sg,
conv.conversion_rate,
conv.sec_conversion_rate,
stg.item_unit_cost,
CASE
WHEN stg.exact_match = C_EXACT_MATCH THEN
C_HIT
WHEN stg.hit_miss_pos IS NULL AND stg.hit_miss_neg IS NULL THEN
C_HIT
WHEN stg.hit_miss_pos IS NULL AND
stg.positive_adjustment_qty > 0 THEN
C_HIT
WHEN stg.hit_miss_neg IS NULL AND
stg.negative_adjustment_qty > 0 THEN
C_HIT
WHEN stg.system_inventory_qty = 0 THEN
CASE
WHEN stg.positive_adjustment_qty = 0 AND
stg.negative_adjustment_qty = 0 THEN
C_HIT
ELSE
C_MISS
END
WHEN stg.positive_adjustment_qty > 0 AND
(stg.positive_adjustment_qty/
abs (stg.system_inventory_qty)) <=
(stg.hit_miss_pos/100) THEN
C_HIT
WHEN stg.negative_adjustment_qty > 0 AND
(stg.negative_adjustment_qty/
abs (stg.system_inventory_qty)) <=
(stg.hit_miss_neg/100) THEN
C_HIT
ELSE
C_MISS
END hit_or_miss,
stg.exact_match,
stg.hit_miss_pos,
stg.hit_miss_neg,
sysdate last_update_date,
s_user_id last_updated_by,
s_login_id last_update_login,
sysdate creation_date,
s_user_id created_by,
s_request_id request_id,
s_program_application_id program_application_id,
s_program_id program_id,
sysdate program_update_date
FROM opi_dbi_inv_cca_stg stg,
opi_dbi_inv_cca_conv conv
WHERE stg.organization_id = conv.organization_id
AND stg.approval_date = conv.approval_date) new
ON (
base.cycle_count_entry_id = new.cycle_count_entry_id
AND base.subinventory_code = new.subinventory_code
AND base.source = new.source)
WHEN MATCHED THEN UPDATE
SET
system_inventory_qty = new.system_inventory_qty,
system_inventory_val_b = new.system_inventory_val_b,
system_inventory_val_g = new.system_inventory_val_g,
system_inventory_val_sg = new.system_inventory_val_sg,
positive_adjustment_qty =
CASE WHEN new.exact_match = C_EXACT_MATCH OR
new.source = C_OPM_SOURCE THEN
new.positive_adjustment_qty
ELSE new.positive_adjustment_qty +
base.positive_adjustment_qty
END,
positive_adjustment_val_b =
CASE WHEN new.exact_match = C_EXACT_MATCH OR
new.source = C_OPM_SOURCE THEN
new.positive_adjustment_val_b
ELSE new.positive_adjustment_val_b +
base.positive_adjustment_val_b
END,
positive_adjustment_val_g =
CASE WHEN new.exact_match = C_EXACT_MATCH OR
new.source = C_OPM_SOURCE THEN
new.positive_adjustment_val_g
ELSE new.positive_adjustment_val_g +
base.positive_adjustment_val_g
END,
positive_adjustment_val_sg =
CASE WHEN new.exact_match = C_EXACT_MATCH OR
new.source = C_OPM_SOURCE THEN
new.positive_adjustment_val_sg
ELSE new.positive_adjustment_val_sg +
base.positive_adjustment_val_sg
END,
negative_adjustment_qty =
CASE WHEN new.exact_match = C_EXACT_MATCH OR
new.source = C_OPM_SOURCE THEN
new.negative_adjustment_qty
ELSE new.negative_adjustment_qty +
base.negative_adjustment_qty
END,
negative_adjustment_val_b =
CASE WHEN new.exact_match = C_EXACT_MATCH OR
new.source = C_OPM_SOURCE THEN
new.negative_adjustment_val_b
ELSE new.negative_adjustment_val_b +
base.negative_adjustment_val_b
END,
negative_adjustment_val_g =
CASE WHEN new.exact_match = C_EXACT_MATCH OR
new.source = C_OPM_SOURCE THEN
new.negative_adjustment_val_g
ELSE new.negative_adjustment_val_g +
base.negative_adjustment_val_g
END,
negative_adjustment_val_sg =
CASE WHEN new.exact_match = C_EXACT_MATCH OR
new.source = C_OPM_SOURCE THEN
new.negative_adjustment_val_sg
ELSE new.negative_adjustment_val_sg +
base.negative_adjustment_val_sg
END,
hit_or_miss =
CASE
WHEN new.source = C_OPM_SOURCE THEN
new.hit_or_miss
WHEN new.exact_match = C_EXACT_MATCH THEN
C_HIT
WHEN new.hit_miss_pos IS NULL AND new.hit_miss_neg IS NULL THEN
C_HIT
WHEN new.source = C_OPI_SOURCE THEN
CASE
WHEN new.hit_miss_pos IS NULL AND
base.positive_adjustment_qty +
new.positive_adjustment_qty > 0 THEN
C_HIT
WHEN new.hit_miss_neg IS NULL AND
base.negative_adjustment_qty +
new.negative_adjustment_qty > 0 THEN
C_HIT
WHEN new.system_inventory_qty +
base.system_inventory_qty = 0 THEN
CASE
WHEN new.positive_adjustment_qty +
base.positive_adjustment_qty = 0 AND
new.negative_adjustment_qty +
base.negative_adjustment_qty = 0 THEN
C_HIT
ELSE
C_MISS
END
WHEN new.positive_adjustment_qty > 0 AND
((new.positive_adjustment_qty +
base.positive_adjustment_qty)/
abs (new.system_inventory_qty)) <=
(new.hit_miss_pos/100) THEN
C_HIT
WHEN new.negative_adjustment_qty > 0 AND
((new.negative_adjustment_qty +
base.negative_adjustment_qty)/
abs (new.system_inventory_qty)) <=
(new.hit_miss_neg/100) THEN
C_HIT
ELSE
C_MISS
END
ELSE
C_MISS -- should never get here!!
END,
exact_match = new.exact_match,
last_update_date = new.last_update_date,
last_updated_by = new.last_updated_by,
last_update_login = new.last_update_login,
creation_date = new.creation_date,
created_by = new.created_by,
request_id = new.request_id,
program_application_id = new.program_application_id,
program_id = new.program_id,
program_update_date = new.program_update_date
WHEN NOT MATCHED THEN INSERT (
organization_id,
inventory_item_id,
cycle_count_header_id,
abc_class_id,
subinventory_code,
cycle_count_entry_id,
source,
approval_date,
uom_code,
system_inventory_qty,
system_inventory_val_b,
system_inventory_val_g,
system_inventory_val_sg,
positive_adjustment_qty,
positive_adjustment_val_b,
positive_adjustment_val_g,
positive_adjustment_val_sg,
negative_adjustment_qty,
negative_adjustment_val_b,
negative_adjustment_val_g,
negative_adjustment_val_sg,
conversion_rate,
sec_conversion_rate,
item_unit_cost,
hit_or_miss,
exact_match,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES (
new.organization_id,
new.inventory_item_id,
new.cycle_count_header_id,
new.abc_class_id,
new.subinventory_code,
new.cycle_count_entry_id,
new.source,
new.approval_date,
new.uom_code,
new.system_inventory_qty,
new.system_inventory_val_b,
new.system_inventory_val_g,
new.system_inventory_val_sg,
new.positive_adjustment_qty,
new.positive_adjustment_val_b,
new.positive_adjustment_val_g,
new.positive_adjustment_val_sg,
new.negative_adjustment_qty,
new.negative_adjustment_val_b,
new.negative_adjustment_val_g,
new.negative_adjustment_val_sg,
new.conversion_rate,
new.sec_conversion_rate,
new.item_unit_cost,
new.hit_or_miss,
new.exact_match,
new.last_update_date,
new.last_updated_by,
new.last_update_login,
new.creation_date,
new.created_by,
new.request_id,
new.program_application_id,
new.program_id,
new.program_update_date);