VIEW: APPS.AR_TRX_DIST_LINE_REP_ITF_V

Source

View Text - Preformatted

SELECT RX.ROWID ROW_ID, RX.REQUEST_ID REQUEST_ID, RX.CREATED_BY CREATED_BY, RX.CREATION_DATE CREATION_DATE, RX.LAST_UPDATED_BY LAST_UPDATED_BY, RX.LAST_UPDATE_DATE LAST_UPDATE_DATE, RX.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, RX.ORGANIZATION_NAME ORGANIZATION_NAME, RX.FUNCTIONAL_CURRENCY_CODE FUNCTIONAL_CURRENCY_CODE, RX.TRX_NUMBER TRX_NUMBER, TRX_CLASS.MEANING TRX_CLASS, TRX_TYPES.NAME TRX_TYPE, RX.CONS_BILL_NUMBER CONS_BILL_NUMBER, RX.TRX_DATE TRX_DATE, SUBSTRB(SHIP_TO_PARTY.PARTY_NAME,1,50) SHIP_TO_CUSTOMER_NAME, DECODE(SHIP_TO_PARTY.PARTY_TYPE, 'ORGANIZATION', SHIP_TO_PARTY.ORGANIZATION_NAME_PHONETIC, NULL) SHIP_TO_CUSTOMER_NAME_PHONETIC, SHIP_TO.ACCOUNT_NUMBER SHIP_TO_CUSTOMER_NUMBER, SHIP_TO_SITE.LOCATION SHIP_TO_CUSTOMER_SITE, SUBSTRB(BILL_TO_PARTY.PARTY_NAME,1,50) BILL_TO_CUSTOMER_NAME, DECODE(BILL_TO_PARTY.PARTY_TYPE, 'ORGANIZATION', BILL_TO_PARTY.ORGANIZATION_NAME_PHONETIC, NULL) BILL_TO_CUSTOMER_NAME_PHONETIC, BILL_TO.ACCOUNT_NUMBER BILL_TO_CUSTOMER_NUMBER, BILL_TO_SITE.LOCATION BILL_TO_CUSTOMER_SITE, RX.TRX_CURRENCY TRX_CURRENCY, RX.EXCHANGE_RATE EXCHANGE_RATE, RX.EXCHANGE_DATE EXCHANGE_DATE, RX.EXCHANGE_TYPE EXCHANGE_TYPE, RX.RECEIVABLES_GL_DATE RECEIVABLES_GL_DATE, RX.REC_ACCOUNT REC_ACCOUNT, RX.REC_ACCOUNT_DESC REC_ACCOUNT_DESC, RX.REC_BALANCE REC_BALANCE, RX.REC_BALANCE_DESC REC_BALANCE_DESC, RX.REC_NATACCT REC_NATACCT, RX.REC_NATACCT_DESC REC_NATACCT_DESC, RX.REC_POSTABLE_FLAG REC_POSTABLE_FLAG, TERMS.NAME PAYMENT_TERMS, RX.TRX_DUE_DATE TRX_DUE_DATE, TAX_LEVEL.MEANING TAX_CALCULATION_LEVEL, DOC_SEQ.NAME DOC_SEQUENCE_NAME, RX.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE, RX.TRX_AMOUNT TRX_AMOUNT, RX.TRX_ACCTD_AMOUNT TRX_ACCTD_AMOUNT, METHODS.NAME PAYMENT_METHOD, NVL(LINK_LINE.LINE_NUMBER, TRX_LINE.LINE_NUMBER) LINE_NUMBER, DECODE(LINK_LINE.LINE_NUMBER, NULL, TRX_LINE.LINE_NUMBER, NULL) SUB_LINE_NUMBER, RX.INVENTORY_ITEM INVENTORY_ITEM, decode(TRX_TYPES.type,'CM',TRX_LINE.QUANTITY_CREDITED,TRX_LINE.QUANTITY_INVOICED) QUANTITY_INVOICED, TRX_LINE.QUANTITY_ORDERED QUANTITY_ORDERED, TRX_LINE.QUANTITY_CREDITED QUANTITY_CREDITED, UOM.UNIT_OF_MEASURE UOM, TRX_LINE.UNIT_SELLING_PRICE UNIT_SELLING_PRICE, TRX_LINE.UNIT_STANDARD_PRICE UNIT_STANDARD_PRICE, TRX_LINE.GROSS_UNIT_SELLING_PRICE GROSS_UNIT_SELLING_PRICE, TRX_LINE.EXTENDED_AMOUNT EXTENDED_AMOUNT, TRX_LINE.GROSS_EXTENDED_AMOUNT GROSS_EXTENDED_AMOUNT, LINE_TYPE.MEANING LINE_TYPE, TRX_LINE.TAX_RATE TAX_RATE, TRX_LINE.TAX_CLASSIFICATION_CODE TAX_CODE, DIST.PERCENT PERCENT, DIST.AMOUNT AMOUNT, DIST.ACCTD_AMOUNT ACCTD_AMOUNT, RX.ACCOUNT ACCOUNT, RX.ACCOUNT_DESC ACCOUNT_DESC, RX.BALANCE BALANCE, RX.BALANCE_DESC BALANCE_DESC, RX.NATACCT NATACCT, RX.NATACCT_DESC NATACCT_DESC, ACCOUNT_CLASS.MEANING ACCOUNT_CLASS, DIST.GL_DATE GL_DATE, POSTED_FLAG.MEANING POSTED_FLAG, DIST.GL_POSTED_DATE POSTED_DATE, USR.USER_NAME TRX_LAST_UPDATED_BY, RX.TRX_LAST_UPDATE_DATE TRX_LAST_UPDATE_DATE, RULES.NAME ACCOUNTING_RULE_NAME, TRX_LINE.ACCOUNTING_RULE_DURATION ACCOUNTING_RULE_DURATION, RX.ITEM_DESCRIPTION ITEM_DESCRIPTION FROM AR_TRANSACTIONS_REP_ITF RX, RA_TERMS TERMS, FND_DOCUMENT_SEQUENCES DOC_SEQ, HZ_CUST_ACCOUNTS SHIP_TO, HZ_PARTIES SHIP_TO_PARTY, HZ_CUST_ACCOUNTS BILL_TO, HZ_PARTIES BILL_TO_PARTY, HZ_CUST_SITE_USES_all SHIP_TO_SITE, HZ_CUST_SITE_USES_all BILL_TO_SITE, RA_CUST_TRX_TYPES_all TRX_TYPES, AR_LOOKUPS TRX_CLASS, AR_LOOKUPS TAX_LEVEL, RA_CUSTOMER_TRX_LINES_all TRX_LINE, RA_CUSTOMER_TRX_LINES_all LINK_LINE, MTL_UNITS_OF_MEASURE UOM, AR_VAT_TAX_all VAT, AR_LOOKUPS LINE_TYPE, RA_CUST_TRX_LINE_GL_DIST_all DIST, AR_LOOKUPS ACCOUNT_CLASS, AR_LOOKUPS POSTED_FLAG, AR_RECEIPT_METHODS METHODS, FND_USER USR, RA_RULES RULES WHERE RX.SHIP_TO_CUSTOMER_ID = SHIP_TO.CUST_ACCOUNT_ID(+) AND SHIP_TO.PARTY_ID = SHIP_TO_PARTY.PARTY_ID(+) AND RX.SHIP_TO_SITE_USE_ID = SHIP_TO_SITE.SITE_USE_ID(+) AND RX.BILL_TO_CUSTOMER_ID = BILL_TO.CUST_ACCOUNT_ID AND BILL_TO.PARTY_ID = BILL_TO_PARTY.PARTY_ID AND RX.BILL_TO_SITE_USE_ID = BILL_TO_SITE.SITE_USE_ID AND RX.CUST_TRX_TYPE_ID = TRX_TYPES.CUST_TRX_TYPE_ID AND RX.TERM_ID = TERMS.TERM_ID(+) AND RX.DOC_SEQUENCE_ID = DOC_SEQ.DOC_SEQUENCE_ID(+) AND TRX_TYPES.TYPE = TRX_CLASS.LOOKUP_CODE AND TRX_CLASS.LOOKUP_TYPE = 'INV/CM' AND NVL(BILL_TO_SITE.TAX_HEADER_LEVEL_FLAG, NVL(BILL_TO.TAX_HEADER_LEVEL_FLAG, RX.TAX_HEADER_LEVEL_FLAG)) = TAX_LEVEL.LOOKUP_CODE AND TAX_LEVEL.LOOKUP_TYPE = 'YES/NO' AND RX.CUSTOMER_TRX_ID = TRX_LINE.CUSTOMER_TRX_ID AND RX.CUSTOMER_TRX_LINE_ID = TRX_LINE.CUSTOMER_TRX_LINE_ID AND TRX_LINE.CUSTOMER_TRX_ID = LINK_LINE.CUSTOMER_TRX_ID(+) AND TRX_LINE.LINK_TO_CUST_TRX_LINE_ID = LINK_LINE.CUSTOMER_TRX_LINE_ID(+) AND TRX_LINE.UOM_CODE = UOM.UOM_CODE(+) AND TRX_LINE.LINE_TYPE = LINE_TYPE.LOOKUP_CODE AND TRX_LINE.VAT_TAX_ID = VAT.VAT_TAX_ID(+) AND LINE_TYPE.LOOKUP_TYPE = 'STD_LINE_TYPE' AND RX.CUST_TRX_LINE_GL_DIST_ID = DIST.CUST_TRX_LINE_GL_DIST_ID AND DECODE(DIST.POSTING_CONTROL_ID, -3, 'N', 'Y') = POSTED_FLAG.LOOKUP_CODE AND POSTED_FLAG.LOOKUP_TYPE = 'YES/NO' AND DIST.ACCOUNT_CLASS = ACCOUNT_CLASS.LOOKUP_CODE AND ACCOUNT_CLASS.LOOKUP_TYPE = 'AUTOGL_TYPE' AND RX.RECEIPT_METHOD_ID = METHODS.RECEIPT_METHOD_ID(+) AND NVL(RX.ORG_ID, -99) = NVL(TRX_TYPES.ORG_ID, -99) AND USR.USER_ID = RX.TRX_LAST_UPDATED_BY AND RULES.RULE_ID(+) = TRX_LINE.ACCOUNTING_RULE_ID

