Oracle Applications – Find responsibilities associated with the users – using sql

Following sql will help you find the responsibilities associated with the users in oracle applications.

SELECT frt.RESPONSIBILITY_NAME, furg.end_date
FROM
         fnd_user_resp_groups furg,
         FND_RESPONSIBILITY fr,
         fnd_responsibility_tl frt,
         fnd_user fu
WHERE fu.user_name = ‘&&username’
AND   fu.user_id = furg.user_id
AND   furg.responsibility_id = fr.RESPONSIBILITY_ID
AND   frt.responsibility_id = fr.RESPONSIBILITY_ID
ORDER BY 1

How To Add Responsibility to USER using pl/sql? – Oracle Applications

Using the pl/sql you can add responsibility to any USER without having Application System Administrator Rights.
First, get value for ‘Responsablity_Application_Short_Name’ and ‘Responsibility_Key’ Parameters you need to run following sql Statement by Using APPS User Name

SELECT FAV.APPLICATION_SHORT_NAME, FAV.APPLICATION_NAME,FRV.RESPONSIBILITY_KEY, FRV.RESPONSIBILITY_NAME
FROM FND_APPLICATION_VL FAV, FND_RESPONSIBILITY_VL FRV
WHERE FRV.APPLICATION_ID=FAV.APPLICATION_ID
ORDER BY FRV.RESPONSIBILITY_NAME

To add Responsibility of “Receivables Manager” to User Jiltin

BEGIN
        fnd_user_pkg.addresp (‘JILTIN’,‘AR’,‘RECEIVABLES_MANAGER’,‘STANDARD’,‘Add Responsibility to USER using pl/sql’,SYSDATE,SYSDATE + 100);
        COMMIT;
        DBMS_OUTPUT.put_line (‘Responsibility Added Successfully’);
EXCEPTION
        WHEN OTHERS
        THEN
        DBMS_OUTPUT.put_line (   ‘ Responsibility is not added due to ‘” SQLCODE” SUBSTR (SQLERRM, 1, 100));
        ROLLBACK;
END;

Oracle Group By ROLLUP

It would be safe to assume that most if not all of us are familiar with Oracle "Group By" clause. You can however use the ROLLUP function to enhance the grouping actions performed within your queries.
Lets take a look at a fairly complex Group By example and then re-write it using the Group By ROLLUP.
For example, if you want to print the monthly total sales for each region, you would probably execute the following query:

SELECT reg.region,
   TO_CHAR(TO_DATE(ord.month, 'MM'), 'Month') month, SUM(ord.total_sales)
FROM orders ord, region reg
WHERE ord.region_id = reg.region_id
GROUP BY reg.region, ord.month;

REGION               MONTH     SUM(ORD.TOTAL_SALES)

-------------------- --------- ----------------

New England          January            1527645

New England          February           1847238

New England          March              1699449

New England          April              1792866

New England          May                1698855

New England          June               1510062

New England          July               1678002

New England          August             1642968

New England          September          1726767

New England          October            1648944

New England          November           1384185

New England          December           1599942

Mid-Atlantic         January            1832091

Mid-Atlantic         February           1286028

Mid-Atlantic         March              1911093

Mid-Atlantic         April              1623438

Mid-Atlantic         May                1778805

Mid-Atlantic         June               1504455

Mid-Atlantic         July               1820742

Mid-Atlantic         August             1381560

Mid-Atlantic         September          1178694

Mid-Atlantic         October            1530351

Mid-Atlantic         November           1598667

Mid-Atlantic         December           1477374

As expected, this report prints the total sales for each region and month combination. However, in a more complex application, you may also want to have the subtotal for each region over all months, along with the total for all regions, or you may want the subtotal for each month over all regions, along with the total for all months. In short, you may need to generate subtotals and totals at more than one level. Lets see how you go about doing this without using ROLLUP.

SELECT reg.region,
   TO_CHAR(TO_DATE(ord.month, 'MM'), 'Month') month, SUM(ord.total_sales)
FROM orders ord, region reg
WHERE ord.region_id = reg.region_id
GROUP BY reg.region, ord.month;

UNION ALL

SELECT reg.region,  NULL, SUM(ord.total_sales)
FROM orders ord, region reg
WHERE ord.region_id = reg.region_id
GROUP BY reg.region

UNION ALL

SELECT NULL, NULL, SUM(ord.total_sales)
FROM orders ord, region reg
WHERE ord.region_id = reg.region_id;

