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;

/

FND: Profile Option Value updation using API

 Declare

Result Boolean;

BEGIN

   Result:= fnd_profile.SAVE ('XX_OTC_ROLE_PF' , null, 'USER',1677);

   IF Result

   THEN

      DBMS_OUTPUT.put_line ('Profile Updated');

   ELSE

      DBMS_OUTPUT.put_line ('Profile Not Updated');

   END IF;

   COMMIT;

END;

EBS: Sales Order Lines Update API in Oracle Apps

--++---------------------------------------------------------

-- Api to Update Sales Order SHIP To

--++---------------------------------------------------------

BEGIN
   l_return_status := NULL;
   l_error_msg := NULL;
   l_line_tbl_index := 1;
   l_header_rec := oe_order_pub.g_miss_header_rec;
   l_header_rec.header_id := p_order_header_id;
   l_header_rec.operation := oe_globals.g_opr_update;
   l_line_tbl (l_line_tbl_index) := oe_order_pub.g_miss_line_rec;
   l_line_tbl (l_line_tbl_index).operation := oe_globals.g_opr_update;
   l_line_tbl (l_line_tbl_index).line_id := p_new_line_id;
   l_line_tbl (l_line_tbl_index).ship_to_org_id := p_ship_to_id;

   BEGIN
      ---------- CALL TO Standard PROCESS ORDER API to Split Order Lines--------------
      oe_order_pub.process_order
                       (p_api_version_number          => 1.0,
                        p_init_msg_list               => fnd_api.g_true,
                        p_return_values               => fnd_api.g_true,
                        p_action_commit               => fnd_api.g_true,
                        x_return_status               => l_return_status,
                        x_msg_count                   => l_msg_count,
                        x_msg_data                    => l_msg_data,
                        p_header_rec                  => l_header_rec,
                        p_line_tbl                    => l_line_tbl,
                        p_action_request_tbl          => l_action_request_tbl
                                                                             -- OUT PARAMETERS
      ,
                        x_header_rec                  => x_header_rec,
                        x_header_val_rec              => x_header_val_rec,
                        x_header_adj_tbl              => x_header_adj_tbl,
                        x_header_adj_val_tbl          => x_header_adj_val_tbl,
                        x_header_price_att_tbl        => x_header_price_att_tbl,
                        x_header_adj_att_tbl          => x_header_adj_att_tbl,
                        x_header_adj_assoc_tbl        => x_header_adj_assoc_tbl,
                        x_header_scredit_tbl          => x_header_scredit_tbl,
                        x_header_scredit_val_tbl      => x_header_scredit_val_tbl,
                        x_line_tbl                    => x_line_tbl,
                        x_line_val_tbl                => x_line_val_tbl,
                        x_line_adj_tbl                => x_line_adj_tbl,
                        x_line_adj_val_tbl            => x_line_adj_val_tbl,
                        x_line_price_att_tbl          => x_line_price_att_tbl,
                        x_line_adj_att_tbl            => x_line_adj_att_tbl,
                        x_line_adj_assoc_tbl          => x_line_adj_assoc_tbl,
                        x_line_scredit_tbl            => x_line_scredit_tbl,
                        x_line_scredit_val_tbl        => x_line_scredit_val_tbl,
                        x_lot_serial_tbl              => x_lot_serial_tbl,
                        x_lot_serial_val_tbl          => x_lot_serial_val_tbl,
                        x_action_request_tbl          => x_action_request_tbl
                       );
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line
                           (   'Error calling  oe_order_pub.Process_order  :'
                            || l_msg_data
                           );
   END;

   IF l_return_status = fnd_api.g_ret_sts_success
   THEN
      p_return_status := fnd_api.g_ret_sts_success;
      l_error_msg :=
            'Order Line updated Successfully :'
         || x_line_tbl.COUNT
         || ','
         || p_return_status;
      DBMS_OUTPUT.put_line (l_error_msg);
   ELSE
      l_error_msg := 'Order Line updation Failed  :';
      DBMS_OUTPUT.put_line (l_error_msg);

      FOR i IN 1 .. l_msg_count
      LOOP
         oe_msg_pub.get (p_msg_index          => i,
                         p_encoded            => fnd_api.g_false,
                         p_data               => l_msg_data,
                         p_msg_index_out      => l_msg_index_out
                        );
         l_error_msg := CHR (10) || l_error_msg || CHR (10) || ','
                        || l_msg_data;
         DBMS_OUTPUT.put_line (l_error_msg);
      END LOOP;
   END IF;
END;

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

#Sales Order #OM #API #Oracle Apps #EBS #ONT #r12

Sales Order Table

oe_order_headers_all

oe_order_lines_all