[置顶] Oracle EBS发放销售订单

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_locator, 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_name, 1, 50 ) old_customer_name, wpr.customer_id, whzl.ui_location_code ship_to_location, wpr.ship_to_location_id, wpr.include_planned_lines, wpr.autocreate_delivery_flag, pgr.name pick_grouping_rule_name, rsqr.name pick_seq_rule_name, wpr.task_id, wpr.project_id, wpr.autodetail_pr_flag, wpr.auto_pick_confirm_flag, wpr.ship_set_number ship_set_id, whrl.ui_location_code ship_from_location, wpr.ship_from_location_id, wpr.document_set_id, wrs.name doc_name, wpr.pick_from_subinventory, wpr.pick_from_locator_id, NULL pick_from_locator, wpr.default_stage_subinventory, wpr.default_stage_locator_id, NULL default_stage_locator, sm.meaning ship_method_meaning, wpr.autopack_flag, wpr.autopack_level, wpr.ship_confirm_rule_id, wscr.name ship_confirm_rule_name, wpr.task_planning_flag, wpr.region_id, wpr.zone_id, wpr.ac_delivery_criteria, wpr.rel_subinventory, wpr.category_set_id, wpr.category_id, wpr.sch_start_days, wpr.sch_end_days, wpr.sch_start_hours, wpr.sch_end_hours, wpr.req_start_days, wpr.req_end_days, wpr.req_start_hours, wpr.req_end_hours, wpr.append_flag, wpr.task_priority, wpr.allocation_method, wpr.crossdock_criteria_id, wpr.dynamic_replenishment_flag, wpr.client_id client_id, mcpv.client_name client_name FROM wsh_picking_rules wpr, mtl_parameters org, hr_all_organization_units_tl h_org_tl, hz_parties party, hz_cust_accounts cust_acct, fnd_lookup_values s1, fnd_lookup_values s2, oe_sets os, wsh_pick_grouping_rules pgr, wsh_pick_sequence_rules rsqr, oe_transaction_types_tl ott, oe_transaction_types_all otb, oe_order_headers_all h, wsh_locations whrl, wsh_locations whzl, wsh_report_sets wrs, wsh_ship_confirm_rules wscr, mtl_system_items_b msi, fnd_lookup_values sm, mtl_client_parameters_v mcpv WHERE org.organization_id(+) = wpr.organization_id AND h_org_tl.organization_id(+) = wpr.organization_id AND cust_acct.cust_account_id(+) = wpr.customer_id AND cust_acct.party_id = party.party_id(+) AND h_org_tl.language(+) = userenv('LANG' ) AND pgr.pick_grouping_rule_id(+) = wpr.pick_grouping_rule_id AND rsqr.pick_sequence_rule_id(+) = wpr.pick_sequence_rule_id AND wpr.ship_confirm_rule_id = wscr.ship_confirm_rule_id(+) AND s1.lookup_code(+) = wpr.shipment_priority_code AND s1.lookup_type(+) = 'SHIPMENT_PRIORITY' AND s1.language(+) = userenv('LANG' ) AND s1.view_application_id(+) = 660 AND s1.security_group_id(+) = 0 AND s2.lookup_code = wpr.backorders_only_flag AND s2.lookup_type = 'PICK_RELEASE_OPTIONS' AND s2.language(+) = userenv('LANG' ) AND s2.view_application_id(+) = 660 AND s2.security_group_id(+) = 0 AND otb.transaction_type_id(+) = nvl(wpr.order_type_id, ⑴) AND otb.transaction_type_code(+) = 'ORDER' AND otb.transaction_type_id = ott.transaction_type_id(+) AND ott.language(+) = userenv('LANG' ) AND whzl.wsh_location_id(+) = nvl(wpr.ship_to_location_id, - 1) AND whrl.wsh_location_id(+) = nvl(wpr.ship_from_location_id, - 1) AND wrs.report_set_id(+) = wpr.document_set_id AND os.set_id(+) = nvl(wpr.ship_set_number, ⑴) AND h.header_id(+) = nvl(wpr.order_header_id, ⑴) AND msi.inventory_item_id(+) = wpr.inventory_item_id AND msi.organization_id(+) = p_organization_id AND trunc(SYSDATE ) BETWEEN nvl(wpr.start_date_active, trunc( SYSDATE)) AND nvl(wpr.end_date_active, trunc( SYSDATE) + 1 ) AND sm.lookup_type(+) = 'SHIP_METHOD' AND sm.lookup_code(+) = wpr.ship_method_code AND sm.view_application_id(+) = 3 AND sm.language(+) = userenv('LANG' ) AND mcpv.client_id(+) = wpr.client_id) wsh WHERE picking_rule_name = :control.pick_rule ORDER BY wsh.picking_rule_name; l_document_set_id NUMBER; l_append_flag VARCHAR2(1 ); l_row_id VARCHAR2(50 ); l_order_header_id NUMBER; l_order_type VARCHAR2(240 ); l_customer_name VARCHAR2(240 ); l_order_type_id NUMBER; l_customer_id NUMBER; l_batch_name VARCHAR2(240 ); l_organization_id NUMBER; l_request_id NUMBER; l_org_id NUMBER; BEGIN BEGIN SELECT DISTINCT otl.name, substrb(party.party_name, 1, 50 ) customer_name, wdd.source_header_type_id, cust_acct.cust_account_id customer_id, wdd.organization_id, wdd.org_id INTO l_order_type, l_customer_name, l_order_type_id, l_customer_id, l_organization_id, l_org_id FROM wsh_delivery_details wdd, hz_parties party, hz_cust_accounts cust_acct, oe_transaction_types_tl otl WHERE wdd.customer_id = cust_acct.cust_account_id /*customer id*/ AND cust_acct.party_id = party.party_id AND otl.language = userenv('LANG' ) AND otl.transaction_type_id = wdd.source_header_type_id AND wdd.source_header_id = :query_find.header_id AND wdd.source_code = 'OE' AND wdd.released_status IN ('B', 'R', 'X' ); EXCEPTION WHEN OTHERS THEN NULL; END; FOR rec_data IN cur_data(l_organization_id) LOOP IF l_release_method IS NULL THEN -- Commit from save instead of buttons ( user chose Save from the menu ) IF ((nvl(l_release_online, 'N' ) = 'Y') AND (:query_find.header_id IS NOT NULL)) THEN l_release_method := 'O'; ELSE l_release_method := 'C'; END IF ; END IF ; IF l_release_method = 'O' THEN NULL; ELSE IF rec_data.document_set_id IS NULL AND rec_data.doc_name IS NOT NULL THEN l_document_set_id := fnd_profile.value('OE_PICKING_DOCUMENT_SET_DEFAULT' ); END IF ; END IF ; IF rec_data.organization_id IS NULL THEN l_append_flag := 'N'; ELSE --bugfix 11830201 added cursor OPEN enable_prop(rec_data.organization_id); FETCH enable_prop INTO l_enable_flag; CLOSE enable_prop; END IF ; -- IF l_enable_flag = 'Y' THEN l_return_code := pjm_project_locator.check_project_references(p_organization_id => rec_data.organization_id, p_locator_id => rec_data.default_stage_locator_id, p_validation_mode => 'SPECIFIC' , p_required_flag => 'N' , p_project_id => rec_data.project_id, p_task_id => rec_data.task_id); IF (l_return_code = FALSE ) THEN fnd_message.retrieve; fnd_message.error; RAISE form_trigger_failure; END IF ; END IF ; -- IF (l_return_code = TRUE ) THEN wsh_picking_batches_pkg.insert_row(x_rowid => l_row_id, x_batch_id => x_batch_id, p_creation_date => SYSDATE, p_created_by => fnd_global.user_id, p_last_update_date => SYSDATE, p_last_updated_by => fnd_global.user_id, p_last_update_login => fnd_global.login_id, x_name => l_batch_name, p_backorders_only_flag => rec_data.backorders_only_flag, p_document_set_id => l_document_set_id, p_existing_rsvs_only_flag => rec_data.existing_rsvs_only_flag, p_shipment_priority_code => rec_data.shipment_priority_code, p_ship_method_code => rec_data.ship_method_code, p_customer_id => l_customer_id, p_order_header_id => :query_find.header_id, p_ship_set_number => rec_data.ship_set_id, p_inventory_item_id => rec_data.inventory_item_id, p_order_type_id => l_order_type_id, p_from_requested_date => to_date(to_char(rec_data.from_requested_date, 'DD-MON-YYYY HH24:MI:SS' ), 'DD-MON-YYYY HH24:MI:SS' ), p_to_requested_date => to_date(to_char(rec_data.to_requested_date, 'DD-MON-YYYY HH24:MI:SS' ), 'DD-MON-YYYY HH24:MI:SS' ), p_from_scheduled_ship_date => to_date(to_char(rec_data.from_scheduled_ship_date, 'DD-MON-YYYY HH24:MI:SS' ), 'DD-MON-YYYY HH24:MI:SS' ), p_to_scheduled_ship_date => to_date(to_char(rec_data.to_scheduled_ship_date, 'DD-MON-YYYY HH24:MI:SS' ), 'DD-MON-YYYY HH24:MI:SS' ), p_ship_to_location_id => rec_data.ship_to_location_id, p_ship_from_location_id => rec_data.ship_from_location_id, p_trip_id => NULL, p_delivery_id => NULL, p_include_planned_lines => rec_data.include_planned_lines, p_pick_grouping_rule_id => NULL, p_pick_sequence_rule_id => NULL, p_autocreate_delivery_flag => rec_data.autocreate_delivery_flag, p_attribute_category => NULL, p_attribute1 => NULL, p_attribute2 => NULL, p_attribute3 => NULL, p_attribute4 => NULL, p_attribute5 => NULL, p_attribute6 => NULL, p_attribute7 => NULL, p_attribute8 => NULL, p_attribute9 => NULL, p_attribute10 => NULL, p_attribute11 => NULL, p_attribute12 => NULL, p_attribute13 => NULL, p_attribute14 => NULL, p_attribute15 => NULL, p_autodetail_pr_flag => rec_data.autodetail_pr_flag, p_carrier_id => NULL, p_trip_stop_id => NULL, p_default_stage_subinventory => rec_data.default_stage_subinventory, p_default_stage_locator_id => rec_data.default_stage_locator_id, p_pick_from_subinventory => rec_data.pick_from_subinventory, p_pick_from_locator_id => rec_data.pick_from_locator_id, p_auto_pick_confirm_flag => rec_data.auto_pick_confirm_flag, p_delivery_detail_id => NULL, p_project_id => rec_data.project_id, p_task_id => rec_data.task_id, p_organization_id => rec_data.organization_id, p_ship_confirm_rule_id => rec_data.ship_confirm_rule_id, p_autopack_flag => rec_data.autopack_flag, p_autopack_level => rec_data.autopack_level, p_task_planning_flag => rec_data.task_planning_flag, p_dynamic_replenishment_flag => rec_data.dynamic_replenishment_flag, --bug# 6689448 (replenishment project) -- rlanka : Pack J p_regionid => rec_data.region_id, p_zoneid => rec_data.zone_id, p_categoryid => rec_data.category_id, p_categorysetid => rec_data.category_set_id, p_acdelivcriteria => rec_data.ac_delivery_criteria, p_relsubinventory => rec_data.rel_subinventory, p_append_flag => l_append_flag, p_task_priority => rec_data.task_priority, p_actual_departure_date => NULL, -- X-dock p_allocation_method => rec_data.allocation_method, p_crossdock_criteria_id => rec_data.crossdock_criteria_id, p_client_id => rec_data.client_id --Modified R12.1.1 LSP PROJECT ); END IF ; END LOOP; --调用要求 l_request_id := fnd_request.submit_request(application => 'CUX', program => 'CUXOMLPKP' , description => NULL, start_time => SYSDATE , sub_request => FALSE , argument1 => x_batch_id, argument2 => l_org_id, argument3 => :query_find.header_id); IF l_request_id IS NULL OR l_request_id = 0 THEN fnd_message.set_string( '提交挑库选择列表生成要求出错.' ); fnd_message.error; RAISE form_trigger_failure; ELSE forms_ddl( 'COMMIT'); fnd_message.debug( '挑库发放成功,请查看要求.' ); END IF; END insert_row;

--调用要求 CUXOMLPKP要求调用以下： l_request_id := wsh_picking_batches_pkg.submit_release_request(p_batch_id => p_batch_id, p_log_level => 0 , p_num_workers => nvl(fnd_profile.value('WSH_PR_NUM_WORKERS' ), 1)); IF l_request_id IS NULL OR l_request_id = 0 THEN errbuf := '提交挑库选择列表生成要求出错.' ; retcode := '2'; RETURN; END IF ;