Hello Experts,
I am receiving the following error: ORA-00933: SQL command not properly ended
Please help..
DECLARE
l_seq_sim NUMBER := -667;
BEGIN
BEGIN
--Dropping the table before building new query
EXECUTE IMMEDIATE 'DROP TABLE LINE_TEMP';
DBMS_OUTPUT.put_line ('Table LINE_TEMP DROPPED SUCCESSFULLY');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error during dropping table LINE_TEMP');
END;
BEGIN
--Creating new table based on the SELECT logic
EXECUTE IMMEDIATE 'CREATE TABLE LINE_TEMP
AS
SELECT abom0, pl0, so0, cin0, it0, cq0, cyf0, ecq0, pq0, ecq0 * pq0 tq0
,abom1, pl1, so1, cin1, it1, cq1, cyf1, ecq1, pq0 pq1
,ecq1 * pq0 tq1, abom2, pl2, so2, cin2, it2, cq2, cyf2, ecq2
,pq0 pq2, ecq2 * pq0 tq2, abom3, pl3, so3, cin3, it3, cq3, cyf3
,ecq3, pq0 pq3, ecq3 * pq0 tq3, abom4, pl4, so4, cin4, it4, cq4
,cyf4, ecq4, pq0 pq4, ecq4 * pq0 tq4
FROM
-- Level 0 --
( SELECT top_alternate_designator abom0, sort_order ss0
,sort_order so0, plan_level pl0, component_item_id cid0
,segment1 cin0, msi.item_type it0
,component_quantity cq0
,NVL (component_yield_factor, 1.0) cyf0
,component_quantity * NVL (component_yield_factor, 1.0)
ecq0, sumqty pq0, component_quantity * sumqty tq0
FROM bom_explosion_temp bet, mtl_system_items msi
,sum bw
WHERE bet.component_item_id = msi.inventory_item_id
AND bet.plan_level = 0
AND bet.GROUP_ID = ' || l_seq_sim || '
AND msi.organization_id = 103
AND bet.component_item_id = bw.item_id
ORDER BY bet.sort_order) bet
-- Level 1 --
(SELECT '' '' abom1
,SUBSTR (
bet.sort_order
,1
,4
)
ss1, sort_order so1
,plan_level pl1
,component_item_id cid1
,segment1 cin1
,msi.item_type it1
,component_quantity cq1
,component_yield_factor cyf1
,extended_quantity ecq1
FROM bom_explosion_temp bet
,mtl_system_items msi
,sum bw
WHERE bet.component_item_id = msi.inventory_item_id
AND bet.plan_level = 1
AND bet.GROUP_ID =
' || l_seq_sim || '
AND msi.organization_id = 103
AND bet.top_item_id = bw.item_id
ORDER BY bet.sort_order) bet1
, -- level 2 --
(SELECT '' '' abom2
,SUBSTR (
bet.sort_order
,1
,8
)
ss2
,sort_order so2
,plan_level pl2
,component_item_id cid2
,segment1 cin2
,msi.item_type it2
,component_quantity cq2
,component_yield_factor cyf2
,extended_quantity
ecq2
FROM TEMP11 bet
,mtl_system_items msi
WHERE bet.component_item_id = msi.inventory_item_id
AND bet.plan_level = 2
AND bet.GROUP_ID =
' || l_seq_sim || '
AND msi.organization_id = 103
ORDER BY bet.sort_order) bet2
,
--Level 3 --
(SELECT '' '' abom3
,SUBSTR (
bet.sort_order
,1
,12
)
ss3
,sort_order so3
,plan_level pl3
,component_item_id cid3
,segment1 cin3
,msi.item_type it3
,component_quantity cq3
,component_yield_factor cyf3
,extended_quantity
ecq3
FROM TEMP11 bet
,mtl_system_items msi
WHERE bet.component_item_id = msi.inventory_item_id
AND bet.plan_level = 3
AND bet.GROUP_ID =
' || l_seq_sim || '
AND msi.organization_id = 103
ORDER BY bet.sort_order) bet3
,
-- level 4 --
(SELECT '' '' abom4
,SUBSTR (
bet.sort_order
,1
,16
)
ss4
,sort_order so4
,plan_level pl4
,component_item_id cid4
,segment1 cin4
,msi.item_type it4
,component_quantity cq4
,component_yield_factor cyf4
,extended_quantity
ecq4
FROM TEMP11 bet
,mtl_system_items msi
WHERE bet.component_item_id = msi.inventory_item_id
AND bet.plan_level = 4
AND bet.GROUP_ID =
' || l_seq_sim || '
AND msi.organization_id = 103
ORDER BY bet.sort_order) bet4
--
WHERE so0 = ss1(+) AND so1 = ss2(+) AND so2 = ss3(+) AND so3 = ss4(+)
ORDER BY bet.so0';
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Error during CREATION table TEMP' || SQLERRM || SQLCODE
);
END;
DBMS_OUTPUT.put_line ('TEMP TABLE SUCCESSFULLY CREATED');
END;