Saturday, June 6, 2015

Oracle Order To Cash (O2C) Queries

OM Open Backlog Orders
  SELECT A.HEADER_ID,
         A.ORG_ID,
         A.ORDER_NUMBER,
         A.ORDER_TYPE_ID,
         A.ORDER_CATEGORY_CODE,
         A.FLOW_STATUS_CODE,
         A.SHIP_FROM_ORG_ID,
         D.INVENTORY_ITEM_ID,
         B.LINE_NUMBER,
         B.SCHEDULE_SHIP_DATE,
         C.NAME,
         B.ORDERED_QUANTITY,
         B.CANCELLED_QUANTITY,
         B.SHIPPED_QUANTITY,
         B.SCHEDULE_SHIP_DATE
    FROM OE_ORDER_HEADERS_ALL A,
         OE_ORDER_LINES_ALL B,
         HR_ALL_ORGANIZATION_UNITS C,
         MTL_SYSTEM_ITEMS_B D
   WHERE     A.HEADER_ID = B.HEADER_ID
         AND A.FLOW_STATUS_CODE NOT LIKE 'CLOSED'
         AND A.SHIP_FROM_ORG_ID(+) = C.ORGANIZATION_ID
         AND A.INVENTORY_ITEM_ID = D.INVENTORY_ITEM_ID
         AND A.ORG_ID = :P_ORG_ID
         AND B.SCHEDULE_SHIP_DATE < SYSDATE
GROUP BY A.HEADER_ID,
         A.ORG_ID,
         A.ORDER_NUMBER,
         A.ORDER_TYPE_ID,
         A.ORDER_CATEGORY_CODE,
         A.FLOW_STATUS_CODE,
         A.SHIP_FROM_ORG_ID,
         B.INVENTORY_ITEM_ID,
         B.LINE_NUMBER,
         B.SCHEDULE_SHIP_DATE,
         C.NAME,
         B.ORDERED_QUANTITY,
         B.CANCELLED_QUANTITY,
         B.SHIPPED_QUANTITY,
         B.SCHEDULE_SHIP_DATE
ORDER BY B.LINE_NUMBER

OM ZERO ON HAND Qty
SELECT DISTINCT m.segment1 AS item,
                h.order_number,
                h.flow_status_code,
                l.ordered_quantity
  FROM oe_order_headers_all h, oe_order_lines_all l, mtl_system_items_b m
 WHERE     l.HEADER_ID = h.header_id
       AND l.INVENTORY_ITEM_ID = m.INVENTORY_ITEM_ID
       AND l.inventory_item_id NOT IN (SELECT inventory_item_id FROM mtl_onhand_quantities)
       AND h.flow_status_code NOT IN ('CLOSED', 'CANCELLED')

OM Backloc Repoer For All Operating Units
SELECT a.order_number "so_Num",
       a.ordered_date,
       a.transactional_curr_code "currency",
       a.order_category_code "order_type",
       soldfrom (a.sold_from_org_id) "sold_from",
       soldto (A.SOLD_TO_ORG_ID) "Customer",
       shipfrom (a.ship_from_org_id) "shiipping_from",
       organizations (b.org_id) "operating_unit",
       b.ordered_item "item_name",
       b.OPEN_FLAG "so_status_open"
  FROM OE_ORDER_HEADERS_all a, OE_ORDER_LINES_all b
 WHERE     a.header_id = b.header_id
       AND a.OPEN_FLAG = 'Y'
       AND b.open_flag = 'Y'
       AND A.SOLD_TO_ORG_ID = :SOLD_TO
       AND A.SHIP_FROM_ORG_ID = :SHIP_FROM

OM Related Queries
SELECT NVL (SUM (NVL (moq.transaction_quantity, 0) * (NVL (cic.item_cost, 0))),0)
  FROM mtl_onhand_quantities moq,
       cst_item_costs cic,
       te_inv_org_operating_units_v inv_ou
 WHERE     cic.inventory_item_id = moq.inventory_item_id
       AND cic.cost_type_id = :g_num_cost_type_id
       AND cic.organization_id = moq.organization_id
       AND moq.inventory_item_id = :in_num_inventory_item_id
       AND inv_ou.org_id = NVL ( :in_num_operating_unit, inv_ou.org_id)
       AND inv_ou.organization_id = moq.organization_id

SELECT UNIQUE rct.trx_number, rct.trx_date, rctt.TYPE
  FROM oe_order_headers_all oha,
       ra_customer_trx_lines_all rctl,
       ra_customer_trx_all rct,
       ra_cust_trx_types_all rctt
 WHERE     rctl.interface_line_context = 'ORDER ENTRY'
       AND rctl.interface_line_attribute1 = oha.order_number
       AND rctl.customer_trx_id = rct.customer_trx_id
       AND rctt.cust_trx_type_id = rct.cust_trx_type_id
       AND oha.header_id = :in_num_header_id
       AND rct.trx_date =
              (SELECT MAX (TRX_DATE)
                 FROM ra_customer_trx_lines_all rctla,
                      ra_customer_trx_all rcta
                WHERE     rctla.interface_line_context =
                             'ORDER
                         ENTRY'
                      AND rctla.interface_line_attribute1 = oha.order_number
                      AND rcta.customer_trx_id = rctla.customer_trx_id)
       AND rct.trx_number =
              (SELECT MAX (TRX_number)
                 FROM ra_customer_trx_lines_all rctla,
                      ra_customer_trx_all rcta
                WHERE     rctla.interface_line_context ='ORDER ENTRY'
                      AND rctla.interface_line_attribute1 = oha.order_number
                      AND rcta.customer_trx_id = rctla.customer_trx_id)

The same functionality can be provided by re writing the SQL as follows, in the process the cost gets reduced to almost half of the first query.
SELECT MAX (rct.trx_number), MAX (rct.trx_date), MAX (rctt.TYPE)
  FROM oe_order_headers_all oha,
       ra_customer_trx_all rct,
       ra_cust_trx_types_all rctt
 WHERE     rct.interface_header_context = 'ORDER ENTRY'
       AND rct.interface_header_attribute1 = oha.order_number
       AND rctt.cust_trx_type_id = rct.cust_trx_type_id
       AND oha.header_id = :in_num_header_id

  SELECT haou.organization_id,
         haou.name,
         hca.cust_account_id,
         hp.party_name,
         hca.account_number,
         hp.category_code,
         hca.sales_channel_code,
         hca.attribute1,
         SUBSTR (hca.attribute2, 5, 3),
         hp.country,
         haou.attribute1,
         MAX (ola.actual_shipment_date) shp_dte
    FROM hz_cust_accounts hca,
         hz_parties hp,
         oe_order_headers_all oha,
         oe_order_lines_all ola,
         hr_all_organization_units haou,
         ra_customers rac,
         ra_addresses_all raa
   WHERE     hp.party_id = hca.party_id(+)
         AND oha.header_id(+) = ola.header_id
         AND ola.sold_to_org_id(+) = hca.cust_account_id
         AND haou.TYPE(+) = 'OU'
         AND raa.party_id = hp.party_id
         AND rac.party_id = raa.party_id
         AND hca.status = 'A'
         AND raa.org_id = haou.organization_id
         AND EXISTS
                (SELECT 1
                   FROM RA_CUSTOMER_TRX_ALL RCTA
                  WHERE     HCA.cust_account_id = RCTA.bill_to_customer_id
                        AND RCTA.ORG_ID = haou.organization_id)
GROUP BY haou.name,
         haou.organization_id,
         hca.cust_account_id,
         hp.party_name,
         hca.account_number,
         hca.sales_channel_code,
         hca.attribute2,
         hp.country,
         hca.attribute1,
         hp.category_code,
         haou.attribute1
ORDER BY shp_dte DESC

  SELECT - (SUM (ordered_quantity * unit_selling_price)),
         - (SUM (shipped_quantity * unit_selling_price))
    INTO out_num_ordered_amt, out_num_shipped_amt
    FROM oe_order_lines_all
   WHERE header_id = in_num_header_id AND booked_flag = 'Y'
GROUP BY header_id;

