OE_ORDER_PUB to Create/Split/Cancel Order Line

API to Cancel Sales order

In this Post I will explain in short how to call Process Order API to 

1. Create Sales Order with 1 Shippable line.
2. Create Sales Order with 1 RMA line.
3.Create Sales Order with 1 Shippable and 1 RMA line.
4. Canel Order line.
 
Purpose of this post is to share some knowledge about OE_ORDER_PUB. Example that I use just creates simple Order and line.

CREATE OR REPLACE PACKAGE xxorderprocess
AS
   FUNCTION xxcreateOrder (p_order_type_id              NUMBER
                         , p_sold_to_org_id             NUMBER
                         , p_ship_to_org_id             NUMBER
                         , p_price_list_id              NUMBER
                         , p_transactional_curr_code    VARCHAR2
                         , p_flow_status_code           VARCHAR2
                         , p_cust_po_number             VARCHAR2
                         , p_order_source_id            NUMBER
                         , p_inventory_item_id          NUMBER
                         , p_ordered_quantity           NUMBER
                         , p_tax_code                   VARCHAR2
                         , p_code                       VARCHAR2
                         , p_header_id                  NUMBER
                         , p_line_id                    NUMBER)
      RETURN VARCHAR2;
END xxorderprocess;
/

CREATE OR REPLACE PACKAGE BODY xxorderprocess
AS
   FUNCTION xxcreateOrder (p_order_type_id              NUMBER
                         , p_sold_to_org_id             NUMBER
                         , p_ship_to_org_id             NUMBER
                         , p_price_list_id              NUMBER
                         , p_transactional_curr_code    VARCHAR2
                         , p_flow_status_code           VARCHAR2
                         , p_cust_po_number             VARCHAR2
                         , p_order_source_id            NUMBER
                         , p_inventory_item_id          NUMBER
                         , p_ordered_quantity           NUMBER
                         , p_tax_code                   VARCHAR2
                         , p_code                       VARCHAR2
                         , p_header_id                  NUMBER
                         , p_line_id                    NUMBER)
      RETURN VARCHAR2
   IS
      l_api_version_number           NUMBER := 1;
      l_return_status                VARCHAR2 (2000);
      l_msg_count                    NUMBER;
      l_msg_data                     VARCHAR2 (2000);
      l_xxstatus                     VARCHAR2 (1000);

      /*****************PARAMETERS****************************************************/

      l_debug_level                  NUMBER := 1;    -- OM DEBUG LEVEL (MAX 5)
      l_org                          NUMBER := 204;          -- OPERATING UNIT
      l_user                         NUMBER := 1318;                   -- USER
      l_resp                         NUMBER := 21623;        -- RESPONSIBLILTY
      l_appl                         NUMBER := 660;        -- ORDER MANAGEMENT
      /***INPUT VARIABLES FOR PROCESS_ORDER API*************************/

      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;
      /***OUT VARIABLES FOR PROCESS_ORDER API***************************/

      l_header_rec_out               oe_order_pub.header_rec_type;
      l_header_val_rec_out           oe_order_pub.header_val_rec_type;
      l_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
      l_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
      l_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
      l_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
      l_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
      l_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
      l_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
      l_line_tbl_out                 oe_order_pub.line_tbl_type;
      l_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
      l_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
      l_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
      l_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
      l_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
      l_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
      l_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
      l_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
      l_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
      l_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
      l_action_request_tbl_out       oe_order_pub.request_tbl_type;
      l_msg_index                    NUMBER;
      l_data                         VARCHAR2 (2000);
      l_loop_count                   NUMBER;
      l_debug_file                   VARCHAR2 (200);
      -- book API vars
      b_return_status                VARCHAR2 (200);
      b_msg_count                    NUMBER;
      b_msg_data                     VARCHAR2 (2000);
   BEGIN
      DBMS_APPLICATION_INFO.set_client_info (l_org);

      /*****************INITIALIZE DEBUG INFO*************************************/
      IF (l_debug_level > 0)
      THEN
         l_debug_file := OE_DEBUG_PUB.Set_Debug_Mode ('FILE');
         oe_debug_pub.initialize;
         oe_debug_pub.setdebuglevel (l_debug_level);
         Oe_Msg_Pub.initialize;
      END IF;

      /*****************INITIALIZE ENVIRONMENT*************************************/
      fnd_global.apps_initialize (l_user, l_resp, l_appl);
      -- pass in user_id, responsibility_id, and application_id
      /*****************INITIALIZE HEADER RECORD******************************/
      l_header_rec := oe_order_pub.G_MISS_HEADER_REC;

      /***********POPULATE REQUIRED ATTRIBUTES **********************************/

      IF SUBSTR (p_code, 1, 1) = 'N'
      THEN
         DBMS_OUTPUT.put_line ('Inside header');
         l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
         l_header_rec.order_type_id := p_order_type_id;          --1437;--1430
         l_header_rec.sold_to_org_id := p_sold_to_org_id;              --1005;
         l_header_rec.ship_to_org_id := p_ship_to_org_id;              --1024;
         l_header_rec.price_list_id := p_price_list_id;                --1000;
         l_header_rec.pricing_date := SYSDATE;
         l_header_rec.transactional_curr_code := p_transactional_curr_code; --'USD';
         l_header_rec.flow_status_code := p_flow_status_code;     --'ENTERED';
         l_header_rec.cust_po_number := p_cust_po_number;    --'06112009-118';
         l_header_rec.order_source_id := p_order_source_id;              --0 ;

         /*******INITIALIZE ACTION REQUEST RECORD*************************************/

         l_action_request_tbl (1) := oe_order_pub.G_MISS_REQUEST_REC;
         l_action_request_tbl (1).request_type := oe_globals.g_book_order;
         l_action_request_tbl (1).entity_code := oe_globals.g_entity_header;

         /*****************INITIALIZE LINE RECORD********************************/

         IF SUBSTR (p_code, 2) = 'E'
         THEN                                         -- Create Shippable Line
            DBMS_OUTPUT.put_line ('Inside Ship Line');
            ---Create 1 Line
            l_line_tbl (1) := oe_order_pub.G_MISS_LINE_REC;
            l_line_tbl (1).operation := OE_GLOBALS.G_OPR_CREATE;
            l_line_tbl (1).inventory_item_id := p_inventory_item_id;   --149 ;
            l_line_tbl (1).ordered_quantity := p_ordered_quantity;        --1;
            l_line_tbl (1).ship_to_org_id := p_ship_to_org_id;        --1024 ;
            l_line_tbl (1).tax_code := p_tax_code;              --'Location' ;
         ELSIF SUBSTR (p_code, 2) = 'R'
         THEN                                               -- Create RMA Line
            DBMS_OUTPUT.put_line ('Inside RMA Line');
            -- RMA ine
            l_line_tbl (1) := oe_order_pub.G_MISS_LINE_REC;
            l_line_tbl (1).operation := OE_GLOBALS.G_OPR_CREATE;
            l_line_tbl (1).inventory_item_id := p_inventory_item_id;   --149 ;
            l_line_tbl (1).ordered_quantity := p_ordered_quantity;        --1;
            l_line_tbl (1).ship_to_org_id := p_ship_to_org_id;        --1024 ;
            l_line_tbl (1).tax_code := p_tax_code;              --'Location' ;
            l_line_tbl (1).line_type_id := 1425;
            l_line_tbl (1).return_reason_code := 'DAMAGED PRODUCT';
         ELSIF SUBSTR (p_code, 2) = 'B'
         THEN                                      -- Create Ship and RMA Line
            DBMS_OUTPUT.put_line ('Inside Ship Line');
            ---Create 1 Line
            l_line_tbl (1) := oe_order_pub.G_MISS_LINE_REC;
            l_line_tbl (1).operation := OE_GLOBALS.G_OPR_CREATE;
            l_line_tbl (1).inventory_item_id := p_inventory_item_id;   --149 ;
            l_line_tbl (1).ordered_quantity := p_ordered_quantity;        --1;
            l_line_tbl (1).ship_to_org_id := p_ship_to_org_id;        --1024 ;
            l_line_tbl (1).tax_code := p_tax_code;              --'Location' ;

            DBMS_OUTPUT.put_line ('Inside RMA Line');
            -- RMA ine
            l_line_tbl (2) := oe_order_pub.G_MISS_LINE_REC;
            l_line_tbl (2).operation := OE_GLOBALS.G_OPR_CREATE;
            l_line_tbl (2).inventory_item_id := p_inventory_item_id;   --149 ;
            l_line_tbl (2).ordered_quantity := p_ordered_quantity;        --1;
            l_line_tbl (2).ship_to_org_id := p_ship_to_org_id;        --1024 ;
            l_line_tbl (2).tax_code := p_tax_code;              --'Location' ;
            l_line_tbl (2).line_type_id := 1425;
            l_line_tbl (2).return_reason_code := 'DAMAGED PRODUCT';
         END IF;
      END IF;

      IF p_code = 'CC'
      THEN                                            -- cancel the Order Line
         DBMS_OUTPUT.put_line ('Inside cancel Line');
         -- Cancel line
         l_line_tbl (1) := oe_order_pub.G_MISS_LINE_REC;
         l_line_tbl (1).operation := OE_GLOBALS.G_OPR_UPDATE;
         l_line_tbl (1).ordered_quantity := 0;
         l_line_tbl (1).cancelled_quantity := p_ordered_quantity;
         l_line_tbl (1).cancelled_flag := 'Y';
         l_line_tbl (1).line_id := p_line_id;
         l_line_tbl (1).header_id := p_header_id;
         l_line_tbl (1).change_reason := 'SYSTEM';
      END IF;

      IF p_code = 'SP'
      THEN                                                   -- Split the line
         l_action_request_tbl (1) := oe_order_pub.G_MISS_REQUEST_REC;
         l_action_request_tbl (1).request_type := oe_globals.g_book_order;
         l_action_request_tbl (1).entity_code := oe_globals.g_entity_header;
         DBMS_OUTPUT.put_line ('Inside SPLIT Line');
         -- Cancel line
         l_line_tbl (1) := oe_order_pub.G_MISS_LINE_REC;
         l_line_tbl (1).operation := OE_GLOBALS.G_OPR_UPDATE;
         l_line_tbl (1).split_by := 'USER';                 --1318; -- user_id
         l_line_tbl (1).split_action_code := 'SPLIT';
         l_line_tbl (1).header_id := p_header_id;
         l_line_tbl (1).line_id := p_line_id;
         l_line_tbl (1).ordered_quantity := 20;
         l_line_tbl (2) := oe_order_pub.G_MISS_LINE_REC;
         l_line_tbl (2).operation := OE_GLOBALS.G_OPR_CREATE;
         l_line_tbl (2).header_id := p_header_id;
         l_line_tbl (2).split_by := 'USER';                         -- user_id
         --l_line_tbl(2).split_action_code := 'SPLIT';
         l_line_tbl (2).split_from_line_id := p_line_id;
         l_line_tbl (2).inventory_item_id := p_inventory_item_id;
         l_line_tbl (2).ordered_quantity := 80;
      END IF;

      /*****************CALLTO PROCESS ORDER API*********************************/
      DBMS_OUTPUT.put_line ('Calling API');
      oe_order_pub.Process_Order (
         p_api_version_number       => l_api_version_number
       , p_header_rec               => l_header_rec
       , p_line_tbl                 => l_line_tbl
       , p_action_request_tbl       => l_action_request_tbl
       ,                                                       --OUT variables
        x_header_rec                => l_header_rec_out
       , x_header_val_rec           => l_header_val_rec_out
       , x_header_adj_tbl           => l_header_adj_tbl_out
       , x_header_adj_val_tbl       => l_header_adj_val_tbl_out
       , x_header_price_att_tbl     => l_header_price_att_tbl_out
       , x_header_adj_att_tbl       => l_header_adj_att_tbl_out
       , x_header_adj_assoc_tbl     => l_header_adj_assoc_tbl_out
       , x_header_scredit_tbl       => l_header_scredit_tbl_out
       , x_header_scredit_val_tbl   => l_header_scredit_val_tbl_out
       , x_line_tbl                 => l_line_tbl_out
       , x_line_val_tbl             => l_line_val_tbl_out
       , x_line_adj_tbl             => l_line_adj_tbl_out
       , x_line_adj_val_tbl         => l_line_adj_val_tbl_out
       , x_line_price_att_tbl       => l_line_price_att_tbl_out
       , x_line_adj_att_tbl         => l_line_adj_att_tbl_out
       , x_line_adj_assoc_tbl       => l_line_adj_assoc_tbl_out
       , x_line_scredit_tbl         => l_line_scredit_tbl_out
       , x_line_scredit_val_tbl     => l_line_scredit_val_tbl_out
       , x_lot_serial_tbl           => l_lot_serial_tbl_out
       , x_lot_serial_val_tbl       => l_lot_serial_val_tbl_out
       , x_action_request_tbl       => l_action_request_tbl_out
       , x_return_status            => l_return_status
       , x_msg_count                => l_msg_count
       , x_msg_data                 => l_msg_data
      );

      /*****************CHECK RETURN STATUS***********************************/
      IF l_return_status = FND_API.G_RET_STS_SUCCESS
      THEN
         DBMS_OUTPUT.put_line ('Return status is success ');
         DBMS_OUTPUT.put_line ('debug level ' || l_debug_level);

         IF (l_debug_level > 0)
         THEN
            DBMS_OUTPUT.put_line ('success');
         END IF;

         COMMIT;
         l_xxstatus := 'S';
      ELSE
         DBMS_OUTPUT.put_line ('Return status failure ');

         IF (l_debug_level > 0)
         THEN
            DBMS_OUTPUT.put_line ('failure');
         END IF;

         ROLLBACK;
         l_xxstatus := 'F';
      END IF;

      /*****************DISPLAY RETURN STATUS FLAGS******************************/
      IF (l_debug_level > 0)
      THEN
         DBMS_OUTPUT.PUT_LINE (
            'process ORDER ret status IS: ' || l_return_status
         );
         DBMS_OUTPUT.PUT_LINE ('process ORDER msg data IS: ' || l_msg_data);
         DBMS_OUTPUT.PUT_LINE ('process ORDER msg COUNT IS: ' || l_msg_count);
         DBMS_OUTPUT.PUT_LINE('header.order_number IS: '|| l_header_rec_out.order_number);
         DBMS_OUTPUT.PUT_LINE ('header.return_status IS: ' || l_header_rec_out.return_status);
         DBMS_OUTPUT.PUT_LINE ('header.booked_flag IS: ' || l_header_rec_out.booked_flag);
         DBMS_OUTPUT.PUT_LINE ('header.header_id IS: ' || l_header_rec_out.header_id);
         DBMS_OUTPUT.PUT_LINE ('header.order_source_id IS: ' || l_header_rec_out.order_source_id);
         DBMS_OUTPUT.PUT_LINE('header.flow_status_code IS: '|| l_header_rec_out.flow_status_code);
      END IF;

      l_xxstatus :=
            l_xxstatus
         || '-'
         || l_header_rec_out.booked_flag
         || '- '
         || l_header_rec_out.header_id
         || '-'
         || l_header_rec_out.flow_status_code;

      /*****************DISPLAY ERROR MSGS*************************************/

      IF (l_debug_level > 0)
      THEN
         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_data
                          , p_msg_index_out   => l_msg_index);
            DBMS_OUTPUT.PUT_LINE ('message is: ' || l_data);
            DBMS_OUTPUT.PUT_LINE ('message index is: ' || l_msg_index);
         END LOOP;
      END IF;

      IF (l_debug_level > 0)
      THEN
         DBMS_OUTPUT.PUT_LINE ('Debug = ' || OE_DEBUG_PUB.G_DEBUG);
         DBMS_OUTPUT.PUT_LINE (
            'Debug Level = ' || TO_CHAR (OE_DEBUG_PUB.G_DEBUG_LEVEL)
         );
         DBMS_OUTPUT.PUT_LINE(   'Debug File = '
                              || OE_DEBUG_PUB.G_DIR
                              || '/'
                              || OE_DEBUG_PUB.G_FILE);

         DBMS_OUTPUT.PUT_LINE (
            '****************************************************'
         );
      END IF;

      RETURN l_xxstatus;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_xxstatus := l_xxstatus || ' ' || SQLERRM;

         RETURN l_xxstatus;
   END xxcreateOrder;
