Dear Friends , I have gone through a recent unique requirement to get to display Purchase Category hierarchy. You can get up to level 4 but can be extended up to your business requirement . You can comment or post your experience  following the details . SQL query to display Purchase Category hierarchy SELECT DISTINCT ( SELECT category_name FROM POR_BROWSE_CATEGORIES_TL pbct WHERE pbct.category_id = picpl.level1_cat_id ) Level_1_Name ,( SELECT category_name FROM POR_BROWSE_CATEGORIES_TL pbct WHERE pbct.category_id = picpl.level2_cat_id ) Level_2_Name ,( SELECT category_name FROM POR_BROWSE_CATEGORIES_TL pbct WHERE pbct.category_id = picpl.level3_cat_id ) Level_3_Name ,( SELECT category_name FROM EGP_CATEGORIES_VL ecv WHERE picpl.level4_cat_id = ecv.category_id ) Level_4_Name FROM POR_ITEM_CAT_PARENT_LEVELS picpl WHERE picpl.type = 'SOURCING' ORDER BY Level_1_Name ,Level_2_Name ,Level_3_Name ,Level_4_Name Happy Learnin

Dear Friends ,  Below Query can be useful to find the Unit cost of Item at costing Module . We can have  FOR Unit cost OF Material under specified Val Unit code AND Cost Book SELECT TO_CHAR ( CICT . total_cost , 'fm99999999d00' ) UNIT_COST FROM CST_ITEM_COSTS_V CICT , CST_TRANSACTIONS CT WHERE 1 = 1 AND CICT . ITEM_NUMBER = :P_Item_Number AND CICT . TRANSACTION_ID = CT . TRANSACTION_ID AND TRUNC ( CICT . PREVIOUS_COST , 6 ) = ( SELECT TRUNC ( UNIT_COST_AVERAGE , 6 ) FROM cst_perpavg_cost WHERE PERPAVG_COST_ID = ( SELECT MIN ( PERPAVG_COST_ID ) FROM cst_perpavg_cost WHERE TRANSACTION_ID = CICT . TRANSACTION_ID ) ) AND CICT . VAL_UNIT_CODE LIKE 'XXXXXX' AND TO_CHAR ( CT . TRANSACTION_DATE , 'DD-MM-YYYY' ) BETWEEN TO_CHAR ( :P_START_DATE , 'DD-MM-YYYY' ) AND TO_CHAR ( :P_END_DATE , 'DD-MM-YYYY' ) And If you want to have the Query to fetch Current Item cost

Sometime , We need to know specific relationship between some important tables . Lets come to know one such important SQL Query to know the relationship between Transfer Order, Pick Slip Number, and Supply Orchestration SQL Query can be used to show the link between a Transfer Order, Pick Slip Number, and Supply Orchestration Belo is the screenshot for instance    The Required output We need from the report is below  The Query used for above requirement is below  SELECT  a.pick_slip_number ,   b.source_header_number transfer_order_number,   c.supply_order_number ,   c.supply_order_reference_number FROM   INV_PICK_SLIP_NUMBERS a ,   WSH_DELIVERY_DETAILS b ,   DOS_SUPPLY_HEADERS c ,   INV_TRANSFER_ORDER_HEADERS d,   INV_TRANSFER_ORDER_LINES e WHERE 1 =1   AND a.PICK_SLIP_NUMBER = 11022   AND a.pick_slip_batch_id = b.batch_id   AND b.source_header_number = d.header_number   AND d.header_id = e.header_id   AND e.source_header_id = c.header_id Thank you , Please do comment and share here .

 Dear Friends , There is one small requirement we face mostly while developing the BI Report in fusion cloud Application . How to enable the Multiple choice list of parameter for the BIP Report such as below and How to make necessary change in the Data model of SQL Query . The above Choice List needs to be setup at Parameter Section Like Below  If you have such requirement for your report , Use the COALESCE function for that column in your  SQL Query .  Example : you can write something like below this will help to retrieve all Items as well as Null values at the same times . It will fulfil all such scenarios . AND (    ESI.ITEM_NUMBER=(Coalesce(NULL, :P_ITEM_NUMBER))     OR ESI.ITEM_NUMBER IN (:P_ITEM_NUMBER)   )  You can also use such as below , All scenarios will be handled  (COALESCE(null, : P_ITEM_NUMBER ) is null) OR ( ESI.ITEM_NUMBER  IN (: P_ITEM_NUMBER )) Hope above solution will help to achieve the goal of Multiple choice list of Parameter . You can comment if you have