SELECT NVL (SUM ( (oela.ordered_quantity, NVL (oela.shipped_quantity, 0))),0)
  FROM oe_order_headers_all oeha, oe_order_lines_all oela
 WHERE     oela.header_id = oeha.header_id
       AND oela.org_id = NVL ( :in_num_operating_unit, oela.org_id)
       AND oela.inventory_item_id = NVL ( :in_num_inventory_item_id, oela.inventory_item_id)
       AND oela.open_flag = 'Y'
       AND oela.cancelled_flag = 'N'
       AND oela.booked_flag = 'Y'
       AND oeha.header_id = 1234

  SELECT --+choose
        oela.header_id HEADER_ID,
         oela.line_id LINE_ID,
         oela.inventory_item_id INVENTORY_ITEM_ID,
         oeha.order_number ORDER_NUMBER,
         TRUNC (oeha.ordered_date) ORDERED_DATE,
         DECODE (
            oela.option_number,
            NULL, oela.line_number || '.' || oela.shipment_number,
               oela.line_number
            || '.'
            || oela.shipment_number
            || '.'
            || oela.option_number)
            LOGICAL_LINE_NUMBER,
         te_inventory_inq_pkg.get_line_status (oela.flow_status_code, oela.line_id)
            LINE_FLOW_STATUS,
         SUBSTRB (hp.party_name, 1, 50) CUSTOMER_NAME,
         oela.ordered_quantity ORDERED_QUANTITY,
         oela.shipped_quantity SHIPPED_QUANTITY,
         oeha.sold_to_org_id SOLD_TO_ORG_ID,
         oeha.cust_po_number CUST_PO_NUMBER,
         oeha.request_date REQUESTED_DATE,
         oela.actual_shipment_date ACTUAL_SHIPMENT_DATE,
         oela.schedule_ship_date SCHEDULE_SHIP_DATE,
         oela.ship_from_org_id SHIP_FROM_ORG_ID,
         mp.organization_code ORGANIZATION_CODE,
         oela.org_id ORG_ID,
         DECODE (mp.attribute1, 'LAGOS', wnd.attribute1, wnd.name) DELIVERY_NAME
    FROM mtl_parameters mp,
         oe_order_headers_all oeha,
         oe_order_lines_all oela,
         wsh_delivery_details wdd,
         wsh_delivery_assignments wda,
         hz_cust_accounts hca,
         hz_parties hp,
         wsh_new_deliveries wnd
   WHERE     hp.party_id = hca.party_id
         AND oeha.sold_to_org_id = hca.cust_account_id
         AND oela.ship_from_org_id = mp.organization_id
         AND oeha.header_id = oela.header_id
         AND oela.line_id = wdd.source_line_id
         AND wdd.delivery_detail_id = wda.delivery_detail_id(+)
         AND wda.delivery_id = wnd.delivery_id(+)
GROUP BY oela.header_id,
         oela.line_id,
         oela.inventory_item_id,
         oeha.order_number,
         oeha.ordered_date,
         oela.line_number,
         oela.shipment_number,
         oela.option_number,
         oela.flow_status_code,
         hp.party_name,
         oeha.sold_to_org_id,
         oeha.cust_po_number,
         oela.ordered_quantity,
         oela.shipped_quantity,
         oeha.ordered_date,
         oeha.request_date,
         oela.actual_shipment_date,
         oela.schedule_ship_date,
         oela.ship_from_org_id,
         mp.organization_code,
         oela.org_id,
         mp.attribute1,
         wnd.attribute1,
         wnd.name;

OM Cancelled Orders With Reasons
  SELECT TO_CHAR (oolh.hist_creation_date, 'YYYY-MM-DD'),
         usr.description,
         oolh.reason_code,
         COUNT (DISTINCT oolh.header_id) Orders
    FROM apps.oe_order_lines_history oolh, apps.fnd_user usr
   WHERE     oolh.hist_creation_date > TRUNC (SYSDATE) - 90
         AND oolh.hist_type_code = 'CANCELLATION'
         AND oolh.last_updated_by = usr.user_id
GROUP BY TO_CHAR (hist_creation_date, 'YYYY-MM-DD'),
         usr.description,
         reason_code
ORDER BY 1

