The following lines contain the word 'select', 'insert', 'update' or 'delete':
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
) 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
) 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'
);
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
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)
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)
)
),
-- 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.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)
AND ( mmt.transaction_action_id = 12
OR mmt.transaction_action_id = 21)
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
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.transfer_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(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_cost_group_id,
2,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
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 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(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.cost_group_id,
2,MMT.transfer_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
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
AND MMT.transaction_date > p_valuation_date
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_cost_group_id,
2,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
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(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.cost_group_id,
2,MMT.transfer_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
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
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(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_cost_group_id,
2,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
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 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.transfer_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(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.cost_group_id,
2,MMT.transfer_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
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.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(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_cost_group_id,
2,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
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
AND MMT.transaction_date > p_valuation_date
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.cost_group_id,
2,MMT.transfer_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
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
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.transfer_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(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_cost_group_id,
2,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
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 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(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.cost_group_id,
2,MMT.transfer_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
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
AND MMT.transaction_date > p_valuation_date
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_cost_group_id,
2,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
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(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.cost_group_id,
2,MMT.transfer_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
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
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(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_cost_group_id,
2,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
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 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.transfer_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(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.cost_group_id,
2,MMT.transfer_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
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.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(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.transfer_cost_group_id,
2,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
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
AND MMT.transaction_date > p_valuation_date
AND MMT.inventory_item_id = ITEMS.inventory_item_id
AND DECODE(
NVL(MMT.fob_point,MIP.fob_point),
1,MMT.cost_group_id,
2,MMT.transfer_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
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')
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')
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);
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(+) = c_organization_id
AND MCACD.inventory_item_id(+) = c_inventory_item_id
AND MMT.transaction_date > c_valuation_date
AND mmt.transaction_action_id NOT IN (5,30,40,41,42,43,50,51,52,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 = c_inventory_item_id
AND MMT.organization_id = c_organization_id
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
) 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);
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;
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,
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 = l_organization_id
AND CIC.organization_id = MP.cost_organization_id
AND CIC.inventory_item_id = l_inventory_item_id
GROUP BY CIC.inventory_asset_flag;
l_rec.inventory_asset_flag := 1; --Standard cost update is only done for asset items
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;
IF l_max_cost_update_id IS NOT NULL
THEN
-- Use the cost in cst_elemental_costs.
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
);
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
0,
0,
0,
0,
0,
0
FROM dual;
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 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 /*+ ORDERED use_nl(CQL,MCACD) use_hash(TFR_TXN,OWN_TXN) */
CIQT.organization_id,
CIQT.inventory_item_id,
CIQT.cost_group_id,
CIQT.primary_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 (
SELECT /*+ no_merge */ 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 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_source = 2
)
AND CIQT.organization_id = p_organization_id
) CIQT,
cst_quantity_layers CQL,
(
SELECT /*+ ORDERED use_hash(MMT,MIP) swap_join_inputs(MIP) */
MIN(MMT.transaction_id) transaction_id,
MMT.transaction_date,
MMT.transfer_organization_id,
MMT.inventory_item_id,
MMT.transfer_cost_group_id,
'N' restrict_mcacd
FROM
(
SELECT /*+ no_merge leading(MIP) use_hash(MMT)*/
MIN(MMT.transaction_date) transaction_date,
MMT.transfer_organization_id,
MMT.inventory_item_id,
MMT.transfer_cost_group_id
FROM mtl_material_transactions MMT,
mtl_interorg_parameters MIP
WHERE MMT.transaction_action_id = 21
AND MIP.from_organization_id = MMT.organization_id
AND MIP.to_organization_id = MMT.transfer_organization_id
AND NVL(MMT.fob_point,MIP.fob_point)=1
AND MMT.costed_flag IS NULL
AND MMT.transaction_date > p_valuation_date
AND MMT.transfer_organization_id = p_organization_id
GROUP
BY MMT.transfer_organization_id,
MMT.inventory_item_id,
MMT.transfer_cost_group_id
) MINDATE,
mtl_material_transactions MMT,
mtl_interorg_parameters MIP
WHERE MMT.transfer_organization_id = MINDATE.transfer_organization_id
AND MMT.inventory_item_id = MINDATE.inventory_item_id
AND MMT.transfer_cost_group_id = MINDATE.transfer_cost_group_id
AND MMT.transaction_date = MINDATE.transaction_date
AND MMT.transaction_action_id = 21
AND MIP.from_organization_id = MMT.organization_id
AND MIP.to_organization_id = MMT.transfer_organization_id
AND NVL(MMT.fob_point,MIP.fob_point)=1
AND MMT.costed_flag IS NULL
AND MMT.transaction_date > p_valuation_date
AND MMT.transfer_organization_id = p_organization_id
GROUP
BY MMT.transaction_date,
MMT.transfer_organization_id,
MMT.inventory_item_id,
MMT.transfer_cost_group_id
) TFR_TXN,
(
SELECT /*+ leading(MINDATE) use_nl(MMT) index(MMT,MTL_MATERIAL_TRANSACTIONS_N1) */
MIN(MMT.transaction_id) transaction_id,
MMT.transaction_date,
MMT.organization_id,
MMT.inventory_item_id,
/* In average costing organizations, common issue to WIP
results in reaveraging of the item cost in the transfer
cost group */
DECODE(
l_cost_method,
2,
DECODE(
MMT.transaction_action_id,
1,
DECODE(
MMT.transaction_source_type_id,
5,
NVL(MMT.TRANSFER_COST_GROUP_ID, MMT.cost_group_id),
MMT.cost_group_id
),
MMT.cost_group_id
),
MMT.cost_group_id
) cost_group_id,
/* For common issue to WIP transactions in average costing
organizations, we insert rows both for the cost group
transfer and issue to WIP. We are only interested in the
rows corresponding to the cost group transfer. The
restrict_mcacd flag is used to determine if we need to
check on MCACD.transaction_action_id */
DECODE(
l_cost_method,
2,
DECODE(
MMT.transaction_action_id,
1,
DECODE(
MMT.transaction_source_type_id,
5,
DECODE(
MMT.transfer_cost_group_id,
NULL,
'N',
/* Bug 3500534
It is possible to have normal issue to WIP transactions in
average costing organizations with transfer_cost_group_id
= cost_group_id. Adding the following condition for
cost_group_id ensures such cases are handled as normal issue
to WIP rather than common. */
MMT.cost_group_id,
'N',
'Y'
),
'N'
),
'N'
),
'N'
) restrict_mcacd
FROM mtl_material_transactions MMT,
(
SELECT /*+ no_merge */ MIN(MMT.transaction_date) transaction_date,
MMT.organization_id,
MMT.inventory_item_id,
DECODE(
l_cost_method,
2,
DECODE(
MMT.transaction_action_id,
1,
DECODE(
MMT.transaction_source_type_id,
5,
NVL(MMT.TRANSFER_COST_GROUP_ID, MMT.cost_group_id),
MMT.cost_group_id
),
MMT.cost_group_id
),
MMT.cost_group_id
) cost_group_id
FROM mtl_material_transactions MMT
WHERE MMT.transaction_action_id <> 30
AND MMT.prior_cost IS NOT NULL
AND MMT.costed_flag IS NULL
-- Ignore consigned transactions
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
AND MMT.transaction_date > p_valuation_date
AND MMT.organization_id = p_organization_id
GROUP
BY MMT.organization_id,
MMT.inventory_item_id,
DECODE(
l_cost_method,
2,
DECODE(
MMT.transaction_action_id,
1,
DECODE(
MMT.transaction_source_type_id,
5,
NVL(MMT.TRANSFER_COST_GROUP_ID, MMT.cost_group_id),
MMT.cost_group_id
),
MMT.cost_group_id
),
MMT.cost_group_id
)
) MINDATE
WHERE MMT.organization_id = MINDATE.organization_id
AND MMT.inventory_item_id = MINDATE.inventory_item_id
AND DECODE(
l_cost_method,
2,
DECODE(
MMT.transaction_action_id,
1,
DECODE(
MMT.transaction_source_type_id,
5,
NVL(MMT.transfer_cost_group_id, MMT.cost_group_id),
MMT.cost_group_id
),
MMT.cost_group_id
),
MMT.cost_group_id
) = MINDATE.cost_group_id
AND MMT.transaction_date = MINDATE.transaction_date
AND MMT.transaction_action_id <> 30
AND MMT.prior_cost IS NOT NULL
AND MMT.costed_flag IS NULL
-- Ignore consigned transactions
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
AND MMT.organization_id = p_organization_id
GROUP
BY MMT.transaction_date,
MMT.organization_id,
MMT.inventory_item_id,
DECODE(
l_cost_method,
2,
DECODE(
MMT.transaction_action_id,
1,
DECODE(
MMT.transaction_source_type_id,
5,
NVL(MMT.transfer_cost_group_id, MMT.cost_group_id),
MMT.cost_group_id
),
MMT.cost_group_id
),
MMT.cost_group_id
),
DECODE(
l_cost_method,
2,
DECODE(
MMT.transaction_action_id,
1,
DECODE(
MMT.transaction_source_type_id,
5,
DECODE(
MMT.transfer_cost_group_id,
NULL,
'N',
/* Bug 3500534
It is possible to have normal issue to WIP transactions in
average costing organizations with transfer_cost_group_id
= cost_group_id. Adding the following condition for
cost_group_id ensures such cases are handled as normal issue
to WIP rather than common. */
MMT.cost_group_id,
'N',
'Y'
),
'N'
),
'N'
),
'N'
)
) OWN_TXN,
mtl_cst_actual_cost_details MCACD
WHERE MCACD.transaction_id = DECODE(
NVL(TFR_TXN.transaction_id,-1),
-1,
OWN_TXN.transaction_id,
DECODE(
NVL(OWN_TXN.transaction_id,-1),
-1,
TFR_TXN.transaction_id,
DECODE(
TFR_TXN.transaction_date,
OWN_TXN.transaction_date,
LEAST(
TFR_TXN.transaction_id,
OWN_TXN.transaction_id
),
DECODE(
LEAST(
TFR_TXN.transaction_date,
OWN_TXN.transaction_date
),
TFR_TXN.transaction_date,
TFR_TXN.transaction_id,
OWN_TXN.transaction_id
)
)
)
)
AND MCACD.layer_id = CQL.layer_id
AND MCACD.organization_id = p_organization_id
AND MCACD.transaction_action_id =
DECODE(
MCACD.transaction_id,
OWN_TXN.transaction_id,
DECODE(OWN_TXN.restrict_mcacd, 'Y', 2, MCACD.transaction_action_id),
MCACD.transaction_action_id
)
AND CQL.organization_id = CIQT.organization_id
AND CQL.cost_group_id = CIQT.cost_group_id
AND CQL.inventory_item_id = CIQT.inventory_item_id
AND TFR_TXN.transfer_organization_id (+) = CIQT.organization_id
AND TFR_TXN.transfer_cost_group_id (+) = CIQT.cost_group_id
AND TFR_TXN.inventory_item_id (+) = CIQT.inventory_item_id
AND OWN_TXN.organization_id (+) = CIQT.organization_id
AND OWN_TXN.cost_group_id (+) = CIQT.cost_group_id
AND OWN_TXN.inventory_item_id (+) = CIQT.inventory_item_id
GROUP
BY CIQT.organization_id,
CIQT.inventory_item_id,
CIQT.cost_group_id,
CIQT.primary_cost_method;
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 */
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;