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,
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 wpr.organization_id =
NVL (fnd_profile.VALUE ('MFG_ORGANIZATION_ID'),
wpr.organization_id) -- changed
AND wpr.name =
NVL (
fnd_PROFILE.VALUE ('XXCNC_WMS_DEFAULT_PICK_RULE'),
wpr.name)
AND 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(+) = :parameter_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
--put yout organization_id to filter the lsst
ORDER BY picking_rule_name
No comments:
Post a Comment