Oracle Apps R12 AR Invoice Transaction API for Single

With Cursor : Oracle Apps R12 AR Invoice Transaction API (With Cursor) Multiple

DECLARE
   v_customer_id             NUMBER := 8980620;
   v_service_name            VARCHAR2(200) := 'XX- Customer Name';
   v_costshare_value         NUMBER := 1000;
   v_user_id                 NUMBER := 26051;
   v_resp_id                 NUMBER := 55556;
   v_org_id                  NUMBER := 1300;
   v_date                    DATE := TO_DATE('30-JUL-2018');
   --v_trx_number        OUT   NUMBER
   v_trx_number           NUMBER;
--)AS
   l_return_status           VARCHAR2 (1);
   l_msg_count               NUMBER;
   l_msg_data                VARCHAR2 (2000);
   l_batch_id                NUMBER;
   l_batch_source_rec        ar_invoice_api_pub.batch_source_rec_type;
   l_trx_header_tbl          ar_invoice_api_pub.trx_header_tbl_type;
   l_trx_lines_tbl           ar_invoice_api_pub.trx_line_tbl_type;
   l_trx_dist_tbl            ar_invoice_api_pub.trx_dist_tbl_type;
   l_trx_salescredits_tbl    ar_invoice_api_pub.trx_salescredits_tbl_type;
   l_trx_contingencies_tbl   ar_invoice_api_pub.trx_contingencies_tbl_type;
   trx_header_id_v           NUMBER;
   trx_line_id_v             NUMBER;
   trx_dist_id_v             NUMBER;
   CURSOR cbatch
   IS
      SELECT customer_trx_id
        FROM ra_customer_trx_all
       WHERE batch_id = l_batch_id;
   CURSOR cvalidtxn
   IS
      SELECT trx_header_id
        FROM ar_trx_header_gt
       WHERE trx_header_id NOT IN (SELECT trx_header_id
                                     FROM ar_trx_errors_gt);
 

BEGIN
     SELECT xx_invoice_header.NEXTVAL
     INTO trx_header_id_v
     FROM DUAL;
   SELECT xx_invoice_line.NEXTVAL
     INTO trx_line_id_v
     FROM DUAL;
   SELECT xx_invoice_dist.NEXTVAL
     INTO trx_dist_id_v
     FROM DUAL;
   fnd_global.apps_initialize (v_user_id, v_resp_id, 222);
   mo_global.init ('AR');
   mo_global.set_policy_context ('S', v_org_id);
   xla_security_pkg.set_security_context (222);
   l_batch_source_rec.batch_source_id := 4048;
   l_trx_header_tbl (1).trx_header_id := trx_header_id_v;
   l_trx_header_tbl (1).bill_to_customer_id := v_customer_id;           --1042
   l_trx_header_tbl (1).cust_trx_type_id := 1420;
   l_trx_header_tbl (1).trx_date := v_date;   --'10-AUG-2006';--'30-MAY-2006';
   l_trx_header_tbl (1).trx_currency := 'AED';
   l_trx_header_tbl (1).term_id := 5;
   --l_trx_header_tbl (1).default_tax_exempt_flag := 'E';
   l_trx_header_tbl (1).legal_entity_id := 21178;
   l_trx_header_tbl (1).finance_charges := NULL;
   l_trx_header_tbl (1).status_trx := 'OP';
   l_trx_header_tbl (1).printing_option := 'PRI';
   --l_trx_header_tbl (1).set_of_books_id := 12345;
   --l_trx_header_tbl (1).complete_flag := 'Y';
   l_trx_lines_tbl (1).trx_header_id := trx_header_id_v;
   l_trx_lines_tbl (1).trx_line_id := trx_line_id_v;
   l_trx_lines_tbl (1).line_number := 1;
   l_trx_lines_tbl (1).description := v_service_name;
   l_trx_lines_tbl (1).quantity_invoiced := 1;
   l_trx_lines_tbl (1).unit_selling_price := v_costshare_value;
   l_trx_lines_tbl (1).line_type := 'LINE';
   --l_trx_lines_tbl (1).tax_exempt_flag := 'E';
   --l_trx_lines_tbl (1).TAX_EXEMPT_REASON_CODE := 'E';
   --l_trx_lines_tbl (1).TAX_EXEMPT_REASON_CODE_MEANING := 'E';
   l_trx_lines_tbl (1).memo_line_id := 46160;
--   l_trx_lines_tbl (1).TAX_PRECEDENCE := 10;
--   l_trx_lines_tbl (1).TAX_RATE := 10;
   l_trx_dist_tbl (1).trx_dist_id := trx_dist_id_v;
   l_trx_dist_tbl (1).trx_line_id := trx_line_id_v;
   l_trx_dist_tbl (1).account_class := 'REV';
   l_trx_dist_tbl (1).PERCENT := 100;
   l_trx_dist_tbl (1).code_combination_id := 3361155;
   ar_invoice_api_pub.create_invoice
                            (p_api_version               => 1.0,
                             p_batch_source_rec          => l_batch_source_rec,
                             p_trx_header_tbl            => l_trx_header_tbl,
                             p_trx_lines_tbl             => l_trx_lines_tbl,
                             p_trx_dist_tbl              => l_trx_dist_tbl,
                             p_trx_salescredits_tbl      => l_trx_salescredits_tbl,
                             x_return_status             => l_return_status,
                             x_msg_count                 => l_msg_count,
                             x_msg_data                  => l_msg_data
                            );
   DBMS_OUTPUT.put_line (l_return_status);
   DBMS_OUTPUT.put_line (l_msg_data);
   IF    l_return_status = fnd_api.g_ret_sts_error
      OR l_return_status = fnd_api.g_ret_sts_unexp_error
   THEN
      DBMS_OUTPUT.put_line ('unexpected errors found!');
   ELSE
      FOR cvalidtxnrec IN cvalidtxn
      LOOP
         IF (ar_invoice_api_pub.g_api_outputs.batch_id IS NOT NULL)
         THEN
            DBMS_OUTPUT.put_line ('Invoice(s) suceessfully created!');
            DBMS_OUTPUT.put_line (   'Batch ID: '
                                  || ar_invoice_api_pub.g_api_outputs.batch_id
                                 );
            l_batch_id := ar_invoice_api_pub.g_api_outputs.batch_id;
            FOR cbatchrec IN cbatch
            LOOP
               DBMS_OUTPUT.put_line (   'Cust Trx Id '
                                     || cbatchrec.customer_trx_id
                                    );
               v_trx_number := cbatchrec.customer_trx_id;
            END LOOP;
         ELSE
            DBMS_OUTPUT.put_line ('Errors found!');
         END IF;
      END LOOP;
   END IF;
END;
/

Oracle Apps R12 AR Invoice Transaction API (With Cursor) Multiple

CREATE OR REPLACE PROCEDURE APPS.xxcreate_invoice_ar (
P_VEHICLE_NO VARCHAR2,
P_DATE DATE,
P_CUSTOMER_CODE VARCHAR2,
P_RESULT OUT VARCHAR2,
P_INVOICE_ID OUT NUMBER,
P_INVOICE_NUM OUT VARCHAR2
) AS

   v_customer_id             NUMBER;--:= 8980620;
   L_VEHICLE_NO        varchar2(200) := P_VEHICLE_NO;
   v_user_id                 NUMBER := 2605;
   v_resp_id                 NUMBER := 52328;
   v_org_id                  NUMBER := 1930;
   v_date                    DATE := P_DATE;
   v_trx_number           NUMBER;
   l_return_status           VARCHAR2 (1);
   l_msg_count               NUMBER;
   l_msg_data                VARCHAR2 (2000);
   l_batch_id                NUMBER;
   l_batch_source_rec        ar_invoice_api_pub.batch_source_rec_type;
   l_trx_header_tbl          ar_invoice_api_pub.trx_header_tbl_type;
   l_trx_lines_tbl           ar_invoice_api_pub.trx_line_tbl_type;
   l_trx_dist_tbl            ar_invoice_api_pub.trx_dist_tbl_type;
   l_trx_salescredits_tbl    ar_invoice_api_pub.trx_salescredits_tbl_type;
   l_trx_contingencies_tbl   ar_invoice_api_pub.trx_contingencies_tbl_type;
   trx_header_id_v           NUMBER;
   trx_line_id_v             NUMBER;
   trx_dist_id_v             NUMBER;
   v_line                    number := 1;
   v_line_count              number :=0;
 
   CURSOR cbatch
   IS
      SELECT customer_trx_id
        FROM ra_customer_trx_all
       WHERE batch_id = l_batch_id;
 
   CURSOR cvalidtxn
   IS
      SELECT trx_header_id
        FROM ar_trx_header_gt
       WHERE trx_header_id NOT IN (SELECT trx_header_id
                                     FROM ar_trx_errors_gt);

  CURSOR line_items is

 SELECT MEMO_LINE_ID,NAME,PRICE,QTY
 ,(SELECT GL_ID_REV FROM AR_MEMO_LINES_ALL_VL WHERE MEMO_LINE_ID =XXF.MEMO_LINE_ID AND ATTRIBUTE1 IS NOT NULL )GL_ID
 FROM XXF_SERVICES_D XXF
 WHERE VEHICLE_NO = P_VEHICLE_NO;--20180701141;

 
