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;