Oracle AR transaction Query for EBS R12

 Oracle AR transaction query

SELECT RCT.customer_trx_id,
       RCT.trx_number "TRX NUMBER",
       RCT.trx_date   "TRX DATE",
       RCG.gl_date,
       RCT.purchase_order,
       OOD.organization_id,
       OOD.organization_name,
       RCL.description,
       HCA.account_number,
       HP.party_name,
       RCL.line_type  AR_LINE_TYPE,
       SUM(( Decode(RCT.invoice_currency_code, 'INR', RCG.amount * 1,
                                               RCG.amount * RCT.exchange_rate) )
       )
                      TOTAL_INV_AMOUNT
FROM   org_organization_definitions OOD,
       hz_cust_accounts HCA,
       hz_parties HP,
       ra_customer_trx_all RCT,
       ra_customer_trx_lines_all RCL,
       ra_cust_trx_line_gl_dist_all RCG
WHERE  RCT.customer_trx_id = RCL.customer_trx_id
       AND RCL.customer_trx_line_id = RCG.customer_trx_line_id
       AND RCT.bill_to_customer_id = HCA.cust_account_id
       AND rct.interface_header_context = 'ORDER ENTRY'
       AND rcl.interface_line_context = 'ORDER ENTRY'
       AND HP.party_name = :p_customer_name
       AND RCT.trx_number = '-----------'
       AND HCA.party_id = HP.party_id
       AND To_number(RCT.interface_header_attribute10) = OOD.organization_id (+)
       AND RCT.trx_date BETWEEN :P_FROM_DATE AND :P_TO_DATE
GROUP  BY RCT.customer_trx_id,
          RCT.trx_number,
          RCT.cust_trx_type_id,
          RCT.trx_date,
          RCG.gl_date,
          RCT.customer_trx_id,
          RCT.purchase_order,
          OOD.organization_name,
          RCL.description,
          HCA.account_number,
          OOD.organization_id,
          HP.party_name,
          RCL.line_type; 

Query to get return Sales Order in Oracle APPS R12

Query to get Return Sales Order(RMA: Return Material Authorization) in Oracle APPS R12

SELECT ooha.order_number "SALES ORDER",
       ac.customer_name,
       ooha.order_category_code,
       oola.ordered_item,
       oola.ordered_quantity,
       rsl.quantity_shipped,
       rsl. quantity_received,
       oola.subinventory,
       shipment_num,
       rsh.receipt_num,
       rsh.attribute_category,
       rsl.unit_of_measure,
       rsl.item_description,
       rsl.shipment_line_status_code,
       rsl.source_document_code,
       RCT.trx_number    trx_number
FROM   apps.oe_order_headers_all ooha,
       apps.oe_order_lines_all oola,
       ra_customer_trx_all RCT,
       apps.rcv_shipment_headers rsh,
       apps.ar_customers ac,
       apps.rcv_shipment_lines rsl,
       apps.hz_cust_accounts hca
WHERE  1 = 1
       AND ooha.header_id = oola.header_id
       AND RCT.interface_header_context = 'ORDER ENTRY'
       AND RCT.interface_header_attribute1 = To_char (ooha.order_number)
       AND ooha.header_id = rsl.oe_order_header_id
       AND rsh.shipment_header_id = rsl.shipment_header_id
       AND rsl.oe_order_line_id = oola.line_id
       AND ooha.order_number = :P_ORDR_NUMBER
       AND ooha.sold_to_org_id = hca.cust_account_id
       AND rsl.source_document_code = 'RMA'
       AND hca.cust_account_id = ac.customer_id; 

Insert Who Columns in Oracle Apps R12 Table

Standard WHO Columns

The following are the Standard WHO Columns in most of the Oracle tables:

1. created_by -> Keeps track of which user created the record(row) : FND_PROFILE.VALUE('USER_ID')
2. creation_date -> Stores the date on which a row was created : SYSDATE
3. last_update_by ->  Keeps track of who last updated the row : FND_PROFILE.VALUE('USER_ID')
4. last_update_date -> Stores the date on which the row was last updated : SYSDATE
5. last_update_login -> Login Session ID of the user : USERENV('SESSIONID')