INV Item Category API

Following APIs can be used to Create/Update/Delete Category Combinations

INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_REC IN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE, X_CATEGORY_ID OUT NUMBER)

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_REC IN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE)

INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_ID IN NUMBER)

Following API is used for assigning a Category to a Category Set. A Category will be available in the list of valid categories for a category set only if it is assigned to the category set. This is a required step for categories if enforce list is checked on
INV_ITEM_CATEGORY_PUB.CREATE_VALID_CATEGORY
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
P_CATEGORY_SET_ID IN NUMBER,
P_CATEGORY_ID IN NUMBER,
P_PARENT_CATEGORY_ID IN NUMBER,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2)

Following APIs can be used to Create/Update/Delete Item Category Assignments

INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY_ASSIGNMENT
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_ID IN NUMBER,
P_CATEGORY_SET_ID IN NUMBER,
P_INVENTORY_ITEM_ID IN NUMBER,
P_ORGANIZATION_ID IN NUMBER)

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_DESCRIPTION
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_ID IN NUMBER,
P_DESCRIPTION IN VARCHAR2)

INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY_ASSIGNMENT
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_ID IN NUMBER,
P_CATEGORY_SET_ID IN NUMBER,
P_INVENTORY_ITEM_ID IN NUMBER,
P_ORGANIZATION_ID IN NUMBER)

AR Customer API

Record Type Variable Declaration
v_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
v_customer_account_rec HZ_CUST_ACCOUNT_V2PUB.cust_account_rec_type;
v_party_rec HZ_PaRTY_V2PUB.party_rec_type;
v_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
v_location_rec hz_location_v2pub.location_rec_type;
v_party_site_rec HZ_PARTY_SITE_V2PUB.party_site_rec_type;
v_party_site_use_rec HZ_PARTY_SITE_V2PUB.party_site_use_rec_type;
v_customer_profile_amt HZ_CUSTOMER_PROFILE_V2PUB.cust_profile_amt_rec_type;
v_cust_acct_site_rec HZ_CUST_ACCOUNT_SITE_V2PUB.cust_acct_site_rec_type;
v_customer_site_use_rec HZ_CUST_ACCOUNT_SITE_V2PUB.cust_site_use_rec_type;
v_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
v_phone_rec hz_contact_point_v2pub.phone_rec_type;
v_person_rec hz_party_v2pub.person_rec_type;
v_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;

Note- Columns values are assigned to record type variable from various Cursor Staging Tables

-- Create the Party Organization Record
v_organization_rec.organization_name := rec_party.customer_name;
v_organization_rec.party_rec.orig_system_reference := rec_party.orig_system_reference ;
v_organization_rec.party_rec.attribute2 := rec_party.attribute2;
v_organization_rec.party_rec.attribute3 := rec_party.attribute3;
v_organization_rec.party_rec.attribute4 := rec_party.attribute4;
v_organization_rec.party_rec.attribute5 := rec_party.attribute5;
v_organization_rec.created_by_module := 'TCA_V1_API';

-- Create Customer Account Record
v_customer_account_rec.account_name:= rec_party.customer_name;
v_customer_account_rec.account_number:= rec_party.customer_number;
v_customer_account_rec.orig_system_reference := rec_party.orig_system_reference;
v_customer_account_rec.created_by_module:='TCA_V1_API';

-- Create Customer Profile Record
v_customer_profile_rec.party_id := v_party_id;
v_customer_profile_rec.status := rec_profile.status;
v_customer_profile_rec.credit_checking := rec_profile.credit_checking;
v_customer_profile_rec.tolerance := rec_profile.tolerance;
v_customer_profile_rec.discount_terms := rec_profile.discount_terms;
v_customer_profile_rec.dunning_letters := rec_profile.dunning_letters;
v_customer_profile_rec.interest_charges := rec_profile.interest_charges;
v_customer_profile_rec.credit_balance_statements := rec_profile.credit_balance_statements;
v_customer_profile_rec.credit_hold := rec_profile.credit_hold;
v_customer_profile_rec.credit_rating := rec_profile.credit_rating;
v_customer_profile_rec.risk_code := rec_profile.risk_code;
v_customer_profile_rec.override_terms := rec_profile.override_terms;
v_customer_profile_rec.payment_grace_days := rec_profile.payment_grace_days;
v_customer_profile_rec.discount_grace_days := rec_profile.discount_grace_days;
v_customer_profile_rec.account_status := rec_profile.account_status;
v_customer_profile_rec.auto_rec_incl_disputed_flag := rec_profile.auto_rec_incl_disputed_flag;
v_customer_profile_rec.tax_printing_option := rec_profile.tax_printing_option;
v_customer_profile_rec.jgzz_attribute5 := rec_profile.jgzz_attribute5;
v_customer_profile_rec.jgzz_attribute6 := rec_profile.jgzz_attribute6;
v_customer_profile_rec.created_by_module := 'TCA_V1_API';

Below API creates record for Party organization, customer account and customer profile all in one

HZ_CUST_ACCOUNT_V2PUB.create_cust_account
(
p_init_msg_list =>FND_API.G_FALSE,
p_cust_account_rec => v_customer_account_rec, -- Customer Account Record
p_organization_rec => v_organization_rec, -- Party Organization Record
p_customer_profile_rec => v_customer_profile_rec, -- Customer Profile Record
p_create_profile_amt => FND_API.G_FALSE,
x_cust_account_id => v_customer_account_id,
x_account_number => v_customer_account_number,
x_party_id => v_party_id,
x_party_number => v_party_number,
x_profile_id => v_profile_id,
x_return_status =>v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);

-- Create Customer Profile Amount Record
v_customer_profile_amt.cust_account_profile_id := v_cust_account_profile_id;
v_customer_profile_amt.cust_account_id := v_customer_account_id;
v_customer_profile_amt.currency_code := rec_customer_prof_amt_staging.currency_code;
v_customer_profile_amt.trx_credit_limit := rec_customer_prof_amt_staging.trx_credit_limit;
v_customer_profile_amt.overall_credit_limit := rec_customer_prof_amt_staging.overall_credit_limit;
v_customer_profile_amt.min_dunning_amount := rec_customer_prof_amt_staging.min_dunning_amount;
v_customer_profile_amt.min_statement_amount := rec_customer_prof_amt_staging.min_statement_amount;
v_customer_profile_amt.created_by_module := 'TCA_V1_API';

HZ_CUSTOMER_PROFILE_V2PUB.create_cust_profile_amt
(
p_init_msg_list => 'T' ,
p_check_foreign_key => FND_API.G_TRUE,
p_cust_profile_amt_rec => v_customer_profile_amt,
x_cust_acct_profile_amt_id => v_cust_act_prof_amt_id,
x_return_status =>v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);

-- Create Location Record
v_location_rec.address1 := rec_addresses.address1;
v_location_rec.address2 := rec_addresses.address2;
v_location_rec.address3 := rec_addresses.address3;
v_location_rec.address4 := rec_addresses.address4;
v_location_rec.city := rec_addresses.city;
v_location_rec.country := rec_addresses.country;
v_location_rec.orig_system_reference := rec_addresses.orig_address_reference;
v_location_rec.postal_code := rec_addresses.postal_code;
v_location_rec.created_by_module :='TCA_V1_API';

HZ_LOCATION_V2PUB.create_location
(
p_init_msg_list => 'T',
p_location_rec => v_location_rec,
x_location_id => v_location_id,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);

-- Create Party Site Record
v_party_site_rec.party_id := v_party_id;
v_party_site_rec.location_id := v_location_id;
v_party_site_rec.orig_system_reference := rec_addresses.orig_address_reference;
v_party_site_id := NULL;
v_party_site_rec.created_by_module := 'TCA_V1_API';

HZ_PARTY_SITE_V2PUB.create_party_site
(
p_init_msg_list => 'T',
p_party_site_rec => v_party_site_rec,
x_party_site_id => v_party_site_id,
x_party_site_number => v_party_site_no,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);

-- Create Contact Point and Phone Record
v_contact_point_rec.contact_point_type := 'PHONE';
v_contact_point_rec.status := rec_phones.status;
v_contact_point_rec.owner_table_name := 'HZ_PARTIES';
v_contact_point_rec.owner_table_id := v_party_id;
v_contact_point_rec.primary_flag := rec_phones.primary_flag;
v_contact_point_rec.orig_system_reference := rec_phones.orig_system_reference;
v_contact_point_rec.created_by_module := 'TCA_V1_API';
v_phone_rec.phone_number := rec_phones.phone_number;
v_phone_rec.phone_line_type := rec_phones.phone_type;

HZ_CONTACT_POINT_V2PUB.create_contact_point
(
p_init_msg_list => 'T',
p_contact_point_rec => v_contact_point_rec,
p_phone_rec => v_phone_rec,
x_contact_point_id => v_contact_point_party_id,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);

-- Create Person Record
v_person_rec.person_first_name := rec_contacts.first_name;
v_person_rec.person_last_name := rec_contacts.last_name;
v_person_rec.party_rec.orig_system_reference := rec_contacts.orig_system_reference;
v_person_rec.created_by_module := 'TCA_V1_API';

HZ_PARTY_V2PUB.create_person
(
p_init_msg_list => 'T',
p_person_rec => v_person_rec,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_party_id => v_person_party_id,
x_party_number => v_person_party_number,
x_profile_id => v_profile_id
);

-- Create Org Contact Record

v_org_contact_rec.created_by_module := 'TCA_V1_API';
v_org_contact_rec.job_title := rec_contacts.job_title;
v_org_contact_rec.party_rel_rec.subject_id := v_person_party_id ;
v_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
v_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
v_org_contact_rec.party_rel_rec.object_id := v_party_id ;
v_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
v_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
v_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
v_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
v_org_contact_rec.party_rel_rec.start_date := SYSDATE;

HZ_PARTY_CONTACT_V2PUB.create_org_contact
(
p_init_msg_list => 'T',
p_org_contact_rec => v_org_contact_rec,
x_org_contact_id => v_org_contact_id,
x_party_rel_id => v_party_rel_id,
x_party_id => v_party_contact_id,
x_party_number => v_party_rel_number,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
) ;

-- Create Party Site Use Record

v_party_site_use_rec.site_use_type := rec_customer_sites_staging.site_use_code;
v_party_site_use_rec.party_site_id := v_party_site_id;
v_party_site_use_rec.primary_per_type := rec_customer_sites_staging.primary_flag;
v_party_site_use_rec.status := rec_customer_sites_staging.status;
v_party_site_use_rec.created_by_module := 'TCA_V1_API';

HZ_PARTY_SITE_V2PUB.create_party_site_use
(
p_init_msg_list => 'T',
p_party_site_use_rec => v_party_site_use_rec,
x_party_site_use_id => v_party_site_use_id ,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);

-- Create Customer Account Site Record
v_cust_acct_site_rec.cust_account_id := v_customer_account_id;
v_cust_acct_site_rec.party_site_id := v_party_site_id;
v_cust_acct_site_rec.orig_system_reference := rec_addresses.orig_address_reference;
v_cust_acct_site_rec.status := rec_customer_sites_staging.status;
v_cust_acct_site_rec.created_by_module := 'TCA_V1_API';

HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_acct_site
(
p_init_msg_list => 'T',
p_cust_acct_site_rec=> v_cust_acct_site_rec,
x_cust_acct_site_id => v_cust_acct_site_id,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);

-- Create Customer Account Site Use Record
v_customer_site_use_rec.cust_acct_site_id := v_cust_acct_site_id;
v_customer_site_use_rec.site_use_code := rec_customer_sites_staging.site_use_code;
v_customer_site_use_rec.status := rec_customer_sites_staging.status;
v_customer_site_use_rec.orig_system_reference :=rec_addresses.orig_address_reference;
v_customer_site_use_rec.primary_flag :=rec_customer_sites_staging.primary_flag;
v_customer_site_use_rec.created_by_module := 'TCA_V1_API';

HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_site_use
(
p_init_msg_list => 'T',
p_cust_site_use_rec => v_customer_site_use_rec,
p_customer_profile_rec => v_customer_profile_rec,
p_create_profile => FND_API.G_FALSE,
p_create_profile_amt => FND_API.G_FALSE,
x_site_use_id => v_site_use_id,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);

-- Create Customer Profile Amount Record

v_customer_profile_amt := NULL;
v_customer_profile_amt.cust_account_profile_id := v_cust_account_profile_id;
v_customer_profile_amt.cust_account_id := v_customer_account_id;
v_customer_profile_amt.currency_code := rec_cust_prof_amt_stage_sites.currency_code;
v_customer_profile_amt.trx_credit_limit := rec_cust_prof_amt_stage_sites.trx_credit_limit;
v_customer_profile_amt.overall_credit_limit := rec_cust_prof_amt_stage_sites.overall_credit_limit;
v_customer_profile_amt.min_dunning_amount := rec_cust_prof_amt_stage_sites.min_dunning_amount;
v_customer_profile_amt.min_statement_amount := rec_cust_prof_amt_stage_sites.min_statement_amount;
v_customer_profile_amt.site_use_id := v_site_use_id;
v_customer_profile_amt.created_by_module := 'TCA_V1_API';

HZ_CUSTOMER_PROFILE_V2PUB.create_cust_profile_amt
(
p_init_msg_list => 'T' ,
p_check_foreign_key => FND_API.G_TRUE,
p_cust_profile_amt_rec => v_customer_profile_amt,
x_cust_acct_profile_amt_id => v_cust_act_prof_amt_id,
x_return_status =>v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);

-- If you want to Create Organization separately in some other program, use below API for reference

HZ_PARTY_V2PUB.create_organization
(
p_init_msg_list => 'T',
p_organization_rec => v_organization_rec,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_party_id => v_party_id,
x_party_number => v_party_number,
x_profile_id => v_profile_id
);

AR-GL Reconciliation

The "AR Reconciliation Report" shows the summary amounts of various AR reports, that are used to reconcile AR. This report mainly has 3 parts
a) Period Beginning Balance
b) Period Activity in different Areas and their differences
c) Period Ending Balance

AR is reconciled when the
"Aging on the beginning of the Period" +
"TheTotal Activity in the Period" =
"Aging on the end of thePeriod"

Total Activity in a period is calculated as follows:Transaction Register for the Period
(-)Applied Receipts Register for the Period
(-)Un-Applied Receipts Register for the Period
(+)Adjustments Register for the Period
(-)Invoice Exceptions for the Period
(+)Rounding Differences for the Period (*)
(+)Credit Memo gain/loss for the Period(*)

(*) These can occur only in the case of foreign currency transactions.

AP-GL Reconciliation

Month End reconciliation between GL and AP is highly recommended. If AP is interfaced to GL, verifying the balance between the two applications is usually done through comparing account balances of the liability (A/P) account.

To reconcile your accounts payable activity for April, make the following calculation:
"Accounts Payable Trial Balance" as of March 31 +
"Posted Invoice Register" for the period between April 1 and April 30 -
"Posted Payment Register" for the period between April 1 and April 30 =
"Accounts Payable Trial Balance" as of April 30

> If total is not matching you will need to find out the root casue of difference:
Eg. Invalidated Invoice for the period
> For the PTD activity check the "period Close Exception" if there are any invoices and payments which are not transferred to GL.
> If the current period does not reconcile, please complete the reconciliation process for all prior periodsfrom the most recent to the earliest until you get to one that reconciles.

Common Reasons for balance mismatch between AP & GL
1) Manual journal entries in the general ledger that involve an AP liability account will cause the AP Trialbalance not to reconcile to the GL. These entries are not included in the AP subledger so they will not be reflected on the AP Trial Balance Report.
Run the "GL Account Analysis" report for the liability account and for the date range in question. Look for transactions with a source other than Payables. This can quickly pinpoint any transactions incorrectly charged to the account.

2) You performed a datafix in the past where you used the undo accounting script and swept a transaction forward from a closed period to reaccount it, this will cause an imbalance between AP and GL. The imbalancewill be corrected in the period in which you made a GL adjustment to account for the fix.

3) Any correction you make during the journal import process will result in the line being changed in the general ledger, but not in AP

4) If you have deleted any AP batches or lines from AP batches out of the GL Interface, this will cause AP and GL to be out of balance

5) If the AP batch is still in the GL Interface, it will not be reflected in the GL reports and this will cause a difference between AP and GL.

6) Any AP batches that are unposted in GL will cause a difference between AP and GL

SQL*Loader Program Code

Below Parameters must be passed to this program while calling/submitting

p_job_name- Concurrent Program Short Name
p_directory- Server Path where data file resides
p_file_name- File name (.csv, .txt, etc)

FUNCTION submit_loader
(p_job_name IN VARCHAR2,
p_directory IN VARCHAR2,
p_file_name IN VARCHAR2)
RETURN BOOLEAN
IS
v_request_id NUMBER;
v_job_finished BOOLEAN := FALSE;
v_conc_request BOOLEAN := FALSE;
e_invalid_job_submission EXCEPTION;
e_job_error EXCEPTION;
e_job_failed EXCEPTION;
e_invalid_records EXCEPTION;
v_stage NUMBER (2) := 4;
v_phase VARCHAR2 (100);
v_request_status VARCHAR2 (100);
v_dev_phase VARCHAR2 (100);
v_dev_status VARCHAR2 (100);
v_message VARCHAR2 (100);
v_return BOOLEAN := FALSE;
BEGIN
fnd_file.put_line (fnd_file.LOG, 'P_JOB_NAME-' p_job_name);
v_request_id :=
fnd_request.submit_request
(application => 'XXCUST',
program => p_job_name,
sub_request => FALSE,
argument1 => p_directory '/' p_file_name);
/* Test to see if job submission has failed (0) */
IF v_request_id = 0 THEN
RAISE e_invalid_job_submission;
ELSIF v_request_id <> 0 -- successfully submitted
THEN
COMMIT;
/* Wait until concurrent job has completed */
WHILE NOT v_job_finished
LOOP
v_conc_request :=
fnd_concurrent.wait_for_request
(v_request_id,
30, -- Seconds between
31, -- Max Time to wait for
v_phase,
v_request_status,
v_dev_phase,
v_dev_status,
v_message);

v_job_finished := (v_conc_request = FALSE) OR (v_conc_request = TRUE AND v_dev_phase = 'COMPLETE');
END LOOP;

fnd_file.put_line (fnd_file.LOG, '*************************');
fnd_file.put_line (fnd_file.LOG, 'Dev Phase: ' v_dev_phase);
fnd_file.put_line (fnd_file.LOG, 'Dev Status: ' v_dev_status);
fnd_file.put_line (fnd_file.LOG, '*************************');

IF (NOT v_conc_request) OR (v_conc_request AND v_dev_phase = 'COMPLETE' AND v_dev_status = 'ERROR') -- v_dev_status <> 'NORMAL')
THEN
RAISE e_job_error;
END IF;
END IF;

v_return := TRUE;
RETURN v_return;
EXCEPTION
WHEN e_invalid_job_submission
THEN
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, '*******************************');
fnd_file.put_line (fnd_file.LOG, 'Error.');
fnd_file.put_line (fnd_file.LOG, 'The Concurrent Manager was unable to submit the SQL*Loader Concurrent job.');
fnd_file.put_line (fnd_file.LOG, '********************************');
fnd_file.put_line (fnd_file.LOG, ' ');
RETURN FALSE;

WHEN e_job_error
THEN
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, '*******************************');
fnd_file.put_line (fnd_file.LOG, 'Error.');
fnd_file.put_line (fnd_file.LOG, 'The SQL*Loader Concurrent job failed to complete successfully.');
fnd_file.put_line (fnd_file.LOG, '*******************************');
fnd_file.put_line (fnd_file.LOG, ' ');
RETURN FALSE;

WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, '*******************************');
fnd_file.put_line (fnd_file.LOG, 'Error.');
fnd_file.put_line (fnd_file.LOG, 'The SQL*Loader Concurrent job submission failed with the error : ');
fnd_file.put_line (fnd_file.LOG, SUBSTR (SQLERRM, 1, 300));
fnd_file.put_line (fnd_file.LOG, '*******************************');
fnd_file.put_line (fnd_file.LOG, ' ');
RETURN FALSE;
END submit_loader;

Bulk Collect

Bulk Collect is used to fetch and load large volumes of data. This makes program to run faster but consume more memory. Below is an example to load all entries from all_objects view into temp table.

CREATE OR REPLACE PROCEDURE BULK_COLLECT_QUERY
IS
TYPE sOwner IS TABLE OF VARCHAR2(30);
TYPE sName IS TABLE OF VARCHAR2(30);
TYPE sType IS TABLE OF VARCHAR2(19);
l_sOwner sOwner;
l_sName sName;
l_sType sType;
BEGIN
dbms_output.put_line(‘Before Bulk Collect: ‘ systimestamp);
SELECT owner, object_name, object_type BULK COLLECT INTO l_sOwner, l_sName, l_sType FROM all_objects;
dbms_output.put_line(‘After Bulk Collect: ‘ systimestamp);
----
FORALL indx IN l_sName.FIRST..l_sName.LAST
INSERT INTO temp values (l_sOwner(indx), l_sName(indx), l_sType(indx));
EXIT WHEN l_sName.count = 0;
----
dbms_output.put_line(‘After FORALL: ‘ systimestamp);
COMMIT;
END;

Below are few tips to keep in mind while using Bulk Collect in a Program

1) It is always safe (that is, you will never raise a NO_DATA_FOUND exception) to iterate through a collection from 1 to collection.COUNT when it has been filled with BULK COLLECT

2) Always check the contents of the collection (with the COUNT method) inside Loop to see if there are more rows to process

