Suppose you want to email output of a concurrent job to a group of
people then you may define a printer that executes a shell script and
than shell script can do all sort of things including sending email to
mailing list. More details can be found at metalink note 164085.1
Thursday 19 July 2012
List of Scheduled Concurrent Programs
select r.request_id,
p.user_concurrent_program_name ||
case
when p.user_concurrent_program_name = 'Report Set' then
(select ' - ' || s.user_request_set_name
from apps.fnd_request_sets_tl s
where s.application_id = r.argument1
and s.request_set_id = r.argument2
and language = 'US'
)
when p.user_concurrent_program_name = 'Check Periodic Alert' then
(select ' - ' || a.alert_name
from apps.alr_alerts a
where a.application_id = r.argument1
and a.alert_id = r.argument2
and language = 'US'
)
end concurrent_program_name,
decode(c.class_type,
'P', 'Periodic',
'S', 'On Specific Days',
'X', 'Advanced',
c.class_type
) schedule_type,
case
when c.class_type = 'P' then
'Repeat every ' ||
substr(c.class_info, 1, instr(c.class_info, ':') - 1) ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
when c.class_type = 'S' then
nvl2(dates.dates, 'Dates: ' || dates.dates || '. ', null) ||
decode(substr(c.class_info, 32, 1), '1', 'Last day of month ') ||
decode(sign(to_number(substr(c.class_info, 33))),
'1', 'Days of week: ' ||
decode(substr(c.class_info, 33, 1), '1', 'Su ') ||
decode(substr(c.class_info, 34, 1), '1', 'Mo ') ||
decode(substr(c.class_info, 35, 1), '1', 'Tu ') ||
decode(substr(c.class_info, 36, 1), '1', 'We ') ||
decode(substr(c.class_info, 37, 1), '1', 'Th ') ||
decode(substr(c.class_info, 38, 1), '1', 'Fr ') ||
decode(substr(c.class_info, 39, 1), '1', 'Sa '))
end schedule,
r.requested_start_date next_run,
case
when p.user_concurrent_program_name != 'Report Set' and
p.user_concurrent_program_name != 'Check Periodic Alert' then
r.argument_text
end argument_text,
r.hold_flag on_hold,
c.date1 start_date,
c.date2 end_date,
c.class_info, user_name
from apps.fnd_concurrent_requests r,
applsys.fnd_conc_release_classes c,
apps.fnd_concurrent_programs_tl p,
apps.fnd_user usr,
(SELECT release_class_id,
substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates ,a
FROM (select release_class_id,
rank() over(partition by release_class_id order by s) a,
s
from (select c.class_info,
l,
c.release_class_id,
decode(substr(c.class_info, l, 1), '1', to_char(l)) s
from (select level l
from dual
connect by level <= 31),
apps.fnd_conc_release_classes c
where c.class_type = 'S')
where s is not null)
CONNECT BY PRIOR
(a || release_class_id) = (a - 1) || release_class_id
group by release_class_id,a) dates
where r.phase_code = 'P'
and c.application_id = r.release_class_app_id
and c.release_class_id = r.release_class_id
and nvl(c.date2, sysdate + 1) > sysdate
and c.class_type is not null
and p.concurrent_program_id = r.concurrent_program_id
and p.application_id = r.program_application_id
and p.language = 'US'
and dates.release_class_id(+) = r.release_class_id
and usr.user_id = requested_by
order by requested_by,on_hold, next_run;
Patch LeveL of Various Application Modules
SELECT i.instance_name
, i.host_name
, i.version db_version
, f.release_name release
, fa.Application_name
, DECODE ( fpi.status, 'I', 'INSTALLED'
, 'S', 'SHARED'
, 'N', 'NOTINSTALLED'
, fpi.status
) status
, fpi.patch_level
FROM v$instance i
, fnd_product_groups f
, applsys.fnd_product_installations fpi
, apps.fnd_application_tl fa
WHERE UPPER(SUBSTR(i.instance_name,1,3)) = UPPER(SUBSTR(f.applications_system_name,1,3))
AND fa.application_id = fpi.application_id;
Example of Creating Customers using Customer Interface (RACUST)
This should work in Oracle Application Release 11.5.9 to Rel 12.
All Records are linked using ORIG_SYSTEM....REF Columns
All Records are linked using ORIG_SYSTEM....REF Columns
-- -- Insert record into customer interface table -- INSERT into ar.RA_CUSTOMERS_INTERFACE_ALL ( ADDRESS1 , ORIG_SYSTEM_CUSTOMER_REF , ORIG_SYSTEM_ADDRESS_REF , SITE_USE_CODE , INSERT_UPDATE_FLAG , CUSTOMER_NAME , CUSTOMER_NUMBER , PERSON_FLAG , CUSTOMER_STATUS , PRIMARY_SITE_USE_FLAG , CITY , STATE , POSTAL_CODE , COUNTRY , COUNTY , JGZZ_FISCAL_CODE , ORG_ID , LAST_UPDATE_DATE , LAST_UPDATED_BY , CREATION_DATE , CREATED_BY ) VALUES ( '3250 Prem Nagar' -- ADDRESS1 , 'SKM_TEST_03' -- ORIG_SYSTEM_CUSTOMER_REF , 'SKM_TEST_03' -- ORIG_SYSTEM_ADDRESS_REF , 'BILL_TO' -- SITE_USE_CODE , 'I' -- INSERT_UPDATE_FLAG , 'WINNIE POOH' -- CUSTOMER_NAME , 'SKM-03' -- CUSTOMER_NUMBER , 'N' -- PERSON_FLAG , 'A' -- CUSTOMER_STATUS , 'Y' -- PRIMARY_SITE_USE_FLAG , 'SAVAGE' -- CITY , 'MN' -- STATE , 55400 -- POSTAL_CODE , 'US' -- COUNTRY , 'SCOTT' -- COUNTY , '21-3456789' -- JGZZ_FISCAL_CODE , 1 -- ORG_ID , SYSDATE -- LAST_UPDATE_DATE , 4670 -- LAST_UPDATED_BY , SYSDATE -- CREATION_DATE , 4670 -- CREATED_BY ) -- -- Insert records inot profile interface table -- INSERT into ar.ra_customer_profiles_int_all ( ORIG_SYSTEM_CUSTOMER_REF , INSERT_UPDATE_FLAG , ORG_ID , CREDIT_HOLD , CUSTOMER_PROFILE_CLASS_NAME , LAST_UPDATE_DATE , LAST_UPDATED_BY , CREATION_DATE , CREATED_BY ) VALUES ( 'SKM_TEST_03' -- ORIG_SYSTEM_CUSTOMER_REF , 'I' -- INSERT_UPDATE_FLAG , 1 -- ORG_ID , 'N' -- CREDIT_HOLD , 'DEFAULT' -- CUSTOMER_PROFILE_CLASS_NAME , SYSDATE -- LAST_UPDATE_DATE , 4670 -- LAST_UPDATED_BY , SYSDATE -- CREATION_DATE , 4670 -- CREATED_BY ) -- -- Insert record into telephone interface table -- INSERT INTO ar.ra_contact_phones_int_all ( ORIG_SYSTEM_CUSTOMER_REF , ORIG_SYSTEM_ADDRESS_REF , ORIG_SYSTEM_CONTACT_REF , ORIG_SYSTEM_TELEPHONE_REF , INSERT_UPDATE_FLAG , CONTACT_FIRST_NAME , CONTACT_LAST_NAME , TELEPHONE , TELEPHONE_AREA_CODE , TELEPHONE_TYPE , LAST_UPDATE_DATE , LAST_UPDATED_BY , CREATION_DATE , CREATED_BY , ORG_ID , CONTACT_POINT_TYPE ) VALUES ( 'SKM_TEST_03' --ORIG_SYSTEM_CUSTOMER_REF , 'SKM_TEST_03' --ORIG_SYSTEM_ADDRESS_REF , 'SKM_TEST_03a' --ORIG_SYSTEM_CONTACT_REF , 'SKM_TEST_03a' --ORIG_SYSTEM_TELEPHONE_REF , 'I' --INSERT_UPDATE_FLAG , 'LICKEY' --CONTACT_FIRST_NAME , 'LOOSE' --CONTACT_LAST_NAME , '123-4756' --TELEPHONE , '612' --TELEPHONE_AREA_CODE , 'GEN' --TELEPHONE_TYPE , SYSDATE --LAST_UPDATE_DATE , 4670 --LAST_UPDATED_BY , SYSDATE --CREATION_DATE , 4670 --CREATED_BY , 1 --ORG_ID , 'PHONE' --CONTACT_POINT_TYPE ) -- -- Insert record into telephone interface table. -- This will be second contact for the customer -- INSERT INTO ar.ra_contact_phones_int_all ( ORIG_SYSTEM_CUSTOMER_REF , ORIG_SYSTEM_ADDRESS_REF , ORIG_SYSTEM_CONTACT_REF , ORIG_SYSTEM_TELEPHONE_REF , INSERT_UPDATE_FLAG , CONTACT_FIRST_NAME , CONTACT_LAST_NAME , TELEPHONE , TELEPHONE_AREA_CODE , TELEPHONE_TYPE , LAST_UPDATE_DATE , LAST_UPDATED_BY , CREATION_DATE , CREATED_BY , ORG_ID , CONTACT_POINT_TYPE ) VALUES ( 'SKM_TEST_03' -- ORIG_SYSTEM_CUSTOMER_REF , 'SKM_TEST_03' -- ORIG_SYSTEM_ADDRESS_REF , 'SKM_TEST_03b' -- ORIG_SYSTEM_CONTACT_REF , 'SKM_TEST_03b' -- ORIG_SYSTEM_TELEPHONE_REF , 'I' -- INSERT_UPDATE_FLAG , 'LICKEY' -- CONTACT_FIRST_NAME , 'LOOSE TWO' -- CONTACT_LAST_NAME , '123-4756' -- TELEPHONE , '512' -- TELEPHONE_AREA_CODE , 'GEN' -- TELEPHONE_TYPE , SYSDATE -- LAST_UPDATE_DATE , 4670 -- LAST_UPDATED_BY , SYSDATE -- CREATION_DATE , 4670 -- CREATED_BY , 1 -- ORG_ID , 'PHONE' -- CONTACT_POINT_TYPE ) -- -- This wil add email for contact LICKEY LOSOE -- INSERT INTO ar.ra_contact_phones_int_all ( ORIG_SYSTEM_CUSTOMER_REF , ORIG_SYSTEM_ADDRESS_REF , ORIG_SYSTEM_CONTACT_REF , ORIG_SYSTEM_TELEPHONE_REF , INSERT_UPDATE_FLAG , CONTACT_FIRST_NAME , CONTACT_LAST_NAME , EMAIL_ADDRESS , LAST_UPDATE_DATE , LAST_UPDATED_BY , CREATION_DATE , CREATED_BY , ORG_ID , CONTACT_POINT_TYPE ) VALUES ( 'SKM_TEST_03' -- ORIG_SYSTEM_CUSTOMER_REF , 'SKM_TEST_03' -- ORIG_SYSTEM_ADDRESS_REF , 'SKM_TEST_03b' -- ORIG_SYSTEM_CONTACT_REF , 'SKM_TEST_03be' -- ORIG_SYSTEM_TELEPHONE_REF , 'I' -- INSERT_UPDATE_FLAG , 'LICKEY' -- CONTACT_FIRST_NAME , 'LOOSE TWO' -- CONTACT_LAST_NAME , 'a.b@c.com' -- EMAIL_ADDRESS , SYSDATE -- LAST_UPDATE_DATE , 4670 -- LAST_UPDATED_BY , SYSDATE -- CREATION_DATE , 4670 -- CREATED_BY , 1 -- ORG_ID , 'EMAIL' -- CONTACT_POINT_TYPE )
List Freight charge Details for a Given Order
SELECT ool.ordered_item
, opa.operand_per_pqty
, opa.adjusted_amount
, qlh.name
, qll.list_line_no
FROM oe_order_headers_all ooh
, oe_order_lines_all ool
, oe_price_adjustments opa
, qp_list_headers_vl qlh
, qp_list_lines qll
WHERE order_number = 10000
AND ool.header_id = ooh.header_id
AND opa.line_id = ool.line_id
AND opa.list_line_type_code = 'FREIGHT_CHARGE'
AND qlh.list_header_id = opa.list_header_id
AND qll.list_line_id = opa.list_line_id;Tuesday 17 July 2012
Descreptive Flex Field
Descriptive Flex Fields
Most of the Oracle application screens come with a facility to expand the space to capture custom fields. This facility of adding new custom fields (without any programming) is called DFF or Descriptive Flex Fields. The advantage of DFF is that around 15 new custom fields can be captured on each of the database table based block or form without doing any programming. The availability of facility of DFF on a screen is shown by square bracket [ ] and is shown below in figure 1.
For using a DFF facility on a standard screen, it has to be activated first. The activation is done by system administrator responsibility and it includes deciding number of fields to be used, window title of the fields, valid values of the fields, context etc. Once activated, the DFF field is immediately available for use. It can be used by clicking on the [ ] field which pops up a screen to capture the enabled fields.
DFF Features
DFF is directly mapped to a table and each segment of DFF is mapped to an individual field of the table. These fields are often named as ATTRIBUTE1, ATTRIBUTE2, …, ATTRIBUTE15 (most of the time, we have maximum of 15 attribute fields available in each standard table). In addition to these 15 attribute fields, there is also one field called ATTRIBUTE_CATEGORY. This field is called Context field or Structure Field. How the structure field is used is explained below:
Some time there is a requirement to capture context sensitive information in DFF fields. Suppose in HR form, we want to capture some custom information. If we are entering record for an Employee, we want to capture certain information; on the other hand, if we are entering record for an Applicant, then we want to capture some other custom information. This can be achieved by defining different contexts. In the example, just given, we can define two contexts, Employee and Applicant. Depending upon what context is chosen, we will show the corresponding fields to the users for entry.
Uses
DFF is a very powerful feature of oracle applications which provides lot of extensibility to an implementation. Since Oracle Applications is implemented across diverse businesses and each business may need different kind of information to be captured (in addition to the standard information capturing which oracle provides), DFF comes in very handy to capture the custom information. Fields captured by DFF are stored in the database and are automatically queried whenever corresponding record is queried on the screen.
Examples
- A business ABC wants to capture additional supplier attributes like supplier turnover, supplier’s rating etc. These fields are not captured by standard oracle application screen and can be captured by activating DFF on supplier screen.
- A leasing company wants to capture maximum mileage allowed and mileage penalty imposed for extra miles for the trucks it leases. These fields are not provided in the standard oracle applications and can be captured by activating DFF on lease authoring screen.
How to enable DFF
Enabling a DFF can be best illustrated by an example. We will take GL Daily Rate Screen (Navigation: GL Super User => Setup => Rates => Daily) for this discussion.
The screen is shown below. The screen has the DFF facility available but is not active currently.
We want to enable this DFF to capture two more fields:
Exchange Rate Source
Exchange Rate Time
- First step is to find out the corresponding table name for this screen. This can be found by querying the record and then going to Help => Diagnostics => Examine and checking the Last Query field in the System Block. This will provide us with the table name underlying the data. In this example, our table name is GL_DAILY_RATES.
- Now go to Application Developer Responsibility and Navigate to Flexfield => Descriptive => Register. Now do a query based on this table name (GL_DAILY_RATES).
We note down the Title (which is Daily Rates).
- Now, we know the title of the standard DFF available for the Daily Rate screen. This DFF needs to be now enabled. For doing this navigate to FlexField=>Descriptive=>Segments screen and do a query based on the DFF title (Daily Rates).
Global Data Elements context is always pre-populated. If we do not want any context, then we can just click on segments button and define the required segments. However, if we need to define different contexts, then those context can be entered here and the corresponding segments can be entered for each of chosen contexts.
- Click on the Segments button, and enter the values as shown below. We can associate value-set for each segment so that users can enter only valid values when entering a record.
- Save the changes and come back to the previous screen. Click on Freeze Flexfield definition check-box and save the changes.
- Now, we are done with activating the DFF. Come back to the Daily Rate Screen and see that DFF is enabled and can be used to capture two new fields as shown below:
Defining a new DFF
DFF is implemented using the Attribute column fields in a table. For example, let us consider definition of a table GL_DAILY_RATES.
SQL> desc gl_daily_rates
Name Null? Type
------------------------------- -------- ----
FROM_CURRENCY NOT NULL VARCHAR2(15)
TO_CURRENCY NOT NULL VARCHAR2(15)
CONVERSION_DATE NOT NULL DATE
CONVERSION_TYPE NOT NULL VARCHAR2(30)
CONVERSION_RATE NOT NULL NUMBER
STATUS_CODE NOT NULL VARCHAR2(1)
CREATION_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER(15)
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER(15)
LAST_UPDATE_LOGIN NOT NULL NUMBER(15)
CONTEXT VARCHAR2(150)
ATTRIBUTE1 VARCHAR2(150)
ATTRIBUTE2 VARCHAR2(150)
ATTRIBUTE3 VARCHAR2(150)
ATTRIBUTE4 VARCHAR2(150)
ATTRIBUTE5 VARCHAR2(150)
ATTRIBUTE6 VARCHAR2(150)
ATTRIBUTE7 VARCHAR2(150)
ATTRIBUTE8 VARCHAR2(150)
ATTRIBUTE9 VARCHAR2(150)
ATTRIBUTE10 VARCHAR2(150)
ATTRIBUTE11 VARCHAR2(150)
ATTRIBUTE12 VARCHAR2(150)
ATTRIBUTE13 VARCHAR2(150)
ATTRIBUTE14 VARCHAR2(150)
ATTRIBUTE15 VARCHAR2(150)
RATE_SOURCE_CODE VARCHAR2(15)
This table contains a field called CONTEXT and 15 Attribute columns. The Context field stores the structure or content of the DFF and attribute columns store the actual values of the custom fields.
When we create a DFF definition (as shown below), we choose a Structure Column (CONTEXT in the below example) and also choose a prompt for the Context.
The fields to be used in the DFF design can be associated by clicking on the Columns button and checking the enabled button as shown below:
There is also a button called Reference Fields, which is used for defining the reference fields for DFF as shown below:
Reference fields are useful for automatically prompting user to enter required values. This can be explained by an example:
Suppose a manufacturing firm sells three kinds of items – Computer, Printers and Fax Machines. Depending upon the item type, we would like to capture different kind of custom information from the user. If ‘item type’ is a reference field in the DFF definition, then we can choose ‘item type’ in the reference field during DFF enabling. For each item type, we can define a context field (Computer, Printers, Fax Machines) and the corresponding segments. When user enters item information, depending upon the item type, the corresponding DFF structure appears automatically.
FAQ on DFF
- Can we create a custom DFF?
Ans. Yes, we can create a custom DFF. But the table to be used in the DFF should be registered with Oracle Applications.
- How do we use a custom DFF?
Ans. Custom DFF can be used only in a custom form. For details on how to use a custom DFF in custom form, please see Oracle Forms section.
- What is meant by Global Context?
Ans. When we need to capture same kind of information for all the records, then global context can be used. In this case, for all the records, the same structure appears for data entry.
- How do we use different Attribute columns during DFF activation?
Ans. If we are using two different contexts (say Printer and Fax) and not using Global Context, then we can re-use the attribute columns. That means, an attribute column associated with Printer context can also be associated with the Fax context. However, if we are using global context also, then the attributes used in global context cannot be re-used for any other context. For example, if we are using Attribute1 to Attribute3 in the global context, then we can use only Attribute4 to Attribute15 in the other contexts.
- What is the Difference between Reference Field and Context?
Ans: When we use multiple contexts, user has to manually select a context when entering values. Based on the context chosen, corresponding fields are shown to the user. However, when we use a reference field, a context is automatically defaulted to the user (which cannot be changed). A reference field is one of the fields from the same table.
- How do we use Reference Field?
Ans: Reference field is defined during the DFF definition. Reference field is one of the fields from the same table. Usually, reference field chosen should be quite selective. For example, a field having 500 distinct value is not a good reference field. However, a field having around 10 distinct values or less can be a good reference field. When reference field is used in the DFF activation, we need to define structure for each of the unique value of the reference field. Once defined properly, based on the reference field, a DFF structure is automatically displayed to the user during data entry.
Subscribe to:
Posts (Atom)
-
I faced some issues when I query the below table data is not fetching in my query: SELECT * FROM hr_operating_units SELECT * FROM MTL_CAT...
-
For this error need to Check RTF template there should be tags are not correctly and if you are developing new RTF latest version. Just se... -
select * from all_objects where object_name like 'GME%BATCH%' and object_type = 'TABLE' --------------------------------...
COMMENTS
Is it possible to use a reference field from another table? Potentially another DFF?
For example, I have a context sensitive DFF structure for invoices. Based on the supplier selected I'd like to reference a DFF value on the supplier record to default this context invoice DFF to capture additional info.
Please let me know if this is possible or if the solution would be a personalization .
Thanks