Query to find Default Shipping WareHouse in OM

SELECT msib.segment1,
       (SELECT ood.organization_code
          FROM org_organization_definitions ood
         WHERE ood.organization_id = msib.default_shipping_org)
          Default_Shipping_Org,
          msib.inventory_item_status_code,
       (SELECT   mis.description
             FROM MTL_ITEM_STATUS_TL mis
                where  mis.inventory_item_status_code = msib.inventory_item_status_code
                and mis.language='US') inventory_item_status        
  FROM mtl_system_items_b msib
 WHERE     1 = 1
       AND msib.organization_id = 121
       AND segment1 NOT LIKE '9%'
       AND msib.default_shipping_org IS NOT NULL
ORDER BY 3,1;

R12 - Ship Confirm using API

Use this script to create a procedure in Database and call the procedure by passing the delivery number as a parameter to ship confirm it.

You can set the options for
1. Back ordering unspecified quantities
2. Closing the delivery automatically by submitting the Trip stop program after ship confirm is successful

SHIP CONFIRMATION THROUGH API

CREATE OR REPLACE PROCEDURE erps_ship_confirm_delivery (
   v_delivery_name      IN     VARCHAR2,                   --  delivery number
   v_action             IN     VARCHAR2, -- Pass 'B' to backorder the unspecified quantity
   p_ship_conf_status      OUT VARCHAR2,
   x_msg_data              OUT VARCHAR2)
IS
   p_api_version_number     NUMBER;

   init_msg_list            VARCHAR2 (30);

   x_msg_count              NUMBER;

   x_msg_details            VARCHAR2 (32000);

   x_msg_summary            VARCHAR2 (32000);

   p_validation_level       NUMBER;

   p_commit                 VARCHAR2 (30);

   x_return_status          VARCHAR2 (15);

   source_code              VARCHAR2 (15);

   changed_attributes       wsh_delivery_details_pub.changedattributetabtype;

   p_action_code            VARCHAR2 (15);

   p_delivery_id            NUMBER;

   p_delivery_name          VARCHAR2 (30);

   p_asg_trip_id            NUMBER;

   p_asg_trip_name          VARCHAR2 (30);

   p_asg_pickup_stop_id     NUMBER;

   p_asg_pickup_loc_id      NUMBER;

   p_asg_pickup_loc_code    VARCHAR2 (30);

   p_asg_pickup_arr_date    DATE;

   p_asg_pickup_dep_date    DATE;

   p_asg_dropoff_stop_id    NUMBER;

   p_asg_dropoff_loc_id     NUMBER;

   p_asg_dropoff_loc_code   VARCHAR2 (30);

   p_asg_dropoff_arr_date   DATE;

   p_asg_dropoff_dep_date   DATE;

   p_sc_action_flag         VARCHAR2 (10);

   p_sc_close_trip_flag     VARCHAR2 (10);

   p_defer_iface            VARCHAR2 (10);

   p_sc_create_bol_flag     VARCHAR2 (10);

   p_sc_stage_del_flag      VARCHAR2 (10);

   p_sc_trip_ship_method    VARCHAR2 (30);

   p_sc_actual_dep_date     VARCHAR2 (30);

   p_sc_report_set_id       NUMBER;

   p_sc_report_set_name     VARCHAR2 (60);

   p_wv_override_flag       VARCHAR2 (10);

   x_trip_id                VARCHAR2 (30);

   x_trip_name              VARCHAR2 (30);

   p_msg_data               VARCHAR2 (32000);

   fail_api                 EXCEPTION;
BEGIN
   x_return_status := wsh_util_core.g_ret_sts_success;

   p_action_code := 'CONFIRM';

   p_delivery_name := v_delivery_name;

   p_sc_action_flag := v_action;

   p_sc_close_trip_flag := 'Y'; -- Trip stop concurrent program will be submitted automatically

   p_defer_iface := 'N';

   wsh_deliveries_pub.
    delivery_action (p_api_version_number        => 1.0,
                     p_init_msg_list             => init_msg_list,
                     x_return_status             => x_return_status,
                     x_msg_count                 => x_msg_count,
                     x_msg_data                  => p_msg_data,
                     p_action_code               => p_action_code,
                     p_delivery_id               => p_delivery_id,
                     p_delivery_name             => p_delivery_name,
                     p_asg_trip_id               => p_asg_trip_id,
                     p_asg_trip_name             => p_asg_trip_name,
                     p_asg_pickup_stop_id        => p_asg_pickup_stop_id,
                     p_asg_pickup_loc_id         => p_asg_pickup_loc_id,
                     p_asg_pickup_loc_code       => p_asg_pickup_loc_code,
                     p_asg_pickup_arr_date       => p_asg_pickup_arr_date,
                     p_asg_pickup_dep_date       => p_asg_pickup_dep_date,
                     p_asg_dropoff_stop_id       => p_asg_dropoff_stop_id,
                     p_asg_dropoff_loc_id        => p_asg_dropoff_loc_id,
                     p_asg_dropoff_loc_code      => p_asg_dropoff_loc_code,
                     p_asg_dropoff_arr_date      => p_asg_dropoff_arr_date,
                     p_asg_dropoff_dep_date      => p_asg_dropoff_dep_date,
                     p_sc_action_flag            => p_sc_action_flag,
                     p_sc_close_trip_flag        => p_sc_close_trip_flag,
                     p_sc_create_bol_flag        => p_sc_create_bol_flag,
                     p_sc_stage_del_flag         => p_sc_stage_del_flag,
                     p_sc_trip_ship_method       => p_sc_trip_ship_method,
                     p_sc_actual_dep_date        => p_sc_actual_dep_date,
                     p_sc_report_set_id          => p_sc_report_set_id,
                     p_sc_report_set_name        => p_sc_report_set_name,
                     p_sc_defer_interface_flag   => p_defer_iface,
                     p_wv_override_flag          => p_wv_override_flag,
                     x_trip_id                   => x_trip_id,
                     x_trip_name                 => x_trip_name);

   IF (x_return_status != wsh_util_core.g_ret_sts_success)
   THEN
      wsh_util_core.get_messages ('Y',
                                  x_msg_summary,
                                  x_msg_details,
                                  x_msg_count);

      IF x_msg_count > 1
      THEN
         x_msg_data := x_msg_summary || x_msg_details;
      ELSE
         x_msg_data := x_msg_summary;
      END IF;

      p_ship_conf_status := 'E';
   ELSE
      p_ship_conf_status := 'S';
   END IF;

END erps_ship_confirm_delivery;

SHIP CONFIRMATION THROUGH FORMS
Navigate to Shipping responsibility >> Shipping >> Transactions
Query the delivery that need to be ship confirmed
click ship confirm button.

Refernces:

http://www.oracleerpappsguide.com/2013/09/r12-ship-confirm-using-api.html

Query to get the Concatenated Line Number on the Sales Order

SELECT   oh.org_id
       , order_number
       ,    ol.line_number
         || '.'
         || ol.shipment_number
         || '.'
         || ol.option_number
         || '.'
         || ol.component_number
         || '.'
         || ol.service_number line_number
    FROM oe_order_lines_all ol
       , oe_order_headers_all oh
   WHERE oh.header_id = ol.header_id
     AND oh.order_number = '&Order_number'
ORDER BY ol.line_number
       , ol.shipment_number
       , ol.option_number
       , ol.component_number
       , ol.service_number;

References:
http://oracleappssolutiions.blogspot.com/search/label/AOL%20And%20Generic%20SQLs?updated-max=2013-12-18T14:39:00-08:00&max-results=20&start=19&by-date=false

O2C Cycle tables

--Inventory
select *
from mtl_system_items_b msib
where msib.segment1='TEST-GJ1';

select *
from mtl_onhand_quantities_detail
where inventory_item_id=9727542;

select *
from mtl_onhand_quantities --view
where inventory_item_id=9727542;

--Order
select ooha.flow_status_code, ooha.*
from oe_order_headers_all ooha
where order_number=11067733;

select oola.flow_status_code, oola.*
from oe_order_lines_all oola
where header_id=40757700;

select wdd.released_status, wdd.*
from wsh_delivery_details wdd
where  source_header_id=40757700; --R(Ready to release), B(), C()

select wda.delivery_id, wda.*
from wsh_delivery_assignments wda
where delivery_detail_id=20924729;

select *
from wsh_new_deliveries
where delivery_id=8723686;

--
select *
from mtl_txn_request_headers
where 1=1
and request_number=23344489;
--and trunc(creation_date)=trunc(sysdate);

select *
from mtl_txn_request_lines
where header_id=23344490;

select *
from mtl_reservations
where inventory_item_id=9727542;

select *
from mtl_material_transactions
where inventory_item_id=9727542;

select *
from ra_interface_lines_all
where interface_line_attribute1='11067733';

--AR
select *
from ra_customer_trx_all
where trx_number='11071374';

select *
from ra_customer_trx_lines_all
where customer_trx_id=5888998;

select rcda.event_id, rcda.*
from ra_cust_trx_line_gl_dist_all rcda
where customer_trx_line_id=8918096;

--Accounting(transactions)
select *
from xla_events
where event_id=46887553;

select *
from xla_ae_headers
where event_id=46887553;

select *
from xla_ae_lines
where ae_header_id=23181811;

select  *
from gl_interface
where reference26=46887553;

--Ledger
select *
from gl_je_batches
where name='Receivables A 10207531 81076218';

select *
from gl_je_headers
where je_batch_id=14085655;

select *
from gl_je_lines
where je_header_id=24716354;

--Posting
select *
from gl_balances
where ledger_id=2022
and code_combination_id in (3006,
7000027,
7000032,
5854)
and period_name='Aug-16'
and trunc(last_update_date)=trunc(sysdate);

--Receipts
select *
from ar_cash_receipts_all acra
where receipt_number='RECEIPT_11067733';

select acrha.event_id, acrha.*
from ar_cash_receipt_history_all acrha
where cash_receipt_id=11822798;

select *
from ar_receivable_applications_all
where cash_receipt_id=11822798;

select *
from ar_payment_schedules_all
where cash_receipt_id=11822798;

--Accounting(Receipts)
select *
from xla_events
where event_id=46887554;

select *
from xla_ae_headers
where event_id=46887554;

select *
from xla_ae_lines
where ae_header_id=23181812;

select  *
from gl_interface
where reference26=46887554;

--Ledger
select *
from gl_je_batches
where name='Receivables A 10207532 81076345';

select *
from gl_je_headers
where je_batch_id=14085657;

select *
from gl_je_lines
where je_header_id=24716356;

--Posting
select *
from gl_balances
where ledger_id=2022
and code_combination_id in (3006,
5092)
and period_name='Aug-16'
and trunc(last_update_date)=trunc(sysdate);

ORDER TO CASH (O2C) Process Flow with Effected Tables in ORACLE EBS R12

LET LOOK the FLOW PART FOR O2C 

• Order  Entry : This is first stage , When the order is entered in the system 
• Order Booking: This is next stage , When the order is booked then the flow status changed from entered into Booked
• Pick Release : Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick then particular sales order 
• Pick confirm / Move order Transaction : Items transferred from Source sub inventory into staging sub inventory
• Ship Confirm: Items are loaded in Truck / Transportation Mode for the delivery to customers 
• Invoice Generation : Invoice is generated
• Close Order 

O2C - TABLE FLOW STATUS 

=> OE_ORDER_HEADERS_ALL - Flow Status Code

1. ENTERED
2. CANCELLED
3. CLOSED
4. BOOKED

=> OE_ORDER_LINES_ALL - Flow Status Code

1. ENTERED
2. REPRICE_COMPLETE
3. CANCELLED
4. AWAITING_SHIPPING
5. CLOSED
6. BOOKED
7. INVOICE_HOLD
8. AWAITING_FULFILLMENT
9. REPRICE_PRICING_ERROR

