--5.0.1.75 --(17.02.2017)v 1.0 добавлена поддержка USENEWJOIN в RpTOReg00 (20160330102637) для работы в версии 5.0.1.75 create or replace package Pk_Tbto IS TYPE ResCursor IS REF CURSOR; sTable1 VARCHAR2(100); sTable2 VARCHAR2(100); sTable2b VARCHAR2(100); sTable3 VARCHAR2(100); sTable4 VARCHAR2(100); sTable5 VARCHAR2(100); sTable5b VARCHAR2(100); PROCEDURE AddToExpr (pExpr0 IN OUT LONG, pExpr1 LONG, pDelimiter VARCHAR2); FUNCTION GetJoinField (pFieldName VARCHAR2, pMode INTEGER) RETURN VARCHAR2; FUNCTION GetJoinFieldList (pCols VARCHAR2, pDelimiter VARCHAR2, pMode INTEGER) RETURN VARCHAR2; FUNCTION GetBalanceTable (pDate DATE, pCols VARCHAR2, pCont CHAR DEFAULT ' ', pCont1 CHAR DEFAULT ' ', pSC CHAR DEFAULT ' ', pDep CHAR DEFAULT ' ', pSC1 CHAR DEFAULT ' ', pNRDOC CHAR DEFAULT ' ', pNrCM CHAR DEFAULT ' ' ) RETURN VARCHAR2; FUNCTION RpTOReg01 (pDate DATE, pCols VARCHAR2, pCont CHAR DEFAULT ' ', pCont1 CHAR DEFAULT ' ', pSC CHAR DEFAULT ' ', pDep CHAR DEFAULT ' ', pSC1 CHAR DEFAULT ' ', pNRDOC CHAR DEFAULT ' ', pNrCM CHAR DEFAULT ' ', pGr1TableName VARCHAR2 DEFAULT ' ', pGr1NameField VARCHAR2 DEFAULT ' ', pGr1IDField VARCHAR2 DEFAULT ' ', pGr1AAField VARCHAR2 DEFAULT ' ', pGr2TableName VARCHAR2 DEFAULT ' ', pGr2NameField VARCHAR2 DEFAULT ' ', pGr2IDField VARCHAR2 DEFAULT ' ', pGr2AAField VARCHAR2 DEFAULT ' ', pGr3TableName VARCHAR2 DEFAULT ' ', pGr3NameField VARCHAR2 DEFAULT ' ', pGr3IDField VARCHAR2 DEFAULT ' ', pGr3AAField VARCHAR2 DEFAULT ' ', pGrStrSCFilter VARCHAR, pGrStrSw NUMBER, pGrStrList VARCHAR, pGrStrList2 VARCHAR ) RETURN LONG; FUNCTION RpTOReg02 (pDateS DATE, pDateE DATE, pCols VARCHAR2, pCont CHAR DEFAULT ' ', pCont1 CHAR DEFAULT ' ', pSC CHAR DEFAULT ' ', pDep CHAR DEFAULT ' ', pSC1 CHAR DEFAULT ' ', pNRDOC CHAR DEFAULT ' ', pNrCM CHAR DEFAULT ' ', pCCont CHAR DEFAULT ' ', pCCont1 CHAR DEFAULT ' ', pCSC CHAR DEFAULT ' ', pCDep CHAR DEFAULT ' ', pCSC1 CHAR DEFAULT ' ', pGr1TableName VARCHAR2 DEFAULT NULL, pGr1NameField VARCHAR2 DEFAULT NULL, pGr1IDField VARCHAR2 DEFAULT NULL, pGr1AAField VARCHAR2 DEFAULT NULL, pGr2TableName VARCHAR2 DEFAULT NULL, pGr2NameField VARCHAR2 DEFAULT NULL, pGr2IDField VARCHAR2 DEFAULT NULL, pGr2AAField VARCHAR2 DEFAULT NULL, pGr3TableName VARCHAR2 DEFAULT NULL, pGr3NameField VARCHAR2 DEFAULT NULL, pGr3IDField VARCHAR2 DEFAULT NULL, pGr3AAField VARCHAR2 DEFAULT NULL, pGrStrSCFilter VARCHAR, pGrStrSw NUMBER, pGrStrList VARCHAR, pGrStrList2 VARCHAR ) RETURN LONG; FUNCTION RpTOReg00 (DateS VARCHAR2, DateE VARCHAR2, ppCols VARCHAR2, ppCols0 VARCHAR2, pCont CHAR DEFAULT ' ', pCont0 CHAR DEFAULT ' ', pCont1 CHAR DEFAULT ' ', pSC CHAR DEFAULT ' ', pDep CHAR DEFAULT ' ', pSC1 CHAR DEFAULT ' ', pNRDOC CHAR DEFAULT ' ', pNrCM CHAR DEFAULT ' ', pCCont CHAR DEFAULT ' ', pCCont1 CHAR DEFAULT ' ', pCSC CHAR DEFAULT ' ', pCDep CHAR DEFAULT ' ', pCSC1 CHAR DEFAULT ' ', pGr1TableName VARCHAR2 DEFAULT NULL, pGr1NameField VARCHAR2 DEFAULT NULL, pGr1IDField VARCHAR2 DEFAULT NULL, pGr1AAField VARCHAR2 DEFAULT NULL, pGr2TableName VARCHAR2 DEFAULT NULL, pGr2NameField VARCHAR2 DEFAULT NULL, pGr2IDField VARCHAR2 DEFAULT NULL, pGr2AAField VARCHAR2 DEFAULT NULL, pGr3TableName VARCHAR2 DEFAULT NULL, pGr3NameField VARCHAR2 DEFAULT NULL, pGr3IDField VARCHAR2 DEFAULT NULL, pGr3AAField VARCHAR2 DEFAULT NULL, pGrStrSCFilter VARCHAR, pGrStrSw NUMBER, pGrStrList VARCHAR, pGrStrList2 VARCHAR ) RETURN ResCursor; FUNCTION GetTable1SQL (pCols VARCHAR2, pFrom1 VARCHAR2, pFrom2 VARCHAR2, pFieldName VARCHAR2) RETURN LONG; PROCEDURE DropTmpTables; PROCEDURE CreateTmpTable; PROCEDURE DeleteTable (pTableName VARCHAR2); END Pk_Tbto; / ------------------------------------- create or replace package body Pk_Tbto AS PROCEDURE AddToExpr(pExpr0 IN OUT LONG, pExpr1 LONG, pDelimiter VARCHAR2) IS Expr0 LONG; Expr1 LONG; BEGIN Expr0 := RTRIM(pExpr0, ' '); Expr1 := RTRIM(pExpr1, ' '); IF (Expr1 IS NOT NULL) THEN IF (Expr0 IS NULL) THEN pExpr0 := pExpr1; ELSE pExpr0 := pExpr0 || pDelimiter || pExpr1; END IF; END IF; END AddToExpr; --------------------------------------------------------------------------- FUNCTION GetJoinField (pFieldName VARCHAR2, pMode INTEGER) RETURN VARCHAR2 IS RetVal VARCHAR2(2000); BEGIN RetVal := ''; IF (pMode IN (0,2)) THEN RetVal := pFieldName; ELSIF (pMode=1) THEN RetVal := pFieldName||'=:'||pFieldName; END IF; RETURN RetVal; END GetJoinField; --------------------------------------------------------------------------- FUNCTION GetJoinFieldList (pCols VARCHAR2, pDelimiter VARCHAR2, pMode INTEGER) RETURN VARCHAR2 IS RetVal VARCHAR2(2000); BEGIN RetVal := ''; IF (pMode<>2 AND INSTR(pCols, ':CONT;', 1, 1)>0) THEN AddToExpr(RetVal, GetJoinField ('CONT', pMode), pDelimiter); END IF; IF (INSTR(pCols, ':CONT2;', 1, 1)>0) THEN AddToExpr(RetVal, GetJoinField ('CONT2', pMode), pDelimiter); END IF; IF (INSTR(pCols, ':CONT3;', 1, 1)>0) THEN AddToExpr(RetVal, GetJoinField ('CONT3', pMode), pDelimiter); END IF; IF (INSTR(pCols, ':CONT1;', 1, 1)>0) THEN AddToExpr(RetVal, GetJoinField ('CONT1', pMode), pDelimiter); END IF; IF (INSTR(pCols, ':SC;', 1, 1)>0) THEN AddToExpr(RetVal, GetJoinField ('SC', pMode), pDelimiter); END IF; IF (INSTR(pCols, ':DEP;', 1, 1)>0) THEN AddToExpr(RetVal, GetJoinField ('DEP', pMode), pDelimiter); END IF; IF (INSTR(pCols, ':SC1;', 1, 1)>0) THEN AddToExpr(RetVal, GetJoinField ('SC1', pMode), pDelimiter); END IF; IF (INSTR(pCols, ':NRDOC;', 1, 1)>0) THEN AddToExpr(RetVal, GetJoinField ('NRDOC', pMode), pDelimiter); END IF; IF (INSTR(pCols, ':STRSC;', 1, 1)>0) THEN AddToExpr(RetVal, GetJoinField ('STRSC', pMode), pDelimiter); END IF; IF (INSTR(pCols, ':NRCM;', 1, 1)>0) THEN AddToExpr(RetVal, GetJoinField ('NRCM', pMode), pDelimiter); END IF; IF (INSTR(pCols, ':CCY;', 1, 1)>0) THEN AddToExpr(RetVal, GetJoinField ('CCY', pMode), pDelimiter); END IF; IF (INSTR(pCols, ':GR1;', 1, 1)>0) THEN AddToExpr(RetVal, GetJoinField ('GR1', pMode), pDelimiter); END IF; IF (INSTR(pCols, ':GR2;', 1, 1)>0) THEN AddToExpr(RetVal, GetJoinField ('GR2', pMode), pDelimiter); END IF; IF (INSTR(pCols, ':GR3;', 1, 1)>0) THEN AddToExpr(RetVal, GetJoinField ('GR3', pMode), pDelimiter); END IF; IF (pMode=2) THEN IF (INSTR(pCols, ':SCNAME;', 1, 1)>0) THEN AddToExpr(RetVal, 'SCNAME, SCCODE, SCUM', ','); END IF; IF (INSTR(pCols, ':DEPNAME;', 1, 1)>0) THEN AddToExpr(RetVal, 'DEPNAME, DEPCODE', ','); END IF; IF (INSTR(pCols, ':SC1NAME;', 1, 1)>0) THEN AddToExpr(RetVal, 'SC1NAME, SC1CODE', ','); END IF; IF (INSTR(pCols, ':GR1NAME;', 1, 1)>0) THEN AddToExpr(RetVal, 'GR1NAME', ','); END IF; IF (INSTR(pCols, ':GR2NAME;', 1, 1)>0) THEN AddToExpr(RetVal, 'GR2NAME', ','); END IF; IF (INSTR(pCols, ':GR3NAME;', 1, 1)>0) THEN AddToExpr(RetVal, 'GR3NAME', ','); END IF; END IF; RETURN RetVal; END GetJoinFieldList; --------------------------------------------------------------------------- FUNCTION GetBalanceTable (pDate DATE, pCols VARCHAR2, pCont CHAR DEFAULT ' ', pCont1 CHAR DEFAULT ' ', pSC CHAR DEFAULT ' ', pDep CHAR DEFAULT ' ', pSC1 CHAR DEFAULT ' ', pNRDOC CHAR DEFAULT ' ', pNrCM CHAR DEFAULT ' ' ) RETURN VARCHAR2 IS sFieldsList VARCHAR2(2000); RetVal VARCHAR2(20); BEGIN sFieldsList := ''; IF (INSTR(pCols, ':CONT;', 1, 1)>0) THEN sFieldsList := sFieldsList || 'A'; END IF; IF (INSTR(pCols, ':CONT1;', 1, 1)>0) THEN sFieldsList := sFieldsList || 'B'; END IF; IF (INSTR(pCols, ':SC;', 1, 1)>0) THEN sFieldsList := sFieldsList || 'C'; END IF; IF (INSTR(pCols, ':DEP;', 1, 1)>0) THEN sFieldsList := sFieldsList || 'D'; END IF; IF (INSTR(pCols, ':SC1;', 1, 1)>0) THEN sFieldsList := sFieldsList || 'E'; END IF; IF (INSTR(pCols, ':NRDOC;', 1, 1)>0) THEN sFieldsList := sFieldsList || 'F'; END IF; IF (INSTR(pCols, ':STRSC;', 1, 1)>0) THEN sFieldsList := sFieldsList || 'G'; END IF; IF (INSTR(pCols, ':NRCM;', 1, 1)>0) THEN sFieldsList := sFieldsList || 'H'; END IF; sFieldsList := sFieldsList || '1'; IF (INSTR(pCols, ':CCY;', 1, 1)>0) THEN sFieldsList := sFieldsList || '3'; END IF; sFieldsList := sFieldsList || '4'; RetVal := Un$sold.GET_SOLDT(pDate, NULL, sFieldsList, pCont,pCont1,pSC,pDep,pSC1,pNRDOC,' ',pNrCM); RETURN RetVal; END GetBalanceTable; --------------------------------------------------------------------------- FUNCTION RpTOReg01 (pDate DATE, pCols VARCHAR2, pCont CHAR DEFAULT ' ', pCont1 CHAR DEFAULT ' ', pSC CHAR DEFAULT ' ', pDep CHAR DEFAULT ' ', pSC1 CHAR DEFAULT ' ', pNRDOC CHAR DEFAULT ' ', pNrCM CHAR DEFAULT ' ', pGr1TableName VARCHAR2 DEFAULT ' ', pGr1NameField VARCHAR2 DEFAULT ' ', pGr1IDField VARCHAR2 DEFAULT ' ', pGr1AAField VARCHAR2 DEFAULT ' ', pGr2TableName VARCHAR2 DEFAULT ' ', pGr2NameField VARCHAR2 DEFAULT ' ', pGr2IDField VARCHAR2 DEFAULT ' ', pGr2AAField VARCHAR2 DEFAULT ' ', pGr3TableName VARCHAR2 DEFAULT ' ', pGr3NameField VARCHAR2 DEFAULT ' ', pGr3IDField VARCHAR2 DEFAULT ' ', pGr3AAField VARCHAR2 DEFAULT ' ', pGrStrSCFilter VARCHAR, pGrStrSw NUMBER, pGrStrList VARCHAR, pGrStrList2 VARCHAR ) RETURN LONG IS SQLText LONG; SQLText0 LONG; TBLSOLD VARCHAR2(20); sFrom LONG; sWhere LONG; GrDISABLED VARCHAR2(255):=' where 1=0 '; TYPE TCur IS REF CURSOR; RetCur TCur; i NUMBER(5); j NUMBER(5); vGrList VARCHAR(1000); vGrWhere VARCHAR(2000); vAAPrefix VARCHAR(10); BEGIN TBLSOLD := GetBalanceTable (pDate, pCols, pCont, pCont1, pSC, pDep, pSC1, pNRDOC, pNrCM); sTable3 := TBLSOLD; SQLText0 := ''; IF (INSTR(pCols, ':CONT;', 1, 1)>0) THEN AddToExpr(SQLText0, 'A.CONT', ','); END IF; IF (INSTR(pCols, ':CONT1;', 1, 1)>0) THEN AddToExpr(SQLText0, 'A.CONT1', ','); END IF; IF (INSTR(pCols, ':SC;', 1, 1)>0) THEN AddToExpr(SQLText0, 'A.SC', ','); END IF; IF (INSTR(pCols, ':DEP;', 1, 1)>0) THEN AddToExpr(SQLText0, 'A.DEP', ','); END IF; IF (INSTR(pCols, ':SC1', 1, 1)>0) THEN AddToExpr(SQLText0, 'A.SC1', ','); END IF; IF (INSTR(pCols, ':NRDOC;', 1, 1)>0) THEN AddToExpr(SQLText0, 'A.NRDOC', ','); END IF; IF (INSTR(pCols, ':STRSC', 1, 1)>0) THEN AddToExpr(SQLText0, 'A.STRSC', ','); END IF; IF (INSTR(pCols, ':NRCM', 1, 1)>0) THEN AddToExpr(SQLText0, 'A.NRCM', ','); END IF; IF (INSTR(pCols, ':CCY', 1, 1)>0) THEN AddToExpr(SQLText0, 'A.VALUTA AS CCY', ','); END IF; IF (INSTR(pCols, ':SCNAME;', 1, 1)>0) THEN AddToExpr(SQLText0, 'U1.DENUMIREA SCNAME, U1.CODVECHI SCCODE, U1.NAMERUS AS SCNAME2, U1.UM SCUM', ','); END IF; IF (INSTR(pCols, ':DEPNAME;', 1, 1)>0) THEN AddToExpr(SQLText0, 'U2.DENUMIREA DEPNAME, U2.CODVECHI DEPCODE, U2.NAMERUS AS DEPNAME2', ','); END IF; IF (INSTR(pCols, ':SC1NAME;', 1, 1)>0) THEN AddToExpr(SQLText0, 'U3.DENUMIREA SC1NAME, U3.CODVECHI SC1CODE, U3.NAMERUS AS SC1NAME2', ','); END IF; IF (INSTR(pCols, ':GR1SC;', 1, 1)>0 OR INSTR(pCols, ':GR2SC;', 1, 1)>0 OR INSTR(pCols, ':GR3SC;', 1, 1)>0 OR INSTR(pCols, ':GR4SC;', 1, 1)>0 OR INSTR(pCols, ':GR5SC;', 1, 1)>0 ) THEN GrDISABLED:=' '; AddToExpr(SQLText0, 'G1.GR1 AS GR1SC, G1.GR2 AS GR2SC, G1.GR3 AS GR3SC, G1.GR4 AS GR4SC, G1.GR5 AS GR5SC', ','); END IF; AddToExpr(SQLText0, 'SUMA,CANT,SUMAGAAP', ','); --AddToExpr(SQLText0, 'CANT', ','); --AddToExpr(SQLText0, 'PRICE', ','); IF (INSTR(pCols, ':CCY', 1, 1)>0) THEN AddToExpr(SQLText0, 'SUMAVAL', ','); ELSE AddToExpr(SQLText0, '0.0 SUMAVAL', ','); END IF; --AddToExpr(SQLText0, 'SUMAGAAP', ','); sFrom := '(SELECT * FROM ' || TBLSOLD || ') A'; IF (INSTR(pCols, ':SCNAME;', 1, 1)>0) THEN AddToExpr(sFrom, 'VMS_UNIVERS U1', ','); END IF; IF (INSTR(pCols, ':DEPNAME;', 1, 1)>0) THEN AddToExpr(sFrom, 'VMS_UNIVERS U2', ','); END IF; IF (INSTR(pCols, ':SC1NAME;', 1, 1)>0) THEN AddToExpr(sFrom, 'VMS_UNIVERS U3', ','); END IF; IF (INSTR(pCols, ':GR1SC;', 1, 1)>0 OR INSTR(pCols, ':GR2SC;', 1, 1)>0 OR INSTR(pCols, ':GR3SC;', 1, 1)>0 OR INSTR(pCols, ':GR4SC;', 1, 1)>0 OR INSTR(pCols, ':GR5SC;', 1, 1)>0 ) THEN AddToExpr(sFrom, '( SELECT GA.Group1 AS Gr1ID, (SELECT coment FROM VMS_SYSGRPH WHERE group1=GA.group1 AND group2=0) AS Gr1, GA.Group2 AS Gr2ID, DECODE(NVL(GA.Group2, 0), 0, '''', (SELECT coment FROM VMS_SYSGRPH WHERE group1=GA.group1 AND group2=GA.group2 AND group3=0)) AS Gr2, GA.Group3 AS Gr3ID, DECODE(NVL(GA.Group3, 0), 0, '''', (SELECT coment FROM VMS_SYSGRPH WHERE group1=GA.group1 AND group2=GA.group2 AND group3=GA.group3 AND group4=0)) AS Gr3, GA.Group4 AS Gr4ID, DECODE(NVL(GA.Group4, 0), 0, '''', (SELECT coment FROM VMS_SYSGRPH WHERE group1=GA.group1 AND group2=GA.group2 AND group3=GA.group3 AND group4=GA.group4 AND group5=0)) AS Gr4, GA.Group5 AS Gr5ID, DECODE(NVL(GA.Group5, 0), 0, '''', (SELECT coment FROM VMS_SYSGRPH WHERE group1=GA.group1 AND group2=GA.group2 AND group3=GA.group3 AND group4=GA.group4 AND group5=GA.group5)) AS Gr5, GA.SC FROM VMS_SYSGRP GA '||GrDISABLED||') G1', ','); END IF; sWhere := ''; IF (INSTR(pCols, ':SCNAME;', 1, 1)>0) THEN AddToExpr(sWhere, 'A.SC=U1.COD(+)', ' AND '); END IF; IF (INSTR(pCols, ':DEPNAME;', 1, 1)>0) THEN AddToExpr(sWhere, 'A.DEP=U2.COD(+)', ' AND '); END IF; IF (INSTR(pCols, ':SC1NAME;', 1, 1)>0) THEN AddToExpr(sWhere, 'A.SC1=U3.COD(+)', ' AND '); END IF; IF (INSTR(pCols, ':GR1SC;', 1, 1)>0 OR INSTR(pCols, ':GR2SC;', 1, 1)>0 OR INSTR(pCols, ':GR3SC;', 1, 1)>0 OR INSTR(pCols, ':GR4SC;', 1, 1)>0 OR INSTR(pCols, ':GR5SC;', 1, 1)>0 ) THEN AddToExpr(sWhere, 'A.SC=G1.SC(+)', ' AND '); END IF; SQLText := 'SELECT ' || SQLText0 || ' FROM ' || sFrom; AddToExpr(SQLText, sWhere, ' WHERE '); -- Groups SQLText0 := ' '; sWhere := ''; sFrom := '(' || SQLText || ') Q1'; vAAPrefix := 'SC'; IF (pGrStrSw=1) THEN vGrList := ' '; /* i := 1; j := pGrStrS; WHILE (j<=pGrStrE) LOOP AddToExpr(vGrList, 'G1.GR'||i||'NAME AS GR'||i||vAAPrefix||'NAME, CAST(G1.GR'||i||' AS NUMBER) AS GR'||i||vAAPrefix, ','); i := i + 1; j := j + 1; END LOOP; AddToExpr(SQLText0, vGrList, ',');*/ AddToExpr(SQLText0, pGrStrList, ','); vGrList := 'SELECT T.ID_UNIV AS COD'; /* i := 1; j := pGrStrS; WHILE (j<=pGrStrE) LOOP AddToExpr(vGrList, 'T.ID_ITEM'||j||' AS gr'||i||', (SELECT ItemName FROM vms_strgrpl WHERE ID=T.ID_ITEM'||j||') AS GR'||i||'NAME', ','); i := i + 1; j := j + 1; END LOOP; */ AddToExpr(vGrList, pGrStrList2, ','); vGrWhere := pGrStrSCFilter; IF (vGrWhere != ' ') THEN vGrWhere := ' WHERE ' || vGrWhere; END IF; AddToExpr(sFrom, '('||vGrList||' FROM vms_strgrpuniv_arr T '||vGrWhere||' ) G1', ','); AddToExpr(sWhere, 'Q1.'||vAAPrefix||'=G1.COD', ' AND '); END IF; IF (NVL(pGr1TableName, ' ')!=' ') THEN AddToExpr(SQLText0, 'S1.'||pGr1NameField||' AS GR1NAME, CAST(S1.'||pGr1IDField||' AS NUMBER) AS GR1', ','); END IF; IF (NVL(pGr2TableName, ' ')!=' ') THEN AddToExpr(SQLText0, 'S2.'||pGr2NameField||' AS GR2NAME, CAST(S2.'||pGr2IDField||' AS NUMBER) AS GR2', ','); END IF; IF (NVL(pGr3TableName, ' ')!=' ') THEN AddToExpr(SQLText0, 'S3.'||pGr3NameField||' AS GR3NAME, CAST(S3.'||pGr3IDField||' AS NUMBER) AS GR3', ','); END IF; IF (NVL(pGr1TableName, ' ')!=' ') THEN AddToExpr(sFrom, pGr1TableName||' S1', ','); END IF; IF (NVL(pGr2TableName, ' ')!=' ') THEN AddToExpr(sFrom, pGr2TableName||' S2', ','); END IF; IF (NVL(pGr3TableName, ' ')!=' ') THEN AddToExpr(sFrom, pGr3TableName||' S3', ','); END IF; IF (NVL(pGr1TableName, ' ')!=' ') THEN AddToExpr(sWhere, 'Q1.'||pGr1AAField||'=S1.COD(+)', ' AND '); END IF; IF (NVL(pGr2TableName, ' ')!=' ') THEN AddToExpr(sWhere, 'Q1.'||pGr2AAField||'=S2.COD(+)', ' AND '); END IF; IF (NVL(pGr3TableName, ' ')!=' ') THEN AddToExpr(sWhere, 'Q1.'||pGr3AAField||'=S3.COD(+)', ' AND '); END IF; IF (NVL(SQLText0, ' ')!=' ') THEN SQLText := 'SELECT Q1.*, ' || SQLText0 || ' FROM ' || sFrom || ' WHERE ' || sWhere; END IF; --Imt(SQLText,re=>true); --OPEN RetCur FOR SQLText; --RETURN RetCur; RETURN SQLText; END RpTOReg01; --------------------------------------------------------------------------- FUNCTION RpTOReg02 (pDateS DATE, pDateE DATE, pCols VARCHAR2, pCont CHAR DEFAULT ' ', pCont1 CHAR DEFAULT ' ', pSC CHAR DEFAULT ' ', pDep CHAR DEFAULT ' ', pSC1 CHAR DEFAULT ' ', pNRDOC CHAR DEFAULT ' ', pNrCM CHAR DEFAULT ' ', pCCont CHAR DEFAULT ' ', pCCont1 CHAR DEFAULT ' ', pCSC CHAR DEFAULT ' ', pCDep CHAR DEFAULT ' ', pCSC1 CHAR DEFAULT ' ', pGr1TableName VARCHAR2 DEFAULT NULL, pGr1NameField VARCHAR2 DEFAULT NULL, pGr1IDField VARCHAR2 DEFAULT NULL, pGr1AAField VARCHAR2 DEFAULT NULL, pGr2TableName VARCHAR2 DEFAULT NULL, pGr2NameField VARCHAR2 DEFAULT NULL, pGr2IDField VARCHAR2 DEFAULT NULL, pGr2AAField VARCHAR2 DEFAULT NULL, pGr3TableName VARCHAR2 DEFAULT NULL, pGr3NameField VARCHAR2 DEFAULT NULL, pGr3IDField VARCHAR2 DEFAULT NULL, pGr3AAField VARCHAR2 DEFAULT NULL, pGrStrSCFilter VARCHAR, pGrStrSw NUMBER, pGrStrList VARCHAR, pGrStrList2 VARCHAR ) RETURN LONG IS SQLText LONG; SQLText0 LONG; SQLTextA LONG; SQLTextB LONG; SQLTextC LONG; sGroupBy VARCHAR2(4000); sWhere LONG; sFrom LONG; TYPE TCur IS REF CURSOR; RetCur TCur; GrDISABLED VARCHAR2(255):=' where 1=0 '; i NUMBER(5); j NUMBER(5); vGrList VARCHAR(1000); vGrWhere VARCHAR(2000); vAAPrefix VARCHAR(10); Zzz VARCHAR(2000); svmdb_cmA LONG; svmdb_cmB LONG; BEGIN SQLTextA := ''; AddToExpr(SQLTextA, 'DT AS CONT', ','); AddToExpr(SQLTextA, 'DT1 AS CONT1', ','); AddToExpr(SQLTextA, 'DTSC AS SC', ','); AddToExpr(SQLTextA, 'DTDEP AS DEP', ','); AddToExpr(SQLTextA, 'DTSC1 AS SC1', ','); AddToExpr(SQLTextA, 'DTNRDOC AS NRDOC', ','); AddToExpr(SQLTextA, 'DTSTRSC AS STRSC', ','); AddToExpr(SQLTextA, 'DTNRCM AS NRCM', ','); AddToExpr(SQLTextA, 'VALUTADT AS CCY', ','); AddToExpr(SQLTextA, 'GR1SC', ','); AddToExpr(SQLTextA, 'GR2SC', ','); AddToExpr(SQLTextA, 'GR3SC', ','); AddToExpr(SQLTextA, 'GR4SC', ','); AddToExpr(SQLTextA, 'GR5SC', ','); AddToExpr(SQLTextA, 'CT AS CCONT', ','); AddToExpr(SQLTextA, '(SELECT ATTR1 FROM VMS_PDC WHERE CONT=CT) AS CCONT2', ','); AddToExpr(SQLTextA, '(SELECT ATTR2 FROM VMS_PDC WHERE CONT=CT) AS CCONT3', ','); AddToExpr(SQLTextA, 'CT1 AS CCONT1', ','); AddToExpr(SQLTextA, 'CTSC AS CSC', ','); AddToExpr(SQLTextA, 'CTDEP AS CDEP', ','); AddToExpr(SQLTextA, 'CTSC1 AS CSC1', ','); AddToExpr(SQLTextA, 'CTNRDOC AS CNRDOC', ','); AddToExpr(SQLTextA, 'CTSTRSC AS CSTRSC', ','); AddToExpr(SQLTextA, 'CTNRCM AS CNRCM', ','); AddToExpr(SQLTextA, 'VALUTACT AS CCCY', ','); AddToExpr(SQLTextA, 'NRDOC AS DOCID', ','); AddToExpr(SQLTextA, 'DATA', ','); AddToExpr(SQLTextA, 'EXTRACT(DAY FROM DATA) AS ZIUA', ','); AddToExpr(SQLTextA, 'EXTRACT(MONTH FROM DATA) AS LUNA', ','); AddToExpr(SQLTextA, 'EXTRACT(YEAR FROM DATA) AS ANUL', ','); --AddToExpr(SQLTextA, 'DTNRCM AS NRCM', ','); AddToExpr(SQLTextA, ' 0.0 AS SUMADTS, 0.0 AS CANTDTS, 0.0 AS SUMAVDTS , 0.0 AS SUMACTS, 0.0 AS CANTCTS, 0.0 AS SUMAVCTS , SUMA AS SUMADTC, CANT AS CANTDTC, SUMAVALDT AS SUMAVDTC , 0.0 AS SUMACTC, 0.0 AS CANTCTC, 0.0 AS SUMAVCTC , 0.0 AS SUMAVALDTI, SUMAVALCT AS SUMAVALCTI , 0.0 AS SUMADTE, 0.0 AS CANTDTE, 0.0 AS SUMAVDTE , 0.0 AS SUMACTE, 0.0 AS CANTCTE, 0.0 AS SUMAVCTE ', ','); SQLTextB := ''; AddToExpr(SQLTextB, 'CT AS CONT', ','); AddToExpr(SQLTextB, 'CT1 AS CONT1', ','); AddToExpr(SQLTextB, 'CTSC AS SC', ','); AddToExpr(SQLTextB, 'CTDEP AS DEP', ','); AddToExpr(SQLTextB, 'CTSC1 AS SC1', ','); AddToExpr(SQLTextB, 'CTNRDOC AS NRDOC', ','); AddToExpr(SQLTextB, 'CTSTRSC AS STRSC', ','); AddToExpr(SQLTextB, 'CTNRCM AS NRCM', ','); AddToExpr(SQLTextB, 'VALUTACT AS CCY', ','); AddToExpr(SQLTextB, 'GR1SC', ','); AddToExpr(SQLTextB, 'GR2SC', ','); AddToExpr(SQLTextB, 'GR3SC', ','); AddToExpr(SQLTextB, 'GR4SC', ','); AddToExpr(SQLTextB, 'GR5SC', ','); AddToExpr(SQLTextB, 'DT AS CCONT', ','); AddToExpr(SQLTextB, '(SELECT ATTR1 FROM VMS_PDC WHERE CONT=DT) AS CCONT2', ','); AddToExpr(SQLTextB, '(SELECT ATTR2 FROM VMS_PDC WHERE CONT=DT) AS CCONT3', ','); AddToExpr(SQLTextB, 'DT1 AS CCONT1', ','); AddToExpr(SQLTextB, 'DTSC AS CSC', ','); AddToExpr(SQLTextB, 'DTDEP AS CDEP', ','); AddToExpr(SQLTextB, 'DTSC1 AS CSC1', ','); AddToExpr(SQLTextB, 'DTNRDOC AS CNRDOC', ','); AddToExpr(SQLTextB, 'DTSTRSC AS CSTRSC', ','); AddToExpr(SQLTextB, 'DTNRCM AS CNRCM', ','); AddToExpr(SQLTextB, 'VALUTADT AS CCCY', ','); AddToExpr(SQLTextB, 'NRDOC AS DOCID', ','); AddToExpr(SQLTextB, 'DATA', ','); AddToExpr(SQLTextB, 'EXTRACT(DAY FROM DATA) AS ZIUA', ','); AddToExpr(SQLTextB, 'EXTRACT(MONTH FROM DATA) AS LUNA', ','); AddToExpr(SQLTextB, 'EXTRACT(YEAR FROM DATA) AS ANUL', ','); --AddToExpr(SQLTextB, 'CTNRCM AS NRCM', ','); AddToExpr(SQLTextB, ' 0.0 AS SUMADTS, 0.0 AS CANTDTS, 0.0 AS SUMAVDTS , 0.0 AS SUMACTS, 0.0 AS CANTCTS, 0.0 AS SUMAVCTS , 0.0 AS SUMADTC, 0.0 AS CANTDTC, 0.0 AS SUMAVDTC , SUMA AS SUMACTC, CANT AS CANTCTC, SUMAVALCT AS SUMAVCTC , SUMAVALDT AS SUMAVALDTI, 0.0 AS SUMAVALCTI , 0.0 AS SUMADTE, 0.0 AS CANTDTE, 0.0 AS SUMAVDTE , 0.0 AS SUMACTE, 0.0 AS CANTCTE, 0.0 AS SUMAVCTE ', ','); --, (SUMA / DECODE (CANT, 0, 1, CANT)) SQLTextC := ''; IF (INSTR(pCols, ':CONT;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'CONT', ','); END IF; IF (INSTR(pCols, ':CONT1;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'CONT1', ','); END IF; IF (INSTR(pCols, ':SC;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'SC', ','); END IF; IF (INSTR(pCols, ':DEP;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'DEP', ','); END IF; IF (INSTR(pCols, ':SC1;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'SC1', ','); END IF; IF (INSTR(pCols, ':NRDOC;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'NRDOC', ','); END IF; IF (INSTR(pCols, ':STRSC;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'STRSC', ','); END IF; IF (INSTR(pCols, ':NRCM;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'NRCM', ','); END IF; IF (INSTR(pCols, ':CCY;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'CCY', ','); END IF; IF (INSTR(pCols, ':GR1SC;', 1, 1)>0) THEN GrDISABLED:=' ';AddToExpr(SQLTextC, 'GR1SC', ','); END IF; IF (INSTR(pCols, ':GR2SC;', 1, 1)>0) THEN GrDISABLED:=' ';AddToExpr(SQLTextC, 'GR2SC', ','); END IF; IF (INSTR(pCols, ':GR3SC;', 1, 1)>0) THEN GrDISABLED:=' ';AddToExpr(SQLTextC, 'GR3SC', ','); END IF; IF (INSTR(pCols, ':GR4SC;', 1, 1)>0) THEN GrDISABLED:=' ';AddToExpr(SQLTextC, 'GR4SC', ','); END IF; IF (INSTR(pCols, ':GR5SC;', 1, 1)>0) THEN GrDISABLED:=' ';AddToExpr(SQLTextC, 'GR5SC', ','); END IF; IF (INSTR(pCols, ':CCONT;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'CCONT', ','); END IF; IF (INSTR(pCols, ':CCONT2;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'CCONT2', ','); END IF; IF (INSTR(pCols, ':CCONT3;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'CCONT3', ','); END IF; IF (INSTR(pCols, ':CCONT1;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'CCONT1', ','); END IF; IF (INSTR(pCols, ':CSC;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'CSC', ','); END IF; IF (INSTR(pCols, ':CDEP;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'CDEP', ','); END IF; IF (INSTR(pCols, ':CSC1;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'CSC1', ','); END IF; IF (INSTR(pCols, ':CNRDOC;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'CNRDOC', ','); END IF; IF (INSTR(pCols, ':CSTRSC;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'CSTRSC', ','); END IF; IF (INSTR(pCols, ':CNRCM;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'CNRCM', ','); END IF; IF (INSTR(pCols, ':CCCY;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'CCCY', ','); END IF; IF (INSTR(pCols, ':DOCID;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'DOCID', ','); END IF; IF (INSTR(pCols, ':DATA;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'DATA', ','); END IF; IF (INSTR(pCols, ':ZIUA;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'ZIUA', ','); END IF; IF (INSTR(pCols, ':LUNA;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'LUNA', ','); END IF; IF (INSTR(pCols, ':ANUL;', 1, 1)>0) THEN AddToExpr(SQLTextC, 'ANUL', ','); END IF; --if (INSTR(pCols, 'NRCM', 1, 1)>0) then AddToExpr(SQLTextC, 'NRCM', ','); end if; --sVMDB_CM := '(SELECT * FROM VMDB_CMR)'; svmdb_cmA := ' (SELECT G.Gr1 AS GR1SC, G.Gr2 AS GR2SC, G.Gr3 AS GR3SC, G.Gr4 AS GR4SC, G.Gr5 AS GR5SC, C.* FROM VMDB_CMR C, ( SELECT GA.Group1 AS Gr1ID, (SELECT coment FROM VMS_SYSGRPH WHERE group1=GA.group1 AND group2=0) AS Gr1, GA.Group2 AS Gr2ID, DECODE(NVL(GA.Group2, 0), 0, '''', (SELECT coment FROM VMS_SYSGRPH WHERE group1=GA.group1 AND group2=GA.group2 AND group3=0)) AS Gr2, GA.Group3 AS Gr3ID, DECODE(NVL(GA.Group3, 0), 0, '''', (SELECT coment FROM VMS_SYSGRPH WHERE group1=GA.group1 AND group2=GA.group2 AND group3=GA.group3 AND group4=0)) AS Gr3, GA.Group4 AS Gr4ID, DECODE(NVL(GA.Group4, 0), 0, '''', (SELECT coment FROM VMS_SYSGRPH WHERE group1=GA.group1 AND group2=GA.group2 AND group3=GA.group3 AND group4=GA.group4 AND group5=0)) AS Gr4, GA.Group5 AS Gr5ID, DECODE(NVL(GA.Group5, 0), 0, '''', (SELECT coment FROM VMS_SYSGRPH WHERE group1=GA.group1 AND group2=GA.group2 AND group3=GA.group3 AND group4=GA.group4 AND group5=GA.group5)) AS Gr5, GA.SC FROM VMS_SYSGRP GA '||GrDISABLED||' ) G WHERE C.dtsc=G.SC(+))'; svmdb_cmB := ' (SELECT G.Gr1 AS GR1SC, G.Gr2 AS GR2SC, G.Gr3 AS GR3SC, G.Gr4 AS GR4SC, G.Gr5 AS GR5SC, C.* FROM VMDB_CMR C, ( SELECT GA.Group1 AS Gr1ID, (SELECT coment FROM VMS_SYSGRPH WHERE group1=GA.group1 AND group2=0) AS Gr1, GA.Group2 AS Gr2ID, DECODE(NVL(GA.Group2, 0), 0, '''', (SELECT coment FROM VMS_SYSGRPH WHERE group1=GA.group1 AND group2=GA.group2 AND group3=0)) AS Gr2, GA.Group3 AS Gr3ID, DECODE(NVL(GA.Group3, 0), 0, '''', (SELECT coment FROM VMS_SYSGRPH WHERE group1=GA.group1 AND group2=GA.group2 AND group3=GA.group3 AND group4=0)) AS Gr3, GA.Group4 AS Gr4ID, DECODE(NVL(GA.Group4, 0), 0, '''', (SELECT coment FROM VMS_SYSGRPH WHERE group1=GA.group1 AND group2=GA.group2 AND group3=GA.group3 AND group4=GA.group4 AND group5=0)) AS Gr4, GA.Group5 AS Gr5ID, DECODE(NVL(GA.Group5, 0), 0, '''', (SELECT coment FROM VMS_SYSGRPH WHERE group1=GA.group1 AND group2=GA.group2 AND group3=GA.group3 AND group4=GA.group4 AND group5=GA.group5)) AS Gr5, GA.SC FROM VMS_SYSGRP GA '||GrDISABLED||' ) G WHERE C.ctsc=G.SC(+))'; say('sGroupBy '||SQLTextC); sGroupBy := SQLTextC; SQLText := SQLTextC; AddToExpr(SQLTextC, ' SUM(SUMADTS) AS SUMADTS , SUM(CANTDTS) AS CANTDTS , SUM(SUMAVDTS) AS SUMAVDTS , SUM(SUMACTS) AS SUMACTS , SUM(CANTCTS) AS CANTCTS , SUM(SUMAVCTS) AS SUMAVCTS , SUM(SUMADTC) AS SUMADTC , SUM(CANTDTC) AS CANTDTC , SUM(SUMAVDTC) AS SUMAVDTC , SUM(SUMAVALDTI) AS SUMAVALDTI , SUM(SUMACTC) AS SUMACTC , SUM(CANTCTC) AS CANTCTC , SUM(SUMAVCTC) AS SUMAVCTC , SUM(SUMAVALCTI) AS SUMAVALCTI , SUM(SUMADTE) AS SUMADTE , SUM(CANTDTE) AS CANTDTE , SUM(SUMAVDTE) AS SUMAVDTE , SUM(SUMACTE) AS SUMACTE , SUM(CANTCTE) AS CANTCTE , SUM(SUMAVCTE) AS SUMAVCTE ', ','); SQLTextC := 'SELECT ' || SQLTextC || ' FROM (SELECT ' || SQLTextA || ' FROM '||svmdb_cmA||' UNION ALL SELECT ' || SQLTextB || ' FROM '||svmdb_cmB||') C'; sWhere := ''; AddToExpr(sWhere, '(DATA BETWEEN '||Pkg_Afx.DateToStr(pDateS)||' AND '||Pkg_Afx.DateToStr(pDateE)||')', ' AND '); IF (pCont<>' ' AND pCont IS NOT NULL) THEN AddToExpr(sWhere, pCont, ' AND '); END IF; IF (pCont1<>' ' AND pCont1 IS NOT NULL) THEN AddToExpr(sWhere, 'CONT1='||pCont1, ' AND '); END IF; IF (pSC<>' ' AND pSC IS NOT NULL) THEN AddToExpr(sWhere, 'SC='||pSC, ' AND '); END IF; IF (pDep<>' ' AND pDep IS NOT NULL) THEN AddToExpr(sWhere, 'DEP='||pDep, ' AND '); END IF; IF (pSC1<>' ' AND pSC1 IS NOT NULL) THEN AddToExpr(sWhere, 'SC1='||pSC1, ' AND '); END IF; IF (pNRDOC<>' ' AND pNRDOC IS NOT NULL) THEN AddToExpr(sWhere, 'NRDOC='||pNRDOC, ' AND '); END IF; IF (pNrCM<>' ' AND pNrCM IS NOT NULL) THEN AddToExpr(sWhere, 'NRCM='||pNrCM, ' AND '); END IF; IF (pCCont<>' ' AND pCCont IS NOT NULL) THEN AddToExpr(sWhere, pCCont, ' AND '); END IF; IF (pCCont1<>' ' AND pCCont1 IS NOT NULL) THEN AddToExpr(sWhere, 'CCONT1='||pCCont1, ' AND '); END IF; IF (pCSC<>' ' AND pCSC IS NOT NULL) THEN AddToExpr(sWhere, 'CSC='||pCSC, ' AND '); END IF; IF (pCDep<>' ' AND pCDep IS NOT NULL) THEN AddToExpr(sWhere, 'CDEP='||pCDep, ' AND '); END IF; IF (pCSC1<>' ' AND pCSC1 IS NOT NULL) THEN AddToExpr(sWhere, 'CSC1='||pCSC1, ' AND '); END IF; AddToExpr(SQLTextC, sWhere, ' WHERE '); AddToExpr(SQLTextC, sGroupBy, ' GROUP BY '); IF (INSTR(pCols, ':SCNAME;', 1, 1)>0) THEN AddToExpr(SQLText, 'U1.DENUMIREA SCNAME, U1.CODVECHI SCCODE, U1.NAMERUS AS SCNAME2, U1.UM SCUM', ','); END IF; IF (INSTR(pCols, ':DEPNAME;', 1, 1)>0) THEN AddToExpr(SQLText, 'U2.DENUMIREA DEPNAME, U2.CODVECHI DEPCODE, U2.NAMERUS AS DEPNAME2', ','); END IF; IF (INSTR(pCols, ':SC1NAME;', 1, 1)>0) THEN AddToExpr(SQLText, 'U3.DENUMIREA SC1NAME, U3.CODVECHI SC1CODE, U3.NAMERUS AS SC1NAME2', ','); END IF; IF (INSTR(pCols, ':CSCNAME;', 1, 1)>0) THEN AddToExpr(SQLText, 'U4.DENUMIREA CSCNAME, U4.CODVECHI CSCCODE, U4.UM CSCUM, U4.NAMERUS AS CSCNAME2', ','); END IF; IF (INSTR(pCols, ':CDEPNAME;', 1, 1)>0) THEN AddToExpr(SQLText, 'U5.DENUMIREA CDEPNAME, U5.CODVECHI CDEPCODE, U5.NAMERUS AS CDEPNAME2', ','); END IF; IF (INSTR(pCols, ':CSC1NAME;', 1, 1)>0) THEN AddToExpr(SQLText, 'U6.DENUMIREA CSC1NAME, U6.CODVECHI CSC1CODE, U6.NAMERUS AS CSC1NAME2', ','); END IF; IF (INSTR(pCols, ':CONT1NAME;', 1, 1)>0) THEN AddToExpr(SQLText, 'U7.DENUMIREA CONT1NAME, U7.CODVECHI CONT1CODE, U7.NAMERUS AS CONT1NAME2', ','); END IF; IF (INSTR(pCols, ':CCONT1NAME;', 1, 1)>0) THEN AddToExpr(SQLText, 'U8.DENUMIREA CCONT1NAME, U8.CODVECHI CCONT1CODE, U8.NAMERUS AS CCONT1NAME2', ','); END IF; IF (INSTR(pCols, ':NRDOCH;', 1, 1)>0) THEN AddToExpr(SQLText, '(SELECT NRMANUAL FROM VMDB_DOCS WHERE COD=A.DOCID) AS NRDOCH', ','); END IF; AddToExpr(SQLText, ' SUMADTS, CANTDTS, SUMAVDTS, SUMACTS, CANTCTS, SUMAVCTS, SUMADTC, CANTDTC, SUMAVDTC, SUMAVALDTI AS SUMAVALCTI, SUMACTC, CANTCTC, SUMAVCTC, SUMAVALCTI AS SUMAVALDTI, SUMADTE, CANTDTE, SUMAVDTE, SUMACTE, CANTCTE, SUMAVCTE , SumaDtS / DECODE (cantDtS, 0, 1, cantDtS) AS PriceDtS , SumaCtS / DECODE (cantCtS, 0, 1, cantCtS) AS PriceCtS , SumaDtC / DECODE (cantDtC, 0, 1, cantDtC) AS PriceDtC , SumaCtC / DECODE (cantCtC, 0, 1, cantCtC) AS PriceCtC , SumaDtE / DECODE (cantDtE, 0, 1, cantDtE) AS PriceDtE , SumaCtE / DECODE (cantCtE, 0, 1, cantCtE) AS PriceCtE , SumaVDtS / DECODE (cantDtS, 0, 1, cantDtS) AS PriceVDtS , SumaVCtS / DECODE (cantCtS, 0, 1, cantCtS) AS PriceVCtS , SumaVDtC / DECODE (cantDtC, 0, 1, cantDtC) AS PriceVDtC , SumaVCtC / DECODE (cantCtC, 0, 1, cantCtC) AS PriceVCtC , SumaVDtE / DECODE (cantDtE, 0, 1, cantDtE) AS PriceVDtE , SumaVCtE / DECODE (cantCtE, 0, 1, cantCtE) AS PriceVCtE ', ','); sFrom := '(' || SQLTextC || ') A'; IF (INSTR(pCols, ':SCNAME;', 1, 1)>0) THEN AddToExpr(sFrom, 'VMS_UNIVERS U1', ','); END IF; IF (INSTR(pCols, ':DEPNAME;', 1, 1)>0) THEN AddToExpr(sFrom, 'VMS_UNIVERS U2', ','); END IF; IF (INSTR(pCols, ':SC1NAME;', 1, 1)>0) THEN AddToExpr(sFrom, 'VMS_UNIVERS U3', ','); END IF; IF (INSTR(pCols, ':CSCNAME;', 1, 1)>0) THEN AddToExpr(sFrom, 'VMS_UNIVERS U4', ','); END IF; IF (INSTR(pCols, ':CDEPNAME;', 1, 1)>0) THEN AddToExpr(sFrom, 'VMS_UNIVERS U5', ','); END IF; IF (INSTR(pCols, ':CSC1NAME;', 1, 1)>0) THEN AddToExpr(sFrom, 'VMS_UNIVERS U6', ','); END IF; IF (INSTR(pCols, ':CONT1NAME;', 1, 1)>0) THEN AddToExpr(sFrom, 'VMS_UNIVERS U7', ','); END IF; IF (INSTR(pCols, ':CCONT1NAME;', 1, 1)>0) THEN AddToExpr(sFrom, 'VMS_UNIVERS U8', ','); END IF; sWhere := ''; IF (INSTR(pCols, ':SCNAME;', 1, 1)>0) THEN AddToExpr(sWhere, 'A.SC=/*5818*/U1.COD(+)', ' AND '); END IF; IF (INSTR(pCols, ':DEPNAME;', 1, 1)>0) THEN AddToExpr(sWhere, 'A.DEP=U2.COD(+)', ' AND '); END IF; IF (INSTR(pCols, ':SC1NAME;', 1, 1)>0) THEN AddToExpr(sWhere, 'A.SC1=U3.COD(+)', ' AND '); END IF; IF (INSTR(pCols, ':CSCNAME;', 1, 1)>0) THEN AddToExpr(sWhere, 'A.CSC=U4.COD(+)', ' AND '); END IF; IF (INSTR(pCols, ':CDEPNAME;', 1, 1)>0) THEN AddToExpr(sWhere, 'A.CDEP=U5.COD(+)', ' AND '); END IF; IF (INSTR(pCols, ':CSC1NAME;', 1, 1)>0) THEN AddToExpr(sWhere, 'A.CSC1=U6.COD(+)', ' AND '); END IF; IF (INSTR(pCols, ':CONT1NAME;', 1, 1)>0) THEN AddToExpr(sWhere, 'A.CONT1=U7.COD(+)', ' AND '); END IF; IF (INSTR(pCols, ':CCONT1NAME;', 1, 1)>0) THEN AddToExpr(sWhere, 'A.CCONT1=U8.COD(+)', ' AND '); END IF; SQLText := 'SELECT ' || SQLText || ' FROM ' || sFrom; AddToExpr(SQLText, sWhere, ' WHERE '); -- Groups SQLText0 := ' '; sWhere := ''; sFrom := '(' || SQLText || ') Q1'; vAAPrefix := 'SC'; IF (pGrStrSw=1) THEN vGrList := ' '; AddToExpr(SQLText0, pGrStrList, ','); vGrList := 'SELECT T.ID_UNIV AS COD'; AddToExpr(vGrList, pGrStrList2, ','); vGrWhere := pGrStrSCFilter; IF (vGrWhere != ' ') THEN vGrWhere := ' WHERE ' || vGrWhere; END IF; /* zzz := '('||vGrList||' FROM vms_strgrpuniv_arr T '||vGrWhere||') G1'; BEGIN sFrom := sFrom || ',' || zzz; EXCEPTION WHEN OTHERS THEN NULL; END; */ AddToExpr(sFrom, '('||vGrList||' FROM vms_strgrpuniv_arr T '||vGrWhere||' ) G1', ','); AddToExpr(sWhere, 'Q1.'||vAAPrefix||'=G1.COD', ' AND '); END IF; IF (NVL(pGr1TableName, ' ')!=' ') THEN AddToExpr(SQLText0, 'S1.'||pGr1NameField||' AS GR1NAME, CAST(S1.'||pGr1IDField||' AS NUMBER) AS GR1', ','); END IF; IF (NVL(pGr2TableName, ' ')!=' ') THEN AddToExpr(SQLText0, 'S2.'||pGr2NameField||' AS GR2NAME, CAST(S2.'||pGr2IDField||' AS NUMBER) AS GR2', ','); END IF; IF (NVL(pGr3TableName, ' ')!=' ') THEN AddToExpr(SQLText0, 'S3.'||pGr3NameField||' AS GR3NAME, CAST(S3.'||pGr3IDField||' AS NUMBER) AS GR3', ','); END IF; IF (NVL(pGr1TableName, ' ')!=' ') THEN AddToExpr(sFrom, pGr1TableName||' S1', ','); END IF; IF (NVL(pGr2TableName, ' ')!=' ') THEN AddToExpr(sFrom, pGr2TableName||' S2', ','); END IF; IF (NVL(pGr3TableName, ' ')!=' ') THEN AddToExpr(sFrom, pGr3TableName||' S3', ','); END IF; IF (NVL(pGr1TableName, ' ')!=' ') THEN AddToExpr(sWhere, 'Q1.'||pGr1AAField||'=S1.COD(+)', ' AND '); END IF; IF (NVL(pGr2TableName, ' ')!=' ') THEN AddToExpr(sWhere, 'Q1.'||pGr2AAField||'=S2.COD(+)', ' AND '); END IF; IF (NVL(pGr3TableName, ' ')!=' ') THEN AddToExpr(sWhere, 'Q1.'||pGr3AAField||'=S3.COD(+)', ' AND '); END IF; IF (NVL(SQLText0, ' ')!=' ') THEN SQLText := 'SELECT Q1.*, ' || SQLText0 || ' FROM ' || sFrom || ' WHERE ' || sWhere; END IF; -- INSERT INTO MY_TABLE (bigfield) VALUES (SQLText); -- COMMIT; -- OPEN RetCur FOR SQLText; -- RETURN RetCur; RETURN SQLText; END RpTOReg02; --------------------------------------------------------------------------- FUNCTION RpTOReg00 (DateS VARCHAR2, DateE VARCHAR2, ppCols VARCHAR2, ppCols0 VARCHAR2, pCont CHAR DEFAULT ' ', pCont0 CHAR DEFAULT ' ', pCont1 CHAR DEFAULT ' ', pSC CHAR DEFAULT ' ', pDep CHAR DEFAULT ' ', pSC1 CHAR DEFAULT ' ', pNRDOC CHAR DEFAULT ' ', pNrCM CHAR DEFAULT ' ', pCCont CHAR DEFAULT ' ', pCCont1 CHAR DEFAULT ' ', pCSC CHAR DEFAULT ' ', pCDep CHAR DEFAULT ' ', pCSC1 CHAR DEFAULT ' ', pGr1TableName VARCHAR2 DEFAULT NULL, pGr1NameField VARCHAR2 DEFAULT NULL, pGr1IDField VARCHAR2 DEFAULT NULL, pGr1AAField VARCHAR2 DEFAULT NULL, pGr2TableName VARCHAR2 DEFAULT NULL, pGr2NameField VARCHAR2 DEFAULT NULL, pGr2IDField VARCHAR2 DEFAULT NULL, pGr2AAField VARCHAR2 DEFAULT NULL, pGr3TableName VARCHAR2 DEFAULT NULL, pGr3NameField VARCHAR2 DEFAULT NULL, pGr3IDField VARCHAR2 DEFAULT NULL, pGr3AAField VARCHAR2 DEFAULT NULL, pGrStrSCFilter VARCHAR, pGrStrSw NUMBER, pGrStrList VARCHAR, pGrStrList2 VARCHAR ) RETURN ResCursor IS pCols VARCHAR2(1000); pCols0 VARCHAR2(1000); SQLText LONG; SQLText0 LONG; pCols1 VARCHAR2(2000); OutParams VARCHAR2(2000); TYPE TCur IS REF CURSOR; RetCur TCur; pDateS DATE; pDateE DATE; mppCols0 varchar2(2000); BEGIN envun4.envsetvalue('un$datadoc',DateS); --DELETE FROM MY_TABLE; --COMMIT; pCols := ppCols; pCols0 := ppCols0; pDateS := TO_DATE(DateS, 'DD.MM.YYYY'); pDateE := TO_DATE(DateE, 'DD.MM.YYYY'); DBMS_OUTPUT.PUT_LINE('-------------------'); DBMS_OUTPUT.PUT_LINE('--- RBTO Report ---'); sTable1 := Un$sold.GET_ztemp_tablename; sTable2 := Un$sold.GET_ztemp_tablename; sTable2b := Un$sold.GET_ztemp_tablename; sTable4 := Un$sold.GET_ztemp_tablename; sTable5 := Un$sold.GET_ztemp_tablename; sTable5b := Un$sold.GET_ztemp_tablename; DBMS_OUTPUT.PUT_LINE('TABLE1 = ' || sTable1); DBMS_OUTPUT.PUT_LINE('TABLE2 = ' || sTable2); IF (INSTR(pCols, ':CONT;', 1, 1)>0) THEN pCols1 := pCols; ELSE pCols1 := pCols || ':CONT;'; END IF; IF ((INSTR(pCols, ':DEP;', 1, 1)<=0) AND (pGr1AAField='DEP' OR pGr2AAField='DEP' OR pGr1AAField='DEP' OR INSTR(pCols, ':DEPNAME;', 1, 1)>0 OR INSTR(pCols, ':DEPCODE;', 1, 1)>0 OR INSTR(pCols, ':DEPNAME2;', 1, 1)>0)) THEN pCols1 := pCols1 || ':DEP;'; END IF; IF ((INSTR(pCols, ':DEPNAME;', 1, 1)<=0) AND (INSTR(pCols, ':DEPCODE;', 1, 1)>0 OR INSTR(pCols, ':DEPNAME2;', 1, 1)>0)) THEN pCols1 := pCols1 || ':DEPNAME;'; END IF; IF ((INSTR(pCols, ':DEPNAME;', 1, 1)>0) AND (INSTR(pCols, ':DEP;', 1, 1)<=0)) THEN pCols1 := pCols1 || ':DEP;'; pCols := pCols || ':DEP;'; pCols0 := pCols0 || ':DEP;'; END IF; IF ((INSTR(pCols, ':SC;', 1, 1)<=0) AND (pGr1AAField='SC' OR pGr2AAField='SC' OR pGr1AAField='SC' OR INSTR(pCols, ':SCNAME;', 1, 1)>0 OR INSTR(pCols, ':SCCODE;', 1, 1)>0 OR INSTR(pCols, ':SCNAME2;', 1, 1)>0 OR INSTR(pCols, ':SCUM;', 1, 1)>0 OR INSTR(pCols, ':GRSC1;', 1, 1)>0 OR INSTR(pCols, ':GRSC2;', 1, 1)>0 OR INSTR(pCols, ':GRSC3;', 1, 1)>0 OR INSTR(pCols, ':GR1SC;', 1, 1)>0 OR INSTR(pCols, ':GR2SC;', 1, 1)>0 OR INSTR(pCols, ':GR3SC;', 1, 1)>0 OR INSTR(pCols, ':GR4SC;', 1, 1)>0 OR INSTR(pCols, ':GR5SC;', 1, 1)>0 )) THEN pCols1 := pCols1 || ':SC;'; END IF; IF ((INSTR(pCols, ':SCNAME;', 1, 1)<=0) AND (INSTR(pCols, ':SCCODE;', 1, 1)>0 OR INSTR(pCols, ':SCNAME2;', 1, 1)>0 OR INSTR(pCols, ':SCUM;', 1, 1)>0)) THEN pCols1 := pCols1 || ':SCNAME;'; END IF; IF ((INSTR(pCols, ':SCNAME;', 1, 1)>0 OR INSTR(pCols, ':GRSC1;', 1, 1)>0 OR INSTR(pCols, ':GRSC2;', 1, 1)>0 OR INSTR(pCols, ':GRSC3;', 1, 1)>0 OR INSTR(pCols, ':GR1SC;', 1, 1)>0 OR INSTR(pCols, ':GR2SC;', 1, 1)>0 OR INSTR(pCols, ':GR3SC;', 1, 1)>0 OR INSTR(pCols, ':GR4SC;', 1, 1)>0 OR INSTR(pCols, ':GR5SC;', 1, 1)>0 ) AND ( INSTR(pCols, ':SC;', 1, 1)<=0) ) THEN pCols1 := pCols1 || ':SC;'; pCols := pCols || ':SC;'; pCols0 := pCols0 || ':SC;'; END IF; IF ((INSTR(pCols, ':SC1;', 1, 1)<=0) AND (pGr1AAField='SC1' OR pGr2AAField='SC1' OR pGr1AAField='SC1' OR INSTR(pCols, ':SC1NAME;', 1, 1)>0 OR INSTR(pCols, ':SC1CODE;', 1, 1)>0 OR INSTR(pCols, ':SC1NAME2;', 1, 1)>0)) THEN pCols1 := pCols1 || ':SC1;'; END IF; IF ((INSTR(pCols, ':SC1NAME;', 1, 1)<=0) AND (INSTR(pCols, ':SC1CODE;', 1, 1)>0 OR INSTR(pCols, ':SC1NAME2;', 1, 1)>0)) THEN pCols1 := pCols1 || ':SC1NAME;'; END IF; IF ((INSTR(pCols, ':SC1NAME;', 1, 1)>0) AND (INSTR(pCols, ':SC1;', 1, 1)<=0)) THEN pCols1 := pCols1 || ':SC1;'; pCols := pCols || ':SC1;'; pCols0 := pCols0 || ':SC1;'; END IF; IF ((INSTR(pCols, ':DOCID;', 1, 1)<=0) AND (INSTR(pCols, ':NRDOCH;', 1, 1)>0)) THEN pCols1 := pCols1 || ':DOCID;'; END IF; IF ((INSTR(pCols, ':CDEPNAME;', 1, 1)<=0) AND (INSTR(pCols, ':CDEPCODE;', 1, 1)>0 OR INSTR(pCols, ':CDEPNAME2;', 1, 1)>0)) THEN pCols1 := pCols1 || ':CDEPNAME;'; END IF; IF ((INSTR(pCols, ':CDEP;', 1, 1)<=0) AND (INSTR(pCols, ':CDEPNAME;', 1, 1)>0)) THEN pCols1 := pCols1 || ':CDEP;'; END IF; IF ((INSTR(pCols, ':CSCNAME;', 1, 1)<=0) AND (INSTR(pCols, ':CSCCODE;', 1, 1)>0 OR INSTR(pCols, ':CSCNAME2;', 1, 1)>0 OR INSTR(pCols, ':CSCUM;', 1, 1)>0)) THEN pCols1 := pCols1 || ':CSCNAME;'; END IF; IF ((INSTR(pCols, ':CSC;', 1, 1)<=0) AND (INSTR(pCols1, ':CSCNAME;', 1, 1)>0)) THEN pCols1 := pCols1 || ':CSC;'; pCols := pCols || ':CSC;'; END IF; -- INSERT INTO MY_TABLE (bigfield) VALUES ('pCols=' || pCols); -- INSERT INTO MY_TABLE (bigfield) VALUES ('pCols1=' || pCols1); -- INSERT INTO MY_TABLE (bigfield) VALUES ('pCols0=' || pCols0); -- COMMIT; --Un$pdc_util.cont_conversion(pCont, DateS) SQLText := Pk_Tbto.RpTOReg02 (pDateS, pDateE, pCols1, pCont, pCont1, pSC, pDep, pSC1, pNRDOC, pNrCM, pCCont, pCCont1, pCSC, pCDep, pCSC1, pGr1TableName, pGr1NameField, pGr1IDField, pGr1AAField, pGr2TableName, pGr2NameField, pGr2IDField, pGr2AAField, pGr3TableName, pGr3NameField, pGr3IDField, pGr3AAField, pGrStrSCFilter, pGrStrSw , pGrStrList, pGrStrList2); IF (INSTR(pCols, ':CONT2;', 1, 1)>0 OR INSTR(pCols, ':CONT3;', 1, 1)>0) THEN EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE '||sTable2b||' ON COMMIT PRESERVE ROWS AS ' || SQLText; EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE '||sTable2||' ON COMMIT PRESERVE ROWS AS SELECT T.*, C.ATTR1 AS CONT2, C.ATTR2 AS CONT3 FROM ' || sTable2b || ' T, VMS_PDC C WHERE T.CONT=C.CONT'; ELSE EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE '||sTable2||' ON COMMIT PRESERVE ROWS AS ' || SQLText; END IF; say(sTable2||' '||SQLText); SQLText := Pk_Tbto.RpTOReg01 (pDateS-1, pCols1, pCont0, pCont1, pSC, pDep, pSC1, pNRDOC, pNrCM, pGr1TableName, pGr1NameField, pGr1IDField, pGr1AAField, pGr2TableName, pGr2NameField, pGr2IDField, pGr2AAField, pGr3TableName, pGr3NameField, pGr3IDField, pGr3AAField, pGrStrSCFilter, pGrStrSw , pGrStrList, pGrStrList2); --imt(SQLText,re=>true); IF (INSTR(pCols, ':CONT2;', 1, 1)>0 OR INSTR(pCols, ':CONT3;', 1, 1)>0) THEN EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE '||sTable5b||' ON COMMIT PRESERVE ROWS AS ' || SQLText; EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE '||sTable5||' ON COMMIT PRESERVE ROWS AS SELECT T.*, C.ATTR1 AS CONT2, C.ATTR2 AS CONT3 FROM ' || sTable5b || ' T, VMS_PDC C WHERE T.CONT=C.CONT'; ELSE EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE '||sTable5||' ON COMMIT PRESERVE ROWS AS ' || SQLText; END IF; --SQLText0 := GetTable1SQL (pCols0, '('||SQLText||')', sTable2, ''); SQLText0 := GetTable1SQL (pCols0, sTable5, sTable2, ''); SQLText0 := 'CREATE GLOBAL TEMPORARY TABLE '||sTable1||' ON COMMIT PRESERVE ROWS AS ' || SQLText0; say(sTable1||' s1 '||SQLText); EXECUTE IMMEDIATE SQLText0; SQLText0 := '"SELECT TO_DATE('''''||DateS||''''', ''''DD.MM.YYYY'''') AS DATES , TO_DATE('''''||DateE||''''', ''''DD.MM.YYYY'''') AS DATEE '; SQLText0 := SQLText0 || ', SYS_CONTEXT(''''ENVUN4'''',''''BG$FILTER'''') AS NRSET'; IF (NVL(pCont, ' ')<>' ') THEN SQLText0 := SQLText0 || ', ''''' || pCont0 || ''''' AS CONT'; END IF; IF (NVL(pCont1, ' ')<>' ') THEN SQLText0 := SQLText0 || ', ''''' || pCont1 || ''''' AS CONT1'; END IF; IF (NVL(pSC, ' ')<>' ') THEN SQLText0 := SQLText0 || ', ' || pSC || ' AS SC, (SELECT DENUMIREA FROM VMS_UNIVERS WHERE COD=' || pSC || ') AS SCNAME'; END IF; IF (NVL(pDep, ' ')<>' ') THEN SQLText0 := SQLText0 || ', ' || pDep || ' AS DEP, (SELECT DENUMIREA FROM VMS_UNIVERS WHERE COD=' || pDep || ') AS DEPNAME'; END IF; IF (NVL(pSC1, ' ')<>' ') THEN SQLText0 := SQLText0 || ', ' || pSC1 || ' AS SC1, (SELECT DENUMIREA FROM VMS_UNIVERS WHERE COD=' || pSC1 || ') AS SC1NAME'; END IF; IF (NVL(pNRDOC, ' ')<>' ') THEN SQLText0 := SQLText0 || ', ''''' || pNRDOC || ''''' AS NRDOC'; END IF; IF (NVL(pNrCM, ' ')<>' ') THEN SQLText0 := SQLText0 || ', ''''' || pNrCM || ''''' AS NrCM'; END IF; IF (NVL(pCCont, ' ')<>' ') THEN SQLText0 := SQLText0 || ', ''''' || pCCont || ''''' AS CCONT'; END IF; IF (NVL(pCCont1, ' ')<>' ') THEN SQLText0 := SQLText0 || ', ''''' || pCCont1 || ''''' AS CCONT1'; END IF; IF (NVL(pCSC, ' ')<>' ') THEN SQLText0 := SQLText0 || ', ' || pCSC || ' AS CSC, (SELECT DENUMIREA FROM VMS_UNIVERS WHERE COD=' || pCSC || ') AS CSCNAME'; END IF; IF (NVL(pCDep, ' ')<>' ') THEN SQLText0 := SQLText0 || ', ' || pCDep || ' AS CDEP, (SELECT DENUMIREA FROM VMS_UNIVERS WHERE COD=' || pCDep || ') AS CDEPNAME'; END IF; IF (NVL(pCSC1, ' ')<>' ') THEN SQLText0 := SQLText0 || ', ' || pCSC1 || ' AS CSC1, (SELECT DENUMIREA FROM VMS_UNIVERS WHERE COD=' || pCSC1 || ') AS CSC1NAME'; END IF; SQLText0 := SQLText0 || ' FROM DUAL"'; -- INSERT INTO MY_TABLE (bigfield) VALUES (SQLText0); -- COMMIT; OutParams := '[PARAMS],'; AddToExpr(OutParams, 'TABLE1='||sTable1, ','); AddToExpr(OutParams, 'TABLE2='||sTable2, ','); AddToExpr(OutParams, '[JOINFIELDS]', ','); AddToExpr(OutParams, GetJoinFieldList (pCols0, ' AND ', 1), ','); AddToExpr(OutParams, '[SQLHEADER]', ','); AddToExpr(OutParams, SQLText0, ','); AddToExpr(OutParams, '[USENEWJOIN]', ','); AddToExpr(OutParams, 'TRUE', ','); if( ppCols0 is not null) then mppCols0 := REPLACE(REPLACE(ppCols0,':'),';',','); if( substr(mppCols0,length(mppCols0),1) = ',' ) then mppCols0 := substr(mppCols0,1,length(mppCols0)-1); end if; SQLText := 'delete from '||sTable1||' where ('||mppCols0||') not in (select '||mppCols0||' from '||sTable2||')'; say('ssd '||SQLText); execute immediate SQLText; end if; --AddToExpr(OutParams, 'SELECT ' || PKG_AFX.DateToStr(pDateS) || ' AS DATES, ' || PKG_AFX.DateToStr(pDateE) || ' AS DATEE FROM DUAL', ','); --execute immediate 'create table ztemp999 as select * from '|| sTable2; SQLText := 'SELECT '''||OutParams||''' AS FFF FROM DUAL'; --INSERT INTO MY_TABLE (bigfield) VALUES ('SQLTextR = ' || SQLText); --COMMIT; say(SQLText); --msg('test'); OPEN RetCur FOR SQLText; RETURN RetCur; END RpTOReg00; --------------------------------------------------------------------------- FUNCTION GetTable1SQL (pCols VARCHAR2, pFrom1 VARCHAR2, pFrom2 VARCHAR2, pFieldName VARCHAR2) RETURN LONG IS RetVal LONG; FieldList VARCHAR2(2000); sss VARCHAR2(2000); sFieldListWithAcc VARCHAR2(2000); sFieldListWithAcc1 VARCHAR2(2000); sComma VARCHAR2(1); sSuffix VARCHAR2(4000); BEGIN -- INSERT INTO MY_TABLE (bigfield) VALUES ('pCols = ' || pCols); -- COMMIT; FieldList := REPLACE(pCols, ';', ','); -- INSERT INTO MY_TABLE (bigfield) VALUES ('FieldList = ' || FieldList); -- COMMIT; FieldList := REPLACE(FieldList, ':', ''); -- INSERT INTO MY_TABLE (bigfield) VALUES ('FieldList = ' || FieldList); -- COMMIT; sFieldListWithAcc := 'CONT'; sss := REPLACE(FieldList, 'CONT,'); sss := SUBSTR(sss, 1, LENGTH(sss)-1); AddToExpr (sFieldListWithAcc, sss, ','); sFieldListWithAcc1:=REPLACE(sFieldListWithAcc, 'CONT1','T1.CONT1'); FieldList := SUBSTR(FieldList, 1, LENGTH(FieldList)-1); IF (NVL(FieldList, ' ')=' ') THEN sComma := ' '; sSuffix := ' '; ELSE sComma := ','; sSuffix := ' GROUP BY '||FieldList||' ORDER BY '||FieldList; END IF; RetVal := 'SELECT '||FieldList||sComma||' SUM (SumaDtS) SumaDtS, SUM (SumaCtS) SumaCtS, SUM (SumaDtC) SumaDtC, SUM (SumaCtC) SumaCtC, SUM (SumaDtE) SumaDtE, SUM (SumaCtE) SumaCtE, SUM (qtyDtS) cantDtS, SUM (qtyCtS) cantCtS, SUM (qtyDtC) cantDtC, SUM (qtyCtC) cantCtC, SUM (qtyDtE) cantDtE, SUM (qtyCtE) cantCtE, SUM (SumaValDtS) SumaVDtS, SUM (SumaValCtS) SumaVCtS, SUM (SumaVDtC) SumaVDtC, SUM (SumaVCtC) SumaVCtC, SUM (SumaValDtE) SumaVDtE, SUM (SumaValCtE) SumaVCtE, SUM (SumIDtS) SumIDtS, SUM (SumICtS) SumICtS, SUM (SumIDt) SumIDt, SUM (SumICt) SumICt, SUM (SumIDtE) SumIDtE, SUM (SumICtE) SumICtE FROM (SELECT T1.'||sFieldListWithAcc1||', T1.SumaDtC, T1.SumaCtC, DECODE (P1.AP, ''A'', SumaS + SumaDtC - SumaCtC, 0) SumaDtE, DECODE (P1.AP, ''P'', - (SumaS + SumaDtC - SumaCtC), 0) SumaCtE, DECODE (P1.AP, ''A'', SumaS, 0) SumaDtS, DECODE (P1.AP, ''P'', -SumaS, 0) SumaCtS, T1.qtyDtC, T1.qtyCtC, DECODE (P1.AP, ''A'', qtyS + qtyDtC - qtyCtC, 0) qtyDtE, DECODE (P1.AP, ''P'', - (qtyS + qtyDtC - qtyCtC), 0) qtyCtE, DECODE (P1.AP, ''A'', qtyS, 0) qtyDtS, DECODE (P1.AP, ''P'', -qtyS, 0) qtyCtS, T1.SumaVDtC, T1.SumaVCtC, DECODE (P1.AP, ''A'', SumaValS + SumaVDtC - SumaVCtC, 0) SumaValDtE, DECODE (P1.AP, ''P'', - (SumaValS + SumaVDtC - SumaVCtC), 0) SumaValCtE, DECODE (P1.AP, ''A'', SumaValS, 0) SumaValDtS, DECODE (P1.AP, ''P'', -SumaValS, 0) SumaValCtS, T1.SumIDt, T1.SumICt, DECODE (P1.AP, ''A'', SumIS + SumIDt - SumICt, 0) SumIDtE, DECODE (P1.AP, ''P'', - (SumIS + SumIDt - SumICt), 0) SumICtE, DECODE (P1.AP, ''A'', SumIS, 0) SumIDtS, DECODE (P1.AP, ''P'', -SumIS, 0) SumICtS FROM (SELECT '||sFieldListWithAcc||', SUM (sumaS) SumaS, SUM (sumaDtC) SumaDtC, SUM (SumaCtC) SumaCtC, SUM (qtyS) qtyS, SUM (qtyDtC) qtyDtC, SUM (qtyCtC) qtyCtC, SUM (SumaValS) SumaValS, SUM (SumaVDtC) SumaVDtC, SUM (SumaVCtC) SumaVCtC, SUM (SumIS) SumIS, SUM (SumIDt) SumIDt, SUM (SumICt) SumICt FROM (SELECT '||sFieldListWithAcc||', CAST (0 AS NUMBER) SumaS, SumadtC AS SumaDtC, SumaCtC SumaCtC, CAST (0 AS NUMBER) qtyS, cantDtC AS qtyDtC, cantCtC qtyCtC, CAST (0 AS NUMBER) SumaValS, SumaVDtC AS SumaVDtC, SumaVCtC SumaVCtC, CAST (0 AS NUMBER) SumIS, SumaValDtI AS SumIDt, SumaValCtI SumICt FROM '||pFrom2||' UNION ALL SELECT '||sFieldListWithAcc||', suma, CAST (0 AS NUMBER), CAST (0 AS NUMBER), cant, CAST (0 AS NUMBER), CAST (0 AS NUMBER), sumaval, CAST (0 AS NUMBER), CAST (0 AS NUMBER), sumagaap, CAST (0 AS NUMBER), CAST (0 AS NUMBER) FROM '||pFrom1||') GROUP BY '||sFieldListWithAcc||') T1, VMS_PDC P1 WHERE T1.cont = P1.cont (+))' || sSuffix; -- INSERT INTO MY_TABLE (bigfield) VALUES ('stepD1 = ' || RetVal); -- COMMIT; EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE '||sTable4||' ON COMMIT PRESERVE ROWS AS ' || RetVal; RetVal := 'SELECT T9.* , SumaDtS / DECODE (cantDtS, 0, 1, cantDtS) AS PriceDtS , SumaCtS / DECODE (cantCtS, 0, 1, cantCtS) AS PriceCtS , SumaDtC / DECODE (cantDtC, 0, 1, cantDtC) AS PriceDtC , SumaCtC / DECODE (cantCtC, 0, 1, cantCtC) AS PriceCtC , SumaDtE / DECODE (cantDtE, 0, 1, cantDtE) AS PriceDtE , SumaCtE / DECODE (cantCtE, 0, 1, cantCtE) AS PriceCtE , SumaVDtS / DECODE (cantDtS, 0, 1, cantDtS) AS PriceVDtS , SumaVCtS / DECODE (cantCtS, 0, 1, cantCtS) AS PriceVCtS , SumaVDtC / DECODE (cantDtC, 0, 1, cantDtC) AS PriceVDtC , SumaVCtC / DECODE (cantCtC, 0, 1, cantCtC) AS PriceVCtC , SumaVDtE / DECODE (cantDtE, 0, 1, cantDtE) AS PriceVDtE , SumaVCtE / DECODE (cantCtE, 0, 1, cantCtE) AS PriceVCtE FROM ' || sTable4 || ' T9'; -- INSERT INTO MY_TABLE (bigfield) VALUES ('stepD2 = ' || RetVal); -- COMMIT; RETURN RetVal; END GetTable1SQL; --------------------------------------------------------------------------- PROCEDURE CreateTmpTable IS BEGIN EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE ztemp_200 AS SELECT * FROM DUAL'; END CreateTmpTable; --------------------------------------------------------------------------- PROCEDURE DropTmpTables IS I INTEGER; BEGIN I := 0; DeleteTable (sTable1); DeleteTable (sTable2); DeleteTable (sTable2b); DeleteTable (sTable3); DeleteTable (sTable4); DeleteTable (sTable5); DeleteTable (sTable5b); END DropTmpTables; --------------------------------------------------------------------------- PROCEDURE DeleteTable (pTableName VARCHAR2) IS BEGIN IF (NVL(sTable1, ' ') != ' ') THEN BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || pTableName || ' DROP STORAGE'; EXECUTE IMMEDIATE 'DELETE FROM ' || pTableName; EXECUTE IMMEDIATE 'DROP TABLE ' || pTableName; EXCEPTION WHEN OTHERS THEN NULL; END; END IF; END DeleteTable; --------------------------------------------------------------------------- END Pk_Tbto; / ------------------------------------- CREATE OR REPLACE TRIGGER "A$ADM$TR" -- Last modified: 17.02.2017 -- Last modified: 15.09.2010 before insert or update or delete on a$adm for each row declare v_modified date:=sysdate; -- /*procedure set_parent_modified(p_parent_id int)is begin for c in (select rowid from a$adm where obj_id=p_parent_id and (modified is null or modified:old.obj_id then update a$adp set obj_id=:new.obj_id where obj_id=:old.obj_id; end if; --:new.obj_id:=:old.obj_id; :new.obj_type:=:old.obj_type; :new.obj_subtype:=:old.obj_subtype; end if; -- if inserting or updating then :new.modified:=v_modified; end if; -- /*if :new.parent_id is not null then set_parent_modified(:new.parent_id); end if; -- if :old.parent_id is not null and (:new.parent_id is null or :new.parent_id<>:old.parent_id) then set_parent_modified(:old.parent_id); end if;*/ end; /