MstCode_id_mismatch

 --------- Main Account - Group and sub group code mismatch ------- 

SELECT m.comp_code, m.mainac_code, m.mainac_desc, m.ac_group_code, m.ac_sub_group_code, m.ac_category  

  FROM Fa_mainac_mst m  

WHERE (m.comp_code, m.ac_group_code, m.ac_sub_group_code) not in (SELECT g.comp_code, g.ac_group_code, s.ac_sub_group_code 

                                                                     FROM Fa_ac_group_mst g, Fa_ac_sub_group_mst s  

                                                                    WHERE g.ac_group_id = s.ac_group_id) 

/                                                                      

  

--------- Main Account - Group and sub group Id mismatch -------       

SELECT m.comp_code, m.mainac_code, m.mainac_desc, m.ac_group_code, m.ac_sub_group_code, m.ac_category  

  FROM Fa_mainac_mst m  

WHERE (m.comp_code, m.ac_group_id, m.ac_sub_group_id) not in (SELECT g.comp_code, g.ac_group_id, s.ac_sub_group_id 

                                                                 FROM Fa_ac_group_mst g, Fa_ac_sub_group_mst s  

                                                                WHERE g.ac_group_id = s.ac_group_id) 

/ 

------- Hdr - Mainac and subac Code mismatch ------- 

SELECT h.trans_cntrl_no, h.comp_code, h.vch_grp_type, h.vch_code, h.vch_no, h.vch_dt, h.mainac_code, h.subac_code,  

       Decode(h.vch_drcr_tag, 'D', h.vch_amt, (Nvl(h.vch_amt,0)*(-1))) vch_amnt  

  FROM Fa_trans_hdr h  

WHERE h.vch_grp_type <> 'J' 

   AND (h.comp_code, h.mainac_code, h.subac_code) not in (SELECT a.comp_code, a.mainac_code, a.subac_code  

                                                            FROM Fa_ac_vu a ) 

ORDER BY h.vch_dt, h.comp_code, h.trans_cntrl_no       

/ 

  

------- Hdr - Mainac and subac Id mismatch ------- 

SELECT h.trans_cntrl_no, h.comp_code, h.vch_grp_type, h.vch_code, h.vch_no, h.vch_dt, h.mainac_code, h.subac_code,  

       h.mainac_id, h.subac_id, Decode(h.vch_drcr_tag, 'D', h.vch_amt, (Nvl(h.vch_amt,0)*(-1))) vch_amnt  

  FROM Fa_trans_hdr h  

WHERE h.vch_grp_type <> 'J' 

   AND (h.comp_code, h.mainac_id, h.subac_id) not in (SELECT a.comp_code, a.mainac_id, a.subac_id  

                                                        FROM Fa_ac_vu a ) 

ORDER BY h.vch_dt, h.comp_code, h.trans_cntrl_no      

/ 

 Update fa_trans_hdr h set subac_id = (select a.subac_id from fa_ac_vu a where a.comp_code = h.comp_code and a.mainac_code = h.mainac_code and a.subac_code = h.subac_code)  

where trans_cntrl_no in (select b.trans_cntrl_no from oa_th_temp.fa_trans_hdr_subac_mm_190521 b) 

/ 

 

------- Dtl - Mainac and subac Code mismatch ------- 

SELECT h.trans_cntrl_no, h.comp_code, h.vch_grp_type, h.vch_code, h.vch_no, h.vch_dt, d.mainac_code, d.subac_code,  

       Decode(d.trans_drcr_tag, 'D', d.trans_amt, (Nvl(d.trans_amt,0)*(-1))) trans_amnt  

  FROM Fa_trans_hdr h, Fa_trans_dtl d  

WHERE h.trans_cntrl_no = d.trans_cntrl_no  

   AND (d.comp_code,d.mainac_code, d.subac_code) not in (SELECT a.comp_code,a.mainac_code, a.subac_code  

                                                            FROM Fa_ac_vu a ) 

ORDER BY h.vch_dt, h.comp_code, h.trans_cntrl_no       

/ 

  

------- Dtl - Mainac and subac Id mismatch ------- 

SELECT h.trans_cntrl_no, h.comp_code, h.vch_grp_type, h.vch_code, h.vch_no, h.vch_dt, d.mainac_code, d.subac_code,  

       h.mainac_id, h.subac_id, Decode(d.trans_drcr_tag, 'D', d.trans_amt, (Nvl(d.trans_amt,0)*(-1))) trans_amnt  

  FROM Fa_trans_hdr h, Fa_trans_dtl d  

WHERE h.trans_cntrl_no = d.trans_cntrl_no  

   AND (d.comp_code, d.mainac_id, d.subac_id) not in (SELECT a.comp_code, a.mainac_id, a.subac_id  

                                                        FROM Fa_ac_vu a ) 

ORDER BY h.vch_dt, h.comp_code, h.trans_cntrl_no  

/  

 

