Oracle EBs Apps Blog of Mahfuz, OPM and OBIEE Consultant

Saturday 15 April 2017

GL Balance Update if necessary

GL Balance Update if necessary

---------=======if new DR Balance >=0  and (fcb.FC_BAL - ((gb.BEGIN_BALANCE_DR-gb.BEGIN_BALANCE_CR)+(gb.PERIOD_NET_DR-gb.PERIOD_NET_CR)))+(gb.PERIOD_NET_DR)>=0=============
--       ===================================
update gl_balances t set period_net_dr = (
select new_dr totalupdt_BAL from
 (
SELECT gcc.code_combination_id, fcb.erp_bal, fcb.fc_bal,
       (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)
       ) begin_bal,
       (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0)) period_bal,
       (  (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0))
        + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
       ) old_erp_bal,
       NVL ((  NVL (fcb.fc_bal, 0)
             - (  (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)
                  )
                + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
               )
            ),
            0
           ) diff_fc_erp,
        ( NVL ((  NVL (fcb.fc_bal, 0)
               - (  (  NVL (gb.begin_balance_dr, 0)
                     - NVL (gb.begin_balance_cr, 0)
                    )
                  + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
                 )
              ),
              0
             )
       + (NVL (gb.period_net_dr, 0))
       )
        new_dr,
       (NVL (gb.period_net_cr, 0)) erp_period_cr,
       (  (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)
          )                                                       -- begin_bal
        + NVL ((  NVL (fcb.fc_bal, 0)
                - (  (  NVL (gb.begin_balance_dr, 0)
                      - NVL (gb.begin_balance_cr, 0)
                     )
                   + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
                  )
               ),
               0
              )
        + (NVL (gb.period_net_dr, 0))                                --new_dr,
        - (NVL (gb.period_net_cr, 0))                                 --ERP_CR
       ) present_bal,
         (NVL (fcb.fc_bal, 0))
       - (  (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)
            )                                                     -- begin_bal
          + NVL ((  NVL (fcb.fc_bal, 0)
                  - (  (  NVL (gb.begin_balance_dr, 0)
                        - NVL (gb.begin_balance_cr, 0)
                       )
                     + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0)
                       )
                    )
                 ),
                 0
                )
          + (NVL (gb.period_net_dr, 0))                              --new_dr,
          - (NVL (gb.period_net_cr, 0))                               --ERP_CR
         ) new_fc_erp_diff,
       NVL (  (  NVL (fcb.fc_bal, 0)
               - (  (  NVL (gb.begin_balance_dr, 0)
                     - NVL (gb.begin_balance_cr, 0)
                    )
                  + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
                 )
              )
            + (NVL (gb.period_net_dr, 0)),
            0
           ) new_totalupdt_bal,
         (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)
         )
       + NVL (  (  NVL (fcb.fc_bal, 0)
                 - (  (  NVL (gb.begin_balance_dr, 0)
                       - NVL (gb.begin_balance_cr, 0)
                      )
                    + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
                   )
                )
              + (NVL (gb.period_net_dr, 0)),
              0
             )
       + (NVL (gb.period_net_cr, 0)) new_bal,
       ((gb.begin_balance_dr - gb.begin_balance_cr) + (gb.period_net_dr)
       ) totalupdt_bal,
       gb.period_name, gcc.segment1 ou, gb.currency_code, gb.begin_balance_dr,
       gb.begin_balance_cr, gb.period_net_dr, gb.period_net_cr,
         (gb.period_net_dr - gb.period_net_cr)
       + (gb.begin_balance_dr - gb.begin_balance_cr) net_balance,
          gcc.segment1
       || '-'
       || gcc.segment2
       || '-'
       || gcc.segment3
       || '-'
       || gcc.segment4
       || '-'
       || gcc.segment5
       || '-'
       || gcc.segment6
       || '-'
       || gcc.segment7
       || '-'
       || gcc.segment8
       || '-'
       || gcc.segment9
       || '-'
       || gcc.segment10 code_combination     --, ffvt.DESCRIPTION ACCOUNT_NAME
  FROM gl_balances gb,
       gl_code_combinations gcc,
       fnd_flex_values ffv,
       fnd_flex_values_tl ffvt,
       xxgl_bal_updt fcb
 WHERE gb.period_name IN ('Oct-16-17')
