CREATE INDEX PIPURINV_COMPSUPINVNO_IN ON oa_jk_purinv.pi_pur_invoice_hdr ( comp_code,sup_code,purinv_no)
/
ALTER TABLE oa_jk_purinv.pi_pur_invoice_hdr
ADD CONSTRAINT PIPURINV_COMPSUPINVNO_UK UNIQUE ( comp_code,sup_code,purinv_no) ENABLE NOVALIDATE
Monday, 7 February 2022
Wednesday, 27 October 2021
one time dba scheduler job creation
PROCEDURE proc_oo_mst_xml_export_cntrl(p_rowid varchar2)
as
v_cnt number;
begin
select count(1) into v_cnt from user_scheduler_running_jobs;
If nvl(v_cnt,0) < 50 then
del_xml_oo_mst(p_rowid);
dbms_scheduler.create_job
(
job_name => 'OTJ_REQ_'||to_char(SYSTIMESTAMP,'yyyyMMddhh24missFF'),
job_type => 'PLSQL_BLOCK',
job_action => 'begin proc_oo_mst_xml_export('''||p_rowid||'''); end;',
start_date => sysdate,
enabled => TRUE,
auto_drop => TRUE,
comments => 'one-time job');
commit;
End if;
end;
Thursday, 21 October 2021
num2word
PACKAGE BODY pkg_number2word is
Function Num2Word_million(pCompCode varchar2, pAmount Number) return Varchar2 is
vWord Varchar2(200) ;
vAmount Number(15,2) ;
vBillionHundred Number(15) Default 0 ;
vBillion Number(15) Default 0 ;
vMillionHundred Number(15) Default 0 ;
vMillion Number(10) Default 0 ;
vHundredThousand Number(12) Default 0 ;
vThousand Number(10) Default 0 ;
vHundred Number(10) Default 0 ;
vOnes Number(2) Default 0 ;
vDecimal Number(2) Default 0 ;
vCurrCode Cm004_comp_mst.curr_code%type ;
vCurrSubUnit Cm001_currency_mst.subunit%type ;
vCurrMainUnit Cm001_currency_mst.mainunit%type ;
BEGIN
vAmount := Abs(pAmount) ;
Select Floor(vAmount/100000000000) Into vBillionHundred From Dual ;
If vBillionHundred >= 1 then
vAmount := vAmount-(vBillionHundred*100000000000) ;
End if;
Select Floor(vAmount/1000000000) Into vBillion From Dual ;
If vBillion >= 1 then
vAmount := vAmount-(vBillion*1000000000) ;
End if;
Select Floor(vAmount/100000000) Into vMillionHundred From Dual ;
If vMillionHundred >= 1 then
vAmount := vAmount-(vMillionHundred*100000000) ;
End if;
Select Floor(vAmount/1000000) Into vMillion From Dual ;
If vMillion >= 1 then
vAmount := vAmount-(vMillion*1000000) ;
End if;
Select Floor(vAmount/100000) Into vHundredThousand From Dual ;
If vHundredThousand >= 1 then
vAmount := vAmount-(vHundredThousand*100000) ;
End if;
Select Floor(vAmount/1000) Into vThousand From Dual ;
If vThousand >= 1 then
vAmount := vAmount-(vThousand*1000) ;
End if;
Select Floor(vAmount/100) Into vHundred From Dual ;
If vHundred >= 1 then
vAmount := vAmount-(vHundred*100) ;
End if;
Select (pAmount - trunc(pAmount,0)) * 100 Into vDecimal From Dual ;
vOnes := vAmount - (vDecimal/100) ;
vWord := '' ;
If vBillionHundred >= 1 then
vWord := vWord || Interprt(vBillionHundred)||' HUNDRED ' ;
If vBillion = 0 then
vWord := vWord || Interprt(vBillion)||' BILLION ' ;
End if ;
End if ;
If vBillion >= 1 then
vWord := vWord || Interprt(vBillion)||' BILLION ' ;
End if ;
If vMillionHundred >= 1 then
vWord := vWord || Interprt(vMillionHundred)||' HUNDRED ' ;
-----if million is 0 then appends the million
If vMillion = 0 then
vWord := vWord || Interprt(vMillion)||' MILLION ' ;
End if ;
End if ;
If vMillion >= 1 then
vWord := vWord || Interprt(vMillion)||' MILLION ' ;
End if ;
---------prints the hundred thousand
If vHundredThousand >= 1 then
vWord := vWord || Interprt(vHundredThousand)||' HUNDRED ' ;
-----if thousand is 0 then appends the thousand
If vThousand = 0 then
vWord := vWord || Interprt(vThousand)||' THOUSAND ' ;
End if ;
---------
End if ;
If vThousand >= 1 then
vWord := vWord || Interprt(vThousand)||' THOUSAND ' ;
End if ;
If vHundred >= 1 then
vWord := vWord || Interprt(vHundred)||' HUNDRED ' ;
End if ;
Begin
SELECT a.curr_code INTO vCurrCode
FROM Cm004_comp_mst a
WHERE a.comp_code = pCompCode ;
Exception
When Others Then
vCurrCode := null;
End;
If vCurrCode is not null Then
Begin
SELECT a.subunit, a.mainunit INTO vCurrSubUnit, vCurrMainUnit
FROM Cm001_currency_mst a
WHERE a.curr_code = vCurrCode ;
Exception
When Others Then
vCurrMainUnit := null ;
vCurrSubUnit := null ;
End ;
End If;
If pCompCode <> '004' Then
vWord := vWord || Interprt(vOnes)||' '||Nvl(vCurrMainUnit, 'RUPEES') ;
Else
vWord := vWord || Interprt(vOnes)||' '||Nvl(vCurrMainUnit, 'TAKA') ;
End If ;
--vWord := vWord || Interprt(vOnes)||' '||Nvl(vCurrMainUnit, 'RUPEES') ;
If vDecimal > 0 then
If vWord is not null then
vWord := vWord ||' AND '|| Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS') || ' ONLY' ;
Else
vWord := Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS')||' ONLY' ;
End if;
Else
If vWord is not null Then
vWord := vWord || ' ONLY' ;
End If;
End if ;
Return(vWord);
Exception
When Value_Error then
return null ;
End;
Function Num2Word_crore (pCompCode varchar2, pAmount Number) return Varchar2 IS
vWord Varchar2(100) ;
vAmount Number(13,2) ;
vCrore Number(10) Default 0 ;
vLakhs Number(10) Default 0 ;
vThousand Number(10) Default 0 ;
vHundred Number(10) Default 0 ;
vOnes Number(2) Default 0 ;
vDecimal Number(2) Default 0 ;
vCurrCode Cm004_comp_mst.curr_code%type ;
vCurrSubUnit Cm001_currency_mst.subunit%type ;
vCurrMainUnit Cm001_currency_mst.mainunit%type ;
BEGIN
vAmount := Abs(pAmount) ;
Select Floor(vAmount/10000000) Into vCrore From Dual ;
If vCrore >= 1 then
vAmount := vAmount-(vCrore*10000000) ;
End if;
Select Floor(vAmount/100000) Into vLakhs From Dual ;
If vLakhs >= 1 then
vAmount := vAmount-(vLakhs*100000) ;
End if;
Select Floor(vAmount/1000) Into vThousand From Dual ;
If vThousand >= 1 then
vAmount := vAmount-(vThousand*1000) ;
End if;
Select Floor(vAmount/100) Into vHundred From Dual ;
If vHundred >= 1 then
vAmount := vAmount-(vHundred*100) ;
End if;
Select (pAmount - trunc(pAmount,0)) * 100 Into vDecimal From Dual ;
vOnes := vAmount - (vDecimal/100) ;
vWord := '' ;
If vCrore >= 1 then
vWord := vWord || Interprt(vCrore)||' CRORES ' ;
End if ;
If vLakhs >= 1 then
vWord := vWord || Interprt(vLakhs)||' LAKHS ' ;
End if ;
If vThousand >= 1 then
vWord := vWord || Interprt(vThousand)||' THOUSAND ' ;
End if ;
If vHundred >= 1 then
vWord := vWord || Interprt(vHundred)||' HUNDRED ' ;
End if ;
Begin
SELECT a.curr_code INTO vCurrCode
FROM Cm004_comp_mst a
WHERE a.comp_code = pCompCode ;
Exception
When Others Then
vCurrCode := null;
End;
If vCurrCode is not null Then
Begin
SELECT a.subunit, a.mainunit INTO vCurrSubUnit, vCurrMainUnit
FROM Cm001_currency_mst a
WHERE a.curr_code = vCurrCode ;
Exception
When Others Then
vCurrMainUnit := null ;
vCurrSubUnit := null ;
End ;
End If;
If pCompCode <> '004' Then
vWord := vWord || Interprt(vOnes)||' '||Nvl(vCurrMainUnit, 'RUPEES') ;
Else
vWord := vWord || Interprt(vOnes)||' '||Nvl(vCurrMainUnit, 'TAKA') ;
End If ;
If vDecimal > 0 then
vWord := vWord || ' And ' || Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'Paise') || ' ONLY' ;
Else
If vWord is not null Then
vWord := vWord || ' ONLY' ;
End If;
End if ;
Return(vWord);
Exception
When Value_Error then
return null ;
END;
Function Interprt(pAmount Number) return Varchar2 is
vText Varchar2(1000);
BEGIN
vText := '' ;
If pAmount > 20 then
vText := NumberText(trunc(pAmount,-1)) ;
vText := vText || ' ' || NumberText(pAmount - trunc(pAmount,-1)) ;
Else
vText := NumberText(pAmount) ;
End if ;
Return vText ;
END;
Function Numbertext(pAmount Number) return Varchar2 IS
vText Varchar2(100) ;
BEGIN
Select Decode(pAmount, 1, 'ONE',
2, 'TWO',
3, 'THREE',
4, 'FOUR',
5, 'FIVE',
6, 'SIX',
7, 'SEVEN',
8, 'EIGHT',
9, 'NINE',
10,'TEN',
11,'ELEVEN',
12,'TWELVE',
13,'THIRTEEN',
14,'FOURTEEN',
15,'FIFTEEN',
16,'SIXTEEN',
17,'SEVENTEEN',
18,'EIGHTEEN',
19,'NINETEEN',
20,'TWENTY',
30,'THIRTY',
40,'FORTY',
50,'FIFTY',
60,'SIXTY',
70,'SEVENTY',
80,'EIGHTY',
90,'NINETY',100,'HUNDRED' ) Into vText From Dual ;
Return vText ;
END;
End;
Tuesday, 19 October 2021
session monitor
select * from locked_objects_info_vu where (owner like 'OA_CH_%' OR owner like 'OA_VIS_%')
--and blocking_session is not null
order by object_name, sid_serial#
/
select * from blocked_objects_hist
where trunc(crtd_dt)=trunc(sysdate)
/
select to_char(logon_time, 'DD-MON-YYYY HH24:MI:SS') from v$session where sid||','||serial# = '1905,30488'
/
alter system kill session '1905,30488'
/
/
SELECT * FROM V$SQL
WHERE SQL_ID = '6t4ds9ggj005u'
/
SELECT TRUNC(CREATION_DATE), COUNT(*) FROM PS_TXN GROUP BY TRUNC(CREATION_DATE)
/
sELECT * FROM PS_TXN
-- check resources
/
select * from v$resource_limit
/
select to_char(crtd_dt,'yyyymmdd hh24:mi:ss') dtime, current_utilization, max_utilization, limit_value
from v$resource_limit_test
where resource_name ='processes'
and crtd_dt>trunc(sysdate)
order by current_utilization desc,crtd_dt desc
/
-- Check TOP_SESSIONS by CPU Usage
Select * from TOP_SESSIONS
WHERE username like 'OA_%'
and status ='ACTIVE'
--and sql_id is not null
/
-- Check for BLOCKING_SESSION
select * from cpu_usage_vu
/
select * from locked_objects_info_vu
--where BLOCKING_SESSION is not null
order by SECONDS_IN_WAIT desc
/
select * from v$session
where status='ACTIVE'
/
-- KILL SESSION
--alter system kill session ' ' immediate
--/
-- Check TOP_SQLS BIG per execution time
SELECT * FROM TOP_SQLS
WHERE MODULE like 'JDBC%' --w3wp
AND per_exec > 3 -- >2SECS
--and LAST_LOAD_TIME>trunc(sysdate)
ORDER BY per_exec desc --executions
/
-- Check TOP_SQLS by execution time
SELECT * FROM TOP_SQLS
WHERE MODULE like 'JDBC%'
AND per_exec > 0.5 -- >2SECS
--and sql_text like '%FIRST_ROWS%'
--and executions>9
ORDER BY executions desc --
/
select * from dba_constraints
where constraint_name ='SYS_C0025355'
/
select * from DBA_CONS_COLUMNS
where constraint_name ='SYS_C0025355'
/
SELECT sql_fulltext FROM v$sqlarea
WHERE sql_id ='g6px76dmjv1jy'
/
select * from dba_hist_sqltext
/
-- accessed between a given time
select function_desc,Count(*)
from FUNCTION_HIST f, log_hist l
where f.log_id=l.log_id
and l.accessed_link like '%7011'
--and l.accessed_link like '%7013'
--and to_date(Substr(start_time,1,11),'dd-Mon-yyyy')=trunc(sysdate)
and to_date(start_time,'dd-Mon-yyyy hh24:mi;ss')> sysdate-5/1440 -- 5 min
--and to_date(start_time,'dd-Mon-yyyy hh24:mi;ss') between to_date('20-jul-2020 18:30:00','dd-mon-yyyy hh24:mi:ss') and to_date('20-jul-2020 19:20:00','dd-mon-yyyy hh24:mi:ss')
group by function_id,function_desc
order by 2 desc
/
select to_char(sysdate-5/1440,'dd-mon-yyyy hh24:mi:ss') dt from dual
/
-- total active users connected on each port
select accessed_link,count(*)
from (
select distinct user_id,accessed_link from log_hist
where to_date(log_in_time,'dd-Mon-yyyy hh24:mi:ss') >= trunc(sysdate)
and user_id>0
and log_out_time is null -- enable for today active users
)
group by accessed_link
order by 2 desc
/
-- pga
select name, value/1024/1024 in_mb from v$pgastat
where name in (
'aggregate PGA auto target',
'maximum PGA allocated',
'total PGA allocated',
'total PGA inuse',
'total freeable PGA memory'
)
/
-- Temporary tablespace free
SELECT TABLESPACE_NAME, TABLESPACE_SIZE/1024/1024/1024 "TBS Size(GB)" ,
ALLOCATED_SPACE/1024/1024/1024"Used Size(GB)",FREE_SPACE/1024/1024/1024" Free Size(GB)"
FROM dba_temp_free_space
/
select tablespace_name , (free_blocks*8)/1024/1024 FreeSpaceInGB,
(used_blocks*8)/1024/1024 UsedSpaceInGB,
(total_blocks*8)/1024/1024 TotalSpaceInGB
from v$sort_segment where tablespace_name like '%TEMP%'
/
-- undo tablespace free -- takes around 29/50 secs
select a.tablespace_name, SIZEGB, USAGEGB, (SIZEGB - USAGEGB) FREEGB
from (select sum(bytes) / 1024 / 1024 /1024 SIZEGB, b.tablespace_name
from dba_data_files a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name
and b.contents = 'UNDO'
group by b.tablespace_name) a,
(select c.tablespace_name, sum(bytes) / 1024 / 1024 /1024 USAGEGB
from DBA_UNDO_EXTENTS c
where status <> 'EXPIRED'
group by c.tablespace_name) b
where a.tablespace_name = b.tablespace_name
/
-- total memory sizes
SELECT component, current_size, min_size, max_size
FROM v$memory_dynamic_components
WHERE current_size != 0
/
-- Active sessions PGA used
select s.inst_id, s.sid,s.username,s.status, s.logon_time, s.program, PGA_USED_MEM/1024/1024 PGA_USED_MEM, PGA_ALLOC_MEM/1024/1024 PGA_ALLOC_MEM, s.sql_id
from gv$session s
, gv$process p
Where s.paddr = p.addr
and s.inst_id = p.inst_id
and s.status ='ACTIVE'
and PGA_USED_MEM/1024/1024 > 20 -- pga_used memory over 20mb
order by PGA_USED_MEM desc
/
-- session wise pga used
select total_size/1024/1024 PGA_GB,awr_flush_emergency_count from v$ash_info;
/
-- uga & Pga Memmory
select name, sum(value/1024/1024) "Value - MB"
from v$statname n,
v$session s,
v$sesstat t
where s.sid=t.sid
and n.statistic# = t.statistic#
and s.type = 'USER'
and s.username is not NULL
and n.name in ('session pga memory', 'session pga memory max',
'session uga memory', 'session uga memory max')
group by name
/
/
select * from locked_objects_info_vu
/
-- pga advisor
select pga_target_for_estimate, pga_target_factor, estd_time
from v$pga_target_advice
/
-- SID wise PGA used
SELECT SID,SUM(VAL) VALINMB FROM (
select sid,name,value/1024/1024 VAL
from v$statname n,v$sesstat s
where n.STATISTIC# = s.STATISTIC# and
name like 'session%memory%max')
GROUP BY SID
order by 2 desc
/
Monday, 18 October 2021
Index Hint in oracle
PROCEDURE otb_setup_mat_sscatg(p_comp_code VARCHAR2,p_main_code varchar2,p_cat_code varchar2) -- u
is
cursor disp_period_cur
is
select substr(period_finyr,1,2) period_ctrlno,substr(period_finyr,3,4) finyr
from (select distinct decode(length(period_ctrlno),1,0||period_ctrlno,period_ctrlno)||finctrl_no period_finyr
from budg_finperiod_hdr
where comp_code=p_comp_code
and main_code=p_main_code
and from_date>add_months(last_day(trunc(sysdate,'MM')-5),-1)
order by substr(decode(length(period_ctrlno),1,0||period_ctrlno,period_ctrlno)||finctrl_no,3,4) ,
substr(decode(length(period_ctrlno),1,0||period_ctrlno,period_ctrlno)||finctrl_no,1,2)asc)
where rownum<=6
and rownum = 1;
p_fn_yr varchar2(5):='X';
v_fnyr_code varchar2(5):='X';
v_pst_dt date;
v_ped_dt date;
v_fnyr_stdt date;
v_cur_period number;
v_ob_stkval1 number;
v_ob_stkval2 number;
v_marg_perc number;
V_TAX_PERC number; -- naren
v_onorder_sp number;
v_val number;
v_close_stkwillbe number;
v_stktur number;
v_close_stkshdbe number;
v_costval number;
v_otbval_sp number;
v_otbval_cp number;
v_salval number;
v_period number;
v_fnyr varchar2(5);
v_clsd_willbe number;
v_val2 number;
v_val3 number;
v_est_sale number;
v_curfinyr varchar2(6);
v_curperiod number;
v_pend_po_cp number;
v_mkt_pending_cp number;
v_onorder_po_sp number;
v_onorder_mkt_sp number;
v_pend_poqty number;
v_mkt_pending_qty number;
v_mat_name varchar2(200) ;
v_prod_status varchar2(100);
v_prod_status_desc varchar2(100);
v_crtd_dt date ;
v_cntnum number;
v_sup_code varchar2(20);
v_sup_name varchar2(100);
v_f_on_b_order number;
v_min_order_qty number;
v_lst1mnth_sale number;
v_lst3mnth_saleval number;
v_lst3mnth_sale number;
v_lst1mnth_saleval number;
v_lst6mnth_sale number;
v_lst6mnth_saleval number;
v_last_recd date;
v_last_rcvd_qty number;
v_first_rcvd_dt date ;
v_first_rcvd_qty number;
v_mnth1 number := 0;
v_mnth2 number := 0;
v_mnth3 number := 0;
v_mnth4 number := 0;
v_mnth5 number := 0;
v_mnth6 number := 0;
v_mnth1_val number := 0;
v_mnth2_val number := 0;
v_mnth3_val number := 0;
v_mnth4_val number := 0;
v_mnth5_val number := 0;
v_mnth6_val number := 0;
v_sgststk_qty number := 0 ;
v_ema_qty number;
v_finper number;
v_ppst_dt date;
v_pped_dt date;
v_prdno number:= 0;
v_new_margin number;
v_rsp number;
begin
delete from merch_otb_mat_sscateg
where cat_code = p_cat_code;
commit;
for i in disp_period_cur
loop
if i.finyr<>p_fn_yr then
---get the fin year start date
BEGIN
select from_date,fn_year into v_fnyr_stdt,v_fnyr_code
from budg_finyr_mst
where trunc(sysdate) between from_date and to_date ;
exception
when no_data_found then
dbms_output.put_line('Fin Year Start Date Is Not Exists');
when TOO_MANY_ROWS then
dbms_output.put_line('Too Many Rows While Validating Fin Year Start Date');
when others then
dbms_output.put_line(sqlerrm||3);
end;
end if;
p_fn_yr:=i.finyr;
----get the period start date and end date
begin
select distinct from_date,to_date,finctrl_no||period_ctrlno,periodnum
into v_pst_dt,v_ped_dt,v_finper, v_prdno
from budg_finperiod_hdr_vu
where comp_code=p_comp_code
and main_code=p_main_code
and finctrl_no=p_fn_yr
and period_ctrlno=i.period_ctrlno;
exception
when no_data_found then
null;
when TOO_MANY_ROWS then
dbms_output.put_line('Too Many Rows While Validating From And To Date');
when others then
dbms_output.put_line(sqlerrm||2);
end;
---ADD NAREN
insert into merch_otb_mat_saleval(comp_code,
main_code ,
mat_type,
matcode,
cat_code,
SGRPCODE,
SUB_SGRPCODE,
finyr_code,
period,
stock ,
salval,
costval,
LASTYR_SP,
stk_qty,
intrnsit_qty,
stk_sp,
intrnsit_sp
)
(select comp_code,main_code,mat_type,matcode,cat_Code,SGRPCODE,SUB_SGRPCODE,p_fn_yr,i.period_ctrlno
, qty,sp,cp,lastyr_sp --,est_sp
,stk_qty,intrnsit_qty,stk_qty_sp,intrnsit_qty_sp
from
(select comp_code,main_code,mat_type,matcode,cat_Code,SGRPCODE,SUB_SGRPCODE,round(sum(qty)) qty
,round(sum(stk_qty)) stk_qty,round(sum(intrnsit_qty)) intrnsit_qty,round(sum(stk_qty_sp)) stk_qty_sp
,round(sum(intrnsit_qty_sp)) intrnsit_qty_sp,round(sum(sp)) sp,round(sum(cp)) cp,round(sum(lastyr_sp)) lastyr_sp --,round(sum(est_sp)) est_sp
from
(select comp_code,main_code,mat_type,matcode,cat_Code,SGRPCODE,SUB_SGRPCODE,sum(qty) qty
,sum(stk_qty) stk_qty,sum(intrnsit_qty) intrnsit_qty,sum(stk_qty*price) stk_qty_sp
,sum(intrnsit_qty*price) intrnsit_qty_sp,
sum(qty*price) sp,sum(qty*cost_price) cp,0 lastyr_sp --,0 est_sp
from
(
select /*+ index (c uk_matcode)*/
a.comp_Code,a.main_code,a.mat_type, a.matcode,a.qty,a.stk_qty,a.intrnsit_qty,b.price,c.cost_price,c.cat_code,c.SGRPCODE,c.SUB_SGRPCODE
from
(select comp_Code,main_code,mat_type, matcode, sum(qty) qty,sum(stk_qty) stk_qty,sum(intrnsit_qty) intrnsit_qty
from (
select comp_Code,main_code,mat_type, matcode, qty qty,qty stk_qty,0 intrnsit_qty
from
(SELECT COMP_CODE,MAIN_CODE, MAT_TYPE,MATCODE,INC_QTY-DEC_QTY QTY
FROM INV_TRN_DAY_SUM_VU
where COMP_CODE =p_comp_code AND
MAIN_CODE = p_main_code AND
TRN_DATE BETWEEN v_fnyr_stdt AND v_pst_dt-1
UNION ALL
select /*+ index (a Matdtl_compmainserial)*/
COMP_CODE,MAIN_CODE, MAT_TYPE,MATCODE,qty qty
from material_detail a
where
COMP_CODE =p_comp_code AND
MAIN_CODE =p_main_code AND
SERIAL= v_fnyr_code
)
union ALL
select comp_Code,main_code,mat_type, matcode, stk_qty qty,0 stk_qty,stk_qty intrnsit_qty
from (
select A.comp_code, A.main_code,A.mat_type,A.matcode,
nvl(issue_qty,0) -nvl(accpt_qty,0) stk_qty
from INV_STKTRFOUT_HDDTL_VU A,
(SELECT comp_code, main_code, unit_code, sloc_code, matcode, mat_type, control_no,
voucher_code,from_unit, from_sloc, stktrf_no, stktrf_date, ref_cntrlno, DELVNOTE_QTY, other_amt,
remarks, ACCPT_QTY, recv_qty, AUTH_STAT
FROM INV_STKTRFIN_HDDTL_VU
WHERE stktrf_date <= v_pst_dt-1) b
where A.comp_code=B.comp_code(+) and
A.main_code=B.main_code(+) and
A.for_unit=B.unit_code(+) and
A.for_sloc=B.sloc_code(+) and
A.matcode=B.matcode(+) and
A.mat_type=B.mat_type(+) and
A.control_no=B.ref_cntrlno(+) and
a.COMP_CODE=p_comp_code AND
a.MAIN_CODE=p_main_code AND
a.issue_date < = v_pst_dt -1 AND
NVL(B.AUTH_STAT,'P')='P'
)
)
GROUP BY COMP_CODE,MAIN_CODE, MAT_TYPE,MATCODE )a,
(select /*+ index (b prod_price_dfn_cmmpv)*/
b.comp_code,b.main_code,b.mat_type,b.matcode,max(b.prod_price) price
from inv_prod_price_dfn b
where b.comp_code = p_comp_code
and b.main_code = p_main_code
and b.mat_type ='R'
and b.price_type = 'CASH'
and b.VLD_FROM_DT = (select /*+ index (a fk_prod_price_dfn_fkidx)*/
trunc(max(a.VLD_FROM_DT))
from inv_unit_prod_price_dtls a
where a.comp_code = b.comp_code
and a.main_code = b.main_code
and a.matcode = b.matcode
and a.mat_type = b.mat_type
and a.price_type = b.price_type
and v_pst_dt-1 between a.VLD_FROM_DT and NVL(a.VLD_to_DT,v_pst_dt-1) )
group by b.comp_code,b.main_code,b.mat_type,b.matcode
)b,material_master c
where a.comp_code=b.comp_code(+)
and a.main_code=b.main_code(+)
and a.MAT_TYPE=b.mat_type(+)
and a.MATCODE=b.matcode(+)
and a.comp_code=c.comp_code
and a.main_code=c.main_code
and a.MAT_TYPE=c.mat_type
and a.MATCODE=c.matcode
and a.comp_code= p_comp_code
and a.main_code= p_main_code
)
where comp_code= p_comp_code
and main_code= p_main_code
group by comp_code,main_code,mat_type,matcode,cat_code,SGRPCODE,SUB_SGRPCODE
union all
select comp_code,main_code,mat_type,matcode,cat_code,sgrpcode,sub_sgrpcode,0 qty,0 stk_qty,0 intrnsit_qty,0 stk_qty_sp,0 intrnsit_qty_sp
,0 sp,0 cp, lastyr_sp --,0 est_sp
from
(select comp_code,main_code,mat_type,matcode,cat_code,sgrpcode,sub_sgrpcode,round(sum(sale_value)) lastyr_sp
from sales_trans
where comp_code= p_comp_code
and main_code= p_main_code
and cntrl_no= p_fn_yr - 1
and period_ctrlno= i.period_ctrlno --11
group by comp_code,main_code,mat_type,matcode,cat_Code,sgrpcode,sub_sgrpcode)
)
where cat_code = p_cat_code --'01' -- in ('01') --,'03')
and sgrpcode = '10'
and sub_sgrpcode = '100'
group by comp_code,main_code,mat_type,matcode,cat_Code,sgrpcode,sub_sgrpcode
)
);
insert into merch_otb_mat_ordcpval (comp_code,
main_code,
mat_type,
matcode,
cat_code,
SGRPCODE,
SUB_SGRPCODE,
finyr_code,
period,
stock,
costval,
pend_poqty,
mkt_pending_qty,
pend_po_cp,
mkt_pending_cp
)
(select comp_code,main_code,mat_type,MAT_CODE,cat_code,sgrpcode,sub_sgrpcode --,sup_code ,cur_code,ex_rtae
,p_fn_yr,i.period_ctrlno,sum(qty),sum(cp),sum(pend_poqty),sum(mkt_pending_qty),sum(pend_po_cp),sum(mkt_pending_cp)
from
(select m.comp_code,m.main_code,m.mat_type,m.MAT_CODE,m.cat_code,m.sgrpcode,m.sub_sgrpcode,m.sup_code,g.cur_code,nvl(g.ex_rate,1) ex_rate,p.lc_factor
,round(sum(pend_qty)) qty,round(sum(nvl(pend_qty,0)*(nvl(unit_rate,0)/nvl(g.ex_rate,1)) * nvl(p.lc_factor,1))) cp
,round(sum(pend_poqty)) pend_poqty ,round(sum(mkt_pending_qty)) mkt_pending_qty
--,round(sum(nvl(pend_poqty,0)*nvl(cost_price,0))) pend_po_cp
,round(sum(nvl(pend_poqty,0)*(nvl(unit_rate,0)/nvl(g.ex_rate,1)) * nvl(p.lc_factor,1))) pend_po_cp
,round(sum(nvl(mkt_pending_qty,0)*nvl(cost_price,0))) mkt_pending_cp
from
(
select a.COMP_CODE,a.MAIN_CODE,a.mat_type,a.MAT_CODE,a.pend_poqty pend_qty,a.pend_poqty,0 mkt_pending_qty
,b.cost_price,b.cat_code,b.sgrpcode,b.sub_sgrpcode,unit_rate,b.sup_code
from
(
select COMP_CODE,MAIN_CODE,UNIT_CODE,PO_CONTROL_NO,ind_control_no,mat_type,MAT_CODE,
nvl(sum(PO_QTY),0) po_qty, nvl(sum(can_qty),0) can_qty,nvl(sum(rcd_qty),0) rcd_qty,
nvl(sum(PO_QTY),0)-(nvl(sum(can_qty),0)+nvl(sum(rcd_qty),0)) pend_poqty,sum(unit_rate) unit_rate
from (
select a.COMP_CODE,a.MAIN_CODE,a.UNIT_CODE,a.PO_CONTROL_NO,a.ind_control_no,a.mat_type,a.MAT_CODE,
nvl(a.PO_QTY,0)+nvl(a.EXCESS_GRNQTY,0) po_qty, a.can_qty,a.po_obrcpt_qty rcd_qty,a.unit_rate
from po_detail a, po_header b
where a.comp_code=b.comp_code
and a.main_code=b.main_code
and a.unit_code=b.unit_code
and a.po_control_no=b.po_control_no
and b.po_stat in ('L') --,'C')
AND A.POAMD_NO=B.POAMD_NO
and nvl(force_close,'N')='N'
and b.po_date BETWEEN v_fnyr_stdt AND v_pst_dt-1
and nvl(force_close,'N')='N'
and case when nvl(b.EST_ARRIVAL_DT,trunc(sysdate))<=trunc(sysdate) then
trunc(sysdate)
else
b.EST_ARRIVAL_DT
end
between v_pst_dt and v_ped_dt
union all
SELECT a.COMP_CODE,a.MAIN_CODE,NVL(A.PO_UNIT,a.UNIT_CODE),to_number(a.PO_CONTROL_NO),a.ind_control_no,a.mat_type,a.MATCODE,
0 po_qty, 0 can_qty,SUM(a.ACCPT_QTY) RCD_QTY,0 unit_rate
FROM INV_GRN_DTL_v a
WHERE a.PO_CONTROL_NO<>999999 ---THIS GIVES THE PO,IND,CONTROL_NO WISE QTY OF MAT WHICH ARE NOT CNCELLED I.E APPROVED OR PENDING----
and a.grn_status<>'C'
and nvl(a.auth_status,'P')in ('P','A')
and a.comp_code=p_comp_code
and a.main_code=p_main_code
-- and NVL(A.PO_UNIT,a.UNIT_CODE) not in ('500','503')
and case when nvl(a.grn_date,trunc(sysdate))<=trunc(sysdate) then
trunc(sysdate)
else
a.grn_date
end between v_pst_dt and v_ped_dt
--and a.grn_date between '27-feb-2017' and '31-mar-2017'
GROUP BY a.COMP_CODE,a.MAIN_CODE,a.UNIT_CODE,to_number(a.PO_CONTROL_NO),a.ind_control_no,a.MATCODE,mat_type,PO_UNIT,a.cur_code
union all
SELECT a.COMP_CODE,a.MAIN_CODE,NVL(A.GRN_UNIT,a.UNIT_CODE),to_number(b.PO_CONTROL_NO),b.ind_control_no,a.mat_type,a.item_CODE,
0 po_qty, 0 can_qty,-1*sum(qty) rej_qty,0 unit_rate
FROM gp_hdr_trn_vu a,inv_grn_dtl b
WHERE b.PO_CONTROL_NO<>999999 ---THIS GIVES THE PO,IND,CONTROL_NO WISE QTY OF MAT WHICH ARE NOT CNCELLED I.E APPROVED OR PENDING----
and a.comp_code=b.comp_code
and a.main_code=b.main_code
and a.grn_unit=b.unit_code
and a.grn_control_no=b.control_no
and a.item_code=b.matcode
-- and NVL(a.attrib_code1,0)=NVL(b.attrib_code1,0)
-- and NVL(a.attrib_code2,0)=NVL(b.attrib_code2,0)
and nvl(a.grn_yn,'Y')='Y'
and a.comp_code=p_comp_code
and a.main_code=p_main_code
-- and NVL(A.GRN_UNIT,a.UNIT_CODE) not in ('500','503')
and case when nvl(a.gp_dt,trunc(sysdate))<=trunc(sysdate) then
trunc(sysdate)
else
a.gp_dt
end between v_pst_dt and v_ped_dt
--and a.gp_dt between '27-feb-2017' and '31-mar-2017'
GROUP BY a.COMP_CODE,a.MAIN_CODE,A.GRN_UNIT,a.UNIT_CODE,b.PO_CONTROL_NO,b.ind_control_no,a.mat_type,a.item_CODE
)
GROUP BY COMP_CODE,MAIN_CODE,UNIT_CODE,PO_CONTROL_NO,ind_control_no,MAT_CODE,mat_type
)a,material_master b
where a.comp_code=b.comp_code
and a.main_code=b.main_code
and a.mat_code=b.matcode
and a.mat_type=b.mat_type
and nvl(a.pend_poqty,0)>0
and b.cat_code = p_cat_code -- in ('01') --,'03','75') ---cat_code where cat_code in ('03','01','75')
and sgrpcode = '10'
and sub_sgrpcode = '100'
) m , curr_exrate_latest_grn g , plnd_stkturn_dtl p
where m.comp_code=p_comp_code
and m.main_code=p_main_code
and m.sup_code = g.supcode(+)
and m.cat_code = p.cat_code
and m.sup_code = p.sup_code
group by m.comp_code,m.main_code,m.mat_type,m.MAT_CODE,m.cat_code,m.sgrpcode,m.sub_sgrpcode,m.sup_code,g.cur_code,g.ex_rate,p.lc_factor
)
group by comp_code,main_code,mat_type,MAT_CODE,cat_code,sgrpcode,sub_sgrpcode
);
v_ob_stkval1:=0;
v_ob_stkval2:=0;
for j in
(
select comp_code,main_code ,mat_type,matcode,cat_code,SGRPCODE,SUB_SGRPCODE,finyr_code,period,
sum(stk_qty) stk_qty
from
(select comp_code,main_code ,mat_type,matcode,cat_code,SGRPCODE,SUB_SGRPCODE,finyr_code,period,
stk_qty
--stock ,salval,costval,LASTYR_SP,stk_qty,intrnsit_qty,stk_sp,intrnsit_sp
from merch_otb_mat_saleval
where comp_code=p_comp_code
and main_code=p_main_code
and finyr_code=p_fn_yr
and period=i.period_ctrlno
and cat_code = p_cat_code
--order by cat_code,sgrpcode,sub_sgrpcode,period
union all
select comp_code,main_code,mat_type,matcode,cat_code,SGRPCODE,SUB_SGRPCODE,finyr_code,period,
0 stk_qty
--stock,costval,pend_poqty,mkt_pending_qty,pend_po_cp,mkt_pending_cp
from merch_otb_mat_ordcpval
where comp_code=p_comp_code
and main_code=p_main_code
and finyr_code=p_fn_yr
and period=i.period_ctrlno
and cat_code = p_cat_code
)
group by comp_code,main_code ,mat_type,matcode,cat_code,SGRPCODE,SUB_SGRPCODE,finyr_code,period
)
loop
begin
select plnd_mths_stk into v_stktur
from plnd_stkturn_mst
where comp_code=j.comp_code
and main_code=j.main_code
and cat_code=j.cat_code
and finyr_code<=j.finyr_code
and rownum=1
order by finyr_code desc ;
exception
when no_data_found then
v_stktur:=3;
when TOO_MANY_ROWS then
dbms_output.put_line('Too Many Rows While Validating Stock Turn Months');
when others then
dbms_output.put_line(sqlerrm||4);
end;
v_close_stkshdbe:=0;--round(nvl(j.est_sp,0)*v_stktur);
if i.period_ctrlno=1 then
v_period:=13;
v_fnyr:=p_fn_yr-1;
else
v_period:=i.period_ctrlno;
v_fnyr:=p_fn_yr; -------------
end if;
--
/*Begin
SELECT TAX_PERC INTO V_TAX_PERC
FROM MAIN_CATEGORY
where COMP_CODE = P_COMP_CODE
AND MAIN_CODE = P_MAIN_CODE
AND CAT_CODE= j.cat_code;
Exception when others then
V_TAX_PERC := 9;
End;
---
if nvl(j.salval,0)=0 then
v_salval:=1;
ELSE
v_salval:=j.salval - ( j.salval * (V_TAX_PERC / (100 + V_TAX_PERC ))) ;
end if;
v_marg_perc:=round(((v_salval-j.costval)/nvl(v_salval,1))*100);*/
begin
select costval,pend_poqty,mkt_pending_qty,pend_po_cp,mkt_pending_cp into v_costval,v_pend_poqty,v_mkt_pending_qty,v_pend_po_cp,v_mkt_pending_cp
-- stock need to add
from merch_otb_mat_ordcpval
where comp_code=j.comp_code
and main_code=j.main_code
and cat_code=j.cat_code
and matcode = j.matcode
and sgrpcode=j.sgrpcode
and sub_sgrpcode=j.sub_sgrpcode
and finyr_code=j.finyr_code
and period=j.period;
exception
when no_data_found then
v_costval:=0;
v_pend_poqty :=0;
v_mkt_pending_qty:=0;
v_pend_po_cp:=0;
v_mkt_pending_cp:=0;
when TOO_MANY_ROWS then
dbms_output.put_line('Too Many Rows While Validating Stock Turn Months');
when others then
dbms_output.put_line(sqlerrm||4);
end;
----siva 19062016--from
begin
select finctrl_no,period_ctrlno into v_curfinyr,v_curperiod
from budg_finperiod_hdr
where trunc(sysdate) between from_date and to_date;
exception
when no_data_found then
dbms_output.put_line('period not found');
when others then
dbms_output.put_line(sqlerrm||4);
end;
Begin
select margin into v_new_margin
from plnd_stkturn_mst
where comp_code=j.comp_code
and main_code=j.main_code
and cat_code= j.cat_code ; -- 'BC03';
Exception when no_data_found then
v_new_margin := 40 ;
when others then
dbms_output.put_line(sqlerrm||' Error Cat Margin ');
End;
--v_rsp := v_costval/(1-v_new_margin/100) ;
v_rsp := v_pend_po_cp/(1-v_new_margin/100) ;
v_rsp := v_rsp - v_mkt_pending_cp;
--v_onorder_sp := v_rsp;
v_pend_po_cp := v_rsp;
/*--dbms_output.put_line('j.matcode '|| j.matcode||' v_pend_po_cp '||v_pend_po_cp||' marg '||v_marg_perc);
v_costval:=v_costval-v_mkt_pending_cp;
v_onorder_sp:=round(v_costval+(v_costval*(v_marg_perc/100)));
v_onorder_po_sp:= round(v_pend_po_cp+(v_pend_po_cp*(v_marg_perc/100)));
-- dbms_output.put_line('v_onorder_po_sp '||v_onorder_po_sp);
v_onorder_mkt_sp:=round(v_mkt_pending_cp);
v_onorder_sp:=v_onorder_sp+v_onorder_mkt_sp;
v_val:=nvl(v_ob_stkval2,0)+nvl(v_onorder_sp,0);
v_close_stkwillbe:=round(v_val-p_est_markdown-0); --j.est_sp); -- rmeember this 1
v_otbval_sp:=nvl(v_close_stkshdbe,0)-nvl(v_close_stkwillbe,0);
v_otbval_cp:=round((v_otbval_sp*(100-v_marg_perc))/100);*/
Begin
select mat_name,a.STATUS_CODE,prod_status,a.crtd_dt,a.sup_code,b.sup_name,a.f_on_b_order,a.min_order_qty
into v_mat_name,v_prod_status,v_prod_status_desc,v_crtd_dt,v_sup_code,v_sup_name, v_f_on_b_order,v_min_order_qty
from material_master a, sup_mast b
where a.comp_code=b.comp_Code
and a.main_code = b.main_code
and a.sup_code = b.sup_code
and a.comp_code = p_comp_code
and a.main_code = p_main_code
and a.cat_code = j.cat_code
and a.matcode = j.matcode;
Exception when no_data_found then
v_mat_name := 'Mat Name';
v_prod_status := null ;
v_crtd_dt := null ;
when others then
dbms_output.put_line(sqlerrm||' Error While getting material name');
End;
begin
select distinct from_date,to_date into v_ppst_dt,v_pped_dt
from budg_finperiod_hdr
where comp_code=p_comp_code
and main_code=p_main_code
and finctrl_no||period_ctrlno=p_fn_yr||i.period_ctrlno-1 ;
exception
when no_data_found then
null;
when TOO_MANY_ROWS then
dbms_output.put_line('Too Many Rows While Validating From And To Date');
when others then
dbms_output.put_line(sqlerrm||2);
end;
/*-- getting last 1month sale values
Begin
select sum(nvl(sale_qty,0)) , sum(nvl(val,0)) into v_lst1mnth_sale,v_lst1mnth_saleval
from stk_Sales_vu_mv a, budg_finperiod_hdr_vu b
where a.comp_code = p_comp_code
and a.main_code = p_main_code
and cat_code = j.cat_code
and sgrpcode = j.SGRPCODE
and sub_sgrpcode = j.SUB_SGRPCODE
and matCode = j.matcode
and ord_date between b.from_date and b.to_date
and b.periodnum between (v_prdno - 2) and (v_prdno - 1) ;
--and ord_date between add_months(v_pst_dt-1,-1) and v_pst_dt-1;
Exception when others then
v_lst1mnth_sale := 0;
v_lst1mnth_saleval := 0;
dbms_output.put_line(sqlerrm ||' Error at 1 month sale value picking');
End;
-- getting last 3month sale values
Begin
select sum(nvl(sale_qty,0)) , sum(nvl(val,0)) into v_lst3mnth_sale,v_lst3mnth_saleval
from stk_Sales_vu_mv a, budg_finperiod_hdr_vu b
where a.comp_code = p_comp_code
and a.main_code = p_main_code
and cat_code = j.cat_code
and sgrpcode = j.SGRPCODE
and sub_sgrpcode = j.SUB_SGRPCODE
and matCode = j.matcode
and ord_date between b.from_date and b.to_date
and b.periodnum between (v_prdno - 3) and (v_prdno - 1) ;
--and ord_date between add_months(v_pst_dt-1,-3) and v_pst_dt-1;
Exception when others then
v_lst1mnth_sale := 0;
v_lst1mnth_saleval := 0;
dbms_output.put_line(sqlerrm ||' Error at 3 month sale value picking');
End;*/
-- getting last 6month sale values
Begin
select sum(nvl(sale_qty,0)) , sum(nvl(val,0)) ,
sum(case when b.periodnum = (v_prdno - 1) then nvl(sale_qty,0)
end) mnth1,
sum(case when b.periodnum = (v_prdno - 1) then nvl(val,0)
end) mnth1_val,
sum(case when b.periodnum = (v_prdno - 2) then nvl(sale_qty,0)
end) mnth2,
sum(case when b.periodnum = (v_prdno - 2) then nvl(val,0)
end) mnth2_val,
sum(case when b.periodnum = (v_prdno - 3) then nvl(sale_qty,0)
end) mnth3,
sum(case when b.periodnum = (v_prdno - 3) then nvl(val,0)
end) mnth3_val,
sum(case when b.periodnum = (v_prdno - 4) then nvl(sale_qty,0)
end) mnth4,
sum(case when b.periodnum = (v_prdno - 4) then nvl(val,0)
end) mnth4_val,
sum(case when b.periodnum = (v_prdno - 5) then nvl(sale_qty,0)
end) mnth5,
sum(case when b.periodnum = (v_prdno - 5) then nvl(val,0)
end) mnth5_val,
sum(case when b.periodnum = (v_prdno - 6) then nvl(sale_qty,0)
end) mnth6,
sum(case when b.periodnum = (v_prdno - 6) then nvl(val,0)
end) mnth6_val
into v_lst6mnth_sale,v_lst6mnth_saleval ,
v_mnth1,v_mnth1_val, v_mnth2,v_mnth2_val, v_mnth3,v_mnth3_val, v_mnth4,v_mnth4_val, v_mnth5,v_mnth5_val, v_mnth6,v_mnth6_val
from stk_Sales_vu_mv a, budg_finperiod_hdr_vu b
where a.comp_code = p_comp_code
and a.main_code = p_main_code
and cat_code = j.cat_code
and sgrpcode = j.SGRPCODE
and sub_sgrpcode = j.SUB_SGRPCODE
and matCode = j.matcode
and ord_date between b.from_date and b.to_date
and b.periodnum between (v_prdno - 6) and (v_prdno - 1) ;
--and ord_date between add_months(v_pst_dt-1,-6) and v_pst_dt-1;
Exception when others then
v_lst1mnth_sale := 0;
v_lst1mnth_saleval := 0;
dbms_output.put_line(sqlerrm ||' Error at 6 month sale value picking');
End;
v_lst1mnth_sale := nvl(v_mnth1,0) ;
v_lst1mnth_saleval := nvl(v_mnth1_val,0);
v_lst3mnth_sale := nvl(v_mnth1,0) + nvl(v_mnth2,0) + nvl(v_mnth3,0);
v_lst3mnth_saleval := nvl(v_mnth1_val,0) + nvl(v_mnth2_val,0) + nvl(v_mnth3_val,0);
Begin
select max(grn_date) , sum(a.recd_qty) into v_last_recd,v_last_rcvd_qty
from inv_grn_dtl_v a
where comp_code = p_comp_code and
NVL(grn_status,'P') ='A' and
NVL(accpt_qty,0)>0 and
grn_date = (select max(grn_date)
from inv_grn_dtl_v b
where b.matcode = a.matcode and
NVL(grn_status,'P') ='A' and
b.grn_date<= v_pst_dt -1 --'29-jan-2017'
)
and matcode =j.matcode
group by a.comp_code , a.main_code, a.mat_type, a.matcode ;
Exception when others then
null; --dbms_output.put_line(sqlerrm ||' last rcvd date'||j.matcode||' '||v_pst_dt );
End;
Begin
select min(grn_date) , sum(a.recd_qty) into v_first_rcvd_dt,v_first_rcvd_qty
from inv_grn_dtl_v a
where
NVL(grn_status,'P') ='A' and
NVL(accpt_qty,0)>0 and
grn_date = (select min(grn_date)
from inv_grn_dtl_v b
where b.matcode = a.matcode and
NVL(grn_status,'P') ='A' and
b.grn_date<= v_pst_dt -1 --'29-jan-2017'
)
and matcode = j.matcode
group by a.comp_code , a.main_code, a.mat_type, a.matcode ;
Exception when others then
null; --dbms_output.put_line(sqlerrm ||' first rcvd date'||j.matcode||' '||v_pst_dt );
End;
--v_ema_qty := otb_stock_emv(p_comp_code, j.cat_code, v_sup_code, nvl(v_mnth1,0) , nvl(v_mnth2,0) ,nvl(v_mnth3,0) , nvl(v_mnth4,0), nvl(v_mnth5,0) , nvl(v_mnth6,0) );
v_ema_qty := otb_stock_emv(p_comp_code, j.cat_code, v_sup_code, nvl(v_mnth6,0) , nvl(v_mnth5,0) ,
nvl(v_mnth4,0) , nvl(v_mnth3,0), nvl(v_mnth2,0) , nvl(v_mnth1,0) );
v_sgststk_qty := nvl(v_ema_qty,0) - ( j.stk_qty + v_pend_poqty); -- j.stk_qty
if v_prod_status = 'C' then -- continued
if nvl(v_sgststk_qty,0)>0 then -- suggested is more than 0
v_sgststk_qty := v_sgststk_qty;
else
v_sgststk_qty := null;
End if;
else
v_sgststk_qty := null;
End If;
insert into merch_otb_mat_sscateg(comp_code ,
main_code ,
mat_type ,
matcode ,
mat_name ,
prod_status ,
prod_stat_desc ,
sup_code ,
sup_name ,
cat_code ,
sgrpcode ,
sub_sgrpcode ,
crtd_dt ,
last_recd ,
last_rcvd_qty ,
first_rcvd_dt ,
first_rcvd_qty ,
stk_qty ,
on_ord_qty ,
cost_price_grn ,
on_ord_val ,
lst1mnth_sale ,
lst1mnth_saleval ,
lst3mnth_sale ,
lst3mnth_saleval ,
lst6mnth_sale ,
lst6mnth_saleval ,
sgst_stk_qty ,
tobe_ord_qty ,
finyr_code ,
period_ctrlno ,
fob ,
min_order_qty ,
mnth1_qty ,
mnth2_qty ,
mnth3_qty ,
mnth4_qty ,
mnth5_qty ,
mnth6_qty,
ema_qty,
crtd_by,
crtd_date
)
values
(p_comp_code,
p_main_code,
j.mat_type,
j.matcode,
v_mat_name,
v_prod_status,
v_prod_status_desc,
v_sup_code,
v_sup_name,
j.cat_code,
j.SGRPCODE,
j.SUB_SGRPCODE,
v_crtd_dt,
v_last_recd,
v_last_rcvd_qty,
v_first_rcvd_dt,
v_first_rcvd_qty,
j. stk_qty,
v_pend_poqty,
null,
--v_onorder_sp, --naren
v_pend_po_cp, --
v_lst1mnth_sale,
v_lst1mnth_saleval,
v_lst3mnth_sale ,
v_lst3mnth_saleval,
v_lst6mnth_sale ,
v_lst6mnth_saleval,
v_sgststk_qty,
null,
j.finyr_code,
j.period,
v_f_on_b_order,
v_min_order_qty,
v_mnth1,
v_mnth2,
v_mnth3,
v_mnth4,
v_mnth5,
v_mnth6,
v_ema_qty,
'SMCN',
sysdate);
end loop;
commit;
--end if;
end loop;
----------------
Commit;
end;
Monday, 23 August 2021
updating on same table trigger creation
CREATE OR REPLACE TRIGGER oa_th_fa.upd_all_comp_periods_finyr
AFTER
UPDATE
ON oa_th_fa.cm_finyr_periods
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
Declare
-- added by SMCN
v_comp_code varchar2(6);
v_curr_id number;
v_sup_id number;
v_ap_ar_type varchar2(2);
v_cnt number;
pragma AUTONOMOUS_TRANSACTION;
Begin
if updating and :new.active_yn <> :old.active_yn then
Begin
update cm_finyr_periods set active_yn = :new.active_yn
where (finyr_id,finyr_period_id) in (select finyr_id,finyr_period_id from cm_finyr_periods
where period_no = :old.period_no and finyr_code = :old.finyr_code
--and finyr_id <> 1410
and finyr_period_id <> :old.finyr_period_id);
Exception when others then
raise_application_error(-20010,'Error at closing all companies periods '||sqlerrm);
End;
End If;
--commit;
End;
Tuesday, 30 March 2021
create a trigger on table for id column with sequence
CREATE OR REPLACE TRIGGER AT_PUNCHING_ID_SEQ_TRG
BEFORE
INSERT
ON BF503_BRSHDR_TRN
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
Begin
SELECT seq_at_punc.NEXTVAL INTO :NEW.ID FROM DUAL;
End;
/
CREATE OR REPLACE TRIGGER xx_purinv.pi_po_hdr_email_trg
AFTER
UPDATE
ON xx_purinv.pi_po_hdr
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
Declare
p_errno varchar2(1000);
p_errmsg varchar2(1000);
Begin
If Nvl(:old.trans_approved, 'N') = 'N' And Nvl(:new.trans_approved, 'N') = 'Y' Then
Begin
xx_all.automails_pos_pkg.PUR_PODOC_MAILSENDMAIL_REP(:old.comp_code, 'narendrae@kk.com',:old.unit_code, 'PO',:old.finyr_code,null,null,'N','Test',:old.po_cntrl_no,:old.po_cntrl_no,'SATHGURU' , p_errno, p_errmsg);
Exception
When Others Then
raise_application_error('-20101','Error while triggereing Email '||p_errno||':p_errno p_errmsg :'||p_errmsg);
End;
End If;
End;
Subscribe to:
Posts (Atom)
-
Cursors And Bulk Collect CURSORS Cursor is a pointer to memory location which is called as context area which contains the informatio...
-
What is Collection? A Collection is an ordered group of elements of particular data types. It can be a collection of simple data type or...
-
https://download.cnet.com/Excel-to-Oracle/3000-2077_4-10537733.html