=> WSH_DELIVERY_DETAILS - Released Status

1. B:Backordered
2. C:Shipped 
3. D:Cancelled 
4. N:Not ready to release 
5. R:Ready to release 
6. S:Released to warehouse 
7. X:Not Applicable 
8. Y:Staged

Step 1; Creating / Entered New Sales Order 

Select * from OE_ORDER_HEADERS_ALL where  ORDER_NUMBER = '830001659'; 

select * from OE_ORDER_HEADERS_ALL where Header_ID = 21009209;

select Flow_status_code from OE_order_Headers_all 

                                where Header_ID = 21009209; -->ENTERED STATUS

select Flow_status_code from OE_ORDER_LINES_ALL 

                               where Header_ID = oe_header_id -->ENTERED 

Step 2; Booked the Sales Order 

Once Order is booked or confirmed then 

select Flow_status_code from OE_order_Headers_all 

                                where Header_ID = 21009209; -->BOOKED STATUS

select Flow_status_code from OE_ORDER_LINES_ALL 

                               where Header_ID = oe_header_id -->AWAITING_SHIPPING

select * from WSH_DELIVERY_DETAILS

                                 where source_header_ID =   OE_HEADER_ID  

select released_status from WSH_DELIVERY_DETAILS

                              where source_header_ID =   OE_HEADER_ID  --> R - READY To RELEASE

Step 3; Release Sales order / Pick Release , Pick Confirm (Staging Location)

Once Release order and pick release some concurrent program trigger out 

1. Pick Selection List Generation 

2. Pick Slip Report ( We can get Move Order Number) 

Select * from MTL_TXN_REQUEST_HEADERS 

            where Request_Number= '<<Move Oder Number(getting from PICKSLIP Report)>>';

Select * from MTL_TXN_REQUEST_LINES where HEADER_ID = '<<Header_ID>>';

Select * from WSH_DELIVERY_DETAILS 

               where Released_Status = 'S';  -- Released to warehouse (Pick Release)

Select * from WSH_DELIVERY_DETAILS

           where Released_Status = 'Y'; -- Staging 

(Pick Confirm , Line has been released to inventory for Processing)

Select * from WSH_picking_batches; --- After batch is created for pick release.

Select * from MTL_Reservations 

             where Inventory_Item_ID = '<<MTL_ITEM_ID>>'; -- Soft move , Not for physical Move

select * From WSH_NEW_DELIVERIES 

             where Deliverd_ID = '<<Delivery_ID- wsh_delivery_assignments>>';

Step 4; Ship Confirm

once ship confirmation done, then it will be triggered out 5 concurrent programs 

1. Bill of Landing , 

2. packing Slip Report , 

3. Commerical Invoice ,

4. Vechile Load Sheet , 

5. Interface Trip stop 

Select * from WSH_DELIVERY_DETAILS where Released_Status = 'C';  -- Shipped

Select flow_status_Code from OE_ORDER_LINES_ALL 

                                       where header_ID = 'Oe_header_ID';  --> Shipped Status

Select * FROM MTL_REVERSATIONS 

               WHERE Inventory_Item_ID = 'ITEM_ID'; --> will be FREEZE

Step 5 : Auto Invoice: 

After Successful completed the respective CC program then run the work flow background process

to Move AR module and Generate Invoice 

 -- >>  RUN WorkFlow - 'WORK FLOW BACKGROUND PROCESS ' INTERFACE 

 --.>>  Auto Invoice Program 

  select * from RA_Interface_lines_all 

                 where Interface_LINE_ATTRIBUTE1 ='ORDER_NUMBER';

  Select * from RA_Interface_Distributions_all 

                  where Interface_Line_ID  ='LINE ID'; 

Step 6 : Account Receivable:

   Select * FROM RA_CUSTOMER_TRX_ALL 

                   Where TRX_NUMBER = 'Invoice_number';

   Select * FROM RA_CUSTOMER_TRX_lines_All 

                   where Customer_Trx_ID = '';

