OPRJ create date and de-activation user and date

[former_member268870]

Experts,

My first question is: How can I get the Creation Date of a Project on the OPRJ table? I can see an OPRJ.UpdateDate, but not a creation date.

Secondly, I need the de-activating User and date included in the query. I can get the Updating User - but this refers to ANY update and not only to the OPRJ.Active change to N. If this Project is still active those will be blank.

SELECT 
	T0.[PrjCode], 
	T0.UserSign,
	T2.[U_NAME] AS 'Creating_User',
	---CreateDate
	T0.UserSign2,
	T3.[U_NAME] AS 'Update_User',
	T0.UpdateDate


FROM OPRJ T0 
	INNER JOIN APRJ T1 ON T0.[PrjCode] = T1.[PrjCode] AND T0.loginstanc = T1.loginstanc-1 
	left JOIN OUSR T2 on t2.userid = t0.usersign 
	left JOIN OUSR T3 ON T3.USERID = t0.UserSign2


WHERE T0.Active <> T1.Active

I appreciate your assistance,

Marli

[former_member390407 · Accepted solution]

Hi Marli,

Please try the following:

SELECT 
	OPRJ.[PrjCode], 
	UserCreated.USERID,
	UserCreated.[U_NAME] AS 'Creating_User',
	FirstInstance.UpdateDate, 
	UserUpdated.USERID,
	UserUpdated.[U_NAME] AS 'Update_User',
	ActivityChangeInstance.UpdateDate
FROM OPRJ -- Project table
JOIN APRJ AS FirstInstance ON OPRJ.[PrjCode] = FirstInstance.[PrjCode] 
	AND FirstInstance.loginstanc = 1 -- First update instance from history table
JOIN 
(
	SELECT APRJ.PrjCode, APRJ.UserSign2, APRJ.UpdateDate
	FROM APRJ
	JOIN 
	(
		SELECT  PrjCode, Min(LogInstanc) AS LogInstanc 
		FROM APRJ 
		WHERE Active = 'N'
		GROUP BY PrjCode
	) AS FirstInstances ON APRJ.PrjCode = FirstInstances.PrjCode -- Get numbers of the first instances where activity was set to N
							AND APRJ.LogInstanc = FirstInstances.LogInstanc 
) AS ActivityChangeInstance ON OPRJ.PrjCode = ActivityChangeInstance.PrjCode -- Get entire data rows of the first instances where activity was set to N
JOIN OUSR AS UserCreated on UserCreated.USERID = OPRJ.usersign -- Join with tables
JOIN OUSR AS UserUpdated ON UserUpdated.USERID = ActivityChangeInstance.UserSign2

First answer: There is no field for the creation date. If you check the history table you will see the project code in the creation date field. But you can get the update date of the first history instance - this will be your creation date.

Second answer: You can get the first instance where activity was set to 'N'. Note that this query won't work stable on projects that were created inactive but as I got your requirement it's not your case.

[former_member268870 · Accepted solution]

Sergei,

I appreciate your answer and played with this query to test.

At first I saw that it did not show my whole project list and I tried to determine why it did show the few it did....

Here is my test results:

1. Create new Project (That is active by default) - Query did NOT show this project
2. Mark this Project as Inactive - Query did NOT show this project
3. Mark this Project as Active - Query DID show this project
4. Any changes after this - The Query will continue to show this project

It seemed that the query only picks up the project if was marked as Inactive and changed back to Active....

Your assistance in solving this would be greatly appreciated!

Marli

[former_member268870 · Accepted solution]

Sergei,

If the Projects were uploaded via DTW - the Creation date is NULL. Is there a way to get the Creation date even if DTW was used to populate the OPRJ table?

Thanks,

Marli

[former_member390407 · Accepted solution]

Hi Marli,

Sorry for misleading it was a bit late when I answered.

