Query for Intercompany Imbalance Report

 SELECT A.*, A.US_Ledger_amount + A.CA_Ledger_amount Imbalance

FROM (

    SELECT  --LEDGER             ,

Comp_Inter_acct    ,

SUM(US_Ledger_amount) US_Ledger_amount   ,

SUM(CA_Ledger_amount) CA_Ledger_amount   ,

Accounting_Year    ,

Accounting_Period

FROM  

( SELECT  

GL.NAME LEDGER,

CASE WHEN GL.NAME = 'XXXXXXXXXXXXXX'

THEN GCC.SEGMENT1||'-'||GCC.SEGMENT7||'-'|| GCC.SEGMENT4

WHEN GL.NAME = 'XXXXXXXXX'

THEN GCC.SEGMENT7||'-'||GCC.SEGMENT1||'-'|| GCC.SEGMENT4

END Comp_Inter_acct,

CASE WHEN GL.NAME = 'XXXXXXXXXXX'

THEN SUM( NVL(GB.PERIOD_NET_DR,0) -  NVL(GB.PERIOD_NET_CR ,0 )) 

   ELSE 0 

   END US_Ledger_amount,

CASE WHEN GL.NAME = 'XXXXXXXX'

-- AND ((GB.translated_flag is null) or (GB.translated_flag = 'Y'))

AND ((GB.translated_flag = 'N') or (GB.translated_flag = 'Y'))

THEN SUM( NVL(GB.PERIOD_NET_DR,0) -  NVL(GB.PERIOD_NET_CR ,0 )) 

ELSE 0 

END CA_Ledger_amount,

GB.PERIOD_YEAR Accounting_Year,

GB.PERIOD_NUM Accounting_Period

FROM GL_CODE_COMBINATIONS GCC,

GL_BALANCES GB,

GL_LEDGERS GL

-- GL_JE_HEADERS GJH,

-- GL_JE_LINES GJL

WHERE 1=1

AND GCC.SEGMENT4 IN ('370500') --, '370000') 

AND  NVL(GB.PERIOD_NET_DR,0) -  NVL(GB.PERIOD_NET_CR ,0 ) != 0

-- AND GCC.SEGMENT1||'-'||GCC.SEGMENT7||'-'|| GCC.SEGMENT4 IN ( 'Z01378-C01031-370500')

-- AND GB.PERIOD_NAME  = 'May-24' 

AND GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID

-- AND GB.CURRENCY_CODE = GL.CURRENCY_CODE

AND GB.LEDGER_ID = GL.LEDGER_ID  

AND GB.CURRENCY_CODE = 'USD'

AND GL.NAME IN (  XXXXXXXXXX )

AND GL.LEDGER_ID     IN (SELECT gl.ledger_id

FROM fun_user_role_data_asgnmnts f,

per_roles_dn_vl p,

per_users pu,

gl_ledgers gl

   WHERE p.role_common_name = f.role_name

AND pu.user_guid = f.user_guid

AND gl.ledger_id = f.ledger_id

AND f.active_flag = 'Y' 

and upper(pu.username) = upper(:xdo_user_name))

AND GB.PERIOD_YEAR = :P_YEAR

AND GB.PERIOD_NUM =   :P_NUM

-- AND GCC.SEGMENT4 =   NVL(:P_ACCOUNT, GCC.SEGMENT4)  

-- AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID

-- AND GJH.ledger_id = GJL.ledger_id

-- AND GJH.LEDGER_ID = GL.LEDGER_ID  

-- AND GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID 

-- AND GJH.status = 'P'

-- AND GJL.PERIOD_NAME  = 'May-24'  

-- AND GB.PERIOD_NAME =   GJL.PERIOD_NAME

-- AND GB.LEDGER_ID = GJL.LEDGER_ID  

-- AND GB.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID 

GROUP BY GL.NAME, GCC.SEGMENT1, GCC.SEGMENT7, GCC.SEGMENT4, 

GB.TRANSLATED_FLAG, GB.PERIOD_YEAR, GB.PERIOD_NUM 

)

WHERE 1=1

   AND (US_Ledger_amount + CA_Ledger_amount) != 0    

GROUP BY --LEDGER             ,

Comp_Inter_acct    ,

Accounting_Year    ,

Accounting_Period

)A

WHERE 1=1

AND (A.US_Ledger_amount + A.CA_Ledger_amount) != 0

Query for Absence and Overlapping employees of Absence

WITH Main_Q as

