Pages

Thursday, 21 July 2016

CUSTOM BUSINESS EVENT IN ORACLE APPLICATION

Create Custom Business event in Oracle Application

Business Event is an occurrence of the business activity. Business events raise by oracle workflows or forms code or Pl/SQL code.

Next, need to create subscription. Subscription is an activity to be performed on occurrence of a business event.We can able to create more one subscriptions in the business event.
Below can through how to create business events following the steps
      •  Create a custom Business event
      •  Create a subscription to the event
      •  Raise the event  from PL/SQL or workflow
      •   Consume the event in PL/SQL 
Step 1:

Create a Business event

Navigate to Workflow Administrator Web Applications  responsibility -> Business Events -> create Event 











    Define / create the Name , display name ,etc ., all mandatory fields 



   Step 2:

    Next click -> create subscription 

     
   Enter the Mandatory fields and define Action type as “Custom” and system as “Connection Name”

   
    Step 3:

   Give the PL/SQL Rule function as  “xx_demobe_subscribe” ( package name.function or function name).  Then define as owner name & owner tag. Then click apply 




    Sample Script :

  CREATE OR REPLACE FUNCTION APPS.xx_demobe_subscribe (
   p_subscription_guid   IN            RAW,
   p_event               IN OUT NOCOPY WF_EVENT_T)
   RETURN VARCHAR2
IS
   l_errm   VARCHAR2 (200);
BEGIN
   INSERT INTO xx_temp_for_demo (msg)
           VALUES (
                        'Event '
                     || p_event.getEventName ()
                     || ' . '
                     || TO_CHAR (SYSDATE,'RRRRMMDD HH24:MI:SS')
                     || ' . '
                     || 'Event Key=>'
                     || p_event.geteventkey ());
Return 'SUCCESS';
EXCEPTION
   WHEN OTHERS
   THEN

      l_errm := SUBSTR (SQLERRM, 1, 1500);

      INSERT INTO xx_temp_for_demo (msg)
              VALUES (
                           p_event.geteventkey ()
                        || ' . '
                        || TO_CHAR (SYSDATE, 'RRRRMMDD HH24:MI:SS')
                        || ' . ERROR:'
                        || l_errm
                        || ';');

      RETURN 'ERROR';
END xx_demobe_subscribe;
    

Wednesday, 13 May 2015

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’;


Sunday, 14 September 2014

Oracle EDI Application Transaction Code


EDI module provide standard interface for transactions with your trading partner.

Purchase Order:-

Transaction Code
Transaction Name
Direction
EDIFACT
832
Price/Sales Catalogue
Inbound
PRICAT
843
Response to Request for Quotation
Inbound
QUOTES
856
Ship Notice/Manifest
Inbound
DESADV
857
Shipment and Billing Notice
Inbound
-
824
Application Advice
Outbound
APERAK
850
Purchase Order
Outbound
ORDERS
860
Purchase Order Change
Outbound
ORDCHG

Order Management:-

Transaction Code
Transaction Name
Direction
EDIFACT
850
Purchase Oder
Inbound
ORDERS
860
Purchase Oder Change
Inbound
ORDCHG
855
Purchase Oder Acknowledgment
Outbound
ORDRSP
865
PO Change Acknowledgment
Outbound
ORDRSP

Oracle Payables:-

Transaction Code
Transaction Name
Direction
EDIFACT
810
Invoice
Inbound
INVOIC
857
Shipment and Billing Notice
Inbound
-
824
Application Advice
Outbound
APERAK
820
Payment Order/Remittance Advice
Outbound
PAYORD-REMADV
PAYEXT-REMADV

Oracle Receivables:-

Transaction Code
Transaction Name
Direction
EDIFACT
810
Invoice
Outbound
INVOIC
812
Credit Memo/Debit Memo
Outbound
CREADV/DEBADV

Process Manufacturing:-

Transaction Code
Transaction Name
Direction
EDIFACT
850
OPM: Purchase Order
Inbound
ORDERS
855
OPM: Purchase Order Acknowledgment
Outbound
ORDRSP
856
OPM: Ship Notice/Manifest
Outbound
DESADV


EDI Transaction Support:-

The following transactions are supported:
ASC X12EDIFACTDocument IDDescription
Inbound Transactions
810INVOICINIInbound Invoice
832PRICATCATIInbound Price / Sales Catalog
843QUOTESRRQIInbound Response to Request for Quote
850ORDERSPOIInbound Purchase Order
856DESADVASNIInbound Ship Notice / Manifest
857No equivalentSBNIInbound Shipping and Billing Notice
Outbound Transactions
824APERAKADVOOutbound Application Advice
810INVOICINOOutbound Invoice
820PAYORD / REMADVPYOOutbound Payment Order / Remittance Advice
830DELFORSPSOOutbound Planning Schedule
862DELJITSSSOOutbound Shipping Schedule
850ORDERSPOOOutbound Purchase Order
860
865
ORDCHG
ORDCHGACK
POCO
POCOACK
Outbound Purchase Order Change Request
Outbound Order Change Acknowledgment 
856DESADVDNSOOutbound Ship Notice / Manifest