CST_XLA_AR_SALES_CREDITS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
REC_CUST_TRX_LINE_GL_DIST_ID REC_SALES_CREDIT_REV_CCID REC_SALES_CREDIT_PERSON_ID REC_SALES_CREDIT_PERSON_NUMBER

Name

REC_CUST_TRX_LINE_GL_DIST_ID

REC_SALES_CREDIT_REV_CCID

REC_SALES_CREDIT_PERSON_ID

REC_SALES_CREDIT_PERSON_NUMBER

Query

SQL_Statement
SELECT l.cust_trx_line_gl_dist_id rec_cust_trx_line_gl_dist_id, ref.rev_ccid rec_sales_credit_rev_ccid, slnf.RESOURCE_ID rec_sales_credit_person_id, slnf.salesrep_number rec_sales_credit_person_number FROM RA_CUST_TRX_LINE_SALESREPS_ALL ctlsaf , jtf_rs_salesreps slnf , ar_ref_accounts_All ref, ra_cust_trx_line_gl_dist_all l WHERE ctlsaf.cust_trx_line_salesrep_id = l.cust_trx_line_salesrep_id AND slnf.resource_salesrep_id = ctlsaf.resource_salesrep_id AND ctlsaf.org_id = l.org_id AND ref.source_ref_table(+) = 'RA_SALESREPS' AND ref.source_ref_account_id(+) = slnf.resource_salesrep_id AND ref.bu_id(+) = l.org_id AND l.cust_trx_line_salesrep_id IS NOT NULL AND l.account_class IN ('REV') AND l.account_set_flag = 'N' AND slnf.SET_ID IN (0, FND_SetID_Utility.getSetID( 'HZ_SALES_PERSON', 'BU',l.org_id)) UNION ALL SELECT l.cust_trx_line_gl_dist_id rec_cust_trx_line_gl_dist_id, ref.rev_ccid rec_sales_credit_rev_ccid, slnf.RESOURCE_ID rec_sales_credit_person_id, slnf.salesrep_number rec_sales_credit_person_number FROM RA_CUST_TRX_LINE_SALESREPS_ALL ctlsaf, jtf_rs_salesreps slnf, ar_ref_accounts_All ref, ra_cust_trx_line_gl_dist_all l WHERE ctlsaf.customer_trx_id = l.customer_trx_id AND l.customer_trx_line_id = ctlsaf.customer_trx_line_id AND slnf.resource_salesrep_id = ctlsaf.resource_salesrep_id AND ctlsaf.org_id = l.org_id AND ref.source_ref_table(+) = 'RA_SALESREPS' AND ref.source_ref_account_id(+) = slnf.resource_salesrep_id AND ref.bu_id(+) = l.org_id AND l.cust_trx_line_salesrep_id IS NULL AND l.account_class iN ('REV') AND l.account_set_flag = 'N' AND ctlsaf.rowid IN (SELECT MAX(ctl.rowid) FROM RA_CUST_TRX_LINE_SALESREPS_ALL CTL WHERE ctl.customer_trx_line_id = ctlsaf.customer_trx_line_id and ctl.revenue_percent_split = (SELECT MAX(maxper.revenue_percent_split) FROM RA_CUST_TRX_LINE_SALESREPS_ALL maxper WHERE maxper.customer_trx_line_id = ctl.customer_trx_line_id) ) AND slnf.SET_ID IN (0, FND_SetID_Utility.getSetID( 'HZ_SALES_PERSON', 'BU',l.org_id))

SQL_Statement

SELECT l.cust_trx_line_gl_dist_id rec_cust_trx_line_gl_dist_id,

ref.rev_ccid rec_sales_credit_rev_ccid,

slnf.RESOURCE_ID rec_sales_credit_person_id,

slnf.salesrep_number rec_sales_credit_person_number

FROM RA_CUST_TRX_LINE_SALESREPS_ALL ctlsaf ,

jtf_rs_salesreps slnf ,

ar_ref_accounts_All ref,

ra_cust_trx_line_gl_dist_all l

WHERE ctlsaf.cust_trx_line_salesrep_id = l.cust_trx_line_salesrep_id

AND slnf.resource_salesrep_id = ctlsaf.resource_salesrep_id

AND ctlsaf.org_id = l.org_id

AND ref.source_ref_table(+) = 'RA_SALESREPS'

AND ref.source_ref_account_id(+) = slnf.resource_salesrep_id

AND ref.bu_id(+) = l.org_id

AND l.cust_trx_line_salesrep_id IS NOT NULL

AND l.account_class IN ('REV')

AND l.account_set_flag = 'N'

AND slnf.SET_ID IN (0, FND_SetID_Utility.getSetID( 'HZ_SALES_PERSON', 'BU',l.org_id))

UNION ALL

SELECT l.cust_trx_line_gl_dist_id rec_cust_trx_line_gl_dist_id,

ref.rev_ccid rec_sales_credit_rev_ccid,

slnf.RESOURCE_ID rec_sales_credit_person_id,

slnf.salesrep_number rec_sales_credit_person_number

FROM RA_CUST_TRX_LINE_SALESREPS_ALL ctlsaf,

jtf_rs_salesreps slnf,

ar_ref_accounts_All ref,

ra_cust_trx_line_gl_dist_all l

WHERE ctlsaf.customer_trx_id = l.customer_trx_id

AND l.customer_trx_line_id = ctlsaf.customer_trx_line_id

AND slnf.resource_salesrep_id = ctlsaf.resource_salesrep_id

AND ctlsaf.org_id = l.org_id

AND ref.source_ref_table(+) = 'RA_SALESREPS'

AND ref.source_ref_account_id(+) = slnf.resource_salesrep_id

AND ref.bu_id(+) = l.org_id

AND l.cust_trx_line_salesrep_id IS NULL

AND l.account_class iN ('REV')

AND l.account_set_flag = 'N'

AND ctlsaf.rowid IN

(SELECT MAX(ctl.rowid)

FROM RA_CUST_TRX_LINE_SALESREPS_ALL CTL

WHERE ctl.customer_trx_line_id = ctlsaf.customer_trx_line_id

and ctl.revenue_percent_split =

(SELECT MAX(maxper.revenue_percent_split)

FROM RA_CUST_TRX_LINE_SALESREPS_ALL maxper

WHERE maxper.customer_trx_line_id = ctl.customer_trx_line_id)

)

AND slnf.SET_ID IN (0, FND_SetID_Utility.getSetID( 'HZ_SALES_PERSON', 'BU',l.org_id))