romfast/roa2web-service-auto
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
roa2web-service-auto/docs/service-auto/pack_auto.pck
Claude Agent 43484db45e docs(service-auto): ground truth audit v3 from MARIUSM_AUTO production 
Add real production sources as authoritative reference (supersedes
vfp_roaauto/Scripturi_instalare/packages.sql which is for a different
product — devize producție, not service auto):

- mariusm_ddl_export.sql: 5127 lines DDL from DBMS_METADATA (tables,
  views, triggers) of MARIUSM_AUTO schema
- pack_auto.pck: main business package (17 procedures)
- PACK_FACTURARE.pck, PACK_SESIUNE.pck, PACK_CONTAFIN.pck,
  PACK_COMENZI.pck: dependency packages
- export_ddl.sql: SQL export helper using DBMS_METADATA + DBMS_OUTPUT
  with discovery via ALL_OBJECTS LIKE patterns

Rewrite tabele-service-auto.md v3 (~600 lines) fully grounded in
production sources. Map all flows end-to-end:

- Create (pack_auto.dev_adauga_lucrare) → NOM_LUCRARI + DEV_ORDL
- Normare (dev_adauga_operatie) → DEV_OPER + DEV_OPER_MECANICI
- Validate ops (dev_valideaza_operatii) → DEV_OPER.VALIDAT
- Validate order (dev_valideaza_comanda) → DEV_ORDL.VALIDAT + CALENDAR
- Archive (dev_arhiveaza_comanda) → DEV_ORDL.INCHIS_FORTAT
- Bonuri consum: generic ROA (ointroduceri.prg tip=3) → RUL.id_lucrare
- Facturare: pack_facturare.* + pack_auto.actualizeaza_deviz

Key business semantics confirmed by Marius 2026-04-11:

- DEV_TIP_DEVIZ.inch_validare = 1 means validation alone closes the
  order (no closing note). inch_validare = 0 means additional closing
  required (via invoice for billable types, or 711=332 journal entry
  for internal types). View AUTO_LISTARE_MAN_TOT_COM has the exact
  "closed" condition as (validat=1 AND inch_validare=1) OR
  (facturat=1 AND inch_validare=0).
- Live DEV_TIP_DEVIZ values: 1=POST GARANTIE, 2=GARANTIE, 3=REGIE,
  4=PREGATIRE, 5=REGIE 2, 6=PRODUCTIE, 7=CONSTATARE. REGIE/PRODUCTIE/
  CONSTATARE have inch_validare=1 (internal, closed at validation).
- DEV_OPER for service auto contains only manopera (id_norme). The
  id_articol/id_rul_aux columns exist in DDL for another product that
  shares the table but are not populated by pack_auto.
- Real materials consumed on an order live in RUL tagged by id_lucrare,
  not in DEV_OPER. DEV_ESTIMARI_REP is a separate pre-sale estimate
  (both manopera and materiale lines) given to the client, independent
  of the real manopera (DEV_OPER) and real materials (RUL).

Plan Correction 13 (claude-main-design-20260411-rethink.md):

- Invalidate Scripturi_instalare references
- Confirm NOM_LUCRARI ← DEV_ORDL inheritance pattern
- Confirm pack_sesiune.dev_idLucrare/dev_idOrdl populated by triggers
- Refine prototype SP (Option 3) template based on real schema
- Timeline unchanged, scope wall reconfirmed

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
2026-06-05 09:37:09 +00:00

1799 lines
69 KiB
Plaintext
Raw Permalink Blame History

