Page View count

Friday, August 26, 2016

Query to get Set of Books, Operating Unit, Organization data

SELECT gl.set_of_books_id,
gl.name,
gl.short_name,
hou.name operatin_unit,
hou.organization_id operating_unit_id,
org.organization_name warehouse_name,
org.organization_id warehouse_id
 FROM org_organization_definitions org,
 hr_operating_units hou,
 gl_sets_of_books gl
 where org.operating_unit=hou.organization_id
 and hou.set_of_books_id = gl.set_of_books_id
order by 1,3,6

Wednesday, August 17, 2016

FND Profile and FND Global Values

FND_PROFILE and FND_GLOBAL values



Following are the FND_PROFILE values that can be used in the PL/SQL code:

   fnd_profile.value('PROFILEOPTION');
   fnd_profile.value('MFG_ORGANIZATION_ID');
   fnd_profile.value('ORG_ID');
   fnd_profile.value('LOGIN_ID');
   fnd_profile.value('USER_ID');
   fnd_profile.value('USERNAME');
   fnd_profile.value('CONCURRENT_REQUEST_ID');
   fnd_profile.value('GL_SET_OF_BKS_ID');
   fnd_profile.value('SO_ORGANIZATION_ID');
   fnd_profile.value('APPL_SHRT_NAME');
   fnd_profile.value('RESP_NAME');
   fnd_profile.value('RESP_ID');

Following are the FND_GLOBAL values that can be used in the PL/SQL code:

   FND_GLOBAL.USER_ID;
   FND_GLOBAL.APPS_INTIALIZE;
   FND_GLOBAL.LOGIN_ID;
   FND_GLOBAL.CONC_LOGIN_ID;
   FND_GLOBAL.PROG_APPL_ID;
   FND_GLOBAL.CONC_PROGRAM_ID;
   FND_GLOBAL.CONC_REQUEST_ID;

For example, I almost always use the following global variable assignments in my package specification to use throughout the entire package body:

   g_user_id      PLS_INTEGER  :=  fnd_global.user_id;
   g_login_id     PLS_INTEGER  :=  fnd_global.login_id;
   g_conc_req_id  PLS_INTEGER  :=  fnd_global.conc_request_id;
   g_org_id       PLS_INTEGER  :=  fnd_profile.value('ORG_ID');
   g_sob_id       PLS_INTEGER  :=  fnd_profile.value('GL_SET_OF_BKS_ID');

And initialize the application environment as follows:

   v_resp_appl_id  := fnd_global.resp_appl_id;
   v_resp_id       := fnd_global.resp_id;
   v_user_id       := fnd_global.user_id;
     
   FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id, v_resp_appl_id);

Wednesday, July 27, 2016

API to Create Lookup Values - "FND_LOOKUP_VALUES"

DECLARE
   CURSOR get_lookup_details
   IS
      SELECT   ltype.application_id,
               ltype.customization_level,
               ltype.creation_date,
               ltype.created_by,
               ltype.last_update_date,
               ltype.last_updated_by,
               ltype.last_update_login,
               tl.lookup_type,
               tl.security_group_id,
               tl.view_application_id,
               tl.description,
               tl.meaning
        FROM   fnd_lookup_types_tl tl, fnd_lookup_types ltype
       WHERE       ltype.lookup_type = 'XX_COUNTRY'
               AND ltype.lookup_type = tl.lookup_type
               AND language = 'US';

   CURSOR get_country
   IS
      SELECT   UPPER (country_name) country FROM xx_country;


   l_rowid   VARCHAR2 (100) := 0;
BEGIN
   FOR i IN get_lookup_details
   LOOP
      FOR j IN get_country
      LOOP
         l_rowid := NULL;

         BEGIN
            fnd_lookup_values_pkg.insert_row (
               x_rowid                 => l_rowid,
               x_lookup_type           => i.lookup_type,
               x_security_group_id     => i.security_group_id,
               x_view_application_id   => i.view_application_id,
               x_lookup_code           => j.country,
               x_tag                   => NULL,
               x_attribute_category    => NULL,
               x_attribute1            => NULL,
               x_attribute2            => NULL,
               x_attribute3            => NULL,
               x_attribute4            => NULL,
               x_enabled_flag          => 'Y',
               x_start_date_active     => TO_DATE ('01-JAN-1950',
                                                   'DD-MON-YYYY'),
               x_end_date_active       => NULL,
               x_territory_code        => NULL,
               x_attribute5            => NULL,
               x_attribute6            => NULL,
               x_attribute7            => NULL,
               x_attribute8            => NULL,
               x_attribute9            => NULL,
               x_attribute10           => NULL,
               x_attribute11           => NULL,
               x_attribute12           => NULL,
               x_attribute13           => NULL,
               x_attribute14           => NULL,
               x_attribute15           => NULL,
               x_meaning               => j.country,
               x_description           => NULL,
               x_creation_date         => SYSDATE,
               x_created_by            => i.created_by,
               x_last_update_date      => i.last_update_date,
               x_last_updated_by       => i.last_updated_by,
               x_last_update_login     => i.last_update_login
            );
            COMMIT;
            DBMS_OUTPUT.put_line (j.country || ' has been loaded');
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);
         END;
      END LOOP;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;

Thursday, July 07, 2016

Explosion of BOM - bompxinq

I got a Requirement for WIP JOBS with comparison of Their BOM's.

So to Explode a BOM Please use below API.




declare
v_group_id NUMBER;
x_error_message VARCHAR2 (2000);
x_error_code NUMBER;

begin
SELECT bom_explosion_temp_s.NEXTVAL
INTO v_group_id
FROM DUAL;

