SQL Script to find Concurrent program details with Path, program type, Executable details
SELECT fcp.concurrent_program_id ,fcp.concurrent_program_name ,fcpt.user_concurrent_program_name usr_conc_program ,fcp.enabled_flag flag ,fcp.output_file_type output_TYPE ,fe.executable_name ,fcp.execution_method_code program_method_code ,(SELECT meaning FROM apps.fnd_lookup_values WHERE lookup_code = fcp.execution_method_code AND lookup_type = 'CP_EXECUTION_METHOD_CODE') prog_method ,fe.execution_method_code executable_method_code ,(SELECT meaning FROM apps.fnd_lookup_values WHERE lookup_code = fe.execution_method_code AND lookup_type = 'CP_EXECUTION_METHOD_CODE') exec_method ,fe.execution_file_name ,fa.application_short_name ,fat.application_name ,fa.basepath top ,(select value from apps.fnd_env_context where variable_name = fa.basepath and concurrent_process_id = (select max(concurrent_process_id) from apps.fnd_env_context)) ||'/reports/US' path ,fcp.creation_date ,(select user_name||' : '||description from apps.fnd_user where user_id = fcp.created_by) created_by ,fcp.last_update_date ,(select user_name||' : '||description from apps.fnd_user where user_id = fcp.last_updated_by) updated_by FROM apps.fnd_concurrent_programs_tl fcpt ,apps.fnd_concurrent_programs fcp ,apps.fnd_executables fe ,apps.fnd_application_tl fat ,apps.fnd_application fa WHERE 1 = 1 and fcpt.application_id = fcp.application_id and fcpt.concurrent_program_id = fcp.concurrent_program_id and fe.executable_id = fcp.executable_id and fe.application_id = fa.application_id and fat.application_id = fa.application_id AND fcp.enabled_flag = 'Y' and upper(fcpt.user_concurrent_program_name) = '&prog_name_upper_case'--like '%EVENT%' ;Read More
July 26, 2017
Yes LPNs can be reused in either same inventory organization or a different organization.
This can be achieved using container public API
Provided the current context of the LPN is issued out of store and there is no pending transaction on that LPN and LPN name is a valid LPN name.
Prior to R12.1.1, oracle was not allowing to re use the LPN once shipped out.
But in certain business case this is necessary to reuse.
Like logistic business they have they container returned to dock or warehouse after delivering to customer and which can be resused, so why not the LPN tagged to that.
As of now there is no functionality in oracle forms using which this can be achieved.
In those cases, this can be achieved by using the below API
apps.wms_container_pub.reuse_lpns
It will Validate the LPN on below points
It should be issued out of stores.
It should have a valid LPN name.
There should not be any pending transactions for this LPN.
It derives the contents from WMS_LPN_CONTENTS & WMS_LICENSE_PLATE_NUMBERS. This will update the WMS_LPN_HISTORIES table as well
It marks the LPN as ‘Defined but not used’ with no Subinventory/Locator.
LPN is ready for reuse in the organization provided.
--SAMPLE API CODE
prompt Enter License Plate Number which has to be reused accept lpn;
Declare
l_Return_Status Varchar2(1);
l_Msg_Count Number;
l_Msg_Data Varchar2(100);
l_Lpn_Id Number;
l_Clear_Attributes Varchar2(10) := 'Y';
l_Unpack_Inner_Lpns Varchar2(10) := 'N';
l_Clear_Containter_Item_Id Varchar2(10) := 'N';
l_Organization_Id Number := Null;
-- l_License_Plate_Number Varchar2(100) := 'JR1';
l_License_Plate_Number VARCHAR2(100):= '&lpn';
Begin
Select Lpn_Id
Into l_Lpn_Id
From Wms_License_Plate_Numbers
Where License_Plate_Number = l_License_Plate_Number;
Enabling and disabling a parameter (for taking a valid date) in a concurrent program depending on another parameter
Let’s do this with one example.
There is a concurrent program named XXX Invoice Generation which has 2 parameters Document Type (Others & Sun Trns) and Date
Date field should be enabled only when the Document Type is Sub Trns or else it should be disabled. Also when the Date parameter is enabled, it should accept only dates
So here we have 2 requirements
Enable and Disable the Date parameter based on the value in Document Type parameter
Accept only valid dates in Date parameter
So actually to achieve the enable and disable of the Date parameter, we need to have a dummy parameter created which will not be displayed and the same should not be used in the code present in background.
So there will be 3 parameters in the concurrent program
Document Type:
Here in this example the parameter is created as Type and prompt as Document Type which is attached to a value set TEST_TYPE which is of type independent and has the values “Others” and “Sub Trns”.
Dummy1:
This is the Dummy parameter which is created to control the enabling disabling of the Date parameter.
This parameter is attached to a valueset DUMMY1 which is of type independent and has the values “Y”.
Also set default value to populate Y when the Document Type is Sub Trns else NULL select decode(:$FLEX$.Type,'Sub Trns','Y', null) from dual
So when Document Type is “Sub Trns”, Dummy1 value will be Y or else NULL
Date:
This parameter will behave based on the values in the Dummy1 Parameter
So to achieve this, we need to use a table type valueset where we need to put the validation for validating with Dummy1 parameter value.
So as per the requirement when Document Type is “Sub Trns”, then Date should be enabled which means when Dummy1 value is Y.
Using above setup enabling and disabling of the Date parameter can be achieved.
But we have another requirement is to validate the values entered in Date parameter as a valid date or not.
Normally this could have been validated using a valueset FND_STANDARD_DATE but that is of Validation Type None and we cannot have the enabling and disabling functionality if we use this.
So technically to achieve this, we need to tweek the requirement little bit.
We cannot have a plain valueset which will simply validate whether it’s a valid date or not. Instead, as we are using a table type valueset to trigger the enabling and siabling of the parameter, we have to create and use a view which will hold dates for certain period like in this example, last 6 months and next 6 months date. And pull the dates from the view in the valueset.
So when user enters a date, it will validate with the list of values for date between last and next 6 months and allow to proceed further.
Extra Information Types allow users to hold unlimited amounts of extra information on people, assignments, locations, positions, jobs and organizations. The format of this data is defined by the user in descriptive flexfield structures and accessed via Extra Information forms that can be added to taskflows and menus.
Defining an Extra Information Type
You define EITs in the relevant descriptive flexfield.
These are
Extra Assignment Info. Details
Extra Assignment Information
Extra Contact Info Details
Extra Job Info Details
Extra Job Information
Extra Location Info Details
Extra Location Information
Extra Person Information
Extra Previous Job Details
Extra Contact Information
Extra Document Info Details
Extra Document Information
Extra Position Information
Extra Previous Job Information
Extra Person Info. Details
Extra Position Info Details
Developer define the Context Reference Field on these flexfields is seeded with the value INFORMATION_TYPE.
You create a new context value for each EIT you require. Then create segments to hold the information you want to record, as you would for any other descriptive flexfield. Then freeze the flexfield structure.
Registering an Extra Information Type
Each Extra Information Type needs to be registered in the relevant EIT table (example, PER_PEOPLE_INFO_TYPES for Person EITs). This is done by running the concurrent process 'Register Extra Information Types (EITs)'. The Parameter 'Table Name' uses a LOV which, as well as the table name, displays the EIT name associated with that table. After selecting the Table Name, you select the 'Information Type' which is the name of the dff context you created for this new EIT. Enter values for 'Multiple Rows' ('Yes' if more than one set of values can be entered for this EIT); 'Legislation Code'; and 'Description'. If the new EIT is an Organization EIT then you can also restrict it to a single classification using the 'Organization Classification' parameter. It will then be available from the 'Others' button only for the relevant classifications.
Restricting Access to Extra Information Types
To allow a responsibility to access an EIT, you need to navigate to the Information Type Security form in HRMS
Security->Extra Information Security.
Highlight the responsibility that should have access to the EIT and then pick in the EIT name in the Information Types zone. Organization EITs are not visible in the Information Type Security form
Security->Extra Information Security. They are automatically accessible from the Others button in the Define Organization screen
Work Structures->Organization->Description for the relevant classification when the Register Extra Information Types (EITs) process is complete.
Adding Extra Information Types forms to a Taskflow
Task flow nodes for all EIT forms are seeded, but not included in any seeded task flows. So you will need to either update an existing taskflow or create a new one in order to access the EIT forms. Use the Define Task Flows screen to create or update your task flow.
Navigate->Security->Task Flow->Definitions
Adding EIT forms to a Menu
It is necessary to create a function for a new task flow before it can be added to a menu. In System Administrator
Navigate->Application->Function
Enter a function name and a user function name then enter the following parameter: WORKFLOW_NAME="" Go to the Define Menu screen
Navigate->Application->Menu
Query the menu that you wish to change. The value that you pick in the Function field will be what was entered as the User Function Name in the Define Function form. There is also a seeded function for each EIT form that could be added to a menu.
Example
Lets Take the example of Location form.
There is option for maintaining Extra Information for a location
Table & API Information
New contexts for the EIT descriptive flexfields are held in FND_DESCR_FLEX_CONTEXTS, and the segments are held in FND_DESCR_FLEX_COL_USAGE as with any other descriptive flexfield.
An EIT is registered by inserting a row into the relevant EIT table. The information here includes the EIT name, legislation code and whether the EIT allows single or multiple entry. The EIT tables are:
For Organization EITs it is also necessary to link the EIT with an organization classification. This link is held in HR_ORG_INFO_TYPES_BY_CLASS.
PER_INFO_TYPE_SECURITY is used to hold the EITs that a responsibility may access (excluding organization EITs). This information is entered in the Information Type Security form
Security->Extra Information Security.
The location of the data entered into EITs depends on the EIT type (eg Person EIT, Org EIT, etc).
There are a number of APIs which allow you to create, update and delete EIT information: create_location_extra_info, update_location_extra_info, delete_location_extra_info