View: RA_CUSTOMER_TRX_LINES_V

Product:	AR - Receivables
Description:	(Release 115)
Implementation/DBA Data:	APPS.RA_CUSTOMER_TRX_LINES_V

View Text

SELECT CTL.ROWID
, CTL.CUSTOMER_TRX_LINE_ID
, CTL.CUSTOMER_TRX_ID
, CTL.LINK_TO_CUST_TRX_LINE_ID
, CTL.PREVIOUS_CUSTOMER_TRX_ID
, CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID
, CTL.INITIAL_CUSTOMER_TRX_LINE_ID
, CTL.LINE_NUMBER
, CTL.LINE_TYPE
, CTL.DESCRIPTION
, CTL.QUANTITY_ORDERED
, CTL.QUANTITY_CREDITED
, CTL.QUANTITY_INVOICED
, NVL(CTL.QUANTITY_CREDITED
, CTL.QUANTITY_INVOICED)
, CTL.UNIT_STANDARD_PRICE
, CTL.UNIT_SELLING_PRICE
, CTL.EXTENDED_AMOUNT
, CTL.REVENUE_AMOUNT
, NVL(CTL.GROSS_EXTENDED_AMOUNT
, CTL.EXTENDED_AMOUNT)
, NVL(CTL.GROSS_UNIT_SELLING_PRICE
, CTL.UNIT_SELLING_PRICE)
, NVL(CTL.AMOUNT_INCLUDES_TAX_FLAG
, 'DEFAULT')
, VAT.AMOUNT_INCLUDES_TAX_OVERRIDE
, CTL.UOM_CODE
, CTL.REASON_CODE
, CTL.ACCOUNTING_RULE_ID
, CTL.ACCOUNTING_RULE_DURATION
, CTL.RULE_START_DATE
, CTL.AUTORULE_COMPLETE_FLAG
, CTL.AUTORULE_DURATION_PROCESSED
, CTL.LAST_PERIOD_TO_CREDIT
, CTL.ITEM_CONTEXT
, CTL.INVENTORY_ITEM_ID
, CTL.MEMO_LINE_ID
, CTL.TAXABLE_FLAG
, CTL.TAX_PRECEDENCE
, CTL.TAX_RATE
, CTL.ITEM_EXCEPTION_RATE_ID
, CTL.TAX_EXEMPTION_ID
, CTL.TAX_EXEMPT_FLAG
, CTL.TAX_EXEMPT_NUMBER
, CTL.TAX_EXEMPT_REASON_CODE
, CTL.TAX_VENDOR_RETURN_CODE
, CTL.SALES_TAX_ID
, CTL.LOCATION_SEGMENT_ID
, CTL.VAT_TAX_ID
, CTL.AUTOTAX
, CTL.SET_OF_BOOKS_ID
, CTL.SALES_ORDER_SOURCE
, CTL.SALES_ORDER
, CTL.SALES_ORDER_REVISION
, CTL.SALES_ORDER_LINE
, CTL.SALES_ORDER_DATE
, CTL.MOVEMENT_ID
, CTL.LAST_UPDATE_DATE
, CTL.LAST_UPDATED_BY
, CTL.CREATION_DATE
, CTL.CREATED_BY
, CTL.LAST_UPDATE_LOGIN
, CTL.REQUEST_ID
, CTL.PROGRAM_APPLICATION_ID
, CTL.PROGRAM_ID
, CTL.PROGRAM_UPDATE_DATE
, CTL.DEFAULT_USSGL_TRANSACTION_CODE
, CTL.DEFAULT_USSGL_TRX_CODE_CONTEXT
, LOC.LANGUAGE CUSTOMER_LANGUAGE
, CTL.INTERFACE_LINE_CONTEXT
, CTL.INTERFACE_LINE_ATTRIBUTE1
, CTL.INTERFACE_LINE_ATTRIBUTE2
, CTL.INTERFACE_LINE_ATTRIBUTE3
, CTL.INTERFACE_LINE_ATTRIBUTE4
, CTL.INTERFACE_LINE_ATTRIBUTE5
, CTL.INTERFACE_LINE_ATTRIBUTE6
, CTL.INTERFACE_LINE_ATTRIBUTE7
, CTL.INTERFACE_LINE_ATTRIBUTE8
, CTL.INTERFACE_LINE_ATTRIBUTE9
, CTL.INTERFACE_LINE_ATTRIBUTE10
, CTL.INTERFACE_LINE_ATTRIBUTE11
, CTL.INTERFACE_LINE_ATTRIBUTE12
, CTL.INTERFACE_LINE_ATTRIBUTE13
, CTL.INTERFACE_LINE_ATTRIBUTE14
, CTL.INTERFACE_LINE_ATTRIBUTE15
, CTL.ATTRIBUTE_CATEGORY
, CTL.ATTRIBUTE1
, CTL.ATTRIBUTE2
, CTL.ATTRIBUTE3
, CTL.ATTRIBUTE4
, CTL.ATTRIBUTE5
, CTL.ATTRIBUTE6
, CTL.ATTRIBUTE7
, CTL.ATTRIBUTE8
, CTL.ATTRIBUTE9
, CTL.ATTRIBUTE10
, CTL.ATTRIBUTE11
, CTL.ATTRIBUTE12
, CTL.ATTRIBUTE13
, CTL.ATTRIBUTE14
, CTL.ATTRIBUTE15
, UOM.UNIT_OF_MEASURE_TL /*4762000*/
, AL_TAX.MEANING
, DECODE(CTT.TYPE
, 'CM'
, AL_CM_REASON.MEANING
, AL_INV_REASON.MEANING)
, RR.NAME
, RR.TYPE
, RR.FREQUENCY
, AL_TAX_REASON.MEANING
, CTL_PREV.LINE_NUMBER
, CTL_PREV.QUANTITY_INVOICED
, UOM_PREV.UNIT_OF_MEASURE_TL /*4762000*/
, CTL_PREV.DESCRIPTION
, NVL(CTL_PREV.GROSS_UNIT_SELLING_PRICE
, CTL_PREV.UNIT_SELLING_PRICE)
, CTL_PREV.UNIT_SELLING_PRICE
, NVL(CTL_PREV.GROSS_EXTENDED_AMOUNT
, CTL_PREV.EXTENDED_AMOUNT)
, CTL_PREV.EXTENDED_AMOUNT
, ARP_BAL_UTIL.GET_LINE_BALANCE( CTL_PREV.CUSTOMER_TRX_LINE_ID
, CTL_PREV.EXTENDED_AMOUNT
, CTL.CUSTOMER_TRX_LINE_ID )
, CTL_PREV.ACCOUNTING_RULE_DURATION
, ARPT_SQL_FUNC_UTIL.GET_LINE_REFERENCE(CTL.ROWID)
, CTL.WAREHOUSE_ID
, ORG.ORGANIZATION_NAME
, ML.LINE_TYPE
, ML.TAX_CODE
, ML.UNIT_STD_PRICE
, DECODE( CTL.LAST_PERIOD_TO_CREDIT
, NULL
, FND_DATE.CANONICAL_TO_DATE(NULL)
, ARP_TRX_LINE_UTIL.DERIVE_LAST_DATE_TO_CR( CTL_PREV.CUSTOMER_TRX_LINE_ID
, CTL.LAST_PERIOD_TO_CREDIT ) )
, VAT.TAX_CODE
, CTL.GLOBAL_ATTRIBUTE_CATEGORY
, CTL.GLOBAL_ATTRIBUTE1
, CTL.GLOBAL_ATTRIBUTE2
, CTL.GLOBAL_ATTRIBUTE3
, CTL.GLOBAL_ATTRIBUTE4
, CTL.GLOBAL_ATTRIBUTE5
, CTL.GLOBAL_ATTRIBUTE6
, CTL.GLOBAL_ATTRIBUTE7
, CTL.GLOBAL_ATTRIBUTE8
, CTL.GLOBAL_ATTRIBUTE9
, CTL.GLOBAL_ATTRIBUTE10
, CTL.GLOBAL_ATTRIBUTE11
, CTL.GLOBAL_ATTRIBUTE12
, CTL.GLOBAL_ATTRIBUTE13
, CTL.GLOBAL_ATTRIBUTE14
, CTL.GLOBAL_ATTRIBUTE15
, CTL.GLOBAL_ATTRIBUTE16
, CTL.GLOBAL_ATTRIBUTE17
, CTL.GLOBAL_ATTRIBUTE18
, CTL.GLOBAL_ATTRIBUTE19
, CTL.GLOBAL_ATTRIBUTE20
, CTL.TRANSLATED_DESCRIPTION
, CTL.PAYMENT_SET_ID
, ARP_TRX_LINE_UTIL.GET_OE_HEADER_ID(CTL.INTERFACE_LINE_ATTRIBUTE6
, CTL.INTERFACE_LINE_CONTEXT)
, CTL.SHIP_TO_CUSTOMER_ID
, RAA_SHIP.CUST_ACCT_SITE_ID
, CTL.SHIP_TO_SITE_USE_ID
, CTL.SHIP_TO_CONTACT_ID
, CTL.HISTORICAL_FLAG
, CTL.TAX_LINE_ID
, CTL.LINE_RECOVERABLE
, CTL.TAX_RECOVERABLE
, DECODE(CTL.TAX_CLASSIFICATION_CODE
, NULL
, DECODE(CTL.VAT_TAX_ID
, NULL
, NULL
, ARP_TRX_LINE_UTIL.GET_TAX_CLASSIFICATION_CODE(CTL.VAT_TAX_ID))
, CTL.TAX_CLASSIFICATION_CODE) /* LINE-LEVEL SHIP TO */
, SUBSTRB(RAC_SHIP_PARTY.PARTY_NAME
, 1
, 50)
, RAC_SHIP.ACCOUNT_NUMBER
, SU_SHIP.LOCATION
, RAA_SHIP_LOC.ADDRESS1
, RAA_SHIP_LOC.ADDRESS2
, RAA_SHIP_LOC.ADDRESS3
, DECODE( RAA_SHIP.CUST_ACCT_SITE_ID
, NULL
, NULL
, ARH_ADDR_PKG.FORMAT_LAST_ADDRESS_LINE(RAA_SHIP_LOC.ADDRESS_STYLE
, RAA_SHIP_LOC.ADDRESS3
, RAA_SHIP_LOC.ADDRESS4
, RAA_SHIP_LOC.CITY
, RAA_SHIP_LOC.COUNTY
, RAA_SHIP_LOC.STATE
, RAA_SHIP_LOC.PROVINCE
, FT_SHIP.TERRITORY_SHORT_NAME
, RAA_SHIP_LOC.POSTAL_CODE) )
, RAA_SHIP_LOC.CITY
, RAA_SHIP_LOC.COUNTY
, RAA_SHIP_LOC.STATE
, RAA_SHIP_LOC.PROVINCE
, RAA_SHIP_LOC.POSTAL_CODE
, DECODE( RAA_SHIP.CUST_ACCT_SITE_ID
, NULL
, NULL
, ARH_ADDR_PKG.ARXTW_FORMAT_ADDRESS(RAA_SHIP_LOC.ADDRESS_STYLE
, RAA_SHIP_LOC.ADDRESS1
, RAA_SHIP_LOC.ADDRESS2
, RAA_SHIP_LOC.ADDRESS3
, RAA_SHIP_LOC.ADDRESS4
, RAA_SHIP_LOC.CITY
, RAA_SHIP_LOC.COUNTY
, RAA_SHIP_LOC.STATE
, RAA_SHIP_LOC.PROVINCE
, RAA_SHIP_LOC.POSTAL_CODE
, FT_SHIP.TERRITORY_SHORT_NAME) )
, DECODE(SUBSTRB(RACO_SHIP_PARTY.PERSON_LAST_NAME
, 1
, 50)
, NULL
, SUBSTRB(RACO_SHIP_PARTY.PERSON_FIRST_NAME
, 1
, 40)
, SUBSTRB(RACO_SHIP_PARTY.PERSON_LAST_NAME
, 1
, 50) || '
, ' || SUBSTRB(RACO_SHIP_PARTY.PERSON_FIRST_NAME
, 1
, 40))
, CTL.RULE_END_DATE
FROM AR_LOOKUPS AL_TAX
, AR_LOOKUPS AL_CM_REASON
, AR_LOOKUPS AL_INV_REASON
, AR_LOOKUPS AL_TAX_REASON
, RA_RULES RR
, MTL_UNITS_OF_MEASURE UOM
, MTL_UNITS_OF_MEASURE UOM_PREV
, RA_CUSTOMER_TRX_LINES_ALL CTL_PREV
, RA_CUSTOMER_TRX_LINES_ALL CTL
, RA_CUSTOMER_TRX CT
, RA_CUST_TRX_TYPES_ALL CTT
, AR_MEMO_LINES ML
, AR_VAT_TAX VAT
, ORG_ORGANIZATION_DEFINITIONS ORG
, HZ_CUST_ACCT_SITES_ALL RADD
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_CUST_SITE_USES_ALL SITE
, HZ_CUST_ACCT_SITES RAA_SHIP
, HZ_CUST_ACCOUNTS RAC_SHIP
, HZ_PARTIES RAC_SHIP_PARTY
, HZ_CUST_SITE_USES SU_SHIP
, FND_TERRITORIES_TL FT_SHIP
, HZ_LOCATIONS RAA_SHIP_LOC
, HZ_PARTY_SITES RAA_SHIP_PS
, HZ_CUST_ACCOUNT_ROLES RACO_SHIP
, HZ_PARTIES RACO_SHIP_PARTY
, HZ_RELATIONSHIPS RACO_SHIP_REL
WHERE CTL.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND CTL.ORG_ID = CT.ORG_ID
AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND CT.ORG_ID = CTT.ORG_ID
AND CTT.TYPE NOT IN ('DEP'
, 'GUAR'
, 'BR') /* 19-APR-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID = CTL_PREV.CUSTOMER_TRX_LINE_ID(+)
AND CTL.ORG_ID = CTL_PREV.ORG_ID(+)
AND CTL.UOM_CODE = UOM.UOM_CODE(+)
AND CTL_PREV.UOM_CODE = UOM_PREV.UOM_CODE(+)
AND CTL.ACCOUNTING_RULE_ID = RR.RULE_ID(+)
AND 'TAX_CONTROL_FLAG' = AL_TAX.LOOKUP_TYPE(+)
AND CTL.TAX_EXEMPT_FLAG = AL_TAX.LOOKUP_CODE(+)
AND 'INVOICING_REASON' = AL_INV_REASON.LOOKUP_TYPE(+)
AND CTL.REASON_CODE = AL_INV_REASON.LOOKUP_CODE(+)
AND 'CREDIT_MEMO_REASON' = AL_CM_REASON.LOOKUP_TYPE(+)
AND CTL.REASON_CODE = AL_CM_REASON.LOOKUP_CODE(+)
AND 'TAX_REASON' = AL_TAX_REASON.LOOKUP_TYPE(+)
AND CTL.TAX_EXEMPT_REASON_CODE = AL_TAX_REASON.LOOKUP_CODE(+)
AND CTL.MEMO_LINE_ID = ML.MEMO_LINE_ID(+)
AND CTL.ORG_ID = ML.ORG_ID(+)
AND CTL.VAT_TAX_ID = VAT.VAT_TAX_ID(+)
AND CTL.ORG_ID = VAT.ORG_ID(+)
AND CTL.WAREHOUSE_ID = ORG.ORGANIZATION_ID(+)
AND CT.BILL_TO_SITE_USE_ID = SITE.SITE_USE_ID
AND CT.ORG_ID = SITE.ORG_ID
AND SITE.CUST_ACCT_SITE_ID = RADD.CUST_ACCT_SITE_ID
AND SITE.ORG_ID = RADD.ORG_ID
AND RADD.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND CTL.SHIP_TO_SITE_USE_ID = SU_SHIP.SITE_USE_ID (+)
AND SU_SHIP.CUST_ACCT_SITE_ID = RAA_SHIP.CUST_ACCT_SITE_ID (+)
AND CTL.SHIP_TO_CUSTOMER_ID = RAC_SHIP.CUST_ACCOUNT_ID (+)
AND RAA_SHIP.PARTY_SITE_ID = RAA_SHIP_PS.PARTY_SITE_ID (+)
AND RAA_SHIP_PS.LOCATION_ID = RAA_SHIP_LOC.LOCATION_ID (+)
AND RAA_SHIP_LOC.COUNTRY = FT_SHIP.TERRITORY_CODE (+)
AND FT_SHIP.LANGUAGE(+) = USERENV('LANG')
AND CTL.SHIP_TO_CONTACT_ID = RACO_SHIP.CUST_ACCOUNT_ROLE_ID(+)
AND RACO_SHIP.PARTY_ID = RACO_SHIP_REL.PARTY_ID(+)
AND RAC_SHIP.PARTY_ID = RAC_SHIP_PARTY.PARTY_ID(+)
AND RACO_SHIP_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND RACO_SHIP_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND RACO_SHIP_REL.DIRECTIONAL_FLAG(+) = 'F'
AND RACO_SHIP.ROLE_TYPE(+) = 'CONTACT'
AND RACO_SHIP_REL.SUBJECT_ID = RACO_SHIP_PARTY.PARTY_ID(+)
AND RACO_SHIP_REL.STATUS (+) = 'A'

Columns