INV - Sample Script to Get Onhand Quantity Using INV_Quantity_Tree_PUB API

Excellent Repository for Oracle APIs
http://irep.oracle.com/index.html

This example is for a Lot controlled Item. Depending on your requirement, it can be Lot/Serial controlled, or no control. Also, in this example, I am using Subinventory.

DECLARE
x_return_status VARCHAR2 (1);
x_msg_data VARCHAR2 (4000);
x_msg_count NUMBER;
x_qoh NUMBER;
x_rqoh NUMBER;
x_qr NUMBER;
x_qs NUMBER;
x_att NUMBER;
x_atr NUMBER;
x_sqoh NUMBER;
x_srqoh NUMBER;
x_sqr NUMBER;
x_sqs NUMBER;
x_satt NUMBER;
x_sqtr NUMBER;
BEGIN
inv_globals.set_org_id (104);

inv_quantity_tree_pub.clear_quantity_cache;

inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => 107,
p_inventory_item_id => 28459,
p_tree_mode => inv_quantity_tree_pvt.g_transaction_mode,
p_is_revision_control => FALSE,
p_is_lot_control => TRUE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => '123456',
p_subinventory_code => 'PACK_FOAM',
p_locator_id => NULL,
x_qoh => x_qoh,
x_rqoh => x_rqoh,
x_qr => x_qr,
x_qs => x_qs,
x_att => x_att,
x_atr => x_atr);

DBMS_OUTPUT.put_line ('x_return_status = ' ||x_return_status);
DBMS_OUTPUT.put_line ('x_msg_count = ' || x_msg_count);
DBMS_OUTPUT.put_line ('x_qr = ' || x_qr);
DBMS_OUTPUT.put_line ('x_qoh = ' || x_qoh);
DBMS_OUTPUT.put_line ('x_rqoh = ' || x_rqoh);
DBMS_OUTPUT.put_line ('x_qs = ' || x_qs);
DBMS_OUTPUT.put_line ('x_att = ' || x_att);
DBMS_OUTPUT.put_line ('x_atr = ' || x_atr);

END;

Some explanations:
X_QOH NUMBER - quantity on hand
X_RQOH NUMBER - reservable quantity on hand
X_QR NUMBER - quantity reserved
X_QS NUMBER - quantity suggested
X_ATT NUMBER - quantity available to transact
X_ATR NUMBER - quantity available to reserve

Order to Invoice Flow

1. Order Entry
This is first stage, When the order is entered in the system, it basically creates a record in order headers and Order Lines table.

oe_order_headers_all (Here the flow_status_code as entered)
oe_order_lines_all (flow_status_code as entered) ( order number is generated)

2.Order Booking
This is next stage , when Order is booked then the Flow status changed from Entered to Booked. At this stage ,these below table get affected.

oe_order_headers_all (flow_status_code as booked ,booked_flag updated)
oe_order_lines_all (flow_status_code as awaiting shipping,booked_flag updated)
wsh_new_deliveries (status_code OP open)
wsh_delivery_details (released_status ‘R’ ready to release)
Same time, Demand interface program runs in background And insert into inventory tables mtl_demand

3. Reservation
This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved.Once this program get successfully get completed , the mtl_reservations table get updated.

4. Pick Release
Ideally pick release is the process which is defined in which the items on the sales order are taken out from inventory. Normally pick release SRS program runs in background . Once the program get completed these are the table get affected:

oe_order_lines_all (flow_status_code ‘PICKED’ )
wsh_delivery_details (released_status ‘S’ ‘submitted for release’ )
mtl_txn_request_headers
mtl_txn_request_lines
(move order tables.Here request is generated to move item from saleble to staging sub inventory)
Mtl_material_transactions_temp (link to above tables through move_order_header_id/line_id

5.Pick Confirm
Items are transferred from saleble to staging Subinventory.

mtl_material_transactions
mtl_transaction_accounts
wsh_delivery_details (released_status ‘Y’‘Released’ )
wsh_delivery_assignments

6.Ship Confirm
Here ship confirm interface program runs in background . Data removed from wsh_new_deliveries

oe_order_lines_all (flow_status_code ‘shipped’)
wsh_delivery_details (released_status ‘C’ ‘Shipped’)
mtl_transaction_interface
mtl_material_transactions(linked through Transaction source header id)
mtl_transaction_accounts
Data deleted from mtl_demand,mtl_reservations
Item deducted from mtl_onhand_quantities

7.Enter Invoice
This is also called Receivables interface, that mean information moved to accounting area for invoicing details. Invoicing workflow activity transfers shipped item information to Oracle Receivables.

ra_interface_lines_all (interface table into which the data is transferred from order management)Then Autoinvoice program imports data from this table which get affected into this stage are recievables base table.

ra_customer_trx_all (cust_trx_id is primary key to link it to trx_lines table and trx_number is the invoice number)
ra_customer_trx_lines_all (line_attribute_1 and line_attribute_6 are linked to header_id (or order number) and line_id of the orders)

8.Complete Line
In this stage order line leval table get updated with Flow status and open flag.

oe_order_lines_all (flow_status_code ‘shipped’, open_flag “N”)

9.Close Order
This is last step of Order Processing . In this stage only oe_order_lines_all table get updated. These are the table get affected in this step.

oe_order_lines_all (flow_status_code ‘closed’,open_flag “N”)

These are the typically data flow of a order to cash model for a
standard order.

Oracle Order Status List

Order Header Statuses

Following is a list of all possible statuses assigned to an order header.

• Active
• Awaiting Invoice Interface - Incomplete Data
• Awaiting Invoice Interface - On Hold
• Awaiting Start Date
• Booked
• Cancelled
• Closed
• Customer Accepted
• Draft
• Draft - Customer Rejected
• Draft - Internal Rejected
• Draft Submitted
• Entered
• Expired
• Internal Approved
• Internal Rejected
• Invoice Interface - Complete
• Lost
• Offer Expired
• Pending Customer Acceptance
• Pending Internal Approval
• Submitted
• Terminated
• User Working


Order Line Statuses

Following is a list of all possible statuses assigned to an order line.

• Awaiting Export Screening
• Awaiting Fulfillment
• Awaiting Invoice Interface - Incomplete Data
• Awaiting Invoice Interface - On Hold
• Awaiting Invoice Interface - Partially Interfaced, RFR Item
• Awaiting Invoice Interface - Pending Complete Delivery
• Awaiting Invoice Interface - RFR Item
• Awaiting Invoice Interface - Unexpected error
• Awaiting Payment Assurance - On Hold
• Awaiting Payment Assurance - Receipts Not Assured
• Awaiting Receipt
• Awaiting Reprice - Invalid setup
• Awaiting Reprice - On reprice line hold
• Awaiting Reprice - Pricing error
• Awaiting Reprice - Unexpected error
• Awaiting Return
• Awaiting Return Disposition
• Awaiting Shipping
• Awaiting Supply
• BOM and Routing Created
• Booked
• Cancelled
• Closed
• Completed Export Screening
• Config Item Created
• Customer Accepted
• Data Error Export Screening
• Draft
• Draft - Customer Rejected
• Draft - Internal Rejected
• Draft Submitted
• Entered
• Fulfilled
• Interfaced to Receivables
• Internal Approved
• Internal Rejected
• Inventory Interfaced
• Invoice Interface - Not Applicable
• Lost
• Offer Expired
• PO-Created
• PO-Partial
• PO-Received
• PO-ReqCreated
• PO-ReqRequested
• Partially Interfaced to Receivables
• Payment Assurance - Complete
• Payment Assurance - Incorrect Data
• Pending Customer Acceptance
• Pending Internal Approval
• Picked
• Picked Partial
• Preprovision
• Preprovision Failed
• Preprovision Requested
• Preprovision Succeeded
• Production Complete
• Production Eligible
• Production Open
• Production Partial
• Provisioning Failed to update Transaction Details
• Provisioning Rejected
• Provisioning Requested
• Provisioning Successful
• Provisioning in Error
• Released to Warehouse
• Reprice - Complete
• Reprice - Not Applicable
• Returned
• Scheduled
• Shipped
• Supply Eligible
• Supply Open
• Supply Partial
• Third Party Billing Failed
• Third Party Billing Requested
• Third Party Billing Succeeded

Creating AP Checks in Oracle R12

Check Printing has changed considerably in Oracle R12. Initially it looked to me to be very complex, but after spending considerable time understanding it, I have started to like many of the new features. Some of the new additions are:

1. There is a standard Oracle Program to generate the XML. It creates most of the fields necessary to create checks.
2. Check payment data has been moved from 'Payables' responsibility to 'Payments' responsibility. The schema name for Payments is IBY.
3. You have to create only the RTF file since the XML file is generated by Oracle. You may change or add a new RTF - all you have to do is to upload the new RTF file once the check processes have been setup.
4. The number of lines per page can be setup in the Payment Processing Profile. The XML file will be generated based on this number. It does seem to me that this option puts some restrictions on the flexibility provided by oracle, but so far I was able to work with it.

Pre-requisites:

1. Download BI Publisher Template Builder for Word ver 10.1.3.4.1 from download.oracle.com
2. Basic requirements to start creating checks is to have an RTF file (Template) and an XML file (data definition).
3. There are 2 ways that you can have an RTF file:
   o Create your own, or
   o (Recommended) Download standard oracle Template
   Payables Manager > Setup > Payment > Payment Administrator > Formats > XML Publisher Format Templates
   Query and download the RTF file
   ‘Standard Check Format’ IBYDC_STD1.rtf or
   Standard Check Format (Stub After Payment) IBYDC_STD1A.rtf
4. Getting an XML file is tricky. I had to spend some trying to get this extract. This is kind of a Catch-22 situation. You have to actually create the entire Payment setup first and run a payment process to get the extract. Here is what Oracle recommends:
   
   --> Go to Metalink and Check Oracle Note 457539.1. This note says that the Package IBY_FD_EXTRACT_EXT_PUB controls the XML extract used in check printing. The supplied version can be found in $IBY_TOP/patch/115/sql/ibyfdxeb.pls
   
   --> Better way is to follow the instructions in Doc ID: 465389.1 R12 Create Or Modify A Payment Format Using XML Builder. This note explains the same steps that I have defined below
   
5. MICR Fonts. This is another important requirement which you will run into later on. So I have decided to address it right away because you may end up spending a lot more time if you start thinking about it later. One of the easiest way is to buy the MICR font from a third party like IDAutomation (http://www.idautomation.com/) and use MICRW1B.ttf. Typical cost is around $750.00 per user license – you may need to purchase only one license. You may also download their Demo version which is good for testing.
   
   The good news is that Oracle also provides a MICR font which is available if you have downloaded the BI Template Builder. Some banks reject this font, but so far I did not have any problem with the bank that we used.
   
   Download and install BI Publisher ver 10.1.3.4.1
   Font File Location: C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\fonts\ MICR____.TTF
   
   In order to use this file in the RTF template, copy the font file MICR____.TTF to C:\WINDOWS\Fonts
   
   

At this stage, you may or may not have designed the Check template. But you can still use IBYDC_STD1.rtf downloaded earlier.

Following steps are needed to create a check in AP from scratch. These steps may also be used to create the initial payment to get the XML file.

Step 1: Create XML Template

Payables Manager > Setup > Payment > Payment Administrator > Formats > XML Publisher Format Templates
Click 'Create Template'
Enter valid name, Code, Application (= Payments)
Type = RTF
Data Definition = Oracle Payments Funds Disbursement Payment Instruction Extract 1.0
Default File Language = English
Default File Territory = UNITED STATES
Upload your RTF File

Step 2: Create Payment Format

Payables Manager > Setup > Payment > Payment Administrator > Formats > Formats
Select Type = Disbursement Payment Instruction
Click on Create
Enter Code, NameType = Same as above
Data Extract = Oracle Payments Funds Disbursement Payment Instruction Extract 1.0
XML Publisher Extract = XML Template name created in Step 1

Step 3: Create Payment Document

Payables Manager > Setup > Payment > Bank Accounts

Query up your Bank Name. It is assumed that your Bank has already been setup in AP.
Select the Bank Account and click on Manage Payment Documents.
Click on Create
Enter:
Name: XXXX Check Payment Document
Paper Stock Type: Blank Stock or Prenumbered Stock
If you have remittance stub, check the box for Attached Remittance Stub
Important: If you check the above box, a new field called ‘Number of Lines per Remittance Stub’ will show up. This is number that you will have to find out by trial and error once you start testing your template. Initially, set it to a lower number, say 15.
Format: Enter Format defined in step 2
First Available Document Number:
Last Available Document Number
Enter any other information that you may need.
Click Apply

Step 4: Create Payment Profile

Payables Manager > Setup > Payment > Payment Administrator > Payment Process Profiles

Click on Create
Enter:
Code
Name
Payment Instruction Format: As defined in Step 2
Processing Type: Printed
Payment Completion Point: When the Payment Instruction is Formatted
Default Payment Document: Document created in Step 3
Payment File: Send to Printer
Check the box ‘Automatically Print After Formatting’ (Note: You mau choose not to check this box initially – it will help in testing)
Default Printer: Valid oracle printer name
Click Apply

Now you are all set to create checks. All you have to do is create an Invoice, or select any existing validated invoice for a vendor.

There is one more thing that you must check before creating the payment.

Verify Concurrent Program

Go to Application Developer or Sys Admin responsibility and bring up the concurrent program Format payment Instructions

Concurrent > Program > Define > F11 > Format Payment Instructions > Ctrl-F11
(Short Name: IBY_FD_PAYMENT_FORMAT)

Set Output Format to XML: Use this option to first generate your XML (Data Definition) file

Set Output to PDF : This will create your check in PDF format once your check template is ready. Also, Set Style to PDF Publisher in this case.


How to Submit a Request to create Payments

Submission process for AP Checks/Payments has also changed in R12. Most of the options are now available in HTML screens. The good news is that you can create templates (Not XML templates - these are different) so that you do not have to enter the same information every time you submit a request.

Submit Request:

Payables Manager > Payment Manager > Submit Single Payment Process Request

Following parameters are minimum required - you can enter others as necessary:

Following parameters are minimum required - you can enter others as necessary:
Payment Process Request name (Mandatory): Provide any useful Name

Use Template: Leave Blank

Payee : Vendor NamePayment Method: Check (Or any other name that has been setup is payment method)

Click on Payment Attributes Tab:
Payment Date: Defaulted to today's date

Disbursement Bank Account: Enter your Bank Account Name associated with this payment
Payment Document : Enter payment document name created in Step 3 above
Payment Process Profile : This value will get filled automatically with the profile defined above in step 4
Payment Exchange Rate Type: Enter valid excahnge rate type (Ex: Corporate, Spot)

These parameters are sufficient to create a payment. Click on Submit in order to process. However, I do recommend following steps also in the begining.

Click on Processing Tabs:

Check following boxes

• Stop Process for Review After Scheduled Payment Selection. This step will stop after building the payments so that you can review the payments selected, and cancel any of them, if necessary
• Stop Process for Review After Creation of Proposed Payments
  Click on Submit. Click on Home. Click on Monitor Requests.

How to Create Template for Request Submission

Payables Manager > Payment Manager > Create Template

Note: All of the fields in this process are the same as in 'Submit Single Request'. Enter any fields that you repeat for request submission. In the above example, usually leave the Payee field blank if you want to run for separate vendors every time. Once the template has been created and saved, you may enter the template name in the field 'Use Template' in the example above.

There are several other options which I haven't explored yet. I will add more once I get to try them out.