ºÝºÝߣ

ºÝºÝߣShare a Scribd company logo

Interface

•Download as DOCX, PDF•
•
pranav kumar verma
pranav kumar verma

The document describes several interfaces for importing data into different Oracle applications from interface tables, including purchase orders, suppliers, invoices, journals, budgets, employees, items, item categories, receipts, and requisitions. Each interface specifies validation rules for the data, relevant interface and base tables, and the import program used.

1 of 12
Download to read offline
Purchase Order Interface/Conversion. Application. Purchasing PO. Validations. . Vendor name should not be null. . Vendor site code should not be null. . Organization should exists for ship to organization. . Address1 should not be null. . Bill to location should not be null and should be a valid one. . Ship to location should not be null and should be a valid one at both header and line level. . Charge account should be a valid one. Interface Tables. PO_HEADERS_INTERFACE. PO_LINES_INTERFACE. PO_DISTRIBUTIONS_INTERFACE. Base Tables. PO_HEADERS_ALL PO_LINES_ALL PO_DISTRIBUTIONS_ALL Import Program Name. Purchase Order Open Interface. Suppliers/Vendors Interface/Conversion. Application.
Payables AP. Validations. . Vendor Name should not be null. . Vendor Type lookup code should be a valid value. . Type 1099 should be a vaild value. . Org type lookup code should be a valid value. . Vendor site code should not be null. . If pay iste flag is not null then it should be either Y or N. . If Hold all Payments flag is not null then it should be either Y or N. . If exclusive payment falg is not null then it should be either Y or N. . If Tax reporting site flag is not null then it should be eeither Y or N. . FOB lookup code should be a valid one. . Terms name should be a valid one. . Address line1 should not be null. . Payment method lookup code should be a valid value. Interface Tables. AP_SUPPLIERS_INT. AP_SUPPLIER_SITES_INT. AP_SUPPLIER_CONTACTS_INT. Base Tables. AP_SUPPLIERS_ALL AP_SUPPLIER_SITES_ALL AP_SUPPLIER_CONTACTS_ALL Import Program Name. Suppliers open intereface import. Supplier Sites open intereface import. Supplier Contact open intereface import.
Customer Interface oracle financials – receivables - customers ra_customers_interface_all ra_customer_profiles_int_all ra_contact_phones_int_all ra_customer_banks_int_all ra_cust_pay_method_int_all hz_party_interface Payables Open Interface Application. Payables Validation Header :- INVOICE_ID (Required) : Unique identifier for this invoice within this batch. Same value should be populated in invoice’s lines in the AP_INVOICE_LINES_INTERFACE table to identify the data as belonging to the same invoice. INVOICE_NUM (Required) : Enter the invoice number that needs to be assigned to the invoice created in Payables from this record. INVOICE_TYPE_LOOKUP_CODE (Optional) : Type of invoice: Credit or Standard. INVOICE DATE (Optional) : Date of the invoice. If you do not enter a value, the system uses the date you submit Payables Open Interface Import as the invoice date. PO_NUMBER (Optional) : Purchase order number from PO_HEADERS.SEGMENT1. This column needs to be populated if invoice to be matched with an purchase order. VENDOR_ID & VENDOR_SITE_ID (Required) : VENDOR_ID is unique identifier for a supplier and VENDOR_SITE_ID is Internal supplier site identifier. Supplier of the invoice to be derived by value in one of the following columns in this table: VENDOR_ID, VENDOR_NUM, VENDOR_NAME, VENDOR_SITE_ID or PO_NUMBER. VENDOR_NUM & VENDOR_NAME (Optional) : Supplier number and name. You must identify
the supplier by entering a value for one of the following columns in this table: VENDOR_ID, VENDOR_NUM, VENDOR_SITE_ID, VENDOR_SITE CODE, or PO_NUMBER. INVOICE_AMOUNT (Required) : Amount of the invoice. INVOICE_CURRENCY_CODE (Optional) : Currency code for the invoice. If you want to createforeign currency invoices, enter a currency code that is different from your functional currency. EXCHANGE_RATE (Optional) : This column is required if you enter a foreign currency code in the INVOICE_CURRENCY_CODE column and you enter User as the EXCHANGE_RATE_TYPE. TERMS_ID (Optional) : Internal identifier for the payment terms. DESCRIPTION (Optional) : Enter the description that you want to assign to the invoice created from this record. SOURCE (Required) : Source of the invoice data. If you import EDI invoices from the Oracle EDI Gateway, the source is EDI Gateway. For invoices you import using SQL*Loader, use a QuickCode with the type Source that you have defined in the QuickCodes window in Payables. Line :- INVOICE_ID (Required) :Enter the INVOICE_ID of the corresponding invoice in the AP_INVOICES_INTERFACE table. INVOICE_LINE_ID : This value is not required. You can enter a unique number for each invoice line of an invoice. LINE_NUMBER (Optional) : You can enter a unique number to identify the line. LINE_TYPE_LOOKUP_CODE (Required) : Enter the lookup code for the type of invoice distribution that you want Payables Open Interface Import to create from this record. The code you enter must be ITEM, TAX, MISCELLANEOUS, or FREIGHT. These lookup codes are stored in the AP_LOOKUP_CODES table. AMOUNT (Required) : The invoice distribution amount. If you are matching to a purchase order, the AMOUNT = QUANTITY_INVOICED x UNIT PRICE. If the total amount of all the invoice distributions does not equal the amount of the invoice that has the same INVOICE_ID, then Payables Open Interface Import will reject the invoice. Interface Tables AP_INVOICES_INTERFACE AP_INVOICE_LINES_INTERFACE Base Tables
AP_INVOICES_ALL AP_INVOICE_DISTRIBUTIONS_ALL AP_PAYMENT_SCHEDULES_ALL Error table: AP_INTERFACE_REJECTIONS AP_INTERFACE_CONTROLS Import Program Name. Payables Open Interface Import Parameter :- Source: Choose the source of the invoices from the list of values. Use EDI Gateway, Credit Card, or a Source type QuickCode you defined in the Payables QuickCodes window. Group: To limit the import to invoices with a particular Group ID, enter the Group ID. The Group must exactly match the GROUP_ID in the Payables Open Interface tables. Batch Name: Payables groups the invoices created from the invoices you import and creates an invoice batch with the batch name you enter. You can enter a batch name only if you have enabled the Use Batch Control Payables option, and if you have enabled the Use Batch Control Payables option, you must enter a batch name. If you use a batch name and some invoices are rejected during the import process, you can import the corrected invoices into the same batch if you enter the exact batch name during the subsequent import. Hold Name: If you want to place all invoices on hold at the time of import, enter an Invoice Hold Reason. You can define your own hold reasons in the Invoice Approvals window. Hold Reason: Payables displays the Invoice Hold Reason Description. GL Date: If you want to assign a specific GL Date to all invoices, enter a GL Date. If you do not enter a value here, the system will assign a GL Date based on the GL Date Payables option. Purge: Enter Yes if you want Payables to delete all successfully imported invoice records that match the Source and Group ID of this import. Payables does not delete any invoice data for which it has not yet created invoices. If you want to purge later, you can use the Payables Open Interface Purge Program.
GL Journals Interface/Conversion. Application. General Ledger SQLGL. Validations. . Actual flag should be A or B and not null. . Accounting date should not be null. . Currency Code should not be null . Entered Cr Or Entered Dr. One of these should not be null and the other should be null. . Code combination id should be valid and should not be null. . Period should be opened. Interface Tables. GL_INTERFACE. Base Tables. GL_JE_HEADERS GL_JE_LINES GL_JE_BATCHES Import Program Name. Program - Import Journals.
GL Budgets Interface/Conversion. Application. General Ledger SQLGL. Validations. . Actual flag should be B and not null. . Budget name should not be null and should be a valid one. . Budget Entity name should not be null and should be a valid one. . Currency Code should not be null and should be a valid one. . Code combination id should be valid and should not be null. . Period should be opened and should not be null. Interface Tables. GL_BUDGET_INTERFACE. Base Tables. GL_BALANCES Import Program Name. Check the responsibility which has the access to budgets and navigate to. Navigation: Budgets ---> Enter ---> Upload.
Employee Interface/Conversion. Application. Human Resources HRMS. Validations. . Business Group should exist. . Ledger should exist. . Last Name of an Employee should not be null . Hire Date should not be null. . Gender Must be either M or F for Employee. Interface Tables. We do not have interface table for employee. Base Tables. PER_ALL_PEOPLE_F API i.e. Application Program Interface. Create Employee: hr_employee_api.create_employee Assign Supervisor: hr_assignment_api.update_emp_asg Assign Job: hr_assignment_api.update_emp_asg_criteria Re Hire Employee: hr_employee_api.re_hire_ex_employee Update Employee: hr_person_api.update_person Terminate Employee: hr_ex_employee_api.actual_termination_emp hr_ex_employee_api.final_process_emp
Items Interface/Conversion. Application. Inventory INV. Validations. . COGS accounts should be populated. . Item type should be a valid one. . Item number should not be bull. . Org should not be null and should be defined. . Item description should not be null. . UOM unit of measurement should not be null. . Primary UOM code should not be bull. . Check item is already created for the same organization. . Item Catalog should not be null and should be a valid one. . Item Template should not be null and should be a valid one. . If planner code provided check if it is a valid one. Interface Tables. MTL_SYSTEM_ITEMS_INTERFACE. MTL_ITEM_REVISIONS_INTERFACE. EGO_ITM_USR_ATTR_INTRFC. MTL_INTERFACE_ERRORS; For error information. Base Tables. MTL_SYSTEM_ITEMS_B MTL_ITEM_REVISIONS Import Program Name. Import Items; For Items. Import Item Catalogs; For SKU Items.
Item Categories Interface/Conversion. Application. Inventory INV. Validations. . Item number should not be null. . Item should have already created. . Org should not be null and should be defined. . Item category should not be null and should be a valid one. . Category list should not be null and should be a valid one.. . Transaction type should be CREATE/UPDATE. Interface Tables. MTL_ITEM_CATEGORIES_INTERFACE. MTL_INTERFACE_ERRORS; For error information. Base Tables. MTL_ITEM_CATEGORIES_B Import Program Name. Item Category Assignment Open Interface.
Receipts Interface/Conversion Application. Account Receivables AR. Validations. . Receipt Number should not be null. . Receipt amount should not be null. . Customer should be available. . Receipt Date should not be null. . Invoice number should not be null. . Receipt amount and total amount of all invoices related to a particular receipt should match. . Invoice amount should not be null. . Check if receipt is already applied to the invoice. . Receipt Apply date should not less than the Transaction Date. . Transaction date should exist for the invoice. Interface Tables. We do not have interface table for Receipts. Base Tables. AR_CASH_RECEIPTS_ALL API i.e. Application Program Interface. Create Receipts: ar_receipt_api_pub.create_cash Apply Amount: ar_receipt_api_pub.apply Apply on Account: ar_receipt_api_pub.apply_on_account
Requisitions Interface/Conversion. Application. Purchasing PO. Validations. . Requisition Type should not be null. . Item Type should not be null. . Item category should not be null. . Item description should not be null. . UOM should not be null and should be a valid one. . Quantity should not be null and should not be negative or 0. . Item Should exist in the system. . Supplier should not be null. . Ship to locaton should not be null. Interface Tables. PO_REQUISITIONS_INTERFACE_ALL. PO_REQ_DIST_INTERFACE_ALL. Base Tables. PO_REQUISITION_HEADERS_ALL PO_REQUISITION_LINES_ALL PO_REQ_DISTRIBUTIONS_ALL Import Program Name. Requisition Import.

