VIEW: APPS.CN_SRP_PERIOD_QUOTAS_PTD

Source

View Text - Preformatted

SELECT spq.salesrep_id, spq.period_id, q.credit_type_id, spa.role_id, ps.quarter_num, ps.period_year, SUM(DECODE(q.incentive_type_code,'COMMISSION', NVL(spq.commission_payed_ptd,0),0)), SUM(DECODE(q.incentive_type_code,'BONUS', NVL(spq.commission_payed_ptd,0),0)), SUM(DECODE(q.incentive_type_code,'MANUAL', NVL(spq.commission_payed_ptd,0),0)), SUM(NVL(advance_to_rec_ptd,0)), SUM(NVL(advance_to_rec_ptd,0)), SUM(NVL(advance_recovered_ptd,0)), SUM(NVL(recovery_amount_ptd,0)), SUM(DECODE(q.incentive_type_code,'COMMISSION',NVL(spq.comm_pend_ptd,0),0)), SUM(DECODE(q.incentive_type_code,'BONUS',NVL(spq.comm_pend_ptd,0),0)), SUM(NVL(commission_payed_ptd_f,0)) FROM cn_srp_period_quotas spq, cn_srp_plan_assigns_all spa, cn_quotas_all q, cn_period_statuses_all ps WHERE spq.srp_plan_assign_id = spa.srp_plan_assign_id AND q.quota_id = spq.quota_id AND q.delete_flag = 'N' AND ps.period_id= spq.period_id AND ps.org_id = spq.org_id GROUP BY spq.salesrep_id, q.credit_type_id, spa.role_id, ps.period_year, ps.quarter_num, spq.period_id UNION SELECT spq.salesrep_id, spq.period_id, q.credit_type_id, -999, ps.quarter_num, ps.period_year, SUM(DECODE(q.incentive_type_code,'COMMISSION', NVL(spq.commission_payed_ptd,0),0)), SUM(DECODE(q.incentive_type_code,'BONUS', NVL(spq.commission_payed_ptd,0),0)), SUM(DECODE(q.incentive_type_code,'MANUAL', NVL(spq.commission_payed_ptd,0),0)), SUM(NVL(advance_to_rec_ptd,0)), SUM(NVL(advance_to_rec_ptd,0)), SUM(NVL(advance_recovered_ptd,0)), SUM(NVL(recovery_amount_ptd,0)), SUM(DECODE(q.incentive_type_code,'COMMISSION',NVL(spq.comm_pend_ptd,0),0)), SUM(DECODE(q.incentive_type_code,'BONUS',NVL(spq.comm_pend_ptd,0),0)), SUM(NVL(commission_payed_ptd_f,0)) FROM cn_srp_period_quotas spq, cn_srp_plan_assigns_all spa, cn_quotas_all q, cn_period_statuses_all ps WHERE spq.srp_plan_assign_id = spa.srp_plan_assign_id AND q.quota_id = spq.quota_id AND q.delete_flag = 'N' AND ps.period_id= spq.period_id AND ps.org_id = spq.org_id GROUP BY spq.salesrep_id, q.credit_type_id, ps.period_year, ps.quarter_num, spq.period_id

View Text - HTML Formatted

