Package realted to Permit Sales by Item

create or replace PACKAGE        xxar_extract_permit_sales_pkg AUTHID CURRENT_USER IS
  /*****************************************************************************/
  /*   Name: xxar_extract_permit_sales_pkg                                     */
  /*   Goal: Package contains functions and variables used for                 */
  /*         report extract_permit_sales                                       */
  /*   Parameters          :  NA                                               */
  /*                                                                           */
  /*   Created by        Date        Description                               */
  /*   ----------------- ----------  -----------------------------------       */
  /*   Chandra Kadali    12/11/2014  Initial coding                            */
  /*                                                                           */
  /*   History of modification                                                 */
  /*   Name                   Date        DDM  Description                     */
  /*   --------------------  ----------  ----  --------------------------------*/
  /*****************************************************************************/


  p_order_type        VARCHAR2(1000);
  p_from_date         DATE;
  p_to_date           DATE;
  p_customer                   VARCHAR2(1000);
  p_permit_class           VARCHAR2(1000);
  p_permit_number VARCHAR2(100);
 


  FUNCTION get_shipment_date(p_line_id IN NUMBER) RETURN VARCHAR2;
  FUNCTION get_cdc_code(p_item_id IN Number,p_org_id IN NUMBER) RETURN VARCHAR2 ;
   FUNCTION get_cdc_code_desc(p_item_id IN Number,p_org_id IN NUMBER) RETURN VARCHAR2 ;
  FUNCTION get_rma_code(p_item_id IN NUMBER,p_org_id  IN NUMBER) RETURN VARCHAR2;
  FUNCTION get_rma_code_desc(p_item_id IN NUMBER,p_org_id  IN NUMBER) RETURN VARCHAR2;
  FUNCTION get_milk_class(p_item_id IN NUMBER,p_org_id  IN NUMBER) RETURN VARCHAR2;
  FUNCTION get_error_desc(p_item_id IN NUMBER,p_org_id  IN NUMBER) RETURN VARCHAR2;
  FUNCTION get_lot_details(p_item_id IN NUMBER,p_org_id  IN NUMBER) RETURN VARCHAR2;

END xxar_extract_permit_sales_pkg;