--and gcc.segment6 in (select headid from xxprg_bsreport where gcc.segment6 = xx.HEADID)
 --and gcc.SEGMENT1 in ('128')
   AND gcc.code_combination_id IN (
          SELECT g.code_combination_id
            FROM gl_code_combinations g                  --, xxgl_bal_updt fcb
           WHERE g.segment1 =
                    :fst
--and g.SEGMENT6=:xth  --and g.SEGMENT1=fcb.SUGMENT1 and g.segment6=fcb.NATURAL_CODE
                        )
   AND gb.currency_code LIKE 'BDT'
   AND gb.code_combination_id = gcc.code_combination_id
   AND ffv.flex_value_set_id = 1014876
   AND gcc.segment6 = ffv.flex_value
   AND ffv.flex_value_id = ffvt.flex_value_id
--and gcc.CODE_COMBINATION_ID in (1069080,836369)
   AND gcc.segment1 = fcb.sugment1
   AND gcc.segment6 = fcb.natural_code
   AND    gcc.segment1
       || '-'
       || gcc.segment2
       || '-'
       || gcc.segment3
       || '-'
       || gcc.segment4
       || '-'
       || gcc.segment5
       || '-'
       || gcc.segment6
       || '-'
       || gcc.segment7
       || '-'
       || gcc.segment8
       || '-'
       || gcc.segment9
       || '-'
       || gcc.segment10 = fcb.acct_code
   AND fcb.fc_bal <>
            (gb.period_net_dr - gb.period_net_cr)
          + (gb.begin_balance_dr - gb.begin_balance_cr)
   AND NVL (  (  NVL (fcb.fc_bal, 0)
               - (  (  NVL (gb.begin_balance_dr, 0)
                     - NVL (gb.begin_balance_cr, 0)
                    )
                  + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
                 )
              )
            + (NVL (gb.period_net_dr, 0)),
            0
           ) >= 0
) kkk
 where t.CURRENCY_CODE like 'BDT' and kkk.period_name=t.period_name and kkk.period_name in ('Oct-16-17') and t.CODE_COMBINATION_ID=kkk.CODE_COMBINATION_ID
 )
