AP Invoice Interface/Creation

AP Invoice Interface is concerned with the creation of payable invoices in the Oracle System. Standard Payable invoices are created in the system for the PO sent across to the supplier and invoice details received for the same.
Payable invoices can be created manually by entering from the Invoice screen but in business having recurring transactions opt for auto invoice creation. This mechanism involves populating the interface tables with the invoice table and run the payables import program to create the invoice.

Table Details:
AP Invoice Interface tables
1.AP_INVOICES_INTERFACE
2.AP_INVOICE_LINES_INTERFACE

AP Invoice Base Tables:
1. AP_INVOICES_ALL
2.AP_INVOICE_DISTRIBUTIONS_ALL
3. AP_PAYMENT_SCHEDULES_ALL

Error table:
1.AP_INTERFACE_REJECTIONS
2.AP_INTERFACE_CONTROLS

Users can use the Payables Open Interface Import program to create Payables invoices from invoice data in the Payables Open Interface Tables.  Payables Open Interface Tables need to be populated with invoice data from the following sources:

• Supplier EDI invoices (ASC X12 810/EDIFACT INVOIC) transferred through Oracle EDI Gateway

• Invoices from other accounting systems with a custom SQL*Loader program 

• Credit card transactions you have transferred using the Credit Card Invoice Interface Summary

After the interface tables are populated users need to submit 

1. The Payables import program to create payables invoices.Enter the report parameters.  In the Source field, select the source name from the list of values. If your records have a Group and you want to import invoices for only that group, enter the Group. This will allow you to import smaller sets of records concurrently for the same source, which will improve your performance. If you use batch control, enter a Batch Name.

Debugging Interface Errors

After the interface program completes invoices will be created in the system. For the records failing the validations no invoices will be created and the records will be errored out in the interface.

Some of the most fatal errors are

• No supplier or supplier site

• The invoice number is a duplicate

If the invoice level information is correct, Payables will validate all values at the line level, and the rejections report will list all line level problems. If a distribution is rejected, the whole invoice is rejected.You can correct the data in one of the following ways:

• Use the Open Interface Invoices window to correct problems directly in the Payables Open Interface tables.

Invoice Validation

Once the Invoices have been created invoice validation needs to be done so that invoices can be processed further to make payments and transfer to GL. For this we need to either manually validate individual invoices from the Invoice Screen or run the Invoice Validation Program. This activity will validate the invoices and allow further processing, In case invoices fail in validation then the invoices will be put on hold . the user can see the hold details by pressing the "HOLDS" button on the invoice screen. at the backend hold details will be stored in the table : ap_invoice_holds

Delivery in Oracle Applications Shipping Execution

The concept of delivery is of importance to any person understanding the concept of shipping in Oracle Order Management. Once the Order lines are pick released, next step is shipping of the order lines. In oracle shipping is handled by the concept of delivery. When a order line(s) is shipped basically shipping of a delivery is done.

Delivery Overview
A Delivery consists of a set of lines that are scheduled to be shipped to a customers ship to location on a specific date and time. A delivery can include lines from different sales orders as well as back orders.
Deliveries can either be created manually and then lines are assigned to the delivery or users can auto-create deliveries when pick releasing a sales order lines.
If deliveries are auto-created they will be by grouped by oracles mandatory grouping criteria ship from location and ship to location,Location. However, additional grouping criteria can be included such as:
■ Customer
■ Freight Terms
■ FOB Code
■ Intermediate Ship To Location
■ Ship Method

Deliveries are the entities which are regarded as the shipment number of an order line and to the deliveries users will assign the tracking numbers,bill of lading and container information.

Delivery Creation

Manual Delivery Creation:
Deliveries can be manually created by users by navigating to the deliveries window, here the user will key in all the delivery details, once the delivery is created the user needs to assign order lines to the deliveries, in case any of the oracle grouping rules are violated the user will be intimated.


To create a delivery:
1. Navigate to the Delivery window.
2. Enter a Name and Org Code for the delivery.

3. Select the Initial Ship from location and the Ultimate Ship to.
At this point, you can save the delivery.If the user wishes he can enter the additional details
4. Save the Work


Auto- Create Deliveries:
When the Order is pick released the user can auto create the deliveries at that time and by selecting the Auto Create Delivery : Yes ,  Oracle will create the delivery and assign the eligible order lines to the delivery.

Additionally the user can pick release the orders without creating deliveries but then navigate to the shipping Transactions Screen where he can auto-create delivery and assign it to order lines. When auto-creating deliveries in case the order has multiple linesOne or more deliveries can be created depending on the default delivery grouping criteria set up in the Shipping Parameters. For example, if two groups of delivery
lines have different Ship To addresses, a different delivery number is assigned to each group.


