insert into disparm(
	prd_code,
	var_name,
	var_type,
	var_value,
	descr,
	chg_date,
	chg_user,
	chg_term )
values (
	'999999',
	'DEFAULT_PKLBLFMT',
	'S',
	'99',
	'DEFAULT CASE PICK LABEL FORMAT',
	SYSDATE,
	'BASE',
	'BASE');

COMMIT;

-----------------------------------------------------------------------------------

INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	0,
	0,
	0,
	'-',
	'Reports and Labels',
	NULL,
	'WMS.1.36.0.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);

INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	1,
	0,
	0,
	'w_pick_label',
	'Task Label',
	'Task Label',
	'WMS.1.36.1.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);


INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	2,
	0,
	0,
	'w_sscc_carton_master_label',
	'SSCC Carton Master Label',
	'SSCC CARTON MASTER LABEL',
	'WMS.1.36.2.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);

INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	3,
	0,
	0,
	'w_shippers_Expo',
	'Shipper Exports Declaration',
	'shippers_Export',
	'WMS.1.36.3.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);


INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	4,
	0,
	0,
	'w_packing_slip_rep',
	'Packing Slip',
	'Packing Slip',
	'WMS.1.36.4.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);

INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	5,
	0,
	0,
	'w_CommInvoice',
	'Commercial Invoice',
	NULL,
	'WMS.1.36.5.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);

INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	6,
	0,
	0,
	'w_bill_of_lading',
	'Bill of Lading',
	NULL,
	'WMS.1.36.6.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);

INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	7,
	0,
	0,
	'w_d_phy_scan_verf',
	'Physical Count Checklist',
	'Physical Count Checklist',
	'WMS.1.36.7.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);

INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	8,
	0,
	0,
	'w_d_phy_scan_rep',
	'Physical Scan Report',
	'Physical Scan Report',
	'WMS.1.36.8.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);

INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	9,
	0,
	0,
	'd_inv_hold_exp',
	'Expiry Report',
	'Expiry Report',
	'WMS.1.36.9.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);


INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	10,
	0,
	0,
	'w_d_current_stock_status_taro',
	'Stock Valuation Report - Taro',
	'Stock Valuation Report',
	'WMS.1.36.10.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);



INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	11,
	0,
	0,
	'w_itemcode_lotno',
	'ITEMCODE-LOTNO BARCODE',
	'ITEMCODE-LOTNO BARCODE',
	'WMS.1.36.11.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);
commit;
-----------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION DDF_PICKLBL_FMT(AS_SALE_ORDER SORDER.SALE_ORDER%TYPE) 
RETURN varchar2 IS 
LS_FMTDISPLAY varchar2(500); 
LS_CUSTCD VARCHAR2(10) ; 
BEGIN 
SELECT TRIM(CUST_CODE) INTO LS_CUSTCD FROM SORDER WHERE SALE_ORDER =AS_SALE_ORDER; 
SELECT TRIM(VAR_VALUE) INTO LS_FMTDISPLAY FROM DISPARM WHERE 
	VAR_NAME = LS_CUSTCD || '_PKLBLFMT'; 

if LS_FMTDISPLAY is null or length(trim(LS_FMTDISPLAY)) = 0 then
	LS_FMTDISPLAY := '99';
end if;
RETURN LS_FMTDISPLAY ; 
EXCEPTION  
WHEN NO_DATA_FOUND THEN RETURN '99' ;  


END;
/

-----------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION DDF_GET_TASKDESCR(REF_TYPE CHAR , ORDER_TYPE CHAR , REF_SER CHAR)
RETURN varchar2 IS 
LS_FMTDISPLAY varchar2(500); 
LS_CUSTCD VARCHAR2(10) ; 
BEGIN 
	IF ORDER_TYPE = 'C' THEN
		LS_FMTDISPLAY := 'Case Pick Task';
	ELSIF REF_TYPE = 'S' AND ORDER_TYPE = 'D' THEN
		LS_FMTDISPLAY := 'Stock-To-Dock';
	ELSIF REF_TYPE = 'A' AND ORDER_TYPE = 'A' THEN
		LS_FMTDISPLAY := 'Active Pick Task';
	ELSIF REF_TYPE = 'C' AND ORDER_TYPE = 'c' THEN
		LS_FMTDISPLAY := 'Case Pick Task';
	ELSIF REF_TYPE = 'M' AND ORDER_TYPE = 'M' THEN
		LS_FMTDISPLAY := 'Master Pick Task';
	ELSIF REF_TYPE = 'E' AND ORDER_TYPE = 'H' THEN
		LS_FMTDISPLAY := 'Replenishment Task';
	ELSIF REF_TYPE = 'N' AND ORDER_TYPE = 'L' THEN
		LS_FMTDISPLAY := 'Stock-To-Dock';
	ELSIF REF_TYPE = 'E' AND ORDER_TYPE = 'E' THEN
		LS_FMTDISPLAY := 'Active Replenishment Task';
	ELSIF REF_TYPE = 'N' AND ORDER_TYPE = 'I' THEN
		LS_FMTDISPLAY := 'Active Replenishment Task';
	ELSIF REF_TYPE = 'E' AND ORDER_TYPE = 'F' THEN
		LS_FMTDISPLAY := 'Replenishment Task (DEEP to PNT)';
	ELSIF REF_TYPE = 'N' AND ORDER_TYPE = 'J' THEN
		LS_FMTDISPLAY := 'Replenishment Task (PNT to Case Pick)';
	ELSE 
		LS_FMTDISPLAY := 'Replenishment Task';
	end if;
	RETURN LS_FMTDISPLAY ; 