Step 7 : General Ledger:

  Select * From GL_JE_BATCHES Where Name ='Receivables A 155 etc';

  Select * From GL_JE_Headers where JE_Batch_ID ='';

  Select * from GL_JE_LInes where JE_Header_ID = '';

SAMPLE QUERY FOR JOIN BETWEEN  OM, WSH, AR TABLES

SELECT ooh.order_number ,

  ool.line_id ,

  ool.ordered_quantity ,

  ool.shipped_quantity ,

  ool.invoiced_quantity ,

  wdd.delivery_detail_id ,

  wnd.delivery_id ,

  rctl.interface_line_attribute1 ,

  rctl.interface_line_attribute3 ,

  rctl.interface_line_attribute6 ,

  rct.org_id ,

  rct.creation_date ,

  trx_number ,

  rctl.quantity_ordered ,

  rct.interface_header_context

FROM oe_order_headers_all ooh ,

  oe_order_lines_all ool ,

  wsh_delivery_details wdd ,

  wsh_new_deliveries wnd ,

  wsh_delivery_assignments wda ,

  ra_customer_trx_all rct ,

  ra_customer_trx_lines_all rctl

WHERE ooh.header_Id               =ool.header_id

AND wdd.source_header_id          =ooh.header_id

AND wdd.delivery_detail_Id        =wda.delivery_detail_id

AND wda.delivery_id               =wnd.delivery_id

AND rctl.interface_line_attribute1=TO_CHAR(ooh.order_number)

AND rctl.interface_line_attribute6=TO_CHAR(ool.line_id)

AND rctl.interface_line_attribute3=TO_CHAR(wnd.delivery_id)

AND rctl.customer_trx_id          =rct.customer_trx_id;

--      AND rct.interface_header_context='ORDER ENTRY'

/

Example query linking MTL_MATERIAL_TRANSACTIONS to the move order:

SELECT mmt.transaction_id,

  tol.organization_id,

  toh.request_number,

  toh.header_id,

  tol.line_number,

  tol.line_id,

  tol.inventory_item_id,

  toh.description,

  toh.move_order_type,

  tol.line_status,

  tol.quantity,

  tol.quantity_delivered,

  tol.quantity_detailed

FROM mtl_txn_request_headers toh,

  mtl_txn_request_lines tol,

  mtl_material_transactions mmt

WHERE toh.header_id     = tol.header_id

AND toh.organization_id = tol.organization_id

AND tol.line_id         = mmt.move_order_line_id

AND toh.request_number  = '&EnterMONumber' 

/

SQL Statement to find out the Drop Ship SO and corresponding Requistion and PO details along with their status

select ooh.order_number

       ,ool.ordered_item

       ,ool.ordered_quantity

       ,ooh.flow_status_code header_status                   

       ,ool.flow_status_code line_status

       ,prha.segment1 requisition

       ,poh.segment1 po_number

       ,poh.closed_code po_status

       ,pll.quantity

       ,pll.quantity_received

       ,pll.closed_code po_shipment_status

fromapps.oe_order_headers_all                   ooh

    ,apps.oe_order_lines_all                     ool

    ,apps.oe_drop_ship_sources                   odss

    ,apps.po_requisition_headers_all             prha

    ,apps.po_headers_all                         poh

    ,apps.po_lines_all                           pol

    ,apps.po_line_locations_all                  pll

              

whereool.header_id = ooh.header_id

and   odss.header_id = ooh.header_id

and   odss.line_id = ool.line_id

and   prha.requisition_header_id = odss.requisition_header_id

and   poh.po_header_id = odss.po_header_id

and   pol.po_line_id = odss.po_line_id

and   pol.po_header_id = poh.po_header_id

and   pll.po_line_id = pol.po_line_id

and   ooh.order_number = ‘89899’;

References:

http://appsraj.blogspot.com/search?updated-min=2015-01-01T00:00:00-08:00&updated-max=2016-01-01T00:00:00-08:00&max-results=1