alter table spl_sales_por_det add remarks varchar2(100);
alter table spl_sales_por_det add transit_day number(3);
alter table spl_sales_por_hdr add debit_note_amt number(14,3);
alter table spl_sales_por_det add debit_note_amt number(14,3);

alter table spl_sales_por_det add aprv_rate number(15,4);

insert into obj_itemchange (obj_name,form_no,field_name,mandatory)
values('proof_of_delivery','2','bil_date','Y');


insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTSITECNE','Empty site code','Site code can not be blank.Please enter site code','E',sysdate,'BASE','BASE');
insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTSITENEX','Invalid site code','Entered site code is not defined in site master','E',sysdate,'BASE','BASE');
insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTCUSTCNE','Empty customer code','Customer code can not be blank.Please enter customer code','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTNCONFT','Not confirmed','Selected transaction not confirmed','E',sysdate,'BASE','BASE'); 

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTBLBDNRP','Invalid Operation','Bill no., stockist can not be repeated for this year','E',sysdate,'BASE','BASE'); 



insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTCUSTNEX','Invalid customer code','Entered customer code is not defined in customer master','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTAPRRIDN','Invalid approved rate','Approved rate not available.Please check regulated rate in item master or var name in disparm.','E',sysdate,'BASE','BASE');



insert into pophelp(FIELD_NAME,MOD_NAME,SQL_STR,MSG_TITLE,WIDTH,HEIGHT,CHG_DATE,CHG_USER,CHG_TERM)
values(	'SITE_CODE','W_PROOF_OF_DELIVERY','SELECT SITE_CODE,DESCR FROM SITE','Site Code',0,0,sysdate,'BASE','BASE');

insert into pophelp(FIELD_NAME,MOD_NAME,SQL_STR,MSG_TITLE,WIDTH,HEIGHT,CHG_DATE,CHG_USER,CHG_TERM)
values(	'CUST_CODE','W_PROOF_OF_DELIVERY','SELECT CUST_CODE,CUST_NAME FROM CUSTOMER','Customer Code',0,0,sysdate,'BASE','BASE');


insert into obj_itemchange (obj_name,form_no,field_name,mandatory)
values('proof_of_delivery','1','site_code','Y');

insert into obj_itemchange (obj_name,form_no,field_name,mandatory)
values('proof_of_delivery','1','cust_code','Y');

delete from pophelp where mod_name='W_PROOF_OF_DELIVERY' and field_name='INVOICE_ID'; 

insert into pophelp(FIELD_NAME,MOD_NAME,SQL_STR,SQL_INPUT,MSG_TITLE,WIDTH,HEIGHT,CHG_DATE,CHG_USER,CHG_TERM)
values(	'INVOICE_ID','W_PROOF_OF_DELIVERY','SELECT INVOICE_ID,TRAN_DATE,FIN_ENTITY,EFF_DATE,DESP_ID FROM INVOICE WHERE SITE_CODE = ''?'' and cust_code = ''?''',':login_site,:cust_code','Invoice ID',0,0,sysdate,'BASE','BASE');

commit;

-- POD Function start -- 20/JUN/2014 

create or replace
Function fn_pod_chk ( as_saleorder in sorder.sale_order%type)
   RETURN number IS
 TYPE InvCurTyp  IS REF CURSOR;
 TYPE DtlCurTyp  IS REF CURSOR;
 TYPE ItemCurTyp  IS REF CURSOR;

  inv_cur    InvCurTyp;
   cnumber number ;
   inv_type varchar2(3);
   inv_type_list varchar2(600);
   pod_cnt number;
	 ls_invoiceid char(10);
	 ls_sql_stmt varchar2(3000);
	 ls_custCodeBill customer.cust_code%type;
	 ls_siteCode site.site_code%type;

   -- 17 Sep 2013
   hdr_sql_stmt varchar2(3000);
   dtl_sql_stmt varchar2(3000);
   podqty_sum number;
   inv_qty number;
   dtl_cur DtlCurTyp;
   item_cur ItemCurTyp;
   ls_podtranid char(10);
   itemcode char(10);
   lineno number;
   ls_itemSerSO	char(5);
   itemSerCnt number;
   last_day_of_month number;   
   
   -- 29-Nov-2013
   curr_date varchar2(2);
   curr_mon varchar2(2);
   curr_yr varchar2(2);
   invoice_mnth varchar2(2);
   begin_date varchar2(8);
   end_date varchar2(8);
   
   itemCodeL varchar2(10);
   lineTraceL number;
   lotNoL varchar2(15);
   lotSlL  varchar2(15);
   
