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%';