, , , , , , ,

RETCODE & ERRBUFF Parameters in Concurrent Program

As we all know there are two mandatory parameters that need to be pased for all the procedures called
1.ERRBUFF
2.RETCODE..

Based on the business process if there is any undefined exeception occured while running concurrent program, we can end the concurrent program with Error/Warning.

Define ERRBUFF as the first parameter and Retcode as the second one. Mention the OUT variable type.

CREATE PROCEDURE PROCEDURE_NAME (errbuf  OUT VARCHAR2,
                                 retcode OUT VARCHAR2)

The retcode has three values returned by the concurrent manager
0–Success
1–Success & warning
2–Error

we can set the concurrent program to any of the three status by using these values in the retcode parameter

Example:
========

BEGIN
…..
EXCEPTION
     WHEN OTHERS THEN
        FND_FILE.PUT_LINE(FND_FILE.LOG,’Unhandled exception occurred in package. ErrMsg: ‘||SQLERRM);
        retcode=’2′;
END;

Even you can use fnd_concurrent.set_completion_Status to send the concurrent program to more status than success,error and warning.

/by
, , , , , , , ,

Create Request Group using oracle api

Recently i got a requirement to create request and attach all the standard reports of GL to these request groups. It seems a lot tedious task to do manually. So i developed this script to copy the request group content to other request groups.

This is very useful if you have to create multiple request groups and attach reports to all.

DECLARE
CURSOR c1 IS
SELECT b.concurrent_program_name programname,c.application_name appname
FROM fnd_request_group_units a
,fnd_concurrent_programs b
,fnd_application_tl c
,fnd_request_groups d
WHERE a.request_Group_id=d.request_Group_id
AND a.request_unit_id=b.concurrent_program_id
AND b.application_id=c.application_id
AND d.request_Group_name=’GL Concurrent Program Group’;
BEGIN
FOR rec IN c1 LOOP
fnd_program.add_to_group(program_short_name =>rec.programname
,program_application=>REC.APPNAME
,request_group=>’XX_GL_REQ_GRP’
,group_application=>’Custom Application’);
END LOOP;
END;

/by
, , , , , , , , ,

Importing Sales Orders From Excel

This article gives you simple steps to load large sales orders in Order Management using Excel Spreasheet.
We arrived at this solution using some good features available in Oracle Database and Oracle Applications. Here are the steps.
  1. We create order header only (using sales order form) with all the necessary information like customer, order type, price list, sales person, sales credits, warehouse and the other values as deemed necessary.
  2. Now we create line information in Excel worksheet (a template is created). We enter values for the fields like Item, quantity, price list name (if that is different from header), Line Type, Sales Person (different from header), any DFF segments, Shipping Instructions if any, warehouse name so on. We can have as many fields as that can fit in the order lines table. But instead of entering all the values, we use defaulting rules to default a lot of values from the header.
  3. In this worksheet there is a simple marco written that we use to generate another worksheet in the same file for the entered rows.This macro does some basic validations on the information entered in the worksheet and generates the worksheet with this statement for each row.
    1.  insert into custom_lines_load_tbl  (column1,column2,….) values ( field1,field2,……)
  4. Now using these rows with this statement (generated worksheet) we create a simple text file (copy and paste).
  5. As a next step we upload this file as an attachment to the order header that we have created in the step number 1.
  6. Now we run a custom program to create order lines into this order. This program takes order number as a parameter. This program reads the file contents line by line and executes the insert statements. This is the heart of the solution which is to read the contents of the file which is stored as LOB and executing the statements. After all the insert statements are executed successfully, the program calls oe_order_pub API to create order lines.
  7. As you can see the program uses this feature : reading the file which is stored in the database as LOB line by line. Here is the code snippet to read the attached file stored as a file.

