View: SO_HEADERS_RMA_V

View Text

SELECT H.ROWID ROW_ID
, H.HEADER_ID
, H.CREATION_DATE
, H.CREATED_BY
, H.LAST_UPDATE_DATE
, H.LAST_UPDATED_BY
, H.LAST_UPDATE_LOGIN
, H.REQUEST_ID
, H.PROGRAM_APPLICATION_ID
, H.PROGRAM_ID
, H.PROGRAM_UPDATE_DATE
, H.ORDER_NUMBER
, H.ORDER_CATEGORY
, H.ORIGINAL_SYSTEM_SOURCE_CODE
, H.ORIGINAL_SYSTEM_REFERENCE
, H.SOURCE_HEADER_ID
, H.ORDER_TYPE_ID
, H.CYCLE_ID
, H.DATE_ORDERED
, H.OPEN_FLAG
, H.CANCELLED_FLAG
, H.CUSTOMER_ID
, H.ORDERED_BY_CONTACT_ID
, H.SHIP_TO_SITE_USE_ID
, H.SHIP_TO_CONTACT_ID
, H.INVOICE_TO_SITE_USE_ID
, H.INVOICE_TO_CONTACT_ID
, H.PURCHASE_ORDER_NUM
, H.DATE_REQUESTED_CURRENT
, H.SHIPMENT_PRIORITY_CODE
, H.SHIP_METHOD_CODE
, H.FREIGHT_TERMS_CODE
, H.FOB_CODE
, H.WAREHOUSE_ID
, H.SHIPPING_INSTRUCTIONS
, H.PACKING_INSTRUCTIONS
, H.AGREEMENT_ID
, H.SALESREP_ID
, H.SALES_CHANNEL_CODE
, H.PRICE_LIST_ID
, H.CURRENCY_CODE
, H.CONVERSION_TYPE_CODE
, H.CONVERSION_RATE
, H.CONVERSION_DATE
, H.TERMS_ID
, H.TAX_EXEMPT_NUM
, H.TAX_EXEMPT_REASON_CODE
, H.DATE_SVRID
, H.ENTRY_STATUS_SVRID
, H.TYPE_SVRID
, H.CUSTOMER_SVRID
, H.CONTACT_SVRID
, H.SHIP_TO_SVRID
, H.SHIP_TO_CONTACT_SVRID
, H.INVOICE_TO_SVRID
, H.INVOICE_TO_CONTACT_SVRID
, H.AGREEMENT_SVRID
, H.PO_NUMBER_SVRID
, H.REQUEST_DATE_SVRID
, H.SALESPERSON_SVRID
, H.SALES_CHANNEL_SVRID
, H.PRICE_LIST_SVRID
, H.CURRENCY_SVRID
, H.CONVERSION_TYPE_SVRID
, H.CONVERSION_DATE_SVRID
, H.CONVERSION_RATE_SVRID
, H.PAYMENT_TERMS_SVRID
, H.TAX_EXEMPT_NUMBER_SVRID
, H.TAX_EXEMPT_REASON_SVRID
, H.SHIPMENT_PRIORITY_SVRID
, H.FREIGHT_CARRIER_SVRID
, H.FREIGHT_TERMS_SVRID
, H.FOB_SVRID
, H.SHIP_PARTIAL_SVRID
, H.WAREHOUSE_SVRID
, H.PACKING_INSTRUCTIONS_SVRID
, H.SHIPPING_INSTRUCTIONS_SVRID
, H.PAYMENT_AMOUNT_SVRID
, H.CHECK_NUMBER_SVRID
, H.CREDIT_CARD_SVRID
, H.CREDIT_CARD_HOLDER_SVRID
, H.CREDIT_CARD_NUMBER_SVRID
, H.CREDIT_CARD_EXP_DATE_SVRID
, H.CREDIT_CARD_APP_CODE_SVRID
, H.DF_SVRID
, H.S1
, H.S1_DATE
, H.S2
, H.S2_DATE
, H.S3
, H.S3_DATE
, H.S4
, H.S4_DATE
, H.S5
, H.S5_DATE
, H.S6
, H.S6_DATE
, H.S7
, H.S7_DATE
, H.S8
, H.S8_DATE
, H.S9
, H.S9_DATE
, H.S10
, H.S10_DATE
, H.S11
, H.S11_DATE
, H.S12
, H.S12_DATE
, H.S13
, H.S13_DATE
, H.S14
, H.S14_DATE
, H.S15
, H.S15_DATE
, H.S16
, H.S16_DATE
, H.S17
, H.S17_DATE
, H.S18
, H.S18_DATE
, H.S19
, H.S19_DATE
, H.S20
, H.S20_DATE
, H.S21
, H.S21_DATE
, H.S22
, H.S22_DATE
, H.S23
, H.S23_DATE
, H.S24
, H.S24_DATE
, H.S25
, H.S25_DATE
, H.S26
, H.S26_DATE
, H.S27
, H.S27_DATE
, H.S28
, H.S28_DATE
, H.S29
, H.S29_DATE
, H.S30
, H.S30_DATE
, H.CONTEXT
, H.ATTRIBUTE1
, H.ATTRIBUTE2
, H.ATTRIBUTE3
, H.ATTRIBUTE4
, H.ATTRIBUTE5
, H.ATTRIBUTE6
, H.ATTRIBUTE7
, H.ATTRIBUTE8
, H.ATTRIBUTE9
, H.ATTRIBUTE10
, H.ATTRIBUTE11
, H.ATTRIBUTE12
, H.ATTRIBUTE13
, H.ATTRIBUTE14
, H.ATTRIBUTE15
, H.TAX_EXEMPT_FLAG
, H.ACCOUNTING_RULE_ID
, H.CHECK_NUMBER
, H.CREDIT_CARD_CODE
, H.CREDIT_CARD_APPROVAL_CODE
, H.CREDIT_CARD_EXPIRATION_DATE
, H.CREDIT_CARD_HOLDER_NAME
, H.CREDIT_CARD_NUMBER
, H.DEMAND_CLASS_CODE
, H.INVOICING_RULE_ID
, H.PAYMENT_AMOUNT
, H.PAYMENT_TYPE_CODE
, H.SHIP_PARTIAL_FLAG
, OE_QUERY.GET_ENTRY_STATUS_NAME(H.OPEN_FLAG
, H.CANCELLED_FLAG
, H.S1) ENTRY_STATUS_NAME
, OS.NAME SOURCE_NAME
, OT.NAME ORDER_TYPE_NAME
, OT.REQUIRE_PO_FLAG REQUIRE_PO_FLAG
, OT.ORDER_NUMBER_SOURCE_ID ORDER_NUMBER_SOURCE_ID
, OT.ENFORCE_LINE_PRICES_FLAG ENFORCE_LINE_PRICES_FLAG
, CUST.CUSTOMER_NAME
, CUST.CUSTOMER_NUMBER
, NVL(INVLOC.GSA_INDICATOR
, NVL(CUST.GSA_INDICATOR
, 'N')) GSA_FLAG
, ORDCONT.LAST_NAME || DECODE(ORDCONT.FIRST_NAME
, NULL
, NULL
, '
, '|| ORDCONT.FIRST_NAME) CONTACT_NAME
, SHIPLOC.LOCATION RECEIVE_FROM_LOCATION
, SHIPLOC.BILL_TO_SITE_USE_ID RCV_FROM_CREDIT_TO_SITE_USE_ID
, SHIPCUST.CUSTOMER_NAME RECEIVE_FROM_CUSTOMER_NAME
, SHIPCUST.CUSTOMER_ID RECEIVE_FROM_CUSTOMER_ID
, SHIPADDR.ADDRESS1 RECEIVE_FROM_ADDRESS1
, SHIPADDR.ADDRESS2 RECEIVE_FROM_ADDRESS2
, SHIPADDR.ADDRESS3 RECEIVE_FROM_ADDRESS3
, SHIPADDR.ADDRESS4 RECEIVE_FROM_ADDRESS4
, DECODE(SHIPADDR.CITY
, NULL
, NULL
, SHIPADDR.CITY || '
, ') || SHIPADDR.STATE || ' ' || SHIPADDR.POSTAL_CODE RECEIVE_FROM_ADDRESS5
, SHIPCONT.LAST_NAME || DECODE(SHIPCONT.FIRST_NAME
, NULL
, NULL
, '
, ' || SHIPCONT.FIRST_NAME) RECEIVE_FROM_CONTACT_NAME
, SHIPADDR.ADDRESS_ID RECEIVE_FROM_ADDRESS_ID
, INVLOC.LOCATION CREDIT_TO_LOCATION
, INVLOC.GSA_INDICATOR CREDIT_TO_GSA_INDICATOR
, INVCUST.CUSTOMER_NAME CREDIT_TO_CUSTOMER_NAME
, INVCUST.CUSTOMER_ID CREDIT_TO_CUSTOMER_ID
, INVADDR.ADDRESS1 CREDIT_TO_ADDRESS1
, INVADDR.ADDRESS2 CREDIT_TO_ADDRESS2
, INVADDR.ADDRESS3 CREDIT_TO_ADDRESS3
, INVADDR.ADDRESS4 CREDIT_TO_ADDRESS4
, DECODE(INVADDR.CITY
, NULL
, NULL
, INVADDR.CITY || '
, ') || INVADDR.STATE || ' ' || INVADDR.POSTAL_CODE CREDIT_TO_ADDRESS5
, INVCONT.LAST_NAME || DECODE(INVCONT.FIRST_NAME
, NULL
, NULL
, '
, ' || INVCONT.FIRST_NAME) CREDIT_TO_CONTACT_NAME
, INVADDR.ADDRESS_ID CREDIT_TO_ADDRESS_ID
, SOLKP2.MEANING SHIPMENT_PRIORITY_NAME
, SOLKP3.MEANING FREIGHT_TERMS_NAME
, ARLKP1.MEANING FOB_POINT_NAME
, ARLKP2.MEANING TAX_EXEMPT_FLAG_DISPLAY
, ARLKP3.MEANING TAX_EXEMPT_REASON
, MTLPRT.ORGANIZATION_CODE WAREHOUSE_CODE
, SREP.NAME SALESREP
, PL.NAME PRICE_LIST_NAME
, PL.ROUNDING_FACTOR ROUNDING_FACTOR
, DCT.USER_CONVERSION_TYPE CONVERSION_TYPE_DISPLAY
, RCTT.TAX_CALCULATION_FLAG CALCULATE_TAX_FLAG
FROM GL_DAILY_CONVERSION_TYPES DCT
, SO_ORDER_SOURCES OS
, MTL_PARAMETERS MTLPRT
, SO_PRICE_LISTS PL
, SO_ORDER_TYPES OT
, RA_CUST_TRX_TYPES RCTT
, RA_SALESREPS SREP
, AR_LOOKUPS ARLKP3
, AR_LOOKUPS ARLKP2
, AR_LOOKUPS ARLKP1
, SO_LOOKUPS SOLKP3
, SO_LOOKUPS SOLKP2
, SO_LOOKUPS SOLKP1
, RA_CONTACTS INVCONT
, RA_CONTACTS SHIPCONT
, RA_CONTACTS ORDCONT
, RA_ADDRESSES INVADDR
, RA_ADDRESSES SHIPADDR
, RA_SITE_USES INVLOC
, RA_SITE_USES SHIPLOC
, RA_CUSTOMERS INVCUST
, RA_CUSTOMERS SHIPCUST
, RA_CUSTOMERS CUST
, SO_HEADERS H
WHERE H.CONVERSION_TYPE_CODE = DCT.CONVERSION_TYPE(+)
AND H.ORIGINAL_SYSTEM_SOURCE_CODE = OS.ORDER_SOURCE_ID(+)
AND H.WAREHOUSE_ID = MTLPRT.ORGANIZATION_ID(+)
AND H.PRICE_LIST_ID = PL.PRICE_LIST_ID(+)
AND H.ORDER_TYPE_ID = OT.ORDER_TYPE_ID
AND OT.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID (+)
AND H.SALESREP_ID = SREP.SALESREP_ID
AND H.SHIP_TO_CONTACT_ID = SHIPCONT.CONTACT_ID(+)
AND H.ORDERED_BY_CONTACT_ID = ORDCONT.CONTACT_ID(+)
AND H.INVOICE_TO_CONTACT_ID = INVCONT.CONTACT_ID(+)
AND H.INVOICE_TO_SITE_USE_ID = INVLOC.SITE_USE_ID
AND INVLOC.ADDRESS_ID = INVADDR.ADDRESS_ID(+)
AND INVADDR.CUSTOMER_ID = INVCUST.CUSTOMER_ID(+)
AND H.SHIP_TO_SITE_USE_ID = SHIPLOC.SITE_USE_ID
AND SHIPLOC.ADDRESS_ID = SHIPADDR.ADDRESS_ID(+)
AND SHIPADDR.CUSTOMER_ID = SHIPCUST.CUSTOMER_ID(+)
AND H.CUSTOMER_ID = CUST.CUSTOMER_ID
AND H.ORDER_CATEGORY = SOLKP1.LOOKUP_CODE(+)
AND SOLKP1.LOOKUP_TYPE(+) = 'ORDER_CATEGORY'
AND H.FREIGHT_TERMS_CODE = SOLKP3.LOOKUP_CODE(+)
AND SOLKP3.LOOKUP_TYPE (+) = 'FREIGHT_TERMS'
AND H.SHIPMENT_PRIORITY_CODE = SOLKP2.LOOKUP_CODE(+)
AND SOLKP2.LOOKUP_TYPE(+) = 'SHIPMENT_PRIORITY'
AND H.FOB_CODE = ARLKP1.LOOKUP_CODE(+)
AND ARLKP1.LOOKUP_TYPE(+) = 'FOB'
AND H.TAX_EXEMPT_FLAG = ARLKP2.LOOKUP_CODE(+)
AND ARLKP2.LOOKUP_TYPE(+) = 'TAX_CONTROL_FLAG'
AND H.TAX_EXEMPT_REASON_CODE = ARLKP3.LOOKUP_CODE(+)
AND ARLKP3.LOOKUP_TYPE(+) = 'TAX_REASON'
AND H.ORDER_CATEGORY = 'RMA'

Columns