add

About Me

My photo
Oracle Apps - Techno Functional consultant

Friday, September 30

Total no of Hits on my blog (As per date -Sep30 )

With in less than two months my blog reached to this hit count..  Congrats Me...

I am very much happy to share this screenshot as my Post for this day.....




Thank you all, for getting 1111 hits to my blog in less than 2 months.

General Sql Queries

select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL )
decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )
from dual

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TO_CHAR(dt+1,'iw') week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Thu",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Fri",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sat"
FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt FROM all_objects WHERE
ROWNUM<= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y')) GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt+1, 'iw' )) ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week)

COUNTS
Provides a count of all (distinct) values in a particular column or table. The column can be either alpha or numeric. Null values in the column are included in the count.
SELECT catalog_nbr, COUNT (*) as Num_of_Stu
FROM XX_STUDENT
WHERE acad_group = 'TCHE'
AND acad_career = 'UGRD'
AND stdnt_enrl_status = 'E'
AND subject = 'FSOS'
GROUP BY catalog_nbr

Sums
Provides a sum of all (distinct) values in a particular column. The column must be numeric. Null values in the column are not included in the sum
SELECT acad_career, subject, SUM (unt_taken) units_taken
FROM XX_STUDENT
WHERE subject = 'SOIL'
GROUP BY acad_career, subject

Having
Use the HAVING clause to restrict which groups of rows defined by the GROUP BY clause are returned by the query
SELECT catalog_nbr, class_section, COUNT (*) AS num_of_stu
FROM XX_STUDENT
WHERE acad_group = 'TCHE'
AND acad_career = 'UGRD'
AND stdnt_enrl_status = 'E'
AND subject = 'FSOS'
AND component_main = 'DIS'
GROUP BY catalog_nbr, class_section
HAVING COUNT (*) > 50

Case/Decode/NVL
Case and Decode statements both perform procedural logic inside a SQL statement without having to use PL/SQL.
SELECT DISTINCT a.NAME,
CASE
WHEN a.emailid_2 IS NULL THEN a.emailid_1
ELSE a.emailid_2
END
email_add
FROM XX_STUDENT a, XX_STUDENT1 b
WHERE a.emplid = b.emplid AND b.acad_prog = '32UGR'

SELECT DISTINCT a.NAME,
DECODE (a.emailid_2, NULL, a.emailid_1, a.emailid_2) email_add
FROM XX_STUDENT a, XX_STUDENT1 b
WHERE a.emplid = b.emplid AND b.acad_prog = '32UGR'

SELECT DISTINCT a.NAME,
NVL (a.emailid_2, a.emailid_1) email_add
FROM XX_STUDENT a, XX_STUDENT1 b WHERE a.emplid = b.emplid AND b.acad_prog = '32UGR'

All of these queries will return a list of student names with there secondary email addresses unless they didn’t report a secondary address, then it will return their primary email address
It is best to use the CASE statement when comparing ranges or more complex logic.

SELECT a.NAME,
(CASE
WHEN a.vac_hrs_taken_ytd <= 40 THEN 'GET A LIFE' WHEN a.vac_hrs_taken_ytd BETWEEN 41 AND 100 THEN 'NEED A BREAK?' WHEN a.vac_hrs_taken_ytd >= 101 THEN 'WELL RESTED'
END) mental_wellbeing
FROM XX_STUDENT a
WHERE a.deptid = '831A'
AND a.fisc_yr = '2003'
AND a.pay_period = '06'
AND a.empl_status = 'A'
ORDER BY 2

Rollup
The use of a ROLLUP clause in the GROUP BY part of the SQL expression displays subtotals and grand totals depending on it’s use.
SELECT NVL (catalog_nbr, 'GRAND_TOTAL') catalog_nbr, class_section,
SUM (unt_taken)
total_units, COUNT (*) num_of_stu
FROM XX_STUDENT WHERE acad_group = 'TCHE'
AND acad_career = 'UGRD'
AND stdnt_enrl_status = 'E'
AND subject = 'FSOS'
GROUP BY ROLLUP (catalog_nbr, class_section)

Inline Views
You can use a SQL statement in the FROM clause of a SQL statement. This is called a inline view. Oracle treats the data set that is returned from the inline view as if it were a table.
SELECT a.NAME, a.office1_phone
FROM XX_STUDENT a,
(SELECT x.emplid
FROM XX_STUDENT1 x
WHERE x.deptid = '831A'
AND x.status_flg = 'C'
AND x.job_terminated ='N') b
WHERE a.emplid = b.emplid

Interview Questions on Oracle Apps AR module – Functional

1. How many Address's can have one Customer?
Primary Address, Bill – To – Address, Ship – To – Address

2. Customer Number Always Automatic / Manual?
Any thing Either Manual or Automatic

3. What are the Mandatory Address's you should create a Customer for Communicate him?
Remit – To – Address

4. Can U Merge the Customers? If How?
Using the Merging Customer Window

5. What is Accounting Rules?
It is For Generating Revenue Recognition Programs like Monthly, Quarterly

6. What is Invoicing Rules?
The Invoicing Rules Helps you when you receive data from Outside systems like Auto Invoices how the data should insert and It contains 2 rules Advance Invoice, Arrears Invoice.

7. Where should the customers send the payments of Invoices?
Remittance Banks or Vendor Places

8. What is the Transaction Type?
It describes us Whether you want transfer the Receivables data into General Ledger or not. And also when ever you create an invoice what are the accounts should be effected and also the sign of Transaction also.

9. What is a Transaction Source?
It is For Invoice or Invoice Batch numbers whether automatically or manually

10. How many Transactions we have?
Six, Credit Transactions: Invoice: Debit Memo: Charge back: Guarantee: Deposit

11. How can i reduce the Invoice amount?
Using with Credit Transactions

