Attachment Functionality

Attachment Functionality

About Attachments:

Attachment feature enables users to link files like images, word files, emails e.t.c to a particular form/function/record. Once you link a file to a form in oracle it will be saved on your server so that you can retrieve it easily when ever you want it.

Supported Formats:

You can link the following types.

·         File (word, pdf, excel, images)

·         Short Text (Notes)

·         Long Text (emails and long descriptions)

·         Webpage (intranet or internet pages)

Related Tables:

·         FND_ATTACHED_DOCS_FORM_VL

·         FND_ATTACHED_DOCUMENTS

·         FND_ATTACHMENT_BLK_ENTITIES

·         FND_ATTACHMENT_BLOCKS

·         FND_ATTACHMENT_FUNCTIONS

·         FND_ATTACHMENT_REPLICATION

·         FND_DM_FOLDERATTACHMENTEXT 

Enable Attachments

It can be better explained using an example so follow the below example to enable the attachment link in Payments Bank Screen from Account Payables

·         Collect the information of the form where you want to enable attachments

·         Define Entities

·         Define Categories or use Miscellaneous category provided by oracle

·         Define Attachment Functions

·         Using Attachments

Form Information

Navigation: Payables Manager à Setup à Payments à Banks

Short Name: APXSUMBA

Form Name: Set Up Bank Information

Block Name: BRANCHES

Primary Key: BANK_BRANCH_ID

Table Name: AP_BANK_BRANCHES

Define Entities:

Navigation: Application Developer ResponsibilityàAttachments à Document Entities

Create the new record

Table: AP_BANK_BRANCHES

Entity ID: AP_BANK_BRANCHES

Entity Name: AP_BANK_BRANCHES

Prompt: Banks

Application: Payables

Close the form.

Navigation: Application Developer à Attachments à Attachment Functions

Enter the following

Type: Form

Name: APXSUMBA

User Name: Set Up Bank Information

Session Context Field:

Enabled: Check

Save.

Click on Categories and assign Miscellaneous

Save.

Close Categories Form.

Click on Blocks

Block Name: BRANCHES

Method: Allow Change

Secured By: None

Context 1: BRANCHES.BANK_NAME

Context 2: BRANCHES.BANK_BRANCH_ID

Save Now.

Click Entities

Entity: AP_BANK_BRANCHES

Display Method: Main Entity

Privileges: Always

Primary Key Fields: BRANCHES.BANK_BRANCH_ID

Save and Close.

Use Attachments:

Navigation: Payable Manager >> Setup >> Banks

Click to open

Now Query any record. If you click on attachments now it will ask you to query any record. This is because attachments are stored at record level and not at form level. ofcourse you can attach documents at form level also for that while defining your Block you should not give your context fields and primary key.

Press F11 and then CTL+F11

Now Click on attachments ICON

Enter Category as Miscellaneous and Data Type as Short Text. You can choose the data type as you need. and finally enter some text in the yellow area.

Save and Close the Attachments Form.

Now Query the same record in Banks Screen

You should see that paper clip icon changes to

Click on it to see your attached Text.

Note: The attachments you saved for this bank can not accessed when you query another bank. Which means all your attachments are saved at record level. You can restrict this level to form, function or record level.

Set who Columns

Set who columns in pre-insert trigger in block level trigger

FND_STANDARD.SET_WHO;


The WHO columns are

CREATED BY,
CREATION_DATE,
LAST_UPDATE_BY,
LAST_UPDATED_DATE

R12 Forms cmd

Command to compile the form  in R12

frmcmp_batch module=$XXAYE_TOP/forms/US/XXAYE_DMS_DO.fmb userid=username/password@instance output_file=$XXAYE_TOP/forms/US/XXAYE_DMS_DO.fmx module_type=form batch=yes compile_all=special

set path from custom top

> FORMS_PATH=$FORMS_PATH:$AU_TOP/forms/US:$XXAYE_TOP/forms/US

>export FORMS_PATH

Form Commands

For compiling a form in the application,we need to set the forms path

Eg. in 11i

Login in host(unix)

pwd ->to find the path
cd $folder_name->to move to that folder
ls->to list the folders in the current path


set path

$cd $AU_TOP/forms/US

Compile Command

$f60gen module= XXFORM.fmb module_type=form output_file=$XXCUST_TOP/forms/US/XXFORM.fmx userid=apps/apps11emrmd module_type=form batch=yes compile_all=special

Adding LOV list to forms

1.Create a record group with the query required for lov list.
2.Create a LOV with the created Record group and give the subclass information as LOV
3.Map the field from the form in LOV mapping property field.

The steps to create an LOV manually are

• Create the record group. You will need to type in the query on which the record group is
based.
• Create the LOV and set its Record Group property to the appropriate record group.
• Set the LOV property Column Mapping. You must type in the columns and their
headings, and then select a return item for each item that you want to populate from
the LOV.
• Assign the LOV to any text items from which you want the LOV to be available.

Retrieving Restricted Data

Do not use quotation marks with character and date items.
• The LIKE operator is implied with % or _.
• Use hash (#) in front of SQL operators.
• Use Query/Where for complex query conditions.
• Use default date format (DD-MON-YY) in Query/Where.
• Use quotes around literals in Query/Where.

Global Variables

Global Variables remains same for the session.

But using of parameter is recommaned compared to usage global variables.

e.g:

create or replace package glob as
procedure G_var;
end;

Create or replace package body as
g1 varchar2(10):='Global Variable';--Global varable declaration
procedure g_var as
begin
dbms_output.put_line(g1);
if g1='Global Variable'
then
g1:='Executed Global Varable';
dbms_output.put_line(g1);
end;
end;

now when u execute this package as glob.g1 the output will be Global Variable and Executed Global Variable.

In forms u can call a global variable in pre form and when-new form instance trigger:

:GLOBAL. := null;
in when new form tigger
:Global.Application_id:='PROD';

Setting who columns on validation

Set Who columns in pre-insert:
BEGIN
DATA BLOCK NAME;
FND_STANDARD.SET_WHO;
PACKAGE.VALIDATE_APPROVAL;(Define this procedure in program units)
END;
Set Who columns in pre-update:

begin

XX_VALIDATE1;--procedure 1
XX_VALIDATE2;--procedure 2
fnd_standard.set_who;
PACKAGE.VALIDATE_APPROVAL;(Define this procedure in program units)
end;
Set triggers at when-new-record-instance-level
DECLARE
v_count number;

v_count2 number;

BEGIN

SELECT count(*) into v_count

FROM fnd_lookup_values
WHERE lookup_type = ‘Receipt Number’(count records ad new in db)

and sysdate between start_date_active and nvl(end_date_active,sysdate)

and lookup_code=fnd_global.user_id;
IF v_count = 0 then
--set datablock and procedure to validate SET_ITEM_PROPERTY('XXGOD_JOB_CARD.APPROVE',ENABLED,PROPERTY_FALSE);
END IF;
IF v_count = 1 THEN

--SET_ITEM_PROPERTY('XXGOD_QUOTATION_HDR1.SALES_ORDER',ENABLED,PROPERTY_FALSE);

IF :XXGOD_JOB_CARD.APPROVALS ='Submitted' THEN

SET_ITEM_PROPERTY('XXGOD_JOB_CARD.APPROVE',ENABLED,PROPERTY_TRUE);

ELSE

SET_ITEM_PROPERTY('XXGOD_JOB_CARD.APPROVE',ENABLED,PROPERTY_FALSE);
END IF;
END IF;
END;
Validating Procedure and Package:


PACKAGE XX_VALIDATE IS
PROCEDURE VALIDATE_APPROVAL;
PROCEDURE VALIDATE_APPROVAL1;
PROCEDURE XVALID_DESC_PRO(EVENT IN VARCHAR2);
END;




PACKAGE BODY XX_VALIDATE IS
PROCEDURE VALIDATE_APPROVAL IS

BEGIN

IF :DATA_BLOCK_NAME.APPROVALS IS NULL THEN

FND_MESSAGE.SET_STRING('Status needs to be selected');

FND_MESSAGE.SHOW;

RAISE FORM_TRIGGER_FAILURE;

END IF;

IF :DATA_BLOCK_NAME.APPROVALS!='New'THEN

FND_MESSAGE.SET_STRING('This is a new Job card,select the status as New');
FND_MESSAGE.SHOW;

RAISE FORM_TRIGGER_FAILURE;

ELSE

XX_VALIDATE_1(CALLING ANOTHER PROCEDURE);
END IF;

EXCEPTION

WHEN OTHERS THEN
FND_MESSAGE.ERROR;

RAISE FORM_TRIGGER_FAILURE;
END;
---------------------------------------------------------------------------------------------

PROCEDURE VALIDATE_APPROVAL1 IS
CURSOR C1 IS SELECT APPROVALS FROM table_name WHERE JOB_ID=:data_bolck.item;
v1 VARCHAR2(20);

BEGIN

OPEN C1;

FETCH C1 INTO v1;

IF V1!=:DATA_BLOCK_NAME.APPROVALS THEN

IF V1='New' AND :DATA_BLOCK_NAME.APPROVALS!='Submitted' THEN
FND_MESSAGE.SET_STRING('This is a new Job card,needs to be Submitted');

FND_MESSAGE.SHOW;

RAISE FORM_TRIGGER_FAILURE;

END IF;
IF V1='Submitted' and :DATA_BLOCK_NAME.APPROVALS not in('Approved','Rejected') THEN
FND_MESSAGE.SET_STRING('Submitted Job card,needs to be Approved or Rejected');
FND_MESSAGE.SHOW;
raise form_trigger_failure;

END IF;


IF V1='Rejected' and :DATA_BLOCK_NAME.APPROVALS not in( 'Rejected','Submitted','Closed') THEN
FND_MESSAGE.SET_STRING('Rejected Quotation can only be Submitted or Closed');
FND_MESSAGE.SHOW;
raise form_trigger_failure;
END IF;
if v1='Approved' and :DATA_BLOCK_NAME.approvals not in('Inactive','Active') then
fnd_message.set_string('Approved Job can be Active or Inactive');
fnd_message.show;
raise form_trigger_failure;

end if;


IF v1='Inactive' and :DATA_BLOCK_NAME.approvals!='Active' then
FND_MESSAGE.SET_STRING('Select the status as Active');
FND_MESSAGE.SHOW;
raise form_trigger_failure;

END IF;


IF v1='Active' and : DATA_BLOCK_NAME .approvals!='Closed' then
FND_MESSAGE.SET_STRING('Active Job card has to be Closed');
FND_MESSAGE.SHOW;
raise form_trigger_failure;
END IF;
if v1='Closed' and : DATA_BLOCK_NAME.approvals!='Closed' then
FND_MESSAGE.SET_STRING('Closed Job card can not be modified');
FND_MESSAGE.SHOW;
raise form_trigger_failure;
end if;
END IF;
CLOSE C1;
EXCEPTION
WHEN OTHERS THEN
FND_MESSAGE.ERROR;
RAISE FORM_TRIGGER_FAILURE;
END;


PROCEDURE XGOD_DESC_PRO(EVENT IN VARCHAR2)
IS
BEGIN
IF EVENT='PRE-FORM' THEN

FND_DESCR_FLEX.DEFINE(BLOCK => 'XXGOD_JOB_CARD',FIELD => 'DFF1',
APPL_SHORT_NAME => 'PO',DESC_FLEX_NAME => 'JobCardDFV');
END IF;
IF EVENT='WHEN-NEW-FORM-INSTANCE' THEN

FND_DESCR_FLEX.DEFINE(BLOCK => 'XXGOD_JOB_CARD',FIELD => 'DFF1',APPL_SHORT_NAME => 'PO',DESC_FLEX_NAME => 'JobCardDFV');

FND_DESCR_FLEX.DEFINE(BLOCK => 'XXGOD_JOB_CARD',FIELD => 'DFF',
APPL_SHORT_NAME => 'PO',DESC_FLEX_NAME => 'JobCardDFF1');

END IF;

EXCEPTION

WHEN OTHERS THEN

RAISE FORM_TRIGGER_FAILURE;
END;
END;

FORMS 3

 Creating LOVs



1. Start a new module by clicking on File - New - Form.


2. Change Module’s name to TEST_LOV.


3. Set up DEPT and EMP data blocks and its relationship.


4. Click Object Navigator
Forms
LOVs and then on tool bar click Create button.


5. In the New LOV dialog box select


• New Record Group Based on the Query Below...


6. Enter the following code


SELECT distinct job


FROM emp


ORDER BY job


7. Click OK. (The hour glass will no go away)


8. Double click to open the property palette for the new LOV (under LOVs).


9. Change Name to JOB_LOV


10. Click on Column Mapping Properties (under Functional).


11. Click on More button.


12. Click Column Name JOB then in Return Item box type Emp.JOB. This specifies the destination into which the LOV will place the job value.


13. Enter a display width of 50. Click OK


14. Change Object Navigator
Record Groups
LOV name to JOB_LOV


15. On Layout Editor double click on JOB to bring up property palette and set List of Values to JOB_LOV


16. Set Validate from List to YES.


17. Save file under c:/orant/Forms50/Class/TEST_LOV.FMB


18. Run form.






• LOV, using auto-reduction, to populate a job if it finds one job that matches the first character that you typed. Otherwise a pup-up window open if more than on job are found or invalid job is entered.

Forms-2

Practice 11 - Create Data Blocks with Relationships

Creating Master Block

================

1.   Right click on Object Navigator | Forms | Data Blocks then select

Data Block Wizard.

2.   At Data Block Wizard - select

      Table or View

Then click Next

3.   Enter DEPT in the edit box of Table or view.

Then click Refresh

Click  select all to database items

Then click Next (Enforce Data Integrity, Content Canvas, Single Record, Form Style, No Scrollbar)

4.       Complete creating the Dept Block

5.      Detail Block Creation

6.      Create a new data block

7.      Select the Table (ex. EMP), enforce data integrity

8.      Select

9.   Click Create Relationship (Auto create relatioship should be checked)

Click OK for the next dialog box.

10. Use the same canvas used by Master block, Should be Multi-Record (in most cases), Style should be Tabular(to display multiple records), Scrollbar should be displayed)

