Tuesday, May 28, 2013

To Download file from Server in OAF

/**
* @param pageContext the current OA page context
* @param file_name_with_path - this is fully qualified file name with its path on unix application
* server. eg "/xxcrp/xxapplcrp/mukul/abc.pdf"
* @param file_name_with_ext - this is file name with extension, you wanna display user
* for download. eg- i wanna display the abc.pdf file download with name five_point_someone.pdf
* then I can pass this as "five_point_someone.pdf"
*/
public void downloadFileFromServer(OAPageContext pageContext,
                                  String file_name_with_path,
                                  String file_name_with_ext) {
  HttpServletResponse response = (HttpServletResponse) pageContext.getRenderingContext().getServletResponse();
  if (((file_name_with_path == null) || ("".equals(file_name_with_path)))){
    throw new OAException("File path is invalid.");
  }

  File fileToDownload = null;
  try{
    fileToDownload = new File(file_name_with_path);
  }catch (Exception e){
    throw new OAException("Invalid File Path or file does not exist.");
  }

  if (!fileToDownload.exists()){
    throw new OAException("File does not exist.");
  }

  if (!fileToDownload.canRead()){
    throw new OAException("Not Able to read the file.");
  }

  String fileType = "application/pdf";//getMimeType(file_name_with_ext);
  response.setContentType(fileType);
  response.setContentLength((int)fileToDownload.length());
  response.setHeader("Content-Disposition", "attachment; filename=\"" + file_name_with_ext + "\"");

  InputStream in = null;
  ServletOutputStream outs = null;

  try{
    outs = response.getOutputStream();
    in = new BufferedInputStream(new FileInputStream(fileToDownload));
    int ch;
    while ((ch = in.read()) != -1){
      outs.write(ch);
    }
  }catch (IOException e){
    // TODO
    e.printStackTrace();
  }finally{
    try{
      outs.flush();
      outs.close();
      if (in != null){
        in.close();
      }
    }catch (Exception e){
      e.printStackTrace();
    }
  }
}
Source:http://mukx.blogspot.in/search/label/OAF

Set Style Sheet Properties Programatically in OAF

In ProcessRequest()
         
import oracle.cabo.style.CSSStyle;

CSSStyle customCss = new CSSStyle();
customCss.setProperty("text-transform","uppercase");
customCss.setProperty("color", "    #ee0000");//# -red
OAMessageStyledTextBean styledTextBean =(OAMessageStyledTextBean)webBean.findIndexedChildRecursive("POCommentsItem");
if(styledTextBean!=null)
{
  styledTextBean.setInlineStyle(customCss);
}

Monday, May 27, 2013

What is new in R12 compare with 11i

1.Ledgers and Ledger Sets:
The ledger is a new fundamental concept in Release 12.  The ledger replaces the 11i concept of a set of books.  It represents an accounting representation for one or more legal entities or for a business need such as consolidation or management reporting.

11i & Prior = Sets of Books (3 C’s)
    Chart of Accounts
    Accounting Calendar
    Currency

R12 = Ledgers (4 C’s)
    Chart of accounts
    Ledger currency
    Accounting calendar
    Accounting method – new 4th

4th C in R12:
In 11i the subledger accounting method (cash/accrual) was used to be defined at the subledger setup (AP/AR). However in R12 things have changed. You define the subledger accounting method at the ledger level. All subledgers tied to that ledger will use that particular accounting convention. So there is a difference in 11i and R12. The accounting "Convention" is now married to the ledger and hence the term 4Cs.

If you are not planning to use subledgers then you do not have to choose a value in the accounting method field. You can leave it blank. Your GL functionality will remain unaffected. However you will have to specify an accounting method at the ledger setup, before you could use a subledger.

Primary Ledger:
    The main “Activity” Ledger
    Usually in the local currency
    For Operational reporting
Secondary Ledger:
    Differs from Primary Ledger by Chart of Account, Calendar, and/or Accounting Method
    For Statutory, Tax or Consolidated reporting
Reporting Currency Ledger:
    Differs from Primary Ledger by Currency ONLY
    Just a translation of the Primary Ledger – no rules required
    For Consolidated reporting

LEDGER SETS:
    Grouping of ledgers with the same chart of accounts and calendar/period type combination. Essentially treats multiple ledgers as one

2.Multi-Org Access Control(MOAC):
'Multi-Org Access Control' popularly known as 'MOAC' in short form is a enhanced feature in Release 12. MOAC will enable users to access secured data in one or more Operating Units from a single responsibility.

End-Users can access/transact data within several operating units based on Security Profile attached to a responsibility. i.e. End-Users can access/transact data on multiple Operating units by accessing one operating unit at a time without changing a responsibility. This Provides flexibility for end-users to work conveniently with multiple Operating Units in shared service Environments with single responsibility.

Profile Options which take major Role in MOAC
MO: Security Profile
MO: Default Operating Unit(Optional)
MO: Operating Unit(Mandatory for only Single Org or if MO: Security Profile is not defined)

Technically:
In 11i, data was restricted to a single operating unit using views which partition the base table data based on the OU setting controlled by Profile Options.
1.Based out of base tables (generally named with '_ALL') contained data for all operating units.
2.Each such table contained a column named ORG_ID to indicate what operating unit a particular row belonged to.
3.Data was then restricted by using restricted views (without _ALL table) which would only return rows which corresponded to the current operating unit's organization ID.
4.The current OU was stored in the first 10 characters of the database Application Context variable CLIENT_INFO.
5.When logging into the applications, the CLIENT_INFO value was set to the appropriate operating unit organization ID for the session based on the profile option setting for 'MO: Operating Unit'.
6.In SQL*Plus, CLIENT_INFO could be set to point to a particular operating unit using
  execute dbms_application_info.set_client_info('');

Ex: ap_invoices_all- is base table with org_id column
    ap_invoices - is a view on base table where org_id = SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10)
 
In release 12 a more flexible architecture allows users to define security profiles so that users may access data for more than one operating unit within a single responsibility.

1. Oracle Multi-org views have been removed, and replaced with synonyms. Thats means in R12 table with out _ALL is no longer be a view defined on base table as Oracle have used the convention earlier, but provided a synonym which points to _ALL table.
2.The data is restriction by assigning a virtual private database (VPD) policy to the synonym. This policy allows the system to dynamically generate restricting conditions when queries are run against the synonym.
3.Data relating to defined VPD policies is viewable in the data dictionary view DBA_POLICIES.

Ex: ap_invoices_all- is base table with org_id column
    ap_invoices - is a APPS synonym of base table where org_id = sys_context('multi_org2',current_org_id)

3.Subledger Accounting(SLA):
You can consider SLA as a bridge or an Intermediate platform that talks to Subledger products (these are other applications or modules) and the General ledger. All Accounting entries for your modules (like AP, AR, Projects, Inventory, etc) are treated as Sub-Ledgers and they first sent to the SLA engine. The SLA applies its rules (some or these rules are pre-configured and also you can configure as many rules as you want) and then sends the necessary journal entries to the General ledger.

In a nutshell, the following services are provided by Oracle SLA
    Rule based Generation and  storing of accounting entries
    Storing subledger balances
    Subledger or SLA accounting entries
    Subledger reporting (some examples could be Open Account Balances Listing and Subledger Journal Reports, etc )

4.Tax Engine
It Centrally manage tax transactions across entire E-Business Suite.

  •     Single Repository of transactions for global business insight
  •     Centralized rules applied to transactions to manage globally and reduce risk
  •     Automation of tax processes on transactions to improve operational efficiency
  •     Improved Reporting
  •     Effective Date Setup
  •     Extensible architecture that supports additions, e.g. Self-assessed Use Tax

5.Actual Shipment Date for Pick Release:
Now in R12 onward Oracle has introduced feature to allow user enter Actual Shipment Date.

This feature was very well required as in many industries they do the actual Shipping in Date D1 and then process the Shipping data in Oracle system on date D2. In Oracle System it always take the system data as the shipping date, thus result in discrepancies, Also many a time organization uses 3rd party system for Pick Pack and ship and then bring back the data into Oracle Shipping for Invoicing and other calculation , but again Oracle by default put the System data as shipping date and it create problems in managing the shipment activities and revenue recognition.

With this enhancement, Oracle allow user to enter the “Actual Departure Date” in

  • Sales Order Picking
  • Ship confirm SRS Parameter Window
  • Pick release SRS Parameter Window

Thursday, May 23, 2013

ADF:Application URL or Context-Root

When you start running ADF applications under JDev 11g you'll become familiar with seeing URLs like the following in your browser:

http://127.0.0.1:7101/Sage-ViewController-context-root/faces/Welcome.jspx

Each part of the URL is significant, though the one that stands out as being odd is the rather cumbersome "Sage-ViewController-context-root". This part of the URL in Java EE parlance is called the context-root, and not just because our example literally has context-root written into it.

The context-root at its most basic form is the unique part of the URL on the application server to clearly distinguish your application from another on the server. However as you can see in this example the "Sage-ViewController-context-root" is hardly something you'd want to present to users. Something more palatable like "Sage" or "HR" would be useful, delivering an end URL like:

http://127.0.0.1:7101/Sage/faces/Welcome.jspx

So how do we go about making a friendly context-root for our applications? Essentially there's 2 locations.

Design time
If you're running your application from JDeveloper by opening & running a jspx page via the right mouse menu or similar, when the application is deployed to the integrated WLS server, it will use Java EE Application options under the ViewController's Project Properties, accessible by right clicking the same name option of the ViewController project:

As you can see in the above picture, the Java EE Web Context Root is set to "Sage-ViewController-context-root". Simply change this to something desired.

Deploy time
Once you've finished testing and running your application in your local integrated WLS instance, you're likely to generate an EAR or WAR file to deploy to an external WLS server. In this case the context-root is influenced by settings in the WAR file for the ViewController project. Simply right click the ViewController project -> select Project Properties -> Deployment and locate the associated WAR file deployment profile. In the following picture you can see it's called "Sage_ViewController_webapp1":

If you then select the edit button for the WAR deployment profile, the first node General includes 2 options, namely Use Project's Java EE Web Context Root and Specify Java EE Web Context Root. If you select the 2nd option and enter your own option, this will override the context-root once the application is deployed to an external WLS server:


Source: http://one-size-doesnt-fit-all.blogspot.in/2010/12/adf-changing-your-applications-url-or.html

Wednesday, May 22, 2013

Script to pick release the orders which are in B, R status


/***************************************************************************************
 * TYPE            : Script                                                            *
 * NAME            : PICK_RELESE.sql                                   *
 * PURPOSE         : The script will pick release the orders which are in B, R status  *
 *                                                                                     *
 * Author    Date          Ver    Description                                          *
 * --------  -----------  ----   -------------------------- ---------------------------*
 * XXXXXXX   20-Feb-2020    1.00   Created                                             *
 ***************************************************************************************/

SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON SIZE 100000;
ALTER SESSION SET current_schema=apps;


DECLARE
    CURSOR CUR_GET_DATA IS
        SELECT WDD.SOURCE_LINE_ID     P_LINE_ID,
                     WDD.INVENTORY_ITEM_ID  P_ITEM_ID,
                     WDD.DELIVERY_DETAIL_ID P_DELIVERY_DETAIL_ID,
                     WDD.BATCH_ID           P_BATCH_ID,
                     WDD.SOURCE_HEADER_ID   P_HEADER_ID,
                     WDD.MOVE_ORDER_LINE_ID P_MOVE_ORDER_LINE_ID
            FROM APPS.WSH_DELIVERY_DETAILS WDD,
                 OE_ORDER_HEADERS_ALL OOH
         WHERE WDD.SOURCE_HEADER_ID = OOH.HEADER_ID
         AND   ooh.org_id = 82
