Query to get the Defaulting rules defined in Order Managment

/* Formatted on 11/18/2016 4:01:35 PM (QP5 v5.114.809.3010) */
SELECT   d.database_object_display_name,
         d.attribute_display_name,
         d.precedence,
         d.display_name,
         d.system_flag,
         d.enabled_flag,
         k.sequence_no,
         l.meaning,
         DECODE (src_type,
                 'API',
                 src_api_pkg || '.' || src_api_fn,
                 'CONSTANT',
                 src_constant_value,
                 'SYSTEM',
                 src_system_variable_expr,
                 'SEQUENCE',
                 src_sequence_name,
                 'DATABASE',
                 src_database_object_name || '.' || src_attribute_code,
                 'WAD_ATTR',
                 ' ',
                 'WAD_OBJATTR',
                 ' ',
                 src_constant_value)
            source,
         src_type,
         k.attribute_code
  FROM   oe_def_attr_condns_v d, oe_lookups l, oe_def_attr_rules_v k
 WHERE       d.database_object_name = k.database_object_name
         AND d.attr_def_condition_id = k.attr_def_condition_id
         AND d.condition_id = k.condition_id
         AND d.attribute_code = k.attribute_code
         AND k.src_type NOT IN
                  ('RELATED_RECORD', 'SAME_RECORD', 'PROFILE_OPTION')
         AND l.lookup_type = 'DEFAULTING_SOURCE_TYPE'
         AND (k.src_type = l.lookup_code
              OR (k.src_type = k.attribute_code
                  AND l.lookup_code = 'CONSTANT'))
UNION
SELECT   d.database_object_display_name,
         d.attribute_display_name,
         d.precedence,
         d.display_name,
         d.system_flag,
         d.enabled_flag,
         k.sequence_no,
         l.meaning,
         a.object_name || '.' || a.name source,
         src_type,
         k.attribute_code
  FROM   oe_def_attr_condns_v d,
         oe_lookups l,
         oe_def_attr_rules_v k,
         ak_object_attributes_vl a
 WHERE       d.database_object_name = k.database_object_name
         AND d.attr_def_condition_id = k.attr_def_condition_id
         AND d.condition_id = k.condition_id
         AND d.attribute_code = k.attribute_code
         AND a.database_object_name(+) = k.src_database_object_name
         AND a.attribute_code(+) = k.src_attribute_code
         AND l.lookup_type = 'DEFAULTING_SOURCE_TYPE'
         AND k.src_type = l.lookup_code
         AND k.src_type = 'RELATED_RECORD'
UNION
SELECT   d.database_object_display_name,
         d.attribute_display_name,
         d.precedence,
         d.display_name,
         d.system_flag,
         d.enabled_flag,
         k.sequence_no,
         l.meaning,
         a.name source,
         src_type,
         k.attribute_code
  FROM   oe_def_attr_condns_v d,
         oe_lookups l,
         oe_def_attr_rules_v k,
         ak_object_attributes_vl a
 WHERE       d.database_object_name = k.database_object_name
         AND d.attr_def_condition_id = k.attr_def_condition_id
         AND d.condition_id = k.condition_id
         AND d.attribute_code = k.attribute_code
         AND a.database_object_name(+) = k.database_object_name
         AND a.attribute_code(+) = k.src_attribute_code
         AND l.lookup_type = 'DEFAULTING_SOURCE_TYPE'
         AND k.src_type = l.lookup_code
         AND k.src_type = 'SAME_RECORD'
UNION
SELECT   d.database_object_display_name,
         d.attribute_display_name,
         d.precedence,
         d.display_name,
         d.system_flag,
         d.enabled_flag,
         k.sequence_no,
         l.meaning,
         a.user_profile_option_name source,
         src_type,
         k.attribute_code
  FROM   oe_def_attr_condns_v d,
         oe_lookups l,
         oe_def_attr_rules_v k,
         fnd_profile_options_vl a
 WHERE       d.database_object_name = k.database_object_name
         AND d.attr_def_condition_id = k.attr_def_condition_id
         AND d.condition_id = k.condition_id
         AND a.profile_option_name(+) = k.src_profile_option
         AND l.lookup_type = 'DEFAULTING_SOURCE_TYPE'
         AND k.src_type = l.lookup_code
         AND k.src_type = 'PROFILE_OPTION'

Query to get the OM Sales Order summary details:-

/* Formatted on 11/18/2016 4:00:35 PM (QP5 v5.114.809.3010) */
  SELECT   h.transactional_curr_code Currency,
           h.conversion_rate Conversion_rate,
           h.conversion_type_code Conversion_Type_Code,
           h.transactional_curr_code use_currency,
           DECODE (SUBSTR (UPPER (:p_order_by), 1, 1), 'S', sr.name, NULL)
              Sales_Person,
           DECODE (SUBSTR (UPPER (:p_order_by), 1, 1),
                   'O', h.ordered_date,
                   NULL)
              dummy_order_date,
           DECODE (SUBSTR (UPPER (:p_order_by), 1, 1), 'A', agree.name, NULL)
              dummy_agreement,
           DECODE (SUBSTR (UPPER (:p_order_by), 1, 1),
                   'P', h.cust_po_number,
                   NULL)
              dummy_po_num,
           ot.name Order_Type,
           DECODE (SUBSTR (UPPER (:p_order_by), 1, 1),
                   'C', party.party_name,
                   NULL)
              Customer_Name1,
           h.order_number Order_Number,
           h.cust_po_number PO_Number,
           h.ordered_date Order_Date,
           party.party_name Customer_Name3,
           h.header_id Header_id,
           u.user_name created_by,
           h.flow_status_code,
           fl.meaning Order_Status,
           agree.name Agreement,
           sr.name Salesrep,
           NVL (
              SUM(DECODE (
                     l.line_category_code,
                     'RETURN',
                     (  NVL (l.unit_list_price, 0)
                      * (NVL (l.ordered_quantity, 0))
                      * (-1)),
                     (NVL (l.unit_list_price, 0)
                      * (NVL (l.ordered_quantity, 0)))
                  )),
              0
           )
              List_Value,
           NVL (
              SUM(DECODE (
                     l.line_category_code,
                     'RETURN',
                     (  NVL (l.unit_selling_price, 0)
                      * (NVL (l.ordered_quantity, 0))
                      * (-1)),
                     (NVL (l.unit_selling_price, 0)
                      * (NVL (l.ordered_quantity, 0)))
                  )),
              0
           )
              Order_Amount,
           NVL (
              SUM(DECODE (
                     l.line_category_code,
                     'RETURN',
                     (  NVL (l.unit_selling_price, 0)
                      * (NVL (l.shipped_quantity, 0))
                      * (-1)),
                     (NVL (l.unit_selling_price, 0)
                      * (NVL (l.shipped_quantity, 0)))
                  )),
              0
           )
              Ship_Value,
           l.charge_periodicity_code
    FROM   oe_order_headers_all h,
           oe_order_lines_all l,
           oe_transaction_types_tl ot,
           fnd_user u,
           fnd_lookup_values fl,
           oe_agreements_tl agree,
           HZ_CUST_SITE_USES_ALL su,
           hz_party_sites party_site,
           hz_locations loc,
           hz_cust_acct_sites_all acct_site,
           ra_salesreps sr,
           fnd_territories_vl terr,
           hz_parties party,
           hz_cust_accounts cust_acct
   WHERE       h.sold_to_org_id = cust_acct.cust_account_id
           AND cust_acct.party_id = party.party_id
           AND h.header_id = l.header_id(+)
           AND NVL (h.org_id, 0) = NVL (:p_org_id, 0)
           AND h.order_type_id = ot.transaction_type_id
           AND ot.language = USERENV ('LANG')
           AND sr.salesrep_id(+) = h.salesrep_id
           AND NVL (sr.org_id(+), NVL (:p_org_id, 0)) = NVL (:p_org_id, 0)
           AND h.agreement_id = agree.agreement_id(+)
           AND agree.language(+) = USERENV ('LANG')
           AND h.created_by = u.user_id
           AND h.ship_to_org_id = su.site_use_id(+)
           AND acct_site.party_site_id = party_site.party_site_id(+)
           AND loc.location_id(+) = party_site.location_id
           AND su.CUST_ACCT_SITE_ID = acct_site.cust_acct_site_id(+)
           AND loc.country = terr.territory_code(+)
           AND fl.lookup_type = 'FLOW_STATUS'
           AND fl.lookup_code = h.flow_status_code
           AND fl.language = USERENV ('LANG')
GROUP BY   h.transactional_curr_code,
           h.conversion_rate,
           h.conversion_type_code,
           DECODE (:p_use_functional_currency,
                   'N', h.transactional_curr_code,
                   :rp_functional_currency),
           sr.name,
           cust_acct.cust_account_id,
           h.order_number,
           h.cust_po_number,
           h.ordered_date,
           party.party_name,
           ot.name,
           h.header_id,
           h.flow_status_code,
           fl.meaning,
           agree.name,
           u.user_name,
           l.charge_periodicity_code
ORDER BY   1,
           DECODE (SUBSTR (UPPER (:p_order_by), 1, 1),
                   'S', sr.name,
                   'O', h.ordered_date,
                   h.order_number),
           DECODE (SUBSTR (UPPER (:p_order_by), 1, 1),
                   'S',
                   party.party_name,
                   'O',
                   party.party_name),
           h.order_number

Retroactive billing report in oracle order management:-

/* Formatted on 11/18/2016 3:59:06 PM (QP5 v5.114.809.3010) */
SELECT   DECODE (:p_sort_by, 'O', orig_head.order_number)
            sort_by_order_number,
         DECODE (
            :p_sort_by,
            'T',
            oe_retrobill_pvt.invoice_number (orig_head.order_number,
                                             orig_lin.line_id,
                                             orig_head.order_type_id),
            NULL
         )
            sort_by_invoice_no,
         DECODE (
            SUBSTR (UPPER (:p_item_display), 1, 1),
            'P',
            NVL (orig_lin.user_item_description, item_info.item_description),
            'D',
            NVL (orig_lin.user_item_description, si_tl.description),
            'O',
            item_info.item,
            'F',
            item_info.inventory_item,
            'C',
            item_info.item || ' - '
            || NVL (orig_lin.user_item_description,
                    item_info.item_description),
            'I',
               item_info.inventory_item
            || ' - '
            || NVL (orig_lin.user_item_description, si_tl.description),
            NVL (orig_lin.user_item_description, item_info.item_description)
         )
            item_name,
         oe_order_misc_pub.get_concat_line_number (orig_lin.line_id) line_no,
         party.party_name customer_name,
         site.location invoice_to_name,
         orig_head.order_number,
         orig_head.ordered_date ordered_date,
         orig_head.transactional_curr_code currency,
         orig_lin.ordered_quantity Ordered,
         orig_lin.shipped_quantity Shipped,
         orig_lin.invoiced_quantity Invoiced,
         TO_CHAR (orig_lin.line_id) line_id,
         TO_CHAR (orig_lin.header_id) header_id,
         orig_lin.unit_selling_price orig_price,
         orig_head.order_type_id,
         orig_head.org_id,
         orig_lin.inventory_item_id
  FROM   oe_order_lines_all orig_lin,
         oe_order_lines_all retro_lin,
         hz_cust_accounts cust_info,
         hz_parties party,
         oe_items_v item_info,
         mtl_system_items_tl si_tl,
         oe_order_headers_all orig_head,
         hz_cust_site_uses_all site,
         hz_cust_acct_sites_all acct_site,
         oe_order_headers_all retro_head
 WHERE       orig_head.header_id = orig_lin.header_id
         AND NVL (orig_head.org_id, 0) = NVL (:p_organization_id, 0)
         AND retro_lin.order_source_id = 27
         AND retro_lin.orig_sys_line_ref = orig_lin.line_id
         AND retro_head.header_id = retro_lin.header_id
         AND retro_head.order_source_id = 27
         AND retro_head.orig_sys_document_ref = TO_CHAR (:p_request_id)
         AND retro_lin.retrobill_request_id = :p_request_id
         AND orig_lin.invoiced_quantity > 0
         AND cust_info.cust_account_id = orig_head.sold_to_org_id
         AND cust_info.party_id = party.party_id
         AND orig_lin.inventory_item_id = item_info.inventory_item_id
         AND NVL (orig_lin.ordered_item, '-99') =
               DECODE (orig_lin.item_identifier_type,
                       'INT', NVL (orig_lin.ordered_item, '-99'),
                       'CUST', NVL (orig_lin.ordered_item, '-99'),
                       item_info.item)
         AND NVL (orig_lin.sold_to_org_id, -99) =
               NVL (item_info.sold_to_org_id,
                    NVL (orig_lin.sold_to_org_id, -99))
         AND NVL (orig_lin.item_identifier_type, 'INT') =
               item_info.item_identifier_type
         AND NVL (orig_lin.ordered_item_id, -99) =
               NVL (item_info.item_id, -99)
         AND si_tl.inventory_item_id = item_info.inventory_item_id
         AND si_tl.organization_id = item_info.organization_id
         AND si_tl.language = USERENV ('LANG')
         AND site.site_use_code = 'BILL_TO'
         AND site.site_use_id = orig_lin.invoice_to_org_id
         AND acct_site.cust_account_id = cust_info.cust_account_id
         AND site.cust_acct_site_id = acct_site.cust_acct_site_id;

Query to get the outstanding holds on an order (Open holds)

