Friday, August 5, 2011

PO ALL RECEIPTS Query

SELECT rcv_shipment_lines.shipment_line_id, po_vendors.vendor_name,
       DECODE (rcv_shipment_headers.vendor_site_id,
               NULL, NULL,
               po_vendor_sites_all.vendor_site_code
              ) vendor_site,
       po_vendor_sites_all.address_line1, po_vendor_sites_all.address_line2,
       po_vendor_sites_all.address_line3, po_vendor_sites_all.city,
       po_vendor_sites_all.state, po_vendor_sites_all.country,
       DECODE (rcv_shipment_lines.po_header_id,
               NULL, 'Unordered',
               'Ordered'
              ) AS receipt_type,
       rcv_shipment_headers.receipt_num,
       rcv_shipment_headers.creation_date AS receipt_date,
       rcv_shipment_lines.ship_to_location_id,
          hr_locations.location_code
       || '-'
       || hr_locations.description AS ship_to_location,
       rcv_shipment_headers.ship_to_org_id,
       hr_all_organization_units_tl.NAME AS ORGANIZATION,
       rcv_shipment_headers.employee_id, per_all_people_f.full_name,
       rcv_shipment_headers.packing_slip, rcv_shipment_headers.shipped_date,
       rcv_shipment_headers.freight_carrier_code,
       rcv_shipment_headers.freight_terms, rcv_shipment_lines.category_id,
          mtl_categories_v.segment1
       || '.'
       || mtl_categories_v.segment2 AS category_subcat,
       rcv_shipment_lines.quantity_shipped,
       rcv_shipment_lines.quantity_received,
       rcv_shipment_lines.unit_of_measure,
       rcv_shipment_lines.item_description, po_headers.segment1 AS po_num,
       po_headers.creation_date AS po_date, rcv_shipment_lines.po_line_id,
       rcv_shipment_lines.po_distribution_id,
       rcv_shipment_lines.requisition_line_id,
       rcv_shipment_lines.deliver_to_person_id,
       disc_rcv_delivery.delivery_date,
       rcv_shipment_headers.comments AS header_note,
       disc_po_distribution_dsc.gl_name_seg1,
       disc_po_distribution_dsc.gl_name_seg2,
       disc_po_distribution_dsc.gl_name_seg3,
       disc_po_distribution_dsc.gl_name_seg4,
       disc_po_distribution_dsc.gl_name_seg5,
       disc_po_distribution_dsc.gl_name_seg6,
       disc_po_distribution_dsc.quantity_ordered,
       disc_po_distribution_dsc.quantity_billed,
       disc_po_distribution_dsc.quantity_delivered, po_lines_all.unit_price,
       po_headers.currency_code, po_line_locations_all.promised_date,
       DECODE (rcv_shipment_lines.routing_header_id,
               2, 'Yes',
               'No'
              ) AS inspection_required,
       disc_rcv_acceptance.inspection_status_code,
       disc_rcv_return.return_date,
       disc_rcv_return.transaction_type AS return_transaction_code
  FROM rcv_shipment_headers rcv_shipment_headers,
       rcv_shipment_lines rcv_shipment_lines,
       disc_rcv_delivery disc_rcv_delivery,
       disc_rcv_return disc_rcv_return,
       po_vendors po_vendors,
       po_vendor_sites_all po_vendor_sites_all,
       hr_locations hr_locations,
       hr_all_organization_units_tl hr_all_organization_units_tl,
       per_all_people_f per_all_people_f,
       po_headers_all po_headers,
       mtl_categories_v mtl_categories_v,
       disc_po_distribution_dsc disc_po_distribution_dsc,
       po_lines_all po_lines_all,
       po_line_locations_all po_line_locations_all,
       disc_rcv_acceptance
 WHERE rcv_shipment_headers.shipment_header_id =
                                         rcv_shipment_lines.shipment_header_id
   AND rcv_shipment_headers.vendor_id = po_vendors.vendor_id
   AND po_vendors.vendor_id = po_vendor_sites_all.vendor_id
   AND NVL (rcv_shipment_headers.vendor_site_id,
            (SELECT MAX (s.vendor_site_id)
               FROM po_vendor_sites_all s
              WHERE s.vendor_id = po_vendors.vendor_id)
           ) = po_vendor_sites_all.vendor_site_id
   AND rcv_shipment_lines.ship_to_location_id = hr_locations.location_id
   AND rcv_shipment_headers.ship_to_org_id =
                                  hr_all_organization_units_tl.organization_id
   AND rcv_shipment_headers.employee_id = per_all_people_f.person_id(+)
   AND rcv_shipment_lines.po_header_id = po_headers.po_header_id(+)
   AND rcv_shipment_lines.po_distribution_id = disc_po_distribution_dsc.po_distribution_id(+)
   AND rcv_shipment_lines.shipment_header_id = disc_rcv_delivery.shipment_header_id(+)
   AND rcv_shipment_lines.shipment_line_id = disc_rcv_delivery.shipment_line_id(+)
   AND rcv_shipment_lines.po_line_id = po_lines_all.po_line_id(+)
   AND rcv_shipment_lines.po_line_location_id = po_line_locations_all.line_location_id(+)
   AND mtl_categories_v.category_id = rcv_shipment_lines.category_id
   AND rcv_shipment_lines.shipment_line_id = disc_rcv_acceptance.shipment_line_id(+)
   AND rcv_shipment_lines.shipment_line_id = disc_rcv_return.shipment_line_id(+)
   AND DECODE (rcv_shipment_headers.employee_id,
               NULL, SYSDATE,
               rcv_shipment_headers.creation_date
              ) BETWEEN DECODE (rcv_shipment_headers.employee_id,
                                NULL, SYSDATE,
                                per_all_people_f.effective_start_date
                               )
                    AND DECODE (rcv_shipment_headers.employee_id,
                                NULL, SYSDATE,
                                per_all_people_f.effective_end_date

                               )

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect