GL XLA Receiving Link

/* Formatted on 3/1/2018 6:35:26 PM (QP5 v5.114.809.3010) */
  SELECT   b.NAME je_batch_name,
           b.description je_batch_description,
           b.running_total_accounted_dr je_batch_total_dr,
           b.running_total_accounted_cr je_batch_total_cr,
           b.status je_batch_status,
           b.default_effective_date je_batch_effective_date,
           b.default_period_name je_batch_period_name,
           b.creation_date je_batch_creation_date,
           u.user_name je_batch_created_by,
           h.je_category je_header_category,
           h.je_source je_header_source,
           h.period_name je_header_period_name,
           h.NAME je_header_journal_name,
           h.status je_header_journal_status,
           h.creation_date je_header_created_date,
           u1.user_name je_header_created_by,
           h.description je_header_description,
           h.running_total_accounted_dr je_header_total_acctd_dr,
           h.running_total_accounted_cr je_header_total_acctd_cr,
           l.je_line_num je_lines_line_number,
           l.ledger_id je_lines_ledger_id,
           glcc.concatenated_segments je_lines_ACCOUNT,
           l.entered_dr je_lines_entered_dr,
           l.entered_cr je_lines_entered_cr,
           l.accounted_dr je_lines_accounted_dr,
           l.accounted_cr je_lines_accounted_cr,
           l.description je_lines_description,
           glcc1.concatenated_segments xla_lines_account,
           xlal.accounting_class_code xla_lines_acct_class_code,
           xlal.accounted_dr xla_lines_accounted_dr,
           xlal.accounted_cr xla_lines_accounted_cr,
           xlal.description xla_lines_description,
           xlal.accounting_date xla_lines_accounting_date,
           xlate.entity_code xla_trx_entity_code,
           xlate.source_id_int_1 xla_trx_source_id_int_1,
           xlate.source_id_int_2 xla_trx_source_id_int_2,
           xlate.source_id_int_3 xla_trx_source_id_int_3,
           xlate.security_id_int_1 xla_trx_security_id_int_1,
           xlate.security_id_int_2 xla_trx_security_id_int_2,
           xlate.transaction_number xla_trx_transaction_number,
           rcvt.transaction_type rcv_trx_transaction_type,
           rcvt.transaction_date rcv_trx_transaction_date,
           rcvt.quantity rcv_trx_quantity,
           rcvt.shipment_header_id rcv_trx_shipment_header_id,
           rcvt.shipment_line_id rcv_trx_shipment_line_id,
           rcvt.destination_type_code rcv_trx_destination_type_code,
           rcvt.po_header_id rcv_trx_po_header_id,
           rcvt.po_line_id rcv_trx_po_line_id,
           rcvt.po_line_location_id rcv_trx_po_line_location_id,
           rcvt.po_distribution_id rcv_trx_po_distribution_id,
           rcvt.vendor_id rcv_trx_vendor_id,
           rcvt.vendor_site_id rcv_trx_vendor_site_id
    FROM   gl_je_batches b,
           gl_je_headers h,
           gl_je_lines l,
           fnd_user u,
           fnd_user u1,
           gl_code_combinations_kfv glcc,
           gl_code_combinations_kfv glcc1,
           gl_import_references gir,
           xla_ae_lines xlal,
           xla_ae_headers xlah,
           xla_events xlae,
           xla.xla_transaction_entities xlate,
           rcv_transactions rcvt
   WHERE       b.created_by = u.user_id
           AND h.created_by = u1.user_id
           AND b.je_batch_id = h.je_batch_id
           AND h.je_header_id = l.je_header_id
           AND l.code_combination_id = glcc.code_combination_id
           AND l.je_header_id = gir.je_header_id
           AND l.je_line_num = gir.je_line_num
           AND gir.gl_sl_link_table = xlal.gl_sl_link_table
           AND gir.gl_sl_link_id = xlal.gl_sl_link_id
           AND xlal.application_id = xlah.application_id
           AND xlal.ae_header_id = xlah.ae_header_id
           AND xlal.code_combination_id = glcc1.code_combination_id
           AND xlah.application_id = xlae.application_id
           AND xlah.event_id = xlae.event_id
           AND xlae.application_id = xlate.application_id
           AND xlae.entity_id = xlate.entity_id
           AND xlate.source_id_int_1 = rcvt.transaction_id
           AND h.JE_HEADER_ID = 2246924
           AND h.je_category = 'Receiving'
           --AND b.default_period_name = '01_APR-2009'
ORDER BY   h.je_category;

OBIEE 11.1.1.7.0 Services Error after changes in RPD

