Разработка модуля автоматизированного учета контрактов услуг для предприятия I.S."Radiocomunicatii"

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC0)CLCDTSC0T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC1)CLCDTSC1T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC2)CLCDTSC2T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC3)CLCDTSC3T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC4)CLCDTSC4T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC5)CLCDTSC5T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC6)CLCDTSC6T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC7)CLCDTSC7T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC8)CLCDTSC8T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC9)CLCDTSC9T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC0)CLCCTSC0T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC1)CLCCTSC1T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC2)CLCCTSC2T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC3)CLCCTSC3T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC4)CLCCTSC4T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC5)CLCCTSC5T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC6)CLCCTSC6T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC7)CLCCTSC7T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC8)CLCCTSC8T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC9)CLCCTSC9T

FROM TMDB_REG_A A;

CREATE OR REPLACE VIEW VMDB_REG_A1

AS

SELECT A."NRDOC",A."NRDOC1",A."NRDOC2",A."SUMA0",A."SUMA1",A."SUMA2",A."SUMA3",A."SUMA4",A."SUMA5",A."SUMA6",A."SUMA7",A."SUMA8",A."SUMA9",A."DT",A."DT1",A."DTSC0",A."DTSC1",A."DTSC2",A."DTSC3",A."DTSC4",A."DTSC5",A."DTSC6",A."DTSC7",A."DTSC8",A."DTSC9",A."DTNUM0",A."DTNUM1",A."DTNUM2",A."DTNUM3",A."DTNUM4",A."DTNUM5",A."DTNUM6",A."DTNUM7",A."DTNUM8",A."DTNUM9",A."DTSTR0",A."DTSTR1",A."DTSTR2",A."DTSTR3",A."DTSTR4",A."DTSTR5",A."DTSTR6",A."DTSTR7",A."DTSTR8",A."DTSTR9",A."DTDATA0",A."DTDATA1",A."DTDATA2",A."DTDATA3",A."DTDATA4",A."DTSUMAVAL0",A."DTSUMAVAL1",A."DTSUMAVAL2",A."DTSUMAVAL3",A."DTSUMAVAL4",A."DTVALUTA0",A."DTVALUTA1",A."DTVALUTA2",A."DTVALUTA3",A."DTVALUTA4",A."DTCANT0",A."DTCANT1",A."DTCANT2",A."DTCANT3",A."DTCANT4",A."DTCANT5",A."DTCANT6",A."DTCANT7",A."DTCANT8",A."DTCANT9",A."CT",A."CT1",A."CTSC0",A."CTSC1",A."CTSC2",A."CTSC3",A."CTSC4",A."CTSC5",A."CTSC6",A."CTSC7",A."CTSC8",A."CTSC9",A."CTNUM0",A."CTNUM1",A."CTNUM2",A."CTNUM3",A."CTNUM4",A."CTNUM5",A."CTNUM6",A."CTNUM7",A."CTNUM8",A."CTNUM9",A."CTSTR0",A."CTSTR1",A."CTSTR2",A."CTSTR3",A."CTSTR4",A."CTSTR5",A."CTSTR6",A."CTSTR7",A."CTSTR8",A."CTSTR9",A."CTDATA0",A."CTDATA1",A."CTDATA2",A."CTDATA3",A."CTDATA4",A."CTSUMAVAL0",A."CTSUMAVAL1",A."CTSUMAVAL2",A."CTSUMAVAL3",A."CTSUMAVAL4",A."CTVALUTA0",A."CTVALUTA1",A."CTVALUTA2",A."CTVALUTA3",A."CTVALUTA4",A."CTCANT0",A."CTCANT1",A."CTCANT2",A."CTCANT3",A."CTCANT4",A."CTCANT5",A."CTCANT6",A."CTCANT7",A."CTCANT8",A."CTCANT9"

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC0)CLCDTSC0T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC1)CLCDTSC1T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC2)CLCDTSC2T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC3)CLCDTSC3T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC4)CLCDTSC4T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC5)CLCDTSC5T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC6)CLCDTSC6T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC7)CLCDTSC7T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC8)CLCDTSC8T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC9)CLCDTSC9T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC0)CLCCTSC0T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC1)CLCCTSC1T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC2)CLCCTSC2T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC3)CLCCTSC3T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC4)CLCCTSC4T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC5)CLCCTSC5T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC6)CLCCTSC6T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC7)CLCCTSC7T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC8)CLCCTSC8T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC9)CLCCTSC9T

FROM TMDB_REG_A1 A;

CREATE OR REPLACE VIEW VMDB_REG_A1D

AS

SELECT A."NRDOC",A."NRDOC1",A."NRDOC2",A."NRDOC3",A."SUMA0",A."SUMA1",A."SUMA2",A."SUMA3",A."SUMA4",A."SUMA5",A."SUMA6",A."SUMA7",A."SUMA8",A."SUMA9",A."DT",A."DT1",A."DTSC0",A."DTSC1",A."DTSC2",A."DTSC3",A."DTSC4",A."DTSC5",A."DTSC6",A."DTSC7",A."DTSC8",A."DTSC9",A."DTNUM0",A."DTNUM1",A."DTNUM2",A."DTNUM3",A."DTNUM4",A."DTNUM5",A."DTNUM6",A."DTNUM7",A."DTNUM8",A."DTNUM9",A."DTSTR0",A."DTSTR1",A."DTSTR2",A."DTSTR3",A."DTSTR4",A."DTSTR5",A."DTSTR6",A."DTSTR7",A."DTSTR8",A."DTSTR9",A."DTDATA0",A."DTDATA1",A."DTDATA2",A."DTDATA3",A."DTDATA4",A."DTSUMAVAL0",A."DTSUMAVAL1",A."DTSUMAVAL2",A."DTSUMAVAL3",A."DTSUMAVAL4",A."DTVALUTA0",A."DTVALUTA1",A."DTVALUTA2",A."DTVALUTA3",A."DTVALUTA4",A."DTCANT0",A."DTCANT1",A."DTCANT2",A."DTCANT3",A."DTCANT4",A."DTCANT5",A."DTCANT6",A."DTCANT7",A."DTCANT8",A."DTCANT9",A."CT",A."CT1",A."CTSC0",A."CTSC1",A."CTSC2",A."CTSC3",A."CTSC4",A."CTSC5",A."CTSC6",A."CTSC7",A."CTSC8",A."CTSC9",A."CTNUM0",A."CTNUM1",A."CTNUM2",A."CTNUM3",A."CTNUM4",A."CTNUM5",A."CTNUM6",A."CTNUM7",A."CTNUM8",A."CTNUM9",A."CTSTR0",A."CTSTR1",A."CTSTR2",A."CTSTR3",A."CTSTR4",A."CTSTR5",A."CTSTR6",A."CTSTR7",A."CTSTR8",A."CTSTR9",A."CTDATA0",A."CTDATA1",A."CTDATA2",A."CTDATA3",A."CTDATA4",A."CTSUMAVAL0",A."CTSUMAVAL1",A."CTSUMAVAL2",A."CTSUMAVAL3",A."CTSUMAVAL4",A."CTVALUTA0",A."CTVALUTA1",A."CTVALUTA2",A."CTVALUTA3",A."CTVALUTA4",A."CTCANT0",A."CTCANT1",A."CTCANT2",A."CTCANT3",A."CTCANT4",A."CTCANT5",A."CTCANT6",A."CTCANT7",A."CTCANT8",A."CTCANT9"

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC0)CLCDTSC0T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC1)CLCDTSC1T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC2)CLCDTSC2T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC3)CLCDTSC3T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC4)CLCDTSC4T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC5)CLCDTSC5T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC6)CLCDTSC6T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC7)CLCDTSC7T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC8)CLCDTSC8T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC9)CLCDTSC9T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC0)CLCCTSC0T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC1)CLCCTSC1T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC2)CLCCTSC2T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC3)CLCCTSC3T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC4)CLCCTSC4T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC5)CLCCTSC5T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC6)CLCCTSC6T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC7)CLCCTSC7T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC8)CLCCTSC8T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC9)CLCCTSC9T

FROM TMDB_REG_A1D A;

CREATE OR REPLACE VIEW VMDB_REG_A2

AS

