Report : GL JE Created After Last Revaluation Report

------GL JE Created After Last Revaluation Report

---Report Parameters 

1. Period 
2. Global Account
3. Excluded JPN  Y/N (Union of 2 queries and it will switch with selected parameter value)

Report Logic--Main Query

SELECT Al1.Period_Mon_Yy period,

       Al1.Ledger_Name,

       Al1.Company co,

       Al1.Batch_Name,

       Al1.Batch_User_Name,

       Al1.Hdr_Creation_Date,

       Al1.Hdr_Posted_Date,

       Al1.Source_Name,

       Al1.Category_Name,

       Al1.Account_Combination acct_cd_comb,

       Al1.Je_Line_Num,

       Al1.Je_Line_Description,

       Al1.Je_Curr_Code,

       Al1.Entered_Net,

       Al1.Converted_Net,

sysdate timestamp

  FROM (SELECT Glh.Ledger_Id Ledger_Id,

               Glldg.Name Ledger_Name,

               Glh.Period_Name Period_Mon_Yy,

               Glcc.Segment1 Company,

               Glcc.Segment3 Glbl_Acct,

               Glcc.Concatenated_Segments Account_Combination,

               Glb.Name Batch_Name,

               Glb.Description Batch_Description,

               Glb.Creation_Date Batch_Creation_Date,

               Fndu.User_id Batch_User_Id,

               Fndu.Username Batch_User_Name,

               Glh.Last_Update_Date Hdr_Last_Updt_Date,

               Glh.Creation_Date Hdr_Creation_Date,

               Glh.Default_Effective_Date Accounting_Date,

               Glh.Name Header_Name,

               Glh.Description Header_Description,

               Glh.Status Je_Status_Flag,

               Glb.Posted_Date Batch_Posted_Date,

               Glh.Posted_Date Hdr_Posted_Date,

               Gljs.User_Je_Source_Name Source_Name,

               Gljc.User_Je_Category_Name Category_Name,

               Gll.Je_Line_Num Je_Line_Num,

               Gll.Description Je_Line_Description,

               Gll.Line_Type_Code Je_Line_Type,

               Gll.Creation_Date Je_Line_Creation_Date,

               Gll.Last_Update_Date Je_Line_Last_Updt_Date,

               Gll.Effective_Date Je_Line_Effective_Date,

               Gll.Status Je_Line_Status,

               Glldg.Currency_Code Ledger_Curr,

               Glh.Currency_Code Je_Curr_Code,

               Gll.Entered_Dr Entered_Dr,

               Gll.Entered_Cr Entered_Cr,

               Nvl(Gll.Entered_Dr, 0) - Nvl(Gll.Entered_Cr, 0) Entered_Net,

               Gll.Accounted_Dr Converted_Dr,

               Gll.Accounted_Cr Converted_Cr,

               Nvl(Gll.Accounted_Dr, 0) - Nvl(Gll.Accounted_Cr, 0) Converted_Net,

               Glh.Je_Batch_Id Je_Batch_Id

        

          FROM Gl_Ledgers               Glldg,

               Gl_Je_Headers            Glh,

               Gl_Je_Lines              Gll,

               Gl_Code_Combinations Glcc,

               Gl_Je_Batches            Glb,

               per_users                 Fndu,

               Gl_Daily_Rates           Rat,

               Gl_Daily_Rates           Rtj,

               Gl_Periods               Glp,

               Gl_Je_Sources_Tl         Gljs,

               Gl_Je_Categories_Tl      Gljc

         WHERE Glh.Ledger_Id = Glldg.Ledger_Id

           AND Glh.Je_Header_Id = Gll.Je_Header_Id

           AND Gll.Code_Combination_Id = Glcc.Code_Combination_Id

           AND Glh.Je_Batch_Id = Glb.Je_Batch_Id

           AND Glb.Created_By = Fndu.Username

           AND Rat.To_Currency(+) = 'USD'

           AND Rat.From_Currency(+) = Glh.Currency_Code

           AND Rat.Conversion_Date(+) = Glh.Default_Effective_Date

           AND Rat.Conversion_Type(+) = 'Corporate'

           AND Rtj.To_Currency(+) = 'USD'

           AND Rtj.From_Currency(+) = 'JPY'

           AND Rtj.Conversion_Date(+) = Glh.Default_Effective_Date

           AND Rtj.Conversion_Type(+) = 'Corporate'

           AND Glh.Period_Name = Glp.Period_Name

           AND Glp.Period_Set_Name = '4-4-5'

   AND Glp.Period_Type = 'MONTH8731158251'

           AND Glh.Je_Source = Gljs.Je_Source_Name

           AND Gljs.Language = 'US'

           AND Glh.Je_Category = Gljc.Je_Category_Name

           AND Gljc.Language = 'US'

          -- AND Substr(Glldg.Name, 5, 3) = 'USD'

           AND Glldg.Ledger_Category_Code <> 'SECONDARY'

           AND Glb.Status = 'P'

           AND Glh.Currency_Code NOT IN ('USD', 'STAT')

          -- AND Glh.Ledger_Id NOT IN (3108, 3111)

   AND Glldg.name  NOT IN ('JPN USD PL')

AND ( case when Glh.Period_Name in (:p_period_name) then 1

when (COALESCE(NULL,:p_period_name) is NULL ) then 1

end = 1 )

  

   --AND Glh.Period_Name= nvl(:p_period_name,Glh.Period_Name)

   --AND Glcc.Segment3= nvl(:p_Segment3,Glcc.Segment2)

AND ( case when Glcc.Segment3 in (:p_Segment3) then 1

when (COALESCE(NULL,:p_Segment3) is NULL ) then 1

end = 1 )

and :p_excl_jpn = 'Y'

  AND Glh.Posted_Date >

               (SELECT MAX(Hrev.Posted_Date)

                  FROM Gl_Je_Headers Hrev,gl_ledgers gld

                 WHERE Hrev.Period_Name = Glh.Period_Name

                   AND Hrev.Ledger_Id = Glh.Ledger_Id

                   AND Hrev.Je_Source = 'Revaluation'

                   AND Hrev.Currency_Code = Glh.Currency_Code

                  -- AND Hrev.Ledger_Id NOT IN (3108, 3111)

   AND Hrev.ledger_id=gld.ledger_id

   AND Gld.name  NOT IN ('JPN USD PL')

                   AND Hrev.Reversed_Je_Header_Id IS NULL)

   ) Al1

   

UNION

SELECT Al1.Period_Mon_Yy period,

       Al1.Ledger_Name,

       Al1.Company co,

       Al1.Batch_Name,

       Al1.Batch_User_Name,

       Al1.Hdr_Creation_Date,

       Al1.Hdr_Posted_Date,

       Al1.Source_Name,

       Al1.Category_Name,

       Al1.Account_Combination acct_cd_comb,

       Al1.Je_Line_Num,

       Al1.Je_Line_Description,

       Al1.Je_Curr_Code,

       Al1.Entered_Net,

       Al1.Converted_Net,

sysdate timestamp       

  FROM (SELECT glh.ledger_id                                            Ledger_ID,

       glldg.name                                               Ledger_Name,

       glh.period_name                                          Period_MON_YY,

       glcc.segment1                                            Company,

       glcc.segment2                                            Location,   

       glcc.segment3                                            Glbl_Acct,

       glcc.segment4                                            Lcl_Acct,

       glcc.segment5                                            Dept,

       glcc.segment7                                            Intco,

       glcc.segment8                                            Project,

       glcc.segment6                                            BU,

       glcc.segment9                                            Future,

       GLCC.SEGMENT1||'.'||GLCC.segment2||'.'||GLCC.segment3||'.'||GLCC.segment4||'.'||

       GLCC.segment5||'.'||GLCC.segment7||'.'||GLCC.segment8||'.'||

       GLCC.segment6||'.'||GLCC.segment9                        Account_Combination,  

       GLCC.SEGMENT1||'.'||GLCC.segment5                        Co_Dept, 

       glb.name                                                 Batch_Name,

       glb.description                                          Batch_Description,

       glb.creation_date                                         Batch_Creation_Date,

       fndu.user_id                                            Batch_User_ID,

       fndu.Username                                          Batch_User_Name,

       glh.last_update_date                                      Hdr_Last_Updt_Date,

       fndu1.user_id                                           Hdr_Last_Updt_User_ID,

       fndu1.username                                       Hdr_Last_Updt_User_Name,

       glh.creation_date                                         Hdr_Creation_Date,

       fndu2.user_id                                           Hdr_Created_User_ID,

       fndu2.Username                                        Hdr_Created_User_Name,

       glh.default_effective_date                                Accounting_Date,

       glh.name                                                  Header_Name,

       glh.description                                           Header_Description,

       glh.currency_conversion_rate                              Curr_Conv_Rate,

       glh.status                                                JE_Status_Flag,

       glb.posted_date                                           Batch_Posted_Date,

       glh.posted_date                                           Hdr_Posted_Date,

       gljs.user_je_source_name                                  Source_Name,

       gljc.user_je_category_name                                Category_Name,                   

       gll.je_line_num                                           JE_Line_Num,

       gll.description                                           JE_Line_Description,

       gll.line_type_code                                        JE_Line_Type,

       gll.creation_date                                         JE_Line_Creation_Date,

       fndu3.user_id                                           JE_Line_Created_ID,

       fndu3.username                                         JE_Line_Created_Name,

       gll.last_update_date                                      JE_Line_Last_Updt_Date,

       fndu4.user_id                                          JE_Line_Last_Updt_ID,

       fndu4.username                                         JE_Line_Last_Updt_Name,

       gll.effective_date                                        JE_Line_Effective_Date,

       gll.status                                                JE_Line_Status,

       glldg.currency_code                                       Ledger_Curr,

       glh.currency_code                                         JE_Curr_Code,

       gll.entered_dr                                            Entered_Dr,

       gll.entered_cr                                            Entered_Cr,

       NVL(gll.entered_dr,0) - NVL(gll.entered_cr,0)             Entered_Net,

       gll.accounted_dr                                          Converted_Dr,

       gll.accounted_cr                                          Converted_Cr,

       NVL(gll.accounted_dr,0) - NVL(gll.accounted_cr,0)         Converted_Net,

       CASE WHEN (SUBSTR(glldg.name,5,3) = 'USD' OR glldg.ledger_id = 2235) THEN gll.accounted_dr

            WHEN glcc.segment1 between '601' and '699' 

            THEN round((gll.accounted_dr * rtj.conversion_rate),2)

            WHEN glh.currency_code = 'USD'   THEN gll.entered_dr

            ELSE ROUND((gll.entered_dr * rat.conversion_rate),2)

       END                                                       Converted_Dr_USD,

       CASE WHEN (SUBSTR(glldg.name,5,3) = 'USD' OR glldg.ledger_id = 2235) THEN gll.accounted_cr

            WHEN glcc.segment1 between '601' and '699' 

            THEN round((gll.accounted_cr * rtj.conversion_rate),2)

            WHEN glh.currency_code = 'USD'   THEN gll.entered_cr

            ELSE ROUND((gll.entered_cr * rat.conversion_rate),2)

       END                                                       Converted_Cr_USD,

       NVL(CASE WHEN (SUBSTR(glldg.name,5,3) = 'USD' OR glldg.ledger_id = 2235) THEN gll.accounted_dr

                WHEN glcc.segment1 between '601' and '699' 

                THEN round((gll.accounted_dr * rtj.conversion_rate),2)

                WHEN glh.currency_code = 'USD'   THEN gll.entered_dr

                ELSE ROUND((gll.entered_dr * rat.conversion_rate),2)

           END,0) -  

       NVL(CASE WHEN (SUBSTR(glldg.name,5,3) = 'USD' OR glldg.ledger_id = 2235) THEN gll.accounted_cr

                WHEN glcc.segment1 between '601' and '699' 

                THEN round((gll.accounted_cr * rtj.conversion_rate),2)

                WHEN glh.currency_code = 'USD'   THEN gll.entered_cr

                ELSE ROUND((gll.entered_cr * rat.conversion_rate),2)

           END,0)                                                Converted_Net_USD,

       rat.conversion_rate                                       Conv_Rate_USD,

       glh.reference_date                                        Reference_Date,

       glh.doc_sequence_value                                    Doc_Sequence,

       glp.period_year                                           Period_YYYY,

       glp.period_num                                            Period_NUM,

       SUBSTR(glh.period_name,1,3)                               Period_MON,

       gll.code_combination_id                                   Code_Combination_ID,

       glh.doc_sequence_id                                       Doc_Sequence_ID,

       glh.je_batch_id                                           JE_Batch_ID,

       nvl(glb.parent_je_batch_id,glb.je_batch_id)               Parent_Batch_ID,

       glh.je_header_id                                          JE_Header_ID,

       glb.request_id                                            Batch_Request_ID ,

       glh.parent_je_header_id           Parent_JE_Header_ID,

       glh.reversed_je_header_id         Reversed_JE_Header_ID,

       glh.currency_conversion_date        Currency_Conversion_Date,

       glh.currency_conversion_type        Currency_Conversion_Type,

       CASE WHEN glh.ACCRUAL_REV_STATUS = 'R'

            THEN 'Reversed'

            ELSE 'Not Reversed'

       END                                                       Reversal_Status,

       glh.accrual_rev_period_name                               Accr_Reversal_Period

FROM gl_ledgers           glldg,

     gl_je_headers        glh,

     gl_je_lines          gll,

     gl_code_combinations glcc,

     gl_je_batches        glb,

     per_users            fndu,

     per_users            fndu1,

    per_users             fndu2,

     per_users            fndu3,

     per_users            fndu4,

     gl_daily_rates       rat,

     gl_daily_rates       rtj,

     gl_periods           glp,

     gl_je_sources_tl     gljs,

     gl_je_categories_tl  gljc

WHERE glh.ledger_id            = glldg.ledger_id

  AND glh.je_header_id         = gll.je_header_id

  AND gll.code_combination_id  = glcc.code_combination_id

  AND glh.je_batch_id          = glb.je_batch_id

  AND glb.created_by           = fndu.username

  AND glh.last_updated_by      = fndu1.username

  AND glh.created_by           = fndu2.username

  AND gll.created_by           = fndu3.username

  AND gll.last_updated_by      = fndu4.username

  AND rat.to_currency(+)       = 'USD'

  AND rat.from_currency(+)     = glh.currency_code

  AND rat.conversion_date(+)   = glh.default_effective_date

  AND rat.conversion_type(+)   = 'Corporate'

  AND rtj.to_currency(+)       = 'USD'

  AND rtj.from_currency(+)     = 'JPY'

  AND rtj.conversion_date(+)   = glh.default_effective_date

  AND rtj.conversion_type(+)   = 'Corporate'

  AND glh.period_name          = glp.period_name

  AND glp.period_set_name      = '4-4-5'

  AND glp.Period_Type          = 'MONTH8731158251'

  AND glh.je_source            = gljs.je_source_name

  AND gljs.language            = 'US'

  AND glh.je_category          = gljc.je_category_name

  AND gljc.language            = 'US'

  AND glb.status               = 'P'             

  and glh.currency_code        NOT IN('JPY','STAT')

  --and glh.ledger_id            IN(2095,2267)

  AND glldg.name               IN ('JPN JPY SL')

   --AND Glh.Period_Name= nvl(:p_period_name,Glh.Period_Name)

AND ( case when Glh.Period_Name in (:p_period_name) then 1

when (COALESCE(NULL,:p_period_name) is NULL ) then 1

end = 1 )

     --AND Glcc.segment3= nvl(:p_segment3,Glcc.segment3)

AND ( case when Glcc.Segment3 in (:p_Segment3) then 1

when (COALESCE(NULL,:p_Segment3) is NULL ) then 1

end = 1 )

and :p_excl_jpn = 'N'

   and glh.posted_date > (select max(hrev.posted_date)

                         from gl_je_headers  hrev,gl_ledgers gld

                         where hrev.period_name   = glh.period_name

                           and hrev.ledger_id     = glh.ledger_id

                           and hrev.je_source     = 'Revaluation'

                           and hrev.currency_code = glh.currency_code

   and Hrev.Ledger_Id     =gld.ledger_id

   -- AND Hrev.Ledger_Id NOT IN  IN(2095,2267)

  -- AND gld.name in  ('JPN JPY SL')

                           and hrev.reversed_je_header_id is null)

   ) Al1