SQL Query to findout Executable, Concurrent Program and its Parameters information

SELECT DISTINCT fcpl.user_concurrent_program_name "Concurrent Program Name",
  fcp.concurrent_program_name "Short Name"                                 ,
  fat.application_name                                                     ,
  fl.meaning execution_method                                              ,
  fe.execution_file_name                                                   ,
  fcp.output_file_type                                                     ,
  fdfcuv.column_seq_num "Column Seq Number"                                ,
  fdfcuv.end_user_column_name "Parameter Name"                             ,
  fdfcuv.form_left_prompt "Prompt"                                         ,
  fdfcuv.enabled_flag " Enabled Flag"                                      ,
  fdfcuv.required_flag "Required Flag"                                     ,
  fdfcuv.display_flag "Display Flag"                                       ,
  fdfcuv.flex_value_set_id "Value Set Id"                                  ,
  ffvs.flex_value_set_name "Value Set Name"                                ,
  flv.meaning "Default Type"                                               ,
  fdfcuv.default_value "Default Value"
   FROM fnd_concurrent_programs fcp ,
  fnd_concurrent_programs_tl fcpl   ,
  fnd_descr_flex_col_usage_vl fdfcuv,
  fnd_flex_value_sets ffvs          ,
  fnd_lookup_values flv             ,
  fnd_lookups fl                    ,
  fnd_executables fe                ,
  fnd_executables_tl fet            ,
  fnd_application_tl fat
  WHERE 1                     = 1
AND fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcp.enabled_flag          = 'Y'
AND fcpl.user_concurrent_program_name LIKE 'Workflow Background Process' --<Your Concurrent Program Name>
AND fdfcuv.descriptive_flexfield_name = '$SRS$.'
  || fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type(+)     = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+)     = fdfcuv.default_type
AND fcpl.LANGUAGE          = 'US'
AND flv.LANGUAGE(+)        = 'US'
AND fl.lookup_type         ='CP_EXECUTION_METHOD_CODE'
AND fl.lookup_code         =fcp.execution_method_code
AND fe.executable_id       = fcp.executable_id
AND fe.executable_id       =fet.executable_id
AND fet.LANGUAGE           = 'US'
AND fat.application_id     =fcp.application_id
AND fat.LANGUAGE           = 'US'
ORDER BY fdfcuv.column_seq_num;

PL/SQL Script to Create a Concurrent Program from backend

We need to use FND_PROGRAM.REGISTER api to create an concurrent program from backend database, below is the sample script.

Creating the Concurrent Program from backend:

/****************************************************** *PURPOSE: To Create a Concurrent Program from backend * *AUTHOR: Shailender Thallam * *******************************************************/ -- DECLARE -- l_program VARCHAR2 (200); l_application VARCHAR2 (200); l_enabled VARCHAR2 (200); l_short_name VARCHAR2 (200); l_description VARCHAR2 (200); l_executable_short_name VARCHAR2 (200); l_executable_application VARCHAR2 (200); l_execution_options VARCHAR2 (200); l_priority NUMBER; l_save_output VARCHAR2 (200); l_print VARCHAR2 (200); l_cols NUMBER; l_rows NUMBER; l_style VARCHAR2 (200); l_style_required VARCHAR2 (200); l_printer VARCHAR2 (200); l_request_type VARCHAR2 (200); l_request_type_application VARCHAR2 (200); l_use_in_srs VARCHAR2 (200); l_allow_disabled_values VARCHAR2 (200); l_run_alone VARCHAR2 (200); l_output_type VARCHAR2 (200); l_enable_trace VARCHAR2 (200); l_restart VARCHAR2 (200); l_nls_compliant VARCHAR2 (200); l_icon_name VARCHAR2 (200); l_language_code VARCHAR2 (200); l_mls_function_short_name VARCHAR2 (200); l_mls_function_application VARCHAR2 (200); l_incrementor VARCHAR2 (200); l_refresh_portlet VARCHAR2 (200); l_check VARCHAR2 (2); -- BEGIN -- l_program := 'Concurrent program registered from backend - OracleAppsDNA'; l_application := 'CUSTOM Custom'; l_enabled := 'Y'; l_short_name := 'XX_ORACLEAPPSDNA_CP'; l_description := 'OracleAppsDNA Test Program'; l_executable_short_name := 'XX_ORACLEAPPSDNA_EXE'; l_executable_application := 'CUSTOM Custom'; l_execution_options := NULL; l_priority := NULL; l_save_output := 'Y'; l_print := 'Y'; l_cols := NULL; l_rows := NULL; l_style := NULL; l_style_required := 'N'; l_printer := NULL; l_request_type := NULL; l_request_type_application := NULL; l_use_in_srs := 'Y'; l_allow_disabled_values := 'N'; l_run_alone := 'N'; l_output_type := 'TEXT'; l_enable_trace := 'N'; l_restart := 'Y'; l_nls_compliant := 'Y'; l_icon_name := NULL; l_language_code := 'US'; l_mls_function_short_name := NULL; l_mls_function_application := NULL; l_incrementor := NULL; l_refresh_portlet := NULL; -- --Calling API to create concurrent program definition -- apps.fnd_program.register (program => l_program, application => l_application, enabled => l_enabled, short_name => l_short_name, description => l_description, executable_short_name => l_executable_short_name, executable_application => l_executable_application, execution_options => l_execution_options, priority => l_priority, save_output => l_save_output, print => l_print, cols => l_cols, ROWS => l_rows, STYLE => l_style, style_required => l_style_required, printer => l_printer, request_type => l_request_type, request_type_application => l_request_type_application, use_in_srs => l_use_in_srs, allow_disabled_values => l_allow_disabled_values, run_alone => l_run_alone, output_type => l_output_type, enable_trace => l_enable_trace, restart => l_restart, nls_compliant => l_nls_compliant, icon_name => l_icon_name, language_code => l_language_code, mls_function_short_name => l_mls_function_short_name, mls_function_application => l_mls_function_application, incrementor => l_incrementor, refresh_portlet => l_refresh_portlet ); -- COMMIT; -- BEGIN -- --To check whether Concurrent Program is registered or not -- SELECT 'Y' INTO l_check FROM fnd_concurrent_programs WHERE concurrent_program_name = 'XX_ORACLEAPPSDNA_CP'; -- DBMS_OUTPUT.put_line ('Concurrent Program Registered Successfully'); -- EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line ('Concurrent Program Registration Failed'); END; END;

View from front end application:

1. The various output types are ‘PS’, ‘PDF’, ‘HTML’, ‘TEXT’, ‘PCL’, ‘XML’.
2. The above script inserts data into FND_CONCURRENT_PROGRAMS and FND_CONCURRENT_PROGRAMS_TL tables

Sales Order Line Status Flow

OM = Order Management Sales Order Form

SE = Shipping Transactions or Shipping Execution Form

Entered [OM]: Order is saved but not booked

Booked [OM]: Order is Booked.

Awaiting Shipping [OM]: Order is booked but lines are not yet picked.

Navigating to Shipping Execution, the delivery line status flow is:Not Ready to Release [SE]: A delivery line may be in this status when it is interfaced manually into Shipping, is not scheduled and has no reservations. When lines are imported automatically from Order Management this status is not used.

Released to Warehouse [SE]: Pick Release has started but not yet completed. One of the reason could be allocation have not been pick confirmed. The Pick Release process creates a Move Order Header & Mover Order Line in Inventory. This is a common status for users that perform a two-step pick release process. This status indicates that inventory allocation has occurred however pick conformation has not yet taken place.
Ready to Release [SE]: Order Line is booked and passed to shipping execution. The line is now eligible to pick Release.
Backordered [SE]: The status of Backorderd is assigned to a line under the following circumstances.● The Pick Release process attempted to allocate inventory to the line and all or a partial quantity of the item was not available. In this case the system automatically backorders the discrepant quantity.
● At Ship confirm the user enters a shipped quantity for an item that is less than the original requested quantity.
● The user manually Backorders the entire delivery.
Shipped [SE]: The delivery line is shipped confirmed.
Confirmed [SE]: The delivery line is shipped or backordered and the trip stops are open.
Picked [OM]: Pick release is complete, both allocations and pick confirm
Picked Partial [OM]: This status occurs when a delivery line is not allocated the full quantity during Pick Release and Ship Confirm has not occurred
Interfaced [SE]: The delivery line is shipped and Inventory interface concurrent process is complete.
Awaiting Fulfillment [OM]: When fulfillment set is used, Not all shippable lines in a fulfillment set or a configuration are fulfilled
Fulfilled [OM]: All lines in a fulfillment set are fulfilled.
Interfaced to Receivables [OM]: The order is linked with Receivables and the invoice is created.
Partially Interfaced to Receivables [OM]: This status is used in a PTO flow and indicates that the particular PTO item is required for revenue.
Closed [OM]: Closed indicates that the line is closed.

Canceled [OM]: Indicates that the line has been completely canceled. No further processing will occur for this line.

WSH_DELIVERY_DETAILS.Release_Status can have any of the below valid values

Execute the below query to see lookup values

SELECT lookup_type, lookup_code, meaning, description FROM fnd_lookup_values WHERE lookup_type = 'PICK_STATUS' AND LANGUAGE = 'US';

LOOKUP_TYPE	LOOKUP_CODE	MEANING	DESCRIPTION
PICK_STATUS	B	Backordered	Line failed to be allocated in Inventory
PICK_STATUS	C	Shipped	Line has been shipped
PICK_STATUS	D	Cancelled	Line is Cancelled
PICK_STATUS	E	Replenishment Requested	Line has been replenishment requested
PICK_STATUS	F	Replenishment Completed	Line has been replenishment completed
PICK_STATUS	I	Interfaced	Line has been shipped and interfaced to Order Management and Inventory
PICK_STATUS	K	Planned for Crossdocking	Line has been Planned for X-dock
PICK_STATUS	L	Closed	Line has been Received
PICK_STATUS	N	Not Ready to Release	Line is not ready to be released
PICK_STATUS	P	Purged	Line has been purged from source system
PICK_STATUS	R	Ready to Release	Line is ready to be released
PICK_STATUS	S	Released to Warehouse	Line has been released to Inventory for processing
PICK_STATUS	X	Not Applicable	Line is not applicable for Pick Release
PICK_STATUS	Y	Staged/Pick Confirmed	Line has been picked and staged by Inventory

WSH_NEW_DELIVERIES.status_code column values

SELECT lookup_type, lookup_code, meaning, description FROM fnd_lookup_values WHERE lookup_type = 'TRIP_STATUS' AND LANGUAGE = 'US';

LOOKUP_TYPE	LOOKUP_CODE	MEANING	DESCRIPTION
TRIP_STATUS	CL	Closed	Trip has completed
TRIP_STATUS	IT	In-Transit	Trip is in-transit and has begun
TRIP_STATUS	OP	Open	Trip is Open and has not begun

No related posts.

PL/SQL Script to Generate XML Tags for XMLP Report(How to Display Leading Zeros in XMLP Report – Excel Output)

There are many ways to generate output in XML tags format, dbms_xmlgen is one of the way to generate.

Lets consider an example to display EMP table output in XMLP report in excel format, below is the scrip to generate xml tags

DECLARE -- --Cursor to fetch the data -- CURSOR data_cur IS -- SELECT empno,ename,job,hiredate,sal FROM emp; -- output_row data_cur%rowtype; BEGIN -- -- dbms_output.put_line('<?xml version="1.0" encoding="US-ASCII" standalone="no"?>'); fnd_file.put_line(fnd_file.output,'<?xml version="1.0" encoding="US-ASCII" standalone="no"?>'); dbms_output.put_line('<OUTPUT>'); fnd_file.put_line(fnd_file.output,'<OUTPUT>'); -- OPEN data_cur; LOOP -- FETCH data_cur INTO output_row; EXIT WHEN data_cur%notfound; -- dbms_output.put_line('<ROW>'); fnd_file.put_line(fnd_file.output,'<ROW>'); -- dbms_output.put_line('<ENUM>'||dbms_xmlgen.CONVERT(output_row.empno)||'</ENUM>'); fnd_file.put_line(fnd_file.output,'<ENUM>'||dbms_xmlgen.CONVERT(output_row.empno)||'</ENUM>'); -- dbms_output.put_line('<ENAME>'||dbms_xmlgen.CONVERT(output_row.ename )||'</ENAME>'); fnd_file.put_line(fnd_file.output,'<ENAME>'||dbms_xmlgen.CONVERT(output_row.ename )||'</ENAME>'); -- dbms_output.put_line('<JOB>'||dbms_xmlgen.CONVERT(output_row.job )||'</JOB>'); fnd_file.put_line(fnd_file.output,'<JOB>'||dbms_xmlgen.CONVERT(output_row.job )||'</JOB>'); -- dbms_output.put_line('<HIRE_DATE>'||dbms_xmlgen.CONVERT(output_row.hiredate )||'</HIRE_DATE>'); fnd_file.put_line(fnd_file.output,'<HIRE_DATE>'||dbms_xmlgen.CONVERT(output_row.hiredate )||'</HIRE_DATE>'); -- dbms_output.put_line('<SAL>'||dbms_xmlgen.CONVERT(output_row.sal )||'</SAL>'); fnd_file.put_line(fnd_file.output,'<SAL>'||dbms_xmlgen.CONVERT(output_row.sal )||'</SAL>'); -- dbms_output.put_line('</ROW>'); fnd_file.put_line(fnd_file.output,'</ROW>'); -- END LOOP; CLOSE data_cur; -- dbms_output.put_line('</OUTPUT>'); fnd_file.put_line(fnd_file.output,'</OUTPUT>'); -- END; /

