Payables Open interface Import in Oracle apps R12

Different Sources that can load data into Open invoice import interface tables

1. Quick Invoices window
2. Oracle e-Commerce Gateway
3. Credit Card transaction data
4. Property Manager
5. The Oracle Assets Export Lease Payments to Payables process
6. Oracle XML Gateway
7. SQL*Loader OR BPEL Interfaces can load invoice information from Legacy Systems
8.  Invoices that your suppliers entered and submitted online via Oracle iSupplier Portal

Interface Tables:

AP_INVOICES_INTERFACE

AP_INVOICE_LINES_INTERFACE

Base Tables:

                AP_INVOICES_ALL

                AP_INVOICE_LINES_ALL

                AP_INVOICE_DISTRIBUTIONS_ALL

                                     (&)

                Payment Tables

Basic Process:

                The Payables Open Interface program validates each record you select for import, and if the record contains valid data, then the program creates a Payables invoice with distributions and scheduled payments based on the invoice header and lineinformation in the record.

Records in the AP_INVOICE_LINES_INTERFACE table create one or more invoice distributions. Note that one row may create more than one distribution. For example, if you enter a Tax line in this table and prorate it across three Item lines, during Open Interface Import the system will create three Tax invoice distributions based on the single Tax line in this table.

 

Below diagram will give you the brief description of “Payables open interface import” programs process.

  required and optional columns in the payables invoice import tables for importing PO Matched Invoices.


 Table:  AP_INVOICE_INTERFACE
  Required Columns

Column Name	Validation
INVOICE_ID	Populated from AP_INVOICES_INTERFACE_S.NEXTVAL
INVOICE_NUM	Must be unique to the supplier
PO_NUMBER	An approved, not cancelled, not closed or final closed PO
VENDOR_ID or VENDOR_NUM or VENDOR_NAME	An active vendor. Validated against PO_VENDORS
VENDOR_SITE_ID or VENDOR_SITE_CODE	An active pay site. Validated against PO_VENDOR_SITES
INVOICE_AMOUNT	Positive amount for 'STANDARD' type, Negative amount for 'CREDIT' type
ORG_ID	Required in Multi-Org Environment. Validated against AP_SYSTEM_PARAMETERS.ORG_ID
SOURCE	Must be in SELECT lookup_code   FROM ap_lookup_codes  WHERE lookup_type = 'SOURCE';

     Optional Columns

Column Name	Validation
INVOICE_DATE	Defaulted to SYSDATE
INVOICE_TYPE_LOOKUP_CODE	Defaulted to 'STANDARD'. It can be 'STANDARD' or 'CREDIT'
INVOICE_CURRENCY_CODE	Defaulted from PO_VENDOR_SITES. INVOICE_CURRENCY_CODE
EXCHANGE_RATE_TYPE	Defaulted from AP_SYSTEM_PARAMETERS. DEFAULT_EXCHANGE_RATE_TYPE
TERMS_ID or TERMS_NAME	Defaulted from  PO_VENDOR_SITES.TERMS_ID
DOC_CATEGORY_CODE	Only populated if using automatic voucher number
PAYMENT_METHOD_LOOKUP_CODE	Defaulted from PO_VENDOR_SITES .PAYMENT_METHOD_LOOKUP_CODE
PAY_GROUP_LOOKUP_CODE	Defaulted from PO_VENDOR_SITES. PAY_GROUP_LOOKUP_CODE
ACCTS_PAY_CODE_COMBINATION_ID	Defaulted from PO_VENDOR_SITES. ACCTS_PAY_CODE_COMBINAITON_ID
GROUP_ID	Group identifier. Suggest to use it
STATUS	DO NOT POPULATE IT

 Table:  AP_INVOICE_LINES_INTERFACE
 Required Columns 

