API - Create New Code Combination In GL

DECLARE
   CURSOR c1
   IS
      SELECT *
        FROM temp_gl_code;

   v_chart_of_accounts_id    VARCHAR2 (200);
   v_n_code_combination_id   VARCHAR2 (200);
BEGIN
   FOR i in c1
   LOOP
      SELECT gsob.chart_of_accounts_id      -- To Get the Chart of Accounts id
        INTO v_chart_of_accounts_id
        FROM gl_sets_of_books gsob
       WHERE gsob.set_of_books_id = fnd_profile.VALUE ('GL_SET_OF_BKS_ID');

      v_n_code_combination_id :=
         fnd_flex_ext.get_ccid ('SQLGL',
                                'GL#',
                                v_chart_of_accounts_id,
                                TO_CHAR (SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
                                i.CODE_COBIN
                               );

      IF v_n_code_combination_id <> 0
      THEN
         DBMS_OUTPUT.put_line ('success');
      ELSE
         DBMS_OUTPUT.put_line ('failure');
      END IF;
   END LOOP;
END;

Inventory out bound interface

CREATE OR REPLACE PROCEDURE xx_INV_Out1 (Errbuf       OUT varchar2,
                                         Retcode      OUT varchar2,
                                         f_id      IN     number,
                                         t_id      IN     varchar2)
AS
   CURSOR c1
   IS
      SELECT   msi.segment1 item,
               msi.inventory_item_id Itemid,
               msi.description itemdesc,
               msi.primary_uom_code Uom,
               ood.organization_name name,
               ood.organization_id id,
               mc.segment1 || ',' || mc.segment2 Category
        FROM   mtl_system_items_b msi,
               org_organization_definitions ood,
               mtl_item_categories mic,
               mtl_categories mc
       WHERE       msi.organization_id = ood.organization_id
               AND msi.inventory_item_id = mic.inventory_item_id
               AND msi.organization_id = mic.organization_id
               AND mic.category_id = mc.category_id
               AND msi.purchasing_item_flag = 'Y'
               AND msi.organization_id BETWEEN f_id AND t_id;

   x_id      UTL_FILE.file_type;
   l_count   number (5) DEFAULT 0 ;
BEGIN
   x_id :=
      UTL_FILE.fopen ('d:\oracle\proddb\8.1.7\plsql\temp',
                      'invoutdata.dat',
                      'W');

   --select * from v$parameter where name like '%utl_file%'
   FOR x1 IN c1
   LOOP
      l_count := l_count + 1;
      UTL_FILE.put_line (
         x_id,
            x1.item
         || '-'
         || x1.itemid
         || '-'
         || x1.itemdesc
         || '-'
         || x1.uom
         || '-'
         || x1.name
         || '-'
         || x1.id
         || '-'
         || x1.category
      );
   END LOOP;

   UTL_FILE.fclose (x_id);
   Fnd_file.Put_line (
      Fnd_file.output,
      'No of Records transfered to the data file :' || l_count
   );
   Fnd_File.Put_line (fnd_File.Output, ' ');
   Fnd_File.Put_line (
      fnd_File.Output,
      'Submitted User name  ' || Fnd_Profile.VALUE ('USERNAME')
   );
   Fnd_File.Put_line (fnd_File.Output, ' ');
   Fnd_File.Put_line (
      fnd_File.Output,
      'Submitted Responsibility name ' || Fnd_profile.VALUE ('RESP_NAME')
   );
   Fnd_File.Put_line (fnd_File.Output, ' ');
   Fnd_File.Put_line (fnd_File.Output, 'Submission Date :' || SYSDATE);
EXCEPTION
   WHEN UTL_FILE.invalid_operation
   THEN
      fnd_file.put_line (fnd_File.LOG, 'invalid operation');
      UTL_FILE.fclose_all;
   WHEN UTL_FILE.invalid_path
   THEN
      fnd_file.put_line (fnd_File.LOG, 'invalid path');
      UTL_FILE.fclose_all;
   WHEN UTL_FILE.invalid_mode
   THEN
      fnd_file.put_line (fnd_File.LOG, 'invalid mode');
      UTL_FILE.fclose_all;
   WHEN UTL_FILE.invalid_filehandle
   THEN
      fnd_file.put_line (fnd_File.LOG, 'invalid filehandle');
      UTL_FILE.fclose_all;
   WHEN UTL_FILE.read_error
   THEN
      fnd_file.put_line (fnd_File.LOG, 'read error');
      UTL_FILE.fclose_all;
   WHEN UTL_FILE.internal_error
   THEN
      fnd_file.put_line (fnd_File.LOG, 'internal error');
      UTL_FILE.fclose_all;
   WHEN OTHERS
   THEN
      fnd_file.put_line (fnd_File.LOG, 'other error');
      UTL_FILE.fclose_all;
END xx_INV_Out1;
/

Oracle GL Account Code Combination ID’s (CCID’s) through APIs

1] FND_FLEX_EXT.GET_COMBINATION_ID:

This API Finds combination_id for given set of key flexfield segment values. Segment values must be input in segments(1) – segments(n_segments) in the order displayed. 

It also creates a new combination if it is valid and the flexfield allows dynamic inserts and the combination does not already exist. It commit the transaction soon after calling this function since if a combination is created it will prevent other users creating similar combinations on any flexfield until a commit is issued.

It performs all checks on values including security and cross-validation. Value security rules will be checked for the current user identified in the FND_GLOBAL package. 

Generally pass in SYSDATE for validation date. If validation date is null, this function considers expired values valid and checks all cross-validation rules even if they are outdated.

This function returns TRUE if combination valid or FALSE and sets error message using FND_MESSAGE utility on error or if invalid. If this function returns FALSE, use GET_MESSAGE to get the text of the error message in the language of the database, or GET_ENCODED_MESSAGE to get the error message in a language-independent encoded format.
 
The Combination_id output may be NULL if combination is invalid.

/* Formatted on 3/27/2017 4:30:27 PM (QP5 v5.114.809.3010) */
SET serveroutput ON;

