Oracle Date and time stampe

In and around Oracle Application, as a developer you need to play with Dates while writing your code. Playing with dates is fun, although sometimes we faced some challenging tasks. Therefore it is always helpful to go through this feature of Oracle in detail. I did the same and here is what I have found.
Oracle database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. The default display and input format for any date is DD-MON-YY. Valid Oracle dates are between January 1, 4712 B.C. and December 31, 9999 A.D.
Unlike other datatypes, DATE datatypes are bit complex. However, Oracle Database and PL/SQL provide a set of true date and time datatypes that store both date and time information in a standard internal format, and they also have an extensive set of built-in functions for manipulating the date and time.
Get the current date and time:
Often it is required to retrieve the current date and time in our code and use them. Many developers go with SYSDATE function, but Oracle Database now offers several other functions as well.

SELECT SYSDATE FROM DUAL;

--27-FEB-12 (You will get the DATE from Oracle Database Server)

SELECT CURRENT_DATE FROM DUAL;

--27-FEB-12 (You will get the Current DATE from Oracle Session Time Zone)

SELECT LOCALTIMESTAMP FROM DUAL;

--27-FEB-12 01.16.42.486809000 AM (You will get the TIMESTAMP from Oracle Session Time Zone)

SELECT SYSTIMESTAMP FROM DUAL;

--27-FEB-12 01.19.41.059413000 AM -06:00 (You will get the TIMESTAMP WITH TIME ZONE from Oracle Database Server)

SELECT CURRENT_TIMESTAMP FROM DUAL;

--27-FEB-12 01.20.57.839733000 AM AMERICA/CHICAGO (You will get the TIMESTAMP WITH TIME ZONE from Oracle Session Time Zone)

DATE Format:

When a DATE value is displayed, Oracle must first convert that value from the special internal format to a printable string. The conversion is done by a function TO_CHAR, according to a DATE format. Oracle’s default format for DATE is “DD-MON-YY”. Whenever a DATE value is displayed, Oracle will call TO_CHAR automatically with the default DATE format. However, you may override the default behavior by calling TO_CHAR explicitly with your own DATE format.
Guidelines

• The format model must be enclosed in single quotation marks and is case sensitive.
• The format model can include any valid date format element. Be sure to separate the date value from the format model by a comma.
• The names of days and months in the output are automatically padded with blanks.
• To remove padded blanks or to suppress leading zeros, use the fill mode fm element.

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY YY Y	Last 3, 2, or 1 digit(s) of year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds.

SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd') FROM DUAL; -- Returns 2012/02/27

SELECT TO_CHAR(SYSDATE, 'Month DD, YYYY') FROM DUAL; -- Returns February  27, 2012

SELECT TO_CHAR(SYSDATE, 'FMMonth DD, YYYY') FROM DUAL; -- Returns February 27, 2012

SELECT TO_CHAR(SYSDATE, 'MON DDth, YYYY') FROM DUAL; -- Returns FEB 27TH, 2012

SELECT TO_CHAR(SYSDATE, 'FMMON DDth, YYYY') FROM DUAL; -- Returns FEB 27TH, 2012

SELECT TO_CHAR(SYSDATE, 'FMMonth DD, YYYY') FROM DUAL; -- Returns February 27, 2012

SELECT TO_CHAR(SYSDATE, 'FMDay, DDth Month YYYY') FROM DUAL; -- Returns Monday, 27TH February 2012

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL; -- Returns 2012-02-27 02:05:59

SELECT TO_CHAR(SYSDATE, 'FMDdspth "of" Month YYYY FMHH:MI:SS AM') FROM DUAL; -- Returns Twenty-Seventh of February 2012 02:06:36 AM

SELECT TO_CHAR(SYSDATE, 'FMDay, DDth Month YYYY','NLS_DATE_LANGUAGE=Spanish') FROM DUAL; -- Returns Lunes, 27TH Febrero 2012

SELECT TO_CHAR(SYSDATE, 'Day') TODAY_EN,
TO_CHAR(sysdate, 'Day', 'nls_date_language=Dutch') TODAY_DT
FROM DUAL; -- Returns Monday    Maandag

TO_DATE & TO_TIMESTAMP Functions:
 
TO_DATE function converts a character string representing a date to a date value according to the fmt specified. If fmt is omitted, the format is DD-MON-YY. The nlsparams parameter has the same purpose in this function as in the TO_CHAR function for date conversion.
TO_DATE(char,[fmt],[nlsparams])

The DD-MON-YY format is usually used to insert a date value. If a date must be entered in a format other than the default format, for example, with another century, or a specific time, you must use the TO_DATE function.

Examples:

SELECT TO_DATE('2012/02/27', 'yyyy/mm/dd') FROM DUAL;
SELECT TO_DATE('022712', 'MMDDYY') FROM DUAL;
SELECT TO_DATE('20120227', 'yyyymmdd') FROM DUAL;
SELECT TO_DATE('February 27, 2012, 04:00 P.M.','Month dd, YYYY, HH:MI A.M.')FROM DUAL;

Similarly TO_TIMESTAMP used to convert char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype.

Examples:

 SELECT TO_TIMESTAMP('2012/FEB/27 04:12:34', 'YYYY/MON/DD HH:MI:SS') FROM DUAL;
SELECT TO_TIMESTAMP('February 27, 2012, 04:12:34 P.M.','Month dd, YYYY, HH:MI:SS A.M.')FROM DUAL;
SELECT TO_TIMESTAMP ('27-Feb-12 04:12:34.123000', 'DD-Mon-RR HH24:MI:SS.FF') FROM DUAL;

Arithmetic with Dates:

Here are 3 golden roles:

1. You can compare DATE values using the standard comparison operators such as =, !=, >, etc.
2. You can subtract two DATE values, and the result is a FLOAT which is the number of days between the two DATE values. In general, the result may contain a fraction because DATE also has a time component. For obvious reasons, adding, multiplying, and dividing two DATE values are not allowed.
3. You can add and subtract constants to and from a DATE value, and these numbers will be interpreted as numbers of days. For example, SYSDATE+1 will be tomorrow. You cannot multiply or divide DATE values.

Date Functions:
Date functions operate on Oracle dates. All date functions return a value of DATE data type except MONTHS_BETWEEN, which returns a numeric value.

• MONTHS_BETWEEN (date1, date2):
This function returns the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.

• ADD_MONTHS(date, n):
This function adds n number of calendar months to date. The value of n must be an integer and can be negative.

• NEXT_DAY(date, ‘char’):
This function finds the date of the next specified day of the week (‘char’) following date. The value of char may be a number representing a day or a character string.

• LAST_DAY(date):
This function finds the date of the last day of the month that contains date.

• ROUND(date[,’fmt’]):
This function returns date rounded to the unit specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day.

• TRUNC(date[, ‘fmt’]):
This function returns date with the time portion of the day truncated to the unit specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.

Examples:

 SELECT SYSTIMESTAMP FROM DUAL;  --Returns 27-FEB-12 05.16.41.676947000 AM -06:00
SELECT MONTHS_BETWEEN(SYSDATE , TO_DATE('01-JAN-2012','DD-MON-YYYY')) FROM DUAL; --Returns months between Sysdate and '01-JAN-2012' (1.84580906511350059737156511350059737157)
SELECT ADD_MONTHS (SYSDATE, 1) FROM DUAL; -- Move ahead one month (27-MAR-12)
SELECT ADD_MONTHS (SYSDATE, -4) FROM DUAL; -- Move backward four months (27-OCT-11)
SELECT NEXT_DAY (SYSDATE, 'MONDAY') FROM DUAL;  -- Go to next Monday after today’s date (05-MAR-12)
SELECT LAST_DAY (SYSDATE) FROM DUAL;  -- Returns the last day of the month (29-FEB-12)
SELECT ROUND (SYSDATE, 'MONTH') FROM DUAL; --01-MAR-12
SELECT TRUNC (SYSDATE, 'MONTH') FROM DUAL; --01-FEB-12
SELECT ROUND (SYSDATE, 'YEAR') FROM DUAL;  --01-JAN-12
SELECT TRUNC (SYSDATE, 'YEAR') FROM DUAL;  --01-JAN-12
SELECT ROUND (SYSDATE, 'DAY') FROM DUAL;   --26-FEB-12
SELECT TRUNC (SYSDATE, 'DAY') FROM DUAL;   --26-FEB-12

EXTRACT Function:
An EXTRACT datetime function extracts and returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation
The syntax of EXTRACT function is
EXTRACT ( YEAR / MONTH / WEEK / DAY / HOUR / MINUTE / TIMEZONE  FROM DATE)

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; --Returns 2017

SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; --Returns 3

SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; --Returns 27

SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) FROM DUAL; --Returns 24

SELECT EXTRACT(MINUTE FROM SYSTIMESTAMP) FROM DUAL; --Returns 41

R12 Customer and Supp;ier Bank Accounts in oracle apps

 The data model for storing Banks and Bank Account information has changed for this release of the Oracle Applications Suite.

Banks and their Branches are now each stored as Parties (in HZ_PARTIES) in their own right. They are linked together through Relationships (in HZ_RELATIONSHIP). There is a separate link for both Bank to Branch and also from Branch to Bank.

The Bank Accounts themselves are now stored in the new Oracle Payments Application. All tables are prefixed with the Application Short Name, IBY. The bank accounts themselves are stored in the IBY_EXT_BANK_ACCOUNTS table. The bank_id and branch_id fields link the Bank Account to the relevant Bank and Branch Parties in the HZ_PARTIES table.

Now, linking the Bank Account to the relevant Customer is a bit more involved. The table IBY_ACCOUNT_OWNERS can be used to identify the Customer Party (using the ext_bank_account_id to link the 2 IBY tables) that the Bank Account belongs to. Identifying the Customer Site that the Bank Account is actually attached to is a little bit trickier!

This is done through linking together the following tables IBY_EXTERNAL_PAYERS_ALL and IBY_PMT_INSTR_USES_ALL. A record is created in the Payment Instrument Uses table IBY_PMT_INSTR_USES_ALL for each assignment of a Bank Account. This record is linked to the bank account by matching the ext_bank_account_id to the instrument_id. Now, each Instrument Record links to an External Payer Record held in IBY_EXTERNAL_PAYERS_ALL using the ext_pmt_party_id. It is the External Payer Record that links us to a customer and Customer Site Use.

There is a record stored in the IBY_EXTERNAL_PAYERS_ALL table for every customer site use defined. The IBY_PMT_INSTR_USES_ALL is a pointer to the specific Site Use that the Bank Account has been assigned to.

The following query gives you the links required for matching a Bank Account to its Customer Site Record:

/* Formatted on 3/27/2017 4:38:20 PM (QP5 v5.114.809.3010) */
SELECT   cust.party_name customer_name,
         cust_acct.account_number,
         cust_uses.site_use_code,
         cust_loc.address1,
         cust_loc.address2,
         cust_loc.address3,
         cust_loc.address4,
         cust_loc.city,
         cust_loc.postal_code,
         bank.party_name bank_name,
         bank_prof.home_country,
         branch.party_name branch_name,
         branch_prof.bank_or_branch_number branch_number,
         account.bank_account_num,
         account.bank_account_name
  FROM   hz_parties bank,
         hz_relationships rel,
         hz_parties branch,
         hz_organization_profiles bank_prof,
         hz_organization_profiles branch_prof,
         iby_ext_bank_accounts account,
         iby_account_owners acc_owner,
         iby_external_payers_all ext_payer,
         iby_pmt_instr_uses_all acc_instr,
         hz_parties cust,
         hz_cust_accounts cust_acct,
         hz_cust_acct_sites_all cust_site,
         hz_cust_site_uses_all cust_uses,
         hz_locations cust_loc
 WHERE       1 = 1
         AND bank.party_id = rel.object_id
         AND bank.party_type = rel.object_type
         AND rel.object_table_name = 'HZ_PARTIES'
         AND rel.relationship_code = 'BRANCH_OF'
         AND rel.subject_id = branch.party_id
         AND rel.subject_type = branch.party_type
         AND rel.subject_table_name = 'HZ_PARTIES'
         AND bank.party_id = bank_prof.party_id
         AND branch.party_id = branch_prof.party_id
         AND bank.party_id = account.bank_id
         AND branch.party_id = account.branch_id
         AND account.ext_bank_account_id = acc_owner.ext_bank_account_id
         AND acc_owner.account_owner_party_id = cust.party_id
         AND account.ext_bank_account_id = acc_instr.instrument_id
         AND acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id
         AND ext_payer.cust_account_id = cust_acct.cust_account_id
         AND cust_acct.cust_account_id = cust_site.cust_account_id
         AND cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
         AND cust_uses.site_use_id = ext_payer.acct_site_use_id
         AND cust_uses.location = cust_loc.location_id
         AND cust.party_id = cust_acct.party_id; 

The following query gives you the links required for matching a Bank Account to its Supplier Site Record

SELECT party_supp.party_name supplier_name

, aps.segment1 supplier_number

, ass.vendor_site_code supplier_site

, ieb.bank_account_num

, ieb.bank_account_name

, party_bank.party_name bank_name

, branch_prof.bank_or_branch_number bank_number

, party_branch.party_name branch_name

, branch_prof.bank_or_branch_number branch_number

FROM hz_parties party_supp

, ap_suppliers aps

, hz_party_sites site_supp

, ap_supplier_sites_all ass

, iby_external_payees_all iep

, iby_pmt_instr_uses_all ipi

, iby_ext_bank_accounts ieb

, hz_parties party_bank

, hz_parties party_branch

, hz_organization_profiles bank_prof

, hz_organization_profiles branch_prof

WHERE party_supp.party_id = aps.party_id

AND party_supp.party_id = site_supp.party_id

AND site_supp.party_site_id = ass.party_site_id

AND ass.vendor_id = aps.vendor_id

AND iep.payee_party_id = party_supp.party_id

AND iep.party_site_id = site_supp.party_site_id

AND iep.supplier_site_id = ass.vendor_site_id

AND iep.ext_payee_id = ipi.ext_pmt_party_id

AND ipi.instrument_id = ieb.ext_bank_account_id

AND ieb.bank_id = party_bank.party_id

AND ieb.bank_id = party_branch.party_id

AND party_branch.party_id = branch_prof.party_id

AND party_bank.party_id = bank_prof.party_id

ORDER BY party_supp.party_name

, ass.vendor_site_code;

Query to find accounting flexfield structure in oracle apps

/* Formatted on 3/27/2017 4:33:18 PM (QP5 v5.114.809.3010) */
  SELECT   sob.name Ledger_Name,
           sob.ledger_id Ledger_Id,
           sob.chart_of_accounts_id coa_id,
           fifst.id_flex_structure_name struct_name,
           ifs.segment_name,
           ifs.application_column_name column_name,
           sav1.attribute_value BALANCING,
           sav2.attribute_value COST_CENTER,
           sav3.attribute_value NATURAL_ACCOUNT,
           sav4.attribute_value INTERCOMPANY,
           sav5.attribute_value SECONDARY_TRACKING,
           sav6.attribute_value GLOBAL,
           ffvs.flex_value_set_name,
           ffvs.flex_value_set_id
    FROM   fnd_id_flex_structures fifs,
           fnd_id_flex_structures_tl fifst,
           fnd_segment_attribute_values sav1,
           fnd_segment_attribute_values sav2,
           fnd_segment_attribute_values sav3,
           fnd_segment_attribute_values sav4,
           fnd_segment_attribute_values sav5,
           fnd_segment_attribute_values sav6,
           fnd_id_flex_segments ifs,
           fnd_flex_value_sets ffvs,
           gl_ledgers sob
   WHERE       1 = 1
           AND fifs.id_flex_code = 'GL#'
           AND fifs.application_id = fifst.application_id
           AND fifs.id_flex_code = fifst.id_flex_code
           AND fifs.id_flex_num = fifst.id_flex_num
           AND fifs.application_id = ifs.application_id
           AND fifs.id_flex_code = ifs.id_flex_code
           AND fifs.id_flex_num = ifs.id_flex_num
           AND sav1.application_id = ifs.application_id
           AND sav1.id_flex_code = ifs.id_flex_code
           AND sav1.id_flex_num = ifs.id_flex_num
           AND sav1.application_column_name = ifs.application_column_name
           AND sav2.application_id = ifs.application_id
           AND sav2.id_flex_code = ifs.id_flex_code
           AND sav2.id_flex_num = ifs.id_flex_num
           AND sav2.application_column_name = ifs.application_column_name
           AND sav3.application_id = ifs.application_id
           AND sav3.id_flex_code = ifs.id_flex_code
           AND sav3.id_flex_num = ifs.id_flex_num
           AND sav3.application_column_name = ifs.application_column_name
           AND sav4.application_id = ifs.application_id
           AND sav4.id_flex_code = ifs.id_flex_code
           AND sav4.id_flex_num = ifs.id_flex_num
           AND sav4.application_column_name = ifs.application_column_name
           AND sav5.application_id = ifs.application_id
           AND sav5.id_flex_code = ifs.id_flex_code
           AND sav5.id_flex_num = ifs.id_flex_num
           AND sav5.application_column_name = ifs.application_column_name
           AND sav6.application_id = ifs.application_id
           AND sav6.id_flex_code = ifs.id_flex_code
           AND sav6.id_flex_num = ifs.id_flex_num
           AND sav6.application_column_name = ifs.application_column_name
           AND sav1.segment_attribute_type = 'GL_BALANCING'
           AND sav2.segment_attribute_type = 'FA_COST_CTR'
           AND sav3.segment_attribute_type = 'GL_ACCOUNT'
           AND sav4.segment_attribute_type = 'GL_INTERCOMPANY'
           AND sav5.segment_attribute_type = 'GL_SECONDARY_TRACKING'
           AND sav6.segment_attribute_type = 'GL_GLOBAL'
           AND ifs.id_flex_num = sob.chart_of_accounts_id
           AND ifs.flex_value_set_id = ffvs.flex_value_set_id
           AND sob.ledger_id =
                 NVL (fnd_profile.VALUE ('GL_SET_OF_BKS_ID'), sob.ledger_id)