/* Formatted on 11/18/2016 3:56:56 PM (QP5 v5.114.809.3010) */
  SELECT   org.name customer_name,
           ho.name hold_name,
           hs.hold_until_date,
           hs.hold_comment,
           h.order_number,
           h.ordered_date order_date,
           h.transactional_curr_code currency_code,
           NVL (l.ordered_quantity, 0) * NVL (l.unit_selling_price, 0) amount,
           oh.header_id,
           oh.line_id,
           oh.order_hold_id,
           l.item_identifier_type,
           l.inventory_item_id,
           l.ordered_item_id,
           l.ordered_item
    FROM   oe_sold_to_orgs_v org,
           mtl_system_items_vl si,
           oe_order_holds_all oh,
           oe_order_lines_all l,
           oe_order_headers h,
           oe_hold_definitions ho,
           oe_hold_sources_all hs
   WHERE       oh.header_id = h.header_id
           AND h.sold_to_org_id = org.organization_id
           AND (h.cancelled_flag IS NULL OR h.cancelled_flag = 'N')
           AND h.open_flag = 'Y'
           AND oh.hold_source_id = hs.hold_source_id
           AND hs.hold_id = ho.hold_id
           AND h.header_id = l.header_id(+)
           AND l.open_flag = 'Y'
           AND l.line_id = NVL (oh.line_id, l.line_id)
           AND l.inventory_item_id = si.inventory_item_id
           AND oh.hold_release_id IS NULL
           AND NVL (si.organization_id, 0) = :c_master_org
           AND NVL (h.org_id, 0) = NVL (:p_org_id, 0)
           AND NVL (l.org_id, 0) = NVL (:p_org_id, 0)
           AND h.order_number = <>
ORDER BY   org.name,
           ho.name,
           h.order_number,
           si.description

Query to get the discrepancy between the sales orders and purchase orders in Drop ship orders

