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'

How to open Oracle Apps R12 forms in Internet Explorer 11 (IE. 11)

1) In internet explorer 11 , navigate to Tools –> Compatibility View Settings . Then add Domain name in the URL (ex: orapps.com in erp.oraapps.com) using Add this website option.

2) Navigate to Tools –> Internet options –> Security . Select internet and click on Custom level. Scroll down till end of the options and change the option Enable XSS filter to  Disable.

This solution worked for me and I am able to open R12 forms in Internet Explorer version 11.

Restart Browser and Try again

The Bill To address id must exist in Oracle Receivables, and it must be assigned to the Bill To customer (ORIG_SYSTEM_BILL_ADDRESS_ID) (33778)

The Bill To address id must exist in Oracle Receivables, and it must be assigned to the Bill To customer (ORIG_SYSTEM_BILL_ADDRESS_ID) (33778)


select
      c.cust_acct_site_id orig_system_bill_address_id,
      b.cust_account_id orig_system_bill_customer_id
from
      hz_parties a,
      hz_cust_accounts b,
      hz_cust_acct_sites_all c,
      hz_cust_site_uses_all d
where
      b.ACCOUNT_NUMBER = 'U99995'  --Enter Your Customer Name Here
and a.party_id = b.party_id
and c.cust_account_id = b.cust_account_id
and c.cust_acct_site_id = d.cust_acct_site_id
and d.site_use_code = 'BILL_TO';


select ORIG_SYSTEM_BILL_ADDRESS_ID from RA_INTERFACE_LINES_all where interface_line_id = 1702930
-- current value - 33778

update RA_INTERFACE_LINES_all
set ORIG_SYSTEM_BILL_ADDRESS_ID = 34035
where interface_line_id = 1702930

R12 Sales Person Query - OM

select   rs.salesrep_number,
         --rs.NAME salesrep_name,
         res.resource_name salesrep_name,
         hou.NAME org_name,
         rs.salesrep_id,
         rs.org_id,
         rs.start_date_active,
         rs.end_date_active
  FROM   apps.jtf_rs_salesreps rs,
         apps.JTF_RS_RESOURCE_EXTNS_VL RES,
         hr_organization_units hou
  WHERE  hou.organization_id = rs.org_id
    AND  rs.resource_id = res.resource_id ;

Payroll Update Employee Email / Attributes

DECLARE
   l_employee_number             per_people_f.employee_number%TYPE;
   l_person_id                   per_people_f.person_id%TYPE;
   l_per_object_version_number   per_people_f.object_version_number%TYPE;
   v_effective_start_date        date;
   v_effective_end_date          date;
   v_full_name                   varchar2 (100);
   v_comment_id                  number;
   v_name_combination_warning    boolean;
   v_assign_payroll_warning      boolean;
   v_orig_hire_warning           boolean;
   Cursor update_flag is select * from xyka_update_email ;
BEGIN
 
   FOR rec IN UPDATE_FLAG
   Loop


   SELECT   max(object_version_number), employee_number , person_id
     INTO   l_per_object_version_number, l_employee_number , l_person_id
     FROM   per_people_f
    WHERE   EMPLOYEE_NUMBER = rec.EMP_NO
     AND SYSDATE BETWEEN effective_start_date and effective_end_date
     Group by object_version_number , employee_number , person_id;
    --person_id = l_person_id;

   hr_person_api.update_person (
      p_validate                   => FALSE,
      p_effective_date             => TO_DATE ('01-JUN-2015', 'DD-MON-YYYY'),
      p_datetrack_update_mode      => 'CORRECTION',
      p_person_id                  => l_person_id,
      p_object_version_number      => l_per_object_version_number,
      p_employee_number            => l_employee_number,
      p_email_address              => rec.EMAIL,
      p_attribute6                 => 'Y',
      p_effective_start_date       => v_effective_start_date,
      p_effective_end_date         => v_effective_end_date,
      p_full_name                  => v_full_name,
      p_comment_id                 => v_comment_id,
      p_name_combination_warning   => v_name_combination_warning,
      p_assign_payroll_warning     => v_assign_payroll_warning,
      p_orig_hire_warning          => v_orig_hire_warning
   );
 
   COMMIT;

end loop;

  -- COMMIT;
END;
/






select attribute6 from per_all_people_f
where ATTRIBUTE6 is not null
and LAST_UPDATE_DATE is SYSDATE