(SELECT distinct * FROM(

SELECT papf.person_id,

       papf.person_number,

       ppnf.display_name

       employee_name,

       pasf_Sup.manager_name

       manager_name,

       pjft.name

       job_name,

       pgft.name

       grade_name,

       --abt.absence_type_id,

       abt.name

       absence_type,

   paam.ASS_ATTRIBUTE1 sector,

   paam.ASS_ATTRIBUTE2 unit,

       (SELECT DISTINCT( pd.name )

        FROM   per_departments pd

        WHERE  pd.organization_id = paam.organization_id

               AND pd.effective_start_date =

                   (SELECT Max(pd1.effective_start_date)

                    FROM   per_departments pd1

                    WHERE

                   pd1.organization_id = pd.organization_id

       ))

       department_name,

       --To_char(apae.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

       apae.start_date start_date,

      -- To_char(apae.end_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

       apae.end_date end_date,

       apae.duration,

   apae.start_date order_by_date,

   apae.attribute1 Delegated_Person

FROM   anc_per_abs_entries apae,

       per_periods_of_service pps,

       anc_absence_types_vl abt,

       per_all_people_f papf,

       per_person_names_f ppnf,

       (SELECT ppnf2.full_name manager_name,

               pasf.person_id

        FROM   per_assignment_supervisors_f pasf,

               per_person_names_f ppnf2

        WHERE  pasf.manager_id = ppnf2.person_id

               AND pasf.manager_type = 'LINE_MANAGER'

               AND Trunc(SYSDATE) BETWEEN pasf.effective_start_date AND

                                          pasf.effective_end_date

               AND Trunc(SYSDATE) BETWEEN ppnf2.effective_start_date AND

                                          ppnf2.effective_end_date

               AND ppnf2.name_type = 'GLOBAL') pasf_Sup,

       per_all_assignments_m paam,

       per_jobs_f_vl pjft,

       per_grades_f_vl pgft,

   hr_organization_units bu,

       hr_organization_units le,

   per_asg_responsibilities resp

   --hr_organization_units dept

WHERE  apae.period_of_service_id = pps.period_of_service_id

       AND apae.absence_type_id = abt.absence_type_id

   AND apae.ABSENCE_STATUS_CD <> 'ORA_WITHDRAWN'

   AND apae.APPROVAL_STATUS_CD = 'APPROVED'

       AND abt.name = 'Tentative Leave Plan'

       AND pps.person_id = papf.person_id

       AND papf.person_id = ppnf.person_id

       AND papf.person_id = pasf_Sup.person_id(+)

       AND papf.person_id = paam.person_id(+)

       AND paam.primary_assignment_flag(+) = 'Y'

       --AND paam.assignment_type = 'E'

       --AND paam.effective_latest_change = 'Y'

       AND paam.job_id = pjft.job_id (+)

       AND paam.grade_id = pgft.grade_id (+)

   AND paam.business_unit_id = bu.organization_id

       AND paam.legal_entity_id = le.organization_id

   AND resp.person_id = HRC_SESSION_UTIL.get_user_personid

       AND resp.responsibility_type ='HR_REP'

       AND resp.status = 'Active'

       AND  resp.legal_entity_id = paam.legal_entity_id

   --AND resp.BUSINESS_UNIT_ID=paam.BUSINESS_UNIT_ID

   --AND resp.organization_id =paam.organization_id 

       AND TRUNC(sysdate) BETWEEN resp.start_date AND NVL(resp.end_date,TRUNC(sysdate))

       AND Trunc(SYSDATE) BETWEEN pjft.effective_start_date(+) AND

                                  pjft.effective_end_date(+)

       AND Trunc(SYSDATE) BETWEEN pgft.effective_start_date(+) AND

                                  pgft.effective_end_date(+)

       AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND

                                  paam.effective_end_date

       AND Trunc(SYSDATE) BETWEEN abt.effective_start_date AND

                                  abt.effective_end_date

       AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND

                                  papf.effective_end_date

       AND Trunc(SYSDATE) BETWEEN ppnf.effective_start_date AND

                                  ppnf.effective_end_date

       AND ppnf.name_type = 'GLOBAL'

      --AND papf.person_number IN ( '0015', '0033' )

  AND to_number(Extract(year FROM apae.start_date)) = to_number(:p_date)

  

  UNION

SELECT papf.person_id,

       papf.person_number,

       ppnf.display_name

       employee_name,

       pasf_Sup.manager_name

       manager_name,

       pjft.name

       job_name,

       pgft.name

       grade_name,

       --abt.absence_type_id,

       abt.name

       absence_type,

   paam.ass_attribute2 sector,

       paam.ass_attribute1 unit,

       (SELECT DISTINCT( pd.name )

        FROM   per_departments pd

        WHERE  pd.organization_id = paam.organization_id

               AND pd.effective_start_date =

                   (SELECT Max(pd1.effective_start_date)

                    FROM   per_departments pd1

                    WHERE

                   pd1.organization_id = pd.organization_id))

       department_name,

      -- To_char(apae.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

       apae.start_date start_date,

      -- To_char(apae.end_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

      apae.end_date end_date,

       apae.duration,

   apae.start_date order_by_date,

   apae.attribute1 Delegated_Person

FROM   anc_per_abs_entries apae,

       per_periods_of_service pps,

       anc_absence_types_vl abt,

       per_all_people_f papf,

       per_person_names_f ppnf,

       (SELECT ppnf2.full_name manager_name,

               pasf.person_id

        FROM   per_assignment_supervisors_f pasf,

               per_person_names_f ppnf2

        WHERE  pasf.manager_id = ppnf2.person_id

               AND pasf.manager_type = 'LINE_MANAGER'

               AND Trunc(SYSDATE) BETWEEN pasf.effective_start_date AND

                                          pasf.effective_end_date

               AND Trunc(SYSDATE) BETWEEN ppnf2.effective_start_date AND

                                          ppnf2.effective_end_date

               AND ppnf2.name_type = 'GLOBAL') pasf_Sup,

       per_all_assignments_m paam,

       per_jobs_f_vl pjft,

       per_grades_f_vl pgft,

   hr_organization_units bu,

       hr_organization_units le,

   per_asg_responsibilities resp

   --hr_organization_units dept

WHERE  apae.period_of_service_id = pps.period_of_service_id

       AND apae.absence_type_id = abt.absence_type_id

   AND apae.ABSENCE_STATUS_CD <> 'ORA_WITHDRAWN'

   AND apae.APPROVAL_STATUS_CD = 'APPROVED'

       AND abt.name = 'Tentative Leave Plan'

       AND pps.person_id = papf.person_id

       AND papf.person_id = ppnf.person_id

       AND papf.person_id = pasf_Sup.person_id(+)

       AND papf.person_id = paam.person_id(+)

       AND paam.primary_assignment_flag(+) = 'Y'

       --AND paam.assignment_type = 'E'

       --AND paam.effective_latest_change = 'Y'

       AND paam.job_id = pjft.job_id (+)

       AND paam.grade_id = pgft.grade_id (+)

   AND paam.business_unit_id = bu.organization_id

       AND paam.legal_entity_id = le.organization_id

   AND resp.person_id = HRC_SESSION_UTIL.get_user_personid

       AND resp.responsibility_type IN ('Sector Head', 'Department Head')

       AND resp.status = 'Active'

       --AND  resp.legal_entity_id = paam.legal_entity_id

   --AND resp.BUSINESS_UNIT_ID=paam.BUSINESS_UNIT_ID

   AND resp.organization_id =paam.organization_id 

       AND TRUNC(sysdate) BETWEEN resp.start_date AND NVL(resp.end_date,TRUNC(sysdate))

       AND Trunc(SYSDATE) BETWEEN pjft.effective_start_date(+) AND

                                  pjft.effective_end_date(+)

       AND Trunc(SYSDATE) BETWEEN pgft.effective_start_date(+) AND

                                  pgft.effective_end_date(+)

       AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND

                                  paam.effective_end_date

       AND Trunc(SYSDATE) BETWEEN abt.effective_start_date AND

                                  abt.effective_end_date

       AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND

                                  papf.effective_end_date

       AND Trunc(SYSDATE) BETWEEN ppnf.effective_start_date AND

                                  ppnf.effective_end_date

       AND ppnf.name_type = 'GLOBAL'

      --AND papf.person_number IN ( '0015', '0033' )

  AND to_number(Extract(year FROM apae.start_date)) = to_number(:p_date)  

)

   WHERE 1=1

    AND (LEAST(:p_sector) IS NULL

OR  trim(sector) IN trim( :p_sector ))

--AND ( (coalesce(null, :p_sector) is null) or (trim(sector) in trim(:p_sector)))

AND (LEAST(:p_dept) IS NULL

OR  department_name IN ( :p_dept ))

--ORDER  BY department_name,order_by_date

)

SELECT mq.person_id,

       mq.person_number,

       mq.employee_name,

       mq.manager_name,

       mq.job_name,

       mq.grade_name,

       --abt.absence_type_id,

       mq.absence_type,

   mq.sector,

   mq.unit,

      mq.department_name,

       To_char(mq.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

   start_date,

       To_char(mq.end_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

   end_date,

       mq.duration,

   mq.order_by_date,

   mq.Delegated_Person,

   (SELECT LISTAGG(mq1.person_number, ', ') WITHIN GROUP (ORDER BY mq1.person_number)

   FROM Main_Q mq1

   WHERE mq.person_id!=mq1.person_id

   AND (TRUNC(mq.start_date) BETWEEN TRUNC(mq1.start_date) AND TRUNC(mq1.end_date)

   OR TRUNC(mq1.start_date) BETWEEN TRUNC(mq.start_date) AND TRUNC(mq.end_date)

   )

   ) overlap_emp

from Main_Q mq

Generate Empty File in BI Publisher Bursting sql returns no data

 Currently there is no supported method for that. The workaround is to make sure that your sql will always return at least one record.

You could accomplish this by adding a union to your sql that will always return one row if the main sql returns no data.

We have provided a sample select that you may use as guidance:

We have modified the Data Model main sql in such a way that it will always return a record when the main sql returns no data. This will prevent the report job from failing and, at the same time, the bursting will not send out the email out as the party_site_id is equals to -999, which does not meet the bursting sql criteria.

SELECT s.vendor_id,

  s.vendor_name,

  ps.party_site_name ,

  ps.party_site_id

FROM fusion.poz_suppliers_v s,

  fusion.hz_party_sites ps

WHERE s.party_id = ps.party_id

UNION

SELECT 1,

  '2' ,

  '3' ,

  -99999

FROM dual

WHERE NOT EXISTS

  (SELECT 1

  FROM fusion.poz_suppliers_v s,

    fusion.hz_party_sites ps

  WHERE s.party_id = ps.party_id

  );

Reference link : Doc ID 1925999.1