More Related Content

Interface

  • 1. Purchase Order Interface/Conversion. Application. Purchasing PO. Validations. . Vendor name should not be null. . Vendor site code should not be null. . Organization should exists for ship to organization. . Address1 should not be null. . Bill to location should not be null and should be a valid one. . Ship to location should not be null and should be a valid one at both header and line level. . Charge account should be a valid one. Interface Tables. PO_HEADERS_INTERFACE. PO_LINES_INTERFACE. PO_DISTRIBUTIONS_INTERFACE. Base Tables. PO_HEADERS_ALL PO_LINES_ALL PO_DISTRIBUTIONS_ALL Import Program Name. Purchase Order Open Interface. Suppliers/Vendors Interface/Conversion. Application.
  • 2. Payables AP. Validations. . Vendor Name should not be null. . Vendor Type lookup code should be a valid value. . Type 1099 should be a vaild value. . Org type lookup code should be a valid value. . Vendor site code should not be null. . If pay iste flag is not null then it should be either Y or N. . If Hold all Payments flag is not null then it should be either Y or N. . If exclusive payment falg is not null then it should be either Y or N. . If Tax reporting site flag is not null then it should be eeither Y or N. . FOB lookup code should be a valid one. . Terms name should be a valid one. . Address line1 should not be null. . Payment method lookup code should be a valid value. Interface Tables. AP_SUPPLIERS_INT. AP_SUPPLIER_SITES_INT. AP_SUPPLIER_CONTACTS_INT. Base Tables. AP_SUPPLIERS_ALL AP_SUPPLIER_SITES_ALL AP_SUPPLIER_CONTACTS_ALL Import Program Name. Suppliers open intereface import. Supplier Sites open intereface import. Supplier Contact open intereface import.
  • 3. Customer Interface oracle financials – receivables - customers ra_customers_interface_all ra_customer_profiles_int_all ra_contact_phones_int_all ra_customer_banks_int_all ra_cust_pay_method_int_all hz_party_interface Payables Open Interface Application. Payables Validation Header :- INVOICE_ID (Required) : Unique identifier for this invoice within this batch. Same value should be populated in invoice’s lines in the AP_INVOICE_LINES_INTERFACE table to identify the data as belonging to the same invoice. INVOICE_NUM (Required) : Enter the invoice number that needs to be assigned to the invoice created in Payables from this record. INVOICE_TYPE_LOOKUP_CODE (Optional) : Type of invoice: Credit or Standard. INVOICE DATE (Optional) : Date of the invoice. If you do not enter a value, the system uses the date you submit Payables Open Interface Import as the invoice date. PO_NUMBER (Optional) : Purchase order number from PO_HEADERS.SEGMENT1. This column needs to be populated if invoice to be matched with an purchase order. VENDOR_ID & VENDOR_SITE_ID (Required) : VENDOR_ID is unique identifier for a supplier and VENDOR_SITE_ID is Internal supplier site identifier. Supplier of the invoice to be derived by value in one of the following columns in this table: VENDOR_ID, VENDOR_NUM, VENDOR_NAME, VENDOR_SITE_ID or PO_NUMBER. VENDOR_NUM & VENDOR_NAME (Optional) : Supplier number and name. You must identify
  • 4. the supplier by entering a value for one of the following columns in this table: VENDOR_ID, VENDOR_NUM, VENDOR_SITE_ID, VENDOR_SITE CODE, or PO_NUMBER. INVOICE_AMOUNT (Required) : Amount of the invoice. INVOICE_CURRENCY_CODE (Optional) : Currency code for the invoice. If you want to createforeign currency invoices, enter a currency code that is different from your functional currency. EXCHANGE_RATE (Optional) : This column is required if you enter a foreign currency code in the INVOICE_CURRENCY_CODE column and you enter User as the EXCHANGE_RATE_TYPE. TERMS_ID (Optional) : Internal identifier for the payment terms. DESCRIPTION (Optional) : Enter the description that you want to assign to the invoice created from this record. SOURCE (Required) : Source of the invoice data. If you import EDI invoices from the Oracle EDI Gateway, the source is EDI Gateway. For invoices you import using SQL*Loader, use a QuickCode with the type Source that you have defined in the QuickCodes window in Payables. Line :- INVOICE_ID (Required) :Enter the INVOICE_ID of the corresponding invoice in the AP_INVOICES_INTERFACE table. INVOICE_LINE_ID : This value is not required. You can enter a unique number for each invoice line of an invoice. LINE_NUMBER (Optional) : You can enter a unique number to identify the line. LINE_TYPE_LOOKUP_CODE (Required) : Enter the lookup code for the type of invoice distribution that you want Payables Open Interface Import to create from this record. The code you enter must be ITEM, TAX, MISCELLANEOUS, or FREIGHT. These lookup codes are stored in the AP_LOOKUP_CODES table. AMOUNT (Required) : The invoice distribution amount. If you are matching to a purchase order, the AMOUNT = QUANTITY_INVOICED x UNIT PRICE. If the total amount of all the invoice distributions does not equal the amount of the invoice that has the same INVOICE_ID, then Payables Open Interface Import will reject the invoice. Interface Tables AP_INVOICES_INTERFACE AP_INVOICE_LINES_INTERFACE Base Tables
  • 5. AP_INVOICES_ALL AP_INVOICE_DISTRIBUTIONS_ALL AP_PAYMENT_SCHEDULES_ALL Error table: AP_INTERFACE_REJECTIONS AP_INTERFACE_CONTROLS Import Program Name. Payables Open Interface Import Parameter :- Source: Choose the source of the invoices from the list of values. Use EDI Gateway, Credit Card, or a Source type QuickCode you defined in the Payables QuickCodes window. Group: To limit the import to invoices with a particular Group ID, enter the Group ID. The Group must exactly match the GROUP_ID in the Payables Open Interface tables. Batch Name: Payables groups the invoices created from the invoices you import and creates an invoice batch with the batch name you enter. You can enter a batch name only if you have enabled the Use Batch Control Payables option, and if you have enabled the Use Batch Control Payables option, you must enter a batch name. If you use a batch name and some invoices are rejected during the import process, you can import the corrected invoices into the same batch if you enter the exact batch name during the subsequent import. Hold Name: If you want to place all invoices on hold at the time of import, enter an Invoice Hold Reason. You can define your own hold reasons in the Invoice Approvals window. Hold Reason: Payables displays the Invoice Hold Reason Description. GL Date: If you want to assign a specific GL Date to all invoices, enter a GL Date. If you do not enter a value here, the system will assign a GL Date based on the GL Date Payables option. Purge: Enter Yes if you want Payables to delete all successfully imported invoice records that match the Source and Group ID of this import. Payables does not delete any invoice data for which it has not yet created invoices. If you want to purge later, you can use the Payables Open Interface Purge Program.
  • 6. GL Journals Interface/Conversion. Application. General Ledger SQLGL. Validations. . Actual flag should be A or B and not null. . Accounting date should not be null. . Currency Code should not be null . Entered Cr Or Entered Dr. One of these should not be null and the other should be null. . Code combination id should be valid and should not be null. . Period should be opened. Interface Tables. GL_INTERFACE. Base Tables. GL_JE_HEADERS GL_JE_LINES GL_JE_BATCHES Import Program Name. Program - Import Journals.
  • 7. GL Budgets Interface/Conversion. Application. General Ledger SQLGL. Validations. . Actual flag should be B and not null. . Budget name should not be null and should be a valid one. . Budget Entity name should not be null and should be a valid one. . Currency Code should not be null and should be a valid one. . Code combination id should be valid and should not be null. . Period should be opened and should not be null. Interface Tables. GL_BUDGET_INTERFACE. Base Tables. GL_BALANCES Import Program Name. Check the responsibility which has the access to budgets and navigate to. Navigation: Budgets ---> Enter ---> Upload.
  • 8. Employee Interface/Conversion. Application. Human Resources HRMS. Validations. . Business Group should exist. . Ledger should exist. . Last Name of an Employee should not be null . Hire Date should not be null. . Gender Must be either M or F for Employee. Interface Tables. We do not have interface table for employee. Base Tables. PER_ALL_PEOPLE_F API i.e. Application Program Interface. Create Employee: hr_employee_api.create_employee Assign Supervisor: hr_assignment_api.update_emp_asg Assign Job: hr_assignment_api.update_emp_asg_criteria Re Hire Employee: hr_employee_api.re_hire_ex_employee Update Employee: hr_person_api.update_person Terminate Employee: hr_ex_employee_api.actual_termination_emp hr_ex_employee_api.final_process_emp
  • 9. Items Interface/Conversion. Application. Inventory INV. Validations. . COGS accounts should be populated. . Item type should be a valid one. . Item number should not be bull. . Org should not be null and should be defined. . Item description should not be null. . UOM unit of measurement should not be null. . Primary UOM code should not be bull. . Check item is already created for the same organization. . Item Catalog should not be null and should be a valid one. . Item Template should not be null and should be a valid one. . If planner code provided check if it is a valid one. Interface Tables. MTL_SYSTEM_ITEMS_INTERFACE. MTL_ITEM_REVISIONS_INTERFACE. EGO_ITM_USR_ATTR_INTRFC. MTL_INTERFACE_ERRORS; For error information. Base Tables. MTL_SYSTEM_ITEMS_B MTL_ITEM_REVISIONS Import Program Name. Import Items; For Items. Import Item Catalogs; For SKU Items.
  • 10. Item Categories Interface/Conversion. Application. Inventory INV. Validations. . Item number should not be null. . Item should have already created. . Org should not be null and should be defined. . Item category should not be null and should be a valid one. . Category list should not be null and should be a valid one.. . Transaction type should be CREATE/UPDATE. Interface Tables. MTL_ITEM_CATEGORIES_INTERFACE. MTL_INTERFACE_ERRORS; For error information. Base Tables. MTL_ITEM_CATEGORIES_B Import Program Name. Item Category Assignment Open Interface.
  • 11. Receipts Interface/Conversion Application. Account Receivables AR. Validations. . Receipt Number should not be null. . Receipt amount should not be null. . Customer should be available. . Receipt Date should not be null. . Invoice number should not be null. . Receipt amount and total amount of all invoices related to a particular receipt should match. . Invoice amount should not be null. . Check if receipt is already applied to the invoice. . Receipt Apply date should not less than the Transaction Date. . Transaction date should exist for the invoice. Interface Tables. We do not have interface table for Receipts. Base Tables. AR_CASH_RECEIPTS_ALL API i.e. Application Program Interface. Create Receipts: ar_receipt_api_pub.create_cash Apply Amount: ar_receipt_api_pub.apply Apply on Account: ar_receipt_api_pub.apply_on_account
  • 12. Requisitions Interface/Conversion. Application. Purchasing PO. Validations. . Requisition Type should not be null. . Item Type should not be null. . Item category should not be null. . Item description should not be null. . UOM should not be null and should be a valid one. . Quantity should not be null and should not be negative or 0. . Item Should exist in the system. . Supplier should not be null. . Ship to locaton should not be null. Interface Tables. PO_REQUISITIONS_INTERFACE_ALL. PO_REQ_DIST_INTERFACE_ALL. Base Tables. PO_REQUISITION_HEADERS_ALL PO_REQUISITION_LINES_ALL PO_REQ_DISTRIBUTIONS_ALL Import Program Name. Requisition Import.