BEGIN

     SELECT CUSTOMER_ID
      INTO v_customer_id
      FROM AR_CUSTOMERS
      WHERE CUSTOMER_NUMBER = P_CUSTOMER_CODE;

     SELECT xx_invoice_header.NEXTVAL
     INTO trx_header_id_v
     FROM DUAL;
   /*SELECT xx_invoice_line.NEXTVAL
     INTO trx_line_id_v
     FROM DUAL;
   SELECT xx_invoice_dist.NEXTVAL
     INTO trx_dist_id_v
     FROM DUAL;*/
   fnd_global.apps_initialize (v_user_id, v_resp_id, 222);
   mo_global.init ('AR');
   mo_global.set_policy_context ('S', v_org_id);
   xla_security_pkg.set_security_context (222);
   l_batch_source_rec.batch_source_id := 4048;
   l_trx_header_tbl (1).trx_header_id := trx_header_id_v;
   l_trx_header_tbl (1).bill_to_customer_id := v_customer_id;           --1042
   l_trx_header_tbl (1).cust_trx_type_id := 1420;
   l_trx_header_tbl (1).trx_date := v_date;   --'10-AUG-2006';--'30-MAY-2006';
   l_trx_header_tbl (1).trx_currency := 'AED';
   l_trx_header_tbl (1).term_id := 5;
   --l_trx_header_tbl (1).default_tax_exempt_flag := 'E';
   l_trx_header_tbl (1).legal_entity_id := 27278;
   l_trx_header_tbl (1).finance_charges := NULL;
   l_trx_header_tbl (1).status_trx := 'OP';
   l_trx_header_tbl (1).printing_option := 'PRI';
   --l_trx_header_tbl (1).set_of_books_id := 12345;
   --l_trx_header_tbl (1).complete_flag := 'Y';
 
   for i in line_items loop
   v_line_count := v_line_count + v_line;
   l_trx_lines_tbl (v_line_count).trx_header_id := trx_header_id_v;
   l_trx_lines_tbl (v_line_count).trx_line_id := xx_invoice_line.NEXTVAL;--trx_line_id_v;
   l_trx_lines_tbl (v_line_count).line_number := v_line_count;
   l_trx_lines_tbl (v_line_count).description := i.name;
   l_trx_lines_tbl (v_line_count).quantity_invoiced := i.qty;
   l_trx_lines_tbl (v_line_count).unit_selling_price := i.price;
   l_trx_lines_tbl (v_line_count).line_type := 'LINE';
   --l_trx_lines_tbl (1).tax_exempt_flag := 'E';
   --l_trx_lines_tbl (1).TAX_EXEMPT_REASON_CODE := 'E';
   --l_trx_lines_tbl (1).TAX_EXEMPT_REASON_CODE_MEANING := 'E';
   l_trx_lines_tbl (v_line_count).memo_line_id := i.memo_line_id;
--   l_trx_lines_tbl (1).TAX_PRECEDENCE := 10;
--   l_trx_lines_tbl (1).TAX_RATE := 10;
   l_trx_dist_tbl (v_line_count).trx_dist_id := xx_invoice_dist.NEXTVAL;
   l_trx_dist_tbl (v_line_count).trx_line_id := xx_invoice_line.CURRVAL;
   l_trx_dist_tbl (v_line_count).account_class := 'REV';
   l_trx_dist_tbl (v_line_count).PERCENT := 100;
   l_trx_dist_tbl (v_line_count).code_combination_id := i.gl_id;
   end loop;
   ar_invoice_api_pub.create_invoice
                            (p_api_version               => 1.0,
                             p_batch_source_rec          => l_batch_source_rec,
                             p_trx_header_tbl            => l_trx_header_tbl,
                             p_trx_lines_tbl             => l_trx_lines_tbl,
                             p_trx_dist_tbl              => l_trx_dist_tbl,
                             p_trx_salescredits_tbl      => l_trx_salescredits_tbl,
                             x_return_status             => l_return_status,
                             x_msg_count                 => l_msg_count,
                             x_msg_data                  => l_msg_data
                            );
   DBMS_OUTPUT.put_line (l_return_status);
   DBMS_OUTPUT.put_line (l_msg_data);
   P_RESULT:= l_return_status||'-'||l_msg_data;
   IF    l_return_status = fnd_api.g_ret_sts_error
      OR l_return_status = fnd_api.g_ret_sts_unexp_error
   THEN
      DBMS_OUTPUT.put_line ('unexpected errors found!');
   ELSE
      FOR cvalidtxnrec IN cvalidtxn
      LOOP
         IF (ar_invoice_api_pub.g_api_outputs.batch_id IS NOT NULL)
         THEN
            DBMS_OUTPUT.put_line ('Invoice(s) suceessfully created!');
            DBMS_OUTPUT.put_line (   'Batch ID: '
                                  || ar_invoice_api_pub.g_api_outputs.batch_id
                                 );
            l_batch_id := ar_invoice_api_pub.g_api_outputs.batch_id;
            FOR cbatchrec IN cbatch
            LOOP
               DBMS_OUTPUT.put_line (   'Cust Trx Id '
                                     || cbatchrec.customer_trx_id
                                    );
               v_trx_number := cbatchrec.customer_trx_id;
               DBMS_OUTPUT.PUT_LINE('INVOICE_ID->'||v_trx_number||'-INVOICE_NUMBER->'||trx_header_id_v);
               P_INVOICE_ID := v_trx_number;
               P_INVOICE_NUM := trx_header_id_v;
            END LOOP;
         ELSE
            DBMS_OUTPUT.put_line ('Errors found!');
           
         END IF;
      END LOOP;
   END IF;
   COMMIT;