SELECT A."NRDOC",A."NRDOC1",A."NRDOC2",A."SUMA0",A."SUMA1",A."SUMA2",A."SUMA3",A."SUMA4",A."SUMA5",A."SUMA6",A."SUMA7",A."SUMA8",A."SUMA9",A."DT",A."DT1",A."DTSC0",A."DTSC1",A."DTSC2",A."DTSC3",A."DTSC4",A."DTSC5",A."DTSC6",A."DTSC7",A."DTSC8",A."DTSC9",A."DTNUM0",A."DTNUM1",A."DTNUM2",A."DTNUM3",A."DTNUM4",A."DTNUM5",A."DTNUM6",A."DTNUM7",A."DTNUM8",A."DTNUM9",A."DTSTR0",A."DTSTR1",A."DTSTR2",A."DTSTR3",A."DTSTR4",A."DTSTR5",A."DTSTR6",A."DTSTR7",A."DTSTR8",A."DTSTR9",A."DTDATA0",A."DTDATA1",A."DTDATA2",A."DTDATA3",A."DTDATA4",A."DTSUMAVAL0",A."DTSUMAVAL1",A."DTSUMAVAL2",A."DTSUMAVAL3",A."DTSUMAVAL4",A."DTVALUTA0",A."DTVALUTA1",A."DTVALUTA2",A."DTVALUTA3",A."DTVALUTA4",A."DTCANT0",A."DTCANT1",A."DTCANT2",A."DTCANT3",A."DTCANT4",A."DTCANT5",A."DTCANT6",A."DTCANT7",A."DTCANT8",A."DTCANT9",A."CT",A."CT1",A."CTSC0",A."CTSC1",A."CTSC2",A."CTSC3",A."CTSC4",A."CTSC5",A."CTSC6",A."CTSC7",A."CTSC8",A."CTSC9",A."CTNUM0",A."CTNUM1",A."CTNUM2",A."CTNUM3",A."CTNUM4",A."CTNUM5",A."CTNUM6",A."CTNUM7",A."CTNUM8",A."CTNUM9",A."CTSTR0",A."CTSTR1",A."CTSTR2",A."CTSTR3",A."CTSTR4",A."CTSTR5",A."CTSTR6",A."CTSTR7",A."CTSTR8",A."CTSTR9",A."CTDATA0",A."CTDATA1",A."CTDATA2",A."CTDATA3",A."CTDATA4",A."CTSUMAVAL0",A."CTSUMAVAL1",A."CTSUMAVAL2",A."CTSUMAVAL3",A."CTSUMAVAL4",A."CTVALUTA0",A."CTVALUTA1",A."CTVALUTA2",A."CTVALUTA3",A."CTVALUTA4",A."CTCANT0",A."CTCANT1",A."CTCANT2",A."CTCANT3",A."CTCANT4",A."CTCANT5",A."CTCANT6",A."CTCANT7",A."CTCANT8",A."CTCANT9"

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC0)CLCDTSC0T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC1)CLCDTSC1T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC2)CLCDTSC2T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC3)CLCDTSC3T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC4)CLCDTSC4T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC5)CLCDTSC5T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC6)CLCDTSC6T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC7)CLCDTSC7T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC8)CLCDTSC8T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC9)CLCDTSC9T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC0)CLCCTSC0T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC1)CLCCTSC1T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC2)CLCCTSC2T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC3)CLCCTSC3T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC4)CLCCTSC4T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC5)CLCCTSC5T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC6)CLCCTSC6T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC7)CLCCTSC7T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC8)CLCCTSC8T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC9)CLCCTSC9T

FROM TMDB_REG_A2 A;

CREATE OR REPLACE VIEW VMDB_REG_A2D

AS

SELECT A."NRDOC",A."NRDOC1",A."NRDOC2",A."NRDOC3",A."SUMA0",A."SUMA1",A."SUMA2",A."SUMA3",A."SUMA4",A."SUMA5",A."SUMA6",A."SUMA7",A."SUMA8",A."SUMA9",A."DT",A."DT1",A."DTSC0",A."DTSC1",A."DTSC2",A."DTSC3",A."DTSC4",A."DTSC5",A."DTSC6",A."DTSC7",A."DTSC8",A."DTSC9",A."DTNUM0",A."DTNUM1",A."DTNUM2",A."DTNUM3",A."DTNUM4",A."DTNUM5",A."DTNUM6",A."DTNUM7",A."DTNUM8",A."DTNUM9",A."DTSTR0",A."DTSTR1",A."DTSTR2",A."DTSTR3",A."DTSTR4",A."DTSTR5",A."DTSTR6",A."DTSTR7",A."DTSTR8",A."DTSTR9",A."DTDATA0",A."DTDATA1",A."DTDATA2",A."DTDATA3",A."DTDATA4",A."DTSUMAVAL0",A."DTSUMAVAL1",A."DTSUMAVAL2",A."DTSUMAVAL3",A."DTSUMAVAL4",A."DTVALUTA0",A."DTVALUTA1",A."DTVALUTA2",A."DTVALUTA3",A."DTVALUTA4",A."DTCANT0",A."DTCANT1",A."DTCANT2",A."DTCANT3",A."DTCANT4",A."DTCANT5",A."DTCANT6",A."DTCANT7",A."DTCANT8",A."DTCANT9",A."CT",A."CT1",A."CTSC0",A."CTSC1",A."CTSC2",A."CTSC3",A."CTSC4",A."CTSC5",A."CTSC6",A."CTSC7",A."CTSC8",A."CTSC9",A."CTNUM0",A."CTNUM1",A."CTNUM2",A."CTNUM3",A."CTNUM4",A."CTNUM5",A."CTNUM6",A."CTNUM7",A."CTNUM8",A."CTNUM9",A."CTSTR0",A."CTSTR1",A."CTSTR2",A."CTSTR3",A."CTSTR4",A."CTSTR5",A."CTSTR6",A."CTSTR7",A."CTSTR8",A."CTSTR9",A."CTDATA0",A."CTDATA1",A."CTDATA2",A."CTDATA3",A."CTDATA4",A."CTSUMAVAL0",A."CTSUMAVAL1",A."CTSUMAVAL2",A."CTSUMAVAL3",A."CTSUMAVAL4",A."CTVALUTA0",A."CTVALUTA1",A."CTVALUTA2",A."CTVALUTA3",A."CTVALUTA4",A."CTCANT0",A."CTCANT1",A."CTCANT2",A."CTCANT3",A."CTCANT4",A."CTCANT5",A."CTCANT6",A."CTCANT7",A."CTCANT8",A."CTCANT9"

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC0)CLCDTSC0T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC1)CLCDTSC1T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC2)CLCDTSC2T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC3)CLCDTSC3T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC4)CLCDTSC4T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC5)CLCDTSC5T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC6)CLCDTSC6T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC7)CLCDTSC7T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC8)CLCDTSC8T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.DTSC9)CLCDTSC9T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC0)CLCCTSC0T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC1)CLCCTSC1T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC2)CLCCTSC2T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC3)CLCCTSC3T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC4)CLCCTSC4T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC5)CLCCTSC5T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC6)CLCCTSC6T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC7)CLCCTSC7T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC8)CLCCTSC8T

,(SELECT DENUMIREA FROM TMS_UNIVERS WHERE COD=A.CTSC9)CLCCTSC9T

FROM TMDB_REG_A2D A;

Пакеты, процедуры и функции:

CREATE OR REPLACE PACKAGE RADIOCOM.yrdc_contracts AUTHID CURRENT_USER AS

/******************************************************************************

NAME: yrdc_contracts

PURPOSE:

REVISIONS:

Ver Date Author Description

- -- ---

1.0 28.10.2009 1. Created this package.

******************************************************************************/

-экшен "Дублирование данных из предыдущей даты"

PROCEDURE contr_dubl(p_nrdoc NUMBER,p_dataspec_new date,p_type_dubl number:=0);

-экшен "Рассчитать суммы"

PROCEDURE sum_account(p_nrdoc NUMBER,p_type_doc number:=0);

- экшен "Заполнить данными согласно контрактов"

PROCEDURE usl_calc(p_nrdoc NUMBER,p_sysfid NUMBER,p_client number:=-1,p_grp_name varchar2:=null);

- экшен "Расчитать НДС"

PROCEDURE calc_tva(p_nrdoc NUMBER,p_sysfid number);

PROCEDURE calc_tva_radiof(p_nrdoc NUMBER);

PROCEDURE calc_tva_telecom(p_nrdoc NUMBER);

PROCEDURE calc_tva_fst(p_nrdoc NUMBER);

PROCEDURE calc_contr_radiof(p_nrdoc NUMBER,p_sysfid NUMBER,p_client NUMBER,p_grp_name varchar2:=null,p_contr number:=null);

PROCEDURE calc_contr_telecom(p_nrdoc NUMBER,p_sysfid NUMBER);

PROCEDURE calc_contr_fst(p_nrdoc NUMBER,p_sysfid NUMBER);

-- action "Sgenerirovati nomera NN" v doc.1421

procedure gnr_NN_1421(p_nrdoc number, p_nrdoc1 number);

---

-- actioni "Заполнить из групы Servicii RD prestate populatiei" v doc.1421

procedure enter_servicii(p_nrdoc number);

---

-- action "Sgenerirovati nomera NN" v doc.1204

procedure gnr_NN_1204(p_nrdoc number, p_rrowid number);

заполнене 1422 документа. Все документы по услугам телекомуникаций за месяц (sysfid - 1411)

PROCEDURE enter_1422(p_nrdoc NUMBER, p_data DATE, p_client NUMBER);

procedure check_date(vdate date);

END yrdc_contracts;

/

CREATE OR REPLACE PACKAGE RADIOCOM.yrdc_contr_reps AS

/******************************************************************************

NAME: yrdc_contr_reps

PURPOSE:

REVISIONS:

Ver Date Author Description

- -- ---

1.0 02.02.2010 1. Created this package.

******************************************************************************/

отчет по истории спецификации, документов контарктов "(1413) Contract radioficare"

PROCEDURE rep_hist_spec(p_datastart date,p_dataend date,p_contr IN LONG,SQLH OUT LONG,SQLM OUT LONG);

PROCEDURE rep_serv_radio(p_datastart date,p_dataend date,p_contr IN LONG,SQLH IN OUT LONG,SQLM IN OUT LONG);

FUNCTION GET_CONTRACTS_DATA RETURN LONG;

END yrdc_contr_reps;



ПРИЛОЖЕНИЕ 2

Программный код для модуля контрактов

CREATE OR REPLACE PACKAGE BODY RADIOCOM.yrdc_contracts AS

/******************************************************************************

NAME: yrdc_contracts

PURPOSE:

REVISIONS:

Ver Date Author Description

- -- ---

1.0 28.10.2009 1. Created this package body.

******************************************************************************/