Firstly: When you create a new project code there is no field with the creation date and the update date is empty. So you can get only the first update date and probably it will give nothing for you. You can create a UDT and put there codes and their creation dates using "SBO_SP_PostTransactionNotice" if it's mandatory for your report. Then you can just join this query with your UDT.

Secondly: When you update your project only the previous information comes to APRJ table, so if project was updated only once you need to get this data from OPRJ.

Try something like this:

SELECT 
	OPRJ.[PrjCode], 
	UserCreated.USERID AS 'CreateUserId',
	UserCreated.[U_NAME] AS 'Creating_User',
	ISNULL(FirstInstance.UpdateDate, OPRJ.UpdateDate) AS 'CreateDate', 
	UserUpdated.USERID AS 'UpdaetUserId',
	UserUpdated.[U_NAME] AS 'Update_User',
	ISNULL(ActivityChangeInstance.UpdateDate, OPRJ.UpdateDate)
FROM OPRJ -- Project table
LEFT JOIN APRJ AS FirstInstance ON OPRJ.[PrjCode] = FirstInstance.[PrjCode] 
	AND FirstInstance.loginstanc = 1 -- First update instance from history table
LEFT JOIN 
(
	SELECT APRJ.PrjCode, APRJ.UserSign2, APRJ.UpdateDate
	FROM APRJ
	JOIN 
	(
		SELECT  PrjCode, Min(LogInstanc) AS LogInstanc 
		FROM APRJ 
		WHERE Active = 'N'
		GROUP BY PrjCode
	) AS FirstInstances ON APRJ.PrjCode = FirstInstances.PrjCode -- Get numbers of the first instances where activity was set to N
							AND APRJ.LogInstanc = FirstInstances.LogInstanc 
) AS ActivityChangeInstance ON OPRJ.PrjCode = ActivityChangeInstance.PrjCode -- Get entire data rows of the first instances where activity was set to N
LEFT JOIN OUSR AS UserCreated on UserCreated.USERID = OPRJ.usersign -- Join with tables
LEFT JOIN OUSR AS UserUpdated ON UserUpdated.USERID = ISNULL(ActivityChangeInstance.UserSign2, OPRJ.UserSign2)
WHERE OPRJ.Active = 'N'

[former_member390407 · Accepted solution]

That is not about DTW, I mistook, when you create a project this field is empty. You can try approach with UDT as I described above.

[former_member268870 · Accepted solution]

Sergei,

Thank you for your continued efforts, this worked!

Regards,

Marli

[Abdul]

Create UDFs in Same table and use Post transaction notification to update and for existing data contact SAP Support to have knowledge of log tables because OPRJ doe not store creation user.

For Auto update keep in mind following SAP Note 896891 - Support Scope for SAP Business One - DB Integrity

SAP will not support any database, which is inconsistent, due to SQL-Queries, which modify datasets or the datastructure of the SAP Business One Database. This includes any update-, delete- or dropstatements executed via SQL-Server Tools or via the the query interface of SAP Business One. This is stated in the support contract between SAP and the SAP Business Partner also. "SAP will not provide support for any problem that arises because Partner or end customer has altered Software or SDK inappropriately " and "Nor will SAP provide any support services for problems that lie in Partner's or end customer's area of responsibility and result, for example, from inappropriate installation, unsatisfactory end-user training, lack of or incorrect business design, incorrect operation, or faulty hardware" Where inconsistencies are found in a database as outlined above, SAP Business One Product Support will be unable to process the incident further. In order to continue the support for this SAP Business One installation,

[former_member268870]

Abdul,

Thanks for your clarification. I am aware of SAP's Update/Delete/Drop rule and do not wish to do that.

But now that you have brought that up - I'm interested:

• So it is acceptable to update UDF/UDT through SQL statements? Like transaction notification?
• Is it acceptable to Drop a UDT?

Thanks for your insights.

Regards,

Marli

[Abdul]

For UDTs and UDFs its Ok to update or delete i never drop any table but i used update and delete for UDOs and UDF and no issues faced even during upgrade.