OM HOLDS Information
  SELECT /*+ use_nl (ooh hld ohs ohd ohr) */
        ooh.order_number "Order Number",
         ooh.ordered_date "Ordered Date",
         ooh.flow_status_code "Order Status",
         ooh.credit_card_approval_code "Credit Card Approval Code",
         ohd.name "Hold Name",
         ohs.released_flag "Hold Release Flag",
         ohr.release_reason_code "Hold Release Reason",
         ohr.creation_date "Hold Release Date",
         ooh.payment_type_code "Payment Type Code"
    FROM apps.oe_order_headers_all ooh,
         apps.oe_order_holds_all hld,
         apps.oe_hold_sources_all ohs,
         apps.oe_hold_definitions ohd,
         apps.oe_hold_releases ohr
   WHERE     ooh.ordered_date BETWEEN TO_DATE ('10-sep-2008 04:40:00','dd-mon-yyyy hh24:mi:ss')
                                  AND TO_DATE ('10-sep-2008 10:40:00', 'dd-mon-yyyy hh24:mi:ss')
         AND ooh.order_category_code = 'ORDER'
         -- Add some other conditions to filter desired orders
         AND hld.header_id(+) = ooh.header_id
         AND ohs.hold_source_id(+) = hld.hold_source_id
         AND ohd.hold_id(+) = ohs.hold_id
         -- Add hold name if your are looking for specific holds
         --  and def.name  = 'Time Out for Credit Card Auth'
         AND ohr.hold_release_id(+) = ohs.hold_release_id
ORDER BY ooh.order_number

OM AND AR Link Query
SELECT ooha.order_number,
       oola.line_number so_line_number,
       oola.ordered_item,
       oola.ordered_quantity * oola.unit_selling_price so_extended_price,
       rcta.trx_number invoice_number,
       rcta.trx_date,
       rctla.line_number inv_line_number,
       rctla.unit_selling_price inv_unit_selling_price
  FROM oe_order_headers_all ooha,
       oe_order_lines_all oola,
       ra_customer_trx_all rcta,
       ra_customer_trx_lines_all rctla
 WHERE     ooha.header_id = oola.header_id
       AND rcta.customer_trx_id = rctla.customer_trx_id
       AND rctla.interface_line_attribute6 = TO_CHAR (oola.line_id)
       AND rctla.interface_line_attribute1 = TO_CHAR (ooha.order_number)
       AND order_number = :p_order_number

SELECT ooh.order_number,
       hp_bill.party_name,
          hl_ship.address1
       || DECODE (hl_ship.address2, NULL, '', CHR (10))
       || hl_ship.address2
       || DECODE (hl_ship.address3, NULL, '', CHR (10))
       || hl_ship.address3
       || DECODE (hl_ship.address4, NULL, '', CHR (10))
       || hl_ship.address4
       || DECODE (hl_ship.city, NULL, '', CHR (10))
       || hl_ship.city
       || DECODE (hl_ship.state, NULL, '', ',')
       || hl_ship.state
       || DECODE (hl_ship.postal_code, '', ',')
       || hl_ship.postal_code
          ship_to_address,
          hl_bill.address1
       || DECODE (hl_bill.address2, NULL, '', CHR (10))
       || hl_bill.address2
       || DECODE (hl_bill.address3, NULL, '', CHR (10))
       || hl_bill.address3
       || DECODE (hl_bill.address4, NULL, '', CHR (10))
       || hl_bill.address4
       || DECODE (hl_bill.city, NULL, '', CHR (10))
       || hl_bill.city
       || DECODE (hl_bill.state, NULL, '', ',')
       || hl_bill.state
       || DECODE (hl_bill.postal_code, '', ',')
       || hl_bill.postal_code
          bill_to_address,
       ooh.transactional_curr_code currency_code,
       mp.organization_code,
       ooh.fob_point_code,
       ooh.freight_terms_code,
       ooh.cust_po_number
  FROM oe_order_headers_all ooh,
       hz_cust_site_uses_all hcs_ship,
       hz_cust_acct_sites_all hca_ship,
       hz_party_sites hps_ship,
       hz_parties hp_ship,
       hz_locations hl_ship,
       hz_cust_site_uses_all hcs_bill,
       hz_cust_acct_sites_all hca_bill,
       hz_party_sites hps_bill,
       hz_parties hp_bill,
       hz_locations hl_bill,
       mtl_parameters mp
 WHERE     1 = 1
       AND header_id = :p_header_id
       AND ooh.ship_to_org_id = hcs_ship.site_use_id
       AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
       AND hca_ship.party_site_id = hps_ship.party_site_id
       AND hps_ship.party_id = hp_ship.party_id
       AND hps_ship.location_id = hl_ship.location_id
       AND ooh.invoice_to_org_id = hcs_bill.site_use_id
       AND hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id
       AND hca_bill.party_site_id = hps_bill.party_site_id
       AND hps_bill.party_id = hp_bill.party_id
       AND hps_bill.location_id = hl_bill.location_id
       AND mp.organization_id(+) = ooh.ship_from_org_id