3) Never use EXIT WHEN Collection%NOTFOUND, instead you can use EXIT WHEN Cursor%NOTFOUND. Ignore the values returned by the cursor attribute- %NOTFOUND

If the volume of data is very large, use Bulk collect with limit clause to split the collection into chunks of data and thereby consume less memory. Below is an example to bulk collect the data in a batch of 10000 records to avoid memory issues.

DECLARE
 TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;
l_tab t_bulk_collect_test_tab;
CURSOR c_data IS
 SELECT * FROM bulk_collect_test;
BEGIN
 OPEN c_data;
 LOOP
 FETCH c_data
 BULK COLLECT INTO l_tab LIMIT 10000;
-- Process contents of collection here.
dbms_output.put_line(l_tab.count || ‘ rows’);
EXIT WHEN c_data%NOTFOUND;
 END LOOP
 CLOSE c_data;
END;
 

Form Building in APPS

1) The TEMPLATE form is the required starting point for all development of new forms

2) Copy TEMPLATE.fmb file from $AU_TOP/forms/US folder to a local directory

3) Copy All PL/SQL Libraries from $AU_TOP/resource folder & APPSTAND.fmb file from $AU_TOP/forms/US folder to desktop path where Form is installed

4) PRE-FORM trigger Contains below code. Modify all the values from ( ). This values are displayed in Front-end menu- 'Help > About Oracle Applications'.

FND_STANDARD.FORM_INFO ('$Revision: (Number)$',
'(Form Name)', '(Application Shortname)', '($Date: (YY/MM/DD HH24:MI:SS) $', '$Author: (developer name) $');
APP_STANDARD.EVENT('PRE–FORM');
APP_WINDOW.SET_WINDOW_POSITION('WINDOWNAME', 'FIRST_WINDOW');

Remember below points while modifying Pre-Form Trigger
a) Modify the application short name. The application short name controls which application’s online help file is accessed when the user presses the window help button on the toolbar.
b) Give the form name is the user form name (form title). This is for your reference only, and is not used elsewhere.
c) Oracle Corporation uses a source control system that automatically updates the values beginning with ”$”. If you are not using it, you should modify those values with your own development information.
d) Modify the APP_WINDOW call to use your own block name (your first block) instead of BLOCKNAME.
e) Do not modify the string FIRST_WINDOW

5) APP_CUSTOM is a PL/SQL package which contains below two procedures
a) open_window
b) close_window
Remember below points while modifying App_Custom Package

a) You can add your own logic in any of the above procedures for opening & closing of the form window
b) You must include the name of your first form window in close_window procedure

6) Property classes are sets of attributes that can be applied to almost anyOracle Forms object.
The TEMPLATE form automatically contains property classes, via references to APPSTAND, that enforce standard cosmetic appearances and behaviors for all widgets and containers

7) Below steps are used to attach Standard Calendar to date text item,

a) Attach Property class to date field as TEXT_ITEM_DATE
b) Attach LOV as ENABLE_LIST_LAMP
c) Create KEY-LISTVAL item level trigger & add following code into it calendar.show;

8) Form coding Standards are as below:

a) Create the item handler procedures in program units and call the procedures from specified item level triggers. This applies to all items except display-only.
b) Use default value property for all items when user first creates a new record in oracle forms designer.
c) Always use APP_ITEM_PROPERTY.SET_PROPERTY to set the field properties DISPLAYED, ENABLED, ENTERABLE, ALTERABLE, INSERT_ALLOWED, UPDATEABLE, NAVIGABLE, REQUIRED & ICON_NAME.
d) Never use Oracle Form built in SET_ITEM_PROPERTY to set the field properties.
e) Do not use FND_GLOBAL routine to set WHO columns, as this are the stored procedures in the database and would cause extra roundtrips to the database.
f) Use FND_STANDARD.SET_WHO in block level trigger PRE-INSERT & PRE- UPDATE to set WHO COLUMNS.
g) Do not create Menu for any of the forms in Oracle Applications. The default menu with menu entries common to all forms is contained in $AU_TOP/resource/US folder as FNDMENU file. Do not ever modify this file.
h) Always use CUSTOM.pll to add additional menu entries in customized form

9) Form deployment steps are as below:

a) Rename the form module name as file name and save it.
b) Transfer the customized form (.fmb file) in binary mode to $AU_TOP/forms/US folder using FTP command
c) Compile the form using 'f60gen' command as below example:
f60gen Module=XXPOCF.fmb Userid=apps/apps Module_Type=form Batch=no Compile_All=special Output_File=$CUST_TOP/forms/US/XXPOCF.fmx

PO Change API

This API is used to make any changes in the existing purchase order details

declare
result number := null;
begin
-- Do not forget to commit after the result returns 1
-- and rollback if result returns 0.
-- This needs to change as per your application.

fnd_global.apps_initialize(1318, 50578, 201);
result := PO_CHANGE_API1_S.update_po(
1263, -- po num
1, -- release num
1,-- revision
1,-- line num
1, -- shipment num
5,-- qty
null, -- price
null, -- date
'Y', -- launch approvals
null, -- update source
'APITEST', -- interface type
null, -- txn id
'1.0'); -- version

dbms_output.put_line('result:'result);
EXCEPTION
WHEN OTHERS THEN
raise;
end;

Parameter Description
PO_NUM:
Purchase order number.
RELEASE_NUM:
Required if the purchase order is a release. The pass-in value must be a number.
REVISION_NUM:
Which revision of the purchase order/release is being acted upon.
LINE_NUM:
Purchase order line number to update.
SHIPMENT_NUM:
If provided, indicates the update occurs at shipment level, otherwise it's at line level
NEW_QUANTITY:
Indicates the new value of quantity ordered that the order should be updated to
NEW_PRICE:
Indicates the new value of unit price that the order should be updated to.
NEW_PROMISED_DATE:
Indicates the new value of promised date that the order should be updated to. Must be in the format of 'MM/DD/YY' or 'MM-DD-YY'
LAUNCH_APPROVALS_FLAG:
Indicates if you want to launch APPROVAL workflow after the update. Its value could be either 'Y' or 'N'. If not provided, the default value is 'N'.
SOURCE_OF_UPDATE:
Reserved for future use to record the source of the update. Purchase Order Change APIs
TRANSACTION_ID:
Used to fetch any error messages recorded in PO_INTERFACE_ERRORS table if the update process fails. If not provided, a default value will be used.
VERSION:
Version of the current API (currently 1.0)

PO Acceptance API

Below API insert the acceptance record into PO_ACCEPTANCES table, before that check box must be checked for acceptance before submitting the PO for approval.

Declare
result number := null;
begin
fnd_global.apps_initialize (user_id, Responsibility_id_id, Application_id);
result :=
PO_CHANGE_API1_S.record_acceptance
(9310000133, ---PO Num
null, ---Release Num
0, ---Revision Num
'NEW', ---Action
sysdate, ---Action Date
261, ---Buyer id
'Y', ---Acceptance flag ‘Y’ or ‘N’
'ABCD', ---Acceptance lookup code
'All valid', ---Note
'1.0'); ---Version Number
commit;
end;

Parameter Description
PO_NUM:
Purchase order number.
RELEASE_NUM:
Required if the purchase order is a release. The pass-in value must be a number.
REVISION_NUM:
Which revision of the purchase order/release is being acted upon.
ACTION:
Indicates the action to take. The value must be NEW.
ACTION_DATE:
Indicates the date of follow-up action. Provide a value in the format of 'MM/DD/YY' or 'MM-DD-YY', its default value is TRUNC (SYSDATE).
EMPLOYEE_ID:
The fnd_global.user_id of the buyer.
ACCEPTED_FLAG:
Indicate if purchase is accepted. Must be 'Y' or 'N'.
ACCEPTANCE_LOOKUP_CODE:
Type of acceptance, its value must be corresponding to the LOOKUP_CODE in PO_LOOKUP_CODES table with LOOKUP_TYPE of ACCEPTANCE TYPE. The possible values are- Accepted Terms, Accepted All Terms, On Schedule, UnacceptableChanges, and REJECTED
NOTE:
Any description you want to insert.
VERSION:
Version of the current API (currently 1.0)

Reports (Quick Interview Guide)

1) Oracle Report Developer uses many Environment Variables. These have Default Values & can be modified in our own environment for different applications.
Some of the variables are,
a) REPORTS60_PATH(Path that Report Builder searches for files at Runtime
b) UI_ICON (Path that all builder search for icon files at run time)
c) REPORTS60_TMP (Path that will be used to Create temporary files)
Note: Windows Registry is used to modify this paths,
Registry Path: HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/HOME1

2) If we have MAPI (Microsoft Messaging Application Program Interface) mail client, we can send Reports as an E-Mail Attachment.

3) Report Editor contains,
a) Data Model: Displays Structural Representation of Data in a Report
b) Layout Model: Displays the layout objects in a report & allows you to make many modifications to any layout object.
c) Live Previewer: Displays Report output & allows you to make commonly Required modifications to the layout.
d) Web Previewer: Preview your Report in your Web Browser in formats like PDF, HTML, XML.etc.
e) Parameter Form: Displays the layout of the parameter form that, at run Time, allows user input of parameter values

4) Data Model Objects: Query, Group, Columns(Formula, Summary & Placeholder), Data Link, Parameters (User & System Parameters)
Layout Model Objects: Repeating Frame, Main Frame, Field, Boilerplate Text
Parameter Form Objects: Field, Boilerplate Text

5) Three Significant Features in Form-Like Style that differ from Tabular Style are,
a) Labels appear to the left of each Field
b) Each Field Appears to the left of Previous Field, across the page
c) Each Record Appears on a new page

6) Difference Between Mailing Label & Form Letter
a) Mailing Label: Multiple Records on one page
b) Form Letter: One Record on each page

7) Live Previewer Window Consists of,
a) Toolbar
b) Tool palette
c) Stylebar(Currency Symbol, Add & Remove Decimal Places,% Symbol,1000 Seperator)
d) Statusbar
Note: In Live Previewer we can Format the Objects Conditionally by option Format-Conditional Formatting

8) .rdf : Full Report Definition, Not Modifiable Binary, Executable, Portable if Transferred as binary
.rep : No Source Code or Comments, Not Modifiable Binary, Executable, Portable as binary if no PL/SQL is present
.rex : Full Report Definition, Not Modifiable ASCII text, not Executable, 100% Portable

9) Maximum Rows to Fetch Restrict the Rows Returned from a Query. It is the Property of Query. Whereas Group Filter Property is of Group where we can filter Records using First, Last Option or PL/SQL Code.

10) There are 4 types of columns in Report:
a) Database columns
b) Formula columns: This are used to perform Computations of another columns data including Placeholder column. It returns one value which is Computed using PL/SQL
c) Summary columns: This columns Performs Computation on another columns data
d) Placeholder columns: This are the columns for which Datatype & Values are set in PL/SQL Code. This code is written at Formula level trigger or Report level trigger

Note: We can Reset Summary Column at Report, Group or Page Level.

11) There are 2 types of User Parameters in Report:
a) Lexical Parameters:
· Lexical Parameters are Placeholders for Text
· We can use Lexical References to Replace clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, START WITH.
· We cannot make Lexical References in PL/SQL Statements
· Lexical References are also used for Multiple Clauses
· Lexical References are created by entering ‘&’ followed by Parameter name

b) Bind Parameters:
· Bind Parameters are used to Replace a Single Value in SQL or PL/SQL, such as character string, number or date
· Bind References are also used to replace Expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, & START WITH.
· We cannot make Bind References in From Clause or in place of Reserved words
· Bind References are created by entering colon (:) followed by Parameter name

12) There are 2 types of Modes in Report:
Confine Mode
ON- We cannot move object outside its correct Enclosing object
OFF- We can move the object outside its Enclosing object & Resize it
Flex ModeON- When we move or Resize one object all affected objects are also Adjusted
OFF- We can move object Individually without affecting other objects

13) Additional Layout Option: It is used to create more than one Layout for the Same Report

14) Buttons enables users to interact with a Report in the Previewer at Runtime. Buttons have no meaning in a printed report. They are created in Report area to,
a) Access a URL
b) Drill down to a separate detail report
c) Display Multimedia information related to Report items

15) Anchors: This are used to Determine the Vertical & Horizontal Positioning of a child object relative to its Parent. Some of the Important Rules are;
a) An object may be Anchored to only one other object
b) Matrix objects like Repeating Frame, Anchors & Margin cannot be anchored to anything.
c) Objects cannot be anchored in such a way that they have a Circular dependency
d) Nothing can be anchored to a hidden object
e) An anchor cannot be resized

16) There are 4 types of Object Elasticity:

a) Fixed: Identical to Layout object size
b) Expand: Can be larger than Layout, but not smaller
c) Contract: Can be smaller than layout, but not larger
d) Variable: Can be larger or smaller than layout

17) Print Direction: It is the Property of Repeating Frame. They are of 4 types:
a) Down: Prints sequential records down the page. At the end of this page, records overflow onto the next page
b) Across: Prints sequential records across the page. When records reach the right edge of the page, they overflow onto a new page.
c) Down/Across: Prints sequential records down the page. At the end of the page, records move across to form a new column if there is a room, otherwise they overflow onto a new page. It is used for Panel/Print Order Property.
d) Across/Down: Prints sequential records across the page. When records reach the right edge of the page, they move down below the previous set of records if there is room, otherwise they overflow onto a new page. Used for Panel/Print Order property.

18) System Variables & System Parameters are,
System Variablesa) Current Date
b) Page Number
c) Panel Number
d) Physical Page Number
e) Total Pages
f) Total Panels
g) Total Physical Pages

System Parameters
a) Background
b) Copies
c) Currencies
d) Decimal
e) Desformat
f) Desname
g) Destype
h) Mode
i) Orientation
j) Print Job
k) Thousands

19) Types of Triggers:
a) Report Trigger: Order of Execution of Report Trigger is,
· Before Parameter form trigger is fired
· Runtime Parameter form appears
· After Parameter form trigger is fired
· Report is Compiled & Queries are Parsed
· Before Report Trigger is fired
· Report is Executed & Data is fetched from database
· Between pages fires for each page except last one
· After Report trigger is fired

b) Group Filter Trigger: It is a PL/SQL Function that determines which record to include in a group. The function must return Boolean value. If it returns True then record is included in group & if False then Excluded.

c) Formula Column Trigger: This are PL/SQL Functions that populate formula or placeholder columns. A column of Datatype Number can have a formula that returns a value of datatype Number.

d) Validation Trigger: This are PL/SQL Functions that are Executed when Parameter values are Specified on the Command line, when we accept Runtime Parameter form. It returns only a Boolean Value.

e) Format Trigger: This are PL/SQL Functions that are Executed before the object is formatted. It is used to dynamically change the formatting attributes of the object. It returns Boolean Value i.e. True or False to include or Exclude Current instance of object from Report output.

f) Action Trigger: This are PL/SQL Procedures Executed when a button is Selected in the Runtime Previewer. The Trigger is used to dynamically call another report or execute any other PL/SQL code.

20) Cursor Variables: This are the Variables to which Datatype Refcursor is Assigned. This are like C or Pascal pointers, which hold the memory location of some item instead of item itself. Cursor variables are mainly used to pass Query Result sets between PL/SQL stored subprograms.
Ref Cursor Query uses PL/SQL to fetch the data for the report. In Refcursor query, you specify a PL/SQL function that returns a cursor value from a Cursor Variable.

Eg: Package cv is
Type comp_rec is RECORD
(deptno number, ename varchar2(10), compensation number);
Type comp_cv is REFCURSOR return comp_rec;
Function emprefc(deptno1 number) return comp_cv
End;

PACKAGE BODY cv Is
Function emprefc(deptno1 number) return comp_cv is
Temp_cv cv.comp_cv
Begin
If deptno1>20 then
Open temp_cv for select deptno, ename, 1.25*(sal+nvl(comm,0)) Compensation
From emp where deptno = deptno1;
Else
Open temp_cv for select deptno, ename, 1.15*(sal+nvl(comm,0)) Compensation
From emp where deptno = deptno1;
End if;
Return temp_cv;
End;

21) User Exit: It is used when we want to pass control from Report Builder to a program which performs some function, and then returns control to Report Builder. They are of 3 types:
a) ORACLE Precompiler user Exits
b) OCI (Oracle Call Interface) User Exits
c) Non-Oracle User Exits

Some of the Important User Exits used if one is using Oracle Application Object Library Features in his Report are,
a) FND SRWINIT: It Fetches Concurrent Request Information & sets up the Profile Options. It is called always from Before Report Trigger

b) FND SRWEXIT: It frees all the Memory Allocations done in other Oracle Applications user exits. It is called always from After Report Trigger

c) FND FLEXSQL: It is also called from Before Report Trigger. It is used to Pass the concatenated segment values from the underlying Code Combination Tables to the user exit so that it can display appropriate data and derive the description & values from switched value set as needed. Its Main Parameters are,
1) CODE
2) APPL_SHORT_NAME
3) OUTPUT (Name of the Lexical Parameter to store the SQL Fragment)
4) MODE (Select, Where, Having, Order By, Group By)
5) DISPLAY (Specify Segment Number, Flexfield Qualifier or ALL for all segments)
6) NUM OR MULTINUM (Chart of Account Id)
7) OPERATOR (Between And, etc.)
8) OPERAND1
9) OPERAND2

d) FND FLEXIDVAL: It is Called from Formula Column. It automatically fetches more Complicated Information such as Descriptions & Prompts so that one does not have to use Complicated Table joins to the flex field tables. Its Main Parameters are,
1) CODE
2) APPL_SHORT_NAME
3) DATA (Source Column or Parameter indicated by Colon)
4) NUM (Chart of Account Id)
5) SHOWDEPSEG
6) DISPLAY (Column where the Output is Displayed)
7) IDISPLAY
8) APROMPT

e) FND FORMAT_CURRENCY: It is used to format the Currency Amount dynamically depending upon the Location (Country) of site.
1) CODE (Column Which Contains Currency code for the Amount)
2) DISPLAY_WIDTH (Width of the field in which formatted Amount is displayed)
3) AMOUNT (Name of Column which Contains Amount Retrieved from Database)
4) DISPLAY (Name of Column into which we want to display formatted values)
5) MINIMUM_PRECISION (Bind Parameter)
6) PRECISION (Standard or Extended)
7) DISPLAY_SCALING_FACTOR (Bind Parameter)

22) Matrix Contains at least 4 Groups:
a) Column Group b) Row Group
c) Cross Product Group d) Cell Group

23) OCI: It is Oracle Call Interface. When applications developers demand the most powerful
interface to the Oracle Database Server, they call upon the Oracle Call Interface (OCI). OCI provides the most comprehensive access to all of the Oracle Database functionality. The newest performance, scalability, and security features appear first in the OCI API. If you write applications for the Oracle Database, you likely already depend on OCI. Some types of applications that depend upon OCI are:
· PL/SQL applications executing SQL
· C++ applications using OCCI
· Java applications using the OCI-based JDBC driver
· C applications using the ODBC driver
· VB applications using the OLEDB driver
· Pro*C applications
· Distributed SQL

24) Precompiler: A precompiler is a tool that allows programmers to embed SQL statements in high-level source programs like C, C++, COBOL, etc. The precompiler accepts the source program as input, translates the embedded SQL statements into standard Oracle runtime library calls, and generates a modified source program that one can compile, link, and execute in the usual way. Examples are the Pro*C Precompiler for C, Pro*Cobol for Cobol, SQLJ for Java etc.

25) Term is a Terminal Definition file that describes the Terminal from which you are using R20DES (Report Designer)

Forms (Quick Interview Guide)

1) Oracle Forms Developer, a component of the Oracle iDS i.e. internet Developer Suite. Oracle Forms Developer applications can be automatically generated from business requirements designed in Oracle Designer modeling environment. This models are stored in Oracle Repository. Code level changes made within the Oracle Forms Developer.

2) Oracle Forms Services is a component of Oracle9i Application Server for Delivering Oracle Forms Developer applications to the Internet. Oracle Forms Services used 3 Tier Architecture to deploy database applications.
a) Client Tier contains the Web Browser
b) Middle Tier is the application Server
c) Database Tier is the Database Server.

3) 9iAS consists of set of services & utilities that can be used to implement applications in a distributed environment
a) Communication Services (Handle Incoming Request received by 9iAS)
b) Presentation Services (Outputs some kind of graphical presentation)
c) Business Logic Services (Develop Business logic using Java, EJB.etc)
d) Data Management Services(To reduce load on back end database instance)
e) System Services (Oracle Enterprise Manager & Oracle Advance security)

4) Forms services consists of 4 Major Components:
a) Java Client: It is an Applet that is downloaded at Runtime from an Application server to an end users Web Browser.
b) Forms Listener: Establishes a connection between the Forms Client & Forms Services Runtime Engine.
c) Forms CGI or Servlet: Dynamically creates an HTML page to be sent back to the user’s Browser.
d) Forms Runtime Engine: It is the process that maintains a connection to the database on behalf of Java Client.
When a user runs a forms session over the Web, a thin, Java-based Forms Applet is dynamically downloaded from the application server & automatically cached on the Java client Machine. The same Java Applet code can be used for any form, regardless of size & complexity.

5) Form Builder is the main application building component of Oracle Forms Developer.

6) Object Navigator: It is a hierarchical browsing & editing interface that enable you to locate & manipulate applications objects quickly & easily.
Property Palette: All objects in a module, including the module itself, have properties that you can see & modify in the Property Palette
Layout Editor: It is a graphical design facility for creating & arranging interface items and graphical objects in your applications.
PL/SQL Editor: It is the integrated functionality of Oracle Procedure Builder that exists within the Form Builder.