PROCEDURE contr_dubl(p_nrdoc NUMBER,p_dataspec_new date,p_type_dubl number:=0)

is

v_nrdoc1 number;

v_nrdoc1_new number;

begin

выбираем nrdoc1 у которого максимальная дата спецификации в документе

execute immediate 'select nrdoc1

from TMDB_reg_A a

where a.DTDATA0=(select max(DTDATA0) from TMDB_reg_A a1 where a.nrdoc=a1.nrdoc)

and a.nrdoc=:1 ' into v_nrdoc1 using p_nrdoc;

заполнение TMDB_reg_A

execute immediate '

insert into TMDB_reg_A

select NRDOC,ID_TMDB_CM.NEXTVAL nrdoc1,SUMA0, SUMA1, SUMA2, SUMA3, SUMA4, SUMA5, SUMA6, SUMA7, SUMA8, SUMA9, DT, DT1, DTSC0, DTSC1, DTSC2, DTSC3, DTSC4, DTSC5, DTSC6, DTSC7, DTSC8, DTSC9, DTNUM0, DTNUM1, DTNUM2, DTNUM3, DTNUM4, DTNUM5, DTNUM6, DTNUM7, DTNUM8, DTNUM9, DTSTR0, DTSTR1, DTSTR2, DTSTR3, DTSTR4, DTSTR5, DTSTR6, DTSTR7, DTSTR8, DTSTR9, '''||p_dataspec_new||''' DTDATA0, DTDATA1, DTDATA2, DTDATA3, DTDATA4, DTSUMAVAL0, DTSUMAVAL1, DTSUMAVAL2, DTSUMAVAL3, DTSUMAVAL4, DTVALUTA0, DTVALUTA1, DTVALUTA2, DTVALUTA3, DTVALUTA4, DTCANT0, DTCANT1, DTCANT2, DTCANT3, DTCANT4, DTCANT5, DTCANT6, DTCANT7, DTCANT8, DTCANT9, CT, CT1, CTSC0, CTSC1, CTSC2, CTSC3, CTSC4, CTSC5, CTSC6, CTSC7, CTSC8, CTSC9, CTNUM0, CTNUM1, CTNUM2, CTNUM3, CTNUM4, CTNUM5, CTNUM6, CTNUM7, CTNUM8, CTNUM9, CTSTR0, CTSTR1, CTSTR2, CTSTR3, CTSTR4, CTSTR5, CTSTR6, CTSTR7, CTSTR8, CTSTR9, CTDATA0, CTDATA1, CTDATA2, CTDATA3, CTDATA4, CTSUMAVAL0, CTSUMAVAL1, CTSUMAVAL2, CTSUMAVAL3, CTSUMAVAL4, CTVALUTA0, CTVALUTA1, CTVALUTA2, CTVALUTA3, CTVALUTA4, CTCANT0, CTCANT1, CTCANT2, CTCANT3, CTCANT4, CTCANT5, CTCANT6, CTCANT7, CTCANT8, CTCANT9

from TMDB_reg_A where nrdoc=:1 and nrdoc1=:2'

using p_nrdoc,v_nrdoc1;

заполнение TMDB_reg_A1

выбираем nrdoc1 новой даты спецификации

execute immediate 'select nrdoc1

from TMDB_reg_A a

where a.DTDATA0=:1

and a.nrdoc=:2 ' into v_nrdoc1_new using p_dataspec_new,p_nrdoc;

в ctnum9 кидаем порядковый номер строки,(понадобится для дальнейшего заполнения TMDB_reg_A1D)

execute immediate '

insert into TMDB_reg_A1

select NRDOC,'||v_nrdoc1_new||' NRDOC1,ID_TMDB_CM.NEXTVAL NRDOC2, SUMA0, SUMA1, SUMA2, SUMA3, SUMA4, SUMA5, SUMA6, SUMA7, SUMA8, SUMA9, DT, DT1, DTSC0, DTSC1, DTSC2, DTSC3, DTSC4, DTSC5, DTSC6, DTSC7, DTSC8, DTSC9, DTNUM0, DTNUM1, DTNUM2, DTNUM3, DTNUM4, DTNUM5, DTNUM6, DTNUM7, DTNUM8, DTNUM9, DTSTR0, DTSTR1, DTSTR2, DTSTR3, DTSTR4, DTSTR5, DTSTR6, DTSTR7, DTSTR8, DTSTR9, DTDATA0, DTDATA1, DTDATA2, DTDATA3, DTDATA4, DTSUMAVAL0, DTSUMAVAL1, DTSUMAVAL2, DTSUMAVAL3, DTSUMAVAL4, DTVALUTA0, DTVALUTA1, DTVALUTA2, DTVALUTA3, DTVALUTA4, DTCANT0, DTCANT1, DTCANT2, DTCANT3, DTCANT4, DTCANT5, DTCANT6, DTCANT7, DTCANT8, DTCANT9, CT, CT1, CTSC0, CTSC1, CTSC2, CTSC3, CTSC4, CTSC5, CTSC6, CTSC7, CTSC8, CTSC9, CTNUM0, CTNUM1, CTNUM2, CTNUM3, CTNUM4, CTNUM5, CTNUM6, CTNUM7, CTNUM8,row_number()over(order by nrdoc2) CTNUM9, CTSTR0, CTSTR1, CTSTR2, CTSTR3, CTSTR4, CTSTR5, CTSTR6, CTSTR7, CTSTR8, CTSTR9, CTDATA0, CTDATA1, CTDATA2, CTDATA3, CTDATA4, CTSUMAVAL0, CTSUMAVAL1, CTSUMAVAL2, CTSUMAVAL3, CTSUMAVAL4, CTVALUTA0, CTVALUTA1, CTVALUTA2, CTVALUTA3, CTVALUTA4, CTCANT0, CTCANT1, CTCANT2, CTCANT3, CTCANT4, CTCANT5, CTCANT6, CTCANT7, CTCANT8, CTCANT9

from TMDB_reg_A1 where nrdoc=:1 and nrdoc1=:2

using p_nrdoc,v_nrdoc1;

if p_type_dubl=0 then

заполнение TMDB_reg_A1D

for i in (select nrdoc2,ctnum9 from TMDB_reg_A1 where nrdoc=p_nrdoc and nrdoc1=v_nrdoc1_new)loop

execute immediate '

insert into TMDB_reg_A1D

select NRDOC,'||v_nrdoc1_new||' NRDOC1,'||i.nrdoc2||' NRDOC2,ID_TMDB_CM.NEXTVAL NRDOC3, SUMA0, SUMA1, SUMA2, SUMA3, SUMA4, SUMA5, SUMA6, SUMA7, SUMA8, SUMA9, DT, DT1, DTSC0, DTSC1, DTSC2, DTSC3, DTSC4, DTSC5, DTSC6, DTSC7, DTSC8, DTSC9, DTNUM0, DTNUM1, DTNUM2, DTNUM3, DTNUM4, DTNUM5, DTNUM6, DTNUM7, DTNUM8, DTNUM9, DTSTR0, DTSTR1, DTSTR2, DTSTR3, DTSTR4, DTSTR5, DTSTR6, DTSTR7, DTSTR8, DTSTR9, DTDATA0, DTDATA1, DTDATA2, DTDATA3, DTDATA4, DTSUMAVAL0, DTSUMAVAL1, DTSUMAVAL2, DTSUMAVAL3, DTSUMAVAL4, DTVALUTA0, DTVALUTA1, DTVALUTA2, DTVALUTA3, DTVALUTA4, DTCANT0, DTCANT1, DTCANT2, DTCANT3, DTCANT4, DTCANT5, DTCANT6, DTCANT7, DTCANT8, DTCANT9, CT, CT1, CTSC0, CTSC1, CTSC2, CTSC3, CTSC4, CTSC5, CTSC6, CTSC7, CTSC8, CTSC9, CTNUM0, CTNUM1, CTNUM2, CTNUM3, CTNUM4, CTNUM5, CTNUM6, CTNUM7, CTNUM8, CTNUM9, CTSTR0, CTSTR1, CTSTR2, CTSTR3, CTSTR4, CTSTR5, CTSTR6, CTSTR7, CTSTR8, CTSTR9, CTDATA0, CTDATA1, CTDATA2, CTDATA3, CTDATA4, CTSUMAVAL0, CTSUMAVAL1, CTSUMAVAL2, CTSUMAVAL3, CTSUMAVAL4, CTVALUTA0, CTVALUTA1, CTVALUTA2, CTVALUTA3, CTVALUTA4, CTCANT0, CTCANT1, CTCANT2, CTCANT3, CTCANT4, CTCANT5, CTCANT6, CTCANT7, CTCANT8, CTCANT9

from TMDB_reg_A1D d where nrdoc=:1 and nrdoc1=:2

and nrdoc2=(select nrdoc2

from (select nrdoc2,row_number()over(order by nrdoc2) rn from TMDB_reg_A1 a

where a.nrdoc='||p_nrdoc||' and a.nrdoc1='||v_nrdoc1||' )a

where rn=:3)

using p_nrdoc,v_nrdoc1,i.ctnum9;

end loop;

end if;

end;

PROCEDURE sum_account(p_nrdoc NUMBER,p_type_doc number:=0)

is

v_nrdoc1 number;

v_sysfid number;

/*v_tipcalc integer;*/

begin

выбираем nrdoc1 у которого максимальная дата спецификации в документе

execute immediate 'select nrdoc1

from TMDB_reg_A a

