10 Replies Latest reply: Mar 14, 2012 10:46 PM by Hussein Sawwan-Oracle RSS

    R12 Oracle Procurement Contracts Storage

    922893
      I am trying to figure out how uploaded supplier contracts (PDF) are stored in the Oracle Database. Our legal department has been tasked with extracting all our originals. I have queried OKC_REP_CONTRACTS_ALL, so I have our entire inventory. I wanted to see if there is a "Silver Bullet" for me to extract all the actual soft-copies to PDF ie. are they stored on a directory that I can access via a SFTP tool?
        • 1. Re: R12 Oracle Procurement Contracts Storage
          EdStevens
          user11986571 wrote:
          I am trying to figure out how uploaded supplier contracts (PDF) are stored in the Oracle Database. Our legal department has been tasked with extracting all our originals. I have queried OKC_REP_CONTRACTS_ALL, so I have our entire inventory. I wanted to see if there is a "Silver Bullet" for me to extract all the actual soft-copies to PDF ie. are they stored on a directory that I can access via a SFTP tool?
          Based on what you've told us, how would we know? Is your undescribed table simply a listing of pdfs and their locations as external files? Are the pdfs stored as blobs within the database?
          • 2. Re: R12 Oracle Procurement Contracts Storage
            922893
            To be honest, I am a totally new to the process and know little re the storage of this data. The table I have described is a key base table for users to retrieve and view contracts via the HTML frontend. From what I understand, several consultants spent weeks uploading individual PDF files via a Java based Front-End template and populating fields that are stored on the aformentioned table. There is a field that describes Storage ie. 11568183.PDF, etc, but nothing that I can drill back to a blob. Based on what I know about Oracle and the described process, I would assume that it's stored as blobs within the database. I do not know if I can simply find the BLOB in the database and extract from them. I have very limited knowledge and unfortunately our key people are no longer here. Is there something I can do to test?

            Thanks in advance for your help!
            • 3. Re: R12 Oracle Procurement Contracts Storage
              EdStevens
              user11986571 wrote:
              To be honest, I am a totally new to the process and know little re the storage of this data. The table I have described is a key base table for users to retrieve and view contracts via the HTML frontend. From what I understand, several consultants spent weeks uploading individual PDF files via a Java based Front-End template and populating fields that are stored on the aformentioned table. There is a field that describes Storage ie. 11568183.PDF, etc, but nothing that I can drill back to a blob. Based on what I know about Oracle and the described process, I would assume that it's stored as blobs within the database. I do not know if I can simply find the BLOB in the database and extract from them. I have very limited knowledge and unfortunately our key people are no longer here. Is there something I can do to test?

              Thanks in advance for your help!
              I assume you can at least connect to the database server with an account that is a member of the dba (*nix) or ora_dba (windows) group. BTW, what OS is this on?

              Once logged onto the server with the requisite OS account, open an os command processor and enter the following:
              sqlplus / as sysdba
              ---- this should get you connected to the database.  Then:
              sql> desc OKC_REP_CONTRACTS_ALL
              copy the commands and results back to here on your next post.
              • 4. Re: R12 Oracle Procurement Contracts Storage
                922893
                Thanks!

                I was unable with SYSDBA role from my own account but I have the passwords for system. We are on Linux auohsmfgh05 2.6.18-92.1.17.0.2.el5 #1 SMP Tue Nov 18 05:20:06 EST 2008 x86_64 x86_64 x86_64 GNU/Linux

                Table desc:
                desc apps.OKC_REP_CONTRACTS_ALL
                Name Null Type
                ------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                CONTRACT_ID NOT NULL NUMBER
                CONTRACT_VERSION_NUM NOT NULL NUMBER
                CONTRACT_NAME VARCHAR2(450)
                CONTRACT_NUMBER NOT NULL VARCHAR2(150)
                CONTRACT_DESC VARCHAR2(2000)
                CONTRACT_TYPE NOT NULL VARCHAR2(30)
                CONTRACT_STATUS_CODE NOT NULL VARCHAR2(30)
                LATEST_SIGNED_VER_NUMBER NUMBER
                VERSION_COMMENTS VARCHAR2(2000)
                ORG_ID NOT NULL NUMBER
                AUTHORING_PARTY_CODE VARCHAR2(30)
                OWNER_ID NOT NULL NUMBER
                CONTRACT_EFFECTIVE_DATE DATE
                CONTRACT_EXPIRATION_DATE DATE
                CURRENCY_CODE VARCHAR2(15)
                AMOUNT NUMBER
                OVERALL_RISK_CODE VARCHAR2(30)
                CANCELLATION_COMMENTS VARCHAR2(2000)
                CANCELLATION_DATE DATE
                TERMINATION_COMMENTS VARCHAR2(2000)
                TERMINATION_DATE DATE
                KEYWORDS VARCHAR2(2000)
                PHYSICAL_LOCATION VARCHAR2(2000)
                EXPIRE_NTF_FLAG VARCHAR2(1)
                EXPIRE_NTF_PERIOD NUMBER
                NOTIFY_CONTACT_ROLE_ID NUMBER
                WF_EXP_NTF_ITEM_KEY VARCHAR2(240)
                ORIG_SYSTEM_REFERENCE_CODE VARCHAR2(30)
                ORIG_SYSTEM_REFERENCE_ID1 VARCHAR2(100)
                ORIG_SYSTEM_REFERENCE_ID2 VARCHAR2(100)
                USE_ACL_FLAG VARCHAR2(1)
                WF_ITEM_TYPE VARCHAR2(8)
                WF_ITEM_KEY VARCHAR2(240)
                CONTRACT_LAST_UPDATED_BY NUMBER(15)
                CONTRACT_LAST_UPDATE_DATE DATE
                SOURCE_LANGUAGE NOT NULL VARCHAR2(4)
                OBJECT_VERSION_NUMBER NOT NULL NUMBER(9)
                CREATED_BY NOT NULL NUMBER(15)
                CREATION_DATE NOT NULL DATE
                LAST_UPDATED_BY NOT NULL NUMBER(15)
                LAST_UPDATE_DATE NOT NULL DATE
                LAST_UPDATE_LOGIN NUMBER(15)
                ATTRIBUTE_CATEGORY VARCHAR2(90)
                ATTRIBUTE1 VARCHAR2(450)
                ATTRIBUTE2 VARCHAR2(450)
                ATTRIBUTE3 VARCHAR2(450)
                ATTRIBUTE4 VARCHAR2(450)
                ATTRIBUTE5 VARCHAR2(450)
                ATTRIBUTE6 VARCHAR2(450)
                ATTRIBUTE7 VARCHAR2(450)
                ATTRIBUTE8 VARCHAR2(450)
                ATTRIBUTE9 VARCHAR2(450)
                ATTRIBUTE10 VARCHAR2(450)
                ATTRIBUTE11 VARCHAR2(450)
                ATTRIBUTE12 VARCHAR2(450)
                ATTRIBUTE13 VARCHAR2(450)
                ATTRIBUTE14 VARCHAR2(450)
                ATTRIBUTE15 VARCHAR2(450)
                PROGRAM_ID NUMBER
                PROGRAM_LOGIN_ID NUMBER
                PROGRAM_APPLICATION_ID NUMBER
                REQUEST_ID NUMBER
                REFERENCE_DOCUMENT_TYPE VARCHAR2(30)
                REFERENCE_DOCUMENT_NUMBER VARCHAR2(150)
                REFERENCE_DOCUMENT_ID NUMBER
                • 5. Re: R12 Oracle Procurement Contracts Storage
                  Srini Chavali-Oracle
                  Pl post details of database and EBS versions. Attachments/documents in EBS are typically stored in either FND_DOCUMENTS or FND_LOBS table. I am not familiar with the OKC module - perhaps others can comment. You can also open an SR with Support asking in which table are these documents stored.

                  HTH
                  Srini
                  • 6. Re: R12 Oracle Procurement Contracts Storage
                    922893
                    Our DB is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production and we are on R12.1.3. Unfortunately, we dont have the SR option at our disposal.

                    I see the tables you mentioned are populated. ie. sample for FND_LOBS is

                    file name | File_content_type | File_data
                    verizon settlement.pdf | application/octet-stream | (BLOB)     

                    Thanks so much!
                    • 7. Re: R12 Oracle Procurement Contracts Storage
                      Srini Chavali-Oracle
                      Pl see these MOS Docs on how to extract/download the docs from FND_LOBS table

                      Attachments in Oracle Applications 11i, Storage and Considerations [ID 176658.1]
                      How To Extract attachements From The Database When a URL Is in The APPLSYS FND_LOBS Table Under The Heading of File_Name [ID 800973.1]
                      Attachments and Exports, a Troubleshooting Guide [ID 135444.1]     

                      HTH
                      Srini
                      • 8. Re: R12 Oracle Procurement Contracts Storage
                        922893
                        Is this an OS command line function or SQL? I have tried both with error.

                        Thanks!
                        • 9. Re: R12 Oracle Procurement Contracts Storage
                          Srini Chavali-Oracle
                          If you are referring to FNDGFU, it is an OS level executable that you should invoke after setting the EBS environment variables.

                          HTH
                          Srini
                          • 10. Re: R12 Oracle Procurement Contracts Storage
                            Hussein Sawwan-Oracle
                            user11986571 wrote:
                            Is this an OS command line function or SQL? I have tried both with error.
                            Login as applmgr user, source the application env file and you should be able to use this tool.

                            Thanks,
                            Hussein