GL Date Move for Lockbox Records – Oracle Applications tips – SQL

Posted by Jiltin     13 April, 2009    1,558 views   

Few companies have streamlined their operations to avoid GL_Date move. Moving GL date is very common fix in many companies, especially during the month end activities. Here is one of the requirements in which we need to move the gl_date of the lockbox records before creating the lockbox records.

The lockbox data is sent by banks very late evening. In addition, loading errors and the records errors are being fixed at night. However, General ledger period will be closed by 12 midnight. Hence, we are forced to move the gl_date for the future or open period.

It is wise to take a backup of the two tables. You need to run this as apps user or any user which has the privilege to run these scripts.

CREATE TABLE ar_transmissions_all_tmp AS SELECT * FROM ar_transmissions_all;

CREATE TABLE ar_payments_interface_all_tmp AS SELECT * FROM ar_payments_interface_all;

If you know the transmission name:

SELECT transmission_id FROM ar_transmissions_all WHERE transmission_name = ‘&&transmission_name’;

SELECT * FROM apps.ar_payments_interface_all
WHERE 1=1
AND transmission_id =  &&transmission_id;

UPDATE ar_payments_interface_all
SET gl_date=to_date(‘&&Required_gl_date’,‘DD/MM/YYYY’), STATUS=NULL
WHERE 1=1
AND transmission_id =  &&transmission_id;

If you know the request id:

UPDATE ar_payments_interface_all
SET gl_date=to_date(‘&&Required_gl_date’,‘DD/MM/YYYY’), STATUS=NULL
WHERE 1=1
AND transmission_request_id =  &&request_id;

Once you commit, you run the lockbox process, it loads the data with the required GL Date.

Provide feedback if you like this:
Following Google Searches Lead To This Post:
oracle list files

Post to Twitter  Post to Delicious  Post to Digg    Post to StumbleUpon

Categories : 11i Scripts, Oracle Applications, Receivables Tags : , ,

Comments

No comments yet.


Leave a comment

(required)

(required)