Insurance Term : 'Aggregate Stop-Loss Insurance'

Definition of 'Aggregate Stop-Loss Insurance'

A policy designed to limit claim coverage (losses) to a specific amount. This type of coverage is to ensure that catastrophic claims (specific stop-loss) or numerous claims (aggregate stop-loss), do not upset the financial reserves of a self-funded plan. Aggregate stop-loss protects the employer against higher-than-expected claims. If total claims exceed the aggregate limit, the stop-loss insurance carrier reimburses the employer.

Example:

A self-funded plan is one in which the employer assumes the financial risk for providing healthcare benefits to its employees. In practical terms, self-funded employers pay for each claim as it is presented instead of paying a fixed premium to an insurance carrier for a fully insured plan. Stop-loss insurance is similar to purchasing high-deductible insurance. The employer remains responsible for claims expense under the deductible amount.

Read more: http://www.investopedia.com/terms/a/aggregate-stop-loss-insurance.asp#ixzz2FiXoMXhI

Oracle : MERGE INTO

Update statement is used to update the records in tables however it become complex task when we need to update the records on JOIN condition with other tables.

MERGE INTO set of commands is efficient way to do the same.

Syntax :
           MERGE INTO tablename
           USING { query| tablename } on { condition }
           [ WHEN MATCHED THEN UPDATE set_clause
                          [ DELETE CONDITION ]
           [ WHEN NOT MATCHED THEN INSERT values_clause ]

Insurance Defination : Damage

If any bad thing happens below are three levels of Damage.

Damage: Actual loss or injury due to accident.

Compensatory Damage: In addition to actual loss or injury, this term may include amounts for expenses, loss of time, bodily suffering and mental suffering

Punitive Damage: Damages awarded over and above compensatory damages to punish a negligent party because of wanton, reckless, or malicious acts or omissions.

Dynamic XFR & DML creation

DMLs and XFRs are consumed and configured when the graph first starts.
If you change any of them during graph execution, the graph will not go
back and get your changes - they will only be visible the next time you
run the graph.

http://datawarehouse.ittoolbox.com/groups/technical-functional/abinitio-l/dynamic-xfr-dml-creation-1595616

Ab Initio : Playing with Vectors

The requirement is prepare the list of Products separated by '~'  for each Customer ID. The sample data looks like below,

Input Data

Cust ID	Product
101	AA
101	AB
101	AC
102	AA
102	AC

Output :

Cust ID	Product
101	AA~AB~AC
102	AA~AC

Solution :
The ROOLUP is used with input SORTED data on Cust ID.

Delimited String Vector is defined and value is assigned to the same by using below functions.

  let  string("\x02", maximum_length=7) [unsigned long] vector_final;
  vector_final = vector_sort_dedup_first(accumulation(in.product) , {machine descending}  );
  out.product:: string_join(vector_final, '~' );

accumulation :   Returns a vector of input values. Available only within the ROLLUP and SCAN components.

vector_sort_dedup_first : Returns a new vector consisting of a sorted version of the input vector, which includes only the first element in each group of elements whose key matches the specified key.

string_join: Concatenates vector string elements into a single string.

Predefined sort sequence modifiers

DML supports the following predefined sort sequence modifiers:

Modifier	Description
phonebook	Treats digits as the lowest-value characters, followed by the letters of the alphabet in the order AaBbCcDd..., followed by spaces. Ignores all other characters, such as punctuation. The order of digits is 0, 1, 2, 3, 4, 5, 6, 7, 8, 9.
index	Orders the same as phonebook, except that punctuation characters have lower values than all other characters and are not ignored. The order of punctuation characters is the machine sequence.
machine	Orders character code values in the sequence in which they are arranged in the character set of the string: ASCII-based character sets — Digits are the lowest-value characters, followed by uppercase letters, followed by lowercase letters. EBCDIC character sets — Lowercase letters are the lowest-value characters, followed by uppercase, followed by digits. Unicode character sets — The order is from the lowest character code value to the highest.

Ab Initio: Null keys in lookup file

NULL-valued arguments in functions

Most built-in functions return NULL when any of their arguments is NULL. Exceptions are:

Function category	How the functions handle NULL values
Lookup functions	When passed a NULL argument to use as a key or subkey, these functions try to match the NULL value to the corresponding key or subkey of a record in the lookup file.
Certain inquiry and error functions	These functions never return NULL, because they are specifically designed to return indicators of NULL or invalid values.

In Short : If there are NULL values in the fields of a lookup file and NULL values in the parameters passed to a lookup function, the values will match and the function will return a record.

Ab Initio : Specifying the length of a string in DML

Specifying the length of a string in DML

When specifying the length of a string in DML — where the length of the string is defined as its number of characters excluding any length prefix or delimiter — you must consider whether its character set uses fixed-size storage or variable-size storage.

Fixed-size character sets are those that use fixed-size storage for characters. Examples of these include ISO-8859-1 and EBCDIC, both of which use 1 byte per character, and UTF-16, which uses 2 bytes per character.

To declare strings using fixed-size character sets, specify the number of characters for the string’s length as follows:

For fixed-length strings, specify the number of characters as fixed_length.

For delimited strings, specify the number of characters as max_len.

For length-prefixed strings, specify the number of characters as type_specifier.

Variable-size character sets are those in which character storage size varies, depending on the character. Example of these include UTF-8, EUC-JP-Unicode-0-9, and Windows-932 (commonly called Shift-JIS).

To declare strings using variable-size character sets, use the number of bytes for the string’s length as follows:

For fixed-length strings, specify the number of bytes as fixed_length.

For delimited strings, specify the number of bytes as max_len.

For length-prefixed strings, specify the number of bytes as type_specifier. Ref : Help FIle

Ab Initio : IN and NOT IN

You can create a vector with the possible values and then use operator
"member" to get that the same functionality in Abinitio. For example, lets
say u have 5 values to compare with, output field name is check_value then
in reformat, u can try as
let decimal("|")[5] temp_vec = [vector 10, 12, 13, 23, 33];
out.check_value :: if(in.field_name member temp_vec) "Yes" else "No".
OR if there are 30 or 40 values ..i mean more data to compare, create a
lookup file and perform the operation.
Ref : http://datawarehouse.ittoolbox.com/groups/technical-functional/abinitio-l/in-and-not-in-2890661

Loading MFS file to Oracle Table

Steps for bulk load in Oracle:

1) Use parallel direct path load using SQL loader with native option =
SKIP_INDEX_MAINTENACE=TRUE (use MFS layout in output table component)
2) You can define a generic db stored procedure to rebuild the indexes on
target table. Otherwise define a graph parameter and construct the index
rebuild sql statement (with online parallel option) by using
m_db command and PDL expression.
3) Use RUN SQL to call the db procedure/SQL parameter(constructed in graph
parameter) in order to rebuild the indexes.
4) analyze the table.

Ref : http://datawarehouse.ittoolbox.com/groups/technical-functional/abinitio-l/loading-mfs-file-to-oracle-table-4968984

SQL *Loader : Direct Load : Description

With the conventional path load method, arrays of rows are inserted with standard sql INSERT statements, integrity constraints and insert triggers are automatically applied. But when you load data with the direct path, SQL*Loader disables some integrity constraints and all database triggers. The constraints that remain in force are:
* NOT NULL
* UNIQUE
* PRIMARY KEY (unique-constraints on not-null columns)
and the following constraints are automatically disabled by default:
* CHECK constraints
* Referential constraints (FOREIGN KEY)

Ab Initio Software Versions

To determine which version of the GDE and Co>Operating System you are using, do one of the following:

•  Choose Help > About Graphical Development Environment from the GDE menu bar

To determine the version of Co>Operating System host connection

• Type m_env -version (or m_env -v) in the dialog’s text box
• m_env -features

An inside view of LOOKUP FILE

The LOOKUP FILE component represents a particular set of lookup data and associates it with a search key and a DML record format. At graph runtime, the Co>Operating System uses this information to load the data from disk into memory. Then your graph’s transform components retrieve records from the memory-resident lookup data using lookup functions.

When to use a LOOKUP FILE

You use a LOOKUP FILE component instead of an INPUT FILE component when your graph needs to access a dataset repeatedly. Retrieving records from main memory is much quicker than retrieving them from disk, so LOOKUP FILE can make graph processing quicker and more efficient. However, you have to make sure that the amount of data associated with the LOOKUP FILE is small enough to be held in main memory.

Static loading only

The LOOKUP FILE component is for statically loaded data only. When you use a LOOKUP FILE, the Co>Operating System (Server) loads data into memory once, at the start of the graph phase. After that, the data in memory cannot be refreshed.

If you need to load lookup data dynamically, use a LOOKUP TEMPLATE component instead of a LOOKUP FILE.

Aditya Ghosh says it is a fatal mistake when professionals begin to think they are the reason for a company's success. "I am what I am today because of the team that surrounds me, I am what I am today because of IndiGo."

Complete Article : http://articles.economictimes.indiatimes.com/2012-06-03/news/31985593_1_aditya-ghosh-indigo-president-kingfisher-airlines

Term  :  Basis Point

1/100th of 1%. For example, 150 basis points = 0.015 = 1.50%

Insurance Term : Renewal Premium

Renewal Premium :

Payment due on the renewal of an insurance policy. The premium may be adjusted up or down to reflect the loss experience of the underwriting classification to which the insured belongs.

This amount to be paid on or before Renewal Date so that Policy Coverage is unaffected.

 

Term : General Ledger

General Ledger : 

The general ledger is the core of company’s financial records. These constitute the central “books” of your system, and every transaction flows through the general ledger. These records remain as a permanent track of the history of all financial transactions since day one of the life of your company.

Each General Ledger is divided into debits and credits sections. The left hand side lists debit transactions and the right hand side lists credit transactions. This gives a 'T' shape to each individual general ledger account.

Debits	Credits

A "T" account showing debits on the left and credits on the right.

Oracle 11g Analytical Function : ListAgg

Hi All,

To show single row output from multiple rows, we do Pivot operations. I learnt today the new function called as ListAgg.

The syntax is
  listagg (<column_name_to_be_concated >, 'field_separator')
WITHIN GROUP --Notes : If you are mentoning the Group By Cause
(ORDER BY (<column_name_to_be_concated > ) aliase_name
For Eg.
Input Data is
Policy No  Policy Type
12  CA
12  BA
12 AB
13 AB

Output by Grouping the records on Policy No is
12  'AB-BA-CA'
13  'AB'