VIEW: APPS.IGI_RA_CUSTOMER_TRX_PARTIAL_V

Source

View Text - Preformatted

SELECT DISTINCT CT.ROWID , CT.CUSTOMER_TRX_ID , CT.TRX_NUMBER , CT.TRX_DATE , CT.TERM_ID , CT.COMPLETE_FLAG , CT.BILL_TO_CUSTOMER_ID , CT.BILL_TO_SITE_USE_ID , CT.INVOICE_CURRENCY_CODE , CT.COMMENTS , CT.LAST_UPDATE_DATE , CT.LAST_UPDATED_BY , CT.CREATION_DATE , CT.CREATED_BY , CT.LAST_UPDATE_LOGIN , RAC_BILL_PARTY.PARTY_NAME RAC_BILL_TO_CUSTOMER_NAME , RAC_SHIP_PARTY.PARTY_NAME RAC_SHIP_TO_CUSTOMER_NAME , BS.NAME , CTT.NAME , CTT.TYPE , ARPT_SQL_FUNC_UTIL.GET_SALESREP_NAME_NUMBER(CT.PRIMARY_SALESREP_ID, 'NAME') RAS_PRIMARY_SALESREP_NAME , RAT.NAME , CT.CT_REFERENCE , GD.GL_DATE , CT.ORG_ID , CT.LEGAL_ENTITY_ID FROM RA_CUST_TRX_LINE_GL_DIST GD , RA_CUSTOMER_TRX CT , HZ_CUST_ACCOUNTS RAC_BILL , HZ_PARTIES RAC_BILL_PARTY , HZ_CUST_ACCOUNTS RAC_SHIP , HZ_PARTIES RAC_SHIP_PARTY , HZ_CUST_SITE_USES SU_BILL , HZ_CUST_ACCT_SITES RAA_BILL , HZ_PARTY_SITES RAA_BILL_PS , HZ_LOCATIONS RAA_BILL_LOC , HZ_CUST_ACCOUNT_ROLES RACO_SHIP , HZ_RELATIONSHIPS RACO_SHIP_REL , HZ_CUST_ACCOUNT_ROLES RACO_BILL , HZ_RELATIONSHIPS RACO_BILL_REL , RA_BATCH_SOURCES_ALL BS , RA_CUST_TRX_TYPES CTT , RA_TERMS_TL RAT , ORG_FREIGHT ORF , AR_LOOKUPS AL_FOB , AR_LOOKUPS AL_TAX WHERE CT.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID AND 'REC' = GD.ACCOUNT_CLASS AND 'Y' = GD.LATEST_REC_FLAG AND CT.BILL_TO_CUSTOMER_ID = RAC_BILL.CUST_ACCOUNT_ID AND RAC_BILL.PARTY_ID = RAC_BILL_PARTY.PARTY_ID AND CT.SHIP_TO_CUSTOMER_ID = RAC_SHIP.CUST_ACCOUNT_ID(+) AND RAC_SHIP.PARTY_ID = RAC_SHIP_PARTY.PARTY_ID(+) AND CT.BILL_TO_SITE_USE_ID = SU_BILL.SITE_USE_ID AND SU_BILL.CUST_ACCT_SITE_ID = RAA_BILL.CUST_ACCT_SITE_ID AND RAA_BILL.PARTY_SITE_ID = RAA_BILL_PS.PARTY_SITE_ID AND RAA_BILL_LOC.LOCATION_ID = RAA_BILL_PS.LOCATION_ID AND CT.BILL_TO_CONTACT_ID = RACO_BILL.CUST_ACCOUNT_ROLE_ID(+) AND RACO_BILL.PARTY_ID = RACO_BILL_REL.PARTY_ID(+) AND RACO_BILL_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND RACO_BILL_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND RACO_BILL_REL.DIRECTIONAL_FLAG(+) = 'F' AND RACO_BILL.ROLE_TYPE(+) = 'CONTACT' AND CT.SHIP_TO_CONTACT_ID = RACO_SHIP.CUST_ACCOUNT_ROLE_ID(+) AND RACO_SHIP.PARTY_ID = RACO_SHIP_REL.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 CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID AND CTT.TYPE <> 'BR' AND CT.TERM_ID = RAT.TERM_ID(+) AND RAT.LANGUAGE(+) = USERENV('LANG') AND 'FOB' = AL_FOB.LOOKUP_TYPE(+) AND CT.FOB_POINT = AL_FOB.LOOKUP_CODE(+) AND CT.SHIP_VIA = ORF.FREIGHT_CODE(+) AND CT.ORG_ID = ORF.ORGANIZATION_ID(+) AND 'TAX_CONTROL_FLAG' = AL_TAX.LOOKUP_TYPE(+) AND CT.DEFAULT_TAX_EXEMPT_FLAG = AL_TAX.LOOKUP_CODE(+) AND RACO_SHIP_REL.STATUS(+) = 'A' AND RACO_BILL_REL.STATUS(+) = 'A' AND BS.ORG_ID = CT.ORG_ID AND CT.customer_trx_id in (SELECT pay.customer_trx_id FROM AR_PAYMENT_SCHEDULES_all pay WHERE pay.CUSTOMER_TRX_ID = ct.CUSTOMER_TRX_ID and status = 'OP' )

View Text - HTML Formatted

SELECT DISTINCT CT.ROWID
, CT.CUSTOMER_TRX_ID
, CT.TRX_NUMBER
, CT.TRX_DATE
, CT.TERM_ID
, CT.COMPLETE_FLAG
, CT.BILL_TO_CUSTOMER_ID
, CT.BILL_TO_SITE_USE_ID
, CT.INVOICE_CURRENCY_CODE
, CT.COMMENTS
, CT.LAST_UPDATE_DATE
, CT.LAST_UPDATED_BY
, CT.CREATION_DATE
, CT.CREATED_BY
, CT.LAST_UPDATE_LOGIN
, RAC_BILL_PARTY.PARTY_NAME RAC_BILL_TO_CUSTOMER_NAME
, RAC_SHIP_PARTY.PARTY_NAME RAC_SHIP_TO_CUSTOMER_NAME
, BS.NAME
, CTT.NAME
, CTT.TYPE
, ARPT_SQL_FUNC_UTIL.GET_SALESREP_NAME_NUMBER(CT.PRIMARY_SALESREP_ID
, 'NAME') RAS_PRIMARY_SALESREP_NAME
, RAT.NAME
, CT.CT_REFERENCE
, GD.GL_DATE
, CT.ORG_ID
, CT.LEGAL_ENTITY_ID
FROM RA_CUST_TRX_LINE_GL_DIST GD
, RA_CUSTOMER_TRX CT
, HZ_CUST_ACCOUNTS RAC_BILL
, HZ_PARTIES RAC_BILL_PARTY
, HZ_CUST_ACCOUNTS RAC_SHIP
, HZ_PARTIES RAC_SHIP_PARTY
, HZ_CUST_SITE_USES SU_BILL
, HZ_CUST_ACCT_SITES RAA_BILL
, HZ_PARTY_SITES RAA_BILL_PS
, HZ_LOCATIONS RAA_BILL_LOC
, HZ_CUST_ACCOUNT_ROLES RACO_SHIP
, HZ_RELATIONSHIPS RACO_SHIP_REL
, HZ_CUST_ACCOUNT_ROLES RACO_BILL
, HZ_RELATIONSHIPS RACO_BILL_REL
, RA_BATCH_SOURCES_ALL BS
, RA_CUST_TRX_TYPES CTT
, RA_TERMS_TL RAT
, ORG_FREIGHT ORF
, AR_LOOKUPS AL_FOB
, AR_LOOKUPS AL_TAX
WHERE CT.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID
AND 'REC' = GD.ACCOUNT_CLASS
AND 'Y' = GD.LATEST_REC_FLAG
AND CT.BILL_TO_CUSTOMER_ID = RAC_BILL.CUST_ACCOUNT_ID
AND RAC_BILL.PARTY_ID = RAC_BILL_PARTY.PARTY_ID
AND CT.SHIP_TO_CUSTOMER_ID = RAC_SHIP.CUST_ACCOUNT_ID(+)
AND RAC_SHIP.PARTY_ID = RAC_SHIP_PARTY.PARTY_ID(+)
AND CT.BILL_TO_SITE_USE_ID = SU_BILL.SITE_USE_ID
AND SU_BILL.CUST_ACCT_SITE_ID = RAA_BILL.CUST_ACCT_SITE_ID
AND RAA_BILL.PARTY_SITE_ID = RAA_BILL_PS.PARTY_SITE_ID
AND RAA_BILL_LOC.LOCATION_ID = RAA_BILL_PS.LOCATION_ID
AND CT.BILL_TO_CONTACT_ID = RACO_BILL.CUST_ACCOUNT_ROLE_ID(+)
AND RACO_BILL.PARTY_ID = RACO_BILL_REL.PARTY_ID(+)
AND RACO_BILL_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND RACO_BILL_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND RACO_BILL_REL.DIRECTIONAL_FLAG(+) = 'F'
AND RACO_BILL.ROLE_TYPE(+) = 'CONTACT'
AND CT.SHIP_TO_CONTACT_ID = RACO_SHIP.CUST_ACCOUNT_ROLE_ID(+)
AND RACO_SHIP.PARTY_ID = RACO_SHIP_REL.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 CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND CTT.TYPE <> 'BR'
AND CT.TERM_ID = RAT.TERM_ID(+)
AND RAT.LANGUAGE(+) = USERENV('LANG')
AND 'FOB' = AL_FOB.LOOKUP_TYPE(+)
AND CT.FOB_POINT = AL_FOB.LOOKUP_CODE(+)
AND CT.SHIP_VIA = ORF.FREIGHT_CODE(+)
AND CT.ORG_ID = ORF.ORGANIZATION_ID(+)
AND 'TAX_CONTROL_FLAG' = AL_TAX.LOOKUP_TYPE(+)
AND CT.DEFAULT_TAX_EXEMPT_FLAG = AL_TAX.LOOKUP_CODE(+)
AND RACO_SHIP_REL.STATUS(+) = 'A'
AND RACO_BILL_REL.STATUS(+) = 'A'
AND BS.ORG_ID = CT.ORG_ID
AND CT.CUSTOMER_TRX_ID IN (SELECT PAY.CUSTOMER_TRX_ID
FROM AR_PAYMENT_SCHEDULES_ALL PAY
WHERE PAY.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND STATUS = 'OP' )