Pages

Friday, 17 November 2017

Procedure To Payable(P2P) Techno-Functional Follow

Overview Flow:


Requisition -> Request for quotation -> Quotation analysis -> Purchase Order -> Create Inventory Receipt -> Create Invoice -> Make payment against Invoice -> Creating Accounting -> Transfer to GL -> Post the Journal to GL

 P2P Techno - Functional Flow:

Creation of Requisition: -




















SELECT * FROM po_requisition_headers_all WHERE segment1 = '10222259';
SELECT * FROM po_requistion_lines_all WHERE requisition_header_id = '905489'
SELECT * FROM po_distributions_all where distribution_id in         (624201,624202,624203,624204);

Creation of Purchase order against requisition: -


SELECT * FROM po_headers_all WHERE segment1 = '15128255';
SELECT * FROM po_lines_all WHERE po_header_id = 998860;
SELECT * FROM po_lines_locations_all WHERE po_header_id = '998860'
SELECT * FROM po_distributions_all where po_header_id = '998860'
 (po_distributions_all .req_distribution_id = po_req_distributions_all.distribution_id)


Find PO / Requisition number:
SELECT DISTINCT
       reqh.segment1 Req_Number, ph.segment1 PO_Number, ph.po_header_id
  FROM po_headers_all ph,
       po_distributions_all pos,
       po_req_distributions_all rdis,
       po_requisition_lines_all rel,
       po_requisition_headers_all reqh
 WHERE     1 = 1
       AND ph.po_header_id = pos.po_header_id
       AND pos.req_distribution_id = rdis.distribution_id
       AND rdis.requisition_line_id = rel.requisition_line_id
       AND rel.requisition_header_id = reqh.requisition_header_id
       AND rel.requisition_header_id = '905489'

Receiving against Purchase Order: -
Navigation: -  Purchase Super User -> Receiving -> Receipt


Query the PO number 



Select number lines to create the receipt, enter the quantity, sub-Inventory. Next, click the ‘lot serial’ button and keen lot details, expiration date etc...,


Once click done, a receipt number will be created



SELECT * FROM rcv_shipment_headers WHERE receipt_num = '45074';
SELECT * FROM rcv_shipment_lines WHERE shipment_header_id = '869858';
SELECT * FROM rcv_transactions WHERE shipment_header_id = '869858';

Account Payable Module: -

Navigation: - Payable Super User -> Invoices -> Entry -> Invoices

Enter the po number and keen invoice amount, invoice date, invoice number, etc.


Invoice should match /validate based on receipt, po number ..


 And click the match button, next query PO number selects the line and enter what is your purchased quantity value 



Next Click the actions button and select validate check box and press ‘Ok’ to validate the invoice


Once validate done, Status changed into ‘Needs Revalidation’       

Re – enter, Invoice amount as per the total amount value and then re-do validation again, if it validation success, status should be changed into “validate”.

Creation of Accounting & Payment:
Next action to be perform the create the accounting 




Once accounting has been successfully created. The Accounted status should be update as ‘YES’


Invoices:

SELECT * FROM ap_invoices_all WHERE invoice_num = ‘TSTINV1234’;

SELECT * FROM ap_invoice_distributions_All WHERE invoice_id = 561266;

Invoice Holds:

SELECT * FROM ap_holds_all WHERE invoice_id = 561266;

SELECT * FROM ap_holds_release_name_v WHERE RELEASE_LOOKUP_CODE = 'VARIANCE CORRECTED';

If payment button disable means, verify whether Invoice got holds or Approved status as “Required”
If it Approved Status required means, make “force Manual Approval”

Go to Action button -> select Force Approval -> okay.

Now you can verify the approval status and any holds


Payments:

 Now go Action click “Pay in Full” 



 Enter required field 







Next, transfer payment details into GL by using concurrent program - ‘Transfer Journal Entries to GL’ or “Payables Transfer to General Ledger”  


Note: Transfer Journal Entries to GL’ is the common program for all the modules

Enter the ledger, end date parameters 


è View Payments
SELECT * FROM ap_invoice_payments_all WHERE Invoice_id  = 561266; --  get check_id
SELECT * FROM ap_payment_schedules_all WHERE Invoice_id  = 561266;


è Check payments
Select * from ap_checks_all WHERE Check_id in (select check_id from
ap_invoice_payments_all WHERE Invoice_id  = 561266);


è Check format
SELECT * FROM ap_check_formats;
SELECT * FROM ap_checkrun_conc_processes_all;

General Ledger:
Navigation -> Goto General Ledger Super User -> Journals -> Enter 





SELECT * FROM gl_je_headers WHERE JE_HEADER_ID = 1086045;

     
     SELECT * FROM gl_je_lines WHERE JE_HEADER_ID = 1086045;
     SELECT * FROM gl_je_batches WHERE JE_BATCH_ID = 736629;


Thursday, 21 July 2016

Create Custom Business Event & Subscription in Oracle EBS

Overview:

Function Usage:- capture the business event data like Item Key, Event data,etc ., and insert into staging table
Parameter Usage:- p_subscription_guid => From WF ,p_event => Event id from WF

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