Total Pageviews

August 21, 2015

8/21/2015 02:49:00 AM
Some pointers on Order Cancellation

Query to find out the cancelled orders

/* Formatted on 21-Aug-15 3:11:51 PM (QP5 v5.115.810.9015) */
SELECT   flt.meaning,
         oer.reason_type,
         oer.reason_code,
         flv.meaning,
         oer.comments,
         oer.creation_date,
         oer.reason_id,
         oer.entity_id,
         oer.entity_code,
         oer.version_number,
         oer.header_id
  FROM   oe_reasons oer, fnd_lookup_values flv, fnd_lookup_types_vl flt
 WHERE       flv.LANGUAGE = USERENV ('LANG')
         AND flv.view_application_id = 660
         AND flt.view_application_id = 660
         AND flt.security_group_id =
               fnd_global.lookup_security_group (FLT.LOOKUP_TYPE,
                                                 FLT.VIEW_APPLICATION_ID)
         AND FLV.SECURITY_GROUP_ID =
               fnd_global.lookup_security_group (flv.lookup_type,
                                                 flv.view_application_id)
         AND flv.lookup_type = flt.lookup_type
         AND oer.reason_type = flv.lookup_type
         AND oer.reason_code = flv.lookup_code;


Cancel reason appears on the sales order : Actions : Additional Order Information : tab 'Quantity change history'.

Information displayed there are stored in tables/fields I listed above.

If you can not see your cancel reason here, you probably have an issue with your sales order screen

/* Formatted on 21-Aug-15 2:56:22 PM (QP5 v5.115.810.9015) */
DECLARE
   l_header_rec               oe_order_pub.header_rec_type;
   l_line_tbl                 oe_order_pub.line_tbl_type;
   l_action_request_tbl       oe_order_pub.request_tbl_type;
   l_header_adj_tbl           oe_order_pub.header_adj_tbl_type;
   l_line_adj_tbl             oe_order_pub.line_adj_tbl_type;
   l_header_scr_tbl           oe_order_pub.header_scredit_tbl_type;
   l_line_scredit_tbl         oe_order_pub.line_scredit_tbl_type;
   l_request_rec              oe_order_pub.request_rec_type;
   l_return_status            VARCHAR2 (1000);
   l_msg_count                NUMBER;
   l_msg_data                 VARCHAR2 (1000);
   p_api_version_number       NUMBER := 1.0;
   p_init_msg_list            VARCHAR2 (10) := fnd_api.g_false;
   p_return_values            VARCHAR2 (10) := fnd_api.g_false;
   p_action_commit            VARCHAR2 (10) := fnd_api.g_false;
   x_return_status            VARCHAR2 (1);
   x_msg_count                NUMBER;
   x_msg_data                 VARCHAR2 (100);
   p_header_rec oe_order_pub.header_rec_type
         := oe_order_pub.g_miss_header_rec ;
   x_header_rec oe_order_pub.header_rec_type
         := oe_order_pub.g_miss_header_rec ;
   p_old_header_rec oe_order_pub.header_rec_type
         := oe_order_pub.g_miss_header_rec ;
   p_header_val_rec oe_order_pub.header_val_rec_type
         := oe_order_pub.g_miss_header_val_rec ;
   p_old_header_val_rec oe_order_pub.header_val_rec_type
         := oe_order_pub.g_miss_header_val_rec ;
   p_header_adj_tbl oe_order_pub.header_adj_tbl_type
         := oe_order_pub.g_miss_header_adj_tbl ;
   p_old_header_adj_tbl oe_order_pub.header_adj_tbl_type
         := oe_order_pub.g_miss_header_adj_tbl ;
   p_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type
         := oe_order_pub.g_miss_header_adj_val_tbl ;
   p_old_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type
         := oe_order_pub.g_miss_header_adj_val_tbl ;
   p_header_price_att_tbl oe_order_pub.header_price_att_tbl_type
         := oe_order_pub.g_miss_header_price_att_tbl ;
   p_old_header_price_att_tbl oe_order_pub.header_price_att_tbl_type
         := oe_order_pub.g_miss_header_price_att_tbl ;
   p_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type
         := oe_order_pub.g_miss_header_adj_att_tbl ;
   p_old_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type
         := oe_order_pub.g_miss_header_adj_att_tbl ;
   p_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type
         := oe_order_pub.g_miss_header_adj_assoc_tbl ;
   p_old_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type
         := oe_order_pub.g_miss_header_adj_assoc_tbl ;
   p_header_scredit_tbl oe_order_pub.header_scredit_tbl_type
         := oe_order_pub.g_miss_header_scredit_tbl ;
   p_old_header_scredit_tbl oe_order_pub.header_scredit_tbl_type
         := oe_order_pub.g_miss_header_scredit_tbl ;
   p_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type
         := oe_order_pub.g_miss_header_scredit_val_tbl ;
   p_old_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type
         := oe_order_pub.g_miss_header_scredit_val_tbl ;
   x_line_tbl oe_order_pub.line_tbl_type
         := oe_order_pub.g_miss_line_tbl ;
   p_old_line_tbl oe_order_pub.line_tbl_type
         := oe_order_pub.g_miss_line_tbl ;
   p_line_val_tbl oe_order_pub.line_val_tbl_type
         := oe_order_pub.g_miss_line_val_tbl ;
   p_old_line_val_tbl oe_order_pub.line_val_tbl_type
         := oe_order_pub.g_miss_line_val_tbl ;
   p_line_adj_tbl oe_order_pub.line_adj_tbl_type
         := oe_order_pub.g_miss_line_adj_tbl ;
   p_old_line_adj_tbl oe_order_pub.line_adj_tbl_type
         := oe_order_pub.g_miss_line_adj_tbl ;
   p_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type
         := oe_order_pub.g_miss_line_adj_val_tbl ;
   p_old_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type
         := oe_order_pub.g_miss_line_adj_val_tbl ;
   p_line_price_att_tbl oe_order_pub.line_price_att_tbl_type
         := oe_order_pub.g_miss_line_price_att_tbl ;
   p_old_line_price_att_tbl oe_order_pub.line_price_att_tbl_type
         := oe_order_pub.g_miss_line_price_att_tbl ;
   p_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type
         := oe_order_pub.g_miss_line_adj_att_tbl ;
   p_old_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type
         := oe_order_pub.g_miss_line_adj_att_tbl ;
   p_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type
         := oe_order_pub.g_miss_line_adj_assoc_tbl ;
   p_old_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type
         := oe_order_pub.g_miss_line_adj_assoc_tbl ;
   p_line_scredit_tbl oe_order_pub.line_scredit_tbl_type
         := oe_order_pub.g_miss_line_scredit_tbl ;
   p_old_line_scredit_tbl oe_order_pub.line_scredit_tbl_type
         := oe_order_pub.g_miss_line_scredit_tbl ;
   p_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type
         := oe_order_pub.g_miss_line_scredit_val_tbl ;
   p_old_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type
         := oe_order_pub.g_miss_line_scredit_val_tbl ;
   p_lot_serial_tbl oe_order_pub.lot_serial_tbl_type
         := oe_order_pub.g_miss_lot_serial_tbl ;
   p_old_lot_serial_tbl oe_order_pub.lot_serial_tbl_type
         := oe_order_pub.g_miss_lot_serial_tbl ;
   p_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type
         := oe_order_pub.g_miss_lot_serial_val_tbl ;
   p_old_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type
         := oe_order_pub.g_miss_lot_serial_val_tbl ;
   p_action_request_tbl oe_order_pub.request_tbl_type
         := oe_order_pub.g_miss_request_tbl ;
   x_header_val_rec           oe_order_pub.header_val_rec_type;
   x_header_adj_tbl           oe_order_pub.header_adj_tbl_type;
   x_header_adj_val_tbl       oe_order_pub.header_adj_val_tbl_type;
   x_header_price_att_tbl     oe_order_pub.header_price_att_tbl_type;
   x_header_adj_att_tbl       oe_order_pub.header_adj_att_tbl_type;
   x_header_adj_assoc_tbl     oe_order_pub.header_adj_assoc_tbl_type;
   x_header_scredit_tbl       oe_order_pub.header_scredit_tbl_type;
   x_header_scredit_val_tbl   oe_order_pub.header_scredit_val_tbl_type;
   x_line_val_tbl             oe_order_pub.line_val_tbl_type;
   x_line_adj_tbl             oe_order_pub.line_adj_tbl_type;
   x_line_adj_val_tbl         oe_order_pub.line_adj_val_tbl_type;
   x_line_price_att_tbl       oe_order_pub.line_price_att_tbl_type;
   x_line_adj_att_tbl         oe_order_pub.line_adj_att_tbl_type;
   x_line_adj_assoc_tbl       oe_order_pub.line_adj_assoc_tbl_type;
   x_line_scredit_tbl         oe_order_pub.line_scredit_tbl_type;
   x_line_scredit_val_tbl     oe_order_pub.line_scredit_val_tbl_type;
   x_lot_serial_tbl           oe_order_pub.lot_serial_tbl_type;
   x_lot_serial_val_tbl       oe_order_pub.lot_serial_val_tbl_type;
   x_action_request_tbl       oe_order_pub.request_tbl_type;
   x_debug_file               VARCHAR2 (100);
   l_msg_index_out            NUMBER (10);
   l_line_tbl_index           NUMBER;
   p_order_number             NUMBER;
   l_header_id                NUMBER;
   l_line_id                  NUMBER;
BEGIN
   DBMS_OUTPUT.ENABLE (1000000);
   fnd_global.Apps_initialize (1318, 21623, 660);
   -- pass in user_id, responsibility_id, and application_id

   --tables used for user_id ->fund_user,
   --USE BELOW CODE

   /*SELECT   user_id, responsibility_application_id, responsibility_id
  FROM   fnd_user_resp_groups_all
 WHERE   responsibility_application_id =
            (SELECT   application_id
               FROM   fnd_allications
              WHERE   application_short_name = 'ONT')*/


   oe_msg_pub.initialize;
   oe_debug_pub.initialize;
   mo_global.Init ('ONT');                                 -- Required for R12
   mo_global.Set_org_context (204, NULL, 'ONT');
   fnd_global.Set_nls_context ('AMERICAN');
   mo_global.Set_policy_context ('S', 204);                -- Required for R12

   --TO GET HEADER_ID,LINE_ID
   SELECT   ooha.header_id, oola.line_id
     INTO   l_header_id, l_line_id
     FROM   oe_order_headers_all ooha, oe_order_lines_all oola
    WHERE   ooha.header_id = oola.header_id
            AND ooha.order_number = p_order_number;


   --X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
   --oe_debug_pub.SetDebugLevel(5);
   --dbms_output.put_line('START OF NEW DEBUG');
   --This is to update a line to an existing order
   l_line_tbl_index := 1;
   -- Changed attributes
   l_line_tbl (l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;
   --l_line_tbl(l_line_tbl_index).invoice_to_org_id := 322;--org_organization_definitions
   l_line_tbl (l_line_tbl_index).ordered_quantity := 2;
   -- Primary key of the entity i.e. the order line
   l_line_tbl (l_line_tbl_index).line_id := l_line_id;
   l_line_tbl (l_line_tbl_index).change_reason := 'Not provided';
   -- Indicates to process order that this is an update operation
   l_line_tbl (l_line_tbl_index).operation := OE_GLOBALS.G_OPR_UPDATE;

   -- CALL TO PROCESS ORDER
   oe_order_pub.Process_order (
      p_api_version_number       => 1.0,
      p_init_msg_list            => fnd_api.g_false,
      p_return_values            => fnd_api.g_false,
      p_action_commit            => fnd_api.g_false,
      x_return_status            => l_return_status,
      x_msg_count                => l_msg_count,
      x_msg_data                 => l_msg_data,
      p_header_rec               => l_header_rec,
      p_line_tbl                 => l_line_tbl,
      p_action_request_tbl       => l_action_request_tbl-- OUT PARAMETERS
      ,
      x_header_rec               => x_header_rec,
      x_header_val_rec           => x_header_val_rec,
      x_header_adj_tbl           => x_header_adj_tbl,
      x_header_adj_val_tbl       => x_header_adj_val_tbl,
      x_header_price_att_tbl     => x_header_price_att_tbl,
      x_header_adj_att_tbl       => x_header_adj_att_tbl,
      x_header_adj_assoc_tbl     => x_header_adj_assoc_tbl,
      x_header_scredit_tbl       => x_header_scredit_tbl,
      x_header_scredit_val_tbl   => x_header_scredit_val_tbl,
      x_line_tbl                 => x_line_tbl,
      x_line_val_tbl             => x_line_val_tbl,
      x_line_adj_tbl             => x_line_adj_tbl,
      x_line_adj_val_tbl         => x_line_adj_val_tbl,
      x_line_price_att_tbl       => x_line_price_att_tbl,
      x_line_adj_att_tbl         => x_line_adj_att_tbl,
      x_line_adj_assoc_tbl       => x_line_adj_assoc_tbl,
      x_line_scredit_tbl         => x_line_scredit_tbl,
      x_line_scredit_val_tbl     => x_line_scredit_val_tbl,
      x_lot_serial_tbl           => x_lot_serial_tbl,
      x_lot_serial_val_tbl       => x_lot_serial_val_tbl,
      x_action_request_tbl       => x_action_request_tbl
   );

   --dbms_output.put_line('OM Debug file: ' ||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE);
   -- Retrieve messages
   DBMS_OUTPUT.Put_line (
      'Line Id: ' || x_line_tbl (l_line_tbl_index).line_id
   );

   FOR i IN 1 .. l_msg_count
   LOOP
      oe_msg_pub.Get (p_msg_index       => i,
                      p_encoded         => fnd_api.g_false,
                      p_data            => l_msg_data,
                      p_msg_index_out   => l_msg_index_out);

      DBMS_OUTPUT.Put_line ('message is: ' || l_msg_data);
      DBMS_OUTPUT.Put_line ('message index is: ' || l_msg_index_out);
   END LOOP;

   -- Check the return status
   IF l_return_status = fnd_api.g_ret_sts_success
   THEN
      DBMS_OUTPUT.put_line ('Line Quantity Update Sucessful');
   ELSE
      DBMS_OUTPUT.put_line ('Line Quantity update Failed');
   END IF;
END;
/

COMMIT;