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;
	
	

  --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:
      
		  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
		
		                         select sum(quantity__resale),sum(quantity__inv) into podqty_sum,inv_qty from spl_sales_por_det where tran_id = ls_podtranid group by item_code ;
		
									if podqty_sum < inv_qty then
		                                cnumber :=1;
		                                
		                                -- Function fn_miscdrcr_chk to check debit note
		                                select fn_miscdrcr_chk(ls_invoiceid) into cnumber from dual;
		                                
		                                return cnumber;
		                                --exit;
		                            end if;
		
		                    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 of else  for partial POD 
		  END LOOP;
		  -- Close cursor:
		  CLOSE inv_cur;
		exception when others then
			return -1;
		end;

 	return cnumber;
END;


-- POD Function ends here

-- Added by Dadaso (used in POD function)

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);
invtrace_sql_stmt varchar2(3000);
   invtrace_qty number;
   invtrace_itemcode char(10);
   invtrace_lot_sl char(15);
   invtrace_lot_no char(15);
   
   miscdrcr_qty number;
   miscdrcr_itemcode char(10);
   miscdrcr_lot_sl char(15);
   miscdrcr_lot_no char(15);
   cnumber number;
   begin
    invtrace_sql_stmt :='select sum(quantity),item_code,lot_no,lot_sl from invoice_trace where invoice_id = '''||as_invoiceId||''' group by item_code,lot_no, lot_sl';
    
    cnumber:=0;
        --dbms_output.put_line('--- Begin function miscdrcr_check-------');
        open invtrace_cur for invtrace_sql_stmt;
        LOOP
           FETCH invtrace_cur INTO invtrace_qty,invtrace_itemcode,invtrace_lot_no,invtrace_lot_sl;
          IF invtrace_cur%FOUND THEN
          
              miscdrcr_sql_stmt := 'select sum(nvl(d.quantity,0)) as Quantity,d.item_code,d.lot_sl, d.lot_no 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';
              open miscdrcr_cur for miscdrcr_sql_stmt;
              loop
               FETCH miscdrcr_cur INTO miscdrcr_qty,miscdrcr_itemcode,miscdrcr_lot_sl,miscdrcr_lot_no;
              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 THEN
                   return 1;
                 END IF;
                 
              END IF;
              end loop;
              close miscdrcr_cur;
          ELSE
           dbms_output.put_line('--- No Records found for sql --->'+invtrace_sql_stmt);
           EXIT;
         END IF;
       END LOOP;
       
       close invtrace_cur;
       
       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');








