1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
TEXT TO lcSQLSelectString TEXTMERGE NOSHOW
SELECT COB.*,NVL(SPAG.NAG,'') NAG FROM
( SELECT CUN.CSSN,TO_CHAR(CUN.POPE,'9')||TO_CHAR(CUN.TPOL,'9')||TO_CHAR(CUN.CPOL,'B999999') KLUC,
MAT.SCET,SUM(CUN.suma) SUMA FROM
(select cssn, 0 POPE, 9 TPOL, 999999 CPOL,cmat,qkol* 1 *QCEN suma from lida.sna_ost WHERE CSSN=?(IDS) OR ?(IDS)= 0
UNION ALL
select cssn, 9 POPE, 9 TPOL, 999999 CPOL,cmat,qkol* 1 *QCEN suma from lida.sna_ost WHERE CSSN=?(IDS) OR ?(IDS)= 0
UNION ALL
SELECT DOC.CSSN,DOC.POPE+ 1 POPE,DOC.TPOL,DOC.CPOL,DMAT.cmat,DMAT.QSUM suma
FROM (SELECT * FROM LIDA.SNA_DOC WHERE DDOC BETWEEN (?DTOC(DATNT)) AND (?DTOC(DATKT)) AND ( CSSN=?(IDS) OR ?(IDS)= 0 )) DOC
INNER JOIN LIDA.SNA_DOCMAT DMAT ON DMAT.CDOC = DOC.CDOC
UNION ALL
SELECT DOC.CSSN,DOC.POPE+ 1 POPE,DOC.TPOL, 999999 CPOL,DMAT.cmat,DMAT.QSUM suma
FROM (SELECT * FROM LIDA.SNA_DOC WHERE DDOC BETWEEN (?DTOC(DATNT)) AND (?DTOC(DATKT)) AND ( CSSN=?(IDS) OR ?(IDS)= 0 )) DOC
INNER JOIN LIDA.SNA_DOCMAT DMAT ON DMAT.CDOC = DOC.CDOC
UNION ALL
SELECT DOC.CSSN, 0 POPE, 9 TPOL, 999999 CPOL,DMAT.cmat,DMAT.QSUM*( 2 *DOC.Pope- 1 ) suma
FROM (SELECT * FROM LIDA.SNA_DOC WHERE DDOC >= (?DTOC(DATNT)) AND ( CSSN=?(IDS) OR ?(IDS)= 0 )) DOC
INNER JOIN LIDA.SNA_DOCMAT DMAT ON DMAT.CDOC = DOC.CDOC
UNION ALL
SELECT DOC.CSSN,DOC.POPE+ 1 POPE, 1 TPOL,DOC.KGPL CPOL,DMAT.cmat,DMAT.QSUM suma
FROM (SELECT * FROM LIDA.SNA_D54 WHERE DDOC BETWEEN (?DTOC(DATNT)) AND (?DTOC(DATKT)) AND ( CSSN=?(IDS) OR ?(IDS)= 0 )) DOC
INNER JOIN LIDA.SNA_D54MAT DMAT ON DMAT.CDOC = DOC.CDOC
UNION ALL
SELECT DOC.CSSN,DOC.POPE+ 1 POPE, 1 TPOL, 999999 CPOL,DMAT.cmat,DMAT.QSUM suma
FROM (SELECT * FROM LIDA.SNA_D54 WHERE DDOC BETWEEN (?DTOC(DATNT)) AND (?DTOC(DATKT)) AND ( CSSN=?(IDS) OR ?(IDS)= 0 )) DOC
INNER JOIN LIDA.SNA_D54MAT DMAT ON DMAT.CDOC = DOC.CDOC
UNION ALL
SELECT DOC.CSSN, 0 POPE, 9 TPOL, 999999 CPOL,DMAT.cmat,DMAT.QSUM*( 2 *DOC.Pope- 1 ) suma
FROM (SELECT * FROM LIDA.SNA_D54 WHERE DDOC >= (?DTOC(DATNT)) AND ( CSSN=?(IDS) OR ?(IDS)= 0 )) DOC
INNER JOIN LIDA.SNA_D54MAT DMAT ON DMAT.CDOC = DOC.CDOC
UNION ALL
SELECT DOC.CSSN,DOC.POPE+ 1 POPE, 3 TPOL,DOC.KSPO CPOL,DMAT.cmat,DMAT.QSUM suma
FROM (SELECT * FROM LIDA.sna_l82 WHERE DDOC BETWEEN (?DTOC(DATNT)) AND (?DTOC(DATKT)) AND ( CSSN=?(IDS) OR ?(IDS)= 0 ) ) DOC
INNER JOIN LIDA.sna_l82otp DMAT ON DMAT.CDOC = DOC.CDOC
UNION ALL
SELECT DOC.CSSN,DOC.POPE+ 1 POPE, 3 TPOL, 999999 CPOL,DMAT.cmat,DMAT.QSUM suma
FROM (SELECT * FROM LIDA.sna_l82 WHERE DDOC BETWEEN (?DTOC(DATNT)) AND (?DTOC(DATKT)) AND ( CSSN=?(IDS) OR ?(IDS)= 0 ) ) DOC
INNER JOIN LIDA.sna_l82otp DMAT ON DMAT.CDOC = DOC.CDOC
UNION ALL
SELECT DOC.CSSN, 0 POPE, 9 TPOL, 999999 CPOL,DMAT.cmat,DMAT.QSUM*( 2 *DOC.Pope- 1 ) suma
FROM (SELECT * FROM LIDA.sna_l82 WHERE DDOC >= (?DTOC(DATNT)) AND ( CSSN=?(IDS) OR ?(IDS)= 0 ) ) DOC
INNER JOIN LIDA.sna_l82otp DMAT ON DMAT.CDOC = DOC.CDOC
UNION ALL
SELECT DOC.CSSN, 9 pope, 9 TPOL, 999999 CPOL,DMAT.cmat,DMAT.QSUM*( 2 *DOC.Pope- 1 ) suma
FROM (SELECT * FROM LIDA.SNA_DOC WHERE DDOC > (?DTOC(DATKT)) AND ( CSSN=?(IDS) OR ?(IDS)= 0 )) DOC
INNER JOIN LIDA.SNA_DOCMAT DMAT ON DMAT.CDOC = DOC.CDOC
UNION ALL
SELECT DOC.CSSN, 9 POPE, 9 TPOL, 999999 CPOL,DMAT.cmat,DMAT.QSUM*( 2 *DOC.Pope- 1 ) suma
FROM (SELECT * FROM LIDA.SNA_D54 WHERE DDOC > (?DTOC(DATKT)) AND ( CSSN=?(IDS) OR ?(IDS)= 0 ) ) DOC
INNER JOIN LIDA.SNA_D54MAT DMAT ON DMAT.CDOC = DOC.CDOC
UNION ALL
SELECT DOC.CSSN, 9 POPE, 9 TPOL, 999999 CPOL,DMAT.cmat,DMAT.QSUM*( 2 *DOC.Pope- 1 ) suma
FROM (SELECT * FROM LIDA.sna_l82 WHERE DDOC > (?DTOC(DATKT)) AND ( CSSN=?(IDS) OR ?(IDS)= 0 ) ) DOC
INNER JOIN LIDA.sna_l82otp DMAT ON DMAT.CDOC = DOC.CDOC ) CUN
INNER JOIN (SELECT CMAT,SUBSTR(SCH, 1 , 2 )||'_'||SUBSTR(SSCH, 1 , 2 ) SCET FROM LIDA.NSI_MAT ) MAT ON MAT.cmat = CUN.cmat
GROUP BY CUN.CSSN,CUN.POPE,CUN.TPOL,CUN.CPOL,MAT.SCET ) COB LEFT JOIN
(SELECT TO_CHAR( 1 ,'9')||TO_CHAR( 1 ,'9')||TO_CHAR(ORG.KORG,'B999999') KOD,ORG.IORG NAG FROM lida.nsi_org ORG
UNION ALL
SELECT TO_CHAR( 2 ,'9')||TO_CHAR( 1 ,'9')||TO_CHAR(ORG.KORG,'B999999') KOD,ORG.IORG NAG FROM lida.nsi_org ORG
UNION ALL
SELECT TO_CHAR( 1 ,'9')||TO_CHAR( 2 ,'9')||TO_CHAR(MOL.CMOL,'B999999') KOD,MOL.IFIO NAG FROM lida.nsi_mol MOL
UNION ALL
SELECT TO_CHAR( 2 ,'9')||TO_CHAR( 2 ,'9')||TO_CHAR(MOL.CMOL,'B999999') KOD,MOL.IFIO NAG FROM lida.nsi_mol MOL
UNION ALL
SELECT TO_CHAR( 1 ,'9')||TO_CHAR( 3 ,'9')||TO_CHAR(SPO.KSPO,'B999999') KOD,SPO.ISPO NAG FROM lida.nsi_spo SPO
UNION ALL
SELECT TO_CHAR( 2 ,'9')||TO_CHAR( 3 ,'9')||TO_CHAR(SPO.KSPO,'B999999') KOD,SPO.ISPO NAG FROM lida.nsi_spo SPO
UNION ALL
SELECT TO_CHAR( 0 ,'9')||TO_CHAR( 9 ,'9')||TO_CHAR( 999999 ,'B999999') KOD,'Сальдо на начало периода' NAG FROM DUAL
UNION ALL
SELECT TO_CHAR( 9 ,'9')||TO_CHAR( 9 ,'9')||TO_CHAR( 999999 ,'B999999') KOD,'Сальдо на конец периода' NAG FROM DUAL
UNION ALL
SELECT TO_CHAR( 1 ,'9')||TO_CHAR( 1 ,'9')||TO_CHAR( 999999 ,'B999999') KOD,'Итого от поставщиков за период' NAG FROM DUAL
UNION ALL
SELECT TO_CHAR( 2 ,'9')||TO_CHAR( 1 ,'9')||TO_CHAR( 999999 ,'B999999') KOD,'Итого на сторону за период' NAG FROM DUAL
UNION ALL
SELECT TO_CHAR( 1 ,'9')||TO_CHAR( 2 ,'9')||TO_CHAR( 999999 ,'B999999') KOD,'Итого от МОЛ за период' NAG FROM DUAL
UNION ALL
SELECT TO_CHAR( 2 ,'9')||TO_CHAR( 2 ,'9')||TO_CHAR( 999999 ,'B999999') KOD,'Итого для МОЛ за период' NAG FROM DUAL
UNION ALL
SELECT TO_CHAR( 1 ,'9')||TO_CHAR( 3 ,'9')||TO_CHAR( 999999 ,'B999999') KOD,'Итого от подр. за период' NAG FROM DUAL
UNION ALL
SELECT TO_CHAR( 2 ,'9')||TO_CHAR( 3 ,'9')||TO_CHAR( 999999 ,'B999999') KOD,'Итого для подр. за период' NAG FROM DUAL
) SPAG ON SPAG.KOD = COB.KLUC ORDER BY 1 , 2
ENDTEXT
lnReturn = SQLEXEC(lnConn, lcSQLSelectString, "OBSAX")
IF lnReturn <= 0
WAIT WINDOW "КРАХ SQLEXEC"
THISFORM.Release
ENDIF