END xxorderprocess;

----------------------------------------------------
/
----------------------------------------------------

-- I have tested my program with below pl/sql block

DECLARE
   l_status      VARCHAR2 (1000);
   p_header_id   NUMBER := &Enter_header_id;
   p_line_id     NUMBER := &Enter_lline_id;
   p_code        VARCHAR2 (10) := 'SP';
--NE to create New Line
--SP to Split Line
--CC to Cancel line
--NB to Create RMA Line
BEGIN
   l_status :=
      xxorderprocess.xxcreateOrder (&order_type_id 
                                  , &sold_to_org_id
                                  , &ship_to_org_id
                                  , &price_list_id
                                  , '&curr_code'
                                  , '&flow_status_code'
                                  , '&cust_po_number'
                                  , &order_source_id
                                  , &inventory_item_id
                                  , &ordered_quantity
                                  , '&tax_code'
                                  , p_code
                                  , p_header_id
                                  , p_line_id);
     dbms_output.put_line('l_status ='||l_status);
END; 


--   All the parameters are not required ex. Header_id and line_id are required only if you want to do Line Split or Line Cancellation.

--  For Line Creation these 2 parameters doesn’t make any sense.

Oracle Apps: How to set Profile Option Value to enable personalization from backend

In this post, I will just post a sample code to set a profile option value from the backend.

This is the sample screen shot of the Profile Option Definition.
Navigation :- Application Developer --> Profile --> System

 --To set the Profile value at user Level

BEGIN
   IF fnd_profile.save (profile_name    => 'DIAGNOSTICS',
                        profile_value   => 'Y',
                        level_name      => 'USER',
                        level_value     => 4516) -- User ID from fnd_user table
   THEN
      DBMS_OUTPUT.put_line ('Success');
   ELSE
      DBMS_OUTPUT.put_line ('Fail');
   END IF;

   COMMIT;
END;

sqlplus : multiple scripts in argument (batch execution) (sql*plus)

execute multiple sql files at once in command prompt

U:\>test.cmd

U:\>(
echo set serverout on
 echo @u:\test.sql
 echo @u:\test.sql
)  | sqlplus user/pass@testdb

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Aug 13 12:50:05 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> SQL> test

PL/SQL procedure successfully completed.

SQL> test

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Pro
duction
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

U:\>

Advanced Queuing & PL/SQL Notification -- Queue Propagate

Sample Code to propagate messages from one Queue to Another Queue

connect "/ as sysdba"

drop user aq cascade;
CREATE USER aq IDENTIFIED BY aq;
GRANT CONNECT, RESOURCE, aq_administrator_role TO aq;
GRANT EXECUTE ON dbms_aq TO aq;
GRANT EXECUTE ON dbms_aqadm TO aq;

begin
dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','AQ',FALSE);
dbms_aqadm.grant_system_privilege('DEQUEUE_ANY','AQ',FALSE);
end;
/

connect AQ/AQ
CREATE type aq.Message_typ as object(subject VARCHAR2(30), text VARCHAR2(80));
/

Create Queue Table

begin
DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'aq.objmsgs80_qtab',
queue_payload_type => 'aq.Message_typ',
multiple_consumers => TRUE);
end;

Create Queue & Start the Queue

begin
DBMS_AQADM.CREATE_QUEUE(queue_name => 'MSG_QUEUE',
queue_table => 'aq.objmsgs80_qtab');
DBMS_AQADM.START_QUEUE(queue_name => 'MSG_QUEUE');
end;
/

Setup Addition Queue to propagate messages to:

begin
DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'aq.objmsgs80_qtabX',
queue_payload_type => 'aq.Message_typ',
multiple_consumers => TRUE);
DBMS_AQADM.CREATE_QUEUE(queue_name => 'MSG_QUEUEX',
queue_table => 'aq.objmsgs80_qtabX');
DBMS_AQADM.START_QUEUE(queue_name => 'MSG_QUEUEX');
end;
/

Create Procedure to Enqueue the messsage:

create or replace procedure enqueue_msg(p_msg in varchar2)
as
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
message aq.message_typ;
recipients DBMS_AQ.aq$_recipient_list_t;

BEGIN
-- ADDED
recipients(1) := SYS.aq$_agent('RECIPIENT', null, null);
message_properties.recipient_list := recipients;

message := message_typ('NORMAL MESSAGE', p_msg );
dbms_aq.enqueue(queue_name => 'msg_queue',
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);
end;
/

begin enqueue_msg('This is a test....'); commit; end;
/

Create Database link to loopback:

create database link AQ.LoopBack connect to AQ identified by AQ using 'ORCL';

Setup Scheduling for messages to propagate:

begin DBMS_AQADM.Schedule_Propagation(Queue_Name => 'MSG_QUEUE',
Destination => 'AQ.LOOPBACK',
Start_Time => sysdate,
Latency => 0);
end;
/

-- Check scheduling: Everything checked out OK.
select * from user_queue_schedules; 

begin enqueue_msg('This should be propagated.'); commit; end;
/
Check Queue query

SELECT   *

    FROM   user_queues

ORDER BY   1;

Check Subscribers

SELECT   *

  FROM   user_queue_subscribers;

Check Scheduling
select * from user_queue_schedules; 

Drop Commands
begin
DBMS_AQADM.stop_queue('MSG_QUEUE');
DBMS_AQADM.drop_queue('MSG_QUEUE');
DBMS_AQADM.drop_queue_table('aq.objmsgs80_qtab');
DBMS_AQADM.stop_queue('MSG_QUEUEX');
DBMS_AQADM.drop_queue('MSG_QUEUEX');
DBMS_AQADM.drop_queue_table('aq.objmsgs80_qtabX');
end;
/

Create New Subscriber

DECLARE
   aSubscriber   sys.aq$_agent;
BEGIN
   aSubscriber :=
      sys.aq$_agent ('GW'
                   , '"PHX_NCMS"."XXONT_XML_ECP_QUEUE_M"@NCMS2DPPCI'
                   , 0);
   DBMS_AQADM.add_subscriber (queue_name   => 'PHX_NCMS.XXONT_ECP_MSG_QUEUE_M'
                            , subscriber   => aSubscriber);
END;

/

Howto - Linux Delete Common Lines From Two Files

Question: How can I delete lines containing matching text from two files?

Answer:

#cat test1
www.xyz.com/abc-1
www.xyz.com/abc-7
www.xyz.com/abc-8
www.xyz.com/abc-2
www.xyz.com/abc-3
www.xyz.com/abc-4
www.xyz.com/abc-5

#cat test2
www.xyz.com/abc-2
www.xyz.com/abc-3
www.xyz.com/abc-4
www.xyz.com/abc-5
www.xyz.com/abc-6





This can be done with the Linux command “comm”. The basic syntax of this command is as follows.

comm [-1] [-2] [-3 ] test1 test2

-1 Suppress the output column of lines unique to test1.
-2 Suppress the output column of lines unique to test2
-3 Suppress the output column of lines duplicated in test1 and test2.
test1 Name of the first file to compare.
test2 Name of the second file to compare.

Before applying “comm”, we need to sort the input files. So, in order to get the lines unique to file1, we can use a combination of “comm” and “sort” commands as follows.

# comm -23 <(sort test1) <(sort test2) > test3

#comm -23 <(sort test2) <(sort test1) > test7
[/home/y100n0]
#cat test7
www.xyz.com/abc-6

#comm -23 <(sort test1) <(sort test2) > test8
[/home/y100n0]
#cat test8
www.xyz.com/abc-1
www.xyz.com/abc-7
www.xyz.com/abc-8

Pick Release Sales order - Oracle Apps Technical

What is Pick Release?

Pick release perform process starts, once the Order is scheduled and booked, then we need to release the order  to warehouse for shipping 

1: ENTER THE SALES ORDER

Once order is entered.

The Header information stored in OE_ORDER_HEADERS_ALL and the Line information stored in OE_ORDER_LINES_ALL, when the order is entered.

The Column: Flow_Status_Code is available both OE header and line tables, to define status of order at each stage        

  Flow_Status_Code consists of 4 types

1.       Entered

2.       Cancelled

3.       Closed

4.       Booked

For initial stage, once order enters = > Column: Flow_Status_Code in OE_ORDER_HEADERS_ALL is ‘Entered’

When the order is booked, Flow_Status_Code column in header change accordingly

2:BOOK THE SALES ORDER

Tables Affected:              

OE_ORDER_HEADERS_ALL   - Column: Flow_Status_Code => ‘Entered’

OE_ORDER_LINES_ALL - Column: Flow_Status_Code => ‘AWAITING_SHIPPING’

Records will be created in the table WSH_DELIVERY_DETAILS

  WSH_DELIVERY_DETAILS – Column Released_Status => ‘R’(Ready to release)

Also record into inserted into WSH_DELIVERY_ASSIGNMENTS

At this stage,”DEMAND INTERFACE PROGRAM” runs in the background and inserts into MTL_DEMAND

3:PICK RELEASE

 In Release Sales order window.

In shipping tab:

Auto Create Delivery: Yes

Auto Pick Confirm: Yes

Auto Pack Delivery: Yes

In Inventory Tab:

            Auto Allocate: Yes

Enter the Ware house like M1 ,M2, Etc

 Click on “Execute button”, After successful completion , you will get popup message

Now , Pick Release process in turn will kick off several other request program like

ü  Auto Pack report

ü  Shipping Execution Report

ü  Pick Slip Report 

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 => set to ‘Y’ (Pick Confirmed)

 if Auto Pick Confirm is set to Yes otherwise RELEASED_STATUS => ‘S’ (Release to Warehouse).

IF Auto Pick Confirm in the above step is set to NO, then the following should be done.

Navigation: Inventory Super User > Move Order> Transact Move Order

In the HEADER tab, enter the BATCH NUMBER (from the above step) of the order. Click FIND. Click on VIEW/UPDATE Allocation, then Click TRANSACT button. Then Transact button will be deactivated then just close it and go to next step

4. SHIP CONFIRM THE ORDER

Once Shipping Transaction Successfully completed, After then few Concurrent program will be trigger .i.e.,

ü  INTERFACE TRIP Stop

ü  Commercial Invoice

ü  Packing Slip Report

ü  Bill of Lading

Tables Affected:

WSH_DELIVERY_DETAILS  - Column : Released_Status=> ‘C’ (Ship Confirmed)

OE_ORDER_HEADERS_ALL – Column: Flow_Status_Code => ‘Booked’

OE_ORDER_LINES_ALL – Column : Flow_status_Code => ‘Shipped’

…   The Final process move into Invoice

Hints :

Pick Release Status – WSH_DELIVERY_DETAILS

Column Name: Released_Status .

B. Backordered – Line failed to be allocated in Inventory

C: Shipped – Line has been shipped

D: Cancelled – Line is cancelled

N: Not ready to release  -Line is not ready to be released

R:Ready to release - Line is ready to be released 

S:Released to warehouse -Line has been released to Inventory for processing

X:Not Applicable -Line is not applicable for Pick Release

Y:Staged - Line has been picked and staged by Inventory 

Delivery line statuses in detail

 Not Applicable (Code X) 

The delivery line can be invoiced but non-shippable, for example, a service line or a warranty line.

Not Ready for Release (Code N)

 The delivery line is not eligible for pick release. This happens when the order line is manually imported into Oracle Shipping Execution using the Import Delivery Line concurrent process or the corresponding order line has not reached the Awaiting Shipping workflow activity.

Ready for Release (Code R)

  The delivery line is eligible for pick release.  Occurs when the order line has reached the Awaiting Shipping workflow activity (it is booked, scheduled, and in Oracle Shipping Execution).

Submitted to Warehouse (Code S)

Pick release has processed the delivery line and has:

1.       Created move order headers and lines.

2.       Found available quantity and created inventory allocations.

3.       Not pick confirmed. If you are using auto-pick confirm, it changes release status to Staged. If you are not using auto-pick confirm and want to progress the delivery lines, navigate to Oracle Inventory Move Order Transaction window and perform manual pick confirm.

Staged (Code Y)

 The delivery line is pick confirmed; inventory is transferred from storage sub-inventory to staging sub-inventory.  It remains staged until ship confirm.

 Backordered (Code B)

Some of the circumstances that can causes this status are listed below

Ø  Pick release has processed the delivery line and cannot find the entire quantity.  This typically occurs when the Oracle Inventory indicates that there is not enough material (either because there is not enough material or because the inventory balance is incorrect). 

Ø  At ship confirm, you: Enter Shipped Quantity that is less than Original Requested Quantity Backorder the entire delivery quantity transfer a reservation to cycle count.

Ø  This typically occurs when the material that you want to ship:

1.       Has become unavailable, for example, damaged, between picking and shipping.

2.       Is available and you backorder material for specific business reasons. For example, all available material has been allocated to a specific customer when you find out additional supply for other orders will be delayed.

  Shipped (Code C)

  The delivery line’s delivery is ship confirmed and posted as in-transit, OM Interface and Inventory Interface have processed, and the trip is closed.

  Cancelled (Code D)

  The order line that the delivery line supports is cancelled.

Sample Scripts / List of Tables 

OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL,WSH_DELIVERY_DETAILS, WSH_DELIVERY_ASSIGNMENTS,

MTL_DEMAND

Sample script :1

SELECT oha.order_number sales_order,

  oha.org_id,

  ola.line_number,

  ola.shipment_number,

  ola.flow_status_code,

  wdd.delivery_detail_id,

  wdd.inv_interfaced_flag,

  wdd.oe_interfaced_flag,

  Decode (wdd.released_status,'C','Shipped','B','Backordered','D','Cancelled','N','Not Ready for  Release','R','Ready to Release',  'S','Released to Warehouse','X','Not Applicable','Y','Staged')as Released_Status

FROM apps.oe_order_headers_all oha,

            apps.oe_order_lines_all ola,

            apps.wsh_delivery_details wdd

WHERE oha.header_id                = ola.header_id

AND oha.org_id                     = ola.org_id

AND oha.header_id                  = wdd.source_header_id

AND ola.line_id                    = wdd.source_line_id

AND oha.booked_flag                = 'Y'

AND NVL (ola.cancelled_flag, 'N') <> 'Y'

 AND wdd.released_status in ('R','B')  

AND ola.flow_status_code = 'CLOSED' –Change condition i.e.,  'AWAITING_SHIPPING'

AND oha.order_number = 12341719 --  pass  Order number

AND oha.org_id = 201;  -- pass Org id

Sample Script :2

SELECT wnd.delivery_id,

  wnd.name delivery_name,

  wdd.source_header_number so_order_number,

  oola.line_number so_line_number,

  wdd.source_header_id so_header_id,

  wdd.source_line_id so_line_id,

  wdd.shipping_instructions,

  wdd.inventory_item_id,

  wdd.requested_quantity_uom,

  msi.description item_description,

  msi.revision_qty_control_code ,

  wdd.ship_method_code carrier,

  wdd.shipment_priority_code priority,

  wdd.organization_id,

  wnd.initial_pickup_location_id,

  Decode (wdd.released_status,'C','Shipped','B','Backordered','D','Cancelled','N','Not Ready for Release','R','Ready to Release',

  'S','Released to Warehouse','X','Not Applicable','Y','Staged')as Released_Status,

  wdd.source_code

FROM mtl_system_items_vl msi,

                oe_order_lines_all oola,

  wsh_delivery_details wdd,

                 wsh_delivery_assignments wda,

                wsh_new_deliveries wnd

WHERE wnd.delivery_id      = 2323   --- pass delivery ID

AND wda.delivery_id        = wnd.delivery_id(+)

AND wdd.delivery_detail_id = wda.delivery_detail_id

AND wdd.inventory_item_id  = msi.inventory_item_id(+)

AND wdd.organization_id    = msi.organization_id(+)

AND wdd.source_line_id     = oola.line_id

AND wdd.source_header_id   = oola.header_id;