[Home] [Help]
1448: THEN NVL(TRUNC(denorm.forecast_date),TRUNC(denorm.decision_date))
1449: ELSE
1450: TRUNC(denorm.decision_date)
1451: END) opty_rank
1452: FROM as_sales_credits_denorm denorm,
1453: as_sales_lead_opportunity aslo1
1454: WHERE denorm.lead_id = aslo1.opportunity_id (+)
1455: AND denorm.sales_group_id IS NOT NULL
1456: AND denorm.sales_credit_amount IS NOT NULL
1469: AND denorm.forecast_date <= l_limit_date))
1470:
1471: AND
1472: (-- sc level change
1473: exists ( select 1 from as_sales_credits credit
1474: where credit.last_update_date>= G_Start_DATE
1475: and denorm.lead_id = credit.lead_id)
1476: OR denorm.opportunity_last_update_date >= G_Start_DATE
1477: OR -- link to lead works => lead to opportunity
1530:
1531: /*delete from bil.bil_bi_opdtl_f a where exists
1532: (select lead_id from as_leads_all b where
1533: a.opty_id=b.lead_id and b.last_update_date >=G_Start_Date
1534: and b.lead_id not in (select distinct lead_id from as_sales_credits));
1535:
1536: l_cnt:=sql%rowcount;
1537:
1538: dbms_output.put_line('deleted these many rows in ins_new_chgd_opty_incr procedure from fact :'||l_cnt);*/
1669: THEN NVL(TRUNC(denorm.forecast_date),TRUNC(denorm.decision_date))
1670: ELSE
1671: TRUNC(denorm.decision_date)
1672: END) opty_rank
1673: FROM as_sales_credits_denorm denorm,
1674: as_sales_lead_opportunity aslo1
1675: WHERE
1676: denorm.lead_id = aslo1.opportunity_id (+)
1677: --AND denorm.source_promotion_id = codes.source_code_id (+)
1786: -- );
1787:
1788: DELETE FROM BIL_BI_OPDTL_F f
1789: WHERE NOT EXISTS
1790: ( SELECT 1 FROM AS_SALES_CREDITS sc WHERE f.sales_credit_id = sc.sales_credit_id );
1791:
1792:
1793: l_count:=sql%rowcount;
1794: