VIEW: APPS.GML_GASNO_SHIPMENTS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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