View: AR_INVOICE_HEADER_V

Product:	AR - Receivables
Description:	Collects invoice header-level information
Implementation/DBA Data:	APPS.AR_INVOICE_HEADER_V

View Text

SELECT O_TYPE.MEANING OPEN_TRX
, TRX.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, L_TYPE.MEANING CLASS_NAME
, TYPES.TYPE CLASS
, TYPES.NAME TYPE_NAME
, B_BILL.CUSTOMER_CLASS_CODE CUSTOMER_CLASS_CODE
, TRX.BATCH_ID BATCH_ID
, NVL(NVL(TL.SEQUENCE_NUM
, PS.TERMS_SEQUENCE_NUMBER)
, 1) TERM_SEQUENCE_NUMBER
, TRX.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, TRX.TRX_NUMBER TRX_NUMBER
, TRX.TRX_DATE TRX_DATE
, TRX.SHIP_TO_CUSTOMER_ID SHIP_TO_CUSTOMER_ID
, TRX.SHIP_TO_CONTACT_ID SHIP_TO_CONTACT_ID
, TRX.REMIT_TO_ADDRESS_ID REMIT_TO_ADDRESS_ID
, TYPES.ACCOUNTING_AFFECT_FLAG OPEN_RECEIVABLE_FLAG
, TRX.INTERNAL_NOTES INTERNAL_NOTES
, TRX.COMMENTS COMMENTS
, PREVTRX.TRX_NUMBER PREVIOUS_TRX_NUMBER
, NVL(TRX.PRINTING_COUNT
, 0) PRINTING_COUNT
, TRX.PRINTING_ORIGINAL_DATE PRINTING_ORIGINAL_DATE
, TRX.PRINTING_LAST_PRINTED PRINTING_LAST_PRINTED
, TRX.PRINTING_PENDING PRINTING_PENDING
, TRX.LAST_PRINTED_SEQUENCE_NUM LAST_PRINTED_SEQUENCE_NUMBER
, TRX.START_DATE_COMMITMENT START_DATE_COMMITMENT
, TRX.END_DATE_COMMITMENT END_DATE_COMMITMENT
, TRX.INITIAL_CUSTOMER_TRX_ID INITIAL_CUSTOMER_TRX_ID
, TRX.PREVIOUS_CUSTOMER_TRX_ID PREVIOUS_CUSTOMER_TRX_ID
, TRX.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, T.NAME TERM_NAME
, TRX.TERM_ID TERM_ID
, TRX.PRIMARY_SALESREP_ID PRIMARY_SALESREP_ID
, TRX.SHIP_DATE_ACTUAL SHIP_DATE_ACTUAL
, TRX.SHIP_VIA SHIP_VIA
, TRX.WAYBILL_NUMBER WAYBILL_NUMBER
, TRX.PURCHASE_ORDER PURCHASE_ORDER_NUMBER
, TRX.PURCHASE_ORDER_REVISION PURCHASE_ORDER_REVISION
, TRX.PURCHASE_ORDER_DATE PURCHASE_ORDER_DATE
, PS.DUE_DATE TERM_DUE_DATE_FROM_PS
, NVL(TL.RELATIVE_AMOUNT
, 100)* (100/NVL(T.BASE_AMOUNT
, 100)) TERM_PERCENT
, TRX.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID
, TRX.BILL_TO_CONTACT_ID BILL_TO_CONTACT_ID
, TRX.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, U_BILL.LOCATION BILL_TO_LOCATION
, B_BILL.ACCOUNT_NUMBER BILL_TO_CUSTOMER_NUMBER
, SUBSTRB(B_BILL_PARTY.PARTY_NAME
, 1
, 50) BILL_TO_CUSTOMER_NAME
, DECODE(NVL(SUBSTRB(C_BILL_PARTY.PERSON_FIRST_NAME
, 1
, 40)
, '*') || NVL(SUBSTRB(C_BILL_PARTY.PERSON_LAST_NAME
, 1
, 50)
, '*')
, '**'
, NVL(A_TYPE.MEANING
, 'ATTN: ACCOUNTS PAYABLE')
, SUBSTRB(C_BILL_PARTY.PERSON_FIRST_NAME
, 1
, 40) || ' ' || SUBSTRB(C_BILL_PARTY.PERSON_LAST_NAME
, 1
, 50) ) DEFAULT_BILL_ATTN
, U_BILL.TAX_REFERENCE BILL_TO_SITE_TAX_REFERENCE
, B_BILL_PARTY.TAX_REFERENCE BILL_TO_CUST_TAX_REFERENCE
, A_BILL_LOC.ADDRESS1 BILL_TO_ADDRESS1
, A_BILL_LOC.ADDRESS2 BILL_TO_ADDRESS2
, A_BILL_LOC.ADDRESS3 BILL_TO_ADDRESS3
, A_BILL_LOC.ADDRESS4 BILL_TO_ADDRESS4
, A_BILL_LOC.CITY BILL_TO_CITY
, A_BILL_LOC.STATE BILL_TO_STATE
, A_BILL_LOC.PROVINCE BILL_TO_PROVINCE
, A_BILL_LOC.POSTAL_CODE BILL_TO_POSTAL_CODE
, A_BILL_LOC.COUNTRY BILL_TO_COUNTRY
, DECODE(A_BILL_LOC.COUNTRY
, NULL
, NULL
, NVL(SP.DEFAULT_COUNTRY
, 'XXXXXX')
, DECODE( SP.PRINT_HOME_COUNTRY_FLAG
, 'Y'
, DFT.TERRITORY_SHORT_NAME
, NULL)
, NVL(FT.TERRITORY_SHORT_NAME
, A_BILL_LOC.COUNTRY)) BILL_TO_COUNTRY_NAME
, SUBSTRB(C_BILL_PARTY.PERSON_FIRST_NAME
, 1
, 40) BILL_TO_CONTACT_FIRST_NAME
, SUBSTRB(C_BILL_PARTY.PERSON_LAST_NAME
, 1
, 50) BILL_TO_CONTACT_LAST_NAME
, SUBSTRB(C_BILL_PARTY.PERSON_FIRST_NAME
, 1
, 40) ||' '|| SUBSTRB(C_BILL_PARTY.PERSON_LAST_NAME
, 1
, 50) BILL_TO_ATTN
, C_BILL_ORG_CONT.MAIL_STOP BILL_TO_MAIL_STOP
, NVL(AMOUNT_LINE_ITEMS_ORIGINAL + DECODE(TRX.INITIAL_CUSTOMER_TRX_ID
, ''
, 0
, NVL(COM_ADJ.AMOUNT
, 0))
, TO_NUMBER(NULL)) LINE_AMOUNT
, NVL(TAX_ORIGINAL
, TO_NUMBER(NULL)) TAX_AMOUNT
, NVL(FREIGHT_ORIGINAL
, TO_NUMBER(NULL)) FREIGHT_AMOUNT
, PS.AMOUNT_DUE_ORIGINAL + DECODE(TRX.INITIAL_CUSTOMER_TRX_ID
, ''
, 0
, NVL(COM_ADJ.AMOUNT
, 0)) TOTAL_AMOUNT
, DECODE(SALES.SALESREP_ID
, ''
, ''
, ''
, ''
, -1
, ''
, -2
, ''
, -3
, ''
, SALES.NAME) PRIMARY_SALESREP_NAME
, ORG.DESCRIPTION SHIP_VIA_DESCRIPTION
, ORG.ORGANIZATION_ID SO_ORGANIZATION_ID
, SUBSTRB(B_SHIP_PARTY.PARTY_NAME
, 1
, 50) SHIP_TO_CUSTOMER_NAME
, A_SHIP_LOC.ADDRESS1 SHIP_TO_ADDRESS1
, A_SHIP_LOC.ADDRESS2 SHIP_TO_ADDRESS2
, A_SHIP_LOC.ADDRESS3 SHIP_TO_ADDRESS3
, A_SHIP_LOC.ADDRESS4 SHIP_TO_ADDRESS4
, A_SHIP_LOC.CITY SHIP_TO_CITY
, A_SHIP_LOC.POSTAL_CODE SHIP_TO_POSTAL_CODE
, A_SHIP_LOC.COUNTRY SHIP_TO_COUNTRY
, DECODE( A_SHIP_LOC.COUNTRY
, NULL
, NULL
, NVL(SP.DEFAULT_COUNTRY
, 'XXXXXX')
, DECODE( SP.PRINT_HOME_COUNTRY_FLAG
, 'Y'
, DFT.TERRITORY_SHORT_NAME
, NULL)
, NVL(FT2.TERRITORY_SHORT_NAME
, A_SHIP_LOC.COUNTRY)) SHIP_TO_COUNTRY_NAME
, U_SHIP.SITE_USE_ID SHIP_TO_SITE_USE_ID
, U_SHIP.LOCATION SHIP_TO_LOCATION
, U_SHIP.TAX_REFERENCE SHIP_TO_SITE_TAX_REFERENCE
, B_SHIP_PARTY.TAX_REFERENCE SHIP_TO_CUST_TAX_REFERENCE
, A_SHIP_LOC.STATE SHIP_TO_STATE
, A_SHIP_LOC.PROVINCE SHIP_TO_PROVINCE
, SUBSTRB(C_SHIP_PARTY.PERSON_FIRST_NAME
, 1
, 40) SHIP_TO_CONTACT_FIRST_NAME
, SUBSTRB(C_SHIP_PARTY.PERSON_LAST_NAME
, 1
, 50) SHIP_TO_CONTACT_LAST_NAME
, SUBSTRB(C_SHIP_PARTY.PERSON_FIRST_NAME
, 1
, 40) ||' '|| SUBSTRB(C_SHIP_PARTY.PERSON_LAST_NAME
, 1
, 50) SHIP_TO_ATTN
, C_SHIP_ORG_CONT.MAIL_STOP SHIP_TO_MAIL_STOP
, NVL( U_SHIP.TAX_REFERENCE
, NVL( U_BILL.TAX_REFERENCE
, NVL( B_SHIP_PARTY.TAX_REFERENCE
, B_BILL_PARTY.TAX_REFERENCE))) TAX_REFERENCE
, BS.NAME BATCH_SOURCE_NAME
, AR_INVOICE_SQL_FUNC_PUB.GET_COMMIT_THIS_INVOICE( TRX.CUSTOMER_TRX_ID) AMOUNT_APPLIED_TO_COMMITMENT
, A_REMIT_LOC.ADDRESS1 REMIT_TO_ADDRESS1
, A_REMIT_LOC.ADDRESS2 REMIT_TO_ADDRESS2
, A_REMIT_LOC.ADDRESS3 REMIT_TO_ADDRESS3
, A_REMIT_LOC.ADDRESS4 REMIT_TO_ADDRESS4
, A_REMIT_LOC.CITY REMIT_TO_CITY
, A_REMIT_LOC.STATE REMIT_TO_STATE
, A_REMIT_LOC.POSTAL_CODE REMIT_TO_POSTAL_CODE
, A_REMIT_LOC.COUNTRY REMIT_TO_COUNTRY
, DECODE( A_REMIT_LOC.COUNTRY
, NULL
, NULL
, NVL(SP.DEFAULT_COUNTRY
, 'XXXXXX')
, DECODE( SP.PRINT_HOME_COUNTRY_FLAG
, 'Y'
, DFT.TERRITORY_SHORT_NAME
, NULL)
, NVL(FT.TERRITORY_SHORT_NAME
, A_REMIT_LOC.COUNTRY)) REMIT_TO_COUNTRY_NAME
, SP.TAX_REGISTRATION_NUMBER TAX_REGISTRATION_NUMBER
, T_COUNT.NUMBER_OF_TERMS NUMBER_OF_TERMS
, AMOUNT.INTEREST_RATE
, TYPES.DESCRIPTION
, B_BILL_PARTY.JGZZ_FISCAL_CODE
, TRX.INTERFACE_HEADER_CONTEXT
, TRX.DEFAULT_USSGL_TRX_CODE_CONTEXT
, TRX.DEFAULT_USSGL_TRANSACTION_CODE
, TRX.RECURRED_FROM_TRX_NUMBER
, TRX.STATUS_TRX
, TRX.DOC_SEQUENCE_ID
, TRX.DOC_SEQUENCE_VALUE
, TRX.PAYING_CUSTOMER_ID
, TRX.PAYING_SITE_USE_ID
, TRX.RELATED_BATCH_SOURCE_ID
, TRX.DEFAULT_TAX_EXEMPT_FLAG
, TRX.CREATED_FROM
, TRX.ORG_ID
, TRX.REQUEST_ID
, TRX.PROGRAM_APPLICATION_ID
, TRX.PROGRAM_ID
, TRX.PROGRAM_UPDATE_DATE
, TRX.FINANCE_CHARGES
, TRX.POSTING_CONTROL_ID
, TRX.BILL_TO_ADDRESS_ID
, TRX.RA_POST_LOOP_NUMBER
, TRX.SHIP_TO_ADDRESS_ID
, TRX.CREDIT_METHOD_FOR_RULES
, TRX.CREDIT_METHOD_FOR_INSTALLMENTS
, TRX.RECEIPT_METHOD_ID
, TRX.RELATED_CUSTOMER_TRX_ID
, TRX.INVOICING_RULE_ID
, TRX.FOB_POINT
, TRX.CUSTOMER_BANK_ACCOUNT_ID
, TRX.INTERFACE_HEADER_ATTRIBUTE1
, TRX.INTERFACE_HEADER_ATTRIBUTE2
, TRX.INTERFACE_HEADER_ATTRIBUTE3
, TRX.INTERFACE_HEADER_ATTRIBUTE4
, TRX.INTERFACE_HEADER_ATTRIBUTE5
, TRX.INTERFACE_HEADER_ATTRIBUTE6
, TRX.INTERFACE_HEADER_ATTRIBUTE7
, TRX.INTERFACE_HEADER_ATTRIBUTE8
, TRX.INTERFACE_HEADER_ATTRIBUTE9
, TRX.INTERFACE_HEADER_ATTRIBUTE10
, TRX.INTERFACE_HEADER_ATTRIBUTE11
, TRX.INTERFACE_HEADER_ATTRIBUTE12
, TRX.INTERFACE_HEADER_ATTRIBUTE13
, TRX.INTERFACE_HEADER_ATTRIBUTE14
, TRX.INTERFACE_HEADER_ATTRIBUTE15
, TRX.PRINTING_OPTION
, TRX.CUSTOMER_REFERENCE
, TRX.CUSTOMER_REFERENCE_DATE
, TRX.EXCHANGE_RATE_TYPE
, TRX.EXCHANGE_DATE
, TRX.EXCHANGE_RATE
, TRX.TERRITORY_ID
, TRX.AGREEMENT_ID
, TRX.ATTRIBUTE_CATEGORY
, TRX.ATTRIBUTE1
, TRX.ATTRIBUTE2
, TRX.ATTRIBUTE3
, TRX.ATTRIBUTE4
, TRX.ATTRIBUTE5
, TRX.ATTRIBUTE6
, TRX.ATTRIBUTE7
, TRX.ATTRIBUTE8
, TRX.ATTRIBUTE9
, TRX.ATTRIBUTE10
, TRX.ATTRIBUTE11
, TRX.ATTRIBUTE12
, TRX.ATTRIBUTE13
, TRX.ATTRIBUTE14
, TRX.ATTRIBUTE15
, TRX.ORIG_SYSTEM_BATCH_NAME
, TRX.POST_REQUEST_ID
, TRX.LAST_UPDATE_DATE
, TRX.LAST_UPDATED_BY
, TRX.CREATION_DATE
, TRX.CREATED_BY
, TRX.LAST_UPDATE_LOGIN
, TRX.SET_OF_BOOKS_ID
, TRX.BATCH_SOURCE_ID
, TRX.REASON_CODE
, TRX.SOLD_TO_CUSTOMER_ID
, TRX.SOLD_TO_CONTACT_ID
, TRX.SOLD_TO_SITE_USE_ID
, TRX.SHIPMENT_ID
, TRX.TERM_DUE_DATE
, TRX.GLOBAL_ATTRIBUTE1
, TRX.GLOBAL_ATTRIBUTE2
, TRX.GLOBAL_ATTRIBUTE3
, TRX.GLOBAL_ATTRIBUTE4
, TRX.GLOBAL_ATTRIBUTE5
, TRX.GLOBAL_ATTRIBUTE6
, TRX.GLOBAL_ATTRIBUTE7
, TRX.GLOBAL_ATTRIBUTE8
, TRX.GLOBAL_ATTRIBUTE9
, TRX.GLOBAL_ATTRIBUTE10
, TRX.GLOBAL_ATTRIBUTE11
, TRX.GLOBAL_ATTRIBUTE12
, TRX.GLOBAL_ATTRIBUTE13
, TRX.GLOBAL_ATTRIBUTE14
, TRX.GLOBAL_ATTRIBUTE15
, TRX.GLOBAL_ATTRIBUTE16
, TRX.GLOBAL_ATTRIBUTE17
, TRX.GLOBAL_ATTRIBUTE18
, TRX.GLOBAL_ATTRIBUTE19
, TRX.GLOBAL_ATTRIBUTE20
, TRX.GLOBAL_ATTRIBUTE_CATEGORY
, TRX.WH_UPDATE_DATE
, HZ_FORMAT_PUB.FORMAT_ADDRESS(A_BILL_LOC.LOCATION_ID) FORMATTED_BILL_TO_ADDRESS
, HZ_FORMAT_PUB.FORMAT_ADDRESS(A_SHIP_LOC.LOCATION_ID) FORMATTED_SHIP_TO_ADDRESS
, HZ_FORMAT_PUB.FORMAT_ADDRESS(A_REMIT_LOC.LOCATION_ID) FORMATTED_REMIT_TO_ADDRESS
, TRX.BILL_TEMPLATE_ID
FROM AR_LOOKUPS O_TYPE
, AR_LOOKUPS L_TYPE
, AR_LOOKUPS A_TYPE
, RA_BATCH_SOURCES BS
, AR_SYSTEM_PARAMETERS SP
, FND_TERRITORIES_VL FT
, FND_TERRITORIES_VL FT2
, FND_TERRITORIES_VL DFT
, HZ_CUST_PROFILE_AMTS AMOUNT
, AR_INVOICE_COUNT_TERMS_V T_COUNT
, AR_ADJUSTMENTS COM_ADJ
, AR_PAYMENT_SCHEDULES PS
, RA_TERMS_LINES TL
, RA_TERMS T
, HZ_CUST_ACCT_SITES A_BILL
, HZ_PARTY_SITES A_BILL_PS
, HZ_LOCATIONS A_BILL_LOC
, HZ_CUST_ACCT_SITES A_SHIP
, HZ_PARTY_SITES A_SHIP_PS
, HZ_LOCATIONS A_SHIP_LOC
, HZ_CUST_SITE_USES U_BILL
, HZ_CUST_SITE_USES U_SHIP
, HZ_CUST_ACCOUNT_ROLES C_BILL
, HZ_PARTIES C_BILL_PARTY
, HZ_RELATIONSHIPS C_BILL_REL
, HZ_ORG_CONTACTS C_BILL_ORG_CONT
, HZ_CUST_ACCOUNT_ROLES C_SHIP
, HZ_PARTIES C_SHIP_PARTY
, HZ_RELATIONSHIPS C_SHIP_REL
, HZ_ORG_CONTACTS C_SHIP_ORG_CONT
, HZ_CUST_ACCOUNTS B_BILL
, HZ_PARTIES B_BILL_PARTY
, HZ_CUST_ACCOUNTS B_SHIP
, HZ_PARTIES B_SHIP_PARTY
, HZ_CUST_ACCT_SITES A_REMIT
, HZ_PARTY_SITES A_REMIT_PS
, HZ_LOCATIONS A_REMIT_LOC
, RA_SALESREPS SALES
, ORG_FREIGHT ORG
, RA_CUST_TRX_TYPES TYPES
, RA_CUSTOMER_TRX PREVTRX
, RA_CUSTOMER_TRX TRX
WHERE TRX.COMPLETE_FLAG = 'Y'
AND TRX.CUST_TRX_TYPE_ID = TYPES.CUST_TRX_TYPE_ID
AND TRX.SHIP_VIA = ORG.FREIGHT_CODE(+)
AND TRX.ORG_ID = ORG.ORGANIZATION_ID(+)
AND TRX.TERM_ID = TL.TERM_ID(+)
AND TRX.TERM_ID = T.TERM_ID(+)
AND TRX.CUSTOMER_TRX_ID+0 = PS.CUSTOMER_TRX_ID(+)
AND TRX.PRIMARY_SALESREP_ID = SALES.SALESREP_ID(+)
AND TRX.BILL_TO_CONTACT_ID = C_BILL.CUST_ACCOUNT_ROLE_ID(+)
AND C_BILL.PARTY_ID = C_BILL_REL.PARTY_ID(+)
AND C_BILL_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND C_BILL_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND C_BILL_REL.DIRECTIONAL_FLAG(+) = 'F'
AND C_BILL.ROLE_TYPE(+) = 'CONTACT'
AND C_BILL_ORG_CONT.PARTY_RELATIONSHIP_ID(+) = C_BILL_REL.RELATIONSHIP_ID
AND C_BILL_REL.SUBJECT_ID = C_BILL_PARTY.PARTY_ID(+)
AND TRX.SHIP_TO_CONTACT_ID = C_SHIP.CUST_ACCOUNT_ROLE_ID(+)
AND C_SHIP.PARTY_ID = C_SHIP_REL.PARTY_ID(+)
AND C_SHIP_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND C_SHIP_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND C_SHIP_REL.DIRECTIONAL_FLAG(+) = 'F'
AND C_SHIP.ROLE_TYPE(+) = 'CONTACT'
AND C_SHIP_ORG_CONT.PARTY_RELATIONSHIP_ID(+) = C_SHIP_REL.RELATIONSHIP_ID
AND C_SHIP_REL.SUBJECT_ID = C_SHIP_PARTY.PARTY_ID(+)
AND TRX.BILL_TO_SITE_USE_ID = U_BILL.SITE_USE_ID
AND TRX.SHIP_TO_SITE_USE_ID = U_SHIP.SITE_USE_ID(+)
AND TRX.BILL_TO_CUSTOMER_ID+0 = B_BILL.CUST_ACCOUNT_ID
AND B_BILL.PARTY_ID = B_BILL_PARTY.PARTY_ID
AND TRX.SHIP_TO_CUSTOMER_ID = B_SHIP.CUST_ACCOUNT_ID(+)
AND B_SHIP.PARTY_ID = B_SHIP_PARTY.PARTY_ID(+)
AND TRX.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID(+)
AND TRX.PREVIOUS_CUSTOMER_TRX_ID = PREVTRX.CUSTOMER_TRX_ID(+)
AND TRX.REMIT_TO_ADDRESS_ID = A_REMIT.CUST_ACCT_SITE_ID(+)
AND A_REMIT.PARTY_SITE_ID = A_REMIT_PS.PARTY_SITE_ID(+)
AND A_REMIT_LOC.LOCATION_ID(+) = A_REMIT_PS.LOCATION_ID AND TRX.BILL_TO_CUSTOMER_ID = AMOUNT.CUST_ACCOUNT_ID(+) AND TRX.BILL_TO_SITE_USE_ID = AMOUNT.SITE_USE_ID(+) AND TRX.INVOICE_CURRENCY_CODE = AMOUNT.CURRENCY_CODE(+) AND PS.PAYMENT_SCHEDULE_ID + DECODE( PS.CLASS
, 'INV'
, 0
, '') = COM_ADJ.PAYMENT_SCHEDULE_ID(+) AND COM_ADJ.SUBSEQUENT_TRX_ID IS NULL AND 'C' = COM_ADJ.ADJUSTMENT_TYPE(+)
AND TYPES.DEFAULT_PRINTING_OPTION = 'PRI'
AND L_TYPE.LOOKUP_TYPE = 'INV/CM/ADJ'
AND L_TYPE.LOOKUP_CODE = DECODE(TYPES.TYPE
, 'DEP'
, 'INV'
, TYPES.TYPE) AND L_TYPE.LOOKUP_CODE <> 'ADJ'
AND NVL(PS.TERMS_SEQUENCE_NUMBER
, NVL(TL.SEQUENCE_NUM
, 0)) = NVL(TL.SEQUENCE_NUM
, NVL(PS.TERMS_SEQUENCE_NUMBER
, 0))
AND U_BILL.CUST_ACCT_SITE_ID = A_BILL.CUST_ACCT_SITE_ID(+)
AND A_BILL.PARTY_SITE_ID = A_BILL_PS.PARTY_SITE_ID(+)
AND A_BILL_LOC.LOCATION_ID(+) = A_BILL_PS.LOCATION_ID
AND U_SHIP.CUST_ACCT_SITE_ID = A_SHIP.CUST_ACCT_SITE_ID(+)
AND A_SHIP.PARTY_SITE_ID = A_SHIP_PS.PARTY_SITE_ID(+)
AND A_SHIP_LOC.LOCATION_ID(+) = A_SHIP_PS.LOCATION_ID
AND O_TYPE.LOOKUP_TYPE = 'YES/NO'
AND O_TYPE.LOOKUP_CODE = DECODE(NVL(PS.AMOUNT_DUE_REMAINING
, 0)
, 0
, 'N'
, 'Y') AND A_TYPE.LOOKUP_TYPE = 'ADDRESS_LABEL' AND A_TYPE.LOOKUP_CODE = 'ATTN_DEFAULT_MSG' AND A_BILL_LOC.COUNTRY = FT.TERRITORY_CODE(+) AND A_SHIP_LOC.COUNTRY = FT2.TERRITORY_CODE(+) AND NVL(SP.DEFAULT_COUNTRY
, 'US') = DFT.TERRITORY_CODE AND NVL(TRX.TERM_ID
, -1) = T_COUNT.TERM_ID

Columns