DECLARE
   l_application_short_name   VARCHAR2 (240);
   l_key_flex_code            VARCHAR2 (240);
   l_structure_num            NUMBER;
   l_validation_date          DATE;
   n_segments                 NUMBER;
   SEGMENTS                   APPS.FND_FLEX_EXT.SEGMENTARRAY;
   l_combination_id           NUMBER;
   l_data_set                 NUMBER;
   l_return                   BOOLEAN;
   l_message                  VARCHAR2 (240);
BEGIN
   l_application_short_name := 'SQLGL';
   l_key_flex_code := 'GL#';

   SELECT   id_flex_num
     INTO   l_structure_num
     FROM   apps.fnd_id_flex_structures
    WHERE   ID_FLEX_CODE = 'GL#'
            AND ID_FLEX_STRUCTURE_CODE = :ACCOUNTING_FLEXFIELD;

   l_validation_date := SYSDATE;
   n_segments := 6;
   segments (1) := '00101';
   segments (2) := '28506';
   segments (3) := '00000';
   segments (4) := '09063';
   segments (5) := '00000';
   segments (6) := '00000';
   l_data_set := NULL;

   l_return :=
      FND_FLEX_EXT.GET_COMBINATION_ID (
         application_short_name   => l_application_short_name,
         key_flex_code            => l_key_flex_code,
         structure_number         => l_structure_num,
         validation_date          => l_validation_date,
         n_segments               => n_segments,
         segments                 => segments,
         combination_id           => l_combination_id,
         data_set                 => l_data_set
      );
   l_message := FND_FLEX_EXT.GET_MESSAGE;

   IF l_return
   THEN
      DBMS_OUTPUT.PUT_LINE ('l_Return = TRUE');
      DBMS_OUTPUT.PUT_LINE ('COMBINATION_ID = ' || l_combination_id);
   ELSE
      DBMS_OUTPUT.PUT_LINE ('Error: ' || l_message);
   END IF;
END;

2] FND_FLEX_EXT.get_ccid:

This API gets combination id for the specified key flexfield segments.It is identical to get_combination_id() except this function takes segment values in a string concatenated by the segment  delimiter for this flexfield, and returns a positive combination id if valid or 0 on error.

3] FND_FLEX_KEYVAL.VALIDATE_SEGS:

These key flexfields server validations API are a low level interface to key flexfields validation.  They are designed to allow access to all the flexfields functionality, and to allow the user to get only the information they need in return.  Because of their generality, these functions are more difficult to use than those in the FND_FLEX_EXT package.  Oracle strongly suggests using the functions in FND_FLEX_EXT package if at all possible.
This function finds combination from given segment values.  Segments are passed in as a concatenated string in increasing order of segment_number (display order).
Various Operations that can be performed are:

• ‘FIND_COMBINATION’ – Combination must already exist.
• ‘CREATE_COMBINATION’ – Combination is created if doesn’t exist.
• ‘CREATE_COMB_NO_AT’ – same as create_combination but does not use an autonomous transaction.
• ‘CHECK_COMBINATION’ – Checks if combination valid, doesn’t create.
• ‘DEFAULT_COMBINATION’ – Returns minimal default combination.
• ‘CHECK_SEGMENTS’ – Validates segments individually.

If validation date is NULL checks all cross-validation rules. It returns TRUE if combination valid or FALSE and sets error message on server if invalid. Use the default values if you do not want any special functionality.

/* Formatted on 3/27/2017 4:31:51 PM (QP5 v5.114.809.3010) */
SET serveroutput ON;

DECLARE
   l_segment1            GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
   l_segment2            GL_CODE_COMBINATIONS.SEGMENT2%TYPE;
   l_segment3            GL_CODE_COMBINATIONS.SEGMENT3%TYPE;
   l_segment4            GL_CODE_COMBINATIONS.SEGMENT4%TYPE;
   l_segment5            GL_CODE_COMBINATIONS.SEGMENT5%TYPE;
   l_segment6            GL_CODE_COMBINATIONS.SEGMENT6%TYPE;
   l_valid_combination   BOOLEAN;
   l_cr_combination      BOOLEAN;
   l_ccid                GL_CODE_COMBINATIONS_KFV.code_combination_id%TYPE;
   l_structure_num       FND_ID_FLEX_STRUCTURES.ID_FLEX_NUM%TYPE;
   l_conc_segs           GL_CODE_COMBINATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;
   p_error_msg1          VARCHAR2 (240);
   p_error_msg2          VARCHAR2 (240);
BEGIN
   l_segment1 := '00101';
   l_segment2 := '28506';
   l_segment3 := '00000';
   l_segment4 := '14302';
   l_segment5 := '00455';
   l_segment6 := '00000';
   l_conc_segs :=
         l_segment1
      || '.'
      || l_segment2
      || '.'
      || l_segment3
      || '.'
      || l_segment4
      || '.'
      || l_segment5
      || '.'
      || l_segment6;
   BEGIN
      SELECT   id_flex_num
        INTO   l_structure_num
        FROM   apps.fnd_id_flex_structures
       WHERE   id_flex_code = 'GL#'
               AND id_flex_structure_code = 'EPC_GL_ACCOUNTING_FLEXFIELD';
   EXCEPTION
      WHEN OTHERS
      THEN
         l_structure_num := NULL;
   END;
   ---------------Check if CCID exits with the above Concatenated Segments---------------
   BEGIN
      SELECT   code_combination_id
        INTO   l_ccid
        FROM   apps.gl_code_combinations_kfv
       WHERE   concatenated_segments = l_conc_segs;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_ccid := NULL;
   END;

   IF l_ccid IS NOT NULL
   THEN
      ------------------------The CCID is Available----------------------
      DBMS_OUTPUT.PUT_LINE ('COMBINATION_ID= ' || l_ccid);
   ELSE
      DBMS_OUTPUT.PUT_LINE (
         'This is a New Combination. Validation Starts....'
      );
      ------------Validate the New Combination--------------------------
      l_valid_combination :=
         APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS (
            operation          => 'CHECK_COMBINATION',
            appl_short_name    => 'SQLGL',
            key_flex_code      => 'GL#',
            structure_number   => L_STRUCTURE_NUM,
            concat_segments    => L_CONC_SEGS
         );
      p_error_msg1 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

      IF l_valid_combination
      THEN
         DBMS_OUTPUT.PUT_LINE (
            'Validation Successful! Creating the Combination...'
         );
         -------------------Create the New CCID--------------------------

         L_CR_COMBINATION :=
            APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS (
               operation          => 'CREATE_COMBINATION',
               appl_short_name    => 'SQLGL',
               key_flex_code      => 'GL#',
               structure_number   => L_STRUCTURE_NUM,
               concat_segments    => L_CONC_SEGS
            );
         p_error_msg2 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

         IF l_cr_combination
         THEN
            -------------------Fetch the New CCID--------------------------
            SELECT   code_combination_id
              INTO   l_ccid
              FROM   apps.gl_code_combinations_kfv
             WHERE   concatenated_segments = l_conc_segs;

            DBMS_OUTPUT.PUT_LINE ('NEW COMBINATION_ID = ' || l_ccid);
         ELSE
            -------------Error in creating a combination-----------------
            DBMS_OUTPUT.PUT_LINE (
               'Error in creating the combination: ' || p_error_msg2
            );
         END IF;
      ELSE
         --------The segments in the account string are not defined in gl value set----------
         DBMS_OUTPUT.PUT_LINE (
            'Error in validating the combination: ' || p_error_msg1
         );
      END IF;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (SQLCODE || ' ' || SQLERRM);