END;
/

------------------------------------------------------------------------------------
INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	12,
	0,
	0,
	'w_location_code',
	'Location Barcode Label',
	'Location Barcode Label',
	'WMS.1.36.12.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);

INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	13,
	0,
	0,
	'w_pallet_no',
	'Pallet Barcode Label',
	'Pallet Barcode Label',
	'WMS.1.36.13.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);

INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	14,
	0,
	0,
	'w_LPN',
	'LPN Barcode Label',
	'LPN Barcode Label',
	'WMS.1.36.14.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);


COMMIT;

------------------------------------------------------------------------------------
2012-07-17-02.Start
------------------------------------------------------------------------------------
INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	15,
	0,
	0,
	'w_d_recv_worksheet',
	'Receiving Worksheet',
	'Receiving Worksheet',
	'WMS.1.36.15.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);


INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	16,
	0,
	0,
	'w_d_pallet_no_print',
	'List Of Unassigned Pallet No',
	'List Of Unassigned Pallet No',
	'WMS.1.36.16.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);


INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	17,
	0,
	0,
	'w_d_taro_cheque_print_main',
	'Taro Cheque Printing',
	'Taro Cheque Printing',
	'WMS.1.36.17.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);

COMMIT;


CREATE OR REPLACE function fdf_get_sundry_details_tarochq(as_sundry_type CHAR,as_sundry_code CHAR,AS_FLAg char)    
   RETURN VARCHAR2 IS LS_RET varchar2(2000) ;    
   ls_name  supplier.supp_name%TYPE;    
   ls_addr1 supplier.ALT_ADDR1%type;    
   ls_addr2 supplier.ALT_ADDR2%type;  
   ls_addr3 supplier.ALT_ADDR3%type;    
   ls_city  supplier.ALT_CITY%TYPE;    
   ls_pin  supplier.ALT_PIN%TYPE;  
 ls_state supplier.state_code%TYPE;   
 ls_count supplier.count_code%TYPE;      
