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 -- 

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);
   
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 -1;
	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;
	
     -- @ Sandesh : Following logic will decide invoice table conf_date range for which POD entry needs to checked
     -- If current date is 31 then it will read invoices from previous month's 1st day to 30th day of previous month
     -- For e.g. assuming current date is 31-Dec-2013 then invoices will between 1-Nov-2013 to 30-Nov-2013
	 -- If current date is between 1 to 30 then it will read invoices from 2 previous month's 1st day to 30th day of that month
     -- For e.g. assuming current date is 30-Dec-2013 then invoices will between 1-Oct-2013 to 30-Oct-2013
           
    begin
		select to_char(sysdate, 'DD'),to_char(sysdate, 'MM'),to_char(sysdate, 'YY') into curr_date,curr_mon,curr_yr FROM DUAL;
      
		if curr_date = '31' then 
			
      		select to_char(sysdate, 'MM')-1 into invoice_mnth FROM DUAL;
      
		      if curr_mon = '01' then
		        select to_char(sysdate, 'YY')-1 into curr_yr FROM DUAL;
		        invoice_mnth := '12';
		      else
		        invoice_mnth := ''|| curr_mon -1 ;
		      end if;
      
		elsif curr_date >= '1' and curr_date <='30' then
			
		      select to_char(sysdate, 'MM')-2 into invoice_mnth FROM DUAL;
		      
		      if curr_mon = '01' then
		        select to_char(sysdate, 'YY')-1 into curr_yr FROM DUAL;
		        invoice_mnth := '11';
		      elsif curr_mon = '02'  then
		        select to_char(sysdate, 'YY')-1 into curr_yr FROM DUAL;
		        invoice_mnth := '12';  
		      else
		        invoice_mnth := ''||curr_mon -2 ;  
		      end if;
      
		end if;
			
		begin_date := '1-'||invoice_mnth||'-'||curr_yr;	
		end_date   := '30-'||invoice_mnth||'-'||curr_yr;
			
   
	exception when others then
		return -1;
	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 conf_date between '''|| to_date(begin_date) ||''' and '''|| to_date(end_date)  ;
    --ls_sql_stmt := 'select invoice_id from invoice where cust_code__bil = :ls_custCodeBill and site_code = :ls_siteCode and inv_type in (''DM '') and conf_date between '''|| to_date(begin_date) ||''' and '''|| to_date(end_date) ||'''' ;
    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 conf_date between '''|| to_date(begin_date) ||''' and '''|| to_date(end_date) ||'''' ;
  
		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:
      
		  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
				cnumber := 1;
       
        return cnumber;
				--exit
			else
          -- Logic for partial POD entries starts here

           hdr_sql_stmt := 'select tran_id from spl_sales_por_hdr where invoice_id = '''||ls_invoiceid||''' and confirmed = ''Y'' and wf_status <> ''R'' ';

           begin
              open dtl_cur for hdr_sql_stmt;-- USING ls_invoiceid;

              LOOP
                FETCH dtl_cur INTO ls_podtranid;
                EXIT WHEN dtl_cur%NOTFOUND;
                    begin

                        dtl_sql_stmt := 'select line_no,item_code,quantity__inv into lineno,itemcode,invqty from spl_sales_por_det where tran_id = '''||ls_podtranid||'''';

                        begin
                          open item_cur for dtl_sql_stmt;-- USING ls_podtranid;
                          LOOP
                            FETCH item_cur INTO ls_podtranid,itemcode,lineno,inv_qty;
                            EXIT WHEN item_cur%NOTFOUND;

                               select sum(quantity__resale) into podqty_sum from spl_sales_por_det where tran_id = ls_podtranid and item_code = itemcode;

                                if podqty_sum < inv_qty then
                                  cnumber :=1;
                                  return cnumber;
                                  --exit;
                                end if;

                          END LOOP;
                          CLOSE item_cur;
                        end;
                    exception when others then
                        return -1;
                    end;
               END LOOP;
             CLOSE dtl_cur; 
           end;

           -- Logic for partial POD entries ends here

				cnumber := 0;
			end if;
		  END LOOP;
		  -- Close cursor:
		  CLOSE inv_cur;
		exception when others then
			return -1;
		end;

 	return cnumber;
END;

-- POD Function ends here

--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');








