Create GL Account Code Combination

Following is the tested function to create account code combination if account code combination not exits.

Prerequisites :

Flex Value should already be available for account creation.

Function :

CREATE OR REPLACE FUNCTION APPS.MY_CREATE_GL_CODE_COMBINATION
( P_CONCAT_SEGS IN VARCHAR2
) RETURN VARCHAR2
IS
  L_KEYVAL_STATUS     BOOLEAN;
  L_COA_ID                       NUMBER;


BEGIN

  L_COA_ID := 101;

  L_KEYVAL_STATUS := FND_FLEX_KEYVAL.VALIDATE_SEGS(
                                           'CREATE_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 'F';
 
  END IF;

  EXCEPTION WHEN OTHERS THEN

    RETURN 'F';
END ;


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

To Execute Function :

SELECT JG_CREATE_GL_CODE_COMBINATION('10.111.1010101.101.00.00.00') FROM DUAL;

Function will return 'S' for successful completion.

How To Find File Version

Use the Following Query To Find The File Version in Oracle ERP


SELECT
        AF.APP_SHORT_NAME,
        AF.SUBDIR,
        AF.FILENAME,
        (
            SELECT
                    VERSION
            FROM
                    AD_FILE_VERSIONS AFV
            WHERE
                    AFV.FILE_ID         =   AF.FILE_ID
            AND     AFV.FILE_VERSION_ID =   (SELECT MAX(AV.FILE_VERSION_ID) FROM  AD_FILE_VERSIONS AV WHERE AV.FILE_ID = AF.FILE_ID)
        )           VERSION_NUMBER
FROM
        AD_FILES            AF
WHERE
        AF.FILENAME         IN  (
                                'xlagdf.pkb'        ,
                                'xlagdf.pkh'      
                                )

Oracle Queries Which Returns Create Scripts Objects

GET DDLs from ORACLE

To Get Table Structure :

---- For Table Structure

select tname ,dbms_metadata.get_ddl('TABLE',TNAME,'APPS')
from tab a
where tname like 'SEL%'
and a.TABTYPE ='TABLE'


To Get View Structure :

---- For View Structure
select tname ,dbms_metadata.get_ddl('VIEW',TNAME,'APPS')
from tab a
where tname like 'SEL%'
and a.TABTYPE ='VIEW'


OR   To Get View, Table, Function, Procedure Structure :

select OWNER, OBJECT_TYPE , OBJECT_NAME ,dbms_metadata.get_ddl(OBJECT_TYPE, OBJECT_NAME ,OWNER)
from DBA_OBJECTS a
where OBJECT_NAME like '%MNJ%'
and OBJECT_TYPE in ('VIEW' ,'TABLE','FUNCTION','PROCEDURE')


To Get Personalizations :

---- For Personalization

select * from fnd_form_custom_rules ffcr,fnd_form_custom_actions ffca
where ffcr.id=ffca.RULE_ID
--and id=2242


To Get FAST FORMULAS :

---- For Formulas

select * from FF_FORMULAS_F
where FORMULA_NAME  like 'RCL%'



To Get PLSQL Data : 

--- For PLSQL Data

select * from dba_source
where name like 'SEL%'

Function Which Provide AP Validation Invoice Status

Create Function Mentioned Below to get AP Invoice Validation Status.

For Example :

Invoice Validation Status 

Fully Applied

Unvalidated

Never Validated

Validated

Cancelled

Available

Needs Revalidation

Unpaid

CREATE OR REPLACE function APPS.TATA_AP_INVOICE_STATUS(P_Invoice_id Number)                          
return varchar2 is                                                          
V_STATUS varchar2(500);
                                             
begin                                                                  
                                                                     

        SELECT
                DECODE(AP_INVOICES_UTILITY_PKG.GET_APPROVAL_STATUS(AIA.INVOICE_ID,AIA.INVOICE_AMOUNT,AIA.PAYMENT_STATUS_FLAG,AIA.INVOICE_TYPE_LOOKUP_CODE),
                'FULL'            , 'Fully Applied',
                'NEVER APPROVED'  , 'Never Validated',
                'NEEDS REAPPROVAL', 'Needs Revalidation',
                'CANCELLED'       , 'Cancelled',
                'UNPAID'          , 'Unpaid',
                'AVAILABLE'       , 'Available',
                'UNAPPROVED'      , 'Unvalidated',
                'APPROVED'        , 'Validated',
                'PERMANENT'       , 'Permanent Prepayment',
                NULL)
                INTO  V_STATUS
        FROM  
                AP_INVOICES_ALL AIA
        WHERE   AIA.INVOICE_ID = P_INVOICE_ID;


return(V_STATUS);                                                      
                                                                     
EXCEPTION                                                              
  when no_data_found then                                                      
  return null;                                                              
                                                                     
end;

Extract Create Scripts of Objects

SELECT * FROM USER_SOURCE

OR

SELECT
        OBJ.OWNER,
        OBJ.OBJECT_TYPE,
        OBJ.OBJECT_NAME,
        DBMS_METADATA.GET_DDL(DECODE(OBJ.OBJECT_TYPE,'MATERIALIZED VIEW','MATERIALIZED_VIEW',OBJ.OBJECT_TYPE), OBJ.OBJECT_NAME , OBJ.OWNER )
FROM
        DBA_OBJECTS OBJ
WHERE
        OBJ.OBJECT_TYPE IN  (
                            'INDEX',
                            'MATERIALIZED VIEW',
                            'PACKAGE' ,
                            'FUNCTION',
                            'TABLE',
                            'TRIGGER',
                            'VIEW',
                            'SEQUENCE'
                            )

Best Download Links

Download Oracle Workflow Builder:

 

http://www.oracle.com/technetwork/database/options%20/winclient-101059.html


Key Words:

Download Workflow Builder Free

Download Workflow Builder Guide

Free Download Workflow Builder

Free Workflow Builder

Workflow Builder Download

 

FNDLOAD Download and Upload Concurrent Program and XML Template Script

FNDLOAD Create Concurrent Program and XML Template

Steps

1- Run the query ( Given Below ). 

              Query will provide you download and upload commands. It also enables you to Download  Template File. (i.e.RTF file)

2- Run environment file and Run download script on Source Node.

              This will create .ldt file and as well as log file.

3- Move .ldt file on Target Node and Run environment file and then Upload Script.

              This will upload you required file on Target Node. 

-------------------- Query for Generating FND LOAD Command -------------------------------

SELECT
    VV.USER_CONCURRENT_PROGRAM_NAME,
    FA.APPLICATION_NAME,
    VV.OUTPUT_FILE_TYPE REPORT_TYPE,
    FE.EXECUTABLE_NAME,
    VV.CREATION_DATE CREATION_DATE,
    -- Executable and Program --
    'FNDLOAD apps/' || NVL(:P_APPS_PWD,'apps') || ' 0 Y DOWNLOAD    $FND_TOP/patch/115/import/afcpprog.lct '|| VV.CONCURRENT_PROGRAM_NAME ||'.ldt PROGRAM APPLICATION_SHORT_NAME="' || FA.APPLICATION_SHORT_NAME || '" CONCURRENT_PROGRAM_NAME="' || VV.CONCURRENT_PROGRAM_NAME ||'"' DOWNLOAD_SCRIPT,
    'FNDLOAD apps/' || NVL(:P_APPS_PWD,'apps') || ' 0 Y UPLOAD      $FND_TOP/patch/115/import/afcpprog.lct '|| VV.CONCURRENT_PROGRAM_NAME ||'.ldt' UPLOAD_SCRIPT,
    -- Xml Template --
    XTMP.TEMPLATE_NAME,
    DECODE(VV.OUTPUT_FILE_TYPE,'XML',
    'FNDLOAD apps/' || NVL(:P_APPS_PWD,'apps') || ' 0 Y DOWNLOAD    $XDO_TOP/patch/115/import/xdotmpl.lct  "'|| FE.EXECUTABLE_NAME     ||'_XT.ldt" XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME="' || XDEF.APPLICATION_SHORT_NAME || '"  DATA_SOURCE_CODE="'|| XDEF.DATA_SOURCE_CODE ||'" TMPL_APP_SHORT_NAME="' || XTMP.APPLICATION_SHORT_NAME ||'" TEMPLATE_CODE= "' || XTMP.TEMPLATE_CODE || '"'
    ) DOWNLOAD_TEMPLATE,
    DECODE(VV.OUTPUT_FILE_TYPE,'XML',
    'FNDLOAD apps/' || NVL(:P_APPS_PWD,'apps') || ' 0 Y UPLOAD      $XDO_TOP/patch/115/import/xdotmpl.lct  "'|| FE.EXECUTABLE_NAME     ||'_XT.ldt"'
        ) UPLOAD_TEMPLATE,
    (      
    SELECT
        XL.FILE_NAME    
    FROM      
        XDO_LOBS XL
    WHERE
        XL.XDO_FILE_TYPE            =   'RTF'
    AND XL.LOB_CODE                 =   XTMP.DATA_SOURCE_CODE  
    AND XL.APPLICATION_SHORT_NAME   =   XTMP.APPLICATION_SHORT_NAME
    )     RTF_FILE_NAME,
    (      
    SELECT
        XL.FILE_DATA    
    FROM      
        XDO_LOBS XL
    WHERE
        XL.XDO_FILE_TYPE            =   'RTF'
    AND XL.LOB_CODE                 =   XTMP.DATA_SOURCE_CODE  
    AND XL.APPLICATION_SHORT_NAME   =   XTMP.APPLICATION_SHORT_NAME
    )     CLICK_TO_DOWNLOAD                                                    
FROM
    FND_CONCURRENT_PROGRAMS_VL      VV,
    FND_APPLICATION_VL              FA,
    FND_EXECUTABLES                 FE,
    XDO_DS_DEFINITIONS_VL           XDEF,
    XDO_TEMPLATES_VL                XTMP
WHERE
    VV.CREATED_BY               =   1110
AND VV.APPLICATION_ID           =   660     ---    Order Management
AND VV.ENABLED_FLAG             =   'Y'
AND VV.EXECUTABLE_ID            =   FE.EXECUTABLE_ID
AND FA.APPLICATION_ID           =   VV.APPLICATION_ID
AND XDEF.DATA_SOURCE_CODE  (+)  =   FE.EXECUTABLE_NAME
AND XTMP.DATA_SOURCE_CODE  (+)  =   FE.EXECUTABLE_NAME
AND UPPER(VV.USER_CONCURRENT_PROGRAM_NAME) LIKE '%'||UPPER(:P_PROGRAM_NAME)||'%'