/* Formatted on 11/18/2016 3:55:24 PM (QP5 v5.114.809.3010) */
  SELECT   osto.name oe_customer_name,
           osto.customer_number oe_customer_number,
           oeh.header_id oe_header_id,
           oeh.order_number oe_number,
           OTYPE.name oe_type,
           oeh.ordered_date oe_order_date,
           oel.line_id oe_line_id,
           oel.line_number
           || DECODE (oel.shipment_number,
                      NULL, NULL,
                      '.' || oel.shipment_number)
           || DECODE (oel.option_number, NULL, NULL, '.' || oel.option_number)
              line_shipment_option_number,
           oel.item_identifier_type,
           OEL.INVENTORY_ITEM_ID,
           OEL.ORDERED_ITEM_ID,
           OEL.ORDERED_ITEM,
           poh.segment1 po_number,
           pol.line_num po_line_number,
           porel.release_num po_release_number,
           poll.shipment_num po_shipment_number,
           porh.segment1 requisition_number,
           porl.line_num requisition_line_number,
           msi1.description po_item_description,
           msi2.description rq_item_description,
           DECODE (
              oel.line_set_id,
              NULL,
              (SELECT   ordered_quantity
                 FROM   oe_order_lines_all
                WHERE       line_id = oel.line_id
                        AND line_set_id IS NULL
                        AND NVL (org_id, 0) = NVL (oel.org_id, 0)),
              (SELECT   ordered_quantity
                 FROM   oe_order_lines_all
                WHERE       line_set_id = oel.line_set_id
                        AND line_id = oel.line_id
                        AND line_set_id IS NOT NULL
                        AND NVL (org_id, 0) = NVL (oel.org_id, 0))
           )
              oe_quantity,
           poll.quantity - NVL (poll.quantity_cancelled, 0)
              po_shipment_quantity,
           porl.quantity - NVL (porl.quantity_cancelled, 0) rq_quantity,
           oel.order_quantity_uom oe_unit,
           muom1.uom_code po_unit,
           muom2.uom_code rq_unit,
           oel.schedule_ship_date oe_schedule_date,
           poll.need_by_date po_needby_date,
           porl.need_by_date rq_needby_date,
           zloc_oe.address1 oe_ship_to_location,
           NVL (rloc_po.location_CODE, zloc_po.address1) po_ship_to_location,
           NVL (rloc_rq.location_code, zloc_rq.address1) rq_ship_to_location,
           DECODE (poh.user_hold_flag, 'Y', 'Y', pol.user_hold_flag) po_hold
    FROM   mtl_system_items_vl msi,
           mtl_system_items_vl msi1,
           mtl_system_items_vl msi2,
           mtl_units_of_measure muom1,
           mtl_units_of_measure muom2,
           po_line_locations_all poll,
           po_releases porel,
           po_lines_all pol,
           po_headers_all poh,
           po_requisition_lines_all porl,
           po_requisition_headers_all porh,
           oe_order_lines_all oel,
           OE_TRANSACTION_TYPES_TL OTYPE,
           oe_order_headers oeh,
           oe_drop_ship_sources oes,
           oe_sold_to_orgs_v osto,
           HR_LOCATIONS rloc_po,
           HR_LOCATIONS rloc_rq,
           HZ_LOCATIONS zloc_oe,
           HZ_LOCATIONS zloc_po,
           HZ_LOCATIONS zloc_rq,
           HZ_PARTY_SITES party,
           HZ_CUST_ACCT_SITES_ALL acct,
           HZ_CUST_SITE_USES_ALL cust
   WHERE   OEH.HEADER_ID = OES.HEADER_ID
           AND NVL (oeh.org_id, 0) = NVL (:p_organization_id, 0)
           AND NVL (oel.org_id, NVL (:p_organization_id, 0)) =
                 NVL (:p_organization_id, 0)
           AND oeh.sold_to_org_id = osto.customer_id(+)
           AND OTYPE.TRANSACTION_TYPE_ID = OEH.ORDER_TYPE_ID
           AND OTYPE.language = USERENV ('lang')
           AND oes.line_id = oel.line_id(+)
           AND MSI.INVENTORY_ITEM_ID = PORL.ITEM_ID
           AND NVL (msi.organization_id, 0) = :c_master_org
           AND poh.po_header_id(+) = oes.po_header_id
           AND pol.po_line_id(+) = oes.po_line_id
           AND msi1.inventory_item_id(+) = pol.item_id
           AND NVL (msi1.organization_id, NVL (:c_master_org, 0)) =
                 :c_master_org
           AND msi2.inventory_item_id(+) = porl.item_id
           AND NVL (msi2.organization_id, 0) = :c_master_org
           AND poll.line_location_id(+) = oes.line_location_id
           AND porh.requisition_header_id(+) = oes.requisition_header_id
           AND porl.requisition_line_id(+) = oes.requisition_line_id
           AND porel.po_release_id(+) = poll.po_release_id
           AND muom1.unit_of_measure(+) = pol.unit_meas_lookup_code
           AND muom2.unit_of_measure(+) = porl.unit_meas_lookup_code
           AND rloc_po.location_id(+) = poll.ship_to_location_id
           AND rloc_rq.location_id(+) = porl.deliver_to_location_id
           AND zloc_po.location_id(+) = poll.ship_to_location_id
           AND zloc_rq.location_id(+) = porl.deliver_to_location_
           AND CUST.SITE_USE_ID(+) = oel.ship_to_org_id
           AND NVL (CUST.STATUS, 'A') = 'A'
           AND NVL (CUST.SITE_USE_CODE, 'SHIP_TO') = 'SHIP_TO'
           AND ACCT.CUST_ACCT_SITE_ID(+) = CUST.CUST_ACCT_SITE_ID
           AND PARTY.PARTY_SITE_ID(+) = ACCT.PARTY_SITE_ID
           AND zloc_oe.location_id(+) = PARTY.location_id
           AND (oes.po_header_id IS NOT NULL
                AND (pol.item_id != oel.inventory_item_id
                     OR TRUNC (poll.need_by_date) !=
                          TRUNC (oel.schedule_ship_date)
                     OR poll.ship_to_location_id != PARTY.LOCATION_ID
                     OR muom1.uom_code != oel.order_quantity_uom
                     OR DECODE (
                          0,
                          0,
                          (SELECT   SUM (ordered_quantity)
                             FROM   oe_order_lines_all
                            WHERE       line_set_id = oel.line_set_id
                                    AND line_set_id IS NOT NULL
                                    AND NVL (org_id, 0) = NVL (oel.org_id, 0))
                       ) NOT IN
                             (SELECT   SUM (quantity)
                                FROM   po_line_locations_all poll2,
                                       oe_drop_ship_sources oes2
                               WHERE   poll2.line_location_id =
                                          oes2.line_location_id
                                       AND oes2.line_id = oel.line_id)
                     OR DECODE (
                          0,
                          0,
                          (SELECT   ordered_quantity
                             FROM   oe_order_lines_all
                            WHERE       line_id = oel.line_id
                                    AND line_set_id IS NULL
                                    AND NVL (org_id, 0) = NVL (oel.org_id, 0))
                       ) NOT IN
                             (SELECT   SUM (quantity)
                                FROM   po_line_locations_all poll2,
                                       oe_drop_ship_sources oes2
                               WHERE   poll2.line_location_id =
                                          oes2.line_location_id
                                       AND oes2.line_id = oel.line_id)
                     OR DECODE (poh.user_hold_flag,
                                'Y', 1,
                                DECODE (pol.user_hold_flag, 'Y', 1, 0)) NOT IN
                             (SELECT   DECODE (COUNT (order_hold_id), 0, 0, 1)
                                FROM   oe_order_holds_all orh,
                                       oe_hold_sources_all ohs,
                                       oe_hold_definitions ohd
                               WHERE   orh.header_id = oel.header_id
                                       AND orh.hold_release_id IS NULL
                                       AND orh.hold_source_id =
                                             ohs.hold_source_id
                                       AND ohs.hold_id = ohd.hold_id
                                       AND ohd.item_type IS NULL
                                       AND ohd.activity_name IS NULL
                                       AND (orh.line_id = oel.line_id
                                            OR orh.line_id IS NULL)))
                OR (oes.po_header_id IS NULL
                    AND oes.requisition_header_id IS NOT NULL
                    AND (DECODE (oel.inventory_item_id,
                                 NULL, -98,
                                 porl.item_id) !=
                            NVL (oel.inventory_item_id, -99)
                         OR TRUNC(DECODE (oel.schedule_ship_date,
                                          NULL, SYSDATE,
                                          porl.need_by_date)) !=
                              TRUNC (NVL (oel.schedule_ship_date, SYSDATE - 1))
                         OR NVL (oel.ship_to_org_id, -98) !=
                              NVL (oel.ship_to_org_id, -99)
                         OR porl.deliver_to_location_id != PARTY.LOCATION_ID
                         OR muom2.uom_code != oel.order_quantity_uom
                         OR porl.quantity !=
                              DECODE (
                                 0,
                                 0,
                                 (SELECT   SUM (ordered_quantity)
                                    FROM   oe_order_lines_all
                                   WHERE   line_set_id = oel.line_set_id
                                           AND line_set_id IS NOT NULL
                                           AND NVL (org_id, 0) =
                                                 NVL (oel.org_id, 0))
                              )
                         OR porl.quantity !=
                              DECODE (
                                 0,
                                 0,
                                 (SELECT   ordered_quantity
                                    FROM   oe_order_lines_all
                                   WHERE   line_id = oel.line_id
                                           AND line_set_id IS NULL
                                           AND NVL (org_id, 0) =
                                                 NVL (oel.org_id, 0))
                              )
                         OR 0 NOT IN
                                 (SELECT   DECODE (COUNT (order_hold_id),
                                                   0, 0,
                                                   1)
                                    FROM   oe_order_holds_all orh,
                                           oe_hold_sources_all ohs,
                                           oe_hold_definitions ohd
                                   WHERE   orh.header_id = oel.header_id
                                           AND orh.hold_source_id =
                                                 ohs.hold_source_id
                                           AND ohs.hold_id = ohd.hold_id
                                           AND ohd.item_type IS NULL
                                           AND ohd.activity_name IS NULL
                                           AND orh.hold_release_id IS NULL
                                           AND (orh.line_id = oel.line_id
                                                OR orh.line_id IS NULL))))
                OR oel.line_id IS NULL)
ORDER BY   oeh.order_number

Query to get the details of discounts on the orders in Order Management

/* Formatted on 11/18/2016 3:53:44 PM (QP5 v5.114.809.3010) */
  SELECT   h.transactional_curr_code Currency2,
           ot.name Order_Type,
           DECODE (SUBSTR (UPPER (:P_ORDER_BY), 1, 1),
                   'C', CUST_ACCT.CUST_ACCOUNT_ID,
                   NULL)
              CUSTOMER_ID,
           DECODE (SUBSTR (UPPER (:P_ORDER_BY), 1, 1),
                   'C', PARTY.PARTY_NAME,
                   NULL)
              CUSTOMER_NAME_2,
           h.order_number Order_Number,
           PARTY.PARTY_NAME CUSTOMER_NAME_1,
           CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER,
           h.ordered_date Order_Date,
           ag.name Agreement,
           sr.name Sales_Person,
           SUM(NVL (l.ordered_quantity, 0)
               * DECODE (L.LINE_CATEGORY_CODE,
                         'RETURN', -1 * NVL (l.unit_list_price, 0),
                         NVL (l.unit_list_price, 0)))
              Order_List,
           SUM(NVL (l.ordered_quantity, 0)
               * DECODE (L.LINE_CATEGORY_CODE,
                         'RETURN', -1 * NVL (l.unit_selling_price, 0),
                         NVL (l.unit_selling_price, 0)))
              Order_Amount,
           curr.precision c_pre,
           l.charge_periodicity_code
    FROM   oe_order_headers_all h,
           oe_order_lines_all l,
           OE_TRANSACTION_TYPES_TL OT,
           ra_salesreps sr,
           HZ_CUST_ACCOUNTS CUST_ACCT,
           HZ_PARTIES PARTY,
           oe_agreements ag,
           fnd_currencies curr
   WHERE       OT.TRANSACTION_TYPE_ID = h.order_type_id
           AND h.sold_to_org_id = CUST_ACCT.CUST_ACCOUNT_ID
           AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
           AND h.salesrep_id = sr.salesrep_id(+)
           AND h.agreement_id = ag.agreement_id(+)
           AND l.header_id = h.header_id
           AND l.service_reference_line_id IS NULL
           AND NVL (h.cancelled_flag, 'N') = 'N'
           AND NVL (h.org_id, 0) = NVL (:p_organization_id, 0)
           AND NVL (l.org_id, 0) = NVL (:p_organization_id, 0)
           AND NVL (sr.org_id, 0) = NVL (:p_organization_id, 0) -- BUG#2202575
           AND ot.LANGUAGE = SYS_CONTEXT ('USERENV', 'LANG') -- change for bug3526405
           AND h.transactional_curr_code = curr.currency_code
GROUP BY   h.transactional_curr_code,
           ot.name,
           DECODE (SUBSTR (UPPER (:P_ORDER_BY), 1, 1),
                   'C', CUST_ACCT.CUST_ACCOUNT_ID,
                   NULL),
           DECODE (SUBSTR (UPPER (:P_ORDER_BY), 1, 1),
                   'C', PARTY.PARTY_NAME,
                   NULL),
           h.order_number,
           PARTY.PARTY_NAME,
           CUST_ACCT.ACCOUNT_NUMBER,
           h.ordered_date,
           ag.name,
           sr.name,
           curr.precision,
           l.charge_periodicity_code
ORDER BY   h.transactional_curr_code, ot.name

Order to Cash Cycle

21. Sales Order Booking, Releasing, Ship Confirm

Since we have the Inventory on Hand now, we can execute the Sales Order.

Log into Order Management Super User Responsibility and check for the availability of the Inventory and book the Sales order.

Navigation Path.

Order management Super user Responsibility

Order, Returns     Sales Orders

Open the Sales Order form and Query for the Sales order number 51823, which was created earlier.

Click on availability button to check the stock for the Item.



Close the window and book the Sales order now.



Release the Sales order for Shipment of the Item.

 

Navigation Path

Order Management Super User Responsibility

Shipping     release Sales orders     Release Sales orders

Click on the form and choose the Based on Rule field as ‘Standard’ and enter the Order Number 51823. Click on Shipping tab and check Auto-create Delivery is set to ‘Yes’ and auto-pack delivery also to ‘Yes’. Click on Inventory Tab.

In the Inventory Tab of Release Sales Order for Picking Form, check the Warehouse is showing as Mi- Seattle Manufacturing and Auto-allocate and auto-pick confirm to ‘Yes’. Choose the sub inventory as ‘FG Stores’ in Default Stage area and save the Transaction.

This will generate a concurrent Request.

View the concurrent Request 1772895.It is completed Normal.

Next, we have to Ship the Sales Order.

Navigation Path

Shipping     Transactions

Click on the Form and enter the Sales Order Number 51823 and click on

Find Button

Shipping Transactions Window Opens and the Line Status shows as Stage

/Pick Confirmed for the Sales Order 51823.

Click on Delivery Tab and choose Ship Confirm from Actions LOV and click Go Button.

This Opens the Confirm Delivery Screen. Click on OK Button. This will generate a concurrent request. Click OK and view the Concurrent Request

1772932.

View the Concurrent Request 1772932. The Request completed Normal. We can view the Output of the commercial Invoice created in concurrent

Request ID 1772931

The Output of Commercial Invoice created at Ship Confirm Stage.



Next we can go and Check the Status of the Sales Order.

Navigation Path.

Order Management Super User

Order, Returns     Sales Order

Query for the Sales Order Number and click on Line Items to view the

Status.

Next, we need to run the Workflow Back ground Process to populate the AR Interface Table.

Navigation Path

Order Management Super User Responsibility. Report and Requests     Run Requests

Choose the Concurrent Request Workflow Back ground Process

And Enter the Parameters as follows. Process Deferred set to Yes

Process Timed Out No.

Click on OK and Click Submit Button.This will generate a Concurrent

Request.

The Concurrent Request ID 1772692 is created.

View the Concurrent request ID 1772692.

22. Run Auto invoice Process

Next, we need to Log on to Accounts Receivables Module to view the Data

Populated in AR Inter face table.

Navigation Path.

Receivables, Vision Operations

Control     Autoinvoice     Interface Lines

Click on the Interface Lines and Query for the Sales Order Number 51823 in the SALES_ORDER Field. Click on the Errors Button to find any errors is there.

Clicking on Errors screen opens Line Errors Form and nothing is displayed.

Run the Autoinvoice Program now.

Navigation Path.

Receivables, Vision Operations

Controls     Requests     Run

Choose Autoinvoice master program and Pass the Following Parameters.

Invoice Source     Choose from LOV as ORDER ENTRY Default Date     Today’s Date 10 Nov 2003

Sales Order number    Low and High 51823

Based Due Date on Trx Date Set to Yes.

Click Ok Button and Submit Button. This will generate a concurrent request

ID.

Concurrent Request ID 1774355 is submitted. View the Concurrent Request to see it is completed Normal.

The Concurrent Request ID 1774355 is completed Normal. We can view the Concurrent request ID 1774357 to view the Autoinvoice validation Report to check is the transaction Imported successfully or not.

The Auto-invoice Validation Report completed successfully.

23. View Sales Invoice, Create Receipt, and Remittance and view

Account Balances

We can view the Sales Invoice created in Accounts Receivables Transaction

Form.

Navigation Path.

Receivables, Vision Operations

Transactions     Transactions.

Query for the Sales Order number 51823 by inputting the Value of the Sales order number in the reference field. Sales Invoice 10008669 is created.

Click on Line Items to view the Details of the Lines.

Line Item shows two Quantities of Dell computer finished goods have been sold for USD 6000 each.

Enter a Receipt number. The Currency field is defaulted is defaulted from Functional currency and the Receipt Date and GL Date are defaulted from System Date and choose the Payment method of Manual Remittance.

Enter in Transaction Number field 10008669, which is the Sales Invoice number against which cash is received and Click on the Applications Button.

The Applications For opens up and check whether the Apply to field is showing the invoice number 10008669 and amount applied field shows as

6000 USD. Save the Record and close the window.

We can go to Collections Form and view the amount due from the customer.

Navigation Path.

Receivables, Vision Operations

Collections     Account Details

Click on the Account Details form and enter the Customer number 2239 in the number field. Click on the Find Button

Account details Window opens for the IBM Corporation and shows the

Balance due as 6000 USD.

Invoice Balance – 12000 (Invoice Number 10008669) Receipt                - 6000 (Receipt Number 1645) Balance Due form the Customer is 6000 USD.

Next, we have to remit the check we have received from IBM Corporation

Navigation Path

Receivables, Vision Operations

Receipts     Remittance

Click on the Remittances Form and enter the following details

Remittances Form opens and Currency, Batch Date, GL Date and Remittance method defaults. Enter the Payment method Manual Remittance and choose the Bank Account Number from the List of Values. Click on Auto Create Button

Create Remittance Batch Window Opens and enter the Receipt Number

1645 and the Customer Number 2239 and enable the Create, Approve and

Format check box. Click on OK Button and a message pops up Submit 1

Batches for Format. Click on Yes Button.

This Generates a Concurrent request and generates the Automatic

Remittances Execution Report.

Now, You go to Receipts screen and Query for the Receipt Number 1645, the status of the Receipt might have changed from Confirmed to remitted.

Navigation Path

Receivables, Vision Operations

Receipts     Receipts

Query for Receipt number 1645

24. Receipt Reconciliation

Next, we need to clear the Receipt in Cash Management.

Navigation Path

Cash Management, Vision Operations

Bank Reconciliation     Bank Statements

Click on the Form and Click on New Button. Enter the Account Number and

Date. Enter Receipts as 6000 in Control Totals. Click on the Lines Button.

Click on New Button. Bank Statement Line Window Opens up. Enter the

Line Number 1 and choose the Type as Receipt and Enter the amount as

6000 and save the record. Close the Window.

Next Step is to choose the Receipt advice and reconcile with the Bank

Statement we created.

Navigation Path

Cash Management, Vision Operations

Bank Reconciliation     Manual Clearing     Clear Transactions

Click on the Clear Transactions form and enter the following.

Find Transactions Window opens up and the radio button detail is enabled and uncheck the Payment and Miscellaneous check box. Click on the Find Button.

Clear transactions Form opens up and enable the check box on left-hand side for the receipt number 1645 and click on clear Transaction. This will clear the Transaction and reconcile the Transactions with the bank Statement.

Now, Log on to receivables Responsibility and check the status of the

Receipt 1645. This will have the Status of cleared.

 

Navigation Path

Receivables, Vision Operations

Receipts     Receipts

Query for Receipt Number 1645

The above full cycle shows the complete steps involved in Make to Stock

Cycle.