PROCEDURE parse_and_load_file (
      ERROR_CODE       OUT      VARCHAR2,
      retcode          OUT      NUMBER,
      p_order_number   IN       NUMBER
   )
   IS
      fil             BLOB;
      pos             INTEGER         := 0;
      amt             BINARY_INTEGER  := 32767;
      buf             RAW (2000);
      l_start         RAW (3000);
      l_end           RAW (3000);
      l_start_pos     INTEGER;
      l_length        INTEGER;
      nth             INTEGER         := 1;
      i               INTEGER         := 2;
      l_text          VARCHAR2 (4000);
      l_end_pos       INTEGER;
      l_plsql_block   VARCHAR2 (4000);
      k               INTEGER;
      l_write_buff    RAW (2000);
      l_text1         VARCHAR2 (4000);
      x_errbuf        VARCHAR2 (4000);
      x_retcode       NUMBER;
   BEGIN
      BEGIN
    begin
    delete custom_lines_upload_tbl
    where order_number=p_order_number
    and line_Id is null; –this is to make sure we do not load duplicate data
    exception when others then
    null;
    end;

SELECT file_data
  INTO fil
  FROM fnd_lobs
 WHERE file_id =
          (SELECT media_id
             FROM fnd_documents_vl
            WHERE document_id IN (
                     SELECT a.document_id
                       FROM fnd_attached_documents a, fnd_documents b
                      WHERE a.document_id = b.document_id
                        AND b.category_id =
                                     (SELECT category_id
                                        FROM fnd_document_categories_tl
                                       WHERE user_name = ‘Custom Lines Upload’)
                        AND entity_name = ‘OE_ORDER_HEADERS’
                        AND pk1_value = (SELECT header_id
                                           FROM oe_order_headers_all
                                          WHERE order_number = p_order_number)))
         FOR UPDATE;
      EXCEPTION
         WHEN OTHERS
         THEN
            debug_log
               (   ‘Order :’
                || p_order_number
                || ‘ does not have any file attached to it to load. Please attach the file and then try running this program’
               );
      END;

      k := DBMS_LOB.ISOPEN (fil);

      — debug_log(‘Checking whether the file is already open’);
      IF k = 1
      THEN
         –debug_log(‘file is already open. Closing it’);
         DBMS_LOB.CLOSE (fil);
      END IF;

      BEGIN
         –debug_log(‘Opening the file’);
         DBMS_LOB.OPEN (fil, DBMS_LOB.lob_readonly);
         k := DBMS_LOB.ISOPEN (fil);
      EXCEPTION
         WHEN OTHERS
         THEN
            debug_log
                    (‘Exception occured in checking whether the file is open’);
      END;

      IF k = 1
      THEN
         LOOP
            l_start := UTL_RAW.cast_to_raw (‘insert’);
            l_end := UTL_RAW.cast_to_raw (‘;’);
            l_start_pos := DBMS_LOB.INSTR (fil, l_start, 1, nth);
            pos := DBMS_LOB.INSTR (fil, l_end, 1, nth);
            buf := ”;
            –debug_log(‘Start position is :’||l_start_pos);
            –debug_log(‘End position is :’||pos);
            l_end_pos := (pos + 1) – (l_start_pos);
            –debug_log(‘Length of the string is :’||l_end_pos);
            –debug_log(‘Reading the file’);
            DBMS_LOB.READ (fil, l_end_pos, l_start_pos, buf);
            l_text := UTL_RAW.cast_to_varchar2 (buf);
            l_plsql_block :=
                  ‘begin’
               || fnd_global.NEWLINE ()
               || l_text
               || fnd_global.NEWLINE ()
               || ‘commit;’
               || fnd_global.NEWLINE ()
               || ‘exception when others then’
               || fnd_global.NEWLINE ()
               ||
                  –’debug_log(”Exception occured in processing the line”);’||fnd_global.NewLine()||
                  ‘NULL;’
               || fnd_global.NEWLINE ()
               || ‘end;’;

            –debug_log(‘———————————————–’);
            –debug_log(l_plsql_block);
            –debug_log(‘———————————————–’);
            BEGIN
            –Now execute the dynamic pl/sql block to insert rows into custom table from the file
               EXECUTE IMMEDIATE l_plsql_block;
            EXCEPTION
               WHEN OTHERS
               THEN
                  –debug_log(‘Exception Occured’);
                  –debug_log(l_plsql_block);
                  –debug_log(‘Exception is :’||substrb(sqlerrm, 1, 55));
                  NULL;
            END;

            nth := nth + 1;
         END LOOP;

         DBMS_LOB.CLOSE (fil);
      END IF;

      COMMIT;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         k := NULL;
         k := DBMS_LOB.ISOPEN (fil);

         IF k = 1
         THEN
            DBMS_LOB.CLOSE (fil);
         END IF;
      WHEN OTHERS
      THEN
         BEGIN
            — debug_log(‘End of File reached in Exception. Closing file’);
            k := NULL;
            k := DBMS_LOB.ISOPEN (fil);

            IF k = 1
            THEN
               DBMS_LOB.CLOSE (fil);
               COMMIT;
            END IF;
         END;

         –Here call the oe_order_pub API to create the lines passing the line table from this custom table.
   END parse_and_load_file;