where exists
 (
   SELECT gcc.code_combination_id, fcb.erp_bal, fcb.fc_bal,
       (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)
       ) begin_bal,
       (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0)) period_bal,
       (  (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0))
        + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
       ) old_erp_bal,
       NVL ((  NVL (fcb.fc_bal, 0)
             - (  (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)
                  )
                + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
               )
            ),
            0
           ) diff_fc_erp,
        ( NVL ((  NVL (fcb.fc_bal, 0)
               - (  (  NVL (gb.begin_balance_dr, 0)
                     - NVL (gb.begin_balance_cr, 0)
                    )
                  + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
                 )
              ),
              0
             )
       + (NVL (gb.period_net_dr, 0))
       )
        new_dr,
       (NVL (gb.period_net_cr, 0)) erp_period_cr,
       (  (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)
          )                                                       -- begin_bal
        + NVL ((  NVL (fcb.fc_bal, 0)
                - (  (  NVL (gb.begin_balance_dr, 0)
                      - NVL (gb.begin_balance_cr, 0)
                     )
                   + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
                  )
               ),
               0
              )
        + (NVL (gb.period_net_dr, 0))                                --new_dr,
        - (NVL (gb.period_net_cr, 0))                                 --ERP_CR
       ) present_bal,
         (NVL (fcb.fc_bal, 0))
       - (  (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)
            )                                                     -- begin_bal
          + NVL ((  NVL (fcb.fc_bal, 0)
                  - (  (  NVL (gb.begin_balance_dr, 0)
                        - NVL (gb.begin_balance_cr, 0)
                       )
                     + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0)
                       )
                    )
                 ),
                 0
                )
          + (NVL (gb.period_net_dr, 0))                              --new_dr,
          - (NVL (gb.period_net_cr, 0))                               --ERP_CR
         ) new_fc_erp_diff,
       NVL (  (  NVL (fcb.fc_bal, 0)
               - (  (  NVL (gb.begin_balance_dr, 0)
                     - NVL (gb.begin_balance_cr, 0)
                    )
                  + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
                 )
              )
            + (NVL (gb.period_net_dr, 0)),
            0
           ) new_totalupdt_bal,
         (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)
         )
       + NVL (  (  NVL (fcb.fc_bal, 0)
                 - (  (  NVL (gb.begin_balance_dr, 0)
                       - NVL (gb.begin_balance_cr, 0)
                      )
                    + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
                   )
                )
              + (NVL (gb.period_net_dr, 0)),
              0
             )
       + (NVL (gb.period_net_cr, 0)) new_bal,
       ((gb.begin_balance_dr - gb.begin_balance_cr) + (gb.period_net_dr)
       ) totalupdt_bal,
       gb.period_name, gcc.segment1 ou, gb.currency_code, gb.begin_balance_dr,
       gb.begin_balance_cr, gb.period_net_dr, gb.period_net_cr,
         (gb.period_net_dr - gb.period_net_cr)
       + (gb.begin_balance_dr - gb.begin_balance_cr) net_balance,
          gcc.segment1
       || '-'
       || gcc.segment2
       || '-'
       || gcc.segment3
       || '-'
       || gcc.segment4
       || '-'
       || gcc.segment5
       || '-'
       || gcc.segment6
       || '-'
       || gcc.segment7
       || '-'
       || gcc.segment8
       || '-'
       || gcc.segment9
       || '-'
       || gcc.segment10 code_combination     --, ffvt.DESCRIPTION ACCOUNT_NAME
  FROM gl_balances gb,
       gl_code_combinations gcc,
       fnd_flex_values ffv,
       fnd_flex_values_tl ffvt,
       xxgl_bal_updt fcb
 WHERE gb.period_name IN ('Oct-16-17')
--and gcc.segment6 in (select headid from xxprg_bsreport where gcc.segment6 = xx.HEADID)
 --and gcc.SEGMENT1 in ('128')
   AND gcc.code_combination_id IN (
          SELECT g.code_combination_id
            FROM gl_code_combinations g                  --, xxgl_bal_updt fcb
           WHERE g.segment1 =
                    :fst
--and g.SEGMENT6=:xth  --and g.SEGMENT1=fcb.SUGMENT1 and g.segment6=fcb.NATURAL_CODE
                        )
   AND gb.currency_code LIKE 'BDT'
   AND gb.code_combination_id = gcc.code_combination_id
   AND ffv.flex_value_set_id = 1014876
   AND gcc.segment6 = ffv.flex_value
   AND ffv.flex_value_id = ffvt.flex_value_id
--and gcc.CODE_COMBINATION_ID in (1069080,836369)
   AND gcc.segment1 = fcb.sugment1
   AND gcc.segment6 = fcb.natural_code
   AND    gcc.segment1
       || '-'
       || gcc.segment2
       || '-'
       || gcc.segment3
       || '-'
       || gcc.segment4
       || '-'
       || gcc.segment5
       || '-'
       || gcc.segment6
       || '-'
       || gcc.segment7
       || '-'
       || gcc.segment8
       || '-'
       || gcc.segment9
       || '-'
       || gcc.segment10 = fcb.acct_code
   AND fcb.fc_bal <>
            (gb.period_net_dr - gb.period_net_cr)
          + (gb.begin_balance_dr - gb.begin_balance_cr)
   AND NVL (  (  NVL (fcb.fc_bal, 0)
               - (  (  NVL (gb.begin_balance_dr, 0)
                     - NVL (gb.begin_balance_cr, 0)
                    )
                  + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
                 )
              )
            + (NVL (gb.period_net_dr, 0)),
            0
           ) >= 0