BEGIN
	cnumber := 0;
	  
    begin
		select '''' || replace(var_value,',',' '',''') || ' '''  into inv_type_list from disparm where var_name = 'POD_INV_TYPE' and prd_code = '999999';
    
	exception when others then
		return -99;
	end;
  
	begin
		--select site_code, cust_code__bil into ls_siteCode, ls_custCodeBill  FROM sorder where sale_order = as_saleorder and order_type in(inv_type_list);
		--select site_code, cust_code__bil, item_ser into ls_siteCode, ls_custCodeBill, ls_itemSerSO  FROM sorder where sale_order = as_saleorder and order_type in(select '''' || replace(var_value,',',''',''') || '''' from disparm where var_name = 'POD_INV_TYPE' and prd_code = '999999');
    select site_code, cust_code__bil, item_ser into ls_siteCode, ls_custCodeBill, ls_itemSerSO  FROM sorder where sale_order = as_saleorder and order_type in('DM','IS');
    
	exception when NO_DATA_FOUND then
		return 0;
	end;
	
	

  --ls_sql_stmt := 'select invoice_id from invoice where cust_code__bil = ''' || ls_custCodeBill ||''' and site_code = ''' || ls_siteCode ||''' and inv_type in ('||inv_type_list||') and sysdate - conf_date > 29';
    -- commented as per new requirement
    --ls_sql_stmt := 'select invoice_id from invoice where cust_code__bil = :ls_custCodeBill and site_code = :ls_siteCode and inv_type in ('||inv_type_list||') and sysdate - conf_date > 29';
    
  ls_sql_stmt := 'select invoice_id from invoice where cust_code__bil = ''' || ls_custCodeBill || ''' and site_code = ''' || ls_siteCode || ''' and inv_type in (''DM'',''IS'') and (sysdate - last_day(conf_date)) > 30';
  
  	begin

		-- cursor for multiple invoices against passed cust_code
		  OPEN inv_cur FOR ls_sql_stmt ;--USING ls_custCodeBill, ls_siteCode;
		  -- Fetch rows from result set one at a time:
      <<continue1>>
		  LOOP
			FETCH inv_cur INTO ls_invoiceid;
          
			EXIT WHEN inv_cur%NOTFOUND;
			begin
				select count(1) into pod_cnt from spl_sales_por_hdr where invoice_id = ls_invoiceid and confirmed='Y';
         
			exception when others then
				return -2;
			end;
		      if  pod_cnt > 0 then
            hdr_sql_stmt := 'select sum(d.quantity__resale),d.quantity__inv, d.item_code,d.line_no__trace,d.lot_no,d.lot_sl from spl_sales_por_hdr h,spl_sales_por_det d  where  h.tran_id=d.tran_id and h.invoice_id = '''||ls_invoiceid||''' and  h.confirmed =''Y''  group by d.quantity__inv, d.item_code, d.line_no__trace, d.lot_no, d.lot_sl';
				    begin --new
					      open dtl_cur for hdr_sql_stmt;-- for check invoice amt and resale amt match or not.
                <<continue2>>
					      Loop
                  FETCH dtl_cur INTO podqty_sum,inv_qty,itemCodeL,lineTraceL,lotNoL,lotSlL;
                  EXIT WHEN dtl_cur%NOTFOUND;
                    if dtl_cur%FOUND then
                        if podqty_sum < inv_qty then
                            cnumber := fn_miscdrcr_chk(ls_invoiceid) ;
                            if cnumber = 1 then
                               return 1;                             
                            else
                                goto continue2;
                            end if;
                        else
                          goto continue2;
                        end if;
                    end if;
                end LOOP;	
				end; --new end
        CLOSE dtl_cur;
				
						--exit
			  else		    
              begin                        
                cnumber := fn_miscdrcr_chk(ls_invoiceid) ;
                if cnumber = 0 then
                  goto continue1 ;
               else
                  return cnumber;
              end if;
              end;	                 
        end if;		-- end of else  for partial POD 
		  END LOOP;
		  -- Close cursor:
		  CLOSE inv_cur;
	exception 
     when CURSOR_ALREADY_OPEN then  
			return -88;
     WHEN TOO_MANY_ROWS THEN
       return -77;
     WHEN INVALID_CURSOR THEN
        return -66;      
     WHEN INVALID_NUMBER THEN
       return -55;
 
		end;

 	return cnumber;
END;



-- POD Function ends here

-- Added by Dadaso (used in POD function) -- 20/JUN/2014 

create or replace function fn_miscdrcr_chk (as_invoiceId in invoice_trace.invoice_id%type)
RETURN number IS
TYPE cursor1  IS REF CURSOR;
TYPE cursor2  IS REF CURSOR;
 invtrace_cur cursor1;
miscdrcr_cur cursor2;
miscdrcr_sql_stmt varchar2(3000);
miscdrcr_sql_stmt_cnt varchar2(3000);
invtrace_sql_stmt varchar2(3000);
   invtrace_qty number;
   invtrace_itemcode char(10);
   invtrace_lot_sl char(15);
   invtrace_lot_no char(15);
   invtrace_lineNoTrace char(15);
    misc_cnt number;
   miscdrcr_qty number;
   miscdrcr_itemcode char(10);
   miscdrcr_lot_sl char(15);
   miscdrcr_lot_no char(15);
  miscdrcr_lineNoTrace char(15);
   cnumber number;
   begin
    invtrace_sql_stmt :='select sum(quantity),item_code,lot_no,lot_sl,inv_line_no from invoice_trace where invoice_id = '''||as_invoiceId||''' group by item_code,lot_no, lot_sl,inv_line_no';
    
    cnumber:=0;
        --dbms_output.put_line('--- Begin function miscdrcr_check-------');
        open invtrace_cur for invtrace_sql_stmt;
         <<cont3>>
        LOOP
           FETCH invtrace_cur INTO invtrace_qty,invtrace_itemcode,invtrace_lot_no,invtrace_lot_sl,invtrace_lineNoTrace;
           EXIT WHEN invtrace_cur%NOTFOUND;
          IF invtrace_cur%FOUND THEN
                  select count(1) into misc_cnt from misc_drcr_rcp h,misc_drcr_rdet d where h.tran_id=d.tran_id and h.remarks  like '%POD%'||as_invoiceId||'' and Quantity <> 0; 
                  if(misc_cnt > 0)  then               
                        miscdrcr_sql_stmt := 'select sum(nvl(d.quantity,0)) as Quantity,d.item_code,d.lot_sl, d.lot_no,line_no__invtrace from misc_drcr_rcp h,misc_drcr_rdet d where h.tran_id=d.tran_id and h.remarks  like ''%POD%'||as_invoiceId||''' and Quantity <> 0 group by d.item_code,d.lot_sl,d.lot_no,line_no__invtrace';
                        open miscdrcr_cur for miscdrcr_sql_stmt;
                       <<cont4>>
                        loop
                             FETCH miscdrcr_cur INTO miscdrcr_qty,miscdrcr_itemcode,miscdrcr_lot_sl,miscdrcr_lot_no,miscdrcr_lineNoTrace;
                             EXIT WHEN miscdrcr_cur%NOTFOUND;
                            IF miscdrcr_cur%FOUND THEN
                               if miscdrcr_qty < invtrace_qty AND invtrace_itemcode = miscdrcr_itemcode AND miscdrcr_lot_no = invtrace_lot_no AND miscdrcr_lot_sl = invtrace_lot_sl AND miscdrcr_lineNoTrace = invtrace_lineNoTrace THEN
                                 return 1;
                               else
                                   goto cont4;
                               END IF;                     
                            END IF;
                        end loop;
                        close miscdrcr_cur;
                  else --ELSE OF misc_cnt cond.
                    return 1;
                  end if; --misc_cnt condition end
          ELSE          
            goto cont3;
         END IF;
       END LOOP;
       
       close invtrace_cur;
       return  cnumber;
       
       exception when others then
      return 1;
       
       --return cnumber;
END;





---

--added cross-update sql [13/12/13] 
update obj_forms set cross_update_expr='[debit_note_amt]',expr_fld_info='[2..D.debit_note_amt]',target_fld_info='[1.1.D.debit_note_amt]'
where  win_name='w_proof_of_delivery' and form_no='2';

--added message for stockist date [20/12/13]  

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTINDLSTD','Invalid date','Stockist date should not be less than Invoice date','E',sysdate,'BASE','BASE');

--added message  [21/12/13]  
insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTCUSTNEI','Invalid combination','Entered customer code not associated with this invoice','E',sysdate,'BASE','BASE');


insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTSITENEI','Invalid combination','Entered site code not associated with this invoice','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTAPRNGRR','Invalid operation','End sale rate must be greater than approved rate','E',sysdate,'BASE','BASE');

--23/12/13 
insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTBILNNN','Bill. no is empty','Please enter bill no.','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTBILDNN','Stockist date is empty','Please enter stockist date','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTTNSVD','Transaction not saved','Error in saving transaction','E',sysdate,'BASE','BASE');

-- 21/01/14 
update gencodes set fld_value='PODDB' where mod_name='W_MISC_DRCR_RCP_DR' and fld_name='REAS_CODE' and fld_value='PODDBTNOTE';
-- 28/01/14 
insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTRMKSG','Remarks length exceed','Remarks length exceed than the specified','E',sysdate,'BASE','BASE');
--05/02/14
update transetup set edit_expr='or wf_status=''S''' where tran_window  = 'w_proof_of_delivery';

--11/02/14
insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTCOSUCFT','Transaction confirmed','Transaction confirmed successfully.No debit note generated. Please refer trace','P',sysdate,'Dadaso','BASE');

ALTER table SPL_SALES_POR_DET ADD PEND_QTY NUMBER(14,3);   

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTPODARI','POD received','Proof of Delivery already received for this invoice ID','E',sysdate,'BASE','BASE');



--Added on 09/JUN/2014

ALTER table SPL_SALES_POR_DET ADD PEND_QTY NUMBER(14,3);   

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTPODARI','POD received','Proof of Delivery already received for this invoice ID','E',sysdate,'BASE','BASE');

update itemser_cr_policy set policy_result='0' where item_ser ='NT' and cr_policy='P93';

-- Added on 06/AUG/14

update pophelp set sql_str= 
'select distinct invoice_id,inv_type,tran_date
from
(
  select case when nvl(tt.quantity,0) <> nvl(dt.quantity,0) then tt.invoice_id else ''NOT'' end invoice_id,tt.inv_type,tt.tran_date
  from
  (
    select inv.invoice_id,tr.item_code,tr.lot_no,tr.lot_sl,tr.inv_line_no,sum(nvl(tr.quantity,0)) quantity ,inv.inv_type,inv.tran_date
    from invoice_trace tr 
    join invoice inv on inv.invoice_id = tr.invoice_id and inv.site_code = ''?'' and inv.cust_code__bil = ''?'' AND sysdate - conf_date > 45 and inv_type in (''DM'',''IS'') 
    group by inv.invoice_id, tr.item_code, tr.lot_no, tr.lot_sl, tr.inv_line_no, inv.inv_type, inv.tran_date 
  ) tt 
  left outer join 
  (
    select max(quantity) quantity,remarks,item_code,lot_no,lot_sl,line_no__trace 
    from 
    (
      select rcp.invoice_id remarks,drcp.item_code,drcp.lot_no,drcp.lot_sl,drcp.line_no__trace,sum(nvl(drcp.quantity__resale,0)) quantity 
      from spl_sales_por_hdr rcp 
      join spl_sales_por_det drcp on drcp.tran_id = rcp.tran_id 
      where drcp.line_no__trace is not null 
      group by rcp.invoice_id, drcp.item_code, drcp.lot_no, drcp.lot_sl, drcp.line_no__trace 
      union all 
      select substr(rcp.remarks,-10) remarks,drcp.item_code,drcp.lot_no,drcp.lot_sl,drcp.line_no__invtrace,sum(nvl(drcp.quantity,0)) quantity 
      from misc_drcr_rcp rcp 
      join misc_drcr_rdet drcp on drcp.tran_id = rcp.tran_id 
      where drcp.line_no__invtrace is not null 
      group by substr(rcp.remarks,-10),drcp.item_code,drcp.lot_no,drcp.lot_sl,drcp.line_no__invtrace 
    ) inner 
    group by remarks,item_code,lot_no,lot_sl,line_no__trace 
  ) dt on dt.remarks = tt.invoice_id and tt.item_code = dt.item_code and tt.lot_no = dt.lot_no and tt.lot_sl = dt.lot_sl and tt.inv_line_no = dt.line_no__trace  
) datatable 
where invoice_id <> ''NOT''  
order by invoice_id'
where mod_name='W_PROOF_OF_DELIVERY' and field_name='INVOICE_ID';