ORDER BY   sob.name, sob.chart_of_accounts_id, ifs.application_column_name;

Oracle GL Account Code Combination ID’s (CCID’s) through APIs

1] FND_FLEX_EXT.GET_COMBINATION_ID:

This API Finds combination_id for given set of key flexfield segment values. Segment values must be input in segments(1) – segments(n_segments) in the order displayed. 

It also creates a new combination if it is valid and the flexfield allows dynamic inserts and the combination does not already exist. It commit the transaction soon after calling this function since if a combination is created it will prevent other users creating similar combinations on any flexfield until a commit is issued.

It performs all checks on values including security and cross-validation. Value security rules will be checked for the current user identified in the FND_GLOBAL package. 

Generally pass in SYSDATE for validation date. If validation date is null, this function considers expired values valid and checks all cross-validation rules even if they are outdated.

This function returns TRUE if combination valid or FALSE and sets error message using FND_MESSAGE utility on error or if invalid. If this function returns FALSE, use GET_MESSAGE to get the text of the error message in the language of the database, or GET_ENCODED_MESSAGE to get the error message in a language-independent encoded format.
 
The Combination_id output may be NULL if combination is invalid.

/* Formatted on 3/27/2017 4:30:27 PM (QP5 v5.114.809.3010) */
SET serveroutput ON;

DECLARE
   l_application_short_name   VARCHAR2 (240);
   l_key_flex_code            VARCHAR2 (240);
   l_structure_num            NUMBER;
   l_validation_date          DATE;
   n_segments                 NUMBER;
   SEGMENTS                   APPS.FND_FLEX_EXT.SEGMENTARRAY;
   l_combination_id           NUMBER;
   l_data_set                 NUMBER;
   l_return                   BOOLEAN;
   l_message                  VARCHAR2 (240);
BEGIN
   l_application_short_name := 'SQLGL';
   l_key_flex_code := 'GL#';

   SELECT   id_flex_num
     INTO   l_structure_num
     FROM   apps.fnd_id_flex_structures
    WHERE   ID_FLEX_CODE = 'GL#'
            AND ID_FLEX_STRUCTURE_CODE = :ACCOUNTING_FLEXFIELD;

   l_validation_date := SYSDATE;
   n_segments := 6;
   segments (1) := '00101';
   segments (2) := '28506';
   segments (3) := '00000';
   segments (4) := '09063';
   segments (5) := '00000';
   segments (6) := '00000';
   l_data_set := NULL;

   l_return :=
      FND_FLEX_EXT.GET_COMBINATION_ID (
         application_short_name   => l_application_short_name,
         key_flex_code            => l_key_flex_code,
         structure_number         => l_structure_num,
         validation_date          => l_validation_date,
         n_segments               => n_segments,
         segments                 => segments,
         combination_id           => l_combination_id,
         data_set                 => l_data_set
      );
   l_message := FND_FLEX_EXT.GET_MESSAGE;

   IF l_return
   THEN
      DBMS_OUTPUT.PUT_LINE ('l_Return = TRUE');
      DBMS_OUTPUT.PUT_LINE ('COMBINATION_ID = ' || l_combination_id);
   ELSE
      DBMS_OUTPUT.PUT_LINE ('Error: ' || l_message);
   END IF;
END;

2] FND_FLEX_EXT.get_ccid:

This API gets combination id for the specified key flexfield segments.It is identical to get_combination_id() except this function takes segment values in a string concatenated by the segment  delimiter for this flexfield, and returns a positive combination id if valid or 0 on error.

3] FND_FLEX_KEYVAL.VALIDATE_SEGS:

These key flexfields server validations API are a low level interface to key flexfields validation.  They are designed to allow access to all the flexfields functionality, and to allow the user to get only the information they need in return.  Because of their generality, these functions are more difficult to use than those in the FND_FLEX_EXT package.  Oracle strongly suggests using the functions in FND_FLEX_EXT package if at all possible.
This function finds combination from given segment values.  Segments are passed in as a concatenated string in increasing order of segment_number (display order).
Various Operations that can be performed are:

• ‘FIND_COMBINATION’ – Combination must already exist.
• ‘CREATE_COMBINATION’ – Combination is created if doesn’t exist.
• ‘CREATE_COMB_NO_AT’ – same as create_combination but does not use an autonomous transaction.
• ‘CHECK_COMBINATION’ – Checks if combination valid, doesn’t create.
• ‘DEFAULT_COMBINATION’ – Returns minimal default combination.
• ‘CHECK_SEGMENTS’ – Validates segments individually.

If validation date is NULL checks all cross-validation rules. It returns TRUE if combination valid or FALSE and sets error message on server if invalid. Use the default values if you do not want any special functionality.

/* Formatted on 3/27/2017 4:31:51 PM (QP5 v5.114.809.3010) */
SET serveroutput ON;

