Account Payable in Oracle Apps


This Document provides the guidance to the user with the necessary information for creating Suppliers,Supplier Sites and Supplier Contacts through Single Insert in an accurate way.
The Interface tables used here are :
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT

AP_SUP_SITE_CONTACT_INT



Insertion steps
Create Suppliers :
Following are the steps that must be followed, in order, to perform the Single insertions for Creating Suppliers :
<><><><>
Execution Step
Description
Step 1
select user_id created_by from fnd_user where user_name = ‘SAYYADEVARA’
Step 2
The value of vendor_interface_id can be passed through sequence ap_suppliers_int_s.NEXTVAL.
Step 3

select * from
po_vendors
where vendor_name = 'TEST_VENDOR'
If no rows returned from the above query,take this vendor name and pass it to the interface table ap_suppliers_int.

Step 4
Vendor Type Look Up Code must be passed as Vendor.
Step 5
select term_id,name from
ap_terms

Any term_id and name can be taken from the above query and pass it to the interface table ap_suppliers_int.Here,we passed terms_id as 10112 and name as GR_NET15.
Step 6
Pass ‘Y’ for all the flags such as always_take_disc_flag,inspection_required_flag, receipt_required_flag, auto_tax_calc_flag, federal_reportable_flag and create_debit_memo_flag in the interface table ap_suppliers_int.
Step 7
Pass set_of_books_id as 1 and invoice_currency_code and payment_currency_code as ‘USD’ to the interface table ap_suppliers_int.
Step 8
The values payment_method_lookup_code and terms_date_basis must be passed as per the requirement.Here,we are passing payment_method_lookup_code as ‘CHECK’ and terms_date_basis as ‘Invoice’ to the interface table ap_suppliers_int.
Step 9
Pass qty_rcv_tolerance as 0,
qty_rcv_exception_code as ‘WARNING’
enforce_ship_to_location_code as ‘NONE’
and receipt_days_exception_code as ‘WARNING’ to the interface table ap_suppliers_int.
Step 10
The values pay_date_basis_lookup_code and payment_priority must be passed as per the requirement.Here,we are passing pay_date_basis_lookup_code as ‘DISCOUNT ’and payment_priority
as 99 to the interface table ap_suppliers_int.
Step 11
Pass the Who columns created_by, last_updated_by as user_id.
and creation_date,last_update_date and the value for start_date_active can be passed as SYSDATE.
The remaining leftout values in the interface table ap_suppliers_int can be passed as NULL.
Step 12
To insert the data into the suppliers interface table

INSERT INTO ap_suppliers_int
(vendor_interface_id, last_update_date,
last_updated_by, vendor_name,
creation_date, created_by,
vendor_type_lookup_code,
terms_id,
terms_name,
set_of_books_id,
always_take_disc_flag,
pay_date_basis_lookup_code,
payment_priority,
invoice_currency_code,
payment_currency_code,
num_1099,
type_1099, start_date_active,
payment_method_lookup_code,
terms_date_basis,
inspection_required_flag,
receipt_required_flag,
qty_rcv_tolerance,
qty_rcv_exception_code,
enforce_ship_to_location_code,
receipt_days_exception_code,
auto_tax_calc_flag,
federal_reportable_flag,
match_option,
create_debit_memo_flag
)
VALUES (ap_suppliers_int_s.NEXTVAL, --vendor_interface_id
SYSDATE, --last_update_date
1007910, --last_updated_by
'TEST_VENDOR',--vendor_name
SYSDATE, --creation_date
1007910,--created_by
'VENDOR',--vendor_type_lookup_code
10112,--terms_id
'GR_NET15',--terms_name
1, --set_of_books_id
'Y', --always_take_disc_flag
'DISCOUNT',--pay_date_basis_lookup_code
99,--payment_priority
'USD',--invoice_currency_code
'USD',--payment_currency_code
NULL,--num_1099
NULL, --type_1099
SYSDATE,--start_date_active
'CHECK', --payment_method_lookup_code
'Invoice',--terms_date_basis
'Y',--inspection_required_flag
'Y', --receipt_required_flag
0, --qty_rcv_tolerance
'WARNING',--qty_rcv_exception_code
'NONE', --enforce_ship_to_location_code
'WARNING',--receipt_days_exception_code
'Y',--auto_tax_calc_flag
'Y',--federal_reportable_flag
NULL,--match_option
'Y'--create_debit_memo_flag
);



Step 13
Run Import Program for Suppliers through the following navigation :

Responsibility : Payables Manager
Navigation : Other -> Requests -> Run
Program Name : Supplier Open Interface Import


Step 15
Once the import program is successful, please check the following query in the interface table :

select * from
ap_suppliers_int
Where trunc(creation_date) = trunc(sysdate)

On successful import,the record remains in the interface table ap_suppliers_int itself with the status as ‘PROCESSED’.





Then, once we observe that the status is PROCESSED in the interface table ap_suppliers_int, query the following base table as follows :

select * from
po_vendors
Where trunc(creation_date) = trunc(sysdate)




