, , ,

R12 – How to Handle NULL for :$FLEX$.VALUE_SET_NAME In Oracle ERP

:$FLEX$.VALUE_SET_NAME to set up value sets where one segment depends on a prior segment that itself depends on a prior segment (“cascading dependencies”)

FLEX Syntax

WHERE CLAUSE

WHERE EXISTS (SELECT 1
FROM PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
MTL_SYSTEM_ITEMS_B MSI
WHERE PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID
AND PRHA.AUTHORIZATION_STATUS = ‘APPROVED’
AND PRLA.REQUISITION_LINE_ID = PRDA.REQUISITION_LINE_ID
AND PRLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND PRLA.DESTINATION_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND (PRLA.LINE_LOCATION_ID IS NULL
OR PRLA.AUCTION_HEADER_ID IS NULL)
AND PRDA.PROJECT_ID = :$FLEX$.XX_SECURED_PROJECTS_ID
AND SUBSTR(MSI.SEGMENT1,1,4) = ITM.ITEM_GROUP
AND NVL(PRLA.SUGGESTED_BUYER_ID,0) = NVL(:$FLEX$.PO_SRS_BUYER_ID:NULL, NVL(PRLA.SUGGESTED_BUYER_ID,0))
AND PRHA.SEGMENT1 BETWEEN NVL(:$FLEX$.P_PR_FROM:NULL, PRHA.SEGMENT1) AND NVL(:$FLEX$.P_PR_TO:NULL, PRHA.SEGMENT1)
AND TRUNC(APPROVED_DATE) BETWEEN fnd_date.canonical_to_date(:$FLEX$.P_FROM_DATE)
AND fnd_date.canonical_to_date(:$FLEX$.P_TO_DATE))
/by
, , , , , ,

How to Stop/Block Work Flow Mailer Send Mail from the Cloned Instance

YOur Work Flow mailer is triggering Mail often when you clone your Instance and you are unable to test the New stuff using work Flow Mailer 

Here is the solution for the Work flow mailer to stop sending older mails in the Test Instance 

UPDATE WF_NOTIFICATIONS

SET STATUS = ‘CLOSED’
where status =’OPEN’;
UPDATE WF_NOTIFICATIONS
SET MAIL_STATUS = ‘SENT’
where status =’OPEN’;

commit; 
Simple , but very Effective steps to Control your work flow mailer , sending old mails

Check this Steps:

Application Manager — > Application Dashboard ->

Application System– > Dev–> service Components –> Component Details

Set override Address : Dev: Work Flow Notification Mailer

Enter the Over Ride Address : *******. Mail .Com

Update the scripts:

update fnd_svc_comp_param_vals fscpv
set fscpv.PARAMETER_VALUE = ‘<override email address>’
where fscpv.parameter_id in (select fscpt.parameter_id
from fnd_svc_comp_params_tl fscpt
where fscpt.display_name = ‘Test Address’);

If this is Entered he/She will receive all the notification mails which has been triggered in the DEV or UAT 

Step 1Login to “Workflow Administrator Web Applications”
Image

Step 2
Ensure that Notification Mailer is running, and then click on icon as below
Image

Step 3
Click on “View Details”
Image

Step 4.
Click on “Set Override Address”
Image

Step 5.
Finally you can change the email address here. Please read the instructions in red carefully.
Image

/by
, , , ,

What is Batch Close Variance in OPM?