How to Debug Error -

Action Plan -

2. Please confirm your OBIEE version - 

3. Please open RPD in offline mode and check whether consistency check works 

4. Check in RPD - Identity and see whether there any users listed - if so - remove and redeploy 

5. If you deploy the old rpd or samplelite rpd does it start 

If All above Ok Then - 
Check following Log filr created when you restart all services 

$MW_HOME/instances/instance1/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1/nqserver*.log 

Error Will be like - 
Server start up failed: [nQSError: 36010] Server version 325 cannot read the newer version 326 of the repository /u01/obiee/mw_home/instances/ykdev/bifoundation/OracleBIServerComponent/coreapplication_obis1/repository/Output_RPD_BI0016.rpd. 

Client Tool used for RPD development and server RPD version must match else OBIEE server will not start after service restart.

How to change RPD version -

Go to folder where OBIEE Admin client tool is installed as below

C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orahome\bifoundation\server\bin

Open Command Prompt go to above path by > cd

run following utility 325 is same version as of server

nqgenoldverrpd.exe -P Admin123 -I C:\Users\OBIEE\Desktop\OBIEE11.1.1.7.0\Output_RPD_BI0002.rpd -O C:\Users\OBIEE\Desktop\OBIEE11.1.1.7.0\Output_RPD_BI0002n.rpd -V 325

WMS LPN DataFix --- LPN hanging at receiving

--Cleanup_Script.sql

set serveroutput on
DECLARE
  l_lpn_id      NUMBER :=&lpn_id;
BEGIN
  -- Data Clean Up
  -- Run the following script. for one LPN ID at a time
  dbms_output.put_line('Cleaning wms_op_plan_instances..');
  delete from wms_op_plan_instances
  where source_task_id in (
  select parent_line_id from mtl_material_transactions_temp
  where  lpn_id = l_lpn_id or transfer_lpn_id = l_lpn_id
  or content_lpn_id = l_lpn_id union
  select transaction_temp_id from mtl_material_transactions_temp
  where  lpn_id = l_lpn_id or transfer_lpn_id = l_lpn_id
  or content_lpn_id = l_lpn_id);
  dbms_output.put_line('wms_op_plan_instances: No of rows deleted ..' || SQL%ROWCOUNT);


  dbms_output.put_line('Cleaning wms_op_operation_instances..');
  delete from wms_op_operation_instances
  where source_task_id in (
  select transaction_temp_id from mtl_material_transactions_temp
  where  lpn_id =l_lpn_id or transfer_lpn_id = l_lpn_id
  or content_lpn_id = l_lpn_id union
  select parent_line_id from mtl_material_transactions_temp
  where  lpn_id = l_lpn_id or transfer_lpn_id = l_lpn_id
  or content_lpn_id = l_lpn_id);
  dbms_output.put_line('wms_op_operation_instances: No of rows deleted ..' || SQL%ROWCOUNT);


  dbms_output.put_line('Cleaning wms_dispatched_tasks..');
  delete from wms_dispatched_tasks where
  transaction_temp_id in (select transaction_temp_id from mtl_material_transactions_temp
  where lpn_id =l_lpn_id or transfer_lpn_id = l_lpn_id
  or content_lpn_id = l_lpn_id union
  select parent_line_id from mtl_material_transactions_temp
  where  lpn_id = l_lpn_id or transfer_lpn_id = l_lpn_id
  or content_lpn_id = l_lpn_id);
  dbms_output.put_line('wms_dispatched_tasks: No of rows deleted ..' || SQL%ROWCOUNT);


  dbms_output.put_line('Cleaning mtl_material_transactions_temp O..');
  delete from mtl_transaction_lots_temp
  where transaction_temp_id in (select transaction_temp_id from
  mtl_material_transactions_temp where lpn_id =l_lpn_id or transfer_lpn_id = l_lpn_id
  or content_lpn_id = l_lpn_id);
  dbms_output.put_line('mtl_material_transactions_temp O: No of rows deleted ..' || SQL%ROWCOUNT);


  dbms_output.put_line('Cleaning mtl_material_transactions_temp A..');
  delete from  mtl_material_transactions_temp
  where transaction_temp_id in
  (select parent_line_id from mtl_material_transactions_temp
  where lpn_id =l_lpn_id or transfer_lpn_id = l_lpn_id
  or content_lpn_id = l_lpn_id);
  dbms_output.put_line('mtl_material_transactions_temp A: No of rows deleted ..' || SQL%ROWCOUNT);

  dbms_output.put_line('Cleaning mtl_material_transactions_temp B..');
  delete from mtl_material_transactions_temp
  where lpn_id =l_lpn_id or transfer_lpn_id = l_lpn_id
  or content_lpn_id = l_lpn_id;
  dbms_output.put_line('mtl_material_transactions_temp B: No of rows deleted ..' || SQL%ROWCOUNT);

  dbms_output.put_line('Cleaning mtl_transaction_lots_interface..');
  delete from mtl_transaction_lots_interface
  where lot_number in (select lot_number
  from wms_lpn_contents where parent_lpn_id = l_lpn_id);
  dbms_output.put_line('mtl_transaction_lots_interface: No of rows deleted ..' || SQL%ROWCOUNT);

  dbms_output.put_line('Cleaning rcv_transactions_interface..');
  delete from rcv_lots_interface
  where interface_transaction_id in (select interface_transaction_id
  from rcv_transactions_interface
  where lpn_id = l_lpn_id or transfer_lpn_id = l_lpn_id);
  dbms_output.put_line('rcv_transactions_interface: No of rows deleted ..' || SQL%ROWCOUNT);

  dbms_output.put_line('Cleaning rcv_transactions_interface..');
  delete from rcv_transactions_interface
  where lpn_id = l_lpn_id or transfer_lpn_id = l_lpn_id;
  dbms_output.put_line('rcv_transactions_interface: No of rows deleted ..' || SQL%ROWCOUNT);

  dbms_output.put_line('Cleaning mtl_txn_request_lines..');
  UPDATE mtl_txn_request_lines
  SET quantity_detailed = null, wms_process_flag = 1
  WHERE lpn_id = l_lpn_id
  and line_status=7;
  dbms_output.put_line('mtl_txn_request_lines: No of rows updated ..' || SQL%ROWCOUNT);

  update wms_lpn_contents
  set source_header_id = NULL,
  source_name = null
  where parent_lpn_id = l_lpn_id;
  dbms_output.put_line('wms_lpn_contents: No of rows updated ..' || SQL%ROWCOUNT);

  commit;

