On Hold / Active / In Active

[former_member445201]

Hi All,

In SBO 8.8 PL 11, BP/Items can be placed as active or inactive. In the help files it states that if the BP / item is used, a warning message will appear - BUT when in a sales order for example, the BP is unable to be found. Why is the BP unable to be found? Is this related to a setting or discrepancy with the patch or new functionality with 8.8?

We have recently upgraded to 8.8 PL17 and realised we had a problem with this function.

Can you let me know the query to create the alert?

I was told to create a new post based on a previous post which is out of date.

Regards

[zal_parchem2 · Accepted solution]

Hello Nick - welcome to the forum...I just returned from Europe last night and received the messages you have posted...yes, as you stated there is a glitch in the BP not being listed...

But there are several alerts which might help you. For instance:

1. An alert which tells the Financial personnel a Customer is to their limit (this includes open invoices, deliveries, and open orders) and they need to take a look to see if the Customer should be placed on hold.

2. An alert which lists out all those Customers who are on hold based upon the same criteria as above.

Both of these require a UDF to be set up on the BP Master Data record (as you read in the previous thread)...

Please let us know which alert you are looking for or maybe your own ideas on what the alert would look like or how it would be triggered...

Regards - Zal

[former_member445201 · Accepted solution]

Hi All, thanks for replying back to my post.

I have just returned from a couple of days off leave.

I was thinking to create a UDF in the BP with "Accounting on Hold" = Yes or No. If the BP is on Hold then when a user enter the specific BP on the Sales Order an alert will pop up stating customer is on hold. Is this possible? i have a bad feeling it is not and a approval procedure is needed when BP is on hold and sales order is created on them, a manager will need to approve the SO.

If this is the only way, what query can i use to generate this function?

Regards

[zal_parchem2 · Accepted solution]

Hello Nick - hope you had a good time on vacation...

The SQL for the approval procedure would go something like this:

SELECT 'TRUE'

FROM OCRD T0

WHERE 
T0.CardCode = $[$4.0.0]
AND T0.U_OnHold = 'Yes'

It is the TRUE return which sets off the approval process. The WHERE T0.CardCode, of course, might change dependent upon if you are using an approval procedure on a marketing document besides Sales Order....

Hope this helps - Zal

[former_member445201 · Accepted solution]

Hi Zal,

Thanks for the information. I'm just confused with the statement $($4.0.0) what does this mean?

U_onhold is a UDF which i will need to create and replace with what i will name it. I will probably name it the same as it is more system oriented.

Regards...

Edited by: Nick Dang on May 23, 2011 1:53 PM

Edited by: Nick Dang on May 23, 2011 1:54 PM

[zal_parchem2 · Accepted solution]

Hello Nick -

T0.CardCode = $[$4.0.0]

refers to the field value you find when you have the System Information displayed in SAP B1.

When the Sales Order window is open, go to "View" in SAP B1 at the very top of the screen, then select "Display System Information", move your cursor around to various fields on the Sales Order, and see what is displayed in the lower left hand corner of the screen. You will see that CardCode has that 4.0.0 value and you can think of it as if the 4.0.0 is like a variable which the SQL checks each time you enter a Sales Order.

Here, find some of the SAP B1 documentation under "SAP Manuals" found here:

http://wiki.sdn.sap.com/wiki/display/B1/SAPB1SQLLinksandAdditionalInformation

Hope this helps - Zal

[zal_parchem2 · Accepted solution]

Ooops - a PS - just a reminder in case you have not created user-defined fields; when creating user-defined fields, you will name it only OnHold, and the SAP B1 System will add the U_ part...

[former_member445201 · Accepted solution]

Hi Zal,

I know that much but didnt really understand the $xxx part. just to confirm the $4.0.0 = item=4 pane=0 variable=1 meaning $4.0.1? i cant see the $4.0.0. anywhere.

sorry to sound like a newbie.

[zal_parchem2 · Accepted solution]

Nick - no problem whatsover - that is why we are here in the forum...we have newbies and really experienced folks coming together helping each other - and that is what the forum is all about...

Just from experience I know that to use the variable reference, you use the first two - the 4 and the 0 - add a 0 on the end - add a $ to the 4 - encase them in the $ and the two

 [ ] 

in order to use them as variable references. There are other rules associated with this variable reference, but I cannot remember them now (usually remember them only when I am working on a specific SQL or problem)...

LOL - See, I am not a technical expert either - just experience...maybe there is something in the manuals reference on the link above???