and t.CURRENCY_CODE like 'BDT' and gb.period_name=t.period_name and gb.period_name in ('Oct-16-17') and t.CODE_COMBINATION_ID=gb.CODE_COMBINATION_ID
)
;






---------=======if New DR Balance <0 
---------==== and (fcb.FC_BAL - ((gb.BEGIN_BALANCE_DR-gb.BEGIN_BALANCE_CR)+(gb.PERIOD_NET_DR-gb.PERIOD_NET_CR)))+(gb.PERIOD_NET_DR)<0=============
--       ===================================
update gl_balances t set period_net_Cr = (
select (new_CR) totalupdt_BAL from
 (
 SELECT gcc.code_combination_id, fcb.erp_bal, fcb.fc_bal,
       (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0) ) begin_bal,
       (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0)) period_bal,
   ((NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)) + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0)) ) old_erp_bal,
       (NVL ((  NVL (fcb.fc_bal, 0)  - (  (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)  ) + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0)) )
            ), 0 )
         ) diff_fc_erp, NVL (gb.period_net_dr, 0) new_dr,
         (
         -1*(NVL ((  NVL (fcb.fc_bal, 0)  - (  (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)  ) + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0)) )
            ), 0 ) )       
         +NVL (gb.period_net_Cr, 0)) new_CR,
       ((NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0) ) -- Begin_bal
        +
        (
        NVL (gb.period_net_dr, 0) -(
         -1*(NVL ((  NVL (fcb.fc_bal, 0)  - (  (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)  ) + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0)) )
            ), 0 ) )       
         +NVL (gb.period_net_Cr, 0))
        )
        ) present_bal,
         ((NVL (fcb.fc_bal, 0))
       -
       ((NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0) ) -- Begin_bal
        +
        (
        NVL (gb.period_net_dr, 0) -(
         -1*(NVL ((  NVL (fcb.fc_bal, 0)  - (  (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)  ) + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0)) )
            ), 0 ) )       
         +NVL (gb.period_net_Cr, 0))
        )
        )
       ) new_fc_erp_diff,
       NVL (  (  NVL (fcb.fc_bal, 0)
               - (  (  NVL (gb.begin_balance_dr, 0)
                     - NVL (gb.begin_balance_cr, 0)
                    )
                  + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
                 )
              )
            + (NVL (gb.period_net_dr, 0)),
            0
           ) new_totalupdt_bal,
           ((NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)  )
          + (NVL (gb.period_net_dr, 0))) new_bal,        
       ((gb.begin_balance_dr - gb.begin_balance_cr) + (gb.period_net_dr)
       ) totalupdt_bal,
       gb.period_name, gcc.segment1 ou, gb.currency_code, gb.begin_balance_dr,
       gb.begin_balance_cr, gb.period_net_dr, gb.period_net_cr,
         (gb.period_net_dr - gb.period_net_cr)
       + (gb.begin_balance_dr - gb.begin_balance_cr) net_balance,
          gcc.segment1
       || '-'
       || gcc.segment2
       || '-'
       || gcc.segment3
       || '-'
       || gcc.segment4
       || '-'
       || gcc.segment5
       || '-'
       || gcc.segment6
       || '-'
       || gcc.segment7
       || '-'
       || gcc.segment8
       || '-'
       || gcc.segment9
       || '-'
       || gcc.segment10 code_combination     --, ffvt.DESCRIPTION ACCOUNT_NAME
  FROM gl_balances gb,
       gl_code_combinations gcc,
       fnd_flex_values ffv,
       fnd_flex_values_tl ffvt,
       xxgl_bal_updt fcb
 WHERE gb.period_name IN ('Oct-16-17')