7) Preference File: Oracle Forms Developer & Oracle Reports Developer share the same preference file named ‘D:\D2K\cauprefs.ora’. This file is an editable text file. Oracle Forms & Reports Developer can read this file whenever Oracle Form or Report Builder is invoked.

8) Oracle Forms Developer uses many Environment Variables. These have Default values & can be modified in our own environment for different applications.
Some of the variables are,
a) FORMS60_PATH (Path that Form Builder searches for files at run time)
b) UI_ICON (Path that all builder search for icon files at run time)
c) FORMS60_USER_DATE_FORMAT (FXFM DD-MM-RRRR)

9) Menu Toolbar: It contains buttons corresponding to menu items.

10) Console: It is the generic name for the standard features that provide information at runtime. The console is displayed at the bottom of Runtime Window & Consists of Message line that displays both Form builder & Application specific messages & Status line that displays a variety of indicators like Record status.

11) There are 2 modes of operation in Form Builder:
a) Enter Query Mode: It is used to retrieve selected Records using Query/Where Dialog box and cannot be used to perform DML Operations. We can Retrieve Records before fetching them from Database by using Queryà Count Hits
b) Normal Mode: It is used to Retrieve all Records, performs DML operations, Commit & Rollback Records. But it is not used to retrieve Restricted number of Records.

12) Four Major objects in a form are,
a) Blocks: Block is the intermediate building unit of form which is the logical owner of items . Each form consists of more than one block.
b) Items: These are interface objects that present data values to the user or enable the user to interact with form, depending upon the item type. Items are logically grouped into blocks & visibly arranged on Canvases
c) Canvases: A Canvas is a Surface where visual objects, such as graphics & items are arranged. To see a Canvas & its items, you must display the canvas in a Window. By default, all Canvases in a form appears in the same Window but we can assign several Canvases to different Windows.
d) Window: It is a Container for all visual objects that make up a Form Builder Application. It is similar to an Empty Picture Frame.

13) A Form Builder Module (Files) can be of following types:
a) Form: As the main component of an Application, the form module presents the objects & data that users can see or interact with. Data items in a form are arranged into records.
b) Menu: A Menu Module can consist of a hierarchy of menus, each with selectable items
c) PL/SQL Library: It is a collection of PL/SQL Program units whose code can be referenced & called from other modules.
d) Object Library: It is a collection of Form objects that you can use in other modules. You can create it to store, maintain & distribute standard objects that can be reused across the entire development organization.

14) There are 2 types of Blocks:
a) Data Block: It is associated with a specific database table(or view), a stored procedure, a From Clause query, or transactional Triggers.
b) Control Block: It is not associated with a database, and its items do not relate to any columns within any database table.

15) While creating Data block through Wizard, Select Enforce Data Integrity check box if you want the wizard to enforce the database integrity constraints.

16) When we save the form module .fmb file is created but after compilation .fmx file i.e. executable file is created.

17) There are 3 types of Master-Detail Relationship:
a) Non-isolated (We cannot delete master record if detail record exists)
b) Isolated (We can delete master & detail records independent of each other)
c) Cascading (If we delete master record, detail record also gets deleted)
We can create more that 1 Detail Block for Single Master Block.

18) Master-Detail Relationship Triggers are:
a) On Clear Details(Form Level Trigger)
b) On Populate Details(Detail Block Level)
c) On Check Delete Master(Detail Block Level)
d) Pre Delete(Detail Block Level)

19) There are 3 types of Co-ordination Property:
a) Default (No Deferred & No Auto Query)
b) Deferred with Auto Query
c) Deferred without Auto Query
d) Prevent Masterless Operations

20) Visual Attributes: It is font, color & Pattern Properties that you set for form & Menu objects. It has 2 main properties:
a) Visual Attribute Group or Current Record Visual Attribute Group
b) Prompt Visual Attribute Group

21) Each property in a property palette has an icon to its left. Summary of all icons,
a) Circle (Property value is the default value)
b) Square (Property value has been changed from default value)
c) Arrow (Property value is Inherited)
d) Arrow with Cross (Property value was inherited but has been overridden)

22) Property Classes: When you display a list of properties in the property palette, the list of property names & associated values can be saved for future application to other objects. This is known as property class, which is a Form Builder object

23) When Datablock is Deleted its subordinate objects, Relationship gets deleted. However the frame border & its title will remain. Delete the frame manually in the Layout Editor.

24) Text Item: It usually corresponds to a column in the database table. When an item is first created, its default type is text. Prompts specify the text label that is associated with an item. Text item has 2 main properties:
a) Distance between Records
b) Number of Items Displayed
c) Keyboard Navigable
(Yes—You can Navigate to an item with keyboard & Mouse
No—Form Builder skips over the item & enters next navigable item in navigation sequence)
d) Enabled (Yes—You can Navigate to an item & Manipulate it with the mouse
No—The item is disabled & appears grayed out)
e) Multiline (Multiple Line Text item)
f) Hint (Write Help Text for Item which is displayed on message line at run time)
g) Display Hint Automatically(Set it to Yes for displaying Hint when focus is on item)
h) Tooltip (Displays the text when Mouse Pointer is on Item)

25) Format Mask Property specifies the format in which the user sees the item value.
There are 2 SQL Formats:
a) FXDD-MON-YY
b) L099G99D99

26) Use any one of the following to issue an initial item value whenever a new record is created:
a) Raw Value (Eg:340, Richmond)
b) System Variable:
($$DATE$$, $$DATETIME$$, $$TIME$$)—For Operating System
($$DBDATE$$, $$DBDATETIME$$, $$DBTIME$$)—For Database
c) Global Variable (:Global.Customer_Id)
d) Form parameter (:Parameter.Sales_Id)
e) Form item (:Order.Id)
f) Sequence (:Seq.S-Ord.Nextval)
Note: We can use Erase Built in to remove global variable

27) LOV & Editors: This are objects in the form Module that open their own window when activated at Run time. For Activation of LOV Press F9 & For Editor Ctrl+E· Editors are generally used for Multiline Text item. They are of 3 Types:
a) Form Builder Editor (By Default)
b) Customized Editor (User Defined Editor)
c) System Editor
· LOVs are used to display List of values. LOV are of 2 Types:
a) Static LOV ( Contains Predetermined Values)
b) Dynamic LOV (Contains Values that Come at Runtime)

28) Record Group: It is a Column & Row structure stored within Forms Runtime Memory and is similar to the structure of a database table. They are of 3 Types:
a) Static : A Record Group without an Associated Query. The Columns & Rows of Record Group are Designed at Design Time & Cannot be Modified Programmatically at Runtime.

b) Query: A Record Group with an Associated Select Statement. The Columns in the Record Group Derive their Properties from the column in the Select Statement. The Rows in the Record Group are Rows Retrieved in Select Statement. You can Create this type of Record Group at the Design Time & Runtime using Built ins like Create_Group_From_Query, Populate_Group.etc.

c) Non-Query: A Record Group Without an Associated Query. The Columns & Rows of Record Group are defined Programmatically at Runtime & also Modified at Runtime. Built ins used are ADD_GROUP_ROW, etc.

Because LOVs & Record Groups are separate objects, you can create multiple LOVs based on the same Record Group.

29) Some of the Important Properties of LOV are,
a) Filter before Display (User should be prompted with a dialog box that enables them to enter search value before LOV is Invoked)
b) Automatic Display (LOV should be invoked automatically when cursor enters an item)
c) Automatic Refresh ( No: Record group query fires only 1st time Lov is invoked
Yes: Record group reexecutes its query every time Lov is invoked)
d) Return Item (Specifies the name of the item or variable to which Form Builder should assign
the column value)

30) Input Items: This are the items which accepts User Input. Through this items We can Insert, Update, Delete & Query Existing Values. They are of 3 Types:

a) Check Box: A check box is a 2 state interface object that indicates whether a certain value is ON or OFF. The display state of check box is always either checked or unchecked. Some of the main Properties are,
1) Value when Checked
2) Value when Unchecked
3) Check Box Mapping of Other Values (Checked or Unchecked)

b) Radio Group: It is a set of Radio Buttons. Each Radio Button represents a different value. Consider List items instead of radio groups if there are more than 4 or 5 choices. Some of the main Properties are,
1) Radio Button Value
2) Mapping of Other Values(Name of the Radio Button, Property of Radio Group)

c) List Item: It is an Interface Object that displays predefined set of choices, each corresponding to a specific data value. They are of 3 Types:
1) Poplist ( Do not Accept User Input)
2) Tlist
3) Combo Box (Accepts User Input)
Some of the Main Properties are,
1) Elements In List (List Elements & List Item Value)
2) Mapping of Other Values (Determines how other values are processed)

31) Non Input Items: Items that do not accept user input. They are divided into 2 main groups:
a) Non Input Items that can Display Data:
· Display Items
· Image Items
· Calculated Items
b) Non Input Items that Cannot Display Data:
· Sound Items
· Push Buttons

32) Display Item: It is similar to Text item, except that it cannot be edited or navigated to at Runtime. It required less memory than text items. It is non-Database Item.

Image Items: It displays Automatically & Cannot be Manipulated at Runtime. It can be Imported from the database or the file system. When we insert images into the database by means of a Form Builder they are automatically compressed using Oracle Image Compression. Some of the Image Formats are BMP, GIF, JPEG, TIFF, CALS. It can be Database or Control Item.

Sound Items: It is a special interface control that can play & record sound data. Some of the types are AU, AIFF, WAV, AIFF-C. Some of the properties are Play, Record, Rewind, Fast Forward, Volume, Time Indicator, Slider. It can be Database or Control Item.

Push Button: It is an interface object that is clicked to Initiate an Action. They are of 2 Types: Text Button & Iconic Button. Iconic Button do not have Labels

Calculated Items: This are read only items like display items. Any item that can store a value can be used as a calculated item. It supports 2 Calculation Modes:
a) Formula (Result of a horizontal calculation)
b) Summary (Vertical Calculation that uses Group Functions)

Hierarchical Tree Item: It is an item that displays data in the form of standard Navigator.

33) Viewport: It is an attribute of a Canvas. It is effectively the visible portion of view onto the Canvas. There are Various Types of Canvases:
a) Content Canvas: It is the Default Base Canvas that occupies the entire content pane of the window in which it displays.
b) Stacked Canvas: It is displayed on top or stacked on the Content Canvas assigned to window. This are usually smaller than the window in which they display. By placing important items on Content Canvas & placing the items that can be scrolled out of sight on a Stacked Canvas, the Stacked Canvas becomes the scrolling region, rather than window itself.
c) Toolbar Canvas: It is created to hold buttons & other frequently used GUI elements. They are of 2 types: Horizontal & Vertical Toolbar Canvas
d) Tab Canvas: It is used to Organize & Display Related Information on Separate Tabs.