END;

API to update and assign Project Roles in an Oracle Project

Oracle has provided a seeded package called PA_PROJECT_PARTIES_PUB to create, update or delete a project party (or Key member) in an oracle project. From front end, the navigation is Project Billing Super User (or related responsibility) > Projects > Find Projects > Open > Options > Key Members. The records in the form are displayed through a view (PA_PROJECT_PLAYERS) and the base table is PA_PROJECT_PARTIES.

PA_PROJECT_PARTIES_PUB.UPDATE_PROJECT_PARTY:

/* Formatted on 3/27/2017 2:43:17 PM (QP5 v5.114.809.3010) */
DECLARE
   l_project_id           PA_PROJECT_PARTIES.PROJECT_ID%TYPE := NULL;
   l_project_role         VARCHAR2 (240) := NULL;
   l_resource_name        PER_ALL_PEOPLE_F.FULL_NAME%TYPE := NULL;
   l_start_date_active    DATE := NULL;
   l_end_date_active      DATE := NULL;
   l_project_role_id      pa_project_role_types.PROJECT_ROLE_ID%TYPE := NULL;
   l_project_role_type    pa_project_role_types.PROJECT_ROLE_TYPE%TYPE := NULL;
   l_resource_source_id   PA_PROJECT_PARTIES.RESOURCE_SOURCE_ID%TYPE := NULL;
   l_project_party_id     PA_PROJECT_PARTIES.PROJECT_PARTY_ID%TYPE := NULL;
   l_object_id            PA_PROJECT_PARTIES.OBJECT_ID%TYPE := NULL;
   l_resource_id          PA_PROJECT_PARTIES.RESOURCE_ID%TYPE := NULL;
   l_record_version_number pa_project_parties.record_version_number%TYPE
         := NULL ;
   l_project_end_date     DATE;
   l_return_status        VARCHAR2 (20) := NULL;
   l_assignment_id        NUMBER := NULL;
   l_wf_type              VARCHAR2 (240) := NULL;
   l_wf_item_type         VARCHAR2 (240) := NULL;
   l_wf_process           VARCHAR2 (240) := NULL;
   l_msg_count            NUMBER := NULL;
   l_msg_data             VARCHAR2 (240) := NULL;
BEGIN
   ---Input Parameters----
   l_project_id := '7033';
   l_project_role := 'Project Manager';
   l_resource_name := 'Koch, Dibyajyoti';
   l_start_date_active := '24-NOV-2011';
   l_end_date_active := '24-NOV-2012';

   SELECT   PROJECT_ROLE_ID, PROJECT_ROLE_TYPE
     INTO   l_project_role_id, l_project_role_type
     FROM   PA_PROJECT_ROLE_TYPES
    WHERE   UPPER (MEANING) = UPPER (l_project_role);

   SELECT   DISTINCT PERSON_ID
     INTO   l_resource_source_id
     FROM   PER_ALL_PEOPLE_F
    WHERE   UPPER (FULL_NAME) = UPPER (l_resource_name);

   SELECT   PROJECT_PARTY_ID,
            OBJECT_ID,
            RESOURCE_ID,
            RECORD_VERSION_NUMBER
     INTO   l_project_party_id,
            l_object_id,
            l_resource_id,
            l_record_version_number
     FROM   PA_PROJECT_PARTIES
    WHERE       PROJECT_ID = l_project_id
            AND PROJECT_ROLE_ID = l_project_role_id
            AND RESOURCE_SOURCE_ID = l_resource_source_id;

   l_project_end_date :=
      pa_project_dates_utils.get_project_finish_date (l_project_id);

   PA_PROJECT_PARTIES_PUB.UPDATE_PROJECT_PARTY (
      P_API_VERSION                    => 1.0,
      P_INIT_MSG_LIST                  => FND_API.G_TRUE,
      P_COMMIT                         => FND_API.G_FALSE,
      P_VALIDATE_ONLY                  => FND_API.G_FALSE,
      P_VALIDATION_LEVEL               => FND_API.G_VALID_LEVEL_FULL,
      P_DEBUG_MODE                     => 'N',
      P_OBJECT_ID                      => l_object_id,
      P_OBJECT_TYPE                    => 'PA_PROJECTS',
      P_PROJECT_ROLE_ID                => l_project_role_id,
      P_PROJECT_ROLE_TYPE              => l_project_role_type,
      P_RESOURCE_TYPE_ID               => 101,                      --EMPLOYEE
      P_RESOURCE_SOURCE_ID             => l_resource_source_id,
      P_RESOURCE_NAME                  => l_resource_name,
      P_RESOURCE_ID                    => l_resource_id,
      P_START_DATE_ACTIVE              => l_start_date_active,
      P_SCHEDULED_FLAG                 => 'N',
      P_RECORD_VERSION_NUMBER          => l_record_version_number,
      P_CALLING_MODULE                 => FND_API.G_MISS_CHAR,
      P_PROJECT_ID                     => l_project_id,
      P_PROJECT_END_DATE               => l_project_end_date,
      P_PROJECT_PARTY_ID               => l_project_party_id,
      P_ASSIGNMENT_ID                  => NULL,
      P_ASSIGN_RECORD_VERSION_NUMBER   => l_record_version_number + 1,
      P_MGR_VALIDATION_TYPE            => 'FORM',
      P_END_DATE_ACTIVE                => l_end_date_active,
      X_ASSIGNMENT_ID                  => l_assignment_id,
      X_WF_TYPE                        => l_wf_type,
      X_WF_ITEM_TYPE                   => l_wf_item_type,
      X_WF_PROCESS                     => l_wf_process,
      X_RETURN_STATUS                  => l_return_status,
      X_MSG_COUNT                      => l_msg_count,
      x_msg_data                       => l_msg_data
   );
   COMMIT;
   DBMS_OUTPUT.PUT_LINE ('Status:' || l_return_status);
   DBMS_OUTPUT.PUT_LINE ('Message:' || l_msg_data);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Try Again!!');
