Supplier Management

Get Involved. Join the Conversation.

Topic

    Ervar Stecher
    How to pull information from BPM regarding Suppliers?Answered
    Topic posted October 4, 2019 by Ervar StecherBronze Medal: 1,250+ Points 
    31 Views, 2 Comments
    Title:
    How to pull information from BPM regarding Suppliers?
    Content:

    Hi,

    We need to provide the bank account changes made to the suppliers, the person who made the change and who approved the change. Looking into BPM we can get the requested information in the "Supplier Profile Change Request":

    We query the below tables but dont contain the whole information:

    FND_BPM_TASK_B
    FND_BPM_TASK_TL
    FND_BPM_TASK_HISTORY_B
    FND_BPM_TASK_COMMENT
     
    What do you recommend for this requirement?
     
    Thanks.
    Image:

    Best Comment

    Maarten van der Burg

    Hi Hong,

    We tried the OTBI way, but the most crucial part of information was missing: The bank account details / IBAN data.

    Ended up writing an SQL query. Posting it here for future reference. It pulls all requests pending approval:

    SELECT
     psv.VENDOR_NAME "Supplier Name"
    ,psav.PARTY_SITE_NAME "Supplier Address Name"
    ,iteba.IBAN "IBAN"
    ,cbbv.BANK_NAME "Bank Name"
    ,cbbv.BANK_BRANCH_NAME "Bank Branch Name"
    ,iteba.CURRENCY_CODE "Bank Account Currency"
    ,iteba.BANK_ACCOUNT_NAME "Bank Account Name"
    ,psr.CHANGE_REQ_NUMBER "Request Number"
    ,psr.LAST_UPDATED_BY "Last Updated By"
    ,psr.LAST_UPDATE_DATE "Last Update Date"
     
    FROM
    POZ_SUPP_REQUESTS psr,
    POZ_SUPP_BANK_ACCT_REQUESTS psbar,
    IBY_TEMP_EXT_BANK_ACCTS iteba,
    CE_BANK_BRANCHES_V cbbv,
    POZ_SUPPLIERS_V psv,
    POZ_SUPPLIER_ADDRESS_V psav
     
    WHERE
    1=1
    AND psr.SUPP_REQUEST_ID = psbar.SUPP_REQUEST_ID
    AND psbar.TEMP_EXT_BANK_ACCT_ID = iteba.TEMP_EXT_BANK_ACCT_ID
    AND iteba.BRANCH_ID = cbbv.BRANCH_PARTY_ID
    AND psr.VENDOR_ID = psv.VENDOR_ID
    AND psv.PARTY_ID = psav.PARTY_ID
     
    AND psr.REQUEST_STATUS_CODE = 'PENDING_APPROVAL'
    Kind regards,
    Maarten

    Comment

     

    • Hong Gao

      To monitor the details on changes, suggest to use the supplier audit functionality.  To view the approval history for supplier profile change requests, you can use Supplier Profile Change Change Real Time subject area to build OTBI report for that.

      Regards,

      Hong

      • Maarten van der Burg

        Hi Hong,

        We tried the OTBI way, but the most crucial part of information was missing: The bank account details / IBAN data.

        Ended up writing an SQL query. Posting it here for future reference. It pulls all requests pending approval:

        SELECT
         psv.VENDOR_NAME "Supplier Name"
        ,psav.PARTY_SITE_NAME "Supplier Address Name"
        ,iteba.IBAN "IBAN"
        ,cbbv.BANK_NAME "Bank Name"
        ,cbbv.BANK_BRANCH_NAME "Bank Branch Name"
        ,iteba.CURRENCY_CODE "Bank Account Currency"
        ,iteba.BANK_ACCOUNT_NAME "Bank Account Name"
        ,psr.CHANGE_REQ_NUMBER "Request Number"
        ,psr.LAST_UPDATED_BY "Last Updated By"
        ,psr.LAST_UPDATE_DATE "Last Update Date"
         
        FROM
        POZ_SUPP_REQUESTS psr,
        POZ_SUPP_BANK_ACCT_REQUESTS psbar,
        IBY_TEMP_EXT_BANK_ACCTS iteba,
        CE_BANK_BRANCHES_V cbbv,
        POZ_SUPPLIERS_V psv,
        POZ_SUPPLIER_ADDRESS_V psav
         
        WHERE
        1=1
        AND psr.SUPP_REQUEST_ID = psbar.SUPP_REQUEST_ID
        AND psbar.TEMP_EXT_BANK_ACCT_ID = iteba.TEMP_EXT_BANK_ACCT_ID
        AND iteba.BRANCH_ID = cbbv.BRANCH_PARTY_ID
        AND psr.VENDOR_ID = psv.VENDOR_ID
        AND psv.PARTY_ID = psav.PARTY_ID
         
        AND psr.REQUEST_STATUS_CODE = 'PENDING_APPROVAL'
        Kind regards,
        Maarten