DECLARE
   l_segment1            GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
   l_segment2            GL_CODE_COMBINATIONS.SEGMENT2%TYPE;
   l_segment3            GL_CODE_COMBINATIONS.SEGMENT3%TYPE;
   l_segment4            GL_CODE_COMBINATIONS.SEGMENT4%TYPE;
   l_segment5            GL_CODE_COMBINATIONS.SEGMENT5%TYPE;
   l_segment6            GL_CODE_COMBINATIONS.SEGMENT6%TYPE;
   l_valid_combination   BOOLEAN;
   l_cr_combination      BOOLEAN;
   l_ccid                GL_CODE_COMBINATIONS_KFV.code_combination_id%TYPE;
   l_structure_num       FND_ID_FLEX_STRUCTURES.ID_FLEX_NUM%TYPE;
   l_conc_segs           GL_CODE_COMBINATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;
   p_error_msg1          VARCHAR2 (240);
   p_error_msg2          VARCHAR2 (240);
BEGIN
   l_segment1 := '00101';
   l_segment2 := '28506';
   l_segment3 := '00000';
   l_segment4 := '14302';
   l_segment5 := '00455';
   l_segment6 := '00000';
   l_conc_segs :=
         l_segment1
      || '.'
      || l_segment2
      || '.'
      || l_segment3
      || '.'
      || l_segment4
      || '.'
      || l_segment5
      || '.'
      || l_segment6;
   BEGIN
      SELECT   id_flex_num
        INTO   l_structure_num
        FROM   apps.fnd_id_flex_structures
       WHERE   id_flex_code = 'GL#'
               AND id_flex_structure_code = 'EPC_GL_ACCOUNTING_FLEXFIELD';
   EXCEPTION
      WHEN OTHERS
      THEN
         l_structure_num := NULL;
   END;
   ---------------Check if CCID exits with the above Concatenated Segments---------------
   BEGIN
      SELECT   code_combination_id
        INTO   l_ccid
        FROM   apps.gl_code_combinations_kfv
       WHERE   concatenated_segments = l_conc_segs;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_ccid := NULL;
   END;

   IF l_ccid IS NOT NULL
   THEN
      ------------------------The CCID is Available----------------------
      DBMS_OUTPUT.PUT_LINE ('COMBINATION_ID= ' || l_ccid);
   ELSE
      DBMS_OUTPUT.PUT_LINE (
         'This is a New Combination. Validation Starts....'
      );
      ------------Validate the New Combination--------------------------
      l_valid_combination :=
         APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS (
            operation          => 'CHECK_COMBINATION',
            appl_short_name    => 'SQLGL',
            key_flex_code      => 'GL#',
            structure_number   => L_STRUCTURE_NUM,
            concat_segments    => L_CONC_SEGS
         );
      p_error_msg1 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

      IF l_valid_combination
      THEN
         DBMS_OUTPUT.PUT_LINE (
            'Validation Successful! Creating the Combination...'
         );
         -------------------Create the New CCID--------------------------

         L_CR_COMBINATION :=
            APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS (
               operation          => 'CREATE_COMBINATION',
               appl_short_name    => 'SQLGL',
               key_flex_code      => 'GL#',
               structure_number   => L_STRUCTURE_NUM,
               concat_segments    => L_CONC_SEGS
            );
         p_error_msg2 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

         IF l_cr_combination
         THEN
            -------------------Fetch the New CCID--------------------------
            SELECT   code_combination_id
              INTO   l_ccid
              FROM   apps.gl_code_combinations_kfv
             WHERE   concatenated_segments = l_conc_segs;

            DBMS_OUTPUT.PUT_LINE ('NEW COMBINATION_ID = ' || l_ccid);
         ELSE
            -------------Error in creating a combination-----------------
            DBMS_OUTPUT.PUT_LINE (
               'Error in creating the combination: ' || p_error_msg2
            );
         END IF;
      ELSE
         --------The segments in the account string are not defined in gl value set----------
         DBMS_OUTPUT.PUT_LINE (
            'Error in validating the combination: ' || p_error_msg1
         );
      END IF;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (SQLCODE || ' ' || SQLERRM);
END;

How to design An Accounting Flexfield Parameter for your Report

If you want to design similar functionality as below in your report parameter, do the below steps.

1] Design two parameters to capture Accounting Flexfield From and To and use them in your report. Also set the default values.

Here use the value set: XLA_SRS_ACCOUNTING_FLEXFIELD
2] XLA_SRS_ACCOUNTING_FLEXFIELD valueset is designed as below:

Edit:

FND POPIDR APPL_SHORT_NAME=”SQLGL” CODE=”GL#”
  NUM=:$FLEX$.XLA_SRS_CHART_OF_ACCOUNTS REQUIRED=”Y”
  VALIDATE=”NONE” SEG=”:!VALUE” DESC=”:!MEANING”
  NAVIGATE=”!DIR” VDATE=””

Validate:

FND VALIDR APPL_SHORT_NAME=”SQLGL” CODE=”GL#”
  NUM=:$FLEX$.XLA_SRS_CHART_OF_ACCOUNTS VALIDATE=”NONE”
  REQUIRED=”Y” DESC=”:!MEANING” SEG=”:!VALUE” VDATE=””

How to create concurrent programs from database in oracle apps

1)    Registering the Executable from back-end:
Usually we create executable in the front-end, but this can be done from the database tier i.e. back-end too. Below is the PL/SQL code to create an executable from back-end.

/* Formatted on 3/27/2017 4:02:08 PM (QP5 v5.114.809.3010) */
BEGIN
   FND_PROGRAM.executable (
      executable            => 'XXFIN TEST EXECUTABLE',     -- Executable Name
      application           => 'XXFIN',              -- Application Short Name
      short_name            => 'XXFINTSTEXE',         -- Executable Short Name
      description           => 'Test Executable created from Backend', -- Description,DEFAULT NULL
      execution_method      => 'PL/SQL Stored Procedure',  -- Execution Method
      execution_file_name   => 'XXFIN_TEST_PROC', -- Execution File Name,DEFAULT NULL
      subroutine_name       => NULL,           -- Subroutine Name,DEFAULT NULL
      icon_name             => NULL,                 -- Icon Name,DEFAULT NULL
      language_code         => 'US',             -- Language Code,DEFAULT 'US'
      execution_file_path   => NULL       -- Execution File Path, DEFAULT NULL
   );
   COMMIT;