where a.DTDATA0=(select max(DTDATA0) from TMDB_reg_A a1 where a.nrdoc=a1.nrdoc)

and a.nrdoc=:1 ' into v_nrdoc1 using p_nrdoc;

--

-

execute immediate 'select sysfid

from TMDB_DOCS a where cod=:1' into v_sysfid using p_nrdoc;

case

(1406) Contract Telecomunicatii

when v_sysfid=1406 then

begin

for i in(select nrdoc,nrdoc1,nrdoc2,dtnum1 from TMDB_reg_A1 where nrdoc=p_nrdoc and nrdoc1=v_nrdoc1) loop

if i.dtnum1=1 then

update TMDB_reg_A1D a1D

set suma1=(case when (select u.gr1 from vms_univers u where u.cod=a1d.dtsc0)='LRR'

then a1d.dtcant0*a1d.dtcant2*a1d.dtcant3

else a1d.dtcant2*a1d.dtcant3 end)

where nrdoc=p_nrdoc and nrdoc1= v_nrdoc1 and nrdoc2=i.nrdoc2;

--' using p_nrdoc,v_nrdoc1,i.nrdoc2;

else

execute immediate 'update TMDB_reg_A1D a1D

set suma1=a1d.dtcant3

where nrdoc=:1 and nrdoc1=:2 and nrdoc2=:3

' using p_nrdoc,v_nrdoc1,i.nrdoc2;

end if;

execute immediate 'update TMDB_reg_A1 a1

set SUMA1=(select sum(SUMA1) from TMDB_reg_A1D d

where a1.nrdoc=d.nrdoc and a1.nrdoc1=d.nrdoc1 and a1.nrdoc2=d.nrdoc2 )

where a1.nrdoc=:1 and a1.nrdoc1=:2 and a1.nrdoc2=:3

' using i.nrdoc,i.nrdoc1,i.nrdoc2;

end loop;

execute immediate 'update TMDB_reg r

set suma2=(select sum(suma1) from TMDB_reg_A1 a1 where r.nrdoc=a1.nrdoc and a1.nrdoc1=:1)

where nrdoc=:2

'using v_nrdoc1,p_nrdoc;

end;

when v_sysfid=1408 then

begin

execute immediate 'update TMDB_reg r

set suma2=(select sum(suma1) from TMDB_reg_A1 a1 where r.nrdoc=a1.nrdoc and a1.nrdoc1=:1)

where nrdoc=:2

'using v_nrdoc1,p_nrdoc;

end;

when v_sysfid=1409 then

begin

for i in(select nrdoc,nrdoc1,nrdoc2 from TMDB_reg_A1 where nrdoc=p_nrdoc and nrdoc1=v_nrdoc1) loop

execute immediate 'update TMDB_reg_A1 a1

set dtcant0=(select sum(SUMA0) from TMDB_reg_A1D d

where a1.nrdoc=d.nrdoc and a1.nrdoc1=d.nrdoc1 and a1.nrdoc2=d.nrdoc2 )

where a1.nrdoc=:1 and a1.nrdoc1=:2 and a1.nrdoc2=:3

' using i.nrdoc,i.nrdoc1,i.nrdoc2;

end loop;

end;

when v_sysfid=1407 or v_sysfid=1413 then

begin

for i in(select nrdoc,nrdoc1,nrdoc2 from TMDB_reg_A1

where nrdoc=p_nrdoc and nrdoc1=v_nrdoc1) loop

execute immediate 'update TMDB_reg_A1 a1

set suma1=dtcant0*suma0

where a1.nrdoc=:1 and a1.nrdoc1=:2 and a1.nrdoc2=:3

' using i.nrdoc,i.nrdoc1,i.nrdoc2;

end loop;

end;

else msg('Данный экшен не предназначен для этого типа документа');

end case;

end;

PROCEDURE usl_calc(p_nrdoc NUMBER,p_sysfid NUMBER,p_client number:=-1,p_grp_name varchar2:=null)

is

s long;

v_contr number;

begin

очистка документа

if p_sysfid not in(1406,1409) then

если sysfid=1413 и sys_context('envun4','filt_contract')=1, перед удалением берем значение контракта(из шапки ctnum0)

if (p_sysfid=1413 or p_sysfid=1407) and sys_context('envun4','filt_contract')=1 then

execute immediate 'select CTNUM0 from tmdb_reg_a where nrdoc='||p_nrdoc into v_contr;

end if;

execute immediate 'delete from TMDB_REG_A where nrdoc=:1' using p_nrdoc;

end if;

execute immediate 'delete from TMDB_REG_A2 where nrdoc=:1' using p_nrdoc;

execute immediate 'delete from TMDB_REG_A2D where nrdoc=:1' using p_nrdoc;

case

-contract Suporturi radioficare and contract radioficare

when p_sysfid in(1407,1413) then yrdc_contracts.calc_contr_radiof(p_nrdoc,p_sysfid,p_client,p_grp_name,v_contr);

-contract telecomunicatii

when p_sysfid=1406 then yrdc_contracts.calc_contr_telecom(p_nrdoc,p_sysfid);

-contract FST

when p_sysfid=1409 then yrdc_contracts.calc_contr_fst(p_nrdoc,p_sysfid);

else null;

end case;

end;

PROCEDURE calc_tva(p_nrdoc NUMBER,p_sysfid number)

is

begin

case

when p_sysfid=1407 or p_sysfid=1413 then yrdc_contracts.calc_tva_radiof(p_nrdoc);

when p_sysfid=1406 then yrdc_contracts.calc_tva_telecom(p_nrdoc);

when p_sysfid=1409 then yrdc_contracts.calc_tva_fst(p_nrdoc);

end case;

end;

PROCEDURE calc_tva_radiof(p_nrdoc NUMBER) is

v_clcmonths varchar2(10):='';

begin

for i in (select nrdoc1,dtnum2,DTNUM4,nvl(SUMA3,0) suma3 from VMDB_REG_A where nrdoc=p_nrdoc) loop

case

when i.DTNUM4=1 then v_clcmonths:='3 ';

when i.DTNUM4=3 then v_clcmonths:='6 ';

else v_clcmonths:='1 ';

end case;

if i.dtnum2=1 then

execute immediate '

update TMDB_REG_A2 a2

set

--TVA

SUMA3=(DECODE(DTCANT1,NULL,'||v_clcmonths||',DTCANT1))*DTCANT0*SUMA0*/*(*/SUMA2/100/*/(1+SUMA2/100))*/

--Suma fara TVA

,SUMA1=(DECODE(DTCANT1,NULL,'||v_clcmonths||',DTCANT1))*DTCANT0*SUMA0--*1/(SUMA2/100+1)

where nrdoc=:1 and nrdoc1=:2

' using p_nrdoc,i.nrdoc1;

else

execute immediate '

update TMDB_REG_A2 a2

set

--TVA

SUMA3=(DECODE(DTCANT1,NULL,'||v_clcmonths||',DTCANT1))*DTCANT0*SUMA0*(SUMA2/100)

--Suma fara TVA

,SUMA1=(DECODE(DTCANT1,NULL,'||v_clcmonths||',DTCANT1))*DTCANT0*SUMA0/**(SUMA2/100+1)*/

where nrdoc=:1 and nrdoc1=:2

' using p_nrdoc,i.nrdoc1;

end if;

end loop;

--TOTAL

execute immediate '

update TMDB_REG_A2 a2

set SUMA4=SUMA3+SUMA1

where nrdoc=:1 ' using p_nrdoc;

end;

PROCEDURE calc_tva_telecom(p_nrdoc NUMBER)is

v_val_contr varchar2(70):='';

v_datadoc date:=abm_util.data_by_nrdoc(p_nrdoc);

begin

for i in (select nrdoc1,nrdoc2,dtnum2,dtnum1,dtvaluta0,dtvaluta1 from VMDB_REG_A2 where nrdoc=p_nrdoc) loop

if upper(i.DTVALUTA1)<>'LEI' then

