Hello ,
I’m working on WO Component Report and adding Po No’s to Production Order data source using Syntax Join . The join was successfully tested i was able to see the PO_HDR table in our data source . However , when i add the po’no’s i dont see any data but we have all the data related to the PO’Nos .
Here’s the join i used
LEFT JOIN po_hdr on (po_hdr.order_date = prod_order_hdr.order_date)
SELECT
TOP 100 ' ' placeholder,
prod_order_hdr.prod_order_number,
prod_order_hdr.order_date,
oe_hdr.order_no,
prod_order_hdr.source_location_id,
location.location_name,
inv_mast.item_id,
inv_mast.item_desc,
component_item_info.item_id,
component_item_info.item_desc,
prod_order_hdr.company_id,
oe_hdr.order_date,(
CASE WHEN prod_order_line_component.service_labor_uid IS NULL THEN prod_order_line_component.qty_used ELSE prod_order_line_comp_labor.hours_worked END
) AS actual_qty,(
CASE WHEN prod_order_line_component.service_labor_uid IS NULL THEN 'Item Component' ELSE 'Labor' END
) AS component_type,
oe_hdr.date_created,
prod_order_line_component.qty_requested,
prod_order_line_component.line_number,
prod_order_line_component.prod_order_number,
oe_hdr.original_promise_date,
oe_hdr.taker,
oe_hdr.source_id,
oe_hdr.source_location_id,
comp_class_id1.class_description,
comp_class_id2.class_description,
comp_class_id3.class_description,
comp_class_id4.class_description,
comp_class_id5.class_description,
class_id1.class_description,
class_id2.class_description,
class_id3.class_description,
class_id4.class_description,
class_id5.class_description,
prod_order_hdr.approved,
prod_order_line.cancel,
customer.customer_name,
prod_order_line.qty_to_make,
oe_hdr.promise_date,
oe_line.disposition,
prod_order_line.deposit_bin,
prod_order_line_component.qty_canceled,
prod_order_hdr.expected_completion_date,
oe_hdr.order_priority_uid,
prod_order_hdr.release_date,
prod_order_line_component.qty_allocated,
prod_order_line_component.qty_needed,
prod_order_line_component.qty_on_pick_tickets,
po_hdr.po_no,
po_hdr.dts_original_po_no,
prod_order_line_component.date_created,
po_hdr.order_date,
po_hdr.date_created,
po_hdr.expected_date,
po_hdr.expected_ship_date,
po_hdr.date_due,
po_hdr_notepad.note,
address.name,
inventory_supplier.supplier_id,
prod_order_hdr.entered_by,
po_hdr_notepad.po_no
FROM
prod_order_hdr
INNER JOIN prod_order_line ON prod_order_line.prod_order_number = prod_order_hdr.prod_order_number
AND COALESCE(prod_order_line.cancel, 'N') <> 'Y'
AND prod_order_line.completed = 'N'
INNER JOIN prod_order_line_component ON prod_order_line_component.prod_order_number = prod_order_line.prod_order_number
AND prod_order_line_component.line_number = prod_order_line.line_number
AND COALESCE(prod_order_line_component.disposition, '') <> 'C'
INNER JOIN (
SELECT
prod_order_line_component.prod_order_line_component_uid,
COALESCE(service_labor.service_labor_id, inv_mast.item_id) 'item_id',
COALESCE(
service_labor.service_labor_desc,
inv_mast.item_desc
) 'item_desc'
FROM
prod_order_line_component
INNER JOIN inv_mast ON inv_mast.inv_mast_uid = prod_order_line_component.inv_mast_uid
LEFT JOIN service_labor ON prod_order_line_component.service_labor_uid = service_labor.service_labor_uid
) AS component_item_info ON component_item_info.prod_order_line_component_uid = prod_order_line_component.prod_order_line_component_uid
INNER JOIN inv_mast ON prod_order_line.inv_mast_uid = inv_mast.inv_mast_uid
INNER JOIN inv_mast AS inv_mast_comp ON prod_order_line_component.inv_mast_uid = inv_mast_comp.inv_mast_uid
INNER JOIN inventory_supplier ON prod_order_line_component.supplier_id = inventory_supplier.supplier_id
AND inv_mast_comp.inv_mast_uid = inventory_supplier.inv_mast_uid
INNER JOIN location ON prod_order_hdr.source_location_id = location.location_id
LEFT JOIN prod_order_line_comp_labor ON prod_order_line_component.prod_order_line_component_uid = prod_order_line_comp_labor.prod_order_line_component_uid
LEFT JOIN prod_order_line_link ON prod_order_line_link.prod_order_number = prod_order_line.prod_order_number
AND prod_order_line_link.prod_order_line_number = prod_order_line.line_number
AND prod_order_line_link.trans_type = 'O'
LEFT JOIN oe_line ON oe_line.oe_line_uid = prod_order_line_link.transaction_uid
LEFT JOIN oe_hdr ON oe_line.order_no = oe_hdr.order_no
LEFT JOIN customer ON oe_hdr.customer_id = customer.customer_id
AND oe_hdr.company_id = customer.company_id
LEFT JOIN contacts ON (contacts.id = oe_hdr.contact_id)
LEFT JOIN company ON (prod_order_hdr.company_id = company.company_id)
LEFT JOIN service_technician ON (
prod_order_line_comp_labor.service_technician_uid = service_technician.service_technician_uid
)
LEFT JOIN contacts AS contacts_2 ON (service_technician.contacts_id = contacts_2.id)
LEFT JOIN class AS class_id1 ON (inv_mast.class_id1 = class_id1.class_id)
AND (class_id1.class_type = 'IV')
AND (class_id1.class_number = 1)
LEFT JOIN class AS class_id2 ON (inv_mast.class_id2 = class_id2.class_id)
AND (class_id2.class_type = 'IV')
AND (class_id2.class_number = 2)
LEFT JOIN class AS class_id3 ON (inv_mast.class_id3 = class_id3.class_id)
AND (class_id3.class_type = 'IV')
AND (class_id3.class_number = 3)
LEFT JOIN class AS class_id4 ON (inv_mast.class_id4 = class_id4.class_id)
AND (class_id4.class_type = 'IV')
AND (class_id4.class_number = 4)
LEFT JOIN class AS class_id5 ON (inv_mast.class_id5 = class_id5.class_id)
AND (class_id5.class_type = 'IV')
AND (class_id5.class_number = 5)
LEFT JOIN class AS comp_class_id1 ON (
inv_mast_comp.class_id1 = comp_class_id1.class_id
)
AND (comp_class_id1.class_type = 'IV')
AND (comp_class_id1.class_number = 1)
LEFT JOIN class AS comp_class_id2 ON (
inv_mast_comp.class_id2 = comp_class_id2.class_id
)
AND (comp_class_id2.class_type = 'IV')
AND (comp_class_id2.class_number = 2)
LEFT JOIN class AS comp_class_id3 ON (
inv_mast_comp.class_id3 = comp_class_id3.class_id
)
AND (comp_class_id3.class_type = 'IV')
AND (comp_class_id3.class_number = 3)
LEFT JOIN class AS comp_class_id4 ON (
inv_mast_comp.class_id4 = comp_class_id4.class_id
)
AND (comp_class_id4.class_type = 'IV')
AND (comp_class_id4.class_number = 4)
LEFT JOIN class as comp_class_id5 ON (
inv_mast_comp.class_id5 = comp_class_id5.class_id
)
AND (comp_class_id5.class_type = 'IV')
AND (comp_class_id5.class_number = 5)
LEFT JOIN po_hdr on (po_hdr.order_date = prod_order_hdr.order_date)
LEFT JOIN po_hdr_notepad on (po_hdr_notepad.po_no = po_hdr.po_no)
LEFT JOIN address ON (address.id = inventory_supplier.supplier_id)
the PO’Nos .