REGION               MONTH     SUM(ORD.TOT_SALES)

-------------------- --------- ----------------

New England          January            1527645

New England          February           1847238

New England          March              1699449

New England          April              1792866

New England          May                1698855

New England          June               1510062

New England          July               1678002

New England          August             1642968

New England          September          1726767

New England          October            1648944

New England          November           1384185

New England          December           1599942

Mid-Atlantic         January            1832091

Mid-Atlantic         February           1286028

Mid-Atlantic         March              1911093

Mid-Atlantic         April              1623438

Mid-Atlantic         May                1778805

Mid-Atlantic         June               1504455

Mid-Atlantic         July               1820742

Mid-Atlantic         August             1381560

Mid-Atlantic         September          1178694

Mid-Atlantic         October            1530351

Mid-Atlantic         November           1598667

Mid-Atlantic         December           1477374

Southeast US         January            1137063

Southeast US         February           1855269

Southeast US         March              1967979

Southeast US         April              1830051

Southeast US         May                1983282

Southeast US         June               1705716

Southeast US         July               1670976

Southeast US         August             1436295

Southeast US         September          1905633

Southeast US         October            1610523

Southeast US         November           1661598

Southeast US         December           1841100

Mid-Atlantic                           18923298

New England                            19756923

Southeast US                           20605485

                                       59285706



40 rows selected.

The 1st 36 rows list the sales for each month for every region. The last 3 rows however list the total per region as well as the sum for all sales. Lets see how we can accomplish the same results using Group By ROLLUP:

Syntax:
SELECT  . . .
FROM  . . .
GROUP BY ROLLUP (ordered list of grouping COLUMNS);

SELECT reg.region,
   TO_CHAR(TO_DATE(ord.month, 'MM'), 'Month') month, SUM(ord.total_sales)
FROM orders ord, region reg
WHERE ord.region_id = reg.region_id
GROUP BY ROLLUP (reg.region, ord.month);

REGION               MONTH     SUM(ORD.TOT_SALES)

-------------------- --------- ----------------

New England          January            1527645

New England          February           1847238

New England          March              1699449

New England          April              1792866

New England          May                1698855

New England          June               1510062

New England          July               1678002

New England          August             1642968

New England          September          1726767

New England          October            1648944

New England          November           1384185

New England          December           1599942

New England                            19756923

Mid-Atlantic         January            1832091

Mid-Atlantic         February           1286028

Mid-Atlantic         March              1911093

Mid-Atlantic         April              1623438

Mid-Atlantic         May                1778805

Mid-Atlantic         June               1504455

Mid-Atlantic         July               1820742

Mid-Atlantic         August             1381560

Mid-Atlantic         September          1178694

Mid-Atlantic         October            1530351

Mid-Atlantic         November           1598667

Mid-Atlantic         December           1477374

Mid-Atlantic                           18923298

Southeast US         January            1137063

Southeast US         February           1855269

Southeast US         March              1967979

Southeast US         April              1830051

Southeast US         May                1983282

Southeast US         June               1705716

Southeast US         July               1670976

Southeast US         August             1436295

Southeast US         September          1905633

Southeast US         October            1610523

Southeast US         November           1661598

Southeast US         December           1841100

Southeast US                           20605485

                                       59285706



40 rows selected.

Same results we got using only Group By but with a lot less code and of course a lot lower SQL cost.
If you want to generate subtotals for each month instead of for each region, all you need to do is change the order of columns in the ROLLUP operation.

GROUP BY ROLLUP (ord.month, reg.region); instead of GROUP BY ROLLUP (reg.region, ord.month);

Oracle Applications – A Simple Way

The below example explains a few of the important terms and concepts used in the Oracle E-Business Suite. This would be a good starting point for the beginners to better understand the concepts behind Oracle Applications.

Say Harry is the owner of a wholesale fruit shop. He buys various fruits like apples, oranges, mangos and grapes etc from farmers directly and sells them to retail shop owners and also to the direct customers.

The farmers are referred to as VENDORS/SUPPLIERS in Oracle Applications. Harry keeps track of all his vendors’ information like addresses, bank account and the amount he owes to them for the fruits that he bought etc, in a book named PAYABLES.

Harry gets an order from a retail shop owner of Fruit Mart, for a shipment of 11 bags of apples, 25 bags of oranges and 32 kgs of grapes. In Oracle Apps, bags and kgs are referred to as UOM (unit of measure), Fruit Mart is called CUSTOMER and the order is referred to as SALES ORDER. Harry maintains a book called ORDER MANAGEMENT where he writes down all the details of the SALES ORDERS that he gets from his customers.

Say the fruits have been shipped to the customer Fruit Mart. Harry now sends him the details like cost of each bag/fruit, the total amount that the customer has to pay etc on a piece of paper which is called INVOICE / TRANSACTION. Once the INVOICE has been sent over, the customer then validates this against the actual quantity of fruits that he received and will process the payments accordingly. The invoice amount could be paid as a single amount or could be paid in installments. Harry’s customer, Fruit Mart pays him in installments (partial payments). So Harry has to make a note of the details like date received, amount received, amount remaining, amount received for what goods/shipments/invoice etc, when Harry receives the payments. This detail is called RECEIPT, which will be compared to the invoice by Harry to find how much Fruit Mart has paid to him and how much has to be paid yet. This information is maintained in a book named RECEIVABLES to keep track of all the customers, their addresses (to ship the items), what and how much he has shipped to his customers and the amount his customers owe him etc.

Harry’s fruit business has begun to improve and has attracted more and more customers. As a result, Harry decided to buy a cold storage unit where he could stock more fruits. In Apps, this cold storage unit is known as WAREHOUSE and all the fruits are referred to as INVENTORY.  Due to increase in customers, Harry needs to hire more people to help him out in his business without any hiccups. These workers are called EMPLOYEES. At the end of every month, Harry pays the salary for all his employees through Checks. These checks are nothing but PAYROLL in Apps.

At the end of every month, Harry prepares a balance sheet in a book called GENERAL LEDGER to determine how much profit/loss he got and keeps track of the money going out and going in.

As the business grows, it becomes impossible to record everything on a paper. To make everybody’s life easier, we have very good tools in the market, which help the business men to keep track of everything. One such tool is Oracle E-Business Suite.

Oracle Applications is not a single application, but is a collection of integrated applications. Each application is referred to as a module and has it own functionality trying to serve a business purpose.

Few of the modules are Purchasing, Accounts Payables, Accounts Receivables, Inventory, Order Management, Human Resources, General Ledger, Fixed Assets etc.

Here is a high level business use of various modules:

Oracle Purchasing handles all the requisitions and purchase orders to the vendors.

Oracle Accounts Payables handles all the payments to the vendors.

Oracle Inventory deals with the items you maintain in stock, warehouse etc.

Order Management helps you collect all the information that your customers order.

Oracle Receivables help you collect the money for the orders that are delivered to the customers.

Oracle Human Resources helps maintain the Employee information, helps run paychecks etc. 

Oracle General Ledger receives information from all the different transaction modules or sub ledgers and summarizes them in order to help you create profit and loss statements, reports for paying Taxes etc. For Example: when you pay your employees that payment is reported back to General Ledgers as cost i.e money going out, when you purchase inventory items and the information is transferred to GL as money going out, and so is the case when you pay your vendors. Similarly when you receive items into your inventory, it is transferred to GL as money coming in, when your customer sends payment, it is transferred to GL as money coming in. So all the different transaction modules report to GL (General Ledger) as either “money going in” or “money going out”, the net result will tell you if you are making a profit or loss.

All the equipment, shops, warehouses, computers can be termed as ASSETS and they are managed by Oracle Fixed Assets.

There is a lot more in Oracle applications. This is the very basic explanation just to give an idea of the flow in ERP for the beginners.

1. Invoice
2. Receipt
3. Customer
4. Vendor
5. Buyer
6. Supplier
7. Purchase Order
8. Requisition
9. ACH: Account Clearance House

1. Sales Order
2. Pack Slip
3. Pick Slip
4. Drop Ship
5. Back Order
6. ASN: Advance Shipping Notice
7. ASBN: Advance Shipping Billing Notice
8. ATP: Available to Promise
9. Lot/Serial Number
10. DFF: Descriptive Flex Fields
11. KFF: Key Flex Fields
12. Value Sets
13. Organization
14. Business Unit
15. Multi Org
16. Folders
17. WHO Columns

1. Oracle Reports
2. Oracle Form
3. Workflow Builder
4. Toad
5. SQL Developer
6. SQL Navigator
7. Discoverer Reports
8. XML/BI Publisher
9. ADI: Application Desktop Integrator
10. Winscp
11. Putt

Oracle AR -Difference between PARTY and CUSTOMER

Difference between PARTY and CUSTOMER?

1. When we create New Customer in ERP, why a PARTY will be created automatically?

2. Can I create a Customer without Party?

3. Can I create a Party without Customer?

There is already a lot of material out there on this topic. There are a couple of articles on this, on oracle forums as well. I will try to explain this as elaborately as possible and not waste my “thanksgiving holiday”. I can get really pedantic at times, when im bored. Oracle as a company was basically into ERP solutions only. As part of its efforts and vision to branch out into every other application such as SCM, CRM and other domains, it started acquiring smaller companies. It acquired Siebel which was one of the industry leaders in the CRM domain. Just to provide some insight into CRM, it is an acronym for Customer Relationship Management. Any CRM is basically a marketing tool. If you understand the definition of the word “marketing”, you will easily understand the difference between a party and customer.
Marketing is the process of identifying, creating and retaining a customer. CRM tools essentially deal with that process. It is used by companies to identify potential customers, make them buy something from the company, maintain effective relationships with them to retain them for repeat purchases as well. This is what CRM does.
But an ERP like oracle does not have any relationship with a person unless he buys something from the company. A customer is someone who has a buying relationship with the company. So how do we distinguish between a person who has bought something and someone who hasnt purchased anything from you? This is where the party definition comes into picture. A party is anybody that exists. He/She/It does not have to have bought something from the company earlier. In fact, they do not have any relationship whatsoever with the company. So how does the company make them buy something? This is where CRM helps. It helps the company in identifying such people, create some marketing campaigns to make them buy something and convert them into the company’s customers, and keep maintaining relationships with them so that they will keep buying from them in the future as well.
Oracle purchased Siebel CRM to target these companies which require CRM tools to create, maintain and retain new customers. Prior to 11i, Oracle had the customer tables only. It did not have to deal with non-customers. Anyone who purchases becomes a customer and is stored in the ar customer tables. When Oracle bought CRM, it needed to incorporate the concept of a ‘potential”  and “repeat” customer into the existing data model. It could not have had separate schemas or databases for CRM tools and ERP tools. Since CRM and ERP have to talk to each other, they wanted one single model to incorporate customers who bought and people who have not purchased. So they created a layer over the customer definition called party and said “Anyone who exists is a party”. Once this party buys something, it becomes a customer. Kindly note that it is still a party. It is both a party and customer as well after the purchase is made. So for a person to become a customer, he has to exist first. Only then he can make the purchase and become a customer.
So the process would be like this. A company wants to create some new customers. So it first uses some CRM tools to identify new people it thinks can be targeted. It uses some CRM modules like marketing, telesales, telemarketing etc… to target the customer and finally convinces the customer to make the purchase. So, this person has entered the oracle database. Remember he has not bought anything yet. He is only a party for now. How does he buy now? CRM does not have any modules to place or process orders and payments. The party has to place an order in the ERP module Order management. Once he places an order, he becomes a customer too. So he is now both a party and customer. I hope this helps in understanding why some companies need CRM and ERP, and why Oracle bought siebel CRM. Oracle can target companies which deal with a lot of customers in its business like retailers (Walmart, Amazon, Target) and can sell them both CRM and ERP packages as part of a wholesome solution. since ERP and CRM are sold together, they need to communicate effectively and easily with each other and hence one single data model for customers called the “Party model”.
If you understood the above concept, you can easily answer the below questions.
1.When we create New Customer in ERP, why a PARTY will be created automatically?
A: Can a person make a purchase without existing? He has to exist first to make a purchase. He has to become a party first before becoming a customer. Once you place order in OM, the person is created as a party and customer as well. The party information can be used by CRM tools to target the person for future purchases.
2. Can I create a Customer without Party?
A: Can a person make a purchase without existing?
3.Can I create a Party without Customer?
A: Can a person exist independently without making a purchase? He can. So a party can exist without making a purchase. Imagine you went to a store, checked some stuff and did not buy anything. You might be asked to fill some information like your name, your address, what you liked,what you didnt like and things like that. So this information has to be stored somewhere. Here you will be entered in the company’s system as a party, but not a customer. they will then start sending you emails or offers trying to make you buy something using their CRM tools. once you buy something, you become a customer. Until you buy, you are only a party.

Purchase Order Interface

Interface Tables Used

1)PO_HEADERS_INTERFACE
2)PO_LINES_INTERFACE
3)PO_DISTRIBUTIONS_INTERFACE

