Forum Stats

  • 3,767,817 Users
  • 2,252,720 Discussions
  • 7,874,336 Comments

Discussions

convert EBS function

Robeen
Robeen Member Posts: 2,109 Silver Badge

Oracle DB 12.1.0.2

EBS 12.1

AIX 6

Hello Team,


could you please advise how to rewrite the following functions?

select  distinct inventory_item_id, segment1 Item_code, apps.xx_mtg_get_last_poprice(inventory_item_id) Last_PO_Price, 
apps.xxmtg_get_max_issuedate(inventory_item_id) Last_Issue_Date 
from inv.mtl_system_items_b;

apps.xx_mtg_get_last_poprice(inventory_item_id)


create or replace FUNCTION   xx_mtg_get_last_poprice (p_itemid IN NUMBER)
  RETURN varchar2
IS
  last_po_price  varchar2(100);
BEGIN
  SELECT max(pha.CURRENCY_CODE)||' ' ||round(max(pla.UNIT_PRICE),2)
   INTO last_po_price
   FROM po_lines_all pla, po_headers_all pha
  WHERE NVL (pla.cancel_flag, 'N') = 'N'
   AND pla.po_header_id = pha.po_header_id
   AND pha.approved_flag = 'Y'
   AND pla.item_id = p_itemid
   AND pha.po_header_id =
       (SELECT MAX (pla.po_header_id)
        FROM po_lines_all pla, po_headers_all pha
        WHERE NVL (pla.cancel_flag, 'N') = 'N'
         AND pla.po_header_id = pha.po_header_id
         AND pha.approved_flag = 'Y'
         AND pla.item_id = p_itemid);
  RETURN last_po_price;
END;

apps.xxmtg_get_max_issuedate(inventory_item_id)

create or replace FUNCTION   xxmtg_get_max_issuedate(p_item_id IN NUMBER)
  RETURN DATE
AS
--M. Claire Lock Son
--Function to get the last issue date for a stock item with onhand qty > 0
  last_date_issued  DATE;
BEGIN
  select max(transaction_date) 
  INTO last_date_issued
  from mtl_material_transactions 
  where inventory_item_id = p_item_id 
  and transaction_action_id=1;

  RETURN last_date_issued;
EXCEPTION
  --When no_data_found then
  --     Return 'Null';
  WHEN OTHERS
  THEN
   RETURN NULL;
END; 


I tried to create 1 view for each function

for price

select b.inventory_item_id inventory_item_id, concat_ws(' ',max(pha.CURRENCY_CODE),cast(max(cast(pla.UNIT_PRICE as decimal(10,2))) as string)) codeprice
   FROM oracle_financial.po_lines_all pla, oracle_financial.po_headers_all pha,oracle_financial.mtl_system_items_b b,of_cubes.xx_mtg_get_last_poprice1 xx
  WHERE NVL (pla.cancel_flag, 'N') = 'N'
   AND pla.po_header_id = pha.po_header_id
   AND pha.approved_flag = 'Y'
   AND pla.item_id = b.inventory_item_id
   AND pha.po_header_id =xx.phi
   group by b.inventory_item_id;


and 1 for date

select a.inventory_item_id,max(a.transaction_date) Last_Issue_Date,b.segment1
  from oracle_financial.mtl_material_transactions a,oracle_financial.mtl_system_items_b b
  where a.inventory_item_id = b.inventory_item_id
  and transaction_action_id=1
group by a.inventory_item_id,b.segment1;

Kindly advise how can I combine these?


Thanks,