END;

Inventory Transaction History

select * from gl_je_headers

select * from xyka_cash_payment_header where x_order_number = 16003100816

select * from xyka.xyka_wasel_revenue_interface   where trunc(TRANSACTION_DATE) = '02-MAR-16'


select * from RCV_TRANSACTIONS_INTERFACE

select * from wms_license_plate_numbers where LICENSE_PLATE_NUMBER=  'EHA120148660'

select * from mtl_material_transactions_temp
  where  lpn_id = :l_lpn_id or transfer_lpn_id = :l_lpn_id
  or content_lpn_id = :l_lpn_id union
  select * from mtl_material_transactions_temp
  where  lpn_id = :l_lpn_id or transfer_lpn_id = :l_lpn_id
  or content_lpn_id = :l_lpn_id


select * from wms_license_plate_numbers where LICENSE_PLATE_NUMBER=  'EHA120148660'        


select * from mtl_material_transactions where trunc(CREATION_DATE) between '16-DEC-15' and '31-DEC-15' and organization_id = 122


/* Formatted on 3-14-2016 1:57:44 PM (QP5 v5.114.809.3010) */
  SELECT   mp1.organization_code "Org" ,
           mp1.organization_name,
           msi.segment1 "SKU",
           mmt.subinventory_code "Subinventtory",
           mmt.transfer_subinventory "Transfer Subinventory",
           mp2.organization_code "Transfer Org",
           mmt.transaction_date,
           mmt.transaction_quantity ,
           cst.item_cost * nvl(mmt.transaction_quantity,0) "Transaction Cost",          
           mtst.transaction_source_type_name "Source Type",
           mgd.segment1 "Source",
           mmt.transaction_type_id,
           mtt.transaction_type_name "Transaction Type",
           ml.meaning "Transaction Action",
           mmt.shipment_number,
           fu.user_name,
           (SELECT   order_number
              FROM   oe_order_headers_all oeh, oe_order_lines_all oel
             WHERE   oeh.header_id = oel.header_id
                     AND oel.line_id = mmt.trx_source_line_id)
              order_number,
           (SELECT   poh.segment1
              FROM   po_headers_all poh
             WHERE   poh.po_header_id = mmt.transaction_source_id)
              po_number,
           mmt.transaction_id,
           mmt.source_code
    FROM   mtl_material_transactions mmt, -- ,mtl_parameters mp1
           org_organization_definitions mp1,
           hr_organization_units hou,
           mtl_system_items_b msi,
           mtl_parameters mp2,
           mtl_transaction_types mtt,
           mtl_txn_source_types mtst,
           mfg_lookups ml,
           fnd_user fu,
           mtl_generic_dispositions mgd,
           cst_item_costs cst
   WHERE    mmt.organization_id = cst.organization_id
     AND mmt.inventory_item_id = cst.inventory_item_id
           AND mmt.organization_id = mp1.organization_id
           AND hou.organization_id = mp1.organization_id
           --and mmt.transaction_type_id = 8 --Physical Inv Adjust
           -- and mmt.transaction_type_id in (2,31,41) --31 - Alias Issue, 41 - Alias Receipt, 2 - Subinv Transfer
           --and mmt.organization_id = 173
           -- and hou.type in ('CC','STORE')
           AND mmt.inventory_item_id = msi.inventory_item_id
           AND msi.organization_id = 122
           AND mp1.organization_code = '021'               ---organization(DC)
           ---and msi.segment1 = '47277371'
           AND mmt.transfer_organization_id = mp2.organization_id(+)
           AND mtt.transaction_type_id = mmt.transaction_type_id
           AND mtst.transaction_source_type_id = mmt.TRANSACTION_SOURCE_TYPE_ID
           AND ml.LOOKUP_TYPE = 'MTL_TRANSACTION_ACTION'
           AND ml.LOOKUP_CODE = mmt.transaction_action_id
           AND fu.user_id(+) = mmt.created_by
           AND mgd.disposition_id(+) = mmt.transaction_source_id
           AND mgd.organization_id(+) = mmt.organization_id           --and mgd.segment1='On-Hand Adjustment'
           AND mmt.TRANSACTION_DATE BETWEEN TO_DATE ('16-12-2015 00:00:00','DD-MM-YYYY HH24:MI:SS') ---from date
                                        AND  TO_DATE ('31-12-2015 23:59:59','DD-MM-YYYY HH24:MI:SS') ---to date