v_val_contr:='un$valuta.GETCURS('''||v_datadoc||''','''||i.DTVALUTA1||''',2)* ';

if i.dtnum1=1 then

execute immediate '

update TMDB_REG_A2D a2

set

--Plata pentru cap.deplina (lei)

suma0=(case when (select u.gr1 from vms_univers u where u.cod=a2.dtsc0)=''LRR''

then nvl(DTCANT1,0)*nvl(DTCANT3,0)*nvl(DTCANT0,0)

else nvl(DTCANT1,0)*nvl(DTCANT3,0) end)

--Plata pentru 1/2 de capacitate (lei)

,SUMA5=(case when (select u.gr1 from vms_univers u where u.cod=a2.dtsc0)=''LRR''

then nvl(DTCANT2,0)*nvl(DTCANT4,0)*nvl(DTCANT0,0)

else nvl(DTCANT2,0)*nvl(DTCANT4,0)end)

--Suma fara TVA

,SUMA1=(case when (select u.gr1 from vms_univers u where u.cod=a2.dtsc0)=''LRR''

then '||v_val_contr||'nvl(DTCANT1,0)*nvl(DTCANT3,0)*nvl(DTCANT0,0)

else '||v_val_contr||'nvl(DTCANT1,0)*nvl(DTCANT3,0) end)+decode('||nvl(i.dtnum1,1)||',1,(case when (select u.gr1 from vms_univers u where u.cod=a2.dtsc0)=''LRR''

then '||v_val_contr||'nvl(DTCANT2,0)*nvl(DTCANT4,0)*nvl(DTCANT0,0)

else '||v_val_contr||'nvl(DTCANT2,0)*nvl(DTCANT4,0)end),0)

where nrdoc=:1 and nrdoc1=:2 and nrdoc2=:3

' using p_nrdoc,i.nrdoc1,i.nrdoc2;

else

execute immediate '

update TMDB_REG_A2D a2

set

suma0=nvl(DTCANT1,0)

,SUMA1='||v_val_contr||'nvl(DTCANT1,0)

where nrdoc=:1 and nrdoc1=:2 and nrdoc2=:3

' using p_nrdoc,i.nrdoc1,i.nrdoc2;

end if;

end if;

if i.dtnum2=1 then

execute immediate '

update TMDB_REG_A2D a2

set

--TVA

SUMA3=SUMA1*(SUMA2/100/(1+SUMA2/100))

where nrdoc=:1 and nrdoc1=:2 and nrdoc2=:3

' using p_nrdoc,i.nrdoc1,i.nrdoc2;

else

execute immediate '

update TMDB_REG_A2D a2

set

--TVA

SUMA3=SUMA1*(SUMA2/100)

where nrdoc=:1 and nrdoc1=:2 and nrdoc2=:3

' using p_nrdoc,i.nrdoc1,i.nrdoc2;

end if;

end loop;

--TOTAL

execute immediate '

update TMDB_REG_A2D a2

set SUMA4=SUMA3+SUMA1

where nrdoc=:1 ' using p_nrdoc;

end;

PROCEDURE calc_tva_fst(p_nrdoc NUMBER)is

begin

execute immediate '

update TMDB_REG_A2 a2

set

--Suma fara tva

SUMA1=nvl(suma0,0)+nvl(dtcant0,0)

where nrdoc=:1

' using p_nrdoc;

execute immediate '

update TMDB_REG_A2 a2

set

--Suma TVA

SUMA3=nvl(suma1,0)*nvl(suma2,0)/100

--Suma Total

,SUMA4=nvl(suma1,0)+nvl(suma1,0)*nvl(suma2,0)/100

where nrdoc=:1

' using p_nrdoc;

end;

sysfid in(1410,1415)

PROCEDURE calc_contr_radiof(p_nrdoc NUMBER,p_sysfid NUMBER,p_client NUMBER,p_grp_name varchar2:=null,p_contr number:=null)

is

v_tip_prest_srv varchar2(70):='';

v_client_flt varchar2(70):= '';

v_val_contr varchar2(70):='';

v_clcmonths varchar2(10):='';

v_filtgrp varchar2(100):='';

v_cond_contr varchar2(70):='';

s long;

v_datadoc date:=abm_util.data_by_nrdoc(p_nrdoc);

---исключения-

miss_expr exception;

pragma exception_init(miss_expr,-936);

begin

проверка на sysfid. если sys_context('envun4','filt_contract')=1,применяем фильтр на контракт

if sys_context('envun4','filt_contract')=1 then

v_cond_contr:=' and nrdoc='||p_contr;

end if;

--

добавление условия на Tipul prestarii SRV для p_sysfid=1407

case

when to_char(abm_util.data_by_nrdoc(p_nrdoc),'mm') in (6,12) then v_tip_prest_srv:=' ';

when to_char(abm_util.data_by_nrdoc(p_nrdoc),'mm') in (3,6,9,12) then v_tip_prest_srv:=' and nvl(c.DTNUM4,2) in(1,2) ';

else v_tip_prest_srv:=' and nvl(c.DTNUM4,2) in(2) ';

end case;

проверка на существование фильтра по клиентам(если -1 то все клиенты, иначе только p_client)

if p_client<>-1 then

v_client_flt:=' and c.dtsc0='||p_client;

end if;

проверка на существование параметра группы контрагентов(если не null,то существует)

if p_grp_name is not null then

v_filtgrp:=' and c.dtsc0 in(select sc from TMS_SYSGRP where GROUP1||GROUP2||GROUP3||GROUP4||GROUP5='||p_grp_name||') ';

-- say('v_filtgrp='||v_filtgrp);

end if;

заполнение первого грида(CTNUM0, CTNUM1 - nrdoc,nrdoc1 документов контрактов 1407 )

execute immediate 'insert into TMDB_REG_A(NRDOC, NRDOC1, DTSC0, DTNUM2, DTNUM4, DTSTR0, DTSTR2, DTSTR4, DTDATA0, DTVALUTA0, DTVALUTA1,CTNUM0, CTNUM1/*,SUMA3*/)

select '||p_nrdoc||' nrdoc, ID_TMDB_CM.NEXTVAL NRDOC1

,c.dtsc0,c.DTNUM2,c.DTNUM4

, c.DTSTR0'||'||'||'''/'''||'||'||'c.nrdoc, c.CLCDTNUM2T, c.CLCDTNUM4T

,(select max(DTDATA0) from TMDB_reg_A a where a.nrdoc=c.nrdoc) DTDATA0

, c.DTVALUTA0, c.DTVALUTA1

,c.nrdoc CTNUM0

,(select nrdoc1 from TMDB_reg_A a where a.nrdoc=c.nrdoc

and DTDATA0=(select max(DTDATA0) from TMDB_reg_A a1 where a.nrdoc=a1.nrdoc) ) CTNUM1

/*,C.SUMA3*/

from yrdc_contract c

where '''||abm_util.data_by_nrdoc(p_nrdoc)||''' between dtdata1 and dtdata2 '||v_tip_prest_srv||'

and exists(select 1 from vmdb_docs d where d.cod=c.nrdoc and sysfid='||p_sysfid||')

'||v_client_flt||' '||v_filtgrp||' '||v_cond_contr||'

';

заполнение второго грида(CTNUM0, CTNUM1, CTNUM2 - nrdoc,nrdoc1,nrdoc2 документов контрактов 1407 )

for i in(select * from TMDB_REG_A a1 where nrdoc=p_nrdoc ) loop

если валюта контракта не лей, то расчет по курсу на дату документа

if upper(i.DTVALUTA0)<>'LEI' then

v_val_contr:='un$valuta.GETCURS('''||v_datadoc||''','''||i.DTVALUTA0||''',2)* ';

end if;

умножение на количество месяцев (возможные значения: 1,3,6)

case

when i.DTNUM4=1 then v_clcmonths:='3* ';

when i.DTNUM4=3 then v_clcmonths:='6* ';

else v_clcmonths:='1* ';

end case;

begin

execute immediate 'insert into TMDB_REG_A2(NRDOC, NRDOC1, NRDOC2,DTSC0, DTSC2, DTSC3, DTCANT0,

DTSTR0,SUMA0, SUMA2,CTNUM0, CTNUM1, CTNUM2,DTSTR4,DTNUM4,dtdata1)

select '||p_nrdoc||' nrdoc

,(select nrdoc1 from TMDB_REG_A a where a.ctnum0=a1.nrdoc and a.ctnum1=a1.nrdoc1 and a.nrdoc='||i.nrdoc||') nrdoc1

, ID_TMDB_CM.NEXTVAL NRDOC2

,a1.dtsc0,a1.dtsc2,a1.dtsc3,a1.dtcant0,a1.dtstr0

,'||v_val_contr||' case when '||i.dtnum2||'=1 then a1.suma0*(100/(UN$FUNCTS.TVA(a1.DTSC0)*100+100)) else a1.suma0 end suma0

,UN$FUNCTS.TVA(a1.DTSC0)*100 suma2

,a1.nrdoc ctnum0,a1.nrdoc1 ctnum1,a1.nrdoc2 ctnum2

,a1.dtstr4,a1.dtnum4

,decode('||i.dtnum4||',2,(select LAST_DAY(ADD_MONTHS((select datamanual from vmdb_docs where cod='||p_nrdoc||'),0)) from dual),null)

from TMDB_reg_A1 a1

where a1.nrdoc='||i.ctnum0||' and a1.nrdoc1='||i.ctnum1||'

';

exception

-missing expression

when miss_expr then

msg('В контракте '||i.ctnum0||' не указано значение поля НДС(Date cu privire la TVA)');

end;

end loop;

end;

sysfid=1411

PROCEDURE calc_contr_telecom(p_nrdoc NUMBER,p_sysfid NUMBER)

is

v_contr number;

v_client number;

v_dataspec_actual date;

v_dataspec_prev date;

v_open_curs varchar2(250);

v_datadoc date:=abm_util.data_by_nrdoc(p_nrdoc);

cr sys_refcursor;

type rec is record(

nrdoc1 number,dtdata0 date

);

in_rec rec;

s long;

begin

execute immediate 'select CTNUM0,dtsc0 from tmdb_reg_a where nrdoc='||p_nrdoc into v_contr,v_client;

v_open_curs:='select nrdoc1,DTDATA0 from tmdb_reg_a where nrdoc='||v_contr||' ';

выбираем максимальную дату контракта, не превышающую месяца документа расчета доходов услуг

execute immediate 'select max(DTDATA0) from tmdb_reg_a where nrdoc=:1

and trunc(DTDATA0,''mm'')<=:2'

into v_dataspec_actual using v_contr,trunc(abm_util.data_by_nrdoc(p_nrdoc),'mm');

if v_dataspec_actual is null then

msg('Внимание!'||'В выбранном контракте дата спецификации отсутствует либо не является актуальной для данного месяца.');

end if;

выбираем максимальную дату спецификации контракта за предыдущие месяцы

execute immediate 'select max(DTDATA0) from tmdb_reg_a where nrdoc=:1

and to_char(DTDATA0,''mm.yyyy'')<:2'

into v_dataspec_prev using v_contr,to_char(abm_util.data_by_nrdoc(p_nrdoc),'mm.yyyy');

формируем динамически курсор

case when v_dataspec_prev is null

then v_open_curs:=v_open_curs||' and dtdata0<='''||v_dataspec_actual||''' ';

when v_dataspec_prev=v_dataspec_actual

then v_open_curs:=v_open_curs||' and dtdata0='''||v_dataspec_actual||''' ';

else v_open_curs:=v_open_curs||' and dtdata0 between '''||v_dataspec_prev||''' and '''||v_dataspec_actual||''' ';

end case;

OPEN cr FOR v_open_curs;

LOOP

FETCH cr INTO in_rec;

EXIT WHEN cr%NOTFOUND;

заполнение первого грида(CTNUM0, CTNUM1, CTNUM2 - nrdoc,nrdoc1,nrdoc2 документов контрактов 1406 )

execute immediate 'insert into TMDB_REG_A2(NRDOC, NRDOC1, NRDOC2,DTSC0, DTSC2, DTSC3

DTDATA0, DTNUM1, DTNUM2, DTSTR1, DTSTR2,DTVALUTA0,DTVALUTA1,CTNUM0, CTNUM1, CTNUM2)

select '||p_nrdoc||' nrdoc

,(select nrdoc1 from TMDB_REG_A a where a.nrdoc='||p_nrdoc||' ) nrdoc1

, ID_TMDB_CM.NEXTVAL NRDOC2

,a1.dtsc0,a1.dtsc2,a1.dtsc3,'''||in_rec.DTDATA0||''' DTDATA0

,a1.DTNUM1

,(select c.dtnum2 from yrdc_contract c where c.nrdoc=a1.nrdoc ) dtnum2

,a1.DTSTR1

,(select c.clcdtnum2t from yrdc_contract c where c.nrdoc=a1.nrdoc ) dtstr2

,(select c.DTVALUTA0 from yrdc_contract c where c.nrdoc=a1.nrdoc ) DTVALUTA0

,(select c.DTVALUTA1 from yrdc_contract c where c.nrdoc=a1.nrdoc ) DTVALUTA1

,a1.nrdoc ctnum0,a1.nrdoc1 ctnum1,a1.nrdoc2 ctnum2

from TMDB_reg_A1 a1

where a1.nrdoc='||v_contr||' and a1.nrdoc1='||in_rec.nrdoc1||'

end loop;

close cr;

заполнение второго грида(CTNUM0, CTNUM1, CTNUM2, CTNUM3 - nrdoc,nrdoc1,nrdoc2,nrdoc3 документов контрактов 1406 )

for i in(select nrdoc,nrdoc1,nrdoc2,CTNUM0, CTNUM1, CTNUM2,dtnum2,dtsc0 from TMDB_REG_A2 where nrdoc=p_nrdoc) loop

execute immediate 'insert into TMDB_REG_A2D(NRDOC, NRDOC1, NRDOC2,NRDOC3

,DTSC0,DTNUM2,DTNUM4,DTSTR0,DTSTR2,DTSTR4,DTCANT0,DTCANT1,DTCANT2,SUMA2

,CTNUM0, CTNUM1, CTNUM2, CTNUM3)

select '||i.nrdoc||' nrdoc,'||i.nrdoc1||' nrdoc1, '||i.nrdoc2||' nrdoc2

, ID_TMDB_CM.NEXTVAL NRDOC3

,d.DTSC0,d.DTNUM2,d.DTNUM4,d.DTSTR0,d.DTSTR2,d.DTSTR4

,d.DTCANT0

,case when '||i.dtnum2||'=1 then d.DTCANT3*(100/(UN$FUNCTS.TVA('||i.DTSC0||','||v_client||')*100+100)) else d.DTCANT3 end DTCANT1

,case when '||i.dtnum2||'=1 then d.DTCANT3*(100/(UN$FUNCTS.TVA('||i.DTSC0||','||v_client||')*100+100)) else d.DTCANT3 end*0.75 DTCANT2

,UN$FUNCTS.TVA('||i.DTSC0||','||v_client||')*100 suma2

,d.nrdoc CTNUM0,d.nrdoc1 CTNUM1,d.nrdoc2 CTNUM2,d.nrdoc3 CTNUM3

from VMDB_reg_A1D d

where d.nrdoc='||i.CTNUM0||' and d.nrdoc1='||i.CTNUM1||' and d.nrdoc2='||i.CTNUM2||'

';

end loop;

--say(v_open_curs);msg('');

end;

sysfid=1412

PROCEDURE calc_contr_fst(p_nrdoc NUMBER,p_sysfid NUMBER)

is

v_contr number;

v_dataspec_actual date;

v_val_contr varchar2(70):='';

v_clcmonths varchar2(10):='';

v_rec varchar2(250);

type rec is record(

nrdoc1 number,dtdata0 date,dtvaluta0 char(3)

,dtnum2 number --Date privitor la TVA

,dtnum4 number --Tipul srv prestat

);

in_rec rec;

begin

execute immediate 'select CTNUM0 from tmdb_reg_a where nrdoc='||p_nrdoc into v_contr;

v_rec:='select a.nrdoc1,a.DTDATA0,c.dtvaluta0,c.dtnum2,c.dtnum4 from tmdb_reg_a a,yrdc_contract c where a.nrdoc='||v_contr||' and c.nrdoc=a.nrdoc';

update шапки документа, приносим валюту и тип НДС

execute immediate 'update tmdb_reg_a a

set (dtvaluta0,dtvaluta1,dtstr2,dtnum2,dtstr4,dtnum4)=

(select dtvaluta0,dtvaluta1,CLCDTNUM2T,DTNUM2,clcdtnum4t,dtnum4 from yrdc_contract c where c.nrdoc=:1)

where a.nrdoc=:2

' using v_contr,p_nrdoc;

выбираем максимальную дату контракта, не превышающую месяца документа расчета доходов услуг

execute immediate 'select max(DTDATA0) from tmdb_reg_a where nrdoc=:1

and to_char(DTDATA0,''mm.yyyy'')<=:2'

into v_dataspec_actual using v_contr,to_char(abm_util.data_by_nrdoc(p_nrdoc),'mm.yyyy');

if v_dataspec_actual is null then

msg('Внимание!'||'В выбранном контракте дата спецификации отсутствует либо не является актуальной для данного месяца.');

end if;

формируем динамически запрос для записи

v_rec:=v_rec||' and a.dtdata0='''||v_dataspec_actual||''' ';

execute immediate v_rec INTO in_rec;

если валюта контракта не лей, то расчет по курсу на дату документа

if upper(in_rec.DTVALUTA0)<>'LEI' then

v_val_contr:='un$valuta.GETCURS('''||abm_util.data_by_nrdoc(p_nrdoc)||''','''||in_rec.DTVALUTA0||''',2)* ';

end if;

умножение на количество месяцев (возможные значения: 1,3,6)

case

when in_rec.DTNUM4=1 then v_clcmonths:='3* ';

when in_rec.DTNUM4=3 then v_clcmonths:='6* ';

else v_clcmonths:='1* ';

end case;

заполнение первого грида(CTNUM0, CTNUM1, CTNUM2 - nrdoc,nrdoc1,nrdoc2 документов контрактов 1409 )

execute immediate 'insert into TMDB_REG_A2(NRDOC, NRDOC1, NRDOC2,DTSC0, DTSC2, DTSC3

, DTDATA0, DTNUM0, DTSTR0 /*, DTCANT0 */, DTCANT1, DTCANT2, SUMA0, SUMA1, SUMA2, CTNUM0, CTNUM1, CTNUM2)

select '||p_nrdoc||' nrdoc

,(select nrdoc1 from TMDB_REG_A a where a.nrdoc='||p_nrdoc||' ) nrdoc1

, ID_TMDB_CM.NEXTVAL NRDOC2

,a1.dtsc0,a1.dtsc2,a1.dtsc3,'''||in_rec.DTDATA0||''' DTDATA0

,a1.dtnum0

,a1.dtstr0

/*,a1.DTCANT0*/,a1.DTCANT1,a1.DTCANT2

,'||v_val_contr||v_clcmonths||' case when '||in_rec.DTNUM2||'=1 then a1.suma0/(1+UN$FUNCTS.TVA(a1.DTSC0)) else a1.suma0 end suma0

,'||v_val_contr||v_clcmonths||' case when '||in_rec.DTNUM2||'=1 then a1.suma0/(1+UN$FUNCTS.TVA(a1.DTSC0)) else a1.suma0 end+nvl(a1.DTCANT0,0) suma1

,UN$FUNCTS.TVA(a1.DTSC0)*100 suma2

,a1.nrdoc ctnum0,a1.nrdoc1 ctnum1,a1.nrdoc2 ctnum2

from TMDB_reg_A1 a1

where a1.nrdoc='||v_contr||' and a1.nrdoc1='||in_rec.nrdoc1||'

';

заполнение второго грида(CTNUM0, CTNUM1, CTNUM2, CTNUM3 - nrdoc,nrdoc1,nrdoc2,nrdoc3 документов контрактов 1409 )

for i in(select nrdoc,nrdoc1,nrdoc2,CTNUM0, CTNUM1, CTNUM2,dtsc0 from TMDB_REG_A2 where nrdoc=p_nrdoc) loop

execute immediate 'insert into TMDB_REG_A2D(NRDOC, NRDOC1, NRDOC2,NRDOC3

,DTSC0,DTNUM2,DTNUM3,DTSTR2,DTSTR3,DTCANT0,DTDATA0,DTDATA1,SUMA0--,SUMA1

,CTNUM0, CTNUM1, CTNUM2, CTNUM3)

select '||i.nrdoc||' nrdoc,'||i.nrdoc1||' nrdoc1, '||i.nrdoc2||' nrdoc2

, ID_TMDB_CM.NEXTVAL NRDOC3

,d.DTSC0,d.DTNUM2,d.DTNUM3,d.DTSTR2,d.DTSTR3

,d.DTCANT0,d.DTDATA0

,nvl(d.DTDATA1,(select c.DTDATA2 from yrdc_contract c where c.nrdoc=d.nrdoc)) DTDATA1

,d.SUMA1

,d.nrdoc CTNUM0,d.nrdoc1 CTNUM1,d.nrdoc2 CTNUM2,d.nrdoc3 CTNUM3

from VMDB_reg_A1D d

where d.nrdoc='||i.CTNUM0||' and d.nrdoc1='||i.CTNUM1||' and d.nrdoc2='||i.CTNUM2||'

';

end loop;

end;

actioni "Sgenerirovati nomera NN" v doc.1421

procedure gnr_NN_1421(p_nrdoc number, p_nrdoc1 number)is

vseriaNN varchar2(2);

vnrNN number;

vnr_prod number;

vrn number;

begin

select dtstr8, dtstr9, CTNUM0

into vseriaNN,vnrNN,vnr_prod

from VMDB_REG_A

where nrdoc=p_nrdoc

and nrdoc1 = p_nrdoc1;

if vseriaNN is null or vnrNN is null then

msg(lng('Indicati seria si numarul FF pentru doc. '||vnr_prod||'!','Укажите серию и номер НН для документа '||vnr_prod||'!'));

end if;

delete from xsldx where id=1;

insert into xsldx (id, num2, cant1, str1,num1, str0, num3)

select 1, CTNUM0, nrdoc1, dtstr8, dtstr9

, clcdtsc0t, row_number() over (order by dtsc0) rn

from VMDB_REG_A

where nrdoc=p_nrdoc;

select num3 into vrn

from xsldx where id=1 and cant1=p_nrdoc1;

update xsldx x set str1=vseriaNN, num1=vnrNN+x.num3-vrn

where id=1 and num3>vrn

and vnrNN+x.num3-vrn<=9999999;

--

update VMDB_REG_A a

set dtstr8 = null, dtstr9 = null

where exists (select * from xsldx where id=1 and num2=a.CTNUM0);

update VMDB_REG_A a

set (dtstr8, dtstr9)=(select str1, num1 from xsldx where id=1 and num2=a.CTNUM0)

where exists (select * from xsldx where id=1 and num2=a.CTNUM0);

delete from VMDB01M_VINZ

where cod in (select CTNUM0 from VMDB_REG_A where nrdoc = p_nrdoc);

insert into VMDB01M_VINZ (cod, PRTVA_SERIA, PRTVA_NR, PRTVA_DATA)

select CTNUM0, dtstr8, dtstr9, sysdate from VMDB_REG_A

where nrdoc = p_nrdoc;

end;

actioni "Заполнить из групы Servicii RD prestate populatiei" v doc.1421

procedure enter_servicii(p_nrdoc number) IS

vDataDocPrev DATE;

vSYSFID INTEGER;

vDataDoc DATE;

BEGIN

execute immediate 'delete from VMDB_REG_A where nrdoc='||p_nrdoc;

execute immediate 'delete from VMDB_REG_A2 where nrdoc='||p_nrdoc;

SELECT datamanual,sysfid INTO vDataDoc,vSysFid

FROM VMDB_DOCS S WHERE S.cod = p_nrdoc;

SELECT MAX(datamanual) INTO vDataDocPrev

FROM VMDB_DOCS d, VMDB_REG_A a

WHERE nrdoc <> p_nrdoc AND datamanual < vDatadoc

AND SYSFID = vSysFID AND a.nrdoc = d.cod;

execute immediate 'insert into VMDB_REG_A (NRDOC, NRDOC1, DTSC0, DTNUM2, DTNUM4, DTSTR0, DTDATA0, DTVALUTA0, DTVALUTA1,CTNUM0, CTNUM1, ctnum9)

select '||p_nrdoc||', ID_TMDB_CM.NEXTVAL NRDOC1, a.dtsc0, a.dtnum2, a.dtnum4,

a.dtstr0,

(select max(DTDATA0) from TMDB_reg_A a where a.nrdoc=d.cod) DTDATA0,

a.DTVALUTA0, a.DTVALUTA1,

a.CTNUM0,

a.CTNUM1,

d.cod

from vmdb_docs d, VMDB_REG_A a

where a.nrdoc = d.cod

and d.sysfid = 1415

and (a.dtsc0, a.nrdoc) not in ( select ra.dtsc0, ra.ctnum9 from VMDB_REG_A ra, vmdb_docs d

where ra.nrdoc = d.cod and d.datamanual <= '''||vDataDoc||'''

and d.sysfid = 1420)

and exists (select nrdoc from vmdb_cmr where nrdoc=a.nrdoc)';

execute immediate 'insert into VMDB_REG_A2 (NRDOC, NRDOC1, NRDOC2,DTSC0, DTSC2, DTSC3, DTCANT0,

DTSTR0,SUMA0, SUMA2,CTNUM0, CTNUM1, CTNUM2,DTSTR4,DTNUM4,dtdata1, dtcant1,

suma1, suma3, suma4, suma5, ctnum9)

select '||p_nrdoc||', a.nrdoc1,

ID_TMDB_CM.NEXTVAL NRDOC2, a2.DTSC0, a2.DTSC2, a2.DTSC3, a2.DTCANT0,

a2.DTSTR0, a2.SUMA0, a2.SUMA2,a2.CTNUM0, a2.CTNUM1, a2.CTNUM2, a2.DTSTR4, a2.DTNUM4, a2.dtdata1,

a2.dtcant1, a2.suma1, a2.suma3, a2.suma4, a2.suma5, a.ctnum9

from VMDB_REG_A a, VMDB_REG_A2 a2

where a2.nrdoc = a.ctnum9 and a.nrdoc = '||p_nrdoc||' ';

end;

actioni "Sgenerirovati nomera NN" v doc.1204

procedure gnr_NN_1204(p_nrdoc number, p_rrowid number)is

vseriaNN varchar2(2);

vnrNN number;

vnr_prod number;

vrn number;

begin

select dtstrsc, ctstrsc, a_param1

into vseriaNN,vnrNN,vnr_prod

from VMDB_SALAR_ABSd1

where nrdoc=p_nrdoc

and rrowid = p_rrowid;

if vseriaNN is null or vnrNN is null then

msg(lng('Indicati seria si numarul FF pentru doc. '||vnr_prod||'!','Укажите серию и номер НН для документа '||vnr_prod||'!'));

end if;

delete from xsldx where id=1;

insert into xsldx (id, num2, cant1, str1,num1, str0, num3)

select 1, a_param1, rrowid, dtstrsc, ctstrsc

, clcconta_dept, row_number() over (order by dtdep) rn

from VMDB_SALAR_ABSd1

where nrdoc=p_nrdoc;

select num3 into vrn

from xsldx where id=1 and cant1=p_rrowid;

update xsldx x set str1=vseriaNN, num1=vnrNN+x.num3-vrn

where id=1 and num3>vrn

and vnrNN+x.num3-vrn<=9999999;

update VMDB_SALAR_ABSd1 a

set dtstrsc = null, ctstrsc = null

where exists (select * from xsldx where id=1 and num2=a.a_param1);

update VMDB_SALAR_ABSd1 a

set (dtstrsc, ctstrsc)=(select str1, num1 from xsldx where id=1 and num2=a.a_param1)

where exists (select * from xsldx where id=1 and num2=a.a_param1);

delete from VMDB01M_VINZ

where cod in (select a_param1 from VMDB_SALAR_ABSd1 where nrdoc = p_nrdoc);

insert into VMDB01M_VINZ (cod, PRTVA_SERIA, PRTVA_NR, PRTVA_DATA)

select a_param1, dtstrsc, ctstrsc, sysdate from VMDB_SALAR_ABSd1

where nrdoc = p_nrdoc;

end;

заполнене 1422 документа. Все документы по услугам телекомуникаций за месяц (sysfid - 1411)

PROCEDURE enter_1422(p_nrdoc NUMBER, p_data DATE, p_client NUMBER)

IS

BEGIN

delete from VMDB_REG_A where nrdoc = p_nrdoc;

delete from VMDB_REG_A2 where nrdoc = p_nrdoc;

delete from VMDB_REG_A2D where nrdoc = p_nrdoc;

INSERT INTO VMDB_REG_A (nrdoc, nrdoc1, dt, dtsc0, dtstr0, ctnum0, dtdata0)

SELECT p_nrdoc, nrdoc, dt, dtsc0, (select c.dtstr0 from yrdc_contract c where c.nrdoc = a.ctnum0)||'/'||ctnum0, ctnum0, d.datamanual

FROM VMDB_REG_A a, VMDB_DOCS d

WHERE a.nrdoc = d.cod and d.sysfid = 1411

and d.datamanual between trunc(to_date(p_data),'mm') and p_data

and a.dtsc0 = p_client;

insert into TMDB_REG_A2(nrdoc, nrdoc1, nrdoc2, ct, dtsc0, dtsc2, dtsc3, dtdata0, dtstr2, dtstr1, CTNUM0, CTNUM1, CTNUM2)

select p_nrdoc nrdoc, a.nrdoc nrdoc1

, ID_TMDB_CM.NEXTVAL NRDOC2

, a2.ct, a2.dtsc0, a2.dtsc2, a2.dtsc3, a2.dtdata0, a2.dtstr2, a2.dtstr1, a2.CTNUM0, a2.CTNUM1, a2.CTNUM2

FROM VMDB_REG_A a, VMDB_REG_A2 a2, VMDB_DOCS d

WHERE a.nrdoc = d.cod and a.nrdoc = a2.nrdoc and d.sysfid = 1411

and d.datamanual between trunc(to_date(p_data),'mm') and p_data

and a.dtsc0 = p_client;

insert into VMDB_REG_A2D(nrdoc, nrdoc1, nrdoc2, nrdoc3, dtsc0, dtstr2, dtcant0, dtstr0, dtstr4,

dtcant1, dtcant2, dtcant3, dtcant4,

suma0, suma1, suma2, suma3, suma4, suma5)

select p_nrdoc nrdoc, a.nrdoc nrdoc1, (select distinct nrdoc2 from VMDB_REG_A2 where nrdoc = p_nrdoc and nrdoc1 = a.nrdoc and dtsc0 = a2.dtsc0 ) nrdoc2

, ID_TMDB_CM.NEXTVAL NRDOC3

,a2d.dtsc0, a2d.dtstr2, a2d.dtcant0, a2d.dtstr0, a2d.dtstr4, a2d.dtcant1, a2d.dtcant2,

a2d.dtcant3, a2d.dtcant4, a2d.suma0, a2d.suma1, a2d.suma2, a2d.suma3, a2d.suma4, a2d.suma5

FROM VMDB_REG_A a, VMDB_REG_A2 a2, VMDB_REG_A2D a2d, VMDB_DOCS d

WHERE a.nrdoc = d.cod and a.nrdoc = a2.nrdoc and a2.nrdoc = a2d.nrdoc and a2.nrdoc2 = a2d.nrdoc2 and d.sysfid = 1411

and d.datamanual between trunc(to_date(p_data),'mm') and p_data

and a.dtsc0 = p_client;

END;

procedure check_date(vdate date) is

begin

msg(vdate);

end;

END yrdc_contracts;

/

CREATE OR REPLACE PACKAGE BODY RADIOCOM.yrdc_contr_reps AS

/******************************************************************************

NAME: yrdc_contr_reps

PURPOSE:

REVISIONS:

Ver Date Author Description

- -- ---

1.0 02.02.2010 1. Created this package body.

******************************************************************************/

PROCEDURE rep_hist_spec(p_datastart date,p_dataend date,p_contr IN LONG,SQLH OUT LONG,SQLM OUT LONG)

is

v_filtgrp varchar2(100):='';

begin

проверка на существование параметра группы контрагентов(если не null,то существует)

if p_contr is not null then

v_filtgrp:=' and '||Un$g$util.ScGrCond('c.dtsc0',p_contr)||' ';

end if;

SQLH:='select '''||p_datastart||''' datastart

,'''||p_dataend||''' dataend

from dual';

SQLM:='select a3.contract,a3.client_cod,a3.client_name

,sum(difference) difference

,sum(sold_init) sold_init,sum(sold_final) sold_final

,case when sum(sold_final)=0 then sum(sold_init) else

abs(sum(diff_neg)) end diff_neg

,sum(diff_pos) diff_pos

from(

select a2.*

,case when rn=1 then sum(difference) over(partition by contract) else 0 end sold_final

from(

select a1.*

, nvl(cant,0)-nvl(cant_prev,0)-case when rn=lv and chk=1 then lv_cant else 0 end difference

,case when rn=1 then 0 else decode(sign(nvl(cant,0)-nvl(cant_prev,0)),1,nvl(cant,0)-nvl(cant_prev,0),0) end diff_pos

,case when rn=1 then 0 when rn=lv and chk=1 then decode(sign(nvl(cant,0)-nvl(cant_prev,0)),-1,nvl(cant,0)-nvl(cant_prev,0),0)-lv_cant

else decode(sign(nvl(cant,0)-nvl(cant_prev,0)),-1,nvl(cant,0)-nvl(cant_prev,0),0) end diff_neg

,case when rn=1 then nvl(cant,0) else 0 end sold_init

from(

select a.*

,last_value(a.rn) over ( partition by contract) lv

,last_value(a.cant) over ( partition by contract) lv_cant

,lag(a.rn) over(partition by contract order by data_spec) rn_prev

,lag(a.cant) over(partition by contract order by data_spec) cant_prev

from(

select row_number() over(partition by c.nrdoc order by a.DTDATA0) rn,c.nrdoc contract

,c.DTSC0 client_cod

,c.CLCDTSC0T client_name

,a.DTDATA0 data_spec

,(select sum(nvl(DTCANT0,0)) from VMDB_reg_A1 a1 where a.nrdoc=a1.nrdoc and a.nrdoc1=a1.nrdoc1) cant

,case when c.DTDATA2 between '''||p_datastart||''' and '''||p_dataend||''' then 1 else 0 end chk

from VMDB_REG_A a,yrdc_contract c

where a.nrdoc=c.nrdoc

and exists(select 1 from vmdb_docs d where d.sysfid=1413 and d.cod=c.nrdoc)

and '''||p_datastart||''' between c.DTDATA1 and c.DTDATA2

and a.DTDATA0 between (select max(aa.DTDATA0) from VMDB_REG_A aa where aa.nrdoc=a.nrdoc and aa.DTDATA0<='''||p_datastart||''') and '''||p_dataend||'''

'||v_filtgrp||'

)a

)a1

)a2

)a3

group by a3.contract,a3.client_name,a3.client_cod

order by a3.client_name,a3.contract';

--Imt(sqlm,re=>true);

end;

PROCEDURE rep_serv_radio(p_datastart date,p_dataend date,p_contr IN LONG,SQLH IN OUT LONG,SQLM IN OUT LONG)

is

v_filtgrp varchar2(100):='';

SQL1 long;

begin

if p_contr is not null then

v_filtgrp:=' and '||Un$g$util.ScGrCond('client',p_contr)||' ';

end if;

yrdc_contr_reps.rep_hist_spec(p_datastart,p_dataend,p_contr,SQLH,SQL);

UN$SLD.make(p_datastart-1/24,'1',pfilt=>'ACD',pcont=>'2211',psc=>'39849,29522');

UN$SLD.make(p_dataend,'2',pfilt=>'ACD',pcont=>'2211',psc=>'39849,29522');

return;

SQLM:='Select

client

,(select denumirea from vms_univers where client=cod) client_name

,contract

,sum(conect_init) conect_init

,sum(diff_neg) diff_neg

,sum(diff_pos) diff_pos

,sum(conect_final) conect_final

,sum(sold_init) sold_init

,sum(dtsuma) dtsuma

,sum(ctsuma) ctsuma

,sum(sold_end) sold_end

from

(

Select

client

,case when analitica=29522 then (select nrdoc from yrdc_contract k where '''||p_datastart||''' between dtdata1 and dtdata2 and dtnum6=2

and client=dtsc0 and exists(select 1 from vmdb_docs where sysfid =1413 and k.nrdoc=cod and at1 is null)) else

case when analitica=39849 then (select nrdoc from yrdc_contract k where '''||p_datastart||''' between dtdata1 and dtdata2 and dtnum6 in (1,3)

and client=dtsc0 and exists(select 1 from vmdb_docs where sysfid =1413 and k.nrdoc=cod and at1 is null)) end end contract

,0 conect_init

,0 diff_neg

,0 diff_pos

,0 conect_final

,sum(sold_init) sold_init

,sum(dtsuma) dtsuma

,sum(ctsuma) ctsuma

,sum(sold_end) sold_end

from

(

Select

cont

,analitica

,client

,sold_init

,0 dtsuma

,0 ctsuma

,sold_end

from

(SELECT cont, sc analitica, dep client

,case when id=1 then suma end sold_init

,case when id=2 then suma end sold_end

FROM xsld where ID=1 or ID=2) a

WHERE 1=1 '||v_filtgrp||'

union all

Select

cont

,analitica

,client

,0 sold_init

,dtsuma

,0 ctsuma

,0 sold_end

from

(

Select c.dt cont,a.ctnum0 contract,c.dtsc analitica,c.dtdep client,sum(c.suma) dtsuma,0 ctsuma

from vmdb_cmi c, vmdb_reg_a a

where c.nrdoc=a.nrdoc and c.dtdep=a.dtsc0

and c.data between '''||p_datastart||''' and '''||p_dataend||'''

and exists (Select 1 from vmdb_docs where sysfid in(1410,1415)and c.nrdoc=cod)

group by c.dt,a.ctnum0,c.dtsc,c.dtdep)

WHERE 1=1 '||v_filtgrp||'

union all

Select

cont

,analitica

,client

,0 sold_init

,0 dtsuma

,sum(ctsuma) ctsuma

,0 sold_end

from

Select c.ct cont

,c.ctsc analitica,c.ctdep client,c.suma ctsuma

from vmdb_cmi c

where ct=2211 and ctsc in(29522,39849)

and c.data between '''||p_datastart||''' and '''||p_dataend||''')

WHERE 1=1 '||v_filtgrp||'

group by cont,client,analitica

)a GROUP BY cont,client,analitica

union all

Select

client_cod client

,contract

,sold_init conect_init

,diff_neg

,diff_pos