CST_XLA_SALES_CREDITS_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name
SALESREP_NUMBER SALESREP_REV_CCID CR_FULFILL_LINE_ID CR_SALESPERSON_ID CR_PERCENT

Query

SQL_Statement

SELECT jrs.salesrep_number salesrep_number, aral.rev_ccid salesrep_rev_ccid, doo_sales_credit.cr_fulfill_line_id cr_fulfill_line_id, jrs.resource_id cr_salesperson_id, doo_sales_credit.cr_percent cr_percent FROM hz_parties hp, jtf_rs_salesreps jrs, (SELECT ar.rev_ccid rev_ccid,ar.source_ref_account_id source_ref_account_id, ar.bu_id ar_bu_id FROM ar_ref_accounts_all ar WHERE ar.source_ref_table = 'RA_SALESREPS') aral, (SELECT DISTINCT cr.fulfill_line_id cr_fulfill_line_id, cr.salesperson_id cr_salesperson_id, cr.percent cr_percent, cr.creation_date FROM doo_sales_credits cr WHERE cr.fulfill_line_id IS NOT NULL AND cr.percent IN (SELECT /*+ NO_UNNEST PUSH_SUBQ */ MAX(crs.percent) FROM doo_sales_credits crs WHERE crs.fulfill_line_id=cr.fulfill_line_id AND crs.fulfill_line_id IS NOT NULL ) AND cr.salesperson_id IN (SELECT /*+ NO_UNNEST PUSH_SUBQ */ MIN(crsss.salesperson_id) FROM doo_sales_credits crsss WHERE crsss.fulfill_line_id=cr.fulfill_line_id AND crsss.sales_credit_id =cr.sales_credit_id AND crsss.fulfill_line_id IS NOT NULL ) AND cr.creation_date IN (SELECT /*+ NO_UNNEST PUSH_SUBQ */ MIN(crss.creation_date) FROM doo_sales_credits crss WHERE crss.fulfill_line_id=cr.fulfill_line_id AND crss.sales_credit_id =cr.sales_credit_id AND crss.fulfill_line_id IS NOT NULL ) UNION SELECT DISTINCT line.fulfill_line_id cr_fulfill_line_id, cr.salesperson_id cr_salesperson_id, cr.percent cr_percent, cr.creation_date FROM doo_sales_credits cr, doo_fulfill_lines_all line WHERE line.header_id = cr.header_id AND cr.fulfill_line_id IS NULL AND NOT EXISTS (SELECT /*+ NO_UNNEST PUSH_SUBQ */ 1 FROM doo_sales_credits cra WHERE cra.header_id =cr.header_id AND cra.fulfill_line_id =cr.fulfill_line_id ) AND NOT EXISTS (SELECT /*+ NO_UNNEST PUSH_SUBQ */ 1 FROM doo_sales_credits cra WHERE cra.header_id =cr.header_id AND cra.fulfill_line_id =line.fulfill_line_id ) AND cr.percent IN (SELECT /*+ NO_UNNEST PUSH_SUBQ */ MAX(crs.percent) FROM doo_sales_credits crs WHERE crs.header_id =cr.header_id AND crs.fulfill_line_id IS NULL ) AND cr.salesperson_id IN (SELECT /*+ NO_UNNEST PUSH_SUBQ */ MIN(crsa.salesperson_id) FROM doo_sales_credits crsa WHERE crsa.header_id =cr.header_id AND crsa.sales_credit_id =cr.sales_credit_id AND crsa.fulfill_line_id IS NULL ) AND cr.creation_date IN (SELECT /*+ NO_UNNEST PUSH_SUBQ */ MIN(crss.creation_date) FROM doo_sales_credits crss WHERE crss.header_id =cr.header_id AND crss.sales_credit_id =cr.sales_credit_id AND crss.fulfill_line_id IS NULL ) ) doo_sales_credit, doo_fulfill_lines_all doo_fulfill WHERE hp.party_id (+) = doo_sales_credit.cr_salesperson_id AND hp.party_id(+) = jrs.resource_id AND hp.status = 'A' AND aral.source_ref_account_id (+) = jrs.resource_salesrep_id AND jrs.status='A' AND aral.ar_bu_id(+) = doo_fulfill.org_id AND doo_fulfill.fulfill_line_id (+) = doo_sales_credit.cr_fulfill_line_id AND jrs.SET_ID IN (0, FND_SetID_Utility.getSetID( 'HZ_SALES_PERSON', 'BU',doo_fulfill.org_id)) AND TRUNC(doo_fulfill.creation_date) BETWEEN TRUNC(jrs.START_DATE_ACTIVE) AND NVL(TRUNC(jrs.END_DATE_ACTIVE),TRUNC(sysdate))