Devendrakumar Rajendran - A Blog to Oracle Financials World

Objective Create a list of values to display all accounts receivables transaction numbers based on each operating unit. Sequence a. Enter user defined value set name b. Enter description that is informative according to value set that you display to user c. Select list type as list of values d. Define your own format validation e. Define value validation (Type should of table type) Enter Validation table information (select t.trx_number, trunc(t.trx_date) trx_date, p.party_name, t.customer_trx_id,t.org_id from ra_customer_trx_all t, hz_parties p, hz_cust_accounts_all hca where  hca.party_id = p.party_id and hca.cust_account_id = t.bill_to_customer_id) Define table columns (name, type, and size) In where/order by field - place following piece of code to rely query table results based on operating unit list of value and order by transaction date org_id = :$FLEX$.DCF_OP_UNITS_LOV order by trx_date desc In additional column , use below piece of code to add extra

Now Oracle 12c has brought out a new feature to use function in SQL statement itself rather than implementing function as a separate database object. This helps developers to gain insight on function usage with latest technique and simplifies database object maintenance. Reduces context switching between SQL and PLSQL engine. Faster the results and efficient coding. Sample Snippet: WITH     FUNCTION get_party_name ( p_cust_account_id   IN NUMBER ) RETURN VARCHAR2 IS         l_party   hz_parties.party_name%TYPE;     BEGIN         SELECT p.party_name         INTO l_party         FROM hz_cust_accounts ca, hz_parties p          WHERE ca.party_id = p.party_id               AND ca.cust_account_id = p_cust_account_id;                         RETURN l_party;     EXCEPTION         WHEN OTHERS THEN             NULL;     END; SELECT     trx_number,     trx_date,     get_party_name (bill_to_customer_id) party_name FROM ra_customer_trx_all W

IDENTITY COLUMNS Oracle 12c Release1 introduces identity columns in the tables, if table column is marked as IDENTITY, it will automatically generate an increment numeric value at the time of record creation. Before release of Oracle 12c, developers has to create additional sequence and assign it to the column to make unique. The new feature simplifies code writing and benefits the migration of non-oracle database to oracle /* Create a table for demonstration purpose */ CREATE TABLE invoice_header  (invoice_id NUMBER GENERATED AS IDENTITY, invoice_no VARCHAR2(30), invoice_date DATE, invoice_amt NUMBER ); Identity column metadata can be queried from the data dictionary views USER_TAB_COLS and USER_TAB_IDENTITY_COLS. Note that oracle implicitly creates a sequence to generate the number values of column. The custom sequence configuration is listed under IDENTITY_OPTIONS in USER_TAB_IDENTITY_COLS view: /* Query identity column information in USER_TAB_COL