To auto-create deliveries:
1. Navigate to the Query Manager window, and find the delivery lines.
The delivery lines are displayed in the Shipping Transactions form.
2. Select the delivery lines for which you want to create a delivery.
3. From the Actions menu, select Auto-create Deliveries.
4. Click Go to create a delivery or deliveries for the selected lines.
You can view the delivery name created for the delivery lines in the Delivery
column in the Lines/LPNs tab.
5. Choose the Delivery tab to view or add additional delivery details.
6. Save your work.


Whenever a delivery is created in the system the status of the delivery is OPEN ,  till the delivery is open user can manipulate the delivery by assigning or un-assigning lines to it.

When the user performs shipping of an Order line by selecting the Option : Ship Confirm from shipping transactions Window , he is basically Ship confirming the Delivery.
When the user ship Confirms a Delivery Line he can perform the following operations

1. Ship all the Quantities to the Order Lines
2. Partially ship some of the quantities of the order lines by updating the same in the shipping transactions window,in case of partial shipment of the delivery line the order line will be split up by the system
3. Backorder the quantities of the order line, In such a case the order line is backordered, the inventory is moved back from Stage to source sub-inventory, the released_status of the line is updated to 'B' and the user is required to pick release the line again
4. The user can specify the Tracking number,bill of lading number, carton details and the serial numbers of the parts in case of serial controlled items.
5. Specify the Shipping documents to be printed e.g : Bill of Lading

Once the user performs Ship Confirm operation on the delivery the delivery status is changed to 'CLOSED', no further changes can be performed on the delivery.

System Changes :

1. When a delivery is created a new row is inserted into the table : wsh_new_deliveries
2. When the user assigns a delivery line to the order line the table wsh_delivery_assignements is updated with the delivery id , this table keeps a track of the delivery assigned to order line as it also tracks the delivery detail id of the order line
3. The delivery id is the shipment number of the order line
4. All the changes can be seen by the user on the front end by navigating to  : Order Line --> Actions Button --> Additional Line Information --> Delivery Tab --> View Delivery Details , this screen will show the user all the delivery and pick release information of the sales order

Process Order Api in Oracle to Create Order

Process Order API is a standard API provided by Oracle to Create/Update/Cancel the Order.
Given below is a sample code which highlights the use of the API

l_msg_index_out            NUMBER;

l_excp_exit                EXCEPTION;

l_num_salesrep_id          NUMBER;

l_line_rec                 oe_order_pub.line_rec_type;

l_line_tbl                 oe_order_pub.line_tbl_type;

l_request_tbl              oe_order_pub.request_tbl_type;

l_header_rec               oe_order_pub.header_rec_type;

l_header_val_rec           oe_order_pub.header_val_rec_type;

l_header_adj_tbl           oe_order_pub.header_adj_tbl_type;

l_header_adj_val_tbl       oe_order_pub.header_adj_val_tbl_type;

l_header_price_att_tbl     oe_order_pub.header_price_att_tbl_type;

l_header_adj_att_tbl       oe_order_pub.header_adj_att_tbl_type;

l_header_adj_assoc_tbl     oe_order_pub.header_adj_assoc_tbl_type;

l_header_scredit_tbl       oe_order_pub.header_scredit_tbl_type;

l_header_scredit_val_tbl   oe_order_pub.header_scredit_val_tbl_type;

l_line_val_tbl             oe_order_pub.line_val_tbl_type;

l_line_adj_tbl             oe_order_pub.line_adj_tbl_type;

l_line_adj_val_tbl         oe_order_pub.line_adj_val_tbl_type;

l_line_price_att_tbl       oe_order_pub.line_price_att_tbl_type;

l_line_adj_att_tbl         oe_order_pub.line_adj_att_tbl_type;

l_line_adj_assoc_tbl       oe_order_pub.line_adj_assoc_tbl_type;

l_line_scredit_tbl         oe_order_pub.line_scredit_tbl_type;

l_line_scredit_val_tbl     oe_order_pub.line_scredit_val_tbl_type;

l_lot_serial_tbl           oe_order_pub.lot_serial_tbl_type;

l_lot_serial_val_tbl       oe_order_pub.lot_serial_val_tbl_type;

l_request_rec              oe_order_pub.request_rec_type;

l_action_request_tbl       oe_order_pub.request_tbl_type;

l_num_tbl_index            NUMBER;

l_chr_return_status        VARCHAR2 (20);           --POA return Status

l_num_msg_count            NUMBER;

l_chr_msg_data             VARCHAR2 (1000);

l_num_msgcntr              NUMBER;

l_num_order_number        NUMBER;

l_num_header_id           NUMBER;

l_num_line_count          NUMBER;

l_chr_error_message       VARCHAR2(1000);

l_num_temp                NUMBER;

l_excp_skip               EXCEPTION;

l_chr_error               VARCHAR2(1000);

