|
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_ |