Column Name	Validation
INVOICE_ID	Populated from AP_INVOICES_INTERFACE.INVOICE_ID
INVOICE_LINE_ID	Populated from AP_INVOICE_LINES_INTERFACE_S. NEXTVAL
LINE_NUMBER	A unique number to the invoice
TAX_CODE or TAX_CODE_ID	Validated against AP_TAX_CODES_ALL
LINE_TYPE_LOOKUP_CODE	'ITEM'
AMOUNT	Should be QUANTITY_INVOICED * UNIT_PRICE
If MATCH_OPTION is 'P', then populate
RELEASE_NUM or PO_RELEASE_ID	For Blanket Release only,  validated against PO_RELEASES_ALL
PO_NUMBER or PO_HEADER_ID	Validated against PO_HEADER_ALL
PO_LINE_NUMBER or PO_LINE_ID	Validated against PO_LINES_ALL
PO_SHIPMENT_NUM or PO_LINE_LOCATION_ID	Validated against PO_LINE_LOCATIONS_ALL
If MATCH_OPTION is 'R', then populate
RECEIPT_NUMBER	Validated against RCV_SHIPMENT_HEADERS.RECEIPT_NUM
RCV_TRANSACTION_ID or PO_LINE_LOCATION_ID	Validated against RCV_TRANSACTIONS

Optional Columns

Column Name	Validation
QUANTITY_INVOICED	Populated if different from PO shipment
UNIT_PRICE	Populated if different from PO shipment
MATCH_OPTION	'P' or 'R' or Defaulted from PO_VENDOR_SITES.MATCH_OPTION
ACCOUNTING_DATE	Defaulted from INVOICE_DATE or SYSDATE
FINAL_MATCH_FLAG	Populated 'Y' if it is final matching
INVENTORY_ITEM_ID	Validated against PO_LINES.INVENTORY_ITEM_ID
INVENTORY_DESCRIPTION	Validated against PO_LINES.INVENTORY_ITEM_DESCRIPTION
SHIP_TO_LOCATION_CODE	Populated if different from PO shipment
PRICE_CORRECTION_FLAG	Populated 'Y' if it is price correction

required and optional columns in the payables invoice import tables for importing simple invoices i.e. not matched with any PO or Project Related.
 Table:  AP_INVOICE_INTERFACE
  Required Columns

Column Name	Validation
INVOICE_ID	Populated from AP_INVOICES_INTERFACE_S.NEXTVAL
INVOICE_NUM	Must be unique to the supplier
VENDOR_ID or VENDOR_NUM or VENDOR_NAME	An active vendor. Validated against PO_VENDORS
VENDOR_SITE_ID or VENDOR_SITE_CODE	An active pay site. Validated against PO_VENDOR_SITES
INVOICE_AMOUNT	Positive amount for 'STANDARD' type, Negative amount for 'CREDIT' type
ORG_ID	Required in Multi-Org Environment. Validated against AP_SYSTEM_PARAMETERS.ORG_ID
SOURCE	Must be in  SELECT lookup_code   FROM ap_lookup_codes  WHERE lookup_type = 'SOURCE'

     Optional Columns

Column Name	Validation
INVOICE_DATE	Defaulted to SYSDATE
INVOICE_TYPE_LOOKUP_CODE	Defaulted to 'STANDARD'.  It can be 'STANDARD' or 'CREDIT'
INVOICE_CURRENCY_CODE	Defaulted from PO_VENDOR_SITES.INVOICE_CURRENCY_CODE
EXCHANGE_RATE_TYPE	Defaulted from AP_SYSTEM_PARAMETERS. DEFAULT_EXCHANGE_RATE_TYPE
TERMS_ID or TERMS_NAME	Defaulted from PO_VENDOR_SITES.TERMS_ID
DOC_CATEGORY_CODE	Only populated if using automatic voucher number
PAYMENT_METHOD_LOOKUP_CODE	Defaulted from PO_VENDOR_SITES. PAYMENT_METHOD_LOOKUP_CODE
PAY_GROUP_LOOKUP_CODE	Defaulted from PO_VENDOR_SITES.PAY_GROUP_LOOKUP_CODE
ACCTS_PAY_CODE_COMBINATION_ID	Defaulted from PO_VENDOR_SITES .ACCTS_PAY_CODE_COMBINAITON_ID
GROUP_ID	Group identifier. Suggest to use it
STATUS	DO NOT POPULATE IT

 Table:  AP_INVOICE_LINES_INTERFACE
 LINE_TYPE_LOOKUP_CODE = 'ITEM'
Required Columns   

Column Name	Validation
INVOICE_ID	Populated from AP_INVOICES_INTERFACE.INVOICE_ID
INVOICE_LINE_ID	Populated from AP_INVOICE_LINES_INTERFACE_S.NEXTVAL
LINE_NUMBER	A unique number to the invoice
LINE_TYPE_LOOKUP_CODE	'ITEM'
AMOUNT
ACCOUNTING_DATE	Optional. Defaulted from INVOICE_DATE or SYSDATE
DIST_CODE_CONCATENATED or DIST_CODE_COMBINATION_ID	Validated against  - 'Parent' must be 'No' for All the segments values  - DIST_CODE_CONCATENATED needs to meet the security rules  - DIST_CODE_COMBINATION_ID must be in SELECT code_combination_id   FROM gl_code_combinations  WHERE account_type = 'E'    AND enabled_flag = 'Y'    AND summary_flag = 'N'    AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE - 1)                    AND NVL (end_date_active, SYSDATE + 1)

LINE_TYPE_LOOKUP_CODE = 'TAX'
Required Columns   

Column Name	Validation
INVOICE_ID	Populated from AP_INVOICES_INTERFACE.INVOICE_ID
INVOICE_LINE_ID	Populated from AP_INVOICE_LINES_INTERFACE_S.NEXTVAL
LINE_NUMBER	A unique number to the invoice
TAX_CODE or TAX_CODE_ID	Validated against AP_TAX_CODES_ALL
LINE_TYPE_LOOKUP_CODE	'TAX'
AMOUNT
ACCOUNTING_DATE	Optional. Defaulted from INVOICE_DATE or SYSDATE
DIST_CODE_CONCATENATED or DIST_CODE_COMBINATION_ID	Optional.   Defaulted from AP_TAX_CODES.TAX_CODE_COMBINATION_ID. If one of them is populated, then validated against   - 'Parent' must be 'No' for All the segments values  - DIST_CODE_CONCATENATED needs to meet the security rules  - DIST_CODE_COMBINATION_ID must be in SELECT code_combination_id   FROM gl_code_combinations  WHERE account_type = 'E'    AND enabled_flag = 'Y'    AND summary_flag = 'N'    AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE - 1)                    AND NVL (end_date_active, SYSDATE + 1)

LINE_TYPE_LOOKUP_CODE = 'FREIGHT'
Required Columns   

Column Name	Validation
INVOICE_ID	Populated from AP_INVOICES_INTERFACE.INVOICE_ID
INVOICE_LINE_ID	Populated from AP_INVOICE_LINES_INTERFACE_S.NEXTVAL
LINE_NUMBER	A unique number to the invoice
LINE_TYPE_LOOKUP_CODE	'FREIGHT'
AMOUNT
ACCOUNTING_DATE	Optional. Defaulted from INVOICE_DATE or SYSDATE
DIST_CODE_CONCATENATED or DIST_CODE_COMBINATION_ID	Optional. Defaulted from AP_SYSTEM_PARAMETERS. FREIGHT_CODE_COMBINATION_ID. If one of them is populated, then validated against   - 'Parent' must be 'No' for All the segments values  - DIST_CODE_CONCATENATED needs to meet the security rules  - DIST_CODE_COMBINATION_ID must be in SELECT code_combination_id   FROM gl_code_combinations  WHERE account_type = 'E'    AND enabled_flag = 'Y'    AND summary_flag = 'N'    AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE - 1)                    AND NVL (end_date_active, SYSDATE + 1)

information about the parameters related to “Payables Open Interface Import” Program. Info from R12.1.1 instance.

Mandatory Parameters:

1.       Source: It can be any of the below list

Ø      Invoice Gateway - Invoice records you entered in the Invoice Gateway window

Ø      E-Commerce Gateway- Supplier invoices transferred through EDI

Ø      Credit Card- Procurement cards transactions from the credit card issuer

Ø      Oracle Property Manager- Lease invoices from Oracle Property Manager.

Ø      Oracle Assets- Lease payments from Oracle Assets.

Ø      User-defined- For invoice loaded from legacy Systems

2.       Invoice Batch Name

Ø      Use Batch Control Payables Profile option need to be enabled to use this parameter

Ø      Payables groups the invoices created from the invoice records you import and creates an invoice batch with the batch name you enter