View Text - HTML Formatted

SELECT RX.ROWID ROW_ID
, RX.REQUEST_ID REQUEST_ID
, RX.CREATED_BY CREATED_BY
, RX.CREATION_DATE CREATION_DATE
, RX.LAST_UPDATED_BY LAST_UPDATED_BY
, RX.LAST_UPDATE_DATE LAST_UPDATE_DATE
, RX.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, RX.ORGANIZATION_NAME ORGANIZATION_NAME
, RX.FUNCTIONAL_CURRENCY_CODE FUNCTIONAL_CURRENCY_CODE
, RX.TRX_NUMBER TRX_NUMBER
, TRX_CLASS.MEANING TRX_CLASS
, TRX_TYPES.NAME TRX_TYPE
, RX.CONS_BILL_NUMBER CONS_BILL_NUMBER
, RX.TRX_DATE TRX_DATE
, SUBSTRB(SHIP_TO_PARTY.PARTY_NAME
, 1
, 50) SHIP_TO_CUSTOMER_NAME
, DECODE(SHIP_TO_PARTY.PARTY_TYPE
, 'ORGANIZATION'
, SHIP_TO_PARTY.ORGANIZATION_NAME_PHONETIC
, NULL) SHIP_TO_CUSTOMER_NAME_PHONETIC
, SHIP_TO.ACCOUNT_NUMBER SHIP_TO_CUSTOMER_NUMBER
, SHIP_TO_SITE.LOCATION SHIP_TO_CUSTOMER_SITE
, SUBSTRB(BILL_TO_PARTY.PARTY_NAME
, 1
, 50) BILL_TO_CUSTOMER_NAME
, DECODE(BILL_TO_PARTY.PARTY_TYPE
, 'ORGANIZATION'
, BILL_TO_PARTY.ORGANIZATION_NAME_PHONETIC
, NULL) BILL_TO_CUSTOMER_NAME_PHONETIC
, BILL_TO.ACCOUNT_NUMBER BILL_TO_CUSTOMER_NUMBER
, BILL_TO_SITE.LOCATION BILL_TO_CUSTOMER_SITE
, RX.TRX_CURRENCY TRX_CURRENCY
, RX.EXCHANGE_RATE EXCHANGE_RATE
, RX.EXCHANGE_DATE EXCHANGE_DATE
, RX.EXCHANGE_TYPE EXCHANGE_TYPE
, RX.RECEIVABLES_GL_DATE RECEIVABLES_GL_DATE
, RX.REC_ACCOUNT REC_ACCOUNT
, RX.REC_ACCOUNT_DESC REC_ACCOUNT_DESC
, RX.REC_BALANCE REC_BALANCE
, RX.REC_BALANCE_DESC REC_BALANCE_DESC
, RX.REC_NATACCT REC_NATACCT
, RX.REC_NATACCT_DESC REC_NATACCT_DESC
, RX.REC_POSTABLE_FLAG REC_POSTABLE_FLAG
, TERMS.NAME PAYMENT_TERMS
, RX.TRX_DUE_DATE TRX_DUE_DATE
, TAX_LEVEL.MEANING TAX_CALCULATION_LEVEL
, DOC_SEQ.NAME DOC_SEQUENCE_NAME
, RX.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, RX.TRX_AMOUNT TRX_AMOUNT
, RX.TRX_ACCTD_AMOUNT TRX_ACCTD_AMOUNT
, METHODS.NAME PAYMENT_METHOD
, NVL(LINK_LINE.LINE_NUMBER
, TRX_LINE.LINE_NUMBER) LINE_NUMBER
, DECODE(LINK_LINE.LINE_NUMBER
, NULL
, TRX_LINE.LINE_NUMBER
, NULL) SUB_LINE_NUMBER
, RX.INVENTORY_ITEM INVENTORY_ITEM
, DECODE(TRX_TYPES.TYPE
, 'CM'
, TRX_LINE.QUANTITY_CREDITED
, TRX_LINE.QUANTITY_INVOICED) QUANTITY_INVOICED
, TRX_LINE.QUANTITY_ORDERED QUANTITY_ORDERED
, TRX_LINE.QUANTITY_CREDITED QUANTITY_CREDITED
, UOM.UNIT_OF_MEASURE UOM
, TRX_LINE.UNIT_SELLING_PRICE UNIT_SELLING_PRICE
, TRX_LINE.UNIT_STANDARD_PRICE UNIT_STANDARD_PRICE
, TRX_LINE.GROSS_UNIT_SELLING_PRICE GROSS_UNIT_SELLING_PRICE
, TRX_LINE.EXTENDED_AMOUNT EXTENDED_AMOUNT
, TRX_LINE.GROSS_EXTENDED_AMOUNT GROSS_EXTENDED_AMOUNT
, LINE_TYPE.MEANING LINE_TYPE
, TRX_LINE.TAX_RATE TAX_RATE
, TRX_LINE.TAX_CLASSIFICATION_CODE TAX_CODE
, DIST.PERCENT PERCENT
, DIST.AMOUNT AMOUNT
, DIST.ACCTD_AMOUNT ACCTD_AMOUNT
, RX.ACCOUNT ACCOUNT
, RX.ACCOUNT_DESC ACCOUNT_DESC
, RX.BALANCE BALANCE
, RX.BALANCE_DESC BALANCE_DESC
, RX.NATACCT NATACCT
, RX.NATACCT_DESC NATACCT_DESC
, ACCOUNT_CLASS.MEANING ACCOUNT_CLASS
, DIST.GL_DATE GL_DATE
, POSTED_FLAG.MEANING POSTED_FLAG
, DIST.GL_POSTED_DATE POSTED_DATE
, USR.USER_NAME TRX_LAST_UPDATED_BY
, RX.TRX_LAST_UPDATE_DATE TRX_LAST_UPDATE_DATE
, RULES.NAME ACCOUNTING_RULE_NAME
, TRX_LINE.ACCOUNTING_RULE_DURATION ACCOUNTING_RULE_DURATION
, RX.ITEM_DESCRIPTION ITEM_DESCRIPTION
FROM AR_TRANSACTIONS_REP_ITF RX
, RA_TERMS TERMS
, FND_DOCUMENT_SEQUENCES DOC_SEQ
, HZ_CUST_ACCOUNTS SHIP_TO
, HZ_PARTIES SHIP_TO_PARTY
, HZ_CUST_ACCOUNTS BILL_TO
, HZ_PARTIES BILL_TO_PARTY
, HZ_CUST_SITE_USES_ALL SHIP_TO_SITE
, HZ_CUST_SITE_USES_ALL BILL_TO_SITE
, RA_CUST_TRX_TYPES_ALL TRX_TYPES
, AR_LOOKUPS TRX_CLASS
, AR_LOOKUPS TAX_LEVEL
, RA_CUSTOMER_TRX_LINES_ALL TRX_LINE
, RA_CUSTOMER_TRX_LINES_ALL LINK_LINE
, MTL_UNITS_OF_MEASURE UOM
, AR_VAT_TAX_ALL VAT
, AR_LOOKUPS LINE_TYPE
, RA_CUST_TRX_LINE_GL_DIST_ALL DIST
, AR_LOOKUPS ACCOUNT_CLASS
, AR_LOOKUPS POSTED_FLAG
, AR_RECEIPT_METHODS METHODS
, FND_USER USR
, RA_RULES RULES
WHERE RX.SHIP_TO_CUSTOMER_ID = SHIP_TO.CUST_ACCOUNT_ID(+)
AND SHIP_TO.PARTY_ID = SHIP_TO_PARTY.PARTY_ID(+)
AND RX.SHIP_TO_SITE_USE_ID = SHIP_TO_SITE.SITE_USE_ID(+)
AND RX.BILL_TO_CUSTOMER_ID = BILL_TO.CUST_ACCOUNT_ID
AND BILL_TO.PARTY_ID = BILL_TO_PARTY.PARTY_ID
AND RX.BILL_TO_SITE_USE_ID = BILL_TO_SITE.SITE_USE_ID
AND RX.CUST_TRX_TYPE_ID = TRX_TYPES.CUST_TRX_TYPE_ID
AND RX.TERM_ID = TERMS.TERM_ID(+)
AND RX.DOC_SEQUENCE_ID = DOC_SEQ.DOC_SEQUENCE_ID(+)
AND TRX_TYPES.TYPE = TRX_CLASS.LOOKUP_CODE
AND TRX_CLASS.LOOKUP_TYPE = 'INV/CM'
AND NVL(BILL_TO_SITE.TAX_HEADER_LEVEL_FLAG
, NVL(BILL_TO.TAX_HEADER_LEVEL_FLAG
, RX.TAX_HEADER_LEVEL_FLAG)) = TAX_LEVEL.LOOKUP_CODE
AND TAX_LEVEL.LOOKUP_TYPE = 'YES/NO'
AND RX.CUSTOMER_TRX_ID = TRX_LINE.CUSTOMER_TRX_ID
AND RX.CUSTOMER_TRX_LINE_ID = TRX_LINE.CUSTOMER_TRX_LINE_ID
AND TRX_LINE.CUSTOMER_TRX_ID = LINK_LINE.CUSTOMER_TRX_ID(+)
AND TRX_LINE.LINK_TO_CUST_TRX_LINE_ID = LINK_LINE.CUSTOMER_TRX_LINE_ID(+)
AND TRX_LINE.UOM_CODE = UOM.UOM_CODE(+)
AND TRX_LINE.LINE_TYPE = LINE_TYPE.LOOKUP_CODE
AND TRX_LINE.VAT_TAX_ID = VAT.VAT_TAX_ID(+)
AND LINE_TYPE.LOOKUP_TYPE = 'STD_LINE_TYPE'
AND RX.CUST_TRX_LINE_GL_DIST_ID = DIST.CUST_TRX_LINE_GL_DIST_ID
AND DECODE(DIST.POSTING_CONTROL_ID
, -3
, 'N'
, 'Y') = POSTED_FLAG.LOOKUP_CODE
AND POSTED_FLAG.LOOKUP_TYPE = 'YES/NO'
AND DIST.ACCOUNT_CLASS = ACCOUNT_CLASS.LOOKUP_CODE
AND ACCOUNT_CLASS.LOOKUP_TYPE = 'AUTOGL_TYPE'
AND RX.RECEIPT_METHOD_ID = METHODS.RECEIPT_METHOD_ID(+)
AND NVL(RX.ORG_ID
, -99) = NVL(TRX_TYPES.ORG_ID
, -99)
AND USR.USER_ID = RX.TRX_LAST_UPDATED_BY
AND RULES.RULE_ID(+) = TRX_LINE.ACCOUNTING_RULE_ID