INSERT INTO po_headers_interface
(interface_header_id
,batch_id
,action
,org_id
,document_type_code
,vendor_id
,vendor_site_code
,vendor_site_id
,vendor_doc_num
,currency_code
,agent_id
,terms_id
,approval_status
,ship_to_location_id
,effective_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,attribute_category
,attribute9 --xtra information
,comments
)
VALUES (po_headers_interface_s.NEXTVAL
,po_headers_interface_s.CURRVAL
,'ORIGINAL'
,g_org_id
,'STANDARD'
,rec_get_header_info.vendor_id
,rec_get_header_info.vendor_site_code
,rec_get_header_info.vendor_site_id
,po_headers_interface_s.CURRVAL
,'USD'
,l_agent_id
,rec_get_header_info.terms_id
,'APPROVED'
,rec_get_header_info.deliver_to_location_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'DS Fields'
,rec_get_header_info.attribute9 --xtra information
,l_comments
)

INSERT INTO po_lines_interface
(interface_header_id
,interface_line_id
,requisition_line_id
,line_num
,shipment_num
,line_type_id
,item
,item_description
,category_id
,unit_of_measure
,quantity
,unit_price
,ship_to_organization_id
,line_location_id
,effective_date
,need_by_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,taxable_flag
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.NEXTVAL
,rec_get_line_info.requisition_line_id
,l_line_num
,1
,rec_get_line_info.line_type_id
,rec_get_line_info.segment1
,rec_get_line_info.item_description
,rec_get_line_info.category_id
,rec_get_line_info.primary_unit_of_measure
,rec_get_line_info.quantity
,rec_get_line_info.unit_price
,rec_get_line_info.destination_organization_id
, po_line_locations_s.NEXTVAL
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (rec_get_line_info.need_by_date, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'Y'
);


INSERT INTO po.po_distributions_interface
(interface_header_id
,interface_line_id
,interface_distribution_id
,req_distribution_id
,org_id
,distribution_num
,quantity_ordered
,destination_organization_id
,destination_type_code
,destination_subinventory
, deliver_to_location_id
,set_of_books_id
,charge_account_id
,budget_account_id
,accrual_account_id
,variance_account_id
,deliver_to_person_id
,creation_date
,created_by
,last_update_date
,last_updated_by
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.CURRVAL
,po.po_distributions_interface_s.NEXTVAL
,rec_get_distrib_info.distribution_id
,g_org_id
,rec_get_distrib_info.distribution_num
,rec_get_distrib_info.req_line_quantity
,rec_get_distrib_info.destination_organization_id
,rec_get_distrib_info.destination_type_code
,l_destination_subinventory
, rec_get_line_info.deliver_to_location_id
,rec_get_distrib_info.set_of_books_id
,rec_get_distrib_info.code_combination_id
,rec_get_distrib_info.budget_account_id
,l_new_accrual_account_id
,rec_get_distrib_info.variance_account_id
,rec_get_line_info.to_person_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
);

Concurrent Program:
apps.fnd_request.submit_request (application => 'PO' --Application,
program => 'POXPOPDOI'--Program,
argument1 => ''--Buyer ID,
argument2 => 'STANDARD'--Document Type,
argument3 => ''--Document Subtype,
argument4 => 'N'--Process Items Flag,
argument5 => 'N'--Create Sourcing rule,
argument6 => ''--Approval Status,
argument7 => ''--Release Generation Method,
argument8 => ''--NULL,
argument9 => g_org_id--Operating Unit ID,
argument10 => ''--Global Agreement
);

Calling PO Approval workflow to approve POs

SELECT TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO v_wf_seq
FROM SYS.DUAL;

v_itemkey := TO_CHAR (l_header.po_header_id)
|| '-'
|| v_wf_seq;
po_reqapproval_init1.start_wf_process (itemtype => 'POAPPRV'
,itemkey => v_itemkey
,workflowprocess => 'POAPPRV_TOP'
,actionoriginatedfrom => 'PO_FORM'
,documentid => l_header.po_header_id
,documentnumber => l_header.segment1
,preparerid => l_header.agent_id
,documenttypecode => 'PO'
,documentsubtype => 'STANDARD'
,submitteraction => 'APPROVE'
,forwardtoid => NULL
,forwardfromid => l_header.agent_id
,defaultapprovalpathid => 10
,note => NULL
,printflag => 'N'
,faxflag => NULL
,faxnumber => NULL
);

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.