[Home] [Help]
75:
76: l_stmt_id :=5;
77: select trunc(mp.curr_start_date),trunc(mp.curr_cutoff_date)
78: into l_curr_start_date,l_curr_cutoff_date
79: from msc_plans mp where mp.plan_id=p_plan_id;
80:
81: select min(bkt_start_date), max(bkt_end_date)
82: into l_first_bkt_start_date, l_last_bkt_end_date
83: from msc_plan_buckets
86: -- save owning organization's functional currency
87: begin
88: select o.currency_code
89: into l_owning_currency_code
90: from msc_trading_partners o, msc_plans p
91: where o.sr_instance_id=p.sr_instance_id
92: and o.sr_tp_id=p.organization_id
93: and o.partner_type=3
94: and p.plan_id=p_plan_id;
689: 30,nvl(md.service_level, 50),
690: 81,nvl(md.service_level, 50),
691: null)) service_level
692:
693: from msc_demands md, msc_trading_partners mtp, msc_plans mp,
694: (select msi_1.plan_id,
695: msi_1.sr_instance_id,
696: msi_1.organization_id,
697: msi_1.inventory_item_id,
860:
861:
862: from msc_demands md1,msc_trading_partners mtp1,
863: msc_exception_details med1,
864: msc_plans mp1,
865: (select msi_2.plan_id,
866: msi_2.sr_instance_id,
867: msi_2.organization_id,
868: msi_2.inventory_item_id,
913: trunc(nvl(md1.firm_date,md1.using_assembly_demand_date)),
914: -1 * md1.origination_type,
915: msi1.vmi_flag,
916: nvl(mtp1.currency_code, l_owning_currency_code)
917: order by 1,2,3,4,5,6,7,8,9,10,11,12,13) demand_tbl,msc_plans mp_tbl,
918: MSC_CURRENCY_CONV_MV mcc,msc_phub_customers_mv cmv
919: where
920: mcc.FROM_CURRENCY(+) =demand_tbl.currency_code -- make sure 'XXX' is not a valid currency code
921: and mcc.TO_CURRENCY(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
1347: and demand_item.char1=t1.demand_class(+)
1348: and t1.aggr_type(+) = 0) cum
1349:
1350: where last_date.date2 = cum.order_Date
1351: and last_date.query_id = l_qid_dl_relation ) demand_cum_tbl,msc_plans mp_cum_tbl
1352: where mp_cum_tbl.plan_id = demand_cum_tbl.plan_id;
1353:
1354:
1355: