Supplier, Requisition, Purchase Order (PO) and Requisition Import Errors For A Drop Ship Order.sql

-- Obtain Requisition Numbers created for the Drop Ship Order
SELECT
	dfla.source_order_number
  , dfla.source_line_number
  , dfla.fulfill_line_number
  , dfla.status_code
  , dla.line_number
  , dla.display_line_number
  , ddr.DOC_USER_KEY "Requisition Number"
  , ddr.DOC_LINE_USER_KEY "Requisition Line Number"
  , ddr.doc_ref_type
  , ddr.doc_id
  , TO_CHAR(ddr.creation_date,'dd-mon-yyyy hh24:mi:ss')    cr_dt
  , TO_CHAR(ddr.last_update_date,'dd-mon-yyyy hh24:mi:ss') upd_dt
FROM
	fusion.doo_document_references ddr
  , fusion.doo_headers_all         dha
  , fusion.doo_lines_all           dla
  , fusion.doo_fulfill_lines_all   dfla
WHERE
	dha.header_id            = dfla.header_id
	AND dha.header_id        = ddr.header_id
	AND dfla.fulfill_line_id = ddr.fulfill_line_id
	AND submitted_flag       = 'Y'
	AND dha.source_order_number IN ('&SOURCE_ORDER_NUMBER')
	AND ddr.DOC_REF_TYPE = 'DROPSHIP_REQ_REFERENCE'
	AND dfla.line_id     = dla.line_id
ORDER BY
	dla.display_line_number ASC
-- Obtain Purchase Order Numbers created for the Drop Ship Order
SELECT
	dfla.source_order_number
  , dfla.source_line_number
  , dfla.fulfill_line_number
  , dfla.fulfill_line_id
  , dfla.status_code
  , dla.line_number
  , dla.display_line_number
  , ddr.DOC_USER_KEY "PO Number"
  , DOC_LINE_USER_KEY "PO Line Number"
  , DOC_SUBLINE_USER_KEY "PO Schedule Number"
  , ddr.doc_ref_type
  , ddr.doc_id
  , TO_CHAR(ddr.creation_date,'dd-mon-yyyy hh24:mi:ss')    cr_dt
  , TO_CHAR(ddr.last_update_date,'dd-mon-yyyy hh24:mi:ss') upd_dt
FROM
	fusion.doo_document_references ddr
  , fusion.doo_headers_all         dha
  , fusion.doo_lines_all           dla
  , fusion.doo_fulfill_lines_all   dfla
WHERE
	dha.header_id            = dfla.header_id
	AND dha.header_id        = ddr.header_id
	AND dfla.fulfill_line_id = ddr.fulfill_line_id
	AND submitted_flag       = 'Y'
	AND dha.source_order_number IN ('&SOURCE_ORDER_NUMBER')
	AND ddr.DOC_REF_TYPE = 'DROPSHIP_PO_REFERENCE'
	AND dfla.line_id     = dla.line_id
ORDER BY
	dla.display_line_number ASC
-- Obtain Review Requisition interface details, including errors
-- NOTE: This data may be purged by the requisition import process
SELECT
	dha.source_order_system
  , dfla.source_order_number
  , dha.order_number
  , dfla.source_line_number
  , dfla.fulfill_line_number
  , dfla.status_code
  , dla.line_number
  , dla.display_line_number
  , dha.header_id
  , dfla.fulfill_line_id
  , dla.line_id
  , dfla.FULFILL_LINE_NUMBER
  , prlia.REQUISITION_HEADER_ID
  , prlia.REQUISITION_LINE_ID
  , prie.MESSAGE_NAME
  , prie.TEXT_LINE
  , prie.COLUMN_NAME
  , prie.COLUMN_VALUE
  , prie.TOKEN_NAME1
  , prie.TOKEN_VALUE1
FROM
	fusion.POR_REQ_HEADERS_INTERFACE_ALL prhia
  , fusion.POR_REQ_LINES_INTERFACE_ALL   prlia
  , fusion.doo_headers_all               dha
  , fusion.doo_fulfill_lines_all         dfla
  , fusion.doo_lines_all                 dla
  , fusion.POR_REQ_IMPORT_ERRORS         PRIE
WHERE
	prhia.REQ_HEADER_INTERFACE_ID     = prlia.REQ_HEADER_INTERFACE_ID
	AND dha.header_id                 = dfla.header_id
	AND TO_CHAR(dha.header_id)        = prhia.INTERFACE_HEADER_KEY
	AND TO_CHAR(dfla.fulfill_line_id) = prlia.interface_LINE_KEY (+)
	AND prhia.batch_id                = prie.import_batch_id (+)
	AND prie.INTERFACE_KEY (+)        = prlia.interface_LINE_KEY
	AND dha.submitted_flag            = 'Y'
	AND dha.source_order_number IN ('&SOURCE_ORDER_NUMBER')
	AND dfla.line_id = dla.line_id