END;


View from Front end:








Notes:
1] The above API inserts the new records in FND_EXECUTABLES and FND_EXECUTABLES_TL table.
2] You can use the below query to get all the Execution Methods available:

SELECT MEANING “Execution Method”
FROM fnd_lookup_values
WHERE lookup_type = ‘CP_EXECUTION_METHOD_CODE’
AND enabled_flag  = ‘Y’;

2)    Registering the Concurrent program from back-end:      

Usually we create Concurrent program in the front-end, but this can be done from the database tier too. Below is the program to create a Concurrent program from back-end.

/* Formatted on 3/27/2017 4:04:29 PM (QP5 v5.114.809.3010) */
BEGIN
   FND_PROGRAM.register (program                    => 'Test CP from DB', -- CP Name
                         application                => 'XXFIN', -- Application Short Name
                         enabled                    => 'Y',
                         -- Flag to Enable/Disable a CP
                         short_name                 => 'XXFINTSTCPDB', -- CP Short Name
                         description                => 'Test CP created from Backend',
                         -- Description,DEFAULT NULL
                         executable_short_name      => 'XXFINTSTEXE', -- Executable Short Name
                         executable_application     => 'XXFIN', -- Executable Application Short Name
                         execution_options          => NULL,
                         -- Execution Options,DEFAULT NULL,
                         priority                   => NULL, -- Priority,DEFAULT NULL,
                         save_output                => 'Y',
                         -- Save Output,DEFAULT 'Y',
                         PRINT                      => 'Y', -- Print,DEFAULT 'Y',
                         cols                       => NULL,  -- DEFAULT NULL,
                         rows                       => NULL,  -- DEFAULT NULL,
                         style                      => NULL,  -- DEFAULT NULL,
                         style_required             => 'N',    -- DEFAULT 'N',
                         printer                    => NULL,  -- DEFAULT NULL,
                         request_type               => NULL,  -- DEFAULT NULL,
                         request_type_application   => NULL,  -- DEFAULT NULL,
                         use_in_srs                 => 'N',    -- DEFAULT 'N',
                         allow_disabled_values      => 'N',    -- DEFAULT 'N',
                         run_alone                  => 'N',    -- DEFAULT 'N',
                         output_type                => 'TEXT', -- DEFAULT 'TEXT'
                         enable_trace               => 'N',    -- DEFAULT 'N',
                         restart                    => 'Y',    -- DEFAULT 'Y',
                         nls_compliant              => 'Y',    -- DEFAULT 'Y',
                         icon_name                  => NULL,  -- DEFAULT NULL,
                         language_code              => 'US',  -- DEFAULT 'US',
                         mls_function_short_name    => NULL,  -- DEFAULT NULL,
                         mls_function_application   => NULL,  -- DEFAULT NULL,
                         incrementor                => NULL,  -- DEFAULT NULL,
                         refresh_portlet            => NULL   -- DEFAULT NULL,
                                                           );
   COMMIT;
END;

View from Front end:



Notes:

1] The various output types are ‘PS’, ‘PDF’, ‘HTML’, ‘TEXT’, ‘PCL’, ‘XML’.
2] The above API inserts the new records in fnd_concurrent_programs and FND_CONCURRENT_PROGRAMS_TL

3)    Attaching the concurrent program to the request group

Usually we Attach Concurrent program to the request group in the front-end, but this can be done from database tier too. Below is the program to Attach Concurrent program to the request group from back-end.

/* Formatted on 3/27/2017 4:06:15 PM (QP5 v5.114.809.3010) */
BEGIN
   FND_PROGRAM.add_to_group ('XXFINTSTCPDB',  -- Concurrent Program Short Name
                             'XXFIN',                -- Application Short Name
                             'All Reports',               -- Report Group Name
                             'SQLAP');             -- Report Group Application
   COMMIT;
END;

Apart from these APIs, the above package also contains to create/delete parameters, delete executable, and delete concurrent programs and all. 

API to update and assign Project Roles in an Oracle Project

Oracle has provided a seeded package called PA_PROJECT_PARTIES_PUB to create, update or delete a project party (or Key member) in an oracle project. From front end, the navigation is Project Billing Super User (or related responsibility) > Projects > Find Projects > Open > Options > Key Members. The records in the form are displayed through a view (PA_PROJECT_PLAYERS) and the base table is PA_PROJECT_PARTIES.

PA_PROJECT_PARTIES_PUB.UPDATE_PROJECT_PARTY:

/* Formatted on 3/27/2017 2:43:17 PM (QP5 v5.114.809.3010) */
DECLARE
   l_project_id           PA_PROJECT_PARTIES.PROJECT_ID%TYPE := NULL;
   l_project_role         VARCHAR2 (240) := NULL;
   l_resource_name        PER_ALL_PEOPLE_F.FULL_NAME%TYPE := NULL;
   l_start_date_active    DATE := NULL;
   l_end_date_active      DATE := NULL;
   l_project_role_id      pa_project_role_types.PROJECT_ROLE_ID%TYPE := NULL;
   l_project_role_type    pa_project_role_types.PROJECT_ROLE_TYPE%TYPE := NULL;
   l_resource_source_id   PA_PROJECT_PARTIES.RESOURCE_SOURCE_ID%TYPE := NULL;
   l_project_party_id     PA_PROJECT_PARTIES.PROJECT_PARTY_ID%TYPE := NULL;
   l_object_id            PA_PROJECT_PARTIES.OBJECT_ID%TYPE := NULL;
   l_resource_id          PA_PROJECT_PARTIES.RESOURCE_ID%TYPE := NULL;
   l_record_version_number pa_project_parties.record_version_number%TYPE
         := NULL ;
   l_project_end_date     DATE;
   l_return_status        VARCHAR2 (20) := NULL;
   l_assignment_id        NUMBER := NULL;
   l_wf_type              VARCHAR2 (240) := NULL;
   l_wf_item_type         VARCHAR2 (240) := NULL;
   l_wf_process           VARCHAR2 (240) := NULL;
   l_msg_count            NUMBER := NULL;
   l_msg_data             VARCHAR2 (240) := NULL;
BEGIN
   ---Input Parameters----
   l_project_id := '7033';
   l_project_role := 'Project Manager';
   l_resource_name := 'Koch, Dibyajyoti';
   l_start_date_active := '24-NOV-2011';
   l_end_date_active := '24-NOV-2012';

   SELECT   PROJECT_ROLE_ID, PROJECT_ROLE_TYPE
     INTO   l_project_role_id, l_project_role_type
     FROM   PA_PROJECT_ROLE_TYPES
    WHERE   UPPER (MEANING) = UPPER (l_project_role);

   SELECT   DISTINCT PERSON_ID
     INTO   l_resource_source_id
     FROM   PER_ALL_PEOPLE_F
    WHERE   UPPER (FULL_NAME) = UPPER (l_resource_name);

   SELECT   PROJECT_PARTY_ID,
            OBJECT_ID,
            RESOURCE_ID,
            RECORD_VERSION_NUMBER
     INTO   l_project_party_id,
            l_object_id,
            l_resource_id,
            l_record_version_number
     FROM   PA_PROJECT_PARTIES
    WHERE       PROJECT_ID = l_project_id
            AND PROJECT_ROLE_ID = l_project_role_id
            AND RESOURCE_SOURCE_ID = l_resource_source_id;

   l_project_end_date :=
      pa_project_dates_utils.get_project_finish_date (l_project_id);

   PA_PROJECT_PARTIES_PUB.UPDATE_PROJECT_PARTY (
      P_API_VERSION                    => 1.0,
      P_INIT_MSG_LIST                  => FND_API.G_TRUE,
      P_COMMIT                         => FND_API.G_FALSE,
      P_VALIDATE_ONLY                  => FND_API.G_FALSE,
      P_VALIDATION_LEVEL               => FND_API.G_VALID_LEVEL_FULL,
      P_DEBUG_MODE                     => 'N',
      P_OBJECT_ID                      => l_object_id,
      P_OBJECT_TYPE                    => 'PA_PROJECTS',
      P_PROJECT_ROLE_ID                => l_project_role_id,
      P_PROJECT_ROLE_TYPE              => l_project_role_type,
      P_RESOURCE_TYPE_ID               => 101,                      --EMPLOYEE
      P_RESOURCE_SOURCE_ID             => l_resource_source_id,
      P_RESOURCE_NAME                  => l_resource_name,
      P_RESOURCE_ID                    => l_resource_id,
      P_START_DATE_ACTIVE              => l_start_date_active,
      P_SCHEDULED_FLAG                 => 'N',
      P_RECORD_VERSION_NUMBER          => l_record_version_number,
      P_CALLING_MODULE                 => FND_API.G_MISS_CHAR,
      P_PROJECT_ID                     => l_project_id,
      P_PROJECT_END_DATE               => l_project_end_date,
      P_PROJECT_PARTY_ID               => l_project_party_id,
      P_ASSIGNMENT_ID                  => NULL,
      P_ASSIGN_RECORD_VERSION_NUMBER   => l_record_version_number + 1,
      P_MGR_VALIDATION_TYPE            => 'FORM',
      P_END_DATE_ACTIVE                => l_end_date_active,
      X_ASSIGNMENT_ID                  => l_assignment_id,
      X_WF_TYPE                        => l_wf_type,
      X_WF_ITEM_TYPE                   => l_wf_item_type,
      X_WF_PROCESS                     => l_wf_process,
      X_RETURN_STATUS                  => l_return_status,
      X_MSG_COUNT                      => l_msg_count,
      x_msg_data                       => l_msg_data
   );
   COMMIT;
   DBMS_OUTPUT.PUT_LINE ('Status:' || l_return_status);
   DBMS_OUTPUT.PUT_LINE ('Message:' || l_msg_data);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Try Again!!');
END;

PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY:

/* Formatted on 3/27/2017 2:44:48 PM (QP5 v5.114.809.3010) */
DECLARE
   l_project_id           PA_PROJECT_PARTIES.PROJECT_ID%TYPE := NULL;
   l_project_role         VARCHAR2 (240) := NULL;
   l_resource_name        PER_ALL_PEOPLE_F.FULL_NAME%TYPE := NULL;
   l_start_date_active    DATE := NULL;
   l_end_date_active      DATE := NULL;
   l_project_role_id      pa_project_role_types.PROJECT_ROLE_ID%TYPE := NULL;
   l_project_role_type    pa_project_role_types.PROJECT_ROLE_TYPE%TYPE := NULL;
   l_resource_source_id   PA_PROJECT_PARTIES.RESOURCE_SOURCE_ID%TYPE := NULL;
   l_project_party_id     PA_PROJECT_PARTIES.PROJECT_PARTY_ID%TYPE := NULL;
   l_object_id            PA_PROJECT_PARTIES.OBJECT_ID%TYPE := NULL;
   l_resource_id          PA_PROJECT_PARTIES.RESOURCE_ID%TYPE := NULL;
   l_record_version_number pa_project_parties.record_version_number%TYPE
         := NULL ;
   l_project_end_date     DATE;
   l_return_status        VARCHAR2 (20) := NULL;
   l_assignment_id        NUMBER := NULL;
   l_wf_type              VARCHAR2 (240) := NULL;
   l_wf_item_type         VARCHAR2 (240) := NULL;
   l_wf_process           VARCHAR2 (240) := NULL;
   l_msg_count            NUMBER := NULL;
   l_msg_data             VARCHAR2 (240) := NULL;