below is the generated output in XML tags

<?xml version="1.0" encoding="US-ASCII" standalone="no"?> <OUTPUT> <ROW> <ENUM>007369</ENUM> <ENAME>SMITH</ENAME> <JOB>CLERK</JOB> <HIRE_DATE>17-12-80</HIRE_DATE> <SAL>800</SAL> </ROW> <ROW> <ENUM>007499</ENUM> <ENAME>ALLEN</ENAME> <JOB>SALESMAN</JOB> <HIRE_DATE>20-02-81</HIRE_DATE> <SAL>1600</SAL> </ROW> <ROW> <ENUM>007521</ENUM> <ENAME>WARD</ENAME> <JOB>SALESMAN</JOB> <HIRE_DATE>22-02-81</HIRE_DATE> <SAL>1250</SAL> </ROW> <ROW> <ENUM>7566</ENUM> <ENAME>JONES</ENAME> <JOB>MANAGER</JOB> <HIRE_DATE>02-04-81</HIRE_DATE> <SAL>2975</SAL> </ROW> </OUTPUT>

Below is the screenshot of the output layout (XMLP Template)

Below is the screen shot of output generated

You might have observed that the xml content has employee number with 00 as prefix but the excel output is not showing the 00 prefix, to know why and how to resolve this.

Microsoft Excel has a tendency to display number format based columns with no prefix of ZEROs. For example, if there is a value ’007′ excel displays it as ’7′, excel display it as ’007′ only if the column is set in text format. You can see the difference in the below screenshot

Please go through the below URL for an example to generate an XMLP report:

In the example mentioned in the above URL you could see the output as shown in the below screenshot

But if you observe the data in the emp table, you could see three rows have leading zeros for employee number( Please note, I have updated the data of seeded emp table with prefix of 00 and also changed the data type of empno column to varchar2(6) for the sake of example.)

As the output is of excel type the empno column is considered as Number column (as the entire column consists of number format data) and the leading zeros are removed in display.

We have multiple ways to resolve this, I have listed them below.

Method 1

1. Open the RTF Template in MS Word.
2. Go to Data -> Load XML Data.
3. Once the data is loaded successfully, double click on that field.
4. Under field properties window set the field formatting type as “Regular Text” and set the check box “Force LTR”.

After following the above said steps, save the template and preview the output which is as shown below

Method 2

Add Ctrl+Shift+Space after/before emplyee number on template to create a non-breaking space. The non-breakable space converts the number column to string column

Disadvantage of this method is that the non-breakable space is visible in the output as well, you can see in the screenshot below

Method 3

This is the best method I have found, In this method we need to use an equal-to symbol before the field and enclose the field in double quotes for example:- =”ENUM”

This works only in excel however it will allow you to cut and paste (ie to use the value to search in Oracle) and also to do vlookups.

The theory is that excel will concatenate the values together because it has quotes around it, it will treat it as a string rather than simply a value.

For any other formats this method will not work.

You can see in the above screenshot that Enum column values are turned to blue color as the content is explicit converted to text format from number format.

Hope this article is useful for those who are in need to show leading zeros for number columns in XMLP reports. If you have any best solution, please leave a comment and share with the readers.