12. What are the Accounts to be use in Transaction Types (Few)?
Revenue, Receivables, Freight, Tax, Clearing, Unearned, Unbilled

13. How can i Assign a Deposit amount to an Invoice?
In the Invoice Window "Commitment" Region

14. What is the Guarantee?
It is agreement between both sides for goods or services in the future , specific range of periods

15. Give the Navigation for Credit Transactions?
Transactions/Credit Transactions

16. How many ways you can apply the Receipt Amount?
Application: Mass Apply

17. How will you know a Customer Balance Amount?
Using with the Customer Account Overview window

18. Can U Define Customer Agreements using with AR?
No, In the Oracle Order Entry Module

19. What are Aging Buckets?
It is for Outstanding Reports purpose the no of days in various ranges

20. How will U View the Outstanding Balance of a Customer?
Generating the Aging Buckets Report

Interview Questions on Inventory,Purchasing

1. What is an Organization & Location?
2. What are the KeyFlexFields in Oracle Inventory Module?
3. What are the Attributes of Item Category & System Items?
4. What are the KeyFlexFields in Oracle Purchasing & Oracle Payables?
5. What are the KeyFlexFields in Oracle HumanResources & Oracle Payroll?
6. How would you create an Employee (Module Name) Describe?
7. What is a Position Hierarchy? Is there any restriction to create that?
8. To whom we call as a Buyer? What are the Responsibilities?
9. How do you Setup an Employee as a User – Navigation?
10. How many Approval Groups we have? Describe?
11. Describe the Types of Requisition?
12. How many Status's and Types For RFQ's & Quotations Describe?
13. How many Types of Purchase Orders We Have?
14. What is a Receipt?
15. What is Catlog Rfq?
16. Give me online about Planned Po?
17. How can the manger view the Approval Documents Information?
18. Whether the manager can forward to Any other person? How?
19. Can u resend the document your subordinate how?
20. What is Po Summary?

Answers:

1. It is a Ware House Which you can Store the Items, And You can setup your business Organization Information Like Key Flexfilelds,Currency,Hr Information and starting time and end time. Location's are like godown place, office place, production point.

2. System items,Item Categories,Account Alias,Sales Order,Item Catalog,Stock Locators

3. The classification of items are Category Like Hard ware and Software, where as the Systems are individual items like Cpu,Key Board.

4. No Flexfields, But the help of Inventory And Human Resources we can use

5. Job Flexfield,Position Flexfield,Grade Flexfiled,Costing,People Group Flexfield,Personal Analysis Flexfield.

6. If Human Resource is Installed Hr/Payroll, If Not I can create Employee using with Gl,Ap,Purchasing,Fixed Assets

7. It is a Grouping of Persons for Approving and Forwarding the Documents from one person to another person, there is no restriction.

8. The Employee is nothing a Buyer, who is responsible for Purchase of Goods or services.

9. Security—-User—-Define, System Administration Module

10. Document Total,Accounts,Items,Item Categories,Location

11. Purchase Requistion,Internal Requistion

12. In Process,Print,Active,Closed for Rfq's In Process,Active,Closed for Quotation

13. Standard,Purchase Agreement,Blanket Po,Planned Po

14. To Register the Purchase orders/Po lines for shipment purpose

15. It contains Price breaks with different quantity levels

16. For a Agreement for long period for goods

17. Notifications Window

18. Yes, In the Notifications Window under the Forward to Push Button

19. Yes, In the Notifications Window under the Forward to Push Button

20. The Purchase Order Summay Information like total lines, and status.

Script To find Oracle API's for any module

SELECT
SUBSTR(a.OWNER,1,20) ,
SUBSTR(a.NAME,1,30) ,
SUBSTR(a.TYPE,1,20) ,
SUBSTR(u.status,1,10) Stat ,
u.last_ddl_time ,
SUBSTR(text,1,80) Description
FROM
dba_source a,
dba_objects u
WHERE
2 =2
AND u.object_name = a.name
AND a.text LIKE '%Header%'
AND a.type = u.object_type
AND a.name LIKE '%XXXX%'
ORDER BY
a.owner,
a.name;

PURCHASE ORDER FAQ'S

Q. AME is possible in PO approval ??
NO. AME for Req approval

Q. Can we create Position heiracchy for Requisition Approval
Yes

Q. vacation rule has to be created by the same user...if being as a first person in the approver goup the user not able to create vacation rule and goes on leave...then any other way, by which we can handle that out of scope

Q. Can I get the recording version of yesterda's session?
No.

Q. Where did we do setups for taxes
Ebiz Tax

Q. Is AME diff in 11i and R12
Conceptually, AME is same or 11i and R12 but the look and feel and navigation is completely different.

Q. Any profile for confidentiality of Quotation?
No. Anybody with access to quote and quote analysis screen has access to quotations

Q. when we delete approved record, if it is by other user, will he be notified for the same
No.

Q. How to protct cofidentialiy and modification of quot from others
- to prevent access to quote screen
- make the quote screen read only

Q. is there any work flow proces behind this?
no

Q. Can we have PO without RFQ or quotes for that matter.
Yes. We can have PO with RFQ and quotes. But its not a good practice.
Purchase Manager can be questioned why he gave order to some supplier. He will always have 2-3 quotes with him.

Q. but for blanket type of PO do you think quotes are required?
2 ways to determine price for an item
- Quotations - document provided by vendor to supply at a price
- Blanket PO - agreement between vendor and us

Q. If we dont want other buyer can see qout of other buyer even they have access to RFQ screen ??
only related buyer only can see his Quot not other one which is not related to him ??
Personalization. Without personalization, its not possible.

Q. Can we copy Catalog RFQ to Standard quotation?
Yes. Copy Catalog RFQ to Catalaog quote or Standard quotation. Copy Bid RFQ to Bid quotation.

