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')

API to Close Sales Order Where SO Lines are already closed in Oracle EBS R12

 --API to Close Sales Order Where SO Lines are already closed

DECLARE
    l_file_val VARCHAR2(300);
    l_result   VARCHAR2(30);
    CURSOR header_cur IS
      SELECT h.header_id
      FROM   wf_item_activity_statuses st,
             oe_order_headers_all h,
             wf_process_activities wpa
      WHERE  wpa.instance_id = st.process_activity
             AND st.item_type = 'OEOH'
             AND wpa.activity_name = 'CLOSE_WAIT_FOR_L'
             AND wpa.process_name = 'CLOSE_HEADER_PROCESS'
             AND st.activity_status = 'NOTIFIED'
             AND st.item_key = To_char(h.header_id)
             AND h.header_id = 3654189
             AND h.open_flag = 'Y'
             AND h.flow_status_code = 'BOOKED'
             AND NOT EXISTS(SELECT 1
                            FROM   oe_order_lines_all oel
                            WHERE  oel.header_id = h.header_id
                                   AND oel.open_flag = 'Y');
BEGIN
    oe_debug_pub.Setdebuglevel(5);
    oe_debug_pub.debug_on;
    oe_debug_pub.initialize;
    l_file_val := oe_debug_pub.Set_debug_mode('FILE');
    dbms_output.Put_line('Debug File : ' ||l_file_val);

    FOR header_rec IN header_cur LOOP

        dbms_output.Put_line('Closing Header:' ||header_rec.header_id);
        oe_debug_pub.ADD('Closing Header:'||header_rec.header_id);
        oe_standard_wf.Oeoh_selector (p_itemtype => 'OEOH',
        p_itemkey => To_char(header_rec.header_id), p_actid => 12345,
        p_funcmode => 'SET_CTX', p_result => l_result);

        wf_engine.Handleerror('OEOH', header_rec.header_id, 'CLOSE_HEADER_PROCESS:CLOSE_HEADER', 'RETRY', NULL);

    END LOOP;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
      oe_debug_pub.ADD(' Error is : '||SQLERRM);
      dbms_output.Put_line('Error : '||SQLERRM);
      NULL;
END;
/*
Metalink Docs:
All Order Lines Are Closed Yet Header Remains Open [ID 145010.1]
Sales Order Header Does Not Close After ALL Lines Have Closed [ID 133837.1]
*/