Oracle Apps Query : Query To get item details

 

select segment1, description,  item_type,inventory_item_status_code,

INVENTORY_ITEM_FLAG,STOCK_ENABLED_FLAG,LOT_CONTROL_CODE, LOCATION_CONTROL_CODE,

LOT_DIVISIBLE_FLAG, LOT_SPLIT_ENABLED, BOM_ENABLED_FLAG, COST_OF_SALES_ACCOUNT,

PURCHASING_item_FLAG, EXPENSE_ACCOUNT, NULL MAKE_OR_BUY

, MIN_MINMAX_QUANTITY, MINIMUM_ORDER_QUANTITY, MAX_MINMAX_QUANTITY, MAXIMUM_ORDER_QUANTITY, BUILD_IN_WIP_FLAG,CUSTOMER_ORDER_FLAG,

 INTERNAL_ORDER_FLAG,INVOICEABLE_ITEM_FLAG ,SALES_ACCOUNT,

       -- category_set_name,

        max(Inventory) Inventory ,

        max(Product_Family) Product_Family,

        max(MRP) MRP,

        max(CST) CST,

        max(PUR) PUR,

        max(PRAMAC_PRODUCT_LINE) PRAMAC_PRODUCT_LINE,

        max(QUALITY) QUALITY,

        max(SALES) SALES,

        max(INTRASTAT_Classification)  INTRASTAT_Classification ,

        max(Sales_Category_Subcategory ) Sales_Category_Subcategory,

        max(PRAMAC_CLASS_MAPPING ) PRAMAC_CLASS_MAPPING,

        max(MANUFACTURED_IN )MANUFACTURED_IN ,

        max(LEAN) LEAN,

        max(R&D_MAINTENANCE_BOM) R&D_MAINTENANCE_BOM,

        max(Product_Categories) Product_Categories,

        max(Sequence_of_Events)Sequence_of_Events,

        max(Product_Family1) Product_Family1

from 

(

SELECT distinct null source, A.ORGANIZATION_ID, a.segment1, a.description,  NULL long_description, a.PRIMARY_UNIT_OF_MEASURE, null templates,

a.item_type,inventory_item_status_code,A.INVENTORY_ITEM_FLAG,A.STOCK_ENABLED_FLAG,LOT_CONTROL_CODE, LOCATION_CONTROL_CODE,

LOT_DIVISIBLE_FLAG, LOT_SPLIT_ENABLED, BOM_ENABLED_FLAG, COST_OF_SALES_ACCOUNT,

PURCHASING_item_FLAG, EXPENSE_ACCOUNT, NULL MAKE_OR_BUY

, MIN_MINMAX_QUANTITY, MINIMUM_ORDER_QUANTITY, MAX_MINMAX_QUANTITY, MAXIMUM_ORDER_QUANTITY, BUILD_IN_WIP_FLAG,CUSTOMER_ORDER_FLAG,

 INTERNAL_ORDER_FLAG,INVOICEABLE_ITEM_FLAG ,SALES_ACCOUNT

        ,b.category_set_name,a.inventory_item_id,

        decode ( b.category_set_name,'Inventory',b.SEGMENT1) Inventory,

        decode ( b.category_set_name,'Product Family',b.SEGMENT1) Product_Family,

        decode ( b.category_set_name,'MRP',b.SEGMENT1) as "MRP",

        decode ( b.category_set_name,'CST',b.SEGMENT1) as "CST",

        decode ( b.category_set_name,'PUR',b.SEGMENT1) as "PUR",

        decode ( b.category_set_name,'PRAMAC PRODUCT LINE',b.SEGMENT1) as "PRAMAC_PRODUCT_LINE",

        decode ( b.category_set_name,'QUALITY',b.SEGMENT1) as "QUALITY",

        decode ( b.category_set_name,'SALES',b.SEGMENT1) as "SALES",

        decode ( b.category_set_name,'INTRASTAT Classification',b.SEGMENT1) INTRASTAT_Classification,

        decode ( b.category_set_name,'Sales Category & Subcategory',b.SEGMENT1) Sales_Category_Subcategory,

        decode ( b.category_set_name,'PRAMAC_CLASS_MAPPING',b.SEGMENT1) PRAMAC_CLASS_MAPPING,

        decode ( b.category_set_name,'MANUFACTURED IN',b.SEGMENT1) MANUFACTURED_IN,

        decode ( b.category_set_name,'LEAN',b.SEGMENT1) LEAN,

        decode ( b.category_set_name,'R&D MAINTENANCE BOM',b.SEGMENT1) R&D_MAINTENANCE_BOM,

        decode ( b.category_set_name,'Product Categories',b.SEGMENT1) Product_Categories,

        decode ( b.category_set_name,'Sequence of Events',b.SEGMENT1) Sequence_of_Events,

        decode ( b.category_set_name,'Product Family',b.SEGMENT2) Product_Family1        

  FROM mtl_system_items_b a, mtl_item_categories_v b

 WHERE 1 = 1

   AND a.inventory_item_status_code = 'Active'

   AND a.inventory_item_id = b.inventory_item_id

   AND a.organization_id = b.organization_id

   AND a.organization_id = 11

   ) xx

group by segment1, description, xx.inventory_item_id, item_type,inventory_item_status_code,

INVENTORY_ITEM_FLAG,STOCK_ENABLED_FLAG,LOT_CONTROL_CODE, LOCATION_CONTROL_CODE,

LOT_DIVISIBLE_FLAG, LOT_SPLIT_ENABLED, BOM_ENABLED_FLAG, COST_OF_SALES_ACCOUNT,

PURCHASING_item_FLAG, EXPENSE_ACCOUNT, MAKE_OR_BUY

, MIN_MINMAX_QUANTITY, MINIMUM_ORDER_QUANTITY, MAX_MINMAX_QUANTITY, MAXIMUM_ORDER_QUANTITY, BUILD_IN_WIP_FLAG,CUSTOMER_ORDER_FLAG,

 INTERNAL_ORDER_FLAG,INVOICEABLE_ITEM_FLAG ,SALES_ACCOUNT

OAF : How to add Attachment Link in OAF Page through Personlization : Oracle APPS

How to create Attachment link through OAF Personalization

 

OAF Page for Customization = /oracle/apps/per/selfservice/extrainfotype/webui/EitPG

 

1)      Define Entity

a)      Go to Applciation Developer

a)      Enter Entity Data

Table := XX_PER_ARREAR_UPLOAD_STG

Entity Id := PERSON_ID

Entity Name := PERSON_ID

Prompt := PERSON_ID

Application := Human Resources

 

1)     2)   OAF Personalization to create Attachment Link

a)      a) Add Attachment Link Attribute

b) Entity mapping

c) Entity Primary Key mapping with VO Attribute

d) Fields added as shown below

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

Below Steps need to perform at Database

 

1)

CREATE TABLE XX_PER_ARREAR_UPLOAD_STG

(PERSON_ID NUMBER,

MEDIA_ID VARCHAR2(100),

FILE_NAME VARCHAR2(240),

message varcha2(2000)

);

6) 2) Create Procedures :- XX_PER_ARREAR_FILE_UPLOAD

3) 3) Check correct oracle directory and define in procedure 

8) 4) Run the Scripts to upload file

dPProcedure :- XX_PER_ARREAR_FILE_UPLOAD

CREATE OR REPLACE PROCEDURE XX_PER_ARREAR_FILE_UPLOAD

(P_EMP_NO VARCHAR2)

IS

 l_rowid                  ROWID;

   l_attached_document_id   NUMBER;

   l_document_id            NUMBER;

   l_media_id               NUMBER;

   l_category_id            NUMBER;

   l_pk1_value              fnd_attached_documents.pk1_value%TYPE ;

----<Primary Key information that uniquely identifies the product (such as the product_ID)>;

   l_description            fnd_documents_tl.description%TYPE := 'Arrear Attachment';

   l_filename               VARCHAR2 (240)                    := p_emp_no||'.xls';

   --l_file_path              VARCHAR2 (240)                          := 'test';

             ---'SALE_INVOICE_PATH'; --Server Directory Path for upload files

   l_seq_num                NUMBER;

   l_blob_data              BLOB;

   l_blob                   BLOB;

   l_bfile                  BFILE;

   l_byte                   NUMBER;

   l_fnd_user_id            NUMBER;

   l_short_datatype_id      NUMBER;

   x_blob                   BLOB;   

   fils                     BFILE;

   blob_length              INTEGER;

   l_entity_name            VARCHAR2 (100)                    := 'PERSON_ID';

   l_oracle_directory varchar2(100):= 'ECX_UTL_LOG_DIR_OBJ';

   l_content_type varchar2(100):= 'application/vnd.ms-excel';

                                 --application/vnd.ms-excel--application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';

                                                           --- <entity_name>;

   l_category_name          VARCHAR2 (100)                      := 'Miscellaneous';--XX_ARREAR_FILE_UPLOAD';

                                                           --<category_name>;

l_message varchar2(2000);                                                           

BEGIN

  bEGIN

  select DISTINCT PERSON_ID INTO l_pk1_value from per_all_people_f

   where employee_number =P_EMP_NO;

  END;

  insert into  XX_PER_ARREAR_UPLOAD_STG (person_id) values(l_pk1_value);

  

  --fnd_global.apps_initialize (<userid>, <applid>,<appluserid>);

   SELECT fnd_documents_s.NEXTVAL

     INTO l_document_id

     FROM DUAL;

   SELECT fnd_attached_documents_s.NEXTVAL

     INTO l_attached_document_id

     FROM DUAL;

   SELECT NVL (MAX (seq_num), 0) + 10

     INTO l_seq_num

     FROM fnd_attached_documents

    WHERE pk1_value = l_pk1_value AND entity_name = l_entity_name;

-- Select User_id

   SELECT user_id

     INTO l_fnd_user_id

     FROM apps.fnd_user

    WHERE user_name = 'TECHHARDS';                        -- <user_name>;

-- Get Data type id for Short Text types of attachments

   SELECT datatype_id

     INTO l_short_datatype_id

     FROM apps.fnd_document_datatypes

    WHERE NAME = 'FILE';

-- Select Category id for Attachments

   --FATEMP

   SELECT category_id

     INTO l_category_id

     FROM apps.fnd_document_categories_vl

    WHERE user_name = l_category_name;

-- Select nexvalues of document id, attached document id and

-- l_media_id

   SELECT apps.fnd_documents_s.NEXTVAL, apps.fnd_attached_documents_s.NEXTVAL

--apps.fnd_documents_long_text_s.NEXTVAL

   INTO   l_document_id, l_attached_document_id

--l_media_id

   FROM   DUAL;

   SELECT MAX (file_id) + 1

     INTO l_media_id

     FROM fnd_lobs;

   fils := BFILENAME (l_oracle_directory, l_filename);

   --dbms_output.put_line('fils:'||fils);

-- Obtain the size of the blob file

   DBMS_LOB.fileopen (fils, DBMS_LOB.file_readonly);

   blob_length := DBMS_LOB.getlength (fils);

   DBMS_LOB.fileclose (fils);

  

-- Insert a new record into the table containing the

-- filename you have specified and a LOB LOCATOR.

-- Return the LOB LOCATOR and assign it to x_blob.

   INSERT INTO fnd_lobs

               (file_id, file_name, file_content_type, upload_date,

                expiration_date, program_name, program_tag, file_data,

                LANGUAGE, oracle_charset, file_format

               )

        VALUES (l_media_id, l_filename, /*'text/plain',*/ l_content_type,

                                                        --'application/pdf',--

                                                                            SYSDATE,

                NULL, 'FNDATTCH', NULL, EMPTY_BLOB ()

                ,'US', /*'UTF8'*/

                                /* 'AR8MSWIN1256' */ 'WE8MSWIN1252',--'AR8ISO8859P6',

                                 'binary'

                                                                    /*'text'*/

               )

     RETURNING file_data

          INTO x_blob;

-- Load the file into the database as a BLOB

   DBMS_LOB.OPEN (fils, DBMS_LOB.lob_readonly);

   DBMS_LOB.OPEN (x_blob, DBMS_LOB.lob_readwrite);

   DBMS_LOB.loadfromfile (x_blob, fils, blob_length);

-- Close handles to blob and file

   DBMS_LOB.CLOSE (x_blob);

   DBMS_LOB.CLOSE (fils);

   DBMS_OUTPUT.put_line ('FND_LOBS File Id Created is ' || l_media_id);

   COMMIT;

-- This package allows user to share file across multiple orgs or restrict to single org

   fnd_documents_pkg.insert_row

      (x_rowid                  => l_rowid,

       x_document_id            => l_document_id,

       x_creation_date          => SYSDATE,

       x_created_by             => l_fnd_user_id,

                                               -- fnd_profile.value('USER_ID')

       x_last_update_date       => SYSDATE,

       x_last_updated_by        => l_fnd_user_id,

                                               -- fnd_profile.value('USER_ID')

       x_last_update_login      => -1,

       x_datatype_id            => l_short_datatype_id,                -- FILE

       x_security_id            => NULL,

          --<security ID defined in your Attchments, Usaully SOB ID/ORG_ID..>,

       x_publish_flag           => /*'N'*/ 'Y',

              --This flag allow the file to share across multiple organization

       x_category_id            => l_category_id,

       x_security_type          => /*1*/ 4,

       x_usage_type             => /*'S'*/ 'O',

       x_language               => 'US',

       x_description            => l_filename,           

       x_file_name              => l_filename,

       x_media_id               => l_media_id

      );

     COMMIT;

     fnd_attached_documents_pkg.insert_row

      (x_rowid                         => l_rowid,

       x_attached_document_id          => l_attached_document_id,

       x_document_id                   => l_document_id,

       x_creation_date                 => SYSDATE,

       x_created_by                    => l_fnd_user_id,

                                               --fnd_profile.VALUE('USER_ID'),

       x_last_update_date              => SYSDATE,

       x_last_updated_by               => l_fnd_user_id,

                                               --fnd_profile.VALUE('USER_ID'),

       x_last_update_login             => -1,

       x_seq_num                       => l_seq_num,

       x_entity_name                   => l_entity_name,

       x_column1                       => NULL,

       x_pk1_value                     => l_pk1_value,

       x_pk2_value                     => NULL,

       x_pk3_value                     => NULL,

       x_pk4_value                     => NULL,

       x_pk5_value                     => NULL,

       x_automatically_added_flag      => 'N',

       x_datatype_id                   => 6,

       x_category_id                   => l_category_id,

       x_security_type                 => /*1*/ 4,

       x_security_id                   => NULL,

          --<security ID defined in your Attchmentsssss, SOB ID/ORG_ID..>,

       x_publish_flag                  => 'Y',

       x_language                      => 'US',

       x_description                   => l_filename,         --l_description,

       x_file_name                     => l_filename,

       x_media_id                      => l_media_id

      );

      update XX_PER_ARREAR_UPLOAD_STG

   set media_id = l_media_id,

   file_name = l_filename,

   message='SUCCESSFUL'

   where person_id =l_pk1_value;    

   COMMIT;

   DBMS_OUTPUT.put_line ('MEDIA ID CREATED IS ' || l_media_id);

Exception when others then

l_message := SQLERRM;

update XX_PER_ARREAR_UPLOAD_STG

     set media_id = l_media_id,

     file_name = l_filename,message = l_message

     where person_id =l_pk1_value;  

    END;

