View: GML_GASNO_SHIPMENTS_V

Product:	GML - Process Manufacturing Logistics
Description:	Shipment view
Implementation/DBA Data:	APPS.GML_GASNO_SHIPMENTS_V

View Text

SELECT 'EC' COMMUNICATION_METHOD
, ETD.TEST_FLAG TEST_INDICATOR
, ETD.DOCUMENT_ID DOCUMENT_ID
, ETH.TP_HEADER_ID TP_HEADER_ID
, ETD.TP_DETAIL_ID TP_DETAIL_ID
, '00' DOCUMENT_PURPOSE_CODE
, BL.BOL_NO TP_DOCUMENT_CODE
, ETH.TP_CODE TP_CODE
, ECC.TP_LOCATION_CODE_EXT TP_LOCATION_CODE_EXT
, ETH.TP_DESCRIPTION TP_DESCRIPTION
, ETH.TP_REFERENCE_EXT1 TP_REFERENCE1
, ETH.TP_REFERENCE_EXT2 TP_REFERENCE2
, SYSDATE TRANSACTION_DATE
, ETH.ATTRIBUTE_CATEGORY TP_HEADER_CATEGORY
, ETH.ATTRIBUTE1 TP_HEADER_ATTRIBUTE1
, ETH.ATTRIBUTE2 TP_HEADER_ATTRIBUTE2
, ETH.ATTRIBUTE3 TP_HEADER_ATTRIBUTE3
, ETH.ATTRIBUTE4 TP_HEADER_ATTRIBUTE4
, ETH.ATTRIBUTE5 TP_HEADER_ATTRIBUTE5
, ETH.ATTRIBUTE6 TP_HEADER_ATTRIBUTE6
, ETH.ATTRIBUTE7 TP_HEADER_ATTRIBUTE7
, ETH.ATTRIBUTE8 TP_HEADER_ATTRIBUTE8
, ETH.ATTRIBUTE9 TP_HEADER_ATTRIBUTE9
, ETH.ATTRIBUTE10 TP_HEADER_ATTRIBUTE10
, ETH.ATTRIBUTE11 TP_HEADER_ATTRIBUTE11
, ETH.ATTRIBUTE12 TP_HEADER_ATTRIBUTE12
, ETH.ATTRIBUTE13 TP_HEADER_ATTRIBUTE13
, ETH.ATTRIBUTE14 TP_HEADER_ATTRIBUTE14
, ETH.ATTRIBUTE15 TP_HEADER_ATTRIBUTE15
, ETD.ATTRIBUTE_CATEGORY TP_DETAIL_CATEGORY
, ETD.ATTRIBUTE1 TP_DETAIL_ATTRIBUTE1
, ETD.ATTRIBUTE2 TP_DETAIL_ATTRIBUTE2
, ETD.ATTRIBUTE3 TP_DETAIL_ATTRIBUTE3
, ETD.ATTRIBUTE4 TP_DETAIL_ATTRIBUTE4
, ETD.ATTRIBUTE5 TP_DETAIL_ATTRIBUTE5
, BL.BOL_ID BOL_ID
, BL.BOL_NO BOL_NO
, BL.ORGN_CODE ORGN_CODE
, BL.BOL_COMMENT BOL_COMMENT
, BL.WAYBILL_NO WAYBILL_NO
, OD.FRTBILL_MTHD FRTBILL_MTHD_INT
, FRTM.FRTBILL_DESC FRTBILL_DESC
, BL.EMBARKATION_PORT EMBARKATION_PORT_INT
, PRTM1.PORT_DESC EMBARKATION_PORT_DESC
, BL.DEBARKATION_PORT DEBARKATION_PORT_INT
, PRTM2.PORT_DESC DEBARKATION_PORT_DESC
, OD.FOB_CODE FOB_CODE_INT
, FOBM.FOB_NAME FOB_NAME
, BL.SHIPPER_CODE SHIPPER_CODE_INT
, SCM.SHIPPER_NAME SHIPPER_NAME
, OD.SHIP_MTHD SHIP_MTHD_INT
, SM.MTHD_DESC1 METH_DESC1
, SUMV.NET_WT NET_WT
, SUMV.TARE_WT TARE_WT
, SUMV.PALLET_WT PALLET_WT
, BL.BOLSHIP_WT BOLSHIP_WT
, BL.BOL_UM BOL_UM_INT
, SUMV.SHIP_VOLUME SHIP_VOLUME
, OD.SHIPVOLUME_UM SHIPVOLUME_UM_INT
, ECC.CUSTOMER_ID SHIPTO_CUST_ID
, ECC.ORIG_SYSTEM_REFERENCE SHIPTO_CODE_INT
, ECC.TP_LOCATION_CODE_EXT SHIPTO_CODE_EXT1
, ECC.TP_REFERENCE_EXT1 SHIPTO_CODE_EXT2
, ECC.TP_REFERENCE_EXT2 SHIPTO_CODE_EXT3
, ECC.CUSTOMER_NAME SHIPTO_CUST_NAME
, ECC.ADDRESS1 SHIPTO_ADDR1
, ECC.ADDRESS2 SHIPTO_ADDR2
, ECC.ADDRESS3 SHIPTO_ADDR3
, ECC.ADDRESS4 SHIPTO_ADDR4
, ECC.POSTAL_CODE SHIPTO_POSTAL_CODE
, ECC.COUNTRY SHIPTO_COUNTRY_CODE_INT
, ECC.STATE SHIPTO_STATE_CODE_INT
, ECC.PROVINCE SHIPTO_PROVINCE_CODE_INT
, ECC.COUNTY SHIPTO_COUNTY
, ECC.LAST_NAME SHIPTO_LAST_NAME
, ECC.FIRST_NAME SHIPTO_FIRST_NAME
, ECC.JOB_TITLE SHIPTO_JOB_TITLE
, ECC.AREA_CODE SHIPTO_AREA_CODE
, ECC.PHONE_NUMBER SHIPTO_TELEPHONE
, BL.BOLADDR_ID BOLADDR_ID
, AM.ADDR1 BOLADDR_ADDR1
, AM.ADDR2 BOLADDR_ADDR2
, AM.ADDR3 BOLADDR_ADDR3
, AM.ADDR4 BOLADDR_ADDR4
, AM.POSTAL_CODE BOLADDR_POSTAL_CODE
, AM.COUNTRY_CODE BOLADDR_COUNTRY_CODE_INT
, AM.STATE_CODE BOLADDR_STATE_CODE_INT
, AM.PROVINCE BOLADDR_PROVINCE_CODE_INT
, AM.COUNTY BOLADDR_COUNTY
, BL.EDI_TRANS_COUNT EDI_TRANS_CNT
, BL.DROPOFF_TIME DROPOFF_TIME
, BL.PICKUP_TIME PICKUP_TIME
, BL.PRINT_COUNT PRINT_COUNT
, BL.DATE_PRINTED PRINT_DATE
, BL.CREATION_DATE CREATION_DATE
, BL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, BL.DEMURRAGE_AMT DEMURRAGE_AMT
, BL.DEMURRAGE_CURRENCY DEMURRAGE_CURRENCY_CODE_INT
, OH.FROM_WHSE FROM_WHSE
, WM.WHSE_NAME FROM_WHSE_NAME
, WM.WHSE_CONTACT FROM_WHSE_CONTACT
, WM.WHSE_PHONE FROM_WHSE_PHONE
, WAM.ADDR1 FROM_WHSE_ADDR1
, WAM.ADDR2 FROM_WHSE_ADDR2
, WAM.ADDR3 FROM_WHSE_ADDR3
, WAM.ADDR4 FROM_WHSE_ADDR4
, WAM.POSTAL_CODE FROM_WHSE_POSTAL_CODE
, WAM.COUNTRY_CODE FROM_WHSE_COUNTRY_CODE_INT
, WAM.STATE_CODE FROM_WHSE_STATE_CODE_INT
, WAM.PROVINCE FROM_WHSE_PROVINCE_CODE_INT
, WAM.COUNTY FROM_WHSE_COUNTY
FROM OP_BILL_LAD BL
, OP_ORDR_DTL OD
, OP_ORDR_HDR OH
, OP_CUST_MST CM
, ECE_TP_HEADERS ETH
, ECE_TP_DETAILS ETD
, GML_EC_CONTACT_V ECC
, OP_FRGT_MTH FRTM
, OP_PORT_MST PRTM1
, OP_PORT_MST PRTM2
, OP_FOBC_MST FOBM
, OP_SHIP_MST SCM
, OP_SHIP_MTH SM
, IC_WHSE_MST WM
, SY_ADDR_MST WAM
, SY_ADDR_MST AM
, GML_GASNO_SUM_V SUMV
WHERE ETD.DOCUMENT_ID = 'GASNO'
AND ETD.EDI_FLAG = 'Y'
AND ETH.TP_HEADER_ID = ETD.TP_HEADER_ID
AND ECC.TP_HEADER_ID = ETD.TP_HEADER_ID
AND BL.BOL_ID = OD.BOL_ID
AND BL.DELETE_MARK = 0
AND OH.ORDER_ID = OD.ORDER_ID
AND OH.DELETE_MARK = 0
AND OH.ORDER_STATUS != -1
AND OD.DELETE_MARK = 0
AND OD.LINE_STATUS != -1
AND OD.LINE_STATUS = 20
AND /* GET FIRST LINE ID OF SHIPMENT TO GET FIRST ORDER OF SHIPMENT*/ OD.LINE_ID IN ( SELECT MIN(SQOD.LINE_ID)
FROM OP_ORDR_DTL SQOD
, OP_BILL_LAD SQBL
WHERE SQOD.BOL_ID = SQBL.BOL_ID GROUP BY SQBL.BOL_ID)
AND FRTM.FRTBILL_MTHD (+) = OH.FRTBILL_MTHD
AND PRTM1.PORT_CODE (+) = OH.EMBARKATION_PORT
AND PRTM2.PORT_CODE (+) = OH.DEBARKATION_PORT
AND FOBM.FOB_CODE (+) = OH.FOB_CODE
AND SCM.SHIPPER_CODE (+) = OH.SHIPPER_CODE
AND SM.SHIP_MTHD (+) = OH.SHIP_MTHD
AND WM.WHSE_CODE (+) = OH.FROM_WHSE
AND WAM.ADDR_ID (+) = WM.ADDR_ID
AND AM.ADDR_ID (+) = BL.BOLADDR_ID
AND CM.CUST_ID = OH.SHIPCUST_ID
AND ECC.SITE_USE_ID = CM.OF_SHIP_TO_SITE_USE_ID (+)
AND ECC.ADDRESS_ID = CM.OF_SHIP_TO_ADDRESS_ID (+)
AND ECC.CUSTOMER_ID = CM.OF_CUST_ID (+)
AND SUMV.BOL_ID = OD.BOL_ID

Columns