GL Interface

CREATE OR REPLACE PACKAGE XX_BOE_GL_INTERFACE_PKG
AUTHID CURRENT_USER
AS  ;
  PROCEDURE processdata
   (p_org_id in number ) ;
END XX_BOE_GL_INTERFACE_PKG;

CREATE OR REPLACE PACKAGE BODY XX_BOE_GL_INTERFACE_PKG
AS
   PROCEDURE processdata (p_org_id in number)
   IS   
      CURSOR gl_cur_new
      IS
         SELECT *
           FROM xxsoboe_headers_all
          WHERE nvl(receive_flag,'N') = 'Y'
          AND nvl(gl_interface_flag,'N') = 'N';

      v_currency_code1          VARCHAR2 (15);
      v_currency_code2          VARCHAR2 (15);
      v_sob_id                  VARCHAR2 (15);
      v_chart_of_account_id     NUMBER       ;
      v_user_je_category_name   VARCHAR2 (25);
      v_period_name1            VARCHAR2 (15);
      v_period_name2            VARCHAR2 (15);
      v_segment1                VARCHAR2 (15);
      v_segment2                VARCHAR2 (15);
      v_segment3                VARCHAR2 (15);
      v_segment4                VARCHAR2 (15);
      v_segment5                VARCHAR2 (15);
      v_segment6                VARCHAR2 (15);
      v_segment7                VARCHAR2 (15);
      v_error_reason            VARCHAR2 (200);
      v_sum_amount              VARCHAR2 (10);
      v_entered_dr              VARCHAR2 (15);
      v_entered_cr              VARCHAR2 (15);
      v_conv_type               VARCHAR2 (100);
      e_segments_vald           EXCEPTION;
      e_foreign_curr            EXCEPTION;
      e_dr_cr                   EXCEPTION;
   BEGIN
      FOR rec_cur_new IN gl_cur_new
      LOOP
         -- Initializing all variables
         v_currency_code1 := '';
         v_currency_code2 := '';
         v_sob_id := '';
         v_user_je_category_name := '';
         v_period_name1 := '';
         v_period_name2 := '';
         v_segment1 := '';
         v_segment2 := '';
         v_segment3 := '';
         v_segment4 := '';
         v_segment5 := '';
         v_segment6 := '';
         v_segment7 := '';
         v_error_reason := '';
         v_sum_amount := '';
         v_entered_dr := '';
         v_entered_cr := '';
         v_conv_type := '';
       
        
        
--THIS PLSQL BLOCK FINDS SET_OF_BOOKS ID AND CHART OF ACCOUNTS ID
       BEGIN
         select distinct OOD.set_of_books_id
                 , OOD.chart_of_accounts_id
         INTO  v_sob_id
             , v_chart_of_account_id
         from org_organization_definitions OOD
         where OOD.operating_unit = p_org_id   ;
        
         fnd_file.put_line (fnd_file.LOG,'Set of Books and Chart of account validated');
                 
       exception
         when OTHERS then
           v_err_msg := substr(sqlerrm,1,60);
           fnd_file.put_line (fnd_file.LOG,'Error while validatating SOB and COA - '||v_err_msg);
           p_message := 'Error while validatating SOB and COA - '||v_err_msg;
       END; 
--END OF SOB AND COA VALIDATIONS                
-- THIS PLSQL BLOCK VALIDATES THE CURRENCY CODE
         BEGIN
            SELECT distinct currency_code
              INTO v_currency_code
              FROM apps.gl_sets_of_books
             WHERE set_of_books_id = v_sob_id;
            fnd_file.put_line (fnd_file.LOG,'Currency Code Validated');
         EXCEPTION
            WHEN OTHERS
            THEN             
               p_message :=                
                 ' Error while validating Currency Code. '
                  || SQLERRM
                  || SQLCODE;
               -- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE
               fnd_file.put_line (fnd_file.LOG,p_message);
         END;
-- END OF CURRENCY CODE VALIDATIONS.
      
-- END OF SET OF BOOKS VALIDATIONS.
         -- THIS PLSQL BLOCK VALIDATES PERIOD NAME
         BEGIN
            SELECT DISTINCT period_name
                       INTO v_period_name
                       FROM apps.gl_periods
                      WHERE trunc(sysdate)  between  start_date and end_date
                      AND period_name not like '%ADJ%';
            fnd_file.put_line(fnd_file.LOG,'Period Name Validated');
         EXCEPTION
            WHEN OTHERS then
               p_message :=         
                   ' Error while validating Period Name. '
                  || SQLERRM
                  || SQLCODE;
               -- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE
               fnd_file.put_line (fnd_file.LOG,p_message);
         END;
