DEV Community: Fazlı Akça

PostgreSQL Log Viewing

Fazlı Akça — Sun, 07 Dec 2025 21:45:55 +0000

SELECT 
op_date,
op,
op_data ->> 'worder_m_id',
op_data ->> 'item_attribute1_id',
op_data ->> 'qty_man'
FROM uyumlog.log
WHERE table_name = 'prdt_worder_m' and op_data ->> 'worder_m_id'   = '4149' 
ORDER BY op_date asc

PostgreSQL Merge Into

Fazlı Akça — Sun, 07 Dec 2025 21:44:32 +0000

UPDATE PSMT_INVOICE_M

SET    SHIPPING_COUNTRY_ID = SRC.COUNTRY_ID, 
       SHIPPING_CITY_ID    = SRC.CITY_ID,
       SHIPPING_TOWN_ID    = SRC.TOWN_ID 

FROM   ( SELECT 
         PM.INVOICE_M_ID,
         FE.COUNTRY_ID,
         FE.CITY_ID,
         FE.TOWN_ID
         FROM PSMT_INVOICE_M PM  
         JOIN FIND_ENTITY    FE ON PM.ENTITY_ID = FE.ENTITY_ID 

        ) SRC    

WHERE  PSMT_INVOICE_M.INVOICE_M_ID = SRC.INVOICE_M_ID

Oracle Merge Into

Fazlı Akça — Sun, 07 Dec 2025 21:43:51 +0000

MERGE INTO FINT_CAD_D TRG
USING
      (
         SELECT 
         CD.ROWID AS RID,
         CM.CREDIT_ACC_ID
         FROM        FINT_CAD_D CD  
         JOIN  FINT_CAD_M CM ON CD.CAD_M_ID = CM.CAD_M_ID  
         WHERE CM.CAD_M_ID = 4918

       ) SRC  ON (TRG.ROWID = SRC.RID)

WHEN MATCHED THEN 

UPDATE SET TRG.CREDIT_ACC_ID = SRC.CREDIT_ACC_ID

Dynamic List Data Authority

Fazlı Akça — Sun, 07 Dec 2025 21:41:56 +0000

DO $$

DECLARE

result CONSTANT refcursor:= 'result';

BEGIN 

PERFORM RPA_HRMD_REGISTER (@UsrId@::INTEGER);  
PERFORM RPA_HRMD_EMPLOYEE (@UsrId@::INTEGER);

OPEN result FOR 

SELECT

PYR.PAYROLL_ID, 
EMP.EMPLOYEE_ID, 
REG.REGISTER_ID,
REG.REGISTER_CODE    AS "SİCİL NO", 
REG.REGISTER_NAME||' '||REG.REGISTER_SURNAME AS "ADI SOYADI",
REG.CITIZENSHIP_NO   AS "TC KİMLİK NO",
PYR.AMT_NET          AS "NET ÖDENEN"

FROM       HRMT_PAYROLL     PYR   
INNER JOIN RP_HRMD_EMPLOYEE EMP ON PYR.EMPLOYEE_ID = EMP.EMPLOYEE_ID  
INNER JOIN RP_HRMD_REGISTER REG ON REG.REGISTER_ID = EMP.REGISTER_ID  

WHERE TO_CHAR(PYR.PAYROLL_YEAR)  = TO_CHAR('@Year@')  
AND   TO_CHAR(PYR.PAYROLL_MONTH) = TO_CHAR('@Month@')  

ORDER BY REG.REGISTER_CODE;

END
$$;

FETCH ALL FROM result;

Oracle Menu Tree

Fazlı Akça — Sun, 07 Dec 2025 21:38:35 +0000

select sys_connect_by_path (substr(mnu_name,0,50) ,'-->')  as path, level
from appd_menu
start with mnu_id = 1
connect by prior mnu_id = prt_id
order by path

PostgreSQL Menu Tree

Fazlı Akça — Sun, 07 Dec 2025 21:37:29 +0000