and wdd.organization_id = 331
         --AND ooh.header_id in (137508997)
                  and ooh.header_id in (135503888)        
             AND WDD.RELEASED_STATUS IN ('B','R');

    CURSOR CUR_OM_GET_ORDER_DATA(P_OE_HEADER_ID NUMBER) IS
        SELECT OEOHA.HEADER_ID,
                     OEOHA.ORDER_NUMBER,
                     OEOHA.ATTRIBUTE2 PB_ID,
                     OEOHA.SHIPPING_METHOD_CODE,
                     OEOHA.ORDER_TYPE_ID,
                     OEOHA.SOLD_TO_ORG_ID,
                     OEOHA.SHIP_FROM_ORG_ID,
                     OEOHA.SALES_CHANNEL_CODE,
                     OEOHA.ORDERED_DATE,
                     OEOHA.REQUEST_DATE,
                     OEOHA.ATTRIBUTE17 OPMAIN_WHSE_ID,
                     OTTA.ATTRIBUTE13 AUTOSHIP_CONFIRM
            FROM OE_ORDER_HEADERS_ALL OEOHA, OE_TRANSACTION_TYPES_ALL OTTA
         WHERE OEOHA.ORDER_TYPE_ID = OTTA.TRANSACTION_TYPE_ID
             AND OEOHA.ATTRIBUTE7 IS NOT NULL
             AND OEOHA.BOOKED_FLAG = 'Y'
             AND OEOHA.CANCELLED_FLAG != 'Y'
             AND OEOHA.HEADER_ID = P_OE_HEADER_ID;

    CURSOR CUR_GET_DELIVERIES(P_NUM_ORDER_HEADER_ID NUMBER, P_ORDER_LINE_ID NUMBER, P_INV_ITEM_ID NUMBER, P_DELIVERY_DET_ID NUMBER) IS
        SELECT WSHDV.SOURCE_LINE_NUMBER,
                     WSHDV.ITEM_DESCRIPTION,
                     WSHDV.DELIVERY_DETAIL_ID,
                     WSHDV.INVENTORY_ITEM_ID,
                     WSHDV.SOURCE_LINE_ID,
                     WSHDV.DELIVERY_ID,
                     WSHDV.REQUESTED_QUANTITY_UOM,
                     WSHDV.BATCH_ID,
                     WSHDV.ORIGINAL_SUBINVENTORY,
                     WSHDV.SRC_REQUESTED_QUANTITY ORDERED_QUANTITY,
                     WSHDV.REQUESTED_QUANTITY,
                     WSHDV.RELEASED_STATUS_NAME,
                     WSHDV.RELEASED_STATUS
            FROM WSH_DELIVERABLES_V WSHDV, OE_ORDER_LINES_ALL OEOLA
         WHERE OEOLA.LINE_ID = WSHDV.SOURCE_LINE_ID
             AND WSHDV.SOURCE_HEADER_ID = OEOLA.HEADER_ID
             AND OEOLA.HEADER_ID = P_NUM_ORDER_HEADER_ID
             AND OEOLA.LINE_ID = P_ORDER_LINE_ID
             AND WSHDV.RELEASED_STATUS IN ('B','R')
             AND WSHDV.INVENTORY_ITEM_ID = P_INV_ITEM_ID
             AND WSHDV.DELIVERY_DETAIL_ID = P_DELIVERY_DET_ID
         ORDER BY WSHDV.REQUESTED_QUANTITY;

    --   AND NVL(WSHDV.BATCH_ID, P_NUM_BATCH_ID) = P_NUM_BATCH_ID;
    CURSOR DELETE_RESERVATION_CUR(P_NUM_LINE_ID NUMBER) IS
        SELECT RESERVATION_ID
            FROM MTL_RESERVATIONS
         WHERE DEMAND_SOURCE_LINE_ID = P_NUM_LINE_ID;

       L_NUM_BATCH_ID          NUMBER;
       L_CHR_PL_RESULT         VARCHAR2(100);
       L_CHR_PL_PHASE          VARCHAR2(30);
       L_CHR_PL_SKIP           VARCHAR2(1) := 'N';
       L_CHR_DISTRIBUTOR_ID    VARCHAR2(1000);
       L_CHR_BATCH_NAME        WSH_PICKING_BATCHES.NAME%TYPE;
       L_NUM_HEADER_ID         NUMBER;
       L_NUM_SHIP_CONF_RULE_ID WSH_SHIP_CONFIRM_RULES.SHIP_CONFIRM_RULE_ID%TYPE;
       L_NUM_USER_ID           NUMBER;
       L_NUM_RESP_ID           NUMBER;
       L_NUM_RESP_APPL_ID      NUMBER;
       L_CHR_TRACKING          VARCHAR2(500);
       L_CHR_BOX               VARCHAR2(500);
       L_DT_SHIP               DATE;
       L_CHR_SHIPWT            VARCHAR2(150);
       L_ORIGINAL_RSV_REC      INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
       L_TBL_SERIAL_NUM1       INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
       L_NUM_REQUEST_ID        NUMBER;
       L_RETURN_STATUS         VARCHAR2(2000);
       L_MSG_DATA              VARCHAR2(2000);
       L_MSG_COUNT             NUMBER;
       LN_PICK_FROM_LOCATOR_ID NUMBER;
       P_PICK_LOC_ACT          VARCHAR2(500);
       LC_SUBINV_CODE          VARCHAR2(500);
BEGIN
        L_NUM_USER_ID := 9009;
    L_NUM_RESP_ID      := 61563;
    L_NUM_RESP_APPL_ID := 20044;
   

    FND_GLOBAL.APPS_INITIALIZE(USER_ID      => L_NUM_USER_ID,
                   RESP_ID      => L_NUM_RESP_ID,
                   RESP_APPL_ID => L_NUM_RESP_APPL_ID);

    FOR A IN CUR_GET_DATA LOOP
        FOR REC_OM_GET_ORDER_DATA IN CUR_OM_GET_ORDER_DATA(A.P_HEADER_ID) LOOP
            L_NUM_HEADER_ID := REC_OM_GET_ORDER_DATA.HEADER_ID;
       
            ---CURSOR CUR_GET_DELIVERIES(P_NUM_ORDER_HEADER_ID NUMBER, P_ORDER_LINE_ID NUMBER,P_INV_ITEM_ID NUMBER,P_DELIVERY_DET_ID NUMBER) IS
            FOR J IN CUR_GET_DELIVERIES(REC_OM_GET_ORDER_DATA.HEADER_ID,
                            A.P_LINE_ID,
                            A.P_ITEM_ID,
                            A.P_DELIVERY_DETAIL_ID) LOOP
                SELECT MTL_TXN_REQUEST_HEADERS_S.NEXTVAL
                    INTO L_NUM_BATCH_ID
                    FROM DUAL;
           
                L_CHR_DISTRIBUTOR_ID    := REC_OM_GET_ORDER_DATA.PB_ID;
                L_CHR_BATCH_NAME        := 'XX_PICK_' || TO_CHAR(L_NUM_BATCH_ID);
                L_NUM_SHIP_CONF_RULE_ID := 0;
           
                BEGIN
                    INSERT INTO WSH_PICKING_BATCHES
                        (BATCH_ID,
                         NAME,
                         BACKORDERS_ONLY_FLAG,
                         EXISTING_RSVS_ONLY_FLAG,
                         CUSTOMER_ID,
                         ORDER_HEADER_ID,
                         ORDER_LINE_ID,
                         ORDER_TYPE_ID,
                         INCLUDE_PLANNED_LINES,
                         CREATION_DATE,
                         CREATED_BY,
                         LAST_UPDATE_DATE,
                         LAST_UPDATED_BY,
                         LAST_UPDATE_LOGIN,
                         PROGRAM_APPLICATION_ID,
                         PROGRAM_ID,
                         PROGRAM_UPDATE_DATE,
                         REQUEST_ID,
                         AUTO_PICK_CONFIRM_FLAG,
                         AUTOPACK_FLAG,
                         AUTOPACK_LEVEL,
                         AUTODETAIL_PR_FLAG,
                         AUTOCREATE_DELIVERY_FLAG,
                         SHIPMENT_PRIORITY_CODE,
                         SHIP_METHOD_CODE,
                         CARRIER_ID,
                         SHIP_TO_LOCATION_ID,
                         SHIP_SET_NUMBER,
                         SUBINVENTORY,
                         SHIP_FROM_LOCATION_ID,
                         DEFAULT_STAGE_SUBINVENTORY,
                         DEFAULT_STAGE_LOCATOR_ID,
                         PICK_FROM_SUBINVENTORY,
                         PICK_FROM_LOCATOR_ID,
                         PICK_GROUPING_RULE_ID,
                         PICK_SEQUENCE_RULE_ID,
                         PARTIAL_ALLOWED_FLAG,
                         SHIP_CONFIRM_RULE_ID)
                    VALUES
                        (L_NUM_BATCH_ID,
                         L_CHR_BATCH_NAME,
                         'O',
                         'N',
                         REC_OM_GET_ORDER_DATA.SOLD_TO_ORG_ID,
                         REC_OM_GET_ORDER_DATA.HEADER_ID,
                         J.SOURCE_LINE_ID,
                         REC_OM_GET_ORDER_DATA.ORDER_TYPE_ID,
                         'N',
                         SYSDATE,
                         -1,
                         SYSDATE,
                         -1,
                         -1,
                         -1,
                         -1,
                         SYSDATE,
                         -1,
                         'Y', -- AUTO PICK CONFIRM FLAG
                         'N',
                         0,
                         'Y',
                         'Y',
                         NULL,
                         NULL,
                         NULL,
                         NULL,
                         NULL,
                         NULL,
                         NULL,
                         'XXXX69', --default_stage_subinventory,
                         56564, --default_stage_locator_id,
                         'XXXX21', -- pick_from_subinventory,
                         NULL,
                         -- pick_from_locator_id,
                         1006, -- pick_grouping_rule_id,
                         1006, --pick_sequence_rule_id,
                         NULL, -- partial_allowed_flag,
                         L_NUM_SHIP_CONF_RULE_ID);
               
                    dbms_output.put_line('l_num_batch_id ' || l_num_batch_id);
                EXCEPTION
                    WHEN OTHERS THEN
                        dbms_output.put_line('INSERT FAILED' || SQLERRM);
                        --p_batch_id := 0;
                END;
           
                COMMIT;
                begin
                WSH_PICK_LIST.ONLINE_RELEASE(L_NUM_BATCH_ID,
                                                                         L_CHR_PL_RESULT,
                                                                         L_CHR_PL_PHASE,
                                                                         L_CHR_PL_SKIP);
                --p_batch_id := l_num_batch_id;
                exception when others then
                 null;
                end;
                COMMIT;
            END LOOP;
        END LOOP;
    END LOOP;
END;
/

Script to ship confirm the staged picked lines


/**************************************************************************************
 * TYPE            : Script                                                           *
 * NAME            : ship_confirm.sql                                    *
 * PURPOSE         : This is to ship confirm the staged picked lines                  *
 *                                                                                    *
 * Author   Date          Ver    Description                                          *
 * ------   -----------  ----   --------------------------- --------------------------*
 * XXXXX  20-Feb-2020    1.00   Created                                             *
 **************************************************************************************/

SET SERVEROUT ON;
alter session set current_schema = apps;

DECLARE
--Parameters for WSH_DELIVERIES_PUB.Delivery_Action.
out_chr_errbuf       VARCHAR2 (3000);
   l_out_chr_errbuf     VARCHAR2 (3000);
   p_action_code VARCHAR2(15);
   p_delivery_id NUMBER;
   p_delivery_name VARCHAR2(30);
   p_asg_trip_id NUMBER;
   p_asg_trip_name VARCHAR2(30);
   p_asg_pickup_stop_id NUMBER;
   p_asg_pickup_loc_id NUMBER;
   p_asg_pickup_loc_code VARCHAR2(30);
   p_asg_pickup_arr_date DATE;
   p_asg_pickup_dep_date DATE;
   p_asg_dropoff_stop_id NUMBER;
   p_asg_dropoff_loc_id NUMBER;
   p_asg_dropoff_loc_code VARCHAR2(30);
   p_asg_dropoff_arr_date DATE;
   p_asg_dropoff_dep_date DATE;
   p_sc_action_flag VARCHAR2(10);
   p_sc_intransit_flag VARCHAR2(10);
   p_sc_close_trip_flag VARCHAR2(10);
   p_sc_create_bol_flag VARCHAR2(10);
   p_sc_stage_del_flag VARCHAR2(10);
   p_sc_trip_ship_method VARCHAR2(30);
   p_sc_actual_dep_date VARCHAR2(30);
   p_sc_report_set_id NUMBER;
   p_sc_report_set_name VARCHAR2(60);
   p_wv_override_flag VARCHAR2(10);
   x_trip_id VARCHAR2(30);
   x_trip_name VARCHAR2(30);
   x_msg_count NUMBER;
   x_msg_data VARCHAR2(2000);
   p_api_version_number NUMBER;
   init_msg_list VARCHAR2(30);
   x_msg_details VARCHAR2(3000);
   x_msg_summary VARCHAR2(3000);
   p_validation_level NUMBER;
   l_chr_status                         VARCHAR2 (3) := 'E';
   x_return_status                      VARCHAR2 (3);
   l_chr_msg_data                       VARCHAR2 (2000);
   l_num_msg_count                      NUMBER;
   l_delivery_detail_id                 wsh_util_core.id_tab_type;
   l_del_rows                           wsh_util_core.id_tab_type;
   l_num_index                          BINARY_INTEGER;
   l_del_id                             NUMBER;
  -- l_chr_check_delivery               VARCHAR2 (3);
   l_chr_return_status                  VARCHAR2(100);
   l_chr_msg                            VARCHAR2(3000);
   l_chr_result                         VARCHAR2(30);
   l_num_line_id                        oe_order_lines_all.line_id%TYPE;
   l_num_user_id                        NUMBER;
   l_num_resp_id                        fnd_responsibility_vl.responsibility_id%TYPE;
   l_num_resp_appl_id                   fnd_application.application_id%TYPE;
   EXP_EXIT                             EXCEPTION;
   f_log                                UTL_FILE.file_type;
   l_num_count                          NUMBER;
 
   CURSOR cur_get_del_details
   IS
   SELECT wdd.delivery_detail_id,ooha.order_number,
          wda.delivery_id,wdd.organization_id
     FROM wsh_delivery_details wdd,
          oe_order_headers_all ooha,
          wsh_delivery_assignments wda
    WHERE ooha.header_id=wdd.source_header_id
      AND wdd.released_status='Y'
      AND wda.delivery_detail_id=wdd.delivery_detail_id
 AND wdd.organization_id = 331
      --AND ooha.header_id in (137508997)
 and wdd.source_header_id in (135503888);
     
BEGIN
 
   fnd_global.apps_initialize (11009, 67142, 20044);

   l_delivery_detail_id.DELETE;
   l_del_rows.DELETE;
   l_num_count:=0;
   FOR rec_get_del_details IN cur_get_del_details
   LOOP
      l_num_index:=1;
      l_delivery_detail_id.DELETE;
      l_del_rows.DELETE;
      IF rec_get_del_details.delivery_id IS NULL THEN
         l_delivery_detail_id(l_num_index):=rec_get_del_details.delivery_detail_id;
         WSH_DELIVERY_DETAILS_PUB.AUTOCREATE_DELIVERIES (1.0
                                                    , fnd_api.g_true
                                                    , fnd_api.g_true
                                                    , l_chr_status
                                                    , l_num_msg_count
                                                    , l_chr_msg_data
                                                    , l_delivery_detail_id
                                                    , l_del_rows
                                                    );
          l_del_id:=l_del_rows(l_num_index);
        ELSE
           l_del_id:=rec_get_del_details.delivery_id;
        END IF;    
                
       --Ship confirm the delivery
        p_action_code := 'CONFIRM'; -- The action code for ship confirm
        p_delivery_id := l_del_id; -- The delivery that needs to be confirmed
        p_delivery_name := to_char(l_del_id); -- The delivery name,
        p_sc_action_flag := 'S'; -- Ship entered quantity.
        p_sc_intransit_flag := 'Y'; -- In transit flag is set to 'Y' closes the
       p_sc_close_trip_flag := 'Y'; -- Close the trip after ship confirm
  --    p_sc_trip_ship_method := 'GROUND'; -- The ship method code
 --    Call to WSH_DELIVERIES_PUB.Delivery_Action.

       begin
        WSH_DELIVERIES_PUB.DELIVERY_ACTION(p_api_version_number => 1.0,
                                           p_init_msg_list => init_msg_list,
                                           x_return_status => x_return_status,
                                           x_msg_count => x_msg_count,
                                           x_msg_data => x_msg_data,
                                           p_action_code => p_action_code,
                                           p_delivery_id => p_delivery_id,
                                           p_delivery_name => p_delivery_name,
                                           p_asg_trip_id => p_asg_trip_id,
                                           p_asg_trip_name => p_asg_trip_name,
                                           p_asg_pickup_stop_id => p_asg_pickup_stop_id,
                                           p_asg_pickup_loc_id => p_asg_pickup_loc_id,
                                           p_asg_pickup_loc_code => p_asg_pickup_loc_code,
                                           p_asg_pickup_arr_date => p_asg_pickup_arr_date,
                                           p_asg_pickup_dep_date => p_asg_pickup_dep_date,
                                           p_asg_dropoff_stop_id => p_asg_dropoff_stop_id,
                                           p_asg_dropoff_loc_id => p_asg_dropoff_loc_id,
                                           p_asg_dropoff_loc_code => p_asg_dropoff_loc_code,
                                           p_asg_dropoff_arr_date => p_asg_dropoff_arr_date,
                                           p_asg_dropoff_dep_date => p_asg_dropoff_dep_date,
                                           p_sc_action_flag => p_sc_action_flag,
                                           p_sc_intransit_flag => p_sc_intransit_flag,
                                           p_sc_close_trip_flag => p_sc_close_trip_flag,
                                           p_sc_create_bol_flag => p_sc_create_bol_flag,
                                           p_sc_stage_del_flag => p_sc_stage_del_flag,
                                           p_sc_trip_ship_method => p_sc_trip_ship_method,
                                           p_sc_actual_dep_date => p_sc_actual_dep_date,
                                           p_sc_report_set_id => p_sc_report_set_id,
                                           p_sc_report_set_name => p_sc_report_set_name,
                                           p_wv_override_flag => p_wv_override_flag,
                                           x_trip_id => x_trip_id,
                                           x_trip_name => x_trip_name);
      exception when others then
       null;
      end;

      l_num_count:=l_num_count+1;
      begin
      WSH_UTIL_CORE.get_messages('Y', x_msg_summary, x_msg_details, x_msg_count);
      exception when others then
       null;
      end;
   END LOOP;
      COMMIT;
   BEGIN
         WSH_SHIP_CONFIRM_ACTIONS.INTERFACE_ALL (errbuf                 => out_chr_errbuf,
                                                 retcode                => l_out_chr_errbuf,
                                                 p_mode                 => 'ALL',
                                                 p_stop_id              => NULL,
                                                 p_delivery_id          => NULL,
                                                 p_log_level            => 0,
                                                 p_batch_id             => NULL,
                                                 p_trip_type            => NULL,
                                                 p_organization_id      => 331,
                                                 p_stops_per_batch      => NULL
                                                );
      COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('Error while ITT');
   END;
EXCEPTION
   WHEN OTHERS THEN
null;
END;
/

Script to backorder the orders which are in Ready to Warehouse status


/***************************************************************************************
 * TYPE            : Script                                                            *
 * NAME            : BACKORDER.sql                                                     *
 * PURPOSE         : The script will backorder the orders which are RTW status         *
 *                                                                                     *
 * Author    Date          Ver    Description                                          *
 * --------  -----------  ----   -------------------------- ---------------------------*
 * XXXXXXX   02-Feb-2020    1.00   Created                                               *
 ***************************************************************************************/

alter session set current_schema = apps;

SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON SIZE 100000;

DECLARE
   x_return_status   VARCHAR2 (5000);
   x_msg_count       NUMBER;
   x_msg_data        VARCHAR2 (5000);
   l_num_user_id             NUMBER;
   l_num_resp_id             NUMBER;
   l_num_resp_appl_id        NUMBER;

   CURSOR get_line_id
   IS
      SELECT DISTINCT wdd.move_order_line_id
                 FROM apps.wsh_delivery_details wdd, apps.oe_order_headers_all ooh
                WHERE wdd.source_header_id = ooh.header_id
                  AND wdd.released_status = 'S' --Ready To Warehouse
                  --AND ooh.header_id in (137508997)
 and wdd.organization_id = 331
 and wdd.source_header_id in (135503888);