/by
, , , , , , , ,

Oracle R12 Move Order using APIs

DECLARE
   l_header_rec      inv_move_order_pub.trohdr_rec_type;
   l_line_tbl        inv_move_order_pub.trolin_tbl_type;
   x_return_status   VARCHAR2 (1);
   x_msg_data        VARCHAR2 (4000);
   x_msg_count       NUMBER;

   PROCEDURE create_move_order (
      x_hdr_rec         OUT   inv_move_order_pub.trohdr_rec_type
    , x_line_tbl        OUT   inv_move_order_pub.trolin_tbl_type
    , x_return_status   OUT   VARCHAR2
    , x_msg_data        OUT   VARCHAR2
    , x_msg_count       OUT   NUMBER
   )
   IS
      l_hdr_rec         inv_move_order_pub.trohdr_rec_type              := inv_move_order_pub.g_miss_trohdr_rec;
      l_line_tbl        inv_move_order_pub.trolin_tbl_type              := inv_move_order_pub.g_miss_trolin_tbl;
      x_hdr_val_rec     inv_move_order_pub.trohdr_val_rec_type;
      x_line_val_tbl    inv_move_order_pub.trolin_val_tbl_type;
      v_msg_index_out   NUMBER;
      l_rsr_type        inv_reservation_global.mtl_reservation_tbl_type;
   BEGIN
      l_line_tbl.DELETE;
      x_line_tbl.DELETE;
      l_hdr_rec.date_required := SYSDATE;
      l_hdr_rec.header_status := inv_globals.g_to_status_preapproved;
      l_hdr_rec.organization_id := 207;
      l_hdr_rec.status_date := SYSDATE;
      l_hdr_rec.transaction_type_id := inv_globals.g_type_transfer_order_issue;
      l_hdr_rec.move_order_type := inv_globals.g_move_order_requisition;
      l_hdr_rec.db_flag := fnd_api.g_true;
      l_hdr_rec.operation := inv_globals.g_opr_create;
      l_hdr_rec.description := ‘Test Move Order’;
      l_hdr_rec.to_account_id := 12831;
      l_hdr_rec.from_subinventory_code := ‘FGI’;
      l_line_tbl (1).date_required := SYSDATE;
      l_line_tbl (1).inventory_item_id := 167742;
      l_line_tbl (1).line_id := fnd_api.g_miss_num;
      l_line_tbl (1).line_number := 1;
      l_line_tbl (1).line_status := inv_globals.g_to_status_preapproved;
      l_line_tbl (1).transaction_type_id := inv_globals.g_type_transfer_order_issue;
      l_line_tbl (1).organization_id := 207;
      l_line_tbl (1).quantity := 1;
      l_line_tbl (1).status_date := SYSDATE;
      l_line_tbl (1).uom_code := ‘Ea’;
      l_line_tbl (1).db_flag := fnd_api.g_true;
      l_line_tbl (1).operation := inv_globals.g_opr_create;
      l_line_tbl (1).from_subinventory_code := ‘FGI’;
      l_line_tbl (1).to_account_id := 12831;
      l_line_tbl (1).lot_number := ‘A01’;                                                         –If the item is lot controlled
      l_line_tbl (1).serial_number_start := ‘A01-0039’;                                 –If the item is serial number controlled
      l_line_tbl (1).serial_number_end := ‘A01-0039’;                                   –If the item is serial number controlled
      inv_move_order_pub.process_move_order (p_api_version_number      => 1.0
                                           , p_init_msg_list           => fnd_api.g_false
                                           , p_return_values           => fnd_api.g_false
                                           , p_commit                  => fnd_api.g_false
                                           , x_return_status           => x_return_status
                                           , x_msg_count               => x_msg_count
                                           , x_msg_data                => x_msg_data
                                           , p_trohdr_rec              => l_hdr_rec
                                           , p_trolin_tbl              => l_line_tbl
                                           , x_trohdr_rec              => x_hdr_rec
                                           , x_trohdr_val_rec          => x_hdr_val_rec
                                           , x_trolin_tbl              => x_line_tbl
                                           , x_trolin_val_tbl          => x_line_val_tbl
                                            );
      DBMS_OUTPUT.put_line (‘Return Status is :’ || x_return_status);
      DBMS_OUTPUT.put_line (‘Message Count is :’ || x_msg_count);
      DBMS_OUTPUT.put_line (‘Move Order Number is :’ || x_hdr_rec.request_number);
      DBMS_OUTPUT.put_line (‘Move Order ID is :’ || x_hdr_rec.header_id);
      DBMS_OUTPUT.put_line (‘Number of Lines Created are :’ || x_line_tbl.COUNT);

      IF x_return_status = ‘S’
      THEN
         COMMIT;
      ELSE
         ROLLBACK;
      END IF;

      IF x_msg_count > 0
      THEN
         FOR v_index IN 1 .. x_msg_count
         LOOP
            fnd_msg_pub.get (p_msg_index => v_index, p_encoded => ‘F’, p_data => x_msg_data, p_msg_index_out => v_msg_index_out);
            x_msg_data := SUBSTR (x_msg_data, 1, 200);
            DBMS_OUTPUT.put_line (x_msg_data);
            DBMS_OUTPUT.put_line (‘============================================================’);
         END LOOP;
      END IF;
   END;

   PROCEDURE allocate_move_order (
      p_line_tbl        IN       inv_move_order_pub.trolin_tbl_type
    , x_return_status   OUT      VARCHAR2
    , x_msg_data        OUT      VARCHAR2
    , x_msg_count       OUT      NUMBER
   )
   IS
      x_line_tbl        inv_move_order_pub.trolin_tbl_type;
      l_trolin_tbl      inv_move_order_pub.trolin_tbl_type;
      l_mold_tbl        inv_mo_line_detail_util.g_mmtt_tbl_type;
      l_qty_detailed    NUMBER;
      l_qty_delivered   NUMBER;
      l_return_status   VARCHAR2 (1);
      v_msg_index_out   NUMBER;
      l_rsr_type        inv_reservation_global.mtl_reservation_tbl_type;
      i                 INTEGER;
      l_trolin_rec      inv_move_order_pub.trolin_rec_type;
   BEGIN
      x_line_tbl := p_line_tbl;

      IF x_line_tbl.COUNT > 0
      THEN
         FOR j IN x_line_tbl.FIRST .. x_line_tbl.LAST
         LOOP
            DBMS_OUTPUT.put_line (x_line_tbl (j).line_id);

            BEGIN
               inv_ppengine_pvt.create_suggestions (p_api_version              => 1.0
                                                  , p_init_msg_list            => fnd_api.g_false
                                                  , p_commit                   => fnd_api.g_false
                                                  , p_validation_level         => fnd_api.g_valid_level_none
                                                  , x_return_status            => x_return_status
                                                  , x_msg_count                => x_msg_count
                                                  , x_msg_data                 => x_msg_data
                                                  , p_transaction_temp_id      => x_line_tbl (j).line_id
                                                  , p_reservations             => l_rsr_type
                                                  , p_suggest_serial           => fnd_api.g_true
                                                  , p_plan_tasks               => FALSE
                                                  , p_quick_pick_flag          => ‘N’
                                                  , p_organization_id          => 207
                                                   );
               DBMS_OUTPUT.put_line (‘Return Status is :’ || x_return_status);
               DBMS_OUTPUT.put_line (‘Message Count is :’ || x_msg_count);

               IF x_return_status = ‘S’
               THEN
                  BEGIN
                     l_trolin_tbl := x_line_tbl;

                     IF (l_trolin_tbl.COUNT <> 0)
                     THEN
                        i := l_trolin_tbl.FIRST;

                        WHILE i IS NOT NULL
                        LOOP
                           IF (    l_trolin_tbl (i).return_status <> fnd_api.g_ret_sts_unexp_error
                               AND l_trolin_tbl (i).return_status <> fnd_api.g_ret_sts_error
                              )
                           THEN
                              l_trolin_rec := inv_trolin_util.query_row (l_trolin_tbl (i).line_id);
                              l_trolin_tbl (i) := l_trolin_rec;
                              l_qty_detailed := l_trolin_tbl (i).quantity_detailed;
                              l_qty_delivered := NVL (l_trolin_tbl (i).quantity_delivered, 0);

                              IF NVL (l_qty_detailed, 0) = 0
                              THEN
                                 l_mold_tbl := inv_mo_line_detail_util.query_rows (p_line_id => l_trolin_tbl (i).line_id);

                                 FOR j IN 1 .. l_mold_tbl.COUNT
                                 LOOP
                                    l_mold_tbl (j).transaction_status := 3;
                                    l_mold_tbl (j).transaction_mode := 1;
                                    l_mold_tbl (j).source_line_id := l_trolin_tbl (i).line_id;
                                    inv_mo_line_detail_util.update_row (l_return_status, l_mold_tbl (j));
                                 END LOOP;

                                 SELECT transaction_header_id, transaction_quantity
                                   INTO l_trolin_tbl (i).transaction_header_id, l_trolin_tbl (i).quantity_detailed
                                   FROM mtl_material_transactions_temp
                                  WHERE move_order_line_id = l_trolin_tbl (i).line_id;

                                 l_trolin_tbl (i).last_update_date := SYSDATE;
                                 l_trolin_tbl (i).last_update_login := fnd_global.login_id;

                                 IF l_trolin_tbl (i).last_update_login = -1
                                 THEN
                                    l_trolin_tbl (i).last_update_login := fnd_global.conc_login_id;
                                 END IF;

                                 l_trolin_tbl (i).last_updated_by := fnd_global.user_id;
                                 l_trolin_tbl (i).program_id := fnd_global.conc_program_id;
                                 l_trolin_tbl (i).program_update_date := SYSDATE;
                                 l_trolin_tbl (i).request_id := fnd_global.conc_request_id;
                                 l_trolin_tbl (i).program_application_id := fnd_global.prog_appl_id;
                                 inv_trolin_util.update_row (l_trolin_tbl (i));
                              END IF;
                           END IF;

                           i := l_trolin_tbl.NEXT (i);
                        END LOOP;
                     END IF;
                  END;
               ELSE
                  ROLLBACK;
               END IF;

               IF x_msg_count > 0
               THEN
                  FOR v_index IN 1 .. x_msg_count
                  LOOP
                     fnd_msg_pub.get (p_msg_index          => v_index
                                    , p_encoded            => ‘F’
                                    , p_data               => x_msg_data
                                    , p_msg_index_out      => v_msg_index_out
                                     );
                     x_msg_data := SUBSTR (x_msg_data, 1, 200);
                     DBMS_OUTPUT.put_line (x_msg_data);
                     DBMS_OUTPUT.put_line (‘============================================================’);
                  END LOOP;
               END IF;
            END;
         END LOOP;
      END IF;
   END;

   PROCEDURE transact_move_order (p_move_order_id IN NUMBER, x_return_status OUT VARCHAR2)
   IS
      l_header_id        NUMBER;
      l_program          VARCHAR2 (100);
      l_func             VARCHAR2 (100);
      l_args             VARCHAR2 (100);
      p_timeout          NUMBER;
      l_old_tm_success   BOOLEAN;
      l_rc_field         NUMBER;

      CURSOR c1 (p_header_id IN NUMBER)
      IS
         SELECT transaction_header_id
           FROM mtl_material_transactions_temp
          WHERE transaction_source_id = p_header_id;
   BEGIN
      FOR i IN c1 (p_move_order_id)
      LOOP
         l_program := ‘INXTPU’;
         l_func := l_program;
         l_args := l_program || ‘ ‘ || ‘TRANS_HEADER_ID=’ || TO_CHAR (i.transaction_header_id);
         p_timeout := 500;
         COMMIT;
         l_old_tm_success :=
            inv_pick_wave_pick_confirm_pub.inv_tm_launch (program      => l_program
                                                        , args         => l_args
                                                        , TIMEOUT      => p_timeout
                                                        , rtval        => l_rc_field
                                                         );

         IF l_old_tm_success
         THEN
            x_return_status := ‘S’;
            DBMS_OUTPUT.put_line (‘Result is :’ || ‘Success’);
         ELSE
            x_return_status := ‘E’;
            DBMS_OUTPUT.put_line (‘Result is :’ || ‘Failed’);
         END IF;

         IF x_return_status = ‘S’
         THEN
            COMMIT;
         ELSE
            ROLLBACK;
         END IF;
      END LOOP;
   END;
BEGIN
   mo_global.set_policy_context (‘S’, 204);
   inv_globals.set_org_id (207);
   fnd_global.apps_initialize (1005902, 50583, 401);
   DBMS_OUTPUT.put_line (‘Creating Move Order’);
   create_move_order (l_header_rec, l_line_tbl, x_return_status, x_msg_data, x_msg_count);

   IF x_return_status = ‘S’
   THEN
      COMMIT;
      DBMS_OUTPUT.put_line (‘Move Order Created’);

      IF l_line_tbl.COUNT > 0
      THEN
         x_return_status := NULL;
         x_msg_data := NULL;
         x_msg_count := NULL;
         DBMS_OUTPUT.put_line (‘Allocating Move Order’);
         allocate_move_order (l_line_tbl, x_return_status, x_msg_data, x_msg_count);

         IF x_return_status = ‘S’
         THEN
            COMMIT;
            DBMS_OUTPUT.put_line (‘Move Order Allocated’);
            x_return_status := NULL;
            DBMS_OUTPUT.put_line (‘Transacting Move Order’);
            DBMS_OUTPUT.put_line (‘l_header_rec.header_id :’ || l_header_rec.header_id);
            transact_move_order (l_header_rec.header_id, x_return_status);

            IF x_return_status = ‘S’
            THEN
               COMMIT;
               DBMS_OUTPUT.put_line (‘Move Order Transacted’);
            END IF;
         END IF;
      END IF;
   END IF;
END;

/by
, , , , , , , ,

Oracle R12 Transact Order using APIs

DECLARE
   x_return_status   VARCHAR2 (1);

   PROCEDURE transact_move_order (p_move_order_id IN NUMBER, x_return_status OUT VARCHAR2)
   IS
      l_header_id        NUMBER;
      l_program          VARCHAR2 (100);
      l_func             VARCHAR2 (100);
      l_args             VARCHAR2 (100);
      p_timeout          NUMBER;
      l_old_tm_success   BOOLEAN;
      l_rc_field         NUMBER;

      CURSOR c1 (p_header_id IN NUMBER)
      IS
         SELECT transaction_header_id
           FROM mtl_material_transactions_temp
          WHERE transaction_source_id = p_header_id;
   BEGIN
      mo_global.set_policy_context (‘S’, 204);
      inv_globals.set_org_id (207);
      fnd_global.apps_initialize (1005902, 50583, 401);

      FOR i IN c1 (p_move_order_id)
      LOOP
         l_program := ‘INXTPU’;
         l_func := l_program;
         l_args := l_program || ‘ ‘ || ‘TRANS_HEADER_ID=’ || TO_CHAR (i.transaction_header_id);
         p_timeout := 500;
         COMMIT;
         l_old_tm_success :=
            inv_pick_wave_pick_confirm_pub.inv_tm_launch (program      => l_program
                                                        , args         => l_args
                                                        , TIMEOUT      => p_timeout
                                                        , rtval        => l_rc_field
                                                         );

         IF l_old_tm_success
         THEN
            x_return_status := ‘S’;
            DBMS_OUTPUT.put_line (‘Result is :’ || ‘Success’);
         ELSE
            x_return_status := ‘E’;
            DBMS_OUTPUT.put_line (‘Result is :’ || ‘Failed’);
         END IF;

         IF x_return_status = ‘S’
         THEN
            COMMIT;
         ELSE
            ROLLBACK;
         END IF;
      END LOOP;
   END;
BEGIN
   transact_move_order (2055447, x_return_status);
   COMMIT;
END;

/by