34) Trigger: Trigger is a Program unit that is executed or fired whenever an event is generated. Triggers are used to add or modify form functionality in a procedural way. There are 3 main components of Triggers:
a) Trigger Type (Defines specific event that cause the trigger to fire)
b) Trigger Code (Body of PL/SQL that defines action of the trigger)
c) Trigger Scope (Level in the form module i.e. Form/Block/Item level trigger)

35) When there is more than one trigger of the same type Form Builder normally fires the trigger most specific to the cursor location. ‘Smart Triggers’ are common triggers that are appropriate to the selected object.

36) Multiple Split Views: We can create up to 4 separate views of the current program unit in the PL/SQL editor by using split bars.

Syntax Palette: It enables you to display & copy the constructs of PL/SQL language elements & build packages into an editor. To invoke select Programà Syntax Palette from menu system.

37) Form Builder accepts 2 types of variables:
a) PL/SQL Variables: These must be declared in a Declare Section & remain available until the end of declaring block. They are not prefixed by colon.
b) Form Builder Variables: These are seen by PL/SQL as external variables, & require a colon (:) prefix to distinguish them from PL/SQL objects. They are not formally declared in Declare section & can exist outside scope of PL/SQL block.

38) Form Builder Built in subprogram belongs to one of the following:
a) Standard Extension Packages: You can call them directly, without any package prefix. You can use more than 100 standard Built ins.
b) Other Form Builder Packages: This require package name as prefix when called.

Note: All Uncompiled triggers are compiled when form module is compiled.

39) Built ins are divided into 2 groups:
a) Unrestricted Built ins: It do not affect logical or physical Navigation & can be called from any trigger, or from any subprogram.
b) Restricted Built ins: It affects Navigation in your form either External or Internal Screen Navigation. We can call these built ins from Triggers while no Internal navigation is occurring. We can Call Restricted Built ins from When Button Pressed, When Checkbox Changed, When New ‘object’ Instance Triggers & Key Triggers but not from Pre & Post Triggers

40) Expand Built in Packages node in Object Navigator & select Built in we need to use.
Select Navigator - Paste Name (To include only Built in name) from Menu
Select Navigator - Paste Argument (To include Built in alongwith Arguments)

41) Form Builder displays Messages at Runtime to inform the Operator of events that occur in the session:
a) Informative Message: We can suppress its appearance with On-Message Trigger.
b) Error Message: We can suppress Message Line Errors with On-Error Trigger
c) Working Message: This type of message can be suppressed by setting system variable Supress_Working to True.
d) Application Message: This are messages that we build into our application using Message Built-in
e) Application Alert: This are designed a part of application, and issue to the operator for a response by using Show_Alert Built-ins.

42) Query Array Processing: Form Builder Supports both Array Fetch Processing & Array DML Processing. For both Querying & DML Operations, you can determine the array size to optimize performance for your needs.
Some of the Properties are,
a) Query Array Size Property: It specifies maximum number of records that Form Builder should fetch from the database at one time. A size of 1 provides fastest perceived response time.
b) Query All Records Property:
Yes: Fetches All Records from query
No: Fetches No. of Records specified by Query Array Size Block Property.

43) Sequence of firing Triggers are,
a) Pre Form e) When New Record Instance
b) Pre Block f) When Validate Record
c) When New Form Instance g) Post Block
d) When New Block Instance h) Post Form

44) System Variables: It is an Form Builder Variable that keeps track of internal Form Builder state. We can reference the value of System Variable to control the way an application behaves. Some of the available System Variables are,

a) SYSTEM.BLOCK_STATUS
b) SYSTEM.CURRENT_BLOCK
c) SYSTEM.CURRENT_DATETIME
d) SYSTEM.CURRENT_FORM
e) SYSTEM.CURRENT_ITEM
f) SYSTEM.CURRENT_VALUE
g) SYSTEM.CURSOR_BLOCK
h) SYSTEM.CURSOR_ITEM
i) SYSTEM.CURSOR_RECORD
j) SYSTEM.CURSOR_VALUE
k) SYSTEM.DATE_THRESHOLD*
l) SYSTEM.EFFECTIVE_DATE*
m) SYSTEM.FORM_STATUS
n) SYSTEM.LAST_QUERY
o) SYSTEM.LAST_RECORD
p) SYSTEM.MASTER_BLOCK
q) SYSTEM.MESSAGE_LEVEL*
r) SYSTEM.MODE
s) SYSTEM.MOUSE_BUTTON_PRESSED
t) SYSTEM.MOUSE_ITEM
u) SYSTEM.RECORD_STATUS
v) SYSTEM.SUPPRESS_WORKING*

Note: All System Variables are Read only Variables except 4 ending with * to which You can assign values explicitly.

45) Object Group: An Object Group is a Container for a group of objects. Object Group is defined when we want to package related objects so that we can copy or reference them in another module. Following points are important:
a) We can create object groups in form & menu modules. Once we create object group we can add remove objects to it as desired
b) Program units cannot be placed in an object group
c) Object group cannot be placed in an object group
d) Object owned by blocks i.e. Items, Item level Triggers, Block level triggers and Relations cannot be placed in an object group. To include these objects we have to include whole block in object group.
e) The objects in a group must all be defined in the same module, we cannot place objects from 2 different forms in the same object group
f) Deleting an object group from a module does not delete the objects it contains from the module, whereas deleting the object from module also delete the object from object group. Because object group do not store copies of objects but rather pointer to the objects.

46) Alerts: An ‘Alert’ is a Modal Window that displays a Message Notifying operator of some Application Condition.

47) Parameters: Parameters provide a simple mechanism for defining and setting the values of Inputs that are required by a form at startup. Form parameters are variables of Type char, number, date that you define at design time.

You can pass parameter values to a form when an application executes the Call_form, New_form, Open_form or Run_product. The maximum no of chars the parameter can store is only valid for char parameters, which can be upto 64K. Number parameters default to 23 Bytes and Date parameter default to 7 Bytes.
There are 2 types of Parameters:
1) Text Parameters
2) Data Parameters
Data Parameters (Pointers to Record Groups) can only be passed to Report Builder & Graphics Builder in Synchronous Mode through Run_Product Built in.
Note: We can create Table, Indexes, Sequences in Forms using Forms_DDL Built in

PA Budget API

pa_budget_pub.create_draft_finplan
(p_api_version_number => 1.0,
p_commit => 'F',
p_init_msg_list => 'T',
p_pm_product_code => 'LEGACY',
p_pa_project_id => l_project_id,
p_fin_plan_type_name => 'Approved Cost',
p_version_type => 'COST',
p_time_phased_code => '',
p_resource_list_name => 'RESOURCE LIST BY EXPENDITURE CATEGORY', p_fin_plan_level_code => 'P',
p_budget_version_name => '1.0',
p_description => 'CONVERSION',
p_raw_cost_flag => 'Y',
p_burdened_cost_flag => 'Y',
p_create_new_curr_working_flag => 'N',
p_replace_current_working_flag => 'Y',
p_using_resource_lists_flag => 'Y',
p_finplan_trans_tab => t_finplan_tbl,
x_finplan_version_id => x_finplan_version_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);

Where t_finplan_tbl is table type variable. Assign record type variable to it as below
t_finplan_tbl (i) := t_finplan_rec;
t_finplan_rec.pm_product_code := 'LEGACY';
t_finplan_rec.task_id := l_task_id;
t_finplan_rec.pm_task_reference := '1';
t_finplan_rec.currency_code := 'EUR';
t_finplan_rec.resource_alias := '1';
t_finplan_rec.resource_list_member_id := '1023';
t_finplan_rec.raw_cost := rec_tasks.raw_cost;
t_finplan_rec.burdened_cost := rec_tasks.burdened_cost;

pa_budget_pub.baseline_budget
(p_api_version_number => 1.0,
p_commit => 'F',
p_init_msg_list => 'T',
p_msg_count => x_msg_count2,
p_msg_data => x_msg_data2,
p_return_status => x_return_status2,
p_workflow_started => x_workflow_started,
p_pm_product_code => 'LEGACY',
p_pa_project_id => l_project_id,
p_pm_project_reference => rec_budget.project_number, p_budget_type_code => '',
p_mark_as_original => '',
p_fin_plan_type_id => 10020,
p_fin_plan_type_name => 'Approved Cost',
p_version_type => 'COST'
);

SQL Loader Overview

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. It supports various load formats, selective loading, and multi-table loads.

Below are the examples of loading data into Tables using different methods

Load Fixed length Data Records
LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( data1,
data2
)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"

Load Positional Data Records
LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB

Skip First 2 Header Records while Loading
LOAD DATA
INFILE *
INTO TABLE load_positional_data
SKIP 2
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
NNNNNNNNNNNNNNN
UUUUUUUUUUUUUUU
11111AAAAAAAAAA
22222BBBBBBBBBB

Loading Data into Multiple Tables
LOAD DATA
INFILE *
REPLACE
INTO TABLE emp
WHEN empno != ' '
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
INTO TABLE proj
WHEN projno != ' '
( projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL
)

Skip Certain Columns while Loading DataLOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)

Erroneous data after loading with SQL Loader

Our Database is created with below settingsCHARACTER SET "UTF8"
NATIONAL CHARACTER SET "WE8ISO8859P1"
NLS_LANGUAGE = AMERICAN
NLS_TERRITORY = AMERICA

Include below code in control file to avoid junk data load using SQL loaderLOAD DATA
CHARACTERSET WE8ISO8859P1
APPEND INTO TABLE XXAP_GENERIC_INVOICES
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(operating_unit
,vendor_number
)

Using SQL Functions for Loader Columns
LOAD DATA
APPEND INTO TABLE
XXAR_INV_AT
FIELDS TERMINATED BY ""
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
DESCRIPTION "NVL(:DESCRIPTION,'Migrated from Legacy')",
TRX_DATE "TO_CHAR(TO_DATE(:TRX_DATE,'YYYYMMDD'),'DD-MON-YYYY')" ,
GL_DATE "TO_CHAR(TO_DATE(:GL_DATE,'YYYYMMDD'),'DD-MON-YYYY')" ,
TAX_RATE "REPLACE(:TAX_RATE,'%','')",
STAGING_TABLE_ID "XXAR_INV_AT_S1.NEXTVAL",
CREATION_DATE SYSDATE,
LAST_UPDATE_DATE SYSDATE
SOURCE "TRIM(REPLACE(:SOURCE,(SUBSTR(:SOURCE,LENGTH(:SOURCE),1)),''))"
)

Table Registration in APPS

We can register custom application tables using a PL/SQL routine in the AD_DD package. Below are the Procedures in the AD_DD Package

Procedure register_table
(p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_tab_type in varchar2,
p_next_extent in number default 512,
p_pct_free in number default 10,
p_pct_used in number default 70);

Procedure register_column
(p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2,
p_col_seq in number,
p_col_type in varchar2,
p_col_width in number,
p_nullable in varchar2,
p_translate in varchar2,
p_precision in number default null,
p_scale in number default null);

Procedure delete_table
(p_appl_short_name in varchar2,
p_tab_name in varchar2);

Procedure delete_column
(p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2);

Parameter Description
p_appl_short_name:
The application short name of the application that owns the table (usually your custom application).
p_tab_name:
The name of the table (in uppercase letters).
p_tab_type:
Use 'T' if it is a transaction table (almost all application tables), or 'S' for a "seed data" table (used only by Oracle Applications products).
p_pct_free:
The percentage of space in each of the table's blocks reserved for future updates to the table (1-99). The sum of p_pct_free and p_pct_used must be less than 100.
p_pct_used:
Minimum percentage of used space in each data block of the table (1-99). The sum of p_pct_free and p_pct_used must be less than 100.
p_col_name:
The name of the column (in uppercase letters).
p_col_seq:
The sequence number of the column in the table (the order in which the column appears in the table definition).
p_col_type:
The column type ('NUMBER', 'VARCHAR2', 'DATE', etc.).
p_col_width:
The column size (a number). Use 9 for DATE columns, 38 for NUMBER columns (unless it has a specific width).
p_nullable:
Use 'N' if the column is mandatory or 'Y' if the column allows null values.
p_translate:
Use 'Y' if the column values will be translated for an Oracle Applications product release (used only by Oracle Applications products) or 'N' if the values are not translated (most application columns).
p_next_extent:
The next extent size, in kilobytes. Do not include the 'K'.
p_precision:
The total number of digits in a number.
p_scale:
The number of digits to the right of the decimal point in a number

SQL (Quick Interview Guide)

1) There are two products:
a) Oracle 9i Database—Stores all your data
b) Oracle 9i Application Server—Runs all our applications like
1) Portals or Websites
2) Java Transactional Applications
3) Business Intelligence Applications

2) Oracle 9i is Object Relational Database Management system(ORDBMS). It supports 10,000 of concurrent users and 512 petabytes of data. It can handle any type of data including text, spatial, image, sound, video.

3) Database is developed using System Development life cycle:
a) Strategy & Analysis—Study & Analyze the business requirements and build a model.
b) Design—Design a database based on model
c) Build & Document—Create tables and supporting objects & develop user documentation, help & Operations manual.
d) Transition—Test the database and make any modifications if required.
e) Production—Rollout the system to users & Monitor its performance.

4) Pieces of Information is called data.

5) Database is an organized collection of information. To manage database we need DBMS. There are 4 main types of databases: Hierarchical, networking, relational, object relational.

6) Oracle 7 is RDBMS and Oracle 8,8i,9i are ORDBMS. Dr. E.F.Codd proposed the relational model for database systems in 1970.

7)
a) Entity: A thing of Significance about which information need to be known. Eg: Table.
b) Attribute: Something that describes or qualifies an entity. Eg: Column
c) Relationship between entities. Eg: Employee & Department.
d) UID: Unique Identifier is any combination of attributes or relationships or both that serves to distinguish occurrences of an entity.

8) With an RDBMS you can relate data in one table with data in another table by using primary & foreign key relationships. Foreign key is a column or set of columns that refer to a primary key in the same table or another table.

9) SQL is a structured query language or ANSI standard language. SQL statements are used to create, access and modify the database.

10) Various SQL Statements are:
a) Data Retrieval: Select
b) DML: Insert, Update, Delete
c) DDL: Create, Alter, Drop, Rename, Truncate
d) Transaction Control: Commit, Rollback, Savepoint
e) DCL: Grant, Revoke
f) Session Control: Alter Session, Set Role
g) System Control: Alter System
h) Embedded SQL Statements: Open, Close, Fetch & Execute.

11) Select statement can do the following:
a) Projection: Choose the columns in a table.
b) Selection: Choose the rows in a table.
c) Joining: Brings data together that is stored in different tables by creating link between them

12) Arithmetic Operators +, -, *, / are used in any clause of SQL Statement except in from clause. Oracle 9i ignores blank spaces before & after Arithmetic operators.

13) iSQL*Plus is a web enabled version of SQL*Plus. It uses 3 Tier architectural model comprising Client Tier, Middle Tier and Database Tier

14) A Null is a value that is unavailable, unassigned, unknown, or inapplicable. Any Arithmetic operation performed on null results in null.

15) Column Aliases that contains spaces or special characters (# or $) must enclose in double quotation marks. Aliases always appear in uppercase unless enclosed within double quotation marks.

16) We can link columns, arithmetic expressions or constant values using the concatenation operator( )

17) A literal value is a character, number, or a date that is included in Select Statement and is not a column name or a column alias. Date and Character literals must be enclosed in ‘ ’ , number literals need not.

18) Distinct Keyword is always used immediately after Select clause to eliminate duplicate rows in the result.

19) Difference between SQL & iSQL*Plus:

SQL
a) It is a command language for communicating with oracle server from any tool or application
b) ANSI standard SQL
c) Manipulates Data and Table definitions in Database

iSQL*Plusa) It is an oracle tool that recognize and submits SQL Statements to oracle server for Execution and contain its own command language
b) Oracle Proprietry
c) Does not allow manipulation of values in Database
d) Runs on a browser
e) Centrally Loaded, does not have to be implemented on each machine

20) We can restrict the rows returned by the query using Where Clause

21) Comparison Operators =, >, <, >=, <=, <> are used in where clause to compare one expression to another value or expression. Other comparison conditions are BETWEEN…AND…, IN, LIKE, IS NULL. In LIKE condition Escape option is used to match characters %, &, _ present in column values.

22) Logical conditions AND, OR, NOT are used in where clause to join two conditions to produce a single result. Order of Precedence is NOT,AND & OR.

23) In Order by Clause Null values are displayed in last for ascending sort & first in descending sort. By default we have ascending sort. We can also use column alias in order by clause.

24) SQL Functions Sometimes takes Arguments and Always Returns a Value.

• Single Row Functions: Operates on single row always & returns one result per row.

1) Character Functions:
a) Case Manipulation Functions: LOWER, UPPER, INITCAP
b) Character Manipulation Functions:
CONCAT
SUBSTR
LENGTH
INSTR
LPAD RPAD
TRIM
REPLACE
TRANSLATE

2) Number Functions:
ROUND
TRUNC
MOD

3) Date Functions:
ROUND
TRUNC
SYSDATE
MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY
• date+number=date(Adds no. of days to a date)
• date-number=date(Substracts no. of days from a date)
• date-date= Number of days(Substracts one date from another)
• date+number/24=date(Add no. of hours to a date)

4) Conversion Functions:
TO_NUMBER
TO_DATE
TO_CHAR with Dates (YY,MM,DY,DD,DAY)
TO_CHAR with Numbers (9,0,$,L, ., , )

5) General Functions:
NVL (Converts Null value to Actual Value)
NVL2 (If expr1 is not null, Return expr2 else expr3)
NULLIF (Compares 2 expr & returns null if they are
equal, or 1st expr if they are not equal)
COALESCE (Returns 1st not null Expr in the Expr list)

• Multiple Row Functions: Manipulate Group of Rows to give one result per group of rows.

25) If-Then-Else logic is implemented within a SQL Statements using
a) CASE Expressions
b) Decode Functions.

26) Comparison between Joins:

Oracle Proprietary
a) Equijoin
b) Outer Join
c) Self Join
d) Non-Equijoin
e) Cartesian Product

SQL 1999
a) Natural or Inner Join
b) Left or Right or Full outer Join
c) Join ON
d) Join USING
e) Cross Join

Note: To join ‘n’ Tables Together, we need a minimum of ‘n-1’ join condition.

27) Group Functions: Operates on set of rows to give one result per group. They ignore null values. Below are few of them
AVG
COUNT(*, expr, Distinct expr)
MAX
MIN
STDDEV
SUM
VARIANCE
Note: We cannot use group functions in where clause but we can use it in Select, Having, Order by clause. Group Functions can be nested to a depth of Two while Single Row Functions are nested to any level.

28) When using the Group By clause, make sure that all columns in the Select list that are not group functions are included in the Group By clause. Also you cannot use column alias in Group By clause. By default columns included in Group By clause are sorted in ascending order.

29) Having clause is used to restrict Groups. Having clause can precede the Group By clause but it is recommended that you place the Group By clause first because it is more logical. Also we can use Group By clause without Having Clause but we cannot use Having Clause without Group By.

30) The inner query is called subquery. It returns a value that is used by the outer query or the main query. We can place the subquery in a number of SQL clauses like Where, Having & From Clause.

31) Single Row Subquery: It returns single row & uses single row operators like <, >, <=, >=, <>
Multiple Row Subquery: It returns more than one row & uses multiple row operators like IN, ANY, ALL
Note: ANY: More than the minimum, ALL: More than the maximum

32) Substitution Variables: It is a Variable used to store values temporarily and values are substituted when statement runs. Values for the variables are supplied by user using ‘&’ or
predefined using DEFINE command. Value is present for the variable only till Session.
&: User is prompted every time the command is executed
&&: It is used if you want to reuse the variable value without prompting the user each time

33) Command Variables are used for sql report generation

34) Collection of DML Statements that form a Logical Unit of work is called as Transaction. In other words Transaction begins with first DML Statements & ends when,
a) Commit or Rollback Statement is issued
b) DDL Statement is issued
c) DCL Statement is issued
d) User Exits the session
e) Machine Fails or System Crashes

35) We can use subquery in Insert Statement, Set Clause of Update Statement & Delete statement.

36) DML Statements always Generates Rollback Information whereas DDL & DCL Cannot, Because DDL & DCL Statements are Automatically Committed while DML Statements need to be explicitly committed by user.

37) Default Keyword is used to set the default value for column. If default value is not specified for column then it sets to null if user do not enter any value.

38) Merge Statement is used to Update or Insert a row conditionally into a table, thus avoiding multiple Update Statement

39) Locks: This are the Mechanism which Prevents destructive interaction of 2 different users Transactions accessing the same Resource. There are 2 types of Locking in Oracle.
a) Implicit Locking ( Obtained Automatically by oracle )
b) Explicity Locking( Manually using commands )

• Implicit Locking
a) Share Lock: It is obtained at the table level during DML Operations. With Share lock mode, several transactions can acquire share locks on the same resource.
b) Exclusive Lock: It is acquired for each row modified by DML Statement. It prevents the row from being changed by other transactions until the transaction is committed or rolled back.

40) Savepoint: It is used as a Marker and created to Rollback the Informations upto that Marker point. This are not the database objects, hence are not stored in data dictionary.

41) Schema: It is a collection of Objects. Schema objects are logical structures that directly refer to the data in a database. Schema objects includes Tables, View, Sequences, Indexes, Synonyms, Clusters, Database Links, Stored Procedures.

42) Table & Column Names
a) Must begin with a letter i.e. alphabet
b) Must be 1 to 30 character long
c) Must contain only A-Z, a-z, 0-9, _, $, #,%
d) Must not duplicate any Oracle Reserved word
e) Must not duplicate the name of another object owned by the same user
f) Are Case Insensitive
Note: Table can have at the most 1000 columns.

