VIEW: APPS.GML_GPOAO_ORDERS_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, oh.order_no 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, oh.order_id ORDER_ID, oh.order_no ORDER_NO, oh.orgn_code ORGN_CODE, oh.order_comment ORDER_COMMENT, oh.order_status ORDER_STATUS, os.order_status_code ORDER_STATUS_CODE, oh.custpo_no CUSTPO_NO, oh.order_date SO_DATE, oh.frtbill_mthd FRTBILL_MTHD_INT, frtm.frtbill_desc FRTBILL_DESC, oh.embarkation_port EMBARKATION_PORT_INT, prtm1.port_desc EMBARKATION_PORT_DESC, oh.debarkation_port DEBARKATION_PORT_INT, prtm2.port_desc DEBARKATION_PORT_DESC, oh.fob_code FOB_CODE_INT, fobm.fob_name FOB_NAME, oh.shipper_code SHIPPER_CODE_INT, scm.shipper_name SHIPPER_NAME, oh.ship_mthd SHIP_MTHD_INT, sm.mthd_desc1 METH_DESC1, ecc2.customer_id BILLTO_CUST_ID, ecc2.orig_system_reference BILLTO_CODE_INT, ecc2.tp_location_code_ext BILLTO_CODE_EXT1, ecc2.tp_reference_ext1 BILLTO_CODE_EXT2, ecc2.tp_reference_ext2 BILLTO_CODE_EXT3, ecc2.customer_name BILLTO_CUST_NAME, ecc2.address1 BILLTO_ADDR1, ecc2.address2 BILLTO_ADDR2, ecc2.address3 BILLTO_ADDR3, ecc2.address4 BILLTO_ADDR4, ecc2.postal_code BILLTO_POSTAL_CODE, ecc2.country BILLTO_COUNTRY_CODE_INT, ecc2.state BILLTO_STATE_CODE_INT, ecc2.province BILLTO_PROVINCE_CODE_INT, ecc2.county BILLTO_COUNTY, ecc2.last_name BILLTO_LAST_NAME, ecc2.first_name BILLTO_FIRST_NAME, ecc2.job_title BILLTO_JOB_TITLE, ecc2.area_code BILLTO_AREA_CODE, ecc2.phone_number BILLTO_TELEPHONE, 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, oh.shipaddr_id SHIPADDR_ID, sam.addr1 SHIPADDR_ADDR1, sam.addr2 SHIPADDR_ADDR2, sam.addr3 SHIPADDR_ADDR3, sam.addr4 SHIPADDR_ADDR4, sam.postal_code SHIPADDR_POSTAL_CODE, sam.country_code SHIPADDR_COUNTRY_CODE_INT, sam.state_code SHIPADDR_STATE_CODE_INT, sam.province SHIPADDR_PROVINCE_CODE_INT, sam.county SHIPADDR_COUNTY, oh.edi_trans_count EDI_TRANS_CNT, oh.print_count PRINT_COUNT, oh.date_printed PRINT_DATE, oh.creation_date CREATION_DATE, oh.last_update_date LAST_UPDATE_DATE, 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, oh.attribute_category SO_HEADER_CATEGORY, oh.attribute1 SO_HEADER_ATTRIBUTE1, oh.attribute2 SO_HEADER_ATTRIBUTE2, oh.attribute3 SO_HEADER_ATTRIBUTE3, oh.attribute4 SO_HEADER_ATTRIBUTE4, oh.attribute5 SO_HEADER_ATTRIBUTE5, oh.attribute6 SO_HEADER_ATTRIBUTE6, oh.attribute7 SO_HEADER_ATTRIBUTE7, oh.attribute8 SO_HEADER_ATTRIBUTE8, oh.attribute9 SO_HEADER_ATTRIBUTE9, oh.attribute10 SO_HEADER_ATTRIBUTE10, oh.attribute11 SO_HEADER_ATTRIBUTE11, oh.attribute12 SO_HEADER_ATTRIBUTE12, oh.attribute13 SO_HEADER_ATTRIBUTE13, oh.attribute14 SO_HEADER_ATTRIBUTE14, oh.attribute15 SO_HEADER_ATTRIBUTE15, oh.attribute16 SO_HEADER_ATTRIBUTE16, oh.attribute17 SO_HEADER_ATTRIBUTE17, oh.attribute18 SO_HEADER_ATTRIBUTE18, oh.attribute19 SO_HEADER_ATTRIBUTE19, oh.attribute20 SO_HEADER_ATTRIBUTE20, oh.attribute21 SO_HEADER_ATTRIBUTE21, oh.attribute22 SO_HEADER_ATTRIBUTE22, oh.attribute23 SO_HEADER_ATTRIBUTE23, oh.attribute24 SO_HEADER_ATTRIBUTE24, oh.attribute25 SO_HEADER_ATTRIBUTE25, oh.attribute26 SO_HEADER_ATTRIBUTE26, oh.attribute27 SO_HEADER_ATTRIBUTE27, oh.attribute28 SO_HEADER_ATTRIBUTE28, oh.attribute29 SO_HEADER_ATTRIBUTE29, oh.attribute30 SO_HEADER_ATTRIBUTE30 FROM op_ordr_hdr oh, ece_tp_headers eth, ece_tp_details etd, gml_ec_contact_v ecc, ece_tp_headers eth2, ece_tp_details etd2, gml_ec_contact_v ecc2, 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 sam, sy_addr_mst bam, op_cust_mst cm, op_cust_mst cm2, op_ordr_sts os WHERE etd.document_id = 'GPOAO' AND etd.edi_flag = 'Y' AND etd.document_id = etd2.document_id AND etd.edi_flag = etd2.edi_flag AND eth.tp_header_id = etd.tp_header_id AND ecc.tp_header_id = etd.tp_header_id AND eth2.tp_header_id = etd2.tp_header_id AND ecc2.tp_header_id = etd2.tp_header_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 sam.addr_id (+) = oh.shipaddr_id AND cm.cust_id = oh.shipcust_id AND ecc.customer_id = cm.of_cust_id (+) AND ecc.site_use_id = cm.of_ship_to_site_use_id (+) AND ecc.address_id = cm.of_ship_to_address_id (+) AND cm2.cust_id = oh.billcust_id AND bam.addr_id (+) = cm2.addr_id AND ecc2.customer_id = cm2.of_cust_id (+) AND ecc2.site_use_id = cm2.of_bill_to_site_use_id (+) AND ecc2.address_id = cm2.of_bill_to_address_id (+) AND oh.order_status != -1 AND oh.order_status = os.order_status

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
, OH.ORDER_NO 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
, OH.ORDER_ID ORDER_ID
, OH.ORDER_NO ORDER_NO
, OH.ORGN_CODE ORGN_CODE
, OH.ORDER_COMMENT ORDER_COMMENT
, OH.ORDER_STATUS ORDER_STATUS
, OS.ORDER_STATUS_CODE ORDER_STATUS_CODE
, OH.CUSTPO_NO CUSTPO_NO
, OH.ORDER_DATE SO_DATE
, OH.FRTBILL_MTHD FRTBILL_MTHD_INT
, FRTM.FRTBILL_DESC FRTBILL_DESC
, OH.EMBARKATION_PORT EMBARKATION_PORT_INT
, PRTM1.PORT_DESC EMBARKATION_PORT_DESC
, OH.DEBARKATION_PORT DEBARKATION_PORT_INT
, PRTM2.PORT_DESC DEBARKATION_PORT_DESC
, OH.FOB_CODE FOB_CODE_INT
, FOBM.FOB_NAME FOB_NAME
, OH.SHIPPER_CODE SHIPPER_CODE_INT
, SCM.SHIPPER_NAME SHIPPER_NAME
, OH.SHIP_MTHD SHIP_MTHD_INT
, SM.MTHD_DESC1 METH_DESC1
, ECC2.CUSTOMER_ID BILLTO_CUST_ID
, ECC2.ORIG_SYSTEM_REFERENCE BILLTO_CODE_INT
, ECC2.TP_LOCATION_CODE_EXT BILLTO_CODE_EXT1
, ECC2.TP_REFERENCE_EXT1 BILLTO_CODE_EXT2
, ECC2.TP_REFERENCE_EXT2 BILLTO_CODE_EXT3
, ECC2.CUSTOMER_NAME BILLTO_CUST_NAME
, ECC2.ADDRESS1 BILLTO_ADDR1
, ECC2.ADDRESS2 BILLTO_ADDR2
, ECC2.ADDRESS3 BILLTO_ADDR3
, ECC2.ADDRESS4 BILLTO_ADDR4
, ECC2.POSTAL_CODE BILLTO_POSTAL_CODE
, ECC2.COUNTRY BILLTO_COUNTRY_CODE_INT
, ECC2.STATE BILLTO_STATE_CODE_INT
, ECC2.PROVINCE BILLTO_PROVINCE_CODE_INT
, ECC2.COUNTY BILLTO_COUNTY
, ECC2.LAST_NAME BILLTO_LAST_NAME
, ECC2.FIRST_NAME BILLTO_FIRST_NAME
, ECC2.JOB_TITLE BILLTO_JOB_TITLE
, ECC2.AREA_CODE BILLTO_AREA_CODE
, ECC2.PHONE_NUMBER BILLTO_TELEPHONE
, 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
, OH.SHIPADDR_ID SHIPADDR_ID
, SAM.ADDR1 SHIPADDR_ADDR1
, SAM.ADDR2 SHIPADDR_ADDR2
, SAM.ADDR3 SHIPADDR_ADDR3
, SAM.ADDR4 SHIPADDR_ADDR4
, SAM.POSTAL_CODE SHIPADDR_POSTAL_CODE
, SAM.COUNTRY_CODE SHIPADDR_COUNTRY_CODE_INT
, SAM.STATE_CODE SHIPADDR_STATE_CODE_INT
, SAM.PROVINCE SHIPADDR_PROVINCE_CODE_INT
, SAM.COUNTY SHIPADDR_COUNTY
, OH.EDI_TRANS_COUNT EDI_TRANS_CNT
, OH.PRINT_COUNT PRINT_COUNT
, OH.DATE_PRINTED PRINT_DATE
, OH.CREATION_DATE CREATION_DATE
, OH.LAST_UPDATE_DATE LAST_UPDATE_DATE
, 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
, OH.ATTRIBUTE_CATEGORY SO_HEADER_CATEGORY
, OH.ATTRIBUTE1 SO_HEADER_ATTRIBUTE1
, OH.ATTRIBUTE2 SO_HEADER_ATTRIBUTE2
, OH.ATTRIBUTE3 SO_HEADER_ATTRIBUTE3
, OH.ATTRIBUTE4 SO_HEADER_ATTRIBUTE4
, OH.ATTRIBUTE5 SO_HEADER_ATTRIBUTE5
, OH.ATTRIBUTE6 SO_HEADER_ATTRIBUTE6
, OH.ATTRIBUTE7 SO_HEADER_ATTRIBUTE7
, OH.ATTRIBUTE8 SO_HEADER_ATTRIBUTE8
, OH.ATTRIBUTE9 SO_HEADER_ATTRIBUTE9
, OH.ATTRIBUTE10 SO_HEADER_ATTRIBUTE10
, OH.ATTRIBUTE11 SO_HEADER_ATTRIBUTE11
, OH.ATTRIBUTE12 SO_HEADER_ATTRIBUTE12
, OH.ATTRIBUTE13 SO_HEADER_ATTRIBUTE13
, OH.ATTRIBUTE14 SO_HEADER_ATTRIBUTE14
, OH.ATTRIBUTE15 SO_HEADER_ATTRIBUTE15
, OH.ATTRIBUTE16 SO_HEADER_ATTRIBUTE16
, OH.ATTRIBUTE17 SO_HEADER_ATTRIBUTE17
, OH.ATTRIBUTE18 SO_HEADER_ATTRIBUTE18
, OH.ATTRIBUTE19 SO_HEADER_ATTRIBUTE19
, OH.ATTRIBUTE20 SO_HEADER_ATTRIBUTE20
, OH.ATTRIBUTE21 SO_HEADER_ATTRIBUTE21
, OH.ATTRIBUTE22 SO_HEADER_ATTRIBUTE22
, OH.ATTRIBUTE23 SO_HEADER_ATTRIBUTE23
, OH.ATTRIBUTE24 SO_HEADER_ATTRIBUTE24
, OH.ATTRIBUTE25 SO_HEADER_ATTRIBUTE25
, OH.ATTRIBUTE26 SO_HEADER_ATTRIBUTE26
, OH.ATTRIBUTE27 SO_HEADER_ATTRIBUTE27
, OH.ATTRIBUTE28 SO_HEADER_ATTRIBUTE28
, OH.ATTRIBUTE29 SO_HEADER_ATTRIBUTE29
, OH.ATTRIBUTE30 SO_HEADER_ATTRIBUTE30
FROM OP_ORDR_HDR OH
, ECE_TP_HEADERS ETH
, ECE_TP_DETAILS ETD
, GML_EC_CONTACT_V ECC
, ECE_TP_HEADERS ETH2
, ECE_TP_DETAILS ETD2
, GML_EC_CONTACT_V ECC2
, 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 SAM
, SY_ADDR_MST BAM
, OP_CUST_MST CM
, OP_CUST_MST CM2
, OP_ORDR_STS OS
WHERE ETD.DOCUMENT_ID = 'GPOAO'
AND ETD.EDI_FLAG = 'Y'
AND ETD.DOCUMENT_ID = ETD2.DOCUMENT_ID
AND ETD.EDI_FLAG = ETD2.EDI_FLAG
AND ETH.TP_HEADER_ID = ETD.TP_HEADER_ID
AND ECC.TP_HEADER_ID = ETD.TP_HEADER_ID
AND ETH2.TP_HEADER_ID = ETD2.TP_HEADER_ID
AND ECC2.TP_HEADER_ID = ETD2.TP_HEADER_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 SAM.ADDR_ID (+) = OH.SHIPADDR_ID
AND CM.CUST_ID = OH.SHIPCUST_ID
AND ECC.CUSTOMER_ID = CM.OF_CUST_ID (+)
AND ECC.SITE_USE_ID = CM.OF_SHIP_TO_SITE_USE_ID (+)
AND ECC.ADDRESS_ID = CM.OF_SHIP_TO_ADDRESS_ID (+)
AND CM2.CUST_ID = OH.BILLCUST_ID
AND BAM.ADDR_ID (+) = CM2.ADDR_ID
AND ECC2.CUSTOMER_ID = CM2.OF_CUST_ID (+)
AND ECC2.SITE_USE_ID = CM2.OF_BILL_TO_SITE_USE_ID (+)
AND ECC2.ADDRESS_ID = CM2.OF_BILL_TO_ADDRESS_ID (+)
AND OH.ORDER_STATUS != -1
AND OH.ORDER_STATUS = OS.ORDER_STATUS