--########## RUN SEPARATE 
create or replace PROCEDURE proc_in_findSeller
(
sellerFirstName IN VARCHAR2, 
sellerLastName IN VARCHAR2,  
sellerPersonalNumber IN VARCHAR2,
entries_cursor_out  OUT SYS_REFCURSOR
)

AS

 rowCount INT;
 somethingFound INT;
 appendCondition VARCHAR2(1000);
 sqlQuery  VARCHAR2(2000);
BEGIN
        rowCount := 0;
        somethingFound := 0;
	IF NOT(sellerPersonalNumber IS NULL OR sellerPersonalNumber||'x' = 'x') OR
	   NOT(sellerLastName||'x' = 'x' OR sellerLastName IS NULL) OR
	   NOT(sellerFirstName||'x' = 'x' OR sellerFirstName IS NULL) THEN
      OPEN entries_cursor_out FOR
        select S.firstname jmeno, S.lastname, SS.staff_id id_uzivatel, SS.unit_id id_skupina, SS.division_id  cisloskupiny, SS.staff_id
        from 
            SYN_RS_DSMP_SALESSTAFF SS
            join SYN_RS_DSMP_STAFF S on S.staff_id = SS.staff_id
            join SYN_RS_DSMP_STAFFSTATUS SSTAT on SSTAT.staffstatus_id = S.staffstatus_id
        where 
           --S.STAFFSTATUS_ID != 'T' and
          (UPPER(trim(SS.staff_id)) LIKE '%'||UPPER(trim(sellerPersonalNumber))||'%' or sellerPersonalNumber IS NULL) and 
	      (UPPER(trim(S.lastname)) LIKE '%'||UPPER(trim(sellerLastName))||'%' or sellerLastName IS NULL) and 
          (UPPER(trim(S.firstname)) LIKE '%'||UPPER(trim(sellerFirstName))||'%' or sellerFirstName IS NULL);
	
    END IF;
END;
/
--########## RUN SEPARATE 
CREATE OR REPLACE PROCEDURE savePayplanSendDate
(
    idInvoiceVal IN RAW,
    userAddedVal IN VARCHAR2,
    userWorkstationVal IN VARCHAR2,
    csPayPlanSendDateVal IN DATE
)
AS
    cntCsPayplanNextVal INT := 1;
    csPayPlanSendDateOldVal DATE;
    csPayPlanSendDateNewVal DATE;
    csPayplanNextVal RAW(16);
    
    loopCnt INT := 1;
BEGIN
    csPayplanNextVal := idInvoiceVal;
    csPayPlanSendDateNewVal := csPayPlanSendDateVal;

    WHILE cntCsPayplanNextVal > 0 AND loopCnt < 1000
    LOOP
        loopCnt := loopCnt + 1;

        SELECT CS_PAYPLAN_SEND_DATE INTO csPayPlanSendDateOldVal
        FROM invoice
        WHERE id = csPayplanNextVal;

        UPDATE invoice SET    
            userAdded = userAddedVal, 
            userWorkstation = userWorkstationVal,
            CS_PAYPLAN_SEND_DATE = csPayPlanSendDateNewVal
        WHERE id = csPayplanNextVal;
        
        INSERT INTO CSInvoiceUpdateArchive
            (idInvoice, userAdded, userWorkstation, CS_PAYPLAN_SEND_DATE_OLD, CS_PAYPLAN_SEND_DATE_NEW)
            VALUES (csPayplanNextVal, userAddedVal, userWorkstationVal, csPayPlanSendDateOldVal, csPayPlanSendDateNewVal);

        SELECT count(id) INTO cntCsPayplanNextVal
        FROM invoice
        WHERE CS_PAYPLAN_PREV = csPayplanNextVal;

        IF cntCsPayplanNextVal > 0 THEN
            csPayPlanSendDateNewVal := csPayPlanSendDateNewVal + 30;
            
            SELECT id INTO csPayplanNextVal
            FROM invoice
            WHERE CS_PAYPLAN_PREV = csPayplanNextVal;
        END IF;
    END LOOP;
END;
/

--########## RUN SEPARATE
--########## BEGIN INSERT THE SCRIPT VERSION INFORMATION
UPDATE dbVersion 
SET isActive = 0 
WHERE versionType = 'PROC_UPDATE'
--########## RUN SEPARATE
INSERT INTO dbVersion (majorVersion, minorVersion, versionType, isActive)
VALUES (1, 5, 'PROC_UPDATE', 1)
--########## RUN SEPARATE
--########## END INSERT THE SCRIPT VERSION INFORMATION
