OracleEBS发放销售订单(一)

PROCEDURE insert_row(x_batch_id OUT NUMBER) IS
  l_autopack_flag  VARCHAR2(1 );
  l_autopack_level NUMBER;
  l_release_method VARCHAR2(1 );
  l_release_online VARCHAR2(240 ) := fnd_profile.value('SHP_PICK_RELEASE_ONLINE' );

  --bugfix 11830201 added cursor
  CURSOR enable_prop(org_id NUMBER) IS
    SELECT nvl(MAX (project_reference_enabled), 'N')
      FROM pjm_org_parameters
     WHERE organization_id = org_id;

  l_enable_flag VARCHAR2(1 ) := 'N';
  l_return_code BOOLEAN DEFAULT TRUE;

  CURSOR cur_data(p_organization_id IN NUMBER ) IS
    SELECT wsh.picking_rule_name,
           wsh.backorders_only_flag,
           wsh.backorders,
           wsh.shipment_priority,
           wsh.shipment_priority_code,
           wsh.organization_id,
           wsh.org_id,
           wsh.warehouse_id_lov,
           wsh.warehouse,
           wsh.warehouse_code,
           wsh.reservation_star,
           wsh.existing_rsvs_only_flag,
           wsh.ship_method_code,
           wsh.from_scheduled_ship_date,
           wsh.to_scheduled_ship_date,
           wsh.from_requested_date,
           wsh.to_requested_date,
           wsh.order_type,
           wsh.order_type_id,
           wsh.order_number,
           wsh.order_header_id,
           wsh.ship_set_number,
           wsh.inventory_item_id,
           wsh.item_description,
           wsh.customer_name,
           wsh.old_customer_name,
           wsh.customer_id,
           wsh.ship_to_location,
           wsh.ship_to_location_id,
           wsh.include_planned_lines,
           wsh.autocreate_delivery_flag,
           wsh.pick_grouping_rule_name,
           wsh.pick_seq_rule_name,
           wsh.task_id,
           wsh.project_id,
           wsh.autodetail_pr_flag,
           wsh.auto_pick_confirm_flag,
           wsh.ship_set_number ship_set_id,
           wsh.ship_from_location,
           wsh.ship_from_location_id,
           wsh.document_set_id,
           wsh.doc_name,
           wsh.pick_from_subinventory,
           wsh.pick_from_locator_id,
           wsh.pick_from_locator,
           wsh.default_stage_subinventory,
           wsh.default_stage_locator_id,
           wsh.default_stage_locat





or,
           wsh.ship_method_meaning,
           wsh.autopack_flag,
           wsh.autopack_level,
           wsh.ship_confirm_rule_id,
           wsh.ship_confirm_rule_name,
           wsh.task_planning_flag,
           wsh.region_id,
           wsh.zone_id,
           wsh.ac_delivery_criteria,
           wsh.rel_subinventory,
           wsh.category_set_id,
           wsh.category_id,
           wsh.sch_start_days,
           wsh.sch_end_days,
           wsh.sch_start_hours,
           wsh.sch_end_hours,
           wsh.req_start_days,
           wsh.req_end_days,
           wsh.req_start_hours,
           wsh.req_end_hours,
           wsh.append_flag,
           wsh.task_priority,
           wsh.allocation_method,
           wsh.crossdock_criteria_id,
           wsh.dynamic_replenishment_flag,
           wsh.client_id,
           wsh.client_name
      FROM (SELECT wpr.name picking_rule_name,
                   wpr.backorders_only_flag,
                   decode(s2.meaning,
                          'Exclude',
                          'Unreleased',
                          'Include',
                          'All',
                          'Only',
                          'Backordered') backorders,
                   s1.meaning shipment_priority,
                   wpr.shipment_priority_code,
                   wpr.organization_id,
                   wpr.organization_id org_id,
                   wpr.organization_id warehouse_id_lov,
                   h_org_tl.name warehouse,
                   org.organization_code warehouse_code,
                   decode(wpr.existing_rsvs_only_flag, 'Y', '*' , NULL) reservation_star,
                   wpr.existing_rsvs_only_flag,
                   wpr.ship_method_code,
                   wpr.from_scheduled_ship_date,
                   wpr.to_scheduled_ship_date,
                   wpr.from_requested_date,
                   wpr.to_requested_date,
                   ott.name order_type,
                   wpr.order_type_id,
                   to_char(h.order_number) order_number,
                   wpr.order_header_id,
                   os.set_name ship_set_number,
                   wpr.inventory_item_id,
                   msi.description item_description,
                   substrb(party.party_name, 1, 50 ) customer_name,
                   substrb(party.party_n