--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<v_modified)) loop
   update a$adm set modified=v_modified where rowid=c.rowid;
  end loop;
 end;*/
 --
begin
 if inserting then
  if :new.obj_id is null then
   select a$adm$sq.nextval into :new.obj_id from dual;
  end if;
  --if :new.nrord is null then     
  if nvl(:new.nrord,0) = 0 then
   :new.nrord:=:new.obj_id;
  end if;
 end if;
 --
 if inserting or updating then
  :new.section:=upper(nvl(:new.section,:new.obj_id));
 end if;
 --
 if updating then
  if :new.obj_id<>: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;
/