Batch Close Variances can occur in the following situations when using Actual Costing,
  • If the batch was released in one cost period and the debit to WIP is valued at one cost, but the batch was completed in a later cost period when the credit to WIP for the same quantities is valued at a different cost.
    This results in a left over balance WIP due to the cost change and must be cleared out.
  • Batch is released in one period and closed in the next period and has ingredient issues after last product yield and no product yield in next period.

  • When the Ingredient consumptions and product yields are recorded in a period, and in the next period the ingredient, resource, or byproduct consumptions for these batches are updated without any further product yields.
  • Ingredient issued in one period and returned back in next period with cost changes for ingredient across periods.
    – Essentially Ingredient/By-product transactions should be posted prior to product transactions in general, and certainly at least prior to last of product transaction so that such transactions do not contribute to batch close variance.

  • Cost Allocation factors on the Formula are not same as Cost Allocation Factors on the Batch Material details which could happen when using Dynamic Cost Allocation factors (profile option – GMF: Cost Allocation Factor Calculation – set to “Dynamic”)
    Any cost allocation factor change made after a Batch is released ( as it happens in case of dynamic cost allocation factors) would require re-layering.
    Re-layering these Batches followed by running the Actual Cost Process, Cost Update and OPM-Preprocessor will eliminate the Batch Close Variances for these Batches.

  • The profile option – ‘GMF: Batch Actual Cost Calculation Basis’ is set to ‘Use Virtual Incremental Backflush Quantities’ for a situation where on a Batch all the Ingredients and Resources are not issued out Upfront.
    Virtual Incremental Backflush is essentially designed to address a common situation in process industries where most or all ingredients are issued upfront and there are multiple product yields. 
    Without using the Virtual Incremental Backflush, the first yield would be posted at very high cost and subsequent yields would be posted at zero cost.
    With Virtual Incremental Back flush, costs are apportioned based on the Formula setup even before transactions occur.
    Thus if those transactions do not actually occur prior to batch close, Batch Close Variances would result.

  • Use of Virtual Incremental Backflush with consumption activity and cost changes for ingredients across periods.
/by
, , , ,

What are all Oracle ASCP Plan Types?

In R12, you can launch three type of plans:

  1. Production Plan
  2. Manufacturing Plan
  3. Master Plan

For MPS Planning item level, you should run Production Plan

And for MRP Planning Item, you should run Manufacturing plan.

Please see the explanation from Dev in similar question from other customers in Oracle Technical forum:

“The below are the 3 plan types :
1. MPP (Master Plan)
2. MPS (Production Plan)
3. MRP (Manufacturing Plan)

A. In 11.5.9, we had three plan types: DRP (Distribution Plan), MPS (Production Plan) and MRP (Manufacturing Plan)

B. In 11.5.10, we had three plan types: MPP, MPS and MRP. We changed the DRP name to MPP.
Why? Because in R12, we introduced a new Distribution Planning engine and having the term DRP used for the old plan type would have caused more confusion than MPP.”

/by
, , ,

How to use CUSTOM.pll in Oracle Apps

Requirement: change the Executable field into uppercase within the Concurrent Program Executable screen
 
Follow these steps to make the Executable field uppercase using CUSTOM.pll:
 
1.    Enable show custom events: Log in to the Application Developer responsibility and enable Show Custom Events. Check the blog post for enabling custom events:


Custom events can be enabled through the below navigation:

Once you enable show custom events, oracle will display the name of each and every event that can be trapped using CUSTOM.pll. Using this technique, you can identify the custom event that you need to trap to achieve the desired result.

 Custom Events

 

2.    Gather information: A message box will appear when this form is opened, indicating that the form name is FNDCPMPE, the block name is FND_EXECUTABLES, the field name is USER_EXECUTABLE_NAME, and the event name is WHEN-NEW-FORM-INSTANCE. Note down the names of these components as these will be required in programming CUSTOM.pll. To double-check the name of field, navigate to the Executable field and use the Help/Diagnostics/Examine menu to see the name.
 
3.    Write code in CUSTOM.pll: Open Oracle Forms Builder, highlight PL/SQL Libraries, and navigate from the menu to File | Open. Open the CUSTOM.pll file and expand the nodes. Within the node Attached Libraries, you will see FNDSQF and APPCORE2. Write your logic in a procedure called event, using the following sample syntax:
 
procedure event(event_name varchar2) is
form_name varchar2(30) := name_in(‘system.current_form’);
block_name varchar2(30) := name_in(‘system.cursor_block’);
begin
if form_name=’FNDCPMPE’ then
if event_name=’WHEN-NEW-FORM-INSTANCE’ then
app_item_property2.set_property(‘FND_EXECUTABLES.USER_EXECUTABLE_NAME’,CASE_RESTRICTION, UPPERCASE);
end if;
end if;
end event;
 
OR you can even write your extensions in a package.event(event_name) and call this procedure in package body of custom.event.
 
After making the programming changes to CUSTOM.pll, scroll down to the bottom of the package body Custom and make these changes to the version history of CUSTOM.pll:
 
This code should appear as a sublisting under #3fdrcsid(‘$Header: CUSTOM.pld 120.1 2009/03/03 18:43:00 appldev ship $’);
 
