[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO cst_inv_cost_temp(
organization_id,
inventory_item_id,
cost_type_id,
cost_source,
inventory_asset_flag,
item_cost,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost,
overhead_cost
) VALUES (
p_rec.organization_id,
p_rec.inventory_item_id,
p_rec.cost_type_id,
p_rec.cost_source,
p_rec.inventory_asset_flag,
p_rec.item_cost,
p_rec.material_cost,
p_rec.material_overhead_cost,
p_rec.resource_cost,
p_rec.outside_processing_cost,
p_rec.overhead_cost);
SELECT cost_organization_id,
primary_cost_method
INTO l_cost_org_id,
l_primary_cost_method
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT default_cost_type_id
INTO l_def_cost_type_id
FROM cst_cost_types
WHERE cost_type_id = NVL(p_cost_type_id, l_primary_cost_method);
INSERT INTO cst_item_list_temp(
inventory_item_id,
category_id,
cost_type_id
)
SELECT MSI.inventory_item_id,
MIC.category_id,
CIC.cost_type_id
FROM mtl_item_categories MIC,
mtl_system_items_kfv MSI,
cst_item_costs CIC
-- cst_item_costs CIC1,
-- cst_item_costs CIC2
WHERE MIC.category_set_id = p_category_set_id
AND MIC.organization_id = p_organization_id
AND MSI.organization_id = p_organization_id
AND MSI.inventory_item_id = MIC.inventory_item_id
--{
-- AND MSI.concatenated_segments
-- BETWEEN NVL(p_item_from,MSI.concatenated_segments)
-- AND NVL(p_item_to,MSI.concatenated_segments)
-- AND CIC1.organization_id (+) = l_cost_org_id
-- AND CIC2.organization_id (+) = l_cost_org_id
-- AND CIC1.inventory_item_id (+) = MSI.inventory_item_id
-- AND CIC2.inventory_item_id (+) = MSI.inventory_item_id
-- AND CIC1.cost_type_id (+) = NVL(p_cost_type_id,l_primary_cost_method)
-- AND CIC2.cost_type_id (+) = l_def_cost_type_id
-- AND CIC.rowid = NVL(CIC1.rowid,CIC2.rowid)
AND CIC.organization_id = p_organization_id
AND CIC.inventory_item_id = MSI.inventory_item_id
AND CIC.cost_type_id = l_primary_cost_method
--}
AND CIC.inventory_asset_flag =
DECODE(p_expense_item,1,CIC.inventory_asset_flag,1)
AND NVL(CIC.item_cost,0) = DECODE(p_zero_cost_only,
1,0,
NVL(CIC.item_cost,0)
);
INSERT INTO cst_item_list_temp(
inventory_item_id,
category_id,
cost_type_id
)
SELECT MSI.inventory_item_id,
MIC.category_id,
CIC.cost_type_id
FROM mtl_item_categories MIC,
mtl_system_items_kfv MSI,
cst_item_costs CIC,
cst_item_costs CIC1,
cst_item_costs CIC2
WHERE MIC.category_set_id = p_category_set_id
AND MIC.organization_id = p_organization_id
AND MSI.organization_id = p_organization_id
AND MSI.inventory_item_id = MIC.inventory_item_id
AND MSI.concatenated_segments
BETWEEN NVL(p_item_from,MSI.concatenated_segments)
AND NVL(p_item_to,MSI.concatenated_segments)
AND CIC1.organization_id (+) = l_cost_org_id
AND CIC2.organization_id (+) = l_cost_org_id
AND CIC1.inventory_item_id (+) = MSI.inventory_item_id
AND CIC2.inventory_item_id (+) = MSI.inventory_item_id
AND CIC1.cost_type_id (+) = NVL(p_cost_type_id,l_primary_cost_method)
AND CIC2.cost_type_id (+) = l_def_cost_type_id
AND CIC.rowid = NVL(CIC1.rowid,CIC2.rowid)
AND CIC.inventory_asset_flag =
DECODE(p_expense_item,1,CIC.inventory_asset_flag,1)
AND NVL(CIC.item_cost,0) = DECODE(p_zero_cost_only,
1,0,
NVL(CIC.item_cost,0)
);
INSERT
INTO cst_item_list_temp(
inventory_item_id,
category_id,
cost_type_id
)
SELECT MSI.inventory_item_id,
MIC.category_id,
CIC.cost_type_id
FROM mtl_item_categories MIC,
mtl_categories_kfv MC,
mtl_system_items_kfv MSI,
cst_item_costs CIC
-- cst_item_costs CIC1,
-- cst_item_costs CIC2
WHERE MC.concatenated_segments
BETWEEN NVL(p_category_from,MC.concatenated_segments)
AND NVL(p_category_to,MC.concatenated_segments)
AND MC.structure_id = (SELECT structure_id FROM mtl_category_sets WHERE category_set_id = p_category_set_id)
AND MIC.category_id = MC.category_id
AND MIC.category_set_id = p_category_set_id
AND MIC.organization_id = p_organization_id
AND MSI.organization_id = p_organization_id
AND MSI.inventory_item_id = MIC.inventory_item_id
AND MSI.concatenated_segments
BETWEEN NVL(p_item_from,MSI.concatenated_segments)
AND NVL(p_item_to,MSI.concatenated_segments)
-- The join to CIC implies that the item is
-- MSI.costing_enabled
--{
-- AND CIC1.organization_id (+) = l_cost_org_id
-- AND CIC2.organization_id (+) = l_cost_org_id
-- AND CIC1.inventory_item_id (+) = MSI.inventory_item_id
-- AND CIC2.inventory_item_id (+) = MSI.inventory_item_id
-- AND CIC1.cost_type_id (+) = NVL(p_cost_type_id,l_primary_cost_method)
-- AND CIC2.cost_type_id (+) = l_def_cost_type_id
-- AND CIC.rowid = NVL(CIC1.rowid,CIC2.rowid)
AND CIC.organization_id = p_organization_id
AND CIC.inventory_item_id = MSI.inventory_item_id
AND CIC.cost_type_id = l_primary_cost_method
--}
AND CIC.inventory_asset_flag =
DECODE(p_expense_item,1,CIC.inventory_asset_flag,1)
AND NVL(CIC.item_cost,0) = DECODE(p_zero_cost_only,
1,0,
NVL(CIC.item_cost,0)
);
INSERT
INTO cst_item_list_temp(
inventory_item_id,
category_id,
cost_type_id
)
SELECT MSI.inventory_item_id,
MIC.category_id,
CIC.cost_type_id
FROM mtl_item_categories MIC,
mtl_categories_kfv MC,
mtl_system_items_kfv MSI,
cst_item_costs CIC,
cst_item_costs CIC1,
cst_item_costs CIC2
WHERE MC.concatenated_segments
BETWEEN NVL(p_category_from,MC.concatenated_segments)
AND NVL(p_category_to,MC.concatenated_segments)
AND MC.structure_id = (SELECT structure_id FROM mtl_category_sets WHERE category_set_id = p_category_set_id)
AND MIC.category_id = MC.category_id
AND MIC.category_set_id = p_category_set_id
AND MIC.organization_id = p_organization_id
AND MSI.organization_id = p_organization_id
AND MSI.inventory_item_id = MIC.inventory_item_id
AND MSI.concatenated_segments
BETWEEN NVL(p_item_from,MSI.concatenated_segments)
AND NVL(p_item_to,MSI.concatenated_segments)
-- The join to CIC implies that the item is
-- MSI.costing_enabled
AND CIC1.organization_id (+) = l_cost_org_id
AND CIC2.organization_id (+) = l_cost_org_id
AND CIC1.inventory_item_id (+) = MSI.inventory_item_id
AND CIC2.inventory_item_id (+) = MSI.inventory_item_id
AND CIC1.cost_type_id (+) = NVL(p_cost_type_id,l_primary_cost_method)
AND CIC2.cost_type_id (+) = l_def_cost_type_id
AND CIC.rowid = NVL(CIC1.rowid,CIC2.rowid)
AND CIC.inventory_asset_flag =
DECODE(p_expense_item,1,CIC.inventory_asset_flag,1)
AND NVL(CIC.item_cost,0) = DECODE(p_zero_cost_only,
1,0,
NVL(CIC.item_cost,0)
);
p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
' items into CILT'||
' - include costing_enabled items only'
);
INSERT
INTO cst_item_list_temp(
inventory_item_id,
category_id,
cost_type_id
)
SELECT MSI.inventory_item_id,
MIC.category_id,
l_def_cost_type_id
FROM mtl_item_categories MIC,
mtl_categories_kfv MC,
mtl_system_items_kfv MSI
WHERE MC.concatenated_segments
BETWEEN NVL(p_category_from,MC.concatenated_segments)
AND NVL(p_category_to,MC.concatenated_segments)
AND MC.structure_id = (SELECT structure_id FROM mtl_category_sets WHERE category_set_id = p_category_set_id)
AND MIC.category_id = MC.category_id
AND MIC.category_set_id = p_category_set_id
AND MIC.organization_id = p_organization_id
AND MSI.organization_id = p_organization_id
AND MSI.inventory_item_id = MIC.inventory_item_id
AND MSI.concatenated_segments
BETWEEN NVL(p_item_from,MSI.concatenated_segments)
AND NVL(p_item_to,MSI.concatenated_segments);
p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
' items into CILT'||
' - include non-costing-enabled items'
);
INSERT
INTO cst_item_list_temp(
inventory_item_id,
category_id,
cost_type_id
)
VALUES
(
NULL,
NULL,
l_def_cost_type_id
);
p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
' items into CILT'||
' - for one-time items'
);
INSERT
INTO cst_cg_list_temp(
cost_group_id
)
SELECT CCG.cost_group_id
FROM cst_cost_groups CCG,
(SELECT organization_id,
cost_group_id
FROM cst_cost_group_accounts
UNION
/* This is required for cases where default
cost group id is 1 and it doesn't exist
in cst_cost_group_accounts */
SELECT organization_id,
default_cost_group_id cost_group_id
FROM mtl_parameters
/* Bug: 7705930
This is required for cases when the default
cost group id was changed from 1 but there
are transactions that belong to this common
cost group (1) which is not covered in the
above query
*/
UNION
SELECT p_organization_id,
1
FROM dual
) CCGA
WHERE CCGA.organization_id = p_organization_id
AND CCG.cost_group_id = CCGA.cost_group_id
AND NVL(CCG.disable_date, sysdate+1) > sysdate
AND CCG.cost_group
BETWEEN NVL(p_cost_group_from, CCG.cost_group)
AND NVL(p_cost_group_to, CCG.cost_group)
AND NOT EXISTS( SELECT 'Cost Group already exists'
FROM cst_cg_list_temp CGLT
where CGLT.cost_group_id = CCG.cost_group_id
);
p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
' cost groups from organization '||p_organization_id||
' into CCLT'
);
INSERT
INTO cst_cg_list_temp(
cost_group_id
)
(SELECT DISTINCT
CCG.cost_group_id
FROM cst_cost_groups CCG,
(SELECT organization_id,
cost_group_id
FROM cst_cost_group_accounts
UNION
/* This is required for cases where default
cost group id is 1 and it doesn't exist
in cst_cost_group_accounts */
SELECT organization_id,
default_cost_group_id cost_group_id
FROM mtl_parameters
/* Bug: 7705930
This is required for cases when the default
cost group id was changed from 1 but there
are transactions that belong to this common
cost group (1) which is not covered in the
above query
*/
UNION
SELECT p_organization_id,
1
FROM dual
) CCGA,
mtl_interorg_parameters MIP
WHERE CCG.cost_group_id = CCGA.cost_group_id
AND NVL(CCG.disable_date, sysdate+1) > sysdate
AND CCG.cost_group
BETWEEN NVL(p_cost_group_from, CCG.cost_group)
AND NVL(p_cost_group_to, CCG.cost_group)
AND ( ( MIP.from_organization_id = p_organization_id
AND MIP.to_organization_id = CCGA.organization_id)
OR
( MIP.to_organization_id = p_organization_id
AND MIP.from_organization_id = CCGA.organization_id)
)
MINUS
SELECT cost_group_id
FROM cst_cg_list_temp);
p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
' cost groups from other organizations '||
' into CCLT'
);
INSERT
INTO cst_sub_list_temp(
subinventory_code
)
SELECT SUB.secondary_inventory_name
FROM mtl_secondary_inventories SUB
WHERE SUB.organization_id = p_organization_id
AND SUB.asset_inventory = DECODE(p_expense_sub,1,SUB.asset_inventory,1)
-- Non-quantity tracked subinventories do not appear in MOQ.
AND SUB.quantity_tracked = 1
AND SUB.secondary_inventory_name
BETWEEN NVL(p_subinventory_from, SUB.secondary_inventory_name)
AND NVL(p_subinventory_to, SUB.secondary_inventory_name)
AND NOT EXISTS ( SELECT 'Subinventory Already Exists'
FROM cst_sub_list_temp CSLT
where CSLT.subinventory_code = SUB.secondary_inventory_name
);
p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
' subinventories into CSLT'
);
select primary_cost_method into l_cost_method
from mtl_parameters
where organization_id = p_organization_id;
INSERT
INTO cst_inv_qty_temp(
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
rollback_qty,
qty_source,
revision,
category_id,
cost_type_id
)
SELECT p_organization_id,
MOQ.cost_group_id,
MOQ.subinventory_code,
MOQ.inventory_item_id,
SUM(MOQ.transaction_quantity),
3, -- CURRENT_ONHAND
DECODE(p_qty_by_revision,1,moq.revision,NULL),
CILT.category_id,
CILT.cost_type_id
FROM mtl_onhand_quantities MOQ,
cst_item_list_temp CILT,
cst_cg_list_temp CCLT,
cst_sub_list_temp CSLT
WHERE MOQ.organization_id = p_organization_id
AND CILT.inventory_item_id = MOQ.inventory_item_id
AND CCLT.cost_group_id = MOQ.cost_group_id
AND CSLT.subinventory_code = MOQ.subinventory_code
AND MOQ.last_update_date <= NVL(CST_Inventory_PVT.g_run_onhand_date,MOQ.last_update_date)
GROUP
BY MOQ.cost_group_id,
MOQ.subinventory_code,
MOQ.inventory_item_id,
DECODE(p_qty_by_revision,1,moq.revision,NULL),
CILT.category_id,
CILT.cost_type_id;
INSERT
INTO cst_inv_qty_temp(
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
rollback_qty,
qty_source,
revision,
txn_source_type_id,
category_id,
cost_type_id
)
SELECT /*+ LEADING (MMT)*/
p_organization_id,
MMT.cost_group_id,
MMT.subinventory_code,
MMT.inventory_item_id,
-1*SUM(MMT.primary_quantity),
-- Sum is used to reduce the number of rows in CIQT
4, -- UNCOSTED_ONHAND
DECODE(p_qty_by_revision, 1, MMT.revision, NULL),
MMT.transaction_source_type_id,
CILT.category_id,
CILT.cost_type_id
FROM mtl_material_transactions MMT,
cst_item_list_temp CILT,
cst_cg_list_temp CCLT,
cst_sub_list_temp CSLT
WHERE MMT.organization_id = p_organization_id
AND CILT.inventory_item_id = MMT.inventory_item_id
AND CCLT.cost_group_id = MMT.cost_group_id
AND CSLT.subinventory_code = MMT.subinventory_code
AND MMT.costed_flag in ('N','E')
-- Ignore consigned transactions
AND MMT.organization_id =
NVL(MMT.owning_organization_id, MMT.organization_id)
AND NVL(MMT.owning_tp_type,2) = 2
-- Ignore logical transactions corresponding to drop shipments
-- and global procurement transactions
AND NVL(MMT.logical_transaction,-1) <> 1
-- Ignore WMS/OSFM transactions, cost updates including periodic cost
-- updates that do not affect onhand quantity
AND MMT.transaction_action_id NOT IN (24,40,41,50,51,52)
AND MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_onhand_date,MMT.creation_date)
GROUP
BY MMT.cost_group_id,
MMT.subinventory_code,
MMT.inventory_item_id,
DECODE(p_qty_by_revision, 1, MMT.revision, NULL),
MMT.transaction_source_type_id,
CILT.category_id,
CILT.cost_type_id;
INSERT
INTO cst_inv_qty_temp
( organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
rollback_qty,
qty_source,
rollback_value,
revision,
txn_source_type_id,
category_id,
cost_type_id
)
SELECT p_organization_id,
MMT.cost_group_id,
MMT.subinventory_code,
MMT.inventory_item_id,
-- There is a bug on Average Cost Update, where primary_quantity
-- is populated in addition to quantity_adjusted
SUM(-1*DECODE(MMT.transaction_action_id,24,0,MMT.primary_quantity)),
-- Sum is used to reduce the number of rows in CIQT
5, -- ROLLBACK_ONHAND
SUM(
DECODE(
MMT.transaction_action_id,
24, MMT.quantity_adjusted*(MMT.new_cost - MMT.prior_cost),
--MMT.primary_quantity*MMT.actual_cost - NVL(MMT.variance_amount,0) /*Bug 9161102 : FP of Bug 7355767*/
decode( l_cost_method, 1, MMT.primary_quantity*MMT.actual_cost,
MMT.primary_quantity*MMT.actual_cost - NVL(MMT.variance_amount,0)
)
)
),
-- Rollback value is used in the Transaction Value Historical
-- Summary - Average Costing report
DECODE(p_qty_by_revision, 1, MMT.revision, NULL),
MMT.transaction_source_type_id,
CILT.category_id,
CILT.cost_type_id
FROM mtl_material_transactions MMT,
cst_item_list_temp CILT
WHERE MMT.organization_id = p_organization_id
AND CILT.inventory_item_id = MMT.inventory_item_id
AND MMT.costed_flag IS NULL
AND MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_onhand_date,MMT.creation_date)
AND MMT.transaction_date > p_valuation_date
-- Ignore Consigned transactions
AND MMT.organization_id = NVL(MMT.owning_organization_id,
MMT.organization_id)
AND NVL(MMT.owning_tp_type,2) = 2
-- Ignore logical transactions corresponding to drop shipments
-- and global procurement transactions
AND NVL(MMT.logical_transaction,-1) <> 1
-- Ignore WMS and OSFM transactions that do not affect onhand
-- quantity and inventory valuation
AND MMT.transaction_action_id NOT IN (40,41,50,51,52)
-- Ignore periodic cost updates
AND MMT.transaction_source_type_id <> 14
-- The only transactions other than the ones ignored above that
-- affect inventory valuation and have null cost_group_id are
-- standard cost updates (non-PJM/WMS)
AND ( ( MMT.transaction_type_id = 24
AND MMT.cost_group_id IS NULL
)
OR EXISTS (
SELECT 1
FROM cst_cg_list_temp CCLT
WHERE CCLT.cost_group_id = MMT.cost_group_id)
)
-- The only transactions other than the ones ignored above that
-- affect inventory valuation and have null subinventory_code are
-- actual cost updates and std cost updates for PJM/WMS orgs
AND ( ( MMT.transaction_action_id = 24
AND MMT.subinventory_code IS NULL
)
OR EXISTS (
SELECT 1
FROM cst_sub_list_temp CSLT
WHERE CSLT.subinventory_code = MMT.subinventory_code)
)
GROUP
BY MMT.cost_group_id,
MMT.subinventory_code,
MMT.inventory_item_id,
DECODE(p_qty_by_revision, 1, MMT.revision, NULL),
MMT.transaction_source_type_id,
CILT.category_id,
CILT.cost_type_id;
INSERT
INTO cst_inv_qty_temp(
organization_id,
inventory_item_id,
rollback_qty,
qty_source,
category_id,
cost_group_id,
cost_type_id
)
SELECT p_organization_id,
TEMP.inventory_item_id,
0,
3, -- CURRENT_ONHAND
TEMP.category_id,
MP.default_cost_group_id,
TEMP.cost_type_id
FROM (
SELECT inventory_item_id,
category_id,
cost_type_id
FROM cst_item_list_temp
MINUS
SELECT DISTINCT
inventory_item_id,
category_id,
cost_type_id
FROM cst_inv_qty_temp
WHERE organization_id = p_organization_id
) TEMP,
mtl_parameters MP
WHERE MP.organization_id = p_organization_id;
SELECT /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */ 1
FROM mtl_material_transactions MMT
WHERE mmt.costed_flag IN ('N','E')
AND ( mmt.organization_id = p_organization_id
OR mmt.transfer_organization_id = p_organization_id)
/* Bug 9764385: Modified the query to include Logical Intransit transactions */
AND mmt.transaction_action_id IN (12,21,15,22)
AND ROWNUM <2;
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account,
shipment_line_id
)
SELECT 6,-- CURRENT_INTRANSIT
MS.intransit_owning_org_id,
ITEMS.inventory_item_id,
ITEMS.category_id,
MS.item_revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MS.from_organization_id,
MS.to_organization_id,
-- quantity is always expressed in the primary unit of measure
-- of the intransit owning organization
SUM(
DECODE(
MS.intransit_owning_org_id,
MS.from_organization_id,
inv_convert.inv_um_convert(
MS.item_id,NULL,MS.quantity,NULL,NULL,
MS.unit_of_measure,MSI_FROM.primary_unit_of_measure
),
MS.to_org_primary_quantity
)
),
NVL(
MMT.intransit_account,
NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
),
MS.shipment_line_id
FROM mtl_supply MS,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_parameters MP,
mtl_interorg_parameters MIP,
mtl_material_transactions MMT,
rcv_shipment_lines RSL,
mtl_system_items MSI_FROM
WHERE MS.to_organization_id = p_organization_id
/* AND MS.intransit_owning_org_id = p_organization_id */ /* Bug 5664736 */
AND MS.intransit_owning_org_id = DECODE(NVL(p_own,-1),1,p_organization_id,MS.intransit_owning_org_id)
AND MS.item_id = ITEMS.inventory_item_id
AND MS.supply_type_code IN ('SHIPMENT','RECEIVING')
AND MS.destination_type_code = 'INVENTORY'
AND NVL(MS.cost_group_id,MP.default_cost_group_id) = CGS.cost_group_id
AND MP.organization_id = MS.intransit_owning_org_id
AND RSL.shipment_line_id = MS.shipment_line_id
AND MMT.transaction_id (+) = RSL.mmt_transaction_id
AND MIP.from_organization_id (+) = MS.from_organization_id
AND MIP.to_organization_id (+) = MS.to_organization_id
AND MIP.fob_point (+) =
DECODE(
MS.intransit_owning_org_id,
MS.from_organization_id, 2,
MS.to_organization_id, 1
)
AND MSI_FROM.inventory_item_id = MS.item_id
AND MSI_FROM.organization_id = MS.from_organization_id
AND MS.last_update_date <= NVL(CST_Inventory_PVT.g_run_incmng_intransit_date,MS.last_update_date)
GROUP
BY MS.intransit_owning_org_id,
ITEMS.inventory_item_id,
ITEMS.category_id,
MS.item_revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MS.from_organization_id,
MS.to_organization_id,
NVL(
MMT.intransit_account,
NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
),
MS.shipment_line_id;
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account,
shipment_line_id
)
/* Bug 9764385: Modified the query to include Logical Intransit transactions */
SELECT 7, -- UNCOSTED_INTRANSIT
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,Decode(MMT.transaction_action_id, 21, MMT.transfer_organization_id, 15, MMT.organization_id),
2,MMT.organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
Decode(MMT.transaction_action_id, 21, MMT.organization_id, 15, MMT.transfer_organization_id),
Decode(MMT.transaction_action_id, 21, MMT.transfer_organization_id, 15, MMT.organization_id),
SUM(
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,
inv_convert.inv_um_convert(
MMT.inventory_item_id,NULL,Decode(MMT.transaction_action_id, 21, MMT.transaction_quantity, -1*MMT.transaction_quantity),
MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
),
2,
MMT.primary_quantity
)
),
NVL(MMT.intransit_account,MIP.intransit_inv_account),
RSL.shipment_line_id
FROM mtl_material_transactions MMT,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_interorg_parameters MIP,
mtl_system_items MSI_TO,
rcv_shipment_headers RSH,
rcv_shipment_lines RSL
WHERE Decode(MMT.transaction_action_id, 21, MMT.transfer_organization_id, 15, MMT.organization_id) = p_organization_id
AND MMT.transaction_action_id IN (21, 15)
AND MMT.costed_flag IN ('N','E')
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(MMT.transaction_action_id, 12, MMT.transfer_cost_group_id, 21, MMT.transfer_cost_group_id, 15, MMT.cost_group_id, 22, MMT.cost_group_id) =
CGS.cost_group_id
AND MIP.to_organization_id = Decode(MMT.transaction_action_id, 21, MMT.transfer_organization_id, 15, MMT.organization_id)
AND MIP.from_organization_id = Decode(MMT.transaction_action_id, 21, MMT.organization_id, 15, MMT.transfer_organization_id)
AND MSI_TO.organization_id = Decode(MMT.transaction_action_id, 21, MMT.transfer_organization_id, 15, MMT.organization_id)
AND MSI_TO.inventory_item_id = MMT.inventory_item_id
AND RSH.shipment_num = MMT.shipment_number
AND RSL.shipment_header_id = RSH.shipment_header_id
AND RSL.mmt_transaction_id = Decode(MMT.transaction_action_id, 21, MMT.transaction_id, 15, MMT.parent_transaction_id)
AND MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_incmng_intransit_date,MMT.creation_date)
GROUP
BY DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,Decode(MMT.transaction_action_id, 21, MMT.transfer_organization_id, 15, MMT.organization_id),
2,MMT.organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
Decode(MMT.transaction_action_id, 21, MMT.organization_id, 15, MMT.transfer_organization_id),
Decode(MMT.transaction_action_id, 21, MMT.transfer_organization_id, 15, MMT.organization_id),
NVL(MMT.intransit_account,MIP.intransit_inv_account),
RSL.shipment_line_id;
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account,
shipment_line_id
)
SELECT 7, -- UNCOSTED_INTRANSIT
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.organization_id,
2,MMT.transfer_organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.transfer_organization_id,
MMT.organization_id,
SUM(
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,
MMT.primary_quantity,
2,
inv_convert.inv_um_convert(
MMT.inventory_item_id,NULL,MMT.transaction_quantity,
MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
)
)
),
NVL(MMT.intransit_account, MIP.intransit_inv_account),
RT.shipment_line_id
FROM mtl_material_transactions MMT,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_interorg_parameters MIP,
mtl_system_items MSI_FROM,
rcv_transactions RT
WHERE MMT.organization_id = p_organization_id
AND MMT.transaction_action_id = 12
AND MMT.costed_flag IN ('N', 'E')
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(MMT.transaction_action_id, 12, MMT.transfer_cost_group_id, 21, MMT.transfer_cost_group_id, 15, MMT.cost_group_id, 22, MMT.cost_group_id) =
CGS.cost_group_id
AND MIP.to_organization_id = MMT.organization_id
AND MIP.from_organization_id = MMT.transfer_organization_id
AND MSI_FROM.organization_id = MMT.transfer_organization_id
AND MSI_FROM.inventory_item_id = MMT.inventory_item_id
AND RT.transaction_id = MMT.rcv_transaction_id
AND MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_incmng_intransit_date,MMT.creation_date)
GROUP
BY DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.organization_id,
2,MMT.transfer_organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.organization_id,
MMT.transfer_organization_id,
NVL(MMT.intransit_account, MIP.intransit_inv_account),
RT.shipment_line_id;
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account,
shipment_line_id
)
SELECT 8, -- ROLLBACK_INTRANSIT
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_organization_id,
2,MMT.organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.organization_id,
MMT.transfer_organization_id,
SUM(
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,
inv_convert.inv_um_convert(
MMT.inventory_item_id,NULL,MMT.transaction_quantity,
MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
),
2,
MMT.primary_quantity
)
),
NVL(MMT.intransit_account,MIP.intransit_inv_account),
RSL.shipment_line_id
FROM mtl_material_transactions MMT,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_interorg_parameters MIP,
mtl_system_items MSI_TO,
rcv_shipment_headers RSH,
rcv_shipment_lines RSL
WHERE MMT.transfer_organization_id = p_organization_id
AND MMT.transaction_action_id = 21
AND MMT.costed_flag IS NULL
/* Bug 9764385: Modified the query to include Logical Intransit transactions */
AND ( NVL(MMT.fob_point,MIP.fob_point) = 2
OR MMT.organization_id NOT IN (SELECT organization_id
FROM mtl_parameters
WHERE process_enabled_flag = 'Y')
OR
EXISTS (SELECT 1
FROM mtl_material_transactions
WHERE parent_transaction_id = mmt.transaction_id
AND transaction_action_id = 15
AND organization_id = p_organization_id
AND costed_flag IS NULL))
AND MMT.transaction_date > p_valuation_date
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(MMT.transaction_action_id, 12, MMT.transfer_cost_group_id, 21, MMT.transfer_cost_group_id, 15, MMT.cost_group_id, 22, MMT.cost_group_id) =
CGS.cost_group_id
AND MIP.to_organization_id = MMT.transfer_organization_id
AND MIP.from_organization_id = MMT.organization_id
AND MSI_TO.organization_id = MMT.transfer_organization_id
AND MSI_TO.inventory_item_id = MMT.inventory_item_id
AND RSH.shipment_num = MMT.shipment_number
AND RSL.shipment_header_id = RSH.shipment_header_id
AND RSL.mmt_transaction_id = MMT.transaction_id
AND MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_incmng_intransit_date,MMT.creation_date)
GROUP
BY DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_organization_id,
2,MMT.organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.organization_id,
MMT.transfer_organization_id,
NVL(MMT.intransit_account,MIP.intransit_inv_account),
RSL.shipment_line_id;
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account,
shipment_line_id
)
SELECT 8, -- ROLLBACK_INTRANSIT
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.organization_id,
2,MMT.transfer_organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.transfer_organization_id,
MMT.organization_id,
SUM(
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,
MMT.primary_quantity,
2,
inv_convert.inv_um_convert(
MMT.inventory_item_id,NULL,MMT.transaction_quantity,
MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
)
)
),
NVL(MMT.intransit_account, MIP.intransit_inv_account),
RT.shipment_line_id
FROM mtl_material_transactions MMT,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_interorg_parameters MIP,
mtl_system_items MSI_FROM,
rcv_transactions RT
WHERE MMT.organization_id = p_organization_id
AND MMT.transaction_action_id = 12
AND MMT.costed_flag IS NULL
AND MMT.transaction_date > p_valuation_date
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(MMT.transaction_action_id, 12, MMT.transfer_cost_group_id, 21, MMT.transfer_cost_group_id, 15, MMT.cost_group_id, 22, MMT.cost_group_id) =
CGS.cost_group_id
AND MIP.to_organization_id = MMT.organization_id
AND MIP.from_organization_id = MMT.transfer_organization_id
AND MSI_FROM.organization_id = MMT.transfer_organization_id
AND MSI_FROM.inventory_item_id = MMT.inventory_item_id
AND RT.transaction_id = MMT.rcv_transaction_id
AND MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_incmng_intransit_date,MMT.creation_date)
GROUP
BY DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.organization_id,
2,MMT.transfer_organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.organization_id,
MMT.transfer_organization_id,
NVL(MMT.intransit_account, MIP.intransit_inv_account),
RT.shipment_line_id;
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account,
shipment_line_id
)
SELECT 6,-- CURRENT_INTRANSIT
MS.intransit_owning_org_id,
ITEMS.inventory_item_id,
ITEMS.category_id,
MS.item_revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MS.from_organization_id,
MS.to_organization_id,
SUM(
DECODE(
MS.intransit_owning_org_id,
MS.from_organization_id,
inv_convert.inv_um_convert(
MS.item_id,NULL,MS.quantity,NULL,NULL,
MS.unit_of_measure,MSI_FROM.primary_unit_of_measure
),
MS.to_org_primary_quantity
)
),
NVL(
MMT.intransit_account,
NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
),
MS.shipment_line_id
FROM mtl_supply MS,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_parameters MP,
mtl_interorg_parameters MIP,
mtl_material_transactions MMT,
rcv_shipment_lines RSL,
mtl_system_items MSI_FROM
WHERE MS.from_organization_id = p_organization_id
/* AND MS.intransit_owning_org_id = p_organization_id */ /* Bug 5664736 */
AND MS.intransit_owning_org_id = DECODE(NVL(p_own,-1),1,p_organization_id,MS.intransit_owning_org_id)
AND MS.item_id = ITEMS.inventory_item_id
AND MS.supply_type_code IN ('SHIPMENT','RECEIVING')
AND MS.destination_type_code = 'INVENTORY'
AND NVL(MS.cost_group_id,MP.default_cost_group_id) = CGS.cost_group_id
AND MP.organization_id = MS.intransit_owning_org_id
AND RSL.shipment_line_id = MS.shipment_line_id
AND MMT.transaction_id (+) = RSL.mmt_transaction_id
AND MIP.from_organization_id (+) = MS.from_organization_id
AND MIP.to_organization_id (+) = MS.to_organization_id
AND MIP.fob_point (+) =
DECODE(
MS.intransit_owning_org_id,
MS.from_organization_id, 2,
MS.to_organization_id, 1)
AND MSI_FROM.inventory_item_id = MS.item_id
AND MSI_FROM.organization_id = MS.from_organization_id
AND MS.last_update_date <= NVL(CST_Inventory_PVT.g_run_outgng_intransit_date,MS.last_update_date)
GROUP
BY MS.intransit_owning_org_id,
ITEMS.inventory_item_id,
ITEMS.category_id,
MS.item_revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MS.from_organization_id,
MS.to_organization_id,
NVL(
MMT.intransit_account,
NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
),
MS.shipment_line_id;
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account,
shipment_line_id
)
SELECT 7, -- UNCOSTED_INTRANSIT
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_organization_id,
2,MMT.organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.organization_id,
MMT.transfer_organization_id,
SUM(
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,
inv_convert.inv_um_convert(
MMT.inventory_item_id,NULL,MMT.transaction_quantity,
MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
),
2,
MMT.primary_quantity
)
),
NVL(MMT.intransit_account,MIP.intransit_inv_account),
RSL.shipment_line_id
FROM mtl_material_transactions MMT,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_interorg_parameters MIP,
mtl_system_items MSI_TO,
rcv_shipment_headers RSH,
rcv_shipment_lines RSL
WHERE MMT.organization_id = p_organization_id
AND MMT.transaction_action_id = 21
AND MMT.costed_flag IN ('N','E')
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(MMT.transaction_action_id, 12, MMT.transfer_cost_group_id, 21, MMT.transfer_cost_group_id, 15, MMT.cost_group_id, 22, MMT.cost_group_id) =
CGS.cost_group_id
AND MIP.to_organization_id = MMT.transfer_organization_id
AND MIP.from_organization_id = MMT.organization_id
AND MSI_TO.organization_id = MMT.transfer_organization_id
AND MSI_TO.inventory_item_id = MMT.inventory_item_id
AND RSH.shipment_num = MMT.shipment_number
AND RSL.shipment_header_id = RSH.shipment_header_id
AND RSL.mmt_transaction_id = MMT.transaction_id
AND MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_outgng_intransit_date,MMT.creation_date)
GROUP
BY DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_organization_id,
2,MMT.organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.organization_id,
MMT.transfer_organization_id,
NVL(MMT.intransit_account,MIP.intransit_inv_account),
RSL.shipment_line_id;
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account,
shipment_line_id
)
/* Bug 9764385: Modified the query to include Logical Intransit transactions */
SELECT 7, -- UNCOSTED_INTRANSIT
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.organization_id,
2,Decode(MMT.transaction_action_id, 12, MMT.transfer_organization_id, 22, MMT.organization_id)
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
Decode(MMT.transaction_action_id, 12, MMT.transfer_organization_id, 22, MMT.organization_id),
Decode(MMT.transaction_action_id, 12, MMT.organization_id, 22, MMT.transfer_organization_id),
SUM( /* Bug 14502148, change -1*MMT.primary_quantity to MMT.primary_quantity for logical txn */
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,
Decode(MMT.transaction_action_id, 12, MMT.primary_quantity, MMT.primary_quantity),
2,
inv_convert.inv_um_convert(
MMT.inventory_item_id,NULL,Decode(MMT.transaction_action_id, 12, MMT.transaction_quantity, MMT.transaction_quantity),
MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
)
)
),
NVL(MMT.intransit_account, MIP.intransit_inv_account),
RT.shipment_line_id
FROM mtl_material_transactions MMT,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_interorg_parameters MIP,
mtl_system_items MSI_FROM,
rcv_transactions RT
WHERE Decode(MMT.transaction_action_id, 12, MMT.transfer_organization_id, 22, MMT.organization_id) = p_organization_id
AND MMT.transaction_action_id IN (12,22)
AND MMT.costed_flag IN ('N', 'E')
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(MMT.transaction_action_id, 12, MMT.transfer_cost_group_id, 21, MMT.transfer_cost_group_id, 15, MMT.cost_group_id, 22, MMT.cost_group_id) =
CGS.cost_group_id
AND MIP.to_organization_id = Decode(MMT.transaction_action_id, 12, MMT.organization_id, 22, MMT.transfer_organization_id)
AND MIP.from_organization_id = Decode(MMT.transaction_action_id, 12, MMT.transfer_organization_id, 22, MMT.organization_id)
AND MSI_FROM.organization_id = Decode(MMT.transaction_action_id, 12, MMT.transfer_organization_id, 22, MMT.organization_id)
AND MSI_FROM.inventory_item_id = MMT.inventory_item_id
AND RT.transaction_id = MMT.rcv_transaction_id
AND MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_outgng_intransit_date,MMT.creation_date)
GROUP
BY DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.organization_id,
2,Decode(MMT.transaction_action_id, 12, MMT.transfer_organization_id, 22, MMT.organization_id)
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
Decode(MMT.transaction_action_id, 12, MMT.organization_id, 22, MMT.transfer_organization_id),
Decode(MMT.transaction_action_id, 12, MMT.transfer_organization_id, 22, MMT.organization_id),
NVL(MMT.intransit_account, MIP.intransit_inv_account),
RT.shipment_line_id;
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account,
shipment_line_id
)
SELECT 8, -- ROLLBACK_INTRANSIT
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_organization_id,
2,MMT.organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.organization_id,
MMT.transfer_organization_id,
SUM(
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,
inv_convert.inv_um_convert(
MMT.inventory_item_id,NULL,MMT.transaction_quantity,
MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
),
2,
MMT.primary_quantity
)
),
NVL(MMT.intransit_account,MIP.intransit_inv_account),
RSL.shipment_line_id
FROM mtl_material_transactions MMT,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_interorg_parameters MIP,
mtl_system_items MSI_TO,
rcv_shipment_headers RSH,
rcv_shipment_lines RSL
WHERE MMT.organization_id = p_organization_id
AND MMT.transaction_action_id = 21
AND MMT.costed_flag IS NULL
AND MMT.transaction_date > p_valuation_date
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(MMT.transaction_action_id, 12, MMT.transfer_cost_group_id, 21, MMT.transfer_cost_group_id, 15, MMT.cost_group_id, 22, MMT.cost_group_id) =
CGS.cost_group_id
AND MIP.to_organization_id = MMT.transfer_organization_id
AND MIP.from_organization_id = MMT.organization_id
AND MSI_TO.organization_id = MMT.transfer_organization_id
AND MSI_TO.inventory_item_id = MMT.inventory_item_id
AND RSH.shipment_num = MMT.shipment_number
AND RSL.shipment_header_id = RSH.shipment_header_id
AND RSL.mmt_transaction_id = MMT.transaction_id
AND MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_outgng_intransit_date,MMT.creation_date)
GROUP
BY DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_organization_id,
2,MMT.organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.organization_id,
MMT.transfer_organization_id,
NVL(MMT.intransit_account,MIP.intransit_inv_account),
RSL.shipment_line_id;
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account,
shipment_line_id
)
SELECT 8, -- ROLLBACK_INTRANSIT
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.organization_id,
2,MMT.transfer_organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.transfer_organization_id,
MMT.organization_id,
SUM(
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,
MMT.primary_quantity,
2,
inv_convert.inv_um_convert(
MMT.inventory_item_id,NULL,MMT.transaction_quantity,
MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
)
)
),
NVL(MMT.intransit_account, MIP.intransit_inv_account),
RT.shipment_line_id
FROM mtl_material_transactions MMT,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_interorg_parameters MIP,
mtl_system_items MSI_FROM,
rcv_transactions RT
WHERE MMT.transfer_organization_id = p_organization_id
AND MMT.transaction_action_id = 12
AND MMT.costed_flag IS NULL
/* Bug 9764385: Modified the query to include Logical Intransit transactions */
AND ( NVL(MMT.fob_point,MIP.fob_point) = 1
/* Bug 14502148, only consider receiving organization is not OPM organization */
OR MMT.organization_id NOT IN (SELECT organization_id
FROM mtl_parameters
WHERE process_enabled_flag = 'Y')
OR
EXISTS (SELECT 1
FROM mtl_material_transactions
WHERE parent_transaction_id = mmt.transaction_id
AND transaction_action_id = 22
AND organization_id = mmt.transfer_organization_id
AND costed_flag IS NULL))
AND MMT.transaction_date > p_valuation_date
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(MMT.transaction_action_id, 12, MMT.transfer_cost_group_id, 21, MMT.transfer_cost_group_id, 15, MMT.cost_group_id, 22, MMT.cost_group_id) =
CGS.cost_group_id
AND MIP.to_organization_id = MMT.organization_id
AND MIP.from_organization_id = MMT.transfer_organization_id
AND MSI_FROM.organization_id = MMT.transfer_organization_id
AND MSI_FROM.inventory_item_id = MMT.inventory_item_id
AND RT.transaction_id = MMT.rcv_transaction_id
AND MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_outgng_intransit_date,MMT.creation_date)
GROUP
BY DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.organization_id,
2,MMT.transfer_organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.organization_id,
MMT.transfer_organization_id,
NVL(MMT.intransit_account, MIP.intransit_inv_account),
RT.shipment_line_id;
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account
)
SELECT 6,-- CURRENT_INTRANSIT
MS.intransit_owning_org_id,
ITEMS.inventory_item_id,
ITEMS.category_id,
MS.item_revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MS.from_organization_id,
MS.to_organization_id,
-- quantity is always expressed in the primary unit of measure
-- of the intransit owning organization
SUM(
DECODE(
MS.intransit_owning_org_id,
MS.from_organization_id,
inv_convert.inv_um_convert(
MS.item_id,NULL,MS.quantity,NULL,NULL,
MS.unit_of_measure,MSI_FROM.primary_unit_of_measure
),
MS.to_org_primary_quantity
)
),
NVL(
MMT.intransit_account,
NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
)
FROM mtl_supply MS,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_parameters MP,
mtl_interorg_parameters MIP,
mtl_material_transactions MMT,
rcv_shipment_lines RSL,
mtl_system_items MSI_FROM
WHERE MS.to_organization_id = p_organization_id
/* AND MS.intransit_owning_org_id = p_organization_id */ /* Bug 5664736 */
AND MS.intransit_owning_org_id = DECODE(NVL(p_own,-1),1,p_organization_id,MS.intransit_owning_org_id)
AND MS.item_id = ITEMS.inventory_item_id
AND MS.supply_type_code IN ('SHIPMENT','RECEIVING')
AND MS.destination_type_code = 'INVENTORY'
AND NVL(MS.cost_group_id,MP.default_cost_group_id) = CGS.cost_group_id
AND MP.organization_id = MS.intransit_owning_org_id
AND RSL.shipment_line_id = MS.shipment_line_id
AND MMT.transaction_id (+) = RSL.mmt_transaction_id
AND MIP.from_organization_id (+) = MS.from_organization_id
AND MIP.to_organization_id (+) = MS.to_organization_id
AND MIP.fob_point (+) =
DECODE(
MS.intransit_owning_org_id,
MS.from_organization_id, 2,
MS.to_organization_id, 1
)
AND MSI_FROM.inventory_item_id = MS.item_id
AND MSI_FROM.organization_id = MS.from_organization_id
AND MS.last_update_date <= NVL(CST_Inventory_PVT.g_run_incmng_intransit_date,MS.last_update_date)
GROUP
BY MS.intransit_owning_org_id,
ITEMS.inventory_item_id,
ITEMS.category_id,
MS.item_revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MS.from_organization_id,
MS.to_organization_id,
NVL(
MMT.intransit_account,
NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
);
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account
)
/* Bug 9764385: Modified the query to include Logical Intransit transactions */
SELECT 7, -- UNCOSTED_INTRANSIT
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,Decode(MMT.transaction_action_id, 21, MMT.transfer_organization_id, 15, MMT.organization_id),
2,MMT.organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
Decode(MMT.transaction_action_id, 21, MMT.organization_id, 15, MMT.transfer_organization_id),
Decode(MMT.transaction_action_id, 21, MMT.transfer_organization_id, 15, MMT.organization_id),
SUM(
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,
inv_convert.inv_um_convert(
MMT.inventory_item_id,NULL,Decode(MMT.transaction_action_id, 21, MMT.transaction_quantity, -1*MMT.transaction_quantity),
MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
),
2,
MMT.primary_quantity
)
),
NVL(MMT.intransit_account,MIP.intransit_inv_account)
FROM mtl_material_transactions MMT,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_interorg_parameters MIP,
mtl_system_items MSI_TO
WHERE Decode(MMT.transaction_action_id, 21, MMT.transfer_organization_id, 15, MMT.organization_id) = p_organization_id
AND MMT.transaction_action_id IN (21, 15)
AND MMT.costed_flag IN ('N','E')
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(MMT.transaction_action_id, 12, MMT.transfer_cost_group_id, 21, MMT.transfer_cost_group_id, 15, MMT.cost_group_id, 22, MMT.cost_group_id) =
CGS.cost_group_id
AND MIP.to_organization_id = Decode(MMT.transaction_action_id, 21, MMT.transfer_organization_id, 15, MMT.organization_id)
AND MIP.from_organization_id = Decode(MMT.transaction_action_id, 21, MMT.organization_id, 15, MMT.transfer_organization_id)
AND MSI_TO.organization_id = Decode(MMT.transaction_action_id, 21, MMT.transfer_organization_id, 15, MMT.organization_id)
AND MSI_TO.inventory_item_id = MMT.inventory_item_id
AND MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_incmng_intransit_date,MMT.creation_date)
GROUP
BY DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,Decode(MMT.transaction_action_id, 21, MMT.transfer_organization_id, 15, MMT.organization_id),
2,MMT.organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
Decode(MMT.transaction_action_id, 21, MMT.organization_id, 15, MMT.transfer_organization_id),
Decode(MMT.transaction_action_id, 21, MMT.transfer_organization_id, 15, MMT.organization_id),
NVL(MMT.intransit_account,MIP.intransit_inv_account);
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account
)
SELECT 7, -- UNCOSTED_INTRANSIT
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.organization_id,
2,MMT.transfer_organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.transfer_organization_id,
MMT.organization_id,
SUM(
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,
MMT.primary_quantity,
2,
inv_convert.inv_um_convert(
MMT.inventory_item_id,NULL,MMT.transaction_quantity,
MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
)
)
),
NVL(MMT.intransit_account, MIP.intransit_inv_account)
FROM mtl_material_transactions MMT,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_interorg_parameters MIP,
mtl_system_items MSI_FROM
WHERE MMT.organization_id = p_organization_id
AND MMT.transaction_action_id = 12
AND MMT.costed_flag IN ('N', 'E')
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(MMT.transaction_action_id, 12, MMT.transfer_cost_group_id, 21, MMT.transfer_cost_group_id, 15, MMT.cost_group_id, 22, MMT.cost_group_id) =
CGS.cost_group_id
AND MIP.to_organization_id = MMT.organization_id
AND MIP.from_organization_id = MMT.transfer_organization_id
AND MSI_FROM.organization_id = MMT.transfer_organization_id
AND MSI_FROM.inventory_item_id = MMT.inventory_item_id
AND MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_incmng_intransit_date,MMT.creation_date)
GROUP
BY DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.organization_id,
2,MMT.transfer_organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.organization_id,
MMT.transfer_organization_id,
NVL(MMT.intransit_account, MIP.intransit_inv_account);
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account
)
SELECT 8, -- ROLLBACK_INTRANSIT
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_organization_id,
2,MMT.organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.organization_id,
MMT.transfer_organization_id,
SUM(
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,
inv_convert.inv_um_convert(
MMT.inventory_item_id,NULL,MMT.transaction_quantity,
MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
),
2,
MMT.primary_quantity
)
),
NVL(MMT.intransit_account,MIP.intransit_inv_account)
FROM mtl_material_transactions MMT,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_interorg_parameters MIP,
mtl_system_items MSI_TO,
mtl_transaction_types MTT
WHERE MMT.transfer_organization_id = p_organization_id
AND MMT.transaction_action_id = 21
AND MMT.costed_flag IS NULL
/* Bug 9764385: Modified the query to include Logical Intransit transactions */
AND ( NVL(MMT.fob_point,MIP.fob_point) = 2
OR MMT.organization_id NOT IN (SELECT organization_id
FROM mtl_parameters
WHERE process_enabled_flag = 'Y')
OR
EXISTS (SELECT 1
FROM mtl_material_transactions
WHERE parent_transaction_id = mmt.transaction_id
AND transaction_action_id = 15
AND organization_id = p_organization_id
AND costed_flag IS NULL))
AND MMT.transaction_date > p_valuation_date
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(MMT.transaction_action_id, 12, MMT.transfer_cost_group_id, 21, MMT.transfer_cost_group_id, 15, MMT.cost_group_id, 22, MMT.cost_group_id) =
CGS.cost_group_id
AND MIP.to_organization_id = MMT.transfer_organization_id
AND MIP.from_organization_id = MMT.organization_id
AND MSI_TO.organization_id = MMT.transfer_organization_id
AND MSI_TO.inventory_item_id = MMT.inventory_item_id
AND MTT.transaction_action_id = MMT.transaction_action_id
AND MTT.transaction_source_type_id = MMT.transaction_source_type_id
AND MTT.transaction_type_id = MMT.transaction_type_id
AND MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_incmng_intransit_date,MMT.creation_date)
GROUP
BY DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_organization_id,
2,MMT.organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.organization_id,
MMT.transfer_organization_id,
NVL(MMT.intransit_account,MIP.intransit_inv_account);
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account
)
SELECT 8, -- ROLLBACK_INTRANSIT
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.organization_id,
2,MMT.transfer_organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.transfer_organization_id,
MMT.organization_id,
SUM(
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,
MMT.primary_quantity,
2,
inv_convert.inv_um_convert(
MMT.inventory_item_id,NULL,MMT.transaction_quantity,
MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
)
)
),
NVL(MMT.intransit_account, MIP.intransit_inv_account)
FROM mtl_material_transactions MMT,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_interorg_parameters MIP,
mtl_system_items MSI_FROM,
mtl_transaction_types MTT
WHERE MMT.organization_id = p_organization_id
AND MMT.transaction_action_id = 12
AND MMT.costed_flag IS NULL
AND MMT.transaction_date > p_valuation_date
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(MMT.transaction_action_id, 12, MMT.transfer_cost_group_id, 21, MMT.transfer_cost_group_id, 15, MMT.cost_group_id, 22, MMT.cost_group_id) =
CGS.cost_group_id
AND MIP.to_organization_id = MMT.organization_id
AND MIP.from_organization_id = MMT.transfer_organization_id
AND MSI_FROM.organization_id = MMT.transfer_organization_id
AND MSI_FROM.inventory_item_id = MMT.inventory_item_id
AND MTT.transaction_action_id = MMT.transaction_action_id
AND MTT.transaction_source_type_id = MMT.transaction_source_type_id
AND MTT.transaction_type_id = MMT.transaction_type_id
AND MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_incmng_intransit_date,MMT.creation_date)
GROUP
BY DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.organization_id,
2,MMT.transfer_organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.organization_id,
MMT.transfer_organization_id,
NVL(MMT.intransit_account, MIP.intransit_inv_account);
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account
)
SELECT 6,-- CURRENT_INTRANSIT
MS.intransit_owning_org_id,
ITEMS.inventory_item_id,
ITEMS.category_id,
MS.item_revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MS.from_organization_id,
MS.to_organization_id,
SUM(
DECODE(
MS.intransit_owning_org_id,
MS.from_organization_id,
inv_convert.inv_um_convert(
MS.item_id,NULL,MS.quantity,NULL,NULL,
MS.unit_of_measure,MSI_FROM.primary_unit_of_measure
),
MS.to_org_primary_quantity
)
),
NVL(
MMT.intransit_account,
NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
)
FROM mtl_supply MS,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_parameters MP,
mtl_interorg_parameters MIP,
mtl_material_transactions MMT,
rcv_shipment_lines RSL,
mtl_system_items MSI_FROM
WHERE MS.from_organization_id = p_organization_id
/* AND MS.intransit_owning_org_id = p_organization_id */ /* Bug 5664736 */
AND MS.intransit_owning_org_id = DECODE(NVL(p_own,-1),1,p_organization_id,MS.intransit_owning_org_id)
AND MS.item_id = ITEMS.inventory_item_id
AND MS.supply_type_code IN ('SHIPMENT','RECEIVING')
AND MS.destination_type_code = 'INVENTORY'
AND NVL(MS.cost_group_id,MP.default_cost_group_id) = CGS.cost_group_id
AND MP.organization_id = MS.intransit_owning_org_id
AND RSL.shipment_line_id = MS.shipment_line_id
AND MMT.transaction_id (+) = RSL.mmt_transaction_id
AND MIP.from_organization_id (+) = MS.from_organization_id
AND MIP.to_organization_id (+) = MS.to_organization_id
AND MIP.fob_point (+) =
DECODE(
MS.intransit_owning_org_id,
MS.from_organization_id, 2,
MS.to_organization_id, 1)
AND MSI_FROM.inventory_item_id = MS.item_id
AND MSI_FROM.organization_id = MS.from_organization_id
AND MS.last_update_date <= NVL(CST_Inventory_PVT.g_run_outgng_intransit_date,MS.last_update_date)
GROUP
BY MS.intransit_owning_org_id,
ITEMS.inventory_item_id,
ITEMS.category_id,
MS.item_revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MS.from_organization_id,
MS.to_organization_id,
NVL(
MMT.intransit_account,
NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
);
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account
)
SELECT 7, -- UNCOSTED_INTRANSIT
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_organization_id,
2,MMT.organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.organization_id,
MMT.transfer_organization_id,
SUM(
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,
inv_convert.inv_um_convert(
MMT.inventory_item_id,NULL,MMT.transaction_quantity,
MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
),
2,
MMT.primary_quantity
)
),
NVL(MMT.intransit_account,MIP.intransit_inv_account)
FROM mtl_material_transactions MMT,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_interorg_parameters MIP,
mtl_system_items MSI_TO
WHERE MMT.organization_id = p_organization_id
AND MMT.transaction_action_id = 21
AND MMT.costed_flag IN ('N','E')
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(MMT.transaction_action_id, 12, MMT.transfer_cost_group_id, 21, MMT.transfer_cost_group_id, 15, MMT.cost_group_id, 22, MMT.cost_group_id) =
CGS.cost_group_id
AND MIP.to_organization_id = MMT.transfer_organization_id
AND MIP.from_organization_id = MMT.organization_id
AND MSI_TO.organization_id = MMT.transfer_organization_id
AND MSI_TO.inventory_item_id = MMT.inventory_item_id
AND MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_outgng_intransit_date,MMT.creation_date)
GROUP
BY DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_organization_id,
2,MMT.organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.organization_id,
MMT.transfer_organization_id,
NVL(MMT.intransit_account,MIP.intransit_inv_account);
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account
)
/* Bug 9764385: Modified the query to include Logical Intransit transactions */
SELECT 7, -- UNCOSTED_INTRANSIT
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.organization_id,
2,Decode(MMT.transaction_action_id, 12, MMT.transfer_organization_id, 22, MMT.organization_id)
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
Decode(MMT.transaction_action_id, 12, MMT.transfer_organization_id, 22, MMT.organization_id),
Decode(MMT.transaction_action_id, 12, MMT.organization_id, 22, MMT.transfer_organization_id),
SUM( /* Bug 14502148, change -1*MMT.primary_quantity to MMT.primary_quantity for logical txn */
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,
Decode(MMT.transaction_action_id, 12, MMT.primary_quantity, MMT.primary_quantity),
2,
inv_convert.inv_um_convert(
MMT.inventory_item_id,NULL,Decode(MMT.transaction_action_id, 12, MMT.transaction_quantity, MMT.transaction_quantity),
MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
)
)
),
NVL(MMT.intransit_account, MIP.intransit_inv_account)
FROM mtl_material_transactions MMT,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_interorg_parameters MIP,
mtl_system_items MSI_FROM
WHERE Decode(MMT.transaction_action_id, 12, MMT.transfer_organization_id, 22, MMT.organization_id) = p_organization_id
AND MMT.transaction_action_id IN (12,22)
AND MMT.costed_flag IN ('N', 'E')
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(MMT.transaction_action_id, 12, MMT.transfer_cost_group_id, 21, MMT.transfer_cost_group_id, 15, MMT.cost_group_id, 22, MMT.cost_group_id) =
CGS.cost_group_id
AND MIP.to_organization_id = Decode(MMT.transaction_action_id, 12, MMT.organization_id, 22, MMT.transfer_organization_id)
AND MIP.from_organization_id = Decode(MMT.transaction_action_id, 12, MMT.transfer_organization_id, 22, MMT.organization_id)
AND MSI_FROM.organization_id = Decode(MMT.transaction_action_id, 12, MMT.transfer_organization_id, 22, MMT.organization_id)
AND MSI_FROM.inventory_item_id = MMT.inventory_item_id
AND MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_outgng_intransit_date,MMT.creation_date)
GROUP
BY DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.organization_id,
2,Decode(MMT.transaction_action_id, 12, MMT.transfer_organization_id, 22, MMT.organization_id)
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
Decode(MMT.transaction_action_id, 12, MMT.organization_id, 22, MMT.transfer_organization_id),
Decode(MMT.transaction_action_id, 12, MMT.transfer_organization_id, 22, MMT.organization_id),
NVL(MMT.intransit_account, MIP.intransit_inv_account);
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account
)
SELECT 8, -- ROLLBACK_INTRANSIT
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_organization_id,
2,MMT.organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.organization_id,
MMT.transfer_organization_id,
SUM(
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,
inv_convert.inv_um_convert(
MMT.inventory_item_id,NULL,MMT.transaction_quantity,
MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
),
2,
MMT.primary_quantity
)
),
NVL(MMT.intransit_account,MIP.intransit_inv_account)
FROM mtl_material_transactions MMT,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_interorg_parameters MIP,
mtl_system_items MSI_TO,
mtl_transaction_types MTT
WHERE MMT.organization_id = p_organization_id
AND MMT.transaction_action_id = 21
AND MMT.costed_flag IS NULL
AND MMT.transaction_date > p_valuation_date
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(MMT.transaction_action_id, 12, MMT.transfer_cost_group_id, 21, MMT.transfer_cost_group_id, 15, MMT.cost_group_id, 22, MMT.cost_group_id) =
CGS.cost_group_id
AND MIP.to_organization_id = MMT.transfer_organization_id
AND MIP.from_organization_id = MMT.organization_id
AND MSI_TO.organization_id = MMT.transfer_organization_id
AND MSI_TO.inventory_item_id = MMT.inventory_item_id
AND MTT.transaction_action_id = MMT.transaction_action_id
AND MTT.transaction_source_type_id = MMT.transaction_source_type_id
AND MTT.transaction_type_id = MMT.transaction_type_id
AND MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_outgng_intransit_date,MMT.creation_date)
GROUP
BY DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_organization_id,
2,MMT.organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.organization_id,
MMT.transfer_organization_id,
NVL(MMT.intransit_account,MIP.intransit_inv_account);
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
revision,
cost_type_id,
cost_group_id,
from_organization_id,
to_organization_id,
rollback_qty,
intransit_inv_account
)
SELECT 8, -- ROLLBACK_INTRANSIT
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.organization_id,
2,MMT.transfer_organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.transfer_organization_id,
MMT.organization_id,
SUM(
DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,
MMT.primary_quantity,
2,
inv_convert.inv_um_convert(
MMT.inventory_item_id,NULL,MMT.transaction_quantity,
MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
)
)
),
NVL(MMT.intransit_account, MIP.intransit_inv_account)
FROM mtl_material_transactions MMT,
cst_item_list_temp ITEMS,
cst_cg_list_temp CGS,
mtl_interorg_parameters MIP,
mtl_system_items MSI_FROM,
mtl_transaction_types MTT
WHERE MMT.transfer_organization_id = p_organization_id
AND MMT.transaction_action_id = 12
AND MMT.costed_flag IS NULL
/* Bug 9764385: Modified the query to include Logical Intransit transactions */
AND ( NVL(MMT.fob_point,MIP.fob_point) = 1
/* Bug 14502148, only consider receiving organization is not OPM organization */
OR MMT.organization_id NOT IN (SELECT organization_id
FROM mtl_parameters
WHERE process_enabled_flag = 'Y')
OR
EXISTS (SELECT 1
FROM mtl_material_transactions
WHERE parent_transaction_id = mmt.transaction_id
AND transaction_action_id = 22
AND organization_id = mmt.transfer_organization_id
AND costed_flag IS NULL))
AND MMT.transaction_date > p_valuation_date
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(MMT.transaction_action_id, 12, MMT.transfer_cost_group_id, 21, MMT.transfer_cost_group_id, 15, MMT.cost_group_id, 22, MMT.cost_group_id) =
CGS.cost_group_id
AND MIP.to_organization_id = MMT.organization_id
AND MIP.from_organization_id = MMT.transfer_organization_id
AND MSI_FROM.organization_id = MMT.transfer_organization_id
AND MSI_FROM.inventory_item_id = MMT.inventory_item_id
AND MTT.transaction_action_id = MMT.transaction_action_id
AND MTT.transaction_source_type_id = MMT.transaction_source_type_id
AND MTT.transaction_type_id = MMT.transaction_type_id
AND MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_outgng_intransit_date,MMT.creation_date)
GROUP
BY DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.organization_id,
2,MMT.transfer_organization_id
),
ITEMS.inventory_item_id,
ITEMS.category_id,
MMT.revision,
ITEMS.cost_type_id,
CGS.cost_group_id,
MMT.organization_id,
MMT.transfer_organization_id,
NVL(MMT.intransit_account, MIP.intransit_inv_account);
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
cost_type_id,
rcv_transaction_id,
revision,
rollback_qty
)
SELECT 9, -- RECEIVED
MS.to_organization_id,
CILT.inventory_item_id,
DECODE(MS.item_id, NULL, POL.category_id, CILT.category_id),
CILT.cost_type_id,
DECODE(RT.transaction_type,
'ACCEPT', Get_ParentReceiveTxn(MS.rcv_transaction_id),
'REJECT', Get_ParentReceiveTxn(MS.rcv_transaction_id),
'TRANSFER', Get_ParentReceiveTxn(MS.rcv_transaction_id),
(MS.rcv_transaction_id)),
-- MTL_SUPPLY stores parent Match/Receive except for Accept/Reject/Transfers
DECODE(p_qty_by_revision, 1, POL.item_revision, NULL),
SUM(MS.to_org_primary_quantity) -- sum across po distributions
FROM cst_item_list_temp CILT,
cst_cg_list_temp CCLT,
mtl_supply MS,
rcv_transactions RT,
mtl_parameters MP,
po_lines_all POL,
po_line_locations_all POLL,
pjm_project_parameters PPP
WHERE NVL(CILT.inventory_item_id, -1) = NVL(MS.item_id, -1)
AND MP.organization_id = MS.to_organization_id
AND MS.to_organization_id = p_organization_id
AND NVL(
MS.cost_group_id,
NVL(PPP.costing_group_id,MP.default_cost_group_id)
) = CCLT.cost_group_id
AND MS.supply_type_code = 'RECEIVING'
AND RT.transaction_id = MS.rcv_transaction_id
-- Joining to MS eliminates consigned and drop ship receipts
AND NVL(RT.consigned_flag, 'N') = 'N' -- eliminate consigned
AND RT.source_document_code = 'PO'
AND POL.po_line_id = RT.po_line_id
AND PPP.project_id (+) = POL.project_id
AND POLL.line_location_id = RT.po_line_location_id
AND POLL.shipment_type <> 'PREPAYMENT'
AND POLL.matching_basis = 'QUANTITY' -- eliminate service line types
AND POLL.accrue_on_receipt_flag = DECODE(p_include_period_end, 1, POLL.accrue_on_receipt_flag, 'Y')
AND RT.creation_date <= NVL(CST_Inventory_PVT.g_run_receiving_date,RT.creation_date)
GROUP
BY MS.to_organization_id,
CILT.inventory_item_id,
DECODE(MS.item_id, NULL, POL.category_id, CILT.category_id),
CILT.cost_type_id,
DECODE(RT.transaction_type,
'ACCEPT', Get_ParentReceiveTxn(MS.rcv_transaction_id),
'REJECT', Get_ParentReceiveTxn(MS.rcv_transaction_id),
'TRANSFER', Get_ParentReceiveTxn(MS.rcv_transaction_id),
(MS.rcv_transaction_id)),
DECODE(p_qty_by_revision, 1, POL.item_revision, NULL);
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
inventory_item_id,
category_id,
cost_type_id,
rcv_transaction_id,
revision,
rollback_qty
)
SELECT 10 qty_source, -- ROLLBACK RECEIVING
RT.organization_id,
CILT.inventory_item_id,
DECODE(POL.item_id, NULL, POL.category_id, CILT.category_id),
CILT.cost_type_id,
DECODE(RT.transaction_type,
'RECEIVE', RT.transaction_id,
'MATCH', RT.transaction_id,
Get_ParentReceiveTxn(RT.transaction_id)) rcv_transaction_id,
DECODE(p_qty_by_revision, 1, POL.item_revision, NULL),
SUM(DECODE(RT.transaction_type,
'RECEIVE', -1 * RT.primary_quantity,
'DELIVER', 1 * RT.primary_quantity,
'RETURN TO RECEIVING', -1 * RT.primary_quantity,
'RETURN TO VENDOR', DECODE(PARENT_RT.transaction_type,
'UNORDERED', 0,
1 * RT.primary_quantity),
'MATCH', -1 * RT.primary_quantity,
'CORRECT', DECODE(PARENT_RT.transaction_type,
'UNORDERED', 0,
'RECEIVE', -1 * RT.primary_quantity,
'DELIVER', 1 * RT.primary_quantity,
'RETURN TO RECEIVING', -1 * RT.primary_quantity,
'RETURN TO VENDOR', DECODE(GRPARENT_RT.transaction_type,
'UNORDERED', 0,
1 * RT.primary_quantity),
'MATCH', -1 * RT.primary_quantity,
0),
0)
) rollback_qty
FROM cst_item_list_temp CILT,
cst_cg_list_temp CCLT,
rcv_transactions RT,
rcv_transactions PARENT_RT,
rcv_transactions GRPARENT_RT,
mtl_parameters MP,
po_lines_all POL,
po_line_locations_all POLL,
pjm_project_parameters PPP
WHERE NVL(CILT.inventory_item_id, -1) = NVL(POL.item_id, -1)
AND MP.organization_id = RT.organization_id
AND RT.organization_id = p_organization_id
AND NVL(PPP.costing_group_id,MP.default_cost_group_id) = CCLT.cost_group_id
AND NVL(RT.consigned_flag, 'N') = 'N' -- eliminate consigned
AND NVL(RT.dropship_type_code, 3) = 3 -- eliminate drop ship
AND RT.transaction_date > p_valuation_date
AND RT.transaction_type in
('RECEIVE', 'DELIVER', 'RETURN TO RECEIVING', 'RETURN TO VENDOR', 'CORRECT', 'MATCH')
AND RT.source_document_code = 'PO'
AND DECODE(RT.parent_transaction_id,
-1, NULL,
0, NULL,
RT.parent_transaction_id) = PARENT_RT.transaction_id(+)
AND DECODE(PARENT_RT.parent_transaction_id,
-1, NULL,
0, NULL,
PARENT_RT.parent_transaction_id) = GRPARENT_RT.transaction_id(+)
AND POL.po_line_id = RT.po_line_id
AND PPP.project_id (+) = POL.project_id
AND POLL.line_location_id = RT.po_line_location_id
AND POLL.shipment_type <> 'PREPAYMENT'
AND POLL.matching_basis = 'QUANTITY' -- eliminate service line types
AND POLL.accrue_on_receipt_flag = DECODE(p_include_period_end, 1, POLL.accrue_on_receipt_flag, 'Y')
AND RT.creation_date <= NVL(CST_Inventory_PVT.g_run_receiving_date,RT.creation_date)
GROUP
BY RT.organization_id,
CILT.inventory_item_id,
DECODE(POL.item_id, NULL, POL.category_id, CILT.category_id),
CILT.cost_type_id,
DECODE(RT.transaction_type,
'RECEIVE', RT.transaction_id,
'MATCH', RT.transaction_id,
Get_ParentReceiveTxn(RT.transaction_id)),
DECODE(p_qty_by_revision, 1, POL.item_revision, NULL)
HAVING SUM(DECODE(RT.transaction_type,
'RECEIVE', -1 * RT.primary_quantity,
'DELIVER', 1 * RT.primary_quantity,
'RETURN TO RECEIVING', -1 * RT.primary_quantity,
'RETURN TO VENDOR', DECODE(PARENT_RT.transaction_type,
'UNORDERED', 0,
1 * RT.primary_quantity),
'MATCH', -1 * RT.primary_quantity,
'CORRECT', DECODE(PARENT_RT.transaction_type,
'UNORDERED', 0,
'RECEIVE', -1 * RT.primary_quantity,
'DELIVER', 1 * RT.primary_quantity,
'RETURN TO RECEIVING', -1 * RT.primary_quantity,
'RETURN TO VENDOR', DECODE(GRPARENT_RT.transaction_type,
'UNORDERED', 0,
1 * RT.primary_quantity),
'MATCH', -1 * RT.primary_quantity,
0),
0)
) <> 0;
SELECT transaction_id
INTO l_parent_transaction_id
FROM (
SELECT RT.transaction_id transaction_id,
RT.parent_transaction_id parent_transaction_id,
RT.transaction_type
FROM rcv_transactions RT
START WITH transaction_id = p_rcv_transaction_id
CONNECT BY transaction_id = PRIOR parent_transaction_id)
WHERE ((transaction_type = 'RECEIVE' and parent_transaction_id=-1)
OR transaction_type = 'MATCH');
l_min_cost_update_id NUMBER(15);
l_max_cost_update_id NUMBER(15);
l_latest_cost_update_id NUMBER(15);
SELECT DISTINCT
CIQT.organization_id,
CIQT.inventory_item_id,
CIQT.cost_type_id
FROM cst_inv_qty_temp CIQT,
mtl_parameters MP
WHERE CIQT.organization_id = p_organization_id
AND MP.organization_id = p_organization_id
AND MP.primary_cost_method = 1
AND CIQT.qty_source NOT IN (1,2,9,10) -- PRIOR SUMMARY, CURRENT SUMMARY,
-- RECEIVING, PAST RECEIVING
AND CIQT.cost_type_id IS NOT NULL; -- bug 6893581
SELECT DISTINCT
CIQT.organization_id,
CIQT.inventory_item_id,
CIQT.rcv_transaction_id
FROM cst_inv_qty_temp CIQT
WHERE CIQT.qty_source in (9,10);
SELECT mcacd_txn,
mmt_txn,
mmt_cost,
mcacd_cost,
TXN_DATE,
prior_COST ,
act,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost,
overhead_cost
FROM
(SELECT MCACD.TRANSACTION_ID mcacd_txn,
MMT.transaction_id mmt_txn,
NVL(MMT.actual_cost,0) mmt_cost,
MMT.transaction_action_id act,
NVL(MMT.prior_COST,0) prior_cost,
SUM(NVL(mcacd.actual_cost,0))
OVER (PARTITION BY MMT.transaction_id) mcacd_cost,
SUM(DECODE(mcacd.cost_element_id,1,NVL(mcacd.actual_cost,0),0))
OVER (PARTITION BY MMT.transaction_id) material_cost,
SUM(DECODE(mcacd.cost_element_id,2,NVL(mcacd.actual_cost,0),0))
OVER (PARTITION BY MMT.transaction_id) material_overhead_cost,
SUM(DECODE(mcacd.cost_element_id,3,NVL(mcacd.actual_cost,0),0))
OVER (PARTITION BY MMT.transaction_id) resource_cost,
SUM(DECODE(mcacd.cost_element_id,4,nvl(mcacd.actual_cost,0),0))
OVER (PARTITION BY MMT.transaction_id) outside_processing_cost,
SUM(DECODE(mcacd.cost_element_id,5,nvl(mcacd.actual_cost,0),0))
OVER (PARTITION BY MMT.transaction_id) overhead_cost,
NVL(MCACD.creation_date,MMT.creation_date) txn_date
FROM MTL_CST_ACTUAL_COST_DETAILS MCACD,
mtl_material_transactions mmt
WHERE MCACD.ORGANIZATION_ID(+) = p_organization_id
AND MCACD.inventory_item_id(+) = p_inventory_item_id
AND MMT.transaction_date > p_valuation_date
AND mmt.transaction_action_id NOT IN (5,30,40,41,42,43,50,51,52,/* 9764385: 15,22,*/11,17,10,13,9,14,7,26,36,25,56,57)
AND NOT (mmt.transaction_action_id IN (2,28,55,3) AND mmt.primary_quantity > 0)
--
-- Standard update only originated by standard cost update avoid PAC cost update
--
AND NOT (mmt.transaction_action_id = 24 AND mmt.transaction_source_type_id <> 11)
AND MMT.inventory_item_id = p_inventory_item_id
AND MMT.organization_id = p_organization_id
AND MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_onhand_date,MMT.creation_date)
AND mmt.transaction_id = mcacd.transaction_id (+) )
ORDER BY TXN_DATE asc,
mmt_txn asc;
INSERT INTO cst_inv_cost_temp(
organization_id,
inventory_item_id,
cost_type_id,
cost_source,
inventory_asset_flag,
item_cost,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost,
overhead_cost
)
SELECT p_organization_id,
p_inventory_item_id,
1,
2, -- PAST
CIC.inventory_asset_flag,
SUM(NVL(CIC.item_cost,0)),
SUM(NVL(CIC.material_cost,0)),
SUM(NVL(CIC.material_overhead_cost,0)),
SUM(NVL(CIC.resource_cost,0)),
SUM(NVL(CIC.outside_processing_cost,0)),
SUM(NVL(CIC.overhead_cost,0))
FROM cst_item_costs CIC,
mtl_parameters MP
WHERE CIC.cost_type_id = 1
AND MP.organization_id = p_organization_id
AND CIC.organization_id = MP.cost_organization_id
AND CIC.inventory_item_id = p_inventory_item_id
GROUP BY CIC.inventory_asset_flag;
l_rec.inventory_asset_flag := 1; --Standard cost update is only done for asset items
INSERT
INTO cst_inv_cost_temp(
organization_id,
inventory_item_id,
cost_type_id,
cost_source,
inventory_asset_flag,
item_cost,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost,
overhead_cost
)
SELECT l_organization_id,
l_inventory_item_id,
l_cost_type_id,
1, -- CURRENT
CIC.inventory_asset_flag,
NVL(CIC.item_cost,0),
NVL(CIC.material_cost,0),
NVL(CIC.material_overhead_cost,0),
NVL(CIC.resource_cost,0),
NVL(CIC.outside_processing_cost,0),
NVL(CIC.overhead_cost,0)
FROM mtl_parameters MP,
cst_item_costs CIC
WHERE MP.organization_id = l_organization_id
AND CIC.organization_id = MP.cost_organization_id
AND CIC.inventory_item_id = l_inventory_item_id
AND CIC.cost_type_id = l_cost_type_id;
INSERT
INTO cst_inv_cost_temp(
organization_id,
inventory_item_id,
cost_group_id,
cost_type_id,
cost_source,
inventory_asset_flag,
item_cost,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost,
overhead_cost
)
SELECT CIQT.organization_id,
CIQT.inventory_item_id,
CIQT.cost_group_id,
CIQT.cost_type_id,
1, -- CURRENT
CIC.inventory_asset_flag,
DECODE(
CIQT.cost_type_id,
MP.primary_cost_method,
NVL(CQL.item_cost,0),
NVL(CIC.item_cost,0)
),
DECODE(
CIQT.cost_type_id,
MP.primary_cost_method,
NVL(CQL.material_cost,0),
NVL(CIC.material_cost,0)
),
DECODE(
CIQT.cost_type_id,
MP.primary_cost_method,
NVL(CQL.material_overhead_cost,0),
NVL(CIC.material_overhead_cost,0)
),
DECODE(
CIQT.cost_type_id,
MP.primary_cost_method,
NVL(CQL.resource_cost,0),
NVL(CIC.resource_cost,0)
),
DECODE(
CIQT.cost_type_id,
MP.primary_cost_method,
NVL(CQL.outside_processing_cost,0),
NVL(CIC.outside_processing_cost,0)
),
DECODE(
CIQT.cost_type_id,
MP.primary_cost_method,
NVL(CQL.overhead_cost,0),
NVL(CIC.overhead_cost,0)
)
FROM (
SELECT DISTINCT
organization_id,
inventory_item_id,
cost_group_id,
cost_type_id
FROM cst_inv_qty_temp
WHERE qty_source NOT IN (1,2,9,10)
) CIQT,
cst_quantity_layers CQL,
cst_item_costs CIC,
mtl_parameters MP
WHERE CIC.organization_id = CIQT.organization_id
AND CIC.inventory_item_id = CIQT.inventory_item_id
AND CIC.cost_type_id = CIQT.cost_type_id
AND MP.organization_id = CIQT.organization_id
AND MP.primary_cost_method <> 1
AND CQL.organization_id (+) = CIQT.organization_id
AND CQL.inventory_item_id (+) = CIQT.inventory_item_id
AND CQL.cost_group_id (+) = CIQT.cost_group_id
-- Outer join on CQL to insert zero costs for expense
-- items and asset items that do not have a layer
AND CIQT.organization_id = p_organization_id;
l_min_cost_update_id := NULL;
l_max_cost_update_id := NULL;
INSERT INTO cst_inv_cost_temp
( organization_id,
inventory_item_id,
cost_type_id,
cost_source,
inventory_asset_flag,
item_cost,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost,
overhead_cost
)
SELECT l_organization_id,
l_inventory_item_id,
l_cost_type_id,
1, -- CURRENT
CIC.inventory_asset_flag,
NVL(CIC.item_cost,0),
NVL(CIC.material_cost,0),
NVL(CIC.material_overhead_cost,0),
NVL(CIC.resource_cost,0),
NVL(CIC.outside_processing_cost,0),
NVL(CIC.overhead_cost,0)
FROM mtl_parameters MP,
cst_item_costs CIC
WHERE MP.organization_id = l_organization_id
AND CIC.organization_id = MP.cost_organization_id
AND CIC.inventory_item_id = l_inventory_item_id
AND CIC.cost_type_id = l_cost_type_id;
SELECT nvl(CIC.inventory_asset_flag,2)
INTO l_exp_item_flag
FROM mtl_parameters MP,
cst_item_costs CIC
WHERE MP.organization_id = l_organization_id
AND CIC.organization_id = MP.cost_organization_id
AND CIC.inventory_item_id = l_inventory_item_id
AND CIC.cost_type_id = 1;
SELECT MIN(CSC.cost_update_id)
INTO l_min_cost_update_id
FROM cst_standard_costs CSC,
mtl_parameters MP
WHERE MP.organization_id = l_organization_id
AND CSC.organization_id = MP.cost_organization_id
AND CSC.inventory_item_id = l_inventory_item_id
AND CSC.standard_cost_revision_date > p_valuation_date;
IF l_min_cost_update_id IS NOT NULL THEN
-- Yes. Figure out the prior cost update
SELECT MAX(CSC.cost_update_id)
INTO l_max_cost_update_id
FROM cst_standard_costs CSC,
mtl_parameters MP
WHERE MP.organization_id = l_organization_id
AND CSC.organization_id = MP.cost_organization_id
AND CSC.inventory_item_id = l_inventory_item_id
AND CSC.standard_cost_revision_date <= p_valuation_date;
SELECT CSC.cost_update_id
FROM cst_standard_costs CSC,
mtl_parameters MP
WHERE MP.organization_id = l_organization_id
AND CSC.organization_id = MP.cost_organization_id
AND CSC.inventory_item_id = l_inventory_item_id
AND CSC.standard_cost_revision_date <= p_valuation_date
order by CSC.standard_cost_revision_date DESC; -- to get the latest updated record's cost_update_id
FETCH csc_cur INTO l_latest_cost_update_id;
IF l_max_cost_update_id is not null THEN
l_max_cost_update_id := l_latest_cost_update_id;
IF l_max_cost_update_id IS NOT NULL THEN
-- Use the cost in cst_elemental_costs of that cost history
INSERT
INTO cst_inv_cost_temp(
organization_id,
inventory_item_id,
cost_type_id,
cost_source,
inventory_asset_flag,
item_cost,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost,
overhead_cost
)
SELECT l_organization_id,
l_inventory_item_id,
1,
2, -- PAST
1, -- Standard cost update is only done for asset items
SUM(NVL(CEC.standard_cost,0)),
SUM(DECODE(CEC.cost_element_id,1,NVL(CEC.standard_cost,0),0)),
SUM(DECODE(CEC.cost_element_id,2,NVL(CEC.standard_cost,0),0)),
SUM(DECODE(CEC.cost_element_id,3,NVL(CEC.standard_cost,0),0)),
SUM(DECODE(CEC.cost_element_id,4,NVL(CEC.standard_cost,0),0)),
SUM(DECODE(CEC.cost_element_id,5,NVL(CEC.standard_cost,0),0))
FROM cst_elemental_costs CEC,
mtl_parameters MP
WHERE CEC.cost_update_id = l_max_cost_update_id
AND MP.organization_id = l_organization_id
AND CEC.organization_id = MP.cost_organization_id
AND CEC.inventory_item_id = l_inventory_item_id;
l_inventory_item_id || ' Cost Update ID '||
l_max_cost_update_id
);
END IF; --l_max_cost_update_id
INSERT
INTO cst_inv_cost_temp(
organization_id,
inventory_item_id,
cost_type_id,
cost_source,
inventory_asset_flag,
item_cost,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost,
overhead_cost
)
SELECT
l_organization_id,
l_inventory_item_id,
1,
2, -- PAST
CIC.inventory_asset_flag,
NVL(CIC.item_cost,0),
NVL(CIC.material_cost,0),
NVL(CIC.material_overhead_cost,0),
NVL(CIC.resource_cost,0),
NVL(CIC.outside_processing_cost,0),
NVL(CIC.overhead_cost,0)
FROM mtl_parameters MP,
cst_item_costs CIC
WHERE MP.organization_id = l_organization_id
AND CIC.organization_id = MP.cost_organization_id
AND CIC.inventory_item_id = l_inventory_item_id
AND CIC.cost_type_id = 1;
END IF; --l_min_cost_update_id
INSERT
INTO cst_inv_cost_temp(
organization_id,
inventory_item_id,
cost_group_id,
cost_type_id,
cost_source,
inventory_asset_flag,
item_cost,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost,
overhead_cost
)
SELECT CIQT.organization_id,
CIQT.inventory_item_id,
CIQT.cost_group_id,
CIQT.cost_type_id,
1, -- CURRENT
CIC.inventory_asset_flag,
NVL(CIC.item_cost,0),
NVL(CIC.material_cost,0),
NVL(CIC.material_overhead_cost,0),
NVL(CIC.resource_cost,0),
NVL(CIC.outside_processing_cost,0),
NVL(CIC.overhead_cost,0)
FROM (
SELECT DISTINCT
organization_id,
inventory_item_id,
cost_group_id,
cost_type_id
FROM cst_inv_qty_temp
WHERE qty_source NOT IN (1,2,9,10)
) CIQT,
cst_item_costs CIC,
mtl_parameters MP
WHERE CIC.organization_id = CIQT.organization_id
AND CIC.inventory_item_id = CIQT.inventory_item_id
AND CIC.cost_type_id = CIQT.cost_type_id
AND MP.organization_id = CIQT.organization_id
AND MP.primary_cost_method <> 1
AND CIQT.organization_id = p_organization_id;
INSERT
INTO cst_inv_cost_temp(
organization_id,
inventory_item_id,
cost_group_id,
cost_type_id,
cost_source,
inventory_asset_flag,
item_cost,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost,
overhead_cost
)
SELECT DISTINCT
CIQT.organization_id,
CIQT.inventory_item_id,
CIQT.cost_group_id,
CIQT.cost_type_id,
2, -- PAST
2, -- EXPENSE
0,
0,
0,
0,
0,
0
FROM cst_inv_qty_temp CIQT,
mtl_parameters MP,
cst_item_costs CIC
WHERE MP.organization_id = CIQT.organization_id
AND MP.primary_cost_method <> 1
AND CIC.organization_id = CIQT.organization_id
AND CIC.inventory_item_id = CIQT.inventory_item_id
AND CIC.cost_type_id = CIQT.cost_type_id
AND CIC.inventory_asset_flag <> 1
AND CIQT.qty_source NOT IN (1,2,9,10) -- PRIOR SUMMARY, CURRENT SUMMARY, RECEIVING, PAST RECEIVING
AND CIQT.organization_id = p_organization_id;
SELECT primary_cost_method
INTO l_cost_method
FROM mtl_parameters
WHERE organization_id = p_organization_id;
INSERT INTO cst_inv_cost_temp(
organization_id,
inventory_item_id,
cost_group_id,
cost_type_id,
cost_source,
inventory_asset_flag,
item_cost,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost,
overhead_cost
)
SELECT CAND_REC.organization_id,
CAND_REC.inventory_item_id,
CAND_REC.cost_group_id,
l_cost_method ,
2, -- PAST
1, -- ASSET
SUM(NVL(MCACD.prior_cost,0)),
SUM(
DECODE(MCACD.cost_element_id,1,NVL(MCACD.prior_cost,0),0)
),
SUM(
DECODE(MCACD.cost_element_id,2,NVL(MCACD.prior_cost,0),0)
),
SUM(
DECODE(MCACD.cost_element_id,3,NVL(MCACD.prior_cost,0),0)
),
SUM(
DECODE(MCACD.cost_element_id,4,NVL(MCACD.prior_cost,0),0)
),
SUM(
DECODE(MCACD.cost_element_id,5,NVL(MCACD.prior_cost,0),0)
)
from mtl_cst_actual_cost_details MCACD,
cst_quantity_layers CQL,
( select organization_id,
inventory_item_id,
cost_group_id,
transaction_id,
transaction_date,
transaction_costed_date,
creation_date,
txn_rank
from
( select organization_id,
inventory_item_id,
cost_group_id,
transaction_id,
transaction_date,
transaction_costed_date,
creation_date,
row_number() over (partition by organization_id,inventory_item_id,cost_group_id
Order by transaction_costed_date,transaction_date,creation_date,transaction_id) txn_rank
from
( select /*+ leading(CIQT) push_pred(RAW_DATA) */
DISTINCT
CIQT.organization_id,
CIQT.inventory_item_id,
CIQT.cost_group_id,
RAW_DATA.transaction_id,
RAW_DATA.transaction_date,
RAW_DATA.transaction_costed_date,
RAW_DATA.creation_date
from cst_quantity_layers CQL,
( SELECT /*+ no_merge */
DISTINCT
CIQT1.organization_id,
CIQT1.inventory_item_id,
CIQT1.cost_group_id
FROM cst_inv_qty_temp CIQT1
WHERE CIQT1.qty_source NOT IN (1,2,9,10)
-- PRIOR SUMMARY, CURRENT SUMMARY, RECEIVING, PAST RECEIVING
AND NOT EXISTS ( SELECT 1
FROM cst_inv_cost_temp CICT
WHERE CICT.organization_id = CIQT1.organization_id
AND CICT.inventory_item_id = CIQT1.inventory_item_id
AND CICT.cost_source = 2
)
AND CIQT1.organization_id = p_organization_id
) CIQT,
( /*Transfer Org Txns*/
select /*+ leading(MIP) */
MMT.transaction_id,
MMT.transaction_date,
nvl(MCACD.transaction_costed_date,MCACD.creation_date) transaction_costed_date,
MMT.creation_date,
MCACD.layer_id,
MMT.inventory_item_id,
MCACD.organization_id
from ( SELECT /*+ no_merge */
decode(from_organization_id,
p_organization_id,to_organization_id,
from_organization_id) relevant_org,
fob_point,
from_organization_id,
to_organization_id
from mtl_interorg_parameters
where from_organization_id = p_organization_id
or to_organization_id = p_organization_id) MIP,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_CST_ACTUAL_COST_DETAILS MCACD
where ( ( MMT.creation_date <= NVL(NVL(CST_Inventory_PVT.g_run_incmng_intransit_date,
CST_Inventory_PVT.g_run_onhand_date),
MMT.creation_date)
AND MMT.transaction_action_id = 21
)
OR ( MMT.creation_date <= NVL(NVL(CST_Inventory_PVT.g_run_outgng_intransit_date,
CST_Inventory_PVT.g_run_onhand_date),
MMT.creation_date)
AND MMT.transaction_action_id = 12
)
)
AND MMT.costed_flag is null
AND MMT.organization_id = MIP.relevant_org
AND NVL(MMT.fob_point,MIP.fob_point)=decode(MMT.transaction_Action_id,
21,1,
2)
AND MMT.transaction_action_id in (21,12)
AND MMT.transaction_date > p_valuation_date
AND MCACD.transaction_id = MMT.transaction_id
AND MCACD.organization_id = p_organization_id
AND MMT.transfer_organization_id = p_organization_id
AND MMT.organization_id = decode(MMT.transaction_action_id,
21,MIP.from_organization_id,
MIP.to_organization_id)
AND MMT.transfer_organization_id = decode(MMT.transaction_action_id,
21,MIP.to_organization_id,
MIP.from_organization_id)
union all
/*OWN Org Txns*/
select MMT.transaction_id,
MMT.transaction_date,
nvl(MCACD.transaction_costed_date,MCACD.creation_date) transaction_costed_date,
MMT.creation_date,
MCACD.layer_id,
MMT.inventory_item_id,
MCACD.organization_id
from MTL_MATERIAL_TRANSACTIONS MMT,
MTL_CST_ACTUAL_COST_DETAILS MCACD
where MMT.creation_date <= NVL(CST_Inventory_PVT.g_run_onhand_date,MMT.creation_date)
AND MMT.costed_flag is null
AND MMT.transaction_action_id not in (5,30,40,41,42,43,50,51,52,11,17,10,13,9,14,7,26,36,25,56,57)
AND NOT (MMT.transaction_action_id IN (2,28,55) AND MMT.primary_quantity > 0)
AND NOT (MMT.transaction_action_id = 24 and MMT.transaction_source_type_id = 14)
AND MMT.organization_id = NVL(MMT.owning_organization_id, MMT.organization_id)
AND NVL(MMT.owning_tp_type,2) = 2
AND (NVL(MMT.logical_transaction,-1) <> 1
OR MMT.transaction_action_id IN (15,22)
)
AND MMT.prior_cost is not null
AND MMT.transaction_date > p_valuation_date
AND MCACD.transaction_id = MMT.transaction_id
AND ( ( MMT.subinventory_code IS NULL
OR ( MMT.subinventory_code IS NOT NULL
AND EXISTS ( SELECT NULL
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.organization_id = MMT.organization_id
AND MSI.secondary_inventory_name = MMT.subinventory_code
AND MSI.asset_inventory = 1
)
)
)
OR
( MMT.organization_id = nvl(MMT.transfer_organization_id,MMT.organization_id)
AND MMT.transfer_subinventory IS NOT NULL
AND EXISTS ( SELECT NULL
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.organization_id = MMT.organization_id
AND MSI.secondary_inventory_name = MMT.transfer_subinventory
AND MSI.asset_inventory = 1
)
)
)
AND MCACD.organization_id = p_organization_id
AND MMT.organization_id = p_organization_id
) RAW_DATA
where RAW_DATA.layer_id = CQL.layer_id
AND RAW_DATA.inventory_item_id = CIQT.inventory_item_id
AND CIQT.cost_group_id = CQL.cost_group_id
AND CIQT.organization_id = CQL.organization_id
AND CIQT.organization_id = RAW_DATA.organization_id
AND RAW_DATA.organization_id = CQL.organization_id
AND RAW_DATA.organization_id = p_organization_id
AND CQL.organization_id = p_organization_id
AND CIQT.organization_id = p_organization_id
AND CQL.inventory_item_id = CIQT.inventory_item_id
AND CQL.inventory_item_id = RAW_DATA.inventory_item_id
) cand_data
)
where txn_rank = 1
)CAND_REC
WHERE MCACD.transaction_id = CAND_REC.transaction_id
AND MCACD.layer_id = CQL.layer_id
AND MCACD.inventory_item_id = CAND_REC.inventory_item_id
AND CQL.inventory_item_id = CAND_REC.inventory_item_id
AND CQL.organization_id = CAND_REC.organization_id
AND MCACD.organization_id = CAND_REC.organization_id
AND CQL.cost_group_id = CAND_REC.cost_group_id
/* Only take action 2 for the common issue/return to wip */
AND NOT ( MCACD.transaction_action_id in (1,27)
AND MCACD.layer_id = CQL.layer_id
AND EXISTS ( SELECT 'restrict wip component issue action'
FROM mtl_cst_actual_cost_details MCACD2
WHERE MCACD2.transaction_id = MCACD.transaction_id
AND MCACD2.layer_id = MCACD.layer_id
AND MCACD2.transaction_action_id = 2
)
)
GROUP BY CAND_REC.organization_id,
CAND_REC.inventory_item_id,
CAND_REC.cost_group_id;
INSERT
INTO cst_inv_cost_temp(
organization_id,
inventory_item_id,
cost_group_id,
cost_type_id,
cost_source,
inventory_asset_flag,
item_cost,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost,
overhead_cost
)
SELECT CIQT.organization_id,
CIQT.inventory_item_id,
CIQT.cost_group_id,
CIQT.primary_cost_method,
2, -- PAST
1, -- ASSET
NVL(CQL.item_cost,0),
NVL(CQL.material_cost,0),
NVL(CQL.material_overhead_cost,0),
NVL(CQL.resource_cost,0),
NVL(CQL.outside_processing_cost,0),
NVL(CQL.overhead_cost,0)
FROM cst_quantity_layers CQL,
(
SELECT DISTINCT
CIQT.organization_id,
CIQT.inventory_item_id,
CIQT.cost_group_id,
MP.primary_cost_method
FROM cst_inv_qty_temp CIQT,
mtl_parameters MP
WHERE MP.organization_id = CIQT.organization_id
AND MP.primary_cost_method <> 1
AND CIQT.qty_source NOT IN (1,2,9,10)
-- PRIOR SUMMARY, CURRENT SUMMARY, RECEIVING, PAST RECEIVING
AND CIQT.organization_id = p_organization_id
) CIQT
WHERE CQL.organization_id (+) = CIQT.organization_id
AND CQL.inventory_item_id (+) = CIQT.inventory_item_id
AND CQL.cost_group_id (+) = CIQT.cost_group_id
/* The outer join above is for asset items that do not have a layer */
/* Added below condition as the outer join inserts zero cost duplicate row in CICT */
AND CIQT.cost_group_id IS NOT NULL /*--Bug 9161102- FP for bug 7672378 */
AND NOT EXISTS (
SELECT 1
FROM cst_inv_cost_temp CICT
WHERE CICT.organization_id = CIQT.organization_id
AND CICT.inventory_item_id = CIQT.inventory_item_id
AND CICT.cost_group_id = CIQT.cost_group_id
AND CICT.cost_source = 2
);
INSERT
INTO cst_inv_cost_temp(
cost_source,
organization_id,
inventory_item_id,
rcv_transaction_id,
item_cost
)
SELECT l_rcv_cost_source,
l_organization_id,
l_inventory_item_id,
l_rcv_transaction_id,
l_receiving_cost
FROM dual;