BEGIN    
   IF AS_SUNDRY_type='T' THEN    
     IF AS_FLAG='N' THEN    
           select tran_name, addr1, addr2, addr3    
              into ls_name, ls_addr1, ls_addr2, ls_addr3    
              from transporter    
              where tran_code = as_sundry_code;    
       ELSIF as_flag = 'D' then    
              select tran_name, addr1, addr2, addr3, city, pin ,state_code ,count_code   
              into ls_name, ls_addr1, ls_addr2, ls_addr3, ls_city, ls_pin , ls_state ,ls_count    
              from transporter    
              where tran_code = as_sundry_code;    
      end if;    
   ELSIF AS_SUNDRY_tYPE='B' THEN    
     if as_flag = 'N' then    
              select  bank_name, addr1, addr2, addr3    
              into ls_name, ls_addr1, ls_addr2, ls_addr3    
              from bank    
         where bank_code = as_sundry_code;    
      elsif as_flag = 'D' then    
              select  bank_name, addr1, addr2, addr3, city, pin,state_code ,count_code     
              into ls_name, ls_addr1, ls_addr2, ls_addr3, ls_city, ls_pin ,ls_state ,ls_count     
              from bank    
         where bank_code = as_sundry_code;    
      end if;    
   ELSIF AS_SUNDRY_tYPE='X' THEN    
    if as_flag = 'N' then    
              select tauth_name, addr1, addr2, addr3    
              into ls_name, ls_addr1, ls_addr2, ls_addr3    
              from tax_authority    
              where tauth_code = as_sundry_code;    
      elsif as_flag = 'D' then    
              select tauth_name, addr1, addr2, addr3, city, pin , state_code ,count_code     
              into ls_name,ls_addr1,ls_addr2, ls_addr3, ls_city, ls_pin , ls_state , ls_count     
              from tax_authority    
              where tauth_code = as_sundry_code;    
      end if;    
   ELSIF AS_SUNDRY_tYPE='E' THEN    
   if as_flag = 'N' then    
              select emp_fname||emp_mname||emp_lname, cur_add1, cur_add2, cur_add3    
              into ls_name, ls_addr1, ls_addr2, ls_addr3    
              from employee    
              where emp_code = as_sundry_code;    
      elsif as_flag = 'D' then    
              select emp_fname||emp_mname||emp_lname, cur_add1, cur_add2, cur_add3, cur_city, trim(cur_pin) ,cur_state 
              into ls_name, ls_addr1, ls_addr2, ls_addr3, ls_city, ls_pin , ls_state    
              from employee    
              where emp_code = as_sundry_code;    
    end if;    
   ELSIF AS_SUNDRY_tYPE='P' THEN    
   if as_flag = 'N' then    
              select sp_name, addr1, addr2, addr3    
              into ls_name, ls_addr1, ls_addr2, ls_addr3    
              from sales_pers    
              where sales_pers = as_sundry_code;    
      elsif as_flag = 'D' then    
              select sp_name, addr1, addr2, addr3, city, pin , state_code ,count_code     
              into ls_name, ls_addr1, ls_addr2, ls_addr3, ls_city, ls_pin ,ls_state ,ls_count     
              from sales_pers    
              where sales_pers = as_sundry_code;    
      end if ;    
   ELSIF AS_SUNDRY_tYPE='S' THEN    
      if as_flag = 'N' then    
              select supp_name, alt_addr1, alt_addr2, alt_addr3    
              into ls_name, ls_addr1, ls_addr2, ls_addr3    
              from supplier    
              where supp_code = as_sundry_code;    
      elsif as_flag = 'D' then    
              select supp_name, alt_addr1, alt_addr2, alt_addr3, alt_city, alt_pin , state_code , count_code     
              into ls_name, ls_addr1, ls_addr2, ls_addr3, ls_city, ls_pin , ls_state , ls_count    
              from supplier    
              where supp_code = as_sundry_code;    
              if  length(rtrim(ls_addr1)) = 0 then    
                      select supp_name, alt_addr1, alt_addr2, alt_addr3    
                      into ls_name, ls_addr1, ls_addr2, ls_addr3    
                      from supplier    
                      where supp_code = as_sundry_code;    
              end if;    
      end if;    
   ELSIF AS_SUNDRY_tYPE='C' THEN    
   if as_flag = 'N' then    
              select cust_name, addr1, addr2, addr3    
              into ls_name, ls_addr1, ls_addr2, ls_addr3    
              from customer    
              where cust_code = as_sundry_code;    
      elsif as_flag = 'D' then    
              select cust_name, addr1, addr2, addr3, city, pin, state_code , count_code     
              into ls_name, ls_addr1, ls_addr2, ls_addr3, ls_city, ls_pin , ls_state , ls_count     
              from customer    
              where cust_code = as_sundry_code;    
      end if;    
   ELSIF AS_SUNDRY_tYPE='L' THEN    
   if as_flag = 'N' then    
              select PARTY_NAME, ADDR1, ADDR2, ADDR3    
              into ls_name, ls_addr1, ls_addr2, ls_addr3    
              from loanparty    
              where party_code = as_sundry_code;    
      elsif as_flag = 'D' then    
              select PARTY_NAME, ADDR1, ADDR2, ADDR3, city, pin , state_code , count_code     
              into ls_name, ls_addr1, ls_addr2, ls_addr3, ls_city, ls_pin, ls_state , ls_count    
              from loanparty    
              where party_code = as_sundry_code;    
      end if;    
   end if;    
 if length(rtrim(ls_addr1))=0 then    
   ls_addr2 :=null ;    
  end if;    
 if length(rtrim(ls_addr2))=0 then    
   ls_addr2 :=null ;    
  end if;    
   if length(rtrim(ls_addr3))=0 then    
   ls_addr3 :=null;    
   end if;    
   if length(rtrim(ls_city))=0 then    
   ls_city := null;    
   end if;    
   if length(rtrim(ls_pin))=0 then    
    ls_pin := null;    
   end if;  
 if length(rtrim(ls_state))=0 then    
    ls_state := null;    
   end if; 
 if length(rtrim(ls_count))=0 then    
    ls_count := null;    
   end if;   
   if as_flag = 'N' then    
      ls_ret := trim(ls_name)||chr(10)||trim(ls_addr1)||chr(10)||trim(ls_addr2)||chr(10)||trim(ls_addr3);    
   elsif as_flag = 'D' then    
      ls_ret := trim(ls_name)||chr(10)||trim(ls_addr1)||chr(10)||trim(ls_addr2)||chr(10)||trim(ls_addr3)||chr(10)||trim(ls_city)||chr(10)||trim(ls_state)||'  '||trim(ls_count)||'  '||trim(ls_pin);    
   end if;    
   return ls_ret;    
   end; 
/

------------------------------------------------------------------------------------
2012-07-17-02.End
------------------------------------------------------------------------------------

------------------------------------------------------------------------------------
2012-07-18-01.Start
------------------------------------------------------------------------------------
INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	18,
	0,
	0,
	'w_d_shipment_detail',
	'Receiving Shipment Detail Report',
	'Receiving Shipment Detail Report',
	'WMS.1.36.18.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);

INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	19,
	0,
	0,
	'w_d_shp_qa_release',
	'Shipment Receipt and QA Release',
	'Shipment Receipt and QA Release',
	'WMS.1.36.19.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);

COMMIT;

------------------------------------------------------------------------------------
2012-08-01-01.Start
------------------------------------------------------------------------------------

INSERT INTO itm2menu (
	application ,
	level_1 ,
	level_2 ,
	level_3 ,
	level_4 ,
	level_5 ,
	win_name ,
	descr,
	comments ,
	menu_path ,
	icon_path ,
	close_icon,
	open_icon ,
	obj_type ,
	chg_date ,
	chg_term ,
	chg_user ,
	mob_deploy )
VALUES (
	'WMS',
	1,
	36,
	20,
	0,
	0,
	'w_d_invoice_sweden_export',
	'Export Invoice - Swedan',
	'Export Invoice - Swedan',
	'WMS.1.36.20.0.0',
	'e12_logo.gif',
	NULL,
	NULL,
	'R',
	SYSDATE,
	'BASE',
	'PIYUSH',
	NULL);


COMMIT;


------------------------------------------------------------------------------------
2012-08-01-01.End
------------------------------------------------------------------------------------

------------------------------------------------------------------------------------
--2012-08-01-02.Start 
--SQL for DISPARM to print NMFC#,NMFC Decription and CLASS in BOL
------------------------------------------------------------------------------------

INSERT INTO DISPARM 
	(PRD_CODE,
	VAR_NAME,
	VAR_TYPE,
	VAR_VALUE,
	DESCR,
	VAR_SUBS,
	CHG_DATE,
	CHG_USER,
	CHG_TERM)
VALUES
	('999999',
	'BOL_NMFC',
	'S',
	'60000..00',
	'PHARMACEUTICALS',
	0,
	TO_DATE('01-08-2012 00:00:00','DD-MM-YYYY HH24:MI:SS'),
	'BASE',
	'BASE'
	);
COMMIT;

INSERT INTO DISPARM 
	(PRD_CODE,
	VAR_NAME,
	VAR_TYPE,
	VAR_VALUE,
	DESCR,
	VAR_SUBS,
	CHG_DATE,
	CHG_USER,
	CHG_TERM)
VALUES
	('999999',
	'BOL_CLASS',
	'S',
	'70.0',
	'CLASS',
	0,
	TO_DATE('01-08-2012 00:00:00','DD-MM-YYYY HH24:MI:SS'),
	'BASE',
	'BASE'
	);
COMMIT;


------------------------------------------------------------------------------------
--2012-08-01-02.End
------------------------------------------------------------------------------------

------------------------------------------------------------------------------------
--2012-08-17-01.Start
------------------------------------------------------------------------------------

-----------------------------WMS - TARO SQL--------------------
------------------------Created by Sagar on 10-AUG-2012-----------------------


-------------------------------------Commercial Invoice (EXPORTER NAME)----------------------------

INSERT INTO DISPARM 
(
	PRD_CODE,
	VAR_NAME,
	VAR_TYPE,
	VAR_VALUE,
	DESCR,
	VAR_SUBS,
	CHG_DATE,
	CHG_USER,
	CHG_TERM
)
VALUES
(
	'999999',
	'WMS_DOC_AUTHORITY',
	'S',
	'JOEL SOKOL',
	'JOEL SOKOL',
	0,
	TO_DATE('10-08-2012 00:00:00','DD-MM-YYYY HH24:MI:SS'),
	'BASE',
	'BASE'
);
COMMIT;

-------------------------------------END--------------------------------------------------



-------------------------------------Commercial Invoice (HARMONIZATION DESCR)----------------------------

INSERT INTO GENCODES 
(
	FLD_NAME,
	MOD_NAME,
	FLD_VALUE,
	DESCR,
	SH_DESCR,
	CHG_DATE,
	CHG_USER,
	CHG_TERM,
	ACTIVE,
	UDF_STR1,
	UDF_STR2,
	UDF_STR3,
)
VALUES
(
	'HARMONIZATION_NO',
	'W_ITEM',
	'3004.90.81',
	'DERMATOLOGICAL AGENTS AND LOCAL ANESTHETICS PUT UP IN MEASURED DOSES OR IN FORMS OR PACKINGS FOR RET',
	'3004.90.81',
	TO_DATE('10-08-2012 00:00:00','DD-MM-YYYY HH24:MI:SS'),
	'BASE',
	'BASE',
	'A',
	' ',
	' ',
	' '
);
COMMIT;

-------------------------------------END--------------------------------------------------
------------------------------------------------------------------------------------
--2012-08-17-01.End
------------------------------------------------------------------------------------

------------------------------------------------------------------------------------
--2012-08-17-02.Start
------------------------------------------------------------------------------------

--------------To pick task description on ref ser base in task label----------------

CREATE OR REPLACE FUNCTION DDF_GET_TASKDESCR(AS_REF_SERIES REFSER.REF_SER%TYPE)
RETURN VARCHAR2 IS 
LS_FMTDISPLAY REFSER.DESCR%TYPE; 
BEGIN 
	SELECT DESCR INTO LS_FMTDISPLAY FROM REFSER WHERE REF_SER = AS_REF_SERIES;
	
	RETURN LS_FMTDISPLAY ; 
END;
/

------------------------------------------------------------------------------------
--2012-08-17-02.End
------------------------------------------------------------------------------------

------------------------------------------------------------------------------------
--2012-08-22-01.Start
------------------------------------------------------------------------------------

--------------------------BILL OF LADING TABLE FUNCTION START-----------------------

CREATE TYPE BOLTYPE AS OBJECT 
(
	count1 NUMBER(5),
	shipment_id CHAR(10),
	pick_type CHAR(2),
	tot_pkt  NUMBER(5),
	gross_weigh NUMBER(14,3),
	NMFC_NO VARCHAR2(10),
	NMFC_DESCR VARCHAR2(40),
	FRT_CLASS NUMBER(5,1),
	CUST_PORD VARCHAR2(50),
	NO_ART NUMBER(5),
	GROSS_WEIGHT NUMBER(14,3),
	SORD_NO CHAR(10),
	PTCN CHAR(10)

)
/

CREATE TYPE BOLTYPESET AS TABLE OF BOLTYPE;
/


CREATE OR REPLACE FUNCTION BOLTYPETBL(AS_DESP_ID DESPATCH.DESP_ID%type, as_flag char) RETURN BOLTYPESET 
PIPELINED IS 
  out_rec BOLTYPE := BOLTYPE(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL); 
  cnt1 NUMBER(5):=0; 
   CURSOR BOL1 IS SELECT sd.shipment_id,  CASE WHEN O.CUST_PORD IS NULL THEN ' ' ELSE O.CUST_PORD END AS CUST_PORD,  D.NO_ART AS NO_ART,  D.GROSS_WEIGHT AS GROSS_WEIGHT,  CASE WHEN D.SORD_NO IS NULL THEN ' ' ELSE D.SORD_NO END AS SORD_NO,  CASE WHEN W.PTCN IS NULL THEN ' ' ELSE W.PTCN END AS PTCN FROM  SORDER O,DESPATCH D, ship_docs sd, WAVE_TASK_DET W WHERE sd.shipment_id =  AS_DESP_ID and        D.DESP_ID = sd.ref_id    AND D.SORD_NO = O.SALE_ORDER  AND 'S-DSP' = w.ref_ser(+) and D.DESP_ID = W.REF_ID(+); 
   CURSOR BOL2 IS SELECT SUM(t.count1) as count1 , 
 t.shipment_id as shipment_id , 
 t.pick_type , 
 SUM(t.tot_pkt) as tot_pkt, 
 SUM(t.gross_weigh) as gross_weigh, 
 t.NMFC_NO AS NMFC_NO , 
 t.NMFC_DESCR AS NMFC_DESCR, 
 t.FRT_CLASS AS FRT_CLASS 
from 
( 
select  distinct 
  (select count(distinct d2.pallet_no) from despatchdet d2 where d2.desp_id = d.desp_id) as count1 , 
  sd.shipment_id, 
  case when (select count(1) from wave_task_det w2 where w2.ptcn = SD.ptcn and w2.ref_ser in ('M-PICK')) > 0 then 'M' else 'P' end as pick_type , 
  d.no_art as tot_pkt, 
  D.gross_weight as gross_weigh, 
  i.NMFC_NO AS NMFC_NO, 
  i.NMFC_DESCR  AS NMFC_DESCR, 
  i.FRT_CLASS AS FRT_CLASS 
from 
 despatch d, SORDER O,ship_docs sd, despatchdet d1, item i 
where 
 sd.shipment_id = AS_DESP_ID 
 and D.DESP_ID = sd.ref_id 
 and sd.ref_ser = 'S-DSP' 
 AND d.sord_no = O.SALE_ORDER 
 AND d.desp_id = d1.desp_id 
 AND d1.item_code = i.item_code 
 ) t 