-- END OF PERIOD NAME VALIDATION
         -- THIS PLSQL BLOCK VALIDATES PERIOD NAME STATUSES
         BEGIN
            SELECT period_name
              INTO v_period_status
              FROM apps.gl_period_statuses
             WHERE trunc(sysdate)  between  start_date and end_date
               AND period_name not like '%ADJ%';
               AND set_of_books_id = v_sob_id
               AND application_id = fnd_global.resp_appl_id
               AND closing_status IN ('O');
           fnd_file.put_line(fnd_file.LOG,'Period Name Statuses Validated');
         EXCEPTION
            WHEN OTHERS
            THEN
               p_message :=         
                   ' Error while validating Period Status. '
                  || SQLERRM
                  || SQLCODE;
               -- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE
               fnd_file.put_line (fnd_file.LOG,p_message);
         END;
-- END OF VALIDATING PERIOD NAME STATUSES
         -- THIS PLSQL BLOCK VALIDATES THE USER JE CATEGORY NAME
         BEGIN
            SELECT user_je_category_name
              INTO v_user_je_category_name
              FROM gl_je_categories
             WHERE upper(user_je_category_name) = 'ACCRUAL';
             fnd_file.put_line(fnd_file.LOG,'User Category Name Validated');
         EXCEPTION
            WHEN OTHERS
            THEN
             
                 fnd_file.put_line(fnd_file.LOG,' Error while validating User Category Name. '
                  || SQLERRM
                  || SQLCODE;
               -- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE
              p_message := ' Error while validating User Category Name. '
                  || SQLERRM
                  || SQLCODE;
         END;
-- END OF USER JE CATEGORY NAME
         -- THIS PLSQL BLOCK VALIDATES THE EXISTENCE OF DR OR CR AMOUNT IN A SINGLE LINE
         BEGIN
           select nvl(custom_duty_shipment_cost,0)
           into v_entered_dr
           from xx_boe_document_cost_v
           where document_number = rec_cur_new.document_number;          
         EXCEPTION
            WHEN others then
              p_message :=
                  ' Error while validating Dr & Cr Amount in Single Line. '
                  || SQLERRM
                  || SQLCODE;
               -- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE   
              fnd_file.put_line(fnd_file.LOG,p_message);    
         END;
        
         IF rec_cur_new.trans_type = 'PO' then
           begin
             select distinct HCA.absorption_acct_ccid
             into ln_code_combination_id
             from hl_cst_comp_mst HCM
                , hl_cst_comp_mst_acct HCA
             where HCM.component_id = HCA.component_id
             AND upper(HCM.component_name) = 'CUSTOMS DUTY';
           exception
           when others then
             p_message := 'Please check Custom Duty Account setup in Landed Cost');
             fnd_file.put_line(fnd_file.LOG,p_message);
           END;
         ELSE
           begin
           NULL;
           EXCEPTION
           END;
         END IF;
-- END OF VALIDATING EXISTENCE OF DR OR CR AMOUNT IN A SINGLE LINE
         begin
           select ledger_id
           into ln_ledger_id
           from gl_ledgers
           where ledger_category_code = 'PRIMARY'
         exception
         when others then
           p_message := 'Please check Primary Legder setup');
           fnd_file.put_line(fnd_file.LOG,p_message);
         end;

         -- END OF ACCOUNTING FLEXFIELD SEGMENTS
-- INSERTING DATA INTO INTERFACE TABLE
         INSERT INTO GL_INTERFACE ( status,
                           set_of_books_id,
                           accounting_date,
                           currency_code,
                           date_created,
                           created_by,
                           actual_flag,
                           user_je_category_name,
                           user_je_source_name,
                           code_combination_id,
                           entered_dr,
                           entered_cr,
                           accounted_dr,
                           accounted_cr,                         
                           chart_of_accounts_id,
                           functional_currency_code,
                           transaction_date,
                           period_name ,
                           ledger_id )
                  VALUES ( 'NEW',
                           v_sob_id
                           trunc(SYSDATE),
                           v_currency_code,
                           SYSDATE,
                           fnd_profile.value('USER_ID'),
                           'A',
                           ,v_user_je_category_name
                           'Other',
                           ln_code_combination_id,
                           ,v_entered_dr
                           0,
                           v_entered_dr,
                           0,                         
                           v_chart_of_account_id ,
                           v_currency_code,
                           SYSDATE,
                           v_period_name,
                           ln_ledger_id
                         )
         UPDATE xxsoboe_headers_all
            SET gl_interface_flag = 'Y'
              , gl_interface_date = sysdate
          WHERE doc_number = rec_cur_new.doc_number;
      END LOOP;
   END processdata;
   /*-----------------------------------------------------------------------
   runInterface
     Called as a concurrent program - manages the import process
   -----------------------------------------------------------------------*/

END dllgl_interface_pkg;
/