create or replace package pack_auto is
v_data_i DATE;
v_data_f DATE;
nid_articol_manopera NUMBER(10) := -100000; -- id_articol pentru articolul MANOPERA
ntip_manopera NUMBER(1) := 1; -- tipul pentru manopera din NOM_ARTICOLE
nIdTipContractAuto TIPURI_CONTRACTE.ID_TIP_CTR%TYPE := -101; -- tip contract CLIENT AUTO pentru citirea discount manopera, materiale pe contract
nIdArticolManopera NOM_ARTICOLE.ID_ARTICOL%TYPE := -100000;
nIdArticolMateriale NOM_ARTICOLE.ID_ARTICOL%TYPE := -100003;
procedure dev_sterge_comanda(tcSchema in varchar2,
tnIdUtil in number,
tnIdOrdl in number,
tnIdLucrare in number);
procedure dev_adauga_oper_fact(tcSchema in varchar2,
tnIdOrdl in number,
tnIdSectie in number,
tnIdNorme in number,
tnTotalFTva in number,
tnTimpN in number,
tnOre in number,
tnIdUtil in number,
tnIdMecanic in number);
procedure dev_adauga_lucrare(v_gcs in varchar2,
tnan in number,
tnluna in number,
tnIdUtil in number,
pcNr in varchar2,
pnIdInsp in number,
pnIdAsig in number,
pcNrDosar in varchar2,
pnIdMC in number,
pnKmInt in number,
pdTermen in date,
pnTipCom in number,
pcSirIdOperatii in varchar2,
pnIdPartRef in number,
V_ID_ORDL out number);
procedure dev_adauga_lucrare(v_gcs in varchar2,
tnan in number,
tnluna in number,
tnIdUtil in number,
pcNr in varchar2,
pnIdInsp in number,
pnIdAsig in number,
pcNrDosar in varchar2,
pnIdMC in number,
pnKmInt in number,
pnOreFct in number default null,
pdTermen in date,
pnTipCom in number,
pcSirIdOperatii in varchar2,
pcObservatii in varchar2 default null,
pcDefectiuni in varchar2 default null,
pnIdPartRef in number,
pnIdOrdl in out number);
procedure dev_valideaza_comanda(v_gcs in varchar2,
tnid_ordl in number,
tnid_util in number,
tnAn in dev_ordl.an%type default null,
tnLuna in dev_ordl.luna%type default null);
procedure dev_invalideaza_comanda(v_gcs in varchar2,
tnid_ordl in number,
tnid_utils in number);
procedure actualizeaza_deviz(tnProcTvav IN NUMBER,
tcSirIdOrdl IN VARCHAR2,
tnIdSet IN NUMBER);
procedure dev_distribuie_timp_n(V_LUNA IN NUMBER, V_AN IN NUMBER);
procedure dev_adauga_operatie(v_gcs in varchar2,
tnid_sectie in number,
tnid_ordl in number,
tnpret in number,
tnid_util in number,
tntimpn in number,
tnid_norme in number,
tcsir_idmecanic in varchar2,
tnAn in number default null,
tnLuna in number default null);
procedure dev_actualizeaza_operatie(v_gcs in varchar2,
v_camp in varchar2,
v_valoare_noua in number,
v_id_oper in number,
v_luna in number,
v_an in number);
procedure dev_valideaza_operatii(v_gcs in varchar2,
tnid_ordl in number,
tnid_util in number);
/* procedure dev_completeaza_rul(v_gcs in varchar2, tnid_lucrare in number);
procedure dev_completeaza_rul(v_gcs in varchar2,
tcSirId_lucrare in varchar2,
tcSeparator in varchar2);*/
procedure valideaza_operatie(V_SCHEMA IN VARCHAR2,
V_VALIDAT IN NUMBER,
V_ID_OPER IN NUMBER,
V_ID_UTIL IN NUMBER);
procedure modifica_sectie(V_SCHEMA IN VARCHAR2,
V_ID_SECTIE IN NUMBER,
V_ID_OPER IN NUMBER,
V_ID_UTIL IN NUMBER);
procedure modifica_tip_comanda(V_SCHEMA IN VARCHAR2,
V_ID_TIP IN NUMBER,
V_ID_ORDL IN NUMBER,
V_ID_UTIL IN NUMBER);
procedure modifica_date_comanda(V_ID_ORDL IN NUMBER,
V_ID_TIP IN NUMBER,
V_KMINT IN NUMBER,
V_ORE_FUNCTIONARE IN NUMBER,
V_ID_UTIL IN NUMBER);
procedure sterge_operatie(V_SCHEMA IN VARCHAR2,
V_ID_OPER IN NUMBER,
V_ID_UTIL IN NUMBER);
procedure modifica_client_comanda(V_SCHEMA IN VARCHAR2,
V_ID_ORDL IN NUMBER,
V_ID_MASINICLIENT IN NUMBER,
V_ID_UTIL IN NUMBER);
------------------------------------------------------------------
-- Adaugari / modificari / stergeri nomenclatoare
------------------------------------------------------------------
procedure adauga_manopera_de(V_ID_LUCRARE IN NUMBER,
V_ID_NORME IN NUMBER,
V_CANTITATE IN NUMBER,
V_PRET IN NUMBER,
V_PRET_CU_TVA IN NUMBER,
V_COTA_TVA IN NUMBER,
V_ID_UTIL IN NUMBER);
procedure adauga_material_de(V_ID_LUCRARE IN NUMBER,
V_ID_ARTICOL IN NUMBER,
V_CANTITATE IN NUMBER,
V_PRET IN NUMBER,
V_PRET_CU_TVA IN NUMBER,
V_COTA_TVA IN NUMBER,
V_ID_UTIL IN NUMBER);
procedure modifica_pret_de(V_ID_DEV_ESTIMARE_REP IN NUMBER,
V_PRET IN NUMBER,
V_PRET_CU_TVA IN NUMBER,
V_ID_UTIL IN NUMBER);
procedure modifica_cota_tva_de(V_ID_DEV_ESTIMARE_REP IN NUMBER,
V_COTA_TVA IN NUMBER,
V_ID_UTIL IN NUMBER);
procedure modifica_cantitate_de(V_ID_DEV_ESTIMARE_REP IN NUMBER,
V_CANTITATE IN NUMBER,
V_ID_UTIL IN NUMBER);
procedure sterge_articol_de(V_ID_DEV_ESTIMARE_REP IN NUMBER,
V_ID_UTIL IN NUMBER);
procedure adauga_pret(V_ID_MARCA IN NUMBER,
V_ID_ASIGURATOR IN NUMBER,
V_PRET IN NUMBER,
V_PRET_VAL IN NUMBER,
V_CURS IN NUMBER,
V_ID_VALUTA IN NUMBER,
V_INACTIV IN NUMBER);
procedure modifica_pret(V_ID_PRET IN NUMBER,
V_ID_MARCA IN NUMBER,
V_ID_ASIGURATOR IN NUMBER,
V_PRET IN NUMBER,
V_PRET_VAL IN NUMBER,
V_CURS IN NUMBER,
V_ID_VALUTA IN NUMBER,
V_INACTIV IN NUMBER);
procedure sterge_pret(V_ID_PRET IN NUMBER, V_ID_UTILS IN NUMBER);
procedure adauga_ansamblu(V_SCHEMA IN VARCHAR2,
V_DENUMIRE IN VARCHAR2,
V_INACTIV IN NUMBER,
V_ID_UTIL IN NUMBER);
procedure modifica_ansamblu(V_SCHEMA IN VARCHAR2,
V_ID_ANSAMBLU IN NUMBER,
V_DENUMIRE IN VARCHAR2,
V_INACTIV IN NUMBER,
V_ID_UTIL IN NUMBER);
procedure sterge_ansamblu(V_SCHEMA IN VARCHAR2,
V_ID_ANSAMBLU IN NUMBER,
V_ID_UTIL IN NUMBER);
procedure adauga_norma(V_ID_MASINA IN NUMBER,
V_ID_ANSAMBLU IN NUMBER,
V_DENOP IN VARCHAR2,
V_CODOP IN VARCHAR2,
V_TIMPN IN NUMBER,
V_INACTIV IN NUMBER,
V_ID_UTIL IN NUMBER);
procedure modifica_norma(V_ID_NORME IN NUMBER,
V_ID_MASINA IN NUMBER,
V_ID_ANSAMBLU IN NUMBER,
V_DENOP IN VARCHAR2,
V_CODOP IN VARCHAR2,
V_TIMPN IN NUMBER,
V_INACTIV IN NUMBER,
V_ID_UTIL IN NUMBER);
procedure sterge_norma(V_ID_NORME IN NUMBER, V_ID_UTIL IN NUMBER);
PROCEDURE adauga_mecanic(V_SCHEMA IN VARCHAR2,
V_ID_PART IN NUMBER,
V_ID_SECTIE IN NUMBER,
V_INACTIV IN NUMBER,
V_ID_UTIL IN NUMBER);
PROCEDURE modifica_mecanic(V_SCHEMA IN VARCHAR2,
V_ID_MECANIC IN NUMBER,
V_ID_PART IN NUMBER,
V_ID_SECTIE IN NUMBER,
V_INACTIV IN NUMBER,
V_ID_UTIL IN NUMBER);
PROCEDURE sterge_mecanic(V_SCHEMA IN VARCHAR2,
V_ID_MECANIC IN NUMBER,
V_ID_UTIL IN NUMBER);
PROCEDURE adauga_asigurator(V_ID_PART IN NUMBER, V_ID_UTIL IN NUMBER);
PROCEDURE modifica_asigurator(V_ID_ASIGURATOR IN NUMBER,
V_INACTIV IN NUMBER,
V_ID_UTIL IN NUMBER);
PROCEDURE set_perioada_raport(V_LUNA_I IN NUMBER,
V_AN_I IN NUMBER,
V_LUNA_F IN NUMBER,
V_AN_F IN NUMBER);
FUNCTION get_datai RETURN DATE;
FUNCTION get_dataf RETURN DATE;
FUNCTION getOptiuneInchidere(V_TIP IN NUMBER) RETURN NUMBER;
procedure setOptiuneInchidere(V_TIP IN NUMBER,
V_INCHIDERE_VALIDARE IN NUMBER);
PROCEDURE citeste_date_avans(V_SIR_ID_LUCRARE IN VARCHAR2,
V_DELIMITATOR IN VARCHAR2,
V_CURSOR OUT pack_types.tip_cursor);
PROCEDURE citeste_sume_stornare(V_SIR_ID_LUCRARE IN VARCHAR2,
V_DELIMITATOR IN VARCHAR2,
V_CURSOR OUT pack_types.tip_cursor);
PROCEDURE citeste_discount_contract(V_ID_PART IN CONTRACTE.ID_PART%TYPE,
V_ID_SUCURSALA IN CONTRACTE.ID_SUCURSALA%TYPE,
V_ID_CTR OUT CONTRACTE.ID_CTR%TYPE,
V_DISCOUNT_MANOPERA OUT CTR_ARTICOLE.COEF_DISCOUNT%TYPE,
V_DISCOUNT_MATERIALE OUT CTR_ARTICOLE.COEF_DISCOUNT%TYPE);
PROCEDURE salveaza_discount_contract(V_ID_PART IN CONTRACTE.ID_PART%TYPE,
V_ID_SUCURSALA IN CONTRACTE.ID_SUCURSALA%TYPE,
V_ID_CTR IN CONTRACTE.ID_CTR%TYPE,
V_DISCOUNT_MANOPERA IN CTR_ARTICOLE.COEF_DISCOUNT%TYPE,
V_DISCOUNT_MATERIALE IN CTR_ARTICOLE.COEF_DISCOUNT%TYPE);
procedure dev_arhiveaza_comanda(tnid_ordl in number, tnid_util in number);
procedure dev_dezarhiveaza_comanda(tnid_ordl in number);
end pack_auto;
/
CREATE OR REPLACE PACKAGE BODY pack_auto is
-- 10.01.2015
-- marius.mutu
-- actualizeaza_deviz: hint INDEX(RUL IDX_RUL_001)
-- 20.08.2015
-- marius.mutu
-- adaugare citeste_discount_contract, salveaza_discount_contract la editare clienti/masini
-- 26.08.2015
-- marius.mutu
-- adaugare dev_arhiveaza_comanda, dev_dezarhiveaza_comanda
-- 08.10.2015
-- marius.mutu
-- dev_adauga_lucrare - s-a tratat cazul id_part_ref = 0 in loc de NULL
-- 27.01.2016
-- marius.mutu
-- citeste_sume_stornare - se preiau si cota de tva, id_jtva_coloana de la avansurile initiale
-- puteau sa fie cu alta cota de TVA decat cea standard din luna curenta
-- 21.09.2016
-- marius.mutu
-- + modifica_date_comanda: kmint, tip_comanda
-- dev_valideaza_comanda, dev_adauga_operatie + parametru optional tnAn, tnLuna
-- pentru introducerea operatiilor si validarea comenzii dintr-o luna precedenta.
-- sunt cazuri in care se doreste facturarea in luna precedenta si trebuie sa fie si validata in luna precedenta,
-- sau operatiile sa se vada chiar daca sunt introduse intr-o luna anterioara, dupa deschiderea lunii urmatoare
-- chiar daca intre timp s-a trecut in alta luna
-- 11.04.2022
-- citeste_sume_stornare + taxcode
-- 03.07.2024
-- marius.mutu
-- dev_adauga_lucrare + ore_functionare, observatii
-- 18.10.2024
-- marius.mutu
-- citeste_date_avans - se iau toate incasarile like '5%' in loc de '53%' (5125 = 4111, 5311 = 4111)
-- 21.01.2025
-- marius.mutu
-- citeste_date_avans + taxcode
--17.03.2026
--robert
--creare procedura setOptiuneInchidere - Modificare modalitate de inchidere comenzi (prin validare sau prin facturare/nota contabila)
procedure dev_sterge_comanda(tcSchema in varchar2,
tnIdUtil in number,
tnIdOrdl in number,
tnIdLucrare in number) is
lnSumaRulaje RUL.PRETV%type := 0;
/*lnInregRulaje NUMBER(10);*/
lnOperatii NUMBER(3) := 0;
V_NESTERS NUMBER(1) := 0;
V_ACTIV NUMBER(1) := 0;
BEGIN
SELECT ROUND(SUM(cante * pretv), 2) AS SUMA
INTO lnSumaRulaje
FROM RUL
WHERE ID_LUCRARE = tnIdLucrare
AND STERS = V_NESTERS;
IF lnSumaRulaje <> 0 then
/* SELECT COUNT(*)
INTO lnInregRulaje
FROM RUL
WHERE ID_LUCRARE = tnIdLucrare
AND STERS = V_NESTERS;
IF lnInregRulaje <> 0 then*/
raise_application_error(-20001,
'Aceasta comanda nu poate fi stearsa, deoarece are legaturi in rulaje!');
ELSE
SELECT COUNT(*) AS SUMA
INTO lnOperatii
FROM DEV_OPER
WHERE ID_ORDL = tnIdOrdl
AND STERS = V_NESTERS;
IF lnOperatii > 0 then
raise_application_error(-20001,
'Aceasta comanda nu poate fi stearsa, deoarece are adaugate operatii!');
ELSE
UPDATE DEV_ORDL
SET STERS = V_NESTERS + 1,
ID_UTILS = tnIdUtil,
DATAORAS = SYSDATE
WHERE ID_ORDL = tnIdOrdl;
UPDATE NOM_LUCRARI
SET INACTIV = V_ACTIV + 1
WHERE ID_LUCRARE = tnIdLucrare;
END IF;
END IF;
END;
------------------------------------------------------------------
procedure dev_adauga_oper_fact(tcSchema in varchar2,
tnIdOrdl in number,
tnIdSectie in number,
tnIdNorme in number,
tnTotalFTva in number,
tnTimpN in number,
tnOre in number,
tnIdUtil in number,
tnIdMecanic in number) is
V_ID_OPER number(10);
V_PRET number(17, 4);
V_DATAORA date;
V_VALIDAT number(1) := 1;
v_nou number(1) := 1;
begin
v_dataora := sysdate;
UPDATE DEV_ORDL
SET VALIDAT = V_VALIDAT,
ID_UTIL_VALID = tnIdUtil,
DATAORAVALID = V_DATAORA
WHERE ID_ORDL = tnIdOrdl;
V_PRET := tnTotalFTva / tnTimpN;
INSERT /*+ APPEND */
INTO DEV_OPER
(ID_ORDL,
ID_SECTIE,
TIMPN,
PRET,
ID_NORME,
ID_UTIL,
DATAORA,
VALIDAT,
NOU)
VALUES
(tnIdOrdl,
tnIdSectie,
tnTimpN,
V_PRET,
tnIdNorme,
tnIdUtil,
V_DATAORA,
V_VALIDAT,
V_NOU)
RETURNING ID_OPER INTO V_ID_OPER;
INSERT /*+ APPEND */
INTO DEV_OPER_MECANICI
(ID_OPER, ID_MECANIC, ORE)
VALUES
(V_ID_OPER, tnIdMecanic, tnOre);
end;
------------------------------------------------------------------
procedure dev_adauga_lucrare(v_gcs in varchar2,
tnan in number,
tnluna in number,
tnIdUtil in number,
pcNr in varchar2,
pnIdInsp in number,
pnIdAsig in number,
pcNrDosar in varchar2,
pnIdMC in number,
pnKmInt in number,
pdTermen in date,
pnTipCom in number,
pcSirIdOperatii in varchar2,
pnIdPartRef in number,
V_ID_ORDL out number) is
pcObservatii varchar2(1000);
pcDefectiuni varchar2(300);
pnOreFct number(10);
pnIdOrdl number(20);
begin
dev_adauga_lucrare(v_gcs,
tnan,
tnluna,
tnIdUtil,
pcNr,
pnIdInsp,
pnIdAsig,
pcNrDosar,
pnIdMC,
pnKmInt,
pnOreFct,
pdTermen,
pnTipCom,
pcSirIdOperatii,
pcObservatii,
pcDefectiuni,
pnIdPartRef,
pnIdOrdl);
V_ID_ORDL := pnIdOrdl;
end;
procedure dev_adauga_lucrare(v_gcs in varchar2,
tnan in number,
tnluna in number,
tnIdUtil in number,
pcNr in varchar2,
pnIdInsp in number,
pnIdAsig in number,
pcNrDosar in varchar2,
pnIdMC in number,
pnKmInt in number,
pnOreFct in number default null,
pdTermen in date,
pnTipCom in number,
pcSirIdOperatii in varchar2,
pcObservatii in varchar2 default null,
pcDefectiuni in varchar2 default null,
pnIdPartRef in number,
pnIdOrdl in out number) is
V_SOLICITARI_CLIENT DEV_ORDL.SOLICITARI_CLIENT%TYPE;
V_SEPARATOR VARCHAR2(1) := ',';
V_NR_INREGISTRARI NUMBER(10);
begin
SELECT COUNT(*)
INTO V_NR_INREGISTRARI
FROM NOM_LUCRARI
WHERE STERS = 0
AND NRORD = pcNr;
IF V_NR_INREGISTRARI > 0 THEN
RAISE_APPLICATION_ERROR(-20000,
'Mai exista o lucrare cu indicativul ' || pcNr ||
'!. Daca doriti sa pastrati numele, atunci stergeti lucrarea existenta!');
END IF;
INSERT /*+ APPEND */
INTO NOM_LUCRARI
(NRORD, ID_MOD)
VALUES
(pcNr, 1200);
IF pcSirIdOperatii IS NOT NULL THEN
SELECT STRINGAGG(DENOP)
INTO V_SOLICITARI_CLIENT
FROM DEV_NOM_NORME
WHERE ID_NORME IN
(SELECT to_number(substr(pcSirIdOperatii || V_SEPARATOR,
decode(rownum,
1,
1,
instr(pcSirIdOperatii ||
V_SEPARATOR,
V_SEPARATOR,
1,
rownum - 1) + 1),
instr(pcSirIdOperatii || V_SEPARATOR,
V_SEPARATOR,
1,
rownum) -
decode(rownum,
1,
0,
instr(pcSirIdOperatii ||
V_SEPARATOR,
V_SEPARATOR,
1,
rownum - 1)) - 1)) AS ID_NORME
FROM dual
connect by level <=
length(pcSirIdOperatii || V_SEPARATOR) -
length(REPLACE(pcSirIdOperatii || V_SEPARATOR,
V_SEPARATOR)));
V_SOLICITARI_CLIENT := V_SOLICITARI_CLIENT;
ELSE
V_SOLICITARI_CLIENT := NULL;
END IF;
INSERT /*+ APPEND */
INTO DEV_ORDL
(AN,
LUNA,
ID_INSPECTOR,
ID_LUCRARE,
DATAI,
ID_UTIL_AD,
ID_MASINICLIENT,
ID_ASIGURATOR,
ID_TIP,
KMINT,
ORE_FUNCTIONARE,
TERMEN,
NR_DOSAR,
DATAORAAD,
SOLICITARI_CLIENT,
OBSERVATII,
DEFECTIUNI,
PROC_TVAV,
ID_PART_REF)
VALUES
(tnAn,
tnLuna,
pnIdInsp,
pack_sesiune.dev_idLucrare,
SYSDATE,
tnIdUtil,
pnIdMC,
pnIdAsig,
pnTipCom,
pnKmInt,
pnOreFct,
pdTermen,
pcNrDosar,
SYSDATE,
V_SOLICITARI_CLIENT,
pcObservatii,
pcDefectiuni,
pack_contafin.getCotaTVAStandard(tnLuna, tnAn),
decode(pnIdPartRef, 0, NULL, pnIdPartRef))
RETURNING ID_ORDL INTO pnidOrdl;
UPDATE DEV_MASINICLIENTI
SET KMINT = pnKmInt, ORE_FUNCTIONARE = pnOreFct
WHERE ID_MASINICLIENT = pnIdMC;
end;
------------------------------------------------------------------
procedure dev_valideaza_comanda(v_gcs in varchar2,
tnid_ordl in number,
tnid_util in number,
tnAn in dev_ordl.an%type default null,
tnLuna in dev_ordl.luna%type default null) as
lnNevalidate NUMBER(3);
lnLunaDeschisa NUMBER(1);
ldData DATE;
lnLuna NUMBER(2);
lnAn NUMBER(4);
begin
SELECT COUNT(*)
INTO lnNevalidate
FROM DEV_OPER
WHERE STERS = 0
AND VALIDAT = 0
AND ID_ORDL = tnid_ordl;
if lnNevalidate > 0 then
RAISE_APPLICATION_ERROR(-20000,
'Mai sunt operatii nevalidate pe aceasta comanda!' ||
CHR(13) || CHR(10) ||
LPAD(CHR(32), 12, CHR(32)) ||
'Comanda nu a fost validata!');
end if;
-- Validez in ziua curenta daca luna curenta este ultima luna sau ultima zi din luna precedenta
if tnAn is null or tnLuna is null then
ldData := SYSDATE;
else
if extract(year from sysdate) * 12 + extract(month from sysdate) =
tnAn * 12 + tnLuna then
ldData := SYSDATE;
else
ldData := ADD_MONTHS(TO_DATE(LPAD(tnAn, 4, '0') ||
LPAD(tnLuna, 2, '0') || '01',
'YYYYMMDD'),
1) - 1;
end if;
end if;
lnLuna := EXTRACT(MONTH FROM ldData);
lnAn := EXTRACT(YEAR FROM ldData);
SELECT COUNT(*)
INTO lnLunaDeschisa
FROM CALENDAR
WHERE AN = lnAn
AND LUNA = lnLuna;
IF lnLunaDeschisa = 0 THEN
RAISE_APPLICATION_ERROR(-20000,
'Nu puteti valida comanda deoarece nu este deschisa luna ' ||
lnLuna || '/' || lnAn || ' !');
END IF;
UPDATE DEV_ORDL
SET VALIDAT = 1,
ID_UTIL_VALID = tnid_util,
DATAORAVALID = ldData,
PROC_TVAV = pack_contafin.getCotaTVAStandard(lnLuna, lnAn)
WHERE ID_ORDL = tnid_ordl;
end;
------------------------------------------------------------------
procedure dev_invalideaza_comanda(v_gcs in varchar2,
tnid_ordl in number,
tnid_utils in number) AS
begin
UPDATE DEV_ORDL
SET VALIDAT = 0,
ID_UTIL_VALID = 0,
DATAORAVALID = NULL,
ID_UTILS = tnid_utils,
DATAORAS = SYSDATE,
PROC_TVAV = pack_contafin.getCotaTVAStandard(LUNA, AN)
WHERE ID_ORDL = tnid_ordl;
end;
------------------------------------------------------------------
procedure actualizeaza_deviz(tnProcTvav IN NUMBER,
tcSirIdOrdl IN VARCHAR2,
tnIdSet IN NUMBER) is
lcSeparator VARCHAR2(1) := ',';
lnIdFact DOCUMENTE.ID_DOC%TYPE;
begin
-- nu iau pack_contafin.get_idfact pentru ca s-ar putea sa am id_fact de la incasare, nu de la factura
SELECT MAX(ID_FACT)
INTO lnIdFact
FROM ACT
WHERE COD = pack_contafin.get_cod()
AND SCD NOT LIKE '5%';
UPDATE DEV_ORDL
SET PROC_TVAV = tnProcTvav
WHERE ID_ORDL IN
(SELECT X as ID_ORDL
FROM table(charn2collection(tcSirIdOrdl, lcSeparator)));
UPDATE /*+ INDEX(RUL IDX_RUL_001) */ RUL
SET ID_FACT = lnIdFact
WHERE ID_SET <> 229
AND ID_LUCRARE IN
(SELECT ID_LUCRARE
FROM DEV_ORDL
WHERE ID_ORDL IN
(SELECT X as ID_ORDL
FROM table(charn2collection(tcSirIdOrdl, lcSeparator))));
-- pentru id_set 31006, 31007 nu verific daca se inchid la validare pentru ca procedura de inchidere
-- se apeleaza doar daca nu se inchid la validare
IF tnIdSet IN (31003, 31004, 31005, 31006, 31007, 31011) THEN
UPDATE NOM_LUCRARI
SET ID_FACT = lnIdFact
WHERE ID_LUCRARE IN
(SELECT ID_LUCRARE
FROM DEV_ORDL
WHERE ID_ORDL IN
(SELECT X as ID_ORDL
FROM table(charn2collection(tcSirIdOrdl, lcSeparator))));
END IF;
end actualizeaza_deviz;
------------------------------------------------------------------
procedure dev_distribuie_timp_n(V_LUNA IN NUMBER, V_AN IN NUMBER) AS
begin
MERGE INTO DEV_OPER_MECANICI A
USING (SELECT A.ID_OPER,
A.TIMPN,
ROUND(SUM(NVL(B.ORE, -1.000)), 3) AS TOTAL,
COUNT(DISTINCT B.ID_MECANIC) AS NR_MECANICI
FROM DEV_OPER A
LEFT JOIN DEV_OPER_MECANICI B
ON A.ID_OPER = B.ID_OPER
AND B.STERS = 0
WHERE A.STERS = 0
AND A.ID_ORDL IN
(SELECT ID_ORDL
FROM AUTO_NORMARE_COMENZI
WHERE TRUNC(DATAI) BETWEEN
TO_DATE(V_LUNA || '/' || V_AN, 'MM/YYYY') AND
LAST_DAY(TO_DATE(V_LUNA || '/' || V_AN, 'MM/YYYY'))
OR (TRUNC(DATAI) <
TO_DATE(V_LUNA || '/' || V_AN, 'MM/YYYY') AND
((INCH_VALIDARE = 0 AND
(FACTURAT = 0 OR
(FACTURAT = 1 AND
DATAFACT BETWEEN
TO_DATE(V_LUNA || '/' || V_AN, 'MM/YYYY') AND
LAST_DAY(TO_DATE(V_LUNA || '/' || V_AN,
'MM/YYYY'))))) OR
(INCH_VALIDARE = 1 AND
(VALIDAT = 0 OR
(VALIDAT = 1 AND
DATAORAVALID BETWEEN
TO_DATE(V_LUNA || '/' || V_AN || ' 00:00:00',
'MM/YYYY HH24:MI:SS') AND
LAST_DAY(TO_DATE(V_LUNA || '/' || V_AN ||
' 23:59:59',
'MM/YYYY HH24:MI:SS'))))))))
GROUP BY A.ID_OPER, A.TIMPN
HAVING A.TIMPN - ROUND(SUM(NVL(B.ORE, -1.000)), 3) <> 0) B
ON (A.ID_OPER = B.ID_OPER AND A.STERS = 0)
WHEN MATCHED THEN
UPDATE SET ORE = B.TIMPN / B.NR_MECANICI
WHEN NOT MATCHED THEN
INSERT (ID_OPER, ID_MECANIC, ORE) VALUES (B.ID_OPER, 1, B.TIMPN);
end;
------------------------------------------------------------------
procedure dev_adauga_operatie(v_gcs in varchar2,
tnid_sectie in number,
tnid_ordl in number,
tnpret in number,
tnid_util in number,
tntimpn in number,
tnid_norme in number,
tcsir_idmecanic in varchar2,
tnAn in number default null,
tnLuna in number default null) AS
v_id_oper number(10);
v_lungime_sir number(3);
v_ore number(7, 3);
-- v_timp_ramas number(7, 3);
v_nou number(1);
v_validat dev_ordl.validat%type;
ldData date;
begin
-- Adaug operatii in ziua curenta daca luna curenta este ultima luna sau ultima zi din luna precedenta
-- Astfel se pot adauga operatii intr-o luna anterioara, chiar daca am trecut in alta luna
if tnAn is null or tnLuna is null then
ldData := SYSDATE;
else
if extract(year from sysdate) * 12 + extract(month from sysdate) =
tnAn * 12 + tnLuna then
ldData := SYSDATE;
else
ldData := ADD_MONTHS(TO_DATE(LPAD(tnAn, 4, '0') ||
LPAD(tnLuna, 2, '0') || '01',
'YYYYMMDD'),
1) - 1;
end if;
end if;
SELECT VALIDAT INTO v_validat FROM DEV_ORDL WHERE ID_ORDL = tnid_ordl;
IF V_VALIDAT = 1 THEN
RAISE_APPLICATION_ERROR(-20000,
'Comanda a fost validata si nu se mai pot face modificari asupra ei!' ||
CHR(13) || CHR(10) ||
'Daca nu o vedeti ca fiind validata, atunci iesiti din fereastra de normare si reintrati!');
END IF;
v_nou := 1;
INSERT INTO DEV_OPER
(ID_ORDL, ID_SECTIE, TIMPN, PRET, ID_NORME, ID_UTILI, NOU, DATAI, DATAORA)
VALUES
(tnid_ordl,
tnid_sectie,
tntimpn,
tnpret,
tnid_norme,
tnid_util,
v_nou,
ldData,
ldData)
RETURNING ID_OPER INTO v_id_oper;
v_lungime_sir := SIR_IN_SIR(tcsir_idmecanic, ';');
v_ore := round(tntimpn / v_lungime_sir, 3);
if v_lungime_sir > 0 then
for i in 1 .. v_lungime_sir loop
INSERT INTO DEV_OPER_MECANICI
(ID_OPER, ID_MECANIC, ORE)
VALUES
(v_id_oper, ID_DIN_SIR(tcsir_idmecanic, ';', i), v_ore);
end loop;
end if;
end;
------------------------------------------------------------------
procedure dev_actualizeaza_operatie(v_gcs in varchar2,
v_camp in varchar2,
v_valoare_noua in number,
v_id_oper in number,
v_luna in number,
v_an in number) as
v_datai date;
v_data_ron date;
v_data_util date;
v_valoare number;
v_validat DEV_OPER.VALIDAT%TYPE;
begin
SELECT TRUNC(DATAI), VALIDAT
INTO v_datai, v_validat
FROM DEV_OPER
WHERE ID_OPER = v_id_oper;
IF V_VALIDAT = 1 THEN
RAISE_APPLICATION_ERROR(-20000,
'Operatia a fost validata si nu se pot face modificari asupra ei!' ||
CHR(13) || CHR(10) ||
'Daca nu o vedeti ca fiind validata, atunci iesiti din fereastra de normare si reintrati!');
ELSE
v_data_ron := to_date(pack_sesiune.get_lunaron || '/' ||
pack_sesiune.get_anron,
'MM/YYYY');
v_data_util := to_date(v_luna || '/' || v_an, 'MM/YYYY');
if UPPER(TRIM(v_camp)) = 'PRET' and v_datai < v_data_ron and
v_data_util >= v_data_ron then
v_valoare := v_valoare_noua * 10000;
else
v_valoare := v_valoare_noua;
end if;
IF UPPER(TRIM(v_camp)) = 'PRET' THEN
UPDATE DEV_OPER SET PRET = V_VALOARE WHERE ID_OPER = V_ID_OPER;
ELSE
UPDATE DEV_OPER SET TIMPN = V_VALOARE WHERE ID_OPER = V_ID_OPER;
END IF;
END IF;
end dev_actualizeaza_operatie;
------------------------------------------------------------------
procedure dev_valideaza_operatii(v_gcs in varchar2,
tnid_ordl in number,
tnid_util in number) as
v_validat DEV_ORDL.ID_ORDL%TYPE;
ldData dev_ordl.dataoravalid%type := SYSDATE;
begin
SELECT VALIDAT INTO v_validat FROM DEV_ORDL WHERE ID_ORDL = tnid_ordl;
IF V_VALIDAT = 1 THEN
RAISE_APPLICATION_ERROR(-20000,
'Comanda a fost validata si nu se mai pot face modificari asupra ei!' ||
CHR(13) || CHR(10) ||
'Daca nu o vedeti ca fiind validata, atunci iesiti din fereastra de normare si reintrati!');
END IF;
UPDATE DEV_OPER
SET VALIDAT = 1, ID_UTIL = tnid_util, DATAORA = ldData
WHERE ID_ORDL = tnid_ordl
AND STERS = 0
AND VALIDAT = 0;
end;
------------------------------------------------------------------
/*procedure dev_completeaza_rul(v_gcs in varchar2, tnid_lucrare in number) AS
begin
UPDATE RUL
SET ID_FACT = pack_contafin.get_idFact()
WHERE ID_LUCRARE = tnId_lucrare
and id_set <> 229;
end;*/
------------------------------------------------------------------
/*procedure dev_completeaza_rul(v_gcs in varchar2,
tcSirId_lucrare in varchar2,
tcSeparator in varchar2) AS
lcSirId_lucrare VARCHAR2(3000);
begin
IF substr(tcSirId_lucrare, length(tcSirId_lucrare), 1) <> tcSeparator THEN
lcSirId_lucrare := tcSirId_lucrare || tcSeparator;
ELSE
lcSirId_lucrare := tcSirId_lucrare;
END IF;
UPDATE RUL
SET ID_FACT = pack_contafin.get_idFact()
WHERE ID_LUCRARE IN
(SELECT to_number(substr(lcSirId_lucrare,
decode(rownum,
1,
1,
instr(lcSirId_lucrare,
tcSeparator,
1,
rownum - 1) + 1),
instr(lcSirId_lucrare,
tcSeparator,
1,
rownum) -
decode(rownum,
1,
0,
instr(lcSirId_lucrare,
tcSeparator,
1,
rownum - 1)) - 1)) AS ID_LUCRARE
FROM dual
connect by level <=
length(lcSirId_lucrare) -
length(REPLACE(lcSirId_lucrare, tcSeparator)));
end;*/
------------------------------------------------------------------
procedure valideaza_operatie(V_SCHEMA IN VARCHAR2,
V_VALIDAT IN NUMBER,
V_ID_OPER IN NUMBER,
V_ID_UTIL IN NUMBER) is
V_DATAORA DATE;
v_validat_com dev_ordl.validat%TYPE;
begin
SELECT VALIDAT
INTO v_validat_com
FROM DEV_ORDL
WHERE ID_ORDL IN
(SELECT ID_ORDL FROM DEV_OPER WHERE ID_OPER = V_ID_OPER);
IF V_VALIDAT_COM = 1 THEN
RAISE_APPLICATION_ERROR(-20000,
'Comanda a fost validata si nu se mai pot face modificari asupra ei!' ||
CHR(13) || CHR(10) ||
'Daca nu o vedeti ca fiind validata, atunci iesiti din fereastra de normare si reintrati!');
END IF;
UPDATE DEV_OPER
SET VALIDAT = V_VALIDAT,
DATAORA = DECODE(V_VALIDAT, 1, SYSDATE, NULL),
ID_UTIL = V_ID_UTIL
WHERE ID_OPER = V_ID_OPER;
end;
------------------------------------------------------------------
procedure modifica_sectie(V_SCHEMA IN VARCHAR2,
V_ID_SECTIE IN NUMBER,
V_ID_OPER IN NUMBER,
V_ID_UTIL IN NUMBER) is
begin
UPDATE DEV_OPER SET ID_SECTIE = V_ID_SECTIE WHERE ID_OPER = V_ID_OPER;
end;
------------------------------------------------------------------
procedure modifica_tip_comanda(V_SCHEMA IN VARCHAR2,
V_ID_TIP IN NUMBER,
V_ID_ORDL IN NUMBER,
V_ID_UTIL IN NUMBER) is
begin
UPDATE DEV_ORDL SET ID_TIP = V_ID_TIP WHERE ID_ORDL = V_ID_ORDL;
end;
------------------------------------------------------------------
procedure modifica_date_comanda(V_ID_ORDL IN NUMBER,
V_ID_TIP IN NUMBER,
V_KMINT IN NUMBER,
V_ORE_FUNCTIONARE IN NUMBER,
V_ID_UTIL IN NUMBER) is
begin
UPDATE DEV_ORDL
SET ID_TIP = V_ID_TIP, KMINT = V_KMINT, ORE_FUNCTIONARE = V_ORE_FUNCTIONARE
WHERE ID_ORDL = V_ID_ORDL;
UPDATE DEV_MASINICLIENTI
SET KMINT = GREATEST(KMINT, V_KMINT), ORE_FUNCTIONARE = GREATEST(ORE_FUNCTIONARE, V_ORE_FUNCTIONARE)
WHERE ID_MASINICLIENT =
(select ID_MASINICLIENT from dev_ordl where ID_ORDL = V_ID_ORDL);
end;
------------------------------------------------------------------
procedure sterge_operatie(V_SCHEMA IN VARCHAR2,
V_ID_OPER IN NUMBER,
V_ID_UTIL IN NUMBER) is
v_validat DEV_ORDL.VALIDAT%TYPE;
begin
SELECT VALIDAT
INTO v_validat
FROM DEV_ORDL
WHERE ID_ORDL IN
(SELECT ID_ORDL FROM DEV_OPER WHERE ID_OPER = V_ID_OPER);
IF V_VALIDAT = 1 THEN
RAISE_APPLICATION_ERROR(-20000,
'Comanda a fost validata si nu se mai pot face modificari asupra ei!' ||
CHR(13) || CHR(10) ||
'Daca nu o vedeti ca fiind validata, atunci iesiti din fereastra de normare si reintrati!');
END IF;
UPDATE DEV_OPER
SET STERS = 1, DATAORAS = SYSDATE, ID_UTILS = V_ID_UTIL
WHERE ID_OPER = V_ID_OPER;
end;
------------------------------------------------------------------
procedure modifica_client_comanda(V_SCHEMA IN VARCHAR2,
V_ID_ORDL IN NUMBER,
V_ID_MASINICLIENT IN NUMBER,
V_ID_UTIL IN NUMBER) is
begin
UPDATE DEV_ORDL
SET ID_MASINICLIENT = V_ID_MASINICLIENT
WHERE ID_ORDL = V_ID_ORDL;
end;
------------------------------------------------------------------
-- Adaugari / modificari / stergeri nomenclatoare
------------------------------------------------------------------
procedure adauga_manopera_de(V_ID_LUCRARE IN NUMBER,
V_ID_NORME IN NUMBER,
V_CANTITATE IN NUMBER,
V_PRET IN NUMBER,
V_PRET_CU_TVA IN NUMBER,
V_COTA_TVA IN NUMBER,
V_ID_UTIL IN NUMBER) is
begin
INSERT INTO dev_estimari_rep
(ID_LUCRARE,
ID_NORME,
CANTITATE,
PRET,
PRET_CU_TVA,
COTA_TVA,
ID_UTIL)
VALUES
(V_ID_LUCRARE,
V_ID_NORME,
V_CANTITATE,
V_PRET,
V_PRET_CU_TVA,
V_COTA_TVA,
V_ID_UTIL);
end adauga_manopera_de;
------------------------------------------------------------------
procedure adauga_material_de(V_ID_LUCRARE IN NUMBER,
V_ID_ARTICOL IN NUMBER,
V_CANTITATE IN NUMBER,
V_PRET IN NUMBER,
V_PRET_CU_TVA IN NUMBER,
V_COTA_TVA IN NUMBER,
V_ID_UTIL IN NUMBER) is
begin
INSERT INTO dev_estimari_rep
(ID_LUCRARE,
ID_ARTICOL,
CANTITATE,
PRET,
PRET_CU_TVA,
COTA_TVA,
ID_UTIL)
VALUES
(V_ID_LUCRARE,
V_ID_ARTICOL,
V_CANTITATE,
V_PRET,
V_PRET_CU_TVA,
V_COTA_TVA,
V_ID_UTIL);
end adauga_material_de;
------------------------------------------------------------------
procedure modifica_pret_de(V_ID_DEV_ESTIMARE_REP IN NUMBER,
V_PRET IN NUMBER,
V_PRET_CU_TVA IN NUMBER,
V_ID_UTIL IN NUMBER) is
begin
UPDATE dev_estimari_rep
SET PRET = V_PRET, PRET_CU_TVA = V_PRET_CU_TVA
WHERE ID_DEV_ESTIMARE_REP = V_ID_DEV_ESTIMARE_REP;
end modifica_pret_de;
------------------------------------------------------------------
procedure modifica_cota_tva_de(V_ID_DEV_ESTIMARE_REP IN NUMBER,
V_COTA_TVA IN NUMBER,
V_ID_UTIL IN NUMBER) is
begin
UPDATE dev_estimari_rep
SET COTA_TVA = V_COTA_TVA
WHERE ID_DEV_ESTIMARE_REP = V_ID_DEV_ESTIMARE_REP;
end modifica_cota_tva_de;
------------------------------------------------------------------
procedure modifica_cantitate_de(V_ID_DEV_ESTIMARE_REP IN NUMBER,
V_CANTITATE IN NUMBER,
V_ID_UTIL IN NUMBER) is
begin
UPDATE dev_estimari_rep
SET CANTITATE = V_CANTITATE
WHERE ID_DEV_ESTIMARE_REP = V_ID_DEV_ESTIMARE_REP;
end modifica_cantitate_de;
------------------------------------------------------------------
procedure sterge_articol_de(V_ID_DEV_ESTIMARE_REP IN NUMBER,
V_ID_UTIL IN NUMBER) is
begin
UPDATE dev_estimari_rep
SET STERS = 1, ID_UTILS = V_ID_UTIL, DATAORAS = SYSDATE
WHERE ID_DEV_ESTIMARE_REP = V_ID_DEV_ESTIMARE_REP;
end sterge_articol_de;
------------------------------------------------------------------
procedure adauga_pret(V_ID_MARCA IN NUMBER,
V_ID_ASIGURATOR IN NUMBER,
V_PRET IN NUMBER,
V_PRET_VAL IN NUMBER,
V_CURS IN NUMBER,
V_ID_VALUTA IN NUMBER,
V_INACTIV IN NUMBER) is
begin
INSERT INTO dev_nom_preturi
(id_marca, id_asigurator, Pret, Pret_val, Curs, Id_valuta, inactiv)
VALUES
(V_ID_MARCA,
V_ID_ASIGURATOR,
V_PRET,
V_PRET_VAL,
V_CURS,
V_ID_VALUTA,
V_INACTIV);
end adauga_pret;
------------------------------------------------------------------
procedure modifica_pret(V_ID_PRET IN NUMBER,
V_ID_MARCA IN NUMBER,
V_ID_ASIGURATOR IN NUMBER,
V_PRET IN NUMBER,
V_PRET_VAL IN NUMBER,
V_CURS IN NUMBER,
V_ID_VALUTA IN NUMBER,
V_INACTIV IN NUMBER) is
begin
UPDATE DEV_NOM_PRETURI
SET ID_MARCA = V_ID_MARCA,
ID_ASIGURATOR = V_ID_ASIGURATOR,
PRET = V_PRET,
PRET_VAL = V_PRET_VAL,
CURS = V_CURS,
ID_VALUTA = V_ID_VALUTA,
INACTIV = V_INACTIV
WHERE ID_PRET = V_ID_PRET;
end modifica_pret;
------------------------------------------------------------------
procedure sterge_pret(V_ID_PRET IN NUMBER, V_ID_UTILS IN NUMBER) is
begin
UPDATE DEV_NOM_PRETURI SET STERS = 1 WHERE ID_PRET = V_ID_PRET;
end sterge_pret;
------------------------------------------------------------------
procedure adauga_ansamblu(V_SCHEMA IN VARCHAR2,
V_DENUMIRE IN VARCHAR2,
V_INACTIV IN NUMBER,
V_ID_UTIL IN NUMBER) is
V_TOTAL NUMBER(5);
begin
SELECT COUNT(*)
INTO V_TOTAL
FROM DEV_NOM_ANSAMBLE
WHERE DENUMIRE = V_DENUMIRE
AND STERS = 0;
IF V_TOTAL = 0 THEN
INSERT INTO DEV_NOM_ANSAMBLE
(DENUMIRE, INACTIV)
VALUES
(V_DENUMIRE, V_INACTIV);
ELSE
RAISE_APPLICATION_ERROR(-20000,
'Mai exista un ansamblu cu aceasta denumire!');
END IF;
end;
------------------------------------------------------------------
procedure modifica_ansamblu(V_SCHEMA IN VARCHAR2,
V_ID_ANSAMBLU IN NUMBER,
V_DENUMIRE IN VARCHAR2,
V_INACTIV IN NUMBER,
V_ID_UTIL IN NUMBER) is
V_TOTAL NUMBER(5);
begin
SELECT COUNT(*)
INTO V_TOTAL
FROM DEV_NOM_ANSAMBLE
WHERE DENUMIRE = V_DENUMIRE
AND STERS = 0
AND ID_ANSAMBLU <> V_ID_ANSAMBLU;
IF V_TOTAL = 0 THEN
UPDATE DEV_NOM_ANSAMBLE
SET DENUMIRE = V_DENUMIRE, INACTIV = V_INACTIV
WHERE ID_ANSAMBLU = V_ID_ANSAMBLU;
ELSE
RAISE_APPLICATION_ERROR(-20000,
'Mai exista un ansamblu cu aceasta denumire!');
END IF;
end;
------------------------------------------------------------------
procedure sterge_ansamblu(V_SCHEMA IN VARCHAR2,
V_ID_ANSAMBLU IN NUMBER,
V_ID_UTIL IN NUMBER) is
V_TOTAL NUMBER(10);
begin
SELECT COUNT(*)
INTO V_TOTAL
FROM DEV_NOM_NORME
WHERE ID_ANSAMBLU = V_ID_ANSAMBLU
AND STERS = 0;
IF V_TOTAL = 0 THEN
UPDATE DEV_NOM_ANSAMBLE
SET STERS = 1
WHERE ID_ANSAMBLU = V_ID_ANSAMBLU;
ELSE
RAISE_APPLICATION_ERROR(-20000,
'Acest ansamblu este folosit la norme si nu poate fi sters!');
END IF;
end;
------------------------------------------------------------------
procedure adauga_norma(V_ID_MASINA IN NUMBER,
V_ID_ANSAMBLU IN NUMBER,
V_DENOP IN VARCHAR2,
V_CODOP IN VARCHAR2,
V_TIMPN IN NUMBER,
V_INACTIV IN NUMBER,
V_ID_UTIL IN NUMBER) is
begin
INSERT INTO DEV_NOM_NORME
(ID_MASINA, ID_ANSAMBLU, DENOP, CODOP, TIMPN, INACTIV)
VALUES
(V_ID_MASINA, V_ID_ANSAMBLU, V_DENOP, V_CODOP, V_TIMPN, V_INACTIV);
end adauga_norma;
------------------------------------------------------------------
procedure modifica_norma(V_ID_NORME IN NUMBER,
V_ID_MASINA IN NUMBER,
V_ID_ANSAMBLU IN NUMBER,
V_DENOP IN VARCHAR2,
V_CODOP IN VARCHAR2,
V_TIMPN IN NUMBER,
V_INACTIV IN NUMBER,
V_ID_UTIL IN NUMBER) is
begin
UPDATE dev_nom_norme
SET id_masina = V_ID_MASINA,
id_ansamblu = V_ID_ANSAMBLU,
Denop = V_DENOP,
Codop = V_CODOP,
timpn = V_TIMPN,
inactiv = V_INACTIV
where id_norme = V_ID_NORME;
end modifica_norma;
------------------------------------------------------------------
procedure sterge_norma(V_ID_NORME IN NUMBER, V_ID_UTIL IN NUMBER) is
begin
nomdelproc('DEV_NOM_NORME',
'ID_NORME',
V_ID_NORME,
'DEV_OPER',
'ID_NORME');
end sterge_norma;
------------------------------------------------------------------
PROCEDURE adauga_mecanic(V_SCHEMA IN VARCHAR2,
V_ID_PART IN NUMBER,
V_ID_SECTIE IN NUMBER,
V_INACTIV IN NUMBER,
V_ID_UTIL IN NUMBER) IS
V_NR_INREGISTRARI NUMBER(10);
BEGIN
SELECT COUNT(*)
INTO V_NR_INREGISTRARI
FROM DEV_MECANICI
WHERE ID_PART = V_ID_PART
AND STERS = 0;
IF V_NR_INREGISTRARI > 0 THEN
RAISE_APPLICATION_ERROR(-20000,
'Acest mecanic este deja inregistrat!');
END IF;
INSERT INTO /*+ APPEND */
DEV_MECANICI
(ID_PART, ID_SECTIE, INACTIV)
VALUES
(V_ID_PART, V_ID_SECTIE, V_INACTIV);
END adauga_mecanic;
------------------------------------------------------------------
PROCEDURE modifica_mecanic(V_SCHEMA IN VARCHAR2,
V_ID_MECANIC IN NUMBER,
V_ID_PART IN NUMBER,
V_ID_SECTIE IN NUMBER,
V_INACTIV IN NUMBER,
V_ID_UTIL IN NUMBER) IS
BEGIN
UPDATE DEV_MECANICI
SET ID_SECTIE = V_ID_SECTIE, INACTIV = V_INACTIV
WHERE ID_MECANIC = V_ID_MECANIC;
END modifica_mecanic;
------------------------------------------------------------------
PROCEDURE sterge_mecanic(V_SCHEMA IN VARCHAR2,
V_ID_MECANIC IN NUMBER,
V_ID_UTIL IN NUMBER) IS
BEGIN
nomdelproc(V_SCHEMA || '.DEV_MECANICI',
'ID_MECANIC',
V_ID_MECANIC,
V_SCHEMA || '.DEV_OPER_MECANICI',
'ID_MECANIC');
END sterge_mecanic;
------------------------------------------------------------------
PROCEDURE adauga_asigurator(V_ID_PART IN NUMBER, V_ID_UTIL IN NUMBER) is
V_NR_INREGISTRARI NUMBER(10);
BEGIN
SELECT COUNT(*)
INTO V_NR_INREGISTRARI
FROM DEV_NOM_ASIGURATORI
WHERE STERS = 0
AND ID_PART = V_ID_PART;
IF V_NR_INREGISTRARI > 0 THEN
RAISE_APPLICATION_ERROR(-20000,
'Acest asigurator mai exista deja in baza de date!');
ELSE
INSERT INTO DEV_NOM_ASIGURATORI (ID_PART) VALUES (V_ID_PART);
END IF;
END adauga_asigurator;
------------------------------------------------------------------
PROCEDURE modifica_asigurator(V_ID_ASIGURATOR IN NUMBER,
V_INACTIV IN NUMBER,
V_ID_UTIL IN NUMBER) is
BEGIN
UPDATE DEV_NOM_ASIGURATORI
SET INACTIV = V_INACTIV
WHERE ID_ASIGURATOR = V_ID_ASIGURATOR;
END modifica_asigurator;
------------------------------------------------------------------
PROCEDURE set_perioada_raport(V_LUNA_I IN NUMBER,
V_AN_I IN NUMBER,
V_LUNA_F IN NUMBER,
V_AN_F IN NUMBER) IS
BEGIN
pack_auto.v_data_i := TO_DATE(LPAD(V_LUNA_I, 2, '0') || V_AN_I ||
' 00:00:00',
'MMYYYY HH24:mi:ss');
pack_auto.v_data_f := LAST_DAY(TO_DATE(LPAD(V_LUNA_F, 2, '0') || V_AN_F ||
' 23:59:00',
'MMYYYY HH24:mi:ss'));
END set_perioada_raport;
------------------------------------------------------------------
FUNCTION get_datai RETURN DATE IS
BEGIN
RETURN pack_auto.v_data_i;
END get_datai;
------------------------------------------------------------------
FUNCTION get_dataf RETURN DATE IS
BEGIN
RETURN pack_auto.v_data_f;
END get_dataf;
------------------------------------------------------------------
FUNCTION getOptiuneInchidere(V_TIP IN NUMBER) RETURN NUMBER IS
V_INCH_VALIDARE DEV_TIP_DEVIZ.INCH_VALIDARE%TYPE;
BEGIN
BEGIN
SELECT INCH_VALIDARE
INTO V_INCH_VALIDARE
FROM DEV_TIP_DEVIZ
WHERE ID_TIP = V_TIP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000, 'Optiune invalida!');
END;
RETURN V_INCH_VALIDARE;
END;
------------------------------------------------------------------
procedure setOptiuneInchidere(V_TIP IN NUMBER,
V_INCHIDERE_VALIDARE IN NUMBER) is
BEGIN
UPDATE DEV_TIP_DEVIZ
SET INCH_VALIDARE = V_INCHIDERE_VALIDARE
WHERE ID_TIP = V_TIP;
END;
------------------------------------------------------------------
PROCEDURE citeste_date_avans(V_SIR_ID_LUCRARE IN VARCHAR2,
V_DELIMITATOR IN VARCHAR2,
V_CURSOR OUT pack_types.tip_cursor) IS
V_AN NUMBER(4);
V_LUNA NUMBER(2);
V_AN_RON NUMBER(4);
V_LUNA_RON NUMBER(2);
BEGIN
V_AN := pack_sesiune.getan();
V_LUNA := pack_sesiune.getluna();
V_AN_RON := pack_Sesiune.get_ANRON();
V_LUNA_RON := pack_sesiune.get_lunaron();
OPEN V_CURSOR FOR
select a.id_lucrare,
(case
when a.luna + a.an * 12 < V_LUNA_RON + V_AN_RON * 12 and
V_AN * 12 + V_LUNA >= V_AN_RON * 12 + V_LUNA_RON then
a.suma / 10000
else
a.suma
end) as avans,
(case
when a.scc = '4427' then
0
when a.luna + a.an * 12 < V_LUNA_RON + V_AN_RON * 12 and
V_AN * 12 + V_LUNA >= V_AN_RON * 12 + V_LUNA_RON then
a.suma / 10000
else
a.suma
end) as avansftva,
(case
when row_number() over(partition by a.id_lucrare,
a.id_fact order by a.id_fact) = 1 then
(select sum(case
when luna + an * 12 < V_LUNA_RON + V_AN_RON * 12 and
V_AN * 12 + V_LUNA >=
V_AN_RON * 12 + V_LUNA_RON then
suma / 10000
else
suma
end)
from act
where id_lucrare = a.id_lucrare
and id_factc = a.id_fact
and scd like '5%'
and sters = 0)
else
0
end) as incasat,
a.scd,
a.ascd,
a.scc,
a.ascc,
a.id_partc,
a.id_partd,
a.id_sectie,
b.nume as partc,
c.nume as partd,
d.sectie,
a.id_jtva_coloana,
a.proc_tva,
a.taxcode,
e.nrord
from (select *
from act
where sters = 0
and id_set in (31001, 31002, 31012, 31013)
and scd = '4111'
and id_lucrare in
(SELECT *
FROM TABLE(charn2collection(V_SIR_ID_LUCRARE,
V_DELIMITATOR)))) a
left join nom_parteneri b
on a.id_partc = b.id_part
left join nom_parteneri c
on a.id_partd = c.id_part
left join nom_sectii d
on a.id_sectie = d.id_sectie
left join vnom_lucrari e
on a.id_lucrare = e.id_lucrare;
end citeste_date_avans;
------------------------------------------------------------------
PROCEDURE citeste_sume_stornare(V_SIR_ID_LUCRARE IN VARCHAR2,
V_DELIMITATOR IN VARCHAR2,
V_CURSOR OUT pack_types.tip_cursor) IS
BEGIN
OPEN V_CURSOR FOR
select sum((-1) * pret) as pret,
pret_cu_tva,
proc_tvav,
id_jtva_coloana,
taxcode
from vanzari_detalii
where id_vanzare in
(select id_vanzare
from vanzari
where tip = -12
and sters = 0
and id_lucrare in
(SELECT *
FROM TABLE(charn2collection(V_SIR_ID_LUCRARE,
V_DELIMITATOR))))
and sters = 0
and id_articol in (-100005, -100006)
group by pret_cu_tva, proc_tvav, id_jtva_coloana, taxcode;
END citeste_sume_stornare;
------------------------------------------------------------------
-- citeste procentele de discount pe contractul tip CLIENT AUTO
-- pentru completarea procentelor de discount la facturare
------------------------------------------------------------------
PROCEDURE citeste_discount_contract(V_ID_PART IN CONTRACTE.ID_PART%TYPE,
V_ID_SUCURSALA IN CONTRACTE.ID_SUCURSALA%TYPE,
V_ID_CTR OUT CONTRACTE.ID_CTR%TYPE,
V_DISCOUNT_MANOPERA OUT CTR_ARTICOLE.COEF_DISCOUNT%TYPE,
V_DISCOUNT_MATERIALE OUT CTR_ARTICOLE.COEF_DISCOUNT%TYPE) IS
BEGIN
SELECT min(ctr.id_ctr)
into V_ID_CTR
FROM contracte ctr
where ctr.id_tip_ctr = nIdTipContractAuto
and ctr.id_part = V_ID_PART
and nvl2(V_ID_SUCURSALA, ctr.id_sucursala, -1) =
nvl(V_ID_SUCURSALA, -1);
select max(decode(ca.id_articol,
nIdArticolManopera,
ca.coef_discount,
cast(0 as number(10, 4)))) as discount_manopera,
max(decode(ca.id_articol,
nIdArticolMateriale,
ca.coef_discount,
cast(0 as number(10, 4)))) as discount_materiale
into V_DISCOUNT_MANOPERA, V_DISCOUNT_MATERIALE
from ctr_articole ca
where ca.id_ctr = V_ID_CTR;
END citeste_discount_contract;
------------------------------------------------------------------
-- salveaza procentele de discount pe contractul tip CLIENT AUTO
-- adauga contract/articole daca nu exista
------------------------------------------------------------------
PROCEDURE salveaza_discount_contract(V_ID_PART IN CONTRACTE.ID_PART%TYPE,
V_ID_SUCURSALA IN CONTRACTE.ID_SUCURSALA%TYPE,
V_ID_CTR IN CONTRACTE.ID_CTR%TYPE,
V_DISCOUNT_MANOPERA IN CTR_ARTICOLE.COEF_DISCOUNT%TYPE,
V_DISCOUNT_MATERIALE IN CTR_ARTICOLE.COEF_DISCOUNT%TYPE) IS
lnIdCtr contracte.id_ctr%type := V_ID_CTR;
lcNumar contracte.numar%type := '1';
lnIdSucursala contracte.id_sucursala%type := V_ID_SUCURSALA;
lnIdTipContractAuto contracte.id_tip_ctr%type := nIdTipContractAuto;
lnPret_cu_tva ctr_articole.pret_cu_tva%type := 0;
lnPret_unitar ctr_articole.pret_unitar%type := 0;
lnCant ctr_articole.cant%type := 0;
lnCoef_Discount ctr_articole.coef_discount%type := 0;
lnVal_discount ctr_articole.val_discount%type := 0;
lnId_valuta ctr_articole.id_valuta%type;
lcUm ctr_articole.um%type;
lcExplicatie ctr_articole.explicatie%type := 'CONTRACT SERVICE AUTO CU DISCOUNT, ADAUGAT AUTOMAT';
lnParametru_prog number(1) := 1; -- CONTRACTE CLIENTI
lnArticole number(1) := 0;
ldDataCtr contracte.data%type := TRUNC(SYSDATE);
lnIdUtil contracte.id_util%type := -3;
lnIdPolServiceAuto crm_politici_preturi.id_pol%type;
lnIdPolArtMan crm_politici_pret_art.id_pol_art%type;
lnIdPolArtMat crm_politici_pret_art.id_pol_art%type;
BEGIN
lnId_valuta := pack_def.GetIdMonedaNationala();
-- aflu politica de preturi SERVICE AUTO
begin
select pp.id_pol
into lnIdPolServiceAuto
from crm_politici_preturi pp
where pp.nume_lista_preturi = 'SERVICE AUTO'
and pp.sters = 0;
-- aflu articolele MATERIALE, MANOPERA din politica SERVICE AUTO
select ppa.id_pol_art
into lnIdPolArtMan
from crm_politici_pret_art ppa
where ppa.id_pol = lnIdPolServiceAuto
and ppa.id_articol = nIdArticolManopera;
select ppa.id_pol_art
into lnIdPolArtMat
from crm_politici_pret_art ppa
where ppa.id_pol = lnIdPolServiceAuto
and ppa.id_articol = nIdArticolMateriale;
exception
when NO_DATA_FOUND then
raise_application_error(-20000,
'Nu sunt definite articolele Materiale si Manopera in politica de preturi SERVICE AUTO!');
when OTHERS then
raise;
end;
-- adaugare contract SERVICE AUTO
IF lnIdCtr IS NULL THEN
pack_crm.adauga_contract(lnIdTipContractAuto,
lnIdSucursala,
lnIdCtr,
lcNumar);
IF lnIdCtr IS NULL THEN
raise_application_error(-20000,
'Nu s-a putut salva contractul tip SERVICE AUTO!');
ELSE
update contracte
set id_part = V_ID_PART,
data = ldDataCtr,
numar = lcNumar,
id_valuta = lnId_valuta,
data_inceput = ldDataCtr,
numar_intern = lcNumar,
id_util = lnIdUtil,
dataora = SYSDATE
where id_ctr = lnIdCtr;
END IF;
END IF;
-- adaugare articol Manopera in ctr_articole
select count(*) as nr
into lnArticole
from ctr_articole ca
where id_ctr = lnIdCtr
and id_pol_art = lnIdPolArtMan;
if lnArticole = 0 then
lnCoef_Discount := V_DISCOUNT_MANOPERA;
pack_crm.adauga_ctr_art(lnIdCtr,
lnIdPolArtMan,
nIdArticolManopera,
lnPret_cu_tva,
lnPret_unitar,
lnCant,
lnCoef_Discount,
lnVal_discount,
lnId_valuta,
lcUm,
lcExplicatie);
end if;
-- adaugare articol Materiale in ctr_articole
select count(*) as nr
into lnArticole
from ctr_articole ca
where id_ctr = lnIdCtr
and id_pol_art = lnIdPolArtMat;
if lnArticole = 0 then
lnCoef_Discount := V_DISCOUNT_MATERIALE;
pack_crm.adauga_ctr_art(lnIdCtr,
lnIdPolArtMat,
nIdArticolMateriale,
lnPret_cu_tva,
lnPret_unitar,
lnCant,
lnCoef_Discount,
lnVal_discount,
lnId_valuta,
lcUm,
lcExplicatie);
end if;
-- actualizare discount articol Manopera in ctr_articole
begin
lnCoef_Discount := V_DISCOUNT_MANOPERA;
select pret_unitar, cant, val_discount, id_valuta, pret_cu_tva
into lnPret_unitar,
lnCant,
lnVal_discount,
lnId_valuta,
lnPret_cu_tva
from ctr_articole
where id_ctr = lnIdCtr
and id_pol_art = lnIdPolArtMan;
pack_crm.modifica_ctr_art(lnParametru_prog,
lnIdCtr,
lnIdPolArtMan,
nIdArticolManopera,
lnCant,
lnPret_cu_tva,
lnPret_unitar,
lnCoef_Discount,
lnVal_discount,
lnId_valuta);
exception
when NO_DATA_FOUND then
RAISE_APPLICATION_ERROR(-20000,
'Nu exista articolul MANOPERA in contractul tip SERVICE AUTO!');
when OTHERS then
RAISE;
end;
-- actualizare discount articol Manopera in ctr_articole
begin
lnCoef_Discount := V_DISCOUNT_MATERIALE;
select pret_unitar, cant, val_discount, id_valuta, pret_cu_tva
into lnPret_unitar,
lnCant,
lnVal_discount,
lnId_valuta,
lnPret_cu_tva
from ctr_articole
where id_ctr = lnIdCtr
and id_pol_art = lnIdPolArtMat;
pack_crm.modifica_ctr_art(lnParametru_prog,
lnIdCtr,
lnIdPolArtMat,
nIdArticolMateriale,
lnCant,
lnPret_cu_tva,
lnPret_unitar,
lnCoef_Discount,
lnVal_discount,
lnId_valuta);
exception
when NO_DATA_FOUND then
RAISE_APPLICATION_ERROR(-20000,
'Nu exista articolul MATERIALE in contractul tip SERVICE AUTO!');
when OTHERS then
RAISE;
end;
END salveaza_discount_contract;
------------------------------------------------------------------
procedure dev_arhiveaza_comanda(tnid_ordl in number, tnid_util in number) as
lnNevalidate NUMBER(3);
lnLunaDeschisa NUMBER(1);
ldData DATE;
lnLuna NUMBER(2);
lnAn NUMBER(4);
begin
ldData := SYSDATE;
lnLuna := EXTRACT(MONTH FROM ldData);
lnAn := EXTRACT(YEAR FROM ldData);
SELECT COUNT(*)
INTO lnLunaDeschisa
FROM CALENDAR
WHERE AN = lnAn
AND LUNA = lnLuna;
IF lnLunaDeschisa = 0 THEN
RAISE_APPLICATION_ERROR(-20000,
'Nu puteti arhiva comanda deoarece nu este deschisa luna ' ||
lnLuna || '/' || lnAn || ' !');
END IF;
UPDATE DEV_ORDL
SET INCHIS_FORTAT = 1,
ID_UTIL_INCHIS = tnid_util,
DATAORAINCHIS = ldData
WHERE ID_ORDL = tnid_ordl;
end;
------------------------------------------------------------------
procedure dev_dezarhiveaza_comanda(tnid_ordl in number) AS
begin
UPDATE DEV_ORDL
SET INCHIS_FORTAT = 0, ID_UTIL_INCHIS = 0, DATAORAINCHIS = NULL
WHERE ID_ORDL = tnid_ordl;
end;
end pack_auto;
/
Reference in New Issue View Git Blame Copy Permalink