--and gcc.segment6 in (select headid from xxprg_bsreport where gcc.segment6 = xx.HEADID)
 --and gcc.SEGMENT1 in ('128')
   AND gcc.code_combination_id IN (
          SELECT g.code_combination_id
            FROM gl_code_combinations g                  --, xxgl_bal_updt fcb
           WHERE g.segment1 =
                    :fst
--and g.SEGMENT6=:xth  --and g.SEGMENT1=fcb.SUGMENT1 and g.segment6=fcb.NATURAL_CODE
                        )
   AND gb.currency_code LIKE 'BDT'
   AND gb.code_combination_id = gcc.code_combination_id
   AND ffv.flex_value_set_id = 1014876
   AND gcc.segment6 = ffv.flex_value
   AND ffv.flex_value_id = ffvt.flex_value_id
--and gcc.CODE_COMBINATION_ID in (1069080,836369)
   AND gcc.segment1 = fcb.sugment1
   AND gcc.segment6 = fcb.natural_code
   AND    gcc.segment1
       || '-'
       || gcc.segment2
       || '-'
       || gcc.segment3
       || '-'
       || gcc.segment4
       || '-'
       || gcc.segment5
       || '-'
       || gcc.segment6
       || '-'
       || gcc.segment7
       || '-'
       || gcc.segment8
       || '-'
       || gcc.segment9
       || '-'
       || gcc.segment10 = fcb.acct_code
   AND fcb.fc_bal <>
            (gb.period_net_dr - gb.period_net_cr)
          + (gb.begin_balance_dr - gb.begin_balance_cr)
   AND NVL (  (  NVL (fcb.fc_bal, 0)
               - (  (  NVL (gb.begin_balance_dr, 0)
                     - NVL (gb.begin_balance_cr, 0)
                    )
                  + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
                 )
              )
            + (NVL (gb.period_net_dr, 0)),
            0
           ) < 0
) kkk
 where t.CURRENCY_CODE like 'BDT' and kkk.period_name=t.period_name and kkk.period_name in ('Oct-16-17') and t.CODE_COMBINATION_ID=kkk.CODE_COMBINATION_ID
 )
where exists
 (
 SELECT gcc.code_combination_id, fcb.erp_bal, fcb.fc_bal,
       (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0) ) begin_bal,
       (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0)) period_bal,
   ((NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)) + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0)) ) old_erp_bal,
       (NVL ((  NVL (fcb.fc_bal, 0)  - (  (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)  ) + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0)) )
            ), 0 )
         ) diff_fc_erp, NVL (gb.period_net_dr, 0) new_dr,
         (
         -1*(NVL ((  NVL (fcb.fc_bal, 0)  - (  (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)  ) + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0)) )
            ), 0 ) )       
         +NVL (gb.period_net_Cr, 0)) new_CR,
       ((NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0) ) -- Begin_bal
        +
        (
        NVL (gb.period_net_dr, 0) -(
         -1*(NVL ((  NVL (fcb.fc_bal, 0)  - (  (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)  ) + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0)) )
            ), 0 ) )       
         +NVL (gb.period_net_Cr, 0))
        )
        ) present_bal,
         ((NVL (fcb.fc_bal, 0))
       -
       ((NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0) ) -- Begin_bal
        +
        (
        NVL (gb.period_net_dr, 0) -(
         -1*(NVL ((  NVL (fcb.fc_bal, 0)  - (  (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)  ) + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0)) )
            ), 0 ) )       
         +NVL (gb.period_net_Cr, 0))
        )
        )
       ) new_fc_erp_diff,
       NVL (  (  NVL (fcb.fc_bal, 0)
               - (  (  NVL (gb.begin_balance_dr, 0)
                     - NVL (gb.begin_balance_cr, 0)
                    )
                  + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
                 )
              )
            + (NVL (gb.period_net_dr, 0)),
            0
           ) new_totalupdt_bal,
           ((NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0)  )
          + (NVL (gb.period_net_dr, 0))) new_bal,        
       ((gb.begin_balance_dr - gb.begin_balance_cr) + (gb.period_net_dr)
       ) totalupdt_bal,
       gb.period_name, gcc.segment1 ou, gb.currency_code, gb.begin_balance_dr,
       gb.begin_balance_cr, gb.period_net_dr, gb.period_net_cr,
         (gb.period_net_dr - gb.period_net_cr)
       + (gb.begin_balance_dr - gb.begin_balance_cr) net_balance,
          gcc.segment1
       || '-'
       || gcc.segment2
       || '-'
       || gcc.segment3
       || '-'
       || gcc.segment4
       || '-'
       || gcc.segment5
       || '-'
       || gcc.segment6
       || '-'
       || gcc.segment7
       || '-'
       || gcc.segment8
       || '-'
       || gcc.segment9
       || '-'
       || gcc.segment10 code_combination     --, ffvt.DESCRIPTION ACCOUNT_NAME
  FROM gl_balances gb,
       gl_code_combinations gcc,
       fnd_flex_values ffv,
       fnd_flex_values_tl ffvt,
       xxgl_bal_updt fcb
 WHERE gb.period_name IN ('Oct-16-17')
