CST_XLA_SHIPMENT_DELIVERIES

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
CST_INV_TRANSACTION_ID SHIP_HDR_ATTRIBUTE_CATEGORY SHIP_HDR_ATTRIBUTE1 SHIP_HDR_ATTRIBUTE2 SHIP_HDR_ATTRIBUTE3 SHIP_HDR_ATTRIBUTE4 SHIP_HDR_ATTRIBUTE5 SHIP_HDR_ATTRIBUTE6 SHIP_HDR_ATTRIBUTE7 SHIP_HDR_ATTRIBUTE8 SHIP_HDR_ATTRIBUTE9 SHIP_HDR_ATTRIBUTE10 SHIP_HDR_ATTRIBUTE11 SHIP_HDR_ATTRIBUTE12 SHIP_HDR_ATTRIBUTE13 SHIP_HDR_ATTRIBUTE14 SHIP_HDR_ATTRIBUTE15 SHIP_HDR_ATTRIBUTE16 SHIP_HDR_ATTRIBUTE17 SHIP_HDR_ATTRIBUTE18 SHIP_HDR_ATTRIBUTE19 SHIP_HDR_ATTRIBUTE20 SHIP_HDR_ATTRIBUTE_NUMBER1 SHIP_HDR_ATTRIBUTE_NUMBER2 SHIP_HDR_ATTRIBUTE_NUMBER3 SHIP_HDR_ATTRIBUTE_NUMBER4 SHIP_HDR_ATTRIBUTE_NUMBER5 SHIP_HDR_ATTRIBUTE_NUMBER6 SHIP_HDR_ATTRIBUTE_NUMBER7 SHIP_HDR_ATTRIBUTE_NUMBER8 SHIP_HDR_ATTRIBUTE_NUMBER9 SHIP_HDR_ATTRIBUTE_NUMBER10 SHIP_HDR_ATTRIBUTE_DATE1 SHIP_HDR_ATTRIBUTE_DATE2 SHIP_HDR_ATTRIBUTE_DATE3 SHIP_HDR_ATTRIBUTE_DATE4 SHIP_HDR_ATTRIBUTE_DATE5 SHIP_HDR_ATTRIBUTE_TIMESTAMP1 SHIP_HDR_ATTRIBUTE_TIMESTAMP2 SHIP_HDR_ATTRIBUTE_TIMESTAMP3 SHIP_HDR_ATTRIBUTE_TIMESTAMP4 SHIP_HDR_ATTRIBUTE_TIMESTAMP5

Name

CST_INV_TRANSACTION_ID

SHIP_HDR_ATTRIBUTE_CATEGORY

SHIP_HDR_ATTRIBUTE1

SHIP_HDR_ATTRIBUTE2

SHIP_HDR_ATTRIBUTE3

SHIP_HDR_ATTRIBUTE4

SHIP_HDR_ATTRIBUTE5

SHIP_HDR_ATTRIBUTE6

SHIP_HDR_ATTRIBUTE7

SHIP_HDR_ATTRIBUTE8

SHIP_HDR_ATTRIBUTE9

SHIP_HDR_ATTRIBUTE10

SHIP_HDR_ATTRIBUTE11

SHIP_HDR_ATTRIBUTE12

SHIP_HDR_ATTRIBUTE13

SHIP_HDR_ATTRIBUTE14

SHIP_HDR_ATTRIBUTE15

SHIP_HDR_ATTRIBUTE16

SHIP_HDR_ATTRIBUTE17

SHIP_HDR_ATTRIBUTE18

SHIP_HDR_ATTRIBUTE19

SHIP_HDR_ATTRIBUTE20

SHIP_HDR_ATTRIBUTE_NUMBER1

SHIP_HDR_ATTRIBUTE_NUMBER2

SHIP_HDR_ATTRIBUTE_NUMBER3

SHIP_HDR_ATTRIBUTE_NUMBER4

SHIP_HDR_ATTRIBUTE_NUMBER5

SHIP_HDR_ATTRIBUTE_NUMBER6

SHIP_HDR_ATTRIBUTE_NUMBER7

SHIP_HDR_ATTRIBUTE_NUMBER8

SHIP_HDR_ATTRIBUTE_NUMBER9

SHIP_HDR_ATTRIBUTE_NUMBER10

SHIP_HDR_ATTRIBUTE_DATE1

SHIP_HDR_ATTRIBUTE_DATE2

SHIP_HDR_ATTRIBUTE_DATE3

SHIP_HDR_ATTRIBUTE_DATE4

SHIP_HDR_ATTRIBUTE_DATE5

SHIP_HDR_ATTRIBUTE_TIMESTAMP1

SHIP_HDR_ATTRIBUTE_TIMESTAMP2

SHIP_HDR_ATTRIBUTE_TIMESTAMP3

SHIP_HDR_ATTRIBUTE_TIMESTAMP4

SHIP_HDR_ATTRIBUTE_TIMESTAMP5

Query

SQL_Statement
SELECT cit.cst_inv_transaction_id cst_inv_transaction_id, wnd.attribute_category ship_hdr_attribute_category, wnd.attribute1 ship_hdr_attribute1, wnd.attribute2 ship_hdr_attribute2, wnd.attribute3 ship_hdr_attribute3, wnd.attribute4 ship_hdr_attribute4, wnd.attribute5 ship_hdr_attribute5, wnd.attribute6 ship_hdr_attribute6, wnd.attribute7 ship_hdr_attribute7, wnd.attribute8 ship_hdr_attribute8, wnd.attribute9 ship_hdr_attribute9, wnd.attribute10 ship_hdr_attribute10, wnd.attribute11 ship_hdr_attribute11, wnd.attribute12 ship_hdr_attribute12, wnd.attribute13 ship_hdr_attribute13, wnd.attribute14 ship_hdr_attribute14, wnd.attribute15 ship_hdr_attribute15, wnd.attribute16 ship_hdr_attribute16, wnd.attribute17 ship_hdr_attribute17, wnd.attribute18 ship_hdr_attribute18, wnd.attribute19 ship_hdr_attribute19, wnd.attribute20 ship_hdr_attribute20, wnd.attribute_number1 ship_hdr_attribute_number1, wnd.attribute_number2 ship_hdr_attribute_number2, wnd.attribute_number3 ship_hdr_attribute_number3, wnd.attribute_number4 ship_hdr_attribute_number4, wnd.attribute_number5 ship_hdr_attribute_number5, wnd.attribute_number6 ship_hdr_attribute_number6, wnd.attribute_number7 ship_hdr_attribute_number7, wnd.attribute_number8 ship_hdr_attribute_number8, wnd.attribute_number9 ship_hdr_attribute_number9, wnd.attribute_number10 ship_hdr_attribute_number10, wnd.attribute_date1 ship_hdr_attribute_date1, wnd.attribute_date2 ship_hdr_attribute_date2, wnd.attribute_date3 ship_hdr_attribute_date3, wnd.attribute_date4 ship_hdr_attribute_date4, wnd.attribute_date5 ship_hdr_attribute_date5, wnd.attribute_timestamp1 ship_hdr_attribute_timestamp1, wnd.attribute_timestamp2 ship_hdr_attribute_timestamp2, wnd.attribute_timestamp3 ship_hdr_attribute_timestamp3, wnd.attribute_timestamp4 ship_hdr_attribute_timestamp4, wnd.attribute_timestamp5 ship_hdr_attribute_timestamp5 FROM cst_inv_transactions cit, inv_material_txns imt, wsh_new_deliveries wnd, wsh_delivery_assignments wda WHERE cit.external_system_ref_id = imt.transaction_id AND cit.external_system_reference = 'FUSION' AND wda.delivery_detail_id (+) = imt.trx_source_line_id AND wnd.delivery_id = CASE WHEN imt.transaction_source_type_id = 2 AND imt.transaction_action_id = 1 AND imt.picking_line_id IS NOT NULL THEN imt.trx_source_delivery_id WHEN (imt.transaction_source_type_id = 8 AND imt.transaction_action_id NOT IN ( 12, 16 )) OR (imt.transaction_source_type_id = 18 AND imt.transaction_action_id NOT IN ( 12, 3 )) THEN wda.delivery_id END UNION ALL SELECT cit.cst_inv_transaction_id cst_inv_transaction_id, wnd.attribute_category ship_hdr_attribute_category, wnd.attribute1 ship_hdr_attribute1, wnd.attribute2 ship_hdr_attribute2, wnd.attribute3 ship_hdr_attribute3, wnd.attribute4 ship_hdr_attribute4, wnd.attribute5 ship_hdr_attribute5, wnd.attribute6 ship_hdr_attribute6, wnd.attribute7 ship_hdr_attribute7, wnd.attribute8 ship_hdr_attribute8, wnd.attribute9 ship_hdr_attribute9, wnd.attribute10 ship_hdr_attribute10, wnd.attribute11 ship_hdr_attribute11, wnd.attribute12 ship_hdr_attribute12, wnd.attribute13 ship_hdr_attribute13, wnd.attribute14 ship_hdr_attribute14, wnd.attribute15 ship_hdr_attribute15, wnd.attribute16 ship_hdr_attribute16, wnd.attribute17 ship_hdr_attribute17, wnd.attribute18 ship_hdr_attribute18, wnd.attribute19 ship_hdr_attribute19, wnd.attribute20 ship_hdr_attribute20, wnd.attribute_number1 ship_hdr_attribute_number1, wnd.attribute_number2 ship_hdr_attribute_number2, wnd.attribute_number3 ship_hdr_attribute_number3, wnd.attribute_number4 ship_hdr_attribute_number4, wnd.attribute_number5 ship_hdr_attribute_number5, wnd.attribute_number6 ship_hdr_attribute_number6, wnd.attribute_number7 ship_hdr_attribute_number7, wnd.attribute_number8 ship_hdr_attribute_number8, wnd.attribute_number9 ship_hdr_attribute_number9, wnd.attribute_number10 ship_hdr_attribute_number10, wnd.attribute_date1 ship_hdr_attribute_date1, wnd.attribute_date2 ship_hdr_attribute_date2, wnd.attribute_date3 ship_hdr_attribute_date3, wnd.attribute_date4 ship_hdr_attribute_date4, wnd.attribute_date5 ship_hdr_attribute_date5, wnd.attribute_timestamp1 ship_hdr_attribute_timestamp1, wnd.attribute_timestamp2 ship_hdr_attribute_timestamp2, wnd.attribute_timestamp3 ship_hdr_attribute_timestamp3, wnd.attribute_timestamp4 ship_hdr_attribute_timestamp4, wnd.attribute_timestamp5 ship_hdr_attribute_timestamp5 FROM cst_inv_transactions cit, wsh_new_deliveries wnd WHERE cit.external_system_reference = 'FOS_INTERNAL' AND wnd.delivery_id (+) = cit.wsh_delivery_detail_id

SQL_Statement

SELECT

cit.cst_inv_transaction_id cst_inv_transaction_id,

wnd.attribute_category ship_hdr_attribute_category,

wnd.attribute1 ship_hdr_attribute1,

wnd.attribute2 ship_hdr_attribute2,

wnd.attribute3 ship_hdr_attribute3,

wnd.attribute4 ship_hdr_attribute4,

wnd.attribute5 ship_hdr_attribute5,

wnd.attribute6 ship_hdr_attribute6,

wnd.attribute7 ship_hdr_attribute7,

wnd.attribute8 ship_hdr_attribute8,

wnd.attribute9 ship_hdr_attribute9,

wnd.attribute10 ship_hdr_attribute10,

wnd.attribute11 ship_hdr_attribute11,

wnd.attribute12 ship_hdr_attribute12,

wnd.attribute13 ship_hdr_attribute13,

wnd.attribute14 ship_hdr_attribute14,

wnd.attribute15 ship_hdr_attribute15,

wnd.attribute16 ship_hdr_attribute16,

wnd.attribute17 ship_hdr_attribute17,

wnd.attribute18 ship_hdr_attribute18,

wnd.attribute19 ship_hdr_attribute19,

wnd.attribute20 ship_hdr_attribute20,

wnd.attribute_number1 ship_hdr_attribute_number1,

wnd.attribute_number2 ship_hdr_attribute_number2,

wnd.attribute_number3 ship_hdr_attribute_number3,

wnd.attribute_number4 ship_hdr_attribute_number4,

wnd.attribute_number5 ship_hdr_attribute_number5,

wnd.attribute_number6 ship_hdr_attribute_number6,

wnd.attribute_number7 ship_hdr_attribute_number7,

wnd.attribute_number8 ship_hdr_attribute_number8,

wnd.attribute_number9 ship_hdr_attribute_number9,

wnd.attribute_number10 ship_hdr_attribute_number10,

wnd.attribute_date1 ship_hdr_attribute_date1,

wnd.attribute_date2 ship_hdr_attribute_date2,

wnd.attribute_date3 ship_hdr_attribute_date3,

wnd.attribute_date4 ship_hdr_attribute_date4,

wnd.attribute_date5 ship_hdr_attribute_date5,

wnd.attribute_timestamp1 ship_hdr_attribute_timestamp1,

wnd.attribute_timestamp2 ship_hdr_attribute_timestamp2,

wnd.attribute_timestamp3 ship_hdr_attribute_timestamp3,

wnd.attribute_timestamp4 ship_hdr_attribute_timestamp4,

wnd.attribute_timestamp5 ship_hdr_attribute_timestamp5

FROM

cst_inv_transactions cit,

inv_material_txns imt,

wsh_new_deliveries wnd,

wsh_delivery_assignments wda

WHERE

cit.external_system_ref_id = imt.transaction_id

AND cit.external_system_reference = 'FUSION'

AND wda.delivery_detail_id (+) = imt.trx_source_line_id

AND wnd.delivery_id = CASE

WHEN imt.transaction_source_type_id = 2 AND imt.transaction_action_id = 1 AND imt.picking_line_id IS NOT NULL THEN imt.trx_source_delivery_id

WHEN (imt.transaction_source_type_id = 8 AND imt.transaction_action_id NOT IN ( 12, 16 ))

OR (imt.transaction_source_type_id = 18 AND imt.transaction_action_id NOT IN ( 12, 3 )) THEN wda.delivery_id

END

UNION ALL