create or replace PACKAGE BODY xxar_extract_permit_sales_pkg IS
  /*****************************************************************************/
  /*   Name: xxont_cust_contact_pkg                                       */
  /*   Goal: Package contains functions  used for                 */
  /*         report control_permit_sales                                      */
  /*   Parameters          :  NA                                               */
  /*                                                                           */
  /*   Created by        Date        Description                               */
  /*   ----------------- ----------  -----------------------------------       */
  /*   Chandra Kadali    12/11/2014  Initial coding                            */
  /*                                                                           */
  /*   History of modification                                                 */
  /*   Name                   Date        DDM  Description                     */
  /*   --------------------  ----------  ----  --------------------------------*/
  /*****************************************************************************/
 
   /*****************************************************************************/
  /* Name of the function : get_shipment_date                                     */
  /* logic : Function to derive the  Address                            */
  /* Creation:                                                                 */
  /* Name : Chandra Kadali    Date : 12/11/2014                                 */
  /*                                                                           */
  /* History of modification                                                   */
  /* Name                  Date         Change  Description                    */
  /* --------------------  ----------  ----  ----------------------------------*/
  /*****************************************************************************/
 
  FUNCTION get_shipment_date(p_line_id IN NUMBER) RETURN VARCHAR2 IS
    v_shipment_date                VARCHAR2(200) := NULL;
   
    BEGIN
   
   SELECT TO_CHAR(orl.actual_shipment_date,
                   'YYYY/MM/DD') shipment_date
           INTO v_shipment_date
          
           FROM oe_order_lines orl
           WHERE line_id = p_line_id;
   
   
     RETURN v_shipment_date;
    
  EXCEPTION
    WHEN OTHERS THEN
      v_shipment_date := NULL;
      RETURN v_shipment_date;
  END get_shipment_date;
 


   /*****************************************************************************/
  /* Name of the function : get_cdc_code                                     */
  /* logic :                             */
  /* Creation:                                                                 */
  /* Name : Chandra Kadali    Date : 12/11/2014                                 */
  /*                                                                           */
  /* History of modification                                                   */
  /* Name                  Date         Change  Description                    */
  /* --------------------  ----------  ----  ----------------------------------*/
  /*****************************************************************************/
 
  FUNCTION get_cdc_code(p_item_id IN NUMBER,
                        p_org_id  IN NUMBER) RETURN VARCHAR2 IS
 
    v_cdc_code VARCHAR2(200) := NULL;
 
  BEGIN
 
    SELECT category_concat_segs
   
      INTO v_cdc_code
      FROM mtl_item_categories_v
     WHERE inventory_item_id = p_item_id
       AND organization_id = p_org_id
       AND category_set_name = 'AGR CODE CDC';
 
    RETURN v_cdc_code;
 
  EXCEPTION
    WHEN OTHERS THEN
      v_cdc_code := NULL;
      RETURN v_cdc_code;
  END get_cdc_code;
 
  /*****************************************************************************/
  /* Name of the function : get_cdc_code_desc                                     */
  /* logic :                             */
  /* Creation:                                                                 */
  /* Name : Chandra Kadali    Date : 12/11/2014                                 */
  /*                                                                           */
  /* History of modification                                                   */
  /* Name                  Date         Change  Description                    */
  /* --------------------  ----------  ----  ----------------------------------*/
  /*****************************************************************************/
 
  FUNCTION get_cdc_code_desc(p_item_id IN NUMBER,
                        p_org_id  IN NUMBER) RETURN VARCHAR2 IS
 
    v_cdc_code_desc VARCHAR2(200) := NULL;
    v_cdc_code varchar2(200):=null;
    v_category_id number:=null;
    v_category_set_id number:=null;
 
  BEGIN
  begin
    SELECT category_concat_segs
    ,mtl_item_categories_v.CATEGORY_SET_ID
    ,category_id
      INTO v_cdc_code
      ,v_category_set_id
      ,v_category_id
      FROM mtl_item_categories_v
     WHERE inventory_item_id = p_item_id
       AND organization_id = p_org_id
       AND category_set_name = 'AGR CODE CDC';
 
    --RETURN v_cdc_code;
 
  EXCEPTION
    WHEN OTHERS THEN
      v_cdc_code := NULL;
      v_category_set_id:=null;
      v_category_id:=null;
     -- RETURN v_cdc_code;
  --END get_cdc_code;
  end;
 
  if(v_cdc_code is not null) then
  begin
  select description into v_cdc_code_desc from mtl_categories_vl
  where  category_id=v_category_id
  --and language=USERENV('LANG')
  ;
return v_cdc_code_desc;
  exception when others then
  v_cdc_code_desc:=null;
  return v_cdc_code_desc;
