Purchasing

Get Involved. Join the Conversation.

Topic

    Boyuan Zhu
    How to Include Back-to-back Sales Order Customer Information...
    Topic posted September 9, 2019 by Boyuan ZhuGold Medal: 3,500+ Points, tagged Orders, Supplier Communication 
    30 Views, 2 Comments
    Title:
    How to Include Back-to-back Sales Order Customer Information in the Purchase Order PDF
    Content:

    If you have a specialized back-to-back order process, you may need to communicate sales order customer information to your supplier. For example, you have asked your supplier to label packages with customer shipping information. You can configure the purchase order PDF data model and template to include customer information from the sales order. In the steps below, you will add the the customer name, address, and the scheduled arrival date to the purchase order data model.

    1. Create a copy of the Purchasing Order Data Model. Make the following edits to the draft and archive distribution data sets.  
    2. Add this statement to the SELECT clause:

      SalesOrder.PARTY_NAME CustomerName,

      SalesOrder.ADDRESS1 CustomerAddress1,

      SalesOrder.CITY CustomerCity,

      SalesOrder.STATE CustomerState,

      SalesOrder.SCHEDULE_ARRIVAL_DATE AS ScheduledArrivalDate

    3. Add this statement to the FROM clause:

      (SELECT ReqDist.DISTRIBUTION_ID, Customer.PARTY_NAME, CustomerLoc.ADDRESS1, CustomerLoc.CITY, CustomerLoc.STATE, FulfillLine.SCHEDULE_ARRIVAL_DATE FROM POR_REQ_DISTRIBUTIONS_ALL ReqDist, DOS_SUPPLY_TRACKING_LINE_DOCS TrackingDoc, DOS_SUPPLY_TRACKING_LINES TrackingLine, DOO_HEADERS_ALL SalesOrderHeader, HZ_PARTIES Customer, HZ_RELATIONSHIPS CustRel, DOO_FULFILL_LINES_ALL FulfillLine, HZ_PARTY_SITES CustomerSite, HZ_LOCATIONS CustomerLoc WHERE TrackingDoc.DOCUMENT_TYPE='ORA_PR' AND TrackingDoc.DOCUMENT_LINE_ID = ReqDist.REQUISITION_LINE_ID  AND TrackingLine.TRACKING_LINE_ID = TrackingDoc.TRACKING_LINE_ID AND TrackingDoc.ACTIVE_FLAG = 1 AND TrackingLine.SUPPLY_ORDER_REFERENCE_LINE_ID = FulfillLine.FULFILL_LINE_ID AND FulfillLine.HEADER_ID = SalesOrderHeader.HEADER_ID AND FulfillLine.SHIP_TO_PARTY_SITE_ID = CustomerSite.PARTY_SITE_ID AND CustomerSite.LOCATION_ID=CustomerLoc.LOCATION_ID AND SalesOrderHeader.SOLD_TO_PARTY_CONTACT_ID = CustRel.RELATIONSHIP_ID AND CustRel.SUBJECT_TYPE = 'PERSON' AND Customer.PARTY_ID = CustRel.SUBJECT_ID) SalesOrder

    4. Add this statement to the WHERE clause: 

      AND Distribution.REQ_DISTRIBUTION_ID = SalesOrder.DISTRIBUTION_ID(+)

    5. Make corresponding changes to add the attributes to the PDF template. If you are using Collaborative Messaging, you can also add the attributes to the CMK message transform.

    Comment

     

    • Deepika Nathany

      Thanks for the wonderful details.

      On your point 5. 

      Can you make changes to a CMK msg? Any simple pointer on where do I do this ?

      • Boyuan Zhu

        There's a few steps involved. You can refer to the Configure Message Maps section in the Oracle Collaboration Messaging Framework Cloud 17C What's New, listed under Oracle Supply Chain Collaboration and Visibility Cloud.