[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select sup.organization_id, --org id
sup.sr_instance_id, --sr_instance_id
sup.supplier_id,
sup.supplier_site_id,
i.base_item_id,
sup.inventory_item_id, --inventory item id
sup.order_type,
GREATEST ( SUM(decode(sup.firm_planned_type,
1,sup.firm_quantity,
sup.new_order_quantity)),
SUM(nvl(sup.quantity_in_process,0) + nvl(sup.implemented_quantity,0))) quantity, --quantity
SUM(decode(sup.firm_planned_type, 1, sup.firm_quantity -
nvl(sup.quantity_in_process,0) - nvl(sup.implemented_quantity,0),
sup.new_order_quantity - nvl(sup.quantity_in_process,0) -
nvl(sup.implemented_quantity,0))) planned_order_quantity,
SUM(nvl(sup.quantity_in_process,0) + nvl(sup.implemented_quantity,0)) released_quantity,
i.item_name, --publisher item name
i.description, --publisher item desc
NULL, --sup.project_id, --project number
NULL, --sup.task_id, --task number
NULL, --sup.planning_group, --planning group
i.uom_code, --primary uom
i.planner_code, --planner code
mpb.bucket_type,
decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.organization_id, sup.sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
), --key_date
decode(sup.firm_date, null, sup.new_ship_date , null), --ship_date
decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.organization_id, sup.sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
) --reciept_date
from msc_plan_organizations_v ov,
msc_system_items i,
msc_supplies sup,
msc_plan_buckets mpb
where ov.plan_id = p_plan_id and
ov.planned_organization = NVL(p_org_id, ov.planned_organization) and
ov.sr_instance_id = NVL(p_sr_instance_id, ov.sr_instance_id) and
i.plan_id = ov.plan_id and
i.organization_id = ov.planned_organization and
i.sr_instance_id = ov.sr_instance_id and
NVL(i.base_item_id, i.inventory_item_id)
IN (select nvl(i1.base_item_id,i1.inventory_item_id)
from msc_system_items i1
where i1.inventory_item_id = nvl(p_item_id, i.inventory_item_id )
and i1.organization_id = i.organization_id
and i1.plan_id = i.plan_id
and i1.sr_instance_id = i.sr_instance_id) and
i.item_name = nvl(p_item_list,i.item_name) and
NVL(i.planner_code,'-99') = NVL(p_planner_code, NVL(i.planner_code,'-99')) and
NVL(i.abc_class_name,'-99') = NVL(p_abc_class,NVL(i.abc_class_name,'-99')) and
((sup.order_type IN (PLANNED_ORDER,PLANNED_NEW_BUY_ORDER) and
sup.source_supplier_id is not null and
sup.source_supplier_site_id is not null and
sup.source_supplier_id = nvl(p_supplier_id, sup.source_supplier_id) and
sup.source_supplier_site_id = nvl(p_supplier_site_id, sup.source_supplier_site_id)
)
OR
(sup.order_type in (p_purchase_order, p_requisition) and
sup.supplier_id is not null and
sup.supplier_site_id is not null and
sup.supplier_id = nvl(p_supplier_id, sup.supplier_id) and
sup.supplier_site_id = nvl(p_supplier_site_id, sup.supplier_site_id)
)
) and
sup.plan_id = i.plan_id and
sup.organization_id = i.organization_id and
sup.sr_instance_id = i.sr_instance_id and
sup.inventory_item_id = i.inventory_item_id and
nvl(sup.planning_group, '-99') = nvl(p_planning_gp, nvl(sup.planning_group, '-99')) and
nvl(sup.project_id,-99) = nvl(p_project_id, nvl(sup.project_id,-99)) and
nvl(sup.task_id, -99) = nvl(p_task_id, nvl(sup.task_id, -99)) and
decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))) --key_date between horizon
between NVL(p_horizon_start, sysdate - 36500)
and NVL(p_horizon_end, sysdate + 36500)
AND mpb.plan_id = sup.plan_id
and mpb.sr_instance_id = sup.sr_instance_id
and mpb.curr_flag = 1
and nvl(sup.firm_date,sup.new_schedule_date)
between mpb.bkt_start_date and mpb.bkt_end_date
GROUP BY sup.organization_id, --org id
sup.sr_instance_id, --sr_instance_id
sup.supplier_id,
sup.supplier_site_id,
i.base_item_id,
sup.inventory_item_id, --inventory item id
sup.order_type,
i.item_name, --publisher item name
i.description, --publisher item desc
i.uom_code, --primary uom
i.planner_code, --planner code
mpb.bucket_type,
decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.organization_id, sup.sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
), --key_date
decode(sup.firm_date, null, sup.new_ship_date , null) --ship_date
UNION --get modelled suppleirs
select sup.organization_id, --org id
sup.sr_instance_id, --sr_instance_id
t1.modeled_supplier_id,
t1.modeled_supplier_site_id,
i.base_item_id,
sup.inventory_item_id, --inventory item id
sup.order_type,
GREATEST ( SUM(decode(sup.firm_planned_type,
1,sup.firm_quantity,
sup.new_order_quantity)),
SUM(nvl(sup.quantity_in_process,0) + nvl(sup.implemented_quantity,0))) quantity, --quantity
SUM(decode(sup.firm_planned_type, 1, sup.firm_quantity -
nvl(sup.quantity_in_process,0) - nvl(sup.implemented_quantity,0),
sup.new_order_quantity - nvl(sup.quantity_in_process,0) -
nvl(sup.implemented_quantity,0))) planned_order_quantity,
SUM(nvl(sup.quantity_in_process,0) + nvl(sup.implemented_quantity,0)) released_quantity,
i.item_name, --publisher item name
i.description, --publisher item desc
NULL, --sup.project_id, --project number
NULL, --sup.task_id, --task number
NULL, --sup.planning_group, --planning group
i.uom_code, --primary uom
i.planner_code, --planner code
mpb.bucket_type,
decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.organization_id, sup.sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
), --key_date
decode(sup.firm_date, null, sup.new_ship_date , null), --ship_date
decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.organization_id, sup.sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
) --reciept_date
from msc_plan_organizations_v ov,
msc_system_items i,
msc_supplies sup,
msc_trading_partners t1,
msc_plan_buckets mpb
where ov.plan_id = p_plan_id and
ov.planned_organization = NVL(p_org_id, ov.planned_organization) and
ov.sr_instance_id = NVL(p_sr_instance_id, ov.sr_instance_id) and
i.plan_id = ov.plan_id and
i.organization_id = ov.planned_organization and
i.sr_instance_id = ov.sr_instance_id and
NVL(i.base_item_id, i.inventory_item_id)
IN (select nvl(i1.base_item_id,i1.inventory_item_id)
from msc_system_items i1
where i1.inventory_item_id = nvl(p_item_id, i.inventory_item_id )
and i1.organization_id = i.organization_id
and i1.plan_id = i.plan_id
and i1.sr_instance_id = i.sr_instance_id) and
i.item_name = nvl(p_item_list,i.item_name) and
NVL(i.planner_code,'-99') = NVL(p_planner_code, NVL(i.planner_code,'-99')) and
NVL(i.abc_class_name,'-99') = NVL(p_abc_class,NVL(i.abc_class_name,'-99')) and
-- bug#6893383 include planned_external_repair_order, external_repair_order
-- used by SPP plan only when supp is modelled as org
((sup.order_type IN (PLANNED_ORDER,EXPECTED_INBOUND_SHIPMENT,
PLANNED_EXTERNAL_REPAIR_ORDER, l_external_repair_order) and
/*order type EXPECTED_INBOUND_SHIPMENT is for DRP Plan Bug: 3951295*/
--sup.source_supplier_id is null and
-- ignore above two lines, no effect on commenting. Why-external_repair_order populate both supplier_id and source_org_id
--sup.source_supplier_site_id is null and
sup.source_organization_id is not null and
sup.source_sr_instance_id is not null and
sup.source_organization_id <> sup.organization_id and
t1.sr_tp_id = sup.source_organization_id and
t1.sr_instance_id = sup.source_sr_instance_id and
t1.partner_type = 3
)
OR
(sup.order_type in (p_purchase_order, p_requisition) and
sup.source_supplier_id is null and
sup.source_supplier_site_id is null and
sup.source_organization_id is not null and -- bug#7446024
sup.source_sr_instance_id is not null and
t1.sr_tp_id = sup.source_organization_id and
t1.sr_instance_id = sup.source_sr_instance_id and
t1.partner_type = 3
)
) and
t1.modeled_supplier_id = nvl(p_supplier_id, t1.modeled_supplier_id) and
t1.modeled_supplier_site_id = nvl(p_supplier_site_id, t1.modeled_supplier_site_id) and
t1.modeled_supplier_id is not null and
t1.modeled_supplier_site_id is not null and
sup.plan_id = i.plan_id and
sup.organization_id = i.organization_id and
sup.sr_instance_id = i.sr_instance_id and
sup.inventory_item_id = i.inventory_item_id and
nvl(sup.planning_group, '-99') = nvl(p_planning_gp, nvl(sup.planning_group, '-99')) and
nvl(sup.project_id,-99) = nvl(p_project_id, nvl(sup.project_id,-99)) and
nvl(sup.task_id, -99) = nvl(p_task_id, nvl(sup.task_id, -99)) and
decode(sup.firm_date,null,nvl(sup.new_dock_date, msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0)))
between NVL(p_horizon_start, sysdate - 36500 )
and NVL(p_horizon_end, sysdate + 36500)
and mpb.plan_id = sup.plan_id
and mpb.sr_instance_id = sup.sr_instance_id
and mpb.curr_flag = 1
and nvl(sup.firm_date,sup.new_schedule_date)
between mpb.bkt_start_date and mpb.bkt_end_date
GROUP BY sup.organization_id,
sup.sr_instance_id,
t1.modeled_supplier_id,
t1.modeled_supplier_site_id,
i.base_item_id,
sup.inventory_item_id,
sup.order_type,
i.item_name,
i.description,
i.uom_code,
i.planner_code, --planner code
mpb.bucket_type,
decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.organization_id, sup.sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
), --key_date
decode(sup.firm_date, null, sup.new_ship_date , null) -- ship date
ORDER BY 1,2,3,4,5,6,7,11,12,16,18,19;
select sup.source_organization_id, --org id
sup.source_sr_instance_id, --sr_instance_id
t1.modeled_supplier_id,
t1.modeled_supplier_site_id,
i.base_item_id,
sup.inventory_item_id, --inventory item id
decode (sup.order_type,
EXPECTED_INBOUND_SHIPMENT, PLANNED_TRANSFER_DEF,
INTRANSIT_SHIPMENT, INTRANSIT_SHIPMENT_DEF,
INTRANSIT_RECEIPT , INTRANSIT_RECEIPT_DEF
) order_type, --internal order_types local to this package
GREATEST ( SUM(decode(sup.firm_planned_type,
1,sup.firm_quantity,
sup.new_order_quantity)),
SUM(nvl(sup.quantity_in_process,0) + nvl(sup.implemented_quantity,0))) quantity, --quantity
SUM(decode(sup.firm_planned_type, 1, sup.firm_quantity -
nvl(sup.quantity_in_process,0) - nvl(sup.implemented_quantity,0),
sup.new_order_quantity - nvl(sup.quantity_in_process,0) -
nvl(sup.implemented_quantity,0))) planned_order_quantity,
SUM(nvl(sup.quantity_in_process,0) + nvl(sup.implemented_quantity,0)) released_quantity,
i.item_name, --publisher item name
i.description, --publisher item desc
NULL, --sup.project_id, --project number
NULL, --sup.task_id, --task number
NULL, --sup.planning_group, --planning group
i.uom_code, --primary uom
NULL, --i.planner_code, --planner code
mpb.bucket_type,
decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.source_organization_id, sup.source_sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
msc_calendar.DATE_OFFSET(sup.source_organization_id,sup.source_sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
), --key_date
decode(sup.firm_date, null, sup.new_ship_date , null), --ship_date
decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.source_organization_id, sup.source_sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
msc_calendar.DATE_OFFSET(sup.source_organization_id,sup.source_sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
), --reciept_date
-- order_number => order number for intransit shipment, NULL for Expected inbound shipment
sup.order_number order_number, -- use only for intransit_shipment_def and intransit_receipt_def
NULL line_number -- dummy type not to be used
from msc_plan_organizations_v ov,
msc_system_items i,
msc_supplies sup,
msc_trading_partners t1,
msc_plan_buckets mpb
where ov.plan_id = p_plan_id and
ov.planned_organization = NVL(p_org_id, ov.planned_organization) and
ov.sr_instance_id = NVL(p_sr_instance_id, ov.sr_instance_id) and
ov.plan_id = sup.plan_id and
ov.planned_organization = sup.source_organization_id and
ov.sr_instance_id = sup.source_sr_instance_id and
/* Check for defective item type*/
sup.item_type_value = 2 and -- for bad goods only
NVL(i.base_item_id, i.inventory_item_id)
IN (select nvl(i1.base_item_id,i1.inventory_item_id)
from msc_system_items i1
where i1.inventory_item_id = nvl(p_item_id, i.inventory_item_id )
and i1.organization_id = i.organization_id
and i1.plan_id = i.plan_id
and i1.sr_instance_id = i.sr_instance_id) and
i.item_name = nvl(p_item_list,i.item_name) and
NVL(i.planner_code,'-99') = NVL(p_planner_code, NVL(i.planner_code,'-99')) and
NVL(i.abc_class_name,'-99') = NVL(p_abc_class,NVL(i.abc_class_name,'-99')) and
(
-- Planned Transfer : Planned Transfer created at supplier modelled org for defective warehouse
sup.order_type in (EXPECTED_INBOUND_SHIPMENT, INTRANSIT_SHIPMENT, INTRANSIT_RECEIPT ) and
sup.source_supplier_id is null and
sup.source_supplier_site_id is null and
sup.source_organization_id is not null and
sup.source_sr_instance_id is not null and
sup.source_organization_id <> sup.organization_id and
t1.sr_tp_id = sup.organization_id and
t1.sr_instance_id = sup.sr_instance_id and
t1.partner_type = 3 and
t1.modeled_supplier_id is not null and
t1.modeled_supplier_site_id is not null
) and
t1.modeled_supplier_id = nvl(p_supplier_id, t1.modeled_supplier_id) and
t1.modeled_supplier_site_id = nvl(p_supplier_site_id, t1.modeled_supplier_site_id) and
t1.modeled_supplier_id is not null and
t1.modeled_supplier_site_id is not null and
sup.plan_id = i.plan_id and
sup.source_organization_id = i.organization_id and
sup.source_sr_instance_id = i.sr_instance_id and
sup.inventory_item_id = i.inventory_item_id and
nvl(sup.planning_group, '-99') = nvl(p_planning_gp, nvl(sup.planning_group, '-99')) and
nvl(sup.project_id,-99) = nvl(p_project_id, nvl(sup.project_id,-99)) and
nvl(sup.task_id, -99) = nvl(p_task_id, nvl(sup.task_id, -99)) and
decode(sup.firm_date,null,nvl(sup.new_dock_date, msc_calendar.DATE_OFFSET(sup.source_organization_id,sup.source_sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
msc_calendar.DATE_OFFSET(sup.source_organization_id,sup.source_sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0)))
between NVL(p_horizon_start, sysdate - 36500 )
and NVL(p_horizon_end, sysdate + 36500)
and mpb.plan_id = sup.plan_id
and mpb.sr_instance_id = sup.source_sr_instance_id
and mpb.curr_flag = 1
and nvl(sup.firm_date,sup.new_schedule_date)
between mpb.bkt_start_date and mpb.bkt_end_date
GROUP BY sup.source_organization_id, --org id
sup.source_sr_instance_id,
t1.modeled_supplier_id,
t1.modeled_supplier_site_id,
i.base_item_id,
sup.inventory_item_id,
decode (sup.order_type,EXPECTED_INBOUND_SHIPMENT, PLANNED_TRANSFER_DEF,
INTRANSIT_SHIPMENT, INTRANSIT_SHIPMENT_DEF,
INTRANSIT_RECEIPT , INTRANSIT_RECEIPT_DEF ),
i.item_name,
i.description,
i.uom_code,
NULL, --i.planner_code, --planner code
mpb.bucket_type,
decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.source_organization_id, sup.source_sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
msc_calendar.DATE_OFFSET(sup.source_organization_id,sup.source_sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
), --key_date
decode(sup.firm_date, null, sup.new_ship_date , null), --ship_date
sup.order_number
UNION
/* dmd_satisfied_date NOT NULL -> ISO that are unshipped will have dmd_satisfie_date ,
NULL -> Parially shipped or shipped and received ISO */
select dem.organization_id, --org id
dem.sr_instance_id, --sr_instance_id
t1.modeled_supplier_id,
t1.modeled_supplier_site_id,
i.base_item_id,
dem.inventory_item_id, --inventory item id
decode (dem.origination_type,
SALES_ORDER ,ISO_DEF
) order_type, --internal order_types local to this package
SUM(NVL(dem.old_demand_quantity,0)) quantity, --quantity
0 planned_order_quantity, -- planned_order_quantity
0 released_quantity, -- released_quantity
i.item_name, --publisher item name
i.description, --publisher item desc
NULL, --project number
NULL, --task number
NULL, --planning group
i.uom_code, --primary uom
NULL, --i.planner_code, --planner code
mpb.bucket_type, -- bucket_type
dem.schedule_ship_date, --key_date
dem.schedule_ship_date, --ship_date
dem.schedule_arrival_date, --reciept_date
-- Order_number => .Order Only.ORDER ENTRY()
decode(instr(dem.order_number,'.'), 0 , dem.order_number, substr(dem.order_number,1,instr(dem.order_number,'.')-1 )),--
TRIM(decode(instr(dem.order_number,'(',-1,1),0,null,substr(dem.order_number,instr(dem.order_number,'(',-1,1) + 1,instr(dem.order_number,')',-1,1) - instr(dem.order_number,'(',-1,1) -1 ))) --
from msc_plan_organizations_v ov,
msc_system_items i,
msc_demands dem,
msc_trading_partners t1,
msc_plan_buckets mpb
where ov.plan_id = p_plan_id and
ov.planned_organization = NVL(p_org_id, ov.planned_organization) and
ov.sr_instance_id = NVL(p_sr_instance_id, ov.sr_instance_id) and
ov.plan_id = dem.plan_id and
ov.planned_organization = dem.organization_id and
ov.sr_instance_id = dem.sr_instance_id and
ov.plan_id = i.plan_id and
ov.planned_organization = i.organization_id and
ov.sr_instance_id = i.sr_instance_id and
/* Check for defective item type*/
dem.item_type_value = 2 and -- for bad goods only
NVL(i.base_item_id, i.inventory_item_id)
IN (select nvl(i1.base_item_id,i1.inventory_item_id)
from msc_system_items i1
where i1.inventory_item_id = nvl(p_item_id, i.inventory_item_id )
and i1.organization_id = i.organization_id
and i1.plan_id = i.plan_id
and i1.sr_instance_id = i.sr_instance_id) and
i.item_name = nvl(p_item_list,i.item_name) and
NVL(i.planner_code,'-99') = NVL(p_planner_code, NVL(i.planner_code,'-99')) and
NVL(i.abc_class_name,'-99') = NVL(p_abc_class,NVL(i.abc_class_name,'-99')) and
(
dem.origination_type in (SALES_ORDER) and
dem.disposition_id is not null and -- verify if SO is internal (ISO)
--dem.customer_id is null and
--dem.customer_site_id is null and
dem.source_organization_id is not null and -- supplier_modeled org
dem.source_org_instance_id is not null and
dem.source_organization_id <> dem.organization_id and
t1.sr_tp_id = dem.source_organization_id and
t1.sr_instance_id = dem.source_org_instance_id and
t1.partner_type = 3 and
t1.modeled_supplier_id is not null and
t1.modeled_supplier_site_id is not null
) and
t1.modeled_supplier_id = nvl(p_supplier_id, t1.modeled_supplier_id) and
t1.modeled_supplier_site_id = nvl(p_supplier_site_id, t1.modeled_supplier_site_id) and
dem.plan_id = i.plan_id and
dem.organization_id = i.organization_id and
dem.sr_instance_id = i.sr_instance_id and
dem.inventory_item_id = i.inventory_item_id and
nvl(dem.planning_group, '-99') = nvl(p_planning_gp, nvl(dem.planning_group, '-99')) and
nvl(dem.project_id,-99) = nvl(p_project_id, nvl(dem.project_id,-99)) and
nvl(dem.task_id, -99) = nvl(p_task_id, nvl(dem.task_id, -99)) and
dem.dmd_satisfied_date between nvl(p_horizon_start, dem.dmd_satisfied_date)
and nvl(p_horizon_end, dem.dmd_satisfied_date)
and exists ( select 'PICK_ONLY_UNSHIPPED_ISO'
from msc_sales_orders mso
where mso.demand_id = dem.demand_id
and mso.sr_instance_id = dem.sr_instance_id
and (mso.primary_uom_quantity - mso.completed_quantity) > 0 )
and mpb.plan_id = dem.plan_id
and mpb.sr_instance_id = dem.sr_instance_id
and mpb.curr_flag = 1
and dem.dmd_satisfied_date between mpb.bkt_start_date and mpb.bkt_end_date
GROUP BY dem.organization_id,
dem.sr_instance_id,
t1.modeled_supplier_id,
t1.modeled_supplier_site_id,
i.base_item_id,
dem.inventory_item_id,
decode (dem.origination_type,SALES_ORDER , ISO_DEF),
i.item_name,
i.description,
i.uom_code,
NULL, --i.planner_code, --planner code
mpb.bucket_type,
dem.schedule_ship_date, --ship date or key date
dem.schedule_arrival_date, --reciept_date
decode(instr(dem.order_number,'.'), 0 , dem.order_number, substr(dem.order_number,1,instr(dem.order_number,'.')-1 )),--
TRIM(decode(instr(dem.order_number,'(',-1,1),0,null,substr(dem.order_number,instr(dem.order_number,'(',-1,1) + 1,instr(dem.order_number,')',-1,1) - instr(dem.order_number,'(',-1,1) -1 ))) --
ORDER BY 1,2,3,4,5,6,7,11,12,16,18,19;
select
fnd_global.user_id,
fnd_global.user_name --,
--fnd_global.resp_name,
--fnd_global.application_name
into l_user_id,
l_user_name --,
--l_resp_name,
--l_application_name
from dual;
SELECT language_code
INTO l_language_code
FROM fnd_languages
WHERE nls_language = l_language;
select compile_designator,curr_plan_type
into p_designator,l_plan_type
from msc_plans
where plan_id = p_plan_id;
select instance_id
into p_sr_instance_id
from msc_apps_instances
where instance_code = p_inst_code;
select sr_tp_id
into p_org_id
from msc_trading_partners
where organization_code = p_org_code and
sr_instance_id = p_sr_instance_id and
partner_type = 3 and
company_id is null;
SELECT item_name
INTO l_item_name
FROM msc_items
WHERE inventory_item_id = p_item_id;
SELECT partner_name
INTO l_supp_name
FROM msc_trading_partners
WHERE partner_id = p_supplier_id;
SELECT tp_site_code
INTO l_supp_site
FROM msc_trading_partner_sites
WHERE partner_id = p_supplier_id
AND partner_site_id = p_supplier_site_id;
l_log_message := get_message('MSC','MSC_X_PUB_DELETE_OF',l_language_code) || fnd_global.local_chr(10);
select inventory_item_id
into l_item_id
from msc_items
where item_name = l_item_list;
delete_old_forecast(
p_plan_id,
p_org_id,
p_sr_instance_id,
p_planner_code,
p_abc_class,
l_item_id,
p_planning_gp,
p_project_id,
p_task_id,
p_supplier_id,
p_supplier_site_id,
l_horizon_start,
l_horizon_end,
p_overwrite
);
insert_into_sup_dem(
t_pub
,t_pub_id
,t_pub_site
,t_pub_site_id
,t_item_id
,t_order_type
,t_qty
,t_planned_order_qty
,t_released_qty
,t_supp
,t_supp_id
,t_supp_site
,t_supp_site_id
,t_owner_item_name
,t_owner_item_desc
,t_base_item_id
,t_base_item_name
,t_base_item_desc
,t_proj_number
,t_task_number
,t_planning_gp
,t_uom_code
,t_planner_code
,t_bucket_type
,t_key_date
,t_ship_date
,t_receipt_date
,t_master_item_name
,t_master_item_desc
,t_supp_item_name
,t_supp_item_desc
,t_tp_uom
,t_tp_qty
,t_tp_planned_order_qty
,t_tp_released_qty
,l_version
,p_designator
,l_user_id
,l_language_code
);
delete_old_forecast(
p_plan_id,
p_org_id,
p_sr_instance_id,
p_planner_code,
p_abc_class,
p_item_id,
p_planning_gp,
p_project_id,
p_task_id,
p_supplier_id,
p_supplier_site_id,
l_horizon_start,
l_horizon_end,
p_overwrite
);
insert_into_sup_dem(
t_pub
,t_pub_id
,t_pub_site
,t_pub_site_id
,t_item_id
,t_order_type
,t_qty
,t_planned_order_qty
,t_released_qty
,t_supp
,t_supp_id
,t_supp_site
,t_supp_site_id
,t_owner_item_name
,t_owner_item_desc
,t_base_item_id
,t_base_item_name
,t_base_item_desc
,t_proj_number
,t_task_number
,t_planning_gp
,t_uom_code
,t_planner_code
,t_bucket_type
,t_key_date
,t_ship_date
,t_receipt_date
,t_master_item_name
,t_master_item_desc
,t_supp_item_name
,t_supp_item_desc
,t_tp_uom
,t_tp_qty
,t_tp_planned_order_qty
,t_tp_released_qty
,l_version
,p_designator
,l_user_id
,l_language_code
);
insert_into_sup_dem_rf_dos(
t_pub
,t_pub_id
,t_pub_site
,t_pub_site_id
,t_item_id
,t_order_type
,t_qty
,t_planned_order_qty
,t_released_qty
,t_supp
,t_supp_id
,t_supp_site
,t_supp_site_id
,t_owner_item_name
,t_owner_item_desc
,t_base_item_id
,t_base_item_name
,t_base_item_desc
,t_proj_number
,t_task_number
,t_planning_gp
,t_uom_code
,t_planner_code
,t_bucket_type
,t_key_date
,t_ship_date
,t_receipt_date
,t_order_num -- bug#7310179
,t_line_num -- bug#7310179
,t_master_item_name
,t_master_item_desc
,t_supp_item_name
,t_supp_item_desc
,t_tp_uom
,t_tp_qty
,t_tp_planned_order_qty
,t_tp_released_qty
,l_version
,p_designator
,l_user_id
,l_language_code
,p_publish_dos --bug#6893383
);
SELECT 1 INTO l_records_exist
FROM dual
WHERE exists ( SELECT 1
FROM msc_sup_dem_entries
WHERE plan_id = -1
AND publisher_order_type = 2
AND designator = p_designator
AND version = TO_CHAR(l_version));
UPDATE msc_plans
SET publish_fcst_version = l_version
WHERE plan_id = p_plan_id;
select distinct mis.processing_lead_time,
mis.using_organization_id
from msc_item_suppliers mis,
msc_trading_partner_maps m,
msc_trading_partner_maps m2,
msc_company_relationships r
where mis.plan_id = -1 and
mis.inventory_item_id = p_item_id and
mis.organization_id = p_org_id and
mis.sr_instance_id = p_inst_id and
r.relationship_type = 2 and
r.subject_id = 1 and
r.object_id = p_supp_id and
m.map_type = 1 and
m.company_key = r.relationship_id and
mis.supplier_id = m.tp_key and
m2.map_type = 3 and
m2.company_key = p_supp_site_id and
nvl(mis.supplier_site_id, m2.tp_key) = m2.tp_key
order by mis.using_organization_id desc;
select c.company_id,
c.company_name,
s.company_site_id,
s.company_site_name
into t_pub_id(j),
t_pub(j),
t_pub_site_id(j),
t_pub_site(j)
from msc_companies c,
msc_company_sites s,
msc_trading_partner_maps m,
msc_trading_partners t
where t.sr_tp_id = t_org_id(j) and
t.sr_instance_id = t_sr_instance_id(j) and
t.partner_type = 3 and
m.tp_key = t.partner_id and
m.map_type = 2 and
s.company_site_id = m.company_key and
c.company_id = s.company_id;
select distinct c.company_id,
c.company_name
into t_supp_id(j),
t_supp(j)
from msc_companies c,
msc_company_sites s,
msc_trading_partner_maps m,
msc_company_relationships r
where m.tp_key = t_source_supp_id(j) and
m.map_type = 1 and
r.relationship_id = m.company_key and
r.subject_id = t_pub_id(j) and
r.relationship_type = 2 and
c.company_id = r.object_id;
select s.company_site_id,
s.company_site_name
into t_supp_site_id(j),
t_supp_site(j)
from msc_company_sites s,
msc_trading_partner_maps m
where m.tp_key = t_source_supp_site_id(j) and
m.map_type = 3 and
s.company_site_id = m.company_key and
s.company_id = t_supp_id(j);
select item_name,
description
into t_master_item_name(j),
t_master_item_desc(j)
from msc_items
where inventory_item_id = t_item_id(j);
select msi.item_name,
msi.description,
msi.uom_code
into t_supp_item_name(j),
t_supp_item_desc(j),
t_tp_uom(j)
from msc_system_items msi,
msc_trading_partners part,
msc_trading_partner_maps map
where msi.plan_id = -1 and
msi.inventory_item_id = t_item_id(j) and
msi.organization_id = part.sr_tp_id and
msi.sr_instance_id = part.sr_instance_id and
part.partner_id = map.tp_key and
map.map_type = 2 and
map.company_key = t_supp_site_id(j) and
nvl(part.company_id,-1) = t_supp_id(j);
select distinct mis.supplier_item_name,
mis.description,
mis.uom_code
into t_supp_item_name(j),
t_supp_item_desc(j),
t_tp_uom(j)
from msc_item_suppliers mis,
msc_trading_partner_maps m,
msc_trading_partner_maps m2,
msc_company_relationships r
where mis.plan_id = -1 and
mis.inventory_item_id = t_item_id(j) and
mis.organization_id = t_org_id(j) and
mis.sr_instance_id = t_sr_instance_id(j) and
r.relationship_type = 2 and
r.subject_id = 1 and
r.object_id = t_supp_id(j) and
m.map_type = 1 and
m.company_key = r.relationship_id and
mis.supplier_id = m.tp_key and
m2.map_type = 3 and
m2.company_key = t_supp_site_id(j) and
nvl(mis.supplier_site_id, m2.tp_key) = m2.tp_key;
select description
into t_supp_item_desc(j)
from msc_system_items
where plan_id = -1 and
inventory_item_id = t_item_id(j) and
organization_id = t_org_id(j) and
sr_instance_id = t_sr_instance_id(j);
select item_name, description
into t_base_item_name(j),
t_base_item_desc(j)
from msc_items
where inventory_item_id = t_base_item_id(j);
PROCEDURE insert_into_sup_dem (
t_pub IN companyNameList,
t_pub_id IN numberList,
t_pub_site IN companySiteList,
t_pub_site_id IN numberList,
t_item_id IN numberList,
t_order_type IN numberList,
t_qty IN numberList,
t_planned_order_qty IN numberList,
t_released_qty IN numberList,
t_supp IN companyNameList,
t_supp_id IN numberList,
t_supp_site IN companySiteList,
t_supp_site_id IN numberList,
t_owner_item_name IN itemNameList,
t_owner_item_desc IN itemDescList,
t_base_item_id IN numberList,
t_base_item_name IN itemNameList,
t_base_item_desc IN itemDescList,
t_proj_number IN numberList,
t_task_number IN numberList,
t_planning_gp IN planningGroupList,
t_uom_code IN itemUomList,
t_planner_code IN plannerCodeList,
t_bucket_type IN numberList,
t_key_date IN dateList,
t_ship_date IN dateList,
t_receipt_date IN dateList,
t_master_item_name IN itemNameList,
t_master_item_desc IN itemDescList,
t_supp_item_name IN itemNameList,
t_supp_item_desc IN itemDescList,
t_tp_uom IN itemUomList,
t_tp_qty IN numberList,
t_tp_planned_order_qty IN numberList,
t_tp_released_qty IN numberList,
p_version IN varchar2,
p_designator IN varchar2,
p_user_id IN number,
p_language_code IN varchar2
) IS
l_order_type_desc varchar2(80);
log_message('In insert_into_sup_dem');
log_message('Records to be inserted : ' || t_pub_id.COUNT);
update msc_sup_dem_entries
set quantity = quantity + t_qty(j),
primary_quantity = primary_quantity + t_qty(j),
tp_quantity = tp_quantity + t_tp_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_base_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = ORDER_FORECAST;
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
ORDER_FORECAST,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_base_item_id(j),
t_base_item_name(j),
t_base_item_name(j),
t_base_item_name(j),
t_supp_item_name(j),
nvl(t_base_item_desc(j), t_owner_item_desc(j)),
t_base_item_desc(j),
t_base_item_desc(j),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
t_key_date(j),
t_ship_date(j),
t_receipt_date(j),
t_qty(j),
t_qty(j),
t_tp_qty(j),
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator);
update msc_sup_dem_entries
set quantity = quantity + t_qty(j),
primary_quantity = primary_quantity + t_qty(j),
tp_quantity = tp_quantity + t_tp_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = ORDER_FORECAST;
update msc_sup_dem_entries
set quantity = quantity + t_qty(j),
primary_quantity = primary_quantity + t_qty(j),
tp_quantity = tp_quantity + t_tp_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_base_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = ORDER_FORECAST;
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
ORDER_FORECAST,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_base_item_id(j),
t_base_item_name(j),
t_base_item_name(j),
t_base_item_name(j),
t_supp_item_name(j),
nvl(t_base_item_desc(j), t_owner_item_desc(j)),
t_base_item_desc(j),
t_base_item_desc(j),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
t_key_date(j),
t_ship_date(j),
t_receipt_date(j),
t_qty(j),
t_qty(j),
t_tp_qty(j),
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator);
update msc_sup_dem_entries
set quantity = quantity + t_qty(j),
primary_quantity = primary_quantity + t_qty(j),
tp_quantity = tp_quantity + t_tp_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = ORDER_FORECAST;
update msc_sup_dem_entries
set quantity = quantity + t_qty(j),
primary_quantity = primary_quantity + t_qty(j),
tp_quantity = tp_quantity + t_tp_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_base_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = ORDER_FORECAST;
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
ORDER_FORECAST,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_base_item_id(j),
t_base_item_name(j),
t_base_item_name(j),
t_base_item_name(j),
t_supp_item_name(j),
nvl(t_base_item_desc(j), t_owner_item_desc(j)),
t_base_item_desc(j),
t_base_item_desc(j),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
t_key_date(j),
t_ship_date(j),
t_receipt_date(j),
t_qty(j),
t_qty(j),
t_tp_qty(j),
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator);
update msc_sup_dem_entries
set quantity = quantity + t_qty(j),
primary_quantity = primary_quantity + t_qty(j),
tp_quantity = tp_quantity + t_tp_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = ORDER_FORECAST;
update msc_sup_dem_entries
set quantity = quantity + t_qty(j),
primary_quantity = primary_quantity + t_qty(j),
tp_quantity = tp_quantity + t_tp_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_base_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = ORDER_FORECAST;
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
ORDER_FORECAST,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_base_item_id(j),
t_base_item_name(j),
t_base_item_name(j),
t_base_item_name(j),
t_supp_item_name(j),
nvl(t_base_item_desc(j), t_owner_item_desc(j)),
t_base_item_desc(j),
t_base_item_desc(j),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
t_key_date(j),
t_ship_date(j),
t_receipt_date(j),
t_qty(j),
t_qty(j),
t_tp_qty(j),
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator);
update msc_sup_dem_entries
set quantity = quantity + t_qty(j),
primary_quantity = primary_quantity + t_qty(j),
tp_quantity = quantity + t_tp_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = ORDER_FORECAST;
update msc_sup_dem_entries
set quantity = quantity + t_planned_order_qty(j),
primary_quantity = primary_quantity + t_planned_order_qty(j) ,
tp_quantity = tp_quantity + t_tp_planned_order_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = CP_PLANNED_ORDER;
update msc_sup_dem_entries
set quantity = quantity + t_released_qty(j),
primary_quantity = primary_quantity + t_released_qty(j),
tp_quantity = tp_quantity + t_tp_released_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = CP_RELEASED_PLANNED_ORDER;
update msc_sup_dem_entries
set quantity = quantity + t_qty(j),
primary_quantity = primary_quantity + t_qty(j),
tp_quantity = tp_quantity + t_tp_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_base_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = ORDER_FORECAST;
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
ORDER_FORECAST,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_base_item_id(j),
t_base_item_name(j),
t_base_item_name(j),
t_base_item_name(j),
t_supp_item_name(j),
nvl(t_base_item_desc(j), t_owner_item_desc(j)),
t_base_item_desc(j),
t_base_item_desc(j),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
t_key_date(j),
t_ship_date(j),
t_receipt_date(j),
t_qty(j),
t_qty(j),
t_tp_qty(j),
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator);
update msc_sup_dem_entries
set quantity = quantity + t_qty(j),
primary_quantity = primary_quantity + t_qty(j),
tp_quantity = tp_quantity + t_tp_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = ORDER_FORECAST;
update msc_sup_dem_entries
set quantity = quantity + t_qty(j),
primary_quantity = primary_quantity + t_qty(j),
tp_quantity = tp_quantity + t_tp_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_base_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = ORDER_FORECAST;
update msc_sup_dem_entries
set quantity = quantity + t_qty(j),
primary_quantity = primary_quantity + t_qty(j),
tp_quantity = tp_quantity + t_tp_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = ORDER_FORECAST;
update msc_sup_dem_entries
set quantity = quantity + t_qty(j),
primary_quantity = primary_quantity + t_qty(j) ,
tp_quantity = tp_quantity + t_tp_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = CP_PURCHASE_ORDER_FROM_PLAN;
update msc_sup_dem_entries
set quantity = quantity + t_qty(j),
primary_quantity = primary_quantity + t_qty(j),
tp_quantity = tp_quantity + t_tp_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_base_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = ORDER_FORECAST;
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
ORDER_FORECAST,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_base_item_id(j),
t_base_item_name(j),
t_base_item_name(j),
t_base_item_name(j),
t_supp_item_name(j),
nvl(t_base_item_desc(j), t_owner_item_desc(j)),
t_base_item_desc(j),
t_base_item_desc(j),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
t_key_date(j),
t_ship_date(j),
t_receipt_date(j),
t_qty(j),
t_qty(j),
t_tp_qty(j),
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator);
update msc_sup_dem_entries
set quantity = quantity + t_qty(j),
primary_quantity = primary_quantity + t_qty(j),
tp_quantity = quantity + t_tp_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = ORDER_FORECAST;
update msc_sup_dem_entries
set quantity = quantity + t_planned_order_qty(j),
primary_quantity = primary_quantity + t_planned_order_qty(j) ,
tp_quantity = tp_quantity + t_tp_planned_order_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = CP_PLANNED_INBOUND_SHIPMENT;
update msc_sup_dem_entries
set quantity = quantity + t_released_qty(j),
primary_quantity = primary_quantity + t_released_qty(j),
tp_quantity = tp_quantity + t_tp_released_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = CP_RELEASED_INBOUND_SHIPMENT;
update msc_sup_dem_entries
set quantity = quantity + t_qty(j),
primary_quantity = primary_quantity + t_qty(j) ,
tp_quantity = tp_quantity + t_tp_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_base_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = ORDER_FORECAST;
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
ORDER_FORECAST,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_base_item_id(j),
t_base_item_name(j),
t_base_item_name(j),
t_base_item_name(j),
t_supp_item_name(j),
nvl(t_base_item_desc(j), t_owner_item_desc(j)),
t_base_item_desc(j),
t_base_item_desc(j),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
t_key_date(j),
t_ship_date(j),
t_receipt_date(j),
t_qty(j),
t_qty(j),
t_tp_qty(j),
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator);
update msc_sup_dem_entries
set quantity = quantity + t_qty(j),
primary_quantity = primary_quantity + t_qty(j) ,
tp_quantity = tp_quantity + t_tp_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = ORDER_FORECAST;
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator,
base_item_id,
base_item_name
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
CP_PLANNED_ORDER,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_item_id(j),
t_master_item_name(j),
t_owner_item_name(j),
t_owner_item_name(j),
t_supp_item_name(j),
nvl(t_master_item_desc(j), t_owner_item_desc(j)),
t_owner_item_desc(j),
t_owner_item_desc(j),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
t_key_date(j),
t_ship_date(j),
t_receipt_date(j),
l_planned_order_qty,
l_planned_order_qty,
l_tp_planned_order_qty,
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator,
t_base_item_id(j),
t_base_item_name(j));
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator,
base_item_id,
base_item_name
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
CP_RELEASED_PLANNED_ORDER,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_item_id(j),
t_master_item_name(j),
t_owner_item_name(j),
t_owner_item_name(j),
t_supp_item_name(j),
nvl(t_master_item_desc(j), t_owner_item_desc(j)),
t_owner_item_desc(j),
t_owner_item_desc(j),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
t_key_date(j),
t_ship_date(j),
t_receipt_date(j),
l_released_qty,
l_released_qty,
l_tp_released_qty,
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator,
t_base_item_id(j),
t_base_item_name(j));
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator,
base_item_id,
base_item_name
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
CP_PURCHASE_ORDER_FROM_PLAN,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_item_id(j),
t_master_item_name(j),
t_owner_item_name(j),
t_owner_item_name(j),
t_supp_item_name(j),
nvl(t_master_item_desc(j), t_owner_item_desc(j)),
t_owner_item_desc(j),
t_owner_item_desc(j),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
t_key_date(j),
t_ship_date(j),
t_receipt_date(j),
t_qty(j),
t_qty(j),
t_tp_qty(j),
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator,
t_base_item_id(j),
t_base_item_name(j));
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator,
base_item_id,
base_item_name
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
CP_PLANNED_INBOUND_SHIPMENT,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_item_id(j),
t_master_item_name(j),
t_owner_item_name(j),
t_owner_item_name(j),
t_supp_item_name(j),
nvl(t_master_item_desc(j), t_owner_item_desc(j)),
t_owner_item_desc(j),
t_owner_item_desc(j),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
t_key_date(j),
t_ship_date(j),
t_receipt_date(j),
l_planned_order_qty,
l_planned_order_qty,
l_tp_planned_order_qty,
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator,
t_base_item_id(j),
t_base_item_name(j));
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator,
base_item_id,
base_item_name
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
CP_RELEASED_INBOUND_SHIPMENT,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_item_id(j),
t_master_item_name(j),
t_owner_item_name(j),
t_owner_item_name(j),
t_supp_item_name(j),
nvl(t_master_item_desc(j), t_owner_item_desc(j)),
t_owner_item_desc(j),
t_owner_item_desc(j),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
t_key_date(j),
t_ship_date(j),
t_receipt_date(j),
l_released_qty,
l_released_qty,
l_tp_released_qty,
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator,
t_base_item_id(j),
t_base_item_name(j));
SELECT count(*)
INTO l_count
FROM msc_sup_dem_entries
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = l_item_id
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = ORDER_FORECAST;
update msc_sup_dem_entries
set quantity = quantity + t_qty(j),
primary_quantity = primary_quantity + t_qty(j) ,
tp_quantity = tp_quantity + t_tp_qty(j)
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = l_item_id
and trunc(key_date) = trunc(t_key_date(j))
and publisher_order_type = ORDER_FORECAST;
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
ORDER_FORECAST,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
l_item_id,
nvl(l_base_item_name, t_master_item_name(j)),
nvl(l_base_item_name, t_owner_item_name(j)),
nvl(l_base_item_name, t_owner_item_name(j)),
t_supp_item_name(j),
nvl(l_base_item_desc, nvl(t_master_item_desc(j), t_owner_item_desc(j))),
nvl(l_base_item_desc, t_owner_item_desc(j)),
nvl(l_base_item_desc, t_owner_item_desc(j)),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
t_key_date(j),
t_ship_date(j),
t_receipt_date(j),
t_qty(j),
t_qty(j),
t_tp_qty(j),
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'insert failed on else statement with base item ' || sqlerrm);
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator,
base_item_id,
base_item_name
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
CP_PLANNED_ORDER,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_item_id(j),
t_master_item_name(j),
t_owner_item_name(j),
t_owner_item_name(j),
t_supp_item_name(j),
nvl(t_master_item_desc(j), t_owner_item_desc(j)),
t_owner_item_desc(j),
t_owner_item_desc(j),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
t_key_date(j),
t_ship_date(j),
t_receipt_date(j),
l_planned_order_qty,
l_planned_order_qty,
l_tp_planned_order_qty,
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator,
t_base_item_id(j),
t_base_item_name(j));
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator,
base_item_id,
base_item_name
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
CP_RELEASED_PLANNED_ORDER,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_item_id(j),
t_master_item_name(j),
t_owner_item_name(j),
t_owner_item_name(j),
t_supp_item_name(j),
nvl(t_master_item_desc(j), t_owner_item_desc(j)),
t_owner_item_desc(j),
t_owner_item_desc(j),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
t_key_date(j),
t_ship_date(j),
t_receipt_date(j),
t_released_qty(j),
t_released_qty(j),
t_tp_released_qty(j),
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator,
t_base_item_id(j),
t_base_item_name(j));
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator,
base_item_id,
base_item_name
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
CP_PURCHASE_ORDER_FROM_PLAN,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_item_id(j),
t_master_item_name(j),
t_owner_item_name(j),
t_owner_item_name(j),
t_supp_item_name(j),
nvl(t_master_item_desc(j), t_owner_item_desc(j)),
t_owner_item_desc(j),
t_owner_item_desc(j),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
t_key_date(j),
t_ship_date(j),
t_receipt_date(j),
t_qty(j),
t_qty(j),
t_tp_qty(j),
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator,
t_base_item_id(j),
t_base_item_name(j));
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator,
base_item_id,
base_item_name
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
CP_PLANNED_INBOUND_SHIPMENT,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_item_id(j),
t_master_item_name(j),
t_owner_item_name(j),
t_owner_item_name(j),
t_supp_item_name(j),
nvl(t_master_item_desc(j), t_owner_item_desc(j)),
t_owner_item_desc(j),
t_owner_item_desc(j),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
t_key_date(j),
t_ship_date(j),
t_receipt_date(j),
l_planned_order_qty,
l_planned_order_qty,
l_tp_planned_order_qty,
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator,
t_base_item_id(j),
t_base_item_name(j));
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator,
base_item_id,
base_item_name
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
CP_RELEASED_INBOUND_SHIPMENT,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_item_id(j),
t_master_item_name(j),
t_owner_item_name(j),
t_owner_item_name(j),
t_supp_item_name(j),
nvl(t_master_item_desc(j), t_owner_item_desc(j)),
t_owner_item_desc(j),
t_owner_item_desc(j),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
t_key_date(j),
t_ship_date(j),
t_receipt_date(j),
t_released_qty(j),
t_released_qty(j),
t_tp_released_qty(j),
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator,
t_base_item_id(j),
t_base_item_name(j));
update msc_sup_dem_entries
set quantity = 0, tp_quantity = 0, primary_quantity = 0
where publisher_order_type = CP_PLANNED_ORDER
and publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_item_id(j)
and quantity < 0;
update msc_sup_dem_entries
set quantity = 0, tp_quantity = 0, primary_quantity = 0
where publisher_order_type = CP_RELEASED_PLANNED_ORDER
and publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_item_id(j)
and quantity < 0;
update msc_sup_dem_entries
set quantity = 0, tp_quantity = 0, primary_quantity = 0
where publisher_order_type = CP_PLANNED_INBOUND_SHIPMENT
and publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_item_id(j)
and quantity < 0;
update msc_sup_dem_entries
set quantity = 0, tp_quantity = 0, primary_quantity = 0
where publisher_order_type = CP_RELEASED_INBOUND_SHIPMENT
and publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_item_id(j)
and quantity < 0;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in insert_sup_dem_entries: ' || sqlerrm);
END insert_into_sup_dem;
PROCEDURE insert_into_sup_dem_rf_dos (
t_pub IN companyNameList,
t_pub_id IN numberList,
t_pub_site IN companySiteList,
t_pub_site_id IN numberList,
t_item_id IN numberList,
t_order_type IN numberList,
t_qty IN numberList,
t_planned_order_qty IN numberList,
t_released_qty IN numberList,
t_supp IN companyNameList,
t_supp_id IN numberList,
t_supp_site IN companySiteList,
t_supp_site_id IN numberList,
t_owner_item_name IN itemNameList,
t_owner_item_desc IN itemDescList,
t_base_item_id IN numberList,
t_base_item_name IN itemNameList,
t_base_item_desc IN itemDescList,
t_proj_number IN numberList,
t_task_number IN numberList,
t_planning_gp IN planningGroupList,
t_uom_code IN itemUomList,
t_planner_code IN plannerCodeList,
t_bucket_type IN numberList,
t_key_date IN dateList,
t_ship_date IN dateList,
t_receipt_date IN dateList,
t_order_num IN orderNumList, -- bug#7310179
t_line_num IN lineNumList, -- bug#7310179
t_master_item_name IN itemNameList,
t_master_item_desc IN itemDescList,
t_supp_item_name IN itemNameList,
t_supp_item_desc IN itemDescList,
t_tp_uom IN itemUomList,
t_tp_qty IN numberList,
t_tp_planned_order_qty IN numberList,
t_tp_released_qty IN numberList,
p_version IN varchar2,
p_designator IN varchar2,
p_user_id IN number,
p_language_code IN varchar2,
p_publish_dos IN number
) IS
l_qty NUMBER;
update msc_sup_dem_entries
set quantity = quantity + l_qty,
primary_quantity = primary_quantity + l_qty,
tp_quantity = tp_quantity + l_tp_qty
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_item_id(j)
and trunc(key_date) = DECODE(t_order_type(j),ISO_DEF,trunc(t_receipt_date(j)),trunc(t_key_date(j)))
and publisher_order_type = RETURNS_FORECAST ;
INSERT INTO msc_sup_dem_entries
(transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator,
base_item_id,
base_item_name)
VALUES(
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
RETURNS_FORECAST,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_item_id(j),
t_master_item_name(j),
t_owner_item_name(j),
t_owner_item_name(j),
t_supp_item_name(j),
nvl(t_master_item_desc(j),
t_owner_item_desc(j)),
t_owner_item_desc(j),
t_owner_item_desc(j),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
DECODE(t_order_type(j),ISO_DEF,trunc(t_receipt_date(j)),trunc(t_key_date(j))),
t_ship_date(j),
t_receipt_date(j),
l_qty,
l_qty,
l_tp_qty,
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id, -1),
sysdate,
nvl(p_user_id, -1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator,
t_base_item_id(j),
t_base_item_name(j));
update msc_sup_dem_entries
set quantity = quantity + l_qty,
primary_quantity = primary_quantity + l_qty,
tp_quantity = tp_quantity + l_tp_qty
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and supplier_id = t_supp_id(j)
and supplier_site_id = t_supp_site_id(j)
and inventory_item_id = t_item_id(j)
and trunc(key_date) = trunc(t_key_date(j))
and order_number = t_order_num(j) -- bug#7310179
and NVL(line_number,G_NULL_STRING) = NVL(t_line_num(j),G_NULL_STRING) -- bug#7310179
and publisher_order_type = DEFECTIVE_OUTBOUND_SHIPMENT;
INSERT INTO msc_sup_dem_entries
(transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
customer_name,
customer_id,
customer_site_name,
customer_site_id,
supplier_name,
supplier_id,
supplier_site_name,
supplier_site_id,
ship_from_party_name,
ship_from_party_id,
ship_from_party_site_name,
ship_from_party_site_id,
ship_to_party_name,
ship_to_party_id,
ship_to_party_site_name,
ship_to_party_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
customer_item_name,
supplier_item_name,
item_description,
owner_item_description,
customer_item_description,
supplier_item_description,
primary_uom,
uom_code,
tp_uom_code,
key_date,
ship_date,
receipt_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
order_number, -- bug#7310179
line_number, -- bug#7310179
project_number,
task_number,
planning_group,
planner_code,
version,
designator,
base_item_id,
base_item_name)
VALUES(
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_supp(j),
t_supp_id(j),
t_supp_site(j),
t_supp_site_id(j),
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
DEFECTIVE_OUTBOUND_SHIPMENT,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_item_id(j),
t_master_item_name(j),
t_owner_item_name(j),
t_owner_item_name(j),
t_supp_item_name(j),
nvl(t_master_item_desc(j),
t_owner_item_desc(j)),
t_owner_item_desc(j),
t_owner_item_desc(j),
t_supp_item_desc(j),
t_uom_code(j),
t_uom_code(j),
t_tp_uom(j),
t_key_date(j),
t_ship_date(j),
t_receipt_date(j),
l_qty,
l_qty,
l_tp_qty,
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id, -1),
sysdate,
nvl(p_user_id, -1),
sysdate,
t_order_num(j), -- bug#7310179
t_line_num(j), -- bug#7310179
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator,
t_base_item_id(j),
t_base_item_name(j));
END insert_into_sup_dem_rf_dos; -- end of procedure insert_into_sup_dem_rf_dos
PROCEDURE delete_old_forecast(
p_plan_id in number,
p_org_id in number,
p_sr_instance_id in number,
p_planner_code in varchar2,
p_abc_class in varchar2,
p_item_id in number,
p_planning_gp in varchar2,
p_project_id in number,
p_task_id in number,
p_supplier_id in number,
p_supplier_site_id in number,
p_horizon_start in date,
p_horizon_end in date,
p_overwrite in number
) IS
--t_publisher_site_id numberList;
log_message('In delete_old_forecast');
select c.company_id
into l_supplier_id
from msc_trading_partner_maps m,
msc_company_relationships r,
msc_companies c
where m.tp_key = p_supplier_id and
m.map_type = 1 and
m.company_key = r.relationship_id and
r.relationship_type = 2 and
r.subject_id = 1 and
c.company_id = r.object_id;
select cs.company_site_id
into l_supplier_site_id
from msc_trading_partner_maps m,
msc_company_sites cs
where m.tp_key = p_supplier_site_id and
m.map_type = 3 and
cs.company_site_id = m.company_key;
IF ( p_overwrite = 1) THEN --delete all
delete from msc_sup_dem_entries sd
where sd.publisher_order_type in (ORDER_FORECAST,CP_PLANNED_ORDER,
CP_PURCHASE_ORDER_FROM_PLAN,CP_RELEASED_PLANNED_ORDER,
CP_PLANNED_INBOUND_SHIPMENT, CP_RELEASED_INBOUND_SHIPMENT,
RETURNS_FORECAST,DEFECTIVE_OUTBOUND_SHIPMENT) and -- bug#6893383
sd.plan_id = -1 and
sd.publisher_id = 1 and
exists (select cs.company_site_id
from msc_plan_organizations o,
msc_company_sites cs,
msc_trading_partner_maps m,
msc_trading_partners p
where o.plan_id = p_plan_id
AND O.ORGANIZATION_ID = NVL(p_org_id , O.ORGANIZATION_ID)
AND O.SR_INSTANCE_ID = NVL(p_sr_instance_id , O.SR_INSTANCE_ID)
AND P.SR_TP_ID = O.ORGANIZATION_ID
AND P.SR_INSTANCE_ID = O.SR_INSTANCE_ID
and p.partner_type = 3 and
m.tp_key = p.partner_id and
m.map_type = 2 and
cs.company_site_id = m.company_key and
cs.company_id = 1 and
cs.company_site_id = sd.publisher_site_id and rownum=1) and
sd.supplier_id = nvl(l_supplier_id, sd.supplier_id) and
sd.supplier_site_id = nvl(l_supplier_site_id, sd.supplier_site_id) and
exists (select nvl(i.base_item_id,i.inventory_item_id)
from msc_system_items i,
msc_plan_organizations o
where o.plan_id = p_plan_id and
i.plan_id = o.plan_id and
O.ORGANIZATION_ID = NVL(p_org_id , O.ORGANIZATION_ID) AND
O.SR_INSTANCE_ID = NVL(p_sr_instance_id , O.SR_INSTANCE_ID) AND
I.ORGANIZATION_ID = O.ORGANIZATION_ID AND
I.SR_INSTANCE_ID = O.SR_INSTANCE_ID AND
NVL(i.planner_code,'-99') = NVL(p_planner_code,
NVL(i.planner_code,'-99')) and
NVL(i.abc_class_name,'-99') = NVL(p_abc_class,
NVL(i.abc_class_name,'-99')) and
i.inventory_item_id = nvl(p_item_id, i.inventory_item_id)
and NVL(sd.base_item_id, sd.inventory_item_id) =nvl(i.base_item_id,i.inventory_item_id) and
rownum=1 ) ;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleted number records: ' || l_row);
ELSIF ( p_overwrite = 2) THEN --delete by overwritten
delete from msc_sup_dem_entries sd
where sd.publisher_order_type IN (ORDER_FORECAST, CP_PLANNED_ORDER,
CP_PURCHASE_ORDER_FROM_PLAN, CP_RELEASED_PLANNED_ORDER,
CP_PLANNED_INBOUND_SHIPMENT, CP_RELEASED_INBOUND_SHIPMENT,
RETURNS_FORECAST,DEFECTIVE_OUTBOUND_SHIPMENT) and -- bug#6893383
sd.plan_id = -1 and
sd.publisher_id = 1 and
exists (select cs.company_site_id
from msc_plan_organizations o,
msc_company_sites cs,
msc_trading_partner_maps m,
msc_trading_partners p
where o.plan_id = p_plan_id and
p.sr_tp_id = nvl(p_org_id, o.organization_id) and
p.sr_instance_id = nvl(p_sr_instance_id,
o.sr_instance_id) and
p.partner_type = 3 and
m.tp_key = p.partner_id and
m.map_type = 2 and
cs.company_site_id = m.company_key and
cs.company_id = 1 and
sd.publisher_site_id = cs.company_site_id and
rownum=1) and
sd.supplier_id = nvl(l_supplier_id, sd.supplier_id) and
sd.supplier_site_id = nvl(l_supplier_site_id, sd.supplier_site_id) and
exists (select nvl(i.base_item_id,i.inventory_item_id)
from msc_system_items i,
msc_plan_organizations o
where o.plan_id = p_plan_id and
i.plan_id = o.plan_id and
i.organization_id = nvl(p_org_id,
o.organization_id) and
i.sr_instance_id = nvl(p_sr_instance_id,
o.sr_instance_id) and
NVL(i.planner_code,'-99') = NVL(p_planner_code,
NVL(i.planner_code,'-99')) and
NVL(i.abc_class_name,'-99') = NVL(p_abc_class,
NVL(i.abc_class_name,'-99')) and
i.inventory_item_id = nvl(p_item_id, i.inventory_item_id) and
NVL(sd.base_item_id, sd.inventory_item_id) =nvl(i.base_item_id,i.inventory_item_id) and
rownum=1) and
key_date between nvl(p_horizon_start, sysdate - 36500) and
nvl(p_horizon_end, sysdate + 36500);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleted number records: ' || l_row);
END delete_old_forecast;
SELECT m.message_text
FROM fnd_new_messages m,
fnd_application a
WHERE m.message_name = msg_name AND
m.language_code = lang AND
a.application_short_name = app_name AND
m.application_id = a.application_id;