Ø      While Re-importing the error invoices make sure the batch name remains the same

Optional Parameters:

3.       Operating Unit: Filtering predicate when null import program process invoice from all operating units.

4.       Hold Name: When this parameter is provided with the hold name, then the import program creates the invoices and blocks them with mentioned hold.

5.       Hold Reason: Corresponds to Hold Name

6.       GL Date: This value is used as a GL date for the invoices whose GL_DATE columns in interface tables are not populated.

7.       Purge. Enter Yes if you want Payables to delete from the Payables Open Interface tables all successfully imported invoice records that match the Source and Group ID of this import

8.       Summarize Report: Provides a Summarize report when ‘YES’

 

Below insert script will insert the minimal information required for creating a invoice through Payables open interface import program.

Change the values used in the script as per the comments given. We have tested this script in R12.1.1 instance.

<< Inserting into AP_INVOICES_INTERFACE>>

INSERT

INTO ap_invoices_interface

  (

    invoice_id,

    invoice_type_lookup_code,

    invoice_date,

    vendor_id,

    vendor_site_id,

    invoice_amount,

    source,

    org_id

  )

  VALUES

  (

    AP_INVOICES_INTERFACE_S.NEXTVAL

   ,'STANDARD'

   ,SYSDATE --

   ,3003    --,

   ,106     --,

   ,100     --

   ,'TEST' -- It can be retrived by following query

                  --(select lookup_code

                          --from ap_lookup_codes

                          --where lookup_type='SOURCE')

   ,823

  );

 

<< Inserting into AP_INVOICE_LINES_INTERFACE>>

INSERT

INTO ap_invoice_lines_interface

  (

    invoice_id,

    invoice_line_id,

    line_number,

    line_type_lookup_code,

    amount,

    accounting_date,

    dist_code_combination_id

  )

  VALUES

  (

    AP_INVOICES_INTERFACE_S.CURRVAL

   ,AP_INVOICE_LINES_INTERFACE_S.NEXTVAL

   ,1        --,

   ,'ITEM'   --//

   ,100      -- sum of line_amount should always be equal to invoice_amount,

   ,SYSDATE

   ,'612860' --''

  );

COMMIT;

After inserting, run the “PAYABLES OPEN INTERFACE IMPORT” program to load the invoice.

Key Flex Field Structures & How to Retrieve Information about segments based on code combination id

How to fetch meanings of each segment related to a code combination id?

To fetch the details like description, meaning, id and value related to each segment based on code combination id.

Tables related to Flexi fields:

FND_ID_FLEXS
FND_ID_FLEX_SEGMENTS
FND_ID_FLEX_STRUCTURES
FND_FLEX_VALUES
FND_FLEX_VALUE_HIERARCHIES

Below are queries used by me to reach out the main query..

QUERY 1:

 The table (fnd_id_flexs) used in the below query stores registration information about key flexfields.
 Each row includes the four–character code that identifies the key flexfield and can be used to dig further
 The "id_flex_code" is the column which can be used to dig further


SELECT application_table_name
      ,DECODE (application_table_type,
               'S','Specific',
               'G','Generic'
              ) table_type
      ,concatenated_segs_view_name
      ,set_defining_column_name
      ,unique_id_column_name
      ,description
      ,id_flex_code  -- Used to drill down
FROM   fnd_id_flexs
WHERE id_flex_name LIKE 'Accounting%';



QUERY 2:

The below query gives information about the segment meanings
We got this data by using the value we got for "id_flex_code"  in query 1

SELECT segment_name
      ,segment_num
      ,enabled_flag
      ,required_flag
      ,flex_value_set_id  -- Used to drill down
      ,additional_where_clause
FROM  fnd_id_flex_segments
WHERE id_flex_code = 'GL#';


QUERY 3:
This query can be used to get various possible values for each segment.

SELECT ffv.flex_value_set_id
      ,ffv.flex_value_id
      ,ffv.flex_value
      ,ffv.enabled_flag
      ,ffvt.LANGUAGE
      ,ffvt.description
FROM   fnd_flex_values ffv
      ,fnd_flex_values_tl ffvt
WHERE  ffv.flex_value_id = ffvt.flex_value_id
AND    ffv.flex_value_set_id = '&id from query 2';


