Technical

Log Activities in Oracle E-Business Suite R12

/ andreasvictor / Leave a comment

Hi, i want to post about log activities in Oracle E-Business Suite R12. My post based on Oracle Metalink Support, and i have prove them in Vision instance my office. I think, whenever we have issues and the error messages in the logs are not enough. Setting those profile options with higher level will allow collect/obtain more details about the error.

These are few examples which related into profile option for log activities:

How To Trace and Debug Oracle Self Service Web Applications For EBS CRM [ID 399229.1]
How To Get Log Files In iProcurement [ID 290614.1]
How to Generate Receiving Transaction Debug Statements in 11.5.10 and R12 [ID 299497.1]
How to Capture Debug / Log / Trace Files During the Purchase Order / Requisition Approval Process? [ID 409155.1]
How to get Required Configurator Log Files for Runtime Specific Issues [ID 313695.1]

And based on concern DBA team, we never set this profile option in production database to Yes at the site level.

Thank you.

Invoice and Credit Memo Applied for Oracle R12

/ andreasvictor / Leave a comment

Hi,
I want to share about Invoice and Credit Memo apply in Oracle R12 module Account Receivables.

Query to find transactions which integrated between Invoice and Credit Memo.

--Query List for AR credit memo transactions
SELECT payment_schedule_id,ract.customer_trx_id,
       ract.trx_number,
       ract.org_id,ractl.extended_amount,
       ract.reason_code,
       ractl.line_type,
       ABS(ractl.gross_extended_amount) gross_extended_amount
FROM ar_payment_schedules_all ps, ra_customer_trx_all ract, ra_customer_trx_lines_all ractl
WHERE     ps.customer_trx_id = ract.customer_trx_id
     AND ract.customer_trx_id = ractl.customer_trx_id
     AND ract.customer_trx_id = ractl.customer_trx_id
     AND ract.org_id = ractl.org_id
     AND ract.org_id = :p_org_id
     AND ractl.line_type = 'LINE'
     AND ract.trx_number = :p_cm_number
ORDER BY ract.customer_trx_id;

Continue reading →

Monitoring List between Sales Order and AR Invoice Oracle R12

/ andreasvictor / Leave a comment 

/* Formatted on 4/25/2016 4:39:02 PM (QP5 v5.163.1008.3004) */
/*
query monitoring from Sales order to AR Invoice
*/
  SELECT hdr.order_number,
         TO_CHAR (hdr.ordered_date, 'dd-MON-yyyy') ord_date,
         dtl.line_number,
         msi.segment1 sona_part_no,
         msi.description item_desc,
         dtl.ordered_quantity,
         (ractl.quantity_invoiced) quantity_invoiced,
         ractl.attribute3 kanban_no,
         dtl.unit_selling_price,
         ract.trx_number,
         TO_CHAR (ract.trx_date, 'dd-MON-yyyy') txn_date,
         TO_CHAR (ractl.creation_date, 'yyyy/mm/dd') invoice_date,
         TO_CHAR (ractl.creation_date, 'hh24:mm:SS') invoice_time,
         rac.customer_number,
         rac.customer_name customer_name,
         ractl.attribute1 cust_po_no,
         extended_amount,
         ractl.revenue_amount
    FROM oe_order_headers_all hdr,
         oe_order_lines_all dtl,
         ra_customer_trx_lines_all ractl,
         mtl_system_items_b msi,
         ra_customer_trx_all ract,
         ar_customers rac
   WHERE     hdr.header_id = dtl.header_id
         AND hdr.org_id = dtl.org_id
         AND ractl.interface_line_attribute1 = TO_CHAR (hdr.order_number)
         AND ractl.sales_order = hdr.order_number
         AND ractl.interface_line_attribute6 = TO_CHAR (dtl.line_id)
         AND msi.inventory_item_id = dtl.inventory_item_id
         AND msi.organization_id = hdr.ship_from_org_id
         AND ract.customer_trx_id = ractl.customer_trx_id
         AND ract.org_id = ractl.org_id
         AND rac.customer_id = ract.ship_to_customer_id
         AND ractl.line_type = 'LINE'
         --AND TRUNC (ract.creation_date) > '01-APR-2016'
         --AND hdr.sold_to_org_id = ract.ship_to_customer_id
         AND hdr.order_number = :p_order_number --'160102000047'
         -- AND NVL (:p_trx_number, ract.trx_number) = :p_trx_number
ORDER BY hdr.order_number, hdr.ordered_date, dtl.line_number