Create Supplier Sites :
Following are the steps that must be followed, in order, to perform the Single insertions for Creating Supplier Sites for the above supplier :
<><><><>
Execution Step
Description
Step 1
select user_id created_by from fnd_user where user_name = ‘SAYYADEVARA’
Step 2
Take vendor_id from the po_vendors table which we get once the supplier is created.This can be taken from the following query and can be passed to the interface table ap_supplier_sites_int.

select vendor_id from
po_vendors
Where trunc(creation_date) = trunc(sysdate)



Step 3

Vendor_Site_Code must be passed in such a way that the value passed is unique.Here,we are passing it as ‘MAR-SANJOSE27’.
Note : Before passing the value for vendor_site_code,please check the values in po_vendor_sites_all whether the value being passed already exists.
Step 4
Address Values such as AddressLine1,AddressLine2 and AddressLine3 and Phone Number,FaxNumber etc..can be passed any.But see that atleast one of them is passed to the interface table ap_supplier_sites_int.
The values for city,state and postal code must be relevant to each other and must be valid.Please take the appropriate values from Vertex tables and pass them to the interface table ap_supplier_sites_int.
Step 5
The values for ship_to_location_id,ship_to_location_code and bill_to_location_id,bill_to_location_code can be passed on taking the location_id and location_code combination from hr_locations table.
Here,we have taken (ship_to_location_id,ship_to_location_code) -> (20494, CSP10121)
and (bill_to_location_id,bill_to_location_code) -> (20549, CSP10140) to the interface table ap_supplier_sites_int.
Step 6
The value for freight_terms_lookup_code must be passed as per the requirement.Here,we are passing it as ‘Due’. The value payment_method_lookup_code must be passed as per the requirement.Here,we are passing payment_method_lookup_code as ‘CHECK’ to the interface table ap_supplier_sites_int.
Step 7
The value for pay_group_lookup_code must be passed from any of the values extracted from the following query :

select lookup_code from
po_lookup_codes
where lookup_type = 'PAY GROUP'
Here,we are considering DOMESTIC.
Step 8
The values pay_date_basis_lookup_code and payment_priority must be passed as per the requirement.Here,we are passing pay_date_basis_lookup_code as ‘DISCOUNT ’and payment_priority
as 99 to the interface table ap_supplier_sites_int.
Step 9
select term_id,name from
ap_terms

Any term_id and name can be taken from the above query and pass it to the interface table ap_supplier_sites_int.Here,we passed terms_id as 10112 and name as GR_NET15.
Step 10
The flags primary_pay_site_flag and auto_tax_calc_flag must be passed as ‘N’ and the flags always_take_disc_flag and create_debit_memo_flag must be passed as ‘Y’ to the interface table ap_supplier_sites_int.
Step 11
The value for supplier_notif_method must be passed as per the requirement.Here,we are passing it as EMAIL.

Step 12
The values for invoice_currency_code and payment_currency_code as ‘USD’ to the interface table ap_supplier_sites_int.
The values for fax and fax_area_code can be any.
Step 13
The value being passed for vendor_site_code_alt must be the vendor name which we give during create suppliers stage.
Step 14
The values being passed for org_id and operating_unit_name must be any of the values from the following query :
select organization_id,name from hr_operating_units
Here,we are considering org_id as 204 and operating_unit_name as Vision Operations.


Step 15
The who column creation_date must be passed as SYSDATE.
The remaining leftout values in the interface table ap_supplier_sites_int can be passed as NULL.
Step 16
-insert into the supplier sites table
INSERT INTO ap_supplier_sites_int
(vendor_id,
vendor_site_code,
purchasing_site_flag,
pay_site_flag,
address_line1,
address_line2,
address_line3,
city,
state,
zip,
country,
area_code,
phone,
ship_to_location_id,
ship_to_location_code,
bill_to_location_id,
bill_to_location_code,
--ship_via_lookup_code,
freight_terms_lookup_code,
fob_lookup_code,
fax,
fax_area_code,
payment_method_lookup_code,
accts_pay_code_combination_id,
prepay_code_combination_id,
pay_group_lookup_code,
payment_priority,
terms_name,
terms_id,
pay_date_basis_lookup_code,
always_take_disc_flag,
invoice_currency_code,
payment_currency_code,
tax_reporting_site_flag,
org_id,
operating_unit_name,
address_line4,
vendor_site_code_alt,
auto_tax_calc_flag,
match_option,
create_debit_memo_flag,
supplier_notif_method,
primary_pay_site_flag,
attribute13,
creation_date
)
VALUES (34925,--vendor_id
LTRIM (RTRIM ('MAR-SANJOSE27')),--vendor_site_code
LTRIM
(RTRIM ('Y')
), --purchasing_site_flag
LTRIM (RTRIM ('Y')),--pay_site_flag
LTRIM (RTRIM ('ABCDE NEW ADDR')),--address_line1
NULL,--address_line2
NULL,--address_line3
LTRIM (RTRIM ('SAN JOSE')),--city
LTRIM (RTRIM ('CA')),--state
LTRIM (RTRIM ('95126')),--zip
LTRIM (RTRIM ('US')),--country
LTRIM (RTRIM ('800')),--area_code
LTRIM (RTRIM (8002238565)),--phone
LTRIM (RTRIM (20494)),--ship_to_location_id
LTRIM (RTRIM ('CSP10121')),--ship_to_location_code
LTRIM (RTRIM (20549)),--bill_to_location_id
LTRIM (RTRIM ('CSP10140')),--bill_to_location_code
--LTRIM (RTRIM ('WAT')),
LTRIM
(RTRIM ('Due')
),--freight_terms_lookup_code
NULL,--fob_lookup_code
LTRIM (RTRIM (7708185548)),--fax
LTRIM (RTRIM ('770')),--fax_area_code
'CHECK',--payment_method_lookup_code
NULL,--accts_pay_code_combination_id
NULL,--prepay_code_combination_id
'DOMESTIC',--pay_group_lookup_code
99,--payment_priority
'GR_NET15',--terms_name
10112,--terms_id
'DISCOUNT',--pay_date_basis_lookup_code
'Y',--always_take_disc_flag
'USD',--invoice_currency_code
'USD',--payment_currency_code
NULL,--tax_reporting_site_flag
204,--org_id
'Vision Operations',--operating_unit_name
NULL,--address_line4
LTRIM (RTRIM ('TEST_VENDOR')),--vendor_site_code_alt
'N',--auto_tax_calc_flag
NULL,--match_option
'Y',--create_debit_memo_flag
'EMAIL',--supplier_notif_method
'N',--primary_pay_site_flag
NULL,--attribute13
SYSDATE --creation_date
);