Regards from Across the Pond - Zal

PS - I bet if you posted your initial thread in the SAP B1 Core Forum, you would get lots of folks answering there. I personally start all my threads there and let them tell me if I should be putting it somewhere else...

[Former Member · Accepted solution]

Hi Nick,

$[ \] means a variable related to the current form. You can get this number from System Info view. $[$4.0.0\] means CardCode in all documents form.

I think you may not need U_OnHold UDF at all since the system field handle onhold already.

Thanks,

Gordon

[former_member445201 · Accepted solution]

Hi Gordon,

Oh really? i thought this is the only really sensible workaround this issue?

Can you suggest what is the best workaround to my and anyone on 8.8 problem? without UDF?

[Former Member · Accepted solution]

Check frozenFor field on BP. If it is onhold, it should be 'Y'. Otherwise, it is 'N'.

[former_member445201 · Accepted solution]

Done

Edited by: Nick Dang on May 26, 2011 2:36 PM

[former_member445201 · Accepted solution]

Hi Zal,

I was wondering what is the $ query for Item instead of BP?

SELECT 'TRUE'

FROM OITM

WHERE

T0.CardCode = $ {$5.0.0}

is the $ part correct?

Thanks in advance.

[zal_parchem2 · Accepted solution]

Hello again Nick...the answer is yes and no...it would be 5, but if you are looking at the OITM table, Items do not have CardCode as an identifier, they have ItemCode, so it would be:

SELECT 'TRUE'
FROM OITM
WHERE 
T0.ITEMCode = $[$5.0.0]

Regards - Zal

[former_member445201 · Accepted solution]

Hi Zal,

Sorry i just copied and pasted the old query and only change the TABLE name, i totally missed the field name and of course OITM does not have CARDCODE as item names. I will try this out with a Yes and NO UDF field within sales quotation as a test. Thanks once again.

[former_member445201 · Accepted solution]

Hi Zal,

I have tried this on the ITEMCODE and it is not working. All the sales quatation comes with an error message when i tried to add the order.

the message reads "Could not commit transaction: Error - 1 detected during transaction". AS soon i have made the approval inactive the order goes through fine.

I have done it the same way with the relevant changes to the table and field names as the account on hold, which works perfectly now. Any advice?

SELECT 'TRUE'

FROM OITM

WHERE

T0.ITEMCode = ${$5.0.0}

and T0.U_retailitem = 'Y'

Note for some reason when i use the [ it becomes a hyperlink. { = [

[Former Member · Accepted solution]

You have not mentioned which form you are in. Assuming it is a market document, you can try:

SELECT Distinct 'TRUE'

FROM OITM T0

WHERE

T0.ITEMCode = $[$38.1.0\] and T0.U_retailitem = 'Y'

[former_member445201 · Accepted solution]

Hi Zal,

Wow it worked with the approval appearing! I am still confused with the $ part of the query. Where on the system information can you see it is $38.0.0.

I am in the stock management > Item master data

when i place my mouse over the item number it is saying form =150 item =5 Pane=0 variable=1? How did you come up with 38.0.0? I only ask becuase I would never have got that from the information i can see on SAP. I would like like to learn about this?

Many thanks Zal.

[Former Member · Accepted solution]

I am Gordon, a best friend of Zal.

You can find it in the marketing document. Not in item master.

Since you say it is working, it means you are used it in the marketing document.

[former_member445201 · Accepted solution]

Hi Gordon,

Sorry to sound really silly to you experts, but where is the market document within SAP B1 8.8? Is that another technical term for ITEM MASTER DATA lol. i can only imagine how silly this must sound?

[Former Member · Accepted solution]

No. All sales and purchasing as well as some inventory documents are marketing documents.

[former_member445201 · Accepted solution]

I see, i just tried to look everywhere to find market but cannot. I am still no closer to knowing how Zal managed to find it is $38.0.0? To me its a mystery

[Former Member · Accepted solution]

It is $[$38.1.0\] that I posted.

You may find my book in amazon by looking up my name. It can answer all your questions.

[Former Member]

Hi Nick,

We have created a different workaround where we have a UDF on the BP to indicate on hold, and a stored procedure to block adding of deliveries, invoices & payments for these customers.

It works well in smaller companies where the decision hierarcy is smaller. An authorised user can drill to the BP and release the hold

[Former Member]

Hi Nick,

Welcome you post on the forum.

This is a new functionality with 8.8. The inactive BP will not show when you add sales order.

What alert do you want?

Thanks,

Gordon