group by t.shipment_id,t.pick_type,t.NMFC_NO,t.NMFC_DESCR,t.FRT_CLASS 
order by t.shipment_id,t.pick_type,t.NMFC_NO,t.NMFC_DESCR,t.FRT_CLASS; 
BEGIN 
if as_flag='SUB1' then 
for i in BOL1 
loop 
out_rec.shipment_id := i.shipment_id; 
out_rec.CUST_PORD := i.CUST_PORD; 
out_rec.NO_ART := i.NO_ART; 
out_rec.GROSS_WEIGHT := i.GROSS_WEIGHT; 
out_rec.SORD_NO := i.SORD_NO; 
out_rec.PTCN := i.PTCN; 
pipe row ( out_rec ); 
cnt1 := cnt1 + 1; 
 end LOOP; 
WHILE (cnt1 < 6) 
loop 
out_rec.shipment_id := ' ';      
out_rec.CUST_PORD := ' ';     
out_rec.NO_ART :=0;             
out_rec.GROSS_WEIGHT :=0; 
out_rec.SORD_NO := ' ';        
out_rec.PTCN := ' ';                
pipe row ( out_rec ); 
cnt1 := cnt1 + 1; 
end loop; 
else 
for i in BOL2 
loop 
out_rec.count1 := i.count1; 
out_rec.shipment_id := i.shipment_id; 
out_rec.pick_type := i.pick_type; 
out_rec.tot_pkt  := i.tot_pkt ; 
out_rec.gross_weigh := i.gross_weigh; 
out_rec.NMFC_NO := i.NMFC_NO; 
out_rec.NMFC_DESCR := i.NMFC_DESCR; 
out_rec.FRT_CLASS := i.FRT_CLASS; 
pipe row ( out_rec ); 
cnt1 := cnt1 + 1; 
 end LOOP; 
WHILE (cnt1 < 6) 
loop 
out_rec.count1 :=0;               
out_rec.shipment_id := ' ';      
out_rec.pick_type := ' ';         
out_rec.tot_pkt  :=0;             
out_rec.gross_weigh :=0;      
out_rec.NMFC_NO := ' ';        
out_rec.NMFC_DESCR := ' ';   
out_rec.FRT_CLASS :=0;        
pipe row ( out_rec ); 
cnt1 := cnt1 + 1; 
end loop; 
end if; 
 RETURN; 
END;
/


--------------------------BILL OF LADING TABLE FUNCTION END---------------------

-------------------------------------ITM2MENU Pallet Shipping Label----------------------------

INSERT INTO itm2menu (APPLICATION,LEVEL_1,LEVEL_2,LEVEL_3,LEVEL_4,LEVEL_5,WIN_NAME,DESCR,COMMENTS,MENU_PATH,ICON_PATH,OBJ_TYPE,CLOSE_ICON,OPEN_ICON) 
VALUES (
	'WMS',
	1,
	36,
	21,
	0,
	0,
	'w_pallet_shipping_label',
	'Pallet Shipping Label',
	'Pallet Shipping Label',
	'WMS.1.36.21.0.0',
	'e12_logo.gif',
	'R',
	'',
	'');

	COMMIT;

-------------------------------------ITM2MENU Carton Contents-------------------------------------------------

INSERT INTO itm2menu (APPLICATION,LEVEL_1,LEVEL_2,LEVEL_3,LEVEL_4,LEVEL_5,WIN_NAME,DESCR,COMMENTS,MENU_PATH,ICON_PATH,OBJ_TYPE,CLOSE_ICON,OPEN_ICON) 
VALUES (
	'WMS',
	1,
	36,
	22,
	0,
	0,
	'w_carton_contents',
	'Carton Contents',
	'Carton Contents',
	'WMS.1.36.22.0.0',
	'e12_logo.gif',
	'R',
	'',
	'');
	COMMIT;

-------------------------------------ITM2MENU Quality Audit Carton-------------------------------------------------

INSERT INTO itm2menu (APPLICATION,LEVEL_1,LEVEL_2,LEVEL_3,LEVEL_4,LEVEL_5,WIN_NAME,DESCR,COMMENTS,MENU_PATH,ICON_PATH,OBJ_TYPE,CLOSE_ICON,OPEN_ICON) 
VALUES (
	'WMS',
	1,
	36,
	23,
	0,
	0,
	'w_d_quality_audit_carton',
	'Quality Audit Carton',
	'Quality Audit Carton',
	'WMS.1.36.23.0.0',
	'e12_logo.gif',
	'R',
	'',
	'');
	COMMIT;


------------------------------------------------------------------------------------
--2012-08-22-01.End
------------------------------------------------------------------------------------

------------------------------------------------------------------------------------
--2012-08-23-01.Start
------------------------------------------------------------------------------------

------------------PALLET SHIPPING LABEL INDEX CREATION FOR DESPATCHDET --> PALLET_NO START-----------------

CREATE INDEX DESPATCHDET_PALLET_NO
ON DESPATCHDET (PALLET_NO);

------------------PALLET SHIPPING LABEL INDEX CREATION FOR DESPATCHDET --> PALLET_NO END-----------------



------------------CARTON CONTENT INDEX CREATION FOR PACK_HDR --> OUTER_CARTON_NO START-----------------

CREATE INDEX PACK_HDR_OUTER_CARTON_NO
ON PACK_HDR (OUTER_CARTON_NO);

------------------CARTON CONTENT INDEX CREATION FOR PACK_HDR --> OUTER_CARTON_NO END-----------------



------------------------------ FUNCTION FOR PTCN START------------------------------ 

CREATE OR REPLACE FUNCTION DDF_GET_PTCN_PICKORDER(AS_PICK_ORDER WAVE_TASK_DET.REF_ID%TYPE) 
RETURN CHAR IS 
AS_PTCN WAVE_TASK_DET.PTCN%TYPE ; 
BEGIN 
SELECT PTCN INTO AS_PTCN FROM WAVE_TASK_DET WHERE REF_ID = AS_PICK_ORDER AND ROWNUM = 1;
RETURN AS_PTCN ; 
END;
/

------------------------------ FUNCTION FOR PTCN END------------------------------ 

------------------------------------------------------------------------------------
--2012-08-23-01.End
------------------------------------------------------------------------------------

------------------------------------------------------------------------------------
--2012-08-25-01.Start
------------------------------------------------------------------------------------

---------------------------------------QUALITY AUDIT CARTON START-----------------------------

------------------------INDEX FOR PICK_ISS_DET (PICK_ORDER, LINE_NO__ORD) START --------------------------------

	CREATE INDEX PICK_ISS_ORDER_LINE_NO
	ON PICK_ISS_DET (PICK_ORDER, LINE_NO__ORD);

------------------------INDEX FOR PICK_ISS_DET (PICK_ORDER, LINE_NO__ORD) END --------------------------------

------------------------INDEX FOR REPL_ISS_DET (REPL_ORDER, LINE_NO__ORD) START --------------------------------

	CREATE INDEX REPL_ISS_ORDER_LINE_NO
	ON REPL_ISS_DET (REPL_ORDER, LINE_NO__ORD);

------------------------INDEX FOR REPL_ISS_DET (REPL_ORDER, LINE_NO__ORD) END --------------------------------

------------------------------ FUNCTION FOR PALLET ID AND LINE NO START------------------------------ 

CREATE OR REPLACE FUNCTION DDF_GET_PALLET_ID_LINE_NO(AS_ORDER PICK_ISS_DET.PICK_ORDER%TYPE, AS_LINE_NO PICK_ISS_DET.LINE_NO__ORD%TYPE, AS_PICK_TYPE CHAR, AS_RET_VAL CHAR) 
RETURN CHAR IS 
AS_LOC_CODE__TO PICK_ISS_DET.LOC_CODE__TO%TYPE ; 
AS_PALLET_NO PICK_ISS_HDR.PALLET_NO%TYPE ; 
BEGIN 
	IF AS_PICK_TYPE = 'P' THEN
		SELECT PICK_ISS_DET.LOC_CODE__TO,  PICK_ISS_HDR.PALLET_NO INTO AS_LOC_CODE__TO,   AS_PALLET_NO
		FROM PICK_ISS_DET, PICK_ISS_HDR 
		WHERE PICK_ISS_DET.PICK_ORDER = AS_ORDER 
		AND PICK_ISS_DET.LINE_NO__ORD = AS_LINE_NO
		AND PICK_ISS_DET.TRAN_ID =  PICK_ISS_HDR.TRAN_ID
		AND PICK_ISS_HDR.PALLET_NO IS NOT NULL
		AND LENGTH(PICK_ISS_HDR.PALLET_NO) > 0;
	ELSE
		SELECT REPL_ISS_DET.LOC_CODE__TO, REPL_ISS_HDR.CARTON_NO INTO AS_LOC_CODE__TO,   AS_PALLET_NO
		FROM REPL_ISS_DET, REPL_ISS_HDR 
		WHERE REPL_ISS_DET.REPL_ORDER = AS_ORDER
		AND REPL_ISS_DET.LINE_NO__ORD = AS_LINE_NO
		AND REPL_ISS_DET.TRAN_ID =  REPL_ISS_HDR.TRAN_ID
		AND REPL_ISS_HDR.CARTON_NO IS NOT NULL
		AND LENGTH(REPL_ISS_HDR.CARTON_NO) > 0;
	END IF;
	IF AS_RET_VAL = 'P' THEN
		RETURN AS_PALLET_NO ; 
	ELSE
		RETURN AS_LOC_CODE__TO ; 
	END IF;