4.    Transfer CUSTOM.pll to $AU_TOP/resource: Log in to the forms server and change the directory to $AU_TOP/resource. Ensure you have a backup of CUSTOM.pll either in the source control system or on the file system. Next, transfer CUSTOM.pll from your desktop to $AU_TOP/resource.
 
5.    Generate CUSTOM.pll: Use the command frmcmp_batch, replacing the apps password with the relevant value on your system. After running this command, CUSTOM.plx will be created in $AU_TOP/resource:
 
cd $AU_TOP/resource
##For R12 use frmcmp_batch
frmcmp_batch module=CUSTOM.pll userid=apps/appspassword output_
file=./CUSTOM.plx compile_all=special module_type=LIBRARY
batch=yes
##For 11i Use f60gen command as shown below
f60gen module=CUSTOM.pll userid=apps/appspassword output_file=./
CUSTOM.plx module_type=LIBRARY
 
6.    Test your changes: Log out and log back in to the Concurrent Program Executable screen. You will notice that it is no longer possible to enter the concurrent program executable name using lowercase letters.
 
 

Concepts: Using the Custom Library

Customizing Oracle E-Business Suite with the CUSTOM Library

The CUSTOM library allows extension of Oracle E-Business Suite without modification of Oracle E-Business Suite code. You can use the CUSTOM library for customizations such as Zoom (such as moving to another form and querying up specific records), enforcing business rules (for example, vendor name must be in uppercase letters), and disabling fields that do not apply for your site.
You write code in the CUSTOM library, within the procedure shells that are provided. All logic must branch based on the form and block for which you want it to run. Oracle E-Business Suite sends events to the CUSTOM library. Your custom code can take effect based on these events.
Important: The CUSTOM library is provided for the exclusive use of Oracle E-Business Suite customers. The Oracle E-Business Suite products do not supply any predefined logic in the CUSTOM library other than the procedure shells described here.

Writing Code for the CUSTOM Library

The CUSTOM library is an Oracle Forms Developer PL/SQL library. It allows you to take full advantage of all the capabilities of Oracle Forms Developer, and integrate your code directly with Oracle E-Business Suite without making changes to Oracle E-Business Suite code.
The as-shipped CUSTOM library is located in the AU_TOP/resource directory (or platform equivalent). Place the CUSTOM library you modify in the AU_TOP/resource directory in order for your code to take effect.
After you write code in the CUSTOM procedures, compile and generate the library using Oracle Forms. Then place this library into $AU_TOP/resource directory (or platform equivalent). Subsequent invocations of Oracle E-Business Suite will then run this new code.

Warning: If there is a .plx (compiled code only) for a library, Oracle Forms Developer always uses the .plx over the .pll. Therefore, either delete the .plx file (so your code runs directly from the .pll file) or create your own .plx file using the Oracle Forms compiler. Using the .plx file will provide better preformance than using the .pll file. Depending on your operating system, a .plx may not be created when you compile and save using the Oracle Forms Developer. Form Builder. In this case, you must generate the library using the Oracle Forms Developer compiler from the command line (using the parameter COMPILE_ALL set to Yes). 

Be aware of the open form environment in which Oracle E-Business Suite operates. Also, each running form has its own database connection.

The following considerations and restrictions apply to the CUSTOM library and any libraries you attach to CUSTOM:

  • You cannot use any SQL in the library. However, you can use a record group to issue SELECT statements, and you can use calls to stored procedures for any other DML operations such as updates, inserts, or deletes.
  • Oracle Forms global variables in your code are visible to all running forms.

Altering Oracle E-Business Suite Code

Frequently you need to know the names of blocks and items within Oracle E-Business Suite forms for your CUSTOM logic. You should use the Examine feature available on the Help->Diagnostics menu while running the form of interest; it will give you easy access to all object names. You should not open Oracle E-Business Suite forms in the Oracle Forms Developer to learn this information.
You should exercise caution when changing any properties or values of items in the form from which CUSTOM logic is invoked.

The CUSTOM library is intended to be a mechanism to augment Oracle code with your own. Using the CUSTOM library to alter Oracle code at runtime may bypass important validation logic and may jeopardize the integrity of your data. You should thoroughly test all logic you add to the CUSTOM library before using it in a production environment. 

/by