Tag: Script

Invoice and Credit Memo Applied for Oracle R12

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

Read More »

Advertisement

Monitoring List between Sales Order and AR Invoice Oracle R12

andreasvictorLeave 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

Query List of Profile Options Oracle R12

andreasvictorLeave a comment 
/* Formatted on 2/26/2016 9:38:27 AM (QP5 v5.163.1008.3004) */
  SELECT b.user_profile_option_name "Long Name",
         a.profile_option_name "Short Name",
         DECODE (TO_CHAR (c.level_id),
                 '10001', 'Site',
                 '10002', 'Application',
                 '10003', 'Responsibility',
                 '10004', 'User',
                 'Unknown')
            "Level",
         DECODE (
            TO_CHAR (c.level_id),
            '10001', 'Site',
            '10002', NVL (h.application_short_name, TO_CHAR (c.level_value)),
            '10003', NVL (g.responsibility_name, TO_CHAR (c.level_value)),
            '10004', NVL (e.user_name, TO_CHAR (c.level_value)),
            'Unknown')
            "Level Value",
         c.PROFILE_OPTION_VALUE "Profile Value",
         c.profile_option_id "Profile ID",
         TO_CHAR (c.LAST_UPDATE_DATE, 'DD-MON-YYYY HH24:MI') "Updated Date",
         NVL (d.user_name, TO_CHAR (c.last_updated_by)) "Updated By"
    FROM apps.fnd_profile_options a,
         apps.FND_PROFILE_OPTIONS_VL b,
         apps.FND_PROFILE_OPTION_VALUES c,
         apps.FND_USER d,
         apps.FND_USER e,
         apps.FND_RESPONSIBILITY_VL g,
         apps.FND_APPLICATION h
   WHERE     1 = 1
         AND a.profile_option_name = b.profile_option_name
         AND a.profile_option_id = c.profile_option_id
         AND a.application_id = c.application_id
         AND c.last_updated_by = d.user_id(+)
         AND c.level_value = e.user_id(+)
         AND c.level_value = g.responsibility_id(+)
         AND c.level_value = h.application_id(+)
         AND UPPER(b.user_profile_option_name) LIKE '%'||:P_PROFILE_NAME||'%'
ORDER BY b.user_profile_option_name,
         c.level_id,
         DECODE (
            TO_CHAR (c.level_id),
            '10001', 'Site',
            '10002', NVL (h.application_short_name, TO_CHAR (c.level_value)),
            '10003', NVL (g.responsibility_name, TO_CHAR (c.level_value)),
            '10004', NVL (e.user_name, TO_CHAR (c.level_value)),
            'Unknown');