END;
/

Oracle Apps R12 Transact Move Order in Inventory API

--Transact Move order from here

PROCEDURE xxcona_alloc_move_order (
      p_move_order_line_id   IN       NUMBER,
      x_return_status        OUT      VARCHAR2,
      x_detailed_qty         OUT      NUMBER
   )
   IS

-- WHO columns
   l_user_id          NUMBER        := -1;
   l_resp_id          NUMBER        := -1;
   l_application_id   NUMBER        := -1;
   l_user_name        VARCHAR2 (30) := 'XX_AFZAL';
   l_resp_name        VARCHAR2 (30) := 'xx Inventory';
      -- Common Declarations
      l_api_version            NUMBER                                  := 1.0;
      l_init_msg_list          VARCHAR2 (2)                 := fnd_api.g_true;
      l_return_values          VARCHAR2 (2)                := fnd_api.g_false;
      l_commit                 VARCHAR2 (2)                := fnd_api.g_false;
      --x_return_status          VARCHAR2 (2);
      x_msg_count              NUMBER                                    := 0;
      x_msg_data               VARCHAR2 (255);
      -- WHO columns
      --l_user_id                NUMBER                                   := -1;
      --l_resp_id                NUMBER                                   := -1;
      --l_application_id         NUMBER                                   := -1;
      l_row_cnt                NUMBER                                    := 1;
       --l_user_name              VARCHAR2 (30)                    := 'CONACENT';
      --l_resp_name              VARCHAR2 (30)                   := 'INVENTORY';
       -- API specific declarations
      l_trohdr_rec             inv_move_order_pub.trohdr_rec_type;
      l_trohdr_val_rec         inv_move_order_pub.trohdr_val_rec_type;
      x_trohdr_rec             inv_move_order_pub.trohdr_rec_type;
      x_trohdr_val_rec         inv_move_order_pub.trohdr_val_rec_type;
      l_validation_flag        VARCHAR2 (2)
                                       := inv_move_order_pub.g_validation_yes;
      l_trolin_tbl             inv_move_order_pub.trolin_tbl_type;
      l_trolin_val_tbl         inv_move_order_pub.trolin_val_tbl_type;
      x_trolin_tbl             inv_move_order_pub.trolin_tbl_type;
      x_trolin_val_tbl         inv_move_order_pub.trolin_val_tbl_type;
      l_line_id                mtl_txn_request_lines.line_id%TYPE
                                                      := p_move_order_line_id;
      l_move_order_type        mtl_txn_request_headers.move_order_type%TYPE
                                                                         := 1;
      --x_detailed_qty           NUMBER                                    := 0;
      x_number_of_rows         NUMBER                                    := 0;
      x_revision               VARCHAR2 (3);
      x_locator_id             NUMBER                                    := 0;
      x_transfer_to_location   NUMBER                                    := 0;
      x_lot_number             VARCHAR2 (30);
      x_expiration_date        DATE;
      x_transaction_temp_id    NUMBER                                    := 0;
   BEGIN
      -- Get the user_id
      /*SELECT user_id
        INTO l_user_id
        FROM fnd_user
       WHERE user_name = l_user_name;

      -- Get the application_id and responsibility_id
      SELECT application_id, responsibility_id
        INTO l_application_id, l_resp_id
        FROM fnd_responsibility
       WHERE responsibility_key = l_resp_name;

      fnd_global.apps_initialize (l_user_id, l_resp_id, l_application_id);
      -- MFG / Mfg Mgr / INV
      DBMS_OUTPUT.put_line (   'Initialized applications context: '
                            || l_user_id
                            || ' '
                            || l_resp_id
                            || ' '
                            || l_application_id
                           );*/
      -- Allocate each line of the Move Order
      inv_replenish_detail_pub.line_details_pub
                           (p_line_id                    => l_line_id,
                            x_number_of_rows             => x_number_of_rows,
                            x_detailed_qty               => x_detailed_qty,
                            x_return_status              => x_return_status,
                            x_msg_count                  => x_msg_count,
                            x_msg_data                   => x_msg_data,
                            x_revision                   => x_revision,
                            x_locator_id                 => x_locator_id,
                            x_transfer_to_location       => x_transfer_to_location,
                            x_lot_number                 => x_lot_number,
                            x_expiration_date            => x_expiration_date,
                            x_transaction_temp_id        => x_transaction_temp_id,
                            p_transaction_header_id      => NULL,
                            p_transaction_mode           => NULL,
                            p_move_order_type            => l_move_order_type,
                            p_serial_flag                => fnd_api.g_false,
                            p_plan_tasks                 => FALSE
                                                                 --FND_API.G_FALSE
      ,
                            p_auto_pick_confirm          => FALSE
                                                                 --FND_API.G_FALSE
      ,
                            p_commit                     => TRUE
                           --FND_API.G_FALSE
                           );
      DBMS_OUTPUT.put_line
                 ('==========================================================');
      DBMS_OUTPUT.put_line ('Return Status: ' || x_return_status);

      IF (x_return_status <> fnd_api.g_ret_sts_success)
      THEN
         DBMS_OUTPUT.put_line ('Error Message :' || x_msg_data);
      END IF;

      IF (x_return_status = fnd_api.g_ret_sts_success)
      THEN
         COMMIT;
         DBMS_OUTPUT.put_line ('Detailed Qty: ' || x_detailed_qty);
         DBMS_OUTPUT.put_line ('Number of rows: ' || x_number_of_rows);
         DBMS_OUTPUT.put_line ('Trx temp ID: ' || x_transaction_temp_id);
      END IF;

      DBMS_OUTPUT.put_line
                 ('==========================================================');
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Exception Occured :');
         DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);
         DBMS_OUTPUT.put_line
                   ('=======================================================');
   END xxcona_alloc_move_order;

   PROCEDURE xxcona_process_move_order_line (
      p_move_order_line_id      IN       NUMBER,
      p_new_quantity_detailed   IN       NUMBER,
      x_return_status           OUT      VARCHAR2
   )
   IS
      -- Main parameters -- Move order to update, and new quantity
      l_new_quantity_detailed   NUMBER             := p_new_quantity_detailed;
      l_mo_line_id              NUMBER                := p_move_order_line_id;
      -- User Information
      --l_user_id                 NUMBER                             := -1;
      --l_user_name               VARCHAR2 (20)                   := 'CONACENT';
      --l_resp_id                 NUMBER                             := -1;
      --l_resp_name               VARCHAR2 (40)                  := 'INVENTORY';
      --l_application_id          NUMBER                             := -1;
      l_rowcnt                  NUMBER                             := 1;
      -- Errors
      --x_return_status           VARCHAR2 (10);
      x_msg_count               NUMBER;
      x_msg_data                VARCHAR2 (255);
      x_message_list            error_handler.error_tbl_type;
      -- Move order variables
      l_trolin_tbl              inv_move_order_pub.trolin_tbl_type;
      l_trolin_old_tbl          inv_move_order_pub.trolin_tbl_type;
      x_trolin_tbl              inv_move_order_pub.trolin_tbl_type;
      l_mo_line_rec             inv_move_order_pub.trolin_rec_type;
   BEGIN
  
      l_mo_line_rec := inv_trolin_util.query_row (p_line_id => l_mo_line_id);
      l_trolin_tbl (1) := l_mo_line_rec;
      l_trolin_old_tbl (1) := l_mo_line_rec;
      l_trolin_tbl (1).operation := 'UPDATE';    -- INV_GLOBALS.G_OPR_UPDATE;
      l_trolin_tbl (1).quantity_detailed := l_new_quantity_detailed;
      inv_move_order_pub.process_move_order_line
                                       (p_api_version_number      => 1.0,
                                        p_init_msg_list           => fnd_api.g_true,
                                        p_return_values           => fnd_api.g_false
                                                                                    -- Convert ids to values
      ,
                                        p_commit                  => fnd_api.g_false
                                                                                    --FND_API.G_FALSE
      ,
                                        x_return_status           => x_return_status,
                                        x_msg_count               => x_msg_count,
                                        x_msg_data                => x_msg_data,
                                        p_trolin_tbl              => l_trolin_tbl,
                                        p_trolin_old_tbl          => l_trolin_old_tbl,
                                        x_trolin_tbl              => x_trolin_tbl
                                       );
      DBMS_OUTPUT.put_line ('=========================================');
      DBMS_OUTPUT.put_line ('Return Status: ' || x_return_status);

      IF (x_return_status <> fnd_api.g_ret_sts_success)
      THEN
         DBMS_OUTPUT.put_line ('x_msg_data :' || x_msg_data);
         DBMS_OUTPUT.put_line ('Error Messages :');
         error_handler.get_message_list (x_message_list => x_message_list);

         FOR i IN 1 .. x_message_list.COUNT
         LOOP
            DBMS_OUTPUT.put_line (x_message_list (i).MESSAGE_TEXT);
         END LOOP;
      ELSE
         COMMIT;
      END IF;

      DBMS_OUTPUT.put_line ('=========================================');
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Exception Occured :');
         DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);
         DBMS_OUTPUT.put_line ('========================================');
   END xxcona_process_move_order_line;

   PROCEDURE xxcona_transact_mo_line (
      p_header_id   IN   NUMBER,
      p_line_id     IN   NUMBER
   )
   IS
      -- Common Declarations
      l_api_version        NUMBER                                  := 1.0;
      l_init_msg_list      VARCHAR2 (2)                     := fnd_api.g_true;
      l_commit             VARCHAR2 (2)                    := fnd_api.g_false;
      x_return_status      VARCHAR2 (2);
      x_msg_count          NUMBER                                  := 0;
      x_msg_data           VARCHAR2 (255);
      -- API specific declarations
      l_move_order_type    NUMBER                                  := 1;
      l_transaction_mode   NUMBER                                  := 1;
      l_trolin_tbl         inv_move_order_pub.trolin_tbl_type;
      l_mold_tbl           inv_mo_line_detail_util.g_mmtt_tbl_type;
      x_mmtt_tbl           inv_mo_line_detail_util.g_mmtt_tbl_type;
      x_trolin_tbl         inv_move_order_pub.trolin_tbl_type;
      l_transaction_date   DATE                                    := SYSDATE;
      -- WHO columns
      --l_user_id            NUMBER                                  := -1;
      --l_resp_id            NUMBER                                  := -1;
      --l_application_id     NUMBER                                  := -1;
      l_row_cnt            NUMBER                                  := 1;
   --l_user_name          VARCHAR2 (30)                        := 'CONACENT';
   --l_resp_name          VARCHAR2 (30)                       := 'INVENTORY';
   BEGIN
      -- Get the user_id
      /*SELECT user_id
        INTO l_user_id
        FROM fnd_user
       WHERE user_name = l_user_name;

      -- Get the application_id and responsibility_id
      SELECT application_id, responsibility_id
        INTO l_application_id, l_resp_id
        FROM fnd_responsibility
       WHERE responsibility_key = l_resp_name;

      fnd_global.apps_initialize (l_user_id, l_resp_id, l_application_id);
      DBMS_OUTPUT.put_line (   'Initialized applications context: '
                            || l_user_id
                            || ' '
                            || l_resp_id
                            || ' '
                            || l_application_id
                           );*/
      l_trolin_tbl (1).line_id := p_line_id;
      l_trolin_tbl (1).header_id := p_header_id;                     -- Added
      -- call API to create move order header
      DBMS_OUTPUT.put_line
                   ('=======================================================');
      DBMS_OUTPUT.put_line
                   ('Calling INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm API');
      inv_pick_wave_pick_confirm_pub.pick_confirm
                                   (p_api_version_number      => l_api_version,
                                    p_init_msg_list           => l_init_msg_list,
                                    p_commit                  => l_commit,
                                    x_return_status           => x_return_status,
                                    x_msg_count               => x_msg_count,
                                    x_msg_data                => x_msg_data,
                                    p_move_order_type         => l_move_order_type,
                                    p_transaction_mode        => l_transaction_mode,
                                    p_trolin_tbl              => l_trolin_tbl,
                                    p_mold_tbl                => l_mold_tbl,
                                    x_mmtt_tbl                => x_mmtt_tbl,
                                    x_trolin_tbl              => x_trolin_tbl,
                                    p_transaction_date        => l_transaction_date
                                   );
      DBMS_OUTPUT.put_line
                    ('=======================================================');
      DBMS_OUTPUT.put_line ('Return Status: ' || x_return_status);

      IF (x_return_status <> fnd_api.g_ret_sts_success)
      THEN
         DBMS_OUTPUT.put_line ('Error Message :' || x_msg_data);
      ELSE
         COMMIT;
      END IF;

      DBMS_OUTPUT.put_line
                    ('=======================================================');
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Exception Occured :');
         DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);
         DBMS_OUTPUT.put_line
                   ('=======================================================');
   END xxcona_transact_mo_line;

   PROCEDURE xxcona_main (
      p_move_order_no     IN   VARCHAR2,
      p_organization_id   IN   NUMBER
   )
   IS
      x_return_status   VARCHAR2 (2);
      x_detailed_qty    NUMBER;

      CURSOR move_order_cur
      IS
         SELECT mtrh.request_number, mtrh.organization_id, mtrh.header_id,
                mtrl.line_id, mtrh.move_order_type, mtrl.quantity,
                mtrl.quantity_delivered,
                (mtrl.quantity - mtrl.quantity_delivered) unallocated_qty
           FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl
          WHERE mtrh.organization_id = mtrl.organization_id
            AND mtrh.header_id = mtrl.header_id
            AND mtrh.request_number = p_move_order_no
            AND mtrh.organization_id = p_organization_id;
   BEGIN
      BEGIN
         -- Get the user_id
         SELECT user_id
           INTO l_user_id
           FROM fnd_user
          WHERE user_name = l_user_name;

         -- Get the application_id and responsibility_id
         SELECT application_id, responsibility_id
           INTO l_application_id, l_resp_id
           FROM fnd_responsibility
          WHERE responsibility_key = l_resp_name;

         fnd_global.apps_initialize (l_user_id, l_resp_id, l_application_id);
         DBMS_OUTPUT.put_line (   'Initialized applications context: '
                               || l_user_id
                               || ' '
                               || l_resp_id
                               || ' '
                               || l_application_id
                              );
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line
               ('Exception Occured : Not able to Initialize applications context'
               );
            DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);
            DBMS_OUTPUT.put_line
                    ('=======================================================');
      END;

      BEGIN
         FOR cur_rec IN move_order_cur
         LOOP
            xxcona_alloc_move_order (cur_rec.line_id,
                                     x_return_status,
                                     x_detailed_qty
                                    );

            IF     x_return_status = fnd_api.g_ret_sts_success
               AND x_detailed_qty > 0
            THEN
               x_return_status := NULL;                      --- Reset Status
               xxcona_process_move_order_line (cur_rec.line_id,
                                               x_detailed_qty,
                                               x_return_status
                                              );

               IF x_return_status = fnd_api.g_ret_sts_success
               THEN
                  xxcona_transact_mo_line (cur_rec.header_id,
                                           cur_rec.line_id);
               END IF;
            ELSE
               DBMS_OUTPUT.put_line
                  ('Exception Occured : Unable to allocate requested quantity.'
                  );
            END IF;
         END LOOP;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line
               ('Exception Occured : Unable to process the request --- Transact Move Orders'
               );
            DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);
            DBMS_OUTPUT.put_line
                    ('=======================================================');
      END;
   END xxcona_main;