Step 17
Run Import Program for Supplier Sites through the following navigation :

Responsibility : Payables Manager
Navigation : Other -> Requests -> Run
Program Name : Supplier Sites Open Interface Import

Step 18
Once the import program is successful, please check the following query in the interface table :

select * from
ap_supplier_sites_int
Where trunc(creation_date) = trunc(sysdate)

On successful import,the record remains in the interface table ap_supplier_sites_int itself with the status as ‘PROCESSED’.

Then, once we observe that the status is PROCESSED in the interface table ap_supplier_sites_int, query the following base table as follows :

select * from
po_vendor_sites_all
Where trunc(creation_date) = trunc(sysdate)






Creating Supplier Contacts :
Following are the steps that must be followed, in order, to perform the Single insertions for Creating Supplier Contacts :
<><><><>
Execution Step
Description
Step 1
select user_id created_by from fnd_user where user_name = ‘SAYYADEVARA’
Step 2
The values being passed for org_id and operating_unit_name must be any of the values from the following query :
select organization_id,name from hr_operating_units
Here,we are considering org_id as 204 and operating_unit_name as Vision Operations.
Step 3

The value for vendor_site_id must be passed on extracting the vendor_site_id for the supplier created during create supplier stage.It can be taken and passed to the interface table ap_sup_site_contact_int using the following query :

select vendor_site_id from
po_vendor_sites_all
Where trunc(creation_date) = trunc(sysdate)



Step 4
The values first_name and last_name must be passed to the interface table ap_sup_site_contact_int such that they are unique for that supplier.Check in the base table po_vendor_contacts with the value being passed for the first name and last name.

Step 5
The values for area_code,fax,phone,fax_area_code,email_address and url can be passed any.The who column creation_date must be passed as SYSDATE.
Step 6
insert into the supplier contacts table
INSERT INTO ap_sup_site_contact_int
(
vendor_site_id,
vendor_site_code,
first_name,
last_name,
area_code, phone,
org_id, fax,
fax_area_code, email_address,
url,
operating_unit_name, creation_date
)
VALUES (
5217,--vendor_site_id
'MAR-SANJOSE27',--vendor_site_code
'First',--first_name
'Second',--last_name
'800', --area_code
8002238565,--phone
204, --org_id
7708185548,--fax
'770', --fax_area_code
mailto:'n_jayakrishna@yahoo.com',--email_address,--email_address
'www.yahoo.com',--url
'Vision Operations', --operating_unit_name
SYSDATE --creation_date
);

Step 7
Run Import Program for Supplier Contacts through the following navigation :

Responsibility : Payables Manager
Navigation : Other -> Requests -> Run
Program Name : Supplier Site Contacts Open Interface Import

Step 8
Once the import program is successful, please check the following query in the interface table :

select * from
ap_sup_site_contact_int
Where trunc(creation_date) = trunc(sysdate)

On successful import,the record remains in the interface table ap_sup_site_contact_int itself with the status as ‘PROCESSED’.

Then, once we observe that the status is PROCESSED in the interface table ap_sup_site_contact_int, query the following base table as follows :

select * from
po_vendor_contacts
Where trunc(creation_date) = trunc(sysdate)

Supplier information can be checked from front-end through the following navigation :

Responsibility : Payables Manager
Navigation : Suppliers -> Entry
Query with the Supplier or Vendor Created.
Here,We can query with TEST_VENDOR.