Apr 16, 2013

Required fields for Payables Open Interface Import


 
Overview of Required fields for Payables Open Interface Import Records
 
 
=================================================================
TEST CASE FOR SIMPLE INVOICES - NOT PO MATCHED OR PROJECT RELATED 
=================================================================
 
  AP_INVOICE_INTERFACE
  ====================
 
    Required Columns
    ================
    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
    ================
    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)  
 
 
 
  AP_INVOICE_LINES_INTERFACE
  ==========================
 
    Required Columns for LINE_TYPE_LOOKUP_CODE = 'ITEM'
    ===================================================
    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))
 
    Required Columns for LINE_TYPE_LOOKUP_CODE = 'TAX'
    ===================================================
    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                  ('TAX')
    TAX_CODE or TAX_CODE_ID                (Validated against AP_TAX_CODES_ALL)
    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))
 
    Required Columns for LINE_TYPE_LOOKUP_CODE = 'FREIGHT'
    ===================================================
    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))
 
 
                                                                                            
=================================
TEST CASE FOR PO MATCHED INVOICES 
=================================
 
  AP_INVOICE_INTERFACE
  ====================
 
    Required Columns
    ================
    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.
                                            Validated against PO_HEADERS)
    INVOICE_AMOUNT                         (Positive Amount)
    SOURCE                                 (Must be in select lookup_code from ap_lookup_codes where lookup_type='SOURCE')
    ORG_ID                                 (Required in Multi-Org Environment. Validated against AP_SYSTEM_PARAMETERS_ALL.ORG_ID)
 
    Optional Columns
    ================
    INVOICE_DATE                           (Defaulted to SYSDATE)
    INVOICE_TYPE_LOOKUP_CODE               (Defaulted to 'STANDARD')
    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)  
 
 
  AP_INVOICE_LINES_INTERFACE
  ==========================
 
    Required Columns for PO Matched Lines
    =====================================
    INVOICE_ID                             (Populated from AP_INVOICES_INTERFACE.INVOICE_ID)
    INVOICE_LINE_ID                        (Populated from AP_INVOICE_LINES_INTERFACE_S.NEXTVAL)
    LINE_NUMBER                            (A unqiue number to the invoice)
    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 for PO Matched Lines
    =====================================
    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)
 
                                                                                    
======================================
TEST CASE FOR PROJECT RELATED INVOICES
======================================
 
  AP_INVOICE_INTERFACE
  ====================
 
    Required Columns
    ================
    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)
    ORG_ID                                 (Required in Multi-Org Environment. Validated against AP_SYSTEM_PARAMETERS_ALL.ORG_ID)
    SOURCE                                 (Must be in select lookup_code from ap_lookup_codes where lookup_type='SOURCE')
 
    Optional Columns
    ================
    INVOICE_DATE                           (Defaulted to SYSDATE)
    INVOICE_TYPE_LOOKUP_CODE               (Defaulted to 'STANDARD')
    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)  
 
 
  AP_INVOICE_LINES_INTERFACE
  ==========================
 
    Required Columns for project related lines
    ==========================================
    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                                 
    PROJECT_ID                             (Validated against PA_PROJECTS_ALL.PROJECT_ID)
    TASK_ID                                (Validated against PA_TASKS.TASK_ID)
    EXPENDITURE_TYPE                       (Validated against PA_EXPENDITURE_TYPES.EXPENDITURE_TYPE)
    EXPENDITURE_ITEM_DATE                  (Needs to be between the task start date and end date)
    EXPENDITURE_ORGANIZATION_ID            (Validated against PA_EXP_ORGS_IT.ORGANIZATION_ID)
    PA_QUANTITY                              
 
 
    Optional Columns for project related lines
    ==========================================
    ACCOUNTING_DATE                        (Defaulted from Invoice Date or SYSDATE)
    PA_ADDITION_FLAG                       ('Y' if the distribution has been transferred into Oracle Projects)

No comments:

OraApps Search

Custom Search

Search This Blog