--Transact move order ends here

Execute:

BEGIN
   xxcona_main (p_move_order_no        => '112',
                                            p_organization_id      => 1733
                                           );
END;

commit

External SOAP Webservice consume in .net application connection Oracle 11g database

 .net Forms Application

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OracleClient;
//using Oracle.ManagedDataAccess.Client;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml;
using System.Net;
using System.IO;
using System.Net.Mail;
namespace WindowsFormsApplication3
{

        private void Submit_Click(object sender, EventArgs e)
        {

            Cursor.Current = Cursors.WaitCursor;
            runCode();
            Cursor.Current = Cursors.Default;
            //MessageBox.Show("No code inside :)");
        }

  public void runCode()
        {
            OracleConnection myConnection = new OracleConnection();
            myConnection.ConnectionString = "Data Source=TEST;User Id=usrname1;Password=test1";
            try
            {
                myConnection.Open();
            }
            catch (Exception ex)
            {
                SendEmail("Error check connection with database ->" + ex.Message);
            }
            //execute queries
            //OracleDataAdapter oda = new OracleDataAdapter("select * from xml_document", myConnection);
            try
            {
                OracleDataAdapter oda = new OracleDataAdapter("select apps.test_function_f() from dual", myConnection);//getting xml output from the function with 3 values ex: newnumber+'S'(success) + xml output.
                string l_msg = "";
                string l_refno = "";
                string l_xml = "";
                DataTable dt = new DataTable();
                oda.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    string output = dt.Rows[0][0].ToString();
                    // string test2 = "FUJREF1907201826";// dt.Rows[0][0].ToString();
                    string[] xx = new string[3];
                    xx = output.Split(',');
                    l_msg = xx[0];
                    l_refno = xx[1];
                    l_xml = xx[2];
                    // MessageBox.Show(l_refno);
                    this.textBox2.Text = l_refno.ToString();

                    if (l_msg == "S")
                    {
                        SOAP_Request(l_xml);
                    }
                    else
                    {
                        SendEmail("Error check the soap webservice ->" + l_msg + l_refno);
                    }


                    /* XmlDocument xmltest = new XmlDocument();
                     xmltest.LoadXml(test);
                     XmlNodeList elemlist = xmltest.GetElementsByTagName("soapenv:Envelope");

                     string result = elemlist[0].OuterXml.ToString();//.InnerXml;
                     XmlDocument xmltest1 = new XmlDocument();
                     xmltest1.LoadXml(result);
                     XmlNodeList elemlist1 = xmltest1.GetElementsByTagName("loc:SerId");
                     result = elemlist1[0].InnerXml.ToString();
                     MessageBox.Show(result);
                     MessageBox.Show(elemlist.Count.ToString());
                     */
                }
            }
            catch (Exception ex)
            {
                SendEmail("Error check for body exception ->" + ex.Message);
            }
            myConnection.Close();
        }

        //consume soap client
        public void SOAP_Request(string data)
        {
            HttpWebRequest request = CreateWebRequest();
            XmlDocument soapEnvelopeXml = new XmlDocument();
            soapEnvelopeXml.LoadXml(@data);
            using (Stream stream = request.GetRequestStream())
            {
                soapEnvelopeXml.Save(stream);
            }

            using (WebResponse response = request.GetResponse())
            {
                using (StreamReader rd = new StreamReader(response.GetResponseStream()))
                {
                    string soapResult = rd.ReadToEnd();
                    this.soapResult.Text = soapResult;
                    SendEmail(this.soapResult.Text);
                }
            }
        }

        //sending response to the emails from soap service
        public void SendEmail(String response)
        {
             MailMessage mail = new MailMessage("test@testmail.com", "test2@testmail.com");
            SmtpClient client = new SmtpClient();
            client.Port = 55;
            client.DeliveryMethod = SmtpDeliveryMethod.Network;
            client.UseDefaultCredentials = false;
            client.Host = "10.1.1.1";//"smtp.gmail.com";
            mail.Subject = "Response from SOAP Webservice1->" + this.textBox2.Text;
            mail.Body = response;// "this is my test email body";
            client.Send(mail);
        }
       
        //soap link details
        public static HttpWebRequest CreateWebRequest()
        {
            HttpWebRequest webRequest = (HttpWebRequest)WebRequest.Create(@"http://test.com/services/EmpStatistics");
            webRequest.Headers.Add(@"SOAP:Action");
            webRequest.ContentType = "text/xml;charset=\"utf-8\"";
            webRequest.Accept = "text/xml";
            webRequest.Method = "POST";
            return webRequest;
        }
}
}