BEGIN
 
   fnd_global.apps_initialize (11009, 67143, 20044);
 

   FOR rec_get_line_id IN get_line_id
   LOOP
      INV_MO_BACKORDER_PVT.BACKORDER (rec_get_line_id.move_order_line_id,
                                      x_return_status,
                                      x_msg_count,
                                      x_msg_data
                                     );
      COMMIT;
    END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR  ' || SQLERRM);
END;
/

Script to get Item On Hand Quantity


SET SERVEROUTPUT ON SIZE 900000;
ALTER SESSION SET current_schema = apps;

DECLARE
   v_api_return_status    VARCHAR2 (500);
   v_qty_oh               NUMBER;
   v_qty_res_oh           NUMBER;
   v_qty_res              NUMBER;
   v_qty_sug              NUMBER;
   v_qty_att              NUMBER;
   v_qty_atr              NUMBER;
   v_msg_count            NUMBER;
   v_msg_data             VARCHAR2 (1000);

   CURSOR c_item_info (
      l_subinventory_code   VARCHAR2,
      l_organization_id     NUMBER,
      l_inventory_item_id   NUMBER
   )
   IS
      SELECT   inventory_item_id, subinventory_code, organization_id
          FROM apps.mtl_onhand_quantities_detail
         WHERE inventory_item_id =
                                 NVL (l_inventory_item_id, inventory_item_id)
           AND organization_id = l_organization_id
           AND subinventory_code = l_subinventory_code
      GROUP BY inventory_item_id, organization_id, subinventory_code;

   ln_subinventory_code   VARCHAR2 (100);
   ln_organization_id     NUMBER;
   ln_inventory_item_id   NUMBER;
   l_item                 VARCHAR2 (15);
BEGIN
/*Mandatory */
   ln_subinventory_code := 'LAAC11';                  --:p_subinventory_code;
/*Mandatory*/
   ln_organization_id := 331;                           --:p_organization_id;
/*Optional*/
   ln_inventory_item_id := NULL;                      --:p_inventory_item_id;
   inv_quantity_tree_grp.clear_quantity_cache;
   DBMS_OUTPUT.put_line ('Sub Inventory|Organization Id|Item|Onhand Qty|Available To Transact|Available To Reserve');

   FOR i IN c_item_info (ln_subinventory_code,
                         ln_organization_id,
                         ln_inventory_item_id
                        )
   LOOP
      BEGIN
         SELECT segment1
           INTO l_item
           FROM mtl_system_items_b
          WHERE inventory_item_id = i.inventory_item_id
            AND organization_id = i.organization_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_item := NULL;
      END;

      APPS.INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES
                (p_api_version_number       => 1.0,
                 p_init_msg_lst             => apps.fnd_api.g_false,
                 x_return_status            => v_api_return_status,
                 x_msg_count                => v_msg_count,
                 x_msg_data                 => v_msg_data,
                 p_organization_id          => i.organization_id,
                 p_inventory_item_id        => i.inventory_item_id,
                 p_tree_mode                => apps.inv_quantity_tree_pub.g_transaction_mode,
                 p_onhand_source            => apps.inv_quantity_tree_pvt.g_all_subs,
                                                                          --3,
                 p_is_revision_control      => FALSE,
                 p_is_lot_control           => FALSE,
                 p_is_serial_control        => FALSE,
                 p_revision                 => NULL,
                 p_lot_number               => NULL,
                 p_subinventory_code        => i.subinventory_code,
                 p_locator_id               => NULL,
                 x_qoh                      => v_qty_oh,
                 x_rqoh                     => v_qty_res_oh,
                 x_qr                       => v_qty_res,
                 x_qs                       => v_qty_sug,
                 x_att                      => v_qty_att,
                 x_atr                      => v_qty_atr
                );
    DBMS_OUTPUT.put_line (i.subinventory_code||'|'||i.organization_id||'|'||l_item||'|'||v_qty_oh||'|'||v_qty_att||'|'||v_qty_atr);          
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (-20001,    'An error was encountered - ' || SQLCODE || ' -ERROR- '|| SQLERRM);
END;
/

Tuesday, May 21, 2013

O2C

Following are the steps the process will go through from creating a Sales Order to Posting the details in GL.
  1. Enter the Sales Order
  2. Book the Sales Order
  3. Launch Pick Release
  4. Ship Confirm
  5. Create Invoice
  6. Create the Receipts either manually or using Auto Lockbox ( In this article we will concentrate on Manual creation)
  7. Transfer to General Ledger
  8. Journal Import
  9. Posting

Involved Modules
Order Management: Creating and shipping the Order
Inventory:  Onhand availability, Reservation
Receivables: For Receipt against invoice
General Ledger: Importing, entering and posting the journals



Enter the Sales Order:
Order Management Super User Operations (USA)>Orders Returns >Sales Orders
Enter the Customer details (Ship to and Bill to address), Order type.
Click on Lines Tab. Enter the Item to be ordered and the quantity required

Line is scheduled automatically when the Line Item is saved.

Scheduling/unscheduling can be done manually by selecting Schedule/Un schedule from the Actions Menu.
You can check if the item to be ordered is available in the Inventory by clicking on Availability Button.

Save the work.

Tables Affected:
OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL
Columns used for identification of flow
FLOW_STATUS_CODE in both the tables which is ENTERED.

Book the Sales Order
Book the Order by clicking on the Book Order button

Now that the Order is BOOKED, the status on the header is change accordingly.

Tables affected:
The FLOW_STATUS_CODE in the table OE_ORDER_HEADERS_ALL would be BOOKED.
The FLOW_STATUS_CODE in OE_ORDER_LINES_ALL will be AWAITING_SHIPPING.
New records will be created in WSH_DELIVERY_DETAILS with RELEASED_STATUS=’R’ (Ready to Release)
Also Record(s) will be inserted into WSH_DELIVERY_ASSIGNMENTS
At the same time DEMAND INTERFACE PROGRAM runs in the background and inserts into MTL_DEMAND.

Launch Pick Release
Shipping > Release Sales Order > Release Sales Orders
Give the Sales Order and Rule details as required
Key in the details in Shipping Tab, Autocreate Delivery: Yes, Auto Pick Confirm: Yes, Autopack Delivery: Yes

Similarly key in the Subinventory, location and org details in Inventory tab as required

Then select Execute Now button.

On completion this will show below window
Pick Release process will in turn run few more concurrent requests like
Auto Pack Report, Pick Slip Report, Shipping Exceptions Report (These are what we have chosen in the shipping tab during Pick Release)