SELECT h.trans_cntrl_no, h.comp_code, h.vch_grp_type, h.vch_code, h.vch_no, h.vch_dt, d.mainac_code, d.subac_code,   

       d.mainac_id, d.subac_id, Decode(d.trans_drcr_tag, 'D', d.trans_amt, (Nvl(d.trans_amt,0)*(-1))) trans_amnt   

  FROM Fa_trans_hdr h, Fa_trans_dtl d   

WHERE h.trans_cntrl_no = d.trans_cntrl_no   

   AND (d.comp_code, d.mainac_code, d.mainac_id, d.subac_code, d.subac_id) not in  

        (SELECT a.comp_code,a.mainac_code, a.mainac_id, a.subac_code, a.subac_id FROM Fa_ac_vu a )  

   --AND h.comp_code = '001' 

ORDER BY h.vch_dt, h.comp_code, h.trans_cntrl_no    

/  

 

-------------- Finyr code mismatch --------- 

Select * from fa_trans_hdr h, cm_finyr_mst f  

Where h.comp_code = f.comp_code 

and h.finyr_code = f.finyr_code 

and h.vch_dt not between f.finyr_from_dt and f.finyr_to_dt 

/ 

---------- Ledger and tb misamatch ------- 
select trans_cntrl_no, ledg_net, tb_net from ( 
select trans_cntrl_no, sum(ledg_dr), suM(ledg_cr), sum(tb_dr), sum(tb_cr), sum(ledg_dr)-suM(ledg_cr) ledg_net, sum(tb_dr)-sum(tb_cr) tb_net 
from ( 
select trans_cntrl_no, dr_amt ledg_dr, cr_amt  ledg_cr, 0 tb_dr, 0 tb_cr 
from fa_ledger_vu where vch_dt = '04-Aug-2019' and mainac_code = '210001' 
union all 
select trans_cntrl_no, 0, 0, Decode(drcr_tag, 'D', trans_amt, 0), Decode(drcr_tag, 'C', trans_amt, 0) from fa_trans_all_vu where vch_dt = '04-Aug-2019' and mainac_code = '210001' 
) 
group by trans_cntrl_no 
) 
where ledg_net <> tb_net 
/ 

update fa_trans_hdr set vch_dt = trunc(vch_dt)  
where (to_char(vch_dt, 'HH24') <> '00' or to_char(vch_dt, 'MI') <> '00' or to_char(vch_dt, 'SS') <> '00')  
/ 

  

update fa_trans_hdr set vch_amt = round(vch_amt, 2) where mod(vch_amt, round(vch_amt, 2)) <> 0  and Nvl(vch_amt, 0) <> 0 

interview questions sql/plsql

 select fa_trans_dtl_seq.nextval,fa_trans_dtl_seq.currval from dual  

-- 120507963,120507962

/

select fa_trans_dtl_seq.nextval,fa_trans_dtl_seq.nextval from dual  

-- 120507964,120507965

/

select fa_trans_dtl_seq.nextval from dual -- 120507962 

/

select fa_trans_dtl_seq.currval from dual  -- 120507962

/

select REGEXP_COUNT('/abc/sample/xyz/hello/oracle/sql/plsql/','/') from dual

/

select 'dept_'||deptno ,sum(sal) sal

from emp

group by deptno

/

SELECT * FROM

(

  SELECT deptno,sal --, column2

  FROM emp

 -- WHERE conditions

)

PIVOT 

(

  sum(sal)

  FOR deptno

  IN ( 10,20,30) --| subquery

)

--ORDER BY expression [ ASC | DESC ];

/

select WM_CONCAT(ename) from emp

/

select LISTAGG(ename, '/') within group (order by ename) 

from emp

/

adf_main_mod_pkg.updateUserPass

file movement from one server to another server

 f:(DB server)

cd export_reports
move *.pdf z:\finance (DMS Server)
move *.csv z:\finance
Exit

AS OF TIMESTAMP

SELECT * FROM employees

AS OF TIMESTAMP

TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')

WHERE last_name = 'Chung';

decr encr

 FUNCTION decr (var1 varchar) RETURN CHAR IS

 LEN Number(2) ;

 i  Number(2) ;

 CONV_LET  Varchar2(1) ;

 CONV_STR  Varchar2(30) ;

 incr number;

Begin

 LEN:=To_number(Length(Var1));

 incr:=20;

 For i in 1..Len Loop

   conv_let:=chr(Ascii(Substr(Var1, i,1)) - (incr+i+len));

   conv_str := conv_str||conv_let;

 End Loop ;

 Return Conv_str;

END;

/

FUNCTION encr (var1 varchar) RETURN CHAR IS

 LEN Number(2) ;

 i  Number(2) ;

 CONV_LET  Varchar2(1) ;

 CONV_STR  Varchar2(30) ;

 incr number;

Begin

 LEN:=To_number(Length(Var1));

   incr:=20;

 For i in 1..Len Loop

   conv_let:=chr(Ascii(Substr(upper(Var1), i,1)) + (incr+i+len));

   conv_str := conv_str||conv_let;

 End Loop ;

 Return Conv_str;

END;