Main Query
 Below is our highlight and it can be used to get all possible information based on code combination id
 I had simplified the same. The decode statement differs from client to client. But it can be framed with use of query 2


SELECT fifs.segment_name
      ,fifs.segment_num
      ,ffv.flex_value
      ,ffvt.description
FROM  fnd_flex_values ffv
     ,fnd_flex_values_tl ffvt
     ,fnd_id_flex_segments fifs
     ,gl_code_combinations glv
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND   ffv.flex_value_set_id = fifs.flex_value_set_id
AND   fifs.id_flex_code = 'GL#'
AND   ffv.flex_value    = DECODE(fifs.segment_name
                                ,'BUSINESS UNIT',glv.segment1
                                ,'LOCATION'     ,glv.segment2
                                ,'DEPARTMENT'   ,glv.segment3
                                ,'ACCOUNT'      ,glv.segment4
                                ,'PROJECT'      ,glv.segment5
                                ,'INTERCOMPANY' ,glv.segment6
                                ,'SPARE'        ,glv.segment7
                                )
AND  glv.code_combination_id = '&code_combination_id'
ORDER BY ffv.flex_value_set_id;

Data Flow between Receiving Transactions (RCV) – Sub ledger Accounting (XLA) – General Ledger (GL)

Collecting the Receiving data related to PO
SELECT rrsl.rcv_sub_ledger_id -- Connects to SLA tables
      ,rrsl.rcv_transaction_id
      ,rrsl.je_source_name
      ,rrsl.code_combination_id
      ,rrsl.accounted_dr
      ,rrsl.accounted_cr
      ,rrsl.reference1
      ,rrsl.reference2   PO_HEADER_ID
      ,rrsl.reference3   PO_DISTRIBUTION_ID
      ,rt.transaction_id
      ,rt.transaction_type
      ,rt.po_header_id
      ,rt.po_release_id
      ,rt.po_line_id
      ,rt.po_line_location_id
      ,pd.code_combination_id
FROM   rcv_receiving_sub_ledger rrsl
      ,rcv_transactions rt
      ,po_distributions_all pd
WHERE pd.po_distribution_id = rt.PO_DISTRIBUTION_ID
AND   rt.transaction_id = rrsl.rcv_transaction_id
AND   rt.PO_DISTRIBUTION_ID = rrsl.reference3
AND   rt.po_header_id = 500;

Collecting the data from XLA
In R12, we have XLA tables that holds link between any sub ledger data to General ledger data.
The below query gives you the important columns available in the XLA tables and GL linking columns for the above RCV_SUB_LEDGER_ID

SELECT xal.gl_sl_link_id     -- Connects to GL tables
      ,xal.gl_sl_link_table  -- Connects to GL tables
      ,xdl.source_dIstribution_id_num_1 rcv_sub_ledger_id
      ,xdl.line_definition_code
      ,xdl.event_class_code
      ,xdl.event_type_code   
      ,xah.ae_header_id
      ,xah.event_id
      ,xah.entity_id
      ,xah.event_type_code
      ,xah.description
      ,xal.ae_line_num
      ,xal.code_combination_id
      ,xal.accounting_class_code    
FROM   xla_events               xev
      ,xla_ae_headers           xah
      ,xla_ae_lines             xal
      ,XLA_DISTRIBUTION_LINKS   xdl
WHERE xah.application_id    = xev.application_id
AND   xev.event_id          = xah.event_id
AND xal.application_id    = xah.application_id
AND xal.ae_header_id      = xah.ae_header_id
AND xev.application_id    = xdl.application_id
AND xal.ae_line_num       = xdl.ae_line_num
AND xah.ae_header_id      = xdl.ae_header_id
AND xdl.SOURCE_DISTRIBUTION_TYPE ='RCV_RECEIVING_SUB_LEDGER'
AND xdl.source_dIstribution_id_num_1   = 3002;

Collecting the data from GL
Below query collects the data from GL.
In the below query, you can notice the linking columns in the first five places and others were just important ones.

