WIP MES Job Traveler Data Template

 Select 
          'Items' Source,
          FL.FILE_NAME Name,
	  FAD.SEQ_NUM Attachment_sequence,
 	  fd.DESCRIPTION Description,
	  null operation_sequence
   FROM fnd_attached_documents fad,
        fnd_documents_vl fd,
        fnd_lobs fl
   WHERE entity_name = 'MTL_SYSTEM_ITEMS'
   AND   pk1_value= to_char(:p_organization_id) 
   AND   pk2_value= to_char(:p_assembly_item_id) 
       AND fad.document_id = fd.document_id
   AND fd.media_id = fl.file_id
   AND Fl.file_content_type = 'application/pdf'
   union
   Select 
          'BOM' Source,
          FL.FILE_NAME Name,
	  FAD.SEQ_NUM Attachment_sequence,
 	  fd.DESCRIPTION Description,
  	  null operation_sequence
   FROM fnd_attached_documents fad,
        fnd_documents_vl fd,
        fnd_lobs fl
   WHERE entity_name = 'BOM_BILL_OF_MATERIALS'
   AND   pk1_value= to_char(:p_bill_sequence_id) 
   AND fad.document_id = fd.document_id
   AND fd.media_id = fl.file_id
   AND Fl.file_content_type = 'application/pdf'
   union
   Select 
          'Routings' Source,
          FL.FILE_NAME Name,
	  FAD.SEQ_NUM Attachment_sequence,
 	  fd.DESCRIPTION Description,
	  null operation_sequence
   FROM fnd_attached_documents fad,
        fnd_documents_vl fd,
        fnd_lobs fl
   WHERE entity_name = 'BOM_OPERATIONAL_ROUTINGS'
   AND   pk1_value= to_char(:p_rtg_sequence_id) 
   AND fad.document_id = fd.document_id
   AND fd.media_id = fl.file_id
   AND Fl.file_content_type = 'application/pdf'
   union
   Select 
          'Discrete Jobs' Source,
          FL.FILE_NAME Name,
	  FAD.SEQ_NUM Attachment_sequence,
 	  fd.DESCRIPTION Description,
	  null operation_sequence
   FROM fnd_attached_documents fad,
        fnd_documents_vl fd,
        fnd_lobs fl
   WHERE entity_name = 'WIP_DISCRETE_JOBS'
   AND   pk1_value= to_char(:p_wip_entity_id) 
   AND   Pk2_value= to_char(:p_organization_id) 
   AND fad.document_id = fd.document_id
   AND fd.media_id = fl.file_id
   AND Fl.file_content_type = 'application/pdf'
   union
   Select 
          'Operations' Source,
          FL.FILE_NAME Name,
	  FAD.SEQ_NUM Attachment_sequence,
 	  fd.DESCRIPTION Description,
	  woo.operation_seq_num operation_sequence
   FROM fnd_attached_documents fad,
        fnd_documents_vl fd,
        fnd_lobs fl,
	wip_operations woo
   WHERE entity_name = 'WIP_DISCRETE_OPERATIONS'
   and   woo.wip_entity_id =:p_wip_entity_id
   AND   pk1_value= to_char(woo.wip_entity_id) 
   AND   Pk2_value= to_char(woo.operation_seq_num) 
   AND   pk3_value = to_char(woo.organization_id) 
   AND fad.document_id = fd.document_id
   AND fd.media_id = fl.file_id
   AND Fl.file_content_type = 'application/pdf'
 union
   Select 
          'Sales Order Lines' Source,
          FL.FILE_NAME Name,
	  FAD.SEQ_NUM Attachment_sequence,
 	  fd.DESCRIPTION Description,
	  null operation_sequence
   FROM fnd_attached_documents fad,
        fnd_documents_vl fd,
        fnd_lobs fl
    WHERE entity_name = 'OE_ORDER_HEADERS'
    AND   Pk1_value IN (select to_char(header_id) 
                            from oe_order_lines_all 
                            where line_id in ( 
                                   select mr.demand_source_line_id
                                   from mtl_reservations mr
                                   where mr.demand_source_type_id in (2,8)
                                   and mr.supply_source_type_id = 5
                                   and mr.supply_source_header_id = :p_wip_entity_id
                                   and mr.organization_id = :p_organization_id)   
                            )
        AND fad.document_id = fd.document_id
   AND fd.media_id = fl.file_id
   AND Fl.file_content_type = 'application/pdf'
 union
   Select 
          'Discrete Jobs' Source,
          FL.FILE_NAME Name,
	  FAD.SEQ_NUM Attachment_sequence,
 	  fd.DESCRIPTION Description,
	  null operation_sequence
   FROM fnd_attached_documents fad,
        fnd_documents_vl fd,
        fnd_lobs fl
	WHERE entity_name = 'OE_ORDER_LINES'
    AND   Pk1_value IN (
                         select to_char(mr.demand_source_line_id)
                         from mtl_reservations mr
                         where mr.demand_source_type_id in (2,8)
                         and mr.supply_source_type_id = 5
                         and mr.supply_source_header_id = :p_wip_entity_id
                         and mr.organization_id = :p_organization_id
                        )
        AND fad.document_id = fd.document_id
   AND fd.media_id = fl.file_id
   AND Fl.file_content_type = 'application/pdf'  
  union
   Select 
          'Components' Source,
          FL.FILE_NAME Name,
	  FAD.SEQ_NUM Attachment_sequence,
 	  fd.DESCRIPTION Description,
	  null operation_sequence
   FROM fnd_attached_documents fad,
        fnd_documents_vl fd,
        fnd_lobs fl
   WHERE entity_name = 'MTL_SYSTEM_ITEMS'
   AND   pk1_value= to_char(:p_organization_id) 
   AND   pk2_value IN ( select to_char(inventory_item_id)  
                           from wip_requirement_operations
                          where wip_entity_id = :p_wip_entity_id
                          and operation_seq_num = :OP_SEQ_NUM
                          and organization_id = :p_organization_id)  
   AND fad.document_id = fd.document_id
   AND fd.media_id = fl.file_id
   AND Fl.file_content_type = 'application/pdf'
   ORDER BY OPERATION_SEQUENCE, ATTACHMENT_SEQUENCE