RA Customer Query
SELECT hp.party_name,
       hp.party_number,
       hca.account_number,
       hca.cust_account_id,
       hp.party_id,
       hps.party_site_id,
       hps.location_id,
       hl.address1,
       hl.address2,
       hl.address3,
       hl.city,
       hl.state,
       hl.country,
       hl.postal_code,
       hcsu.site_use_code,
       hcsu.site_use_id,
       hcsa.bill_to_flag
  FROM hz_parties hp,
       hz_party_sites hps,
       hz_locations hl,
       hz_cust_accounts_all hca,
       hz_cust_acct_sites_all hcsa,
       hz_cust_site_uses_all hcsu
 WHERE     hp.party_id = hps.party_id
       AND hps.location_id = hl.location_id
       AND hp.party_id = hca.party_id
       AND hcsa.party_site_id = hps.party_site_id
       AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
       AND hca.cust_account_id = hcsa.cust_account_id
       AND hca.account_number = :customer_number

OM SO Query
SELECT ooha.order_number,
       oola.line_number so_line_number,
       oola.ordered_item,
       oola.ordered_quantity * oola.unit_selling_price so_extended_price,
       rcta.trx_number invoice_number,
       rcta.trx_date,
       rctla.line_number inv_line_number,
       rctla.unit_selling_price inv_unit_selling_price
  FROM oe_order_headers_all ooha,
       oe_order_lines_all oola,
       ra_customer_trx_all rcta,
       ra_customer_trx_lines_all rctla
 WHERE     ooha.header_id = oola.header_id
       AND rcta.customer_trx_id = rctla.customer_trx_id
       AND rctla.interface_line_attribute6 = TO_CHAR (oola.line_id)
       AND rctla.interface_line_attribute1 = TO_CHAR (ooha.order_number)
       AND order_number = :p_order_number

AR Customer Contacts
   SELECT rac.customer_name,
          rac.customer_number,
          ara.address1,
          ara.address2,
          ara.address3,
          ara.address4,
          ara.city,
          ara.county,
          ara.country,
          ara.state,
          ara.postal_code,
          ara.concatenated_address,
          bil.contact_name bill_contact,
          shp.contact_name ship_contact,
          bil.primary_flag,
          shp.payment_term_name,
          phn.phone_number,
          phn.phone_type_meaning,
          DECODE (phn.status, 'A', 'Active', 'Inactive') status,
          phn.country_code
     FROM ra_customers rac,
          ar_addresses_v ara,
          hz_site_uses_v bil,
          hz_site_uses_v shp,
          ar_phones_v phn
    WHERE     rac.customer_id = ara.customer_id(+)
          AND ara.address_id = bil.address_id(+)
          AND ara.address_id = shp.address_id(+)
          AND bil.site_use_code(+) = 'BILL_TO'
          AND shp.site_use_code(+) = 'SHIP_TO'
          AND ara.party_site_id = phn.owner_table_id(+)

--      and rac.customer_name like 'AEGON%';

2 comments:

Anonymous said...

can you give details explanation of o2c cycle flow with all affected tables including interface tables step by step

Raju Ch said...

Check out the below link for O2C process:
http://oracleapps88.blogspot.in/2014/06/oracle-order-to-cash-o2c-life-cycle.html

Post a Comment

Best Blogger TipsGet Flower Effect