Query to get failed records in Invoices Interface in Oracle APPS R12

SELECT aif.invoice_id

              ,ailf.invoice_line_id

  ,aif.invoice_num

  ,air.reject_lookup_code

  ,aif.invoice_amount

  ,ailf.amount line_amount

  ,ailf.line_number

  ,aif.source

  ,ailf.accounting_date

  ,aif.gl_date

  ,aif.invoice_date

  ,aif.creation_date

  ,(SELECT vendor_name

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_name

  ,(SELECT end_date_active

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_end_date_active

  ,(SELECT last_update_date

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_last_update_date

  ,(SELECT segment1

                  FROM po_headers_all

                 WHERE po_header_id = ailf.po_header_id) po_number

  ,(SELECT name

                  FROM hr_operating_units

                 WHERE organization_id = aif.org_id) operating_unit

          FROM ap_invoices_interface aif

      ,ap_interface_rejections air

  ,ap_invoice_lines_interface ailf

         WHERE aif.invoice_id = ailf.invoice_id

           AND ailf.invoice_line_id = air.parent_id

           -- AND    aif.group_id        =v_group_id

           AND air.parent_table = 'AP_INVOICE_LINES_INTERFACE'

   --AND aif.source = 'ERS'

           AND aif.status = 'REJECTED'

        UNION

        SELECT aif.invoice_id

      ,ailf.invoice_line_id

  ,aif.invoice_num

  ,air.reject_lookup_code

  ,aif.invoice_amount

  ,ailf.amount line_amount

  ,ailf.line_number

  ,aif.source

  ,ailf.accounting_date

  ,aif.gl_date

  ,aif.invoice_date

  ,aif.creation_date

  ,(SELECT vendor_name

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_name

  ,(SELECT end_date_active

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_end_date_active

  ,(SELECT last_update_date

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_last_update_date

  ,(SELECT segment1

                  FROM po_headers_all

                 WHERE po_header_id = ailf.po_header_id) po_number

  ,(SELECT name

                  FROM hr_operating_units

                 WHERE organization_id = aif.org_id) operating_unit

          FROM ap_invoices_interface aif

      ,ap_interface_rejections air

  ,ap_invoice_lines_interface ailf

         WHERE aif.invoice_id = ailf.invoice_id

           AND aif.invoice_id = air.parent_id

           -- AND    aif.group_id        =v_group_id

           AND air.parent_table = 'AP_INVOICES_INTERFACE'

           --AND aif.source = 'ERS'

   AND aif.status = 'REJECTED'

Pricing Data Bulk Loader in Oracle APPS

The pricing data bulk loader imports data from the following interface tables:

• QP_INTERFACE_LIST_HEADERS
• QP_INTERFACE_LIST_LINES
• QP_INTERFACE_QUALIFIERS 
• QP_INTERFACE_PRICING_ATTRIBS 
• QP_INTERFACE_ERRORS

QP_INTERFACE_LIST_HEADERS: This table captures the Price List header data.

QP_INTERFACE_LIST_LINES: Price list line data is captured in this interface table.

QP_INTERFACE_QUALIFIERS: This table contains the header qualifiers associated with the price lists to be imported. 

QP_INTERFACE_PRICING_ATTRIBS: The product and pricing attributes data is captured in this table. 

Import Standard Purchase Order Program or Import Price Catalog Failed in Oracle APPS

Below solution is from Oracle Support Doc ID 1632117.1

Caution: Before doing any changes please validate it from Oracle Support.

1)  Verify the executable method in the below PDOI concurrent programs:

     Import Standard Purchase Orders
     Import Price Catalogs
     Import Contract Purchase Agreements

     Steps:
     1. Navigate to System Administrator -> Concurrent -> Program -> Define
     2. Query for the program for example 'Import Standard Purchase Orders'
     3. Check the Executable method defined for the program. If it is 'SQL*Plus' then select the LOV beside Executable 'POXPDOI' and reselect the executable as'POXPDOI'.
     4. Confirm that the executable method has been changed as 'PL/SQL Procedure'
     5. Do the same for the other programs.

2)  Check whether the attribute 'MULTI_ORG_CATEGORY' is set to 'S' in all the below concurrent programs:

     Import Standard Purchase Orders
     Import Price Catalogs
     Import Contract Purchase Agreements

     Steps:
     1. Navigate to System Administration > Concurrent > Programs
     2. Query for the Program for example 'Import Standard Purchase Orders'
     3. Click on Update pencil icon
     4. Click on Request tab
     5. For field Operating Unit Mode - The value should be 'Single'
     6. Do the same for the other programs.

3)  Test and run the program import standard purchase orders. Program will get completed successfully and should not get any error

      Ex:  'Routine fdpsql cannot read the file
             /u16/oracle/XX/apps/apps_st/appl/po/12.0.0/sql/PO_PDOI_GRP.start_process
 

Customer Interface failed with B5 Interface_Status in oracle apps

B5 means PRIMARY_SITE_USE_FLAG is mandatory when inserting an address.

Running the Customer Interface throwing "racina: ORA-00001: unique constraint (AR.HZ_LOCATIONS_PK) violated and racina: Error inserting addresses." Errors

When trying to Import customers using Customer Interface (RACUST) and receive the following error:

ORA-00001 running Customer Interface

racina: ORA-00001: unique constraint (AR.HZ_LOCATIONS_PK) violated

racina: Error inserting addresses.

The Customer Interface process is attempting to insert an existing location_id in the HZ_LOCATIONS table.

Running the Customer Interface throwing "racina: ORA-00001: unique constraint(AR.HZ_PARTY_SITES_U2) violated" error:

 When you run "Customer Interface" program by populating ra_customers_interface_all and ra_customers_interface_all interface tables throwing "racina: ORA-00001: unique constraint(AR.HZ_PARTY_SITES_U2) violated". This error is due to the next site number generated using HZ_PARTY_SITE_NUMBER_S is less than the max (party_site_number) that is already generated in system.

Please check party_site_number and exists take action accordingly.

FA Module Interface Tables and Base Tables

 

AP Module Interface Tables and Base Tables

INV Module Interface Tables and Base Tables

OM Module Interface Tables and Base Tables

AR Module Interface Tables and Base Tables

GL Module Interface tables and Base Tables.