Wednesday, March 30, 2016

Query to get the order and invoice details,Joins between OM and AR



Joins between OM and AR
Modules Involved : AR, OM
Description      : get the invoices and order details


SELECT ooha.order_number        Order_Number,
       rct.trx_number           Invoice_number,
       rctl.line_type           Invoice_Line_Type,
       msi.segment1             Item_Number,
       rctl.quantity_ordered    Quantity_ordered,
       rctl.quantity_invoiced   Quantity_invoiced,
       rctl.unit_standard_price Unit_standard_price,
       rctl.unit_selling_price  Unit_selling_price,
       RCTL.EXTENDED_AMOUNT     EXTENDED_AMOUNT
       --,rctl.*
  FROM apps.oe_order_headers_all      ooha,
       apps.ra_customer_trx_all       rct,
       APPS.RA_CUSTOMER_TRX_LINES_ALL RCTL,
       apps.mtl_system_items_b          msi
 WHERE 1=1
   AND to_char(ooha.order_number)      = rct.interface_header_attribute1
   AND rct.customer_trx_id             = rctl.customer_trx_id
   and RCTL.INVENTORY_ITEM_ID          = MSI.INVENTORY_ITEM_ID
   AND msi.organization_id             = 65 -- Master
   and RCT.TRX_NUMBER                  = '284968'
   --and RCTL.INTERFACE_LINE_ATTRIBUTE2  = 'Test_Order_Type'
   ;

No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...