VIEW: APPS.RA_CUSTOMER_TRX_LINES_V

Source

View Text - Preformatted

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, FND_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 'ZX_EXEMPTION_REASON_CODE' = 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'

View Text - HTML Formatted

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
, FND_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 'ZX_EXEMPTION_REASON_CODE' = 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'