[Home] [Help]
748: SUBSTR(md.order_number,1,30) col17,
749: md.organization_id col18,
750: md.demand_class col19,
751: md.demand_id col20
752: from msc_demands md,
753: -- Inline view for Customers
754: ( select mtil.sr_instance_id sr_instance_id,
755: mtil.tp_id tp_id,
756: max(mtil.sr_tp_id) sr_tp_id
774: (
775: -- Sales orders in old plan not in New plan
776: select max (oldp.demand_id),
777: oldp.sr_instance_id
778: from msc_demands oldp,
779: msc_plan_refreshes mpr
780: where oldp.plan_id = p_old_plan_id
781: and oldp.origination_type in (6,30)
782: and NVL(oldp.atp_synchronization_flag,-1) <> 1
785: and oldp.refresh_number < mpr.apps_lrn
786: and oldp.sr_instance_id = mpr.sr_instance_id
787: and not exists (
788: select sales_order_line_id
789: from msc_demands md1
790: where md1.plan_id = p_new_plan_id
791: and md1.origination_type in (6,30)
792: and md1.sr_instance_id = oldp.sr_instance_id
793: and md1.sales_order_line_id = oldp.sales_order_line_id
801: (
802: -- Demands in both plans but have been rescheduled
803: select max(oldp.demand_id),
804: oldp.sr_instance_id
805: from msc_demands oldp,
806: msc_demands newp
807: where oldp.plan_id = p_old_plan_id
808: and newp.plan_id = p_new_plan_id
809: and oldp.origination_type in (6,30)
802: -- Demands in both plans but have been rescheduled
803: select max(oldp.demand_id),
804: oldp.sr_instance_id
805: from msc_demands oldp,
806: msc_demands newp
807: where oldp.plan_id = p_old_plan_id
808: and newp.plan_id = p_new_plan_id
809: and oldp.origination_type in (6,30)
810: and newp.origination_type in (6,30)
812: and oldp.sales_order_line_id = newp.sales_order_line_id
813: and oldp.using_assembly_item_id = newp.using_assembly_item_id
814: and oldp.demand_id = (
815: select max (md1.demand_id)
816: from msc_demands md1,
817: msc_plan_refreshes mpr1
818: where md1.origination_type in (6,30)
819: and md1.plan_id = p_old_plan_id
820: and md1.sr_instance_id = oldp.sr_instance_id
828: )
829: and newp.demand_id = (
830: -- select demand id for a particular sales order line ID
831: select max (md2.demand_id)
832: from msc_demands md2
833: where md2.origination_type in (6,30)
834: and md2.plan_id = p_new_plan_id
835: and md2.sales_order_line_id = oldp.sales_order_line_id
836: and md2.using_assembly_item_id = oldp.using_assembly_item_id
1036: md.atp_session_id col28, -- Use session id for CTO Re-arch
1037: md.ship_set_name col29, -- Ship Set Name
1038: md.arrival_set_name col30, -- Arrival Set Name
1039: 0 col31 -- Insert Flag
1040: from msc_demands md,
1041: -- Inline view for Customers
1042: ( select mtil.sr_instance_id sr_instance_id,
1043: mtil.tp_id tp_id,
1044: max(mtil.sr_tp_id) sr_tp_id
1060: and md.origination_type in (6,30)
1061: and (md.demand_id, md.sr_instance_id) in (
1062: select max (md1.demand_id),
1063: md1.sr_instance_id
1064: from msc_demands md1,
1065: msc_plan_refreshes mpr,
1066: msc_plan_organizations mpo
1067: where md1.plan_id = p_old_plan_id
1068: and md1.origination_type in (6,30)
1248:
1249: conc_debug ('Updating Data at :' || l_sysdate);
1250:
1251: forall l_counter in 1..p_atp_table.calling_module.count
1252: update msc_demands
1253: set atp_synchronization_flag = 1,
1254: last_updated_by = G_FND_USER,
1255: last_update_login = G_FND_USER,
1256: last_update_date = l_sysdate
1264: and NVL(atp_synchronization_flag, -1) <> 1;
1265:
1266: conc_debug ('Rows Updated : ' || sql%rowcount);
1267:
1268: /* Not Needed as component records aren't selected from msc_demands
1269: -- CTO Re-arch changes, need to update demands for ATO Model's options/ OC's
1270: conc_debug ('Updating CTO Component Demands');
1271:
1272: forall l_counter in 1..p_atp_table.calling_module.count
1269: -- CTO Re-arch changes, need to update demands for ATO Model's options/ OC's
1270: conc_debug ('Updating CTO Component Demands');
1271:
1272: forall l_counter in 1..p_atp_table.calling_module.count
1273: update msc_demands
1274: set atp_synchronization_flag = 1,
1275: last_updated_by = G_FND_USER,
1276: last_update_login = G_FND_USER,
1277: last_update_date = l_sysdate