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; 

Clear BNE Cache for WebADI Changes

It Sometime happens that WebAdi Changes doesn't reflect once migrated in controlled instances. Here are the quick steps(Generally perfor...