BEGIN

   fnd_file.Put_line(fnd_file.LOG,'*********************************************** BOOK ORDER **************************');

   fnd_file.put_line(fnd_file.LOG,'Begining of the procedure Book Order');

   out_chr_errbuf := 'success';

       --Initialize header record

         l_header_rec                   := oe_order_pub.g_miss_header_rec;

        

         l_header_rec.cust_po_number := cust_po_number;

         l_header_rec.ordered_date := g_dte_sysdate;

         l_header_rec.salesrep_id            := -3;

         l_header_rec.order_type_id := 1085; 

         l_header_rec.operation := oe_globals.g_opr_create; --Specifies that Order is getting created

        

         l_header_rec.order_category_code := 'ORDER';

         l_num_tbl_index := 1;

         l_header_rec.booked_flag                    := 'Y';

         l_header_rec.sold_to_org_id                 := customer_id;

         l_header_rec.invoice_to_org_id              := invoice_to_org_id;

         l_header_rec.ship_to_org_id                 := ship_to_org_id;

         l_header_rec.price_list_id                  := price_list_id;

         l_line_tbl (l_num_tbl_index) := oe_order_pub.g_miss_line_rec;

         -- Line attributes

         l_line_tbl (l_num_tbl_index).inventory_item_id      := inventory_item_id;

         l_line_tbl (l_num_tbl_index).ordered_quantity       := quantity;

         l_line_tbl (l_num_tbl_index).line_category_code     := 'ORDER';

         l_line_tbl (l_num_tbl_index).line_type_id           := 1043; --transaction type id

         l_line_tbl (l_num_tbl_index).operation              :=     oe_globals.g_opr_create; -- Create Order Line

         l_action_request_tbl (l_num_tbl_index)              := oe_order_pub.g_miss_request_rec;

         l_action_request_tbl (l_num_tbl_index).request_type := oe_globals.g_book_order;

         l_action_request_tbl (l_num_tbl_index).entity_code  := oe_globals.g_entity_header;

         l_num_tbl_index := l_num_tbl_index + 1;

         oe_order_pub.process_order

                        (p_api_version_number          => 1.0,

                         p_init_msg_list               => fnd_api.g_true,

                         p_return_values               => fnd_api.g_false,

                         x_return_status               => l_chr_return_status,

                         x_msg_count                   => l_num_msg_count,

                         x_msg_data                    => l_chr_msg_data,

                         p_header_rec                  => l_header_rec,

                         p_line_tbl                    => l_line_tbl,

                         p_action_request_tbl          => l_action_request_tbl,

                         x_header_rec                  => l_header_rec,

                         x_header_val_rec              => l_header_val_rec,

                         x_header_adj_tbl              => l_header_adj_tbl,

                         x_header_adj_val_tbl          => l_header_adj_val_tbl,

                         x_header_price_att_tbl        => l_header_price_att_tbl,

                         x_header_adj_att_tbl          => l_header_adj_att_tbl,

                         x_header_adj_assoc_tbl        => l_header_adj_assoc_tbl,

                        x_header_scredit_tbl          => l_header_scredit_tbl,

                         x_header_scredit_val_tbl      => l_header_scredit_val_tbl,

                         x_line_tbl                    => l_line_tbl,

                         x_line_val_tbl                => l_line_val_tbl,

                         x_line_adj_tbl                => l_line_adj_tbl,

                         x_line_adj_val_tbl            => l_line_adj_val_tbl,

                         x_line_price_att_tbl          => l_line_price_att_tbl,

                         x_line_adj_att_tbl            => l_line_adj_att_tbl,

                         x_line_adj_assoc_tbl          => l_line_adj_assoc_tbl,

                         x_line_scredit_tbl            => l_line_scredit_tbl,

                         x_line_scredit_val_tbl        => l_line_scredit_val_tbl,

                         x_lot_serial_tbl              => l_lot_serial_tbl,

                         x_lot_serial_val_tbl          => l_lot_serial_val_tbl,

                         x_action_request_tbl          => l_request_tbl

                        );  

            fnd_file.put_line (fnd_file.LOG,

                                     'Return from the API'

                                  || 'return status'

                                  || l_chr_return_status

                                 );

        COMMIT;

        IF l_chr_return_status <> 'S'

        THEN

           print_api_error_messages (l_chr_error,

                                    'OE_ORDER_PUB.PROCESS_ORDER',

                                     l_chr_return_status,

                                     l_num_msg_count

                                     );

        -- fnd_file.put_line(fnd_file.LOG,'Return from api error Procedure' || l_chr_error);

           l_chr_error_message := l_chr_error;

           RAISE l_excp_skip;

        END IF;

END;

The API can be used to create or update an order. 

In order to create a sales order pass the parameter  l_header_rec.operation := oe_globals.g_opr_create

In order to Update an Order pass the parameter :  l_header_rec.operation := oe_globals.g_opr_update

In order to Cancel an Order Line pass the parameter: l_header_rec.operation := oe_globals.g_opr_update

 when passing the other parameter of the line record,update the Ordered Quantity as 0

When we are calling the Process Order API there are certain mandatory fields which need to be passed eg : order type,customer details and others. and there are other details are derived by the defaulting rules of the Order Management setup, in case any of the values being derived by the defaulting rules is already provided in the API it overrides the defaulting rules value.