ORDER BY
	dla.display_line_number
-- Obtain Requisition Details
SELECT
	prla.po_header_id
  , prla.po_line_id
  , dha.source_order_system
  , dfla.source_order_number
  , dha.order_number
  , dfla.source_line_number
  , dfla.fulfill_line_number
  , dfla.status_code
  , dla.line_number
  , dla.display_line_number
  , dha.header_id
  , dfla.fulfill_line_id
  , dla.line_id
  , dfla.FULFILL_LINE_NUMBER
  , PRHA.REQUISITION_HEADER_ID
  , PRHA.REQUISITION_NUMBER
  , PRHA.DOCUMENT_STATUS
  , PRHA.PROCESS_STATUS
  , PRHA.REQ_BU_ID
  , PRHA.PREPARER_ID
  , PRHA.DESCRIPTION
  , PRLA.REQUISITION_LINE_ID
  , PRLA.SUGGESTED_VENDOR_NAME
  , PRLA.SUGGESTED_VENDOR_SITE
  , PRLA.VENDOR_ID
  , PRLA.VENDOR_SITE_ID
  , PRLA.ITEM_DESCRIPTION
  , PRLA.UOM_CODE
  , PRLA.UNIT_PRICE
FROM
	fusion.por_requisition_headers_all prha
  , fusion.por_requisition_lines_all   prla
  , fusion.doo_headers_all             dha
  , fusion.doo_fulfill_lines_all       dfla
  , fusion.doo_lines_all               dla
WHERE
	prha.requisition_header_id = prla.requisition_header_id
	AND dha.source_order_number IN ('&SOURCE_ORDER_NUMBER')
	AND dha.header_id                 = dfla.header_id
	AND TO_CHAR(dfla.fulfill_line_id) = prla.interface_LINE_KEY (+)
	AND dha.submitted_flag            = 'Y'
	AND dfla.line_id                  = dla.line_id
ORDER BY
	dla.display_line_number
-- Obtain Purchase Order Details Details
SELECT
	dha.source_order_system
  , dfla.source_order_number
  , dha.order_number
  , dfla.source_line_number
  , dfla.fulfill_line_number
  , dfla.status_code
  , dla.line_number
  , dla.display_line_number
  , dha.header_id
  , dfla.fulfill_line_id
  , dla.line_id
  , dfla.FULFILL_LINE_NUMBER
  , PRHA.REQUISITION_NUMBER
  , PRHA.DOCUMENT_STATUS
  , PRHA.PROCESS_STATUS
  , PRLA.REQUISITION_LINE_ID
  , pha.segment1 po_number
  , pha.DOCUMENT_STATUS
  , pla.line_num po_line_number
  , pla.LINE_STATUS
  , pla.item_description
  , plla.shipment_num
  , plla.QUANTITY
  , plla.QUANTITY_RECEIVED
  , plla.QUANTITY_ACCEPTED
  , plla.QUANTITY_REJECTED
  , plla.QUANTITY_BILLED
  , plla.QUANTITY_CANCELLED
FROM
	fusion.por_requisition_headers_all prha
  , fusion.por_requisition_lines_all   prla
  , fusion.doo_headers_all             dha
  , fusion.doo_fulfill_lines_all       dfla
  , fusion.doo_lines_all               dla
  , fusion.po_headers_all              pha
  , fusion.po_lines_all                pla
  , fusion.po_line_locations_all       plla
WHERE
	prha.requisition_header_id = prla.requisition_header_id
	AND dha.source_order_number IN ('&SOURCE_ORDER_NUMBER')
	AND dha.header_id                 = dfla.header_id
	AND TO_CHAR(dfla.fulfill_line_id) = prla.interface_LINE_KEY (+)
	AND dha.submitted_flag            = 'Y'
	AND dfla.line_id                  = dla.line_id
	AND pha.po_header_id              = prla.po_header_id
	AND pla.po_line_id                = prla.po_line_id
	AND plla.po_line_id (+)           = pla.po_line_id
ORDER BY
	dla.display_line_number
-- Obtain Supplier Information using Vendor ID on Requisition
SELECT
	hzp.party_name
FROM
	fusion.POZ_SUPPLIERS PS
  , fusion.HZ_PARTies    HZP
WHERE
	vendor_id        = XXXXXXXXXXXXXX
	AND hzp.party_id = ps.party_id
SELECT
	hzp.party_name
  , Ps.vendor_id
  , pssam.VENDOR_SITE_CODE
  , pssam.vendor_site_id
FROM
	fusion.POZ_SUPPLIERS            PS
  , fusion.POZ_SUPPLIER_SITES_ALL_M PSSAM
  , fusion.HZ_PARTies               HZP
WHERE
	(
		ps.vendor_id =
		&VENDOR_ID
		OR upper(party_name) LIKE upper('&VENDOR_NAME')
	)
	AND hzp.party_id = ps.party_id
	AND ps.Vendor_id = pssam.VENDOR_ID