Search This Blog

Sunday, November 14, 2010

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;
/

1 comment:

  1. In general, components like CSS, HTML coding, content management, programming are some of the few areas
    concerned with web development service. With professional
    expertise and years of experience they can bring quality backlinks to your website.

    The first service is based on registering a domain name and adding of local IP address in local languages
    in the targeted market.

    Feel free to surf to my blog :: Referencement maroc

    ReplyDelete