View: AS_DOSSIER_REPS_UNIONALL_V

View Text

SELECT DISTINCT SC.HEADER_ID
, 0
, NVL(DECODE(LN.ITEM_TYPE_CODE
, 'MODEL'
, LN2.ORDERED_QUANTITY - NVL(LN2.CANCELLED_QUANTITY
, 0)
, LN.ORDERED_QUANTITY - NVL(LN2.CANCELLED_QUANTITY
, 0) )
, 0) * NVL(DECODE(LN.ITEM_TYPE_CODE
, 'MODEL'
, LN2.SELLING_PRICE
, 'SERVICE'
, NVL(LN.SELLING_PRICE
, 0) * NVL(LN.SERVICE_DURATION
, 1)
, LN.SELLING_PRICE)
, 0) * (SC.PERCENT/100)
, SCT.NAME
, SR.SALESREP_ID
, SR.NAME
FROM SO_SALES_CREDIT_TYPES_115 SCT
, RA_SALESREPS SR
, SO_SALES_CREDITS SC
, SO_LINES LN2
, SO_LINES LN
WHERE SC.SALES_CREDIT_TYPE_ID = SCT.SALES_CREDIT_TYPE_ID
AND SC.SALESREP_ID = SR.SALESREP_ID
AND LN.LINE_ID = LN2.PARENT_LINE_ID(+)
AND LN.LINE_TYPE_CODE = 'REGULAR'
AND SC.HEADER_ID = LN.HEADER_ID
AND SC.LINE_ID = LN.LINE_ID UNION ALL SELECT DISTINCT SC.HEADER_ID
, NVL(LN.ORDERED_QUANTITY - NVL(CANCELLED_QUANTITY
, 0)
, 0) * NVL(DECODE(LN.ITEM_TYPE_CODE
, 'SERVICE'
, NVL(LN.SELLING_PRICE
, 0) * NVL(LN.SERVICE_DURATION
, 1)
, LN.SELLING_PRICE)
, 0) * (SC.PERCENT/100)
, 0
, SCT.NAME
, SR.SALESREP_ID
, SR.NAME
FROM SO_SALES_CREDIT_TYPES SCT
, RA_SALESREPS SR
, SO_SALES_CREDITS SC
, SO_LINES LN
WHERE SC.SALES_CREDIT_TYPE_ID = SCT.SALES_CREDIT_TYPE_ID
AND SC.SALESREP_ID = SR.SALESREP_ID
AND LN.LINE_TYPE_CODE = 'REGULAR'
AND SC.HEADER_ID = LN.HEADER_ID
AND SC.LINE_ID IS NULL
AND NOT EXISTS (SELECT 'X'
FROM SO_SALES_CREDITS SC2
, SO_SALES_CREDIT_TYPES SCT2
WHERE (LN.PARENT_LINE_ID = SC2.LINE_ID OR LN.LINE_ID = SC2.LINE_ID)
AND SC2.SALES_CREDIT_TYPE_ID = SCT2.SALES_CREDIT_TYPE_ID
AND SCT2.QUOTA_FLAG = SCT.QUOTA_FLAG)

Columns