6 Replies Latest reply on Oct 12, 2011 1:19 PM by Sandeep Gandhi, Consultant

    delivery term in TERMS

    makdutakdu
      hi all

      in PO abc super user responsibility ,when the autocreate to Purchase orders there is this TERMS tab in the bottom
      in which the delivery term and shipment term is entered,

      kindly help me to get the table for this delivery term and shipment term

      thanking in advance
        • 1. Re: delivery term in TERMS
          Sandeep Gandhi, Consultant
          Payment terms are in ap_terms
          • 2. Re: delivery term in TERMS
            Sandeep Gandhi, Consultant
            Freight terms are in fnd_lookup_values where lookup_type='FREIGHT TERMS'
            1 person found this helpful
            • 3. Re: delivery term in TERMS
              makdutakdu
              hi


              thank you very much for your guidance
              i have one doubt though
              this is the query for po_pending to receive
              SELECT DISTINCT pha.segment1 po_num, pla.line_num, pha.authorization_status,
                              plla.quantity qty_ord, plla.quantity_received,
                              plla.quantity_accepted,
                              (plla.quantity - plla.quantity_received) pending_qty,
                              pha.approved_date, TO_CHAR (pha.approved_date, 'YYYY') years,
                              TO_CHAR (pha.approved_date, 'MON') months,
                              pha.currency_code ccy, avv.vendor_name,
                              napesco_exchange_rat
                                       (pha.currency_code,
                                        'KWD',
                                        TO_DATE (TO_CHAR (pha.creation_date, 'MM/DD/YYYY'),
                                                 'MM/DD/YYYY'
                                                )
                                       ) exchange_rate,
                              msi.segment1, msi.segment2, msi.segment3, msi.description,
                              pla.unit_meas_lookup_code,
                              pha.freight_terms_lookup_code delivery_terms
                         FROM po_headers_all pha,
                              po_lines_all pla,
                              po_line_locations_all plla,
                              mtl_system_items msi,
                              ap_vendors_v avv
                        WHERE pha.po_header_id = pla.po_header_id
                          AND pha.po_header_id = plla.po_header_id
                          AND pla.po_line_id = plla.po_line_id
                          AND pha.type_lookup_code = 'STANDARD'
                          AND pha.vendor_id = avv.vendor_id
                          AND pha.closed_code = 'OPEN'
                          AND plla.quantity > plla.quantity_received
                          AND plla.quantity_cancelled = 0
                          AND plla.quantity_rejected = 0
                          AND pha.authorization_status IN ('APPROVED')
                          AND msi.inventory_item_id = pla.item_id
                          AND plla.closed_code NOT IN ('CLOSED', 'FINALLY CLOSED')
                          AND NVL (plla.cancel_flag, 'N') != 'Y'
                       HAVING (plla.quantity - plla.quantity_received) != 0
                     GROUP BY plla.quantity,
                              plla.quantity_received,
                              plla.quantity_accepted,
                              pha.approved_date,
                              msi.segment1,
                              msi.description,
                              pha.segment1,
                              pla.line_num,
                              pha.authorization_status,
                              pha.currency_code,
                              avv.vendor_name,
                              msi.segment2,
                              msi.segment3,
                              pla.unit_meas_lookup_code,
                              pha.freight_terms_lookup_code,
                              pha.creation_date
              in this query i have to insert flv.meaning from

              select flv.meaning ,flv.lookup_code from fnd_lookup_values flv where lookup_type='FREIGHT TERMS'

              is this the only join i have to perform as shown below
              select pha.FREIGHT_TERMS_LOOKUP_CODE ,flv.meaning ,flv.lookup_code from po_headers_all pha,fnd_lookup_values flv 
              where lookup_type='FREIGHT TERMS'
              and pha.FREIGHT_TERMS_LOOKUP_CODE=flv.lookup_code(+)
              when i incorporate this join in the main query
              at this time i see a decrease in record count

              kindly guide me
              thanking in advance
              • 4. Re: delivery term in TERMS
                Sandeep Gandhi, Consultant
                Try

                where flv.lookup_type(+)='FREIGHT TERMS'
                and pha.FREIGHT_TERMS_LOOKUP_CODE=flv.lookup_code(+)

                Hope this helps,
                Sandeep Gandhi
                • 5. Re: delivery term in TERMS
                  makdutakdu
                  hi

                  thank u very much for your guidance :)

                  in PO abc super user responsibility there is a Purchase Order form ,
                  in that form there is a field named Total

                  when i did record history it says po_headers_v
                  but i am not able to find tht field

                  could you please tell me the table to find it

                  thanking in advance

                  Edited by: makdutakdu on Oct 12, 2011 10:23 AM
                  • 6. Re: delivery term in TERMS
                    Sandeep Gandhi, Consultant
                    Some fields such as total are not stored in the view/table.
                    They are calculated on the fly by Oracle.
                    If you need a total, you will have to write code to do that. In your case, it is probably quantity*price for all non-cancelled orders.

                    Hope this helps,
                    Sandeep Gandhi