Based on plsql code

User_0IIX4

Hello team,

I need a code when an invoice date not falls in an open period ,then we need to set GL_date as first date of first open period.

I could like to get the code in PLSQL.

Jan Gorkow

Hi @User_0IIX4 ,

please give us an example of the given inputs and the expected output.

Best regards,

Jan

BEDE

How do you wish to do that? Is it to be a procedure called via some user interface? Is it to be a trigger?

What is the input and what is the output? What actions are to be performed?

User_0IIX4

https://community.oracle.com/tech/developers/discussion/comment/16809985#Comment_16809985

invoice date coming from RG sensum (used in ur project)should be the gl_date in AR.if that date is in closed/not open period in AR,then the GL date should be the first date in the first open period AFTER the invoice date.if no such periods exists then the invoice date should be the Gl date.

User_0IIX4

https://community.oracle.com/tech/developers/discussion/comment/16809987#Comment_16809987

Hii

It should a procedure called via user interface.

example

invoice date coming from RG sensum (used in ur project)should be the gl_date in AR.if that date is in closed/not open period in AR,then the GL date should be the first date in the first open period AFTER the invoice date.if no such periods exists then the invoice date should be the Gl date.

Frank Kulash

Hi, @User_0IIX4

It's still not clear what you want. Are you trying to update a table? Post CREATE TABLE and INSERT statements for a little sample data as it occurs before the UPDATE, then show what the changed table looks like after the UPDATE, and explain why some rows got changed and others didn't. If you are defining the "open period" at the time of the UPDATE, then say what that period is for the example you posted. Always give your full Oracle version (e.g. 18.4.0.0.0).

User_0IIX4

Here I need a code where if the invoice date not falls in an open period then we need to set the gl_date to first open date of first open period in oracle(11g).

for example

if today’s date is 23-sept-21 and we got invoice date as 12-dec-2020 then we need to set the gl date to 1-sept-21 as it’s a open period irrespective of the invoice date

BluShadow

https://community.oracle.com/tech/developers/discussion/4489367/based-on-plsql-code

It sounds as if you're referring to a specific (3rd party?) database/application, or one of Oracle application suites.

This forum is for the SQL and PL/SQL language in the database, so you should assume that nobody here has your database, nor any knowledge of it's structure. Don't assume that because you're using "Oracle" that everybody has the same database and applications as yourself.

That is why people are asking you to provide some example tables and data with the expected output.

It's not good talking to us about "invoice date" and "open period" and "gl_date" or "RG sensum" when nobody here has those things.... my database certain doesn't have them.

So, share some basic table structures, and example data, and show what results you want from that data, with the logic explained.