Tables Affected
If Autocreate Delivery is set to ‘Yes’ then a new record is created in the table WSH_NEW_DELIVERIES
DELIVERY_ID is populated in the table WSH_DELIVERY_ASSIGNMENTS
The RELEASED_STATUS in WSH_DELIVERY_DETAILS would be now set to ‘Y’ (Pick Confirmed) if Auto Pick Confirm is set to Yes otherwise RELEASED_STATUS is ‘S’ (Release to Warehouse)

Pick Confirm the Order:
If Auto Pick Confirm is set to Yes during Pick Release, then this process will happen systematically.
In other cases, need to do this by doing a move order transaction
Inventory Super User > Move Order> Transact Move Order
Take the Move Order Number form the Shipping Transaction Form and find that and transact.

Ship Confirm the Order:
Order Management Super User>Shipping >Transactions
Find by Order Number

Here you can find all the details

If the line status is Staged/Pick Confirmed, we can go for a shipment Then Click on the Delivery tab

Then Click Ship Confirm

Note: If Ship Confirm button is inactive, then please check to the user id you are logged in, whether Shipping Grant given or not. If not they add a shipping grant to the user id and try again.

Then OK.
The Status in Shipping Transaction form in Delivery tab should be closed

This will kick off concurrent programs like.INTERFACE TRIP Stop, Commercial Invoice, Packing Slip Report, Bill of Lading

Tables Affected:
RELEASED_STATUS in WSH_DELIVERY_DETAILS would be ‘C’ (Ship Confirmed)
FLOW_STATUS_CODE in OE_ORDER_HEADERS_ALL would be “BOOKED“
FLOW_STATUS_CODE in OE_ORDER_LINES_ALL would be “SHIPPED“
Once all background process completes, the Shipping Transaction Status in Lines/LPNs tab becomes Interfaced


Create Invoice:
Run workflow background Process
Order Management >view >Requests

Workflow Background Process inserts the records RA_INTERFACE_LINES_ALL with
INTERFACE_LINE_CONTEXT     =     ’ORDER ENTRY’
INTERFACE_LINE_ATTRIBUTE1=     Order_number
INTERFACE_LINE_ATTRIBUTE3=     Delivery_id
And spawns Auto invoice Master Program and Auto invoice import program which creates Invoice for that particular Order


Check the Invoice:
The Invoice created can be seen using the Receivables responsibility
Receivables Super User> Transactions> Transactions
Query with the Order Number as Reference

Affected Tables:
RA_CUSTOMER_TRX_ALL will have the Invoice header information. The column INTERFACE_HEADER_ATTRIBUTE1 will have the Order Number.
RA_CUSTOMER_TRX_LINES_ALL will have the Invoice lines information. The column INTERFACE_LINE_ATTRIBUTE1 will have the Order Number.

Create receipt: Receivables> Receipts> Receipts
Click Apply to apply this to an invoice and then select the invoice in next screen and then apply.

Tables affected:
AR_CASH_RECEIPTS_ALL

Transfer to General Ledger:
To transfer the Receivables accounting information to general ledger, run General Ledger Transfer Program
Receivables> View Requests
Parameters:

  • Give in the Start date and Post through date to specify the date range of the transactions to be transferred.
  • Specify the GL Posted Date, defaults to SYSDATE.
  • Post in summary: This controls how Receivables creates journal entries for your transactions in the interface table. If you select ‘No’, then the General Ledger Interface program creates at least one journal entry in the interface table for each transaction in your posting submission. If you select ‘Yes’, then the program creates one journal entry for each general ledger account.
  • If the Parameter Run Journal Import is set to ‘Yes’, the journal import program is kicked off automatically which transfers journal entries from the interface table to General Ledger, otherwise follow the topic Journal Import to import the journals to General Ledger manually.


Tables Affected:
GL_INTERFACE

Journal Import:
To transfer the data from General Ledger Interface table to General Ledger, run the Journal Import program from Oracle General Ledger.
General Ledger > Journal> Import> Run
Parameters:
    Select the appropriate Source.
    Enter one of the following Selection Criteria:
No Group ID: To import all data for that source that has no group ID. Use this option if you specified a NULL group ID for this source.
All Group IDs: To import all data for that source that has a group ID. Use this option to import multiple journal batches for the same source with varying group IDs.
Specific Group ID: To import data for a specific source/group ID combination. Choose a specific group ID from the List of Values for the Specific Value field.
If you do not specify a Group ID, General Ledger imports all data from the specified journal entry source, where the Group_ID is null.
Define the Journal Import Run Options (optional)
Choose Post Errors to Suspense if you have suspense posting enabled for your set of books to post the difference resulting from any unbalanced journals to your suspense account.
Choose Create Summary Journals to have journal import create the following:
• one journal line for all transactions that share the same account, period, and currency and that has a debit balance
• One journal line for all transactions that share the same account, period, and currency and that has a credit balance.
Enter a Date Range to have General Ledger import only journals with accounting dates in that range. If you do not specify a date range, General Ledger imports all journals data.
Choose whether to Import Descriptive Flexfields, and whether to import them with validation.
Click on Import button

Affected tables
GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES

Posting:
We have to Post journal batches that we have imported previously to update the account balances in General Ledger
General Ledger> Journals > Enter

Click Find
From the list of unposted journals displayed, select one journal at a time and click on Post button to post the journal.

If you know the batch name to be posted you can directly post using the Post window


Friday, May 17, 2013

OAF and Profile Options


FND : Diagnostics:
Setting the FND : Diagnostics (FND_DIAGNOSTICS) profile option to "Yes" will enable the diagnostics global button to be rendered on the screen. Pressing this button brings the user to an interface where the user can choose what type of logged messages to display.

Also Enabling the profile "FND: Diagnostics / FND_DIAGNOSTICS" automatically renders the "About this page" link at the bottom of every OA Framework page.

Personalization Levels
Personalizations can be enabled at the function, site, operating unit or responsibility level. Personalizations at lower levels override personalizations at higher levels. Values inherit the definition from the level immediately above unless changed.

FND: Personalization Region Link Enabled:
Valid values:  Yes - renders the "Personalize  Region" links above each  region in a page. Each link  takes you first to the Choose Personalization Context page, then to the Page Hierarchy Personalization page with focus on the region node from which  you selected the "Personalize  Region" link.

Personalize Self-Service Defn: – Set this profile to Yes to allow personalizations.

Disable Self-Service Personalization: - Yes will disable all personalizations at any level.

FND: Personalization Document Root Path: - Set this profile option to a tmp directory with open (777)  permissions for migrating personalizations between instances.


How to See Log on Page
Enable profile Option FND_Diagnostics to "Yes" at User OR Site Level.

In Controller write this code:-
pageContext.writeDiagnostics(this, "Checking profile options", 1);

