Home » RDBMS Server » Performance Tuning » Report Taking So Much Of Time and goes into ORA-01555 (Oracle Apps 11.5.10.2)
Report Taking So Much Of Time and goes into ORA-01555 [message #396889] |
Wed, 08 April 2009 05:35 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sumitgarg
Messages: 3 Registered: April 2009 Location: Ahmedabad
|
Junior Member |
|
|
Hi All,
I m running a report in Oracle Apps and it goes in to error with ora-01555 error.The report contains insert and update statements as follows:
INSERT INTO HHLI.HHLI_RG1
(INVENTORY_ITEM_ID,
SUBINVENTORY_CODE,
MNF_QTY, --r
CT3, --i
EXPORT , --rv
EXCISE, --iv
ORGANIZATION_ID
)
Select
inventory_item_id,
subinventory_code,
sum(decode(sign(mmtt.primary_quantity),1,mmtt.primary_quantity,0)) m_qty,
sum(decode(sign(mmtt.primary_quantity),-1,mmtt.primary_quantity,0)) ct3,
sum(decode(sign(mmtt.primary_quantity),1,nvl(mmtt.primary_quantity,0)*nvl(mmtt.actual_cost,nvl(mmtt.transaction_cost,0)),0)
) rcpts_value,
sum(decode(sign(mmtt.primary_quantity),-1,nvl(mmtt.primary_quantity,0)*nvl(mmtt.actual_cost,nvl(mmtt.transaction_cost,0)),0)
) isss_value ,
mmtt.ORGANIZATION_ID
From Mtl_Material_Transactions MMTT
Where MMTT.Organization_Id = :P_ORG_ID
And MMTT.INVENTORY_ITEM_ID = Nvl(:P_Item_code,MMTT.INVENTORY_ITEM_ID)
and Trunc(MMTT.Transaction_date) >= :P_From_Date
and Trunc(MMTT.Transaction_date) <= :P_To_Date
and MMTT.Subinventory_Code = Nvl(:P_Subinv,MMTT.Subinventory_Code)
group by mmtt.subinventory_code, mmtt.ORGANIZATION_ID , mmtt.inventory_item_id;
INSERT INTO HHLI.HHLI_RG1
(INVENTORY_ITEM_ID,
SUBINVENTORY_CODE,
op_qty,
ORGANIZATION_ID
)
Select inventory_item_id,
subinventory_code,
sum(primary_quantity),
mmtt.ORGANIZATION_ID
From Mtl_Material_Transactions MMTT
Where organization_Id = :P_ORG_ID
And inventory_item_id = Nvl(:P_Item_code,inventory_item_id)
And Trunc(MMTT.Transaction_date) < :P_from_Date
And MMTT.Subinventory_Code = Nvl(:P_Subinv,MMTT.Subinventory_Code)
group by inventory_item_id,
subinventory_code,
mmtt.ORGANIZATION_ID;
UPDATE /*+ DRIVING_SITE(mtl_material_transactions) */ HHLI.hhli_rg1 m
SET wip = (
SELECT new_cost
FROM mtl_material_transactions k
WHERE k.transaction_id = (SELECT MAX(TRANSACTION_ID)
FROM MTL_MATERIAL_TRANSACTIONS L
WHERE L.INVENTORY_ITEM_ID = k.INVENTORY_ITEM_ID
AND L.ORGANIZATION_ID = k.ORGANIZATION_ID
AND TRUNC(L.TRANSACTION_DATE) < :P_from_DATE
)
AND k.inventory_item_id = m.inventory_item_id
AND k.organization_id = m.organization_id
AND trunc(k.transaction_date) < :P_from_Date
);
UPDATE /*+ DRIVING_SITE(mtl_material_transactions) */HHLI.hhli_rg1 m
SET OUT_SRC = (
SELECT new_cost
FROM mtl_material_transactions k
WHERE k.transaction_id = (SELECT MAX(TRANSACTION_ID)
FROM MTL_MATERIAL_TRANSACTIONS L
WHERE L.INVENTORY_ITEM_ID = k.INVENTORY_ITEM_ID
AND L.ORGANIZATION_ID = k.ORGANIZATION_ID
AND TRUNC(L.TRANSACTION_DATE) <= :P_to_DATE
)
AND k.inventory_item_id = m.inventory_item_id
AND k.organization_id = m.organization_id
AND trunc(k.transaction_date) <= :P_to_Date
);
exception
when no_data_found then
null;
when others then
null;
end;
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Sep 10 23:38:41 CDT 2024
|