SELECT glimp.reference_5 ENTITY_ID
      ,glimp.reference_6 EVENT_ID
      ,glimp.reference_7 AE_HEADER_ID
      ,glimp.gl_sl_link_id
      ,glimp.gl_sl_link_table
      ,glb.je_batch_id
      ,glb.name
      ,glb.status
      ,glb.description
      ,glh.je_header_id
      ,glh.je_category
      ,glh.je_source
      ,glh.name
      ,glh.description
      ,glh.running_total_accounted_cr
      ,glh.running_total_accounted_dr
      ,gll.je_line_num
      ,gll.code_combination_id
FROM   gl_import_references glimp
      ,gl_je_batches glb
      ,gl_je_headers glh
      ,gl_je_lines   gll
WHERE  glimp.je_header_id     = glh.je_header_id
AND    glimp.je_line_num      = gll.je_line_num
AND    glimp.je_batch_id      = glb.je_batch_id
AND    glh.je_header_id       = gll.je_header_id
AND    glh.je_batch_id        = glb.je_batch_id
AND    glimp.gl_sl_link_id    = 245133        
AND    glimp.gl_sl_link_table = 'XLAJEL';

FND_USER_PKG.CREATEUSER ( Create Applications User via PLSQL)

Below script will be used for creating a Oracle Applications user via PLSQL. The api being used is FND_USER_PKG.

Script:

DECLARE
   p_user_name                    VARCHAR2 (200) := 'SYSCNTEST';
   p_owner                        VARCHAR2 (200) := NULL;
   p_unencrypted_password         VARCHAR2 (200) := 'oracle123';
   p_session_number               NUMBER         := USERENV ('sessionid');
   p_start_date                   DATE           := SYSDATE;
   p_end_date                     DATE           := NULL;
   p_last_logon_date              DATE           := NULL;
   p_description                  VARCHAR2 (200) := 'teamsearch';
   p_password_date                DATE           :=   SYSDATE
                                                    - 1;
   p_password_accesses_left       NUMBER         := 1000;
   p_password_lifespan_accesses   NUMBER         := 1000;
   p_password_lifespan_days       NUMBER         := 1000;
   p_employee_id                  NUMBER         := NULL;
   p_email_address                VARCHAR2 (200) := NULL;
   p_fax                          VARCHAR2 (200) := NULL;
   p_customer_id                  NUMBER         := NULL;
   p_supplier_id                  NUMBER         := NULL;
   v_user_id                      NUMBER;
BEGIN
   fnd_user_pkg.createuser (
      x_user_name=> p_user_name,
      x_owner=> p_owner,
      x_unencrypted_password=> p_unencrypted_password,
      x_session_number=> p_session_number,
      x_start_date=> p_start_date,
      x_end_date=> p_end_date,
      x_last_logon_date=> p_last_logon_date,
      x_description=> p_description,
      x_password_date=> p_password_date,
      x_password_accesses_left=> p_password_accesses_left,
      x_password_lifespan_accesses=> p_password_lifespan_accesses,
      x_password_lifespan_days=> p_password_lifespan_days,
      x_employee_id=> p_employee_id,
      x_email_address=> p_email_address,
      x_fax => p_fax,
      x_customer_id=> p_customer_id,
      x_supplier_id=> p_supplier_id
   );

   SELECT user_id
     INTO v_user_id
     FROM fnd_user
    WHERE user_name = p_user_name;

   DBMS_OUTPUT.put_line (   'User_id : '
                         || v_user_id);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (   'Error while creating a user: '
                            || SQLERRM);
END;

Procure To Pay Cycle Process

1. Create a Standard Purchase order.

2. Give Shipments

3. Give Distributions

4. Approve PO

5. See the status of the PO In The header level: It is approved.

6. Go to the ReceivingàReceipts

7. Give the sub inv and stock locator where you want store the goods. And Save.

8. View the request status and refresh If not Completed.

9. Run the Program Pay on receipt auto invoice.(Which Generate Invoice Automatically)

10. View the Status of the program and refresh if not completed.

11. Go to Payables: InvoiceàEntry àInvoice
Query with the Invoice Num ERS%

12. Validate the Invoice .After Validate Enable the creating Accounting check box.

13.See The Account

14. Enable the check box Pay in full. And Pres ok.

15. Select the Bank and Go to Actions

16. Enable the check box Create Accounting and press ok.

17. See the Account Created.


18. Now see the invoice status validated, accounted and amount paid.