Q. Can you show us Creating PO from Requisition having RFQ as Source Doc.
RFQ cannot be source doc. We can have Quote as Source and we will be creating PO from Req using Quote as Source.

Q. Yes but not for release.
Question not clear

Q. What about negotion process in this ??
Negotiation is possible in Oracle Sourcing. Out of Scope.

Q. Are u going to take Planned Purchase order
Yes. That will be covered later.

Q. Will we cover Schedule release VMIand Consigned.?
Out of scope. Inventory related. Kindly attend Inventory training.

Q. Is there any analytics available in this release to freeze the minimum quote automatically?
No. Quote analysis and approval is a manual process. No automation is provided.

Q. Can you cover the Advance Pricing intergration in Purchasing?
This will be covered conceptually. There wont be any practicals on the same.

Q. What about Bill of distribution??
Sourcing Rules - ASL is required. Will cover Sourcing rules later.

Q. Sourcing means for RFQ ??
Selecting the Source of Supply of material.
Sourcing - Sourcing Rules - automatically create PO from requisitions

Q. While using ASL for Commodity theall items listed under that commodity will get imapcted . believe it's true can u confirm .

Yes. I will show you guys a chart



Q. How the Supplier status behave differently in Global & Local ASL?
Local ASL will override Global
Local (M1) - debarred Global - Approved



PO for M1 - will not allow
PO for M2 - should allow



But local is specific to that org not for others. so for M2 it should allowed.



Q. what is category level supplier?
The supplier will be ASL for all items which fall under that category.



Q. then where do we define that?
That is defined in ASL



Q. Where do we define the range of items under that category?
Linked while defining Items



Q. AND is approved for Item not for category so Item shoud be presidance. and it should allowed. Item shoud have more presidance then category.
NO. Category is having higher precedence. So, if a Supplier is debarred for a category then he is debarred for all items under that category.



Q. Can we create Req to Release Item if is not matching on Requisition and BPA. but ASL is set.
Question is not clear



Q. what if allocation for ASL is <100 %
For manual PR, 100% allocation does not matter. 100% allocation is required only for planned orders. For manual PR the PO will be raised against the supplier with highest allocation.



Q. What does Sourcing Assignment signify?
Sourcing Assignment will determine which supplier to source an item for a given ORG



Q. If we have one org then can we do transaction in Master Org
Yes. No restriction. Best practice that we should not do any transaction in Master Inv Org.





Q. What isTCA??
Trading Community Architecture. Covered in AR. This is party, customer relationship and stored in HZ tables.



Q. How intercompany sourcing happens and how PO will be managed in two companies?
Internal Requisition/Internal Sales Orders



Q. What is the need to add the price into price list...when we have given price for the item in Inventory setup?
Price that we mentioned is the Purchase list price. But for OM, it picks the price from Price list.



Q. While giving the value in Price list...do we add our all the cost and profit in that
For internal items, the transfer price is calculated based on the cost + the transfer options.



Q. What does create internal order conc request do?
Create internal order request will transfer the IR info to OM interface tables.



Q. Why is the cost zero for Internal Orders?
We did not enter any cost for the item, so the cost was zero.



Q. could you please show this cost thing by raising external sales order?
Out of Scope. OM class is required.


Q. Cost is zero for internal sales order, however you told that it will be 10% addition of the cost of that item then why it is not 10 % addition in the cost?

Q. Why is the cost of the Item zero?
The cost of the Item was zero because we did not enter the cost of the item.



Q. Where do we enter the cost?
We enter the cost while defining the Item.



Q. How does the price default while creating IR?
The price of the item while creating IR, is the cost of the Item.



Q. Can you clarify where we set up zero cost for an Item for IR?
We did not specify the zero for IR. If we do not enter the cost, it will be considered as zero.



Q. Can we change PO Currency?
Yes. We can change PO currency. It defaults from supplier setup but we can change it.


Q. Can you explain what is confirming order ?
Sometimes, the Purchase manager requests for certain items over phone. Then he sends the PO just to confirm the verbal order. If the vendor thinks that this is a duplicate order and delivers double, it could cause problem, so he sends a PO with a message that it is confirming order.


What is Difference Between Blanket release and Planned Release?
Blanket release is against blanket purchase agreement and planned release is against planned orders.

Under what Circumstance we'll use Blanket and Planned Agreement
In blanket, the price of the agreement is fixed but the quantity is not fixed. We get price breaks in Blanket for volume discounts.
In planned, the shipment is tentative. It does not have any price breaks so no volume discounts.

So, blanket is for high volume transactions.

Q. For planned orders, Schedule is available but blanket is for as and when required?
The schedule in planned Orders is just tentative. The actual schedule is determined later.
Blanket can have an effective date range. Planned does not have any effective date range.

Q. Can you cover Advance Pricing?
Not in scope.

Q. What is the way for adding items or modify BPA from supplier point of view..are they need have oracle apps installed with Oracle iSupplier portal?
They will have access to Oracle iSupplier portal which is a web based application. No installation is required.


Usage of :$FLEX$ in oracle apps



$FLEX$ is a Special Variables in oracle Apps environment and is used in Value sets to hold values at runtime. It is Used for basing the value of a parameter and generate a LOV on another parameter. This variable is used in the Parameter form of a Concurrent Program and its defined in the Where Clause window in the value set ofTable type.


Syntax: 
:$FLEX$.previous_value_set_name
Important:
  • $FLEX$ must always be in capitals.
  • A ‘:’ must precede the declaration of $FLEX$.
  • The previous value set name must have already been assigned and saved on a different parameter.
This is explained with an example:
    1.    Create a concurrent program with 2 parameters. Namely “Run_type” and “Run_value”

    2.    The 2 parameters have 2 different value sets.
a.    Value set of Run_type parameter is independent value set