Oracle 11g -> SOAP Webservice consume (client) from plsql

DECLARE
  v_value        VARCHAR2(4000);
  soap_request   clob;-- can use varchar2 also but its limit is 32767
  http_req       UTL_HTTP.REQ;
  http_resp      UTL_HTTP.RESP;
  t_buffer       VARCHAR2(32767);
  t_http_version VARCHAR2(20)   := 'HTTP/1.1';
  t_content_type VARCHAR2(60)   := 'text/xml; charset=utf-8';
  t_url          VARCHAR2(1000) := 'http://test.com/TestEmpStatistics'; --- update this accordingly without '?wsdl'
  l_msg varchar2(2000);
  --l_soap_req clob;
  l_refno varchar2(20);

BEGIN

BEGIN
--SELECT XML_DOC INTO SOAP_REQUEST FROM XML_DOCUMENT;
   --xx_localempstatistics_p ('C', soap_request, l_msg, l_refno);--create procedure to give out as xml document,
   --check my blog how to create xml output, procedure name ' P_XML_TRANSACTION_DOCUMENT2'
--link : http://mogalafzal.blogspot.com/2018/04/apply-absence-sshr-transaction-and.html
   soap_request := '
<?xml version="1.0"?>

<soap:Envelope
xmlns:soap="http://www.w3.org/2003/05/soap-envelope/"
soap:encodingStyle="http://www.w3.org/2003/05/soap-encoding">

