API - GL Code Combination Creation

1) Custom Table to store account segments.
 
create table XX_ACCOUNT_COMBINATIONS (CODE VARCHAR2(2000))
 
===========================================================================================
 
 
2) Function to Create or Check the Code Combinations in GL 
 
create or replace function create_ccid 
( p_concat_segs in varchar2
) return varchar2
is
  -- pragma autonomous_transaction; -- if you need autonomy!
  l_keyval_status     BOOLEAN;
  l_coa_id            NUMBER;
   l_err_msg          varchar2(2000);
   l_error varchar2(255);
begin
  begin
    select chart_of_accounts_id
    into   l_coa_id
    from   gl_sets_of_books
    where  set_of_books_id = fnd_profile.value('GL_SET_OF_BKS_ID');
  exception
    when no_data_found then
      dbms_output.put_line('Chart of Accounts ID not found from profile option GL_SET_OF_BKS_ID');
      dbms_output.put_line('Try setting up your environment with fnd_global.apps_initialize');
      raise;
  end;
  -- keyval_mode can be one of CREATE_COMBINATION CHECK_COMBINATION FIND_COMBINATION
  --create will only work if dynamic inserts on and cross validation rules not broken
  l_keyval_status := fnd_flex_keyval.validate_segs(
                                           'CHECK_COMBINATION',
                                           'SQLGL',
                                           'GL#',
                                           l_coa_id,
                                           p_concat_segs,
                                           'V',
                                           sysdate,
                                           'ALL', NULL, NULL, NULL, NULL,
                                           FALSE,FALSE, NULL, NULL, NULL);
                                
  if l_keyval_status then  
    return 'S';   
  else
   --return l_error;
    l_err_msg:=substr(fnd_flex_keyval.error_message, 1, 240);     --fnd_message.get;
    
    l_error := substr(fnd_flex_keyval.error_message, 1, 240); 
   dbms_output.put_line(l_error); 
   dbms_output.put_line('ERROR SEGMENT :');
   l_error := to_char(fnd_flex_keyval.error_segment);
   dbms_output.put_line(l_error); 
   dbms_output.put_line('ERROR ENCODED :');
   l_error := substr(fnd_flex_keyval.encoded_error_message, 1, 240);
   dbms_output.put_line(l_error); 
   dbms_output.put_line('FALSE'); 
 
    dbms_output.put_line(l_err_msg||substr(sqlerrm,150,3));
    return l_error;
  end if;
end create_ccid;
/
 
3) Table to store Segments and Error / Success Status
 
CREATE TABLE XX_COMBINATION_ERROR_STATUS
(
  CODE    VARCHAR2(2000 BYTE),
  STATUS  VARCHAR2(2000 BYTE)
)
 
 
4) Procedure to Call Function to create combinations from the custom table
 
CREATE OR REPLACE procedure APPS.XX_CREATE_GL_ACC_COMBINATION
IS
V_COMBINATION VARCHAR2(240);
CURSOR C1 IS
SELECT DISTINCT CODE 
FROM   XX_ACCOUNT_COMBINATIONS;
--where   rownum=1;
 
begin
 
FOR I IN C1 LOOP
 
select create_ccid(I.CODE) into V_COMBINATION  from dual;
 
dbms_output.put_line (V_COMBINATION);
 
insert into xx_combination_error_status (code,status) values (i.code,v_combination);
 
END LOOP;
 
 
end;
/

WHAT ARE RICE COMPONENTS

 What are RICE Components

The acronym, RICE, stands for Reports, Interfaces, Conversions, and Enhancements (or Extensions).

During a Oracle Applications implementation, additional configuration, design, or programming requirements are necessary to satisfy functional requirements and achieve the desired functionality. The objective during any implementation would be to keep new object development to an absolute minimum.

RICE objects represent requirements not currently supported within the core functionality of an Oracle Application Module being implemented and thus require additional technical development to satisfy the functional requirement of a organization.

During an Implementation development of RICE components involves:

1. Creation of Reports that are not available as standard reports in the oracle apps modules.

2. Creation of the Interfaces to external systems.

3. Creation of Conversion programs to transfer data from a legacy system to the new system.