with recursive menu_tree (mnu_id, mnu_name, prt_id, mnu_level) as 

   ( 
         select 
         mnu_id, 
         substring(mnu_name,0,60) as mnu_name, 
         prt_id, 
         1 as mnu_level
         from appd_menu 
         where mnu_id = 1 

         union all 

         select 
         m.mnu_id, 
         t.mnu_name||'-->'||m.mnu_name, 
         m.prt_id, 
         t.mnu_level + 1 
         from appd_menu m, menu_tree t 
         where t.mnu_id = m.prt_id 

     ) 

select 

mnu_name, 
split_part(mnu_name,'-->', 1) node1,
split_part(mnu_name,'-->', 2) node2,
split_part(mnu_name,'-->', 3) node3,
split_part(mnu_name,'-->', 4) node4,
split_part(mnu_name,'-->', 5) node5,
split_part(mnu_name,'-->', 6) node6,
split_part(mnu_name,'-->', 7) node7,
split_part(mnu_name,'-->', 8) node8,
mnu_level 

from menu_tree order by mnu_name

PostgreSQL | Calc Age in Year, Month, Day

Fazlı Akça — Sun, 07 Dec 2025 21:33:18 +0000

CREATE OR REPLACE FUNCTION get_age_ymd(p_birthday date)
RETURNS text AS
$$
DECLARE
    v_years int;
    v_months int;
    v_days int;
BEGIN
    SELECT 
        EXTRACT(year FROM age(current_date, p_birthday))::int,
        EXTRACT(month FROM age(current_date, p_birthday))::int,
        EXTRACT(day   FROM age(current_date, p_birthday))::int
    INTO v_years, v_months, v_days;

    RETURN v_years || ' yıl ' || v_months || ' ay ' || v_days || ' gün';
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Örnek Kullanım

SELECT
REG.REGISTER_CODE    AS "Sicil No",
REG.REGISTER_NAME    AS "Adı",
REG.REGISTER_SURNAME AS "Soyadı",
REG.BIRTHDAY         AS "Doğum Tarihi",
GET_AGE_YMD(REG.birthday::date) AS "Yaş"

FROM HRMD_REGISTER REG

Dynamic Popup GridField Update

Fazlı Akça — Thu, 29 Aug 2024 14:57:24 +0000

function CalcQtyPrmSum()
{

GetControl('BwhItemDCollection').GetRowValues(GetControl('BwhItemDCollection').GetFocusedRowIndex(),'Id',GetValues);

function GetValues(Id) {
/*console.log(Id);*/

ClientExecSQL('UPDATE INVD_BWH_ITEM_D 
SET QTY_PRM = ( SELECT 
                COALESCE(SUM(QTY_PRM*PLUS_MINUS),0) AS QTY_PRM
                FROM INVT_ITEM_D
                WHERE BRANCH_ID = (SELECT BRANCH_ID FROM INVD_BWH_ITEM_D WHERE BWH_ITEM_D_ID = @Id::integer)
                AND   WHOUSE_ID = (SELECT WHOUSE_ID FROM INVD_BWH_ITEM_D WHERE BWH_ITEM_D_ID = @Id::integer)
                AND   ITEM_ID   = (SELECT ITEM_ID FROM INVD_BWH_ITEM_D WHERE BWH_ITEM_D_ID = @Id::integer)
                AND   ITEM_ATTRIBUTE1_ID = (SELECT ITEM_ATTRIBUTE1_ID FROM INVD_BWH_ITEM_D WHERE BWH_ITEM_D_ID = @Id::integer)
                AND   FREE_PRM_M_ID = (SELECT FREE_PRM_M_ID FROM INVD_BWH_ITEM_D WHERE BWH_ITEM_D_ID = @Id::integer)    
              )
WHERE BWH_ITEM_D_ID = @Id::integer',
function (p) {
alert('İşlem Tamam'); 
GetControl('BwhItemDCollection').PerformCallback('Refresh'); 
},
Id,Id,Id,Id,Id,Id,Id
)
}
}

Get Employee Experience in Years, Months, Days

Fazlı Akça — Tue, 05 Mar 2024 13:36:22 +0000

CREATE OR REPLACE FUNCTION UYUMSOFT.GET_HRM_REG_EXPERIENCE 
(
   pregisterId integer   
)

RETURNS varchar(100) AS
$body$
DECLARE

   totalDate date;
   totalDay integer;
   ay integer;
   gun integer;
   yil integer;
   res varchar(100);

BEGIN
   res:='';

   SELECT               
   SUM((CASE WHEN TO_DATE(EMP.QUIT_DATE,'DD.MM.YYYY')=TO_DATE('01.01.0001','DD.MM.YYYY') 
      THEN TO_DATE(CURRENT_DATE,'DD.MM.YYYY') 
      ELSE TO_DATE(EMP.QUIT_DATE,'DD.MM.YYYY') END)-TO_DATE(EMP.EMP_DATE,'DD.MM.YYYY')+1) into totalDay
   FROM HRMD_EMPLOYEE EMP
   WHERE EMP.REGISTER_ID = pregisterId;

   totalDate:=TO_DATE('01.01.1900','DD.MM.YYYY') + interval '1 day' * totalDay;          
   yil:= extract( year  from totalDate)-1900;
   ay:= extract( month  from totalDate)-1;
   gun:= extract( day  from totalDate)-1;
   if(yil>0) then
   res:=to_char(yil)||' Yıl ' ;   
   end if;
   if(ay>0) then
   res:=res||to_char(ay)||' Ay ' ;   
   end if;
    if(gun>0) then
   res:=res||to_char(gun)||' Gün' ;   
   end if;

RETURN res;

END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
PARALLEL UNSAFE
COST 100;

MainList Dynamic Popup Ekleme

Fazlı Akça — Mon, 04 Mar 2024 08:06:23 +0000

function OpenReport4 () 

    {
        var Id=GetGridKeyValue(myListPage);
        var prmKeys = ["CurrentIdentity", "PayrolId"];
        var prmValues = ["12345",Id];

        OpenReportPDF('12000021', prmKeys, prmValues);

    }

SetControlValue From ClientExecSQL With Parameter

Fazlı Akça — Mon, 15 Jan 2024 14:56:49 +0000

<row>
    <cell colspan="2">
        <control FieldName="ZZClientExecProc1" ControlType="Button" Caption="Yenile" ControlEditEnabled="False" Width="150">
            <ClientSideEvents ValueChanged="function (s,e) 
                              {ClientExecSQL('select AVG_BALANCE_DATE||chr(124)||AMT_BALANCE from table(get_entity_balance(@p1,@p2,@p3))', function (p) { var arr = p.split('|'); SetControlValue('ZZAvgBalanceDate',arr[0]),SetControlValue('ZZAmtBalance',arr[1]) } ,GetControlValue('CoId'), GetControlValue('CreditAccId')||'0', GetControlValue('DocDate'));}">
            </ClientSideEvents> 
        </control>
    </cell>
</row>

<row>
<cell colspan="3">
<control FieldName="ZZAmtBalance" ControlType="SpinEdit" Caption="Bakiye Tutar" ControlEnabled="false" ServerAttribute="BackColor=#99FF99"/>
</cell>
</row>
<row>
<cell colspan="3">
<control FieldName="ZZAvgBalanceDate" ControlType="DateEdit" Caption="Ortalama Vade Tarih" ControlEnabled="false" ServerAttribute="BackColor=#99FF99"/>
</cell>
</row>

SQL String Mask

Fazlı Akça — Tue, 07 Nov 2023 14:36:40 +0000

SELECT
REG.register_full_name,
rpad(left(reg.register_name,1),length(reg.register_name)-1,'*') as "Adı",
rpad(left(reg.register_surname,1),length(reg.register_surname)-1,'*') as "Soyadı"
FROM HRMD_REGISTER REG;

SELECT
REG.register_full_name,
left(reg.register_name,1)||repeat('*',length(reg.register_name)-1) as "Adı",
left(reg.register_surname,1)||repeat('*',length(reg.register_surname)-1) as "Soyadı"
FROM HRMD_REGISTER REG;

SELECT
REG.register_full_name,
regexp_replace(reg.register_name,'\Y\w','*','g') as "Adı",
regexp_replace(reg.register_surname,'\w\Y','*','g') as "Soyadı"
FROM HRMD_REGISTER REG;