ORACLE Fusion Cloud, EBS PRO(for Oracle Fusion, EBS Professionals)

EAM SQL Statement to fetch the Short Term attached text for a particular EAM Work Order

2020-07-16T17:42:00.000-04:00

-------------------------------------------------------------------------------------------------------------------

--EAM SQL Statement to fetch the Short Term attached text for a particular EAM Work Order

-- Parameters P_WIP_ENTITY_ID

-- P_ORGANIZATION_ID

-------------------------------------------------------------------------------------------------------------------

SELECT fdst.short_text,

fdv.title,

ad.pk1_value organization_id,

ad.pk2_value wip_entity_id,

fdv.media_id

FROM fnd_documents_vl fdv,

fnd_attached_documents ad,

fnd_documents_short_text fdst

WHERE 1=1

AND fdv.document_id = ad.document_id

AND ad.entity_name = 'EAM_WORK_ORDERS'

AND fdv.datatype_name = 'Short Text'

AND (security_type = 4 OR publish_flag = 'Y')

AND fdst.media_id = fdv.media_id

AND ad.pk2_value = :P_Wip_Entity_Id

AND ad.pk1_value = :P_Organization_Id;

SQL Statement to fetch AME Rules based on a condition across different transaction types

2014-06-22T18:28:00.016-04:00

---------------------------------------------------------------------
--SQL Statement to get AME Rules based on a condition across transaction types
-- Parameter:P_AME_CONDITION -- Input condition name(partial-match works)
---------------------------------------------------------------------

SELECT ame.rulename,ame.creation_date,

ame.condition_and_action "condition/action",

atl.val "Condition_Desc"

FROM (SELECT r.description rulename, r.creation_date,

condition_id, 'Condition' condition_and_action,

ame_utility_pkg.get_condition_description (condition_id) val,

NULL action_type

FROM ame_rules_tl rtl, ame_rules r, ame_condition_usages cu

WHERE r.rule_id = rtl.rule_id

AND cu.rule_id = r.rule_id

AND rtl.LANGUAGE = 'US'

UNION ALL

SELECT DISTINCT r.description rulename, r.creation_date,

a.action_id, 'Action' condition_and_action,

atl.description val,

actl.user_action_type_name action_type

FROM ame_rules_tl rtl,

ame_rules r,

ame_action_usages au,

ame_actions a,

ame_actions_tl atl,

ame_action_types act,

ame_action_types_tl actl

WHERE r.rule_id = rtl.rule_id

AND au.rule_id = r.rule_id

AND rtl.LANGUAGE = 'US'

AND au.action_id = a.action_id

AND a.action_id = atl.action_id

AND act.action_type_id = actl.action_type_id

AND act.action_type_id = a.action_type_id

) ame

WHERE ame_utility_pkg.get_condition_description (condition_id) LIKE '%&P_AME_CONDITION%'

ORDER BY rulename, condition_and_action DESC;

Cloned date of Oracle application and Version

2014-06-22T18:28:00.003-04:00

--Query to get Version details..

SELECT product, version, status

FROM product_component_version;

--Query to get cloned date of an oracle instance

SELECT resetlogs_time,name

FROM v$database;

Select statement to derive all the POs, Reqs delegated by user/s with WF Admin privileges in Oracle EBS

2014-06-22T18:28:00.001-04:00

-------------------------------------------------------------------------------------------------------------------------------------

--Query to fetch all the POs,Requisitions delegated by User with WF Admin privileges

--Parameters:

-- p_po_or_req_num -- Input PO/Requisition Number (Optional parameter)

-- p_delegated_by_racfid -- Input RACFID of person who delegated the PO/Requisitions (Optional parameter)

-- p_delegate_from_date -- Input date to fetch records on/after the input date(Defaults to 03/Aug/2014 if left blank)

-------------------------------------------------------------------------------------------------------------------------------------

SELECT papf2.full_name, pah3.sequence_num seq, pah3.action_code,

pha.segment1, pah3.object_type_code, pah3.note,

pah3.last_update_date, fu.user_name delegated_by,

fu.description delegated_by_person, fu1.user_name delegated_from,

fu1.description delegated_from_person, fu.employee_id,

pah3.employee_id, prh.wf_item_key

FROM hr.per_all_people_f papf2,

po.po_action_history pah3,

po.po_headers_all pha,

fnd_user fu,

fnd_user fu1,

po_requisition_headers_all prh

WHERE pah3.employee_id = papf2.person_id

AND pah3.last_updated_by = fu.user_id

AND pah3.object_id = pha.po_header_id

AND pah3.employee_id = fu1.employee_id

AND TRUNC (SYSDATE) BETWEEN papf2.effective_start_date

AND papf2.effective_end_date

AND pah3.action_code = 'DELEGATE'

AND TRUNC (pah3.action_date) >= NVL(:p_delegate_from_date,'01-JAN-1951')

AND pah3.object_type_code = 'REQUISITION'

AND pah3.object_id = prh.requisition_header_id

AND fu.user_name = NVL (:p_delegated_by_racfid, fu.user_name)

AND prh.segment1 = NVL (:p_po_or_req_num, prh.segment1)

UNION

SELECT papf2.full_name, pah3.sequence_num seq, pah3.action_code,

pha.segment1, pah3.object_type_code, pah3.note,

pah3.last_update_date, fu.user_name delegated_by,

fu.description delegated_by_person, fu1.user_name delegated_from,

fu1.description delegated_from_person, fu.employee_id,

pah3.employee_id, prh.wf_item_key

FROM hr.per_all_people_f papf2,

po.po_action_history pah3,

po.po_headers_all pha,

fnd_user fu,

fnd_user fu1,

po_headers_all prh

WHERE pah3.employee_id = papf2.person_id

AND pah3.last_updated_by = fu.user_id

AND pah3.object_id = pha.po_header_id

AND pah3.employee_id = fu1.employee_id

AND TRUNC (SYSDATE) BETWEEN papf2.effective_start_date

AND papf2.effective_end_date

AND pah3.action_code = 'DELEGATE'

AND TRUNC (pah3.action_date) >= NVL(:p_delegate_from_date,'01-JAN-1951')

AND pah3.object_type_code = 'PO'

AND pah3.object_id = prh.po_header_id

AND prh.segment1 = NVL (:p_po_or_req_num, prh.segment1)

AND fu.user_name = NVL (:p_delegated_by_racfid, fu.user_name)

ORDER BY 5 DESC, 7 DESC;

How to fetch Oracle AME Rule details based on Condition for across transaction types(SQL QUERY)

2014-05-24T20:45:00.004-04:00

-------------------------------------------------------------------------
--SQL Statement to get AME Rules based on a condition across transaction types
-- Parameter:P_AME_CONDITION -- Input condition name(partial-match works)
-------------------------------------------------------------------------

SELECT ame.rulename,ame.creation_date,

ame.condition_and_action "condition/action",

atl.val "Condition_Desc"

FROM (SELECT r.description rulename, r.creation_date,

condition_id, 'Condition' condition_and_action,

ame_utility_pkg.get_condition_description (condition_id) val,

NULL action_type

FROM ame_rules_tl rtl, ame_rules r, ame_condition_usages cu

WHERE r.rule_id = rtl.rule_id

AND cu.rule_id = r.rule_id

AND rtl.LANGUAGE = 'US'

UNION ALL

SELECT DISTINCT r.description rulename, r.creation_date,

a.action_id, 'Action' condition_and_action,

atl.description val,

actl.user_action_type_name action_type

FROM ame_rules_tl rtl,

ame_rules r,

ame_action_usages au,

ame_actions a,

ame_actions_tl atl,

ame_action_types act,

ame_action_types_tl actl

WHERE r.rule_id = rtl.rule_id

AND au.rule_id = r.rule_id

AND rtl.LANGUAGE = 'US'

AND au.action_id = a.action_id

AND a.action_id = atl.action_id

AND act.action_type_id = actl.action_type_id

AND act.action_type_id = a.action_type_id

) ame

WHERE ame_utility_pkg.get_condition_description (condition_id) LIKE '%&P_AME_CONDITION%'

ORDER BY rulename, condition_and_action DESC;

How to remove Scientific Notation for a numeric value in Toad for Oracle

2014-05-24T20:45:00.001-04:00

How to remove Scientific Notation for a numeric value in Toad in Oracle

Example of Scientific Notation:-

View/Toad Options/Data Grids/Data
Uncheck "Display large numbers in Scientific Notation" checkbox as mentioned below:-

Run the query again to view full numeric value in the result:-

How to link OTL Time Attributes with Purchase Order, PO Lines

2014-05-24T20:44:00.001-04:00

Following SQL Statement is a sample to link a specific Purchase Order(PO), Purchase Order Line/s with Oracle Time and Labor Time Entry table:-

Parameters :-

P_PO_NUM - Purchase Order Number
P_LINE_NUM - Purchase Order Line Number(Optional)

SELECT ph.segment1 po_num,

pl.line_num po_line_num,

tbbd.start_time,

tbbd.stop_time

    FROM hxc_time_attribute_usages tau1,
             hxc_time_attributes ta1,
             hxc_time_building_blocks tbbde,
             hxc_time_building_blocks tbbd,
             hxc_timecard_summary hts,
             po_headers_all ph,
             po_lines_all pl
   WHERE  1 = 1
         AND tau1.time_building_block_id = tbbde.time_building_block_id
         AND tau1.time_building_block_ovn = tbbde.object_version_number
         AND tbbde.SCOPE = 'DETAIL'
         AND tbbde.parent_building_block_id = tbbd.time_building_block_id
         AND tbbde.parent_building_block_ovn = tbbd.object_version_number
         AND tbbd.SCOPE = 'DAY'
         AND tbbd.parent_building_block_id = hts.timecard_id
         AND tbbd.parent_building_block_ovn = hts.timecard_ovn
         AND tau1.time_attribute_id = ta1.time_attribute_id
         AND ta1.bld_blk_info_type_id = 61
         AND ph.po_header_id = TO_NUMBER (ta1.attribute8)
         AND pl.po_line_id = TO_NUMBER (ta1.attribute2)
         AND ph.po_header_id = pl.po_header_id
         AND ph.segment1 = '&P_PO_NUM'
         AND pl.line_num = NVL ('&P_LINE_NUM', pl.line_num)
ORDER BY tbbd.start_time, tbbd.stop_time;

How to change Number Generation for "Contingent Worker" in Oracle HRMS

2014-04-11T00:45:00.001-04:00

Navigate -> US Super HRMS Manager -> Work Structures -> Organization -> Description
Query for the "Business Group" for which you want to change the "Contingency Worker Number Generation"

Click on "Others" button and Select the first option "Business Group Info."

The "Contingent Worker Number Generation" has the following Options in the LOV:-

Automatic - Default Value
Manual
Use Employee Numbering

Tip : The "Contingent Worker Number" is stored in PER_ALL_PEOPLE_F.NPW_NUMBER column

AP Payments to GL Link - Select statement linking JE, JE Lines to AP Payments using SLA tables/views

2014-04-11T00:32:00.001-04:00

Following SQL statement is used to link the AP Payment/s to Journal Entry and Journal Entry Lines

Parameters:-
1. P_FROM_DATE - Journal Header Accounted From Date
2. P_FROM_DATE - Journal Header Accounted To Date
3. P_CHECK_NUMBER - AP Check Number

SELECT XAH.AE_HEADER_ID,

XTE.ENTITY_ID,

GJH.JE_HEADER_ID,

GJL.JE_LINE_NUM,

GJH.DESCRIPTION JV_HEADER_DESCRIPTION,

GJH.NAME JV_NAME,

GJH.JE_CATEGORY,

GJH.JE_SOURCE,

GJH.PERIOD_NAME,

XE.EVENT_NUMBER,

NVL (XAL.ACCOUNTED_CR, NULL) GL_CR,

NVL (XAL.ACCOUNTED_DR, NULL) GL_DR,

GJL.DESCRIPTION JV_LINE_DESCRIPTION,

XAH.EVENT_TYPE_CODE,

XAH.DESCRIPTION SLA_DESCRIPTION,

XAL.AE_LINE_NUM,

XAL.ACCOUNTING_DATE GL_DATE,

ASUP.VENDOR_NAME,

TO_CHAR (ACA.CHECK_NUMBER) CHECK_NUMBER,

ACA.CHECK_DATE,

ACA.DOC_SEQUENCE_VALUE VOUCHER_NUMBER,

ACA.CREATION_DATE VOUCHER_DATE,

XAH.EVENT_ID,