19. Go to Cash Management: Bank Statementsà Manual clearing. àClear Transactions
Find the Bank Account.


20. Enable the Transaction and press Clear Transaction Button.


21. Go to Payables: PaymentàEntryàPayment
Query with Payment Date


22. Go to actions, enable Create Accounting and press ok.


23. See the Account created.

24. Run: Payables Transfer to General Ledger.


25.View the Request status and refers if not completed.


26.Got to GL:JournalàImportàRun



27.View the request status and refers if not completed.


28. Go to JournalsàEnter .Find with Source as Payables.

29.Now We can Post the journals.

Oracle Applications – Order Management Drop Shipment, Functional Setup and flow

E-commerce site like ebay, ubid.com and amazon.com which are very common place in advance ountries like USA, UK & Singapore where people can sell or buy there product. Have you ever think ,what is similar situation in real world , when the the word ‘drop shipment’ comes to your mind. ……a business situation when the retailer or trader has no stock himself, instead giving customer details directly to the seller, and then saler than fills the order and send it to customer directly.How its sounds…

In other words, Drop Shipment is a process where the customer places a purchase order on a company and this company instructs its supplier to directly ship the items to the customer. A Drop Shipment occurs when a customer order is sourced from and delivered by a supplier.Order Management sends information to the Purchasing Application to create that PO, and then when that PO is received (to indicate shipment from the supplier to your customer), the order line is automatically updated to indicate that it was fulfilled. In this process, the company running Order Management is modeled as the company to whom the end customer places the original order.
We need to make sure these are attribute setup Correctly:
Item Attributes
Purchased : Enabled
Purchasable : Enabled
Transactable : Enabled
Stockable : Optional
Reservable : Optional
Inventory Item : Optional
Customer Ordered : Enabled
Customer Order Enabled : Enabled
Internal Ordered : Disabled
OE Transactable : Enabled
Shippable : Optional
And We do set up for Order Source Type as External.
Drop Shipment Setup checklist
Ensure you have created your Order Management Transaction Types and linked your Transaction Types to order and line workflows that support drop shipments.
Ensure the Oracle Workflow Background Engine is running.
Ensure all Drop ship locations you will use to perform drop shipments have the Ship To Site and Receiving Site defined.
Ensure you have defined the Internal Ship To Locations for your drop shipment customers (Oracle Receivables Standard Customer window, Business Purpose Details Tab).
Ensure your standard items have an associated List Price defined within your PO Inventory organization (Oracle Payables Financial Options window, Supplier-Purchasing Tab).
Drop Shipment – Process Steps
Create a Sales Order with line where the line source is External
Book and Schedule the Sales Order
Run Requisition Import Process
Now the line status will be in Awaiting Receipt
Login to the Receiving Organization (Purchasing) who has been setup as an Approver
Run Requisition Import
Create Purchase Order from the Requisition
Approve the PO
Receive the full quantity
Run Auto Invoice
Verify Invoice in Sales Order
Check the details here .

Things not to forget in a Drop Shipment
Release 11i/12 does not support Drop Shipment across operating units.
Blanket PO’s will not used with Drop Shipment , the reason the PO must be created when OM notifies PO that a Drop Ship order has been created.
You can’t cancelled Drop Shipments once Oracle Purchasing obtains the receipt.
Standard Items can be used for Drop Shipment.
In 11i, PTO’s and ATO’s cannot be drop shipped
Difference between “Internal” and “External” Drop-Ship
In Oracle Context External Drop-Shipping means your Oracle Order Management uses purchase orders to outside suppliers that are automatically generated from sales orders for goods supplied directly from the supplier. The “external ” supplier ships the goods directly to the 3rd Party customer and confirms the shipment through the use of an Advanced Shipment Notice(ASN). Note:Oracle uses this ASN to record a receiving transaction into inventory followed by an immediate logical shipping transaction. From these transactions, conveyance of title takes place and the customer can be invoiced and the supplier’s invoice can be processed. where as “Internal” context Drop-Shipping functions in a similar fashion. The key difference is that no inventory transactions take place on the books of the selling operating unit; transfer of ownership of the goods from shipper to seller to customer with the only physical movement of the goods being out of the shipping organization.