Tuesday, 21 May 2013

R12: Bank accounts are not visible

This is due to security set up. Please follow the steps:

1. Log in as Sysadmin.
2. Go to the User Management Responsibility. Path: Roles & Role Inheritance
3. In the Type field select Roles and Responsibilities
4. Wait until a new field appears, you will see the category field, please select Miscellaneous
5. In the Application select 'Cash Management' and click on GO button.
6. Search the responsibility name.
7. Click on the update icon.
8. A new window will be displayed, please click in the Security wizard button.
9. For CE UMX Security wizard click on the Run Wizard.
10. In this window, add the Legal Entity name that you want to grant the access to all the bank accounts and choose the privileges that you want to assign to this role on the bank accounts: USE, MAINTENANCE and BANK ACCOUNT TRANSFERS.
11. Save and apply the changes.

Thursday, 2 May 2013

Display DFF value set values depending on form filed value


Create a table type value set.
Choose the form filed, from the diagonistics choose the form block name and filed name
In the where condition add the condition like

Where 1=1
and table.column_name= :FORM_BLOCK_NAME.FILED_NAME

example:
SELECT segment1
FROM pa_projects_all
WHERE project_status_code = 'UNAPPROVED'
and attribute1 || '.' || attribute2 || '.' || attribute3=SUBSTR(:LINES.ACCOUNTING_FLEXFIELD,1,11)


Form Folder - LINES
Form filed  -- ACCOUNTING_FLEXFIELD

Define Table type value set
Table Name: pa_projects_all
value: segment1
Where:
project_status_code = 'UNAPPROVED'
and attribute1 || '.' || attribute2 || '.' || attribute3=SUBSTR(:LINES.ACCOUNTING_FLEXFIELD,1,11)




Wednesday, 6 March 2013

R12 AR Receipts upload API


In R12 instead of Bank Account ID, Bank account use id has to be passed.


---Custom Table
CREATE TABLE XXVIR.XXVIRTU_DM_RCPT_TBL
(
  OPERATING_UNIT       VARCHAR2(200 BYTE),
  RECEIPT_METHOD       VARCHAR2(200 BYTE),
  RECEIPT_NO           VARCHAR2(50 BYTE),
  RECEIPT_DATE         DATE,
  GL_DATE              DATE,
  CUSTOMER_NUMBER      VARCHAR2(200 BYTE),
  CUSTOMER_LOCATION    VARCHAR2(40 BYTE),
  CURRENCY             VARCHAR2(3 BYTE),
  RECEIPT_AMOUNT       NUMBER,
  BANK_ACCOUNT_ID      NUMBER,
  ORG_ID               NUMBER,
  PROCESS              VARCHAR2(1 BYTE)         DEFAULT 'N',
  ERROR_DESCRIPTION    VARCHAR2(4000 BYTE),
  CUSTOMER_ID          NUMBER,
  SITE_ID              NUMBER,
  RECEIPT_METHOD_ID    NUMBER,
  BANK_ACCOUNT_USE_ID  NUMBER,
  ATTRIBUTE1           VARCHAR2(150 BYTE),
  ATTRIBUTE2           VARCHAR2(150 BYTE)
)


--Validation procedure ---
   l_error_flag           VARCHAR2 (1)                             := 'N';
   l_description          VARCHAR2 (4000)                          := NULL;
   l_customer_id          ar_customers.customer_id%TYPE            := NULL;
   l_site_use_id          hz_cust_site_uses_all.site_use_id%TYPE   := NULL;
   --
   l_attribute_rec_type   ar_receipt_api_pub.attribute_rec_type;
   l_receipt_method_id    NUMBER;
   l_cr_id                INTEGER;
   l_org_id               NUMBER;
   l_location             VARCHAR2 (40);
   x_return_status        VARCHAR2 (2000);
   x_msg_count            NUMBER;
   x_msg_data             VARCHAR2 (2000);
   l_process              VARCHAR2 (1)                             := 'N';
   l_error_description    VARCHAR2 (4000)                          := NULL;
   l_error_message        VARCHAR2 (255)                           := NULL;
