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;

Query to get Permit Sales by Item

SELECT DISTINCT
  obh.attribute2 PERMIT_CLASS,
  hca.account_number,
  hca.account_name,
  msib.segment1 ITEM_CODE,
  msib.description ITEM_DESC,
  -- CDC
  xxar_extract_permit_sales_pkg.get_cdc_code(orl.inventory_item_id,orl.ship_from_org_id) CDC,
  xxar_extract_permit_sales_pkg.get_cdc_code_desc(orl.inventory_item_id,orl.ship_from_org_id) CDC_DESC,
  --RMA GROUP
  xxar_extract_permit_sales_pkg.get_rma_code(orl.inventory_item_id,orl.ship_from_org_id) RMA,
  xxar_extract_permit_sales_pkg.get_rma_code_desc(orl.inventory_item_id,orl.ship_from_org_id) RMA_DESC,
  --MILK CLASS based on RMA GROUP
  xxar_extract_permit_sales_pkg.get_milk_class(orl.inventory_item_id,orl.ship_from_org_id) MILK_CLASS,
  obh.cust_po_number PERMIT,
  rct.trx_number TRX_NUMBER,
  rctype.name TRX_TYPE,
  ooh.order_number,
  --- Actual shipment date for Order or Return as per FS for return  the shipment date will be the one in the original transaction referenced in the RMA transaction
  xxar_extract_permit_sales_pkg.get_shipment_date(DECODE(ooh.order_category_code,'ORDER',orl.line_id,'RETURN',orl.reference_line_id))SHIPMENT_DATE,
  rct.trx_date,
  rctgl.gl_date gl_date,
  orl.ordered_quantity ,
  orl.unit_selling_price,
  (orl.ordered_quantity*orl.unit_selling_price) amount,
  xxar_extract_permit_sales_pkg.get_lot_details(orl.inventory_item_id,orl.ship_from_org_id)  lot_number
FROM ra_customer_trx rct,
  oe_order_headers ooh,
  oe_order_lines orl,
  hz_cust_accounts hca,
  mtl_system_items_b msib,
  ra_cust_trx_types rctype,
  oe_blanket_headers obh ,
  oe_blanket_lines obl ,
  oe_transaction_types_vl ottv,
  ra_cust_trx_line_gl_dist rctgl,
  ra_batch_sources rbatchs,
  RA_CUSTOMER_TRX_LINES_ALL rl
WHERE to_char(ooh.order_number)     = (rct.interface_header_attribute1)--ct_reference)
AND ooh.header_id            = orl.header_id
and rct.interface_header_context = 'ORDER ENTRY'
and rct.org_id=121
and rl.line_type = 'LINE'
and rl.interface_line_context = 'ORDER ENTRY'
and rctgl.customer_trx_line_id=rl.customer_trx_line_id
and rl.interface_line_attribute6 = to_char(orl.line_id)
and rl.interface_line_attribute1 = to_char(ooh.order_number)
and rl.sales_order = ooh.order_number
AND rct.sold_to_customer_id  = hca.cust_account_id
AND rl.inventory_item_id(+) = msib.inventory_item_id
AND msib.inventory_item_id   = orl.inventory_item_id
AND msib.organization_id     = orl.ship_from_org_id
AND rctype.cust_trx_type_id  = rct.cust_trx_type_id
AND rct.sold_to_customer_id  =obl.sold_to_org_id
AND obh.header_id            =obl.header_id
AND ottv.transaction_type_id = obh.order_type_id
AND ooh.order_category_code = DECODE(:p_order_type,'STANDARD','ORDER','RMA','RETURN','ALL',ooh.order_category_code) --:p_order_type  for standard or RMA
AND TRUNC(rctgl.gl_date) BETWEEN :p_from_date AND :p_to_date
AND NVL(hca.account_number,-1) = NVL(:p_customer,NVL(hca.account_number,-1))
AND NVL(obh.attribute2,    -1) = NVL(:p_permit_class,NVL(obh.attribute2,-1))
AND NVL(obh.cust_po_number,-1) = NVL(:p_permit_number,NVL(obh.cust_po_number,-1))
AND rctgl.customer_trx_id   =rct.customer_trx_id
AND rct.batch_source_id     =rbatchs.batch_source_id
AND ottv.name               =ANY('CDC PERMIT (CCBU_CA)','PERMIS CCL (CCBU_CA)')
AND upper(rctype.name)      =ANY('INVOICE','DEBIT','CREDIT')
AND ooh.order_category_code = ANY('ORDER','RETURN')
AND rbatchs.name            ='ORDER MANAGEMENT'
  --Parameter  selection Criteria