43) There is another collection of tables & views in the Oracle Database known as the Data Dictionary. This collection is created and maintained by the Oracle Server and contains metadata i.e. data about data.

44) Three main Schemas of Oracle 9i Database are
a) SYSTEM
b) SCOTT (Users Schema)
c) SYS
• SYS Schema contains
1) All data dictionaries in the form of views
2) Oracle supplied procedures, functions, Packages
3) Table like AUDIT_ACTIONS, DUAL, STMT_AUDIT_OPTION_MAP, SYSTEM_PRIVILEGE_MAP, TABLE_PRIVILEGE_MAP. Etc.

There are 4 main Categories of Data Dictionary views
a) USER_: Contains information about objects owned by user
b) ALL_: Contains information about all of the tables accessible to the user
c) DBA_: Accessed by only DBA person
d) V$: Dynamic performance views, database server performance, memory & locking.

45) Some of the Data-types are,
a) VARCHAR2 (Max. Size = 4000 bytes)
b) CHAR (Max. Size = 2000 bytes)
c) NUMBER ( Size = 1 to 38)
d) DATE
e) LONG (2 GB)
f) LONG RAW (2 GB)
g) RAW ( 2000 bytes)
h) CLOB (4 GB)
i) BLOB (4 GB)
j) BFILE (4 GB)
k) Timestamp With Local Time Zone
l) Interval Year to Month
m) Interval Day to Second

46) We can Add, Modify & Drop columns using Alter Table Statement. The new column always becomes the last column in the table. The table must have atleast one column after it is altered

47) We can set one or more columns as unused using SET UNUSED option in ‘Alter Table’ Statement so that they can be dropped when the demand on system resources is lower. Unused columns are treated as if they were dropped i.e. it does not occupy disk space, hence response time is faster if we execute Drop clause. Structure as well as data of unused column is inaccessible.

48) Difference between Delete & Truncate Statements:
a) Delete Statement can be Rolled back whereas Truncate cannot
b) Deleting rows from a table will not remove disk space whereas Truncating a table remove all disk space
c) Structure remains same in both the statement
d) Truncate statement is faster than Delete because
1) Truncate is DDL statement & generates no Rollback information
2) Truncating table does not fire Delete triggers of the table

49) Constraints: This are used,
a) To prevent invalid data entry into tables
b) Enforce rules on the data in a table whenever a row is inserted, updated or deleted from that table
c) Prevents deletion of a table if there are dependencies from other table
d) Provides rules for Oracle tools such as Oracle Developer

Constraints are Defined at Table Level & Column Level. They are,
NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK

50) Not Null Constraint is defined only at column level & not table level. The composite Unique & Primary key are defined at Table level. A table can have one Primary key constraint but several Unique key constraint.

51) Syntax for Column level constraint:
column CONSTRAINT constraint_name constraint_type
Syntax for Table level constraint:
CONSTRAINT constraint_name constraint_type(column_name)

52) We can Add constraint, Add Not Null Constraint using Modify Clause, Drop Constraint, Drop Cascade Constraint, Enable Constraint, Disable Constraint using Alter Table Statement. But we cant modify the structure of existing constraint.

53) View: It is a Logical Representation of Subsets of data from a table. A view contains no data of its own but is like a window through which data from tables can be viewed or changed.

There are 2 types of Views:
a) Simple View: It is one that,
1) Derives data from only one table
2) Contains no functions of groups of data
3) Can perform DML operations through view into the table

b) Complex View: It is one that,
1) Derives data from many tables
2) Contains functions or groups of data
3) Does not allow DML operations through the view.

54) While Creating a view,
a) It does not contain an Order By Clause. Order By clause is specified when we retrieve the data from view.
b) With Check Option Constraint constraint_name specifies that only rows accessible to the view can be inserted or updated.
c) With Read Only Option Ensures that no DML Operations can be performed on view.

55) We cannot perform Delete Operation on view if it contains,
a) Group functions
b) A Group By clause
c) Distinct Keyword
d) Pseudocolumn Rownum keyword

56) We cannot Modify the view if it contains,
a) Group functions
b) A Group By clause
c) Distinct Keyword
d) Pseudocolumn Rownum keyword
e) Columns defined by expression(Eg: sal*12)

57) We cannot Insert through view if it contains,
a) Group functions
b) A Group By clause
c) Distinct Keyword
d) Pseudocolumn Rownum keyword
e) Columns defined by expression(Eg: sal*12)
f) Not Null columns in the Base tables that are not selected by view.

58) Inline View: It is created by placing subquery in From Clause and giving that subquery an alias. It is used to perform Top-N-Analysis.

59) Sequence: It is Database object & is created to generate a primary key value. Sequence values are referenced by Pseudo-columns Nextval & Currval. This columns are used in Select, Insert & Update Statement, But they are not used in following contexts:
a) Select List of a view
b) Select statement with Distinct Keyword, Group By,Having & Order By clause
c) Subquery in Select, Update, Delete statement
d) Default expression in a Create Table or Alter Table statement.

Note: We can Prevent Oracle from using an Index & do full Table Scan by Adding Expressions or Hints in SQL Queries.

60) Nocache Option is used while creating a sequence to view the next available sequence value without incrementing it, because if the sequence is cached values in the memory are lost if system crashes.

61) START WITH Options cannot be changed using Alter Sequence.

62) Index: It is a schema object that can speed up the retrieval of rows using a pointer. If index is not created on column, whole table scan occurs. There are types of Indexes:
a) Unique Index: Created automatically by oracle server for columns in a table with Primary Key & Unique Key Constraint. Name of the index is same as that of constraint.
b) Non-Unique Index: Created Explicitly by User especially for Foreign Key column

63) We should create an index if,
a) Columns contains a wide range of values
b) Column contains large number of null values
c) One or more columns are frequently used together in a Where clause or join condition.
d) Table is large and most queries are expected to retrieve less than 2-4% of rows.

64) Function Based index is based on Expression or Functions. Dropping a Table will also drops all its Related Non-Unique Indexes.

65) Synonym: It is created to provide an alternative name to database Objects. DBA can create a public synonym accessible to all users. Only DBA can drop public synonym.

66) Privileges are the right to Execute particular type of SQL statements. There are 2 types of privileges:
a) System Privileges: Gaining Access to the database
Eg: Create User, Create Table, Create Procedure, Select Any Table, Create Session.etc.

b) Object Privileges: Manipulating the content of the objects in the database.
Eg: Alter, Select, Insert, Update, Delete, Execute, Index & References.

67) Role: It is a named group of related privileges that can be granted to the user. Grant Statement is used to allocate System & Object Privileges to user. ‘With Grant Option’ gives user the authority to grant same privileges to any other user.
Revoke Statement is used to take out privileges granted to the user. If user A revokes the privileges from user B then privileges granted to user C & D are also revoked.

68) A database link is a pointer that defines a one way communication path from an Oracle database server to remote database server. One way means users of Database A can accesss database B only if link of B is stored in A, Similarly users of Database B can access database A only if link of A is stored in B. We cannot Grant Privileges to Remote objects.

69) SET Operators: It combines the results of two or more component queries. Queries containing SET operators are called compound queries.
a) UNION: All distinct rows selected by either query
b) UNION ALL: All rows selected by either query, including duplicates
c) INTERSECT: All distinct rows selected by both queries
d) MINUS: All distinct rows that are selected by 1st Select Statement and not selected in 2nd Select Statement.

70) CHR is a SQL Function that converts an ASCII code to its corresponding Character. Whereas ASCII Function is used to convert Character to ASCII Value.

71) There are only 2 Objects which can be created by same name in same schema Table & Trigger

72) Rowid: It is a Hexadecimal Representation of a Row in a Table. Rowid can only be Changed if we ‘Enable Row Movement’ on a Partitioned Table. Rowid’s of Deleted Rows can be Reused if Transaction is Committed.

73) We can Execute Operating System Commands from SQL Prompt using ! or HOST, however HOST perform Variable Substitution(i.e.&,&&) while ! Cannot.

74) Some of the Important Data Dictionaries are,

a) USER_CATALOG
b) USER_COL_PRIVS
c) USER_CONSTRAINTS
d) USER_CONS_COLUMNS
e) USER_DB_LINKS
f) USER_DEPENDENCIES
g) USER_ERRORS
h) USER_EXTENTS
i) USER_INDEXES
j) USER_IND_COLUMNS
k) USER_JOBS
l) USER_LIBRARIES
m) USER_OBJECTS
n) USER_ROLE_PRIVS
o) USER_SEGMENTS
p) USER_SEQUENCES
q) USER_SNAPSHOTS
r) USER_SYS_PRIVS
s) USER_TABLES
t) USER_TABLESPACES
u) USER_TAB_COLUMNS
v) USER_TAB_PRIVS
w) USER_TRIGGERS
x) USER_USERS
y) USER_VARRAYS
z) USER_VIEWS

Que: How can a SQL Script be Executed Automatically as soon as SQL*Plus is Invoked?
Ans: When SQL*Plus Starts up, it will look for a global login script called ‘glogin.sql’, if found this script will be executed. Thereafter it will try to find a local login script called ‘login.sql’, if found it will execute it. Both the Scripts are found in C:\Oracle\Ora90\sqlplus\admin\glogin.sql

Note: DBA can get a Grant over Dynamic Performance tables like v$statname, v$session, v$sesstat by running the script C:\Oracle\Ora90\sqlplus\admin\plustrce.sql

Note: Path for Superb Help: C:\Oracle\Ora90\sqlplus\admin\iplus\

Que: How to Display Number Values in Words?
Ans: Select Sal, (to_char(to_date(Sal,'j'),'jsp')) from emp;
Here ‘j’ means Julian Day, the number of days since January 1,4712 BC & sp means to spell out.

75) Partitioning: It Enables Tables & Indexes or Index-Organized tables to be subdivided into smaller manageable Pieces & these each small Piece is called Partition.
They are of following Types:
a) Range Partitioning
b) Hash Partitioning
c) List Partitioning
d) Composite Range-Hash Partitioning

76) Cluster: A cluster provides an optional method of storing table data. A cluster is comprised of a group of tables that share the same data blocks, which are grouped together because they share common columns and are often used together. For example, the EMP and DEPT table share the DEPTNO column. When you cluster the EMP and DEPT, Oracle physically stores all rows for each department from both the EMP and DEPT tables in the same data blocks. You should not use Clusters for tables that are frequently accessed individually.

77) Difference between Nested Subquery & Correlated Subquery:

Nested Subquerya) Inner Query runs first and executes once, returning values which are to be used by the Main query or outer query
b) Outer query is driver by Inner Query

Correlated Subquerya) A Correlated Subquery or Inner Query execute once for each candidate row considered by outer query
b) Inner Query is Driven by Outer Query

78) Difference between Translate & Replace:
Translate function converts each character in String with specified one whereas
Replace function replaces part of the string in continuity by another sub-string.