Oracle EBs Apps Blog of Mahfuz, OPM and OBIEE Consultant

Tuesday 26 December 2017

R12.2.6 : Legal Entities LoV’s in the bank account definition page not Flow

If your List of Values (LoV) of  Legal Entities are  not flow within the bank account definition form in Oracle Cash Management, following steps will  help you.

  1. Login to Oracle Applications as sysadmin
  2. Select the User Management Responsibility
  3. Select the navigation path of ‘Roles and Role Inheritance’
  4. Within the field ‘Type‘, select Roles and Responsibilities
  5. Following step 4, a new field appears called ’Category‘, select ‘Miscellaneous’ as the value
  6. Within the ‘Application‘ field, select ‘Cash Management‘. 
  7. Click Go
  8. Select your Responsibility, if not Add you Responsibility to give the role permission to create bank accounts. 
  9. Click on the update icon against the desired role or responsibility
  10. A new window (update Role) will be displayed. Click the ‘Security Wizards‘ button
  11. Click Save.
  12. Click the ‘Run Wizard‘ icon against ‘CE UMX Security'
  13. Bank Account Security Management Window:   Click the ‘Add Legal Entities‘ button and select you Legal entities that are not already listed.
  14. Select Use, Maintenance and Bank account Transfers. 
  15. Apply the changes and then verify in the Cash Management responsibility if you are now able to see the Legal Entities in bank account creation form.
  16.  After that the entry will like bellow display : 
Bank Account Grants: Permissions
Use: Allows the user to Cash Positioning, Cash Forecasting and Cash flows, as well as reconcile cash flows for the selected legal entities bank accounts.
Bank Account Transfer: Allows the user to transfer funds from and to the bank accounts for the selected legal entity.
Maintenance: Allows the user to create and update bank accounts for the selected legal entity.


If you feel helpful, please comments.
 
at No comments:

Saturday 29 July 2017

Promotional Items have generated COGS, why?

Promotional Items have generated COGS, because of Cost of those Items.
For that, need to adjust cost and then  need to run cost process to accounting.

at No comments:

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...