bompxinq.exploder_userexit ( verify_flag       => 0,
                             org_id            => 249,
                             order_by          => 1,
                             grp_id            => v_group_id,
                             session_id        => 0,
                             levels_to_explode => 20,
                             bom_or_eng        => 1,
                             impl_flag         => 1,
                             plan_factor_flag  => 2,
                             explode_option    => 2,
                             module            => 2,
                             cst_type_id       => 1,
                             std_comp_flag     => 0,
                             expl_qty          => 1,
                             item_id           => 206818,
                             unit_number_from  => NULL,
                             unit_number_to    => NULL,
                             alt_desg          => '',
                             comp_code         => '',
                             rev_date          => sysdate,
                             show_rev          => 2,
                             material_ctrl     => 2,
                             lead_time         => 2,
                             err_msg           => x_error_message,
                             error_code        => x_error_code
                           );
                           

dbms_output.put_line(v_group_id);
commit;
END; 

Tuesday, June 21, 2016

Query to find Parameters and Value Sets associated with a Concurrent Program

SELECT
        fcpl.user_concurrent_program_name "Concurrent Program Name",
        fcp.concurrent_program_name "Short Name",
        fcp.EXECUTION_METHOD_CODE,
        fdfcuv.column_seq_num "Column Seq Number",
        fdfcuv.end_user_column_name "Parameter Name",
        fdfcuv.form_left_prompt "Prompt",
        fdfcuv.enabled_flag " Enabled Flag",
        fdfcuv.required_flag "Required Flag",
        fdfcuv.display_flag "Display Flag",
        fdfcuv.flex_value_set_id "Value Set Id",
        ffvs.flex_value_set_name "Value Set Name",
        flv.meaning "Default Type",
        fdfcuv.DEFAULT_VALUE "Default Value"
FROM
        fnd_concurrent_programs fcp,
        fnd_concurrent_programs_tl fcpl,
        fnd_descr_flex_col_usage_vl fdfcuv,
        fnd_flex_value_sets ffvs,
        fnd_lookup_values flv
WHERE
        fcp.concurrent_program_id = fcpl.concurrent_program_id
       -- AND    fcpl.user_concurrent_program_name = :conc_prg_name
        AND    fdfcuv.descriptive_flexfield_name = '$SRS$.'
                 || fcp.concurrent_program_name
                 and ffvs.flex_value_set_name = 'FND_STANDARD_DATE'
                 and fcp.concurrent_program_name like 'XXSYK%'
                 and fcp.EXECUTION_METHOD_CODE ='H'
        AND    ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
        AND    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
        AND    flv.lookup_code(+) = fdfcuv.default_type
        AND    fcpl.LANGUAGE = USERENV ('LANG')
        AND    flv.LANGUAGE(+) = USERENV ('LANG')

Thursday, June 02, 2016

Query to Find Org id based on Responsibility id

SELECT   pers.organization_id
    FROM apps.fnd_profile_options_vl fpo,
         apps.fnd_profile_option_values fpov,
         apps.fnd_responsibility_vl frv,
         apps.per_security_organizations_v pers
   WHERE frv.responsibility_id = :resp_id
     AND fpov.level_value = frv.responsibility_id
     AND fpo.profile_option_id = fpov.profile_option_id
     AND fpo.user_profile_option_name = 'MO: Security Profile'
     AND fpov.profile_option_id = fpo.profile_option_id
     AND fpov.profile_option_value = pers.security_profile_id
ORDER BY frv.responsibility_name;







SELECT frv.responsibility_name, fpov.profile_option_value org_id, hou.NAME
FROM apps.hr_organization_units hou,
apps.fnd_profile_options_vl fpo,
apps.fnd_profile_option_values fpov,
apps.fnd_responsibility_vl frv
WHERE frv.responsibility_name = :p_resp_name
AND fpov.level_value = frv.responsibility_id
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.user_profile_option_name = 'MO: Operating Unit'
AND fpov.profile_option_id = fpo.profile_option_id
AND hou.organization_id = TO_NUMBER (fpov.profile_option_value)
ORDER BY frv.responsibility_name

Monday, May 02, 2016

Creating Global Temporary Table

DROP TABLE xxx.xxx_fg_lot_det_gt CASCADE CONSTRAINTS;

CREATE GLOBAL TEMPORARY TABLE xxx.xxx_fg_lot_det_gt
(
  ORGANIZATION_CODE                  VARCHAR2(10 BYTE),
  ITEM_NUMBER                        VARCHAR2(50 BYTE),
  DESCRIPTION                        VARCHAR2(240 BYTE),
  GTIN                               VARCHAR2(50 BYTE),
  LOT_NUMBER                         VARCHAR2(50 BYTE),
  INVENTORY_ITEM_STATUS_CODE         VARCHAR2(80 BYTE),
  DOM                                VARCHAR2(50 BYTE),
  SHELF_LIFE_DAYS                    NUMBER,
  EXPIRATION_DATE                    DATE,
  CONFIGURATION                      VARCHAR2(10 BYTE),
  COUNTRY_OF_ORIGIN                  VARCHAR2(50 BYTE),
  MANUFACTURIN_FACILITY              VARCHAR2(10 BYTE),
  LOT_STATUS                         VARCHAR2(50 BYTE),
  ONHAND_QTY                         NUMBER,
  LOCATION                           VARCHAR2(80 BYTE),
  RECEIPT_QTY                        NUMBER
)
ON COMMIT PRESERVE ROWS
NOCACHE;


DROP SYNONYM APPS.xxx_fg_lot_det_gt;

CREATE SYNONYM APPS.xxx_fg_lot_det_gt FOR xxx.xxx_fg_lot_det_gt;