--=========================================================================

   # OAF , # OAF Personalization , # Add Attachment Link through Personalization , # Oracle Apps , # EBS

SQL: How to Write Insert Statement using Rowtype

SQL: How to Write Insert Statement using Rowtype

  r_mtl_lot_rec := NULL;

               g_l_transaction_id := l_transaction_id;

               r_mtl_lot_rec.transaction_interface_id := l_transaction_id;

               r_mtl_lot_rec.source_code := p_transaction_type;

               r_mtl_lot_rec.source_line_id := l_transaction_id;

               r_mtl_lot_rec.last_update_date := SYSDATE;

               r_mtl_lot_rec.last_updated_by :=gv_user_id;

               r_mtl_lot_rec.creation_date := SYSDATE;

               r_mtl_lot_rec.created_by := gv_user_id;

               r_mtl_lot_rec.last_update_login := fnd_global.login_id;

               r_mtl_lot_rec.lot_number := p_lot_no;

               r_mtl_lot_rec.attribute1 := p_batch_no;

               r_mtl_lot_rec.lot_expiration_date := l_expiration_date;

               r_mtl_lot_rec.transaction_quantity := p_quantity;

               r_mtl_lot_rec.primary_quantity := p_quantity;

               r_mtl_lot_rec.serial_transaction_temp_id := l_transaction_id;

               DBMS_OUTPUT.put_line ('STEP5');

               INSERT INTO mtl_transaction_lots_interface

                    VALUES r_mtl_lot_rec;

 

WEBADI Tables and Query

 List of WEBADI Tables

1) Integrator

BNE_INTEGRATORS_B

BNE_INTEGRATORS_VL

BNE_INTEGRATORS_TL

Layout

BNE_LAYOUTS_B

BNE_LAYOUTS_VL

BNE_LAYOUTS_TL

Layout Columns

BNE_LAYOUT_COLS

BNE_LAYOUT_COLS_V

Mapping

BNE_MAPPINGS_B

BNE_MAPPINGS_TL

BNE_MAPPINGS_VL

Content

BNE_CONTENTS_B

BNE_CONTENTS_TL

BNE_CONTENTS_VL

Interface

BNE_INTERFACES_B

BNE_INTERFACES_TL

BNE_INTERFACES_VL

--------------SQL Query to get  View and PAckage Name used in integrator--------------

SELECT   xxbits..user_name web_adi, ba.attribute2 "PACKAGE", xxbct.user_name "VIEW"

    FROM bne_integrators_tl xxbits., bne_attributes ba, bne_contents_tl xxbct

   WHERE    SUBSTR (xxbits..integrator_code, 1, LENGTH (xxbits..integrator_code) - 5)

         || '_ATT' = ba.attribute_code

     AND xxbits..application_id = xxbct.application_id

     AND xxbits..LANGUAGE = 'US'

     AND xxbct.LANGUAGE = 'US'

     AND ba.attribute1 = 'PROCEDURE'

     AND xxbits..application_id = ba.application_id

     AND xxbct.content_code =

               SUBSTR (xxbits..integrator_code, 1,

                       LENGTH (xxbits..integrator_code) - 5)

            || '_CNT'

     AND xxbits..user_name LIKE 'XX%'

ORDER BY xxbits..user_name;

Word Meaning

• Conundrum -> Difficult Question
• Fleet -> Lasted for a short period of Time
• Enduring -> Last for a long period of time
• Apprise-> Inform or Tell(Someone)

OAF: Page Navigation (Oracle Apps)

How to navigate between pages in OAF

1) 

pageContext.setForwardURL("OA.jsp?page=/oracle/apps/fnd/framework/toolbox/tutorial/webui/SupplierPG",

null,

KEEP_MENU_CONTEXT,

null,

null,

true, // Retain AM

OAWebBeanConstants.ADD_BREAD_CRUMB_YES,

OAWebBeanConstants.IGNORE_MESSAGES);

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

#Oracle Apps , # OAF

OAF : Exception Types ( Oracle Apps)

