Oracle EBs Apps Blog of Mahfuz, OPM and OBIEE Consultant

Saturday 29 April 2017

Same Item Produced in Multiple Organizations

Same Item Produced in Multiple Organizations

/* Formatted on 2017/04/30 12:11 (Formatter Plus v4.8.8) */
SELECT   subinventory_code, t.transaction_type_id, org.organization_code,
         org.organization_name, h.batch_no, SUM (t.transaction_quantity) qty,
         c.acctg_cost, SUM (t.transaction_quantity * c.acctg_cost) total
    FROM mtl_material_transactions t,
         gl_item_cst c,
         org_organization_definitions org,
         gme_batch_header h
   WHERE t.transaction_type_id IN (33, 37, 43, 44)
     AND t.inventory_item_id =
                   (SELECT inventory_item_id
                      FROM mtl_system_items_b i
                     WHERE i.segment1 = :item_code AND i.organization_id = 102)
     AND TRUNC (t.transaction_date) >= c.start_date
     AND TRUNC (t.transaction_date) <= c.end_date
     AND t.inventory_item_id = c.inventory_item_id
     AND t.organization_id = c.organization_id
     AND c.period_id = :period_id
     AND t.organization_id = org.organization_id
     AND t.transaction_source_id = h.batch_id
     AND t.organization_id = h.organization_id
GROUP BY subinventory_code,
         t.transaction_type_id,
         org.organization_name,
         org.organization_code,
         h.batch_no,
         c.acctg_cost
ORDER BY organization_code ASC
at No comments:

Period Close checking

=========Pending Chevk ==============


select * from mtl_txn_request_lines l,  mtl_txn_request_headers h
where l.LINE_ID=5949945
and l.HEADER_ID=h.HEADER_ID





 SELECT distinct --TRANSACTION_TYPE_ID,
 t.SUBINVENTORY_CODE, COUNT(*)
  --select *
  FROM MTL_MATERIAL_TRANSACTIONS_TEMP t
  where t.TRANSACTION_DATE<'01-SEP-2016'
  --and t.TRANSACTION_type_id = 35 --
  and t.TRANSACTION_DATE>='01-AUG-2016'  and  t.SUBINVENTORY_CODE not like 'D%'
  --and SUBINVENTORY_CODE like 'G10%'
  --and TRANSACTION_SOURCE_ID= 620388
  group by --TRANSACTION_TYPE_ID,
  t.SUBINVENTORY_CODE
  order by t.SUBINVENTORY_CODE desc
 
  ACCT_PERIOD_ID=139723
 
  select * from org_acct_periods where  ACCT_PERIOD_ID=139723
 
WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>' AND
       TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')
  AND NVL(TRANSACTION_STATUS, 0) <> 2
 

--Uncosted Material Transactions

SELECT /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */
*-- COUNT(*)
  FROM MTL_MATERIAL_TRANSACTIONS MMT
 WHERE ORGANIZATION_ID in
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
        -- WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
         )
   AND TRANSACTION_DATE <
       (SELECT distinct SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID in
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                -- WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
                 )
           AND PERIOD_NAME = :PERIOD_NAME)
   AND COSTED_FLAG IS NOT NULL

--Pending WIP Costing Transactions

SELECT COUNT(*)
  FROM WIP_COST_TXN_INTERFACE
 WHERE ORGANIZATION_ID in
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         --WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
         )
   AND TRANSACTION_DATE <
       (SELECT distinct SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                -- WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
                 )
           AND PERIOD_NAME = :PERIOD_NAME)

--Uncosted WSM Transactions
    
SELECT *--COUNT(*)
  FROM WSM_SPLIT_MERGE_TRANSACTIONS
 WHERE ORGANIZATION_ID in
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
        -- WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
         )
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                -- WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
                 )
           AND PERIOD_NAME =:PERIOD_NAME)

--Uncosted WSM Interface

SELECT *--COUNT(*)
  FROM WSM_SPLIT_MERGE_TXN_INTERFACE
 WHERE ORGANIZATION_ID in
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
        -- WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
         )
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 --WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
                 )
           AND PERIOD_NAME = :PERIOD_NAME)

--Pending Receiving Transactions

SELECT *--COUNT(*)
  FROM RCV_TRANSACTIONS_INTERFACE
 WHERE to_ORGANIZATION_ID in
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
        -- WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
         )
   AND TRANSACTION_DATE <
       (SELECT distinct SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID in
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                -- WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
                 )
           AND PERIOD_NAME = :PERIOD_NAME)
   AND DESTINATION_TYPE_CODE = 'INVENTORY'

--Pending Material Transactions

SELECT *--COUNT(*)
  FROM MTL_TRANSACTIONS_INTERFACE
 WHERE ORGANIZATION_ID in
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         --WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
         )
   AND TRANSACTION_DATE <
       (
       SELECT distinct SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID in
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 --WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
                 )
           AND PERIOD_NAME = 'Feb-16-17'--<PERIOD_NAME>'
           )
   AND PROCESS_FLAG <> 9


--Pending Shop Floor Move Transactions

SELECT COUNT(*)
  FROM WIP_MOVE_TXN_INTERFACE
 WHERE ORGANIZATION_ID =
       (
       SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
        -- WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
         )
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                -- WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>'
                 )
           AND PERIOD_NAME = 'Feb-16-17')--<PERIOD_NAME>')

--Unprocessed Shipping Transactions


SELECT *--COUNT(*)
  FROM WSH_DELIVERY_DETAILS     WDD,
       WSH_DELIVERY_ASSIGNMENTS WDA,
       WSH_NEW_DELIVERIES       WND,
       WSH_DELIVERY_LEGS        WDL,
       WSH_TRIP_STOPS           WTS
 WHERE WDD.SOURCE_CODE = 'OE'
   AND WDD.RELEASED_STATUS = 'C'
   AND WDD.INV_INTERFACED_FLAG IN ('N', 'P')
   --AND WDD.ORGANIZATION_ID = 86
   AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
   AND WND.DELIVERY_ID = WDA.DELIVERY_ID
   AND WND.STATUS_CODE IN ('CL', 'IT')
   AND WDL.DELIVERY_ID = WND.DELIVERY_ID
   AND WTS.PENDING_INTERFACE_FLAG IN ('Y', 'P')
   AND TRUNC(WTS.ACTUAL_DEPARTURE_DATE) BETWEEN
       TO_DATE('01-FEB-2017 00:00:00', 'DD-MON-YYYY HH24:MI:SS') AND
       TO_DATE('28-FEB-2017 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
   AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID


at No comments:

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:
Subscribe to: Posts (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...