END;

PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY:

/* Formatted on 3/27/2017 2:44:48 PM (QP5 v5.114.809.3010) */
DECLARE
   l_project_id           PA_PROJECT_PARTIES.PROJECT_ID%TYPE := NULL;
   l_project_role         VARCHAR2 (240) := NULL;
   l_resource_name        PER_ALL_PEOPLE_F.FULL_NAME%TYPE := NULL;
   l_start_date_active    DATE := NULL;
   l_end_date_active      DATE := NULL;
   l_project_role_id      pa_project_role_types.PROJECT_ROLE_ID%TYPE := NULL;
   l_project_role_type    pa_project_role_types.PROJECT_ROLE_TYPE%TYPE := NULL;
   l_resource_source_id   PA_PROJECT_PARTIES.RESOURCE_SOURCE_ID%TYPE := NULL;
   l_project_party_id     PA_PROJECT_PARTIES.PROJECT_PARTY_ID%TYPE := NULL;
   l_object_id            PA_PROJECT_PARTIES.OBJECT_ID%TYPE := NULL;
   l_resource_id          PA_PROJECT_PARTIES.RESOURCE_ID%TYPE := NULL;
   l_record_version_number pa_project_parties.record_version_number%TYPE
         := NULL ;
   l_project_end_date     DATE;
   l_return_status        VARCHAR2 (20) := NULL;
   l_assignment_id        NUMBER := NULL;
   l_wf_type              VARCHAR2 (240) := NULL;
   l_wf_item_type         VARCHAR2 (240) := NULL;
   l_wf_process           VARCHAR2 (240) := NULL;
   l_msg_count            NUMBER := NULL;
   l_msg_data             VARCHAR2 (240) := NULL;
BEGIN
   ---Input Parameters----
   l_project_id := '7033';
   l_project_role := 'Project Accountant';
   l_resource_name := 'Koch, Dibyajyoti';
   l_start_date_active := '24-NOV-2011';
   l_end_date_active := '24-NOV-2012';

   SELECT   PROJECT_ROLE_ID, PROJECT_ROLE_TYPE
     INTO   l_project_role_id, l_project_role_type
     FROM   PA_PROJECT_ROLE_TYPES
    WHERE   UPPER (MEANING) = UPPER (l_project_role);

   SELECT   DISTINCT PERSON_ID
     INTO   l_resource_source_id
     FROM   PER_ALL_PEOPLE_F
    WHERE   UPPER (FULL_NAME) = UPPER (l_resource_name);

   l_project_end_date :=
      pa_project_dates_utils.get_project_finish_date (l_project_id);

   PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY (
      P_API_VERSION           => 1.0,
      P_INIT_MSG_LIST         => FND_API.G_TRUE,
      P_COMMIT                => FND_API.G_FALSE,
      P_VALIDATE_ONLY         => FND_API.G_FALSE,
      P_VALIDATION_LEVEL      => FND_API.G_VALID_LEVEL_FULL,
      P_DEBUG_MODE            => 'N',
      P_OBJECT_ID             => l_project_id,
      P_OBJECT_TYPE           => 'PA_PROJECTS',
      P_PROJECT_ROLE_ID       => l_project_role_id,
      P_PROJECT_ROLE_TYPE     => l_project_role_type,
      P_RESOURCE_TYPE_ID      => 101,                               --EMPLOYEE
      P_RESOURCE_SOURCE_ID    => l_resource_source_id,
      P_RESOURCE_NAME         => l_resource_name,
      P_START_DATE_ACTIVE     => l_start_date_active,
      P_SCHEDULED_FLAG        => 'N',
      P_CALLING_MODULE        => NULL,
      P_PROJECT_ID            => l_project_id,
      P_PROJECT_END_DATE      => l_project_end_date,
      P_MGR_VALIDATION_TYPE   => 'FORM',
      P_END_DATE_ACTIVE       => l_end_date_active,
      X_PROJECT_PARTY_ID      => l_project_party_id,
      X_RESOURCE_ID           => l_resource_id,
      X_ASSIGNMENT_ID         => l_assignment_id,
      X_WF_TYPE               => l_wf_type,
      X_WF_ITEM_TYPE          => l_wf_item_type,
      X_WF_PROCESS            => l_wf_process,
      X_RETURN_STATUS         => l_return_status,
      X_MSG_COUNT             => l_msg_count,
      X_MSG_DATA              => l_msg_data
   );
   COMMIT;
   DBMS_OUTPUT.PUT_LINE ('Status:' || l_return_status);
   DBMS_OUTPUT.PUT_LINE ('Message:' || l_msg_data);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Try Again!!');
END;  

AP Invoice rejection query in oracle apps

/* Formatted on 11/15/2016 11:48:52 AM (QP5 v5.114.809.3010) */
SELECT   air.*
  FROM   ap_interface_rejections air,
         ap_invoice_lines_interface aili,
         ap_invoices_interface aii
 WHERE       aii.invoice_id = aili.invoice_id
         AND aili.invoice_line_id = air.parent_id
         AND INVOICE_NUM LIKE '%111424%'

API to cancel AP incoice in oracle apps

/* Formatted on 11/14/2016 3:39:40 PM (QP5 v5.114.809.3010) */
CREATE OR REPLACE PROCEDURE cancel_invoices (ip_operating_unit IN VARCHAR2)
AS
   l_resp_id                      NUMBER;
   l_appl_id                      NUMBER;
   l_user_id                      NUMBER := apps.fnd_global.user_id;
   l_org_id                       NUMBER := apps.fnd_global.org_id;
   l_message_name                 VARCHAR2 (1000);
   l_invoice_amount               NUMBER;
   l_base_amount                  NUMBER;
   l_tax_amount                   NUMBER;
   l_temp_cancelled_amount        NUMBER;
   l_cancelled_by                 VARCHAR2 (1000);
   l_cancelled_amount             NUMBER;
   l_cancelled_date               DATE;
   l_last_update_date             DATE;
   l_original_prepayment_amount   NUMBER;
   l_pay_curr_invoice_amount      NUMBER;
   l_token                        VARCHAR2 (100);
   l_boolean                      BOOLEAN;
   err_msg                        VARCHAR2 (2000);

   CURSOR invoice_cur
   IS
      SELECT   aia.invoice_id,
               aia.last_updated_by,
               aia.last_update_login,
               aia.gl_date,
               aia.invoice_num
        FROM   xx_ap_invoices_conv_stg a,
               ap_invoices_all aia,
               ap_invoice_lines_all aila
       WHERE       a.ls_inv_num = aia.invoice_num
               AND a.ls_org_id = aia.org_id
               AND aia.invoice_id = aila.invoice_id
               AND aia.org_id = aila.org_id
               AND aia.payment_status_flag = 'N'
               AND NVL (aila.cancelled_flag, 'N') <> 'Y';
--- AND aia.invoice_num = '65431';
BEGIN
   BEGIN
      SELECT   DISTINCT fr.responsibility_id, frx.application_id
        INTO   l_resp_id, l_appl_id
        FROM   apps.fnd_responsibility frx, apps.fnd_responsibility_tl fr
       WHERE   fr.responsibility_id = frx.responsibility_id
               AND UPPER (fr.responsibility_name) LIKE
                     UPPER (DECODE (ip_operating_unit,
                                    'OU USA MA',
                                    'Payables Manager',
                                    'OU USA WI',
                                    'OU USA WI_Payables Manager',
                                    'OU Austria',
                                    'OU AUSTRIA_Payables Manager',
                                    'OU China',
                                    'OU CHINA_Payables Manager'));

      DBMS_OUTPUT.put_line ('l_resp_id => ' || l_resp_id);
      DBMS_OUTPUT.put_line ('l_appl_id => ' || l_appl_id);
   EXCEPTION
      WHEN OTHERS
      THEN
         err_msg :=
            'Error Occured while Deriving responsibility id' || SQLERRM;
         apps.fnd_file.put_line (
            apps.fnd_file.output,
            'Error Occured while Deriving responsibility id'
         );
   END;

   mo_global.set_policy_context ('S', l_org_id);
   apps.fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

   FOR l_inv_rec IN invoice_cur                                 ----(l_org_id)
   LOOP
      DBMS_OUTPUT.put_line('Calling API ap_cancel_pkg.ap_cancel_single_invoice to Cancel Invoice: '
                           || l_inv_rec.invoice_num);
      DBMS_OUTPUT.put_line (
         '**************************************************************'
      );
      l_boolean :=
         ap_cancel_pkg.ap_cancel_single_invoice (
            p_invoice_id                   => l_inv_rec.invoice_id,
            p_last_updated_by              => l_inv_rec.last_updated_by,
            p_last_update_login            => l_inv_rec.last_update_login,
            p_accounting_date              => l_inv_rec.gl_date,
            p_message_name                 => l_message_name,
            p_invoice_amount               => l_invoice_amount,
            p_base_amount                  => l_base_amount,
            p_temp_cancelled_amount        => l_temp_cancelled_amount,
            p_cancelled_by                 => l_cancelled_by,
            p_cancelled_amount             => l_cancelled_amount,
            p_cancelled_date               => l_cancelled_date,
            p_last_update_date             => l_last_update_date,
            p_original_prepayment_amount   => l_original_prepayment_amount,
            p_pay_curr_invoice_amount      => l_pay_curr_invoice_amount,
            p_token                        => l_token,
            p_calling_sequence             => NULL
         );
      DBMS_OUTPUT.put_line ('l_message_name => ' || l_message_name);
      DBMS_OUTPUT.put_line ('l_invoice_amount => ' || l_invoice_amount);
      DBMS_OUTPUT.put_line ('l_base_amount => ' || l_base_amount);
      DBMS_OUTPUT.put_line ('l_tax_amount => ' || l_tax_amount);
      DBMS_OUTPUT.put_line (
         'l_temp_cancelled_amount => ' || l_temp_cancelled_amount
      );
      DBMS_OUTPUT.put_line ('l_cancelled_by => ' || l_cancelled_by);
      DBMS_OUTPUT.put_line ('l_cancelled_amount => ' || l_cancelled_amount);
      DBMS_OUTPUT.put_line ('l_cancelled_date => ' || l_cancelled_date);
      DBMS_OUTPUT.put_line ('P_last_update_date => ' || l_last_update_date);
      DBMS_OUTPUT.put_line (
         'P_original_prepayment_amount => ' || l_original_prepayment_amount
      );
      DBMS_OUTPUT.put_line (
         'l_pay_curr_invoice_amount => ' || l_pay_curr_invoice_amount
      );

      IF l_boolean
      THEN
         DBMS_OUTPUT.put_line (
            'Successfully Cancelled the Invoice => ' || l_inv_rec.invoice_num
         );
         COMMIT;
      ELSE
         DBMS_OUTPUT.put_line (
            'Failed to Cancel the Invoice => ' || l_inv_rec.invoice_num
         );
         ROLLBACK;
      END IF;
   END LOOP;
END cancel_invoices;

PO Match Option Update in oracle apps r12

/* Formatted on 4/27/2016 11:43:46 AM (QP5 v5.114.809.3010) */
CREATE OR REPLACE PROCEDURE XX_PO_MATCH_OPTION_UPDATE (
   ERRBUF           OUT VARCHAR2,
   RETCODE          OUT NUMBER,
   p_header_id   IN     NUMBER
)
AS
   n_count1   NUMBER := 0;
   n_count2   NUMBER := 0;
   n_count3   NUMBER := 0;

   CURSOR c1
   IS
      SELECT   pll.po_header_id, pll.line_location_id
        FROM   po_headers_all poh, po_line_locations_all pll
       WHERE       poh.po_header_id = pll.po_header_id
               AND poh.org_id = 101
               AND poh.org_id = pll.org_id
               AND pll.receipt_required_flag = 'Y'
               AND pll.inspection_required_flag = 'N'
               AND match_option = 'P'
               AND poh.po_header_id = p_header_id;

   CURSOR c2
   IS
      SELECT   pll.po_header_id, pll.line_location_id
        FROM   po_headers_all poh, po_line_locations_all pll
       WHERE       poh.po_header_id = pll.po_header_id
               AND poh.org_id = 101
               AND poh.org_id = pll.org_id
               AND pll.receipt_required_flag = 'Y'
               AND pll.inspection_required_flag = 'N'
               AND match_option = 'R'
               AND pll.accrue_on_receipt_flag = 'N'
               AND poh.po_header_id = p_header_id;

   CURSOR c3
   IS
      SELECT   pll.po_header_id, pll.line_location_id
        FROM   po_headers_all poh, po_line_locations_all pll
       WHERE       poh.po_header_id = pll.po_header_id
               AND poh.org_id = 101
               AND poh.org_id = pll.org_id
               AND pll.receipt_required_flag = 'Y'
               AND pll.inspection_required_flag = 'N'
               AND match_option = 'P'
               AND pll.accrue_on_receipt_flag = 'N'
               AND poh.po_header_id = p_header_id;
BEGIN
   --CASE 1
   FOR i1 IN c1
   LOOP
      BEGIN
         UPDATE   po_line_locations_all
            SET   match_option = 'R'
          WHERE   line_location_id = i1.line_location_id;

         DBMS_OUTPUT.put_line(   ' PO_HEADER_ID '
                              || i1.po_header_id
                              || '  - '
                              || ' PO_LINE_LOCATION_ID '
                              || i1.line_location_id
                              || ' IS  UPDATED');
         fnd_file.put_line (
            fnd_file.output,
               ' PO_HEADER_ID '
            || i1.po_header_id
            || '  - '
            || ' PO_LINE_LOCATION_ID '
            || i1.line_location_id
            || ' IS  UPDATED'
         );
         n_count1 := n_count1 + 1;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line(   ' PO_HEADER_ID '
                                 || i1.po_header_id
                                 || '  - '
                                 || ' PO_LINE_LOCATION_ID '
                                 || i1.line_location_id
                                 || ' IS NOT UPDATED');
            fnd_file.put_line (
               fnd_file.LOG,
                  ' PO_HEADER_ID '
               || i1.po_header_id
               || '  - '
               || ' PO_LINE_LOCATION_ID '
               || i1.line_location_id
               || ' IS NOT UPDATED'
            );
      END;
   END LOOP;

   DBMS_OUTPUT.put_line (' NO OF RECORDS UPDATED IN CASE 1 ' || n_count1);
   fnd_file.put_line (fnd_file.output,
                      ' NO OF RECORDS UPDATED IN CASE 1 ' || n_count1);

   --CASE 2
   FOR i2 IN c2
   LOOP
      BEGIN
         UPDATE   po_line_locations_all
            SET   accrue_on_receipt_flag = 'Y'
          WHERE   line_location_id = i2.line_location_id;

         DBMS_OUTPUT.put_line(   ' PO_HEADER_ID '
                              || i2.po_header_id
                              || '  - '
                              || ' PO_LINE_LOCATION_ID '
                              || i2.line_location_id
                              || ' IS  UPDATED');
         fnd_file.put_line (
            fnd_file.output,
               ' PO_HEADER_ID '
            || i2.po_header_id
            || '  - '
            || ' PO_LINE_LOCATION_ID '
            || i2.line_location_id
            || ' IS  UPDATED'
         );
         n_count2 := n_count2 + 1;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line(   ' PO_HEADER_ID '
                                 || i2.po_header_id
                                 || '  - '
                                 || ' PO_LINE_LOCATION_ID '
                                 || i2.line_location_id
                                 || ' IS NOT UPDATED');
            fnd_file.put_line (
               fnd_file.LOG,
                  ' PO_HEADER_ID '
               || i2.po_header_id
               || '  - '
               || ' PO_LINE_LOCATION_ID '
               || i2.line_location_id
               || ' IS NOT UPDATED'
            );
      END;
   END LOOP;

   DBMS_OUTPUT.put_line (' NO OF RECORDS UPDATED IN CASE 2 ' || n_count2);
   fnd_file.put_line (fnd_file.output,
                      ' NO OF RECORDS UPDATED IN CASE 2 ' || n_count2);

   --CASE 3
   FOR i3 IN c3
   LOOP
      BEGIN
         UPDATE   po_line_locations_all
            SET   accrue_on_receipt_flag = 'Y', match_option = 'R'
          WHERE   line_location_id = i3.line_location_id;

         DBMS_OUTPUT.put_line(   ' PO_HEADER_ID '
                              || i3.po_header_id
                              || '  - '
                              || ' PO_LINE_LOCATION_ID '
                              || i3.line_location_id
                              || ' IS  UPDATED');
         fnd_file.put_line (
            fnd_file.output,
               ' PO_HEADER_ID '
            || i3.po_header_id
            || '  - '
            || ' PO_LINE_LOCATION_ID '
            || i3.line_location_id
            || ' IS  UPDATED'
         );
         n_count3 := n_count3 + 1;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line(   ' PO_HEADER_ID '
                                 || i3.po_header_id
                                 || '  - '
                                 || ' PO_LINE_LOCATION_ID '
                                 || i3.line_location_id
                                 || ' IS NOT UPDATED');
            fnd_file.put_line (
               fnd_file.LOG,
                  ' PO_HEADER_ID '
               || i3.po_header_id
               || '  - '
               || ' PO_LINE_LOCATION_ID '
               || i3.line_location_id
               || ' IS NOT UPDATED'
            );
      END;
   END LOOP;

   DBMS_OUTPUT.put_line (' NO OF RECORDS UPDATED IN CASE 3 ' || n_count3);
   fnd_file.put_line (fnd_file.output,
                      ' NO OF RECORDS UPDATED IN CASE 3 ' || n_count3);