In Application Module write this code
getOADBTransaction().writeDiagnostics(this, "Checking Profile Option", 1);

Now to see log on screen Click on “Diagnostics” on Page [Top right on page]. Then Choose Show Log on Screen from the picklist and choose log level as Statement Level and Click on Go

How Add Descriptive Flex Field(DFF) in OAF page

How to Check VO state whether it is modified or not

ApplicationModule.getTransaction().isDirty() - This method tells you whether the transaction contains any changes in the view objects. This works for transactions made by entity object-based view objects only.

OAViewObject.isDirty() - This method tells you whether a particular view object contains changes or not. This works for both entity object-based view objects and view objects based on OAPlsqlViewObjectImpl. For view objects based on OAPlsqlViewObjectImpl, you can also use OAPlsqlViewObjectImpl.getState() method 

How to Call OAF Page from Workflow Email Notification

In R12  below error will occur if you try to open an OAF Page URL link directly.

You are trying to access a page that is no longer active. The referring page may have come from a previous session. Please select Home to Proceed.

Note: When you call a standard page this error won't appear. This happens only when you try to open a custom page.

Below are the steps to resolve the error:
1.Open the custom page in Jdev and select the PageLayoutRN. In the property inspector set the Security Mode to selfSecured.
2.Set the rendered property to ${oa.FunctionSecurity.}
Eg: ${oa.FunctionSecurity.XXCUSTPG}
Note: Your user id should have access to the custom page function.
3.In your workflow procedure generate the  OA page URL with the below function and set the workflow attribute with the generated URL.

lc_url:= FND_RUN_FUNCTION.get_run_function_url(p_function_id => 53637,
p_resp_appl_id => null,
p_resp_id => null,
p_security_group_id => 0,
p_parameters =>'PARAM1='||p_lease_id,--'&'||'LeaseId=784',
p_override_agent => null,
p_org_id => null,
p_lang_code => null,
p_encryptParameters => false);

Now you should be able to open the OAF page from workflow email notification.

Source:http://www.oraclearea51.com/contribute/post-a-blog-article/how-to-call-oaf-page-from-workflow-email-notification.html

Open Workflow status monitor diagram page in OAF


Create a VO for the query which is given below through this you can get the current diagram url,

select WF_MONITOR.GetDiagramURL(WF_CORE.Translate('WF_WEB_AGENT'),NtfEO.MESSAGE_TYPE,NtfEO.ITEM_KEY,'NO') monitor_url from
(SELECT ITEM_KEY,MESSAGE_TYPE FROM WF_NOTIFICATIONS WHERE NOTIFICATION_ID = :1) NtfEO

Steps:
1.First find the button component of your page.
2.Call VO with the input parameters which will return the diagram url.
3.Put that url into the java script function.
4.Through the java script you can open a new popup window which will display the status monitor diagram

Sample Code:
OASubmitButtonBean reAssignBea1n = (OASubmitButtonBean) paramOAWebBean.findChildRecursive("WfMonDiagramCtrl");

Serializable[] parameters1 = { NotificationId };
Serializable aa1 = am.invokeMethod("getMonitorURL", parameters1);
if (aa1 != null && !aa1.toString().equals(""))
{
String url = "window.open('" + aa1.toString() + "')";
paramOAPageContext.putJavaScriptFunction("LaunchMonitor", url);
}

Source:http://anuj-oaframeworkblog.blogspot.in/2009/09/open-workflow-status-monitor-diagram.html

Call Workflow from OAF Page


Oracle Workflow is tightly integrated with Oracle Apps and it is very common to invoke workflow from OAF Pages too.

The class oracle.apps.fnd.framework.webui.OANavigation provides Java wrappers for Oracle Workflow Engine’s PL/SQL APIs.

It is very simple to invoke Workflow using OAF, again there are two options first using Java wrappers and second through calling PL/SQL procedures but the later approach you can use if you are converting some Oracle Form into OAF form and all the code for Workflow is already ready and tested but if it is new workflow then you should use Java wrappers:

Following piece of code invokes the Workflow from OAF:
import oracle.apps.fnd.framework.webui.OANavigation;
public void launchWorkFlowFromOAF(OAPageContext pageContext)
{
  String wfItemType = “‘XXSR’”;
  String wfProcess = “‘SR_MAIN_PROCESS’”;
  OADBTransaction transaction = getOADBTransaction();
  String Sr_No ;
  String wfItemKey = ” “;

  Sr_No = pageContext.getParameter(“sr_no”);
  wfItemKey = Sr_No+ transaction.getSequenceValue(“xxsr_key_s.NEXTVAL”).toString();
  OANavigation wfClass = new OANavigation();

  // Create Workflow Process
  wfClass.createProcess(pageContext, wfItemType, wfProcess, wfItemKey);

  // Set Number Attribute: SR_NO
  wfClass.setItemAttrNumber( pageContext, wfItemType, wfItemKey, ”SR_NO”, Sr_No);

  // Start Workflow Process
  wfClass.startProcess(pageContext, wfItemType, wfProcess, wfItemKey);
}


If you want to invoke this workflow using callable statement then you have to write code like this:
OAF code:

if(pageContext.getParameter(“btnSubmit”)!=null)
{
  String sql = "BEGIN xx_sr_notf_pkg.invoke_wf (:1); END;";
  String status = null;
  OracleCallableStatement cs = (OracleCallableStatement)
  am.getOADBTransaction().createCallableStatement(sql,1);
  try
  {
    cs.setString(1,srNo);
    cs.execute();
    cs.close();
  }
  catch (Exception ex)
  {
    throw new OAException(ex.getMessage().toString(),
    OAException.ERROR);
  }
  throw new OAException("SR "+srNo+" has been submitted",
  OAException.CONFIRMATION);
}

PL/SQL Code to Invoke Workflow:
PROCEDURE invoke_wf (p_sr_doc_number IN VARCHAR2)
IS
   l_item_key             VARCHAR2 (50);
   BEGIN
        SELECT p_sr_doc_number ||  xxegasr_key_s.NEXTVAL
        INTO l_item_key
        FROM DUAL;
        wf_engine.createprocess ('XXSR', l_item_key, 'SR_MAIN_PROCESS');
        wf_engine.setitemattrnumber(itemtype      => 'XXSR',
                            itemkey       => l_item_key,
                            aname         => 'P_SR_DOC_NO',
                            avalue        => p_sr_doc_number
                           );    
        wf_engine.startprocess ('XXSR', l_item_key);
        COMMIT;
END invoke_wf;

Source:http://dineshnair.wordpress.com/2009/06/02/integrating-workflow-and-oaf/
Related Posts Plugin for WordPress, Blogger...