DELIMITER $$
DROP PROCEDURE IF EXISTS sp_inv_fg_reconciliation_proc $$
CREATE  PROCEDURE sp_inv_fg_reconciliation_proc(
IN p_location_code  varchar(25),
IN p_inventory_mapping_id int(10),
IN p_date_from date,
IN p_date_to date,
IN p_bag_number  varchar(25),
IN p_design_no  varchar(25),
IN p_variant_no  varchar(25),
IN p_internal_order_no  varchar(25),
IN p_customer_code  varchar(25),
IN p_external_order_no  varchar(25),
IN p_reference_id varchar(25),
IN p_internal_id varchar(25),
IN p_item_status varchar(25)
)
    MODIFIES SQL DATA
    DETERMINISTIC
BEGIN

IF p_date_from IS NULL
THEN
SET p_date_from = '1900-01-01';
END IF;

DROP   TEMPORARY TABLE IF EXISTS fg_reconciliation_details;
CREATE TEMPORARY TABLE IF NOT EXISTS fg_reconciliation_details
  ( location_code varchar(25),location_name varchar(255),transaction_id int(10),transaction_date date,
    bag_id int(10),bag_number  varchar(25),design_id int(10),design_no  varchar(25),variant_no varchar(25),
    internal_order_no varchar(25),external_order_no varchar(25),quantity int(10),
    issue_quantity int(10),final_quantity int(10),
    stock_status varchar(25),user_login varchar(25),receipt_request_id int(10),issue_request_id int(10),
    from_inv_location_id int(10),to_location_id int(10),from_inv_location_description varchar(60),
    to_inv_location_code varchar(25),to_inv_location_description varchar(60),stock_req_no varchar(25), 
    issue_to varchar(25),grn_no varchar(25),secondary_grn_no varchar(25),stock_request_id int(10),
    internal_id varchar(25),flag varchar(25),receipt_customer_code varchar(25)
  );
  

DROP   TEMPORARY TABLE IF EXISTS fg_reconciliation_details_final;
CREATE TEMPORARY TABLE IF NOT EXISTS fg_reconciliation_details_final
  ( location_code varchar(25),location_name varchar(255),transaction_id int(10),transaction_date date,
    bag_id int(10),bag_number  varchar(25),design_id int(10),design_no  varchar(25),variant_no varchar(25),
    internal_order_no varchar(25),external_order_no varchar(25),final_quantity int(10),
    stock_status varchar(25),user_login varchar(25),receipt_request_id int(10),issue_request_id int(10),
    from_inv_location_id int(10),to_location_id int(10),from_inv_location_description varchar(60),
    to_inv_location_code varchar(25),to_inv_location_description varchar(60),stock_req_no varchar(25), 
    issue_to varchar(25),grn_no varchar(25),secondary_grn_no varchar(25),stock_request_id int(10),
    internal_id varchar(25),flag varchar(25),receipt_customer_code varchar(25)
  );

       
/* i have to find out first opening stock */

/* receipt entry */
INSERT INTO fg_reconciliation_details
        ( location_code,location_name,transaction_id,transaction_date,bag_id,bag_number,design_id,design_no,
          variant_no,internal_order_no,external_order_no,quantity,issue_quantity,final_quantity,stock_status,
          user_login,receipt_request_id,issue_request_id,from_inv_location_id,to_location_id,
          from_inv_location_description,to_inv_location_code,to_inv_location_description,stock_req_no,
          issue_to,grn_no,secondary_grn_no,stock_request_id,internal_id,flag,receipt_customer_code
        )
SELECT  clm.location_code, clm.location_name,ifsr.transaction_id,DATE(ifsr.transaction_date),ifsr.bag_id,
        ifsr.bag_number,ifsr.design_id,ifsr.design_no,ifsr.variant_no,ifsr.internal_order_no,ifsr.external_order_no,
        ifsr.quantity,null,null,ifsr.stock_status,ifsr.user_login,ifsr.receipt_request_id,null,ifsr.from_inv_location_id,
        ifsr.to_location_id,null,iilm.inventory_location_code,iilm.inventory_location_name,null,
        null,ifsr.grn_no,ifsr.secondary_grn_no,null,ifsr.internal_id,'OPENING_STOCK',ifsr.customer_code
