Script to Cancel Order Line and Workflow in Oracle Apps 11i

This script will cancel order lines in Oracle 11i OM. It will also cancel or abort the corresponding OEOL (order line) workflow. Take caution to run the select statement of the cursor standalone prior to running the script to verify the record set to be cancelled.

DECLARE
   v_itemkey   VARCHAR2 (240); –This is the line_id
   v_ctr       NUMBER := 0;

   CURSOR wf
   IS
      SELECT wi.item_type,
             wi.ITEM_KEY,
             wi.BEGIN_DATE,
             wi.end_date,
             ooh.order_number,
             ooh.OPEN_FLAG hdr_open_flag,
             ooh.CANCELLED_FLAG hdr_can_flag,
             ooh.header_id,
             ool.line_id,
             ooh.org_id,
             ool.FLOW_STATUS_CODE,
             ool.CANCELLED_FLAG,
             ool.OPEN_FLAG,
             ool.line_number || ‘.’ || ool.shipment_number line_no
        FROM oe_order_headers_all ooh,
             oe_order_lines_all ool,
             wf_item_attribute_values wiav,
             wf_items wi
       WHERE wi.item_type = ‘OEOL’
             AND wi.end_date IS NULL
             AND wiav.item_type = wi.ITEM_type
             AND wiav.ITEM_KEY = wi.ITEM_KEY
             AND wiav.name = ‘ORG_ID’
             AND wiav.number_value = ooh.org_id
             AND ool.LINE_ID = wi.ITEM_KEY
             AND ooh.HEADER_ID = ool.HEADER_ID
             AND wi.item_key IN
             –Modify this section to get the order lines desired
               (–No updates in 1 yr
               SELECT ol.line_id
                 FROM oe_order_headers_all oh,
                      oe_order_lines_all ol,
                      mtl_parameters p,
                      hz_parties hp,
                      hz_cust_accounts hca
                WHERE oh.header_id = ol.header_id
                AND ol.flow_status_code NOT IN
                     (‘ENTERED’,‘CANCELLED’, ‘CLOSED’)
                AND NVL (ol.shipped_quantity, 0) = 0
                AND p.organization_id = ol.ship_from_org_id
                AND hca.cust_account_id(+) = oh.sold_to_org_id
                AND hp.party_id(+) = hca.party_id
                AND f.line_detail_id(+) = ol.line_id
                AND TRUNC(ol.creation_date) <= TRUNC(SYSDATE-365)
                AND TRUNC(ol.last_update_date) <= TRUNC(SYSDATE-365)
                );


BEGIN
   DBMS_OUTPUT.put_line (‘Order No~Line No~WF Key~Status’);

   FOR wf_rec IN wf
   LOOP
      –Cancels the work flow of the line
      wf_engine.ABORTPROCESS (wf_rec.item_type, wf_rec.item_key);
      COMMIT;

      –Cancels the order line
      UPDATE oe_order_lines_all
         SET fulfilled_quantity = NULL,
             fulfilled_flag = NULL,
             fulfillment_date = NULL,
             cancelled_quantity = ordered_quantity,
             ordered_quantity = 0,
             pricing_quantity = 0,
             schedule_ship_date = NULL,
             visible_demand_flag = NULL,
             schedule_arrival_date = NULL,
             schedule_status_code = NULL,
             cancelled_flag = ‘Y’,
             open_flag = ‘N’,
             flow_status_code = ‘CANCELLED’,
             calculate_price_flag = ‘N’
       WHERE line_id = wf_rec.line_id;

      COMMIT;
      v_ctr := v_ctr + 1;
      DBMS_OUTPUT.put_line(   wf_rec.order_number
                           || ‘~’
                           || wf_rec.line_no
                           || ‘~’
                           || wf_rec.item_key
                           || ‘~CANCELLED’);
   END LOOP;

   DBMS_OUTPUT.put_line (‘No of lines ‘ || v_ctr);
END;
/

Query to Get Customers in Oracle 11i Applications

 SELECT hp.party_name        customer_name,
         hca.cust_account_id  customer_id,
         hp.status            status
    FROM hz_parties hp, 
         hz_cust_accounts hca
   WHERE hp.party_id = hca.party_id 
   –AND hca.cust_account_id = 9999 –customer_id
ORDER BY hp.party_name, hca.cust_account_id

Query to Get Customer Sites in Oracle 11i Applications with Tax and Payment Method

 SELECT hou.name operating_unit,
         hp.party_name customer_name,
         hca.account_number customer_number,
         hca.cust_account_id customer_id,
         hps.party_site_number site_number,
         hcsua.site_use_code,
         hcsua.location,
         hl.address1,
         hl.address2,
         hl.city,
         hl.state,
         hcsua.tax_reference,
         hcsua.tax_code,
         rm.name pay_method,
         rm.start_date pay_method_start,
         rm.end_date pay_method_end,
         crm.start_date cust_pay_method_start,
         crm.end_date cust_pay_method_end
    FROM hz_locations hl,
         hz_party_sites hps,
         hz_cust_site_uses_all hcsua,
         hr_operating_units hou,
         hz_cust_acct_sites_all hcasa,
         hz_cust_accounts hca,
         hz_parties hp,
         ra_cust_receipt_methods crm,
         ar_receipt_methods rm
   WHERE hl.location_id = hps.location_id
     AND hps.party_site_id = hcasa.party_site_id
     AND hps.party_id = hp.party_id
     AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
     AND hou.organization_id = hcasa.org_id
     AND hcasa.cust_account_id = hca.cust_account_id
     AND hca.party_id = hp.party_id
     AND hp.status = ‘A’
     AND hca.status = ‘A’
     AND hcasa.status = ‘A’
     AND hcsua.status = ‘A’
     AND hps.status = ‘A’
     AND rm.receipt_method_id = crm.receipt_method_id
     AND crm.customer_id = hca.cust_account_id
     AND crm.site_use_id = hcsua.site_use_id
     AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (rm.start_date, SYSDATE))
                                 AND  TRUNC (NVL (rm.end_date, SYSDATE))
     AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (crm.start_date, SYSDATE))
                                 AND  TRUNC (NVL (crm.end_date, SYSDATE))                              
ORDER BY 1,2,6,7,14

Label Printing in MSCA/WMS

Labels

Either we can use the existing labels or we can design new labels

Label Types:

There are different label types depending on the different business and requirement with data elements.

Label Format:

A single design of a label that may include fixed data (such as field prompts) and variable data (such as Part Number). A Label Format is associated to a Label Type. It includes a subset of the available data elements from a particular Label Type. A label type can have multiple label formats, however each Label Format belongs to only one label type. Label Formats are registered within Oracle Warehouse Management after you create the label design in the third party software package.

Either you can use the seeded label format or you can design your own label format

We can customize the label as per our requirement with required and customized fields

Printing Modes

Asynchronous:

An integration mode between Oracle Warehouse Management and the third party software supplier that is handled through dropping an XML file into a watched directory. When using this mode, Oracle Warehouse Management has no knowledge of the status of the print request after creation of the XML file.

Synchronous:

Generic:

A real-time integration mode between Oracle Warehouse Management and the third party software vendor that is handled through a PL/SQL API. When using this mode, the third party supplier is able to return success or failure messages regarding the status of the label print request. Oracle Warehouse Management stores those messages in the label print history so label printing problems can be easily identified and diagnosed.

TCP/IP:

A real-time integration mode between Oracle Warehouse Management and the 3rd Party software vendor that is handled through a standard Ethernet TCP/IP connection. When using this mode, Oracle ensures that the printer or print server is listening to the IP and port and has successfully received the message, but no knowledge of the detailed status of the print request beyond acceptance of the XML message is known by Oracle Warehouse Management.

Example Printer vendors like Zebra, Intermec, Cognitive Solutions, Datamax, Toshiba TEC, Printronix and Sato

Printer

·        Define the printer details in Oracle system (Sysadmin) with proper styles and drivers and with unique name

·         Assign IP and Port to printers

Assigning Label Types to Business Flow

Business Flow: An identifiable business process that occurs as part of material handling within Oracle Warehouse Management. Oracle Warehouse Management recognizes a pre-determined list of Business Flows you can set up to include label printing as part of the transaction processing.

Defining Label Format Rules

Creating label format rules is an optional step. Some implementations may require only a single label format for each label type. If this is the case, the system picks the default label format for each type. The system also picks the default if there are no applicable rules. If the implementation requires more than one label format for a label type label format rules are necessary.

Assigning Printer to the Required Level

Printers can be assigned to Site level, responsibility level, user level.

WMS Label Format Setup

Labels are an integral part of WMS functionalities.
This will help in easy transaction, clear tracking and using this transactions can be automated.
Oracle provides different types of labels to setup as per your business transactions.
Supplier labeling speeds-up the receiving process by enabling bar code scanning of inbound purchase orders, that results in less receipt processing time, immediate recognition of available materials, and higher receiving accuracy.

Labels can be customized as per customer needs to cpmly with specific business or govt compliances with fields and barcodes.

Even you can print customized documents as labels in case of requirement using label printing api.
Below is the step wise setup with an example for clear understanding

Create an Item

Navigation:
Inventory Super User -> Inventory -> Items -> Master Items

Assign Items to the organization

Tools -> Organization Assignments

Define Label Format

You can use oracle provided standard Labels or you can customize as per your business need

Warehouse Manager -> Setup -> Warehouse Configuration -> Printers & Devices -> Define Label Format

Select label type and entity type, find seeded label format and click on Label Fields and variables to check visible fields. You can define your custom format and select required fields.

Assign Label Format to Business Flow

Setup -> Warehouse Configuration -> Printers & Devices -> Assign Label Format to Business Flow

*Drill down business flow and select Miscellaneous / Alias Receipt. Assign label type at user level

Create Label Format Rule

Setup -> Warehouse Configuration -> Rules -> Warehouse Execution -> Rules

*Create a new Label format rule to satisfy the business condition

Then run Generate All Rule concurrent program

Do A transaction to understand how it works

