Project Status in Oracle Project Costing

Last week, i got my task about solution of Oracle Project Costing for my client.
I have found interesting one, it was project status standard feature. Oracle EBS provide project status management using 2 form, they are Form Project Status Inquiry and Form Percent Complete.

Project Status Inquiry

Project Costing Responsibility > Project Status Inquiry > Project Status Inquiry



This form is informative and provides the project details, like available budget, total commitment and expenses, and also any kind of task which related to the project.


For additional info, procurement documents (include requisition & purchase order) and supplier invoice have content about project data, like project name, task name, expenditure name. That relations at document’s distribution level.

Project Status Inquiry

Project Costing Responsibility > Project Status Inquiry > Percent Complete


PMO can use this to do some updates, like update project precent, based on task, subtask, or that own project.


Invoice and Credit Memo Applied for Oracle R12

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

Read More »

Monitoring List between Sales Order and AR Invoice Oracle R12

/* 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,
         msi.segment1 sona_part_no,
         msi.description item_desc,
         (ractl.quantity_invoiced) quantity_invoiced,
         ractl.attribute3 kanban_no,
         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_name customer_name,
         ractl.attribute1 cust_po_no,
    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

Advanced Pricing for Oracle R12

At this moment, i wanted to share about pricing strategy module in Oracle E-Business Suite, especially for R12 version. There was a condition which client want to do something about pricing management from procurement and sales point of view.

The module was Advanced Pricing. It was the pricing engine for the Oracle E-Business Suite. As the overview, the module had context by product attribute – all items, item category, or item code. The module was related with qualifier and modifier. Qualifier was that tells about who will be charged that pricing. And the qualifier decides which modifier would give the price.

Based on Metalink documents, there were some documents which was described for it.

455778.1 : What Are The Steps Required To Setup And Use Advanced Pricing in Purchasing White Paper
739560.1 : How To Use Advance Pricing With Order Management And Purchasing
293991.1 : Advanced Pricing Is Not Defaulting Price From Price List
357657.1 : FAQ Using Advanced Pricing in Purchase Orders and Requisitions
361233.1 : Purchasing & Advanced Pricing Integration: Default is List Price
299698.1 : 11.5.10: Does Advanced Pricing In Purchasing Require Oracle Pricing To Be Installed
312665.1 : Advanced Pricing Is it Possible To Use Manual Modifiers ?
293793.1 : What Are the Profile Options Required for Advanced Pricing in Oracle Purchasing

Source :
Oracle Community
Oracleapps88 Blogspot