11. Use the default Width and Height

Then click Next

12. Select

       Form

Then click Next

13. Frame Title: DEPARTMENT-EMPLOYEE DETAILS

Records Displayed: 1

Distance Between Records: 0

Then click Next

Then click Finish

14. Compile and Run Form

Forms

Practicals:

Single Table Maintenance - (Create all the single record maintenance forms in your project)

 Practice 1 - Create a Data Block Using the Data Block Wizard

1.   Launching the Form Builder

·                                 Start - Programs - Developer 2000 R2.1 - Form Builder.

2.   At Welcome to the Form Builder - select

·         Use the Data Block Wizard

Then click OK

3.   At Data Block Wizard - select

·         Table or View

Then click Next

4.   Click Browse…

Connect to database SCOTT / TIGER

Then click Connect

Click OK at the next dialog box.

At select DEPT then click OK

Click >> select all to database items then click Next

(Enforce Data Integrity, Content Canvas, Single Record, Form Style, No Scrollbar)

5.   Select

·         Just create the data block

Then click Finish

6.   Change MODULE1 to DEPT

Practice 2 - Create and Modifying Layouts Using the Layout Wizard

1.   Right click on Object Navigator | Forms | Data Blocks | DEPT

2.   Select Layout Wizard

3.   Select Content type

