Thursday, 26 May 2022

Query to get Business unit (ORGANIZATION_ID) Detail in oracle Fusion

  SELECT HAO.ORGANIZATION_ID AS BUSINESS_UNIT_ID, 

       HAOT.NAME, 

       HAO.BUSINESS_GROUP_ID, 

       HAO.EFFECTIVE_START_DATE, 

       HAO.EFFECTIVE_END_DATE 

  FROM HR_ALL_ORGANIZATION_UNITS_F HAO, HR_ORGANIZATION_UNITS_F_TL HAOT 

 WHERE HAO.ORGANIZATION_ID = HAOT.ORGANIZATION_ID 

   AND HAO.EFFECTIVE_START_DATE = HAOT.EFFECTIVE_START_DATE 

   AND HAO.EFFECTIVE_END_DATE = HAOT.EFFECTIVE_END_DATE 

   AND TRUNC(SYSDATE) BETWEEN HAO.EFFECTIVE_START_DATE AND 

       HAO.EFFECTIVE_END_DATE 

   AND HAOT.LANGUAGE = 'US' 

 ORDER BY HAOT.NAME

Currency code and Currency detail in oracle Fusion

select a.enterprise_id, a.currency_code, a.CURRENCY_CODE || '-' || b.NAME

  from FND_CURRENCIES_B a, FND_CURRENCIES_TL b

 where a.enterprise_id = b.enterprise_id

   and a.currency_code = b.currency_code

   and a.enabled_flag = 'Y'

   and a.currency_flag = 'Y'

   and a.iso_flag = 'Y'

   and b.language = 'US'

Wednesday, 15 December 2021

How to resolve error "This installer must be executed using a Java Development Kit (JDK) "but your jdk path" is not a valid JDK." while installing weblogic server

 Hi

Sometimes we face "not a valid jdk" error while installing weblogic server 


to resolve the above error use the following command and wait some time installation process will start shortly 

"C:\Program Files\Java\jdk1.8.0_311\bin\javaw.exe" -jar F:\Software\servers\weblogic\fmw_12.1.3.0.0_wls.jar









Wednesday, 1 December 2021

Query to get cash receipts information in oracle fusion

 /* Formatted on 12/2/2021 11:37:58 AM (QP5 v5.294) */

SELECT *

  FROM ar_cash_receipts_all

 WHERE     receipt_number = '1'

       AND NVL (TRUNC (RECEIPT_DATE), SYSDATE) =

              TO_DATE ('28-11-21', 'DD-MM-YY')

Thursday, 25 November 2021

Query to get PO tax amount against po header id (zx_lines join with po) in oracle fusion

 SELECT 

       sum(((nvl(zl.taxable_amt,0) *nvl(zl.tax_rate,0))/100) )tax_amount

  FROM zx_lines              zl,

       po_line_locations_all pll,

       po_lines_all          pl,

       po_headers_all        ph

 WHERE     zl.trx_id = pll.po_header_id

       AND zl.trx_line_id = pll.line_location_id

       AND zl.entity_code = 'PURCHASE_ORDER'

       AND pl.po_header_id = pll.po_header_id

       AND pl.po_line_id = pll.po_line_id

       AND pl.po_header_id = ph.po_header_id

       AND ph.po_header_id = 300000003243945 --po_headers_all.po_header_id

       order by pl.po_line_id

Tuesday, 23 November 2021

Query to get PO total amount in oracle fusion

 /* Formatted on 11/24/2021 11:03:32 AM (QP5 v5.294) */

SELECT SUM (NVL ( (spl.unit_price * spl.quantity), 0)) po_total_amount

   FROM po_headers_all sph, po_lines_all spl

  WHERE     sph.po_header_id = spl.po_header_id

        AND sph.type_lookup_code = 'STANDARD'

        AND sph.po_header_id = po_headers_all.po_header_id

Query to Get Requisition creator name and Po creator name in oracle fusion

 1-To get requisition creator name 

 SELECT spn.display_name  req_creator_name

          FROM per_person_names_f_v spn

         WHERE     POR_REQUISITION_HEADERS_ALL.preparer_id = spn.PERSON_ID(+)

               AND TRUNC (SYSDATE) BETWEEN spn.EFFECTIVE_START_DATE(+)

                                       AND spn.EFFECTIVE_END_DATE(+)


2- To get PO creator name 

SELECT spn.display_name po_creator_name

  FROM per_person_names_f_v spn

 WHERE     po_headers_all.AGENT_ID = spn.PERSON_ID(+)

       AND TRUNC (SYSDATE) BETWEEN spn.EFFECTIVE_START_DATE(+)

                               AND spn.EFFECTIVE_END_DATE(+)

          

Query to get Business unit (ORGANIZATION_ID) Detail in oracle Fusion

  SELECT HAO.ORGANIZATION_ID AS BUSINESS_UNIT_ID,         HAOT.NAME,         HAO.BUSINESS_GROUP_ID,         HAO.EFFECTIVE_START_DATE,       ...