Hi all
I dont know where was the mistake, getting column ambiguously defined error
CREATE OR REPLACE PROCEDURE FLXUSER.testgg
AS
BEGIN
/*----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------*/
BEGIN
DECLARE
v_CntTextIdNotProcessed NUMBER:=0;
BEGIN
SELECT COUNT(1) INTO v_CntTextIdNotProcessed FROM COB_T_LT_TEXTID_SO_RECOMPILE WHERE IsProcessed = 0;
IF (v_CntTextIdNotProcessed > 0) THEN
INSERT INTO COB_T_LANG_TRANS_OPERATION
(
pt.e,pt.f,pt.g,
ISPROCESSED,
CREATEDON,
CREATEDBY
)
SELECT pt.e,pt.f,pt.g,
0,
cob_f_getutc,
'COB_WP_INS_LANG_TRANS_SO'
From
(
with st as (select textid from Text_Translation TT
where TT.Active =1
AND (TT.Short <> 'Null'
OR TT.Medium <> 'Null'
OR TT.Extended <> 'Null')),
pt as ( select osf.functionid a,osf.active b,os.active c,op.revisionstatusid d,op.id e,op.operationcode f,op.operationrevision g,op.active h,osf.operationstepid i,os.id j
from OPERATION OP
INNER JOIN OPERATION_STEP OS ON Op.Id = OS.OperationId
INNER JOIN OPERATION_STEP_FUNCTION OSF ON OS.Id = OSF.OperationStepId)
SELECT pt.e,pt.f,pt.g
FROM Function_ Fn
INNER JOIN Function_Message Ft
ON Ft.Functionid = Fn.Id
AND Ft.Active =1
AND Fn.Active =1
INNER JOIN st ON st.Textid = Ft.Textid
INNER JOIN Function_Type Fnty
ON Fn.Functiontype=Fnty.Functiontype
AND Fnty.Active =1
INNER JOIN pt
ON pt.a=Fn.Id
AND pt.b =1
AND pt.c=1
AND pt.h =1
And pt.d In (1,4)
AND TO_CHAR(pt.e) IN (SELECT DISTINCT TO_CHAR(SUBSTR(FPV.PropertyValue,INSTR(FPV.PropertyValue, '<PropertyBagItem Key="OperationID"><Value xsi:type="xsd:int">')+61,9))
FROM pt
INNER JOIN FUNCTION_ F ON pt.a = F.id
AND F.FunctionType = 21
INNER JOIN FUNCTION_PROPERTY_VALUE FPV ON FPV.Functionid = F.Id
WHERE UPPER(pt.g) = 'WRAPPER')
WHERE st.TextID IN (SELECT TextId FROM COB_T_LT_TEXTID_SO_RECOMPILE WHERE IsProcessed = 0)
UNION
SELECT pt.e,pt.f,pt.g
FROM Function_Output Fo
INNER JOIN Function_Output_Routing Forout
ON Forout.Functionoutputid = Fo.Id
INNER JOIN st
ON st.Textid = Forout.PromptTextid
INNER JOIN Function_Output_Routing_Type Fort
ON Fort.Id=Forout.Outputroutingtypeid
INNER JOIN Text_Translation Ttdt
ON Ttdt.Textid = Fort.Textid
AND Ttdt.Languageid =1033
AND Upper(Ttdt.Extended) = 'USER'
INNER JOIN Function_ Fn
ON Fn.Id = Fo.Functionid
INNER JOIN Function_Type Fnty
ON Fn.Functiontype=Fnty.Functiontype
INNER JOIN pt
ON (pt.a=Fn.Id)
And pt.d In (1,4)
AND to_char(pt.e) IN (SELECT DISTINCT TO_CHAR(SUBSTR(FPV.PropertyValue,INSTR(FPV.PropertyValue, '<PropertyBagItem Key="OperationID"><Value xsi:type="xsd:int">')+61,9))
FROM pt
INNER JOIN FUNCTION_ F ON pt.a = F.id
AND F.FunctionType = 21
INNER JOIN FUNCTION_PROPERTY_VALUE FPV ON FPV.Functionid = F.Id
WHERE UPPER(pt.g) = 'WRAPPER')
WHERE st.TextID IN (SELECT TextId FROM COB_T_LT_TEXTID_SO_RECOMPILE WHERE IsProcessed = 0)
UNION
SELECT pt.e,pt.f,pt.g
FROM Function_Input Fi
INNER JOIN st
ON st.Textid = Fi.Prompttextid
INNER JOIN Function_Input_Source_Type Fist
ON Fist.Id = Fi.Inputsourcetypeid
AND Fist.Active=1
INNER JOIN Text_Translation Ttdt
ON Ttdt.Textid = Fist.Textid
AND Ttdt.Active =1
AND Ttdt.Languageid = 1033
AND Upper(Ttdt.Extended) = 'USER'
INNER JOIN Function_ Fn
ON Fn.Id = Fi.Functionid
AND Fn.Active=1
INNER JOIN Function_Type Fnty
ON Fn.Functiontype=Fnty.Functiontype
AND Fnty.Active =1
INNER JOIN pt
ON pt.a=Fn.Id
AND pt.b =1
INNER JOIN pt
ON pt.j = pt.i
AND pt.c=1
AND pt.h =1
And pt.d In (1,4)
AND TO_CHAR(op.id) IN (SELECT DISTINCT TO_CHAR(SUBSTR(FPV.PropertyValue,INSTR(FPV.PropertyValue, '<PropertyBagItem Key="OperationID"><Value xsi:type="xsd:int">')+61,9))
FROM pt
INNER JOIN FUNCTION_ F ON pt.a = F.id
AND F.FunctionType = 21
INNER JOIN FUNCTION_PROPERTY_VALUE FPV ON FPV.Functionid = F.Id
WHERE UPPER(pt.g) = 'WRAPPER' )
where (st.textid IN (SELECT TextId FROM COB_T_LT_TEXTID_SO_RECOMPILE WHERE IsProcessed = 0) )
MINUS
( SELECT OperationId ,OperationCode,OperationRevision From COB_T_LANG_TRANS_OPERATION WHERE IsProcessed = 0 ) );
DELETE FROM COB_T_LT_TEXTID_SO_RECOMPILE where IsProcessed = 1;
END if;
END;
END;
END;
/
Edited by: 964145 on Oct 30, 2012 10:05 AM