Oracle Apps

To know the Last Compiled DB Objects and Its Status  SELECT object_name, status, LAST_DDL_TIME,TIMESTAMP   FROM dba_objects  WHERE  1=1        AND owner IN ('APPS', 'XX')        AND TRUNC (LAST_DDL_TIME) = TRUNC (SYSDATE)        AND object_name LIKE '%XX%'; SELECT OWNER,object_name, status, LAST_DDL_TIME,TIMESTAMP   FROM all_objects  WHERE  1=1        AND owner IN ('APPS', 'XX')        AND TRUNC (LAST_DDL_TIME) = TRUNC (SYSDATE-5)       AND object_name LIKE '%XX%';

Query to Fetch NON Serialized Quantity based on Sub inventory Code, Locator Name, organization id and Item Name SELECT msi.segment1,          SUM (mq.transaction_quantity) on_hand,          ood.organization_id,          mq.subinventory_code,          mq.locator_id,          mil.segment1     FROM apps.org_organization_definitions ood,          apps.mtl_onhand_quantities mq,          apps.mtl_system_items_b msi,          mtl_item_locations mil    WHERE     1 = 1          AND mq.organization_id = msi.organization_id          AND ood.organization_id = msi.organization_id          AND mq.inventory_item_id = msi.inventory_item_id          AND...

Query to Fetch Serialized Quantity based on Sub inventory Name, Locator Name, organization id and Item Name SELECT msi.segment1,          ood.ORGANIZATION_ID,          mq.subinventory_code,          mq.LOCATOR_ID,          mil.SEGMENT1,          msn.SERIAL_NUMBER     FROM apps.org_organization_definitions ood,          apps.mtl_onhand_quantities mq,          apps.mtl_system_items_b msi,          mtl_item_locations MIL,          apps.mtl_serial_numbers msn    WHERE     1 = 1          AND mq.organization_id = msi.organization_id          AND ood.organization_id = msi.organization_id          AND mq.inventory_item_id = msi.inventory_item_i...

Query to Fetch the User Function name based on Form name and Function Name SELECT   fl.FORM_NAME,          fl.USER_FORM_NAME,          fl.DESCRIPTION form_description,          ffl.FUNCTION_NAME,          ffl.USER_FUNCTION_NAME   FROM   fnd_form_vl fl, FND_FORM_FUNCTIONS_VL ffl  WHERE   fl.FORM_ID = ffl.FORM_ID AND fl.form_name like 'APXINWKB' and function_name like 'AP_APXINWKB'

Below Commands are Used for Download and upload the .LTD file from one Instances to Other Instances by Using FNDLOAD. Concurrent Program FNDLOAD apps/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct SUPPINVHISTORY_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XX" CONCURRENT_PROGRAM_NAME="SUPPINVHISTORY" FNDLOAD apps/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct SUPPINVHISTORY_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE Function FNDLOAD Apps/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct ITEM_MASTER.ldt FUNCTION FUNCTION_NAME="XX_ITEM_MASTER" FNDLOAD Apps/password 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_ITEM_MASTER_FUN.ldt PLOAD_MODE=REPLACE CUSTOM_MODE=FORCE Web Adi Integrator Download FNDLOAD Apps/<password> 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneintegrator.lct $XX_TOP/bin/SYSTEM_UPLOAD.ldt BNE_INTEGRATORS INTEGRATOR_ASN="XX" INTE...

Using of LISTAGG to get Data in Single column of mulipule Rows SELECT LISTAGG (ename, '; ') WITHIN GROUP (ORDER BY ename DESC) EMP_LIST,          MIN (HIREDATE) Earliest     FROM scott.emp    WHERE 1 = 1 GROUP BY deptno;

Difference between Rank and DENSE_RANK Here When you Check with Rank If we get Same Rank for two Members it will Skip the Sequence and show the Alternate Number Here When you Check with Dense Rank If we get Same Rank for two Members it will Display the Sequence Number From Below Query You can find the Difference of Rank and Dense SELECT empno,        ename,        sal,        RANK () OVER (ORDER BY sal DESC) RANK,        DENSE_RANK () OVER (ORDER BY sal DESC) DENSE_RANK   FROM scott.emp;