Exception Types in OAF ( Oracle Application Framework )

 1)    catch(Exception ex)

        {

            throw new OAException(ex.getMessage(), (byte)0);

        }

2) throw new OAException("Path not selected. Please select the path first.", (byte)0);

3) OAException message = new OAException("File uploaded successfully.", (byte)3);

            pageContext.putDialogMessage(message);

4)  OAException oaexception2 = new OAException("SQLAP", "AP_PAY_PSR_APPLY_CONFIRM", null, (byte)3, null);

     oapagecontext.putDialogMessage(oaexception2);

5) catch(NullPointerException ex)

      {   System.out.println("you are in inside catch exception");

       throw new OAException("Please Select a File to Upload", OAException.ERROR);

      }

    

OAF : How to Retrieve DFF Value in OAF

 

super.processFormRequest(pageContext,webBean);

   OADescriptiveFlexBean oaDFF = null;

 //  OADescriptiveFlexBean oadescriptiveflexbean = null;

  DescriptiveFlexfield descriptiveflexfield =null;

   OAMessageTextInputBean addr1 = null;

 oaDFF = (OADescriptiveFlexBean)webBean.findChildRecursive("DetailFlex");//Get Source by going thorugh Inspectr Element right click//

               oaDFF.processFlex(pageContext);

   addr1 = (OAMessageTextInputBean)webBean.findChildRecursive("DetailFlex3");

OAF : How to Add days in input date

 OAF : How to Add days in input date

import java.text.SimpleDateFormat;

import java.util.Calendar;

import java.text.ParseException;

public class Example{

   public static void main(String args[]){

        String oldDate = "2017-01-29"; 

               System.out.println("Date before Addition: "+oldDate);

               SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

               Calendar c = Calendar.getInstance();

               try{

                  c.setTime(sdf.parse(oldDate));

               }catch(ParseException e){

                  e.printStackTrace();

                }

               //Incrementing the date by 1 day

               c.add(Calendar.DAY_OF_MONTH, 1); 

               String newDate = sdf.format(c.getTime()); 

               System.out.println("Date Incremented by One: "+newDate);

   }

}

OAF : How to Dynamically set Render/Read only properties in OAF Table

 

1)     1) Create transient Attribute as Boolean type in your VO

2)     2)  Place this syntax in a fields properties where dynamic is needed. ${oa.ReqUpdateVO1.IMEIIssueTextReadOnly}

3)     3) Write below code in Processrequest to enable or disable

OARow singleRow = (OARow)ReqUpdateVO.first();

g_req_status = (String)singleRow.getAttribute("ReqStatus");

singleRow.setAttribute("IMEIIssueTextReadOnly",Boolean.TRUE);

PLSQL: CONTEXT

 -------set context---

EXEC XX_OM_CTX_SET_PKG.pr_set_sun_ctx('P_CUST_GRP_ACC_NUM','415');

EXEC XX_OM_CTX_SET_PKG.pr_set_sun_ctx('P_CUST_ACC_NUM','430');

----retrive context value---------

SELECT SYS_CONTEXT('XX_OM_CTX_CON','P_CUSTOMER_ID')

FROM   dual;

---how to create syntax

CREATE OR REPLACE CONTEXT XX_OM_CTX_CON

 USING APPS.XX_OM_CTX_SET_PKG;

CREATE OR REPLACE PACKAGE BODY APPS.xx_om_ctx_set_pkg

AS

   PROCEDURE pr_set_sun_ctx (p_attrib varchar2, p_value varchar2)

   IS

   BEGIN

      DBMS_SESSION.set_context ('XX_OM_CTX_CON', p_attrib, p_value);

   END pr_set_sun_ctx;

   PROCEDURE pr_clear_sun_ctx (p_attrib varchar2)

   IS

   BEGIN

      DBMS_SESSION.clear_context ('XX_OM_CTX_CON', p_attrib);

   END pr_clear_sun_ctx;

END xx_om_ctx_set_pkg;

/