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

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;

Wms_Container_Pub.Reuse_Lpns

(

p_Api_Version => 1,

x_Return_Status => l_Return_Status,

x_Msg_Count => l_Msg_Count,

x_Msg_Data => l_Msg_Data,

p_Lpn_Id => l_Lpn_Id,

p_Clear_Attributes => l_Clear_Attributes,

p_New_Org_Id => l_Organization_Id,

p_Unpack_Inner_Lpns => l_Unpack_Inner_Lpns,

p_Clear_Containter_Item_Id => l_Clear_Containter_Item_Id

);

Commit;

Exception

When Others Then

Dbms_Output.Put_Line('SQLEERM ' || Sqlerrm);

End;

Read More