View: SO_HEADERS_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.ORDER_CATEGORY ORDER_CATEGORY_CODE
, 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.SHIP_PARTIAL_FLAG
, H.WAREHOUSE_ID
, H.DEMAND_CLASS_CODE
, 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.INVOICING_RULE_ID
, H.ACCOUNTING_RULE_ID
, H.TAX_EXEMPT_NUM
, H.TAX_EXEMPT_REASON_CODE
, H.PAYMENT_TYPE_CODE
, H.PAYMENT_AMOUNT
, H.CHECK_NUMBER
, H.CREDIT_CARD_CODE
, H.CREDIT_CARD_HOLDER_NAME
, H.CREDIT_CARD_NUMBER
, H.CREDIT_CARD_EXPIRATION_DATE
, H.CREDIT_CARD_APPROVAL_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.INVOICING_RULE_SVRID
, H.ACCOUNTING_RULE_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.DEMAND_CLASS_SVRID
, H.PACKING_INSTRUCTIONS_SVRID
, H.SHIPPING_INSTRUCTIONS_SVRID
, H.PAYMENT_TYPE_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
, OE_QUERY.ORDER_TOTAL(H.HEADER_ID) ORDER_TOTAL
, FNDCUR.PRECISION CURRENCY_PRECISION
, SOLKP1.MEANING ORDER_CATEGORY_NAME
, OE_QUERY.GET_ENTRY_STATUS_NAME(H.OPEN_FLAG
, H.CANCELLED_FLAG
, H.S1) ENTRY_STATUS
, OS.NAME ORDER_SOURCE_NAME
, OT.NAME ORDER_TYPE_NAME
, OT.STANDARD_VALUE_RULE_SET_ID
, OT.NAVIGATION_PREFERENCE_SET_ID
, OT.ENFORCE_LINE_PRICES_FLAG
, OT.AGREEMENT_TYPE_CODE TYPE_AGREEMENT_TYPE_CODE
, CUST.CUSTOMER_NAME
, CUST.CUSTOMER_NUMBER
, NVL(CUST.GSA_INDICATOR
, 'N') GSA_FLAG
, ORDCONT.LAST_NAME || DECODE(ORDCONT.FIRST_NAME
, NULL
, NULL
, '
, '|| ORDCONT.FIRST_NAME) ORDERED_CONTACT_NAME
, SHIPLOC.LOCATION SHIP_TO_LOCATION
, SHIPLOC.GSA_INDICATOR SHIP_TO_GSA_INDICATOR
, SHIPCUST.CUSTOMER_NAME SHIP_TO_CUSTOMER_NAME
, SHIPCUST.CUSTOMER_ID SHIP_TO_CUSTOMER_ID
, SHIPADDR.ADDRESS1 SHIP_TO_ADDRESS1
, SHIPADDR.ADDRESS2 SHIP_TO_ADDRESS2
, SHIPADDR.ADDRESS3 SHIP_TO_ADDRESS3
, SHIPADDR.ADDRESS4 SHIP_TO_ADDRESS4
, DECODE(SHIPADDR.CITY
, NULL
, NULL
, SHIPADDR.CITY || '
, ') || SHIPADDR.STATE || ' ' || SHIPADDR.POSTAL_CODE SHIP_TO_ADDRESS5
, SHIPCONT.LAST_NAME || DECODE(SHIPCONT.FIRST_NAME
, NULL
, NULL
, '
, ' || SHIPCONT.FIRST_NAME) SHIP_TO_CONTACT_NAME
, SHIPADDR.ADDRESS_ID SHIP_TO_ADDRESS_ID
, INVLOC.LOCATION INVOICE_TO_LOCATION
, INVLOC.GSA_INDICATOR INVOICE_TO_GSA_INDICATOR
, INVCUST.CUSTOMER_NAME INVOICE_TO_CUSTOMER_NAME
, INVCUST.CUSTOMER_ID INVOICE_TO_CUSTOMER_ID
, INVADDR.ADDRESS1 INVOICE_TO_ADDRESS1
, INVADDR.ADDRESS2 INVOICE_TO_ADDRESS2
, INVADDR.ADDRESS3 INVOICE_TO_ADDRESS3
, INVADDR.ADDRESS4 INVOICE_TO_ADDRESS4
, DECODE(INVADDR.CITY
, NULL
, NULL
, INVADDR.CITY || '
, ') || INVADDR.STATE || ' ' || INVADDR.POSTAL_CODE INVOICE_TO_ADDRESS5
, INVCONT.LAST_NAME || DECODE(INVCONT.FIRST_NAME
, NULL
, NULL
, '
, ' || INVCONT.FIRST_NAME) INVOICE_TO_CONTACT_NAME
, INVADDR.ADDRESS_ID INVOICE_TO_ADDRESS_ID
, SOLKP2.MEANING SHIPMENT_PRIORITY
, SOLKP3.MEANING FREIGHT_TERMS
, ARLKP1.MEANING FOB
, ARLKP2.MEANING TAX_CONTROL_STATUS
, ARLKP3.MEANING TAX_EXEMPT_REASON
, DECODE(H.TAX_EXEMPT_FLAG
, 'S'
, OE_QUERY.STD_TAX_EXEMPT_NUMBER(H.SHIP_TO_SITE_USE_ID
, INVCUST.CUSTOMER_ID
, H.DATE_ORDERED)) STD_TAX_EXEMPT_NUM
, DECODE(H.TAX_EXEMPT_FLAG
, 'S'
, OE_QUERY.STD_TAX_EXEMPT_REASON(H.SHIP_TO_SITE_USE_ID
, INVCUST.CUSTOMER_ID
, H.DATE_ORDERED)) STD_TAX_EXEMPT_REASON
, MTLPAR.ORGANIZATION_CODE WAREHOUSE
, MTLPAR.ORGANIZATION_CODE WAREHOUSE_CODE
, FNDLKP1.MEANING DEMAND_CLASS
, AGREE.NAME AGREEMENT_NAME
, AGREE.AGREEMENT_TYPE_CODE AGREEMENT_TYPE_CODE
, AGREE.INVOICE_TO_SITE_USE_ID AGR_INVOICE_TO_SITE_USE_ID
, AGREE.INVOICE_CONTACT_ID AGR_INVOICE_CONTACT_ID
, SREP.NAME SALESREP
, SOLKP4.MEANING SALES_CHANNEL
, SOLKP5.MEANING PAYMENT_TYPE
, SOLKP6.MEANING CREDIT_CARD
, PL.NAME PRICE_LIST_NAME
, -PL.ROUNDING_FACTOR ROUNDING_FACTOR
, DCT.USER_CONVERSION_TYPE CONVERSION_TYPE
, TERM.NAME TERMS_NAME
, INVRULE.NAME INVOICING_RULE
, ACCRULE.NAME ACCOUNTING_RULE
, DECODE(H.ORIGINAL_SYSTEM_SOURCE_CODE
, '2'
, OE_QUERY.SOURCE_ORDER_NUMBER( H.ORIGINAL_SYSTEM_SOURCE_CODE
, H.ORIGINAL_SYSTEM_REFERENCE)
, H.ORIGINAL_SYSTEM_REFERENCE) SOURCE_ORDER_REFERENCE
, DECODE(H.ORIGINAL_SYSTEM_SOURCE_CODE
, '2'
, OE_QUERY.SOURCE_ORDER_TYPE( H.ORIGINAL_SYSTEM_SOURCE_CODE
, H.ORIGINAL_SYSTEM_REFERENCE)
, '') SOURCE_ORDER_TYPE
FROM GL_DAILY_CONVERSION_TYPES DCT
, SO_ORDER_SOURCES OS
, RA_RULES INVRULE
, RA_RULES ACCRULE
, SO_AGREEMENTS AGREE
, RA_TERMS TERM
, MTL_PARAMETERS MTLPAR
, SO_PRICE_LISTS PL
, SO_ORDER_TYPES OT
, RA_SALESREPS SREP
, FND_CURRENCIES FNDCUR
, FND_COMMON_LOOKUPS FNDLKP1
, AR_LOOKUPS ARLKP3
, AR_LOOKUPS ARLKP2
, AR_LOOKUPS ARLKP1
, SO_LOOKUPS SOLKP6
, SO_LOOKUPS SOLKP5
, SO_LOOKUPS SOLKP4
, 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 TO_NUMBER( H.ORIGINAL_SYSTEM_SOURCE_CODE) = OS.ORDER_SOURCE_ID(+)
AND H.WAREHOUSE_ID = MTLPAR.ORGANIZATION_ID(+)
AND H.AGREEMENT_ID = AGREE.AGREEMENT_ID(+)
AND H.INVOICING_RULE_ID = INVRULE.RULE_ID(+)
AND H.ACCOUNTING_RULE_ID = ACCRULE.RULE_ID(+)
AND H.TERMS_ID = TERM.TERM_ID(+)
AND H.PRICE_LIST_ID = PL.PRICE_LIST_ID(+)
AND H.ORDER_TYPE_ID = OT.ORDER_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.DEMAND_CLASS_CODE = FNDLKP1.LOOKUP_CODE(+)
AND FNDLKP1.APPLICATION_ID(+) = 700
AND FNDLKP1.LOOKUP_TYPE(+) = 'DEMAND_CLASS'
AND H.CREDIT_CARD_CODE = SOLKP6.LOOKUP_CODE(+)
AND SOLKP6.LOOKUP_TYPE(+) = 'CREDIT_CARD'
AND H.PAYMENT_TYPE_CODE = SOLKP5.LOOKUP_CODE(+)
AND SOLKP5.LOOKUP_TYPE(+) = 'PAYMENT TYPE'
AND H.SALES_CHANNEL_CODE = SOLKP4.LOOKUP_CODE(+)
AND SOLKP4.LOOKUP_TYPE(+) = 'SALES_CHANNEL'
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.CURRENCY_CODE = FNDCUR.CURRENCY_CODE

Columns