BEGIN
   fnd_file.put_line (fnd_file.LOG, 'Validation started !!!');

   FOR rec IN (SELECT a.ROWID r_id, a.*
                 FROM xxvirtu_dm_rcpt_tbl a
                WHERE a.process = 'N')
   LOOP
      l_error_flag := 'N';
      l_description := NULL;
      l_customer_id := NULL;
      l_site_use_id := NULL;
      l_receipt_method_id := NULL;
      l_org_id := NULL;
      l_location :=NULL;
      fnd_file.put_line (fnd_file.LOG, '---------------');
      fnd_file.put_line (fnd_file.LOG, 'Receipt No. ' || rec.receipt_no);

      BEGIN
         SELECT organization_id
           INTO l_org_id
           FROM hr_operating_units
          WHERE NAME = rec.operating_unit;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            l_error_flag := 'E';
            l_description := 'Invalid Org.';
      END;

      BEGIN
         SELECT hca.cust_account_id
           INTO l_customer_id
           FROM hz_cust_accounts hca
          --WHERE account_number = rec.customer_number;
          WHERE trim (account_name) = trim (rec.customer_number);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            l_error_flag := 'E';
            l_description := 'Invalid customer.';
      END;

      BEGIN
         SELECT site_use_id, LOCATION
           INTO l_site_use_id, l_location
           FROM hz_cust_site_uses_all
          WHERE cust_acct_site_id IN (SELECT cust_acct_site_id
                                        FROM hz_cust_acct_sites_all
                                       WHERE cust_account_id = l_customer_id)
            AND site_use_code = 'BILL_TO'
            AND trim (LOCATION) = trim(rec.customer_location)
            AND org_id = l_org_id;
            --AND primary_flag = 'Y';
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            l_error_flag := 'E';
            l_description := l_description || ' Invalid site';
      END;

      BEGIN
         SELECT receipt_method_id
           INTO l_receipt_method_id
           FROM ar_receipt_methods
          WHERE NAME = rec.receipt_method;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            l_error_flag := 'E';
            l_description := l_description || ' Invalid Receipt Method';
      END;

      fnd_file.put_line (fnd_file.LOG, l_description);
      UPDATE xxvirtu_dm_rcpt_tbl
         SET process = DECODE (l_error_flag, 'N', 'V', l_error_flag),
             error_description = l_description,
             customer_id = l_customer_id,
             site_id = l_site_use_id,
             org_id = l_org_id,
             --customer_location = l_location,
             receipt_method_id = l_receipt_method_id
       WHERE ROWID = rec.r_id;
   END LOOP;

   COMMIT;
END;


---Upload procedure
CREATE OR REPLACE PROCEDURE XX_rcpt_upload_prc (
   retcode                  NUMBER,
   error_buf                VARCHAR2,
   pi_operating_unit   IN   VARCHAR2
)
AS
   l_error_flag           VARCHAR2 (1)                             := 'N';
   l_description          VARCHAR2 (4000)                          := NULL;
   l_customer_id          ar_customers.customer_id%TYPE            := NULL;
   l_site_use_id          hz_cust_site_uses_all.site_use_id%TYPE   := NULL;
   l_location             VARCHAR2 (40);
   --
   l_attribute_rec_type   ar_receipt_api_pub.attribute_rec_type;
   l_cr_id                INTEGER;
   l_process              VARCHAR2 (1)                             := 'N';
   l_error_description    VARCHAR2 (4000)                          := NULL;
   l_error_message        VARCHAR2 (4000)                           := NULL;
   i                      NUMBER                                   := 0;
   l_org_id               NUMBER;
   l_return_status        VARCHAR2 (2000);
   l_msg_count            NUMBER;
   l_msg_data             VARCHAR2 (4000);
   l_msg_index_out        NUMBER;
   l_dummy_cnt            NUMBER;
   l_loop_cnt             NUMBER;
BEGIN
   SELECT organization_id
     INTO l_org_id
     FROM hr_operating_units
    WHERE NAME = pi_operating_unit;

   fnd_global.apps_initialize (user_id           => fnd_global.user_id,
                               resp_id           => fnd_global.resp_id,
                               resp_appl_id      => fnd_global.resp_appl_id
                              );
   mo_global.set_org_context (l_org_id, NULL, 'AR');
   mo_global.init ('AR');
   mo_global.set_policy_context ('S', l_org_id);

   FOR rec_1 IN (SELECT x.ROWID r_id, x.*
                   FROM xxvirtu_dm_rcpt_tbl x
                  WHERE x.process = 'V'
                    AND operating_unit = pi_operating_unit)
                    --AND receipt_no = 'PYMNT0005286')
   LOOP
      fnd_file.put_line (fnd_file.LOG, 'Receipt creation started !!!');
      --i:=10;
      l_cr_id := NULL;
      l_attribute_rec_type := NULL;
      l_return_status := NULL;
      l_msg_count := NULL;
      l_msg_data := NULL;
      l_location := NULL;
      l_process := 'Y';
      l_error_description := NULL;
      l_error_message := NULL;
      fnd_file.put_line (fnd_file.LOG, '---------------');
      fnd_file.put_line (fnd_file.LOG, 'Receipt No. ' || rec_1.receipt_no);
      fnd_file.put_line (fnd_file.LOG, 'rec_1.org_id' || rec_1.org_id);
      l_attribute_rec_type.attribute2 := '000000';
     
      ar_receipt_api_pub.create_cash
                  (p_api_version                     => 1.0,
                   p_init_msg_list                   => fnd_api.g_true,
                   p_commit                          => fnd_api.g_true,
                   p_validation_level                => fnd_api.g_valid_level_full,
                   p_receipt_number                  => rec_1.receipt_no,
                   p_amount                          => rec_1.receipt_amount,
                   p_receipt_date                    => rec_1.receipt_date,
                   p_gl_date                         => rec_1.gl_date,
                   p_receipt_method_id               => rec_1.receipt_method_id,
                   p_customer_id                     => rec_1.customer_id,
                   --p_exchange_rate_type              => 'User',
                   --p_exchange_rate                   =>1,
                   --p_customer_number                 => rec_1.customer_number,
                   p_customer_site_use_id            => rec_1.site_id,
                   p_location                        => rec_1.customer_location,
                   p_remittance_bank_account_id      => rec_1.bank_account_use_id,
                   -- 10061,
                   p_org_id                          => rec_1.org_id,
                   p_usr_currency_code               => NULL,
                   p_currency_code                   => rec_1.currency,
                   p_attribute_rec                   => l_attribute_rec_type,
                   p_cr_id                           => l_cr_id,
                   x_return_status                   => l_return_status,
                   x_msg_count                       => l_msg_count,
                   x_msg_data                        => l_msg_data
                  );
      xxvirtu_common_pkg.writelog ('l_return_status :' || l_return_status);

      IF NVL (l_return_status, 'E') != 'S'
      THEN
         l_process := 'E';

         IF l_msg_count > 0
         THEN
            l_loop_cnt := 1;

            LOOP
               fnd_msg_pub.get (p_msg_index          => l_loop_cnt,
                                p_data               => l_msg_data,
                                p_encoded            => fnd_api.g_false,
                                p_msg_index_out      => l_dummy_cnt
                               );

               IF l_return_status = 'E' OR l_return_status = 'U'
               THEN
                  l_msg_data := CONCAT ('ERROR >>> ', l_msg_data);

                  IF LENGTH (l_error_message || l_msg_data) < 3000
                  THEN
                     l_error_message := l_error_message || l_msg_data;
                  END IF;
               END IF;

               l_loop_cnt := l_loop_cnt + 1;
               EXIT WHEN l_loop_cnt > l_msg_count;
            END LOOP;

            xxvirtu_common_pkg.writelog ('l_error_message :'
                                         || l_error_message
                                        );
         END IF;

         ROLLBACK;
      ELSE
         l_process := 'Y';
         fnd_file.put_line (fnd_file.LOG, 'Receipt Created!!!');
         fnd_file.put_line (fnd_file.LOG, 'Receipt ID :' || l_cr_id);
         xxvirtu_common_pkg.writelog ('l_error_message :' || l_error_message);
         xxvirtu_common_pkg.writelog ('l_error_message :' || l_error_message);
      END IF;

      UPDATE xxvirtu_dm_rcpt_tbl
         SET process = l_process,
             error_description = l_error_message
       WHERE ROWID = rec_1.r_id;

      COMMIT;
   END LOOP;
END;

Tuesday, 19 February 2013

Custom workflow timeout issue


Faced a problem in a custom workflow in time out node.
Have created a workflow with timeout node.


The workflow initiated from a custom form which registered in a custom application. Even if the time out node has been added in wf, still time out has not been worked. Process stuck at the node where timeout has been added. The reason I have found when workflow background process runs, the time out called from WF_ENGINE.ProcessTimeOut(itemtype in varchar2) package. And the package has only considered APPS schema or more precisely it checks the the owner of the workflow is in apps schema.

As the owner of the wf is custom application so each time wf process stuck at the timeout node and its wait for the response.


procedure ProcessTimeOut(itemtype in varchar2)
is
  resource_busy exception;
  pragma exception_init(resource_busy, -00054);

  l_itemtype      varchar2(8);
  l_itemkey       varchar2(240);
  l_actid         pls_integer;
  pntfstatus      varchar2(8);
  pntfresult      varchar2(30);

  -- Select one timeout activity that matches itemtype
  -- NOTE: Two separate cursors are used for itemtype and no-itemtype
  -- cases to get better execution plans.


  -- select everything but completed and error.
  -- avoid "not in" which disables index in RBO
  cursor curs_itype is
    select
         S.ROWID ROW_ID
    from WF_ITEM_ACTIVITY_STATUSES S
    where S.DUE_DATE < SYSDATE
    and S.ACTIVITY_STATUS in ('ACTIVE','WAITING','NOTIFIED',
                              'SUSPEND','DEFERRED')
    and S.ITEM_TYPE = itemtype;

  cursor curs_noitype is
    select
         S.ROWID ROW_ID
    from WF_ITEM_ACTIVITY_STATUSES S
    where S.DUE_DATE < SYSDATE
    and S.ACTIVITY_STATUS in ('ACTIVE','WAITING','NOTIFIED',
                              'SUSPEND','DEFERRED');

  idarr RowidArrayTyp;
  arrsize pls_integer;
  eligible boolean;
  schema   varchar2(30);

begin
  -- Fetch eligible rows into array
  arrsize := 0;
  if (itemtype is not null) then
    -- Fetch by itemtype
    for id in curs_itype loop
      arrsize := arrsize + 1;
      idarr(arrsize) := id.row_id;
    end loop;
  else
    -- Fetch all itemtypes
    for id in curs_noitype loop
      arrsize := arrsize + 1;
      idarr(arrsize) := id.row_id;
    end loop;
  end if;

  -- Process all eligible rows found
  for i in 1 .. arrsize loop
    -- Lock row, and check if still eligible for execution
    -- To check eligibility, do original select only add rowid condition.
    -- Note ok to use no-itemtype variant since itemtype can't change
    -- and was already filtered for in original select.
    -- select everything but completed and error. avoid "not in" which
    -- disables index in RBO.
    begin
      select
        S.ITEM_TYPE, S.ITEM_KEY, S.PROCESS_ACTIVITY
      into l_itemtype, l_itemkey, l_actid
      from WF_ITEM_ACTIVITY_STATUSES S , WF_ITEMS WI
      where S.DUE_DATE < SYSDATE
      and S.ACTIVITY_STATUS in ('WAITING','NOTIFIED','SUSPEND',
                                'DEFERRED','ACTIVE')
      and S.ROWID = idarr(i)
      and WI.item_type   = S.ITEM_TYPE
      and WI.item_key    = S.ITEM_KEY
      for update of S.ACTIVITY_STATUS, WI.item_type , wi.item_key NOWAIT;

      -- check if schema matched
        schema := Wf_Engine.GetItemAttrText(l_itemtype,l_itemkey,
                    wf_engine.eng_schema, ignore_notfound=>TRUE);
                   
        ---*** XX Added
        --- For time out feature wf only consider APPS schema.
        --- We have registered our custom packages in the XX schema
        --- So delibaretly we are bypassing schema value as APPS
        If Schema = 'XX'
        then
            schema :='APPS';
        End if;
        ---*** XX End
         
 
      if (schema is null or
          schema = Wf_Engine.Current_Schema) then
        eligible := TRUE;
      else
        eligible := FALSE;
      end if;
    exception
      when resource_busy or no_data_found then
        -- If row already locked, or no longer eligible to run,
        -- continue on to next item in list.
        eligible := FALSE;
    end;

    if (eligible) then
      -- Set the status to COMPLETE:#TIMEOUT.
      Wf_Item_Activity_Status.Create_Status(l_itemtype, l_itemkey, l_actid,
          wf_engine.eng_completed, wf_engine.eng_timedout);

      begin
       begin
        begin
          savepoint wf_savepoint;
          -- If there is a function attached, call it in timeout mode to
          -- give the function one last chance to complete and override
          -- the timeout.
          Wf_Engine_Util.Execute_Post_NTF_Function(l_itemtype, l_itemkey,
              l_actid, wf_engine.eng_timeout, pntfstatus, pntfresult);
          if (pntfstatus = wf_engine.eng_completed) then
            -- Post-notification function found and returned a completed
            -- status.
            -- Complete activity with result of post-notification function.
            Wf_Engine_Util.Complete_Activity(l_itemtype, l_itemkey, l_actid,
                pntfresult, FALSE);
          else
            -- Either had no post-notification function, or result was still
            -- not complete.
            -- In either case, complete activity with #TIMEOUT.
            Wf_Engine_Util.Complete_Activity(l_itemtype, l_itemkey, l_actid,
                wf_engine.eng_timedout);
          end if;
        exception
          when others then
            -- If anything in this process raises an exception:
            -- 1. rollback any work in this process thread
            -- Raise an exception for the next exception handler to finish
            -- remaining steps.
            rollback to wf_savepoint;
            raise;
        end;
       exception
         when NO_SAVEPOINT then
           -- Catch any savepoint error in case of a commit happened.
           Wf_Core.Token('ACTIVITY', Wf_Engine.GetActivityLabel(l_actid));
           Wf_Core.Raise('WFENG_COMMIT_IN_COMPLETE');
       end;
      exception
        when OTHERS then
          -- Remaining steps for completing activity raises an exception:
          -- 2. set this activity to error status
          -- 3. execute the error process (if any)
          -- 4. clear the error to continue with next activity
          Wf_Core.Context('Wf_Engine', 'ProcessTimeout', l_itemkey, l_itemtype,
              to_char(l_actid));
          Wf_Item_Activity_Status.Set_Error(l_itemtype, l_itemkey, l_actid,
              wf_engine.eng_exception, FALSE);
          Wf_Engine_Util.Execute_Error_Process(l_itemtype, l_itemkey,
              l_actid, wf_engine.eng_exception);
          Wf_Core.Clear;
      end;
    end if;

    -- bug 7828862 - Resynch apps context from cached values if it changed
    wfa_sec.Restore_Ctx();

    -- For eligible row: Commit work to insure this activity
    --   thread doesn't interfere with others.
    -- For non-eligible row: Commit to release the lock.
    commit;
    Fnd_Concurrent.Set_Preferred_RBS;

  end loop;

exception
  when others then
    Wf_Core.Context('Wf_Engine', 'ProcessTimeout', l_itemkey, l_itemtype,
                    to_char(l_actid));
    raise;
end ProcessTimeOut;


Tuesday, 22 January 2013

R12 Supplier Contact creation API


AP_SUP_SITE_CONTACT_INT interface used in 11i for loading supplier contact. Currently in R12 contacts can not be loaded using the interface table.

ap_vendor_pub_pkg.create_vendor_contact API is used in R12 and the program has to be registered as a concurrent program.
### Sample R12 Code    

BEGIN
     v_party_usg_assignment_id := NULL;
      v_relationship_id := NULL;
      v_directional_flag := NULL;
      v_rel_rowid := NULL;
      fnd_file.put_line (fnd_file.LOG,
                            'Vendor code :'
                         || rec.vendor_name
                         || '  Vendor site code :'
                         || rec.vendor_site_code
                         || '  Person Last Name : '
                         || rec.person_last_name
                        );
      l_vendor_contact.vendor_id := rec.vendor_id;
      l_vendor_contact.vendor_site_id := rec.vendor_site_id;
      l_vendor_contact.org_id := rec.org_id;
      l_vendor_contact.person_first_name := rec.person_first_name;
      l_vendor_contact.person_middle_name := rec.person_middle_name;
      l_vendor_contact.person_last_name := rec.person_last_name;
     l_vendor_contact.phone := rec.phone;
      l_vendor_contact.email_address := rec.email_address;
      p_init_msg_list := fnd_api.g_true;
      p_commit := fnd_api.g_false;
      x_return_status := NULL;
      x_msg_count := NULL;
      x_msg_data := NULL;

      IF rec.process_flag = 'I'
      THEN
         fnd_file.put_line (fnd_file.LOG, 'Creating contacts....');
         ap_vendor_pub_pkg.create_vendor_contact
                  (p_api_version             => p_api_version,
                   p_init_msg_list           => p_init_msg_list,
                   p_commit                  => p_commit,
                   x_return_status           => x_return_status,
                   x_msg_count               => x_msg_count,
                   x_msg_data                => x_msg_data,
                   p_vendor_contact_rec      => l_vendor_contact,
                   x_vendor_contact_id       => l_vendor_contact.vendor_contact_id,
                   x_per_party_id            => l_vendor_contact.per_party_id,
                   x_rel_party_id            => l_vendor_contact.relationship_id,
                   x_rel_id                  => l_vendor_contact.rel_party_id,
                   x_org_contact_id          => l_vendor_contact.org_contact_id,
                   x_party_site_id           => l_vendor_contact.party_site_id
                  );
         error_handling (rec.r_id, x_return_status, x_msg_count, x_msg_data);
         fnd_file.put_line (fnd_file.LOG, '*************');
      END IF;
END;



Friday, 11 January 2013

Text Attachment content print

Various files are attached in the documents like PO, Requisition, Invoice etc. If an attachment is a text type then  it is possible to print the content of the attachment .

One of the example various PO terms and conditions are added in the PO header or line level. The following script will help print in the report.


/* Formatted on 2013/01/11 23:36 (Formatter Plus v4.8.8) */
DECLARE
   CURSOR cur_file_info (i_hdr_id NUMBER)
   IS
      SELECT DISTINCT seq_num, media_id
                 FROM fnd_attached_docs_form_vl
                WHERE pk1_value = i_hdr_id
                  AND category_description = 'To Supplier'
                  AND datatype_name = 'File'
             ORDER BY seq_num;
BEGIN
   BEGIN
      l_file_content := NULL;

      FOR get_file_content IN cur_file_info (:po_header_id)
      LOOP
         SELECT file_id, file_name
           INTO l_file_id, l_file_name
           FROM fnd_lobs
          WHERE file_content_type = 'text/plain'
            AND file_id = get_file_content.media_id;

         populate_file_content (l_file_id, l_file_content);
      END LOOP;
   END;
END;






PROCEDURE populate_file_content (i_file_id NUMBER, i_file_content OUT VARCHAR2)
IS
   l_blob                   BLOB;
   l_clob                   CLOB             := EMPTY_CLOB ();
   l_src_offset             NUMBER           := 1;
   l_dest_offset            NUMBER           := 1;
   l_blob_csid              INTEGER          := 0;
   v_lang_context           INTEGER          := 0;
   l_warning                NUMBER;
   l_amount                 BINARY_INTEGER;
   l_buffer_size   CONSTANT BINARY_INTEGER   := 32767;
   l_buffer                 VARCHAR2 (32767) := NULL;
   --l_file_content           VARCHAR2 (32767) := NULL;
   l_offset                 NUMBER           := 1;
BEGIN
   l_buffer := NULL;
   DBMS_LOB.createtemporary (l_blob, TRUE);
   DBMS_LOB.createtemporary (l_clob, TRUE);

   SELECT file_data
     INTO l_blob
     FROM fnd_lobs
    WHERE file_id = i_file_id;

   IF DBMS_LOB.getlength (l_blob) > 0
   THEN
      DBMS_LOB.converttoclob (l_clob,
                              l_blob,
                              DBMS_LOB.getlength (l_blob),
                              l_dest_offset,
                              l_src_offset,
                              1,
                              v_lang_context,
                              l_warning
                             );
   END IF;

   l_amount := l_buffer_size;

   WHILE l_amount >= l_buffer_size
   LOOP
      BEGIN
         DBMS_LOB.READ (lob_loc      => l_clob,
                        amount       => l_amount,
                        offset       => l_offset,
                        buffer       => l_buffer
                       );
         l_buffer := REPLACE (l_buffer, CHR (13), '');
         l_offset := l_offset + l_amount;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;
   END LOOP;

   i_file_content := l_buffer;
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;





Friday, 28 December 2012

R12 Vendor/Supplier Bank Details Query


Employee as a supplier Bank details

SELECT aps.vendor_id, apss.vendor_site_id, aps.vendor_name,
       apss.vendor_site_code, ieb.bank_name, ieb.country,
       iebb.bank_branch_name, iebb.eft_swift_code, iebb.branch_number,
       ieba.bank_account_num, ieba.bank_account_name, iban
  FROM ap.ap_suppliers aps,
       per_all_people_f papf,
       ap.ap_supplier_sites_all apss,
       apps.iby_ext_bank_accounts ieba,
       apps.iby_account_owners iao,
       apps.iby_ext_banks_v ieb,
       apps.iby_ext_bank_branches_v iebb
 WHERE aps.vendor_id = apss.vendor_id
   AND iao.account_owner_party_id = aps.party_id
   AND ieba.ext_bank_account_id = iao.ext_bank_account_id
   AND ieb.bank_party_id = iebb.bank_party_id
   AND ieba.branch_id = iebb.branch_party_id
   AND ieba.bank_id = ieb.bank_party_id
   AND aps.employee_id = papf.person_id
   AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                           AND papf.effective_end_date


Supplier Bank details (Not an Employee)


SELECT aps.vendor_id, apss.vendor_site_id, aps.vendor_name,
       apss.vendor_site_code, ieb.bank_name, ieb.country,
       iebb.bank_branch_name, iebb.eft_swift_code, iebb.branch_number,
       ieba.bank_account_num, ieba.bank_account_name, iban
  FROM ap.ap_suppliers aps,
       ap.ap_supplier_sites_all apss,
       apps.iby_ext_bank_accounts ieba,
       apps.iby_account_owners iao,
       apps.iby_ext_banks_v ieb,
       apps.iby_ext_bank_branches_v iebb
 WHERE aps.vendor_id = apss.vendor_id
   AND iao.account_owner_party_id = aps.party_id
   AND ieba.ext_bank_account_id = iao.ext_bank_account_id
   AND ieb.bank_party_id = iebb.bank_party_id
   AND ieba.branch_id = iebb.branch_party_id
   AND ieba.bank_id = ieb.bank_party_id

Check series information exists in CE_PAYMENT_DOCUMENTS table.