4. Enhancements (or extensions) to the existing system to add additional functionality to existing system without disturbing the core software functionality of the system.

A RICE object has a lifecycle throughout the project that includes approval to develop, functional design specification, technical design specification, coding, testing and implementation. The effort required to complete this development cycle can be significant.

Query to get all Personalization applied to Forms in oracle EBS R12.x

select   ffv.form_id "Form ID"
        ,ffv.form_name "Form Name"
        ,ffv.user_form_name "User Form Name"
        ,ffv.description "Form Description"
        ,ffcr.sequence "Sequence"
        ,ffcr.description "Personalization Rule Name"
        ,ppx.full_name "Last updated by"
        ,ppx2.full_name "Created by"
from     fnd_form_vl ffv
        ,fnd_form_custom_rules ffcr
        ,per_people_x ppx
        ,fnd_user fus
        ,per_people_x ppx2
        ,fnd_user fus2
where    ffv.form_name = ffcr.form_name
and      ffcr.last_updated_by = fus.user_id
and      ppx.person_id = fus.employee_id
and      ffcr.created_by = fus2.user_id
and      ppx2.person_id = fus2.employee_id
order by ffv.form_name, ffcr.sequence;

Oracle Apps : LDT Commands

LDT Commands for Upload and Download of Programs:

Download

FNDLOAD apps/<pwd> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXMLBEX.ldt PROGRAM APPLICATION_SHORT_NAME="XXTTY" CONCURRENT_PROGRAM_NAME="XXMLBEXPGAL"

Upload:

FNDLOAD apps/<appspwd> O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXMLBEX.ldt

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

Request group download for Specific Program

Download:

FNDLOAD apps/<appspwd> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXMREQ.ldt REQUEST_GROUP REQUEST_GROUP_NAME="All Project Billing Programs" UNIT_NAME="XXMLBEGAL"

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

Request Group (NEW request group creation)

Download:

FNDLOAD apps/<Pwd> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_XXX_DTC_Request_Gr.ldt REQUEST_GROUP REQUEST_GROUP_NAME="ALL Programs" APPLICATION_SHORT_NAME="XX"

Upload:

FNDLOAD apps/<apps> 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_XXX_DTC_REQUEST_GR.ldt

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

Oracle Menu (New Menu)

Download:

FNDLOAD apps/<Pwd> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXX_DTC_GLOBAL_MENU.ldt MENU MENU_NAME="XXX_DTC_GLOBAL"

Upload:

FND_TOP/bin/FNDLOAD apps/<apps> 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXX_DTC_GLOBAL_MENU.ldt

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

Oracle Responsibility (New Responsibility)

Download:

FNDLOAD apps/<pwd> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XXX_DTC_GLOBAL_RESPO.ldt FND_RESPONSIBILITY RESP_KEY="XXX_DTC_GLOBAL"

Upload

FNDLOAD apps/<apps> 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XXX_DTC_GLOBAL_RESPO.ldt

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

Lookup Type:

DownLoad:

FNDLOAD apps/pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_OA_STATUTORY_ACCT_MAPPING.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XX" LOOKUP_TYPE="XX_OA_STATUTORY_ACCT_MAPPING"

Upload:

FNDLOAD apps/<Pwd> 0 Y UPLAOD $FND_TOP/patch/115/import/aflvmlu.lct XX_OA_STATUTORY_ACCT_MAPPING.ldt

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

Value set only:

Download:

FNDLOAD apps/apps@seed115 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"

Upload:

FNDLOAD apps/apps@seed115 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt

Value set with values:

Download:

FNDLOAD apps/apps@seed115 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"

Upload:

FNDLOAD apps/apps@seed115 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt

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

Descriptive flex field

Download:

FNDLOAD apps/apps_pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_OA_EXP_RPT_SEG_MAP_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME="FND" DESCRIPTIVE_FLEXFIELD_NAME="FND_COMMON_LOOKUPS" DESCRIPTIVE_FLEX_CONTEXT_CODE="XX_OA_EXP_RPT_SEG_MAP"

Upload:

FNDLOAD apps/apps_pwd 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_OA_EXP_RPT_SEG_MAP_DFF.ldt

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

Concurrent Program

Download:

FNDLOAD apps/<pwd> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXOAGLTSIMP_CONC.ldt PROGRAM APPLICATION_SHORT_NAME="XX" CONCURRENT_PROGRAM_NAME="XXOAGLTSIMP"

Upload:

FNDLOAD <apps>/<apps> 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXOAGLTSIMP_CONC.ldt PROGRAM APPLICATION_SHORT_NAME="XX" CONCURRENT_PROGRAM_NAME="XXOAGLTSIMP"

Conc program assignment to Request Group

Download:

FNDLOAD apps/<pwd> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXOAGLTSIMP_RG.ldt REQUEST_GROUP REQUEST_GROUP_NAME=" DTC Global" APPLICATION_SHORT_NAME="XX" REQUEST_GROUP_UNIT UNIT_NAME="XXOAGLTSIMP"

Upload:

FNDLOAD <apps>/<apps> 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXOAGLTSIMP_RG.ldt REQUEST_GROUP REQUEST_GROUP_NAME=" DTC Global" APPLICATION_SHORT_NAME="XX" REQUEST_GROUP_UNIT UNIT_NAME="XXOAGLTSIMP"

FNDLOAD <apps>/<apps> 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_CW_EVAL_NOTIFY_CONC_RG.ldt REQUEST_GROUP REQUEST_GROUP_NAME="PO All Reports" APPLICATION_SHORT_NAME="PO" REQUEST_GROUP_UNIT UNIT_NAME="XX_CW_EVAL_NOTIFY"

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

Forms:

Download

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXFRMNAME.ldt FORM APPLICATION_SHORT_NAME="PN" FORM_NAME="XXFRMNAME"

Upload:

FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afsload.lct XXFRMNAME.ldt

Functions:

Download

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXFUNNAME.ldt FUNCTION FUNC_APP_SHORT_NAME="PN" FUNCTION_NAME="XXFUNNAME"

Upload

FNDLOAD apps/apps O Y UPLOAD @FND:patch/115/import/afsload.lct XXFUNNAME.ldt

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

Form Personalization

Download:

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_PERWSHRG.ldt FND_FORM_CUSTOM_RULES function_name="PERWSHRG-404"

Upload:

FNDLOAD apps/<Pwd> 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XXGLJOURNALIMP.ldt

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

FND Load for DFF Context

FNDLOAD apps/password 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXAPdff_CUST_DECL.ldt DESC_FLEX APPLICATION_SHORT_NAME="SQLAP" DESCRIPTIVE_FLEXFIELD_NAME="AP_INVOICES" DFF_CONTEXT DESCRIPTIVE_FLEX_CONTEXT_CODE="CUST_DECL"

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

FND Load for Messages

Donwload:

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_ICX_POR_LIFECYCLE_PAY_TIP.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='ICX' MESSAGE_NAME=XX_ICX_POR_LIFECYCLE_PAY_TIP

or for all messages

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_ALL_GMS_MESSAGES_00.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='XXGMS'

Upload:

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_ICX_POR_LIFECYCLE_PAY_TIP.ldt

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

FND Load for Alert

Download

FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct Email_to_requestes_for_PO_Receipt.ldt ALR_ALERTS APPLICATION_SHORT_NAME='PO' ALERT_NAME=' Email to requestes for PO Receipt'

Upload

FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct Email_to_requestes_for_PO_Receipt.ldt

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

XML Publisher Data Definition and Templates

a) To download all Templates defined for a particular Data Definition

FNDLOAD apps/apps 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct ldt_name.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=[APPSHORTNAME] DATA_SOURCE_CODE=[DATADEFINITIONCODE]

b) To download for a particular Template and Data Definition

FNDLOAD apps/apps 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct ldt_name.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=[APPSHORTNAME] DATA_SOURCE_CODE=[DATADEFSHORTCODE] TMPL_APP_SHORT_NAME=[APPSHORTNAME] TEMPLATE_CODE=[TEMPLATECODE]

Identify the Locked objects in session

Query to get locked Object in Database.


SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID
/