Then click Next

4.   Click >> select all to Display items

Then click Next

5.   Use the default Width and Height

Then click Next

6.   Select

·         Form

Then click Next

7.   Frame Title: DEPT

Records Displayed: 1

Distance Between Records: 0

Then click Next

Then click Finish

8.   Compile and Save the file in D:/Oracle_Dev/TEST_DEPT

9.   Run Form

10. Click Execute Query button to populate data into data block.

11. Click Next / Previous button for navigation.

12. Exit Form

Practice 3 - Modifying Layouts Using the Layout Wizard

1.   Right click on Object Navigator | Forms | Canvases | Graphics | FRAME3

2.   Select Layout Wizard

3.   Remove DEPTNO from Displayed items to Available Items

Then click Next

4.   Use the default Width and Height

Then click Next

5.   Select

·         Tabular

Then click Next

6.   Frame Title: DEPT

Records Displayed: 5

Distance Between Records: 0

Then click Next

Then click Finish

7.   Compile and Run Form

8.   Exit CANVAS2

Restricting Queries with SQL statements

Practice 5 - Restricting queries with field-level Criteria

1.   Click on Enter Query Button.

2.   Move cursor to Sal

3.   Type:

:Salary

4.   Click Execute Query Button.

5.   Type the following in the pop up dialog window.

:Salary between 1200 and 3000

6.   Click OK.

Inserting, Updating and Deleting Records

Practice 6 - using Form to update records

1.   Click on Execute Query Button.

2.   Modify any record.

3.   Click Save Button.

Practice 7 - using Form to insert records

1.   Click on Insert Record Button.

2.   Enter any data in the blank area.

3.   Click Save Button.

Practice 8 - using Form to delete records

1.   Click on Execute Query Button.

2.   Navigate to any that you wont to delete.

3.   Click on Delete Record Button.

4.   Click Save Button.

Practice 10 - Creating a hints

1.   Right click on EXIT button

2.   Select Property Palette

3.   Find Hint under Help section.

4.   Type Click to Exit This Program

5.   Exit and run.

Practice 11 - Creating a ToolTips

1.   Right click on EXIT button

2.   Select Property Palette

3.   Find ToolTip under Help section.

4.   Type Exit

5.   Exit and run.