Update AP_CHECKS_ALL THROUGH API

Update AP_CHECKS_ALL THROUGH API

 CREATE OR REPLACE PACKAGE APPS.AP_GTREASURY_TMS_PKG AS
 Procedure GTREASURY_TMS_PRO (
   X_RETCODE out  VARCHAR2,
   X_ERRBUF  out VARCHAR2);
 End;


/* Formatted on 4/26/2013 12:36:53 PM (QP5 v5.114.809.3010) */
CREATE OR REPLACE PACKAGE BODY AP_GTREASURY_TMS_PKG
AS
   PROCEDURE GTREASURY_TMS_PRO (X_RETCODE   OUT VARCHAR2,
                                    X_ERRBUF    OUT VARCHAR2)
   IS
      CURSOR CUR_GTREASURY
      IS
         /*  Select the data from staging and base table  */

         SELECT   apc.ROWID, apc.*, temp.STATUS_LOOKUP_CODE STG_LOOKUP_CODE
           FROM   ap_checks_all apc, GTREASURY_TMS_TBL temp
          WHERE       apc.CHECK_NUMBER = temp.CHECK_NUMBER
                  AND apc.CHECK_DATE = temp.CHECK_DATE
                  AND UPPER (temp.STATUS_LOOKUP_CODE) = UPPER ('reconciled')
                  and temp.PROCESS_FLAG = 'N';

      --  ln_stg_lookup    VARCHAR2(100);
      ln_count         Number;
      in_gl_stat       VARCHAR2 (30);
      l_flag           VARCHAR2 (4);
      l_msg            VARCHAR2 (200);
      V_USER_ID        NUMBER;
      V_RESP_ID        NUMBER;
      V_RESP_APPL_ID   NUMBER;
   BEGIN
      FND_PROFILE.GET ('USER_ID', V_USER_ID);
      FND_PROFILE.GET ('RESP_ID', V_RESP_ID);
      FND_PROFILE.GET ('RESP_APPL_ID', V_RESP_APPL_ID);
      FND_GLOBAL.APPS_INITIALIZE (V_USER_ID, V_RESP_ID, V_RESP_APPL_ID);

      FOR REC_GTREASURY IN CUR_GTREASURY
      LOOP
         /* To validate the check Number*/
         BEGIN
            SELECT   COUNT (1)
              INTO   ln_count
              FROM   ap_checks_all
             WHERE   CHECK_NUMBER = REC_GTREASURY.CHECK_NUMBER
                     AND UPPER (STATUS_LOOKUP_CODE) = UPPER ('reconciled');
         EXCEPTION
            WHEN OTHERS
            THEN
               l_flag := 'E';
               ln_count := 0;
               l_msg := 'Check Number is not available in  System';
         END;

         /* Set the Error Flag */
         IF ln_count = 0
         THEN
            l_flag := 'S';
            l_msg := 'Check Number is ready to reconciled';
         ELSE
            l_flag := 'E';
            l_msg := 'Check Number is already reconciled';
         END IF;

         /* To validate the Gl Period */
         IF l_flag = 'S'
         THEN
            BEGIN
               SELECT   COUNT (1)
                 INTO   in_gl_stat
                 FROM   gl_period_statuses glps, fnd_application_tl fa
                WHERE   fa.application_id = glps.application_id
                        AND REC_GTREASURY.check_date BETWEEN glps.START_DATE
                                                         AND  glps.END_DATE
                        AND fa.application_name = 'General Ledger'
                        AND glps.closing_status = 'O';
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_flag := 'E';
                  in_gl_stat := 0;
                  l_msg := 'GL period is close u can not process the data';
            END;

            /* Set the Error Flag */
            IF in_gl_stat >= 1
            THEN
               l_flag := 'S';
               l_msg := 'Gl period is open';
            ELSE
               l_flag := 'E';
               l_msg := 'Gl period is closed';
            END IF;
         END IF;

         /*  Update the staging table Suceess and Error records */
         IF l_flag = 'S'
         THEN
            UPDATE   GTREASURY_TMS_TBL
               SET   PROCESS_FLAG = 'S', ERR_FLAG = NULL
             WHERE   CHECK_NUMBER = REC_GTREASURY.CHECK_NUMBER;
            
         ELSE
            UPDATE   GTREASURY_TMS_TBL
               SET   PROCESS_FLAG = 'E', ERR_FLAG = l_msg
             WHERE   CHECK_NUMBER = REC_GTREASURY.CHECK_NUMBER;
         END IF;

         /*  Update the Ap_checks_all table through using API */
         IF l_flag = 'S'
         THEN
            AP_AC_TABLE_HANDLER_PKG.UPDATE_ROW (
               p_Rowid                          => REC_GTREASURY.ROWID,
               p_Amount                         => REC_GTREASURY.Amount,
               p_Bank_Account_Id                => REC_GTREASURY.Bank_Account_Id,
               p_Bank_Account_Name              => REC_GTREASURY.Bank_Account_Name,
               p_Check_Date                     => REC_GTREASURY.Check_Date,
               p_Check_Id                       => REC_GTREASURY.Check_Id,
               p_Check_Number                   => REC_GTREASURY.Check_Number,
               p_Currency_Code                  => REC_GTREASURY.Currency_Code,
               p_Last_Updated_By                => REC_GTREASURY.Last_Updated_By,
               p_Last_Update_Date               => REC_GTREASURY.Last_Update_Date,
               p_Payment_Method_Lookup_Code     => REC_GTREASURY.Payment_Method_Lookup_Code,
               p_Payment_Type_Flag              => REC_GTREASURY.Payment_Type_Flag,
               p_External_Bank_Account_Id       => REC_GTREASURY.External_Bank_Account_Id,
               p_Vendor_Id                      => REC_GTREASURY.Vendor_Id,
               p_Vendor_Site_Id                 => REC_GTREASURY.Vendor_Site_Id,
               p_Status_Lookup_Code             => REC_GTREASURY.STG_LOOKUP_CODE, -- update
               p_Address_Line1                  => REC_GTREASURY.ADDRESS_LINE1,
               p_Address_Line2                  => REC_GTREASURY.ADDRESS_LINE2,
               p_Address_Line3                  => REC_GTREASURY.ADDRESS_LINE3,
               p_Checkrun_Name                  => REC_GTREASURY.CHECKRUN_NAME,
               p_Check_Format_Id                => REC_GTREASURY.CHECK_FORMAT_ID,
               p_Check_Stock_Id                 => REC_GTREASURY.CHECK_STOCK_ID,
               p_City                           => REC_GTREASURY.CITY,
               p_Country                        => REC_GTREASURY.COUNTRY,
               p_Last_Update_Login              => REC_GTREASURY.LAST_UPDATE_LOGIN,
               p_Vendor_Name                    => REC_GTREASURY.VENDOR_NAME,
               p_Vendor_Site_Code               => REC_GTREASURY.VENDOR_SITE_CODE,
               p_Zip                            => REC_GTREASURY.ZIP,
               p_Bank_Account_Num               => REC_GTREASURY.BANK_ACCOUNT_NUM,
               p_Bank_Account_Type              => REC_GTREASURY.BANK_ACCOUNT_TYPE,
               p_Bank_Num                       => REC_GTREASURY.BANK_NUM,
               p_Check_Voucher_Num              => REC_GTREASURY.CHECK_VOUCHER_NUM,
               p_Cleared_Amount                 => REC_GTREASURY.CLEARED_AMOUNT,
               p_Cleared_Date                   => REC_GTREASURY.CLEARED_DATE,
               p_Doc_Category_Code              => REC_GTREASURY.DOC_CATEGORY_CODE,
               p_Doc_Sequence_Id                => REC_GTREASURY.DOC_SEQUENCE_ID,
               p_Doc_Sequence_Value             => REC_GTREASURY.DOC_SEQUENCE_VALUE,
               p_Province                       => REC_GTREASURY.PROVINCE,
               p_Released_Date                  => REC_GTREASURY.RELEASED_AT,
               p_Released_By                    => REC_GTREASURY.RELEASED_BY,
               p_State                          => REC_GTREASURY.STATE,
               p_Stopped_Date                   => REC_GTREASURY.STOPPED_AT,
               p_Stopped_By                     => REC_GTREASURY.STOPPED_BY,
               p_Void_Date                      => REC_GTREASURY.VOID_DATE,
               p_Attribute1                     => REC_GTREASURY.ATTRIBUTE1,
               p_Attribute2                     => REC_GTREASURY.ATTRIBUTE2,
               p_Attribute3                     => REC_GTREASURY.ATTRIBUTE3,
               p_Attribute4                     => REC_GTREASURY.ATTRIBUTE4,
               p_Attribute5                     => REC_GTREASURY.ATTRIBUTE5,
               p_Attribute6                     => REC_GTREASURY.ATTRIBUTE6,
               p_Attribute7                     => REC_GTREASURY.ATTRIBUTE7,
               p_Attribute8                     => REC_GTREASURY.ATTRIBUTE8,
               p_Attribute9                     => REC_GTREASURY.ATTRIBUTE9,
               p_Attribute_Category             => REC_GTREASURY.ATTRIBUTE_CATEGORY,
               p_Future_Pay_Due_Date            => REC_GTREASURY.FUTURE_PAY_DUE_DATE,
               p_Treasury_Pay_Date              => REC_GTREASURY.TREASURY_PAY_DATE,
               p_Treasury_Pay_Number            => REC_GTREASURY.TREASURY_PAY_NUMBER,
               p_Ussgl_Transaction_Code         => REC_GTREASURY.USSGL_TRANSACTION_CODE,
               p_Ussgl_Trx_Code_Context         => REC_GTREASURY.USSGL_TRX_CODE_CONTEXT,
               p_Withholding_Status_Lkup_Code   => REC_GTREASURY.WITHHOLDING_STATUS_LOOKUP_CODE,
               p_Reconciliation_Batch_Id        => REC_GTREASURY.RECONCILIATION_BATCH_ID,
               p_Cleared_Base_Amount            => REC_GTREASURY.CLEARED_BASE_AMOUNT,
               p_Cleared_Exchange_Rate          => REC_GTREASURY.CLEARED_EXCHANGE_RATE,
               p_Cleared_Exchange_Date          => REC_GTREASURY.CLEARED_EXCHANGE_DATE,
               p_Cleared_Exchange_Rate_Type     => REC_GTREASURY.CLEARED_EXCHANGE_RATE_TYPE,
               p_Address_Line4                  => REC_GTREASURY.ADDRESS_LINE4,
               p_County                         => REC_GTREASURY.COUNTY,
               p_Address_Style                  => REC_GTREASURY.ADDRESS_STYLE,
               p_Org_Id                         => REC_GTREASURY.ORG_ID,
               p_Exchange_Rate                  => REC_GTREASURY.EXCHANGE_RATE,
               p_Exchange_Date                  => REC_GTREASURY.EXCHANGE_DATE,
               p_Exchange_Rate_Type             => REC_GTREASURY.EXCHANGE_RATE_TYPE,
               p_Base_Amount                    => REC_GTREASURY.BASE_AMOUNT,
               p_Checkrun_Id                    => REC_GTREASURY.CHECKRUN_ID,
               p_global_attribute_category      => REC_GTREASURY.GLOBAL_ATTRIBUTE_CATEGORY,
               p_global_attribute1              => REC_GTREASURY.GLOBAL_ATTRIBUTE1,
               p_global_attribute2              => REC_GTREASURY.GLOBAL_ATTRIBUTE2,
               p_global_attribute3              => REC_GTREASURY.GLOBAL_ATTRIBUTE3,
               p_global_attribute4              => REC_GTREASURY.GLOBAL_ATTRIBUTE4,
               p_global_attribute5              => REC_GTREASURY.GLOBAL_ATTRIBUTE5,
               p_global_attribute6              => REC_GTREASURY.GLOBAL_ATTRIBUTE6,
               p_global_attribute7              => REC_GTREASURY.GLOBAL_ATTRIBUTE7,
               p_global_attribute8              => REC_GTREASURY.GLOBAL_ATTRIBUTE8,
               p_global_attribute9              => REC_GTREASURY.GLOBAL_ATTRIBUTE9,
               p_global_attribute10             => REC_GTREASURY.GLOBAL_ATTRIBUTE10,
               p_global_attribute11             => REC_GTREASURY.GLOBAL_ATTRIBUTE11,
               p_global_attribute12             => REC_GTREASURY.GLOBAL_ATTRIBUTE12,
               p_global_attribute13             => REC_GTREASURY.GLOBAL_ATTRIBUTE13,
               p_global_attribute14             => REC_GTREASURY.GLOBAL_ATTRIBUTE14,
               p_global_attribute15             => REC_GTREASURY.GLOBAL_ATTRIBUTE15,
               p_global_attribute16             => REC_GTREASURY.GLOBAL_ATTRIBUTE16,
               p_global_attribute17             => REC_GTREASURY.GLOBAL_ATTRIBUTE17,
               p_global_attribute18             => REC_GTREASURY.GLOBAL_ATTRIBUTE18,
               p_global_attribute19             => REC_GTREASURY.GLOBAL_ATTRIBUTE19,
               p_global_attribute20             => REC_GTREASURY.GLOBAL_ATTRIBUTE20,
               p_transfer_priority              => REC_GTREASURY.TRANSFER_PRIORITY,
               p_maturity_exchange_rate_type    => REC_GTREASURY.MATURITY_EXCHANGE_RATE_TYPE,
               p_maturity_exchange_date         => REC_GTREASURY.MATURITY_EXCHANGE_DATE,
               p_maturity_exchange_rate         => REC_GTREASURY.MATURITY_EXCHANGE_RATE,
               p_description                    => REC_GTREASURY.DESCRIPTION,
               p_anticipated_value_date         => REC_GTREASURY.ANTICIPATED_VALUE_DATE,
               p_actual_value_date              => REC_GTREASURY.ACTUAL_VALUE_DATE,
               p_void_check_id                  => REC_GTREASURY.VOID_CHECK_ID,
               p_void_check_number              => REC_GTREASURY.VOID_CHECK_NUMBER,
               p_calling_sequence               => 2
            );
         END IF;
      END LOOP;

      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, SQLERRM);
   END GTREASURY_TMS_PRO;
END AP_GTREASURY_TMS_PKG;