Cancel the Concurrent Program from Backend SELECT fcr.request_id,        fcpt.CONCURRENT_PROGRAM_ID,        DECODE (fcpt.user_concurrent_program_name,                'Report Set', 'Report Set:' || fcr.description,                fcpt.user_concurrent_program_name)           CONC_PROG_NAME,        argument_text PARAMETERS,        NVL2 (fcr.resubmit_interval,              'PERIODICALLY',              NVL2 (fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE'))           PROG_SCHEDULE_TYPE,        DECODE (           NVL2 (fcr.resubmit_interval,                 'PERIODICALLY',             ...

To get the all DB Objects Backup files to Stage table CREATE TABLE XX_DB_BACKUP_TB (    Sno             NUMBER,    object_type     VARCHAR2 (100),    Object_name     VARCHAR2 (100),    Object_schema   VARCHAR2 (100),    Object_code     CLOB ); / DECLARE    CURSOR C_back    IS         SELECT owner, object_name, OBJECT_TYPE           FROM dba_objects          WHERE     object_name LIKE ('Give Your Object name here')                AND OBJECT_TYPE IN                       ('PACKAGE',                        'PROCEDURE',               ...

 Item went error please check with this query SELECT b.SEGMENT1,          a.message_name,          b.creation_date,          a.creation_date,          SUBSTR (a.table_name, 1, 25) tbl_name,          SUBSTR (a.column_name, 1, 20) col_name,          a.error_message,          a.organization_id     FROM inv.mtl_interface_errors a, inv.mtl_system_items_interface b    WHERE     a.transaction_id = b.transaction_id          AND b.process_flag NOT IN (1, 7)          AND TRUNC (a.creation_date) = TRUNC(SYSDATE)          AND error_message = '(Keep your Error Message here)' ORDER BY a.creation_date DESC

To get the parameter List of the Report SELECT a.concurrent_program_name AS concurrent_program_name,          a.user_concurrent_program_name AS user_concurrent_program_name,          c.application_short_name AS application_short_name,          b.column_seq_num AS column_seq_num,          b.srw_param AS param_seq,          b.form_left_prompt AS prompt,          d.flex_value_set_name AS values_set_name     FROM fnd_concurrent_programs_vl a,          fnd_descr_flex_col_usage_vl b,          fnd_application c,          fnd_flex_value_sets d    WHERE     a.enabled_flag = 'Y'          AND a.concurrent_program_name =                 SUBSTR (b.descriptive_flexf...

To find the concurrent program Attached to Responsibility SELECT responsibility_name   FROM fnd_responsibility_tl rsp_tl,        fnd_responsibility fr,        fnd_request_groups frg,        fnd_request_group_units frgu,        fnd_concurrent_programs_tl fcpt  WHERE     rsp_tl.responsibility_id = fr.responsibility_id        AND frg.request_group_id = fr.request_group_id        AND fr.request_group_id = frgu.request_group_id        AND fcpt.concurrent_program_id = frgu.request_unit_id        AND UPPER (fcpt.USER_CONCURRENT_PROGRAM_NAME) =               UPPER ('XX program name');

To find the Last Updated Rtf Template SELECT DISTINCT (xl.file_name) "File Name",                 xtb.template_code "Template Code",                 xtt.template_name "Template Name",                 xtb.template_type_code "Type",                 xtb.default_output_type "Default Output Type",                 xtt.last_update_date   FROM apps.xdo_ds_definitions_vl xddv,        apps.xdo_templates_b xtb,        apps.xdo_templates_tl xtt,        apps.xdo_lobs xl,        apps.fnd_application_tl fat,        apps.fnd_application fa  WHERE     1 = 1        AND xddv.application_short_name = fa.application_short_name       ...

To know the Query Behind the LOV in Oracle Apps SELECT client_identifier,        ses.module,        (SELECT TO_CHAR (sql_fulltext)           FROM v$sqlarea          WHERE sql_id = ses.prev_sql_id AND ROWNUM = 1)   FROM v$session ses, v$sqlarea sq  WHERE     1 = 1        AND ses.module LIKE '%Here you need to give the Form Name%'        AND client_identifier = 'Giver the Oracle User Name Here'        AND sq.sql_id(+) = ses.sql_id;