END;
/
------------------------------ FUNCTION FOR PALLET ID AND LINE NO END------------------------------ 

---------------------------------------QUALITY AUDIT CARTON END-----------------------------

------------------------------ FUNCTION FOR VALID DATE IN PACKING SLIP START------------------------------ 

CREATE OR REPLACE function ddf_inv_lic_date_valid (as_var_name char , 
as_fin_ent char, 
as_site_code char) 
return date is mreg_date date; 
mvar_value char (25); 
merrcode char (100); 
begin 
select var_value into mvar_value from disparm 
where prd_code = '999999' 
  and var_name = as_var_name; 
select valid_upto into mreg_date from siteregno 
 where fin_entity = as_fin_ent 
   and site_code = as_site_code 
   and ref_code = mvar_value; 
return mreg_date; 
end;
/

------------------------------ FUNCTION FOR VALID DATE IN PACKING SLIP END------------------------------ 

------------------------------------------------------------------------------------
--2012-08-25-01.End
------------------------------------------------------------------------------------

------------------------------------------------------------------------------------
--2012-08-31-01.Start
------------------------------------------------------------------------------------

------------------------------------- TASK PRODUCTIVITY REPORT --------------------------------------
INSERT INTO itm2menu (APPLICATION,LEVEL_1,LEVEL_2,LEVEL_3,LEVEL_4,LEVEL_5,WIN_NAME,DESCR,COMMENTS,MENU_PATH,ICON_PATH,OBJ_TYPE,CLOSE_ICON,OPEN_ICON) 
VALUES (
	'WMS',
	1,
	36,
	24,
	0,
	0,
	'w_d_task_productivity',
	'Task Productivity',
	'Task Productivity',
	'WMS.1.36.24.0.0',
	'e12_logo.gif',
	'R',
	'',
	'');

COMMIT;
----------------------------------------------END------------------------------------------------------------

----------- Description of Quality Audit Carton REPORT is Changed to Check Sheet--------------------------------------

update itm2menu 
SET DESCR = 'Check Sheet', COMMENTS='Check Sheet'
WHERE APPLICATION = 'WMS' 
and LEVEL_1 = 1 and LEVEL_2 = 36 and LEVEL_3 = 23 and LEVEL_4 = 0 and LEVEL_5 = 0;

COMMIT;

----------------------------------------------END---------------------------------------------
------------------------------------------------------------------------------------
--2012-08-31-01.End
------------------------------------------------------------------------------------

------------------------------------------------------------------------------------
--2012-09-14-01.Start
------------------------------------------------------------------------------------
------------------------------------- CUSTOMER INVOICE --------------------------------------
INSERT INTO itm2menu (APPLICATION,LEVEL_1,LEVEL_2,LEVEL_3,LEVEL_4,LEVEL_5,WIN_NAME,DESCR,COMMENTS,MENU_PATH,ICON_PATH,OBJ_TYPE,CLOSE_ICON,OPEN_ICON) 
VALUES (
	'WMS',
	1,
	36,
	25,
	0,
	0,
	'w_customer_invoice',
	'Customer Invoice',
	'Customer Invoice',
	'WMS.1.36.25.0.0',
	'e12_logo.gif',
	'R',
	'',
	'');

	COMMIT;
----------------------------------------------END------------------------------------------------------------

------------------------------------------------------------------------------------
--2012-09-14-01.End
------------------------------------------------------------------------------------

------------------------------------------------------------------------------------
--2012-09-21-01.Start
------------------------------------------------------------------------------------

-----------------------------WMS - TARO DISPARM SQL--------------------
------------------------Created by Sagar on 21-SEP-2012-----------------------


-------------------------------------Task Label----------------------------

INSERT INTO DISPARM 
(PRD_CODE,
VAR_NAME,
VAR_TYPE,
VAR_VALUE,
DESCR,
VAR_SUBS,
CHG_DATE,
CHG_USER,
CHG_TERM)
VALUES
('999999',
'TSKLBL_IGNORE_REFSER',
'S',
'R-TASK,R-DSA ,RS-DSO,RP-PND,R-DSO ,R-PND ',	\\Add Ref Ser for which Task Label not to print
'Ref Series for which task summary label not to be printed',
0,
TO_DATE('21-09-2012 00:00:00','DD-MM-YYYY HH24:MI:SS'),
'BASE',
'BASE'
);
COMMIT;

-------------------------------------END--------------------------------------------------
------------------------------------------------------------------------------------
--2012-09-21-01.End
------------------------------------------------------------------------------------