--and gcc.segment6 in (select headid from xxprg_bsreport where gcc.segment6 = xx.HEADID)
 --and gcc.SEGMENT1 in ('128')
   AND gcc.code_combination_id IN (
          SELECT g.code_combination_id
            FROM gl_code_combinations g                  --, xxgl_bal_updt fcb
           WHERE g.segment1 =
                    :fst
--and g.SEGMENT6=:xth  --and g.SEGMENT1=fcb.SUGMENT1 and g.segment6=fcb.NATURAL_CODE
                        )
   AND gb.currency_code LIKE 'BDT'
   AND gb.code_combination_id = gcc.code_combination_id
   AND ffv.flex_value_set_id = 1014876
   AND gcc.segment6 = ffv.flex_value
   AND ffv.flex_value_id = ffvt.flex_value_id
--and gcc.CODE_COMBINATION_ID in (1069080,836369)
   AND gcc.segment1 = fcb.sugment1
   AND gcc.segment6 = fcb.natural_code
   AND    gcc.segment1
       || '-'
       || gcc.segment2
       || '-'
       || gcc.segment3
       || '-'
       || gcc.segment4
       || '-'
       || gcc.segment5
       || '-'
       || gcc.segment6
       || '-'
       || gcc.segment7
       || '-'
       || gcc.segment8
       || '-'
       || gcc.segment9
       || '-'
       || gcc.segment10 = fcb.acct_code
   AND fcb.fc_bal <>
            (gb.period_net_dr - gb.period_net_cr)
          + (gb.begin_balance_dr - gb.begin_balance_cr)
   AND NVL (  (  NVL (fcb.fc_bal, 0)
               - (  (  NVL (gb.begin_balance_dr, 0)
                     - NVL (gb.begin_balance_cr, 0)
                    )
                  + (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
                 )
              )
            + (NVL (gb.period_net_dr, 0)),
            0
           ) < 0
and t.CURRENCY_CODE like 'BDT' and gb.period_name=t.period_name and gb.period_name in ('Oct-16-17') and t.CODE_COMBINATION_ID=gb.CODE_COMBINATION_ID
);


 Custom Table :
Insert into XXGL_BAL_UPDT
   (SUGMENT1, ACCT_CODE, ERP_BAL, FC_BAL, NATURAL_CODE)
 Values
   ('123', '123-000-000-0000-00000-220034-108-000-00000-00000', 121000000, 0, '220034');
COMMIT;







at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Internal location is Already assigned to another Ship-to Address

      SELECT *   FROM hz_cust_accounts  WHERE ACCOUNT_NUMBER = 'R08'; -- CUST_ACCOUNT_ID =51401 , PARTY_ID= 235048    SELECT *   FRO...