b.    Value set of Run_value parameter is table value set

    3.    Now you can see at run time, the LOV generated in Run_value parameter is dependent on Run_type.



     Say we have 3 Params in a concurrent program
     Param1 – valueset: run_type
     Param2 – valueset: run_type
     Param3 – valueset: run_value
    Now Param1 and Param2 has the same valueset. And Param3’s valueset is calculated based on Run_type
    Which Param value will be used in calculating the valueset of Param3? The answer is Param2. Even though param1 and param2 has the same valueset, $FLEX$ uses the value in the valueset which is just before it ie., param2
   See the example:


Oracle Apps Shortcut Keys

XDOLoader Utility to upload XML Templates

The XDOLoader utility is a Java-based command line program to load template (RTF, PDF, and XSL-FO), XML, and XSD files to the XML Publisher database tables. Before uploading any xml template you need to upload your concurrent program (if any) and data definitions through the FNDLOAD utility.

General Syntax to execute the XDOLoader utility as follows:

% java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
-DB_USERNAME \
-DB_PASSWORD \
-JDBC_CONNECTION \
-LOB_TYPE \
-APPS_SHORT_NAME \
-LOB_CODE \
-LANGUAGE \
-TERRITORY \
-XDO_FILE_TYPE \
-NLS_LANG \
-FILE_CONTENT_TYPE \
-FILE_NAME \
-OWNER \
-CUSTOM_MODE [FORCE|NOFORCE] \
-LOG_FILE

The parameters are described below:
  • UPLOAD (Mandatory): The first parameter: UPLOAD will be implemented in the feature.
  • DB_USERNAME (Mandatory): Database user name (example: apps).
  • DB_PASSWORD (Mandatory): Database user password (example: manager).
  • JDBC_CONNECTION (Mandatory): JDBC database connection string
    (example: ap000sun:1234:ebs).
  • LOB_TYPE (Mandatory): XDO LOB type. Valid values are: ‘TEMPLATE’, ‘XML_SCHEMA’ and ‘XML_SAMPLE’
  • APPS_SHORT_NAME (Mandatory): Application short name (example: AR).
  • LOB_CODE (Mandatory): XDO LOB code. Enter either the Template Code or the Data Definition Code.
  • LANGUAGE (Optional): ISO two-letter language code (example: en).
  • TERRITORY (Optional): ISO two-letter territory code (example: US), default is ’00’.
  • XDO_FILE_TYPE (Mandatory): Enter the XDO file type, valid values are: PDF, RTF, XLS, XSL-FO, XSL-HTML, XSL-XML, XSLTEXT, XSD, XML, RTF-ETEXT
  • NLS_LANG (Optional): Enter the NLS_LANG environment variable.
  • FILE_CONTENT_TYPE (Optional): Content type of the file (example: text/html, application/pdf)
  • FILE_NAME (Mandatory): Name of the file you want to upload. You can give full path name of the file.
  • OWNER (Optional): Owner of the template. Default is “ORACLE”.
  • CUSTOM_MODE (Optional): Whether to force update. Valid values are FORCE and NOFORCE (default).
  • LOG_FILE (Optional): Enter a file name for the output log file (default: xdotmpl.log).

Sample script to run the XDOLoader utility to upload a xml template:

echo  "----------------------------------------------------------------------"
echo "Please enter the following details needed for the XML XDOLoad process"
echo "This details can be found in the TNS entry"
echo "----------------------------------------------------------------------"

echo "Enter Apps password =>"
read apps_pw

echo "Enter Host TNS Database Server name =>"
read v_host

echo "Enter Host Database TNS Port Number =>"
read v_port

echo "Enter Host Database SID name =>"
read v_sid

echo "Uploading rtf(XXFIN_SAMPLE_TEMPLATE.rtf) for the XML Template Definition"

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD $apps_pw \
-JDBC_CONNECTION $v_host:$v_port:$v_sid \
-LOB_TYPE TEMPLATE \
-APPS_SHORT_NAME "IBY" \
-LOB_CODE "XXFIN_TEMPLATE_CODE" \
-LANGUAGE en \
-XDO_FILE_TYPE RTF-ETEXT \
-FILE_CONTENT_TYPE 'text/html' \
-FILE_NAME "$XXFIN_TOP/templates/XXFIN_SAMPLE_TEMPLATE.rtf" \
-CUSTOM_MODE FORCE

echo "Finish uploading rtf(XXFIN_SAMPLE_TEMPLATE.rtf) for the XML Template Definition"

You can run the below query to see the details of your uploaded Template in the database.

SELECT * FROM XDO_LOBS ORDER BY LAST_UPDATE_DATE DESC;


Folder Options in Custom Forms

Folder option is one of the good concepts available to reduce the Future customization in many custom Forms.Here i am putting basic functionality of
the folders and will try to put the simple steps to do them.
These thing are available in the metalink with a little search but i putting for ready reference...for technical people

Functions of the Folder

Folders provide the following functions to the user:

• Customizable display of retrieved data, including:

Columns to be displayed
Width of columns to be displayed
Sequence of columns
Prompts associated with columns
Record ordering (with some exceptions)

• Persistent storage of folder definitions
• Automatic rerun of a previously defined query
• Public and private access to persistent configurations
• Default configuration specification

In addition, system administrators can prevent users from creating or modifying folders by setting the profile option FOLDERS: ALLOW_CUSTOMIZATION to ’N’. This setting disables all folder functions (except opening a predefined folder) for individual users.

Actions in Folder Forms

Folder form allows the user to perform the following functions:

• New: Creates a new folder. The user must enter a new, unique (per entity and user) folder name. This function is not available at enter query mode.

• Open: Loads a previously defined folder. A user can select from a list of his own folders, and any public folders, for the current entity.

• Save/Save As: Saves the current folder. If it has never been saved then revert to ‘Save As’ functionality.

• Delete: Allows a user to delete any folder that they created for the current entity. If another user is referencing the folder as their default, that reference is deleted as well.

• Show Field: Opens an LOV displaying fields that can be shown and are not currently shown. Selecting a value adds the field after the current cursor position.

• Hide Field: Hides the current field. The cursor moves to the field sequenced after the field that was just hidden. By default, required fields cannot be hidden.

• Move Right: Swaps the current field with the one to its right.

• Move Left: Swaps the current field with the one to its left.

• Widen: Increases the width of the current field, up to a maximum size of 20 inches, in .2 inch increments.

• Shrink: Decreases the width of the current field, to a minimum size of 0.3 inches, in .2 inch increments.

• Auto Size: Sizes each stacked–canvas field based on a small sample of records within the block. Uses the prompt width to establish a minimum width. Lists are sized based on the elements they contain, and check boxes are sized based on the prompt only. This function is not available in ‘Enter Query’ mode.

• Show Order By: Allows the user to toggle the order by buttons on and off. When they are shown, the user can set the ordering of first three base-table items currently shown. Each column can be set to Ascending, Descending, or Exclude. The three settings are applied left–to–right when data is fetched and sorted.

• View Query: Allows the user to view the WHERE clause of the folder.

• Reset Query: Clears the current WHERE clause. Similar to New, except that the current folder name and all of its definitions are retained.

Following are the feature which developer can customize:

• Developers can disable all folder functions independently. For instance, a developer could create a folder that prevents the user from moving fields, setting the ordering, and specifying that a folder can autoquery upon being opened.

• Developers can use the folder technology to lay out a block, but not expose any folder functions to the user.

• Developers can prevent users from hiding specific fields. This allows including mandatory fields in enterable blocks (mandatory fields should always appear).

• Most of the folder form functionality customization is done in user defined trigger ‘Folder Return Action‘ which comes with folder form template.

Behavior of a Folder Block

• Instead of boilerplate text, the prompts are actually display items in a single row block. The prompts are all painted on a single line.

• The initial settings of the prompts drive the layout of the Default Screen, including item width and tabbing sequence.

• Some field properties, including widths, X–positions, and prompts are set at runtime. Any values that remain static while the form is run are the responsibility of the developer. These include visual attributes, Y–positions, etc.

• The descriptive flexfield must exist on the stacked canvas, rather than the content or fixed canvas where it is always visible, so that the user can resize, move, and label it as needed.

Descriptive Flex Fields in Custom Forms

Basic steps for implementing descriptive flex in our custom forms..These steps you will find in the application develeoper as well as in may sites on the net..
i am putting the content from one of the documents i have for ready reference and single source place..
If you are creating a DFF on the custom form for already defined DFF jump to step 4..
Step1: Registering your table(Required in case of the DFF on a custom table)
Use the add_dd package to register the table and the corresponding columns

For sample....

EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'CHN_REP_CODE', 1, 'VARCHAR2', 10, 'N', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'NAME', 2, 'VARCHAR2', 30, 'N', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'LOCATION', 3, 'VARCHAR2', 10, 'Y', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'REGION', 4, 'VARCHAR2', 5, 'Y', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'PRIORITY', 5, 'VARCHAR2', 1, 'Y', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'STATUS', 6, 'VARCHAR2', 1, 'Y', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'EFFECTIVE_DATE', 7, 'DATE', 10, 'Y', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'PARENT_CODE', 8, 'VARCHAR2', 10, 'Y', 'N');EXECUTE ad_dd.register_column('CUSTOM', 'C01_CHN_REP','last_update_date', 9, 'DATE', 10, 'N'



Step2:Check whether all the columns are registered or not...

use the sysadmin responsibility and see the table details..



Step3:Register the Descriptive Flex field..






Step4:Create a new item and attach the TEXT_ITEM_DESC_FLEX subclass information and set the following properties
Subclass ='Text_Item_Desc_Flex'
Database Items = 'No'
Query Allowed 'Yes'
Insert Allowed 'YES'
Update Allowed 'YES'
List of Values = 'Enable_List_Lamp'
Validate from list 'No'


Form Builder Flexfield a control 1 character size is created on the canvas. Block is correct C01_CHN_REP_BLK

Step5:use a event hanlder to make the code as per oracle standards..
2. Create a Procedure as event handler for the field. This will be used in WHEN-NEW-FORM-INSTANCE
to initialize the flexfield.

PROCEDURE c01_desc_flex( EVENT varchar2) IS
BEGIN
if ( event = 'WHEN-NEW-FORM-INSTANCE') then
FND_DESCR_FLEX.DEFINE( BLOCK=>'C01_CHN_REP_BLK',
FIELD=>'DESC_FLEX',
APPL_SHORT_NAME=>'CUSTOM',
DESC_FLEX_NAME=>'C01_CHN_Descr_flex') ;
else
null ;
end if ;
END;

3. Call the procedure c01_desc_flex , in WHEN-NEW-FORM-INSTANCE
C01_DESC_FLEX(‘WHEN-NEW-FORM-INSTANCE’);

4. Oracle apps provide api FND_FLEX for Descr flex field events. You call it from all events that can edit the DESC_FLEX field.
It is better to write all these block level triggers to have consistent normal behaviour of the descriptive flex field.
FND_FLEX.event('WHEN-NEW-ITEM-INSTANCE') ;
from the WHEN-VALIDATE-ITEM trigger of the DESC_FLEX item as
FND_FLEX.EVENT('WHEN-VALIDATE-ITEM ');
FND_FLEX.EVENT(’PRE-QUERY’);
FND_FLEX.EVENT(’POST-QUERY’);
FND_FLEX.EVENT('WHEN-VALIDATE-RECORD');
FND_FLEX.EVENT('WHEN-NEW-ITEM-INSTANCE');
FND_FLEX.EVENT('PRE-INSERT');
FND_FLEX.EVENT('WHEN-VALIDATE-ITEM');
FND_FLEX.EVENT('PRE-UPDATE);

Saturday, February 16, 2008

Folder Options in Forms -Part2

Steps to create Folder Form

Step – 1 Create Folder Objects

• Reference Folder Object

Every folder form must contain the folder objects (such as the windows that are used for saving folders and changing prompts) included in the STANDARD_FOLDER object group. If this object group does not already exist in your form, reference it from APPSTAND. $AU_TOP/au/11.5.0/forms/US/APPSTAND.fmb

• Attached the Folder library

Every folder form must have the library APPFLDR.pll attached. The library file is $APPL_TOP/au/11.5.0/resource/APPFLDR.pll.

• Create the Content/Fixed Canvas

Create a content canvas, as you would for any window. Assign it the property class CANVAS, and assign it to the appropriate window. This canvas will hold the block scroll bar, the record indicator, and the folder_open and folder_title objects. This canvas will also hold any fields that would not be part of the folder block, such as required primary key fields.

Note: The name of the content canvas cannot be a substring of the name of the stacked canvas, as this will cause errors. For example, if the content canvas is called ORDERS, then the stacked canvas cannot be called ORDERS_FOLDER, but ORDERS_MAIN and ORDERS_FOLDER would be acceptable.

• Create the Stack Canvas

Create a stacked canvas; it holds prompt fields and displayed fields of the folder block, as well as the ORDER_BY# buttons.

Determine the appropriate Y position for the top of the folder block (this corresponds to the Y position for the top of the Folder Open button and the Folder Title). This value is referred to as Y_OFFSET, and is used to calculate the correct positions for items and canvasses. Typically Y_OFFSET should be .25 inches (that is, the top of the Folder Open button should be one character down from the top of the window) if the folder is the first block in the window.

Set the following properties for the stacked canvas:

Sequence : After the content canvas.

Property Class : CANVAS_STACKED
Displayed :True for the stacked canvas that will be shown
immediately upon entering the block; False for any
others.

Window : Same as for the content canvas.

View Height :.5” + (.25” * number of rows to be displayed)

Display X Pos : .5” (May be adjusted later)

Display Y pos : Y offset + .25

View Horiz
Scroll Bar : True


Step – 2 Create Folder block item
• Folder Block Item

Create a block to hold the fields of the folder, as you would any other block. The block scroll bar must be on the content canvas.

Suggestion: Although the scroll bar must be on the content canvas, most of the fields belong on the stacked canvas. If you are using the default block functionality, this can be achieved most easily by specifying the stacked canvas when creating the default block, and then changing the Scroll Bar Canvas property after the block has been created. One or more fields may be on the content canvas. These fields must always be the leftmost fields in the block, and will not respond to folder events (e.g. Move Right, Widen, Hide). Identify which, if any, fields will be on the content canvas, and move them there. Sequence these fields before any of the fields that will be on stacked canvasses.

Adjust the Display X Position for the stacked canvas view so that it is .1” to the right of the fields on the content canvas. For each item to be displayed as part of the folder, set the properties as follows:

TYPE: Text Item, Check box or List only.

X Position: Positioned at runtime

Y Position: If on content canvas, Y_OFFSET + .5”; otherwise, .25”

Width: Set at runtime

Canvas: The content or stacked canvas

Displayed: If part of the default display, True; otherwise False. Fields on the content canvas must be Displayed.

Note: The position and width of any field on the content canvas must be set accurately in the Designer. Also, the width of a check box should be set accurately to 0.3”. Check boxes with width 0.2” are acceptable, but do not save space since APPFLDR always allocates at least 0.3” for a check box.

• Folder Switcher Item

Create a switcher field for the block. In a combination block, this should be called ’SWITCHER’, and in any other folder block, it should be called ’FOLDER_SWITCHER’. It must be sequenced first in the block, and must use the SWITCHER property class. Place it on the content/fixed canvas. This field must not have a corresponding field in the prompt block. Attached the following trigger to the switcher item:-

Trigger: WHEN–NEW–ITEM–INSTANCE (Execution Style: Override)
On field SWITCHER:

app_folder_move_cursor(’1’);

• Create Current Record Indicator/Drilldown Record Indicator

Create a record indicator for the block. It must be called either CURRENT_RECORD_INDICATOR or DRILLDOWN_RECORD_INDICATOR. Do not include a corresponding field in the prompt block. The folder code disables certain functions automatically, but it is developer responsibility to write the code to manage the indicator. The WHEN–NEW–ITEM–INSTANCE trigger for CURRENT_RECORD_INDICATOR must call
app_folder_move_cursor(’1’).

Step – 3 Create Block Level Trigger
• Trigger: WHEN–NEW–BLOCK–INSTANCE (Execution Style: Before)

app_folder.event(’WHEN–NEW–BLOCK–INSTANCE’);

This trigger instantiates the folder block(if necessary), attempting to load the user’s default if on exists.

• Trigger: KEY-NEXT-ITEM

app_folder.event (’KEY–NEXT–ITEM’);

This trigger navigates to the next folder–sequenced item. Moves to next record if needed. Fire in Enter Query mode must be TRUE.

• Trigger: KEY-PREV-ITEM

app_folder.event(’KEY–PREV–ITEM’);

This trigger navigates to the prior folder–sequenced item. Moves to
prior record if needed. Fire in Enter Query mode must be TRUE.

• Trigger: PRE–BLOCK (Execution Style: Before)

app_folder.event(’PRE–BLOCK’);

This trigger establishes values for the folder block (when more than one is defined). It re-enables the folder menu upon entering the block.

• Trigger: POST–BLOCK (Execution Style: Before)

app_folder.event(’POST–BLOCK’);

This call disables the folder menu.

• Trigger: PRE–QUERY (Execution Style: Before)

app_folder.event(’PRE–QUERY’);

This call builds the order by clause for the query.

• Trigger: POS–QUERY (Execution Style: Before)

app_folder.event(’POST–QUERY’);

If the folder block is the master of a master–detail relationship, this trigger is required. It extracts the WHERE clause from SYSTEM.LAST_QUERY. Without this trigger, the detail block’s query can be extracted instead, which can lead to saving an invalid query with the folder.

• Trigger: User–named trigger FOLDER_RETURN_ACTION

<>

This trigger is fired each time the folder does certain operations that you may need to further process. ’global.folder_action’ contains the name of the process, and ’global.folder_field’ holds the name of the relevant field, if any, stripped of the block name. Specific callbacks may populate other global variables. The FOLDER_RETURN_ACTION trigger is optional. You should code it if you need to perform logic on any of the supported events.

Example:-
Following is code written in FOLDER_RETURN_ACTION trigger to allow update for ‘COL1’, ‘COL2’ and ‘COL3’ in the folder block:-

DECLARE
field_name VARCHAR2(30);
BEGIN
IF (:global.folder_action = ’SHOW–FIELD’) THEN
field_name:= NAME_IN(’global.folder_field’);
IF (field_name IN (’COL1’, ’COL2’, ’COL3’)) THEN
app_item_property.set_property (
’’||’.’||field_name,
ALTERABLE,
PROPERTY_ON);
END IF;
END IF;
END;

Step – 4 Create Prompt Block
Create a one record block to hold the fields that act as prompts for the folder block. For every item in the folder block that may be displayed, the prompt block must contain an item of the same name (except for the record indicator and the switcher). The initial settings of the prompts drive the layout of the Default Screen, including item width and tabbing sequence.

Typically one would name the prompt block _PROMPT, for example, ORDERS_PROMPT.

Each ’prompt’ field must have the following characteristics:

Property Class: DYNAMIC_PROMPT
Sequence: Must match the name of an item in the folder block. Sequence the first
set of fields to be displayed properly, starting at 1. This drives the
folder TAB order.
X Position: Positioned at runtime.
Y Position: .05.

Width: Dictates the width of the corresponding field. Make sure the width is adequate to display the text for the prompt fully. The width only needs to
account for English text – no translation expansion space is needed. Specifying the width as 0.1” causes it to adjust automatically based on the text it contains. For checkboxes, specify a width of 0.1”

Alignment: Usually Start, but follows alignment of data in corresponding folder
block field. Prompts for check boxes are always Center aligned.

Canvas: Stacked canvas.

Default: The text for the prompt. The translation tool translates this
automatically.

Displayed: True only for those fields you want displayed on the default screen;
otherwise, False.


Create Special Fields in Prompt Block

The following special fields must exist in the prompt block:-

Field: FOLDER_OPEN
PROPERTY CLASS: FOLDER_OPEN
X POSITION: 0.1”
Y POSITION: Y_OFFSET
CANVAS: content canvas

Field: FOLDER_TITLE
PROPERTY CLASS: DYNAMIC_TITLE
X POSITION: 0.4”
WIDTH: 4”
Y POSITION: Y_OFFSET +.05
CANVAS: content canvas

Field: FOLDER_DUMMY
PROPERTY CLASS: FOLDER_DUMMY
CANVAS: TOOLBAR

Field: ORDER_BY1,ORDER_BY2,ORDER_BY3
PROPERTY CLASS: FOLDER_ORDERBY
X POSITION: Set at runtime.
Y POSITION: For content canvas Y_OFFSET+.5+(.25*Rows)
For stack canvas .25+(.25*Rows)
CANVAS: If it’s a fixed field specify content canvas otherwise specify stack
canvas.

Step – 5 Code Form Level Trigger
The following form–level triggers must exist in order for a folder block to operate correctly:

• Trigger: WHEN–WINDOW–RESIZED:

IF :system.event_window IN (’<>’) THEN
app_folder.event(’WHEN–WINDOW–RESIZED’);
END IF;


• Trigger: KEY–CLRFRM:

app_folder.event(’KEY–CLRFRM’);

This call must be made anywhere a clear_form is issued. This cause the folder code to repaint the prompts and folder titles as if they were normal boilerplate.

• Trigger: WHEN–NEW–FORM–INSTANCE:

app_folder.define_folder_block('OBJECT NAME',
’’,
’’,
’’
’’,
[’’]);

Note: All parameters must be passed in UPPERCASE.

Example:
App_flolder.define_folder_block(‘PO_HEADER’
,’POHEADER’
,’POHEADER_PROMPT’
,’POHEADER_STK’
,POHEADER_MAIN’)

Choose the object name carefully; it must be unique across all products. Prefix it with the application short name, for example ’FND_ALERTS’. This object name identifies all folder definitions that users define and save for your folder, and it appears in the Administer Folders form as the Folder Set, so you should make the object name descriptive and readable.

’DISABLED FUNCTIONS’ can be a string combining any of the following. Specifying that a function is disabled prevents the user from performing it.

• OPEN
• TOOLS: Disables everything except OPEN. Note that if the profile FOLDERS:ALLOW_CUSTOMIZATION is ’N’, all tools are automatically disabled except Open.

• ORDERBY
• NEW
• SAVE: Disables Save and Save As.

• AUTOQUERY: Should be disabled for any detail block.

• PUBLIC
• DEFAULT
• DELETE
• SHOW
• HIDE
• MOVE
• WIDEN
• SHRINK
• AUTOSIZE
• PROMPT
• QUERY

Following example will disable ‘ORDER BY’ and ‘HIDE’ function in the folder form:

App_flolder.define_folder_block(‘PO_HEADER’
,’POHEADER’
,’POHEADER_PROMPT’
,’POHEADER_STK’
, POHEADER_MAIN’
,’ORDERBY HIDE’);

• User-named Trigger: FOLDER_ACTION

app_folder.event(:global.folder_action);

Do not insert additional code in this trigger. It is used to allow the menu and folder toolbar to communicate with the folder library that is attached to the form.

• User-named Trigger: FOLDER_RETURN_ACTION
NULL;

This trigger must exist at form–level to handle the case in which it has not been coded at block–level.



The FOLDER_RETURN_ACTION trigger supports the following events:

• SHOW_FIELD: The field in :global.folder_field has just been made visible. In updateable blocks, you may need to set some properties (such as Update Allowed, Required, etc.). Fields can become visible in Enter Query mode or normal mode. When a folder is opened, all fields currently displayed are hidden, even if they are in the new folder; thus this call is made for each field as it is redisplayed.

• ADD_FILED: Populates :global.folder_field. Called at the end of a user–initiated ’Show Field’ event that results in a field being displayed. The ’SHOW–FIELD’ callback is also triggered before this callback.

• HIDE_FIELD: Populates :global.folder_field. Called at the end of a user–initiated ’Hide Field’ event that results in a field being hidden.

• OPEN_FOLDER: Called at the end of an ’Open Folder’ event that results in a folder being loaded, but before the folder autoqueries. Populates global.folder_id.

• SET-WHERE-CLAUSE: Called during a ’Save Folder’ event, before setting the where clause that will be saved with the folder. Sets global.folder_action_allowed ’TRUE’. If the value of :global.folder_action_allowed is changed to anything but ‘TRUE’ by the FOLDER_RETURN_ACTION trigger, the WHERE clause saved with the folder is null.

• RESET-WHERE-CLAUSE: Called when the block WHERE clause is set back to the developer’s WHERE clause, because the user has selected Folder–>Reset query from the menu, or because the user has selected Folder–>New from the menu.

• SAVE-FOLDER: Called at the end of a ’Save Folder’ event, immediately before the Commit. Populates :global.folder_id.

• DELETE-FOLDER: Called during a ’Delete Folder’ event, immediately before the Commit. Populates :global.folder_id.

• CONFIRM-HIDE-FIELD: Called during a user–initiated ’Hide Field’ event, immediately after doing all other checks that the field can be cut (for example, it is not on the content or fixed canvas, it is not the only field on the stacked canvas, etc.). The variable ’global.folder_action_allowed’ is seeded with ’TRUE’. If this variable is changed to anything but ’TRUE’, the action is aborted and the user cannot hide the field. By default, if the user attempts to hide a required field, CONFIRM–HIDE-FIELD automatically prevents it, and users are allowed to hide non–required fields. You can override this behavior by changing the value of :global.folder_action_allowed to either ’TRUE’ (allow hiding) or ’FALSE’ (prevent hiding non–required fields) in the CONFIRM–HIDE–FIELD callback to the FOLDER_RETURN_ACTION trigger.

• AUTOQUERY: Called when a folder loads and automatically executes a query.

• CONFIRM-AUTOQUERY: Called when a folder that is designed to autoquery opens. ’GLOBAL.FOLDER_ACTION_ALLOWED’ is seeded with ’TRUE’. If this variable is changed to anything but ’TRUE’, the folder does not autoquery.

Example: Prevent Hiding a Field
Prevent the user from hiding the (non–required) ’PERIOD_NAME’ field:

Trigger: FOLDER_RETURN_ACTION (block–level):
IF(:global.folder_action = ’CONFIRM–HIDE–FIELD’) THEN
IF :global.folder_field = ’PERIOD_NAME’ THEN
:global.folder_action_allowed := ’FALSE’;
END IF;
END IF;

Attachment Functionality in Oracle Applications

why do i need a attachment functionality?
The attachments feature enables users to link unstructured data, such as images, wordprocessing documents, spreadsheets, or text to their application data. For example,users can link images to items or video to operations as operation instructions.
Can i enable it to any form i want?
For most of the standar you can do that..i hope custom form done in standard way can also have this feauture enabled
where should i start???
Lets Define the terms first
1.Entity:An entity is an object within Oracle Applications data, such as an item, an order, or anorder line. The attachments feature must be enabled for an entity before users can link attachments to the entity.
2.Category:A document category is a label that users apply to individual attachments anddocuments. Document categories provide security by restricting the documents that canbe viewed or added via a specific form or form function.
In the below example i am tryign to enable the attachment funcionality for the USer form..I want to upload the approval copy for creating a user or attaching new responsibility
what are the steps to do that??
Step1:First Define a Entity for which you are creating the attachments.In my case it is users.
define the base table and entityid and others things


Step2:Define a category(optional).if the attachments belongs logically to different group.
In this example i am addding to the miscellenous category.

Make the assignments of the Function once you define it.
step3:Find the Form Name,Function Name and block name to which the attachment feauture need to be enabled.
Step4:Define the Attachment Function .Seelct the type either you want the attachmetn feature at form level or Function level


Press the Blocks Button. and Enter the Block name you want to enable the attachment feauture.

Press the Entities Button

Enter the entity and other settings and see what fuctionalties are required for this form.like query,insert,update and delete allowed can be done or not from this form
Enter the primary Key so that it show the document based on the primary key.

If it a common document like across users then primary key should not be given.

Press the categories button to select the caegories

Press the Assignments Button in document categories and attach the Function.

Now the attache button is enabled..press the button and upload the document..




uploading document is self explainable
for developing some complex one..read tthe application developer for more info..
more or less it depends on the entity definition and the primary key for the document..
you can have more than one primary key...just play aroud with the primary key..you will be able to get any requirement...