<soap:Header>
...
</soap:Header>

<soap:Body>
...
  <soap:Fault>
  ...
  </soap:Fault>
</soap:Body>

</soap:Envelope> ';
   DBMS_OUTPUT.PUT_LINE(l_refno);
   exception when others then
   dbms_output.put_line('exception under procedure begin');
END;
--soap_request := ;
  http_req := UTL_HTTP.BEGIN_REQUEST(t_url ,'POST' ,t_http_version);
  UTL_HTTP.SET_HEADER(http_req, 'User-Agent', 'Mozilla/4.0');
  UTL_HTTP.SET_HEADER(http_req, 'Content-Type', t_content_type);
  UTL_HTTP.SET_HEADER(http_req, 'Content-Length', lengthb(soap_request)); --lengthb for multilingual
  utl_http.set_header(http_req, 'SOAPAction', 'PushTestEmpStatistics');
  UTL_HTTP.WRITE_TEXT(http_req, soap_request);
  http_resp := UTL_HTTP.GET_RESPONSE(http_req);

  DBMS_OUTPUT.PUT_LINE('Response Status: ' ||http_resp.status_code||' ' || http_resp.reason_phrase);
  BEGIN
    LOOP
      UTL_HTTP.READ_TEXT(http_resp, t_buffer);
      DBMS_OUTPUT.PUT_LINE(substr(t_buffer, 1, 32767));
   END LOOP;
   EXCEPTION
    WHEN UTL_HTTP.END_OF_BODY THEN
    DBMS_OUTPUT.PUT_LINE('under exception loop');
      UTL_HTTP.END_RESPONSE(http_resp);
  END;
--  ELSE
--  DBMS_OUTPUT.PUT_LINE('NOT EXECUTED, UNDER ELSE');
--  END IF;

  EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('under final exception');
  DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;