AND ooh.order_category_code = DECODE(:p_order_type,'STANDARD','ORDER','RMA','RETURN','ALL',ooh.order_category_code) --:p_order_type  for standard or RMA
AND TRUNC(rctgl.gl_date) BETWEEN :p_from_date AND :p_to_date
AND NVL(hca.account_number,-1) = NVL(:p_customer,NVL(hca.account_number,-1))
AND NVL(obh.attribute2,    -1) = NVL(:p_permit_class,NVL(obh.attribute2,-1))
AND NVL(obh.cust_po_number,-1) = NVL(:p_permit_number,NVL(obh.cust_po_number,-1))
 group by
  obh.attribute2,
  hca.account_number,
  hca.account_name,
  msib.segment1 ,
  msib.description,
  -- CDC
  xxar_extract_permit_sales_pkg.get_cdc_code(orl.inventory_item_id,orl.ship_from_org_id) ,
  xxar_extract_permit_sales_pkg.get_cdc_code_desc(orl.inventory_item_id,orl.ship_from_org_id),
  --RMA GROUP
  xxar_extract_permit_sales_pkg.get_rma_code(orl.inventory_item_id,orl.ship_from_org_id) ,
  xxar_extract_permit_sales_pkg.get_rma_code_desc(orl.inventory_item_id,orl.ship_from_org_id),
  --MILK CLASS based on RMA GROUP
  xxar_extract_permit_sales_pkg.get_milk_class(orl.inventory_item_id,orl.ship_from_org_id) ,
  obh.cust_po_number ,
  rct.trx_number ,
  rctype.name ,
  ooh.order_number,
  --- Actual shipment date for Order or Return as per FS for return  the shipment date will be the one in the original transaction referenced in the RMA transaction
  xxar_extract_permit_sales_pkg.get_shipment_date(DECODE(ooh.order_category_code,'ORDER',orl.line_id,'RETURN',orl.reference_line_id)),
  rct.trx_date,
  rctgl.gl_date,
  orl.ordered_quantity ,
  orl.unit_selling_price,
  (orl.ordered_quantity*orl.unit_selling_price)
,   xxar_extract_permit_sales_pkg.get_lot_details(orl.inventory_item_id,orl.ship_from_org_id) 
ORDER BY obh.attribute2,
  hca.account_number,
  hca.account_name,
  msib.segment1 ,
  msib.description,
  -- CDC
  xxar_extract_permit_sales_pkg.get_cdc_code(orl.inventory_item_id,orl.ship_from_org_id) ,
  xxar_extract_permit_sales_pkg.get_cdc_code_desc(orl.inventory_item_id,orl.ship_from_org_id),
  --RMA GROUP
  xxar_extract_permit_sales_pkg.get_rma_code(orl.inventory_item_id,orl.ship_from_org_id) ,
  xxar_extract_permit_sales_pkg.get_rma_code_desc(orl.inventory_item_id,orl.ship_from_org_id),
  --MILK CLASS based on RMA GROUP
  xxar_extract_permit_sales_pkg.get_milk_class(orl.inventory_item_id,orl.ship_from_org_id) ,
  obh.cust_po_number ,
  rct.trx_number ,
  rctype.name ,
  ooh.order_number,
  --- Actual shipment date for Order or Return as per FS for return  the shipment date will be the one in the original transaction referenced in the RMA transaction
  xxar_extract_permit_sales_pkg.get_shipment_date(DECODE(ooh.order_category_code,'ORDER',orl.line_id,'RETURN',orl.reference_line_id)),
  rct.trx_date,
  rctgl.gl_date,
  orl.ordered_quantity ,
  orl.unit_selling_price,
  (orl.ordered_quantity*orl.unit_selling_price),
   xxar_extract_permit_sales_pkg.get_lot_details(orl.inventory_item_id,orl.ship_from_org_id) 

Query to identify receipts for which multiple entries are generated with Transaction Type = 'RECEIVE'

SELECT   receipt_num
        ,transaction_id
        ,shipment_line_id
        ,acct_type
        ,acct_nature
        ,a.code_combination_id
        ,entered_dr
        ,entered_cr
        ,COUNT(*) cnt
        , SUM(NVL(entered_cr, 0) ) - AVG(NVL(entered_cr, 0) ) "Excess Credit"
        , SUM(NVL(entered_dr, 0) ) - AVG(NVL(entered_dr, 0) ) "Excess Debit"
        ,gcc.concatenated_segments
    FROM jai_rcv_journal_entries a
        ,gl_code_combinations_kfv gcc
   WHERE 0 <
            (SELECT COUNT(*)
               FROM jai_rcv_journal_entries b
              WHERE a.transaction_id = b.transaction_id
                AND a.code_combination_id = b.code_combination_id
                AND a.acct_nature = b.acct_nature
                AND a.acct_type = b.acct_type
                AND NVL(a.entered_cr, 0) = NVL(b.entered_cr, 0)
                AND NVL(a.entered_dr, 0) = NVL(b.entered_dr, 0)
                AND a.ROWID <> b.ROWID)
     AND a.period_name = 'P01-11'                                                                                                 --'&period_name'
     AND a.organization_code = 22                                                                                           --'&organization_code'
     AND a.transaction_type = 'RECEIVE'
--and a.code_combination_id=
     AND gcc.code_combination_id = a.code_combination_id
GROUP BY receipt_num
        ,transaction_id
        ,shipment_line_id
        ,acct_type
        ,acct_nature
        ,a.code_combination_id
        ,entered_dr
        ,entered_cr
        ,gcc.concatenated_segments
ORDER BY receipt_num
        ,transaction_id
        ,shipment_line_id
        ,acct_type
        ,acct_nature
        ,a.code_combination_id
        ,entered_dr
        ,entered_cr