END xx_po_match_option_update;

                 *********************************************************
exec po_match_option_update( 50428);

Ap supplier site update API in oracle apps

/* Formatted on 8/12/2015 12:49:32 PM (QP5 v5.240.12305.39446) */
DECLARE
   l_vendor_site_rec   AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
   l_vendor_rec        AP_VENDOR_PUB_PKG.r_vendor_rec_type;
   x_vendor_site_id    NUMBER;
   x_vendor_id         NUMBER;
   x_msg_data          VARCHAR2 (1000);
   x_msg_count         NUMBER;
   p_count             NUMBER;
   x_return_status     VARCHAR2 (10);

   CURSOR lcu_rec
   IS
      SELECT vendor_id,
             vendor_site_id,
             purchasing_site_flag,
             org_id
        FROM ap_supplier_sites_all
       WHERE vendor_site_id = 1136620;

BEGIN
   FOR i IN lcu_rec
   LOOP
      l_vendor_site_rec.vendor_id := i.vendor_id;               -- Supplier Id
      l_vendor_site_rec.org_id := i.org_id;               -- Operating Unit id
      x_vendor_site_id := i.vendor_site_id;           -- Site Id to be updated
      --      l_vendor_site_rec.match_option := 'R';
      l_vendor_site_rec.purchasing_site_flag := 'N'; -- eg: purchasing_site_flag

      AP_VENDOR_PUB_PKG.Update_Vendor_Site (
         p_api_version       => 1,
         x_return_status     => x_return_status,
         x_msg_count         => x_msg_count,
         x_msg_data          => x_msg_data,
         p_vendor_site_rec   => l_vendor_site_rec,
         p_vendor_site_id    => x_vendor_site_id);
      COMMIT;
   END LOOP;
END;

Oracle Applications Interface Programs

Oracle Applications Interface Programs

1) Order Import Interface
2) Item Import (Item conversion)
3) On-hand Quantity
4) Customer Conversion
5) Customer API
6) Auto Invoice Interface
7) Receipt API
8) Auto Lockbox Interface
9) AP Invoice Interface
10)Vendor Interface/Conversion
11)Requisition Import
12)PO receipts interface
13)GL Interface
14)GL Budget Interface
15)GL Daily conversion rates

The Information for all the above mentioned Interface programs is given below:

Order Import Interface (Sales Order Conversion)

Interface tables:
· OE_HEADERS_IFACE_ALL
· OE_LINES_IFACE_ALL
· OE_ACTIONS_IFACE_ALL
· OE_ORDER_SOURCES
· OE_CUSTOMER_INFO_IFACE_ALL
· OE_PRICE_ADJS_IFACE_ALL
· OE_PRICE_ATTS_IFACE_ALL

Base tables:
· OE_ORDER_HEADERS_ALL
· OE_ORDER_LINES_ALL
Pricing tables
· QP_PRICING_ATTRIBUTES

During import of orders, shipping tables are not populated.

If importing customers together with the order, OE_ORDER_CUST_IFACE_ALL
Base Tables: HZ_PARTIES HZ_LOCATIONS

Orders can be categorized based on their status:
1. Entered orders 2. Booked orders 3. Closed orders
Concurrent Program: Order Import

Validations:
· Check for sold_to_org_id. If does not exist, create new customer by calling create_new_cust_info API.
· Check for sales_rep_id. Should exist for a booked order.
· Ordered_date should exist. -------- header level
· Delivery_lead_time should exist. ----------- line level
· Earliest_acceptable_date should exist.
· Freight_terms should exist

Order Import API OE_ORDER_PUB.GET_ORDER, PROCESS_ORDER
Concurrent programs will in turn call APIs.

2 APIs are called during order import process.
· OE_CNCL_ORDER_IMPORT_PVT (cancelled orders)
· ORDER_IMPORT_PRIVATE
Procedure: import_order()

On-hand quantity Interface tables:
MTL_TRANSACTIONS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE
MTL_SERIAL_NUMBERS_INTERFACE

The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, PROCESS_FLAG to manipulate the records in the table. Only records with TRANSACTION_MODE of 3, LOCK_FLAG of '2', and PROCESS_FLAG of '1' will be picked up by the Transaction Manager and assigned to a Transaction Worker. If a record fails to process completely, then PROCESS_FLAG will be set to '3' and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.

Base tables: MTL_ON_HAND_QUANTITIES
MTL_LOT_NUMBERS MTL_SERIAL_NUMBERS

Concurrent program:

Validations: validate organization_id, organization_code.
Validate inventory item id.
Transaction period must be open.

Customer conversion Interface tables:
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

Base tables: HZ_PARTIES
HZ_CONTACTS
HZ_PROFILES
HZ_LOCATIONS
Base tables for RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMERS
RA_ADDRESSES_ALL
RA_CUSTOMER_RELATIONSHIPS_ALL
RA_SITE_USES_ALL

Uses TCA APIs.

Concurrent program: Customer Interface

Validations:
Check if legacy values fetched are valid.
Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation: validate cust_account_id, validate customer status.
Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.

Customer API
1. Set the organization id
Exec dbms_application_info.set_client_info(‘204’);

2. Create a party and an account
HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT()
HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE
HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE
HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE

3. Create a physical location
HZ_LOCATION_V2PUB.CREATE_LOCATION()
HZ_LOCATION_V2PUB.LOCATION_REC_TYPE

4. Create a party site using party_id you get from step 2 and location_id from step 3.
HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE()
HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE

5. Create an account site using account_id you get from step 2 and party_site_id from step 4.
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE()
HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE

6. Create an account site use using cust_acct_site_id you get from step 5 ans site_use_code = ‘BILL_TO’.
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE()
HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE
HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE

Base table:
· HZ_PARTIES
· HZ_PARTY_SITES
· HZ_LOCATIONS
· HZ_CUST_ACCOUNTS
· HZ_CUST_SITE_USES_ALL
· HZ_CUST_ACCT_SITES_ALL
· HZ_PARTY_SITE_USES

Validations: Check if legacy values fetched are valid.
Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation: validate cust_account_id, validate customer status.
Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.

Auto Invoice interface Interface tables: RA_INTERFACE_LINES_ALL

Base tables:
RA_CUSTOMER_TRX_ALL RA_BATCHES RA_CUSTOMER_TRX_LINES_ALL AR_PAYMENT_SCHEDULES_ALL RA_CUSTOMER_TRX_LINE_SALESREPS RA_CUST_TRX_GL_DIST_ALL AR_RECEIVABLES_APPLICATIONS AR_ADJUSTMENTS AR_CASH_RECEIPTS RA_CUSTOMER_TRX_TYPES_ALL
Concurrent Program: Auto invoice master program

Validations: check for amount, batch source name, conversion rate, conversion type.
Validate orig_system_bill_customer_id, orig_system_bill_address_id, quantity.
Validate if the amount includes tax flag.

Receipt APIAR_RECEIPT_API_PUB.CREATE_CASH
AR_RECEIPT_API_PUB.CREATE_AND_APPLY

To bring in Unapplied Receipts and Conversion Receipts for Open Debit items to reduce the balance to the original amount due.

Base tables: AR_CASH_RECEIPTS

Validations: check the currency and the exchange rate type to assign the exchange rate.
Validate bill to the customer.
Get bill to site use id.
Get the customer trx id for this particular transaction number.
Get payment schedule date for the customer trx id.

Lockbox interface Interface tables: AR_PAYMENTS_INTERFACE_ALL (Import data from bank file )

Base tables: AR_INTERIM_CASH_RECEIPTS_ALL AR_INTERIM_CASH_RCPT_LINES_ALL (Validate data in interface table and place in quick cash tables)

Related Tables: AR_BANK_ACCOUNTS_ALL AR_RECEIPT_METHODS
AR_TRANSMISSIONS_ALL HZ_CUST_ACCOUNTS HZ_CUST_SITE_USES_ALL AR_CASH_RECEIPTS
(POST QUICK CASH -- applies the receipts and updates customer balances)
Concurrent program: nav-> receivables->interfaces->lockbox

Validations: check for valid record type, transmission record id.
Validate sum of the payments within the transmission.
Identify the lockbox number (no given by a bank to identify a lockbox).

AP invoice interface Interface tables: 

AP_INVOICES_INTERFACE 

AP_INVOICE_LINES_INTERFACE

Base tables: AP_INVOICES_ALL – header information
AP_INVOICE_DISTRIBUTIONS_ALL – lines info

Concurrent program: Payables Open Interface Import

Validations: check for valid vendor
Check for valid vendor site code.
Check if record already exists in payables interface table.

Vendor conversion/interface No interface tables

Base tables: PO_VENDORS

 PO_VENDOR_SITES_ALL

No concurrent program as data is directly populated into base tables.

Validations: check if a vendor already exists with the same name as the TIMSS customer
mail name.
Check if the proper site code and id exists based on the site code from TIMSS.
Check for uppercase value of the vendor name existed in Oracle and in TIMSS, vendor name is mixed case, a new Oracle vendor will not be created.

Purchasing:
Interface Tables Base Tables
PO_HEADERS_INTERFACE PO_HEADERS_ALL
PO_LINES_INTERFACE PO_LINES_ALL
PO_REQUISITIONS_INTERFACE_ALL PO_REQUISITIONS_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
PO_REQ_DIST_INTERFACE_ALL PO_REQ_DISTRIBUTIONS_ALL
PO_DISTRIBUTIONS_INTERFACE PO_DISTRIBUTIONS_ALL
PO_RESCHEDULE_INTERFACE PO_REQUISITION_LINES_ALL

Requisition import Interface tables:
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL

Basetables: PO_REQUISITIONS_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL

Concurrent program: REQUISITION IMPORT

Validations: check for interface transaction source code, requisition destination type.
Check for quantity ordered, authorization status type.

PO Receipts Interface
Interface tables:
· RCV_HEADERS_INTERFACE
· RCV_TRANSACTIONS_INTERFACE

Base tables:
· RCV_SHIPMENT_HEADERS
· RCV_SHIPMENT_LINES
· RCV_TRANSACTIONS

Concurrent program: RECEIVING OPEN INTERFACE

Error messages: 1. Run RECEIVING INTERFACE ERRORS REPORT
2. Look in PO_INTERFACE_ERRORS
Query to check interface errors: PO_INTERFACE_ERRORS .inteface_transaction_id =
RCV_HEADERS_INTERFACE.header_interface_id and processing_status_code in (‘error’ ,’print’)

Validations:

GL interface Interface tables:

 GL_INTERFACE

Base tables:
GL_JE_HEADERS GL_JE_LINES GL_JE_BACTHES
Concurrent Program: Journal Import
Journal Posting --- populates GL_BALANCES

Validations: check SOB, journal source name, journal category name, actual flag
A – actual amounts
B – budget amounts
E – encumbrance amount
If u enter E in the interface table, then enter appropriate encumbrance ID.
B – budget id.
Check if accounting date or GL date based period name is valid (i.e., not closed).
Check if accounting date falls in open or future open period status.
Check chart of accounts id based on Sob id.
Check if valid code combination.
Check if ccid is enabled.
Check if record already exists in GL interface table.
Check if already journal exists in GL application.

Validations for the staging table:
Check if the input data file is already uploaded into staging table.
Check if the record already exists in the interface table.
Check if the journal already exists in the GL application.

GL budget interface Interface tables:
GL_BUDGET_INTERFACE

Base tables:
GL_BUDGETS GL_BUDGET_ASSIGNMENTS GL_BUDGET_TYPES
Concurrent program: Budget Upload

Validations: Check Account combination is valid or not. You check this in GL_CODE_COMBINATIONS table.

GL daily conversion rates
Interface tables: GL_DAILY_RATES_INTERFACE

Base tables:
· GL_DAILY_RATES
· GL_DAILY_CONVERSION_TYPES