SELECT SPQ.SALESREP_ID
, SPQ.PERIOD_ID
, Q.CREDIT_TYPE_ID
, SPA.ROLE_ID
, PS.QUARTER_NUM
, PS.PERIOD_YEAR
, SUM(DECODE(Q.INCENTIVE_TYPE_CODE
, 'COMMISSION'
, NVL(SPQ.COMMISSION_PAYED_PTD
, 0)
, 0))
, SUM(DECODE(Q.INCENTIVE_TYPE_CODE
, 'BONUS'
, NVL(SPQ.COMMISSION_PAYED_PTD
, 0)
, 0))
, SUM(DECODE(Q.INCENTIVE_TYPE_CODE
, 'MANUAL'
, NVL(SPQ.COMMISSION_PAYED_PTD
, 0)
, 0))
, SUM(NVL(ADVANCE_TO_REC_PTD
, 0))
, SUM(NVL(ADVANCE_TO_REC_PTD
, 0))
, SUM(NVL(ADVANCE_RECOVERED_PTD
, 0))
, SUM(NVL(RECOVERY_AMOUNT_PTD
, 0))
, SUM(DECODE(Q.INCENTIVE_TYPE_CODE
, 'COMMISSION'
, NVL(SPQ.COMM_PEND_PTD
, 0)
, 0))
, SUM(DECODE(Q.INCENTIVE_TYPE_CODE
, 'BONUS'
, NVL(SPQ.COMM_PEND_PTD
, 0)
, 0))
, SUM(NVL(COMMISSION_PAYED_PTD_F
, 0))
FROM CN_SRP_PERIOD_QUOTAS SPQ
, CN_SRP_PLAN_ASSIGNS_ALL SPA
, CN_QUOTAS_ALL Q
, CN_PERIOD_STATUSES_ALL PS
WHERE SPQ.SRP_PLAN_ASSIGN_ID = SPA.SRP_PLAN_ASSIGN_ID
AND Q.QUOTA_ID = SPQ.QUOTA_ID
AND Q.DELETE_FLAG = 'N'
AND PS.PERIOD_ID= SPQ.PERIOD_ID
AND PS.ORG_ID = SPQ.ORG_ID GROUP BY SPQ.SALESREP_ID
, Q.CREDIT_TYPE_ID
, SPA.ROLE_ID
, PS.PERIOD_YEAR
, PS.QUARTER_NUM
, SPQ.PERIOD_ID UNION SELECT SPQ.SALESREP_ID
, SPQ.PERIOD_ID
, Q.CREDIT_TYPE_ID
, -999
, PS.QUARTER_NUM
, PS.PERIOD_YEAR
, SUM(DECODE(Q.INCENTIVE_TYPE_CODE
, 'COMMISSION'
, NVL(SPQ.COMMISSION_PAYED_PTD
, 0)
, 0))
, SUM(DECODE(Q.INCENTIVE_TYPE_CODE
, 'BONUS'
, NVL(SPQ.COMMISSION_PAYED_PTD
, 0)
, 0))
, SUM(DECODE(Q.INCENTIVE_TYPE_CODE
, 'MANUAL'
, NVL(SPQ.COMMISSION_PAYED_PTD
, 0)
, 0))
, SUM(NVL(ADVANCE_TO_REC_PTD
, 0))
, SUM(NVL(ADVANCE_TO_REC_PTD
, 0))
, SUM(NVL(ADVANCE_RECOVERED_PTD
, 0))
, SUM(NVL(RECOVERY_AMOUNT_PTD
, 0))
, SUM(DECODE(Q.INCENTIVE_TYPE_CODE
, 'COMMISSION'
, NVL(SPQ.COMM_PEND_PTD
, 0)
, 0))
, SUM(DECODE(Q.INCENTIVE_TYPE_CODE
, 'BONUS'
, NVL(SPQ.COMM_PEND_PTD
, 0)
, 0))
, SUM(NVL(COMMISSION_PAYED_PTD_F
, 0))
FROM CN_SRP_PERIOD_QUOTAS SPQ
, CN_SRP_PLAN_ASSIGNS_ALL SPA
, CN_QUOTAS_ALL Q
, CN_PERIOD_STATUSES_ALL PS
WHERE SPQ.SRP_PLAN_ASSIGN_ID = SPA.SRP_PLAN_ASSIGN_ID
AND Q.QUOTA_ID = SPQ.QUOTA_ID
AND Q.DELETE_FLAG = 'N'
AND PS.PERIOD_ID= SPQ.PERIOD_ID
AND PS.ORG_ID = SPQ.ORG_ID GROUP BY SPQ.SALESREP_ID
, Q.CREDIT_TYPE_ID
, PS.PERIOD_YEAR
, PS.QUARTER_NUM
, SPQ.PERIOD_ID