DECODE (XAH.EVENT_TYPE_CODE,

'PAYMENT CANCELLED', AMOUNT * NVL (EXCHANGE_RATE, 1),

'REFUND RECORDED', XAL.ACCOUNTED_DR,

RECEIPT,

DECODE (XAH.EVENT_TYPE_CODE,

'PAYMENT CREATED', AMOUNT * NVL (EXCHANGE_RATE, 1),

PAYMENT,

DECODE (XAL.ACCOUNTED_CR, NULL, XAL.ACCOUNTED_DR, NULL) RECEIPT,

DECODE (XAL.ACCOUNTED_DR, NULL, XAL.ACCOUNTED_CR, NULL) PAYMENT

FROM XLA_EVENTS XE,

XLA_AE_HEADERS XAH,

XLA_AE_LINES XAL,

XLA_TRANSACTION_ENTITIES XTE,

GL_JE_LINES GJL,

GL_IMPORT_REFERENCES GIR,

GL_JE_HEADERS GJH,

GL_CODE_COMBINATIONS GCC,

AP_SUPPLIERS ASUP,

AP_CHECKS_ALL ACA

WHERE XAH.AE_HEADER_ID = XAL.AE_HEADER_ID

AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM

AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID

AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE

AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID

AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID

AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

AND ASUP.VENDOR_ID(+) = XAL.PARTY_ID

AND ACA.CHECK_ID = XTE.SOURCE_ID_INT_1

AND XTE.ENTITY_ID = XAH.ENTITY_ID

AND XTE.ENTITY_CODE = 'AP_PAYMENTS'

AND XAH.EVENT_ID = XE.EVENT_ID

AND XTE.ENTITY_ID = XE.ENTITY_ID

AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL (

:P_FROM_DATE,

TRUNC (

GJH.DEFAULT_EFFECTIVE_DATE))

AND NVL (

:P_TO_DATE,

TRUNC (

GJH.DEFAULT_EFFECTIVE_DATE))

AND GJH.STATUS = 'P'

AND GJH.JE_SOURCE = 'Payables'

AND GJH.JE_CATEGORY = 'Payments'

AND ACA.CHECK_NUMBER = NVL(:P_CHECK_NUMBER, ACA.CHECK_NUMBER)

ORDER BY GJH.JE_HEADER_ID, GJL.JE_LINE_NUM;

How to set FND Profile Option Value programmatically

2014-03-20T00:15:00.004-04:00

Sample script to set FND Profile value programmatically at the User Level:-

Note:- The profile value can as well be set at SITE/APPLICATION/RESPONSIBILITY level by changing the "X_LEVEL_NAME" parameter accordingly.

-------------------------------------------------------------------------------

SET SERVEROUTPUT ON

DECLARE

x_val BOOLEAN;

x_profile fnd_profile_options_tl.PROFILE_OPTION_NAME%TYPE;

BEGIN

SELECT PROFILE_OPTION_NAME

INTO x_profile

FROM fnd_profile_options_tl

WHERE user_profile_option_name = 'ABM: ABM Help'; -- Change here

FOR rec_i IN (SELECT user_id FROM fnd_user WHERE user_name IN ( 'EBSPRO' -- Build List Here ))

LOOP

x_val := FND_PROFILE.SAVE

( X_NAME => x_profile

, X_VALUE => 'Test123' -- Change here

, X_LEVEL_NAME => 'USER'

, X_LEVEL_VALUE => rec_i.user_id

);

IF x_val THEN

dbms_output.put_line('TRUE');

ELSE

dbms_output.put_line('FALSE');

END IF;

END LOOP;

END;

COMMIT;

---------------------------------------------------------------------------------

How to Update HR Assignment programmatically using HR_ASSIGNMENT_API

2014-03-20T00:15:00.001-04:00

-- Following is the sample code to programmatically create Flex value for a particular Value set

--API Name: fnd_flex_val_api.create_independent_vset_value

-- Parameters:

-- P_DATE -- Effective Date

-- P_ASSIGNMENT_ID -- Assignment_Id

-- P_JOB_ID -- Job_ID

-- P_POSITION_ID -- Position_Id

DECLARE

l_date DATE := TO_DATE ('&P_DATE', 'DD-MON-YYYY');

l_true BOOLEAN := FALSE;

l_assign_id NUMBER := &P_ASSIGNMENT_ID;

l_obj NUMBER;

l_datetrack_update_mode VARCHAR2 (30);

l_organization_id NUMBER;

l_special_ceiling_step_id NUMBER;

l_effective_start_date DATE;

l_effective_end_date DATE;

l_people_group_id NUMBER;

l_group_name VARCHAR2 (30);

l_org_now_no_manager_warning BOOLEAN;

l_other_manager_warning BOOLEAN;

l_spp_delete_warning BOOLEAN;

l_entries_changed_warning VARCHAR2 (30);

l_tax_district_changed_warning BOOLEAN;

l_job_id NUMBER := &P_JOB_ID;

l_position_id NUMBER := &P_POSITION_ID;

CURSOR csr_ovn

SELECT object_version_number

FROM per_all_assignments_f

WHERE assignment_id = l_assign_id

AND l_date BETWEEN effective_start_date AND effective_end_date;

BEGIN

OPEN csr_ovn;

FETCH csr_ovn INTO l_obj;

CLOSE csr_ovn;

hr_assignment_api.update_emp_asg_criteria (

p_validate => l_true,

p_effective_date => l_date,

p_datetrack_update_mode => 'UPDATE',

p_assignment_id => l_assign_id,

p_object_version_number => l_obj,

p_job_id => l_job_id,

p_position_id => l_position_id,

p_ass_attribute1 => l_emp_job_imp_rec.mgr_level,

p_ass_attribute2 => l_emp_job_imp_rec.per_sal_type,

p_special_ceiling_step_id => l_special_ceiling_step_id,

P_effective_start_date => l_effective_start_date,

p_effective_end_date => l_effective_end_date,

p_people_group_id => l_people_group_id,

p_group_name => l_group_name,

p_org_now_no_manager_warning => l_org_now_no_manager_warning,

p_other_manager_warning => l_other_manager_warning,

p_spp_delete_warning => l_spp_delete_warning,

p_entries_changed_warning => l_entries_changed_warning,

p_tax_district_changed_warning => l_tax_district_changed_warning);

DBMS_OUTPUT.put_line ('Successfully updated job id to assignment ');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Exception=>' || SQLERRM);

END;

How to progrmmatically create FND flex values for a Flex Value Set using FND_FLEX_VAL_API

2014-02-24T23:51:00.003-05:00

-- Following is the sample code to programmatically create Flex value for a particular Value set

--API Name: fnd_flex_val_api.create_independent_vset_value

-- Parameters:

-- p_user_id -- FND User_Id

-- p_resp_id -- Responsibility Id

-- p_resp_appl_id -- Responsibility Application Id

-- p_flex_value_set_name -- FND Flex Value Set Name

-- p_flex_value -- FND Flex Value

-- p_flex_value_desc -- FND Flex Value Description

DECLARE

x_str_value VARCHAR2 (2000);

lv_message VARCHAR2 (2000);

lv_user_id NUMBER := &p_user_id;

lv_resp_id NUMBER := &p_resp_id;

lv_resp_appl_id NUMBER := &p_resp_appl_id;

lv_flex_vs_name VARCHAR2 (60) := '&p_flex_value_set_name';

lv_flex_value VARCHAR2 (60) := '&p_flex_value';

lv_flex_value_desc VARCHAR2 (60) := '&p_flex_value_desc';

BEGIN

--Initialize required if this block is run from within Oracle database

fnd_global.apps_initialize (user_id => lv_user_id,

resp_id => lv_resp_id,

resp_appl_id => lv_resp_appl_id);

fnd_flex_val_api.create_independent_vset_value (

p_flex_value_set_name => lv_flex_vs_name,

p_flex_value => lv_flex_value,

p_description => lv_flex_value_desc,

p_enabled_flag => 'Y',

x_storage_value => x_str_value);

DBMS_OUTPUT.put_line (x_str_value);

EXCEPTION

WHEN OTHERS

THEN

v_message := fnd_flex_val_api.MESSAGE;

DBMS_OUTPUT.put_line (lv_message);

END;

How to programmatically create HR Job Codes Key Flexfield using HR_JOB_API

2014-02-24T23:51:00.001-05:00

-- Following is a sample code to create/populate HR Jobs in Oracle HRMS module

-- API Name: HR_JOB_API.CREATE_JOB

-- Parameters:

-- p_bg_id - Business_Group_Id

-- p_job_code - Job Code populated into Segment1 of Job Key Flexfield

-- p_job_code - Job Title populated into Segment2 of Job Key Flexfield

DECLARE

op_job_id NUMBER;

op_object_version_number NUMBER;

x_job_definition_id NUMBER;

op_name VARCHAR2 (1000);

lv_job_group_id per_job_groups.job_group_id%TYPE;

lv_business_group_id NUMBER := &p_bg_id;

lv_job_code VARCHAR2 (60) := '&p_job_code';

lv_job_title VARCHAR2 (60) := '&p_job_title';

BEGIN

---------------------------------------------------

-->> JOB GROUP ID <<--

---------------------------------------------------

SELECT job_group_id

INTO lv_job_group_id

FROM per_job_groups

WHERE 1=1

AND displayed_name LIKE 'Setup Business Group'

AND internal_name = 'HR_81';

hr_job_api.create_job (

p_business_group_id => lv_business_group_id,

p_date_from => '01-JAN-1950',

p_job_group_id => lv_job_group_id,

p_segment1 => lv_job_code, --JOB_CODE

p_segment2 => lv_job_title, --JOB_TITLE

p_job_id => op_job_id,

p_object_version_number => op_object_version_number,

p_job_definition_id => x_job_definition_id,

p_job_information_category => 'US',

p_name => op_name);

DBMS_OUTPUT.PUT_LINE ('OUTPUT JOB NAME CREATED : ' || op_name);

DBMS_OUTPUT.PUT_LINE ('OUTPUT JOB ID CREATED : ' || op_job_id);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE ('Error Message Is: ' || SQLERRM);

END;

FND_MESSAGE Displaying only message code and not message text

2013-12-19T17:08:00.003-05:00

--Application: The short name of the application this message is associated with. This short name references the application
--you associate with your message when
--you define it using the Messages form.
--
--Name: The message name that identifies your message. This name is identical to the name you use when you define your message

--using the Messages form. Message Dictionary names are not case sensitive (for example, MESSAGE_NAME is the same name as message_name).

--

--Procedure FND_MESSAGE.SET_NAME(application IN varchar2,
--                               name IN varchar2);
DECLARE
   l_message VARCHAR2 (4000);

BEGIN
   FND_MESSAGE.SET_NAME ('XXTST', 'XX_TEST_MSG_NAME');
   l_message := FND_MESSAGE.GET;   DBMS_OUTPUT.put_line(l_message);

END;

--Note:-IF FND_MESSAGE.GET displays only the message code whatever
--is passed in the 2nd parameter and not message text, then, it
--means either the "Application Short Name" or the "Message Name" is
--incorrect
--Also, make sure the "Generate Messages" Concurrent Program is run
--from "Application Developer" responsibility

SELECT Statement to retrieve Oracle AOL Descriptive Flexfield Segment(DFF) Details

2013-12-19T16:04:00.002-05:00

SELECT Statement to retrieve Oracle AOL Descriptive Flexfield Segment(DFF) Details

-- Application Developer > Navigate > Flexfields > Descriptive > Segments
-- Search for relevant Title, second half of the screen, under "Context Field Values"
-- lists the main parts of the Flexfield
-- Click into a name on the "Context Field Values" section in the lower part of the
-- screen, and click "Segments"
-- This lists the bits users see in Core Applications when they click into the DFF
-- plus shows if there is a LOV linked to the field
-- Following SELECT statement retrieves DFF details related to an application,DFF
-- title, DFF Context or
-- the entire application if no parameter is passed

-- Optional Parameters:-
-- P_TITLE -- DFF Title
-- P_APPLICATION -- Application Name
-- P_DESC_FLEX_CD, DFF Context

SELECT fat.application_name
       , fdfv.title
       , fdfcv.descriptive_flex_context_code context
       , fdfcuv.column_seq_num num
       , fdfcuv.end_user_column_name name
       , fdfcuv.application_column_name column_
       , ffvs.flex_value_set_name value_set
       , ffvs.description value_set_description
       , fdfcuv.required_flag
       , fdfcuv.display_flag
       , fdfcuv.enabled_flag
       , fdfcuv.security_enabled_flag
       , fdfcuv.default_value
    FROM apps.fnd_descriptive_flexs_vl fdfv
       , applsys.fnd_application_tl fat
       , apps.fnd_descr_flex_contexts_vl fdfcv
       , apps.fnd_descr_flex_col_usage_vl fdfcuv
       , applsys.fnd_flex_value_sets ffvs
   WHERE fdfv.application_id = fat.application_id
     AND fdfcv.descriptive_flexfield_name = fdfv.descriptive_flexfield_name
     AND fdfcv.descriptive_flexfield_name = fdfcuv.descriptive_flexfield_name
     AND fdfcv.descriptive_flex_context_code =
                                       fdfcuv.descriptive_flex_context_code
     AND fdfcuv.flex_value_set_id = ffvs.flex_value_set_id(+)
     AND fdfv.title = NVL(:P_TITLE,fdfv.title)
     AND fat.application_name = NVL(:P_APPLICATION,fat.application_name)
     AND fdfcv.descriptive_flex_context_code =
                         NVL(:P_DESC_FLEX_CD,fdfcv.descriptive_flex_context_code)

ORDER BY fat.application_name

, fdfv.title

, fdfcv.descriptive_flex_context_code

, fdfcuv.column_seq_num;

How to enable the MOAC parameter “Operating Unit” for a Concurrent Program in Oracle EBS R12

2013-12-10T16:29:00.002-05:00

You can notice that the new field “Operating Unit” which is new field in R12 and by default is in disabled mode:-

With the introduction of multi-org access control in release 12, an applications responsibility
can access multiple operating units. Some concurrent programs have been enhanced to process multiple operating units simultaneously, while for other requests the operating unit must be specified when you run the program.

To support this, concurrent programs are defined with an operating unit mode of 'S' for single operating unit or 'M'(concurrent program will be in this mode by default) for multiple operating units. If the 'Operating Unit Mode' is not set for the concurrent program it will fail.

The “Operating Unit” field/parameter is known as “Reporting Context” in MOAC(Multi Org Access Control) terminology.

For a concurrent program though there may be a need to only run for Single Operating Unit, though the Responsibility has access to Multiple Operating Units’ data based on the “MO: Security Profile” profile option.

Running a particular concurrent program for only a Single Operating Unit can be achieved by a simple update statement or from the front-end application:-

--Execute the following update statement in the database for the particular concurrent program you want set to run for "Single Operating Unit"

UPDATE fnd_concurrent_programs

SET multi_org_category = 'S'

WHERE concurrent_program_name = '<your program name>';

Now we will see how we can achieve from the Oracle Applications front-end.

1. Login into application with System Administration responsibility (NOT System
Administrator)
2. Navigate: Concurrent -> Programs

3. Query for short name of the concurrent program

4. Click on Update pencil icon of your program

5. Under 'Update Concurrent Program' region, select Request tab
6. Under 'Request Setting' region, select 'Single' from the drop down of 'Operating Unit
Mode' field
7. Save changes by clicking on 'Apply' button

8. Change responsibility to the responsibility where the “Concurrent Program” can be run, in

this example “TESTOU” is assigned to “AR Super User” responsibility

9. Select the “Concurrent Program” from the “SRS Window”, now you can see that “Operating

Unit” field is enabled and you can see the Operating Units in the LOV

10. This particular selected Operating Unit value can be accessed using the standard MOAC API: MO_GLOBAL.get_current_org_id

Note:- The return value for the “Operating Unit” reporting context is corresponding “ORG_ID” for the selected “Operating Unit Name”

How to update a Sales Order Line using OE_ORDER_PUB

2013-11-26T08:36:00.001-05:00

-- OE_ORDER_PUB.PROCESS_ORDER will be used to update sales order line details in this example the Bill-to Site of the sales order line is updated.

DECLARE

l_header_rec oe_order_pub.header_rec_type;

l_line_tbl oe_order_pub.line_tbl_type;

l_action_request_tbl oe_order_pub.request_tbl_type;

l_header_adj_tbl oe_order_pub.header_adj_tbl_type;

l_line_adj_tbl oe_order_pub.line_adj_tbl_type;

l_header_scr_tbl oe_order_pub.header_scredit_tbl_type;

l_line_scredit_tbl oe_order_pub.line_scredit_tbl_type;

l_request_rec oe_order_pub.request_rec_type;

l_return_status VARCHAR2(1000);

l_msg_count NUMBER;

l_msg_data VARCHAR2(1000);

p_api_version_number NUMBER := 1.0;

p_init_msg_list VARCHAR2(10) := fnd_api.g_false;

p_return_values VARCHAR2(10) := fnd_api.g_false;

p_action_commit VARCHAR2(10) := fnd_api.g_false;

x_return_status VARCHAR2(1);

x_msg_count NUMBER;

x_msg_data VARCHAR2(100);

p_header_rec oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;

x_header_rec oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;

p_old_header_rec oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;

p_header_val_rec oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec;

p_old_header_val_rec oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec;

p_header_adj_tbl oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl;

p_old_header_adj_tbl oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl;

p_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl;

p_old_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl;

p_header_price_att_tbl oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl;

p_old_header_price_att_tbl oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl;

p_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl;

p_old_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl;

p_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl;

p_old_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl;

p_header_scredit_tbl oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl;

p_old_header_scredit_tbl oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl;

p_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl;

p_old_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl;

x_line_tbl oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;

p_old_line_tbl oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;

p_line_val_tbl oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl;

p_old_line_val_tbl oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl;

p_line_adj_tbl oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl;

p_old_line_adj_tbl oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl;

p_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl;

p_old_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl;

p_line_price_att_tbl oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl;

p_old_line_price_att_tbl oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl;

p_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl;

p_old_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl;

p_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl;

p_old_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl;

p_line_scredit_tbl oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl;

p_old_line_scredit_tbl oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl;

p_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl;

p_old_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl;

p_lot_serial_tbl oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl;

p_old_lot_serial_tbl oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl;

p_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl;

p_old_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl;

p_action_request_tbl oe_order_pub.request_tbl_type := oe_order_pub.g_miss_request_tbl;

x_header_val_rec oe_order_pub.header_val_rec_type;

x_header_adj_tbl oe_order_pub.header_adj_tbl_type;

x_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type;

x_header_price_att_tbl oe_order_pub.header_price_att_tbl_type;

x_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type;

x_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type;

x_header_scredit_tbl oe_order_pub.header_scredit_tbl_type;

x_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type;

x_line_val_tbl oe_order_pub.line_val_tbl_type;

x_line_adj_tbl oe_order_pub.line_adj_tbl_type;

x_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type;

x_line_price_att_tbl oe_order_pub.line_price_att_tbl_type;

x_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type;

x_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type;

x_line_scredit_tbl oe_order_pub.line_scredit_tbl_type;

x_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type;

x_lot_serial_tbl oe_order_pub.lot_serial_tbl_type;

x_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type;

x_action_request_tbl oe_order_pub.request_tbl_type;

x_debug_file VARCHAR2(100);

l_msg_index_out NUMBER(10);

l_line_tbl_index NUMBER;

BEGIN

dbms_output.ENABLE(1000000);

fnd_global.Apps_initialize(1318, 21623, 660);

-- pass in user_id, responsibility_id, and application_id

oe_msg_pub.initialize;

oe_debug_pub.initialize;

mo_global.Init ('ONT'); -- Required for R12

fnd_global.Set_nls_context ('AMERICAN');

mo_global.Set_policy_context ('S', 87); -- Required for R12

--X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE');

--oe_debug_pub.SetDebugLevel(5);

--dbms_output.put_line('START OF NEW DEBUG');

--This is to update a line to an existing order

l_line_tbl_index :=1;

-- Changed attributes

l_line_tbl(l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;

l_line_tbl(l_line_tbl_index).invoice_to_org_id := 322;

-- Primary key of the entity i.e. the order line

l_line_tbl(l_line_tbl_index).line_id := 388459;

l_line_tbl(l_line_tbl_index).change_reason := 'Not provided';

-- Indicates to process order that this is an update operation

l_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_UPDATE;

-- CALL TO PROCESS ORDER

oe_order_pub.Process_order ( p_api_version_number => 1.0 ,

p_init_msg_list => fnd_api.g_false ,

p_return_values => fnd_api.g_false ,

p_action_commit => fnd_api.g_false ,

x_return_status => l_return_status ,

x_msg_count => l_msg_count ,

x_msg_data => l_msg_data ,

p_header_rec => l_header_rec ,

p_line_tbl => l_line_tbl ,

p_action_request_tbl => l_action_request_tbl

-- OUT PARAMETERS

, x_header_rec => x_header_rec

, x_header_val_rec => x_header_val_rec

, x_header_adj_tbl => x_header_adj_tbl

, x_header_adj_val_tbl => x_header_adj_val_tbl

, x_header_price_att_tbl => x_header_price_att_tbl

, x_header_adj_att_tbl => x_header_adj_att_tbl

, x_header_adj_assoc_tbl => x_header_adj_assoc_tbl

, x_header_scredit_tbl => x_header_scredit_tbl

, x_header_scredit_val_tbl => x_header_scredit_val_tbl

, x_line_tbl => x_line_tbl

, x_line_val_tbl => x_line_val_tbl

, x_line_adj_tbl => x_line_adj_tbl

, x_line_adj_val_tbl => x_line_adj_val_tbl

, x_line_price_att_tbl => x_line_price_att_tbl

, x_line_adj_att_tbl => x_line_adj_att_tbl

, x_line_adj_assoc_tbl => x_line_adj_assoc_tbl

, x_line_scredit_tbl => x_line_scredit_tbl

, x_line_scredit_val_tbl => x_line_scredit_val_tbl

, x_lot_serial_tbl => x_lot_serial_tbl

, x_lot_serial_val_tbl => x_lot_serial_val_tbl

, x_action_request_tbl => x_action_request_tbl

);

--dbms_output.put_line('OM Debug file: ' ||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE);

-- Retrieve messages

dbms_output.Put_line('Line Id: ' ||x_line_tbl(l_line_tbl_index).line_id);

FOR i IN 1 .. l_msg_count

LOOP

oe_msg_pub.Get ( p_msg_index => i ,

p_encoded => fnd_api.g_false ,

p_data => l_msg_data ,

p_msg_index_out => l_msg_index_out

);

dbms_output.Put_line('message is: ' || l_msg_data);

dbms_output.Put_line('message index is: ' || l_msg_index_out);

END LOOP;

-- Check the return status

IF l_return_status = fnd_api.g_ret_sts_success

THEN

dbms_output.put_line('Line Quantity Update Sucessful');

ELSE

dbms_output.put_line('Line Quantity update Failed');

END IF;

END;

COMMIT;

--Note:- In R12 it is very important to have different input and output parameter/s to pass to the oe_order_pub API so as to successfully update the record

APP-FND-01931 : your session is no longer valid or your logon or your login information could not be reestablished from your session.

2013-10-17T01:45:00.001-04:00

APP-FND-01931 : your session is no longer valid or your logon information could not be reestablished from your session.

Solution:-

1. Open Internet Explorer (version: 9, 10)

2. Tools -> Internet Options -> "Security" tab

3. Select "Custom Level" button

4. Scroll down to the bottom to find "Enable XSS filter" under "Scripting". If it is "Enabled",
change it to "Disable"

5. Click OK, Apply, OK

6. Restart the IE and try to open Oracle Applications forms

Oracle PLSQL string tokenizer package procedure to break a string into tokens based on a delimiter

2013-09-26T00:13:00.001-04:00

--Following package procedure is used to break a string into substrings or tokens based on a delimiter

CREATE PACKAGE BODY xx_token_pkg
AS TYPE token_tbl IS TABLE OF VARCHAR2 (2000)
INDEX BY BINARY_INTEGER;

PROCEDURE string_tokenizer (
                                      p_string        IN        VARCHAR2,
                                      p_delimiter    IN        VARCHAR2,
                                      p_result_tbl   OUT     xx_ar_billing_ext_pkg.token_tbl
                                           )
   IS

      CURSOR c_tokens
      IS
         SELECT     LEVEL,
                    SUBSTR (string_to_tokenize,
                            DECODE (LEVEL,
                                    1, 1,
                                      INSTR (string_to_tokenize,
                                             delimiter,
                                             1,
                                             LEVEL - 1
                                            )
                                    + 1
                                   ),
                              INSTR (string_to_tokenize, delimiter, 1, LEVEL)
                            - DECODE (LEVEL,
                                      1, 1,
                                        INSTR (string_to_tokenize,
                                               delimiter,
                                               1,
                                               LEVEL - 1
                                              )
                                      + 1
                                     )
                           ) token
               FROM (SELECT p_string || p_delimiter AS string_to_tokenize,
                            p_delimiter AS delimiter
                       FROM DUAL)
         CONNECT BY INSTR (string_to_tokenize, delimiter, 1, LEVEL) > 0
         ORDER BY LEVEL ASC;

l_index BINARY_INTEGER := 0;

BEGIN

FOR token_rec IN c_tokens

      LOOP
         p_result_tbl (l_index) := token_rec.token;
         l_index := l_index + 1;
      END LOOP;

EXCEPTION

     WHEN OTHERS THEN
       print_log('Exception in string_tokenizer'||sqlerrm);
   END string_tokenizer;

END xx_token_pkg;

--Following is an anonymous block which calls xx_token_pkg.string_tokenizer to break the string into substrings/tokens using a separater/delimiter '-'DECLARE

l_result xx_token_pkg.token_tbl;

BEGIN

xx_token_pkg.string_tokenizer ('Test1-Test2-Test3', '-', l_result);

FOR i IN l_result.FIRST .. l_result.LAST

LOOP

DBMS_OUTPUT.put_line ('result=' || l_result (i));

END LOOP;

END;

Running the above blcok results in the following output:-

result=Test1
result=Test2
result=Test3

How to update an existing value in HZ_CUST_ACCOUNTS table to NULL fnd_api.g_null_char

2013-08-29T00:10:00.003-04:00

--How to assign null value to TCA APIs

--Take an example of HZ_CUST_ACCOUNTS.ATTRIBUTE1 column has an existing value of "YES"

--if you want to set this value to NULL by assigning NULL to the attribute1 record type and passing it to the TCA API hz_cust_account_v2pub.update_cust_account

--will not set the value to null

-- We need to use "fnd_api.g_null_char" to set an existing value in the TCA table columns to NULL

-- Below example sets the HZ_CUST_ACCOUNTS.ATTRIBUTE1 which has an existing value of 'YES" to NULL

DECLARE

x_return_status VARCHAR2 (100);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (32767);

BEGIN

lv_cust_account_rec.cust_account_id := 1234;

l_acct_ovn := 5;

lv_cust_account_rec.attribute1 := fnd_api.g_null_char;

print_log ('Calling Customer Account Update API', 2);

hz_cust_account_v2pub.update_cust_account (

p_init_msg_list => fnd_api.g_false,

p_cust_account_rec => lv_cust_account_rec,

p_object_version_number => l_acct_ovn,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data);

IF x_return_status <> 'S'

THEN

FOR i IN 1 .. x_msg_count

LOOP

l_error_msg :=

l_error_msg || i || '.'

|| SUBSTR (

NVL (fnd_msg_pub.get (p_encoded => fnd_api.g_false),

x_msg_data),

500);

END LOOP;

END IF;

END;

TCA API Example to Update Party Site using hz_party_site_v2pub.update_party_site

2013-07-31T16:30:00.002-04:00

DECLARE

l_party_site_rec hz_party_site_v2pub.PARTY_SITE_REC_TYPE;

l_obj_num NUMBER := 2;

l_return_status VARCHAR2 (1);

l_msg_count NUMBER;

l_msg_data VARCHAR2 (2000);

BEGIN

l_party_site_rec.party_site_id := &p_party_site_id;

l_party_site_rec.status := '&p_status';

l_party_site_rec.identifying_address_flag := 'p_id_address';

l_obj_num := &p_ovn;

hz_party_site_v2pub.update_party_site (

p_init_msg_list => FND_API.G_FALSE,

p_party_site_rec => l_party_site_rec,

p_object_version_number => l_obj_num,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data);

DBMS_OUTPUT.put_line ('Ret Status:' || l_return_status);

DBMS_OUTPUT.put_line ('l_msg_data:' || l_msg_data);

IF l_msg_count > 0

THEN

FOR I IN 1 .. l_msg_count

LOOP

DBMS_OUTPUT.put_line (

|| '.'

|| SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE), 1, 255));

END LOOP;

END IF;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Error:' || SQLERRM);

END;

Useful SQL Queries to validate FND(Foundation)(Conc. Program, Req Set etc.,) and XDO(Xml Data Objects) Objects(RTF file, Template)

2013-07-01T22:56:00.001-04:00

-- Select statement to identify which a concurrent_program

-- Parameters:

-- p_conc_prog - Concurrent Program Short Name

SELECT cp.user_concurrent_program_name, cp.concurrent_program_name

FROM fnd_concurrent_programs_vl cp

WHERE cp.concurrent_program_name = '&p_conc_prog';

-- Select statement to identify which request group is associated to a particular concurrent_program

-- Parameters:

-- p_conc_prog - Concurrent Program Name

-- p_req_group - Request Group Name

SELECT cp.user_concurrent_program_name,

cp.concurrent_program_name,

rg.request_group_name

FROM fnd_request_group_units rgu,

fnd_request_groups rg,

fnd_concurrent_programs_vl cp

WHERE cp.concurrent_program_id = rgu.request_unit_id

AND rg.request_group_id = rgu.request_group_id

AND cp.concurrent_program_name = '&p_conc_prog'

AND rg.request_group_name = '&p_req_group';

-- Select statement to identify Request set and it's corresponding stages and stage links for a particular request set

-- Parameters:

-- p_req_set_short_name - Request Set Name

SELECT rs.request_set_name,

rs.user_request_set_name,

sv.stage_name,

sv.request_set_stage_id,

sv.success_link,

sv.success_link_name,

sv.warning_link,

sv.warning_link_name,

sv.error_link,

sv.error_link_name,

sv.display_sequence,

sv.user_executable_name,

sv.user_stage_name

FROM apps.fnd_request_sets_vl rs, fnd_req_set_stages_form_v sv

WHERE rs.request_set_name = UPPER('&p_req_set_short_name')

AND sv.request_set_id = rs.request_set_id;

xx_ar_std_register_trans

-- Select statement to identify which request group is associated to a particular request set

-- Parameters:

-- p_req_set - Request Set Name

-- p_req_group - Request Group Name

SELECT cp.request_set_name, cp.user_request_set_name, rg.request_group_name

FROM fnd_request_group_units rgu,

fnd_request_groups rg,

fnd_request_sets_vl cp

WHERE cp.request_set_id = rgu.request_unit_id

AND rg.request_group_id = rgu.request_group_id

AND cp.request_set_name = '&p_req_set'

AND rg.request_group_name = '&p_req_group';

--Select statement to validate RTF Template

--Parameter

-- p_template_code - Template Code

SELECT xl.lob_type,

xl.application_short_name,

xl.lob_code,

xl.language,

xl.file_name,

xl.xdo_file_type,

xl.file_content_type,

xl.file_data,

xl.program,

xl.territory,

xt.template_code,

xt.data_source_code

FROM xdo_lobs xl, xdo_templates_b xt

WHERE xt.template_code = xl.lob_code(+)

AND template_code = '&p_template_code';

--Select statement to validate Forms Personalization

-- Parameter

-- p_func_name - Form Function Name

SELECT ff.user_function_name,

ff.user_function_name,

ff.function_id,

cr.description,

cr.trigger_event,

cr.trigger_object,

cr.condition,

cr.sequence,

cr.form_name

FROM fnd_form_custom_rules cr, fnd_form_functions_vl ff

WHERE ff.function_name = cr.function_name

AND ff.function_name = '&p_func_name';

-- Select statement to validate Custom Profile

-- Parameter:

-- p_prof_name -- Profile Option Name

SELECT po.profile_option_name,

po.user_profile_option_name,

pov.profile_option_value

FROM fnd_profile_options_vl po, fnd_profile_option_values pov

WHERE pov.profile_option_id = po.profile_option_id

AND po.profile_option_name LIKE UPPER ('&p_prof_name%');

-- Select statement to identify association of Form function to a particular Menu as a menu entry

-- Parameters:

-- p_menu_name - Menu Name

-- p_function_name - Function Name

SELECT mv.menu_name,

ff.function_name,

ff.user_function_name,

me.prompt,

me.description Entry_Description

FROM fnd_menus_vl mv, fnd_menu_entries_vl me, fnd_form_functions_vl ff

WHERE mv.menu_id = me.menu_id

AND mv.menu_name = '&p_menu_name'

AND ff.function_id = me.function_id

AND ff.function_name = '&p_function_name';

How to select user and responsibility details associated to an Application(FND) user

2013-06-26T01:48:00.001-04:00

-- Query to derive responsibility/responsibilities associated to an Application(FND) user/users

Parameters:-

1. Application(FND) User Name

2. Responsibility Name

SELECT u.user_name,

u.start_date user_start_date,

u.end_date user_end_date,

u.email_address user_email_address,

r.responsibility_name,

g.start_date resp_assign_start_date,

g.end_date resp_assign_end_date,

g.user_id,

r.responsibility_id

FROM apps.fnd_user u,

apps.fnd_user_resp_groups g,

apps.fnd_responsibility_tl r

WHERE 1 = 1

AND r.responsibility_id = g.responsibility_id

AND g.user_id = u.user_id

AND u.user_name = NVL(:p_user_name, u.user_name)

AND r.responsibility_name = NVL(:p_responsibility_name, r.responsibility_name)

ORDER BY u.user_name, r.responsibility_name DESC;

How to fetch Aging Bucket line details for a delinquent Customer account

2013-04-30T18:46:00.000-04:00

Following Select statement will fetch Aging bucket line details for a particular Customer to send dunning letter:-

Parameters:

Dunning Plan Name
Customer Account Number

SELECT l.aging_bucket_line_id,
                l.type Aging_Bucket_Line_Type,
                l.days_start,
                l.days_to
FROM ar_aging_bucket_lines l,
              ar_aging_buckets b,
              iex_dunning_plans_vl d
WHERE 1 = 1
   AND d.name = :p_dunning_plan_name
   AND d.aging_bucket_id = b.aging_bucket_id
   AND b.aging_bucket_id = l.aging_bucket_id
   AND (SELECT MAX (TRUNC (SYSDATE) - TRUNC (ar.due_date)) days
                 FROM iex_delinquencies del,
                             ar_payment_schedules ar,
                             hz_cust_accounts hca
             WHERE 1 = 1
             AND hca.cust_account_id = del.cust_account_id
             AND hca.account_number = :p_account_number
            AND del.payment_schedule_id = ar.payment_schedule_id
            AND del.status IN ('DELINQUENT', 'PREDELINQUENT'))
            BETWEEN l.days_start AND l.days_to
            AND EXISTS                    (SELECT 1
                     FROM iex_ag_dn_xref x
                     WHERE 1=1
                      AND d.dunning_plan_id = x.dunning_plan_id
                      AND d.aging_bucket_id = x.aging_bucket_id
                      AND x.aging_bucket_line_id = l.aging_bucket_line_id);

How to delete a Concurrent Program and Concurrent Executable

2013-03-31T23:44:00.001-04:00

Concurrent program cannot be deleted from Oracle Applications front-end, it can only be disabled.

Try to delete a concurrent program from Oracle Applications front-end:-

Execute the following database script using fnd_program.delete_program to delete Concurrent program programmatically:-

BEGIN
       fnd_program.delete_program (program_short_name => 'XX_CP', -- Conc. Executable Short Name
                                                  application => 'AR' -- Program Application Short Name
                                                           );
    COMMIT;
END;

After the program deletion script is executed, try to query the Concurrent Program from Oracle Applications front-end:-

Steps:- F11, Enter Program: XX Concurrent Program, Ctrl + F11

Note: - The concurrent program is deleted

The Concurrent executable can be deleted from the Oracle Applications front-end after the associated Concurrent Program is deleted or it can be deleted by using a database script:-

Press on “Delete” icon and save to delete Concurrent executable:-

Following database script using fnd_program.delete_executable can be used to delete Concurrent executable programmatically:-

BEGIN
fnd_program.delete_executable (executable_short_name => 'XX_EXEC', -- Conc. Executable Short Name
                                                 application => 'AR' -- Program Application Short Name
                                                    );
   COMMIT;
END;

After the executable deletion script is executed in the database, try to query the Concurrent Program from Oracle Applications front-end:-

Steps:- F11, Enter Executable: XX_EXEC, Ctrl + F11

Note: - The concurrent executable is deleted