end;
else
v_cdc_code_desc:=null;
return v_cdc_code_desc;
end if;
END get_cdc_code_desc;

   /*****************************************************************************/
  /* Name of the function : get_rma_code                                     */
  /* logic :                             */
  /* Creation:                                                                 */
  /* Name : Chandra Kadali    Date : 12/11/2014                                 */
  /*                                                                           */
  /* History of modification                                                   */
  /* Name                  Date         Change  Description                    */
  /* --------------------  ----------  ----  ----------------------------------*/
  /*****************************************************************************/
 
  FUNCTION get_rma_code(p_item_id IN NUMBER,
                        p_org_id  IN NUMBER) RETURN VARCHAR2 IS
 
    v_rma_code VARCHAR2(200) := NULL;
 
  BEGIN
 
    SELECT category_concat_segs
      INTO v_rma_code
      FROM mtl_item_categories_v
     WHERE inventory_item_id = p_item_id
       AND organization_id = p_org_id
       AND category_set_name = 'AGR RMA GROUP';
 
    RETURN v_rma_code;
 
  EXCEPTION
    WHEN OTHERS THEN
      v_rma_code := NULL;
      RETURN v_rma_code;
  END get_rma_code;
 
 
   /*****************************************************************************/
  /* Name of the function : get_rma_code_desc                                     */
  /* logic :                             */
  /* Creation:                                                                 */
  /* Name : Chandra Kadali    Date : 12/11/2014                                 */
  /*                                                                           */
  /* History of modification                                                   */
  /* Name                  Date         Change  Description                    */
  /* --------------------  ----------  ----  ----------------------------------*/
  /*****************************************************************************/
 
  FUNCTION get_rma_code_desc(p_item_id IN NUMBER,
                        p_org_id  IN NUMBER) RETURN VARCHAR2 IS
 
    v_rma_code VARCHAR2(200) := NULL;
    v_rma_code_desc VARCHAR2(200) := NULL;
    v_category_id number;
 
  BEGIN
  begin
    SELECT category_concat_segs,category_id
      INTO v_rma_code,v_category_id
      FROM mtl_item_categories_v
     WHERE inventory_item_id = p_item_id
       AND organization_id = p_org_id
       AND category_set_name = 'AGR RMA GROUP';
 
    ---RETURN v_rma_code;
 
  EXCEPTION
    WHEN OTHERS THEN
      v_rma_code := NULL;
      --RETURN v_rma_code;
      end;
     
      if(v_rma_code is not null ) then
      begin
      select description into v_rma_code_desc from mtl_categories_vl where category_id=v_category_id ;--and language=USERENV('LANG');
      return v_rma_code_desc;
      exception when others then
      v_rma_code_desc:=null;
      return v_rma_code_desc;
      end;
      end if;
      return v_rma_code_desc;
  END get_rma_code_desc;
 
 
 

   /*****************************************************************************/
  /* Name of the function : get_milk_class                                     */
  /* logic :                             */
  /* Creation:                                                                 */
  /* Name : Chandra Kadali    Date : 12/11/2014                                 */
  /*                                                                           */
  /* History of modification                                                   */
  /* Name                  Date         Change  Description                    */
  /* --------------------  ----------  ----  ----------------------------------*/
  /*****************************************************************************/
 
  FUNCTION get_milk_class(p_item_id IN NUMBER,
                        p_org_id  IN NUMBER) RETURN VARCHAR2 IS
 
    v_rma_code VARCHAR2(200) := NULL;
    v_milk_class VARCHAR2(200) := NULL;
 
  BEGIN
 
    SELECT category_concat_segs
      INTO v_rma_code
      FROM mtl_item_categories_v
     WHERE inventory_item_id = p_item_id
       AND organization_id = p_org_id
       AND category_set_name = 'AGR RMA GROUP';
      
       select attribute1 into v_milk_class from mtl_categories where
        segment1 = v_rma_code
       and attribute_category='AGR RMA GROUP CATEGORY';      
 
    RETURN v_milk_class;
 
  EXCEPTION
    WHEN OTHERS THEN
      v_milk_class := NULL;
      RETURN v_milk_class;
  END get_milk_class; 
 
 
 

   /*****************************************************************************/
  /* Name of the function : get_error_desc                                     */
  /* logic :                             */
  /* Creation:                                                                 */
  /* Name : Chandra Kadali    Date : 12/11/2014                                 */
  /*                                                                           */
  /* History of modification                                                   */
  /* Name                  Date         Change  Description                    */
  /* --------------------  ----------  ----  ----------------------------------*/
  /*****************************************************************************/
 
  FUNCTION get_error_desc(p_item_id IN NUMBER,
                        p_org_id  IN NUMBER) RETURN VARCHAR2 IS
 

    v_error varchar2(4000) :=NULL;
   
 
  BEGIN
 
    SELECT decode(xxar_extract_permit_sales_pkg.get_cdc_code(p_item_id,p_org_id),NULL,'Missing CDC||','') ||
           decode(xxar_extract_permit_sales_pkg.get_rma_code(p_item_id,p_org_id),NULL,'Missing RMA||','') ||
           decode(xxar_extract_permit_sales_pkg.get_milk_class(p_item_id,p_org_id),NULL,'Missing Milk Class','')
          
           INTO v_error
          
           FROM DUAL;
   
 
    RETURN v_error;
 
  EXCEPTION
    WHEN OTHERS THEN
      v_error := NULL;
      RETURN v_error;
  END get_error_desc; 
 
  FUNCTION get_lot_details(p_item_id IN NUMBER,
                        p_org_id  IN NUMBER) RETURN VARCHAR2 IS
 
cursor c_lot_number is
 SELECT lot_number from mtl_lot_numbers where inventory_item_id=p_item_id and organization_id=p_org_id;
    v_lot varchar2(4000) :=NULL;
   
 
  BEGIN
  for rec_lot_number in c_lot_number
  loop
 
  v_lot:=v_lot||rec_lot_number.lot_number|| '     ';
  end loop;
    
 
    RETURN v_lot;
  
 
  EXCEPTION
    WHEN OTHERS THEN
      v_lot := NULL;
      RETURN v_lot;
  END get_lot_details  ;
 
 
END xxar_extract_permit_sales_pkg;