BEGIN
   ---Input Parameters----
   l_project_id := '7033';
   l_project_role := 'Project Accountant';
   l_resource_name := 'Koch, Dibyajyoti';
   l_start_date_active := '24-NOV-2011';
   l_end_date_active := '24-NOV-2012';

   SELECT   PROJECT_ROLE_ID, PROJECT_ROLE_TYPE
     INTO   l_project_role_id, l_project_role_type
     FROM   PA_PROJECT_ROLE_TYPES
    WHERE   UPPER (MEANING) = UPPER (l_project_role);

   SELECT   DISTINCT PERSON_ID
     INTO   l_resource_source_id
     FROM   PER_ALL_PEOPLE_F
    WHERE   UPPER (FULL_NAME) = UPPER (l_resource_name);

   l_project_end_date :=
      pa_project_dates_utils.get_project_finish_date (l_project_id);

   PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY (
      P_API_VERSION           => 1.0,
      P_INIT_MSG_LIST         => FND_API.G_TRUE,
      P_COMMIT                => FND_API.G_FALSE,
      P_VALIDATE_ONLY         => FND_API.G_FALSE,
      P_VALIDATION_LEVEL      => FND_API.G_VALID_LEVEL_FULL,
      P_DEBUG_MODE            => 'N',
      P_OBJECT_ID             => l_project_id,
      P_OBJECT_TYPE           => 'PA_PROJECTS',
      P_PROJECT_ROLE_ID       => l_project_role_id,
      P_PROJECT_ROLE_TYPE     => l_project_role_type,
      P_RESOURCE_TYPE_ID      => 101,                               --EMPLOYEE
      P_RESOURCE_SOURCE_ID    => l_resource_source_id,
      P_RESOURCE_NAME         => l_resource_name,
      P_START_DATE_ACTIVE     => l_start_date_active,
      P_SCHEDULED_FLAG        => 'N',
      P_CALLING_MODULE        => NULL,
      P_PROJECT_ID            => l_project_id,
      P_PROJECT_END_DATE      => l_project_end_date,
      P_MGR_VALIDATION_TYPE   => 'FORM',
      P_END_DATE_ACTIVE       => l_end_date_active,
      X_PROJECT_PARTY_ID      => l_project_party_id,
      X_RESOURCE_ID           => l_resource_id,
      X_ASSIGNMENT_ID         => l_assignment_id,
      X_WF_TYPE               => l_wf_type,
      X_WF_ITEM_TYPE          => l_wf_item_type,
      X_WF_PROCESS            => l_wf_process,
      X_RETURN_STATUS         => l_return_status,
      X_MSG_COUNT             => l_msg_count,
      X_MSG_DATA              => l_msg_data
   );
   COMMIT;
   DBMS_OUTPUT.PUT_LINE ('Status:' || l_return_status);
   DBMS_OUTPUT.PUT_LINE ('Message:' || l_msg_data);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Try Again!!');
END;  

Project & Change Order Approval Action History in oracle projcts query

/* Formatted on 3/27/2017 2:42:17 PM (QP5 v5.114.809.3010) */
SELECT   ROWNUM,
         ACTION_DATE,
         ACTION,
         from_user,
         from_role,
         to_user,
         to_role,
         Details,
         SEQUENCE,
         NOTIFICATION_ID,
         ACTION_TYPE
  FROM   (  SELECT   ACTION_DATE,
                     ACTION,
                     from_user,
                     from_role,
                     to_user,
                     to_role,
                     Details,
                     SEQUENCE,
                     NOTIFICATION_ID,
                     ACTION_TYPE
              FROM   (SELECT   c.comment_date DATE1,
                               TO_CHAR (c.comment_date,
                                        'DD-MON-RRRR HH24:MI:SS')
                                  action_date,
                               c.action action,
                               c.from_user from_user,
                               c.from_role from_role,
                               c.to_user to_user,
                               c.to_role to_role,
                               c.user_comment Details,
                               C.SEQUENCE SEQUENCE,
                               C.NOTIFICATION_ID NOTIFICATION_ID,
                               C.ACTION_TYPE ACTION_TYPE
                        FROM   WF_NOTIFICATIONS WFN,
                               pa_wf_processes pa,
                               wf_item_activity_statuses wfitems,
                               wf_comments c
                       WHERE       wfitems.notification_id = wfn.GROUP_ID
                               AND wfitems.NOTIFICATION_ID = C.NOTIFICATION_ID
                               AND pa.item_type = wfitems.item_type
                               AND pa.item_key = wfitems.item_key
                               AND pa.entity_key1 = :p_project_id
                               AND pa.item_type IN ('PAPROWF', 'PAWFCISC')
                      UNION ALL
                      SELECT   c.comment_date DATE1,
                               TO_CHAR (c.comment_date,
                                        'DD-MON-RRRR HH24:MI:SS')
                                  action_date,
                               c.action action,
                               c.from_user from_user,
                               c.from_role from_role,
                               c.to_user to_user,
                               c.to_role to_role,
                               c.user_comment Details,
                               C.SEQUENCE SEQUENCE,
                               C.NOTIFICATION_ID NOTIFICATION_ID,
                               C.ACTION_TYPE ACTION_TYPE
                        FROM   WF_NOTIFICATIONS WFN,
                               pa_wf_processes pa,
                               WF_ITEM_ACTIVITY_STATUSES_H wfitems,
                               wf_comments c
                       WHERE       wfitems.notification_id = wfn.GROUP_ID
                               AND wfitems.NOTIFICATION_ID = C.NOTIFICATION_ID
                               AND pa.item_type = wfitems.item_type
                               AND pa.item_key = wfitems.item_key
                               AND pa.entity_key1 = :p_project_id
                               AND pa.item_type IN ('PAPROWF', 'PAWFCISC'))
          ORDER BY   DATE1 DESC, notification_id, sequence);