4 Replies Latest reply on May 31, 2019 3:07 PM by skull

    Where is the One Time Address Details for PO stored in the database tables?

    skull

      Hi,

       

      EBS 12.1.3

       

      I found the below note which provides the query & the table (POR_ITEM_ATTRIBUTE_VALUES) to find the One Time Address details for a Requisition. Where are the One Time Address details for POs stored?

       

      How Are The One Time Address Details Stored In The Database Tables? ( Doc ID 555644.1 )

       

      Thanks.

        • 1. Re: Where is the One Time Address Details for PO stored in the database tables?
          Sanjay Desai EBS

          This is the table (POR_ITEM_ATTRIBUTE_VALUES) is in iProcurement.

          • 2. Re: Where is the One Time Address Details for PO stored in the database tables?
            skull

            Thanks Sanjay for responding.  However this table only has the Requisition information, not PO.  Here is the table structure.

             

            sql> desc ICX.POR_ITEM_ATTRIBUTE_VALUES

            Name                                      Null?    Type

            ----------------------------------------- -------- ----------------------------

            ITEM_TYPE                                 NOT NULL VARCHAR2(15)

            REQUISITION_HEADER_ID                     NOT NULL NUMBER

            REQUISITION_LINE_ID                       NOT NULL NUMBER

            ATTRIBUTE1                                         VARCHAR2(240)

            ATTRIBUTE2                                         VARCHAR2(240)

            ATTRIBUTE3                                         VARCHAR2(240)

            ATTRIBUTE4                                         VARCHAR2(240)

            ATTRIBUTE5                                         VARCHAR2(240)

            ATTRIBUTE6                                         VARCHAR2(240)

            ATTRIBUTE7                                         VARCHAR2(240)

            ATTRIBUTE8                                         VARCHAR2(240)

            ATTRIBUTE9                                         VARCHAR2(240)

            ATTRIBUTE10                                        VARCHAR2(240)

            ATTRIBUTE11                                        VARCHAR2(240)

            ATTRIBUTE12                                        VARCHAR2(240)

            ATTRIBUTE13                                        VARCHAR2(240)

            ATTRIBUTE14                                        VARCHAR2(240)

            ATTRIBUTE15                                        VARCHAR2(240)

            ORG_ID                                             NUMBER

            CREATED_BY                                         NUMBER

            CREATION_DATE                                      DATE

            LAST_UPDATED_BY                           NOT NULL NUMBER

            LAST_UPDATE_DATE                          NOT NULL DATE

            LAST_UPDATE_LOGIN                                  NUMBER

            • 3. Re: Where is the One Time Address Details for PO stored in the database tables?
              Mat Tyndall-Oracle

              See this note on MOS : How Are The One Time Address Details Stored In The Database Tables? (Doc ID 555644.1)

               

              As the user checks out in iProcurement and chooses the option to create a one time address, the details of the address which the user enters is stored in the table por_item_attribute_values.

              The following query would show what the user has entered:

               

              select prh.segment1, prl.line_num, por.attribute1
              from por_item_attribute_values por, po_requisition_headers_all prh, po_requisition_lines_all prl
              where por.requisition_header_id = prh.requisition_header_id
              and prh.requisition_header_id = prl.requisition_header_id
              and por.requisition_line_id = prl.requisition_line_id
              order by prh.creation_date desc;


              ___________________________________________________________________

              After the requisition is Approved - the Requisition Approval Workflow creates the value stored into an attachment on the Requisition line - which eventually is presented on the Purchase Order Shipment after Autocreate.

              The following SQL can be used to view the attachment that is created:

               

              SET LONG 32000
              select prh.segment1, f3.long_text
              from
              fnd_attached_documents f1,
              po_requisition_headers_all prh,
              po_requisition_lines_all prl,
              fnd_documents_tl f2,
              fnd_documents_long_text f3
              where prh.requisition_header_id = prl.requisition_header_id
              and f1.pk1_value = TO_CHAR(prl.requisition_line_id)
              and f1.document_id = f2.document_id
              and f2.media_id  = f3.media_id
              and pk2_value = 'ONE_TIME_LOCATION';



              ___________________________________________________________________

              If it is necessary to track all requisitions that have been created and approved - and are utilizing the one time address, then the following SQL can be considered:
               

              select prh.segment1, hr.name, prl.deliver_to_location_id, hr1.location_code
              from
              po_requisition_headers_all prh, hr_all_organization_units hr, po_requisition_lines_all prl,
              hr_locations_all hr1
              where
              prh.org_id = hr.organization_id and
              prh.requisition_header_id = prl.requisition_header_id and
              prl.deliver_to_location_id = hr1.location_id and
              hr1.location_code in
              (select
                  v.profile_option_value VALUE
                from
                 fnd_profile_options p,
                 fnd_profile_option_values v,
                 fnd_profile_options_tl n,
                 fnd_user usr,
                 fnd_application app,
                 fnd_responsibility rsp,
                 fnd_nodes svr,
                 hr_operating_units org
              where
                 p.profile_option_id = v.profile_option_id
                 and n.profile_option_name = 'POR_ONE_TIME_LOCATION'
                 and p.profile_option_name = n.profile_option_name
                 and usr.user_id = v.level_value
                 and rsp.application_id = v.level_value_application_id
                 and rsp.responsibility_id = v.level_value
                 and app.application_id = v.level_value
                 and svr.node_id = v.level_value
                 and org.organization_id = v.level_value);

              1 person found this helpful
              • 4. Re: Where is the One Time Address Details for PO stored in the database tables?
                skull

                WOW -- thanks a lot Mat Tyndall-Oracle for the solution.  Strange that I still haven't got a clear answer on the SR.

                 

                Seems odd that Oracle has a dedicated table for Requisitions but using the FND tables for Purchase Orders.

                 

                Here is the final query I drafted.

                 

                select poh.segment1, poh.creation_date, poh.revised_date, docs.file_name, docs.title, ad.entity_name, docs.description, ltxt.long_text, ad.document_id, ad.pk1_value, ad.pk2_value
                from po.po_headers_all poh, po.po_line_locations_all pll, apps.FND_ATTACHED_DOCUMENTS ad, apps.FND_DOCUMENTS_VL docs, apps.FND_DOCUMENTS_LONG_TEXT ltxt
                where poh.po_header_id = pll.po_header_id
                --and poh.segment1 = '80906'
                and (ad.entity_name = 'PO_SHIPMENTS' and ad.pk1_value = pll.line_location_id)
                and ad.document_id = docs.document_id
                and docs.media_id = ltxt.media_id
                and ad.pk2_value = 'ONE_TIME_LOCATION'
                ;