FROM    inv_fg_stock_register ifsr,
        inv_location_to_inventory_location_mapping iltilm,
        inv_inventory_location_master iilm,
        core_location_master clm
WHERE   ifsr.design_no = IFNULL(p_design_no,ifsr.design_no)
        AND ifsr.variant_no = IFNULL(p_variant_no,ifsr.variant_no)
        AND  CASE IFNULL(p_bag_number,'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.bag_number = p_bag_number END
        AND  CASE IFNULL(p_internal_order_no,'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.internal_order_no = p_internal_order_no END
        AND  CASE IFNULL(p_external_order_no,'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.external_order_no = p_external_order_no END
        AND  CASE IFNULL(p_internal_id,'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.internal_id = p_internal_id END
        AND  ifsr.stock_status = IFNULL(p_item_status,ifsr.stock_status)
        AND iltilm .location_code = p_location_code
        AND iltilm.location_inventory_location_mapping_id = IFNULL(p_inventory_mapping_id ,iltilm .location_inventory_location_mapping_id)
        AND iltilm.inventory_location_code = iilm.inventory_location_code
        AND ifsr.to_location_id = iltilm.location_inventory_location_mapping_id  
        AND clm.location_code = iltilm.location_code  
        AND ifsr.trans_type = (select parameter_id FROM core_parameter_master WHERE param_code = 'INVENTORY_TRANSACTION_TYPE'
		     AND sequence_number = 1)    
        AND  DATE(ifsr.transaction_date) <= p_date_from   ;
  
/* issue entry */

UPDATE  fg_reconciliation_details frd,
        inv_fg_stock_register ifsr,
        inv_location_to_inventory_location_mapping iltilm,
        inv_inventory_location_master iilm,
        core_location_master clm
SET     frd.issue_quantity = ifsr.quantity
WHERE   frd.design_no = ifsr.design_no
        AND frd.grn_no = ifsr.grn_no
        AND frd.stock_status = ifsr.stock_status 
        AND frd.to_location_id = ifsr.from_inv_location_id
        AND frd.receipt_request_id =ifsr.receipt_request_id
        AND ifsr.design_no = IFNULL(p_design_no,ifsr.design_no)
        AND ifsr.variant_no = IFNULL(p_variant_no,ifsr.variant_no)
        AND  CASE IFNULL(p_bag_number,'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.bag_number = p_bag_number END
        AND  CASE IFNULL(p_internal_order_no,'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.internal_order_no = p_internal_order_no END
        AND  CASE IFNULL(p_external_order_no,'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.external_order_no = p_external_order_no END
        AND  CASE IFNULL(p_internal_id,'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.internal_id = p_internal_id END
        AND  ifsr.stock_status = IFNULL(p_item_status,ifsr.stock_status)
        AND iltilm .location_code = p_location_code
        AND iltilm.location_inventory_location_mapping_id = IFNULL(p_inventory_mapping_id ,iltilm .location_inventory_location_mapping_id)
        AND iltilm.inventory_location_code = iilm.inventory_location_code
        AND ifsr.from_inv_location_id = iltilm.location_inventory_location_mapping_id  
        AND clm.location_code = iltilm.location_code  
        AND ifsr.trans_type = (select parameter_id FROM core_parameter_master WHERE param_code = 'INVENTORY_TRANSACTION_TYPE'
		     AND sequence_number = 2)    
        AND  DATE(ifsr.transaction_date) <= p_date_from ;

UPDATE  fg_reconciliation_details
SET     final_quantity = quantity - IFNULL(issue_quantity,0); 


INSERT INTO  fg_reconciliation_details_final
        ( location_code,location_name,transaction_id,transaction_date,bag_id,bag_number,design_id,design_no,
          variant_no,internal_order_no,external_order_no,final_quantity,stock_status,
          user_login,receipt_request_id,issue_request_id,from_inv_location_id,to_location_id,
          from_inv_location_description,to_inv_location_code,to_inv_location_description,stock_req_no,
          issue_to,grn_no,secondary_grn_no,stock_request_id,internal_id,flag,receipt_customer_code
        )
 SELECT location_code ,location_name ,transaction_id,transaction_date,bag_id,bag_number,design_id,design_no,
        variant_no,internal_order_no,external_order_no ,final_quantity,stock_status,
        user_login,receipt_request_id,issue_request_id,from_inv_location_id,to_location_id,
        from_inv_location_description, to_inv_location_code,to_inv_location_description ,stock_req_no,
        issue_to,grn_no,secondary_grn_no,stock_request_id,internal_id,flag ,receipt_customer_code
 FROM   fg_reconciliation_details;           

/* for receipt entry */
  
  
INSERT INTO fg_reconciliation_details_final
        ( location_code,location_name,transaction_id,transaction_date,bag_id,bag_number,design_id,design_no,
          variant_no,internal_order_no,external_order_no,final_quantity,stock_status,
          user_login,receipt_request_id,issue_request_id,from_inv_location_id,to_location_id,
          from_inv_location_description,to_inv_location_code,to_inv_location_description,stock_req_no,
          issue_to,grn_no,secondary_grn_no,stock_request_id,internal_id,flag,receipt_customer_code
        )
SELECT  clm.location_code, clm.location_name,ifsr.transaction_id,DATE(ifsr.transaction_date),ifsr.bag_id,
        ifsr.bag_number,ifsr.design_id,ifsr.design_no,ifsr.variant_no,ifsr.internal_order_no,ifsr.external_order_no,
        ifsr.quantity,ifsr.stock_status,ifsr.user_login,ifsr.receipt_request_id,null,ifsr.from_inv_location_id,
        ifsr.to_location_id,null,iilm.inventory_location_code,iilm.inventory_location_name,null,
        null,ifsr.grn_no,ifsr.secondary_grn_no,null,ifsr.internal_id,'RECEIPT',ifsr.customer_code
FROM    inv_fg_stock_register ifsr,
        inv_location_to_inventory_location_mapping iltilm,
        inv_inventory_location_master iilm,
        core_location_master clm
WHERE   ifsr.design_no = IFNULL(p_design_no,ifsr.design_no)
        AND ifsr.variant_no = IFNULL(p_variant_no,ifsr.variant_no)
        AND  CASE IFNULL(p_bag_number,'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.bag_number = p_bag_number END
        AND  CASE IFNULL(p_internal_order_no,'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.internal_order_no = p_internal_order_no END
        AND  CASE IFNULL(p_external_order_no,'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.external_order_no = p_external_order_no END
        AND  CASE IFNULL(p_internal_id,'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.internal_id = p_internal_id END
        AND  ifsr.stock_status = IFNULL(p_item_status,ifsr.stock_status)
        AND iltilm .location_code = p_location_code
        AND iltilm.location_inventory_location_mapping_id = IFNULL(p_inventory_mapping_id ,iltilm .location_inventory_location_mapping_id)
        AND iltilm.inventory_location_code = iilm.inventory_location_code
        AND ifsr.to_location_id = iltilm.location_inventory_location_mapping_id  
        AND clm.location_code = iltilm.location_code  
        AND ifsr.trans_type = (select parameter_id FROM core_parameter_master WHERE param_code = 'INVENTORY_TRANSACTION_TYPE'
		     AND sequence_number = 1)    
        AND DATE(ifsr.transaction_date) BETWEEN  DATE_ADD(p_date_from,INTERVAL 1 DAY)  AND  p_date_to  ;  
  
/* for issue entry */  
 
INSERT INTO fg_reconciliation_details_final
        ( location_code,location_name,transaction_id,transaction_date,bag_id,bag_number,design_id,design_no,
          variant_no,internal_order_no,external_order_no,final_quantity,stock_status,
          user_login,receipt_request_id,issue_request_id,from_inv_location_id,to_location_id,
          from_inv_location_description,to_inv_location_code,to_inv_location_description,stock_req_no,
          issue_to,grn_no,secondary_grn_no,stock_request_id,internal_id,flag,receipt_customer_code
        )
SELECT  clm.location_code, clm.location_name,ifsr.transaction_id,DATE(ifsr.transaction_date),ifsr.bag_id,
        ifsr.bag_number,ifsr.design_id,ifsr.design_no,ifsr.variant_no,ifsr.internal_order_no,ifsr.external_order_no,
        ifsr.quantity,ifsr.stock_status,ifsr.user_login,ifsr.receipt_request_id,null,ifsr.from_inv_location_id,
        ifsr.to_location_id,null,iilm.inventory_location_code,iilm.inventory_location_name,null,
        null,ifsr.grn_no,ifsr.secondary_grn_no,null,ifsr.internal_id,'ISSUE',ifsr.customer_code
FROM    inv_fg_stock_register ifsr,
        inv_location_to_inventory_location_mapping iltilm,
        inv_inventory_location_master iilm,
        core_location_master clm
WHERE   ifsr.design_no = IFNULL(p_design_no,ifsr.design_no)
        AND ifsr.variant_no = IFNULL(p_variant_no,ifsr.variant_no)
        AND  CASE IFNULL(p_bag_number,'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.bag_number = p_bag_number END
        AND  CASE IFNULL(p_internal_order_no,'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.internal_order_no = p_internal_order_no END
        AND  CASE IFNULL(p_external_order_no,'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.external_order_no = p_external_order_no END
        AND  CASE IFNULL(p_internal_id,'NULL') WHEN 'NULL' THEN 1=1 ELSE ifsr.internal_id = p_internal_id END
        AND  ifsr.stock_status = IFNULL(p_item_status,ifsr.stock_status)
        AND iltilm .location_code = p_location_code
        AND iltilm.location_inventory_location_mapping_id = IFNULL(p_inventory_mapping_id ,iltilm .location_inventory_location_mapping_id)
        AND iltilm.inventory_location_code = iilm.inventory_location_code
        AND ifsr.from_inv_location_id = iltilm.location_inventory_location_mapping_id  
        AND clm.location_code = iltilm.location_code  
        AND ifsr.trans_type = (select parameter_id FROM core_parameter_master WHERE param_code = 'INVENTORY_TRANSACTION_TYPE'
		     AND sequence_number = 2)    
        AND DATE(ifsr.transaction_date) BETWEEN  DATE_ADD(p_date_from,INTERVAL 1 DAY)  AND  p_date_to  ; 
 
 
  
   /* in this i have to find out actual gross wgt from production */
    /*
       SELECT *  FROM   fg_reconciliation_details_final;     
       */
       
       
       /* here i have to find out weight and insert into temp table
        if the piece is customer and from production  then from inv_fg_receipt_item_category_mapping table 
        AND if piece is from vendor there from inv_fg_receipt_design_bom_capture table  */
       
       
       
       
SELECT frdf.*,cvch.variant_combination_description,
       ifsrd.reference_no,final_quantity/final_quantity as final_qnty, null as gross_wgt,null as metal_wgt,null as acc_wgt,null as diamond_wgt,null cs_wgt,
       (SELECT cdd.concept_level_code FROM core_design_details cdd 
         WHERE design_id = frdf.design_id) as category
FROM   fg_reconciliation_details_final frdf,
       core_variant_combination_header cvch,
       inv_fg_stock_register_detail ifsrd              
WHERE  cvch.variant_combination_number = frdf.variant_no
       AND ifsrd.transaction_id = frdf.transaction_id
       AND ifsrd.reference_no =(SELECT  ifsrd1.reference_no 
                                 FROM inv_fg_stock_register_detail ifsrd1
                                 WHERE ifsrd1.detail_transaction_id = ifsrd.detail_transaction_id LIMIT 1) ;                                   
END $$
DELIMITER ;