Query to find Parameters and Value Sets associated with a Concurrent Program

1. SELECT
2. fcpl.user_concurrent_program_name "Concurrent Program Name",
3. fcp.concurrent_program_name "Short Name",
4. fdfcuv.column_seq_num "Column Seq Number",
5. fdfcuv.end_user_column_name "Parameter Name",
6. fdfcuv.form_left_prompt "Prompt",
7. fdfcuv.enabled_flag " Enabled Flag",
8. fdfcuv.required_flag "Required Flag",
9. fdfcuv.display_flag "Display Flag",
10. fdfcuv.flex_value_set_id "Value Set Id",
11. ffvs.flex_value_set_name "Value Set Name",
12. flv.meaning "Default Type",
13. fdfcuv.DEFAULT_VALUE "Default Value"
14. FROM
15. fnd_concurrent_programs fcp,
16. fnd_concurrent_programs_tl fcpl,
17. fnd_descr_flex_col_usage_vl fdfcuv,
18. fnd_flex_value_sets ffvs,
19. fnd_lookup_values flv
20. WHERE
21. fcp.concurrent_program_id = fcpl.concurrent_program_id
22. AND fcpl.user_concurrent_program_name = :conc_prg_name
23. AND fdfcuv.descriptive_flexfield_name = '$SRS$.'
24. || fcp.concurrent_program_name
25. AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
26. AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
27. AND flv.lookup_code(+) = fdfcuv.default_type
28. AND fcpl.LANGUAGE = USERENV ('LANG')
29. AND flv.LANGUAGE(+) = USERENV ('LANG')
30. ORDER BY fdfcuv.column_seq_num;

Date convert Function

Have created very useful function to convert date format.

We are using it to take the date format from user's preferences and changing it to standard format.




CREATE OR REPLACE FUNCTION APPS.XX_DATE_CONVERT (p_date_format     IN VARCHAR2,
                                               P_PROMISED_DATE      VARCHAR2)
   RETURN VARCHAR2
IS
   p_return_promise_date   VARCHAR2 (100);
BEGIN
   IF p_date_format = 'MM-DD-RRRR'
   THEN
      p_return_promise_date :=
         TO_DATE(TO_CHAR (TO_DATE (P_PROMISED_DATE, 'MM-DD-RRRR HH24:MI:SS'),
                          'DD-MON-RRRR'));
   ELSIF p_date_format = 'DD-MM-RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'DD-MM-RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'MM/DD/RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'MM/DD/RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'MM.DD.RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'MM.DD.RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'DD-MON-RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'DD-MON-RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'DD.MM.RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'DD.MM.RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'DD/MM/RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'DD/MM/RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'DD/MON/RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'DD/MON/RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'DD.MON.RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'DD.MON.RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'RRRR-MM-DD'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'RRRR-MM-DD HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'RRRR-MON-DD'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'RRRR-MON-DD HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'RRRR/MON/DD'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'RRRR/MON/DD HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'RRRR/MM/DD'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'RRRR/MM/DD HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'RRRR.MM.DD'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'RRRR.MM.DD HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'RRRR.MON.DD'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'RRRR.MON.DD HH24:MI:SS'),
                  'DD-MON-RRRR');
   END IF;

   RETURN p_return_promise_date;
EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (
         -20001,
         'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM
      );
END;
/

This can be used like :-

XXNHR_DATE_CONVERT (fnd_profile.value('ICX_DATE_FORMAT_MASK'),:PO_SHIPMENTS.PROMISED_DATE)