Roshan

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,200 Red Diamond

    Hi, @Robeen

    Kindly advise how can I combine these?

    How to do what you want depends on what you want. Explain exactly how you want to combine those two queries.

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. If you need to use any functions that are not built into the database (such as concat_ws), then incclude CREATE FUNCTION statements for them. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

  • User_H3J7U
    User_H3J7U Member Posts: 629 Silver Trophy
    concat_ws(' ',max(pha.CURRENCY_CODE),cast(max(cast(pla.UNIT_PRICE as decimal(10,2))) as string))
    

    concat_ws and decimal can indicate a mysql or postgresql dialect.

  • Robeen
    Robeen Member Posts: 2,109 Silver Badge

    I have converted the 2 functions. Please find below sample data from each functions


    Kindly advise how I can combine these 2 results based on inventory_item_id? Combine here means 1 table having inventory_item_id with their respective code price and last_issue_date

  • Robeen
    Robeen Member Posts: 2,109 Silver Badge
    edited Oct 26, 2021 3:57PM

    first query:


    SELECT b.INVENTORY_ITEM_ID,max(pha.CURRENCY_CODE)||' ' ||round(max(pla.UNIT_PRICE),2) codeprice
       FROM oracle_financial.po_lines_all pla, oracle_financial.po_headers_all pha,oracle_financial.mtl_system_items_b b
      WHERE NVL (pla.cancel_flag, 'N') = 'N'
       AND pla.po_header_id = pha.po_header_id
       AND pha.approved_flag = 'Y'
       AND pla.item_id = b.INVENTORY_ITEM_ID
       AND pha.po_header_id =
           (SELECT MAX (pla.po_header_id)
            FROM oracle_financial.po_lines_all pla, oracle_financial.po_headers_all pha
            WHERE NVL (pla.cancel_flag, 'N') = 'N'
             AND pla.po_header_id = pha.po_header_id
             AND pha.approved_flag = 'Y'
             AND pla.item_id = b.INVENTORY_ITEM_ID)
      group by b.INVENTORY_ITEM_ID
    

       

    second query:

      select b.inventory_item_id inventory_item_id,max(a.transaction_date) Last_Issue_Date
      from oracle_financial.mtl_material_transactions a,oracle_financial.mtl_system_items_b b
      where a.inventory_item_id = b.inventory_item_id
      and transaction_action_id=1
    group by b.inventory_item_id;
    
  • Robeen
    Robeen Member Posts: 2,109 Silver Badge

    Sample result:

    inventory_item_id Item_code Last_PO_Price Last_Issue_Date

    4	ACCE.CYGN.00000008	 	      18-FEB-16
    140	BATT.MERI.00000004	 	       30-OCT-13
    144	BEAR.CABL.00000003	MUR 378.1	22-JAN-21
    148	BEND.PVCS.00000002	MUR 113.8	21-OCT-21
    154	BIND.RING.00000002	MUR 3	    08-JUN-17
    99	BITR.CARD.00000002	 	   30-OCT-13
    29	BITR.CARD.00000006	 	      30-OCT-13
    30	BITR.CARD.00000008	 	    30-OCT-13
    7	ACCE.CYGN.00000011	 	     18-FEB-16
    89	ACCE.NOKI.00000N70	 	     18-FEB-16
    117	ADAP.ACCE.00008520	 	      06-JUN-17
    129	ANTI.SEPT.00000002	MUR 152	       13-SEP-11
    1365	ASST.SECU.00000004	MUR 53420	
    1371	ASST.STSY.00000004	MUR 2131044	
    133	BARS.ANGL.00000001	 	       08-JUN-17
    243	CABL.COAX.00000019	 	
    257	CABL.LASE.00000001	 	       31-OCT-13
    261	CABL.OPTI.00000010	EUR 1.08	15-OCT-21
    262	CABL.OPTI.00000018	EUR 1.64	01-NOV-17
    276	CABL.UNDG.00030PR8	 	        15-SEP-15
    292	CABL.UNDG.02400PR4	EUR 71.76	07-JAN-20
    293	CABL.USBS.00000003	 	         06-JUN-17
    


  • Robeen
    Robeen Member Posts: 2,109 Silver Badge

    @Frank Kulash

    ok sure I will post sample data next time.