oracle挑库发放次数,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_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, -1)

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, -1)

AND h.header_id(+) = nvl(wpr.order_header_id, -1)

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 ;