Do a Misc Receipt Transaction using MSCA 

Check the generated Labels

Navigate to

Warehouse manager -> Inquiry -> View Label Requests

Query by giving your user id or any such specific parameter in the form and you will be able to see the label, its status with details and the XML format of the label.

This labels can be printed in all types of label printers provided by many different vendors like Zebra, etc.

Overview of Oracle Inventory

Oracle Inventory

Oracle Inventory treats many different types of things as inventory. Inventory can be:

•        Finished goods that you sell to customers.

•        Services that you sell to customers.

•        Spare parts for maintenance.

•        Raw materials for manufacturing processes.

•        Inventory you purchase from a supplier on consignment.

Inventory Capabilities

•        Define Part Numbers

•        Define Organization Structures

•        Track On-hand Inventories

•        Plan material Replenishment

•        Issue Material

•        Forecast demand

•        Reserve Material

•        Maintain Inventory Accuracy

Receipt to Issue Lifecycle

Oracle Inventory uses the receipt to issue process to maintain its inventory. When goods come to the warehouse, receipt process captures the onhand in Inventory. After receiving the goods, it can be transferred within the organization or to other organizations too. Finally, we can issue the goods from the Inventory for any business.

Receiving Inventory

There are different ways of receiving materials into organizations

Oracle Purchasing

You can use Oracle Purchasing to receive material from outside of your organization. You can receive:

•        Purchase order receipts

•        Internal requisitions

•        In-transit receipts

•        Return material authorizations

•        Unexpected receipts

Oracle Work in Process

You can use Oracle Work in Process to receive material from the manufacturing floor. You can receive:

•        Component returns

•        Negative component issues

•        Assembly returns

Types of Inventory receipts:

·         Receipt and Deliver (Direct)

·         Receipt and then Deliver (Standard)

·         Inspection

·         Receipt

·         Inspection

·         Deliver

Transferring Inventory

Different applications can generate requests to transfer inventory.

Oracle Shipping Execution

You can use Oracle Shipping Execution to generate a transfer to move material from a storage area to a staging to for shipping.

Oracle Order Management

You can use Oracle Order Management to generate a transfer to move material from a storage area to a staging area for shipping.

Oracle Work in Process

You can use Oracle Work in Process to generate a transfer to acquire components for a project.

Oracle Inventory

You can use Oracle Inventory to:

•        Transfer material between organizations

•        Transfer material within an organization

•        Replenish materials

•        Request transfers

Issuing Inventory

You can use the following applications to issue material:

Oracle Order Management

Oracle Order Management can generate an inventory issue through:

•        Sales orders

•        Internal orders

Oracle Purchasing

Oracle Purchasing can generate an inventory issue for:

•        Return to vendor materials

Oracle Work in Process

Oracle Work in Process can generate an inventory issue through:

•        Component issues

•        Assembly returns

Oracle Inventory

Oracle Inventory can issue stock through:

•        User-defined material issues

•        Inter-Organization Transfer

•        Cycle count negative Issue

•        Issue Requests

Integration of Oracle Inventory to Manufacturing Applications

Oracle Inventory interacts with the following applications:

•        Oracle Cost Management receives cost information and transaction rates from Oracle Inventory. Oracle Inventory integrates to Oracle General Ledger via Oracle Cost Management

•        Oracle Work in Process provides work in process (WIP) activity and available to promise (ATP) supply information to Oracle Inventory and receives item and on-hand quantity information from Oracle Inventory.

•        Oracle Advanced Supply Chain Planning and Oracle Global Order Promising provide ATP supply information and receive on-hand quantity and forecast information from Oracle Inventory.

•        Oracle Bill of Materials receives item information from Oracle Inventory.

•        Oracle Engineering provides engineering item information and receives item information from Oracle Inventory.

•        Oracle Order Management provides shipping, reservations, and demand information and receives item, ATP, and on-hand quantity information from Oracle Inventory.

•        Oracle Purchasing provides receipt, delivery, ATP supply, and planning supply information and receives item requisition, and inter-organization shipment information from Oracle Inventory.

Integration of Oracle Inventory to Financial Applications

•        Oracle General Ledger provides ledger and currency exchange rates and receiving accounting summary and detail information via Oracle Cost Management.

•        Oracle Landed Cost Management (LCM) enables you to determine the real costs (such as insurance, transportation, handling, storage costs, container fees, and import or export charges) associated with acquiring items. Oracle Landed Cost Management calculates the actual landed costs after actual values are received from invoices. The variance between the actual landed cost and the estimated landed cost are passed to Oracle Cost Management which in turn updates the proper accounts and valuation. To use Oracle LCM in Oracle Inventory, you need to:

- Set up inventory organization options in the Organization Parameters – Inventory   Parameters window.

- Set up the inventory organizations to be used with Oracle Landed Cost Management and specify the landed cost management variance account for that organization.

•        Oracle Payables receives Item information from Oracle Inventory.

•        Oracle Receivables provides intercompany invoice information and receives item information from Oracle Inventory.

•        Oracle Assets receives item information from Oracle Inventory