---and msi.segment1='64709397'
-- and ( mmt.TRANSACTION_ACTION_ID NOT IN (24,30) )
--and fu.user_name = 'ESPCOFI' /** Physical Inv job ran under ESPCOFI
ORDER BY   mp1.organization_code, msi.segment1, mmt.subinventory_code

Open Closed inventory period

GOAL

If an Inventory Accounting Period has been closed prematurely by accident the following scripts can be used to re-open the accounting period if the corresponding GL period is open.  Re-Opening a closed period will allow transactions to be process for that period.

The re-opening of a closed period should not be used to back date transactions, the system allow back dated transactions but this may cause discrepancies between inventory and GL. Any discrepancies caused by back dated transactions are not supported by Oracle and would have to be resolved with a manual adjustment to the General Ledger.

SOLUTION

DISCLAIMER: THE RE-OPENING OF A CLOSED INVENTORY PERIOD COULD POTENTIALLY CAUSE DATA CORRUPTION AND ANY DATA CORRUPTION CAUSED BY RE-OPENING A CLOSED INVENTORY PERIOD WILL BE THE RESPONSIBILITY OF THE CUSTOMER AND NO DATA FIX WILL BE PROVIDED FOR ANY DATA CORRUPTION THAT HAS BEEN CAUSED BY RE-OPENING A CLOSED PERIOD.
TEST THOROUGHLY ALL SCRIPTS ON A NON-PRODUCTION INSTANCE, FIRST BACKING UP ALL TABLE DATA PRIOR TO IMPLEMENTING IN PRODUCTION.
IF THERE IS CONCERN THAT RE-OPENING A CLOSED PERIOD MAY CAUSE DATA CORRUPTION PLEASE OPEN AN SR WITH ORACLE SUPPORT PRIOR TO RE-OPENING A CLOSED PERIOD.

-- A script to list all inventory periods for a specific organization
-- A script to reopen closed inventory accounting periods in 11.5.10 
-- The script will reopen all inventory periods for the specified 
-- Delete scripts to remove the rows created during the period close process to prevent duplicate rows
-- organization starting from the specified accounting period. 
-- The organization_id can be obtained from the MTL_PARAMETERS table. 
-- The acct_period_id can be obtained from the ORG_ACCT_PERIODS table.

SELECT acct_period_id period, open_flag, period_name name,
period_start_date, schedule_close_date, period_close_date
FROM org_acct_periods
WHERE organization_id = &org_id
order by 1,2;



UPDATE org_acct_periods
SET open_flag = 'Y',
period_close_date = NULL,
summarized_flag = 'N'
WHERE organization_id = &&org_id
AND acct_period_id >= &&acct_period_id;



DELETE mtl_period_summary
WHERE organization_id = &org_id
AND acct_period_id >= &acct_period_id;



DELETE mtl_period_cg_summary
WHERE organization_id = &org_id
AND acct_period_id >= &acct_period_id;



DELETE mtl_per_close_dtls
WHERE organization_id = &org_id
AND acct_period_id >= &acct_period_id;



DELETE cst_period_close_summary
WHERE organization_id = &org_id
AND acct_period_id >= &acct_period_id;



commit

Accounting Period does not exist in the value set CST_SRS_ORG_PERIODS.

CAUSE

Issue with CST_SRS_ORGANIZATIONS value set.

The following justifies how the issue is related to this specific customer:
 In Oracle Cost Management, not able to submit Period Close
 Pending Txn report with the intended parameters. Get error:
" ID 1 for the flexfield segment Accounting Period does not exist in the value set CST_SRS_ORG_PERIODS."

This is explained in the following bug:
 Bug 10638664 : TST212:GETTING ERROR UNABLE TO RUN PERIOD CLOSE PENDING TXN REPORT
 

SOLUTION

To implement the solution, please execute the following steps:

1. Download and review the readme and pre-requisites for Patch. 10638664:R12.BOM.C

2. Ensure that you have taken a backup of your system before applying
the recommended patch.

3. Apply the patch in a test environment.

4. Confirm the following file versions:
        bomprg.ldt 120.117.12010000.32

You can use the commands like the following:
        strings -a $XX_TOP/filename |grep '$Header'

5. Retest the issue.

6. Migrate the solution as appropriate to other environments.

PO - RCV - INV - XLA - GL

/* Formatted on 03-Nov-15 2:28:17 PM (QP5 v5.114.809.3010) */
/* Formatted on 03-Nov-15 2:28:45 PM (QP5 v5.114.809.3010) */
SELECT   *
  FROM   po_headers_all
 WHERE   segment1 = 1361170636

-- Accounting Entries for PO --

/* Formatted on 03-Nov-15 2:28:49 PM (QP5 v5.114.809.3010) */
SELECT   *
  FROM   po_headers_all
 WHERE   segment1 = 1361170636

--PO Receipt --
/* Formatted on 03-Nov-15 2:28:52 PM (QP5 v5.114.809.3010) */
SELECT   *
  FROM   rcv_transactions
 WHERE   po_header_id = 1182531

--RECEIVE     --  RECEIVING   --  rcv_receiving_sub_ledger
--DELIVER     -- INVENTORY  --  mtl_material_transactions

-- For DELIVER
/* Formatted on 03-Nov-15 2:28:55 PM (QP5 v5.114.809.3010) */
SELECT   *
  FROM   mtl_material_transactions
 WHERE   RCV_TRANSACTION_ID IN (SELECT   transaction_id
                                  FROM   rcv_transactions
                                 WHERE   po_header_id = 1182531)
-- 2699101
/* Formatted on 03-Nov-15 2:29:00 PM (QP5 v5.114.809.3010) */
SELECT   *
  FROM   mtl_transaction_accounts
 WHERE   transaction_id = 18820381
      -- 18820381
-- For Receive
--Accrual_Method_Flag = 'O'
--JE_SOURCE_NAME ='Purchasing'

--Transactions with accrue_on_receipt_flag ='N' ie Period End Accruals are
--      classified as:
--Accrual_Method_Flag = 'P'
--JE_SOURCE_NAME ='Purchasing'

select * from rcv_receiving_sub_ledger where RCV_TRANSACTION_ID in (select
      transaction_id
      from rcv_transactions
      where po_header_id = 1182531 )

/* Formatted on 03-Nov-15 2:28:30 PM (QP5 v5.114.809.3010) */
SELECT   jel.*
  FROM   gl_import_references gli,
         xla_ae_lines xll,
         xla_ae_headers xlh,
         xla_distribution_links xld,
         rcv_receiving_sub_ledger rsl,
         gl_je_lines jel
 WHERE       gli.gl_sl_link_table = xll.gl_sl_link_table
         AND gli.gl_sl_link_id = xll.gl_sl_link_id
         AND xll.ae_header_id = xlh.ae_header_id
         AND xld.ae_header_id = xlh.ae_header_id
         AND jel.je_header_id = gli.je_header_id
         AND jel.je_line_num = gli.je_line_num
         AND xld.source_distribution_id_num_1 = rsl.rcv_sub_ledger_id
         AND rsl.RCV_TRANSACTION_ID IN (SELECT   transaction_id
                                          FROM   rcv_transactions
                                         WHERE   po_header_id = 1182531)
         AND xld.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
         AND xll.gl_sl_link_table = 'XLAJEL'